Oracle Table Relationship SQL of Procure To Pay Life Cycle

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