Ledger, OU and Legal Entity (1)

User can select legal entities in AP invoice and AR transaction through “Customer Tax Payer ID” and “Legal Entity” field. The list of value of these two fields looks from the view xle_le_ou_ledger_v through operating unit, in which the relationship between ledger, OU and legal entity are described. The three entities are associated by leger configuration (ledger&LE) and organization definition (OU&ledger).

—————————————————————————————-

CREATE OR REPLACE VIEW APPS.XLE_LE_OU_LEDGER_V AS
SELECT O2.ORGANIZATION_ID OPERATING_UNIT_ID,
       lg.LEDGER_ID ,
       lg.NAME LEDGER_NAME ,
       lg.SHORT_NAME LEDGER_SHORT_NAME ,
       cfgDet.OBJECT_ID LEGAL_ENTITY_ID ,
       xlep.name legal_entity_name,
       xlep.legal_entity_identifier,
       xlep.activity_code,
       xlep.sub_activity_code,
       xlep.type_of_company,
       xlep.effective_from le_effective_from,
       xlep.effective_to le_effective_to,
       reg.registration_number,
       hrl.address_line_1,
       hrl.address_line_2,
       hrl.address_line_3,
       hrl.region_1,
       hrl.region_2,
       hrl.region_3,
       hrl.town_or_city,
       hrl.postal_code,
       hrl.country
       FROM GL_LEDGERS primaryLg ,
       GL_LEDGERS lg ,
       GL_LEDGER_RELATIONSHIPS rs ,
       GL_LEDGER_CONFIGURATIONS cfg ,
       GL_LEDGER_CONFIG_DETAILS cfgDet,
       XLE_ENTITY_PROFILES xlep,
       XLE_REGISTRATIONS reg,
       HR_LOCATIONS_ALL hrl,
       HR_ORGANIZATION_INFORMATION O2,
       HR_ORGANIZATION_INFORMATION O3
       WHERE rs.application_id = 101
         AND ( ( rs.target_ledger_category_code = ‘SECONDARY’ AND rs.relationship_type_code <> ‘NONE’ )
           OR ( rs.target_ledger_category_code = ‘PRIMARY’ AND rs.relationship_type_code = ‘NONE’ )
            OR ( rs.target_ledger_category_code = ‘ALC’ AND rs.relationship_type_code IN ( ‘JOURNAL’, ‘SUBLEDGER’) ) )
         AND lg.ledger_id = rs.target_ledger_id
         AND lg.ledger_category_code = rs.target_ledger_category_code
         AND primaryLg.ledger_id = rs.primary_ledger_id
         AND primaryLg.ledger_category_code = ‘PRIMARY’
         AND cfg.configuration_id = primaryLg.configuration_id
         AND cfgDet.configuration_id ( + ) = cfg.configuration_id
         AND cfgDet.object_type_code ( + ) = ‘LEGAL_ENTITY’
    
AND cfgDet.object_id = xlep.legal_entity_id
     AND xlep.legal_entity_id = reg.source_id
     AND reg.source_table = ‘XLE_ENTITY_PROFILES’
     AND reg.identifying_flag = ‘Y’
     AND reg.location_id = hrl.location_id
     AND O3.ORG_INFORMATION3 = to_char(lg.LEDGER_ID)
     AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
         AND O2.ORG_INFORMATION_CONTEXT = ‘CLASS’
         AND O3.ORG_INFORMATION_CONTEXT = ‘Operating Unit Information’
         AND O2.ORG_INFORMATION1 = ‘OPERATING_UNIT’
         AND O2.ORG_INFORMATION2 = ‘Y’
    AND xlep.transacting_entity_flag = ‘Y’;

Differences between HR:Security Profile & MO:Security Profile

HR:Security Profile and MO:Security Profile are used for MOAC (Multi-Org-Access-Control).
HR:Security Profile is used to restrict the data in Human Resources according to the Business Group or whatever criteria you define in this security profile. Thus, for HR, it uses this secuity profile in its data exposure to the user.
MO:Security Profile acts the same but is used for Financials and Manufacturing applications. It restricts the access (site level or can be set at responsibility level) to certain operating unit (or whatever criteria defined in the security profile).
If there is no value at MO:Security Profile, then Financials and Manufacturing use the HR:Security Profile option for their data exposure to users.

Multi-Org features in Oracle EBS 11i and R12

Generally, Oracle 11i fulfills multi-org feature by the views in apps schema; while Oracle R12 fulfills the feature by Virtual Private Database feature.

In 11i,  the base table are created by product schema, e.g. oe_order_headers_all in ont db schema. In apps schema, there’s a view called oe_order_headers, which has a where clause like “org_id = SUBSTRB (USERENV (‘CLIENT_INFO’), 1, 10)” . So every user accessed application has his own userenv, then every user can access his own org’s data.

In R12, the base table are also created by product schema, e.g. oe_order_haders_all in ont schema. In apps schema, there’s a Synonym oe_order_headers with VPD feature to fulfill the multi-org feature.

In 11i, we set MO: Operating Unit profile on the responsibility level, then all of user logged from this responsibility has this OU(org_id) client info, so this user can only access this operating unit data from this responsibility.

In R12, we set up a security profile, which can include several operating units, then we assign this security profile to the profile option “MO: Security Profile” on the responsibility level.  The user logged from this responsibility can access all of OUs’ data  in the security profile.

Adding a New Operating Unit

From:  http://www.oracleug.com

The following steps are required to add new operating units to your organizational structure:
1.1 Revise the Organization Structure.
1.2 Define Sets of Books (optional).
1.3 Define Locations.
1.4. Define Organizations and Relationships.

2.1 Define Responsibilities.
2.2 Set Profile Options for Each Responsibility linked to the new Operating Unit.
1.1. HR: Business Group
1.2  HR: Security Option
1.3: HR: User Type (FOR accessing HRMS functions)
1.4  HR: Cross Business Group
2.1. GL: Set of Books(11i)
2.1  GL:%Ledger%  (R12)
2.3  GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.
3.1. MO: Operating Unit
3.2. MO: Security Profile (R12)
3.3. MO: Default Operating Unit
4.1 Tax: Allow Override of Tax Code
4.2 Tax: Invoice Freight as Revenue
4.3 Tax: Inventory Item for Freight
5.1 Sequential Numbering
5.2 INV: Intercompany Currency Conversion
2.3 Run the Replicate Seed Data concurrent program.

3.1 Define Inventory Organization Security.
3.2 Define Intercompany Relations (optional).
3.3 Implement the Application Products.

4.1 Secure Balancing Segment Values (optional).
4.2 Run the Setup Validation Report (recommended).
4.3 Implement Document Sequencing (optional).
4.4 Set the top reporting level (optional).
4.5 Set up conflict domains (optional).

A SQL Script to Reconcile GL and Sub-ledger

A SQL Script to Reconcile GL and Sub-ledger: The script exact all of the journal lines for specific accounts, which is not imported from sub-ledger.

SELECT gla.*

FROM GL_JE_JOURNAL_LINES_V gla,

  GL_CODE_COMBINATIONS glc

WHERE gla.line_code_combination_id=glc.code_combination_id

AND glc.segment1                  ='xxx'

AND glc.segment4                  ='xxxxxxx'

AND gla.je_source <> 'Purchasing'

and gla.je_source <> 'Payables';

Error Message during ADI installation

We met an error message during ADI installation.

adicom30.ins(509):OS_ERROR while spwawning
C:\windows\system32\spawner.exe
C:\windows\system32\AUWERG32.exe /S /32

We just comment the 509th line in the file of adicom30.ins, then it is passed. Or you can download the  attached file, rename the file name to “adicom30.ins” and replace the one in your installation package if you also met this problem and have not other solution to resolve it.

New added account can’t be shown in the FSG report

One user called me that the new added account 1xxxxx can’t be shown in the balance sheet (FSG report) after she added it in the row set definition properly.

Generally it is because of security rule, but this time after checking in the system, the security rule has been release.

So why can’t the account be shown in Balance Sheet on this situation? I did some test and found below:
  • If the account has balance of “0”, it will be shown as “0” in the report;
  • If the account had never been used before the period of the runninig report, the whole line of this account won’t be shown in the report.

It may be because of FSG’s logic – extracting the required accounting entries firstly, then organizing the report.

Error 1004 from Oracle ADI (Application Desktop Integration)

After installing Oracle ADI (Application Desktop Integration),  we met the error 1004 during generating journal template.

In fact, it is not the error message from ADI, but Excel. You need to go to Tools > Options > Security, click the macro button, then check both two check-boxes.

Two Tips to Adjust Column Set Format in FSG Report

It is indeed a challenge to adjust the format of FSG column set, especially when there’re a lot of columns even more then 40.  Once you add a new column to it, the whole format usually in a big mass. Yesterday I found a new way and a simple way to adjust it. (Maybe we can’t call it as “Adjust” but “Re-Build”. )

1. Add the column to the column set definition.

2. Re-calculate the column position for all of the columns. Every column may be able to be set the width like “[500(the maximum width) – Left Margin(generally about 50) ]/the number of columns”.  For example, the position of the first column is 50, then the second is 60, the third is 70, …

3. Go to Build Column Set, build the format by “Default

4. Add the report header one by one.

Above way looks time-consuming, but it is faster than “exactly adjustment” in practice (certainly just in some cases).

For other issue, as you may have more than 20 columns, a warning message “APP-RG-09542” would be shown during building column header.  We have a workaround that un-checking “Display this Column” option for some columns to set others’ column header, then checking the option for these columns and un-checking the others’ to do the same thing.

Retained Earning Acccount

All of the P/L accounts will be transferred to retained earning account during year end closing by run a concurrent request. This is an important step at the year end, but how to secure it during the common days? Make it un-postable or any other ways?

We may set up “No” in the qualification field of “Allow Post” in flex field values. But it affects all of the SOBs, and if we would like to do any adjustment on this account we need manually open it every time.

FSG report across SOB

Most of us know we can use FSG report to perform the consolidation. But some of us ignore that, in fact, we can get a FSG report across SOB by specifying the SOB in column set up. It means we can select an SOB in one column and select another SOB in another column, then we can use calculate feature in the summary column to get the sum of the value across SOB. It is applied to use for some companies which have several SOB but the SOB’s structure are same or similar.