Oracle Apps Knowledge Sharing

Query to fetch Request for Quotation(RFQ) details in Oracle Apps R12 (PON_AUCTION_HEADERS_ALL, PON_BID_HEADERS, PON_AUCTION_ITEM_PRICES_ALL, PON_BID_ITEM_PRICES)

Below query will help you to extract key details related to RFQ’s from iSourcing module. It also gives you the joins between key tables. Hope it helps.

Key Tables:

PON_AUCTION_HEADERS_ALL

PON_BID_HEADERS

PON_AUCTION_ITEM_PRICES_ALL

PON_BID_ITEM_PRICES

Query:

SELECT ah.document_number rfq_number,
       ah.auction_header_id,
       ah.auction_title,
       nvl((SELECT auc_sts.meaning
              FROM fnd_lookup_values auc_sts
             WHERE auc_sts.lookup_type = 'PON_AUCTION_STATUS'
               AND auc_sts.lookup_code = ah.auction_status
               AND auc_sts.language = USERENV('LANG')
            ),ah.auction_status
           ) auction_status, 
       (SELECT auc_awrd_sts.meaning
          FROM fnd_lookup_values auc_awrd_sts
         WHERE auc_awrd_sts.lookup_type = 'PON_AWARD_STATUS'
           AND auc_awrd_sts.lookup_code = ah.award_status
           AND auc_awrd_sts.language = USERENV('LANG')
       ) auction_award_status, 
       ah.org_id,
       hou.NAME operating_unit_name,
       ah.trading_partner_contact_name,
       ah.trading_partner_contact_id,
       ah.creation_date,
       ah.publish_date,
       ah.open_bidding_date,
       ah.close_bidding_date,
       ah.award_by_date,
       ah.currency_code,
       ah.language_code,
       paipa.line_number,
       TO_NUMBER(paipa.document_disp_line_number) display_rfq_line_number, 
       paipa.item_description,
       paipa.category_name unspsc,
       paipa.unit_of_measure,
       paipa.quantity auction_quantity,
       paipa.need_by_date,
       paipa.best_bid_price,
       paipa.best_bid_quantity,
       paipa.best_bid_number,
       paipa.best_bid_promised_date,
       ah.number_of_bids,
       bh.bid_number bid_number,
       bh.trading_partner_contact_name bidder_username,
       bh.trading_partner_name bidder_name,
       pbipa.item_description bid_line_item_description,
       pbipa.category_name bid_line_category_name,
       pbipa.quantity bid_line_quantity,
       pbipa.unit_of_measure bid_line_uom,
       pbipa.price bid_line_price,
       pbipa.promised_date,
       (SELECT bid_line_awrd_sts.meaning
          FROM fnd_lookup_values bid_line_awrd_sts
         WHERE bid_line_awrd_sts.lookup_type = 'PON_ITEM_AWARD_STATUS'
           AND bid_line_awrd_sts.lookup_code = pbipa.award_status
           AND bid_line_awrd_sts.language = USERENV('LANG')
       ) bid_line_award_status, 
       pbipa.award_date bid_line_award_date,
       pbipa.bid_currency_price,
       (SELECT bid_sts.meaning
          FROM fnd_lookup_values bid_sts
         WHERE bid_sts.lookup_type = 'PON_BID_STATUS'
           AND bid_sts.lookup_code = bh.bid_status
           AND bid_sts.language = USERENV('LANG')
       ) bid_status,
	   bh.disqualify_reason,
       (SELECT awrd_sts.meaning
          FROM fnd_lookup_values awrd_sts
         WHERE awrd_sts.lookup_type = 'PON_AWARD_STATUS'
           AND awrd_sts.lookup_code = bh.award_status
           AND awrd_sts.language = USERENV('LANG')
           AND pbipa.award_quantity IS NOT NULL
       ) award_status,
       bh.bid_expiration_date,
       bh.bid_currency_code,
       bh.buyer_bid_total,
       bh.vendor_id,
       bh.vendor_site_id,
       bh.internal_note,
       bh.shortlist_flag,
       bh.total_award_amount,      
       fu.user_name quote_created_by, 
       pbipa.creation_date quote_created_on, 
       pbipa.publish_date quote_submitted_on                                      
  FROM pon_auction_headers_all     ah,
       hr_all_organization_units   hou,
       pon_bid_headers             bh,
       pon_auction_item_prices_all paipa,
       pon_bid_item_prices         pbipa,
       fnd_user fu 
 WHERE bh.auction_header_id    = ah.auction_header_id
   AND hou.organization_id     = ah.org_id
   AND paipa.auction_header_id = ah.auction_header_id
   AND bh.bid_number           = pbipa.bid_number
   AND paipa.line_number       = pbipa.auction_line_number
   AND paipa.auction_header_id = pbipa.auction_header_id
   AND pbipa.created_by        = fu.user_id