ECC Sourcing, Negotiations, SQL1

Description
Columns: Ecc Spec Id, 'Pon Auc Row Id', 'Pon Auc Context Value', 'Pon Auc Mobile Number', 'Pon Auc Landline Number', 'Pon Auc Phone', 'Pon Auc Email', 'Pon Auc Fax', 'Pon Auc Concatenated Segments', 'Pon Auc Int Row Id' ...
Imported from ECC
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,pon_auc_dfv.*,pon_auc_int_dfv.*,
AH.AUCTION_HEADER_ID,
AH.DOCUMENT_NUMBER,
AH.AUCTION_TITLE,
AH.ABSTRACT_DETAILS AS DESCRIPTION,
AH.AUCTION_STATUS AS AUCTION_STATUS_CODE,
pon_pcc_negotiations_util_pvt.get_auction_status(ah.auction_header_id,hrou.language) AS AUCTION_STATUS,
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,
ps.display_name as OUTCOME,
FL2.MEANING STYLE,
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,
/*HR1.LOCATION_CODE AS SHIP_TO_LOCATION,
HR2.LOCATION_CODE AS BILL_TO_LOCATION,*/
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,
FL1.MEANING DOCTYPE,
FL3.MEANING AS BID_RANKING_DISPLAY,
FL8.MEANING AS RANK_INDICATOR,
pon_locale_pkg.get_party_display_name(ah.trading_partner_contact_id,12,hrou.language) BUYER_NAME,
/*AH.PO_MIN_REL_AMOUNT,
AH.GLOBAL_AGREEMENT_FLAG,*/
AH.AWARD_COMPLETE_DATE,
FL10.MEANING as SECURITY_LEVEL_CODE,
HROU.NAME  AH_OPERATING_UNIT,
AH.ORG_ID,
AH.AMENDMENT_NUMBER,
AH.AMENDMENT_DESCRIPTION,
AH.IS_PAUSED,
decode (nvl(AH.TWO_PART_FLAG,'N'),'N',pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_NO','396',HROU.language),
 pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_YES','396',HROU.language)) as TWO_PART_FLAG,
case when
AH.AUCTION_STATUS = 'AMENDED' or (AH.AUCTION_STATUS='AUCTION_CLOSED' and ah.AWARD_STATUS='NO') then
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_NO','396',HROU.language)
else
pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_YES','396',HROU.language)
end as LATEST_VERSION,
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,
/*case when
AH.AUCTION_STATUS='ACTIVE' or (AH.AUCTION_STATUS='AUCTION_CLOSED' and AH.AWARD_STATUS IN ('COMPLETED','PARTIAL'))  then
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_AMEND_ROUNDS(AH.AUCTION_HEADER_ID)
else
0 end null as  TOTAL_AMEND_ROUNDS,  */
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEG_TOTAL_AMOUNT(AH.AUCTION_HEADER_ID) as NEGOTIATION_AMOUNT,
/*PON_AUCTION_PKG.TIME_REMAINING(AH.AUCTION_HEADER_ID) TIMELEFT,*/
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,
/*NEG_TEAM.USER_ID AS COLLABMEM_USER_ID,
NEG_TEAM.USER_NAME AS COLLABMEM_USER_NAME,
DECODE(NEG_TEAM.MEMBER_TYPE,'N',FND_MESSAGE.GET_STRING('PON','PON_AUC_MEMBER_ROLE'),'M',FND_MESSAGE.GET_STRING('PON','PON_AUC_MEMBER_ROLE'),'C',FND_MESSAGE.GET_STRING('PON','PON_AUC_OWNER_ROLE')) AS COLLABMEM_ROLE, */
PITMDS.ITEM_DISP_LINE_NUMBER,
PITMDS.GROUP_TYPE_CODE,
PITMDS.ITEM_LINE_GROUP_TYPE,
PITMDS.ITEM_LINE_NUMBER,
/*PITMDS.ITEM_PARENT_LINE_NUM, */
PITMDS.ITEM_NUM_REV,
PITMDS.ITEM_DESCRIPTION,
/*PITMDS.ITEM_ORGANIZATION_ID,*/
PITMDS.ITEM_CATEGORY_NAME,
PITMDS.ITEM_LINE_TYPE,
PITMDS.ITEM_QUANTITY,
PITMDS.ITEM_UNIT_OF_MEASURE,
/*PITMDS.ITEM_BID_START_PRICE,*/
PITMDS.ITEM_TARGET_PRICE,
PITMDS.ITEM_CURRENT_PRICE,
PITMDS.ITEM_NEED_BY_START_DATE,
PITMDS.ITEM_NEED_BY_DATE,
/*PITMDS.ITEM_SHIP_TO_ADDRESS,
PITMDS.ITEM_PRICE_BREAK_TYPE,*/
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,
decode(AH.SEALED_AUCTION_STATUS, 'LOCKED',FL2.MEANING,PBIDS.BID_NUMBER) AS BID_NUMBER_DISP,
PBIDS.BID_NUMBER,
PBIDS.BID_STATUS,
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.BID_SHORTLIST_FLAG,
PBIDS.SURROG_BID_FLAG,
decode(AH.SEALED_AUCTION_STATUS, 'LOCKED',FL2.MEANING,PON_TRANSFORM_BIDDING_PKG.calculate_bid_total(AH.AUCTION_HEADER_ID,PBIDS.BID_NUMBER,AH.TRADING_PARTNER_ID,NULL)) AS BID_TOTAL,
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),NVL(PBIDS.BIDLINE_PARENT_LINE_NUMBER,PITMDS.ITEM_PARENT_LINE_NUM)) AS BIDLINE_PARENT_LINE_NUMBER,*/
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,NVL(PBIDS.BIDLINE_GROUP_TYPE,PITMDS.ITEM_LINE_GROUP_TYPE)) AS BIDLINE_GROUP_TYPE,
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',NULL,NVL(PBIDS.BIDLINE_LINE_TYPE,PITMDS.ITEM_LINE_TYPE)) AS BIDLINE_LINE_TYPE,
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',NULL,PBIDS.BIDLINE_UOM) AS BIDLINE_UOM,
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',NULL,decode(AH.SEALED_AUCTION_STATUS,'LOCKED',FL2.MEANING,PBIDS.BIDLINE_QUOTE_PRICE)) AS BIDLINE_QUOTE_PRICE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,decode(AH.SEALED_AUCTION_STATUS,'LOCKED',FL2.MEANING,PON_PCC_NEGOTIATIONS_UTIL_PVT.CALCULATE_BIDLINE_TOTAL(AH.AUCTION_HEADER_ID,
PBIDS.BID_NUMBER,PBIDS.BIDLINE_NUMBER,AH.TRADING_PARTNER_ID,AH.CURRENCY_CODE))) AS BIDLINE_TOTAL,
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),PBIDS.BIDLINE_ADVANCE_AMOUNT) AS BIDLINE_ADVANCE_AMOUNT,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_RECOUP_RATE_PERC) AS BIDLINE_RECOUP_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_PROG_PYMT_RATE_PERC) AS BIDLINE_PROG_PYMT_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_RETAINAGE_RATE_PERC) AS BIDLINE_RETAINAGE_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_MAX_RETAINAGE_AMT) AS BIDLINE_MAX_RETAINAGE_AMT,*/
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL, decode(AH.SEALED_AUCTION_STATUS,'LOCKED',FL2.MEANING,
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_BIDLINE_RANK(AH.AUCTION_HEADER_ID,PITMDS.ITEM_LINE_NUMBER,PBIDS.BID_NUMBER,PBIDS.BIDLINE_NUMBER,AH.RANK_INDICATOR,AH.BID_RANKING,PBIDS.BIDLINE_RANK,PITMDS.BEST_BID_NUMBER,PITMDS.BEST_BID_BID_NUMBER)))
 AS BIDLINE_RANK,
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',NULL,PBIDS.BIDLINE_AWARD_STATUS) AS BIDLINE_AWARD_STATUS,
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_NEG_LINE_REFS AS BIDLINE_NEG_LINE_REFS,   */
PBIDS.BIDLINE_LINK_TYPE AS BIDLINE_LINK_TYPE,
/*PBIDS.BIDLINE_LINE_DONE_BY AS BIDLINE_LINE_DONE_BY, */
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' , MSIT.DESCRIPTION) ITEM_DESCRIPTION,
       	FSP.INVENTORY_ORGANIZATION_ID AS ITEM_ORGANIZATION_ID,
       	ITM.CATEGORY_NAME AS ITEM_CATEGORY_NAME,
       	ICCT.CATEGORY_NAME AS ITEM_IP_CATEGORY_NAME,
       	ITM.LINE_ORIGINATION_CODE AS ITEM_LINE_ORIG_CODE,
       	LT.LINE_TYPE AS ITEM_LINE_TYPE,
        ITM.QUANTITY  AS ITEM_QUANTITY,
	    UNITS.UNIT_OF_MEASURE_TL AS ITEM_UNIT_OF_MEASURE,
	    ITM.BID_START_PRICE AS ITEM_BID_START_PRICE,
	    ITM.TARGET_PRICE AS ITEM_TARGET_PRICE,
	    ITM.CURRENT_PRICE AS ITEM_CURRENT_PRICE,
	    DECODE(ITM.GROUP_TYPE ,'LOT_LINE',TO_NUMBER(NULL),'GROUP', TO_NUMBER(NULL),
	               DECODE(ITM.CURRENT_PRICE,TO_NUMBER(NULL),TO_NUMBER(NULL),DECODE(ITM.TARGET_PRICE,TO_NUMBER(NULL),TO_NUMBER(NULL),NVL(ITM.QUANTITY,1) * (ITM.CURRENT_PRICE - ITM.TARGET_PRICE)))) AS ITEM_PROJECTED_SAVINGS,
	    DECODE(ITM.GROUP_TYPE ,'LOT_LINE',TO_NUMBER(NULL),'GROUP', TO_NUMBER(NULL),DECODE(ITM.CURRENT_PRICE,TO_NUMBER(NULL),TO_NUMBER(NULL),NVL(ITM.QUANTITY,1) * ITM.CURRENT_PRICE)) AS ITEM_PROJECTED_SAVINGS_DENOM,
	    DECODE(ITM.GROUP_TYPE ,'LOT_LINE',TO_NUMBER(NULL)