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 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
|
CREATE OR REPLACE PROCEDURE FILL_DUES (in_reference_date IN DATE)
IS
TYPE w_TYPE_ID_AFFAIRE is table of lf_dpd_dues.id_affaire%type;
TYPE w_TYPE_DUE_DATE is table of lf_dpd_dues.due_date%type;
TYPE w_TYPE_TOTAL_DUE is table of lf_dpd_dues.total_due%type;
TYPE w_TYPE_SUM_TOTAL_DUE is table of lf_dpd_dues.sum_total_due%type;
TYPE w_TYPE_AMOUNT_50PERCENT is table of lf_dpd_dues.amount_50_percent%type;
w_ID_AFFAIRE w_TYPE_ID_AFFAIRE;
w_DUE_DATE w_TYPE_DUE_DATE;
w_TOTAL_DUE w_TYPE_TOTAL_DUE;
w_SUM_TOTAL_DUE w_TYPE_SUM_TOTAL_DUE;
w_AMOUNT_50PERCENT w_TYPE_AMOUNT_50PERCENT;
v_query VARCHAR2(10000);
BEGIN
execute immediate 'truncate table lf_dpd_dues';
v_query := '
SELECT due.id_affaire AS id_affaire
, due.due_date AS due_date
, due.total_due AS total_due
, due.sum_total_due AS sum_total_due
, (due.sum_total_due - due.amount_50percent) AS amount_50percent
FROM
(SELECT
id_affaire
, due_date
, total_due
, sum(total_due) over (partition by id_affaire order by due_date) as sum_total_due
, total_due * 0.5 as amount_50percent
FROM
(SELECT
cor.id_affaire AS id_affaire
-- , RB2_FIND_NEXT_BOOKING_DATE(to_date(mvt.date_comptable,''j'')) as due_date
, to_date(mvt.date_comptable,''j'') AS due_date
, SUM(mvt.mt_ttc*s1.signe * -1) AS total_due
FROM
lf_contract_object_retail cor
, mvtnew mvt
, signefac s1
WHERE
mvt.id_affaire = cor.id_affaire
AND mvt.code_statut = ''2''
AND upper(mvt.code_role_tiers) IN (''CLIE'',''RCLI'')
AND upper(mvt.code_operation) IN (''FACE'',''FCCE'')
AND mvt.mt_ttc*s1.signe * -1 > 0
AND to_date(mvt.date_comptable,''j'') <= ''28/02/2009''
AND s1.code_operation = mvt.code_operation
AND s1.code_sens_ni = mvt.code_sens_ni
AND cor.id_affaire > 0
GROUP BY
cor.id_affaire
, to_date(mvt.date_comptable,''j'')
)
) due
';
EXECUTE IMMEDIATE v_query BULK COLLECT INTO w_ID_AFFAIRE, w_DUE_DATE, w_TOTAL_DUE, w_SUM_TOTAL_DUE, w_AMOUNT_50PERCENT;
FORALL i IN w_ID_AFFAIRE.FIRST..w_ID_AFFAIRE.LAST
INSERT INTO lf_dpd_dues
(id_affaire
, due_date
, total_due
, sum_total_due
, amount_50_percent
)
VALUES
(
w_ID_AFFAIRE(i)
, w_DUE_DATE(i)
, w_TOTAL_DUE(i)
, w_SUM_TOTAL_DUE(i)
, w_AMOUNT_50PERCENT(i)
);
COMMIT;
END FILL_DUES; |
Partager