VIEW: APPS.POS_RCV_EARLY_LATE_RECEIPTS_V

Source

View Text - Preformatted

SELECT RT.ORGANIZATION_ID , RT.PARENT_TRANSACTION_ID , RT.SHIPMENT_HEADER_ID , RT.SHIPMENT_LINE_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 , RT.PRIMARY_QUANTITY PRIMARY_QUANTITY , POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY , RSL.ITEM_ID , RSL.ITEM_REVISION ITEM_REV , RSL.ITEM_DESCRIPTION ITEM_DESC , RSL.VENDOR_ITEM_NUM , RT.DESTINATION_TYPE_CODE DESTINATION_TYPE /* 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 , RT.EMPLOYEE_ID , PRF.FULL_NAME RECEIVER , RT.DELIVER_TO_LOCATION_ID , RT.SUBINVENTORY , /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE , RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM , POLL.QUANTITY SOURCE_DOC_QTY , RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE , RSH.VENDOR_ID , POV.VENDOR_NAME , POV.SEGMENT1 VENDOR_NUMBER, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID , POVS.VENDOR_SITE_CODE SUPPLIER_SITE , RSL.FROM_ORGANIZATION_ID , RT.INSPECTION_STATUS_CODE , RT.COMMENTS , RT.VENDOR_LOT_NUM , RT.RMA_REFERENCE , RSH.RECEIPT_NUM , RSL.CREATION_DATE RECEIPT_DATE , DECODE(POR.RELEASE_NUM, NULL, POH.SEGMENT1, POH.SEGMENT1||'-'||POR.RELEASE_NUM) PO_NUM , POL.LINE_NUM PO_LINE , 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 , POR.RELEASE_NUM PO_RELEASE , POL.UNIT_MEAS_LOOKUP_CODE , POLL.NEED_BY_DATE , NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) PROMISED_DATE, REASON.REASON_NAME REASON , RRH.ROUTING_NAME ROUTING , POV.ATTRIBUTE14 SUPPLIER_URL , MSI.ATTRIBUTE14 ITEM_URL , MSI.CONCATENATED_SEGMENTS ITEM_NUM , POLKUP.DISPLAYED_FIELD DELIVERY_STATUS , RSH.WAYBILL_AIRBILL_NUM, POH.ORG_ID, HOU.NAME, POL.SUPPLIER_REF_NUMBER, NVL(POLL.PROMISED_DATE, POLL.NEED_BY_DATE) PROMISED_DATETIME FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, FINANCIALS_SYSTEM_PARAMS_ALL FSP, MTL_SYSTEM_ITEMS_KFV MSI, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL POLL, PO_RELEASES_ALL POR, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_LOOKUP_CODES POLKUP, HR_LOCATIONS_ALL_TL HRL1, PER_ALL_PEOPLE_F PRF, MTL_TRANSACTION_REASONS REASON, RCV_ROUTING_HEADERS RRH, HR_ALL_ORGANIZATION_UNITS_TL HOU WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_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 POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID AND POV.VENDOR_ID = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID = RT.VENDOR_SITE_ID AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID AND HRL1.LANGUAGE (+) = USERENV('LANG') and PRF.PERSON_ID (+) = RT.EMPLOYEE_ID AND SYSDATE BETWEEN PRF.EFFECTIVE_START_DATE (+) AND PRF.EFFECTIVE_END_DATE (+) AND REASON.REASON_ID (+) = RT.REASON_ID AND RRH.ROUTING_HEADER_ID = RT.ROUTING_HEADER_ID AND RT.TRANSACTION_TYPE = 'RECEIVE' AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+) AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) AND FSP.ORG_ID (+) = POH.ORG_ID AND POLKUP.LOOKUP_TYPE = 'RELATIVE TIME' AND DECODE(SIGN(NVL(TRUNC(POLL.PROMISED_DATE),TRUNC(POLL.NEED_BY_DATE))+POLL.DAYS_LATE_RECEIPT_ALLOWED- TRUNC(RT.TRANSACTION_DATE)),-1,'Late',DECODE (SIGN((NVL(TRUNC(POLL.PROMISED_DATE),TRUNC( POLL.NEED_BY_DATE)) - POLL.DAYS_EARLY_RECEIPT_ALLOWED) - TRUNC(RT.TRANSACTION_DATE)),1,'Early','On-Time'))=POLKUP.LOOKUP_CODE AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID AND HOU.LANGUAGE (+)= USERENV('LANG')

View Text - HTML Formatted

SELECT RT.ORGANIZATION_ID
, RT.PARENT_TRANSACTION_ID
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_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
, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY
, POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID
, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY
, RSL.ITEM_ID
, RSL.ITEM_REVISION ITEM_REV
, RSL.ITEM_DESCRIPTION ITEM_DESC
, RSL.VENDOR_ITEM_NUM
, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE /* 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
, RT.EMPLOYEE_ID
, PRF.FULL_NAME RECEIVER
, RT.DELIVER_TO_LOCATION_ID
, RT.SUBINVENTORY
, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE
, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM
, POLL.QUANTITY SOURCE_DOC_QTY
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE
, RSH.VENDOR_ID
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID
, POVS.VENDOR_SITE_CODE SUPPLIER_SITE
, RSL.FROM_ORGANIZATION_ID
, RT.INSPECTION_STATUS_CODE
, RT.COMMENTS
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RSH.RECEIPT_NUM
, RSL.CREATION_DATE RECEIPT_DATE
, DECODE(POR.RELEASE_NUM
, NULL
, POH.SEGMENT1
, POH.SEGMENT1||'-'||POR.RELEASE_NUM) PO_NUM
, POL.LINE_NUM PO_LINE
, 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
, POR.RELEASE_NUM PO_RELEASE
, POL.UNIT_MEAS_LOOKUP_CODE
, POLL.NEED_BY_DATE
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) PROMISED_DATE
, REASON.REASON_NAME REASON
, RRH.ROUTING_NAME ROUTING
, POV.ATTRIBUTE14 SUPPLIER_URL
, MSI.ATTRIBUTE14 ITEM_URL
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, POLKUP.DISPLAYED_FIELD DELIVERY_STATUS
, RSH.WAYBILL_AIRBILL_NUM
, POH.ORG_ID
, HOU.NAME
, POL.SUPPLIER_REF_NUMBER
, NVL(POLL.PROMISED_DATE
, POLL.NEED_BY_DATE) PROMISED_DATETIME
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_RELEASES_ALL POR
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_LOOKUP_CODES POLKUP
, HR_LOCATIONS_ALL_TL HRL1
, PER_ALL_PEOPLE_F PRF
, MTL_TRANSACTION_REASONS REASON
, RCV_ROUTING_HEADERS RRH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_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 POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID
AND POV.VENDOR_ID = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID = RT.VENDOR_SITE_ID
AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL1.LANGUAGE (+) = USERENV('LANG')
AND PRF.PERSON_ID (+) = RT.EMPLOYEE_ID
AND SYSDATE BETWEEN PRF.EFFECTIVE_START_DATE (+)
AND PRF.EFFECTIVE_END_DATE (+)
AND REASON.REASON_ID (+) = RT.REASON_ID
AND RRH.ROUTING_HEADER_ID = RT.ROUTING_HEADER_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND FSP.ORG_ID (+) = POH.ORG_ID
AND POLKUP.LOOKUP_TYPE = 'RELATIVE TIME'
AND DECODE(SIGN(NVL(TRUNC(POLL.PROMISED_DATE)
, TRUNC(POLL.NEED_BY_DATE))+POLL.DAYS_LATE_RECEIPT_ALLOWED- TRUNC(RT.TRANSACTION_DATE))
, -1
, 'LATE'
, DECODE (SIGN((NVL(TRUNC(POLL.PROMISED_DATE)
, TRUNC( POLL.NEED_BY_DATE)) - POLL.DAYS_EARLY_RECEIPT_ALLOWED) - TRUNC(RT.TRANSACTION_DATE))
, 1
, 'EARLY'
, 'ON-TIME'))=POLKUP.LOOKUP_CODE
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')