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