[Home] [Help]
PACKAGE BODY: APPS.RLM_DP_SV
Source
1 PACKAGE BODY RLM_DP_SV as
2 /*$Header: RLMDPWPB.pls 120.5.12000000.3 2007/10/17 06:22:21 sunilku ship $*/
3 /*========================== rlm_dp_sv =============================*/
4
5 --
6 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
7 --
8 PROCEDURE DemandProcessor(
9 errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY VARCHAR2,
11 p_org_id NUMBER,
12 p_schedule_purpose_code VARCHAR2,
13 p_from_date VARCHAR2,
14 p_to_date VARCHAR2,
15 p_from_customer_ext VARCHAR2,
16 p_to_customer_ext VARCHAR2,
17 p_from_ship_to_ext VARCHAR2,
18 p_to_ship_to_ext VARCHAR2,
19 p_header_id NUMBER,
20 p_dummy VARCHAR2,
21 p_cust_ship_from_ext VARCHAR2,
22 p_warn_replace_schedule VARCHAR2,
23 p_order_by_schedule_type VARCHAR2 DEFAULT 'N',
24 p_child_processes NUMBER DEFAULT 0,
25 p_request_id NUMBER)
26 IS
27 --
28 v_from_clause VARCHAR2(32000);
29 v_where_clause VARCHAR2(32000);
30 v_order_clause VARCHAR2(32000);
31 v_select_clause VARCHAR2(32000);
32 v_statement VARCHAR2(32000);
33 v_Progress VARCHAR2(3) := '010';
34 v_WF_Enabled VARCHAR2(1) := 'N';
35 v_cursor_id NUMBER;
36 v_errbuf VARCHAR2(2000);
37 v_retcode NUMBER;
38 v_header_id NUMBER;
39 v_schedule_header_id NUMBER;
40 v_header_ps NUMBER;
41 v_status NUMBER;
42 v_count NUMBER;
43 v_replace_status BOOLEAN DEFAULT FALSE;
44 --
45 e_VDFailed EXCEPTION;
46 e_MDFailed EXCEPTION;
47 e_FDFailed EXCEPTION;
48 e_RDFailed EXCEPTION;
49 e_linesLocked EXCEPTION;
50 e_ConfirmationSchedule EXCEPTION;
51 e_ReplaceSchedule EXCEPTION;
52 e_testschedule EXCEPTION; /* 2554058 */
53 --
54 TYPE ref_demand_cur IS REF CURSOR;
55 c_demand ref_demand_cur;
56 --
57 v_Sched_rec RLM_INTERFACE_HEADERS%ROWTYPE;
58 v_Group_rec t_Group_rec;
59
60 l_start_time NUMBER;
61 l_end_time NUMBER;
62 l_val_start_time NUMBER;
63 l_val_end_time NUMBER;
64 l_val_total NUMBER := 0;
65 l_comp_start_time NUMBER;
66 l_comp_end_time NUMBER;
67 l_comp_total NUMBER := 0;
68 l_post_start_time NUMBER;
69 l_post_end_time NUMBER;
70 l_post_total NUMBER := 0;
71 v_msg_text VARCHAR2(32000);
72 l_start_child_time NUMBER;
73 l_end_child_time NUMBER;
74 v_num_child NUMBER :=0;
75 v_child_req_id g_request_tbl;
76 ind NUMBER DEFAULT 0;
77 i NUMBER DEFAULT 0;
78 j NUMBER DEFAULT 0;
79 g_BindVarTab RLM_CORE_SV.t_dynamic_tab;
80 --
81 BEGIN
82 --
83 IF (l_debug <> -1) THEN
84 rlm_core_sv.start_debug;
85 rlm_core_sv.dpush(C_SDEBUG,'DemandProcessor');
86 rlm_core_sv.dlog(C_DEBUG,'from date',p_from_date);
87 rlm_core_sv.dlog(C_DEBUG,'to date',p_to_date);
88 rlm_core_sv.dlog(C_DEBUG,'Org ID', p_org_id);
89 END IF;
90 --
91 SELECT hsecs INTO l_start_time from v$timer;
92 --
93 rlm_message_sv.populate_req_id;
94 --
95 MO_GLOBAL.set_policy_context(p_access_mode => 'S',
96 p_org_id => p_org_id);
97 --
98 -- Initialize retCode to success. It will be set to error only
99 -- in case of a fatal error, in WHEN OTHERS exception handler blocks
100 --
101 retcode := 0;
102 --
103 IF p_header_id IS NOT NULL THEN
104 --
105 v_from_clause := '
106 FROM rlm_interface_headers hdr';
107 --
108 -- bug 3756599
109 v_where_clause := '
110 WHERE hdr.process_status IN (:k_PS_AVAILABLE, :k_PS_PARTIAL_PROCESSED, :k_PS_ERROR) AND header_id = :p_header_id ';
111
112 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_AVAILABLE;
113 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_PARTIAL_PROCESSED;
114 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_ERROR;
115 g_BindVarTab(g_BindVarTab.COUNT+1) := p_header_id;
116 --
117 ELSE
118 --
119 v_from_clause := '
120 FROM rlm_interface_headers hdr';
121 v_where_clause := '
122 WHERE hdr.process_status IN (:k_PS_AVAILABLE, :k_PS_PARTIAL_PROCESSED, :k_PS_ERROR) ';
123 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_AVAILABLE;
124 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_PARTIAL_PROCESSED;
125 g_BindVarTab(g_BindVarTab.COUNT+1) := rlm_core_sv.k_PS_ERROR;
126 --
127 -- Addition of customer to the where clause
128 --
129 IF (p_from_customer_ext IS NOT NULL) THEN
130 --
131 v_where_clause := v_where_clause || ' AND nvl(hdr.customer_ext,hdr.ece_tp_translator_code) BETWEEN :p_from_customer_ext and nvl(:p_to_customer_ext,:p_from_customer_ext)';
132 --
133 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_customer_ext;
134 g_BindVarTab(g_BindVarTab.COUNT+1) := p_to_customer_ext;
135 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_customer_ext;
136 --
137 END IF;
138 --
139 -- Add the Ship From External to the where clause ER 3883413
140 --
141 IF p_cust_ship_from_ext is NOT NULL THEN
142 --
143 v_where_clause := v_where_clause || ' AND header_id NOT IN (Select header_id from rlm_schedule_interface_lines_v ril
144 where ((ril.cust_ship_from_org_ext <> :p_cust_ship_from_ext) OR (ril.cust_ship_from_org_ext is NULL)) AND
145 ril.process_status <> 5)' ;
146 --
147 g_BindVarTab(g_BindVarTab.COUNT+1) := p_cust_ship_from_ext;
148 --
149 END IF ;
150 --
151 -- Addition of ship to to the where clause
152 --
153 IF (p_from_ship_to_ext IS NOT NULL) THEN
154 --
155 v_where_clause := v_where_clause || ' AND hdr.ece_tp_location_code_ext BETWEEN :p_from_ship_to_ext AND nvl(:p_to_ship_to_ext,:p_from_ship_to_ext) ';
156 --
157 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_ship_to_ext;
158 g_BindVarTab(g_BindVarTab.COUNT+1) := p_to_ship_to_ext;
159 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_ship_to_ext;
160 --
161 END IF;
162 --
163 -- Addition of date Validation to the where clause
164 --
165 IF (p_from_date IS NOT NULL) THEN
166 --
167 v_where_clause := v_where_clause ||' AND hdr.sched_generation_date BETWEEN to_date(:p_from_date,''YYYY/MM/DD HH24:MI:SS'') AND nvl( to_date(:p_to_date, ''YYYY/MM/DD HH24:MI:SS''), to_date(:p_from_date, ''YYYY/MM/DD HH24:MI:SS'')) ';
168 --
169 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_date;
170 g_BindVarTab(g_BindVarTab.COUNT+1) := p_to_date;
171 g_BindVarTab(g_BindVarTab.COUNT+1) := p_from_date;
172 --
173 END IF;
174 --
175 -- Addition of schedule_purpose_code
176 --
177 IF (p_schedule_purpose_code IS NOT NULL) THEN
178 --
179 v_where_clause := v_where_clause ||'
180 AND hdr.schedule_purpose = :p_schedule_purpose_code ';
181 --
182 g_BindVarTab(g_BindVarTab.COUNT+1) := p_schedule_purpose_code;
183 --
184 END IF;
185 --
186 -- The above query may need modification in order to use some particular
187 -- index for performance purposes
188 END IF;
189 --
190 IF(p_request_id IS NOT NULL) THEN
191 --
192 g_BindVarTab.DELETE;
193 v_where_clause := ' WHERE hdr.request_id = :p_request_id ';
194 g_BindVarTab(g_BindVarTab.COUNT+1) := p_request_id;
195 --
196 END IF;
197 --
198 -- 2554058 : Added hdr.edi_test_indicator in the following select clause
199 --
200 v_select_clause := 'SELECT hdr.header_id, hdr.process_status,
201 hdr.edi_test_indicator ';
202
203 -- stype
204 g_order_by_schedule_type := p_order_by_schedule_type;
205 --
206 IF (l_debug <> -1) THEN
207 rlm_core_sv.dlog(C_DEBUG,'g_order_by_schedule_type',g_order_by_schedule_type);
208 END IF;
209 --
210 IF g_order_by_schedule_type = 'N' THEN
211 --
212 v_order_clause := '
213 ORDER BY hdr.ece_tp_translator_code,
214 hdr.ece_tp_location_code_ext,
215 hdr.sched_generation_date,
216 hdr.edi_control_num_2,
217 hdr.edi_control_num_3,
218 DECODE(hdr.schedule_type, ''PLANNING_RELEASE'', 1,
219 ''SHIPPING'', 2, ''SEQUENCED'', 3) ,
220 hdr.schedule_reference_num ,
221 DECODE(hdr.schedule_purpose, ''ADD'', 1,
222 ''CONFIRMATION'', 2, ''ORIGINAL'', 3,
223 ''REPLACE'', 4, ''REPLACE_ALL'', 5, ''CANCELLATION'', 6,
224 ''CHANGE'', 7, ''DELETE'', 8),
225 hdr.creation_date';
226 --
227 ELSE
228 --
229 v_order_clause := '
230 ORDER BY hdr.ece_tp_translator_code,
231 hdr.ece_tp_location_code_ext,
232 DECODE(hdr.schedule_type, ''PLANNING_RELEASE'', 1,
233 ''SHIPPING'', 2, ''SEQUENCED'', 3) ,
234 hdr.sched_generation_date,
235 hdr.edi_control_num_2,
236 hdr.edi_control_num_3,
237 hdr.schedule_reference_num ,
238 DECODE(hdr.schedule_purpose, ''ADD'', 1,
239 ''CONFIRMATION'', 2, ''ORIGINAL'', 3,
240 ''REPLACE'', 4, ''REPLACE_ALL'', 5,''CANCELLATION'', 6,
241 ''CHANGE'', 7, ''DELETE'', 8),
242 hdr.creation_date';
243 --
244 END IF;
245 --
246 v_Statement := v_select_clause || ' '
247 || v_from_clause || ' '
248 || v_where_clause
249 || v_order_clause;
250 --
251 IF (l_debug <> -1) THEN
252 rlm_core_sv.dlog(C_DEBUG,'The select Statement is:
253 ', v_Statement);
254 END IF;
255 --
256 IF (l_debug <> -1) THEN
257 rlm_core_sv.dlog(C_DEBUG,'g_BindVarTab.COUNT',g_BindVarTab.COUNT );
258 END IF;
259 --
260 RLM_CORE_SV.OpenDynamicCursor(c_demand, v_statement, g_BindVarTab);
261 --
262 LOOP
263 BEGIN
264 --
265 -- 2554058 : Added edi_test_indicator in the following fetch statement
266 --
267 FETCH c_demand INTO v_header_id, v_header_ps, edi_test_indicator;
268 --
269 IF (l_debug <> -1) THEN
270 rlm_core_sv.dlog(C_DEBUG, '==============================');
271 rlm_core_sv.dlog(C_DEBUG,'Header Id:', v_header_id);
272 rlm_core_sv.dlog(C_DEBUG,'v_header_ps:', v_header_ps);
273 END IF;
274 --
275 EXIT WHEN c_demand%NOTFOUND;
276 --
277 rlm_message_sv.initialize_messages;
278 --
279 -- bug 2721219
280 --
281 RLM_FORECAST_SV.k_REPLACE_FLAG := TRUE;
282 RLM_FORECAST_SV.g_designator_tab.delete;
283
284 IF v_header_ps IN (rlm_core_sv.k_PS_ERROR,
285 rlm_core_sv.k_PS_PARTIAL_PROCESSED) THEN
286 --
287 update rlm_interface_headers_all
288 set process_status = rlm_core_sv.k_PS_AVAILABLE
289 where header_id = v_header_id;
290 --
291 IF (l_debug <> -1) THEN
292 rlm_core_sv.dlog(C_DEBUG,' No of headers updated:', SQL%ROWCOUNT);
293 END IF;
294 --
295 update rlm_interface_lines
296 set process_status = rlm_core_sv.k_PS_AVAILABLE,
297 dsp_child_process_index = NULL
298 where header_id = v_header_id
299 and process_status = rlm_core_sv.k_PS_ERROR;
300 --
301 IF (l_debug <> -1) THEN
302 rlm_core_sv.dlog(C_DEBUG,' No of lines updated:', SQL%ROWCOUNT);
303 END IF;
304 --
305 --Bugfix 6453415 Start <<modified the below query>>
306 delete from rlm_demand_exceptions rde
307 where rde.interface_header_id = v_header_id
308 and (rde.interface_line_id in (select ril.line_id
309 from rlm_interface_lines ril
310 where ril.header_id = rde.interface_header_id)
311 or exception_level = 'E');
312 --Bugfix 6453415 End
313 --
314 IF (l_debug <> -1) THEN
315 rlm_core_sv.dlog(C_DEBUG,' No of demand exceptions lines deleted:', SQL%ROWCOUNT);
316 END IF;
317 --
318 --Bugfix 6453415 Start
319 update rlm_demand_exceptions
320 set request_id = rlm_message_sv.g_conc_req_id
321 where interface_header_id = v_header_id;
322 --Bugfix 6453415 End
323 --
324 END IF;
325 --
326 fnd_profile.get('RLM_WF_ENABLED', v_WF_Enabled);
327 --
328 IF (l_debug <> -1) THEN
332 v_num_child := p_child_processes;
329 rlm_core_sv.dlog(C_DEBUG,'Workflow Enabled set to: ', v_WF_Enabled);
330 END IF;
331 --
333 --
334 -- If Profile Workflow Enabled set to No then
335 --
336 IF (nvl(v_WF_Enabled, 'N') = 'N') THEN
337 --{
338 SELECT hsecs INTO l_val_start_time from v$timer;
339 rlm_validatedemand_sv.GroupValidateDemand(v_header_id, v_status);
340 SELECT hsecs INTO l_val_end_time from v$timer;
341 l_val_total:=l_val_total+(l_val_end_time-l_val_start_time)/100;
342 --
343 IF (v_status = rlm_core_sv.k_PROC_ERROR) OR
344 (rlm_validatedemand_sv.g_schedule_PS = rlm_core_sv.k_PS_ERROR)
345 THEN
346 --
347 RAISE e_VDFailed;
348 --
349 ELSIF (rlm_validatedemand_sv.g_schedule_PS <> rlm_core_sv.k_PS_ERROR)
350 THEN
351 --
352 IF (l_debug <> -1) THEN
353 rlm_core_sv.dlog(C_DEBUG,'Before PostValidation');
354 END IF;
355 --
356 SELECT hsecs INTO l_post_start_time from v$timer;
357 RLM_TPA_SV.PostValidation;
358 SELECT hsecs INTO l_post_end_time from v$timer;
359 l_post_total:=l_post_total+(l_post_end_time-l_post_start_time)/100;
360 COMMIT;
361 --
362 END IF;
363 --
364 -- Check for test indicator (Bug 2554058)
365 --
366 IF edi_test_indicator = 'T' then
367 --
368 IF (l_debug <> -1) THEN
369 rlm_core_sv.dlog(C_DEBUG,'Test schedule found');
370 END IF;
371 --
372 raise e_testschedule;
373 --
374 END IF;
375 --
376 -- Lock the headers and Populate v_sched_rec
377 --
378 IF NOT LockHeader(v_Header_Id, v_Sched_rec) THEN
379 --
380 v_progress := '015';
381 --
382 IF (l_debug <> -1) THEN
383 rlm_core_sv.dlog(C_DEBUG,'header not locked');
384 END IF;
385 --
386 raise e_headerLocked;
387 --
388 END IF;
389 --
390 -- Check for confirmation schedule
391 --
392 IF v_Sched_rec.schedule_purpose = k_CONFIRMATION THEN
393 --
394 IF (l_debug <> -1) THEN
395 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
396 END IF;
397 --
398 raise e_ConfirmationSchedule;
399 --
400 END IF;
401 --
402 -- Call Sweeper Program here
403 -- (Enhancement bug# 1062039)
404 --
405 g_warn_replace_schedule := p_warn_replace_schedule;
406
407 SELECT hsecs INTO l_comp_start_time from v$timer;
408 --
409 RLM_REPLACE_SV.CompareReplaceSched(v_Sched_rec,
410 p_warn_replace_schedule,
411 v_replace_status);
412 --
413 SELECT hsecs INTO l_comp_end_time from v$timer;
414 --
415 IF v_replace_status = FALSE THEN
416 --
417 RAISE e_ReplaceSchedule;
418 --
419 END IF;
420 --
421 -- fetch Group in Group Tab
422 --
423 IF v_num_child > 1 THEN /* Parallel DSP */
424 --
425 -- submit concurrent program requests
426 --
427 CreateChildGroups (v_header_id,
428 v_num_child);
429 --
430 IF NOT LockHeader(v_header_id, v_Sched_rec) THEN
431 --
432 IF (l_debug <> -1) THEN
433 rlm_core_sv.dlog(C_DEBUG, 'Header not locked after
434 CreateChildGroups');
435 END IF;
436 --
437 RAISE e_HeaderLocked;
438 --
439 END IF;
440 --
441 -- Parallelize if more than 1 group found
442 --
443 IF (v_num_child > 1) THEN
444 --
445 SELECT hsecs INTO l_start_child_time from v$timer;
446 --
447 SubmitChildRequests(v_header_id,
448 v_num_child,
449 v_child_req_id);
450 --
451 ProcessChildRequests(v_header_id,
452 v_child_req_id);
453
454 SELECT hsecs INTO l_end_child_time from v$timer;
455 v_msg_text:='Total Time spent in DSP Child Requests - '|| (l_end_child_time-l_start_child_time)/100 ;
456 fnd_file.put_line(fnd_file.log, v_msg_text);
457
458 v_child_req_id.delete;
459 --
460 ELSE
461 --
462 ProcessGroups (v_sched_rec,
466 END IF;
463 v_header_id,
464 1, k_SEQ_DSP);
465 --
467 --
468 ELSE /* Sequential Processing */
469 --
470 ProcessGroups (v_sched_rec,
471 v_header_id,
472 NULL,
473 k_SEQ_DSP);
474
475 --
476 END IF; /*check for parallelization*/
477 --
478 UpdateHeaderPS(v_Sched_rec.header_id,
479 v_Sched_rec.schedule_header_id);
480 --
481 rlm_message_sv.dump_messages(v_header_id);
482 --
483 PurgeInterfaceLines(v_header_id);
484 --
485 COMMIT;
486 --}
487 ELSE
488 -- If Profile Workflow Enabled set to Yes then
489 -- -- Call Workflow version of DSP
490 --{
491 g_warn_replace_schedule := p_warn_replace_schedule;
492 --
493 IF NOT LockHeader(v_header_id, v_Sched_rec) THEN
494 --
495 IF (l_debug <> -1) THEN
496 rlm_core_sv.dlog(C_DEBUG, 'Header not locked');
497 END IF;
498 --
499 RAISE e_HeaderLocked;
500 --
501 END IF;
502 --
503 rlm_wf_sv.StartDSPProcess(v_errbuf, v_retcode, v_header_id,
504 v_Sched_rec,v_num_child);
505 --
506 retcode := v_retcode;
507 --
508 COMMIT;
509 --}
510 END IF;
511 --
512 EXCEPTION
513 --
514 WHEN e_ConfirmationSchedule THEN
515 --
516 IF (l_debug <> -1) THEN
517 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
518 END IF;
519 --
520 rlm_message_sv.app_error(
521 x_ExceptionLevel => rlm_message_sv.k_warn_level,
522 x_MessageName => 'RLM_CONF_SCH_RCD',
523 x_InterfaceHeaderId => v_sched_rec.header_id,
524 x_InterfaceLineId => null,
525 x_ScheduleHeaderId => v_sched_rec.schedule_header_id,
526 x_ScheduleLineId => NULL,
527 x_OrderHeaderId => v_group_rec.setup_terms_rec.header_id,
528 x_OrderLineId => NULL,
529 x_Token1 => 'SCHED_REF',
530 x_Value1 => v_sched_rec.schedule_reference_num);
531 --
532 UpdateGroupPS(v_Sched_rec.header_id,
533 v_Sched_rec.Schedule_header_id,
534 v_Group_rec,
535 rlm_core_sv.K_PS_PROCESSED,
536 'ALL');
537 --
538 UpdateHeaderPS(v_Sched_rec.header_id,
539 v_Sched_rec.Schedule_header_id);
540 --
541 rlm_message_sv.dump_messages(v_header_id);
542 PurgeInterfaceLines(v_header_id); /*2699981*/
543 --
544 COMMIT;
545 --
546 WHEN NO_DATA_FOUND THEN
547 --
548 IF (l_debug <> -1) THEN
549 rlm_core_sv.dlog(C_DEBUG,'No data found ');
550 END IF;
551 --
552 rlm_message_sv.dump_messages(v_header_id);
553 --
554 WHEN e_VDFailed THEN
555 --
556 IF (l_debug <> -1) THEN
557 rlm_core_sv.dlog(C_DEBUG,'Validate Demand Failed');
558 END IF;
559 --
560 ROLLBACK;
561 --
562 UpdateGroupPS(v_header_id,
563 v_schedule_header_id,
564 v_Group_rec,
565 rlm_core_sv.k_PS_ERROR,
566 'ALL');
567 --
568 UpdateHeaderPS(v_header_id,
569 v_schedule_header_id);
570 --
571 rlm_message_sv.dump_messages(v_header_id);
572 --
573 COMMIT;
574 --
575 WHEN e_ReplaceSchedule THEN
576 --
577 ROLLBACK;
578 --
579 UpdateGroupPS(v_Sched_rec.header_id,
580 v_Sched_rec.schedule_header_id,
581 v_Group_rec,
582 rlm_core_sv.k_PS_ERROR);
583
584 --
585 UpdateHeaderPS(v_header_id, v_schedule_header_id);
586 rlm_message_sv.dump_messages(v_header_id);
587 --
588 COMMIT;
589 --
590 /* Bug 2554058 */
591 --
592 WHEN e_testschedule THEN
593 --
594 IF (l_debug <> -1) THEN
595 rlm_core_sv.dlog(C_DEBUG, 'request ID', RLM_MESSAGE_SV.g_conc_req_id);
596 END IF;
597 --
598 rlm_message_sv.app_error(
599 x_ExceptionLevel => rlm_message_sv.k_warn_level,
600 x_MessageName => 'RLM_TEST_SCHEDULE_DETECTED',
601 x_InterfaceHeaderId => v_header_id,
602 x_InterfaceLineId => NULL,
606 --
603 x_OrderLineId => NULL,
604 x_Token1 => 'SCHED_REF',
605 x_Value1 =>rlm_core_sv.get_schedule_reference_num(v_header_id));
607 rlm_message_sv.dump_messages(v_header_id);
608 PurgeInterfaceLines(v_header_id); /*2699981*/
609 --
610 COMMIT;
611 --
612 WHEN e_headerLocked THEN
613 --
614 ROLLBACK;
615 --
616 UpdateGroupPS(v_header_id,
617 v_schedule_header_id,
618 v_Group_rec,
619 rlm_core_sv.k_PS_ERROR,
620 'ALL');
621 --
622 /* UpdateHeaderPS(v_header_id,
623 v_schedule_header_id); */
624 --
625 rlm_message_sv.app_error(
626 x_ExceptionLevel => rlm_message_sv.k_error_level,
627 x_MessageName => 'RLM_HEADER_LOCK_NOT_OBTAINED',
628 x_InterfaceHeaderId => v_header_id,
629 x_InterfaceLineId => NULL,
630 x_OrderLineId => NULL,
631 x_Token1 => 'SCHED_REF',
632 x_Value1 => rlm_core_sv.get_schedule_reference_num(v_header_id));
633 --
634 IF (l_debug <> -1) THEN
635 rlm_core_sv.dlog(C_DEBUG,'Header could not be locked');
636 rlm_core_sv.dpop(C_SDEBUG, 'RLM_LOCK_NOT_OBTAINED');
637 END IF;
638 --
639 rlm_message_sv.dump_messages(v_header_id);
640 --
641 COMMIT;
642 --
643 WHEN OTHERS THEN
644 --
645 ROLLBACK;
646 --
647 retcode := 2;
648 --
649 IF (l_debug <> -1) THEN
650 rlm_core_sv.dlog(C_DEBUG,'when others');
651 rlm_core_sv.dlog(C_DEBUG, 'Return Code', retcode);
652 rlm_core_sv.dlog(C_DEBUG, 'ERROR:', SUBSTR(SQLERRM,1,200));
653 rlm_core_sv.dlog(C_DEBUG, 'request ID', RLM_MESSAGE_SV.g_conc_req_id);
654 END IF;
655 --
656 UpdateGroupPS(v_header_id,
657 v_schedule_header_id,
658 v_Group_rec,
659 rlm_core_sv.k_PS_ERROR,
660 'ALL');
661 --
662 UpdateHeaderPS(v_header_id,
663 v_schedule_header_id);
664 --
665 rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
666 --
667 rlm_message_sv.dump_messages(v_header_id);
668 --
669 COMMIT;
670 --
671 END;
672 --
673 END LOOP;
674 --
675 IF (l_debug <> -1) THEN
676 rlm_core_sv.dlog(C_DEBUG,'rowcount' , c_demand%ROWCOUNT);
677 END IF;
678 --
679 IF c_demand%ROWCOUNT = 0 THEN
680 --
681 rlm_message_sv.initialize_messages;
682 --
683 rlm_message_sv.app_error(
684 x_ExceptionLevel => rlm_message_sv.k_error_level,
685 x_MessageName => 'RLM_NO_DATA_FOR_CRITERIA',
686 x_InterfaceHeaderId => null,
687 x_InterfaceLineId => NULL,
688 x_ScheduleHeaderId => null,
689 x_ScheduleLineId => NULL,
690 x_OrderHeaderId => null,
691 x_OrderLineId => NULL,
692 x_ErrorText => 'No data found for ',
693 x_Token1 => 'SCHEDULE_PURPOSE',
694 x_value1 => p_schedule_purpose_code,
695 x_Token2 => 'FROM_CUSTOMER_EXT',
696 x_value2 => p_from_customer_ext,
697 x_Token3 => 'TO_CUSTOMER_EXT',
698 x_value3 => p_to_customer_ext);
699 --
700 IF (l_debug <> -1) THEN
701 rlm_core_sv.dlog(C_DEBUG,'RLM_NO_DATA_FOR_CRITERIA' );
702 END IF;
703 --
704 rlm_message_sv.dump_messages;
705 --
706 END IF;
707 --
708 CLOSE c_demand;
709 --
710 RunExceptionReport(x_requestId => rlm_message_sv.g_conc_req_id,
711 x_OrgId => p_org_id);
712
713 SELECT hsecs INTO l_end_time from v$timer;
714
715 IF (l_debug <> -1) THEN
716 rlm_core_sv.dlog(C_DEBUG,'ValidateDemandTime', l_val_total);
717 rlm_core_sv.dlog(C_DEBUG,'PostValidateTime', l_post_total);
718 rlm_core_sv.dlog(C_DEBUG,'CompScheduleTime', l_comp_total);
719 rlm_core_sv.dlog(C_DEBUG,'ManageDemandTime', g_md_total);
720 rlm_core_sv.dlog(C_DEBUG,'ManageForecastTime', g_mf_total);
721 rlm_core_sv.dlog(C_DEBUG,'RecDemandTime', g_rd_total);
722 rlm_core_sv.dlog(C_DEBUG,'DSPTime', (l_end_time-l_start_time)/100);
723 END IF;
724
725 v_msg_text:='Total Time spent in Validatedemand call - '||l_val_total;
726 fnd_file.put_line(fnd_file.log, v_msg_text);
727
728 v_msg_text:='Total Time spent in Postvalidation call - '||l_post_total ;
729 fnd_file.put_line(fnd_file.log, v_msg_text);
730
731 v_msg_text:='Total Time spent in CompareSched call - '||l_comp_total ;
735 fnd_file.put_line(fnd_file.log, v_msg_text);
732 fnd_file.put_line(fnd_file.log, v_msg_text);
733
734 v_msg_text:='Total Time spent in Managedemand call - '|| g_md_total;
736
737 v_msg_text:='Total Time spent in Manageforecast call - '|| g_mf_total ;
738 fnd_file.put_line(fnd_file.log, v_msg_text);
739
740 v_msg_text:='Total Time spent in RecDemand call - '|| g_rd_total ;
741 fnd_file.put_line(fnd_file.log, v_msg_text);
742
743 v_msg_text:='Total Time spent in DSP call - '||
744 (l_end_time-l_start_time)/100 ;
745 fnd_file.put_line(fnd_file.log, v_msg_text);
746 --
747 v_msg_text := 'Return Code from DSP concurrent program - ' || retcode;
748 fnd_file.put_line(fnd_file.log, v_msg_text);
749 --
750 IF (l_debug <> -1) THEN
751 rlm_core_sv.dlog(C_DEBUG, 'Return Code', retcode);
752 rlm_core_sv.dpop(C_SDEBUG);
753 rlm_core_sv.stop_debug;
754 END IF;
755 --
756 EXCEPTION
757 --
758 WHEN OTHERS THEN
759 --
760 retcode := 2;
761 rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
762 rlm_message_sv.dump_messages(v_header_id);
763 --
764 IF (l_debug <> -1) THEN
765 rlm_core_sv.dlog(C_DEBUG, 'Return Code', retcode);
766 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
767 rlm_core_sv.stop_debug;
768 END IF;
769 raise;
770 --
771 END DemandProcessor;
772
773 /*===========================================================================
774
775 PROCEDURE NAME: PurgeInterfaceLines
776
777 ===========================================================================*/
778
779 PROCEDURE PurgeInterfaceLines(x_header_id IN NUMBER)
780 IS
781 --
782 v_Progress VARCHAR2(3) := '010';
783 v_process_status NUMBER;
784 --
785 BEGIN
786 --
787 IF (l_debug <> -1) THEN
788 rlm_core_sv.dpush(C_SDEBUG, 'PurgeInterfaceLines');
789 rlm_core_sv.dlog(C_DEBUG, 'x_header_id',x_header_id);
790 END IF;
791 --
792 select process_status into v_process_status
793 from rlm_interface_headers
794 where header_id = x_header_id;
795 --
796 DELETE FROM RLM_INTERFACE_HEADERS
797 WHERE header_id = x_header_id
798 and process_Status = rlm_core_sv.k_PS_PROCESSED;
799 --
800 IF(v_process_status <> rlm_core_sv.k_PS_PARTIAL_PROCESSED) THEN
801 --
802 DELETE FROM RLM_INTERFACE_LINES
803 WHERE header_id = x_header_id
804 and process_Status = rlm_core_sv.k_PS_PROCESSED;
805 --
806 ELSE
807 --
808 DELETE FROM RLM_INTERFACE_LINES
809 WHERE header_id = x_header_id
810 and process_Status = rlm_core_sv.k_PS_PROCESSED
811 and item_detail_type <> rlm_rd_sv.k_MRP_FORECAST;
812 --
813 END IF;
814 --
815 IF (l_debug <> -1) THEN
816 rlm_core_sv.dlog(C_DEBUG, 'Lines deleted ', SQL%ROWCOUNT);
817 rlm_core_sv.dpop(C_SDEBUG);
818 END IF;
819 --
820 EXCEPTION
821 --
822 WHEN NO_DATA_FOUND THEN
823 --
824 rlm_message_sv.sql_error('rlm_dp_sv.PurgeInterfaceLines', v_Progress);
825 --
826 IF (l_debug <> -1) THEN
827 rlm_core_sv.dlog(C_DEBUG, 'No records to delete' );
828 rlm_core_sv.dpop(C_SDEBUG);
829 END IF;
830 --
831 WHEN OTHERS THEN
832 --
833 rlm_message_sv.sql_error('rlm_dp_sv.PurgeInterfaceLines', v_Progress);
834 --
835 IF (l_debug <> -1) THEN
836 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
837 END IF;
838 --
839 raise;
840 --
841 END PurgeInterfaceLines;
842
843 /*=========================================================================
844
845 PROCEDURE NAME: LockHeader
846
847 ===========================================================================*/
848
849 FUNCTION LockHeader (x_HeaderId IN NUMBER, v_Sched_rec OUT NOCOPY RLM_INTERFACE_HEADERS%ROWTYPE)
850 RETURN BOOLEAN
851 IS
852 x_progress VARCHAR2(3) := '010';
853
854 CURSOR c IS
855 SELECT *
856 FROM rlm_interface_headers
857 WHERE header_id = x_HeaderId
858 and process_status IN (rlm_core_sv.k_PS_AVAILABLE,
859 rlm_core_sv.k_PS_PARTIAL_PROCESSED)
860 FOR UPDATE NOWAIT;
861
862 BEGIN
863 --
864 IF (l_debug <> -1) THEN
865 rlm_core_sv.dpush(C_SDEBUG,'LockHeader');
866 rlm_core_sv.dlog(C_DEBUG,'Locking RLM_INTERFACE_HEADERS');
867 END IF;
868 --
869 OPEN c;
870 FETCH c INTO v_Sched_rec;
871 --
872 IF c%NOTFOUND THEN
873 raise NO_DATA_FOUND;
874 END IF;
875 --
876 CLOSE c;
877 --
878 IF (l_debug <> -1) THEN
879 rlm_core_sv.dpop(C_SDEBUG);
880 END IF;
881 --
882 RETURN(TRUE);
883 --
884 EXCEPTION
885 --
886 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
887 --
888 IF (l_debug <> -1) THEN
892 --
889 rlm_core_sv.dlog(C_DEBUG,'APP_EXCEPTION.RECORD_LOCK_EXCEPTION error');
890 rlm_core_sv.dpop(C_SDEBUG);
891 END IF;
893 RETURN(FALSE);
894 --
895 WHEN NO_DATA_FOUND THEN
896 --
897 IF (l_debug <> -1) THEN
898 rlm_core_sv.dlog(C_DEBUG,'No header found with the headerID',
899 x_HeaderId);
900 rlm_core_sv.dpop(C_SDEBUG, 'NO_DATA_FOUND');
901 END IF;
902 --
903 RETURN(FALSE);
904 --
905 WHEN OTHERS THEN
906 rlm_message_sv.sql_error('rlm_managedemand_sv.lockHeader', x_progress);
907 --
908 IF (l_debug <> -1) THEN
909 rlm_core_sv.dlog(C_DEBUG,'progress',x_Progress);
910 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
911 END IF;
912 --
913 RAISE;
914
915 END LockHeader;
916
917 /*===========================================================================
918
919 PROCEDURE NAME: UpdateGroupPS
920
921 ===========================================================================*/
922
923 PROCEDURE UpdateGroupPS(x_header_id IN NUMBER,
924 x_ScheduleHeaderId IN NUMBER,
925 x_Group_rec IN rlm_dp_sv.t_Group_rec,
926 x_status IN NUMBER,
927 x_UpdateLevel IN VARCHAR2)
928 IS
929 --
930 v_Progress VARCHAR2(3) := '010';
931 v_SchedHeaderId NUMBER;
932 v_login_id NUMBER;
933 v_request_id NUMBER;
934 v_program_app_id NUMBER;
935 v_program_id NUMBER;
936 v_program_update_date DATE:= sysdate;
937 --
938 BEGIN
939 --
940 IF (l_debug <> -1) THEN
941 rlm_core_sv.dpush(C_SDEBUG, 'UpdateGroupPS');
942 rlm_core_sv.dlog(C_DEBUG,'UpdateGroupStatus to ', x_status);
943 rlm_core_sv.dlog(C_DEBUG,'x_header_id ', x_header_id);
944 rlm_core_sv.dlog(C_DEBUG,'x_Group_rec.ship_from_org_id ',
945 x_Group_rec.ship_from_org_id);
946 rlm_core_sv.dlog(C_DEBUG,'x_Group_rec.order_header_id ',
947 x_Group_rec.order_header_id);
948 rlm_core_sv.dlog(C_DEBUG,'x_Group_rec.ship_to_org_id ',
949 x_Group_rec.ship_to_org_id);
950 rlm_core_sv.dlog(C_DEBUG,'x_Group_rec.customer_item_id ',
951 x_Group_rec.customer_item_id);
952 rlm_core_sv.dlog(C_DEBUG,'x_UpdateLevel to ', x_UpdateLevel);
953 rlm_core_sv.dlog(C_DEBUG, 'request ID', RLM_MESSAGE_SV.g_conc_req_id);
954 END IF;
955 --
956 v_login_id := fnd_global.login_id ;
957 v_request_id := RLM_MESSAGE_SV.g_conc_req_id ;
958 v_program_app_id := fnd_global.PROG_APPL_ID ;
959 v_program_id := fnd_global.conc_program_id;
960 --
961 SELECT schedule_header_id
962 INTO v_SchedHeaderId
963 FROM rlm_interface_headers
964 WHERE header_id = x_header_id;
965 --
966 IF (l_debug <> -1) THEN
967 rlm_core_sv.dlog(C_DEBUG, 'v_SchedHeaderId', v_SchedHeaderId);
968 END IF;
969 --
970 IF x_UpdateLevel <> 'GROUP' THEN
971 --
972 UPDATE rlm_interface_lines
973 SET process_status = x_Status,
974 LAST_UPDATE_LOGIN = v_login_id ,
975 REQUEST_ID = v_request_id,
976 PROGRAM_APPLICATION_ID = v_program_app_id,
977 PROGRAM_ID = v_program_id,
978 PROGRAM_UPDATE_DATE = v_program_update_date
979 WHERE header_id = x_header_id
980 AND process_status <> rlm_core_sv.k_PS_ERROR; -- bug 5134706
981
982 --
983 IF (l_debug <> -1) THEN
984 rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
985 END IF;
986
987 UPDATE rlm_schedule_lines sl
988 SET process_status = x_Status,
989 LAST_UPDATE_LOGIN = v_login_id ,
990 REQUEST_ID = v_request_id,
991 PROGRAM_APPLICATION_ID = v_program_app_id,
992 PROGRAM_ID = v_program_id,
993 PROGRAM_UPDATE_DATE = v_program_update_date
994 WHERE sl.header_id = v_SchedHeaderId
995 AND process_status <> rlm_core_sv.k_PS_ERROR -- bug 5134706
996 AND interface_line_id in
997 (SELECT line_id
998 FROM rlm_interface_lines_all il
999 WHERE il.header_id = x_header_id);
1000 --
1001 IF (l_debug <> -1) THEN
1002 rlm_core_sv.dlog(C_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
1003 END IF;
1004
1005 ELSE
1006 --
1007 IF (l_debug <> -1) THEN
1008 rlm_core_sv.dlog(C_DEBUG,'Update Group');
1009 END IF;
1010 --
1011 -- JAUTOMO: Update rlm_schedule_lines first before rlm_interface_lines
1012 --
1013 UPDATE rlm_schedule_lines sch
1014 SET process_status = x_Status,
1015 LAST_UPDATE_LOGIN = v_login_id ,
1016 REQUEST_ID = v_request_id,
1020 WHERE header_id = v_SchedHeaderId
1017 PROGRAM_APPLICATION_ID = v_program_app_id,
1018 PROGRAM_ID = v_program_id,
1019 PROGRAM_UPDATE_DATE = v_program_update_date
1021 AND interface_line_id in
1022 (SELECT line_id
1023 FROM rlm_interface_lines_all il
1024 WHERE header_id = x_header_id
1025 AND industry_attribute15 = x_Group_rec.industry_attribute15
1026 AND ship_to_org_id = x_Group_rec.ship_to_org_id
1027 AND customer_item_id = x_Group_rec.customer_item_id
1028 AND inventory_item_id = x_Group_rec.inventory_item_id
1029 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
1030 rlm_core_sv.k_PS_PROCESSED,
1031 rlm_core_sv.k_PS_FROZEN_FIRM));
1032 --
1033 IF (l_debug <> -1) THEN
1034 rlm_core_sv.dlog(C_DEBUG,'No of Schedule Lines Updated ', SQL%ROWCOUNT);
1035 END IF;
1036 --
1037 UPDATE rlm_interface_lines
1038 SET process_status = x_Status,
1039 LAST_UPDATE_LOGIN = v_login_id ,
1040 REQUEST_ID = v_request_id,
1041 PROGRAM_APPLICATION_ID = v_program_app_id,
1042 PROGRAM_ID = v_program_id,
1043 PROGRAM_UPDATE_DATE = v_program_update_date
1044 WHERE header_id = x_header_id
1045 AND industry_attribute15 = x_Group_rec.industry_attribute15
1046 AND ship_to_org_id = x_Group_rec.ship_to_org_id
1047 AND customer_item_id = x_Group_rec.customer_item_id
1048 AND inventory_item_id = x_Group_rec.inventory_item_id
1049 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
1050 rlm_core_sv.k_PS_PROCESSED,
1051 rlm_core_sv.k_PS_FROZEN_FIRM);
1052 --
1053 IF (l_debug <> -1) THEN
1054 rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated ', SQL%ROWCOUNT);
1055 END IF;
1056 --
1057 END IF;
1058 --
1059 IF (l_debug <> -1) THEN
1060 rlm_core_sv.dpop(C_SDEBUG);
1061 END IF;
1062 --
1063 EXCEPTION
1064 --
1065 WHEN OTHERS THEN
1066 --
1067 rlm_message_sv.sql_error('rlm_dp_sv.UpdateGroupPS', v_Progress);
1068 --
1069 IF (l_debug <> -1) THEN
1070 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1071 END IF;
1072 --
1073 raise;
1074 --
1075 END UpdateGroupPS;
1076
1077 /*=========================================================================
1078
1079 PROCEDURE NAME: UpdateHeaderPS
1080
1081 ===========================================================================*/
1082
1083 PROCEDURE UpdateHeaderPS (x_HeaderId IN NUMBER,
1084 x_ScheduleHeaderId IN NUMBER)
1085 IS
1086 --
1087 x_progress VARCHAR2(3) := '010';
1088 --
1089 x_HeaderStatus NUMBER;
1090 --
1091 v_tot_recs NUMBER;
1092 v_proc_recs NUMBER;
1093 v_error_recs NUMBER;
1094 v_proc_sch NUMBER := 0;
1095 v_SchedHeaderId NUMBER DEFAULT NULL;
1096 --
1097 BEGIN
1098 --
1099 IF (l_debug <> -1) THEN
1100 rlm_core_sv.dpush(C_SDEBUG,'UpdateHeaderPS');
1101 rlm_core_sv.dlog(C_DEBUG,'x_HeaderId',x_HeaderId);
1102 rlm_core_sv.dlog(C_DEBUG, 'request ID', RLM_MESSAGE_SV.g_conc_req_id);
1103 END IF;
1104 --
1105 SELECT schedule_header_id
1106 INTO v_SchedHeaderId
1107 FROM rlm_interface_headers
1108 WHERE header_id = x_HeaderId;
1109 --
1110 IF (l_debug <> -1) THEN
1111 rlm_core_sv.dlog(C_DEBUG, 'v_SchedHeaderId', v_SchedHeaderId);
1112 END IF;
1113 --
1114 -- Clearup the header status when no data found in the Group ref
1115 -- if the no of error recs = tot recs then all errors
1116 -- if the no of proc recs = tot recs then all processed
1117 -- else partial proc
1118 --
1119 SELECT count(*)
1120 INTO v_tot_recs
1121 FROM rlm_interface_lines
1122 WHERE header_id = x_HeaderId;
1123 --
1124 IF (l_debug <> -1) THEN
1125 rlm_core_sv.dlog(C_DEBUG,'v_tot_recs', v_tot_recs);
1126 END IF;
1127 --
1128 SELECT count(*)
1129 INTO v_error_recs
1130 FROM rlm_interface_lines
1131 WHERE header_id = x_HeaderId
1132 AND process_status = rlm_core_sv.k_PS_ERROR;
1133 --
1134 IF (l_debug <> -1) THEN
1135 rlm_core_sv.dlog(C_DEBUG,'v_error_recs', v_error_recs);
1136 END IF;
1137 --
1138 SELECT count(*)
1139 INTO v_proc_recs
1140 FROM rlm_interface_lines
1141 WHERE header_id = x_HeaderId
1142 AND process_status = rlm_core_sv.k_PS_PROCESSED;
1143 --
1144 IF (l_debug <> -1) THEN
1145 rlm_core_sv.dlog(C_DEBUG,'v_proc_recs', v_proc_recs);
1146 END IF;
1147 --
1148 SELECT COUNT(1)
1149 INTO v_proc_sch
1150 FROM rlm_schedule_lines
1151 WHERE process_status = rlm_core_sv.k_PS_PROCESSED
1152 AND header_id = v_SchedHeaderId;
1153 --
1154 IF (l_debug <> -1) THEN
1155 rlm_core_sv.dlog(C_DEBUG,'v_proc_sch', v_proc_sch);
1156 END IF;
1157 --
1158 IF v_error_recs = v_tot_recs THEN
1159 --
1160 IF v_proc_sch = 0 THEN
1161 --
1165 --
1162 x_HeaderStatus := rlm_core_sv.k_PS_ERROR;
1163 --
1164 ELSE
1166 x_HeaderStatus := rlm_core_sv.k_PS_PARTIAL_PROCESSED;
1167 --
1168 END IF;
1169 --
1170 ELSIF v_proc_recs = v_tot_recs THEN
1171 --
1172 x_HeaderStatus := rlm_core_sv.k_PS_PROCESSED;
1173 --
1174 ELSE
1175 --
1176 x_HeaderStatus := rlm_core_sv.k_PS_PARTIAL_PROCESSED;
1177 --
1178 END IF;
1179 --
1180 IF (l_debug <> -1) THEN
1181 rlm_core_sv.dlog(C_DEBUG,'x_HeaderStatus', x_HeaderStatus);
1182 END IF;
1183 --
1184 UPDATE rlm_interface_headers
1185 SET process_status = x_HeaderStatus,
1186 LAST_UPDATE_LOGIN = fnd_global.login_id ,
1187 REQUEST_ID = RLM_MESSAGE_SV.g_conc_req_id ,
1188 PROGRAM_APPLICATION_ID = fnd_global.PROG_APPL_ID ,
1189 PROGRAM_ID = fnd_global.conc_program_id,
1190 PROGRAM_UPDATE_DATE = sysdate
1191 WHERE header_id = x_HeaderId;
1192 --
1193 --
1194 IF (l_debug <> -1) THEN
1195 rlm_core_sv.dlog(C_DEBUG,'Number of Interface header updated',SQL%ROWCOUNT);
1196 END IF;
1197 --
1198 UPDATE rlm_schedule_headers
1199 SET process_status = x_HeaderStatus,
1200 LAST_UPDATE_LOGIN = fnd_global.login_id ,
1201 REQUEST_ID = RLM_MESSAGE_SV.g_conc_req_id ,
1202 PROGRAM_APPLICATION_ID = fnd_global.PROG_APPL_ID ,
1203 PROGRAM_ID = fnd_global.conc_program_id,
1204 PROGRAM_UPDATE_DATE = sysdate
1205 WHERE header_id = v_SchedHeaderId ;
1206 --
1207 IF (l_debug <> -1) THEN
1208 rlm_core_sv.dlog(C_DEBUG,'Number of schedule header updated',SQL%ROWCOUNT);
1209 rlm_core_sv.dpop(C_SDEBUG);
1210 END IF;
1211 --
1212 EXCEPTION
1213 --
1214 WHEN NO_DATA_FOUND THEN
1215 --
1216 IF (l_debug <> -1) THEN
1217 rlm_core_sv.dlog(C_DEBUG,'NO DATA FOUND ERROR',x_Progress);
1218 rlm_core_sv.dpop(C_SDEBUG);
1219 END IF;
1220 --
1221 WHEN OTHERS THEN
1222 --
1223 rlm_message_sv.sql_error('rlm_dp_sv.UpdateHeaderStatus', x_progress);
1224 --
1225 IF (l_debug <> -1) THEN
1226 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
1227 END IF;
1228 --
1229 END UpdateHeaderPS;
1230
1231
1232 /*=========================================================================
1233
1234 PROCEDURE NAME: RunExceptionReport
1235
1236 ===========================================================================*/
1237
1238 PROCEDURE RunExceptionReport(x_requestId IN NUMBER,
1239 x_OrgId IN NUMBER)
1240 IS
1241 --
1242 x_progress VARCHAR2(3) := '010';
1243 x_errors NUMBER := 0;
1244 x_request_id NUMBER := -1;
1245 v_org_id NUMBER := 0;
1246 x_no_copies NUMBER :=0;
1247 x_print_style VARCHAR2(30);
1248 x_printer VARCHAR2(30);
1249 x_save_output_flag VARCHAR2(1);
1250 x_result BOOLEAN;
1251 --
1252 BEGIN
1253 --
1254 IF (l_debug <> -1) THEN
1255 rlm_core_sv.dpush('RunExceptionReport');
1256 rlm_core_sv.dlog('Request Id', x_requestId);
1257 rlm_core_sv.dlog('Org ID', x_OrgId);
1258 END IF;
1259 --
1260 /** If there are Errors/warnings then only submit Concurrent Request for
1261 Exception Report. ****/
1262 --
1263 Select count(*)
1264 into x_errors
1265 from rlm_demand_exceptions
1266 where request_id = x_requestid
1267 and exception_level in ('E', 'W', 'I');
1268 --
1269 IF (x_errors > 0) then
1270 --
1271 x_result :=fnd_concurrent.get_request_print_options(
1272 fnd_global.conc_request_id,
1273 x_no_copies ,
1274 x_print_style ,
1275 x_printer ,
1276 x_save_output_flag );
1277 --
1278 IF (x_result =TRUE) then
1279 --
1280 x_result :=fnd_request.set_print_options(x_printer,
1281 x_print_style,
1282 x_no_copies,
1283 NULL,
1284 'N');
1285 --
1286 END IF;
1287 --
1288 fnd_request.set_org_id(x_OrgId);
1289 --
1290 x_request_id := fnd_request.submit_request ('RLM',
1291 'RLMDPDER',
1292 NULL,
1293 NULL,
1294 FALSE,
1295 x_OrgId,
1296 x_requestId,
1297 x_requestId,
1298 NULL,
1299 NULL,
1300 NULL,
1301 NULL,
1302 NULL,
1306 NULL,
1303 NULL,
1304 NULL,
1305 NULL,
1307 NULL,
1308 NULL,
1309 NULL,
1310 NULL,
1311 NULL,
1312 NULL, --v_sched_num
1313 NULL, --v_sched_num
1314 NULL,
1315 NULL,
1316 NULL,
1317 NULL,
1318 NULL,
1319 NULL,
1320 NULL);
1321 --
1322 IF (l_debug <> -1) THEN
1323 rlm_core_sv.dlog('Report Request Id ', x_request_id);
1324 END IF;
1325 --
1326 END IF;
1327 --
1328 IF (l_debug <> -1) THEN
1329 rlm_core_sv.dpop(C_SDEBUG);
1330 END IF;
1331 --
1332 EXCEPTION
1333 --
1334 WHEN NO_DATA_FOUND THEN
1335 --
1336 IF (l_debug <> -1) THEN
1337 rlm_core_sv.dlog(C_DEBUG,'NO DATA FOUND ERROR',x_Progress);
1338 rlm_core_sv.dpop(C_SDEBUG);
1339 END IF;
1340 --
1341 WHEN OTHERS THEN
1342 --
1343 rlm_message_sv.sql_error('rlm_dp_sv.RunExceptionReport', x_progress);
1344 --
1345 IF (l_debug <> -1) THEN
1346 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: OTHER - sql error');
1347 END IF;
1348 --
1349 END RunExceptionReport;
1350
1351 /*===========================================================================
1352
1353 FUNCTION NAME: CheckForecast
1354
1355 ===========================================================================*/
1356
1357 FUNCTION CheckForecast(x_header_id IN NUMBER,
1358 x_Group_rec IN rlm_dp_sv.t_Group_rec)
1359 RETURN BOOLEAN
1360
1361 IS
1362 --
1363 v_Progress VARCHAR2(3) := '010';
1364 v_Count NUMBER := 0;
1365 --
1366 BEGIN
1367 --
1368 IF (l_debug <> -1) THEN
1369 rlm_core_sv.dpush(C_SDEBUG, 'CheckForecast');
1370 END IF;
1371 --
1372 --
1373 SELECT count(*) into v_Count
1374 FROM rlm_interface_lines
1375 WHERE header_id = x_header_id
1376 AND industry_attribute15 = x_Group_rec.industry_attribute15
1377 AND ship_to_org_id = x_Group_rec.ship_to_org_id
1378 AND customer_item_id = x_Group_rec.customer_item_id
1379 AND item_detail_type = rlm_rd_sv.k_MRP_FORECAST
1380 AND process_status = rlm_core_sv.k_PS_AVAILABLE;
1381 --
1382 IF (l_debug <> -1) THEN
1383 rlm_core_sv.dlog('No of Forecast Lines for this group', v_Count);
1384 rlm_core_sv.dpop(C_SDEBUG);
1385 END IF;
1386
1387 IF(v_Count>0) THEN
1388 return (TRUE);
1389 ELSE
1390 return (FALSE);
1391 END IF;
1392
1393 --
1394 EXCEPTION
1395 --
1396 WHEN OTHERS THEN
1397 --
1398 rlm_message_sv.sql_error('rlm_dp_sv.CheckForecast', v_Progress);
1399 --
1400 IF (l_debug <> -1) THEN
1401 rlm_core_sv.dpop(C_SDEBUG,'EXCEPTION: '|| SUBSTR(SQLERRM,1,200));
1402 END IF;
1403 --
1404 raise;
1405 --
1406 END CheckForecast;
1407
1408 /*===========================================================================
1409
1410 PROCEDURE NAME: CreateChildGroups
1411
1412 ===========================================================================*/
1413
1414 PROCEDURE CreateChildGroups(x_header_id IN NUMBER,
1415 x_num_child IN OUT NOCOPY NUMBER)
1416
1417 IS
1418 --
1419 v_index NUMBER;
1420 v_group_count NUMBER;
1421 v_Group_rec t_group_rec;
1422 --
1423 CURSOR c_group_cur IS
1424 SELECT ril.order_header_id,
1425 ril.blanket_number
1426 FROM rlm_interface_headers rih,
1427 rlm_interface_lines_all ril
1428 WHERE ril.header_id = x_header_id
1429 AND ril.header_id = rih.header_id
1430 AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
1431 rlm_core_sv.k_PS_PARTIAL_PROCESSED)
1432 AND rih.org_id = ril.org_id
1433 GROUP BY ril.order_header_id,ril.blanket_number ;
1434 --
1435 BEGIN
1436 --
1437 -- Distribute groups among processes by marking lines
1438 -- with a child process index
1439 --
1440 IF (l_debug <> -1) THEN
1441 rlm_core_sv.dpush(C_SDEBUG, 'CreateChildGroups');
1442 rlm_core_sv.dlog(C_DEBUG, 'header Id', x_header_id);
1443 rlm_core_sv.dlog(C_DEBUG, 'Input num of child processes', x_num_child);
1444 END IF;
1445 --
1446 v_index:=1;
1447 v_group_count:=0;
1448 --
1449 OPEN c_group_cur;
1450 --
1451 LOOP
1452 --
1453 BEGIN
1454 --
1455 FETCH c_group_cur INTO
1456 v_Group_rec.order_header_id,
1457 v_Group_rec.blanket_number;
1458 --
1462 --
1459 EXIT WHEN c_group_cur%NOTFOUND;
1460 --
1461 v_group_count:=v_group_count+1;
1463 update rlm_interface_lines
1464 set dsp_child_process_index = v_index
1465 where header_id = x_header_id
1466 and nvl(order_header_id,-99) = nvl(v_Group_rec.order_header_id,-99)
1467 and nvl(blanket_number,-99) = nvl(v_Group_rec.blanket_number,-99);
1468 --
1469 COMMIT;
1470 --
1471 IF (v_index = x_num_child ) THEN
1472 --
1473 v_index:=1;
1474 --
1475 ELSE
1476 --
1477 v_index:= v_index+1;
1478 --
1479 END IF;
1480 --
1481 END;
1482 --
1483 END LOOP;
1484 --
1485 CLOSE c_group_cur;
1486 --
1487 IF(v_group_count < x_num_child) THEN
1488 --
1489 x_num_child := v_group_count;
1490 --
1491 END IF;
1492 --
1493 IF (l_debug <> -1) THEN
1494 rlm_core_sv.dlog(C_DEBUG, 'Actual num of child processes', x_num_child);
1495 rlm_core_sv.dpop(C_SDEBUG);
1496 END IF;
1497 --
1498 -- end of marking lines with child process index
1499 --
1500 EXCEPTION
1501 --
1502 When others then
1503 --
1504 IF (l_debug <> -1) THEN
1505 rlm_core_sv.dpop(C_SDEBUG, SUBSTRB(SQLERRM, 1, 200));
1506 END IF;
1507 --
1508 raise;
1509
1510 END CreateChildGroups;
1511
1512 /*===========================================================================
1513
1514 PROCEDURE NAME: SubmitChildRequests
1515
1516 ===========================================================================*/
1517
1518 PROCEDURE SubmitChildRequests(
1519 x_header_id IN NUMBER,
1520 x_num_child IN NUMBER,
1521 x_child_req_id IN OUT NOCOPY g_request_tbl)
1522 IS
1523 --
1524 pragma AUTONOMOUS_TRANSACTION;
1525 i NUMBER;
1526 v_msg_text VARCHAR2(32000);
1527 v_OrgId NUMBER;
1528 --
1529 BEGIN
1530 --
1531 IF (l_debug <> -1) THEN
1532 rlm_core_sv.dpush(C_SDEBUG, 'SubmitChildRequests');
1533 rlm_core_sv.dlog(C_DEBUG, 'Current Org', MO_GLOBAL.get_current_org_id);
1534 END IF;
1535 --
1536 v_OrgId := MO_GLOBAL.get_current_org_id;
1537 --
1538 FOR i in 1..x_num_child LOOP
1539 --
1540 fnd_request.set_org_id(v_OrgId);
1541 --
1542 x_child_req_id(x_child_req_id.COUNT+1) :=
1543 fnd_request.submit_request('RLM',
1544 'RLMDSPCHILD',
1545 NULL,
1546 NULL,
1547 FALSE,
1548 fnd_global.conc_request_id,
1549 x_header_id,
1550 i,
1551 v_OrgId);
1552 --
1553 v_msg_text:='Submitting DSP Child Request: '||x_child_req_id(i);
1554 fnd_file.put_line(fnd_file.log, v_msg_text);
1555 --
1556 IF (l_debug <> -1) THEN
1557 rlm_core_sv.dlog(C_DEBUG, 'DSP Child request', x_child_req_id(i));
1558 END IF;
1559 --
1560 END LOOP;
1561 --
1562 COMMIT;
1563 --
1564 IF (l_debug <> -1) THEN
1565 rlm_core_sv.dpop(C_SDEBUG);
1566 END IF;
1567 --
1568 EXCEPTION
1569 --
1570 When others then
1571 --
1572 IF (l_debug <> -1) THEN
1573 rlm_core_sv.dpop(C_SDEBUG);
1574 END IF;
1575 --
1576 raise;
1577 --
1578 END SubmitChildRequests;
1579
1580 /*===========================================================================
1581
1582 PROCEDURE NAME: ProcessChildRequests
1583
1584 ===========================================================================*/
1585
1586 PROCEDURE ProcessChildRequests(x_header_id IN NUMBER,
1587 x_child_req_id IN g_request_tbl)
1588 IS
1589 --
1590 i NUMBER;
1591 v_index NUMBER DEFAULT 0;
1592 v_group_count NUMBER DEFAULT 0;
1593 v_phase VARCHAR2(80);
1594 v_reqstatus VARCHAR2(80);
1595 v_devphase VARCHAR2(80);
1596 v_devstatus VARCHAR2(80);
1597 v_reqmessage VARCHAR2(80);
1598 v_wait_status BOOLEAN;
1599 --
1600 BEGIN
1601 --
1602 IF (l_debug <> -1) THEN
1603 rlm_core_sv.dpush(C_SDEBUG, 'ProcessChildRequests');
1604 rlm_core_sv.dlog(C_DEBUG, 'Total number of child requests',
1605 x_child_req_id.COUNT);
1606 END IF;
1607 --
1608 /* parent process has to wait until each child request
1609 is completed before it can update the header.
1610 child request updates the interface lines after
1611 completing manage demand, forecast and rec demand */
1612 --
1613 FOR i IN x_child_req_id.FIRST..x_child_req_id.LAST LOOP
1614 --
1615 v_wait_status := fnd_concurrent.wait_for_request(
1616 x_child_req_id(i),
1617 10, -- check every 10 sec
1618 10000, -- timeout after 10000 sec
1619 v_phase,
1620 v_reqstatus,
1621 v_devphase,
1622 v_devstatus,
1626 set request_id = RLM_MESSAGE_SV.g_conc_req_id
1623 v_reqmessage);
1624 --
1625 update rlm_demand_exceptions
1627 where request_id = x_child_req_id(i);
1628 --
1629 /* update group status for all lines with child req id*/
1630 --
1631 IF (l_debug <> -1) THEN
1632 rlm_core_sv.dlog(C_DEBUG,'child process index ', i);
1633 rlm_core_sv.dlog(C_DEBUG, 'v_phase', v_phase);
1634 rlm_core_sv.dlog(C_DEBUG, 'v_reqstatus', v_reqstatus);
1635 rlm_core_sv.dlog(C_DEBUG, 'v_devphase', v_devphase);
1636 rlm_core_sv.dlog(C_DEBUG, 'v_devstatus', v_devstatus);
1637 END IF;
1638 --
1639 IF(upper(v_reqstatus) <> 'NORMAL') THEN
1640 --
1641 update rlm_interface_lines
1642 set process_status= rlm_core_sv.k_PS_ERROR
1643 where header_id = x_header_id
1644 and dsp_child_process_index = i
1645 and process_status <> rlm_core_sv.k_PS_PROCESSED;
1646 --
1647 IF (l_debug <> -1) THEN
1648 rlm_core_sv.dlog(C_DEBUG,'x_header_id', x_header_id);
1649 rlm_core_sv.dlog(C_DEBUG,'No of interface Lines Updated', SQL%ROWCOUNT);
1650 END IF;
1651 --
1652 update rlm_schedule_lines sch
1653 set process_status = rlm_core_sv.k_PS_ERROR
1654 where interface_line_id in
1655 (select line_id
1656 from rlm_interface_lines_all il
1657 where header_id = x_header_id
1658 and dsp_child_process_index = i
1659 and process_status <> rlm_core_sv.k_PS_PROCESSED);
1660 --
1661 IF (l_debug <> -1) THEN
1662 rlm_core_sv.dlog(C_DEBUG,'No of schedule Lines Updated ', SQL%ROWCOUNT);
1663 END IF;
1664 --
1665 END IF;
1666 --
1667 COMMIT;
1668 --
1669 END LOOP;
1670 --
1671 IF (l_debug <> -1) THEN
1672 rlm_core_sv.dpop(C_SDEBUG);
1673 END IF;
1674 --
1675 EXCEPTION
1676 --
1677 When others then
1678 --
1679 IF (l_debug <> -1) THEN
1680 rlm_core_sv.dpop(C_SDEBUG,'Error: '|| SUBSTR(SQLERRM,1,200));
1681 END IF;
1682 raise;
1683 --
1684 END ProcessChildRequests;
1685
1686
1687 /*===========================================================================
1688
1689 PROCEDURE NAME: ChildProcess
1690
1691 ===========================================================================*/
1692
1693 PROCEDURE ChildProcess(errbuf OUT NOCOPY VARCHAR2,
1694 retcode OUT NOCOPY VARCHAR2,
1695 p_request_id IN NUMBER,
1696 p_header_id IN NUMBER,
1697 p_index IN NUMBER,
1698 p_org_id IN NUMBER)
1699 IS
1700 --
1701 v_group_rec t_group_rec;
1702 v_sched_rec rlm_interface_headers%ROWTYPE;
1703 e_linesLocked EXCEPTION;
1704 e_MDFailed EXCEPTION;
1705 e_FDFailed EXCEPTION;
1706 e_RDFailed EXCEPTION;
1707 v_status NUMBER;
1708 v_temp NUMBER;
1709 --
1710 BEGIN
1711 --
1712 rlm_message_sv.populate_req_id;
1713 --
1714 IF (l_debug <> -1) THEN
1715 rlm_core_sv.start_debug;
1716 rlm_core_sv.dpush(C_SDEBUG, 'ChildProcess');
1717 rlm_core_sv.dlog(C_DEBUG, 'p_request_id', p_request_id);
1718 rlm_core_sv.dlog(C_DEBUG, 'p_header_id', p_header_id);
1719 rlm_core_sv.dlog(C_DEBUG, 'p_index', p_index);
1720 rlm_core_sv.dlog(C_DEBUG, 'p_org_id', p_org_id);
1721 END IF;
1722 --
1723 -- Initialize retcode to 0 and set to 2 only in case of fatal error
1724 --
1725 retcode := 0;
1726 --
1727 IF MO_GLOBAL.get_current_org_id IS NULL THEN
1728 --
1729 MO_GLOBAL.set_policy_context(p_access_mode => 'S',
1730 p_org_id => p_org_id);
1731 --
1732 END IF;
1733 --
1734 BEGIN
1735 --{
1736 -- populate v_sched_rec
1737 --
1738 SELECT *
1739 INTO v_sched_rec
1740 FROM rlm_interface_headers_all
1741 WHERE header_id = p_header_id
1742 AND process_status IN (rlm_core_sv.k_PS_AVAILABLE,
1743 rlm_core_sv.k_PS_PARTIAL_PROCESSED);
1744 --
1745 EXCEPTION
1746 --
1747 WHEN NO_DATA_FOUND THEN
1748 --
1749 UpdateGroupPS(p_header_id,
1750 null,
1751 v_Group_rec,
1752 rlm_core_sv.K_PS_ERROR,
1753 'ALL');
1754 COMMIT;
1755 --
1756 IF (l_debug <> -1) THEN
1757 rlm_core_sv.dpop(C_SDEBUG, 'when others'|| SUBSTR(SQLERRM,1,200));
1758 rlm_core_sv.stop_debug;
1759 END IF;
1760 --
1761 RETURN;
1762 --}
1763 END;
1764 --
1765 ProcessGroups(v_sched_rec,
1766 p_header_id,
1767 p_index, k_PARALLEL_DSP);
1768 --
1769 IF (l_debug <> -1) THEN
1770 rlm_core_sv.dlog(C_DEBUG, 'Return Code', retcode);
1771 rlm_core_sv.dpop(C_SDEBUG);
1772 rlm_core_sv.stop_debug;
1773 END IF;
1774 --
1775 EXCEPTION
1776 --
1777 WHEN OTHERS THEN
1778 --
1779 retcode := 2;
1780 --
1784 and dsp_child_process_index = p_index;
1781 update rlm_interface_lines
1782 set process_status = rlm_core_sv.k_PS_ERROR
1783 where header_id = p_header_id
1785 --
1786 update rlm_schedule_lines sch
1787 set process_status = rlm_core_sv.k_PS_ERROR
1788 where interface_line_id in
1789 (select line_id
1790 from rlm_interface_lines_all il
1791 where header_id = p_header_id
1792 and dsp_child_process_index = p_index);
1793 --
1794 COMMIT;
1795 --
1796 IF (l_debug <> -1) THEN
1797 rlm_core_sv.dlog(C_DEBUG, 'Return Code', retcode);
1798 rlm_core_sv.dlog(C_DEBUG,'When others:'||SUBSTR(SQLERRM,1,200));
1799 rlm_core_sv.dpop(C_SDEBUG);
1800 rlm_core_sv.stop_debug;
1801 END IF;
1802 --
1803 END ChildProcess;
1804
1805 /*===========================================================================
1806
1807 PROCEDURE NAME: ProcessGroups
1808
1809 ===========================================================================*/
1810
1811 PROCEDURE ProcessGroups (p_sched_rec IN RLM_INTERFACE_HEADERS%ROWTYPE,
1812 p_header_id IN NUMBER,
1813 p_index IN NUMBER DEFAULT NULL,
1814 p_dspMode IN VARCHAR2)
1815 IS
1816 --
1817 v_sched_rec RLM_INTERFACE_HEADERS%ROWTYPE;
1818 v_Group_rec t_Group_rec;
1819 e_linesLocked EXCEPTION;
1820 e_MDFailed EXCEPTION;
1821 e_FDFailed EXCEPTION;
1822 e_RDFailed EXCEPTION;
1823 v_status NUMBER;
1824 v_temp NUMBER;
1825 l_md_start_time NUMBER;
1826 l_md_end_time NUMBER;
1827 l_md_total NUMBER :=0;
1828 l_mf_start_time NUMBER;
1829 l_mf_end_time NUMBER;
1830 l_mf_total NUMBER:=0;
1831 l_rd_start_time NUMBER;
1832 l_rd_end_time NUMBER;
1833 l_rd_total NUMBER:=0;
1834 v_msg_text VARCHAR2(32000);
1835
1836 -- ER 4299804
1837 -- Adding ril.start_date_time to the cursor so that the group with the
1838 -- earliest date will be fetched first in to cursor.This is done as a part of
1839 -- Eaton ER-Across Item Behaviour for blankets.
1840 -- CR : added ship_to_customer_id as part of grouping criteria
1841 --
1842 CURSOR c_group_cur IS
1843 SELECT rih.customer_id,
1844 ril.ship_from_org_id,
1845 ril.ship_to_address_id,
1846 ril.ship_to_site_use_id,
1847 ril.ship_to_org_id,
1848 ril.customer_item_id,
1849 ril.inventory_item_id,
1850 ril.industry_attribute15,
1851 ril.intrmd_ship_to_id, --Bugfix 5911991
1852 ril.intmed_ship_to_org_id, --Bugfix 5911991
1853 ril.order_header_id,
1854 ril.blanket_number,
1855 min(ril.start_date_time),
1856 ril.ship_to_customer_id
1857 FROM rlm_interface_headers rih,
1858 rlm_interface_lines_all ril
1859 WHERE ril.header_id = p_header_id
1860 AND ril.header_id = rih.header_id
1861 AND nvl(ril.dsp_child_process_index,-99) =nvl(p_index, -99)
1862 AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
1863 rlm_core_sv.k_PS_PARTIAL_PROCESSED)
1864 AND rih.org_id = ril.org_id
1865 GROUP BY rih.customer_id,
1866 ril.ship_from_org_id,
1867 ril.ship_to_address_id,
1868 ril.ship_to_site_use_id,
1869 ril.ship_to_org_id,
1870 ril.customer_item_id,
1871 ril.inventory_item_id,
1872 ril.industry_attribute15,
1873 ril.intrmd_ship_to_id, --Bugfix 5911991
1874 ril.intmed_ship_to_org_id, --Bugfix 5911991
1875 ril.order_header_id,
1876 ril.blanket_number,
1877 ril.ship_to_customer_id
1878 ORDER BY min(ril.start_date_time),
1879 ril.ship_to_address_id,
1880 ril.customer_item_id;
1881 --
1882 BEGIN
1883 --
1884 IF (l_debug <> -1) THEN
1885 rlm_core_sv.dpush(C_SDEBUG, 'PROCESSGROUPS');
1886 rlm_core_sv.dlog(C_DEBUG, 'DSP Mode', p_dspMode);
1887 END IF;
1888 --
1889 v_sched_rec := p_sched_rec;
1890 --
1891 OPEN c_group_cur;
1892 --
1893 LOOP
1894 --{
1895 BEGIN
1896 --{
1897 -- ER 4299804: Added min_start_date_time to the fetch stmt.
1898 --
1899 FETCH c_group_cur INTO
1900 v_Group_rec.customer_id,
1901 v_Group_rec.ship_from_org_id,
1902 v_Group_rec.ship_to_address_id,
1903 v_Group_rec.ship_to_site_use_id,
1904 v_Group_rec.ship_to_org_id,
1905 v_Group_rec.customer_item_id,
1906 v_Group_rec.inventory_item_id,
1907 v_Group_rec.industry_attribute15,
1908 v_Group_rec.intrmd_ship_to_id, --Bugfix 5911991
1909 v_Group_rec.intmed_ship_to_org_id, --Bugfix 5911991
1910 v_Group_rec.order_header_id,
1911 v_Group_rec.blanket_number,
1912 v_Group_rec.min_start_date_time,
1913 v_Group_rec.ship_to_customer_id;
1914 --
1915 EXIT WHEN c_group_cur%NOTFOUND;
1916 --
1917 SAVEPOINT GroupDemand;
1918 --
1922 --
1919 IF (l_debug <> -1) THEN
1920 rlm_core_sv.dlog(C_DEBUG, '***** Processing new group ****');
1921 END IF;
1923 SELECT hsecs INTO l_md_start_time from v$timer;
1924 --
1925 IF NOT rlm_manage_demand_sv.LockLines(v_sched_rec.header_id,
1926 v_group_rec)
1927 THEN
1928 --
1929 RAISE e_linesLocked;
1930 --
1931 END IF;
1932 --
1933 rlm_manage_demand_sv.ManageDemand(v_sched_rec.header_id,
1934 v_sched_rec,
1935 v_group_rec,
1936 v_status);
1937 --
1938 SELECT hsecs INTO l_md_end_time from v$timer;
1939 l_md_total:=l_md_total+(l_md_end_time-l_md_start_time)/100;
1940 --
1941 IF (l_debug <> -1) THEN
1942 rlm_core_sv.dlog(C_DEBUG,'v_status:',v_status);
1943 END IF;
1944 --
1945 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
1946 --
1947 RAISE e_MDFailed;
1948 --
1949 END IF;
1950 --
1951 SELECT hsecs INTO l_mf_start_time from v$timer;
1952 --
1953 rlm_tpa_sv.ManageForecast(v_sched_rec.header_id,
1954 v_sched_rec,
1955 v_group_rec,
1956 v_status);
1957 --
1958 SELECT hsecs INTO l_mf_end_time from v$timer;
1959 l_mf_total:=l_mf_total+(l_mf_end_time-l_mf_start_time)/100;
1960 --
1961 IF (l_debug <> -1) THEN
1962 rlm_core_sv.dlog(C_DEBUG,'v_status:',v_status);
1963 END IF;
1964 --
1965 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
1966 --
1967 RAISE e_FDFailed;
1968 --
1969 END IF;
1970 --
1971 SELECT hsecs INTO l_rd_start_time from v$timer;
1972 --
1973 rlm_rd_sv.RecDemand(v_sched_rec.header_id,
1974 v_sched_rec,
1975 v_group_rec,
1976 v_status);
1977 --
1978 SELECT hsecs INTO l_rd_end_time from v$timer;
1979 l_rd_total:=l_rd_total+(l_rd_end_time-l_rd_start_time)/100;
1980 --
1981 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
1982 --
1983 RAISE e_RDFailed;
1984 --
1985 END IF;
1986 --
1987 UpdateGroupPS(v_Sched_rec.header_id,
1988 v_Sched_rec.schedule_header_id,
1989 v_Group_rec,
1990 rlm_core_sv.k_PS_PROCESSED);
1991 --
1992 COMMIT;
1993 --
1994 IF (p_dspMode = k_SEQ_DSP) THEN
1995 --
1996 IF NOT LockHeader(p_header_id, v_Sched_rec) THEN
1997 --
1998 IF (l_debug <> -1) THEN
1999 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header after call ProcessGroups');
2000 END IF;
2001 --
2002 RAISE e_headerLocked;
2003 --
2004 END IF;
2005 --
2006 END IF;
2007 --
2008 EXCEPTION
2009 --
2010 WHEN e_MDFailed THEN
2011 --
2012 IF (l_debug <> -1) THEN
2013 rlm_core_sv.dlog(C_DEBUG,'Manage Demand failed group');
2014 END IF;
2015 --
2016 ROLLBACK TO GroupDemand;
2017 --
2018 UpdateGroupPS(v_Sched_rec.header_id,
2019 v_Sched_rec.schedule_header_id,
2020 v_Group_rec,
2021 rlm_core_sv.k_PS_ERROR);
2022 -- Bug#: 2771756 - Start
2023 -- Bug 4198330 added group information
2024 rlm_core_sv.dlog(C_DEBUG,'Manage Demand remove messages');
2025 rlm_message_sv.removeMessages(
2026 p_header_id => p_header_id,
2027 p_message => 'RLM_RSO_CREATION_INFO',
2028 p_message_type => 'I',
2029 p_ship_from_org_id => v_group_rec.ship_from_org_id,
2030 p_ship_to_address_id => v_group_rec.ship_to_address_id,
2031 p_customer_item_id => v_group_rec.customer_item_id,
2032 p_inventory_item_id => v_group_rec.inventory_item_id);
2033 -- Bug#: 2771756 - End
2034 rlm_message_sv.dump_messages(p_header_id);
2035 --
2036 COMMIT;
2037 --
2038 IF (p_dspMode = k_SEQ_DSP) THEN
2039 --
2040 IF NOT LockHeader(p_header_id, v_Sched_rec) THEN
2041 --
2042 IF (l_debug <> -1) THEN
2043 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_MDFailed');
2044 END IF;
2045 --
2046 RAISE e_HeaderLocked;
2047 --
2048 END IF;
2049 --
2050 END IF;
2051 --
2052 WHEN e_FDFailed THEN
2053 --
2054 IF (l_debug <> -1) THEN
2055 rlm_core_sv.dlog(C_DEBUG,'Forecast Processor failed group');
2056 END IF;
2057 --
2058 ROLLBACK TO GroupDemand;
2059 --
2060 UpdateGroupPS(v_Sched_rec.header_id,
2061 v_Sched_rec.schedule_header_id,
2062 v_Group_rec,
2063 rlm_core_sv.k_PS_ERROR);
2064 -- Bug#: 2771756 - Start
2065 -- Start bug 4198330 added grouping information
2069 p_message_type => 'I',
2066 rlm_message_sv.removeMessages(
2067 p_header_id => p_header_id,
2068 p_message => 'RLM_RSO_CREATION_INFO',
2070 p_ship_from_org_id => v_group_rec.ship_from_org_id,
2071 p_ship_to_address_id => v_group_rec.ship_to_address_id,
2072 p_customer_item_id => v_group_rec.customer_item_id,
2073 p_inventory_item_id => v_group_rec.inventory_item_id);
2074 -- Bug#: 2771756 - End
2075 rlm_message_sv.dump_messages(p_header_id);
2076 --
2077 COMMIT;
2078 --
2079 IF (p_dspMode = k_SEQ_DSP) THEN
2080 --
2081 IF NOT LockHeader(p_header_id, v_Sched_rec) THEN
2082 --
2083 IF (l_debug <> -1) THEN
2084 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_FDFailed');
2085 END IF;
2086 --
2087 RAISE e_HeaderLocked;
2088 --
2089 END IF;
2090 --
2091 END IF;
2092 --
2093 WHEN e_RDFailed THEN
2094 --
2095 IF (l_debug <> -1) THEN
2096 rlm_core_sv.dlog(C_DEBUG,'Reconcile Demand failed group');
2097 END IF;
2098 --
2099 ROLLBACK TO GroupDemand;
2100 --
2101 UpdateGroupPS(v_Sched_rec.header_id,
2102 v_Sched_rec.schedule_header_id,
2103 v_Group_rec,
2104 rlm_core_sv.k_PS_ERROR);
2105 -- Bug#: 2771756 - Start
2106 -- Start bug 4198330 added grouping information
2107 rlm_core_sv.dlog(C_DEBUG,' before remove messages');
2108 rlm_message_sv.removeMessages(
2109 p_header_id => p_header_id,
2110 p_message => 'RLM_RSO_CREATION_INFO',
2111 p_message_type => 'I',
2112 p_ship_from_org_id => v_group_rec.ship_from_org_id,
2113 p_ship_to_address_id => v_group_rec.ship_to_address_id,
2114 p_customer_item_id => v_group_rec.customer_item_id,
2115 p_inventory_item_id => v_group_rec.inventory_item_id);
2116 -- Bug#: 2771756 - End
2117 rlm_message_sv.dump_messages(p_header_id);
2118 --
2119 COMMIT;
2120 --
2121 IF (p_dspMode = k_SEQ_DSP) THEN
2122 --
2123 IF NOT LockHeader(p_header_id, v_Sched_rec) THEN
2124 --
2125 IF (l_debug <> -1) THEN
2126 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_RDFailed');
2127 END IF;
2128 --
2129 RAISE e_HeaderLocked;
2130 --
2131 END IF;
2132 --
2133 END IF;
2134 --
2135 WHEN e_HeaderLocked THEN
2136 --
2137 IF (l_debug <> -1) THEN
2138 rlm_core_sv.dlog(C_DEBUG, 'e_HeaderLocked grp. level exception handler');
2139 END IF;
2140 --
2141 RAISE e_HeaderLocked;
2142 --
2143 WHEN OTHERS THEN
2144 --
2145 IF (l_debug <> -1) THEN
2146 rlm_core_sv.dlog(C_DEBUG, 'ERROR::', SUBSTR(SQLERRM,1,200));
2147 END IF;
2148 --
2149 ROLLBACK TO GroupDemand;
2150 --
2151 UpdateGroupPS(v_Sched_rec.header_id,
2152 v_Sched_rec.schedule_header_id,
2153 v_Group_rec,
2154 rlm_core_sv.k_PS_ERROR);
2155 -- Bug#: 2771756 - Start
2156 -- Start bug 4198330 added grouping information
2157 rlm_message_sv.removeMessages(
2158 p_header_id => p_header_id,
2159 p_message => 'RLM_RSO_CREATION_INFO',
2160 p_message_type => 'I',
2161 p_ship_from_org_id => v_group_rec.ship_from_org_id,
2162 p_ship_to_address_id => v_group_rec.ship_to_address_id,
2163 p_customer_item_id => v_group_rec.customer_item_id,
2164 p_inventory_item_id => v_group_rec.inventory_item_id);
2165 -- Bug#: 2771756 - End
2166 rlm_message_sv.dump_messages(p_header_id);
2167 --
2168 COMMIT;
2169 --
2170 IF (p_dspMode = k_SEQ_DSP) THEN
2171 --
2172 IF NOT LockHeader(p_header_id, v_Sched_rec) THEN
2173 --
2174 IF (l_debug <> -1) THEN
2175 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_MDFailed');
2176 END IF;
2177 --
2178 RAISE e_HeaderLocked;
2179 --
2180 END IF;
2181 --
2182 END IF;
2183 --}
2184 END;
2185 --}
2186 END LOOP; /*Loop for fetching groups with same child process index*/
2187 CLOSE c_group_cur;
2188 --
2189 g_md_total := g_md_total + l_md_total;
2190 g_mf_total := g_mf_total + l_mf_total;
2191 g_rd_total := g_rd_total + l_rd_total;
2192 --
2193 IF (p_dspMode <> k_SEQ_DSP) THEN
2194 --
2195 v_msg_text:='Total Time spent in Managedemand call - '|| l_md_total;
2196 fnd_file.put_line(fnd_file.log, v_msg_text);
2197 --
2198 v_msg_text:='Total Time spent in Manageforecast call - '|| l_mf_total ;
2199 fnd_file.put_line(fnd_file.log, v_msg_text);
2200 --
2201 v_msg_text:='Total Time spent in RecDemand call - '|| l_rd_total ;
2202 fnd_file.put_line(fnd_file.log, v_msg_text);
2203 --
2207 rlm_core_sv.dpop(C_SDEBUG);
2204 END IF;
2205 --
2206 IF (l_debug <> -1) THEN
2208 END IF;
2209 --
2210 EXCEPTION
2211 --
2212 WHEN e_HeaderLocked THEN
2213 --
2214 IF c_group_cur%ISOPEN THEN
2215 CLOSE c_group_cur; --bug 4570658
2216 END IF;
2217
2218 IF (l_debug <> -1) THEN
2219 rlm_core_sv.dlog(C_DEBUG, 'e_HeaderLocked Exception in ProcessGroups');
2220 rlm_core_sv.dpop(C_SDEBUG);
2221 END IF;
2222 --
2223 RAISE e_HeaderLocked;
2224 --
2225 WHEN OTHERS THEN
2226 --
2227 IF (l_debug <> -1) THEN
2228 rlm_message_sv.dump_messages(p_header_id);
2229 rlm_core_sv.dpop(C_SDEBUG, 'Error: '||SUBSTR(SQLERRM,1,200));
2230 END IF;
2231 --
2232 raise;
2233 --
2234 END ProcessGroups;
2235
2236 END RLM_DP_SV;