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
| SELECT pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation,
sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact,
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code,
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type,
DECODE( ccp_libelle, 'Temporaire décès à PU' , gad_prime_nette, 0 )
||';'|| DECODE( ccp_libelle, 'Temporaire décès à PP' , gad_prime_nette, 0 )
||';'|| DECODE( ccp_libelle, 'Rentes à PU', gad_prime_nette, 0 )
||';'|| DECODE( ccp_libelle, 'ASSISTANCE - FRAIS MEDICAUX A L ETRANGER' , gad_prime_nette, 0 )
FROM
( SELECT pol_numpol, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin,
sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle, gad_prime_nette,
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code,
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
FROM f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque,
f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire, f_tarif_ass, t_intercalaire,
f_tarif_gar, t_cdecpt
WHERE gad_ptrsorid = sor_ident (+)
AND sor_ptrpolid = pol_ident (+)
AND sor_ident = mvt_ptrsuivantid (+)
AND mvt_ptrverid = ver_ident (+)
AND pol_ptrpasid = pas_ident (+)
AND pas_ident = pri_ptrpasident (+)
AND sor_ident = sar_ptrsorid (+)
AND sar_ptrassid = bpass_ident (+)
AND bpass_ptrbppident = bpp_ident (+)
AND bpp_ptrmarid = mar_ident (+)
AND pol_ptrinid = in_ident (+)
AND sor_datetarif = tas_dateffet (+)
AND tas_codeintercalaire = ta_code (+)
AND ta_cle = tga_cletarif (+)
AND tga_code_compta = ccp_code (+)
AND (pol_datresil IS NULL OR pol_datresil >= (SYSDATE - TO_YMINTERVAL('03-00')))
GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation,
sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact,
gad_prime_nette, ccp_libelle,
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code,
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
)
GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation,
sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact,
pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code,
mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type
ORDER BY pol_numpol ASC, ver_compteur ASC |
