PAYABLES(ABOUT INVOICES USEFUL INFORMATION)
ABOUT INVOICES
--------------
/*ACCOUNTING METHOD , ACCRUAL OR CASH :
So do you set the accounting method only at the Payables,Receivables levels,
not at the GL Level. I believe so,because of those settings,payables and
receivables will generate the journal entries accordingly.
When we create an invoice we initially create a invoice batch and then create
invoices in that batch. While creating invoice batch, we provide the control amount
and control count (the intial batch amount and intial batch count),payment terms,
type of invoice(like PO default, standard,CM,DM etc), pay group. These invoices go into
the "ap_batches_all" table. However some of the above fields are optional at the batch
level and can be specific at the invoice level.
Having entered an invoice batch, we enter the invoices in that batch and enter
the supplier information currency. Here the values mentioned at the batch level
will default at the invoice level which can be changed at the invoice level. All
the invoices go into the "ap_invoices_all" table.
Each invoice will have distributions and each distribution (which could be either
item, tax, frieght charge) will correspond to a particular gl_account and will
be stored in "ap_invoice_distributions_all" with the corresponding code_combination_id
in the table.
Having created an invoice the invoice needs to be validated,payables accounting
process and approved(may not be required). For validating an invoice it is
important that the control amount at the batch level sums up to the sum of the
individual amounts of the invoices. The control count at the batch level should
be equal to the number of invoices in that batch.
The invoice can be validated by pressing the validate button.
Make sure there are no holds on that invoice.i.e holds are 0.
And only the validated invoices will be paid or payment applied.
As mentioned before, the invoice is distributed on item,tax,freight etc), where
we mentioned for each distribution which account it should go to i.e the item should
go to account1 and tax should go to another tax related account.
Or we can create a distribution set where we mention the percentages of each item,
say the 70% of the invoice item should go to the account1 and 30% to account2.etc
and assign that distribtuon set to that supplier.
once this is done, for every invoice corresponding to that supplier ,if we select
that distribution set, the above %'s are automatically applied.
One feature of the Oracle Payables is that, the AP can also treat the employees
in a particular company as suppliers (i.e typically the suppliers send the invoices
which are paid for) similarly the employees should also be paid for the services
they render every pay period and what should be the employees payment terms.
Accounting in Payables Transactions.
There are two document classes in Accounts Payables which can give rise to accounting
transactions.
Invoice (creating Invoices)
Payment (Issuing Payments).
Entering an Invoice :
An invoice increases the suppliers account balance by the invoice amount.
Once the invoice is completed,
The corresponding journal entries will be posted to the General Ledger in the
next GL transfer. As an ex, the following are the entries created for an invoice
entered for amount $200. Hence the three main accounts that are updated in an
invoice entry are
Account Derivation DR CR
-------- --------------------------------------------------------
Liability : Defaulted from the Supplier site ,otherwise can be entered : $235
at the invoice batch level
Expense/ Asset : Defaulted from the Purchase Order,otherwise entered at the : $200
invoice level.
Tax : Defaulted from tax name,can be overwritten during invoice. : $35
*/
select batch_id,batch_name,invoice_currency_code,payment_currency_code
terms_id,gl_date,invoice_type_lookup_code,org_id
from ap_batches_all
where batch_name ='mybatch9'
select * from ap_terms_lines -- ap_terms_tl
where terms_id = 1205
/* Actually there is a payment term specified at the batch level and as well as the invoice
level. However invoice level take precedence. When you dont mention anything at the
invoice level/batch level, the term from supplier will default. Since invoices come from
suppliers, there is a chance that two invoices from two different suppliers might have
the same invoice number, and hence internally we assign a voucher number. For the same
supplier u cannot have two invoices with the same invoice number.*/
SELECT * FROM ap_invoices_all
WHERE 1 =1
and batch_id = 10065
--and invoice_num = 'myinv1'
--where invoice_id = 52989
-- Just like AR, in AP as well, the payment schedules table will have the due dates etc.
--Even without any payments,once an invoice is created, it will figure in this table.
select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code,
batch_id,org_id,inv_curr_gross_amount
from ap_payment_schedules_all
where invoice_id = 10544
SELECT * --dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_id = 10663
/* While creating an invoice batch we can provide the liability account and
while creating the invoice we provide the expense accounts for the main item
and frieght or tax purposes (called distributions) */
select * from gl_code_combinations -- 1012
where segment1 =01
and segment2 = 0000
and segment3 = 0000
and segment4 in (73100,67170,21330)
and segment5 = 0000
and segment6 = 0000
and segment7 = 0000
and segment8 = 0000
/*Default Expense or Charge Account for AP Invoice , Distribution Sets:
We know that the default liability account will come from the Financials Options and which is
shown at the invoice header level(liability account field can be sometimes hidden and hence
should be unhidden.
There is also an expense account which is at the invoice distribution level. In general
this has to be entered manually at the dist level. However this can be defaulted by defining
a distribution set and associating that distribution set at the supplier site level. Hence it is
important that DISTRIBUTION SETS ARE ONLY FOR CHARGE ACCOUNTS NOT LIABILITY ACCOUNT.
*/
select *
from ap_invoice_distributions_all
/*To approve or generate accounting entries online , ensure that the following setting is done,
setup => options => payables => invoice tab => Allow Online Validation. */
/*Hence after this is done, we want to transfer all this information into the GL,
because it is a ledger system. AP,AR,Assets are the feeder systems(or subledgers)
in financials and they will be transferring the txn's to GL to reflect the accounts
correctly. From payables to GL, this can be done by "Payables Transfer to GL" program.
So the various steps the payables invoices will go thru before they are transferred
to GL are given below
*/
Invoice Validation,
Payables Accounting Process,
Invoice Approval workflow and then
Payables Transfer to gl(which involves payables transfer,journal import and posting).
/*So once the payables txns are transferred (gl_je_headers and gl_je_lines) tables
are populated and when the txns are posted, the gl_balances tables is updated.
The gl_balances tables is a huge table which consists of an entry for each account
or code_combination_id, when the txns are posted, the corresponding accounts in the
invoice distributions are updated accordingly.*/
/* ACCOUNT GENERATOR Role in Payables & Projects :
Just want to talk about ,what is the role of account generator, say while
creating an invoice, For ex;liability and expense account are already specified
as part of the system options/distribution sets and hence it has to copy it
straight from that.
What I mean to say is that unlike AutoAccounting in AR(or FA),which has to build each
segment and the combination, the Account Generator in AP does not have to build
each segment,correct? The only place where I saw account generator work, is when
you specify a project information at the invoice distribution level, then it will
rebuild the distribution. Other than that, the Account generator does not really
have to build segments of the code combination, other than copy from the options.
So for ex, if you specify project information in the distribution level, there is
a standard workflow functionality to build the combination. However even this
can be customized by developing our own workflow functionality and specifying at
this location, (from the General Ledger responsibility)
Setup => Financials => Flexfields => Key => Accounts
This will open the form "Account Generator Processes",query for the application
"General Ledger" and the flexfield title "Accounting Flexfield" and for your
corresponding structure. Here you can see for the item type "Project Supplier
Invoice Account Generation", what is the process name. THe default value is
"Generate Default Account",which is the standard value. Any customized workflow
process can be specified here.
INVOICE VALIDATION :
--------------------
/*The Invoice validation program does a bunch of things when it starts validation,like ,
Matching
Tax
Distribution
Period Status
Exchange Rate
so while it is matching the invoice, what kind of matching it uses 2-way,3-way
or 4-way. Typically when we create an invoice we provide the supplier and supplier
site information from where we are receiving that invoice. So the system will
decide how to do the Matching based on the setting at the supplier site level.
If at the supplier site, the Invoice Match Option is set to Purhcase Order,then at the
time of validation, the invoice is matched against the purchase order.
Another interesting point is that, when we create a purchase order, at the
distributions the invoice match option is also specified which basically defaults
from the supplier site,however we can change that accordingly.
Hence as we see, there are two important parameters
Invoice Match Option : PO (or Receipt)
Match Approval Level : 2-Way (3-Way or 4-Way)
2-Way : Invoice Quantity <= PO Quantity ; Invoice Price <= PO Price;
3-Way : 2-Way + Invoice Quantity <= Receipt Quantity (by the tolerance level)
4-Way : 3-Way + Invoice Quantity <= Accepted Quantity (by the tolerance level)
(Interestingly there is also a Invoice Match Option that you can set at the Financials Options
Setup => Options => Financials Options )
The different kinds of scenarios that are available are
We can have 1 PO , 1 Receipt => 1 Invoice
We can have 1 PO , 5 Receipts => 5 Invoice
*/
-- The validation flag gets updated in the ap_invoices_all table
select * from ap_invoices_all -- approval_status?
-- Invoice Matching with Purchase Order and 3-way Matching.
/*
Generally, when an invoice is raised and there is a corresponding matching
PO, then the po_distribution_id column in the ap_invoice_distribution will
be not null, and that is the best way to identify invoices that have been
matched with PO, even though there is a column by name match_status_flag.
Once an invoice is raised against this PO, the quantity billed and the amount
billed columns will get updated with the invoice prices).
*/
select a.quantity_invoiced,a.unit_price ,c.quantity ,c.unit_price,
c.unit_price - a.unit_price price_difference
from ap_invoice_distributions_all a,
po_distributions_all b,
po_lines_all c
where a.po_distribution_id = b.po_distribution_id
and b.po_line_id = c.po_line_id
and a.accounting_date between trunc(sysdate -1) and trunc(sysdate-1) + 0.99999 --- 60354
and a.unit_price < c.unit_price
and a.po_distribution_id is not null
/* So basically the following query should be able to give us what is the
corresponding PO from the invoice. This is very much similar to the relation
that we have between requisition and the PO i.e by distribution id.
*/
ap_invoice_distributions_all.po_distribution_id = po_distributions_all.po_distribution_id
So if the Matching fails, then it will put the Invoice on Hold. We can run the
Invoices on Hold report to see what invoices are hold
/* PAYABLES ACCOUNTING PROCESS ;
--------------------------------
ACCRUALS :
We know that the two main accounts that get updated in Accounts payables when an invoice is
created are LIABILITY(and EXPENSE).That is when you create a stand alone AP Invoice,
with out any PO matched, where you will manually enter the expense account. The dists
can be seen from the query.
*/
select * from ap_invoice_distributions_all
/*However let us take the case of a PO received and an invoice is created by matching the PO.
When the PO is received, the following accounting entries gets generated
at the time of receiving
Receiving Inventory (Dr)
AP accrual (Cr)
Later when AP invoice is created and matched.
AP Accrual Acount Dr
Trade Payables Cr
Here one thing we need to notice is that, once you match the PO, the account is auto
matically defaulted with the accrual account from PO.
Concept of Accounting Event :
An accounting event is a payables transaction that results in an accounting impact.
So for the two document classes of Invoices and Payments, the accounting events would be
Invoices => Invoice, Invoice Adjustment, Cancellation, Prepayment application, Unapplication.
Payments => Payment,future dated payment, adjustment, cancellation, clearing,unclearing.
So having created the accounting entries we can expect the data in these two
tables with the balances being updated for the liability and the expense
accounts. We track this by using the code combination id.*/
*/
/* In the headers table we will not have invoice references.Interestingly we
would expect the gl transfer flag in the lines,but it is in the headers table here */
select * from ap_ae_headers_all
where ae_header_id = (
select distinct ae_header_id
from ap_ae_lines_all
where reference2 = 10407)
/*Here in the lines table, the invoice references are there from reference2 (invoice_id)
or from source _id */
select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr,
accounted_cr,creation_date, last_update_date
from ap_ae_lines_all
where reference2 = 10407
-- and source _id = 10467
-- and source_table in ('AP_INVOICES','AP_INVOICE_DISTRIBUTIONS')
select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr, accounted_cr
,creation_date, last_update_date
from ap_ae_lines_all
where creation_date >= trunc(sysdate)
-- and code_combination_id in (1012,4583,5933)
/*INVOICE APPROVAL WORKFLOW :
----------------------------
To setup the invoice approval workflow in AP, you need to use the Oracle
Approvals Management(AME) to define the required rules. And if you want the
HR hierarchy to be used for the AME,then you need to define the logic
accordingly in OAM.
manual holds after matching can be released.
interestingly at the payables invoice level, no inv items are specified=> and hence no lines only
distributions probably
*/
select * from ap_invoices_all
/*Payables Transfer to GL :
****************************
It is very important to understand that Payables Transfer to GL will result in failure if
the accounting combination given is invalid. What this means is the if say for the expense
account combination if you give an asset account, the payable transfer program will fail.
Another thing I noticed is that some times, the parameters set of books id, are not displayed
and hence they dont have any value,so they are failing, in which case enable and display them
and then run again.
*/
select * from gl_interface
/*Supplier Balances : Just as in AR (where the customer balances are given by the
ar_payment_schedules_all),the supplier balances are given by ap_payment_schedules_all
table. However there is no vendor id and hence the query to get the supplier
balances are given by. */
select * from po_vendors
where vendor_name = 'mysupplier' -- 57175
select invoice_id, gross_amount,amount_remaining
from ap_payment_schedules_all where invoice_id in (
select invoice_id from ap_invoices_all where vendor_id = 57175)
/*ABOUT PAYMENTS
---------------
Having created the invoice batch and invoices,we now create a payment batch
and possible give the invoice batch name as the source for this payment. The
different phases a payment will go thru are
New
Selecting
Built
Rebuilding
Formatted
Confirmed (payment batch)
Create Accounting
All these statuses we can progress thru from the Action button.
A caveat is while creating a payment batch, we have to provide the
documents which we may have to create using the bank ,branch, account form. */
/* Initially when we create a payment batch, we create a batch which corresponds
to a group of invoices from a particular supplier or from a particular pay group.
After this, from the actions button, we select "select invoices and build
payments options" and choose this action to be performed. what this means is that,
we want a payment to be made for the amount equal to the sum of all the invoices
corresponding to that particular supplier chosen or corresponding to all the
suppliers of a particular pay group.
Conveniently we can also give a invoice batch created earlier and it
would pick only the invoice corresponding to that particular batch.The requery
batch will show the different statuses like Selected,Built etc.
Also while creating a payment batch, we provide the document of payment. this is
+done as follows. We can go to the (setup,payments,banks ) and then create a
bank, bank branch and then bank account. While creating a bank account, we provide
the GL account corresponding to the cash. That is when this document is encashed,
this particular GL account is updated. So having created successfully a bank
account, we can create the payable documents, where we create a payable document
(we also provide what kind of format it is) and provide all the relevant
information.(like the starting and ending check numbers in case of check payment method)
So when this particular action is completed we do get a payment total which is equal to this sum.
and the status of the payment batch is "Built".And then we can "confirm" this batch as well ,upon
which the status changed to "Confirmed".
Now when look at the "payments" button for this particular payment batch, then
we would see the diversification of this amount into different supplier amounts.
What this means is that for each supplier how much amount is owed is provided.
In this screen we can also look at the division of this grand amount into
individual invoice amounts, by pressing the invoices button.
*/
--Actually the payment batches are called checkrun_names and they are stored in this table.
select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
start_print_document,end_print_document,first_available_document,last_update_date
from ap_inv_selection_criteria_all
order by last_update_date desc
/* Hence when we make payments, the liability account(5933) in Payables will get
debited while the cash clearing account will get credited (4583) */
/*Automatic Payment Programs :
*****************************
In Oracle Payables, we can use the "Automatic Payment Programs" window to define
the payment programs that you use to build,format payments or for creating a
remittance advice.
Oracle Payables defines 3 payment program types
1 standard program for building the payments => Build Payments ,
13 standard programs for formatting the payments => Format Payments (Standard Oracle),
1 standard program for creating a separate advice for payments => Remittance Advice.
When we define a payment format, we associate the above defined programs (for
build,format and remittance advice) and some other options.
And when we create a payables document ,we associate the above created payment
format and specify some other parameters like the min and max document numbers.
So the relation is
(Payment Formats,methods) => Payment Programs => Payables Documents
Some information about the document numbers : the document numbers can be
associated with any kind of payment method (i.e not necessarily for checks).
However let us take the simplest case of checks.
We know that in AP, we define banks, branches accounts and for any particular
bank we can define the payables documents. In the case of checks,
LAST_USED => If you are defining a new payment method,then you usually enter
the value of 1 as minimum. Let us say you already defined this document
number. After this some payment batch has used this particular payment
method. Since it has used that, the document for that payment batch will
be 1. Also during that entire process of building,formatting the payments,
no other payment batch can use this particular document. Also once any
payment batch starts using this document,it will update the last_used
column to the appropriate value(i.e to value 1) and the value is not
updatable until that batch is done processing. After that we can update
that value again.
LAST_AVAILABLE => is the document number that is the last available number,
beyond which we cannot use.
/* When you actually start creating a payment batch, the bank account information is
automatically defaulted. And it comes from the payables options level. Now to get a bank
and branch from the bank account name, run this query.*/
select * from ap_bank_accounts
WHERE bank_account_name like 'BofA_CN_INTERNAL' --'110 US 2788 BOFAUS3N USD'
--
select *
from ap_bank_branches
where bank_branch_id =48000
/*A bank account is striped by org_id i.e by the operating unit id,but not the bank branch.
That is if you have banks
in different countries, then just having a bank account in US will not do.
You should create a bank account corresponding to that particular operating
unit and then transact.
An exception is Wire method :
Wire method of payment is a type of payment which you do outside of payable system and once
payment is done then based on document sent by bank you record it in payable system and hence
payment batch is not possible with wire transfer when you enter in the system,by which time
the payment is already done and hence no need to tell the system which invoices to be selected.
*/
PAYMENT BATCHES :
----------------
begin
fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
END;
--BUILDING THE PAYMENTS :
------------------------
/*Building is the process where the selection of the invoices & building
for that batch happens.
Only invoices which match the criteria of the payment batch will be selected.
Basically we must ensure on the payment batch for the following things ,:
PAYTHRU DUE DATE, PAY GROUP, PAYMENT PRIORITY, PAYMENT METHOD*/
--Actually the payment batches are called checkrun_names and they are stored in this table.
select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
start_print_document,end_print_document,first_available_document,last_update_date
from ap_inv_selection_criteria_all
order by last_update_date desc
/*If you change the payment method code on a supplier , then all the existing
invoices/batches will not change, you will have to manually change the payment method
code. However all subsequent changes will have the new changed payment method code.*/
different payment terms on batch level and invoice level.
see and explore what exactly the pay thru date means.
-- All the invoices that are selected for the payment batch are from this table.
select * from ap_selected_invoices_all
where checkrun_name = 'mypbatch7'
order by last_update_date desc
-- We can also check what is the check number that is used for this payment here.
select *
from ap_selected_invoice_checks_all
-- where checkrun_name ='mypbatch47'
--and status_lookup_code = 'UNCONFIRMED SET UP' order by selected_check_id
order by last_update_date desc
FORMATTING THE PAYMENTS :(Very Important Step)
-------------------------
/*The next step after building the payments is formatting. As mentioned before, there are
13 different standard format programs which oracle provides. And we can create as many
as we want as well. When you run the format program, the program that is specified
in the payment method(like check etc),which you specify in the payment batch will be run.
*/
From the bank account =>
Payables document =>
document name =>
(payment format,method) =>
BUILD and FORMAT program name.
/*Usually in the case of the check payment , the remittance advice program is part of the
Format program and hence we dont need to run any separate program. However for the payment
methods like EFT, then there will be a separete remittance advice program which will be
running.
And once the payments are built,formatted and confirmed, the checks last_used value will
get updated as mentioned earlier,*/
/*Basically formatting means (nothing much happens on the back end side) it
searches for certain options and shows how the output or check will look like
and based on the parameters like stub first or last, print check stub or not
it will show us how the check looks like in the output file once the program
completes. The following query gives pretty much the formatting options,
*/
SELECT aisc.checkrun_name,
acf.separate_remittance_advice, acf.stub_first_flag,
acf.print_check_stub, aisc.check_date
FROM ap_invoice_selection_criteria aisc,
ap_check_formats acf,
ap_check_stocks acs
WHERE aisc.checkrun_name = 'mypbatch7'
AND acs.check_stock_id = aisc.check_stock_id
AND acs.check_format_id = acf.check_format_id
-- Even though the table name says check formats all the payment formats are
--stored in this table.
select stub_first_flag,print_check_stub, separate_remittance_advice,invoices_per_stub
from ap_check_formats
where name like 'Standard Check Format'
order by last_update_date desc
/* Basically check stocks is not transaction dependent. It tells you what is the
starting number and ending number */
select * from ap_check_stocks
order by last_update_date desc
/* "ap_selected_invoice_checks_all" is a temporary table that stores payment
information during a payment batch. Payables inserts into this table when
you build payments in a payment batch. There will be one row for each payment
issued during the current payment batch. When you confirm a payment batch,
your Oracle Payables application inserts these payments into"ap_checks_all"
and creates a payment file. And then it deletes from the
ap_selected_invoice_checks_all*/
select * --
FROM ap_selected_invoice_checks_all
order by last_update_date desc
/*I changed the option in the payment format from before document to after document,
and then the invoice information is printed below the check now.So the formatting
gives us with a lot of options like how the check document looks like.*/
--
select * from ap_checks_all
order by last_update_date desc
select * from ap_checkrun_conc_processes_all
select * from ap_checkrun_confirmations_all
/* Just like AR, in AP as well, the payment schedules table will have the due dates etc.
now we can see the amount remaining is zero,once the payments are made for the invoices.*/
select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code,
batch_id,org_id,inv_curr_gross_amount
from ap_payment_schedules_all
where invoice_id = 10544
--And from the form, we can check the invoice payments from
Invoices => View Payments
/*CONFIRMING THE PAYMENT BATCH
----------------------------
Basically when we format the documents at that point of time, we can print the checks.
This is how it works.
Basically we load the check documents in the printer, these check
documents will already have the numbers on them. Now when the printing of checks completes,
then we know what got printed successfully and what not. Let us say one particular check,
say 1005, is damaged ,then we can tell the system during the confirm process. That is we
choose the value "skip" and provide that number,so that system will ignore that. We also tell
which got printed successfully. After all this is done, the last_available and last_used ones
will get updated successfully.
The confirm payment batch will print separate payment document for each
supplier ,otherwise it will just print one payment document. is that true.
*/
/*ACCOUNTING FOR PAYMENT BATCHES.
We have seen that we progressed the payment batches thru building, formatting,
confirming etc (regardless of the formatting method used). Payment batches
can be accounted only after the corresponding invoice batches have been accounted
for. And payment batches can be accounted only after they have been confirmed.
*/
select *
from ap_accounting_events_all
--where event_type_code like 'PAYMENT%'
order by creation_date desc
select *
from ap_ae_headers_all
order by last_update_date desc
/*Pay Alone Invoices :
-------------------
Pay Alone Invoices : When the pay alone check box is checked at the invoice
level(which actually defaults from the supplier site level and can be changed
at the invoice level), then payables will create a separate payment document
for that invoice. That is when you build the payments it does not include
any other invoices other than a pay-alone invoice. If there is pay alone
and non pay-alone invoice, then pay alone invoice will be built first.*/
/*SINGLE PAYMENTS :
-------------------
The three different types of single payments are
Manual Payments
Quick Payments
Refunds.
One of the major difference between the single payments and batch payments is
that in the single payments you do not have the process of building the payments.*/
/*MANUAL Payments : One main functionality of the manual payments are when
the payment has already been made,then you come to the manual payments
screen and record that payment. So it is basically after the fact and
you mention the invoice for which it is paid so the invoice balance is
reduced. Since there is no invoice selection criteria and you directly
mention/enter the invoices they do not go into the table
ap_inv_selection_criteria and they go to ap_invoice_payments_all.
We can identify such payments from the following table using the invoice id,*/
/* Unlike in the case of batches, where the checks are inserted into ap_checks_all
at the time of confirming. Here at the time of creation itself the checks are
inserted into ap_checks_all,all at once. */
select payment_type_flag, payment_method_lookup_code,check_id,check_number
from ap_checks_all
where check_number= '1000009'
-- Put the above check id here to get payment/invoice information.
select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id,
invoice_id,amount,period_name ,accounting_date
from ap_invoice_payments_all
where check_id = 10480
SELECT *
FROM ap_lookup_codes
WHERE lookup_type = 'PAYMENT TYPE'
AND lookup_code = 'M'
/*Following this, for the manual payments there are no further actions, i.e there is nothing like
building ,formatting,confirming etc.It just reduces the supplier balance.
The advantage of manual payments, is that you can create a single manual payment for
multiple pay-alone invoices or you can pay a supplier who has Hold All Payments enabled. */
/*QUICK Payments : Quick Payments is a process where you quickly print a payment document
like check and send it to supplier. Basically when you create a payment,then only you
specify which invoice you are paying. The advantage is that you can select an invoice
regardless of the due date or payment terms.
However in this case, after you create a payment you will format and print the payment,but
there is no build stage.*/
select payment_type_flag, payment_method_lookup_code,check_id,check_number
from ap_checks_all
where check_number= '1000010'
-- put the above check id here to get payment/invoice information.
select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id,
invoice_id,amount,period_name ,accounting_date
from ap_invoice_payments_all
where check_id = 10500
SELECT *
FROM ap_lookup_codes
WHERE lookup_type = 'PAYMENT TYPE'
AND lookup_code = 'Q'
-- GL TRANSFER :
/*Now we need to transfer these invoices and payments transactions to the General Ledger.
Typically this happens in stages. The steps involved are
"Payables Transfer to GL",
"Journal Import" and
"Posting to General Ledger".
When we run the "Payables Transfer to GL", the data gets moved into the GL_INTERFACE table,and here
we can optionally mention to do the Journal Import as well. Hence after the Journal Import is done,we
can check the data in the following tables.*/
select * from gl_je_headers
select * from gl_je_batches
where creation_date = (select max(creation_date) from gl_je_batches)
select max(creation_date) from gl_je_lines
/* Generally while running the Posting Process i.e "Automatic Posting", the program will ask for
the autopost set id. For this we go to the form "Autopost Criteria Sets" and create a criteria
set by providing the priority,source,
category, balance type and period columns.
Having created a criteria set, we can then run the conc program (we can also run from the same form)
by providing this autopost set id */
select *
from gl_automatic_posting_sets
/* Hence after Posting process is completed we can check the gl_balances table which will contain most
importantly ,only the summarized information. */
SELECT *
FROM gl_balances
wHERE code_combination_id =4583
order by last_update_date desc
/*Use the Payables func to print a check. A payment document should need only
one document number right?
--why is that it needs so many document numbers from last used to last available
numbers.*/
/*
You need create receipt first and invoice later. If you created invoice and match
to po the invoice will be on hold as no receipts are created.
*/
/* PREPAYMENTS IN ACCOUNTS PAYABLES :
There are two kinds of prepayments => Temporary and Permanent.
Temporary prepayments are those which you apply to an invoice.
Perf
The way you deal with the prepayments in AP is that, you first create a prepayment invoice for a
particular supplier and make a payment for that prepayment invoice. Then whenever you are entering
an invoice for that supplier ,the system will prompt you saying that there is a prepayment against
this supplier.
1) First create a Prepayment invoice,make sure it is temporary, and provide a settlement date.
2) Make a payment for this prepayment invoice by doing this
Actions => Pay in Full
(why the prepay invoices needs to paid in full,why not partial?)
3) Now create a Standard Invoice for the same above supplier. when you tab out of the supplier window,
the system prompts you saying that, there is a prepayment for this supplier.
4) Next there are two ways, a prepayment can be applied to the supplier invoice.
* First you pull up the prepayment invoice and click on the
Actions=> Apply/Unapply Payments.
now this will give all the eligible invoices for that supplier and you can apply to one or
more than one number of invoices.
* Second,you pull up the Supplier invoice created in the step 3 and choose
Actions => Apply/Unapply Payments.
In this case also the screen looks very similar with the exception that you have an
additional check box "Prepayment On Invoice". If you check this, that what it means is
that the supplier invoice has included the prepayment amount as well
in the invoice amount. The only difference in both the ways of application is in the
creation of accounting distributions.
(Prepayment on Invoice checkbox :ticking the 'Prepayment on Invoice' checkbox when you
Apply the prepayment? This will create a new negative distribution line on the invoice
and reduce the overall invoice distribution total accordingly)
*/
select aia.invoice_id,invoice_date,invoice_type_lookup_code, aida.prepay_distribution_id,
prepay_amount_remaining
from ap_invoices_all aia, ap_invoice_distributions_all aida
where aia.invoice_id = aida.invoice_id
and aia.invoice_num like '08-MAR-2007'
select * from ap_payment_bsets_all
/* In AP, you cannot create payments without associating them with the invoice. That is ,without
a supplier invoice which has an open balance,you cannot create payments.
*/
select * from ap_invoice_prepays_all where last_update_date > sysdate -100
select * from ap_payment_history_all where last_update_date > sysdate -1
/*Payables & Cash Management => If the cash clearing needs to work, then
you need to give the correct cash clearing account information in the
bank account as wells as the payables documents level. then it will hit
the cash clearing account when a payment is account. further when you use
the cash management you can reconcile the bank statements with the cash
clearing account.
*/
Recurring Invoices :
--------------------
Generally a supplier sends a invoice and we enter it into our system. However
sometimes we can also generate a invoice on behalf of supplier. This can be done
by recurring invoice. A recurring invoice works on the line of recurring journals
in GL. It works in two steps,
Recurring Invoice template Definition
Recurring invoice creation.
Some of the important things that are needed in the template definition are
Special calendar => needs to be defined,with months,quarters etc.
Distribution source => get it from the distribution set or from PO.
About the calendar : Let us say if there is calendar consisting of 12 periods
from January to December.
And if you want first invoice period to be ,say , May. Then the next period
will be populated as May. Once the invoice has been generated, the next
period will be Jun. So at any point of time, we can see what will be the
next recurring invoice period. (The fields next invoice period and the
next amount indicate are non-editable fields).
There are two special amounts and periods, that means, on that period,
the invoice amount will be that amount.
Once a recurring invoice has been generated, you cannot change any fields
in the recurring template,other than the account distribution.
Do we need to come to the template everytime and run the recurring invoice
isn't that cumbersome ??
Payables Credit Memo, Debit Memo :
----------------------------------
A credit memo is a document sent by the supplier which decreases the supplier balance.
It is a negative amount and is applied by matching to an supplier invoice.
A debit memo is a document which you create and send it to the supplier for their
reference. It is also a negative amount and is applied by matching to an supplier invoice
and it decreases the supplier balance.
Pay group :
Pay group is a look up code defined in the purchasing application.
Q & A
------
Receiving Transactions processor should be running.
/*Payables Invoice Import Process
_______________________________
The following process goes thru the flow of generating the invoices (along with the underlying table updates)
until the ultimate GL Posting Process.
Invoices Testing Queries :
-- Set the Environment */
begin
fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
end;
-- Run the invoice_test.sql to populate the invoices into the ap_invoices_interface table.
select status,source,count(*)
from ap_invoices_interface
where source = 'WW-Real Estate-Payments'
group by source,status
-- Run the concurrent program "Payables Open Interface Import", with the invoice batch name as any name.
The import program will create a record in the ap_batches_all
-- You can keep checking the query #1 whether the invoices are processed or not in the source table.
-- We can also check in the destination table after setting the env, by running the following query.
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,
count(*)
from ap_invoices -- (or ap_invoices_all)
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
-- Get one such invoice imported into ap_invoices
select invoice_id,approval_status, posting_status ,wfapproval_status,
to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') creation_date,
to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS') last_update_date
from ap_invoices
where creation_date = (select max(creation_date) from ap_invoices)
and rownum < 2
-- The Following programs are scheduled in SJPRF to run for every 10 minutes.
/* "Invoice Validation" Concurrent Program.(APPRVL) (Param: Process all matching lines)
If the approval_status_lookup_code = 'APPROVED', THEN the status is 'VALIDATED'. if the
approval_status_lookup_code is 'NEEDS REAPPROVAL', then the status is 'needs revalidation'
*/
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'), count(*)
from ap_invoices_v
where trunc(creation_date) = trunc(sysdate)
and approval_status_lookup_code = 'APPROVED'
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select invoice_id,approval_status, posting_status ,wfapproval_status,approval_status_lookup_code
from ap_invoices_v
where invoice_id = 5354999
-- Run the "Payables Accounting Process" Concurrent program. (Param: Validate Accounts : NO).
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_ae_headers_all where trunc(creation_date) =trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_ae_lines_all where trunc(creation_date) =trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
/* "Workflow Background Process" for invoice approval. This is already enabled for every 30 sec in SJPRF.
OR "Invoice Approval Workflow" needs to be run.
--Before approval, the below query should give the import count# */
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_invoices_v
where trunc(creation_date) =trunc(sysdate)
and wfapproval_status = 'WFAPPROVED'
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
-- After approval process, the above query should give 0 records.
/* "Cisco Payables GL Transfer Post Program" ap_invoice_distributions_all (posted_flag),
(gl_interface, gl_je_batches, gl_balances.)
After the feeder systems (i.e AP,AR ) transfer the data to GL, they first go to gl_interface table and then
from there , they are moved to the gl_je tables. Once these entries are posted, they will update the
gl_balances table.
So this program essentially kicks off
Payables Transfer to GL
Journal Import
Automatic Posting
Hence it is important we need to have US GL Super User resposibility assigned to ourself
before we kick off this program.
*/
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*)
from gl_je_headers
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*)
from gl_je_lines
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select table_name,num_rows from dba_tables where table_name in
('AP_AE_LINES_ALL')
/* Once the invoices are imported into the ap_invoices_all , ap_invoice_distributions_all tables from
ap_invoices_interface and ap_invoice_lines_interface, we can also look at them from the applications
either in the invoice form or look at all the invoices in
Invoice=>Invoices=>Open Interface Invoices. */
/*
Quick Invoices & Invoices Workbench:
Quick invoices window will not do much of the validation and do not default the values.
Also this information will go into the ap_invoices_interface table,after which we can run the
invoice import program to import them in AP.
For manually entered invoices,when we validate them, does it do matching and if it does,where is
the PO information.
*/
--
For invoice matching is the PO really required, just by the supplier id, cant the
system go and find the related PO and match them. so we found that for the matching
to succeed we really need a PO for the invoice,otherwise it fails.
--
regardless of the po, for each receipt, you generally have one AP invoice.
--
you can receive any amount lesser than the PO line amount, however the over-receipt
tolerance amount pertains to the scenaroi,when u r receiving more than the po line amount.
--
The only way you can enter a quantity invoiced amount in the invoice distribution is
to match it to a po line /shipment.
then the quantity invoiced is defaulted to the value of the po line qnty requested
You could edit that value if you want to and change it.
--
so basically in matching the invoice distributons are matched to the PO lines/distributions
or receiving distributions based on 2 way or 3 way matching.
--- QUESTIONNAIRE :
1) Is there a tax hiearchy in AP as well ???
Yes. Just like in AR,here too we define tax code(from tax types) and tax codes
can be specified at different places like Supplier, site, payables options,
invoice header, template. And the hierachy of preference can be found in the
Financial options.
Howeer I did not find the location tax code in the tax code in the payables. This
is probably because the company is not taxing ;it is supplier who will have to
apply the taxes.
2) What is a voucher and document sequence ??
Since two suppliers can provide you two invoices which might have the same invoice
number, in which case, invoice number cannot be unique. Hence you have to create
a voucher number within your system for these two invoices, so that you can
uniquely identify them.
Also we can assign a document sequence for each document category with in the
Oracle applcations system. And so you can for a AP invoice.Simple steps are
There are already predefined document categories like AP invoice, AP credit memo,
so no need to create anything new
Create a new sequence with starting number, etc.
Assign the above sequence to a particular
(Application, Category, Set of Books, Start date)
Ensure that Sequential Numbering profile option is aptly set.
3) What happens in these matching scenarios ??
PO , 2 -way => No confusion here, 2-way matching is performed,
Receipt, 3-way => No confusion here, 3-way matching is performed.
PO, 3-way => what will happen here, 2-way matching or 3-way matching with receipts
Receipt, 2-way => what will happen here , 3-way matching or matching with PO's.
Also between, 2 way vs 3 way matching, which one is used in what circumstances
and what are the advantages of one over the other? when both of them can be
used, which is preferred ? is it like the more receipts for a PO, better 3,4 way matching?
4)What are credit memos and debit memos in Payables ??
A credit memo is a document sent by the Supplier which you will apply to
an invoice and which reduces the invoice balance.
A debit memo is a document which you will create ,which will also reduce the
invoice balance,and send it tto the supplier for verification,approval.
5) Should every invoice in Payables be a part of an invoice batch?
If the profile option "AP: Use Invoice Batch Control" is set to yes, then you can
create an invoice only after you create a invoice batch.
If the above profile option is set to No, the you can create a standalone invoice,
without being part of a batch.
6) Should every payment be a part of a payment batch?
No.
Payments => Entry => Batches ; Payment batches go thru the build,format and confirmation.
Payments=> Entry => Payments ; If you enter a standalone payments, they are called
single payments and they are of 3 types (Quick,Manual,Refund).
7)What is the difference between online Matching and offline Matching????
When you are entering online invoice in Invoice workbench, the only way you
can enter an invoice quantity (Match Quantity) is by matching it to a PO line,upon
which the invoice quantity is defaulted from the PO line,which you can change.
So you cannot manually type in the invoice quantity in the Invoice distribution
with out matching.
However if we manually create an invoice line which is identical to a PO line then
run matching,then the system just matches and validates it successfully.
what is that so & why there is no invoice qty field in the distribution form
8) What are the steps required to void a check in AP ?
9) Difference between suppliers and vendors ??
I believe vendor is a type of supplier. This is bcoz, when you choose supplier type,
you have a option of vendor there.The different kinds
of suppliers are government, federal ,Mfg.
Also Employee is a type of Supplier.
try to see where you can define supplier types.
10) For manually entered invoices,when we validate them, does it do matching and if it does,where
is the PO information??
Yes it does matching and its puts on hold if there is no corresponding matching. In such
case,you can release the hold manually.
11).When I go to the view accounting from the receiving transaction, i am not able to see the accounting
entry for the accrual account??
Firstly ensure that you have accrue on receipt. And then if you do that, then the
accounting lines are automatically xferred to GL and can be seen
in gl_interface.
12).In Oracle 11i, there is no centralized payment systems ??
which means that you just cannot have a
bank defined in one country and send payments from that bank. You need to define banks in each
operating unit. So it is understandable bank is tied to an operating unit/ SOB id.
When we build the payments, it is important that you can only give one type of payment types
i.e you cannot build corresponding to CHECK and ACH with in a payment batch.
ABOUT INVOICES
--------------
/*ACCOUNTING METHOD , ACCRUAL OR CASH :
So do you set the accounting method only at the Payables,Receivables levels,
not at the GL Level. I believe so,because of those settings,payables and
receivables will generate the journal entries accordingly.
When we create an invoice we initially create a invoice batch and then create
invoices in that batch. While creating invoice batch, we provide the control amount
and control count (the intial batch amount and intial batch count),payment terms,
type of invoice(like PO default, standard,CM,DM etc), pay group. These invoices go into
the "ap_batches_all" table. However some of the above fields are optional at the batch
level and can be specific at the invoice level.
Having entered an invoice batch, we enter the invoices in that batch and enter
the supplier information currency. Here the values mentioned at the batch level
will default at the invoice level which can be changed at the invoice level. All
the invoices go into the "ap_invoices_all" table.
Each invoice will have distributions and each distribution (which could be either
item, tax, frieght charge) will correspond to a particular gl_account and will
be stored in "ap_invoice_distributions_all" with the corresponding code_combination_id
in the table.
Having created an invoice the invoice needs to be validated,payables accounting
process and approved(may not be required). For validating an invoice it is
important that the control amount at the batch level sums up to the sum of the
individual amounts of the invoices. The control count at the batch level should
be equal to the number of invoices in that batch.
The invoice can be validated by pressing the validate button.
Make sure there are no holds on that invoice.i.e holds are 0.
And only the validated invoices will be paid or payment applied.
As mentioned before, the invoice is distributed on item,tax,freight etc), where
we mentioned for each distribution which account it should go to i.e the item should
go to account1 and tax should go to another tax related account.
Or we can create a distribution set where we mention the percentages of each item,
say the 70% of the invoice item should go to the account1 and 30% to account2.etc
and assign that distribtuon set to that supplier.
once this is done, for every invoice corresponding to that supplier ,if we select
that distribution set, the above %'s are automatically applied.
One feature of the Oracle Payables is that, the AP can also treat the employees
in a particular company as suppliers (i.e typically the suppliers send the invoices
which are paid for) similarly the employees should also be paid for the services
they render every pay period and what should be the employees payment terms.
Accounting in Payables Transactions.
There are two document classes in Accounts Payables which can give rise to accounting
transactions.
Invoice (creating Invoices)
Payment (Issuing Payments).
Entering an Invoice :
An invoice increases the suppliers account balance by the invoice amount.
Once the invoice is completed,
The corresponding journal entries will be posted to the General Ledger in the
next GL transfer. As an ex, the following are the entries created for an invoice
entered for amount $200. Hence the three main accounts that are updated in an
invoice entry are
Account Derivation DR CR
-------- --------------------------------------------------------
Liability : Defaulted from the Supplier site ,otherwise can be entered : $235
at the invoice batch level
Expense/ Asset : Defaulted from the Purchase Order,otherwise entered at the : $200
invoice level.
Tax : Defaulted from tax name,can be overwritten during invoice. : $35
*/
select batch_id,batch_name,invoice_currency_code,payment_currency_code
terms_id,gl_date,invoice_type_lookup_code,org_id
from ap_batches_all
where batch_name ='mybatch9'
select * from ap_terms_lines -- ap_terms_tl
where terms_id = 1205
/* Actually there is a payment term specified at the batch level and as well as the invoice
level. However invoice level take precedence. When you dont mention anything at the
invoice level/batch level, the term from supplier will default. Since invoices come from
suppliers, there is a chance that two invoices from two different suppliers might have
the same invoice number, and hence internally we assign a voucher number. For the same
supplier u cannot have two invoices with the same invoice number.*/
SELECT * FROM ap_invoices_all
WHERE 1 =1
and batch_id = 10065
--and invoice_num = 'myinv1'
--where invoice_id = 52989
-- Just like AR, in AP as well, the payment schedules table will have the due dates etc.
--Even without any payments,once an invoice is created, it will figure in this table.
select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code,
batch_id,org_id,inv_curr_gross_amount
from ap_payment_schedules_all
where invoice_id = 10544
SELECT * --dist_code_combination_id
FROM ap_invoice_distributions_all
WHERE invoice_id = 10663
/* While creating an invoice batch we can provide the liability account and
while creating the invoice we provide the expense accounts for the main item
and frieght or tax purposes (called distributions) */
select * from gl_code_combinations -- 1012
where segment1 =01
and segment2 = 0000
and segment3 = 0000
and segment4 in (73100,67170,21330)
and segment5 = 0000
and segment6 = 0000
and segment7 = 0000
and segment8 = 0000
/*Default Expense or Charge Account for AP Invoice , Distribution Sets:
We know that the default liability account will come from the Financials Options and which is
shown at the invoice header level(liability account field can be sometimes hidden and hence
should be unhidden.
There is also an expense account which is at the invoice distribution level. In general
this has to be entered manually at the dist level. However this can be defaulted by defining
a distribution set and associating that distribution set at the supplier site level. Hence it is
important that DISTRIBUTION SETS ARE ONLY FOR CHARGE ACCOUNTS NOT LIABILITY ACCOUNT.
*/
select *
from ap_invoice_distributions_all
/*To approve or generate accounting entries online , ensure that the following setting is done,
setup => options => payables => invoice tab => Allow Online Validation. */
/*Hence after this is done, we want to transfer all this information into the GL,
because it is a ledger system. AP,AR,Assets are the feeder systems(or subledgers)
in financials and they will be transferring the txn's to GL to reflect the accounts
correctly. From payables to GL, this can be done by "Payables Transfer to GL" program.
So the various steps the payables invoices will go thru before they are transferred
to GL are given below
*/
Invoice Validation,
Payables Accounting Process,
Invoice Approval workflow and then
Payables Transfer to gl(which involves payables transfer,journal import and posting).
/*So once the payables txns are transferred (gl_je_headers and gl_je_lines) tables
are populated and when the txns are posted, the gl_balances tables is updated.
The gl_balances tables is a huge table which consists of an entry for each account
or code_combination_id, when the txns are posted, the corresponding accounts in the
invoice distributions are updated accordingly.*/
/* ACCOUNT GENERATOR Role in Payables & Projects :
Just want to talk about ,what is the role of account generator, say while
creating an invoice, For ex;liability and expense account are already specified
as part of the system options/distribution sets and hence it has to copy it
straight from that.
What I mean to say is that unlike AutoAccounting in AR(or FA),which has to build each
segment and the combination, the Account Generator in AP does not have to build
each segment,correct? The only place where I saw account generator work, is when
you specify a project information at the invoice distribution level, then it will
rebuild the distribution. Other than that, the Account generator does not really
have to build segments of the code combination, other than copy from the options.
So for ex, if you specify project information in the distribution level, there is
a standard workflow functionality to build the combination. However even this
can be customized by developing our own workflow functionality and specifying at
this location, (from the General Ledger responsibility)
Setup => Financials => Flexfields => Key => Accounts
This will open the form "Account Generator Processes",query for the application
"General Ledger" and the flexfield title "Accounting Flexfield" and for your
corresponding structure. Here you can see for the item type "Project Supplier
Invoice Account Generation", what is the process name. THe default value is
"Generate Default Account",which is the standard value. Any customized workflow
process can be specified here.
INVOICE VALIDATION :
--------------------
/*The Invoice validation program does a bunch of things when it starts validation,like ,
Matching
Tax
Distribution
Period Status
Exchange Rate
so while it is matching the invoice, what kind of matching it uses 2-way,3-way
or 4-way. Typically when we create an invoice we provide the supplier and supplier
site information from where we are receiving that invoice. So the system will
decide how to do the Matching based on the setting at the supplier site level.
If at the supplier site, the Invoice Match Option is set to Purhcase Order,then at the
time of validation, the invoice is matched against the purchase order.
Another interesting point is that, when we create a purchase order, at the
distributions the invoice match option is also specified which basically defaults
from the supplier site,however we can change that accordingly.
Hence as we see, there are two important parameters
Invoice Match Option : PO (or Receipt)
Match Approval Level : 2-Way (3-Way or 4-Way)
2-Way : Invoice Quantity <= PO Quantity ; Invoice Price <= PO Price;
3-Way : 2-Way + Invoice Quantity <= Receipt Quantity (by the tolerance level)
4-Way : 3-Way + Invoice Quantity <= Accepted Quantity (by the tolerance level)
(Interestingly there is also a Invoice Match Option that you can set at the Financials Options
Setup => Options => Financials Options )
The different kinds of scenarios that are available are
We can have 1 PO , 1 Receipt => 1 Invoice
We can have 1 PO , 5 Receipts => 5 Invoice
*/
-- The validation flag gets updated in the ap_invoices_all table
select * from ap_invoices_all -- approval_status?
-- Invoice Matching with Purchase Order and 3-way Matching.
/*
Generally, when an invoice is raised and there is a corresponding matching
PO, then the po_distribution_id column in the ap_invoice_distribution will
be not null, and that is the best way to identify invoices that have been
matched with PO, even though there is a column by name match_status_flag.
Once an invoice is raised against this PO, the quantity billed and the amount
billed columns will get updated with the invoice prices).
*/
select a.quantity_invoiced,a.unit_price ,c.quantity ,c.unit_price,
c.unit_price - a.unit_price price_difference
from ap_invoice_distributions_all a,
po_distributions_all b,
po_lines_all c
where a.po_distribution_id = b.po_distribution_id
and b.po_line_id = c.po_line_id
and a.accounting_date between trunc(sysdate -1) and trunc(sysdate-1) + 0.99999 --- 60354
and a.unit_price < c.unit_price
and a.po_distribution_id is not null
/* So basically the following query should be able to give us what is the
corresponding PO from the invoice. This is very much similar to the relation
that we have between requisition and the PO i.e by distribution id.
*/
ap_invoice_distributions_all.po_distribution_id = po_distributions_all.po_distribution_id
So if the Matching fails, then it will put the Invoice on Hold. We can run the
Invoices on Hold report to see what invoices are hold
/* PAYABLES ACCOUNTING PROCESS ;
--------------------------------
ACCRUALS :
We know that the two main accounts that get updated in Accounts payables when an invoice is
created are LIABILITY(and EXPENSE).That is when you create a stand alone AP Invoice,
with out any PO matched, where you will manually enter the expense account. The dists
can be seen from the query.
*/
select * from ap_invoice_distributions_all
/*However let us take the case of a PO received and an invoice is created by matching the PO.
When the PO is received, the following accounting entries gets generated
at the time of receiving
Receiving Inventory (Dr)
AP accrual (Cr)
Later when AP invoice is created and matched.
AP Accrual Acount Dr
Trade Payables Cr
Here one thing we need to notice is that, once you match the PO, the account is auto
matically defaulted with the accrual account from PO.
Concept of Accounting Event :
An accounting event is a payables transaction that results in an accounting impact.
So for the two document classes of Invoices and Payments, the accounting events would be
Invoices => Invoice, Invoice Adjustment, Cancellation, Prepayment application, Unapplication.
Payments => Payment,future dated payment, adjustment, cancellation, clearing,unclearing.
So having created the accounting entries we can expect the data in these two
tables with the balances being updated for the liability and the expense
accounts. We track this by using the code combination id.*/
*/
/* In the headers table we will not have invoice references.Interestingly we
would expect the gl transfer flag in the lines,but it is in the headers table here */
select * from ap_ae_headers_all
where ae_header_id = (
select distinct ae_header_id
from ap_ae_lines_all
where reference2 = 10407)
/*Here in the lines table, the invoice references are there from reference2 (invoice_id)
or from source _id */
select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr,
accounted_cr,creation_date, last_update_date
from ap_ae_lines_all
where reference2 = 10407
-- and source _id = 10467
-- and source_table in ('AP_INVOICES','AP_INVOICE_DISTRIBUTIONS')
select ae_header_id, ae_line_id, code_combination_id,entered_dr, accounted_dr,entered_cr, accounted_cr
,creation_date, last_update_date
from ap_ae_lines_all
where creation_date >= trunc(sysdate)
-- and code_combination_id in (1012,4583,5933)
/*INVOICE APPROVAL WORKFLOW :
----------------------------
To setup the invoice approval workflow in AP, you need to use the Oracle
Approvals Management(AME) to define the required rules. And if you want the
HR hierarchy to be used for the AME,then you need to define the logic
accordingly in OAM.
manual holds after matching can be released.
interestingly at the payables invoice level, no inv items are specified=> and hence no lines only
distributions probably
*/
select * from ap_invoices_all
/*Payables Transfer to GL :
****************************
It is very important to understand that Payables Transfer to GL will result in failure if
the accounting combination given is invalid. What this means is the if say for the expense
account combination if you give an asset account, the payable transfer program will fail.
Another thing I noticed is that some times, the parameters set of books id, are not displayed
and hence they dont have any value,so they are failing, in which case enable and display them
and then run again.
*/
select * from gl_interface
/*Supplier Balances : Just as in AR (where the customer balances are given by the
ar_payment_schedules_all),the supplier balances are given by ap_payment_schedules_all
table. However there is no vendor id and hence the query to get the supplier
balances are given by. */
select * from po_vendors
where vendor_name = 'mysupplier' -- 57175
select invoice_id, gross_amount,amount_remaining
from ap_payment_schedules_all where invoice_id in (
select invoice_id from ap_invoices_all where vendor_id = 57175)
/*ABOUT PAYMENTS
---------------
Having created the invoice batch and invoices,we now create a payment batch
and possible give the invoice batch name as the source for this payment. The
different phases a payment will go thru are
New
Selecting
Built
Rebuilding
Formatted
Confirmed (payment batch)
Create Accounting
All these statuses we can progress thru from the Action button.
A caveat is while creating a payment batch, we have to provide the
documents which we may have to create using the bank ,branch, account form. */
/* Initially when we create a payment batch, we create a batch which corresponds
to a group of invoices from a particular supplier or from a particular pay group.
After this, from the actions button, we select "select invoices and build
payments options" and choose this action to be performed. what this means is that,
we want a payment to be made for the amount equal to the sum of all the invoices
corresponding to that particular supplier chosen or corresponding to all the
suppliers of a particular pay group.
Conveniently we can also give a invoice batch created earlier and it
would pick only the invoice corresponding to that particular batch.The requery
batch will show the different statuses like Selected,Built etc.
Also while creating a payment batch, we provide the document of payment. this is
+done as follows. We can go to the (setup,payments,banks ) and then create a
bank, bank branch and then bank account. While creating a bank account, we provide
the GL account corresponding to the cash. That is when this document is encashed,
this particular GL account is updated. So having created successfully a bank
account, we can create the payable documents, where we create a payable document
(we also provide what kind of format it is) and provide all the relevant
information.(like the starting and ending check numbers in case of check payment method)
So when this particular action is completed we do get a payment total which is equal to this sum.
and the status of the payment batch is "Built".And then we can "confirm" this batch as well ,upon
which the status changed to "Confirmed".
Now when look at the "payments" button for this particular payment batch, then
we would see the diversification of this amount into different supplier amounts.
What this means is that for each supplier how much amount is owed is provided.
In this screen we can also look at the division of this grand amount into
individual invoice amounts, by pressing the invoices button.
*/
--Actually the payment batches are called checkrun_names and they are stored in this table.
select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
start_print_document,end_print_document,first_available_document,last_update_date
from ap_inv_selection_criteria_all
order by last_update_date desc
/* Hence when we make payments, the liability account(5933) in Payables will get
debited while the cash clearing account will get credited (4583) */
/*Automatic Payment Programs :
*****************************
In Oracle Payables, we can use the "Automatic Payment Programs" window to define
the payment programs that you use to build,format payments or for creating a
remittance advice.
Oracle Payables defines 3 payment program types
1 standard program for building the payments => Build Payments ,
13 standard programs for formatting the payments => Format Payments (Standard Oracle),
1 standard program for creating a separate advice for payments => Remittance Advice.
When we define a payment format, we associate the above defined programs (for
build,format and remittance advice) and some other options.
And when we create a payables document ,we associate the above created payment
format and specify some other parameters like the min and max document numbers.
So the relation is
(Payment Formats,methods) => Payment Programs => Payables Documents
Some information about the document numbers : the document numbers can be
associated with any kind of payment method (i.e not necessarily for checks).
However let us take the simplest case of checks.
We know that in AP, we define banks, branches accounts and for any particular
bank we can define the payables documents. In the case of checks,
LAST_USED => If you are defining a new payment method,then you usually enter
the value of 1 as minimum. Let us say you already defined this document
number. After this some payment batch has used this particular payment
method. Since it has used that, the document for that payment batch will
be 1. Also during that entire process of building,formatting the payments,
no other payment batch can use this particular document. Also once any
payment batch starts using this document,it will update the last_used
column to the appropriate value(i.e to value 1) and the value is not
updatable until that batch is done processing. After that we can update
that value again.
LAST_AVAILABLE => is the document number that is the last available number,
beyond which we cannot use.
/* When you actually start creating a payment batch, the bank account information is
automatically defaulted. And it comes from the payables options level. Now to get a bank
and branch from the bank account name, run this query.*/
select * from ap_bank_accounts
WHERE bank_account_name like 'BofA_CN_INTERNAL' --'110 US 2788 BOFAUS3N USD'
--
select *
from ap_bank_branches
where bank_branch_id =48000
/*A bank account is striped by org_id i.e by the operating unit id,but not the bank branch.
That is if you have banks
in different countries, then just having a bank account in US will not do.
You should create a bank account corresponding to that particular operating
unit and then transact.
An exception is Wire method :
Wire method of payment is a type of payment which you do outside of payable system and once
payment is done then based on document sent by bank you record it in payable system and hence
payment batch is not possible with wire transfer when you enter in the system,by which time
the payment is already done and hence no need to tell the system which invoices to be selected.
*/
PAYMENT BATCHES :
----------------
begin
fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
END;
--BUILDING THE PAYMENTS :
------------------------
/*Building is the process where the selection of the invoices & building
for that batch happens.
Only invoices which match the criteria of the payment batch will be selected.
Basically we must ensure on the payment batch for the following things ,:
PAYTHRU DUE DATE, PAY GROUP, PAYMENT PRIORITY, PAYMENT METHOD*/
--Actually the payment batches are called checkrun_names and they are stored in this table.
select checkrun_name,bank_account_name,status, payment_method_lookup_code,org_id,
start_print_document,end_print_document,first_available_document,last_update_date
from ap_inv_selection_criteria_all
order by last_update_date desc
/*If you change the payment method code on a supplier , then all the existing
invoices/batches will not change, you will have to manually change the payment method
code. However all subsequent changes will have the new changed payment method code.*/
different payment terms on batch level and invoice level.
see and explore what exactly the pay thru date means.
-- All the invoices that are selected for the payment batch are from this table.
select * from ap_selected_invoices_all
where checkrun_name = 'mypbatch7'
order by last_update_date desc
-- We can also check what is the check number that is used for this payment here.
select *
from ap_selected_invoice_checks_all
-- where checkrun_name ='mypbatch47'
--and status_lookup_code = 'UNCONFIRMED SET UP' order by selected_check_id
order by last_update_date desc
FORMATTING THE PAYMENTS :(Very Important Step)
-------------------------
/*The next step after building the payments is formatting. As mentioned before, there are
13 different standard format programs which oracle provides. And we can create as many
as we want as well. When you run the format program, the program that is specified
in the payment method(like check etc),which you specify in the payment batch will be run.
*/
From the bank account =>
Payables document =>
document name =>
(payment format,method) =>
BUILD and FORMAT program name.
/*Usually in the case of the check payment , the remittance advice program is part of the
Format program and hence we dont need to run any separate program. However for the payment
methods like EFT, then there will be a separete remittance advice program which will be
running.
And once the payments are built,formatted and confirmed, the checks last_used value will
get updated as mentioned earlier,*/
/*Basically formatting means (nothing much happens on the back end side) it
searches for certain options and shows how the output or check will look like
and based on the parameters like stub first or last, print check stub or not
it will show us how the check looks like in the output file once the program
completes. The following query gives pretty much the formatting options,
*/
SELECT aisc.checkrun_name,
acf.separate_remittance_advice, acf.stub_first_flag,
acf.print_check_stub, aisc.check_date
FROM ap_invoice_selection_criteria aisc,
ap_check_formats acf,
ap_check_stocks acs
WHERE aisc.checkrun_name = 'mypbatch7'
AND acs.check_stock_id = aisc.check_stock_id
AND acs.check_format_id = acf.check_format_id
-- Even though the table name says check formats all the payment formats are
--stored in this table.
select stub_first_flag,print_check_stub, separate_remittance_advice,invoices_per_stub
from ap_check_formats
where name like 'Standard Check Format'
order by last_update_date desc
/* Basically check stocks is not transaction dependent. It tells you what is the
starting number and ending number */
select * from ap_check_stocks
order by last_update_date desc
/* "ap_selected_invoice_checks_all" is a temporary table that stores payment
information during a payment batch. Payables inserts into this table when
you build payments in a payment batch. There will be one row for each payment
issued during the current payment batch. When you confirm a payment batch,
your Oracle Payables application inserts these payments into"ap_checks_all"
and creates a payment file. And then it deletes from the
ap_selected_invoice_checks_all*/
select * --
FROM ap_selected_invoice_checks_all
order by last_update_date desc
/*I changed the option in the payment format from before document to after document,
and then the invoice information is printed below the check now.So the formatting
gives us with a lot of options like how the check document looks like.*/
--
select * from ap_checks_all
order by last_update_date desc
select * from ap_checkrun_conc_processes_all
select * from ap_checkrun_confirmations_all
/* Just like AR, in AP as well, the payment schedules table will have the due dates etc.
now we can see the amount remaining is zero,once the payments are made for the invoices.*/
select invoice_id, gross_amount, amount_remaining, due_date,payment_method_lookup_code,
batch_id,org_id,inv_curr_gross_amount
from ap_payment_schedules_all
where invoice_id = 10544
--And from the form, we can check the invoice payments from
Invoices => View Payments
/*CONFIRMING THE PAYMENT BATCH
----------------------------
Basically when we format the documents at that point of time, we can print the checks.
This is how it works.
Basically we load the check documents in the printer, these check
documents will already have the numbers on them. Now when the printing of checks completes,
then we know what got printed successfully and what not. Let us say one particular check,
say 1005, is damaged ,then we can tell the system during the confirm process. That is we
choose the value "skip" and provide that number,so that system will ignore that. We also tell
which got printed successfully. After all this is done, the last_available and last_used ones
will get updated successfully.
The confirm payment batch will print separate payment document for each
supplier ,otherwise it will just print one payment document. is that true.
*/
/*ACCOUNTING FOR PAYMENT BATCHES.
We have seen that we progressed the payment batches thru building, formatting,
confirming etc (regardless of the formatting method used). Payment batches
can be accounted only after the corresponding invoice batches have been accounted
for. And payment batches can be accounted only after they have been confirmed.
*/
select *
from ap_accounting_events_all
--where event_type_code like 'PAYMENT%'
order by creation_date desc
select *
from ap_ae_headers_all
order by last_update_date desc
/*Pay Alone Invoices :
-------------------
Pay Alone Invoices : When the pay alone check box is checked at the invoice
level(which actually defaults from the supplier site level and can be changed
at the invoice level), then payables will create a separate payment document
for that invoice. That is when you build the payments it does not include
any other invoices other than a pay-alone invoice. If there is pay alone
and non pay-alone invoice, then pay alone invoice will be built first.*/
/*SINGLE PAYMENTS :
-------------------
The three different types of single payments are
Manual Payments
Quick Payments
Refunds.
One of the major difference between the single payments and batch payments is
that in the single payments you do not have the process of building the payments.*/
/*MANUAL Payments : One main functionality of the manual payments are when
the payment has already been made,then you come to the manual payments
screen and record that payment. So it is basically after the fact and
you mention the invoice for which it is paid so the invoice balance is
reduced. Since there is no invoice selection criteria and you directly
mention/enter the invoices they do not go into the table
ap_inv_selection_criteria and they go to ap_invoice_payments_all.
We can identify such payments from the following table using the invoice id,*/
/* Unlike in the case of batches, where the checks are inserted into ap_checks_all
at the time of confirming. Here at the time of creation itself the checks are
inserted into ap_checks_all,all at once. */
select payment_type_flag, payment_method_lookup_code,check_id,check_number
from ap_checks_all
where check_number= '1000009'
-- Put the above check id here to get payment/invoice information.
select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id,
invoice_id,amount,period_name ,accounting_date
from ap_invoice_payments_all
where check_id = 10480
SELECT *
FROM ap_lookup_codes
WHERE lookup_type = 'PAYMENT TYPE'
AND lookup_code = 'M'
/*Following this, for the manual payments there are no further actions, i.e there is nothing like
building ,formatting,confirming etc.It just reduces the supplier balance.
The advantage of manual payments, is that you can create a single manual payment for
multiple pay-alone invoices or you can pay a supplier who has Hold All Payments enabled. */
/*QUICK Payments : Quick Payments is a process where you quickly print a payment document
like check and send it to supplier. Basically when you create a payment,then only you
specify which invoice you are paying. The advantage is that you can select an invoice
regardless of the due date or payment terms.
However in this case, after you create a payment you will format and print the payment,but
there is no build stage.*/
select payment_type_flag, payment_method_lookup_code,check_id,check_number
from ap_checks_all
where check_number= '1000010'
-- put the above check id here to get payment/invoice information.
select invoice_payment_id,invoice_payment_type, accounting_event_id, cash_posted_flag,check_id,
invoice_id,amount,period_name ,accounting_date
from ap_invoice_payments_all
where check_id = 10500
SELECT *
FROM ap_lookup_codes
WHERE lookup_type = 'PAYMENT TYPE'
AND lookup_code = 'Q'
-- GL TRANSFER :
/*Now we need to transfer these invoices and payments transactions to the General Ledger.
Typically this happens in stages. The steps involved are
"Payables Transfer to GL",
"Journal Import" and
"Posting to General Ledger".
When we run the "Payables Transfer to GL", the data gets moved into the GL_INTERFACE table,and here
we can optionally mention to do the Journal Import as well. Hence after the Journal Import is done,we
can check the data in the following tables.*/
select * from gl_je_headers
select * from gl_je_batches
where creation_date = (select max(creation_date) from gl_je_batches)
select max(creation_date) from gl_je_lines
/* Generally while running the Posting Process i.e "Automatic Posting", the program will ask for
the autopost set id. For this we go to the form "Autopost Criteria Sets" and create a criteria
set by providing the priority,source,
category, balance type and period columns.
Having created a criteria set, we can then run the conc program (we can also run from the same form)
by providing this autopost set id */
select *
from gl_automatic_posting_sets
/* Hence after Posting process is completed we can check the gl_balances table which will contain most
importantly ,only the summarized information. */
SELECT *
FROM gl_balances
wHERE code_combination_id =4583
order by last_update_date desc
/*Use the Payables func to print a check. A payment document should need only
one document number right?
--why is that it needs so many document numbers from last used to last available
numbers.*/
/*
You need create receipt first and invoice later. If you created invoice and match
to po the invoice will be on hold as no receipts are created.
*/
/* PREPAYMENTS IN ACCOUNTS PAYABLES :
There are two kinds of prepayments => Temporary and Permanent.
Temporary prepayments are those which you apply to an invoice.
Perf
The way you deal with the prepayments in AP is that, you first create a prepayment invoice for a
particular supplier and make a payment for that prepayment invoice. Then whenever you are entering
an invoice for that supplier ,the system will prompt you saying that there is a prepayment against
this supplier.
1) First create a Prepayment invoice,make sure it is temporary, and provide a settlement date.
2) Make a payment for this prepayment invoice by doing this
Actions => Pay in Full
(why the prepay invoices needs to paid in full,why not partial?)
3) Now create a Standard Invoice for the same above supplier. when you tab out of the supplier window,
the system prompts you saying that, there is a prepayment for this supplier.
4) Next there are two ways, a prepayment can be applied to the supplier invoice.
* First you pull up the prepayment invoice and click on the
Actions=> Apply/Unapply Payments.
now this will give all the eligible invoices for that supplier and you can apply to one or
more than one number of invoices.
* Second,you pull up the Supplier invoice created in the step 3 and choose
Actions => Apply/Unapply Payments.
In this case also the screen looks very similar with the exception that you have an
additional check box "Prepayment On Invoice". If you check this, that what it means is
that the supplier invoice has included the prepayment amount as well
in the invoice amount. The only difference in both the ways of application is in the
creation of accounting distributions.
(Prepayment on Invoice checkbox :ticking the 'Prepayment on Invoice' checkbox when you
Apply the prepayment? This will create a new negative distribution line on the invoice
and reduce the overall invoice distribution total accordingly)
*/
select aia.invoice_id,invoice_date,invoice_type_lookup_code, aida.prepay_distribution_id,
prepay_amount_remaining
from ap_invoices_all aia, ap_invoice_distributions_all aida
where aia.invoice_id = aida.invoice_id
and aia.invoice_num like '08-MAR-2007'
select * from ap_payment_bsets_all
/* In AP, you cannot create payments without associating them with the invoice. That is ,without
a supplier invoice which has an open balance,you cannot create payments.
*/
select * from ap_invoice_prepays_all where last_update_date > sysdate -100
select * from ap_payment_history_all where last_update_date > sysdate -1
/*Payables & Cash Management => If the cash clearing needs to work, then
you need to give the correct cash clearing account information in the
bank account as wells as the payables documents level. then it will hit
the cash clearing account when a payment is account. further when you use
the cash management you can reconcile the bank statements with the cash
clearing account.
*/
Recurring Invoices :
--------------------
Generally a supplier sends a invoice and we enter it into our system. However
sometimes we can also generate a invoice on behalf of supplier. This can be done
by recurring invoice. A recurring invoice works on the line of recurring journals
in GL. It works in two steps,
Recurring Invoice template Definition
Recurring invoice creation.
Some of the important things that are needed in the template definition are
Special calendar => needs to be defined,with months,quarters etc.
Distribution source => get it from the distribution set or from PO.
About the calendar : Let us say if there is calendar consisting of 12 periods
from January to December.
And if you want first invoice period to be ,say , May. Then the next period
will be populated as May. Once the invoice has been generated, the next
period will be Jun. So at any point of time, we can see what will be the
next recurring invoice period. (The fields next invoice period and the
next amount indicate are non-editable fields).
There are two special amounts and periods, that means, on that period,
the invoice amount will be that amount.
Once a recurring invoice has been generated, you cannot change any fields
in the recurring template,other than the account distribution.
Do we need to come to the template everytime and run the recurring invoice
isn't that cumbersome ??
Payables Credit Memo, Debit Memo :
----------------------------------
A credit memo is a document sent by the supplier which decreases the supplier balance.
It is a negative amount and is applied by matching to an supplier invoice.
A debit memo is a document which you create and send it to the supplier for their
reference. It is also a negative amount and is applied by matching to an supplier invoice
and it decreases the supplier balance.
Pay group :
Pay group is a look up code defined in the purchasing application.
Q & A
------
Receiving Transactions processor should be running.
/*Payables Invoice Import Process
_______________________________
The following process goes thru the flow of generating the invoices (along with the underlying table updates)
until the ultimate GL Posting Process.
Invoices Testing Queries :
-- Set the Environment */
begin
fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
end;
-- Run the invoice_test.sql to populate the invoices into the ap_invoices_interface table.
select status,source,count(*)
from ap_invoices_interface
where source = 'WW-Real Estate-Payments'
group by source,status
-- Run the concurrent program "Payables Open Interface Import", with the invoice batch name as any name.
The import program will create a record in the ap_batches_all
-- You can keep checking the query #1 whether the invoices are processed or not in the source table.
-- We can also check in the destination table after setting the env, by running the following query.
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,
count(*)
from ap_invoices -- (or ap_invoices_all)
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
-- Get one such invoice imported into ap_invoices
select invoice_id,approval_status, posting_status ,wfapproval_status,
to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') creation_date,
to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS') last_update_date
from ap_invoices
where creation_date = (select max(creation_date) from ap_invoices)
and rownum < 2
-- The Following programs are scheduled in SJPRF to run for every 10 minutes.
/* "Invoice Validation" Concurrent Program.(APPRVL) (Param: Process all matching lines)
If the approval_status_lookup_code = 'APPROVED', THEN the status is 'VALIDATED'. if the
approval_status_lookup_code is 'NEEDS REAPPROVAL', then the status is 'needs revalidation'
*/
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'), count(*)
from ap_invoices_v
where trunc(creation_date) = trunc(sysdate)
and approval_status_lookup_code = 'APPROVED'
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select invoice_id,approval_status, posting_status ,wfapproval_status,approval_status_lookup_code
from ap_invoices_v
where invoice_id = 5354999
-- Run the "Payables Accounting Process" Concurrent program. (Param: Validate Accounts : NO).
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_ae_headers_all where trunc(creation_date) =trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_ae_lines_all where trunc(creation_date) =trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
/* "Workflow Background Process" for invoice approval. This is already enabled for every 30 sec in SJPRF.
OR "Invoice Approval Workflow" needs to be run.
--Before approval, the below query should give the import count# */
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS'),count(*)
from ap_invoices_v
where trunc(creation_date) =trunc(sysdate)
and wfapproval_status = 'WFAPPROVED'
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
-- After approval process, the above query should give 0 records.
/* "Cisco Payables GL Transfer Post Program" ap_invoice_distributions_all (posted_flag),
(gl_interface, gl_je_batches, gl_balances.)
After the feeder systems (i.e AP,AR ) transfer the data to GL, they first go to gl_interface table and then
from there , they are moved to the gl_je tables. Once these entries are posted, they will update the
gl_balances table.
So this program essentially kicks off
Payables Transfer to GL
Journal Import
Automatic Posting
Hence it is important we need to have US GL Super User resposibility assigned to ourself
before we kick off this program.
*/
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*)
from gl_je_headers
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') ,count(*)
from gl_je_lines
where trunc(creation_date) = trunc(sysdate)
group by to_char(creation_date,'DD-MON-YYYY HH24:MI:SS')
select table_name,num_rows from dba_tables where table_name in
('AP_AE_LINES_ALL')
/* Once the invoices are imported into the ap_invoices_all , ap_invoice_distributions_all tables from
ap_invoices_interface and ap_invoice_lines_interface, we can also look at them from the applications
either in the invoice form or look at all the invoices in
Invoice=>Invoices=>Open Interface Invoices. */
/*
Quick Invoices & Invoices Workbench:
Quick invoices window will not do much of the validation and do not default the values.
Also this information will go into the ap_invoices_interface table,after which we can run the
invoice import program to import them in AP.
For manually entered invoices,when we validate them, does it do matching and if it does,where is
the PO information.
*/
--
For invoice matching is the PO really required, just by the supplier id, cant the
system go and find the related PO and match them. so we found that for the matching
to succeed we really need a PO for the invoice,otherwise it fails.
--
regardless of the po, for each receipt, you generally have one AP invoice.
--
you can receive any amount lesser than the PO line amount, however the over-receipt
tolerance amount pertains to the scenaroi,when u r receiving more than the po line amount.
--
The only way you can enter a quantity invoiced amount in the invoice distribution is
to match it to a po line /shipment.
then the quantity invoiced is defaulted to the value of the po line qnty requested
You could edit that value if you want to and change it.
--
so basically in matching the invoice distributons are matched to the PO lines/distributions
or receiving distributions based on 2 way or 3 way matching.
--- QUESTIONNAIRE :
1) Is there a tax hiearchy in AP as well ???
Yes. Just like in AR,here too we define tax code(from tax types) and tax codes
can be specified at different places like Supplier, site, payables options,
invoice header, template. And the hierachy of preference can be found in the
Financial options.
Howeer I did not find the location tax code in the tax code in the payables. This
is probably because the company is not taxing ;it is supplier who will have to
apply the taxes.
2) What is a voucher and document sequence ??
Since two suppliers can provide you two invoices which might have the same invoice
number, in which case, invoice number cannot be unique. Hence you have to create
a voucher number within your system for these two invoices, so that you can
uniquely identify them.
Also we can assign a document sequence for each document category with in the
Oracle applcations system. And so you can for a AP invoice.Simple steps are
There are already predefined document categories like AP invoice, AP credit memo,
so no need to create anything new
Create a new sequence with starting number, etc.
Assign the above sequence to a particular
(Application, Category, Set of Books, Start date)
Ensure that Sequential Numbering profile option is aptly set.
3) What happens in these matching scenarios ??
PO , 2 -way => No confusion here, 2-way matching is performed,
Receipt, 3-way => No confusion here, 3-way matching is performed.
PO, 3-way => what will happen here, 2-way matching or 3-way matching with receipts
Receipt, 2-way => what will happen here , 3-way matching or matching with PO's.
Also between, 2 way vs 3 way matching, which one is used in what circumstances
and what are the advantages of one over the other? when both of them can be
used, which is preferred ? is it like the more receipts for a PO, better 3,4 way matching?
4)What are credit memos and debit memos in Payables ??
A credit memo is a document sent by the Supplier which you will apply to
an invoice and which reduces the invoice balance.
A debit memo is a document which you will create ,which will also reduce the
invoice balance,and send it tto the supplier for verification,approval.
5) Should every invoice in Payables be a part of an invoice batch?
If the profile option "AP: Use Invoice Batch Control" is set to yes, then you can
create an invoice only after you create a invoice batch.
If the above profile option is set to No, the you can create a standalone invoice,
without being part of a batch.
6) Should every payment be a part of a payment batch?
No.
Payments => Entry => Batches ; Payment batches go thru the build,format and confirmation.
Payments=> Entry => Payments ; If you enter a standalone payments, they are called
single payments and they are of 3 types (Quick,Manual,Refund).
7)What is the difference between online Matching and offline Matching????
When you are entering online invoice in Invoice workbench, the only way you
can enter an invoice quantity (Match Quantity) is by matching it to a PO line,upon
which the invoice quantity is defaulted from the PO line,which you can change.
So you cannot manually type in the invoice quantity in the Invoice distribution
with out matching.
However if we manually create an invoice line which is identical to a PO line then
run matching,then the system just matches and validates it successfully.
what is that so & why there is no invoice qty field in the distribution form
8) What are the steps required to void a check in AP ?
9) Difference between suppliers and vendors ??
I believe vendor is a type of supplier. This is bcoz, when you choose supplier type,
you have a option of vendor there.The different kinds
of suppliers are government, federal ,Mfg.
Also Employee is a type of Supplier.
try to see where you can define supplier types.
10) For manually entered invoices,when we validate them, does it do matching and if it does,where
is the PO information??
Yes it does matching and its puts on hold if there is no corresponding matching. In such
case,you can release the hold manually.
11).When I go to the view accounting from the receiving transaction, i am not able to see the accounting
entry for the accrual account??
Firstly ensure that you have accrue on receipt. And then if you do that, then the
accounting lines are automatically xferred to GL and can be seen
in gl_interface.
12).In Oracle 11i, there is no centralized payment systems ??
which means that you just cannot have a
bank defined in one country and send payments from that bank. You need to define banks in each
operating unit. So it is understandable bank is tied to an operating unit/ SOB id.
When we build the payments, it is important that you can only give one type of payment types
i.e you cannot build corresponding to CHECK and ACH with in a payment batch.
No comments:
Post a Comment
Please review my topic and update your comments
Note: only a member of this blog may post a comment.