Reference Step by Step Process http://blog.csdn.net/papaya14/article/details/7541769
Procedure
Stage 1: Choosing an Item
Stage 2: Creation of Requisition
Stage 3: Checking the Status of Requisition
Stage 4: Creation of Purchase Order
Stage 5: Creation of Receipts
Stage 6: Checking the On Hand
Stage 7: Check the Material Transactions
Stage 8: Creation of Invoice
Stage 9: Creation of Accounting and Payment
Stage 10: Payables Transfer to General Ledger
Stage 11: Journals created in GL
-------------11111111111111111
SELECT * FROM mtl_system_items_b
WHERE segment1 = 'ITEM_SOPHIA_15';--36816
SELECT * FROM mtl_onhand_quantities_detail moqd
WHERE moqd.INVENTORY_ITEM_ID=36816
-------------22222222222222222222222222222
Create a new Requisition for the item viewed in Stage 1.
Click on Distributions to View the charge Account.
Save and Submit for Approval
SELECT * FROM po_requisition_headers_all prha WHERE prha.SEGMENT1='5659'
-- prha.REQUISITION_HEADER_ID=56869
SELECT * FROM po_requisition_lines_all prla
WHERE prla.REQUISITION_HEADER_ID=56869
--prla.REQUISITION_LINE_ID=60800
SELECT prda.* FROM po_req_distributions_all prda
WHERE prda.REQUISITION_LINE_ID=60800
-- prda.DISTRIBUTION_ID=59767
----------------------3333333333333333333333333333333333333333
For creating a Purchase order, let us use the “Autocreate Documents” Form. Follow the below Navigation
View the shipment screen to change the “Match Approval Level” to “2-Way”.
Click the “Receiving Controls” to make sure that the “Routing” is made as “Direct Delivery”
Click Save and submit for Approval.
SELECT * FROM po_distributions_all pda
WHERE pda.REQ_DISTRIBUTION_ID=59767
--pda.PO_HEADER_ID=32835
SELECT pha.* FROM po_headers_all pha
WHERE pha.SEGMENT1='123'
AND pha.PO_HEADER_ID=32835
SELECT pla.order_type_lookup_code, pla.* FROM po_lines_all pla
WHERE pla.PO_header_id=32835
SELECT r.segment1 req_num
,rl.line_num req_line_num
,P.PO_HEADER_ID
,P.segment1 po_num
,pl.line_num po_line_num
FROM po_headers_all P
,po_lines_all pl
,po_distributions_all d
,po_req_distributions_all rd
,po_requisition_headers_all r
,po_requisition_lines_all rl
WHERE P.po_header_id = d.po_header_id
AND P.po_header_id = pl.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
AND r.SEGMENT1='5659'
----------------------555555555555555555555555555555555555
SELECT * FROM RCV_TRANSACTIONS rt
WHERE rt.PO_HEADER_ID=32835
--AND rt.TRANSACTION_ID=127681/127682
-- AND rt.SHIPMENT_HEADER_ID=75416
-- and rt.SHIPMENT_LINE_ID=81364
SELECT * FROM rcv_shipment_headers rsh
WHERE rsh.SHIPMENT_HEADER_ID=75416
--and rsh.RECEIPT_NUM=7462
SELECT * FROM rcv_shipment_lines rsl
WHERE rsl.SHIPMENT_LINE_ID=81364
SELECT poh.segment1, pol.line_num, rsh.receipt_num, rsh.shipment_num,
pol.po_header_id, pol.po_line_id, pll.line_location_id, pll.quantity,
rsh.shipment_header_id, rsh.receipt_source_code, rsh.vendor_id,
rsh.vendor_site_id, rsh.organization_id, rsh.ship_to_location_id,
rsh.bill_of_lading, rsl.shipment_line_id, rsl.quantity_shipped,
rsl.quantity_received, rct.transaction_type, rct.transaction_id,
DECODE (pol.order_type_lookup_code,
'RATE', NVL (rct.amount, 0),
'FIXED PRICE', NVL (rct.amount, 0),
NVL (rct.source_doc_quantity, 0)
) transaction_qty
FROM rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll
WHERE rct.po_line_location_id = pll.line_location_id
AND poh.po_header_id = pol.po_header_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
('RATE', 'FIXED PRICE')
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
-- AND rct.TRANSACTION_ID=127681
----------------------66666666666666666666666666666666666
SELECT * FROM mtl_onhand_quantities_detail moqd
WHERE moqd.INVENTORY_ITEM_ID=36816
SELECT rsh.receipt_num
,rsl.line_num
,rct.quantity transaction_qty
,moq.transaction_quantity
FROM mtl_onhand_quantities_detail moq
,mtl_material_transactions mmt
,rcv_transactions rct
,rcv_shipment_headers rsh
,rcv_shipment_lines rsl
WHERE moq.create_transaction_id = mmt.transaction_id
AND mmt.rcv_transaction_id = rct.transaction_id
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND moq.is_consigned = 2
-- AND rsh.RECEIPT_NUM=7462
----------------------77777777777777777777777777777
Follow the below Navigation to reach “Material Transactions” Form
SELECT mmt.* FROM mtl_material_transactions mmt
WHERE transaction_action_id NOT IN (24, 30)
AND ( (organization_id = 204)
AND (inventory_item_id = 36816)
AND (transaction_date BETWEEN TO_DATE ('15-05-2012 00:00:00',
'DD-MM-YYYY HH24:MI:SS'
)
AND TO_DATE ('15-05-2012 23:59:59',
'DD-MM-YYYY HH24:MI:SS'
)
)
AND (logical_transaction = 2 OR logical_transaction IS NULL)
)
----------------------8888888888888888888888888888888888888888888888
Enter new invoice and matched it to receipt.
Validate the invoice
Below screenshot will give you the status of the invoice
SELECT * FROM ap_invoices_all apia
WHERE apia.INVOICE_NUM='SO01'
SELECT * FROM ap_invoices_all apia
WHERE apia.INVOICE_NUM='SO02'
--and apia.INVOICE_ID=63568
SELECT * FROM ap_invoice_distributions_all apida
WHERE apida.INVOICE_ID=63567
SELECT DISTINCT A.org_id "ORG ID"
,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"
,g1.quantity_received
,g1.quantity_rejected
,g1.quantity_billed
,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
FROM ap.ap_invoices_all A
,ap.ap_invoice_distributions_all b
,po.po_distributions_all c
,po.po_headers_all d
,po.po_lines_all G
,po.po_line_locations_all g1
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 d.po_header_id = G.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = G.po_line_id
AND c.po_line_id = G.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'
AND D.SEGMENT1='123'
----------------------99999999999999999999999999999999999999999
SELECT * FROMap_invoice_payments_allapipa
WHEREapipa.INVOICE_ID=63569--SO03
In Invoice Actions Button enable Pay in full check Box and click Ok. In Invoice Actions Button enable Pay in full check Box and click Ok.
It will open the payments form. Select the type as Quick and also enter the mandatory fields in the form like Bank account and document type and click on save.
Enter the payment type as ‘Quick’, and bank Account details.
It will show the alert box mentioning ’Payables is reserving the payment document’.
Click on Ok and save the form.Click on Tools Menu to view the Accounting Entries created for the Payment.Note down the Document Number.
Click on actions, and then check the format option,this is to generate the bank statement for submitting the payment detail document to the bank.
Click on ok.Go to the concurrent program requests window, one concurrent program will be automatically submitted and running as shown below.It will internally calls another concurrent programs.
Click on view out put.The following output shows the payment details for submitting to bank for the purchase order 4582,and invoice number 4582 for the payment need to done
Similarly, payment details for the purchase orders 4586,4587 will be obtained in the same way.
Stage 10 Payables Transfer to General Ledger
Submit a new request “Payables Transfer to General Ledger” to transfer all payables to general ledger as shown below by giving the mandatory parameters.
Navigation>>Payables responsibility>>View>>Request
Set of Books Name: Give the Set of Books name. in our case, Set of books name is “Vision Operations”.
Transfer Reporting Books: set to No.
From Date: Payables from which date need to be submitted
To date: Payables to which date need to be submitted
Journal Category: select category of journal. In this case select as “ALL”.
Validate Account:
Yesà It will validates the accounts while submitting program.
Noà It will not validates the accounts while submitting program.
Transfer to GL Interface: Transferring into GL Interface includes 3 methods.
In Detail: It will transfer in detail.
Summarize By Accounting Date: It will summarize based on accounting date.
Summarize By Accounting Period: It will summarize based on accounting date.
Submit Journal Import:
Yesà It will automatically import the “Journal Import” program for importing the journals.
Noà It will not import the “Journal Import” program need to manually import them.
Stage 11 Journals created in GL
------------------11111111111111111111111111111111111111111111111
SELECT *FROMGL_INTERFACE gi
WHEREupper(gi.REFERENCE10)LIKE'%ITEM_SOPHIA_15%'
--AND gi.REFERENCE22='32835' --po_header_id
SELECT *FROMGL_JE_BATCHESglb
WHEREglb.CREATED_BY=1318
ANDglb.DEFAULT_PERIOD_NAMELIKE'May-12'
--AND glb.JE_BATCH_ID=138688
SELECT *FROM GL_JE_HEADERSglh
WHERE glh.JE_BATCH_ID=138688
--glh.JE_HEADER_ID=113390
SELECT *FROM GL_JE_LINESgll
WHEREgll.JE_HEADER_ID=113390