[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_coa_id
and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
select 1 into l_num
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_coa_id
and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID)
and ROWNUM = 1;
procedure update_dimension( p_short_name in varchar2,
p_name in varchar2,
p_description in varchar2,
p_system_enabled_flag in varchar2,
p_dbi_enabled_flag in varchar2,
p_master_value_set_id in number,
p_dbi_hier_top_node in varchar2,
p_dbi_hier_top_node_id in number,
x_status out nocopy varchar2,
x_message_count out nocopy number,
x_error_message out nocopy varchar2) as
begin
if g_debug_flag = 'Y' then
fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(+)');
update FII_FINANCIAL_DIMENSIONS
set system_enabled_flag = p_system_enabled_flag,
dbi_enabled_flag = p_dbi_enabled_flag,
master_value_set_id = p_master_value_set_id,
dbi_hier_top_node = p_dbi_hier_top_node,
dbi_hier_top_node_id = p_dbi_hier_top_node_id
where dimension_short_name = p_short_name;
delete from fii_dim_norm_hierarchy
where child_flex_value_set_id <> parent_flex_value_set_id
and parent_flex_value_set_id not in
( select master_value_set_id
from fii_financial_dimensions_v );
DELETE /*+ index_ffs(fii_dim_norm_hierarchy) */
FROM fii_dim_norm_hierarchy
WHERE child_flex_value_set_id <> parent_flex_value_set_id
AND NOT EXISTS
(
SELECT
MASTER_VALUE_SET_ID
FROM
(
SELECT /*+ NO_MERGE */
DECODE(frd.dimension_short_name, 'ENI_ITEM_VBH_CAT',
ENI_VALUESET_CATEGORY.GET_FLEX_VALUE_SET_ID(401, 'MCAT',
ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID),
frd.master_value_set_id) MASTER_VALUE_SET_ID
FROM fii_financial_dimensions frd
WHERE dimension_short_name is not null
)
WHERE MASTER_VALUE_SET_ID = parent_flex_value_set_id
AND MASTER_VALUE_SET_ID is not null
);
fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(-)');
FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.update_dimension');
fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(EXCEPTION)');
select chart_of_accounts_id from fii_dim_mapping_rules
where dimension_short_name = 'ENI_ITEM_VBH_CAT';
select application_column_name,
flex_value_set_id
into col_name,
vsid
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = r.chart_of_accounts_id
and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
update fii_dim_mapping_rules
set MAPPING_TYPE_CODE = 'S',
application_column_name1 = col_name,
flex_value_set_id1 = vsid
where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
update fii_dim_mapping_rules
set MAPPING_TYPE_CODE = 'R',
application_column_name1 = null,
flex_value_set_id1 = null
where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
update fii_dim_mapping_rules
set MAPPING_TYPE_CODE = 'R',
application_column_name1 = null,
flex_value_set_id1 = null
where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
select ffd.dimension_short_name dimension_short_name,
fdmr.chart_of_accounts_id chart_of_accounts_id,
'O' status_code,
sysdate creation_date,
fnd_global.user_id created_by,
sysdate last_update_date,
fnd_global.user_id last_updated_by,
fnd_global.user_id last_update_login,
'S' mapping_type_code,
null application_column_name1,
null flex_value_set_id1,
null application_column_name2,
null flex_value_set_id2,
null application_column_name3,
null flex_value_set_id3
from fii_financial_dimensions_v ffd,
fii_dim_mapping_rules fdmr
where ffd.dimension_short_name = fdmr.dimension_short_name(+)
and fdmr.chart_of_accounts_id(+) = p_chart_of_accounts_id;
select fsav.application_column_name,
fifs.flex_value_set_id
from fnd_id_flex_segments fifs,
fnd_segment_attribute_values fsav
where fifs.application_id = 101
and fifs.id_flex_code = 'GL#'
and fifs.application_column_name = fsav.application_column_name
and fifs.id_flex_code = fsav.id_flex_code
and fifs.id_flex_num = fsav.id_flex_num
and fsav.attribute_value = 'Y'
and fifs.id_flex_num = p_chart_of_accounts_id
and fsav.segment_attribute_type = p_segment_attribute_type;
select 'X'
from fnd_id_flex_structures
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_chart_of_accounts_id;
select application_column_name,
flex_value_set_id
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_chart_of_accounts_id
and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
select 'x' from dual
where exists (select 'x' from fii_source_ledger_groups x, fii_slg_assignments y
where x.source_ledger_group_id = y.source_ledger_group_id and
y.chart_of_accounts_id = p_chart_of_accounts_id and
x.usage_code='DBI');
delete from fii_dim_mapping_rules
where chart_of_accounts_id = p_chart_of_accounts_id and
not exists(select 'x' from
fii_slg_assignments
where chart_of_accounts_id = p_chart_of_accounts_id);
select 'X' into l_x
from fnd_id_flex_structures
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = p_chart_of_accounts_id
and ROWNUM = 1;
insert into fii_dim_mapping_rules(
DIMENSION_SHORT_NAME,
CHART_OF_ACCOUNTS_ID,
STATUS_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
MAPPING_TYPE_CODE,
APPLICATION_COLUMN_NAME1,
FLEX_VALUE_SET_ID1,
APPLICATION_COLUMN_NAME2,
FLEX_VALUE_SET_ID2,
APPLICATION_COLUMN_NAME3,
FLEX_VALUE_SET_ID3
)
values(
rule_rec.DIMENSION_SHORT_NAME,
p_chart_of_accounts_id,
rule_rec.STATUS_CODE,
rule_rec.CREATION_DATE,
rule_rec.CREATED_BY,
rule_rec.LAST_UPDATE_DATE,
rule_rec.LAST_UPDATED_BY,
rule_rec.LAST_UPDATE_LOGIN,
rule_rec.MAPPING_TYPE_CODE,
rule_rec.APPLICATION_COLUMN_NAME1,
rule_rec.FLEX_VALUE_SET_ID1,
rule_rec.APPLICATION_COLUMN_NAME2,
rule_rec.FLEX_VALUE_SET_ID2,
rule_rec.APPLICATION_COLUMN_NAME3,
rule_rec.FLEX_VALUE_SET_ID3);
| Plsql api to delete je inclusion rules associated with a particular |
| je rule set id. (When slg assignment is deleted, associated |
| je inclusion rules need to be deleted). |
| HISTORY |
| 21-JUL-03 H.Chung Created |
| 05-APR-05 MManasse Bug 4277376: Added update of je_rule_set_id to null in|
| fii_slg_assignments. |
| |
*****************************************************************************/
PROCEDURE DeleteJeInclusionRules(p_je_rule_set_id IN NUMBER,
x_status OUT nocopy VARCHAR2,
x_message_count OUT nocopy NUMBER,
x_error_message OUT nocopy VARCHAR2)
AS
l_msg_count number;
FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules(+)');
DELETE FROM GL_JE_INCLUSION_RULES
WHERE je_rule_set_id = p_je_rule_set_id;
UPDATE FII_SLG_ASSIGNMENTS
SET JE_RULE_SET_ID = NULL WHERE JE_RULE_SET_ID = p_je_rule_set_id;
FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(-)');
FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules');
FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules(EXCEPTION)');