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
| SELECT TO_NUMBER(NVL(PL.HEURE_DEPART,'2359')) AS HEURE_DEPART,
PL.COD_LIGNE_EXPEDITION AS LIGNE_EXPEDITION,
PL.COD_TOURNEE,
T.NOM_TOURNEE,
PL.COD_TRANSPORTEUR,
TR.NOM_TRANSPORTEUR,
SUM(NVL(EX.NB_COLIS_PREVU,0)) AS NB_COLIS_PREVU,
SUM(NVL(EX.NB_COLIS_PREPARE,0)) AS NB_COLIS_PREPARE,
SUM(NVL(EX.NB_COLIS_PREVU,0)-NVL(EX.NB_COLIS_PREPARE,0)) AS NB_COLIS_RESTANT,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0)) AS NB_COMMANDE,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0) *(1 - NVL(EX.IS_PREVISION, 0))) AS NB_SERVIS,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0) * NVL(EX.IS_BL_RECU,0)) AS NB_BL,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0))-SUM(DECODE(EX.COD_SOC_COM ,'5',1,0) * NVL(EX.IS_BL_RECU,0)) AS TOT,
SUM(decode(ELC1.COD_SOC_COM, 5, 1, 0)) AS IS_EDITE
FROM REF_EXPEDITION EX
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC1
ON EX.DATE_EXPEDITION = ELC1.DATE_EDITION_LISTE
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC2
ON EX.COD_SOC_COM = ELC2.COD_SOC_COM
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC3
ON EX.NUM_TOURNEE = ELC3.COD_TOURNEE
INNER JOIN REF_PLAN_LIGNE PL
ON EX.NUM_TOURNEE = PL.COD_TOURNEE
INNER JOIN REF_TRANSPORTEURS TR
ON PL.COD_TRANSPORTEUR = TR.COD_TRANSPORTEUR
INNER JOIN REF_TOURNEES_TRANSPORT T
ON EX.NUM_TOURNEE = T.COD_TOURNEE
WHERE EX.DATE_EXPEDITION = TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.DATE_DEBUT <= TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.DATE_FIN >= TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.COD_JOUR IN (0,1)
AND PL.COD_SITE_LOGISTIQ = '907'
AND PL.COD_TOURNEE IN (214,221,286,295)
AND (EX.NO_ZONE_TARIF_TRANSPORT = '5' OR EX.COD_SOC_COM = '5')
GROUP BY PL.HEURE_DEPART, PL.COD_LIGNE_EXPEDITION, PL.COD_TOURNEE, T.NOM_TOURNEE, PL.COD_TRANSPORTEUR,
TR.NOM_TRANSPORTEUR
UNION
SELECT 2359 AS HEURE_DEPART, 999999 AS LIGNE_EXPEDITION, EX.NUM_TOURNEE , T.NOM_TOURNEE ,
999 AS COD_TRANSPORTEUR, ' ' AS NOM_TRANSPORTEUR ,
SUM(NVL(EX.NB_COLIS_PREVU,0)) AS NB_COLIS_PREVU,
SUM(NVL(EX.NB_COLIS_PREPARE,0)) AS NB_COLIS_PREPARE,
SUM(NVL(EX.NB_COLIS_PREVU,0)-NVL(EX.NB_COLIS_PREPARE,0)) AS NB_COLIS_RESTANT,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0)) AS NB_COMMANDE,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0) *(1 - NVL(EX.IS_PREVISION, 0))) AS NB_SERVIS,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0) * NVL(EX.IS_BL_RECU,0)) AS NB_BL,
SUM(DECODE(EX.COD_SOC_COM, 5, 1, 0))-SUM(DECODE(EX.COD_SOC_COM ,5,1,0) * NVL(EX.IS_BL_RECU,0)) AS TOT,
SUM(decode(ELC.COD_SOC_COM, 5, 1, 0)) AS IS_EDITE
FROM REF_EXPEDITION EX, REF_TOURNEES_TRANSPORT T,REF_EDITE_LISTE_COLISAGE ELC
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC1
-- ON EX.DATE_EXPEDITION = ELC1.DATE_EDITION_LISTE
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC2
ON EX.COD_SOC_COM = ELC2.COD_SOC_COM
RIGHT OUTER JOIN REF_EDITE_LISTE_COLISAGE ELC3
ON EX.NUM_TOURNEE = ELC3.COD_TOURNEE
INNER JOIN REF_TOURNEES_TRANSPORT T
ON EX.NUM_TOURNEE = T.COD_TOURNEE
WHERE EX.DATE_EXPEDITION = TO_DATE('08/12/2007','dd/mm/yyyy')
AND EX.NUM_TOURNEE NOT IN( SELECT COD_TOURNEE
FROM REF_PLAN_LIGNE PL
WHERE EX.DATE_EXPEDITION = TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.DATE_DEBUT <= TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.DATE_FIN >= TO_DATE('08/12/2007','dd/mm/yyyy')
AND PL.COD_JOUR IN (0,1)
AND (EX.NO_ZONE_TARIF_TRANSPORT = '5' OR EX.COD_SOC_COM = '5')
AND PL.COD_SITE_LOGISTIQ = '907' )
AND T.COD_TOURNEE in (214,221,286,295)
AND (EX.COD_SOC_COM = '5' OR EX.NO_ZONE_TARIF_TRANSPORT = '5')
GROUP BY EX.NUM_TOURNEE,T.NOM_TOURNEE |
Partager