[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_path VARCHAR2(100) := 'psa.plsql.glffglib.gl_flex_insert_pkg.';
user_id gl_code_combinations.last_updated_by%TYPE;
login_id gl_budget_assignments.last_update_login%TYPE;
/* Main Routine for insertion of Foundation Flexfields. Returns TRUE */
/* if successful; otherwise, it returns FALSE. */
select 'Y' into l_temp_var
from dual
where not exists (select 'x'
from gl_account_hierarchies
where detail_code_combination_id = ccid)
and not exists (select 'x'
from gl_budget_assignments
where code_combination_id = ccid);
' update company_cost_enter_org_id of GL_CODE_COMBINATIONS');
' Insert newly created Parent Accounts into Code Combinations table');
sql_glcc := 'select ' ||
'chart_of_accounts_id, ' ||
'detail_budgeting_allowed_flag, ' ||
'account_type';
select count(*)
from gl_ledgers
where enable_budgetary_control_flag = 'Y'
and chart_of_accounts_id = coaid;
select application_column_name,
nvl(flex_value_set_id, 0) value_set_id
from fnd_id_flex_segments
where enabled_flag = 'Y'
and id_flex_num = flex_num
and application_id = appl_id
and id_flex_code = flex_code;
select /*+ ORDERED INDEX (FND_SEGMENT_ATTRIBUTE_VALUES
FND_SEGMENT_ATTRIBUTE_VALS_U1) */
application_column_name
from fnd_segment_attribute_values
where attribute_value = 'Y'
and segment_attribute_type = 'GL_ACCOUNT'
and id_flex_num = flex_num
and application_id = appl_id
and id_flex_code = flex_code;
insert into gl_budget_assignments (
budget_entity_id,
ledger_id,
currency_code,
range_id,
entry_code,
ordering_value,
code_combination_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
select bar.budget_entity_id,
bar.ledger_id,
bar.currency_code,
bar.range_id,
bar.entry_code,
seg_val(acct_seg_index),
ccid,
sysdate,
user_id,
sysdate,
user_id,
login_id
from gl_budget_assignment_ranges bar,
gl_ledgers lgr
where
exists (select 'found' from gl_budorg_bc_options bco
where bar.range_id = bco.range_id)
AND (seg_val(1) IS NULL OR seg_val(1) BETWEEN bar.segment1_low AND
bar.segment1_high)
AND (seg_val(2) IS NULL OR seg_val(2) BETWEEN bar.segment2_low AND
bar.segment2_high)
AND (seg_val(3) IS NULL OR seg_val(3) BETWEEN bar.segment3_low AND
bar.segment3_high)
AND (seg_val(4) IS NULL OR seg_val(4) BETWEEN bar.segment4_low AND
bar.segment4_high)
AND (seg_val(5) IS NULL OR seg_val(5) BETWEEN bar.segment5_low AND
bar.segment5_high)
AND (seg_val(6) IS NULL OR seg_val(6) BETWEEN bar.segment6_low AND
bar.segment6_high)
AND (seg_val(7) IS NULL OR seg_val(7) BETWEEN bar.segment7_low AND
bar.segment7_high)
AND (seg_val(8) IS NULL OR seg_val(8) BETWEEN bar.segment8_low AND
bar.segment8_high)
AND (seg_val(9) IS NULL OR seg_val(9) BETWEEN bar.segment9_low AND
bar.segment9_high)
AND (seg_val(10) IS NULL OR seg_val(10) BETWEEN bar.segment10_low AND
bar.segment10_high)
AND (seg_val(11) IS NULL OR seg_val(11) BETWEEN bar.segment11_low AND
bar.segment11_high)
AND (seg_val(12) IS NULL OR seg_val(12) BETWEEN bar.segment12_low AND
bar.segment12_high)
AND (seg_val(13) IS NULL OR seg_val(13) BETWEEN bar.segment13_low AND
bar.segment13_high)
AND (seg_val(14) IS NULL OR seg_val(14) BETWEEN bar.segment14_low AND
bar.segment14_high)
AND (seg_val(15) IS NULL OR seg_val(15) BETWEEN bar.segment15_low AND
bar.segment15_high)
AND (seg_val(16) IS NULL OR seg_val(16) BETWEEN bar.segment16_low AND
bar.segment16_high)
AND (seg_val(17) IS NULL OR seg_val(17) BETWEEN bar.segment17_low AND
bar.segment17_high)
AND (seg_val(18) IS NULL OR seg_val(18) BETWEEN bar.segment18_low AND
bar.segment18_high)
AND (seg_val(19) IS NULL OR seg_val(19) BETWEEN bar.segment19_low AND
bar.segment19_high)
AND (seg_val(20) IS NULL OR seg_val(20) BETWEEN bar.segment20_low AND
bar.segment20_high)
AND (seg_val(21) IS NULL OR seg_val(21) BETWEEN bar.segment21_low AND
bar.segment21_high)
AND (seg_val(22) IS NULL OR seg_val(22) BETWEEN bar.segment22_low AND
bar.segment22_high)
AND (seg_val(23) IS NULL OR seg_val(23) BETWEEN bar.segment23_low AND
bar.segment23_high)
AND (seg_val(24) IS NULL OR seg_val(24) BETWEEN bar.segment24_low AND
bar.segment24_high)
AND (seg_val(25) IS NULL OR seg_val(25) BETWEEN bar.segment25_low AND
bar.segment25_high)
AND (seg_val(26) IS NULL OR seg_val(26) BETWEEN bar.segment26_low AND
bar.segment26_high)
AND (seg_val(27) IS NULL OR seg_val(27) BETWEEN bar.segment27_low AND
bar.segment27_high)
AND (seg_val(28) IS NULL OR seg_val(28) BETWEEN bar.segment28_low AND
bar.segment28_high)
AND (seg_val(29) IS NULL OR seg_val(29) BETWEEN bar.segment29_low AND
bar.segment29_high)
AND (seg_val(30) IS NULL OR seg_val(30) BETWEEN bar.segment30_low AND
bar.segment30_high)
and bar.currency_code = lgr.currency_code
and bar.ledger_id = lgr.ledger_id
and lgr.enable_budgetary_control_flag = 'Y'
and lgr.chart_of_accounts_id = coaid ;
sql_stmp := 'select ' ||
'smt.template_name, ' ||
'smt.template_id, ' ||
'smt.ledger_id';
'and EXISTS (select ''found'' from ' ||
'gl_summary_bc_options smb where ' ||
'smt.template_id = smb.template_id) ' ;
'or segment' || i || '_type in (select fh.hierarchy_name ' ||
'from gl_code_combinations cc, gl_summary_hierarchies gsh, fnd_flex_values fv, fnd_id_flex_segments fs, ' ||
'fnd_flex_hierarchies_vl fh ' ||
'where cc.code_combination_id = :ccid and ' ||
'cc.segment' || i || ' between gsh.child_flex_value_low and gsh.child_flex_value_high and ' ||
'gsh.flex_value_set_id = fv.flex_value_set_id and ' ||
'gsh.parent_flex_value = fv.flex_value and ' ||
'gsh.flex_value_set_id = fv.flex_value_set_id and ' ||
'fv.enabled_flag = ''Y'' and ' ||
'fs.flex_value_set_id = gsh.flex_value_set_id and ' ||
-- 'sob.chart_of_accounts_id = cc.chart_of_accounts_id and ' ||
'fs.enabled_flag = ''Y'' and ' ||
'cc.chart_of_accounts_id = fs.id_flex_num and ' ||
'fs.application_id = 101 and ' ||
'fs.id_flex_code = ''GL#'' and ' ||
'fs.application_column_name = ''SEGMENT' || i || ''' and ' ||
'fv.flex_value_set_id = fh.flex_value_set_id and ' ||
'fv.structured_hierarchy_level = fh.hierarchy_id)) ';
select gl_dynamic_summ_combinations_s.NEXTVAL
from sys.dual;
select hierarchy_id
from fnd_flex_hierarchies_vl
where flex_value_set_id = vsid
and hierarchy_name = hname;
| The logic used is to create a PL/SQL table and dump the values to be inserted |
| in this table. Later use the values from this table for inserting data. |
| The original design had a cartesian join and hence the same is implemented in this |
| change. The logic is implemented using 3 procedures initialize_values, assign_values |
| and create_duplicates. Parameters passed to these procedures are as below |
| |
| p_row -> Row Number to be updated |
| p_segment -> Segment to be be updated (eg. segment1, segment2 etc.) |
| p_val -> Value to be updated |
| p_status_code -> This is used to set the ledger_id |
| |
| We go on assigning the value to the PL/SQL table until we find multiple parents |
| For the second parent we create duplicate rows and assign the new value to the |
| duplicated rows. This is the way cartesian is established. |
| Finally we insert all rows in the table and later delete the duplicate ones |
| Earlier since DML was used we could make use of distinct clause but now since we are |
| inserting via PL/SQL table we cant make use of that clause. Hence we insert all rows |
| and later delete the duplicate ones |
+======================================================================================*/
FUNCTION glfcpc(seg_type IN SegTypeArray,
rgroup IN SegRgrpArray,
rgroup_sorted IN RgrpSrtArray,
rgroup_ind IN RgrpIndArray,
val_set IN SegVsetArray,
template_id IN NUMBER,
lgr_id IN NUMBER) RETURN BOOLEAN IS
-- Create a PL/SQL table which will have the same structure as that of the table
-- it will update
Type GDSC_Table IS TABLE OF GL_DYNAMIC_SUMM_COMBINATIONS%ROWTYPE;
SELECT DISTINCT gsh.status_code, fv.flex_value
FROM gl_summary_hierarchies gsh, fnd_flex_values fv
WHERE gsh.flex_value_set_id = fv.flex_value_set_id
AND gsh.parent_flex_value = fv.flex_value
AND (seg_val(rgroup_ind(i)) between gsh.child_flex_value_low
and gsh.child_flex_value_high)
AND gsh.flex_value_set_id = val_set(rgroup_ind(i))
AND fv.flex_value_set_id = val_set(rgroup_ind(i))
AND fv.structured_hierarchy_level = rgroup(rgroup_ind(i))
AND fv.enabled_flag = 'Y';
psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_dynamic_summ_combinations');
insert into gl_dynamic_summ_combinations (
dynamic_group_id,
code_combination_id,
last_update_date,
last_updated_by,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
ledger_id,
template_id
)
VALUES (
dyn_grp_id,
-1,
sysdate,
user_id,
GDSC_Type(i).SEGMENT1,
GDSC_Type(i).SEGMENT2,
GDSC_Type(i).SEGMENT3,
GDSC_Type(i).SEGMENT4,
GDSC_Type(i).SEGMENT5,
GDSC_Type(i).SEGMENT6,
GDSC_Type(i).SEGMENT7,
GDSC_Type(i).SEGMENT8,
GDSC_Type(i).SEGMENT9,
GDSC_Type(i).SEGMENT10,
GDSC_Type(i).SEGMENT11,
GDSC_Type(i).SEGMENT12,
GDSC_Type(i).SEGMENT13,
GDSC_Type(i).SEGMENT14,
GDSC_Type(i).SEGMENT15,
GDSC_Type(i).SEGMENT16,
GDSC_Type(i).SEGMENT17,
GDSC_Type(i).SEGMENT18,
GDSC_Type(i).SEGMENT19,
GDSC_Type(i).SEGMENT20,
GDSC_Type(i).SEGMENT21,
GDSC_Type(i).SEGMENT22,
GDSC_Type(i).SEGMENT23,
GDSC_Type(i).SEGMENT24,
GDSC_Type(i).SEGMENT25,
GDSC_Type(i).SEGMENT26,
GDSC_Type(i).SEGMENT27,
GDSC_Type(i).SEGMENT28,
GDSC_Type(i).SEGMENT29,
GDSC_Type(i).SEGMENT30,
DECODE(GDSC_Type(i).LEDGER_ID, NULL, lgr_id, GDSC_Type(i).LEDGER_ID),
DECODE(GDSC_Type(i).TEMPLATE_ID, NULL, template_id, GDSC_Type(i).TEMPLATE_ID)
);
psa_utils.debug_other_string(g_state_level,l_full_path,' DELETEING DUPLICATE ROWS.');
DELETE FROM gl_dynamic_summ_combinations
WHERE rowid NOT IN (SELECT min(rowid)
FROM gl_dynamic_summ_combinations
WHERE dynamic_group_id = dyn_grp_id
GROUP BY dynamic_group_id,
ledger_id,
template_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30)
and dynamic_group_id = dyn_grp_id;
' DELETE FROM gl_dynamic_summ_combinations -> ' || SQL%ROWCOUNT);
FOR crec in (SELECT abs(ledger_id) ledger_id,
abs(template_id) template_id,
segment1, segment2, segment3,
segment4, segment5, segment6,
segment7, segment8, segment9,
segment10, segment11, segment12,
segment13, segment14, segment15,
segment16, segment17, segment18,
segment19, segment20, segment21,
segment22, segment23, segment24,
segment25, segment26, segment27,
segment28, segment29, segment30
FROM gl_dynamic_summ_combinations
WHERE dynamic_group_id = dyn_grp_id
GROUP BY dynamic_group_id,
abs(ledger_id),
abs(template_id),
segment1, segment2, segment3,
segment4, segment5, segment6,
segment7, segment8, segment9,
segment10, segment11, segment12,
segment13, segment14, segment15,
segment16, segment17, segment18,
segment19, segment20, segment21,
segment22, segment23, segment24,
segment25, segment26, segment27,
segment28, segment29, segment30
HAVING count(*) > 1)
LOOP
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,' Inside crec cursor');
DELETE from gl_dynamic_summ_combinations
WHERE ledger_id = -1 * crec.ledger_id
AND template_id = -1 * crec.template_id
AND dynamic_group_id = dyn_grp_id
AND nvl(segment1,g_segment_nvl_value) = nvl(crec.segment1,g_segment_nvl_value)
AND nvl(segment2,g_segment_nvl_value) = nvl(crec.segment2,g_segment_nvl_value)
AND nvl(segment3,g_segment_nvl_value) = nvl(crec.segment3,g_segment_nvl_value)
AND nvl(segment4,g_segment_nvl_value) = nvl(crec.segment4,g_segment_nvl_value)
AND nvl(segment5,g_segment_nvl_value) = nvl(crec.segment5,g_segment_nvl_value)
AND nvl(segment6,g_segment_nvl_value) = nvl(crec.segment6,g_segment_nvl_value)
AND nvl(segment7,g_segment_nvl_value) = nvl(crec.segment7,g_segment_nvl_value)
AND nvl(segment8,g_segment_nvl_value) = nvl(crec.segment8,g_segment_nvl_value)
AND nvl(segment9,g_segment_nvl_value) = nvl(crec.segment9,g_segment_nvl_value)
AND nvl(segment10,g_segment_nvl_value) = nvl(crec.segment10,g_segment_nvl_value)
AND nvl(segment11,g_segment_nvl_value) = nvl(crec.segment11,g_segment_nvl_value)
AND nvl(segment12,g_segment_nvl_value) = nvl(crec.segment12,g_segment_nvl_value)
AND nvl(segment13,g_segment_nvl_value) = nvl(crec.segment13,g_segment_nvl_value)
AND nvl(segment14,g_segment_nvl_value) = nvl(crec.segment14,g_segment_nvl_value)
AND nvl(segment15,g_segment_nvl_value) = nvl(crec.segment15,g_segment_nvl_value)
AND nvl(segment16,g_segment_nvl_value) = nvl(crec.segment16,g_segment_nvl_value)
AND nvl(segment17,g_segment_nvl_value) = nvl(crec.segment17,g_segment_nvl_value)
AND nvl(segment18,g_segment_nvl_value) = nvl(crec.segment18,g_segment_nvl_value)
AND nvl(segment19,g_segment_nvl_value) = nvl(crec.segment19,g_segment_nvl_value)
AND nvl(segment20,g_segment_nvl_value) = nvl(crec.segment20,g_segment_nvl_value)
AND nvl(segment21,g_segment_nvl_value) = nvl(crec.segment21,g_segment_nvl_value)
AND nvl(segment22,g_segment_nvl_value) = nvl(crec.segment22,g_segment_nvl_value)
AND nvl(segment23,g_segment_nvl_value) = nvl(crec.segment23,g_segment_nvl_value)
AND nvl(segment24,g_segment_nvl_value) = nvl(crec.segment24,g_segment_nvl_value)
AND nvl(segment25,g_segment_nvl_value) = nvl(crec.segment25,g_segment_nvl_value)
AND nvl(segment26,g_segment_nvl_value) = nvl(crec.segment26,g_segment_nvl_value)
AND nvl(segment27,g_segment_nvl_value) = nvl(crec.segment27,g_segment_nvl_value)
AND nvl(segment28,g_segment_nvl_value) = nvl(crec.segment28,g_segment_nvl_value)
AND nvl(segment29,g_segment_nvl_value) = nvl(crec.segment29,g_segment_nvl_value)
AND nvl(segment30,g_segment_nvl_value) = nvl(crec.segment30,g_segment_nvl_value);
' DELETE FROM gl_dynamic_summ_combinations II -> ' || SQL%ROWCOUNT);
message_token('SQLSTR', 'INSERT INTO GL_DYNAMIC_SUMM_COMBINATIONS ...');
select 'Obtain Row Share Lock on the ' ||
'corresponding record of this template in ' ||
'gl_concurrency_control'
from gl_concurrency_control ct
where ct.concurrency_class = 'INSERT_SUMMARY_ACCOUNTS'
and ct.concurrency_entity_name = 'SUMMARY_TEMPLATE'
and exists (
select 1
from gl_dynamic_summ_combinations dsc
where to_char(abs(dsc.template_id)) = ct.concurrency_entity_id
and dsc.dynamic_group_id = dyn_grp_id
)
FOR UPDATE;
sql_statement := 'update gl_dynamic_summ_combinations tc1 ' ||
'set code_combination_id = (' ||
'select nvl(cc.code_combination_id, -1) ' ||
'from gl_code_combinations cc, ' ||
'gl_dynamic_summ_combinations tc2 ' ||
'where cc.template_id(+) = abs(tc1.template_id) ' ||
'and cc.chart_of_accounts_id(+) = ' || coaid || ' ';
select gl_code_combinations_s.NEXTVAL
from sys.dual;
update gl_dynamic_summ_combinations
set code_combination_id = gl_code_combinations_s.NEXTVAL
where code_combination_id = -1
and dynamic_group_id = dyn_grp_id;
' update gl_dynamic_summ_combinations -' || SQL%ROWCOUNT);
sql_inscc := 'insert into gl_code_combinations (' ||
'code_combination_id, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'chart_of_accounts_id, ' ||
'detail_posting_allowed_flag, ' ||
'detail_budgeting_allowed_flag, ' ||
'account_type, ' ||
'enabled_flag, ' ||
'summary_flag, ' ||
'template_id, ' ||
'allocation_create_flag, ' ||
'start_date_active, ' ||
'end_date_active';
'select ' ||
'code_combination_id, ' ||
'sysdate, ' ||
':user_id, ' ||
':coaid, ' ||
'''N'', ' ||
'''N'', ' ||
'''O'', ' ||
'''Y'', ' ||
'''Y'', ' ||
'abs(template_id), ' ||
'''Y'', ' ||
'null, ' ||
'null';
' Calling GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types');
GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types(coaid, min_ccid);
' After GL_SUMMARY_ACCOUNT_TYPES_PKG.update_account_types');
sql_acchy := 'insert into gl_account_hierarchies (' ||
'ledger_id, ' ||
'summary_code_combination_id, ' ||
'detail_code_combination_id, ' ||
'template_id, ' ||
'last_updated_by, ' ||
'last_update_date, ' ||
'ordering_value) ';
'select ledger_id, ' ||
'code_combination_id, ' ||
':ccid, ' ||
'template_id, ' ||
':user_id, ' ||
'sysdate, ' ||
':ordering_value ' ||
'from gl_dynamic_summ_combinations dsc ' ||
'where dsc.dynamic_group_id = :grp_id ' ||
'and not exists (' ||
'select 1 ' ||
'from gl_account_hierarchies ah ' ||
'where ah.summary_code_combination_id = ' ||
'dsc.code_combination_id ' ||
'and ah.detail_code_combination_id = :ccid)';
select code_combination_id ccid
from gl_dynamic_summ_combinations
where dynamic_group_id = grp_id
and code_combination_id >= min_ccid;
select attr.flex_value_set_id vsid,
nvl(attr.attribute_num, '') attr_name,
attr.table_id table_id,
nvl(attr.application_column_name, '') col_name,
nvl(attr.segment_name, '') seg_name,
attr.segment_num seg_num,
nvl(attr.attr_segment_name, '') aseg_name,
nvl(valset.validation_type, '') vtype,
valset.parent_flex_value_set_id parent_vsid
from fnd_seg_rpt_attributes attr,
fnd_flex_value_sets valset
where attr.application_id = 101
and valset.flex_value_set_id = attr.flex_value_set_id
and attr.id_flex_num = flex_num
order by attr.segment_num;
update_cl VARCHAR2(10000);
update_cl := 'UPDATE gl_code_combinations glcc SET ';
update_cl := update_cl ||
c_RptAttr.aseg_name ||
' = (select ' || c_RptAttr.attr_name ||
' from fnd_flex_values ffval '||
' where ffval.flex_value_set_id = ' || c_RptAttr.vsid ||
' and enabled_flag = ''Y''' ||
' and ffval.flex_value = glcc.' || c_RptAttr.seg_name;
update_cl := update_cl ||
' and parent_flex_value_low = glcc.'|| parentval;
update_cl := update_cl || '), ';
select user_table_name
into vs_table_name
from fnd_tables
where application_id = 101
and table_id = c_RptAttr.table_id;
select value_column_name
into vs_column_name
from fnd_flex_validation_tables
where flex_value_set_id = c_RptAttr.vsid;
update_cl := update_cl ||
c_RptAttr.aseg_name ||
' = ( select ' || c_RptAttr.col_name ||
' from ' || vs_table_name ||
' where ' || vs_column_name || ' = glcc.' ||
c_RptAttr.seg_name || ' ), ';
update_cl := update_cl ||
'last_update_date = sysdate, ' ||
'last_updated_by = ' || user_id ||
' where glcc.code_combination_id = ' || ccid;
psa_utils.debug_other_string(g_state_level,l_full_path,' update_cl -> ' || SUBSTR(update_cl,1,3000));
psa_utils.debug_other_string(g_state_level,l_full_path,' update_cl -> ' || SUBSTR(update_cl,3000,6000));
if dsql_execute(update_cl) < 0 then
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN -> FALSE');