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)) , '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 IN ('US')
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  , (select "ROW_ID" "'PON_AUC_ROW_ID'","CONTEXT_VALUE" "'PON_AUC_CONTEXT_VALUE'","MOBILE_NUMBER" "'PON_AUC_MOBILE_NUMBER'","LANDLINE_NUMBER" "'PON_AUC_LANDLINE_NUMBER'","PHONE" "'PON_AUC_PHONE'","EMAIL" "'PON_AUC_EMAIL'","FAX" "'PON_AUC_FAX'","CONCATENATED_SEGMENTS" "'PON_AUC_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",EXT_ATTRIBUTE_CATEGORY "CONTEXT_VALUE",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Supplier Contact Details',EXT_ATTRIBUTE1,'Buyer Contact Details',EXT_ATTRIBUTE1,NULL)) "MOBILE_NUMBER",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Supplier Contact Details',EXT_ATTRIBUTE2,'Buyer Contact Details',EXT_ATTRIBUTE2,NULL)) "LANDLINE_NUMBER",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Communication Method',EXT_ATTRIBUTE1,NULL)) "PHONE",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Communication Method',EXT_ATTRIBUTE2,NULL)) "EMAIL",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Communication Method',EXT_ATTRIBUTE3,NULL)) "FAX",(DECODE(EXT_ATTRIBUTE_CATEGORY,'Buyer Contact Details',EXT_ATTRIBUTE_CATEGORY||'.'||EXT_ATTRIBUTE1||'.'||EXT_ATTRIBUTE2,'Communication Method',EXT_ATTRIBUTE_CATEGORY||'.'||EXT_ATTRIBUTE1||'.'||EXT_ATTRIBUTE2||'.'||EXT_ATTRIBUTE3,'Supplier Contact Details',EXT_ATTRIBUTE_CATEGORY||'.'||EXT_ATTRIBUTE1||'.'||EXT_ATTRIBUTE2,NULL)) "CONCATENATED_SEGMENTS" from PON_AUCTION_HEADERS_ALL )) pon_auc_dfv  , (select "ROW_ID" "'PON_AUC_INT_ROW_ID'","CONTEXT_VALUE" "'PON_AUC_INT_CONTEXT_VALUE'","EXPRESS_DELIVERY" "'PON_AUC_INT_EXPRESS_DELIVERY'","NORMAL_DELIVERY" "'PON_AUC_INT_NORMAL_DELIVERY'","TURNOVER_DISCOUNT" "'PON_AUC_INT_TURNOVER_DISCOUNT'","COMPETITIVE_PRICE" "'PON_AUC_INT_COMPETITIVE_PRICE'","PREMIUM" "'PON_AUC_INT_PREMIUM'","STANDARD" "'PON_AUC_INT_STANDARD'","CONCATENATED_SEGMENTS" "'PON_AUC_INT_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",INT_ATTRIBUTE_CATEGORY "CONTEXT_VALUE",(DECODE(INT_ATTRIBUTE_CATEGORY,'Delivery Standards',INT_ATTRIBUTE1,NULL)) "EXPRESS_DELIVERY",(DECODE(INT_ATTRIBUTE_CATEGORY,'Delivery Standards',INT_ATTRIBUTE2,NULL)) "NORMAL_DELIVERY",(DECODE(INT_ATTRIBUTE_CATEGORY,'Pricing Policy',INT_ATTRIBUTE1,NULL)) "TURNOVER_DISCOUNT",(DECODE(INT_ATTRIBUTE_CATEGORY,'Pricing Policy',INT_ATTRIBUTE2,NULL)) "COMPETITIVE_PRICE",(DECODE(INT_ATTRIBUTE_CATEGORY,'Product Quality',INT_ATTRIBUTE1,NULL)) "PREMIUM",(DECODE(INT_ATTRIBUTE_CATEGORY,'Product Quality',INT_ATTRIBUTE2,NULL)) "STANDARD",(DECODE(INT_ATTRIBUTE_CATEGORY,'Delivery Standards',INT_ATTRIBUTE_CATEGORY||'.'||INT_ATTRIBUTE1||'.'||INT_ATTRIBUTE2,'Pricing Policy',INT_ATTRIBUTE_CATEGORY||'.'||INT_ATTRIBUTE1||'.'||INT_ATTRIBUTE2,'Product Quality',INT_ATTRIBUTE_CATEGORY||'.'||INT_ATTRIBUTE1||'.'||INT_ATTRIBUTE2,NULL)) "CONCATENATED_SEGMENTS" from PON_AUCTION_HEADERS_ALL )) pon_auc_int_dfv 
 
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 IN ('US')
  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(+) = AH.BUYER_ID
  AND FL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
  AND FL.LOOKUP_CODE(+) = HZ1.PERSON_PRE_NAME_ADJUNCT
  AND FL.VIEW_APPLICATION_ID(+) = 222
  AND FL.SECURITY_GROUP_ID(+) = 0
  AND Nvl(FL.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND AH.AUCTION_STATUS <> 'DELETED'
  AND AH.DOCTYPE_ID = DOC.DOCTYPE_ID
  AND FL1.LOOKUP_TYPE = 'PON_AUCTION_DOC_TYPES'
  AND FL1.LOOKUP_CODE = DOC.INTERNAL_NAME
  AND Nvl(FL1.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND DOC.INTERNAL_NAME <> 'SOLICITATION'
  AND Decode(Nvl(AH.SUPP_REG_QUAL_FLAG, 'N'), 'Y', 'Y','N',
                  Nvl(AH.SUPP_EVAL_FLAG, 'N'), 'Y', 'Y','N') ='N'
  AND FL2.LOOKUP_TYPE(+) = 'PON_BID_VISIBILITY_CODE'
  AND FL2.LOOKUP_CODE(+) = NVL(AH.BID_VISIBILITY_CODE,'-9999')
  AND Nvl(FL2.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND FL3.LOOKUP_TYPE(+) = 'PON_BID_RANKING_CODE'
  AND FL3.LOOKUP_CODE(+) = NVL(AH.BID_RANKING,'-9999')
  AND Nvl(FL3.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND FL8.LOOKUP_TYPE(+) = 'PON_RANK_INDICATOR_CODE'
  AND FL8.LOOKUP_CODE(+) = AH.RANK_INDICATOR
  AND Nvl(FL8.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND FL10.LOOKUP_TYPE(+) = 'PON_SECURITY_LEVEL_CODE'
  AND FL10.LOOKUP_CODE(+) = NVL(AH.SECURITY_LEVEL_CODE,'-9999')
  AND Nvl(FL10.LANGUAGE,HROU.LANGUAGE) =  HROU.LANGUAGE
  AND AH.SHIP_TO_LOCATION_ID	=	HR1.LOCATION_ID(+)
  AND AH.BILL_TO_LOCATION_ID	=	HR2.LOCATION_ID(+)
 AND AH.CREATION_DATE >= nvl(fnd_date.Canonical_to_date(fnd_profile.Value('PO_PSC_ITEM_SUPP_LOAD_CUT_OFF')),AH.CREATION_DATE)  AND ah.rowid=pon_auc_dfv."'PON_AUC_ROW_ID'"(+)  AND ah.rowid=pon_auc_int_dfv."'PON_AUC_INT_ROW_ID'"(+)  )  PIVOT(max(AH_OPERATING_UNIT) as AH_OPERATING_UNIT,
max(STYLE) as STYLE,
max(BUYER_NAME) as BUYER_NAME,
max(LATEST_VERSION) as LATEST_VERSION,
max(AUCTION_STATUS) as AUCTION_STATUS,
max(TWO_PART_FLAG) as TWO_PART_FLAG,
max(DOCTYPE) as DOCTYPE,
max(BID_RANKING_DISPLAY) as BID_RANKING_DISPLAY,
max(RANK_INDICATOR) as RANK_INDICATOR,
max(SECURITY_LEVEL_CODE) as SECURITY_LEVEL_CODE,
max(ITEM_LINE_GROUP_TYPE) as ITEM_LINE_GROUP_TYPE,
max(ITEM_UNIT_OF_MEASURE) as ITEM_UNIT_OF_MEASURE,
max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION,
max(BID_STATUS) as BID_STATUS,
max(BIDLINE_LINE_TYPE) as BIDLINE_LINE_TYPE,
max(BIDLINE_UOM) as BIDLINE_UOM,
max(BIDLINE_AWARD_STATUS) as BIDLINE_AWARD_STATUS,
max(BIDLINE_LINK_TYPE) as BIDLINE_LINK_TYPE ,
max(BIDLINE_GROUP_TYPE) as BIDLINE_GROUP_TYPE,
max(BID_NUMBER_DISP) as BID_NUMBER_DISP,
max(BID_SHORTLIST_FLAG) as BID_SHORTLIST_FLAG,
max(SURROG_BID_FLAG) as SURROG_BID_FLAG,
max(OUTCOME) as OUTCOME,
max(ITEM_LINE_TYPE) as ITEM_LINE_TYPE,
max(BID_TOTAL) as BID_TOTAL,
max(BIDLINE_QUOTE_PRICE) as BIDLINE_QUOTE_PRICE,
max(BIDLINE_TOTAL) as BIDLINE_TOTAL,
max(BIDLINE_RANK) as BIDLINE_RANK
for LANGUAGE in ('US' "US"   ))
) x
where
2=2