ECC Sourcing, Negotiations, SQL1
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, SQL1 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, 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 |