[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FII_UTIL.Write_Log('Inserting DBI log items into FII_CHANGE_LOG');
INSERT INTO FII_CHANGE_LOG (
log_item,
item_value,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by)
SELECT
DECODE(glrm.multiplier,
1, 'AR_RESUMMARIZE',
2, 'GL_RESUMMARIZE',
3, 'AP_RESUMMARIZE',
4, 'MAX_CCID',
5, 'CCID_RELOAD',
6, 'PROD_CAT_SET_ID',
7, 'GL_PROD_CHANGE',
8, 'AR_PROD_CHANGE'),
DECODE(glrm.multiplier,
1, 'N',
2, 'N',
3, 'N',
4, '0',
5, 'N',
6, G_PROD_CAT_SET_ID,
7, 'N',
8, 'N'),
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_user_id
FROM GL_ROW_MULTIPLIERS glrm
WHERE glrm.multiplier between 1 and 8
AND NOT EXISTS
(SELECT 1
FROM FII_CHANGE_LOG
WHERE log_item = DECODE(glrm.multiplier,
1, 'AR_RESUMMARIZE',
2, 'GL_RESUMMARIZE',
3, 'AP_RESUMMARIZE',
4, 'MAX_CCID',
5, 'CCID_RELOAD',
6, 'PROD_CAT_SET_ID',
7, 'GL_PROD_CHANGE',
8, 'AR_PROD_CHANGE'));
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' log items into FII_CHANGE_LOG');
SELECT DISTINCT id_flex_structure_name INTO l_coa_name
FROM fnd_id_flex_structures_tl t
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = p_coa_id
AND language = g_current_language;
SELECT fsav1.application_column_name,
fsav2.application_column_name,
fsav3.application_column_name
INTO p_company_seg,
p_cc_seg,
p_natural_seg
FROM FND_SEGMENT_ATTRIBUTE_VALUES fsav1,
FND_SEGMENT_ATTRIBUTE_VALUES fsav2,
FND_SEGMENT_ATTRIBUTE_VALUES fsav3
WHERE fsav1.application_id = 101
AND fsav1.id_flex_code = 'GL#'
AND fsav1.id_flex_num = p_coa_id
AND fsav1.segment_attribute_type = 'GL_BALANCING'
AND fsav1.attribute_value = 'Y'
AND fsav2.application_id = 101
AND fsav2.id_flex_code = 'GL#'
AND fsav2.id_flex_num = p_coa_id
AND fsav2.segment_attribute_type = 'FA_COST_CTR'
AND fsav2.attribute_value = 'Y'
AND fsav3.application_id = 101
AND fsav3.id_flex_code = 'GL#'
AND fsav3.id_flex_num = p_coa_id
AND fsav3.segment_attribute_type = 'GL_ACCOUNT'
AND fsav3.attribute_value = 'Y';
select distinct COA_ID
from FII_CCID_SLG_GT
where BAL_SEG_VALUE_ID = -1;
FII_UTIL.Write_Log('Insert to FII_CCID_SLG_GT by select DISTINCT ');
insert into FII_CCID_SLG_GT
(COA_ID,
BAL_SEG_VALUE,
BAL_SEG_VALUE_ID)
select DISTINCT
sts.chart_of_accounts_id,
sts.bal_seg_value,
sts.bal_seg_value_id
from fii_slg_assignments sts,
fii_source_ledger_groups slg
where slg.usage_code = 'DBI'
and slg.source_ledger_group_id = sts.source_ledger_group_id;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CCID_SLG_GT');
FII_UTIL.Write_Log('Update FII_CCID_SLG_GT for BAL_SEG_VALUE_ID = -1 (insert all company values)');
delete from FII_CCID_SLG_GT where COA_ID = l_coa_id;
SELECT application_column_name INTO l_company_seg
FROM FND_SEGMENT_ATTRIBUTE_VALUES
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = l_coa_id
AND segment_attribute_type = 'GL_BALANCING'
AND attribute_value = 'Y';
l_stmt := 'INSERT INTO FII_CCID_SLG_GT
(COA_ID,
BAL_SEG_VALUE,
BAL_SEG_VALUE_ID)
SELECT DISTINCT
CHART_OF_ACCOUNTS_ID,
' || l_company_seg || ',
-2
FROM GL_CODE_COMBINATIONS
WHERE CHART_OF_ACCOUNTS_ID = ' || l_coa_id || '
AND SUMMARY_FLAG = ''N''
AND TEMPLATE_ID IS NULL ';
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CCID_SLG_GT');
PROCEDURE INSERT_INTO_CCID_DIM (p_company_seg IN VARCHAR2,
p_cc_seg IN VARCHAR2,
p_natural_seg IN VARCHAR2,
p_ud1_seg IN VARCHAR2,
p_ud2_seg IN VARCHAR2) IS
l_stmt VARCHAR2(10000);
FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
FII_UTIL.Write_Log('Inserting CCIDs in chart of accounts: ' ||
p_company_seg || ' - ' ||
p_cc_seg || ' - ' ||
p_natural_seg || ' - ' ||
p_ud1_seg || ' - ' ||
p_ud2_seg);
l_stmt := 'INSERT INTO FII_GL_CCID_DIMENSIONS (
code_combination_id,
chart_of_accounts_id,
company_id,
cost_center_id,
natural_account_id,
company_cost_center_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
user_dim1_id,
user_dim2_id)
SELECT /*+ ordered use_nl(seg1,seg2,seg3)
use_hash(glcc) */
glcc.code_combination_id,
glcc.chart_of_accounts_id,
flx1.flex_value_id,
flx2.flex_value_id,
flx3.flex_value_id,
NVL(glcc.company_cost_center_org_id, -1),
sysdate,
' ||g_fii_user_id || ',
sysdate,
' || g_fii_user_id || ',
' || g_fii_login_id ;
l_stmt := l_stmt || ' FROM ( select coa_id, udd1_vset_id, udd2_vset_id
from FII_ACCT_SEG_GT
where company_seg_name = ''' || p_company_seg || '''
and costctr_seg_name = ''' || p_cc_seg || '''
and natural_seg_name = ''' || p_natural_seg || '''
and nvl(udd1_seg_name, 1) = nvl(''' || p_ud1_seg || ''',1)
and nvl(udd2_seg_name, 1) =nvl(''' || p_ud2_seg || ''',1)
) accts,
FII_CCID_SLG_GT csg,
fnd_id_flex_segments seg1,
fnd_id_flex_segments seg2,
fnd_id_flex_segments seg3,
GL_CODE_COMBINATIONS glcc,
fnd_flex_values flx1,
fnd_flex_values flx2,
fnd_flex_values flx3 ';
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
Error occured in Procedure: INSERT_INTO_CCID_DIM
Message: ' || sqlerrm);
FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_INTO_CCID_DIM');
END INSERT_INTO_CCID_DIM;
PROCEDURE INSERT_INTO_CCID_DIM_INIT (p_company_seg IN VARCHAR2,
p_cc_seg IN VARCHAR2,
p_natural_seg IN VARCHAR2,
p_ud1_seg IN VARCHAR2,
p_ud2_seg IN VARCHAR2) IS
l_stmt VARCHAR2(10000);
FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
FII_UTIL.Write_Log('Inserting CCIDs in chart of accounts: ' ||
p_company_seg || ' - ' ||
p_cc_seg || ' - ' ||
p_natural_seg);
l_stmt := 'INSERT /*+ append parallel(fii) */ INTO
FII_GL_CCID_DIMENSIONS fii (
code_combination_id,
chart_of_accounts_id,
company_id,
cost_center_id,
natural_account_id,
company_cost_center_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
user_dim1_id, user_dim2_id)
SELECT /*+ ordered use_nl(seg1,seg2,seg3)
use_hash(glcc) parallel(glcc) */
glcc.code_combination_id,
glcc.chart_of_accounts_id,
flx1.flex_value_id,
flx2.flex_value_id,
flx3.flex_value_id,
NVL(glcc.company_cost_center_org_id, -1),
sysdate,
' ||g_fii_user_id || ',
sysdate,
' || g_fii_user_id || ',
' || g_fii_login_id || ',';
l_stmt := l_stmt || ' FROM ( select coa_id, udd1_vset_id, udd2_vset_id
from FII_ACCT_SEG_GT
where company_seg_name = ''' || p_company_seg || '''
and costctr_seg_name = ''' || p_cc_seg || '''
and natural_seg_name = ''' || p_natural_seg || '''
and nvl(udd1_seg_name, 1) = nvl('''|| p_ud1_seg ||''',1)
and nvl(udd2_seg_name, 1) =nvl('''|| p_ud2_seg ||''',1)
) accts,
FII_CCID_SLG_GT csg,
fnd_id_flex_segments seg1,
fnd_id_flex_segments seg2,
fnd_id_flex_segments seg3,
GL_CODE_COMBINATIONS glcc,
fnd_flex_values flx1,
fnd_flex_values flx2,
fnd_flex_values flx3 ';
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_GL_CCID_DIMENSIONS');
FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
Error occured in Procedure: INSERT_INTO_CCID_DIM_INIT
Message: ' || sqlerrm);
FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_INTO_CCID_DIM_INIT');
END INSERT_INTO_CCID_DIM_INIT;
g_phase := 'SELECT FROM fii_gl_ccid_dimensions';
SELECT MAX(code_combination_id) INTO l_tmp_max_ccid
FROM fii_gl_ccid_dimensions;
g_phase := 'UPDATE fii_change_log';
UPDATE fii_change_log
SET item_value = decode (log_item, 'MAX_CCID', to_char(g_new_max_ccid),
'PROD_CAT_SET_ID', g_prod_cat_set_id),
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'MAX_CCID'
OR (log_item = 'PROD_CAT_SET_ID' and g_prod_cat_set_id is not null);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
SELECT item_value INTO g_max_ccid
FROM fii_change_log
WHERE log_item = g_log_item;
SELECT max(code_combination_id) INTO g_new_max_ccid
FROM gl_code_combinations;
UPDATE fii_gl_ccid_dimensions dim
SET dim.company_cost_center_org_id =
(SELECT NVL(gcc.company_cost_center_org_id, -1)
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = dim.code_combination_id)
WHERE dim.company_cost_center_org_id = -1;
PROCEDURE INSERT_NEW_CCID IS
CURSOR sss_list IS
SELECT DISTINCT company_seg_name, costctr_seg_name,
natural_seg_name, udd1_seg_name, udd2_seg_name
FROM FII_ACCT_SEG_GT;
FII_MESSAGE.Func_Ent('FII_GL_CCID_C.INSERT_NEW_CCID');
SELECT item_value INTO g_max_ccid
FROM fii_change_log
WHERE log_item = g_log_item;
g_phase := 'UPDATE fii_change_log';
UPDATE fii_change_log
SET item_value = '0',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = g_log_item;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');
g_phase := 'SELECT FROM gl_code_combinations';
SELECT max(code_combination_id) INTO g_new_max_ccid
FROM gl_code_combinations;
g_phase := 'Insert new CCIDs into FII_GL_CCID_DIMENSIONS table';
-- in the INSERT_INTO_CCID API.
-- For supporting UD1/UD2 dimensions as well segment names for
-- these two dimensions are also populated. Also to avoid join
-- with FII_DIM_MAPPING_RULES the value set id is also populated.
------------------------------------------------------------------
FII_UTIL.TRUNCATE_TABLE('FII_ACCT_SEG_GT', g_fii_schema, g_retcode);
g_phase := 'INSERT INTO FII_ACCT_SEG_GT';
INSERT INTO FII_ACCT_SEG_GT(
coa_id, company_seg_name, costctr_seg_name, natural_seg_name
)
SELECT coa_list.chart_of_accounts_id,
fsav1.application_column_name,
fsav2.application_column_name,
fsav3.application_column_name
FROM ( SELECT DISTINCT sts.chart_of_accounts_id
FROM fii_slg_assignments sts,
fii_source_ledger_groups slg
WHERE slg.usage_code = 'DBI'
AND slg.source_ledger_group_id = sts.source_ledger_group_id
) coa_list,
FND_SEGMENT_ATTRIBUTE_VALUES fsav1,
FND_SEGMENT_ATTRIBUTE_VALUES fsav2,
FND_SEGMENT_ATTRIBUTE_VALUES fsav3
WHERE fsav1.application_id = 101
AND fsav1.id_flex_code = 'GL#'
AND fsav1.id_flex_num = coa_list.chart_of_accounts_id
AND fsav1.segment_attribute_type = 'GL_BALANCING'
AND fsav1.attribute_value = 'Y'
AND fsav2.application_id = 101
AND fsav2.id_flex_code = 'GL#'
AND fsav2.id_flex_num = coa_list.chart_of_accounts_id
AND fsav2.segment_attribute_type = 'FA_COST_CTR'
AND fsav2.attribute_value = 'Y'
AND fsav3.application_id = 101
AND fsav3.id_flex_code = 'GL#'
AND fsav3.id_flex_num = coa_list.chart_of_accounts_id
AND fsav3.segment_attribute_type = 'GL_ACCOUNT'
AND fsav3.attribute_value = 'Y';
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
-- with FII_DIM_MAPPING_RULES in INSERT_INTO_CCID_DIM_INIT/INSERT_INTO_CCID_DIM
-- the value set id is also populated.
--------------------------------------------------------------------------------
IF(G_UD1_ENABLED = 'Y' ) THEN
g_dimension_name := 'FII_USER_DEFINED_1';
UPDATE FII_ACCT_SEG_GT tab1
SET (udd1_seg_name, udd1_vset_id) = (select application_column_name1, flex_value_set_id1
from fii_dim_mapping_rules
where chart_of_accounts_id = tab1.coa_id
and dimension_short_name = g_dimension_name);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
UPDATE FII_ACCT_SEG_GT tab1
SET (udd2_seg_name, udd2_vset_id) = (select application_column_name1, flex_value_set_id1
from fii_dim_mapping_rules
where chart_of_accounts_id = tab1.coa_id
and dimension_short_name = g_dimension_name);
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_ACCT_SEG_GT');
g_phase := 'Call INSERT_INTO_CCID_DIM_INIT';
INSERT_INTO_CCID_DIM_INIT(
sss.company_seg_name,
sss.costctr_seg_name,
sss.natural_seg_name,
sss.udd1_seg_name,
sss.udd2_seg_name);
g_phase := 'Call INSERT_INTO_CCID_DIM';
INSERT_INTO_CCID_DIM(
sss.company_seg_name,
sss.costctr_seg_name,
sss.natural_seg_name,
sss.udd1_seg_name,
sss.udd2_seg_name);
FII_MESSAGE.Func_Succ('FII_GL_CCID_C.INSERT_NEW_CCID');
UPDATE fii_change_log
SET item_value = '0',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = g_log_item;
Error occured in Procedure: INSERT_NEW_CCID
Phase: ' || g_phase || '
Message: ' || sqlerrm);
FII_MESSAGE.Func_Fail('FII_GL_CCID_C.INSERT_NEW_CCID');
END INSERT_NEW_CCID;
l_stmt := 'UPDATE fii_gl_ccid_dimensions glcc
SET (glcc.product_id, glcc.PROD_CATEGORY_ID) =
(SELECT flx1.flex_value_id, mtc.category_id
FROM gl_code_combinations glccd,
mtl_categories mtc,
fnd_id_flex_segments seg1,
fnd_flex_values flx1
WHERE glccd.code_combination_id = glcc.code_combination_id
AND mtc.structure_id = ' || g_mtc_structure_id || '
AND mtc.' || g_mtc_column_name || ' = glccd.' || p_product_seg || '
AND seg1.application_id = 101
AND seg1.id_flex_code = ''GL#''
AND seg1.id_flex_num = ' || p_coa_id || '
AND seg1.application_column_name = ''' || p_product_seg || '''
AND glccd.' || p_product_seg || ' = flx1.flex_value
AND flx1.flex_value_set_id = seg1.flex_value_set_id)
WHERE glcc.chart_of_accounts_id = ' || p_coa_id;
FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
|| ' records in FII_GL_CCID_DIMENSIONS');
SELECT code_combination_id, count(*) cnt
FROM fii_gl_ccid_prod_int
GROUP BY code_combination_id
HAVING count(*) > 1;
SELECT cat.description cat_name
FROM fii_gl_ccid_prod_int int,
mtl_categories cat
WHERE int.code_combination_id = p_ccid
AND int.prod_category_id = cat.category_id;
l_stmt := 'INSERT INTO FII_GL_CCID_PROD_INT (
code_combination_id,
prod_category_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
glcc.code_combination_id,
fipa.prod_category_id,
sysdate,
' ||g_fii_user_id || ',
sysdate,
' || g_fii_user_id || ',
' || g_fii_login_id || '
FROM gl_code_combinations glcc,
fii_product_assignments fipa
WHERE glcc.chart_of_accounts_id = :p_coa_id
AND fipa.chart_of_accounts_id = glcc.chart_of_accounts_id
AND fipa.PROD_CATEGORY_SET_ID = :G_PROD_CAT_SET_ID
AND NVL(glcc.segment1,1) >= NVL(fipa.segment1_low, NVL(glcc.segment1,1))
AND NVL(glcc.segment1,1) <= NVL(fipa.segment1_high, NVL(glcc.segment1,1))
AND NVL(glcc.segment2,1) >= NVL(fipa.segment2_low, NVL(glcc.segment2,1))
AND NVL(glcc.segment2,1) <= NVL(fipa.segment2_high, NVL(glcc.segment2,1))
AND NVL(glcc.segment3,1) >= NVL(fipa.segment3_low, NVL(glcc.segment3,1))
AND NVL(glcc.segment3,1) <= NVL(fipa.segment3_high, NVL(glcc.segment3,1))
AND NVL(glcc.segment4,1) >= NVL(fipa.segment4_low, NVL(glcc.segment4,1))
AND NVL(glcc.segment4,1) <= NVL(fipa.segment4_high, NVL(glcc.segment4,1))
AND NVL(glcc.segment5,1) >= NVL(fipa.segment5_low, NVL(glcc.segment5,1))
AND NVL(glcc.segment5,1) <= NVL(fipa.segment5_high, NVL(glcc.segment5,1))
AND NVL(glcc.segment6,1) >= NVL(fipa.segment6_low, NVL(glcc.segment6,1))
AND NVL(glcc.segment6,1) <= NVL(fipa.segment6_high, NVL(glcc.segment6,1))
AND NVL(glcc.segment7,1) >= NVL(fipa.segment7_low, NVL(glcc.segment7,1))
AND NVL(glcc.segment7,1) <= NVL(fipa.segment7_high, NVL(glcc.segment7,1))
AND NVL(glcc.segment8,1) >= NVL(fipa.segment8_low, NVL(glcc.segment8,1))
AND NVL(glcc.segment8,1) <= NVL(fipa.segment8_high, NVL(glcc.segment8,1))
AND NVL(glcc.segment9,1) >= NVL(fipa.segment9_low, NVL(glcc.segment9,1))
AND NVL(glcc.segment9,1) <= NVL(fipa.segment9_high, NVL(glcc.segment9,1))
AND NVL(glcc.segment10,1) >= NVL(fipa.segment10_low, NVL(glcc.segment10,1))
AND NVL(glcc.segment10,1) <= NVL(fipa.segment10_high, NVL(glcc.segment10,1))
AND NVL(glcc.segment11,1) >= NVL(fipa.segment11_low, NVL(glcc.segment11,1))
AND NVL(glcc.segment11,1) <= NVL(fipa.segment11_high, NVL(glcc.segment11,1))
AND NVL(glcc.segment12,1) >= NVL(fipa.segment12_low, NVL(glcc.segment12,1))
AND NVL(glcc.segment12,1) <= NVL(fipa.segment12_high, NVL(glcc.segment12,1))
AND NVL(glcc.segment13,1) >= NVL(fipa.segment13_low, NVL(glcc.segment13,1))
AND NVL(glcc.segment13,1) <= NVL(fipa.segment13_high, NVL(glcc.segment13,1))
AND NVL(glcc.segment14,1) >= NVL(fipa.segment14_low, NVL(glcc.segment14,1))
AND NVL(glcc.segment14,1) <= NVL(fipa.segment14_high, NVL(glcc.segment14,1))
AND NVL(glcc.segment15,1) >= NVL(fipa.segment15_low, NVL(glcc.segment15,1))
AND NVL(glcc.segment15,1) <= NVL(fipa.segment15_high, NVL(glcc.segment15,1))
AND NVL(glcc.segment16,1) >= NVL(fipa.segment16_low, NVL(glcc.segment16,1))
AND NVL(glcc.segment16,1) <= NVL(fipa.segment16_high, NVL(glcc.segment16,1))
AND NVL(glcc.segment17,1) >= NVL(fipa.segment17_low, NVL(glcc.segment17,1))
AND NVL(glcc.segment17,1) <= NVL(fipa.segment17_high, NVL(glcc.segment17,1))
AND NVL(glcc.segment18,1) >= NVL(fipa.segment18_low, NVL(glcc.segment18,1))
AND NVL(glcc.segment18,1) <= NVL(fipa.segment18_high, NVL(glcc.segment18,1))
AND NVL(glcc.segment19,1) >= NVL(fipa.segment19_low, NVL(glcc.segment19,1))
AND NVL(glcc.segment19,1) <= NVL(fipa.segment19_high, NVL(glcc.segment19,1))
AND NVL(glcc.segment20,1) >= NVL(fipa.segment20_low, NVL(glcc.segment20,1))
AND NVL(glcc.segment20,1) <= NVL(fipa.segment20_high, NVL(glcc.segment20,1))
AND NVL(glcc.segment21,1) >= NVL(fipa.segment21_low, NVL(glcc.segment21,1))
AND NVL(glcc.segment21,1) <= NVL(fipa.segment21_high, NVL(glcc.segment21,1))
AND NVL(glcc.segment22,1) >= NVL(fipa.segment22_low, NVL(glcc.segment22,1))
AND NVL(glcc.segment22,1) <= NVL(fipa.segment22_high, NVL(glcc.segment22,1))
AND NVL(glcc.segment23,1) >= NVL(fipa.segment23_low, NVL(glcc.segment23,1))
AND NVL(glcc.segment23,1) <= NVL(fipa.segment23_high, NVL(glcc.segment23,1))
AND NVL(glcc.segment24,1) >= NVL(fipa.segment24_low, NVL(glcc.segment24,1))
AND NVL(glcc.segment24,1) <= NVL(fipa.segment24_high, NVL(glcc.segment24,1))
AND NVL(glcc.segment25,1) >= NVL(fipa.segment25_low, NVL(glcc.segment25,1))
AND NVL(glcc.segment25,1) <= NVL(fipa.segment25_high, NVL(glcc.segment25,1))
AND NVL(glcc.segment26,1) >= NVL(fipa.segment26_low, NVL(glcc.segment26,1))
AND NVL(glcc.segment26,1) <= NVL(fipa.segment26_high, NVL(glcc.segment26,1))
AND NVL(glcc.segment27,1) >= NVL(fipa.segment27_low, NVL(glcc.segment27,1))
AND NVL(glcc.segment27,1) <= NVL(fipa.segment27_high, NVL(glcc.segment27,1))
AND NVL(glcc.segment28,1) >= NVL(fipa.segment28_low, NVL(glcc.segment28,1))
AND NVL(glcc.segment28,1) <= NVL(fipa.segment28_high, NVL(glcc.segment28,1))
AND NVL(glcc.segment29,1) >= NVL(fipa.segment29_low, NVL(glcc.segment29,1))
AND NVL(glcc.segment29,1) <= NVL(fipa.segment29_high, NVL(glcc.segment29,1))
AND NVL(glcc.segment30,1) >= NVL(fipa.segment30_low, NVL(glcc.segment30,1))
AND NVL(glcc.segment30,1) <= NVL(fipa.segment30_high, NVL(glcc.segment30,1))';
FII_UTIL.Write_Log('Inserted Product Assignments for ' || SQL%ROWCOUNT
|| ' records in FII_GL_CCID_PROD_INT');
UPDATE fii_gl_ccid_dimensions glcc
SET glcc.PROD_CATEGORY_ID =
(SELECT NVL(int.prod_category_id, glcc.prod_category_id)
FROM fii_gl_ccid_prod_int int
WHERE int.code_combination_id = glcc.code_combination_id
AND glcc.chart_of_accounts_id = p_coa_id)
WHERE glcc.chart_of_accounts_id = p_coa_id;
FII_UTIL.Write_Log('Updated Product Assignments for ' || SQL%ROWCOUNT
|| ' records in FII_GL_CCID_DIMENSIONS');
SELECT 1 INTO l_count
FROM fii_gl_ccid_dimensions
WHERE PRODUCT_ID IS NOT NULL
AND PROD_CATEGORY_ID IS NULL
AND rownum = 1;
select coa_id,
prod_seg,
assignment_type_code,
fact_resummarization_needed
from (
SELECT DISTINCT
map.chart_of_accounts_id coa_id,
NVL(map.application_column_name1,'NO_PROD_COLUMN') prod_seg,
map.mapping_type_code assignment_type_code,
'FALSE' fact_resummarization_needed
FROM fii_gl_ccid_dimensions gcc,
fii_dim_mapping_rules map
WHERE gcc.chart_of_accounts_id = map.chart_of_accounts_id
AND map.dimension_short_name = g_dimension_name
AND gcc.code_combination_id > g_max_ccid
UNION ALL
SELECT chart_of_accounts_id coa_id,
NVL(application_column_name1,'NO_PROD_COLUMN') prod_seg,
mapping_type_code assignment_type_code,
'TRUE' fact_resummarization_needed
FROM fii_dim_mapping_rules
WHERE dimension_short_name = g_dimension_name
AND status_code = 'O')
order by 1;
UPDATE FII_CHANGE_LOG
SET item_value = 'Y',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item IN ('AR_PROD_CHANGE', 'GL_PROD_CHANGE');
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CHANGE_LOG');
UPDATE fii_dim_mapping_rules
SET status_code = 'C',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE dimension_short_name = g_dimension_name
AND status_code = 'O';
FII_UTIL.Write_Log ('Updated ' || SQL%ROWCOUNT || ' records in FII_DIM_MAPPING_RULES');
SELECT dbi_enabled_flag into G_PROD_CAT_ENABLED_FLAG
FROM FII_FINANCIAL_DIMENSIONS
WHERE dimension_short_name = g_dimension_name;
SELECT structure_id INTO g_mtc_structure_id
FROM mtl_category_sets_vl
WHERE category_set_id = g_prod_cat_set_id;
SELECT application_column_name into g_mtc_column_name
FROM
(select application_column_name
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MCAT'
and id_flex_num = g_mtc_structure_id
and flex_value_set_id = g_mtc_value_set_id
and enabled_flag = 'Y'
order by to_number(substr(application_column_name, 8, 2)) ASC)
WHERE rownum = 1;
g_phase := 'Checking product category for incremental update...';
SELECT item_value INTO l_old_prod_cat
FROM fii_change_log
WHERE log_item = g_log_item;
SELECT DBI_ENABLED_FLAG
INTO G_UD1_ENABLED
FROM FII_FINANCIAL_DIMENSIONS
WHERE DIMENSION_SHORT_NAME = g_dimension_name;
SELECT DBI_ENABLED_FLAG
INTO G_UD2_ENABLED
FROM FII_FINANCIAL_DIMENSIONS
WHERE DIMENSION_SHORT_NAME = g_dimension_name;
FII_UTIL.Write_Log ('>>New product catalog is detected for incremental update');
SELECT item_value INTO l_reload
FROM fii_change_log
WHERE log_item = g_log_item;
UPDATE fii_change_log
SET item_value = '0',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = g_log_item;
FII_UTIL.Write_Log(SQL%ROWCOUNT || ' record got updated');
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = g_log_item;
FII_UTIL.Write_Log(SQL%ROWCOUNT || ' record got updated');
g_phase := 'Call INSERT_NEW_CCID';
INSERT_NEW_CCID;
g_phase := 'Call FII_CCID_CALLOUT.UPDATE_FC';
FII_CCID_CALLOUT.UPDATE_FC(g_max_ccid, g_new_max_ccid);
g_phase := 'UPDATE fii_change_log';
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = sysdate,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = g_log_item
AND item_value = 'Y';
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');