[Home] [Help]
PACKAGE BODY: APPS.CE_BASIG_WF
Source
1 package body CE_BASIG_WF as
2 /* $Header: cebasigwfb.pls 120.2 2006/01/18 20:23:02 sspoonen noship $ */
3
4
5 G_signatory_id NUMBER(15);
6 G_signatory_history_id NUMBER(15);
7 G_approver_person_id NUMBER(15);
8 G_rowid varchar2(20);
9
10 g_name varchar2(100);
11 g_display_name varchar2(100);
12 g_requester_id number;
13 g_single_limit_amount NUMBER;
14 g_joint_limit_amount NUMBER;
15 l_signatory_id NUMBER;
16 g_signatory_name VARCHAR2(100);
17 g_bank_account_name CE_BANK_ACCOUNTS.BANK_ACCOUNT_NAME%TYPE;
18 g_currency_code FND_CURRENCIES.CURRENCY_CODE%TYPE;
19 g_org_id NUMBER(15);
20
21 g_start_date DATE;
22 g_end_date DATE;
23 g_other_limits CE_BA_SIGNATORIES.OTHER_LIMITS%TYPE;
24 g_person_type CE_BA_SIGNATORIES_V.person_type%TYPE;
25 g_person_job CE_BA_SIGNATORIES_V.person_job%TYPE;
26 g_person_org_name CE_BA_SIGNATORIES_V.person_org_name%TYPE;
27 g_person_location CE_BA_SIGNATORIES_V.person_location%type;
28 g_bank_branch_name ce_bank_branches_v.bank_branch_name%type;
29 g_bank_account_number ce_bank_accounts.bank_Account_num%type;
30 g_signer_group CE_BA_SIGNATORIES_V.signer_group%type;
31
32
33
34 PROCEDURE initialize
35 (fndApplicationIdIn in integer,
36 transactionIdIn in varchar2,
37 transactionTypeIn in varchar2 default null)
38
39 IS
40 ameappid number;
41 l_org_id number(15);
42 BEGIN
43 FND_PROFILE.get('ORG_ID',l_org_id);
44 FND_CLIENT_INFO.set_org_context(l_org_id);
45 fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
46 fnd_profile.value('RESP_ID'),
47 fnd_profile.value('RESP_APPL_ID'));
48
49 ame_api.clearAllApprovals(applicationIdIn => fndApplicationIdIn,
50 transactionIdIn => transactionidin,
51 transactionTypeIn => transactionTypeIn);
52 END;
53
54 procedure SELECT_NEXT_APPROVER(
55 itemtype in varchar2,
56 itemkey in varchar2,
57 actid in number,
58 funcmode in varchar2,
59 result in out NOCOPY varchar2)
60 is
61 tempApprover ame_util.approverRecord;
62 l_emp_id number;
63 l_user_name fnd_user.user_name%TYPE;
64
65 begin
66
67 --
68 -- RUN mode - normal process execution
69 --
70 if (funcmode = 'RUN') then
71
72 ame_api.getNextApprover(applicationIdIn => 260,
73 transactionIdIn => itemkey,
74 transactionTypeIn => itemtype,
75 nextApproverOut => tempApprover);
76
77 IF(tempApprover.user_id IS NULL AND
78 tempApprover.person_id is null) THEN
79 result := 'COMPLETE:F';
80 return;
81 ELSE
82 WF_DIRECTORY.getusername('PER',tempApprover.person_id,
83 g_name, g_display_name);
84
85 WF_ENGINE.SetItemAttrText(itemtype,
86 itemkey,
87 'APPROVER_NAME',
88 g_name);
89
90 WF_ENGINE.SetItemAttrText(itemtype,
91 itemkey,
92 'APPROVAL_DISPLAY_NAME',
93 g_display_name);
94
95 WF_ENGINE.SetItemAttrNumber(itemtype,
96 itemkey,
97 'APPROVER_PERSON_ID',
98 tempapprover.person_id);
99
100 result := 'COMPLETE:T';
101 return;
102 END IF;
103 END IF;
104 if (funcmode = 'CANCEL') then
105
106 -- your cancel code goes here
107 null;
108
109 -- no result needed
110 result := 'COMPLETE';
111 return;
112 end if;
113
114 result := '';
115 return;
116
117 exception
118 when others then
119 wf_core.context('CEBASIG', 'SELECT_NEXT_APPROVER',
120 itemtype, itemkey, to_char(actid), funcmode);
121 raise;
122 end SELECT_NEXT_APPROVER;
123
124 procedure UPDATE_SIGNATORY_HISTORY_APPR(
125 itemtype in varchar2,
126 itemkey in varchar2,
127 actid in number,
128 funcmode in varchar2,
129 result in out NOCOPY varchar2)
130 is
131 begin
132
133 --
134 -- RUN mode - normal process execution
135 --
136 if (funcmode = 'RUN') then
137
138 G_signatory_id := WF_ENGINE.GetItemAttrNumber(
139 itemtype,
140 itemkey,
141 'SIGNATORY_ID');
142
143 G_approver_person_id := WF_ENGINE.GetItemAttrNumber(
144 itemtype,
145 itemkey,
146 'APPROVER_PERSON_ID');
147
148 ame_api.updateApprovalStatus2
149 (applicationIdIn => 260,
150 transactionIdIn => itemkey,
151 approvalstatusin =>'APPROVED',
152 approverpersonidIn => g_approver_person_id,
153 transactionTypeIn => 'CEBASIG',
154 forwardeeIn => ame_util.emptyApproverRecord);
155
156 Insert_history_record('APPROVED');
157
158 result := 'COMPLETE';
159 return;
160 end if;
161
162 if (funcmode = 'CANCEL') then
163
164 -- your cancel code goes here
165 null;
166
167 -- no result needed
168 result := 'COMPLETE';
169 return;
170 end if;
171
172 result := '';
173 return;
174
175 exception
176 when others then
177 wf_core.context('CEBASIG', 'UPDATE_SIGNATORY_HISTORY_APPR',
178 itemtype, itemkey, to_char(actid), funcmode);
179 raise;
180 end UPDATE_SIGNATORY_HISTORY_APPR;
181
182 procedure UPDATE_SIGNATORY_HISTORY_REJ(
183 itemtype in varchar2,
184 itemkey in varchar2,
185 actid in number,
186 funcmode in varchar2,
187 result in out NOCOPY varchar2)
188 is
189 begin
190
191 --
192 -- RUN mode - normal process execution
193 --
194 if (funcmode = 'RUN') then
195
196
197 G_signatory_id := WF_ENGINE.GetItemAttrNumber(
198 itemtype,
199 itemkey,
200 'SIGNATORY_ID');
201
202 G_approver_person_id := WF_ENGINE.GetItemAttrText(
203 itemtype,
204 itemkey,
205 'APPROVER_PERSON_ID');
206
207 Insert_history_record('REJECTED');
208
209 ame_api.updateApprovalStatus2
210 (applicationIdIn => 260,
211 transactionIdIn => itemkey,
212 approvalstatusin =>'REJECTED',
213 approverpersonidIn => g_approver_person_id,
214 transactionTypeIn => 'CEBASIG',
215 forwardeeIn => ame_util.emptyApproverRecord);
216
217 result := 'COMPLETE';
218 return;
219 end if;
220
221 if (funcmode = 'CANCEL') then
222
223 -- your cancel code goes here
224 null;
225
226 -- no result needed
227 result := 'COMPLETE';
228 return;
229 end if;
230
231 result := '';
232 return;
233
234 exception
235 when others then
236 wf_core.context('CEBASIG', 'UPDATE_SIGNATORY_HISTORY_REJ',
237 itemtype, itemkey, to_char(actid), funcmode);
238 raise;
239 end UPDATE_SIGNATORY_HISTORY_REJ;
240
241 procedure APPROVE_SIGNATORY(
242 itemtype in varchar2,
243 itemkey in varchar2,
244 actid in number,
245 funcmode in varchar2,
246 result in out NOCOPY varchar2)
247 is
248 l_signatory_id NUMBER(15);
249 begin
250
251 --
252 -- RUN mode - normal process execution
253 --
254 if (funcmode = 'RUN') then
255 l_signatory_id := WF_ENGINE.GetItemAttrNumber(itemtype,
256 itemkey,
257 'SIGNATORY_ID');
258 UPDATE ce_ba_signatories
259 SET status = 'APPROVED'
260 WHERE signatory_id = l_signatory_id;
261
262 result := 'COMPLETE';
263 return;
264 end if;
265
266 if (funcmode = 'CANCEL') then
267
268 -- your cancel code goes here
269 null;
270
271 -- no result needed
272 result := 'COMPLETE';
273 return;
274 end if;
275
276 result := '';
277 return;
278
279 exception
280 when others then
281 wf_core.context('CE_BASIG', 'APPROVE_SIGNATORY',
282 itemtype, itemkey, to_char(actid), funcmode);
283 raise;
284 end APPROVE_SIGNATORY;
285
286 procedure REJECT_SIGNATORY(
287 itemtype in varchar2,
288 itemkey in varchar2,
289 actid in number,
290 funcmode in varchar2,
291 result in out NOCOPY varchar2)
292 is
293 l_signatory_id NUMBER(15);
294 begin
295
296 --
297 -- RUN mode - normal process execution
298 --
299 if (funcmode = 'RUN') then
300
301 l_signatory_id := WF_ENGINE.GetItemAttrNumber(itemtype,
302 itemkey,
303 'SIGNATORY_ID');
304
305 UPDATE ce_ba_signatories
306 SET status = 'REJECTED'
307 WHERE signatory_id = l_signatory_id;
308
309 result := 'COMPLETE';
310 return;
311 end if;
312
313
314 --
315 -- CANCEL mode - activity 'compensation'
316 if (funcmode = 'CANCEL') then
317
318 -- your cancel code goes here
319 null;
320
321 -- no result needed
322 result := 'COMPLETE';
323 return;
324 end if;
325
326 result := '';
327 return;
328
329 exception
330 when others then
331 wf_core.context('CEBASIG', 'REJECT_SIGNATORY',
332 itemtype, itemkey, to_char(actid), funcmode);
333 raise;
334 end REJECT_SIGNATORY;
335
336 PROCEDURE selector(
337 itemtype in varchar2,
338 itemkey in varchar2,
339 actid in number,
340 command in varchar2,
341 result in out NOCOPY varchar2)
342 IS
343 l_n_org_id number;
344 l_user_id number;
345 l_resp_id number;
346 l_resp_appl_id number;
347
348 BEGIN
349 l_n_org_id := WF_ENGINE.GetItemAttrNumber(itemtype,
350 itemkey,
351 'ORG_ID');
352
353 IF (command = 'RUN') THEN
354 result := 'CEBASIG';
355 return;
356 ELSIF command = 'SET_CTX' THEN
357 l_user_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,
358 'USER_ID'));
359 l_resp_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,
360 'RESPONSIBILITY_ID'));
361 l_resp_appl_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,
362 'APPLICATION_ID'));
363 l_n_org_id := WF_ENGINE.GetItemAttrText(itemtype,
364 itemkey,
365 'ORG_ID');
366
367 fnd_global.apps_initialize(l_user_id,
368 l_resp_id,
369 l_resp_appl_id);
370
371 fnd_client_info.set_org_context(l_n_org_id);
372
373 return;
374 ELSIF (command = 'TEST_CTX') THEN
375 l_n_org_id := WF_ENGINE.GetItemAttrText(itemtype,
376 itemkey,
377 'ORG_ID');
378
379 IF (nvl(rtrim(substrb(USERENV('CLIENT_INFO'), 1, 10)),'NULL') =
380 nvl(to_char(l_n_org_id),'NULL')) THEN
381 result := 'TRUE';
382 ELSE
383 result := 'FALSE';
384 END IF;
385 END IF;
386 exception
387 when others then
388 wf_core.context('CEBASIG', 'SELECTOR',
389 itemtype, itemkey, to_char(actid), command);
390 raise;
391
392 END selector;
393
394
395 PROCEDURE insert_history_record (p_action VARCHAR2)
396 IS
397 BEGIN
398
399 UPDATE ce_ba_signatory_hist_h
400 SET current_record_flag='N'
401 WHERE signatory_id = G_signatory_id;
402
403 CE_BA_SIGNATORY_HISTORY.insert_row
404 (X_rowid => G_rowid,
405 X_Signatory_id => G_signatory_id,
406 X_Signatory_History_id => G_signatory_history_id,
407 X_Approver_person_id => G_approver_person_id,
408 X_Action => p_action,
409 X_Current_record_flag => 'Y',
410 X_Last_Updated_By => FND_GLOBAL.user_id,
411 X_Last_Update_Date => sysdate,
412 X_Last_Update_Login => FND_GLOBAL.login_id,
413 X_Created_By => FND_GLOBAL.user_id,
414 X_Creation_Date => sysdate,
415 X_Attribute_category => null,
416 X_Attribute1 => null,
417 X_Attribute2 => null,
418 X_Attribute3 => null,
419 X_Attribute4 => null,
420 X_Attribute5 => null,
421 X_Attribute6 => null,
422 X_Attribute7 => null,
423 X_Attribute8 => null,
424 X_Attribute9 => null,
425 X_Attribute10 => null,
426 X_Attribute11 => null,
427 X_Attribute12 => null,
428 X_Attribute13 => null,
429 X_Attribute14 => null,
430 X_Attribute15 => null);
431 END insert_history_record;
432
433
434
435 PROCEDURE init_all( itemtype in varchar2,
436 itemkey in varchar2,
437 actid in number,
438 command in varchar2,
439 result in out NOCOPY varchar2)
440 IS
441 l_user_id NUMBER;
442 l_resp_id NUMBER;
443 l_resp_appl_id NUMBER;
444 l_org_id NUMBER;
445 id number := itemkey;
446 BEGIN
447 IF command='RUN' THEN
448 initialize(260,id,'CEBASIG');
449 fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
450 fnd_profile.value('RESP_ID'),
451 fnd_profile.value('RESP_APPL_ID'));
452 FND_PROFILE.get('ORG_ID',l_org_id);
453 fnd_client_info.set_org_context(l_org_id);
454
455 FND_PROFILE.get('ORG_ID',l_org_id);
456 l_user_id := fnd_profile.value('USER_ID');
457 l_resp_id := fnd_profile.value('RESP_ID');
458 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
459
460 WF_ENGINE.setItemAttrText('CEBASIG',id,'ORG_ID',to_char(l_org_id));
461 WF_ENGINE.setItemAttrText('CEBASIG',id,'USER_ID',to_char(l_user_id));
462 WF_ENGINE.setItemAttrText('CEBASIG',id,'RESPONSIBILITY_ID',to_char(l_resp_id));
463 WF_ENGINE.setItemAttrText('CEBASIG',id,'APPLICATION_ID',to_char(l_resp_appl_id));
464
465 l_signatory_id := id;
466
467 -- populate ce_security_profiles_tmp table with ce_security_profiles_v
468 CEP_STANDARD.init_security;
469
470 SELECT basv.person_name, basv.single_limit_amount,
471 basv.joint_limit_amount,basv.other_limits,
472 basv.signer_group, basv.requester_id,
473 ba.bank_account_name, ba.bank_account_num,
474 ba.currency_code, bb.bank_branch_name,
475 basv.person_type, basv.person_job,
476 basv.person_location, basv.person_org_name,
477 basv.start_date, basv.end_date
478 INTO g_signatory_name, g_single_limit_amount,
479 g_joint_limit_amount, g_other_limits,
480 g_signer_group,g_requester_id,
481 g_bank_Account_name, g_bank_account_number,
482 g_currency_code, g_bank_branch_name,
483 g_person_type, g_person_job,
484 g_person_location, g_person_org_name,
485 g_start_Date, g_end_date
486 FROM ce_ba_signatories_v basv,
487 ce_bank_accts_gt_v ba, --ce_bank_accounts_v ba,
488 ce_bank_branches_v bb
489 WHERE basv.signatory_id = l_signatory_id
490 AND ba.bank_account_id = basv.bank_Account_id
491 AND bb.branch_party_id = ba.bank_branch_id;
492
493 WF_DIRECTORY.getusername('PER',g_requester_id,
494 g_name, g_display_name);
495
496 WF_ENGINE.SetItemAttrText(itemtype,
497 itemkey,
498 'REQUESTER_NAME',
499 g_name);
500
501 WF_ENGINE.SetItemAttrText(itemtype,
502 itemkey,
503 'REQUESTER_DISPLAY_NAME',
504 g_display_name);
505
506 WF_ENGINE.SetItemAttrNumber(itemtype,
507 itemkey,
508 'SIGNATORY_ID',
509 l_signatory_id);
510
511 WF_DIRECTORY.getusername('PER',l_signatory_id,
512 g_name, g_display_name);
513
514 WF_ENGINE.SetItemAttrText(itemtype,
515 itemkey,
516 'SIGNATORY_NAME',
517 g_signatory_name);
518
519 WF_ENGINE.SetItemAttrText(itemtype,
520 itemkey,
521 'SINGLE_LIMIT_AMOUNT',
522 g_single_limit_amount);
523
524 WF_ENGINE.SetItemAttrText(itemtype,
525 itemkey,
526 'JOINT_LIMIT_AMOUNT',
527 g_joint_limit_amount);
528
529 WF_ENGINE.SetItemAttrText(itemtype,
530 itemkey,
531 'BANK_ACCOUNT_NAME',
532 g_bank_Account_name);
533
534 WF_ENGINE.SetItemAttrText(itemtype,
535 itemkey,
536 'BANK_ACCOUNT_CURRENCY_CODE',
537 g_currency_code);
538
539 WF_ENGINE.SetItemAttrText(itemtype,
540 itemkey,
541 'PERSON_TYPE',
542 g_person_type);
543
544 WF_ENGINE.SetItemAttrText(itemtype,
545 itemkey,
546 'PERSON_JOB',
547 g_person_job);
548
549 WF_ENGINE.SetItemAttrText(itemtype,
550 itemkey,
551 'PERSON_LOCATION',
552 g_person_location);
553
554 WF_ENGINE.SetItemAttrText(itemtype,
555 itemkey,
556 'HR_ORGANIZATION',
557 g_person_org_name);
558
559 WF_ENGINE.SetItemAttrText(itemtype,
560 itemkey,
561 'PERSON_GROUP',
562 g_signer_group);
563
564 WF_ENGINE.SetItemAttrText(itemtype,
565 itemkey,
566 'OTHER_LIMITATIONS',
567 g_other_limits);
568
569 WF_ENGINE.SetItemAttrText(itemtype,
570 itemkey,
571 'START_DATE',
572 g_start_date);
573
574 WF_ENGINE.SetItemAttrText(itemtype,
575 itemkey,
576 'END_DATE',
577 g_end_date);
578
579 WF_ENGINE.SetItemAttrText(itemtype,
580 itemkey,
581 'BANK_ACCOUNT_NUMBER',
582 g_bank_account_number);
583
584 WF_ENGINE.SetItemAttrText(itemtype,
585 itemkey,
586 'BANK_BRANCH_NAME',
587 g_bank_branch_name);
588
589
590 result:='COMPLETE';
591 RETURN;
592 ELSE
593 return;
594 END IF;
595 EXCEPTION
596 when others then
597 wf_core.context('CEBASIG', 'INIT_ALL',
598 itemtype, itemkey, to_char(actid), command);
599 raise;
600
601 END init_all;
602
603
604 /* This procedure can be used to start the wf process from pl/sql */
605 PROCEDURE startit(id number)
606 IS
607 l_user_id NUMBER;
608 l_resp_id NUMBER;
609 l_resp_appl_id NUMBER;
610 l_org_id NUMBER;
611 itemtype VARCHAR2(100);
612 itemkey NUMBER;
613 BEGIN
614
615 initialize(260,id,'CEBASIG');
616 WF_ENGINE.CREATEPROCESS('CEBASIG',
617 id,
618 'CEBASIG');
619
620 fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
621 fnd_profile.value('RESP_ID'),
622 fnd_profile.value('RESP_APPL_ID'));
623 FND_PROFILE.get('ORG_ID',l_org_id);
624 fnd_client_info.set_org_context(l_org_id);
625
626 FND_PROFILE.get('ORG_ID',l_org_id);
627 l_user_id := fnd_profile.value('USER_ID');
628 l_resp_id := fnd_profile.value('RESP_ID');
629 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
630
631 WF_ENGINE.setItemAttrText('CEBASIG',id,'ORG_ID',to_char(l_org_id));
632 WF_ENGINE.setItemAttrText('CEBASIG',id,'USER_ID',to_char(l_user_id));
633 WF_ENGINE.setItemAttrText('CEBASIG',id,'RESPONSIBILITY_ID',to_char(l_resp_id));
634 WF_ENGINE.setItemAttrText('CEBASIG',id,'APPLICATION_ID',to_char(l_resp_appl_id));
635
636 WF_ENGINE.STARTPROCESS('CEBASIG',
637 id);
638 COMMIT;
639 END startit;
640
641 end CE_BASIG_WF;