VIEW: APPS.RCV_VRC_TXS_INT_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 , TO_NUMBER(null) /* SOURCE_DOC_QTY */ , RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE /* INTERNAL ORDER, INVENTORY, VENDOR */ , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', RT.VENDOR_ID, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID , DECODE(RT.SOURCE_DOCUMENT_CODE , 'PO', POV.VENDOR_NAME, ORG.NAME) SUPPLIER , RT.VENDOR_ID , POV.VENDOR_NAME VENDOR_NAME , RT.VENDOR_SITE_ID SUPPLIER_SITE_ID , 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 , null /* PO_NUM */ , TO_NUMBER(null) /* PO_LINE */ , TO_NUMBER(null) /* 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(null) /* PO_RELEASE */ , PRH.SEGMENT1 /* REQUISITION_NUM */ , PRL.LINE_NUM /* REQUISITION_LINE */ , PRL.REQUISITION_HEADER_ID /* REQ_HEADER_ID */ , PRL.REQUISITION_LINE_ID /* REQ_LINE_ID */ , PRH.SEGMENT1 /* ORDER_NUM */ , PRL.LINE_NUM /* ORDER_LINE */ , null /* UNIT_MEAS_LOOKUP_CODE */ , TO_NUMBER(null) /* HAZARD_CLASS_ID */ , TO_NUMBER(null) /* UN_NUMBER_ID */ , TO_DATE(null) /* NEED_BY_DATE */ , TO_DATE(null) /* PROMISED_DATE */ , TO_NUMBER(null) /* 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 , null /* OE_ORDER_NUM */ , RT.OE_ORDER_LINE_ID , TO_NUMBER(null) /* OE_ORDER_LINE_NUM */ , RT.CUSTOMER_ID , null /* CUSTOMER */ , 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 , null /* NOTE_TO_RECEIVER */ , null /* PLL_NOTE_TO_RECEIVER */ , RSL.TO_ORGANIZATION_ID , null /*Matching basis */ , null /*Transact_amt*/ , null /*currency_name*/ , null/*job_name*/ , null /*JOB_DESCRIPTION*/ , PRL.ORG_ID /* ORG_ID */ , RT.LCM_SHIPMENT_LINE_ID , RT.UNIT_LANDED_COST FROM RCV_TRANSACTIONS RT , RCV_SHIPMENT_HEADERS RSH , RCV_SHIPMENT_LINES RSL , PO_REQUISITION_HEADERS_ALL 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 WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_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 NVL(PRH.ORG_ID, -99) = NVL(PRL.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
, TO_NUMBER(NULL) /* SOURCE_DOC_QTY */
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE /* INTERNAL ORDER
, INVENTORY
, VENDOR */
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, RT.VENDOR_ID
, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, ORG.NAME) SUPPLIER
, RT.VENDOR_ID
, POV.VENDOR_NAME VENDOR_NAME
, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID
, 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
, NULL /* PO_NUM */
, TO_NUMBER(NULL) /* PO_LINE */
, TO_NUMBER(NULL) /* 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(NULL) /* PO_RELEASE */
, PRH.SEGMENT1 /* REQUISITION_NUM */
, PRL.LINE_NUM /* REQUISITION_LINE */
, PRL.REQUISITION_HEADER_ID /* REQ_HEADER_ID */
, PRL.REQUISITION_LINE_ID /* REQ_LINE_ID */
, PRH.SEGMENT1 /* ORDER_NUM */
, PRL.LINE_NUM /* ORDER_LINE */
, NULL /* UNIT_MEAS_LOOKUP_CODE */
, TO_NUMBER(NULL) /* HAZARD_CLASS_ID */
, TO_NUMBER(NULL) /* UN_NUMBER_ID */
, TO_DATE(NULL) /* NEED_BY_DATE */
, TO_DATE(NULL) /* PROMISED_DATE */
, TO_NUMBER(NULL) /* 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
, NULL /* OE_ORDER_NUM */
, RT.OE_ORDER_LINE_ID
, TO_NUMBER(NULL) /* OE_ORDER_LINE_NUM */
, RT.CUSTOMER_ID
, NULL /* CUSTOMER */
, 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
, NULL /* NOTE_TO_RECEIVER */
, NULL /* PLL_NOTE_TO_RECEIVER */
, RSL.TO_ORGANIZATION_ID
, NULL /*MATCHING BASIS */
, NULL /*TRANSACT_AMT*/
, NULL /*CURRENCY_NAME*/
, NULL/*JOB_NAME*/
, NULL /*JOB_DESCRIPTION*/
, PRL.ORG_ID /* ORG_ID */
, RT.LCM_SHIPMENT_LINE_ID
, RT.UNIT_LANDED_COST
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_REQUISITION_HEADERS_ALL 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
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_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 NVL(PRH.ORG_ID
, -99) = NVL(PRL.ORG_ID
, -99)