Account Payables Complete Flow (Invoices, Payments)

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.