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
|
CREATE OR REPLACE PROCEDURE EXTRACT_PLJ_PIECES IS
tmpVar NUMBER;
cursor c_dossier is
SELECT
SO.DOSSIER.COD_DOSSIER,
SO.DOSSIER.LIB_DOSSIER,
ASTRE.AIDE.LIB_AIDE,
SO.DOSSIER.COD_AIDE,
ASTRE.TIERS_DONGEN.LIC_NOM_ENREG,
ASTRE.TIERS_DONGEN.LIC_RAIS1,
ASTRE.TIERS_ADR.ADR_RUE,
ASTRE.TIERS_ADR.ADR_COMPVOIE,
ASTRE.TIERS_ADR.ADR_COD_CODPOS,
ASTRE.TIERS_ADR.ADR_BURDIS,
ASTRE.CIVILITE.LIB_CIV,
ASTRE.TIERS_DONGEN.COD_CIV
FROM
SO.DOSSIER@stat_gfprod,
ASTRE.AIDE@stat_gfprod,
ASTRE.TIERS_DONGEN@stat_gfprod,
ASTRE.TIERS_ADR@stat_gfprod,
ASTRE.CIVILITE@stat_gfprod,
ASTRE.DOSS_TIERS@stat_gfprod,
ASTRE.LOT@stat_gfprod,
ASTRE.LOT_DOSSIER@stat_gfprod
WHERE
( ASTRE.TIERS_DONGEN.COD_CIV=ASTRE.CIVILITE.COD_CIV(+) )
AND ( ASTRE.DOSS_TIERS.TIERS_ID=ASTRE.TIERS_DONGEN.TIERS_ID(+) )
AND ( ASTRE.LOT_DOSSIER.COD_DOSSIER(+)=SO.DOSSIER.COD_DOSSIER )
AND ( ASTRE.LOT.COD_LOT(+)=ASTRE.LOT_DOSSIER.COD_LOT )
AND ( SO.DOSSIER.COD_AIDE=ASTRE.AIDE.COD_AIDE(+) )
AND ( SO.DOSSIER.COD_DOSSIER=ASTRE.DOSS_TIERS.COD_DOSSIER(+) )
AND ( ASTRE.TIERS_DONGEN.TIERS_ID=ASTRE.TIERS_ADR.TIERS_ID(+) )
AND (
( SO.DOSSIER.COD_AIDE IN ('1201','1202','1203','1204'))
AND ( ASTRE.DOSS_TIERS.COD_ROLE='BENEF' )
AND ASTRE.TIERS_ADR.IND_PRINC = 1
AND ASTRE.LOT.COD_LOT LIKE '2007%264'
)
;
cursor c_pieces (p_num_dossier in char) is
SELECT
SO.DOSSIER.COD_DOSSIER,
ASTRE.PIECE.LIB_PIECE
FROM
SO.DOSSIER@stat_gfprod,
ASTRE.PIECE@stat_gfprod,
ASTRE.DOSS_PIECE@stat_gfprod
WHERE
( ASTRE.DOSS_PIECE.COD_PIECE=ASTRE.PIECE.COD_PIECE(+) )
AND ( SO.DOSSIER.COD_DOSSIER=ASTRE.DOSS_PIECE.COD_DOSSIER(+) )
and ASTRE.DOSS_PIECE.IND_A_RECLAMER = '1';
file_handle UTL_FILE.FILE_TYPE;
ligne varchar2(1000);
pieces varchar2(1000);
BEGIN
tmpVar := 0;
file_handle := UTL_FILE.FOPEN('/data/expora/ASTREGF','plj_pieces_manquantes_AR.dat','W');
-- ouverture du curseur
for dos in c_dossier loop
for pie in c_pieces(dos.cod_dossier) loop
pieces:=pieces||'-'||pie.lib_piece;
end loop;
ligne:=dos.cod_dossier||' : '||pieces;
UTL_FILE.PUT_LINE(file_handle, ligne);
END LOOP;
UTL_FILE.FCLOSE (file_handle);
END EXTRACT_PLJ_PIECES; |
Partager