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
| SELECT
UTEV.TAUX_HOR_REEL TAUX_HORAIRE
FROM (SELECT ID_EMPLOYE, TYPE_EMPLOYE,FORFAITAIRE,
MIN(DATE_DEBUT) AS DATE_DEBUT,
MAX(NVL(DATE_FIN,SYSDATE)) AS DATE_FIN
FROM UTL_ASSOC_EMPL_DRI
GROUP BY ID_EMPLOYE, FORFAITAIRE, TYPE_EMPLOYE) EDRI,
GPB_CATGR_PROJ CATP, UTL_PROJ PROJ,
UTL_FEUIL_TEMPS UFT, GRI_EMPLOYE GEMP,
GPB_DEMAND DEMA,GPB_ENGGMN ENGG,
GPB_ENGGMN_ITEM ENGI,GPB_FOURNS FOUR,
(SELECT SENGGMN.ID_ENGGMN, SFORF.DATE_DEBUT,SFORF.DATE_FIN,
SFORF.MNT_FORFAIT MNT_FORFAIT,
SFORF.MNT_FORFAIT / (SFORF.DATE_FIN - SFORF.DATE_DEBUT + 1)
* (LEAST (:P12,SFORF.DATE_FIN)
- GREATEST(:P11,SFORF.DATE_DEBUT)+ 1)
/ SUM(SUFT.NBR_HEURE) AS TAUX_HOR_REEL
FROM GPB_MNT_FORFAIT SFORF,
GPB_ENGGMN_ITEM SENGITEM,
GPB_ENGGMN SENGGMN,
GPB_DEMAND SDEMAN,
UTL_FEUIL_TEMPS SUFT
WHERE SDEMAN.ID_FOURNS = :P10
AND SDEMAN.FORFAITAIRE ='O'
AND SENGGMN.ID_DEMAND = SDEMAN.ID_DEMAND
AND SFORF.ID_ENGGMN = SENGGMN.ID_ENGGMN
AND SENGGMN.NO_ENGGMN LIKE :P13
AND (SFORF.DATE_DEBUT BETWEEN :P11 AND :P12
OR SFORF.DATE_FIN BETWEEN :P11 AND :P12
OR :P11 BETWEEN SFORF.DATE_DEBUT AND SFORF.DATE_FIN
OR :P12 BETWEEN SFORF.DATE_DEBUT AND SFORF.DATE_FIN)
AND SENGITEM.ID_ENGGMN = SENGGMN.ID_ENGGMN
AND SENGITEM.ID_EMPLOYE = SUFT.ID_EMPLOYE
AND SUFT.DATE_DEBUT BETWEEN GREATEST(:P11,SFORF.DATE_DEBUT, NVL(SENGITEM.DATE_DEBUT,:P11))
AND LEAST (:P12,SFORF.DATE_FIN,NVL(SENGITEM.DATE_FIN,:P12))
AND SUFT.ID_PROJ <> '0'
GROUP BY
SENGGMN.ID_ENGGMN,
SFORF.DATE_DEBUT,
SFORF.DATE_FIN,
SFORF.MNT_FORFAIT) UTEV --
WHERE UFT.ID_PROJ <> '0' -- EMPLOYEE POUR PéRIODE SANS LE PROJET 0
AND UFT.DATE_DEBUT BETWEEN :P11 AND :P12
AND UFT.ID_EMPLOYE = GEMP.ID_EMPLOYE
AND EDRI.ID_EMPLOYE = UFT.ID_EMPLOYE
AND (:P11 BETWEEN EDRI.DATE_DEBUT AND NVL(EDRI.DATE_FIN,:P12)
OR :P12 BETWEEN EDRI.DATE_DEBUT AND NVL(EDRI.DATE_FIN,:P12))
AND EDRI.TYPE_EMPLOYE <> 'RE'
AND UFT.ID_PROJ = PROJ.ID_PROJ -- LA CATEGORIE DE PROJET
AND PROJ.ID_CATGR_PROJ = CATP.ID_CATGR_PROJ
AND FOUR.ID_FOURNS = :P10 -- FOURNISSEUR SéLECTIONNé
AND DEMA.ID_FOURNS = FOUR.ID_FOURNS-- DEMANDE POUR PéRIODES ET FOURN
AND UFT.DATE_DEBUT BETWEEN NVL(DEMA.DATE_DEBUT,:P11) AND NVL(DEMA.DATE_FIN, :P12)
AND ENGG.ID_DEMAND = DEMA.ID_DEMAND -- ENGAGEMENTS PéRIODE ET DEMANDE
AND ENGG.NO_ENGGMN LIKE :P13
AND UFT.DATE_DEBUT BETWEEN NVL(ENGG.DATE_DEBUT, NVL(DEMA.DATE_DEBUT,:P11))
AND NVL(ENGG.DATE_FIN, NVL(DEMA.DATE_FIN, :P12))
AND UFT.DATE_DEBUT BETWEEN NVL(ENGG.DATE_DEBUT, NVL(DEMA.DATE_DEBUT,:P11))
AND NVL(ENGG.DATE_FIN, NVL(DEMA.DATE_FIN, :P12))
AND UTEV.ID_ENGGMN = ENGG.ID_ENGGMN --LE TAUX HORAIRE SELON LE FORFAIT
AND UFT.DATE_DEBUT BETWEEN UTEV.DATE_DEBUT AND NVL(UTEV.DATE_FIN,UFT.DATE_DEBUT)
AND ENGI.ID_ENGGMN = ENGG.ID_ENGGMN
AND ENGI.ID_EMPLOYE = UFT.ID_EMPLOYE
AND UFT.DATE_DEBUT BETWEEN NVL(ENGI.DATE_DEBUT,NVL(ENGG.DATE_DEBUT, NVL(DEMA.DATE_DEBUT,:P11)))
AND NVL(ENGI.DATE_FIN,NVL(ENGG.DATE_FIN, NVL(DEMA.DATE_FIN,:P12)))
GROUP BY :P11, :P12, UTEV.ID_ENGGMN, FOUR.NOM_FOURNS, DEMA.NO_DEMAND, UFT.ID_PROJ,
GEMP.NOM || ',' || GEMP.PRENOM || DECODE(EDRI.FORFAITAIRE,'O','(FORFAITAIRE)',''),
UTEV.TAUX_HOR_REEL, CATP.ID_CATGR_PROJ, CATP.DESC_CATGR_PROJ
UNION
--TEMPS HORAIRE NON-FORFAITAIRE
SELECT
UAED.TAUX_HOR_REEL TAUX_HORAIRE
FROM UTL_ASSOC_EMPL_DRI UAED,GPB_CATGR_PROJ GCP, UTL_PROJ UP,
UTL_FEUIL_TEMPS UFT, GRI_EMPLOYE GE, GPB_ENGGMN_ITEM GEI,
GPB_ENGGMN ENGG,GPB_DEMAND DEMA, GPB_FOURNS FOUR
WHERE UFT.DATE_DEBUT BETWEEN :P11 AND :P12-- PÉRIODE SANS PROJET 0
AND UFT.ID_PROJ <> '0'
AND UFT.ID_PROJ = UP.ID_PROJ -- TROUVER LA CATEGORIE DE PROJET
AND UP.ID_CATGR_PROJ = GCP.ID_CATGR_PROJ
AND UFT.ID_EMPLOYE = GE.ID_EMPLOYE -- LES EMPLOYE CONSULTANT PÉRIODE
AND UFT.ID_EMPLOYE = UAED.ID_EMPLOYE
AND UAED.TYPE_EMPLOYE <> 'RE'
AND UAED.FORFAITAIRE = 'N'
AND UFT.DATE_DEBUT BETWEEN UAED.DATE_DEBUT AND NVL(UAED.DATE_FIN,:P12)
AND FOUR.ID_FOURNS = :P10 -- POUR LE FOURNISSEUR SÉLECTIONNÉ
AND DEMA.ID_FOURNS = FOUR.ID_FOURNS-- LES DEMANDES DANS LA PÉRIODES
AND UFT.DATE_DEBUT BETWEEN NVL(DEMA.DATE_DEBUT,:P11) AND NVL(DEMA.DATE_FIN,:P12)
AND ENGG.ID_DEMAND = DEMA.ID_DEMAND -- ENGAGEMENTS POUR LA PÉRIODE
AND ENGG.NO_ENGGMN LIKE :P13
AND UFT.DATE_DEBUT BETWEEN NVL(ENGG.DATE_DEBUT, NVL(DEMA.DATE_DEBUT,:P11)) AND NVL(ENGG.DATE_FIN, NVL(DEMA.DATE_FIN,:P12))
-- ITEMS D'ENGAGEMENT POUR LES EMPLOYE ET LA PÉRIODE
AND GEI.ID_ENGGMN = ENGG.ID_ENGGMN
AND GEI.ID_EMPLOYE = UFT.ID_EMPLOYE
AND UFT.DATE_DEBUT BETWEEN NVL(GEI.DATE_DEBUT,NVL(ENGG.DATE_DEBUT,NVL(DEMA.DATE_DEBUT,:P11)))
AND NVL(GEI.DATE_FIN,NVL(ENGG.DATE_FIN,NVL(DEMA.DATE_FIN,:P12)))
AND :P11 <= NVL(GEI.DATE_FIN,NVL(ENGG.DATE_FIN,NVL(DEMA.DATE_FIN,:P12)))
GROUP BY :P12, :P11, FOUR.NOM_FOURNS, DEMA.NO_DEMAND,
GE.NOM || ',' || GE.PRENOM || DECODE(UAED.FORFAITAIRE,'O','(FORFAITAIRE)',''),
UAED.TAUX_HOR_REEL,UFT.ID_PROJ,GCP.ID_CATGR_PROJ,GCP.DESC_CATGR_PROJ |
Partager