VIEW: APPS.CSP_RECEIVE_HEADERS_V

Source

View Text - Preformatted

SELECT flv.MEANING , flv.LOOKUP_CODE , ph.PO_HEADER_ID HEADER_ID , ph.SEGMENT1 DOCUMENT_NUMBER, 'VENDOR' SOURCE_TYPE_CODE, 'VENDOR' RECEIPT_SOURCE_CODE, PH.VENDOR_ID , PH.VENDOR_SITE_ID , PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID, PLL.SHIPMENT_NUM PO_SHIPMENT_NUM, ph.PO_HEADER_ID PO_HEADER_ID, ph.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID, ph.SEGMENT1 RCV_SHIPMENT_NUMBER, NULL RECEIPT_NUMBER, NULL BILL_OF_LADING , NULL PACKING_SLIP, TO_DATE(NULL) SHIPPED_DATE , NULL FREIGHT_CARRIER_CODE, NVL(PLL.PROMISED_DATE,PLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE, NULL EMPLOYEE_ID , TO_NUMBER(NULL) NUM_OF_CONTAINERS, NULL WAYBILL_AIRBILL_NUM, NULL COMMENTS, PH.ATTRIBUTE_CATEGORY , PH.ATTRIBUTE1 , PH.ATTRIBUTE2 , PH.ATTRIBUTE3 , PH.ATTRIBUTE4 , PH.ATTRIBUTE5 , PH.ATTRIBUTE6 , PH.ATTRIBUTE7 , PH.ATTRIBUTE8 , PH.ATTRIBUTE9 , PH.ATTRIBUTE10 , PH.ATTRIBUTE11 , PH.ATTRIBUTE12 , PH.ATTRIBUTE13 , PH.ATTRIBUTE14 , PH.ATTRIBUTE15 , NULL , POD.DESTINATION_SUBINVENTORY , MP.ORGANIZATION_CODE, PV.VENDOR_NAME FROM PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_RELEASES_ALL PR, PO_DISTRIBUTIONS_ALL POD, FND_LOOKUP_VALUES_VL FLV, MTL_PARAMETERS MP, PO_VENDORS PV WHERE FLV.LOOKUP_CODE = 'PO' AND FLV.LOOKUP_TYPE = 'DOC_TYPE' AND nvl(flv.start_date_active, sysdate)<=sysdate AND nvl(flv.end_date_active,sysdate)>=sysdate AND FLV.ENABLED_FLAG = 'Y' AND PH.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED', 'BLANKET','CONTRACT') AND NVL(PH.CANCEL_FLAG, 'N') IN ('N', 'I') AND NVL(PH.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED') AND PH.PO_HEADER_ID = PLL.PO_HEADER_ID AND Nvl(PLL.APPROVED_FLAG,'N') = 'Y' AND Nvl(PLL.CANCEL_FLAG,'N') = 'N' AND Nvl(PLL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED FOR RECEIVING') AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED') AND PL.PO_LINE_ID = PLL.PO_LINE_ID AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND MP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID AND PV.VENDOR_ID = PH.VENDOR_ID AND pll.RECEIVING_ROUTING_ID = 3 GROUP BY flv.MEANING , flv.LOOKUP_CODE , ph.PO_HEADER_ID , ph.SEGMENT1 , PH.VENDOR_ID , PH.VENDOR_SITE_ID , PLL.SHIP_TO_ORGANIZATION_ID , PLL.SHIPMENT_NUM , ph.PO_HEADER_ID , ph.PO_HEADER_ID , ph.SEGMENT1 , NVL(PLL.PROMISED_DATE,PLL.NEED_BY_DATE), PH.ATTRIBUTE_CATEGORY , PH.ATTRIBUTE1 , PH.ATTRIBUTE2 , PH.ATTRIBUTE3 , PH.ATTRIBUTE4 , PH.ATTRIBUTE5 , PH.ATTRIBUTE6 , PH.ATTRIBUTE7 , PH.ATTRIBUTE8 , PH.ATTRIBUTE9 , PH.ATTRIBUTE10 , PH.ATTRIBUTE11 , PH.ATTRIBUTE12 , PH.ATTRIBUTE13 , PH.ATTRIBUTE14 , PH.ATTRIBUTE15 , POD.DESTINATION_SUBINVENTORY , MP.ORGANIZATION_CODE, PV.VENDOR_NAME UNION ALL SELECT flv.MEANING DOC_TYPE , flv.LOOKUP_CODE DOC_TYPE_CODE, RSH.SHIPMENT_HEADER_ID HEADER_ID, RSH.SHIPMENT_NUM DOCUMENT_NUMBER, 'INTERNAL' SOURCE_TYPE_CODE, DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') RECEIPT_SOURCE_CODE, TO_NUMBER(NULL) VENDOR_ID, TO_NUMBER(NULL) VENDOR_SITE_ID, PRL.DESTINATION_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID, TO_NUMBER(NULL) PO_SHIPMENT_NUMBER, TO_NUMBER(NULL) PO_HEADER_ID, RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID, RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER, RSH.RECEIPT_NUM RECEIPT_NUMBER, RSH.BILL_OF_LADING BILL_OF_LADING, RSH.PACKING_SLIP PACKING_SLIP, RSH.SHIPPED_DATE SHIPPED_DATE, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE, RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE, NULL EMPLOYEE_ID, RSH.NUM_OF_CONTAINERS NUM_OF_CONTAINERS, RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM, RSH.COMMENTS COMMENTS, RSH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, RSH.ATTRIBUTE1 ATTRIBUTE1, RSH.ATTRIBUTE2 ATTRIBUTE2, RSH.ATTRIBUTE3 ATTRIBUTE3, RSH.ATTRIBUTE4 ATTRIBUTE4, RSH.ATTRIBUTE5 ATTRIBUTE5, RSH.ATTRIBUTE6 ATTRIBUTE6, RSH.ATTRIBUTE7 ATTRIBUTE7, RSH.ATTRIBUTE8 ATTRIBUTE8, RSH.ATTRIBUTE9 ATTRIBUTE9, RSH.ATTRIBUTE10 ATTRIBUTE10, RSH.ATTRIBUTE11 ATTRIBUTE11, RSH.ATTRIBUTE12 ATTRIBUTE12, RSH.ATTRIBUTE13 ATTRIBUTE13, RSH.ATTRIBUTE14 ATTRIBUTE14, RSH.ATTRIBUTE15 ATTRIBUTE15, RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE, PRL.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY, MP.ORGANIZATION_CODE SHIP_TO_ORGANIZATION_CODE, HOU.NAME VENDOR_NAME FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, FND_LOOKUP_VALUES_VL flv, HR_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP,PO_REQUISITION_LINES_ALL PRL WHERE flv.lookup_code = 'INTSHIP' AND flv.lookup_type = 'DOC_TYPE' AND nvl(flv.start_date_active, sysdate)<=sysdate AND nvl(flv.end_date_active,sysdate)>=sysdate AND flv.enabled_flag = 'Y' AND RSH.SHIPMENT_NUM IS NOT NULL AND RSH.RECEIPT_SOURCE_CODE IN ('INTERNAL ORDER','INVENTORY') AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED','PARTIALLY RECEIVED') AND EXISTS (SELECT 'AVAILABLE SUPPLY' FROM MTL_SUPPLY MS WHERE MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID) AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID(+) AND HOU.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID AND MP.ORGANIZATION_ID (+) = PRL.DESTINATION_ORGANIZATION_ID AND RSL.ROUTING_HEADER_ID = 3 GROUP BY flv.MEANING, flv.LOOKUP_CODE , RSH.SHIPMENT_HEADER_ID , RSH.SHIPMENT_NUM , DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') , RSL.SHIPMENT_LINE_ID, PRL.DESTINATION_ORGANIZATION_ID, RSL.LINE_NUM, RSH.SHIPMENT_HEADER_ID, RSL.SHIPMENT_HEADER_ID, RSH.SHIPMENT_NUM, RSH.RECEIPT_NUM , RSH.BILL_OF_LADING, RSH.PACKING_SLIP, RSH.SHIPPED_DATE, RSH.FREIGHT_CARRIER_CODE, RSH.EXPECTED_RECEIPT_DATE, RSH.NUM_OF_CONTAINERS, RSH.WAYBILL_AIRBILL_NUM , RSH.COMMENTS , RSH.ATTRIBUTE_CATEGORY , RSH.ATTRIBUTE1 , RSH.ATTRIBUTE2, RSH.ATTRIBUTE3, RSH.ATTRIBUTE4 , RSH.ATTRIBUTE5, RSH.ATTRIBUTE6, RSH.ATTRIBUTE7, RSH.ATTRIBUTE8, RSH.ATTRIBUTE9, RSH.ATTRIBUTE10, RSH.ATTRIBUTE11, RSH.ATTRIBUTE12, RSH.ATTRIBUTE13, RSH.ATTRIBUTE14, RSH.ATTRIBUTE15, RSH.USSGL_TRANSACTION_CODE, PRL.DESTINATION_SUBINVENTORY, MP.ORGANIZATION_CODE, HOU.NAME

View Text - HTML Formatted

SELECT FLV.MEANING
, FLV.LOOKUP_CODE
, PH.PO_HEADER_ID HEADER_ID
, PH.SEGMENT1 DOCUMENT_NUMBER
, 'VENDOR' SOURCE_TYPE_CODE
, 'VENDOR' RECEIPT_SOURCE_CODE
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, PLL.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID
, PLL.SHIPMENT_NUM PO_SHIPMENT_NUM
, PH.PO_HEADER_ID PO_HEADER_ID
, PH.PO_HEADER_ID RCV_SHIPMENT_HEADER_ID
, PH.SEGMENT1 RCV_SHIPMENT_NUMBER
, NULL RECEIPT_NUMBER
, NULL BILL_OF_LADING
, NULL PACKING_SLIP
, TO_DATE(NULL) SHIPPED_DATE
, NULL FREIGHT_CARRIER_CODE
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) EXPECTED_RECEIPT_DATE
, NULL EMPLOYEE_ID
, TO_NUMBER(NULL) NUM_OF_CONTAINERS
, NULL WAYBILL_AIRBILL_NUM
, NULL COMMENTS
, PH.ATTRIBUTE_CATEGORY
, PH.ATTRIBUTE1
, PH.ATTRIBUTE2
, PH.ATTRIBUTE3
, PH.ATTRIBUTE4
, PH.ATTRIBUTE5
, PH.ATTRIBUTE6
, PH.ATTRIBUTE7
, PH.ATTRIBUTE8
, PH.ATTRIBUTE9
, PH.ATTRIBUTE10
, PH.ATTRIBUTE11
, PH.ATTRIBUTE12
, PH.ATTRIBUTE13
, PH.ATTRIBUTE14
, PH.ATTRIBUTE15
, NULL
, POD.DESTINATION_SUBINVENTORY
, MP.ORGANIZATION_CODE
, PV.VENDOR_NAME
FROM PO_HEADERS_ALL PH
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL PL
, PO_RELEASES_ALL PR
, PO_DISTRIBUTIONS_ALL POD
, FND_LOOKUP_VALUES_VL FLV
, MTL_PARAMETERS MP
, PO_VENDORS PV
WHERE FLV.LOOKUP_CODE = 'PO'
AND FLV.LOOKUP_TYPE = 'DOC_TYPE'
AND NVL(FLV.START_DATE_ACTIVE
, SYSDATE)<=SYSDATE
AND NVL(FLV.END_DATE_ACTIVE
, SYSDATE)>=SYSDATE
AND FLV.ENABLED_FLAG = 'Y'
AND PH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'CONTRACT')
AND NVL(PH.CANCEL_FLAG
, 'N') IN ('N'
, 'I')
AND NVL(PH.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CLOSED')
AND PH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED FOR RECEIVING')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND MP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
AND PV.VENDOR_ID = PH.VENDOR_ID
AND PLL.RECEIVING_ROUTING_ID = 3 GROUP BY FLV.MEANING
, FLV.LOOKUP_CODE
, PH.PO_HEADER_ID
, PH.SEGMENT1
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SHIPMENT_NUM
, PH.PO_HEADER_ID
, PH.PO_HEADER_ID
, PH.SEGMENT1
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PH.ATTRIBUTE_CATEGORY
, PH.ATTRIBUTE1
, PH.ATTRIBUTE2
, PH.ATTRIBUTE3
, PH.ATTRIBUTE4
, PH.ATTRIBUTE5
, PH.ATTRIBUTE6
, PH.ATTRIBUTE7
, PH.ATTRIBUTE8
, PH.ATTRIBUTE9
, PH.ATTRIBUTE10
, PH.ATTRIBUTE11
, PH.ATTRIBUTE12
, PH.ATTRIBUTE13
, PH.ATTRIBUTE14
, PH.ATTRIBUTE15
, POD.DESTINATION_SUBINVENTORY
, MP.ORGANIZATION_CODE
, PV.VENDOR_NAME UNION ALL SELECT FLV.MEANING DOC_TYPE
, FLV.LOOKUP_CODE DOC_TYPE_CODE
, RSH.SHIPMENT_HEADER_ID HEADER_ID
, RSH.SHIPMENT_NUM DOCUMENT_NUMBER
, 'INTERNAL' SOURCE_TYPE_CODE
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, 'INVENTORY'
, 'REQ'
, 'INTERNAL ORDER') RECEIPT_SOURCE_CODE
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) VENDOR_SITE_ID
, PRL.DESTINATION_ORGANIZATION_ID SHIP_TO_ORGANIZATION_ID
, TO_NUMBER(NULL) PO_SHIPMENT_NUMBER
, TO_NUMBER(NULL) PO_HEADER_ID
, RSL.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM RCV_SHIPMENT_NUMBER
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, RSH.BILL_OF_LADING BILL_OF_LADING
, RSH.PACKING_SLIP PACKING_SLIP
, RSH.SHIPPED_DATE SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE
, RSH.EXPECTED_RECEIPT_DATE EXPECTED_RECEIPT_DATE
, NULL EMPLOYEE_ID
, RSH.NUM_OF_CONTAINERS NUM_OF_CONTAINERS
, RSH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM
, RSH.COMMENTS COMMENTS
, RSH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, RSH.ATTRIBUTE1 ATTRIBUTE1
, RSH.ATTRIBUTE2 ATTRIBUTE2
, RSH.ATTRIBUTE3 ATTRIBUTE3
, RSH.ATTRIBUTE4 ATTRIBUTE4
, RSH.ATTRIBUTE5 ATTRIBUTE5
, RSH.ATTRIBUTE6 ATTRIBUTE6
, RSH.ATTRIBUTE7 ATTRIBUTE7
, RSH.ATTRIBUTE8 ATTRIBUTE8
, RSH.ATTRIBUTE9 ATTRIBUTE9
, RSH.ATTRIBUTE10 ATTRIBUTE10
, RSH.ATTRIBUTE11 ATTRIBUTE11
, RSH.ATTRIBUTE12 ATTRIBUTE12
, RSH.ATTRIBUTE13 ATTRIBUTE13
, RSH.ATTRIBUTE14 ATTRIBUTE14
, RSH.ATTRIBUTE15 ATTRIBUTE15
, RSH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, PRL.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY
, MP.ORGANIZATION_CODE SHIP_TO_ORGANIZATION_CODE
, HOU.NAME VENDOR_NAME
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, FND_LOOKUP_VALUES_VL FLV
, HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, PO_REQUISITION_LINES_ALL PRL
WHERE FLV.LOOKUP_CODE = 'INTSHIP'
AND FLV.LOOKUP_TYPE = 'DOC_TYPE'
AND NVL(FLV.START_DATE_ACTIVE
, SYSDATE)<=SYSDATE
AND NVL(FLV.END_DATE_ACTIVE
, SYSDATE)>=SYSDATE
AND FLV.ENABLED_FLAG = 'Y'
AND RSH.SHIPMENT_NUM IS NOT NULL
AND RSH.RECEIPT_SOURCE_CODE IN ('INTERNAL ORDER'
, 'INVENTORY')
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED'
, 'PARTIALLY RECEIVED')
AND EXISTS (SELECT 'AVAILABLE SUPPLY'
FROM MTL_SUPPLY MS
WHERE MS.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID)
AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID(+)
AND HOU.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID
AND MP.ORGANIZATION_ID (+) = PRL.DESTINATION_ORGANIZATION_ID
AND RSL.ROUTING_HEADER_ID = 3 GROUP BY FLV.MEANING
, FLV.LOOKUP_CODE
, RSH.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, 'INVENTORY'
, 'REQ'
, 'INTERNAL ORDER')
, RSL.SHIPMENT_LINE_ID
, PRL.DESTINATION_ORGANIZATION_ID
, RSL.LINE_NUM
, RSH.SHIPMENT_HEADER_ID
, RSL.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, RSH.RECEIPT_NUM
, RSH.BILL_OF_LADING
, RSH.PACKING_SLIP
, RSH.SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE
, RSH.EXPECTED_RECEIPT_DATE
, RSH.NUM_OF_CONTAINERS
, RSH.WAYBILL_AIRBILL_NUM
, RSH.COMMENTS
, RSH.ATTRIBUTE_CATEGORY
, RSH.ATTRIBUTE1
, RSH.ATTRIBUTE2
, RSH.ATTRIBUTE3
, RSH.ATTRIBUTE4
, RSH.ATTRIBUTE5
, RSH.ATTRIBUTE6
, RSH.ATTRIBUTE7
, RSH.ATTRIBUTE8
, RSH.ATTRIBUTE9
, RSH.ATTRIBUTE10
, RSH.ATTRIBUTE11
, RSH.ATTRIBUTE12
, RSH.ATTRIBUTE13
, RSH.ATTRIBUTE14
, RSH.ATTRIBUTE15
, RSH.USSGL_TRANSACTION_CODE
, PRL.DESTINATION_SUBINVENTORY
, MP.ORGANIZATION_CODE
, HOU.NAME