ECC Sourcing, Negotiations, SQL1

Description

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' ... 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', 'Pon Auc Int Context Value', 'Pon Auc Int Express Delivery', 'Pon Auc Int Normal Delivery', 'Pon Auc Int Turnover Discount', 'Pon Auc Int Competitive Price', 'Pon Auc Int Premium', 'Pon Auc Int Standard', 'Pon Auc Int Concatenated Segments', Auction Header Id, Document Number, Auction Title, Description, Auction Status Code, Style Name, Auction Type, Trading Partner Name, Open Bidding Date, Close Bidding Date, Preview Date, Award By Date, Publish Date, Close Date, Cancel Date, Total Bids, Ah Currency Code, Creation Date, Auction Round Number, Neg Creation Month, Neg Creation Year, Event Title, Award Complete Date, Org Id, Amendment Number, Amendment Description, Is Paused, Active Document, Negotiation Amount, Active Bids, Unread Message Count, Auction Header Id Prev Round, Auction Header Id Prev Amend, Award Status, Award Status Flag, Award Date, Total Draft Time, Total Bid Time, Total Analyse Time, Total Award Time, Total Cycle Time, Authorized User Ids, Security Level Code Code, Number Of Bids, Winning Offers, Ah Supp Count, Item Disp Line Number, Group Type Code, Item Line Number, Item Num Rev, Item Category Name, Item Quantity, Item Target Price, Item Current Price, Item Need By Start Date, Item Need By Date, Number Of Bids Of Item, Backing Reqs Info, Backing Blanket Info, Item Projected Savings, Item Negotiated Savings, Best Bid Number, Bid Number, Bid Publish Date, Bid Effective Date, Bid Expiration Date, Bid Supplier Contact, Bid Quote Total Amount, Awarded Bids, Awarded Quotes, Bid Award Status Code, Bid Supplier Contact Name, Bid Supplier Name, Bid Vendor Site Code, Bid Currency Code, Order Created From Bid, Po Header Id From Bid, Bid Award Date, Bid Total Score, Bidline Disp Number, Bidline Number, Bidline Item Num Rev, Bidline Item Category Name, Bidline Item Desc, Bidline Need By Start Date, Bidline Need By Date, Bidline Promised Date, Bidline Target Quantity, Bidline Quote Quantity, Bidline Start Price, Bidline Target Price, Bidline Po Min Rel Amt, Bidline Po Bid Min Rel Amt, Bidline Active Bids, Bidline Award Date, Bidline Award Quantity, Bidline Award Price, Bidline Order Number, Bidline Creation Date, Bidline Score, Bid Line Identifier, Us Ah Operating Unit, Us Style, Us Buyer Name, Us Latest Version, Us Auction Status, Us Two Part Flag, Us Doctype, Us Bid Ranking Display, Us Rank Indicator, Us Security Level Code, Us Item Line Group Type, Us Item Unit Of Measure, Us Item Description, Us Bid Status, Us Bidline Line Type, Us Bidline Uom, Us Bidline Award Status, Us Bidline Link Type, Us Bidline Group Type, Us Bid Number Disp, Us Bid Shortlist Flag, Us Surrog Bid Flag, Us Outcome, Us Item Line Type, Us Bid Total, Us Bidline Quote Price, Us Bidline Total, Us Bidline Rank

Imported from ECC

Copy

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.*,
AH.AUCTION_HEADER_ID,
AH.DOCUMENT_NUMBER,
AH.AUCTION_TITLE,
AH.ABSTRACT_DETAILS AS DESCRIPTION,
AH.AUCTION_STATUS AS AUCTION_STATUS_CODE,
pon_pcc_negotiations_util_pvt.get_auction_status(ah.auction_header_id,hrou.language) AS AUCTION_STATUS,
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,
ps.display_name as OUTCOME,
FL2.MEANING STYLE,
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,
/*HR1.LOCATION_CODE AS SHIP_TO_LOCATION,
HR2.LOCATION_CODE AS BILL_TO_LOCATION,*/
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,
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,
/*AH.PO_MIN_REL_AMOUNT,
AH.GLOBAL_AGREEMENT_FLAG,*/
AH.AWARD_COMPLETE_DATE,
FL10.MEANING as SECURITY_LEVEL_CODE,
HROU.NAME  AH_OPERATING_UNIT,
AH.ORG_ID,
AH.AMENDMENT_NUMBER,
AH.AMENDMENT_DESCRIPTION,
AH.IS_PAUSED,
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,
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,
/*case when
AH.AUCTION_STATUS='ACTIVE' or (AH.AUCTION_STATUS='AUCTION_CLOSED' and AH.AWARD_STATUS IN ('COMPLETED','PARTIAL'))  then
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_TOTAL_AMEND_ROUNDS(AH.AUCTION_HEADER_ID)
else
0 end null as  TOTAL_AMEND_ROUNDS,  */
PON_PCC_NEGOTIATIONS_UTIL_PVT.GET_NEG_TOTAL_AMOUNT(AH.AUCTION_HEADER_ID) as NEGOTIATION_AMOUNT,
/*PON_AUCTION_PKG.TIME_REMAINING(AH.AUCTION_HEADER_ID) TIMELEFT,*/
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,
/*NEG_TEAM.USER_ID AS COLLABMEM_USER_ID,
NEG_TEAM.USER_NAME AS COLLABMEM_USER_NAME,
DECODE(NEG_TEAM.MEMBER_TYPE,'N',FND_MESSAGE.GET_STRING('PON','PON_AUC_MEMBER_ROLE'),'M',FND_MESSAGE.GET_STRING('PON','PON_AUC_MEMBER_ROLE'),'C',FND_MESSAGE.GET_STRING('PON','PON_AUC_OWNER_ROLE')) AS COLLABMEM_ROLE, */
PITMDS.ITEM_DISP_LINE_NUMBER,
PITMDS.GROUP_TYPE_CODE,
PITMDS.ITEM_LINE_GROUP_TYPE,
PITMDS.ITEM_LINE_NUMBER,
/*PITMDS.ITEM_PARENT_LINE_NUM, */
PITMDS.ITEM_NUM_REV,
PITMDS.ITEM_DESCRIPTION,
/*PITMDS.ITEM_ORGANIZATION_ID,*/
PITMDS.ITEM_CATEGORY_NAME,
PITMDS.ITEM_LINE_TYPE,
PITMDS.ITEM_QUANTITY,
PITMDS.ITEM_UNIT_OF_MEASURE,
/*PITMDS.ITEM_BID_START_PRICE,*/
PITMDS.ITEM_TARGET_PRICE,
PITMDS.ITEM_CURRENT_PRICE,
PITMDS.ITEM_NEED_BY_START_DATE,
PITMDS.ITEM_NEED_BY_DATE,
/*PITMDS.ITEM_SHIP_TO_ADDRESS,
PITMDS.ITEM_PRICE_BREAK_TYPE,*/
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,
decode(AH.SEALED_AUCTION_STATUS, 'LOCKED',FL2.MEANING,PBIDS.BID_NUMBER) AS BID_NUMBER_DISP,
PBIDS.BID_NUMBER,
PBIDS.BID_STATUS,
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.BID_SHORTLIST_FLAG,
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,
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),NVL(PBIDS.BIDLINE_PARENT_LINE_NUMBER,PITMDS.ITEM_PARENT_LINE_NUM)) AS BIDLINE_PARENT_LINE_NUMBER,*/
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',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',NULL,NVL(PBIDS.BIDLINE_LINE_TYPE,PITMDS.ITEM_LINE_TYPE)) AS BIDLINE_LINE_TYPE,
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',NULL,PBIDS.BIDLINE_UOM) AS BIDLINE_UOM,
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',NULL,decode(AH.SEALED_AUCTION_STATUS,'LOCKED',FL2.MEANING,PBIDS.BIDLINE_QUOTE_PRICE)) AS BIDLINE_QUOTE_PRICE,
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',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),PBIDS.BIDLINE_ADVANCE_AMOUNT) AS BIDLINE_ADVANCE_AMOUNT,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_RECOUP_RATE_PERC) AS BIDLINE_RECOUP_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_PROG_PYMT_RATE_PERC) AS BIDLINE_PROG_PYMT_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_RETAINAGE_RATE_PERC) AS BIDLINE_RETAINAGE_RATE_PERC,
DECODE (AH.TECHNICAL_LOCK_STATUS,'LOCKED',TO_NUMBER(NULL),PBIDS.BIDLINE_MAX_RETAINAGE_AMT) AS BIDLINE_MAX_RETAINAGE_AMT,*/
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,
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',NULL,PBIDS.BIDLINE_AWARD_STATUS) AS BIDLINE_AWARD_STATUS,
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_NEG_LINE_REFS AS BIDLINE_NEG_LINE_REFS,   */
PBIDS.BIDLINE_LINK_TYPE AS BIDLINE_LINK_TYPE,
/*PBIDS.BIDLINE_LINE_DONE_BY AS BIDLINE_LINE_DONE_BY, */
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)