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
| WITH
FACPRI AS (SELECT
SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2
FROM
(SELECT
SIH.CPY_0 AS SOCIETE,
SIH.FCY_0 AS SITE,
SIH.ACCDAT_0 AS DATFAC,
SIH.NUM_0 AS NUMFAC,
SIH.CUR_0 AS DEVISE,
SID.BPCINV_0 AS BPCCODE,
BPR.BPRNAM_0 AS CLIENT,
SID.ITMREF_0 AS ITEM,
SID.ITMDES_0 AS DES1,
SID.ITMDES1_0 AS DES2,
ITU.ITMREFBPC_0 AS DESBPC,
SID.GROPRI_0 AS PRIHT,
SID.SOHNUM_0 AS NUMCOM,
SOH.CUSORDREF_0 AS COMCLI,
SOQ.ORDDAT_0 AS DATCOM,
SID.SDHNUM_0 AS NUMLIV,
SDD.SHIDAT_0 AS DATEXP,
SDD.QTY_0 AS QTY,
SDD.SAU_0 AS UOM,
(SELECT MIN(SPL.PRI_0) FROM SPRICLIST SPL WHERE SIH.CPY_0 = SPL.PLICRI1_0
AND SIH.CUR_0 = SPL.CUR_0
AND SID.ITMREF_0 = SPL.PLICRI2_0
AND SDD.BPCORD_0 = SPL.PLICRI3_0
AND SDD.BPAADD_0 = SPL.PLICRI4_0
AND SIH.ACCDAT_0 >= SPL.PLISTRDAT_0
AND SIH.ACCDAT_0 <= SPL.PLIENDDAT_0
AND SID.QTY_0 >= SPL.MINQTY_0
AND SID.QTY_0 <= SPL.MAXQTY_0
AND SPL.PLI_0 = 'NIEF01') AS PRIX1,
(SELECT MIN(SPL1.PRI_0) FROM SPRICLIST SPL1 WHERE SIH.CPY_0 = SPL1.PLICRI1_0
AND SIH.CUR_0 = SPL1.CUR_0
AND SID.ITMREF_0 = SPL1.PLICRI2_0
AND SDD.BPCORD_0 = SPL1.PLICRI3_0
AND SIH.ACCDAT_0 >= SPL1.PLISTRDAT_0
AND SIH.ACCDAT_0 <= SPL1.PLIENDDAT_0
AND SID.QTY_0 >= SPL1.MINQTY_0
AND SID.QTY_0 <= SPL1.MAXQTY_0
AND SPL1.PLI_0 = 'NIEF02') AS PRIX2
FROM
SINVOICED SID
JOIN SINVOICE SIH ON SID.NUM_0 = SIH.NUM_0
JOIN BPARTNER BPR ON SID.BPCINV_0 = BPR.BPRNUM_0
LEFT OUTER JOIN SORDERQ SOQ ON SID.SOHNUM_0 = SOQ.SOHNUM_0 AND SID.SOPLIN_0 = SOQ.SOPLIN_0 AND SID.SOQSEQ_0 = SOQ.SOQSEQ_0
LEFT OUTER JOIN SORDER SOH ON SID.SOHNUM_0 = SOH.SOHNUM_0
LEFT OUTER JOIN ITMBPC ITU ON SOQ.BPCORD_0 = ITU.BPCNUM_0 AND SOQ.ITMREF_0 = ITU.ITMREF_0
LEFT OUTER JOIN SDELIVERYD SDD ON SID.SDHNUM_0 = SDD.SDHNUM_0 AND SID.SDDLIN_0 = SDD.SDDLIN_0
WHERE
SIH.CPY_0 BETWEEN %1% AND %2% AND
SIH.FCY_0 BETWEEN %3% AND %4% AND
SIH.ACCDAT_0 BETWEEN %5% AND %6%
)),
RESULT AS (
SELECT SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2,
CASE
WHEN (PRIX1>0 AND PRIX2>0 AND PRIX1<PRIX2 AND PRIX1<>PRIHT) THEN ((PRIX1-PRIHT)*QTY)
WHEN (PRIX1>0 AND PRIX1 <> PRIHT) THEN ((PRIX1-PRIHT)*QTY)
WHEN (PRIX2>0 AND PRIX2 <> PRIHT) THEN ((PRIX2-PRIHT)*QTY)
ELSE 0
END AS MNTREGUL
FROM FACPRI),
SELECT SOCIETE, SITE, DATFAC, NUMFAC, DEVISE, BPCCODE, CLIENT, ITEM, DES1, DES2, DESBPC, PRIHT, NUMCOM, COMCLI, DATCOM, NUMLIV, DATEXP, QTY, UOM, PRIX1, PRIX2, MNTREGUL
FROM RESULT
WHERE MNTREGUL <> 0 |
