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 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
| "select /*+rule*/
l.sigtie as SIGTIE, l.idtrn as IDTRN, l.datexpprv as DATEXPPRV,
l.codscl as CODSCL, l.camion as CAMION, 1 as COUNT,
l.codsoc as CODSOC,l.sigtra as SIGTRA,c.idobjcol as IDOBJCOL,
c.sigliv as SIGLIV,c.valzn2 as VALZN2,
(select count(*)
from lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst = '5') as NB_PAL_COMPL,
(select count(*)
from lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst != '5') as NB_PAL_DETAIL,
(select /*+ CHOOSE */ sum(qteunt/decode(qteunt3,0,qteunt,qteunt3))
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst != '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobjpere = lcol.idobj) as NB_COLIS_DETAIL_CAMION,
(select /*+ CHOOSE */ sum(qteunt/decode(qteunt3,0,qteunt,qteunt3))
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst = '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobj = lcol.idobj) as NB_COLIS_COMPL_CAMION,
(select /*+ CHOOSE */ sum(poids)
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst != '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobjpere = lcol.idobj) POIDS_DETAIL,
(select /*+ CHOOSE */ sum(poids)
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst = '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobj = lcol.idobj) POIDS_COMPL,
(select /*+ CHOOSE */ sum(lobjstk.volume)
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst != '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobjpere = lcol.idobj) VOLUME_DETAIL,
(select /*+ CHOOSE */ sum(lobjstk.volume)
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst = '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobj = lcol.idobj) VOLUME_COMPL,
(select /*+ CHOOSE */ sum ( decode (QTEUNT5, QTEUNT, decode (QTEUNT3, 0,0, (QTEUNT/ QTEUNT3))))
from lobjstk, lcol
where lcol.codsoc = l.codsoc
and lcol.typtie = l.typtie
and lcol.sigtie = l.sigtie
and lcol.idtrn = l.idtrn
and lcol.codlnst != '5'
and lobjstk.codsoc = lcol.codsoc
and lobjstk.typtie = lcol.typtie
and lobjstk.sigtie = lcol.sigtie
and lobjstk.idobjpere = lcol.idobj) NB_BOX
from ltrn l,
(select l.codsoc, l.typtie, l.datexpprv, l.statut, l.codscl, l.codplntrn, l.camion, count(*) nbtrn
from ltrn l
where codsoc ='"+context.getProperty("soc")+"'
and typtie = 'DEP'
and statut = '8'
and datmod='"+context.getProperty("datecharge")+"'
group by l.codsoc, l.typtie, l.datexpprv, l.statut, l.codscl, l.codplntrn, l.camion
) ltrn_p,lcol c,lobjstk o
where l.codsoc = ltrn_p.codsoc
and l.typtie = ltrn_p.typtie
and l.statut = ltrn_p.statut
and l.datexpprv = ltrn_p.datexpprv
and l.codscl = ltrn_p.codscl
and l.codplntrn = ltrn_p.codplntrn
and l.camion = ltrn_p.camion
and c.codsoc = l.codsoc
and c.typtie = l.typtie
and c.sigtie = l.sigtie
and c.idtrn = l.idtrn
and o.codsoc = c.codsoc
and o.typtie = c.typtie
and o.sigtie = c.sigtie
and o.idobj = c.idobj
and(l.datmod='"+context.getProperty("datecharge")+"' or c.datmod='"+context.getProperty("datecharge")+"'
or o.datmod='"+context.getProperty("datecharge")+"')
" |
Partager