R12 : Bank & Trading Community Architecture(TCA)

Three key CE tables now as:

• CE_BANK_ACCOUNTS for bank accounts
• CE_BANK_ACCT_USES_ALL for account uses by Operating Units & Legal Entities
• CE_GL_ACCOUNTS_CCID for bank account use accounting data

TCA and Bank

The TCA party model is being used to model banks and bank branches as parties with the associated attributes of Relationships, Address, Contact and Locations. The TCA tables used by Cash Management for modelling Banks and Bank Branches are listed below:

1. HZ_PARTIES
2. HZ_RELATIONSHIPS
3. HZ_RELATIONSHIP_TYPES
4. HZ_ORG_CONTACTS
5. HZ_ORG_CONTACT_ROLES
6. HZ_CONTACT_POINTS
7. HZ_PARTY_SITES
8. HZ_LOCATIONS
9. HZ_ORGANIZATION_PROFILES

The HZ_ORGANIZATION_PROFILES table stores additional attributes of banks and bank branches along with the history of changes made to Banks and Bank Branches. The contact person at the bank, bank branch and bank account is defined as a party in HZ_PARTIES, while the contact details will be stored in HZ_CONTACT_POINTS (stores contact methods), HZ_ORG_CONTACTS (stores the contact’s title) and HZ_ORG_CONTACT_ROLES (stores the contact’s purpose or role). The address details of Banks and Bank Branches will be in HZ_LOCATIONS (stores addresses) and HZ_PARTY_SITES (stores party sites).

The new table CE_BANK_ACCOUNT stores bank account attributes while the CE_BANK_ACCT_USES_ALL table stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).

The accounting data pertaining to the bank account use will be stored in the CE_GL_ACCOUNTS_CCID table.

All of the bank, branch and bank account related attributes in AP_BANK_BRANCHES and AP_BANK_ACCOUNTS_ALL tables will be upgraded to HZ_PARTIES and the new tables in Cash Management.

Within TCA model, here is various attributes how they fits inside the model.

Descriptive flexfields SQL to get DFF Details

Descriptive flex fields have always been used on Standard Oracle forms to provide the business additional fields for information representation.

From the front end information of the DFF can be seen from the Application Developer Responsibility

Flexfields --> Descriptive --> Register

For a technical developer if needed the DFF information can be checked from the Data base too via an SQL.

The below SQL gives the DFF details defined for AR Invoices :
Line transaction flexfield

SELECT fdf.application_id,
       fdf.APPLICATION_TABLE_NAME,
       fdf.TITLE,
       fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE,
       fdfc.DESCRIPTION    ,
       col.APPLICATION_COLUMN_NAME,
       col.end_user_column_name,
       col.ENABLED_FLAG,
       col.FLEX_VALUE_SET_ID
  FROM FND_DESCRIPTIVE_FLEXS_VL fdf,
       FND_DESCR_FLEX_CONTEXTS_VL fdfc,
       FND_DESCR_FLEX_COL_USAGE_VL col
 WHERE     fdfc.DESCRIPTIVE_FLEXFIELD_NAME = fdf.DESCRIPTIVE_FLEXFIELD_NAME
       AND (fdf.TITLE) LIKE '%Orga%para%'
       AND fdf.application_id = col.application_id
       AND col.DESCRIPTIVE_FLEXFIELD_NAME = fdf.DESCRIPTIVE_FLEXFIELD_NAME
       AND fdfc.descriptive_flex_context_code = col.descriptive_flex_context_code;

Changing the Title we can get details of Order flexfield Structures too

Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

How to Get GL Code Combination Description

Script 1:(Works in Both 11i & R12)

SELECT gcc.segment1
  ||'.'
  ||gcc.segment2
  ||'.'
  ||gcc.segment3 ACCOUNT,
  A1.DESCRIPTION
  ||'-'
  ||A2.DESCRIPTION
  ||'-'
  || A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
  fnd_flex_values_vl A2,
  fnd_flex_values_vl A3,
  gl_code_combinations gcc
WHERE a1.flex_value       =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
  (SELECT FLEX_VALUE_SET_ID
  FROM fnd_id_flex_segments
  WHERE application_id       = 101
  AND id_flex_code           = 'GL#'
  AND enabled_flag           = 'Y'
  AND application_column_name='SEGMENT1'
  )
AND a2.flex_value         =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
  (SELECT FLEX_VALUE_SET_ID
  FROM fnd_id_flex_segments
  WHERE application_id       = 101
  AND id_flex_code           = 'GL#'
  AND enabled_flag           = 'Y'
  AND application_column_name='SEGMENT2'
  )
AND a3.flex_value         =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
  (SELECT FLEX_VALUE_SET_ID
  FROM fnd_id_flex_segments
  WHERE application_id       = 101
  AND id_flex_code           = 'GL#'
  AND enabled_flag           = 'Y'
  AND application_column_name='SEGMENT3'

  );

Script 2: ( Applicable in R12)

SELECT gcc.CONCATENATED_SEGMENTS,
  gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description

FROM gl_code_combinations_kfv gcc;

Table Suffixes in Oracle Apps

Table Suffixes:

You have lot of tables ending with different suffixes in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffixes and what they mean.

_ALL: Table ending with _ALL means it stores data related to multiple organizations. Such tables definitely will have a column called org_id which specifies to which org the record belongs to.

_TL: Table ending with _TL is a Translation Table. Such tables have a column called language

_B: Tables ending _B are the base tables

_VL: Tables ending with _VL are Views built upon multi language or translation tables. In other words such views combine base table data with translation table data. If you wish to see the data related to a one particular language, you should set the language using USERENV(‘LANG’).

_V: Tables ending with _V are views

_S: Tables ending with _S are Sequences

_A, _Avn, _ACn: Tables ending with _A, _Avn, _ACn are audit tables.

_F: Tables in which data is tracked with two date columns (Effective_start_Date, Effective_end_date). These tables are primarily used in HRMS and payroll modules.

Concept of Multi-Org

What is Multi-Org?

 

Q. What is Multi Org Architecture?

A. the Multiorg Architecture is meant to allow multiple companies
or subsidiaries to store their records within a single database.
The multiple Organization architecture allows this by partitioning data
through views in the APPS schema. Multiorg also allows you to maintain
multiple sets of books. Implementation of Multiorg generally includes
defining more than one Business Group.

Q. How can I know if I am using Multi Org?
A. Run the SQL statement:
select multi_org_flag
from fnd_product_groups;
The result 'Y' means your database is setup for Multiorg.

Q. What are the relationships I can define in a multi org environment?
A. You define the relationships among inventory organizations,
operating units, legal entities, Business Groups, and sets of books to
create a multilevel company structure:
. Business Groups separate major segments of a business. Each can have
its own set of books. Each Group will also have a structure of other
organizations classifications assigned to it.
· Legal Entities post to a Set of Books
· Operating Units are part of a Legal Entity
· Inventory Organizations are part of an Operating Unit
· Inventory Organizations define and maintain items used by other
manufacturing modules (Order Entry, Purchasing, MRP, etc.). They also
collect and pass data to the Financials modules.

Q. I want to Setup Additional Organizations, do I have to setup Multiorg?
A. No, you do not need to setup multiorg. You do not have to be
multi-org to have multiple organizations only if you intend to have multiple
sets of books.

Q. Is there any documentation on how to setup an organization?
A. · Refer to "Oracle Manufacturing Implementation Manual" part#A50765.
· Refer to "Multiple Organizations in Oracle Applications"
part#A58478 for Release 11 and part#50771 for Release 10.
· Also refer to note 76440.1 for Organization setup steps.

Q. When my organization hierarchy changes, can I move an organization from
one set of books or legal entity to another?
A. No, you should not try to move an organization from one set of books
or legal entity to another because your data may not be valid for the
new set of books or legal entity. Instead, you should disable the old
organization and create a new organization for the appropriate set of
books or legal entity. The new organization will contain your new data,
and the disabled organization will act as an "old" or "history" entity
that stores past transactions.

Q. How can I setup a child org to be its own costing organization?
A. Steps:
· Change the attribute control for "default category set" to organization level
· Change the attribute control for "costing enabled" to organization level
· Change the attribute control for "inventory asset value" to organization control
Then you can make a child organization it's own costing organization by
entering the organization name in the column labelled "Costing Organization"

Q. How can I define organization restriction?
A. Use the Organization Access form (INVSDORA) to restrict the list of organizations
displayed for each responsibility level.
Refer to Oracle Inventory User's Guide for steps and important notes.
*WARNING* If you populate any rows in this form, you MUST populate a row for
EACH responsibility that you wish to have access to that Organization.

Q. What responsibility do I need to use to setup organization?
A. Use the General Ledger responsibility to define the Set of Books
Use the Inventory Responsibility to define Organizations and other related
information such as Inventory Key Flexfields, Locations, Workday calendar,
other Organization Classifications, and other inventory information.

Q. What are the main profile options relating to Organization setup and
what are they used for?
A. · HR:User Type = HR User
This is necessary to allow the Inventory responsibility to complete
the organization setup. Setting the profile to a value of 'User' as
opposed to 'Payroll & User' will restrict the Inventory user from
accessing any Payroll information if Oracle Payroll is installed.
· HR: Business Group = {the users Business Group name}
This points the responsibility to the appropriate Business Group.
When multiple Business Groups are defined, you must associate
each responsibility with one and only one Business Group.
A responsibility cannot see organization data from more than
one Business Group.
· MO: Operating Unit = {the users Operating Unit name}
Used primarily in a Multiorg environment.
This points the responsibility to the appropriate Operating Unit.
Set the site level to the desired default operating unit.
If there is more than 1 Operating Unit Defined,
this profile option must be set at the responsibility level
for each responsibility.