declare
g_commit_count number; cursor cu1 is select gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 1, --1 gcc.segment1) company_name, p_com_code company_code, gl.name, gl.description sob_desc, ps.period_year, ps.period_name, to_char(jeh.default_effective_date, 'yyyy-mm-dd') creation_date, to_char(jeh.posted_date, 'yyyy-mm-dd') posted_date, jec.user_je_category_name, jeh.doc_sequence_value, jel.je_line_num, decode(nvl(jel.accounted_dr, 0), 0, null, gcc.segment2) dr_acc, decode(nvl(jel.accounted_cr, 0), 0, null, gcc.segment2) cr_acc, jel.description, jel.accounted_dr, jel.accounted_cr, jeh.attribute3, null fa_num, null po_num, jeh.currency_code from gl_je_headers jeh, gl_je_lines jel, gl_code_combinations gcc, gl_je_sources_vl src, gl_je_categories jec, gl_period_statuses ps, gl_ledgers gl WHERE jeh.je_header_id = jel.je_header_id AND jeh.je_source = src.je_source_name AND jeh.je_category = jec.JE_CATEGORY_name AND jel.code_combination_id = gcc.code_combination_id AND jeh.ledger_id = gl.ledger_id AND src.language = userenv('LANG') AND jec.language = userenv('LANG') AND jeh.actual_flag = 'A' AND jeh.status = 'P' AND ps.application_id = 101 AND jel.ledger_id = ps.ledger_id AND ps.period_name = jeh.period_name AND (nvl(jel.accounted_dr, 0) <> 0 OR nvl(jel.accounted_cr, 0) <> 0) AND jeh.ledger_id = p_sob_id --and jeh.je_header_id=13704942-- AND ps.period_name between nvl(p_period_f, ps.period_name) and nvl(p_period_t, to_char(sysdate, 'YYYY-MM')) --AND (jeh.default_effective_date >=to_date(P_START_DATE,'yyyy-mm-dd hh24:mi:ss') or P_START_DATE is null) -- AND (jeh.default_effective_date <=to_date(P_END_DATE,'yyyy-mm-dd hh24:mi:ss') or P_END_DATE is null) ; --order by ps.period_name, jeh.je_header_id, jel.je_line_num; TYPE type_je_detail IS TABLE OF cux_glje_detail_tmp%ROWTYPE INDEX BY BINARY_INTEGER; t_je_detail type_je_detail ; BEGIN g_commit_count := 5000; OPEN cu1; LOOP FETCH cu1 BULK COLLECT INTO t_je_detail LIMIT g_commit_count; FORALL i IN 1 .. t_je_detail .count INSERT /* +APPEND */ INTO cux_glje_detail_tmp VALUES ( t_je_detail (i).company_name t_je_detail (i).company_code, --中间省略 t_je_detail (i).币currency_code); COMMIT; EXIT WHEN cu1%NOTFOUND; END LOOP; CLOSE cu1;
