1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| CREATE OR REPLACE PROCEDURE
remplissage_t_bt (p_base VARCHAR,
p_mois NUMBER,
p_annee NUMBER)
IS
v_requette VARCHAR2(2000);
BEGIN
v_requette:= 'insert into TABLE_TEMP '
|| '(t_date, t_mois, t_expl, T_EXPL_LIB, t_mnt_bt) '
|| 'SELECT JR_DATE, to_char(JR_DATE,''MM/YYYY'') mois, EXPL, LIBELLE, sum(MT_JRB) + sum(MT_JRC) T_MNT_BT '
|| 'FROM '
|| '(SELECT JC_EXPL# EXPL, expl_libelle LIBELLE, '
|| 'to_date(to_char(JC_DATE#,''DD/MM/YYYY''),''DD/MM/YYYY'') JR_DATE, '
|| 'SUM(JC_MONTANT) MT_JRC, 0 MT_JRB '
|| 'FROM t_jc@'
||p_base
||', t_expl@'
||p_base
|| ' WHERE to_char(to_date(JC_DATE#,''DD/MM/YYYY''),''MM/YYYY'') '
|| '= to_char(to_date(to_char('
||p_mois
||')||''/''||to_char('
||p_annee
||'),''MM/YYYY''),''MM/YYYY'') '
|| 'AND JC_EVT# IN (9,10) '
|| 'AND t_jc.JC_EXPL# = t_expl.expl_code# '
|| 'group by JC_EXPL#,expl_libelle, to_date(to_char(JC_DATE#,''DD/MM/YYYY''),''DD/MM/YYYY'') '
|| 'UNION '
|| 'SELECT JB_EXPL# EXPL, expl_libelle LIBELLE, '
|| 'to_date(to_char(JB_DATE#,''DD/MM/YYYY''),''DD/MM/YYYY'') JR_DATE, 0 MT_JRC, '
|| 'SUM(JB_MONTANT) MT_JRB '
|| 'FROM t_jb@'
||p_base
||', t_expl@'
||p_base
|| ' WHERE to_char(to_date(JB_DATE#,''DD/MM/YYYY''),''MM/YYYY'') '
|| '= to_char(to_date(to_char('
||p_mois
||')||''/''||to_char('
||p_annee
||'),''MM/YYYY''),''MM/YYYY'') '
|| ' AND JB_EVT# IN (9,10) '
|| ' AND t_jb.JB_EXPL# = t_expl.expl_code# '
|| ' group by JB_EXPL#, expl_libelle, to_date(to_char(JB_DATE#,''DD/MM/YYYY''),''DD/MM/YYYY'')) '
|| 'GROUP BY JR_DATE, to_char(JR_DATE,''MM/YYYY''), EXPL, LIBELLE order by 1,2,3';
EXECUTE immediate v_requette ;
END; |
Partager