ECC Sourcing, Negotiations, SQL1

select
x.*
from
(
 
select * from (SELECT
AH.AUCTION_HEADER_ID||'-'||NEG_TEAM.USER_ID||'-'||PITMDS.ITEM_LINE_NUMBER||'-'
||PBIDS.BID_NUMBER||'-'||PBIDS.BIDLINE_NUMBER AS ecc_spec_id,
AH.AUCTION_HEADER_ID,
AH.DOCUMENT_NUMBER,
AH.AUCTION_TITLE,
AH.ABSTRACT_DETAILS AS DESCRIPTION,
AH.AUCTION_STATUS AS AUCTION_STATUS_CODE,
POSH.STYLE_NAME,
AH.AUCTION_TYPE,
AH.TRADING_PARTNER_NAME,
AH.OPEN_BIDDING_DATE,
DECODE ( NVL( AH.IS_PAUSED, 'N'), 'Y', ( SYSDATE + ( AH.CLOSE_BIDDING_DATE - AH.LAST_PAUSE_DATE ) ) , AH.CLOSE_BIDDING_DATE ) CLOSE_BIDDING_DATE,
AH.VIEW_BY_DATE   PREVIEW_DATE,
AH.AWARD_BY_DATE,
AH.PUBLISH_DATE,
AH.CLOSE_DATE,
AH.CANCEL_DATE,
to_char(nvl(decode(nvl(ah.two_part_flag, 'N' ), 'N' ,(SELECT count(*) FROM pon_bid_headers pbh1 WHERE pbh1.auction_header_id = ah.auction_header_id and pbh1.bid_status NOT IN ('DRAFT','ARCHIVED_DRAFT')),'Y',
( SELECT count(pbd.bid_number) FROM pon_bid_headers pbd,pon_auction_headers_all paha WHERE pbd.auction_header_id=ah.auction_header_id
AND pbd.auction_header_id = paha.auction_header_id AND nvl2(pbd.submit_stage,decode(paha.technical_evaluation_status, null, 'TECHNICAL','NOT_COMPLETED','TECHNICAL','COMPLETED',decode(paha.sealed_auction_status,
'LOCKED','TECHNICAL','COMMERCIAL')),'-9999') = nvl(pbd.submit_stage,'-9999')
 AND PBD.BID_STATUS NOT IN ('DRAFT','ARCHIVED_DRAFT') ) ),0)) as TOTAL_BIDS,
AH.CURRENCY_CODE AS AH_CURRENCY_CODE,
AH.CREATION_DATE,
AH.AUCTION_ROUND_NUMBER,
to_char( AH.CREATION_DATE,'Month') AS NEG_CREATION_MONTH,
EXTRACT(YEAR FROM AH.CREATION_DATE) AS NEG_CREATION_YEAR,
AH.EVENT_TITLE,
HROU.LANGUAGE as  LANGUAGE,
AH.AWARD_COMPLETE_DATE,
AH.ORG_ID,
AH.AMENDMENT_NUMBER,
AH.AMENDMENT_DESCRIPTION,
AH.IS_PAUSED,
case when
AH.AUCTION_STATUS='ACTIVE' or (AH.AUCTION_STATUS='AUCTION_CLOSED' and AH.AWARD_STATUS IN ('COMPLETED','PARTIAL'))  then
'Y'
else
'N' end as active_document,
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEG_TOTAL_AMOUNT(AH.AUCTION_HEADER_ID) as NEGOTIATION_AMOUNT,
to_char(nvl(decode(nvl(ah.two_part_flag, 'N' ), 'N' ,nvl((SELECT count(*) FROM pon_bid_headers pbh1 WHERE pbh1.auction_header_id = ah.auction_header_id and pbh1.bid_status = 'ACTIVE'),0),'Y',
( SELECT count(pbd.bid_number) FROM pon_bid_headers pbd,pon_auction_headers_all paha WHERE pbd.auction_header_id=ah.auction_header_id AND pbd.auction_header_id = paha.auction_header_id
AND nvl2(pbd.submit_stage,decode(paha.technical_evaluation_status, null, 'TECHNICAL','NOT_COMPLETED','TECHNICAL','COMPLETED',decode(paha.sealed_auction_status,'LOCKED',
'TECHNICAL','COMMERCIAL')),'-9999') = nvl(pbd.submit_stage,'-9999') AND PBD.BID_STATUS = 'ACTIVE' ) ),0)) AS ACTIVE_BIDS,
PON_AUCTION_DISCUSSION_PKG.GET_UNREAD_MESSAGE_COUNT(AH.AUCTION_HEADER_ID,AH.TRADING_PARTNER_CONTACT_ID,AH.TRADING_PARTNER_ID) AS UNREAD_MESSAGE_COUNT,
AH.AUCTION_HEADER_ID_PREV_ROUND,
AH.AUCTION_HEADER_ID_PREV_AMEND,
AH.AWARD_STATUS,
CASE WHEN AH.AUCTION_STATUS IN ('ACTIVE','AUCTION_CLOSED') and AH.AWARD_STATUS IN ( 'AWARDED','PARTIAL')
  THEN 'Y'
  ELSE 'N'
  END AS AWARD_STATUS_FLAG ,
AH.AWARD_DATE,
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,
 
((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)
     ELSE 0
       END) +
(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)
     ELSE 0
       END )+
(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)
     ELSE 0
       END )+
(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)
       ELSE 0
        END ) ) as TOTAL_CYCLE_TIME,
Nvl2(ah.auction_header_id, PON_PCC_NEGOTIATIONS_UTIL_PVT.get_authorized_userids(ah.auction_header_id),null
       ) AS AUTHORIZED_USER_IDS,
AH.SECURITY_LEVEL_CODE SECURITY_LEVEL_CODE_CODE,
AH.NUMBER_OF_BIDS,
CASE WHEN AH.AWARD_STATUS IN ('AWARDED','PARTIAL','COMPLETED')
  THEN PON_PCC_NEGOTIATIONS_UTIL_PVT.get_winning_offers(AH.AUCTION_HEADER_ID)
  ELSE NULL
  END AS WINNING_OFFERS ,
INVITED_SUPP.AH_SUPP_COUNT,
PITMDS.ITEM_DISP_LINE_NUMBER,
PITMDS.GROUP_TYPE_CODE,
PITMDS.ITEM_LINE_NUMBER,
PITMDS.ITEM_NUM_REV,
PITMDS.ITEM_CATEGORY_NAME,
PITMDS.ITEM_QUANTITY,
PITMDS.ITEM_TARGET_PRICE,
PITMDS.ITEM_CURRENT_PRICE,
PITMDS.ITEM_NEED_BY_START_DATE,
PITMDS.ITEM_NEED_BY_DATE,
PITMDS.NUMBER_OF_BIDS_OF_ITEM,
DECODE(PITMDS.ITEM_LINE_ORIG_CODE, 'REQUISITION',PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEGLINE_BACKING_REQ_INFO(AH.AUCTION_HEADER_ID,PITMDS.ITEM_LINE_NUMBER),NULL) AS BACKING_REQS_INFO,
DECODE(PITMDS.ITEM_LINE_ORIG_CODE, 'BLANKET',PITMDS.SOURCE_DOC_NUMBER||'/'||PITMDS.SOURCE_LINE_NUMBER,NULL) AS BACKING_BLANKET_INFO,
NVL(PITMDS.ITEM_PROJECTED_SAVINGS,0) as ITEM_PROJECTED_SAVINGS,
/*PITMDS.ITEM_PROJECTED_SAVINGS_DENOM,*/
NVL(PITMDS.ITEM_NEGOTIATED_SAVINGS,0) as ITEM_NEGOTIATED_SAVINGS,
/*PITMDS.ITEM_NEGOTIATED_SAVINGS_DENOM,*/
PITMDS.BEST_BID_NUMBER,
PBIDS.BID_NUMBER,
PBIDS.BID_PUBLISH_DATE,
PBIDS.BID_EFFECTIVE_DATE,
PBIDS.BID_EXPIRATION_DATE,
PBIDS.BID_SUPPLIER_CONTACT,
decode(ah.contract_type, 'STANDARD',PBIDS.BID_AWARD_AMOUNT, PBIDS.BID_PO_AGREED_AMOUNT) BID_QUOTE_TOTAL_AMOUNT,
decode(doc.internal_name, 'BUYER_AUCTION', decode(ah.contract_type, 'STANDARD',PBIDS.BID_AWARD_AMOUNT, PBIDS.BID_PO_AGREED_AMOUNT),0) AWARDED_BIDS,
decode(doc.internal_name, 'REQUEST_FOR_QUOTE', decode(ah.contract_type, 'STANDARD',PBIDS.BID_AWARD_AMOUNT, PBIDS.BID_PO_AGREED_AMOUNT),0) AWARDED_QUOTES,
PBIDS.BID_AWARD_STATUS_CODE,
Nvl2(PBIDS.BID_SUPPLIER_CONTACT_ID,PON_LOCALE_PKG.get_party_display_name(PBIDS.BID_SUPPLIER_CONTACT_ID) ,NULL ) AS BID_SUPPLIER_CONTACT_NAME,
PBIDS.BID_SUPPLIER_NAME,
PBIDS.BID_VENDOR_SITE_CODE,
AH.CURRENCY_CODE AS BID_CURRENCY_CODE,
PBIDS.ORDER_CREATED_FROM_BID,
PBIDS.PO_HEADER_ID_FROM_BID,
PBIDS.BID_AWARD_DATE,
PBIDS.BID_TOTAL_SCORE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,NVL(PBIDS.BIDLINE_DISP_NUMBER,PITMDS.ITEM_DISP_LINE_NUMBER)) AS BIDLINE_DISP_NUMBER,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_NUMBER) AS BIDLINE_NUMBER,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,PITMDS.ITEM_NUM_REV) AS BIDLINE_ITEM_NUM_REV,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,NVL(PBIDS.BIDLINE_ITEM_CATEGORY_NAME,PITMDS.ITEM_IP_CATEGORY_NAME)) AS BIDLINE_ITEM_CATEGORY_NAME,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,NVL(PBIDS.BIDLINE_ITEM_DESC,PITMDS.ITEM_DESCRIPTION)) AS BIDLINE_ITEM_DESC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_DATE(NULL),PITMDS.ITEM_NEED_BY_START_DATE) AS BIDLINE_NEED_BY_START_DATE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_DATE(NULL),PITMDS.ITEM_NEED_BY_DATE) AS BIDLINE_NEED_BY_DATE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_DATE(NULL),PBIDS.BIDLINE_PROMISED_DATE) AS BIDLINE_PROMISED_DATE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PITMDS.ITEM_QUANTITY) AS BIDLINE_TARGET_QUANTITY,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_QUOTE_QUANTITY) AS BIDLINE_QUOTE_QUANTITY,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_START_PRICE) AS BIDLINE_START_PRICE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PITMDS.ITEM_TARGET_PRICE) AS BIDLINE_TARGET_PRICE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_PO_MIN_REL_AMT) AS BIDLINE_PO_MIN_REL_AMT,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_PO_BID_MIN_REL_AMT) AS BIDLINE_PO_BID_MIN_REL_AMT,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PON_OA_UTIL_PKG.GET_ACTIVE_BID_COUNT(PBIDS.AUCTION_HEADER_ID,PBIDS.BIDLINE_NUMBER)) AS BIDLINE_ACTIVE_BIDS,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_DATE(NULL),PBIDS.BIDLINE_AWARD_DATE) AS BIDLINE_AWARD_DATE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_AWARD_QUANTITY) AS BIDLINE_AWARD_QUANTITY,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_AWARD_PRICE) AS BIDLINE_AWARD_PRICE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,PBIDS.BIDLINE_ORDER_NUMBER) AS BIDLINE_ORDER_NUMBER,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_DATE(NULL),PBIDS.BIDLINE_CREATION_DATE) AS BIDLINE_CREATION_DATE,
PBIDS.BIDLINE_SCORE,
PBIDS.BID_NUMBER||'-'|| PBIDS.BIDLINE_NUMBER as BID_LINE_IDENTIFIER
FROM
HR_ALL_ORGANIZATION_UNITS_TL HROU,
HR_LOCATIONS HR1,
HR_LOCATIONS HR2,
HZ_PARTIES HZ1,
HZ_PARTIES HZ6,
PON_AUCTION_HEADERS_ALL AH,
FND_LOOKUP_VALUES FL,
PON_AUC_DOCTYPES DOC,
FND_LOOKUP_VALUES FL1,
PO_DOC_STYLE_HEADERS POSH,
FND_LOOKUP_VALUES FL2,
FND_LOOKUP_VALUES FL3,
FND_LOOKUP_VALUES FL8,
FND_LOOKUP_VALUES FL10,
po_all_doc_style_lines ps,
PON_NEG_TEAM_MEMBERS NEG_TEAM,
(SELECT COUNT(*) AS AH_SUPP_COUNT ,
		MAX(LAST_UPDATE_DATE) AS BID_PARTY_LAST_UPD_DATE,
        AUCTION_HEADER_ID
 FROM PON_BIDDING_PARTIES
 GROUP BY AUCTION_HEADER_ID) INVITED_SUPP,
 (SELECT
        ITM.AUCTION_HEADER_ID,
        ITM.DOCUMENT_DISP_LINE_NUMBER AS ITEM_DISP_LINE_NUMBER,
        ITM.GROUP_TYPE as GROUP_TYPE_CODE,
        FL1.Meaning AS ITEM_LINE_GROUP_TYPE,
        ITM.LINE_NUMBER  AS ITEM_LINE_NUMBER,
        ITM.PARENT_LINE_NUMBER AS ITEM_PARENT_LINE_NUM,
        ITM.ITEM_NUMBER || NVL2(ITM.ITEM_REVISION, ', ', '') || ITM.ITEM_REVISION || JOBS.NAME ITEM_NUM_REV,
       	DECODE(NVL(MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'), 'Y' ,ITM.ITEM_DESCRIPTION ,'N'