The following lines contain the word 'select', 'insert', 'update' or 'delete':
line_select VARCHAR2(2000); -- Buffer for line select dynamic sql
ccid_select VARCHAR2(4500); -- Buffer for flex select dynamic sql
PROCEDURE build_selects(coa_id NUMBER,
calculation_level VARCHAR2) IS
flexwherebuf VARCHAR2(1500); -- Holds the flexfield portion of the
flexselectbuf VARCHAR2(2000); -- Holds the flexfield portion of the
-- select clause
segcount NUMBER; -- Number of segments in flexfield
SELECT count(*)
INTO segcount
FROM fnd_id_flex_segments
WHERE enabled_flag = 'Y'
AND id_flex_num = coa_id
AND application_id = 101
AND id_flex_code = 'GL#';
flexselectbuf := '';
flexselectbuf := flexselectbuf ||
'replace(:bal_seg_val, ''' ||
delim || ''', ''
'') ';
flexselectbuf := flexselectbuf ||
'replace(tax_cc.' || appcol_name || ', ''' ||
delim || ''', ''
'') ';
flexselectbuf := flexselectbuf || ' || ''' || delim || ''' || ';
line_select := 'SELECT jel.je_line_num, jel.effective_date, ' ||
'jel.entered_dr, jel.entered_cr, ' ||
'jel.tax_rounding_rule_code, jel.description, ' ||
'jel.amount_includes_tax_flag, ' ||
'jel.tax_type_code, jel.tax_code_id, ' ||
'cc.'||bal_seg_appcol||' ' ||
'FROM gl_je_lines jel, ' ||
'gl_code_combinations cc ' ||
'WHERE jel.je_header_id = :header ' ||
'AND jel.taxable_line_flag = ''Y'' ' ||
'AND cc.code_combination_id = jel.code_combination_id ';
line_select := line_select ||
'ORDER BY jel.je_line_num ';
line_select := line_select ||
'ORDER BY jel.tax_type_code, jel.tax_code_id, ' ||
'jel.tax_rounding_rule_code, ' ||
'jel.amount_includes_tax_flag, ' ||
'cc.' || bal_seg_appcol || ', ' ||
'decode(jel.entered_dr, NULL, 1, 0), ' ||
'decode(jel.entered_cr, NULL, 1, 0), ' ||
'greatest(nvl(jel.entered_dr, 0), ' ||
'nvl(jel.entered_cr, 0)), ' ||
'jel.je_line_num ';
ccid_select := 'SELECT new_cc.code_combination_id, ' ||
flexselectbuf ||
'FROM gl_code_combinations tax_cc, ' ||
'gl_code_combinations new_cc ' ||
'WHERE tax_cc.code_combination_id = :tax_ccid ' ||
'AND tax_cc.chart_of_accounts_id = :coa_id ' ||
flexwherebuf ||
'AND new_cc.template_id(+) IS NULL ' ||
'AND new_cc.chart_of_accounts_id(+) = :coa_id ' ||
'AND new_cc.enabled_flag(+) = ''Y'' ' ||
'AND new_cc.detail_posting_allowed_flag(+) = ''Y'' ' ||
'AND trunc(:eff_date) ' ||
'between trunc(nvl(new_cc.start_date_active(+),' ||
':eff_date - 1)) ' ||
'and trunc(nvl(new_cc.end_date_active(+), ' ||
':eff_date + 1))';
END build_selects;
build_selects(coa_id => coa_id,
calculation_level => calculation_level);
dbms_sql.parse(lines_cursor, line_select, dbms_sql.v7);
dbms_sql.parse(ccid_cursor, ccid_select, dbms_sql.v7);
SELECT gl_je_lines_s.nextval
INTO tax_group
FROM sys.dual;
SELECT gl_je_lines_s.nextval
INTO next_tax_group
FROM sys.dual;