ECC Procurement, Agreements

select
x.*
from
(
select * from (SELECT /*+ leading(gt) */
          gt.char1                  ecc_spec_id,  dfv_h.*, 
    agr_header.po_header_id   agreement,
    agr_header.segment1       agreement_number,
    agr_header.revision_num   agr_revision,
   /* po_pcc_orders_util_pvt.get_po_status(agr_header.po_header_id,houtl.language) status, */
   (SELECT plc_sta.meaning
             FROM fnd_lookup_values plc_sta
             WHERE plc_sta.lookup_code = decode(agr_header.approved_flag, 'R', agr_header.approved_flag,nvl(agr_header.authorization_status,'INCOMPLETE'))
             and    plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
             AND   plc_sta.LANGUAGE = houtl.language)
             ||
             decode(agr_header.closed_code, 'OPEN', '', '', '',
				     ', '||decode(nvl(agr_header.closed_code, 'OPEN'), 'OPEN', NULL,
             (SELECT plc_clo.meaning FROM fnd_lookup_values plc_clo WHERE plc_clo.lookup_code = nvl(agr_header.closed_code, 'OPEN')
              and    plc_clo.lookup_type = 'DOCUMENT STATE'
              AND    plc_clo.LANGUAGE = houtl.language)
              ))
              ||
              decode(nvl(agr_header.cancel_flag, 'N'), 'N', '', '', '',
			        ', '||decode(agr_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(agr_header.frozen_flag, 'N'), 'N', '', '', '',
				       ', '||decode(agr_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(agr_header.user_hold_flag, 'N'), 'N', '', '', '',
				       ', '||decode(agr_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)) status,
    ap_supplier.vendor_name   supplier,
    (
        SELECT
            apst.vendor_site_code
        FROM
            ap_supplier_sites_all apst
        WHERE
            apst.vendor_site_id = agr_header.vendor_site_id
    ) supplier_site,
     ( SELECT
    pvc.first_name || ' '
             || pvc.last_name
         FROM
             po_vendor_contacts pvc
         WHERE
             pvc.vendor_contact_id = agr_header.vendor_contact_id
             AND pvc.vendor_site_id = agr_header.vendor_site_id
     ) agr_supplier_contact,
     agr_header.blanket_total_amount      agr_agreed_amount,
     agr_header.amount_limit              agr_amount_limit,
     gt.num4 agr_amount_released,
     gt.char2 functional_currency_code,
     agr_header.currency_code             currency,
     (
         SELECT
             emp.full_name
         FROM
             per_all_people_f emp
         WHERE
             emp.person_id = agr_header.agent_id
             AND emp.effective_start_date = (
                 SELECT
                     MAX(c.effective_start_date)
                 FROM
                     per_all_people_f c
                 WHERE
                     emp.person_id = c.person_id )
        and rownum <2
     ) buyer_name,
     agr_header.start_date                effective_from,
     agr_header.end_date                  effective_to,
     agr_header.creation_date             creation_date,
     agr_header.approved_date             approval_date,
     agr_header.submit_date               submit_date,
     (SELECT
             at.name
         FROM
             AP_TERMS  at
         WHERE
             at.term_id = agr_header.terms_id
     ) agr_payment_term,
     agr_header.org_id                    org_id,
     houtl.name Operating_Unit,
     gt.num5 as received_amount,
     gt.num6 as invoiced_amount,
     gt.num7 as shipped_amount,
     houtl.language,
    ( CASE
         WHEN ( nvl(agr_header.authorization_status,'INCOMPLETE') IN (
             'INCOMPLETE',
             'REQUIRES REAPPROVAL',
             'REJECTED'
         ) ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_DRAFT_STATUS', '201',houtl.language)
         WHEN ( agr_header.authorization_status IN (
             'IN PROCESS',
             'PRE-APPROVED'
         )
                OR ((nvl(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(agr_header.pending_signature_flag,'N') = 'Y' and nvl(agr_header.acceptance_required_flag, 'N') <> 'P'
              and not exists (select 1 from   PO_ACCEPTANCES
                      where  po_header_id = agr_header.po_header_id and revision_num = agr_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'	))
              or (nvl(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(agr_header.acceptance_required_flag,'N') IN ('Y','D')
		and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=agr_header.po_header_id AND poa.revision_num=agr_header.revision_num)))
         )  THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_INPROC_STATUS', '201',houtl.language)
         WHEN ( agr_header.closed_code IN (
             'CLOSED',
             'FINALLY CLOSED'
         )
                OR agr_header.cancel_flag LIKE 'Y'
                /*OR agr_header.end_date <= SYSDATE
                OR gt.num4 = agr_header.amount_limit*/ ) THEN   po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_CLOSED_STATUS', '201',houtl.language)
         WHEN agr_header.po_header_id IS NOT NULL THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_OPEN_STATUS', '201',houtl.language)
     END ) GROUP_STATUS,
     ( CASE
         WHEN agr_header.closed_code = 'FINALLY CLOSED'  THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_FINALLY_CLOSED', '201',houtl.language)
         WHEN agr_header.closed_code = 'CLOSED'    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CLOSED', '201',houtl.language)
         WHEN agr_header.cancel_flag = 'Y'    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CANCELED', '201',houtl.language)
         WHEN agr_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(agr_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(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(agr_header.pending_signature_flag,'N') = 'Y' and nvl(agr_header.acceptance_required_flag, 'N') <> 'P'
              and not exists (select 1 from   PO_ACCEPTANCES
                      where  po_header_id = agr_header.po_header_id and revision_num = agr_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_agreements_util_pvt.get_fnd_message ('PO_PCC_REQ_SIGN', '201',houtl.language)
         WHEN nvl(agr_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(agr_header.acceptance_required_flag,'N') IN ('Y','D')
		and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=agr_header.po_header_id AND poa.revision_num=agr_header.revision_num)
         THEN po_pcc_agreements_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 = agr_header.po_header_id
                 AND failed_funds_lookup_code LIKE 'F%'
         ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_PEND_FUNDS', '201',houtl.language)
         WHEN agr_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 agr_header.authorization_status IN (
             'IN PROCESS'
         ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_IN_PROCESS', '201',houtl.language)
         WHEN agr_header.user_hold_flag = 'Y'  THEN 'On Hold'
         WHEN EXISTS (
             SELECT
                 1
             FROM
                 po_change_requests
             WHERE
                 document_header_id = agr_header.po_header_id
                 AND request_status = 'PENDING'
         ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_PEND_CHG_REQ', '201',houtl.language)
         WHEN agr_header.end_date <= SYSDATE    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_END_DATE_REACHED', '201',houtl.language)
         WHEN gt.num4 = agr_header.amount_limit  THEN
         po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_AMT_LIMIT_REACHED', '201',houtl.language)
         WHEN SYSDATE >= agr_header.end_date - 30   THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_APPROACH_END_DATE', '201',houtl.language)
 
         WHEN gt.num4 >= ( agr_header.amount_limit * 0.9 ) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_APPROACH_AMT_LIMIT', '201',houtl.language)
         WHEN agr_header.frozen_flag = 'Y'  THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_FROZEN', '201',houtl.language)
        /* WHEN EXISTS (
             SELECT
                 1
             FROM
                 po_buyer_actions
             WHERE
                 trunc(nvl(completion_date,SYSDATE) ) - trunc(target_date) > 0
                 AND status <> 'CLOSED'
                 AND entity_type = 'PA_HEADER'
                 AND entity_id = agr_header.po_header_id
         ) THEN 'Overdue Notes Exist'*/
         ELSE (
             SELECT
                 flv.meaning
             FROM
                  fnd_lookup_values flv
             WHERE
                 flv.lookup_type = 'AUTHORIZATION STATUS'
                 AND flv.lookup_code = agr_header.authorization_status
                 AND flv.language= houtl.language
         )
     END ) SUB_STATUS,
     /*(
         SELECT
             next_action_type
         FROM
             (
                 SELECT
                     fnd_look.meaning   next_action_type,
                     entity_id
                 FROM
                     po_buyer_actions pba,
                     fnd_lookup_values fnd_look
                 WHERE
                     entity_type = 'PA_HEADER'
                     AND status = 'OPEN'
                     AND fnd_look.lookup_type = 'PO_BUYER_PA_HEADER_ACTIONS'
                     AND fnd_look.lookup_code = pba.action_type
                     and fnd_look.LANGUAGE=  houtl.language
                     ORDER BY
                     target_date
             )
         WHERE
             entity_id = agr_header.po_header_id
             AND ROWNUM = 1
     )*/ null as NEXT_ACTION_TYPE,
     DECODE(gt.char5,'Y', (
         SELECT
             polc.meaning
         FROM
             fnd_lookup_values  polc
         WHERE
             polc.lookup_type = 'DOCUMENT STATE'
             AND polc.lookup_code = 'RESERVED'
             AND polc.language= houtl.language
 
     ), (
         SELECT
             polc.meaning
         FROM
             fnd_lookup_values polc
         WHERE
             polc.lookup_type = 'DOCUMENT STATE'
             AND polc.lookup_code = 'NOT RESERVED'
             AND polc.language= houtl.language
     ) ) reservation_status,
     extract(YEAR from agr_header.creation_date) CREATION_YEAR,
     to_char(agr_header.creation_date,'Month') creation_month,
 
     (CASE WHEN
	      agr_header.authorization_status = 'APPROVED'
       AND Nvl(agr_header.closed_code, 'OPEN') <> 'FINALLY CLOSED'
       AND Nvl(agr_header.cancel_flag, 'N') = 'N'
       AND Nvl(agr_header.frozen_flag, 'N') = 'N'
       AND    ((gt.num4 >= ( agr_header.amount_limit * 0.9 )) )
       OR     (gt.num4 = agr_header.amount_limit)
    THEN  'Yes'
	ELSE 'No'
   END ) AGREEMENTS_TO_WATCH,
 
(
CASE WHEN
           agr_header.authorization_status = 'APPROVED'
       AND Nvl(agr_header.closed_code, 'OPEN') <> 'FINALLY CLOSED'
       AND Nvl(agr_header.cancel_flag, 'N') = 'N'
       AND Nvl(agr_header.frozen_flag, 'N') = 'N'
       AND ((SYSDATE >= agr_header.end_date - 30)
        OR (agr_header.end_date <= SYSDATE)) THEN
      'Yes'
      ELSE
      'No'
      END
 
) as  EXPIRING_AGREEMENTS,
 
doc_style.style_name AGR_STYLE ,
     CASE
                            WHEN (agr_header.currency_code IS NULL   OR gt.char2 = agr_header.currency_code) THEN
                            agr_header.blanket_total_amount
                            ELSE nvl(agr_header.rate,1)*agr_header.blanket_total_amount
                     END AS amount_agreed_func,
    CASE
                            WHEN ( agr_header.currency_code IS NULL  OR gt.char2 = agr_header.currency_code) THEN gt.num4
                            ELSE nvl(agr_header.rate,1)*gt.num4
                     END AS released_amount_func,
                     agr_header.authorization_status,
                     decode(agr_header.type_lookup_code,'CONTRACT',po_pcc_agreements_util_pvt.get_fnd_message ('PO_CONTRACT_AGREEMENT', '201',houtl.language),
                                                                     po_pcc_agreements_util_pvt.get_fnd_message ('PO_BLANKET_PO', '201',houtl.language)) document_type,
 
  case
     when agr_header.blanket_total_amount is not null and agr_header.blanket_total_amount >0 then
       (gt.num4/agr_header.blanket_total_amount)*100
    else
      null end as utilization_percentage,
      gt.char3  DOC_AUTHORIZED_USER_IDS ,
      GT.CHAR4 SECURITY_LEVEL_CODE,
      agr_header.type_lookup_code,
   agr_header.vendor_id supplier_id,  DFV_L.*, 
  agr_line.po_line_id                  agreement_line,
  agr_line.line_num                    line_number,
     (
         SELECT
             line_type
         FROM
             po_line_types_tl lt
         WHERE
             lt.line_type_id = agr_line.line_type_id
             AND lt.language  = houtl.language
     ) line_type,
     mtl_sys_item.concatenated_segments   item,
     agr_line.item_description,
     mtl_cat_kfv.concatenated_segments    category,
     agr_line.unit_meas_lookup_code       uom,
     agr_line.unit_price                  unit_price,
     DECODE( NVL(agr_line.closed_code, 'OPEN'),
               'OPEN', po_pcc_agreements_util_pvt.get_fnd_message('PO_OPEN', '201',houtl.language),
               'CLOSED FOR RECEIVING', po_pcc_agreements_util_pvt.get_fnd_message('PO_STATUS_CLOSED', '201',houtl.language),
               po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CLOSED', '201',houtl.language)) line_status	,
 
     agr_attr_values.lead_time            lead_time,
     (SELECT Count(1)
                   FROM po_line_locations_all pll
                   WHERE pll.po_line_id = agr_line.po_line_id
  )  	no_price_breaks,
 
  (SELECT document_number FROM pon_auction_headers_all paha
  WHERE paha.auction_header_id= agr_line.auction_header_id) negotiation,
 /* (SELECT
                LISTAGG(poh.segment1,'|') WITHIN GROUP  (ORDER BY nvl(pla.from_header_id,pla.contract_id)) AS order_num
                  FROM
                  po_lines_all pla,
                  po_headers_all poh
                WHERE
                  nvl(pla.from_header_id,pla.contract_id) = agr_header.po_header_id
                  AND poh.po_header_id=pla.po_header_id
                  GROUP BY nvl(pla.from_header_id,pla.contract_id))*/ null AS ORDER_num,
 
    po_order_lines.Released_Quantity,
    po_order_lines.Shipped_Quantity,
    po_order_lines.Received_Quantity,
    po_order_lines.Rejected_Quantity,
    po_order_lines.Invoiced_Quantity,
    po_line_rel_amt.line_released_amount ,
    po_order_lines.line_received_amount,
    po_order_lines.line_invoiced_amount,
    po_order_lines.line_shipped_amount,
 
     (
         SELECT
             DECODE(flv.lookup_code,'APPROVED', (CASE
                 WHEN agr_header.closed_code = 'CLOSED'         THEN 'PO_STATUS_CLOSED'
                 WHEN agr_header.closed_code = 'FINALLY CLOSED' THEN 'PO_STATUS_FINALLY_CLOSED'
                 WHEN nvl(agr_header.cancel_flag,'N') = 'Y'    THEN 'PO_STATUS_CANCELED'
                 WHEN nvl(agr_header.frozen_flag,'N') = 'Y'    THEN 'PO_STATUS_FROZEN'
                 WHEN nvl(agr_header.user_hold_flag,'N') = 'Y' THEN 'PO_STATUS_ON_HOLD'
                 ELSE flv.lookup_code
             END),flv.lookup_code)
         FROM
             fnd_lookup_values_vl flv
         WHERE
             nvl(agr_header.authorization_status,'INCOMPLETE') = flv.lookup_code
             AND flv.lookup_type = 'AUTHORIZATION STATUS'
             AND agr_header.po_header_id IS NOT NULL
     ) agreement_status_code,
 
     agr_line.vendor_product_num          SUPPLIER_ITEM_NUM,
     deliverable.deliverable_id okc_deliverable_id ,
   deliverable.deliverable_name  okc_deliverable_name,
   (SELECT deliverabletypes_tl.name
           FROM okc_deliverable_types_tl  deliverabletypes_tl
           WHERE deliverabletypes_tl.deliverable_type_code=deliverable.deliverable_type
             AND deliverabletypes_tl.LANGUAGE = houtl.LANGUAGE)
             okc_deliverable_type,
    (SELECT busdoc_tl.name FROM
            okc_bus_doc_types_tl busdoc_tl
          WHERE  deliverable.business_document_type = busdoc_tl.document_type
          AND busdoc_tl.language = houtl.LANGUAGE) AS okc_business_document_type,
          deliverable.business_document_type okc_doc_type_code,
   deliverable.business_document_number   okc_business_doc_number,
   (select name
	 from okc_resp_parties_vl  okc_resp_party
	 where okc_resp_party.resp_party_code=deliverable.responsible_party
	 and okc_resp_party.document_type_class = 'PO')   responsible_party,
   (CASE
     WHEN deliverable.responsible_party = 'INTERNAL_ORG' THEN
		(SELECT EMPLOYEE.FULL_NAME CONTACT_NAME
		 FROM PER_ALL_PEOPLE_F EMPLOYEE
		 WHERE EMPLOYEE.EFFECTIVE_START_DATE = EMPLOYEE.START_DATE
		 AND EMPLOYEE.PERSON_ID = deliverable.INTERNAL_PARTY_CONTACT_ID
		 AND ROWNUM <2)
	ELSE
     (select  hpcontact.party_name  FROM
     hz_parties hpcontact WHERE
     deliverable.external_party_contact_id = hpcontact.party_id)
    END
    )   okc_party_contact,
    deliverable.actual_due_date  due_date,
    (select meaning
	   from  fnd_lookup_values fnd_lookup
	   where fnd_lookup.lookup_code = deliverable.deliverable_status
	   and fnd_lookup.lookup_type = 'OKC_DELIVERABLE_STATUS'
     AND fnd_lookup.language = houtl.language )  deliverable_status,
     DECODE(nvl(agr_header.cancel_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',houtl.language),
            PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',houtl.language)) CANCEL_FLAG,
     DECODE(nvl