Sales Order Related Scripts

 

--Line Ids Associated With This Order Number
SELECT LIN.LINE_ID LINE_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE_NUM,
  ITM.SEGMENT1 ITEM,
  LIN.ORDERED_QUANTITY ORD_QTY,
  LIN.LINE_CATEGORY_CODE LINE_CTG,
  LIN.ARRIVAL_SET_ID ARRIV_SET_ID,
  LIN.SHIP_SET_ID SHIP_SET_ID,
  LIN.LINE_SET_ID LINE_SET_ID
FROM OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE LIN.HEADER_ID       = &SalesOrder_Header_ID
AND LIN.SHIP_FROM_ORG_ID  = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID(+)
AND LIN.OPTION_NUMBER    IS NULL
AND LIN.ITEM_TYPE_CODE   <> 'INCLUDED'
ORDER BY NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SERVICE_REFERENCE_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--OE_SETS (set)  
 SELECT ST1.SET_ID SET_ID,
  ST1.SET_NAME SET_NAME,
  ST1.SET_TYPE SET_TYPE,
  ST1.HEADER_ID HEADER_ID,
  LST.LINE_ID LINE_ID,
  ST1.INVENTORY_ITEM_ID ITEM_ID,
  ST1.ORDERED_QUANTITY_UOM UOM,
  ST1.LINE_TYPE_ID LINE_TYPE_ID,
  NVL(LST.SYSTEM_REQUIRED_FLAG,'N') SYS_REQD,
  ST1.SET_STATUS STATUS,
  TO_CHAR(ST1.SCHEDULE_SHIP_DATE,'DD-MON-RR HH24:MI:SS') SCH_SHP_DATE,
  TO_CHAR(ST1.SCHEDULE_ARRIVAL_DATE,'DD-MON-RR HH24:MI:SS') SCH_ARV_DATE,
  ST1.SHIP_FROM_ORG_ID SHIP_FROM,
  ST1.SHIP_TO_ORG_ID SHIP_TO_ID,
  ST1.SHIPMENT_PRIORITY_CODE SHIP_PRIORITY,
  ST1.FREIGHT_CARRIER_CODE CARRIER,
  ST1.SHIPPING_METHOD_CODE SHIP_METHOD,
  ST1.SHIP_TOLERANCE_ABOVE STA,
  ST1.SHIP_TOLERANCE_BELOW STB
FROM OE_SETS ST1,
  OE_LINE_SETS LST
WHERE ST1.SET_ID  = LST.SET_ID(+)
AND ST1.HEADER_ID = &SalesOrder_Header_ID;  

--OE_ORDER_HEADERS (ord)
 SELECT DISTINCT ORD.HEADER_ID HEADER_ID,
  ORD.ORDER_NUMBER ORD_NUM,
  ORD.ORDER_TYPE_ID TYPE_ID,
  TYP.NAME TYPE_NAME,
  (SELECT WFA1.DISPLAY_NAME
  FROM WF_ITEM_ACTIVITY_STATUSES WFS,
    WF_PROCESS_ACTIVITIES WFP,
    WF_ACTIVITIES_VL WFA,
    WF_ACTIVITIES_VL WFA1
  WHERE WFS.ITEM_TYPE        = 'OEOH'
  AND WFS.item_key           = &SalesOrder_Header_ID
  AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID(+)
  AND WFP.PROCESS_NAME       = 'ROOT'
  AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
  AND WFP.PROCESS_NAME       = WFA.NAME
  AND WFP.PROCESS_VERSION    = WFA.VERSION
  AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
  AND WFP.ACTIVITY_NAME      = WFA1.NAME
  AND WFA1.VERSION           =
    (SELECT NVL(MAX(VERSION),-1)
    FROM WF_ACTIVITIES WF2
    WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
    AND WF2.NAME        = WFP.ACTIVITY_NAME
    )
  ) FLOW_PROCESS,
  ORD.SOLD_TO_ORG_ID CUST_ID,
  SUBSTR(CUS.CUSTOMER_NAME,1,20) CUST_NAME,
  ORD.SHIP_TO_ORG_ID SHIP_ID,
  SUBSTR(SHP.NAME,1,12) SHIP_TO,
  ORD.INVOICE_TO_ORG_ID BILL_ID,
  TO_CHAR(ORD.ORDERED_DATE,'DD-MON-RR HH24:MI:SS') ORDERED_DATE,
  NVL(ORD.BOOKED_FLAG,'N') BK,
  NVL(ORD.OPEN_FLAG,'N') OP,
  NVL(ORD.CANCELLED_FLAG,'N') CN,
  NVL(ORD.PARTIAL_SHIPMENTS_ALLOWED,'Y') SP,
  ORD.SHIP_FROM_ORG_ID WH_ID,
  PAR.ORGANIZATION_CODE ORG,
  ORD.FLOW_STATUS_CODE FLOW_CODE,
  ORD.ORDER_CATEGORY_CODE CAT,
  ORD.PRICE_LIST_ID PL_ID,
  PRC.NAME PL_NAME,
  ORD.CUST_PO_NUMBER PO_NUMBER,
  ORD.SHIP_TOLERANCE_ABOVE STA,
  ORD.SHIP_TOLERANCE_BELOW STB,
  ORD.org_id ou,
  ORD.transactional_curr_code curr,
  ORD.agreement_id agr_id,
  NVL(ORD.upgraded_flag,'N') upg_flag,
  ORD.source_document_id sdi,
  ORD.orig_sys_document_ref osdr,
  ORD.blanket_number bn,
  ORD.first_ack_code fac,
  ORD.first_ack_date fad,
  ORD.last_ack_code lac,
  ORD.last_ack_date lad,
  ORD.xml_message_id xmi
FROM OE_ORDER_HEADERS ORD,
  MTL_PARAMETERS PAR,
  OE_TRANSACTION_TYPES_V TYP,
  AR_CUSTOMERS CUS,
  OE_SHIP_TO_ORGS_V SHP,
  QP_LIST_HEADERS_V PRC
WHERE ORD.HEADER_ID      = &SalesOrder_Header_ID
AND ORD.SHIP_FROM_ORG_ID = PAR.ORGANIZATION_ID(+)
AND ORD.ORDER_TYPE_ID    = TYP.TRANSACTION_TYPE_ID
AND SHP.SITE_USE_ID      = ORD.SHIP_TO_ORG_ID
AND ORD.SOLD_TO_ORG_ID   = CUS.CUSTOMER_ID
AND ORD.PRICE_LIST_ID    = PRC.LIST_HEADER_ID;


--Workflow Order Item Information
SELECT TO_CHAR(ool.line_number)
  || DECODE(ool.shipment_number, NULL, NULL, '.'
  || TO_CHAR(ool.shipment_number))
  || DECODE(ool.option_number, NULL, NULL, '.'
  || TO_CHAR(ool.option_number))
  || DECODE(ool.component_number, NULL, NULL, DECODE(ool.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(ool.component_number))
  || DECODE(ool.service_number,NULL,NULL, DECODE(ool.component_number, NULL, '.' , NULL)
  || DECODE(ool.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(ool.service_number)) LINE_NUM,
  ool.line_id LINE_ID,
  wi.parent_item_type PRT_ITEM_TYPE,
  wi.parent_item_key PRT_ITEM_KEY,
  TO_CHAR(wi.begin_date, 'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
  TO_CHAR(wi.end_date, 'DD-MON-RR HH24:MI:SS') END_DATE ,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key , 'RESPONSIBILITY_ID') RESP_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'ORG_ID') ORG_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'APPLICATION_ID') APPL_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'USER_ID') USER_ID,
  wi.root_activity ROOT_ACTIVITY,
  wi.root_activity_version ROOT_ACTIVITY_VERSION
FROM wf_items wi,
  oe_order_lines ool
WHERE wi.item_type = 'OEOL'
AND wi.item_key    = TO_CHAR(ool.line_id)
AND ool.header_id  = &SalesOrder_Header_ID
AND NVL( :2 ,0)   IN (0,ool.LINE_ID, ool.TOP_MODEL_LINE_ID, ool.ATO_LINE_ID, ool.LINK_TO_LINE_ID, ool.SERVICE_REFERENCE_LINE_ID);

--Workflow Order Status
SELECT WFA.DISPLAY_NAME PROCESS_NAME ,
  WFA1.DISPLAY_NAME ACTIVITY_NAME ,
  WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT_CODE,
  LKP.MEANING ,
  WFS.NOTIFICATION_ID ,
  WFP.PROCESS_NAME,
  WFP.ACTIVITY_NAME,
  TO_CHAR(WFS.BEGIN_DATE,'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
  TO_CHAR(WFS.END_DATE,'DD-MON-RR HH24:MI:SS') END_DATE ,
  WFS.ERROR_NAME
FROM WF_ITEM_ACTIVITY_STATUSES WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA,
  WF_ACTIVITIES_VL WFA1,
  WF_LOOKUPS LKP
WHERE WFS.ITEM_TYPE        = 'OEOH'
AND WFS.item_key           = &SalesOrder_Header_ID
AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME       = WFA.NAME
AND WFP.PROCESS_VERSION    = WFA.VERSION
AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
AND WFP.ACTIVITY_NAME      = WFA1.NAME
AND WFA1.VERSION           =
  (SELECT MAX(VERSION)
  FROM WF_ACTIVITIES WF2
  WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
  AND WF2.NAME        = WFP.ACTIVITY_NAME
  )
AND LKP.LOOKUP_TYPE = 'WFENG_STATUS'
AND LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS
ORDER BY WFS.ITEM_KEY,
  WFS.BEGIN_DATE,
  EXECUTION_TIME;
 
--WORKFLOW_ORDER_NOTIFICATION_INFORMATION (wfn)
 SELECT wfs.item_type ITEM_TYPE,
  WFN.NOTIFICATION_ID NOTIF_ID,
  WFN.TO_USER TO_USER,
  WFN.ORIGINAL_RECIPIENT ORIG_RECIP,
  WFN.RECIPIENT_ROLE RECIP_ROLE,
  WFN.MAIL_STATUS MAIL_STAT,
  WFN.MESSAGE_NAME MESSAGE_NAME,
  WFN.STATUS STATUS,
  WFN.SUBJECT SUBJECT,
  DECODE(wfs.error_message, NULL, NULL, 'Error Message: '
  ||wfs.error_message
  ||fnd_global.local_chr(10))
  || DECODE(wfs.error_stack, NULL, NULL, fnd_global.local_chr(10)
  ||'Error Stack: '
  ||wfs.error_stack) ERR_MSG
FROM WF_ITEM_ACTIVITY_STATUSES WFS,
  WF_NOTIFICATIONS WFN
WHERE WFS.ITEM_TYPE      = 'OEOH'
AND WFS.item_key         = &SalesOrder_Header_ID
AND WFS.NOTIFICATION_ID IS NOT NULL
AND WFN.NOTIFICATION_ID  = WFS.NOTIFICATION_ID
ORDER BY WFS.ITEM_KEY,
  WFS.BEGIN_DATE,
  EXECUTION_TIME;
 
--WORKFLOW_ORDER_SKIP_INFORMATION (wfskip)
SELECT WFS.HEADER_ID HEADER_ID,
  WFA.DISPLAY_NAME DISPLAY_NAME,
  TO_CHAR(WFS.CREATION_DATE,'DD-MON-RR HH24:MI:SS') CRE_DATE,
  WFS.USER_ID USER_ID,
  WFS.RESPONSIBILITY_ID RESPONSIBILITY_ID,
  WFS.APPLICATION_ID APPLICATION_ID
FROM ONT_WF_SKIP_LOG WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA
WHERE WFS.LINE_ID        IS NULL
AND WFS.HEADER_ID         = &SalesOrder_Header_ID
AND WFS.ACTIVITY_ID       = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME      = WFA.NAME
AND WFP.PROCESS_VERSION   = WFA.VERSION
ORDER BY WFS.HEADER_ID,
  WFS.CREATION_DATE;
 
--Workflow Order Level Errors
SELECT WFA.DISPLAY_NAME ,
  WFA1.DISPLAY_NAME ,
  WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) ,
  LKP.MEANING ,
  WFS.ERROR_NAME ,
  WFS.ERROR_MESSAGE ,
  WFS.ERROR_STACK
FROM WF_ITEM_ACTIVITY_STATUSES WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA,
  WF_ACTIVITIES_VL WFA1,
  WF_LOOKUPS LKP
WHERE WFS.ITEM_TYPE        = 'OEOH'
AND WFS.item_key           = &SalesOrder_Header_ID
AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME       = WFA.NAME
AND WFP.PROCESS_VERSION    = WFA.VERSION
AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
AND WFP.ACTIVITY_NAME      = WFA1.NAME
AND WFA1.VERSION           =
  (SELECT MAX(VERSION)
  FROM WF_ACTIVITIES WF2
  WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
  AND WF2.NAME        = WFP.ACTIVITY_NAME
  )
AND LKP.LOOKUP_TYPE     = 'WFENG_STATUS'
AND LKP.LOOKUP_CODE     = WFS.ACTIVITY_STATUS
AND WFS.ACTIVITY_STATUS = 'ERROR'
ORDER BY WFS.ITEM_KEY,
  WFS.BEGIN_DATE,
  EXECUTION_TIME;
 
--Workflow Activity Status For Order Error Process
 SELECT WFS.ITEM_TYPE
  || '-'
  || WFS.ITEM_KEY ERR_TYPE_KEY,
  WFA.DISPLAY_NAME ERR_PROCESS_NAME,
  WFA1.DISPLAY_NAME ERR_ACTIVITY_NAME,
  WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT,
  LKP.MEANING ACT_STATUS,
  WFS.NOTIFICATION_ID NOTIF_ID,
  WFS.ASSIGNED_USER ASGND_USER,
  TO_CHAR(WFS.BEGIN_DATE,'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
  TO_CHAR(WFS.END_DATE,'DD-MON-RR HH24:MI:SS') END_DATE
FROM WF_ITEM_ACTIVITY_STATUSES WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA,
  WF_ACTIVITIES_VL WFA1,
  WF_LOOKUPS LKP,
  WF_ITEMS WFI
WHERE WFS.ITEM_TYPE        = WFI.ITEM_TYPE
AND WFS.item_key           = WFI.ITEM_KEY
AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME       = WFA.NAME
AND WFP.PROCESS_VERSION    = WFA.VERSION
AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
AND WFP.ACTIVITY_NAME      = WFA1.NAME
AND WFA1.VERSION           =
  (SELECT MAX(VERSION)
  FROM WF_ACTIVITIES WF2
  WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
  AND WF2.NAME        = WFP.ACTIVITY_NAME
  )
AND LKP.LOOKUP_TYPE      = 'WFENG_STATUS'
AND LKP.LOOKUP_CODE      = WFS.ACTIVITY_STATUS
AND WFI.PARENT_ITEM_TYPE = 'OEOH'
AND WFI.PARENT_ITEM_KEY  = &SalesOrder_Header_ID
AND WFI.ITEM_TYPE       IN
  (SELECT WFAE.ERROR_ITEM_TYPE
  FROM WF_ITEM_ACTIVITY_STATUSES WFSE,
    WF_PROCESS_ACTIVITIES WFPE,
    WF_ACTIVITIES_VL WFAE,
    WF_ACTIVITIES_VL WFA1E
  WHERE WFSE.ITEM_TYPE        = 'OEOH'
  AND WFSE.ITEM_KEY           = &SalesOrder_Header_ID
  AND WFSE.PROCESS_ACTIVITY   = WFPE.INSTANCE_ID
  AND WFPE.PROCESS_ITEM_TYPE  = WFAE.ITEM_TYPE
  AND WFPE.PROCESS_NAME       = WFAE.NAME
  AND WFPE.PROCESS_VERSION    = WFAE.VERSION
  AND WFPE.ACTIVITY_ITEM_TYPE = WFA1E.ITEM_TYPE
  AND WFPE.ACTIVITY_NAME      = WFA1E.NAME
  AND WFA1E.VERSION           =
    (SELECT MAX(VERSION)
    FROM WF_ACTIVITIES WF2E
    WHERE WF2E.ITEM_TYPE = WFPE.ACTIVITY_ITEM_TYPE
    AND WF2E.NAME        = WFPE.ACTIVITY_NAME
    )
  AND WFSE.ACTIVITY_STATUS = 'ERROR'
  )
ORDER BY WFS.ITEM_KEY,
  WFS.BEGIN_DATE,
  EXECUTION_TIME;
 
--Generic Holds (Order and/or Lines)
SELECT HDF.HOLD_ID ,
  HDF.NAME ,
  HDF.TYPE_CODE ,
  HDF.ITEM_TYPE ,
  HDF.ACTIVITY_NAME ,
  HLD.ORDER_HOLD_ID ,
  HLD.HOLD_SOURCE_ID ,
  HLD.HOLD_RELEASE_ID ,
  HLD.HEADER_ID ,
  HLD.RELEASED_FLAG ,
  HSR.RELEASED_FLAG ,
  HRL.RELEASE_REASON_CODE ,
  DECODE(HSR.HOLD_ENTITY_CODE, 'B','Bill To', 'C','Customer', 'I','Item', 'O','Order', 'S','Ship To', 'W','Workflow', HSR.HOLD_ENTITY_CODE) ENTITY_CODE,
  HSR.HOLD_ENTITY_ID ENTITY_ID,
  DECODE(HSR.HOLD_ENTITY_CODE2, 'B','Bill To', 'C','Customer', 'I','Item', 'O','Order', 'S','Ship To', 'W','Workflow', HSR.HOLD_ENTITY_CODE2) ENTITY_CODE2 ,
  HSR.HOLD_ENTITY_ID2 ENTITY_ID2 ,
  HLD.created_by HELD_BY,
  TO_CHAR(HLD.creation_date,'DD-MON-RR HH24:MI:SS') HELD_DATE,
  TO_CHAR(HSR.HOLD_UNTIL_DATE,'DD-MON-RR HH24:MI:SS') HOLD_UNTIL,
  NVL(HDF.hold_included_items_flag, 'N') HOLD_INCLUDED_ITEMS
FROM OE_ORDER_HOLDS_ALL HLD,
  OE_HOLD_SOURCES_ALL HSR,
  OE_HOLD_DEFINITIONS HDF,
  OE_HOLD_RELEASES HRL
WHERE HLD.HEADER_ID     = &SalesOrder_Header_ID
AND HLD.HOLD_SOURCE_ID  = HSR.HOLD_SOURCE_ID
AND HSR.HOLD_ID         = HDF.HOLD_ID
AND HLD.HOLD_RELEASE_ID = HRL.HOLD_RELEASE_ID(+)
AND HLD.LINE_ID        IS NULL;

--Header Processing Messages
 SELECT DISTINCT MSG.header_id HEADER_ID,
  DECODE(MSG.MESSAGE_SOURCE_CODE, 'U','U=On-Line(UI)', 'C','C=Conc Process', 'W','W=Workflow', MSG.MESSAGE_SOURCE_CODE) MSG_SOURCE,
  MSG.PROCESS_ACTIVITY PROCESS_ACTIVITY,
  MSG.request_id REQUEST_ID,
  MST.message_text MESSAGE_TEXT
FROM oe_processing_msgs_vl MSG,
  oe_processing_msgs_tl MST,
  fnd_languages FLA
WHERE MSG.header_id                                 = &SalesOrder_Header_ID
AND msg.transaction_id                              = mst.transaction_id
AND MST.LANGUAGE                                    = FLA.LANGUAGE_CODE
AND FLA.INSTALLED_FLAG                              = 'B'
AND DECODE(MSG.LINE_ID,9.99E+125,NULL,MSG.LINE_ID) IS NULL;

--APPLIED_HEADER_PRICE_ADJUSTMENTS (adj)
 SELECT ADJ.PRICE_ADJUSTMENT_ID PRC_ADJ_ID,
  ADJ.LIST_HEADER_ID LST_HD_ID,
  ADJ.LIST_LINE_ID LST_LN_ID,
  ADJ.LIST_LINE_NO LIST_LN_NO,
  ADJ.MODIFIER_LEVEL_CODE MOD_LVL,
  ADJ.LIST_LINE_TYPE_CODE LIST_TYPE_CODE,
  ADJ.CHARGE_TYPE_CODE CHG_TY_CD,
  ADJ.ARITHMETIC_OPERATOR ARITH_OP,
  ADJ.OPERAND_PER_PQTY OP_PER_QTY,
  ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJ_AMT_PQ,
  ADJ.OPERAND OPERAND,
  ADJ.ADJUSTED_AMOUNT ADJ_AMT,
  ADJ.CREDIT_OR_CHARGE_FLAG CD,
  ADJ.AUTOMATIC_FLAG AF,
  ADJ.PRINT_ON_INVOICE_FLAG PI,
  ADJ.ACCRUAL_FLAG AC,
  ADJ.INVOICED_FLAG IF,
  ADJ.ESTIMATED_FLAG EF,
  ADJ.UPDATE_ALLOWED UA,
  ADJ.UPDATED_FLAG UF,
  ADJ.APPLIED_FLAG AP,
  ADJ.LOCK_CONTROL LK,
  ADJ.PERCENT PERC,
  ADJ.COST_ID COST_ID,
  ADJ.TAX_CODE TAX_CODE,
  ADJ.PRICING_PHASE_ID PP
FROM OE_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.HEADER_ID  = &SalesOrder_Header_ID
AND ADJ.LINE_ID     IS NULL
AND ADJ.APPLIED_FLAG = 'Y'
ORDER BY LIST_TYPE_CODE;

--UN-APPLIED_HEADER_PRICE_ADJUSTMENTS (adj)
SELECT ADJ.PRICE_ADJUSTMENT_ID PRC_ADJ_ID,
  ADJ.LIST_HEADER_ID LST_HD_ID,
  ADJ.LIST_LINE_ID LST_LN_ID,
  ADJ.LIST_LINE_NO LIST_LN_NO,
  ADJ.MODIFIER_LEVEL_CODE MOD_LVL,
  ADJ.LIST_LINE_TYPE_CODE LIST_TYPE_CODE,
  ADJ.CHARGE_TYPE_CODE CHG_TY_CD,
  ADJ.ARITHMETIC_OPERATOR ARITH_OP,
  ADJ.OPERAND_PER_PQTY OP_PER_QTY,
  ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJ_AMT_PQ,
  ADJ.OPERAND OPERAND,
  ADJ.ADJUSTED_AMOUNT ADJ_AMT,
  ADJ.CREDIT_OR_CHARGE_FLAG CD,
  ADJ.AUTOMATIC_FLAG AF,
  ADJ.PRINT_ON_INVOICE_FLAG PI,
  ADJ.ACCRUAL_FLAG AC,
  ADJ.INVOICED_FLAG IF,
  ADJ.ESTIMATED_FLAG EF,
  ADJ.UPDATE_ALLOWED UA,
  ADJ.UPDATED_FLAG UF,
  ADJ.APPLIED_FLAG AP,
  ADJ.LOCK_CONTROL LK,
  ADJ.PERCENT PERC,
  ADJ.COST_ID COST_ID,
  ADJ.TAX_CODE TAX_CODE,
  ADJ.PRICING_PHASE_ID PP
FROM OE_PRICE_ADJUSTMENTS ADJ
WHERE ADJ.HEADER_ID           = &SalesOrder_Header_ID
AND ADJ.LINE_ID              IS NULL
AND NVL(ADJ.APPLIED_FLAG,'N') = 'N'
ORDER BY LIST_TYPE_CODE;

--MTL_SALES_ORDERS (mso)
SELECT MSO.SALES_ORDER_ID ,
  ORD.HEADER_ID ,
  ORD.ORDER_NUMBER ,
  TYP.NAME ,
  MSO.SEGMENT3
FROM MTL_SALES_ORDERS MSO,
  OE_ORDER_HEADERS ORD,
  OE_TRANSACTION_TYPES_VL TYP
WHERE ORD.ORDER_TYPE_ID       = TYP.TRANSACTION_TYPE_ID
AND TO_CHAR(ORD.ORDER_NUMBER) = MSO.SEGMENT1
AND TYP.NAME                  = MSO.SEGMENT2
AND ORD.HEADER_ID             = &SalesOrder_Header_ID
ORDER BY ORD.HEADER_ID;
 
--OE_ORDER_LINES (lin)   
 SELECT TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) line_num,
  LIN.LINE_ID LINE_ID,
  LIN.INVENTORY_ITEM_ID ITEM_ID,
  ITM.SEGMENT1 ITEM,
  SUBSTR(LIN.ORDERED_ITEM,1,17) Ordered_Item,
  lin.ordered_item_id Order_itm_id,
  (SELECT WFA.DISPLAY_NAME
  FROM WF_ITEM_ACTIVITY_STATUSES WFS,
    WF_PROCESS_ACTIVITIES WFP,
    WF_ACTIVITIES_VL WFA
  WHERE WFS.ITEM_TYPE        = 'OEOL'
  AND WFS.ITEM_KEY           = TO_CHAR(LIN.LINE_ID)
  AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
  AND WFP.PROCESS_NAME       = 'ROOT'
  AND WFP.ACTIVITY_ITEM_TYPE = WFA.ITEM_TYPE
  AND WFP.ACTIVITY_NAME      = WFA.NAME
  AND NVL(WFA.VERSION,       -1)    =
    (SELECT NVL(MAX(VERSION),-1)
    FROM WF_ACTIVITIES WF2
    WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
    AND WF2.NAME        = WFP.ACTIVITY_NAME
    )
  ) FLOW_PROCESS,
  SUBSTR(LIN.FLOW_STATUS_CODE,1,22) Line_st,
  NVL(LIN.ORDERED_QUANTITY,0) ORD_Q,
  LIN.ORDER_QUANTITY_UOM UOM,
  NVL(LIN.ORDERED_QUANTITY2,0) "DISC ORD_Q",
  LIN.ORDERED_QUANTITY_UOM2 "DISC UOM",
  LIN.UNIT_SELLING_PRICE SEL_PRICE,
  LIN.UNIT_LIST_PRICE LIS_PRICE,
  LIN.unit_list_price_per_pqty lp_per_pqty,
  TO_CHAR(LIN.pricing_date,'DD-MON-RR HH24:MI:SS') prc_date,
  LIN.pricing_quantity PRICE_qty,
  LIN.pricing_quantity_uom PRICE_uom,
  LIN.CALCULATE_PRICE_FLAG CAL_PRICE,
  (SELECT SUM(NVL(RESERVATION_QUANTITY,0))
  FROM MTL_RESERVATIONS RES
  WHERE RES.DEMAND_SOURCE_HEADER_ID = LIN.HEADER_ID
  AND RES.DEMAND_SOURCE_TYPE_ID    IN (2,8,9,21,22)
  AND RES.DEMAND_SOURCE_LINE_ID     = LIN.LINE_ID
  ) RES_Q,
  NVL(LIN.SHIPPING_QUANTITY,0) SHN_Q,
  NVL(LIN.SHIPPING_QUANTITY_UOM,0) SHN_UOM,
  NVL(LIN.SHIPPING_QUANTITY2,0) "DISC SHN_Q",
  NVL(LIN.SHIPPING_QUANTITY_UOM2,0) "DISC SHN_UOM",
  NVL(LIN.SHIPPED_QUANTITY,0) shpq,
  NVL(LIN.SHIPPED_QUANTITY2,0) "DISC SHPQ",
  NVL(FULFILLED_QUANTITY,0) fulq,
  NVL(FULFILLED_QUANTITY2,0) "DISC FULQ",
  NVL(LIN.CANCELLED_QUANTITY,0) canq,
  NVL(LIN.CANCELLED_QUANTITY2,0) "DISC CANQ",
  NVL(LIN.INVOICED_QUANTITY,0) invq,
  SUBSTR(LIN.SCHEDULE_STATUS_CODE,1,5) schc,
  NVL(LIN.OPEN_FLAG,'N') openf,
  NVL(LIN.BOOKED_FLAG,'N') bookf,
  NVL(LIN.SHIPPABLE_FLAG,'N') shipf,
  NVL(LIN.CANCELLED_FLAG,'N') canf,
  NVL(LIN.VISIBLE_DEMAND_FLAG,'N') vdem,
  NVL(LIN.FULFILLED_FLAG, 'N') fulf,
  NVL(LIN.SHIPPING_INTERFACED_FLAG,'N') SI,
  DECODE(NVL(LIN.ATO_LINE_ID,0),0,'N','Y') ato_i,
  NVL(LIN.SHIP_MODEL_COMPLETE_FLAG,'N') smcf,
  LIN.SHIP_FROM_ORG_ID shipfrom,
  PAR.ORGANIZATION_CODE org_i,
  TO_CHAR(LIN.promise_date,'DD-MON-RR HH24:MI:SS') promise_date,
  TO_CHAR(LIN.REQUEST_DATE,'DD-MON-RR HH24:MI:SS') REQUEST_DATE,
  TO_CHAR(LIN.SCHEDULE_SHIP_DATE,'DD-MON-RR HH24:MI:SS') SCHEDULE_SHIP_DATE,
  TYP.NAME line_type,
  LIN.LINE_TYPE_ID LINE_TYPE_ID,
  LIN.LINE_CATEGORY_CODE lin_cat,
  LIN.ITEM_TYPE_CODE itm_tc,
  LIN.ORDERED_ITEM_ID ord_it,
  LIN.SOURCE_TYPE_CODE Src_tc,
  LIN.PRICE_LIST_ID prlst,
  LIN.DEMAND_CLASS_CODE demc,
  NVL(LIN.OPTION_FLAG,'N') CFG,
  LIN.SHIP_FROM_ORG_ID WH_ID,
  LIN.TOP_MODEL_LINE_ID PRT_LN_ID,
  LIN.ATO_LINE_ID ATO_LN_ID,
  LIN.LINK_TO_LINE_ID LNK_LN_ID,
  LIN.service_reference_line_id srli,
  LIN.SPLIT_FROM_LINE_ID SPL_LN_ID,
  LIN.CONFIG_HEADER_ID CFG_HD_ID,
  NVL(LIN.INVOICE_INTERFACE_STATUS_CODE,'N') INVC_INT_STAT,
  LIN.SHIP_TOLERANCE_ABOVE STA,
  LIN.SHIP_TOLERANCE_BELOW STB,
  LIN.SHIP_SET_ID SH_SET_ID,
  ST2.SET_TYPE SH_SET_TY,
  LIN.LINE_SET_ID LN_SET_ID,
  ST3.SET_TYPE LN_SET_TY,
  LIN.ARRIVAL_SET_ID AR_SET_ID,
  ST1.SET_TYPE AR_SET_TY,
  LIN.CALCULATE_PRICE_FLAG CAL_PR,
  TO_CHAR(LIN.ACTUAL_SHIPMENT_DATE,'DD-MON-RR HH24:MI:SS') ACTUAL_SHIPMENT_DATE,
  TO_CHAR(LIN.CREATION_DATE,'DD-MON-RR HH24:MI:SS') cre_date,
  LIN.source_document_line_id source_doc_lin_id,
  LIN.ORDER_SOURCE_ID order_source_id,
  LIN.ORIG_SYS_DOCUMENT_REF ORIG_SYS_DOCUMENT_REF,
  LIN.ORIG_SYS_LINE_REF ORIG_SYS_LINE_REF,
  LIN.split_by split_by,
  LIN.split_from_line_id split_from,
  LIN.config_rev_nbr conf_nbr,
  LIN.configuration_id conf_id,
  LIN.subinventory subinv,
  LIN.invoice_to_org_id invoice_to,
  LIN.cust_po_number cust_po,
  NVL(LIN.upgraded_flag,'N') "UPGRADED FLAG",
  LIN.blanket_number blnket_no,
  LIN.blanket_line_number blnket_ln,
  LIN.first_ack_code first_acK,
  TO_CHAR(LIN.first_ack_date,'DD-MON-RR HH24:MI:SS') "FIRST ACK_DATE",
  LIN.last_ack_code last_ack,
  TO_CHAR(LIN.last_ack_date,'DD-MON-RR HH24:MI:SS') "LAST ACK_DATE",
  LIN.tax_value tax_v,
  NVL(LIN.model_remnant_flag, 'N') "REMNANT FLAG",
  LIN.PREFERRED_GRADE "PREFERRED GRADE",
  ITM.TRACKING_QUANTITY_IND "TRACKING QTY_IND",
  ITM.PRIMARY_UOM_CODE "PRIMARY UOM_CODE",
  ITM.DUAL_UOM_CONTROL "DUAL_UOM CONTROL",
  ITM.SECONDARY_DEFAULT_IND "SECONDARY DEFA_IND",
  ITM.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  ITM.CHILD_LOT_FLAG "CHILD LOT_FLAG",
  ITM.PARENT_CHILD_GENERATION_FLAG "PAR_CHILD GEN_FLAG",
  ITM.LOT_DIVISIBLE_FLAG "LOT_DIVIS FLAG",
  ITM.GRADE_CONTROL_FLAG "GRADE CTRL_FLAG",
  ITM.ONT_PRICING_QTY_SOURCE "ONT_PRICE QTY_SRC"
FROM OE_ORDER_LINES LIN,
  OE_TRANSACTION_TYPES TYP,
  MTL_SYSTEM_ITEMS ITM,
  MTL_PARAMETERS PAR,
  OE_SETS ST1,
  OE_SETS ST2,
  OE_SETS ST3
WHERE LIN.HEADER_ID       = &SalesOrder_Header_ID
AND NVL( 0 ,0)           IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND LIN.LINE_TYPE_ID      = TYP.TRANSACTION_TYPE_ID
AND LIN.SHIP_FROM_ORG_ID  = PAR.ORGANIZATION_ID(+)
AND LIN.SHIP_FROM_ORG_ID  = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID(+)
AND LIN.ARRIVAL_SET_ID    = ST1.SET_ID(+)
AND LIN.SHIP_SET_ID       = ST2.SET_ID(+)
AND LIN.LINE_SET_ID       = ST3.SET_ID(+)
ORDER BY 1;

--OE_ORDER_LINES_HISTORY (hil)
SELECT HIL.LINE_ID ,
  TO_CHAR(HIL.line_number)
  || DECODE(HIL.shipment_number, NULL, NULL, '.'
  || TO_CHAR(HIL.shipment_number))
  || DECODE(HIL.option_number, NULL, NULL, '.'
  || TO_CHAR(HIL.option_number))
  || DECODE(HIL.component_number, NULL, NULL, DECODE(HIL.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(HIL.component_number))
  || DECODE(HIL.service_number,NULL,NULL, DECODE(HIL.component_number, NULL, '.' , NULL)
  || DECODE(HIL.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(HIL.service_number)) Line_no ,
  NVL(HIL.ORDERED_QUANTITY,0) Ordered_Q ,
  HIL.ORDER_QUANTITY_UOM Order_UOM ,
  NVL(HIL.SHIPPING_QUANTITY,0) SHipping_Q ,
  NVL(HIL.SHIPPED_QUANTITY,0) Shipped_Q ,
  NVL(HIL.FULFILLED_QUANTITY,0) Fulfill_Q ,
  NVL(HIL.CANCELLED_QUANTITY,0) Cancel_Q ,
  NVL(HIL.INVOICED_QUANTITY,0) Invoiced_Q ,
  NVL(HIL.LATEST_CANCELLED_QUANTITY,0) Last_Can_Q ,
  SUBSTR(HIL.SCHEDULE_STATUS_CODE,1,5) Sched_code ,
  NVL(HIL.OPEN_FLAG,'N') Open_f ,
  NVL(HIL.BOOKED_FLAG,'N') Book_f ,
  NVL(HIL.SHIPPING_INTERFACED_FLAG,'N') Ship_Int ,
  NVL(HIL.SHIPPABLE_FLAG,'N') Ship_f ,
  NVL(HIL.FULFILLED_FLAG,'N') Fulf_f ,
  NVL(HIL.CANCELLED_FLAG,'N') Canc_f ,
  NVL(HIL.VISIBLE_DEMAND_FLAG,'N') Vis_dem ,
  HIL.FLOW_STATUS_CODE FLOW_Status ,
  HIL.WF_ACTIVITY_CODE WF_Activty ,
  HIL.WF_RESULT_CODE WF_result ,
  HIL.HIST_TYPE_CODE Hist_type ,
  TO_CHAR(HIL.HIST_CREATION_DATE, 'DD-MON-RR HH24:MI:SS') Crea_date ,
  HIL.UNIT_SELLING_PRICE Unit_sell ,
  HIL.REASON_CODE reason ,
  HIL.HIST_COMMENTS Hist_comts
FROM OE_ORDER_LINES_HISTORY HIL
WHERE HIL.HEADER_ID = &SalesOrder_Header_ID
AND NVL( 0 ,0)     IN (0,HIL.LINE_ID, HIL.TOP_MODEL_LINE_ID, HIL.ATO_LINE_ID, HIL.LINK_TO_LINE_ID, HIL.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(HIL.ATO_LINE_ID, HIL.LINE_ID),
  NVL(HIL.SORT_ORDER, '0000'),
  NVL(HIL.LINK_TO_LINE_ID, HIL.LINE_ID),
  NVL(HIL.SOURCE_DOCUMENT_LINE_ID, HIL.LINE_ID),
  NVL(HIL.SERVICE_REFERENCE_LINE_ID, HIL.LINE_ID),
  HIL.LINE_ID;
 
--Workflow Line Item Information
SELECT TO_CHAR(ool.line_number)
  || DECODE(ool.shipment_number, NULL, NULL, '.'
  || TO_CHAR(ool.shipment_number))
  || DECODE(ool.option_number, NULL, NULL, '.'
  || TO_CHAR(ool.option_number))
  || DECODE(ool.component_number, NULL, NULL, DECODE(ool.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(ool.component_number))
  || DECODE(ool.service_number,NULL,NULL, DECODE(ool.component_number, NULL, '.' , NULL)
  || DECODE(ool.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(ool.service_number)) LINE_NUM,
  ool.line_id LINE_ID,
  wi.parent_item_type PRT_ITEM_TYPE,
  wi.parent_item_key PRT_ITEM_KEY,
  TO_CHAR(wi.begin_date, 'DD-MON-RR HH24:MI:SS') BEGIN_DATE,
  TO_CHAR(wi.end_date, 'DD-MON-RR HH24:MI:SS') END_DATE ,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key , 'RESPONSIBILITY_ID') RESP_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'ORG_ID') ORG_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'APPLICATION_ID') APPL_ID,
  wf_engine.GetItemAttrNumber('OEOL', wi.item_key, 'USER_ID') USER_ID,
  wi.root_activity ROOT_ACTIVITY,
  wi.root_activity_version ROOT_ACTIVITY_VERSION
FROM wf_items wi,
  oe_order_lines ool
WHERE wi.item_type = 'OEOL'
AND wi.item_key    = TO_CHAR(ool.line_id)
AND ool.header_id  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,ool.LINE_ID, ool.TOP_MODEL_LINE_ID, ool.ATO_LINE_ID, ool.LINK_TO_LINE_ID, ool.SERVICE_REFERENCE_LINE_ID);

--Workflow Line Status
SELECT WFS.ITEM_KEY ITEM_KEY,
  WFA.DISPLAY_NAME PROCESS_NAME,
  WFA1.DISPLAY_NAME ACTIVITY_NAME,
  WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT_CODE,
  LKP.MEANING MEANING,
  WFS.ITEM_TYPE
  || '-'
  || WFS.NOTIFICATION_ID NOTIFICATION_ID,
  WFP.PROCESS_NAME INT_Process_name,
  WFP.ACTIVITY_NAME INT_Activity_name,
  WFS.ASSIGNED_USER ASGND_USER,
  TO_CHAR(WFS.BEGIN_DATE,'DD-MON-RR HH24:MI:SS') Begin_Date,
  TO_CHAR(WFS.END_DATE,'DD-MON-RR HH24:MI:SS') End_Date,
  WFS.ERROR_NAME Err_name,
  WFS.BEGIN_DATE BEGIN_DATE2,
  WFS.EXECUTION_TIME EXECUTION_TIME2
FROM WF_ITEM_ACTIVITY_STATUSES WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA,
  WF_ACTIVITIES_VL WFA1,
  WF_LOOKUPS LKP
WHERE WFS.ITEM_TYPE = 'OEOL'
AND WFS.item_key   IN
  (SELECT TO_CHAR(line_id)
  FROM OE_ORDER_LINES LIN
  WHERE LIN.HEADER_ID = &SalesOrder_Header_ID
  AND NVL( 0 ,0)     IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  )
AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME       = WFA.NAME
AND WFP.PROCESS_VERSION    = WFA.VERSION
AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
AND WFP.ACTIVITY_NAME      = WFA1.NAME
AND WFA1.VERSION           =
  (SELECT MAX(VERSION)
  FROM WF_ACTIVITIES WF2
  WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
  AND WF2.NAME        = WFP.ACTIVITY_NAME
  )
AND LKP.LOOKUP_TYPE = 'WFENG_STATUS'
AND LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS
UNION ALL
SELECT WFS.ITEM_KEY ITEM_KEY,
  WFA.DISPLAY_NAME PROCESS_NAME,
  WFA1.DISPLAY_NAME ACTIVITY_NAME,
  WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT_CODE,
  LKP.MEANING MEANING,
  WFS.ITEM_TYPE
  || '-'
  || WFS.NOTIFICATION_ID NOTIFICATION_ID,
  WFP.PROCESS_NAME INT_Process_name,
  WFP.ACTIVITY_NAME INT_Activity_name,
  WFS.ASSIGNED_USER ASGND_USER,
  TO_CHAR(WFS.BEGIN_DATE,'DD-MON-RR HH24:MI:SS') Begin_Date,
  TO_CHAR(WFS.END_DATE,'DD-MON-RR HH24:MI:SS') End_Date,
  WFS.ERROR_NAME Err_name,
  WFS.BEGIN_DATE BEGIN_DATE2,
  WFS.EXECUTION_TIME EXECUTION_TIME2
FROM WF_ITEM_ACTIVITY_STATUSES_H WFS,
  WF_PROCESS_ACTIVITIES WFP,
  WF_ACTIVITIES_VL WFA,
  WF_ACTIVITIES_VL WFA1,
  WF_LOOKUPS LKP
WHERE WFS.ITEM_TYPE = 'OEOL'
AND WFS.item_key   IN
  (SELECT TO_CHAR(line_id)
  FROM OE_ORDER_LINES LIN
  WHERE LIN.HEADER_ID = &SalesOrder_Header_ID
  AND NVL( 0 ,0)     IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  )
AND WFS.PROCESS_ACTIVITY   = WFP.INSTANCE_ID
AND WFP.PROCESS_ITEM_TYPE  = WFA.ITEM_TYPE
AND WFP.PROCESS_NAME       = WFA.NAME
AND WFP.PROCESS_VERSION    = WFA.VERSION
AND WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE
AND WFP.ACTIVITY_NAME      = WFA1.NAME
AND WFA1.VERSION           =
  (SELECT MAX(VERSION)
  FROM WF_ACTIVITIES WF2
  WHERE WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE
  AND WF2.NAME        = WFP.ACTIVITY_NAME
  )
AND LKP.LOOKUP_TYPE = 'WFENG_STATUS'
AND LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS
ORDER BY ITEM_KEY,
  BEGIN_DATE2,
  EXECUTION_TIME2;
 
--OE_DROP_SHIP_SOURCES (src)
SELECT SRC.DROP_SHIP_SOURCE_ID DROP_SHIP_ID,
  SRC.HEADER_ID HEADER_ID,
  SRC.LINE_ID LINE_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  SRC.ORG_ID ORG_ID,
  SRC.DESTINATION_ORGANIZATION_ID DEST_ID,
  PAR.ORGANIZATION_CODE DEST_ORG,
  SRC.REQUISITION_HEADER_ID REQ_HEADER_ID,
  SRC.REQUISITION_LINE_ID REQ_LINE_ID,
  SRC.PO_HEADER_ID PO_HEAD_ID,
  SRC.PO_LINE_ID PO_LINE_ID,
  SRC.LINE_LOCATION_ID LINE_LOC_ID,
  SRC.PO_RELEASE_ID PO_RELEASE_ID
FROM OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN,
  MTL_PARAMETERS PAR
WHERE SRC.LINE_ID                   = LIN.LINE_ID
AND SRC.DESTINATION_ORGANIZATION_ID = PAR.ORGANIZATION_ID(+)
AND LIN.HEADER_ID                   = &SalesOrder_Header_ID
AND NVL( 0 ,0)                     IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_REQUISITIONS_INTERFACE_ALL (rqi)
SELECT RQI.INTERFACE_SOURCE_LINE_ID DROP_SHIP_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RQI.AUTHORIZATION_STATUS AUTH_STATUS,
  RQI.DELIVER_TO_LOCATION_ID DELIV_LOC,
  RQI.PREPARER_ID PREPARER,
  RQI.DESTINATION_ORGANIZATION_ID DEST_ORG_ID,
  RQI.DESTINATION_TYPE_CODE DEST_TYPE,
  RQI.INTERFACE_SOURCE_CODE SRC_CODE,
  RQI.SOURCE_TYPE_CODE SRC_TYPE_CODE,
  RQI.ITEM_ID ITEM_ID,
  TO_CHAR(RQI.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY,
  RQI.QUANTITY QTY,
  RQI.UNIT_PRICE PRICE
FROM PO_REQUISITIONS_INTERFACE_ALL RQI,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID           = LIN.LINE_ID
AND SRC.DROP_SHIP_SOURCE_ID = RQI.INTERFACE_SOURCE_LINE_ID
AND LIN.HEADER_ID           = &SalesOrder_Header_ID
AND NVL( 0 ,0)             IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_INTERFACE_ERRORS_ALL (poe)
SELECT POE.INTERFACE_TRANSACTION_ID INTF_TRANS_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  POE.COLUMN_NAME COLUMN_NAME,
  POE.ERROR_MESSAGE ERROR,
  POE.INTERFACE_TYPE INTF_TYPE,
  POE.REQUEST_ID REQUEST_ID,
  POE.TABLE_NAME TABLE_NAME
FROM PO_INTERFACE_ERRORS POE,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN,
  PO_REQUISITIONS_INTERFACE_ALL RQI
WHERE SRC.LINE_ID           = LIN.LINE_ID
AND SRC.DROP_SHIP_SOURCE_ID = RQI.INTERFACE_SOURCE_LINE_ID
AND RQI.TRANSACTION_ID      = POE.INTERFACE_TRANSACTION_ID
AND LIN.HEADER_ID           = &SalesOrder_Header_ID
AND NVL( 0 ,0)             IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_REQUISITION_HEADERS_ALL (rqh)
 SELECT DISTINCT RQH.REQUISITION_HEADER_ID REQ_HEADER_ID ,
  RQH.SEGMENT1 REQ_NUMBER,
  RQH.INTERFACE_SOURCE_LINE_ID DROP_SHIP_ID,
  RQH.AUTHORIZATION_STATUS AUTH_STATUS,
  RQH.ENABLED_FLAG ENABLED,
  RQH.INTERFACE_SOURCE_CODE SRC_CODE,
  RQH.SUMMARY_FLAG SUMMARY,
  RQH.TRANSFERRED_TO_OE_FLAG XFR_OE_FLAG,
  RQH.TYPE_LOOKUP_CODE REQ_TYPE,
  RQH.WF_ITEM_TYPE ITEM_TYPE,
  RQH.WF_ITEM_KEY ITEM_KEY
FROM PO_REQUISITION_HEADERS_ALL RQH,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID             = LIN.LINE_ID
AND SRC.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
AND LIN.HEADER_ID             = &SalesOrder_Header_ID
AND NVL( 0 ,0)               IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT DISTINCT RQH.REQUISITION_HEADER_ID REQ_HEADER_ID ,
  RQH.SEGMENT1 REQ_NUMBER,
  RQH.INTERFACE_SOURCE_LINE_ID DROP_SHIP_ID,
  RQH.AUTHORIZATION_STATUS AUTH_STATUS,
  RQH.ENABLED_FLAG ENABLED,
  RQH.INTERFACE_SOURCE_CODE SRC_CODE,
  RQH.SUMMARY_FLAG SUMMARY,
  RQH.TRANSFERRED_TO_OE_FLAG XFR_OE_FLAG,
  RQH.TYPE_LOOKUP_CODE REQ_TYPE,
  RQH.WF_ITEM_TYPE ITEM_TYPE,
  RQH.WF_ITEM_KEY ITEM_KEY
FROM PO_REQUISITION_HEADERS_ALL RQH,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_ID    = RQH.REQUISITION_HEADER_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID = 10
AND LIN.HEADER_ID               = &SalesOrder_Header_ID
AND NVL( 0 ,0)                 IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT DISTINCT RQH.REQUISITION_HEADER_ID REQ_HEADER_ID ,
  RQH.SEGMENT1 REQ_NUMBER,
  RQH.INTERFACE_SOURCE_LINE_ID DROP_SHIP_ID,
  RQH.AUTHORIZATION_STATUS AUTH_STATUS,
  RQH.ENABLED_FLAG ENABLED,
  RQH.INTERFACE_SOURCE_CODE SRC_CODE,
  RQH.SUMMARY_FLAG SUMMARY,
  RQH.TRANSFERRED_TO_OE_FLAG XFR_OE_FLAG,
  RQH.TYPE_LOOKUP_CODE REQ_TYPE,
  RQH.WF_ITEM_TYPE ITEM_TYPE,
  RQH.WF_ITEM_KEY ITEM_KEY
FROM PO_REQUISITION_HEADERS_ALL RQH,
  MTL_RESERVATIONS RES
WHERE RES.DEMAND_SOURCE_HEADER_ID =
  (SELECT DISTINCT(MSO.SALES_ORDER_ID)
  FROM MTL_SALES_ORDERS MSO,
    OE_ORDER_HEADERS ORD,
    OE_TRANSACTION_TYPES_TL TYP,
    FND_LANGUAGES FLA
  WHERE ORD.HEADER_ID    = &SalesOrder_Header_ID
  AND ORD.ORDER_TYPE_ID  = TYP.TRANSACTION_TYPE_ID
  AND TYP.LANGUAGE       = FLA.LANGUAGE_CODE
  AND FLA.INSTALLED_FLAG = 'B'
  AND MSO.SEGMENT1       = ORD.ORDER_NUMBER
  AND TYP.NAME           = MSO.SEGMENT2
  )
AND RES.DEMAND_SOURCE_TYPE_ID   = 2
AND RES.SUPPLY_SOURCE_TYPE_ID   = 17
AND RES.SUPPLY_SOURCE_HEADER_ID = RQH.REQUISITION_HEADER_ID;

--PO_REQUISITION_LINES_ALL (rql)
 SELECT RQL.REQUISITION_HEADER_ID REQ_HEAD_ID,
  RQL.REQUISITION_LINE_ID REQ_LINE_ID,
  'DROP SHIP' DOC_TYPE,
  RQL.LINE_NUM REQ_LINE,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RQL.ITEM_ID ITEM_ID,
  RQL.ITEM_DESCRIPTION ITEM_DESC,
  RQL.UNIT_MEAS_LOOKUP_CODE UOM,
  RQL.UNIT_PRICE PRICE,
  RQL.QUANTITY QTY,
  RQL.QUANTITY_CANCELLED QTY_CNC,
  RQL.QUANTITY_DELIVERED QTY_DLV,
  RQL.CANCEL_FLAG CANC,
  RQL.SOURCE_TYPE_CODE SRC_TYPE,
  RQL.SOURCE_ORGANIZATION_ID SRC_ORG,
  RQL.DESTINATION_CONTEXT DEST_TYPE,
  RQL.DESTINATION_ORGANIZATION_ID DEST_ORG,
  RQL.ENCUMBERED_FLAG ENC_FL,
  RQL.LINE_TYPE_ID LINE_TYPE_ID,
  TO_CHAR(RQL.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY,
  RQL.ON_RFQ_FLAG RFQ ,
  RQL.SUGGESTED_BUYER_ID BUYER_ID
FROM PO_REQUISITION_LINES_ALL RQL,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID           = LIN.LINE_ID
AND RQL.REQUISITION_LINE_ID = SRC.REQUISITION_LINE_ID
AND LIN.HEADER_ID           = &SalesOrder_Header_ID
AND NVL( 0 ,0)             IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RQL.REQUISITION_HEADER_ID REQ_HEAD_ID,
  RQL.REQUISITION_LINE_ID REQ_LINE_ID,
  'INTERNAL SO' DOC_TYPE,
  RQL.LINE_NUM REQ_LINE,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RQL.ITEM_ID ITEM_ID,
  RQL.ITEM_DESCRIPTION ITEM_DESC,
  RQL.UNIT_MEAS_LOOKUP_CODE UOM,
  RQL.UNIT_PRICE PRICE,
  RQL.QUANTITY QTY,
  RQL.QUANTITY_CANCELLED QTY_CNC,
  RQL.QUANTITY_DELIVERED QTY_DLV,
  RQL.CANCEL_FLAG CANC,
  RQL.SOURCE_TYPE_CODE SRC_TYPE,
  RQL.SOURCE_ORGANIZATION_ID SRC_ORG,
  RQL.DESTINATION_CONTEXT DEST_TYPE,
  RQL.DESTINATION_ORGANIZATION_ID DEST_ORG,
  RQL.ENCUMBERED_FLAG ENC_FL ,
  RQL.LINE_TYPE_ID LINE_TYPE_ID,
  TO_CHAR(RQL.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY,
  RQL.ON_RFQ_FLAG RFQ ,
  RQL.SUGGESTED_BUYER_ID BUYER_ID
FROM PO_REQUISITION_LINES_ALL RQL,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID   = 10
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--PO_HEADERS_INTERFACE (phi)
 SELECT PHI.INTERFACE_HEADER_ID INTF_HEAD_ID,
  PHI.BATCH_ID BATCH_ID,
  PHI.INTERFACE_SOURCE_CODE INTF_SRC_CODE,
  PHI.PROCESS_CODE PROCESS_CODE,
  PHI.ACTION ACTION,
  PHI.PO_HEADER_ID PO_HEADER_ID,
  PHI.RELEASE_NUM REL_NUM,
  PHI.PO_RELEASE_ID PO_RELEASE_ID,
  PHI.VENDOR_NAME VENDOR_NAME,
  PHI.APPROVAL_STATUS APPRV_STAT,
  PHI.FIRM_FLAG FIRM,
  PHI.FROZEN_FLAG FROZEN,
  PHI.CLOSED_CODE CLOSE_CODE,
  TO_CHAR(PHI.CLOSED_DATE,'DD-MON-RR HH24:MI:SS') CLOSE_DATE,
  PHI.APPROVAL_REQUIRED_FLAG APPRV_REQD,
  PHI.REFERENCE_NUM REF_NUM,
  PHI.VENDOR_NUM VEND_NUM,
  PHI.WF_GROUP_ID WF_GROUP_ID
FROM PO_HEADERS_INTERFACE PHI,
  PO_LINES_INTERFACE PLI,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID           = LIN.LINE_ID
AND SRC.REQUISITION_LINE_ID = PLI.REQUISITION_LINE_ID
AND PHI.INTERFACE_HEADER_ID = PLI.INTERFACE_HEADER_ID
AND LIN.HEADER_ID           = &SalesOrder_Header_ID
AND NVL( 0 ,0)             IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_LINES_INTERFACE (pli)  
 SELECT PLI.INTERFACE_LINE_ID INTF_LINE_ID,
  PLI.INTERFACE_HEADER_ID INTF_HEAD_ID,
  PLI.ACTION ACTION,
  PLI.LINE_NUM PO_LINE,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  PLI.PO_LINE_ID PO_LINE_ID,
  PLI.SHIPMENT_NUM SHIP_NUM,
  PLI.SHIPMENT_TYPE SHIP_TYPE,
  PLI.REQUISITION_LINE_ID REQ_LINE_ID,
  PLI.PO_HEADER_ID PO_HEADER_ID,
  PLI.LINE_TYPE LINE_TYPE,
  PLI.ITEM ITEM,
  PLI.UNIT_OF_MEASURE UOM,
  PLI.QUANTITY QUANTITY,
  PLI.UNIT_PRICE PRICE,
  PLI.FIRM_FLAG FIRM,
  PLI.SHIP_TO_ORGANIZATION_CODE SHIP_ORG_CODE,
  PLI.SHIP_TO_ORGANIZATION_ID SHIP_ORG_ID,
  PLI.SHIP_TO_LOCATION SHIP_TO_LOC,
  PLI.SHIP_TO_LOCATION_ID SHIP_TO_LOC_ID,
  TO_CHAR(PLI.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY,
  TO_CHAR(PLI.PROMISED_DATE,'DD-MON-RR HH24:MI:SS') PROMISE,
  PLI.ORGANIZATION_ID WAREH_ID
FROM PO_LINES_INTERFACE PLI,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID           = LIN.LINE_ID
AND SRC.REQUISITION_LINE_ID = PLI.REQUISITION_LINE_ID
AND LIN.HEADER_ID           = &SalesOrder_Header_ID
AND NVL( 0 ,0)             IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_INTERFACE_ERRORS_ALL1 (poe)
 SELECT POE.INTERFACE_TRANSACTION_ID INF_TRANS_ID,
  POE.COLUMN_NAME COLUMN_NAME,
  POE.ERROR_MESSAGE ERROR,
  POE.INTERFACE_TYPE INF_TYPE,
  POE.REQUEST_ID REQUEST_ID,
  POE.TABLE_NAME TABLE_NAME
FROM PO_INTERFACE_ERRORS POE,
  PO_HEADERS_INTERFACE PHI,
  PO_LINES_INTERFACE PLI,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID                 = LIN.LINE_ID
AND SRC.REQUISITION_LINE_ID       = PLI.REQUISITION_LINE_ID
AND PHI.INTERFACE_HEADER_ID       = PLI.INTERFACE_HEADER_ID
AND (POE.INTERFACE_TRANSACTION_ID = PLI.INTERFACE_LINE_ID
OR POE.INTERFACE_TRANSACTION_ID   = PHI.INTERFACE_HEADER_ID)
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--PO_HEADERS_ALL (poh)
 SELECT POH.PO_HEADER_ID PO_HEADER_ID,
  POH.SEGMENT1 PO_NUM,
  POH.ACCEPTANCE_REQUIRED_FLAG ACCEPT_REQD,
  POH.BILL_TO_LOCATION_ID BILL_TO,
  POH.SHIP_TO_LOCATION_ID SHIP_TO,
  POH.CLOSED_CODE CLS_STAT,
  POH.CONFIRMING_ORDER_FLAG CONF_ORD,
  POH.CURRENCY_CODE CURR,
  POH.ENABLED_FLAG ENABLED,
  POH.FROZEN_FLAG FROZEN,
  POH.SUMMARY_FLAG SUMM,
  POH.TYPE_LOOKUP_CODE TYPE,
  POH.VENDOR_CONTACT_ID VEND_CNCACT,
  POH.VENDOR_ID VEND_ID,
  POH.VENDOR_SITE_ID VEND_SITE,
  POH.WF_ITEM_TYPE ITEM_TYPE,
  POH.WF_ITEM_KEY ITEM_KEY
FROM PO_HEADERS POH,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID    = LIN.LINE_ID
AND SRC.PO_HEADER_ID = POH.PO_HEADER_ID
AND LIN.HEADER_ID    = &SalesOrder_Header_ID
AND NVL( 0 ,0)      IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT DISTINCT POH.PO_HEADER_ID PO_HEADER_ID,
  POH.SEGMENT1 PO_NUM,
  POH.ACCEPTANCE_REQUIRED_FLAG ACCEPT_REQD,
  POH.BILL_TO_LOCATION_ID BILL_TO,
  POH.SHIP_TO_LOCATION_ID SHIP_TO,
  POH.CLOSED_CODE CLS_STAT,
  POH.CONFIRMING_ORDER_FLAG CONF_ORD,
  POH.CURRENCY_CODE CURR,
  POH.ENABLED_FLAG ENABLED,
  POH.FROZEN_FLAG FROZEN,
  POH.SUMMARY_FLAG SUMM,
  POH.TYPE_LOOKUP_CODE TYPE,
  POH.VENDOR_CONTACT_ID VEND_CNCACT,
  POH.VENDOR_ID VEND_ID,
  POH.VENDOR_SITE_ID VEND_SITE,
  POH.WF_ITEM_TYPE ITEM_TYPE,
  POH.WF_ITEM_KEY ITEM_KEY
FROM PO_HEADERS POH,
  MTL_RESERVATIONS RES
WHERE RES.DEMAND_SOURCE_HEADER_ID =
  (SELECT DISTINCT(MSO.SALES_ORDER_ID)
  FROM MTL_SALES_ORDERS MSO,
    OE_ORDER_HEADERS ORD,
    OE_TRANSACTION_TYPES_TL TYP,
    FND_LANGUAGES FLA
  WHERE ORD.HEADER_ID    = &SalesOrder_Header_ID
  AND ORD.ORDER_TYPE_ID  = TYP.TRANSACTION_TYPE_ID
  AND TYP.LANGUAGE       = FLA.LANGUAGE_CODE
  AND FLA.INSTALLED_FLAG = 'B'
  AND MSO.SEGMENT1       = ORD.ORDER_NUMBER
  AND TYP.NAME           = MSO.SEGMENT2
  )
AND RES.DEMAND_SOURCE_TYPE_ID   = 2
AND RES.SUPPLY_SOURCE_TYPE_ID  IN (1,13)
AND RES.SUPPLY_SOURCE_HEADER_ID = POH.PO_HEADER_ID;

--PO_LINES (pol)
SELECT POL.PO_HEADER_ID PO_HEADER_ID,
  POL.PO_LINE_ID PO_LINE_ID,
  POL.LINE_NUM PO_LINE,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  POL.CATEGORY_ID CATEGORY_ID,
  POL.CLOSED_CODE CLS_STAT,
  POL.FIRM_STATUS_LOOKUP_CODE FIRM,
  POL.ITEM_DESCRIPTION ITEM_DESC,
  POL.ITEM_ID ITEM_ID,
  POL.LINE_TYPE_ID LINE_TYPE_ID,
  POL.QUANTITY QTY,
  POL.UNIT_PRICE PRICE
FROM PO_LINES POL,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID  = LIN.LINE_ID
AND SRC.PO_LINE_ID = POL.PO_LINE_ID
AND LIN.HEADER_ID  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID;
 
--RCV_SHIPMENT_LINES (shl)
 SELECT SHL.SHIPMENT_LINE_ID SHP_LN_ID,
  SHL.SHIPMENT_HEADER_ID SHP_HD_ID,
  SHL.SHIPMENT_LINE_STATUS_CODE SHP_LINE_STATUS,
  SHL.QUANTITY_RECEIVED RCV_Q,
  SHL.QUANTITY_SHIPPED SHP_Q,
  SHL.DESTINATION_TYPE_CODE DEST_TYPE,
  SHL.OE_ORDER_HEADER_ID OE_HD_ID,
  SHL.OE_ORDER_LINE_ID OE_LN_ID,
  SHL.ITEM_ID ITEM_ID,
  SHL.SHIP_TO_LOCATION_ID TO_ORG,
  SHL.SOURCE_DOCUMENT_CODE SRC_DOC,
  SHL.REQUEST_ID REQUEST_ID
FROM RCV_SHIPMENT_LINES SHL,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID  = LIN.LINE_ID
AND SRC.PO_LINE_ID = SHL.PO_LINE_ID
AND LIN.HEADER_ID  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT SHL.SHIPMENT_LINE_ID SHP_LN_ID,
  SHL.SHIPMENT_HEADER_ID SHP_HD_ID,
  SHL.SHIPMENT_LINE_STATUS_CODE SHP_LINE_STATUS,
  SHL.QUANTITY_RECEIVED RCV_Q,
  SHL.QUANTITY_SHIPPED SHP_Q,
  SHL.DESTINATION_TYPE_CODE DEST_TYPE,
  SHL.OE_ORDER_HEADER_ID OE_HD_ID,
  SHL.OE_ORDER_LINE_ID OE_LN_ID,
  SHL.ITEM_ID ITEM_ID,
  SHL.SHIP_TO_LOCATION_ID TO_ORG,
  SHL.SOURCE_DOCUMENT_CODE SRC_DOC,
  SHL.REQUEST_ID REQUEST_ID
FROM RCV_SHIPMENT_LINES SHL,
  OE_ORDER_LINES LIN
WHERE SHL.OE_ORDER_LINE_ID = LIN.LINE_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT SHL.SHIPMENT_LINE_ID SHP_LN_ID,
  SHL.SHIPMENT_HEADER_ID SHP_HD_ID,
  SHL.SHIPMENT_LINE_STATUS_CODE SHP_LINE_STATUS,
  SHL.QUANTITY_RECEIVED RCV_Q,
  SHL.QUANTITY_SHIPPED SHP_Q,
  SHL.DESTINATION_TYPE_CODE DEST_TYPE,
  SHL.OE_ORDER_HEADER_ID OE_HD_ID,
  SHL.OE_ORDER_LINE_ID OE_LN_ID,
  SHL.ITEM_ID ITEM_ID,
  SHL.SHIP_TO_LOCATION_ID TO_ORG,
  SHL.SOURCE_DOCUMENT_CODE SRC_DOC,
  SHL.REQUEST_ID REQUEST_ID
FROM RCV_SHIPMENT_LINES SHL,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID = SHL.REQUISITION_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID   = 10
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT SHL.SHIPMENT_LINE_ID SHP_LN_ID,
  SHL.SHIPMENT_HEADER_ID SHP_HD_ID,
  SHL.SHIPMENT_LINE_STATUS_CODE SHP_LINE_STATUS,
  SHL.QUANTITY_RECEIVED RCV_Q,
  SHL.QUANTITY_SHIPPED SHP_Q,
  SHL.DESTINATION_TYPE_CODE DEST_TYPE,
  SHL.OE_ORDER_HEADER_ID OE_HD_ID,
  SHL.OE_ORDER_LINE_ID OE_LN_ID,
  SHL.ITEM_ID ITEM_ID,
  SHL.SHIP_TO_LOCATION_ID TO_ORG,
  SHL.SOURCE_DOCUMENT_CODE SRC_DOC,
  SHL.REQUEST_ID REQUEST_ID
FROM RCV_SHIPMENT_LINES SHL,
  MTL_RESERVATIONS RES,
  PO_HEADERS_ALL POH
WHERE RES.DEMAND_SOURCE_HEADER_ID = &SalesOrder_Header_ID
AND RES.DEMAND_SOURCE_LINE_ID     = NVL( 0 ,RES.DEMAND_SOURCE_LINE_ID)
AND RES.DEMAND_SOURCE_TYPE_ID     = 2
AND RES.SUPPLY_SOURCE_TYPE_ID    IN (1,13)
AND RES.SUPPLY_SOURCE_HEADER_ID   = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID              = SHL.PO_HEADER_ID;

--RCV_TRANSACTIONS (rcv)  
 SELECT RCV.TRANSACTION_ID TRANS_ID,
  RCV.PARENT_TRANSACTION_ID PRNT_TRANS_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RCV.TRANSACTION_TYPE TRANS_TYPE,
  TO_CHAR(RCV.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRANS_DATE,
  RCV.DESTINATION_TYPE_CODE DEST_TYPE,
  RCV.INSPECTION_STATUS_CODE INSPECT_STAT,
  RCV.INTERFACE_SOURCE_CODE INTF_SRC,
  RCV.INTERFACE_TRANSACTION_ID INTF_TRANS_ID,
  RCV.LOCATION_ID LOC_ID,
  RCV.MVT_STAT_STATUS MVT_STAT,
  RCV.ORGANIZATION_ID ORG_ID,
  RCV.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RCV.OE_ORDER_LINE_ID OE_LINE_ID,
  RCV.PO_HEADER_ID PO_HEAD_ID,
  RCV.PO_LINE_ID PO_LINE_ID,
  RCV.PO_LINE_LOCATION_ID LINE_LOC_ID,
  RCV.PO_UNIT_PRICE UNIT_PRICE,
  RCV.PRIMARY_UNIT_OF_MEASURE UOM,
  RCV.QUANTITY QTY,
  RCV.REQUEST_ID REQUEST_ID,
  RCV.SHIPMENT_HEADER_ID SHIP_HEAD_ID,
  RCV.SHIPMENT_LINE_ID SHIP_LINE_ID,
  RCV.SOURCE_DOCUMENT_CODE SRC_DOC_CODE,
  RCV.VENDOR_ID VEND_ID,
  RCV.VENDOR_SITE_ID VEND_SITE_ID
FROM RCV_TRANSACTIONS RCV,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID  = LIN.LINE_ID
AND SRC.PO_LINE_ID = RCV.PO_LINE_ID
AND LIN.HEADER_ID  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RCV.TRANSACTION_ID TRANS_ID,
  RCV.PARENT_TRANSACTION_ID PRNT_TRANS_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RCV.TRANSACTION_TYPE TRANS_TYPE,
  TO_CHAR(RCV.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRANS_DATE,
  RCV.DESTINATION_TYPE_CODE DEST_TYPE,
  RCV.INSPECTION_STATUS_CODE INSPECT_STAT,
  RCV.INTERFACE_SOURCE_CODE INTF_SRC_CODE,
  RCV.INTERFACE_TRANSACTION_ID INTF_TRANS_ID,
  RCV.LOCATION_ID LOC_ID,
  RCV.MVT_STAT_STATUS MVT_STAT,
  RCV.ORGANIZATION_ID ORG_ID,
  RCV.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RCV.OE_ORDER_LINE_ID OE_LINE_ID,
  RCV.PO_HEADER_ID PO_HEAD_ID,
  RCV.PO_LINE_ID PO_LINE_ID,
  RCV.PO_LINE_LOCATION_ID LINE_LOC_ID,
  RCV.PO_UNIT_PRICE UNIT_PRICE,
  RCV.PRIMARY_UNIT_OF_MEASURE UOM,
  RCV.QUANTITY QTY,
  RCV.REQUEST_ID REQUEST_ID,
  RCV.SHIPMENT_HEADER_ID SHIP_HEAD_ID,
  RCV.SHIPMENT_LINE_ID SHIP_LINE_ID,
  RCV.SOURCE_DOCUMENT_CODE SRC_DOC_CODE,
  RCV.VENDOR_ID VEND_ID,
  RCV.VENDOR_SITE_ID VEND_SITE_ID
FROM RCV_TRANSACTIONS RCV,
  OE_ORDER_LINES LIN
WHERE RCV.OE_ORDER_LINE_ID = LIN.LINE_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RCV.TRANSACTION_ID TRANS_ID,
  RCV.PARENT_TRANSACTION_ID PRNT_TRANS_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RCV.TRANSACTION_TYPE TRANS_TYPE,
  TO_CHAR(RCV.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRANS_DATE,
  RCV.DESTINATION_TYPE_CODE DEST_TYPE,
  RCV.INSPECTION_STATUS_CODE INSPECT_STAT,
  RCV.INTERFACE_SOURCE_CODE INTF_SRC_CODE,
  RCV.INTERFACE_TRANSACTION_ID INTF_TRANS_ID,
  RCV.LOCATION_ID LOC_ID,
  RCV.MVT_STAT_STATUS MVT_STAT,
  RCV.ORGANIZATION_ID ORG_ID,
  RCV.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RCV.OE_ORDER_LINE_ID OE_LINE_ID,
  RCV.PO_HEADER_ID PO_HEAD_ID,
  RCV.PO_LINE_ID PO_LINE_ID,
  RCV.PO_LINE_LOCATION_ID LINE_LOC_ID,
  RCV.PO_UNIT_PRICE UNIT_PRICE,
  RCV.PRIMARY_UNIT_OF_MEASURE UOM,
  RCV.QUANTITY QTY,
  RCV.REQUEST_ID REQUEST_ID,
  RCV.SHIPMENT_HEADER_ID SHIP_HEAD_ID,
  RCV.SHIPMENT_LINE_ID SHIP_LINE_ID,
  RCV.SOURCE_DOCUMENT_CODE SRC_DOC_CODE,
  RCV.VENDOR_ID VEND_ID,
  RCV.VENDOR_SITE_ID VEND_SITE_ID
FROM RCV_TRANSACTIONS RCV,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID = RCV.REQUISITION_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID   = 10
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RCV.TRANSACTION_ID TRANS_ID,
  RCV.PARENT_TRANSACTION_ID PRNT_TRANS_ID,
  TO_CHAR(RES.DEMAND_SOURCE_LINE_ID) LINE,
  RCV.TRANSACTION_TYPE TRANS_TYPE,
  TO_CHAR(RCV.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRANS_DATE,
  RCV.DESTINATION_TYPE_CODE DEST_TYPE,
  RCV.INSPECTION_STATUS_CODE INSPECT_STAT,
  RCV.INTERFACE_SOURCE_CODE INTF_SRC_CODE,
  RCV.INTERFACE_TRANSACTION_ID INTF_TRANS_ID,
  RCV.LOCATION_ID LOC_ID,
  RCV.MVT_STAT_STATUS MVT_STAT,
  RCV.ORGANIZATION_ID ORG_ID,
  RCV.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RCV.OE_ORDER_LINE_ID OE_LINE_ID,
  RCV.PO_HEADER_ID PO_HEAD_ID,
  RCV.PO_LINE_ID PO_LINE_ID,
  RCV.PO_LINE_LOCATION_ID LINE_LOC_ID,
  RCV.PO_UNIT_PRICE UNIT_PRICE,
  RCV.PRIMARY_UNIT_OF_MEASURE UOM,
  RCV.QUANTITY QTY,
  RCV.REQUEST_ID REQUEST_ID,
  RCV.SHIPMENT_HEADER_ID SHIP_HEAD_ID,
  RCV.SHIPMENT_LINE_ID SHIP_LINE_ID,
  RCV.SOURCE_DOCUMENT_CODE SRC_DOC_CODE,
  RCV.VENDOR_ID VEND_ID,
  RCV.VENDOR_SITE_ID VEND_SITE_ID
FROM RCV_TRANSACTIONS RCV,
  MTL_RESERVATIONS RES,
  PO_HEADERS_ALL POH
WHERE RES.DEMAND_SOURCE_HEADER_ID = &SalesOrder_Header_ID
AND RES.DEMAND_SOURCE_LINE_ID     = NVL( 0 ,RES.DEMAND_SOURCE_LINE_ID)
AND RES.DEMAND_SOURCE_TYPE_ID     = 2
AND RES.SUPPLY_SOURCE_TYPE_ID    IN (1,13)
AND RES.SUPPLY_SOURCE_HEADER_ID   = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID              = RCV.PO_HEADER_ID;

--RCV_TRANSACTIONS_INTERFACE (rti)
 SELECT RTI.INTERFACE_TRANSACTION_ID INTF_TRNS_ID,
  RTI.PROCESSING_MODE_CODE PROC_MODE,
  RTI.PROCESSING_STATUS_CODE PROC_STAT,
  RTI.TRANSACTION_STATUS_CODE TRNS_STAT,
  TO_CHAR(RTI.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRNS_DATE,
  RTI.TRANSACTION_TYPE TRNS_TYPE,
  RTI.DESTINATION_TYPE_CODE DEST_TYPE,
  RTI.INSPECTION_STATUS_CODE INSP_STAT,
  RTI.INTERFACE_SOURCE_CODE INSP_SRC,
  RTI.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RTI.OE_ORDER_LINE_ID OE_LINE_ID,
  RTI.ITEM_ID ITEM_ID,
  RTI.QUANTITY QTY,
  RTI.PRIMARY_QUANTITY PRY_Q,
  RTI.PO_UNIT_PRICE PO_UNIT_PRC,
  RTI.SUBINVENTORY SUB,
  RTI.TO_ORGANIZATION_ID TO_ORG_ID,
  RTI.RECEIPT_SOURCE_CODE RCPT_SRC,
  RTI.PARENT_TRANSACTION_ID PRNT_TRN_ID,
  RTI.SHIPMENT_HEADER_ID SHP_HEAD_ID,
  RTI.SHIPMENT_LINE_ID SHP_LINE_ID,
  RTI.SOURCE_DOCUMENT_CODE SRC_DOC,
  RTI.PROCESSING_REQUEST_ID REQUEST_ID
FROM RCV_TRANSACTIONS_INTERFACE RTI,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID  = LIN.LINE_ID
AND SRC.PO_LINE_ID = RTI.PO_LINE_ID
AND LIN.HEADER_ID  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RTI.INTERFACE_TRANSACTION_ID INTF_TRNS_ID,
  RTI.PROCESSING_MODE_CODE PROC_MODE,
  RTI.PROCESSING_STATUS_CODE PROC_STAT,
  RTI.TRANSACTION_STATUS_CODE TRNS_STAT,
  TO_CHAR(RTI.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRNS_DATE,
  RTI.TRANSACTION_TYPE TRNS_TYPE,
  RTI.DESTINATION_TYPE_CODE DEST_TYPE,
  RTI.INSPECTION_STATUS_CODE INSP_STAT,
  RTI.INTERFACE_SOURCE_CODE INSP_SRC,
  RTI.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RTI.OE_ORDER_LINE_ID OE_LINE_ID,
  RTI.ITEM_ID ITEM_ID,
  RTI.QUANTITY QTY,
  RTI.PRIMARY_QUANTITY PRY_Q,
  RTI.PO_UNIT_PRICE PO_UNIT_PRC,
  RTI.SUBINVENTORY SUB,
  RTI.TO_ORGANIZATION_ID TO_ORG_ID,
  RTI.RECEIPT_SOURCE_CODE RCPT_SRC,
  RTI.PARENT_TRANSACTION_ID PRNT_TRN_ID,
  RTI.SHIPMENT_HEADER_ID SHP_HEAD_ID,
  RTI.SHIPMENT_LINE_ID SHP_LINE_ID,
  RTI.SOURCE_DOCUMENT_CODE SRC_DOC,
  RTI.PROCESSING_REQUEST_ID REQUEST_ID
FROM RCV_TRANSACTIONS_INTERFACE RTI,
  OE_ORDER_LINES LIN
WHERE RTI.OE_ORDER_LINE_ID = LIN.LINE_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT RTI.INTERFACE_TRANSACTION_ID INTF_TRNS_ID,
  RTI.PROCESSING_MODE_CODE PROC_MODE,
  RTI.PROCESSING_STATUS_CODE PROC_STAT,
  RTI.TRANSACTION_STATUS_CODE TRNS_STAT,
  TO_CHAR(RTI.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRNS_DATE,
  RTI.TRANSACTION_TYPE TRNS_TYPE,
  RTI.DESTINATION_TYPE_CODE DEST_TYPE,
  RTI.INSPECTION_STATUS_CODE INSP_STAT,
  RTI.INTERFACE_SOURCE_CODE INSP_SRC,
  RTI.OE_ORDER_HEADER_ID OE_HEAD_ID,
  RTI.OE_ORDER_LINE_ID OE_LINE_ID,
  RTI.ITEM_ID ITEM_ID,
  RTI.QUANTITY QTY,
  RTI.PRIMARY_QUANTITY PRY_Q,
  RTI.PO_UNIT_PRICE PO_UNIT_PRC,
  RTI.SUBINVENTORY SUB,
  RTI.TO_ORGANIZATION_ID TO_ORG_ID,
  RTI.RECEIPT_SOURCE_CODE RCPT_SRC,
  RTI.PARENT_TRANSACTION_ID PRNT_TRN_ID,
  RTI.SHIPMENT_HEADER_ID SHP_HEAD_ID,
  RTI.SHIPMENT_LINE_ID SHP_LINE_ID,
  RTI.SOURCE_DOCUMENT_CODE SRC_DOC,
  RTI.PROCESSING_REQUEST_ID REQUEST_ID
FROM RCV_TRANSACTIONS_INTERFACE RTI,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID = RTI.REQUISITION_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID   = 10
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--MTL_SUPPLY (sup)
SELECT SUP.SUPPLY_TYPE_CODE SUP_TYPE,
  SUP.REQ_HEADER_ID REQ_HEAD_ID,
  SUP.REQ_LINE_ID REQ_LINE_ID,
  SUP.PO_HEADER_ID PO_HEAD_ID,
  SUP.PO_RELEASE_ID PO_REL_ID,
  SUP.PO_LINE_ID PO_LINE_ID,
  SUP.PO_LINE_LOCATION_ID PO_LINE_LOC_ID,
  SUP.PO_DISTRIBUTION_ID PO_DIST_ID,
  SUP.SHIPMENT_HEADER_ID SHP_HEAD_ID,
  SUP.SHIPMENT_LINE_ID SHP_LINE_ID,
  SUP.RCV_TRANSACTION_ID RCV_TRANS_ID,
  SUP.ITEM_ID ITEM_ID,
  SUP.QUANTITY QTY,
  TO_CHAR(SUP.RECEIPT_DATE,'DD-MON-RR HH24:MI:SS') RECPT_DATE,
  TO_CHAR(SUP.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY_DATE,
  SUP.DESTINATION_TYPE_CODE DEST_TYPE,
  SUP.FROM_ORGANIZATION_ID FROM_ORG,
  SUP.FROM_SUBINVENTORY FROM_SUB,
  SUP.TO_ORGANIZATION_ID TO_ORG,
  SUP.TO_SUBINVENTORY TO_SUB,
  SUP.INTRANSIT_OWNING_ORG_ID INTRNS_OWN_ORG
FROM MTL_SUPPLY SUP,
  OE_DROP_SHIP_SOURCES SRC,
  OE_ORDER_LINES LIN
WHERE SRC.LINE_ID  = LIN.LINE_ID
AND SRC.PO_LINE_ID = SUP.PO_LINE_ID
AND LIN.HEADER_ID  = &SalesOrder_Header_ID
AND NVL( 0 ,0)    IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT SUP.SUPPLY_TYPE_CODE SUP_TYPE,
  SUP.REQ_HEADER_ID REQ_HEAD_ID,
  SUP.REQ_LINE_ID REQ_LINE_ID,
  SUP.PO_HEADER_ID PO_HEAD_ID,
  SUP.PO_RELEASE_ID PO_REL_ID,
  SUP.PO_LINE_ID PO_LINE_ID,
  SUP.PO_LINE_LOCATION_ID PO_LINE_LOC_ID,
  SUP.PO_DISTRIBUTION_ID PO_DIST_ID,
  SUP.SHIPMENT_HEADER_ID SHP_HEAD_ID,
  SUP.SHIPMENT_LINE_ID SHP_LINE_ID,
  SUP.RCV_TRANSACTION_ID RCV_TRANS_ID,
  SUP.ITEM_ID ITEM_ID,
  SUP.QUANTITY QTY,
  TO_CHAR(SUP.RECEIPT_DATE,'DD-MON-RR HH24:MI:SS') RECPT_DATE,
  TO_CHAR(SUP.NEED_BY_DATE,'DD-MON-RR HH24:MI:SS') NEED_BY_DATE,
  SUP.DESTINATION_TYPE_CODE DEST_TYPE,
  SUP.FROM_ORGANIZATION_ID FROM_ORG,
  SUP.FROM_SUBINVENTORY FROM_SUB,
  SUP.TO_ORGANIZATION_ID TO_ORG,
  SUP.TO_SUBINVENTORY TO_SUB,
  SUP.INTRANSIT_OWNING_ORG_ID INTRNS_OWN_ORG
FROM MTL_SUPPLY SUP,
  OE_ORDER_LINES LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID = SUP.REQ_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID   = 10
AND LIN.HEADER_ID                 = &SalesOrder_Header_ID
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--MTL_RESERVATIONS (res)
 SELECT RES.RESERVATION_ID RESERV_ID,
  DECODE(RES.SHIP_READY_FLAG, 1,'1=Released', 2,'2=Submitted', TO_CHAR(RES.SHIP_READY_FLAG)) SHIP_READY,
  RES.DEMAND_SOURCE_HEADER_ID DS_HEAD_ID,
  RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID,
  RES.DEMAND_SOURCE_DELIVERY DS_DELIV,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  RES.INVENTORY_ITEM_ID ITEM_ID,
  ITM.SEGMENT1 ITEM,
  RES.PRIMARY_RESERVATION_QUANTITY RES_Q,
  RES.DETAILED_QUANTITY DET_Q,
  RES.PRIMARY_UOM_CODE UOM,
  TO_CHAR(RES.REQUIREMENT_DATE,'DD-MON-RR HH24:MI:SS') REQUIRD_DATE,
  RES.DEMAND_SOURCE_TYPE_ID DS_TYPE,
  RES.ORGANIZATION_ID WH_ID,
  RES.SUBINVENTORY_CODE SUBINV,
  RES.LOT_NUMBER LOT,
  RES.REVISION REV,
  RES.LOCATOR_ID LOC_ID,
  RES.SERIAL_NUMBER SERIAL_NUM,
  DECODE(RES.SUPPLY_SOURCE_TYPE_ID, 5,'5=WIP DJ', RES.SUPPLY_SOURCE_TYPE_ID) SS_TYPE_ID,
  WIP.WIP_ENTITY_ID WIP_ID,
  WIP.WIP_ENTITY_NAME JOB_NAME,
  JOB.STATUS_TYPE_DISP JOB_STAT,
  RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID,
  RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET,
  RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE
FROM MTL_RESERVATIONS RES,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM,
  WIP_ENTITIES WIP,
  WIP_DISCRETE_JOBS_V JOB
WHERE RES.DEMAND_SOURCE_HEADER_ID = &SalesOrder_Header_ID
AND RES.DEMAND_SOURCE_TYPE_ID    IN (2,8,9,21,22)
AND RES.DEMAND_SOURCE_LINE_ID     = LIN.LINE_ID(+)
AND NVL( 0 ,0)                   IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND RES.ORGANIZATION_ID           = ITM.ORGANIZATION_ID(+)
AND RES.INVENTORY_ITEM_ID         = ITM.INVENTORY_ITEM_ID(+)
AND RES.SUPPLY_SOURCE_HEADER_ID   = WIP.WIP_ENTITY_ID(+)
AND WIP.WIP_ENTITY_ID             = JOB.WIP_ENTITY_ID(+)
ORDER BY NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
  NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SORT_ORDER, '0000'),
  NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
  NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
  LIN.LINE_ID,
  RES.RESERVATION_ID;
 
--WIP_DISCRETE_JOBS (wip)
SELECT WIP.WIP_ENTITY_ID WIP_ENT_ID,
  WIV.WIP_ENTITY_NAME JOB_NAME,
  WIP.ORGANIZATION_ID WH_ID,
  WIP.REQUEST_ID REQUEST_ID,
  WIP.SOURCE_LINE_ID LINE_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  WIV.STATUS_TYPE_DISP STATUS,
  WIP.PRIMARY_ITEM_ID PRY_ITEM_ID,
  ITM.SEGMENT1 ITEM,
  WIP.FIRM_PLANNED_FLAG FIRM,
  WIP.JOB_TYPE JOB_TY,
  WIV.WIP_SUPPLY_TYPE_DISP WIP_SUP_TYPE,
  WIP.SCHEDULED_START_DATE SCH_STRT,
  WIP.SCHEDULED_COMPLETION_DATE SCH_CMPL,
  WIP.DATE_RELEASED RLS_DATE,
  WIP.DUE_DATE DUE_DATE,
  WIP.DATE_COMPLETED COMPLETED_DATE,
  WIP.DATE_CLOSED CLOSED_DATE,
  WIP.START_QUANTITY START_QUANTITY,
  WIP.QUANTITY_COMPLETED COMPLETED_QUANTITY,
  WIP.QUANTITY_SCRAPPED SCRAPPED_QUANTITY,
  WIP.NET_QUANTITY NET_QUANTITY,
  WIP.LINE_ID LINE_ID
FROM WIP_DISCRETE_JOBS WIP,
  WIP_DISCRETE_JOBS_V WIV,
  MTL_SYSTEM_ITEMS_B ITM,
  OE_ORDER_LINES_ALL LIN
WHERE WIP.WIP_ENTITY_ID = WIV.WIP_ENTITY_ID
AND WIP.PRIMARY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND WIP.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND WIP.SOURCE_LINE_ID  = LIN.LINE_ID
AND LIN.HEADER_ID       = &SalesOrder_Header_ID
AND NVL( 0 ,0)         IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);


--WSH_TRIPS (trp)
 SELECT DISTINCT TRP.TRIP_ID TRIP_ID,
  TRP.NAME NAME,
  TRP.PLANNED_FLAG PLND,
  TRP.STATUS_CODE STATUS_CODE,
  TRP.VEHICLE_NUMBER VEH_NUM,
  TRP.CARRIER_ID CARR_ID,
  TRP.SHIP_METHOD_CODE SHIP_METH,
  TRP.ROUTE_ID ROUTE_ID,
  TRP.VEHICLE_ORGANIZATION_ID VEH_ORG_ID
FROM WSH_TRIPS TRP
WHERE TRIP_ID IN
  (SELECT DISTINCT(STP.TRIP_ID)
  FROM OE_ORDER_LINES LIN,
    WSH_DELIVERY_DETAILS DET,
    WSH_NEW_DELIVERIES DEL,
    WSH_DELIVERY_LEGS LEG,
    WSH_TRIP_STOPS STP,
    WSH_DELIVERY_ASSIGNMENTS ASG
  WHERE DEL.DELIVERY_ID      = ASG.DELIVERY_ID
  AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
  AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
  AND STP.STOP_ID            = LEG.PICK_UP_STOP_ID
  AND LEG.DELIVERY_ID        = DEL.DELIVERY_ID
  AND LIN.HEADER_ID          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  )
ORDER BY TRP.TRIP_ID;

--WSH_TRIP_STOPS (stp)
 SELECT DISTINCT STP.STOP_ID STOP_ID,
  STP1.STOP_DESCRIPTION STOP_DESCRIPTION,
  STP.TRIP_ID TRIP_ID,
  TRP.NAME TRIP_NAME,
  STP.STOP_LOCATION_ID STOP_LOC_ID,
  STP.STATUS_CODE STATUS,
  STP.LOCK_STOP_ID LOCK_ST_ID,
  STP.PENDING_INTERFACE_FLAG PEND_INTERF,
  TO_CHAR(STP.PLANNED_DEPARTURE_DATE,'DD-MON-RR HH24:MI:SS') PLN_DEP_DATE,
  TO_CHAR(STP.ACTUAL_DEPARTURE_DATE,'DD-MON-RR HH24:MI:SS') ACT_DEP_DATE
FROM WSH_TRIP_STOPS STP,
  WSH_SRS_TRIP_STOPS_V STP1,
  WSH_TRIPS TRP
WHERE STP.TRIP_ID = TRP.TRIP_ID(+)
AND STP1.STOP_ID  = STP.STOP_ID
AND STP.STOP_ID  IN (
  (SELECT DISTINCT(LEG.PICK_UP_STOP_ID)
  FROM OE_ORDER_LINES LIN,
    WSH_DELIVERY_DETAILS DET,
    WSH_NEW_DELIVERIES DEL,
    WSH_DELIVERY_LEGS LEG,
    WSH_DELIVERY_ASSIGNMENTS ASG
  WHERE DEL.DELIVERY_ID      = ASG.DELIVERY_ID
  AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
  AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
  AND LEG.DELIVERY_ID        = DEL.DELIVERY_ID
  AND LIN.HEADER_ID          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  )
UNION
  (SELECT DISTINCT(LEG.DROP_OFF_STOP_ID)
  FROM OE_ORDER_LINES LIN,
    WSH_DELIVERY_DETAILS DET,
    WSH_NEW_DELIVERIES DEL,
    WSH_DELIVERY_LEGS LEG,
    WSH_DELIVERY_ASSIGNMENTS ASG
  WHERE DEL.DELIVERY_ID      = ASG.DELIVERY_ID
  AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
  AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
  AND LEG.DELIVERY_ID        = DEL.DELIVERY_ID
  AND LIN.HEADER_ID          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  ) );
 
--WSH_DELIVERY_LEGS (leg)
 SELECT DISTINCT LEG.DELIVERY_LEG_ID LEG_ID,
  LEG.SEQUENCE_NUMBER SEQ_NUM,
  LEG.DELIVERY_ID DELIVERY_ID,
  LEG.PICK_UP_STOP_ID PICKUP_STOP_ID,
  LEG.DROP_OFF_STOP_ID DROPOFF_STOP_ID
FROM OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_LEGS LEG,
  WSH_TRIP_STOPS STP,
  WSH_DELIVERY_ASSIGNMENTS ASG,
  WSH_TRIPS TRP
WHERE DEL.DELIVERY_ID      = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
AND STP.STOP_ID(+)         = LEG.PICK_UP_STOP_ID
AND STP.TRIP_ID            = TRP.TRIP_ID
AND LEG.DELIVERY_ID(+)     = DEL.DELIVERY_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY LEG.DELIVERY_LEG_ID;

--WSH_NEW_DELIVERIES (del)
 SELECT DISTINCT DEL.DELIVERY_ID DELIVERY_ID,
  DEL.NAME DEL_NAME,
  DEL.STATUS_CODE STATUS_CODE,
  DEL.WAYBILL WAYBILL,
  DEL.PLANNED_FLAG PLND,
  TO_CHAR(DEL.INITIAL_PICKUP_DATE,'DD-MON-RR HH24:MI:SS') PICKUP_DATE,
  DEL.INITIAL_PICKUP_LOCATION_ID PICKUP_LOC,
  TO_CHAR(DEL.ULTIMATE_DROPOFF_DATE,'DD-MON-RR HH24:MI:SS') DROPOFF_DATE,
  DEL.ULTIMATE_DROPOFF_LOCATION_ID DROPOFF_LOC,
  DEL.SHIP_METHOD_CODE SHP_METH,
  TO_CHAR(DEL.CONFIRM_DATE,'DD-MON-RR HH24:MI:SS') CONFIRM_DATE
FROM OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE DEL.DELIVERY_ID      = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY Del.DELIVERY_ID;

--WSH_DELIVERY_ASSIGNMENTS (asg)
 SELECT DISTINCT ASG.DELIVERY_ASSIGNMENT_ID DEL_ASGN_ID,
  ASG.DELIVERY_ID DELIVERY_ID,
  ASG.DELIVERY_DETAIL_ID DEL_DETAIL_ID,
  ASG.PARENT_DELIVERY_ID PAR_DEL_ID,
  ASG.PARENT_DELIVERY_DETAIL_ID PAR_DETAIL_ID
FROM OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID       = LIN.LINE_ID
AND LIN.HEADER_ID            = &SalesOrder_Header_ID
AND NVL( 0 ,0)              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--ORGANIZATIONS (org)
 SELECT DISTINCT wdd.organization_id w_org,
  WSH_UTIL_VALIDATE.CHECK_WMS_ORG(wdd.organization_id)WMS_ENABLE
FROM wsh_delivery_details wdd
WHERE wdd.source_code    = 'OE'
AND wdd.source_header_id = &SalesOrder_Header_ID ;

--WSH_DELIVERY_DETAILS (det)
SELECT DISTINCT DET.DELIVERY_DETAIL_ID DEL_DET_ID,
  DECODE(DET.RELEASED_STATUS, 'Y','Y=Staged', 'R','R=Ready to Release', 'S','S=Rel to Warhouse', 'B','B=Backorder', 'P','P=Pending Inv', 'C','C=Shipped', 'N','N=Not Ready', 'D','D=Cancelled', 'X','X=Not Applicable','Unknown: '
  ||DET.RELEASED_STATUS) REL_STATUS,
  DET.MOVE_ORDER_LINE_ID MO_LINE_ID,
  ASG.DELIVERY_ID DELIV_ID,
  TRP.TRIP_ID TRIP_ID,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  DET.SOURCE_LINE_ID LINE_ID,
  DET.INVENTORY_ITEM_ID ITEM_ID,
  ITM.SEGMENT1 ITEM,
  NVL(DET.SRC_REQUESTED_QUANTITY,0) "SRQ QTY",
  SRC_REQUESTED_QUANTITY_UOM "SRQ UOM",
  NVL(DET.SRC_REQUESTED_QUANTITY2,0) "DISC SRQ_QTY",
  SRC_REQUESTED_QUANTITY_UOM2 "DISC SRQ_UOM",
  NVL(DET.REQUESTED_QUANTITY,0) "REQ QTY",
  DET.REQUESTED_QUANTITY_UOM "REQ UOM",
  NVL(DET.REQUESTED_QUANTITY2,0) "DISC REQ_QTY",
  DET.REQUESTED_QUANTITY_UOM2 "DISC REQ_UOM",
  NVL(DET.SHIPPED_QUANTITY,0) "SHP QTY",
  NVL(DET.SHIPPED_QUANTITY2,0) "DISC SHP_QTY",
  NVL(DET.DELIVERED_QUANTITY,0) "DLV QTY",
  NVL(DET.DELIVERED_QUANTITY2,0) "DISC DLV_QTY",
  NVL(DET.CANCELLED_QUANTITY,0) "CAN QTY",
  NVL(DET.CANCELLED_QUANTITY2,0) "DISC CAN_QTY",
  NVL(DET.INV_INTERFACED_FLAG,'N') INI,
  NVL(DET.OE_INTERFACED_FLAG,'N') OMI,
  DET.SHIP_TOLERANCE_ABOVE STA,
  DET.SHIP_TOLERANCE_BELOW STB,
  DET.SHIP_FROM_LOCATION_ID "SHIP FROM_ID",
  DET.SHIP_TO_LOCATION_ID "SHIP TO_ID",
  DET.ORGANIZATION_ID WH_ID,
  DET.SUBINVENTORY CUR_SUB,
  DET.ATTRIBUTE15 ORG_SUB,
  DET.REVISION REV,
  DET.LOT_NUMBER "LOT NUMBER",
  DET.SERIAL_NUMBER "SERIAL NUMBER",
  DET.LOCATOR_ID "LOCATOR ID",
  DET.SHIP_METHOD_CODE "SHIP METHOD",
  DET.SPLIT_FROM_DELIVERY_DETAIL_ID "SPL_FROM DEL_DET",
  DET.PICKABLE_FLAG "WDD PICK_FLG",
  NVL(ITM.mtl_transactions_enabled_flag,'N') "ITEM PICK_FLG",
  DET.PICKED_QUANTITY "PICKED QUANTITY",
  DET.PICKED_QUANTITY2 "DISC PICKED_QTY",
  DET.SHIP_SET_ID "SHIP SET_ID",
  DET.SHIP_MODEL_COMPLETE_FLAG "SHIP_MODEL COMPL_FLAG",
  DET.TRANSACTION_TEMP_ID TRX_TEMP_ID,
  DET.TOP_MODEL_LINE_ID TOP_MO_LIN,
  TO_CHAR(DET.CREATION_DATE,'DD-MON-RR HH24:MI:SS') CRE_DATE,
  DET.CYCLE_COUNT_QUANTITY "BACK QTY",
  DET.CYCLE_COUNT_QUANTITY2 "DISC BACK_QTY",
  DET.QUALITY_CONTROL_QUANTITY "QUALITY CTRL_QTY",
  DET.QUALITY_CONTROL_QUANTITY2 "DISC QUALITY_QTY",
  DET.TO_SERIAL_NUMBER "TO_SERIAL NUMBER",
  DET.CARRIER_ID CARR_ID,
  DET.CONTAINER_NAME "CONTAINER NAME"
FROM OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_LEGS LEG,
  WSH_TRIP_STOPS STP,
  MTL_SYSTEM_ITEMS ITM,
  WSH_DELIVERY_ASSIGNMENTS ASG,
  WSH_TRIPS TRP
WHERE DEL.DELIVERY_ID(+)   = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
AND STP.STOP_ID(+)         = LEG.PICK_UP_STOP_ID
AND STP.TRIP_ID            = TRP.TRIP_ID(+)
AND LEG.DELIVERY_ID(+)     = DEL.DELIVERY_ID
AND LIN.SHIP_FROM_ORG_ID   = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID  = ITM.INVENTORY_ITEM_ID(+)
AND DET.SOURCE_CODE        = 'OE'
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
ORDER BY DET.SOURCE_LINE_ID,
  DET.DELIVERY_DETAIL_ID;

--WSH_DELIVERY_DETAILS (containers)
 SELECT DISTINCT DET.DELIVERY_DETAIL_ID DEL_DET_ID,
  DECODE(DET.RELEASED_STATUS, 'Y','Y=Staged', 'R','R=Ready to Release', 'S','S=Rel to Warehouse', 'B','B=Backorder', 'P','P=Pending Inv', 'C','C=Shipped', 'N','N=Not Ready', 'D','D=Cancelled', 'X','X=Not Applicable','Unknown: '
  ||DET.RELEASED_STATUS) REL_STATUS,
  ASG.DELIVERY_ID DELIV_ID,
  TRP.TRIP_ID TRIP_ID,
  DET.INVENTORY_ITEM_ID ITEM_ID,
  ITM.SEGMENT1 ITEM,
  DET.CONTAINER_FLAG CF,
  DET.CONTAINER_NAME CONT_NAME,
  DET.CONTAINER_TYPE_CODE CONT_TYPE,
  DET.FILL_PERCENT FL_PER,
  DET.GROSS_WEIGHT GRS_WT,
  DET.NET_WEIGHT NET_WT,
  DET.WEIGHT_UOM_CODE WT_UOM,
  DET.VOLUME VOL,
  DET.VOLUME_UOM_CODE VOL_UOM,
  NVL(DET.SRC_REQUESTED_QUANTITY,0) SRQ_Q,
  NVL(DET.REQUESTED_QUANTITY,0) REQ_Q,
  NVL(DET.SHIPPED_QUANTITY,0) SHP_Q,
  NVL(DET.DELIVERED_QUANTITY,0) DLV_Q,
  NVL(DET.CANCELLED_QUANTITY,0) CAN_Q,
  NVL(DET.INV_INTERFACED_FLAG,'N') INI,
  NVL(DET.OE_INTERFACED_FLAG,'N') OMI,
  DET.SHIP_FROM_LOCATION_ID SH_FROM_ID,
  DET.SHIP_TO_LOCATION_ID SH_TO_ID,
  DET.ORGANIZATION_ID WH_ID,
  DET.SHIP_METHOD_CODE SHIP_METH,
  TO_CHAR(DET.CREATION_DATE,'DD-MON-RR HH24:MI:SS') CREATION_DATE ,
  DET.FOB_CODE FOB_CODE,
  DET.FREIGHT_TERMS_CODE FRT_TERMS,
  DET.SPLIT_FROM_DELIVERY_DETAIL_ID SPL_DEL_DET_ID,
  DET.LPN_ID LPN_ID
FROM WSH_DELIVERY_DETAILS DET,
  WSH_DELIVERY_LEGS LEG,
  WSH_TRIP_STOPS STP,
  MTL_SYSTEM_ITEMS ITM,
  WSH_DELIVERY_ASSIGNMENTS ASG,
  WSH_TRIPS TRP
WHERE ASG.DELIVERY_DETAIL_ID = DET.DELIVERY_DETAIL_ID
AND DET.ORGANIZATION_ID      = ITM.ORGANIZATION_ID(+)
AND DET.INVENTORY_ITEM_ID    = ITM.INVENTORY_ITEM_ID(+)
AND STP.STOP_ID(+)           = LEG.PICK_UP_STOP_ID
AND STP.TRIP_ID              = TRP.TRIP_ID(+)
AND LEG.DELIVERY_ID(+)       = ASG.DELIVERY_ID
AND DET.SOURCE_CODE          = 'WSH'
AND ASG.DELIVERY_ID         IN
  (SELECT ASG1.DELIVERY_ID
  FROM WSH_DELIVERY_ASSIGNMENTS ASG1,
    WSH_DELIVERY_DETAILS DET1,
    OE_ORDER_LINES_ALL LIN1
  WHERE DET1.SOURCE_LINE_ID   = LIN1.LINE_ID
  AND DET1.DELIVERY_DETAIL_ID = ASG1.DELIVERY_DETAIL_ID
  AND DET1.SOURCE_CODE        = 'OE'
  AND LIN1.HEADER_ID          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)             IN (0,LIN1.LINE_ID, LIN1.TOP_MODEL_LINE_ID, LIN1.ATO_LINE_ID, LIN1.LINK_TO_LINE_ID, LIN1.REFERENCE_LINE_ID, LIN1.SERVICE_REFERENCE_LINE_ID)
  );
 
--WSH_SERIAL_NUMBERS (wsn)
SELECT DISTINCT DET.DELIVERY_DETAIL_ID DEL_DET_ID,
  DET.RELEASED_STATUS REL_STATUS_C,
  DECODE(DET.RELEASED_STATUS, 'Y','Y=Staged', 'R','R=Ready to Release', 'S','S=Rel to Warehouse', 'B','B=Backorder', 'P','P=Pending Inv', 'C','C=Shipped', 'N','N=Not Ready', 'D','D=Cancelled', 'X','X=Not Applicable','Unknown: '
  ||DET.RELEASED_STATUS) REL_STATUS,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  DET.SOURCE_LINE_ID LINE_ID,
  DET.INVENTORY_ITEM_ID ITEM_ID,
  NVL(DET.SRC_REQUESTED_QUANTITY,0) SRQ_Q,
  SRC_REQUESTED_QUANTITY_UOM SRQ_U,
  NVL(DET.REQUESTED_QUANTITY,0) REQ_Q,
  SRC_REQUESTED_QUANTITY_UOM REQ_U,
  NVL(DET.SHIPPED_QUANTITY,0) SHP_Q,
  NVL(DET.DELIVERED_QUANTITY,0) DLV_Q,
  NVL(DET.CANCELLED_QUANTITY,0) CAN_Q,
  NVL(DET.INV_INTERFACED_FLAG,'N') INI,
  NVL(DET.OE_INTERFACED_FLAG,'N') OMI,
  DET.SHIP_TOLERANCE_ABOVE STA,
  DET.SHIP_TOLERANCE_BELOW STB,
  DET.ORGANIZATION_ID WH_ID,
  DET.SUBINVENTORY CUR_SUB,
  DET.ATTRIBUTE15 ORG_SUB,
  DET.REVISION REV,
  DET.LOT_NUMBER LOT,
  DET.SERIAL_NUMBER SERIAL,
  DET.LOCATOR_ID LOC_ID,
  DET.SPLIT_FROM_DELIVERY_DETAIL_ID SPL_DEL_DET_ID,
  DET.PICKED_QUANTITY PICKED_QUANTITY,
  SUBSTR(wsn.fm_serial_number,1,15) FROM_SERIAL,
  SUBSTR(wsn.to_serial_number,1,15) TO_SERIAL,
  wsn.quantity WSN_QTY,
  TO_CHAR(wsn.creation_date,'DD-MON-RR HH24:MI:SS') WSN_CRE_DATE
FROM OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_SERIAL_NUMBERS WSN
WHERE DET.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID       = LIN.LINE_ID
AND DET.SOURCE_CODE          = 'OE'
AND LIN.HEADER_ID            = &SalesOrder_Header_ID
AND NVL( 0 ,0)              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--WSH_FREIGHT_COSTS (cst)
 SELECT DISTINCT wfc.FREIGHT_COST_ID FRT_CST_ID,
  wfc.FREIGHT_COST_TYPE FRT_NAME,
  lkp.Meaning FRT_TYPE,
  'DELIV_DETAIL' FRT_LEVEL,
  wfc.QUANTITY QTY,
  wfc.UNIT_AMOUNT UNIT_AMT,
  wfc.TOTAL_AMOUNT TOT_AMT,
  wfc.DELIVERY_DETAIL_ID ENTITY_ID
FROM wsh_freight_costs_v wfc,
  wsh_freight_cost_types wft,
  fnd_lookup_values lkp,
  OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET
WHERE wfc.freight_cost_type_id = wft.freight_cost_type_id
AND wft.freight_cost_type_code = lkp.lookup_code
AND lkp.lookup_type            = 'FREIGHT_COST_TYPE'
AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
AND LIN.HEADER_ID              = &SalesOrder_Header_ID
AND NVL( 0 ,0)                IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND WFC.DELIVERY_DETAIL_ID     = DET.DELIVERY_DETAIL_ID
UNION ALL
SELECT DISTINCT wfc.FREIGHT_COST_ID FRT_CST_ID,
  wfc.FREIGHT_COST_TYPE FRT_NAME,
  lkp.Meaning FRT_TYPE,
  'DELIVERY' FRT_LEVEL,
  wfc.QUANTITY QTY,
  wfc.UNIT_AMOUNT UNIT_AMT,
  wfc.TOTAL_AMOUNT TOT_AMT,
  wfc.DELIVERY_ID ENTITY_ID
FROM wsh_freight_costs_v wfc,
  wsh_freight_cost_types wft,
  fnd_lookup_values lkp,
  OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE wfc.freight_cost_type_id = wft.freight_cost_type_id
AND wft.freight_cost_type_code = lkp.lookup_code
AND lkp.lookup_type            = 'FREIGHT_COST_TYPE'
AND ASG.DELIVERY_DETAIL_ID     = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
AND LIN.HEADER_ID              = &SalesOrder_Header_ID
AND NVL( 0 ,0)                IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND WFC.DELIVERY_ID            = ASG.DELIVERY_ID
AND WFC.DELIVERY_DETAIL_ID    IS NULL
UNION ALL
SELECT DISTINCT wfc.FREIGHT_COST_ID FRT_CST_ID,
  wfc.FREIGHT_COST_TYPE FRT_NAME,
  lkp.Meaning FRT_TYPE,
  'LEG' FRT_LEVEL,
  wfc.QUANTITY QTY,
  wfc.UNIT_AMOUNT UNIT_AMT,
  wfc.TOTAL_AMOUNT TOT_AMT,
  wfc.DELIVERY_LEG_ID ENTITY_ID
FROM wsh_freight_costs_v wfc,
  wsh_freight_cost_types wft,
  fnd_lookup_values lkp,
  OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_LEGS LEG,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE wfc.freight_cost_type_id = wft.freight_cost_type_id
AND wft.freight_cost_type_code = lkp.lookup_code
AND lkp.lookup_type            = 'FREIGHT_COST_TYPE'
AND DEL.DELIVERY_ID            = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID     = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
AND LEG.DELIVERY_ID            = DEL.DELIVERY_ID
AND LIN.HEADER_ID              = &SalesOrder_Header_ID
AND NVL( 0 ,0)                IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND WFC.DELIVERY_LEG_ID        = LEG.DELIVERY_LEG_ID
AND WFC.DELIVERY_DETAIL_ID    IS NULL
AND WFC.DELIVERY_ID           IS NULL
UNION ALL
SELECT DISTINCT wfc.FREIGHT_COST_ID FRT_CST_ID,
  wfc.FREIGHT_COST_TYPE FRT_NAME,
  lkp.Meaning FRT_TYPE,
  'STOP' FRT_LEVEL,
  wfc.QUANTITY QTY,
  wfc.UNIT_AMOUNT UNIT_AMT,
  wfc.TOTAL_AMOUNT TOT_AMT,
  wfc.STOP_ID ENTITY_ID
FROM wsh_freight_costs_v wfc,
  wsh_freight_cost_types wft,
  fnd_lookup_values lkp,
  OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_LEGS LEG,
  WSH_TRIP_STOPS STP,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE wfc.freight_cost_type_id = wft.freight_cost_type_id
AND wft.freight_cost_type_code = lkp.lookup_code
AND lkp.lookup_type            = 'FREIGHT_COST_TYPE'
AND DEL.DELIVERY_ID            = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID     = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
AND STP.STOP_ID                = LEG.PICK_UP_STOP_ID
AND LEG.DELIVERY_ID            = DEL.DELIVERY_ID
AND LIN.HEADER_ID              = &SalesOrder_Header_ID
AND NVL( 0 ,0)                IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND WFC.STOP_ID                = STP.STOP_ID
AND WFC.DELIVERY_DETAIL_ID    IS NULL
AND WFC.DELIVERY_ID           IS NULL
AND WFC.DELIVERY_LEG_ID       IS NULL
UNION ALL
SELECT DISTINCT wfc.FREIGHT_COST_ID FRT_CST_ID,
  wfc.FREIGHT_COST_TYPE FRT_NAME,
  lkp.Meaning FRT_TYPE,
  'TRIP' FRT_LEVEL,
  wfc.QUANTITY QTY,
  wfc.UNIT_AMOUNT UNIT_AMT,
  wfc.TOTAL_AMOUNT TOT_AMT,
  wfc.TRIP_ID ENTITY_ID
FROM wsh_freight_costs_v wfc,
  wsh_freight_cost_types wft,
  fnd_lookup_values lkp,
  OE_ORDER_LINES LIN,
  WSH_DELIVERY_DETAILS DET,
  WSH_NEW_DELIVERIES DEL,
  WSH_DELIVERY_LEGS LEG,
  WSH_TRIP_STOPS STP,
  WSH_DELIVERY_ASSIGNMENTS ASG
WHERE wfc.freight_cost_type_id = wft.freight_cost_type_id
AND wft.freight_cost_type_code = lkp.lookup_code
AND lkp.lookup_type            = 'FREIGHT_COST_TYPE'
AND DEL.DELIVERY_ID            = ASG.DELIVERY_ID
AND ASG.DELIVERY_DETAIL_ID     = DET.DELIVERY_DETAIL_ID
AND DET.SOURCE_LINE_ID         = LIN.LINE_ID
AND STP.STOP_ID                = LEG.PICK_UP_STOP_ID
AND LEG.DELIVERY_ID            = DEL.DELIVERY_ID
AND LIN.HEADER_ID              = &SalesOrder_Header_ID
AND NVL( 0 ,0)                IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND WFC.TRIP_ID                = STP.TRIP_ID
AND WFC.DELIVERY_DETAIL_ID    IS NULL
AND WFC.DELIVERY_ID           IS NULL
AND WFC.STOP_ID               IS NULL
AND WFC.DELIVERY_LEG_ID       IS NULL;

--MTL_TRANSACTIONS_INTERFACE (mti)
 SELECT TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  LIN.LINE_ID LINE_ID,
  DET.DELIVERY_DETAIL_ID DEL_DETAIL_ID,
  ITM.SEGMENT1 ITEM,
  TMP.PICKING_LINE_ID PICK_LN_ID,
  DECODE(TMP.TRANSACTION_TYPE_ID, 52,'Stage Trans', 33,'SO Issue', 15,'RMA Receipt', 18,'PO Receipt', TMP.TRANSACTION_TYPE_ID) TXN_TYPE,
  TMP.PRIMARY_QUANTITY "PRIMARY QTY",
  TMP.SUBINVENTORY_CODE FROM_SUB,
  TMP.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TMP.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TMP.LOCATOR_ID "FROM LOC_ID",
  TMP.PROCESS_FLAG PROCESS,
  TMP.LOCK_FLAG LCK,
  TMP.TRANSACTION_MODE "TRANS MODE",
  TMP.CONTENT_LPN_ID LPN_ID,
  TMP.ERROR_CODE ERROR_CODE,
  TMP.ERROR_EXPLANATION ERROR_EXPL
FROM MTL_TRANSACTIONS_INTERFACE TMP,
  WSH_DELIVERY_DETAILS DET,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE TMP.SOURCE_LINE_ID   = LIN.LINE_ID
AND LIN.LINE_CATEGORY_CODE = 'ORDER'
AND LIN.SHIP_FROM_ORG_ID   = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID  = ITM.INVENTORY_ITEM_ID(+)
AND DET.SOURCE_LINE_ID     = LIN.LINE_ID
AND DET.DELIVERY_DETAIL_ID = TMP.PICKING_LINE_ID
AND LIN.HEADER_ID          = &SalesOrder_Header_ID
AND NVL( 0 ,0)            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  LIN.LINE_ID LINE_ID,
  DET.DELIVERY_DETAIL_ID DEL_DETAIL_ID,
  ITM.SEGMENT1 ITEM,
  TMP.PICKING_LINE_ID PICK_LN_ID,
  DECODE(TMP.TRANSACTION_TYPE_ID, 52,'Stage Trans', 33,'SO Issue', 15,'RMA Receipt', 18,'PO Receipt', TMP.TRANSACTION_TYPE_ID) TXN_TYPE,
  TMP.PRIMARY_QUANTITY "PRIMARY QTY",
  TMP.SUBINVENTORY_CODE FROM_SUB,
  TMP.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TMP.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TMP.LOCATOR_ID "FROM LOC_ID",
  TMP.PROCESS_FLAG PROCESS,
  TMP.LOCK_FLAG LCK,
  TMP.TRANSACTION_MODE "TRANS MODE",
  TMP.CONTENT_LPN_ID LPN_ID,
  TMP.ERROR_CODE ERROR_CODE,
  TMP.ERROR_EXPLANATION ERROR_EXPL
FROM MTL_TRANSACTIONS_INTERFACE TMP,
  WSH_DELIVERY_DETAILS DET,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE TMP.TRX_SOURCE_LINE_ID = LIN.LINE_ID
AND LIN.LINE_CATEGORY_CODE   = 'RETURN'
AND LIN.SHIP_FROM_ORG_ID     = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID    = ITM.INVENTORY_ITEM_ID(+)
AND DET.SOURCE_LINE_ID       = LIN.LINE_ID
AND DET.DELIVERY_DETAIL_ID   = TMP.PICKING_LINE_ID
AND LIN.HEADER_ID            = &SalesOrder_Header_ID
AND NVL( 0 ,0)              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);


--MTL_MATERIAL_TRANSACTIONS_TEMP (tmp) - Unpicked Lines
 SELECT DISTINCT TMP.TRANSACTION_TEMP_ID MTL_TRNS_ID,
  TMP.MOVE_ORDER_LINE_ID MOVE_LINE_ID,
  TMP.PICK_SLIP_NUMBER PICK_SLIP,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  LIN.LINE_ID LINE_ID,
  ITM.SEGMENT1 ITEM,
  TMP.PRIMARY_QUANTITY "PRIMARY QTY",
  TMP.SUBINVENTORY_CODE FROM_SUB,
  TMP.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TMP.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TMP.LOCATOR_ID "FROM LOC_ID",
  TMP.TRANSFER_SUBINVENTORY TO_SUB,
  TMP.TRANSFER_TO_LOCATION TO_LOC_ID,
  TMP.PROCESS_FLAG PROCESS,
  TMP.LOCK_FLAG LCK,
  TMP.TRANSACTION_MODE TRANS_MODE,
  TMP.TRX_SOURCE_LINE_ID SRC_LINE_ID,
  TMP.PICKING_LINE_ID PICK_LINE_ID,
  TMP.ERROR_CODE ERROR_CODE,
  TMP.ERROR_EXPLANATION ERROR_EXPL
FROM MTL_MATERIAL_TRANSACTIONS_TEMP TMP,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE TMP.DEMAND_SOURCE_LINE = LIN.LINE_ID
AND LIN.LINE_CATEGORY_CODE   = 'ORDER'
AND LIN.SHIP_FROM_ORG_ID     = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID    = ITM.INVENTORY_ITEM_ID(+)
AND LIN.HEADER_ID            = &SalesOrder_Header_ID
AND NVL( 0 ,0)              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
UNION ALL
SELECT DISTINCT TMP.TRANSACTION_TEMP_ID MTL_TRNS_ID,
  TMP.MOVE_ORDER_LINE_ID MOVE_LINE_ID,
  TMP.PICK_SLIP_NUMBER PICK_SLIP,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  LIN.LINE_ID LINE_ID,
  ITM.SEGMENT1 ITEM,
  TMP.PRIMARY_QUANTITY "PRIMARY QTY",
  TMP.SUBINVENTORY_CODE FROM_SUB,
  TMP.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TMP.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TMP.LOCATOR_ID "FROM LOC_ID",
  TMP.TRANSFER_SUBINVENTORY TO_SUB,
  TMP.TRANSFER_TO_LOCATION TO_LOC_ID,
  TMP.PROCESS_FLAG PROCESS,
  TMP.LOCK_FLAG LCK,
  TMP.TRANSACTION_MODE TRANS_MODE,
  TMP.TRX_SOURCE_LINE_ID SRC_LINE_ID,
  TMP.PICKING_LINE_ID PICK_LINE_ID,
  TMP.ERROR_CODE ERROR_CODE,
  TMP.ERROR_EXPLANATION ERROR_EXPL
FROM MTL_MATERIAL_TRANSACTIONS_TEMP TMP,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE TMP.TRX_SOURCE_LINE_ID = LIN.LINE_ID
AND LIN.LINE_CATEGORY_CODE   = 'RETURN'
AND LIN.SHIP_FROM_ORG_ID     = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID    = ITM.INVENTORY_ITEM_ID(+)
AND LIN.HEADER_ID            = &SalesOrder_Header_ID
AND NVL( 0 ,0)              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--MTL_MATERIAL_TRANSACTIONS (trn) - Picked Lines
 SELECT TRN.TRANSACTION_ID MTL_TRNS_ID,
  TRN.MOVE_ORDER_LINE_ID MOVE_LINE_ID,
  DECODE(TRN.TRANSACTION_TYPE_ID, 52,'Stage Trans', 53,'Stage Trans INT', 33,'SO Issue', 34,'SO Issue INT', 15,'RMA Receipt', 18,'PO Receipt', 'Invalid '
  ||TO_CHAR(TRN.TRANSACTION_TYPE_ID)) TRANS_TYPE,
  TRN.PICK_SLIP_NUMBER PICK_SLIP,
  TRN.TRX_SOURCE_LINE_ID TRX_SOURCE_LINE_ID,
  TRN.TRX_SOURCE_LINE_ID LINE_ID,
  TRN.PRIMARY_QUANTITY PRM_Q,
  TRN.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TRN.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TRN.SUBINVENTORY_CODE FROM_SUB,
  TRN.LOCATOR_ID FROM_LOC_ID,
  TRN.TRANSFER_SUBINVENTORY TO_SUB,
  TRN.TRANSFER_LOCATOR_ID TO_LOC_ID,
  TRN.ORGANIZATION_ID ORG_ID,
  TRN.PICKING_LINE_ID PICKING_LINE_ID,
  TRN.TRANSACTION_SOURCE_TYPE_ID TRX_SRC_TYPID,
  TRN.SHIPMENT_NUMBER DELIVERY_ID,
  TRN.TRANSACTION_QUANTITY TRX_QTY,
  TRN.REVISION REVISION
FROM MTL_MATERIAL_TRANSACTIONS TRN,
  MTL_SYSTEM_ITEMS ITM
WHERE (TRN.TRX_SOURCE_LINE_ID,TRN.ORGANIZATION_ID,TRN.INVENTORY_ITEM_ID) IN
  (SELECT DISTINCT LINE_ID,
    SHIP_FROM_ORG_ID,
    INVENTORY_ITEM_ID
  FROM OE_ORDER_LINES LIN1
  WHERE LIN1.HEADER_ID = &SalesOrder_Header_ID
  AND NVL( 0 ,0)      IN (0,LIN1.LINE_ID, LIN1.TOP_MODEL_LINE_ID, LIN1.ATO_LINE_ID, LIN1.LINK_TO_LINE_ID, LIN1.REFERENCE_LINE_ID, LIN1.SERVICE_REFERENCE_LINE_ID)
  )
AND TRN.ORGANIZATION_ID   = ITM.ORGANIZATION_ID
AND TRN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
UNION ALL
SELECT TRN.TRANSACTION_ID MTL_TRNS_ID,
  TRN.MOVE_ORDER_LINE_ID MOVE_LINE_ID,
  DECODE(TRN.TRANSACTION_TYPE_ID, 52,'Stage Trans', 33,'SO Issue', 15,'RMA Receipt', 18,'PO Receipt', 'Invalid '
  ||TO_CHAR(TRN.TRANSACTION_TYPE_ID)) TRANS_TYPE,
  TRN.PICK_SLIP_NUMBER PICK_SLIP,
  TRN.TRX_SOURCE_LINE_ID TRX_SOURCE_LINE_ID,
  TRN.TRX_SOURCE_LINE_ID LINE_ID,
  TRN.PRIMARY_QUANTITY PRM_Q,
  TRN.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TRN.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TRN.SUBINVENTORY_CODE FROM_SUB,
  TRN.LOCATOR_ID FROM_LOC_ID,
  TRN.TRANSFER_SUBINVENTORY TO_SUB,
  TRN.TRANSFER_LOCATOR_ID TO_LOC_ID,
  TRN.ORGANIZATION_ID ORG_ID,
  TRN.PICKING_LINE_ID PICKING_LINE_ID,
  TRN.TRANSACTION_SOURCE_TYPE_ID TRX_SRC_TYPID,
  TRN.SHIPMENT_NUMBER DELIVERY_ID,
  TRN.TRANSACTION_QUANTITY TRX_QTY,
  TRN.REVISION REVISION
FROM MTL_MATERIAL_TRANSACTIONS TRN,
  MTL_SYSTEM_ITEMS ITM,
  OE_DROP_SHIP_SOURCES DRP,
  PO_HEADERS_ALL POH
WHERE TRN.TRANSACTION_TYPE_ID      = 18
AND TRN.TRANSACTION_SOURCE_TYPE_ID = 1
AND TRN.TRANSACTION_SOURCE_ID      = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID               = DRP.PO_HEADER_ID
AND DRP.HEADER_ID                  = &SalesOrder_Header_ID
AND TRN.ORGANIZATION_ID            = ITM.ORGANIZATION_ID
AND TRN.INVENTORY_ITEM_ID          = ITM.INVENTORY_ITEM_ID
UNION ALL
SELECT TRN.TRANSACTION_ID MTL_TRNS_ID,
  TRN.MOVE_ORDER_LINE_ID MOVE_LINE_ID,
  DECODE(TRN.TRANSACTION_TYPE_ID, 52,'Stage Trans', 33,'SO Issue', 15,'RMA Receipt', 18,'PO Receipt', 'Invalid '
  ||TO_CHAR(TRN.TRANSACTION_TYPE_ID)) TRANS_TYPE,
  TRN.PICK_SLIP_NUMBER PICK_SLIP,
  TRN.TRX_SOURCE_LINE_ID TRX_SOURCE_LINE_ID,
  TRN.TRX_SOURCE_LINE_ID LINE_ID,
  TRN.PRIMARY_QUANTITY PRM_Q,
  TRN.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TRN.SECONDARY_TRANSACTION_QUANTITY "SECONDARY TRANS_QTY",
  TRN.SUBINVENTORY_CODE FROM_SUB,
  TRN.LOCATOR_ID FROM_LOC_ID,
  TRN.TRANSFER_SUBINVENTORY TO_SUB,
  TRN.TRANSFER_LOCATOR_ID TO_LOC_ID,
  TRN.ORGANIZATION_ID ORG_ID,
  TRN.PICKING_LINE_ID PICKING_LINE_ID,
  TRN.TRANSACTION_SOURCE_TYPE_ID TRX_SRC_TYPID,
  TRN.SHIPMENT_NUMBER DELIVERY_ID,
  TRN.TRANSACTION_QUANTITY TRX_QTY,
  TRN.REVISION REVISION
FROM MTL_MATERIAL_TRANSACTIONS TRN,
  MTL_SYSTEM_ITEMS ITM,
  MTL_RESERVATIONS RES,
  PO_HEADERS_ALL POH
WHERE RES.DEMAND_SOURCE_HEADER_ID  = &SalesOrder_Header_ID
AND RES.DEMAND_SOURCE_TYPE_ID      = 2
AND RES.SUPPLY_SOURCE_TYPE_ID     IN (1,13)
AND RES.SUPPLY_SOURCE_HEADER_ID    = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID               = TRN.TRANSACTION_SOURCE_ID
AND TRN.ORGANIZATION_ID            = ITM.ORGANIZATION_ID
AND TRN.INVENTORY_ITEM_ID          = ITM.INVENTORY_ITEM_ID
AND TRN.TRANSACTION_TYPE_ID        = 18
AND TRN.TRANSACTION_SOURCE_TYPE_ID = 1;

--MTL_UNIT_TRANSACTIONS (unt)
 SELECT UNT.TRANSACTION_ID TRANS_ID ,
  UNT.STATUS_ID STATUS_ID ,
  UNT.SUBINVENTORY_CODE SUBINV ,
  UNT.LOCATOR_ID LOC_ID ,
  UNT.SERIAL_NUMBER SERIAL_NUM ,
  UNT.INVENTORY_ITEM_ID ITEM_ID ,
  UNT.ORGANIZATION_ID WH_ID ,
  TO_CHAR(UNT.TRANSACTION_DATE,'DD-MON-RR HH24:MI:SS') TRX_DATE ,
  UNT.TRANSACTION_SOURCE_ID TRX_SRC_ID ,
  UNT.TRANSACTION_SOURCE_TYPE_ID TRX_SRC_TYPE_ID ,
  UNT.RECEIPT_ISSUE_TYPE RECEIPT_ISSUE_TYPE ,
  UNT.CUSTOMER_ID CUST_ID ,
  UNT.SHIP_ID SHIP_ID
FROM MTL_UNIT_TRANSACTIONS UNT
WHERE UNT.TRANSACTION_ID IN
  (SELECT TRN.TRANSACTION_ID
  FROM MTL_MATERIAL_TRANSACTIONS TRN,
    MTL_SYSTEM_ITEMS ITM
  WHERE (TRN.TRX_SOURCE_LINE_ID,TRN.ORGANIZATION_ID,TRN.INVENTORY_ITEM_ID) IN
    (SELECT DISTINCT LINE_ID,
      SHIP_FROM_ORG_ID,
      INVENTORY_ITEM_ID
    FROM OE_ORDER_LINES LIN1
    WHERE LIN1.HEADER_ID = &SalesOrder_Header_ID
    AND NVL( 0 ,0)      IN (0,LIN1.LINE_ID, LIN1.TOP_MODEL_LINE_ID, LIN1.ATO_LINE_ID, LIN1.LINK_TO_LINE_ID, LIN1.REFERENCE_LINE_ID, LIN1.SERVICE_REFERENCE_LINE_ID)
    )
  AND TRN.ORGANIZATION_ID   = ITM.ORGANIZATION_ID
  AND TRN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
  );
 
--MTL_TXN_REQUEST_LINES_V (mov) - Move Transactions
 SELECT DISTINCT TRL.LINE_ID MO_LINE_ID,
  TRH.REQUEST_NUMBER MO_NUMBER,
  TRL.HEADER_ID MV_HDR_ID,
  TRL.LINE_NUMBER MV_LINE_NUM,
  DECODE(TRL.LINE_STATUS, 1, '1=Incomplete', 2, '2=Pend Aprvl', 3, '3=Approved', 4, '4=Not Apprvd', 5, '5=Closed', 6, '6=Canceled', 7, '7=Pre Apprvd', 8, '8=Part Aprvd', 9, '9=Cncld Source', TRL.LINE_STATUS) MV_LINE_STAT,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, '.'
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, '.'
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, '.',NULL)
  || '.'
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, '.' , NULL)
  || DECODE(LIN.option_number, NULL, '.', NULL )
  || '.'
  || TO_CHAR(LIN.service_number)) LINE,
  TRL.TXN_SOURCE_LINE_ID "TXN_SRC LINE_ID",
  ITM.SEGMENT1 ITEM,
  TRL.QUANTITY QTY,
  TRL.PRIMARY_QUANTITY "PRM QTY",
  TRL.QUANTITY_DELIVERED "DLV QTY",
  TRL.QUANTITY_DETAILED "DTL QTY",
  TRL.SECONDARY_QUANTITY "SECONDARY QUANTITY",
  TRL.SECONDARY_QUANTITY_DETAILED "SECONDARY QTY_DETAIL",
  TRL.SECONDARY_QUANTITY_DELIVERED "SECONDARY QTY_DELIV",
  TRL.SECONDARY_REQUIRED_QUANTITY "SECONDARY REQ_QTY",
  TRL.SECONDARY_UOM_CODE "SECONDARY UOM_CODE",
  TRL.MOVE_ORDER_TYPE_NAME MOVE_TYPE_NAME,
  DECODE(TRL.TRANSACTION_SOURCE_TYPE_ID,2,'Sales Order',TRL.TRANSACTION_SOURCE_TYPE_ID) TRNS_SRC_TYPE,
  TRL.TRANSACTION_TYPE_NAME TRNS_TYPE_NAME,
  DECODE(TRL.TRANSACTION_ACTION_ID,28,'Staging Xfr', TRL.TRANSACTION_ACTION_ID) TRNS_ACTION,
  TRL.ORGANIZATION_ID WH_ID,
  TRL.FROM_SUBINVENTORY_CODE FROM_SUB,
  TRL.FROM_LOCATOR_ID FROM_LOC_ID,
  TRL.TO_SUBINVENTORY_CODE TO_SUB,
  TRL.TO_LOCATOR_ID TO_LOC_ID,
  TRL.LOT_NUMBER LOT_NUM,
  TRL.TRANSACTION_HEADER_ID TRNS_HEAD_ID
FROM MTL_TXN_REQUEST_LINES_V TRL,
  MTL_TXN_REQUEST_HEADERS TRH,
  WSH_DELIVERY_DETAILS DET,
  OE_ORDER_LINES LIN,
  MTL_SYSTEM_ITEMS ITM
WHERE TRL.LINE_ID         = DET.MOVE_ORDER_LINE_ID
AND LIN.SHIP_FROM_ORG_ID  = ITM.ORGANIZATION_ID(+)
AND LIN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID(+)
AND DET.SOURCE_LINE_ID    = LIN.LINE_ID
AND TRL.HEADER_ID         = TRH.HEADER_ID
AND LIN.HEADER_ID         = &SalesOrder_Header_ID
AND NVL( 0 ,0)           IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID);

--RA_INTERFACE_LINES (rai) - Receivables Interface Lines
 SELECT DISTINCT RAI.INTERFACE_LINE_ID INTF_LINE_ID,
  RAI.BATCH_SOURCE_NAME BATCH_SOURCE,
  RAI.INVENTORY_ITEM_ID ITEM_ID,
  RAI.QUANTITY QTY,
  RAI.QUANTITY_ORDERED ORD_Q,
  RAI.UOM_CODE UOM,
  RAI.AMOUNT PRICE,
  trim(RAI.SALES_ORDER_LINE) SO_LIN,
  RAI.ACCOUNTING_RULE_ID AR_ID,
  RAI.INVOICING_RULE_ID IR_ID,
  RAI.LINE_TYPE LINE_TYPE,
  RAI.INTERFACE_LINE_ATTRIBUTE1 Order_Num_1,
  RAI.INTERFACE_LINE_ATTRIBUTE2 Order_Type_2,
  RAI.INTERFACE_LINE_ATTRIBUTE3 Delivery_3,
  RAI.INTERFACE_LINE_ATTRIBUTE4 WayBill_4,
  RAI.INTERFACE_LINE_ATTRIBUTE6 Line_ID_5,
  RAI.INTERFACE_LINE_ATTRIBUTE7 Pick_Line_Id_7,
  RAI.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading_8,
  RAI.INTERFACE_LINE_ATTRIBUTE10 WH_ID_10,
  RAI.INTERFACE_LINE_ATTRIBUTE11 PA_ID_11,
  RAI.interface_line_context context,
  RAI.CONVERSION_RATE C_RATE,
  TO_CHAR(RAI.CONVERSION_DATE,'DD-MON-RR HH24:MI:SS') CONVERSION_DATE,
  RAI.CURRENCY_CODE CURR,
  RAI.unit_selling_price sell_price,
  RAI.purchase_order cust_po,
  RAI.reference_line_id credit_invoice,
  RAI.customer_bank_account_id cbai,
  RAI.receipt_method_id rmi,
  RAI.interface_status INTERFACE_STAT,
  RAI.TAX_RATE TR,
  RAI.SALES_TAX_ID S_TAX_ID,
  RAI.VAT_TAX_ID VAT_ID,
  RAI.TAX_EXEMPT_FLAG EF,
  RAI.TERRITORY_ID TERR_ID
FROM RA_INTERFACE_LINES RAI,
  OE_ORDER_LINES LIN,
  OE_ORDER_HEADERS ORD,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER                     = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ATTRIBUTE2         = TYP.NAME
AND TYP.TRANSACTION_TYPE_ID               = ORD.ORDER_TYPE_ID
AND ORD.HEADER_ID                         = &SalesOrder_Header_ID
AND RAI.LINE_TYPE                         = 'LINE'
AND (NVL( 0 ,0)                          IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND NVL(RAI.INTERFACE_LINE_ATTRIBUTE6,0) IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID))
AND LIN.HEADER_ID                         = ORD.HEADER_ID
UNION
SELECT DISTINCT RAI.INTERFACE_LINE_ID INTF_LINE_ID,
  RAI.BATCH_SOURCE_NAME BATCH_SOURCE,
  RAI.INVENTORY_ITEM_ID ITEM_ID,
  RAI.QUANTITY QTY,
  RAI.QUANTITY_ORDERED ORD_Q,
  RAI.UOM_CODE UOM,
  RAI.AMOUNT PRICE,
  trim(RAI.SALES_ORDER_LINE) SO_LIN,
  RAI.ACCOUNTING_RULE_ID AR_ID,
  RAI.INVOICING_RULE_ID IR_ID,
  RAI.LINE_TYPE LINE_TYPE,
  RAI.INTERFACE_LINE_ATTRIBUTE1 Order_Num_1,
  RAI.INTERFACE_LINE_ATTRIBUTE2 Order_Type_2,
  RAI.INTERFACE_LINE_ATTRIBUTE3 Delivery_3,
  RAI.INTERFACE_LINE_ATTRIBUTE4 WayBill_4,
  RAI.INTERFACE_LINE_ATTRIBUTE6 Line_ID_5,
  RAI.INTERFACE_LINE_ATTRIBUTE7 Pick_Line_Id_7,
  RAI.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading_8,
  RAI.INTERFACE_LINE_ATTRIBUTE10 WH_ID_10,
  RAI.INTERFACE_LINE_ATTRIBUTE11 PA_ID_11,
  RAI.interface_line_context context,
  RAI.CONVERSION_RATE C_RATE,
  TO_CHAR(RAI.CONVERSION_DATE,'DD-MON-RR HH24:MI:SS') CONVERSION_DATE,
  RAI.CURRENCY_CODE CURR,
  RAI.unit_selling_price sell_price,
  RAI.purchase_order cust_po,
  RAI.reference_line_id credit_invoice,
  RAI.customer_bank_account_id cbai,
  RAI.receipt_method_id rmi,
  RAI.interface_status INTERFACE_STAT,
  RAI.TAX_RATE TR,
  RAI.SALES_TAX_ID S_TAX_ID,
  RAI.VAT_TAX_ID VAT_ID,
  RAI.TAX_EXEMPT_FLAG EF,
  RAI.TERRITORY_ID TERR_ID
FROM RA_INTERFACE_LINES RAI,
  OE_ORDER_LINES LIN,
  OE_ORDER_HEADERS ORD,
  OE_PRICE_ADJUSTMENTS ADJ,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER                        = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ATTRIBUTE2            = TYP.NAME
AND TYP.TRANSACTION_TYPE_ID                  = ORD.ORDER_TYPE_ID
AND ORD.HEADER_ID                            = &SalesOrder_Header_ID
AND RAI.LINE_TYPE                            = 'FREIGHT'
AND NVL( 0 ,0)                              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND to_number(RAI.INTERFACE_LINE_ATTRIBUTE6) = ADJ.PRICE_ADJUSTMENT_ID
AND ADJ.LINE_ID                              = LIN.LINE_ID
AND ADJ.LINE_ID                             IS NOT NULL
AND ADJ.HEADER_ID                            = ORD.HEADER_ID
AND LIN.HEADER_ID                            = ORD.HEADER_ID
UNION
SELECT DISTINCT RAI.INTERFACE_LINE_ID INTF_LINE_ID,
  RAI.BATCH_SOURCE_NAME BATCH_SOURCE,
  RAI.INVENTORY_ITEM_ID ITEM_ID,
  RAI.QUANTITY QTY,
  RAI.QUANTITY_ORDERED ORD_Q,
  RAI.UOM_CODE UOM,
  RAI.AMOUNT PRICE,
  trim(RAI.SALES_ORDER_LINE) SO_LIN,
  RAI.ACCOUNTING_RULE_ID AR_ID,
  RAI.INVOICING_RULE_ID IR_ID,
  RAI.LINE_TYPE LINE_TYPE,
  RAI.INTERFACE_LINE_ATTRIBUTE1 Order_Num_1,
  RAI.INTERFACE_LINE_ATTRIBUTE2 Order_Type_2,
  RAI.INTERFACE_LINE_ATTRIBUTE3 Delivery_3,
  RAI.INTERFACE_LINE_ATTRIBUTE4 WayBill_4,
  RAI.INTERFACE_LINE_ATTRIBUTE6 Line_ID_5,
  RAI.INTERFACE_LINE_ATTRIBUTE7 Pick_Line_Id_7,
  RAI.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading_8,
  RAI.INTERFACE_LINE_ATTRIBUTE10 WH_ID_10,
  RAI.INTERFACE_LINE_ATTRIBUTE11 PA_ID_11,
  RAI.interface_line_context context,
  RAI.CONVERSION_RATE C_RATE,
  TO_CHAR(RAI.CONVERSION_DATE,'DD-MON-RR HH24:MI:SS') CONVERSION_DATE,
  RAI.CURRENCY_CODE CURR,
  RAI.unit_selling_price sell_price,
  RAI.purchase_order cust_po,
  RAI.reference_line_id credit_invoice,
  RAI.customer_bank_account_id cbai,
  RAI.receipt_method_id rmi,
  RAI.interface_status INTERFACE_STAT,
  RAI.TAX_RATE TR,
  RAI.SALES_TAX_ID S_TAX_ID,
  RAI.VAT_TAX_ID VAT_ID,
  RAI.TAX_EXEMPT_FLAG EF,
  RAI.TERRITORY_ID TERR_ID
FROM RA_INTERFACE_LINES RAI,
  OE_ORDER_HEADERS ORD,
  OE_PRICE_ADJUSTMENTS ADJ,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER             = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ATTRIBUTE2 = TYP.NAME
AND TYP.TRANSACTION_TYPE_ID       = ORD.ORDER_TYPE_ID
AND RAI.LINE_TYPE                 = 'FREIGHT'
AND ADJ.HEADER_ID                 = &SalesOrder_Header_ID
AND ADJ.LINE_ID                  IS NULL
AND ORD.HEADER_ID                 = ADJ.HEADER_ID;

--RA_INTERFACE_ERRORS (rae) - Receivables Interface Errors  
 SELECT DISTINCT RAE.INTERFACE_LINE_ID,
  RAE.INTERFACE_SALESCREDIT_ID,
  RAE.MESSAGE_TEXT
FROM RA_INTERFACE_LINES RAI,
  RA_INTERFACE_ERRORS RAE,
  OE_ORDER_LINES LIN,
  OE_ORDER_HEADERS ORD,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER                     = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ID                 = RAE.INTERFACE_LINE_ID
AND RAI.INTERFACE_LINE_ATTRIBUTE2         = TYP.NAME
AND TYP.TRANSACTION_TYPE_ID               = ORD.ORDER_TYPE_ID
AND ORD.HEADER_ID                         = &SalesOrder_Header_ID
AND (NVL( 0 ,0)                          IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND NVL(RAI.INTERFACE_LINE_ATTRIBUTE6,0) IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID))
AND LIN.HEADER_ID                         = ORD.HEADER_ID
UNION
SELECT DISTINCT RAE.INTERFACE_LINE_ID,
  RAE.INTERFACE_SALESCREDIT_ID,
  RAE.MESSAGE_TEXT
FROM RA_INTERFACE_LINES RAI,
  RA_INTERFACE_ERRORS RAE,
  OE_ORDER_LINES LIN,
  OE_ORDER_HEADERS ORD,
  OE_PRICE_ADJUSTMENTS ADJ,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER                        = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ATTRIBUTE2            = TYP.NAME
AND RAI.INTERFACE_LINE_ID                    = RAE.INTERFACE_LINE_ID
AND TYP.TRANSACTION_TYPE_ID                  = ORD.ORDER_TYPE_ID
AND ORD.HEADER_ID                            = &SalesOrder_Header_ID
AND RAI.LINE_TYPE                            = 'FREIGHT'
AND NVL( 0 ,0)                              IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND to_number(RAI.INTERFACE_LINE_ATTRIBUTE6) = ADJ.PRICE_ADJUSTMENT_ID
AND ADJ.LINE_ID                              = LIN.LINE_ID
AND ADJ.LINE_ID                             IS NOT NULL
AND ADJ.HEADER_ID                            = ORD.HEADER_ID
AND LIN.HEADER_ID                            = ORD.HEADER_ID
UNION
SELECT DISTINCT RAE.INTERFACE_LINE_ID,
  RAE.INTERFACE_SALESCREDIT_ID,
  RAE.MESSAGE_TEXT
FROM RA_INTERFACE_LINES RAI,
  RA_INTERFACE_ERRORS RAE,
  OE_ORDER_HEADERS ORD,
  OE_PRICE_ADJUSTMENTS ADJ,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAI.SALES_ORDER             = TO_CHAR(ORD.ORDER_NUMBER)
AND RAI.INTERFACE_LINE_ATTRIBUTE2 = TYP.NAME
AND RAI.INTERFACE_LINE_ID         = RAE.INTERFACE_LINE_ID
AND TYP.TRANSACTION_TYPE_ID       = ORD.ORDER_TYPE_ID
AND RAI.LINE_TYPE                 = 'FREIGHT'
AND ADJ.HEADER_ID                 = ORD.HEADER_ID
AND ADJ.LINE_ID                  IS NULL
AND ORD.HEADER_ID                 = &SalesOrder_Header_ID;

--RA_CUSTOMER_TRX (rah) - Invoice Headers
 SELECT DISTINCT RAH.CUSTOMER_TRX_ID CUST_TRX_ID,
  RAH.TRX_NUMBER TRX_NUMBER,
  RAH.CUST_TRX_TYPE_ID TRX_TYPE_ID,
  TO_CHAR(RAH.TRX_DATE,'DD-MON-RR HH24:MI:SS') TRX_DATE,
  RAH.BATCH_ID BATCH_ID,
  RAH.BATCH_SOURCE_ID SOURCE_ID,
  RAH.BILL_TO_CUSTOMER_ID BILL_CUST,
  RAH.BILL_TO_SITE_USE_ID BILL_SITE,
  RAH.SHIP_TO_CUSTOMER_ID SHIP_CUST,
  RAH.SHIP_TO_SITE_USE_ID SHIP_SITE,
  RAH.TERM_ID TERM_ID,
  RAH.PRIMARY_SALESREP_ID SALESREP_ID,
  RAH.PURCHASE_ORDER PO_NUMBER,
  RAH.INVOICE_CURRENCY_CODE CURR,
  RAH.AGREEMENT_ID AGREEMENT,
  RAH.COMPLETE_FLAG COMP_FL,
  RAH.INVOICING_RULE_ID IR_ID,
  RAH.SHIP_VIA SHIP_VIA,
  RAH.WAYBILL_NUMBER WAYBILL,
  RAH.STATUS_TRX STATUS
FROM RA_CUSTOMER_TRX RAH,
  RA_CUSTOMER_TRX_LINES RAL,
  OE_ORDER_LINES LIN,
  OE_ORDER_HEADERS ORD,
  OE_TRANSACTION_TYPES_V TYP
WHERE RAH.CUSTOMER_TRX_ID                 = RAL.CUSTOMER_TRX_ID
AND RAL.SALES_ORDER                       = TO_CHAR(ORD.ORDER_NUMBER)
AND RAL.INTERFACE_LINE_ATTRIBUTE2         = TYP.NAME
AND TYP.TRANSACTION_TYPE_ID               = ORD.ORDER_TYPE_ID
AND ORD.HEADER_ID                         = LIN.HEADER_ID
AND NVL( 0 ,0)                           IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND NVL(RAL.INTERFACE_LINE_ATTRIBUTE6,0) IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
AND LIN.HEADER_ID                         = &SalesOrder_Header_ID
ORDER BY RAH.TRX_NUMBER;

--RA_CUSTOMER_TRX_LINES (ral) - Invoice Lines
 SELECT DISTINCT RAL.CUSTOMER_TRX_LINE_ID TRX_LINE_ID,
  RAL.LINK_TO_CUST_TRX_LINE_ID LINK_TO_ID,
  RAL.CUSTOMER_TRX_ID CUST_TRX_ID,
  RAH.TRX_NUMBER TRX_NUMBER,
  RAL.SALES_ORDER_SOURCE SOURCE,
  RAL.LINE_NUMBER LINE_NUM,
  RAL.INVENTORY_ITEM_ID ITEM_ID,
  RAL.QUANTITY_ORDERED ORD_Q,
  RAL.QUANTITY_INVOICED INV_Q,
  RAL.QUANTITY_CREDITED CRD_Q,
  RAL.UOM_CODE UOM,
  RAL.UNIT_SELLING_PRICE PRICE,
  RAL.EXTENDED_AMOUNT EXTD_AMT,
  RAL.REVENUE_AMOUNT REV_AMT,
  TRIM(RAL.SALES_ORDER_LINE) SO_LIN,
  RAL.LINE_TYPE LINE_TYPE,
  RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num_1,
  RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type_2,
  RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery_3,
  RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill_4,
  RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID_5,
  RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading_8,
  RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID_10,
  RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID_11,
  RAL.TAXABLE_FLAG TF,
  RAL.TAX_RATE TR,
  RAL.VAT_TAX_ID VAT_ID,
  RAL.SALES_TAX_ID S_TAX_ID
FROM RA_CUSTOMER_TRX RAH,
  RA_CUSTOMER_TRX_LINES RAL
WHERE RAH.CUSTOMER_TRX_ID     = RAL.CUSTOMER_TRX_ID
AND RAL.CUSTOMER_TRX_LINE_ID IN
  (SELECT RAL1.CUSTOMER_TRX_LINE_ID
  FROM RA_CUSTOMER_TRX RAH1,
    RA_CUSTOMER_TRX_LINES RAL1,
    OE_ORDER_LINES LIN,
    OE_ORDER_HEADERS ORD,
    OE_TRANSACTION_TYPES_V TYP
  WHERE RAH1.CUSTOMER_TRX_ID                 = RAL1.CUSTOMER_TRX_ID
  AND RAL1.SALES_ORDER                       = TO_CHAR(ORD.ORDER_NUMBER)
  AND RAL1.INTERFACE_LINE_ATTRIBUTE2         = TYP.NAME
  AND TYP.TRANSACTION_TYPE_ID                = ORD.ORDER_TYPE_ID
  AND ORD.HEADER_ID                          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)                            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  AND NVL(RAL1.INTERFACE_LINE_ATTRIBUTE6,0) IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  AND LIN.HEADER_ID                          = ORD.HEADER_ID
  )
UNION
SELECT DISTINCT RAL.CUSTOMER_TRX_LINE_ID TRX_LINE_ID,
  RAL.LINK_TO_CUST_TRX_LINE_ID LINK_TO_ID,
  RAL.CUSTOMER_TRX_ID CUST_TRX_ID,
  RAH.TRX_NUMBER TRX_NUMBER,
  RAL.SALES_ORDER_SOURCE SOURCE,
  RAL.LINE_NUMBER LINE_NUM,
  RAL.INVENTORY_ITEM_ID ITEM_ID,
  RAL.QUANTITY_ORDERED ORD_Q,
  RAL.QUANTITY_INVOICED INV_Q,
  RAL.QUANTITY_CREDITED CRD_Q,
  RAL.UOM_CODE UOM,
  RAL.UNIT_SELLING_PRICE PRICE,
  RAL.EXTENDED_AMOUNT EXTD_AMT,
  RAL.REVENUE_AMOUNT REV_AMT,
  TRIM(RAL.SALES_ORDER_LINE) SO_LIN,
  RAL.LINE_TYPE LINE_TYPE,
  RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num_1,
  RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type_2,
  RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery_3,
  RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill_4,
  RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID_5,
  RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading_8,
  RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID_10,
  RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID_11,
  RAL.TAXABLE_FLAG TF,
  RAL.TAX_RATE TR,
  RAL.VAT_TAX_ID VAT_ID,
  RAL.SALES_TAX_ID S_TAX_ID
FROM RA_CUSTOMER_TRX RAH,
  RA_CUSTOMER_TRX_LINES RAL
WHERE RAH.CUSTOMER_TRX_ID         = RAL.CUSTOMER_TRX_ID
AND RAL.LINK_TO_CUST_TRX_LINE_ID IN
  (SELECT RAL1.CUSTOMER_TRX_LINE_ID
  FROM RA_CUSTOMER_TRX RAH1,
    RA_CUSTOMER_TRX_LINES RAL1,
    OE_ORDER_LINES LIN,
    OE_ORDER_HEADERS ORD,
    OE_TRANSACTION_TYPES_V TYP
  WHERE RAH1.CUSTOMER_TRX_ID                 = RAL1.CUSTOMER_TRX_ID
  AND RAL1.SALES_ORDER                       = TO_CHAR(ORD.ORDER_NUMBER)
  AND RAL1.INTERFACE_LINE_ATTRIBUTE2         = TYP.NAME
  AND TYP.TRANSACTION_TYPE_ID                = ORD.ORDER_TYPE_ID
  AND ORD.HEADER_ID                          = &SalesOrder_Header_ID
  AND NVL( 0 ,0)                            IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  AND NVL(RAL1.INTERFACE_LINE_ATTRIBUTE6,0) IN (0,LIN.LINE_ID, LIN.TOP_MODEL_LINE_ID, LIN.ATO_LINE_ID, LIN.LINK_TO_LINE_ID, LIN.REFERENCE_LINE_ID, LIN.SERVICE_REFERENCE_LINE_ID)
  AND LIN.HEADER_ID                          = ORD.HEADER_ID
  )
ORDER BY TRX_NUMBER,
  TRX_LINE_