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