ECC Sourcing, Negotiations, SQL2

Description
Categories: Enterprise Command Center
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 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,
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(+