ECC Contract Lifecycle Management, Solicitation Deliverables

Description
Categories: Enterprise Command Center
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
Run ECC Contract Lifecycle Management, Solicitation Deliverables and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Blitz Report™