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 ...
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
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 |