ECC Procurement, Purchase Orders

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, 'Po Ord H Row Id', 'Po Ord H Context', 'Po Ord H Concatenated Segments', Po Header Id, Order Number, Po Revision, Po Document Style, Po Buyer Name, Po Supplier Name ...
Imported from Enterprise Command Center
Dataset Key: po-pcc-orders
Query Procedure: po_pcc_orders_util_pvt.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPO
select
x.*
from
(
SELECT * FROM
     (
         SELECT  /*+ leading(gt) */
             gt.char1                               ecc_spec_id,
             DFV.*,
             po_header.po_header_id,
             po_header.segment1                     order_number,
             po_header.revision_num                 po_revision,
             /*po_pcc_orders_util_pvt.get_po_status(po_header.po_header_id,houtl.language) po_status,*/
             (SELECT plc_sta.meaning
             FROM fnd_lookup_values plc_sta
             WHERE plc_sta.lookup_code = decode(po_header.approved_flag, 'R', po_header.approved_flag,nvl(po_header.authorization_status,'INCOMPLETE'))
             and    plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
             AND   plc_sta.LANGUAGE = houtl.language)
             ||
             decode(po_header.closed_code, 'OPEN', '', '', '',
				     ', '||decode(nvl(po_header.closed_code, 'OPEN'), 'OPEN', NULL,
             (SELECT plc_clo.meaning FROM fnd_lookup_values plc_clo WHERE plc_clo.lookup_code = nvl(po_header.closed_code, 'OPEN')
              and    plc_clo.lookup_type = 'DOCUMENT STATE'
              AND    plc_clo.LANGUAGE = houtl.language)
              ))
              ||
              decode(nvl(po_header.cancel_flag, 'N'), 'N', '', '', '',
			        ', '||decode(po_header.cancel_flag,'Y',
              (SELECT plc_can.meaning FROM fnd_lookup_values plc_can WHERE plc_can.lookup_code ='CANCELLED'
               AND plc_can.lookup_type = 'DOCUMENT STATE' AND plc_can.LANGUAGE = houtl.language ), NULL))
               ||
               decode(nvl(po_header.frozen_flag, 'N'), 'N', '', '', '',
				       ', '||decode(po_header.frozen_flag,'Y', (SELECT plc_fro.meaning FROM fnd_lookup_values plc_fro WHERE plc_fro.lookup_code ='FROZEN'
               AND plc_fro.lookup_type = 'DOCUMENT STATE' AND     plc_fro.LANGUAGE = houtl.language ), NULL))
               ||
               decode(nvl(po_header.user_hold_flag, 'N'), 'N', '', '', '',
				       ', '||decode(po_header.user_hold_flag,'Y', (SELECT plc_hld.meaning FROM fnd_lookup_values plc_hld WHERE plc_hld.lookup_code ='ON HOLD'
               AND plc_hld.lookup_type = 'DOCUMENT STATE' AND     plc_hld.LANGUAGE = houtl.language ), NULL))
               ||
               decode(Nvl(gt.char5,'N'), 'N', '', '', '',
				       ', '||(SELECT flv.meaning
               FROM fnd_lookup_values flv
               WHERE flv.lookup_type = 'DOCUMENT STATE'
               AND flv.lookup_code = 'RESERVED'
               AND flv.LANGUAGE = houtl.language)) po_status,
     /*po_headers_sv3.get_po_status(po_header.po_header_id) po_status,*/
             doc_style.style_name                   po_document_style,
             (
                 SELECT
                     emp.full_name
                 FROM
                     per_all_people_f emp
                 WHERE
                     emp.person_id = po_header.agent_id
                     AND ( ( emp.effective_end_date IS NULL )
                           OR ( emp.effective_end_date = (
                         SELECT
                             MAX(c.effective_end_date)
                         FROM
                             per_all_people_f c
                         WHERE
                             emp.person_id = c.person_id
                     ) ) )
             ) po_buyer_name,
             ap_supplier.vendor_name                po_supplier_name,
             po_header.vendor_id                    po_supplier_id,
             (
                 SELECT
                     apst.vendor_site_code
                 FROM
                     ap_supplier_sites_all apst
                 WHERE
                     apst.vendor_site_id = po_header.vendor_site_id
             ) po_supplier_site,
             (
                 SELECT
                     pvc.first_name
                     || ' '
                     || pvc.last_name
                 FROM
                     po_vendor_contacts pvc
                 WHERE
                     pvc.vendor_contact_id = po_header.vendor_contact_id
                     AND pvc.vendor_site_id = po_header.vendor_site_id
             ) po_supplier_contact,
             po_header.currency_code                po_currency,
             ( CASE
                 WHEN po_header.closed_code = 'FINALLY CLOSED'  THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_FINALLY_CLOSED'
                ,'201',houtl.language)
                 WHEN po_header.closed_code = 'CLOSED'         THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CLOSED','201',houtl.
                 language)
                 WHEN po_header.cancel_flag = 'Y'              THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CANCELED','201',houtl.language
                 )
                 WHEN po_header.authorization_status IN (
                     'REJECTED'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'REJECTED'
                         AND flv.language = houtl.language
                 )
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') IN (
                     'INCOMPLETE'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'INCOMPLETE'
                         AND flv.language = houtl.language
                 )
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(po_header.pending_signature_flag,'N') = 'Y' and nvl(po_header.acceptance_required_flag, 'N') <> 'P'
                 and not exists (select 1 from   PO_ACCEPTANCES
                      where  po_header_id = po_header.po_header_id and revision_num = po_header.revision_num
                      and    po_release_id       is null and    po_line_location_id is null
                      and    accepting_party  = 'S' and accepted_flag    = 'Y' and signature_flag   = 'Y'	)
                THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_REQ_SIGN','201',houtl.language)
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(po_header.acceptance_required_flag,'N') IN ('Y','D')
                      and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=po_header.po_header_id AND poa.revision_num=po_header.revision_num)
                 THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_SUPP_ACK','201',houtl.language)
		/* WHEN EXISTS (SELECT 1
					  FROM po_distributions_all pda
					  WHERE pda.po_header_id = po_header.po_header_id
					  AND failed_funds_lookup_code like 'F%')
			  THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_FUNDS','201',houtl.language) */
                 WHEN po_header.authorization_status IN (
                     'PRE-APPROVED'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'PRE-APPROVED'
                         AND flv.language = houtl.language
                 )
                 WHEN po_header.authorization_status IN (
                     'IN PROCESS'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'IN PROCESS'
                         AND flv.language = houtl.language
                 )
                 WHEN po_header.user_hold_flag = 'Y'           THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_ON_HOLD','201',houtl.language
                 )
                 WHEN EXISTS (
                     SELECT
                         1
                     FROM
                         po_change_requests
                     WHERE
                         document_header_id = po_header.po_header_id
                         AND request_status = 'PENDING'
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_CHG_REQ','201',houtl.language)
                 WHEN po_header.authorization_status = 'APPROVED'
                      AND EXISTS (
                     SELECT
                         1
                     FROM
                         po_line_locations_all plla
                     WHERE
                         plla.need_by_date IS NOT NULL
                         AND plla.po_header_id = po_header.po_header_id
                         AND nvl(plla.promised_date,plla.need_by_date) < SYSDATE
                         AND plla.po_header_id = po_header.po_header_id
                         AND ( nvl( (plla.quantity - plla.quantity_cancelled),0) - nvl(plla.quantity_received,0) ) > 0
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_OVERDUE_SHIP','201',houtl.language)
                 WHEN EXISTS (
                     SELECT
                         1
                     FROM
                         po_line_locations_all plla
                     WHERE
                         plla.po_header_id = po_header.po_header_id
                         AND plla.quantity_rejected > 0
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_REJECTIONS','201',houtl.language)
                 WHEN po_header.authorization_status = 'APPROVED'
                      AND EXISTS (
                     SELECT
                         1
                     FROM
                         po_lines_all pla,
                         po_lines_all agrla,
                         po_line_locations_all plla,
                         po_attribute_values pav
                     WHERE
                         plla.po_line_id = pla.po_line_id
                         AND pla.from_line_id = agrla.po_line_id (+)