[Home] [Help]
PACKAGE BODY: APPS.WSH_WMS_SYNC_TMP_PKG
Source
1 PACKAGE BODY WSH_WMS_SYNC_TMP_PKG AS
2 /* $Header: WSHWSYTB.pls 120.1 2005/11/15 13:42:31 bsadri noship $ */
3
4
5 --
6 --
7 --
8 --
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_WMS_SYNC_TMP_PKG';
11 --
12 PROCEDURE MERGE
13 (
14 p_sync_tmp_rec IN wsh_glbl_var_strct_grp.sync_tmp_rec_type,
15 x_return_status OUT NOCOPY VARCHAR2
16 )
17 IS
18 --{
19 --
20 l_debug_on BOOLEAN;
21 --
22 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MERGE';
23 --
24 cursor l_sync_tmp_rec_csr (p_del_det_id IN NUMBER,
25 p_opn_type IN VARCHAR2,
26 l_hw_date IN DATE) is
27 select 'X'
28 from wsh_wms_sync_tmp
29 WHERE delivery_detail_id = p_del_det_id
30 AND operation_type= p_opn_type
31 AND creation_date = l_hw_date;
32
33 l_rec_exists VARCHAR2(10);
34 --}
35 BEGIN
36 --{
37 --
38 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
39 --
40 IF l_debug_on IS NULL
41 THEN
42 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
43 END IF;
44 --
45 --
46 -- Debug Statements
47 --
48 IF l_debug_on THEN
49 WSH_DEBUG_SV.push(l_module_name);
50 WSH_DEBUG_SV.log(l_module_name, 'Delivery Detail Id', p_sync_tmp_rec.delivery_detail_id);
51 WSH_DEBUG_SV.log(l_module_name, 'Delivery Id', p_sync_tmp_rec.delivery_id);
52 WSH_DEBUG_SV.log(l_module_name, 'Parent Delivery Detail Id', p_sync_tmp_rec.parent_delivery_detail_id);
53 WSH_DEBUG_SV.log(l_module_name, 'Operation Type', p_sync_tmp_rec.operation_type);
54 WSH_DEBUG_SV.log(l_module_name, 'WSH_WMS_LPN_GRP.G_HW_TIME_STAMP', WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
55
56 END IF;
57 --
58 SAVEPOINT WSH_WMS_SYNC_TMP_PKG_MERGE;
59 --
60 x_return_status := wsh_util_core.g_ret_sts_success;
61
62 IF (WSH_WMS_LPN_GRP.G_HW_TIME_STAMP IS NULL) THEN
63 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP := sysdate;
64 END IF;
65 --
66 IF (p_sync_tmp_rec.operation_type IN ('UPDATE', 'PRIOR')) THEN
67 --{
68 --
69 /*
70 MERGE INTO WSH_WMS_SYNC_TMP T
71 USING (SELECT delivery_detail_id
72 FROM WSH_WMS_SYNC_TMP
73 WHERE delivery_detail_id = p_sync_tmp_rec.delivery_detail_id
74 AND operation_type= p_sync_tmp_rec.operation_type
75 AND creation_date > WSH_WMS_LPN_GRP.G_HW_TIME_STAMP) S
76 ON (T.delivery_detail_id = S.delivery_detail_id)
77 WHEN MATCHED THEN
78 UPDATE
79 SET T.temp_col = null
80 WHEN NOT MATCHED THEN
81 INSERT (T.delivery_detail_id,
82 T.parent_delivery_detail_id,
83 T.delivery_id,
84 T.operation_type,
85 T.creation_date)
86 VALUES (p_sync_tmp_rec.delivery_detail_id,
87 p_sync_tmp_rec.parent_delivery_detail_id,
88 p_sync_tmp_rec.delivery_id,
89 p_sync_tmp_rec.operation_type,
90 sysdate);
91 */
92 -- The above stmt does not work. Therefore, using the following logic.
93 open l_sync_tmp_rec_csr(p_sync_tmp_rec.delivery_detail_id,
94 p_sync_tmp_rec.operation_type,
95 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
96 fetch l_sync_tmp_rec_csr into l_rec_exists;
97 close l_sync_tmp_rec_csr;
98
99 IF (l_rec_exists is null) THEN
100 insert into WSH_WMS_SYNC_TMP
101 (delivery_detail_id,
102 parent_delivery_detail_id,
103 delivery_id,
104 operation_type,
105 creation_date,
106 call_level)
107 values (p_sync_tmp_rec.delivery_detail_id,
108 p_sync_tmp_rec.parent_delivery_detail_id,
109 p_sync_tmp_rec.delivery_id,
110 p_sync_tmp_rec.operation_type,
111 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP,
112 p_sync_tmp_rec.call_level);
113
114 IF l_debug_on THEN
115 WSH_DEBUG_SV.logmsg(l_module_name, 'INSERTED '||SQL%ROWCOUNT||' RECORDS IN WSH_WMS_SYNC_TMP' );
116 END IF;
117 END IF;
118 --
119 --
120 --}
121 ELSE
122 --{
123 --
124 IF l_debug_on THEN
125 WSH_DEBUG_SV.log(l_module_name, 'Invalid Operation Type', p_sync_tmp_rec.operation_type);
126 END IF;
127 x_return_status := wsh_util_core.g_ret_sts_error;
128 --
129 --}
130 END IF;
131 --
132 -- Debug Statements
133 --
134 IF l_debug_on THEN
135 WSH_DEBUG_SV.pop(l_module_name);
136 END IF;
137 --
138 --}
139 EXCEPTION
140 --{
141 --
142 WHEN OTHERS THEN
143 ROLLBACK TO WSH_WMS_SYNC_TMP_PKG_MERGE;
144 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
145 wsh_util_core.default_handler('WSH_WMS_SYNC_TMP_PKG.MERGE',l_module_name);
146 --
147 -- Debug Statements
148 --
149 IF l_debug_on THEN
150 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
151 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
152 END IF;
153 --
154 --}
155 END MERGE;
156 --
157 --
158 PROCEDURE MERGE_BULK
159 (
160 p_sync_tmp_recTbl IN wsh_glbl_var_strct_grp.sync_tmp_recTbl_type,
161 x_return_status OUT NOCOPY VARCHAR2,
162 p_operation_type IN VARCHAR2
163 )
164 IS
165 --{
166 l_operation_type WSH_WMS_SYNC_TMP.OPERATION_TYPE%TYPE;
167 l_first NUMBER;
168 l_last NUMBER;
169
170 l_sync_tmp_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
171 l_tbl_cnt NUMBER;
172 l_del_tbl_cnt NUMBER;
173 l_call_tbl_cnt NUMBER;
174 --
175 cursor l_sync_tmp_rec_csr (p_del_det_id IN NUMBER,
176 p_opn_type IN VARCHAR2,
177 l_hw_date IN DATE) is
178 select 'X'
179 from wsh_wms_sync_tmp
180 WHERE delivery_detail_id = p_del_det_id
181 AND operation_type= p_opn_type
182 AND creation_date = l_hw_date;
183
184 l_rec_exists VARCHAR2(10);
185 --
186 l_debug_on BOOLEAN;
187 --
188 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MERGE_BULK';
189 --
190 --}
191 BEGIN
192 --{
193 --
194 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
195 --
196 IF l_debug_on IS NULL
197 THEN
198 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
199 END IF;
200 --
201 --
202 -- Debug Statements
203 --
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.push(l_module_name);
206 WSH_DEBUG_SV.log(l_module_name, 'Input operation type', p_operation_type);
207 WSH_DEBUG_SV.log(l_module_name, 'Count of Input Table ', p_sync_tmp_recTbl.delivery_detail_id_tbl.count);
208 WSH_DEBUG_SV.log(l_module_name, 'WSH_WMS_LPN_GRP.G_HW_TIME_STAMP', WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
209 END IF;
210 --
211 SAVEPOINT WSH_WMS_SYNC_TMP_PKG_MRG_BULK;
212 --
213 x_return_status := wsh_util_core.g_ret_sts_success;
214 l_first := p_sync_tmp_recTbl.delivery_detail_id_tbl.first;
215 l_last := p_sync_tmp_recTbl.delivery_detail_id_tbl.last;
216 l_operation_type := nvl(p_operation_type,p_sync_tmp_recTbl.operation_type_tbl(l_first));
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.log(l_module_name, 'operation type', l_operation_type);
219 END IF;
220
221 IF (WSH_WMS_LPN_GRP.G_HW_TIME_STAMP IS NULL) THEN
222 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP := sysdate;
223 END IF;
224 /*
225 IF (l_operation_type = 'UPDATE') THEN
226 --{
227 FORALL i in l_first..l_last
228 MERGE into WSH_WMS_SYNC_TMP T
229 USING ( SELECT delivery_detail_id
230 FROM WSH_WMS_SYNC_TMP
231 WHERE delivery_detail_id = p_sync_tmp_recTbl.delivery_detail_id_tbl(i)
232 AND operation_type= p_sync_tmp_recTbl.operation_type_tbl(i)
233 AND creation_date > WSH_WMS_LPN_GRP.G_HW_TIME_STAMP) S
234 on (T.delivery_detail_id = S.delivery_detail_id)
235 WHEN MATCHED THEN
236 UPDATE
237 SET T.temp_col = null
238 WHEN NOT MATCHED THEN
239 INSERT (T.delivery_detail_id,
240 T.parent_delivery_detail_id,
241 T.delivery_id,
242 T.operation_type,
243 T.creation_date)
244 VALUES (p_sync_tmp_recTbl.delivery_detail_id_tbl(i),
245 NULL,
246 NULL,
247 l_operation_type,
248 sysdate);
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.logmsg(l_module_name, 'MERGED '||SQL%ROWCOUNT||' RECORDS IN WSH_WMS_SYNC_TMP - 1' );
252 END IF;
253 --
254 --}
255 ELSIF (l_operation_type = 'PRIOR') THEN
256 --{
257 FORALL i in l_first..l_last
258 MERGE into WSH_WMS_SYNC_TMP T
259 USING ( SELECT delivery_detail_id
260 FROM WSH_WMS_SYNC_TMP
261 WHERE delivery_detail_id = p_sync_tmp_recTbl.delivery_detail_id_tbl(i)
262 AND operation_type= p_sync_tmp_recTbl.operation_type_tbl(i)
263 AND creation_date > WSH_WMS_LPN_GRP.G_HW_TIME_STAMP) S
264 on (T.delivery_detail_id = S.delivery_detail_id)
265 WHEN MATCHED THEN
266 UPDATE
267 SET T.temp_col = null
268 WHEN NOT MATCHED THEN
269 INSERT (T.delivery_detail_id,
270 T.parent_delivery_detail_id,
271 T.delivery_id,
272 T.operation_type,
273 T.creation_date)
274 VALUES (p_sync_tmp_recTbl.delivery_detail_id_tbl(i),
275 p_sync_tmp_recTbl.parent_detail_id_tbl(i),
276 p_sync_tmp_recTbl.delivery_id_tbl(i),
277 l_operation_type,
278 sysdate);
279 --
280 IF l_debug_on THEN
281 WSH_DEBUG_SV.logmsg(l_module_name, 'MERGED '||SQL%ROWCOUNT||' RECORDS IN WSH_WMS_SYNC_TMP - 2' );
282 END IF;
283 --
284 --}
285 ELSE
286 --{
287 --
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.log(l_module_name, 'Invalid Operation Type', l_operation_type);
290 END IF;
291 x_return_status := wsh_util_core.g_ret_sts_error;
292 --
293 --}
294 END IF;
295 */
296 --
297 l_tbl_cnt := 1;
298 l_del_tbl_cnt := p_sync_tmp_recTbl.delivery_id_tbl.count;
299 --
300 l_call_tbl_cnt := p_sync_tmp_recTbl.call_level.COUNT;
301
302 IF l_debug_on THEN
303 WSH_DEBUG_SV.log(l_module_name, 'l_del_tbl_cnt', l_del_tbl_cnt);
304 WSH_DEBUG_SV.log(l_module_name, 'l_call_tbl_cnt', l_call_tbl_cnt);
305 END IF;
306 --
307
308 FOR i in l_first..l_last LOOP
309 --{
310 -- The above stmt does not work. Therefore, using the following logic.
311 open l_sync_tmp_rec_csr(p_sync_tmp_recTbl.delivery_detail_id_tbl(i),
312 l_operation_type,
313 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
314 fetch l_sync_tmp_rec_csr into l_rec_exists;
315 close l_sync_tmp_rec_csr;
316
317 IF (l_rec_exists is null) THEN
318 --
319 l_sync_tmp_recTbl.delivery_detail_id_tbl(l_tbl_cnt) := p_sync_tmp_recTbl.delivery_detail_id_tbl(i);
320 IF l_call_tbl_cnt = 0 THEN
321 l_sync_tmp_recTbl.call_level(l_tbl_cnt) := NULL;
322 ELSE
323 l_sync_tmp_recTbl.call_level(l_tbl_cnt) := p_sync_tmp_recTbl.call_level(i);
324 END IF;
325
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.log(l_module_name, 'call_level', l_sync_tmp_recTbl.call_level(l_tbl_cnt));
328 END IF;
329
330 IF (l_del_tbl_cnt >0) THEN
331 l_sync_tmp_recTbl.delivery_id_tbl(l_tbl_cnt) := p_sync_tmp_recTbl.delivery_id_tbl(i);
332 l_sync_tmp_recTbl.parent_detail_id_tbl(l_tbl_cnt) := p_sync_tmp_recTbl.parent_detail_id_tbl(i);
333
334 END IF;
335 l_tbl_cnt := l_tbl_cnt + 1;
336 --
337 ELSE
338 --
339 l_rec_exists := NULL;
340 --
341 END IF;
342 --}
343 END LOOP;
344
345 IF l_debug_on THEN
346 WSH_DEBUG_SV.log(l_module_name, 'count of l_sync_tmp_recTbl is ',l_sync_tmp_recTbl.delivery_detail_id_tbl.count);
347 END IF;
348 IF (l_sync_tmp_recTbl.delivery_detail_id_tbl.count > 0) THEN
349 --{
350 IF (l_operation_type = 'PRIOR') THEN
351 --
352 FORALL i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last
353 insert into WSH_WMS_SYNC_TMP
354 (delivery_detail_id,
355 parent_delivery_detail_id,
356 delivery_id,
357 operation_type,
358 creation_date)
359 VALUES (l_sync_tmp_recTbl.delivery_detail_id_tbl(i),
360 l_sync_tmp_recTbl.parent_detail_id_tbl(i),
361 l_sync_tmp_recTbl.delivery_id_tbl(i),
362 l_operation_type,
363 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP);
364 --
365 IF l_debug_on THEN
366 WSH_DEBUG_SV.logmsg(l_module_name, 'INSERTED '||SQL%ROWCOUNT||' RECORDS IN WSH_WMS_SYNC_TMP - 1' );
367 END IF;
368 --
369 ELSIF (l_operation_type = 'UPDATE') THEN
370 --
371 FORALL i in l_sync_tmp_recTbl.delivery_detail_id_tbl.first..l_sync_tmp_recTbl.delivery_detail_id_tbl.last
372 insert into WSH_WMS_SYNC_TMP
373 (delivery_detail_id,
374 parent_delivery_detail_id,
375 delivery_id,
376 operation_type,
377 creation_date,
378 call_level)
379 VALUES (l_sync_tmp_recTbl.delivery_detail_id_tbl(i),
380 NULL,
381 NULL,
382 l_operation_type,
383 WSH_WMS_LPN_GRP.G_HW_TIME_STAMP,
384 l_sync_tmp_recTbl.call_level(i));
385 --
386 IF l_debug_on THEN
387 WSH_DEBUG_SV.logmsg(l_module_name, 'INSERTED '||SQL%ROWCOUNT||' RECORDS IN WSH_WMS_SYNC_TMP - 1' );
388 END IF;
389 --
390 ELSE
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.log(l_module_name, 'Invalid Operation Type', l_operation_type);
394 END IF;
395 x_return_status := wsh_util_core.g_ret_sts_error;
396 --
397 END IF;
398 --}
399 END IF;
400 -- Debug Statements
401 --
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.pop(l_module_name);
404 END IF;
405 --
406 --}
407 --
408 EXCEPTION
409 --{
410 --
411 WHEN OTHERS THEN
412 --
413 ROLLBACK TO WSH_WMS_SYNC_TMP_PKG_MRG_BULK;
414 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
415 wsh_util_core.default_handler('WSH_WMS_SYNC_TMP_PKG.MERGE',l_module_name);
416 --
417 -- Debug Statements
418 --
419 IF l_debug_on THEN
420 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
422 END IF;
423 --
424 --}
425 END MERGE_BULK;
426
427 --
428
429 END WSH_WMS_SYNC_TMP_PKG;