ECC Sourcing, Negotiations, SQL2

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,  pon_auc_dfv.*,  pon_auc_int_dfv.*,
 
pon_pcc_negotiations_util_pvt.get_auction_status(ah.auction_header_id,hrou.language) AS AUCTION_STATUS,
ps.display_name as OUTCOME,
FL2.MEANING STYLE,
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,
FL10.MEANING as SECURITY_LEVEL_CODE,
HROU.NAME  AH_OPERATING_UNIT,
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,
PITMDS.ITEM_LINE_GROUP_TYPE,
decode(AH.SEALED_AUCTION_STATUS, 'LOCKED',FL2.MEANING,PBIDS.BID_NUMBER) AS BID_NUMBER_DISP,
PBIDS.BID_STATUS,
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,
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',NULL,PBIDS.BIDLINE_UOM) AS BIDLINE_UOM,
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',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',NULL,PBIDS.BIDLINE_AWARD_STATUS) AS BIDLINE_AWARD_STATUS,
PITMDS.ITEM_UNIT_OF_MEASURE,
PITMDS.ITEM_DESCRIPTION,
PBIDS.BID_SHORTLIST_FLAG,
PITMDS.ITEM_LINE_TYPE,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',NULL,decode(AH.SEALED_AUCTION_STATUS,'LOCKED',FL2.MEANING,PBIDS.BIDLINE_QUOTE_PRICE)) AS BIDLINE_QUOTE_PRICE,
PBIDS.BIDLINE_LINK_TYPE AS BIDLINE_LINK_TYPE,
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,
 HROU.LANGUAGE as LANGUAGE
 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 IN ('US')
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