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
| with CodeReception as (
SELECT DISTINCT
A.NOROHE as NOR,
case when B.NUMORC is not null then 'Frais' when C.NUMOR is not null then 'Sec' when D.NUORDO is not null then 'Métier' else '' end as Type
from LODTA.ELHEAVP0 A
left join LODTA.BCPECDFO B ON A.NOROHE=B.NUMORC
left join LODTA.GSPORLF C ON A.NOROHE=C.NUMOR
left join LODTA.FODORMP0 D ON A.NOROHE=D.NUORDO
),
NbreColis as (
SELECT
NOR,
TYPE,
case TYPE when 'Frais' then B.QTCURE when 'Sec' then C.QTSTF when 'Métier' then D.QTRECUC else '' end as CumColis
from CodeReception A
left join LODTA.BCPDCDFO B on A.NOR = B.NUMORC
left join LODTA.GSPORLF C on A.NOR = C.NUMOR
left join LODTA.TCRFCPP0 D on A.NOR = D.NUORWF
),
Commentaire as (
SELECT
NOR,
TYPE,
CUMCOLIS,
case TYPE when 'Sec' then E.COMCD1 when 'Frais' then C1.TEXTLX else '' end as ligne1,
case TYPE when 'Sec' then E.COMCD2 when 'Frais' then C1.TEXTLX else '' end as ligne2,
case TYPE when 'Sec' then E.COMCD3 when 'Frais' then C1.TEXTLX else '' end as ligne3,
case TYPE when 'Sec' then E.COMCD4 else '' end as ligne4
from NbreColis A
left join LODTA.BCPECDFO B on A.NOR = B.NUMORC
left join LODTA.BCPCCDFO C1 on B.NCDEFO = C1.NCDEFO AND C1.NOLIGN = 1
left join LODTA.BCPCCDFO C2 on B.NCDEFO = C2.NCDEFO AND C2.NOLIGN = 2
left join LODTA.BCPCCDFO C3 on B.NCDEFO = C3.NCDEFO AND C3.NOLIGN = 3
left join LODTA.GSPORLF D on A.NOR = D.NUMOR
left join LODTA.GSPCDFRE E on D.NCDEFO = E.NCDEFO
)
select
C.CACTHP,
D.LACTAT,
A.NENTHE,
E.LEN1EN,
L.TYPE,
A.DENLHE,
A.NFOUHE,
A.FILIHE,
'',
J.INFOTB,
H.CPOSLF concat ' ' concat VILLLF,
A.NOROHE,
'',
L.CUMCOLIS,
A.POCFHE,
SUBSTRING(COENLVT, 1, 30),
SUBSTRING(COENLVT, 61, 30),
A.NTRPHE,
K.RSO1FO,
SUBSTRING(COENLVT, 31, 30),
SUBSTRING(COENLVT, 91, 30),
L.LIGNE1,
L.LIGNE2,
L.LIGNE3,
L.LIGNE4
from LODTA.ELHEAVP0 A
left join DBLIB.DBFDEPP0 B ON A.NFOUHE=B.NFOUFA AND A.DACTHE=B.DACTFA
left join DBLIB.DBCHPRP0 C ON B.CCPRFA = C.CCPRHP
left join DBLIB.DBACTIP0 D ON C.CACTHP=D.CACTAT
left join DBLIB.DBENTRP0 E ON A.NENTHE=E.NENTEN
left join DBLIB.DBFILFP0 G ON A.NFOUHE=G.NFOUFF AND A.FILIHE=G.FILIFF
left join DBLIB.DBLFOFP0 H ON G.NFOUFF=H.NFOULF
left join DBLIB.DBEBENP0 I ON A.BAREHE=I.BAREBE
left join DBLIB.DBTABLP0 J ON I.ZODPBE=J.CLTBTB AND NMTBTB = 'ZGE' AND NDOSTB = '001'
left join DBLIB.DBFOURP0 K ON A.NFOUHE=K.NFOUFO
left join Commentaire L ON A.NOROHE = L.NOR |
