Oracle AP Invoice APIs

Oracle AP Invoice APIs

These APIs are handful when you want to do Insert, Update or Delete programmatically for some business requirements (rare cases!) API Name Procedure Name Description AP_AI_TABLE_HANDLER_PKG insert_row Inserts a row in AP_INVOICES_ALL update_row Updates a row in AP_INVOICES_ALL delete_row Deletes a row in AP_INVOICES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL,AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL API Name Procedure Name Description AP_AIL_TABLE_HANDLER_PKG check_unique Check the Uniqueness of a Row insert_row Inserts a row in AP_INVOICE_LINES_ALL update_row Updates a row in AP_INVOICE_LINES_ALL delete_row Deletes a row in AP_INVOICE_LINES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL API Name Procedure Name Description AP_AID_TABLE_HANDLER_PKG check_unique Check the Uniqueness of a Row insert_row Inserts a row in AP_INVOICE_DISTRIBUTIONS update_row Updates a row in AP_INVOICE_DISTRIBUTIONS delete_row Deletes a row in AP_INVOICE_DISTRIBUTIONS API Name Procedure Name Description AP_AIP_TABLE_HANDLER_PKG insert_row Inserts a row in AP_INVOICE_PAYMENTS update_amounts Update amounts in AP_INVOICE_PAYMENTS API Name Procedure Name Description AP_AC_TABLE_HANDLER_PKG insert_row Inserts a row in AP_CHECKS_ALL update_row Updates a row in AP_CHECKS delete_row Deletes a row in AP_CHECKS update_amounts Update amounts in AP_CHECKS http://bhaskarreddyapps.blogspot.in/

SATURDAY, 20 OCTOBER 2012

How to find Last Query executed on the form

How to find Last Query executed on the form

Business Case:
Imagine we have Queried/Searched something on the form and data is displayed. If you are curious to find database query executed for this search then this article explains the steps to find the same.

Solution:
Imagine i want to list the invoices for Trading Partner 'CDS, Inc' from the application.
Payables Manager > Invoices > Inquiry > Invoices > give "CDS, Inc" in the Trading Partner Name field > Find

Now we want to find the database query executed in the backend to show this data for you. Then goto

Help > Diagnostics > Examine

enter the following values
Block: SYSTEM
Field: LAST_QUERY

Value filed displays the Query for you. Copy the query and executed in TOAD/Sql Plus etc to see the same data!

How to check total outstanding you need to pay to a Supplier

Q: How much you owe a supplier and how many unpaid invoices you have in the system for   the supplier?

A: Use the Find Invoices window and choose the Calculate Balance Owed button. 

Navigation:
Invoices > Inquiry > Invoices
Enter Supplier Name or Supplier Number
and Click on 'Calculate Balance Owed' on the bottom left

Note:
If you want to calculate balance in foreign currency, then
Enter the currency for which you would like to calculate the balance.

It opens 'Calculate Balance Owed' form
Select the Operating Unit and click on 'Calculate' that shows Unpaid Amount.

• You can check all invoices against Unpaid Amount by clicking on Invoices button
• Optionally check Take Discount to have Payables reduce the balance by available discounts
• To calculate the balance owed, Payables subtracts available prepayments from the unpaid invoice amount. 
• Payables does not use unpaid prepayments when it calculates the balance. 

SUNDAY, 18 MARCH 2012

Invoice to Payment Process Flow in Oracle Payables R12

The Invoice to Payment process takes you from entering an invoice to paying the vendor.  This process includes 6 steps:
1.    Entering or Importing the Supplier Invoice
2.    Optionally, matching the invoice to a Purchase Order or Receipt
3.    Validating the invoice
4.    Resolving automatic invoice holds
5.    Running the invoice approval process and approving invoices
6.    Creating and approving payment

Step 1: Entering or importing the supplier invoice

The first step in the Invoice to Payment process will be to enter the invoice information into the Payables module.  You can perform this step a number of different ways.  You can manually enter the invoice into the Invoice Workbench form or the Quick Invoice form.  You can interface and then import the invoice.  You can also generate invoices based on your PO receipts.  To generate invoices from PO receipts, you need to run the “Pay on Receipt AutoInvoice Program” in the Purchasing module.

Step 2: Matching the invoice to a purchase order or receipt

If your invoice has an associated purchase order, you can match the invoice quantity and price to that purchase order.  If your purchase order is setup as a 2 –way match, then you match the invoice to the purchase order lines.  If your purchase order is setup as a 3-way match, then you match the invoice to the inventory receipts.

Step 3: Validating the Invoice

Once the invoice is entered into the system, you will need to run the invoice validation process.  This process can be run from the Invoice Workbench form (by clicking the Actions button) or you can validate invoices by running the invoice validation concurrent program.  The validation process performs a couple of different checks.  First, it checks to see if should apply any matching holds.  Then it will calculate and apply taxes, verify the GL period status, verify exchange rates, and verify distribution information is valid.

Step 4:  Resolving Invoice Holds

When the validation process places an invoice on hold, you must either resolve the hold or release the hold before payment can be made.  Some holds can be released (like price and quantity holds) and other need to be resolved.  These holds are called system holds and are need to be resolved to make the invoice valid.

 Step 5: Running the invoice approval process and approving invoices

After we have a valid invoice, you will need to run the invoice approval process.  This process can be tailor to your specific business needs with the Approval Management Engine (AME).  If the invoice meets the rules to require approval, AME will determine the approver(s) and send a notification.  The approver can then approve or reject the invoice online.

Step 6:  Creating and Approving payment

After we have an approved invoice, you are ready to pay the supplier.  You can create a manual payment for this invoice or you can batch all the invoices that are due for payment.  To create a payment batch, you use the Payments Process Request and select a template.  This template will identify the invoices to be paid and determines if approval is necessary.  If approval is not necessary, the payment documents will be formatted for printing (for checks) or electronic transmission.