Customer Conversion/Interface Techniques in Oracle EBS R12

These 3 methods can all be used to create customer records. Following are some considerations to help you decide which method to use:


Customer Interface:

If you want to create accounts and the volume is data is high, then it is better to use Customer Interface program. These would be more optimal for bulk processing than the row by row TCA APIs. It also creates the Party records as by-product.

TCA APIs:

TCA APIs are the best if you want to process a smaller volume of data. TCA APIs are designed for performing row by row operations as if you were entering data into the Customer form. They are not designed for bulk loading of data.
TCA APIs do not scale well for large volumes of data as they consume a lot of CPU time by inserting records into related tables one at a time, see Bug 4724425 TCA APIS BURNING CPU TIME BY RUNNING HUGE NUMBER OF DECODE STATEMENTS


Bulk Import:

The Bulk Import only creates Parties, not Accounts. So, if you use Bulk Import, you still need to create Accounts via TCA APIs if accounts are needed as well.

Read More

Mandatory Column For GL_Interface

Column	Purpose
SEGMENT1 through SEGMENT30	Identifies the Accounting Flexfield segment values of the journal
CODE_COMBINATION_ID	Is used as an alternative to the SEGMENT values
ACTUAL_FLAG	A for actual amounts
REFERENCE1	Used to create a journal batch name of the format REFERENCE1Request IDActual FlagGroup ID
REFERENCE4	Used to format the journal-entry name
ACCOUNTED_DR	A debit amount
ACCOUNTED_CR	A credit amount
ACCOUNTING_DATE	Determines in which accounting period the journal appears
STATUS	The value NEW
LEDGER_ID	Determines in which ledger the journal appears
USER_JE_SOURCE_NAME	Determines which source the journal has
USER_JE_CATEGORY_NAME	Determines which category the journal has
CURRENCY_CODE	Determines which currency the journal has

Read More

Creating and Extracting a tar file

Creating a Tar file:-

Step 1- Create a folder and dump the requested files in unix.

Step 2- tar cvf <tar_file_name>.tar  *.*

Extracting from the Tar:-

Step 1- Go to a folder where you wanted to extract the files from the Tar file

Step 2-tar xvf <tar_file_name>.tar

Read More

Accounting FlexFields of Bank Account in R12

alter session set current_schema=apps;

SELECT cba.Bank_Account_Name,
  cbbv.Bank_Name,
  cbbv.Bank_Branch_Name,
  cba.Bank_Account_Num,
  gcc_asset.segment1||'.'||gcc_asset.segment2||'.'||gcc_asset.segment3||'.'||gcc_asset.segment4||'.'||gcc_asset.segment5||'.'||gcc_asset.segment6||'.'||gcc_asset.segment7||'.'||gcc_asset.segment8||'.'||gcc_asset.segment9 CASH_ACCOUNT,
  gcc_gain.segment1||'.'||gcc_gain.segment2||'.'||gcc_gain.segment3||'.'||gcc_gain.segment4||'.'||gcc_gain.segment5||'.'||gcc_gain.segment6||'.'||gcc_gain.segment7||'.'||gcc_gain.segment8||'.'||gcc_gain.segment9 GAIN_ACCOUNT,
  gcc_loss.segment1||'.'||gcc_loss.segment2||'.'||gcc_loss.segment3||'.'||gcc_loss.segment4||'.'||gcc_loss.segment5||'.'||gcc_loss.segment6||'.'||gcc_loss.segment7||'.'||gcc_loss.segment8||'.'||gcc_loss.segment9 LOSS_ACCOUNT,
  gcc_cashclear.segment1||'.'||gcc_cashclear.segment2||'.'||gcc_cashclear.segment3||'.'||gcc_cashclear.segment4||'.'||gcc_cashclear.segment5||'.'||gcc_cashclear.segment6||'.'||gcc_cashclear.segment7||'.'||gcc_cashclear.segment8||'.'||gcc_cashclear.segment9 CASH_CLEARING_ACCOUNT,
  gcc_bank.segment1||'.'||gcc_bank.segment2||'.'||gcc_bank.segment3||'.'||gcc_bank.segment4||'.'||gcc_bank.segment5||'.'||gcc_bank.segment6||'.'||gcc_bank.segment7||'.'||gcc_bank.segment8||'.'||gcc_bank.segment9 BANK_CHARGES_ACCOUNT
FROM ce_bank_accounts cba,
ce_bank_branches_v cbbv,
gl_code_combinations gcc_gain,
gl_code_combinations gcc_loss,
gl_code_combinations gcc_cashclear,
gl_code_combinations gcc_bank,
gl_code_combinations gcc_asset
where cba.bank_branch_id=cbbv.branch_party_id
and cba.bank_id=cbbv.bank_party_id
and cba.Gain_code_combination_id=gcc_gain.code_combination_id(+)
and cba.loss_code_combination_id=gcc_loss.code_combination_id(+)
and cba.cash_clearing_ccid=gcc_cashclear.code_combination_id(+)
and cba.bank_charges_ccid=gcc_bank.code_combination_id(+)
and cba.asset_code_combination_id=gcc_asset.code_combination_id(+);

Read More

Request Set of a Concurrent Program

Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

Read More

Programs in a Request Set

Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

Read More

Query to find ORG_ID attached to a responsibility

SELECT * --fpov.profile_option_value
             FROM applsys.fnd_profile_option_values fpov
                , applsys.fnd_profile_options fpo
                , applsys.fnd_profile_options_tl fpot
                , applsys.fnd_responsibility_tl fr
            WHERE 1 = 1
              AND fpo.profile_option_name = fpot.profile_option_name
              AND fpo.profile_option_id = fpov.profile_option_id
              AND fr.responsibility_id(+) = fpov.level_value
             -- AND fr.responsibility_id = :$PROFILES$.RESP_ID
              AND fpot.profile_option_name = 'ORG_ID'
              and fpov.profile_option_value='11'
              and fr.responsibility_name like '%Payables%Super%User%';

Read More

Examples of XMLCONCAT in PLSQL

SET serveroutput ON size 300000;
DECLARE
  LV_PAYMENT_TYPE_XML XMLTYPE;
  l_string VARCHAR2(1000);
BEGIN
  SELECT XMLCONCAT(xmlelement("C_EXCHANGE_RATE",'1.2'), xmlelement("C_EXCHANGE_RATE_TYPE",'user'))
  INTO LV_PAYMENT_TYPE_XML
  FROM DUAL;
  l_string:= LV_PAYMENT_TYPE_XML.getStringVal();
  DBMS_OUTPUT.PUT_LINE(l_string) ;
  SELECT XMLCONCAT(LV_PAYMENT_TYPE_XML,xmlelement("C_EXCHANGE",'1.2'))
  INTO LV_PAYMENT_TYPE_XML
  FROM DUAL;
  l_string:= LV_PAYMENT_TYPE_XML.getStringVal();
  DBMS_OUTPUT.PUT_LINE(l_string) ;
END;
    

Read More

How To Calculate Tax as Inclusive on an Invoice Oracle EBTax R12

Tax as Inclusive on an Invoice Oracle EBTax R12

The tax inclusive handling setting at the tax rate level takes precedence over all other tax inclusive handling settings

This is documented in Note:1082008.1 How To Calculate Tax as Inclusive on an Invoice?

Solution:
(1) If the customer needs inclusive tax line irrespective of supplier set up.
Verify the set up at Tax Rates level (Tax Manager>Tax Configuration>Tax Rates>Rate Details)
Allow Tax Inclusion field should be set up for Standard Inclusive Handling - The price on the transaction line is inclusive of tax.
The tax inclusive handling setting at the tax rate level takes precedence over all other tax inclusive handling settings.

(2) If the customer needs inclusive tax line for a specific supplier.
Verify the set up at Party Tax Profile level (Tax Manager>Parties>Party Tax Profile>Third Party Site)
Verify the check the Set Invoice Values as Tax Inclusive option
If this party intends to send or receive invoices with invoice line amounts inclusive of tax, check the Set Invoice Values as Tax Inclusive box. This option overrides the tax inclusive handling setting at the tax level, but not at the tax rate leve

Read More

Oracle Workflow Important Tables

Workflow Tables

--Two classification of tables

--Definition and Setup

--if we have item key it is instance specific

SELECT * FROM wf_item_types WHERE NAME = 'XXKSRBWF';

SELECT * FROM wf_items;  -- will have item type and item key (instance table)

select * from wf_item_attributes;

SELECT * FROM wf_item_attribute_values;

SELECT * FROM wf_Activities;

SELECT * FROM wf_notifications;  --message type is same as item type in this table

SELECT * FROM wf_Resources;

select * from wf_process_Activities; --process_item_type is same as item_type

Read More