[Home] [Help]
PACKAGE BODY: APPS.RLM_REPLACE_SV
Source
1 PACKAGE BODY RLM_REPLACE_SV as
2 /*$Header: RLMDPSWB.pls 120.1.12010000.2 2009/12/01 14:42:49 sunilku ship $*/
3 --
4 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
5 --
6 /*===========================================================================
7
8 PROCEDURE NAME: CompareReplaceSched
9
10 ===========================================================================*/
11 PROCEDURE CompareReplaceSched (
12 x_sched_rec IN rlm_interface_headers%ROWTYPE,
13 x_warn_dropped_items IN VARCHAR2,
14 x_return_status OUT NOCOPY BOOLEAN)
15 IS
16 --
17 v_prev_header_id NUMBER;
18 v_curr_header_id NUMBER;
19 v_curr_sch_header_id NUMBER; --Bugfix 8844817
20 v_return_status BOOLEAN;
21 --
22 BEGIN
23 --
24 IF (l_debug <> -1) THEN
25 rlm_core_sv.dpush(C_SDEBUG,'CompareReplaceSched');
26 END IF;
27 --
28 v_curr_header_id := x_sched_rec.header_id;
29 v_curr_sch_header_id := x_sched_rec.schedule_header_id; --Bugfix 8844817
30 --
31 IF (l_debug <> -1) THEN
32 rlm_core_sv.dlog(C_DEBUG, 'v_curr_header_id', v_curr_header_id);
33 rlm_core_sv.dlog(C_DEBUG, 'v_curr_sch_header_id', v_curr_sch_header_id); --Bugfix 8844817
34 END IF;
35 --
36 IF IsWarningNeeded(x_sched_rec, x_warn_dropped_items) THEN
37 --
38 FindEligibleSched(x_sched_rec,
39 v_prev_header_id,
40 v_return_status);
41 --
42 IF v_return_status <> FALSE THEN
43 --
44 IF (l_debug <> -1) THEN
45 rlm_core_sv.dlog(C_DEBUG, 'v_prev_header_id',v_prev_header_id);
46 END IF;
47 --
48 -- Populate the global list
49 --
50 -- 4198327
51 PopulateList(v_prev_header_id,
52 v_curr_header_id,
53 v_curr_sch_header_id, --Bugfix 8844817
54 v_return_status);
55 --
56 x_return_status := TRUE;
57 --
58 ELSE
59 --
60 -- Even though no previous schedule is found,
61 -- the return status to wrapper program should be TRUE
62 --
63 x_return_status := TRUE;
64 --
65 END IF;
66 --
67 ELSE
68 --
69 -- Warning is not needed. The return status should still be TRUE
70 --
71 x_return_status := TRUE;
72 --
73 END IF;
74 --
75 IF (l_debug <> -1) THEN
76 rlm_core_sv.dpop(C_SDEBUG);
77 END IF;
78 --
79 EXCEPTION
80 --
81 WHEN OTHERS THEN
82 --
83 x_return_status := FALSE;
84 --
85 IF (l_debug <> -1) THEN
86 rlm_core_sv.dpop(C_SDEBUG, 'EXCEPTION: OTHER - ' || SUBSTR(SQLERRM,1,200));
87 END IF;
88 --
89 END CompareReplaceSched;
90
91
92 /*===========================================================================
93 FUNCTION NAME: IsWarningNeeded
94
95 ===========================================================================*/
96 FUNCTION IsWarningNeeded (
97 x_sched_rec IN rlm_interface_headers%ROWTYPE,
98 x_warn_dropped_items IN VARCHAR2)
99 RETURN BOOLEAN
100 IS
101 BEGIN
102 --
103 IF (l_debug <> -1) THEN
104 rlm_core_sv.dpush(C_SDEBUG,'IsWarningNeeded');
105 rlm_core_sv.dlog(C_DEBUG, 'x_warn_dropped_items', x_warn_dropped_items);
106 rlm_core_sv.dlog(C_DEBUG, 'schedule source', x_sched_rec.schedule_source);
107 rlm_core_sv.dlog(C_DEBUG, 'schedule purpose', x_sched_rec.schedule_purpose);
108 END IF;
109 --
110 IF x_warn_dropped_items <> 'Y'
111 AND x_warn_dropped_items <> 'y' THEN
112 --
113 IF (l_debug <> -1) THEN
114 rlm_core_sv.dpop(C_SDEBUG);
115 END IF;
116 --
117 RETURN FALSE;
118 --
119 END IF;
120 --
121 IF x_sched_rec.schedule_purpose not in ('REPLACE', 'REPLACE_ALL') THEN
122 --
123 IF (l_debug <> -1) THEN
124 rlm_core_sv.dpop(C_SDEBUG);
125 END IF;
126 --
127 RETURN FALSE;
128 --
129 END IF;
130 --
131 IF x_sched_rec.schedule_source = 'MANUAL' THEN
132 --
133 IF (l_debug <> -1) THEN
134 rlm_core_sv.dpop(C_SDEBUG);
135 END IF;
136 --
137 RETURN FALSE;
138 --
139 END IF;
140 --
141 IF (l_debug <> -1) THEN
142 rlm_core_sv.dpop(C_SDEBUG);
143 END IF;
144 --
145 RETURN TRUE;
146 --
147 END IsWarningNeeded;
148
149 /*===========================================================================
150
151 PROCEDURE NAME: FindEligibleSched
152
153 ===========================================================================*/
154 PROCEDURE FindEligibleSched (
155 x_sched_rec IN rlm_interface_headers%ROWTYPE,
156 x_prev_header_id OUT NOCOPY NUMBER,
157 x_return_status OUT NOCOPY BOOLEAN)
158 IS
159 --
160 CURSOR c_eligible_schedule IS
161 SELECT header_id
162 FROM rlm_schedule_headers
163 WHERE schedule_type = x_sched_rec.schedule_type
164 AND schedule_purpose in (RLM_DP_SV.k_REPLACE, RLM_DP_SV.k_REPLACE_ALL)
165 AND schedule_source <> RLM_DP_SV.k_MANUAL
166 -- AND customer_id = x_sched_rec.customer_id
167 AND ece_tp_translator_code = x_sched_rec.ece_tp_translator_code
168 AND ece_tp_location_code_ext = x_sched_rec.ece_tp_location_code_ext
169 AND NVL(edi_test_indicator,'P') = NVL(x_sched_rec.edi_test_indicator,'P')
170 AND process_status IN (5,7)
171 AND sched_generation_date < x_sched_rec.sched_generation_date
172 -- bug 4198327
173 ORDER BY sched_generation_date DESC, program_update_date desc;
174 --
175 e_no_eligible_schedule EXCEPTION;
176 --
177 BEGIN
178 --
179 IF (l_debug <> -1) THEN
180 rlm_core_sv.dpush(C_SDEBUG,'FindEligibleSched');
181 rlm_core_sv.dlog(C_DEBUG,'Curr ece_tp_translator_code', x_sched_rec.ece_tp_translator_code);
182 rlm_core_sv.dlog(C_DEBUG,'Curr ece_tp_location_code_ext', x_sched_rec.ece_tp_location_code_ext);
183 rlm_core_sv.dlog(C_DEBUG,'Curr edi_test_indicator', x_sched_rec.edi_test_indicator);
184 rlm_core_sv.dlog(C_DEBUG,'Curr sched_generation_date', x_sched_rec.sched_generation_date);
185 END IF;
186 --
187 OPEN c_eligible_schedule;
188 -- Get the second highest generation date schedule only
189 FETCH c_eligible_schedule INTO x_prev_header_id;
190 --
191 IF c_eligible_schedule%NOTFOUND THEN
192 --
193 RAISE e_no_eligible_schedule;
194 --
195 END IF;
196 --
197 CLOSE c_eligible_schedule;
198 --
199 x_return_status := TRUE;
200 --
201 IF (l_debug <> -1) THEN
202 rlm_core_sv.dlog(C_DEBUG, 'x_return_status', x_return_status);
203 rlm_core_sv.dpop(C_SDEBUG);
204 END IF;
205 --
206 EXCEPTION
207 --
208 WHEN e_no_eligible_schedule THEN
209 --
210 x_return_status := FALSE;
211 --
212 IF (l_debug <> -1) THEN
213 rlm_core_sv.dpop(C_SDEBUG, 'e_no_eligible_schedule');
214 END IF;
215 --
216 WHEN OTHERS THEN
217 --
218 x_return_status := FALSE;
219 --
220 IF (l_debug <> -1) THEN
221 rlm_core_sv.dpop(C_SDEBUG, 'EXCEPTION: OTHER - ' || SUBSTR(SQLERRM,1,200));
222 END IF;
223 --
224 END FindEligibleSched;
225
226
227 /*===========================================================================
228
229 PROCEDURE NAME: PopulateList
230
231 ===========================================================================*/
232 PROCEDURE PopulateList (
233 x_prev_header_id IN NUMBER,
234 x_curr_header_id IN NUMBER,
235 x_curr_sch_header_id IN NUMBER, --Bugfix 8844817
236 x_return_status OUT NOCOPY BOOLEAN)
237 IS
238 --
239 -- bug 4198327 changes include getting all the items which are not in the current schedule
240 -- but on the previous schedule. Get the list, then check if there are open order lines
241 -- for that item.
242 --
243 CURSOR c_drop_list IS
244 SELECT rsl.ship_from_org_id,
245 rsl.ship_to_address_id,
246 rsl.ship_to_org_id,
247 rsl.customer_item_id
248 FROM rlm_schedule_lines_all rsl
249 WHERE rsl.header_id = x_prev_header_id
250 AND rsl.item_detail_type IN (0,1,2)
251 AND NOT EXISTS ( SELECT 'X'
252 FROM rlm_interface_lines ril
253 WHERE ril.header_id = x_curr_header_id
254 AND ril.ship_from_org_id = rsl.ship_from_org_id
255 AND ril.ship_to_address_id = rsl.ship_to_address_id
256 AND ril.ship_to_org_id = rsl.ship_to_org_id
257 AND ril.customer_item_id = rsl.customer_item_id
258 AND ril.item_detail_type IN (0,1,2))
259 AND NOT EXISTS ( SELECT 'X' --Bugfix 8844817
260 FROM rlm_schedule_lines rsl2
261 WHERE rsl2.header_id = x_curr_sch_header_id
262 AND rsl2.ship_from_org_id = rsl.ship_from_org_id
263 AND rsl2.ship_to_address_id = rsl.ship_to_address_id
264 AND rsl2.ship_to_org_id = rsl.ship_to_org_id
265 AND rsl2.customer_item_id = rsl.customer_item_id
266 AND rsl2.item_detail_type IN (0,1,2))
267 GROUP BY rsl.ship_from_org_id,
268 rsl.ship_to_address_id,
269 rsl.ship_to_org_id,
270 rsl.customer_item_id;
271 --
272 CURSOR get_order_info (v_ship_from_org_id NUMBER,
273 v_ship_to_org_id NUMBER,
274 v_customer_item_id NUMBER) IS
275 SELECT oeh.order_number , oeh.header_id
276 FROM oe_order_lines_all oel ,
277 oe_order_headers oeh
278 WHERE oeh.header_id = oel.header_id
279 AND oel.ship_from_org_id = v_ship_from_org_id
280 AND oel.ship_to_org_id = v_ship_to_org_id
281 AND oel.ordered_item_id = v_customer_item_id
282 AND oel.open_flag = 'Y'
283 AND oel.source_document_type_id = '5';
284 --
285 v_order_number NUMBER;
286 v_header_id NUMBER;
287 --
288 i NUMBER DEFAULT 0;
289 --
290 BEGIN
291 --
292 IF (l_debug <> -1) THEN
293 rlm_core_sv.dpush(C_SDEBUG, 'PopulateList');
294 END IF;
295 --
296 FOR drop_list IN c_drop_list LOOP
297 --
298 IF (l_debug <> -1) THEN
299 rlm_core_sv.dlog(C_DEBUG, 'Missing items on the schedule');
300 rlm_core_sv.dlog(C_DEBUG, 'Ship From:', drop_list.ship_from_org_id);
301 rlm_core_sv.dlog(C_DEBUG, 'Ship To Address Id:', drop_list.ship_to_address_id);
302 rlm_core_sv.dlog(C_DEBUG, 'ST Org Id:', drop_list.ship_to_org_id);
303 rlm_core_sv.dlog(C_DEBUG, 'Customer Item:', drop_list.customer_item_id);
304 END IF;
305 --
306 v_order_number := NULL;
307 v_header_id := NULL;
308 --
309 BEGIN
310 --
311 OPEN get_order_info (drop_list.ship_from_org_id,
312 drop_list.ship_to_org_id,
313 drop_list.customer_item_id);
314 --
315 FETCH get_order_info INTO v_order_number,v_header_id;
316 --
317 CLOSE get_order_info;
318 --
319 IF v_header_id is NOT NULL THEN
320 --
321 IF (l_debug <> -1) THEN
322 rlm_core_sv.dlog(C_DEBUG, 'RLM_DROPPED_ITEMS');
323 rlm_core_sv.dlog(C_DEBUG, 'SF', drop_list.ship_from_org_id);
324 rlm_core_sv.dlog(C_DEBUG, 'ST', drop_list.ship_to_address_id);
325 rlm_core_sv.dlog(C_DEBUG, 'CI', drop_list.customer_item_id);
326 rlm_core_sv.dlog(C_DEBUG, 'Order Number', v_order_number);
327 rlm_core_sv.dlog(C_DEBUG, 'Header_id', v_header_id);
328 END IF;
329 --
330 rlm_message_sv.app_error(
331 x_ExceptionLevel => rlm_message_sv.k_warn_level,
332 x_MessageName => 'RLM_WARN_DROPPED_ITEMS',
333 x_InterfaceHeaderId => x_curr_header_id,
334 x_InterfaceLineId => NULL,
335 x_token1 => 'SF',
336 x_value1 => RLM_CORE_SV.get_ship_from(drop_list.ship_from_org_id),
337 x_token2 => 'ST',
338 x_value2 => RLM_CORE_SV.get_ship_to(drop_list.ship_to_address_id),
339 x_token3 => 'CI',
340 x_value3 => RLM_CORE_SV.get_item_number(drop_list.customer_item_id),
341 x_token4 => 'ORDER_NO',
342 x_value4 => v_order_number);
343 --
344 ELSE
345 --
346 IF (l_debug <> -1) THEN
347 rlm_core_sv.dlog(C_DEBUG, 'No Open order line exists for below combination of '|| 'SF' ||','|| 'ST'||' and '|| 'CI.' );
348 rlm_core_sv.dlog(C_DEBUG, 'SF', drop_list.ship_from_org_id);
349 rlm_core_sv.dlog(C_DEBUG, 'ST', drop_list.ship_to_address_id);
350 rlm_core_sv.dlog(C_DEBUG, 'CI', drop_list.customer_item_id);
351 END IF ;
352 --
353 END IF ;
354 --
355 EXCEPTION
356 --
357 WHEN OTHERS THEN
358 --
359 IF (l_debug <> -1) THEN
360 rlm_core_sv.dlog(C_DEBUG, 'In the when others of Populate Lists');
361 rlm_core_sv.dlog(C_DEBUG, 'EXCEPTION: OTHER - ' || SUBSTR(SQLERRM,1,200));
362 END IF ;
363 --
364 END ;
365 --
366 END LOOP;
367 --
368 x_return_status := TRUE;
369 --
370 IF (l_debug <> -1) THEN
371 -- rlm_core_sv.dlog(C_DEBUG, 'g_list_tbl.COUNT', g_list_tbl.COUNT);
372 rlm_core_sv.dlog(C_DEBUG, 'x_return_status', x_return_status);
373 rlm_core_sv.dpop(C_SDEBUG);
374 END IF;
375 --
376 EXCEPTION
377 --
378 WHEN OTHERS THEN
379 --
380 x_return_status := FALSE;
381 --
382 IF (l_debug <> -1) THEN
383 rlm_core_sv.dpop(C_SDEBUG, 'EXCEPTION: OTHER - ' || SUBSTR(SQLERRM,1,200));
384 END IF;
385 --
386
387 END PopulateList;
388
389
390 /*===========================================================================
391
392 PROCEDURE NAME: CompareList
393
394 ===========================================================================*/
395 PROCEDURE CompareList (
396 x_curr_header_id IN NUMBER)
397 IS
398 --
399 v_exist_count VARCHAR2(10);
400 --
401 i NUMBER;
402 --
403 BEGIN
404 --
405 IF (l_debug <> -1) THEN
406 rlm_core_sv.dpush(C_SDEBUG, 'CompareList');
407 rlm_core_sv.dlog(C_DEBUG, 'g_list_tbl.COUNT', g_list_tbl.COUNT);
408 END IF;
409 --
410 --
411 -- Bug 2778728 : Proceed further only if g_list_tbl has any entries
412 --
413 IF g_list_tbl.COUNT > 0 THEN
414 --
415 i := g_list_tbl.FIRST;
416 --
417 IF (l_debug <> -1) THEN
418 rlm_core_sv.dlog(C_DEBUG, 'Starting Loop');
419 END IF;
420 --
421 LOOP
422 --
423 IF (l_debug <> -1) THEN
424 rlm_core_sv.dlog(C_DEBUG, 'ship_from_org_id', g_list_tbl(i).ship_from_org_id);
425 rlm_core_sv.dlog(C_DEBUG, 'ship_to_address_id', g_list_tbl(i).ship_to_address_id);
426 rlm_core_sv.dlog(C_DEBUG, 'customer_item_id', g_list_tbl(i).customer_item_id);
427 END IF;
428 --
429 SELECT count(1)
430 INTO v_exist_count
431 FROM rlm_interface_lines_all
432 WHERE header_id = x_curr_header_id
433 AND ship_from_org_id = g_list_tbl(i).ship_from_org_id
434 AND ship_to_address_id = g_list_tbl(i).ship_to_address_id
435 AND customer_item_id = g_list_tbl(i).customer_item_id;
436 --
437 IF (l_debug <> -1) THEN
438 rlm_core_sv.dlog(C_DEBUG, 'i', i);
439 rlm_core_sv.dlog(C_DEBUG, 'v_exist_count', v_exist_count);
440 END IF;
441 --
442 IF v_exist_count > 0 THEN
443 --
444 EXIT WHEN i = g_list_tbl.LAST;
445 --
446 i := g_list_tbl.NEXT(i);
447 --
448 ELSE
449 --
450 IF (l_debug <> -1) THEN
451 rlm_core_sv.dlog(C_DEBUG, 'RLM_DROPPED_ITEMS');
452 rlm_core_sv.dlog(C_DEBUG, 'SF', g_list_tbl(i).ship_from_org_id);
453 rlm_core_sv.dlog(C_DEBUG, 'ST', g_list_tbl(i).ship_to_address_id);
454 rlm_core_sv.dlog(C_DEBUG, 'CI', g_list_tbl(i).customer_item_id);
455 rlm_core_sv.dlog(C_DEBUG, 'Order Number', g_list_tbl(i).order_number);
456 END IF;
457 --
458 rlm_message_sv.app_error(
459 x_ExceptionLevel => rlm_message_sv.k_warn_level,
460 x_MessageName => 'RLM_WARN_DROPPED_ITEMS',
461 x_InterfaceHeaderId => x_curr_header_id,
462 x_InterfaceLineId => NULL,
463 x_token1 => 'SF',
464 x_value1 => RLM_CORE_SV.get_ship_from(g_list_tbl(i).ship_from_org_id),
465 x_token2 => 'ST',
466 x_value2 => RLM_CORE_SV.get_ship_to(g_list_tbl(i).ship_to_address_id),
467 x_token3 => 'CI',
468 x_value3 => RLM_CORE_SV.get_item_number(g_list_tbl(i).customer_item_id),
469 x_token4 => 'ORDER_NO',
470 x_value4 => g_list_tbl(i).order_number);
471 --
472 EXIT WHEN i = g_list_tbl.LAST;
473 --
474 i := g_list_tbl.NEXT(i);
475 --
476 END IF;
477 --
478 END LOOP;
479 --
480 END IF; /* g_list_tbl.COUNT > 0 */
481 --
482 IF (l_debug <> -1) THEN
483 rlm_core_sv.dpop(C_SDEBUG);
484 END IF;
485 --
486 EXCEPTION
487 --
488 WHEN OTHERS THEN
489 --
490 IF (l_debug <> -1) THEN
491 rlm_core_sv.dpop(C_SDEBUG, 'EXCEPTION: OTHER - ' || SUBSTR(SQLERRM,1,200));
492 END IF;
493 --
494
495 END CompareList;
496
497 END RLM_REPLACE_SV;