Friday, December 31, 2010

Autoinvoice Setup: Profile Values

In this post, i have given a consolidated data taken from different metalink notes and other sources about the Profile options associated with Autoinvoice Program.
                                                              

1. Security Profile
There are three profile options which govern access to data partitioned by organizations.

MO: Operating Unit
R11i profile option that will be maintained for those products and customers not leveraging Multiple Organizations.

MO: Security Profile
The MO Security Profile controls the list of operating units that a responsibility can access. So you would assign the Security Profile that you just created to this profile option. The lowest level you can set the profile option for is the responsibility level.

MO: Default Operating Unit
This allows you to specify an operating unit that will be the default when you open different subledger application pages. Because users can access multiple operating units, you may want to set up a default operating unit rather than forcing users to constantly have to choose one during data entry.  When we talk about the User Preferences later, you can also specify a different default operating unit than the one assigned to this profile option. 

2. AR: Autoinvoice Gather Statistics
This profile option determines if the AutoInvoice Master program analyzes and gathers information about the interface tables each time AutoInvoice is run. Analyzing tables ties up system resources, but in some cases may address AutoInvoice performance issues.

If the value for this profile option is set to Yes, or is null, AutoInvoice analyzes the interface tables and gathers statistics. If the value is set to No, AutoInvoice does not analyze the interface tables.

This profile option can be set by the system administrator at the site, application, responsibility, and user levels. The user can also update this profile option.

3. AR: Default Exchange Rate Type
This option determines the default exchange rate to use when converting foreign currency transactions to your functional currency.
Valid values are:
Corporate Exchange Rate - An exchange rate you define to standardize rates. This rate is usually a standard market rate determined by senior financial management for use throughout the organization.
Spot Exchange Rate - An exchange rate to perform a conversion based on the rate on a specific date.
User Specified Rate - An exchange rate to specify when entering a foreign currency transaction.

4. AR: Maximum Lines per AutoInvoice Worker
This profile option lets you set a maximum number of lines per AutoInvoice worker. The value for this option can be set by the system administrator at the site, application responsibility, and user levels, but cannot be updated by the user. This profile option has no default value.

5. AR: Use Invoice Accounting For Credit Memos
This profile option determines whether to assign your credit memo to the same accounts that are assigned to the invoice you are crediting.
The value for this option can be set by the system administrator at the site, application,responsibility, and user levels, but cannot be updated by the user. 
Note:
If you are interfacing data from Project Accounting, you need to set this profile to No at the application level for Oracle Projects. This setting will then allow data to be passed into RA_INTERFACE_DISTRIBUTIONS_ALL table for Projects Invoices. 

6. Sequential Numbering
Sequential Numbering assigns numbers to documents that you create in Oracle financial products. For example, when you are in a window that creates invoices, each invoice document can be numbered sequentially.
Sequential numbering provides a method of checking whether documents have been posted or lost.
Not all windows within an application can be selected to support sequential numbering.
Sequential Numbering has the following profile option settings:
Always Used - You may not enter a document if no sequence exists for it.
Not Used  - You may always enter a document.
Partially Used - You will be warned, but not prevented from entering a document, when no sequence exists.

Friday, December 31, 2010 by Team search · 0

Wednesday, December 29, 2010

How Payable Invoices related Reconciliation and Currencies Data is stored in Oracle Apps R12? (Cash Management and General Ledger)

                                                                                                        

In this post, we will find the tables involved in storing the Reconciliation and Currency Data related to the   Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

Reconciliation - Tables Involved
    CE_STATEMENT_RECONCILS_ALL
    CE_STATEMENT_HEADERS
    CE_STATEMENT_LINES

Query:
SELECT DISTINCT cel.*
FROM   CE_STATEMENT_LINES cel,
       CE_STATEMENT_HEADERS ceh,
       CE_STATEMENT_RECONCILS_ALL csr,
       AP_INVOICE_PAYMENTS_ALL aip,
       AP_CHECKS_ALL ac
WHERE cel.statement_header_id   = ceh.statement_header_id
AND   ac.check_id               = aip.check_id
AND   aip.invoice_id            = '166014'
AND   aip.check_id              = ac.check_id
AND   ac.bank_account_id        = ceh.bank_account_id
AND   TO_CHAR(ac.check_number)  = cel.bank_trx_number
AND   csr.statement_line_id     = cel.statement_line_id;

Currencies - Tables Involved
    FND_CURRENCIES
    AP_INVOICES_ALL
    AP_SYSTEM_PARAMETERS_ALL

Query:
SELECT *
FROM   FND_CURRENCIES
WHERE  currency_code IN
  (SELECT a.invoice_currency_code
  FROM    AP_INVOICES_ALL a
  WHERE   a.invoice_id = '166014' 
  UNION 
  SELECT  b.payment_currency_code
  FROM    AP_INVOICES_ALL b
  WHERE   b.invoice_id = '166014' 
  UNION 
  SELECT  c.base_currency_code
  FROM    AP_SYSTEM_PARAMETERS_ALL c
  WHERE   NVL(c.org_id, -99) = '911'

Wednesday, December 29, 2010 by Team search · 0

How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

                                                                                  

In this post, we will find the tables involved in storing the Payment Data related to the   Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance. 

AP_TERMS
SELECT *
FROM   AP_TERMS
WHERE  term_id IN
       ( SELECT DISTINCT terms_id
           FROM   AP_INVOICES_ALL
             WHERE  invoice_id = '166014'
       );        

AP_TERMS_LINES 
SELECT *
FROM   AP_TERMS_LINES
WHERE  term_id IN
       ( SELECT DISTINCT terms_id
           FROM   AP_INVOICES_ALL
             WHERE  invoice_id = '166014'
       ); 

AP_PAYMENT_SCHEDULES_ALL 
SELECT
  amount_remaining,
  batch_id,
  due_date,
  gross_amount,
  hold_flag,
  invoice_id,
  payment_num,
  SUBSTR(payment_status_flag,1,1) payment_status_flag,
  org_id
FROM
  AP_PAYMENT_SCHEDULES_ALL
WHERE
  invoice_id = '166014'; 

AP_INVOICE_PAYMENTS_ALL 
SELECT
  check_id,
  SUBSTR(invoice_payment_id,1,15) invoice_payment_id,
  amount,
  payment_base_amount,
  invoice_base_amount,
  accounting_date,
  period_name,
  posted_flag,
  accounting_event_id,
  invoice_id,
  org_id
FROM
  AP_INVOICE_PAYMENTS_ALL
WHERE
  invoice_id = '166014'
ORDER BY check_id ASC; 

AP_PAYMENT_DISTRIBUTIONS_ALL 
SELECT tab.*
FROM   AP_INVOICE_PAYMENTS_ALL aip,
       AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE  aip.invoice_payment_id = tab.invoice_payment_id
AND    aip.invoice_id         = '166014';

AP_CHECKS_ALL 
SELECT
  check_id,
  check_number,
  vendor_site_code,
  amount,
  base_amount,
  checkrun_id,
  checkrun_name,
  check_date,
  SUBSTR(status_lookup_code,1,15) status_lookup_code,
  void_date,
  org_id
FROM
  AP_CHECKS_ALL
WHERE check_id IN
      ( SELECT DISTINCT check_id
        FROM   AP_INVOICE_PAYMENTS_ALL
        WHERE  invoice_id = '166014'
      ); 

AP_PAYMENT_HISTORY_ALL 
SELECT
  payment_history_id,
  check_id,
  accounting_date,
  SUBSTR(transaction_type,1,20)    transaction_type,
  posted_flag,
  SUBSTR(accounting_event_id,1,10) accounting_event_id,
  rev_pmt_hist_id,
  org_id
FROM
  AP_PAYMENT_HISTORY_ALL
WHERE check_id IN
      (SELECT DISTINCT check_id
       FROM AP_INVOICE_PAYMENTS_ALL
       WHERE invoice_id = '166014'
      )
ORDER BY payment_history_id ASC; 

AP_PAYMENT_HIST_DISTS 
SELECT aphd.*
FROM   AP_INVOICE_DISTRIBUTIONS_ALL aid,
       AP_PAYMENT_HIST_DISTS aphd,
       AP_PAYMENT_HISTORY_ALL aph
WHERE  aid.invoice_id              = '166014'
AND    aid.invoice_distribution_id = aphd.invoice_distribution_id
AND    aph.payment_history_id      = aphd.payment_history_id;


AP_RECON_DISTRIBUTIONS_ALL  
SELECT *
FROM AP_RECON_DISTRIBUTIONS_ALL
WHERE check_id IN
  ( SELECT check_id
    FROM AP_INVOICE_PAYMENTS_ALL
    WHERE invoice_id = '166014'
  );
  
AP_DOCUMENTS_PAYABLE 
SELECT
  pay_proc_trxn_type_code,
  calling_app_doc_unique_ref1 check_id,
  calling_app_doc_unique_ref2 invoice_id,
  calling_app_doc_unique_ref4 invoice_payment_id,
  calling_app_doc_ref_number invoice_number,
  payment_function,
  payment_date,
  document_date,
  document_type,
  payment_currency_code,
  payment_amount,
  payment_method_code
FROM
  AP_DOCUMENTS_PAYABLE
WHERE calling_app_id              = 200  -- Application id for Payables
AND   calling_app_doc_unique_ref2 = '166014'; 

IBY_DOCS_PAYABLE_ALL 
SELECT *
FROM   IBY_DOCS_PAYABLE_ALL
WHERE  calling_app_id            = 200
AND    calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL 
SELECT *
FROM   IBY_PAYMENTS_ALL
WHERE  payment_id IN
       (SELECT payment_id
        FROM   IBY_DOCS_PAYABLE_ALL
        WHERE   calling_app_id              = 200
        AND     calling_app_doc_unique_ref2 = '166014'
        ); 

IBY_PAY_INSTRUCTIONS_ALL 
SELECT *
FROM   IBY_PAY_INSTRUCTIONS_ALL
WHERE  payment_instruction_id IN
       (SELECT payment_instruction_id
        FROM IBY_PAYMENTS_ALL
        WHERE payment_id IN
              (SELECT payment_id
               FROM   IBY_DOCS_PAYABLE_ALL
               WHERE   calling_app_id              = 200
               AND     calling_app_doc_unique_ref2 = '166014'
              );
        );
 

by Team search · 3

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.