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
|
var r refcursor
declare joblist varchar2(4000);
begin
SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle)
INTO joblist FROM (SELECT DISTINCT substr(ccp_libelle,1,30) as libelle FROM t_cdecpt);
open :r FOR 'SELECT *
FROM (select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, substr(ccp_libelle,1,30) as 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 gad_code = tga_code
and tga_genre = ''GAR''
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval(''03-00'')))
and ((sor_datedebut != sor_datefin) or (sor_datefin is null and sor_datedebut != pol_datresil)
or (sor_datefin is null and pol_datresil is null and sor_datedebut != pol_datechpro))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, substr(ccp_libelle,1,30), 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 )
pivot (sum(gad_prime_nette) for (libelle) in ('||joblist||'))
order by pol_numpol asc, ver_compteur asc, sor_ident asc';
end;
/
print r |
Partager