declare
type t_cursor is ref cursor;
v_segment_cursor t_cursor;
v_segment_cursor2 t_cursor;
strsql varchar2(5000);
strsql2 varchar2(5000);
type t_record is record(
segmentx varchar2(24),
chart_of_accounts_id number);
type t_record2 is record(
flex_value_set_name varchar2(60),
segmentx varchar2(25),
description varchar2(1000));
v_value t_record;
v_value2 t_record2;
begin
for i in 1 .. 9 loop
strsql := 'select distinct gcc.segment' || i ||
',gcc.chart_of_accounts_id
from fnd_lookup_values_vl flv,
gl_code_combinations gcc,
gl_ledgers gl
where flv.lookup_type = ''MERGERING OU''
and flv.enabled_flag = ''Y''
and nvl(flv.end_date_active, trunc(sysdate) + 1) >
trunc(sysdate)
and flv.meaning = gcc.segment1
and gcc.chart_of_accounts_id = gl.chart_of_accounts_id
and gl.name = ''KBD_CNY_LEDGER''
AND gcc.TEMPLATE_ID is null
and exists
(select ''1''
from gl_balances gb
where gb.actual_flag = ''A''
and gb.code_combination_id = gcc.code_combination_id
and gb.period_name = ''2016-04''
and (gb.period_net_dr <> 0 or gb.period_net_cr <> 0))';
open v_segment_cursor for strsql;
loop
fetch v_segment_cursor
into v_value;
exit when v_segment_cursor%notfound;
--dbms_output.put_line(v_value.segmentx);
strsql2 := 'select fv.FLEX_VALUE_SET_NAME,' || '''' ||
v_value.segmentx || '''' ||
' segmentx
,gl_flexfields_pkg.get_description_sql(' ||
v_value.chart_of_accounts_id || ',' || i || ',' || '''' ||
v_value.segmentx || '''' ||
') description
from FND_ID_FLEX_SEGMENTS fi,
FND_FLEX_VALUE_SETS fv
where fi.id_flex_num = ' ||
v_value.chart_of_accounts_id || '
and (fi.ID_FLEX_CODE = ''GL#'')
and (fi.APPLICATION_ID = 101)
and fi.ENABLED_FLAG = ''Y''
and fi.flex_value_set_id = fv.flex_value_set_id
and fi.APPLICATION_COLUMN_NAME = ' ||
'''SEGMENT' || i || '''' || ' and not exists (select ''1''
from gl_cons_flex_hierarchies gcr,
gl_cons_segment_map gcs,
gl_consolidation_v gc
where gcr.segment_map_id = gcs.segment_map_id
and gcs.coa_mapping_id = gc.mapping_id
and gc.from_ledger_name = ''KBD_CNY_LEDGER''
and gc.to_ledger_name = ''TEST_IFRS_DEC''
and ' || '''' || v_value.segmentx || '''' ||
' between gcr.child_flex_value_low and gcr.child_flex_value_high
and gcs.from_application_column_name = ' ||
'''SEGMENT' || i || ''')';
--dbms_output.put_line(strsql2);
open v_segment_cursor2 for strsql2;
loop
fetch v_segment_cursor2
into v_value2;
exit when v_segment_cursor2%notfound;
--
dbms_output.put_line(v_value2.flex_value_set_name || ',' ||
v_value2.segmentx || ',' ||
v_value2.description);
end loop;
close v_segment_cursor2;
end loop;
close v_segment_cursor;
end loop;
end;
使用动态游标验证总账科目表映射
最新推荐文章于 2021-12-15 13:56:26 发布