[Home] [Help]
PACKAGE BODY: APPS.RLM_PS_SV
Source
1 PACKAGE BODY RLM_PS_SV as
2 /*$Header: RLMDPPSB.pls 120.5.12010000.2 2009/09/01 07:46:29 sunilku ship $*/
3 /*========================== rlm_ps_sv========================*/
4 --
5 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
6 TYPE g_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 g_BindVarTab RLM_CORE_SV.t_dynamic_tab;
8 g_interface_headers_tab g_number_tbl_type;
9 g_schedule_headers_tab g_number_tbl_type;
10 --
11 PROCEDURE PurgeSchedule(errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY NUMBER,
13 p_org_id NUMBER,
14 p_execution_mode VARCHAR2,
15 p_translator_code_from VARCHAR2,
16 p_translator_code_to VARCHAR2,
17 p_customer VARCHAR2,
18 p_ship_to_address_id_from NUMBER,
19 p_ship_to_address_id_to NUMBER,
20 p_issue_date_from VARCHAR2,
21 p_issue_date_to VARCHAR2,
22 p_schedule_type VARCHAR2,
23 p_schedule_ref_no VARCHAR2 ,
24 p_delete_beyond_days NUMBER,
25 p_authorization VARCHAR2,
26 p_status NUMBER)
27
28 IS
29 --
30 v_from_clause VARCHAR2(32000);
31 v_where_clause VARCHAR2(32000);
32 v_order_clause VARCHAR2(32000);
33 v_forupdate_clause VARCHAR2(32000);
34 v_select_clause VARCHAR2(32000);
35 v_statement_oe VARCHAR2(32000);
36 v_statement_rlm VARCHAR2(32000);
37 v_Progress VARCHAR2(3) := '010';
38 v_WF_Enabled VARCHAR2(1) := 'N';
39 v_cursor_id NUMBER;
40 v_errbuf VARCHAR2(2000);
41 v_retcode NUMBER;
42 v_sched_header_id NUMBER;
43 v_sched_line_id NUMBER;
44 v_interface_header NUMBER;
45 v_interface_line NUMBER;
46 v_order_header NUMBER;
47 v_order_line NUMBER;
48 v_line_count NUMBER;
49 v_line_count2 NUMBER;
50 v_open_flag VARCHAR2(1);
51 v_order_exists BOOLEAN;
52 v_partial_schedule BOOLEAN;
53 x_request_id NUMBER;
54 e_no_data_found EXCEPTION;
55 -- p_org_id NUMBER := NULL;
56 x_purge_rec rlm_message_sv.t_PurExp_rec;
57 v_statement VARCHAR2(32000);
58 v_int_statement VARCHAR2(32000);
59 v_arch_statement VARCHAR2(32000);
60 v_schedule_ref_no NUMBER;
61 v_schedule_source VARCHAR2(10) := 'X';
62 --
63 TYPE ref_demand_cur IS REF CURSOR;
64 c_demand ref_demand_cur;
65 oe_demand ref_demand_cur;
66 --
67 CURSOR c IS
68 SELECT *
69 FROM oe_order_lines_all
70 WHERE line_id = v_order_line;
71 --
72 v_Sched_rec OE_ORDER_LINES_ALL%ROWTYPE;
73 --
74 BEGIN
75 --
76 MO_GLOBAL.set_policy_context(p_access_mode => 'S',
77 p_org_id => p_org_id);
78 --
79 IF (l_debug <> -1) THEN
80 rlm_core_sv.start_debug;
81 rlm_core_sv.dpush(C_SDEBUG,'PurgeSchedule');
82 END IF;
83 --
84 rlm_message_sv.populate_req_id;
85 --
86 IF(p_schedule_ref_no IS NOT NULL) THEN
87 --
88 v_schedule_source := substr(p_schedule_ref_no,1,1);
89 v_schedule_ref_no := to_number(substr(p_schedule_ref_no,2));
90 --
91 END IF;
92 --
93 --where caluse
94 --
95 v_where_clause := BuildQuery (p_execution_mode,
96 p_translator_code_from,
97 p_translator_code_to,
98 p_customer,
99 p_ship_to_address_id_from,
100 p_ship_to_address_id_to,
101 p_issue_date_from,
102 p_issue_date_to,
103 p_schedule_type,
104 v_schedule_ref_no,
105 p_delete_beyond_days,
106 p_authorization,
107 p_status);
108 --
109 --no criteria specified by the user
110 --
111 IF (v_where_clause = 'WHERE rh.header_id=rl.header_id') THEN
112 raise e_no_data_found;
113 END IF;
114 --
115 -- Final Queries
116 --
117 v_arch_statement := 'select distinct rh.header_id
118 from rlm_schedule_headers rh, rlm_schedule_lines_all rl '
119 || v_where_clause
120 || ' and rh.process_status = :k_ps_5'
121 || ' and rh.org_id = rl.org_id';
122
123 v_int_statement := 'select distinct rh.header_id
124 from rlm_interface_headers rh, rlm_interface_lines_all rl '
125 || v_where_clause
126 ||' and 5 = :k_ps_5'
127 ||' and rh.org_id = rl.org_id';
128
129 g_BindVarTab(g_BindVarTab.COUNT+1):=5;
130 --
131 IF (l_debug <> -1) THEN
132 rlm_core_sv.dlog(C_DEBUG, 'v_arch_statement', v_arch_statement);
133 rlm_core_sv.dlog(C_DEBUG, 'v_int_statement', v_int_statement);
134 rlm_core_sv.dlog(C_DEBUG, 'v_schedule_source', v_schedule_source);
135 rlm_core_sv.dlog(C_DEBUG, '============================');
136 rlm_core_sv.dlog(C_DEBUG, 'Printing Bind Variable Values');
137 rlm_core_sv.dlog(C_DEBUG, 'g_BindVarTab.COUNT', g_BindVarTab.COUNT);
138 --
139 FOR i IN 1..g_BindVarTab.COUNT LOOP
140 rlm_core_sv.dlog(C_DEBUG, 'g_BindVarTab('||i||')', g_BindVarTab(i));
141 END LOOP;
142 --
143 rlm_core_sv.dlog(C_DEBUG, '============================');
144 END IF;
145 --
146 -- interface only
147 --
148 IF(p_status = 1 or p_status = 2 or p_status = 3) THEN
149 --{
150 RLM_CORE_SV.OpenDynamicCursor(c_demand, v_int_statement, g_BindVarTab);
151 FETCH c_demand INTO v_sched_header_id;
152 --
153 IF c_demand%NOTFOUND THEN
154 raise e_no_data_found;
155 END IF;
156 --
157 CLOSE c_demand;
158 --
159 IF(v_schedule_source <> 'S') THEN
160 --
161 PurgeInterface(p_execution_mode=>p_execution_mode,
162 p_authorization=>p_authorization,
163 p_ship_to_address_id_from=>p_ship_to_address_id_from,
164 p_ship_to_address_id_to=>p_ship_to_address_id_to,
165 p_statement=>v_int_statement);
166 --
167 ELSE
168 raise e_no_data_found;
169 END IF;
170 --}
171 -- archive only
172 --
173 ELSIF(p_status = 4) THEN
174 --{
175 -- Test for no schedules matching the criteria
176 --
177 RLM_CORE_SV.OpenDynamicCursor(c_demand, v_arch_statement, g_BindVarTab);
178 FETCH c_demand INTO v_sched_header_id;
179 --
180 IF c_demand%NOTFOUND THEN
181 raise e_no_data_found;
182 END IF;
183 --
184 CLOSE c_demand;
185 --
186 IF(v_schedule_source <> 'I') THEN
187 --
188 PurgeArchive( p_execution_mode=>p_execution_mode,
189 p_authorization=>p_authorization,
190 p_ship_to_address_id_from=>p_ship_to_address_id_from,
191 p_ship_to_address_id_to=>p_ship_to_address_id_to,
192 p_statement=>v_arch_statement);
193 --
194 ELSE
195 raise e_no_data_found;
196 END IF;
197 --}
198 ELSE
199 --{
200 -- check for matching interface and archive schedules
201 --
202 RLM_CORE_SV.OpenDynamicCursor(c_demand, v_int_statement, g_BindVarTab);
203 FETCH c_demand INTO v_sched_header_id;
204 --
205 IF c_demand%NOTFOUND THEN
206 --{
207 CLOSE c_demand;
208 RLM_CORE_SV.OpenDynamicCursor(c_demand, v_arch_statement, g_BindVarTab);
209 FETCH c_demand INTO v_sched_header_id;
210 --
211 IF c_demand%NOTFOUND THEN
212 --
213 CLOSE c_demand;
214 raise e_no_data_found;
215 --
216 END IF;
217 --}
218 END IF;
219 --
220 IF(v_schedule_source <> 'S') THEN
221 --
222 PurgeInterface(p_execution_mode=>p_execution_mode,
223 p_authorization=>p_authorization,
224 p_ship_to_address_id_from=>p_ship_to_address_id_from,
225 p_ship_to_address_id_to=>p_ship_to_address_id_to,
226 p_statement=>v_int_statement);
227 --
228 END IF;
229 --
230 IF(v_schedule_source <> 'I') THEN
231 --
232 PurgeArchive( p_execution_mode=>p_execution_mode,
233 p_authorization=>p_authorization,
234 p_ship_to_address_id_from=>p_ship_to_address_id_from,
235 p_ship_to_address_id_to=>p_ship_to_address_id_to,
236 p_statement=>v_arch_statement);
237 --
238 END IF;
239 --}
240 END IF;
241 --
242 -- Purge rlm_demand_exceptions
243 --
244 FORALL counter in 1..g_schedule_headers_tab.COUNT
245 --
246 DELETE from rlm_demand_exceptions
247 where schedule_header_id= g_schedule_headers_tab(counter)
248 and request_id <> fnd_global.conc_request_id;
249 --
250 IF (l_debug <> -1) THEN
251 rlm_core_sv.dlog(C_DEBUG, 'No of Schedule Exception Lines Deleted ', SQL%ROWCOUNT);
252 END IF;
253 --
254 FORALL counter in 1..g_interface_headers_tab.COUNT
255 --
256 DELETE from rlm_demand_exceptions
257 where interface_header_id= g_interface_headers_tab(counter)
258 and request_id <> fnd_global.conc_request_id;
259 --
260 IF (l_debug <> -1) THEN
261 rlm_core_sv.dlog(C_DEBUG, 'No of Interface Exception Lines Deleted ', SQL%ROWCOUNT);
262 END IF;
263 --
264 -- runreport
265 --
266 RunReport (p_org_id => p_org_id,
267 p_execution_mode => p_execution_mode,
268 p_translator_code_from => p_translator_code_from,
269 p_translator_code_to => p_translator_code_to,
270 p_customer => p_customer,
271 p_ship_to_address_id_from => p_ship_to_address_id_from,
272 p_ship_to_address_id_to => p_ship_to_address_id_to,
273 p_issue_date_from => p_issue_date_from,
274 p_issue_date_to => p_issue_date_to,
275 p_schedule_type => p_schedule_type,
276 p_schedule_ref_no => v_schedule_ref_no,
277 p_delete_beyond_days => p_delete_beyond_days,
278 p_authorization => p_authorization,
279 p_status => p_status);
280 --
281 IF (l_debug <> -1) THEN
282 rlm_core_sv.dpop(C_SDEBUG);
283 rlm_core_sv.stop_debug;
284 END IF;
285 --
286 EXCEPTION
287 --
288 WHEN e_no_data_found THEN
289 --
290 IF (l_debug <> -1) THEN
291 rlm_core_sv.dlog(C_DEBUG, 'No schedules to delete' );
292 END IF;
293 --
294 --runreport
295 --
296 RunReport( p_org_id => p_org_id,
297 p_execution_mode => p_execution_mode,
298 p_translator_code_from => p_translator_code_from,
299 p_translator_code_to => p_translator_code_to,
300 p_customer => p_customer,
301 p_ship_to_address_id_from => p_ship_to_address_id_from,
302 p_ship_to_address_id_to => p_ship_to_address_id_to,
303 p_issue_date_from => p_issue_date_from,
304 p_issue_date_to => p_issue_date_to,
305 p_schedule_type => p_schedule_type,
306 p_schedule_ref_no => v_schedule_ref_no,
307 p_delete_beyond_days => p_delete_beyond_days,
308 p_authorization => p_authorization,
309 p_status => p_status);
310 --
311 rlm_message_sv.sql_error('rlm_ps_sv.PurgeSchedule', v_Progress);
312 --
313 IF (l_debug <> -1) THEN
314 rlm_core_sv.dpop(C_SDEBUG);
315 END IF;
316 --
317 WHEN OTHERS THEN
318 --
319 IF (l_debug <> -1) THEN
320 rlm_core_sv.dlog(C_DEBUG, 'When others - Purge Schedule' );
321 END IF;
322 --
323 --runreport
324 --
325 RunReport( p_org_id => p_org_id,
326 p_execution_mode => p_execution_mode,
327 p_translator_code_from => p_translator_code_from,
328 p_translator_code_to => p_translator_code_to,
329 p_customer => p_customer,
330 p_ship_to_address_id_from => p_ship_to_address_id_from,
331 p_ship_to_address_id_to => p_ship_to_address_id_to,
332 p_issue_date_from => p_issue_date_from,
333 p_issue_date_to => p_issue_date_to,
334 p_schedule_type => p_schedule_type,
335 p_schedule_ref_no => v_schedule_ref_no,
336 p_delete_beyond_days => p_delete_beyond_days,
337 p_authorization => p_authorization,
338 p_status => p_status);
339 --
340 rlm_message_sv.sql_error('rlm_ps_sv.PurgeSchedule', v_Progress);
341 rlm_message_sv.dump_messages;
342 --
343 IF (l_debug <> -1) THEN
344 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
345 END IF;
346 raise;
347 --
348 END PurgeSchedule;
349
350
351 /* Purge Archive*/
352
353
354 PROCEDURE PurgeArchive(p_execution_mode VARCHAR2,
355 p_authorization VARCHAR2,
356 p_ship_to_address_id_from NUMBER,
357 p_ship_to_address_id_to NUMBER,
358 p_statement VARCHAR2)
359
360 IS
361
362 v_from_clause VARCHAR2(32000);
363 v_where_clause VARCHAR2(32000);
364 v_order_clause VARCHAR2(32000);
365 v_forupdate_clause VARCHAR2(32000);
366 v_select_clause VARCHAR2(32000);
367 v_statement_oe VARCHAR2(32000);
368 v_statement_rlm VARCHAR2(32000);
369 v_Progress VARCHAR2(3) := '010';
370 v_WF_Enabled VARCHAR2(1) := 'N';
371 v_cursor_id NUMBER;
372 v_errbuf VARCHAR2(2000);
373 v_retcode NUMBER;
374 v_process_status NUMBER;
375 v_sched_header_id NUMBER;
376 v_sched_line_id NUMBER;
377 v_interface_header NUMBER;
378 v_interface_line NUMBER;
379 v_order_header NUMBER;
380 v_order_line NUMBER;
381 v_line_count NUMBER;
382 v_line_count2 NUMBER;
383 v_open_flag VARCHAR2(1);
384 v_order_exists BOOLEAN;
385 v_partial_schedule BOOLEAN;
386 x_request_id NUMBER;
387 e_no_data_found EXCEPTION;
388 p_org_id NUMBER := NULL;
389 x_purge_rec rlm_message_sv.t_PurExp_rec;
390 --
391 TYPE ref_demand_cur IS REF CURSOR;
392 c_demand ref_demand_cur;
393 oe_demand ref_demand_cur;
394 --
395 CURSOR c IS
396 select *
397 from oe_order_lines_all
398 where line_id = v_order_line;
399 --
400 v_Sched_rec OE_ORDER_LINES_ALL%ROWTYPE;
401 --
402 BEGIN
403
404 IF (l_debug <> -1) THEN
405 rlm_core_sv.dpush(C_SDEBUG,'PurgeArchive');
406 END IF;
407 --fetch header_id from the select statement
408 RLM_CORE_SV.OpenDynamicCursor(c_demand, p_statement, g_BindVarTab);
409
410 LOOP
411
412 BEGIN
413
414 FETCH c_demand INTO v_sched_header_id;
415
416 IF (l_debug <> -1) THEN
417 rlm_core_sv.dlog(C_DEBUG,'Schedule Header Id',v_sched_header_id);
418 END IF;
419
420 EXIT WHEN c_demand%NOTFOUND;
421
422 -- get all other header information
423
424 SELECT ECE_TP_TRANSLATOR_CODE, SCHEDULE_REFERENCE_NUM,
425 SCHEDULE_TYPE, SCHED_GENERATION_DATE,'SCHEDULE',
426 PROCESS_STATUS
427 INTO x_purge_rec.ECE_TP_TRANSLATOR_CODE,
428 x_purge_rec.SCHEDULE_REFERENCE_NUM,
429 x_purge_rec.SCHEDULE_TYPE,
430 x_purge_rec.SCHED_GENERATION_DATE,
431 x_purge_rec.ORIGIN_TABLE,/*2261812*/
432 v_process_status
433 FROM rlm_schedule_headers
434 WHERE header_id = v_sched_header_id;
435
436 --check for partially selected schedule
437
438 IF(p_ship_to_address_id_from IS NOT NULL) THEN
439
440 select count(*) into v_line_count from rlm_schedule_lines
441 where header_id = v_sched_header_id
442 AND ship_to_address_id between p_ship_to_address_id_from
443 AND nvl(p_ship_to_address_id_to, p_ship_to_address_id_from);
444
445 IF (l_debug <> -1) THEN
446 rlm_core_sv.dlog(C_DEBUG,'v_line_count1',v_line_count);
447 END IF;
448
449 select count(*) into v_line_count2 from rlm_schedule_lines
450 where header_id = v_sched_header_id;
451
452 IF (l_debug <> -1) THEN
453 rlm_core_sv.dlog(C_DEBUG,'v_line_count2',v_line_count2);
454 END IF;
455
456 v_partial_schedule :=FALSE;
457
458 IF(v_line_count2 > v_line_count) THEN
459
460 --partial selection of a schedule
461
462 IF (l_debug <> -1) THEN
463 rlm_core_sv.dlog(C_DEBUG,'partial ship to location selection for',v_sched_header_id);
464 END IF;
465
466 --insert exception
467
468 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
469 x_MessageName => 'RLM_PARTIAL_SELECTION',
470 x_ErrorText => 'RLM_PARTIAL_SELECTION',
471 x_ScheduleHeaderId => v_sched_header_id,
472 x_conc_req_id => fnd_global.conc_request_id,
473 x_PurgeStatus => 'N',
474 x_PurgeExp_rec=>x_purge_rec);
475
476 v_partial_schedule := TRUE;
477
478 END IF;
479
480 END IF; --end partial
481
482
483
484 --check for open orders
485
486 v_order_exists:=FALSE;
487
488 IF(v_process_status =5 or v_process_status=7) THEN
489
490 v_order_exists:=CheckOpenOrder(v_sched_header_id,x_purge_rec);
491
492 END IF;
493
494 IF (v_order_exists = TRUE OR v_partial_schedule = TRUE) THEN
495
496 IF (l_debug <> -1) THEN
497 rlm_core_sv.dlog(C_DEBUG,'Open Order found or Partial Schedule For Schedule Header Id',
498 v_sched_header_id);
499 END IF;
500
501 null;
502
503 ELSE
504
505 IF(p_execution_mode = 'P') THEN
506
507 IF (l_debug <> -1) THEN
508 rlm_core_sv.dlog(C_DEBUG,'Execution Mode',p_execution_mode);
509 END IF;
510
511 --delete schedules
512
513 select count(*) into v_line_count from rlm_schedule_headers where header_id = v_sched_header_id;
514
515 IF (l_debug <> -1) THEN
516 rlm_core_sv.dlog(C_DEBUG,'v_line_count_process',v_line_count);
517 END IF;
518
519
520 IF(v_line_count > 0) THEN
521
522 --check for delete schedules with authorization
523 IF (l_debug <> -1) THEN
524 rlm_core_sv.dlog(C_DEBUG,'checking for authorization...');
525 END IF;
526
527 IF(p_authorization = 'Y') THEN
528
529 IF (l_debug <> -1) THEN
530 rlm_core_sv.dlog(C_DEBUG,'Deleting...',v_sched_header_id);
531 END IF;
532
533
534 --store exception
535 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
536 x_MessageName => 'SUCCESS',
537 x_ErrorText => '',
538 x_ScheduleHeaderId => v_sched_header_id,
539 x_conc_req_id => fnd_global.conc_request_id,
540 x_PurgeStatus => 'Y',
541 x_PurgeExp_rec=>x_purge_rec );
542
543
544 delete from rlm_schedule_lines where header_id = v_sched_header_id;
545 delete from rlm_schedule_headers where header_id = v_sched_header_id;
546 g_schedule_headers_tab(g_schedule_headers_tab.COUNT+1):= v_sched_header_id;
547
548 ELSE
549
550
551 select count(*) into v_line_count from rlm_schedule_lines where header_id = v_sched_header_id and item_detail_type = '3';
552
553 IF (l_debug <> -1) THEN
554 rlm_core_sv.dlog(C_DEBUG,'v_line_count_detail',v_line_count);
555 END IF;
556
557
558 IF(v_line_count > 0) THEN
559
560
561 IF (l_debug <> -1) THEN
562 rlm_core_sv.dlog(C_DEBUG,'Authorization exists, Retaining....',v_sched_header_id);
563 END IF;
564
565 --store exception retained
566 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
567 x_MessageName => 'RLM_AUTHORIZATION_FOUND',
568 x_ErrorText => 'RLM_AUTHORIZATION_FOUND',
569 x_ScheduleHeaderId => v_sched_header_id,
570 x_conc_req_id => fnd_global.conc_request_id,
571 x_PurgeStatus => 'N',
572 x_PurgeExp_rec=>x_purge_rec );
573
574 null;
575
576 ELSE
577
578 --store exception
579
580 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
581 x_MessageName => 'SUCCESS',
582 x_ErrorText => '',
583 x_ScheduleHeaderId => v_sched_header_id,
584 x_conc_req_id => fnd_global.conc_request_id,
585 x_PurgeStatus => 'Y',
586 x_PurgeExp_rec=>x_purge_rec );
587
588
589
590 IF (l_debug <> -1) THEN
591 rlm_core_sv.dlog(C_DEBUG,'Authorization not found, deleting....',v_sched_header_id);
592 END IF;
593
594 delete from rlm_schedule_lines where header_id = v_sched_header_id;
595 delete from rlm_schedule_headers where header_id = v_sched_header_id;
596
597
598 --delete exceptions associated with the schedule
599
600 g_schedule_headers_tab(g_schedule_headers_tab.COUNT+1):= v_sched_header_id;
601
602 END IF; --check for item_detail = 3
603
604 END IF; --p_authorization
605
606 END IF; --check for process status in purge mode
607
608
609 ELSE
610
611 --view mode
612
613 IF (l_debug <> -1) THEN
614 rlm_core_sv.dlog(C_DEBUG,'Execution Mode',p_execution_mode);
615 END IF;
616
617 --check for process status in view mode
618
619 select count(*) into v_line_count from rlm_schedule_headers where header_id = v_sched_header_id;
620
621 IF (l_debug <> -1) THEN
622 rlm_core_sv.dlog(C_DEBUG,'v_line_count_status',v_line_count);
623 END IF;
624
625 IF(v_line_count > 0) THEN
626
627 IF (l_debug <> -1) THEN
628 rlm_core_sv.dlog(C_DEBUG,'checking for authorization...');
629 END IF;
630
631 IF(p_authorization = 'Y') THEN
632
633 --store exception purgable
634 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
635 x_MessageName => 'PURGABLE',
636 x_ErrorText => '',
637 x_ScheduleHeaderId => v_sched_header_id,
638 x_conc_req_id => fnd_global.conc_request_id,
639 x_PurgeStatus => 'Y',
640 x_PurgeExp_rec=>x_purge_rec );
641
642 IF (l_debug <> -1) THEN
643 rlm_core_sv.dlog(C_DEBUG,'Purgable...(view)',v_sched_header_id);
644 END IF;
645
646 null;
647
648 ELSE
649
650
651 select count(*) into v_line_count from rlm_schedule_lines where header_id = v_sched_header_id and item_detail_type = '3';
652
653 IF (l_debug <> -1) THEN
654 rlm_core_sv.dlog(C_DEBUG,'v_line_count_detail',v_line_count);
655 END IF;
656
657 IF(v_line_count > 0) THEN
658
659 --store exception retained
660 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
661 x_MessageName => 'RLM_AUTHORIZATION_FOUND',
662 x_ErrorText => 'RLM_AUTHORIZATION_FOUND',
663 x_ScheduleHeaderId => v_sched_header_id,
664 x_conc_req_id => fnd_global.conc_request_id,
665 x_PurgeStatus => 'N',
666 x_PurgeExp_rec=>x_purge_rec );
667
668
669 IF (l_debug <> -1) THEN
670 rlm_core_sv.dlog(C_DEBUG,'Authorization found..not purgable..(view)',v_sched_header_id);
671 END IF;
672
673 null;
674
675 ELSE
676
677 null;
678
679 --store exception purgable
680 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
681 x_MessageName => 'PURGABLE',
682 x_ErrorText => '',
683 x_ScheduleHeaderId => v_sched_header_id,
684 x_conc_req_id => fnd_global.conc_request_id,
685 x_PurgeStatus => 'Y',
686 x_PurgeExp_rec=>x_purge_rec );
687
688
689 IF (l_debug <> -1) THEN
690 rlm_core_sv.dlog(C_DEBUG,'Authorization not found.. purgable..(view)',v_sched_header_id);
691 END IF;
692
693
694 END IF; --check for item_detail = 3
695
696 END IF; --p_authorization check
697
698 END IF; --check process status in view mode
699
700 END IF; --check execution_mode view or purge
701
702 END IF; --order exists true/false
703
704 END;
705
706 END LOOP;
707 --
708 commit;
709 --
710 IF (l_debug <> -1) THEN
711 rlm_core_sv.dlog(C_DEBUG,'success');
712 rlm_core_sv.dpop(C_SDEBUG,'PurgeArchive');
713 END IF;
714
715
716 EXCEPTION
717
718 when others then
719
720 rlm_message_sv.sql_error('rlm_ps_sv.PurgeArchive', v_Progress);
721 rlm_message_sv.dump_messages;
722 --
723 IF (l_debug <> -1) THEN
724 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
725 END IF;
726 --
727 raise;
728
729
730 END PurgeArchive;
731
732
733 /* Purge_Interface */
734
735
736 PROCEDURE PurgeInterface(p_execution_mode VARCHAR2,
737 p_authorization VARCHAR2,
738 p_ship_to_address_id_from NUMBER,
739 p_ship_to_address_id_to NUMBER,
740 p_statement VARCHAR2)
741 IS
742
743 v_from_clause VARCHAR2(32000);
744 v_where_clause VARCHAR2(32000);
745 v_order_clause VARCHAR2(32000);
746 v_forupdate_clause VARCHAR2(32000);
747 v_select_clause VARCHAR2(32000);
748
749 v_statement_oe VARCHAR2(32000);
750 v_statement_rlm VARCHAR2(32000);
751 v_Progress VARCHAR2(3) := '010';
752 v_WF_Enabled VARCHAR2(1) := 'N';
753 v_cursor_id NUMBER;
754 v_errbuf VARCHAR2(2000);
755 v_retcode NUMBER;
756 v_process_status NUMBER;
757 v_sched_header_id NUMBER;
758 v_sched_id NUMBER;
759 v_sched_line_id NUMBER;
760 v_interface_header NUMBER;
761 v_interface_line NUMBER;
762 v_order_header NUMBER;
763 v_order_line NUMBER;
764 v_line_count NUMBER;
765 v_line_count2 NUMBER;
766 v_open_flag VARCHAR2(1);
767 v_order_exists BOOLEAN;
768 v_partial_schedule BOOLEAN;
769 x_request_id NUMBER;
770 e_no_data_found EXCEPTION;
771 p_org_id NUMBER := NULL;
772 x_purge_rec rlm_message_sv.t_PurExp_rec;
773 --
774 TYPE ref_demand_cur IS REF CURSOR;
775 c_demand ref_demand_cur;
776 oe_demand ref_demand_cur;
777 --
778 CURSOR c IS
779 select *
780 from oe_order_lines_all
781 where line_id = v_order_line;
782 --
783 v_Sched_rec OE_ORDER_LINES_ALL%ROWTYPE;
784 --
785 BEGIN
786 --
787 IF (l_debug <> -1) THEN
788 rlm_core_sv.dpush(C_SDEBUG,'PurgeInterface');
789 END IF;
790 --
791 --fetch header_id from the select statement
792 --
793 RLM_CORE_SV.OpenDynamicCursor(c_demand, p_statement, g_BindVarTab);
794 --
795 LOOP
796 --{
797 BEGIN
798 --{
799 FETCH c_demand INTO v_sched_header_id;
800 IF (l_debug <> -1) THEN
801 rlm_core_sv.dlog(C_DEBUG,'Schedule Header Id',v_sched_header_id);
802 END IF;
803
804 EXIT WHEN c_demand%NOTFOUND;
805
806 -- get all other header information
807
808 SELECT ECE_TP_TRANSLATOR_CODE, SCHEDULE_REFERENCE_NUM,
809 SCHEDULE_TYPE, SCHED_GENERATION_DATE,'INTERFACE',
810 PROCESS_STATUS
811 INTO x_purge_rec.ECE_TP_TRANSLATOR_CODE,
812 x_purge_rec.SCHEDULE_REFERENCE_NUM,
813 x_purge_rec.SCHEDULE_TYPE,
814 x_purge_rec.SCHED_GENERATION_DATE,
815 x_purge_rec.ORIGIN_TABLE, /*2261812*/
816 v_process_status
817 FROM rlm_interface_headers
818 WHERE header_id = v_sched_header_id;
819
820 --check for partially selected schedule
821
822 IF(p_ship_to_address_id_from IS NOT NULL) THEN
823
824 select count(*) into v_line_count from rlm_interface_lines
825 where header_id = v_sched_header_id
826 AND ship_to_address_id between p_ship_to_address_id_from
827 AND nvl(p_ship_to_address_id_to, p_ship_to_address_id_from);
828
829 IF (l_debug <> -1) THEN
830 rlm_core_sv.dlog(C_DEBUG,'v_line_count1',v_line_count);
831 END IF;
832
833 select count(*) into v_line_count2 from rlm_interface_lines
834 where header_id = v_sched_header_id;
835
836 IF (l_debug <> -1) THEN
837 rlm_core_sv.dlog(C_DEBUG,'v_line_count2',v_line_count2);
838 END IF;
839
840 v_partial_schedule :=FALSE;
841
842 IF(v_line_count2 > v_line_count) THEN
843
844 --partial selection of a schedule
845
846 IF (l_debug <> -1) THEN
847 rlm_core_sv.dlog(C_DEBUG,'partial ship to location selection for',v_sched_header_id);
848 END IF;
849
850 --insert exception
851
852 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
853 x_MessageName => 'RLM_PARTIAL_SELECTION',
854 x_ErrorText => 'RLM_PARTIAL_SELECTION',
855 x_ScheduleHeaderId => v_sched_header_id,
856 x_conc_req_id => fnd_global.conc_request_id,
857 x_PurgeStatus => 'N',
858 x_PurgeExp_rec=>x_purge_rec);
859
860 v_partial_schedule := TRUE;
861
862 END IF;
863
864 END IF; --end partial
865 --
866 v_order_exists := FALSE;
867 --
868 --check for open orders
869 --
870 IF (v_process_status=7) THEN
871 --
872 BEGIN
873 --
874 select header_id
875 into v_sched_id
876 from rlm_schedule_headers
877 where interface_header_id = v_sched_header_id;
878 --
879 v_order_exists := CheckOpenOrder(v_sched_id,x_purge_rec);
880 --
881 EXCEPTION
882 WHEN NO_DATA_FOUND THEN
883 --
884 IF (l_debug <> -1) THEN
885 rlm_core_sv.dlog(C_DEBUG,'No link to any archive schedule for ',v_sched_header_id);
886 END IF;
887 --
888 END;
889 --
890 END IF;
891 --
892 IF (v_order_exists =TRUE or v_partial_schedule = TRUE) THEN
893 IF (l_debug <> -1) THEN
894 rlm_core_sv.dlog(C_DEBUG,'Open Order or Partial Schedule For Interface Header Id',
895 v_sched_header_id);
896 END IF;
897 null;
898 ELSE
899 IF(p_execution_mode = 'P') THEN
900 IF (l_debug <> -1) THEN
901 rlm_core_sv.dlog(C_DEBUG,'Execution Mode',p_execution_mode);
902 END IF;
903 --delete schedules
904 IF(p_authorization = 'Y') THEN
905
906 IF (l_debug <> -1) THEN
907 rlm_core_sv.dlog(C_DEBUG,'Deleting...',v_sched_header_id);
908 END IF;
909
910 --store exception
911 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
912 x_MessageName => 'SUCCESS',
913 x_ErrorText => '',
914 x_ScheduleHeaderId => v_sched_header_id,
915 x_conc_req_id => fnd_global.conc_request_id,
916 x_PurgeStatus => 'Y',
917 x_PurgeExp_rec=>x_purge_rec );
918 --
919 delete from rlm_interface_lines where header_id = v_sched_header_id;
920 delete from rlm_interface_headers where header_id = v_sched_header_id;
921 --
922 --delete from archive as well
923 --
924 delete from rlm_schedule_lines_all
925 where header_id = (select header_id
926 from rlm_schedule_headers
927 where interface_header_id = v_sched_header_id);
928 --
929 delete from rlm_schedule_headers where interface_header_id = v_sched_header_id;
930 --
931 --delete exceptions associated with the schedule
932 --
933 g_interface_headers_tab(g_interface_headers_tab.COUNT+1):= v_sched_header_id;
934 --
935 ELSE
936
937 select count(*) into v_line_count from rlm_interface_lines where header_id = v_sched_header_id and item_detail_type = '3';
938
939 IF (l_debug <> -1) THEN
940 rlm_core_sv.dlog(C_DEBUG,'v_line_count_detail',v_line_count);
941 END IF;
942
943
944 IF(v_line_count > 0) THEN
945
946
947 IF (l_debug <> -1) THEN
948 rlm_core_sv.dlog(C_DEBUG,'Authorization exists, Retaining....',v_sched_header_id);
949 END IF;
950
951 --store exception retained
952 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
953 x_MessageName => 'RLM_AUTHORIZATION_FOUND',
954 x_ErrorText => 'RLM_AUTHORIZATION_FOUND',
955 x_ScheduleHeaderId => v_sched_header_id,
956 x_conc_req_id => fnd_global.conc_request_id,
957 x_PurgeStatus => 'N',
958 x_PurgeExp_rec=>x_purge_rec );
959
960 ELSE
961
962 --store exception
963
964 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
965 x_MessageName => 'SUCCESS',
966 x_ErrorText => '',
967 x_ScheduleHeaderId => v_sched_header_id,
968 x_conc_req_id => fnd_global.conc_request_id,
969 x_PurgeStatus => 'Y',
970 x_PurgeExp_rec=>x_purge_rec );
971
972
973
974 IF (l_debug <> -1) THEN
975 rlm_core_sv.dlog(C_DEBUG,'Authorization not found, deleting....',v_sched_header_id);
976 END IF;
977
978 delete from rlm_interface_lines where header_id = v_sched_header_id;
979 delete from rlm_interface_headers where header_id = v_sched_header_id;
980
981 --delete from archive as well
982
983 delete from rlm_schedule_lines where header_id = (select header_id from rlm_schedule_headers where interface_header_id = v_sched_header_id);
984
985 delete from rlm_schedule_headers where interface_header_id = v_sched_header_id;
986
987 --delete exceptions associated with the schedule
988 g_interface_headers_tab(g_interface_headers_tab.COUNT+1):= v_sched_header_id;
989
990 END IF; --check for item_detail = 3
991
992 END IF; --p_authorization
993
994
995
996
997 ELSE
998
999 --view mode-------------------------------------------------
1000
1001 IF (l_debug <> -1) THEN
1002 rlm_core_sv.dlog(C_DEBUG,'Execution Mode',p_execution_mode);
1003 END IF;
1004
1005 IF(p_authorization = 'Y') THEN
1006
1007 --store exception purgable
1008 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
1009 x_MessageName => 'PURGABLE',
1010 x_ErrorText => '',
1011 x_ScheduleHeaderId => v_sched_header_id,
1012 x_conc_req_id => fnd_global.conc_request_id,
1013 x_PurgeStatus => 'Y',
1014 x_PurgeExp_rec=>x_purge_rec );
1015
1016 IF (l_debug <> -1) THEN
1017 rlm_core_sv.dlog(C_DEBUG,'Purgable...(view)',v_sched_header_id);
1018 END IF;
1019
1020 null;
1021
1022 ELSE
1023
1024 select count(*) into v_line_count from rlm_interface_lines where header_id = v_sched_header_id and item_detail_type = '3';
1025
1026 IF (l_debug <> -1) THEN
1027 rlm_core_sv.dlog(C_DEBUG,'v_line_count_detail',v_line_count);
1028 END IF;
1029
1030 IF(v_line_count > 0) THEN
1031
1032 --store exception retained
1033 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
1034 x_MessageName => 'RLM_AUTHORIZATION_FOUND',
1035 x_ErrorText => 'RLM_AUTHORIZATION_FOUND',
1036 x_ScheduleHeaderId => v_sched_header_id,
1037 x_conc_req_id => fnd_global.conc_request_id,
1038 x_PurgeStatus => 'N',
1039 x_PurgeExp_rec=>x_purge_rec );
1040
1041
1042 IF (l_debug <> -1) THEN
1043 rlm_core_sv.dlog(C_DEBUG,'Authorization found..not purgable..(view)',v_sched_header_id);
1044 END IF;
1045 null;
1046
1047 ELSE
1048
1049 null;
1050
1051 --store exception purgable
1052 rlm_message_sv.insert_purge_row (x_ExceptionLevel => 'X',
1053 x_MessageName => 'PURGABLE',
1054 x_ErrorText => '',
1055 x_ScheduleHeaderId => v_sched_header_id,
1056 x_conc_req_id => fnd_global.conc_request_id,
1057 x_PurgeStatus => 'Y',
1058 x_PurgeExp_rec=>x_purge_rec );
1059
1060
1061 IF (l_debug <> -1) THEN
1062 rlm_core_sv.dlog(C_DEBUG,'Authorization not found.. purgable..(view)',v_sched_header_id);
1063 END IF;
1064
1065
1066 END IF; --check for item_detail = 3
1067
1068 END IF; --p_authorization check
1069
1070 END IF; --check execution_mode view or purge
1071
1072 END IF; --check partial
1073 --
1074 END;
1075 --}
1076 END LOOP;
1077 --}
1078 commit;
1079 --
1080 IF (l_debug <> -1) THEN
1081 rlm_core_sv.dlog(C_DEBUG,'success');
1082 rlm_core_sv.dpop(C_SDEBUG,'PurgeInterface');
1083 END IF;
1084 --
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 --
1088 rlm_message_sv.sql_error('rlm_ps_sv.PurgeInterface', v_Progress);
1089 rlm_message_sv.dump_messages;
1090 --
1091 IF (l_debug <> -1) THEN
1092 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1093 END IF;
1094 --
1095 raise;
1096
1097 END PurgeInterface;
1098
1099 /* Run Exception Report */
1100
1101 PROCEDURE RunReport( p_org_id NUMBER,
1102 p_execution_mode VARCHAR2,
1103 p_translator_code_from VARCHAR2,
1104 p_translator_code_to VARCHAR2,
1105 p_customer VARCHAR2,
1106 p_ship_to_address_id_from NUMBER,
1107 p_ship_to_address_id_to NUMBER,
1108 p_issue_date_from VARCHAR2,
1109 p_issue_date_to VARCHAR2,
1110 p_schedule_type VARCHAR2,
1111 p_schedule_ref_no NUMBER,
1112 p_delete_beyond_days NUMBER,
1113 p_authorization VARCHAR2,
1114 p_status NUMBER)
1115 IS
1116
1117 x_request_id NUMBER;
1118 -- p_org_id NUMBER := NULL;
1119 v_Progress VARCHAR2(3) := '010';
1120
1121 BEGIN
1122 --
1123 IF (l_debug <> -1) THEN
1124 rlm_core_sv.dpush(C_SDEBUG,'RunReport');
1125 rlm_core_sv.dlog(C_DEBUG,'Begin Report');
1126 END IF;
1127 --
1128 --MOAC Changes fnd_profile.get('ORG_ID', p_org_id);
1129 --
1130 IF (l_debug <> -1) THEN
1131 rlm_core_sv.dlog(C_DEBUG,'Operating unit', p_org_id);
1132 END IF;
1133 --
1134 fnd_request.set_org_id(p_org_id);
1135 --
1136 x_request_id := fnd_request.submit_request (application => 'RLM',
1137 program => 'RLMPSRP',
1138 argument1 =>fnd_global.conc_request_id,
1139 argument2 =>p_execution_mode,
1140 argument3 =>p_translator_code_from,
1141 argument4 =>p_translator_code_to,
1142 argument5 =>p_customer,
1143 argument6 =>p_ship_to_address_id_from,
1144 argument7 =>p_ship_to_address_id_to,
1145 argument8 =>p_issue_date_from,
1146 argument9 =>p_issue_date_to,
1147 argument10 =>p_schedule_type,
1148 argument11 =>p_schedule_ref_no,
1149 argument12 =>p_delete_beyond_days,
1150 argument13 =>p_authorization,
1151 argument14 =>p_status,
1152 argument15 =>p_org_id
1153 );
1154 --
1155 IF (l_debug <> -1) THEN
1156 rlm_core_sv.dlog(C_DEBUG,'Report Request ID', x_request_id);
1157 rlm_core_sv.dlog(C_DEBUG,'End Report');
1158 rlm_core_sv.dpop(C_SDEBUG,'RunReport');
1159 END IF;
1160 --
1161 EXCEPTION
1162 --
1163 WHEN OTHERS THEN
1164 --
1165 rlm_message_sv.sql_error('rlm_ps_sv.RunReport', v_Progress);
1166 rlm_message_sv.dump_messages;
1167 --
1168 IF (l_debug <> -1) THEN
1169 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1170 END IF;
1171 --
1172 raise;
1173 --
1174 END RunReport;
1175
1176
1177 /* Build Query */
1178
1179 FUNCTION BuildQuery ( p_execution_mode VARCHAR2,
1180 p_translator_code_from VARCHAR2,
1181 p_translator_code_to VARCHAR2,
1182 p_customer VARCHAR2,
1183 p_ship_to_address_id_from NUMBER,
1184 p_ship_to_address_id_to NUMBER,
1185 p_issue_date_from VARCHAR2,
1186 p_issue_date_to VARCHAR2,
1187 p_schedule_type VARCHAR2,
1188 p_schedule_ref_no NUMBER,
1189 p_delete_beyond_days NUMBER,
1190 p_authorization VARCHAR2,
1191 p_status NUMBER)
1192
1193 RETURN VARCHAR2
1194
1195 IS
1196
1197 v_where_clause VARCHAR2(32000);
1198 e_no_data_found EXCEPTION;
1199 v_Progress VARCHAR2(3) := '010';
1200 temp_cust VARCHAR2(360);/*2261960*/
1201
1202 BEGIN
1203
1204 IF (l_debug <> -1) THEN
1205 rlm_core_sv.dpush(C_SDEBUG,'BuildQuery');
1206 END IF;
1207 --
1208 IF (p_execution_mode IS NOT NULL) THEN
1209 --
1210 IF (l_debug <> -1) THEN
1211 rlm_core_sv.dlog(C_DEBUG,'p_execution_mode',p_execution_mode);
1212 END IF;
1213 --
1214 END IF;
1215 --
1216 v_where_clause := 'WHERE rh.header_id=rl.header_id';
1217 --
1218 -- dynamic sql starts from here
1219 --
1220 IF(p_translator_code_from IS NOT NULL) THEN
1221 --
1222 IF (l_debug <> -1) THEN
1223 rlm_core_sv.dlog(C_DEBUG,'p_translator_code_from',
1224 p_translator_code_from);
1225 rlm_core_sv.dlog(C_DEBUG,'p_translator_code_to',
1226 p_translator_code_to);
1227 END IF;
1228 --
1229 v_where_clause := v_where_clause ||
1230 ' AND rh.ece_tp_translator_code between :p_translator_code_from AND nvl(:p_translator_code_to, :p_translator_code_from)';
1231
1232 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_translator_code_from;
1233 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_translator_code_to;
1234 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_translator_code_from;
1235 --
1236 END IF;
1237 --
1238 IF(p_customer IS NOT NULL) THEN
1239 --
1240 IF (l_debug <> -1) THEN
1241 rlm_core_sv.dlog(C_DEBUG,'p_customer',p_customer);
1242 END IF;
1243 --
1244 -- 2261960
1245 --
1246 -- Following query is changed as per TCA obsolescence project.
1247 select PARTY.PARTY_NAME
1248 into temp_cust
1249 from HZ_PARTIES PARTY,
1250 HZ_CUST_ACCOUNTS CUST_ACCT
1251 where CUST_ACCT.CUST_ACCOUNT_ID = p_customer
1252 and CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
1253 --
1254 v_where_clause := v_where_clause ||
1255 ' AND (rh.customer_id = :p_customer OR rh.cust_name_ext = :temp_cust)';
1256 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_customer;
1257 g_BindVarTab(g_BindVarTab.COUNT+1) :=temp_cust;
1258 --
1259 END IF;
1260 --
1261 IF(p_ship_to_address_id_from IS NOT NULL) THEN
1262 --
1263 IF (l_debug <> -1) THEN
1264 rlm_core_sv.dlog(C_DEBUG,'p_ship_to_address_id_from',
1265 p_ship_to_address_id_from);
1266 rlm_core_sv.dlog(C_DEBUG,'p_ship_to_address_id_to',
1267 p_ship_to_address_id_to);
1268 END IF;
1269 --
1270 v_where_clause := v_where_clause ||' AND rl.ship_to_address_id between :p_ship_to_address_id_from AND nvl(:p_ship_to_address_id_to, :p_ship_to_address_id_from)';
1271
1272 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_ship_to_address_id_from;
1273 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_ship_to_address_id_to;
1274 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_ship_to_address_id_from;
1275 --
1276 END IF;
1277 --
1278 IF (p_issue_date_from IS NOT NULL) THEN
1279 --
1280 IF (l_debug <> -1) THEN
1281 rlm_core_sv.dlog(C_DEBUG,'p_issue_date_from',p_issue_date_from);
1282 rlm_core_sv.dlog(C_DEBUG,'p_issue_date_to',p_issue_date_to);
1283 END IF;
1284 --
1285 v_where_clause := v_where_clause || ' AND rh.sched_generation_date between to_date(:p_issue_date_from,''YYYY/MM/DD HH24:MI:SS'') AND to_date(nvl(:p_issue_date_to,:p_issue_date_from), ''YYYY/MM/DD HH24:MI:SS'')';
1286 --
1287 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_issue_date_from;
1288 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_issue_date_to;
1289 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_issue_date_from;
1290 --
1291 END IF;
1292 --
1293 IF(p_schedule_type IS NOT NULL) THEN
1294 --
1295 IF (l_debug <> -1) THEN
1296 rlm_core_sv.dlog(C_DEBUG,'p_schedule_type',p_schedule_type);
1297 END IF;
1298 --
1299 v_where_clause := v_where_clause || ' AND rh.schedule_type = :p_schedule_type ';
1300 --
1301 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_schedule_type;
1302 --
1303 END IF;
1304 --
1305 IF (p_delete_beyond_days IS NOT NULL) THEN
1306 --
1307 IF (l_debug <> -1) THEN
1308 rlm_core_sv.dlog(C_DEBUG,'p_delete_beyond_days',p_delete_beyond_days);
1309 rlm_core_sv.dlog(C_DEBUG,'prior to',sysdate-p_delete_beyond_days);
1310 END IF;
1311 --
1312 v_where_clause := v_where_clause || ' AND rh.sched_generation_date < sysdate-:p_delete_beyond_days';
1313 --
1314 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_delete_beyond_days;
1315 --
1316 END IF;
1317 --
1318 IF (p_authorization IS NOT NULL) THEN
1319 --
1320 IF (l_debug <> -1) THEN
1321 rlm_core_sv.dlog(C_DEBUG,'p_authorization',p_authorization);
1322 END IF;
1323 --
1324 END IF;
1325 --
1326 -- header_id overrides all other parameters
1327 -- Bug 3777594 : Clean up g_BindVarTab, if header_id is provided
1328 --
1329 IF (p_schedule_ref_no IS NOT NULL) THEN
1330 --
1331 g_BindVarTab.DELETE;
1332 --
1333 IF (l_debug <> -1) THEN
1334 rlm_core_sv.dlog(C_DEBUG,'p_schedule_ref_no',p_schedule_ref_no);
1335 END IF;
1336 --
1337 v_where_clause := ' where rh.header_id = rl.header_id AND rh.header_id =:p_schedule_ref_no';
1338 --
1339 g_BindVarTab(g_BindVarTab.COUNT+1) :=p_schedule_ref_no;
1340 --
1341 END IF;
1342 --
1343 IF(p_status IS NOT NULL) THEN
1344 --{
1345 IF (l_debug <> -1) THEN
1346 rlm_core_sv.dlog(C_DEBUG,'p_status',p_status);
1347 END IF;
1348 --
1349 -- to be processed
1350 --
1351 IF(p_status = 1) THEN
1352 --
1353 v_where_clause := v_where_clause || ' AND rh.process_status =:ps_1';
1354 g_BindVarTab(g_BindVarTab.COUNT+1) :=2;
1355 --
1356 END IF;
1357 --
1358 -- processed with errors
1359 --
1360 IF(p_status = 2) THEN
1361 --
1362 v_where_clause := v_where_clause || ' AND rh.process_status = :ps_2';
1363 g_BindVarTab(g_BindVarTab.COUNT+1) :=4;
1364 --
1365 END IF;
1366 --
1367 -- partially processed
1368 --
1369 IF(p_status = 3) THEN
1370 --
1371 v_where_clause := v_where_clause || ' AND rh.process_status = :ps_3';
1372 g_BindVarTab(g_BindVarTab.COUNT+1) :=7;
1373 --
1374 END IF;
1375 --
1376 -- processed successfully
1377 --
1378 IF(p_status = 4) THEN
1379 --
1380 v_where_clause := v_where_clause || ' AND rh.process_status = :ps_4';
1381 g_BindVarTab(g_BindVarTab.COUNT+1) :=5;
1382 --
1383 END IF;
1384 --
1385 -- All Status
1386 --
1387 IF(p_status = 5) THEN --Added IF Condition as part of Bugfix 8758276
1388 --
1389 v_where_clause := v_where_clause || ' AND 8 = :ps_5';
1390 g_BindVarTab(g_BindVarTab.COUNT+1) := 8;
1391 --
1392 END IF;
1393 --}
1394 END IF;
1395 --
1396 IF (l_debug <> -1) THEN
1397 rlm_core_sv.dlog(C_DEBUG, 'Where Clause', v_where_clause);
1398 rlm_core_sv.dlog(C_DEBUG, '# of bind variables', g_BindVarTab.COUNT);
1399 rlm_core_sv.dpop(C_SDEBUG);
1400 END IF;
1401 --
1402 return v_where_clause;
1403 --
1404 EXCEPTION
1405
1406 When others then
1407 rlm_message_sv.sql_error('rlm_ps_sv.BuildQuery', v_Progress);
1408 rlm_message_sv.dump_messages;
1409 --
1410 IF (l_debug <> -1) THEN
1411 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1412 END IF;
1413 --
1414 return v_where_clause;
1415
1416 END BuildQuery;
1417
1418
1419 /* Checks for Open Order Associated with a Schedule */
1420
1421 FUNCTION CheckOpenOrder (p_schedule_header_id NUMBER,
1422 x_purge_rec rlm_message_sv.t_PurExp_rec)
1423 RETURN BOOLEAN
1424
1425 IS
1426
1427 v_from_clause VARCHAR2(32000);
1428 v_where_clause VARCHAR2(32000);
1429 v_order_clause VARCHAR2(32000);
1430 v_forupdate_clause VARCHAR2(32000);
1431 v_select_clause VARCHAR2(32000);
1432
1433 v_statement_oe VARCHAR2(32000);
1434 v_statement_rlm VARCHAR2(32000);
1435 v_Progress VARCHAR2(3) := '010';
1436 v_WF_Enabled VARCHAR2(1) := 'N';
1437 v_cursor_id NUMBER;
1438 v_errbuf VARCHAR2(2000);
1439 v_retcode NUMBER;
1440 v_process_status NUMBER;
1441 v_sched_header_id NUMBER;
1442 v_sched_line_id NUMBER;
1443 v_interface_header NUMBER;
1444 v_interface_line NUMBER;
1445 v_order_header NUMBER;
1446 v_order_line NUMBER;
1447 v_line_count NUMBER;
1448 v_line_count2 NUMBER;
1449 v_open_flag VARCHAR2(1);
1450 v_order_exists BOOLEAN;
1451 v_line_number NUMBER; --bugfix 6319027
1452 v_partial_schedule BOOLEAN;
1453 x_request_id NUMBER;
1454 e_no_data_found EXCEPTION;
1455 p_org_id NUMBER := NULL;
1456
1457 --
1458 TYPE ref_demand_cur IS REF CURSOR;
1459 c_demand ref_demand_cur;
1460 oe_demand ref_demand_cur;
1461 --
1462
1463 CURSOR c IS
1464 select *
1465 from oe_order_lines_all
1466 where line_id = v_order_line;
1467 --
1468 v_Sched_rec OE_ORDER_LINES_ALL%ROWTYPE;
1469 --
1470
1471
1472 BEGIN
1473
1474 IF (l_debug <> -1) THEN
1475 rlm_core_sv.dpush(C_SDEBUG,'CheckOpenOrder');
1476 END IF;
1477
1478 v_order_exists := FALSE;
1479
1480 --
1481 /** Commented the code as per bugfix 6319027
1482 v_statement_oe :='select oe.open_flag, oe.header_id, oe.line_id ,oe.source_document_line_id
1483 from oe_order_lines_all oe
1484 where (oe.header_id,oe.source_document_id)
1485 IN ( select rlm.order_header_id,rlm.header_id
1486 from rlm_schedule_lines rlm
1487 where rlm.header_id = :p_schedule_header_id)';
1488 **/
1489 --
1490 --Modified the code as per bugfix 6319027
1491 v_statement_oe :='select oe.open_flag, oe.header_id, oe.line_id ,oe.source_document_line_id, scl.line_number
1492 from oe_order_lines_all oe,
1493 rlm_schedule_lines_all scl
1494 where oe.header_id = scl.order_header_id
1495 and oe.source_document_line_id = scl.line_id
1496 and oe.source_document_type_id = 5
1497 and scl.header_id = :p_schedule_header_id' ;
1498 --
1499
1500 OPEN oe_demand for v_statement_oe using p_schedule_header_id;
1501
1502 v_order_exists := FALSE;
1503 v_open_flag := 'N';
1504
1505 LOOP
1506
1507 BEGIN
1508
1509 FETCH oe_demand INTO v_open_flag, v_order_header, v_order_line, v_sched_line_id, v_line_number; --bugfix 6319027
1510
1511 EXIT WHEN oe_demand%NOTFOUND;
1512
1513 IF (l_debug <> -1) THEN
1514 rlm_core_sv.dlog(C_DEBUG,'For Schedule Header Id',p_schedule_header_id);
1515 rlm_core_sv.dlog(C_DEBUG,'v_open_flag',v_open_flag);
1516 rlm_core_sv.dlog(C_DEBUG,'v_order_header',v_order_header);
1517 rlm_core_sv.dlog(C_DEBUG,'v_order_line',v_order_line);
1518 rlm_core_sv.dlog(C_DEBUG,'Schedule Line Id',v_sched_line_id);
1519 rlm_core_sv.dlog(C_DEBUG,'Schedule Line Number',v_line_number); --bugfix 6319027
1520 END IF;
1521
1522 IF (v_open_flag = 'Y') THEN
1523
1524 --exception open Order Line
1525
1526 v_order_exists := TRUE;
1527
1528 IF (l_debug <> -1) THEN
1529 rlm_core_sv.dlog(C_DEBUG,'Order_exists',v_order_exists);
1530 END IF;
1531
1532 --insert exception
1533
1534 rlm_message_sv.app_purge_error (x_ExceptionLevel => 'E',
1535 x_MessageName => 'RLM_OPEN_ORDER',
1536 x_ErrorText => 'RLM_OPEN_ORDER',
1537 x_ScheduleHeaderId => p_schedule_header_id,
1538 x_ScheduleLineId => v_sched_line_id,
1539 x_OrderHeaderId => v_order_header,
1540 x_OrderLineId => v_order_line,
1541 x_ScheduleLineNum => v_line_number, --bugfix 6319027
1542 x_conc_req_id => fnd_global.conc_request_id,
1543 x_PurgeStatus => 'N',
1544 x_PurgeExp_rec=>x_purge_rec );
1545
1546 --EXIT;
1547
1548 END IF;
1549
1550 END;
1551
1552 END LOOP;
1553
1554 CLOSE oe_demand;
1555 --
1556 IF (l_debug <> -1) THEN
1557 rlm_core_sv.dpop(C_SDEBUG,'CheckOpenOrder');
1558 END IF;
1559 --
1560 return v_order_exists;
1561
1562 EXCEPTION
1563
1564 When others then
1565 rlm_message_sv.sql_error('rlm_ps_sv.CheckOpenOrder', v_Progress);
1566 rlm_message_sv.dump_messages;
1567 --
1568 IF (l_debug <> -1) THEN
1569 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1570 END IF;
1571 --
1572 return FALSE;
1573
1574 END CheckOpenOrder;
1575
1576
1577 END RLM_PS_SV;