Friday, December 31, 2010
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.
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: 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.
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.
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.
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.
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.
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.
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.
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.
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:
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'
);
Payable Invoice Data in other Modules
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
Subscribe to:
Posts (Atom)
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.