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_ENTER_RECEIPTS_ASN_V

Source

View Text - Preformatted


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 , POH.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) ,POLL.ORG_ID ,to_number(null) , RSL.LCM_SHIPMENT_LINE_ID , RSL.UNIT_LANDED_COST , POLL.lcm_flag FROM RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_HEADERS_ALL POH, PO_LINE_LOCATIONS POLL, PO_LINES_ALL POL, PO_RELEASES_ALL 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, RCV_PARAMETERS RP 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(POLL.MATCHING_BASIS,'QUANTITY') != 'AMOUNT' AND POLL.PAYMENT_TYPE IS NULL AND RP.ORGANIZATION_ID = POLL.SHIP_TO_ORGANIZATION_ID AND ( NVL(RP.PRE_RECEIVE,'N') = 'N' OR (NVL(RP.PRE_RECEIVE,'N') = 'Y' AND NVL(POLL.LCM_FLAG,'N') = 'N'))

View Text - HTML Formatted

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
, POH.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)
, POLL.ORG_ID
, TO_NUMBER(NULL)
, RSL.LCM_SHIPMENT_LINE_ID
, RSL.UNIT_LANDED_COST
, POLL.LCM_FLAG
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS POLL
, PO_LINES_ALL POL
, PO_RELEASES_ALL 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
, RCV_PARAMETERS RP
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(POLL.MATCHING_BASIS
, 'QUANTITY') != 'AMOUNT'
AND POLL.PAYMENT_TYPE IS NULL
AND RP.ORGANIZATION_ID = POLL.SHIP_TO_ORGANIZATION_ID
AND ( NVL(RP.PRE_RECEIVE
, 'N') = 'N' OR (NVL(RP.PRE_RECEIVE
, 'N') = 'Y'
AND NVL(POLL.LCM_FLAG
, 'N') = 'N'))