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;

Switch single segment in account combination (CCID)

Occasionally I have a problem where the Debit and Credit entries in a subledger can’t balance due to mismatched balancing segment. The easy fix would be to change the segment the Code Combination ID (CCID), not the segments themselves, is stored in all the subledger accounting tables. One can’t just simply update the segment in a table but needs to find the proper CCID.

But what if the CCID does not exist, despite the account combination being valid? Simply inserting it into GL_CODE_COMBINATIONS could violate the cross-validation rules.

To address this problem, I wrote the simple function below that takes the original CCID as an input, switches the segment number provided in the p_segment_number parameter to value in the p_switch_to_parameter. 

Since it is all done using standard APIs it either :

•          returns the existing  valid CCID of the new combination
•           generates valid CCID if the combination does not exist
•          returns 0 when the combination is invalid and  no CCID exists or can be generated

This function can be used for data fixes but also for custom accounting programs and table triggers, where one needs to change one or more segments in an account. In example if you want to have the same combination in the Receivables account as in COGS with only the natural account different the AR Autoaccounting rules do not have functionality to accomplish it. You could explore putting a trigger on RA_INTERFACE_LINES_ALL to insert a value into RA_INTERFACE_DISTRIBUTIONS_ALL as long as the function returns non zero CCID

Here is how I designed the function:

1. Translate the CCID into the segment combination

2. Find the number of segments in the combination

3. Replace the segment

5. Translate the new segment combination into a CCID. 

The last part was a bit  tricky because the CCID for the new combination may or may not exist and I didn't want to get into building a logic that would check it. In addition to that there was always risk that an invalid combination can be created, so it had to be validated against GL cross validation rules. Under other circumstances I might have used account generator workflow but I wanted to keep it as simple as possible and to be used in SQL so Workflow was out of  question

Let's translate the above points into APIs used:

1. FND_FLEX_EXT.get_segs('SQLGL', 'GL#',v_coa_id,p_CCID) -- concatenated account

2. FND_FLEX_EXT.breakup_segments(concatenated_segs => v_input_combination

,delimiter => p_delimiter

,segments => v_input_array);

3. v_output_array:=v_input_array;

v_output_array(p_segment_number):=p_switch_to;  -- replace the segment

4. FND_FLEX_EXT.get_ccid('SQLGL','GL#', v_coa_id, fnd_date.date_to_canonical(sysdate), v_output_combination); --recreate the new array as CCID.

Here is an example of how the function can be called:

select ccid,

fnd_flex_ext.get_segs('SQLGL', 'GL#',CHART_OF_ACCOUNTS_ID,ccID) old_code_combination,

new_ccid,

fnd_flex_ext.get_segs('SQLGL', 'GL#',CHART_OF_ACCOUNTS_ID,new_ccID) new_code_combination

from

(

SELECT CHART_OF_ACCOUNTS_ID,a.code_combination_id ccID,

xx_ccID.switch_CCID_segment (CODE_COMBINATION_ID,   --p_CCID

CHART_OF_ACCOUNTS_ID,  --p_coa_id

1,                     --p_segment_number

'02',                  --p_switch_to

'.'                    --p_delimiter

                              )

          new_ccid

  FROM gl_code_combinations a

 WHERE code_combination_id = 21704)

and the actual package code:

CREATE OR REPLACE PACKAGE APPS.XX_CCID

AS

Function switch_CCID_segment (p_CCID IN NUMBER,

p_coa_id IN number,

p_segment_number IN number,

p_switch_to IN varchar2,

p_delimiter IN varchar2)

 return number;

END XX_CCID ;

/

CREATE OR REPLACE package body APPS.XX_CCID

 as

  Function switch_CCID_segment (p_CCID IN NUMBER,

p_coa_id IN number,

p_segment_number IN number,

p_switch_to IN varchar2,

p_delimiter IN varchar2)

 return number IS

v_new_account number;

v_coa_id number;

v_input_combination varchar2(200);

v_output_combination varchar2(200);

v_input_array FND_FLEX_EXT.SegmentArray;

v_output_array FND_FLEX_EXT.SegmentArray;

v_number_of_segments number;

V_ERROR VARCHAR2(2000);

Begin

-- chart of accounts

v_coa_id:=p_coa_id;

-- get the combination from the ccid

v_input_combination := fnd_flex_ext.get_segs('SQLGL', 'GL#',v_coa_id,p_CCID) ;

-- this returns the number of segments but also feeds the segments to the v_input_array

v_number_of_segments:=FND_FLEX_EXT.breakup_segments(concatenated_segs => v_input_combination

,delimiter => p_delimiter

,segments => v_input_array);

v_output_array:=v_input_array;

--switch the segmentvalue

v_output_array(p_segment_number):=p_switch_to;

-- concatenate the new combination into string

v_output_combination:=FND_FLEX_EXT.concatenate_segments(v_number_of_segments,v_output_array,p_delimiter);

-- convert the string to CCID

v_new_account:=FND_FLEX_EXT.get_ccid('SQLGL','GL#', v_coa_id,fnd_date.date_to_canonical(sysdate),v_output_combination);

return v_new_account;

EXCEPTION

When others then

-- in case of failure return the original account

v_new_account:=p_CCID;

return v_new_account;

END switch_CCID_segment;

END XX_CCID;

/

Very simple function that can save tons of time.

Single drop shipment warehouse

In many cases enterprises need only one Drop Shipment warehouse, which is separate from the regular stock carrying facility. Separating the Drop Shipment Organization (DSO) into its own entity allows for much better control over billing as any on-hand quantities in that org are a clear sign of a customer billing that went wrong. There is much less transparency if the onhands form drop ships are commingled with regular on hand quantities in stock carrying warehouses.

The trick to having a single drop ship warehouse is to make sure that it gets defaulted in onto all Externally sourced lines, and especially when the line Source Type is changed from Internal to External.

Here are the changes and setups to make it happen:

1.       Make Source Type a dependency source attribute

Enter the below code into OE_Line_Util_Ext. Clear_Dependent_Attr

IF NOT OE_GLOBALS.Equal(p_x_line_rec.source_type_code, p_old_line_rec.SOURCE_TYPE_code)

        THEN

            l_index := l_index + 1.0;

            l_src_attr_tbl(l_index) := OE_LINE_UTIL.G_SOURCE_TYPE ;

        END IF;

This goes at the end of the package, as the very last code inside the main IF statement  in the package. It will make the ‘Source Type’ a dependency source attribute

2.       Change the OEXUDEPB.pls  version

From something like this:

/* $Header: OEXUDEPB.pls 120.5.12010000.7 2012/02/01 20:10:02 gabhatia ship $ */

To

/* $Header: OEXUDEPB.pls 120.999 custom version freeze  $ */

This is recommended by oracle documentation to prevent patches from overriding the changes in OE_Line_Util_Ext. If any patches alter this package the above code changes will have to be applied to the OEXUDEPB.pls  version  provided by the patch manually and only then the new version can be deployed.

3.       Make Warehouse dependent on Source Type

Alter OE_Dependencies_Extn. Load_Entity_Attributes by uncommenting the sample dependency code and adding the text highlighted in red

   ELSIF p_entity_code = OE_GLOBALS.G_ENTITY_LINE THEN

       null;

       -- Sample Code for Disabling dependency of Invoice To on Ship To

       -- x_extn_dep_tbl(l_index).source_attribute := OE_LINE_UTIL.G_SHIP_TO_ORG;

       -- x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_INVOICE_TO_ORG;

       -- x_extn_dep_tbl(l_index).enabled_flag := 'N';

       -- l_index := l_index + 1;

       -- Sample Code for adding dependency of Source Type on Item

        x_extn_dep_tbl(l_index).source_attribute :=  OE_LINE_UTIL.G_SOURCE_TYPE;

        x_extn_dep_tbl(l_index).dependent_attribute := OE_LINE_UTIL.G_SHIP_FROM_ORG;

        x_extn_dep_tbl(l_index).enabled_flag := 'Y';

        l_index := l_index + 1;

    END IF;

4.       Change the OEXEDEPB.pls version to prevent patches from overriding OE_Dependencies_Extn. Similar way as in p.2

5.       Create new dependency template

OM-> Setup -> Rules -> Defaulting -> Order Line -> Defaulting Condition Templates

6.       Change order of defaulting rules by putting Source Type in front of Warehouse

OM-> Setup -> Rules -> Defaulting -> Order Line

7.       Set up the default drop ship warehouse

OM-> Setup -> Rules -> Defaulting -> Order Line ->Warehouse -> Defaulting Rules

Make sure that the Drop Ship condition has lower precedence than the Always condition

Go to Tools and Generate the Defaulting Handler Package

Now that all the setups are done, you can create an OM line, add Receiving Org to your folder and see how the warehouse and Source Org change as you change the Source Type from Internal to External.

The last thing to remember is adding items and costs to your new organization.
To simplify and full automate it, you may consider

• creating a concurrent request based on EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG API to always add all the items from your mater item org to the Drop Shipment Organization.
• defining the org as FIFO, even if you are Standard or Average Cost shop in all other orgs. This way you will never have any variances and never have to define cost for this org.

With that in place, create an alert of recurring report to tell you about any onhands present in the DSO org. If there are any that means someone didn't get invoiced properly...

With the single drop shipment org this is trivial to identify.

Workflow stuck in Ship Confirm, Trip Stop fails OM interface

During my carrier I often run across an annoying scenario where the entire order line was shipped but Trip Stop failed interfacing to OM and workflow would not progress past Ship Confirm.
When I run across the same issue a few days ago Oracle Support quickly identified the problem and ... recommended a patch that would take weeks to test and put into production.

To work around the issue I have prepared the below script. Use it for a single stuck OM line at a time and only if there are going to be no more partial shipments for it :

declare

cursor c is

select line_id from oe_order_lines_all where line_id in (

select distinct(source_line_id) from wsh_delivery_details where delivery_detail_id in (147432,147710)

and flow_status_code='AWAITING_SHIPPING'

);

begin

FOR r in c loop

-- progress the workflow

wf_engine.CompleteActivity('OEOL', to_char(r.line_id),'SHIP_LINE', 'SHIP_CONFIRM');

--mark shipment as itnerfaced to OM so you can run Trip Stop for Inventory

update wsh_delivery_details set oe_interfaced_flag='Y' where source_line_id=r.line_id;

-- update order line with quantity shipped so that it can be invoiced,

-- leaving this out would result in Invoice Interface not Eligible and no invoice

update oe_order_lines_all set shipped_quantity=

(select sum(shipped_quantity) from wsh_delivery_details where  source_line_id=r.line_id)
where line_id=r.line_id;

dbms_output.put_line(r.line_id||' processed');

end loop;

Exception

when others then null;

end;

This will push the OM line past Ship Confirm and create invoice in the end.  To be safe you may consider adding a 'not exist' statement in the cursor for the wsh_delivery_details t

o eliminate order lines that have delivery details in status other than Shipped or Cancelled.