View: CPG_CP_PO_HDR_MCH_V

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

View Text

SELECT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, 0 GMS_INT
, APPROVED_DATE PO_DATE
, 1 MSG_CODE
FROM PO_HEADERS_ALL A
WHERE A.TYPE_LOOKUP_CODE = 'STANDARD'
AND NOT EXISTS ( SELECT 'Y'
FROM PO_ORDR_HDR B
WHERE B.PO_NO = A.SEGMENT1 OR ( B.PO_NO = SUBSTR(A.SEGMENT1
, 5)
AND B.ORGN_CODE = RTRIM(SUBSTR(A.SEGMENT1
, 1
, 4)) /*FOR MIGRATED PO'S*/ ) )
AND EXISTS (SELECT 'Y'
FROM CPG_ORAGEMS_MAPPING M
WHERE M.PO_HEADER_ID = A.PO_HEADER_ID) UNION SELECT LTRIM(A.PO_NO
, '0') 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 C.TRANSACTION_TYPE = 'STANDARD')
AND NOT EXISTS (SELECT 'Y'
FROM PO_HEADERS_ALL B
WHERE B.SEGMENT1 = A.PO_NO OR B.SEGMENT1 = RPAD(A.ORGN_CODE
, 4
, ' ')||A.PO_NO /*FOR MIGRATED PO'S*/)
AND EXISTS (SELECT 'Y'
FROM CPG_ORAGEMS_MAPPING M
WHERE M.PO_ID = A.PO_ID) UNION SELECT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, B.PO_ID GMS_INT
, APPROVED_DATE PO_DATE
, 3 MSG_CODE
FROM PO_HEADERS_ALL A
, 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 GML_AUDIT_REPORTS.GET_OFI_LINE_COUNT(A.PO_HEADER_ID)!= GML_AUDIT_REPORTS.GET_GEMMS_LINE_COUNT(B.PO_ID)
AND A.TYPE_LOOKUP_CODE = 'STANDARD'
AND (A.SEGMENT1 = B.PO_NO OR A.SEGMENT1 = RPAD(B.ORGN_CODE
, 4
, ' ')||B.PO_NO /*FOR MIGRATED PO'S*/) UNION SELECT A.SEGMENT1 PO_NO
, ' ' ORA_LINE
, ' ' GMS_LINE
, A.PO_HEADER_ID ORA_INT
, B.PO_ID GMS_INT
, APPROVED_DATE PO_DATE
, 4 MSG_CODE
FROM PO_HEADERS_ALL A
, 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 GML_AUDIT_REPORTS.GET_OFI_TOTAL_COST(A.PO_HEADER_ID)!= GML_AUDIT_REPORTS.GET_GEMMS_TOTAL_COST(B.PO_ID)
AND A.TYPE_LOOKUP_CODE = 'STANDARD'
AND (A.SEGMENT1 = B.PO_NO OR A.SEGMENT1 = RPAD(B.ORGN_CODE
, 4
, ' ')||B.PO_NO /*FOR MIGRATED PO'S*/)

Columns