View: CPG_CP_BLAN_PO_HDR_MCH_V

Product:	GML - Process Manufacturing Logistics
Description:	Blanket PO Header view
Implementation/DBA Data:	APPS.CPG_CP_BLAN_PO_HDR_MCH_V

View Text

SELECT DISTINCT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, 0 GMS_INT
, A.APPROVED_DATE PO_DATE
, 1 MSG_CODE
FROM PO_HEADERS_ALL A
, PO_RELEASES_ALL R
WHERE A.PO_HEADER_ID = R.PO_HEADER_ID
AND NOT EXISTS (SELECT 'Y'
FROM PO_ORDR_HDR B
WHERE GML_AUDIT_REPORTS.GET_PO_NUM(B.PO_NO)=A.SEGMENT1)
AND EXISTS (SELECT 'Y'
FROM CPG_ORAGEMS_MAPPING M
WHERE M.PO_HEADER_ID = A.PO_HEADER_ID) UNION SELECT DISTINCT GML_AUDIT_REPORTS.GET_PO_NUM(A.PO_NO) PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, 0 ORA_INT
, A.PO_ID GMS_INT
, PO_DATE PO_DATE
, 2 MSG_CODE
FROM PO_ORDR_HDR A
WHERE EXISTS (SELECT 'Y'
FROM CPG_ORAGEMS_MAPPING C
WHERE C.PO_ID = A.PO_ID
AND TRANSACTION_TYPE != 'STANDARD')
AND NOT EXISTS (SELECT DISTINCT 'Y'
FROM PO_HEADERS_ALL B
, PO_RELEASES_ALL R
WHERE B.PO_HEADER_ID = R.PO_HEADER_ID)
AND EXISTS (SELECT 'Y'
FROM CPG_ORAGEMS_MAPPING M
WHERE M.PO_ID = A.PO_ID) UNION SELECT DISTINCT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, B.BPO_ID GMS_INT
, A.APPROVED_DATE PO_DATE
, 3 MSG_CODE
FROM PO_HEADERS_ALL A
, PO_RELEASES_ALL R
, PO_ORDR_HDR B
, CPG_ORAGEMS_MAPPING C
WHERE C.PO_HEADER_ID = A.PO_HEADER_ID
AND C.PO_ID = B.PO_ID
AND A.PO_HEADER_ID = R.PO_HEADER_ID
AND A.SEGMENT1 = GML_AUDIT_REPORTS.GET_PO_NUM(B.PO_NO)
AND GML_AUDIT_REPORTS.GET_OFI_BLINE_COUNT(R.PO_HEADER_ID)!= GML_AUDIT_REPORTS.GET_GEMMS_BLINE_COUNT(R.PO_HEADER_ID) UNION SELECT DISTINCT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, B.BPO_ID GMS_INT
, A.APPROVED_DATE PO_DATE
, 4 MSG_CODE
FROM PO_HEADERS_ALL A
, PO_RELEASES_ALL R
, PO_ORDR_HDR B
, CPG_ORAGEMS_MAPPING C
WHERE C.PO_HEADER_ID = A.PO_HEADER_ID
AND C.PO_ID = B.PO_ID
AND A.PO_HEADER_ID = R.PO_HEADER_ID
AND A.SEGMENT1 = GML_AUDIT_REPORTS.GET_PO_NUM(B.PO_NO)
AND GML_AUDIT_REPORTS.GET_OFI_BTOTAL_COST(R.PO_HEADER_ID) != GML_AUDIT_REPORTS.GET_GEMMS_BTOTAL_COST(R.PO_HEADER_ID)

Columns