[Home] [Help]
PACKAGE BODY: APPS.ZPB_DC_WF
Source
1 PACKAGE BODY ZPB_DC_WF as
2 /* $Header: ZPBDCWFB.pls 120.6 2007/12/04 14:34:27 mbhat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_DC_WF';
5 TYPE template_list_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 template_list template_list_type;
7
8 PROCEDURE generate_template (
9 itemtype IN varchar2,
10 itemkey IN varchar2,
11 actid IN number,
12 funcmode IN varchar2,
13 resultout OUT nocopy varchar2
14 )
15 IS
16 l_instance_id number := 0;
17 l_ac_id number := 0;
18 l_task_id number := 0;
19 l_req_id number := 0;
20
21 l_respapp_id number := 0;
22 l_user_id number := 0;
23 l_resp_id number := 0;
24
25 l_template_name zpb_dc_objects.template_name%TYPE;
26 l_template_id number;
27 l_wait_for_review varchar2(1);
28 l_owner varchar2(30);
29 l_issue_msg fnd_new_messages.message_text%TYPE;
30
31 BEGIN
32
33 IF (funcmode = 'RUN') THEN
34 resultout :='COMPLETE:N';
35
36 -- Retrieve values from previous wf process
37 l_ac_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
38 Itemkey => ItemKey,
39 aname => 'ACID');
40 l_task_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
41 Itemkey => ItemKey,
42 aname => 'TASKID');
43 l_instance_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
44 Itemkey => ItemKey,
45 aname => 'INSTANCEID');
46 l_resp_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
47 Itemkey => ItemKey,
48 aname => 'RESPID');
49 l_user_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
50 Itemkey => ItemKey,
51 aname => 'OWNERID');
52 l_respapp_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
53 Itemkey => ItemKey,
54 aname => 'RESPAPPID');
55
56 -- Issue encountered related
57 l_owner := fnd_global.user_name;
58
59 -- Get the short text from fnd messages
60 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_GEN_TEMP_ISSUE_MSG');
61 l_issue_msg := FND_MESSAGE.GET;
62
63 wf_engine.SetItemAttrText(
64 Itemtype => ItemType,
65 Itemkey => ItemKey,
66 aname => 'EPBPERFORMER',
67 avalue => l_owner);
68
69 wf_engine.SetItemAttrText(
70 Itemtype => ItemType,
71 Itemkey => ItemKey,
72 aname => 'ISSUEMSG',
73 avalue => l_issue_msg);
74
75 -- Run the CP
76 fnd_global.apps_initialize(l_user_id,l_resp_id,l_respapp_id);
77
78 -- Set attributes
79 SELECT value
80 INTO l_template_name
81 FROM zpb_task_parameters
82 WHERE task_id = l_task_id
83 AND name = 'TEMPLATE_NAME';
84
85 wf_engine.SetItemAttrText(
86 Itemtype => ItemType,
87 Itemkey => ItemKey,
88 aname => 'DC_TEMPLATE_NAME',
89 avalue => l_template_name);
90
91 SELECT value
92 INTO l_wait_for_review
93 FROM zpb_task_parameters
94 WHERE task_id = l_task_id
95 AND name = 'TEMPLATE_WAIT_FOR_REVIEW';
96
97 wf_engine.SetItemAttrText(
98 Itemtype => ItemType,
99 Itemkey => ItemKey,
100 aname => 'DC_WAIT_FOR_REVIEW',
101 avalue => l_wait_for_review);
102
103 resultout := 'COMPLETE:Y';
104 END IF;
105
106 IF ( funcmode = 'CANCEL' ) THEN
107 resultout := 'COMPLETE:Y';
108 END IF;
109
110 IF (funcmode not in ('RUN','CANCEL')) THEN
111 resultout := '';
112 END IF;
113 return;
114
115 EXCEPTION
116
117 WHEN OTHERS THEN
118 WF_CORE.CONTEXT('zpb_dc_wf.generate_template', itemtype, itemkey, to_char(actid), funcmode);
119 raise;
120 END generate_template;
121
122 PROCEDURE get_review_option (
123 itemtype IN varchar2,
124 itemkey IN varchar2,
125 actid IN number,
126 funcmode IN varchar2,
127 resultout OUT nocopy varchar2
128 )
129 IS
130 l_wait_for_review varchar2(1);
131 l_notify zpb_task_parameters.value%TYPE;
132 l_task_id number;
133 l_from_name fnd_user.user_name%TYPE;
134 l_user_id number;
135 l_template_name zpb_dc_objects.template_name%TYPE;
136
137 BEGIN
138
139 IF (funcmode = 'RUN') THEN
140
141 l_wait_for_review := wf_engine.GetItemAttrText(
142 Itemtype => ItemType,
143 Itemkey => ItemKey,
144 aname => 'DC_WAIT_FOR_REVIEW');
145
146 l_task_id := wf_engine.GetItemAttrNumber(
147 Itemtype => ItemType,
148 Itemkey => ItemKey,
149 aname => 'TASKID');
150
151 -- Populate the from field in the notification details page
152 SELECT fnd.user_name
153 INTO l_from_name
154 FROM fnd_user fnd,zpb_dc_objects obj
155 WHERE fnd.user_id = obj.object_user_id
156 AND obj.generate_template_task_id = l_task_id
157 AND obj.object_type = 'M';
158
159 wf_engine.SetItemAttrText(
160 Itemtype => ItemType,
161 Itemkey => ItemKey,
162 aname => '#FROM_ROLE',
163 avalue => l_from_name);
164
165 SELECT value
166 INTO l_notify
167 FROM zpb_task_parameters
168 WHERE task_id = l_task_id
169 AND name = 'NOTIFICATION_RECIPIENT_TYPE';
170
171 IF (l_wait_for_review = 'Y') THEN
172 -- 5301285 06-JUN-27 =============================================
173 -- Will find and set shadow users if EPBPERFORMER is a single user
174 zpb_wf_ntf.SHADOWS_FOR_EPBPERFORMER (itemtype => ItemType,
175 Itemkey => ItemKey,
176 actid => 0,
177 funcmode => 'EPBPERFORMER',
178 resultout => resultout);
179
180 resultout := 'COMPLETE:WAIT' ;
181 ELSIF (l_wait_for_review = 'N' AND l_notify <> 'NONE') THEN
182 resultout := 'COMPLETE:NOTIFY' ;
183 ELSIF (l_wait_for_review = 'N' AND l_notify = 'NONE') THEN
184 resultout := 'COMPLETE:PROCEED' ;
185 END IF;
186
187 END IF;
188
189 IF ( funcmode = 'CANCEL' ) THEN
190 resultout := 'COMPLETE';
191 END IF;
192
193 IF (funcmode not in ('RUN','CANCEL')) THEN
194 resultout := '';
195 END IF;
196 return;
197
198 EXCEPTION
199
200 WHEN OTHERS THEN
201 WF_CORE.CONTEXT('zpb_dc_wf.get_review_option', itemtype, itemkey, to_char(actid), funcmode);
202 raise;
203 END get_review_option;
204
205
206 PROCEDURE review_complete(
207 itemtype IN varchar2,
208 itemkey IN varchar2,
209 actid IN number,
210 funcmode IN varchar2,
211 resultout OUT nocopy varchar2
212 )
213 IS
214 l_wait_for_review_flag varchar2(1);
215 l_review_complete_flag varchar2(1);
216 l_template_id number;
217 l_instance_id number;
218 l_task_id number;
219 BEGIN
220
221 IF (funcmode = 'RUN') THEN
222 resultout :='COMPLETE:N';
223
224 l_instance_id := wf_engine.GetItemAttrNumber(
225 Itemtype => ItemType,
226 Itemkey => ItemKey,
227 aname => 'INSTANCEID');
228 l_task_id := wf_engine.GetItemAttrNumber(
229 Itemtype => ItemType,
230 Itemkey => ItemKey,
231 aname => 'TASKID');
232
233 /* Get the template id from objects table
234 instead of task parameters table */
235 SELECT template_id
236 INTO l_template_id
237 FROM zpb_dc_objects
238 WHERE ac_instance_id = l_instance_id
239 AND object_type = 'M'
240 AND generate_template_task_id = l_task_id;
241
242 wf_engine.SetItemAttrNumber(
243 Itemtype => ItemType,
244 Itemkey => ItemKey,
245 aname => 'DC_TEMPLATE_ID',
246 avalue => l_template_id);
247
248 SELECT review_complete_flag
249 INTO l_review_complete_flag
250 FROM zpb_dc_objects
251 WHERE template_id = l_template_id
252 AND object_type = 'M';
253
254 IF l_review_complete_flag = 'Y' THEN
255 resultout :='COMPLETE:Y';
256 ELSE
257 resultout :='COMPLETE:N';
258 END IF;
259
260 END IF;
261
262 IF ( funcmode = 'CANCEL' ) THEN
263 resultout := 'COMPLETE:Y';
264 END IF;
265
266 IF (funcmode not in ('RUN','CANCEL')) THEN
267 resultout := '';
268 END IF;
269 return;
270
271 EXCEPTION
272
273 WHEN others THEN
274 WF_CORE.CONTEXT('zpb_dc_wf.review_complete', itemtype, itemkey, to_char(actid), funcmode);
275 raise;
276
277 END review_complete;
278
279
280 PROCEDURE auto_distribute (
281 itemtype IN varchar2,
282 itemkey IN varchar2,
283 actid IN number,
284 funcmode IN varchar2,
285 resultout OUT nocopy varchar2
286 )
287 IS
288 l_instance_id number := 0;
289 l_task_id number := 0;
290 l_template_id number := 0;
291 l_ac_template_id number := 0;
292 l_req_id number := 0;
293 l_object_id number;
294 l_object_user_id number;
295 l_object_user_name fnd_user.description%TYPE;
296 l_distribute_message zpb_task_parameters.value%TYPE;
297 l_template_name zpb_dc_objects.template_name%TYPE;
298 l_prior_item_key zpb_analysis_cycle_tasks.item_key%TYPE;
299 l_issue_msg fnd_new_messages.message_text%TYPE;
300
301 l_respapp_id number := 0;
302 l_user_id number := 0;
303 l_resp_id number := 0;
304
305 BEGIN
306
307 IF (funcmode = 'RUN') THEN
308 resultout :='COMPLETE:N';
309
310 l_instance_id := wf_engine.GetItemAttrNumber(
311 Itemtype => ItemType,
312 Itemkey => ItemKey,
313 aname => 'INSTANCEID');
314
315 l_task_id := wf_engine.GetItemAttrNumber(
316 Itemtype => ItemType,
317 Itemkey => ItemKey,
318 aname => 'TASKID');
319
320 -- Issue encounter msg
321 -- Get Message from Fnd_Messages
322 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_AUTO_DIST_ISSUE_MSG');
323 l_issue_msg := FND_MESSAGE.GET;
324
325 wf_engine.SetItemAttrText(
326 Itemtype => ItemType,
327 Itemkey => ItemKey,
328 aname => 'ISSUEMSG',
329 avalue => l_issue_msg);
330
331 /* To find out the specific distributed template
332 for multiple template cases */
333 SELECT value
334 INTO l_ac_template_id
335 FROM zpb_task_parameters
336 WHERE task_id = l_task_id
337 AND name = 'DISTRIBUTION_TEMPLATE_ID';
338
339 SELECT template_id, template_name
340 INTO l_template_id, l_template_name
341 FROM zpb_dc_objects
342 WHERE ac_instance_id = l_instance_id
343 AND ac_template_id = l_ac_template_id
344 AND object_type = 'M';
345
346 wf_engine.SetItemAttrText(
347 Itemtype => ItemType,
348 Itemkey => ItemKey,
349 aname => 'DC_TEMPLATE_NAME',
350 avalue => l_template_name);
351
352 wf_engine.SetItemAttrNumber(
353 Itemtype => ItemType,
354 Itemkey => ItemKey,
355 aname => 'DC_TEMPLATE_ID',
356 avalue => l_template_id);
357
358 -- Get the parameters for set the apps context
359 l_resp_id := wf_engine.GetItemAttrNumber(
360 Itemtype => ItemType,
361 Itemkey => ItemKey,
362 aname => 'RESPID');
363 l_user_id := wf_engine.GetItemAttrNumber(
364 Itemtype => ItemType,
365 Itemkey => ItemKey,
366 aname => 'OWNERID');
367 l_respapp_id := wf_engine.GetItemAttrNumber(
368 Itemtype => ItemType,
369 Itemkey => ItemKey,
370 aname => 'RESPAPPID');
371
372 -- Get the user name to populate dc distributors
373 SELECT nvl(fnd.description,fnd.user_name)
374 INTO l_object_user_name
375 FROM fnd_user fnd, zpb_dc_objects obj
376 WHERE fnd.user_id = obj.object_user_id
377 AND obj.template_id = l_template_id
378 AND obj.object_type = 'M';
379
380 wf_engine.SetItemAttrText(
381 Itemtype => ItemType,
382 Itemkey => ItemKey,
383 aname => 'DC_DISTRIBUTOR',
384 avalue => l_object_user_name);
385
386 -- Get the distribute message to populate dc distribution message
387 SELECT value
388 INTO l_distribute_message
389 FROM zpb_task_parameters
390 WHERE task_id = l_task_id
391 AND name = 'DISTRIBUTION_MESSAGE';
392
393 wf_engine.SetItemAttrText(
394 Itemtype => ItemType,
395 Itemkey => ItemKey,
396 aname => 'DC_DISTRIBUTION_MESSAGE',
397 avalue => l_distribute_message);
398
399 -- Set the context and Run the CP
400 fnd_global.apps_initialize(l_user_id,l_resp_id,l_respapp_id);
401
402 resultout := 'COMPLETE:Y';
403 END IF;
404
405 IF ( funcmode = 'CANCEL' ) THEN
406 resultout := 'COMPLETE';
407 END IF;
408
409 IF ( funcmode not in ('RUN','CANCEL') ) THEN
410 resultout := '';
411 END IF;
412 return;
413
414 EXCEPTION
415
416 WHEN NO_DATA_FOUND THEN
417 WF_CORE.CONTEXT('zpb_dc_wf.auto_distribute: no data found', itemtype, itemkey, to_char(actid), funcmode);
418 raise;
419
420 WHEN OTHERS THEN
421 WF_CORE.CONTEXT('zpb_dc_wf.auto_distribute', itemtype, itemkey, to_char(actid), funcmode);
422 raise;
423 END auto_distribute;
424
425 PROCEDURE set_ws_recipient (
426 itemtype IN varchar2,
427 itemkey IN varchar2,
428 actid IN number,
429 funcmode IN varchar2,
430 resultout OUT nocopy varchar2
431 )
432 IS
433 l_task_id NUMBER;
434 l_template_id NUMBER;
435 l_ac_template_id NUMBER;
436 l_instance_id NUMBER;
437 l_object_id NUMBER;
438 l_object_user_id NUMBER;
439 l_rolename VARCHAR2(320);
440 l_dist_list_id NUMBER;
441 l_recipient_type VARCHAR2(30);
442 l_owner VARCHAR2(30);
443 l_process_name fnd_new_messages.message_text%TYPE;
444 l_from_name fnd_user.user_name%TYPE;
445 l_template_name zpb_dc_objects.template_name%TYPE;
446
447 l_api_version NUMBER;
448 l_init_msg_list VARCHAR2(1);
449 l_commit VARCHAR2(1);
450 l_validation_level NUMBER;
451 l_return_status VARCHAR2(1);
452 l_msg_count NUMBER;
453 l_msg_data VARCHAR2(4000);
454 l_resultout VARCHAR2(30);
455 BEGIN
456
457 l_api_version := 1.0;
458 l_init_msg_list := FND_API.G_FALSE;
459 l_commit := FND_API.G_FALSE;
460 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
461 l_owner := fnd_global.user_name;
462
463 IF (funcmode = 'RUN') THEN
464 resultout :='COMPLETE:N';
465
466 -- Auto distribution parameters
467 l_task_id := wf_engine.GetItemAttrNumber(
468 Itemtype => ItemType,
469 Itemkey => ItemKey,
470 aname => 'TASKID');
471
472 l_template_id := wf_engine.GetItemAttrNumber(
473 Itemtype => ItemType,
474 Itemkey => ItemKey,
475 aname => 'DC_TEMPLATE_ID');
476
477 l_instance_id := wf_engine.GetItemAttrNumber(
478 Itemtype => ItemType,
479 Itemkey => ItemKey,
480 aname => 'INSTANCEID');
481
482 -- Manual distribution parameters
483 IF (l_task_id is null) THEN
484 l_dist_list_id := wf_engine.GetItemAttrNumber(
485 Itemtype => ItemType,
486 Itemkey => ItemKey,
487 aname => 'DC_DIST_LIST_ID');
488 l_recipient_type := wf_engine.GetItemAttrText(
489 Itemtype => ItemType,
490 Itemkey => ItemKey,
491 aname => 'DC_RECIPIENT_TYPE');
492 l_object_id := wf_engine.GetItemAttrNumber(
493 Itemtype => ItemType,
494 Itemkey => ItemKey,
495 aname => 'DC_OBJECT_ID');
496 END IF;
497
498
499 -- set issue notifications related parameters
500 wf_engine.SetItemAttrText(
501 Itemtype => ItemType,
502 Itemkey => ItemKey,
503 aname => 'EPBPERFORMER',
504 avalue => l_owner);
505
506 /* To find out the FROM ROLE
507 for multiple template cases */
508 IF (l_instance_id is not null and
509 l_task_id is not null) then -- AUTO--
510 SELECT value
511 INTO l_ac_template_id
512 FROM zpb_task_parameters
513 WHERE task_id = l_task_id
514 AND name = 'DISTRIBUTION_TEMPLATE_ID';
515
516 SELECT object_user_id
517 INTO l_object_user_id
518 FROM zpb_dc_objects
519 WHERE ac_instance_id = l_instance_id
520 AND ac_template_id = l_ac_template_id
521 AND object_type = 'M';
522 ELSE -- Manual--
523 SELECT template_id, template_name
524 INTO l_template_id, l_template_name
525 FROM zpb_dc_objects
526 WHERE object_id = l_object_id;
527
528 SELECT object_user_id
529 INTO l_object_user_id
530 FROM zpb_dc_objects
531 WHERE template_id = l_template_id
532 AND object_type = 'M';
533
534 -- Get Message from Fnd_Messages
535 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_MANU_DIST_ISSUE_MSG');
536 l_process_name := FND_MESSAGE.GET;
537
538 wf_engine.SetItemAttrText(
539 Itemtype => ItemType,
540 Itemkey => ItemKey,
541 aname => 'DC_PROCESS_NAME',
542 avalue => l_process_name);
543
544 wf_engine.SetItemAttrText(
545 Itemtype => ItemType,
546 Itemkey => ItemKey,
547 aname => 'DC_TEMPLATE_NAME',
548 avalue => l_template_name);
549
550 END IF;
551
552 SELECT user_name
553 INTO l_from_name
554 FROM fnd_user
555 WHERE user_id = l_object_user_id;
556
557 wf_engine.SetItemAttrText(
558 Itemtype => ItemType,
559 Itemkey => ItemKey,
560 aname => '#FROM_ROLE',
561 avalue => l_from_name);
562
563 -- Find out distribution recipients
564 ZPB_DC_OBJECTS_PVT.Set_Ws_Recipient(
565 p_api_version => l_api_version,
566 p_init_msg_list => l_init_msg_list,
567 p_commit => l_commit,
568 p_validation_level => l_validation_level,
569 x_return_status => l_return_status,
570 x_msg_count => l_msg_count,
571 x_msg_data => l_msg_data,
572 --
573 p_task_id => l_task_id,
574 p_template_id => l_template_id,
575 p_dist_list_id => l_dist_list_id,
576 p_object_id => l_object_id,
577 p_recipient_type => l_recipient_type,
578 x_role_name => l_rolename,
579 x_resultout => l_resultout);
580
581 wf_engine.SetItemAttrText(
582 Itemtype => ItemType,
583 Itemkey => ItemKey,
584 aname => 'DC_DISTRIBUTE_RECIPIENT',
585 avalue => l_rolename);
586
587 -- l_resultout from above call will be 'N' when role_name does not have any Users
588 IF (l_resultout = 'COMPLETE:Y') THEN
589 resultout := 'COMPLETE:Y';
590 ELSE
591 resultout := 'COMPLETE:F';
592 END IF;
593 END IF;
594
595 IF ( funcmode = 'CANCEL' ) THEN
596 resultout := 'COMPLETE:N';
597 END IF;
598
599 IF (funcmode not in ('RUN','CANCEL')) THEN
600 resultout := '';
601 END IF;
602 return;
603
604 EXCEPTION
605
606 WHEN others THEN
607 WF_CORE.CONTEXT('zpb_dc_wf.set_ws_recipient', itemtype, itemkey, to_char(actid), funcmode);
608 resultout :='COMPLETE:N';
609 raise;
610 END set_ws_recipient;
611
612
613 PROCEDURE raise_distribution_event(
614 p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2,
616 p_commit IN VARCHAR2,
617 p_validation_level IN NUMBER,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621 --
622 p_object_id IN number,
623 p_recipient_type IN varchar2,
624 p_dist_list_id IN number,
625 p_approver_type IN varchar2,
626 p_deadline_date IN varchar2,
627 p_overwrite_cust IN varchar2,
628 p_overwrite_data IN varchar2,
629 p_distribution_message IN varchar2
630 )
631 IS
632
633 l_api_name CONSTANT VARCHAR2(30) := 'raise_distribution_event' ;
634 l_api_version CONSTANT NUMBER := 1.0 ;
635 l_return_status VARCHAR2(1);
636 --
637 l_deadline_date DATE;
638 l_template_name zpb_dc_objects.template_name%TYPE;
639 l_substr_templ_name VARCHAR2(140);
640 l_sequence NUMBER;
641 l_distribute_type VARCHAR2(30);
642 l_char_date VARCHAR2(30);
643 --
644 l_item_type VARCHAR2(100) ;
645 l_item_key VARCHAR2(240) ;
646 l_event_t wf_event_t;
647 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
648 --
649 BEGIN
650
651 SAVEPOINT Raise_Distribution_Event ;
652
653 IF NOT FND_API.Compatible_API_Call ( l_api_version,
654 p_api_version,
655 l_api_name,
656 G_PKG_NAME )
657 THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
659 END IF;
660
661
662 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
663 FND_MSG_PUB.initialize ;
664 END IF;
665
666 x_return_status := FND_API.G_RET_STS_SUCCESS ;
667 -- Initialize the parameters
668 l_item_type := 'ZPBDC' ;
669
670
671 SELECT ZPB_DC_WF_PROCESSES_S.nextval
672 INTO l_sequence
673 FROM dual;
674
675 SELECT template_name
676 INTO l_template_name
677 FROM zpb_dc_objects
678 WHERE object_id = p_object_id;
679
680 l_substr_templ_name := substr(l_template_name,1,140);
681 l_distribute_type := 'Distribute Template';
682 l_char_date := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
683 l_item_key := to_char(l_sequence) ||
684 '_' || l_distribute_type||
685 '_' || l_substr_templ_name ||
686 '_' || l_char_date;
687
688 FND_FILE.Put_Line ( FND_FILE.LOG, 'WF key ' || l_item_key ) ;
689
690 wf_event.AddParameterToList(
691 p_name => 'DC_OBJECT_ID',
692 p_value => p_object_id,
693 p_parameterlist=> l_parameter_list);
694
695 wf_event.AddParameterToList(
696 p_name => 'DC_RECIPIENT_TYPE',
697 p_value => p_recipient_type,
698 p_parameterlist=> l_parameter_list);
699
700 wf_event.AddParameterToList(
701 p_name => 'DC_DIST_LIST_ID',
702 p_value => p_dist_list_id,
703 p_parameterlist=> l_parameter_list);
704
705 wf_event.AddParameterToList(
706 p_name => 'DC_APPROVER_TYPE',
707 p_value => p_approver_type,
708 p_parameterlist=> l_parameter_list);
709
710 wf_event.AddParameterToList(
711 p_name => 'DC_DEADLINE_DATE',
712 p_value => p_deadline_date,
713 p_parameterlist=> l_parameter_list);
714
715 wf_event.AddParameterToList(
716 p_name => 'DC_OVERWRITE_CUST',
717 p_value => p_overwrite_cust,
718 p_parameterlist=> l_parameter_list);
719
720 wf_event.AddParameterToList(
721 p_name => 'DC_OVERWRITE_DATA',
722 p_value => p_overwrite_data,
723 p_parameterlist=> l_parameter_list);
724
725 wf_event.AddParameterToList(
726 p_name => 'DC_DISTRIBUTION_MESSAGE',
727 p_value => p_distribution_message,
728 p_parameterlist=> l_parameter_list);
729
730 -- set fnd values so workflow process can use this values
731 -- since they can now be run in deferred mode
732
733 wf_event.AddParameterToList(p_name=>'FND_USER_ID',
734 p_value=> fnd_global.user_id,
735 p_parameterlist=>l_parameter_list);
736
737
738 wf_event.AddParameterToList(p_name=>'FND_APPLICATION_ID',
739 p_value=> fnd_global.resp_appl_id,
740 p_parameterlist=>l_parameter_list);
741
742 wf_event.AddParameterToList(p_name=>'FND_RESPONSIBILITY_ID',
743 p_value=> fnd_global.resp_id,
744 p_parameterlist=>l_parameter_list);
745
746 -- wf debugging
747 wf_log_pkg.wf_debug_flag := TRUE;
748 -- raise the event
749 wf_event.raise(p_event_name => 'oracle.apps.zpb.dc.worksheet.distribute',
750 p_event_key => l_item_key,
751 p_parameters => l_parameter_list);
752
753 l_parameter_list.delete;
754
755
756 COMMIT;
757
758 --
759 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
760 p_data => x_msg_data ) ;
761 --
762 EXCEPTION
763 --
764 when FND_API.G_EXC_ERROR then
765 --
766 rollback to Raise_Distribution_Event ;
767 x_return_status := FND_API.G_RET_STS_ERROR;
768 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
769 p_data => x_msg_data);
770 --
771 when FND_API.G_EXC_UNEXPECTED_ERROR then
772 --
773 rollback to Raise_Distribution_Event ;
774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
776 p_data => x_msg_data);
777 --
778 when OTHERS then
779 --
780 rollback to Raise_Distribution_Event ;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
783 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
784 l_api_name);
785 END if;
786 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
787 p_data => x_msg_data);
788 --
789
790
791 END raise_distribution_event;
792
793 PROCEDURE manual_distribute (
794 itemtype IN varchar2,
795 itemkey IN varchar2,
796 actid IN number,
797 funcmode IN varchar2,
798 resultout OUT nocopy varchar2
799 )
800 IS
801 l_object_id number;
802 l_recipient_type varchar2(30);
803 l_dist_list_id number;
804 l_approver_type zpb_dc_objects.approver_type%TYPE;
805 l_deadline_date_text varchar2(30);
806 l_overwrite_cust varchar2(30);
807 l_overwrite_data varchar2(30);
808 l_req_id number;
809 l_object_user_id number;
810 l_object_user_name fnd_user.description%TYPE;
811 l_from_name fnd_user.user_name%TYPE;
812 l_message varchar2(4000);
813
814 l_user_id number;
815 l_resp_id number;
816 l_respapp_id number;
817
818 BEGIN
819 l_object_id := 0;
820 l_dist_list_id := 0;
821 l_req_id := 0;
822 l_user_id := fnd_global.USER_ID;
823 l_resp_id := fnd_global.RESP_ID;
824 l_respapp_id := fnd_global.RESP_APPL_ID;
825
826 IF (funcmode = 'RUN') THEN
827 resultout :='COMPLETE:N';
828
829 l_object_id := wf_engine.GetItemAttrNumber(
830 Itemtype => ItemType,
831 Itemkey => ItemKey,
832 aname => 'DC_OBJECT_ID');
833
834 l_dist_list_id := wf_engine.GetItemAttrNumber(
835 Itemtype => ItemType,
836 Itemkey => ItemKey,
837 aname => 'DC_DIST_LIST_ID');
838
839
840 l_recipient_type := wf_engine.GetItemAttrText(
841 Itemtype => ItemType,
842 Itemkey => ItemKey,
843 aname => 'DC_RECIPIENT_TYPE');
844
845 l_approver_type := wf_engine.GetItemAttrText(
846 Itemtype => ItemType,
847 Itemkey => ItemKey,
848 aname => 'DC_APPROVER_TYPE');
849 IF (l_approver_type = '' OR l_approver_type is null) THEN
850 l_approver_type := 'DISTRIBUTOR';
851 END IF;
852
853 l_deadline_date_text := wf_engine.GetItemAttrText(
854 Itemtype => ItemType,
855 Itemkey => ItemKey,
856 aname => 'DC_DEADLINE_DATE');
857
858 l_overwrite_cust := wf_engine.GetItemAttrText(
859 Itemtype => ItemType,
860 Itemkey => ItemKey,
861 aname => 'DC_OVERWRITE_CUST');
862
863 l_overwrite_data := wf_engine.GetItemAttrText(
864 Itemtype => ItemType,
865 Itemkey => ItemKey,
866 aname => 'DC_OVERWRITE_DATA');
867
868 l_user_id := wf_engine.GetItemAttrNumber(
869 Itemtype => ItemType,
870 Itemkey => ItemKey,
871 aname => 'FND_USER_ID');
872
873 l_resp_id := wf_engine.GetItemAttrNumber(
874 Itemtype => ItemType,
875 Itemkey => ItemKey,
876 aname => 'FND_RESPONSIBILITY_ID');
877
878 l_respapp_id := wf_engine.GetItemAttrNumber(
879 Itemtype => ItemType,
880 Itemkey => ItemKey,
881 aname => 'FND_APPLICATION_ID');
882
883 -- Get the object user id to populate dc distributors
884 SELECT nvl(fnd.description,fnd.user_name)
885 INTO l_object_user_name
886 FROM fnd_user fnd,zpb_dc_objects obj
887 WHERE fnd.user_id = obj.object_user_id
888 AND obj.object_id = l_object_id;
889
890 wf_engine.SetItemAttrText(
891 Itemtype => ItemType,
892 Itemkey => ItemKey,
893 aname => 'DC_DISTRIBUTOR',
894 avalue => l_object_user_name);
895
896 /* Populate the from field in the notification details page
897 This name should be user name from the fnd table */
898 SELECT fnd.user_name
899 INTO l_from_name
900 FROM fnd_user fnd,zpb_dc_objects obj
901 WHERE fnd.user_id = obj.object_user_id
902 AND obj.object_id = l_object_id;
903
904 wf_engine.SetItemAttrText(
905 Itemtype => ItemType,
906 Itemkey => ItemKey,
907 aname => '#FROM_ROLE',
908 avalue => l_from_name);
909
910 -- Get Message from Fnd_Messages
911 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_DISTRIBUTE_ISSUE_MSG');
912 l_message := FND_MESSAGE.GET;
913
914 wf_engine.SetItemAttrText(
915 Itemtype => ItemType,
916 Itemkey => ItemKey,
917 aname => 'ISSUEMSG',
918 avalue => l_message);
919
920
921 -- Run the CP
922 fnd_global.apps_initialize(l_user_id,l_resp_id,l_respapp_id);
923
924 l_req_id := FND_REQUEST.SUBMIT_REQUEST ('ZPB',
925 'ZPB_DC_MANUAL_DISTRIBUTE', NULL, NULL, FALSE,
926 l_object_id,l_recipient_type,l_dist_list_id,
927 l_approver_type,l_deadline_date_text,l_overwrite_cust,
928 l_overwrite_data);
929
930 -- Set the values for ntf
931 wf_engine.SetItemAttrNumber(
932 Itemtype => ItemType,
933 Itemkey => ItemKey,
934 aname => 'REQUEST_ID',
935 avalue => l_req_id);
936
937 IF l_req_id = 0 THEN
938 resultout := 'COMPLETE:N';
939 ELSE
940 resultout := 'COMPLETE:Y';
941 END IF;
942 END IF;
943
944 IF ( funcmode = 'CANCEL' ) THEN
945 resultout := 'COMPLETE:Y';
946 END IF;
947
948 IF (funcmode not in ('RUN','CANCEL')) THEN
949 resultout := '';
950 END IF;
951 return;
952
953 EXCEPTION
954
955 WHEN NO_DATA_FOUND THEN
956 WF_CORE.CONTEXT('zpb_dc_wf.manual_distribute: no data found', itemtype, itemkey, to_char(actid), funcmode);
957 resultout :='COMPLETE:N';
958 raise;
959
960 WHEN OTHERS THEN
961 WF_CORE.CONTEXT('zpb_dc_wf.manual_distribute', itemtype, itemkey, to_char(actid), funcmode);
962 resultout :='COMPLETE:N';
963 raise;
964 END manual_distribute;
965
966 PROCEDURE get_template_count (
967 itemtype IN varchar2,
968 itemkey IN varchar2,
969 actid IN number,
970 funcmode IN varchar2,
971 resultout OUT nocopy varchar2
972 )
973 IS
974 l_task_id NUMBER ;
975 l_count NUMBER ;
976 l_instance_id NUMBER ;
977 l_ac_template_id NUMBER ;
978 l_object_user_id NUMBER ;
979 l_from_name fnd_user.user_name%TYPE;
980 BEGIN
981
982 l_task_id := 0;
983 l_count := 0;
984 l_instance_id := 0;
985 l_ac_template_id := 0;
986 l_object_user_id := 0;
987
988 IF (funcmode = 'RUN') THEN
989 resultout :='COMPLETE:N';
990
991 l_task_id := wf_engine.GetItemAttrNumber(
992 Itemtype => ItemType,
993 Itemkey => ItemKey,
994 aname => 'TASKID');
995
996 FOR template_id_rec IN (
997 SELECT obj.template_id
998 FROM zpb_task_parameters param,
999 zpb_dc_objects obj
1000 WHERE param.task_id = l_task_id
1001 AND param.name = 'SUBMISSION_TEMPLATE_ID'
1002 AND to_number(param.value) = obj.ac_template_id
1003 AND obj.status <> 'SUBMITTED_TO_SHARED'
1004 AND obj.object_type = 'M' -- consistently choose M record
1005 )
1006 LOOP
1007 l_count := l_count + 1;
1008 template_list(l_count) := template_id_rec.template_id;
1009 END LOOP;
1010
1011 wf_engine.SetItemAttrText(
1012 Itemtype => ItemType,
1013 Itemkey => ItemKey,
1014 aname => 'DC_SUBMIT_LOOP_COUNTER',
1015 avalue => l_count);
1016
1017 wf_engine.SetItemAttrText(
1018 Itemtype => ItemType,
1019 Itemkey => ItemKey,
1020 aname => 'DC_LOOP_VISITED_COUNTER',
1021 avalue => 0);
1022
1023 -- Populate the from field in the notification details page
1024 l_instance_id := wf_engine.GetItemAttrNumber(
1025 Itemtype => ItemType,
1026 Itemkey => ItemKey,
1027 aname => 'INSTANCEID');
1028
1029 /* To find out any (of the many) template(s)
1030 for multiple template cases */
1031 SELECT min(value)
1032 INTO l_ac_template_id
1033 FROM zpb_task_parameters
1034 WHERE task_id = l_task_id
1035 AND name = 'SUBMISSION_TEMPLATE_ID';
1036
1037 SELECT object_user_id
1038 INTO l_object_user_id
1039 FROM zpb_dc_objects
1040 WHERE ac_instance_id = l_instance_id
1041 AND ac_template_id = l_ac_template_id
1042 AND object_type = 'M';
1043
1044 SELECT user_name
1045 INTO l_from_name
1046 FROM fnd_user
1047 WHERE user_id = l_object_user_id;
1048
1049 wf_engine.SetItemAttrText(
1050 Itemtype => ItemType,
1051 Itemkey => ItemKey,
1052 aname => '#FROM_ROLE',
1053 avalue => l_from_name);
1054
1055 resultout := 'COMPLETE:Y';
1056 END IF;
1057
1058 IF ( funcmode = 'CANCEL' ) THEN
1059 resultout := 'COMPLETE:Y';
1060 END IF;
1061
1062 IF (funcmode not in ('RUN','CANCEL')) THEN
1063 resultout := '';
1064 END IF;
1065 return;
1066
1067 EXCEPTION
1068
1069 WHEN others THEN
1070 WF_CORE.CONTEXT('zpb_dc_wf.get_template_count', itemtype, itemkey, to_char(actid), funcmode);
1071 raise;
1072 END get_template_count;
1073
1074 PROCEDURE manage_submission (
1075 itemtype IN varchar2,
1076 itemkey IN varchar2,
1077 actid IN number,
1078 funcmode IN varchar2,
1079 resultout OUT nocopy varchar2
1080 )
1081 IS
1082 l_template_id NUMBER ;
1083 l_ws_count NUMBER ;
1084 l_ws_status_count NUMBER ;
1085 l_loop_visited_counter NUMBER ;
1086 l_distribution_method zpb_dc_objects.distribution_method%TYPE;
1087 l_approval_required_flag zpb_dc_objects.approval_required_flag%TYPE;
1088 l_multiple_submissions_flag zpb_dc_objects.multiple_submissions_flag%TYPE;
1089
1090 BEGIN
1091 l_template_id := 0;
1092 l_ws_count := 0;
1093 l_ws_status_count := 0;
1094 l_loop_visited_counter := 0;
1095
1096 IF (funcmode = 'RUN') THEN
1097
1098 --------------------------------------
1099 -- Get the right template to process -
1100 --------------------------------------
1101
1102 l_loop_visited_counter := wf_engine.GetItemAttrNumber
1103 ( itemtype => itemtype,
1104 itemkey => itemkey,
1105 aname => 'DC_LOOP_VISITED_COUNTER');
1106 l_loop_visited_counter := l_loop_visited_counter + 1;
1107
1108 l_template_id := template_list(l_loop_visited_counter);
1109
1110 wf_engine.SetItemAttrNumber( ItemType => ItemType,
1111 ItemKey => ItemKey,
1112 aname => 'DC_LOOP_VISITED_COUNTER',
1113 avalue => l_loop_visited_counter );
1114
1115
1116 wf_engine.SetItemAttrNumber( ItemType => ItemType,
1117 ItemKey => ItemKey,
1118 aname => 'DC_TEMPLATE_ID',
1119 avalue => l_template_id );
1120 --
1121 FND_FILE.Put_Line ( FND_FILE.LOG, 'manage_submission - l_template_id=' || l_template_id ) ;
1122 --------------------------------------
1123 -- Processing --
1124 --------------------------------------
1125 -- Check whether distribution happened --
1126 SELECT count(*)
1127 INTO l_ws_count
1128 FROM zpb_dc_objects
1129 WHERE template_id = l_template_id
1130 AND object_type = 'W';
1131 FND_FILE.Put_Line ( FND_FILE.LOG, 'manage_submission - l_ws_count=' || l_ws_count ) ;
1132
1133 -- Get the necessary info for processing --
1134 FOR worksheet_rec IN (
1135 SELECT distribution_method,
1136 approval_required_flag,
1137 multiple_submissions_flag
1138 FROM zpb_dc_objects
1139 WHERE template_id = l_template_id
1140 AND object_type = 'M' )
1141 LOOP
1142 l_distribution_method := worksheet_rec.distribution_method;
1143 l_approval_required_flag := worksheet_rec.approval_required_flag;
1144 l_multiple_submissions_flag := worksheet_rec.multiple_submissions_flag;
1145
1146 END LOOP;
1147 IF (l_ws_count = 0) THEN -- template not distributed yet--
1148 resultout := 'COMPLETE:WAIT';
1149 ELSE
1150 IF (l_distribution_method = 'DIRECT_DISTRIBUTION' AND
1151 l_approval_required_flag = 'N' AND
1152 l_multiple_submissions_flag <>'Y') THEN
1153 SELECT count(*)
1154 INTO l_ws_status_count
1155 FROM zpb_dc_objects, fnd_user
1156 WHERE object_user_id = user_id
1157 AND template_id = l_template_id
1158 AND object_type = 'W'
1159 AND status NOT IN ('SUBMITTED_TO_SHARED')
1160 AND (end_date is null OR end_date > sysdate);
1161 FND_FILE.Put_Line ( FND_FILE.LOG, 'manage_submission - l_ws_status_count=' || l_ws_status_count ) ;
1162
1163 IF (l_ws_status_count = 0) THEN
1164 resultout := 'COMPLETE:UPDATE_STATUS';
1165 ELSE
1166 resultout := 'COMPLETE:WAIT';
1167 END IF;
1168 ELSE -- Direct with appr and Cascade, wait for BPO to submit --
1169 resultout := 'COMPLETE:WAIT';
1170 END IF;
1171 END IF;
1172 END IF;
1173 IF ( funcmode = 'CANCEL' ) THEN
1174 resultout := 'COMPLETE:WAIT';
1175 END IF;
1176
1177 IF (funcmode not in ('RUN','CANCEL')) THEN
1178 resultout := '';
1179 END IF;
1180 return;
1181
1182 EXCEPTION
1183
1184 WHEN NO_DATA_FOUND THEN
1185 WF_CORE.CONTEXT('zpb_dc_wf.manage_submission: no data found', itemtype, itemkey, to_char(actid), funcmode);
1186 raise;
1187
1188 WHEN OTHERS THEN
1189 WF_CORE.CONTEXT('zpb_dc_wf.manage_submission', itemtype, itemkey, to_char(actid), funcmode);
1190 raise;
1191 END manage_submission;
1192
1193 PROCEDURE set_template_recipient (
1194 itemtype IN varchar2,
1195 itemkey IN varchar2,
1196 actid IN number,
1197 funcmode IN varchar2,
1198 resultout OUT nocopy varchar2
1199 )
1200 IS
1201 l_rolename VARCHAR2(320);
1202 l_template_id NUMBER;
1203
1204 l_api_version CONSTANT NUMBER := 1.0;
1205 l_init_msg_list VARCHAR2(1);
1206 l_commit VARCHAR2(1);
1207 l_validation_level NUMBER;
1208 l_return_status VARCHAR2(1);
1209 l_msg_count NUMBER;
1210 l_msg_data VARCHAR2(4000);
1211 BEGIN
1212
1213 l_init_msg_list := FND_API.G_FALSE;
1214 l_commit := FND_API.G_FALSE;
1215 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1216
1217 IF (funcmode = 'RUN') THEN
1218 l_template_id := wf_engine.GetItemAttrNumber(
1219 Itemtype => ItemType,
1220 Itemkey => ItemKey,
1221 aname => 'DC_TEMPLATE_ID');
1222
1223 ZPB_DC_OBJECTS_PVT.Set_Template_Recipient(
1224 p_api_version => l_api_version,
1225 p_init_msg_list => l_init_msg_list,
1226 p_commit => l_commit,
1227 p_validation_level => l_validation_level,
1228 x_return_status => l_return_status,
1229 x_msg_count => l_msg_count,
1230 x_msg_data => l_msg_data,
1231 --
1232 p_template_id => l_template_id,
1233 x_role_name => l_rolename);
1234
1235 wf_engine.SetItemAttrText(
1236 Itemtype => ItemType,
1237 Itemkey => ItemKey,
1238 aname => 'DC_TEMPLATE_RECIPIENT',
1239 avalue => l_rolename);
1240
1241 resultout := 'COMPLETE';
1242 END IF;
1243
1244 IF ( funcmode = 'CANCEL' ) THEN
1245 resultout := 'COMPLETE';
1246 END IF;
1247
1248 IF ( funcmode not in ('RUN','CANCEL') ) THEN
1249 resultout := '';
1250 END IF;
1251 return;
1252
1253 EXCEPTION
1254
1255 WHEN others THEN
1256 WF_CORE.CONTEXT('zpb_dc_wf.set_template_recipient', itemtype, itemkey, to_char(actid), funcmode);
1257 raise;
1258 END set_template_recipient;
1259
1260 PROCEDURE set_template_status (
1261 itemtype IN varchar2,
1262 itemkey IN varchar2,
1263 actid IN number,
1264 funcmode IN varchar2,
1265 resultout OUT nocopy varchar2
1266 )
1267 IS
1268 l_template_id NUMBER;
1269 BEGIN
1270
1271 l_template_id := 0;
1272 IF (funcmode = 'RUN') THEN
1273
1274 l_template_id := wf_engine.GetItemAttrNumber(
1275 Itemtype => ItemType,
1276 Itemkey => ItemKey,
1277 aname => 'DC_TEMPLATE_ID');
1278
1279 UPDATE zpb_dc_objects
1280 SET status = 'SUBMITTED_TO_SHARED',
1281 freeze_flag = 'Y',
1282 LAST_UPDATED_BY = fnd_global.USER_ID,
1283 LAST_UPDATE_DATE = SYSDATE,
1284 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1285 WHERE template_id = l_template_id
1286 AND object_type in ('M','E','C');
1287
1288 resultout := 'COMPLETE';
1289 END IF;
1290
1291 IF ( funcmode = 'CANCEL' ) THEN
1292 resultout := 'COMPLETE';
1293 END IF;
1294
1295 IF (funcmode not in ('RUN','CANCEL')) THEN
1296 resultout := '';
1297 END IF;
1298 return;
1299
1300 EXCEPTION
1301
1302 WHEN NO_DATA_FOUND THEN
1303 WF_CORE.CONTEXT('zpb_dc_wf.set_template_status: no data found', itemtype, itemkey, to_char(actid), funcmode);
1304 raise;
1305
1306 WHEN others THEN
1307 WF_CORE.CONTEXT('zpb_dc_wf.set_template_status', itemtype, itemkey, to_char(actid), funcmode);
1308 raise;
1309 END set_template_status;
1310
1311 PROCEDURE check_template_status (
1312 itemtype IN varchar2,
1313 itemkey IN varchar2,
1314 actid IN number,
1315 funcmode IN varchar2,
1316 resultout OUT nocopy varchar2
1317 )
1318 IS
1319 l_count NUMBER;
1320 l_task_id NUMBER;
1321 l_instance_id NUMBER;
1322 BEGIN
1323
1324 IF ( funcmode = 'RUN' ) THEN
1325 resultout :='COMPLETE:N';
1326
1327 l_task_id := wf_engine.GetItemAttrNumber(
1328 Itemtype => ItemType,
1329 Itemkey => ItemKey,
1330 aname => 'TASKID');
1331 l_instance_id := wf_engine.GetItemAttrNumber(
1332 Itemtype => ItemType,
1333 Itemkey => ItemKey,
1334 aname => 'INSTANCEID');
1335
1336 SELECT count(*)
1337 INTO l_count
1338 FROM zpb_task_parameters param,
1339 zpb_dc_objects obj
1340 WHERE param.task_id = l_task_id
1341 AND param.name = 'SUBMISSION_TEMPLATE_ID'
1342 AND to_number(param.value) = obj.ac_template_id
1343 AND obj.status <> 'SUBMITTED_TO_SHARED'
1344 AND obj.ac_instance_id = l_instance_id
1345 AND obj.object_type = 'M'; -- consistently choose M record
1346
1347 IF (l_count = 0) THEN
1348 resultout :='COMPLETE:N';
1349 ELSE
1350 resultout :='COMPLETE:Y';
1351 END IF;
1352
1353 END IF;
1354
1355 IF ( funcmode = 'CANCEL' ) THEN
1356 resultout := 'COMPLETE';
1357 END IF;
1358
1359 IF (funcmode not in ('RUN','CANCEL')) THEN
1360 resultout := '';
1361 END IF;
1362 return;
1363
1364 EXCEPTION
1365 WHEN others THEN
1366 WF_CORE.CONTEXT('zpb_dc_wf.wait_to_process', itemtype, itemkey, to_char(actid), funcmode);
1367 raise;
1368 END check_template_status;
1369
1370 PROCEDURE raise_submission_event(
1371 p_api_version IN NUMBER,
1372 p_init_msg_list IN VARCHAR2,
1373 p_commit IN VARCHAR2,
1374 p_validation_level IN NUMBER,
1375 x_return_status OUT NOCOPY VARCHAR2,
1376 x_msg_count OUT NOCOPY NUMBER,
1377 x_msg_data OUT NOCOPY VARCHAR2,
1378 --
1379 p_object_id IN number,
1380 p_submission_message IN varchar2
1381 )
1382 IS
1383
1384 l_api_name CONSTANT VARCHAR2(30) := 'raise_submission_event' ;
1385 l_api_version CONSTANT NUMBER := 1.0 ;
1386 l_return_status VARCHAR2(1);
1387 --
1388 l_template_id NUMBER;
1389 l_distributor_id NUMBER;
1390 l_bpo_id NUMBER;
1391 l_approver_id NUMBER;
1392 l_template_name zpb_dc_objects.template_name%TYPE;
1393 l_substr_templ_name VARCHAR2(140);
1394 l_object_type zpb_dc_objects.object_type%TYPE;
1395 l_distribution_method zpb_dc_objects.distribution_method%TYPE;
1396 l_object_user_id NUMBER;
1397 l_char_date VARCHAR2(30);
1398 l_sequence NUMBER;
1399 l_submit_type VARCHAR2(30);
1400 l_multiple_submissions_flag VARCHAR2(1);
1401 l_approval_required_flag VARCHAR2(1);
1402 --
1403 l_item_type VARCHAR2(100);
1404 l_item_key VARCHAR2(240) ;
1405 l_event_t wf_event_t;
1406 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1407 --
1408 BEGIN
1409
1410 SAVEPOINT Raise_Submission_Event ;
1411
1412 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1413 p_api_version,
1414 l_api_name,
1415 G_PKG_NAME )
1416 THEN
1417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1418 END IF;
1419
1420 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1421 FND_MSG_PUB.initialize ;
1422 END IF;
1423
1424 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1425
1426 -- Initialize the parameters
1427 l_item_type := 'ZPBDC' ;
1428
1429 -- Get infor from the submitting user
1430 SELECT template_id,
1431 object_type,
1432 template_name,
1433 object_user_id,
1434 distributor_user_id,
1435 distribution_method,
1436 multiple_submissions_flag,
1437 approval_required_flag
1438 INTO l_template_id,
1439 l_object_type,
1440 l_template_name,
1441 l_object_user_id,
1442 l_distributor_id,
1443 l_distribution_method,
1444 l_multiple_submissions_flag,
1445 l_approval_required_flag
1446 FROM zpb_dc_objects
1447 WHERE object_id = p_object_id;
1448
1449 SELECT object_user_id
1450 INTO l_bpo_id
1451 FROM zpb_dc_objects
1452 WHERE template_id = l_template_id
1453 AND object_type = 'M';
1454
1455 -- Set the approver user id
1456 IF (l_distribution_method = 'DIRECT_DISTRIBUTION') THEN
1457 l_approver_id := l_bpo_id;
1458 ELSE
1459 IF (l_distributor_id <> -100) THEN
1460 l_approver_id := l_distributor_id;
1461 ELSE
1462 l_approver_id := l_bpo_id;
1463 END IF;
1464 END IF;
1465
1466 -- Set the status to SUBMITTTED to prevent updating
1467 IF (l_object_type = 'E') THEN
1468 UPDATE zpb_dc_objects
1469 SET status = 'SUBMITTED',
1470 submission_date = sysdate,
1471 submitted_by = l_object_user_id,
1472 LAST_UPDATED_BY = fnd_global.USER_ID,
1473 LAST_UPDATE_DATE = SYSDATE,
1474 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1475 WHERE template_id = l_template_id
1476 AND object_type in ('M','E','C');
1477 ELSE
1478 IF(l_distribution_method = 'DIRECT_DISTRIBUTION' AND l_multiple_submissions_flag = 'Y'
1479 AND l_approval_required_flag <> 'Y')
1480 THEN
1481 UPDATE zpb_dc_objects
1482 SET status = 'SUBMITTED',
1483 submission_date = sysdate,
1484 submitted_by = l_object_user_id,
1485 approver_user_id = l_approver_id,
1486 create_approval_measures_flag = 'Y',
1487 delete_approval_measures_flag = 'N',
1488 LAST_UPDATED_BY = fnd_global.USER_ID,
1489 LAST_UPDATE_DATE = SYSDATE,
1490 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1491 WHERE object_id = p_object_id;
1492 ELSE
1493 UPDATE zpb_dc_objects
1494 SET status = 'SUBMITTED',
1495 freeze_flag = 'Y',
1496 submission_date = sysdate,
1497 submitted_by = l_object_user_id,
1498 approver_user_id = l_approver_id,
1499 create_approval_measures_flag = 'Y',
1500 delete_approval_measures_flag = 'N', --3834999--
1501 LAST_UPDATED_BY = fnd_global.USER_ID,
1502 LAST_UPDATE_DATE = SYSDATE,
1503 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1504 WHERE object_id = p_object_id;
1505 END IF;
1506 END IF;
1507
1508 -- Create a meaningful item key for dev use--
1509 SELECT ZPB_DC_WF_PROCESSES_S.nextval
1510 INTO l_sequence
1511 FROM dual;
1512
1513 IF (l_object_type = 'E') THEN
1514 l_submit_type := 'Submit Template';
1515 ELSE
1516 l_submit_type := 'Submit Worksheet';
1517 END IF;
1518
1519 l_substr_templ_name := substr(l_template_name,1,140);
1520 l_char_date := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
1521 l_item_key := to_char(l_sequence) ||
1522 '_' || l_submit_type||
1523 '_' || l_substr_templ_name ||
1524 '_' || l_char_date;
1525
1526
1527 FND_FILE.Put_Line ( FND_FILE.LOG, 'WF key ' || l_item_key ) ;
1528
1529 wf_event.AddParameterToList(
1530 p_name => 'DC_OBJECT_ID',
1531 p_value => p_object_id,
1532 p_parameterlist=> l_parameter_list);
1533
1534 wf_event.AddParameterToList(
1535 p_name => 'DC_TEMPLATE_ID',
1536 p_value => l_template_id,
1537 p_parameterlist=> l_parameter_list);
1538
1539 wf_event.AddParameterToList(
1540 p_name => 'DC_OBJECT_TYPE',
1541 p_value => l_object_type,
1542 p_parameterlist=> l_parameter_list);
1543
1544 wf_event.AddParameterToList(
1545 p_name => 'DC_SUBMISSION_MESSAGE',
1546 p_value => p_submission_message,
1547 p_parameterlist=> l_parameter_list);
1548
1549 -- set fnd values so workflow process can use this values
1550 -- since they can now be run in deferred mode
1551
1552 wf_event.AddParameterToList(p_name=>'FND_USER_ID',
1553 p_value=> fnd_global.user_id,
1554 p_parameterlist=>l_parameter_list);
1555
1556 wf_event.AddParameterToList(p_name=>'FND_APPLICATION_ID',
1557 p_value=> fnd_global.resp_appl_id,
1558 p_parameterlist=>l_parameter_list);
1559
1560 wf_event.AddParameterToList(p_name=>'FND_RESPONSIBILITY_ID',
1561 p_value=> fnd_global.resp_id,
1562 p_parameterlist=>l_parameter_list);
1563
1564 -- wf debugging
1565 wf_log_pkg.wf_debug_flag := TRUE;
1566
1567 -- raise the event
1568 wf_event.raise(p_event_name => 'oracle.apps.zpb.dc.worksheet.submit',
1569 p_event_key => l_item_key,
1570 p_parameters => l_parameter_list);
1571
1572 l_parameter_list.delete;
1573
1574
1575 COMMIT;
1576
1577 --
1578 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1579 p_data => x_msg_data ) ;
1580 --
1581 EXCEPTION
1582 --
1583 when FND_API.G_EXC_ERROR then
1584 --
1585 rollback to Raise_Submission_Event ;
1586 x_return_status := FND_API.G_RET_STS_ERROR;
1587 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1588 p_data => x_msg_data);
1589 --
1590 when FND_API.G_EXC_UNEXPECTED_ERROR then
1591 --
1592 rollback to Raise_Submission_Event ;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1594 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1595 p_data => x_msg_data);
1596 --
1597 when OTHERS then
1598 --
1599 rollback to Raise_Submission_Event ;
1600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1601 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1602 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1603 l_api_name);
1604 END if;
1605 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1606 p_data => x_msg_data);
1607 --
1608
1609
1610 END raise_submission_event;
1611
1612 PROCEDURE check_object_type (
1613 itemtype IN varchar2,
1614 itemkey IN varchar2,
1615 actid IN number,
1616 funcmode IN varchar2,
1617 resultout OUT nocopy varchar2
1618 )
1619 IS
1620 l_object_type VARCHAR2(10);
1621 l_object_id NUMBER;
1622 l_distribution_method zpb_dc_objects.distribution_method%TYPE;
1623 l_approval_required VARCHAR2(1);
1624 BEGIN
1625
1626 IF (funcmode = 'RUN') THEN
1627
1628 l_object_type := wf_engine.GetItemAttrText(
1629 Itemtype => ItemType,
1630 Itemkey => ItemKey,
1631 aname => 'DC_OBJECT_TYPE');
1632
1633 l_object_id := wf_engine.GetItemAttrNumber(
1634 Itemtype => ItemType,
1635 Itemkey => ItemKey,
1636 aname => 'DC_OBJECT_ID');
1637
1638 SELECT distribution_method, approval_required_flag
1639 INTO l_distribution_method, l_approval_required
1640 FROM zpb_dc_objects
1641 WHERE object_id = l_object_id;
1642
1643 IF (l_object_type = 'E') THEN -- submit template --
1644 IF (l_distribution_method = 'DIRECT_DISTRIBUTION' AND
1645 l_approval_required = 'Y') OR
1646 (l_distribution_method = 'CASCADE_DISTRIBUTION') THEN
1647 resultout := 'COMPLETE:TEMPLATE_DIRECT_APPR';
1648 ELSIF (l_distribution_method = 'DIRECT_DISTRIBUTION' AND
1649 l_approval_required = 'N') THEN
1650 resultout := 'COMPLETE:TEMPLATE_DIRECT_NO_APPR';
1651 END IF;
1652 ELSE -- submite worksheet --
1653 IF (l_distribution_method = 'DIRECT_DISTRIBUTION' AND
1654 l_approval_required = 'Y') THEN
1655 resultout := 'COMPLETE:WORKSHEET_DIRECT_APPR';
1656 ELSIF (l_distribution_method = 'DIRECT_DISTRIBUTION' AND
1657 l_approval_required = 'N') THEN
1658 resultout := 'COMPLETE:WORKSHEET_DIRECT_NO_APPR';
1659 ELSE
1660 resultout := 'COMPLETE:WORKSHEET_CASCADE';
1661 END IF;
1662 END IF;
1663 END IF;
1664
1665 IF ( funcmode = 'CANCEL' ) THEN
1666 resultout := 'COMPLETE';
1667 END IF;
1668
1669 IF (funcmode not in ('RUN','CANCEL')) THEN
1670 resultout := '';
1671 END IF;
1672 return;
1673
1674 EXCEPTION
1675
1676 WHEN NO_DATA_FOUND THEN
1677 WF_CORE.CONTEXT('zpb_dc_wf.check_object_type: no data found', itemtype, itemkey, to_char(actid), funcmode);
1678 raise;
1679
1680 WHEN others THEN
1681 WF_CORE.CONTEXT('zpb_dc_wf.check_object_type', itemtype, itemkey, to_char(actid), funcmode);
1682 raise;
1683 END check_object_type;
1684
1685 PROCEDURE freeze_template (
1686 itemtype IN varchar2,
1687 itemkey IN varchar2,
1688 actid IN number,
1689 funcmode IN varchar2,
1690 resultout OUT nocopy varchar2
1691 )
1692 IS
1693 l_template_id NUMBER;
1694 l_object_id NUMBER;
1695 l_object_user_id NUMBER;
1696 l_template_name VARCHAR2(1000);
1697
1698 l_object_user_name fnd_user.description%TYPE;
1699 l_from_name fnd_user.user_name%TYPE;
1700 l_frzn_rolename VARCHAR2(320);
1701 l_all_rolename VARCHAR2(320);
1702 l_exp_days NUMBER;
1703 l_charDate VARCHAR2(20);
1704 l_frzn_role_has_users VARCHAR2(1);
1705 --
1706 BEGIN
1707
1708 l_exp_days := 7;
1709
1710 IF (funcmode = 'RUN') THEN
1711
1712 l_template_id := wf_engine.GetItemAttrNumber(
1713 Itemtype => ItemType,
1714 Itemkey => ItemKey,
1715 aname => 'DC_TEMPLATE_ID');
1716
1717 l_object_id := wf_engine.GetItemAttrNumber(
1718 Itemtype => ItemType,
1719 Itemkey => ItemKey,
1720 aname => 'DC_OBJECT_ID');
1721 -- Get the template name
1722 SELECT template_name
1723 INTO l_template_name
1724 FROM zpb_dc_objects
1725 WHERE template_id = l_template_id
1726 AND object_type = 'M';
1727
1728
1729 -- Populate the dc approver for frozen ntf
1730 SELECT nvl(fnd.description,fnd.user_name), object_user_id
1731 INTO l_object_user_name, l_object_user_id
1732 FROM zpb_dc_objects, fnd_user fnd
1733 WHERE object_id = l_object_id
1734 AND object_user_id = fnd.user_id;
1735
1736 -- Populate FROM field
1737 SELECT fnd.user_name
1738 INTO l_from_name
1739 FROM fnd_user fnd, zpb_dc_objects
1740 WHERE object_id = l_object_id
1741 AND object_user_id = fnd.user_id;
1742
1743 -- Create the roles for fromzen and all users
1744 l_charDate := to_char(sysdate, 'J-SSSSS');
1745 l_frzn_rolename := 'ZPB_DC_SUBMIT_FRZN'|| to_char(l_template_id) || '-' || l_charDate;
1746 l_all_rolename := 'ZPB_DC_SUBMIT_ALLU'|| to_char(l_template_id) || '-' || l_charDate;
1747 zpb_wf_ntf.SetRole(l_frzn_rolename, l_exp_days);
1748 zpb_wf_ntf.SetRole(l_all_rolename, l_exp_days);
1749 l_frzn_role_has_users := 'N';
1750 FOR frzn_rec IN (
1751 SELECT u.user_name as user_name
1752 FROM zpb_dc_objects obj, fnd_user u
1753 WHERE obj.template_id = l_template_id
1754 AND obj.object_type = 'W'
1755 AND obj.status NOT IN ('SUBMITTED','FROZEN','APPROVED','SUBMITTED_TO_SHARED')
1756 AND obj.object_user_id = u.user_id
1757 AND (u.end_date is null OR u.end_date > sysdate))
1758 LOOP
1759 l_frzn_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_frzn_rolename, frzn_rec.user_name);
1760 l_frzn_role_has_users := 'Y';
1761 END LOOP;
1762
1763 FOR all_rec IN (
1764 SELECT u.user_name as user_name
1765 FROM zpb_dc_objects obj, fnd_user u
1766 WHERE obj.template_id = l_template_id
1767 AND obj.object_type in ('W','C')
1768 AND obj.object_user_id = u.user_id)
1769 LOOP
1770 l_all_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_all_rolename, all_rec.user_name);
1771 END LOOP;
1772
1773 -- Change the status to submitted to shared for the template records
1774 UPDATE zpb_dc_objects
1775 SET status = 'SUBMITTED_TO_SHARED',
1776 submission_date = sysdate,
1777 submitted_by = l_object_user_id
1778 WHERE template_id = l_template_id
1779 AND object_type in ('M','E','C');
1780
1781 /* Set the status to Frozen if status DISTRIBUTION_PENDING,
1782 DISTRIBUTED, REJECTED */
1783 UPDATE zpb_dc_objects
1784 SET status = 'FROZEN'
1785 WHERE template_id = l_template_id
1786 AND OBJECT_TYPE = 'W'
1787 AND status not in ('SUBMITTED','FROZEN','APPROVED','SUBMITTED_TO_SHARED');
1788
1789 -- Set the freeze/app mea flag all records
1790 UPDATE zpb_dc_objects
1791 SET freeze_flag = 'Y',
1792 LAST_UPDATED_BY = fnd_global.USER_ID,
1793 LAST_UPDATE_DATE = SYSDATE,
1794 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1795 WHERE template_id = l_template_id;
1796
1797 --Set template name
1798 wf_engine.SetItemAttrText(
1799 Itemtype => ItemType,
1800 Itemkey => ItemKey,
1801 aname => 'DC_TEMPLATE_NAME',
1802 avalue => l_template_name);
1803
1804
1805 -- Set notification recipients --
1806 wf_engine.SetItemAttrText(
1807 Itemtype => ItemType,
1808 Itemkey => ItemKey,
1809 aname => 'DC_FROZEN_WS_USER',
1810 avalue => l_frzn_rolename);
1811
1812 wf_engine.SetItemAttrText(
1813 Itemtype => ItemType,
1814 Itemkey => ItemKey,
1815 aname => 'DC_SUBMITTER',
1816 avalue => l_all_rolename);
1817
1818 wf_engine.SetItemAttrText(
1819 Itemtype => ItemType,
1820 Itemkey => ItemKey,
1821 aname => 'DC_APPROVER',
1822 avalue => l_object_user_name);
1823
1824 wf_engine.SetItemAttrText(
1825 Itemtype => ItemType,
1826 Itemkey => ItemKey,
1827 aname => '#FROM_ROLE',
1828 avalue => l_from_name);
1829
1830 IF (l_frzn_role_has_users = 'Y') THEN
1831 resultout := 'COMPLETE:Y';
1832 ELSE
1833 resultout := 'COMPLETE:N';
1834 END IF;
1835 END IF;
1836
1837 IF ( funcmode = 'CANCEL' ) THEN
1838 resultout := 'COMPLETE:Y';
1839 END IF;
1840
1841 IF (funcmode not in ('RUN','CANCEL')) THEN
1842 resultout := '';
1843 END IF;
1844
1845 EXCEPTION
1846
1847 WHEN NO_DATA_FOUND THEN
1848 WF_CORE.CONTEXT('zpb_dc_wf.freeze_template: no data found', itemtype, itemkey, to_char(actid), funcmode);
1849 raise;
1850
1851 WHEN others THEN
1852 WF_CORE.CONTEXT('zpb_dc_wf.freeze_template', itemtype, itemkey, to_char(actid), funcmode);
1853 raise;
1854 END freeze_template;
1855
1856 PROCEDURE freeze_worksheet (
1857 itemtype IN varchar2,
1858 itemkey IN varchar2,
1859 actid IN number,
1860 funcmode IN varchar2,
1861 resultout OUT nocopy varchar2
1862 )
1863 IS
1864 l_object_id NUMBER;
1865 l_template_id NUMBER;
1866 l_distribution_method zpb_dc_objects.distribution_method%TYPE;
1867
1868 l_api_version NUMBER;
1869 l_init_msg_list VARCHAR2(1);
1870 l_commit VARCHAR2(1);
1871 l_validation_level NUMBER;
1872 l_return_status VARCHAR2(1);
1873 l_msg_count NUMBER;
1874 l_msg_data VARCHAR2(4000);
1875
1876 l_bpo_id NUMBER;
1877 l_object_user_id NUMBER;
1878 l_distributor_id NUMBER;
1879 l_freeze_user_id NUMBER;
1880 l_approver_id NUMBER;
1881 --
1882 CURSOR worksheet_csr IS
1883 SELECT object_user_id
1884 FROM
1885 (SELECT distributor_user_id, object_user_id
1886 FROM zpb_dc_objects
1887 WHERE template_id = l_template_id
1888 AND object_type = 'W'
1889 )
1890 START with distributor_user_id = l_object_user_id
1891 CONNECT by prior object_user_id = distributor_user_id;
1892
1893
1894 BEGIN
1895
1896 l_api_version := 1.0;
1897 l_init_msg_list := FND_API.G_FALSE;
1898 l_commit := FND_API.G_FALSE;
1899 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1900
1901 IF (funcmode = 'RUN') THEN
1902
1903 l_object_id := wf_engine.GetItemAttrNumber(
1904 Itemtype => ItemType,
1905 Itemkey => ItemKey,
1906 aname => 'DC_OBJECT_ID');
1907
1908 l_template_id := wf_engine.GetItemAttrNumber(
1909 Itemtype => ItemType,
1910 Itemkey => ItemKey,
1911 aname => 'DC_TEMPLATE_ID');
1912
1913 -- If CASCADE, need to freeze all sub ws
1914 SELECT distribution_method,
1915 distributor_user_id,
1916 object_user_id
1917 INTO l_distribution_method,
1918 l_distributor_id,
1919 l_object_user_id
1920 FROM zpb_dc_objects
1921 WHERE object_id = l_object_id;
1922
1923 IF (l_distribution_method = 'CASCADE_DISTRIBUTION') THEN
1924
1925 OPEN worksheet_csr;
1926 LOOP
1927 FETCH worksheet_csr INTO l_freeze_user_id;
1928 EXIT WHEN worksheet_csr%NOTFOUND;
1929
1930 IF (l_freeze_user_id <> l_object_user_id) THEN
1931 UPDATE zpb_dc_objects
1932 SET status = 'FROZEN',
1933 freeze_flag = 'Y',
1934 LAST_UPDATED_BY = fnd_global.USER_ID,
1935 LAST_UPDATE_DATE = SYSDATE,
1936 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1937 WHERE template_id = l_template_id
1938 AND object_user_id = l_freeze_user_id;
1939 END IF;
1940 END LOOP;
1941 CLOSE worksheet_csr;
1942
1943 resultout := 'COMPLETE';
1944 END IF;
1945 resultout := 'COMPLETE';
1946
1947 END IF;
1948
1949 IF ( funcmode = 'CANCEL' ) THEN
1950 resultout := 'COMPLETE';
1951 END IF;
1952
1953 IF (funcmode not in ('RUN','CANCEL')) THEN
1954 resultout := '';
1955 END IF;
1956
1957 EXCEPTION
1958
1959 WHEN NO_DATA_FOUND THEN
1960 WF_CORE.CONTEXT('zpb_dc_wf.freeze_worksheet: no data found', itemtype, itemkey, to_char(actid), funcmode);
1961 raise;
1962
1963 WHEN others THEN
1964 WF_CORE.CONTEXT('zpb_dc_wf.freeze_worksheet', itemtype, itemkey, to_char(actid), funcmode);
1965 raise;
1966 END freeze_worksheet;
1967
1968 PROCEDURE find_approver (
1969 itemtype IN varchar2,
1970 itemkey IN varchar2,
1971 actid IN number,
1972 funcmode IN varchar2,
1973 resultout OUT nocopy varchar2
1974 )
1975 IS
1976 l_approver_type zpb_dc_objects.approver_type%TYPE;
1977 l_object_id number;
1978 BEGIN
1979
1980 IF (funcmode = 'RUN') THEN
1981 l_object_id := wf_engine.GetItemAttrNumber(
1982 Itemtype => ItemType,
1983 Itemkey => ItemKey,
1984 aname => 'DC_OBJECT_ID');
1985
1986 SELECT approver_type
1987 INTO l_approver_type
1988 FROM zpb_dc_objects
1989 WHERE object_id = l_object_id;
1990
1991 wf_engine.SetItemAttrText(
1992 Itemtype => ItemType,
1993 Itemkey => ItemKey,
1994 aname => 'DC_APPROVER_TYPE',
1995 avalue => l_approver_type);
1996
1997 resultout := 'COMPLETE';
1998 END IF;
1999
2000 IF ( funcmode = 'CANCEL' ) THEN
2001 resultout := 'COMPLETE';
2002 END IF;
2003
2004 IF (funcmode not in ('RUN','CANCEL')) THEN
2005 resultout := '';
2006 END IF;
2007 return;
2008
2009 EXCEPTION
2010
2011 WHEN NO_DATA_FOUND THEN
2012 WF_CORE.CONTEXT('zpb_dc_wf.find_approver: no data found', itemtype, itemkey, to_char(actid), funcmode);
2013 raise;
2014
2015 WHEN others THEN
2016 WF_CORE.CONTEXT('zpb_dc_wf.find_approver', itemtype, itemkey, to_char(actid), funcmode);
2017 raise;
2018 END find_approver;
2019
2020 PROCEDURE set_submit_ntf_recipients (
2021 itemtype IN varchar2,
2022 itemkey IN varchar2,
2023 actid IN number,
2024 funcmode IN varchar2,
2025 resultout OUT nocopy varchar2
2026 )
2027 IS
2028 l_approver_type zpb_dc_objects.approver_type%TYPE;
2029 l_dist_method zpb_dc_objects.distribution_method%TYPE;
2030 l_template_name zpb_dc_objects.template_name%TYPE;
2031 l_object_id NUMBER;
2032 l_template_id NUMBER;
2033 l_approver_for_msg fnd_user.description%TYPE;
2034 l_submitter_for_msg fnd_user.description%TYPE;
2035 l_from_name fnd_user.user_name%TYPE;
2036 l_approver fnd_user.user_name%TYPE;
2037 l_submitter fnd_user.user_name%TYPE;
2038 l_exp_days NUMBER := 7;
2039 l_charDate VARCHAR2(20);
2040 l_frozen_rolename VARCHAR2(320);
2041 l_appr_rolename VARCHAR2(320);
2042 l_subtr_rolename VARCHAR2(320);
2043
2044 l_api_version NUMBER;
2045 l_init_msg_list VARCHAR2(1);
2046 l_commit VARCHAR2(1);
2047 l_validation_level NUMBER;
2048 l_return_status VARCHAR2(1);
2049 l_msg_count NUMBER;
2050 l_msg_data VARCHAR2(4000);
2051 l_frzn_role_has_users VARCHAR2(1);
2052
2053 CURSOR approver_csr IS
2054 SELECT fnd.user_name
2055 FROM zpb_dc_objects obj, fnd_user fnd
2056 WHERE obj.object_id = l_object_id
2057 AND obj.approver_user_id = fnd.user_id;
2058
2059 CURSOR submitter_csr IS
2060 SELECT fnd.user_name
2061 FROM zpb_dc_objects obj, fnd_user fnd
2062 WHERE obj.object_id = l_object_id
2063 AND obj.object_user_id = fnd.user_id;
2064
2065 CURSOR approver_for_msg_csr IS
2066 SELECT nvl(fnd.description, fnd.user_name)
2067 FROM zpb_dc_objects obj, fnd_user fnd
2068 WHERE obj.object_id = l_object_id
2069 AND obj.approver_user_id = fnd.user_id;
2070
2071 CURSOR submitter_for_msg_csr IS
2072 SELECT nvl(fnd.description, fnd.user_name)
2073 FROM zpb_dc_objects obj, fnd_user fnd
2074 WHERE obj.object_id = l_object_id
2075 AND obj.object_user_id = fnd.user_id;
2076
2077 BEGIN
2078 IF (funcmode = 'RUN') THEN
2079 l_object_id := wf_engine.GetItemAttrNumber(
2080 Itemtype => ItemType,
2081 Itemkey => ItemKey,
2082 aname => 'DC_OBJECT_ID');
2083
2084 l_template_id := wf_engine.GetItemAttrNumber(
2085 Itemtype => ItemType,
2086 Itemkey => ItemKey,
2087 aname => 'DC_TEMPLATE_ID');
2088
2089 -- Appeover type is 'DISTRIBUTOR' for this release
2090 l_approver_type := wf_engine.GetItemAttrText(
2091 Itemtype => ItemType,
2092 Itemkey => ItemKey,
2093 aname => 'DC_APPROVER_TYPE');
2094
2095 SELECT template_name
2096 INTO l_template_name
2097 FROM zpb_dc_objects
2098 WHERE object_id = l_object_id;
2099
2100 wf_engine.SetItemAttrText(
2101 Itemtype => ItemType,
2102 Itemkey => ItemKey,
2103 aname => 'DC_TEMPLATE_NAME',
2104 avalue => l_template_name);
2105
2106 -- Create the roles for Approver and Submitter users
2107 l_charDate := to_char(sysdate, 'J-SSSSS');
2108 l_appr_rolename := 'ZPB_NOTE_APPR'|| to_char(l_object_id) || '-' || l_charDate;
2109 l_subtr_rolename := 'ZPB_NOTE_SUBTR'|| to_char(l_object_id) || '-' || l_charDate;
2110 zpb_wf_ntf.SetRole(l_appr_rolename, l_exp_days);
2111 zpb_wf_ntf.SetRole(l_subtr_rolename, l_exp_days);
2112 -- Set the recipients of the ntfs --
2113
2114 OPEN approver_csr;
2115 FETCH approver_csr INTO l_approver;
2116 CLOSE approver_csr;
2117
2118 IF (l_approver IS NULL) THEN
2119 SELECT fnd.user_name
2120 INTO l_approver
2121 FROM zpb_dc_objects obj, fnd_user fnd
2122 WHERE obj.object_id = l_object_id
2123 AND obj.distributor_user_id = fnd.user_id;
2124
2125 IF (l_approver IS NULL) THEN -- -100 --
2126 SELECT fnd.user_name
2127 INTO l_approver
2128 FROM zpb_dc_objects obj, fnd_user fnd
2129 WHERE obj.template_id = l_template_id
2130 AND obj.object_type = 'M'
2131 AND obj.object_user_id = fnd.user_id;
2132 END IF;
2133 END IF;
2134
2135 l_appr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_appr_rolename, l_approver);
2136 wf_engine.SetItemAttrText(
2137 Itemtype => ItemType,
2138 Itemkey => ItemKey,
2139 aname => 'DC_APPROVER',
2140 avalue => l_appr_rolename);
2141
2142 OPEN submitter_csr;
2143 FETCH submitter_csr INTO l_submitter;
2144 CLOSE submitter_csr;
2145
2146 l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);
2147 wf_engine.SetItemAttrText(
2148 Itemtype => ItemType,
2149 Itemkey => ItemKey,
2150 aname => 'DC_SUBMITTER',
2151 avalue => l_subtr_rolename);
2152
2153 /* The following is to populate approver
2154 and submitter for ntf msg, user description is used */
2155 OPEN approver_for_msg_csr;
2156 FETCH approver_for_msg_csr INTO l_approver_for_msg;
2157 CLOSE approver_for_msg_csr;
2158
2159 IF (l_approver_for_msg IS NULL) THEN
2160 SELECT nvl(fnd.description, fnd.user_name)
2161 INTO l_approver_for_msg
2162 FROM zpb_dc_objects obj, fnd_user fnd
2163 WHERE obj.object_id = l_object_id
2164 AND obj.distributor_user_id = fnd.user_id;
2165
2166 IF (l_approver_for_msg IS NULL) THEN -- -100 --
2167 SELECT nvl(fnd.description, fnd.user_name)
2168 INTO l_approver_for_msg
2169 FROM zpb_dc_objects obj, fnd_user fnd
2170 WHERE obj.template_id = l_template_id
2171 AND obj.object_type = 'M'
2172 AND obj.object_user_id = fnd.user_id;
2173 END IF;
2174 END IF;
2175
2176 wf_engine.SetItemAttrText(
2177 Itemtype => ItemType,
2178 Itemkey => ItemKey,
2179 aname => 'DC_APPROVER_FOR_MSG',
2180 avalue => l_approver_for_msg);
2181
2182 OPEN submitter_for_msg_csr;
2183 FETCH submitter_for_msg_csr INTO l_submitter_for_msg;
2184 CLOSE submitter_for_msg_csr;
2185
2186 wf_engine.SetItemAttrText(
2187 Itemtype => ItemType,
2188 Itemkey => ItemKey,
2189 aname => 'DC_SUBMITTER_FOR_MSG',
2190 avalue => l_submitter_for_msg);
2191
2192 /* Populate the From field in notification details page
2193 Can not use description here, use user name*/
2194 SELECT fnd.user_name
2195 INTO l_from_name
2196 FROM fnd_user fnd, zpb_dc_objects obj
2197 WHERE obj.object_id = l_object_id
2198 AND obj.object_user_id = fnd.user_id;
2199
2200 wf_engine.SetItemAttrText(
2201 Itemtype => ItemType,
2202 Itemkey => ItemKey,
2203 aname => '#FROM_ROLE',
2204 avalue => l_from_name);
2205
2206 -- Create the role for frozen ws user
2207 l_charDate := to_char(sysdate, 'J-SSSSS');
2208 l_frozen_rolename := 'ZPB_DC_SUB_FZN'|| to_char(l_object_id) || '-' || l_charDate;
2209 zpb_wf_ntf.SetRole(l_frozen_rolename, l_exp_days);
2210 l_frzn_role_has_users := 'N';
2211 FOR frozen_user_rec IN (
2212 SELECT u.user_name as user_name
2213 FROM zpb_dc_objects obj, fnd_user u
2214 WHERE obj.template_id = l_template_id
2215 AND obj.object_type = 'W'
2216 AND obj.status = 'FROZEN'
2217 AND obj.object_user_id = u.user_id)
2218 LOOP
2219 l_frozen_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_frozen_rolename, frozen_user_rec.user_name);
2220 l_frzn_role_has_users := 'Y';
2221 END LOOP;
2222
2223 wf_engine.SetItemAttrText(
2224 Itemtype => ItemType,
2225 Itemkey => ItemKey,
2226 aname => 'DC_FROZEN_WS_USER',
2227 avalue => l_frozen_rolename);
2228
2229 IF (l_frzn_role_has_users = 'Y') THEN
2230 resultout := 'COMPLETE:Y';
2231 ELSE
2232 resultout := 'COMPLETE:N';
2233 END IF;
2234 END IF; -- run mode
2235
2236 IF ( funcmode = 'CANCEL' ) THEN
2237 resultout := 'COMPLETE:Y';
2238 END IF;
2239
2240 IF (funcmode not in ('RUN','CANCEL')) THEN
2241 resultout := '';
2242 END IF;
2243 return;
2244
2245 EXCEPTION
2246
2247 WHEN NO_DATA_FOUND THEN
2248 WF_CORE.CONTEXT('zpb_dc_wf.set_submit_ntf_recipients: no data found', itemtype, itemkey, to_char(actid), funcmode);
2249 raise;
2250
2251 WHEN others THEN
2252 WF_CORE.CONTEXT('zpb_dc_wf.set_submit_ntf_recipients', itemtype, itemkey, to_char(actid), funcmode);
2253 raise;
2254 END set_submit_ntf_recipients ;
2255
2256 PROCEDURE update_aw(
2257 itemtype IN varchar2,
2258 itemkey IN varchar2,
2259 actid IN number,
2260 funcmode IN varchar2,
2261 resultout OUT nocopy varchar2
2262 )
2263 IS
2264 l_req_id NUMBER;
2265 l_template_id NUMBER;
2266 l_object_id NUMBER;
2267 l_object_user_id NUMBER;
2268 l_owner VARCHAR2(30);
2269 l_process_name fnd_new_messages.message_text%TYPE;
2270 l_template_name zpb_dc_objects.template_name%TYPE;
2271
2272 l_user_id NUMBER ;
2273 l_resp_id NUMBER ;
2274 l_respapp_id NUMBER ;
2275 BEGIN
2276
2277 l_user_id := fnd_global.USER_ID;
2278 l_resp_id := fnd_global.RESP_ID;
2279 l_respapp_id := fnd_global.RESP_APPL_ID;
2280 l_owner := fnd_global.user_name;
2281
2282 IF (funcmode = 'RUN') THEN
2283
2284 l_object_id := wf_engine.GetItemAttrNumber(
2285 Itemtype => ItemType,
2286 Itemkey => ItemKey,
2287 aname => 'DC_OBJECT_ID');
2288
2289 l_template_id := wf_engine.GetItemAttrNumber(
2290 Itemtype => ItemType,
2291 Itemkey => ItemKey,
2292 aname => 'DC_TEMPLATE_ID');
2293
2294 l_user_id := wf_engine.GetItemAttrNumber(
2295 Itemtype => ItemType,
2296 Itemkey => ItemKey,
2297 aname => 'FND_USER_ID');
2298
2299 l_resp_id := wf_engine.GetItemAttrNumber(
2300 Itemtype => ItemType,
2301 Itemkey => ItemKey,
2302 aname => 'FND_RESPONSIBILITY_ID');
2303
2304 l_respapp_id := wf_engine.GetItemAttrNumber(
2305 Itemtype => ItemType,
2306 Itemkey => ItemKey,
2307 aname => 'FND_APPLICATION_ID');
2308
2309 SELECT object_user_id, template_name
2310 INTO l_object_user_id, l_template_name
2311 FROM zpb_dc_objects
2312 WHERE object_id = l_object_id;
2313
2314 -- set EPBPerformer to owner name for issue notifications
2315 wf_engine.SetItemAttrText(
2316 Itemtype => ItemType,
2317 Itemkey => ItemKey,
2318 aname => 'EPBPERFORMER',
2319 avalue => l_owner);
2320
2321 -- Get Message from Fnd_Messages
2322 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_DC_SUBMIT_ISSUE_MSG');
2323 l_process_name := FND_MESSAGE.GET;
2324
2325 wf_engine.SetItemAttrText(
2326 Itemtype => ItemType,
2327 Itemkey => ItemKey,
2328 aname => 'DC_PROCESS_NAME',
2329 avalue => l_process_name);
2330
2331 wf_engine.SetItemAttrText(
2332 Itemtype => ItemType,
2333 Itemkey => ItemKey,
2334 aname => 'DC_TEMPLATE_NAME',
2335 avalue => l_template_name);
2336
2337 -- Push data to shared AW --
2338 fnd_global.apps_initialize(l_user_id,l_resp_id,l_respapp_id);
2339 zpb_wf.submit_to_shared(l_object_user_id, l_template_id, l_req_id);
2340
2341 -- Set the values for wait for concurrent program
2342 wf_engine.SetItemAttrNumber(
2343 Itemtype => ItemType,
2344 Itemkey => ItemKey,
2345 aname => 'REQUEST_ID',
2346 avalue => l_req_id);
2347
2348 IF l_req_id = 0 THEN
2349 resultout := 'COMPLETE:N';
2350 ELSE
2351 resultout := 'COMPLETE:Y';
2352 END IF;
2353 END IF;
2354
2355 IF ( funcmode = 'CANCEL' ) THEN
2356 resultout := 'COMPLETE:Y';
2357 END IF;
2358
2359 IF (funcmode not in ('RUN','CANCEL')) THEN
2360 resultout := '';
2361 END IF;
2362 return;
2363
2364 EXCEPTION
2365
2366 WHEN NO_DATA_FOUND THEN
2367 WF_CORE.CONTEXT('zpb_dc_wf.update_aw: no data found', itemtype, itemkey, to_char(actid), funcmode);
2368 raise;
2369
2370 WHEN others THEN
2371 WF_CORE.CONTEXT('zpb_dc_wf.update_aw', itemtype, itemkey, to_char(actid), funcmode);
2372 raise;
2373 END update_aw;
2374
2375 PROCEDURE check_update_aw_type (
2376 itemtype IN varchar2,
2377 itemkey IN varchar2,
2378 actid IN number,
2379 funcmode IN varchar2,
2380 resultout OUT nocopy varchar2
2381 )
2382 IS
2383 l_object_type VARCHAR2(10);
2384 BEGIN
2385
2386 IF (funcmode = 'RUN') THEN
2387
2388 l_object_type := wf_engine.GetItemAttrText(
2389 Itemtype => ItemType,
2390 Itemkey => ItemKey,
2391 aname => 'DC_OBJECT_TYPE');
2392
2393 IF (l_object_type = 'E') THEN
2394 resultout := 'COMPLETE:TEMPLATE_DIRECT_APPR';
2395 ELSE
2396 resultout := 'COMPLETE:WORKSHEET_DIRECT_NO_APPR';
2397 END IF;
2398 END IF;
2399
2400 IF ( funcmode = 'CANCEL' ) THEN
2401 resultout := 'COMPLETE';
2402 END IF;
2403
2404 IF (funcmode not in ('RUN','CANCEL')) THEN
2405 resultout := '';
2406 END IF;
2407 return;
2408
2409 EXCEPTION
2410
2411 WHEN NO_DATA_FOUND THEN
2412 WF_CORE.CONTEXT('zpb_dc_wf.check_update_aw_type: no data found', itemtype, itemkey, to_char(actid), funcmode);
2413 raise;
2414
2415 WHEN others THEN
2416 WF_CORE.CONTEXT('zpb_dc_wf.check_update_aw_type', itemtype, itemkey, to_char(actid), funcmode);
2417 raise;
2418 END check_update_aw_type;
2419
2420 PROCEDURE raise_approval_event(
2421 p_api_version IN NUMBER,
2422 p_init_msg_list IN VARCHAR2,
2423 p_commit IN VARCHAR2,
2424 p_validation_level IN NUMBER,
2425 x_return_status OUT NOCOPY VARCHAR2,
2426 x_msg_count OUT NOCOPY NUMBER,
2427 x_msg_data OUT NOCOPY VARCHAR2,
2428 --
2429 p_object_id IN number,
2430 p_approver_user_id IN number,
2431 p_approval_message IN varchar2
2432 )
2433 IS
2434
2435 l_api_name CONSTANT VARCHAR2(30) := 'raise_approval_event' ;
2436 l_api_version CONSTANT NUMBER := 1.0 ;
2437 l_init_msg_list VARCHAR2(1);
2438 l_commit VARCHAR2(1);
2439 l_validation_level NUMBER;
2440 l_return_status VARCHAR2(1);
2441 l_msg_count NUMBER;
2442 l_msg_data VARCHAR2(4000);
2443 --
2444 l_submitter fnd_user.user_name%TYPE;
2445 l_approver_for_msg fnd_user.description%TYPE;
2446 l_template_name zpb_dc_objects.template_name%TYPE;
2447 l_substr_templ_name VARCHAR2(140);
2448 l_template_id NUMBER;
2449 l_from_name fnd_user.user_name%TYPE;
2450 l_sequence NUMBER;
2451 l_approval_type VARCHAR2(30);
2452 l_char_date VARCHAR2(30);
2453 l_exp_days NUMBER := 7;
2454 l_frozen_rolename VARCHAR2(320);
2455 l_appr_rolename VARCHAR2(320);
2456 l_subtr_rolename VARCHAR2(320);
2457
2458 --
2459 l_item_type VARCHAR2(100) ;
2460 l_item_key VARCHAR2(240) ;
2461 l_event_t wf_event_t;
2462 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2463 --
2464 BEGIN
2465
2466 SAVEPOINT Raise_Approval_Event ;
2467
2468 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2469 p_api_version,
2470 l_api_name,
2471 G_PKG_NAME )
2472 THEN
2473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2474 END IF;
2475
2476
2477 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2478 FND_MSG_PUB.initialize ;
2479 END IF;
2480
2481 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2482
2483 -- Initialize the parameters
2484 l_item_type := 'ZPBDC' ;
2485 l_init_msg_list := FND_API.G_FALSE;
2486 l_commit := FND_API.G_FALSE;
2487 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
2488
2489 SELECT nvl(description,user_name)
2490 INTO l_approver_for_msg
2491 FROM fnd_user
2492 WHERE user_id= p_approver_user_id;
2493
2494 SELECT user_name
2495 INTO l_from_name
2496 FROM fnd_user
2497 WHERE user_id= p_approver_user_id;
2498
2499 SELECT obj.template_name, fnd.user_name, obj.template_id
2500 INTO l_template_name, l_submitter, l_template_id
2501 FROM zpb_dc_objects obj, fnd_user fnd
2502 WHERE obj.object_id = p_object_id
2503 AND obj.object_user_id = fnd.user_id;
2504
2505 SELECT ZPB_DC_WF_PROCESSES_S.nextval
2506 INTO l_sequence
2507 FROM dual;
2508
2509 l_substr_templ_name := substr(l_template_name,1,140);
2510 l_approval_type := 'Approve Worksheet';
2511 l_char_date := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
2512 l_item_key := to_char(l_sequence) ||
2513 '_' || l_approval_type||
2514 '_' || l_substr_templ_name ||
2515 '_' || l_char_date;
2516
2517 FND_FILE.Put_Line ( FND_FILE.LOG, 'WF key ' || l_item_key ) ;
2518
2519 -- Create the roles for Approver and Submitter users
2520 l_char_date := to_char(sysdate, 'J-SSSSS');
2521 l_appr_rolename := 'ZPB_APP_APPR'|| to_char(p_object_id) || '-' || l_char_date;
2522 l_subtr_rolename := 'ZPB_APP_SUBTR'|| to_char(p_object_id) || '-' || l_char_date;
2523 zpb_wf_ntf.SetRole(l_appr_rolename, l_exp_days);
2524 zpb_wf_ntf.SetRole(l_subtr_rolename, l_exp_days);
2525
2526 wf_event.AddParameterToList(
2527 p_name => '#FROM_ROLE',
2528 p_value => l_from_name,
2529 p_parameterlist=> l_parameter_list);
2530
2531 wf_event.AddParameterToList(
2532 p_name => 'DC_APPROVER_FOR_MSG',
2533 p_value => l_approver_for_msg,
2534 p_parameterlist=> l_parameter_list);
2535
2536 l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);
2537 wf_event.AddParameterToList(
2538 p_name => 'DC_SUBMITTER',
2539 p_value => l_subtr_rolename,
2540 p_parameterlist=> l_parameter_list);
2541
2542 wf_event.AddParameterToList(
2543 p_name => 'DC_TEMPLATE_NAME',
2544 p_value => l_template_name,
2545 p_parameterlist=> l_parameter_list);
2546
2547 wf_event.AddParameterToList(
2548 p_name => 'DC_APPROVAL_MESSAGE',
2549 p_value => p_approval_message,
2550 p_parameterlist=> l_parameter_list);
2551
2552 -- set fnd values so workflow process can use this values
2553 -- since they can now be run in deferred mode
2554 -- working:fnd_global.apps_initialize(1005258,57124,210);
2555
2556 wf_event.AddParameterToList(p_name=>'FND_USER_ID',
2557 p_value=> fnd_global.user_id,
2558 p_parameterlist=>l_parameter_list);
2559
2560 wf_event.AddParameterToList(p_name=>'FND_APPLICATION_ID',
2561 p_value=> fnd_global.resp_appl_id,
2562 p_parameterlist=>l_parameter_list);
2563
2564 wf_event.AddParameterToList(p_name=>'FND_RESPONSIBILITY_ID',
2565 p_value=> fnd_global.resp_id,
2566 p_parameterlist=>l_parameter_list);
2567
2568 -- wf debugging
2569 wf_log_pkg.wf_debug_flag := TRUE;
2570 -- raise the event
2571 wf_event.raise(p_event_name => 'oracle.apps.zpb.dc.worksheet.approve',
2572 p_event_key => l_item_key,
2573 p_parameters => l_parameter_list);
2574
2575 l_parameter_list.delete;
2576
2577 COMMIT;
2578
2579 ZPB_DC_OBJECTS_PVT.Populate_Approvers(
2580 p_api_version => l_api_version,
2581 p_init_msg_list => l_init_msg_list,
2582 p_commit => l_commit,
2583 p_validation_level => l_validation_level,
2584 x_return_status => l_return_status,
2585 x_msg_count => l_msg_count,
2586 x_msg_data => l_msg_data,
2587 --
2588 p_object_id => p_object_id,
2589 p_approver_user_id => p_approver_user_id,
2590 p_approval_date => sysdate);
2591
2592 --
2593 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2594 p_data => x_msg_data ) ;
2595 --
2596 EXCEPTION
2597 --
2598 when FND_API.G_EXC_ERROR then
2599 --
2600 rollback to Raise_Approval_Event ;
2601 x_return_status := FND_API.G_RET_STS_ERROR;
2602 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2603 p_data => x_msg_data);
2604 --
2605 when FND_API.G_EXC_UNEXPECTED_ERROR then
2606 --
2607 rollback to Raise_Approval_Event ;
2608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2609 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2610 p_data => x_msg_data);
2611 --
2612 when OTHERS then
2613 --
2614 rollback to Raise_Approval_Event ;
2615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2616 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2617 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2618 l_api_name);
2619 END if;
2620 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2621 p_data => x_msg_data);
2622 --
2623
2624
2625 END raise_approval_event;
2626
2627 PROCEDURE raise_rejection_event(
2628 p_api_version IN NUMBER,
2629 p_init_msg_list IN VARCHAR2,
2630 p_commit IN VARCHAR2,
2631 p_validation_level IN NUMBER,
2632 x_return_status OUT NOCOPY VARCHAR2,
2633 x_msg_count OUT NOCOPY NUMBER,
2634 x_msg_data OUT NOCOPY VARCHAR2,
2635 --
2636 p_object_id IN number,
2637 p_approver_user_id IN number,
2638 p_rejection_message IN varchar2
2639 )
2640 IS
2641
2642 l_api_name CONSTANT VARCHAR2(30) := 'raise_rejection_event' ;
2643 l_api_version CONSTANT NUMBER := 1.0 ;
2644 l_init_msg_list VARCHAR2(1);
2645 l_commit VARCHAR2(1);
2646 l_validation_level NUMBER;
2647 l_return_status VARCHAR2(1);
2648 l_msg_count NUMBER;
2649 l_msg_data VARCHAR2(4000);
2650 --
2651 l_submitter fnd_user.user_name%TYPE;
2652 l_approver_for_msg fnd_user.description%TYPE;
2653 l_template_name zpb_dc_objects.template_name%TYPE;
2654 l_substr_templ_name VARCHAR2(140);
2655 l_template_id NUMBER;
2656 l_from_name fnd_user.user_name%TYPE;
2657 l_sequence NUMBER;
2658 l_rejection_type VARCHAR2(30);
2659 l_char_date VARCHAR2(30);
2660 l_exp_days NUMBER := 7;
2661 l_frozen_rolename VARCHAR2(320);
2662 l_appr_rolename VARCHAR2(320);
2663 l_subtr_rolename VARCHAR2(320);
2664 --
2665 l_item_type VARCHAR2(100) ;
2666 l_item_key VARCHAR2(240) ;
2667 l_event_t wf_event_t;
2668 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2669 --
2670 BEGIN
2671
2672 SAVEPOINT Raise_Rejection_Event ;
2673
2674 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2675 p_api_version,
2676 l_api_name,
2677 G_PKG_NAME )
2678 THEN
2679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2680 END IF;
2681
2682
2683 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2684 FND_MSG_PUB.initialize ;
2685 END IF;
2686
2687 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2688
2689 -- Initialize the parameters
2690 l_init_msg_list := FND_API.G_FALSE;
2691 l_commit := FND_API.G_FALSE;
2692 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
2693
2694
2695 SELECT ZPB_DC_WF_PROCESSES_S.nextval
2696 INTO l_item_key
2697 FROM dual;
2698
2699 SELECT nvl(description,user_name)
2700 INTO l_approver_for_msg
2701 FROM fnd_user
2702 WHERE user_id= p_approver_user_id;
2703
2704 SELECT obj.template_name, fnd.user_name, template_id
2705 INTO l_template_name, l_submitter, l_template_id
2706 FROM zpb_dc_objects obj, fnd_user fnd
2707 WHERE obj.object_id = p_object_id
2708 AND obj.object_user_id = fnd.user_id;
2709
2710 -- Populate the From field in notification details page
2711 SELECT user_name
2712 INTO l_from_name
2713 FROM fnd_user
2714 WHERE user_id= p_approver_user_id;
2715
2716 SELECT ZPB_DC_WF_PROCESSES_S.nextval
2717 INTO l_sequence
2718 FROM dual;
2719
2720 l_substr_templ_name := substr(l_template_name,1,140);
2721 l_rejection_type := 'Reject Worksheet';
2722 l_char_date := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
2723 l_item_key := to_char(l_sequence) ||
2724 '_' || l_rejection_type||
2725 '_' || l_substr_templ_name ||
2726 '_' || l_char_date;
2727
2728 FND_FILE.Put_Line ( FND_FILE.LOG, 'WF key ' || l_item_key ) ;
2729
2730 -- Create the roles for Approver and Submitter users
2731 l_char_date := to_char(sysdate, 'J-SSSSS');
2732 l_appr_rolename := 'ZPB_REJ_APPR'|| to_char(p_object_id) || '-' || l_char_date;
2733 l_subtr_rolename := 'ZPB_REJ_SUBTR'|| to_char(p_object_id) || '-' || l_char_date;
2734 zpb_wf_ntf.SetRole(l_appr_rolename, l_exp_days);
2735 zpb_wf_ntf.SetRole(l_subtr_rolename, l_exp_days);
2736
2737 wf_event.AddParameterToList(
2738 p_name => '#FROM_ROLE',
2739 p_value => l_from_name,
2740 p_parameterlist=> l_parameter_list);
2741
2742 wf_event.AddParameterToList(
2743 p_name => 'DC_APPROVER_FOR_MSG',
2744 p_value => l_approver_for_msg,
2745 p_parameterlist=> l_parameter_list);
2746
2747 l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);
2748 wf_event.AddParameterToList(
2749 p_name => 'DC_SUBMITTER',
2750 p_value => l_subtr_rolename,
2751 p_parameterlist=> l_parameter_list);
2752
2753 wf_event.AddParameterToList(
2754 p_name => 'DC_TEMPLATE_NAME',
2755 p_value => l_template_name,
2756 p_parameterlist=> l_parameter_list);
2757
2758 wf_event.AddParameterToList(
2759 p_name => 'DC_REJECTION_MESSAGE',
2760 p_value => p_rejection_message,
2761 p_parameterlist=> l_parameter_list);
2762
2763 -- set fnd values so workflow process can use this values
2764 -- since they can now be run in deferred mode
2765 -- working:fnd_global.apps_initialize(1005258,57124,210);
2766
2767 wf_event.AddParameterToList(p_name=>'FND_USER_ID',
2768 p_value=> fnd_global.user_id,
2769 p_parameterlist=>l_parameter_list);
2770
2771 wf_event.AddParameterToList(p_name=>'FND_APPLICATION_ID',
2772 p_value=> fnd_global.resp_appl_id,
2773 p_parameterlist=>l_parameter_list);
2774
2775 wf_event.AddParameterToList(p_name=>'FND_RESPONSIBILITY_ID',
2776 p_value=> fnd_global.resp_id,
2777 p_parameterlist=>l_parameter_list);
2778
2779 -- wf debugging
2780 wf_log_pkg.wf_debug_flag := TRUE;
2781 -- raise the event
2782 wf_event.raise(p_event_name => 'oracle.apps.zpb.dc.worksheet.reject',
2783 p_event_key => l_item_key,
2784 p_parameters => l_parameter_list);
2785
2786 l_parameter_list.delete;
2787
2788 COMMIT;
2789
2790 ZPB_DC_OBJECTS_PVT.Populate_Approvers(
2791 p_api_version => l_api_version,
2792 p_init_msg_list => l_init_msg_list,
2793 p_commit => l_commit,
2794 p_validation_level => l_validation_level,
2795 x_return_status => l_return_status,
2796 x_msg_count => l_msg_count,
2797 x_msg_data => l_msg_data,
2798 --
2799 p_object_id => p_object_id,
2800 p_approver_user_id => p_approver_user_id,
2801 p_approval_date => sysdate);
2802
2803 --
2804 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2805 p_data => x_msg_data ) ;
2806 --
2807 EXCEPTION
2808 --
2809 when FND_API.G_EXC_ERROR then
2810 --
2811 rollback to Raise_Rejection_Event ;
2812 x_return_status := FND_API.G_RET_STS_ERROR;
2813 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2814 p_data => x_msg_data);
2815 --
2816 when FND_API.G_EXC_UNEXPECTED_ERROR then
2817 --
2818 rollback to Raise_Rejection_Event ;
2819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2820 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2821 p_data => x_msg_data);
2822 --
2823 when OTHERS then
2824 --
2825 rollback to Raise_Rejection_Event ;
2826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2828 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2829 l_api_name);
2830 END if;
2831 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2832 p_data => x_msg_data);
2833 --
2834
2835
2836 END raise_rejection_event;
2837
2838 PROCEDURE set_worksheet_status (
2839 itemtype IN varchar2,
2840 itemkey IN varchar2,
2841 actid IN number,
2842 funcmode IN varchar2,
2843 resultout OUT nocopy varchar2
2844 )
2845 IS
2846 l_object_id NUMBER;
2847 l_multiple_submissions_flag VARCHAR2(1);
2848 BEGIN
2849
2850 l_object_id := 0;
2851 IF (funcmode = 'RUN') THEN
2852
2853 l_object_id := wf_engine.GetItemAttrNumber(
2854 Itemtype => ItemType,
2855 Itemkey => ItemKey,
2856 aname => 'DC_OBJECT_ID');
2857
2858 --find if multiple submissions are allowed
2859 --If multiple submission are allowed - only update status
2860 --else update status and freeze flag
2861 SELECT multiple_submissions_flag
2862 INTO l_multiple_submissions_flag
2863 FROM zpb_dc_objects
2864 WHERE object_id = l_object_id;
2865
2866 IF(l_multiple_submissions_flag <>'Y')
2867 THEN
2868 UPDATE zpb_dc_objects
2869 SET status = 'SUBMITTED_TO_SHARED',
2870 freeze_flag = 'Y',
2871 LAST_UPDATED_BY = fnd_global.USER_ID,
2872 LAST_UPDATE_DATE = SYSDATE,
2873 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2874 WHERE object_id = l_object_id;
2875 ELSE
2876 UPDATE zpb_dc_objects
2877 SET status = 'SUBMITTED_TO_SHARED',
2878 LAST_UPDATED_BY = fnd_global.USER_ID,
2879 LAST_UPDATE_DATE = SYSDATE,
2880 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2881 WHERE object_id = l_object_id;
2882
2883 END IF;
2884
2885
2886 resultout := 'COMPLETE';
2887 END IF;
2888
2889 IF ( funcmode = 'CANCEL' ) THEN
2890 resultout := 'COMPLETE';
2891 END IF;
2892
2893 IF (funcmode not in ('RUN','CANCEL')) THEN
2894 resultout := '';
2895 END IF;
2896 return;
2897
2898 EXCEPTION
2899
2900 WHEN NO_DATA_FOUND THEN
2901 WF_CORE.CONTEXT('zpb_dc_wf.set_worksheet_status: no data found', itemtype, itemkey, to_char(actid), funcmode);
2902 raise;
2903
2904 WHEN others THEN
2905 WF_CORE.CONTEXT('zpb_dc_wf.set_worksheet_status', itemtype, itemkey, to_char(actid), funcmode);
2906 raise;
2907 END set_worksheet_status;
2908
2909
2910 PROCEDURE unblock_manage_submission (
2911 itemtype IN varchar2,
2912 itemkey IN varchar2,
2913 actid IN number,
2914 funcmode IN varchar2,
2915 resultout OUT nocopy varchar2
2916 )
2917 IS
2918 l_object_id NUMBER;
2919 l_item_key VARCHAR2(4000);
2920
2921 BEGIN
2922
2923 l_object_id := 0;
2924 IF (funcmode = 'RUN') THEN
2925
2926 l_object_id := wf_engine.GetItemAttrNumber(
2927 Itemtype => ItemType,
2928 Itemkey => ItemKey,
2929 aname => 'DC_OBJECT_ID');
2930
2931 -- Find the item key manage submission task that is managing the submissions
2932 -- of this template
2933 SELECT task.item_key
2934 INTO l_item_key
2935 FROM zpb_dc_objects obj,zpb_analysis_cycle_tasks task,zpb_task_parameters param
2936 WHERE obj.object_id = l_object_id
2937 AND task.analysis_cycle_id = obj.ac_instance_id
2938 AND task.task_id = param.task_id
2939 AND param.name = 'SUBMISSION_TEMPLATE_ID'
2940 AND obj.ac_template_id = to_number(param.value)
2941 AND task.wf_process_name = 'MANAGE_SUBMISSION';
2942
2943
2944 --Unblock the manage submissions workflow
2945 wf_engine.completeactivity('EPBCYCLE',l_item_key,'BLOCK',NULL);
2946
2947 resultout := 'COMPLETE';
2948 END IF;
2949
2950 IF ( funcmode = 'CANCEL' ) THEN
2951 resultout := 'COMPLETE';
2952 END IF;
2953
2954 IF (funcmode not in ('RUN','CANCEL')) THEN
2955 resultout := '';
2956 END IF;
2957 return;
2958
2959 EXCEPTION
2960
2961 WHEN NO_DATA_FOUND THEN
2962 WF_CORE.CONTEXT('zpb_dc_wf.unblock_manage_submission: no data found', itemtype, itemkey, to_char(actid), funcmode);
2963 raise;
2964
2965 WHEN others THEN
2966 WF_CORE.CONTEXT('zpb_dc_wf.unblock_manage_submission', itemtype, itemkey, to_char(actid), funcmode);
2967 raise;
2968 END unblock_manage_submission;
2969
2970 PROCEDURE check_all_ws_submitted (
2971 itemtype IN varchar2,
2972 itemkey IN varchar2,
2973 actid IN number,
2974 funcmode IN varchar2,
2975 resultout OUT nocopy varchar2
2976 )
2977 IS
2978 l_object_id NUMBER;
2979 l_template_id NUMBER;
2980 l_ws_status_count NUMBER;
2981 l_multiple_submissions_flag VARCHAR2(1);
2982 BEGIN
2983
2984 l_object_id := 0;
2985 IF (funcmode = 'RUN') THEN
2986
2987 l_object_id := wf_engine.GetItemAttrNumber(
2988 Itemtype => ItemType,
2989 Itemkey => ItemKey,
2990 aname => 'DC_OBJECT_ID');
2991 -- get the template_id
2992 SELECT template_id
2993 INTO l_template_id
2994 FROM zpb_dc_objects
2995 WHERE object_id = l_object_id;
2996
2997 --check if all the worksheet for this template have been submitted
2998 SELECT count(*)
2999 INTO l_ws_status_count
3000 FROM zpb_dc_objects obj , fnd_user usr
3001 WHERE obj.object_user_id = usr.user_id
3002 AND l_template_id = obj.template_id
3003 AND obj.object_type = 'W'
3004 AND obj.status NOT IN ('SUBMITTED_TO_SHARED')
3005 AND (usr.end_date is null OR usr.end_date > sysdate);
3006
3007 --Find if multiple submissions are allowed
3008 SELECT multiple_submissions_flag
3009 INTO l_multiple_submissions_flag
3010 FROM zpb_dc_objects
3011 WHERE object_id = l_object_id;
3012
3013 --if all the worksheet are not submitted to shared or multiple submission are allowed
3014 -- take the No transition.
3015 IF(l_ws_status_count > 0 OR l_multiple_submissions_flag = 'Y') THEN
3016 resultout := 'COMPLETE:N';
3017 ELSE
3018 resultout := 'COMPLETE:Y';
3019 END IF;
3020 END IF;
3021
3022 IF ( funcmode = 'CANCEL' ) THEN
3023 resultout := 'COMPLETE:Y';
3024 END IF;
3025
3026 IF (funcmode not in ('RUN','CANCEL')) THEN
3027 resultout := '';
3028 END IF;
3029 return;
3030
3031 EXCEPTION
3032
3033 WHEN NO_DATA_FOUND THEN
3034 WF_CORE.CONTEXT('zpb_dc_wf.check_all_ws_submitted: no data found', itemtype, itemkey, to_char(actid), funcmode);
3035 raise;
3036
3037 WHEN others THEN
3038 WF_CORE.CONTEXT('zpb_dc_wf.check_all_ws_submitted', itemtype, itemkey, to_char(actid), funcmode);
3039 raise;
3040 END check_all_ws_submitted;
3041
3042
3043 END ZPB_DC_WF ;