ECC Contract Lifecycle Management, Solicitation Forms, SQL1

Description
Columns: Ecc Spec Id, Auction Header Id, Solicitation Number, Current Status Code, Action Date, Form Type Code, Control Number, Is Sbcr Open, Org Id, Security Level Code ...
Imported from ECC
 select * from (
SELECT
       ah.auction_header_id||'-'||pfi.form_id||'-'||'FEDBIZ'||'-'||Forms.form_code ECC_SPEC_ID,
       ah.AUCTION_HEADER_ID AUCTION_HEADER_ID,
       ah.document_number solicitation_number,
       pfi.STATUS current_status_code,
       pfi.LAST_UPDATE_DATE action_date,
       fl_federal_exc.MEANING exception_reason,
      CASE WHEN Forms.FORM_CODE is not null THEN
       Pfst.form_name
       ELSE
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_ECC_PRE_SOL', '201',hrou.language)
 
        END form_name,
       'FEDBIZ' form_type_code,
 
      case when  form_code is null then
       PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_ECC_NOT_CRT', '201',hrou.language)
       else
       CASE WHEN Forms.form_code='ABSTRACT' THEN
        CASE WHEN pfi.STATUS='PUBLISHED' THEN
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_PUBLISHED', '396',hrou.language)
        ELSE
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_NOT_PUBLISHED', '396',hrou.language)
         END
       ELSE
         CASE WHEN pfi.STATUS='DATA_ENTERED' THEN
         PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_DATA_ENTERED', '396',hrou.language)
        WHEN pfi.STATUS='POSTED' THEN
         PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_POSTED', '396',hrou.language)
         WHEN pfi.STATUS='FAILED_POSTED' THEN
          PO_PON_ECC_UTIL_PVT.get_fnd_message ('FAILED_POSTED', '396',hrou.language)
          WHEN pfi.STATUS='FBO_INPROCESS' THEN
          PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_FBO_INPROCESS', '396',hrou.language)
          WHEN pfi.STATUS='FBO_ERROR' THEN
          PO_PON_ECC_UTIL_PVT.get_fnd_message ('FBO_ERROR', '396',hrou.language)
           ELSE
          PO_PON_ECC_UTIL_PVT.get_fnd_message ('PON_NO_DATA_ENTERED', '396',hrou.language)
         END
        END
       end AS form_current_status,
       hrou.language,
null as control_number,
'No' is_sbcr_open,
ah.org_id,
ah.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=ah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS,
                  pfi.form_id,
                  ah.DOCTYPE_ID,
                  ah.ABSTRACT_STATUS,
                  ah.INCLUDE_PDF_IN_EXTERNAL_PAGE ABSTRACT_PDF_FLAG,
                  null CONTRACT_VERSION,
                  case when  form_code is not null and pfi.STATUS<> 'NOT_ENTERED' then
                  'Y'
                  else
                  'N'
                  end show_view_action
 
FROM PON_FORMS_INSTANCES pfi,
       PON_FORMS_SECTIONS_TL pfst,
       PON_FORMS_SECTIONS Forms,
       pon_auction_headers_all ah,
       fnd_lookup_values fl_federal_exc,
       HR_ALL_ORGANIZATION_UNITS_TL HROU,
       po_session_gt gt
WHERE
    ah.auction_header_id= gt.num1
    AND gt.key = 3127021
    and hrou.organization_id (+) = ah.org_id
and hrou.language in ( 'US')
 
AND ah.auction_header_id=pfi.entity_pk1(+)
AND pfst.FORM_ID(+)=pfi.FORM_ID
AND Forms.FORM_ID(+)=pfst.FORM_ID
AND nvl(pfst.language,hrou.language)=hrou.language
AND pfi.ENTITY_CODE(+)='PON_AUCTION_HEADERS_ALL'
AND fl_federal_exc.lookup_type(+) = 'PON_FEDERAL_PUBLICATION'
AND fl_federal_exc.lookup_code(+) = ah.federal_publication_exception
AND nvl(fl_federal_exc.language,hrou.language) = hrou.language
AND (ah.federal_publication_exception IS NOT NULL OR pfi.FORM_ID IS NOT NULL
     or pon_clm_okc_integ_pkg.get_sol_total_amt(ah.auction_header_id) > 25000)
 
 
UNION ALL
 
SELECT
ah.auction_header_id||'-'||'SBCR'||'-'||orca.contract_number  ECC_SPEC_ID,
ah.auction_header_id,
ah.document_number solicitation_number,
Decode(orcu.contract_id,NULL,'NOT_CREATED',orca.contract_status_code) current_status_code,
orca.LAST_UPDATE_DATE action_date,
null as exception_reason,
'SBCR' FORM_NAME,
'SBCR' form_type_code,
CASE WHEN orcu.contract_id IS NULL THEN
           PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_ECC_NOT_CRT', '201',hrou.language)
           ELSE statuslkup.meaning
     END form_current_status,
     hrou.language,
orca.contract_number CONTROL_NUMBER,
CASE WHEN orca.contract_status_code IN ('CANCELLED','SIGNED','TERMINATED') THEN
'No'
ELSE
'Yes' end is_sbcr_open,
ah.org_id,
ah.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=ah.auction_header_id
                  GROUP BY pntm.auction_header_id) NEG_TEAM_MEMBERS ,
                  orcu.contract_id form_id,
                  ah.DOCTYPE_ID,
                  ah.ABSTRACT_STATUS,
                  ah.INCLUDE_PDF_IN_EXTERNAL_PAGE ABSTRACT_PDF_FLAG,
                  orcu.CONTRACT_VERSION,
                  case when orcu.contract_id is null then
                  'N'
                  else
                  'Y' end as show_view_action
 
FROM
pon_auction_headers_all ah,
HR_ALL_ORGANIZATION_UNITS_TL HROU,
okc_rep_contract_usages orcu,
okc_rep_contracts_all orca,
fnd_lookup_values statuslkup,
po_session_gt gt
WHERE
ah.auction_header_id= gt.num1
AND gt.key = 3127021
and hrou.organization_id (+) = ah.org_id
and hrou.language in ( 'US')
AND orcu.business_document_type(+) = 'SOLICITATION'
AND orcu.business_document_id(+) = ah.auction_header_id
AND orcu.contract_type(+) = 'REP_SBCR'
AND orca.contract_type(+)=  'REP_SBCR'
AND orca.contract_id(+) = orcu.contract_id
AND orca.contract_version_num(+) = orcu.contract_version
and statuslkup.lookup_type(+) = 'OKC_REP_CONTRACT_STATUSES'
and statuslkup.lookup_code(+) = orca.contract_status_code
AND nvl(statuslkup.LANGUAGE,hrou.language)= HROU.language
AND (
(ah.auction_status = 'DRAFT' AND po_pon_ecc_util_pvt.IS_SBCR_CREATION_REQUIRED(ah.auction_header_id)='Y' AND nvl(fair_opp_notice_flag,'N')='N')
     OR orcu.contract_id IS NOT NULL)
) pivot(Max(form_name) AS form_name,
                                     Max(form_current_status) AS form_current_status,
                                     max(exception_reason) as exception_reason
                                     FOR LANGUAGE IN ('US' "US"))