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 , ms.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 , MS.TO_SUBINVENTORY , MP.ORGANIZATION_CODE ,csp_receive_pvt.vendor(ph.vendor_id) VENDOR_NAME FROM PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL, FND_LOOKUP_VALUES_VL FLV, MTL_PARAMETERS MP, mtl_supply ms WHERE FLV.LOOKUP_CODE = 'PO' and ms.supply_type_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 = MS.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 pll.line_location_id = ms.po_line_location_id AND MP.ORGANIZATION_ID = MS.TO_ORGANIZATION_ID AND pll.RECEIVING_ROUTING_ID = 3 /*and ms.to_organization_id = 207 and ms.to_subinventory = 'FGI'*/ GROUP BY flv.MEANING , flv.LOOKUP_CODE , ph.PO_HEADER_ID , ph.SEGMENT1 , PH.VENDOR_ID , PH.VENDOR_SITE_ID , ms.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 , MS.TO_SUBINVENTORY , MP.ORGANIZATION_CODE ,csp_receive_pvt.vendor(ph.vendor_id) 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 /*and prl.destination_organization_id = 207 and prl.destination_subinventory = 'FGI'*/ GROUP BY flv.MEANING, flv.LOOKUP_CODE , RSH.SHIPMENT_HEADER_ID , RSH.SHIPMENT_NUM , DECODE(RSL.SOURCE_DOCUMENT_CODE,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER') , PRL.DESTINATION_ORGANIZATION_ID, 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
, MS.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
, MS.TO_SUBINVENTORY
, MP.ORGANIZATION_CODE
, CSP_RECEIVE_PVT.VENDOR(PH.VENDOR_ID) VENDOR_NAME
FROM PO_HEADERS_ALL PH
, PO_LINE_LOCATIONS_ALL PLL
, FND_LOOKUP_VALUES_VL FLV
, MTL_PARAMETERS MP
, MTL_SUPPLY MS
WHERE FLV.LOOKUP_CODE = 'PO'
AND MS.SUPPLY_TYPE_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 = MS.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 PLL.LINE_LOCATION_ID = MS.PO_LINE_LOCATION_ID
AND MP.ORGANIZATION_ID = MS.TO_ORGANIZATION_ID
AND PLL.RECEIVING_ROUTING_ID = 3 /*AND MS.TO_ORGANIZATION_ID = 207
AND MS.TO_SUBINVENTORY = 'FGI'*/ GROUP BY FLV.MEANING
, FLV.LOOKUP_CODE
, PH.PO_HEADER_ID
, PH.SEGMENT1
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, MS.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
, MS.TO_SUBINVENTORY
, MP.ORGANIZATION_CODE
, CSP_RECEIVE_PVT.VENDOR(PH.VENDOR_ID) 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 /*AND PRL.DESTINATION_ORGANIZATION_ID = 207
AND PRL.DESTINATION_SUBINVENTORY = 'FGI'*/ GROUP BY FLV.MEANING
, FLV.LOOKUP_CODE
, RSH.SHIPMENT_HEADER_ID
, RSH.SHIPMENT_NUM
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'INVENTORY'
, 'INVENTORY'
, 'REQ'
, 'INTERNAL ORDER')
, PRL.DESTINATION_ORGANIZATION_ID
, 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