Purchase order with Budget account query in oracle apps

SELECT   prh.SEGMENT1 "PO_Rrequisition_ #",
         Prl.LINE_NUM "PO_Rrequisition_Line_ #",
         (Prl.UNIT_PRICE * Prl.QUANTITY) "PO_Rrequisition_Amount",
         Prd.GL_ENCUMBERED_DATE "PO_Req_Encumbrance_Date",
         Prd.GL_ENCUMBERED_PERIOD_NAME "PO_Req_Period",
         Prd.ENCUMBERED_FLAG "Req_Encum_Flag",
         Prd.ENCUMBERED_AMOUNT "Req_Encum_Amount",
         glkfv.SEGMENT5 "Account",
         glkfv.CONCATENATED_SEGMENTS "Concat_Seg",
         poh.SEGMENT1 "PO_ #",
         pol.LINE_NUM "PO_Line_ #",
         (pol.UNIT_PRICE * pol.QUANTITY) "PO_Amount",
         Pod.GL_ENCUMBERED_DATE "PO_Encumbrance_Date",
         Pod.GL_ENCUMBERED_PERIOD_NAME "PO_Period",
         pol.CLOSED_DATE,
         pol.CLOSED_FLAG,
         Pod.ENCUMBERED_FLAG "PO_Encum_Flag",
         Pod.ENCUMBERED_AMOUNT "PO_Encum_Amount"
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         PO_REQ_DISTRIBUTIONS_INQ_V PORV,
         po_distributions_all pod,
         po_headers_all poh,
         po_lines_all pol,
         GL_CODE_COMBINATIONS_kfv glkfv
 WHERE       1 = 1
         AND prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND prd.DISTRIBUTION_ID = PORV.DISTRIBUTION_ID
         AND prd.distribution_id = pod.req_distribution_id(+)
         AND pol.po_line_id(+) = pod.po_line_id
         AND poh.po_header_id(+) = pol.po_header_id
        AND glkfv.CODE_COMBINATION_ID = PORV.BUDGET_ACCOUNT_ID
         AND glkfv.SEGMENT5 IN ('00110011')

exec MO_GLOBAL.INIT('PO');

Purchase order to AP Invoice Detail Query

/* Formatted on 10/26/2016 1:41:32 PM (QP5 v5.114.809.3010) */
SELECT   prh.SEGMENT1 "PO_Rrequisition_ #",
         Prl.LINE_NUM "PO_Rrequisition_Line_ #",
         (Prl.UNIT_PRICE * Prl.QUANTITY) "PO_Rrequisition_Amount",
         Prd.GL_ENCUMBERED_DATE "PO_Req_Encumbered_Date",
         Prd.GL_ENCUMBERED_PERIOD_NAME "PO_Req_Period",
         poh.SEGMENT1 "PO_ #",
         pol.LINE_NUM "PO_Line_ #",
         (pol.UNIT_PRICE * pol.QUANTITY) "PO_Amount",
         Pod.GL_ENCUMBERED_DATE "PO_Encumbered_Date",
         Pod.GL_ENCUMBERED_PERIOD_NAME "PO_Period",
         Api.INVOICE_NUM "Invoice #",
         Apl.LINE_NUMBER "Invoice_Line_ #",
         Apl.AMOUNT "Invoice_Amount",
         Apl.PERIOD_NAME "Invoice_Period",
         Api.GL_DATE "INN_Gl_Date"
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         po_distributions_all pod,
         po_headers_all poh,
         po_lines_all pol,
         ap_invoice_distributions_all apd,
         ap_invoice_lines_all apl,
         ap_invoices_all api
 WHERE       1 = 1
         AND prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND prh.SEGMENT1 in('7') -- PO Requisition #
--         And  poh.SEGMENT1 = '1083'  -- Purchase Order #
--         AND api.invoice_num = 'CRINV00930'  -- AP Invoice #
         AND prd.distribution_id = pod.req_distribution_id(+)
         AND pol.po_line_id(+) = pod.po_line_id
         AND poh.po_header_id(+) = pol.po_header_id
         AND pod.po_distribution_id = apd.PO_DISTRIBUTION_ID(+)
         AND api.invoice_id(+) = apd.invoice_id
         AND api.invoice_id = apl.invoice_id(+)

PO to GL link in R12

/* Formatted on 6/24/2014 1:11:42 PM (QP5 v5.115.810.9015) */
SELECT prh.segment1 req_number,
       prd.distribution_id,
       poh.segment1 po_num,
       poh.org_id,
       rsh.receipt_num,
       api.invoice_num,
       apl.line_number,
       aps.vendor_name
FROM  -- Purchase Requisition --
     po_requisition_headers_all prh,
     po_requisition_lines_all prl,
     po_req_distributions_all prd,
     -- Purchase Order --
     po_distributions_all pod,
     po_headers_all poh,
     po_lines_all pol,
     -- Receipt --
     rcv_transactions rct,
     rcv_shipment_lines rsl,
     rcv_shipment_headers rsh,
     -- AP Invoice --
     ap_invoice_distributions_all apd,
     ap_invoice_lines_all apl,
     ap_invoices_all api,
     -- Subledger Accounting--
     xla_distribution_links xldl,
     apps.xla_ae_lines al,
     xla_ae_headers ah,
     apps.xla_events e,
     apps.xla_transaction_entities te,
     -- GL --
     gl_import_references gir,
     apps.gl_je_lines jl,
     gl_je_headers glh,
     apps.gl_code_combinations glcc,
    -- Supplier Detail --
     ap_suppliers aps
WHERE  1 = 1
       -- Purchase Requisition --
      AND prh.requisition_header_id = prl.requisition_header_id
      AND prl.requisition_line_id = prd.requisition_line_id
      -- Purchase Requisition TO  Purchase Order --
      AND prd.distribution_id = pod.req_distribution_id
      -- Purchase Order --
      AND pol.po_line_id = pod.po_line_id
      AND poh.po_header_id = pol.po_header_id
      -- Purchase Order  TO Receipt --
      AND pod.po_distribution_id = rct.po_distribution_id
      -- Receipt --
      AND rct.shipment_line_id = rsl.shipment_line_id
      AND rsh.shipment_header_id = rsl.shipment_header_id
      -- Receipt TO Invoice --
      AND apd.po_distribution_id = rct.po_distribution_id
      -- AP Invoice --
      AND api.invoice_id = apd.invoice_id
      AND api.invoice_id = apl.invoice_id
      -- AP Invoice TO Sub ledger Accounting --
      AND xldl.applied_to_source_id_num_1 = api.invoice_id
      AND xldl.source_distribution_id_num_1 = apd.invoice_distribution_id
      -- Sub ledger Accounting --
      AND xldl.ae_line_num = al.ae_line_num
      AND xldl.ae_header_id = al.ae_header_id
      AND al.ae_header_id = ah.ae_header_id
      AND al.application_id = ah.application_id
      AND ah.event_id = e.event_id
      AND e.entity_id = te.entity_id(+)
      AND e.application_id = te.application_id(+)
      -- Subledger Accounting  TO   GL  --
      AND al.gl_sl_link_id = gir.gl_sl_link_id
      -- GL  ---
      AND gir.je_header_id = jl.je_header_id
      AND gir.je_line_num = jl.je_line_num
      AND jl.je_header_id = glh.je_header_id
     AND jl.code_combination_id = glcc.code_combination_id
      -- Supplier Detail --
      AND api.vendor_id = aps.vendor_id
      AND ah.je_category_name = 'Purchase Invoices'
      AND ah.gl_transfer_status_code= 'Y'
      AND glh.STATUS='P'
      --      AND api.invoice_num = 'ERS-9772-135635'
      --      AND apl.line_number = 1

P2P Technical Flow(Complete Query)

/* Formatted on 6/23/2014 5:26:24 PM (QP5 v5.115.810.9015) */
SELECT prh.segment1 "PO Requisition Number",
       pha.segment1 "PO Number",
       aps.segment1 "Supplier Number",
       aps.vendor_name,
       apss.vendor_site_code,
       apsc.first_name,
       apsc.last_name,
       pla.item_id,
       plla.ship_to_organization_id,
       plla.ship_to_location_id,
       rt.transaction_type,
       rt.destination_type_code,
       rsh.receipt_num "PO Receipt Number",
       aia.invoice_num,
       aida.dist_code_combination_id,
       aca.check_number,
       gjh.ledger_id,
       gjh.name
FROM po_requisition_headers_all prh,
     po_requisition_lines_all prl,
     po_req_distributions_all prd,
     po_headers_all pha,
     po_lines_all pla,
     po_distributions_all pda,
     po_line_locations_all plla,
     ap_suppliers aps,
     ap_supplier_sites_all apss,
     ap_supplier_contacts apsc,
     rcv_transactions rt,
     rcv_shipment_headers rsh,
     rcv_shipment_lines rsl,
     ap_invoices_all aia,
     ap_invoice_lines_all aila,
     ap_invoice_distributions_all aida,
     ap_invoice_payments_all aipa,
     ap_checks_all aca,
     xla.xla_transaction_entities xte,
     xla_events xe,
     xla_ae_headers xah,
     xla_ae_lines xal,
     xla_distribution_links xdl,
     gl_import_references gir,
     gl_je_batches gjb,
     gl_je_headers gjh,
     gl_je_lines gjl
WHERE     prh.segment1 = :requitionnumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
      AND aps.vendor_id = pha.vendor_id
      AND apss.vendor_id = aps.vendor_id
      AND apss.vendor_site_id(+) = pha.vendor_site_id
      AND apss.vendor_site_id = aca.vendor_site_id
      AND apsc.vendor_site_id = apss.vendor_site_id
      AND apsc.vendor_contact_id = pha.vendor_contact_id
      AND prl.requisition_header_id = prh.requisition_header_id
      AND prd.requisition_line_id = prl.requisition_line_id
      AND pda.req_distribution_id = prd.distribution_id
      AND pla.po_header_id = pda.po_header_id
      AND pla.po_line_id = pda.po_line_id
      AND pha.po_header_id = pla.po_header_id
      AND pha.org_id = 204
      AND plla.po_header_id = pla.po_header_id
      AND plla.po_line_id = pla.po_line_id
      AND rt.transaction_type = 'DELIVER'
      AND rt.po_header_id = pha.po_header_id
      AND rt.po_line_id = pla.po_line_id
      AND rsh.shipment_header_id = rt.shipment_header_id
      AND rsl.shipment_header_id = rsh.shipment_header_id
      AND rsl.shipment_line_id = rt.shipment_line_id
      AND aila.po_header_id = pha.po_header_id
      AND aila.po_line_id = pla.po_line_id
      AND aia.invoice_id = aila.invoice_id
      AND aida.invoice_id = aila.invoice_id
      AND aida.invoice_line_number = aila.line_number
      AND aipa.invoice_id = aia.invoice_id
      AND aca.check_id = aipa.check_id
      AND xte.entity_code = 'AP_PAYMENTS'
      AND xte.transaction_number = aca.check_number
      AND xte.source_id_int_1 = aipa.check_id
      AND xte.security_id_int_1 = aia.org_id
      AND xe.entity_id = xte.entity_id
      AND xah.event_id = xe.event_id
      AND xal.ae_header_id = xah.ae_header_id
      AND xal.ae_line_num = aida.invoice_line_number
      AND xdl.ae_header_id = xah.ae_header_id
      AND xdl.ae_line_num = xal.ae_line_num
      AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
      AND gir.reference_5 = xte.entity_id                         -- Entity Id
      AND gir.reference_6 = TO_CHAR (xe.event_id)                   --Event Id
      AND gir.reference_7 = TO_CHAR (xah.ae_header_id)         -- AE Header Id
      AND gir.gl_sl_link_id = xal.gl_sl_link_id
      --and gir.created_by = 1318
      AND gjb.je_batch_id = gir.je_batch_id
      AND gjh.je_batch_id = gjb.je_batch_id
      AND gjh.je_header_id = gir.je_header_id
      AND gjl.je_header_id = gjh.je_header_id
      AND gjl.je_line_num = gir.je_line_num

11i vs. R12 Procure to Pay (P2P) Cycle different in oracle apps

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.

1. Choose the requisition type and enter the Item, quantity, Price details in the Lines tab. 
2. In Source Details tab, specify the Buyer name.
3. Click the Distributions button. Enter the Charge Account.
4. Save the work. The status of the requisition will now be Incomplete. And now the Approve button is highlighted.

The requisition needs to be approved first before proceeding further by the concerned authority. Submit this requisition for Approval by clicking on the Approve button. The status will now be updated to In Process .The workflow then will send an Approval notification to the concerned person (derived based on hierarchy used Position or Supervisor hierarchy) using which he can Approve or Reject the requisition.

• At any time the status of requisition can be checked using the Requisition summary window. Navigation (Requisitions > Requisition Summary). Here enter requisition number and click on the find button.
• We can also check the Action History of requisition (it will show details about who has submitted, approved and cancelled the requisitions). Navigation (Tools menu > Action History).
• Underlying Tables:
  • PO_REQUISITION_HEADERS_ALL
  • PO_REQUISITION_LINES_ALL
  • PO_REQ_DISTRIBUTIONS_ALL

Without changing Responsibility and without closing windows buyers will now be able to view consolidated requisition demand spanning multiple operating units. The operating units that buyers can transact on behalf of will be controlled by the security profile associated with their responsibilities.

• Operating Unit extra field in the R12 forms due to MOAC.
• Base tables are same as 11i but more than 15 extra columns in R12 for the table PO_REQUISTION_HEADERS_ALL, PO_REQUISTION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL for tax etc.
• Many changes were happened in supplier details in R12.

Note: Suppliers into TCA – Architecture in R12:
Prior to R12 the supplier’s information is stored in PO_VENDORS. From R12 onwards suppliers have been moved in to the TCA Data Model.

• HZ_PARTIES: This is the master table for suppliers.
• HZ_PARTY_USG_ASSIGNMENTS: This table stores the Party Usages, for example, for the suppliers it captures the fact that the given party_id is of type SUPPLIER. 

• HZ_ORGANIZATION_PROFILES: This table captures additional Supplier information, for example, credit scoring details of Supplier or the Number of Employees working in Supplier Organization.
• IBY_EXTERNAL_PAYEES_ALL: It captures payment related details of the supplier. Like how should the supplier’s remittance advice must be sent, default Payment method Code for this supplier, who bears the bank charges when let’s say SWIFT payment is made?
• AP_SUPPLIERS: This is another master table that replaces the PO_VENDORS table. It stores the supplier specific attributes.
• POS_SUPPLIER_MAPPINGS: This table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID. This is useful in cases whereby two vendors effectively belong the same HZ_Party Record.
• ZX_PARTY_TAX_PROFILE: The taxation related details like Tax Codes, and Tax Accounts etc. have been moved from AP into ZX. ZX is the name of a new Application “E-Business Tax”.
• PO_VENDORS: PO_VENDORS is a view in R12, which joins AP_SUPPLIERS & HZ_PARTIES.
  Similarly, PO_VENDOR_SITES and PO_VENDOR_SITES_ALL are also views based upon AP_SUPPLIER_SITES_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 long-term 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:

1. Purchase Orders > Purchase Orders
2. Choose type as Standard Purchase Order. Enter the Supplier, Buyer. In the Lines tab, specify the line number, line type, Item, quantity, price etc.
3. Click Terms to enter terms, conditions, and control information for purchase orders.
4. Click Currency button to enter and change currency information for purchase orders, RFQs, and quotations.
5. Click Shipments button to enter multiple shipments for standard and planned purchase order lines Purchase order shipment specifies the quantity, ship-to 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.
6. To enter more shipment information, select the More tab.
7. Enter the Receipt Close Tolerance percent, Invoice Close Tolerance percent to set the receiving and invoice close point.
8. 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.

1. Save the work.
2. Click the Receiving Controls button to enter receiving control information for purchase orders.
3. Enter the maximum acceptable number of Days Early and Days Late for receipts.
4. Enter the Action for receipt date control.
5. Enter the maximum acceptable over-receipt Tolerance percent (receipts that exceed the quantity received tolerance).
6. Enter the Action for Over-receipt Quantity.
7. Select Allow Substitute Receipts to indicate that receivers can receive substitute items in place of ordered items.
8. Enter the default Receipt Routing that you assign goods: Direct Delivery, Inspection Required, or Standard Receipt.
9. Enter the Enforce Ship To location option to determine whether the receiving location must be the same as the ship-to location.
10. Save the work.
11. Click Distributions button to enter distributions for the shipments.
12. Select more tab to enter more details and the requisition number (optional).
13. Save the work.
14. 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

  
For creating a Purchase order, let us use the “Autocreate Documents” Form. Follow the below Navigation and Query for the Requisition.

1. Click on Automatic to create a Purchase Order.
2. Click on “Create” button to create a Purchase order.
3. View the shipment screen to change the “Match Approval Level” to “2-Way”.
4. Click the “Receiving Controls” to make sure that the “Routing” is made as “Direct Routing”
5. Click Save and submit for Approval.

Note:

1. In Release 11i10 generation of purchasing documents in Adobe PDF format required organizations to create layout templates in the form of XSL-FO style sheets. With Release 12.0 organizations can also use RTF or PDF layout templates which require little training to configure and maintain.
2. Base tables are same as 11i but few extra columns.
3. Release 12.0 allows finer control of how blanket agreements are enabled for automatic document sourcing. Buyers can now choose to enable them only in specific inventory organizations.

3) Create Receipt:

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

1. Receiving—Receipts.
2. Enter the PO Number and select find button.
3. Go to Lines; check the lines you want to receive in the PO.
4. Click on Header button and save which creates the receipt.
5. Now you can check the Inventory in the same subinventory for the item’s on-hand quantity.

Receipt Tables are:

• RCV_SHIPMENT_HEADERS
• RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

 

• Navigate to RECEIVING–>RECEIPTS
• Query with the Purchase order created in the above stage.
• Check the check box near to the lines that are received and click save.
• Click the “Header Button” to view the Receipt Number.

Note:
Base tables are same as in 11i but few extra columns.

4) Create Invoice in Payable: 

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

1. Navigation: Payables, Vision Operations (USA) > Invoices Entry> Invoices
2. Enter type –Standard, supplier information and amount.
3. 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.
4. Enter the PO Number you want match to and click Find.
5. Select the lines required and click on Match button.
6. Click on Distribute button to navigate to the Match to Purchase Order Distributions window.
7. 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.
8. Validating the Invoice: Click on Actions Button and Select Validate. Click on OK button.
9. Now you can see the status of the invoice as Validated, if there are no issues during validation.
10. Create Accounting Entries: Click on Actions Button and Select Create Accounting. Click on OK button.
11. You can see the Accounting Entries here: Tools –View Accounting.
12. Now we can see the Accounted status as Yes.

Invoice Tables:

• AP_INVOICES_ALL
• AP_INVOICE_DISTRIBUTIONS_ALL

Accounting Entries Tables:

• AP_ACCOUNTING_EVENTS_ALL
• AP_AE_HEADERS_ALL
• AP_AE_LINES_ALL

 
Creating invoices, debit memo, credit memo and do payments for the suppliers. The information regarding these invoices are stored into ap_invoices_all and also in in to ap_invoice_lines_all.
AP_INVOICE_LINES_ALL holds invoice lines records entered manually, generated automatically or imported from the Open Interface tables. An invoice can have one or more invoice lines and an invoice line can have one or more invoice distributions. An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
Till 11i version, we have seen invoices:

• Had only distributions line.
• Allocation of freight and special charges are captured at the distribution level only.
• Tax and payment and Project accounting Payment was captured through global Descriptive Flexfields.

But in R12:

• Invoice Lines as a new additional line accommodated in Invoice data model.
• Allocate freight and special charges are captured to the lines on the invoice.
• Invoice distributions created at the maximum level of detail similar to 11i.

Because of introduction of invoice lines there is significant improvement of data flow with other oracle modules

• Fixed Asset – Asset Tracking
• Business Tax – Tax line
• Payment – Payment
• SubLedger Accounting – Accounting

5) Making a Payment:

 

1. 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.
2. Click Actions button. Select Pay in full and click OK.
3. Select the bank account and Document. Save the Work.
4. Now that the payment is made, when you query for the invoice in Invoice window, you will find the Amount Paid.
5. Create Accounting entries for payment. Click Actions and select Create Accounting.
6. Select the void checkbox to cancel the payment.
7. View Accounting Entries: In the Payments window, query for the payment. Tools> menu>View Accounting.
8. You can also pay the invoices using Payment Batch screen.

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

Once invoice got approved, click actions so that we can “Create Accounting” and “Create Payments” via “Action” Button in the “Invoice Form” as we validated the invoice.
Suppliers Bank Accounts:

• In 11i, Banks and Branches are defined in AP. Due to this, Bank accounts often replicated in multiple OUs.
• In R12, Suppliers, Banks and Branches are defined as Parties in TCA. Supplier (party’s) payment information and all payment instruments (Bank Accounts, Credit Cards) moved into Oracle Payments.

6) Transfer to General Ledger: 

Navigation:
Payables Responsibility > View Requests
Run the concurrent program Payables Transfer to General Ledger with the required parameters.
Journal Import: To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.

1. Navigation: General Ledger > Journal> Import> Run
2. Select the appropriate Source.
3. Enter one of the following Selection Criteria:

• No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.
• All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.
• Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.

1. If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
2. Define the Journal Import Run Options (optional).
3. Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.
4. Choose Create Summary Journals to have journal import create the following:

• One journal line for all transactions that share the same account, period, and currency and that has a debit balance.
• One journal line for all transactions that share the same account, period, and currency and that has a credit balance.

1. Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
2. Choose whether to Import Descriptive Flexfields, and whether to import them with validation.
3. Click on Import button.

Posting:
We have to Post journal batches that we have imported previously to update the account balances in General Ledger.

1. Navigation: General Ledger> Journals > Enter
2. Query for the unposted journals for a specific period.
3. If you know the batch name to be posted you can directly post using the Post window. Navigation: General Ledger> Journals> Post

Navigation:
Payables Responsibility > View –>Requests
Run the concurrent program “create accounting” with the required parameters.
Mode= Final
Post in GL = yes….etc
The Create Accounting process has similar options, you can create accounting in Final or Draft mode and if Final mode is selected, the Transfer to GL parameter can be used to automatically transfer the accounting created by the corresponding run. When the Create Accounting process transfers the journal entries to GL it only transfers the accounting created by the process that calls it. If there is accounting created by the online option = Final or a previous Create Accounting program that was not transferred, that accounting will not be transferred. The Transfer Journal Entries to GL program needs to be run separately to transfer any accounting created online or created by a previous Create Accounting process that did not transfer the entries.
Note:

1. In R12 the adjustment in GL is automated. No manual journal is required. The create accounting program and Transfer to GL program will transfer the write off journals automatically to General Ledger.
2. In Release 11i you reconciled your transactions and distributions to General Ledger through transactional reports. In the accounting reports in Release 11i you’d see the balances but not the transactional information. In Release 12 there is no reason to reconcile distributions on transactions to General Ledger. You can still reconcile transaction reports to accounting reports. The new accounting reports in Release 12 now also contain more comprehensive information not just on balances but also on suppliers, customers and transactions. The point to note is that when you do account balance reconciliation you should ignore distributions in Release 12, especially if you have altered your subledger accounting rules.

Pay on Receipt(s) in Oracle Apps R12

Pay on Receipt(s) in Oracle Apps R12
POR (Pay on Receipt) is a feature in P2P cycle. Its helping to generate the invoice automatically when you receive the goods.

Pre-Requisites
1. Supplier site should be enabled the following flags. (PAY and PURCHASING)
2. "Pay on" field should have a value "Receipt". The invoice summary level should also have the value of "Receipt".
3. In Purchase Order; Terms window "Pay On" field value should be "Receipt".

1. Supplier Creation
Navigation (Purchasing Super User > Supply Base > Suppliers > Create Supplier)
Name    : SUPPLIER_1
Type     : Standard
Country : United States

2. Supplier Site Creation (Address Book)
Enter valid address and check the PURCHASING & PAYMENT flags.

3. Purchase Order Creations
** For an example, I am creating the Blanket PO. (for an easy process/testing purpose, we can create multiple requisitions against this PO.)

(Approval Notification)

4. Just ensure the Approved Supplier list. (Supplier, Site and Item) If the combination is not present please create the same.

5. Define the Sourcing Rule and assign the same into "Assignment Set"

6. Create the PO Requisitions for the item as we mentioned in PO. (Complete the approval and Release process.)

Inventory Responsibility >> Receipts

Purchasing Responsibility >> Requests >> Submit Request ("Pay On Receipt Autoinvoice")

It's automatically submits the "Payables Open Interface Import". Verify the invoice details ...

Purchase module sub-query in oracle apps

/* Formatted on 12/24/2013 6:18:41 PM (QP5 v5.114.809.3010) */
SELECT   segment1
  FROM   po_headers_all
 WHERE   po_header_id IN (SELECT   po_header_id
                            FROM   po_lines_all
                           WHERE   po_header_id = 756464)


/* Formatted on 12/24/2013 6:20:35 PM (QP5 v5.114.809.3010) */
SELECT   segment1
  FROM   po_headers_all
 WHERE   po_header_id IN
               (SELECT   po_header_id
                  FROM   po_lines_all
                 WHERE   po_header_id IN (SELECT   po_header_id
                                            FROM   po_distributions_all
                                           WHERE   po_header_id = 756464))
                                         
                
/* Formatted on 12/24/2013 6:30:32 PM (QP5 v5.114.809.3010) */
SELECT   segment1
  FROM   po_headers_all
 WHERE   po_header_id IN
               (SELECT   po_header_id
                  FROM   po_lines_all
                 WHERE   po_header_id IN
                               (SELECT   po_header_id
                                  FROM   po_distributions_all
                                 WHERE   PO_DISTRIBUTION_ID IN
                                               (SELECT   PO_DISTRIBUTION_ID
                                                  FROM   rcv_transactions
                                                 WHERE   PO_DISTRIBUTION_ID =
                                                            53129)))
                                                           
                                                           
                                                           
/* Formatted on 12/24/2013 6:46:27 PM (QP5 v5.114.809.3010) */
SELECT   segment1
  FROM   po_headers_all
 WHERE   po_header_id IN
               (SELECT   po_header_id
                  FROM   po_lines_all
                 WHERE   po_header_id IN
                               (SELECT   po_header_id
                                  FROM   po_distributions_all
                                 WHERE   PO_DISTRIBUTION_ID IN
                                               (SELECT   PO_DISTRIBUTION_ID
                                                  FROM   rcv_transactions
                                                 WHERE   SHIPMENT_LINE_ID IN
                                                               (SELECT   SHIPMENT_LINE_ID
                                                                  FROM   RCV_SHIPMENT_LINES
                                                                 WHERE   SHIPMENT_LINE_ID =
                                                                            3011))))


/* Formatted on 12/24/2013 7:23:19 PM (QP5 v5.114.809.3010) */
SELECT   segment1
  FROM   po_headers_all
 WHERE   po_header_id IN
               (SELECT   po_header_id
                  FROM   po_lines_all
                 WHERE   po_header_id IN
                               (SELECT   po_header_id
                                  FROM   po_distributions_all
                                 WHERE   PO_DISTRIBUTION_ID IN
                                               (SELECT   PO_DISTRIBUTION_ID
                                                  FROM   rcv_transactions
                                                 WHERE   SHIPMENT_LINE_ID IN
                                                               (SELECT   SHIPMENT_LINE_ID
                                                                  FROM   RCV_SHIPMENT_LINES
                                                                 WHERE   SHIPMENT_LINE_ID IN
                                                                               (SELECT   SHIPMENT_LINE_ID
                                                                                  FROM   RCV_SHIPMENT_HEADERS
                                                                                 WHERE   SHIPMENT_LINE_ID =
                                                                                            3005)))))

PO TO GL LINK IN R12

  PO TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )

SELECT   prh.segment1 "PO Requisition Number",
         pha.segment1 "PO Number",
         aps.SEGMENT1 "Supplier Number",
         aps.vendor_name,
         apss.vendor_site_code,
         apsc.first_name,
         apsc.last_name,
         pla.item_id,
         plla.ship_to_organization_id,
         plla.ship_to_location_id,
         rt.transaction_type,
         rt.destination_type_code,
         rsh.receipt_num "PO Receipt Number",
         aia.invoice_num,
         aida.dist_code_combination_id,
         aca.check_number,
         gjh.ledger_id,
         gjh.name
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         po_headers_all pha,
         po_lines_all pla,
         po_distributions_all pda,
         po_line_locations_all plla,
         ap_suppliers aps,
         ap_supplier_sites_all apss,
         ap_supplier_contacts apsc,
         rcv_transactions rt,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         ap_invoices_all aia,
         ap_invoice_lines_all aila,
         ap_invoice_distributions_all aida,
         ap_invoice_payments_all aipa,
         ap_checks_all aca,
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 WHERE       prh.segment1 = :RequitionNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND aps.vendor_id = pha.vendor_id
         AND apss.vendor_id = aps.vendor_id
         AND apss.vendor_site_id(+) = pha.vendor_site_id
         AND apss.vendor_site_id = aca.vendor_site_id
         AND apsc.vendor_site_id = apss.vendor_site_id
         AND apsc.vendor_contact_id = pha.vendor_contact_id
         AND prl.requisition_header_id = prh.requisition_header_id
         AND prd.requisition_line_id = prl.requisition_line_id
         AND pda.req_distribution_id = prd.distribution_id
         AND pla.po_header_id = pda.po_header_id
         AND pla.po_line_id = pda.po_line_id
         AND pha.po_header_id = pla.po_header_id
         AND pha.org_id = 204
         AND plla.po_header_id = pla.po_header_id
         AND plla.po_line_id = pla.po_line_id
         AND rt.transaction_type = 'DELIVER'
         AND rt.po_header_id = pha.po_header_id
         AND rt.po_line_id = pla.po_line_id
         AND rsh.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND aila.po_header_id = pha.po_header_id
         AND aila.po_line_id = pla.po_line_id
         AND aia.invoice_id = aila.invoice_id
         AND aida.invoice_id = aila.invoice_id
         AND aida.invoice_line_number = aila.line_number
         AND aipa.invoice_id = aia.invoice_id
         AND aca.check_id = aipa.check_id
         AND xte.entity_code = 'AP_PAYMENTS'
         AND xte.transaction_number = aca.check_number
         AND xte.source_id_int_1 = aipa.check_id
         AND xte.security_id_int_1 = aia.org_id
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.ae_line_num = aida.invoice_line_number
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         --and gir.created_by = 1318
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num

P2P FAQ

1.       In which table is Purchase order number stored?

Ans: po_headers_all 

2.      How many types of purchasing matching option are there?

Ans: 3

3.      Types of purchasing matching options?

Ans: 2 way, 3 way, 4 way

4.      4. How many types of purchase orders are present in Oracle?

Ans: 4    

5.      5. The purchase order which consists of Supplier, Item and Cost but doesn’t know the delivery schedules

Ans: Blanket purchase Agreement

6.      Which purchase order is a long-term agreement committing to buy items or services from a single source having tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

Ans: Planned purchase order

7.       How many types of Approval Hierarchies are used in purchasing?

Ans: 2      

8.      What are the types of Approval Hierarchies used in purchasing?

Ans:  Employee-Supervisor Hierarchy and Position Hierarchy

9.      Purchasing performs matching to verify that purchase order and invoice information  Match within tolerance this is called as

Ans:  2 way      

10.  Purchasing performs matching to verify the purchase order, receipt, and invoice information match within tolerance this is called as

Ans: 3 way      

11.  Purchasing performs matching to verify the purchase order, receipt, inspection, and invoice information match within tolerance this is called as  

Ans: 4 way      

12.  A statement of the price, terms, and conditions of sale a supplier offers you for and item or items is called as  

Ans:  Quotation     

13.  A document you use to solicit supplier quotations for goods or services you need is called as

Ans:  Request for quotation (RFQ)