Tuesday, January 6, 2015

SQL: Menu hierarchy

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       --AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = 'AR_NAVIGATE_GUI')
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence

Monday, January 5, 2015

Forms: Implementing sub-functions

Oracle apps sub functions Overview:
There are two types of functions: form functions, and non-form functions. For clarity, we refer to a form function as a form, and a non-form function as a sub function, even though both are just instances of functions in the database.
Oracle Applications aggregates several related business functions into a single form. But all users should not have access to every business function in a form; Oracle Applications provides the ability to identify pieces of applications logic as subfunctions. When part of an application’s functionality is identified as a sub functions, it can be secured (i.e., included or excluded from a responsibility).

Sub function (Non-Form Function):
A non-form function (sub function) is a securable subset of a form’s functionality. Sub functions are frequently associated with buttons or other graphical elements on forms. For example, when a sub function is created for a button in a form and if the created sub function is added to the particular responsibility, then only the corresponding button will be enabled in that responsibility, otherwise (if the created sub function is not added to responsibility) the button is disabled, even if the main function is added to that responsibility, and the user will not be able to access the button.
The user can access the main function but not the sub function, if the sub function is not added to the responsibility, which means we can provide security to the part of the form functionality, using the function security mechanism through the sub function.
Diagrammatic illustration of the sub functions

Steps to implement a Sub function in a form:
1: Open Template.fmb using Oracle form builder, save it with another name(XX_SUB_FUNC.fmb) and give the module name as “XX_SUB_FUNC”.
2: Delete the following:
“BLOCKNAME” from Canvas and Data Block
“DETAILBLOCK” from Data Block
“BLOCKNAME” from Window.
3: Create window (XX_SUB_WIN), apply subclass information as “WINDOW” and give the title.
4: Give this window name in PRE-FORM trigger (Form Level) and also in APP_CUSTOM body from Program_Unit (in place of window name).
Give this window name in PRE-FORM trigger (Form Level) As Shown below

Give this window name in APP_CUSTOM (PACKAGE BODY) as below
5: Create New Canvas
6: Now create a new Data block (EMP) using Wizard or Manual, apply subclass information to the block as “BLOCK” and also apply subclass information to the items as “TEXT_ITEM”.
Click on Finish…………..until finish
7: Give the Subclass information for Block (EMP) As BLOCK
8: Create a Button(EXIT) in Canvas and write your required functionality code in when button pressed trigger.
9: Give the Subclass information for ITEM (EMPNO) as TEXT_ITEM
10: Finally move your form from our Local Machine to CUSTOM_TOP using WINSCP or any of your local tools.
11: Compile the Form using “f60gen”
In the UNIX environment, type the following command:
f60gen XX_SUB_FUNC.fmb apps/apps
12: Connect to Oracle Applications:
1. Create a Form (Navigation: Application Developer->Application-> Form)
Here give the following details and save the details.
Form =>XX_SUB_FUNC (.fmx )
Application => xxmz Custom(Custom TOP Application)
User Form Name=>Give any name
2. Create a function (Navigation: Application->function)
Here give the following details:
Description Tab:
Function name: Any Name (XX_SUB_FUNC_FUNC)
User Function Name: Any Name (XX_SUB_FUNC_FUNC)
Properties Tab:
Type: Form
Maintenance Mode Support: None
Context Dependence: Responsibility
Form Tab:

Attach User Form Name:XX_SUB_FUNC

Create Sub Function (XX_SUB_FUNC_FUNC1) below the main function
(Navigation: Application Developer=>Application=>Function)
Give the User Function Name: XX_SUB_FUNC_FUNC: EXIT (User function name: button name)


Properties Tab:

Type: Sub function
Maintenance Mode Support: None
Context Dependence: Responsibility
13: Once again Switch to Form builder open the user form and go to Form Level Trigger
“WHEN_NEW_FORM_INSTANCE” and incorporate the function
14: Write the Function ( Function name=>fnd_function.test) As
Script:
if fnd_function.test(‘XX_SUB_FUNC_FUNC1′)then
set_item_property(‘emp.exit’,ENABLED<PROPERTY_TRUE):
else
set_item_property(‘emp.exit’,ENABLED,PROPERTY_FALSE);
END IF;
15: Save it and move the form to custom top using WINSCP like tools and compile the form again
16: Switch to Oracle application:
Switch to Menu
(Navigation: Application Developer=>Application=>Menu)
Attach Main function to the Custom TOP menu
User function name: XX_SUB_FUNC_FUNC
Switch to Custom Application Responsibility (XXMZ CUSTOM)
Execute the Function (Click on the Function name)
(Function name=>sub functions)
The button will be disabled (because the sub function is added for the button and sub function is not added to the custom menu)
Switch to Application Developer Responsibility
Attach Sub function to the Custom TOP menu without giving prompt.
User sub function name: XX_SUB_FUNC_FUNC: EXIT
Again Switch Custom Application Responsibility(XXMZ CUSTOM)
Click on Function name (sub functions)
The button will be enabled (because the sub function is added to the button and also to the custom menu)

Wednesday, December 10, 2014

Forms: NAME_IN and COPY in Forms

Name_In function : 

You can reference items indirectly with the NAME_IN and COPY built-in subprograms. The NAME_IN function returns the contents of an indicated variable or item. Use the NAME_IN function to get the value of an item without referring to the item directly. 

The following two statements retruning the content of an indicated variable ('global.'||name_in('system.cursor_block')||'_active'

1)IF name_in('global.'||name_in('system.cursor_block')||'_active') = 'Y' THEN
property();

2)name_in('global.privelege') NOT IN ('A','D') then 
error menu 

Copy Procedure : The COPY procedure assigns an indicated value to an indicated variable or item. Unlike standard PL/SQL assignment, however, using the COPY procedure allows you to indirectly reference the item whose value is being set: 

3)copy('I',name_in('system.cursor_block')||'.active_status');



COPY can be used with the NAME_IN function to assign a value to an item whose name is stored in a reference variable or item: 

/* put value 'KING' in item whose name is stored in ref_item */ 
Copy('KING',Name_In('control.ref_item')); 

More in Name_In and copy 

Why Use Indirect Reference Referencing items indirectly allows you to write more generic, reusable code. By using variables in place of actual item names, you can write a subprogram that can operate on any item whose name has been assigned to the indicated variable. 
Also, using indirect reference is mandatory when you refer to the value of a form bind variable (item, parameter, global variable) in PL/SQL that you write in a library or a menu module. Because libraries, menus, and forms are separate application modules, you cannot refer directly to the value of a form item in a menu-item command or library procedure.

Tuesday, December 9, 2014

AR: Receivables Important Information

Accounts Receivables 
====================
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.

  /*Introduction :  Companies can sell their products either for cash (which 
  is checks, credit cards etc)or as invoiced sales on credit with specific 
  payment terms. Invoiced sales create a receivable on the balance sheet
  (on GL) which represents the money due to the company. Receivables produce 
  3 legal docs which are invoice,statement and dunning letter. The different 
  types of transactions that are available are 
   Invoice    (debit item)
   debit memo (debit item)  
   credit memo   
   adjustments
   chargebacks (debit item)
   commitments
   refunds
  Apart from the above transactions, the most important thing is 
     Receipts
  */

/*Make sure for the period that you are defining an invoice batch, that period is 
either open or future enterable.*/

 control=> accounting => open/close periods

/*Actually in a system, we can know what is the set of books by running 
the command */

 begin
 dbms_output.put_line(fnd_profile.value('gl_set_of_bks_id')) ; -- or name
 end;
 
/* 
Once we get the set of books id and name, we can lookup the , 
Accounting flexfield structure, operational currency and the fiscal calendar. 
Another important thing is the accounts(like retained earnings etc).
For successfully defining a batch we need to have all the setup data 
correctly defined like the currencies, accounting period, period types and set of books.
SET THE ORG_ID TO 101  
*/
  
-- Now let us start with the first transaction "INVOICE" and see what ACCOUNTS 
--will get updated.  

  select organization_id,name -- 82 for Netflix US 
  from hr_all_organization_units
 
  begin
  fnd_client_info.set_org_context(fnd_profile.value('ORG_ID'));
  end;
    
  select set_of_books_id ,name 
  from gl_sets_of_books 
  where set_of_books_id = 1
  
  /*Now let us say, we are trying to create an invoice, in a particular batch 
  The source and currency information will default to the values specified in 
  the batch.  Now we set the class to Invoice. For each invoice class we can 
  have different types of invoices. We can create different types of invoices in 
  setup data in (setup, transactions, transaction type). For ex we can 
  create 2 txn types both of type invoice but one with printing and one without 
  printing option,or having different GL accounts for revenue, tax freight etc. 
  All this information goes into "ra_cust_trx_types_all" table. 
  
  A word about "Open Receivables" and "Transfer to GL" :In the transaction types 
  set up from usually we have 2 check boxes apart from different fields and different 
  accounts setups. They are Open receivables and transfer to GL.
  
  Open receivables means, whenever a transaction of this type is created, then 
  the customer balance will get affected. That is,when a transaction is created, 
  it finds an entry immediately in the payment schedules_all table once the
  transaction is 'complete'd.And since the customer balances are always calculated 
  based on this important table, the balance will get affected. If 'Open Receivables' 
  is not set, then even if we complete the transaction,it will not appear in the 
  payment schedules table and hence the balances are not reflected.
  
  Transfer to GL : If this set then the transaction is transferred to GL, once 
  the GL transfer program runs, otherwise not.
  
  Usually companies implement by creating a VOID transaction by not setting these 
  flags in the transaction type.
  
   
  Now for the purpose of argument, let us say we have
   Open Receivables to Yes, and Transfer to GL set to No, then we are recording 
   some transaction in AR, but not showing that up in GL which is not correct.
   Open Receivables to No, and Transfer to GL set to Yes,usually this can happen in 
   conversion transactions. 
     Usually, we can create a trx type with Open Rec to No for the transactions 
   which you want to review initially and when you are satisfied, you can change 
   the trx type to Final(with open rec to Yes). Usually changing the trx type 
   will make the AutoAccounting rerun and create the correct gl entries.
     (post-to-gl checkbox is used for adjustment (whcih generally happen in small 
   amounts) and need not be reflected in gl account balances) 
  */

 select a.cust_trx_type_id,a.name,a.description, a.type,a.org_id,a.*
 from ra_cust_trx_types_all a

/* The invoice batch source is properly set up. The following query can be used to check that. 
   FOR THE INVOICE BATCH SOURCE*/  

SELECT rowid, auto_trx_numbering_flag, name, org_id, description,
       batch_source_type, batch_source_id, status, default_inv_trx_type,
       start_date, end_date,creation_date
  FROM ra_batch_sources
 WHERE batch_source_type = 'INV'
   AND batch_source_id NOT IN (11, 12)
   AND org_id = 82
   AND status = 'A'

/* The invoice batch Currency is properly set up. The following query can be used to check that. 
 FOR THE INVOICE BATCH CURRENCY*/ 

SELECT fc.currency_code, fc.NAME, fc.description
  FROM fnd_currencies_vl fc, gl_sets_of_books gl, ar_system_parameters ar
 WHERE fc.currency_flag = 'Y'
   AND fc.enabled_flag = 'Y'
   AND fc.currency_code = gl.currency_code
   AND gl.set_of_books_id = ar.set_of_books_id

-- For the invoice batch gl_date.The following query can be used to check the gl_date.

SELECT period_name, closing_status, period_name
  FROM gl_period_statuses
 WHERE application_id = 222
   AND set_of_books_id = 1
   AND adjustment_period_flag = 'N'
   AND period_name = 'OCT-04'
  
  begin
  dbms_output.put_line('the value is ' ||fnd_profile.value('AR_SET_OF_BKS_ID'));
  end;

/* Hence having created a transaction , we can look at the table ra_customer_trx_all.
   While creating an invoice transaction online, we can see that the reference number 
   is null. Actually this is the order number(???)that would be populated when 
   AutoInvoice process has pulled the orders from the Order Management to the 
   Accounts Receivables. */
 
  select batch_id,batch_source_id,customer_trx_id,sold_to_customer_id,bill_to_site_use_id,
     remit_to_address_id,status_trx,paying_customer_id,trx_number,cust_trx_type_id,
   previous_customer_trx_id,trx_date,creation_date           
  from ra_customer_trx_all
  order by creation_date desc

/* Trx_date, GL_date, Creation_date : the trx_date could be different from 
 creation_date. The trx date could have happened yesterday , but you have not entered 
 it,(say system down) and you are entering it now. Then in that case, the trx_date is 
 yesterday and creation_date is today. The gl_date is not stored at the trx or line 
 level. it is stored only in line dist level. The gl_date is required because when 
 we transfer the trx to GL,it will pick all the records from the gl_distributions 
 table,which fall with in the range specified in the GL transfer request form.*/   
  
  -- And the invoice lines can be seen from the query
  select customer_trx_line_id line_id,set_of_books_id,description,quantity_invoiced,
     unit_selling_price,line_type,org_id
  from  ra_customer_trx_lines_all
  where customer_trx_id = 1034
  
  /* The distributions of the INVOICE line are given by query below. Hence the two 
  important accounts that will get affected by Invoice transaction are Receivables 
  and Revenue.*/
  select  cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, 
      code_combination_id, set_of_books_id
      ,amount,gl_date,account_class,customer_trx_id, org_id
  from  ra_cust_trx_line_gl_dist_all
  where  customer_trx_id = 1035
  
 /*  A useful query to give us the code_combination_id given an account number is given below.  
  So for the invoice process, the receivables account will get debited and the revenue account 
  (tax,freight etc) will be credited.*/ 
  select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-'||a.segment5
     ||'-'||a.segment6 acct_code,a.* 
    from gl_code_combinations a 
    where segment1 =01    
    and segment2 = 0000
    and segment3 = 0000
    and segment4 in (11100,40310)  -- 11100(4587) is receivable and 40310(1386) is revenue account. 
    and segment5 = 0000
    and segment6 = 0000
    and segment7 = 0000
    and segment8 = 0000
 
 /*REMIT TO Address: While creating a transaction, we may have to enter the remit to 
  address. Basically remit to address is the address to which the customer 
  should send his payment to. You can create remit to address using the following
  menu option
         setup => print => remit to
     
  This will pull up the Remit-To Address form where you will enter
  the remit-to addresses. Basically what we specify here is that for a range
  of zip codes(based on country and state), we can specify the payment to be
  sent to a particular address i.e a local address.
  */       
 
-- COMPLETE THE TRANSACTION /INVOICE/CREDIT MEMO.
  /*Having created all the above, we need to COMPLETE the txn, and the important steps
   that we should look at for the completion process are given below.
Validation for completing a standard transaction 
    The invoice must have at least one line. 
    The GL date of the invoice must be in an Open or Future period. 
    The invoice sign must agree with the creation sign of the transaction type. 
    The sum of distributions for each line must equal the invoice line amount. 
    If the Calculate Tax field for the transaction type is set to Yes, tax is required
     for each line (except lines of type Charges). 
    If freight was entered for this transaction, you must specify a freight account. 
    If the system option Require Salesreps is Yes, salespersons must be assigned to each line. 
    If salespeople are assigned to each line, the total revenue sales credit percentage 
    must equal 100%. 
    All the activity date ranges for the setup values (for example, payment terms) must 
  be valid for the invoice date. 
    If this transaction uses an automatic payment method, you must enter Customer bank, 
    branch, and account information.*/

/* Once the invoice (or any) transaction is succesfully COMPLETE'd, then we can use 
 that invoice i.e that invoice goes into the important table called ar_payment_schedules, 
 so that we can apply a receipt to this invoice. Once an invoice is COMPLETE'd then 
 the "Complete" check box is checked. We can try to Incomplete and Complete this
 particular invoice any number of times, until a receipt is applied against this 
 invoice. Once a receipt is applied against this invoice, then the Complete/Incomplete
 button is disabled. Also if we want to transfer this transaction to the GL, we  want 
 the transaction to be complete. This table stores multiple kinds of information. 
 (Also look at completing the receipt). And once this invoice is transferred
 then also we cannot incomplete the invoice,infact the Incomplete button will be disabled.
*/
  select customer_trx_id,cash_receipt_id,payment_schedule_id, class,customer_id,
     trx_number,trx_date
   ,customer_site_use_id, selected_for_receipt_batch_id btc_id,
   acctd_amount_due_remaining amt_due
   ,org_id,reserved_value,status
  from ar_payment_schedules_all
  where  customer_trx_id = 1034
   
 /*
 For A CREDIT MEMO.(Another kind of transaction).
 Now for a credit memo, everything looks identical to that of a invoice, however as 
 far as the accounting entries are concerned, Receivables account will be credited 
 and the revenue accounts will be debited (because it is a credit to the customer. 
 While entering a credit memo, make sure you enter the amount as negative value.
*/

-- A useful query to give us the code_combination_id given an account number is given below. 
  select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-'
     ||a.segment5||'-'||a.segment6 acct_code ,a.* 
    from gl_code_combinations a 
    where segment1 =01    
    and segment2 = 0000
    and segment3 = 0000
    and segment4 in (11100,40230)  -- 11100 is receivable and 40230 is revenue account. 
    and segment5 = 0000
    and segment6 = 0000
    and segment7 = 0000
    and segment8 = 0000

  /* The distributions of the invoice line are given by query below. Hence the
  two important accounts that will get affected by Credit Memo transaction, and 
  they are Receivables and Revenue(of a kind). There is a posting_control_id field 
  in RA_CUST_TRX_LINE_GL_DIST_ALL table. If the posting fails or is unposted 
  yet,you have a value of -3 otherwise if posting is successful you get the next 
  value in the sequence. The moment we run the GL transfer program, these transactions
  are moved to the GL Interface table and at the end of that process, the 
  "Update Posting Control" process will kick off and it will back populate the 
  posting_control_id in this table. This does not mean that the gl posting is done 
  for this transaction. */
  select  cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id,
      code_combination_id, set_of_books_id
      ,amount,gl_date,account_class,customer_trx_id, org_id,
    posting_control_id
  from  ra_cust_trx_line_gl_dist_all
  where  code_combination_id in (4587,4590)
  
   /* There are two ways of associating a Credit Memo to an Invoice. 
 Pull up the original invoice in the Invoice transactions form.
 From the menu item,   
        Actions => Credit, 
 Pull up the Credit Transaction from,
 Here in this form, we cannot associate a pre-created credit memo. Instead, we 
 can specify the credit amount (or %) and save this transaction. This will internally 
 create a credit memo. And this credit memo we can try to query again from the 
 transactions form. Look for the column previous_customer_trx_id, which stores
 the original invoice transaction id. */
     select customer_trx_id,previous_customer_trx_id,creation_date,sold_to_customer_id,
    bill_to_site_use_id,remit_to_address_id,status_trx,paying_customer_id,
   trx_number,cust_trx_type_id
     from ra_customer_trx_all
  where  customer_trx_id = 1035
     order by creation_date desc

  /* on-account credit : Alternatively create an credit memo from the transactions 
  workbench which is called "on-account credit memo" or "on-account credits" by 
  requerying the same credit memo from the menu  
        Actions => Adjustments 
   and provide the invoice number to which you can apply the credit memo.*/
  
  -- We can see all the balances for a transaction by clicking on the Balances button.
     select customer_trx_line_id line_id,set_of_books_id,description,quantity_invoiced,
        unit_selling_price,line_type,org_id, extended_amount, revenue_amount
     from  ra_customer_trx_lines_all
     where customer_trx_id = 1035
  
     /* The distributions of the invoice line are given by query below. Hence the two 
  important accounts that will get affected by Invoice transaction are Receivables 
  and Revenue.*/
     select  cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, 
     code_combination_id, set_of_books_id
        ,amount,gl_date,account_class,customer_trx_id, org_id
     from  ra_cust_trx_line_gl_dist_all
     where  customer_trx_id = 1035
  
 /*Hence in the credit memo record we can see that we will have reference to the 
 original invoice transaction number. This make sense, because one invoice can have 
 multiple credit memos and each one can store the correct invoice number. It is difficult to
 store the credit memo information in invoice if there are more than one credit memo for 
 an invoice.*/
 
  /* Adjusting an Invoice transaction. When you adjust an invoice transaction, we can adjust 
   in such a way that the invoice balance is 0. i.e If there is an invoice transaction for 
   $100 and we have a receipt of $50, then we should make an adjustment equal to exactly $50 
   and not any amount less than that. It is important to note that once an adjustment is made, 
   the adjustment needs to be approved, or the user should have the approval  rights to approve 
   the adjustment , otherwise we still see that there is a balance for that invoice transaction.
   */ 
 select * 
 from ar_adjustments_all

   /* Approval Limits for adjustments, Receipt Writeoff's and Credit Memos   
 Generally when a user creates an adjustment,small balances write-off or 
 create credit memos, he needs to have an privilege or approval authority to do 
 that. This can be done by creating an approval limit for each of the above. 
 Using the menu item 
    setup => Transactions => approval limits.*/
   /* You can associate a credit memo to an invoice which has already been paid.*/

   /* when you create a credit memo you can either associate it with an item or with 
   a memo line created in setup */   
    select memo_line_id,accounting_rule_id, line_type,uom_code, name,description,org_id
    from ar_memo_lines
  
  -- A useful query which will select the memo lines from lov is given below
     SELECT 
       aml.memo_line_id,
       aml.description "aml.description",
       aml.name,
       al.meaning,
       aml.line_type
     FROM 
       ar_memo_lines aml,
       ar_lookups al,
       mtl_units_of_measure uom,ra_rules rr, ra_rule_schedules rs
     WHERE 
          al.lookup_type =     'STD_LINE_TYPE'
      and  al.lookup_code = aml.line_type 
      and  aml.uom_code =    uom.uom_code (+) 
      and  aml.accounting_rule_id = rr.rule_id (+) 
      and  rr.rule_id    = rs.rule_id (+) 

   
  /*PAYMENT TERMS : Payment terms indicate when the customer needs to pay the invoice. 
     
  There are different kinds of payment terms that you can create,based on
  what you enter in the cutoff region and the detail region.
  
  First, simple one is say the invoice is due after 30 days of the invoice creation.
          (enter only Days field in details)
      
  Second one is the invoice is due, on a specific date.
         (enter only the Date field)
     
  Third on a specific day of the month like 15th.
          (enter only days of month and months ahead)
      
  /*Payment terms, some examples of the payment terms are like net15, net30 1% 
  (which means that the invoice is due from the 30th day of the invoice creation 
  date and if made with in that time, the customer gets 1% discount of the
  invoice total amount) */

  -- The following 2 queries give info about the Payment terms.
   select term_id,name, description  
   from ra_terms
   where name like 'NET 7'
   
   select * --term_id,relative_amount,due_days
   from   ra_terms_lines
   where  term_id = 1056  
      
   select * --term_id,relative_amount,due_days
   from   ra_terms_lines
   where  due_day_of_month > 0  
   
   
   -- And if there are any discounts for the terms,it will be here.You dont
   --find a term_line_id, but only a term_id.
   select * 
   from  ra_terms_lines_discounts
  
  
  
  -- Split Payment Terms and Installment Invoices in AR
/****************************************************

   -- simple query to find out the split payment terms in the system. 
   select a.term_id,count(*)
   from   ra_terms a, ra_terms_lines b
   where  a.term_id = b.term_id
   group by a.term_id
   having count(*) > 1
   
   select * from ra_terms where term_id = 1070
   
   In general a transaction or an invoice will have a payment term like Net 15 
   which means that the invoice is due within 15 days from the invoice date(
   or gl_date). However we can create an installment invoice(for$300) with the 
   payment term being specified as the Installment term(i.e we define a specific 
   installment payment term with ,say four payment schedules as due in 15,30,45,
   60 days. When a invoice is created in such a way and completed, then it will 
   have four records in the payment schedules table with the same customer_trx_id,
   with each installment having an amount due_remaining and original as $75.
   Now a credit memo or receipt can be applied to any one specific installment 
   driving that installment amount to negative. 
   
   So to find such customer transactions from the payment schedules we can use 
   the following query.*/
   
   select distinct customer_trx_id ,payment_schedule_id, 
       amount_due_original,amount_due_remaining
   from ar_payment_schedules_all a
   where status ='OP' and class ='INV'
   and   amount_due_remaining >0 
   and  exists  (select 1 from ar_payment_schedules_all b
         where  amount_due_remaining <0
        and    b.customer_trx_id = a.customer_trx_id)
   
   select * from ra_customer_trx_all where trx_number ='13352'
       
   select * from ra_cust_trx_line_gl_dist_all 
   where customer_trx_id = 29936776 --,82584133, 364831854
   
   select * from ar_distributions_all  where source_id =364831856


   
 /*Customer Profile :
      Each customer is associated with a customer profile. The profile tells
      what is the credit limit for the customer
      who is the collector for this customer
      what kind of dunning letter should be sent.
      what is the grace period before we sent dunning letter.
      whether a finance charge should be charged or not  etc.
      Consolidated billing invoice can be sent or not.
    */
 
   -- Payment Terms and Finance Charges in AR : 
   Payment terms,finance charge,grace days are specified as part of a customer profile.
   
   Customer Standard > Profile: Document Printing screens.
    
 /*You must check/uncheck the flag at both the customer and site levels.
 Once an invoice is due, and after the grace period, the system starts
 sending the dunning letters to the customer and at the time of sending
 dunning letter or printing statements,if the finance charge option for 
 a customer is set at the profile, then that customer is charged a finance 
 charge. Usually Finance Charges are calcuated when running the Statements
 or printing Dunning Letters. */
 
  
   /*Proxima Payment Terms : Proxima payment terms is one where the invoice
   is due on a specific day every month like phone bill,electricity bill,etc.
   Typically for the proxima payment terms, we enter the cutoff date,
   days of the month, months ahead fields. Bear in  mind that cutoff date is
   at the header level while the day_of_month,b.months_ahead are at the 
   detail level. */   
   
   select a.due_cutoff_day,b.day_of_month,b.months_ahead
   from ra_terms a, ra_terms_lines b
   where a.term_id = b.term_id
   
  /*Consolidated Billing Invoice (CBN) : 
    A Consolidated Billing Invoice is also like a regular invoice,however
 it consists of all the activity i.e invoices, credit memos,debit memos,
 receipts,adjustments etc all consolidated and the net balance is shown
 on the invoice. You can only run a consolidated billing invoice once per
 period. That is why you have the facility to run a draft CBN,look at it
 and then reject it if you dont need it. Here are the following things/features
 that you need,to ensure so that you can successfully print a CBN. 
 
     Usually you create a proxima payment term(explained above and associate it to a customer.
  
  The Consolidated Billing Invoices program ignores the payment terms assigned 
  to individual debit items when selecting transactions.It looks at the 
  payment terms at the customer bill-to site,address and customer level
  in that above specific order.
     
     When submitting the Print Consolidated Billing Invoices program, you must 
  enter a Cutoff Date. For ex, if the current month is June, and if you 
  enter as 12-JUN-2008, then the program will check for that specific
  customer, the cut off day is 12 or not.If it is ,then it will pick all
  the transactions for that customer, which are dated less than the 12-JUN-2008.
  
     If you provide a not-null payment terms in the parameter form when printing
  this consolidated billing invoice and if it does not match payment terms  at 
  the site or customer level,no transactions will be selected.
     */
  
  /* If there are any transactions selected for consolidated billing invoice
  it would be in this temporary table. However if you reject this invoice, it will
  reject the CBN, then it would delete from this table. */
  
  select * from ra_cons_inv_trx

  /* Statements :
    
    There are few prerequisites for a statement to be printed for a particular customer.
  Firstly,in the customer profile we should set option of sending the statements.
  Usually the statement are printed on a location by location basis. Hence for
  each location or address we should ensure that a language is being set,otherwise
  it will print for each language. Similarly whether a finance charge needs to
  be charged or not,it should be set at the profile option.*/
  
  --If the Accrue Interest option IS SET at the System Options level ,
    Setup => System => System Options => Miscellaneous.
   
  /*and if the Finance Charge is set at the Customer Profile level and also while
  running the statement, then the system will calculate the finance charge
  and will include that charge as part of the invoice balance.
  And the corresponding balancing entry is created for a pre-defined receivable
  activity. We can find a pre-defined receivable activity "Finance Charge" under
  the menu  Setup => Receipts => Receivable Activity.
  
  However,if the Accrue Interest option is NOT SET and if the Finance Charge is 
  set at the Customer Profile level and also while running the statement, then the 
  system will calculate the finance charge and show it in the statement,but it
  will not be part of the invoice balance. Hence it is for only display purposes.
  
  If there are multiple bill-to sites, then it is better to create a statement 
  site.  
  */ 
  
  /* Each transaction type belongs to a class (type) i.e we can have 2 types which 
    are of type invoice class*/
   select cust_trx_type_id,name,description,status,type,default_status,gl_id_rec,
       gl_id_rev,set_of_books_id,org_id
   from   ra_cust_trx_types 
  
   -- Invoice and accounting schedules.
   select rule_id, period_number, percent,creation_date,last_update_date
   from  ra_rule_schedules
    
  /*
  -- FOR A DEBIT MEMO. (debit Item)
   Now for A debit memo, it is similar to that of a credit memo , however as far as 
   the accounting entries are concerned, Receivables account will be debited and the 
   revenue accounts will be credited 
   (because the customer has to pay that much amount back to us).
  
  -- FOR A CHARGEBACK. (debit Item)
   Now for a chargeback, it is identical to that of a debit memo , as far as the 
   accounting entries are concerned, Receivables account will be debited and the 
   revenue accounts will be credited (because the customer has to pay that much 
   amount back to us).
  
  -- FOR ADJUSTMENTS.  
     Adjustments are alterations to the debit items. We can separately adjust the tax, 
  frieght or receivables amount of an item and the adjustments can be either 
  positive or negative. YOU NEED NOT INFORM THE CUSTOMER about the adjustment as 
  they are internal corrections that do not affect the legal documents. 
     The accounting entries that are generated in the case of an adjustment are
      Receivables account credited by the adjustment amount.
      Adjustment account debited by the adjustment amount.
  
  -- FOR COMMITMENTS : deposit commitment and guarantee commitment.
     A deposit commitment occurs when the customer agrees to pay a deposit for goods
  for they have not ordered yet.
     A guarantee commitment is a contractual guarantee of future purchases.
    Typical accounting entries for commitments will be
    
     Receivables debited by the commitment amount
     Unearned revenue will be credited by the commitment amount.
  */

 -- For all of the above transactions, we can run the following query giving 
 -- diff code combination id's below.
  select  cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, 
      code_combination_id, set_of_books_id
      ,amount,gl_date,account_class,customer_trx_id, org_id,posting_control_id
  from  ra_cust_trx_line_gl_dist_all
  where  code_combination_id in (4587,4590)

  /*Transaction classes determine if a transaction relates to either the 
  RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. 
  Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL 
  table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, 
  such as the creation of credit memos, debit memos, invoices, chargebacks, or 
  deposits. 
  Using the CASH_RECEIPT_ID foreign key column the AR_PAYMENT_SCHEDULES_ALL table 
  joins to the AR_CASH_RECEIPTS_ALL table for invoice-related paymenttransactions*/

  --RECEIPTS 
  /***************************************************************************/
  
  /*Receipt Creation :
  After this, we proceed to create a receipt. Here too we first create a receipt 
  batch and a receipt within. And when we create a receipt batch, we need to 
  provide comprehensive receipt hierarchy information. The receipt hierarchy 
  information is given below.
  
    receipt source  ("ra_batch_sources")
     ||
    receipt class  ("ar_receipt_classes")
        //            
    payment method         bank information ("ap_bank_branches") 
   ("ar_receipt_methods")        (bank,bank branches)
  ("ar_reciept_method_accounts")          ||
               bank accounts 
               ("ap_bank_accounts")
       
  (Here we use ap_bank_accounts, because banks are owned by AP).
  All the receipts fall into two main categories which are cash and miscellaneous 
  and when a receipt is created,it goes into "ar_cash_receipts_all" with different 
  types. */
  
  /*Receipt Classes to Receipt Methods is a one-to-many relationship. That is,say 
  if we have receipt class of type DISCOVER. then we can define multiple receipt 
  methods(or payment methods), using the same screen. The ex of payment methods 
  are DISCOVER-NT (Discover Northern Trust), DISCOVER-BOFA(Bank of America) etc.  
  */
  
  /*Banks : We can create banks from the menu item
       Setup => Receipts => Banks 
   
  When we define the banks, we can create any type of bank, Internal ,Customer or
  Supplier. Internal bank is a remittance banks and it and means it is defined for your 
  own company purposes. That is you use that bank for your remittance purposes. */
  
  Each receipt is associated with a receipt class/payment method. When we 
  create a receipt class/payment method, we always associate it with a bank
  and that is remittance bank. That is in that from, only banks that we see are 
  the remittance banks(and not customer or supplier banks). 
  
  /* COMPLETE A RECEIPT :
  Just as we complete a transaction(i.e invoice,credit memo etc) and then it would 
  appear in the ar_payments_schedules_all table, even receipts can be completed. 
  That is a receipt will also have a status of (OP,CL) etc. ie. if we have a receipt 
  of amount say $10, then the receipt in ar_payment_schedules will look like below. 
  Hence a receipt entry in payment schedules table will be exactly identical to a 
  transaction. Hence as long as if there is any balance for a receipt(i.e unapplied 
  balance), then that particular receipt will still be open OP.*/
  
  select amount_due_original,amount_due_remaining,status,class,customer_id,
     gl_date_closed,trx_number,trx_date,gl_date
  from ar_payment_schedules_all 
  where cash_receipt_id = 29925610
  
  /*If the customer name is left empty , the status would be UNID (unidentified receipt) 
  and if it is provided the status of the receipt is UNAPP (unapplied). Now if the receipt 
  is also applied for a particular invoice,then the status is Applied. And when we 
  distribute the invoice (or any trxn type), i.e when we mention, to which GL account this 
  particular invoice amount should go to, and to which particulary receivable gl account 
  this should go to, the information goes into the "ra_cust_txn_line_gl_dist" table. (Look 
  for the spreadsheet explaining all the details of the accounting entries in AR in a flow).
  
  For applied receipts,ie. receipts for which we know the corresponding invoice and the customer
   An applied receipt will reduce the customer balance by that amt. 
 The journal entries for say $100 would be
   
   Receivables             :    $100 (cr)  $0(db)
   Cash (Bank Asset Account)     :    $100 (dr)  $0(cr)
   
   (It is important to note that above account is cash account which is different 
 from the cash clearing account that is used in the Accounts Payables).
  Hence the queries that we can use to see the data are given below.
  */ 
    select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-'
     ||a.segment5||'-'||a.segment6 acct_code
        ,a.* 
    from gl_code_combinations a 
    where segment1 =01
    and segment2 = 0000
    and segment3 = 0000
    and segment4 in (14300,10210)  --14300(4586) cash account, 10210(4597) unapplied account.
    and segment5 = 0000
    and segment6 = 0000
    and segment7 = 0000
    and segment8 = 0000

  -- Any transaction batches can be obtainted from this query.
    select * 
  from ra_batches
  
   select batch_source_id, name,description,org_id,status, batch_source_type
   from   ra_batch_sources 

   -- Any receipt will go into this table.
    select cash_receipt_id, amount, currency_code,pay_from_customer,status,type,
 receipt_number,receipt_date,org_id     
 from ar_cash_receipts_all
 order by receipt_date desc
  
   -- The gl account distributions can be seen from this table.
   select * -- source_type, source_id, code_combination_id, amount_dr,amount_cr,
    creation_date,last_update_date,org_id
   from   ar_distributions_all
   where  code_combination_id = 4597

  /*Difference between Unapplied and On-account receipts 
  
  Both unapplied and on-account DO NOT reduce the customer balance. 
   It does not impact a business, if you leave an amount in unapplied or 
   onaccount. Both of them DO NOT reduce the customer balance.
   It is just a business decision, where in we can decide to have the amount 
   either in unapplied or on account.For ex, if we do not know the customer 
   name while we create a receipt, we can optionally leave that amount as 
   unapplied(which is like that to start with).
   Similarly if you know the customer for a particular receipt, then you can 
   optionally keep that amount in on-account by going to the applications screen.
   An ex of a On-Account receipt are prepayments and deposits.
   If the amount is in unapplied status, we can apply that amount to any 
   debit items like invoice. However if the amount is in on-account, then we cannot 
   apply to any debit items. We have to first unapply the on-account and then 
   apply to any debit items. 
   
  Unidentified receipts, receipts for which dont know both the invoice and customer information.
  */

-- APPLY A RECEIPT TO AN INVOICE 

  select customer_trx_id,cash_receipt_id,payment_schedule_id, class,
     customer_id,trx_number,trx_date,customer_site_use_id, 
   selected_for_receipt_batch_id btc_id,acctd_amount_due_remaining amt_due
   ,acctd_amount_due_remaining,amount_due_original, amount_due_remaining,
   amount_applied,amount_credited,org_id,reserved_value,status
  from   ar_payment_schedules
  where  customer_trx_id = 1034

 /* Ar_payment_schedules will record both the transaction and receipts. In the case
  of the transactions,the cash_receipt_id and other receipt related columns are null. 
  And in the case of the receipts, the customer_trx_id, trx_number and other 
  transaction related columns are null.In either case, the status column will indicate 
  whether the transaction or receipt is still open or not.*/
  
   select * from ar_payment_schedules_all

   select amount, receipt_method_id, customer_bank_account_id, customer_bank_branch_id,
   customer_site_use_id, receivables_trx_id, receipt_number,comments, last_update_date
   from   ar_cash_receipts_all
   order by last_update_date desc
   
 /* Once we APPLY A RECEIPT to a particular transaction like INVOICE, we can see 
   it from the following table */ 
   select cash_receipt_id, applied_payment_schedule_id, applied_customer_trx_id,
       payment_schedule_id
         acctd_amount_applied_from, amount_applied, amount_applied_from, set_of_books_id,
        customer_trx_id,status, acctd_amount_applied_to
      applied_customer_trx_line_id
 from ar_receivable_applications_all 
 where status ='APP'
 and  cash_receipt_id = 1327
  
  /* Invoice to Receipts is a Many to Many relationship. From UI, if we need to 
  know what are all the receipts that have paid for an invoice, then (we can get 
  the receipt trx number).
   Transactions Summary => Installments => activities. 
   If we need to know the all invoices that a receipt has paid for,then go to
     receipt => applications. */
  
/*Apart from the main table AR_PAYMENT_SCHEDULES, there are some other tables 
which might get updated. They are given by the following queries. */

   select line_id, source_id, source_table, source_type,
       source_type_secondary, code_combination_id, amount_dr, amount_cr, 
    acctd_amount_dr, acctd_amount_cr
   from   ar_distributions_all
   order by last_update_date desc
   
   select * 
   from    ar_cash_receipt_history
   order by last_update_date desc
   

 /*  Q: Unable to apply a receipt to an invoice. the following query does not 
 give any records because dont know why the ar_payment_schedules table is storing 
 the customer_trx_id as -1 while the ra_customer_trx table stores the
 actual transaction id and no way they can match. 
  A: This problem can be solved once the transaction (i.e invoice)
 is complete and if it is complete it would definitely figure in the ar_payment_schedules.
  */
 
 /* Very Important Point. In Payables, the payments are always tied to a bank 
  account(and gl accounts). Similarly in Receivables, in the receipt batches we 
  see the bank account to which the receipts go into.
  This information is useful for the reconciliation purposes.*/

/* The following query is very useful as it joins all the related tables
 and in fact used by one of the 11i forms*/
  SELECT *
  FROM 
  hz_cust_site_uses site_uses,
  hz_cust_accounts cust_acct,
  hz_parties party,
  ra_cust_trx_types ctt,
  ar_lookups lu,
  ar_payment_schedules ps,
  ra_customer_trx trx
WHERE 
  site_uses.site_use_id = ps.customer_site_use_id and
  cust_acct.cust_account_id = ps.customer_id and
  cust_acct.party_id =    party.party_id and
  ctt.cust_trx_type_id = ps.cust_trx_type_id and
  ps.selected_for_receipt_batch_id is null and
  ps.reserved_type is null and
  ps.reserved_value is null and
  ps.class not in ('GUAR', 'PMT') and
  --ps.invoice_currency_code = decode(ps.class, 'CM',:2,    ps.invoice_currency_code) and
  ps.status = 'OP' and
  ps.class =    lu.lookup_code and
  lu.lookup_type = decode(ps.class, null, 'INV/CM',    'INV/CM') and
  ps.customer_trx_id = trx.customer_trx_id   
  
  /* Now having created the Invoices , Receipts etc in Receivables, we can 
  transfer these transactions and receipts to GL. Using the step  
         Interfaces => General Ledger   
  Here it is important to note , we have to give the GL period start and 
  end dates , typically these are the month start and end dates. The above step 
  will spawn the concurrent program  "General Ledger Transfer Program" 
  which will,in turn, spawn these programs,
           "Revenue Recognition"
           "Journal Import"  (If the option is yes in the parameters window)
           "Updating Posting Control :
         The moment we run the GL transfer program, these transactions are moved to the GL Interface table
      and at the end of that process, the "Update Posting Control" process will kick 
      off and it will back populate the posting_control_id in this table. This 
      does not mean that the gl posting is done for this transaction. 
      Similary in the case of receipts the ar_cash_receipt_history_all table will get updated with 
         the corresponding posting_control_id */

-- So effectively the gl_date in the following tables will take of the gl transfer. 
      Transactions        ==>> ra_cust_trx_line_gl_dist_all
   Receipts  ==>> ar_cash_receipt_history_all
   adjustments  ==>> ar_adjustments_all     
       
   select * 
   from   gl_je_batches
   where  creation_date = (select max(creation_date) from gl_je_batches)   
  
  /*At this point we have to post the data. This can be done in General Ledger 
  application using the GL Super User responsibility and using the navigation path  
     "Journals => Post". 
   Find the right batch and post it. 
  Interestingly, there are two ways we can do the GL Posting, 
   1) Journal => Post which kicks off a conc program
   2) Run the "Program - Automatic Posting" which will take a predefined autopost 
   set id (see GL notes) 
  Once the posting process is succesfully completed, we can see the data from the below query.
  */

   select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4
       ||'-'||a.segment5||'-'||a.segment6 acct_code  ,a.* 
    from gl_code_combinations a 
    where segment1 =01
    and segment2 = 0000
    and segment3 = 0000
    and segment4 in (11100,24100) -- 11100 is receivable, 24100 is revenue. (4587,4589)
    and segment5 = 0000
    and segment6 = 0000
    and segment7 = 0000
    and segment8 = 0000
 
-- Watch for the above code_combination_id's in the below queries results.
   select * --set_of_books_id,code_combination_id, period_name
   from   gl_balances
   where  last_update_date = (select max(last_update_date) from gl_balances)
   --where  set_of_books_id = 82

  -- How to apply Discounts in AR:
  /*We can apply discounts in AR(with out using the OM) byusing the payment terms 
  in AR. For ex let us say if we have a payment term like "2% 15 Net 30" which 
  means that the payment is due with in 30 days from the invoice date and the 
  customer will get 2% discount if the payment is applied within first 15 days(this 
  is usually done by creating discount lines in that payment terms), then when 
  we go to apply this receipt to the invoice and if the application date is 
  with in 15 days, then the discount field is automatically populated with 
  the discount amount and the remaining amount goes into the unapplied account.
    
  Now from an accounting standpoint, here the invoice is closed,with the same distributoin.
  However in the receipt distribution, we can see that there is a new distribution 
  line which is Earned Discounts which is basically receivable activity ,
  corresponding to a particular GL account. So there is an additional journal line.
   
   So think of it this way. The invoice balance of $100 has been closed by a customer 
   receipt of $98 and a journal corresponding to receivable activity Earned discount
   has been applied to the additional $2. If the customer sent a $100 receipt, the $2
   will be on unapplied amount. 
  */   
     
  /* Customer OPEN Balance and Transactions.
  At any point of time, if we need to have all the customer transactions and any 
  open balance, we can get it from the menu 
      Collections => Customer Accounts
  /*

  AutoLockBox Feature : LockBox Functionality
  ------------------------------------------
  Normally for any company doing business , they have a Accounts Receivable(AR) 
  system. That is ,they receive all kinds  of receipts like cash, checks, credit cards,
  direct debits etc. However the company by itself would not receive all these receipts. 
  Generally all these receipts would go to a different PO box address typically known 
  as Lockbox and from there, the bank would collect all these receipts, deposit money,
  summarize them and then send that information to the company. All these transactions 
  go into the company's receivable system. So this information would come as a batch of 
  records with count and amount. However for all these transactions, the actual cash is 
  already remitted into their bank.
  Usually when we setup a lockbox in the AR system, we have to provide a lockbox number. 
  This is a reference to the number of origin of the bank data file. Basically, you 
  should be able to use any number you want, as long as the number is unique. So no,
  it's not a mandatory number that should be provided by your bank.

  In Oracle Receivables, the Lockbox process would mainly consist of three steps and 
  they are...
   1. Using the sqlloader, import the flat file obtained from bank in a specific format 
      (ex EDI 820,BAI). Once the import process completes, the data will be loaded into 
   AR_PAYMENTS_INTERFACE table. And the process also generates the Lockbox execution report.
   2. QuickCash step: Lockbox Validation. The data that is loaded into AR_PAYMENTS_INTERFACE 
      table is now validated by this process and the validated results are written to 
   AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL tables and the log 
   is written to Lockbox execution report. If the validation fails, then the process will
   not write any data into the interim table and we need to fix the report errors.
   3. Post QuickCash. Once the data is validated, this validated data is written into the 
      actual AR Receivables tables(like AR_CASH_RECEIPTS etc) and we also get a QuickCash 
   Execution Report. Also this program will look for the AutoCash Rule sets which  (i.e 
   whether the oldest invoice or the highest invoice etc).

    So we can summarize this as 
        AR_PAYMENTS_INTERFACE  ==>  AR_INTERIM_CASH_RECEIPTS => AR_CASH_RECEIPTS_ALL etc.
    
    The Lockbox process, is where the bank gives us the statement periodically 
    consisting of complete receipts and we try to apply to the debit items/on account 
    using the autocash rules.(i.e whether the oldest invoice or the highest invoice etc).
    (However for reconciliation purposes, the bank can provide all the activity completely 
    until that point, which includes the payments and receipts. And if you try to start 
    the AutoReconciliation process in Cash Management, it will match it against the 
    receipts in the receivables and invoices in payables systems).
    
    Lockbox processing is a little bit different from the regular receipt processing. 
    In the case of lockbox,the receipts are created after we get the file from the bank. 
    However in the case of regular receipt processing, first we create the receipt, remit 
    and clear and then the cash is deposited in the bank. In the lockbox, the cash
    is already deposited in the bank and the bank sends the file to us and then we create 
    the receipts in our AR system.
    
    The following are the steps involved in how the Autolockbox applies receipts :
      Receivables applies the receipts in a lockbox to the transactions during 
   the PostQuickCash process.
*/

-- If you create a lockbox,then it would show up in this table 
  select lockbox_id,lockbox_number, status,bank_origination_number, 
  batch_size,telephone,receipt_method_id, org_id
  from   ar_lockboxes_all 
  order by lockbox_id 
  
  /* Actually the hierarchy is that for each lockbox, we can multiple transmissions. 
  And for each transmission, the bank can send in multiple batches. Actually we may 
  not have any values for batch name and amount as it is not mandatory
            Lockbox => Transmission => Batch
  */
  
  INSERT INTO ar_transmissions_all
       (transmission_request_id, transmission_id,transmission_name, trans_date,
    count, amount,status, requested_lockbox_id, requested_gl_date, org_id, 
    requested_trans_format_id,created_by,creation_date,last_updated_by,
    last_update_date)
  VALUES (922,822,'MyTransmission22',sysdate,1,500,
     'OP',1200,SYSDATE,44,1080,
     1626,sysdate,1626,sysdate)
 
  select transmission_request_id,transmission_id, transmission_name,trans_date,
     time,count, amount,status, requested_lockbox_id, requested_gl_date, 
   org_id , requested_trans_format_id,creation_date
  from   ar_transmissions_all
  where  requested_lockbox_id= 1200 and transmission_name ='MyTransmission22' 
     --- order by trans_date desc
  --  where  requested_lockbox_id in(1200,1020) and transmission_name in 
  --  ('NTDP081202','MyTransmission3') 
  --- order by trans_date desc
  AND     count >0 
------

  select * from ar_transmission_formats where transmission_format_id =1020
 
  -- ar_trans_record_formats   ar_payments_interface_v

/* Just a word on the record types in Lockbox processing: Each lockbox will have 
   specific file format which will be sent by the bank. Oracle provides some standard 
   predefined transmission formats like DEFAULT (Which is of the type BAI - Bank 
   Administration International format). This is made up of a set of record types. These 
   record types are all predefined and when we create a  transmission format we define 
   the sequence of these record types according to what we want. So we can define any 
   kind of transmission format that we want, that suits our business needs. Ex of the record 
   types are Transmission Header, Transmission Trailer, Lockbox header, lockbox
   trailer, Overflow payment, Payment(or receipt), Service Header. 
 
   Just as we have a set of predefined record types, we also have a set of predefined 
   field types. What we do is we pick a record type ,say ,Payment and click on the 
   tranmission fields and here we choose what fields and the sequence of those fields. 
   Exs of field types are transit routing number,account,remittance amount,deposit 
   date etc
 */

 insert into ar_payments_interface_all
     (transmission_request_id, transmission_id,transmission_amount,record_type,org_id,
         customer_number,customer_id,
   --batch_name, batch_amount,
         lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count,
         receipt_date,receipt_method_id,check_number,item_number,
         remittance_amount,remittance_bank_name,remittance_bank_branch_name,
         --invoice1,amount_applied1, 
   gl_date, creation_date, last_update_date, deposit_date,
   transmission_record_id,currency_code, transmission_record_count)
  values (999,888, 1000,1,44,
     296577, 309319,
  1200,1,500,1,
  '24-MAR-2006',1793,'CHK13',1,
  500,'BOA','Mountain View',
  --'1190011566',500,
  sysdate, sysdate, sysdate , sysdate,
  1,'USD',1)
  
  select batch_name, batch_amount,
     transmission_id,transmission_amount,record_type,org_id,
         customer_number,customer_id,
         lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count,
         receipt_date,receipt_method_id,check_number receipt_number,
         remittance_amount,remittance_bank_name,remittance_bank_branch_name,remittance_amount,
         invoice1,invoice2 ,
   status,
   gl_date, creation_date, last_update_date, deposit_date
   ,transmission_record_id,record_type, currency_code,
   receipt_method_id,item_number,transmission_record_count 
    from   ar_payments_interface_all  --where lockbox_number is not null
  where  transmission_request_id = 902
    
  COMMIT;
 
/* During the Validation phase the lockbox processing will check for different things like , 
   --  Ensure that the receipt number is there. (i.e the check number)
   --  Item number should be there , which should be unique, in a batch, transmission or lockbox. 
   --  Receipt has invalid applications
   
   Once all the validation is complete , the rows are inserted into the ar_interim_cash tables.
*/ 

--  Now let us insert a row in ar_payments_interface_all with no customer number information  or the combination
--  of the (routing#,account#) and with the  AutoAssociate being set to true.
  
   insert into ar_payments_interface_all
     (transmission_request_id, transmission_id,transmission_amount,record_type,org_id,
         --customer_number,customer_id, 
   --transit_routing_number, account,
   --batch_name, batch_amount,
         lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count,
         receipt_date,receipt_method_id,check_number,item_number,
         remittance_amount,remittance_bank_name,remittance_bank_branch_name,
         invoice1,--amount_applied1, 
   gl_date, creation_date, last_update_date, deposit_date,
   transmission_record_id,currency_code, transmission_record_count)
   values (922,822, 1000,1,44,
     --296577, 309319,
  1200,1,400,1,
  '24-MAR-2006',1793,'CHK922',1,
  400,'BOA','Mountain View',
  '1190011566',--400,
  sysdate, sysdate, sysdate , sysdate,
  1,'USD',1)
  
 /* Now run the second step of Validation. Now since the customer information
  is missing and since the AutoAssociate is set to true, then it should go by 
  the lockbox setting, "Match Receipt by" and if it is transaction number,
  then it associates this receipt to that particular transaction. 

  The following 4 points summarize the complete functionality of how the 
  lockboxes identifies and applied to receipts.
  
  If the customer# or MICR(routing#,account#) is provided,
   then the customer is identified.
  
  If the customer# or MICR is not provided, 
   AND Autoassociate is set to YES (and say the invoice# is provided) then 
 the lockbox will try to apply the matching rules.
    and apply the receipt amount to that particular invoice.
    So in this case, the customer is identified and the status of the 
  receipt is APP.
    (If the invoice amount is already 0, and if the overapplication 
  profile option is No, then the status of the receipt will be UNAPP), 
  otherwise the receipt will be applied and the status will be APP. 
  
  If the customer# or MICR is not provided, 
   AND Autoassociate is set to YES (but invoice# is not provided) 
    So in this case, the customer is NOT identified and the status of 
  the receipt is UNAPP.
  
  If the customer# or MICR is not provided, 
   AND Autoassociate is set to NO (so no matching rules applied etc)
    So in this case, the customer is not identified and the status of 
  the receipt is UNAPP.
  --  
 If the profile option AR:OverApplication of Invoices is set to 'Yes', then 
 the invoice balance can go into negative after application.If it is set to 
 No,and if the invoice balance is already 0, then the receipt amount will be 
 in UNAPP status.  
  */

  /* Here one important point is that even if the receipt is unidentified, the 
  column "status" will show a value of UNAPP ,but the "special_type" column will 
  have a value of UNIDENTIFIED.*/
  
   select cash_receipt_id,amount,pay_from_customer,type,status, special_type,
  receipt_number,gl_date
   from ar_interim_cash_receipts_all 
  
/* There are 2 interim cash tables in lockbox. Once a receipt is validated it 
   figures in the  table ar_interim_cash_receipts_all and if there are any 
   applications, then they go into the ar_interim_cash_rcpt_lines_all table. 
   So if a particular receipt is applied against 2 invoices, then the lines 
   table will have 2 lines,corresponding to header cash_receipt_id 
   ar_interim_cash_receipts_all.*/

  select * 
  from ar_interim_cash_rcpt_lines_all
  
  /* Now after we run the post quickcash program, these receipts are transferred 
  from the interim cash tables to the cash_receipt table ar_cash_receipts_all and 
  ar_receivable_applications_all tables. Interestingly, the same cash_receipt_id 
  in interim tables is preserved in the ar_cash_receipts_all table.*/
    
  select * from ar_cash_receipts_all where receipt_date >= '24-MAR-2006' 
  ORDER BY creation_date desc  
  
  /* Generally sometimes in some of the columns in tables, some of the values might 
  be strings like OOB, or constants like 1,2 and we dont know exactly what they mean. 
  In such case, we can try to get the meaning of
  those from the lookup tables. For ex,  */
   
  select * from ar_lookups where meaning = '8' --lookup_code like '%TYPE%' --code = '8'

 /* Overflow Indicator indicates whether there are any further records for this 
 particular receipt. Let us say a particular receipt is there,apart from the usual 
 header and trailer,you might have the payment record type which will consist of 
 fields like (lockbox#,routing%,customer bankacct#,amt,date,check# etc).
 
 Now the overflow record will consist of invoice information etc,i.e info like
 (receipt#, invoice#, amount applied,overflow indicator etc)
 Typically  the overflow indicator value of 0 indicates that there are further 
 overflow records and a value of -9 indicates that it is the last record.
 */
  
 SELECT arm.NAME, arm.receipt_method_id,
 arc.creation_method_code, arm.NAME,
        arm.receipt_method_id, arc.creation_method_code
   FROM ar_receipt_methods arm,
        ra_cust_receipt_methods rcrm,
        ar_receipt_method_accounts arma,
        ap_bank_accounts aba,
        ar_receipt_classes arc
  WHERE arm.receipt_method_id = rcrm.receipt_method_id
    AND arm.receipt_method_id = arma.receipt_method_id
    AND arm.receipt_class_id = arc.receipt_class_id
    AND arma.bank_account_id = aba.bank_account_id
    AND aba.set_of_books_id = 1
    AND arm.receipt_method_id = 1002

  /*Before we talk about the Automatic receipt creation process let us talk about
   the Manual Receipts.
  
  Manual receipts are those which do not require any remittance. Let us explain this. 
  Typically when a receipt is automatically generated i.e the Automatic Receipt 
  Generation Program has generated that receipt. That kind of receipts will require 
  the remittance, i.e the receipt has originated from the AR side. These receipts 
  are called automatic receipts.
  
  Any other receipts are called Manual receipts; i.e the after the remittance has 
  happened, the receipts are created either thru the lockbox or entered manually thru 
  the form.
  
  See ,receipts for ex, checks, are never sent directly to the AR dept and they never 
  enter manual checks in the form. Receipts typically checks are sent to a 
  location called lockbox and from there they go to a bank and the bank prepares and 
  sends the remittance advise to the customer banks,collects the money and then sends 
  the lockbox file,containing the payment information to the company AR dept. This 
  lockbox file is then loaded into our systems. All such receipts created are of payment 
  type Manual.*/    
    
-- Step by Step Lockbox Testing Process :     
------------------------------------------    
 select transmission_request_id,transmission_id, transmission_name, trans_date,time,
   count, amount, status, requested_lockbox_id, requested_gl_date, org_id , 
  requested_trans_format_id,creation_date
  from   ar_transmissions_all
  where  transmission_name = 'NTDP09142006'
  order by creation_date desc
  
  -- Get the transmission id from the above query.  
  select *  --count(*)  -- 340
  from   ar_payments_interface_all
  WHERE  transmission_id = 49302

/* The number of lines that are in the flat file is the number of records that we see
   in this table. The verisign flat file : The file itself is consisting of a different 
   number of batches, with each batch consisting of fields like that batch amount, 
   control count etc.
   
   That is for each set of records,called a batch, there will also be a record which 
   gives the sum of that batch receipts. This record is preceded by a record identifier 7.
       Similarly for the entire transmission, there will be another record which 
   will give the sum of all the receipts corresponding to the entire transmission.Similarly 
   this record is preceded by a record identifier 7. 
   */
   
   select Batch_name, Batch_amount
   ,lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count
   ,Transmission_id,Transmission_amount,Record_type,Org_id
         ,customer_number,customer_id
         ,receipt_date,receipt_method_id,check_number receipt_number
         ,remittance_amount,remittance_bank_name,remittance_bank_branch_name,remittance_amount
         ,invoice1,invoice2
   ,status
   ,gl_date, creation_date, last_update_date, deposit_date
   ,transmission_record_id,record_type, currency_code
   ,receipt_method_id,item_number,transmission_record_count 
  from   ar_payments_interface_all  --where lockbox_number is not null
  where  transmission_id = 49302

  /* Some times you might get an error" invalid applications" which means the 
  invoice information/number that appears in the overflow record in not there in the 
  AR system and hence the lockbox process does not know to whom it should be applied.
  
  Also if the period is not open, you might get an error. */
  
 /* The best way I believe is to open up the lockbox file,which is usually a text 
 file and open up the transmission formats from and see what are the payment and 
 overflow record identifiers. The payment record contains the receipt information while 
 the overflow record contains the invoice information. Once we do that we need to see
 what is the starting and ending positions for these fields,pick up the invoice# and 
 receipt# and then pull up those in the Oracle applications.*/
  
  select sum(remittance_amount),sum(batch_amount), batch_name 
  from   ar_payments_interface_all 
  where  transmission_id = 49302
  group by   batch_name
  
  select remittance_amount, 
    batch_amount
  ,batch_name 
  from   ar_payments_interface_all 
  where  transmission_id = 49302 and batch_name = 7
  
  /* Even right after the first step is completed, the transmission table can show 
   an error of OOB (out of balance) what this means is that the sum amounts are not 
   adding up to the individual amounts. For ex,Batch amount column may not be adding up 
   to the sum of the remittance amounts for a particular batch.
   Lockbox amount may not be adding up to the sum of all the receipt amounts. 
   Transmission record count may not equal the total number of records,i.e let us say 
   if the flat file has in total 340 lines, the transmission trailer line should show a 
   value of 340. The above point can be simply verified by running the below query.
  */
  select sum(remittance_amount) sum_rmt_amount,
     sum(batch_amount)   sum_batch_amount, 
   batch_name    batch_name
  from   ar_payments_interface_all 
  where  transmission_id = 49302
  group by   batch_name
  
  /* IMPORTANT: 
  Receipt number and payment number is always part of the lockbox file. If the 
  receipt number is already existing in the AR, then it fails. And receipt number 
  should never be system generated (i.e it should not be generated by a document 
  sequence etc)*/
  ----
  
  update ar_payments_interface_all
  set  gl_date = gl_date + 30
  where  transmission_id = 49302
  
  ---- Once the validation part completes,the records should be found here.
  select * from ar_interim_cash_receipts_all
  
  -- what kind of records are found here.
  select * from ar_interim_cash_rcpt_lines_all
  
  /* Now during the 3rd step, the post quick cash completes and records should go 
    to AR and the applications should happen, in ar_receivable_applications_all  */
  select * from ar_cash_receipts_all where creation_date >= trunc(sysdate) -- 140

  select * from ar_cash_receipt_history_all where creation_date >= trunc(sysdate) --140

  -- We can find out which receipts are created by going to the 
      Receipts => Batch Summary
   
 /*and there query by the Transmission Name which is coming from all along.Here 
 we see that the receipt batches are created consisting of the unidentified and 
 unapplied receipts.Each record corresponds to a transmission batch coming from 
 the file. We can try to correlate the data here with the flat file and ,open the 
 receipts and try to correct the data,like enterting the customer name etc.
 */
   
/* AUTOMATIC RECEIPT CREATION PROCESS
--------------------------------------
 The criteria for creating the Automatic receipts

   Firstly the paying customer information(like the bank account information) on 
        the transaction form should be available.
   The transaction should be complete and for the associated customer, the currency 
       information should be available.
   The payment term should be immediate (or only on the due date the auto receipt 
       is created).
   Only after the Creation, Approval And Formatting the receipt appears in the 
     ar_cash_receipts_all).
   The automatic receipt creation program will first create the receipt batch 
       and then creates the receipt as part of that. 
   The receipt history table will have the batch id.
    How is the PSON (payment server order number) populated in ar_cash_receipts_all
    What is the difference between the auto and manual creation of remittances.
*/

  select rowid,a.* from ra_customer_trx_all a where trx_number = '11048'

   /*
   1).Hence to create an automatic receipt, first go to the batches screen using the menu option
      Receipts => Batches 
      Pick the automatic option And Click on the Create button.
   2).Now here pick or enter the transaction number for which you want the 
      automatic receipt to be created. This will kick off the "Automatic Receipt 
      Creation Process" program.
   3).A receipt has to go thru the Creation, Approval and Formatted option. Hence 
      choose those options if required.  and only after they are Approved and Formatted, 
      they appear in the Cash Receipts table.
      
   Most important,the following query should yeild the record for the 
     Automatic Receipt creation to create a record */

   SELECT -- cust_cpa.*, 
     cust_cpa.currency_code , site_cpa.currency_code site_cpa_cur,ps.payment_schedule_id,
          ps.cash_receipt_id,
          ct.paying_customer_id,
          ct.paying_site_use_id,
          ct.payment_server_order_num,
          ps.due_date,
          ps.amount_due_remaining,
          ct.customer_bank_account_id
   FROM   hz_customer_profiles cust_cp,
          hz_customer_profiles site_cp,
          hz_cust_profile_amts cust_cpa,
          hz_cust_profile_amts site_cpa,
          ra_customer_trx ct,
          ar_payment_schedules ps
   WHERE  ps.status                     = 'OP'
   AND    PS.gl_date_closed             = TO_DATE('4712/12/31', 'YYYY/MM/DD')
   AND    ps.selected_for_receipt_batch_id IS NULL
 --  AND    ps.due_date+0                 <= TO_DATE('28-AUG-2005') + TO_NUMBER(0)
   AND    ps.invoice_currency_code      = 'USD'
   AND    ps.customer_trx_id            = ct.customer_trx_id
   AND    ps.reserved_type             IS NULL
   AND    ps.reserved_value            IS NULL
   --AND    ct.receipt_method_id          = 1035
   AND    ct.paying_customer_id         = cust_cp.cust_account_id
   AND    cust_cp.site_use_id      IS NULL
   AND    cust_cp.cust_account_profile_id  = cust_cpa.cust_account_profile_id(+)
   AND    cust_cpa.currency_code(+)     = 'USD'
   AND    ct.paying_site_use_id         = site_cp.site_use_id(+)
   AND    site_cp.cust_account_profile_id  = site_cpa.cust_account_profile_id(+)
   AND    site_cpa.currency_code(+)     = 'USD'
   AND    (NVL(ps.amount_in_dispute,0) = 0 OR (NVL(ps.amount_in_dispute,0)  != 0
            AND NVL(site_cp.auto_rec_incl_disputed_flag,cust_cp.auto_rec_incl_disputed_flag) = 'Y'))
   AND    ps.trx_number = '444'
   FOR UPDATE OF ps.selected_for_receipt_batch_id;

   /* Most importantly,Once the automatic receipt process will pick a transaction 
   for the receipt creation, then in the payment schedules table for that particular 
   transaction, the select_for_receipt_batch_id will be populated with the batch id 
   of the receipt batch. Also that transaction is closed (with the amount due 
   remaining being made 0) after applying this receipt to that particular transaction. 
   This can be checked from the below queries. 
   */

  select * from ar_payment_schedules_all where customer_trx_id = 2800398
  
  select * from ar_receivable_applications_all where applied_customer_trx_id = 2800398
  
  select * from ar_batches_all  
  where 1=1
  and   batch_date >= trunc(sysdate)
  --and batch_id = '7810'
  
  select * from ar_cash_receipts_all where cash_receipt_id = 2195031
  
  select * from ar_cash_receipt_history_all where batch_id  = 7814
  
  select * from ar_cash_receipt_history_all  where cash_receipt_id = 2195031
  
  select * from iby_trxn_summaries_all where tangibleid = 'AR_177807'

  /*Now the receipt is created, it needs to be remitted. Remittance is the process 
  of going thru the payment processor and depositing the customer money into our 
  bank. Interestingly there is a record in the iby table even before the receipt is 
  remitted. And the selected_remittance_batch_id is populated in the ar_cash_receipts_all 
  for that particular cash receipt. (ar_boe_auto_receipts_v). Just like the receipt, the
  remittance process also goes thru the Creation, Approval and Formatting options. 
  Hence for that go to the
  Receipts => Remittances  -- Enter the payment information
  and click on the AutoCreate.  This will kick off the 
  "Automatic Remittances Creation" program.
   
  The below query should be successful for the automatic remittance process to succeed.*/
  
  SELECT selected_remittance_batch_id,a.* 
  FROM ar_cash_receipts_all a
  where receipt_number = '11048'
  
  SELECT  /*+ LEADING (crh) INDEX (crh AR_CASH_RECEIPT_HISTORY_N6) */ cr.cash_receipt_id,
              cr.amount
        FROM ar_cash_receipt_history crh,
             ar_cash_receipts cr,
             ar_payment_schedules ps,
             ar_receipt_classes rclass,
             ar_receipt_methods rm,
             ar_receipt_method_accounts rma1,
             ar_receipt_method_accounts rma2
       WHERE crh.status = 'CONFIRMED'
         AND crh.current_record_flag = 'Y'
         AND crh.cash_receipt_id = cr.cash_receipt_id
         AND NOT EXISTS
         (SELECT 1 FROM ar_lookups l
          WHERE NVL(cr.reversal_category,'~')    = l.lookup_code
          AND l.lookup_type           = 'REVERSAL_CATEGORY_TYPE')
         AND cr.currency_code = 'USD'
         AND cr.cash_receipt_id = ps.cash_receipt_id(+)
         AND cr.receipt_method_id = rm.receipt_method_id
  AND cr.selected_remittance_batch_id is null
         AND (( cr.amount >= 0) OR
              (cr.type = 'MISC' and cr.amount < 0))
         AND rm.receipt_class_id = rclass.receipt_class_id
         AND rma1.receipt_method_id = cr.receipt_method_id
         AND rma1.bank_account_id = cr.remittance_bank_account_id
         AND rma2.receipt_method_id = rma1.receipt_method_id
        -- AND rma2.bank_account_id = :bs_remit_account_id
         AND cr.receipt_number = '-2500'
          FOR UPDATE OF cr.selected_remittance_batch_id

  /*Once the remittance process completes, a 'ORAPMTCAPTURE' record will be created 
  in the ipayment table i.e iby_trxn_summaries_all table, and the tangibleid from 
  that table is back populated into the PSON of the cash receipts table. */
  
  /* The typical next step in the standard oracle receivables workflow is to clear 
  the receipts,which is done as
     Receipts => Clear/Risk Eliminate
  and after entering the right parameters, this will kick off the  
  "Automatic Clearing for Receipts" program. 
  */
   
  select * from iby_trxn_summaries_all where tangibleid = 'AR_177807'
   
  select * from fnd_concurrent_requests where request_id = 1718284
  
  /*Trouble shooting the Automatic Receipt Creation Process : 
  What to check when a transaction is not selected during automatic receipt 
  creation? and the following notes on metalink can help.293031.1 & 227025.1
  */

  
 CUSTOMER PAYMENT TYPES. 
  Customers can pay by 
   Bank Account => Cash, Check, ACH payment methods
   credit card => Credit Card payment method
   the receipts can be coming by Lockbox process.(No remittance) 
 
 /* RECEIPT CREATION FROM CUSTOMER BANK ACCOUNT DETAILS.

  Before you create anything, ensure that the set of books and operating unit 
     is specified correctly and to US.
  Define a Bank, Branch and Account (of Type Customer). This is a customer bank 
     account. We can optionally assign this bank account to the customer at 
   the customer bill-to site level.
  Define a receipt class which is 
         Creation Method : Automatic 
      Remittance Method : Standard 
         Clearance Method : By Automatic Clearing  
   Since this is for Bank account, give 
   the payment type as "ACH Bank Account".  Then go to the Bank Accounts form 
   and provide the remittance bank information. This is the remittance bank 
   account(i.e internal).
  Ensure you have Immediate payment terms in the system. For Immediate payment 
     terms, the due days is 0. If you already have one, no need to create a new one.
  Now Create a transaction for the above customer,provide Immediate payment term,
       give bill-to details,USD currency and paying customer information is 
   present. The most important fields are Payment method, Customer Bank,Branch, 
   Account Number,Expiration date.
  Now provide the receipt class/method that you created in step 2.   
     (It is important to understand that depending on the payment method you have chosen,
     only the corresponding customer payment type can be provided in the customer payment
     details feilds. For ex,  if you chose credit card payment method, then the 
     customer credit card acct information only can be provided. if you chose ACH payment 
     method, then the customer bank account only  can be provided)
  Come to the bank field and provide the bank that 
   is created in step 4. Enter the account number. You dont need to enter any 
   expiration date as this is not a credit card payment method.
  Enter the line detail with correct accounting distribution information.
  Complete the transaction.

  Now this transaction is ready for the Automatic Receipt Creation program. 

--  REFUNDING A BANK ACCOUNT RECEIPT.

  It is important to understand that you can only refund a receipt after it has 
  been remitted (otherwise you can simply cancel or delete the receipt,since it
  has not been remitted).
  
  Refunding a receipt(generated from ACH), starts from the Credit memo. Pull up
  the invoice in the Transaction work bench.
  Issue a credit memo for this transaction. Assume that the invoice has been completely
  paid by the receipt and the invoice balance is 0.
  Since the invoice balance is 0, apply the credit memo to an Electronic Refund 
  receivable activity,which immediately create a (-ve) miscellaneous receipt and the number
  is populated in the reference number etc,which can be pulled up in Receipt workbench.
  Now this refund  (or negative miscellaneous receipt) is ready for remittance
  process etc.
 


-- CREDIT CARD PROCESSING IN AR. 

 Credit card payments : As far as credit card payments are concerned, there are different
 ways credit card payments are made,

    First, the invoice is already generated and the customer is making payment thru
        the credit card, which is thru automatic receipt creation,where the customer 
  credit card payment information is available.

    Second,  the transactions are coming directly as credit card transactions,where
  there is no invoice at all.
 
-- RECEIPT CREATION FROM CREDIT CARD DETAILS.

  Before you create anything, ensure that the set of books and operating unit 
     is specified correctly and to US.
  Define a bank by name "Credit Card" with the branch name as "Credit Card". 
     Go to the Bank accounts screen and define an account by 
   providing a credit card number etc. So for each credit card customer, we will 
   have a credit card account. 
  Define a receipt class which is (and also payment method)
         Creation Method : Automatic 
      Remittance Method : Standard 
         Clearance Method : By Automatic Clearing  
   Since this is for Credit Card payment 
   method, give the payment type as "Credit Card".Then go to the Bank Accounts 
   form and provide the remittance bank information. This is the remittance 
   bank account(i.e internal).
  Ensure you have Immediate payment terms in the system. For Immediate payment terms, 
     the due days is 0. If you already have one, no need to create a new one.
  Now Create a transaction for the above customer,provide Immediate payment term,give 
        bill-to details,USD currency and paying customer information is present.   
   The most important fields are Payment method, Customer Bank,Branch, 
   Account Number,Expiration date.
  Now provide the receipt class/method that you created in step 2. Now since you 
       have given the payment type as Credit Card, immediately in the Bank,branch 
   you  will see the "Credit Card" and "Credit card" respectively. Come to the 
   account number field and provide a credit card number created in step 4 
   or enter a new cc number. Also enter the expiration date of the credit card.
  (It is important to understand that depending on the payment method that you provide
  in that field, the corresponding customer payment type can be provided in the 
  other field,i.e if you provide credit card payment method, then the customer credit card acct can
  be provide. if you provide ACH payment method, then the customer bank account only
  can be provided)
 
  Enter the line detail with correct accounting distribution information.
  Complete the transaction.
  
  Now this transaction is ready for the Automatic Receipt Creation program.
  So when the Automatic Receipt Creation program runs , this will create a receipt ,
  which can be opened up in the Receipt workbench as well.
  
  --
  Now another way of creating a credit card receipt is to go manually to the receipt
  work bench and pick the same payment method that was created above and provide all
  the customer account details. 
  ---
  
  --  REFUNDING A CREDIT CARD RECEIPT.

  It is important to understand that you can only refund a receipt after it has 
  been remitted (otherwise you can simply cancel or delete the receipt,since it
  has not been remitted).
  
  Refunding a receipt(generated from Credit Card), starts from the receipt itself. 
  Just pull up the receipt in the receipt work bench and apply the receipt to a 
  Credit Card Refund receivable activity,which immediately create a (-ve) 
     miscellaneous receipt and the number is populated in the reference number etc,
     which can be pulled up in Receipt workbench.
  Now this refund  (or negative miscellaneous receipt) is ready for remittance
     process etc.
 
  
 /*  MISCELLANEOUS RECEIPT :  
   Let us briefly talk about the miscellaneous receipts and the associated 
 details in it.  
   We can create a Miscellaneous Receipt from the form, but the activities 
   that we can create against are limited to the one corresponding to the 
   "Miscellaneous Cash"; that is we will see only activities that are created 
    in the menu item, 
         setup => receipts => Receivable Activities  
     (Corr to Miscellaneous Cash Only).
    
  What this means is that if we create a receivable activity corresponding to,
  say, "Prepayment" using      
           setup => receipts => Receivable Activities 
  and if we want to enter a receipt against that activity using the form,it is 
  not possible. This can be done only from the backend using the api's, but such 
  kind of the receipts created from backend can be viewed from the receipts form.

  A Miscellaneous receipt can have a  positive sign(+) or negative sign for the amount.
  Usually the miscellaneous receipts correspond to investment income for a company
  and hence they have a positive sign for the amount. 
  */     
    
  /* REFUNDS (& CREDIT CARD REFUNDS) IN AR :
  
    A REFUND IS A NEGATIVE MISCELLANEOUS RECEIPT.
 When a receipt is applied to a receivable activity like credit card refund, then
 a negative miscellaneous receipt is automatically created and this negative
 miscellaneous receipt is called Refund. 
 We can see this in the 
  applications window itself in the fields "application reference type" and 
  "application reference number" which will be the "Miscellaneous Receipt" text and 
  the receipt number respectively. We can try to pull up this receipt separately 
  from the receipts workbench as well. 

    In AR, usually the customer balances are positive, that is customer needs to
  pay us. However due to a credit memo application or over receipt applications,
  the invoice balances can be driven negative as well. In that case, that 
  amount needs to be returned to the customer.
  
  One way of doing is to identify all such invoices with negative balances and
  handle the refunds within the AR department(rather than AP). That is the AR department will
  take a print out of all the invoices and print checks and mail them to customers.
     Now from an accounting perspective, a neagtive miscellaneous receipt is created
  to offset the cash account. So the entries look like this. 
   Cash           $100 (cr)
   Negative Miscellaneous Receipt     $100 (dr)
    (associated with a Receivable Activity)
   
  One other way of doing it is to let the AP (accounts payable) to handle it. In
  this case, for each customer, who needs to be refunded, a supplier account is
  dynamically created and then AP will handle the check printing and sending it.
  Remember that AP can only send payments if there is a supplier account available.
  But this can get cumbersome, if the number of refunds are more. Its a business-to
  -business decision. The first one is most commonly used approach. 
  */

  /*In the above example, we have manually applied the receipt to "Credit Card Refund"
  and then the refund is created behind the scenes.
  However usually the refunds are created automatically by the Automatic Receipt Creation program.
  When Automatic Receipt Creation program runs ,it converts the invoices into receipts
  and the credit memos(which are tied to invoices) are converted into refunds (i.e
  negative miscellaneous receipts) are created. However if there are on-account
  credits, (i.e credit memos which are not tied to invoices), then the Oracle 
  Automatic Receipt Creation program does not create the refunds, because the sale
  receipt is not present in the system. Hence the key point, is that Oracle only
  performs refunds, when the sale receipt is present in the system. For on-account
  credits, we dont have the original sale receipt. 
  */
     
   /*******************************************
   CHARGEBACKS AND RECEIPT REVERSALS EXPLAINED
   ********************************************
   
   Chargeback Scenario.
   --------------------
   First create a receipt say for $45.
   Apply this receipt to an invoice of $26.
    Only after the invoice is applied, the chargeback button is enabled. 
    The chargebacks can only be created from the receipt applications window 
    and cannot be created directly from the transactions window.(even though you 
    can query the chargeback from the transactions window).
   If the invoice amount is greater than the receipt amount, then the difference 
      amount is defaulted in the amount field of the chargeback screen. If the 
      receipt amount is greater,then the amount will not default.
   
   Reversing the Receipts
   ----------------------
   
   1)  Let us consider a case where the receipt is having an application (with 
   out any chargebacks or adjustments) ie. it is applied to an invoice. If you 
   reverse such a receipt, then AR will try to unapply all the applications and 
   opens up all the associated transactions.(Simplest case). (What happens to 
   the receipt status?? The reverse journal entries will take care of the receipt 
   amount and where these journal entries are stored???)
   
   When reversing a receipt all the reverse journal entries that are created 
   will be in the gl_dist_all table. 
   
   2)  Let us consider a case where the receipt is having an application related 
   to a chargeback i.e. it is applied to an invoice and also a chargeback is 
   created.(Note : The invoice is closed here and where is this
   balance amount for the invoice is coming from ?????). 
   So what is happening in this case is that if you reverse this receipt, it will 
   open up all the associated transactions,and reverses the associated chargebacks 
   and adjustments.*/
   
    select * from ar_cash_receipts_all where receipt_number ='myrcpt2'
    
    select * from ar_receivable_applications_all where cash_receipt_id = 29925249
    
    select * from ar_cash_receipt_history_all where cash_receipt_id = 29925249
    
   /*3). Let us consider a case where the receipt is having an application related 
   to a chargebacks i.e. it is applied to an invoice and also a chargeback is 
   created.(Note : The invoice is closed here). And there is an activity against 
   this chargeback ie. say, a credit memo is applied against this chargeback.
   Then if you try to reverse the receipt,the system will not allow you to do 
   a standard reversal of the receipt. (And so is the case, if we have a chargeback 
   and this chargeback has been already posted to the GL. In that case
   too the system will not allow to do a standard reversal of the receipt).
     
   In this case, you will have to create a debit memo reversal. A debit memo 
   reversal means that instead of creating reverse journal entries and then opening 
   up all the associated transactions,it will create a debit memo for an
   amount which is the sum of the transaction balances.Hence you can still see the 
   reversed receipts applications to the transactions.
    
   From the following query. we can trace the reversed receipt record.
   
   select max(date_created) from gl_interface
      
   
--REPORTS :

/*"Invoice Print Selected Invoices" Report :
/*******************************************
  This will print the invoices for the customer. Usually if you print an invoice, 
  the invoice balance is always the same, no matter when you print it. 
   
   When you print Installment invoices this is how it works. 
   if you have two installments it will print 2 pages, 1 for each installment in a 
   separate page each specifying the corresponding due date.If you look at the printed 
   invoice it will be very clear to you.

   Another thing you might notice here is that once you specify a split payment 
   term on an invoice, the due date that shows on the invoice is first installment due date. 
   */
   
 /* Supplier Customer Netting Report : 
  ************************************
  This report is used when you are having a party who is both a customer as well as 
 supplier. That is, you purchase goods from them and as well as you sell goods to them.
  So this report will basically tell what is the net balance i.e
      Receivables minus Payables.
   
 When you run this report, you can use the join criteria i.e whether you want to
 system to join by 
            Name
       Tax ID
       NIF Code?
 Based on that it print the payables and receivables records in the report and then
 finally the net balance. 
         
   
  -- Oracle "AR Reconciliation Report" and Oracle "Aging 7 Bucket report" (or 4 Bucket report) 
  /*************************************************************************************************
  Ideally the "AR Reconciliation Report" and Oracle "Aging 7 Bucket report" 
  should have the same open balance.
  
  The "AR Reconciliation Report" typically gives the opening balance for an 
  "as of date" and computes the key metrics like the Transaction Register, 
  Applied Receipts Register ,Unapplied Receipts Register etc and comes up with the 
  total's for the period.
  And finally it also computes the closing balance for an "as of date". 
  Now the Closing balance = Opening Balance + algebraic sum of (registers etc)
  
  The major difference between the AR Reconciliation Report and Aging Report is 
  that, in the Aging Report, if there is a transaction which was created in that 
  particular period and also closed in the same period, then it would 
  not show up there. However the way the recon report works is that it picks up 
  all the transactions in the transaction register and then in the Applied Register 
  ,unapplied Register etc.
  
  /*
    The Aging Report will give the outstanding balance as of a particular date. 
  It should always be same no matter when you run the report as long as you give 
  the same as-of-date. As an ex, let us say there is an invoice for $100 in 
  march which got closed on apr 10th(say by a receipt). So if you run the Aging Report
  with as of date as 31st Mar,it should give the same output no matter whether 
  you run the report on Apr 1st or Apr 15th, because you are asking the balance of 
  the invoice as of 31st March which is always $100.
  Now from a technical perspective, Oracle is able to provide this information 
  because there is a column called gl_date_closed in the transaction table. 
  
  I found that the unapplied receipt register will change its output based on when you run. 
  */

select * from ar_cash_receipts_all where receipt_number like 't7' -- 29925249

select * from ar_receivable_applications_all
where cash_receipt_id = 29925248

select /* index(a ra_cust_trx_line_gl_dist_n2) */ *  --count(*)
   -- customer_trx_id,customer_trx_line_id,cust_trx_line_gl_dist_id
from ra_cust_trx_line_gl_dist_all a
where gl_date between to_date('05-SEP-2005','DD-MON-YYYY') and to_date('05-SEP-2005','DD-MON-YYYY') + 0.99999
--and  creation_date >= trunc(to_date('05-SEP-2005','DD-MON-YYYY'))
and cust_trx_line_gl_dist_id > 364834000

select max(cust_trx_line_gl_dist_id) from  ra_cust_trx_line_gl_dist_all --364834116

select  * 
from  ar_distributions_all
where  line_id > 210341000

The difference between ra_cust_trx_line_gl_dist_all and ar_distributions_all is that 
in the "ar_distributions_all" table, the data is stored in the form of dr/cr format. 
try this out and see what are the differences.ar_distributions_all table will store 
the dist for all the types of items, trxns, reeipt adjustments


-- Applied Receipts Register : 
/*****************************

The applied receipts register will only print all the receipts that are applied to the invoices.
 Sources of Discrepancies : 
 * This report prints the receipts for each receipt currency. That is it prints all the 
   receipts and then it prints the receipts for each such receipt currency.
  Now even in the receipt currency USD receipts, you will see the records corresponding 
     to the transaction currency,say, 'EUR' or 'GBP'. What this means is that the transaction 
   currency is 'EUR' and the receipt currency is in 'USD'. Now for these kind of 
   receipts, we might see the allocated receipt amount is different from the functional 
   amount. This can happen when the loss/gain of dollar happens from the time the 
   receipt was created to the time the receipt was applied. 
   Hence it is always important to take the functional amount.
 * Check what are all kinds of currency transactions that the applied receipt register 
      is printing and take that into consideration.
 * What we found is that when we run the Applied Receipts Register with the attribute 
    set as 'CUSTOMER' it is summing up the functional amount correctly as opposed to 
 running it with attribute set as 'DEFAULT' 

* VERY VERY IMPORTANT POINT FOR RECONCILIATION :  When we run the standard Oracle reports, even though the reports
    might look jumbled, we can do the following (all at once, or in portions) and get the summations that we want.
 Copy all the transaction of the report into a spreadsheet and do these two simple steps.
     a). Data => Text to Columns 
    b). Click on any amount column of interest, and do a Data => Sort. This would sort the data and
      put all the unwanted text either at the end/beginning,which can be deleted.Then we can easily
     sum or do any operation that we want.  
 */
 
  Applied Receipts Register,say,for June 2006, will give 
       all the receipts created before June and got applied in June,  (Case 1)
          all the receipts created and got applied in June 2006      (Case 2),
          all the receipts created in June and got applied in July 2006 and later,if so (Case 3).
   
 /*As of 11.5.10.2 the Applied Receipts Register is doing all the processing and dumping the information into
 the temp table and reading from it. So in order to see from the backend as to what is happening in each register
 do the following. Let us say we run the report "Applied Receipt register", then the table is populated with the
 data corresponding to that and it also populates the concurrent request id. We can use that id and go to the
 following table and get the data.
 */
 
 select * from ar_receipts_rep_itf 
 where request_id = 3851546

/* Similarly for the "Miscellaneous Receipts" register and "Other Receipt Applications report". Just use the 
   corresponding concurrent request id's and get the results from that table. However for the unapplied  receipts
   register we have to use the query below.
*/

-- Verisign process of Reconciliation : Each company canuse its own standard
 process of reconciliation. That is
-- a check point whether everything is ok at the monthend. In Verisign, one 
such check point is 

   Receipt Register = Applied Reg + Unapplied Reg + Miscellaneous Reg  + Other Receipts Application Reg 

-- Unposted Items Report
/***********************

The unposted items report is an important report for any finance person, because it 
gives a list of all the items which are not posted i.e transactions, receipts,
adjustments etc which are not transferred to GL.

The unposted items,shows which are the items which are still pending in the AR side. 
Once they are moved to GL, then we can close the period. For ex, in the case of ,say, 
transactions, they are the set of completed invoices, for which the revenue has been 
recognized,but they have not yet been pushed over to GL. Dont get confused with the 
gl posted, posting means here transferring them to GL.
 They all have a value of -3 for the posting_control_id. The following query would 
 typically print the unposted items (transactions) in the system for AR. Similarly 
 we have different queriers for printing different unposted items, like unposted 
 receipts, adjustments etc(look for metalink note).
 */
 SELECT gl.customer_trx_id trx_id, 
  gl.customer_trx_line_id line_id, 
  cust_trx_line_gl_dist_id dist_id,
  substr(account_class,1,3) acc,
  gl.amount,
  percent,
  gl.gl_date,
  gl.gl_posted_date,
  gl.acctd_amount,
  ct.invoice_currency_code currency
 FROM    ra_customer_trx_all ct,
                ra_cust_trx_line_gl_dist_all gl
 WHERE   gl.customer_trx_id = ct.customer_trx_id
 AND     ct.complete_flag = 'Y'
 AND     gl.account_set_flag = 'N'
 AND     gl.gl_date BETWEEN to_date('15-MAR-2006', 'dd-mon-yyyy') 
             AND to_date('16-MAR-2006', 'dd-mon-yyyy')
 AND     gl.posting_control_id = -3
 ORDER BY trx_id, line_id

/*Another issue which can cause this is because of a known oracle bug which 
is generating incorrect distributions,when the amount on the credit memo 
line is positive.(for which there is a tar 5477432.993).This can be eliminated 
by restricting in the transaction type (by the creation sign).

The MOST COMMON error for some items not being posted to GL are 
 "UNBALANCED credit and debit entries".

If you find that "Unposted Items" report is empty and you are still getting error, use Oracle Diagnostic 
tools and Select Receivables > Closing Period option. This will pin point you precisely which transactions
 or adjustments in corresponding tables is not posted and is causing the problem.
*/

Incomplete Invoices Report :
/******************************
 This is another simple report in which we have invoices which have not
 been completed at all. Now this is one report which functional people might
 run,before they close the period. The thing is even there are any incomplete 
 invoices, you can still close the period, unlike in the case of "Unposted 
 Items Report". In "Unposted Items Report" if there are any pending items, 
 then you cannot close the period.*/ 
    
 SELECT ct.*
 FROM    ra_customer_trx_all ct
 where  complete_flag='Y'


-- Billing and History Report :
/******************************

Many times it is convenient to know what are all the receipts that are 
applied to a specific invoice. One way of doing it is to run the Billing and History 
which is a very simple report which gives all the transactions on a customer by 
customer basis. Now for a single transaction we can do the following. 
   Pull up the transaction,
   Click the Actions => Installments
   Now click on the Activities button to see the receipts that are applied 
         against this invoice. 
*/

-- Aging - 7 Buckets Report By Collector :
/**************************************
   When I ran the AR aging by account and AR aging by collector, those two reports 
   are not matching with each other on the "receipts and credit memos". This could be 
   because of the unidentified receipts.
   If we run the unapplied receipts register, it will also print the unidentified 
   receipts. These unidentified do not correspond to any customer and hence they do not 
   correspond to any collector as such, so they may not be showing up in the "AR Aging 
   By Collector". once they are cleared, it will also tally.
   Usually the collector information is present at the customer profile and this 
   profile is associated to the customer.(You can define a profile at the customer site level).
   
   Hence in Summary,
      Aging by Account  : will show the invoice balance and the unapplied,
             unidentified and creditmemos
      Aging by Collector : will show the invoice balance and the unidentified 
             and creditmemos (not unapplied).
*/

--  Unapplied Receipts Register : Very Very Important Running Query :
/********************************************************************/
  
SELECT   gc.segment1 balancing_segment, NULL dcolsort,
         SUBSTRB (party.party_name, 1, 50) customer_name,
         cust.account_number customer_number,
         MAX (DECODE (UPPER (:p_in_format_option),
                      'SUMMARY', NULL,
                      app.gl_date
                     )
             ) gl_date,
         NVL (ar_batch_sources.NAME, :nls_no_batch) batch_source_name,
         NVL (ar_batches.NAME, :nls_no_batch) batch_name,
         rm.NAME receipt_method, 
   rcpt.receipt_number receipt_number,
   --,app.acctd_amount_applied_from
   --, app.amount_applied,
         rcpt.receipt_date receipt_date,
         SUM
            (DECODE (app.status,
                     'ACC', DECODE (UPPER ('USD'),
                                    NULL, app.acctd_amount_applied_from,
                                    app.amount_applied
                                   ),
                     'OTHER ACC', DECODE
                                      (app.applied_payment_schedule_id,
                                       -7, DECODE
                                               (UPPER ('USD'),
                                                NULL, app.acctd_amount_applied_from,
                                                app.amount_applied
                                               ),
                                       0
                                      ),
                     0
                    )
            ) on_account_amt,
         SUM (DECODE (app.status,
                      'UNAPP', DECODE (UPPER ('USD'),
                                       NULL, app.acctd_amount_applied_from,
                                       app.amount_applied
                                      ),
                      'UNID', DECODE (UPPER ('USD'),
                                      NULL, app.acctd_amount_applied_from,
                                      app.amount_applied
                                     ),
                      0
                     )
             ) unapplied_amt,
         SUM
            (DECODE (app.status,
                     'OTHER ACC', DECODE
                                      (app.applied_payment_schedule_id,
                                       -4, DECODE
                                               (UPPER ('USD'),
                                                NULL, app.acctd_amount_applied_from,
                                                app.amount_applied
                                               ),
                                       0
                                      ),
                     0
                    )
            ) claim_amt
       --   NVL (cust.cust_account_id, 0) customer_id,
       --   DECODE (cust.cust_account_id, NULL, '*', NULL) unid_flag
    FROM ar_batch_sources,
         ar_batches,
         hz_cust_accounts cust,
         hz_parties party,
         ar_receipt_methods rm,
         gl_code_combinations gc,
         ar_receivable_applications app,
         ar_cash_receipt_history crh,
         ar_cash_receipts rcpt
   WHERE app.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC')
     AND NVL (app.confirmed_flag, 'Y') = 'Y'
--      AND app.gl_date >= :p_in_gl_date_low
--      AND app.gl_date <= :p_in_gl_date_high
     AND rcpt.cash_receipt_id = app.cash_receipt_id
     AND NVL (rcpt.confirmed_flag, 'Y') = 'Y'
     AND crh.cash_receipt_id = rcpt.cash_receipt_id
     AND crh.first_posted_record_flag = 'Y'
     AND cust.cust_account_id(+) = rcpt.pay_from_customer
     AND cust.party_id = party.party_id(+)
     AND rcpt.receipt_method_id = rm.receipt_method_id
     AND ar_batches.batch_id(+) = crh.batch_id
     AND ar_batch_sources.batch_source_id(+) = ar_batches.batch_source_id
     AND gc.code_combination_id = app.code_combination_id
     and  app.gl_date >='01-JUN-2006' and app.gl_date <='30-JUN-2006'
 GROUP BY 
     gc.segment1,
         NULL,
         party.party_name,
         cust.account_number,
         NVL (ar_batch_sources.NAME, :nls_no_batch),
         NVL (ar_batches.NAME, :nls_no_batch),
         rm.NAME,
          rcpt.receipt_number
         rcpt.receipt_date,
         NVL (cust.cust_account_id, 0),
         DECODE (cust.cust_account_id, NULL, '*', NULL)
  HAVING SUM (DECODE (app.status, 'ACC', app.acctd_amount_applied_from, 0)) !=
                                                                             0
      OR SUM (DECODE (app.status,
                      'UNAPP', app.acctd_amount_applied_from,
                      'UNID', app.acctd_amount_applied_from,
                      0
                     )
             ) != 0
      OR SUM (DECODE (app.status,
                      'OTHER ACC', app.acctd_amount_applied_from,
                      0
                     )
             ) != 0
ORDER BY 1 ASC,
         3 ASC,
         4 ASC,
         gc.segment1,
         party.party_name,
         cust.account_number,
         rcpt.receipt_number,
         MAX (DECODE (UPPER (:p_in_format_option),
                      'SUMMARY', NULL,
                      app.gl_date
                     )
             ),
         NVL (ar_batch_sources.NAME, :nls_no_batch),
         NVL (ar_batches.NAME, :nls_no_batch),
         rm.NAME,
         rcpt.receipt_date,
         NVL (cust.cust_account_id, 0),
         DECODE (cust.cust_account_id, NULL, '*', NULL)
   
 -- AR To GL Reconciliation Report :
  This report can be run from the menu 
         Control => Accounting => AR To GL Reconciliation Report. 
  
  /* GL Transfer while the system is still up and running :
  
  And as per your earlier question if somebody is still doing transactions 
  at that point of time - only those transactions that are completed and receipts 
  that are saved at the time of interface will be interfaced.
    
   -- Can people be logged on to the system when run the transfers from AR to GL and AP to GL? YES
   -- If they are logged on, can they enter transactions?  YES
   -- If they are logged on, can they perform inquiries?   YES
   -- Can the transfer from AP to GL be scheduled?(I believe it can).  YES
   -- Can the transfer from AR to GL be scheduled?   YES
   -- If a big process like the transfer is running can the existing framework handle it with multiple 
       users logged on? YES 
  */
   
/***************************************************************/

  /*Prepayment Process (Also Includes how Intuit handles it).
  
  Usually in a B2B busines-to-business environment, firstly a sales order 
  is created and booked. Following that the invoice is generated out of that. 
  And this invoice,along with the goods, is sent to the customer. Once 
  an invoice reaches the customer, the customer will make the payment. 
  Even in the case of the automatic receipt generation, the conventional 
  process is that the first invoice is created,sent to the customer and 
  only on the invoice due date,the automatic receipt is created.
  
  However in the case of the prepayments, BY DEFINITION ;
  THE RECEIPT IS CREATED EVEN BEFORE THE INVOICE IS GENERATED. 
  The following is the process.

  Initially once a prepayment sales order is created,immediately a prepayment 
  receipt is created. 
   1) Here one of the flexfields will determine whether the order is a prepayment or not.
      And if it is,then it will also record the amount etc. (Actually using the 
   standard process it is related to the payment term,that is,if the payment 
   term is classified as prepayment, then it should create a receipt, but how 
   it is happening?)
   2) A cash receipt is created immediately, from the backend.
   3) And this receipt is applied to a prepayment activity.
    This receipt amount is applied to a prepayment receivable activity(predefined activity).
    Subsequently whenever a invoice is created, the previous prepayment application is 
   unapplied and then applied to this invoice.
*/

  /***************************************************
  REVENUE RECOGNITION : CREDIT MEMO ACCOUNTING RULES :
  ****************************************************
  
  Interesting problem regarding the revenue distribution with respect to 
  Credit Memos. 
  
  Let us say we have an invoice for a product raised in Jan 2005 for $1000 and this 
  invoice is associated with an accounting rule of ,say, (12 month equal distribution 
  with 8.13% each month). Then the revenue that is recognized
  for each month until Dec 2005 is $81.3. 
  
  Now in the month of May 2005, the product has been returned and an amount of 
  -593.5 has been credited to the customer. However we can recognize this revenue in 
  a couple of ways. 
  
  Firstly, we can recognize -$81.3 for each successive month going forward until 
  Dec 2005. That is we are going by the amounts of the invoice for each remaining 
  month until Dec 2005. (called LIFO)
  
  Secondly, We can take the percentages for each successive month and ie get 8.13% 
  of $593.5 = $48 for each month starting with the last month ie. Dec 2005 until 
  Jul 2005 and in the last month i.e Jun 2005, we will recognize the remaining 
  amount -$306. (called PRORATE)
  
  Currently it is doing the second method and what we want it to do is the first method. 
  --
  
  Generally companies want to push this (negative revenue i.e revenue due to the 
  credit memos) towards the end of the accouting period, while the auditors, for precision, 
  would like that negative revenue to be recognized as soon as possible so that it 
  reflects the correct figures on part of the company.
  */
 
 select * from ra_customer_trx_all --where creation_date >= trunc(sysdate)
 where customer_trx_id = 29485707 -- 29936462
 
 select cash_receipt_id,customer_trx_id,applied_customer_trx_id
  from ar_receivable_applications_all -- 29936462 ,29485707
 where customer_trx_id = 29936462
 
 select * from ra_cust_trx_types_all where cust_trx_type_id = 1133

/*Revenue recognition is the process where by revenue is distributed in appropriate 
  gl periods.For one off transaction we can use the following api to create the 
  distributions.Before you run the rev rec below api, run the queries to get the 
  user_id, resp_id and resp_appl_id is always 222*/
  
 select * from fnd_user  where user_name ='SETUPUSER'
 
 select * from fnd_responsibility_tl 
 where responsibility_name like 'Receivables Manager'
 and language ='US'

  declare
  l_create_dist_count number := 0;
  begin 
     fnd_global.apps_initialize (3724, 50385, 222);
     l_create_dist_count :=Arp_Auto_Rule.create_distributions
     (p_commit=>'Y',             --P_COMMIT_AT_END
     p_debug =>'N',           --Debug Flag
     p_trx_id=>1535592,     --Customer TRX id
     p_suppress_round=>NULL,     --Rounding Suppressed
     p_continue_on_error=>'Y');  --P_CONTINUE_ON_ERROR
  commit;
  dbms_output.put_line(' Dist Count -> '||l_create_dist_count);
  end;

 /* Just as the revenue recognition picks up by the gl_date on the ra_customer_trx_all 
 table and puts it in different buckets in the ra_cust_trx_gl_dist_all. In the case of 
 receipts, the receipts go into different accounts and it can be seen on the 
 ar_cash_receipt_history_all based on different statuses. The revenue recognition program 
 need not have to do any thing,the distribution are immediately generated once the receipt
 is created,remitted or cleared.
 */
 
 select a.trx_number,a.creation_date 
 from ra_customer_trx_all  a, ra_cust_trx_types_all b
 where a.cust_trx_type_id = b.cust_trx_type_id
 and a.customer_trx_id in(
 select distinct customer_trx_id from ra_customer_trx_lines_all where accounting_rule_id = 1026
 --and creation_date < to_date('01-JUL-2005')
 and creation_date > to_date('01-JUN-2005')
 and rownum < 100)
 and  b.type ='INV'


  /*TAR 4430342.994
  ---------------
  Hi 
  We have a problem regarding the revenue distribution with respect to Credit 
  Memos. 
  Let us say we have an invoice raised in June 2005 for $329 and this invoice is 
  associated with an accounting rule of 13 months (To summarize, the percentage 
  and the revenue amount distribution are given in the attachment (INV_DIST.TXT) 
  GL_DATE PERCENT AMOUNT 
  -------- ------ ------ 
  6/8/2005 4.1672 13.71 
  8/1/2005 8.3343 27.42 
  8/8/2005 8.3343 27.42 
  9/8/2005 8.3343 27.42 
  10/8/2005 8.3343 27.42 
  11/8/2005 8.3343 27.42 
  12/8/2005 8.3343 27.42 
  1/8/2006 8.3343 27.42 
  2/8/2006 8.3343 27.42 
  3/8/2006 8.3343 27.42 
  4/8/2006 8.3343 27.42 
  5/8/2006 8.3343 27.42 
  6/8/2006 4.1555 13.67 
  Hence that revenue is recognized for each month until June 2006. 
  Now in the month of Aug 2005, a credit memo has been generated for the amount 
  of $200 and we have the credit memo accounting rule as LIFO. 
  (The revenue distribution for this credit memo is given in the attachment 
  CM_DIST.TXT). 
  GL_DATE PERCENT AMOUNT 
  -------- ------ ------ 
  11/8/2005 10.88 -21.76 
  12/8/2005 13.71 -27.42 
  1/8/2006 13.71 -27.42 
  2/8/2006 13.71 -27.42 
  3/8/2006 13.71 -27.42 
  4/8/2006 13.71 -27.42 
  5/8/2006 13.71 -27.42 
  6/8/2006 6.86 -13.72 
  According to us it is doing exactly what we expected it to do (for LIFO) ie. go 
  to the farthest period and apportion the credit memo amounts to each period as 
  it goes up the periods. 
  However there is a small discrepancy in the period of June 2006 as you can see 
  from those attachments. We expect the revenue amount for the credit memo to be 
  -13.67 while the amount it is showing as -13.72. 
  Our business is questioning as to why there is such a discrepancy. Is this a 
  bug and if so could you please provide us with a fix. 
  Your quick response is highly appreciated. 
  
  Hi Tota, 
  Thanks for the response. Let me make it clear for you. See the way LIFO is 
  expected to work is that it should go to the farthest period ,which is Aug 2006 
  and put the same amount i.e -13.67 in that period and then come up the next 
  period which is -27.42 and then keep doing same thing for each period going 
  backwards until it is exhausted of that $200 amount. So in this case it ran out 
  of that $200 amount by the time it came to the Nov 2005 period and it should 
  put the remaining amount in that period. So according to our understanding it 
  should put the remaining -21.81 amount in the November 2005. 
  Instead for some unknown reason it is getting this amount of $13.72 (dont know 
  how it got that amount) and putting it in June 2006 (which is incorrect). It 
  should look at the invoice distribution for June2006 which is $13.67 and put 
  the same amount of -$13.67 for the June 2006 period for the credit memo 
  distribution. 
  Just to summarize, we know that the credit memos follow the revenue 
  distribution of the invoice and in the case of LIFO it should go by the amounts 
  of the invoices (and NOT percentages). 
  Hope I have explained the problem to you very clearly. Please get backto 
  immediately as we need to close our books based on this bug as this has an 
  financial impact.  Thanks in advance. */
       
  /*   Accounting rules create the revenue recognition schedules for invoices.
  Accounting rules determine the number of periods and % of total revenue to
  record in each accounting period. When you run the revenue recognition program 
  for an invoice that is associated with one or more accounting rules,Receivables 
  creates the invoice's revenue distributions for the period or periods in which rules
  fall.  The revenue recognition program does not pick the invoices with no 
  accounting rule specified. Now after this, we can see that we have data in 
  gl_interface, gl_je_batches,gl_je_headers,gl_je_lines as below. 
    
  There is an exception to the above statement.If you set the profile option 
  "Use Invoice Accounting for Credit Memo" to No, then the credit memos will 
  have their own accounting rules.

  /*Receipt Write-Off Functionality :  Small Balance Receipt Write-Off.
  --------------------------------------------------------------------
   Some times we can have receipts in the AR with small balances which are in the 
   Unapplied or Onaccount status.This could probably be because of the customer 
   overpayments. Now we can write-off such small balances within certain limits defined 
   for that user. That is a user can write off a specific receipt for an amount,
   if it is only within his limit. 
   The important thing to note is that,receipt write-offs do not affect customer 
   balances or cash account. Also we cannot write-off miscellaneous receipts and 
   it can only done for cash receipts.

 Online receipt write-off : 
   Receipts => Applications => Choose receipt write-off 
  (inthe detailed block record),save it.
 Batch receipt write-off :Call the setup => Create receipt write off ,which in turn 
 kicks off the Receipt write off batch program. (which can be run initially as a 
 report and check and then actually run the program)
          
 So all these small balances of the receipts will go into a separate GL account,
 which is defined in the receivable activities. So receipt write off is a 
 receivable activity. Typically once the receipt write-off completes the
 status of the receipt should be CL in payment schedules and the unapplied 
 amount should be 0. Typically this program is run, before month end to close 
 all those small receipts,so that they can close the period.
   
 Another important point about the Receipt Write-Off process is the write-off 
 limit that is set in the systems option In the setup=> system options also, 
 please make sure that the maximum write-off limit is properly set. 
 This is the limit for all the users of the system (and hence should be very 
 high and maximum). Make sure this amount is greater than any individual amounts.
 
 AutoAdjustment : Small Balance Invoice Adjustment : 
 --------------------------------------------------
 Just as we write off small balances of receipts, sometimes we might even small
 balances of Invoices ,which can be written off. If there are very few, then we
 can do it manually assign it to a receivable activity. Otherwise we can run the 
 program,
      Control => Adjustments  => Create AutoAdjustments
     
 /* This program takes some parameters and by clicking submit, it will submit a 
 concurrent program which will write-off and close all the invoices which satisfy
 the criteria specified.*/
 
 
 /*Global Billing Functionality - Intercompany Transactions from AR :
 --------------------------------------------------------------------
     
   At sun, the global billing functionality does not mean that the bill is sent to a 
   customer. But instead we are billing different OU's with in our company. 
   
   Let us take this by example in the case of Sun Microsystems(SMI).
   Sun Operates in many countries around the world and hence has many Operating Units defined,
   
   Sun United States
   Sun United Kingdom
   Sun Argentina ,etc
   
   Now let us say Nortel Canada has placed a PO order for a service work to Sun Canada.
   In this case Sun Canada would be considered as host. So once the payment is fully made 
   by Nortel Canada to Sun Canada, then the service fulfilment would start.  
   Then Sun Canada might give that service to its different subsidiaries like Sun US,Sun India etc
   and get the service done. These subsidiaries like Sun US,Sun India etc are called
   receivers.
   
   Since the service is distributed, Sun US will have to pay its
   subsidiaries for the service they provided. So an invoice is created with each line
   being referring to one operating unit. 
   
   "Sun United States" has a operating unit id = 203 (hr org id)
   "Sun United States" also has a company value = 110 
   "Sun United States" is defined with a subsidiary code which is a concat of LCO||999|||MCO = 110999110
      
   what is the vanilla functionality for global billing. 
   is this invoice being sent to the customer. 
   why not put this in a DFF.  
 
 
/* AUTOINVOICE INTERFACE :
**************************
     
 select creation_date,credit_method_for_acct_rule,batch_source_name,ship_date_actual,a.*
 from ra_interface_lines_all a  where batch_source_name = 'OM IMPORT'
 and creation_date >= trunc(sysdate) 

 /*In the ra_interface_lines_all table, the interface_line_attribute1 would 
 correspond to the order number from the Oracle OM i.e the autoinvoice process 
 expects the order number in that column, but if it is not coming from OM and if we 
 are directly populating it, it is a some sequence number And once the invoices are
 imported into AR tables, then the records are deleted from the interface tables.
 
 Actually when the Autoinvoice process runs, it imports all kinds of transactions 
 i.e invoices, credit memos etc. While the credit memos are imported into AR, and if 
 it finds the original invoice related information in the appropriate column, then 
 it would go ahead and apply that credit memo to the particular transaction. In such
 case,we can go the table ar_receivable_applications_all table and look for that 
 specific record; i.e. we can find that the customer_trx_id and the applied_customer_trx_id 
 will correspond to the invoice and credit memo id.

The different kinds of attributes that are stored in the ra_interface_lines_all table are given below.
The 3 kinds of flex field attributes in the ra_interface_lines_all table are 
 __ interface_line_attribute columns => contains the order related attributes
 __ reference_line_attribute columns => contains the original order related attributes.
 __ link_to_line_attribute columns   => contains the tax,freight related attributes.

 /* Once the order is closed, the data goes into ra_interface_lines_all, 
  ra_interface_sales_credits_all and ra_interface_distributions_all. When the Autoinvoice 
 process runs and if it succeeds, the data goes into the ar receivables tables. 
 If for any reason an order fails, then it goes into the ra_interface_errors_all 
 table. Initially when a record is created in the ra_interface_lines_all table, 
 the interface_line_id value is null for that record,when the Autoinvoice picks 
 it up and processes it, it populates the interface_line_id column with some 
 sequence value. (It is important to remember that when the records come from OM,
 they come in completed status.

   ** Ensure that payment terms, frieght, tax codes,salespersons,invoicing_rule_id,
     accounting_rule_id are present in the ra_interface_lines_all,otherwise the 
   Autoinvoice will error out. 
   ** Also ensure that both in AR and GL, the corresponding period is open.
   ** Ensure that the transaction source, has the autoinvoice and accounting options 
    in a way that you want. i.e you want to match by the value or id. If it is value, 
  then it will try to match by ref values. This could be one reason why we might end 
  up with the interface line errors. This is very IMPORTANT.

 The starting point for the Autoinvoice is the ra_interface_lines_all table. This 
 table can get the data from different sources. Typically users can populate this 
 table from sql loader. However in general, whenever an order is closed,immediately 
 and automatically this table will get populated with a record.If there are 2 lines 
 in an order there will be 2 records in this table,and in this case the source will 
 be called as 'OM IMPORT'. 
 Hence we can see this batch source from the menu option
       setup => transactions => sources => AutoInvoice Options.
    Here we can see what are the grouping rule,gl_date options etc.
 
 Grouping rule is an important feature of the autoinvoice process. What this 
 means is the Autoinvoice groups by all the columns that are mentioned in this 
 grouping rule before it creates the invoices(or transactions) in the AR side. 
 
 Ex 1: Let us say if there is an order which has got 2 lines (corresponding to 
 2 different inventory items). Corresponding to this,let us say there are 2 
 lines in the ra_interface_lines_all table. If the grouping rule says to group 
 by (sales_order), then the Autoinvoice will create only 1 invoice since both 
 the above lines correspond to only one sales order.
 
 Ex 2: Let us say if there is an order which has got 2 lines (corresponding to 
 2 different inventory items). Corresponding  to this,let us say there are 2 
 lines in the ra_interface_lines_all table. If the grouping rule  in this case
 says to group by (sales_order,inventory_item_id), then the Autoinvoice will 
 create  2 invoices corresponding to two lines of the sales order.

 Similarly the line ordering rules. The grouping rules do a group by, while the 
 ordering rules do an order by. That is,these rules ensure that the lines on the 
 invoice are in the same order as they are in the sales order 
*/
  
/* When the order is finally pushed from the interface table to the AR,the value of 
  the gl_date that is populated in the lines table is obtained as follows.*/
  
   ra_interface_lines_all.gl_date 
     => (Check batch Source gl_date option) 
      => YES => check the ra_interface_lines_all.ship_date_actual 
   => NO  => ra_interface_lines_all.sales_order_date     
      => NO  => default date on run autoinvoice SRS request window.  
      
--  The following query should give the information about the different available dates.*/
  
 SELECT ship_date_actual,gl_date,sales_order_date,interface_line_id, batch_source_name,
     invoicing_rule_id, accounting_rule_id,interface_line_context
   FROM ra_interface_lines_all
 --  where interface_line_attribute1= '1100026568'
 WHERE  interface_line_context = 'ORDER ENTRY'
    AND creation_date >= '28-MAR-2006'
 
 select rowid,gl_date, original_gl_date,interface_line_id, batch_source_name
 ,invoicing_rule_id, accounting_rule_id
 from ra_interface_lines_all 
 where interface_line_attribute1='1100026562' -- 53984148  
 
 select * from ra_interface_distributions_all where interface_line_id = 53984148
 
/*The errors can be viewed from the menu option 
       Control => AutoInvoice => Interface Lines */

 select * from ra_interface_errors_all where interface_line_id = 53984155
  
 select * from ra_customer_trx_all   -- 11005 & 52365
 where  interface_header_context='ORDER ENTRY'
 and    interface_header_attribute1='50915297'
 
   /* INVOICING RULE & ACCOUNTING RULES: The most important point to notice here is 
   that, we have to define the invoicing rule, if we need to define the accounting rule. 
   Unless we define the Invoicing rule ,we cannot define the Accounting rule successfully.
   Generally accounting rule is defined at the line level, that means even in the 
   inventory for each master item we can define the accounting rule.
   
    Accounting Rules can be defined at the item level or at the memo lines. So 
   when you create a transaction ,say an invoice,which consists of item. Now this 
   item is associated with an accounting rule id(in inventory). If there is no 
   accounting rule id, all the amount of the invoice is recognized in the current 
   AR period,otherwise it is adjusted according to that rule. If you define an 
   accounting rule both at the transaction header level and at the item level, then 
   the item level will take the precedence. 
     If a credit memo is created, in which case we need not give an item 
   and choose a memo line. So the revenue is recognised according to the accounting 
   rule mentioned in the memo line. In fact in a credit memo,  We can even type 
   in a value for the description in which case, the entire amount is recognized 
   in the same period.
   */

 select code_combination_id,percent, amount,gl_date,gl_posted_date,posting_control_id,
     account_class,acctd_amount
 from ra_cust_trx_line_gl_dist_all 
 where customer_trx_line_id IN (10521857,10521856)
 and code_combination_id = 1047

 select * from ar_memo_lines_all_tl
 where name like 'cm%'
 
 select * from ra_rules where name like '%12%'
 
 /*As mentioned earlier, if the invoicing rule is not specified, then you cannot
 specify the accounting rule. If the invoicing rule is "Bill in Advance" then
 you can specify any accounting rule, and the Unearned Revenue(UER) account will 
 be hit ,when the revenue recognition program runs. 
 If the invoicing rule is "Bill in Arrears" then you can specify any accounting 
 rule, and the Unbilled Receivables(UBR) account will be hit ,when the revenue 
 recognition program runs.
 
 Let us briefly understand how the accounting entries look like if we specify 
 bill in advance and how Unearned Revenue entries will be :
 
 For example, a invoice was created on May 1 of USD 1200, entries will be

    1-May-08: Receivables Dr 1200
        Unearned Revenue Cr 1200
    
    1-May-08: Unearned Revenue Dr 120
        Revenue Cr 120
    
    1-Jun-08: Unearned Revenue Dr 120
        Revenue Cr 120
    
    This way at the end of the 10 months, there will be "0" balance in the 
 Unearned Revenue A/C and the Revenue A/C will be credited every month 
 for equal amount and finally the total amount will be in revenue.  
 */
 
    /*Bill in Arrears Explanation :
    You can use this invoicing rule to recognize receivable (remember 
 receivable not revenue) at the end of the revenue recognition schedule. 
 Let us explain this with an example of an invoice with different invoicing rules,
    
 
     Invoice  : $2000
     Invoicing Rule  : Bill in Advance
     Accounting Rule : 10 Month
     Invoice date  : 10-JAN-2008; Payment term : Net 15
     Due date      : 25-JAN-2008
     
 -----------------
  
     Invoice  :  $2000
     Invoicing Rule  : Bill in Arrears
     Accounting Rule : 10 Month
     Invoice creation date = 10-JAN-2008; Payment term : Net 15
     Invoice date is changed to  10-NOV-2008;
     Due date      : 25-NOV-2008 (see the due date is 10 months + net 15)
    
    Hence if you see above, the invoice is having a invoice date as 10-NOV-2008,
 even though the invoice creation date was 10-JAN-2008. Now when the 
 revenue recognition program completes, the account that is hit here is
 Unbilled Receivables (instead of unearned revenue),otherwise eveything remains
 the same. And to apply the same ex, we will have the accounting entries as,
 */
 For example, a invoice was created on May 1 of USD 1200, entries will be

    1-May-08: Revenue Cr 120
        Unbilled Receivables Cr 1200
    
    1-May-08: Unbilled Receivables Dr 120
        Revenue Cr 120
    
    1-Jun-08: Unbilled Receivables Dr 120
        Revenue Cr 120
    
 
    1-Feb-09: Unbilled Receivables Dr 120
        Receivable  Cr 1200
        Revenue Cr 120
     Unbilled Receivables cR 1200
     
    This way at the end of the 10 months, there will be "0" balance in the 
 Unearned Revenue A/C and the Revenue A/C will be credited every month 
 for equal amount and finally the total amount will be in revenue.  
 */
 
 /*UNBILLED CREDITS :
   As explained earlier, unbilled credits are those credit memos which
 are having an invoicing rule of "Bill in Arrears". That means,the receviables 
 */
 
 /* DEFERRED REVENUE :
    To explain the Revenue recognition program, let us consider the example 
 of the Gift Certificate. If you buy a Gift Certificate of $100 from a company 
 X in a period ,say Q1, then the company cannot report this revenue of $100 for 
 that period. It can only report the revenue when that gift certificate is redeemed, 
 that is when somebody has used it, say may be in a different quarter Q2. So 
 they can show revenue in Q2.

    When you use deferred accounting rules, the Revenue Recognition program creates 
 a single distribution per line that posts to an unearned revenue GL account. 
 You can use deferred accounting rules only for invoices that are assigned the 
 Bill in Advance invoicing rule. If the invoicing rule on a transaction is 
 Bill in Arrears, the Revenue Recognition program ignores the deferred flag. 
 You can later earn the revenue using the Revenue Accounting feature 
 So the essence is that you will not see any revenue lines, but there will be 
 only one line corresponding to the unearned revenue account corresponding to
 the whole invoice amount. Later on, we can recognize the revenue amount as
 well from the Revenue Accounting Wizard from the menu item
 */
      Control => Accounting => Revenue Accounting 
     
   --Accounting Rules and First date in the Transaction Line.: 
   
   /* Based on the first_date in the line item,I found that the trx_date and the
    gl date are automatically changing. 
   
   Revenue recognition program is completing with warning,which I think is because 
   of the first date specification in the rule.(I could not make out the message, as 
   it is not clear).
   
   However when I saw the accounting entries for this particular transaction, it is 
   not creating the accounting for the prior months, it is putting every thing under 
   the first day of the current period,which is same as giving the first date as the 
   first day of the current period.
   
   This is usually the case when let us say there is a service contract which actually 
   was started some time back and has not been entered into the system till now. And 
   since the prior periods are closed, all the revenue till now will fall in the current 
   period and after that in the subsequent periods.*/

/* AutoAccounting: 
   AutoAccounting is the tool which determines which GL account should be 
   chosen when generating the accounting lines for the transactions. 
  Whether the transactions are entered online or thru autoinvoice, Autoaccounting 
  will generate the GL acounts for each account type. In the auto accounting 
  we can specify  from which source we need to pick the gl code combination 
  for each account type ex, Receivable, Revenue, tax,frieght etc
  As an ex of autoaccounting, let us consider an accounting structure consisting of 
   (company,Business Unit, dept, Nat account, IC segment1, line2,line3) 
  Let us say we have an account "Unearned Revenue" ,where in the autoaccounting 
   we have the setting as follows,i.e
   For Company,Business unit, Dept, Account  ==>> transaction type.
   For  Product Line  ==>> Standard Line(i.e from Inventory setting).
   
   So in this case, when the autoaccounting generates the distributions, it will 
   take the first four segments from Transaction types(ra_cust_trx_types_all), 
   and take the product line segment from inventory and then concatenate and 
   form a new GL account combination.
   
 I think the Autoaccounting will only decide the distributions, it will not 
 generate the actual accounting entries, which is done by the Revenue 
 Recognition Program. That means once the revenue recognition is complete
 you will find the entries in the GL distribution table.
   */

 select * from ra_account_default_segments
  
/* Just remember one important point :
  AutoInvoice => For invoices without rules;
  Revenue Recognition => For invoices with rules;

 What this means is that if you create an invoice, with out a invoice/accounting
 rule, once the invoice is completed, the distributions and accounting are
 created immediately after completion. No need to run the revenue recognition
 for generating accounting distributions.
   
 However if you have an invoice/accounting rule, then you need to run
 revenue recognition for generating accounting distributions.


*/

 /* AUTOINVOICE AND AUTOACCOUNTING : 
   In AutoAccounting, we specify for each account type like Receivable, Revenue, the 
   source for each segment of the COA.
   
   Now when an order of a particular type is fulfilled it directly falls into the 
   AR interface(ra_interface_lines_all) table.
   
   At this point we run the AutoInvoice to import the invoices,which internally runs 
   the AutoAccounting process as well.

   Now if you want AutoAccounting to determine your general ledger accounts you must not
   enter values in ra_interface_distributions_all. If you enter values in this table,
   then Autoaccounting will NOT be run and the AutoInvoice will simply pick the values from
   this distribution table.
   
   Now let us say if you dont populate values in the distribution table and you use the
   AutoAccounting tool,which means it will find out the distribution for you. Then say
   for receivables,it will go to the autoaccounting setup and find out the sources.
    
 If the segment is based on transaction type, then the segment value is obtained
 from the transaction type. (remember the AR trx type is obtained from the OM trx type 
   as each order type can be associated with a receivables transaction type).
 
 If the segment is based on standard lines, then the Autoinvoice will get the segemnt
 value from the Inventory item from the interface lines.
 
 If the segment is based on sales reps, then the Autoinvoice will get the segemnt
 value from the RA_INTERFACE_SALESCREDITS_ALL for each invoice line in RA_INTERFACE_LINES_ALL.
 This is actually obtained from the order entry information.  
 */
  
 /* Some of the contexts come out-of-the-box with Oracle Apps. For ex, the context code 
  'ORDER ENTRY' in the Line Transaction Flexfield (where each attribute corresponding 
  to fields like order number,delivery waybill etc) is defined by Oracle apps by 
  default.What this means is that if we go the transaction line and open up the DFF Line
  Transaction and if we choose the context value of 'ORDER ENTRY', then we can see 
  all these fields. Likewise we can define as many context codes as possible and 
  define corresponding segments for them.
  
  When a RMA is created and comes into the ra_interface_lines_all table, the 
  reference_line_id will store the customer_trx_line_id of the original invoice. ie.
  
   ra_interface_lines_all.reference_line_id = ra_customer_trx_lines_all.customer_trx_line_id.
  */   

  select  batch_source_name, interface_line_context,interface_line_id, creation_date
     ,interface_line_attribute1
     ,interface_line_attribute2
     ,interface_line_attribute3
     ,interface_line_attribute4
     ,interface_line_attribute5
     ,interface_line_attribute6
     ,interface_line_attribute7
     ,interface_line_attribute8
     ,interface_line_attribute9
     ,interface_line_attribute10
     ,interface_line_attribute11
     ,interface_line_attribute12
     ,interface_line_attribute13
     ,interface_line_attribute14
     ,interface_line_attribute15
  from    ra_interface_lines_all
  where interface_line_attribute1= '1100026568'
  
  select 
     reference_line_attribute1
     ,reference_line_attribute2
     ,reference_line_attribute3
     ,reference_line_attribute4
     ,reference_line_attribute5
     ,reference_line_attribute6
     ,reference_line_attribute7
     ,reference_line_attribute8
     ,reference_line_attribute9
     ,reference_line_attribute10
     ,reference_line_attribute11
     ,reference_line_attribute12
     ,reference_line_attribute13
     ,reference_line_attribute14
     ,reference_line_attribute15
  from    ra_interface_lines_all
  where interface_line_attribute1= '1100026568'
  
  delete ra_interface_lines_all where interface_line_attribute1= '1100026567'
  
  select
    link_to_line_attribute1
    ,link_to_line_attribute2
    ,link_to_line_attribute3
    ,link_to_line_attribute4
    ,link_to_line_attribute5
    ,link_to_line_attribute6
    ,link_to_line_attribute7
    ,link_to_line_attribute8
    ,link_to_line_attribute9
    ,link_to_line_attribute10
    ,link_to_line_attribute11
    ,link_to_line_attribute12
    ,link_to_line_attribute13
    ,link_to_line_attribute14
    ,link_to_line_attribute15
  from    ra_interface_lines_all
  where interface_line_attribute1= '1100026568'
  
  select * from ra_customer_trx_all where interface_header_attribute1 =  '1100026562'
  
  select * from ra_customer_trx_lines_all where customer_trx_id = 1407740
  
  select b.type,a.trx_number from ra_customer_trx_all a , ra_cust_trx_types_all b
  where a.cust_trx_type_id = b.cust_trx_type_id
  and customer_trx_id = 1407739  
  
  select * from ra_customer_trx_all
  where trx_number = '1170028229'
  
  select * from ra_customer_trx_lines_all
  where  customer_trx_id = 1407739
     
  select * --rowid,invoicing_rule_id,accounting_rule_id,term_id
  from ra_interface_lines_all where interface_line_attribute1 = '1100026568'

/*Once the autoinvoice completes, the exact set of columns in the 
ra_interface_lines_all are copied over to the lines table ra_customer_trx_lines_all.*/
  
  update ra_interface_lines_all
   set  
   reference_line_attribute1 = interface_line_attribute1,
   reference_line_attribute2 = interface_line_attribute2,
   reference_line_attribute3 = interface_line_attribute3,
   reference_line_attribute4 = interface_line_attribute4,
   reference_line_attribute5 = interface_line_attribute5,
   reference_line_attribute6 = interface_line_attribute6,
   reference_line_attribute7 = interface_line_attribute7,
   reference_line_attribute8 = interface_line_attribute8,
   reference_line_attribute9 = interface_line_attribute9,
   reference_line_attribute10 = interface_line_attribute10,
   reference_line_attribute11 = interface_line_attribute11,
   reference_line_attribute12 = interface_line_attribute12,
   reference_line_attribute13 = interface_line_attribute13,
   reference_line_attribute14 = interface_line_attribute14,
   reference_line_attribute15 = interface_line_attribute15
  where  interface_line_attribute1='1100026567'
  
  /*Intuit Process of Invoice Import    

  XXINT_OM_ORDER_IMPORT_PUB (Imports Orders)
 They do not have the orders being progressed thru the steps of pick launch,pick release and ship confirm etc.
 Once the order is booked by this program and populated into the ra_interface_lines_all table.
 After this PRE-AR (-- ( PRE-AR) Intuit AR: Invoicing & Accounting Parallel Process 
  (XXINT_AR_MULTI_INV_REV_PROCESS) process will run and will populate the key fields of the 
  ra_interface_lines_all table. The key attributes in the ra_interface_lines_all are from 
  interface_line_attribute1 thru interface_line_attribute15. If any of these fields are null, then
  standard AutoInvoice process will fail.(PRE-AR will populate these fields).
 Following this the (Intuit AR: Auto Invoice Master Program) will pick up these records and populate into
  the AR related table. Actually this program will inturn call the Oracle AutoInvoice program.
  */

  /* The data is transferred into the GL,either detailed or Summary, If the 
   data is pushed in detailed format, the reference columns reference_1,2 etc 
   are populated with the feeder system ids. If in summary format, these columns 
   are not populated with any values.  
  */ 
  
  select *  from gl_je_batches where je_batch_id = 457618
  
  select * from gl_je_headers where je_batch_id = 457618
  
--  je_source => Receivables, je_category => Sales Invoices, Credit Memos

 --  REFERENCE_1 PCID            Posting Control ID
 --  REFERENCE_2 ID       Customer Transaction Id
 --  REFERENCE_3 SOURCE_ID      Cust Txn GL Dist ID
 --  REFERENCE_4 "TRX/REC_NUMBER"    Trx Number
 --  REFERENCE_5 REF_25      Shipto number
 --  REFERENCE_6 CUSTOMER     'CUSTOMER'
 --  REFERENCE_7 BILL_TO_CUST    Bill To customer
 --  REFERENCE_8 "TRX/REC_TYPE"    'CM'  i.e Credit Memo
 --  REFERENCE_9 SOURCE_TYPE    CM_REV
 --  REFERENCE_10 SOURCE_TABLE    RA_CUST_TRX_LINE_GL_DIST

  select * -- reference_1,reference_2,reference_3,reference_4,reference_5 
  from gl_je_lines where je_header_id = 194295 -- and  reference_4 = 1170028234
  -- and reference_4='1170025015'

   -- The reference_1,2 etc attributes referred in gl_import_references and 
   --gl_je_lines store same values.
   SELECT * 
   FRom gl_import_references where je_batch_id = 457615 and je_header_id = 194283
   --and  reference_4 = 1170028235
   
 /*-- Detail : So from the above column explanation, it seems clear that if the 
   data is moved in a detailed format, then it stores the level from the gl_dist tables.
      
   -- Summary :  In the case of summary, what is the level at which the data is 
    stored, transaction, account?   */
   
 select * from ra_customer_trx_all --where customer_trx_id > 1407757 -30
 WHERE trx_number= '1170025015'
 order by creation_date
 
 select * from ra_customer_trx_lines_all -- 53984190
 where  customer_trx_id = 1235368 
 
 select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 43799136
  ----   
   
 select * from ar_payment_schedules_all where payment_schedule_id < 0

  /* On-Account credit memo  : not always a credit memo be tied to an invoice. 
  Sometimes there could be a credit memo for a specific customer but which is not 
  tied to any invoice as such, these kind of credit memos are called on-account 
  credit memos.*/
 
 /*AutoInvoice and Prepayment Matching : Usually once all the invoices are imported
 into the AR system,the autoaccounting process will try to "Complete" them and then
 try to run the program "Prepayment Matching Program" which applies any existing prepaid 
 receipts to these just-imported invoices. 
 So if you dont want AutoInvoice to run this program then you will have to disable
 this program from the Concurrent program setup from sysadmin responsibility.
 This is probably this program "Prepayment Matching Program" might be always run from
 Autoinvoice program.
 */
 
/* TAX INTERFACE :  How TAXES are dealt with in Oracle Financials   
  
 Usually companies use the most popular tax softwares that are available in market 
 like Vertex,Taxware,Sabrix etc. Since Uncle Sam (US goverment) tax rules keep 
 changing regularly i.e the sales tax percentage,vat tax etc vary from state to state. 
 Similarly there are different kinds of taxes like state tax, city tax ,county tax etc.
 These tax softwares will keep track of these of all these changes regularly. That is,
 say if a customer is using the Vertex tax software, then the Vertex company will 
 keep sending regularly the files to their customers so that they are up-to-date in 
 terms of tax information. Typically Vertex deals with what is called geocode which
 identifies uniquely a particular geographical area. 
 
 Just like Autoinvoice,Lockbox etc the "Sales Tax Rate Interface" will populate the 
 tax information into this table ar_location_rates. 
 
 So the way Vertex is integrated with Oracle apps is using the Tax interface. That 
 is from the vertex system,the data is populated into the interface tables and 
 after running the "Sales Tax Rate Interface" program, the data is populated into 
 the ar_location_rates table where all the tax rates for different postal codes 
 are stored and the triggers will immediately populate the data into ar_sales_tax.  */
 
 select location_rate_id,location_segment_id,from_postal_code,to_postal_code, tax_rate,  
   attribute_category, attribute1,attribute2
 from ar_location_rates where attribute_category='VERTEX'
 
 /* ar_location_rates is the source of all the sales tax rates. Any changes in
 this table are automatically (thru triggers) into a composite rate and a 
 composite rate is stored in the ar_sales_tax.  Here in this table,the 
 tax rate is the sum of the sub rates that is stored in the location1_rate,
 location2_rate etc. So if your key flexfield includes something like state, 
 county,city, then these 3 correspond to the location1_rate,location2_rate,
 location3_rate. We can also get the rate corresponding a particular location
 from the from   
          Setup => Tax => Sales Tax Rate
 */
 
  select * from ar_sales_tax
  where  upper(substr(from_postal_code,1,5)) = lower(substr(from_postal_code,1,5))
  and  upper(substr(to_postal_code,1,5)) = lower(substr(to_postal_code,1,5))
   and   94043 between to_number(substr(from_postal_code,1,5)) and  to_number(substr(to_postal_code,1,5))
  
 -- This table does not store any tax rate information,it only stores about the location information.
 select location_segment_value , location_segment_qualifier, 
   attribute_category, attribute1,attribute2
 from ar_location_values_v
 where location_segment_qualifier = 'STATE'

  /*DEFAULT TAX CODE:(HOW A TAX CODE IS CHOSEN): Usually we can define 
    any number of tax codes that we want. However while entering a transaction 
    at the line level, the tax code will default to a specific code. This is 
    done as follows.
   
   When we go the System Options under the tab "Tax Defaults and Rules" there 
   is a hierarchy mentioned under the tax code defaults,which mentions the precedence 
   of choosing the tax codes i.e first the customer site,then customer, and product
   (i.e the inventory item level) and finally "System Options". 
   If it comes to "System Options", since there is the location flexfield value 
   there, it will choose the corresponding location flexfield. There is a tax code 
   location defined in the tax code setups.That is the reason why you dont see any 
   rate specified in the tax codes Location,because it is calculated on the fly 
   (which is the sum of the sub segments)
  */ 

  /* A word about Vertex software : The document "Integrating Oracle Receivables 
   with Vertex Quantum" released by Oracle says to enable the debugging of the tax 
   calculation we need to set the following profile options. 
   Conveniently set the profile options mentioned in the note 279118.1 and get the 
   tax debug file right from the sqlplus output.
  */

  Finding the Vertex Geocode given a state,county,city combination or zip code. 

  Let us say we have a zip code 95050 which corresponds to (CA,Santa Clara, santa clara city) 

--Now go to the screen, (to get the authority which state, county,city from the zip)
 Setup => Tax => Sales Tax Rates 
 
--From the above combination , go to screen
 Setup => Tax => Locations 
  /* and choose city value in the Find list box and enter the county. Click on the required city. Now
 click on the DFF and get the Vertex gecode. Now in this case, the geocode for santa clara city is 050853180
 Usually when vertex is installed it populates a DFF values of 'VERTEX'. 
 Geocode, usually the first digit/2 digits of the geocode corresponds to the vertex state code, so in this case
 the state code for CA is 05. */

 select rowid,a.* --invno,shiptogeocode,invtotaltax,citytax, cityrate,statetax,staterate, cntyrate,cntytax
 from   vertex.regprereturnstbl a -- 30649222
 where  invdate = 20060824
 and    invno in (1190012439,1190012434)
 -- transtaxedgeocode=441136035
    -- arp_tax_view_vertex, ra_tax_exemptions_all 

  /* Typical Issue : One issue which arose is the tax calculation discrepancy.
   When we create a transaction for a specific particular customer based in 
   (Texas,Dallas,Addison) then the tax rate is calculated as 6.25%. However   
   when I lookup the tax rate for that particular city,county,state, the Vertex 
   shows that as 8.25% which is the correct rate. This was caused because for that 
   specific customer, the value of the flag "Inside City Limits" was not set at 
   the customer ship-to site level,which is the reason why it was not calculating 
   the city tax, for that particular customer. */
 
    select customer_id, party_id
 from  ra_addresses_all
 where sales_tax_inside_city_limits is not null
 
 select * from hz_locations
 

 
-- CUSTOMER INTERFACE
/*******************************
  delete ra_customers_interface_all
  
  delete ra_customer_profiles_interface
  
  delete ra_contact_phones_interface 
  
  The Customer Import done using the standard customer interface. Alternatively 
  it can also be done using the hz api, however,I believe the customer interface 
  is much better(??).
  
  The customer import references the orig_system_customer_ref between interface 
  tables. What i found is that at a bare minimum, we should have a record in 
  profile interface table(it does not take any default profile). So if we
  know the profile name in AR, we need to put that in the customer_profile_class_name 
  column. It does not matter whether we have the contacts,paymethods, banks etc 
  interface information.
  
  Incidentally if there is a record in the ra_customer_profiles_interface which is 
  not referenced by any of the records in the ra_customers_interface_all table, 
  then the "customer interface CI" thinks that it is importing the profile.  If 
  you dont give the existing AR profile name, then you have to give a whole bunch 
  of other information so that the CI will create a new profile for you.
  */ 

   insert into ra_customers_interface_all
   (orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status,
    last_updated_by ,last_update_date ,created_by ,creation_date,validated_flag)
   values (2001,'I','MY IMPORTED CUST 3','A',-1,SYSDATE,-1,SYSDATE,NULL)
   
   insert into ra_customer_profiles_interface
   (customer_profile_class_name, orig_system_customer_ref,insert_update_flag 
   ,credit_hold ,last_updated_by ,last_update_date ,creation_date ,created_by , validated_flag )
   values('DEFAULT',2001,'I','N',-1,sysdate, sysdate,-1 ,NULL);
   
   insert into ra_customers_interface_all
   (orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status,
    address1,city,state,postal_code,country,
    orig_system_address_ref,last_updated_by ,last_update_date ,created_by ,
 creation_date,validated_flag)
   values (2001,'U','MY IMPORTED CUST 3','A','870 E EL CAMINO REAL','MT VIEW','CA',95032,'US',
   'Legacy System',-1,SYSDATE,-1,SYSDATE,NULL)
   
   commit;
   
   -- Request id is the back populated column value by the customer interface program, validated flag
   --   indicates whether the record is validated or not 
   select orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status,
       validated_flag, request_id
   from   ra_customers_interface_all

 select * 
 from ra_customer_profiles_interface
 
 select * 
 from   ra_contact_phones_interface
 
 select * from ra_customers  order by creation_date desc
 
 select * from hz_parties where orig_system_reference = '2001'
 
 select * from hz_cust_accounts where party_id = 1758
  
/*
insert into ra_contact_phones_interface
(orig_system_customer_ref ,insert_update_flag ,telephone,orig_system_telephone_ref,
 last_updated_by ,last_update_date ,created_by ,creation_date,validated_flag)
values (2001,'U','6509409550','6509409550',-1,sysdate, -1,sysdate,'N');
*/

-- Autoinvoice Query.
select * from ra_interface_lines_all
where rowid in 
   (select min(rowid) from ra_interface_lines_all
    where trx_number is not null  group by trx_number)
order by trx_number
 
/* For the cash receipts , the receivable activity or trx id will be null, */

SELECT  NULL      VID
    ,NULL     PID
    ,rc.customer_number   OracleAccountNumber
    ,rc.customer_name     CompanyName
    ,acra.receipt_number  PaymentNumber
    ,arm.name    PaymentType
    ,acra.amount    Amount
    ,arpa.amount_applied  AmountApplied
    ,acra.receipt_date  PaymentDate
    ,rcta.trx_number   InvoiceNumber
    ,arpa.receivables_trx_id Rtrxid
    ,arta.name    ReceivableActivity
    ,acra.currency_code  ReceiptCurrency   
FROM    ar_cash_receipts_all  acra
    ,ar_receivable_applications_all arpa
    ,ra_customers     rc 
    ,ar_receipt_methods      arm
    ,ra_customer_trx_all     rcta
    ,ar_receivables_trx_all  arta
where acra.cash_receipt_id= arpa.cash_receipt_id
  and   acra.receipt_method_id = arm.receipt_method_id
 and  acra.receipt_date >= '18-NOV-2005'
  and  rc.customer_id = acra.pay_from_customer
--  and  receipt_number = 'WTR113004A' 
  and  arpa.applied_customer_trx_id = rcta.customer_trx_id(+)
  and   arpa.status <> 'UNAPP'
  and  arpa.receivables_trx_id = arta.receivables_trx_id(+) 
 order by 1,2,3,4,5,6,9

 /* the account name in the hz_cust_accounts is for some reason null and hence 
 the ra_customers view is looking at the hz_parties.party_name  */
    

  /* Deleting a transaction.
  Normally we would not be able to delete a transaction, however,if we set the 
  system option in AR, we should be able to do that.     
 
  Due Date(term_due_date) : The due date indicates when the invoice is due. There 
     are due dates in the tables ra_customer_trx_all and ar_payment_schedules_all. 
   But always pick it up from the payment schedules table. */

  /*Receipts API vs Lockbox
 Once the receipts data comes from the bank, it can be loaded into the AR table, 
 using the receipt api or for more simple lockbox. For receipts api, the file 
 format needs to be understood, parsed and for each such record the receipts api 
 needs to be invoked which inturn creates the receipts in AR.

    You can change the receipt amount regardless whether the receipt has been posted 
 to gl or not (or regardless of the profile option AR: Bank Charges)

   /* Payment schedule with the payment schedule id <0: All the receivable 
   activities that we define as the receivable activities for ex, prepayments, credit 
   card refunds, will go into the ar_payment_schedules_all table as well, with 
   payment schedule id < 0, so that way, some of them are available to be picked when 
   we are applying a receipt to these activities. */
 
  select payment_schedule_id, trx_number  
  from ar_payment_schedules_all 
  where payment_schedule_id < 0 
 
  /* Printing an Invoice : Also if you print an invoice, you cannot incomplete that 
  invoice any more. No,however once we create a transaction of this type, then all 
  the setting of this transaction type will go to that particular transaction. So for 
  ex, if the print type is no, and if you create an invoice of this type, then the      
  print flag of this invoice is no. So even if we change the print type =yes on the 
  transaction type after that transaction is created, it does not help. i.e you still 
  cannot print.*/


  /* Payment Netting : Payment Netting is a functionality provided in 11.5.10. Payment 
  Netting is something to do with when a Customer is also treated as supplier (for refunds 
  or any other business requirements).
  Netting would work only if your customer and the supplier happens to be the same party
  That is we create transactions for a customer and if there are any refunds to be made ,
  then we can use the same customer as a supplier and pay him. I heard from some one,
  by giving the same tax identification number for two parties they can effectively the 
  same party. Is this true? 

  (Is payment netting same as Customer Supplier Netting 
  (is Payment netting a receipt applied to another receipt.)
   
--  Incompleting an Transaction :
  To incomplete transactions in AR, the following things should be considered:- 

 The transaction should not have been posted to GL. 
 There should be no receipts for this transaction. 
 The dunning letter program should not have run for that transaction. 
 The main important thing is under System options, Trans and Customers tab, 
 "Allow Transaction Deletion" check box should have been checked. 

 So even though the payment terms are defined for installment types, there might 
 be the different payment schedules for them,but the gl_date will still be following 
 the accounting rule and hence all the revenue will be recognized in the same period,
 if the accounting rule is Immediate.

  /* Balancing Segment :
 Usually an accounting segment would have as structure like 
   Company |  Dept | Account | Line1 | Line2.
     
  When we set up the account, usually we mention what is the balancing segment. 
  
  What a balancing segment means is that for each value of this segment, the credit 
  and debit entries will cancel each other or balance each other. For ex, for any
  segment value ,say,  '01', all the entries will balance each other. Usually it is
  recommended that if you have a company segment, then you should always set the company
  as the balancing segment.
  
  However for a specific dept ,it may not balance, because it could be possible that we post credit 
  entry in one dept and debit entry in another dept account. However since both the depts
  will fall under the same company, at the company level, it should balance.
  */       

  /*Accounting Rules and Payment Schedules :
 Recognizing revenue is done using invoicing and accounting rules. 
 Billing is done using payment schedules. You can setup a payment schedule to make 
 1/4 due at each of 4 dates of the year. These are two different animals. 
 
   /*The transactions are coming from different sources,say from Order management, 
   Projects, Service Contracts etc to AR. Let us say there are two transactions 
   one coming from OM and another from Service Contracts(OKS) and both of 
   them have the trx date and GL date as 30-AUG-2006. The August period is closed and 
   the september period is open. However one of them has successfully gone thru the 
   Autoinvoice while the other has not. This could be because the transaction source 
   for each of them might have different setup values ie.
    Setup => Transactions => Sources => "GL Date in a Closed Period".
   */

  /*Dunning Letter Generate : The Dunning Letter Generate program is the standard 
   program provided by Oracle. 
   The Dunning Letter Generate Program can be invoked from the menu option */
         Print Documents => Dunning Letters     
   /*The typical important parameters of this program are the letter set and the 
   transaction types. Actually we can run this program even for a particular customers,
   so that it will print the dunning letter corr to the invoices of that particular 
   customer only. The trans type low and high means, it will take all the transaction types
   which falls lexically between those two.*/
        
   /* The standard program will spawn the program "Dunning Letter Print from 
   Dunning Letter Generate".
   For testing the dunning process we can actually change the due date of a 
   particular invoice even if it has been posted to the GL(or printed). This can 
   be done from the Collections menu. */
    Collections => Account Details 
  
  /*  So this particular function is only available for the collectors.  
  
   Verisign Custom Process : In Verisign, the standard program Dunning Letter Generate 
   has been modified to call another custom program which actually reads thru a 
   profile value and get the different dunning buckets and based on that,it would 
   send different kinds of email messages.    
  
 /*One possible reason a particular customer might not get a dunning letter 
 even though he might have the invoices due is because of the setting at the site level.*/
     Customers => bill to site =>  "Profile: Document Printing" tab 
                              => "Send Letters" Check box 
     
  /*When when the Dunning Program Is run with a specific Dunning Letter Set , 
    It will pick up only those invoices whose dunning letter set matches the Letter Set Parameter.*/    
   Customers => Bill to site =>  "Profile: Document Printing" tab => Dunning Letter Set needs to be set.
  -- Look at the consolidated dunning check list document.





--Receipt Amount Update :
--------------------------
You need to set Menu Exculsion function of "Receipt: Update" to achieve this. 
An ex of error caused by updating the receipt amount after it has 
been posted to GL
/*
The original receipt was created for the amount of 119.70. The receipt 
was applied to invoice 99091272 for the amount of $ 39.90. There was 
$79.80 left unapplied.
The left over of the payment was supposed to be going to Bad Debt 
reserve. In July, the amount on receipt number 3103 was changed from 
$119.70 to $39.90 and a miscellaneous receipt was created to bad debt for $70.90. 
The correct way to deal with this situation is:

Unapply and Reverse the entire receipt ($119.70)
Create one receipt for $39.90 and apply it to the open invoice.
Create a second miscellaneous receipt for $79.80 for bad debt.

I think if we reverse the entire thing and re-enter the receipts 
again the correct way, then will be fine.

*/ 
 
 
/*"AR: Allow Overapplication In Lockbox" and "Allowing the Overapplication" :
Issue :
      If the profile option "AR: Allow Overapplication In Lockbox" is set 
 and the transaction type is not set, the remainder of the receipt will be 
 unapplied. If the transaction type allows overapplication, but the profile 
 option does not, then you will still have the remainder of the receipt 
 unapplied. Now our requirement is that the credit memos should be able to 
 drive the invoices to zero or negative balances. However when the lockbox 
 applies receipts to invoices, they should not be able to drive the invoice 
 balance to negative and amount should be shown as Unapplied. Ideally this 
 can be obtained by setting the profile option 
 "AR: Allow Overapplication In Lockbox" to "No" 
 with the transaction type "Allowing the Overappliction". However what I have 
 seen is that even though in our production system this particular profile option 
 is set to No, it is still going ahead and doing the Overapplication and driving 
 the invoices to Negative balances.

Fix : 
 I have researced on this and found that, this is an unpublished Bug 4931731 
 with oracle. Oracle has identified it as a bug and released a Document 
 "Lockbox Program Ignores Profile Option 'AR: Allow Overapplication In Lockbox'
 And Applies Receipts To Closed Transactions. Note:358321.1)" in Feb 2006. 
 They also have a Standalone Patch (patch 4904833) ready for this particular one.
*/

   -- Query giving the credit limits at the customer site level.
    
   select  
         hca.account_number        customer_number
        ,hcsua.location     location
        ,hcpa.overall_credit_limit    overall_credit_limit
        ,hcpa.trx_credit_limit        order_credit_limit
   from    hz_cust_accounts hca
      ,hz_cust_acct_sites_all hcas 
      ,hz_cust_site_uses_all hcsua
      ,hz_cust_profile_amts hcpa
   where  hca.cust_account_id    = hcas.cust_account_id
   and    hcas.cust_acct_site_id = hcsua.cust_acct_site_id  
   and    hcsua.site_use_id   = hcpa.site_use_id  
   and    hca.cust_account_id      = hcpa.cust_account_id
   and    (hcpa.overall_credit_limit > 0 or hcpa.trx_credit_limit > 0)
   and    hcsua.site_use_code    = 'BILL_TO'
   --and  account_number       = '59402'
   and    hcas.status = 'A' and hcsua.status ='A' 
   and hca.status ='A'
   order by hca.account_number

REVENUE MANAGEMENT AND REVENUE POLICY :
---------------------------------------

There is a separate engine called Revenue Management Engine in AR. The
timing of the revenue recognition program is primarily controlled by 
the Revenue Management Engine. That is its main functionality.

 - Use the revenue policy tab in the System Options window to specify 
 your enterprise's revenue policy. 
 - The revenue management engine uses the information you enter in 
   this tabbed region to make automatic revenue recognition decisions 
   for your imported invoices. 
 - The Revenue Management engine compares each invoice that you import
   against the infromatoin that you enter in the revenue policy tab.


The revenue Policy tab has mainly 5 fields.

  Standard Refund Policy Days :
     This field is related to invoice related to the service contracts. 
     If the contract refund period > refund period specified here,
     the revenue Mgmt automatically defers the revenue on that line.

  Payment Term Threshold Days :
     This is the maximum days for the payment term. If an invoice payment
     terms(say net45) is greater than the payment term  specified here
     (say, 40), then the Revenue Management engine defers the revenue 
     for that particular invoice.

  Credit classifications for deferred Revenue :
  First ,second and third selection :   These three fields are basically
  related to the noncreditworthy customers. If the Rev Mgmt recognizes 
   an invoice corresonding to a customer with bad credit, then the engine
   automatically defers that invoice revenue. 

  In all the above, we mentioned that Rev Mgmt is deferring the revenue
  for that line, what I think Revenue Management is doing is to update
  the interface lines with the contigency code accodingly.

  Event-Based Revenue Management, is said to be enabled if either one of
  them is enabled.
 Atleast one revenue policy option is being set OR
  Imported billing lines are associated with contigency codes.

11.5.9 & 11.5.10 Difference for AR :
/************************************

1)  The receipt workbench screen in 11.5.9 (refer to Page 2) is different 
    from receipt screen in 11.5.10 (Page 3).  The screenshots of both of these 
    are in the document. From 
   Receipts => Receipts, 
 The search and Apply button has been added in 11.5.10.
 The different tabs of the receipt workbench have been accommodated 
        in only two tabs in 11.5.10. (Main & More)

2) In 11.5.10, in the setup => transactions=> transaction sources
 The “receipt handling for Credits” field has been added.(Page 4)
 which is not there in 11.5.9.

3). In 11.5.10, in the setup => receipts => receivable activities,
 A new type of receivable activity (Payment Netting) has been added 
 which was not there in 11.5.9.

4). There is a difference in the screens in 11.5.9 and 11.5.10 for the freight
    carriers’ setup.
 From setup => System => Freight Carriers , the freight carrier screen 
    is different in 11.5.9 (Page 6) and 11.5.10( Page 7)
 The number of tabs are different and more in 11.5.10 than 11.5.9.

5)  There is a difference in the system Options screen in 11.5.9 and 11.5.10.
    There is an additional tab by name “Claims” in the System Options window 
    in 11.5.10 (page 8 ) which is not there in 11.5.9(Page 9)

6)  There is a difference in the layout of the locations form in 11.5.9 
    (Page 10)  and 11.5.10( Page11)
    Setup => System => Organizations => Locations  
 There is an additional field timezone in the locations form 11.5.10( Page11).

7)  There is an additional function in 11.5.10 (Page 12) And it is 
 “Correct Invalid GL Accounts”.
        (This function is not there in 11.5.9)
 

RECEIVABLES ARCHIVE & PURGE PROCESS
---------------------------
Archive Preview

Archive Header
Archive Header Report
Archive Detail
Archive Detail Report

Archive Restart
Archive Selection

Archive Summary Report

Archive and Purge
New Archive and Purge
Call New Archive and Purge

Archive to File
--

Usually the purge program will have a criteria. if there is a chanin of transactions, then the archive and purge program will delete the entire chain, if any one transaction does not satisfy the purge criteria.

Clear archive tables, ar_archive_header, ar_archive_detail
Ensure that no other concurrent programs are running and no users are accesssing the system.
Runn the OSC sales compensation interface, to move the data from the trx hdr,line,lne_salesreps
Intrastat ??
verify autoinvoice tables are empty (otional)
verify lockbox tables are empty (optional),both ar_payments interface and ar_interim cash lines tables
Run the tax reports and store them in file format
backup the database.

Archive and Purge Cycle : 
-------------------------
The cycle for the standard Archive and Purge program is divided into four separate
processes:  
 Selection and Validation, 
 Archive, 
 Purge, and 
 optionally Copying to a file.


General Questionnaire :
----------------------
 
 1. What are the issues with closing a period.

 Typically let us say you are trying to close a period in AR or AP. However 
 when we try to do that the system will not let you do that. In that case, we 
 can run the reports like Unposted Items Report and Incomplete Invoices Report etc.
 Unposted Items report ,as mentioned before, will print all the items that 
 are not being posted to GL yet. These items can be because of the incorrect 
 (cr,dr) distribution differences that exists. For ex, for a particular 
 transaction,there could be cr entry($5.5) and debit entry($6). We need to 
 resolve them ,post them to gl and try to close the period  again. */
 
 2. How to get Customer Balances from backend:
 
 How to find a customer balance : 
   Collections => Account Details  
 Or select from this view. 
  
  select balance,acctd_balance,location
  from ar_customer_accounts 
  where customer_id = 671040
  and currency_code = 'USD'
 
 3. What happens when two consecutive periods are open,say June and July and 
 you are trying to issue a credit memo on July 1st for a June Invoice. 

 GL date would be the system date. However we would like to have the 
 GL date of the CM to be the same as the GL date of invoice. So we have to
 manually go and change the GL date to be in the same month i.e in June.
 This is done for the purpose of revenue recognition process.

 4.What is the difference between Bill in Advance and Bill in Arrears 
   for the Invoice rule :
   Bill in Advance => Receivable is recognized immediately
   Bill in Arrears => Receivable is not recognized immediately and
     it is put in a Unbilled receivables initially and then in 
    recognized in portions.
 
 5. Difference between Invoice rule and Accounting rule :

    Invoice Rule determines how the receivable is recognized while,
    Accounting Rule determines how the revenue is recognized.

   And you cannot have accounting rules with out specifying the Invoice rules.
 
 6. What is the difference between Invoices with rules and Invoices without Rules.

  The accounting is done by AutoAccounting and Revenue recognition 
  for invoices without and with rules respectively.

   AutoAccounting => For invoices without rules;
   Revenue Recognition => For invoices with rules;

   so the bottomline is even autoaccounting can be used for recognizing
   revenue in the case of invoice without rules.

 7. You have created a remittance batch for a receipt by providing a 
    wrong bank name.Now what are we supposed to do as a first step?

    Should we delete the remittance batch?
 
 8. What are the different steps that Autoinvoice does
 Import the invoices
 Try to complete them.
 Import the credit memos
 Try to apply the credit memos to the associated invoices.
 Try to run the Prepayment matching program so that if 
    there are any prepaid receipts,they can be applied to
    the just imported invoices.
 Try to run the revenue recognition. 

 9.What is Revenue Accounting Wizard : 
   Revenue accounting wizard is a tool which lets you make the adjustments
   to the accounting or the amounts for all those invoices and credit 
   memos with defined accounting rules. Revenue is said to be scheduled if 
   the distributions are created. Most generally the revenue accounting wizard
   is used to adjust the deferred revenue invoices. 
                  Or 
   You can manually defer the revenue corresponding to any invoice using the 
   Revenue Accounting wizard.

 10. How to recognize deferred revenue :
    Receivables identifies deferred revenue for invoices with rules having deferred 
 flag set. The only way to recognize revenue for such invoices is to go to the 
 Revenue Accounting wizard and go to Actions wizard. 

 11. What items are processsed by Revenue Recognition.
    Interestingly Revenue Recognition only processes the Invoices and Credit memos
 (not debit memos, chargebacks, adjustments etc). Although this
    needs to be confirmed.

 12. Use the revenue accounting feature to make revenue adjustments to completed
     invoices and credit memos. 

 13. Can I apply a receipt of USD or Credit memo of USD to an invoice of INR.

     Yes, cross currency receipt application is available,however we need to 
  set the appropriate profile option. However if you are trying to apply
 a credit memo then the credit memo and transaction(Or invoice) currency
 should be the same as of R12(12.0.6). 
 
 14. Are receivable and revenue same as far as autoaccounting is concerned??
 
     No. while setting up Autoaccounting, in receivable account, we cannot 
  choose the standard line corresponding to inventory items, as the receivable account 
  corresponds to the whole invoice and not the lines.
  However in the revenue account setting, we can choose all the values of 
  standard lines, transaction type, sales person etc.
 
 15. What is the difference between two accounting rule types??
 Accounting, Fixed Schedule
 Accounting, Variable Schedule

    In the Accounting, Fixed Schedule, you specify the schedule at the time of
    the rule definition, i.e you candefine 12 monhths and the rev rec program
    will apportion the revenue accordingly.
    In the Accounting, Variable Schedule, you cannot specify the schedule 
    at the time of rule definition. However youcan specify the scheduleat the
    time of the invoice creation or import.

15. What are the different types of transaction from Revenue Recognition stand point ?
 Recognition of revenue from four types of transactions:

   1. Revenues from selling inventory are recognized at the date of sale often 
           interpreted as the date of delivery.
   2. Revenues from rendering services are recognized, when services are 
           completed and billed.
   3. Revenue from permission to use company’s assets (e.g. interests for using 
          money, rent for using fixed assets, and royalties for using 
     intangible assets) is recognized as time passes or as assets are used.
   4. Revenue from selling an asset other than inventory is recognized at the 
          point of sale, when it takes place.

16. What is the Revenue Recognition Principle.
   The revenue recognition principle states that Companies should recognize revenue
       when the revenue is realized and earned.

 Revenue is said to be realized,when the goods are exchanged for cash
 Revenue is said to be earned, when the earning process is complete, i.e if the
   acct rule is 12 months, after 12 months, the revenue is completely earned.

   The terms realizable and realized are used interchangeably.
   
17. What is Scheduled Revenue and Unscheduled Revenue??

    Revenue is said to be scheduled for a line, if distribution records are created for all the 
   periods corresponding to the accounting rule specified by that line item.
   
 Revenue is said to be unscheduled, if the line is associated with an accounting
 rule which is deferred, i.e every thing is associated with an unearned single 
 distribution.
   
18. why would you post few things on deferred revenue account typically??
 
 The following are the reasons why why you would put a particular transactions revenue on
 a deferred revenue and they are 
  
 For ex, the collectibility of the line items like line charges, lease payments
 loan fees, other charges is in doubt and hence should not be considered as
 earned revenue until the payment is received. Hence such kind of invoice lines
 will be put under deferred revenue. However when the payment is received and
 when the payment is applied to this kind of line items, its no longer deferred
 revenue and will be considered as earned revenue. 

 Receivables uses the Credit management module to check the customers credit 
 worthiness. If the customer is not creditworthy, then the revenue corresponding
 to all the invoices lines for that customer will be deferred.
 
 The customers should have a PO(on their side),otherwise its not a good idea for us to 
  put that in earned revenue, we should instead put it in a deferred revenue.

19. Are there any exceptions to the payment based revenue recognition.
  
   Yes. We have seen that application of a payment to an invoice can trigger the
 revenue recognition process. However if an invoice has been manually deferred then 
 the application of receipt amount to that invoice will not trigger revenue 
 recogniztion for that invoice.    
  
20.  WHAT are the privileges that a COLLECTOR can exercise ??
      -A collector can change the due date of a transaction even after it has been
       posted to GL.
      - A collector can put a credit hold, so that no new orders are booked,but can be entered.
      - A collector can record as calls, any conversation that he has with thecustomers
          called the call log; a call should always have  a contact
      -If your customer disagrees about the outstanding balance for an item, you can mark
            that item or a specific amount due as ’in dispute.’ Amounts that are in dispute appear
            in collections reports. Receivables does not prevent you from applying payments to
            disputed transactions.
           customer calls => actions => select transactions  => save => actions
        => give a dispute reason and dispute  amount(To remove the item from dispute put a 0 amount)  
      - What I have seen is that you can select actions either directly from the customer
       calls form or select a specific trx, then choose the actions function.
      - A collector can use the scheduler window to "Complete" a call. Completing a call
       means that issue is closed. Disputes cannot be seen in the customer calls
       window.
      - He can record the customer correspondences which are typically,
        printing account statements
        printing dunning letters
        making customer calls.
   - View customer balances by summary,detail, by aging buckets
   - He can see dunning history in the collections workbench.

21. What are the two methods of dunning letter generation. 

 The two methods are "days overdue" method and "staged dunning" method.
 
 days overdue : if a invoice is due by 10 to 20 days, first dunning letter will be sent,
   and if it is due by 20 to 30 days, second dunning letter is sent etc.
 staged dunning : if a invoice is picked by Dunning letter generate program ,then its
   dunning level goes up 1. And if the dunning level is say between 1 and 5, then
   first dunning letter will be sent etc. Usually once a dunning level is incremented,
   the program will wait for a certain days, before it increments the level for an item.      
        
22.  What is simple flow of Dunning program.
   Dunning letter generate program runs probably once in a month. The mandatory parameters
   it takes are letter sets from and to.
   -- For each letter set in the range From to To, it will find out all the customers 
      that are tied to that particular letter set. Each customer is tied to a dunning 
   letter set thru the profile.
   -- For each such customer it will check to see if any items are due and generate 
      dunning letters appropriately. 
   -- If you specify a customer name in the parameter as well, then it will just narrow 
      down the search only for that customer name. 

23. What is a statement cycle and statement site. 
  Usually each customer will have multiple sites,with each site having a use or
  business purposes like bill-to,ship-to etc or there could be multiple bill-to
  sites. If a statement site is not specified, each customer site is sent a letter
  otherwise only that site is sent.
  A statement cycle is like a calendar where you specify the date on which you
  want to send the statement periodically.

24. What does a receipt class or a payment method say ?
 All customer payments of a particular payment type like credit card or bank account will
 go to a corresponding internal remittance bank account. 
 For ex,
 All customer credit card payments should go to bank of america account one.
 All customer bank account payments should go to bank of america account two.
 
25. What is a prepayment ?
   A prepayment can be defined as a payment even before the goods or services are delivered,
   or its a payment even before an invoice is sent to the customer.
   Ex : downpayment; prepayment for consulting services.  
    
 
26. What are cross currency receipts ??
 A cross currency receipt is one,where a receipt of say GBP is used to pay the invoice
 of USD. AR handles this by posting the following difference to a gain/loss account
 receipt amount in func curr (at receipt date) - invoice amount in func curr(at invoice date)
   = foreign exchange gain or loss. 
 
27. What are receipts at risk. 
 The receipts for this risk which have not cleared the bank. when seeing the customer 
 balance, we can choose to include/not include the receipts at risk.  
 
28. Explain how the revenue entries are for an invoice will bill in advance.
 /*As mentioned earlier, if the invoicing rule is not specified, then you cannot
 specify the accounting rule. If the invoicing rule is "Bill in Advance" then
 you can specify any accounting rule, and the Unearned Revenue(UER) account will 
 be hit ,when the revenue recognition program runs. 
 If the invoicing rule is "Bill in Arrears" then you can specify any accounting 
 rule, and the Unbilled Receivables(UBR) account will be hit ,when the revenue 
 recognition program runs.
 
 Let us briefly understand how the accounting entries look like if we specify 
 bill in advance and how Unearned Revenue entries will be :
 
 For example, a invoice was created on May 1 of USD 1200, entries will be

    1-May-08: Receivables Dr 1200
        Unearned Revenue Cr 1200
    
    1-May-08: Unearned Revenue Dr 120
        Revenue Cr 120
    
    1-Jun-08: Unearned Revenue Dr 120
        Revenue Cr 120
    
    This way at the end of the 10 months, there will be "0" balance in the 
 Unearned Revenue A/C and the Revenue A/C will be credited every month 
 for equal amount and finally the total amount will be in revenue.  
 */
 
29. Explain how the revenue entries are for an invoice with bill in arrears.
    You can use this invoicing rule to recognize receivable (remember 
 receivable not revenue) at the end of the revenue recognition schedule. 
 Let us explain this with an example of an invoice with different invoicing rules,
    
 
     Invoice  : $2000
     Invoicing Rule  : Bill in Advance
     Accounting Rule : 10 Month
     Invoice date  : 10-JAN-2008; Payment term : Net 15
     Due date      : 25-JAN-2008
     
 -----------------
  
     Invoice  :  $2000
     Invoicing Rule  : Bill in Arrears
     Accounting Rule : 10 Month
     Invoice creation date = 10-JAN-2008; Payment term : Net 15
     Invoice date is changed to  10-NOV-2008;
     Due date      : 25-NOV-2008 (see the due date is 10 months + net 15)
    
    Hence if you see above, the invoice is having an invoice date as 10-NOV-2008,
 even though the invoice creation date was 10-JAN-2008. Now when the 
 revenue recognition program completes, the account that is hit here is
 Unbilled Receivables (instead of unearned revenue),otherwise eveything remains
 the same. And to apply the same ex, we will have the accounting entries as,
 */
 For example, a invoice was created on May 1 of USD 1200, entries will be

    1-May-08: Revenue Cr 120
        Unbilled Receivables Cr 1200
    
    1-May-08: Unbilled Receivables Dr 120
        Revenue Cr 120
    
    1-Jun-08: Unbilled Receivables Dr 120
        Revenue Cr 120
    
 
    1-Feb-09: Unbilled Receivables Dr 120
        Receivable  Cr 1200
        Revenue Cr 120
     Unbilled Receivables cR 1200
     
    This way at the end of the 10 months, there will be "0" balance in the 
 Unbilled Receivables A/C and the Revenue A/C will be credited every month 
 for equal amount and finally the total amount will be in revenue.  
 */
 
30. What is the most important point in the Receipts functionality.
 EACH SPECIFIC CUSTOMER PAYMENT METHOD IS ASSOCIATED WITH A SPECIFIC REMITTANCE BANK ACCOUNT.

31. What is the most important concept while defining the receipt classes, payment methods ?
Firstly the three important components are 
Definition of Receipt classes, Payment methods
Definition of banks, bank accounts.
Definition of transactions which uses the above.
Now the most important concept is ,again
EACH SPECIFIC CUSTOMER PAYMENT METHOD IS ASSOCIATED WITH A SPECIFIC REMITTANCE BANK ACCOUNT.

For ex;  let us say customers use the credit cards to pay their invoices and let us say they
use visa card and discover card.
Then we can define a payment method as say 
      DISCOVER CARD PAYMENT => all payments from DISCOVER should go to BOFA remittance account 154245.
      VISA CARD PAYMENT => all payments from VISA should go to BOFA remittance account 154245.

32). Does the dunning letter print for each due item,or per customer ?? 
Dunning letter is generated per one debit item.  If a customer has 2 due items; the 
system prints two dunning letters. This makes sense as those two items might be under 
two different buckets. 

33). What are late charges  ??
late charges : Late charge functionality is not available in 11.5.10.2. That 
functionality is available only in R12. Basically think like this. If the customer pays 
early ,then he might get a discount (if the payment term is say net 15,5% discount).
However if the customer pays late, then he might get charged. This will happen at the 
time of receipt application,just like the case of applying a discount.  The system 
creates another line of type "CHARGE" if the invoice is due at the time of application.
Autoinvoice also handles the late charges,however there are certain rules that need 
to be applied. That is certain attributes need to set properly and certain columns 
should be left null. The documentation should provide these details. 
You can set at the  invoice header level (more tab) ,whether this invoice will have 
late charges. if yes, then the system will go look at the customer profile and 
apply the late charges. 

34). What is an item in dispute ?
Sometimes customer calls the company and disagrees with the invoice amount or something, 
then the collector can record that particular item as in dispute. He does that in 
customer calls form. 

35). What are deductions and Claims ?
Deductions are a functionality that is existing only in R12.  
In response to an invoice, a customer can make a short payment, which means the amount 
is less than the invoice amount, which could be because of the promotional deals, 
short shipments ,damages etc.    
 OR 
he could make an over payment as well.
If the remittance advice does not supply you with enough details like a promo code 
etc, AR lets you create a claim by specifying an amount in the claim feild for 
this deduction. The AR lets you interact with the Trade management to deal with 
these deductions. 

36) can we import bank statements thru lockbox, and if so how?
 Not sure. However we can import the lockbox files thru the bank statement loader 
  program which comes with the Cash management module. 




for bank account refund
PAYMENT method should be there
bank account details should be there
credit memo approval limits should be there.