-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCV_IAG_QUICK_CHECK.~sql
178 lines (157 loc) · 6.26 KB
/
CV_IAG_QUICK_CHECK.~sql
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
--Get total order count for PDS orders SINCE SET DATE
var start_date varchar2(20)
exec :start_date := To_Date('26-May-2014')
var end_date varchar2(20)
exec :end_date := To_Date('31-Jul-2014')
var order_num varchar2(20)
exec :order_num := ' 1527600'
SELECT 'PDS' AS "SCAN"
,SD.SD_ORDER
,SD.SD_LINE
,SD.SD_STOCK
,SD.SD_ADD_DATE
,SD.SD_LAST_PICK_NUM
,SD.SD_ADD_TIME
,SD.SD_ADD_OP
,SD.SD_LOCN
,SD.SD_QTY_ORDER
,SD.SD_QTY_DEMAND
,SD.SD_QTY_DESP
,SD.SD_QTY_UNIT
,SD.SD_DESC
,IU.IU_TO_METRIC * SD.SD_QTY_ORDER AS "SinglesQTY"
,NULL AS "PASS/FAIL"
,NULL AS "OriginalPromoQty"
,IA_ALT_STOCK AS "IA_ALT_STOCK"
,IA_STOCK AS "IA_STOCK"
,IM_XX_CC01_QTY AS "SPDS_QTY"
,NULL AS "PDS_STOCK"
FROM SD
INNER JOIN IU ON IU.IU_UNIT = SD.SD_QTY_UNIT
INNER JOIN IM ON IM.IM_STOCK = SD.SD_STOCK
LEFT OUTER JOIN IA ON SD.SD_STOCK = IA.IA_STOCK
WHERE SD.SD_ADD_DATE >= :start_date AND SD.SD_ADD_DATE <= :end_date
AND IM_CUST IN ('RACV','IAG')
--AND SD.SD_LAST_PICK_NUM IS NOT NULL
AND IA_STOCK IS NOT NULL
--AND SD.SD_ORDER = :order_num
AND SD.SD_STATUS <> 3
AND IA_ADD_OP = 'PRJ'
UNION ALL
SELECT 'SPDS' AS "SCAN"
,SD.SD_ORDER
,SD.SD_LINE
,SD.SD_STOCK
,SD.SD_ADD_DATE
,SD.SD_LAST_PICK_NUM
,SD.SD_ADD_TIME
,SD.SD_ADD_OP
,SD.SD_LOCN
,SD.SD_QTY_ORDER
,SD.SD_QTY_DEMAND
,SD.SD_QTY_DESP
,SD.SD_QTY_UNIT
,SD.SD_DESC
,IU.IU_TO_METRIC * SD.SD_QTY_ORDER AS "SinglesQTY"
,CASE WHEN (To_Number((SELECT IM_XX_CC01_QTY
FROM IM
LEFT OUTER JOIN IA ON IA_STOCK = IM.IM_STOCK
WHERE IA_ADD_OP = 'PRJ'
AND IA_STOCK = (SELECT D4.SD_STOCK
FROM SD D4
LEFT OUTER JOIN IA ON IA_STOCK = D4.SD_STOCK
WHERE D4.SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1)) * (SELECT D5.SD_QTY_ORDER
FROM SD D5
LEFT OUTER JOIN IA ON IA_STOCK = D5.SD_STOCK
WHERE D5.SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1)) ) = SD.SD_QTY_ORDER THEN 'PASS'
ELSE 'FAIL'
END AS "PASS/FAIL"
,(SELECT IM_XX_CC01_QTY
FROM IM
INNER JOIN IA ON IA_STOCK = IM.IM_STOCK
WHERE IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK IN (SELECT IA_ALT_STOCK FROM IA
WHERE IA_ADD_OP = 'PRJ' AND rownum <= 1) ) AS "OriginalPromoQty"
,NULL AS "IA_ALT_STOCK"
,NULL AS "IA_STOCK"
,To_Number((SELECT IM_XX_CC01_QTY
FROM IM
LEFT OUTER JOIN IA ON IA_STOCK = IM.IM_STOCK
WHERE IA_ADD_OP = 'PRJ'
AND IA_STOCK = (SELECT D4.SD_STOCK
FROM SD D4
LEFT OUTER JOIN IA ON IA_STOCK = D4.SD_STOCK
WHERE D4.SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1)) * (SELECT D5.SD_QTY_ORDER
FROM SD D5
LEFT OUTER JOIN IA ON IA_STOCK = D5.SD_STOCK
WHERE D5.SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1 ))
AS "EXP SPDS_QTY"
,(SELECT SD_STOCK
FROM SD D3
INNER JOIN IA ON IA_STOCK = D3.SD_STOCK
WHERE SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1 ) AS "PDS_STOCK"
FROM SD
INNER JOIN IU ON IU.IU_UNIT = SD.SD_QTY_UNIT
INNER JOIN IM ON IM.IM_STOCK = SD.SD_STOCK
--LEFT OUTER JOIN IA ON SD.SD_STOCK = IA.IA_ALT_STOCK
WHERE SD.SD_ADD_DATE >= :start_date AND SD.SD_ADD_DATE <= :end_date
AND IM_CUST IN ('RACV','IAG')
AND SD.SD_STATUS <> 3
--AND SD.SD_ORDER = :order_num
AND (SELECT SD_STOCK
FROM SD D3
INNER JOIN IA ON IA_STOCK = D3.SD_STOCK
WHERE SD_ORDER = SD.SD_ORDER
AND IA_ADD_OP = 'PRJ'
AND IA_ALT_STOCK = SD.SD_STOCK AND rownum = 1) IS NOT NULL
/*
UNION ALL
SELECT 'NEITHER' AS "SCAN"
,SD.SD_ORDER
,SD.SD_LINE
,SD.SD_STOCK
,SD.SD_ADD_DATE
,SD.SD_LAST_PICK_NUM
,SD.SD_ADD_TIME
,SD.SD_ADD_OP
,SD.SD_LOCN
,SD.SD_QTY_ORDER
,SD.SD_QTY_DEMAND
,SD.SD_QTY_DESP
,SD.SD_QTY_UNIT
,SD.SD_DESC
,IU.IU_TO_METRIC * SD.SD_QTY_ORDER AS "SinglesQTY"
--,NULL AS "ExpPromoSinglesQTY"
,NULL AS "PASS/FAIL"
,NULL AS "OriginalPromoQty"
,NULL AS "IA_ALT_STOCK"
,NULL AS "IA_STOCK"
,NULL AS "SPDS_QTY"
,NULL AS "PDS_STOCK"
FROM SD
INNER JOIN IU ON IU.IU_UNIT = SD.SD_QTY_UNIT
INNER JOIN IM ON IM.IM_STOCK = SD.SD_STOCK
--LEFT OUTER JOIN IA ON SD.SD_STOCK != IA.IA_ALT_STOCK AND SD.SD_STOCK != IA.IA_STOCK
WHERE SD.SD_ADD_DATE >= :start_date AND SD.SD_ADD_DATE <= :end_date
AND IM_CUST IN ('RACV','IAG')
--AND SD.SD_LAST_PICK_NUM IS NOT NULL
--AND IA_ALT_STOCK IS NULL
AND SD.SD_STATUS <> 3
--AND SD.SD_ORDER > :order_num
AND SD_STOCK NOT IN (SELECT IM_STOCK
FROM IM INNER JOIN IA ON IM_STOCK = IA_STOCK
WHERE IA_ADD_OP = 'PRJ' AND IM_XX_CC01_QTY > 0)
AND SD_STOCK NOT IN (SELECT IM_STOCK
FROM IM INNER JOIN IA ON IM_STOCK = IA_ALT_STOCK)
*/
ORDER BY 6,1,3 Asc