Oracle Fusion Procure to Pay (P2P) Cycle with tables and query

Procure to Pay

 Oracle Procure to Pay - Cycle with tables and details are as below:

1. Create Requisition 
2. Create Purchase Order
3. Create Receipts
4. Create Invoice
5. Invoice Payment to Supplier with Electronic Method
6. Verify the Transaction Life Cycle
7. Transfer Payables data to GL
8. Transfer Payments data to GL
9. Verify the Journal Batches
   

1. Requisition:

A requisition is a request for the goods or services. If approved, a purchase order is created to fulfill the requisition.

We can raise requisition from the Catalog or from the Non Catalog items as well, for this example lets consider NonCatalog item.

 Navigation:

 

Once you open this page you will see the below screen

 

Click on More Tasks > Click on Request Noncatalog Item.

  

Enter the details in each box as per your requirment

 

Click on Add to Cart.

Once the Item is added to the Cart click on the Cart Symbol

 

 Click on Submit. This will Create the Requisition and you can see it in the UI as below:

Click on Ok to procced furhter or View PDF to view the Requisition in PDF format

Requisition will undergo approval if there is any approval hierarchy is set, else it will get auto approved based on the configuration.

NOTE: Based on the configuration thess Requisitons are Auto Create in the Purchase order. Else this have to be manually transferred to the Purcahse Order Module.

To do this you need to run the Standard Scheduled Process:

"Generate Orders" 

This will Moves approved requisition for processing into your procurement business unit. The process will also create purchase orders for those requisition lines requesting products or services set up for touchless buying.

You tube Link on how to create Purchase Requisition

https://www.youtube.com/watch?v=PnCfpXd1HeQ&feature=youtu.be

The tables that effect requisition are 

1. POR_REQUISITION_HEADERS_ALL
2. POR_REQUISITION_LINES_ALL
3. PO_LINE_LOCATIONS_ALL
4. POR_REQ_DISTRIBUTIONS_ALL

Sql Query to find the requisition details are as below:

SELECT
PRHA.APPROVED_DATE AS APPROVED_ON_DATE
,PRLA.QUANTITY_cANCELLED AS CANCELLED_LINE_QTY
,PRLA.REQ_BU_ID AS BU_KEY
,PRLA.DESTINATION_ORGANIZATION_ID AS ORG_ID
,NVL(TO_CHAR(PRLA.DELIVER_TO_CUST_ID),'0') AS DELIVER_TO_CUST_ID
,PRLA.DESTINATION_TYPE_CODE AS DESTINATION_TYPE_cODE
,NVL(PRLA.SUGGESTED_SUPPLIER_ITEM_NUMBER,0) AS SUGGESTED_SUPPLIER_ITEM_NUM
,PRLA.LINE_STATUS
,(CASE
    WHEN PRLA.MATCHING_BASIS = 'AMOUNT' THEN PRLA.AMOUNT
    ELSE PRLA.QUANTITY * PRLA.UNIT_PRICE
 END) AS LINE_AMOUNT
,PRLA.REQUESTED_SHIP_DATE
,PRLA.LAST_APPROVAL_DATE
,PRLA.ITEM_DESCRIPTION AS LINE_TEXT
,PRLA.UOM_CODE AS PRIMARY_UOM_CODE
,PRHA.REQUISITION_NUMBER
,PRLA.URGENT_FLAG
,PRLA.QUANTITY AS ORDERED_QTY
,(PRLA.UNIT_PRICE * PRLA.QUANTITY) AS AMOUNT_ORDERED
,FND.USERNAME AS LINE_LAST_UPDATED_BY
,FND_C.USERNAME AS LINE_CREATED_BY
FROM
POR_REQUISITION_HEADERS_ALL PRHA
,POR_REQUISITION_LINES_ALL PRLA
,PO_LINE_LOCATIONS_ALL POLLA
,GL_CODE_COMBINATIONS GLCC
,POR_REQ_DISTRIBUTIONS_ALL PDA
,PER_USERS FND
,PER_USERS FND_C
,POZ_SUPPLIERS POV
WHERE 1=1
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID (+)
AND PRLA.REQUISITION_LINE_ID = PDA.REQUISITION_LINE_ID
AND PDA.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND TO_CHAR(FND.USER_ID (+)) = PRLA.LAST_UPDATED_BY
AND TO_CHAR(FND_C.USER_ID (+)) = PRLA.CREATED_BY
AND POV.VENDOR_ID (+) = PRLA.VENDOR_ID
AND PRHA.REQUISITION_NUMBER = 'GUS712'

2. Purchase Order

Post Successful Creation of Purchase Requisition, we can enable the option to Auto Create the Purchase Order or we can manually create Purchase Order by running "Generate Orders" Scheduled process.

For Auto Create Purchase Orders we need to have set ups configured please refer my other links for further details.

 

Navigate to Purchase Orders.

Click on Process Requisition on the Task Pane as shown in the below screen

 

 

 

Query the Purchase Requisition Number, Select the line Click on Add to Document Builder.

 

 

Then Click on Ok as shown in the below screen shot.

 

Click on Create as shown in the below screen shot.

This will Create a Purchase Order in Incomplete Status. Then Click on Actions Validate to validate the Purchase Order.

Click on Manage Approvals, Click on Submit in the new window.

This will Create the Purchase Order.

You Tube link on how to create Purchase Order

https://www.youtube.com/watch?v=jCUEBjNi86k

The tables that effect Purchase Orders are as below:

1. PO_HEADERS_ALL
2. PO_LINES_ALL
3. PO_DISTRIBUTIONS_ALL
4. PO_LINE_LOCATIONS_ALL

Sql Query to fetch the Purchase Order details is as below:

SELECT
to_char(prla.requisition_line_id) as purch_req_key
,prha.requisition_number as purch_requisition_num
,prla.line_number as purch_line_num
,pla.uom_code as standard_uom_code
,prla.quantity as ordered_qty
,pdar.quantity_delivered
,(prla.unit_price * prla.quantity ) as amount_ordered
,(prla.unit_price * pdar.quanity_delivered) as amount_delivered
,to_char(pha.segment1) as PO_NUM
,to_char(pla.line_num) as PO_LINE_NUM
from por_Requisition_headers_all prha
,por_requisition_lines_All prla
,po_line_locations_all polla
,po_headers_All pha
,po_lines_all pla
,po_distributions_all pdar
,gl_code_combinations glcc
,por_req_distributions_all pda
,per_users fnd
,per_users fnd_c
,poz_suppliers pov
where prla.requisition_header_id = prha.requisition_header_id (+)
and prla.requisition_line_id (+) = pda.requisition_line_id
and pla.po_header_id = pha.po_header_id (+)
and pdar.po_header_id = pha.po_header_id (+)
and pdar.line_location_id = polla.line_locaation_id (+)
and polla.po_line_id (+) = pla.po_line_id
and polla.po_header_id (+) = pha.po_header_id
and pda.code_combination_od = glc..code_combination_id
and pdar.req_distribution_id (+) = pda.distribution_id
and to_char( fnd.user_id (+)) = prla.last_updated_by
and to_char (fnd_c.user_id (+)) = prla.created_by
and pov.vendor_id (+) = prla.vendor_id
order by prda.po_distribution_id

3. Receive Goods

Login to the application, Enter the Requisition Number and Purchase Order. Click on Search.

Select the line and click on Receive button

Enter the Received quantity and click on Submit. This will generate the Receipt Number.

The tables that effect Receipts are as below:

1. RCV_TRANSACTIONS - This has Return Supplier goods details check for the column Transaction Type.
   
2. RCV_SHIPMENT_HEADERS - Contains Receipt Header Details
   
3. RCV_SHIPMENT_LINES  - Contains Receipt Line details including quantities

 Sql Query to fetch the Receipts details is as below:

 

SELECT
rcvl.shipment_line_od
,poz.segment1 vendor_number
,decode(rcvh.vendor_site_id,NULL,NULL,site.vendor_site_code) vendor_site
,decode(rcvl.po_header_id,NULL,'Unordered','Ordered') receipt_type
,rcvh.receipt_num
,rcvh.creation_Date as receipt_Date
,rcvl.ship_to_location_id
,hrl.location_code||'-'||hrl.description as ship_to_location
,rcvh.ship_to_org_id
,rcvh.employee_id
,rcvl.quantity_shipped
,rcvl.quantity_received
,rcvl.item_description
,poh.segment1 as po_num
,poh.creation_Date as po_date
,rcvh.comments as header_note
,pol.unit_price
,poh.currency_code
,poll.promised_Date
,DECODE(rcvl.routing_header_id , 2, 'Yes' , 'No')
from rcv_shipment_headers  rcvh
,rcv_shipment_lines rcvl
,poz_suppliers poz
,poz_supplier_sites_all_m sites
,hr_locations_all hrl
,per_all_people_d ppf
,po_headers_all poh
,po_lines_All pol
,po_line_locations_all poll
where rcvh.shipment_header_id = rcvl.shipment_header_id
and rcvh.vendor_id = poh.vendor_id
and poh.vendor_id = site.vendor_id
and rcvh.vendor_site_id = site.vendor_site_id
and rcvl.ship_to_location_id = hrl.location_id
and rcvh.employee_id = ppf.person_id (+)
and rcvl.po_header_id = poh.po_header_id (+)
and rcvl.po_line_id = pol.po_line_id (+)
and rcvl.po_line_location_id = poll.line_location_id (+) 

4. Create Payable Invoice

Once we have received the goods to the inventory, its time we have to raise the invoice in the system for the received goods.

 

Navigate: Payables > Invoices

 

 

 

Click on Create Invoice Under the Task

 

 

 

Query the PO Number and Invoice lines will get defaulted from the PO lines

 

Click on Validate and then send for Approval.

 

Approve Invoice

 

 The tables that effect Invoices are as below:

1. AP_INVOICES_ALL
2. AP_INVOICE_LINES_ALL
3. AP_INVOICE_DISTRIBUTIONS_ALL
4. AP_TERMS_TL
5. AP_BATCHES_ALL
6. IBY_PAYMENT_METHODS_TL

  Sql Query to fetch the Invocie details is as below:

 
select
aia.set_of_books_id as ledger_id
,aia.legal_entity_id as legal_entity_id
,aia.org_id as bu_id
,aia.vendor_id supplier_id
,aia.vendor_site_id as supplier_site_id
,to_number(to_char(aia.invoice_date,'YYYYMMDD')) as invoice_Date
,aia.accts_pay_code_combination_id as glcc_liab_id
,dist.dist_Code_combination_id as glcc_expense_id
,aia.payment_method_code
,pm.payment_method_name
,aia.terms_id
,apt.name as terms_name
,aia.invoice_num
from ap_invoices_all aia
,iby_payment_methods_tl pm
,ap_terms_tl apt
,ap_invoice_lines_All aila
,ap_batches_all bat
,ap_invoice_distributions_all dist
,ap_lookup_code alc
,ap_lookup_code alc1
,ap_lookup_code alc2
,ap_lookup_code alc3
,po_distributions_all pda
,po_headers_all pha
where 1=1
and aia.payment_method_code = pm.payment_method_code(+)
and NVL(pm.language,'US') = 'US'
and aia.term_id = apt.term_id (+)
and NVL(apt.language,'US') = 'US'
and aia.invoice_id = aila.invoice_id
and aia.batch_id = bat.batch_id (+)
and aila.invoice_id = dist.invoice_id (+)
and aila.line_number = dist.invoice_line_number (+)
and alc.lookup_type = 'INVOICE TYPE'
and alc.lookup_code = aia.invoice_type_lookup_code
and alc1.lookup_type (+) = 'POSTING_STATUS'
and alc1.lookup_code (+) = dist.posted_flag
and alc2.lookup_type (+) = 'INVOICE DISTRIBTUION TYPE'
and alc2.lookup_code (+) = dist.line_tyoe_lookup_code
and alc3.lookup_type (+) = 'INVOICE LINE TYPE'
and alc3.lookup_code (+) = aila.line_type_lookup_code
and dist.po_distribution_id = pda.po_distribution_id (+)
and pda.po_header_id = poh.po_header_id (+)

5. Invoice Payment to Supplier with Electronic Method

 Payables > Payments

 

Submit Payment Process Request link from the Payments Task

 

Enter the PPR Name and select the payment method, Supplier, Bank, Payment process profile and Approver and click on Submit button.

 

Verify the Payment and click on Submit Button.

 

Click on the Resume Payment Process button.

 

 Once the payment is created it will ask for the approval, click on the Go Action Button to initiate the approval.

 

Click on Save button.

 

Approve Payment.

 The tables that effect Invoices are as below:

 

1. AP_CHECKS_ALL
2. AP_INVOICE_PAYEMNTS_ALL
3. AP_PAYMENT_SCHEDULES_ALL
4. CE_PAYMENT_DOCUMENTS
5. IBY_PAYMENTS_ALL

  Sql Query to fetch the Payment details is as below:

 

SELECT
bu.primary_ledger_id as ledger_id
,ipa.org_id as bu_id
,ipa.legal_entity_id as legal_entity_id
,checks.vendor_id as supplier_id
,aia.invoice_num as invoice_num
,aia.invoice_Date as invoice_date
,Checks.check_voucher_num
,checks.check_number
,aia.invoice_currency_code
,ipa.payee_name as payee_name
,ipa.payment_amount as payment_amount
,ipa.payee_supplier_site_name as payee_site_name
,checks.iban_number as iban
,ipa.ext_bank_name as remit_to_bank_name
,ipa.ext_branch_number as ext_branch_number
,ipa.payment_profile_acct_name as payment_process_profile
,ipa.int_bank_account_name as disbursement_bank_account
,ipa.int_bank_name as int_bank_name
,ipa.int_branch_name as int_bank_branch_name
,ipa.int_branch_number as int_bank_branch_number
,ipa.int_bank_account_number as int_bank_account_number
from ap_checks_All checks
,ap_invoice_payments_all aipa
,fun_all_business_units_v bu
,ap_invoices_all aia
,ap_payment_schedules_all ps
,ap_lookup_codes aplc
.ce_payment_documents cpd
,iby_payments_All ipa
,fnd_lookup_values_tl flvt
where 1=1
and aipa.check_id = checks.check_id
and ipa.org_id = bu.bu_id
and aipa.invoice_id = aia.invoice_id
and aipa.invoice_id = ps.invoice_id
and aipa.payment_num = ps.payment_num
and aplc.lookup_type (+) = 'CHECK STATE'
and aplc.lookup_code (+) = checks.status_lookup_code
and checks.payment_document_id = cpd.payment_document_id (+)
and checks.payment_id = ipa.payment_id (+)
and ipa.payment_function = flvt.lookup_code (+)
and flvt.lookup_type (+) = 'IBY_PAYMENT_FUNCTIONS'
and flvt.source_lang (+) = 'US' 

6.Verify the Transaction Life Cycle

 Procurement > Purchase Requisitions

 

Search with requisition number, click on the requisition number

 

Click on View Life Cycle Button

 

Click on Purchase Order > On Side screen click on View Details.

 

 

7.Transfer Payables data to General Ledger

Payables > Search for the invoice number in Manage Invoice Task

Click on the Invoice Actions and click on to Post to Ledger Button.

Invoice Accounting Status will get changed to Accounted.

8.Transfer Payments data to General Ledger

Payments > Click on Manage Payments link

 

Search with PPR name and click on Payment number

 

Click on Actions and post to Journal Button

 

Verify the Post Journal Report

 

9.Verify the Journal Batches 

 General Accounting > Journal

 

Click on Manage Journal  task

Search with Journal Batch Name

Click on the Payments Batch

 

Tables that affect the Journal Batches are as below:

 

GL_JE_LINES
GL_JE_HEADERS
GL_JE_BATCHES

GL_LEDGERS

GL_CODE_COMBINATIONS

GL_PERIODS

GL_TRANSLATION_RATES

Sql query to fetch the Journal details are as below:

select
COALESCE(l.ledger_id,0) as ledger_id
,COALESCE(TO_NUMBER(TO_CHAR(l.effective_Date,'YYYYMMDD')),0) as JOURNAL_DT_ID
,COALESCE(TO_NUMBER(TO_CHAR(h.posted_Date,'YYYYMMDD')),0) as POSTED_DT_ID
,l.status "JOURNAL LINE STATUS"
,b.name "JE_BATCH_NAME"
,b.status "JE_BATCH_STATUS"
,b.desccription "JE_BATCH_DESC"
,b.je_source "JE_BATCH_SOURCE"
,b.posted_DAte
,l.je_header_id "JE_HEADER_ID"
,l.je_line_num "JE_LINE_NUM"
,h.name as "JE_HEADER_NAME"
,h.description "JE_HEADER_DESCCRIPTION"
from gl_je_headers h
,gl_je_lines l
,gl_je_batches b
,gl_ledgers gl
,per_users fu
,gl_code_combinations gcc
,gl_periods gp
,per_users ful
,gl_translation_rates gtr
where 1=1
and l.je_header_id = h.je_header_id
and h.je_batch_id = b.je_batch_id
and gl.ledger_id = h.ledger_id
and gcc.code_combination_id = l.code_Combination_id
and l.period_name = gp.period_name
and gp.period_set_name = :period_set_name
and l.last_updated_by = ful.username (+)
and fu.active_flag (+) = 'Y'
and ful.active_flag (+) = 'Y'
and gtr.set_of_books_id (+) = gl.ledger_id
and gtr.period_name (+) = gp.period_name
and gtr.to_currency_Cide (+) = 'USD'
and l.last_update_Date >= :last_run_Date