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
| create or replace
PROCEDURE PRC_PDD_CRM_CONTRAT_AWS as
begin
BEGIN
--DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE ('Start at'|| TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
----------------------------------------------------------------------------------------------------------------------------------
-- EXTRACTION/CREATION VUE INTERMEDIAIRE POUR REGROUPEMENT ET AGGREGATION DES MONTANTS DES PM quotidiennes PAR CONTRAT
----------------------------------------------------------------------------------------------------------------------------------
execute immediate 'create or replace view AWM_ADMIN_AIA_DEV_I.CRM_DYNM_CUMUL as
select c.id_contrat,cum_vers_1jan ,cum_vers_aprec,cum_vers_uc_1jan,
case when cum_vers_1jan <> 0 then ROUND (((a.cum_vers_uc_1jan/a.cum_vers_1jan) * 100 ),2) else null end as tx_uc_collecte_brute ,
cum_vers_uc_aprec,
cum_rach_1jan RACHAT,
cum_rach_aprec RACHAT_APREC,
mt_vers_brut_uc mt_prem_verst_uc,
mt_vers_brut mt_prem_verst,
case when cum_vers_1jan = 0 and mt_vers_brut<>0 then mt_vers_brut else null end as mt_vers_brut_aprec,
(cum_vers_1jan-mt_vers_brut) vers_compl ,
cd_ptf_rdu code_portef,
TOP_ANN AN,
TOP_ANN_APREC AN_PREC,
TOP_MANDAT contrat_ss_mandat,
TOP_OPTFIN
from AWM_ADMIN_AIA_DEV_I.PDD_CONTRAT_CALC a
inner join AWM_ADMIN_AIA_DEV_I.pdd_contrat_VI b
on a.id_contrat=b.id_contrat
inner join
AWM_ADMIN_AIA_DEV_I.pdd_contrat c on a.id_contrat=c.id_contrat
where c.lb_status not in (''sans effet'',''annulé'')
group by c.id_contrat,
cum_vers_1jan,(cum_vers_1jan-mt_vers_brut),
cd_ptf_rdu,
TOP_ANN ,
TOP_ANN_APREC,
TOP_MANDAT ,
TOP_OPTFIN,
cum_vers_uc_1jan,
cum_rach_1jan,
cum_rach_aprec,
mt_vers_brut_uc,
cum_vers_aprec,
cum_vers_uc_aprec,
mt_vers_brut;';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('pas de données ds vue CRM_DYNM_CUMUL');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE : ' || SQLCODE) ;
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM) ;
commit;
DBMS_OUTPUT.PUT_LINE ('End at'|| TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
end;
END PRC_PDD_CRM_CONTRAT_AWS; |
Partager