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' , 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),'GROUP', TO_NUMBER(NULL),PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEGOTIATED_SAVINGS(ITM.AUCTION_HEADER_ID,ITM.LINE_NUMBER)) AS ITEM_NEGOTIATED_SAVINGS,
	   	DECODE(ITM.GROUP_TYPE ,'LOT_LINE',TO_NUMBER(NULL),'GROUP', TO_NUMBER(NULL),DECODE(ITM.CURRENT_PRICE,TO_NUMBER(NULL),TO_NUMBER(NULL),PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEGOTIATED_SAVINGS_DENOM(ITM.AUCTION_HEADER_ID,ITM.LINE_NUMBER)))
		AS ITEM_NEGOTIATED_SAVINGS_DENOM,
        ITM.NEED_BY_START_DATE AS  ITEM_NEED_BY_START_DATE,
        ITM.NEED_BY_DATE AS ITEM_NEED_BY_DATE,
        HR3.LOCATION_CODE  AS ITEM_SHIP_TO_ADDRESS,
        ITM.PRICE_BREAK_TYPE AS ITEM_PRICE_BREAK_TYPE,
        ITM.PO_AGREED_AMOUNT AS ITEM_PO_AGREED_AMOUNT,
        ITM.PO_MIN_REL_AMOUNT AS ITEM_PO_MIN_REL_AMOUNT,
        JOBS.NAME AS  ITEM_JOB_DISPLAY,
        ITM.ADVANCE_AMOUNT AS ITEM_ADVANCE_AMT,
        ITM.RECOUPMENT_RATE_PERCENT AS ITEM_RECOUP_RATE_PERC,
        ITM.PROGRESS_PYMT_RATE_PERCENT AS ITEM_PROG_PYMT_RATE_PERC,
        ITM.RETAINAGE_RATE_PERCENT AS ITEM_RETAINAGE_RATE_PERC,
        ITM.MAX_RETAINAGE_AMOUNT AS ITEM_MAX_RETAINAGE_AMT,
        ITM.PROJECT_EXPENDITURE_ITEM_DATE AS ITEM_PROJ_EXP_ITEM_DATE,
        PROJ.SEGMENT1     AS ITEM_PROJ_NUMBER,
        TASK.TASK_NUMBER  AS ITEM_PROJ_TASK_NUMBER,
        GMS1.AWARD_NUMBER  AS ITEM_PROJ_AWARD_NUMBER,
        ORGHR.NAME        AS PROJ_EXP_ORG_NAME,
        ITM.PROJECT_EXPENDITURE_TYPE AS ITEM_PROJ_EXP_TYPE,
        NVL2(ITM.WORK_APPROVER_USER_ID, (SELECT PER.FULL_NAME
                                   FROM PER_ALL_PEOPLE_F PER
                                   WHERE PER.PERSON_ID = USR.EMPLOYEE_ID
                                   AND PER.EFFECTIVE_END_DATE =
                                               (SELECT MAX(PER1.EFFECTIVE_END_DATE)
                                                 FROM PER_ALL_PEOPLE_F PER1
                                                 WHERE PER.PERSON_ID = PER1.PERSON_ID)), NULL) AS  ITEM_WORK_APPR_USER_NAME,
        PON_OA_UTIL_PKG.GET_ACTIVE_BID_COUNT(ITM.AUCTION_HEADER_ID, ITM.LINE_NUMBER) NUMBER_OF_BIDS_OF_ITEM,
        DECODE(NVL(MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'), 'Y', MSIT.LAST_UPDATE_DATE,TO_DATE(NULL)) AS MAST_ITEM_DESC_LAST_UPD,
        ITM.BEST_BID_NUMBER,
        ITM.BEST_BID_BID_NUMBER,
        ITM.SOURCE_DOC_NUMBER,
        ITM.SOURCE_LINE_NUMBER,
        ITM.LAST_UPDATE_DATE AS ITEM_LAST_UPD_DATE,
        HROU.LANGUAGE
FROM  PON_AUCTION_ITEM_PRICES_ALL ITM,
      pon_auction_headers_all pah,
      HR_ALL_ORGANIZATION_UNITS_TL HROU,
      MTL_UNITS_OF_MEASURE_TL UNITS,
      PO_LINE_TYPES_TL LT,
      HR_LOCATIONS HR3,
      PER_JOBS_VL JOBS,
      FINANCIALS_SYSTEM_PARAMS_ALL FSP,
      PA_PROJECTS_ALL PROJ,
      PA_TASKS TASK,
      GMS_AWARDS_ALL GMS1,
      HR_ALL_ORGANIZATION_UNITS ORGHR,
      FND_USER USR,
      MTL_SYSTEM_ITEMS_TL MSIT ,
      MTL_SYSTEM_ITEMS_KFV MSI,
      ICX_CAT_CATEGORIES_V ICCT,
      FND_LOOKUP_VALUES FL1
WHERE pah.auction_header_id=itm.auction_header_id
AND HROU.ORGANIZATION_ID (+) = pah.ORG_ID
and HROU.LANGUAGE=USERENV('LANG')
and ITM.SHIP_TO_LOCATION_ID = HR3.LOCATION_ID(+)
AND ITM.IP_CATEGORY_ID = ICCT.RT_CATEGORY_ID(+)
AND icct.LANGUAGE(+) = USERENV('LANG')
AND ITM.LINE_TYPE_ID =  LT.LINE_TYPE_ID(+)
AND nvl(LT.LANGUAGE,HROU.LANGUAGE) = HROU.LANGUAGE
AND ITM.UOM_CODE = UNITS.UOM_CODE(+)
AND nvl(UNITS.LANGUAGE,HROU.LANGUAGE) = HROU.LANGUAGE
AND ITM.JOB_ID = JOBS.JOB_ID(+)
AND ITM.ORG_ID = FSP.ORG_ID(+)
AND ITM.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
AND NVL(MSIT.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND ITM.ITEM_ID  = MSI.INVENTORY_ITEM_ID(+)
AND NVL(MSI.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID)  = FSP.INVENTORY_ORGANIZATION_ID
AND nvl(MSIT.LANGUAGE,HROU.LANGUAGE)= HROU.LANGUAGE
AND	FL1.LOOKUP_TYPE(+) = 'PON_GROUP_TYPE'
AND FL1.LOOKUP_CODE(+) = NVL(ITM.GROUP_TYPE,'-9999')
AND nvl(FL1.LANGUAGE,HROU.LANGUAGE) =HROU.LANGUAGE
AND ITM.PROJECT_ID = PROJ.PROJECT_ID(+)
AND ITM.PROJECT_TASK_ID = TASK.TASK_ID(+)
AND ITM.PROJECT_AWARD_ID = GMS1.AWARD_ID(+)
AND ITM.PROJECT_EXP_ORGANIZATION_ID = ORGHR.ORGANIZATION_ID(+)
AND ITM.WORK_APPROVER_USER_ID = USR.USER_ID(+))   PITMDS,
(SELECT	PBD.AUCTION_HEADER_ID,
      	PBD.BID_NUMBER AS BID_NUMBER,
        FL2.MEANING AS BID_STATUS,
        PBD.PUBLISH_DATE AS BID_PUBLISH_DATE,
        PBD.BID_EFFECTIVE_DATE,
        PBD.BID_EXPIRATION_DATE,
        PBD.TRADING_PARTNER_CONTACT_NAME AS BID_SUPPLIER_CONTACT,
        PBD.TRADING_PARTNER_CONTACT_ID AS BID_SUPPLIER_CONTACT_ID,
        PBD.TRADING_PARTNER_NAME AS BID_SUPPLIER_NAME,
        PBD.VENDOR_SITE_CODE  AS BID_VENDOR_SITE_CODE,
        decode(nvl(PBD.SHORTLIST_FLAG,'N'),'N',pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_NO','396',fl2.language),
        pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_YES','396',fl2.language))  AS BID_SHORTLIST_FLAG,
        decode(nvl(PBD.SURROG_BID_FLAG,'N'),'N',pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_NO','396',fl2.language),
        pon_pcc_negotiations_util_pvt.get_fnd_message('PON_CORE_YES','396',fl2.language))  AS SURROG_BID_FLAG,
        PBD.ORDER_NUMBER  AS  ORDER_CREATED_FROM_BID,
        PBD.PO_HEADER_ID AS PO_HEADER_ID_FROM_BID,
        PBD.AWARD_DATE AS BID_AWARD_DATE,
        PBD.LAST_UPDATE_DATE AS BID_LAST_UPD_DATE,
        BL.DOCUMENT_DISP_LINE_NUMBER AS BIDLINE_DISP_NUMBER,
        NULL AS BIDLINE_PARENT_LINE_NUMBER,
        NULL AS BIDLINE_GROUP_TYPE,
        BL.LINE_NUMBER AS BIDLINE_NUMBER,
        DECODE(BL.AUCTION_LINE_NUMBER,-1,DECODE(NVL(BID_LINE_REF.AUCTION_LINE_NUMBER,-1),-1,-1,BID_LINE_REF.AUCTION_LINE_NUMBER),BL.AUCTION_LINE_NUMBER) AS BIDLINE_AUC_LINE_NUM,
		    MC.CONCATENATED_SEGMENTS AS BIDLINE_ITEM_CATEGORY_NAME,
        BL.ITEM_DESCRIPTION AS BIDLINE_ITEM_DESC,
        LT.LINE_TYPE AS BIDLINE_LINE_TYPE,
        BL.PROMISED_DATE AS BIDLINE_PROMISED_DATE,
        BL.QUANTITY   AS BIDLINE_QUOTE_QUANTITY,
        UNITS.UNIT_OF_MEASURE_TL  AS BIDLINE_UOM,
        BL.BID_START_PRICE  AS BIDLINE_START_PRICE,
        BL.PRICE AS BIDLINE_QUOTE_PRICE,
        TO_NUMBER(NULL) AS BIDLINE_TOTAL,
        BL.PO_MIN_REL_AMOUNT AS BIDLINE_PO_MIN_REL_AMT,
        BL.PO_BID_MIN_REL_AMOUNT AS BIDLINE_PO_BID_MIN_REL_AMT,
        BL.ADVANCE_AMOUNT AS BIDLINE_ADVANCE_AMOUNT,
        BL.RECOUPMENT_RATE_PERCENT AS BIDLINE_RECOUP_RATE_PERC,
        BL.PROGRESS_PYMT_RATE_PERCENT AS BIDLINE_PROG_PYMT_RATE_PERC,
        BL.RETAINAGE_RATE_PERCENT AS BIDLINE_RETAINAGE_RATE_PERC,
        BL.MAX_RETAINAGE_AMOUNT AS BIDLINE_MAX_RETAINAGE_AMT,
        BL.RANK AS BIDLINE_RANK,
        nvl(BL.AWARD_PRICE*BL.AWARD_QUANTITY,0) BID_AWARD_AMOUNT,
        PBD.PO_AGREED_AMOUNT BID_PO_AGREED_AMOUNT,
        NVL(BL.AWARD_STATUS,PBD.AWARD_STATUS) BID_AWARD_STATUS_CODE,
        TO_NUMBER(NULL) AS BIDLINE_ACTIVE_BIDS,
        FL1.MEANING AS BIDLINE_AWARD_STATUS,
        BL.AWARD_DATE AS BIDLINE_AWARD_DATE,
        BL.AWARD_QUANTITY AS BIDLINE_AWARD_QUANTITY,
        BL.AWARD_PRICE AS BIDLINE_AWARD_PRICE,
        BL.ORDER_NUMBER AS BIDLINE_ORDER_NUMBER,
        BL.CREATION_DATE AS BIDLINE_CREATION_DATE,
        BL.LAST_UPDATE_DATE AS BIDLINE_LAST_UPD_DATE,
        DECODE(BL.AUCTION_LINE_NUMBER,-1,PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEG_LINE_REFS_FOR_BIDLINE(BL.AUCTION_HEADER_ID,BL.BID_NUMBER,BL.LINE_NUMBER),NULL) AS BIDLINE_NEG_LINE_REFS,
        DECODE(BID_LINE_REF.LINK_TYPE , NULL,DECODE(BL.AUCTION_LINE_NUMBER,-1,FL3.MEANING,NULL),FL3.MEANING) AS BIDLINE_LINK_TYPE,
        BID_LINE_REF.LINK_DONE_BY AS BIDLINE_LINE_DONE_BY,
        BL.TOTAL_WEIGHTED_SCORE BIDLINE_SCORE,
        FL2.language,
      (SELECT  DECODE(NVL(pah.HDR_ATTR_ENABLE_WEIGHTS, 'N') , 'Y', SUM(NVL(pba.WEIGHTED_SCORE, 0)) , 'N', SUM(NVL(pba.SCORE, 0)) ) TOTAL_SCORE
       FROM     pon_bid_attribute_values pba,
                pon_auction_headers_all pah ,
                pon_auction_sections pas    ,
                pon_bid_headers pbh         ,
                pon_auction_headers_all_v pahv
       WHERE    pba.auction_header_id = PBD.AUCTION_HEADER_ID
       AND pba.bid_number = pbd.bid_number
       AND pba.BID_NUMBER            = pbh.bid_number
            AND pba.LINE_NUMBER           = -1
            AND pba.AUCTION_HEADER_ID     = pah.AUCTION_HEADER_ID
            AND pba.ATTR_GROUP_SEQ_NUMBER = pas.ATTR_GROUP_SEQ_NUMBER
            AND pba.AUCTION_HEADER_ID     = pas.AUCTION_HEADER_ID
            AND pba.AUCTION_HEADER_ID     = pahv.AUCTION_HEADER_ID
            AND (
                         (
                                  pah.TWO_PART_FLAG = 'Y'
                              AND pas.TWO_PART_SECTION_TYPE IN ('TECHNICAL',
                                                                DECODE(pahv.sealed_auction_status,'LOCKED','TECHNICAL',DECODE(pbh.technical_shortlist_flag,'Y','COMMERCIAL','TECHNICAL')))
                         )
                      OR (
                                  NVL(pah.TWO_PART_FLAG,'N') = 'N'
                         ))
      GROUP BY pba.BID_NUMBER ,
                pah.HDR_ATTR_ENABLE_WEIGHTS) AS BID_TOTAL_SCORE
 FROM  	PON_BID_HEADERS PBD,
        PON_BID_ITEM_PRICES BL,
        MTL_UNITS_OF_MEASURE_TL UNITS,
        PO_LINE_TYPES_TL LT,
        FND_LOOKUP_VALUES FL1,
        FND_LOOKUP_VALUES FL2,
        FND_LOOKUP_VALUES FL3,
        MTL_CATEGORIES_KFV  MC,
        (SELECT PBF.AUCTION_HEADER_ID,PBF.BID_NUMBER,PBF.LINE_NUMBER,
                MAX(PBF.AUCTION_LINE_NUMBER) AS AUCTION_LINE_NUMBER ,
                MAX(PBF.LINK_TYPE) AS LINK_TYPE,
                MAX(PBF.LINK_DONE_BY) AS LINK_DONE_BY
        FROM PON_BID_ITEM_REFERENCES PBF
        GROUP BY PBF.AUCTION_HEADER_ID,PBF.BID_NUMBER,PBF.LINE_NUMBER) BID_LINE_REF
WHERE	PBD.AUCTION_HEADER_ID = BL.AUCTION_HEADER_ID
AND   	PBD.BID_NUMBER = BL.BID_NUMBER
AND     BL.BID_NUMBER = BID_LINE_REF.BID_NUMBER(+)
AND     BL.AUCTION_HEADER_ID = BID_LINE_REF.AUCTION_HEADER_ID(+)
AND     BL.LINE_NUMBER = BID_LINE_REF.LINE_NUMBER(+)
AND		  MC.CATEGORY_ID(+) = BL.CATEGORY_ID
AND   	FL1.LOOKUP_TYPE(+) = 'PON_ITEM_AWARD_STATUS'
AND   	FL1.LOOKUP_CODE(+) = NVL(BL.AWARD_STATUS,'-9999')
AND     nvl(FL1.LANGUAGE,FL2.LANGUAGE) = FL2.language
AND   	FL2.LOOKUP_TYPE(+) = 'PON_BID_STATUS'
AND   	FL2.LOOKUP_CODE(+) = NVL(PBD.BID_STATUS,'-9999')
and     FL2.LANGUAGE=USERENV('LANG')
AND     FL3.LOOKUP_TYPE(+) = 'PON_UNSOL_LINK_TYPE'
AND	    FL3.LOOKUP_CODE(+) = NVL(BID_LINE_REF.LINK_TYPE,'ADDITIONAL')
AND     nvl(FL3.LANGUAGE,FL2.LANGUAGE) = FL2.language
AND 	  BL.LINE_TYPE_ID =  LT.LINE_TYPE_ID(+)
AND 	  nvl(LT.LANGUAGE,FL2.language) = FL2.language
AND   	BL.UOM = UNITS.UOM_CODE(+)
AND   	nvl(UNITS.LANGUAGE,FL2.language) =FL2.language
AND   	PBD.BID_STATUS IN ('ACTIVE')
) PBIDS
 
WHERE AH.AUCTION_HEADER_ID = PITMDS.AUCTION_HEADER_ID(+)
  AND NVL(PITMDS.LANGUAGE,HROU.LANGUAGE) = HROU.LANGUAGE
  AND AH.PO_STYLE_ID = PS.STYLE_ID (+)
  AND AH.CONTRACT_TYPE = PS.DOCUMENT_SUBTYPE (+)
  AND nvl(PS.LANGUAGE,HROU.LANGUAGE) = HROU.LANGUAGE
  AND HROU.LANGUAGE = USERENV('LANG')
  AND POSH.STATUS(+) = 'ACTIVE'
  AND POSH.STYLE_ID(+) = AH.STYLE_ID
  AND PITMDS.AUCTION_HEADER_ID = PBIDS.AUCTION_HEADER_ID(+)
  AND pitmds.item_line_number =  pbids.bidline_auc_line_num(+)
  AND NVL(PBIDS.LANGUAGE,HROU.LANGUAGE) = HROU.LANGUAGE
  AND (Nvl(pbids.bidline_auc_line_num,-1) =  Nvl2(pbids.bidline_auc_line_num, PITMDS.ITEM_LINE_NUMBER, -1) OR pbids.bidline_auc_line_num=-1)
  AND AH.AUCTION_HEADER_ID = INVITED_SUPP.AUCTION_HEADER_ID (+)
  AND AH.AUCTION_HEADER_ID = NEG_TEAM.AUCTION_HEADER_ID(+)
  AND HROU.ORGANIZATION_ID (+) = AH.ORG_ID
  AND HZ1.PARTY_ID(+) = AH.TRADING_PARTNER_CONTACT_ID
  AND HZ6.PARTY_ID