ECC Sourcing, Negotiations, SQL2
Description
Categories: Enterprise Command Center
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
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
Run
ECC Sourcing, Negotiations, SQL2 and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |