[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.4 2009/06/22 10:42:26 skotakar 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
847 l_budgetary_status <> 'P'
848 AND l_balance_type <> 'B') THEN /* added this condition for bug7531835 */
849 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_RESERVE_FUNDS');
850 l_invalid_error := FND_MESSAGE.Get;
851 wf_engine.SetItemAttrText( itemtype,
852 itemkey,
853 'INVALID_JE_ERROR',
854 l_invalid_error );
855 result := 'COMPLETE:N';
856 return;
857 END IF;
858
859 -- Make sure the control total matches the
860 -- running totals
861 IF ( l_control_total IS NULL
862 OR ( l_balance_type IN ('A', 'E')
863 AND l_running_total_dr = l_control_total)
864 OR ( l_balance_type = 'B'
865 AND greatest(l_running_total_cr, l_running_total_dr)
866 = l_control_total)
867 ) THEN
868 null;
869 ELSE
870 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_CONTROL_TOTAL');
871 l_invalid_error := FND_MESSAGE.Get;
872 wf_engine.SetItemAttrText( itemtype,
873 itemkey,
874 'INVALID_JE_ERROR',
875 l_invalid_error);
876 result := 'COMPLETE:N';
877 return;
878 END IF;
879
880 -- If the batch passes all the above checks, then its valid.
881 result := 'COMPLETE:Y';
882
883 ELSIF (funcmode = 'CANCEL') THEN
884 NULL;
885 END IF;
886
887 EXCEPTION
888 WHEN OTHERS THEN
889 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'is_je_valid',
890 itemtype, itemkey, to_char(actid), funcmode);
891 raise;
892 END is_je_valid;
893
894
895 -- ****************************************************************************
896 -- set_je_invalid
897 -- ****************************************************************************
898
899 PROCEDURE set_je_invalid(itemtype IN VARCHAR2,
900 itemkey IN VARCHAR2,
901 actid IN NUMBER,
902 funcmode IN VARCHAR2,
903 result OUT NOCOPY VARCHAR2) IS
904 l_je_batch_id NUMBER;
905 BEGIN
906 IF ( funcmode = 'RUN' ) THEN
907 -- Get JE batch ID
908 l_je_batch_id := wf_engine.GetItemAttrNumber(
909 itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'BATCH_ID');
912
913 UPDATE GL_JE_BATCHES
914 SET approval_status_code = 'V'
915 WHERE je_batch_id = l_je_batch_id;
916
917 ELSIF ( funcmode = 'CANCEL' ) THEN
918 null;
919 END IF;
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_je_invalid',
924 itemtype, itemkey, to_char(actid), funcmode);
925 raise;
926 END set_je_invalid;
927
928
929 -- ****************************************************************************
930 -- does_je_need_approval
931 -- ****************************************************************************
932
933 PROCEDURE does_je_need_approval(itemtype IN VARCHAR2,
934 itemkey IN VARCHAR2,
935 actid IN NUMBER,
936 funcmode IN VARCHAR2,
937 result OUT NOCOPY VARCHAR2) IS
938 l_je_batch_id NUMBER;
939 l_non_stat_cursor VARCHAR2(40);
940
941 CURSOR non_stat IS
942 SELECT 'non stat journal exists'
943 FROM DUAL
944 WHERE EXISTS
945 (SELECT 'X'
946 FROM GL_JE_HEADERS
947 WHERE je_batch_id = l_je_batch_id
948 AND currency_code <> 'STAT');
949 BEGIN
950 IF ( funcmode = 'RUN' ) THEN
951 -- Get JE batch ID (primary key)
952 l_je_batch_id := wf_engine.GetItemAttrNumber(
953 itemtype => itemtype,
954 itemkey => itemkey,
955 aname => 'BATCH_ID');
956
957 -- Check whether STAT journal exists
958 OPEN non_stat;
959 FETCH non_stat INTO l_non_stat_cursor;
960
961 IF non_stat%FOUND THEN
962 CLOSE non_stat;
963 result := 'COMPLETE:Y';
964 ELSE
965 CLOSE non_stat;
966 result := 'COMPLETE:N';
967 END IF;
968
969 ELSIF ( funcmode = 'CANCEL' ) THEN
970 NULL;
971 END IF;
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'does_je_need_approval',
976 itemtype, itemkey, to_char(actid), funcmode);
977 raise;
978 END does_je_need_approval;
979
980
981 -- ****************************************************************************
982 -- set_approval_not_required
983 -- ****************************************************************************
984
985 PROCEDURE set_approval_not_required(itemtype IN VARCHAR2,
986 itemkey IN VARCHAR2,
987 actid IN NUMBER,
988 funcmode IN VARCHAR2,
989 result OUT NOCOPY VARCHAR2) IS
990 l_je_batch_id NUMBER;
991 BEGIN
992 IF ( funcmode = 'RUN' ) THEN
993 -- Get JE batch ID
994 l_je_batch_id := wf_engine.GetItemAttrNumber(
995 itemtype => itemtype,
996 itemkey => itemkey,
997 aname => 'BATCH_ID');
998
999 UPDATE GL_JE_BATCHES
1000 SET approval_status_code = 'A'
1001 WHERE je_batch_id = l_je_batch_id;
1002
1003 ELSIF ( funcmode = 'CANCEL' ) THEN
1004 null;
1005 END IF;
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_approval_not_required',
1010 itemtype, itemkey, to_char(actid), funcmode);
1011 raise;
1012 END set_approval_not_required;
1013
1014
1015 -- ****************************************************************************
1016 -- can_preparer_approve
1017 -- ****************************************************************************
1018
1019 PROCEDURE can_preparer_approve(itemtype IN VARCHAR2,
1020 itemkey IN VARCHAR2,
1021 actid IN NUMBER,
1022 funcmode IN VARCHAR2,
1023 result OUT NOCOPY VARCHAR2) IS
1024 l_preparer_id NUMBER;
1025 l_batch_id NUMBER;
1026 l_profile_option_val fnd_profile_option_values.profile_option_value%TYPE;
1027 BEGIN
1028 IF ( funcmode = 'RUN' ) THEN
1029 -- Get the preparer_id
1030 l_preparer_id := wf_engine.GetItemAttrNumber(
1031 itemtype => itemtype,
1032 itemkey => itemkey,
1033 aname => 'PREPARER_ID');
1034
1035 -- Get batch id
1036 l_batch_id := wf_engine.GetItemAttrNumber(
1037 itemtype => itemtype,
1038 itemkey => itemkey,
1039 aname => 'BATCH_ID');
1040
1041 -- Get the profile option value
1042 FND_PROFILE.GET('GL_ALLOW_PREPARER_APPROVAL', l_profile_option_val);
1043 IF (l_profile_option_val IS NULL) THEN
1044 l_profile_option_val := 'N';
1045 END IF;
1046
1047 IF ( l_profile_option_val = 'Y'
1048 AND check_authorization_limit(l_preparer_id, l_batch_id)) THEN
1049 result := 'COMPLETE:Y';
1050 ELSE
1051 result := 'COMPLETE:N';
1052 END IF;
1053
1054 ELSIF ( funcmode = 'CANCEL' ) THEN
1055 NULL;
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'can_preparer_approve',
1061 itemtype, itemkey, to_char(actid), funcmode);
1062 raise;
1063 END can_preparer_approve;
1064
1065
1066 -- ****************************************************************************
1067 -- set_approver_name
1068 -- ****************************************************************************
1069
1070 PROCEDURE set_approver_name (itemtype IN VARCHAR2,
1071 itemkey IN VARCHAR2,
1072 actid IN NUMBER,
1073 funcmode IN VARCHAR2,
1074 result OUT NOCOPY VARCHAR2) IS
1075 l_preparer_id NUMBER;
1076 l_preparer_name VARCHAR2(240);
1077 l_preparer_display_name VARCHAR2(240);
1078 BEGIN
1079 IF ( funcmode = 'RUN' ) THEN
1080 l_preparer_id := wf_engine.GetItemAttrNumber(
1081 itemtype => itemtype,
1082 itemkey => itemkey,
1083 aname => 'PREPARER_ID' );
1084 l_preparer_name := wf_engine.GetItemAttrText(
1085 itemtype => itemtype,
1086 itemkey => itemkey,
1087 aname => 'PREPARER_NAME');
1088 l_preparer_display_name := wf_engine.GetItemAttrText(
1089 itemtype => itemtype,
1090 itemkey => itemkey,
1091 aname => 'PREPARER_DISPLAY_NAME');
1092
1093 wf_engine.SetItemAttrNumber( itemtype => itemtype,
1094 itemkey => itemkey,
1095 aname => 'APPROVER_ID',
1096 avalue => l_preparer_id );
1097 wf_engine.SetItemAttrText( itemtype => itemtype,
1098 itemkey => itemkey,
1099 aname => 'APPROVER_NAME',
1100 avalue => l_preparer_name );
1101 wf_engine.SetItemAttrText( itemtype => itemtype,
1102 itemkey => itemkey,
1103 aname => 'APPROVER_DISPLAY_NAME',
1104 avalue => l_preparer_display_name );
1105 diagn_msg('Approver name set for JE batch ');
1106
1107 ELSIF ( funcmode = 'CANCEL' ) THEN
1108 null;
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_approver_name',
1114 itemtype, itemkey, to_char(actid), funcmode);
1115 raise;
1116 END set_approver_name;
1117
1118
1119 -- ****************************************************************************
1120 -- set_je_approver
1121 -- ****************************************************************************
1122
1123 PROCEDURE set_je_approver (itemtype IN VARCHAR2,
1124 itemkey IN VARCHAR2,
1125 actid IN NUMBER,
1126 funcmode IN VARCHAR2,
1127 result OUT NOCOPY VARCHAR2) IS
1128 l_je_batch_id NUMBER;
1129 l_approver_id NUMBER;
1130 BEGIN
1131 IF (funcmode = 'RUN') THEN
1132 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1133 itemkey => itemkey,
1134 aname => 'BATCH_ID' );
1135 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1136 itemkey => itemkey,
1137 aname => 'APPROVER_ID' );
1138
1139 UPDATE GL_JE_BATCHES
1140 SET approver_employee_id = l_approver_id
1141 WHERE je_batch_id = l_je_batch_id;
1142
1143 ELSIF (funcmode = 'CANCEL') THEN
1144 null;
1145 END IF;
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_je_approver',
1150 itemtype, itemkey, to_char(actid), funcmode);
1151 raise;
1152 END set_je_approver;
1153
1154
1155 -- ****************************************************************************
1156 -- approve_je
1157 -- ****************************************************************************
1158
1159 PROCEDURE approve_je (itemtype IN VARCHAR2,
1160 itemkey IN VARCHAR2,
1161 actid IN NUMBER,
1162 funcmode IN VARCHAR2,
1163 result OUT NOCOPY VARCHAR2) IS
1164 l_je_batch_id NUMBER;
1165 l_param_list WF_PARAMETER_LIST_T := wf_parameter_list_t();
1166 BEGIN
1167 IF ( funcmode = 'RUN' ) THEN
1168 diagn_msg('Executing Approve_JE');
1169 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'BATCH_ID' );
1172
1173 UPDATE GL_JE_BATCHES
1174 SET approval_status_code = 'A'
1175 WHERE je_batch_id = l_je_batch_id;
1176
1177 -- diagn_msg('JE batch '||to_char(l_je_batch_id)||' is approved');
1178
1179 -- Raise business event for journal approved
1180 WF_EVENT.AddParameterToList(p_name => 'BATCH_ID',
1181 p_value => to_char(l_je_batch_id),
1182 p_parameterlist => l_param_list);
1183 WF_EVENT.raise(p_event_name => 'oracle.apps.gl.Journals.journal.approve',
1184 p_event_key => itemkey,
1185 p_parameters => l_param_list);
1186
1187 ELSIF ( funcmode = 'CANCEL' ) THEN
1188 null;
1189 END IF;
1190
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'approve_je',
1194 itemtype, itemkey, to_char(actid), funcmode);
1195 raise;
1196 END approve_je;
1197
1198
1199 -- ****************************************************************************
1200 -- reject_je
1201 -- ****************************************************************************
1202
1203 PROCEDURE reject_je (itemtype IN VARCHAR2,
1204 itemkey IN VARCHAR2,
1205 actid IN NUMBER,
1206 funcmode IN VARCHAR2,
1207 result OUT NOCOPY VARCHAR2) IS
1208 l_je_batch_id NUMBER;
1209 BEGIN
1210 IF ( funcmode = 'RUN' ) THEN
1211 l_je_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1212 itemkey => itemkey,
1213 aname => 'BATCH_ID' );
1214
1215 UPDATE GL_JE_BATCHES
1216 SET approval_status_code = 'J'
1217 WHERE je_batch_id = l_je_batch_id;
1218
1219 ELSIF ( funcmode = 'CANCEL' ) THEN
1220 null;
1221 END IF;
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'reject_je',
1226 itemtype, itemkey, to_char(actid), funcmode);
1227 raise;
1228 END reject_je;
1229
1230
1231 -- ****************************************************************************
1232 -- find_approver
1233 -- ****************************************************************************
1234
1235 PROCEDURE find_approver(item_type IN VARCHAR2,
1236 item_key IN VARCHAR2,
1237 actid IN NUMBER,
1238 funcmode IN VARCHAR2,
1239 result OUT NOCOPY VARCHAR2) IS
1240 l_employee_id NUMBER;
1241 l_resp_id NUMBER;
1242 l_user_id NUMBER;
1243 l_curr_approver_id NUMBER := NULL;
1244 l_next_approver_id NUMBER := NULL;
1245 l_dir_manager_id NUMBER := NULL;
1246 l_find_approver_method VARCHAR2(240);
1247 l_preparer_name VARCHAR2(240);
1248 l_preparer_display_name VARCHAR2(240);
1249 l_defined BOOLEAN;
1250 l_find_approver_counter NUMBER;
1251 l_error_message VARCHAR2(2000);
1252 BEGIN
1253
1254 IF (funcmode = 'RUN') THEN
1255 diagn_msg('Entering Find_Approver activity');
1256
1257 l_employee_id := wf_engine.GetItemAttrNumber(item_type,
1258 item_key,
1259 'PREPARER_ID');
1260 l_curr_approver_id := wf_engine.GetItemAttrNumber(item_type,
1261 item_key,
1262 'APPROVER_ID');
1263 l_resp_id := wf_engine.GetItemAttrNumber(item_type,
1264 item_key,
1265 'PREPARER_RESP_ID');
1266 l_user_id := wf_engine.GetItemAttrNumber(item_type,
1267 item_key,
1268 'PREPARER_FND_ID');
1269
1270 -- Get the profile option value for approver method
1271 FND_PROFILE.GET_SPECIFIC('GL_FIND_APPROVER_METHOD',
1272 l_user_id,
1273 l_resp_id,
1274 101,
1275 l_find_approver_method,
1276 l_defined);
1277
1278 IF (l_find_approver_method IS NULL) THEN
1279 l_find_approver_method := 'CHAIN';
1280 END IF;
1281
1282 l_find_approver_counter := wf_engine.GetItemAttrNumber(
1283 item_type,
1284 item_key,
1285 'FIND_APPROVER_COUNTER');
1286
1287 IF (l_find_approver_counter = 0) THEN
1288
1289 diagn_msg('Find_Approver activity is called for the first time.');
1290
1291 getmanager(l_employee_id, l_dir_manager_id);
1292 setpersonas(l_dir_manager_id,
1293 item_type,
1294 item_key,
1295 'MANAGER');
1296
1297 IF (l_dir_manager_id IS NOT NULL) THEN
1298 l_next_approver_id := l_dir_manager_id;
1299 ELSE
1300 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_MANAGER');
1301 l_error_message := FND_MESSAGE.Get;
1302
1303 wf_engine.SetItemAttrText( item_type,
1304 item_key,
1305 'ERROR_MESSAGE',
1306 l_error_message);
1307
1308 result := 'COMPLETE:N';
1309 END IF;
1310
1311 END IF;
1312
1313 IF (l_curr_approver_id IS NOT NULL OR
1314 l_find_approver_method = 'DIRECT') THEN
1315
1316 diagn_msg('Find_Approver: Calling Get Approver');
1317
1318 GetApprover(l_employee_id,
1319 item_type,
1320 item_key,
1321 l_curr_approver_id,
1322 l_find_approver_method,
1323 l_next_approver_id);
1324
1325 END IF;
1326
1327 IF (l_next_approver_id IS NULL) THEN
1328 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_CANNOT_FIND_APPROVER');
1329 l_error_message := FND_MESSAGE.Get;
1330
1331 WF_ENGINE.SetItemAttrText( item_type,
1332 item_key,
1333 'ERROR_MESSAGE',
1334 l_error_message);
1335
1336 result := 'COMPLETE:N';
1337 ELSE
1338 setpersonas(l_next_approver_id,
1339 item_type,
1340 item_key,
1341 'APPROVER');
1342
1343 WF_ENGINE.SetItemAttrNumber(item_type,
1344 item_key,
1345 'FIND_APPROVER_COUNTER',
1346 l_find_approver_counter + 1);
1347
1348 result := 'COMPLETE:Y';
1349 END IF;
1350
1351 ELSIF (funcmode = 'CANCEL') THEN
1352 NULL;
1353 END IF;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'Find_Approver',
1358 item_type, item_key, to_char(actid), funcmode);
1359 raise;
1360
1361 END find_approver;
1362
1363
1364 -- ****************************************************************************
1365 -- first_approver
1366 -- ****************************************************************************
1367
1368 PROCEDURE first_approver(item_type IN VARCHAR2,
1369 item_key IN VARCHAR2,
1370 actid IN NUMBER,
1371 funcmode IN VARCHAR2,
1372 result OUT NOCOPY VARCHAR2) IS
1373 l_find_approver_counter NUMBER;
1374 l_preparer_name VARCHAR2(240);
1375 l_forward_comment_from VARCHAR2(300);
1376 l_forward_from_display_name VARCHAR2(240);
1377 BEGIN
1378 IF (funcmode = 'RUN') THEN
1379 diagn_msg('First_Approver: Retrieving Find_Approver_Counter Item Attribute');
1380
1381 l_find_approver_counter := WF_ENGINE.GetItemAttrNumber(
1382 item_type,
1383 item_key,
1384 'FIND_APPROVER_COUNTER');
1385
1386 -- Set the approver comment attribute to null
1387 WF_ENGINE.SetItemAttrText(item_type,
1388 item_key,
1389 'APPROVER_COMMENT',
1390 '');
1391
1392 IF (l_find_approver_counter = 1) THEN
1393 result := 'COMPLETE:Y';
1394
1395 --bugfix 2926418
1396 WF_ENGINE.SetItemAttrText(item_type,
1397 item_key,
1398 'COMMENT_FROM',
1399 '');
1400
1401 -- Put preparer name as the forward from name
1402 l_preparer_name := WF_ENGINE.GetItemAttrText(
1403 item_type,
1404 item_key,
1405 'PREPARER_NAME');
1406 WF_ENGINE.SetItemAttrText(item_type,
1407 item_key,
1408 'FORWARD_FROM_NAME',
1409 l_preparer_name);
1410 ELSE
1411 result := 'COMPLETE:N';
1412
1413 --bugfix 2926418
1414 l_forward_from_display_name := WF_ENGINE.GetItemAttrText(
1415 item_type,
1416 item_key,
1417 'FORWARD_FROM_DISPLAY_NAME');
1418
1419 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_FORWARD_COMMENT_FROM');
1420 FND_MESSAGE.Set_Token('APPROVER_NAME', l_forward_from_display_name);
1421 l_forward_comment_from := FND_MESSAGE.Get;
1422
1423 WF_ENGINE.SetItemAttrText(item_type,
1424 item_key,
1425 'COMMENT_FROM',
1426 l_forward_comment_from);
1427 END IF;
1428
1429 ELSIF (funcmode = 'CANCEL') THEN
1430 NULL;
1431 END IF;
1432
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'first_approver',
1436 item_type, item_key, to_char(actid), funcmode);
1437 raise;
1438 END first_approver;
1439
1440
1441 -- ****************************************************************************
1442 -- set_curr_approver
1443 -- ****************************************************************************
1444
1445 PROCEDURE set_curr_approver(itemtype IN VARCHAR2,
1446 itemkey IN VARCHAR2,
1447 actid IN NUMBER,
1448 funcmode IN VARCHAR2,
1449 result OUT NOCOPY VARCHAR2) IS
1450 l_TransferToID NUMBER;
1451 l_Transferee wf_users.name%type;
1452 l_TransferToName wf_users.name%type;
1453 l_role wf_roles.name%type;
1454 l_notification_id number;
1455
1456 CURSOR c_person_id IS
1457 SELECT orig_system_id
1458 FROM wf_roles
1459 WHERE orig_system = 'PER'
1460 AND name = l_role;
1461
1462 BEGIN
1463
1464 IF (funcmode = 'RESPOND') THEN
1465 -- wf_engine.context_text = new responder
1466 l_notification_id := wf_engine.context_nid;
1467
1468 SELECT original_recipient
1469 INTO l_role
1470 FROM wf_notifications
1471 WHERE notification_id = l_notification_id;
1472
1473 l_Transferee := wf_engine.context_text;
1474
1475 OPEN c_person_id;
1476 FETCH c_person_id into l_TransferToID;
1477
1478 IF c_person_id%NOTFOUND THEN
1479 result := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1480 Wf_Core.Raise(wf_core.translate('NO_ROLE_FOUND'));
1481 RETURN;
1482 ELSE
1483 IF l_TransferToID IS NULL THEN
1484 result := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1485 Wf_Core.Raise(wf_core.translate('PERSON_ID_NULL'));
1486 RETURN;
1487 END IF;
1488 END IF;
1489
1490 CLOSE c_person_id;
1491
1492 -- set approver name and approver display name
1493 setpersonas(l_TransferToID,
1494 itemtype,
1495 itemkey,
1496 'APPROVER');
1497 END IF;
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'set_curr_approver', itemtype, itemkey);
1502 raise;
1503 END set_curr_approver;
1504
1505
1506 -- ****************************************************************************
1507 -- mgr_equalto_aprv
1508 -- ****************************************************************************
1509
1510 PROCEDURE mgr_equalto_aprv(item_type IN VARCHAR2,
1511 item_key IN VARCHAR2,
1512 actid IN NUMBER,
1513 funcmode IN VARCHAR2,
1514 result OUT NOCOPY VARCHAR2) IS
1515 l_approver_id NUMBER;
1516 l_manager_id NUMBER;
1517 BEGIN
1518 IF (funcmode = 'RUN') THEN
1519 l_approver_id := WF_ENGINE.GetItemAttrNumber(item_type,
1520 item_key,
1521 'APPROVER_ID');
1522
1523 l_manager_id := WF_ENGINE.GetItemAttrNumber(item_type,
1524 item_key,
1525 'MANAGER_ID');
1526
1527 IF (l_approver_id <> l_manager_id) THEN
1528 result := 'COMPLETE:N';
1529 ELSE
1530 result := 'COMPLETE:Y';
1531 END IF;
1532
1533 ELSIF (funcmode = 'CANCEL') THEN
1534 NULL;
1535 END IF;
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'mgr_equalto_aprv',
1540 item_type, item_key, to_char(actid), funcmode);
1541 raise;
1542 END mgr_equalto_aprv;
1543
1544
1545 -- ****************************************************************************
1546 -- notifyprep_noaprvresp
1547 -- ****************************************************************************
1548
1549 PROCEDURE notifyprep_noaprvresp(item_type IN VARCHAR2,
1550 item_key IN VARCHAR2,
1551 actid IN NUMBER,
1552 funcmode IN VARCHAR2,
1553 result OUT NOCOPY VARCHAR2) IS
1554 l_approver_id NUMBER;
1555 l_manager_id NUMBER;
1556 l_count NUMBER;
1557 l_limit NUMBER;
1558 BEGIN
1559 IF (funcmode = 'RUN') THEN
1560 l_count := WF_ENGINE.GetItemAttrNumber(item_type,
1561 item_key,
1562 'MANAGER_APPROVAL_SEND_COUNT');
1563
1564 l_limit := WF_ENGINE.GetActivityAttrNumber(item_type,
1565 item_key,
1566 actid,
1567 'MANAGER_SEND_LIMIT');
1568
1569 WF_ENGINE.SetItemAttrNumber(item_type,
1570 item_key,
1571 'MANAGER_APPROVAL_SEND_COUNT',
1572 l_count + 1);
1573
1574 IF (l_count+1 >= l_limit) THEN
1575 WF_ENGINE.SetItemAttrNumber(item_type,
1576 item_key,
1577 'MANAGER_APPROVAL_SEND_COUNT',
1578 0);
1579 result := 'COMPLETE:Y';
1580 ELSE
1581 result := 'COMPLETE:N';
1582 END IF;
1583
1584 ELSIF (funcmode = 'CANCEL') THEN
1585 NULL;
1586 END IF;
1587
1588 EXCEPTION
1589 WHEN OTHERS THEN
1590 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'notifyprep_noaprvresp',
1591 item_type, item_key, to_char(actid), funcmode);
1592 raise;
1593 END notifyprep_noaprvresp;
1594
1595
1596 -- ****************************************************************************
1597 -- get_approver_manager
1598 -- ****************************************************************************
1599
1600 PROCEDURE get_approver_manager(item_type IN VARCHAR2,
1601 item_key IN VARCHAR2,
1602 actid IN NUMBER,
1603 funcmode IN VARCHAR2,
1604 result OUT NOCOPY VARCHAR2) IS
1605 l_approver_id NUMBER;
1606 l_manager_id NUMBER;
1607 BEGIN
1608
1609 IF ( funcmode = 'RUN' ) THEN
1610 l_approver_id := WF_ENGINE.GetItemAttrNumber( item_type,
1611 item_key,
1612 'APPROVER_ID' );
1613
1614 getmanager(l_approver_id, l_manager_id);
1615
1616 IF (l_manager_id IS NULL) THEN
1617 result := 'COMPLETE:FAIL';
1618 ELSE
1619 setpersonas(l_manager_id,
1620 item_type,
1621 item_key,
1622 'APPROVER');
1623 result := 'COMPLETE:PASS';
1624 END IF;
1625
1626 ELSIF ( funcmode = 'CANCEL' ) THEN
1627 NULL;
1628 END IF;
1629
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'get_approver_manager',
1633 item_type, item_key, to_char(actid), funcmode);
1634 raise;
1635 END get_approver_manager;
1636
1637
1638 -- ****************************************************************************
1639 -- record_forward_from_info
1640 -- ****************************************************************************
1641
1642 PROCEDURE record_forward_from_info(item_type IN VARCHAR2,
1643 item_key IN VARCHAR2,
1644 actid IN NUMBER,
1645 funcmode IN VARCHAR2,
1646 result OUT NOCOPY VARCHAR2) IS
1647 l_approver_id NUMBER;
1648 l_approver_name VARCHAR2(240);
1649 l_approver_display_name VARCHAR2(240);
1650 l_approver_comment VARCHAR2(2000);
1651 BEGIN
1652 IF (funcmode = 'RUN') THEN
1653 l_approver_id := WF_ENGINE.GetItemAttrNumber(item_type,
1654 item_key,
1655 'APPROVER_ID');
1656
1657 l_approver_name := WF_ENGINE.GetItemAttrText(item_type,
1658 item_key,
1659 'APPROVER_NAME');
1660
1661 l_approver_display_name := WF_ENGINE.GetItemAttrText(
1662 item_type,
1663 item_key,
1664 'APPROVER_DISPLAY_NAME');
1665
1666 l_approver_comment := WF_ENGINE.GetItemAttrText(item_type,
1667 item_key,
1668 'APPROVER_COMMENT');
1669
1670 WF_ENGINE.SetItemAttrNumber(item_type,
1671 item_key,
1672 'FORWARD_FROM_ID',
1673 l_approver_id);
1674
1675 WF_ENGINE.SetItemAttrText(item_type,
1676 item_key,
1677 'FORWARD_FROM_NAME',
1678 l_approver_name);
1679
1680 WF_ENGINE.SetItemAttrText(item_type,
1681 item_key,
1682 'FORWARD_FROM_DISPLAY_NAME',
1683 l_approver_display_name);
1684
1685 -- bugfix 2926418
1686 WF_ENGINE.SetItemAttrText(item_type,
1687 item_key,
1688 'FORWARD_APPROVER_COMMENT',
1689 l_approver_comment);
1690
1691 ELSIF (funcmode = 'CANCEL') THEN
1692 NULL;
1693 END IF;
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'record_forward_from_info',
1698 item_type, item_key, to_char(actid), funcmode);
1699 raise;
1700 END record_forward_from_info;
1701
1702
1703 -- ****************************************************************************
1704 -- verify_authority
1705 -- ****************************************************************************
1706
1707 PROCEDURE verify_authority(itemtype IN VARCHAR2,
1708 itemkey IN VARCHAR2,
1709 actid IN NUMBER,
1710 funcmode IN VARCHAR2,
1711 result OUT NOCOPY VARCHAR2) IS
1712 l_approver_id NUMBER;
1713 l_batch_id NUMBER;
1714 BEGIN
1715 IF ( funcmode = 'RUN' ) THEN
1716
1717 diagn_msg('Executing Verify_Authority');
1718 l_approver_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1719 itemkey => itemkey,
1720 aname => 'APPROVER_ID' );
1721 l_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1722 itemkey => itemkey,
1723 aname => 'BATCH_ID' );
1724
1725 IF (check_authorization_limit(l_approver_id, l_batch_id)) THEN
1726 result := 'COMPLETE:PASS';
1727 ELSE
1728 result := 'COMPLETE:FAIL';
1729 END IF;
1730
1731 ELSIF ( funcmode = 'CANCEL' ) THEN
1732 NULL;
1733 END IF;
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'verify_authority',
1738 itemtype, itemkey, to_char(actid), funcmode);
1739 raise;
1740 END verify_authority;
1741
1742
1743 -- ****************************************************************************
1744 -- Abort_Process
1745 -- ****************************************************************************
1746
1747 PROCEDURE abort_process(itemtype IN VARCHAR2,
1748 itemkey IN VARCHAR2,
1749 actid IN NUMBER,
1750 funcmode IN VARCHAR2,
1751 result OUT NOCOPY VARCHAR2) IS
1752 l_je_batch_id NUMBER;
1753 BEGIN
1754 IF (funcmode = 'RUN') THEN
1755 -- reset approval status of JE batch back to 'Required'
1756 l_je_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1757 itemkey => itemkey,
1758 aname => 'BATCH_ID');
1759
1760 UPDATE GL_JE_BATCHES
1761 SET approval_status_code = 'R'
1762 WHERE je_batch_id = l_je_batch_id;
1763
1764 -- then abort the process
1765 WF_ENGINE.AbortProcess(itemtype, itemkey, '', WF_ENGINE.eng_timeout);
1766
1767 ELSIF (funcmode = 'CANCEL') THEN
1768 null;
1769 END IF;
1770
1771 EXCEPTION
1772 WHEN OTHERS THEN
1773 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'abort_process',
1774 itemtype, itemkey, to_char(actid), funcmode);
1775 raise;
1776 END abort_process;
1777
1778
1779 END GL_WF_JE_APPROVAL_PKG;