Correct unbalanced subledger entries

Here  a practical application of the function xx_ccID.switch_CCID_segment  used to correct entries in the Inventory and WiP subledgers where Debit and Credit are posted to accounts with different balancing segments.

This problem generates this error when running the Create Accounting in Cost Management - SLA

Message : The Legal Entity segment value for the account code combination
() specified in line () is not assigned to the ledger ()
Please update the Legal Entity segment value assignments for the ledger in the Accounting
Configuration page or use another Legal Entity segment value.

Description of the fix:

The first cursor establishes list of transactions where the segments are mismatching.

The second cursor loops through each of those transactions and finds the entry where the balancing segment of the associated legal entity does not match the balancing segment of the entry, then corrects it using the xx_ccID.switch_CCID_segment function described in my previous post.

Optionally it moves the transaction to the  current accounting period to make sure the period is not closed.

This is a relatively simple script with 2 important assumptions:

• The entry with wrong balancing segment has all the other segments correct. This  is probably true more often than not, but doesn't have to be. It depends on the nature of the problem. If, in example, the offsetting account for cost adjustment was typed in wrong, changing just the balancing segment may actually make things worse as the fix will post the entries to a valid but wrong account. 
• All operating units reside in separate legal entities or have the same balancing segment if they reside in one legal entity. This is true in most implementations but not in all.

The actual code for Inventory subledger:

Declare

v_transaction_id number;
--v_COA number;
v_correct_account number;
i number;
j number;

cursor a is -- find all the material transactions that have balancing segment mismatch between CR and DT
-- this is to speed up cursor b
select distinct a.transaction_id from
mtl_transaction_accounts a, mtl_transaction_accounts b,
gl_code_combinations ac, gl_code_combinations bc
where
a.transaction_id=b.transaction_id
and a.reference_account=ac.CODE_COMBINATION_ID
and b.reference_account=bc.CODE_COMBINATION_ID
and ac.segment1!=bc.segment1;

cursor b is -- find the actual accounting entries for material trx from previous cursor
select mta.transaction_id, mta.inv_sub_ledger_id, gcc.chart_of_accounts_id, fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                gcc.chart_of_accounts_id,--v_COA,
                                xal.CODE_COMBINATION_ID) account, gle.FLEX_SEGMENT_VALUE org_balancing_segment,
                                xal.accounting_class_code,
                                 mta.reference_account, xah.ae_header_id, xal.ae_line_num,
                                 qa_moac_pkg.derive_ou_id(mmt.organization_id),
                                 mta.transaction_date, xah.ACCOUNTING_DATE, xah.period_name, xe.event_id, xe.APPLICATION_ID
                                  from
XLA_DISTRIBUTION_LINKS xdl,
XLA_AE_HEADERS xah,
XLA_AE_LINES xal,
XLA_EVENTS xe,
MTL_TRANSACTION_ACCOUNTS  mta,
mtl_material_transactions mmt,
hr_organization_information hoi,
gl_legal_entities_bsvs  gle,
gl_code_combinations gcc
where
xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1(+) = mta.inv_sub_ledger_id
and xah.ae_header_id(+) = xdl.ae_header_id
and  xdl.ae_line_num = xal.ae_line_num(+)
and xah.application_id = xal.application_id
and xah.ae_header_id = xal.ae_header_id
and xe.application_id=xah.application_id
and xe.event_id=xah.event_id
and mta.transaction_id=mmt.transaction_id(+)
and hoi.organization_id(+) = mmt.organization_id
and hoi.org_information_context = 'Accounting Information'
and to_number(hoi.ORG_INFORMATION2)=gle.LEGAL_ENTITY_ID(+)
and mta.reference_account=gcc.code_combination_id
and substr(fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                gcc.chart_of_accounts_id,--v_COA,
                                xal.CODE_COMBINATION_ID),1,length(gle.FLEX_SEGMENT_VALUE))!=gle.FLEX_SEGMENT_VALUE --code combination starts with wrong balancing segment
and
 mta.transaction_id =v_transaction_id;




BEGIN

i:=0;



for x in a loop
v_transaction_id:=x.transaction_id;

for y in b loop
i:=i+1;
v_correct_account:=XX_CCID.switch_CCID_segment(y.reference_account,
y.chart_of_accounts_id,
1,
y.org_balancing_segment,'.'); -- get the CCID with correct balancing segment

dbms_output.put_line('---------------------------');
dbms_output.put_line('transaction_Id= '||y.transaction_id);
dbms_output.put_line('balancing segment ='||y.org_balancing_segment);
dbms_output.put_line('original account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                y.reference_account));
dbms_output.put_line('corrected account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                v_correct_account));
dbms_output.put_line('corrected account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                v_correct_account));
dbms_output.put_line('original period = '||y.period_name);


--update the base accounting table
update mtl_transaction_accounts
set reference_account=v_correct_account
where inv_sub_ledger_id=y.inv_sub_ledger_id;

-- update the subledger acct tabels to keep them in sync
update XLA_AE_LINES set CODE_COMBINATION_ID=v_correct_account
where ae_header_id=y.ae_header_id
and AE_LINE_NUM=y.AE_LINE_NUM;

-- optional date update so that the transaction moves to the current accounting period
update xla_events set
event_date =sysdate,
 REFERENCE_DATE_1 =sysdate,
last_updated_by=2021,
last_update_date=sysdate
where event_id=y.event_id
and application_id=y.application_id;



end loop;
end loop;

dbms_output.put_line('  ');
dbms_output.put_line('  ');
dbms_output.put_line('============================== ');
dbms_output.put_line('total number of corrected entries = '||i);

rollback; -- change this to Commit; during final execution

END;

and for the WIP subledger

Declare

v_transaction_id number;
v_COA number;
v_correct_account number;
i number;
j number;

cursor a is -- find all the wip transactions that have balancing segment mismatch between CR and DT
-- this is to speed up cursor b
select distinct a.transaction_id from
WIP_transaction_accounts a, WIP_transaction_accounts b,
gl_code_combinations ac, gl_code_combinations bc
where
a.transaction_id=b.transaction_id
and a.reference_account=ac.CODE_COMBINATION_ID
and b.reference_account=bc.CODE_COMBINATION_ID
and ac.segment1!=bc.segment1;

cursor b is -- find the actual accounting entries for material trx from previous cursor
select mta.transaction_id, mta.wip_sub_ledger_id, gcc.chart_of_accounts_id, fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                gcc.chart_of_accounts_id,--v_COA,
                                xal.CODE_COMBINATION_ID) account, gle.FLEX_SEGMENT_VALUE org_balancing_segment,
                                xal.accounting_class_code,
                                 mta.reference_account, xah.ae_header_id, xal.ae_line_num,
                                 qa_moac_pkg.derive_ou_id(mmt.organization_id),
                                 mta.transaction_date, xah.ACCOUNTING_DATE, xah.period_name, xe.event_id, xe.APPLICATION_ID
                                  from
XLA_DISTRIBUTION_LINKS xdl,
XLA_AE_HEADERS xah,
XLA_AE_LINES xal,
XLA_EVENTS xe,
WIP_TRANSACTION_ACCOUNTS  mta,
wip_transactions mmt,
hr_organization_information hoi,
gl_legal_entities_bsvs  gle,
gl_code_combinations gcc
where
xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
and xdl.source_distribution_id_num_1(+) = mta.wip_sub_ledger_id
and xah.ae_header_id(+) = xdl.ae_header_id
and  xdl.ae_line_num = xal.ae_line_num(+)
and xah.application_id = xal.application_id
and xah.ae_header_id = xal.ae_header_id
and xe.application_id=xah.application_id
and xe.event_id=xah.event_id
and mta.transaction_id=mmt.transaction_id(+)
and hoi.organization_id(+) = mmt.organization_id
and hoi.org_information_context = 'Accounting Information'
and to_number(hoi.ORG_INFORMATION2)=gle.LEGAL_ENTITY_ID(+)
and mta.reference_account=gcc.code_combination_id
and substr(fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                gcc.chart_of_accounts_id,--v_COA,
                                xal.CODE_COMBINATION_ID),1,length(gle.FLEX_SEGMENT_VALUE))!=gle.FLEX_SEGMENT_VALUE --code combination starts with wrong balancing segment
and
 mta.transaction_id =v_transaction_id;




BEGIN

i:=0;



for x in a loop
v_transaction_id:=x.transaction_id;

for y in b loop
i:=i+1;
v_correct_account:=XX_CCID.switch_CCID_segment(y.reference_account,
y.chart_of_accounts_id,
1,
y.org_balancing_segment,'.'); -- get the CCID with correct balancing segment

dbms_output.put_line('---------------------------');
dbms_output.put_line('transaction_Id= '||y.transaction_id);
dbms_output.put_line('balancing segment ='||y.org_balancing_segment);
dbms_output.put_line('original account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                y.reference_account));
dbms_output.put_line('corrected account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                v_correct_account));
dbms_output.put_line('corrected account = '||fnd_flex_ext.get_segs ('SQLGL',
                                'GL#',
                                y.chart_of_accounts_id,--v_COA,
                                v_correct_account));
dbms_output.put_line('original period = '||y.period_name);


--update the base accounting table
update WIP_transaction_accounts
set reference_account=v_correct_account
where WIP_SUB_LEDGER_ID=y.WIP_SUB_LEDGER_ID;

-- update the subledger acct tabels to keep them in sync
update XLA_AE_LINES set CODE_COMBINATION_ID=v_correct_account
where ae_header_id=y.ae_header_id
and AE_LINE_NUM=y.AE_LINE_NUM;

-- optional date update so that the transaction moves to the current accounting period
update xla_events set
event_date =sysdate,
 REFERENCE_DATE_1 =sysdate,
last_updated_by=2021,
last_update_date=sysdate
where event_id=y.event_id
and application_id=y.application_id;



end loop;
end loop;

dbms_output.put_line('  ');
dbms_output.put_line('  ');
dbms_output.put_line('============================== ');
dbms_output.put_line('total number of corrected entries = '||i);

rollback; -- change this to Commit; during final execution

END;