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

TCA Parties Table Drill Down

SELECT * FROM hz_parties WHERE party_name = 'Saikam INTL' -- party_id = 39266,party_number(registry_id)= 1849,party_type = organization

SELECT * FROM hz_cust_accounts WHERE party_id = 563212 -- cust_account_id=10042,account_number=1180

SELECT * FROM hz_cust_acct_sites_all WHERE cust_account_id = 147077 -- cust_acct_site_id=1247,party_site_id=30119

SELECT * FROM hz_cust_site_uses_all WHERE cust_acct_site_id = 117353 --site_use_id=1266,location=1267,

SELECT * FROM hz_party_sites WHERE party_id = 563212  --party_site_id = 30119,location_id=1023,party_site_number=1598

SELECT * FROM hz_locations WHERE location_id =127754

SELECT * FROM hz_party_site_uses WHERE party_site_id = 30119 --party_site_use_id = 29029

SELECT * FROM hz_customer_profiles WHERE cust_account_id = 10042 --cust_account_profile_id=11043,party_id

SELECT * FROM hz_organization_profiles WHERE party_id=39266 --organization_profile_id=30101,

SELECT * FROM hz_person_profiles WHERE party_id=39266

Read More

Query to find strategies of a Customer Oracle R12 AR

Strategies of a Customer

SELECT DISTINCT(ist.cust_account_id),
  ist.status_code,
  ist.strategy_id,
  ist.strategy_template_id,
  ist.creation_date,
  hca.account_number,
  hca.account_name,
  istt.strategy_name
FROM iex_strategies ist,
  hz_cust_accounts_all hca,
  iex_strategy_templates_tl istt,
  (SELECT MAX(creation_date) max_date,
    cust_account_id
  FROM iex_strategies
  GROUP BY cust_account_id
  ) da
WHERE hca.cust_account_id    = ist.cust_account_id
AND ist.strategy_template_id = istt.strategy_temp_id
AND ist.object_type          = 'ACCOUNT'
AND ist.status_code         <> 'CANCELLED' --= 'OPEN'
AND TRUNC(ist.creation_date) =TRUNC(da.max_date)
AND ist.cust_account_id      =da.cust_account_id
AND hca.cust_account_id      =da.cust_account_id
AND hca.account_number      IN ('GL-21618645');

Read More

Tables used in Defining Request Sets Oracle R12

REQUEST SETS:
• FND_REQUEST_SETS
• FND_REQUEST_SETS_TL


REQUEST SET – STAGES:
• FND_REQUEST_SET_STAGES
• FND_REQUEST_SET_STAGES_TL


REQUEST SET – STAGES-STAGE REQUESTS:
• FND_REQUEST_SET_PROGRAMS


REQUEST SET – STAGES-STAGE REQUESTS-REQUEST PARAMETERS:
• FND_DESCR_FLEX_COL_USAGE_TL
• FND_DESCR_FLEX_COLUMN_USAGES

Read More

Query to find responsibilities attached to a user in Oracle R12

SELECT * FROM FND_USER WHERE user_name LIKE 'DAVIDEVANS';

SELECT *
  FROM FND_USER_RESP_GROUPS
 WHERE user_id=1180; --user_id is the link between the FND_USER and the FIND_USER_RESP_GROUPS

SELECT *
  FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_id=50621; --responsibility_id is the link between the FND_RESPONSIBILITY_VL and FND_USER_RESP_GROUPS

-- The query which displays the user name and his responsibility_name will be

select fu.user_id,
       fu.user_name,
       furg.RESPONSIBILITY_APPLICATION_ID,
       frv.RESPONSIBILITY_NAME
  from FND_USER fu, FND_USER_RESP_GROUPS furg, FND_RESPONSIBILITY_VL frv
 where fu.user_id = furg.user_id
   and furg.responsibility_id = frv.responsibility_id
   and fu.user_name like 'DAVIDEVANS'

Read More

Concurrent Program to Executable Name Drill Down Oracle R12

SELECT fcpv.concurrent_program_name,
  fcpv.user_concurrent_program_name,
  fefv.executable_name,
  fefv.execution_file_name,
  fefv.execution_file_path
FROM fnd_concurrent_programs_vl fcpv,
  fnd_executables_form_v fefv
WHERE fcpv.executable_id = fefv.executable_id
AND fcpv.user_concurrent_program_name LIKE '%File%Watcher%'

Read More

AR Transactions to GL Drill Down

select * from RA_CUSTOMER_TRX_ALL  where trx_number = &trx_num --pk customer_trx_id ; trx_number=100771
select * from RA_CUST_TRX_LINE_GL_DIST_ALL where customer_trx_id=1557 --pk cust_trx_line_gl_dist_id
select * from XLA_DISTRIBUTION_LINKS where source_distribution_id_num_1 in (1471,1331) --(373185,373186,373191) --pk application_id
select * from XLA_AE_LINES where ae_header_id=(14807) -- 193233 --pk ae_header_id
select * from GL_CODE_COMBINATIONS where code_combination_id in (98480,98482,98483)
select * from GL_LEDGERS where ledger_id=2023 --xla_ae_line links with this table using ledger_id

select distinct t.* from (
select gl.name Ledger_Name,
       (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
       gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6) Account,
       rctlgda.gl_date Gl_Date,
       xal.accounting_class_code class,
       xal.accounted_dr,
       xal.accounted_cr
  from GL_LEDGERS                   gl,
       GL_CODE_COMBINATIONS         gcc,
       XLA_AE_LINES                 xal,
       XLA_DISTRIBUTION_LINKS       xdl,
       RA_CUST_TRX_LINE_GL_DIST_ALL rctlgda,
       RA_CUSTOMER_TRX_ALL          rcta
 where gl.ledger_id = xal.ledger_id
   and gcc.code_combination_id = xal.code_combination_id
   and xal.ae_header_id = xdl.ae_header_id
   and xdl.source_distribution_id_num_1 = rctlgda.cust_trx_line_gl_dist_id
   and rctlgda.customer_trx_id = rcta.customer_trx_id
   and rcta.trx_number = &trx_number
   and xdl.source_distribution_type ='RA_CUST_TRX_LINE_GL_DIST_ALL' ) t

Read More

AR Receipts to Transaction Drill Down

 select acra.receipt_number,
           acra.amount,
           rcta.trx_number,
           acra.receipt_date,
           apsa.amount_applied
     from ar_cash_receipts_all acra,
          ar_receivable_applications_all araa,
          ra_customer_trx_all rcta,
          ar_payment_schedules_all apsa
    where acra.receipt_number = 'BACS170211MORETON'
      and  apsa.customer_trx_id=rcta.customer_trx_id
      and araa.cash_receipt_id = acra.cash_Receipt_id
      and rcta.customer_trx_id=araa.applied_customer_trx_id;

Read More

Open Requisition of a User

Open Requisition of  a User

alter session set current_schema=apps;

SELECT DISTINCT pha1.segment1 PO_NUMBER
                ,prha1.segment1 REQUISITION_NUMBER
,aps.vendor_name SUPPLIER_NAME
, pha1.currency_code AMOUNT_CURRENCY
, to_char(trunc(pha1.CREATION_DATE),'DD-MON-YYYY') CREATION_DATE
FROM apps.po_headers_all PHA1
     , apps.po_distributions_all PDA1
     , apps.po_req_distributions_all PRDA1
     , apps.po_requisition_lines_all PRLA1
     , apps.po_requisition_headers_all PRHA1
,ap_suppliers aps
WHERE pha1.po_header_id = pda1.po_header_id
AND ( ( pda1.req_header_reference_num = prha1.segment1
AND pda1.req_line_reference_num = prla1.line_num
)
OR(pda1.req_distribution_id=prda1.distribution_id)
)
AND prda1.requisition_line_id = prla1.requisition_line_id
AND prla1.requisition_header_id = prha1.requisition_header_id
AND pha1.org_id=prha1.org_id
and aps.vendor_id=pha1.vendor_id
and prha1.wf_item_key in
       (
     
        select item_key
          from wf_notifications
         where upper(trim(recipient_role)) like upper('%ashleigh%')
           and trunc(sysdate) - trunc(begin_date) < 30
           /*and status <> 'OPEN'*/
           and message_type = 'REQAPPRV')
and prha1.created_by = 4852

Read More

Join GL tables with XLA (SubLedger Accounting) tables

Foreign Keys

GL_JE_BATCHES (je_batch_id)                              => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                            => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)             => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)                   => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)                    => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id)                        => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)


The source_id_int_1 column of xla.xla_transaction_entities stores the primary_id value for the transactions. You can join the xla.xla_transaction_entities table with the corresponding transactions table for obtaining additional information of the transaction. For e.g you join the xla.xla_transaction_entities table with ra_customer_trx_all for obtaining receivables transactions information or with mtl_material_transactions table for obtaining material transactions information.
The entity_id mappings can be obtained from the XLA_ENTITY_ID_MAPPINGS table

Read More

Few Examples on Dynamic SQL

Dynamic SQL

------------------------------------------------------------------
DECLARE
v_table_name varchar2(100) := 'test_dept';
v_stmt varchar2(1000);
BEGIN
v_stmt := 'insert into '||v_table_name||' values(50,''HR'')';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
END;

-------------------------------------------------------------------
DECLARE
type sob_record is record
(
name gl_sets_of_books.NAME%type,
currency_code gl_sets_of_books.CURRENCY_CODE%type,
set_of_books gl_sets_of_books.SET_OF_BOOKS_ID%type
);
v_stmt varchar2(1000);
curr sys_refcursor;
row_rec sob_record;
BEGIN
v_stmt := 'select name,currency_code,set_of_books_id from gl_sets_of_books where set_of_books_id=:input';
open curr for v_stmt using 2023;
loop
fetch curr into row_rec;
exit when curr%notfound;
dbms_output.put_line(row_rec.currency_code || '    '||row_rec.name);
end loop;
END;


-------------------------------------------------------------------------------

DECLARE
type status_record is record
(
cid xx_account_status_swap.customer_number%type,
acc_status xx_account_status_swap.new_account_status%type
);
type status_table is table of status_record;

v_stmt varchar2(1000);
curr sys_refcursor;
status_coll status_table;
BEGIN
v_stmt := 'select customer_number,new_account_status from xx_account_status_swap where success_status=:input';

open curr for v_stmt using 'P';
fetch curr bulk collect into status_coll;

for i in 1..status_coll.count loop
dbms_output.put_line(status_coll(i).cid||'   '||status_coll(i).acc_status);
end loop;

dbms_output.put_line('----------------------------------------------');
dbms_output.put_line(status_coll.count);
dbms_output.put_line('----------------------------------------------');
END;

--------------------------------------------------------------------------------------

Read More

Convert CSV to Rows in Oracle

Query

SELECT substr(csv,
              instr(csv,
                    ',',
                    1,
                    lev) + 1,
              instr(csv,
                    ',',
                   1,
                    lev + 1) - instr(csv,
                                    ',',
                                    1,
                                    lev) - 1)
  FROM (SELECT ',' || 'a,b,c,d' || ',' csv
          FROM dual),
       (SELECT LEVEL lev
          FROM dual
        CONNECT BY LEVEL <= 100)
 WHERE lev <= length(csv) - length(REPLACE(csv,
                                           ',')) - 1;

Read More

Drill Down from AR Cash Receipts to GL Ledger R12

Query:

select distinct t.* from
(select gl.name Ledger_Name,
       (gcc.segment1||'-'||
       gcc.segment2||'-'||
       gcc.segment3||'-'||
       gcc.segment4||'-'||
       gcc.segment5||'-'||
       gcc.segment6)Account,
       araa.gl_date Gl_Date,
       xal.accounting_class_code Class,
       xal.accounted_dr,
       xal.accounted_cr
from GL_LEDGERS gl,
     XLA_DISTRIBUTION_LINKS  xdl,
     XLA_AE_LINES            xal,
     gl_code_combinations    gcc,
     ar_cash_receipts_all    acra,
     ar_payment_schedules_all apsa,
     AR_RECEIVABLE_APPLICATIONS_ALL araa,
     AR_DISTRIBUTIONS_ALL           ada
where xal.ledger_id = gl.ledger_id
and      acra.cash_receipt_id = apsa.cash_receipt_id
and      apsa.payment_schedule_id = araa.payment_schedule_id
and      araa.payment_schedule_id = apsa.payment_schedule_id
and      ada.source_id = araa.receivable_application_id -----------
--and      ada.source_type = 'CASH'
--and      ada.code_combination_id = gcc.code_combination_id
and      ada.third_party_id = acra.pay_from_customer
and      ada.third_party_sub_id = acra.customer_site_use_id
and      xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =  ada.line_id
and      ada.source_table = 'RA'
and      xdl.event_id = araa.event_id
--and      xdl.event_class_code like 'RECEIPT' ---------
--and      xdl.event_type_code like 'RECEIPT_ALL'------------
and      xdl.source_distribution_type like 'AR_DISTRIBUTIONS_ALL'
--and      xdl.rounding_class_code = 'RECEIVABLE'
--and      xdl.application_id = 222
and      xal.ae_header_id = xdl.ae_header_id
and      xal.code_combination_id = gcc.code_combination_id
and      acra.receipt_number = &Recipt_Number ) t


select * from ar_distributions_all;

select * from XLA_DISTRIBUTION_LINKS where source_distribution_type LIKE 'AR_DISTRIBUTIONS_ALL';

Read More