View: RCV_ENTER_RECEIPTS_ASN_V

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

View Text

SELECT 'N'
, 'ASN'
, 'VENDOR'
, 'PO'
, POH.TYPE_LOOKUP_CODE
, POLL.PO_HEADER_ID
, POH.SEGMENT1
, POLL.PO_LINE_ID
, POL.LINE_NUM
, POLL.LINE_LOCATION_ID
, POLL.SHIPMENT_NUM
, POLL.PO_RELEASE_ID
, POR.RELEASE_NUM
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, RSH.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, RSL.SHIPMENT_LINE_ID
, RSL.LINE_NUM
, NVL(RSL.FROM_ORGANIZATION_ID
, POH.PO_HEADER_ID)
, RSL.TO_ORGANIZATION_ID
, RSH.VENDOR_ID
, POV.VENDOR_NAME
, RSH.VENDOR_SITE_ID
, NVL(POLT.OUTSIDE_OPERATION_FLAG
, 'N')
, RSL.ITEM_ID
, RSL.UNIT_OF_MEASURE
, MUM.UOM_CLASS
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2)
, NVL(MSI.LOCATION_CONTROL_CODE
, 1)
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N')
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N')
, NVL(MSI.SHELF_LIFE_CODE
, 1)
, NVL(MSI.SHELF_LIFE_DAYS
, 0)
, MSI.SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N')
, NULL
, NULL ITEM_NUMBER
, RSL.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, POHC.HAZARD_CLASS
, POUN.UN_NUMBER
, RSL.VENDOR_ITEM_NUM
, RSL.SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE
, RSL.PACKING_SLIP
, RSL.ROUTING_HEADER_ID
, RCVRH.ROUTING_NAME
, POLL.NEED_BY_DATE
, RSH.EXPECTED_RECEIPT_DATE
, POLL.QUANTITY
, POL.UNIT_MEAS_LOOKUP_CODE
, RSL.USSGL_TRANSACTION_CODE
, RSL.GOVERNMENT_CONTEXT
, POLL.INSPECTION_REQUIRED_FLAG
, POLL.RECEIPT_REQUIRED_FLAG
, POLL.ENFORCE_SHIP_TO_LOCATION_CODE
, NVL(POLL.PRICE_OVERRIDE
, POL.UNIT_PRICE)
, POH.CURRENCY_CODE
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.NOTE_TO_RECEIVER
, RSL.DESTINATION_TYPE_CODE
, RSL.DELIVER_TO_PERSON_ID
, RSL.DELIVER_TO_LOCATION_ID
, RSL.TO_SUBINVENTORY
, RSL.ATTRIBUTE_CATEGORY
, RSL.ATTRIBUTE1
, RSL.ATTRIBUTE2
, RSL.ATTRIBUTE3
, RSL.ATTRIBUTE4
, RSL.ATTRIBUTE5
, RSL.ATTRIBUTE6
, RSL.ATTRIBUTE7
, RSL.ATTRIBUTE8
, RSL.ATTRIBUTE9
, RSL.ATTRIBUTE10
, RSL.ATTRIBUTE11
, RSL.ATTRIBUTE12
, RSL.ATTRIBUTE13
, RSL.ATTRIBUTE14
, RSL.ATTRIBUTE15
, POLL.CLOSED_CODE
, RSH.ASN_TYPE
, RSH.BILL_OF_LADING
, RSH.SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE
, RSH.WAYBILL_AIRBILL_NUM
, RSH.FREIGHT_BILL_NUMBER
, RSL.VENDOR_LOT_NUM
, RSL.CONTAINER_NUM
, RSL.TRUCK_NUM
, RSL.BAR_CODE_LABEL
, DCT.USER_CONVERSION_TYPE
, POLL.MATCH_OPTION
, RSL.COUNTRY_OF_ORIGIN_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, POLL.NOTE_TO_RECEIVER PLL_NOTE_TO_RECEIVER
, POLL.SECONDARY_QUANTITY SECONDARY_ORDERED_QTY
, POLL.SECONDARY_UNIT_OF_MEASURE SECONDARY_ORDERED_UOM
, POLL.PREFERRED_GRADE QC_GRADE
, RSL.ASN_LPN_ID
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_HEADERS POH
, PO_LINE_LOCATIONS POLL
, PO_LINES POL
, PO_RELEASES POR
, PO_VENDORS POV
, PO_HAZARD_CLASSES_TL POHC
, PO_UN_NUMBERS_TL POUN
, RCV_ROUTING_HEADERS RCVRH
, HR_LOCATIONS_ALL_TL HL
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM
, PO_LINE_TYPES_B POLT
, GL_DAILY_CONVERSION_TYPES DCT
WHERE NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND POLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POL.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID (+)
AND POHC.LANGUAGE(+) = USERENV('LANG')
AND POL.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND POUN.LANGUAGE(+) = USERENV('LANG')
AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RSL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND RSH.VENDOR_ID = POV.VENDOR_ID (+)
AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID (+)
AND RSL.ROUTING_HEADER_ID = RCVRH.ROUTING_HEADER_ID (+)
AND MUM.UNIT_OF_MEASURE (+) = RSL.UNIT_OF_MEASURE
AND NVL(MSI.ORGANIZATION_ID
, RSL.TO_ORGANIZATION_ID) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND POLL.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN')
AND RSL.SHIPMENT_LINE_STATUS_CODE != 'CANCELLED'
AND DCT.CONVERSION_TYPE (+) = POH.RATE_TYPE
AND NVL( POH.CONSIGNED_CONSUMPTION_FLAG
, 'N') = 'N'
AND NVL( POR.CONSIGNED_CONSUMPTION_FLAG
, 'N') = 'N'
AND NOT EXISTS(SELECT 1
FROM RCV_TRANSACTIONS_INTERFACE RT
WHERE RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND NVL(RT.TRANSACTION_TYPE
, 'SHIP') = 'CANCEL'
AND NVL(RT.TRANSACTION_STATUS_CODE
, 'PENDING') = 'PENDING'
AND RT.PO_LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID)
AND NVL(POL.MATCHING_BASIS
, 'QUANTITY') != 'AMOUNT'

Columns