ECC Sourcing, Negotiations, SQL1

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Auction Header Id, Document Number, Auction Title, Description, Auction Status Code, Style Name, Auction Type, Trading Partner Name, Open Bidding Date ...
Imported from Enterprise Command Center
Dataset Key: pon-pcc-neg
Query Procedure: PON_PCC_NEGOTIATIONS_UTIL_PVT.get_ecc_data_load_info
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesPON
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