Block manual entries to control accounts

Objective:

The customization aims at blocking the use of control accounts via manual journal entries. This is in order to prevent posting to those accounts except from subledgers.

Approach:

In R12, a new control account restriction feature is introduced: Third Party Control Account in Segment Qualifiers.

General Ledger responsibility -> Setup -> Financials -> Flexfields -> Key -> Values

Find the right value set (XX_GL_ACCOUNT), go to tab: Values, Hierarchy, Qualifiers, give the right value to Segment Qualifiers which include: Allow Budgeting, Allow Posting, Account Type, Third Party Control Account, Reconcile. Assign ‘Yes’ to Third Party control Account.

Unfortunately, this restriction can only be applied to GL module. Other subledger modules have to be implemented via a standard DFF at the level of account itself.

If this restriction has exception, which means certain high level users can use control accounts to do some adjustment, a profile option at the responsibility level needs to be created in order to ignore the blocking control account logic.

DFF: Flexfield segment values:

System Administrator -> Application -> Flexfield -> Descriptive -> Segments

Application: Application Object Library

Title: Flexfield Segment values

Find the right Context Files Values which is XX_GL_ACCOUNT, define the control account segment.

Profile Creation:

Application Developer -> Profile

Name: XX_SUBLEDGER_DRIVEN

User Profile Name: Subledger Driven Control Account

Hierarchy Type: Server-Responsibility

SQL validation used for the Profile Option’s Listof Values:

SQL="select lookup_code, meaning "Subledger Driven"
into :profile_option_value, :visible_option_value
from fnd_lookups
where lookup_type = 'YES_NO'"
Column=""Subledger Driven"(*)"

Form Personalization:

The below describe the detail logics to apply for blocking using control accounts in different modules.

1) In GL module

In R12, an account can be marked as third party control account and the values are: C(ustomer), S(upplier), None, Yes.

To enable 3rd party control in R12, in the Segment Values form, change the qualifier in the Third Party Control Account to be either of Customer, Supplier, or Yes.

After that GL should NOT allow you to enter a manual journal against that account, but this needs to be implemented by form personalization by calling a checking GL control account function.

Form: GLXJEENT
Description of personalization: Exclude controlled accounts
Level: Function

Conditions
Trigger Event: WHEN-VALIDATE-RECORD
Trigger Object: LINES
Conditions:
         :HEADER.USER_JE_SOURCE_NAME in ('Manual', 'Manuel') and
         1 = XX_FIN_FORM_PERS.GL_Control_Account_Check(:LINES.CODE_COMBINATION_ID)
Processing Mode: Not in Enter-Query Mode

Actions
Type: Message
Message Type: Error
Message Text: “You cannot use a Controlled Account, please choose another Account.”

2) AR and other modules.

For subledgers like Receivables, Payables, the customization is realized by form personalization too, but with the different control account checking logic which is based on DFF segment and a profile option ‘CAE Subledger Driven’ at responsibility level. The personalization should be implemented the following rule depending on the flags set both the account level and at the responsibility level:

Account Level Flag	Responsibility Level Flag	Outcome
Yes	Yes	Is control account
Yes	No	Is not control account
No	Yes	Is not control account
No	No	Is not control account

Form: ARXTWMAI (Receivables - Transactions)

Description of personalization: Block controlled accounts
Level: Function
Conditions
Trigger Event: WHEN-VALIDATE-RECORD
Trigger Object: TACC_ACC_ASSGN
Conditions:
        /*:TACC_ACC_ASSGN.ACCOUNT_CLASS <> 'REC' and */
        1=XX_FIN_FORM_PERS.SL_Control_Account_Check(:TACC_ACC_ASSGN.CODE_COMBINATION_ID)
        and FND_PROFLE.VALUE('XXSUBLEDGERDRIVEN') = 'Y'
Processing Mode: Not in Enter-Query Mode

Actions
Type: Message
Message Type: Error
Message Text: “Controlled Account is blocked.”

The same logic applies to other subledger modules.

Package and functions.

create or replace PACKAGE BODY XX_FIN_FORM_PERS AS
function GL_Control_Account_Check (P_ACCOUNT IN NUMBER) RETURN NUMBER AS
RET_VALUE VARCHAR2(5);

BEGIN
SELECT substr(fv.COMPILED_VALUE_ATTRIBUTES, 7, 1)
INTO RET_VALUE
FROM GL_CODE_COMBINATIONS GLC
, FND_FLEX_VALUE_SETS fvset
, FND_FLEX_VALUES fv
, GL.GL_LEDGERS GLL
WHERE GLC.CODE_COMBINATION_ID = P_ACCOUNT
AND fvset.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID
AND fv.ENABLED_FLAG = 'Y'
AND fvset.FLEX_VALUE_SET_NAME = 'CAE_GL_ACCOUNT'
AND fv.FLEX_VALUE = GLC.SEGMENT3
AND GLL.CHART_OF_ACCOUNTS_ID = GLC.CHART_OF_ACCOUNTS_ID
AND rownum = 1;

IF RET_VALUE = 'Y' THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END GL_Control_Account_Check;

function SL_Control_Account_Check (P_ACCOUNT IN NUMBER) RETURN NUMBER AS
        RET_VALUE VARCHAR2(5);
BEGIN
SELECT fv.ATTRIBUTE1
INTO RET_VALUE
FROM GL_CODE_COMBINATIONS GLC
, FND_FLEX_VALUE_SETS fvset
, FND_FLEX_VALUES fv
, GL.GL_LEDGERS GLL
WHERE fvset.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID
AND fv.ENABLED_FLAG = 'Y'
AND fvset.FLEX_VALUE_SET_NAME = 'CAE_GL_ACCOUNT'
AND fv.FLEX_VALUE = GLC.SEGMENT3
AND GLL.CHART_OF_ACCOUNTS_ID = GLC.CHART_OF_ACCOUNTS_ID
AND GLL.LEDGER_ID NOT IN (SELECT GLCON.TO_LEDGER_ID
FROM GL_CONSOLIDATION GLCON)
AND GLC.ENABLED_FLAG = 'Y'
AND GLC.CODE_COMBINATION_ID = P_ACCOUNT
-- AND to_char(GSB.SET_OF_BOOKS_ID) = FND_PROFILE.VALUE('GL_LEDGER_ID')
AND rownum = 1;

IF RET_VALUE = 'Y' THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END SL_Control_Account_Check;

END XX_FIN_FORM_PERS;