ECC Contract Lifecycle Management, Solicitation Time Line

select
x.*
from
(
 select * from (
SELECT
auction_header_id||'-'||duration_type  ECC_SPEC_ID,
auction_header_id,
document_number,
duration_type,
duration,
solicitation_amount,
language ,
org_id,
SECURITY_LEVEL_CODE,
NEG_TEAM_MEMBERS
 
FROM (
SELECT
pah.auction_header_id,
pah.document_number,
PON_CLM_OKC_INTEG_PKG.GET_SOL_TOTAL_AMT(pah.auction_header_id) solicitation_amount,
Nvl(po_pon_ecc_util_pvt.get_preparation_time(pah.auction_header_id),0) creation_duration,
Nvl(po_pon_ecc_util_pvt.get_approval_time(pah.auction_header_id),0) approval_duration,
Nvl(po_pon_ecc_util_pvt.get_publish_time(pah.auction_header_id),0)  publish_duration,
Nvl(po_pon_ecc_util_pvt.get_total_bid_time(pah.auction_header_id),0) closure_duration,
Nvl(po_pon_ecc_util_pvt.get_total_analyse_time(pah.auction_header_id),0) evaluation_duration,
Nvl(po_pon_ecc_util_pvt.get_total_award_time(pah.auction_header_id),0) award_duration,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_CRT_TYP', '201',hrou.language) creation_type,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_APP_TYP', '201',hrou.language) approval_Type,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_PUB_TYP', '201',hrou.language) publish_type,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_CLOSE_TYP', '201',hrou.language) closure_type,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_EVAL_TYP', '201',hrou.language) evaluation_type,
PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_SRC_ECC_AWARD_TYP', '201',hrou.language) award_type,
hrou.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
pon_auction_headers_all pah,
HR_ALL_ORGANIZATION_UNITS_TL HROU,
po_session_gt gt
WHERE
pah.auction_header_id=gt.num1
and hrou.language in ( 'US')
and gt.key = 3197941
and hrou.organization_id (+) = pah.org_id) ah
unpivot ((duration_type,duration) FOR  sol_duration IN ((creation_type,creation_duration)AS '1',
                                                           (approval_Type,approval_duration) AS '2',
                                                           (publish_type,publish_duration) AS '3' ,
                                                            (closure_type,closure_duration ) AS '4',
                                                            (evaluation_type, evaluation_duration) AS '5',
                                                            (award_type,award_duration) AS '6'))
 
) pivot(Max(duration_type) AS duration_type FOR LANGUAGE IN ('US' "US"))
) x
where
2=2