[Home] [Help]
PACKAGE BODY: APPS.GL_WF_JE_APPROVAL_PKG
Source
1 PACKAGE BODY GL_WF_JE_APPROVAL_PKG AS
2 /* $Header: glwfjeab.pls 120.13.12010000.3 2008/12/01 11:48:12 bsrikant ship $ */
3
4 -- +--------------------+
5 -- | PRIVATE PROCEDURES |
6 -- +--------------------+
7
8 -- ****************************************************************************
9 -- Private procedure: Display diagnostic message
10 -- ****************************************************************************
11 PROCEDURE diagn_msg (message_string IN VARCHAR2) IS
12 BEGIN
13 IF diagn_msg_flag THEN
14 -- dbms_output.put_line (message_string);
15 NULL;
16 END IF;
17 EXCEPTION
18 WHEN OTHERS THEN
19 NULL;
20 END diagn_msg;
21
22
23 -- ****************************************************************************
24 -- Function: check_authorization_limit
25 -- Purpose: checks if the employee has enough authorization limits to approve
26 -- the entire batch.
27 -- ****************************************************************************
28
29 FUNCTION check_authorization_limit(p_employee_id NUMBER,
30 p_batch_id NUMBER) RETURN BOOLEAN IS
31 CURSOR get_ledgers IS
32 SELECT led.ledger_id,
33 max(abs(nvl(jel.accounted_dr, 0) -
34 nvl(jel.accounted_cr, 0))) LARGEST_NET_AMOUNT
35 FROM GL_JE_HEADERS jeh, GL_JE_LINES jel, GL_LEDGERS led
36 WHERE jeh.je_batch_id = p_batch_id
37 AND jeh.currency_code <> 'STAT'
38 AND jel.je_header_id = jeh.je_header_id
39 AND led.ledger_id = jeh.ledger_id
40 AND led.enable_je_approval_flag = 'Y'
41 GROUP BY led.ledger_id;
42
43 l_limit NUMBER;
44 BEGIN
45 FOR ledger_rec IN get_ledgers LOOP
46 SELECT nvl(min(authorization_limit), -1)
47 INTO l_limit
48 FROM GL_AUTHORIZATION_LIMITS
49 WHERE employee_id = p_employee_id
50 AND ledger_id = ledger_rec.ledger_id;
51
52 IF (l_limit < ledger_rec.largest_net_amount) THEN
53 RETURN FALSE;
54 END IF;
55 END LOOP;
56
57 RETURN TRUE;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG',
62 'check_authorization_limit');
63 raise;
64 END check_authorization_limit;
65
66
67 -- ****************************************************************************
68 -- Procedure: getmanager
69 -- Purpose: get the manager id of an employee.
70 -- ****************************************************************************
71
72 PROCEDURE getmanager(employee_id IN NUMBER,
73 manager_id OUT NOCOPY NUMBER) IS
74 BEGIN
75 diagn_msg('getmanager: employee_id = ' || to_char(employee_id));
76
77 -- 4880614: Instead of using GL_HR_EMPLOYEES_CURRENT_V, use HR base tables
78 -- to skip HR security in the workflow.
79 SELECT A.supervisor_id
80 INTO manager_id
81 FROM PER_ALL_PEOPLE_F P, PER_ALL_ASSIGNMENTS_F A
82 WHERE P.business_group_id + 0 = A.business_group_id
83 AND P.employee_number IS NOT NULL
84 AND TRUNC(sysdate) BETWEEN P.effective_start_date AND P.effective_end_date
85 AND A.primary_flag = 'Y'
86 AND A.assignment_type = 'E'
87 AND A.person_id = P.person_id
88 AND TRUNC(sysdate) BETWEEN A.effective_start_date AND A.effective_end_date
89 AND P.person_id = employee_id;
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 manager_id := NULL;
94 WHEN OTHERS THEN
95 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'getmanager');
96 raise;
97 END getmanager;
98
99
100 -- ****************************************************************************
101 -- Procedure: setpersonas
102 -- Purpose: set the given (manager) id as the MANAGER or the APPROVER.
103 -- ****************************************************************************
104
105 PROCEDURE setpersonas(manager_id IN NUMBER,
106 item_type IN VARCHAR2,
107 item_key IN VARCHAR2,
108 manager_target IN VARCHAR2) IS
109 l_manager_name VARCHAR2(240);
110 l_manager_display_name VARCHAR2(240);
111 BEGIN
112 diagn_msg('Executing the setpersonas activity..');
113
114 WF_DIRECTORY.GetUserName('PER',
115 manager_id,
116 l_manager_name,
117 l_manager_display_name);
118
119 diagn_msg('setpersonas: manager_name = ' || l_manager_name);
120 diagn_msg('setpersonas: manager_display_name = ' || l_manager_display_name);
121
122 IF (manager_target = 'MANAGER') THEN
123 WF_ENGINE.SetItemAttrText(item_type,
124 item_key,
125 'MANAGER_ID',
126 manager_id);
127
128 WF_ENGINE.SetItemAttrText(item_type,
129 item_key,
130 'MANAGER_NAME',
131 l_manager_name);
132
133 WF_ENGINE.SetItemAttrText(item_type,
134 item_key,
135 'MANAGER_DISPLAY_NAME',
136 l_manager_display_name);
137
138 ELSE
139 WF_ENGINE.SetItemAttrText(item_type,
140 item_key,
141 'APPROVER_ID',
142 manager_id);
143
144 WF_ENGINE.SetItemAttrText(item_type,
145 item_key,
146 'APPROVER_NAME',
147 l_manager_name);
148
149 WF_ENGINE.SetItemAttrText(item_type,
150 item_key,
151 'APPROVER_DISPLAY_NAME',
152 l_manager_display_name);
153 END IF;
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'setpersonas',
158 item_type, item_key);
159 raise;
160 END setpersonas;
161
162
163 -- ****************************************************************************
164 -- Procedure: getfinalapprover
165 -- Purpose: find the first approver who can approve the entire batch.
166 -- ****************************************************************************
167
168 PROCEDURE getfinalapprover(p_employee_id IN NUMBER,
169 p_batch_id IN NUMBER,
170 p_final_approver_id OUT NOCOPY NUMBER) IS
171 l_approver_id NUMBER := p_employee_id;
172 l_temp_employee_id NUMBER;
173 BEGIN
174 LOOP
175 -- bug 2708423: can't pass the same variable as IN and OUT parameter
176 l_temp_employee_id := l_approver_id;
177 GetManager(l_temp_employee_id,
178 l_approver_id);
179
180 IF (l_approver_id IS NULL) THEN
181 p_final_approver_id := NULL;
182 return;
183 END IF;
184
185 IF (check_authorization_limit(l_approver_id, p_batch_id)) THEN
186 p_final_approver_id := l_approver_id;
187 return;
188 END IF;
189
190 END LOOP;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'getfinalapprover');
195 raise;
196 END getfinalapprover;
197
198
199 -- ****************************************************************************
200 -- Procedure: getapprover
201 -- Purpose: get the next approver for a given employee id based upon the
202 -- find_approver_method.
203 -- ****************************************************************************
204
205 PROCEDURE getapprover(employee_id IN NUMBER,
206 item_type IN VARCHAR2,
207 item_key IN VARCHAR2,
208 curr_approver_id IN NUMBER,
209 find_approver_method IN VARCHAR2,
210 next_approver_id IN OUT NOCOPY NUMBER) IS
211 l_error_message VARCHAR2(2000);
212 l_batch_id NUMBER;
213 BEGIN
214 -- Get batch id
215 l_batch_id := wf_engine.GetItemAttrNumber(
216 itemtype => item_type,
217 itemkey => item_key,
218 aname => 'BATCH_ID');
219
220 IF (find_approver_method = 'CHAIN') THEN
221
222 IF (next_approver_id IS NULL) THEN
223
224 diagn_msg('Getapprover: Calling getmanager with method equal CHAIN');
225
226 getmanager(curr_approver_id, next_approver_id);
227
228 END IF;
229
230 ELSIF (find_approver_method = 'DIRECT') THEN
231
232 diagn_msg('Getapprover: Calling getfinalapprover with method equal DIRECT');
233
234 getfinalapprover(employee_id,
235 l_batch_id,
236 next_approver_id);
237
238 ELSIF (find_approver_method = 'ONE_STOP_DIRECT') THEN
239
240 IF (next_approver_id IS NULL) THEN
241
242 diagn_msg('Getapprover: Calling getfinalapprover with method equal ONE_STOP_DIRECT');
243
244 getfinalapprover(curr_approver_id,
245 l_batch_id,
246 next_approver_id);
247 END IF;
248
249 ELSE
250 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_APPROVER_METHOD');
251 l_error_message := FND_MESSAGE.Get;
252
253 wf_engine.SetItemAttrText(item_type,
254 item_key,
255 'ERROR_MESSAGE',
256 l_error_message);
257 END IF;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'getapprover',
262 item_type, item_key);
263 raise;
264 END getapprover;
265
266
267 -- +-------------------+
268 -- | PUBLIC PROCEDURES |
269 -- +-------------------+
270
271 -- ****************************************************************************
272 -- start_approval_workflow
273 -- ****************************************************************************
274
275 PROCEDURE start_approval_workflow (p_je_batch_id IN NUMBER,
276 p_preparer_fnd_user_id IN NUMBER,
277 p_preparer_resp_id IN NUMBER,
278 p_je_batch_name IN VARCHAR2) IS
279 l_itemtype VARCHAR2(10) := 'GLBATCH';
280 l_itemkey VARCHAR2(40);
281 l_fnd_user_name VARCHAR2(100);
282 l_preparer_id NUMBER;
283 l_monitor_url VARCHAR2(500);
284 l_approval_run_id NUMBER;
285 l_business_group_id NUMBER;
286 l_reassign_role VARCHAR2(320);
287 BEGIN
288 diagn_msg('Executing Start_Approval_Workflow for JE batch '||
289 to_char(p_je_batch_id));
290
291 -- Update the approval status of the batch to 'I'
292 UPDATE gl_je_batches
293 SET approval_status_code = 'I'
294 WHERE je_batch_id = p_je_batch_id;
295
296 -- Get Approval run id
297 SELECT GL_JE_APPROVAL_S.nextval
298 INTO l_approval_run_id
299 FROM DUAL;
300
301 -- Get AOL user name and person id
302 SELECT user_name, nvl(employee_id, -1)
303 INTO l_fnd_user_name, l_preparer_id
304 FROM fnd_user
305 WHERE user_id = p_preparer_fnd_user_id;
306
307 -- generate the item key
308 l_itemkey := to_char(p_je_batch_id) || '*' || to_char(l_approval_run_id);
309
310 diagn_msg('Generated Item Key = ' || l_itemkey);
311
312 -- Kick Off workflow process
313 wf_engine.CreateProcess( itemtype => l_itemtype,
314 itemkey => l_itemkey,
315 process => 'GL_JE_APPROVAL_PROCESS' );
316 diagn_msg('Process for GL_JE_APPROVAL_PROCESS created');
317
318 -- Set item user key
319 wf_engine.SetItemUserKey( itemtype => l_itemtype,
320 itemkey => l_itemkey,
321 userkey => p_je_batch_name );
322
323 -- Set the process owner
324 wf_engine.SetItemOwner( itemtype => l_itemtype,
325 itemkey => l_itemkey,
326 owner => l_fnd_user_name );
327
328 -- Set the fnd user name
329 wf_engine.SetItemAttrText( itemtype => l_itemtype,
330 itemkey => l_itemkey,
331 aname => 'FND_USER_NAME',
332 avalue => l_fnd_user_name );
333
334 -- Set batch id (JE batch ID)
335 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
336 itemkey => l_itemkey,
337 aname => 'BATCH_ID',
338 avalue => p_je_batch_id );
339 diagn_msg('Attribute JE_BATCH_ID set to ' || to_char(p_je_batch_id));
340
341 -- Set the unique item key
342 wf_engine.SetItemAttrText( itemtype => l_itemtype,
343 itemkey => l_itemkey,
344 aname => 'UNIQUE_ITEMKEY',
345 avalue => l_itemkey );
346 diagn_msg('Set the unique item key: '|| l_itemkey);
347
348 -- Set PersonID attribute (HR personID from PER_PERSONS_F)
349 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
350 itemkey => l_itemkey,
351 aname => 'PREPARER_ID',
352 avalue => l_preparer_id );
353 diagn_msg('Attribute PREPARER_ID set to ' || l_preparer_id);
354
355 -- Set UserID attribute (AOL userID from FND_USER table).
356 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
357 itemkey => l_itemkey,
358 aname => 'PREPARER_FND_ID',
359 avalue => p_preparer_fnd_user_id );
360
361 -- Set ResponsibilityID attribute
362 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
363 itemkey => l_itemkey,
364 aname => 'PREPARER_RESP_ID',
365 avalue => p_preparer_resp_id );
366 diagn_msg('Attribute PREPARER_RESP_ID set');
367
368 -- Get the monitor URL
369 l_monitor_url := wf_monitor.GetUrl(wf_core.translate('WF_WEB_AGENT'),
370 l_itemtype, l_itemkey, 'NO');
371 wf_engine.SetItemAttrText( itemtype => l_itemtype,
372 itemkey => l_itemkey,
373 aname => 'MONITOR_URL',
374 avalue => l_monitor_url );
375 diagn_msg('Attribute Monitor_URL set to ' || l_monitor_url);
376
377 -- Set Reassignment Role
378 SELECT min(business_group_id)
379 INTO l_business_group_id
380 FROM per_all_people_f
381 WHERE person_id = l_preparer_id
382 AND trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;
383
384 -- Take the Max to get the business group specific role if exists
385 SELECT max(name)
386 INTO l_reassign_role
387 FROM WF_LOCAL_ROLES
388 WHERE name IN ('GL_JE_APPROVAL_REASSIGN_ROLE' || ' ' || l_business_group_id,
389 'GL_JE_APPROVAL_REASSIGN_ROLE')
390 AND nvl(expiration_date, sysdate + 1) > sysdate;
391
392 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
393 itemkey => l_itemkey,
394 aname => 'REASSIGNMENT_ROLE',
395 avalue => l_reassign_role );
396
397 -- Finally, start the process
398 wf_engine.StartProcess( itemtype => l_itemtype,
399 itemkey => l_itemkey );
400
401 diagn_msg('Process GL_JE_APPROVAL_PROCESS started');
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'start_approval_workflow',
406 l_itemtype, l_itemkey);
407 raise;
408
409 END start_approval_workflow;
410
411
412 -- ****************************************************************************
413 -- is_employee_set
414 -- ****************************************************************************
415
416 PROCEDURE is_employee_set (itemtype IN VARCHAR2,
417 itemkey IN VARCHAR2,
418 actid IN NUMBER,
419 funcmode IN VARCHAR2,
420 result OUT NOCOPY VARCHAR2) IS
421 l_preparer_id NUMBER;
422 l_preparer_name VARCHAR2(240);
423 l_preparer_display_name VARCHAR2(240);
424 BEGIN
425
426 IF ( funcmode = 'RUN' ) THEN
427 -- Get PersonID attribute (already set when process started)
428 l_preparer_id := wf_engine.GetItemAttrNumber(
429 itemtype => itemtype,
430 itemkey => itemkey,
431 aname => 'PREPARER_ID' );
432
433 IF (l_preparer_id = -1 OR l_preparer_id IS NULL) THEN
434 result := 'COMPLETE:N';
435 return;
436 ELSE
437 -- Retrieve preparer's User name (Login name for Apps) and displayed name
438 wf_directory.GetUserName( p_orig_system => 'PER',
439 p_orig_system_id => l_preparer_id,
440 p_name => l_preparer_name,
441 p_display_name => l_preparer_display_name );
442 diagn_msg('Retrieved user name: '|| l_preparer_name);
443
444 -- Copy username to Workfow
445 wf_engine.SetItemAttrText( itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'PREPARER_NAME',
448 avalue => l_preparer_name );
449 diagn_msg('Attribute PREPARER_NAME set to' || l_preparer_name);
450
451 -- Copy displayed username to Workfow
452 wf_engine.SetItemAttrText( itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'PREPARER_DISPLAY_NAME',
455 avalue => l_preparer_display_name );
456 diagn_msg('Attribute PREPARER_DISPLAY_NAME set to '||l_preparer_display_name);
457 result := 'COMPLETE:Y';
458 return;
459 END IF;
460
461 ELSIF ( funcmode = 'CANCEL' ) THEN
462 null;
463 END IF;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'is_employee_set',
468 itemtype, itemkey, to_char(actid), funcmode);
469 raise;
470
471 END is_employee_set;
472
473
474 -- ****************************************************************************
475 -- get_sob_attributes
476 -- ****************************************************************************
477
478 PROCEDURE get_sob_attributes (itemtype IN VARCHAR2,
479 itemkey IN VARCHAR2,
480 actid IN NUMBER,
481 funcmode IN VARCHAR2,
482 result OUT NOCOPY VARCHAR2) IS
483 l_je_batch_id NUMBER;
484 l_current_ledger_id NUMBER;
485 l_ledger_id NUMBER;
486 l_func_currency VARCHAR2(15);
487 l_average_balances_flag VARCHAR2(1);
488 l_cons_sob_flag VARCHAR2(1);
489 l_suspense_flag VARCHAR2(1);
490 l_period_set_name VARCHAR2(15);
491 l_budgetary_control_flag VARCHAR2(1);
492 l_enable_automatic_tax_flag VARCHAR2(1);
493 l_latest_encumbrance_year VARCHAR2(15);
494 recinfo gl_ledgers%ROWTYPE;
495
496 l_ledger_approval_amount NUMBER;
497
498 CURSOR batch_ledgers (v_batch_id NUMBER, v_curr_ledger_id NUMBER) IS
499 SELECT led.ledger_id
500 FROM GL_LEDGERS led
501 WHERE led.ledger_id IN (SELECT jeh.ledger_id
502 FROM GL_JE_HEADERS jeh
503 WHERE jeh.je_batch_id = v_batch_id)
504 AND led.ledger_id > v_curr_ledger_id
505 ORDER BY led.ledger_id;
506
507 BEGIN
508
509 IF ( funcmode = 'RUN' ) THEN
510 -- Get JE batch ID
511 l_je_batch_id := wf_engine.GetItemAttrNumber(
512 itemtype => itemtype,
513 itemkey => itemkey,
514 aname => 'BATCH_ID');
515
516 -- Get current ledger ID
517 l_current_ledger_id := wf_engine.GetItemAttrNumber(
518 itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'SET_OF_BOOKS_ID');
521
522 OPEN batch_ledgers(l_je_batch_id, l_current_ledger_id);
523 FETCH batch_ledgers INTO l_ledger_id;
524 IF (batch_ledgers%NOTFOUND) THEN
525 -- no more ledgers: reset Set of Books (Ledger) Attribute to -1
526 CLOSE batch_ledgers;
527 wf_engine.SetItemAttrText(itemtype => itemtype,
528 itemkey => itemkey,
529 aname => 'SET_OF_BOOKS_ID',
530 avalue => -1);
531 result := 'COMPLETE:FAIL';
532 return;
533 END IF;
534 CLOSE batch_ledgers;
535
536 -- Retrieve set of books attributes
537 recinfo.ledger_id := l_ledger_id;
538 GL_LEDGERS_PKG.select_row(recinfo);
539
540 l_func_currency := recinfo.currency_code;
541 l_average_balances_flag := recinfo.enable_average_balances_flag;
542 l_cons_sob_flag := recinfo.consolidation_ledger_flag;
543 l_suspense_flag := recinfo.suspense_allowed_flag;
544 l_period_set_name := recinfo.period_set_name;
545 l_budgetary_control_flag := recinfo.enable_budgetary_control_flag;
546 l_enable_automatic_tax_flag := recinfo.enable_automatic_tax_flag;
547 l_latest_encumbrance_year := recinfo.latest_encumbrance_year;
548
549 diagn_msg('Ledger Attributes retrieved from db');
550
551 -- Set the corresponding attributes in workflow
552 wf_engine.SetItemAttrText ( itemtype => itemtype,
553 itemkey => itemkey,
554 aname => 'FUNC_CURRENCY',
555 avalue => l_func_currency );
556 diagn_msg('Get_SOB_Attributes: Func currency = '||l_func_currency);
557
558 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
559 itemkey => itemkey,
560 aname => 'SET_OF_BOOKS_ID',
561 avalue => l_ledger_id );
562 diagn_msg('Get_SOB_Attributes: Ledger id = ' ||to_char(l_ledger_id));
563
564 wf_engine.SetItemAttrText ( itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'SUSPENSE_FLAG',
567 avalue => l_suspense_flag );
568 wf_engine.SetItemAttrText ( itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'AVERAGE_BALANCES_FLAG',
571 avalue => l_average_balances_flag );
572 wf_engine.SetItemAttrText ( itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'CONS_SOB_FLAG',
575 avalue => l_cons_sob_flag );
576 wf_engine.SetItemAttrText ( itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'BUDGETARY_CONTROL_FLAG',
579 avalue => l_budgetary_control_flag );
580 diagn_msg('Get_SOB_Attributes: budgetary control flag = '||l_budgetary_control_flag);
581
582 wf_engine.SetItemAttrText ( itemtype => itemtype,
583 itemkey => itemkey,
584 aname => 'AUTOMATIC_TAX_FLAG',
585 avalue => l_enable_automatic_tax_flag );
586
587 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
588 itemkey => itemkey,
589 aname => 'LATEST_ENCUMBRANCE_YEAR',
590 avalue => l_latest_encumbrance_year );
591
592 SELECT max(abs(nvl(jel.accounted_dr, 0) - nvl(jel.accounted_cr, 0)))
593 INTO l_ledger_approval_amount
594 FROM GL_JE_HEADERS jeh, GL_JE_LINES jel
595 WHERE jeh.je_batch_id = l_je_batch_id
596 AND jeh.ledger_id = l_ledger_id
597 AND jeh.currency_code <> 'STAT'
598 AND jel.je_header_id = jeh.je_header_id;
599
600 -- Copy the corresponding item attribute in workflow
601 wf_engine.SetItemAttrNumber( itemtype => itemtype,
602 itemkey => itemkey,
603 aname => 'BATCH_TOTAL',
604 avalue => l_ledger_approval_amount );
605
606 result := 'COMPLETE:SUCCESS';
607
608 ELSIF ( funcmode = 'CANCEL' ) THEN
609 null;
610 END IF;
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'get_sob_attributes',
615 itemtype, itemkey, to_char(actid), funcmode);
616 raise;
617 END get_sob_attributes;
618
619
620 -- ****************************************************************************
621 -- get_jeb_attributes
622 -- ****************************************************************************
623
624 PROCEDURE get_jeb_attributes(itemtype IN VARCHAR2,
625 itemkey IN VARCHAR2,
626 actid IN NUMBER,
627 funcmode IN VARCHAR2,
628 result OUT NOCOPY VARCHAR2) IS
629 l_je_batch_id NUMBER;
630 l_je_batch_name VARCHAR2(100);
631 l_balance_type VARCHAR2(1);
632 l_budgetary_status VARCHAR2(1);
633 l_period_name VARCHAR2(15);
634 l_control_total NUMBER;
635 l_running_total_dr NUMBER;
636 l_running_total_cr NUMBER;
637 l_enter_journals VARCHAR2(500);
638 BEGIN
639
640 IF ( funcmode = 'RUN' ) THEN
641
642 -- Get JE batch ID (primary key)
643 l_je_batch_id := wf_engine.GetItemAttrNumber(
644 itemtype => itemtype,
645 itemkey => itemkey,
646 aname => 'BATCH_ID');
647
648 diagn_msg('Executing Get_JEB_Attributes for JE batch '||to_char(l_je_batch_id));
649
650 -- Get other batch info
651 SELECT
652 name,
653 actual_flag,
654 default_period_name,
655 control_total,
656 running_total_dr,
657 running_total_cr,
658 budgetary_control_status
659 INTO l_je_batch_name,
660 l_balance_type,
661 l_period_name,
662 l_control_total,
663 l_running_total_dr,
664 l_running_total_cr,
665 l_budgetary_status
666 FROM GL_JE_BATCHES
667 WHERE je_batch_id = l_je_batch_id;
668
669 diagn_msg('JEB Attributes retrieved from db');
670
671 -- Copy JE batch name to corresponding item attribute in workflow
672 wf_engine.SetItemAttrText ( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'BATCH_NAME',
675 avalue => l_je_batch_name );
676 diagn_msg('get_jeb_attributes JEB name = ' || l_je_batch_name);
677
678 -- Copy JE batch period name to corresponding item attribute in workflow
679 wf_engine.SetItemAttrText ( itemtype => itemtype,
680 itemkey => itemkey,
681 aname => 'PERIOD_NAME',
682 avalue => l_period_name );
683 diagn_msg('get_jeb_attributes JEB period name = ' ||l_period_name );
684
685 -- Copy control total of the batch in workflow
686 wf_engine.SetItemAttrText ( itemtype => itemtype,
687 itemkey => itemkey,
688 aname => 'CONTROL_TOTAL',
689 avalue => l_control_total );
690
691 -- Copy running totals of the batch in workflow
692 wf_engine.SetItemAttrText ( itemtype => itemtype,
693 itemkey => itemkey,
694 aname => 'RUNNING_TOTAL_DR',
695 avalue => l_running_total_dr );
696 wf_engine.SetItemAttrText ( itemtype => itemtype,
697 itemkey => itemkey,
698 aname => 'RUNNING_TOTAL_CR',
699 avalue => l_running_total_cr );
700
701 -- Copy budgetary control status item attribute in workflow
702 wf_engine.SetItemAttrText ( itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'BUDGETARY_CONTROL_STATUS',
705 avalue => l_budgetary_status );
706 diagn_msg('get_jeb_attributes budgetary control status = ' || l_budgetary_status);
707
708 -- Copy actual flag to corresponding item attribute in workflow
709 wf_engine.SetItemAttrText ( itemtype => itemtype,
710 itemkey => itemkey,
711 aname => 'BALANCE_TYPE',
712 avalue => l_balance_type );
713 diagn_msg('get_jeb_attributes: Balance Type = ' || l_balance_type);
714
715 -- Based on the balance type, populate the enter_journals variable.
716 IF (l_balance_type = 'E') THEN
717 l_enter_journals := 'GLXJEENT_E:autoquery_level=BATCH ' ||
718 'autoquery_coordination=INITIAL ' ||
719 'autoquery_criteria=' || to_char(l_je_batch_id);
720 ELSE
721 l_enter_journals := 'GLXJEENT_A:autoquery_level=BATCH ' ||
722 'autoquery_coordination=INITIAL ' ||
723 'autoquery_criteria=' || to_char(l_je_batch_id);
724 END IF;
725
726 wf_engine.SetItemAttrText ( itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'ENTER_JOURNALS_FORM',
729 avalue => l_enter_journals );
730
731 diagn_msg('JEB Attributes stored in WF tables');
732
733 ELSIF ( funcmode = 'CANCEL' ) THEN
734 null;
735 END IF;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'get_jeb_attributes',
740 itemtype, itemkey, to_char(actid), funcmode);
741 raise;
742
743 END get_jeb_attributes;
744
745
746 -- ****************************************************************************
747 -- is_je_valid
748 -- ****************************************************************************
749
750 PROCEDURE is_je_valid(itemtype IN VARCHAR2,
751 itemkey IN VARCHAR2,
752 actid IN NUMBER,
753 funcmode IN VARCHAR2,
754 result OUT NOCOPY VARCHAR2) IS
755 l_je_batch_id NUMBER;
756 l_untaxed_cursor VARCHAR2(20);
757 l_balance_type VARCHAR2(1);
758 l_budgetary_control_flag VARCHAR2(1);
759 l_budgetary_status VARCHAR2(1);
760 l_control_total NUMBER;
761 l_running_total_dr NUMBER;
762 l_running_total_cr NUMBER;
763 l_invalid_error VARCHAR2(2000);
764
765 CURSOR check_untaxed IS
766 SELECT 'untaxed journals'
767 FROM DUAL
768 WHERE EXISTS
769 (SELECT 'UNTAXED'
770 FROM GL_JE_HEADERS JEH, GL_LEDGERS LED
771 WHERE JEH.je_batch_id = l_je_batch_id
772 AND JEH.tax_status_code = 'R'
773 AND JEH.currency_code <> 'STAT'
774 AND JEH.je_source = 'Manual'
775 AND LED.ledger_id = JEH.ledger_id
776 AND enable_automatic_tax_flag = 'Y');
777 BEGIN
778
779 IF ( funcmode = 'RUN' ) THEN
780
781 -- Get the batch id
782 l_je_batch_id := wf_engine.GetItemAttrNumber(
783 itemtype => itemtype,
784 itemkey => itemkey,
785 aname => 'BATCH_ID');
786 diagn_msg('Is JE Valid: Batch Id = ' || to_char(l_je_batch_id));
787
788 -- Get the control total and running totals for the batch.
789 l_control_total := wf_engine.GetItemAttrNumber(
790 itemtype => itemtype,
791 itemkey => itemkey,
792 aname => 'CONTROL_TOTAL');
793 diagn_msg('Is JE Valid: Control Total = ' ||to_char(l_control_total));
794 l_running_total_dr := wf_engine.GetItemAttrNumber(
795 itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'RUNNING_TOTAL_DR');
798 diagn_msg('Is JE Valid: Running Total Debit = ' ||to_char(l_running_total_dr));
799 l_running_total_cr := wf_engine.GetItemAttrNumber(
800 itemtype => itemtype,
801 itemkey => itemkey,
802 aname => 'RUNNING_TOTAL_CR');
803 diagn_msg('Is JE Valid: Running Total Credit = ' ||to_char(l_running_total_cr));
804
805 -- Get the actual flag
806 l_balance_type := wf_engine.GetItemAttrText(
807 itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'BALANCE_TYPE');
810 diagn_msg('Is JE Valid: Balance Type = ' ||l_balance_type);
811
812 -- Get the budgetary control status
813 l_budgetary_status := wf_engine.GetItemAttrText(
814 itemtype => itemtype,
815 itemkey => itemkey,
816 aname => 'BUDGETARY_CONTROL_STATUS');
817 diagn_msg('Is JE Valid: Budgetary Control Status = ' ||l_budgetary_status);
818
819 -- Check whether the batch contains untaxed journals.
820 IF (l_balance_type = 'A') THEN
821 OPEN check_untaxed;
822 FETCH check_untaxed INTO l_untaxed_cursor;
823
824 IF check_untaxed%FOUND THEN
825 CLOSE check_untaxed;
826 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_UNTAXED');
827 l_invalid_error := FND_MESSAGE.Get;
828 wf_engine.SetItemAttrText( itemtype,
829 itemkey,
830 'INVALID_JE_ERROR',
831 l_invalid_error );
832 result := 'COMPLETE:N';
833 return;
834 END IF;
835 CLOSE check_untaxed;
836 END IF;
837
838 -- Check if budgetary control is on but funds have not been reserved.
839 SELECT nvl(max(led.enable_budgetary_control_flag), 'N')
840 INTO l_budgetary_control_flag
841 FROM GL_JE_HEADERS jeh, GL_LEDGERS led
842 WHERE jeh.je_batch_id = l_je_batch_id
843 AND led.ledger_id = jeh.ledger_id
844 AND led.enable_budgetary_control_flag = 'Y';
845
846 IF (l_budgetary_control_flag = 'Y' AND l_budgetary_status <> 'P') THEN
847 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_RESERVE_FUNDS');
848 l_invalid_error := FND_MESSAGE.Get;
849 wf_engine.SetItemAttrText( itemtype,
850 itemkey,
851 'INVALID_JE_ERROR',
852 l_invalid_error );
853 result := 'COMPLETE:N';
854 return;
855 END IF;
856
857 -- Make sure the control total matches the
858 -- running totals
859 IF ( l_control_total IS NULL
860 OR ( l_balance_type IN ('A', 'E')
861 AND l_running_total_dr = l_control_total)
862 OR ( l_balance_type = 'B'
863 AND greatest(l_running_total_cr, l_running_total_dr)
864 = l_control_total)
865 ) THEN
866 null;
867 ELSE
868 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_CONTROL_TOTAL');
869 l_invalid_error := FND_MESSAGE.Get;
870 wf_engine.SetItemAttrText( itemtype,
871 itemkey,
872 'INVALID_JE_ERROR',
873 l_invalid_error);
874 result := 'COMPLETE:N';
875 return;
876 END IF;
877
878 -- If the batch passes all the above checks, then its valid.
879 result := 'COMPLETE:Y';
880
881 ELSIF (funcmode = 'CANCEL') THEN
882 NULL;
883 END IF;
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'is_je_valid',
888 itemtype, itemkey, to_char(actid), funcmode);
889 raise;
890 END is_je_valid;
891
892
893 -- ****************************************************************************
894 -- set_je_invalid
895 -- ****************************************************************************
896
897 PROCEDURE set_je_invalid(itemtype IN VARCHAR2,
898 itemkey IN VARCHAR2,
899 actid IN NUMBER,
900 funcmode IN VARCHAR2,
901 result OUT NOCOPY VARCHAR2) IS
902 l_je_batch_id NUMBER;
903 BEGIN
904 IF ( funcmode = 'RUN' ) THEN
905 -- Get JE batch ID
906 l_je_batch_id := wf_engine.GetItemAttrNumber(
907 itemtype => itemtype,
908 itemkey => itemkey,
909 aname => 'BATCH_ID');
910
911 UPDATE GL_JE_BATCHES
912 SET approval_status_code = 'V'
913 WHERE je_batch_id = l_je_batch_id;
914
915 ELSIF ( funcmode = 'CANCEL' ) THEN
916 null;
917 END IF;
918
919 EXCEPTION
920 WHEN OTHERS THEN
921 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_je_invalid',
922 itemtype, itemkey, to_char(actid), funcmode);
923 raise;
924 END set_je_invalid;
925
926
927 -- ****************************************************************************
928 -- does_je_need_approval
929 -- ****************************************************************************
930
931 PROCEDURE does_je_need_approval(itemtype IN VARCHAR2,
932 itemkey IN VARCHAR2,
933 actid IN NUMBER,
934 funcmode IN VARCHAR2,
935 result OUT NOCOPY VARCHAR2) IS
936 l_je_batch_id NUMBER;
937 l_non_stat_cursor VARCHAR2(40);
938
939 CURSOR non_stat IS
940 SELECT 'non stat journal exists'
941 FROM DUAL
942 WHERE EXISTS
943 (SELECT 'X'
944 FROM GL_JE_HEADERS
945 WHERE je_batch_id = l_je_batch_id
946 AND currency_code <> 'STAT');
947 BEGIN
948 IF ( funcmode = 'RUN' ) THEN
949 -- Get JE batch ID (primary key)
950 l_je_batch_id := wf_engine.GetItemAttrNumber(
951 itemtype => itemtype,
952 itemkey => itemkey,
953 aname => 'BATCH_ID');
954
955 -- Check whether STAT journal exists
956 OPEN non_stat;
957 FETCH non_stat INTO l_non_stat_cursor;
958
959 IF non_stat%FOUND THEN
960 CLOSE non_stat;
961 result := 'COMPLETE:Y';
962 ELSE
963 CLOSE non_stat;
964 result := 'COMPLETE:N';
965 END IF;
966
967 ELSIF ( funcmode = 'CANCEL' ) THEN
968 NULL;
969 END IF;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'does_je_need_approval',
974 itemtype, itemkey, to_char(actid), funcmode);
975 raise;
976 END does_je_need_approval;
977
978
979 -- ****************************************************************************
980 -- set_approval_not_required
981 -- ****************************************************************************
982
983 PROCEDURE set_approval_not_required(itemtype IN VARCHAR2,
984 itemkey IN VARCHAR2,
985 actid IN NUMBER,
986 funcmode IN VARCHAR2,
987 result OUT NOCOPY VARCHAR2) IS
988 l_je_batch_id NUMBER;
989 BEGIN
990 IF ( funcmode = 'RUN' ) THEN
991 -- Get JE batch ID
992 l_je_batch_id := wf_engine.GetItemAttrNumber(
993 itemtype => itemtype,
994 itemkey => itemkey,
995 aname => 'BATCH_ID');
996
997 UPDATE GL_JE_BATCHES
998 SET approval_status_code = 'A'
999 WHERE je_batch_id = l_je_batch_id;
1000
1001 ELSIF ( funcmode = 'CANCEL' ) THEN
1002 null;
1003 END IF;
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_approval_not_required',
1008 itemtype, itemkey, to_char(actid), funcmode);
1009 raise;
1010 END set_approval_not_required;
1011
1012
1013 -- ****************************************************************************
1014 -- can_preparer_approve
1015 -- ****************************************************************************
1016
1017 PROCEDURE can_preparer_approve(itemtype IN VARCHAR2,
1018 itemkey IN VARCHAR2,
1019 actid IN NUMBER,
1020 funcmode IN VARCHAR2,
1021 result OUT NOCOPY VARCHAR2) IS
1022 l_preparer_id NUMBER;
1023 l_batch_id NUMBER;
1024 l_profile_option_val fnd_profile_option_values.profile_option_value%TYPE;
1025 BEGIN
1026 IF ( funcmode = 'RUN' ) THEN
1027 -- Get the preparer_id
1028 l_preparer_id := wf_engine.GetItemAttrNumber(
1029 itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => 'PREPARER_ID');
1032
1033 -- Get batch id
1034 l_batch_id := wf_engine.GetItemAttrNumber(
1035 itemtype => itemtype,
1036 itemkey => itemkey,
1037 aname => 'BATCH_ID');
1038
1039 -- Get the profile option value
1040 FND_PROFILE.GET('GL_ALLOW_PREPARER_APPROVAL', l_profile_option_val);
1041 IF (l_profile_option_val IS NULL) THEN
1042 l_profile_option_val := 'N';
1043 END IF;
1044
1045 IF ( l_profile_option_val = 'Y'
1046 AND check_authorization_limit(l_preparer_id, l_batch_id)) THEN
1047 result := 'COMPLETE:Y';
1048 ELSE
1049 result := 'COMPLETE:N';
1050 END IF;
1051
1052 ELSIF ( funcmode = 'CANCEL' ) THEN
1053 NULL;
1054 END IF;
1055
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'can_preparer_approve',
1059 itemtype, itemkey, to_char(actid), funcmode);
1060 raise;
1061 END can_preparer_approve;
1062
1063
1064 -- ****************************************************************************
1065 -- set_approver_name
1066 -- ****************************************************************************
1067
1068 PROCEDURE set_approver_name (itemtype IN VARCHAR2,
1069 itemkey IN VARCHAR2,
1070 actid IN NUMBER,
1071 funcmode IN VARCHAR2,
1072 result OUT NOCOPY VARCHAR2) IS
1073 l_preparer_id NUMBER;
1074 l_preparer_name VARCHAR2(240);
1075 l_preparer_display_name VARCHAR2(240);
1076 BEGIN
1077 IF ( funcmode = 'RUN' ) THEN
1078 l_preparer_id := wf_engine.GetItemAttrNumber(
1079 itemtype => itemtype,
1080 itemkey => itemkey,
1081 aname => 'PREPARER_ID' );
1082 l_preparer_name := wf_engine.GetItemAttrText(
1083 itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'PREPARER_NAME');
1086 l_preparer_display_name := wf_engine.GetItemAttrText(
1087 itemtype => itemtype,
1088 itemkey => itemkey,
1089 aname => 'PREPARER_DISPLAY_NAME');
1090
1091 wf_engine.SetItemAttrNumber( itemtype => itemtype,
1092 itemkey => itemkey,
1093 aname => 'APPROVER_ID',
1094 avalue => l_preparer_id );
1095 wf_engine.SetItemAttrText( itemtype => itemtype,
1096 itemkey => itemkey,
1097 aname => 'APPROVER_NAME',
1098 avalue => l_preparer_name );
1099 wf_engine.SetItemAttrText( itemtype => itemtype,
1100 itemkey => itemkey,
1101 aname => 'APPROVER_DISPLAY_NAME',
1102 avalue => l_preparer_display_name );
1103 diagn_msg('Approver name set for JE batch ');
1104
1105 ELSIF ( funcmode = 'CANCEL' ) THEN
1106 null;
1107 END IF;
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_approver_name',
1112 itemtype, itemkey, to_char(actid), funcmode);
1113 raise;
1114 END set_approver_name;
1115
1116
1117 -- ****************************************************************************
1118 -- set_je_approver
1119 -- ****************************************************************************
1120
1121 PROCEDURE set_je_approver (itemtype IN VARCHAR2,
1122 itemkey IN VARCHAR2,
1123 actid IN NUMBER,
1124 funcmode IN VARCHAR2,
1125 result OUT NOCOPY VARCHAR2) IS
1126 l_je_batch_id NUMBER;
1127 l_approver_id NUMBER;
1128 BEGIN
1129 IF (funcmode = 'RUN') THEN
1130 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1131 itemkey => itemkey,
1132 aname => 'BATCH_ID' );
1133 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1134 itemkey => itemkey,
1135 aname => 'APPROVER_ID' );
1136
1137 UPDATE GL_JE_BATCHES
1138 SET approver_employee_id = l_approver_id
1139 WHERE je_batch_id = l_je_batch_id;
1140
1141 ELSIF (funcmode = 'CANCEL') THEN
1142 null;
1143 END IF;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_je_approver',
1148 itemtype, itemkey, to_char(actid), funcmode);
1149 raise;
1150 END set_je_approver;
1151
1152
1153 -- ****************************************************************************
1154 -- approve_je
1155 -- ****************************************************************************
1156
1157 PROCEDURE approve_je (itemtype IN VARCHAR2,
1158 itemkey IN VARCHAR2,
1159 actid IN NUMBER,
1160 funcmode IN VARCHAR2,
1161 result OUT NOCOPY VARCHAR2) IS
1162 l_je_batch_id NUMBER;
1163 l_param_list WF_PARAMETER_LIST_T := wf_parameter_list_t();
1164 BEGIN
1165 IF ( funcmode = 'RUN' ) THEN
1166 diagn_msg('Executing Approve_JE');
1167 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1168 itemkey => itemkey,
1169 aname => 'BATCH_ID' );
1170
1171 UPDATE GL_JE_BATCHES
1172 SET approval_status_code = 'A'
1173 WHERE je_batch_id = l_je_batch_id;
1174
1175 -- diagn_msg('JE batch '||to_char(l_je_batch_id)||' is approved');
1176
1177 -- Raise business event for journal approved
1178 WF_EVENT.AddParameterToList(p_name => 'BATCH_ID',
1179 p_value => to_char(l_je_batch_id),
1180 p_parameterlist => l_param_list);
1181 WF_EVENT.raise(p_event_name => 'oracle.apps.gl.Journals.journal.approve',
1182 p_event_key => itemkey,
1183 p_parameters => l_param_list);
1184
1185 ELSIF ( funcmode = 'CANCEL' ) THEN
1186 null;
1187 END IF;
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'approve_je',
1192 itemtype, itemkey, to_char(actid), funcmode);
1193 raise;
1194 END approve_je;
1195
1196
1197 -- ****************************************************************************
1198 -- reject_je
1199 -- ****************************************************************************
1200
1201 PROCEDURE reject_je (itemtype IN VARCHAR2,
1202 itemkey IN VARCHAR2,
1203 actid IN NUMBER,
1204 funcmode IN VARCHAR2,
1205 result OUT NOCOPY VARCHAR2) IS
1206 l_je_batch_id NUMBER;
1207 BEGIN
1208 IF ( funcmode = 'RUN' ) THEN
1209 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1210 itemkey => itemkey,
1211 aname => 'BATCH_ID' );
1212
1213 UPDATE GL_JE_BATCHES
1214 SET approval_status_code = 'J'
1215 WHERE je_batch_id = l_je_batch_id;
1216
1217 ELSIF ( funcmode = 'CANCEL' ) THEN
1218 null;
1219 END IF;
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'reject_je',
1224 itemtype, itemkey, to_char(actid), funcmode);
1225 raise;
1226 END reject_je;
1227
1228
1229 -- ****************************************************************************
1230 -- find_approver
1231 -- ****************************************************************************
1232
1233 PROCEDURE find_approver(item_type IN VARCHAR2,
1234 item_key IN VARCHAR2,
1235 actid IN NUMBER,
1236 funcmode IN VARCHAR2,
1237 result OUT NOCOPY VARCHAR2) IS
1238 l_employee_id NUMBER;
1239 l_resp_id NUMBER;
1240 l_user_id NUMBER;
1241 l_curr_approver_id NUMBER := NULL;
1242 l_next_approver_id NUMBER := NULL;
1243 l_dir_manager_id NUMBER := NULL;
1244 l_find_approver_method VARCHAR2(240);
1245 l_preparer_name VARCHAR2(240);
1246 l_preparer_display_name VARCHAR2(240);
1247 l_defined BOOLEAN;
1248 l_find_approver_counter NUMBER;
1249 l_error_message VARCHAR2(2000);
1250 BEGIN
1251
1252 IF (funcmode = 'RUN') THEN
1253 diagn_msg('Entering Find_Approver activity');
1254
1255 l_employee_id := wf_engine.GetItemAttrNumber(item_type,
1256 item_key,
1257 'PREPARER_ID');
1258 l_curr_approver_id := wf_engine.GetItemAttrNumber(item_type,
1259 item_key,
1260 'APPROVER_ID');
1261 l_resp_id := wf_engine.GetItemAttrNumber(item_type,
1262 item_key,
1263 'PREPARER_RESP_ID');
1264 l_user_id := wf_engine.GetItemAttrNumber(item_type,
1265 item_key,
1266 'PREPARER_FND_ID');
1267
1268 -- Get the profile option value for approver method
1269 FND_PROFILE.GET_SPECIFIC('GL_FIND_APPROVER_METHOD',
1270 l_user_id,
1271 l_resp_id,
1272 101,
1273 l_find_approver_method,
1274 l_defined);
1275
1276 IF (l_find_approver_method IS NULL) THEN
1277 l_find_approver_method := 'CHAIN';
1278 END IF;
1279
1280 l_find_approver_counter := wf_engine.GetItemAttrNumber(
1281 item_type,
1282 item_key,
1283 'FIND_APPROVER_COUNTER');
1284
1285 IF (l_find_approver_counter = 0) THEN
1286
1287 diagn_msg('Find_Approver activity is called for the first time.');
1288
1289 getmanager(l_employee_id, l_dir_manager_id);
1290 setpersonas(l_dir_manager_id,
1291 item_type,
1292 item_key,
1293 'MANAGER');
1294
1295 IF (l_dir_manager_id IS NOT NULL) THEN
1296 l_next_approver_id := l_dir_manager_id;
1297 ELSE
1298 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_MANAGER');
1299 l_error_message := FND_MESSAGE.Get;
1300
1301 wf_engine.SetItemAttrText( item_type,
1302 item_key,
1303 'ERROR_MESSAGE',
1304 l_error_message);
1305
1306 result := 'COMPLETE:N';
1307 END IF;
1308
1309 END IF;
1310
1311 IF (l_curr_approver_id IS NOT NULL OR
1312 l_find_approver_method = 'DIRECT') THEN
1313
1314 diagn_msg('Find_Approver: Calling Get Approver');
1315
1316 GetApprover(l_employee_id,
1317 item_type,
1318 item_key,
1319 l_curr_approver_id,
1320 l_find_approver_method,
1321 l_next_approver_id);
1322
1323 END IF;
1324
1325 IF (l_next_approver_id IS NULL) THEN
1326 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_APPROVER');
1327 l_error_message := FND_MESSAGE.Get;
1328
1329 WF_ENGINE.SetItemAttrText( item_type,
1330 item_key,
1331 'ERROR_MESSAGE',
1332 l_error_message);
1333
1334 result := 'COMPLETE:N';
1335 ELSE
1336 setpersonas(l_next_approver_id,
1337 item_type,
1338 item_key,
1339 'APPROVER');
1340
1341 WF_ENGINE.SetItemAttrNumber(item_type,
1342 item_key,
1343 'FIND_APPROVER_COUNTER',
1344 l_find_approver_counter + 1);
1345
1346 result := 'COMPLETE:Y';
1347 END IF;
1348
1349 ELSIF (funcmode = 'CANCEL') THEN
1350 NULL;
1351 END IF;
1352
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'Find_Approver',
1356 item_type, item_key, to_char(actid), funcmode);
1357 raise;
1358
1359 END find_approver;
1360
1361
1362 -- ****************************************************************************
1363 -- first_approver
1364 -- ****************************************************************************
1365
1366 PROCEDURE first_approver(item_type IN VARCHAR2,
1367 item_key IN VARCHAR2,
1368 actid IN NUMBER,
1369 funcmode IN VARCHAR2,
1370 result OUT NOCOPY VARCHAR2) IS
1371 l_find_approver_counter NUMBER;
1372 l_preparer_name VARCHAR2(240);
1373 l_forward_comment_from VARCHAR2(300);
1374 l_forward_from_display_name VARCHAR2(240);
1375 BEGIN
1376 IF (funcmode = 'RUN') THEN
1377 diagn_msg('First_Approver: Retrieving Find_Approver_Counter Item Attribute');
1378
1379 l_find_approver_counter := WF_ENGINE.GetItemAttrNumber(
1380 item_type,
1381 item_key,
1382 'FIND_APPROVER_COUNTER');
1383
1384 -- Set the approver comment attribute to null
1385 WF_ENGINE.SetItemAttrText(item_type,
1386 item_key,
1387 'APPROVER_COMMENT',
1388 '');
1389
1390 IF (l_find_approver_counter = 1) THEN
1391 result := 'COMPLETE:Y';
1392
1393 --bugfix 2926418
1394 WF_ENGINE.SetItemAttrText(item_type,
1395 item_key,
1396 'COMMENT_FROM',
1397 '');
1398
1399 -- Put preparer name as the forward from name
1400 l_preparer_name := WF_ENGINE.GetItemAttrText(
1401 item_type,
1402 item_key,
1403 'PREPARER_NAME');
1404 WF_ENGINE.SetItemAttrText(item_type,
1405 item_key,
1406 'FORWARD_FROM_NAME',
1407 l_preparer_name);
1408 ELSE
1409 result := 'COMPLETE:N';
1410
1411 --bugfix 2926418
1412 l_forward_from_display_name := WF_ENGINE.GetItemAttrText(
1413 item_type,
1414 item_key,
1415 'FORWARD_FROM_DISPLAY_NAME');
1416
1417 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_FORWARD_COMMENT_FROM');
1418 FND_MESSAGE.Set_Token('APPROVER_NAME', l_forward_from_display_name);
1419 l_forward_comment_from := FND_MESSAGE.Get;
1420
1421 WF_ENGINE.SetItemAttrText(item_type,
1422 item_key,
1423 'COMMENT_FROM',
1424 l_forward_comment_from);
1425 END IF;
1426
1427 ELSIF (funcmode = 'CANCEL') THEN
1428 NULL;
1429 END IF;
1430
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'first_approver',
1434 item_type, item_key, to_char(actid), funcmode);
1435 raise;
1436 END first_approver;
1437
1438
1439 -- ****************************************************************************
1440 -- set_curr_approver
1441 -- ****************************************************************************
1442
1443 PROCEDURE set_curr_approver(itemtype IN VARCHAR2,
1444 itemkey IN VARCHAR2,
1445 actid IN NUMBER,
1446 funcmode IN VARCHAR2,
1447 result OUT NOCOPY VARCHAR2) IS
1448 l_TransferToID NUMBER;
1449 l_Transferee wf_users.name%type;
1450 l_TransferToName wf_users.name%type;
1451 l_role wf_roles.name%type;
1452 l_notification_id number;
1453
1454 CURSOR c_person_id IS
1455 SELECT orig_system_id
1456 FROM wf_roles
1457 WHERE orig_system = 'PER'
1458 AND name = l_role;
1459
1460 BEGIN
1461
1462 IF (funcmode = 'RESPOND') THEN
1463 -- wf_engine.context_text = new responder
1464 l_notification_id := wf_engine.context_nid;
1465
1466 SELECT original_recipient
1467 INTO l_role
1468 FROM wf_notifications
1469 WHERE notification_id = l_notification_id;
1470
1471 l_Transferee := wf_engine.context_text;
1472
1473 OPEN c_person_id;
1474 FETCH c_person_id into l_TransferToID;
1475
1476 IF c_person_id%NOTFOUND THEN
1477 result := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1478 Wf_Core.Raise(wf_core.translate('NO_ROLE_FOUND'));
1479 RETURN;
1480 ELSE
1481 IF l_TransferToID IS NULL THEN
1482 result := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1483 Wf_Core.Raise(wf_core.translate('PERSON_ID_NULL'));
1484 RETURN;
1485 END IF;
1486 END IF;
1487
1488 CLOSE c_person_id;
1489
1490 -- set approver name and approver display name
1491 setpersonas(l_TransferToID,
1492 itemtype,
1493 itemkey,
1494 'APPROVER');
1495 END IF;
1496
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_curr_approver', itemtype, itemkey);
1500 raise;
1501 END set_curr_approver;
1502
1503
1504 -- ****************************************************************************
1505 -- mgr_equalto_aprv
1506 -- ****************************************************************************
1507
1508 PROCEDURE mgr_equalto_aprv(item_type IN VARCHAR2,
1509 item_key IN VARCHAR2,
1510 actid IN NUMBER,
1511 funcmode IN VARCHAR2,
1512 result OUT NOCOPY VARCHAR2) IS
1513 l_approver_id NUMBER;
1514 l_manager_id NUMBER;
1515 BEGIN
1516 IF (funcmode = 'RUN') THEN
1517 l_approver_id := WF_ENGINE.GetItemAttrNumber(item_type,
1518 item_key,
1519 'APPROVER_ID');
1520
1521 l_manager_id := WF_ENGINE.GetItemAttrNumber(item_type,
1522 item_key,
1523 'MANAGER_ID');
1524
1525 IF (l_approver_id <> l_manager_id) THEN
1526 result := 'COMPLETE:N';
1527 ELSE
1528 result := 'COMPLETE:Y';
1529 END IF;
1530
1531 ELSIF (funcmode = 'CANCEL') THEN
1532 NULL;
1533 END IF;
1534
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'mgr_equalto_aprv',
1538 item_type, item_key, to_char(actid), funcmode);
1539 raise;
1540 END mgr_equalto_aprv;
1541
1542
1543 -- ****************************************************************************
1544 -- notifyprep_noaprvresp
1545 -- ****************************************************************************
1546
1547 PROCEDURE notifyprep_noaprvresp(item_type IN VARCHAR2,
1548 item_key IN VARCHAR2,
1549 actid IN NUMBER,
1550 funcmode IN VARCHAR2,
1551 result OUT NOCOPY VARCHAR2) IS
1552 l_approver_id NUMBER;
1553 l_manager_id NUMBER;
1554 l_count NUMBER;
1555 l_limit NUMBER;
1556 BEGIN
1557 IF (funcmode = 'RUN') THEN
1558 l_count := WF_ENGINE.GetItemAttrNumber(item_type,
1559 item_key,
1560 'MANAGER_APPROVAL_SEND_COUNT');
1561
1562 l_limit := WF_ENGINE.GetActivityAttrNumber(item_type,
1563 item_key,
1564 actid,
1565 'MANAGER_SEND_LIMIT');
1566
1567 WF_ENGINE.SetItemAttrNumber(item_type,
1568 item_key,
1569 'MANAGER_APPROVAL_SEND_COUNT',
1570 l_count + 1);
1571
1572 IF (l_count+1 >= l_limit) THEN
1573 WF_ENGINE.SetItemAttrNumber(item_type,
1574 item_key,
1575 'MANAGER_APPROVAL_SEND_COUNT',
1576 0);
1577 result := 'COMPLETE:Y';
1578 ELSE
1579 result := 'COMPLETE:N';
1580 END IF;
1581
1582 ELSIF (funcmode = 'CANCEL') THEN
1583 NULL;
1584 END IF;
1585
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'notifyprep_noaprvresp',
1589 item_type, item_key, to_char(actid), funcmode);
1590 raise;
1591 END notifyprep_noaprvresp;
1592
1593
1594 -- ****************************************************************************
1595 -- get_approver_manager
1596 -- ****************************************************************************
1597
1598 PROCEDURE get_approver_manager(item_type IN VARCHAR2,
1599 item_key IN VARCHAR2,
1600 actid IN NUMBER,
1601 funcmode IN VARCHAR2,
1602 result OUT NOCOPY VARCHAR2) IS
1603 l_approver_id NUMBER;
1604 l_manager_id NUMBER;
1605 BEGIN
1606
1607 IF ( funcmode = 'RUN' ) THEN
1608 l_approver_id := WF_ENGINE.GetItemAttrNumber( item_type,
1609 item_key,
1610 'APPROVER_ID' );
1611
1612 getmanager(l_approver_id, l_manager_id);
1613
1614 IF (l_manager_id IS NULL) THEN
1615 result := 'COMPLETE:FAIL';
1616 ELSE
1617 setpersonas(l_manager_id,
1618 item_type,
1619 item_key,
1620 'APPROVER');
1621 result := 'COMPLETE:PASS';
1622 END IF;
1623
1624 ELSIF ( funcmode = 'CANCEL' ) THEN
1625 NULL;
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'get_approver_manager',
1631 item_type, item_key, to_char(actid), funcmode);
1632 raise;
1633 END get_approver_manager;
1634
1635
1636 -- ****************************************************************************
1637 -- record_forward_from_info
1638 -- ****************************************************************************
1639
1640 PROCEDURE record_forward_from_info(item_type IN VARCHAR2,
1641 item_key IN VARCHAR2,
1642 actid IN NUMBER,
1643 funcmode IN VARCHAR2,
1644 result OUT NOCOPY VARCHAR2) IS
1645 l_approver_id NUMBER;
1646 l_approver_name VARCHAR2(240);
1647 l_approver_display_name VARCHAR2(240);
1648 l_approver_comment VARCHAR2(2000);
1649 BEGIN
1650 IF (funcmode = 'RUN') THEN
1651 l_approver_id := WF_ENGINE.GetItemAttrNumber(item_type,
1652 item_key,
1653 'APPROVER_ID');
1654
1655 l_approver_name := WF_ENGINE.GetItemAttrText(item_type,
1656 item_key,
1657 'APPROVER_NAME');
1658
1659 l_approver_display_name := WF_ENGINE.GetItemAttrText(
1660 item_type,
1661 item_key,
1662 'APPROVER_DISPLAY_NAME');
1663
1664 l_approver_comment := WF_ENGINE.GetItemAttrText(item_type,
1665 item_key,
1666 'APPROVER_COMMENT');
1667
1668 WF_ENGINE.SetItemAttrNumber(item_type,
1669 item_key,
1670 'FORWARD_FROM_ID',
1671 l_approver_id);
1672
1673 WF_ENGINE.SetItemAttrText(item_type,
1674 item_key,
1675 'FORWARD_FROM_NAME',
1676 l_approver_name);
1677
1678 WF_ENGINE.SetItemAttrText(item_type,
1679 item_key,
1680 'FORWARD_FROM_DISPLAY_NAME',
1681 l_approver_display_name);
1682
1683 -- bugfix 2926418
1684 WF_ENGINE.SetItemAttrText(item_type,
1685 item_key,
1686 'FORWARD_APPROVER_COMMENT',
1687 l_approver_comment);
1688
1689 ELSIF (funcmode = 'CANCEL') THEN
1690 NULL;
1691 END IF;
1692
1693 EXCEPTION
1694 WHEN OTHERS THEN
1695 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'record_forward_from_info',
1696 item_type, item_key, to_char(actid), funcmode);
1697 raise;
1698 END record_forward_from_info;
1699
1700
1701 -- ****************************************************************************
1702 -- verify_authority
1703 -- ****************************************************************************
1704
1705 PROCEDURE verify_authority(itemtype IN VARCHAR2,
1706 itemkey IN VARCHAR2,
1707 actid IN NUMBER,
1708 funcmode IN VARCHAR2,
1709 result OUT NOCOPY VARCHAR2) IS
1710 l_approver_id NUMBER;
1711 l_batch_id NUMBER;
1712 BEGIN
1713 IF ( funcmode = 'RUN' ) THEN
1714
1715 diagn_msg('Executing Verify_Authority');
1716 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1717 itemkey => itemkey,
1718 aname => 'APPROVER_ID' );
1719 l_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1720 itemkey => itemkey,
1721 aname => 'BATCH_ID' );
1722
1723 IF (check_authorization_limit(l_approver_id, l_batch_id)) THEN
1724 result := 'COMPLETE:PASS';
1725 ELSE
1726 result := 'COMPLETE:FAIL';
1727 END IF;
1728
1729 ELSIF ( funcmode = 'CANCEL' ) THEN
1730 NULL;
1731 END IF;
1732
1733 EXCEPTION
1734 WHEN OTHERS THEN
1735 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'verify_authority',
1736 itemtype, itemkey, to_char(actid), funcmode);
1737 raise;
1738 END verify_authority;
1739
1740
1741 -- ****************************************************************************
1742 -- Abort_Process
1743 -- ****************************************************************************
1744
1745 PROCEDURE abort_process(itemtype IN VARCHAR2,
1746 itemkey IN VARCHAR2,
1747 actid IN NUMBER,
1748 funcmode IN VARCHAR2,
1749 result OUT NOCOPY VARCHAR2) IS
1750 l_je_batch_id NUMBER;
1751 BEGIN
1752 IF (funcmode = 'RUN') THEN
1753 -- reset approval status of JE batch back to 'Required'
1754 l_je_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1755 itemkey => itemkey,
1756 aname => 'BATCH_ID');
1757
1758 UPDATE GL_JE_BATCHES
1759 SET approval_status_code = 'R'
1760 WHERE je_batch_id = l_je_batch_id;
1761
1762 -- then abort the process
1763 WF_ENGINE.AbortProcess(itemtype, itemkey, '', WF_ENGINE.eng_timeout);
1764
1765 ELSIF (funcmode = 'CANCEL') THEN
1766 null;
1767 END IF;
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'abort_process',
1772 itemtype, itemkey, to_char(actid), funcmode);
1773 raise;
1774 END abort_process;
1775
1776
1777 END GL_WF_JE_APPROVAL_PKG;