View: RCV_VRC_TXS_V

Product:	PO - Purchasing
Description:	10SC ONLY - Retrofitted
Implementation/DBA Data:	APPS.RCV_VRC_TXS_V

View Text

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_NUMBER( 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'
, POL.MATCHING_BASIS
, NULL) MATCHING_BASIS
, RT.AMOUNT
, FCV.NAME
, PJ.NAME
, PJA.JOB_DESCRIPTION
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 POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_DISTRIBUTIONS POD
, PO_RELEASES POR
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES 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
AND HRE1.PERSON_ID 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
AND HRE2.PERSON_ID 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 NOT( POL.ORDER_TYPE_LOOKUP_CODE = 'RATE'
AND POL.PURCHASE_BASIS = 'TEMP LABOR')

Columns