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; |
