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