VIEW: APPS.CPG_CP_PO_HDR_MCH_V

Source

View Text - Preformatted

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*/)

View Text - HTML Formatted

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*/)