ECC Contract Lifecycle Management, Solicitation Deliverables

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Deliverable Id, Auction Header Id, Solicitation Number, Business Document Id, Business Document Number, Business Document Version, Deliverables Record, Deliverable Name, Deliverable Status Code ...
Imported from Enterprise Command Center
Dataset Key: po-clm-sol-deliverables
Query Procedure: PO_PON_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesSol
select
x.*
from
(
 select * from ( select pah.auction_header_id||'-'||deliverable.deliverable_id as ECC_SPEC_ID,
       deliverable.deliverable_id,
       pah.auction_header_id,
       pah.document_number solicitation_number,
       deliverable.business_document_id,
       deliverable.BUSINESS_DOCUMENT_NUMBER,
       deliverable.business_document_version,
       'Y' AS deliverables_record,
       deliverable.deliverable_name,
       deliverable.deliverable_status deliverable_status_code,
       PO_PON_ECC_UTIL_PVT.get_lookup_meaning(deliverable.deliverable_status,'OKC_DELIVERABLE_STATUS',0,deliverabletypes_tl.language) deliverable_status,
       deliverable.del_category_code,
       PO_PON_ECC_UTIL_PVT.get_lookup_meaning(deliverable.del_category_code,'OKC_DEL_CATEGORIES',0,deliverabletypes_tl.language) deliverable_category,
       deliverable.deliverable_type deliverable_type_code,
       deliverabletypes_tl.name deliverable_type,
       resp_party_tl.name responsible_party,
 
              (CASE deliverable.responsible_party WHEN 'INTERNAL_ORG' THEN
                org.name
               ELSE
                okc_deliverable_process_pvt.get_party_name(deliverable.external_party_id,deliverable.responsible_party)
               END) party_name,
 
               deliverable.description,
               (SELECT Name
              FROM   HR_ALL_ORGANIZATION_UNITS_TL  hrou
              WHERE  deliverable.internal_party_id = hrou.organization_id
                      and hrou.language = deliverabletypes_tl.language) Internal_Organization,
 
               (SELECT distinct buyer_contact.full_name
              FROM   per_all_people_f buyer_contact
              WHERE  buyer_contact.person_id = deliverable.internal_party_contact_id
               AND  buyer_contact.effective_start_date <= SYSDATE
      AND (buyer_contact.effective_end_date is NULL OR buyer_contact.effective_end_date> sysdate)
 
       )  Internal_Contact,
       (SELECT party_name
              FROM   hz_parties
              WHERE  party_id = deliverable.external_party_contact_id)  supplier_contact,
 
       (SELECT distinct requester_contact.full_name
              FROM   per_all_people_f requester_contact
              WHERE  deliverable.requester_id = requester_contact.person_id
              AND requester_contact.effective_start_date <= SYSDATE
             AND (requester_contact.effective_end_date is NULL OR requester_contact.effective_end_date> sysdate)
 
      ) requestor_name ,
      busdoc_tl.name Document_Type,
      deliverable.completion_date actual_date_of_completion,
      deliverable.actual_due_date due_date,
      deliverable.comments notes,
      deliverable.description deliverable_description,
   DECODE(deliverable.deliverable_status, 'OPEN', 'Yes', 'SUBMITTED', 'Yes', 'No') deliverable_due_flag,
   CASE
	 WHEN
	   deliverable.actual_due_date < sysdate and deliverable.deliverable_status = 'OPEN'
	 THEN 'OA_MEDIA/warningind_status.gif'
   else
   null
   end deliverable_alert,
case
   when deliverable.completion_date is not null and deliverable.deliverable_status = 'COMPLETED' then
     case
       when deliverable.completion_date <= deliverable.actual_due_date  then
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',deliverabletypes_tl.language)
       else
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',deliverabletypes_tl.language)
       end
     else
      null
     end as on_time_completion,
   deliverabletypes_tl.language language ,
   pah.org_id,
   pah.SECURITY_LEVEL_CODE,
             (SELECT
                LISTAGG(pntm.user_id,'|') WITHIN GROUP  (ORDER BY pntm.auction_header_id) AS NEG_TEAM_MEMBERS
                  FROM
                  pon_neg_team_members pntm
                WHERE
                  pntm.auction_header_id=pah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS
 
FROM
 
  okc_deliverables deliverable,
  okc_deliverable_types_tl deliverabletypes_tl,
  hr_all_organization_units org,
  okc_resp_parties_tl resp_party_tl,
  okc_bus_doc_types_b busdoc,
  okc_bus_doc_types_tl busdoc_tl,
  pon_bid_headers pbh,
  pon_auction_headers_all pah
 
WHERE
  pah.auction_status NOT IN ('APPLIED','DELETED')
  AND pbh.auction_header_id=pah.auction_header_id
  AND deliverable.business_document_type='SOLICITATION_RESPONSE'
  AND deliverable.deliverable_status <> 'INACTIVE'
  AND deliverable.business_document_id=pbh.bid_number
  AND deliverable.deliverable_type = deliverabletypes_tl.deliverable_type_code
  AND deliverabletypes_tl.language = deliverabletypes_tl.language
  and deliverable.internal_party_id = org.organization_id (+)
  and deliverable.responsible_party = resp_party_tl.resp_party_code
  and busdoc.document_type_class = resp_party_tl.document_type_class
  and busdoc.intent = resp_party_tl.intent
  and resp_party_tl.language = deliverabletypes_tl.language
  and deliverable.business_document_type = busdoc.document_type
  and busdoc.intent = resp_party_tl.intent
  and busdoc.document_type = busdoc_tl.document_type
  AND busdoc_tl.language = deliverabletypes_tl.language
  and deliverabletypes_tl.language in ( 'US')
  AND deliverable.creation_date >=
       to_date(to_char(to_timestamp('01-OCT-19'),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS')) PIVOT (max(deliverable_status) as deliverable_status,
                         max(deliverable_category) as deliverable_category,
                         max(deliverable_type) as deliverable_type,
                         max(responsible_party) as responsible_party,
                         max(Document_Type) as Document_Type,
                         max(Internal_Organization) as Internal_Organization,
                         max(on_time_completion) as on_time_completion
                         for LANGUAGE in ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV