Hi
Im trying to execute one PL sql query in my custom SQL But im getting invalid identifier .
Its a select Sql and no inputs are beeing passed.
But this query is getting executed in DB.
Could you please help me with this
Please check if you are defining the schema/table correctly with respect to connection parameters used. So that columns are getting recognized from right table using Custom SQL adapter template.
It should work without issues, please post your query if you are still facing issues.
Hi feroz,
Could you please tell what is the schema/table with respect to connection parameters mean.
I actually did not get you .
Thanks
Amruth
What I mean is to check if you are referring to the correct table in your SQL query, which webMethods can recognize as available from the connection parameters used in your JDBC connection.
It should be some minor issue with the query itself or identifying the right column/table combo, so referring you to check all possible cases.
Hope this clarifies.
@Amruth_Ruttala Is this resolved already ? If not kindly provide the query for which the service is failing . This will help us resolve it faster.
Thanks,
Dipankar
Hi depanker,
This is my query Where its running good in sql but not in webMethods.also i have mentioned below what are the outpuSLA3_CycleCountAudit (4).sql (27.7 KB) ts which im passing as VARCHAR.
Out puts defined in adapter service:
WITH WWT_CYCLE_COUNT_V
AS
(SELECT – GitHub Header: #Source: https://github.wwt.com/data-analytics/supply-chain-data-services/blob/5db8c0551a2f6171d1decb03536f5ceebc0313b1/ERP/BI_ADMIN/views/wwt_cycle_count_audit_report_v.sql, Sha: 5db8c0551a2f6171d1decb03536f5ceebc0313b1, Committer: null, Date: Tue Feb 13 15:49:19 CST 2018
/*+ ORDERED /
grouped_data.org,
grouped_data.subinventory,
grouped_data.item,
grouped_data.cost_group,
grouped_data.loc,
grouped_data.sys_qty,
CASE
WHEN grouped_data.first_count IS NULL
THEN
NULL
ELSE
NVL (grouped_data.first_count, 0)
+ NVL (excess_lpns.first_count, 0)
END
first_count,
CASE
WHEN grouped_data.current_count IS NULL
THEN
NULL
ELSE
NVL (grouped_data.current_count, 0)
+ NVL (excess_lpns.current_count, 0)
END
current_count,
CASE
WHEN grouped_data.current_count IS NULL
THEN
NULL
ELSE
( NVL (grouped_data.current_count, 0)
+ NVL (excess_lpns.current_count, 0)) – Count Qty
- NVL (grouped_data.sys_qty, 0) – Sys Qty
END
adjust_qty,
grouped_data.unit_cost,
CASE
WHEN grouped_data.current_count IS NULL
THEN
NULL
ELSE
( ( NVL (grouped_data.current_count, 0)
+ NVL (excess_lpns.current_count, 0)) – Count Qty
- NVL (grouped_data.sys_qty, 0))
* grouped_data.unit_cost – Sys Qty
END
total_adj,
grouped_data.details,
grouped_data.missing_serials_lpns,
grouped_data.excess_serials_lpns --STRY0661577 case start
,
CASE
WHEN grouped_data.status = ‘Complete’
AND grouped_data.first_count = grouped_data.sys_qty_first
AND grouped_data.adjust_qty = 0
THEN
‘1st Chance Hit’
WHEN grouped_data.status = ‘Complete’
AND grouped_data.first_count <> grouped_data.sys_qty_first
AND grouped_data.current_count = grouped_data.sys_qty --CHG22690
–AND mcce.adjust_qty = 0 – CHG22690
THEN
‘2nd Chance Hit’
WHEN grouped_data.status = ‘Complete’
AND grouped_data.adjust_qty <> 0
AND grouped_data.adjustment_date IS NOT NULL – CHG22690
THEN
‘Adjusted’
WHEN grouped_data.status = ‘Rejected’
AND grouped_data.first_count IS NOT NULL
THEN
‘Skipped with a Count’
WHEN grouped_data.status = ‘Rejected’
AND grouped_data.first_count IS NULL
THEN
‘Skippped with No Count’
ELSE
‘N/A’
END
count_type --STRY0661577 case end
,
grouped_data.last_count_level last_count_level,
grouped_data.detail_requirement detail_requirement,
grouped_data.status status,
grouped_data.reason reason,
grouped_data.reason_group reason_group,
grouped_data.reference_notes reference_notes,
grouped_data.cycle_count_name cycle_count_name,
grouped_data.created_by created_by,
grouped_data.creation_date creation_date,
grouped_data.number_of_counts no_of_counts,
grouped_data.first_count_by first_count_by,
grouped_data.first_count_date first_count_date,
grouped_data.current_count_by current_count_by,
grouped_data.current_count_date current_count_date,
grouped_data.different_counters different_counters,
grouped_data.approved_by approved_by,
grouped_data.approved_date approved_date,
grouped_data.whse whse,
grouped_data.num_onhand_locations num_onhand_locations,
grouped_data.last_count last_count,
grouped_data.description description,
grouped_data.request_id,
grouped_data.cycle_count_header_id,
grouped_data.locator_id,
grouped_data.inventory_item_id item_id,
grouped_data.market_price --CHG34568
– STRY0655817
,
grouped_data.previous_count,
grouped_data.previous_count_by,
grouped_data.previous_count_date,
CASE
WHEN ( NVL (grouped_data.first_count, 0)
+ NVL (excess_lpns.first_count, 0)) = 0
AND NVL (grouped_data.sys_qty, 0) > 0
THEN
‘Y’
WHEN ( NVL (grouped_data.first_count, 0)
+ NVL (excess_lpns.first_count, 0)) = 0
AND NVL (grouped_data.sys_qty, 0) = 0
THEN
‘N’
ELSE
‘N/A’
END
zero_count_miss
FROM ( SELECT base.org,
base.subinventory,
base.item,
base.cost_group,
base.loc,
SUM (base.sys_qty) sys_qty,
SUM (base.sys_qty_first) sys_qty_first --STRY0661577
,
SUM (base.first_count) first_count,
SUM (base.current_count) current_count,
SUM (base.adjust) adjust_qty,
base.unit_cost unit_cost,
SUM (base.total_adj) total_adj,
MAX (base.adjustment_date) adjustment_date --STRY0661577
,
base.details,
base.missing_serials missing_serials_lpns,
base.excess_lpns excess_serials_lpns,
base.last_count_level last_count_level,
base.detail_requirement detail_requirement,
base.status status,
base.reason reason,
base.reason_group reason_group,
base.REFERENCE reference_notes,
base.cycle_count_name cycle_count_name,
base.created_by created_by,
base.created_date creation_date,
base.number_of_counts number_of_counts,
base.first_count_by first_count_by,
base.first_count_date first_count_date,
base.current_count_by current_count_by,
base.current_count_date current_count_date,
base.different_counters different_counters,
base.approved_by approved_by,
MAX (base.approved_date) approved_date --STRY0661577
,
base.whse whse,
base.num_onhand_locations num_onhand_locations,
base.last_count last_count,
base.description description,
base.attribute8 request_id,
base.cycle_count_header_id,
base.locator_id,
base.inventory_item_id,
base.market_price --CHG34568
,
SUM (base.previous_count) previous_count --STRY0661577
,
base.previous_count_by,
MAX (base.previous_count_date) previous_count_date --STRY0661577
FROM (SELECT mp.organization_code org,
mcce.subinventory subinventory,
msik.concatenated_segments item,
apps.wwt_xxwms_cc_audit_rep_pkg.get_cost_Group (
mcce.cycle_count_entry_id,
mcce.locator_id)
cost_group,
mil.segment1 loc,
mcce.system_quantity_current sys_qty,
mcce.system_quantity_first sys_qty_first --STRY0661577
,
mcce.count_quantity_first first_count,
mcce.count_quantity_current current_count,
mcce.adjustment_quantity adjust,
mcce.adjustment_date --STRY0661577
,
ROUND (mcce.item_unit_cost, 2) unit_cost,
ROUND (mcce.adjustment_amount, 2) total_adj,
NULL details,
NULL missing_serials,
NULL excess_lpns,
CASE
WHEN mcch.maximum_auto_recounts >=
NVL (mcce.number_of_counts, 0)
THEN
‘Summary’
WHEN mcch.maximum_auto_recounts <
NVL (mcce.number_of_counts, 0)
THEN
‘Detail’
END
last_count_level,
CASE
WHEN mcce.outermost_lpn_id IS NULL
AND msik.serial_number_control_code != 5
THEN
‘None’
WHEN mcce.outermost_lpn_id IS NULL
AND msik.serial_number_control_code = 5
THEN
‘Serial’
ELSE
‘LPN’
END
detail_requirement,
ml.meaning status,
mtr.reason_name reason,
mtr.attribute2 reason_group,
(SELECT DISTINCT
NVL (mcce.reference_current,
mmt.transaction_reference)
FROM apps.mtl_material_transactions mmt
WHERE mmt.cycle_count_id =
mcce.cycle_count_entry_id
AND mmt.inventory_item_id =
mcce.inventory_item_id
AND mmt.organization_id =
mcch.organization_id --AND mmt.transaction_date >=
– mcce.creation_date
)
REFERENCE,
mcch.cycle_count_header_name cycle_count_name,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = mcch.created_by)
created_by,
mcch.creation_date created_date,
mcce.number_of_counts number_of_counts,
(SELECT user_name
FROM apps.fnd_user
WHERE employee_id =
mcce.counted_by_employee_id_first)
first_count_by,
(SELECT MIN (mcce1.count_date_first)
FROM apps.mtl_cycle_count_entries mcce1
WHERE mcce1.inventory_item_id =
mcce.inventory_item_id
AND mcce1.locator_id = mcce.locator_id
AND mcce1.cycle_count_header_id =
mcce.cycle_count_header_id)
first_count_date,
(SELECT user_name
FROM apps.fnd_user
WHERE employee_id =
mcce.counted_by_employee_id_current)
current_count_by,
(SELECT MIN (mcce1.count_date_current)
FROM apps.mtl_cycle_count_entries mcce1
WHERE mcce1.inventory_item_id =
mcce.inventory_item_id
AND mcce1.locator_id = mcce.locator_id
AND mcce1.cycle_count_header_id =
mcce.cycle_count_header_id)
current_count_date,
CASE
WHEN mcce.counted_by_employee_id_first <>
mcce.counted_by_employee_id_current
OR mcce.counted_by_employee_id_first <>
mcce.counted_by_employee_id_prior
OR mcce.counted_by_employee_id_prior <>
mcce.counted_by_employee_id_current
THEN
‘Y’
ELSE
‘N’
END
different_counters --STRY0661577
,
(SELECT user_name
FROM apps.fnd_user
WHERE employee_id = mcce.approver_employee_id)
approved_by,
CASE
WHEN mcce.entry_status_code <> 5 THEN NULL
ELSE mcce.approval_date
END
approved_date,
mp.attribute4 whse,
(SELECT COUNT (DISTINCT (mil2.segment1))
FROM apps.mtl_onhand_quantities_detail moqd,
apps.mtl_item_locations mil2
WHERE moqd.locator_id =
mil2.inventory_location_id
AND moqd.inventory_item_id =
mcce.inventory_item_id
AND moqd.organization_id =
mcce.organization_id)
num_onhand_locations,
(SELECT MAX (mcce_last_count.count_date_current)
FROM apps.mtl_cycle_count_entries mcce_last_count
WHERE mcce.inventory_item_id =
mcce_last_count.inventory_item_id
AND mcce.organization_id =
mcce_last_count.organization_id
AND mcce.subinventory =
mcce_last_count.subinventory
AND mcce.locator_id =
mcce_last_count.locator_id
AND mcce_last_count.entry_status_code = 5 – Completed
)
last_count,
SUBSTRB (msik.description, 1, 25) description,
mcch.attribute8,
mcce.cycle_count_header_id,
mcce.locator_id,
mcce.inventory_item_id,
msik.market_price,
mcce.count_quantity_prior previous_count,
(SELECT fu.user_name
FROM apps.fnd_user fu
WHERE fu.employee_id =
mcce.counted_by_employee_id_prior)
previous_count_by,
mcce.count_date_prior previous_count_date
FROM apps.mtl_system_items_kfv msik,
apps.mtl_item_locations mil,
apps.mfg_lookups ml,
apps.mtl_parameters mp,
apps.mtl_cycle_count_headers mcch,
apps.mtl_cycle_count_entries mcce,
apps.mtl_secondary_inventories msi,
apps.mtl_transaction_reasons mtr,
apps.wms_license_plate_numbers wlpn
WHERE 1 = 1
AND mcce.transaction_reason_id = mtr.reason_id(+)
AND mcce.inventory_item_id = msik.inventory_item_id
AND mcce.organization_id = msik.organization_id
AND mcce.locator_id = mil.inventory_location_id(+)
AND ml.lookup_type = ‘MTL_CC_ENTRY_STATUSES’
AND ml.lookup_code = mcce.entry_status_code
AND mcch.cycle_count_header_id =
mcce.cycle_count_header_id
AND mp.organization_id = mcch.organization_id
AND mcce.subinventory = msi.secondary_inventory_name
AND mcce.organization_id = msi.organization_id
AND mp.wms_enabled_flag = ‘Y’
AND mcce.outermost_lpn_id = wlpn.lpn_id(+)
– AND mp.attribute4 = ‘WPC’
) base
GROUP BY base.org,
base.subinventory,
base.item,
base.cost_group,
base.loc,
base.unit_cost,
base.details,
base.last_count_level,
base.detail_requirement,
base.status,
base.reason,
base.reason_group,
base.REFERENCE,
base.cycle_count_name,
base.created_by,
base.created_date,
base.number_of_counts,
base.first_count_by,
base.first_count_date,
base.current_count_by,
base.current_count_date,
base.different_counters,
base.approved_by,
base.whse,
base.num_onhand_locations,
base.last_count,
base.description,
base.attribute8,
base.cycle_count_header_id,
base.locator_id,
base.inventory_item_id,
base.market_price,
base.previous_count_by) grouped_data,
( SELECT ungrouped_lpns.cycle_count_header_id,
ungrouped_lpns.subinventory,
ungrouped_lpns.locator_id,
ungrouped_lpns.inventory_item_id,
SUM (ungrouped_lpns.first_count) first_count,
SUM (ungrouped_lpns.current_count) current_count
FROM ( SELECT wxcel_first.cycle_count_header_id,
wxcel_first.subinventory,
wxcel_first.locator_id,
wxcel_first.inventory_item_id,
SUM (NVL (wxcel_first.excess_lpn_count, 0))
first_count,
0 current_count
FROM apps.wwt_xxwms_cc_excess_lpn wxcel_first
WHERE wxcel_first.number_of_count = 1
GROUP BY wxcel_first.cycle_count_header_id,
wxcel_first.subinventory,
wxcel_first.locator_id,
wxcel_first.inventory_item_id
UNION ALL
SELECT wxcel_current.cycle_count_header_id,
wxcel_current.subinventory,
wxcel_current.locator_id,
wxcel_current.inventory_item_id,
0 first_count,
SUM (NVL (wxcel_current.excess_lpn_count, 0))
current_count
FROM apps.wwt_xxwms_cc_excess_lpn wxcel_current
WHERE wxcel_current.number_of_count =
(SELECT MAX (mcce.number_of_counts)
FROM apps.mtl_cycle_count_entries mcce
WHERE wxcel_current.cycle_count_header_id =
mcce.cycle_count_header_id
AND wxcel_current.subinventory =
mcce.subinventory
AND wxcel_current.locator_id =
mcce.locator_id
AND wxcel_current.inventory_item_id =
mcce.inventory_item_id)
GROUP BY wxcel_current.cycle_count_header_id,
wxcel_current.subinventory,
wxcel_current.locator_id,
wxcel_current.inventory_item_id) ungrouped_lpns
GROUP BY ungrouped_lpns.cycle_count_header_id,
ungrouped_lpns.subinventory,
ungrouped_lpns.locator_id,
ungrouped_lpns.inventory_item_id) excess_lpns
WHERE excess_lpns.cycle_count_header_id(+) =
grouped_data.cycle_count_header_id
AND excess_lpns.subinventory(+) = grouped_data.subinventory
AND excess_lpns.locator_id(+) = grouped_data.locator_id
AND excess_lpns.inventory_item_id(+) =
grouped_data.inventory_item_id
AND TRUNC(grouped_data.creation_date) = NVL(?,TRUNC(SYSDATE - 1))
UNION ALL --Below sql added for DFCT0019075
SELECT /+ ORDERED /
mp.organization_code org,
mmt.subinventory_code,
msik.concatenated_segments item,
ccg.cost_group,
milk.segment1 loc,
NULL sys_qty,
NULL first_count,
NULL current_count,
mmt.transaction_quantity adjust_qty,
NULL unit_cost,
NULL total_adj,
NULL details,
NULL missing_serials_lpns,
NULL excess_serials_lpns,
‘CC Transfer’ count_type,
NULL last_count_level,
NULL detail_requirement,
‘Completed’ status,
mtr.reason_name reason,
mtr.attribute2 reason_group,
mmt.transaction_reference reference_notes,
mcch.cycle_count_header_name cycle_count_name,
fu.user_name created_by,
mmt.transaction_date creation_date,
NULL number_of_counts,
NULL first_count_by,
NULL first_count_date,
NULL current_count_by,
NULL current_count_date,
NULL different_counters,
NULL approved_by,
NULL approved_date,
mp.attribute4 whse,
(SELECT COUNT (DISTINCT (mil2.segment1))
FROM apps.mtl_onhand_quantities_detail moqd,
apps.mtl_item_locations mil2
WHERE moqd.locator_id = mil2.inventory_location_id
AND moqd.inventory_item_id = mmt.inventory_item_id
AND moqd.organization_id = mmt.organization_id)
num_onhand_locations,
(SELECT MAX (mcce_last_count.count_date_current)
FROM apps.mtl_cycle_count_entries mcce_last_count
WHERE mmt.inventory_item_id =
mcce_last_count.inventory_item_id
AND mmt.organization_id = mcce_last_count.organization_id
AND mmt.subinventory_code = mcce_last_count.subinventory
AND mmt.locator_id = mcce_last_count.locator_id
AND mcce_last_count.entry_status_code = 5 – Completed
)
last_count,
SUBSTRB (msik.description, 1, 25) description,
mcch.attribute8 request_id,
mcch.cycle_count_header_id,
mmt.locator_id,
mmt.inventory_item_id item_id,
msik.market_price --CHG34568
,
NULL previous_count,
NULL previous_count_by,
NULL previous_count_date,
NULL zero_count_miss
FROM apps.mtl_material_transactions mmt,
apps.mtl_cycle_count_headers mcch,
apps.mtl_system_items_kfv msik,
apps.wwt_mtl_item_locations_kfv milk,
apps.mtl_transaction_reasons mtr,
apps.cst_cost_groups ccg,
apps.mtl_parameters mp,
apps.fnd_user fu
WHERE 1 = 1
AND mmt.transaction_source_id = mcch.cycle_count_header_id
AND mmt.inventory_item_id = msik.inventory_item_id
AND mmt.organization_id = msik.organization_id
AND mmt.locator_id = milk.inventory_location_id
AND mmt.reason_id = mtr.reason_id
AND mmt.cost_group_id = ccg.cost_group_id
AND mmt.transaction_type_id IN (SELECT transaction_type_id
FROM apps.mtl_transaction_types
WHERE transaction_type_name =
‘WWT Cycle Count Transfer’) --Added for ver 1.3
AND mmt.organization_id = mp.organization_id
AND mp.wms_enabled_flag = ‘Y’
AND mmt.created_by = fu.user_id
AND TRUNC(mmt.transaction_date) = NVL(?,TRUNC(SYSDATE - 1))
– AND mp.attribute4 = ‘WPC’
)
/ Formatted on 10/22/2019 8:22:12 AM (QP5 v5.267.14150.38573) */
CYCLE_COUNT_NAME
STATUS
,ITEM
LOC
SYS_QTY
,FIRST_COUNT
,CURRENT_COUNT
,ADJUST_QTY
, UNIT_COST
,TOTAL_COST
,LAST_COUNT
,DESCRIPTION
,CREATION_DATE
,COST_GROUP
, COUNT_TYPE
,REASON
,REASON_GROUP
,REFERENCE_NOTES
1 Like
Thanks for sharing the information.
Can you verify if the parameters in jdbc connection which is used by the service are same as the connection parameters configured when executing the same in DB client ?
Thanks,
Dipankar
So you mean to say there is no issue with the query right?
Hi Amruth,
In case of jdbc adapter custom query operation, the data/query which is passed to the service is used in the execution as is without any explicit manipulation . Now if there’s any error in the data/query, DB throws the error which in this case is what you are getting upon executing the service. So from this perspective I feel there might be some issue with the query itself or there are some identifier used in the query which is not valid for the db environment where it is pointing to.
In this scenario I would suggest to check if the query and connection parameters used in adapter service are same when executing it in the DB client where it is working.
Thanks,
Dipankar
Hi,
I can see your SQL is rather complex. Why don’t you write in a stored procedure and then the adapter service just called the stored procedure? Complex data manipulation should always be done on the database side. Keep the adapter service simple by making a call to the store procedure will help you to troubleshoot. Also if it’s done on database side, you can also could achieve better performance.
3 Likes
Hi All,
Thanks for ur help and suggestions.
My issue is resolved now and its beacause …there is a function in the query and the tables in that function need grant permissions. After running the grant command for the table my issue got resolved.
1 Like
Good to know that the issue was at the DB side and not in webMethods.
It would be better if you follow the approach suggested by Mike Ng for putting most of the logic of this complex query in the stored procedure and invoke the stored procedure from webMethods.