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.