Query to fetch Purchase Receipt and its related Purchase order, Vendor details in Oracle Apps R12

 Key Tables:

RCV_TRANSACTIONS

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

PO_DISTRIBUTIONS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_HEADERS_ALL

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

Query:

SELECT hou.name ou_name, ph.segment1 po_num, ph.po_header_id, pol.po_line_id, pol.line_num, order_type.displayed_field line_type, COALESCE(pd.quantity_ordered,pll.quantity,pol.quantity) quantity, (pol.unit_price * (pd.quantity_ordered - pd.quantity_cancelled)) line_amount, rsh.receipt_source_code, flv_ship_source.meaning receipt_source_code, papf.full_name received_by, rsh.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_id, assa.vendor_site_code, ph.org_id, rsh.shipment_num, rsh.receipt_num, rsh.ship_to_location_id, hl.location_code deliver_to, rsl.item_description, rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received, rsl.unit_of_measure, rsl.vendor_item_num, flv_shipment.meaning shipment_line_status_code, flv_inspection.meaning inspection_status_code, rsl.shipment_line_status_code, rct.inspection_status_code, flv_tran_type.meaning transaction_type, rct.transaction_type transaction_type_lookup_code, rct.transaction_id, nvl(rct.source_doc_quantity,0) transaction_qty, rct.transaction_date, assa.attribute1 vendor_global_code, assa.attribute2 entity_supplier_code, assa.attribute3 vendor_communication_language, pd.attribute1 company_specific_gl_code, pol.attribute2 sanction_number, pol.attribute3 inspection_required, pol.attribute4 end_user_details, rsh.attribute1 Invoice_number, rct.comments gre_comments, rsh.attribute2 exchange_rate_information, rsh.attribute3 gst_invoice_amt, rsh.attribute4 gst_exchange_rate, rsh.attribute5 custom_form_no, rct.attribute1 receipt_line_level_tax_rate from rcv_transactions rct, rcv_shipment_headers rsh, per_all_people_f papf, rcv_shipment_lines rsl, po_distributions_all pd, po_lines_all pol, po_line_locations_all pll, po_headers_all ph, ap_suppliers aps, ap_supplier_sites_all assa, hr_all_organization_units hou, hr_locations hl, fnd_lookup_values flv_shipment, fnd_lookup_values flv_inspection, fnd_lookup_values flv_tran_type, fnd_lookup_values flv_ship_source, po_lookup_codes order_type, po_line_types_b plt WHERE 1 = 1 and rct.po_header_id = ph.po_header_id and rct.po_line_location_id = pll.line_location_id AND rct.po_line_id = pol.po_line_id AND pol.po_line_id = pd.po_line_id and rct.shipment_line_id = rsl.shipment_line_id and rsl.shipment_header_id = rsh.shipment_header_id AND rct.po_distribution_id = pd.po_distribution_id AND rct.employee_id = papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date and rsh.vendor_id = aps.vendor_id and ph.vendor_site_id = assa.vendor_site_id AND hou.organization_id = ph.org_id AND rct.deliver_to_location_id = hl.location_id AND rsl.shipment_line_status_code = flv_shipment.lookup_code AND flv_shipment.lookup_type = 'SHIPMENT LINE STATUS' AND flv_shipment.language = USERENV('LANG') AND rct.inspection_status_code = flv_inspection.lookup_code AND flv_inspection.lookup_type = 'INSPECTION STATUS' AND flv_inspection.language = USERENV('LANG') AND rct.transaction_type = flv_tran_type.lookup_code AND flv_tran_type.lookup_type = 'RCV TRANSACTION TYPE' AND flv_tran_type.language = USERENV('LANG') AND flv_ship_source.lookup_type = 'SHIPMENT SOURCE TYPE' AND flv_ship_source.language = USERENV('LANG') AND flv_ship_source.lookup_code = rsh.receipt_source_code AND order_type.lookup_type = 'ORDER TYPE' AND order_type.lookup_code = plt.order_type_lookup_code AND pol.line_type_id = plt.line_type_id;

Regards,

Amar Alam

Query to Find Requisition Number from Purchase Order Number

Query to find the purchase order number from requisition number:

SELECT DISTINCT pha.segment1
FROM po_headers_all pha,po_distributions_all pda,po_req_distributions_all rda,
po_requisition_headers_all rha,po_requisition_lines_all rla
WHERE 1=1
AND pha.po_header_id=pda.po_header_id
AND pda.req_distribution_id=rda.distribution_id
AND rda.requisition_line_id=rla.requisition_line_id
AND rla.requisition_header_id=rha.requisition_header_id
AND rha.segment1=:P_REQ_NUM

Query to find the requisition number from purchase order number:

SELECT DISTINCT rha.segment1
FROM po_requisition_headers_all rha,po_requisition_lines_all rla,
po_req_distributions_all rda,po_distributions_all pda,po_headers_all pha
WHERE 1=1
AND rha.requisition_header_id=rla.requisition_header_id
AND rla.requisition_line_id=rda.requisition_line_id
AND rda.distribution_id=pda.req_distribution_id
AND pda.po_header_id=pha.po_header_id
AND pha.segment1=:P_PO_NUMBER

Thanks
Amar Alam

PO Matching Report

SELECT
      PHA.TYPE_LOOKUP_CODE,
      PHA.LAST_UPDATE_DATE,
      PHA.SEGMENT1,
      PHA.SUMMARY_FLAG,
      PHA.ENABLED_FLAG,
      PHA.APPROVED_FLAG,
      PLA.UNIT_MEAS_LOOKUP_CODE,    
      PLA.QUANTITY,
      PLA.UNIT_PRICE,
      PLA.LIST_PRICE_PER_UNIT,
      PLA.PRICE_TYPE_LOOKUP_CODE,
      PLL.RECEIPT_REQUIRED_FLAG,
      PLL.INSPECTION_REQUIRED_FLAG,decode
         (INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')  
                              Matching,      
      PLL.ENCUMBERED_FLAG,
      PDA.QUANTITY_ORDERED,    
      PDA.ACCRUAL_ACCOUNT_ID,
      PDA.VARIANCE_ACCOUNT_ID
 FROM
       PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLL,
       PO_DISTRIBUTIONS_ALL PDA
 WHERE
        PDA.PO_HEADER_ID=PLL.PO_HEADER_ID
AND          
       PLL.PO_HEADER_ID=PLA.PO_HEADER_ID
AND
       PLA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND         
       PLA.UNIT_PRICE>1000000000
AND
      decode
(INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')=:MATCH LEVEL


Ur's
AmarAlam

Pending Purchase Orders Report Code

select sysdate REP_DATE,
pv.SEGMENT1 VENDOR_NUM,
ph.segment1 PO_NUM,
PH.REVISION_NUM,
ph.type_lookup_code PO_TYPE,
pol.ITEM_DESCRIPTION,
pol.UNIT_MEAS_LOOKUP_CODE,
pol.UNIT_PRICE,hr.name,
pds.QUANTITY_ORDERED,
PDS.QUANTITY_CANCELLED,
pds.QUANTITY_DELIVERED,
pds.QUANTITY_BILLED,
pds.AMOUNT_BILLED,
pol.CANCEL_DATE,
pds.QUANTITY_ORDERED-pds.QUANTITY_DELIVERED REMANING_QTY,
pv.vendor_name Supplier,
PH.AUTHORIZATION_STATUS STATUS,
PH.CREATION_DATE po_date
from po_headers_all ph,
po_lines_all pol,
po_distributions_all pds,
HR_OPERATING_UNITS hr,
po_vendors pv
where ph.PO_HEADER_ID=pol.PO_HEADER_ID
and ph.PO_HEADER_ID=pds.PO_HEADER_ID
and ph.VENDOR_ID=pv.VENDOR_ID
and hr.ORGANIZATION_ID=ph.ORG_ID
AND PdS.QUANTITY_ORDERED<>PdS.QUANTITY_DELIVERED
AND ph.TYPE_LOOKUP_CODE<>'RFQ'
AND ph.TYPE_LOOKUP_CODE<>'QUOTATION'
AND PH.AUTHORIZATION_STATUS='APPROVED'
and hr.NAME=:org
and trunc(ph.creation_date)=trunc(:dt)
and to_char(ph.creation_date,initcap('mon')||'-'||'yy')=:mon
and trunc(ph.creation_date)  between trunc(:inp) - 7   and trunc(:inp)


Ur's
AmarAlam

Puchasing and Payables Module: Technical Details

PO_VENDORS
This table stores information about your suppliers. Oracle Purchasing uses this information to determine active suppliers. The primary key is VENDOR_ID.

PO_VENDOR_SITES_ALL
This table stores information about supplier sites. Oracle Purchasing uses this information to store supplier address information. The primary key is VENDOR_SITE_ID.

PO_VENDOR_CONTACTS
This table stores information about supplier site contacts. The primary key is VENDOR_CONTACT_ID.

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. Each row contains the requisition number, preparer status, and description. It is one of three tables that stores requisition information. The primary key is REQUISITION_HEADER_ID.

PO_REQUISITION_LINES_ALL
This table stores information about requisition lines. Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. This table is one of three tables that stores requisition information. The primary key is REQUISITION_LINE_ID.

PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution. Each row includes the Accounting Flex field ID and requisition line quantity. This table is one of three tables that stores requisition information. The primary key is DISTRIBUTION_ID.

PO_HEADERS_ALL
PO_HEADERS_ALL contains information for your purchasing documents. Each row contains buyer information, supplier information, notes, foreign currency information, terms and conditions information, and the document status. Oracle Purchasing uses this information to record information related to a complete document. The primary key is PO-HEADER_ID.

PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. Each row includes the line number, item number and category unit, price, tax information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. The primary key is PO_LINE_ID.

PO_LINE_LOCATIONS_ALL
This table contains information about purchase order shipment schedules and blanket agreement price breaks. You must have one row for each schedule or price break you attach to a document line. Each row contains the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders and price break information for blanket purchase orders, quotations, and RFQs. The primary key is LINE_LOCATION_ID.

PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL contains accounting information for a purchase order shipment line. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. It is one of five tables that stores purchase orders and releases. The primary key is PO_DISTRIBUTION_ID.

RCV_SHIPMENT_HEADERS
This table stores common information about the source of your receipts or expected receipts. You group your receipts by the source type and the source of the receipt. Oracle Purchasing does not allow you to group receipts from different sources under one receipt header. The primary key is SHIPMENT_HEADER_ID.

RCV_SHIPMENT_LINES
This table stores information about items that have been shipped or received from a specific receipt source. This table also stores information about the default destination for in-transit shipments. The primary key is SHIPMENT_LINE_ID.

RCV_TRANSACTIONS
This table stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table.
Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing. The primary key is TRANSACTION_ID.

PO_RELEASES_ALL
This table contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment (PO_LINE_LOCATIONS_ALL). The primary key is PO_RELEASE_ID.

AP_INVOICES_ALL
This table contains records for supplier invoices and has one row for each invoice.
To pass Invoice Validation, the INVOICE_AMOUNT in the AP_INVOICES_ALL table must equal the sum of the AMOUNT columns in the AP_DISTRIBUTIONS_ALL table. The primary key is INVOICE_ID.

AP_INVOICE_DISTRIBUTIONS_ALL
This table holds the distribution line information.
There is a row for each invoice distribution. If matching is used, distribution information is copied from PO_DISTRIBUTIONS_ALL during the matching process.

AP_PAYMENT_SCHEDULES_ALL
This table contains information about scheduled payments for an invoice.
Oracle Payables uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch.

AP_HOLDS_ALL
This table contains a record for each hold placed on an invoice.
An invoice might have multiple records in this table.
An invoice cannot be paid until all holds placed on it have been released.

AP_CHECKS_ALL
This table stores information about payments issued to suppliers.
Each row includes the supplier name, address, and bank account name for auditing purposes, in case any of them change after you make the payment.
If the payment is electronic, the supplier bank account information is stored in the payment record.

AP_INVOICE_PAYMENTS_ALL
This table provides the link between a payment (CHECK_ID) and the invoice(s) paid by that payment.
This table contains records of invoice payments that you make to suppliers. Oracle Payables updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick Payment. This table contains one row for each payment made for each invoice.
Void payments are also entered into this table as a negative record of the original payment line.

AP_BANK_ACCOUNTS_ALL
This table stores information about your bank accounts and your suppliers’ and customers’ bank accounts. This table contains one row for every bank account, whether it is an internal (supplier) or external (customer) account.

AP_BANK_BRANCHES
This table stores information about the bank branches and details about the branches.



Ur's
Amar Alam

Creating Purchase Order Requisitions

Below is the example to create a requisition.

We just need to insert data into Interface table and then we need to call standard oracle import program.

In below example, i have created a Internal Requisition.

************************************************************************************************
DECLARE
   l_request_id   NUMBER;
BEGIN
   INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (interface_source_code,
                                              source_type_code,
                                              requisition_type,
                                              destination_type_code,
                                              item_id,
                                              item_description,
                                              quantity,
                                              authorization_status,
                                              preparer_id,
                                              autosource_flag,
                                              uom_code,
                                              destination_organization_id,
                                              deliver_to_location_id,
                                              deliver_to_requestor_id,
                                              need_by_date,
                                              gl_date,
                                              charge_account_id,
                                              org_id,
                                              suggested_vendor_id,
                                              suggested_vendor_site_id,
                                              unit_price,
                                              creation_date,
                                              created_by,
                                              last_update_date,
                                              last_updated_by,
                                              batch_id
                                              )
     VALUES   ('INV',
               'INVENTORY',
               'INTERNAL',
               'INVENTORY',
               1831768,
               'leadsets',
               50,
               'APPROVED',
               48880,
               'P',
               'EA',
               2609,
               75018,
               48880,
               SYSDATE,
               SYSDATE,
               237125,
               2592,
               1058,
               607,
               150,
               SYSDATE,
               59944,
               SYSDATE,
               59944,
               454
               );

   COMMIT;

   l_request_id :=
      fnd_request.submit_request (application   => 'PO',
                                  program       => 'REQIMPORT',
                                  argument1     => 'INV',
                                  argument2     => '454',
                                  argument3     => 'ALL',
                                  argument4     => '',
                                  argument5     => '',
                                  argument6     => 'N');
   COMMIT;
   DBMS_OUTPUT.put_line ('request_id - ' || l_request_id);
END;
/


Ur's
AmarAlam