-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathEOMFreight.~sql
More file actions
212 lines (194 loc) · 7.96 KB
/
EOMFreight.~sql
File metadata and controls
212 lines (194 loc) · 7.96 KB
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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
var start_date varchar2(20)
exec :start_date := To_Date('01-Oct-2013')
var end_date varchar2(20)
exec :end_date := To_Date('30-Oct-2013')
/*freight fees*/
select s.SH_CUST AS "Customer",
r.RM_PARENT AS "Parent",
s.SH_SPARE_STR_4 AS "CostCentre",
s.SH_ORDER AS "Order",
s.SH_SPARE_STR_5 AS "OrderwareNum",
s.SH_CUST_REF AS "CustomerRef",
t.ST_PICK AS "Pickslip",
d.SD_XX_PICKLIST_NUM AS "PickNum",
t.ST_PSLIP AS "DespatchNote",
substr(To_Char(t.ST_DESP_DATE),0,10) AS "DespatchDate",
CASE WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 THEN 'Freight Fee'
ELSE To_Char(d.SD_DESC)
END AS "FeeType",
d.SD_STOCK AS "Item",
'="' || To_Char(d.SD_DESC) || '"' AS "Description",
CASE WHEN d.SD_LINE IS NOT NULL THEN 1
ELSE NULL
END AS "Qty",
CASE WHEN d.SD_LINE IS NOT NULL THEN '1'
ELSE NULL
END AS "UOI",
CASE WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 AND (r.RM_PARENT <> 'BORBUI' AND r.RM_PARENT <> 'BEYONDBLUE') THEN d.SD_SELL_PRICE
WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 AND (r.RM_PARENT = 'BORBUI' OR r.RM_PARENT = 'BEYONDBLUE') AND (t.ST_SPARE_DBL_1 <= 0.125) And (t.ST_SPARE_DBL_1 > 0.00) THEN 1.70
WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 AND (r.RM_PARENT = 'BORBUI' OR r.RM_PARENT = 'BEYONDBLUE') AND (t.ST_SPARE_DBL_1 <= 0.250) And (t.ST_SPARE_DBL_1 > 0.126) THEN 2.30
WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 AND (r.RM_PARENT = 'BORBUI' OR r.RM_PARENT = 'BEYONDBLUE') AND (t.ST_SPARE_DBL_1 <= 0.500) And (t.ST_SPARE_DBL_1 > 0.251) THEN 3.40
WHEN d.SD_STOCK like 'COURIER%' AND d.SD_SELL_PRICE >= 1 AND (r.RM_PARENT = 'BORBUI' OR r.RM_PARENT = 'BEYONDBLUE') AND (t.ST_WEIGHT > 0.01) THEN d.SD_SELL_PRICE
ELSE NULL
END AS "UnitPrice",
d.SD_XX_OW_UNIT_PRICE AS "OWUnitPrice",
d.SD_EXCL AS "DExcl",
Sum(d.SD_EXCL) AS "Excl_Total",
d.SD_INCL AS "DIncl",
Sum(d.SD_INCL) AS "Incl_Total",
NULL AS "ReportingPrice",
s.SH_ADDRESS AS "Address",
s.SH_SUBURB AS "Address2",
s.SH_CITY AS "Suburb",
s.SH_STATE AS "State",
s.SH_POST_CODE AS "Postcode",
s.SH_NOTE_1 AS "DeliverTo",
s.SH_NOTE_2 AS "AttentionTo" ,
t.ST_WEIGHT AS "Weight",
t.ST_PACKAGES AS "Packages",
s.SH_SPARE_DBL_9 AS "OrderSource",
NULL AS "Pallet/Shelf Space", /*Pallet/Space*/
NULL AS "Locn", /*Locn*/
0 AS "AvailSOH",/*Avail SOH*/
0 AS "CountOfStocks",
CASE WHEN regexp_substr(s.SH_SPARE_STR_3,'[a-z]+', 1, 2) IS NOT NULL THEN s.SH_SPARE_STR_3 || '@' || s.SH_SPARE_STR_1
ELSE ''
END AS Email,
'N/A' AS Brand
FROM PWIN175.SD d
INNER JOIN PWIN175.SH s ON s.SH_ORDER = d.SD_ORDER
INNER JOIN PWIN175.ST t ON LTRIM(RTRIM(t.ST_PICK)) = LTRIM(RTRIM(d.SD_XX_PICKLIST_NUM))
INNER JOIN PWIN175.RM r ON r.RM_CUST = s.SH_CUST
WHERE s.SH_ORDER = d.SD_ORDER
--AND r.RM_ANAL = :anal
--AND (r.RM_PARENT = :cust OR r.RM_CUST = :cust)
AND d.SD_STOCK IN ('COURIERM','COURIERS','COURIER')
AND s.SH_ORDER = t.ST_ORDER
AND d.SD_SELL_PRICE >= 1
AND t.ST_DESP_DATE >= :start_date AND t.ST_DESP_DATE <= :end_date
AND d.SD_ADD_OP LIKE 'SERV%'
GROUP BY s.SH_CUST,
s.SH_SPARE_STR_4,
s.SH_ORDER,
t.ST_PICK,
d.SD_XX_PICKLIST_NUM,
t.ST_PSLIP,
t.ST_DESP_DATE,
d.SD_STOCK,
d.SD_DESC,
d.SD_LINE,
d.SD_EXCL,
d.SD_INCL,
d.SD_NOTE_1,
d.SD_SELL_PRICE,
d.SD_XX_OW_UNIT_PRICE,
d.SD_QTY_ORDER,
d.SD_QTY_ORDER,
s.SH_ADDRESS,
s.SH_SUBURB,
s.SH_CITY,
s.SH_STATE,
s.SH_POST_CODE,
s.SH_NOTE_1,
s.SH_NOTE_2,
t.ST_WEIGHT,
t.ST_PACKAGES,
s.SH_SPARE_DBL_9,
r.RM_PARENT,
s.SH_SPARE_STR_5,
s.SH_CUST_REF,
s.SH_SPARE_STR_3,
s.SH_SPARE_STR_1,
t.ST_SPARE_DBL_1
UNION ALL
/*freight fees*/
--SELECT Sum(NE_AVAIL_ACTUAL) FROM IM, NE WHERE IM_STOCK = 'AUST106AUCS' AND NE_STOCK = IM_STOCK AND NE_AVAIL_ACTUAL >= 1 AND NE_TRAN_TYPE = 1 ORDER BY NE_ENTRY Desc
/*Manual freight fees*/
select s.SH_CUST AS "Customer",
r.RM_PARENT AS "Parent",
s.SH_SPARE_STR_4 AS "CostCentre",
s.SH_ORDER AS "Order",
s.SH_SPARE_STR_5 AS "OrderwareNum",
s.SH_CUST_REF AS "CustomerRef",
t.ST_PICK AS "Pickslip",
d.SD_XX_PICKLIST_NUM AS "PickNum",
t.ST_PSLIP AS "DespatchNote",
substr(To_Char(t.ST_DESP_DATE),0,10) AS "DespatchDate",
CASE WHEN d.SD_STOCK IN ('COURIERM','COURIERS','COURIER','DETENTIONTIMEM','DETENTIONTIMES','FREIGHTDUTY') AND d.SD_SELL_PRICE >= 1 THEN 'Manual Freight Fee'
ELSE NULL
END AS "FeeType",
d.SD_STOCK AS "Item",
'="' || To_Char(d.SD_DESC) || '"' AS "Description",
CASE WHEN d.SD_LINE IS NOT NULL THEN 1
ELSE NULL
END AS "Qty",
CASE WHEN d.SD_LINE IS NOT NULL THEN '1'
ELSE NULL
END AS "UOI",
d.SD_SELL_PRICE AS "UnitPrice",
d.SD_XX_OW_UNIT_PRICE AS "OWUnitPrice",
d.SD_EXCL AS "DExcl",
Sum(d.SD_EXCL) AS "Excl_Total",
d.SD_INCL AS "DIncl",
Sum(d.SD_INCL) AS "Incl_Total",
NULL AS "ReportingPrice",
s.SH_ADDRESS AS "Address",
s.SH_SUBURB AS "Address2",
s.SH_CITY AS "Suburb",
s.SH_STATE AS "State",
s.SH_POST_CODE AS "Postcode",
s.SH_NOTE_1 AS "DeliverTo",
s.SH_NOTE_2 AS "AttentionTo" ,
t.ST_WEIGHT AS "Weight",
t.ST_PACKAGES AS "Packages",
s.SH_SPARE_DBL_9 AS "OrderSource",
NULL AS "Pallet/Shelf Space", /*Pallet/Space*/
NULL AS "Locn", /*Locn*/
0 AS "AvailSOH",/*Avail SOH*/
0 AS "CountOfStocks",
CASE WHEN regexp_substr(s.SH_SPARE_STR_3,'[a-z]+', 1, 2) IS NOT NULL THEN s.SH_SPARE_STR_3 || '@' || s.SH_SPARE_STR_1
ELSE ''
END AS Email,
'N/A' AS Brand
FROM PWIN175.SD d
INNER JOIN PWIN175.SH s ON s.SH_ORDER = d.SD_ORDER
INNER JOIN PWIN175.ST t ON LTRIM(RTRIM(t.ST_ORDER)) = LTRIM(RTRIM(d.SD_ORDER))
INNER JOIN PWIN175.RM r ON r.RM_CUST = s.SH_CUST
WHERE s.SH_ORDER = d.SD_ORDER
--AND r.RM_ANAL = :anal
-- AND (r.RM_PARENT = :cust OR r.RM_CUST = :cust)
AND d.SD_STOCK IN ('COURIERM','COURIERS','COURIER','DETENTIONTIMEM','DETENTIONTIMES','FREIGHTDUTY')
AND s.SH_ORDER = t.ST_ORDER
AND d.SD_SELL_PRICE >= 1
AND d.SD_ADD_DATE >= :start_date AND d.SD_ADD_DATE <= :end_date
AND d.SD_ADD_OP NOT LIKE 'SERV%'
GROUP BY s.SH_CUST,
s.SH_SPARE_STR_4,
s.SH_ORDER,
t.ST_PICK,
d.SD_XX_PICKLIST_NUM,
t.ST_PSLIP,
t.ST_DESP_DATE,
d.SD_STOCK,
d.SD_DESC,
d.SD_LINE,
d.SD_EXCL,
d.SD_INCL,
d.SD_NOTE_1,
d.SD_SELL_PRICE,
d.SD_XX_OW_UNIT_PRICE,
d.SD_QTY_ORDER,
d.SD_QTY_ORDER,
s.SH_ADDRESS,
s.SH_SUBURB,
s.SH_CITY,
s.SH_STATE,
s.SH_POST_CODE,
s.SH_NOTE_1,
s.SH_NOTE_2,
t.ST_WEIGHT,
t.ST_PACKAGES,
s.SH_SPARE_DBL_9,
r.RM_PARENT,
s.SH_SPARE_STR_5,
s.SH_CUST_REF,s.SH_SPARE_STR_3,s.SH_SPARE_STR_1