[Home] [Help]
PACKAGE BODY: APPS.RLM_WF_SV
Source
1 PACKAGE BODY RLM_WF_SV as
2 /*$Header: RLMDPWFB.pls 120.8 2011/12/20 07:31:17 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 /* Bugfix 12863728 */
894 v_rlm_gps_start_time NUMBER;
895 v_rlm_gps_end_time NUMBER;
896 v_rlm_gps_total NUMBER:=0;
897 v_rlm_gps_msg_text VARCHAR2(32000);
898 BEGIN
899 --
900 IF (l_debug <> -1) THEN
901 rlm_core_sv.dpush(C_SDEBUG,'ReconcileDemand');
902 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
903 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
904 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
905 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
906 END IF;
907 --
908 IF (FUNCMODE = 'RUN') THEN
909 -- Executable Statements
910 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
911 --
912 IF (l_debug <> -1) THEN
913 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
914 END IF;
915 --
916 SELECT hsecs INTO v_rd_start_time from v$timer;
917 --
918 rlm_rd_sv.RecDemand(v_header_id,
919 g_Sch_rec,
920 g_Grp_rec,
921 v_status);
922 --
923 IF (l_debug <> -1) THEN
924 rlm_core_sv.dlog(C_DEBUG,' After ReconcileDemand v_status: ', v_status);
925 END IF;
926 --
927 SELECT hsecs INTO v_rd_end_time FROM v$timer;
928 v_rd_total :=v_rd_total+(v_rd_end_time-v_rd_start_time)/100;
929 --
930 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
931 --
932 RAISE e_DPFailed;
933 --
934 END IF;
935 --
936 SELECT hsecs INTO v_rlm_gps_start_time from v$timer; --Bugfix 12863728
937 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
938 g_Sch_rec.schedule_header_id,
939 g_Grp_rec,
940 rlm_core_sv.k_PS_PROCESSED);
941 --
942 COMMIT;
943 --
944 SELECT hsecs INTO v_rlm_gps_end_time FROM v$timer; --Bugfix 12863728
945 v_rlm_gps_total :=v_rlm_gps_total+(v_rlm_gps_end_time-v_rlm_gps_start_time )/100; --Bugfix 12863728
946 --
947 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
948 --
949 IF (l_debug <> -1) THEN
950 rlm_core_sv.dlog(C_DEBUG,'Unable to lock header after processing grp');
951 END IF;
952 --
953 RAISE e_LockH;
954 --
955 END IF;
956 --
957 resultout := 'COMPLETE:SUCCESS';
958 --
959 IF (l_debug <> -1) THEN
960 rlm_core_sv.dpop(C_SDEBUG);
961 END IF;
962 --
963 v_rd_msg_text:='Total Time spent in RecDemand call - '|| v_rd_total ;
964 fnd_file.put_line(fnd_file.log, v_rd_msg_text);
965 v_rlm_gps_msg_text:='Total Time spent in Update GroupPS call - '|| v_rlm_gps_total ; --Bugfix 12863728
966 fnd_file.put_line(fnd_file.log, v_rlm_gps_msg_text); --Bugfix 12863728
967 --
968 RETURN;
969 --
970 END IF;
971
972 IF (FUNCMODE = 'CANCEL') THEN
973 -- Executable Statements
974 resultout := 'COMPLETE';
975 RETURN;
976 END IF;
977
978 IF (FUNCMODE = 'RESPOND') THEN
979 -- Executable Statements
980 resultout := 'COMPLETE';
981 RETURN;
982 END IF;
983
984 IF (FUNCMODE = 'FORWARD') THEN
985 -- Executable Statements
986 resultout := 'COMPLETE';
987 RETURN;
988 END IF;
989
990 IF (FUNCMODE = 'TRANSFER') THEN
991 -- Executable Statements
992 resultout := 'COMPLETE';
993 RETURN;
994 END IF;
995
996 IF (FUNCMODE = 'TIMEOUT') THEN
997 -- Executable Statements
998 resultout := 'COMPLETE';
999 RETURN;
1000 END IF;
1001
1002 IF (FUNCMODE = 'CANCEL') THEN
1003 -- Executable Statements
1004 resultout := 'COMPLETE';
1005 RETURN;
1006 END IF;
1007
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 --
1011 IF (l_debug <> -1) THEN
1012 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1013 END IF;
1014 --
1015 wf_core.context('RLM_WF_SV','ReconcileDemand',
1016 itemtype, itemkey,TO_CHAR(actid),funcmode);
1017 resultout := 'COMPLETE:FAILURE';
1018 --
1019 IF (l_debug <> -1) THEN
1020 rlm_core_sv.dpop(C_SDEBUG);
1021 END IF;
1022 --
1023 WHEN e_DPFailed THEN
1024 --
1025 IF (l_debug <> -1) THEN
1026 rlm_core_sv.dlog(C_DEBUG,'Reconcile Demand failed');
1027 END IF;
1028 --
1029 IF g_Sch_rec.schedule_type <> RLM_DP_SV.k_SEQUENCED THEN
1030 --
1031 ROLLBACK TO s_child_process;
1032 --
1033 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
1034 g_Sch_rec.schedule_header_id,
1035 g_Grp_rec,
1036 rlm_core_sv.k_PS_ERROR);
1037 END IF;
1038 --
1039 COMMIT;
1040 wf_core.context('RLM_WF_SV','ReconcileDemand',
1041 itemtype, itemkey,TO_CHAR(actid),funcmode);
1042 --
1043 resultout := 'COMPLETE:FAILURE';
1044 --
1045 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
1046 --
1047 IF (l_debug <> -1) THEN
1048 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header in RD.e_DPFailed');
1049 END IF;
1050 --
1051 RAISE e_LockH;
1052 END IF;
1053 --
1054 IF (l_debug <> -1) THEN
1055 rlm_core_sv.dpop(C_SDEBUG);
1056 END IF;
1057 --
1058 WHEN OTHERS THEN
1059 wf_core.context('RLM_WF_SV','ReconcileDemand',
1060 itemtype, itemkey,TO_CHAR(actid),funcmode);
1061 resultout := 'COMPLETE:FAILURE';
1062 --
1063 IF (l_debug <> -1) THEN
1064 rlm_core_sv.dpop(C_SDEBUG);
1065 END IF;
1066 --RAISE;
1067
1068 END ReconcileDemand;
1069
1070 PROCEDURE PurgeInterface(
1071 itemtype IN VARCHAR2,
1072 itemkey IN VARCHAR2,
1073 actid IN NUMBER,
1074 funcmode IN VARCHAR2,
1075 resultout OUT NOCOPY VARCHAR2)
1076 IS
1077 --
1078 v_header_id NUMBER;
1079 v_status NUMBER;
1080 e_DPFailed EXCEPTION;
1081 --
1082 BEGIN
1083 --
1084 IF (l_debug <> -1) THEN
1085 rlm_core_sv.dpush(C_SDEBUG,'PurgeInterface');
1086 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1087 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1088 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1089 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1090 END IF;
1091 --
1092 IF (FUNCMODE = 'RUN') THEN
1093 -- Executable Statements
1094 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1095 --
1096 IF (l_debug <> -1) THEN
1097 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1098 END IF;
1099 --
1100 rlm_message_sv.dump_messages(v_header_id);
1101 rlm_dp_sv.PurgeInterfaceLines(v_header_id);
1102 rlm_message_sv.initialize_messages;
1103 COMMIT;
1104 --
1105 resultout := 'COMPLETE:SUCCESS';
1106 --
1107 IF (l_debug <> -1) THEN
1108 rlm_core_sv.dpop(C_SDEBUG);
1109 END IF;
1110 --
1111 RETURN;
1112 --
1113 END IF;
1114
1115 IF (FUNCMODE = 'CANCEL') THEN
1116 -- Executable Statements
1117 resultout := 'COMPLETE';
1118 RETURN;
1119 END IF;
1120
1121 IF (FUNCMODE = 'RESPOND') THEN
1122 -- Executable Statements
1123 resultout := 'COMPLETE';
1124 RETURN;
1125 END IF;
1126
1127 IF (FUNCMODE = 'FORWARD') THEN
1128 -- Executable Statements
1129 resultout := 'COMPLETE';
1130 RETURN;
1131 END IF;
1132
1133 IF (FUNCMODE = 'TRANSFER') THEN
1134 -- Executable Statements
1135 resultout := 'COMPLETE';
1136 RETURN;
1137 END IF;
1138
1139 IF (FUNCMODE = 'TIMEOUT') THEN
1140 -- Executable Statements
1141 resultout := 'COMPLETE';
1142 RETURN;
1143 END IF;
1144
1145 IF (FUNCMODE = 'CANCEL') THEN
1146 -- Executable Statements
1147 resultout := 'COMPLETE';
1148 RETURN;
1149 END IF;
1150
1151 EXCEPTION
1152 WHEN NO_DATA_FOUND THEN
1153 --
1154 IF (l_debug <> -1) THEN
1155 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1156 END IF;
1157 --
1158 wf_core.context('RLM_WF_SV','PurgeInterface',
1159 itemtype, itemkey,TO_CHAR(actid),funcmode);
1160 resultout := 'COMPLETE:FAILURE';
1161 --
1162 IF (l_debug <> -1) THEN
1163 rlm_core_sv.dpop(C_SDEBUG);
1164 END IF;
1165 --
1166 WHEN OTHERS THEN
1167 wf_core.context('RLM_WF_SV','PurgeInterface',
1168 itemtype, itemkey,TO_CHAR(actid),funcmode);
1169 resultout := 'COMPLETE:FAILURE';
1170 --
1171 IF (l_debug <> -1) THEN
1172 rlm_core_sv.dpop(C_SDEBUG);
1173 END IF;
1174 --
1175 RAISE;
1176
1177 END PurgeInterface;
1178
1179 PROCEDURE CheckErrors(
1180 itemtype IN VARCHAR2,
1181 itemkey IN VARCHAR2,
1182 actid IN NUMBER,
1183 funcmode IN VARCHAR2,
1184 resultout OUT NOCOPY VARCHAR2)
1185 IS
1186 -- Bug #: 3291401
1187 CURSOR c_process_status_schedule(p_header_id IN NUMBER) is
1188 SELECT process_status
1189 FROM rlm_schedule_headers
1190 WHERE header_id = p_header_id;
1191
1192 CURSOR c_process_status_interface(p_header_id IN NUMBER) is
1193 SELECT process_status
1194 FROM rlm_interface_headers
1195 WHERE header_id = p_header_id;
1196
1197 v_process_status NUMBER;
1198
1199 --
1200 v_header_id NUMBER;
1201 v_request_id NUMBER;
1202 v_schedule_num VARCHAR2(50);
1203 x_errors NUMBER := -1;
1204 x_real_errors NUMBER := -1;
1205 v_status NUMBER;
1206 e_DPFailed EXCEPTION;
1207
1208 BEGIN
1209 --
1210 IF (l_debug <> -1) THEN
1211 rlm_core_sv.dpush(C_SDEBUG,'CheckErrors');
1212 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1213 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1214 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1215 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1216 END IF;
1217 --
1218 IF (FUNCMODE = 'RUN') THEN
1219 -- Executable Statements
1220 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID'); --
1221 IF (l_debug <> -1) THEN
1222 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1223 END IF;
1224 --
1225 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
1226 --
1227 IF (l_debug <> -1) THEN
1228 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
1229 END IF;
1230 --
1231 v_schedule_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
1232 --
1233 IF (l_debug <> -1) THEN
1234 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_schedule_num);
1235 END IF;
1236 --
1237 rlm_message_sv.dump_messages(v_header_id);
1238 rlm_message_sv.initialize_messages;
1239 --
1240 -- Check for Errors
1241 -- After PurgeInterface, the succesfully processed schedule
1242 -- should have been deleted from interface tables.
1243 --
1244 SELECT COUNT(*)
1245 INTO x_errors
1246 FROM rlm_interface_headers
1247 WHERE header_id = v_header_id;
1248 --
1249 IF (l_debug <> -1) THEN
1250 rlm_core_sv.dlog(C_DEBUG,' No of Errors :', x_errors);
1251 END IF;
1252 --
1253 IF (x_errors = 0) then
1254 --
1255 -- Bug# 3291401 - Start
1256 -- Incase there is not data in the interface tables, checking for
1257 -- the schedule header status.
1258 OPEN c_process_status_schedule(v_header_id);
1259 --
1260 FETCH c_process_status_schedule INTO v_process_status;
1261 --
1262 IF (c_process_status_schedule%NOTFOUND) THEN
1263 --
1264 OPEN c_process_status_interface(v_header_id);
1265 FETCH c_process_status_interface INTO v_process_status;
1266 --
1267 IF (c_process_status_interface%NOTFOUND) THEN
1268 resultout := 'COMPLETE:ABT';
1269 END IF;
1270 --
1271 IF (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 --
1275 -- Setting the x_errors to be more than one
1276 -- so that correct return value can be set
1277 x_errors := 1;
1278 END IF ;
1279 --
1280 CLOSE c_process_status_interface;
1281 --
1282 ELSIF (v_process_status IS NOT NULL AND
1283 (v_process_status = rlm_core_sv.k_PS_PARTIAL_PROCESSED OR
1284 v_process_status = RLM_CORE_SV.k_PS_ERROR) ) THEN
1285 -- Setting the x_errors to be more than one
1286 -- so that correct return value can be set
1287 x_errors := 1;
1288 END IF;
1289 --
1290 CLOSE c_process_status_schedule;
1291 --
1292 -- Bug# 3291401 - End
1293 --
1294 wf_engine.SetItemAttrNumber( itemtype,
1295 itemkey,
1296 'ERRORS_EXIST',
1297 g_PROC_SUCCESS); ---No Errors
1298 --
1299 ELSE
1300 --
1301 wf_engine.SetItemAttrNumber( itemtype,
1302 itemkey,
1303 'ERRORS_EXIST',
1304 g_PROC_ERROR); ---Errors Exist
1305 --
1306 END IF;
1307 --
1308 IF (x_errors > 0 ) THEN
1309 resultout := 'COMPLETE:ERRORS';
1310 --
1311 IF (l_debug <> -1) THEN
1312 rlm_core_sv.dlog(C_DEBUG,' resultout :', resultout);
1313 rlm_core_sv.dpop(C_SDEBUG);
1314 END IF;
1315 --
1316 RETURN;
1317 ELSE
1318 resultout := 'COMPLETE:N';
1319 --
1320 IF (l_debug <> -1) THEN
1321 rlm_core_sv.dlog(C_DEBUG,' resultout :', resultout);
1322 rlm_core_sv.dpop(C_SDEBUG);
1323 END IF;
1324 --
1325 RETURN;
1326 END IF;
1327 --
1328 END IF;
1329
1330 IF (FUNCMODE = 'CANCEL') THEN
1331 -- Executable Statements
1332 resultout := 'COMPLETE';
1333 RETURN;
1334 END IF;
1335
1336 IF (FUNCMODE = 'RESPOND') THEN
1337 -- Executable Statements
1338 resultout := 'COMPLETE';
1339 RETURN;
1340 END IF;
1341
1342 IF (FUNCMODE = 'FORWARD') THEN
1343 -- Executable Statements
1344 resultout := 'COMPLETE';
1345 RETURN;
1346 END IF;
1347
1348 IF (FUNCMODE = 'TRANSFER') THEN
1349 -- Executable Statements
1350 resultout := 'COMPLETE';
1351 RETURN;
1352 END IF;
1353
1354 IF (FUNCMODE = 'TIMEOUT') THEN
1355 -- Executable Statements
1356 resultout := 'COMPLETE';
1357 RETURN;
1358 END IF;
1359
1360 IF (FUNCMODE = 'CANCEL') THEN
1361 -- Executable Statements
1362 resultout := 'COMPLETE';
1363 RETURN;
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN NO_DATA_FOUND THEN
1368 --
1369 IF (l_debug <> -1) THEN
1370 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1371 END IF;
1372 --
1373 wf_core.context('RLM_WF_SV','CheckErrors',
1374 itemtype, itemkey,TO_CHAR(actid),funcmode);
1375 resultout := 'COMPLETE:ABT';
1376 --
1377 IF (l_debug <> -1) THEN
1378 rlm_core_sv.dpop(C_SDEBUG);
1379 END IF;
1380 --
1381 WHEN OTHERS THEN
1382 wf_core.context('RLM_WF_SV','CheckErrors',
1383 itemtype, itemkey,TO_CHAR(actid),funcmode);
1384 resultout := 'COMPLETE:ABT';
1385 --
1386 IF (l_debug <> -1) THEN
1387 rlm_core_sv.dpop(C_SDEBUG);
1388 END IF;
1389 --
1390 RAISE;
1391
1392 END CheckErrors;
1393
1394 PROCEDURE RunReport(
1395 itemtype IN VARCHAR2,
1396 itemkey IN VARCHAR2,
1397 actid IN NUMBER,
1398 funcmode IN VARCHAR2,
1399 resultout OUT NOCOPY VARCHAR2)
1400 IS
1401 --
1402 v_header_id NUMBER;
1403 v_status NUMBER;
1404 v_org_id NUMBER;
1405 v_request_id NUMBER := -1;
1406 v_sched_num VARCHAR2(50);
1407 x_request_id NUMBER := -1;
1408 x_errors NUMBER := -1;
1409 x_no_copies NUMBER :=0;
1410 x_print_style VARCHAR2(30);
1411 x_printer VARCHAR2(30);
1412 x_save_output_flag VARCHAR2(1);
1413 x_result BOOLEAN;
1414 e_DPFailed EXCEPTION;
1415 --
1416 BEGIN
1417 --
1418 IF (l_debug <> -1) THEN
1419 rlm_core_sv.dpush(C_SDEBUG,'RunReport');
1420 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1421 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1422 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1423 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1424 END IF;
1425 --
1426 IF (FUNCMODE = 'RUN') THEN
1427 -- Executable Statements
1428 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1429 --
1430 IF (l_debug <> -1) THEN
1431 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
1432 END IF;
1433 --
1434 v_org_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
1435 --
1436 IF (l_debug <> -1) THEN
1437 rlm_core_sv.dlog(C_DEBUG,' Org Id :', v_org_id);
1438 END IF;
1439 --
1440 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
1441 --
1442 IF (l_debug <> -1) THEN
1443 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
1444 END IF;
1445 --
1446 v_sched_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
1447 --
1448 IF (l_debug <> -1) THEN
1449 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_sched_num);
1450 END IF;
1451
1452 /*
1453 -- Needs to be used once DSP concurrent program sets print options parameters
1454 x_result :=fnd_concurrent.get_request_print_options(fnd_global.conc_request_id,
1455 x_no_copies ,
1456 x_print_style ,
1457 x_printer ,
1458 x_save_output_flag );
1459 IF (x_result =TRUE) then
1460 x_result :=fnd_request.set_print_options(x_printer,
1461 x_print_style,
1462 x_no_copies,
1463 NULL,
1464 'N');
1465 END IF;
1466 */
1467 --
1468 fnd_request.set_org_id(v_org_id);
1469 --
1470 x_request_id := fnd_request.submit_request ('RLM',
1471 'RLMDPDER',
1472 NULL,
1473 NULL,
1474 FALSE,
1475 v_org_id,
1476 v_request_id,
1477 v_request_id,
1478 NULL,
1479 NULL,
1480 NULL,
1481 NULL,
1482 NULL,
1483 NULL,
1484 NULL,
1485 NULL,
1486 NULL,
1487 NULL,
1488 NULL,
1489 NULL,
1490 NULL,
1491 NULL,
1492 NULL, --v_sched_num
1493 NULL, --v_sched_num
1494 NULL,
1495 NULL,
1496 NULL,
1497 NULL,
1498 NULL,
1499 NULL,
1500 NULL);
1501 -- END IF;
1502 -- commit;
1503
1504 resultout := 'COMPLETE:SUCCESS';
1505 --
1506 IF (l_debug <> -1) THEN
1507 rlm_core_sv.dpop(C_SDEBUG);
1508 END IF;
1509 --
1510 RETURN;
1511 END IF;
1512
1513 IF (FUNCMODE = 'CANCEL') THEN
1514 -- Executable Statements
1515 resultout := 'COMPLETE';
1516 RETURN;
1517 END IF;
1518
1519 IF (FUNCMODE = 'RESPOND') THEN
1520 -- Executable Statements
1521 resultout := 'COMPLETE';
1522 RETURN;
1523 END IF;
1524
1525 IF (FUNCMODE = 'FORWARD') THEN
1526 -- Executable Statements
1527 resultout := 'COMPLETE';
1528 RETURN;
1529 END IF;
1530
1531 IF (FUNCMODE = 'TRANSFER') THEN
1532 -- Executable Statements
1533 resultout := 'COMPLETE';
1534 RETURN;
1535 END IF;
1536
1537 IF (FUNCMODE = 'TIMEOUT') THEN
1538 -- Executable Statements
1539 resultout := 'COMPLETE';
1540 RETURN;
1541 END IF;
1542
1543 IF (FUNCMODE = 'CANCEL') THEN
1544 -- Executable Statements
1545 resultout := 'COMPLETE';
1546 RETURN;
1547 END IF;
1548
1549 EXCEPTION
1550 WHEN NO_DATA_FOUND THEN
1551 --
1552 IF (l_debug <> -1) THEN
1553 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1554 END IF;
1555 --
1556 wf_core.context('RLM_WF_SV','RunReport',
1557 itemtype, itemkey,TO_CHAR(actid),funcmode);
1558 resultout := 'COMPLETE:FAILURE';
1559 --
1560 IF (l_debug <> -1) THEN
1561 rlm_core_sv.dpop(C_SDEBUG);
1562 END IF;
1563 --
1564 WHEN OTHERS THEN
1565 wf_core.context('RLM_WF_SV','RunReport',
1566 itemtype, itemkey,TO_CHAR(actid),funcmode);
1567 resultout := 'COMPLETE:FAILURE';
1568 --
1569 IF (l_debug <> -1) THEN
1570 rlm_core_sv.dpop(C_SDEBUG);
1571 END IF;
1572 --
1573 RAISE;
1574
1575 END RunReport;
1576
1577 PROCEDURE GetScheduleDetails( x_Header_Id IN NUMBER,
1578 x_Schedule_Num OUT NOCOPY VARCHAR2,
1579 x_Customer_Name OUT NOCOPY VARCHAR2,
1580 x_Schedule_Gen_Date OUT NOCOPY DATE)
1581 -- Bug#: 3053299 - Added Schedule Generation Date as argument
1582 IS
1583 BEGIN
1584 --
1585 IF (l_debug <> -1) THEN
1586 rlm_core_sv.dpush(C_SDEBUG,'GetScheduleDetails');
1587 rlm_core_sv.dlog(C_DEBUG,'x_Header_Id ', x_Header_Id);
1588 END IF;
1589 --
1590 select schedule_reference_num, cust_name_ext, sched_generation_date
1591 into x_Schedule_Num, x_Customer_Name , x_Schedule_Gen_Date
1592 from rlm_interface_headers
1593 where header_id = x_Header_Id ;
1594 --
1595 IF (l_debug <> -1) THEN
1596 rlm_core_sv.dlog(C_DEBUG,' Schedule Generation Date :',
1597 TO_CHAR(x_Schedule_Gen_Date));
1598 rlm_core_sv.dlog(C_DEBUG,' Successful select');
1599 rlm_core_sv.dpop(C_SDEBUG);
1600 END IF;
1601 --
1602 EXCEPTION
1603 WHEN NO_DATA_FOUND THEN
1604 --
1605 IF (l_debug <> -1) THEN
1606 rlm_core_sv.dpop(C_SDEBUG, 'No Data Found');
1607 END IF;
1608 --
1609 WHEN OTHERS THEN
1610 --
1611 IF (l_debug <> -1) THEN
1612 rlm_core_sv.dpop(C_SDEBUG, 'Other Errors');
1613 END IF;
1614
1615 END GetScheduleDetails;
1616
1617
1618 PROCEDURE StartDSPLoop( errbuf OUT NOCOPY VARCHAR2,
1619 retcode OUT NOCOPY VARCHAR2,
1620 p_Header_Id IN NUMBER,
1621 p_Line_id IN NUMBER,
1622 v_Sch_rec IN rlm_interface_headers%ROWTYPE,
1623 v_Grp_rec IN rlm_dp_sv.t_Group_rec)
1624 IS
1625 --
1626 v_ItemKey VARCHAR2(100) := to_char(p_Header_Id)||
1627 '+' || to_char(p_Line_id);
1628 v_ItemType VARCHAR2(30) := g_ItemType;
1629 v_ProcessName VARCHAR2(30) := g_ProcessNameLoop;
1630 v_ScheduleNum VARCHAR2(35) ;
1631 v_Customer VARCHAR2(30) ;
1632 v_ScheduleGenDate DATE ;
1633 v_org_id NUMBER;
1634 v_retcode NUMBER;
1635 v_count NUMBER;
1636 v_dummy NUMBER DEFAULT 0;
1637 e_DSPFailed EXCEPTION;
1638 BEGIN
1639 --
1640 IF (l_debug <> -1) THEN
1641 rlm_core_sv.dpush(C_SDEBUG,'StartDSPLoop');
1642 rlm_core_sv.dlog(C_DEBUG,'p_Header_Id ', p_Header_Id);
1643 rlm_core_sv.dlog(C_DEBUG,'Starting process:',v_ItemKey);
1644 rlm_core_sv.dlog(C_DEBUG,'Org ID', v_org_id);
1645 END IF;
1646 --
1647 -- fnd_profile.get('ORG_ID', v_org_id);
1648 --
1649 v_org_id := v_Sch_rec.org_id;
1650 --
1651 IF (l_debug <> -1) THEN
1652 rlm_core_sv.dlog(C_DEBUG,'Org Id ', v_org_id);
1653 END IF;
1654 --
1655 -- Bug#: 3053299 - Added Schedule Generation Date as argument
1656 GetScheduleDetails(p_Header_Id, v_ScheduleNum, v_Customer, v_ScheduleGenDate);
1657 --
1658 IF (l_debug <> -1) THEN
1659 rlm_core_sv.dlog(C_DEBUG,'v_ScheduleNum ', v_ScheduleNum);
1660 rlm_core_sv.dlog(C_DEBUG,'v_Customer ', v_Customer);
1661 END IF;
1662 --
1663 -- Set various Header Attributes
1664
1665 wf_engine.SetItemAttrNumber( v_ItemType,
1666 v_ItemKey,
1667 'USER_ID',
1668 FND_GLOBAL.USER_ID);
1669
1670 wf_engine.SetItemAttrNumber( v_ItemType,
1671 v_ItemKey,
1672 'APPLICATION_ID',
1673 FND_GLOBAL.RESP_APPL_ID);
1674
1675 wf_engine.SetItemAttrNumber( v_ItemType,
1676 v_ItemKey,
1677 'REQUEST_ID',
1678 FND_GLOBAL.CONC_REQUEST_ID);
1679
1680 wf_engine.SetItemAttrNumber( v_ItemType,
1681 v_ItemKey,
1682 'RESPONSIBILITY_ID',
1683 FND_GLOBAL.RESP_ID);
1684
1685 wf_engine.SetItemAttrNumber( v_ItemType,
1686 v_ItemKey,
1687 'ORG_ID',
1688 v_org_id);
1689
1690 wf_engine.SetItemAttrNumber( v_ItemType,
1691 v_ItemKey,
1692 'HEADER_ID',
1693 p_Header_Id);
1694
1695 wf_engine.SetItemAttrText( v_ItemType,
1696 v_ItemKey,
1697 'SCHEDULE_NUMBER',
1698 v_ScheduleNum);
1699
1700 wf_engine.SetItemAttrText( v_ItemType,
1701 v_ItemKey,
1702 'CUSTOMER_NAME',
1703 v_Customer);
1704 wf_engine.SetItemAttrNumber( v_ItemType,
1705 v_ItemKey,
1706 'ERRORS_EXIST',
1707 v_dummy);
1708
1709 -- Bug#: 3053299 - Setting the tokens for From User,
1710 -- To User and Schedule Generation Date
1711
1712
1713 wf_engine.SetItemAttrText( v_ItemType,
1714 v_ItemKey,
1715 'FROM_USER',
1716 Notification_FromUser(FND_GLOBAL.RESP_ID,
1717 FND_GLOBAL.RESP_APPL_ID));
1718
1719
1720 wf_engine.SetItemAttrText( v_ItemType,
1721 v_ItemKey,
1722 'TO_USER',
1723 Notification_ToUser(FND_GLOBAL.USER_ID));
1724
1725 wf_engine.SetItemAttrText( v_ItemType,
1726 v_ItemKey,
1727 'SCHED_GEN_DATE',
1728 v_ScheduleGenDate);
1729
1730 --
1731 IF (l_debug <> -1) THEN
1732 rlm_core_sv.dlog(C_DEBUG,'Before Start Process DSP Loop');
1733 END IF;
1734 --
1735 wf_engine.StartProcess(v_ItemType, v_ItemKey);
1736 --
1737 IF (l_debug <> -1) THEN
1738 rlm_core_sv.dlog(C_DEBUG,'After Start Process DSP Loop ');
1739 END IF;
1740 --
1741 v_retcode := wf_engine.GetItemAttrNumber(v_ItemType,v_ItemKey,'ERRORS_EXIST');
1742 --
1743 IF (l_debug <> -1) THEN
1744 rlm_core_sv.dlog(C_DEBUG,' Errors Status: ', v_retcode);
1745 END IF;
1746 --
1747 retcode := g_PROC_SUCCESS;
1748 --
1749 IF (l_debug <> -1) THEN
1750 rlm_core_sv.dpop(C_SDEBUG);
1751 END IF;
1752 --
1753 EXCEPTION
1754 --
1755 WHEN OTHERS THEN
1756 --
1757 IF (l_debug <> -1) THEN
1758 rlm_core_sv.dlog(C_DEBUG,'Errors found ');
1759 rlm_core_sv.dlog(C_DEBUG,'Error: ',SUBSTR(SQLERRM,1,1500));
1760 END IF;
1761 --
1762 retcode := g_PROC_ERROR;
1763 --
1764 IF (l_debug <> -1) THEN
1765 rlm_core_sv.dpop(C_SDEBUG);
1766 END IF;
1767 --
1768 -- rlm_core_sv.stop_debug;
1769 --
1770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1771
1772 END StartDSPLoop;
1773
1774
1775
1776
1777 PROCEDURE CreateDSPLoop( errbuf OUT NOCOPY VARCHAR2,
1778 retcode OUT NOCOPY VARCHAR2,
1779 p_Header_Id IN NUMBER,
1780 p_Line_id IN NUMBER)
1781 IS
1782 --
1783 v_ItemKey VARCHAR2(100) := to_char(p_Header_Id)||
1784 '+' || to_char(p_Line_id);
1785 v_ItemType VARCHAR2(30) := g_ItemType;
1786 v_ProcessName VARCHAR2(30) := g_ProcessNameLoop;
1787 v_org_id NUMBER;
1788 v_retcode NUMBER;
1789 v_count NUMBER;
1790 v_dummy NUMBER DEFAULT 0;
1791 e_DSPFailed EXCEPTION;
1792 --
1793 BEGIN
1794 --
1795 IF (l_debug <> -1) THEN
1796 rlm_core_sv.dpush(C_SDEBUG,'CreateDSPLoop');
1797 rlm_core_sv.dlog(C_DEBUG,'p_Header_Id ', p_Header_Id);
1798 rlm_core_sv.dlog(C_DEBUG,'Creating Loop:',v_ItemKey);
1799 rlm_core_Sv.dlog(C_DEBUG, 'v_ItemType', v_ItemType);
1800 END IF;
1801 --
1802 -- fnd_profile.get('ORG_ID', v_org_id);
1803 --
1804 IF (l_debug <> -1) THEN
1805 rlm_core_sv.dlog(C_DEBUG, 'Org Id ', MO_GLOBAL.get_current_org_id);
1806 END IF;
1807 --
1808 -- Abort the process if it has hung
1809 BEGIN
1810 --
1811 IF (l_debug <> -1) THEN
1812 rlm_core_sv.dlog(C_DEBUG,'Aborting old processes');
1813 END IF;
1814 --
1815 wf_engine.AbortProcess(itemtype => v_ItemType,
1816 itemkey => v_ItemKey);
1817 --
1818 IF (l_debug <> -1) THEN
1819 rlm_core_sv.dlog(C_DEBUG,'after abort bad process');
1820 END IF;
1821 --
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 --
1825 IF (l_debug <> -1) THEN
1826 rlm_core_sv.dlog(C_DEBUG,'Did not abort any process');
1827 END IF;
1828 --
1829 END;
1830 --
1831 -- Check whether Item has already been run in Workflow and purge it
1832 -- Bug 2756981: Set force to TRUE to allow child processes to be purged
1833 --
1834 wf_purge.Items(itemtype => v_ItemType,
1835 itemkey => v_ItemKey,
1836 enddate => sysdate,
1837 docommit => FALSE,
1838 force => TRUE);
1839 --
1840 IF (l_debug <> -1) THEN
1841 rlm_core_sv.dlog(C_DEBUG,'After Purge for Item key ',v_ItemKey);
1842 END IF;
1843 --
1844 wf_engine.CreateProcess(v_ItemType, v_ItemKey, v_ProcessName);
1845 wf_engine.SetItemParent(v_ItemType,v_ItemKey,v_ItemType,to_char(p_Header_Id),
1846 to_char(p_Header_Id));
1847 --
1848 wf_engine.SetItemUserKey(v_ItemType, v_ItemKey,
1849 g_Sch_rec.schedule_reference_num);
1850
1851 --
1852 retcode := g_PROC_SUCCESS;
1853 --
1854 IF (l_debug <> -1) THEN
1855 rlm_core_sv.dlog(C_DEBUG,'After Create Process DSP Loop ');
1856 rlm_core_sv.dpop(C_SDEBUG);
1857 END IF;
1858
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 --
1862 IF (l_debug <> -1) THEN
1863 rlm_core_sv.dlog(C_DEBUG,'Errors found ');
1864 rlm_core_sv.dlog(C_DEBUG,'Error: ',SUBSTR(SQLERRM,1,1500));
1865 END IF;
1866 --
1867 retcode := g_PROC_ERROR;
1868 --
1869 IF (l_debug <> -1) THEN
1870 rlm_core_sv.dpop(C_SDEBUG);
1871 END IF;
1872 --
1873 -- rlm_core_sv.stop_debug;
1874 --
1875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1876
1877
1878 END CreateDSPLoop;
1879
1880
1881 PROCEDURE UpdateHeaderPS(
1882 itemtype IN VARCHAR2,
1883 itemkey IN VARCHAR2,
1884 actid IN NUMBER,
1885 funcmode IN VARCHAR2,
1886 resultout OUT NOCOPY VARCHAR2)
1887 IS
1888 --
1889 v_header_id NUMBER;
1890 --
1891 BEGIN
1892 --
1893 IF (l_debug <> -1) THEN
1894 rlm_core_sv.dpush(C_SDEBUG,'UpdateHeaderPS');
1895 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
1896 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
1897 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
1898 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
1899 END IF;
1900 --
1901 IF (FUNCMODE = 'RUN') THEN
1902 -- Executable Statements
1903 --
1904 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1905 --
1906 IF (l_debug <> -1) THEN
1907 rlm_core_sv.dlog(C_DEBUG,'WF Header_id :',v_header_id);
1908 rlm_core_sv.dlog(C_DEBUG,' Header_id :', g_Sch_rec.header_id);
1909 rlm_core_sv.dlog(C_DEBUG,'schedule_header_id',
1910 g_Sch_rec.schedule_header_id);
1911
1912 END IF;
1913 --
1914 IF g_Sch_rec.header_id IS NULL THEN
1915 --
1916 IF (l_debug <> -1) THEN
1917 rlm_core_sv.dlog(C_DEBUG,'Assigning the global variable.');
1918 END IF;
1919 --
1920 SELECT * INTO g_Sch_rec
1921 FROM rlm_interface_headers
1922 WHERE header_id = v_header_id;
1923 --
1924 END IF;
1925 rlm_dp_sv.UpdateHeaderPS(g_Sch_rec.header_id,
1926 g_Sch_rec.schedule_header_id);
1927
1928 --
1929 resultout := 'COMPLETE:SUCCESS';
1930 --
1931 IF (l_debug <> -1) THEN
1932 rlm_core_sv.dpop(C_SDEBUG);
1933 END IF;
1934 --
1935 RETURN;
1936 --
1937 END IF;
1938
1939 IF (FUNCMODE = 'CANCEL') THEN
1940 -- Executable Statements
1941 resultout := 'COMPLETE';
1942 RETURN;
1943 END IF;
1944
1945 IF (FUNCMODE = 'RESPOND') THEN
1946 -- Executable Statements
1947 resultout := 'COMPLETE';
1948 RETURN;
1949 END IF;
1950
1951 IF (FUNCMODE = 'FORWARD') THEN
1952 -- Executable Statements
1953 resultout := 'COMPLETE';
1954 RETURN;
1955 END IF;
1956
1957 IF (FUNCMODE = 'TRANSFER') THEN
1958 -- Executable Statements
1959 resultout := 'COMPLETE';
1960 RETURN;
1961 END IF;
1962
1963 IF (FUNCMODE = 'TIMEOUT') THEN
1964 -- Executable Statements
1965 resultout := 'COMPLETE';
1966 RETURN;
1967 END IF;
1968
1969 IF (FUNCMODE = 'CANCEL') THEN
1970 -- Executable Statements
1971 resultout := 'COMPLETE';
1972 RETURN;
1973 END IF;
1974
1975 EXCEPTION
1976 WHEN NO_DATA_FOUND THEN
1977 --
1978 IF (l_debug <> -1) THEN
1979 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
1980 END IF;
1981 --
1982 wf_core.context('RLM_WF_SV','UpdateHeaderPS',
1983 itemtype, itemkey,TO_CHAR(actid),funcmode);
1984 resultout := 'COMPLETE:FAILURE';
1985 --
1986 IF (l_debug <> -1) THEN
1987 rlm_core_sv.dpop(C_SDEBUG);
1988 END IF;
1989 --
1990 WHEN OTHERS THEN
1991 --
1992 IF (l_debug <> -1) THEN
1993 rlm_core_sv.dlog(C_DEBUG,'When Others');
1994 END IF;
1995 --
1996 wf_core.context('RLM_WF_SV','UpdateHeaderPS',
1997 itemtype, itemkey,TO_CHAR(actid),funcmode);
1998 resultout := 'COMPLETE:FAILURE';
1999 --
2000 IF (l_debug <> -1) THEN
2001 rlm_core_sv.dpop(C_SDEBUG);
2002 END IF;
2003 --RAISE;
2004 END UpdateHeaderPS;
2005
2006
2007 PROCEDURE ProcessGroupDemand(
2008 itemtype IN VARCHAR2,
2009 itemkey IN VARCHAR2,
2010 actid IN NUMBER,
2011 funcmode IN VARCHAR2,
2012 resultout OUT NOCOPY VARCHAR2)
2013 IS
2014 --
2015 v_header_id NUMBER;
2016 v_status NUMBER;
2017 e_MDFailed EXCEPTION;
2018 e_MFFailed EXCEPTION;
2019 e_RDFailed EXCEPTION;
2020 /*Bug 2581117 */
2021 v_md_start_time NUMBER;
2022 v_md_end_time NUMBER;
2023 v_md_total NUMBER :=0;
2024 v_rd_start_time NUMBER;
2025 v_rd_end_time NUMBER;
2026 v_rd_total NUMBER:=0;
2027 v_mf_start_time NUMBER;
2028 v_mf_end_time NUMBER;
2029 v_mf_total NUMBER:=0;
2030 v_msg_text VARCHAR2(32000);
2031 /* Bugfix 12863728 */
2032 v_rlm_pg_gps_start_time NUMBER;
2033 v_rlm_pg_gps_end_time NUMBER;
2034 v_rlm_pg_gps_total NUMBER:=0;
2035 v_rlm_pg_gps_msg_text VARCHAR2(32000);
2036 --
2037 BEGIN
2038 --
2039 IF (l_debug <> -1) THEN
2040 rlm_core_sv.dpush(C_SDEBUG,'ProcessGroupDemand');
2041 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2042 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2043 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2044 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2045 END IF;
2046 --
2047 IF (FUNCMODE = 'RUN') THEN
2048 -- Executable Statements
2049 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2050 --
2051 IF (l_debug <> -1) THEN
2052 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2053 END IF;
2054 --
2055 SAVEPOINT s_child_process;
2056 SELECT hsecs INTO v_md_start_time FROM v$timer;
2057 rlm_manage_demand_sv.ManageDemand(v_header_id,
2058 g_Sch_rec,
2059 g_Grp_rec,
2060 v_status);
2061 --
2062 IF (l_debug <> -1) THEN
2063 rlm_core_sv.dlog(C_DEBUG,' After ManageDemand v_status: ', v_status);
2064 END IF;
2065 --
2066 SELECT hsecs INTO v_md_end_time FROM v$timer;
2067 v_md_total:=v_md_total+(v_md_end_time-v_md_start_time)/100;
2068
2069 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2070 --
2071 RAISE e_MDFailed;
2072 --
2073 END IF;
2074 --
2075 IF (l_debug <> -1) THEN
2076 rlm_core_sv.dlog(C_DEBUG,'Before Manage Forecast');
2077 END IF;
2078 --
2079 SELECT hsecs INTO v_mf_start_time FROM v$timer;
2080
2081 rlm_forecast_sv.ManageForecast(v_header_id,
2082 g_Sch_rec,
2083 g_Grp_rec,
2084 v_status);
2085 --
2086 IF (l_debug <> -1) THEN
2087 rlm_core_sv.dlog(C_DEBUG,' After ManageForecast v_status: ', v_status);
2088 END IF;
2089 --
2090 SELECT hsecs INTO v_mf_end_time FROM v$timer;
2091 v_mf_total:=v_mf_total+(v_mf_end_time-v_mf_start_time)/100;
2092
2093 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2094 --
2095 RAISE e_MFFailed;
2096 --
2097 END IF;
2098 --
2099 IF (l_debug <> -1) THEN
2100 rlm_core_sv.dlog(C_DEBUG,'Before Reconcile Demand');
2101 END IF;
2102 --
2103 SELECT hsecs INTO v_rd_start_time FROM v$timer;
2104
2105 rlm_rd_sv.RecDemand(v_header_id,
2106 g_Sch_rec,
2107 g_Grp_rec,
2108 v_status);
2109
2110 --
2111 IF (l_debug <> -1) THEN
2112 rlm_core_sv.dlog(C_DEBUG, 'After ReconcileDemand v_status', v_status);
2113 END IF;
2114 --
2115 SELECT hsecs INTO v_rd_end_time FROM v$timer;
2116 v_rd_total :=v_rd_total+(v_rd_end_time-v_rd_start_time)/100;
2117 --
2118 IF v_status = rlm_core_sv.k_PROC_ERROR THEN
2119 --
2120 RAISE e_RDFailed;
2121 --
2122 END IF;
2123 --
2124 SELECT hsecs INTO v_rlm_pg_gps_start_time from v$timer; --Bugfix 12863728
2125 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2126 g_Sch_rec.schedule_header_id,
2127 g_Grp_rec,
2128 rlm_core_sv.k_PS_PROCESSED);
2129
2130 COMMIT;
2131 --
2132 SELECT hsecs INTO v_rlm_pg_gps_end_time FROM v$timer; --Bugfix 12863728
2133 v_rlm_pg_gps_total :=v_rlm_pg_gps_total+(v_rlm_pg_gps_end_time-v_rlm_pg_gps_start_time )/100; --Bugfix 12863728
2134 --
2135 resultout := 'COMPLETE:SUCCESS';
2136 --
2137 IF (l_debug <> -1) THEN
2138 rlm_core_sv.dpop(C_SDEBUG);
2139 END IF;
2140 --
2141 l_msg_text:='Total Time spent in Validatedemand call - '||
2142 (l_val_end_time-l_val_start_time)/100;
2143 fnd_file.put_line(fnd_file.log,l_msg_text);
2144
2145 l_msg_text:='Time spent in CompareSched call - '||
2146 (l_comp_end_time-l_comp_start_time)/100 ;
2147 fnd_file.put_line(fnd_file.log,l_msg_text);
2148
2149 v_msg_text:='Total Time spent in Managedemand call - '|| v_md_total;
2150 fnd_file.put_line(fnd_file.log, v_msg_text);
2151
2152 v_msg_text:='Total Time spent in Manageforecast call - '|| v_mf_total ;
2153 fnd_file.put_line(fnd_file.log,v_msg_text);
2154
2155 v_msg_text:='Total Time spent in RecDemand call - '|| v_rd_total ;
2156 fnd_file.put_line(fnd_file.log,v_msg_text);
2157
2158 v_rlm_pg_gps_msg_text:='Total Time spent in ProcessGroup Update GroupPS call - '|| v_rlm_pg_gps_total ; --Bugfix 12863728
2159 fnd_file.put_line(fnd_file.log, v_rlm_pg_gps_msg_text); --Bugfix 12863728
2160
2161 RETURN;
2162 --
2163 END IF;
2164
2165 IF (FUNCMODE = 'CANCEL') THEN
2166 -- Executable Statements
2167 resultout := 'COMPLETE';
2168 RETURN;
2169 END IF;
2170
2171 IF (FUNCMODE = 'RESPOND') THEN
2172 -- Executable Statements
2173 resultout := 'COMPLETE';
2174 RETURN;
2175 END IF;
2176
2177 IF (FUNCMODE = 'FORWARD') THEN
2178 -- Executable Statements
2179 resultout := 'COMPLETE';
2180 RETURN;
2181 END IF;
2182
2183 IF (FUNCMODE = 'TRANSFER') THEN
2184 -- Executable Statements
2185 resultout := 'COMPLETE';
2186 RETURN;
2187 END IF;
2188
2189 IF (FUNCMODE = 'TIMEOUT') THEN
2190 -- Executable Statements
2191 resultout := 'COMPLETE';
2192 RETURN;
2193 END IF;
2194
2195 IF (FUNCMODE = 'CANCEL') THEN
2196 -- Executable Statements
2197 resultout := 'COMPLETE';
2198 RETURN;
2199 END IF;
2200
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND THEN
2203 --
2204 IF (l_debug <> -1) THEN
2205 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2206 END IF;
2207 --
2208 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2209 itemtype, itemkey,TO_CHAR(actid),funcmode);
2210 resultout := 'COMPLETE:FAILURE';
2211 --
2212 IF (l_debug <> -1) THEN
2213 rlm_core_sv.dpop(C_SDEBUG);
2214 END IF;
2215 --
2216 WHEN e_MDFailed THEN
2217 --
2218 IF (l_debug <> -1) THEN
2219 rlm_core_sv.dlog(C_DEBUG,'Manage Demand failed');
2220 END IF;
2221 --
2222 ROLLBACK TO s_child_process;
2223 --
2224 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2225 g_Sch_rec.schedule_header_id,
2226 g_Grp_rec,
2227 rlm_core_sv.k_PS_ERROR);
2228
2229 -- Bug#: 2771756 - Start
2230 -- Bug: 4198330 added grouping info
2231 rlm_message_sv.removeMessages(
2232 p_header_id => v_header_id,
2233 p_message => 'RLM_RSO_CREATION_INFO',
2234 p_message_type => 'I',
2235 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2236 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2237 p_customer_item_id => g_Grp_rec.customer_item_id,
2238 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2239 -- Bug#: 2771756 - End
2240
2241 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2242 itemtype, itemkey,TO_CHAR(actid),funcmode);
2243 --
2244 resultout := 'COMPLETE:FAILURE';
2245 COMMIT;
2246 --
2247 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2248 --
2249 IF (l_debug <> -1) THEN
2250 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_MDFailed');
2251 END IF;
2252 --
2253 RAISE e_LockH;
2254 --
2255 END IF;
2256 --
2257 IF (l_debug <> -1) THEN
2258 rlm_core_sv.dpop(C_SDEBUG);
2259 END IF;
2260 --
2261 WHEN e_MFFailed THEN
2262 --
2263 IF (l_debug <> -1) THEN
2264 rlm_core_sv.dlog(C_DEBUG,'Manage Forecast failed');
2265 END IF;
2266 --
2267 ROLLBACK TO s_child_process;
2268 --
2269 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2270 g_Sch_rec.schedule_header_id,
2271 g_Grp_rec,
2272 rlm_core_sv.k_PS_ERROR);
2273
2274 -- Bug#: 2771756 - Start
2275 -- Bug: 4198330 added grouping info
2276 rlm_message_sv.removeMessages(
2277 p_header_id => v_header_id,
2278 p_message => 'RLM_RSO_CREATION_INFO',
2279 p_message_type => 'I',
2280 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2281 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2282 p_customer_item_id => g_Grp_rec.customer_item_id,
2283 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2284 -- Bug#: 2771756 - End
2285
2286 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2287 itemtype, itemkey,TO_CHAR(actid),funcmode);
2288 resultout := 'COMPLETE:FAILURE';
2289 COMMIT;
2290 --
2291 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2292 --
2293 IF (l_debug <> -1) THEN
2294 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_FDFailed');
2295 END IF;
2296 --
2297 RAISE e_LockH;
2298 --
2299 END IF;
2300 --
2301 IF (l_debug <> -1) THEN
2302 rlm_core_sv.dpop(C_SDEBUG);
2303 END IF;
2304 --
2305 WHEN e_RDFailed THEN
2306 --
2307 IF (l_debug <> -1) THEN
2308 rlm_core_sv.dlog(C_DEBUG,'Reconcile Demand failed');
2309 END IF;
2310 --
2311 ROLLBACK TO s_child_process;
2312 --
2313 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2314 g_Sch_rec.schedule_header_id,
2315 g_Grp_rec,
2316 rlm_core_sv.k_PS_ERROR);
2317 -- Bug#: 2771756 - Start
2318 -- Bug: 4198330 added grouping info
2319 rlm_message_sv.removeMessages(
2320 p_header_id => v_header_id,
2321 p_message => 'RLM_RSO_CREATION_INFO',
2322 p_message_type => 'I',
2323 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2324 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2325 p_customer_item_id => g_Grp_rec.customer_item_id,
2326 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2327 -- Bug#: 2771756 - End
2328
2329 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2330 itemtype, itemkey,TO_CHAR(actid),funcmode);
2331 --
2332 resultout := 'COMPLETE:FAILURE';
2333 COMMIT;
2334 --
2335 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2336 --
2337 IF (l_debug <> -1) THEN
2338 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in e_RDFailed');
2339 END IF;
2340 --
2341 RAISE e_LockH;
2342 --
2343 END IF;
2344 --
2345 IF (l_debug <> -1) THEN
2346 rlm_core_sv.dpop(C_SDEBUG);
2347 END IF;
2348 --
2349 WHEN e_LockH THEN
2350 --
2351 IF (l_debug <> -1) THEN
2352 rlm_core_sv.dlog(C_DEBUG, 'e_LockH exception in ProcessGroupdemand');
2353 rlm_core_sv.dpop(C_SDEBUG);
2354 END IF;
2355 --
2356 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2357 g_Sch_rec.schedule_header_id,
2358 g_Grp_rec,
2359 rlm_core_sv.k_PS_ERROR,
2360 'ALL');
2361 --
2362 rlm_message_sv.app_error(
2363 x_ExceptionLevel => rlm_message_sv.k_error_level,
2364 x_MessageName => 'RLM_HEADER_LOCK_NOT_OBTAINED',
2365 x_InterfaceHeaderId => v_header_id,
2366 x_InterfaceLineId => NULL,
2367 x_OrderLineId => NULL,
2368 x_Token1 => 'SCHED_REF',
2369 x_Value1 => rlm_core_sv.get_schedule_reference_num(v_header_id));
2370 --
2371 COMMIT;
2372 resultout := 'COMPLETE:FAILURE';
2373 --
2374 WHEN OTHERS THEN
2375
2376 -- Bug 2771756 : Added the rollback statement.
2377 IF (l_debug <> -1) THEN
2378 rlm_core_sv.dlog(C_DEBUG,'ProcessGroupDemand when others',
2379 SUBSTR(SQLERRM,1,1500));
2380 END IF;
2381 --
2382 ROLLBACK TO s_child_process;
2383 --
2384 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2385 g_Sch_rec.schedule_header_id,
2386 g_Grp_rec,
2387 rlm_core_sv.k_PS_ERROR);
2388
2389 -- Bug#: 2771756 - Start
2390 -- Bug: 4198330 added grouping info
2391 rlm_message_sv.removeMessages(
2392 p_header_id => v_header_id,
2393 p_message => 'RLM_RSO_CREATION_INFO',
2394 p_message_type => 'I',
2395 p_ship_from_org_id => g_Grp_rec.ship_from_org_id,
2396 p_ship_to_address_id => g_Grp_rec.ship_to_address_id,
2397 p_customer_item_id => g_Grp_rec.customer_item_id,
2398 p_inventory_item_id => g_Grp_rec.inventory_item_id);
2399 -- Bug#: 2771756 - End
2400
2401 wf_core.context('RLM_WF_SV','ProcessGroupDemand',
2402 itemtype, itemkey,TO_CHAR(actid),funcmode);
2403 resultout := 'COMPLETE:FAILURE';
2404 COMMIT;
2405 --
2406 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2407 --
2408 IF (l_debug <> -1) THEN
2409 rlm_core_sv.dlog(C_DEBUG, 'Unable to lock header in when others');
2410 rlm_core_sv.dpop(C_SDEBUG);
2411 END IF;
2412 --
2413 RAISE e_LockH;
2414 --
2415 END IF;
2416 --
2417 IF (l_debug <> -1) THEN
2418 rlm_core_sv.dpop(C_SDEBUG);
2419 END IF;
2420 --
2421 --RAISE;
2422
2423 END ProcessGroupDemand;
2424
2425
2426 PROCEDURE ArchiveDemand(
2427 itemtype IN VARCHAR2,
2428 itemkey IN VARCHAR2,
2429 actid IN NUMBER,
2430 funcmode IN VARCHAR2,
2431 resultout OUT NOCOPY VARCHAR2)
2432 IS
2433 --
2434 v_status NUMBER;
2435 v_schedulePS NUMBER;
2436 retcode NUMBER;
2437 errbuf VARCHAR2(2000);
2438 v_Progress VARCHAR2(3) := '020';
2439 v_header_id NUMBER;
2440 e_DPFailed EXCEPTION;
2441 e_ConfirmationSchedule EXCEPTION;
2442 --
2443
2444 BEGIN
2445 --
2446 IF (l_debug <> -1) THEN
2447 rlm_core_sv.dpush(C_SDEBUG,'ArchiveDemand');
2448 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2449 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2450 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2451 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2452 END IF;
2453 --
2454 IF (FUNCMODE = 'RUN') THEN
2455 -- Executable Statements
2456 --
2457 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2458 --
2459 IF (l_debug <> -1) THEN
2460 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2461 rlm_core_sv.dlog(C_DEBUG,'Before Archive_Demand');
2462 END IF;
2463 --
2464 RLM_TPA_SV.PostValidation;
2465 --
2466 IF RLM_VALIDATEDEMAND_SV.g_header_rec.process_status =
2467 rlm_core_sv.k_PS_ERROR THEN
2468 --
2469 resultout := 'COMPLETE:ABT';
2470 --
2471 IF (l_debug <> -1) THEN
2472 rlm_core_sv.dlog(C_DEBUG,'resultout',resultout);
2473 END IF;
2474 --
2475 ELSE
2476 --
2477 resultout := 'COMPLETE:CONT';
2478 --
2479 IF (l_debug <> -1) THEN
2480 rlm_core_sv.dlog(C_DEBUG,'resultout',resultout);
2481 END IF;
2482 --
2483 END IF;
2484 --
2485 IF (l_debug <> -1) THEN
2486 rlm_core_sv.dpop(C_SDEBUG);
2487 END IF;
2488 --
2489 RETURN;
2490 END IF;
2491
2492 IF (FUNCMODE = 'CANCEL') THEN
2493 -- Executable Statements
2494 resultout := 'COMPLETE';
2495 RETURN;
2496 END IF;
2497
2498 IF (FUNCMODE = 'RESPOND') THEN
2499 -- Executable Statements
2500 resultout := 'COMPLETE';
2501 RETURN;
2502 END IF;
2503
2504 IF (FUNCMODE = 'FORWARD') THEN
2505 -- Executable Statements
2506 resultout := 'COMPLETE';
2507 RETURN;
2508 END IF;
2509
2510 IF (FUNCMODE = 'TRANSFER') THEN
2511 -- Executable Statements
2512 resultout := 'COMPLETE';
2513 RETURN;
2514 END IF;
2515
2516 IF (FUNCMODE = 'TIMEOUT') THEN
2517 -- Executable Statements
2518 resultout := 'COMPLETE';
2519 RETURN;
2520 END IF;
2521
2522 IF (FUNCMODE = 'CANCEL') THEN
2523 -- Executable Statements
2524 resultout := 'COMPLETE';
2525 RETURN;
2526 END IF;
2527
2528 EXCEPTION
2529 WHEN NO_DATA_FOUND THEN
2530 --
2531 IF (l_debug <> -1) THEN
2532 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2533 END IF;
2534 --
2535 wf_core.context('RLM_WF_SV','ArchiveDemand',
2536 itemtype, itemkey,TO_CHAR(actid),funcmode);
2537 resultout := 'COMPLETE:ABT';
2538 --
2539 IF (l_debug <> -1) THEN
2540 rlm_core_sv.dpop(C_SDEBUG);
2541 END IF;
2542 --
2543 WHEN OTHERS THEN
2544 --
2545 IF (l_debug <> -1) THEN
2546 rlm_core_sv.dlog(C_DEBUG,'ArchiveDemand when others',
2547 SUBSTR(SQLERRM,1,1500));
2548 END IF;
2549 --
2550 rlm_dp_sv.UpdateGroupPS(v_header_id,
2551 g_Sch_rec.schedule_header_id,
2552 g_Grp_rec,
2553 rlm_core_sv.k_PS_ERROR,
2554 'ALL');
2555 --
2556 rlm_dp_sv.UpdateHeaderPS(v_header_id,
2557 g_Sch_rec.schedule_header_id);
2558 --
2559 rlm_message_sv.sql_error('rlm_wf_sv.ArchiveDemand', v_Progress);
2560 --
2561 rlm_message_sv.dump_messages(v_header_id);
2562 rlm_message_sv.initialize_messages;
2563 --
2564 COMMIT;
2565 --
2566 IF (l_debug <> -1) THEN
2567 rlm_core_sv.dpop(C_SDEBUG);
2568 END IF;
2569 --
2570 wf_core.context('RLM_WF_SV','ArchiveDemand',
2571 itemtype, itemkey,TO_CHAR(actid),funcmode);
2572 resultout := 'COMPLETE:ABT';
2573 --
2574
2575 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2576
2577 END ArchiveDemand;
2578
2579 /* Bug 2554058: Added the following procedure */
2580
2581 PROCEDURE Testschedule(
2582 itemtype IN VARCHAR2,
2583 itemkey IN VARCHAR2,
2584 actid IN NUMBER,
2585 funcmode IN VARCHAR2,
2586 resultout OUT NOCOPY VARCHAR2)
2587 IS
2588 v_header_id NUMBER;
2589 e_wftestschedule EXCEPTION;
2590
2591 BEGIN
2592 --
2593 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2594
2595 IF RLM_DP_SV.edi_test_indicator = 'T' then
2596 --
2597 rlm_core_sv.dlog(C_DEBUG,'Test schedule found');
2598 raise e_wftestschedule;
2599 --
2600 ELSE
2601 --
2602 resultout := 'F';
2603 --
2604 END IF;
2605 --
2606 EXCEPTION
2607 WHEN e_wftestschedule THEN
2608 --
2609 rlm_message_sv.app_error(
2610 x_ExceptionLevel => rlm_message_sv.k_warn_level,
2611 x_MessageName => 'RLM_TEST_SCHEDULE_DETECTED',
2612 x_InterfaceHeaderId => v_header_id,
2613 x_InterfaceLineId => NULL,
2614 x_OrderLineId => NULL,
2615 x_Token1 => 'SCHED_REF',
2616 x_Value1 =>rlm_core_sv.get_schedule_reference_num(v_header_id));
2617
2618 resultout := 'T';
2619 COMMIT;
2620 --
2621 END Testschedule;
2622
2623
2624 PROCEDURE CallProcessGroup(
2625 itemtype IN VARCHAR2,
2626 itemkey IN VARCHAR2,
2627 actid IN NUMBER,
2628 funcmode IN VARCHAR2,
2629 resultout OUT NOCOPY VARCHAR2)
2630 IS
2631 --
2632 v_header_id NUMBER;
2633 v_tmp_status NUMBER;
2634 v_count NUMBER;
2635 retcode NUMBER;
2636 errbuf VARCHAR2(2000);
2637 v_num_child NUMBER;
2638 v_child_req_id rlm_dp_sv.g_request_tbl;
2639
2640 -- 4299804: Added min_start_date_time and ship_to_customer_id to the
2641 -- select stmt.
2642
2643 CURSOR c_group_cur (v_hdr_id IN VARCHAR2) IS
2644 SELECT rih.customer_id,
2645 ril.ship_from_org_id,
2646 ril.ship_to_address_id,
2647 ril.ship_to_site_use_id,
2648 ril.ship_to_org_id,
2649 ril.customer_item_id,
2650 ril.inventory_item_id,
2651 ril.industry_attribute15, --Bugfix 12947017
2652 ril.intrmd_ship_to_id, --Bugfix 5911991
2653 ril.intmed_ship_to_org_id, --Bugfix 5911991
2654 ril.order_header_id,
2655 ril.blanket_number,
2656 min(ril.start_date_time),
2657 ril.ship_to_customer_id
2658 FROM rlm_interface_headers rih,
2659 rlm_interface_lines_all ril
2660 WHERE ril.header_id = v_hdr_id
2661 AND ril.org_id = rih.org_id
2662 AND ril.header_id = rih.header_id
2663 AND ril.process_status in ( rlm_core_sv.k_PS_AVAILABLE,
2664 rlm_core_sv.k_PS_PARTIAL_PROCESSED)
2665 GROUP BY rih.customer_id,
2666 ril.ship_from_org_id,
2667 ril.ship_to_address_id,
2668 ril.ship_to_site_use_id,
2669 ril.ship_to_org_id,
2670 ril.customer_item_id,
2671 ril.inventory_item_id,
2672 ril.industry_attribute15,
2673 ril.intrmd_ship_to_id, --Bugfix 5911991
2674 ril.intmed_ship_to_org_id, --Bugfix 5911991
2675 ril.order_header_id,
2676 ril.blanket_number,
2677 ril.ship_to_customer_id
2678 ORDER BY min(ril.start_date_time),
2679 ril.ship_to_address_id,
2680 ril.customer_item_id;
2681 --
2682
2683 BEGIN
2684 --
2685 IF (l_debug <> -1) THEN
2686 rlm_core_sv.dpush(C_SDEBUG,'CallProcessGroup');
2687 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2688 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2689 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2690 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2691 END IF;
2692 --
2693 IF (FUNCMODE = 'RUN') THEN
2694 -- Executable Statements
2695 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2696 --
2697 IF (l_debug <> -1) THEN
2698 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2699 END IF;
2700 --
2701 SELECT process_status
2702 INTO v_tmp_status
2703 FROM rlm_interface_headers
2704 WHERE header_id = v_Header_Id;
2705 --
2706 IF (l_debug <> -1) THEN
2707 rlm_core_sv.dlog(C_DEBUG,'process status =', v_tmp_status);
2708 END IF;
2709 --
2710
2711 --
2712 IF v_tmp_status <> rlm_core_sv.k_PROC_ERROR THEN
2713 --
2714 v_num_child := g_num_child;
2715 --
2716 IF v_num_child > 1 THEN /* Parallel DSP */
2717 --
2718 /* submit concurrent program requests*/
2719
2720 rlm_dp_sv.CreateChildGroups (v_header_id,
2721 v_num_child);
2722 --
2723 IF NOT RLM_DP_SV.LockHeader(v_header_id, g_Sch_rec) THEN
2724 --
2725 IF (l_debug <> -1) THEN
2726 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header after CreateChildGroups');
2727 END IF;
2728 --
2729 RAISE e_LockH;
2730 --
2731 END IF;
2732 --
2733 IF (v_num_child > 1) THEN /*Parallelize if more than 1 group found */
2734
2735 --
2736 rlm_dp_sv.SubmitChildRequests(v_header_id,
2737 v_num_child,
2738 v_child_req_id);
2739 --
2740 rlm_dp_sv.ProcessChildRequests(v_header_id,
2741 v_child_req_id);
2742
2743
2744
2745 v_child_req_id.delete;
2746 --
2747 ELSE
2748 --
2749 rlm_dp_sv.ProcessGroups (g_Sch_rec,
2750 v_header_id,
2751 1, rlm_dp_sv.k_PARALLEL_DSP);
2752 --
2753 END IF;
2754 --
2755 ELSE /*sequencial processing*/
2756 --
2757 v_count := 1;
2758 --
2759 OPEN c_group_cur(v_header_id);
2760 --
2761 LOOP
2762 --
2763 BEGIN
2764 --
2765 -- 4299804: Added min_start_date_time and
2766 -- ship_to_customer_id to the fetch stmt.
2767
2768 FETCH c_group_cur INTO
2769 g_Grp_rec.customer_id,
2770 g_Grp_rec.ship_from_org_id,
2771 g_Grp_rec.ship_to_address_id,
2772 g_Grp_rec.ship_to_site_use_id,
2773 g_Grp_rec.ship_to_org_id,
2774 g_Grp_rec.customer_item_id,
2775 g_Grp_rec.inventory_item_id,
2776 g_Grp_rec.industry_attribute15,
2777 g_Grp_rec.intrmd_ship_to_id, --Bugfix 5911991
2778 g_Grp_rec.intmed_ship_to_org_id, --Bugfix 5911991
2779 g_Grp_rec.order_header_id,
2780 g_Grp_rec.blanket_number,
2781 g_Grp_rec.min_start_date_time,
2782 g_Grp_rec.ship_to_customer_id;
2783
2784 --
2785 EXIT WHEN c_group_cur%NOTFOUND;
2786 --
2787 -- Setting the global vars
2788 IF (l_debug <> -1) THEN
2789 rlm_core_sv.dlog(C_DEBUG,'Create loop',v_count);
2790 END IF;
2791 --
2792 IF v_count > 1 THEN
2793 --
2794 IF NOT rlm_dp_sv.LockHeader(v_header_id, g_Sch_rec) THEN
2795 --
2796 IF (l_debug <> -1) THEN
2797 rlm_core_sv.dlog(C_DEBUG, 'Cannot lock header');
2798 END IF;
2799 --
2800 resultout := 'COMPLETE:FAILURE';
2801 RAISE e_LockH;
2802 --
2803 END IF;
2804 END IF;
2805 --
2806 rlm_wf_sv.CreateDSPLoop(errbuf,
2807 retcode,
2808 v_header_id,
2809 v_count);
2810 --
2811 IF (l_debug <> -1) THEN
2812 rlm_core_sv.dlog(C_DEBUG,'start loop',v_count);
2813 rlm_core_sv.dlog(C_DEBUG, '***** Processing new group *****');
2814 rlm_core_sv.dlog(C_DEBUG, 'Blanket Number', g_Grp_rec.blanket_number);
2815 END IF;
2816 --
2817 rlm_wf_sv.StartDSPLoop(errbuf,
2818 retcode,
2819 v_header_id,
2820 v_count,
2821 g_Sch_rec,
2822 g_Grp_rec);
2823 --
2824 v_count:= v_count+1;
2825 --
2826 END;
2827 --
2828 END LOOP;
2829 --
2830 CLOSE c_group_cur;
2831 --
2832 END IF;
2833 --
2834 resultout := 'COMPLETE:SUCCESS';
2835 --
2836 ELSE
2837 --
2838 resultout := 'COMPLETE:FAILURE';
2839 --
2840 END IF;
2841 --
2842 IF (l_debug <> -1) THEN
2843 rlm_core_sv.dpop(C_SDEBUG);
2844 END IF;
2845 RETURN;
2846 END IF;
2847
2848 IF (FUNCMODE = 'CANCEL') THEN
2849 -- Executable Statements
2850 resultout := 'COMPLETE';
2851 RETURN;
2852 END IF;
2853
2854 IF (FUNCMODE = 'RESPOND') THEN
2855 -- Executable Statements
2856 resultout := 'COMPLETE';
2857 RETURN;
2858 END IF;
2859
2860 IF (FUNCMODE = 'FORWARD') THEN
2861 -- Executable Statements
2862 resultout := 'COMPLETE';
2863 RETURN;
2864 END IF;
2865
2866 IF (FUNCMODE = 'TRANSFER') THEN
2867 -- Executable Statements
2868 resultout := 'COMPLETE';
2869 RETURN;
2870 END IF;
2871
2872 IF (FUNCMODE = 'TIMEOUT') THEN
2873 -- Executable Statements
2874 resultout := 'COMPLETE';
2875 RETURN;
2876 END IF;
2877
2878 IF (FUNCMODE = 'CANCEL') THEN
2879 -- Executable Statements
2880 resultout := 'COMPLETE';
2881 RETURN;
2882 END IF;
2883
2884 EXCEPTION
2885 WHEN NO_DATA_FOUND THEN
2886 --
2887 IF (l_debug <> -1) THEN
2888 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
2889 END IF;
2890 --
2891 wf_core.context('RLM_WF_SV','CallProcessGroup',
2892 itemtype, itemkey,TO_CHAR(actid),funcmode);
2893 resultout := 'COMPLETE:FAILURE';
2894 --
2895 IF (l_debug <> -1) THEN
2896 rlm_core_sv.dpop(C_SDEBUG);
2897 END IF;
2898 --
2899 WHEN e_LockH THEN
2900 --
2901 IF (l_debug <> -1) THEN
2902 rlm_core_sv.dlog(C_DEBUG, 'e_LockH exception in CallProcessGroup');
2903 rlm_core_sv.dpop(C_SDEBUG);
2904 END IF;
2905 --
2906 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
2907 g_Sch_rec.schedule_header_id,
2908 g_Grp_rec,
2909 rlm_core_sv.k_PS_ERROR,
2910 'ALL');
2911 --
2912 rlm_message_sv.app_error(
2913 x_ExceptionLevel => rlm_message_sv.k_error_level,
2914 x_MessageName => 'RLM_HEADER_LOCK_NOT_OBTAINED',
2915 x_InterfaceHeaderId => v_header_id,
2916 x_InterfaceLineId => NULL,
2917 x_OrderLineId => NULL,
2918 x_Token1 => 'SCHED_REF',
2919 x_Value1 => rlm_core_sv.get_schedule_reference_num(v_header_id));
2920 --
2921 COMMIT;
2922 resultout := 'COMPLETE:FAILURE';
2923 --
2924 WHEN OTHERS THEN
2925 wf_core.context('RLM_WF_SV','CallProcessGroup',
2926 itemtype, itemkey,TO_CHAR(actid),funcmode);
2927 resultout := 'COMPLETE:FAILURE';
2928 --
2929 IF (l_debug <> -1) THEN
2930 rlm_core_sv.dpop(C_SDEBUG);
2931 END IF;
2932 --
2933 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2934
2935 END CallProcessGroup;
2936
2937
2938
2939 PROCEDURE PostValidate(
2940 itemtype IN VARCHAR2,
2941 itemkey IN VARCHAR2,
2942 actid IN NUMBER,
2943 funcmode IN VARCHAR2,
2944 resultout OUT NOCOPY VARCHAR2)
2945 IS
2946 --
2947 v_header_id NUMBER;
2948 retcode NUMBER;
2949 errbuf VARCHAR2(2000);
2950 v_Progress VARCHAR2(3) := '030';
2951 e_ConfirmationSchedule EXCEPTION;
2952 v_replace_status BOOLEAN DEFAULT FALSE;
2953 e_ReplaceSchedule EXCEPTION;
2954 --
2955
2956 BEGIN
2957 --
2958 IF (l_debug <> -1) THEN
2959 rlm_core_sv.dpush(C_SDEBUG,'PostValidate');
2960 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
2961 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
2962 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
2963 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
2964 END IF;
2965 --
2966 IF (FUNCMODE = 'RUN') THEN
2967 -- Executable Statements
2968 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
2969 --
2970 IF (l_debug <> -1) THEN
2971 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
2972 END IF;
2973 --
2974 -- Lock the headers and Populate g_Sch_rec
2975 IF NOT rlm_dp_sv.LockHeader(v_header_Id, g_Sch_rec) THEN
2976 --
2977 IF (l_debug <> -1) THEN
2978 rlm_core_sv.dlog(C_DEBUG,'header not locked');
2979 END IF;
2980 --
2981 raise e_LockH;
2982 --
2983 END IF;
2984 --
2985 IF g_Sch_rec.schedule_purpose = rlm_dp_sv.k_CONFIRMATION THEN
2986 --
2987 IF (l_debug <> -1) THEN
2988 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
2989 END IF;
2990 --
2991 raise e_ConfirmationSchedule;
2992 --
2993 END IF;
2994 --
2995 --
2996 -- Call Sweeper Program here
2997 -- (Enhancement bug# 1062039)
2998 --
2999
3000 SELECT hsecs INTO l_comp_start_time FROM v$timer;
3001
3002 RLM_REPLACE_SV.CompareReplaceSched(g_Sch_rec,
3003 RLM_DP_SV.g_warn_replace_schedule,
3004 v_replace_status);
3005
3006 --
3007 SELECT hsecs INTO l_comp_end_time FROM v$timer;
3008
3009
3010 IF v_replace_status = FALSE THEN
3011 --
3012 RAISE e_ReplaceSchedule;
3013 --
3014 END IF;
3015 --
3016 resultout := 'COMPLETE:SUCCESS';
3017 --
3018 IF (l_debug <> -1) THEN
3019 rlm_core_sv.dpop(C_SDEBUG);
3020 END IF;
3021 --
3022 RETURN;
3023 END IF;
3024
3025 IF (FUNCMODE = 'CANCEL') THEN
3026 -- Executable Statements
3027 resultout := 'COMPLETE';
3028 RETURN;
3029 END IF;
3030
3031 IF (FUNCMODE = 'RESPOND') THEN
3032 -- Executable Statements
3033 resultout := 'COMPLETE';
3034 RETURN;
3035 END IF;
3036
3037 IF (FUNCMODE = 'FORWARD') THEN
3038 -- Executable Statements
3039 resultout := 'COMPLETE';
3040 RETURN;
3041 END IF;
3042
3043 IF (FUNCMODE = 'TRANSFER') THEN
3044 -- Executable Statements
3045 resultout := 'COMPLETE';
3046 RETURN;
3047 END IF;
3048
3049 IF (FUNCMODE = 'TIMEOUT') THEN
3050 -- Executable Statements
3051 resultout := 'COMPLETE';
3052 RETURN;
3053 END IF;
3054
3055 IF (FUNCMODE = 'CANCEL') THEN
3056 -- Executable Statements
3057 resultout := 'COMPLETE';
3058 RETURN;
3059 END IF;
3060
3061 EXCEPTION
3062 WHEN NO_DATA_FOUND THEN
3063 --
3064 IF (l_debug <> -1) THEN
3065 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
3066 END IF;
3067 --
3068 wf_core.context('RLM_WF_SV','PostValidate',
3069 itemtype, itemkey,TO_CHAR(actid),funcmode);
3070 resultout := 'COMPLETE:FAILURE';
3071 --
3072 IF (l_debug <> -1) THEN
3073 rlm_core_sv.dpop(C_SDEBUG);
3074 END IF;
3075 --
3076 WHEN e_LockH THEN
3077 --
3078 IF (l_debug <> -1) THEN
3079 rlm_core_sv.dlog(C_DEBUG,'PostValidate failed Lock header');
3080 END IF;
3081 --
3082 rlm_dp_sv.UpdateGroupPS(v_header_id,
3083 g_Sch_rec.schedule_header_id,
3084 g_Grp_rec,
3085 rlm_core_sv.k_PS_ERROR,
3086 'ALL');
3087 --
3088 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3089 g_Sch_rec.schedule_header_id);
3090 --
3091 rlm_message_sv.dump_messages(v_header_id);
3092 rlm_message_sv.initialize_messages;
3093 --
3094
3095 wf_core.context('RLM_WF_SV','PostValidate',
3096 itemtype, itemkey,TO_CHAR(actid),funcmode);
3097 resultout := 'COMPLETE:FAILURE';
3098 --
3099 IF (l_debug <> -1) THEN
3100 rlm_core_sv.dpop(C_SDEBUG);
3101 END IF;
3102 --
3103 WHEN e_ConfirmationSchedule THEN
3104 --
3105 IF (l_debug <> -1) THEN
3106 rlm_core_sv.dlog(C_DEBUG,'RLM_CONF_SCH_RCD');
3107 END IF;
3108 --
3109 rlm_message_sv.app_error(
3110 x_ExceptionLevel => rlm_message_sv.k_warn_level,
3111 x_MessageName => 'RLM_CONF_SCH_RCD',
3112 x_InterfaceHeaderId => g_Sch_rec.header_id,
3113 x_InterfaceLineId => null,
3114 x_ScheduleHeaderId => g_Sch_rec.schedule_header_id,
3115 x_ScheduleLineId => NULL,
3116 x_OrderHeaderId => g_Grp_rec.setup_terms_rec.header_id,
3117 x_OrderLineId => NULL,
3118 x_Token1 => 'SCHED_REF',
3119 x_Value1 => g_Sch_rec.schedule_reference_num);
3120 --
3121 rlm_dp_sv.UpdateGroupPS(g_Sch_rec.header_id,
3122 g_Sch_rec.Schedule_header_id,
3123 g_Grp_rec,
3124 rlm_core_sv.K_PS_PROCESSED,
3125 'ALL');
3126 --
3127 rlm_dp_sv.UpdateHeaderPS(g_Sch_rec.header_id,
3128 g_Sch_rec.Schedule_header_id);
3129 --
3130 rlm_message_sv.dump_messages(v_header_id);
3131 rlm_message_sv.initialize_messages;
3132 --
3133 COMMIT;
3134 -- Bug#: 3053299 -- Setting the output
3135 resultout := 'COMPLETE:SUCCESS';
3136 --
3137 IF (l_debug <> -1) THEN
3138 rlm_core_sv.dpop(C_SDEBUG);
3139 END IF;
3140
3141 WHEN e_ReplaceSchedule THEN
3142 --
3143 wf_core.context('RLM_WF_SV','PostValidate',
3144 itemtype, itemkey,TO_CHAR(actid),funcmode);
3145 resultout := 'COMPLETE:FAILURE';
3146 --
3147 rlm_dp_sv.UpdateGroupPS(v_header_id,
3148 g_Sch_rec.schedule_header_id,
3149 g_Grp_rec,
3150 rlm_core_sv.k_PS_ERROR,
3151 'ALL');
3152 --
3153 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3154 g_Sch_rec.schedule_header_id);
3155 --
3156 -- Bug 2930695: Frontport bug 2912996
3157 -- rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
3158 --
3159 rlm_message_sv.dump_messages(v_header_id);
3160 rlm_message_sv.initialize_messages;
3161 --
3162 COMMIT;
3163 --
3164 IF (l_debug <> -1) THEN
3165 rlm_core_sv.dpop(C_SDEBUG);
3166 END IF;
3167
3168 WHEN OTHERS THEN
3169 wf_core.context('RLM_WF_SV','PostValidate',
3170 itemtype, itemkey,TO_CHAR(actid),funcmode);
3171 resultout := 'COMPLETE:FAILURE';
3172 --
3173 rlm_dp_sv.UpdateGroupPS(v_header_id,
3174 g_Sch_rec.schedule_header_id,
3175 g_Grp_rec,
3176 rlm_core_sv.k_PS_ERROR,
3177 'ALL');
3178 --
3179 rlm_dp_sv.UpdateHeaderPS(v_header_id,
3180 g_Sch_rec.schedule_header_id);
3181 --
3182 rlm_message_sv.sql_error('rlm_dp_sv.DemandProcessor', v_Progress);
3183 --
3184 rlm_message_sv.dump_messages(v_header_id);
3185 rlm_message_sv.initialize_messages;
3186 --
3187 COMMIT;
3188 --
3189 IF (l_debug <> -1) THEN
3190 rlm_core_sv.dpop(C_SDEBUG);
3191 END IF;
3192 --RAISE ;
3193
3194 END PostValidate;
3195
3196
3197 PROCEDURE CHeckStatus(
3198 itemtype IN VARCHAR2,
3199 itemkey IN VARCHAR2,
3200 actid IN NUMBER,
3201 funcmode IN VARCHAR2,
3202 resultout OUT NOCOPY VARCHAR2)
3203 IS
3204 --
3205 v_header_id NUMBER;
3206 v_request_id NUMBER;
3207 v_schedule_num VARCHAR2(50);
3208 x_errors NUMBER := -1;
3209 x_real_errors NUMBER := -1;
3210 v_status NUMBER;
3211 e_DPFailed EXCEPTION;
3212 BEGIN
3213 --
3214 IF (l_debug <> -1) THEN
3215 rlm_core_sv.dpush(C_SDEBUG,'CHeckStatus');
3216 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
3217 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
3218 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
3219 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
3220 END IF;
3221 --
3222 IF (FUNCMODE = 'RUN') THEN
3223 -- Executable Statements
3224 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
3225 --
3226 IF (l_debug <> -1) THEN
3227 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
3228 END IF;
3229 --
3230 v_request_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'REQUEST_ID');
3231 --
3232 IF (l_debug <> -1) THEN
3233 rlm_core_sv.dlog(C_DEBUG,' Request Id :', v_request_id);
3234 END IF;
3235 --
3236 v_schedule_num := wf_engine.GetItemAttrText(itemtype,itemkey,'SCHEDULE_NUMBER');
3237 --
3238 IF (l_debug <> -1) THEN
3239 rlm_core_sv.dlog(C_DEBUG,' Schedule Number :', v_schedule_num);
3240 END IF;
3241 --
3242 rlm_message_sv.dump_messages(v_header_id);
3243 rlm_message_sv.initialize_messages;
3244
3245 -- Check for Errors
3246
3247 wf_engine.SetItemAttrNumber( itemtype,
3248 itemkey,
3249 'ERRORS_EXIST',
3250 g_PROC_ERROR); ---Errors Exist
3251
3252 resultout := 'COMPLETE:ERR';
3253 --
3254 IF (l_debug <> -1) THEN
3255 rlm_core_sv.dpop(C_SDEBUG);
3256 END IF;
3257 --
3258 RETURN;
3259
3260 END IF;
3261
3262 IF (FUNCMODE = 'CANCEL') THEN
3263 -- Executable Statements
3264 resultout := 'COMPLETE';
3265 RETURN;
3266 END IF;
3267
3268 IF (FUNCMODE = 'RESPOND') THEN
3269 -- Executable Statements
3270 resultout := 'COMPLETE';
3271 RETURN;
3272 END IF;
3273
3274 IF (FUNCMODE = 'FORWARD') THEN
3275 -- Executable Statements
3276 resultout := 'COMPLETE';
3277 RETURN;
3278 END IF;
3279
3280 IF (FUNCMODE = 'TRANSFER') THEN
3281 -- Executable Statements
3282 resultout := 'COMPLETE';
3283 RETURN;
3284 END IF;
3285
3286 IF (FUNCMODE = 'TIMEOUT') THEN
3287 -- Executable Statements
3288 resultout := 'COMPLETE';
3289 RETURN;
3290 END IF;
3291
3292 IF (FUNCMODE = 'CANCEL') THEN
3293 -- Executable Statements
3294 resultout := 'COMPLETE';
3295 RETURN;
3296 END IF;
3297
3298 EXCEPTION
3299 WHEN NO_DATA_FOUND THEN
3300 --
3301 IF (l_debug <> -1) THEN
3302 rlm_core_sv.dlog(C_DEBUG,'NO data found ');
3303 END IF;
3304 --
3305 wf_core.context('RLM_WF_SV','CHeckStatus',
3306 itemtype, itemkey,TO_CHAR(actid),funcmode);
3307 resultout := 'COMPLETE:N';
3308 --
3309 IF (l_debug <> -1) THEN
3310 rlm_core_sv.dpop(C_SDEBUG);
3311 END IF;
3312 --
3313 WHEN OTHERS THEN
3314 wf_core.context('RLM_WF_SV','CHeckStatus',
3315 itemtype, itemkey,TO_CHAR(actid),funcmode);
3316 resultout := 'COMPLETE:N';
3317 --
3318 IF (l_debug <> -1) THEN
3319 rlm_core_sv.dpop(C_SDEBUG);
3320 END IF;
3321 --
3322 RAISE;
3323
3324 END CHeckStatus;
3325
3326 -- Bug#: 3053299 - Start of the functions
3327
3328 /*=============================================================================
3329
3330 FUNCTION NAME: Notification_ToUser
3331
3332 DESCRIPTION: This function returns the To User to whom the notifications
3333 are to be send. The To User is set as an attribute in the Work
3334 Flow. The To User is retrieved from the FND_USER table.
3335
3336 PARAMETERS: user_id IN NUMBER
3337
3338 RETURN: VARCHAR2
3339
3340 ============================================================================*/
3341
3342 FUNCTION Notification_ToUser(user_id IN NUMBER) RETURN VARCHAR2 is
3343 --
3344 CURSOR c_user(v_user_id IN NUMBER) is
3345 SELECT user_name
3346 FROM fnd_user
3347 WHERE user_id = v_user_id;
3348 --
3349 v_ToUserName fnd_user.user_name%TYPE;
3350 e_UserNotFound EXCEPTION;
3351 --
3352 BEGIN
3353 --
3354 IF (l_debug <> -1) THEN
3355 rlm_core_sv.dpush(C_SDEBUG, 'Notification_ToUser');
3356 rlm_core_sv.dlog(C_DEBUG, 'User Id :', user_id);
3357 END IF;
3358 --
3359 OPEN c_user(user_id);
3360 FETCH c_user INTO v_ToUserName;
3361 --
3362 IF (c_user%NOTFOUND) THEN
3363 --
3364 IF (l_debug <> -1) THEN
3365 rlm_core_sv.dlog(C_DEBUG,'Notification_ToUser :: No Data Found');
3366 END IF;
3367 --
3368 RAISE e_UserNotFound;
3369 --
3370 END IF;
3371 --
3372 CLOSE c_user;
3373 --
3374 IF (l_debug <> -1) THEN
3375 rlm_core_sv.dlog(C_DEBUG, 'To User', v_ToUserName);
3376 rlm_core_sv.dpop(C_SDEBUG);
3377 END IF;
3378 --
3379 RETURN v_ToUserName;
3380 --
3381 EXCEPTION
3382 --
3383 WHEN e_UserNotFound THEN
3384 --
3385 rlm_message_sv.sql_error('RLM_WF_SV.Notification_ToUser',user_id);
3386 --
3387 IF (l_debug <> -1) THEN
3388 rlm_core_sv.dlog(C_DEBUG, 'Exception : e_UserNotFound');
3389 rlm_core_sv.dpop(C_SDEBUG);
3390 END IF;
3391 --
3392 RAISE;
3393 --
3394 END Notification_ToUser;
3395
3396 /*=============================================================================
3397
3398 FUNCTION NAME: Notification_FromUser
3399
3400 DESCRIPTION: This function returns the From User from whom the notifications
3401 are sent. The From User is set as an attribute in the Work
3402 Flow item. We call an FND API in order to obtain the
3403 internal responsibility-based role name for a given
3404 responsibility.
3405 Modifications made as a part of bug 3764527.
3406
3407 PARAMETERS: responsibility_id IN NUMBER
3408 resp_appl_id IN NUMBER
3409
3410 RETURN: VARCHAR2
3411
3412 ============================================================================*/
3413
3414 FUNCTION Notification_FromUser(responsibility_id NUMBER,
3415 resp_appl_id NUMBER) RETURN VARCHAR2 is
3416 --
3417 /*
3418 * Bug 3680168 : Do not need this cursor anymore
3419 * We now call an FND API to get the role name
3420 *
3421 CURSOR c_user(v_resp_id IN NUMBER, v_appl_id IN NUMBER) is
3422 SELECT display_name
3423 FROM wf_roles
3424 WHERE name =
3425 (
3426 SELECT 'FND_RESP' || r.application_id || ':' || r.responsibility_id
3427 FROM fnd_responsibility_vl r
3428 WHERE r.responsibility_id = v_resp_id
3429 AND r.application_id = v_appl_id);
3430 */
3431 --
3432 v_FromUserName wf_roles.name%type;
3433 --
3434 BEGIN
3435 --
3436 IF (l_debug <> -1) THEN
3437 rlm_core_sv.dpush(C_SDEBUG, 'Notification_FromUser');
3438 rlm_core_sv.dlog(C_DEBUG, 'Responsibility Id :', responsibility_id);
3439 rlm_core_sv.dlog(C_DEBUG, 'Application Id :', resp_appl_id);
3440 END IF;
3441 --
3442 v_FromUserName := FND_USER_RESP_GROUPS_API. upgrade_resp_role
3443 (respid => responsibility_id,
3444 appid => resp_appl_id);
3445 --
3446 IF (l_debug <> -1) THEN
3447 rlm_core_sv.dlog(C_DEBUG, 'Internal Role Name', v_FromUserName);
3448 rlm_core_sv.dpop(C_SDEBUG);
3449 END IF;
3450 --
3451 RETURN v_FromUserName;
3452 --
3453 EXCEPTION
3454 --
3455 WHEN OTHERS THEN
3456 --
3457 rlm_message_sv.sql_error('RLM_WF_SV.Notification_FromUser',
3458 SUBSTRB(SQLERRM, 1, 200));
3459 --
3460 IF (l_debug <> -1) THEN
3461 rlm_core_sv.dlog(C_DEBUG, 'When Others - ' || SUBSTRB(SQLERRM, 1, 200));
3462 rlm_core_sv.dpop(C_SDEBUG);
3463 END IF;
3464 --
3465 RAISE;
3466 --
3467 END Notification_FromUser;
3468
3469 -- Bug#: 3053299 - End of the functions
3470
3471 -- Bug#: 3291401 - Start
3472 /*=============================================================================
3473
3474 Procedure NAME: GetScheduleStatus
3475
3476 DESCRIPTION: This procedure checks for the schedule status.
3477
3478 PARAMETERS: itemtype IN VARCHAR2,
3479 itemkey IN VARCHAR2,
3480 actid IN NUMBER,
3481 funcmode IN VARCHAR2,
3482 resultout OUT NOCOPY VARCHAR2`
3483
3484 ============================================================================*/
3485
3486
3487 PROCEDURE GetScheduleStatus(
3488 itemtype IN VARCHAR2,
3489 itemkey IN VARCHAR2,
3490 actid IN NUMBER,
3491 funcmode IN VARCHAR2,
3492 resultout OUT NOCOPY VARCHAR2)
3493 IS
3494
3495 CURSOR c_process_status_schedule(p_header_id IN NUMBER) is
3496 SELECT process_status
3497 FROM rlm_schedule_headers
3498 WHERE header_id = p_header_id;
3499
3500 CURSOR c_process_status_interface(p_header_id IN NUMBER) is
3501 SELECT process_status
3502 FROM rlm_interface_headers
3503 WHERE header_id = p_header_id;
3504
3505 --
3506 v_header_id NUMBER;
3507 v_process_status NUMBER;
3508 BEGIN
3509 --
3510 IF (l_debug <> -1) THEN
3511 rlm_core_sv.dpush(C_SDEBUG,'GetScheduleStatus');
3512 rlm_core_sv.dlog(C_DEBUG,'itemtype ', itemtype);
3513 rlm_core_sv.dlog(C_DEBUG,'itemkey ', itemkey);
3514 rlm_core_sv.dlog(C_DEBUG,'actid ', actid);
3515 rlm_core_sv.dlog(C_DEBUG,'funcmode ', funcmode);
3516 END IF;
3517 --
3518 IF (FUNCMODE = 'RUN') THEN
3519 -- Executable Statements
3520 v_header_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
3521 --
3522 IF (l_debug <> -1) THEN
3523 rlm_core_sv.dlog(C_DEBUG,' Header_id :', v_header_id);
3524 END IF;
3525 --
3526
3527 OPEN c_process_status_schedule(g_Sch_rec.schedule_header_id); --Bug# 3567538
3528
3529 FETCH c_process_status_schedule INTO v_process_status;
3530
3531 IF (c_process_status_schedule%NOTFOUND) THEN
3532
3533 OPEN c_process_status_interface(v_header_id);
3534
3535 FETCH c_process_status_interface INTO v_process_status;
3536
3537 IF (c_process_status_interface%NOTFOUND) THEN
3538 resultout := 'COMPLETE:ERROR';
3539 END IF;
3540
3541 IF (v_process_status IS NOT NULL AND v_process_status = RLM_CORE_SV.k_PS_PARTIAL_PROCESSED) THEN
3542 resultout := 'COMPLETE:PARTIAL_PROCESS';
3543 ELSE
3544 resultout := 'COMPLETE:ERROR';
3545 END IF ;
3546
3547 CLOSE c_process_status_interface;
3548 END IF;
3549
3550 IF (v_process_status IS NOT NULL AND v_process_status = RLM_CORE_SV.k_PS_PARTIAL_PROCESSED) THEN
3551 resultout := 'COMPLETE:PARTIAL_PROCESS';
3552 ELSE
3553 resultout := 'COMPLETE:ERROR';
3554 END IF;
3555
3556
3557 CLOSE c_process_status_schedule;
3558
3559 --
3560 IF (l_debug <> -1) THEN
3561 rlm_core_sv.dpop(C_SDEBUG);
3562 END IF;
3563 --
3564 RETURN;
3565
3566 END IF;
3567
3568 IF (FUNCMODE = 'CANCEL') THEN
3569 -- Executable Statements
3570 resultout := 'COMPLETE:ERROR';
3571 RETURN;
3572 END IF;
3573
3574 IF (FUNCMODE = 'RESPOND') THEN
3575 -- Executable Statements
3576 resultout := 'COMPLETE:ERROR';
3577 RETURN;
3578 END IF;
3579
3580 IF (FUNCMODE = 'FORWARD') THEN
3581 -- Executable Statements
3582 resultout := 'COMPLETE:ERROR';
3583 RETURN;
3584 END IF;
3585
3586 IF (FUNCMODE = 'TRANSFER') THEN
3587 -- Executable Statements
3588 resultout := 'COMPLETE:ERROR';
3589 RETURN;
3590 END IF;
3591
3592 IF (FUNCMODE = 'TIMEOUT') THEN
3593 -- Executable Statements
3594 resultout := 'COMPLETE:ERROR';
3595 RETURN;
3596 END IF;
3597
3598 IF (FUNCMODE = 'CANCEL') THEN
3599 -- Executable Statements
3600 resultout := 'COMPLETE:ERROR';
3601 RETURN;
3602 END IF;
3603
3604 EXCEPTION
3605 WHEN OTHERS THEN
3606 wf_core.context('RLM_WF_SV','GetScheduleStatus',
3607 itemtype, itemkey,TO_CHAR(actid),funcmode);
3608 resultout := 'COMPLETE:ERROR';
3609 --
3610 IF (l_debug <> -1) THEN
3611 rlm_core_sv.dpop(C_SDEBUG);
3612 END IF;
3613 --
3614 RAISE;
3615
3616 END GetScheduleStatus;
3617 -- Bug#: 3291401 - End
3618
3619 END RLM_WF_SV;