eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.RCV_VRC_TXS_V

Source

View Text - Preformatted


SELECT RT.ROWID , RT.CREATION_DATE , RT.CREATED_BY , RT.LAST_UPDATE_LOGIN , RT.LAST_UPDATE_DATE , RT.LAST_UPDATED_BY , RT.ORGANIZATION_ID , RT.PARENT_TRANSACTION_ID , RT.SHIPMENT_HEADER_ID , RT.SHIPMENT_LINE_ID , RT.INTERFACE_TRANSACTION_ID , RT.TRANSACTION_ID , RT.TRANSACTION_TYPE , RT.TRANSACTION_DATE , RT.QUANTITY TRANSACT_QTY , RT.UNIT_OF_MEASURE TRANSACT_UOM , RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM , RSL.ITEM_ID , RSL.ITEM_REVISION ITEM_REV , RSL.ITEM_DESCRIPTION ITEM_DESC , RSL.VENDOR_ITEM_NUM , RT.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE /*INVENTORY, WIP, EXPENSE*/ , RT.PO_HEADER_ID , RT.PO_LINE_ID , RT.PO_LINE_LOCATION_ID , RT.PO_DISTRIBUTION_ID , RT.PO_RELEASE_ID , RT.ROUTING_HEADER_ID , RT.LOCATION_ID SHIP_TO_LOCATION_ID , HRL1.LOCATION_CODE SHIP_TO_LOCATION , HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC , NVL(RSL.EMPLOYEE_ID,RT.EMPLOYEE_ID) , HRE1.FULL_NAME RECEIVER , RT.DELIVER_TO_LOCATION_ID , HRL2.LOCATION_CODE DELIVER_TO_LOCATION , HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC , RT.DELIVER_TO_PERSON_ID , HRE2.FULL_NAME DELIVER_TO_PERSON , RT.SUBINVENTORY , RT.LOCATOR_ID , /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE , RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.ORDERED_QUANTITY, POLL.QUANTITY) SOURCE_DOC_QTY , RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE /* INTERNAL ORDER, INVENTORY, VENDOR */ , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', RT.VENDOR_ID, 'RMA', NULL, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', POV.VENDOR_NAME, 'RMA', NULL, ORG.NAME) SUPPLIER , RT.VENDOR_ID , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POV.VENDOR_NAME) VENDOR_NAME , RT.VENDOR_SITE_ID SUPPLIER_SITE_ID , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POVS.VENDOR_SITE_CODE) SUPPLIER_SITE , RSL.FROM_ORGANIZATION_ID , RT.WIP_ENTITY_ID , RT.WIP_LINE_ID , RT.WIP_REPETITIVE_SCHEDULE_ID , RT.WIP_OPERATION_SEQ_NUM , RT.DEPARTMENT_CODE , RT.INSPECTION_STATUS_CODE , RT.REASON_ID , RT.COMMENTS , RT.VENDOR_LOT_NUM , RT.RMA_REFERENCE , RT.RECEIPT_EXCEPTION_FLAG , RSL.CATEGORY_ID , RSH.RECEIPT_NUM , RSL.CREATION_DATE RECEIPT_DATE , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POH.SEGMENT1) PO_NUM , TO_CHAR( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POL.LINE_NUM) ) PO_LINE , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POLL.SHIPMENT_NUM) ) PO_SHIPMENT , RSH.SHIPMENT_NUM , RSH.BILL_OF_LADING , NVL(RSL.PACKING_SLIP , RSH.PACKING_SLIP) PACKING_SLIP , RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POR.RELEASE_NUM) ) PO_RELEASE , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, PRH.SEGMENT1) REQUISITION_NUM , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, PRL.LINE_NUM) ) REQUISITION_LINE , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, PRL.REQUISITION_HEADER_ID) ) REQ_HEADER_ID , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, PRL.REQUISITION_LINE_ID) ) REQ_LINE_ID , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', POH.SEGMENT1, 'RMA', OEH.ORDER_NUMBER, PRH.SEGMENT1) ORDER_NUM , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', POL.LINE_NUM, 'RMA', OEL.LINE_NUMBER, PRL.LINE_NUM) ORDER_LINE , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.ORDER_QUANTITY_UOM, POL.UNIT_MEAS_LOOKUP_CODE) , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POL.HAZARD_CLASS_ID) ) , TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', NULL, POL.UN_NUMBER_ID) ) , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.SCHEDULE_ARRIVAL_DATE, POLL.NEED_BY_DATE) , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.PROMISE_DATE, POLL.PROMISED_DATE) , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.ORDERED_QUANTITY, POD.QUANTITY_ORDERED) , null /* REASON */ , RRH.ROUTING_NAME ROUTING , null /* HAZARD */ , null /* UN_NUM */ , null /* DISPLAYED_TRANS_TYPE */ , null /* DISPLAYED_DEST_TYPE */ , null /* DISPLAYED_SOURCE_DOC_CODE */ , null /* DISPLAYED_SOURCE_TYPE */ , RT.ATTRIBUTE_CATEGORY , RT.ATTRIBUTE1 , RT.ATTRIBUTE2 , RT.ATTRIBUTE3 , RT.ATTRIBUTE4 , RT.ATTRIBUTE5 , RT.ATTRIBUTE6 , RT.ATTRIBUTE7 , RT.ATTRIBUTE8 , RT.ATTRIBUTE9 , RT.ATTRIBUTE10 , RT.ATTRIBUTE11 , RT.ATTRIBUTE12 , RT.ATTRIBUTE13 , RT.ATTRIBUTE14 , RT.ATTRIBUTE15 , RT.CURRENCY_CODE , DCT.USER_CONVERSION_TYPE , RT.CURRENCY_CONVERSION_TYPE , RT.CURRENCY_CONVERSION_DATE , RT.CURRENCY_CONVERSION_RATE , FT.TERRITORY_SHORT_NAME , RT.OE_ORDER_HEADER_ID , OEH.ORDER_NUMBER || '-' || OEH.VERSION_NUMBER , RT.OE_ORDER_LINE_ID , OEL.LINE_NUMBER , RT.CUSTOMER_ID , SUBSTRB(HZP.PARTY_NAME, 1, 240) , RT.CUSTOMER_SITE_ID , RT.INSPECTION_QUALITY_CODE , RT.LPN_ID LPN_ID , null /* LICENSE_PLATE_NUMBER */ , RT.TRANSFER_LPN_ID TRANSFER_LPN_ID , null /* TRANSFER_LICENSE_PLATE_NUMBER */ , RT.SECONDARY_QUANTITY , RT.SECONDARY_UNIT_OF_MEASURE , RT.QC_GRADE , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', DECODE(OEOT.ORDER_CATEGORY_CODE , 'RETURN', OEH.SHIPPING_INSTRUCTIONS, NULL) , POH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER , DECODE(RT.SOURCE_DOCUMENT_CODE , 'RMA', OEL.SHIPPING_INSTRUCTIONS, POLL.NOTE_TO_RECEIVER) PLL_NOTE_TO_RECEIVER , RSL.TO_ORGANIZATION_ID , DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POLL.MATCHING_BASIS,NULL) MATCHING_BASIS , RT.AMOUNT , FCV.NAME , PJ.NAME , PJA.JOB_DESCRIPTION , DECODE(RT.SOURCE_DOCUMENT_CODE,'PO',POLL.ORG_ID, 'REQ',PRL.ORG_ID, 'RMA', OEH.ORG_ID ,NULL) , RT.LCM_SHIPMENT_LINE_ID , RT.UNIT_LANDED_COST FROM RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RSH , RCV_SHIPMENT_LINES RSL , OE_ORDER_HEADERS_ALL OEH , OE_ORDER_LINES_ALL OEL , OE_TRANSACTION_TYPES_ALL OEOT , OE_TRANSACTION_TYPES_TL OETL , HZ_CUST_ACCOUNTS HZCA , HZ_PARTIES HZP , PO_HEADERS_TRX_V POH , PO_LINES_TRX_V POL , PO_LINE_LOCATIONS_TRX_V POLL , PO_DISTRIBUTIONS_TRX_V POD , PO_RELEASES_ALL POR , PO_REQ_HEADERS_TRX_V PRH , PO_REQ_LINES_TRX_V PRL , PO_VENDORS POV , PO_VENDOR_SITES POVS , HR_LOCATIONS_ALL_TL HRL1 , HR_LOCATIONS_ALL_TL HRL2 , PER_PEOPLE_F HRE1 , PER_PEOPLE_F HRE2 , HR_ALL_ORGANIZATION_UNITS_TL ORG , RCV_ROUTING_HEADERS RRH , GL_DAILY_CONVERSION_TYPES DCT , FND_TERRITORIES_TL FT , FND_CURRENCIES_VL FCV , PER_JOBS PJ , PO_JOB_ASSOCIATIONS PJA WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID AND OEH.HEADER_ID (+) = RT.OE_ORDER_HEADER_ID AND OEL.LINE_ID (+) = RT.OE_ORDER_LINE_ID AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+) AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = USERENV('LANG') AND HZCA.CUST_ACCOUNT_ID (+) = RT.CUSTOMER_ID AND HZCA.PARTY_ID = HZP.PARTY_ID(+) AND POH.PO_HEADER_ID (+) = RT.PO_HEADER_ID AND POL.PO_LINE_ID (+) = RT.PO_LINE_ID AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID AND POV.VENDOR_ID (+) = RT.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID AND HRL1.LANGUAGE(+) = USERENV('LANG') AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID AND HRL2.LANGUAGE (+) = USERENV('LANG') AND HRE1.PERSON_ID (+) = RT.EMPLOYEE_ID AND FT.TERRITORY_CODE (+) = RT.COUNTRY_OF_ORIGIN_CODE AND NVL(FT.LANGUAGE, USERENV('LANG')) = USERENV('LANG') AND ( ( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL AND HRE1.NPW_NUMBER IS NOT NULL ) ) ) OR HRE1.PERSON_ID IS NULL ) AND HRE2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID AND ( ( TRUNC(SYSDATE) BETWEEN HRE2.EFFECTIVE_START_DATE AND HRE2.EFFECTIVE_END_DATE AND ( HRE2.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE2.EMPLOYEE_NUMBER IS NULL AND HRE2.NPW_NUMBER IS NOT NULL ) ) ) OR HRE2.PERSON_ID IS NULL ) AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID AND ORG.LANGUAGE (+) = USERENV('LANG') AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID AND DCT.CONVERSION_TYPE (+) = RT.CURRENCY_CONVERSION_TYPE AND FCV.CURRENCY_CODE (+) = RT.CURRENCY_CODE AND PJ.JOB_ID (+) = RT.JOB_ID AND PJA.JOB_ID (+) = RT.JOB_ID AND ( POH.PO_HEADER_ID IS NULL OR NOT( POL.order_type_lookup_code = 'RATE' AND POL.purchase_basis = 'TEMP LABOR' ) ) AND NVL(PRH.ORG_ID, -99) = NVL(PRL.ORG_ID, -99) AND NVL(POH.ORG_ID, -99) = NVL(POLL.ORG_ID, -99)

View Text - HTML Formatted

SELECT RT.ROWID
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.ORGANIZATION_ID
, RT.PARENT_TRANSACTION_ID
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.INTERFACE_TRANSACTION_ID
, RT.TRANSACTION_ID
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY TRANSACT_QTY
, RT.UNIT_OF_MEASURE TRANSACT_UOM
, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, RSL.ITEM_ID
, RSL.ITEM_REVISION ITEM_REV
, RSL.ITEM_DESCRIPTION ITEM_DESC
, RSL.VENDOR_ITEM_NUM
, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE /*INVENTORY
, WIP
, EXPENSE*/
, RT.PO_HEADER_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_RELEASE_ID
, RT.ROUTING_HEADER_ID
, RT.LOCATION_ID SHIP_TO_LOCATION_ID
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC
, NVL(RSL.EMPLOYEE_ID
, RT.EMPLOYEE_ID)
, HRE1.FULL_NAME RECEIVER
, RT.DELIVER_TO_LOCATION_ID
, HRL2.LOCATION_CODE DELIVER_TO_LOCATION
, HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC
, RT.DELIVER_TO_PERSON_ID
, HRE2.FULL_NAME DELIVER_TO_PERSON
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE
, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, POLL.QUANTITY) SOURCE_DOC_QTY
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE /* INTERNAL ORDER
, INVENTORY
, VENDOR */
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, RT.VENDOR_ID
, 'RMA'
, NULL
, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, 'RMA'
, NULL
, ORG.NAME) SUPPLIER
, RT.VENDOR_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POV.VENDOR_NAME) VENDOR_NAME
, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POVS.VENDOR_SITE_CODE) SUPPLIER_SITE
, RSL.FROM_ORGANIZATION_ID
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.INSPECTION_STATUS_CODE
, RT.REASON_ID
, RT.COMMENTS
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RT.RECEIPT_EXCEPTION_FLAG
, RSL.CATEGORY_ID
, RSH.RECEIPT_NUM
, RSL.CREATION_DATE RECEIPT_DATE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POH.SEGMENT1) PO_NUM
, TO_CHAR( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POL.LINE_NUM) ) PO_LINE
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POLL.SHIPMENT_NUM) ) PO_SHIPMENT
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) PACKING_SLIP
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POR.RELEASE_NUM) ) PO_RELEASE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRH.SEGMENT1) REQUISITION_NUM
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRL.LINE_NUM) ) REQUISITION_LINE
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRL.REQUISITION_HEADER_ID) ) REQ_HEADER_ID
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRL.REQUISITION_LINE_ID) ) REQ_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, PRH.SEGMENT1) ORDER_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, 'RMA'
, OEL.LINE_NUMBER
, PRL.LINE_NUM) ORDER_LINE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, POL.UNIT_MEAS_LOOKUP_CODE)
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POL.HAZARD_CLASS_ID) )
, TO_NUMBER( DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POL.UN_NUMBER_ID) )
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, POLL.NEED_BY_DATE)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.PROMISE_DATE
, POLL.PROMISED_DATE)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, POD.QUANTITY_ORDERED)
, NULL /* REASON */
, RRH.ROUTING_NAME ROUTING
, NULL /* HAZARD */
, NULL /* UN_NUM */
, NULL /* DISPLAYED_TRANS_TYPE */
, NULL /* DISPLAYED_DEST_TYPE */
, NULL /* DISPLAYED_SOURCE_DOC_CODE */
, NULL /* DISPLAYED_SOURCE_TYPE */
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, RT.CURRENCY_CODE
, DCT.USER_CONVERSION_TYPE
, RT.CURRENCY_CONVERSION_TYPE
, RT.CURRENCY_CONVERSION_DATE
, RT.CURRENCY_CONVERSION_RATE
, FT.TERRITORY_SHORT_NAME
, RT.OE_ORDER_HEADER_ID
, OEH.ORDER_NUMBER || '-' || OEH.VERSION_NUMBER
, RT.OE_ORDER_LINE_ID
, OEL.LINE_NUMBER
, RT.CUSTOMER_ID
, SUBSTRB(HZP.PARTY_NAME
, 1
, 240)
, RT.CUSTOMER_SITE_ID
, RT.INSPECTION_QUALITY_CODE
, RT.LPN_ID LPN_ID
, NULL /* LICENSE_PLATE_NUMBER */
, RT.TRANSFER_LPN_ID TRANSFER_LPN_ID
, NULL /* TRANSFER_LICENSE_PLATE_NUMBER */
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, RT.QC_GRADE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, POH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, POLL.NOTE_TO_RECEIVER) PLL_NOTE_TO_RECEIVER
, RSL.TO_ORGANIZATION_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POLL.MATCHING_BASIS
, NULL) MATCHING_BASIS
, RT.AMOUNT
, FCV.NAME
, PJ.NAME
, PJA.JOB_DESCRIPTION
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POLL.ORG_ID
, 'REQ'
, PRL.ORG_ID
, 'RMA'
, OEH.ORG_ID
, NULL)
, RT.LCM_SHIPMENT_LINE_ID
, RT.UNIT_LANDED_COST
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_TL OETL
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, PO_HEADERS_TRX_V POH
, PO_LINES_TRX_V POL
, PO_LINE_LOCATIONS_TRX_V POLL
, PO_DISTRIBUTIONS_TRX_V POD
, PO_RELEASES_ALL POR
, PO_REQ_HEADERS_TRX_V PRH
, PO_REQ_LINES_TRX_V PRL
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, HR_LOCATIONS_ALL_TL HRL1
, HR_LOCATIONS_ALL_TL HRL2
, PER_PEOPLE_F HRE1
, PER_PEOPLE_F HRE2
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, RCV_ROUTING_HEADERS RRH
, GL_DAILY_CONVERSION_TYPES DCT
, FND_TERRITORIES_TL FT
, FND_CURRENCIES_VL FCV
, PER_JOBS PJ
, PO_JOB_ASSOCIATIONS PJA
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND OEH.HEADER_ID (+) = RT.OE_ORDER_HEADER_ID
AND OEL.LINE_ID (+) = RT.OE_ORDER_LINE_ID
AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND HZCA.CUST_ACCOUNT_ID (+) = RT.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND POH.PO_HEADER_ID (+) = RT.PO_HEADER_ID
AND POL.PO_LINE_ID (+) = RT.PO_LINE_ID
AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID
AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID
AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID
AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID
AND POV.VENDOR_ID (+) = RT.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID
AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID
AND HRL2.LANGUAGE (+) = USERENV('LANG')
AND HRE1.PERSON_ID (+) = RT.EMPLOYEE_ID
AND FT.TERRITORY_CODE (+) = RT.COUNTRY_OF_ORIGIN_CODE
AND NVL(FT.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND ( ( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE
AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL
AND HRE1.NPW_NUMBER IS NOT NULL ) ) ) OR HRE1.PERSON_ID IS NULL )
AND HRE2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID
AND ( ( TRUNC(SYSDATE) BETWEEN HRE2.EFFECTIVE_START_DATE
AND HRE2.EFFECTIVE_END_DATE
AND ( HRE2.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE2.EMPLOYEE_NUMBER IS NULL
AND HRE2.NPW_NUMBER IS NOT NULL ) ) ) OR HRE2.PERSON_ID IS NULL )
AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
AND ORG.LANGUAGE (+) = USERENV('LANG')
AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID
AND DCT.CONVERSION_TYPE (+) = RT.CURRENCY_CONVERSION_TYPE
AND FCV.CURRENCY_CODE (+) = RT.CURRENCY_CODE
AND PJ.JOB_ID (+) = RT.JOB_ID
AND PJA.JOB_ID (+) = RT.JOB_ID
AND ( POH.PO_HEADER_ID IS NULL OR NOT( POL.ORDER_TYPE_LOOKUP_CODE = 'RATE'
AND POL.PURCHASE_BASIS = 'TEMP LABOR' ) )
AND NVL(PRH.ORG_ID
, -99) = NVL(PRL.ORG_ID
, -99)
AND NVL(POH.ORG_ID
, -99) = NVL(POLL.ORG_ID
, -99)