P2P Queries

***********************************************************************************************************************************************************************************************************************

1. Requisition and PO

-----Relation with Requistion and PO

select r.segment1 "Req Num",

p.segment1 "PO Num"

from po_headers_all p,

po_distributions_all d,

po_req_distributions_all rd,

po_requisition_lines_all rl,

po_requisition_headers_all r

where p.po_header_id = d.po_header_id

and d.req_distribution_id = rd.distribution_id

and rd.requisition_line_id = rl.requisition_line_id

and rl.requisition_header_id = r.requisition_header_id

***********************************************************************************************************************************************************************************************************************

2. Internal Requisitions without Sales order

---used to list all Internal Requisitions that do not have an associated Internal Sales order

Select RQH.SEGMENT1 REQ_NUM,

RQL.LINE_NUM,

RQL.REQUISITION_HEADER_ID ,

RQL.REQUISITION_LINE_ID,

RQL.ITEM_ID ,

RQL.UNIT_MEAS_LOOKUP_CODE ,

RQL.UNIT_PRICE ,

RQL.QUANTITY ,

RQL.QUANTITY_CANCELLED,

RQL.QUANTITY_DELIVERED ,

RQL.CANCEL_FLAG ,

RQL.SOURCE_TYPE_CODE ,

RQL.SOURCE_ORGANIZATION_ID ,

RQL.DESTINATION_ORGANIZATION_ID,

RQH.TRANSFERRED_TO_OE_FLAG

from

PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH

where

RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID

and RQL.SOURCE_TYPE_CODE = 'INVENTORY'

and RQL.SOURCE_ORGANIZATION_ID is not null

and not exists (select 'existing internal order'

from OE_ORDER_LINES_ALL LIN

where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID

and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)

ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

***********************************************************************************************************************************************************************************************************************

3. cancelled requisition

-----list My cancel Requistion

select prh.REQUISITION_HEADER_ID,

prh.PREPARER_ID ,

prh.SEGMENT1 "REQ NUM",

trunc(prh.CREATION_DATE),

prh.DESCRIPTION,

prh.NOTE_TO_AUTHORIZER

from apps.Po_Requisition_headers_all prh,

apps.po_action_history pah

where Action_code='CANCEL'

and pah.object_type_code='REQUISITION'

and pah.object_id=prh.REQUISITION_HEADER_ID

***********************************************************************************************************************************************************************************************************************

4. -----list all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO.

select

prh.segment1 "PR NUM",

trunc(prh.creation_date) "CREATED ON",

trunc(prl.creation_date) "Line Creation Date" ,

prl.line_num "Seq #",

msi.segment1 "Item Num",

prl.item_description "Description",

prl.quantity "Qty",

trunc(prl.need_by_date) "Required By",

ppf1.full_name "REQUESTOR",

ppf2.agent_name "BUYER"

from

po.po_requisition_headers_all prh,

po.po_requisition_lines_all prl,

apps.per_people_f ppf1,

(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,

po.po_req_distributions_all prd,

inv.mtl_system_items_b msi,

po.po_line_locations_all pll,

po.po_lines_all pl,

po.po_headers_all ph

WHERE

prh.requisition_header_id = prl.requisition_header_id

and prl.requisition_line_id = prd.requisition_line_id

and ppf1.person_id = prh.preparer_id

and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date

and ppf2.agent_id(+) = msi.buyer_id

and msi.inventory_item_id = prl.item_id

and msi.organization_id = prl.destination_organization_id

and pll.line_location_id(+) = prl.line_location_id

and pll.po_header_id = ph.po_header_id(+)

AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)

AND PRH.AUTHORIZATION_STATUS = 'APPROVED'

AND PLL.LINE_LOCATION_ID IS NULL

AND PRL.CLOSED_CODE IS NULL

AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'

ORDER BY 1,2

***********************************************************************************************************************************************************************************************************************

5. ----- List and all data entry from PR till PO

select distinct u.description "Requestor",

porh.segment1 as "Req Number",

trunc(porh.Creation_Date) "Created On",

pord.LAST_UPDATED_BY,

porh.Authorization_Status "Status",

porh.Description "Description",

poh.segment1 "PO Number",

trunc(poh.Creation_date) "PO Creation Date",

poh.AUTHORIZATION_STATUS "PO Status",

trunc(poh.Approved_Date) "Approved Date"

from apps.po_headers_all poh,

apps.po_distributions_all pod,

apps.po_req_distributions_all pord,

apps.po_requisition_lines_all porl,

apps.po_requisition_headers_all porh,

apps.fnd_user u

where porh.requisition_header_id = porl.requisition_header_id

and porl.requisition_line_id = pord.requisition_line_id

and pord.distribution_id = pod.req_distribution_id(+)

and pod.po_header_id = poh.po_header_id(+)

and porh.created_by = u.user_id

order by 2

***********************************************************************************************************************************************************************************************************************

6. -----list all Purchase Requisition without a Purchase Order that means a PR has not been autocreated to PO.

select

prh.segment1 "PR NUM",

trunc(prh.creation_date) "CREATED ON",

trunc(prl.creation_date) "Line Creation Date" ,

prl.line_num "Seq #",

msi.segment1 "Item Num",

prl.item_description "Description",

prl.quantity "Qty",

trunc(prl.need_by_date) "Required By",

ppf1.full_name "REQUESTOR",

ppf2.agent_name "BUYER"

from

po.po_requisition_headers_all prh,

po.po_requisition_lines_all prl,

apps.per_people_f ppf1,

(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,

po.po_req_distributions_all prd,

inv.mtl_system_items_b msi,

po.po_line_locations_all pll,

po.po_lines_all pl,

po.po_headers_all ph

WHERE

prh.requisition_header_id = prl.requisition_header_id

and prl.requisition_line_id = prd.requisition_line_id

and ppf1.person_id = prh.preparer_id

and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date

and ppf2.agent_id(+) = msi.buyer_id

and msi.inventory_item_id = prl.item_id

and msi.organization_id = prl.destination_organization_id

and pll.line_location_id(+) = prl.line_location_id

and pll.po_header_id = ph.po_header_id(+)

AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)

AND PRH.AUTHORIZATION_STATUS = 'APPROVED'

AND PLL.LINE_LOCATION_ID IS NULL

AND PRL.CLOSED_CODE IS NULL

AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'

ORDER BY 1,2

***********************************************************************************************************************************************************************************************************************

7. ----- List and PO With there approval , invoice and payment details

select

a.org_id "ORG ID",

E.SEGMENT1 "VENDOR NUM",

e.vendor_name "SUPPLIER NAME",

UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",

f.vendor_site_code "VENDOR SITE CODE",

f.ADDRESS_LINE1 "ADDRESS",

f.city "CITY",

f.country "COUNTRY",

to_char(trunc(d.CREATION_DATE)) "PO Date",

d.segment1 "PO NUM",

d.type_lookup_code "PO Type",

c.quantity_ordered "QTY ORDERED",

c.quantity_cancelled "QTY CANCELLED",

g.item_id "ITEM ID" ,

g.item_description "ITEM DESCRIPTION",

g.unit_price "UNIT PRICE",

(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",

(select

decode(ph.approved_FLAG, 'Y', 'Approved')

from po.po_headers_all ph

where ph.po_header_ID = d.po_header_id)"PO Approved?",

a.invoice_type_lookup_code "INVOICE TYPE",

a.invoice_amount "INVOICE AMOUNT",

to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",

a.invoice_num "INVOICE NUMBER",

(select

decode(x.MATCH_STATUS_FLAG, 'A', 'Approved')

from ap.ap_invoice_distributions_all x

where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?",

a.amount_paid,

h.amount,

h.check_id,

h.invoice_payment_id "Payment Id",

i.check_number "Cheque Number",

to_char(trunc(i.check_DATE)) "PAYMENT DATE"

FROM AP.AP_INVOICES_ALL A,

AP.AP_INVOICE_DISTRIBUTIONS_ALL B,

PO.PO_DISTRIBUTIONS_ALL C,

PO.PO_HEADERS_ALL D,

PO.PO_VENDORS E,

PO.PO_VENDOR_SITES_ALL F,

PO.PO_LINES_ALL G,

AP.AP_INVOICE_PAYMENTS_ALL H,

AP.AP_CHECKS_ALL I

where a.invoice_id = b.invoice_id

and b.po_distribution_id = c. po_distribution_id (+)

and c.po_header_id = d.po_header_id (+)

and e.vendor_id (+) = d.VENDOR_ID

and f.vendor_site_id (+) = d.vendor_site_id

and d.po_header_id = g.po_header_id

and c.po_line_id = g.po_line_id

and a.invoice_id = h.invoice_id

and h.check_id = i.check_id

and f.vendor_site_id = i.vendor_site_id

and c.PO_HEADER_ID is not null

and a.payment_status_flag = 'Y'

and d.type_lookup_code != 'BLANKET'

***********************************************************************************************************************************************************************************************************************

8. ---- List all open PO'S

select

h.segment1 "PO NUM",

h.authorization_status "STATUS",

l.line_num "SEQ NUM",

ll.line_location_id,

d.po_distribution_id ,

h.type_lookup_code "TYPE"

from

po.po_headers_all h,

po.po_lines_all l,

po.po_line_locations_all ll,

po.po_distributions_all d

where h.po_header_id = l.po_header_id

and ll.po_line_id = l.po_Line_id

and ll.line_location_id = d.line_location_id

and h.closed_date is null

and h.type_lookup_code not in ('QUOTATION') ;

and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.

***********************************************************************************************************************************************************************************************************************

9. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID

PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1

PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID

PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)

PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

***********************************************************************************************************************************************************************************************************************

10.You need to find table which hold PO Approval path...

These two table keeps the data:

PO_APPROVAL_LIST_HEADERS

PO_APPROVAL_LIST_LINES

***********************************************************************************************************************************************************************************************************************

11.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is

Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

REFERENCE_1- Source (PO or REQ)

REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or

po_requisition_headers_all.requisition_header_id)

REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or

po_req_distributions_all.distribution_id)

REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or

po_requisition_headers_all.segment1)

REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

Accrual Records from PO:

REFERENCE_1- Source (PO)

REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)

REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id

REFERENCE_4- Purchase Order number (from po_headers_all.segment1)

REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)

Take a note for Period end accruals, the REFERENCE_5 column is not used.

***********************************************************************************************************************************************************************************************************************

12. A standard Quotations one that you can tie back to a PO.

Navigate to RFQ -> Auto create -> enter a PO and reference it back.

***********************************************************************************************************************************************************************************************************************

13. I want to debug for a PO , where should I start.

Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.

Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =;

select * from po_headers_all where po_header_id =;

po_lines_all

select * from po_lines_all where po_header_id =;

po_line_locations_all

select * from po_line_locations_all where po_header_id =;

po_distributions_all

select * from po_distributions_all where po_header_id =;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received data is moved to respective receving 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 =);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =;

RCV_TRANSACTIONS

select * from rcv_transactions where 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 =);

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 =);

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 =);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =);

Stage 3: Invoicing details

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 =);

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 =));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in

( select to_char(transaction_id) from mtl_material_transactions

where transaction_source_id = );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('');

GL_INTERFACE

SELECT *

FROM GL_INTERFACE GLI

WHERE user_je_source_name ='Purchasing'

AND gl_sl_link_table ='RSL'

AND reference21='PO'

AND EXISTS

( SELECT 1

FROM rcv_receiving_sub_ledger RRSL

WHERE GLI.reference22 =RRSL.reference2

AND GLI.reference23 =RRSL.reference3

AND GLI.reference24 =RRSL.reference4

AND RRSL.rcv_transaction_id in

(select transaction_id from rcv_transactions

where po_header_id ));

GL_IMPORT_REFERENCES

SELECT *

FROM gl_import_references GLIR

WHERE reference_1='PO'

AND gl_sl_link_table ='RSL'

AND EXISTS

( SELECT 1

FROM rcv_receiving_sub_ledger RRSL

WHERE GLIR.reference_2 =RRSL.reference2

AND GLIR.reference_3 =RRSL.reference3

AND GLIR.reference_4 =RRSL.reference4

AND RRSL.rcv_transaction_id in

(select transaction_id from rcv_transactions

where po_header_id =))

To set ORG ID

begin

dbms_application_info.set_client_info(value);

end;

begin

fnd_client_info.set_org_context(value);

end;

Procure to Pay

Overview:

In this article, we will see the steps involved in Procure to Pay Cycle.

1. Create Requisition and approve

2. Create purchase and approve

3. Generate receipts for Goods Received

4. Create an Invoice in AP

5. Pay the Invoice

6. Transfer, Import and Post to GL

1) Create Requisition:

Requisition is nothing but a formal request to buy something (like Inventory material, office supplies etc) needed for the enterprise. Only an employee can create one. There are two types of requisitions:

Internal Requisition: Internal requisitions provide the mechanism for requesting and transferring material from one inventory to other inventory.

Purchase requisition: Unlike Internal requisitions, Purchase requisitions are used for requesting material from suppliers.

Navigation:

Purchasing Vision Operations (USA) > Requisitions > Requisitions

Choose the requisition type and enter the Item, quantity, Price details in the Lines tab.

At any time the status of requisition can be checked using the Requisition summary window.

Navigation:

Requisitions > Requisition Summary

Enter

We can also check the Action History of requisition (it will show details about who has submitted, approved and cancelled the requisitions) as below:

Navigation:

Tools menu > Action History.

Underlying Tables:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

2) Create Purchase Order:

There are 4 types of Purchase Orders:

1. Standard PO: A Standard PO is created for onetime purchase of various items

2. Planned PO: A Planned PO is a longterm agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

3. Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.

4. Contract agreement: Contract purchase agreements are created with your suppliers to agree

on specific terms and conditions without indicating the goods and services that you will be purchasing

Navigation for creating a standard PO:

Purchase Orders > Purchase Orders

Choose type as Standard Purchase Order. Enter the Supplier, Buyer. In the Lines tab, specify the line number, line type, Item, quantity, price etc.

Click Terms to enter terms, conditions, and control information for purchase orders.

Click Currency button to enter and change currency information for purchase orders, RFQs, and quotations.

Click Shipments button to enter multiple shipments for standard and planned purchase order lines Purchase order shipment specifies the quantity, shipto organization and location, date you want your supplier to deliver the items on a purchase order line, and country of origin for the items. When you save, Purchasing creates distributions depending on the default information available.

To enter more shipment information, select the More tab.

1. Enter the Receipt Close Tolerance percent, Invoice Close Tolerance percent to set the receiving and invoice close point.

2. Select one of the following options for Match Approval Level:

TwoWay: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

ThreeWay: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

FourWay: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

1. Select an Invoice Match Option:

Purchase Order: Payables must match the invoice to the purchase order.

Receipt: Payables must match the invoice to the receipt.

Save the work.

Click the Receiving Controls button to enter receiving control information for purchase orders.

- Enter the maximum acceptable number of Days Early and Days Late for receipts.

- Enter the Action for receipt date control.

- Enter the maximum acceptable overreceipt Tolerance percent (receipts that exceed the

quantity received tolerance).

- Enter the Action for Overreceipt Quantity.

- Select Allow Substitute Receipts to indicate that receivers can receive substitute items in place

of ordered items.

- Enter the default Receipt Routing that you assign goods: Direct Delivery, Inspection Required,

or Standard Receipt.

- Enter the Enforce Ship To location option to determine whether the receiving location must be

the same as the shipto location.

Save the work.

Click Distributions button to enter distributions for the shipments.

Select more tab to enter more details and the requisition number (optional).

Save the work.

Click on the Approve button to initiate the Approval process.

Underlying Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL (REQ_HEADER_REFERENCE_NUM in Distributions table is the Requisition number for this PO)

PO_LINE_LOCATIONS_ALL

3) Create Receipt:

Create a receipt to receive the items in the Purchase Order.

Navigation:

ReceivingReceipts

Enter the PO Number and select find button.

Go to Lines, check the lines you want to receive in the PO.

Click on Header button and Save which creates the receipt.

Receipt Tables are:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4) Create Invoice in Payables:

Once the goods are received, its time to pay the vendor for the goods purchased and hence the invoices are created.

Navigation:

Payables, Vision Operations (USA) > Invoices->Entry->Invoices

Enter type -- Standard, supplier information and amount.

Click the Match button to match to either Purchase Order or Receipt (depending on the Invoice Match option specified on the PO) and avoid manually entering the invoice.

Enter the PO Number you want match to and click Find.

Select the lines required and click on Match button.

Click on Distribute button to navigate to the Match to Purchase Order Distributions window.

This creates the invoice and you can see the status of the invoice as Never Validated. it has to be Validated and Accounted before you can pay it.

Validating the Invoice:

Click on Actions Button and Select Validate. Click on OK button.

Now you can see the status of the invoice as Validated, if there are no issues during validation.

Create Accounting Entries:

Click on Actions Button and Select Create Accounting. Click on OK button.

Now we can see the Accounted status as Yes.

You can see the Accounting Entries here:

Tools View Accounting

Invoice Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Accounting Entries Tables:

AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL

5) Making a Payment:

Go to the Invoice window and query the invoice you want to pay. You would see Amount paid as 0.00 before you make a payment.

Click Actions button. Select Pay in full and click OK.

Select the Bank Account and Document. Save the Work.

Now that the payment is made, when you query for the invoice in Invoice window, you will the Amount Paid as $4,000.00.

Create Accounting entries for payment.

Click Actions and select Create Accounting

Select the void checkbox to cancel the payment.

View Accounting Entries:

In the Payments window, query for the payment.

Tools menu View Accounting

Payment Tables:

AP_INVOICE_PAYMENTS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_CHECKS_ALL

AP_CHECK_FORMATS

AP_BANK_ACCOUNTS_ALL

AP_BANK_BRANCHES

AP_TERMS

You can also pay the invoices using Payment Batch screen. Refer to the article Make AP Payments through Payment Batches

6) Transfer to General Ledger:

Navigation:

Payables Responsibility > View Requests

Run the concurrent program Payables Transfer to General Ledger with the required parameters.