Saturday, August 8, 2020
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “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)”
Post a Comment