ECC Sourcing, Negotiation Timeline

select
x.*
from
(
 
 
select * from (
SELECT
auction_header_id||'-'||duration_code  ECC_SPEC_ID,
auction_header_id,
document_number,
auction_status_code,
duration_type,
duration,
org_id,
SECURITY_LEVEL_CODE as SECURITY_LEVEL_CODE_CODE,
CURRENCY_CODE,
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEG_TOTAL_AMOUNT(auction_header_id) NEGOTIATION_AMOUNT,
PON_PCC_NEGOTIATIONS_UTIL_PVT.get_authorized_userids(auction_header_id) AS AUTHORIZED_USER_IDS,
language
 
 
FROM (
SELECT
ah.auction_header_id,
ah.document_number,
ah.auction_status auction_status_code,
CASE WHEN AUCTION_HEADER_ID_PREV_AMEND IS NOT NULL OR AUCTION_HEADER_ID_PREV_ROUND IS NOT NULL
       THEN PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_DRAFT_TIME(AH.AUCTION_HEADER_ID)
     WHEN AUCTION_STATUS <> 'AMENDED' AND NOT (AUCTION_STATUS='AUCTION_CLOSED' AND AWARD_STATUS='NO')
     THEN (AH.PUBLISH_DATE - AH.CREATION_DATE)
END AS TOTAL_DRAFT_TIME,
CASE WHEN AUCTION_HEADER_ID_PREV_AMEND IS NOT NULL OR AUCTION_HEADER_ID_PREV_ROUND IS NOT NULL
       THEN PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_BID_TIME(AH.AUCTION_HEADER_ID)
     WHEN AUCTION_STATUS <> 'AMENDED' AND NOT (AUCTION_STATUS='AUCTION_CLOSED' AND AWARD_STATUS='NO')
     THEN (AH.CLOSE_BIDDING_DATE - AH.PUBLISH_DATE)
       END AS TOTAL_BID_TIME,
CASE WHEN AUCTION_HEADER_ID_PREV_AMEND IS NOT NULL OR AUCTION_HEADER_ID_PREV_ROUND IS NOT NULL
       THEN PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_ANALYSE_TIME(AH.AUCTION_HEADER_ID)
     WHEN AUCTION_STATUS <> 'AMENDED' AND NOT (AUCTION_STATUS='AUCTION_CLOSED' AND AWARD_STATUS='NO')
     THEN (AH.AWARD_DATE - AH.CLOSE_BIDDING_DATE)
       END AS TOTAL_ANALYSE_TIME,
CASE WHEN AUCTION_HEADER_ID_PREV_AMEND IS NOT NULL OR AUCTION_HEADER_ID_PREV_ROUND IS NOT NULL
       THEN PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_AWARD_TIME(AH.AUCTION_HEADER_ID)
     WHEN  AUCTION_STATUS = 'AUCTION_CLOSED' AND AWARD_STATUS = 'COMPLETED'
       THEN (AH.AWARD_COMPLETE_DATE - AH.AWARD_DATE)
        END AS TOTAL_AWARD_TIME,
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_PCC_DRAFT_TIME','396',HROU.language) as draft_type,
'DRAFT_TIME' as draft_type_code,
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_PCC_BID_TIME','396',HROU.language) as bid_Type,
'BID_TIME' as bid_Type_code,
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_PCC_ANALYZE_TIME','396',HROU.language) as analyze_type,
'ANALYZE_TIME' as analyze_type_code,
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_PCC_AWARD_TIME','396',HROU.language) as award_type,
'AWARD_TIME' as award_type_code,
 
ah.org_id,
ah.SECURITY_LEVEL_CODE,
ah.currency_code,
hrou.language
 
FROM
pon_auction_headers_all ah,
PON_AUC_DOCTYPES DOC,
HR_ALL_ORGANIZATION_UNITS_TL HROU
where
hrou.organization_id (+) = ah.org_id
AND HROU.LANGUAGE IN ('US')
AND AH.DOCTYPE_ID = DOC.DOCTYPE_ID
AND DOC.INTERNAL_NAME <> 'SOLICITATION'
AND Decode(Nvl(AH.SUPP_REG_QUAL_FLAG, 'N'), 'Y', 'Y','N',
Nvl(AH.SUPP_EVAL_FLAG, 'N'), 'Y', 'Y','N') ='N'
AND AH.CREATION_DATE >= nvl(fnd_date.Canonical_to_date(fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')),AH.CREATION_DATE)
 
 
 ) ah
unpivot ((duration_type,duration,duration_code) FOR  sol_duration IN ((draft_type,TOTAL_DRAFT_TIME,draft_type_code)AS '1',
                                                           (bid_Type,TOTAL_BID_TIME,bid_Type_code) AS '2',
                                                           (analyze_type,TOTAL_ANALYSE_TIME,analyze_type_code) AS '3' ,
                                                            (award_type,TOTAL_AWARD_TIME,award_type_code ) AS '4'
                                                            ) ) )pivot(Max(duration_type) AS duration_type FOR LANGUAGE IN ('US' "US"))
) x
where
2=2