RCV_RECEIPT_ADVICE_LINES_V

Details

• Schema: FUSION

• Object owner: RCV

• Object type: VIEW

Columns

Name

ACTION_CODE EXT_SYS_GROUP_ID RECEIPT_SOURCE_CODE SOURCE_DOCUMENT_TYPE SOLDTO_LE_NAME DOCUMENT_NUMBER DOCUMENT_REVISION_NUMBER DOCUMENT_REVISION_DATE DOCUMENT_CREATION_DATE DOCUMENT_LAST_UPDATE_DATE RECEIPT_ADVICE_LINE_STATUS CO_SEQUENCE ORGANIZATION_CODE RECEIVING_LOCATION FROM_ORGANIZATION FROM_ORGANIZATION_ID CUSTOMER_PARTY_NAME SUPPLIER_NAME SUPPLIER_SITE_CODE CARRIER FREIGHT_TERMS NOTE_TO_RECEIVER COMMENTS DOCUMENT_LINE_NUMBER DOCUMENT_SCHEDULE_NUMBER TRANSFER_ORDER_NUMBER TRANSFER_ORDER_LINE_NUMBER SOURCE_HEADER_NUMBER SOURCE_LINE_NUMBER SOURCE_FULFIL_LINE_NUMBER ORIGINAL_SOURCE_HEADER_NUMBER ORIGINAL_SOURCE_LINE_NUMBER ORIG_SRC_FULFIL_LINE_NUMBER ORIGINAL_SALES_ORDER_NUMBER ORIG_SALES_ORDER_LINE_NUMBER DOCUMENT_LINE_CREATION_DATE DOCUMENT_LINE_LAST_UPDATE_DATE ITEM_NUMBER ITEM_DESCRIPTION ITEM_REVISION ITEM_CATEGORY SUPPLIER_ITEM_NUMBER CUSTOMER_ITEM_NUMBER UOM_CODE UNIT_OF_MEASURE QUANTITY EXPECTED_RECEIPT_DATE COUNTRY_OF_ORIGIN_CODE PRIMARY_UOM_CODE PRIMARY_UNIT_OF_MEASURE PRIMARY_QUANTITY SECONDARY_UOM_CODE SECONDARY_UNIT_OF_MEASURE SECONDARY_QUANTITY DAYS_EARLY_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED RECEIPT_DAYS_EXCEPTION_CODE ALLOW_SUBSTITUTE_ITEM_RECEIPT ENFORCE_SHIP_TO_LOCATION_CODE OVER_RECEIPT_TOLERANCE OVER_RECEIPT_EXCEPTION_CODE RECEIVING_ROUTING ROUTING_HEADER_ID EXTERNAL_INTERFACE_STATUS ORGANIZATION_ID SHIPMENT_LINE_ID PO_HEADER_ID PO_LINE_ID PO_LINE_LOCATION_ID INTRANSIT_SHIPMENT_HEADER_ID INTRANSIT_SHIPMENT_LINE_ID TRANSFER_ORDER_HEADER_ID TRANSFER_ORDER_LINE_ID RMA_HEADER_ID RMA_LINE_ID SUPPLIER_ID SUPPLIER_NUMBER SUPPLIER_SITE_ID CARRIER_ID CUSTOMER_ID ITEM_ID LOCATION_ID SUPPLY_SOURCE_TYPE_ID SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_LINE_ID PROJECT_ID TASK_ID ITEM_CATEGORY_ID ORG_PDSC_ENABLED SHIPPING_SHIPMENT_LINE LOTS_COUNT SERIALS_COUNT

Query

SQL_Statement

SELECT DECODE(POLL.EXTERNAL_SYS_RCV_INTF_CO_SEQ, NULL, DECODE(POLL.SCHEDULE_STATUS, 'ON HOLD', 'ON HOLD','CREATE'), 0, DECODE(POLL.SCHEDULE_STATUS, 'ON HOLD', 'ON HOLD','CREATE'), DECODE(POH.DOCUMENT_STATUS, 'ON HOLD', 'ON HOLD', 'CANCELED', 'CANCEL', DECODE(POLL.SCHEDULE_STATUS, 'CANCELED', 'CANCEL', 'UPDATE') ) ) AS ACTION_CODE , POLL.EXTERNAL_SYS_RCV_GROUP_ID AS EXT_SYS_GROUP_ID , 'VENDOR' AS RECEIPT_SOURCE_CODE , 'PO' AS SOURCE_DOCUMENT_TYPE , XEP.NAME AS SOLDTO_LE_NAME , POH.SEGMENT1 AS DOCUMENT_NUMBER , TO_CHAR(POH.REVISION_NUM) AS DOCUMENT_REVISION_NUMBER , POH.REVISED_DATE AS DOCUMENT_REVISION_DATE , POH.CREATION_DATE AS DOCUMENT_CREATION_DATE , POH.LAST_UPDATE_DATE AS DOCUMENT_LAST_UPDATE_DATE , POLL.SCHEDULE_STATUS AS RECEIPT_ADVICE_LINE_STATUS , POV.CO_SEQUENCE AS CO_SEQUENCE , IOP.ORGANIZATION_CODE AS ORGANIZATION_CODE , HRL.LOCATION_CODE AS RECEIVING_LOCATION , TO_CHAR(NULL) AS FROM_ORGANIZATION , TO_NUMBER(NULL) AS FROM_ORGANIZATION_ID , TO_CHAR(NULL) AS CUSTOMER_PARTY_NAME , HPS.PARTY_NAME AS SUPPLIER_NAME , POSSM.VENDOR_SITE_CODE AS SUPPLIER_SITE_CODE , WCV.CARRIER_NAME AS CARRIER , FTL.MEANING AS FREIGHT_TERMS , NVL(POLL.NOTE_TO_RECEIVER, POH.NOTE_TO_RECEIVER) AS NOTE_TO_RECEIVER , POH.COMMENTS AS COMMENTS , TO_CHAR(POL.LINE_NUM) AS DOCUMENT_LINE_NUMBER , TO_CHAR(POLL.SHIPMENT_NUM) AS DOCUMENT_SCHEDULE_NUMBER , TO_CHAR(NULL) AS TRANSFER_ORDER_NUMBER , TO_CHAR(NULL) AS TRANSFER_ORDER_LINE_NUMBER , TO_CHAR(NULL) AS SOURCE_HEADER_NUMBER , TO_CHAR(NULL) AS SOURCE_LINE_NUMBER , TO_CHAR(NULL) AS SOURCE_FULFIL_LINE_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SOURCE_HEADER_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SOURCE_LINE_NUMBER , TO_CHAR(NULL) AS ORIG_SRC_FULFIL_LINE_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SALES_ORDER_NUMBER , TO_CHAR(NULL) AS ORIG_SALES_ORDER_LINE_NUMBER , POLL.CREATION_DATE AS DOCUMENT_LINE_CREATION_DATE , POLL.LAST_UPDATE_DATE AS DOCUMENT_LINE_LAST_UPDATE_DATE , ESI.ITEM_NUMBER AS ITEM_NUMBER , POL.ITEM_DESCRIPTION AS ITEM_DESCRIPTION , POL.ITEM_REVISION AS ITEM_REVISION , EC.CATEGORY_NAME AS ITEM_CATEGORY , POL.VENDOR_PRODUCT_NUM AS SUPPLIER_ITEM_NUMBER , TO_CHAR(NULL) AS CUSTOMER_ITEM_NUMBER , UOMVL.UOM_CODE AS UOM_CODE , UOMVL.UNIT_OF_MEASURE AS UNIT_OF_MEASURE , (NVL(POLL.QUANTITY, 0) - NVL(POLL.QUANTITY_CANCELLED, 0)) AS QUANTITY , NVL(POLL.ANTICIPATED_ARRIVAL_DATE, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE)) AS EXPECTED_RECEIPT_DATE , POLL.COUNTRY_OF_ORIGIN_CODE AS COUNTRY_OF_ORIGIN_CODE , UOMVL1.UOM_CODE AS PRIMARY_UOM_CODE , UOMVL1.UNIT_OF_MEASURE AS PRIMARY_UNIT_OF_MEASURE , INV_CONVERT.INV_UM_CONVERT(POL.ITEM_ID , NULL , NVL(POLL.QUANTITY, 0) - NVL(POLL.QUANTITY_CANCELLED, 0) , UOMVL.UOM_CODE , UOMVL1.UOM_CODE , NULL , NULL) AS PRIMARY_QUANTITY , UOMVL2.UOM_CODE AS SECONDARY_UOM_CODE , UOMVL2.UNIT_OF_MEASURE AS SECONDARY_UNIT_OF_MEASURE , (NVL(POLL.SECONDARY_QUANTITY, 0) - NVL(POLL.SECONDARY_QUANTITY_CANCELLED, 0)) AS SECONDARY_QUANTITY , POLL.DAYS_EARLY_RECEIPT_ALLOWED AS DAYS_EARLY_RECEIPT_ALLOWED , POLL.DAYS_LATE_RECEIPT_ALLOWED AS DAYS_LATE_RECEIPT_ALLOWED , POLL.RECEIPT_DAYS_EXCEPTION_CODE AS RECEIPT_DAYS_EXCEPTION_CODE , POLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG AS ALLOW_SUBSTITUTE_ITEM_RECEIPT , POLL.ENFORCE_SHIP_TO_LOCATION_CODE AS ENFORCE_SHIP_TO_LOCATION_CODE , POLL.QTY_RCV_TOLERANCE AS OVER_RECEIPT_TOLERANCE , POLL.QTY_RCV_EXCEPTION_CODE AS OVER_RECEIPT_EXCEPTION_CODE , RRL.MEANING AS RECEIVING_ROUTING , POLL.RECEIVING_ROUTING_ID AS ROUTING_HEADER_ID , POLL.EXTERNAL_SYS_RCV_INTF_STATUS AS EXTERNAL_INTERFACE_STATUS , POLL.SHIP_TO_ORGANIZATION_ID AS ORGANIZATION_ID , -9998 AS SHIPMENT_LINE_ID , POH.PO_HEADER_ID AS PO_HEADER_ID , POL.PO_LINE_ID AS PO_LINE_ID , POLL.LINE_LOCATION_ID AS PO_LINE_LOCATION_ID , TO_NUMBER(NULL) AS INTRANSIT_SHIPMENT_HEADER_ID , TO_NUMBER(NULL) AS INTRANSIT_SHIPMENT_LINE_ID , TO_NUMBER(NULL) AS TRANSFER_ORDER_HEADER_ID , TO_NUMBER(NULL) AS TRANSFER_ORDER_LINE_ID , TO_NUMBER(NULL) AS RMA_HEADER_ID , TO_NUMBER(NULL) AS RMA_LINE_ID , POH.VENDOR_ID AS SUPPLIER_ID , POS.SEGMENT1 AS SUPPLIER_NUMBER , POH.VENDOR_SITE_ID AS SUPPLIER_SITE_ID , WCV.CARRIER_ID AS CARRIER_ID , TO_NUMBER(NULL) AS CUSTOMER_ID , POL.ITEM_ID AS ITEM_ID , POLL.SHIP_TO_LOCATION_ID AS LOCATION_ID , 1 AS SUPPLY_SOURCE_TYPE_ID , POH.PO_HEADER_ID AS SUPPLY_SOURCE_HEADER_ID , POLL.LINE_LOCATION_ID AS SUPPLY_SOURCE_LINE_ID , TO_NUMBER(NULL) AS PROJECT_ID , TO_NUMBER(NULL) AS TASK_ID , POL.CATEGORY_ID AS ITEM_CATEGORY_ID , NVL(IOP.PROJECT_REFERENCE_ENABLED, 2) AS ORG_PDSC_ENABLED , TO_NUMBER(NULL) AS SHIPPING_SHIPMENT_LINE , TO_NUMBER(NULL) LOTS_COUNT , TO_NUMBER(NULL) SERIALS_COUNT FROM PO_HEADERS_ALL POH , PO_VERSIONS POV , PO_LINES_ALL POL , PO_INTG_LINE_LOCATIONS_V POLL , INV_ORG_PARAMETERS IOP , HR_LOCATIONS_ALL_VL HRL , INV_UNITS_OF_MEASURE UOMVL , INV_UNITS_OF_MEASURE UOMVL1 , INV_UNITS_OF_MEASURE UOMVL2 , POZ_SUPPLIERS POS , HZ_PARTIES HPS , POZ_SUPPLIER_SITES_ALL_M POSSM , WSH_CARRIERS_V WCV , (SELECT LOOKUP_TYPE , LOOKUP_CODE , MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE ='FREIGHT TERMS' AND VIEW_APPLICATION_ID = 0 AND SET_ID = 0) FTL , (SELECT LOOKUP_TYPE , LOOKUP_CODE , MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE ='RCV_ROUTING_HEADERS' AND VIEW_APPLICATION_ID = 0 AND SET_ID = 0) RRL , EGP_SYSTEM_ITEMS_BV ESI , EGP_CATEGORIES_VL EC , XLE_ENTITY_PROFILES XEP WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POL.PO_LINE_ID = POLL.PO_LINE_ID AND POL.PO_HEADER_ID = POLL.PO_HEADER_ID AND POH.CURRENT_VERSION_ID = POV.VERSION_ID AND NOT (POH.DOCUMENT_STATUS = 'CANCELED' AND POV.CO_SEQUENCE < 10000) AND POLL.SHIP_TO_ORGANIZATION_ID = IOP.ORGANIZATION_ID AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID AND NVL(POH.ORCHESTRATION_ORDER_FLAG, 'N') = 'N' AND POLL.SHIPMENT_TYPE IN ('STANDARD','SCHEDULED') AND POLL.MATCHING_BASIS = 'QUANTITY' AND POLL.DESTINATION_TYPE_CODE = 'INVENTORY' AND POH.DOCUMENT_STATUS NOT IN ('INCOMPLETE','WITHDRAWN','PENDING APPROVAL','REJECTED','PENDING ACKNOWLEDGEMENT') AND (POLL.EXTERNAL_SYS_RCV_INTF_CO_SEQ IS NOT NULL OR (POLL.EXTERNAL_SYS_RCV_INTF_CO_SEQ IS NULL AND POH.DOCUMENT_STATUS NOT IN ('ON HOLD', 'CANCELED'))) AND POLL.SCHEDULE_STATUS IN ('OPEN', 'CLOSED FOR INVOICING', 'ON HOLD', 'CANCELED') AND (POLL.EXTERNAL_SYS_RCV_INTF_CO_SEQ IS NOT NULL OR (POLL.EXTERNAL_SYS_RCV_INTF_CO_SEQ IS NULL AND POLL.SCHEDULE_STATUS NOT IN ('ON HOLD', 'CANCELED'))) AND POLL.UOM_CODE = UOMVL.UOM_CODE AND POLL.SECONDARY_UOM_CODE = UOMVL2.UOM_CODE(+) AND POH.VENDOR_ID = POS.VENDOR_ID AND POS.PARTY_ID = HPS.PARTY_ID AND POH.VENDOR_ID <> NVL(IOP.SUPPLIER_ID, -1) AND POH.VENDOR_SITE_ID = POSSM.VENDOR_SITE_ID AND POH.CARRIER_ID = WCV.CARRIER_ID (+) AND POH.FREIGHT_TERMS_LOOKUP_CODE = FTL.LOOKUP_CODE(+) AND POLL.RECEIVING_ROUTING_ID = RRL.LOOKUP_CODE AND POL.ITEM_ID = ESI.INVENTORY_ITEM_ID AND ESI.ORGANIZATION_ID = POLL.SHIP_TO_ORGANIZATION_ID AND POL.CATEGORY_ID = EC.CATEGORY_ID AND POH.SOLDTO_LE_ID = XEP.LEGAL_ENTITY_ID AND ESI.PRIMARY_UOM_CODE = UOMVL1.UOM_CODE UNION ALL SELECT 'CREATE' AS ACTION_CODE , RSL.EXT_SYS_GROUP_ID AS EXT_SYS_GROUP_ID , RSH.RECEIPT_SOURCE_CODE AS RECEIPT_SOURCE_CODE , RSL.SOURCE_DOCUMENT_CODE AS SOURCE_DOCUMENT_TYPE , TO_CHAR(NULL) AS SOLDTO_LE_NAME , RSH.SHIPMENT_NUM AS DOCUMENT_NUMBER , TO_CHAR(NULL) AS DOCUMENT_REVISION_NUMBER , NULL AS DOCUMENT_REVISION_DATE , RSH.CREATION_DATE AS DOCUMENT_CREATION_DATE , RSH.LAST_UPDATE_DATE AS DOCUMENT_LAST_UPDATE_DATE , RSL.SHIPMENT_LINE_STATUS_CODE AS RECEIPT_ADVICE_LINE_STATUS , TO_NUMBER(NULL) AS CO_SEQUENCE , IOP_TO.ORGANIZATION_CODE AS ORGANIZATION_CODE , HRL_TO.LOCATION_CODE AS RECEIVING_LOCATION , IOP_FROM.ORGANIZATION_CODE AS FROM_ORGANIZATION , RSL.FROM_ORGANIZATION_ID AS FROM_ORGANIZATION_ID , TO_CHAR(NULL) AS CUSTOMER_PARTY_NAME , TO_CHAR(NULL) AS SUPPLIER_NAME , TO_CHAR(NULL) AS SUPPLIER_SITE_CODE , WCV.CARRIER_NAME AS CARRIER , FTL.MEANING AS FREIGHT_TERMS , DECODE(RSH.RECEIPT_SOURCE_CODE, 'TRANSFER ORDER', ITOL.NOTE_TO_RECEIVER, TO_CHAR(NULL)) AS NOTE_TO_RECEIVER , RSH.COMMENTS AS COMMENTS , TO_CHAR(RSL.LINE_NUM) AS DOCUMENT_LINE_NUMBER , TO_CHAR(NULL) AS DOCUMENT_SCHEDULE_NUMBER , ITOH.HEADER_NUMBER AS TRANSFER_ORDER_NUMBER , TO_CHAR(ITOL.LINE_NUMBER) AS TRANSFER_ORDER_LINE_NUMBER , TO_CHAR(NULL) AS SOURCE_HEADER_NUMBER , TO_CHAR(NULL) AS SOURCE_LINE_NUMBER , TO_CHAR(NULL) AS SOURCE_FULFIL_LINE_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SOURCE_HEADER_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SOURCE_LINE_NUMBER , TO_CHAR(NULL) AS ORIG_SRC_FULFIL_LINE_NUMBER , TO_CHAR(NULL) AS ORIGINAL_SALES_ORDER_NUMBER , TO_CHAR(NULL) AS ORIG_SALES_ORDER_LINE_NUMBER , RSL.CREATION_DATE AS DOCUMENT_LINE_CREATION_DATE , RSL.LAST_UPDATE_DATE AS DOCUMENT_LINE_LAST_UPDATE_DATE , ESI.ITEM_NUMBER AS ITEM_NUMBER , RSL.ITEM_DESCRIPTION AS ITEM_DESCRIPTION , RSL.ITEM_REVISION AS ITEM_REVISION , EC.CATEGORY_NAME AS ITEM_CATEGORY , TO_CHAR(NULL) AS SUPPLIER_ITEM_NUMBER , TO_CHAR(NULL) AS CUSTOMER_ITEM_NUMBER , UOMVL.UOM_CODE AS UOM_CODE , UOMVL.UNIT_OF_MEASURE AS UNIT_OF_MEASURE , RSL.QUANTITY_SHIPPED AS QUANTITY , RSH.EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE , RSL.COUNTRY_OF_ORIGIN_CODE AS COUNTRY_OF_ORIGIN_CODE , UOMVL1.UOM_CODE AS PRIMARY_UOM_CODE , UOMVL1.UNIT_OF_MEASURE AS PRIMARY_UNIT_OF_MEASURE , INV_CONVERT.INV_UM_CONVERT(RSL.ITEM_ID , NULL , RSL.QUANTITY_SHIPPED , UOMVL.UOM_CODE , UOMVL1.UOM_CODE , NULL , NULL) AS PRIMARY_QUANTITY , UOMVL2.UOM_CODE AS SECONDARY_UOM_CODE , UOMVL2.UNIT_OF_MEASURE AS SECONDARY_UNIT_OF_MEASURE , RSL.SECONDARY_QUANTITY_SHIPPED AS SECONDARY_QUANTITY , 0 AS DAYS_EARLY_RECEIPT_ALLOWED , 0 AS DAYS_LATE_RECEIPT_ALLOWED , 'NONE' AS RECEIPT_DAYS_EXCEPTION_CODE , 'N' AS ALLOW_SUBSTITUTE_ITEM_RECEIPT , 'NONE' AS ENFORCE_SHIP_TO_LOCATION_CODE , 0 AS OVER_RECEIPT_TOLERANCE , 'REJECT' AS OVER_RECEIPT_EXCEPTION_CODE , RRL.MEANING AS RECEIVING_ROUTING , RSL.ROUTING_HEADER_ID AS ROUTING_HEADER_ID , RSL.EXTERNAL_SYS_INTF_STATUS AS EXTERNAL_INTERFACE_STATUS , RSL.TO_ORGANIZATION_ID AS ORGANIZATION_ID , RSL.SHIPMENT_LINE_ID AS SHIPMENT_LINE_ID , TO_NUMBER(NULL) AS PO_HEADER_ID , TO_NUMBER(NULL) AS PO_LINE_ID , TO_NUMBER(NULL) AS PO_LINE_LOCATION_ID , RSH.SHIPMENT_HEADER_ID AS INTRANSIT_SHIPMENT_HEADER_ID , RSL.SHIPMENT_LINE_ID AS INTRANSIT_SHIPMENT_LINE_ID , RSL.TRANSFER_ORDER_HEADER_ID AS TRANSFER_ORDER_HEADER_ID , RSL.TRANSFER_ORDER_LINE_ID AS TRANSFER_ORDER_LINE_ID , TO_NUMBER(NULL) AS RMA_HEADER_ID , TO_NUMBER(NULL) AS RMA_LINE_ID , TO_NUMBER(NULL) AS SUPPLIER_ID , TO_CHAR(NULL) AS SUPPLIER_NUMBER , TO_NUMBER(NULL) AS SUPPLIER_SITE_ID , WCV.CARRIER_ID AS CARRIER_ID , TO_NUMBER(NULL) AS CUSTOMER_ID , RSL.ITEM_ID AS ITEM_ID , RSL.SHIP_TO_LOCATION_ID AS LOCATION_ID , 8 AS SUPPLY_SOURCE_TYPE_ID , RSL.TRANSFER_ORDER_HEADER_ID AS SUPPLY_SOURCE_HEADER_ID , RSL.TRANSFER_ORDER_LINE_ID AS SUPPLY_SOURCE_LINE_ID , RSL.PROJECT_ID AS PROJECT_ID , RSL.TASK_ID AS TASK_ID , RSL.CATEGORY_ID AS ITEM_CATEGORY_ID , NVL(IOP_TO.PROJECT_REFERENCE_ENABLED, 2) AS ORG_PDSC_ENABLED , DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY',TO_NUMBER(NULL), 'TRANSFER ORDER',(SELECT PICKING_LINE_ID FROM INV_MATERIAL_TXNS WHERE TRANSACTION_ID = RSL.MMT_TRANSACTION_ID) ) AS SHIPPING_SHIPMENT_LINE , (SELECT COUNT(1) FROM RCV_LOTS_SUPPLY WHERE SUPPLY_TYPE_CODE = 'SHIPMENT' and SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID) AS LOTS_COUNT , (SELECT COUNT(1) FROM RCV_SERIALS_SUPPLY WHERE SUPPLY_TYPE_CODE = 'SHIPMENT' and SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID) AS SERIALS_COUNT FROM RCV_SHIPMENT_HEADERS RSH , RCV_SHIPMENT_LINES RSL , INV_UNITS_OF_MEASURE UOMVL , INV_UNITS_OF_MEASURE UOMVL1 , INV_UNITS_OF_MEASURE UOMVL2 , INV_TRANSFER_ORDER_HEADERS ITOH , INV_TRANSFER_ORDER_LINES ITOL , INV_ORG_PARAMETERS IOP_TO , HR_LOCATIONS_ALL_VL HRL_TO , INV_ORG_PARAMETERS IOP_FROM , WSH_CARRIERS_V WCV , (SELECT LOOKUP_TYPE , LOOKUP_CODE , MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE ='FREIGHT TERMS' AND VIEW_APPLICATION_ID = 0 AND SET_ID = 0) FTL , (SELECT LOOKUP_TYPE , LOOKUP_CODE , MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE ='RCV_ROUTING_HEADERS' AND VIEW_APPLICATION_ID = 0 AND SET_ID = 0) RRL , EGP_SYSTEM_ITEMS_BV ESI , EGP_CATEGORIES_VL EC WHERE RSL.TO_ORGANIZATION_ID = IOP_TO.ORGANIZATION_ID AND RSL.SOURCE_DOCUMENT_CODE IN ('INVENTORY','TRANSFER ORDER') AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED', 'PARTIALLY RECEIVED') AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSH.RECEIPT_SOURCE_CODE IN ('INVENTORY','TRANSFER ORDER') AND RSL.ITEM_ID = ESI.INVENTORY_ITEM_ID AND ESI.ORGANIZATION_ID = RSL.TO_ORGANIZATION_ID AND RSL.UOM_CODE = UOMVL.UOM_CODE(+) AND RSL.PRIMARY_UOM_CODE = UOMVL1.UOM_CODE AND RSL.SECONDARY_UOM_CODE = UOMVL2.UOM_CODE(+) AND RSL.TRANSFER_ORDER_HEADER_ID = ITOH.HEADER_ID(+) AND RSL.TRANSFER_ORDER_LINE_ID = ITOL.LINE_ID(+) AND RSL.SHIP_TO_LOCATION_ID = HRL_TO.LOCATION_ID(+) AND RSL.FROM_ORGANIZATION_ID = IOP_FROM.ORGANIZATION_ID AND RSH.FREIGHT_CARRIER_ID = WCV.CARRIER_ID(+) AND RSH.FREIGHT_TERMS = FTL.LOOKUP_CODE(+) AND RSL.ROUTING_HEADER_ID = RRL.LOOKUP_CODE AND RSL.CATEGORY_ID = EC.CATEGORY_ID AND ( (RSL.SOURCE_DOCUMENT_CODE = 'INVENTORY') OR (RSL.SOURCE_DOCUMENT_CODE = 'TRANSFER ORDER' AND RSL.DESTINATION_TYPE_CODE='INVENTORY') ) UNION ALL SELECT DECODE(RAL.RA_LAST_ACTION_CODE, NULL, 'CREATE', 'UPDATE', 'UPDATE', 'CANCEL', 'CANCEL', 'CREATE') AS ACTION_CODE , RAL.EXT_SYS_GROUP_ID AS EXT_SYS_GROUP_ID , RAH.RECEIPT_SOURCE_CODE AS RECEIPT_SOURCE_CODE , RAL.SOURCE_DOCUMENT_CODE AS SOURCE_DOCUMENT_TYPE , TO_CHAR(NULL) AS SOLDTO_LE_NAME , RAH.RA_DOCUMENT_NUMBER AS DOCUMENT_NUMBER , RAH.RA_DOC_REVISION_NUMBER AS DOCUMENT_REVISION_NUMBER , RAH.RA_DOC_REVISION_DATE AS DOCUMENT_REVISION_DATE , RAH.RA_DOC_CREATION_DATE AS DOCUMENT_CREATION_DATE , RAH.RA_DOC_LAST_UPDATE_DATE AS DOCUMENT_LAST_UPDATE_DATE , RAL.SHIPMENT_LINE_STATUS_CODE AS RECEIPT_ADVICE_LINE_STATUS , TO_NUMBER(NULL) AS CO_SEQUENCE , IOP.ORGANIZATION_CODE AS ORGANIZATION_CODE , HRL.LOCATION_CODE AS RECEIVING_LOCATION , TO_CHAR(NULL) AS FROM_ORGANIZATION , TO_NUMBER(NULL) AS FROM_ORGANIZATION_ID , CUST.PARTY_NAME AS CUSTOMER_PARTY_NAME , TO_CHAR(NULL) AS SUPPLIER_NAME , TO_CHAR(NULL) AS SUPPLIER_SITE_CODE , WCV.CARRIER_NAME AS CARRIER , TO_CHAR(NULL) AS FREIGHT_TERMS , RAH.RA_NOTE_TO_RECEIVER AS NOTE_TO_RECEIVER , RAH.COMMENTS AS COMMENTS , RAL.RA_DOCUMENT_LINE_NUMBER AS DOCUMENT_LINE_NUMBER , RAL.RA_DOC_SCHEDULE_NUMBER AS DOCUMENT_SCHEDULE_NUMBER , TO_CHAR(NULL) AS TRANSFER_ORDER_NUMBER , TO_CHAR(NULL) AS TRANSFER_ORDER_LINE_NUMBER , RAL.RA_DOO_HEADER_NUMBER AS SOURCE_HEADER_NUMBER , RAL.RA_DOO_LINE_NUMBER AS SOURCE_LINE_NUMBER , RAL.RA_DOO_FULFILLMENT_LINE_NUMBER AS SOURCE_FULFIL_LINE_NUMBER , RAL.RA_ORIG_DOO_HEADER_NUMBER AS ORIGINAL_SOURCE_HEADER_NUMBER , RAL.RA_ORIG_DOO_LINE_NUMBER AS ORIGINAL_SOURCE_LINE_NUMBER , RAL.RA_ORIG_DOO_FULFIL_LINE_NUM AS ORIG_SRC_FULFIL_LINE_NUMBER , RAL.RA_ORIG_OC_HEADER_NUMBER AS ORIGINAL_SALES_ORDER_NUMBER , RAL.RA_ORIG_OC_LINE_NUMBER AS ORIG_SALES_ORDER_LINE_NUMBER , RAL.RA_DOC_LINE_CREATION_DATE AS DOCUMENT_LINE_CREATION_DATE , RAL.RA_DOC_LINE_LAST_UPDATE_DATE AS DOCUMENT_LINE_LAST_UPDATE_DATE , ESI.ITEM_NUMBER AS ITEM_NUMBER , RAL.ITEM_DESCRIPTION AS ITEM_DESCRIPTION , RAL.ITEM_REVISION AS ITEM_REVISION , EC.CATEGORY_NAME AS ITEM_CATEGORY , TO_CHAR(NULL) AS SUPPLIER_ITEM_NUMBER , TO_CHAR(NULL) AS CUSTOMER_ITEM_NUMBER , UOMVL.UOM_CODE AS UOM_CODE , UOMVL.UNIT_OF_MEASURE AS UNIT_OF_MEASURE , RAL.RA_QUANTITY_EXPECTED AS QUANTITY , RAL.RA_EXPECTED_RECEIPT_DATE AS EXPECTED_RECEIPT_DATE , RAL.COUNTRY_OF_ORIGIN_CODE AS COUNTRY_OF_ORIGIN_CODE , UOMVL1.UOM_CODE AS PRIMARY_UOM_CODE , UOMVL1.UNIT_OF_MEASURE AS PRIMARY_UNIT_OF_MEASURE , INV_CONVERT.INV_UM_CONVERT(RAL.ITEM_ID , NULL , RAL.RA_QUANTITY_EXPECTED , UOMVL.UOM_CODE , UOMVL1.UOM_CODE , NULL , NULL) AS PRIMARY_QUANTITY , UOMVL2.UOM_CODE AS SECONDARY_UOM_CODE , UOMVL2.UNIT_OF_MEASURE AS SECONDARY_UNIT_OF_MEASURE , RAL.RA_SECONDARY_QUANTITY_EXPECTED AS SECONDARY_QUANTITY , RAL.RA_DAYS_EARLY_RECEIPT_ALLOWED AS DAYS_EARLY_RECEIPT_ALLOWED , RAL.RA_DAYS_LATE_RECEIPT_ALLOWED AS DAYS_LATE_RECEIPT_ALLOWED , RAL.RA_RECEIPT_DAYS_EXCEPTION_CODE AS RECEIPT_DAYS_EXCEPTION_CODE , RAL.RA_ALLOW_SUBSTITUTE_RECEIPT AS ALLOW_SUBSTITUTE_ITEM_RECEIPT , RAL.RA_ENFORCE_SHIP_TO_LOC_CODE AS ENFORCE_SHIP_TO_LOCATION_CODE , 0 AS OVER_RECEIPT_TOLERANCE , 'REJECT' AS OVER_RECEIPT_EXCEPTION_CODE , RRL.MEANING AS RECEIVING_ROUTING , RAL.ROUTING_HEADER_ID AS ROUTING_HEADER_ID , RAL.EXTERNAL_SYS_INTF_STATUS AS EXTERNAL_INTERFACE_STATUS , RAL.TO_ORGANIZATION_ID AS ORGANIZATION_ID , RAL.SHIPMENT_LINE_ID AS SHIPMENT_LINE_ID , TO_NUMBER(NULL) AS PO_HEADER_ID , TO_NUMBER(NULL) AS PO_LINE_ID , TO_NUMBER(NULL) AS PO_LINE_LOCATION_ID , TO_NUMBER(NULL) AS INTRANSIT_SHIPMENT_HEADER_ID , TO_NUMBER(NULL) AS INTRANSIT_SHIPMENT_LINE_ID , TO_NUMBER(NULL) AS TRANSFER_ORDER_HEADER_ID , TO_NUMBER(NULL) AS TRANSFER_ORDER_LINE_ID , RAH.SHIPMENT_HEADER_ID AS RMA_HEADER_ID , RAL.SHIPMENT_LINE_ID AS RMA_LINE_ID , TO_NUMBER(NULL) AS SUPPLIER_ID , TO_CHAR(NULL) AS SUPPLIER_NUMBER , TO_NUMBER(NULL) AS SUPPLIER_SITE_ID , WCV.CARRIER_ID AS CARRIER_ID , RAH.CUSTOMER_ID AS CUSTOMER_ID , RAL.ITEM_ID AS ITEM_ID , RAL.SHIP_TO_LOCATION_ID AS LOCATION_ID , TO_NUMBER(NULL) AS SUPPLY_SOURCE_TYPE_ID , TO_NUMBER(NULL) AS SUPPLY_SOURCE_HEADER_ID , TO_NUMBER(NULL) AS SUPPLY_SOURCE_LINE_ID , TO_NUMBER(NULL) AS PROJECT_ID , TO_NUMBER(NULL) AS TASK_ID , RAL.CATEGORY_ID AS ITEM_CATEGORY_ID , NVL(IOP.PROJECT_REFERENCE_ENABLED, 2) AS ORG_PDSC_ENABLED , TO_NUMBER(NULL) AS SHIPPING_SHIPMENT_LINE , (SELECT COUNT(1) FROM RCV_LOTS_SUPPLY WHERE SUPPLY_TYPE_CODE = 'SHIPMENT' and SHIPMENT_LINE_ID = RAL.SHIPMENT_LINE_ID) AS LOTS_COUNT , (SELECT COUNT(1) FROM RCV_SERIALS_SUPPLY WHERE SUPPLY_TYPE_CODE = 'SHIPMENT' and SHIPMENT_LINE_ID = RAL.SHIPMENT_LINE_ID) AS SERIALS_COUNT FROM RCV_SHIPMENT_HEADERS RAH , RCV_SHIPMENT_LINES RAL , INV_UNITS_OF_MEASURE UOMVL , INV_UNITS_OF_MEASURE UOMVL1 , INV_UNITS_OF_MEASURE UOMVL2 , INV_ORG_PARAMETERS IOP , HR_LOCATIONS_ALL_VL HRL , HZ_PARTIES CUST , WSH_CARRIERS_V WCV , (SELECT LOOKUP_TYPE , LOOKUP_CODE , MEANING FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE ='RCV_ROUTING_HEADERS' AND VIEW_APPLICATION_ID = 0 AND SET_ID = 0) RRL , EGP_SYSTEM_ITEMS_BV ESI , EGP_CATEGORIES_VL EC WHERE RAL.TO_ORGANIZATION_ID = IOP.ORGANIZATION_ID AND RAL.SOURCE_DOCUMENT_CODE = 'RMA' AND RAH.SHIPMENT_HEADER_ID = RAL.SHIPMENT_HEADER_ID AND RAH.RECEIPT_SOURCE_CODE = 'CUSTOMER' AND RAH.RA_DOCUMENT_CODE = 'RMA' AND RAL.CUSTOMER_ID = CUST.PARTY_ID AND RAL.ITEM_ID = ESI.INVENTORY_ITEM_ID AND ESI.ORGANIZATION_ID = RAL.TO_ORGANIZATION_ID AND RAL.UOM_CODE = UOMVL.UOM_CODE AND RAL.PRIMARY_UOM_CODE = UOMVL1.UOM_CODE AND RAL.SECONDARY_UOM_CODE = UOMVL2.UOM_CODE(+) AND RAL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID AND RAH.FREIGHT_CARRIER_ID = WCV.CARRIER_ID(+) AND RAL.ROUTING_HEADER_ID = RRL.LOOKUP_CODE AND RAL.CATEGORY_ID = EC.CATEGORY_ID(+)