Home » RDBMS Server » Performance Tuning » Query is taking huge time to display the record (Oracle e-business Suite 12.1.3.)
| Query is taking huge time to display the record [message #604380] |
Mon, 30 December 2013 00:10  |
 |
ajit.jha31
Messages: 3
Registered: December 2013
|
Junior Member |
|
|
I have a custom OAF page in Oracle Apps.Over there we are trying to get value based on item no.
Whatever query is running behind that OAF page is attached along with execution plan.Trace file,TKPROF file is too big to load in this forum.That's why just loaded the particular query and their execution plan.
We are using Union All to combine the result-set of 43 different select statement.
This query used as Cursor in a Procedure.This procedure is displaying the data on the base of item id in OAF page.
Currently it is taking around 3-4 min to display the data.
Please help me out to tune this query...
|
|
|
|
|
|
| Re: Query is taking huge time to display the record [message #604382 is a reply to message #604381] |
Mon, 30 December 2013 00:48  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
The elapsed time is 48.15 seconds for the query and it returns 7 rows. You are saying it takes 3-4 minutes to display, so what else is happening?
The following index is associated with which table? Post the total count of rows of that table.
INDEX RANGE SCAN XXADAT_MTL_MATERIAL_TXN_N1
Post the result of the following :
select count(*) from OE_ORDER_LINES_ALL;
select count(*) from OE_ORDER_HEADERS_ALL;
Could you please explain why are you doing this?
AND OOLA1.LINE_ID != OOLA.LINE_ID
AND OOLA1.HEADER_ID = OOHA.HEADER_ID
[Updated on: Sat, 08 March 2014 13:50] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Query is taking huge time to display the record [message #604394 is a reply to message #604380] |
Mon, 30 December 2013 02:04 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
You cannot expect anyone to read such a huge mess of unformatted code, and I am surprised that your local coding standards permit it. On this occasion, I have formatted it for you. Please do this yourself in future.
Query is here
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (serial_number) QTY,
'SERV' TYPE,
MSN.current_organization_id
ORGANIZATION_ID
FROM mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE MSN.inventory_item_id = :B2
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND MSN.current_status = 3
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MMS.enabled_flag = 1
GROUP BY MSN.c_attribute1,
MSN.current_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (MOQ.transaction_quantity) QTY,
'SERV' TYPE,
MLN.organization_id
FROM mtl_lot_numbers MLN,
mtl_material_statuses MMS,
mtl_onhand_quantities_detail MOQ
WHERE MLN.inventory_item_id = :B2
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND MMS.enabled_flag = 1
AND MOQ.inventory_item_id = MLN.inventory_item_id
AND MOQ.organization_id = MLN.organization_id
AND MOQ.lot_number = MLN.lot_number
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND EXISTS (SELECT 1
FROM mtl_system_items_b MSIB
WHERE MSIB.inventory_item_id = MLN.inventory_item_id
AND MSIB.organization_id = MLN.organization_id
AND MSIB.lot_control_code != 1
AND MSIB.serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
MLN.organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (serial_number) QTY,
'UNSERV' TYPE,
MSN.current_organization_id
ORGANIZATION_ID
FROM mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE MSN.inventory_item_id = :B2
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND MSN.current_status = 3
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MMS.enabled_flag = 1
GROUP BY MSN.c_attribute1,
MSN.current_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (MOQ.transaction_quantity) QTY,
'UNSERV' TYPE,
MLN.organization_id
ORGANIZATION_ID
FROM mtl_lot_numbers MLN,
mtl_material_statuses MMS,
mtl_onhand_quantities_detail MOQ
WHERE MLN.inventory_item_id = :B2
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND MMS.enabled_flag = 1
AND MOQ.inventory_item_id = MLN.inventory_item_id
AND MOQ.organization_id = MLN.organization_id
AND MOQ.lot_number = MLN.lot_number
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND EXISTS (SELECT 1
FROM mtl_system_items_b MSIB
WHERE MSIB.inventory_item_id = MLN.inventory_item_id
AND MSIB.organization_id = MLN.organization_id
AND MSIB.lot_control_code != 1
AND MSIB.serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
MLN.organization_id
UNION ALL
SELECT owner_code,
owner,
SUM (qty) QTY,
TYPE,
organization_id
FROM (SELECT PHA.attribute11
OWNER_CODE
,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = PHA.attribute11
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE)
AND Nvl
(FFV.end_date_active, SYSDATE))
OWNER,
PLA.quantity - (SELECT Nvl (SUM (RT.quantity), 0)
FROM rcv_transactions RT
WHERE RT.po_line_id = PLA.po_line_id
AND RT.transaction_type = 'DELIVER'
AND NOT EXISTS(SELECT 1
FROM rcv_transactions
WHERE
po_line_id = RT.po_line_id
AND transaction_type =
'RETURN TO VENDOR'))
QTY,
'OPENPO'
TYPE,
PLLA.ship_to_organization_id
ORGANIZATION_ID
FROM po_headers_all PHA,
po_lines_all PLA,
po_line_locations_all PLLA
WHERE PLA.item_id = :B2
AND PLA.po_header_id = PHA.po_header_id
AND PHA.authorization_status = 'APPROVED'
AND Nvl (PHA.attribute11, '-999') =
Nvl (:B1, Nvl (PHA.attribute11,
'-999'))
AND Nvl (PHA.closed_code, 'OPEN') = 'OPEN'
AND PLLA.po_line_id = PLA.po_line_id
AND PLA.quantity - (SELECT Nvl (SUM (RT.quantity), 0)
FROM rcv_transactions RT
WHERE RT.po_line_id = PLA.po_line_id
AND RT.transaction_type = 'DELIVER'
AND NOT EXISTS(SELECT 1
FROM rcv_transactions
WHERE
po_line_id = RT.po_line_id
AND transaction_type =
'RETURN TO VENDOR')) > 0)
QR
GROUP BY owner_code,
owner,
TYPE,
organization_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT',
'ETIHAD_LINE_RECEIPT'
, 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER',
'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT',
'FSK_RECEIPT',
'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT',
'FSK_RECEIPT',
'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT',
'FSK_RECEIPT',
'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1,
'-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT',
'RETURNED',
'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
ahl_osp_orders_b AOO,
ahl_osp_order_lines AOOL,
mtl_serial_numbers MSN
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND MSN.serial_number = AOOL.serial_number
AND MSN.inventory_item_id = AOOL.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name = 'OSP_ORDER'
AND OOLA.line_category_code = 'RETURN'
AND AOO.oe_header_id = OOHA.header_id
AND AOOL.osp_order_id = AOO.osp_order_id
AND AOOL.oe_return_line_id = OOLA.line_id
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA1
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM oe_order_lines_all OOLA1,
mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND OOLA1.line_id = AOOL.oe_ship_line_id
AND MMT1.source_code = 'ORDER
ENTRY'
AND OOLA1.flow_status_code != 'CANCELLED')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
MSN.c_attribute1
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id
ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
ahl_osp_orders_b AOO,
ahl_osp_order_lines AOOL,
mtl_lot_numbers MLN
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND MLN.lot_number = AOOL.lot_number
AND MLN.inventory_item_id = AOOL.inventory_item_id
AND MLN.organization_id = AOOL.inventory_org_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = AOOL.inventory_item_id
AND organization_id = AOOL.inventory_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND AOOL.serial_number IS NULL
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name = 'OSP_ORDER'
AND OOLA.line_category_code = 'RETURN'
AND AOO.oe_header_id = OOHA.header_id
AND AOOL.osp_order_id = AOO.osp_order_id
AND AOOL.oe_return_line_id = OOLA.line_id
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA1
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN (
'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE',
'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM oe_order_lines_all OOLA1,
mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND OOLA1.line_id = AOOL.oe_ship_line_id
AND MMT1.source_code = 'ORDER
ENTRY'
AND OOLA1.flow_status_code != 'CANCELLED')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
MLN.c_attribute1
UNION ALL
SELECT XIP.participant OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = XIP.participant
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (XIP.quantity) QTY,
'FLOAT' TYPE,
XIP.org_id
ORGANIZATION_ID
FROM xxadat_inv_pool XIP
WHERE XIP.inventory_item_id = :B2
AND XIP.effective_date = (SELECT Max (effective_date)
FROM xxadat_inv_pool
WHERE
inventory_item_id = XIP.inventory_item_id
AND org_id = XIP.org_id
AND pool_type = XIP.pool_type)
AND XIP.pool_type = 'FLOAT'
AND Nvl (XIP.participant, '-999') = Nvl (:B1, Nvl (XIP.participant,
'-999'))
GROUP BY XIP.participant,
XIP.org_id
UNION ALL
SELECT XIP.participant OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = XIP.participant
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (XIP.quantity) QTY,
'LALLOC' TYPE,
XIP.org_id
ORGANIZATION_ID
FROM xxadat_inv_pool XIP
WHERE XIP.inventory_item_id = :B2
AND XIP.effective_date = (SELECT Max (effective_date)
FROM xxadat_inv_pool
WHERE
inventory_item_id = XIP.inventory_item_id
AND org_id = XIP.org_id
AND pool_type = XIP.pool_type)
AND XIP.pool_type = 'LALLOC'
AND Nvl (XIP.participant, '-999') = Nvl (:B1, Nvl (XIP.participant,
'-999'))
GROUP BY XIP.participant,
XIP.org_id
UNION ALL
SELECT :B1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = :B1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (MSS.safety_stock_quantity) QTY,
'SAFETYSTK' TYPE,
MSS.organization_id
ORGANIZATION_ID
FROM mtl_safety_stocks MSS
WHERE MSS.inventory_item_id = :B2
AND MSS.effectivity_date = (SELECT Max (effectivity_date)
FROM mtl_safety_stocks
WHERE
inventory_item_id = MSS.inventory_item_id
AND organization_id = MSS.organization_id)
GROUP BY :B1,
MSS.organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1,
'-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl
(FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id
ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1,
'-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.to_organization_id;
[EDITED by LF: applied [spoiler] tags]
[Updated on: Mon, 30 December 2013 05:59] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Query is taking huge time to display the record [message #604544 is a reply to message #604380] |
Wed, 01 January 2014 00:49 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. IMHO - you have an I/O problem accessing OE_ORDER_LINES_ALL table for the first time (time=18239891 and 1503 physical reads):
2829 2829 2829 TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (cr=6637 pr=1503 pw=0 time=18239891 us cost=10 size=205 card=5)
2880 2880 2880 INDEX RANGE SCAN OE_ORDER_LINES_N3 (cr=14 pr=0 pw=0 time=10314 us cost=3 size=0 card=10)(object id 119540)
From that moment all accesses to that table are executed in memory and don't use physical IOs.
Can you try following index:
CREATE INDEX ... ON OE_ORDER_LINES_ALL ( INVENTORY_ITEM_ID, LINE_CATEGORY_ID, FLOW_STATUS_CODE, HEADER_ID, SHIP_FROM_ORG_ID, ORDERED_QUANTITY ) COMPUTE STATISTICS ...
?
2. Use query subfactoring (WITH) to retrive the necessary data only once.
HTH.
|
|
|
|
| Re: Query is taking huge time to display the record [message #604834 is a reply to message #604380] |
Sat, 04 January 2014 06:59 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
For those seeking it, here is a formatted version of the query.
You can use this website for formatting queries. Works most of the time.
Query is here
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (serial_number) QTY,
'SERV' TYPE,
MSN.current_organization_id ORGANIZATION_ID
FROM mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE MSN.inventory_item_id = :B2
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND MSN.current_status = 3
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MMS.enabled_flag = 1
GROUP BY MSN.c_attribute1,
MSN.current_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (MOQ.transaction_quantity) QTY,
'SERV' TYPE,
MLN.organization_id
FROM mtl_lot_numbers MLN,
mtl_material_statuses MMS,
mtl_onhand_quantities_detail MOQ
WHERE MLN.inventory_item_id = :B2
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND MMS.enabled_flag = 1
AND MOQ.inventory_item_id = MLN.inventory_item_id
AND MOQ.organization_id = MLN.organization_id
AND MOQ.lot_number = MLN.lot_number
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND EXISTS (SELECT 1
FROM mtl_system_items_b MSIB
WHERE MSIB.inventory_item_id = MLN.inventory_item_id
AND MSIB.organization_id = MLN.organization_id
AND MSIB.lot_control_code != 1
AND MSIB.serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
MLN.organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (serial_number) QTY,
'UNSERV' TYPE,
MSN.current_organization_id ORGANIZATION_ID
FROM mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE MSN.inventory_item_id = :B2
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND MSN.current_status = 3
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MMS.enabled_flag = 1
GROUP BY MSN.c_attribute1,
MSN.current_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (MOQ.transaction_quantity) QTY,
'UNSERV' TYPE,
MLN.organization_id ORGANIZATION_ID
FROM mtl_lot_numbers MLN,
mtl_material_statuses MMS,
mtl_onhand_quantities_detail MOQ
WHERE MLN.inventory_item_id = :B2
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND MMS.enabled_flag = 1
AND MOQ.inventory_item_id = MLN.inventory_item_id
AND MOQ.organization_id = MLN.organization_id
AND MOQ.lot_number = MLN.lot_number
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND EXISTS (SELECT 1
FROM mtl_system_items_b MSIB
WHERE MSIB.inventory_item_id = MLN.inventory_item_id
AND MSIB.organization_id = MLN.organization_id
AND MSIB.lot_control_code != 1
AND MSIB.serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
MLN.organization_id
UNION ALL
SELECT owner_code,
owner,
SUM (qty) QTY,
TYPE,
organization_id
FROM (SELECT PHA.attribute11 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = PHA.attribute11
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
PLA.quantity - (SELECT Nvl (SUM (RT.quantity), 0)
FROM rcv_transactions RT
WHERE RT.po_line_id = PLA.po_line_id
AND RT.transaction_type = 'DELIVER'
AND NOT EXISTS(SELECT 1
FROM rcv_transactions
WHERE po_line_id = RT.po_line_id
AND transaction_type = 'RETURN TO VENDOR')) QTY,
'OPENPO' TYPE,
PLLA.ship_to_organization_id ORGANIZATION_ID
FROM po_headers_all PHA,
po_lines_all PLA,
po_line_locations_all PLLA
WHERE PLA.item_id = :B2
AND PLA.po_header_id = PHA.po_header_id
AND PHA.authorization_status = 'APPROVED'
AND Nvl (PHA.attribute11, '-999') = Nvl (:B1, Nvl (PHA.attribute11, '-999'))
AND Nvl (PHA.closed_code, 'OPEN') = 'OPEN'
AND PLLA.po_line_id = PLA.po_line_id
AND PLA.quantity - (SELECT Nvl (SUM (RT.quantity), 0)
FROM rcv_transactions RT
WHERE RT.po_line_id = PLA.po_line_id
AND RT.transaction_type = 'DELIVER'
AND NOT EXISTS(SELECT 1
FROM rcv_transactions
WHERE po_line_id = RT.po_line_id
AND transaction_type = 'RETURN TO VENDOR')) > 0) QR
GROUP BY owner_code,
owner,
TYPE,
organization_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code != 'CANCELLED'
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
oe_order_lines_all OOLA1
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name NOT IN ( 'OSP_ORDER', 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT',
'OUTSTATION_SALE', 'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND OOLA1.line_id != OOLA.line_id
AND OOLA1.header_id = OOHA.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND OOLA1.flow_status_code != 'CANCELLED'
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA2
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND MMT1.source_code = 'ORDER ENTRY')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
OOLA1.line_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT', 'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT', 'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT OOHA.attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = OOHA.attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)
AND ROWNUM = 1) OWNER,
SUM (OOLA.ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name IN ( 'THIRD_PARTY_FOC_RECEIPT', 'ETIHAD_LINE_RECEIPT', 'FSK_RECEIPT', 'OUTSTATION_SALE',
'MAXIMERLIN_OSP_ORDER', 'MAXIMERLIN_OSP_ORDER' )
AND OOLA.line_category_code = 'RETURN'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = OOLA.inventory_item_id
AND organization_id = OOLA.ship_from_org_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND Nvl (OOHA.attribute1, '-999') = Nvl (:B1, Nvl (OOHA.attribute1, '-999'))
AND OOLA.flow_status_code NOT IN ( 'CLOSED', 'AWAITING_FULFILLMENT', 'RETURNED', 'CANCELLED' )
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
ahl_osp_orders_b AOO,
ahl_osp_order_lines AOOL,
mtl_serial_numbers MSN
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND MSN.serial_number = AOOL.serial_number
AND MSN.inventory_item_id = AOOL.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name = 'OSP_ORDER'
AND OOLA.line_category_code = 'RETURN'
AND AOO.oe_header_id = OOHA.header_id
AND AOOL.osp_order_id = AOO.osp_order_id
AND AOOL.oe_return_line_id = OOLA.line_id
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA1
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM oe_order_lines_all OOLA1,
mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND OOLA1.line_id = AOOL.oe_ship_line_id
AND MMT1.source_code = 'ORDER ENTRY'
AND OOLA1.flow_status_code != 'CANCELLED')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
MSN.c_attribute1
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (ordered_quantity) QTY,
'OPENRMA' TYPE,
OOLA.ship_from_org_id ORGANIZATION_ID
FROM oe_order_headers_all OOHA,
oe_transaction_types_tl OTTL,
oe_order_lines_all OOLA,
ahl_osp_orders_b AOO,
ahl_osp_order_lines AOOL,
mtl_lot_numbers MLN
WHERE OOLA.inventory_item_id = :B2
AND OOHA.header_id = OOLA.header_id
AND OTTL.transaction_type_id = OOHA.order_type_id
AND MLN.lot_number = AOOL.lot_number
AND MLN.inventory_item_id = AOOL.inventory_item_id
AND MLN.organization_id = AOOL.inventory_org_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = AOOL.inventory_item_id
AND organization_id = AOOL.inventory_org_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND AOOL.serial_number IS NULL
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND OTTL.LANGUAGE = Userenv ('LANG')
AND OTTL.name = 'OSP_ORDER'
AND OOLA.line_category_code = 'RETURN'
AND AOO.oe_header_id = OOHA.header_id
AND AOOL.osp_order_id = AOO.osp_order_id
AND AOOL.oe_return_line_id = OOLA.line_id
AND EXISTS (SELECT 'X'
FROM oe_order_lines_all OOLA1
WHERE OOLA1.header_id = OOHA.header_id
AND OOLA1.flow_status_code NOT IN ( 'CLOSED', 'CANCELLED', 'INVOICE_INCOMPLETE', 'INVOICE_HOLD' ))
AND NOT EXISTS (SELECT 1
FROM rcv_transactions RCV,
mtl_material_transactions MMT1
WHERE RCV.oe_order_line_id = OOLA.line_id
AND RCV.transaction_type = 'DELIVER'
AND MMT1.source_code = 'RCV'
AND MMT1.source_line_id = RCV.transaction_id)
AND EXISTS (SELECT 1
FROM oe_order_lines_all OOLA1,
mtl_material_transactions MMT1
WHERE OOLA1.line_id = MMT1.source_line_id
AND OOLA1.line_id = AOOL.oe_ship_line_id
AND MMT1.source_code = 'ORDER ENTRY'
AND OOLA1.flow_status_code != 'CANCELLED')
GROUP BY OOHA.attribute1,
OOLA.ship_from_org_id,
MLN.c_attribute1
UNION ALL
SELECT XIP.participant OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = XIP.participant
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (XIP.quantity) QTY,
'FLOAT' TYPE,
XIP.org_id ORGANIZATION_ID
FROM xxadat_inv_pool XIP
WHERE XIP.inventory_item_id = :B2
AND XIP.effective_date = (SELECT Max (effective_date)
FROM xxadat_inv_pool
WHERE inventory_item_id = XIP.inventory_item_id
AND org_id = XIP.org_id
AND pool_type = XIP.pool_type)
AND XIP.pool_type = 'FLOAT'
AND Nvl (XIP.participant, '-999') = Nvl (:B1, Nvl (XIP.participant, '-999'))
GROUP BY XIP.participant,
XIP.org_id
UNION ALL
SELECT XIP.participant OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = XIP.participant
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (XIP.quantity) QTY,
'LALLOC' TYPE,
XIP.org_id ORGANIZATION_ID
FROM xxadat_inv_pool XIP
WHERE XIP.inventory_item_id = :B2
AND XIP.effective_date = (SELECT Max (effective_date)
FROM xxadat_inv_pool
WHERE inventory_item_id = XIP.inventory_item_id
AND org_id = XIP.org_id
AND pool_type = XIP.pool_type)
AND XIP.pool_type = 'LALLOC'
AND Nvl (XIP.participant, '-999') = Nvl (:B1, Nvl (XIP.participant, '-999'))
GROUP BY XIP.participant,
XIP.org_id
UNION ALL
SELECT :B1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = :B1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (MSS.safety_stock_quantity) QTY,
'SAFETYSTK' TYPE,
MSS.organization_id ORGANIZATION_ID
FROM mtl_safety_stocks MSS
WHERE MSS.inventory_item_id = :B2
AND MSS.effectivity_date = (SELECT Max (effectivity_date)
FROM mtl_safety_stocks
WHERE inventory_item_id = MSS.inventory_item_id
AND organization_id = MSS.organization_id)
GROUP BY :B1,
MSS.organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTFRMUNSERV' TYPE,
RSL.from_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.from_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.from_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Serviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INVENTORY'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = RSL.mmt_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code = 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MSN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MSN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
Count (MSN.serial_number) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_unit_transactions MUT,
mtl_serial_numbers MSN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MUT.transaction_id = MTLN.serial_transaction_id
AND MSN.serial_number = MUT.serial_number
AND MSN.inventory_item_id = MUT.inventory_item_id
AND Nvl (MSN.c_attribute1, '-999') = Nvl (:B1, Nvl (MSN.c_attribute1, '-999'))
AND MSN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code != 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MSN.c_attribute1,
RSL.to_organization_id
UNION ALL
SELECT MLN.c_attribute1 OWNER_CODE,
(SELECT FFV.description
FROM fnd_flex_value_sets FFVS,
fnd_flex_values_vl FFV
WHERE FFVS.flex_value_set_name = 'ADAT_INV_OWNERSHIP'
AND FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFV.enabled_flag = 'Y'
AND FFV.flex_value = MLN.c_attribute1
AND SYSDATE BETWEEN Nvl (FFV.start_date_active, SYSDATE) AND Nvl (FFV.end_date_active, SYSDATE)) OWNER,
SUM (RSL.quantity_shipped) QTY,
'INTTOUNSERV' TYPE,
RSL.to_organization_id ORGANIZATION_ID
FROM rcv_shipment_lines RSL,
rcv_shipment_headers RSH,
mtl_material_transactions MMT,
mtl_transaction_lot_numbers MTLN,
mtl_lot_numbers MLN,
mtl_material_statuses MMS,
po_requisition_lines_all PRL,
po_requisition_headers_all PRH,
oe_order_lines_all OOL,
oe_order_headers_all OOH
WHERE shipment_line_status_code = 'EXPECTED'
AND RSH.shipment_header_id = RSL.shipment_header_id
AND RSH.receipt_source_code = 'INTERNAL ORDER'
AND RSL.quantity_received = 0
AND RSL.item_id = :B2
AND MMT.transaction_id = RSL.mmt_transaction_id
AND MTLN.transaction_id = RSL.mmt_transaction_id
AND MLN.lot_number = MTLN.lot_number
AND MLN.inventory_item_id = MTLN.inventory_item_id
AND MLN.organization_id = MTLN.organization_id
AND Nvl (MLN.c_attribute1, '-999') = Nvl (:B1, Nvl (MLN.c_attribute1, '-999'))
AND MLN.status_id = MMS.status_id
AND MMS.status_code = 'Unserviceable'
AND OOL.line_id = MMT.source_line_id
AND OOH.header_id = OOL.header_id
AND EXISTS (SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = RSL.item_id
AND organization_id = RSL.to_organization_id
AND lot_control_code != 1
AND serial_number_control_code = 1)
AND PRL.requisition_line_id = RSL.requisition_line_id
AND PRH.requisition_header_id = PRL.requisition_header_id
GROUP BY MLN.c_attribute1,
RSL.to_organization_id
[EDITED by LF: applied [spoiler] tags]
[Updated on: Sat, 04 January 2014 11:59] by Moderator Report message to a moderator |
|
|
|
| Re: Query is taking huge time to display the record [message #604835 is a reply to message #604834] |
Sat, 04 January 2014 07:20 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
In order to solve a problem like this, isolate components to determine what is really happening and where time is going. For example, given the query above, you could do this from SQLPLUS.
set timing on
create table temp1
nologging
as
select ...
/
drop table temp1
/
create table temp1
nologging
as
select ...
/
The timed result will give you a wall clock on how long it takes to execute. You may find a large percentage of time may be in parsing so we execute the query twice in a row.
Notice how executing this query twice does two things:
1. it shows you how long the query itself takes (more or less) without dealing with network I/O etc.
2. it shows you how long the parsing takes and what the disk caching might mean to the query.
If you find that the query is taking too long (only you know what too long is) then you can begin to address why. Use query decomposition and reconstruction for a case like this to isolate each individual sub-query to see how long it takes to execute. You may get lucky and find one that is a problem. Do this by decomposing the big query into a set of individual sub-queries and test each sub-query. If that does not yield an answer then incrementally add one sub-query at a time and test each new reconstructed version to see how long it takes, until you have made your way back to the original query. By doing this you then know how much time is being spent in each sub-query and how long parsing takes for each incremental addition. KEEP EXCELLENT NOTES. You don't want to do all this work and then lose data you should have captured. An Excel sheet for the timings with a note is a good idea.
Yes this will take a while, a day probably, but this is tuning. In the end as was noted, you may find any of the following:
1. it is not the query at all as most of the time from your app is expended elsewhere besides the query
2. there may be a specific sub-query that is taking too long
3. parsing may be the culprit due to the large query size and many object references
4. something else
Point is, you will know more tomorrow than you know today. Do not assume the query is the problem. Figure out if the query is the problem or if it is somewhere else. Once you have determined if the query is actually the problem, then you can look at how to tune it. Tuning this SQL is at this moment premature.
Kevin
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 11:23:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
