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
| FUNCTION HistoDetail(
cIdPerso CHAR,
cIdPersoCgu VARCHAR2,
cJDeb CHAR,
cJFin CHAR,
cFinActivite CHAR) RETURN SYS_REFCURSOR IS
rc SYS_REFCURSOR;
cJFinPtgPers CHAR(8);
cHeureFin CHAR(6);
Invalid_HistoDetail EXCEPTION;
BEGIN
IF (cJFin >= TO_CHAR(SYSDATE,'YYYYMMDD')) THEN
cJFinPtgPers:=TO_CHAR(SYSDATE-1,'YYYYMMDD');
ELSE
cJFinPtgPers:=cJFin;
END IF;
IF (cJFinPtgPers != cJFin) THEN
cHeureFin:=TO_CHAR(SYSDATE,'HH24MISS');
END IF;
OPEN rc FOR
SELECT *
FROM
(
SELECT TO_CHAR(POIN.jour,'YYYYMMDD') CJour,
POIN.heure CHeureDeb,
NVL(LEAD(POIN.heure) OVER (ORDER BY POIN.jour, POIN.heure), POIN.heure) CHeureFin,
Tps_pack.HeureDiff(POIN.heure, NVL(LEAD(POIN.heure) OVER (ORDER BY POIN.jour, POIN.heure), POIN.heure), 0) CTemps, A.id_ligne CIdLig,
L.libelle CLibLig,
A.id_plan CIdPlan,
P.libelle CLibPlan,
A.id_fonction CIdFonc,
F.libelle CLibFonc,
A.id_operation CIdOpe,
O.libelle CLibOpe,
POIN.of_sec COfSec,
OO.libelle ClibOf
FROM Pointage POIN LEFT OUTER JOIN O_f OO ON (DECODE(oth_pack.LASTALPHA(POIN.of_sec),0,POIN.of_sec,SUBSTR(POIN.of_sec,1,LENGTH(POIN.of_sec)-1))=SUBSTR(OO.of_sec,1,LENGTH(OO.of_sec)-1)),Activite A,Ligne L,Plan P,Fonction F,Operation O
WHERE POIN.jour=TRUNC(SYSDATE)
AND POIN.id_perso=cIdPerso
AND POIN.id_activite=A.id_ligne||A.id_plan||A.id_fonction||A.id_operation
AND A.id_type_ptg_sst='G'
AND TO_CHAR(SYSDATE,'YYYYMMDD')<=TO_CHAR(A.date_fin_valide,'YYYYMMDD')
AND A.id_ligne=L.id_ligne AND A.id_plan=P.id_plan AND A.id_fonction=F.id_fonction AND A.id_operation=O.id_operation
AND (cJFinPtgPers != cJFin) -- CPointage n'était ouvert que sous cette condition
UNION ALL
SELECT TO_CHAR(PTG.jour,'YYYYMMDD') CJour,
PTG.heure_deb CHeureDeb,
PTG.heure_fin CHeureFin,
Tps_pack.Centi2Heure(PTG.Temps) CTemps,
A.id_ligne CIdLig,
L.libelle CLibLig,
A.id_plan CIdPlan,
P.libelle CLibPlan,
A.id_fonction CIdFonc,
F.libelle CLibFonc,
A.id_operation CIdOpe,
O.libelle CLibOpe,
PTG.ofs_cpt COfSec,
OO.libelle CLibOf
FROM Ptg_pers PTG,Activite A,Ligne L,Plan P,Fonction F,Operation O,O_f OO
WHERE PTG.jour>=TO_DATE(cJDeb,'YYYYMMDD') AND PTG.jour<=TO_DATE(cJFinPtgPers,'YYYYMMDD')
AND PTG.id_perso=cIdPersoCgu
AND PTG.id_ligne=A.id_ligne AND PTG.id_plan=A.id_plan AND PTG.id_fonction=A.id_fonction AND PTG.id_operation=A.id_operation
AND A.id_type_ptg_sst='G'
AND cJDeb<=TO_CHAR(A.date_fin_valide,'YYYYMMDD')
AND A.id_ligne=L.id_ligne AND A.id_plan=P.id_plan AND A.id_fonction=F.id_fonction AND A.id_operation=O.id_operation
AND PTG.ofs_cpt=OO.of_sec(+)
)
ORDER BY CJour DESC,CHeureDeb DESC,CHeureFin DESC;
RETURN rc;
EXCEPTION
WHEN OTHERS THEN
RAISE Invalid_HistoDetail;
END HistoDetail; |
Partager