-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathEOMCartonDespatchFee.~sql
More file actions
125 lines (118 loc) · 6.06 KB
/
EOMCartonDespatchFee.~sql
File metadata and controls
125 lines (118 loc) · 6.06 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
--Admin Order Data
/*decalre variables*/
var cust varchar2(20)
exec :cust := 'TABCORP'
var ordernum varchar2(20)
exec :ordernum := '1370684'
var stock varchar2(20)
exec :stock := 'COURIER'
var stockexclude varchar2(20)
exec :stockexclude := 'FEE%'
var source varchar2(20)
exec :source := 'BSPRINTNSW'
var anal varchar2(20)
exec :anal := '72'
var start_date varchar2(20)
exec :start_date := To_Date('1-Jul-2013')
var end_date varchar2(20)
exec :end_date := To_Date('7-Jul-2013')
--Select rm1.RM_CUST,rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND (rm2.RM_XX_FEE15 <> '' OR LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL ) AND rm1.RM_CUST = :cust
/*Get Carton Despatch Fee*/
SELECT s.SH_CUST AS "Customer",
s.SH_SPARE_STR_4 AS "CostCentre",
s.SH_ORDER AS "Order",
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 t.ST_XX_NUM_CARTONS >= 1 THEN 'Carton Fee is '
ELSE NULL
END AS "FeeType",
CASE WHEN d.SD_LINE IS NOT NULL THEN 'Carton'
ELSE NULL
END AS "Item",
CASE WHEN d.SD_LINE IS NOT NULL THEN 'Carton Fee'
ELSE NULL
END AS "Description",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN ST_XX_NUM_CARTONS
ELSE NULL
END AS "Qty",
CASE WHEN d.SD_LINE IS NOT NULL THEN '1'
ELSE NULL
END AS "UOI",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN '' || (Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL )))
ELSE null
END AS "UnitPrice",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN '' || (Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL ))) * ST_XX_NUM_CARTONS
ELSE NULL
END AS "DExcl",
NULL AS "OWUnitPrice",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN '' || (Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL ))) * ST_XX_NUM_CARTONS
ELSE ''
END AS "Excl_Total",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN '' || ((Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL )) ) * ST_XX_NUM_CARTONS) * 1.1
ELSE ''
END AS "DIncl",
CASE WHEN t.ST_XX_NUM_CARTONS >= 1 THEN '' || ((Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL )) ) * ST_XX_NUM_CARTONS) * 1.1
ELSE ''
END 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*/
NULL AS "AvailSOH",/*Avail SOH*/
NULL AS "CountOfStocks"
FROM PWIN175.SD d
INNER JOIN PWIN175.SH s ON s.SH_ORDER = d.SD_ORDER
INNER JOIN PWIN175.ST t ON t.ST_ORDER = s.SH_ORDER
INNER JOIN PWIN175.RM r ON r.RM_CUST = s.SH_CUST
INNER JOIN PWIN175.IM i ON i.IM_STOCK = d.SD_STOCK
--AND (d.SD_STOCK = 'EMERQSRFEE' AND s.SH_CAMPAIGN = 'TABSPEC')
WHERE ((Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL ))) <> NULL OR (Select rm2.RM_XX_FEE15 from RM rm1 INNER JOIN RM rm2 ON rm2.RM_CUST = rm1.RM_CUST AND rm1.RM_CUST = :cust AND ((rm2.RM_XX_FEE15 <> '') OR (LENGTH(TRIM(TRANSLATE(rm2.RM_XX_FEE15, ' +-.0123456789', ' '))) IS NULL ))) <> 0)
AND s.SH_STATUS <> 3
AND r.RM_ANAL = :anal
AND s.SH_ORDER = t.ST_ORDER
AND (ST_XX_NUM_CARTONS >= 1)
AND d.SD_LINE = 1
AND t.ST_DESP_DATE >= :start_date AND t.ST_DESP_DATE <= :end_date
GROUP BY s.SH_CUST,
s.SH_NOTE_1,
s.SH_CAMPAIGN,
s.SH_SPARE_STR_4,
s.SH_ORDER,
t.ST_PICK,
d.SD_XX_PICKLIST_NUM,
t.ST_PSLIP,
t.ST_DESP_DATE,
t.ST_XX_NUM_CARTONS,
i.IM_TYPE,
d.SD_STOCK,
d.SD_DESC,
d.SD_LINE,
d.SD_EXCL,
d.SD_INCL,
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
--,Rate
HAVING Sum(s.SH_ORDER) <> 1 --AND (Select RM_XX_FEE15 from RM where RM_CUST = :cust ) IS NOT null