[Home] [Help]
PACKAGE BODY: APPS.RLM_WF_SV
Source
1 PACKAGE BODY RLM_WF_SV as
2 /*$Header: RLMDPWFB.pls 120.5.12000000.2 2007/04/09 10:12:14 sunilku ship $*/
3 /*========================== rlm_wf_sv =============================*/
4
5 --
6 l_DEBUG NUMBER := NVL(fnd_profile.value('RLM_DEBUG_MODE'),-1);
7
8 FUNCTION Notification_ToUser(user_id NUMBER) RETURN VARCHAR2;
9 FUNCTION Notification_FromUser(responsibility_id NUMBER,
10 resp_appl_id NUMBER) RETURN VARCHAR2;
11
12 --
13 /*Bug 2581117 */
14 l_comp_start_time NUMBER;
15 l_comp_end_time NUMBER;
16 l_val_start_time NUMBER;
17 l_val_end_time NUMBER;
18 l_msg_text VARCHAR2(32000);
19 --
20 PROCEDURE StartDSPProcess( errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_Header_Id IN NUMBER,
23 v_Sch_rec IN OUT NOCOPY rlm_interface_headers%ROWTYPE,
24 v_num_child IN NUMBER
25 )
26 IS
27 --
28 v_ItemKey VARCHAR2(100) := to_char(p_Header_Id);
29 v_ItemKeyNew VARCHAR2(100) := 'END' || to_char(p_header_id) ;
30 v_ItemType VARCHAR2(30) := g_ItemType;
31 v_ProcessName VARCHAR2(30) := g_ProcessName;
32 v_ScheduleNum VARCHAR2(35) ;
33 v_Customer VARCHAR2(30) ;
34 v_ScheduleGenDate DATE;
35 v_org_id NUMBER;
36 v_retcode NUMBER;
37 v_count NUMBER;
38 /*Bug 2581117 */
39 v_start_time NUMBER;
40 v_end_time NUMBER;
41 v_wf_msg_text VARCHAR2(32000);
42 ---
43 e_DSPFailed EXCEPTION;
44
45 BEGIN
46
47 IF (l_debug <> -1) THEN
48 rlm_core_sv.dpush(C_SDEBUG,'StartDSPProcess');
49 rlm_core_sv.dlog(C_DEBUG,'p_Header_Id ', p_Header_Id);
50 END IF;
51 --
52 -- fnd_profile.get('ORG_ID', v_org_id);
53 --
54 g_num_child := v_num_child;
55 v_org_id := v_Sch_rec.org_id;
56 --
57 IF (l_debug <> -1) THEN
58 rlm_core_sv.dlog(C_DEBUG,'Org Id ', v_Sch_rec.org_id);
59 END IF;
60 --
61 -- Bug#: 3053299 - Added Schedule Generation Date as argument
62 --
63 GetScheduleDetails(p_Header_Id, v_ScheduleNum, v_Customer, v_ScheduleGenDate);
64 --
65 IF (l_debug <> -1) THEN
66 rlm_core_sv.dlog(C_DEBUG,'v_ScheduleNum ', v_ScheduleNum);
67 rlm_core_sv.dlog(C_DEBUG,'v_Customer ', v_Customer);
68 END IF;
69
70 -- Abort the process if it has hung
71 --
72 BEGIN
73 --
74 IF (l_debug <> -1) THEN
75 rlm_core_sv.dlog(C_DEBUG,'Aborting old processes');
76 END IF;
77 --
78 wf_engine.AbortProcess(itemtype => v_ItemType,
79 itemkey => v_ItemKey);
80 --
81 IF (l_debug <> -1) THEN
82 rlm_core_sv.dlog(C_DEBUG,'After abort bad process');
83 END IF;
84 --
85 EXCEPTION
86 WHEN OTHERS THEN
87 --
88 IF (l_debug <> -1) THEN
89 rlm_core_sv.dlog(C_DEBUG,'Did not abort any process');
90 END IF;
91 --
92 END;
93 --
94 -- Check whether Item has already been run in Workflow and purge it
95 -- Bug 2756981: Set force to TRUE to allow child processes to be purged
96 --
97 wf_purge.Items(itemtype => v_ItemType,
98 itemkey => v_ItemKey,
99 enddate => sysdate,
100 docommit => FALSE,
101 force => TRUE);
102 --
103 IF (l_debug <> -1) THEN
104 rlm_core_sv.dlog(C_DEBUG,'After Purge for Item key ',p_Header_Id);
105 END IF;
106 --
107 wf_engine.CreateProcess(v_ItemType, v_ItemKey, v_ProcessName);
108
109 -- Set various Header Attributes
110
111 wf_engine.SetItemUserKey(v_ItemType, v_ItemKey,v_ScheduleNum);
112 --
113 wf_engine.SetItemAttrNumber( v_ItemType,
114 v_ItemKey,
115 'USER_ID',
116 FND_GLOBAL.USER_ID);
117
118 wf_engine.SetItemAttrNumber( v_ItemType,
119 v_ItemKey,
120 'APPLICATION_ID',
121 FND_GLOBAL.RESP_APPL_ID);
122
123 wf_engine.SetItemAttrNumber( v_ItemType,
124 v_ItemKey,
125 'REQUEST_ID',
126 FND_GLOBAL.CONC_REQUEST_ID);
127
128 wf_engine.SetItemAttrNumber( v_ItemType,
129 v_ItemKey,
130 'RESPONSIBILITY_ID',
131 FND_GLOBAL.RESP_ID);
132
133 wf_engine.SetItemAttrNumber( v_ItemType,
134 v_ItemKey,
135 'ORG_ID',
136 v_org_id);
137
138 wf_engine.SetItemAttrNumber( v_ItemType,
139 v_ItemKey,
140 'HEADER_ID',
141 p_Header_Id);
142
143 wf_engine.SetItemAttrText( v_ItemType,
144 v_ItemKey,
145 'SCHEDULE_NUMBER',
146 v_ScheduleNum);
147
148 wf_engine.SetItemAttrText( v_ItemType,
149 v_ItemKey,
150 'CUSTOMER_NAME',
151 v_Customer);
152
153 -- Bug#: 3053299 - Setting the tokens for From User, To User and
154 -- Schedule Generation Date
155 --
156 wf_engine.SetItemAttrText( v_ItemType,
157 v_ItemKey,
158 'FROM_USER',
159 Notification_FromUser(FND_GLOBAL.RESP_ID,
160 FND_GLOBAL.RESP_APPL_ID));
161
162 wf_engine.SetItemAttrText( v_ItemType,
163 v_ItemKey,
164 'TO_USER',
165 Notification_ToUser(FND_GLOBAL.USER_ID));
166
167 wf_engine.SetItemAttrText( v_ItemType,
168 v_ItemKey,
169 'SCHED_GEN_DATE',
170 v_ScheduleGenDate);
171
172 --
173 IF (l_debug <> -1) THEN
174 rlm_core_sv.dlog(C_DEBUG,'Before Start Process ');
175 END IF;
176 --
177 SELECT hsecs INTO v_start_time from v$timer;
178
179 wf_engine.StartProcess(v_ItemType, v_ItemKey);
180 --
181 IF (l_debug <> -1) THEN
182 rlm_core_sv.dlog(C_DEBUG,'After Start Process ');
183 END IF;
184 --
185 v_retcode := wf_engine.GetItemAttrNumber(v_ItemType,v_ItemKey,'ERRORS_EXIST');
186 --
187 IF (l_debug <> -1) THEN
188 rlm_core_sv.dlog(C_DEBUG,' Errors Status: ', v_retcode);
189 END IF;
190 --
191 retcode := g_PROC_SUCCESS;
192 --
193 IF (l_debug <> -1) THEN
194 rlm_core_sv.dlog(C_DEBUG,'Before End Process ');
195 END IF;
196 --
197 BEGIN
198 --
199 IF (l_debug <> -1) THEN
200 rlm_core_sv.dlog(C_DEBUG,'Aborting old processes');
201 END IF;
202 --
203 wf_engine.AbortProcess(itemtype => v_ItemType,
204 itemkey => v_ItemKeyNew);
205 --
206 IF (l_debug <> -1) THEN
207 rlm_core_sv.dlog(C_DEBUG,'after abort bad process');
208 END IF;
209 --
210 EXCEPTION
211 WHEN OTHERS THEN
212 --
213 IF (l_debug <> -1) THEN
214 rlm_core_sv.dlog(C_DEBUG,'Did not abort any process');
215 END IF;
216 --
217 END;
218 --
219 -- Bug 2756981: Set force to TRUE to allow child processes to be purged
220 --
221 wf_purge.Items(itemtype => v_ItemType,
222 itemkey => v_ItemKeyNew,
223 enddate => sysdate,
224 docommit => FALSE,
225 force => TRUE);
226 --
227
228 IF (l_debug <> -1) THEN
229 rlm_core_sv.dlog(C_DEBUG,'Creating End Process ');
230 END IF;
231 --
232 wf_engine.CreateProcess(v_ItemType, v_ItemKeyNew, 'RLMEND');
233 --
234 wf_engine.SetItemParent(v_ItemType,v_ItemKeyNew,v_ItemType,
235 to_char(p_Header_Id), to_char(p_Header_Id));
236 --
237 wf_engine.SetItemUserKey(v_ItemType, v_ItemKeyNew,v_ScheduleNum);
238 --
239 IF (l_debug <> -1) THEN
240 rlm_core_sv.dlog(C_DEBUG,'Setting attributes End Process ');
241 END IF;
242 --
243 wf_engine.SetItemAttrNumber( v_ItemType,
244 v_ItemKeyNew,
245 'USER_ID',
246 FND_GLOBAL.USER_ID);
247
248 wf_engine.SetItemAttrNumber( v_ItemType,
249 v_ItemKeyNew,
250 'APPLICATION_ID',
251 FND_GLOBAL.RESP_APPL_ID);
252
253 wf_engine.SetItemAttrNumber( v_ItemType,
254 v_ItemKeyNew,
255 'REQUEST_ID',
256 FND_GLOBAL.CONC_REQUEST_ID);
257
258 wf_engine.SetItemAttrNumber( v_ItemType,
259 v_ItemKeyNew,
260 'RESPONSIBILITY_ID',
261 FND_GLOBAL.RESP_ID);
262
263 wf_engine.SetItemAttrNumber( v_ItemType,
264 v_ItemKeyNew,
265 'ORG_ID',
266 v_org_id);
267
268 wf_engine.SetItemAttrNumber( v_ItemType,
269 v_ItemKeyNew,
270 'HEADER_ID',
271 p_Header_Id);
272
273 wf_engine.SetItemAttrText( v_ItemType,
274 v_ItemKeyNew,
275 'SCHEDULE_NUMBER',
276 v_ScheduleNum);
277
278 wf_engine.SetItemAttrText( v_ItemType,
279 v_ItemKeyNew,
280 'CUSTOMER_NAME',
281 v_Customer);
282
283 -- Bug#: 3053299 - Setting the tokens for From User, To User
284 -- and Schedule Generation Date
285
286
287 wf_engine.SetItemAttrText( v_ItemType,
288 v_ItemKeyNew,
289 'FROM_USER',
290 Notification_FromUser(FND_GLOBAL.RESP_ID,
291 FND_GLOBAL.RESP_APPL_ID));
292
293
294 wf_engine.SetItemAttrText( v_ItemType,
295 v_ItemKeyNew,
296 'TO_USER',
297 Notification_ToUser(FND_GLOBAL.USER_ID));
298
299 wf_engine.SetItemAttrText( v_ItemType,
300 v_ItemKeyNew,
301 'SCHED_GEN_DATE',
302 v_ScheduleGenDate);
303
304 --
305 IF (l_debug <> -1) THEN
306 rlm_core_sv.dlog(C_DEBUG,'Start EndProcess ');
307 END IF;
308 --
309 wf_engine.StartProcess(v_ItemType, v_ItemKeyNew);
310
311 -- Set various Header Attributes
312 /*
313 -- Decided to return Sucess even if errors/warnings generated since process itself
314 -- is successful according to Kathleen. Consistent with current DSP. Mohana
315 retcode := v_retcode;
316 */
317
318 IF (l_debug <> -1) THEN
319 rlm_core_sv.dpop(C_SDEBUG);
320 END IF;
321
322 SELECT hsecs INTO v_end_time FROM v$timer;
323 v_wf_msg_text:= 'Time spent in Work Flow call - '||(v_end_time-v_start_time)/100 ;
324 fnd_file.put_line(fnd_file.log,v_wf_msg_text);
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 --
329 retcode := g_PROC_ERROR;
330 --
331 IF (l_debug <> -1) THEN
332 rlm_core_sv.dlog(C_DEBUG,'Errors found ');
333 rlm_core_sv.dlog(C_DEBUG,'Error: ',SUBSTR(SQLERRM,1,1500));
334 rlm_core_sv.dpop(C_SDEBUG);
335 END IF;
336 --
337 -- rlm_core_sv.stop_debug;
338 --
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340
341 END StartDSPProcess;
342
343
344 PROCEDURE ValidateDemand(
345 itemtype IN VARCHAR2,
346 itemkey IN VARCHAR2,
347 actid IN NUMBER,
348 funcmode IN VARCHAR2,
349 resultout OUT NOCOPY VARCHAR2)
350 IS
351 --
352 v_header_id NUMBER;
353 v_status NUMBER;
354 retcode NUMBER;
355 errbuf VARCHAR2(2000);
356 v_Progress VARCHAR2(3) := '010';
357 e_DPFailed EXCEPTION;
358 --
359
360 BEGIN
361 --
362 IF (l_debug <> -1) THEN
363 rlm_core_sv.dpush(C_SDEBUG,'ValidateDemand');
364 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
365 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
366 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
367 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
368 END IF;
369
370 IF (FUNCMODE = 'RUN') THEN
371 -- Executable Statements
372 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
373 --
374 IF (l_debug <> -1) THEN
375 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
376 rlm_core_sv.dlog(C_DEBUG,' Before Validate');
377 END IF;
378 --
379 -- Bug 2868593
380 SAVEPOINT s_ValidateDemand;
381 --
382 SELECT hsecs INTO l_val_start_time from v$timer;
383 --
384 rlm_validatedemand_sv.GroupValidateDemand(v_header_id, v_status);
385 --
386 IF (l_debug <> -1) THEN
387 rlm_core_sv.dlog(C_DEBUG,' After Validate v_status: ', v_status);
388 rlm_core_sv.dlog(C_DEBUG,'g_schedule_PS', RLM_VALIDATEDEMAND_SV.g_schedule_PS);
389 END IF;
390
391 SELECT hsecs INTO l_val_end_time FROM v$timer;
392 --
393 IF v_status <> rlm_core_sv.k_PROC_ERROR THEN
394 --
395 IF RLM_VALIDATEDEMAND_SV.g_schedule_PS <> rlm_core_sv.k_PS_ERROR
396 THEN
397 --
398 -- Archive Demand
399 --
400 IF (l_debug <> -1) THEN
401 rlm_core_sv.dlog(C_DEBUG,'Archiving');
402 END IF;
403 --
404 resultout := 'COMPLETE:CONT';
405 --
406 ELSE
407 -- No Archiving
408 --
409 IF (l_debug <> -1) THEN
410 rlm_core_sv.dlog(C_DEBUG,'Not Archiving');
411 END IF;
412 --
413 resultout := 'COMPLETE:ABT';
414 --
415 END IF;
416 --
417 ELSE
418 --
419 IF (l_debug <> -1) THEN
420 rlm_core_sv.dlog(C_DEBUG,'Not Archiving');
421 END IF;
422 --
423 resultout := 'COMPLETE:ABT';
424 --
425 END IF;
426 --
427
428 IF (v_status = rlm_core_sv.k_PROC_ERROR) OR (rlm_validatedemand_sv.g_schedule_PS = rlm_core_sv.k_PS_ERROR) THEN
429 --
430 RAISE e_DPFailed;
431 --
432 END IF;
433 --
434
435 IF (l_debug <> -1) THEN
436 rlm_core_sv.dpop(C_SDEBUG);
437 END IF;
438 ---
439 RETURN;
440 --
441 END IF;
442
443 IF (FUNCMODE = 'CANCEL') THEN
444 -- Executable Statements
445 resultout := 'COMPLETE';
446 RETURN;
447 END IF;
448
449 IF (FUNCMODE = 'RESPOND') THEN
450 -- Executable Statements
451 resultout := 'COMPLETE';
452 RETURN;
453 END IF;
454
455 IF (FUNCMODE = 'FORWARD') THEN
456 -- Executable Statements
457 resultout := 'COMPLETE';
458 RETURN;
459 END IF;
460
461 IF (FUNCMODE = 'TRANSFER') THEN
462 -- Executable Statements
463 resultout := 'COMPLETE';
464 RETURN;
465 END IF;
466
467 IF (FUNCMODE = 'TIMEOUT') THEN
468 -- Executable Statements
469 resultout := 'COMPLETE';
470 RETURN;
471 END IF;
472
473 IF (FUNCMODE = 'CANCEL') THEN
474 -- Executable Statements
475 resultout := 'COMPLETE';
476 RETURN;
477 END IF;
478
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 --
482 IF (l_debug <> -1) THEN
483 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
484 END IF;
485 --
486 wf_core.context('RLM_WF_SV','ValidateDemand',
487 itemtype, itemkey,TO_CHAR(actid),funcmode);
488 resultout := 'COMPLETE:ABT';
489 --
490 IF (l_debug <> -1) THEN
491 rlm_core_sv.dpop(C_SDEBUG);
492 END IF;
493 --
494 WHEN e_DPFailed THEN
495 --
496 IF (l_debug <> -1) THEN
497 rlm_core_sv.dlog(C_DEBUG,'Validate Demand failed');
498 END IF;
499 --
500 ROLLBACK TO s_ValidateDemand; /* Bug 2868593 */
501 --
502 rlm_dp_sv.UpdateGroupPS(v_header_id,
503 g_Sch_rec.schedule_header_id,
504 g_Grp_rec,
505 rlm_core_sv.k_PS_ERROR,
506 'ALL');
507 --
508 rlm_dp_sv.UpdateHeaderPS(v_header_id,
509 g_Sch_rec.schedule_header_id);
510 --
511 rlm_message_sv.dump_messages(v_header_id);
512 rlm_message_sv.initialize_messages;
513 --
514 COMMIT;
515 --
516 wf_core.context('RLM_WF_SV','ValidateDemand',
517 itemtype, itemkey,TO_CHAR(actid),funcmode);
518 resultout := 'COMPLETE:ABT';
519 --
520 IF (l_debug <> -1) THEN
521 rlm_core_sv.dpop(C_SDEBUG);
522 END IF;
523 --
524 WHEN OTHERS THEN
525 --
526 wf_core.context('RLM_WF_SV','ValidateDemand',
527 itemtype, itemkey,TO_CHAR(actid),funcmode);
528 resultout := 'COMPLETE:ABT';
529 --
530 rlm_dp_sv.UpdateGroupPS(v_header_id,
531 g_Sch_rec.schedule_header_id,
532 g_Grp_rec,
533 rlm_core_sv.k_PS_ERROR,
534 'ALL');
535 --
536 rlm_dp_sv.UpdateHeaderPS(v_header_id,
537 g_Sch_rec.schedule_header_id);
538 --
539 rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
540 --
541 rlm_message_sv.dump_messages(v_header_id);
542 rlm_message_sv.initialize_messages;
543 --
544 COMMIT;
545 --
546 IF (l_debug <> -1) THEN
547 rlm_core_sv.dpop(C_SDEBUG);
548 END IF;
549 --
550 --RAISE;
551
552 END ValidateDemand;
553
554 PROCEDURE ManageDemand(
555 itemtype IN VARCHAR2,
556 itemkey IN VARCHAR2,
557 actid IN NUMBER,
558 funcmode IN VARCHAR2,
559 resultout OUT NOCOPY VARCHAR2)
560 IS
561 --
562 v_header_id NUMBER;
563 v_status NUMBER;
564 e_DPFailed EXCEPTION;
565 /*Bug 2581117 */
566 v_md_start_time NUMBER;
567 v_md_end_time NUMBER;
568 v_md_total NUMBER :=0;
569 v_md_msg_text VARCHAR2(32000);
570 BEGIN
571 --
572 IF (l_debug <> -1) THEN
573 rlm_core_sv.dpush(C_SDEBUG,'ManageDemand');
574 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
575 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
576 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
577 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
578 END IF;
579 --
580 IF (FUNCMODE = 'RUN') THEN
581 -- Executable Statements
582 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
583 --
584 IF (l_debug <> -1) THEN
585 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
586 END IF;
587 --
588 SAVEPOINT s_child_process;
589
590 SELECT hsecs INTO v_md_start_time FROM v$timer;
591
592 rlm_manage_demand_sv.ManageDemand(v_header_id,
593 g_Sch_rec,
594 g_Grp_rec,
595 v_status);
596 --
597 IF (l_debug <> -1) THEN
598 rlm_core_sv.dlog(C_DEBUG,' After ManageDemand v_status: ', v_status);
599 END IF;
600 --
601 SELECT hsecs INTO v_md_end_time FROM v$timer;
602 v_md_total:=v_md_total+(v_md_end_time-v_md_start_time)/100;
603
604 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
605 --
606 RAISE e_DPFailed;
607 --
608 END IF;
609 --
610 resultout := 'COMPLETE:SUCCESS';
611 --
612 IF (l_debug <> -1) THEN
613 rlm_core_sv.dpop(C_SDEBUG);
614 END IF;
615 --
616 v_md_msg_text :='Total Time spent in Managedemand call - '|| v_md_total;
617 fnd_file.put_line(fnd_file.log,v_md_msg_text);
618
619 RETURN;
620 --
621 END IF;
622
623 IF (FUNCMODE = 'CANCEL') THEN
624 -- Executable Statements
625 resultout := 'COMPLETE';
626 RETURN;
627 END IF;
628
629 IF (FUNCMODE = 'RESPOND') THEN
630 -- Executable Statements
631 resultout := 'COMPLETE';
632 RETURN;
633 END IF;
634
635 IF (FUNCMODE = 'FORWARD') THEN
636 -- Executable Statements
637 resultout := 'COMPLETE';
638 RETURN;
639 END IF;
640
641 IF (FUNCMODE = 'TRANSFER') THEN
642 -- Executable Statements
643 resultout := 'COMPLETE';
644 RETURN;
645 END IF;
646
647 IF (FUNCMODE = 'TIMEOUT') THEN
648 -- Executable Statements
649 resultout := 'COMPLETE';
650 RETURN;
651 END IF;
652
653 IF (FUNCMODE = 'CANCEL') THEN
654 -- Executable Statements
655 resultout := 'COMPLETE';
656 RETURN;
657 END IF;
658
659 EXCEPTION
660 WHEN NO_DATA_FOUND THEN
661 --
662 IF (l_debug <> -1) THEN
663 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
664 END IF;
665 --
666 wf_core.context('RLM_WF_SV','ManageDemand',
667 itemtype, itemkey,TO_CHAR(actid),funcmode);
668 resultout := 'COMPLETE:FAILURE';
669 --
670 IF (l_debug <> -1) THEN
671 rlm_core_sv.dpop(C_SDEBUG);
672 END IF;
673 --
674 WHEN e_DPFailed THEN
675 --
676 IF (l_debug <> -1) THEN
677 rlm_core_sv.dlog(C_DEBUG,'Manage Demand failed');
678 END IF;
679 --
680 ROLLBACK TO s_child_process;
681 --
682 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
683 g_Sch_rec.schedule_header_id,
684 g_Grp_rec,
685 rlm_core_sv.k_PS_ERROR);
686 COMMIT;
687 wf_core.context('RLM_WF_SV','ManageDemand',
688 itemtype, itemkey,TO_CHAR(actid),funcmode);
689 resultout := 'COMPLETE:FAILURE';
690 --
691 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
692 --
693 IF (l_debug <> -1) THEN
694 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header in MD.e_DPFailed');
695 END IF;
696 --
697 RAISE e_LockH;
698 END IF;
699 --
700 IF (l_debug <> -1) THEN
701 rlm_core_sv.dpop(C_SDEBUG);
702 END IF;
703 --
704 WHEN OTHERS THEN
705 wf_core.context('RLM_WF_SV','ManageDemand',
706 itemtype, itemkey,TO_CHAR(actid),funcmode);
707 resultout := 'COMPLETE:FAILURE';
708 --
709 IF (l_debug <> -1) THEN
710 rlm_core_sv.dpop(C_SDEBUG);
711 END IF;
712 --RAISE;
713
714 END ManageDemand;
715
716 PROCEDURE ManageForecast(
717 itemtype IN VARCHAR2,
718 itemkey IN VARCHAR2,
719 actid IN NUMBER,
720 funcmode IN VARCHAR2,
721 resultout OUT NOCOPY VARCHAR2)
722 IS
723 --
724 v_header_id NUMBER;
725 v_status NUMBER;
726 e_DPFailed EXCEPTION;
727 /*Bug 2581117 */
728 v_mf_start_time NUMBER;
729 v_mf_end_time NUMBER;
730 v_mf_total NUMBER:=0;
731 v_mf_msg_text VARCHAR2(32000);
732
733 BEGIN
734 --
735 IF (l_debug <> -1) THEN
736 rlm_core_sv.dpush(C_SDEBUG,'ManageForecast');
737 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
738 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
739 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
740 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
741 END IF;
742
743 IF (FUNCMODE = 'RUN') THEN
744 -- Executable Statements
745 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
746 --
747 IF (l_debug <> -1) THEN
748 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
749 rlm_core_sv.dlog(C_DEBUG,' ManageForecast');
750 END IF;
751 --
752 SELECT hsecs INTO v_mf_start_time FROM v$timer;
753
754 rlm_forecast_sv.ManageForecast(v_header_id,
755 g_Sch_rec,
756 g_Grp_rec,
757 v_status);
758 --
759 IF (l_debug <> -1) THEN
760 rlm_core_sv.dlog(C_DEBUG,' After ManageForecast v_status: ', v_status);
761 END IF;
762 --
763 SELECT hsecs INTO v_mf_end_time FROM v$timer;
764 v_mf_total:=v_mf_total+(v_mf_end_time-v_mf_start_time)/100;
765
766 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
767 --
768 RAISE e_DPFailed;
769 --
770 END IF;
771 --
772 resultout := 'COMPLETE:SUCCESS';
773 --
774 IF (l_debug <> -1) THEN
775 rlm_core_sv.dpop(C_SDEBUG);
776 END IF;
777 --
778 v_mf_msg_text:='Total Time spent in Manageforecast call - '|| v_mf_total ;
779 fnd_file.put_line(fnd_file.log, v_mf_msg_text);
780 RETURN;
781 END IF;
782
783 IF (FUNCMODE = 'CANCEL') THEN
784 -- Executable Statements
785 resultout := 'COMPLETE';
786 RETURN;
787 END IF;
788
789 IF (FUNCMODE = 'RESPOND') THEN
790 -- Executable Statements
791 resultout := 'COMPLETE';
792 RETURN;
793 END IF;
794
795 IF (FUNCMODE = 'FORWARD') THEN
796 -- Executable Statements
797 resultout := 'COMPLETE';
798 RETURN;
799 END IF;
800
801 IF (FUNCMODE = 'TRANSFER') THEN
802 -- Executable Statements
803 resultout := 'COMPLETE';
804 RETURN;
805 END IF;
806
807 IF (FUNCMODE = 'TIMEOUT') THEN
808 -- Executable Statements
809 resultout := 'COMPLETE';
810 RETURN;
811 END IF;
812
813 IF (FUNCMODE = 'CANCEL') THEN
814 -- Executable Statements
815 resultout := 'COMPLETE';
816 RETURN;
817 END IF;
818
819 EXCEPTION
820 WHEN NO_DATA_FOUND THEN
821 --
822 IF (l_debug <> -1) THEN
823 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
824 END IF;
825 --
826 wf_core.context('RLM_WF_SV','ManageForecast',
827 itemtype, itemkey,TO_CHAR(actid),funcmode);
828 resultout := 'COMPLETE:FAILURE';
829 --
830 IF (l_debug <> -1) THEN
831 rlm_core_sv.dpop(C_SDEBUG);
832 END IF;
833 --
834 WHEN e_DPFailed THEN
835 --
836 IF (l_debug <> -1) THEN
837 rlm_core_sv.dlog(C_DEBUG,'Manage Forecast failed');
838 END IF;
839 --
840 ROLLBACK TO s_child_process;
841 --
842 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
843 g_Sch_rec.schedule_header_id,
844 g_Grp_rec,
845 rlm_core_sv.k_PS_ERROR);
846 COMMIT;
847
848 wf_core.context('RLM_WF_SV','ManageForecast',
849 itemtype, itemkey,TO_CHAR(actid),funcmode);
850 resultout := 'COMPLETE:FAILURE';
851 --
852 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
853 --
854 IF (l_debug <> -1) THEN
855 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header in FD.e_DPFailed');
856 END IF;
857 --
858 RAISE e_LockH;
859 END IF;
860 --
861 IF (l_debug <> -1) THEN
862 rlm_core_sv.dpop(C_SDEBUG);
863 END IF;
864 --
865 WHEN OTHERS THEN
866 wf_core.context('RLM_WF_SV','ManageForecast',
867 itemtype, itemkey,TO_CHAR(actid),funcmode);
868 resultout := 'COMPLETE:FAILURE';
869 --
870 IF (l_debug <> -1) THEN
871 rlm_core_sv.dpop(C_SDEBUG);
872 END IF;
873 --RAISE;
874
875 END ManageForecast;
876
877 PROCEDURE ReconcileDemand(
878 itemtype IN VARCHAR2,
879 itemkey IN VARCHAR2,
880 actid IN NUMBER,
881 funcmode IN VARCHAR2,
882 resultout OUT NOCOPY VARCHAR2)
883 IS
884 --
885 v_header_id NUMBER;
886 v_status NUMBER;
887 e_DPFailed EXCEPTION;
888 /*Bug 2581117*/
889 v_rd_start_time NUMBER;
890 v_rd_end_time NUMBER;
891 v_rd_total NUMBER:=0;
892 v_rd_msg_text VARCHAR2(32000);
893 BEGIN
894 --
895 IF (l_debug <> -1) THEN
896 rlm_core_sv.dpush(C_SDEBUG,'ReconcileDemand');
897 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
898 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
899 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
900 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
901 END IF;
902 --
903 IF (FUNCMODE = 'RUN') THEN
904 -- Executable Statements
905 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
906 --
907 IF (l_debug <> -1) THEN
908 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
909 END IF;
910 --
911 SELECT hsecs INTO v_rd_start_time from v$timer;
912 --
913 rlm_rd_sv.RecDemand(v_header_id,
914 g_Sch_rec,
915 g_Grp_rec,
916 v_status);
917 --
918 IF (l_debug <> -1) THEN
919 rlm_core_sv.dlog(C_DEBUG,' After ReconcileDemand v_status: ', v_status);
920 END IF;
921 --
922 SELECT hsecs INTO v_rd_end_time FROM v$timer;
923 v_rd_total :=v_rd_total+(v_rd_end_time-v_rd_start_time)/100;
924 --
925 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
926 --
927 RAISE e_DPFailed;
928 --
929 END IF;
930 --
931 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
932 g_Sch_rec.schedule_header_id,
933 g_Grp_rec,
934 rlm_core_sv.k_PS_PROCESSED);
935 --
936 COMMIT;
937 --
938 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
939 --
940 IF (l_debug <> -1) THEN
941 rlm_core_sv.dlog(C_DEBUG,'Unable to lock header after processing grp');
942 END IF;
943 --
944 RAISE e_LockH;
945 --
946 END IF;
947 --
948 resultout := 'COMPLETE:SUCCESS';
949 --
950 IF (l_debug <> -1) THEN
951 rlm_core_sv.dpop(C_SDEBUG);
952 END IF;
953 --
954 v_rd_msg_text:='Total Time spent in RecDemand call - '|| v_rd_total ;
955 fnd_file.put_line(fnd_file.log, v_rd_msg_text);
956 --
957 RETURN;
958 --
959 END IF;
960
961 IF (FUNCMODE = 'CANCEL') THEN
962 -- Executable Statements
963 resultout := 'COMPLETE';
964 RETURN;
965 END IF;
966
967 IF (FUNCMODE = 'RESPOND') THEN
968 -- Executable Statements
969 resultout := 'COMPLETE';
970 RETURN;
971 END IF;
972
973 IF (FUNCMODE = 'FORWARD') THEN
974 -- Executable Statements
975 resultout := 'COMPLETE';
976 RETURN;
977 END IF;
978
979 IF (FUNCMODE = 'TRANSFER') THEN
980 -- Executable Statements
981 resultout := 'COMPLETE';
982 RETURN;
983 END IF;
984
985 IF (FUNCMODE = 'TIMEOUT') THEN
986 -- Executable Statements
987 resultout := 'COMPLETE';
988 RETURN;
989 END IF;
990
991 IF (FUNCMODE = 'CANCEL') THEN
992 -- Executable Statements
993 resultout := 'COMPLETE';
994 RETURN;
995 END IF;
996
997 EXCEPTION
998 WHEN NO_DATA_FOUND THEN
999 --
1000 IF (l_debug <> -1) THEN
1001 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1002 END IF;
1003 --
1004 wf_core.context('RLM_WF_SV','ReconcileDemand',
1005 itemtype, itemkey,TO_CHAR(actid),funcmode);
1006 resultout := 'COMPLETE:FAILURE';
1007 --
1008 IF (l_debug <> -1) THEN
1009 rlm_core_sv.dpop(C_SDEBUG);
1010 END IF;
1011 --
1012 WHEN e_DPFailed THEN
1013 --
1014 IF (l_debug <> -1) THEN
1015 rlm_core_sv.dlog(C_DEBUG,'Reconcile Demand failed');
1016 END IF;
1017 --
1018 IF g_Sch_rec.schedule_type <> RLM_DP_SV.k_SEQUENCED THEN
1019 --
1020 ROLLBACK TO s_child_process;
1021 --
1022 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
1023 g_Sch_rec.schedule_header_id,
1024 g_Grp_rec,
1025 rlm_core_sv.k_PS_ERROR);
1026 END IF;
1027 --
1028 COMMIT;
1029 wf_core.context('RLM_WF_SV','ReconcileDemand',
1030 itemtype, itemkey,TO_CHAR(actid),funcmode);
1031 --
1032 resultout := 'COMPLETE:FAILURE';
1033 --
1034 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
1035 --
1036 IF (l_debug <> -1) THEN
1037 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header in RD.e_DPFailed');
1038 END IF;
1039 --
1040 RAISE e_LockH;
1041 END IF;
1042 --
1043 IF (l_debug <> -1) THEN
1044 rlm_core_sv.dpop(C_SDEBUG);
1045 END IF;
1046 --
1047 WHEN OTHERS THEN
1048 wf_core.context('RLM_WF_SV','ReconcileDemand',
1049 itemtype, itemkey,TO_CHAR(actid),funcmode);
1050 resultout := 'COMPLETE:FAILURE';
1051 --
1052 IF (l_debug <> -1) THEN
1053 rlm_core_sv.dpop(C_SDEBUG);
1054 END IF;
1055 --RAISE;
1056
1057 END ReconcileDemand;
1058
1059 PROCEDURE PurgeInterface(
1060 itemtype IN VARCHAR2,
1061 itemkey IN VARCHAR2,
1062 actid IN NUMBER,
1063 funcmode IN VARCHAR2,
1064 resultout OUT NOCOPY VARCHAR2)
1065 IS
1066 --
1067 v_header_id NUMBER;
1068 v_status NUMBER;
1069 e_DPFailed EXCEPTION;
1070 --
1071 BEGIN
1072 --
1073 IF (l_debug <> -1) THEN
1074 rlm_core_sv.dpush(C_SDEBUG,'PurgeInterface');
1075 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1076 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1077 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1078 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1079 END IF;
1080 --
1081 IF (FUNCMODE = 'RUN') THEN
1082 -- Executable Statements
1083 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1084 --
1085 IF (l_debug <> -1) THEN
1086 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1087 END IF;
1088 --
1089 rlm_message_sv.dump_messages(v_header_id);
1090 rlm_dp_sv.PurgeInterfaceLines(v_header_id);
1091 rlm_message_sv.initialize_messages;
1092 COMMIT;
1093 --
1094 resultout := 'COMPLETE:SUCCESS';
1095 --
1096 IF (l_debug <> -1) THEN
1097 rlm_core_sv.dpop(C_SDEBUG);
1098 END IF;
1099 --
1100 RETURN;
1101 --
1102 END IF;
1103
1104 IF (FUNCMODE = 'CANCEL') THEN
1105 -- Executable Statements
1106 resultout := 'COMPLETE';
1107 RETURN;
1108 END IF;
1109
1110 IF (FUNCMODE = 'RESPOND') THEN
1111 -- Executable Statements
1112 resultout := 'COMPLETE';
1113 RETURN;
1114 END IF;
1115
1116 IF (FUNCMODE = 'FORWARD') THEN
1117 -- Executable Statements
1118 resultout := 'COMPLETE';
1119 RETURN;
1120 END IF;
1121
1122 IF (FUNCMODE = 'TRANSFER') THEN
1123 -- Executable Statements
1124 resultout := 'COMPLETE';
1125 RETURN;
1126 END IF;
1127
1128 IF (FUNCMODE = 'TIMEOUT') THEN
1129 -- Executable Statements
1130 resultout := 'COMPLETE';
1131 RETURN;
1132 END IF;
1133
1134 IF (FUNCMODE = 'CANCEL') THEN
1135 -- Executable Statements
1136 resultout := 'COMPLETE';
1137 RETURN;
1138 END IF;
1139
1140 EXCEPTION
1141 WHEN NO_DATA_FOUND THEN
1142 --
1143 IF (l_debug <> -1) THEN
1144 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1145 END IF;
1146 --
1147 wf_core.context('RLM_WF_SV','PurgeInterface',
1148 itemtype, itemkey,TO_CHAR(actid),funcmode);
1149 resultout := 'COMPLETE:FAILURE';
1150 --
1151 IF (l_debug <> -1) THEN
1152 rlm_core_sv.dpop(C_SDEBUG);
1153 END IF;
1154 --
1155 WHEN OTHERS THEN
1156 wf_core.context('RLM_WF_SV','PurgeInterface',
1157 itemtype, itemkey,TO_CHAR(actid),funcmode);
1158 resultout := 'COMPLETE:FAILURE';
1159 --
1160 IF (l_debug <> -1) THEN
1161 rlm_core_sv.dpop(C_SDEBUG);
1162 END IF;
1163 --
1164 RAISE;
1165
1166 END PurgeInterface;
1167
1168 PROCEDURE CheckErrors(
1169 itemtype IN VARCHAR2,
1170 itemkey IN VARCHAR2,
1171 actid IN NUMBER,
1172 funcmode IN VARCHAR2,
1173 resultout OUT NOCOPY VARCHAR2)
1174 IS
1175 -- Bug #: 3291401
1176 CURSOR c_process_status_schedule(p_header_id IN NUMBER) is
1177 SELECT process_status
1178 FROM rlm_schedule_headers
1179 WHERE header_id = p_header_id;
1180
1181 CURSOR c_process_status_interface(p_header_id IN NUMBER) is
1182 SELECT process_status
1183 FROM rlm_interface_headers
1184 WHERE header_id = p_header_id;
1185
1186 v_process_status NUMBER;
1187
1188 --
1189 v_header_id NUMBER;
1190 v_request_id NUMBER;
1191 v_schedule_num VARCHAR2(50);
1192 x_errors NUMBER := -1;
1193 x_real_errors NUMBER := -1;
1194 v_status NUMBER;
1195 e_DPFailed EXCEPTION;
1196
1197 BEGIN
1198 --
1199 IF (l_debug <> -1) THEN
1200 rlm_core_sv.dpush(C_SDEBUG,'CheckErrors');
1201 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1202 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1203 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1204 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1205 END IF;
1206 --
1207 IF (FUNCMODE = 'RUN') THEN
1208 -- Executable Statements
1209 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID'); --
1210 IF (l_debug <> -1) THEN
1211 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1212 END IF;
1213 --
1214 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
1215 --
1216 IF (l_debug <> -1) THEN
1217 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
1218 END IF;
1219 --
1220 v_schedule_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
1221 --
1222 IF (l_debug <> -1) THEN
1223 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_schedule_num);
1224 END IF;
1225 --
1226 rlm_message_sv.dump_messages(v_header_id);
1227 rlm_message_sv.initialize_messages;
1228 --
1229 -- Check for Errors
1230 -- After PurgeInterface, the succesfully processed schedule
1231 -- should have been deleted from interface tables.
1232 --
1233 SELECT COUNT(*)
1234 INTO x_errors
1235 FROM rlm_interface_headers
1236 WHERE header_id = v_header_id;
1237 --
1238 IF (l_debug <> -1) THEN
1239 rlm_core_sv.dlog(C_DEBUG,' No of Errors :', x_errors);
1240 END IF;
1241 --
1242 IF (x_errors = 0) then
1243 --
1244 -- Bug# 3291401 - Start
1245 -- Incase there is not data in the interface tables, checking for
1246 -- the schedule header status.
1247 OPEN c_process_status_schedule(v_header_id);
1248 --
1249 FETCH c_process_status_schedule INTO v_process_status;
1250 --
1251 IF (c_process_status_schedule%NOTFOUND) THEN
1252 --
1253 OPEN c_process_status_interface(v_header_id);
1254 FETCH c_process_status_interface INTO v_process_status;
1255 --
1256 IF (c_process_status_interface%NOTFOUND) THEN
1257 resultout := 'COMPLETE:ABT';
1258 END IF;
1259 --
1260 IF (v_process_status IS NOT NULL AND
1261 (v_process_status = rlm_core_sv.k_PS_PARTIAL_PROCESSED OR
1262 v_process_status = RLM_CORE_SV.k_PS_ERROR) ) THEN
1263 --
1264 -- Setting the x_errors to be more than one
1265 -- so that correct return value can be set
1266 x_errors := 1;
1267 END IF ;
1268 --
1269 CLOSE c_process_status_interface;
1270 --
1271 ELSIF (v_process_status IS NOT NULL AND
1272 (v_process_status = rlm_core_sv.k_PS_PARTIAL_PROCESSED OR
1273 v_process_status = RLM_CORE_SV.k_PS_ERROR) ) THEN
1274 -- Setting the x_errors to be more than one
1275 -- so that correct return value can be set
1276 x_errors := 1;
1277 END IF;
1278 --
1279 CLOSE c_process_status_schedule;
1280 --
1281 -- Bug# 3291401 - End
1282 --
1283 wf_engine.SetItemAttrNumber( itemtype,
1284 itemkey,
1285 'ERRORS_EXIST',
1286 g_PROC_SUCCESS); ---No Errors
1287 --
1288 ELSE
1289 --
1290 wf_engine.SetItemAttrNumber( itemtype,
1291 itemkey,
1292 'ERRORS_EXIST',
1293 g_PROC_ERROR); ---Errors Exist
1294 --
1295 END IF;
1296 --
1297 IF (x_errors > 0 ) THEN
1298 resultout := 'COMPLETE:ERRORS';
1299 --
1300 IF (l_debug <> -1) THEN
1301 rlm_core_sv.dlog(C_DEBUG,' resultout :', resultout);
1302 rlm_core_sv.dpop(C_SDEBUG);
1303 END IF;
1304 --
1305 RETURN;
1306 ELSE
1307 resultout := 'COMPLETE:N';
1308 --
1309 IF (l_debug <> -1) THEN
1310 rlm_core_sv.dlog(C_DEBUG,' resultout :', resultout);
1311 rlm_core_sv.dpop(C_SDEBUG);
1312 END IF;
1313 --
1314 RETURN;
1315 END IF;
1316 --
1317 END IF;
1318
1319 IF (FUNCMODE = 'CANCEL') THEN
1320 -- Executable Statements
1321 resultout := 'COMPLETE';
1322 RETURN;
1323 END IF;
1324
1325 IF (FUNCMODE = 'RESPOND') THEN
1326 -- Executable Statements
1327 resultout := 'COMPLETE';
1328 RETURN;
1329 END IF;
1330
1331 IF (FUNCMODE = 'FORWARD') THEN
1332 -- Executable Statements
1333 resultout := 'COMPLETE';
1334 RETURN;
1335 END IF;
1336
1337 IF (FUNCMODE = 'TRANSFER') THEN
1338 -- Executable Statements
1339 resultout := 'COMPLETE';
1340 RETURN;
1341 END IF;
1342
1343 IF (FUNCMODE = 'TIMEOUT') THEN
1344 -- Executable Statements
1345 resultout := 'COMPLETE';
1346 RETURN;
1347 END IF;
1348
1349 IF (FUNCMODE = 'CANCEL') THEN
1350 -- Executable Statements
1351 resultout := 'COMPLETE';
1352 RETURN;
1353 END IF;
1354
1355 EXCEPTION
1356 WHEN NO_DATA_FOUND THEN
1357 --
1358 IF (l_debug <> -1) THEN
1359 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1360 END IF;
1361 --
1362 wf_core.context('RLM_WF_SV','CheckErrors',
1363 itemtype, itemkey,TO_CHAR(actid),funcmode);
1364 resultout := 'COMPLETE:ABT';
1365 --
1366 IF (l_debug <> -1) THEN
1367 rlm_core_sv.dpop(C_SDEBUG);
1368 END IF;
1369 --
1370 WHEN OTHERS THEN
1371 wf_core.context('RLM_WF_SV','CheckErrors',
1372 itemtype, itemkey,TO_CHAR(actid),funcmode);
1373 resultout := 'COMPLETE:ABT';
1374 --
1375 IF (l_debug <> -1) THEN
1376 rlm_core_sv.dpop(C_SDEBUG);
1377 END IF;
1378 --
1379 RAISE;
1380
1381 END CheckErrors;
1382
1383 PROCEDURE RunReport(
1384 itemtype IN VARCHAR2,
1385 itemkey IN VARCHAR2,
1386 actid IN NUMBER,
1387 funcmode IN VARCHAR2,
1388 resultout OUT NOCOPY VARCHAR2)
1389 IS
1390 --
1391 v_header_id NUMBER;
1392 v_status NUMBER;
1393 v_org_id NUMBER;
1394 v_request_id NUMBER := -1;
1395 v_sched_num VARCHAR2(50);
1396 x_request_id NUMBER := -1;
1397 x_errors NUMBER := -1;
1398 x_no_copies NUMBER :=0;
1399 x_print_style VARCHAR2(30);
1400 x_printer VARCHAR2(30);
1401 x_save_output_flag VARCHAR2(1);
1402 x_result BOOLEAN;
1403 e_DPFailed EXCEPTION;
1404 --
1405 BEGIN
1406 --
1407 IF (l_debug <> -1) THEN
1408 rlm_core_sv.dpush(C_SDEBUG,'RunReport');
1409 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1410 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1411 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1412 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1413 END IF;
1414 --
1415 IF (FUNCMODE = 'RUN') THEN
1416 -- Executable Statements
1417 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1418 --
1419 IF (l_debug <> -1) THEN
1420 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1421 END IF;
1422 --
1423 v_org_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
1424 --
1425 IF (l_debug <> -1) THEN
1426 rlm_core_sv.dlog(C_DEBUG,' Org Id :', v_org_id);
1427 END IF;
1428 --
1429 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
1430 --
1431 IF (l_debug <> -1) THEN
1432 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
1433 END IF;
1434 --
1435 v_sched_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
1436 --
1437 IF (l_debug <> -1) THEN
1438 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_sched_num);
1439 END IF;
1440
1441 /*
1442 -- Needs to be used once DSP concurrent program sets print options parameters
1443 x_result :=fnd_concurrent.get_request_print_options(fnd_global.conc_request_id,
1444 x_no_copies ,
1445 x_print_style ,
1446 x_printer ,
1447 x_save_output_flag );
1448 IF (x_result =TRUE) then
1449 x_result :=fnd_request.set_print_options(x_printer,
1450 x_print_style,
1451 x_no_copies,
1452 NULL,
1453 'N');
1454 END IF;
1455 */
1456 --
1457 fnd_request.set_org_id(v_org_id);
1458 --
1459 x_request_id := fnd_request.submit_request ('RLM',
1460 'RLMDPDER',
1461 NULL,
1462 NULL,
1463 FALSE,
1464 v_org_id,
1465 v_request_id,
1466 v_request_id,
1467 NULL,
1468 NULL,
1469 NULL,
1470 NULL,
1471 NULL,
1472 NULL,
1473 NULL,
1474 NULL,
1475 NULL,
1476 NULL,
1477 NULL,
1478 NULL,
1479 NULL,
1480 NULL,
1481 NULL, --v_sched_num
1482 NULL, --v_sched_num
1483 NULL,
1484 NULL,
1485 NULL,
1486 NULL,
1487 NULL,
1488 NULL,
1489 NULL);
1490 -- END IF;
1491 -- commit;
1492
1493 resultout := 'COMPLETE:SUCCESS';
1494 --
1495 IF (l_debug <> -1) THEN
1496 rlm_core_sv.dpop(C_SDEBUG);
1497 END IF;
1498 --
1499 RETURN;
1500 END IF;
1501
1502 IF (FUNCMODE = 'CANCEL') THEN
1503 -- Executable Statements
1504 resultout := 'COMPLETE';
1505 RETURN;
1506 END IF;
1507
1508 IF (FUNCMODE = 'RESPOND') THEN
1509 -- Executable Statements
1510 resultout := 'COMPLETE';
1511 RETURN;
1512 END IF;
1513
1514 IF (FUNCMODE = 'FORWARD') THEN
1515 -- Executable Statements
1516 resultout := 'COMPLETE';
1517 RETURN;
1518 END IF;
1519
1520 IF (FUNCMODE = 'TRANSFER') THEN
1521 -- Executable Statements
1522 resultout := 'COMPLETE';
1523 RETURN;
1524 END IF;
1525
1526 IF (FUNCMODE = 'TIMEOUT') THEN
1527 -- Executable Statements
1528 resultout := 'COMPLETE';
1529 RETURN;
1530 END IF;
1531
1532 IF (FUNCMODE = 'CANCEL') THEN
1533 -- Executable Statements
1534 resultout := 'COMPLETE';
1535 RETURN;
1536 END IF;
1537
1538 EXCEPTION
1539 WHEN NO_DATA_FOUND THEN
1540 --
1541 IF (l_debug <> -1) THEN
1542 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1543 END IF;
1544 --
1545 wf_core.context('RLM_WF_SV','RunReport',
1546 itemtype, itemkey,TO_CHAR(actid),funcmode);
1547 resultout := 'COMPLETE:FAILURE';
1548 --
1549 IF (l_debug <> -1) THEN
1550 rlm_core_sv.dpop(C_SDEBUG);
1551 END IF;
1552 --
1553 WHEN OTHERS THEN
1554 wf_core.context('RLM_WF_SV','RunReport',
1555 itemtype, itemkey,TO_CHAR(actid),funcmode);
1556 resultout := 'COMPLETE:FAILURE';
1557 --
1558 IF (l_debug <> -1) THEN
1559 rlm_core_sv.dpop(C_SDEBUG);
1560 END IF;
1561 --
1562 RAISE;
1563
1564 END RunReport;
1565
1566 PROCEDURE GetScheduleDetails( x_Header_Id IN NUMBER,
1567 x_Schedule_Num OUT NOCOPY VARCHAR2,
1568 x_Customer_Name OUT NOCOPY VARCHAR2,
1569 x_Schedule_Gen_Date OUT NOCOPY DATE)
1570 -- Bug#: 3053299 - Added Schedule Generation Date as argument
1571 IS
1572 BEGIN
1573 --
1574 IF (l_debug <> -1) THEN
1575 rlm_core_sv.dpush(C_SDEBUG,'GetScheduleDetails');
1576 rlm_core_sv.dlog(C_DEBUG,'x_Header_Id ', x_Header_Id);
1577 END IF;
1578 --
1579 select schedule_reference_num, cust_name_ext, sched_generation_date
1580 into x_Schedule_Num, x_Customer_Name , x_Schedule_Gen_Date
1581 from rlm_interface_headers
1582 where header_id = x_Header_Id ;
1583 --
1584 IF (l_debug <> -1) THEN
1585 rlm_core_sv.dlog(C_DEBUG,' Schedule Generation Date :',
1586 TO_CHAR(x_Schedule_Gen_Date));
1587 rlm_core_sv.dlog(C_DEBUG,' Successful select');
1588 rlm_core_sv.dpop(C_SDEBUG);
1589 END IF;
1590 --
1591 EXCEPTION
1592 WHEN NO_DATA_FOUND THEN
1593 --
1594 IF (l_debug <> -1) THEN
1595 rlm_core_sv.dpop(C_SDEBUG, 'No Data Found');
1596 END IF;
1597 --
1598 WHEN OTHERS THEN
1599 --
1600 IF (l_debug <> -1) THEN
1601 rlm_core_sv.dpop(C_SDEBUG, 'Other Errors');
1602 END IF;
1603
1604 END GetScheduleDetails;
1605
1606
1607 PROCEDURE StartDSPLoop( errbuf OUT NOCOPY VARCHAR2,
1608 retcode OUT NOCOPY VARCHAR2,
1609 p_Header_Id IN NUMBER,
1610 p_Line_id IN NUMBER,
1611 v_Sch_rec IN rlm_interface_headers%ROWTYPE,
1612 v_Grp_rec IN rlm_dp_sv.t_Group_rec)
1613 IS
1614 --
1615 v_ItemKey VARCHAR2(100) := to_char(p_Header_Id)||
1616 '+' || to_char(p_Line_id);
1617 v_ItemType VARCHAR2(30) := g_ItemType;
1618 v_ProcessName VARCHAR2(30) := g_ProcessNameLoop;
1619 v_ScheduleNum VARCHAR2(35) ;
1620 v_Customer VARCHAR2(30) ;
1621 v_ScheduleGenDate DATE ;
1622 v_org_id NUMBER;
1623 v_retcode NUMBER;
1624 v_count NUMBER;
1625 v_dummy NUMBER DEFAULT 0;
1626 e_DSPFailed EXCEPTION;
1627 BEGIN
1628 --
1629 IF (l_debug <> -1) THEN
1630 rlm_core_sv.dpush(C_SDEBUG,'StartDSPLoop');
1631 rlm_core_sv.dlog(C_DEBUG,'p_Header_Id ', p_Header_Id);
1632 rlm_core_sv.dlog(C_DEBUG,'Starting process:',v_ItemKey);
1633 rlm_core_sv.dlog(C_DEBUG,'Org ID', v_org_id);
1634 END IF;
1635 --
1636 -- fnd_profile.get('ORG_ID', v_org_id);
1637 --
1638 v_org_id := v_Sch_rec.org_id;
1639 --
1640 IF (l_debug <> -1) THEN
1641 rlm_core_sv.dlog(C_DEBUG,'Org Id ', v_org_id);
1642 END IF;
1643 --
1644 -- Bug#: 3053299 - Added Schedule Generation Date as argument
1645 GetScheduleDetails(p_Header_Id, v_ScheduleNum, v_Customer, v_ScheduleGenDate);
1646 --
1647 IF (l_debug <> -1) THEN
1648 rlm_core_sv.dlog(C_DEBUG,'v_ScheduleNum ', v_ScheduleNum);
1649 rlm_core_sv.dlog(C_DEBUG,'v_Customer ', v_Customer);
1650 END IF;
1651 --
1652 -- Set various Header Attributes
1653
1654 wf_engine.SetItemAttrNumber( v_ItemType,
1655 v_ItemKey,
1656 'USER_ID',
1657 FND_GLOBAL.USER_ID);
1658
1659 wf_engine.SetItemAttrNumber( v_ItemType,
1660 v_ItemKey,
1661 'APPLICATION_ID',
1662 FND_GLOBAL.RESP_APPL_ID);
1663
1664 wf_engine.SetItemAttrNumber( v_ItemType,
1665 v_ItemKey,
1666 'REQUEST_ID',
1667 FND_GLOBAL.CONC_REQUEST_ID);
1668
1669 wf_engine.SetItemAttrNumber( v_ItemType,
1670 v_ItemKey,
1671 'RESPONSIBILITY_ID',
1672 FND_GLOBAL.RESP_ID);
1673
1674 wf_engine.SetItemAttrNumber( v_ItemType,
1675 v_ItemKey,
1676 'ORG_ID',
1677 v_org_id);
1678
1679 wf_engine.SetItemAttrNumber( v_ItemType,
1680 v_ItemKey,
1681 'HEADER_ID',
1682 p_Header_Id);
1683
1684 wf_engine.SetItemAttrText( v_ItemType,
1685 v_ItemKey,
1686 'SCHEDULE_NUMBER',
1687 v_ScheduleNum);
1688
1689 wf_engine.SetItemAttrText( v_ItemType,
1690 v_ItemKey,
1691 'CUSTOMER_NAME',
1692 v_Customer);
1693 wf_engine.SetItemAttrNumber( v_ItemType,
1694 v_ItemKey,
1695 'ERRORS_EXIST',
1696 v_dummy);
1697
1698 -- Bug#: 3053299 - Setting the tokens for From User,
1699 -- To User and Schedule Generation Date
1700
1701
1702 wf_engine.SetItemAttrText( v_ItemType,
1703 v_ItemKey,
1704 'FROM_USER',
1705 Notification_FromUser(FND_GLOBAL.RESP_ID,
1706 FND_GLOBAL.RESP_APPL_ID));
1707
1708
1709 wf_engine.SetItemAttrText( v_ItemType,
1710 v_ItemKey,
1711 'TO_USER',
1712 Notification_ToUser(FND_GLOBAL.USER_ID));
1713
1714 wf_engine.SetItemAttrText( v_ItemType,
1715 v_ItemKey,
1716 'SCHED_GEN_DATE',
1717 v_ScheduleGenDate);
1718
1719 --
1720 IF (l_debug <> -1) THEN
1721 rlm_core_sv.dlog(C_DEBUG,'Before Start Process DSP Loop');
1722 END IF;
1723 --
1724 wf_engine.StartProcess(v_ItemType, v_ItemKey);
1725 --
1726 IF (l_debug <> -1) THEN
1727 rlm_core_sv.dlog(C_DEBUG,'After Start Process DSP Loop ');
1728 END IF;
1729 --
1730 v_retcode := wf_engine.GetItemAttrNumber(v_ItemType,v_ItemKey,'ERRORS_EXIST');
1731 --
1732 IF (l_debug <> -1) THEN
1733 rlm_core_sv.dlog(C_DEBUG,' Errors Status: ', v_retcode);
1734 END IF;
1735 --
1736 retcode := g_PROC_SUCCESS;
1737 --
1738 IF (l_debug <> -1) THEN
1739 rlm_core_sv.dpop(C_SDEBUG);
1740 END IF;
1741 --
1742 EXCEPTION
1743 --
1744 WHEN OTHERS THEN
1745 --
1746 IF (l_debug <> -1) THEN
1747 rlm_core_sv.dlog(C_DEBUG,'Errors found ');
1748 rlm_core_sv.dlog(C_DEBUG,'Error: ',SUBSTR(SQLERRM,1,1500));
1749 END IF;
1750 --
1751 retcode := g_PROC_ERROR;
1752 --
1753 IF (l_debug <> -1) THEN
1754 rlm_core_sv.dpop(C_SDEBUG);
1755 END IF;
1756 --
1757 -- rlm_core_sv.stop_debug;
1758 --
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760
1761 END StartDSPLoop;
1762
1763
1764
1765
1766 PROCEDURE CreateDSPLoop( errbuf OUT NOCOPY VARCHAR2,
1767 retcode OUT NOCOPY VARCHAR2,
1768 p_Header_Id IN NUMBER,
1769 p_Line_id IN NUMBER)
1770 IS
1771 --
1772 v_ItemKey VARCHAR2(100) := to_char(p_Header_Id)||
1773 '+' || to_char(p_Line_id);
1774 v_ItemType VARCHAR2(30) := g_ItemType;
1775 v_ProcessName VARCHAR2(30) := g_ProcessNameLoop;
1776 v_org_id NUMBER;
1777 v_retcode NUMBER;
1778 v_count NUMBER;
1779 v_dummy NUMBER DEFAULT 0;
1780 e_DSPFailed EXCEPTION;
1781 --
1782 BEGIN
1783 --
1784 IF (l_debug <> -1) THEN
1785 rlm_core_sv.dpush(C_SDEBUG,'CreateDSPLoop');
1786 rlm_core_sv.dlog(C_DEBUG,'p_Header_Id ', p_Header_Id);
1787 rlm_core_sv.dlog(C_DEBUG,'Creating Loop:',v_ItemKey);
1788 rlm_core_Sv.dlog(C_DEBUG, 'v_ItemType', v_ItemType);
1789 END IF;
1790 --
1791 -- fnd_profile.get('ORG_ID', v_org_id);
1792 --
1793 IF (l_debug <> -1) THEN
1794 rlm_core_sv.dlog(C_DEBUG, 'Org Id ', MO_GLOBAL.get_current_org_id);
1795 END IF;
1796 --
1797 -- Abort the process if it has hung
1798 BEGIN
1799 --
1800 IF (l_debug <> -1) THEN
1801 rlm_core_sv.dlog(C_DEBUG,'Aborting old processes');
1802 END IF;
1803 --
1804 wf_engine.AbortProcess(itemtype => v_ItemType,
1805 itemkey => v_ItemKey);
1806 --
1807 IF (l_debug <> -1) THEN
1808 rlm_core_sv.dlog(C_DEBUG,'after abort bad process');
1809 END IF;
1810 --
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 --
1814 IF (l_debug <> -1) THEN
1815 rlm_core_sv.dlog(C_DEBUG,'Did not abort any process');
1816 END IF;
1817 --
1818 END;
1819 --
1820 -- Check whether Item has already been run in Workflow and purge it
1821 -- Bug 2756981: Set force to TRUE to allow child processes to be purged
1822 --
1823 wf_purge.Items(itemtype => v_ItemType,
1824 itemkey => v_ItemKey,
1825 enddate => sysdate,
1826 docommit => FALSE,
1827 force => TRUE);
1828 --
1829 IF (l_debug <> -1) THEN
1830 rlm_core_sv.dlog(C_DEBUG,'After Purge for Item key ',v_ItemKey);
1831 END IF;
1832 --
1833 wf_engine.CreateProcess(v_ItemType, v_ItemKey, v_ProcessName);
1834 wf_engine.SetItemParent(v_ItemType,v_ItemKey,v_ItemType,to_char(p_Header_Id),
1835 to_char(p_Header_Id));
1836 --
1837 wf_engine.SetItemUserKey(v_ItemType, v_ItemKey,
1838 g_Sch_rec.schedule_reference_num);
1839
1840 --
1841 retcode := g_PROC_SUCCESS;
1842 --
1843 IF (l_debug <> -1) THEN
1844 rlm_core_sv.dlog(C_DEBUG,'After Create Process DSP Loop ');
1845 rlm_core_sv.dpop(C_SDEBUG);
1846 END IF;
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 --
1851 IF (l_debug <> -1) THEN
1852 rlm_core_sv.dlog(C_DEBUG,'Errors found ');
1853 rlm_core_sv.dlog(C_DEBUG,'Error: ',SUBSTR(SQLERRM,1,1500));
1854 END IF;
1855 --
1856 retcode := g_PROC_ERROR;
1857 --
1858 IF (l_debug <> -1) THEN
1859 rlm_core_sv.dpop(C_SDEBUG);
1860 END IF;
1861 --
1862 -- rlm_core_sv.stop_debug;
1863 --
1864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865
1866
1867 END CreateDSPLoop;
1868
1869
1870 PROCEDURE UpdateHeaderPS(
1871 itemtype IN VARCHAR2,
1872 itemkey IN VARCHAR2,
1873 actid IN NUMBER,
1874 funcmode IN VARCHAR2,
1875 resultout OUT NOCOPY VARCHAR2)
1876 IS
1877 --
1878 v_header_id NUMBER;
1879 --
1880 BEGIN
1881 --
1882 IF (l_debug <> -1) THEN
1883 rlm_core_sv.dpush(C_SDEBUG,'UpdateHeaderPS');
1884 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1885 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1886 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1887 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1888 END IF;
1889 --
1890 IF (FUNCMODE = 'RUN') THEN
1891 -- Executable Statements
1892 --
1893 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1894 --
1895 IF (l_debug <> -1) THEN
1896 rlm_core_sv.dlog(C_DEBUG,'WF Header_id :',v_header_id);
1897 rlm_core_sv.dlog(C_DEBUG,' Header_id :', g_Sch_rec.header_id);
1898 rlm_core_sv.dlog(C_DEBUG,'schedule_header_id',
1899 g_Sch_rec.schedule_header_id);
1900
1901 END IF;
1902 --
1903 IF g_Sch_rec.header_id IS NULL THEN
1904 --
1905 IF (l_debug <> -1) THEN
1906 rlm_core_sv.dlog(C_DEBUG,'Assigning the global variable.');
1907 END IF;
1908 --
1909 SELECT * INTO g_Sch_rec
1910 FROM rlm_interface_headers
1911 WHERE header_id = v_header_id;
1912 --
1913 END IF;
1914 rlm_dp_sv.UpdateHeaderPS(g_Sch_rec.header_id,
1915 g_Sch_rec.schedule_header_id);
1916
1917 --
1918 resultout := 'COMPLETE:SUCCESS';
1919 --
1920 IF (l_debug <> -1) THEN
1921 rlm_core_sv.dpop(C_SDEBUG);
1922 END IF;
1923 --
1924 RETURN;
1925 --
1926 END IF;
1927
1928 IF (FUNCMODE = 'CANCEL') THEN
1929 -- Executable Statements
1930 resultout := 'COMPLETE';
1931 RETURN;
1932 END IF;
1933
1934 IF (FUNCMODE = 'RESPOND') THEN
1935 -- Executable Statements
1936 resultout := 'COMPLETE';
1937 RETURN;
1938 END IF;
1939
1940 IF (FUNCMODE = 'FORWARD') THEN
1941 -- Executable Statements
1942 resultout := 'COMPLETE';
1943 RETURN;
1944 END IF;
1945
1946 IF (FUNCMODE = 'TRANSFER') THEN
1947 -- Executable Statements
1948 resultout := 'COMPLETE';
1949 RETURN;
1950 END IF;
1951
1952 IF (FUNCMODE = 'TIMEOUT') THEN
1953 -- Executable Statements
1954 resultout := 'COMPLETE';
1955 RETURN;
1956 END IF;
1957
1958 IF (FUNCMODE = 'CANCEL') THEN
1959 -- Executable Statements
1960 resultout := 'COMPLETE';
1961 RETURN;
1962 END IF;
1963
1964 EXCEPTION
1965 WHEN NO_DATA_FOUND THEN
1966 --
1967 IF (l_debug <> -1) THEN
1968 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1969 END IF;
1970 --
1971 wf_core.context('RLM_WF_SV','UpdateHeaderPS',
1972 itemtype, itemkey,TO_CHAR(actid),funcmode);
1973 resultout := 'COMPLETE:FAILURE';
1974 --
1975 IF (l_debug <> -1) THEN
1976 rlm_core_sv.dpop(C_SDEBUG);
1977 END IF;
1978 --
1979 WHEN OTHERS THEN
1980 --
1981 IF (l_debug <> -1) THEN
1982 rlm_core_sv.dlog(C_DEBUG,'When Others');
1983 END IF;
1984 --
1985 wf_core.context('RLM_WF_SV','UpdateHeaderPS',
1986 itemtype, itemkey,TO_CHAR(actid),funcmode);
1987 resultout := 'COMPLETE:FAILURE';
1988 --
1989 IF (l_debug <> -1) THEN
1990 rlm_core_sv.dpop(C_SDEBUG);
1991 END IF;
1992 --RAISE;
1993 END UpdateHeaderPS;
1994
1995
1996 PROCEDURE ProcessGroupDemand(
1997 itemtype IN VARCHAR2,
1998 itemkey IN VARCHAR2,
1999 actid IN NUMBER,
2000 funcmode IN VARCHAR2,
2001 resultout OUT NOCOPY VARCHAR2)
2002 IS
2003 --
2004 v_header_id NUMBER;
2005 v_status NUMBER;
2006 e_MDFailed EXCEPTION;
2007 e_MFFailed EXCEPTION;
2008 e_RDFailed EXCEPTION;
2009 /*Bug 2581117 */
2010 v_md_start_time NUMBER;
2011 v_md_end_time NUMBER;
2012 v_md_total NUMBER :=0;
2013 v_rd_start_time NUMBER;
2014 v_rd_end_time NUMBER;
2015 v_rd_total NUMBER:=0;
2016 v_mf_start_time NUMBER;
2017 v_mf_end_time NUMBER;
2018 v_mf_total NUMBER:=0;
2019 v_msg_text VARCHAR2(32000);
2020
2021 --
2022 BEGIN
2023 --
2024 IF (l_debug <> -1) THEN
2025 rlm_core_sv.dpush(C_SDEBUG,'ProcessGroupDemand');
2026 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2027 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2028 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2029 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2030 END IF;
2031 --
2032 IF (FUNCMODE = 'RUN') THEN
2033 -- Executable Statements
2034 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2035 --
2036 IF (l_debug <> -1) THEN
2037 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2038 END IF;
2039 --
2040 SAVEPOINT s_child_process;
2041 SELECT hsecs INTO v_md_start_time FROM v$timer;
2042 rlm_manage_demand_sv.ManageDemand(v_header_id,
2043 g_Sch_rec,
2044 g_Grp_rec,
2045 v_status);
2046 --
2047 IF (l_debug <> -1) THEN
2048 rlm_core_sv.dlog(C_DEBUG,' After ManageDemand v_status: ', v_status);
2049 END IF;
2050 --
2051 SELECT hsecs INTO v_md_end_time FROM v$timer;
2052 v_md_total:=v_md_total+(v_md_end_time-v_md_start_time)/100;
2053
2054 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2055 --
2056 RAISE e_MDFailed;
2057 --
2058 END IF;
2059 --
2060 IF (l_debug <> -1) THEN
2061 rlm_core_sv.dlog(C_DEBUG,'Before Manage Forecast');
2062 END IF;
2063 --
2064 SELECT hsecs INTO v_mf_start_time FROM v$timer;
2065
2066 rlm_forecast_sv.ManageForecast(v_header_id,
2067 g_Sch_rec,
2068 g_Grp_rec,
2069 v_status);
2070 --
2071 IF (l_debug <> -1) THEN
2072 rlm_core_sv.dlog(C_DEBUG,' After ManageForecast v_status: ', v_status);
2073 END IF;
2074 --
2075 SELECT hsecs INTO v_mf_end_time FROM v$timer;
2076 v_mf_total:=v_mf_total+(v_mf_end_time-v_mf_start_time)/100;
2077
2078 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2079 --
2080 RAISE e_MFFailed;
2081 --
2082 END IF;
2083 --
2084 IF (l_debug <> -1) THEN
2085 rlm_core_sv.dlog(C_DEBUG,'Before Reconcile Demand');
2086 END IF;
2087 --
2088 SELECT hsecs INTO v_rd_start_time FROM v$timer;
2089
2090 rlm_rd_sv.RecDemand(v_header_id,
2091 g_Sch_rec,
2092 g_Grp_rec,
2093 v_status);
2094
2095 --
2096 IF (l_debug <> -1) THEN
2097 rlm_core_sv.dlog(C_DEBUG, 'After ReconcileDemand v_status', v_status);
2098 END IF;
2099 --
2100 SELECT hsecs INTO v_rd_end_time FROM v$timer;
2101 v_rd_total :=v_rd_total+(v_rd_end_time-v_rd_start_time)/100;
2102 --
2103 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2104 --
2105 RAISE e_RDFailed;
2106 --
2107 END IF;
2108 --
2109 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2110 g_Sch_rec.schedule_header_id,
2111 g_Grp_rec,
2112 rlm_core_sv.k_PS_PROCESSED);
2113
2114 COMMIT;
2115 --
2116 resultout := 'COMPLETE:SUCCESS';
2117 --
2118 IF (l_debug <> -1) THEN
2119 rlm_core_sv.dpop(C_SDEBUG);
2120 END IF;
2121 --
2122 l_msg_text:='Total Time spent in Validatedemand call - '||
2123 (l_val_end_time-l_val_start_time)/100;
2124 fnd_file.put_line(fnd_file.log,l_msg_text);
2125
2126 l_msg_text:='Time spent in CompareSched call - '||
2127 (l_comp_end_time-l_comp_start_time)/100 ;
2128 fnd_file.put_line(fnd_file.log,l_msg_text);
2129
2130 v_msg_text:='Total Time spent in Managedemand call - '|| v_md_total;
2131 fnd_file.put_line(fnd_file.log, v_msg_text);
2132
2133 v_msg_text:='Total Time spent in Manageforecast call - '|| v_mf_total ;
2134 fnd_file.put_line(fnd_file.log,v_msg_text);
2135
2136 v_msg_text:='Total Time spent in RecDemand call - '|| v_rd_total ;
2137 fnd_file.put_line(fnd_file.log,v_msg_text);
2138
2139 RETURN;
2140 --
2141 END IF;
2142
2143 IF (FUNCMODE = 'CANCEL') THEN
2144 -- Executable Statements
2145 resultout := 'COMPLETE';
2146 RETURN;
2147 END IF;
2148
2149 IF (FUNCMODE = 'RESPOND') THEN
2150 -- Executable Statements
2151 resultout := 'COMPLETE';
2152 RETURN;
2153 END IF;
2154
2155 IF (FUNCMODE = 'FORWARD') THEN
2156 -- Executable Statements
2157 resultout := 'COMPLETE';
2158 RETURN;
2159 END IF;
2160
2161 IF (FUNCMODE = 'TRANSFER') THEN
2162 -- Executable Statements
2163 resultout := 'COMPLETE';
2164 RETURN;
2165 END IF;
2166
2167 IF (FUNCMODE = 'TIMEOUT') THEN
2168 -- Executable Statements
2169 resultout := 'COMPLETE';
2170 RETURN;
2171 END IF;
2172
2173 IF (FUNCMODE = 'CANCEL') THEN
2174 -- Executable Statements
2175 resultout := 'COMPLETE';
2176 RETURN;
2177 END IF;
2178
2179 EXCEPTION
2180 WHEN NO_DATA_FOUND THEN
2181 --
2182 IF (l_debug <> -1) THEN
2183 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2184 END IF;
2185 --
2186 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2187 itemtype, itemkey,TO_CHAR(actid),funcmode);
2188 resultout := 'COMPLETE:FAILURE';
2189 --
2190 IF (l_debug <> -1) THEN
2191 rlm_core_sv.dpop(C_SDEBUG);
2192 END IF;
2193 --
2194 WHEN e_MDFailed THEN
2195 --
2196 IF (l_debug <> -1) THEN
2197 rlm_core_sv.dlog(C_DEBUG,'Manage Demand failed');
2198 END IF;
2199 --
2200 ROLLBACK TO s_child_process;
2201 --
2202 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2203 g_Sch_rec.schedule_header_id,
2204 g_Grp_rec,
2205 rlm_core_sv.k_PS_ERROR);
2206
2207 -- Bug#: 2771756 - Start
2208 -- Bug: 4198330 added grouping info
2209 rlm_message_sv.removeMessages(
2210 p_header_id => v_header_id,
2211 p_message => 'RLM_RSO_CREATION_INFO',
2212 p_message_type => 'I',
2213 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2214 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2215 p_customer_item_id => g_Grp_rec.customer_item_id,
2216 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2217 -- Bug#: 2771756 - End
2218
2219 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2220 itemtype, itemkey,TO_CHAR(actid),funcmode);
2221 --
2222 resultout := 'COMPLETE:FAILURE';
2223 COMMIT;
2224 --
2225 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2226 --
2227 IF (l_debug <> -1) THEN
2228 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_MDFailed');
2229 END IF;
2230 --
2231 RAISE e_LockH;
2232 --
2233 END IF;
2234 --
2235 IF (l_debug <> -1) THEN
2236 rlm_core_sv.dpop(C_SDEBUG);
2237 END IF;
2238 --
2239 WHEN e_MFFailed THEN
2240 --
2241 IF (l_debug <> -1) THEN
2242 rlm_core_sv.dlog(C_DEBUG,'Manage Forecast failed');
2243 END IF;
2244 --
2245 ROLLBACK TO s_child_process;
2246 --
2247 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2248 g_Sch_rec.schedule_header_id,
2249 g_Grp_rec,
2250 rlm_core_sv.k_PS_ERROR);
2251
2252 -- Bug#: 2771756 - Start
2253 -- Bug: 4198330 added grouping info
2254 rlm_message_sv.removeMessages(
2255 p_header_id => v_header_id,
2256 p_message => 'RLM_RSO_CREATION_INFO',
2257 p_message_type => 'I',
2258 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2259 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2260 p_customer_item_id => g_Grp_rec.customer_item_id,
2261 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2262 -- Bug#: 2771756 - End
2263
2264 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2265 itemtype, itemkey,TO_CHAR(actid),funcmode);
2266 resultout := 'COMPLETE:FAILURE';
2267 COMMIT;
2268 --
2269 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2270 --
2271 IF (l_debug <> -1) THEN
2272 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_FDFailed');
2273 END IF;
2274 --
2275 RAISE e_LockH;
2276 --
2277 END IF;
2278 --
2279 IF (l_debug <> -1) THEN
2280 rlm_core_sv.dpop(C_SDEBUG);
2281 END IF;
2282 --
2283 WHEN e_RDFailed THEN
2284 --
2285 IF (l_debug <> -1) THEN
2286 rlm_core_sv.dlog(C_DEBUG,'Reconcile Demand failed');
2287 END IF;
2288 --
2289 ROLLBACK TO s_child_process;
2290 --
2291 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2292 g_Sch_rec.schedule_header_id,
2293 g_Grp_rec,
2294 rlm_core_sv.k_PS_ERROR);
2295 -- Bug#: 2771756 - Start
2296 -- Bug: 4198330 added grouping info
2297 rlm_message_sv.removeMessages(
2298 p_header_id => v_header_id,
2299 p_message => 'RLM_RSO_CREATION_INFO',
2300 p_message_type => 'I',
2301 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2302 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2303 p_customer_item_id => g_Grp_rec.customer_item_id,
2304 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2305 -- Bug#: 2771756 - End
2306
2307 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2308 itemtype, itemkey,TO_CHAR(actid),funcmode);
2309 --
2310 resultout := 'COMPLETE:FAILURE';
2311 COMMIT;
2312 --
2313 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2314 --
2315 IF (l_debug <> -1) THEN
2316 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_RDFailed');
2317 END IF;
2318 --
2319 RAISE e_LockH;
2320 --
2321 END IF;
2322 --
2323 IF (l_debug <> -1) THEN
2324 rlm_core_sv.dpop(C_SDEBUG);
2325 END IF;
2326 --
2327 WHEN e_LockH THEN
2328 --
2329 IF (l_debug <> -1) THEN
2330 rlm_core_sv.dlog(C_DEBUG, 'e_LockH exception in ProcessGroupdemand');
2331 rlm_core_sv.dpop(C_SDEBUG);
2332 END IF;
2333 --
2334 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2335 g_Sch_rec.schedule_header_id,
2336 g_Grp_rec,
2337 rlm_core_sv.k_PS_ERROR,
2338 'ALL');
2339 --
2340 rlm_message_sv.app_error(
2341 x_ExceptionLevel => rlm_message_sv.k_error_level,
2342 x_MessageName => 'RLM_HEADER_LOCK_NOT_OBTAINED',
2343 x_InterfaceHeaderId => v_header_id,
2344 x_InterfaceLineId => NULL,
2345 x_OrderLineId => NULL,
2346 x_Token1 => 'SCHED_REF',
2347 x_Value1 => rlm_core_sv.get_schedule_reference_num(v_header_id));
2348 --
2349 COMMIT;
2350 resultout := 'COMPLETE:FAILURE';
2351 --
2352 WHEN OTHERS THEN
2353
2354 -- Bug 2771756 : Added the rollback statement.
2355 IF (l_debug <> -1) THEN
2356 rlm_core_sv.dlog(C_DEBUG,'ProcessGroupDemand when others',
2357 SUBSTR(SQLERRM,1,1500));
2358 END IF;
2359 --
2360 ROLLBACK TO s_child_process;
2361 --
2362 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2363 g_Sch_rec.schedule_header_id,
2364 g_Grp_rec,
2365 rlm_core_sv.k_PS_ERROR);
2366
2367 -- Bug#: 2771756 - Start
2368 -- Bug: 4198330 added grouping info
2369 rlm_message_sv.removeMessages(
2370 p_header_id => v_header_id,
2371 p_message => 'RLM_RSO_CREATION_INFO',
2372 p_message_type => 'I',
2373 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2374 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2375 p_customer_item_id => g_Grp_rec.customer_item_id,
2376 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2377 -- Bug#: 2771756 - End
2378
2379 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2380 itemtype, itemkey,TO_CHAR(actid),funcmode);
2381 resultout := 'COMPLETE:FAILURE';
2382 COMMIT;
2383 --
2384 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2385 --
2386 IF (l_debug <> -1) THEN
2387 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in when others');
2388 rlm_core_sv.dpop(C_SDEBUG);
2389 END IF;
2390 --
2391 RAISE e_LockH;
2392 --
2393 END IF;
2394 --
2395 IF (l_debug <> -1) THEN
2396 rlm_core_sv.dpop(C_SDEBUG);
2397 END IF;
2398 --
2399 --RAISE;
2400
2401 END ProcessGroupDemand;
2402
2403
2404 PROCEDURE ArchiveDemand(
2405 itemtype IN VARCHAR2,
2406 itemkey IN VARCHAR2,
2407 actid IN NUMBER,
2408 funcmode IN VARCHAR2,
2409 resultout OUT NOCOPY VARCHAR2)
2410 IS
2411 --
2412 v_status NUMBER;
2413 v_schedulePS NUMBER;
2414 retcode NUMBER;
2415 errbuf VARCHAR2(2000);
2416 v_Progress VARCHAR2(3) := '020';
2417 v_header_id NUMBER;
2418 e_DPFailed EXCEPTION;
2419 e_ConfirmationSchedule EXCEPTION;
2420 --
2421
2422 BEGIN
2423 --
2424 IF (l_debug <> -1) THEN
2425 rlm_core_sv.dpush(C_SDEBUG,'ArchiveDemand');
2426 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2427 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2428 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2429 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2430 END IF;
2431 --
2432 IF (FUNCMODE = 'RUN') THEN
2433 -- Executable Statements
2434 --
2435 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2436 --
2437 IF (l_debug <> -1) THEN
2438 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2439 rlm_core_sv.dlog(C_DEBUG,'Before Archive_Demand');
2440 END IF;
2441 --
2442 RLM_TPA_SV.PostValidation;
2443 --
2444 IF RLM_VALIDATEDEMAND_SV.g_header_rec.process_status =
2445 rlm_core_sv.k_PS_ERROR THEN
2446 --
2447 resultout := 'COMPLETE:ABT';
2448 --
2449 IF (l_debug <> -1) THEN
2450 rlm_core_sv.dlog(C_DEBUG,'resultout',resultout);
2451 END IF;
2452 --
2453 ELSE
2454 --
2455 resultout := 'COMPLETE:CONT';
2456 --
2457 IF (l_debug <> -1) THEN
2458 rlm_core_sv.dlog(C_DEBUG,'resultout',resultout);
2459 END IF;
2460 --
2461 END IF;
2462 --
2463 IF (l_debug <> -1) THEN
2464 rlm_core_sv.dpop(C_SDEBUG);
2465 END IF;
2466 --
2467 RETURN;
2468 END IF;
2469
2470 IF (FUNCMODE = 'CANCEL') THEN
2471 -- Executable Statements
2472 resultout := 'COMPLETE';
2473 RETURN;
2474 END IF;
2475
2476 IF (FUNCMODE = 'RESPOND') THEN
2477 -- Executable Statements
2478 resultout := 'COMPLETE';
2479 RETURN;
2480 END IF;
2481
2482 IF (FUNCMODE = 'FORWARD') THEN
2483 -- Executable Statements
2484 resultout := 'COMPLETE';
2485 RETURN;
2486 END IF;
2487
2488 IF (FUNCMODE = 'TRANSFER') THEN
2489 -- Executable Statements
2490 resultout := 'COMPLETE';
2491 RETURN;
2492 END IF;
2493
2494 IF (FUNCMODE = 'TIMEOUT') THEN
2495 -- Executable Statements
2496 resultout := 'COMPLETE';
2497 RETURN;
2498 END IF;
2499
2500 IF (FUNCMODE = 'CANCEL') THEN
2501 -- Executable Statements
2502 resultout := 'COMPLETE';
2503 RETURN;
2504 END IF;
2505
2506 EXCEPTION
2507 WHEN NO_DATA_FOUND THEN
2508 --
2509 IF (l_debug <> -1) THEN
2510 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2511 END IF;
2512 --
2513 wf_core.context('RLM_WF_SV','ArchiveDemand',
2514 itemtype, itemkey,TO_CHAR(actid),funcmode);
2515 resultout := 'COMPLETE:ABT';
2516 --
2517 IF (l_debug <> -1) THEN
2518 rlm_core_sv.dpop(C_SDEBUG);
2519 END IF;
2520 --
2521 WHEN OTHERS THEN
2522 --
2523 IF (l_debug <> -1) THEN
2524 rlm_core_sv.dlog(C_DEBUG,'ArchiveDemand when others',
2525 SUBSTR(SQLERRM,1,1500));
2526 END IF;
2527 --
2528 rlm_dp_sv.UpdateGroupPS(v_header_id,
2529 g_Sch_rec.schedule_header_id,
2530 g_Grp_rec,
2531 rlm_core_sv.k_PS_ERROR,
2532 'ALL');
2533 --
2534 rlm_dp_sv.UpdateHeaderPS(v_header_id,
2535 g_Sch_rec.schedule_header_id);
2536 --
2537 rlm_message_sv.sql_error('rlm_wf_sv.ArchiveDemand', v_Progress);
2538 --
2539 rlm_message_sv.dump_messages(v_header_id);
2540 rlm_message_sv.initialize_messages;
2541 --
2542 COMMIT;
2543 --
2544 IF (l_debug <> -1) THEN
2545 rlm_core_sv.dpop(C_SDEBUG);
2546 END IF;
2547 --
2548 wf_core.context('RLM_WF_SV','ArchiveDemand',
2549 itemtype, itemkey,TO_CHAR(actid),funcmode);
2550 resultout := 'COMPLETE:ABT';
2551 --
2552
2553 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2554
2555 END ArchiveDemand;
2556
2557 /* Bug 2554058: Added the following procedure */
2558
2559 PROCEDURE Testschedule(
2560 itemtype IN VARCHAR2,
2561 itemkey IN VARCHAR2,
2562 actid IN NUMBER,
2563 funcmode IN VARCHAR2,
2564 resultout OUT NOCOPY VARCHAR2)
2565 IS
2566 v_header_id NUMBER;
2567 e_wftestschedule EXCEPTION;
2568
2569 BEGIN
2570 --
2571 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2572
2573 IF RLM_DP_SV.edi_test_indicator = 'T' then
2574 --
2575 rlm_core_sv.dlog(C_DEBUG,'Test schedule found');
2576 raise e_wftestschedule;
2577 --
2578 ELSE
2579 --
2580 resultout := 'F';
2581 --
2582 END IF;
2583 --
2584 EXCEPTION
2585 WHEN e_wftestschedule THEN
2586 --
2587 rlm_message_sv.app_error(
2588 x_ExceptionLevel => rlm_message_sv.k_warn_level,
2589 x_MessageName => 'RLM_TEST_SCHEDULE_DETECTED',
2590 x_InterfaceHeaderId => v_header_id,
2591 x_InterfaceLineId => NULL,
2592 x_OrderLineId => NULL,
2593 x_Token1 => 'SCHED_REF',
2594 x_Value1 =>rlm_core_sv.get_schedule_reference_num(v_header_id));
2595
2596 resultout := 'T';
2597 COMMIT;
2598 --
2599 END Testschedule;
2600
2601
2602 PROCEDURE CallProcessGroup(
2603 itemtype IN VARCHAR2,
2604 itemkey IN VARCHAR2,
2605 actid IN NUMBER,
2606 funcmode IN VARCHAR2,
2607 resultout OUT NOCOPY VARCHAR2)
2608 IS
2609 --
2610 v_header_id NUMBER;
2611 v_tmp_status NUMBER;
2612 v_count NUMBER;
2613 retcode NUMBER;
2614 errbuf VARCHAR2(2000);
2615 v_num_child NUMBER;
2616 v_child_req_id rlm_dp_sv.g_request_tbl;
2617
2618 -- 4299804: Added min_start_date_time and ship_to_customer_id to the
2619 -- select stmt.
2620
2621 CURSOR c_group_cur (v_hdr_id IN VARCHAR2) IS
2622 SELECT rih.customer_id,
2623 ril.ship_from_org_id,
2624 ril.ship_to_address_id,
2625 ril.ship_to_site_use_id,
2626 ril.ship_to_org_id,
2627 ril.customer_item_id,
2628 ril.inventory_item_id,
2629 ril.intrmd_ship_to_id, --Bugfix 5911991
2630 ril.intmed_ship_to_org_id, --Bugfix 5911991
2631 ril.industry_attribute15,
2632 ril.order_header_id,
2633 ril.blanket_number,
2634 min(ril.start_date_time),
2635 ril.ship_to_customer_id
2636 FROM rlm_interface_headers rih,
2637 rlm_interface_lines_all ril
2638 WHERE ril.header_id = v_hdr_id
2639 AND ril.org_id = rih.org_id
2640 AND ril.header_id = rih.header_id
2641 AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
2642 rlm_core_sv.k_PS_PARTIAL_PROCESSED)
2643 GROUP BY rih.customer_id,
2644 ril.ship_from_org_id,
2645 ril.ship_to_address_id,
2646 ril.ship_to_site_use_id,
2647 ril.ship_to_org_id,
2648 ril.customer_item_id,
2649 ril.inventory_item_id,
2650 ril.industry_attribute15,
2651 ril.intrmd_ship_to_id, --Bugfix 5911991
2652 ril.intmed_ship_to_org_id, --Bugfix 5911991
2653 ril.order_header_id,
2654 ril.blanket_number,
2655 ril.ship_to_customer_id
2656 ORDER BY min(ril.start_date_time),
2657 ril.ship_to_address_id,
2658 ril.customer_item_id;
2659 --
2660
2661 BEGIN
2662 --
2663 IF (l_debug <> -1) THEN
2664 rlm_core_sv.dpush(C_SDEBUG,'CallProcessGroup');
2665 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2666 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2667 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2668 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2669 END IF;
2670 --
2671 IF (FUNCMODE = 'RUN') THEN
2672 -- Executable Statements
2673 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2674 --
2675 IF (l_debug <> -1) THEN
2676 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2677 END IF;
2678 --
2679 SELECT process_status
2680 INTO v_tmp_status
2681 FROM rlm_interface_headers
2682 WHERE header_id = v_Header_Id;
2683 --
2684 IF (l_debug <> -1) THEN
2685 rlm_core_sv.dlog(C_DEBUG,'process status =', v_tmp_status);
2686 END IF;
2687 --
2688
2689 --
2690 IF v_tmp_status <> rlm_core_sv.k_PROC_ERROR THEN
2691 --
2692 v_num_child := g_num_child;
2693 --
2694 IF v_num_child > 1 THEN /* Parallel DSP */
2695 --
2696 /* submit concurrent program requests*/
2697
2698 rlm_dp_sv.CreateChildGroups (v_header_id,
2699 v_num_child);
2700 --
2701 IF NOT RLM_DP_SV.LockHeader(v_header_id, g_Sch_rec) THEN
2702 --
2703 IF (l_debug <> -1) THEN
2704 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header after CreateChildGroups');
2705 END IF;
2706 --
2707 RAISE e_LockH;
2708 --
2709 END IF;
2710 --
2711 IF (v_num_child > 1) THEN /*Parallelize if more than 1 group found */
2712
2713 --
2714 rlm_dp_sv.SubmitChildRequests(v_header_id,
2715 v_num_child,
2716 v_child_req_id);
2717 --
2718 rlm_dp_sv.ProcessChildRequests(v_header_id,
2719 v_child_req_id);
2720
2721
2722
2723 v_child_req_id.delete;
2724 --
2725 ELSE
2726 --
2727 rlm_dp_sv.ProcessGroups (g_Sch_rec,
2728 v_header_id,
2729 1, rlm_dp_sv.k_PARALLEL_DSP);
2730 --
2731 END IF;
2732 --
2733 ELSE /*sequencial processing*/
2734 --
2735 v_count := 1;
2736 --
2737 OPEN c_group_cur(v_header_id);
2738 --
2739 LOOP
2740 --
2741 BEGIN
2742 --
2743 -- 4299804: Added min_start_date_time and
2744 -- ship_to_customer_id to the fetch stmt.
2745
2746 FETCH c_group_cur INTO
2747 g_Grp_rec.customer_id,
2748 g_Grp_rec.ship_from_org_id,
2749 g_Grp_rec.ship_to_address_id,
2750 g_Grp_rec.ship_to_site_use_id,
2751 g_Grp_rec.ship_to_org_id,
2752 g_Grp_rec.customer_item_id,
2753 g_Grp_rec.inventory_item_id,
2754 g_Grp_rec.industry_attribute15,
2755 g_Grp_rec.intrmd_ship_to_id, --Bugfix 5911991
2756 g_Grp_rec.intmed_ship_to_org_id, --Bugfix 5911991
2757 g_Grp_rec.order_header_id,
2758 g_Grp_rec.blanket_number,
2759 g_Grp_rec.min_start_date_time,
2760 g_Grp_rec.ship_to_customer_id;
2761
2762 --
2763 EXIT WHEN c_group_cur%NOTFOUND;
2764 --
2765 -- Setting the global vars
2766 IF (l_debug <> -1) THEN
2767 rlm_core_sv.dlog(C_DEBUG,'Create loop',v_count);
2768 END IF;
2769 --
2770 IF v_count > 1 THEN
2771 --
2772 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2773 --
2774 IF (l_debug <> -1) THEN
2775 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header');
2776 END IF;
2777 --
2778 resultout := 'COMPLETE:FAILURE';
2779 RAISE e_LockH;
2780 --
2781 END IF;
2782 END IF;
2783 --
2784 rlm_wf_sv.CreateDSPLoop(errbuf,
2785 retcode,
2786 v_header_id,
2787 v_count);
2788 --
2789 IF (l_debug <> -1) THEN
2790 rlm_core_sv.dlog(C_DEBUG,'start loop',v_count);
2791 rlm_core_sv.dlog(C_DEBUG, '***** Processing new group *****');
2792 rlm_core_sv.dlog(C_DEBUG, 'Blanket Number', g_Grp_rec.blanket_number);
2793 END IF;
2794 --
2795 rlm_wf_sv.StartDSPLoop(errbuf,
2796 retcode,
2797 v_header_id,
2798 v_count,
2799 g_Sch_rec,
2800 g_Grp_rec);
2801 --
2802 v_count:= v_count+1;
2803 --
2804 END;
2805 --
2806 END LOOP;
2807 --
2808 CLOSE c_group_cur;
2809 --
2810 END IF;
2811 --
2812 resultout := 'COMPLETE:SUCCESS';
2813 --
2814 ELSE
2815 --
2816 resultout := 'COMPLETE:FAILURE';
2817 --
2818 END IF;
2819 --
2820 IF (l_debug <> -1) THEN
2821 rlm_core_sv.dpop(C_SDEBUG);
2822 END IF;
2823 RETURN;
2824 END IF;
2825
2826 IF (FUNCMODE = 'CANCEL') THEN
2827 -- Executable Statements
2828 resultout := 'COMPLETE';
2829 RETURN;
2830 END IF;
2831
2832 IF (FUNCMODE = 'RESPOND') THEN
2833 -- Executable Statements
2834 resultout := 'COMPLETE';
2835 RETURN;
2836 END IF;
2837
2838 IF (FUNCMODE = 'FORWARD') THEN
2839 -- Executable Statements
2840 resultout := 'COMPLETE';
2841 RETURN;
2842 END IF;
2843
2844 IF (FUNCMODE = 'TRANSFER') THEN
2845 -- Executable Statements
2846 resultout := 'COMPLETE';
2847 RETURN;
2848 END IF;
2849
2850 IF (FUNCMODE = 'TIMEOUT') THEN
2851 -- Executable Statements
2852 resultout := 'COMPLETE';
2853 RETURN;
2854 END IF;
2855
2856 IF (FUNCMODE = 'CANCEL') THEN
2857 -- Executable Statements
2858 resultout := 'COMPLETE';
2859 RETURN;
2860 END IF;
2861
2862 EXCEPTION
2863 WHEN NO_DATA_FOUND THEN
2864 --
2865 IF (l_debug <> -1) THEN
2866 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2867 END IF;
2868 --
2869 wf_core.context('RLM_WF_SV','CallProcessGroup',
2870 itemtype, itemkey,TO_CHAR(actid),funcmode);
2871 resultout := 'COMPLETE:FAILURE';
2872 --
2873 IF (l_debug <> -1) THEN
2874 rlm_core_sv.dpop(C_SDEBUG);
2875 END IF;
2876 --
2877 WHEN e_LockH THEN
2878 --
2879 IF (l_debug <> -1) THEN
2880 rlm_core_sv.dlog(C_DEBUG, 'e_LockH exception in CallProcessGroup');
2881 rlm_core_sv.dpop(C_SDEBUG);
2882 END IF;
2883 --
2884 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2885 g_Sch_rec.schedule_header_id,
2886 g_Grp_rec,
2887 rlm_core_sv.k_PS_ERROR,
2888 'ALL');
2889 --
2890 rlm_message_sv.app_error(
2891 x_ExceptionLevel => rlm_message_sv.k_error_level,
2892 x_MessageName => 'RLM_HEADER_LOCK_NOT_OBTAINED',
2893 x_InterfaceHeaderId => v_header_id,
2894 x_InterfaceLineId => NULL,
2895 x_OrderLineId => NULL,
2896 x_Token1 => 'SCHED_REF',
2897 x_Value1 => rlm_core_sv.get_schedule_reference_num(v_header_id));
2898 --
2899 COMMIT;
2900 resultout := 'COMPLETE:FAILURE';
2901 --
2902 WHEN OTHERS THEN
2903 wf_core.context('RLM_WF_SV','CallProcessGroup',
2904 itemtype, itemkey,TO_CHAR(actid),funcmode);
2905 resultout := 'COMPLETE:FAILURE';
2906 --
2907 IF (l_debug <> -1) THEN
2908 rlm_core_sv.dpop(C_SDEBUG);
2909 END IF;
2910 --
2911 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2912
2913 END CallProcessGroup;
2914
2915
2916
2917 PROCEDURE PostValidate(
2918 itemtype IN VARCHAR2,
2919 itemkey IN VARCHAR2,
2920 actid IN NUMBER,
2921 funcmode IN VARCHAR2,
2922 resultout OUT NOCOPY VARCHAR2)
2923 IS
2924 --
2925 v_header_id NUMBER;
2926 retcode NUMBER;
2927 errbuf VARCHAR2(2000);
2928 v_Progress VARCHAR2(3) := '030';
2929 e_ConfirmationSchedule EXCEPTION;
2930 v_replace_status BOOLEAN DEFAULT FALSE;
2931 e_ReplaceSchedule EXCEPTION;
2932 --
2933
2934 BEGIN
2935 --
2936 IF (l_debug <> -1) THEN
2937 rlm_core_sv.dpush(C_SDEBUG,'PostValidate');
2938 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2939 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2940 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2941 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2942 END IF;
2943 --
2944 IF (FUNCMODE = 'RUN') THEN
2945 -- Executable Statements
2946 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2947 --
2948 IF (l_debug <> -1) THEN
2949 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2950 END IF;
2951 --
2952 -- Lock the headers and Populate g_Sch_rec
2953 IF NOT rlm_dp_sv.LockHeader(v_header_Id, g_Sch_rec) THEN
2954 --
2955 IF (l_debug <> -1) THEN
2956 rlm_core_sv.dlog(C_DEBUG,'header not locked');
2957 END IF;
2958 --
2959 raise e_LockH;
2960 --
2961 END IF;
2962 --
2963 IF g_Sch_rec.schedule_purpose = rlm_dp_sv.k_CONFIRMATION THEN
2964 --
2965 IF (l_debug <> -1) THEN
2966 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
2967 END IF;
2968 --
2969 raise e_ConfirmationSchedule;
2970 --
2971 END IF;
2972 --
2973 --
2974 -- Call Sweeper Program here
2975 -- (Enhancement bug# 1062039)
2976 --
2977
2978 SELECT hsecs INTO l_comp_start_time FROM v$timer;
2979
2980 RLM_REPLACE_SV.CompareReplaceSched(g_Sch_rec,
2981 RLM_DP_SV.g_warn_replace_schedule,
2982 v_replace_status);
2983
2984 --
2985 SELECT hsecs INTO l_comp_end_time FROM v$timer;
2986
2987
2988 IF v_replace_status = FALSE THEN
2989 --
2990 RAISE e_ReplaceSchedule;
2991 --
2992 END IF;
2993 --
2994 resultout := 'COMPLETE:SUCCESS';
2995 --
2996 IF (l_debug <> -1) THEN
2997 rlm_core_sv.dpop(C_SDEBUG);
2998 END IF;
2999 --
3000 RETURN;
3001 END IF;
3002
3003 IF (FUNCMODE = 'CANCEL') THEN
3004 -- Executable Statements
3005 resultout := 'COMPLETE';
3006 RETURN;
3007 END IF;
3008
3009 IF (FUNCMODE = 'RESPOND') THEN
3010 -- Executable Statements
3011 resultout := 'COMPLETE';
3012 RETURN;
3013 END IF;
3014
3015 IF (FUNCMODE = 'FORWARD') THEN
3016 -- Executable Statements
3017 resultout := 'COMPLETE';
3018 RETURN;
3019 END IF;
3020
3021 IF (FUNCMODE = 'TRANSFER') THEN
3022 -- Executable Statements
3023 resultout := 'COMPLETE';
3024 RETURN;
3025 END IF;
3026
3027 IF (FUNCMODE = 'TIMEOUT') THEN
3028 -- Executable Statements
3029 resultout := 'COMPLETE';
3030 RETURN;
3031 END IF;
3032
3033 IF (FUNCMODE = 'CANCEL') THEN
3034 -- Executable Statements
3035 resultout := 'COMPLETE';
3036 RETURN;
3037 END IF;
3038
3039 EXCEPTION
3040 WHEN NO_DATA_FOUND THEN
3041 --
3042 IF (l_debug <> -1) THEN
3043 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
3044 END IF;
3045 --
3046 wf_core.context('RLM_WF_SV','PostValidate',
3047 itemtype, itemkey,TO_CHAR(actid),funcmode);
3048 resultout := 'COMPLETE:FAILURE';
3049 --
3050 IF (l_debug <> -1) THEN
3051 rlm_core_sv.dpop(C_SDEBUG);
3052 END IF;
3053 --
3054 WHEN e_LockH THEN
3055 --
3056 IF (l_debug <> -1) THEN
3057 rlm_core_sv.dlog(C_DEBUG,'PostValidate failed Lock header');
3058 END IF;
3059 --
3060 rlm_dp_sv.UpdateGroupPS(v_header_id,
3061 g_Sch_rec.schedule_header_id,
3062 g_Grp_rec,
3063 rlm_core_sv.k_PS_ERROR,
3064 'ALL');
3065 --
3066 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3067 g_Sch_rec.schedule_header_id);
3068 --
3069 rlm_message_sv.dump_messages(v_header_id);
3070 rlm_message_sv.initialize_messages;
3071 --
3072
3073 wf_core.context('RLM_WF_SV','PostValidate',
3074 itemtype, itemkey,TO_CHAR(actid),funcmode);
3075 resultout := 'COMPLETE:FAILURE';
3076 --
3077 IF (l_debug <> -1) THEN
3078 rlm_core_sv.dpop(C_SDEBUG);
3079 END IF;
3080 --
3081 WHEN e_ConfirmationSchedule THEN
3082 --
3083 IF (l_debug <> -1) THEN
3084 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
3085 END IF;
3086 --
3087 rlm_message_sv.app_error(
3088 x_ExceptionLevel => rlm_message_sv.k_warn_level,
3089 x_MessageName => 'RLM_CONF_SCH_RCD',
3090 x_InterfaceHeaderId => g_Sch_rec.header_id,
3091 x_InterfaceLineId => null,
3092 x_ScheduleHeaderId => g_Sch_rec.schedule_header_id,
3093 x_ScheduleLineId => NULL,
3094 x_OrderHeaderId => g_Grp_rec.setup_terms_rec.header_id,
3095 x_OrderLineId => NULL,
3096 x_Token1 => 'SCHED_REF',
3097 x_Value1 => g_Sch_rec.schedule_reference_num);
3098 --
3099 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
3100 g_Sch_rec.Schedule_header_id,
3101 g_Grp_rec,
3102 rlm_core_sv.K_PS_PROCESSED,
3103 'ALL');
3104 --
3105 rlm_dp_sv.UpdateHeaderPS(g_Sch_rec.header_id,
3106 g_Sch_rec.Schedule_header_id);
3107 --
3108 rlm_message_sv.dump_messages(v_header_id);
3109 rlm_message_sv.initialize_messages;
3110 --
3111 COMMIT;
3112 -- Bug#: 3053299 -- Setting the output
3113 resultout := 'COMPLETE:SUCCESS';
3114 --
3115 IF (l_debug <> -1) THEN
3116 rlm_core_sv.dpop(C_SDEBUG);
3117 END IF;
3118
3119 WHEN e_ReplaceSchedule THEN
3120 --
3121 wf_core.context('RLM_WF_SV','PostValidate',
3122 itemtype, itemkey,TO_CHAR(actid),funcmode);
3123 resultout := 'COMPLETE:FAILURE';
3124 --
3125 rlm_dp_sv.UpdateGroupPS(v_header_id,
3126 g_Sch_rec.schedule_header_id,
3127 g_Grp_rec,
3128 rlm_core_sv.k_PS_ERROR,
3129 'ALL');
3130 --
3131 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3132 g_Sch_rec.schedule_header_id);
3133 --
3134 -- Bug 2930695: Frontport bug 2912996
3135 -- rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
3136 --
3137 rlm_message_sv.dump_messages(v_header_id);
3138 rlm_message_sv.initialize_messages;
3139 --
3140 COMMIT;
3141 --
3142 IF (l_debug <> -1) THEN
3143 rlm_core_sv.dpop(C_SDEBUG);
3144 END IF;
3145
3146 WHEN OTHERS THEN
3147 wf_core.context('RLM_WF_SV','PostValidate',
3148 itemtype, itemkey,TO_CHAR(actid),funcmode);
3149 resultout := 'COMPLETE:FAILURE';
3150 --
3151 rlm_dp_sv.UpdateGroupPS(v_header_id,
3152 g_Sch_rec.schedule_header_id,
3153 g_Grp_rec,
3154 rlm_core_sv.k_PS_ERROR,
3155 'ALL');
3156 --
3157 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3158 g_Sch_rec.schedule_header_id);
3159 --
3160 rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
3161 --
3162 rlm_message_sv.dump_messages(v_header_id);
3163 rlm_message_sv.initialize_messages;
3164 --
3165 COMMIT;
3166 --
3167 IF (l_debug <> -1) THEN
3168 rlm_core_sv.dpop(C_SDEBUG);
3169 END IF;
3170 --RAISE ;
3171
3172 END PostValidate;
3173
3174
3175 PROCEDURE CHeckStatus(
3176 itemtype IN VARCHAR2,
3177 itemkey IN VARCHAR2,
3178 actid IN NUMBER,
3179 funcmode IN VARCHAR2,
3180 resultout OUT NOCOPY VARCHAR2)
3181 IS
3182 --
3183 v_header_id NUMBER;
3184 v_request_id NUMBER;
3185 v_schedule_num VARCHAR2(50);
3186 x_errors NUMBER := -1;
3187 x_real_errors NUMBER := -1;
3188 v_status NUMBER;
3189 e_DPFailed EXCEPTION;
3190 BEGIN
3191 --
3192 IF (l_debug <> -1) THEN
3193 rlm_core_sv.dpush(C_SDEBUG,'CHeckStatus');
3194 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
3195 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
3196 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
3197 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
3198 END IF;
3199 --
3200 IF (FUNCMODE = 'RUN') THEN
3201 -- Executable Statements
3202 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
3203 --
3204 IF (l_debug <> -1) THEN
3205 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
3206 END IF;
3207 --
3208 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
3209 --
3210 IF (l_debug <> -1) THEN
3211 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
3212 END IF;
3213 --
3214 v_schedule_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
3215 --
3216 IF (l_debug <> -1) THEN
3217 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_schedule_num);
3218 END IF;
3219 --
3220 rlm_message_sv.dump_messages(v_header_id);
3221 rlm_message_sv.initialize_messages;
3222
3223 -- Check for Errors
3224
3225 wf_engine.SetItemAttrNumber( itemtype,
3226 itemkey,
3227 'ERRORS_EXIST',
3228 g_PROC_ERROR); ---Errors Exist
3229
3230 resultout := 'COMPLETE:ERR';
3231 --
3232 IF (l_debug <> -1) THEN
3233 rlm_core_sv.dpop(C_SDEBUG);
3234 END IF;
3235 --
3236 RETURN;
3237
3238 END IF;
3239
3240 IF (FUNCMODE = 'CANCEL') THEN
3241 -- Executable Statements
3242 resultout := 'COMPLETE';
3243 RETURN;
3244 END IF;
3245
3246 IF (FUNCMODE = 'RESPOND') THEN
3247 -- Executable Statements
3248 resultout := 'COMPLETE';
3249 RETURN;
3250 END IF;
3251
3252 IF (FUNCMODE = 'FORWARD') THEN
3253 -- Executable Statements
3254 resultout := 'COMPLETE';
3255 RETURN;
3256 END IF;
3257
3258 IF (FUNCMODE = 'TRANSFER') THEN
3259 -- Executable Statements
3260 resultout := 'COMPLETE';
3261 RETURN;
3262 END IF;
3263
3264 IF (FUNCMODE = 'TIMEOUT') THEN
3265 -- Executable Statements
3266 resultout := 'COMPLETE';
3267 RETURN;
3268 END IF;
3269
3270 IF (FUNCMODE = 'CANCEL') THEN
3271 -- Executable Statements
3272 resultout := 'COMPLETE';
3273 RETURN;
3274 END IF;
3275
3276 EXCEPTION
3277 WHEN NO_DATA_FOUND THEN
3278 --
3279 IF (l_debug <> -1) THEN
3280 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
3281 END IF;
3282 --
3283 wf_core.context('RLM_WF_SV','CHeckStatus',
3284 itemtype, itemkey,TO_CHAR(actid),funcmode);
3285 resultout := 'COMPLETE:N';
3286 --
3287 IF (l_debug <> -1) THEN
3288 rlm_core_sv.dpop(C_SDEBUG);
3289 END IF;
3290 --
3291 WHEN OTHERS THEN
3292 wf_core.context('RLM_WF_SV','CHeckStatus',
3293 itemtype, itemkey,TO_CHAR(actid),funcmode);
3294 resultout := 'COMPLETE:N';
3295 --
3296 IF (l_debug <> -1) THEN
3297 rlm_core_sv.dpop(C_SDEBUG);
3298 END IF;
3299 --
3300 RAISE;
3301
3302 END CHeckStatus;
3303
3304 -- Bug#: 3053299 - Start of the functions
3305
3306 /*=============================================================================
3307
3308 FUNCTION NAME: Notification_ToUser
3309
3310 DESCRIPTION: This function returns the To User to whom the notifications
3311 are to be send. The To User is set as an attribute in the Work
3312 Flow. The To User is retrieved from the FND_USER table.
3313
3314 PARAMETERS: user_id IN NUMBER
3315
3316 RETURN: VARCHAR2
3317
3318 ============================================================================*/
3319
3320 FUNCTION Notification_ToUser(user_id IN NUMBER) RETURN VARCHAR2 is
3321 --
3322 CURSOR c_user(v_user_id IN NUMBER) is
3323 SELECT user_name
3324 FROM fnd_user
3325 WHERE user_id = v_user_id;
3326 --
3327 v_ToUserName fnd_user.user_name%TYPE;
3328 e_UserNotFound EXCEPTION;
3329 --
3330 BEGIN
3331 --
3332 IF (l_debug <> -1) THEN
3333 rlm_core_sv.dpush(C_SDEBUG, 'Notification_ToUser');
3334 rlm_core_sv.dlog(C_DEBUG, 'User Id :', user_id);
3335 END IF;
3336 --
3337 OPEN c_user(user_id);
3338 FETCH c_user INTO v_ToUserName;
3339 --
3340 IF (c_user%NOTFOUND) THEN
3341 --
3342 IF (l_debug <> -1) THEN
3343 rlm_core_sv.dlog(C_DEBUG,'Notification_ToUser :: No Data Found');
3344 END IF;
3345 --
3346 RAISE e_UserNotFound;
3347 --
3348 END IF;
3349 --
3350 CLOSE c_user;
3351 --
3352 IF (l_debug <> -1) THEN
3353 rlm_core_sv.dlog(C_DEBUG, 'To User', v_ToUserName);
3354 rlm_core_sv.dpop(C_SDEBUG);
3355 END IF;
3356 --
3357 RETURN v_ToUserName;
3358 --
3359 EXCEPTION
3360 --
3361 WHEN e_UserNotFound THEN
3362 --
3363 rlm_message_sv.sql_error('RLM_WF_SV.Notification_ToUser',user_id);
3364 --
3365 IF (l_debug <> -1) THEN
3366 rlm_core_sv.dlog(C_DEBUG, 'Exception : e_UserNotFound');
3367 rlm_core_sv.dpop(C_SDEBUG);
3368 END IF;
3369 --
3370 RAISE;
3371 --
3372 END Notification_ToUser;
3373
3374 /*=============================================================================
3375
3376 FUNCTION NAME: Notification_FromUser
3377
3378 DESCRIPTION: This function returns the From User from whom the notifications
3379 are sent. The From User is set as an attribute in the Work
3380 Flow item. We call an FND API in order to obtain the
3381 internal responsibility-based role name for a given
3382 responsibility.
3383 Modifications made as a part of bug 3764527.
3384
3385 PARAMETERS: responsibility_id IN NUMBER
3386 resp_appl_id IN NUMBER
3387
3388 RETURN: VARCHAR2
3389
3390 ============================================================================*/
3391
3392 FUNCTION Notification_FromUser(responsibility_id NUMBER,
3393 resp_appl_id NUMBER) RETURN VARCHAR2 is
3394 --
3395 /*
3396 * Bug 3680168 : Do not need this cursor anymore
3397 * We now call an FND API to get the role name
3398 *
3399 CURSOR c_user(v_resp_id IN NUMBER, v_appl_id IN NUMBER) is
3400 SELECT display_name
3401 FROM wf_roles
3402 WHERE name =
3403 (
3404 SELECT 'FND_RESP' || r.application_id || ':' || r.responsibility_id
3405 FROM fnd_responsibility_vl r
3406 WHERE r.responsibility_id = v_resp_id
3407 AND r.application_id = v_appl_id);
3408 */
3409 --
3410 v_FromUserName wf_roles.name%type;
3411 --
3412 BEGIN
3413 --
3414 IF (l_debug <> -1) THEN
3415 rlm_core_sv.dpush(C_SDEBUG, 'Notification_FromUser');
3416 rlm_core_sv.dlog(C_DEBUG, 'Responsibility Id :', responsibility_id);
3417 rlm_core_sv.dlog(C_DEBUG, 'Application Id :', resp_appl_id);
3418 END IF;
3419 --
3420 v_FromUserName := FND_USER_RESP_GROUPS_API. upgrade_resp_role
3421 (respid => responsibility_id,
3422 appid => resp_appl_id);
3423 --
3424 IF (l_debug <> -1) THEN
3425 rlm_core_sv.dlog(C_DEBUG, 'Internal Role Name', v_FromUserName);
3426 rlm_core_sv.dpop(C_SDEBUG);
3427 END IF;
3428 --
3429 RETURN v_FromUserName;
3430 --
3431 EXCEPTION
3432 --
3433 WHEN OTHERS THEN
3434 --
3435 rlm_message_sv.sql_error('RLM_WF_SV.Notification_FromUser',
3436 SUBSTRB(SQLERRM, 1, 200));
3437 --
3438 IF (l_debug <> -1) THEN
3439 rlm_core_sv.dlog(C_DEBUG, 'When Others - ' || SUBSTRB(SQLERRM, 1, 200));
3440 rlm_core_sv.dpop(C_SDEBUG);
3441 END IF;
3442 --
3443 RAISE;
3444 --
3445 END Notification_FromUser;
3446
3447 -- Bug#: 3053299 - End of the functions
3448
3449 -- Bug#: 3291401 - Start
3450 /*=============================================================================
3451
3452 Procedure NAME: GetScheduleStatus
3453
3454 DESCRIPTION: This procedure checks for the schedule status.
3455
3456 PARAMETERS: itemtype IN VARCHAR2,
3457 itemkey IN VARCHAR2,
3458 actid IN NUMBER,
3459 funcmode IN VARCHAR2,
3460 resultout OUT NOCOPY VARCHAR2`
3461
3462 ============================================================================*/
3463
3464
3465 PROCEDURE GetScheduleStatus(
3466 itemtype IN VARCHAR2,
3467 itemkey IN VARCHAR2,
3468 actid IN NUMBER,
3469 funcmode IN VARCHAR2,
3470 resultout OUT NOCOPY VARCHAR2)
3471 IS
3472
3473 CURSOR c_process_status_schedule(p_header_id IN NUMBER) is
3474 SELECT process_status
3475 FROM rlm_schedule_headers
3476 WHERE header_id = p_header_id;
3477
3478 CURSOR c_process_status_interface(p_header_id IN NUMBER) is
3479 SELECT process_status
3480 FROM rlm_interface_headers
3481 WHERE header_id = p_header_id;
3482
3483 --
3484 v_header_id NUMBER;
3485 v_process_status NUMBER;
3486 BEGIN
3487 --
3488 IF (l_debug <> -1) THEN
3489 rlm_core_sv.dpush(C_SDEBUG,'GetScheduleStatus');
3490 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
3491 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
3492 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
3493 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
3494 END IF;
3495 --
3496 IF (FUNCMODE = 'RUN') THEN
3497 -- Executable Statements
3498 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
3499 --
3500 IF (l_debug <> -1) THEN
3501 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
3502 END IF;
3503 --
3504
3505 OPEN c_process_status_schedule(g_Sch_rec.schedule_header_id); --Bug# 3567538
3506
3507 FETCH c_process_status_schedule INTO v_process_status;
3508
3509 IF (c_process_status_schedule%NOTFOUND) THEN
3510
3511 OPEN c_process_status_interface(v_header_id);
3512
3513 FETCH c_process_status_interface INTO v_process_status;
3514
3515 IF (c_process_status_interface%NOTFOUND) THEN
3516 resultout := 'COMPLETE:ERROR';
3517 END IF;
3518
3519 IF (v_process_status IS NOT NULL AND v_process_status = RLM_CORE_SV.k_PS_PARTIAL_PROCESSED) THEN
3520 resultout := 'COMPLETE:PARTIAL_PROCESS';
3521 ELSE
3522 resultout := 'COMPLETE:ERROR';
3523 END IF ;
3524
3525 CLOSE c_process_status_interface;
3526 END IF;
3527
3528 IF (v_process_status IS NOT NULL AND v_process_status = RLM_CORE_SV.k_PS_PARTIAL_PROCESSED) THEN
3529 resultout := 'COMPLETE:PARTIAL_PROCESS';
3530 ELSE
3531 resultout := 'COMPLETE:ERROR';
3532 END IF;
3533
3534
3535 CLOSE c_process_status_schedule;
3536
3537 --
3538 IF (l_debug <> -1) THEN
3539 rlm_core_sv.dpop(C_SDEBUG);
3540 END IF;
3541 --
3542 RETURN;
3543
3544 END IF;
3545
3546 IF (FUNCMODE = 'CANCEL') THEN
3547 -- Executable Statements
3548 resultout := 'COMPLETE:ERROR';
3549 RETURN;
3550 END IF;
3551
3552 IF (FUNCMODE = 'RESPOND') THEN
3553 -- Executable Statements
3554 resultout := 'COMPLETE:ERROR';
3555 RETURN;
3556 END IF;
3557
3558 IF (FUNCMODE = 'FORWARD') THEN
3559 -- Executable Statements
3560 resultout := 'COMPLETE:ERROR';
3561 RETURN;
3562 END IF;
3563
3564 IF (FUNCMODE = 'TRANSFER') THEN
3565 -- Executable Statements
3566 resultout := 'COMPLETE:ERROR';
3567 RETURN;
3568 END IF;
3569
3570 IF (FUNCMODE = 'TIMEOUT') THEN
3571 -- Executable Statements
3572 resultout := 'COMPLETE:ERROR';
3573 RETURN;
3574 END IF;
3575
3576 IF (FUNCMODE = 'CANCEL') THEN
3577 -- Executable Statements
3578 resultout := 'COMPLETE:ERROR';
3579 RETURN;
3580 END IF;
3581
3582 EXCEPTION
3583 WHEN OTHERS THEN
3584 wf_core.context('RLM_WF_SV','GetScheduleStatus',
3585 itemtype, itemkey,TO_CHAR(actid),funcmode);
3586 resultout := 'COMPLETE:ERROR';
3587 --
3588 IF (l_debug <> -1) THEN
3589 rlm_core_sv.dpop(C_SDEBUG);
3590 END IF;
3591 --
3592 RAISE;
3593
3594 END GetScheduleStatus;
3595 -- Bug#: 3291401 - End
3596
3597 END RLM_WF_SV;