Query for to get Tax Details In oracle apps R12

SELECT hou.organization_id            org_id,

       led.name                       ledger,

       hou.name                       operating_unit,

       --

       zxr.tax_regime_code            tax_regime_code,

       zxr.tax                        tax_code,

       zxr.tax_status_code            tax_status_code,

       zxr.tax_rate_code              tax_rate_code,

       zxr.tax_jurisdiction_code      tax_jurisdiction_code,

       --

       zxr.rate_type_code             rate_type_code,

       zxr.percentage_rate            percentage_rate,

       zxr.effective_from             rate_effective_from,

       zxr.effective_to               rate_effective_to,

       --

       acc.tax_account_ccid           tax_account_ccid,

       gcc.concatenated_segments      tax_account

  FROM

       zx_rates_vl                  zxr,

       zx_accounts                  acc,

       hr_operating_units           hou,

       gl_ledgers                   led,

       xxrl_gl_code_combinations_v  gcc

 WHERE

       1=1

   --

   -- AND zxr.tax_regime_code = 'UK VAT'

   -- AND zxr.tax_rate_code = 'UK_AR_DOM'

   --

   AND acc.tax_account_entity_code = 'RATES'

   AND zxr.active_flag = 'Y'

   AND TRUNC (SYSDATE) BETWEEN

          TRUNC (zxr.effective_from) AND

          NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)

   --

   AND led.ledger_id = hou.set_of_books_id

   AND gcc.code_combination_id = acc.tax_account_ccid

   AND hou.organization_id = acc.internal_organization_id

   AND acc.tax_account_entity_id = zxr.tax_rate_id

   --

   ;

Query for to get Standard Concurrent Programs with arguments and valuesets in oracle apps R12

SELECT FCPL.USER_CONCURRENT_PROGRAM_NAME USER_CONCURRENT_PROGRAM_NAME,
       FDFCUV.COLUMN_SEQ_NUM COLUMN_SEQ_NUM,
       FDFCUV.END_USER_COLUMN_NAME PARAMETER_NAME,
       FDFCUV.FORM_LEFT_PROMPT PROMPT, FDFCUV.ENABLED_FLAG ENABLED_FLAG,
       FDFCUV.REQUIRED_FLAG REQUIRED_FLAG,
       FDFCUV.DISPLAY_FLAG DISPLAY_FLAG,
       FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME
  FROM fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpl,
       fnd_descr_flex_col_usage_vl fdfcuv,
       fnd_flex_value_sets ffvs,
       fnd_lookup_values flv
 WHERE 1 = 1
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name =
                                       '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
   AND flv.lookup_code(+) = fdfcuv.default_type
   AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
   AND fcpl.LANGUAGE = USERENV ('LANG')
   AND flv.LANGUAGE(+) =
          USERENV
             ('LANG')
--AND    fcpl.user_concurrent_program_name LIKE 'XXENEUR%'
AND ffvs.flex_value_set_name NOT LIKE 'XX%'
And fcp.CONCURRENT_PROGRAM_NAME in(
'APXAAREP',
'APXCRRCR'
))

how to debug plsql blok

by using SQLERRM we can debug like below


EXCEPTION
                WHEN OTHERS THEN
                           FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error in XXATU_BLACKLINE_EXTRACT');
                           v_error := SUBSTR(SQLERRM,1,240);
                           FND_FILE.PUT_LINE (FND_FILE.LOG, v_error);

Query for to get Standard Customization objects list in Oracle apps R12

Declare
v_user_cp1 varchar2(200);
v_cp1   varchar2(200);
v_user_cp varchar2(200);
v_cp   varchar2(200);
v_cp_method varchar2(200);
Cursor Cur_name is
select distinct  cc.concurrent_program_name,tl.user_concurrent_program_name
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
and cc.concurrent_program_name not like 'XX%'
 And tl.LANGUAGE ='US';
cursor cur1 (cpname varchar2) is
    select distinct tl.user_concurrent_program_name, cc.concurrent_program_name,
    decode(cc.execution_method_code,
'P', 'Oracle Reports',
'I' ,'PL/SQL Stored Procedure'
) Execution_Method
    into v_user_cp,v_cp ,v_cp_method
    from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
    where tl.application_id = cc.application_id
    and tl.concurrent_program_id = cc.concurrent_program_id
    and cc.execution_method_code in ('P','I')
    And cc.concurrent_program_name like 'XX%'
    And tl.LANGUAGE ='US'
    And cc.concurrent_program_name <> cpname
    And REGEXP_LIKE  (cc.concurrent_program_name, cpname);
begin
Open Cur_name;
 LOOP
  Fetch Cur_name into v_user_cp1,v_cp1;
  exit when Cur_name%notfound;
    open cur1(v_user_cp1);
    loop
    fetch cur1 into v_user_cp,v_cp,v_cp_method;
    exit when cur1%notfound;
    dbms_output.put_line(v_cp||' | '||v_user_cp||' | '||v_user_cp1||' | '||v_cp1||' | '||v_cp_method);
    end loop;
    close cur1;
 END LOOP;
  Close Cur_name;
end;

It will tack to time to execute query 15 to 20 mints.

how to set environment variable in putty

log in on putty
enter . /sharedapps/<instance Name>/EBSapps.env
you can enter 'R' for run mode

Customers in TCA Architecture in Release 12

Customers in TCA Architecture in Release 12

Scope and Application

This note is intended for anyone who must  upgrade queries or customizations from 11i which refer to any of the following views or anyone who requires an understanding of the TCA architecture  to create new queries or customizations:

• RA_CUSTOMERS
• RA_ADDRESSES
• RA_SITE_USES
• RA_CONTACTS
• RA_CONTACT_ROLES

Projects Uptake of the TCA Architecture in Release 12

1. The TCA Architecture

The following sections describe the individual views that are being replaced by the TCA (HZ) tables along with a mapping of view columns to HZ table columns and the query conditions that should be used to retrieve them

2.1 RA_CUSTOMERS

• In the 11i architecture RA_CUSTOMERS is a synonym in the APPS schema which points to the view RA_HCUSTOMERS. 
• The source file for RA_HCUSTOMERS in 11i is archz.odf.
• The table below lists the corresponding HZ table and column for various columns in the current RA_CUSTOMERS view:

Column in RA_CUSTOMERS	Corresponding Table	Column
customer_name	hz_parties	substrb(party_name,1,50)
customer_id	hz_cust_accounts	cust_account_id
customer_number	hz_cust_accounts	account_number
status	hz_cust_accounts	status

• Source Tables:  HZ_PARTIES, HZ_CUST_ACCOUNTS
• Join Conditions:

HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id

2.2 RA_ADDRESSES

• In the 11i architecture RA_ADDRESSES is a multi-org striped view based on RA_ADDRESSES_ALL.  RA_ADDRESSES_ALL is a synonym for the view RA_ADDRESSES_MORG.
• The source file for view RA_ADDRESSES_MORG in 11i is archz.odf.
• The table below lists the corresponding HZ table and column for various columns in the current RA_ADDRESSES view:

Column in RA_ADDRESSES	Corresponding Table	Column
address_id	hz_cust_acct_sites_all	cust_acct_site_id
status	hz_cust_acct_sites_all	status
address1	hz_locations	address1
address2	hz_locations	address2
address3	hz_locations	address3
address4	hz_locations	address4
city	hz_locations	city
state	hz_locations	state
postal_code	hz_locations	postal_code
county	hz_locations	county
country	hz_locations	country
language	hz_locations	language

• Source Tables:  HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL
• Join Conditions:

HZ_CUST_ACCT_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id
HZ_LOCATIONS.location_id = HZ_PARTY_SITES.location_id

2.3 RA_SITE_USES

• In the 11i architecture RA_SITE_USES is a multi-org striped view based on RA_SITE_USES_ALL. RA_SITE_USES_ALL is a synonym for the view RA_SITE_USES_MORG.
• The source file for view RA_SITE_USES_MORG in 11i is archz.odf
•  The table below lists the corresponding HZ table and column for various columns in the current RA_SITE_USES view:

Column in RA_SITE_USES	Corresponding Table	Column
site_use_id	hz_cust_site_uses	site_use_id
site_use_code	hz_cust_site_uses	site_use_code
status	hz_cust_site_uses	status
address_id	hz_cust_site_uses	cust_acct_site_id

• Source Tables:  HZ_CUST_SITE_USES
• Join Conditions:  none

2.4 RA_CONTACTS

• In the 11i architecture RA_CONTACTS is a synonym for the view RA_HCONTACTS
• The source file for view RA_HCONTACTS in 11i is archz.odf
• The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACTS view:

Column in RA_CONTACTS	Corresponding Table	Column
contact_id	hz_cust_account_roles	cust_account_role_id
status	hz_cust_account_roles	status
customer_id	hz_cust_account_roles	cust_account_id
address_id	hz_cust_account_roles	cust_acct_site_id
first_name	hz_parties	substrb(person_first_name,1,40)
last_name	hz_parties	substrb(person_last_name,1,50)

• Source Tables: HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_PARTIES
• Join Conditions:

HZ_CUST_ACCOUNT_ROLES.party_id = HZ_PARTY_RELATIONSHIPS.party_id
HZ_CUST_ACCOUNT_ROLES.role_type = 'CONTACT'
HZ_PARTIES.party_id = HZ_PARTY_RELATIONSHIPS.subject_id

2.5 RA_CONTACT_ROLES

• In the 11i architecture RA_CONTACT_ROLES is a synonym for the view RA_HCONTACT_ROLES
• The source file for view RA_HCONTACT_ROLES in 11i is archz.odf
• The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACT_ROLES view:

Column in RA_CONTACT_ROLES	Corresponding Table	Column
contact_id	hz_role_responsibility	cust_account_role_id
usage_code	hz_role_responsibility	responsibility_type
primary_flag	hz_role_responsibility	primary_flag

• Source Tables: HZ_ROLE_RESPONSIBILITY
• Join Conditions:  none.

11i Table	R12 Change
ra_addresses_all	SELECT acct_site.cust_account_id customer_id,     acct_site.cust_acct_site_id address_id FROM hz_party_sites party_site,                  hz_loc_assignments loc_assign,                  hz_locations loc,                  hz_cust_acct_sites_all acct_site            WHERE acct_site.party_site_id = party_site.party_site_id              AND loc.location_id = party_site.location_id              AND loc.location_id = loc_assign.location_id              AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99)
ra_site_uses_all	SELECT site_use_id, LOCATION, attribute1  FROM hz_cust_site_uses_all
ra_customers	SELECT cust_acct.cust_account_id customer_id,                  SUBSTRB (party.party_name, 1, 50) customer_name,                  cust_acct.account_number customer_number             FROM hz_parties party, hz_cust_accounts cust_acct            WHERE cust_acct.party_id = party.party_id

RA_ADDRESSES_ALL is obsolete table in Release 11.5.10 and Release 12. You can get the customer address information from the following tables in Release 12.

HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITES
HZ_LOCATIONS

Here is the Linking information:

- HZ_CUST_ACCT_SITES_ALL & HZ_PARTY_SITES - Link column PARTY_SITE_ID
- HZ_PARTY_SITES & HZ_LOCATIONS - Link column is LOCATION_ID.

Please share Your Comments