Infolinks

Monday 13 May 2013

P2P CYCLE Query

Query that covers P2P life cycle

P2P Cycle

There are certain things that should be wkown before going indetail about the P2P cycle.

What is PO?
PO is a commercial document issued by a buyer to a seller, indicating the type, quantities and agreed prices for products or services that the seller will provide to the buyer.

Why we need this PO?
This document represents the buyer’s intent to purchase specific quantities of product at specified prices. In the event of non-payment, the seller can use the PO as a legal document in a court of law.

What are the types of Purchase Orders?

1. Standard Purchase Orders:You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require. Each standard purchase order line can have multiple shipments, and you can distribute the quantity of each shipment across multiple accounts.

2.Contract Purchase Orders:A contract purchase agreement is an agreement between you and a supplier for unspecified goods or services. This agreement may include terms and conditions, committed amount, and effective and expiration dates.

3. Blanket Purchase Orders:With blanket purchase agreements and releases, you can negotiate a stable, long-term contract while maintaining flexible delivery schedules and order quantity commitments. You create blanket purchase agreements when you know the details of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules.

4. Planned Purchase Orders:You create a planned purchase order when you want to establish a long-term agreement with a single supplier to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You schedule releases against the planned purchase order to actually order the goods or services.

(P to P) cycle: There are different steps involved in P to P cycle they are `

1 Define items: Inventory
2 Enter item information: Purchase Order
3 Raise requisition: Purchase Order
4 Request For Quotation(RFQ) raised: Purchase Order
5 Quotation received: Purchase Order
6 Quote analysis: Purchase Order
7 Raise purchase order: Purchase Order
8 Receive materials: Inventory
9 Enter invoice: Accounts payable
10 Payment process: Accounts payable
11 Transfer to general ledger: General ledger

Generating a purchase order
Purchasing order can be generated in two ways
1) Auto generation
2) Procedural generation

Auto generation: The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.

Creating a Requisition
1) Click on Requisition Requisitions a new form opens
2) Enter all the required information and save.
3) The approve button is enabled .click on approve button a new form opens.
4) Enter the hierarchal information and click on OK and close the form.

Raising a Purchase order directly
1) Click on the ‘Auto create’ a new form opens enter the requisition number and click on ‘Find’.
2) A new form opens. Check the item line and Select the document type as ‘Standard po’ and click on ‘Automatic’ tab.
3) A new form opens and click on ‘Create’ tab.
4) A window with the message with your purchase order number is displayed click ‘ok’ on it
5) Now click on “tools” and then on “copy Document” a new form opens,
6) click on ‘ok’ tab. And a new window with purchase order appears.
7) A new form opens Click on Approve button on this form
8) Enter the required information (real time) and click on ‘ok’ (submit approval)
9) Click ‘ok’ on new window that appears
10) And now we can see the status as ‘approved’ on the purchase order form.

Procedural generation
The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.

Creating a Requisition
1) Click on Requisition
2) Enter all the required information and save.
3) The approve button is enabled .click on approve button a new form opens.
4) Enter the hierarchal information and click on OK and close the form.

Raising A RFQ
1) A new form opens. Select the document type as RFQ and click on ‘Automatic’ tab.
2) A new form opens and clicks on ‘Create’ tab.
3) A new form opens change the status as “Active” and click on “Suppliers” tab a new from opens, enter the required information and save the form and close it.
4) Click on tools and select “copy document” a new from opens
5) Enter the required information and click on “OK” a new form opens

Approving the Quotation
1) A new from opens
2) Change as status as”active” click on “Type” and then on “approve a new form opens
3) Enter the require information and click on “ok” and a message with text ‘quotation has been approved’ will appear, now click on ‘ok’ and close the form
4) Now click on “tools” and then on “copy Document” a new form opens, click ‘ok’ on the document where your purchase order will be created with a number..
5) Click ‘ok’ on the document a new form opens

Raising a Purchase Order
1) A new form opens, Click on Approve button on this form
2) Enter the required information (real time)and click on ‘ok’(submit approval)
3) Click ‘ok’ on new window that appears
4) And now we can see the status as ‘approved’ on the purchase order form.

To view Summary of the purchase order
1) Close all the existing opened forms and click on ‘notification summary’ In the main menu.
2) A new window opens in the internet explorer
3) Enter the user name and password provided by the system admin and login
4) A new window opens with all the purchase order details.
5) Click on the exact purchase order which is created by you. this can be done only if you remember your purchase order number.

Releasing the Order
The step by step process of releasing the order is as under:
1) Click on Purchase Order Releases –A new form opens
2) Enter the Purchase Order number and other required information and click on ‘Save’
3) ‘Approve’ button gets enabled now, hence click on it.
4) A new form opens, enter the required information and click on ‘ok’
5) Now click on ‘ok’ tab that is seen on message window.
6) Now the status changes to ‘Approved’
7) Now go to ‘Notification Summary’ window
8) You can see the ‘Blanket Purchase release document’ approved.
9) Click on it to view the detailed summary.

Receiving the Order
The step by step process of receiving the order is as under:
1) Click on Receiving Receipts –A new form opens select the organisation and click on ‘ok’ simultaneously a new form opens
2) Enter the P.O number in that form and click on ‘find’
3) ‘Receipt Header’ form opens-click on new receipt radio button and close the form
4) A new form opens, check box the item that is available there and save the form. Close it.
5) Click on ‘receiving transactions’, a new form opens.
6) Enter the P.O number and click on ‘Find’, a new form opens.
7) Check box the item that is available and select ‘Stores’ as sub inventory .save and close the form.

Note: The query to retrieve the data which covers the complete p2p cycle is as follows:

 

SELECT
A.ORG_ID “ORG ID”,
E.VENDOR_NAME “VENDOR NAME”,
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) “VENDOR TYPE”,
F.VENDOR_SITE_CODE “VENDOR SITE”,
F.ADDRESS_LINE1 “ADDRESS”,
F.CITY “CITY”,
F.COUNTRY “COUNTRY”,
TO_CHAR(TRUNC(D.CREATION_DATE)) “PO DATE”,
D.SEGMENT1 “PO NUMBER”,
D.TYPE_LOOKUP_CODE “PO TYPE”,
C.QUANTITY_ORDERED “QTY ORDERED”,
C.QUANTITY_CANCELLED “QTY CANCALLED”,
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 STATUS”,
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,
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’;

No comments:

Post a Comment