Sales order line status query in Oracle apps
Oracle EBS query to find sales order line status as seen in frontend
Flow status code meaning oe_order_lines_all
--Sales order line status query
SELECT CASE
WHEN flow_status_code <> 'AWAITING_SHIPPING'
AND flow_status_code <> 'PRODUCTION_COMPLETE'
AND flow_status_code <> 'PICKED'
AND flow_status_code <> 'PICKED_PARTIAL'
AND flow_status_code <> 'PO_RECEIVED'
THEN
(SELECT meaning
FROM apps.fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = flow_status_code
AND LANGUAGE = USERENV ('LANG')
AND VIEW_APPLICATION_ID = 660)
ELSE
(SELECT NVL (
(SELECT meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED'
AND LANGUAGE = USERENV ('LANG')
AND VIEW_APPLICATION_ID = 660
AND (SELECT SUM (
DECODE (released_status,
'Y', 1,
'C', 1,
0))
FROM wsh_delivery_details
WHERE source_line_id =
oeol.line_id
AND source_code = 'OE'
AND released_status <> 'D') =
(SELECT SUM (1)
FROM wsh_delivery_details
WHERE source_line_id =
oeol.line_id
AND source_code = 'OE'
AND released_status <> 'D')
UNION
SELECT meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED_PARTIAL'
AND LANGUAGE = USERENV ('LANG')
AND VIEW_APPLICATION_ID = 660
AND (SELECT SUM (
DECODE (released_status,
'Y', 1,
'C', 1,
0))
FROM wsh_delivery_details
WHERE source_line_id = oeol.line_id
AND source_code = 'OE'
AND released_status <> 'D') <
(SELECT SUM (1)
FROM wsh_delivery_details
WHERE source_line_id =
oeol.line_id
AND source_code = 'OE'
AND released_status <> 'D')
AND (SELECT SUM (
DECODE (released_status,
'Y', 1,
'C', 1,
0))
FROM wsh_delivery_details
WHERE source_line_id = oeol.line_id
AND source_code = 'OE'
AND released_status <> 'D') <> 0),
(SELECT meaning
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = flow_status_code
AND LANGUAGE = USERENV ('LANG')
AND VIEW_APPLICATION_ID = 660))
FROM DUAL)
END
line_status
FROM oe_order_lines_all oeol
WHERE header_id = 1234
Related posts:
- Sales order line status query in Oracle apps
- Query to get price list details in Oracle EBS
- Query to find all APIs of Oracle Apps modules
- Oracle R12 shipping status query
- Query to get customer tax registration details in R12
- Query to get Sales order and lines details in Oracle Apps
- Query to find serial number material transactions details
- Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
- Query to find Sales Orders that are shipped but not invoiced
- Query to get Customer Bank Account Details
- AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
- Query to find shipset and fulfilment set