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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
create or replace procedure SUIVI_CCO_GENERE_ERREUR(P_NUM_PAN IN VARCHAR2,
P_NUM_CLIENT IN VARCHAR2,
P_SCA_ID IN NUMBER,
P_DATE_DEBUT IN VARCHAR2,
P_DATE_FIN IN VARCHAR2,
P_FORCE_TRAITEMENT IN NUMBER,
P_CODE_RETOUR OUT NUMBER,
P_MSG_RETOUR OUT VARCHAR2)
IS
/********************************************************************************
NAME: SUIVI_CCO_GENERE_ERREUR
PURPOSE: Calcule et sauvegarde les champs utiles au rapport de CCO en erreur
REVISIONS: 10/10/2008, ALI, Mantis 10304 : création
PARAMETERS : P_NUM_PAN le numero de badge (null si tous les PANS)
P_NUM_CLIENT le numéro de client (null si global)
P_SCA_ID l'identifiant de la SCA (null si toutes les SCA)
P_DATE_DEBUT la date de debut du rapport
P_DATE_FIN la date de fin du rapport
P_FORCE_TRAITEMENT indicateur permettant de forcer le traitement meme si le rapport existe deja
P_CODE_RETOUR le code erreur ou l'identifiant du rapport
P_MSG le message de traitements
NOTES :
******************************************************************************/
l_scer_id NUMBER(19);
l_req VARCHAR2(5);
l_date_debut DATE;
l_date_fin DATE;
l_num_client CLIENT.CLI_NUMERO_CLIENT%TYPE;
l_num_PAN PORTEUR.POR_NUMERO_PORTEUR%TYPE;
l_traitement BOOLEAN;
BEGIN
l_traitement := false;
-- Vérification des dates
BEGIN
l_req := 'Req1';
SELECT to_date(P_DATE_DEBUT, 'dd/MM/YYYY')
INTO l_date_debut
FROM dual;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, 'le format de la date de debut n''est pas reconnu');
END;
BEGIN
l_req := 'Req2';
SELECT to_date(P_DATE_FIN, 'dd/MM/YYYY')
INTO l_date_fin
FROM dual;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, 'le format de la date de fin n''est pas reconnu');
END;
-- Recherche de l'existence du rapport ou d'un rapport moins filtré pouvant etre utilisé
l_req := 'Req3';
BEGIN
SELECT scer_id INTO l_scer_id
FROM (
SELECT first_value(scer_id) OVER (PARTITION BY CLI_NUMERO_CLIENT, SCA_ID, POR_NUMERO_PORTEUR, SCER_DAT_DEBUT, SCER_DAT_FIN ORDER BY SCER_DAT_DEMANDE DESC) scer_id
FROM SUIVI_CCO_ERREUR
WHERE (CLI_NUMERO_CLIENT IS NULL OR CLI_NUMERO_CLIENT = P_NUM_CLIENT)
AND (por_numero_porteur IS NULL OR por_numero_porteur = P_NUM_PAN)
AND (SCA_ID IS NULL OR SCA_ID = P_SCA_ID)
AND SCER_DAT_DEBUT = l_date_debut
AND SCER_DAT_FIN = l_date_fin
)
WHERE rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_traitement := true;
END;
IF l_traitement OR P_FORCE_TRAITEMENT = 1 THEN
l_req := 'Req4';
SELECT SQ_SCER.NEXTVAL INTO l_scer_id FROM dual;
l_req := 'Req5';
-- Vérification du numéro client
IF P_NUM_CLIENT IS NOT NULL THEN
BEGIN
SELECT cli_numero_client
INTO l_num_client
FROM client
WHERE cli_numero_client = P_NUM_CLIENT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20002, 'le numéro client n''est pas reconnu');
END;
END IF;
-- Vérification du PAN
IF P_NUM_PAN IS NOT NULL THEN
BEGIN
SELECT po.por_numero_porteur
INTO l_num_PAN
FROM PORTEUR po
WHERE po.por_numero_porteur = P_NUM_PAN;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20002, 'le numéro client n''est pas reconnu');
END;
END IF;
-- Insertion de l'entete du rapport
l_req := 'Req6';
INSERT INTO SUIVI_CCO_ERREUR (SCER_ID, CLI_NUMERO_CLIENT, POR_NUMERO_PORTEUR, SCA_ID, SCER_DAT_DEBUT, SCER_DAT_FIN, SCER_DAT_DEMANDE)
VALUES (l_scer_id, P_NUM_CLIENT, P_NUM_PAN, P_SCA_ID, l_date_debut, l_date_fin, sysdate);
commit;
-- Recherche des trajets sans CCO pour un PAN avec CCO
l_req := 'Req7';
INSERT INTO SUIVI_CCO_ERREUR_LIGNE(
SCER_ID,
POR_NUMERO_PORTEUR,
SCA_ID,
CCO_SCA,
CCO_SOUSCRITE,
CCO_TRAJET,
TRA_DHM_E,
TRA_DHM_S,
TRA_CODE_GARE_E,
TRA_CODE_GARE_S,
TRA_ID
)
(SELECT
l_scer_id
,po.por_numero_porteur
,ccc.sca_id
,r17.cod_ccl
,ccc.ccc_code --as CCO_souscrite
,tra.tra_code_prodt_serv --as CCO_trajet
,tra.tra_dhm_e
,tra.tra_dhm_s
,tra.tra_code_gare_e
,tra.tra_code_gare_s
,tra.tra_id
FROM
eurotoll.transaction tra
JOIN contrat ctr on tra.cta_id = ctr.cta_id
JOIN client cli on ctr.cli_id = cli.cli_id
JOIN porteur po on tra.tra_code_porteur = po.por_numero_porteur
JOIN condition_com_selectionnee ccs on ccs.por_id = po.por_id
JOIN valeur_attribut_service vas on vas.csl_id = ccs.csl_id
JOIN condition_com_circulee ccc on vas.ccc_id = ccc.ccc_id
JOIN sca on ccc.sca_id=sca.sca_id
JOIN (SELECT cod_ccl,
sca_circulee,
num_cpt_pan,
ROW_NUMBER() OVER(PARTITION BY sca_circulee, num_cpt_pan ORDER BY dat_deb_ccl desc) rank
FROM europl.r17_contrat_ccial r17) r17 on r17.num_cpt_pan = po.por_numero_porteur and r17.rank = 1 and sca.sca_code=substr(r17.sca_circulee,1,3) || substr(r17.sca_circulee,7,2)
where 1=1
and (P_NUM_PAN IS NULL OR tra.tra_code_porteur=P_NUM_PAN)
and (P_NUM_CLIENT IS NULL OR cli.cli_numero_client=P_NUM_CLIENT)
and (P_SCA_ID IS NULL OR CCC.SCA_ID = P_SCA_ID)
and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') <= to_date(P_DATE_FIN,'DD/MM/YYYY')
and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') >= to_date(P_DATE_DEBUT,'DD/MM/YYYY')
and ((ltrim(tra.tra_code_prodt_serv) is null and ltrim(ccc.ccc_code) is not null)
or (ltrim(tra.tra_code_prodt_serv) is not null and ltrim(ccc.ccc_code) is null))
);
commit;
P_MSG_RETOUR := 'OK';
ELSE
P_MSG_RETOUR := 'OK - Traitement déjà effectué';
END IF;
P_CODE_RETOUR := l_scer_id;
EXCEPTION
WHEN OTHERS THEN
P_MSG_RETOUR := 'Erreur ' || l_req || ' ' || sqlerrm;
P_CODE_RETOUR := sqlcode;
rollback;
end SUIVI_CCO_GENERE_ERREUR; |
