Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Sunday, January 8, 2017

P2P - Oracle Procure To Pay (P2P) Life Cycle Functional and Technical Process Flow

The Procure To Pay cycle is vital to an organization as in this process the organization buys and received goods or services from its vendors and makes necessary payments.

The P2P cycle comprises of the following steps,
1.         Create requisition
2.         Approve requisition
3.         Create purchase order
4.         Approve purchase order
5.         Receive goods
6.         Create Payables invoice
7.         Pay the invoice
8.         Return to Vendor (optional)
9.         Transfer to General Ledger
10.       Import Journal into General Ledger

Step 1: Create requisition
Responsibility: Any Purchasing responsibility
Navigation: Requisition > Requisition Summary

Click on New button

Create a new Purchase Requisition.
Enter the requisition details and save the form.

Step 2: Approve the requisition
After the requisition is saved the Approve button is enabled.
Click on the Approve button to send the requisition for approval.

Click on OK button and the requisition will be sent for approval.

Now go into Workflow Administrator responsibility to approve the workflow.

Requisition is approved by default if not we need to approve manually

After the requisition is approved the status of the requisition will change to Approved.

Below the tables are effected
SELECT *
FROM PO_REQUISITION_HEADERS_ALL
WHERE 1=1
AND SEGMENT1='14308'
AND REQUISITION_HEADER_ID = 183202
ORDER BY CREATION_DATE DESC

SELECT * FROM PO_REQUISITION_LINES_ALL
WHERE 1=1
AND REQUISITION_HEADER_ID = 183202
AND REQUISITION_LINE_ID = 210409

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE 1=1
AND REQUISITION_LINE_ID = 210409

Step 3: Create the Purchase Order from the requisition
When the Requisition is approved we need to create a Purchase Order for it. We shall use the AutoCreate feature of Purchasing to do the conversion from Requisition to Purchase Order.

Responsibility: Purchasing responsibility
Navigation: AutoCreate

Enter the requisition number, 14308, and click on Find.

You might get the below message. For this one we can remove the Buyer name and create the po or assign the buyer to requisition

Navigation: Management > Manage Buyer Workload

Enter the requisition number, 14308, and click on Find button.

Now assign a Buyer to the requisition so that this requisition can be picked up by him to create the Purchase order.

We shall assign a new buyer, Stock, Ms. Pat, and select the requisition.

Save the form.
You will get a confirmation message.

Now you need to log into oracle as the Stock, Ms. Pat (i.e. the buyer who was set on the requisition). After logging in to Oracle, go to a Purchasing responsibility.

Open the AutoCreate form.

Enter the requisition number, 14308, and click on Find button. The AutoCreate
Documents form opens with the requisition details.

Select the requisition. We shall take the default values,
Action: Create
Document Type: Standard PO
Grouping: Default

Click on Automatic button.

Click on Create button to create the PO. The PO form opens with the new PO details that has been created from the requisition.

The PO number is 6036.

Step 4: Approve the Purchase Order
Enter the Supplier and the supplier site on this PO.
Save the PO. Click on the Shipments button.

The Shipments form opens. We find the shipping org is set to V1. This means that the goods will be received in the V1 org when it is shipped by the supplier (Step 5).

Close the Shipment form to go back to the PO form. Click on Approve button to send the PO for approval.

The Approve Document form opens. Click on OK button to send the PO for approval.

Once the PO is approved the status changes to Approved, Reserved.

Below the tables are effected
--PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =<po_number>;
select * from po_headers_all where po_header_id =<po_header_id>;

--po_lines_all
select * from po_lines_all where po_header_id =<po_header_id>;

--po_line_locations_all
select * from po_line_locations_all where po_header_id =<po_header_id>;

--po_distributions_all
select * from po_distributions_all where po_header_id =<po_header_id>;

--po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;


SELECT * FROM PO_HEADERS_ALL
WHERE 1=1
AND SEGMENT1 = '6036'
AND PO_HEADER_ID = 119352

SELECT * FROM PO_LINES_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352
AND PO_LINE_ID = 182280

SELECT * FROM PO_DISTRIBUTIONS_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352

SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352

Step 5: Receive the goods/items
Once the PO is sent to the supplier to be fulfilled the supplier sends the goods to the organization. The goods now have to be received in Oracle.

To receive goods go to any Purchasing responsibility that has the receiving functionality.
Navigation: Receiving > Receipts

The Organization screen opens. Select V1 (As checked in PO shipment in Step 4) and click on OK.

Enter the PO number and the supplier name is populated automatically.

Click on Find button. The receipt header form opens.

Close this form and the Receipts form opens.

Select the Line. You can change the quantity received at this section..
Save the form.

To get the receipt number, click on the Header button.

You now have the receipt number, 8458.

Close all the forms

Step 6: Inspect the goods
Now that we have received the goods based on the PO (even though the shipment was partial) we need to have the goods inspected and QA check done (This is required if the PO is based on 4 way matching, else the payment cannot be made).

Responsibility: Purchasing responsibility that has the Receiving Transactions or QA functionality enabled

Navigation: Receiving > Receiving Transactions

Enter the Purchase Order Number, i.e. 6036.

The supplier number is populated automatically. Now click on Find button.

Note that the Inspection status shows as Not Inspected – Standard Receipt and the Destination Type is Inventory.

Click on Inspect button. The inspection window opens.

We are going to pass all 5 goods as part of the QA check process. We shall select Quality Code from the list of values as Excellent.

Enter a comment on the right most field.

Press OK button. The window will close and the receipt status will change to Inspect. Requery the receiving transaction on the PO.

Note that the Inspection field has the value, Accepted – Standard Receipt. QA check has been completed. Close the form.

Below are the tables effected
Once PO is received data is moved to respective receiving tables and inventory tables

--RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);

--RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =<po_header_id>;

--RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =<po_header_id>;

--RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

--RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

--RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

--MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

--MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = <po_header_id>);

--à>>>>----------------------------
--RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers
where 1=1
AND SHIPMENT_HEADER_ID = 4815921

--RCV_SHIPMENT_LINES
select * from rcv_shipment_lines
where 1=1
AND po_header_id = 119352
AND SHIPMENT_HEADER_ID = 4815921
and SHIPMENT_LINE_ID = 4805482

--RCV_TRANSACTIONS
select * from rcv_transactions
where 1=1
AND po_header_id = 119352
and transaction_id in (4895409 ,4895410)

--RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_details
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions
where 1=1
and transaction_source_id = 119352

--MTL_TRANSACTION_ACCOUNTS
SELECT *
FROM mtl_transaction_accounts
WHERE transaction_id IN (SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_id = 119352);

Step 7: Create Payables invoice
Responsibility: Payables responsibility to create an AP invoice

Navigation: Invoice > Entry > Invoices

Create an Invoice for supplier. This is the supplier which had received the PO (Refer to Step 3 or 4).

Note:
We are creating the invoice manually in this case. The invoice can be created automatically by executing the concurrent program named, Pay on Receipt AutoInvoice, from the Purchasing responsibility and entering the goods receipt number (GRN) as the parameter.

Click on Match button at the bottom right of the form.


Important:
We shall not enter the invoice distribution as this will come automatically from matching the invoice with PO receipt.

There are 3 options for matching.
•          Invoice
•          Receipt
•          Purchase Order

We are matching Receipt to this invoice so that the supplier is paid for only the amount that has been received in the store.

Matching is done in 3 ways
2-way  Ã PO and Invoice quantity and total amounts are matched
3-way  Ã PO, Invoice, Receipt quantity and total amounts are matched
4-way  Ã PO, Invoice, Receipt, QA (Acceptance) quantity and total amounts are matched

Find Receipts form opens.

Enter the Receipt number (End of Step 5)
Click on Find button. Match to Receipts form opens.

Check the box under Match.

Notice that the Distribution Total is in Red. It means that the Invoice header amount and distribution amounts are not matching. Click on Match button at the bottom

You will be taken back to the Invoice form. Modify the Invoice header amount to match the amount of the distribution.

Now you can validate the Invoice by clicking on Actions button on the Invoice form and selecting Validate.

Click on OK button.

Now the invoice status changes to Validated.

Close the invoice form.

Step 8: Pay the invoice
Responsibility: Payables responsibility with payment functionality Navigation: Payments > Entry > Payments

Enter the bank and supplier details

Click on Enter/Adjust Invoices button to enter the invoice

Save and close this window.

Click on Actions button. Select Format and Print Remittance Advice.

Click on OK. The Payment Process Manager will handle the payment and will execute the remittance program as well to make the payment.

After the processes are over the supplier is paid fully for the goods that he has supplied.

Below are the tables effected
--AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);

--AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));


select * from ap_invoice_distributions_all
where 1=1
AND po_distribution_id = 279894

select * from ap_invoices_all
where 1=1
AND invoice_id in (218359)


Step 9: Receive the remaining goods or Close the Invoice line
Receive the remaining goods
For example if we select the some of goods like 10 and in that we did the transaction for only 5 goods
The supplier has been paid for the goods that have been received in the stores but we have received 5 goods out of the total number of 10. Therefore we have the option of receiving 5 more to complete the PO. If we receive the other 5 goods then we have to create another invoice to pay off those received goods.

If we do not want to receive the remaining goods but instead we want to close the PO at this stage then we need to Close the PO line so that the remaining quantity is cancelled and the PO is closed.

Note:
If we do not finish this step the PO will remain in open state and the amount will go into encumbrance, i.e. the open amount will be carried over to the next periods as the amount which will need to be paid to the supplier.

Responsibility: Purchasing responsibility

Navigation: Purchase Orders > Purchase Order Summary

Query for the PO, i.e. 6036.

Click on Find.

Click on Lines button.

On the top menu click on Tools > Control. You will get a popup message.

Click on OK.

The Control Documents window opens. Select Finally Close and enter a reason. Click on OK button.

Click on OK button. Now requery the PO.

The PO is now Closed. Note the PO status, Approved, Closed, Reserved.

Step 10: Transfer Payables data to General Ledger
Responsibility: Payables

Navigation: Invoices Ã query with invoice and click on Actions and check the create accounting and select the final posting to GL
Check the GL batch name from vew requests






P2P Query

SELECT
  reqh.segment1 req_num,
  reqh.authorization_status req_status,
  -- poh.po_header_id,
  poh.segment1 po_num,
  pol.line_num,
  poh.authorization_status po_status,
  --       i.invoice_id,
  i.invoice_num,
  i.invoice_amount,
  i.amount_paid,
  i.vendor_id,
  -- v.vendor_name,
  -- p.check_id,
  c.check_number,
  h.gl_transfer_flag,
  h.period_name
FROM
  ap_invoices_all i,
  ap_invoice_distributions_all invd,
  po_headers_all poh,
  po_lines_all pol,
  po_distributions_all pod,
  po_vendors v,
  po_requisition_headers_all reqh,
  po_requisition_lines_all reql,
  po_req_distributions_all reqd,
  ap_invoice_payments_all p,
  ap_checks_all c,
  ap_ae_headers_all h,
  ap_ae_lines_all l
WHERE 1 = 1
  AND i.vendor_id = v.vendor_id
  AND c.check_id = p.check_id
  AND p.invoice_id = i.invoice_id
  AND poh.po_header_id = pol.po_header_id
  AND reqh.requisition_header_id = reql.requisition_header_id
  AND reqd.requisition_line_id = reql.requisition_line_id
  AND pod.req_distribution_id = reqd.distribution_id
  AND pod.po_header_id = poh.po_header_id
  AND pod.po_distribution_id = invd.po_distribution_id
  AND invd.invoice_id = i.invoice_id
  AND h.ae_header_id = l.ae_header_id
  AND l.source_table = 'AP_INVOICES'
  AND l.source_id = i.invoice_id
  --and poh.segment1 = 67859 -- PO NUMBER
  AND reqh.segment1 = '80988'

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect