1 PACKAGE BODY AP_WEB_CUST_AME_PKG AS
2 /* $Header: apwamecb.pls 120.7.12020000.2 2012/07/19 14:31:43 dsadipir ship $ */
3
4 /*----------------------------------------------------------------------------*
5 Function
6 checkAndGetApprover
7
8 Description
9 Added the function for fixing Bug : 4112598
10
11 PARAMETERS
12 p_approver_id - approver ID
13 p_employee_id - employee ID
14
15 RETURNS
16 Return approver ID if approver <> employee or approver is null
17 else return approver's manager ID
18 *----------------------------------------------------------------------------*/
19 FUNCTION checkAndGetApprover(p_approver_id IN AP_WEB_DB_EXPRPT_PKG.expHdr_overrideApprID,
20 p_employee_id IN NUMBER)
21 RETURN VARCHAR2 IS
22 l_debugInfo varchar2(240);
23 l_dir_manager_id NUMBER := NULL;
24 BEGIN
25 if (p_approver_id is null) then
26 return (p_approver_id);
27 end if;
28
29 if (p_approver_id <> p_employee_id) then
30 return (p_approver_id);
31 else
32 AP_WEB_EXPENSE_WF.GetManager(p_employee_id, l_dir_manager_id);
33 return to_char(l_dir_manager_id);
34 end if;
35 EXCEPTION
36 WHEN OTHERS THEN
37 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.checkAndGetApprover',
38 l_debugInfo);
39 APP_EXCEPTION.RAISE_EXCEPTION;
40 END;
41
42 /*----------------------------------------------------------------------------*
43 | Procedure
44 | getHeaderLevelApprover
45 |
46 | DESCRIPTION
47 | This function is to get the Approver ID for AME attribute
48 | Job Level Non Default Starting Point Person ID.
49 | Seeded logic for this function is as following:
50 | - If approver is entered, return the approver ID.
51 | - If only one award number is entered, get the award manager ID.
52 | - If only one project number is entered, get the project manager ID.
53 | - Return the cost center owner ID if the cost center is different
54 | than the employee's cost center. If the same, get the employee's
55 | supervisor ID.
56 | - If none of the above are entered, system should get the employee's
57 | supervisor ID.
58 |
59 | PARAMETERS
60 | p_report_header_id - The expense report ID.
61 |
62 | RETURNS
63 | Approver ID - Employee id of Header Level Approver.
64 | - Return NULL means default manager will be used to build
65 | the approver list.
66 *----------------------------------------------------------------------------*/
67 -------------------------------------------------------------------------
68 FUNCTION getHeaderLevelApprover(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE) RETURN VARCHAR2 IS
69 -------------------------------------------------------------------------
70 l_debug_info VARCHAR2(200);
71 l_approver_id AP_WEB_DB_EXPRPT_PKG.expHdr_overrideApprID;
72 l_employee_id NUMBER;
73 l_num_of_distinct_awards INTEGER;
74 l_num_of_awards INTEGER;
75 l_num_of_distinct_projects INTEGER;
76 l_num_of_projects INTEGER;
77 l_award_manager_id NUMBER;
78 l_project_manager_id NUMBER;
79 l_cost_center_owner_id NUMBER;
80 l_cost_center_manager_id NUMBER;
81 l_default_cost_center VARCHAR2(240);
82 l_cost_center VARCHAR2(240);
83 l_employee_num VARCHAR2(30);
84 l_employee_name VARCHAR2(240);
85 l_project_id NUMBER;
86 l_award_id NUMBER;
87 l_week_end_date DATE;
88 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
89 l_roundingParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
90 BEGIN
91 SELECT aeh.week_end_date, aeh.employee_id, flex_concatenated
92 INTO l_week_end_date, l_employee_id, l_cost_center
93 FROM ap_expense_report_headers_all aeh
94 WHERE report_header_id = p_report_header_id;
95
96 -----------------------------------------------------
97 l_debug_info := 'Check whether user entered an Approver';
98 -----------------------------------------------------
99 IF (NOT (AP_WEB_DB_EXPRPT_PKG.GetOverrideApproverID(to_number(p_report_header_id), l_approver_id))) THEN
100 l_approver_id := NULL;
101 END IF;
102
103 IF (l_approver_id IS NOT NULL) THEN
104 RETURN to_char(l_approver_id);
105 END IF;
106
107
108 -----------------------------------------------------
109 -- Start getting Award Manager ID based on entered Award Number.
110 -- Please comment out the following code if it's not desired to have
111 -- the Award Manager be included in the Approver list under any
112 -- circumstance.
113 -----------------------------------------------------
114
115 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
116 APP_EXCEPTION.RAISE_EXCEPTION;
117 END IF;
118
119 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetRoundingParamID(l_roundingParameterId)) THEN
120 APP_EXCEPTION.RAISE_EXCEPTION;
121 END IF;
122
123 -----------------------------------------------------
124 l_debug_info := 'Check whether there is only one Award Number entered and all lines have an Award Number';
125 -----------------------------------------------------
126 select count(distinct award_id), count(distinct nvl(award_id, -1)),
127 count(distinct project_id), count(distinct nvl(project_id, -1))
128 into l_num_of_distinct_awards, l_num_of_awards, l_num_of_distinct_projects, l_num_of_projects
129 from ap_exp_report_dists_all dist
130 where report_header_id = p_report_header_id
131 and report_line_id not in (
132 select report_line_id
133 from ap_expense_report_lines_all line
134 where line.report_header_id = dist.report_header_id
135 and web_parameter_id in (l_personalParameterId, l_roundingParameterId));
136
137 -----------------------------------------------------
138 l_debug_info := 'Try to get Award Manager ID if only one Award Number entered';
139 -----------------------------------------------------
140 IF (l_num_of_distinct_awards = 1 AND l_num_of_awards = 1) THEN
141 select distinct award_id
142 into l_award_id
143 from ap_exp_report_dists_all dist
144 where report_header_id = p_report_header_id
145 and report_line_id not in (
146 select report_line_id
147 from ap_expense_report_lines_all line
148 where line.report_header_id = dist.report_header_id
149 and web_parameter_id in (l_personalParameterId, l_roundingParameterId));
150
151 l_approver_id := AP_WEB_AME_PKG.getAwardManagerID(l_award_id, nvl(l_week_end_date,trunc(sysdate)));
152 RETURN checkAndGetApprover(l_approver_id,l_employee_id);
153 END IF;
154
155 -----------------------------------------------------
156 -- End of getting Award Manager ID.
157 -----------------------------------------------------
158
159
160 -----------------------------------------------------
161 -- Start getting Project Manager ID based on entered Project Number.
162 -- Please comment out the following code if it's not desired to include
163 -- Project Manager in the Approver list under any circumstance.
164 -----------------------------------------------------
165
166 -----------------------------------------------------
167 l_debug_info := 'Try to get Project Manager ID if only one Project Number entered';
168 -----------------------------------------------------
169 IF (l_num_of_distinct_projects = 1 AND l_num_of_projects = 1) THEN
170 select distinct project_id
171 into l_project_id
172 from ap_exp_report_dists_all dist
173 where report_header_id = p_report_header_id
174 and report_line_id not in (
175 select report_line_id
176 from ap_expense_report_lines_all line
177 where line.report_header_id = dist.report_header_id
178 and web_parameter_id in (l_personalParameterId, l_roundingParameterId));
179
180 l_approver_id := AP_WEB_AME_PKG.getProjectManagerID(l_project_id, nvl(l_week_end_date,trunc(sysdate)));
181 RETURN checkAndGetApprover(l_approver_id,l_employee_id);
182 END IF;
183 -----------------------------------------------------
184 -- End of getting Project Manager ID.
185 -----------------------------------------------------
186
187
188 -----------------------------------------------------
189 -- Start getting Cost Center Owner ID based on entered Cost Center.
190 -- Please comment out the following code if it's not desired to include
191 -- the Cost Center owner in the Approver list under any circumstance.
192 -----------------------------------------------------
193
194 -----------------------------------------------------
195 l_debug_info := 'Check whether entered Cost Center equals to Employee Default Cost Center';
196 -----------------------------------------------------
197
198 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
199 l_employee_name,
200 l_employee_num,
201 l_default_cost_center,
202 l_employee_id);
203
204 IF (l_default_cost_center = l_cost_center) THEN
205 RETURN NULL;
206 END IF;
207
208 -----------------------------------------------------
209 l_debug_info := 'Return Cost Center Business Manager or Cost Center Owner if cost center if different from default';
210 -----------------------------------------------------
211
212 l_cost_center_manager_id := getCCBusinessManager(p_report_header_id, l_cost_center);
213
214 IF (l_cost_center_manager_id IS NOT NULL) THEN
215 RETURN checkAndGetApprover(l_cost_center_manager_id,l_employee_id);
216 ELSE
217 l_cost_center_owner_id := AP_WEB_CUST_AME_PKG.getCustomCostCenterOwner(p_report_header_id, l_cost_center);
218 RETURN checkAndGetApprover(l_cost_center_owner_id,l_employee_id);
219 END IF;
220
221 -----------------------------------------------------
222 -- End of getting Cost Center Owner ID.
223 -----------------------------------------------------
224
225 -----------------------------------------------------
226 -- Return NULL if none of the above logic is enabled.
227 -----------------------------------------------------
228 RETURN NULL;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 IF (SQLCODE <> -20001) THEN
233 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
234 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
235 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getHeaderLevelApprover');
236 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 ELSE
239 -- Do not need to set the token since it has been done in the
240 -- child process
241 RAISE;
242 END IF;
243 END getHeaderLevelApprover;
244
245
246 /*----------------------------------------------------------------------------*
247 | Procedure
248 | getLineLevelApprover
249 |
250 | DESCRIPTION
251 | This function is to get the Approver ID for AME attribute
252 | Item Starting Point Person ID.
253 | Seeded logic for this function is as following:
254 | - If award number is entered, get the award manager ID.
255 | - If project number is entered, get the project manager ID.
256 | - Return the cost center owner ID if the line-level cost center
257 | is different than the employee's cost center. If the same,
258 | get the employee's supervisor ID.
259 |
260 | PARAMETERS
261 | p_report_header_id - The expense report ID.
262 | p_dist_line_number - The distribution line number of the
263 | expense line.
264 |
265 | RETURNS
266 | Approver ID - Employee id of Line-Level Approver.
267 |
268 *----------------------------------------------------------------------------*/
269 -------------------------------------------------------------------------
270 FUNCTION getLineLevelApprover(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
271 p_dist_line_number IN AP_EXPENSE_REPORT_LINES.distribution_line_number%TYPE) RETURN VARCHAR2 IS
272 -------------------------------------------------------------------------
273 l_debug_info VARCHAR2(200);
274
275 BEGIN
276
277 return null;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 IF (SQLCODE <> -20001) THEN
282 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
283 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
284 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getLineLevelApprover');
285 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 ELSE
288 -- Do not need to set the token since it has been done in the
289 -- child process
290 RAISE;
291 END IF;
292 END getLineLevelApprover;
293
294
295
296
297 /*----------------------------------------------------------------------------*
298 | Procedure
299 | getCustomCostCenterOwner
300 |
301 | DESCRIPTION
302 | This procedure provides a way to get Cost Center owner bases on
303 | report header ID and cost center.
304 | This function will be used for AME attribute
305 | Line Item Starting Point Person ID.
306 |
307 | PARAMETERS
308 | p_report_header_id - The expense report id of the report.
309 | p_cost_center - The cost center entered by the user.
310 |
311 | RETURNS
312 | Cost Center Owner ID - Employee id of Cost Center owner.
313 | - Return null from this function means the associated
314 | AME attributes will have null value at run-time.
315 | The corresponding AME rules won't take affect.
316 *----------------------------------------------------------------------------*/
317 -------------------------------------------------------------------------
318 FUNCTION getCustomCostCenterOwner(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
319 p_cost_center IN AP_EXPENSE_REPORT_LINES.FLEX_CONCATENATED%TYPE) RETURN VARCHAR2
320 -------------------------------------------------------------------------
321 IS
322 l_debug_info VARCHAR2(200);
323
324 BEGIN
325
326 --
327 -- Example: If cost center 999 doesn't have a cost center owner and it's
328 -- used to later allocate the charge to the appropriate cost centers.
329 -- All charges to this cost center will be approved by the employee's
330 -- supervisor.
331 --
332 -- IF p_cost_center = '999' THEN
333 -- RETURN NULL;
334 -- END IF;
335 --
336
337 RETURN AP_WEB_AME_PKG.getCostCenterOwner(p_report_header_id, p_cost_center);
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 IF (SQLCODE <> -20001) THEN
342 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
343 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
344 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getCustomCostCenterOwner');
345 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
346 APP_EXCEPTION.RAISE_EXCEPTION;
347 ELSE
348 -- Do not need to set the token since it has been done in the
349 -- child process
350 RAISE;
351 END IF;
352 END getCustomCostCenterOwner;
353
354 /*----------------------------------------------------------------------------*
355 | Procedure
356 | getCCBusinessManager
357 |
358 | DESCRIPTION
359 | This function is to get the cost center business manager's ID.
360 |
361 | PARAMETERS
362 | p_report_header_id - The expense report ID.
363 | p_cost_center - The cost center entered by the user.
364 |
365 | RETURNS
366 | Cost Center Business Manager ID - Employee ID of Cost Center Business Owner.
367 *----------------------------------------------------------------------------*/
368 -------------------------------------------------------------------------
369 FUNCTION getCCBusinessManager(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
370 p_cost_center IN AP_EXPENSE_REPORT_LINES.FLEX_CONCATENATED%TYPE) RETURN VARCHAR2
371 -------------------------------------------------------------------------
372 IS
373 l_debug_info VARCHAR2(200);
374 l_cc_business_mgr_id HR_ORGANIZATION_INFORMATION.ATTRIBUTE2%TYPE := NULL;
375 l_segment_name FND_SEGMENT_ATTRIBUTE_VALUES.application_column_name%TYPE := NULL;
376 l_rows_processed NUMBER := 0;
377 l_cur_hdl INTEGER;
378 l_query_stmt VARCHAR2(4000);
379 l_char_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
380
381 BEGIN
382 -----------------------------------------------------
383 l_debug_info := 'open the cursor for processing';
384 -----------------------------------------------------
385 l_cur_hdl := dbms_sql.open_cursor;
386
387 -----------------------------------------------------
388 l_debug_info := 'Get the Column Name which implements the Cost Center Segment';
389 -----------------------------------------------------
390 l_segment_name := AP_WEB_OA_REPORTING_UTIL.GetCostCenterSegmentName;
391
392 IF (l_segment_name is not null) THEN
393
394 -----------------------------------------------------
395 l_debug_info := 'get char of accounts id';
396 -----------------------------------------------------
397 SELECT GS.chart_of_accounts_id
398 INTO l_char_of_accounts_id
399 FROM ap_system_parameters_all S,
400 gl_sets_of_books GS,
401 ap_expense_report_headers_all erh
402 WHERE GS.set_of_books_id = S.set_of_books_id
403 AND S.org_id = erh.org_id
404 AND erh.report_header_id = p_report_header_id;
405
406 -----------------------------------------------------
407 l_debug_info := 'set query statement';
408 -----------------------------------------------------
409 -- This query will only include current employees
410 -- and contingent workers, not terminated ones.
411
412 l_query_stmt := 'SELECT DISTINCT HOIP.ATTRIBUTE2 business_manager_id
413 FROM GL_CODE_COMBINATIONS GLCC,
414 HR_ORGANIZATION_INFORMATION HOIP,
415 HR_ORGANIZATION_INFORMATION HOIC,
416 HR_ORGANIZATION_INFORMATION HOI,
417 PER_WORKFORCE_CURRENT_X PP
418 WHERE ENABLED_FLAG = ''Y''
419 AND GLCC.' || l_segment_name || ' = :costCenter
420 AND CHART_OF_ACCOUNTS_ID = :charOfAccountsId
421 AND COMPANY_COST_CENTER_ORG_ID IS NOT NULL
422 AND HOI.ORG_INFORMATION_CONTEXT = ''CLASS''
423 AND HOI.ORG_INFORMATION1 = ''CC''
424 AND HOIC.ORGANIZATION_ID = HOI.ORGANIZATION_ID
425 AND UPPER(HOIC.ORG_INFORMATION_CONTEXT) = ''COMPANY COST CENTER''
426 AND GLCC.COMPANY_COST_CENTER_ORG_ID = HOIC.ORGANIZATION_ID
427 AND HOIC.ORGANIZATION_ID = HOIP.ORGANIZATION_ID
428 AND UPPER(HOIP.ORG_INFORMATION_CONTEXT) = ''ORGANIZATION NAME ALIAS''
429 AND PP.PERSON_ID = HOIP.ATTRIBUTE2';
430
431 -----------------------------------------------------
432 l_debug_info := 'parse cursor';
433 -----------------------------------------------------
434 dbms_sql.parse(l_cur_hdl, l_query_stmt,dbms_sql.native);
435
436 -----------------------------------------------------
437 l_debug_info := 'bind values to the placeholder';
438 -----------------------------------------------------
439 dbms_sql.bind_variable(l_cur_hdl, ':costCenter', p_cost_center);
440 dbms_sql.bind_variable(l_cur_hdl, ':charOfAccountsId', l_char_of_accounts_id);
441
442 -----------------------------------------------------
443 l_debug_info := 'setup output';
444 -----------------------------------------------------
445 dbms_sql.define_column(l_cur_hdl, 1, l_cc_business_mgr_id, 150);
446
447 -----------------------------------------------------
448 l_debug_info := 'execute cursor';
449 -----------------------------------------------------
450 l_rows_processed := dbms_sql.execute(l_cur_hdl);
451
452 -----------------------------------------------------
453 l_debug_info := 'fetch a row';
454 -----------------------------------------------------
455 IF dbms_sql.fetch_rows(l_cur_hdl) > 0 then
456 -- fetch columns from the row
457 dbms_sql.column_value(l_cur_hdl, 1, l_cc_business_mgr_id);
458 END IF;
459
460 -----------------------------------------------------
461 l_debug_info := 'close cursor';
462 -----------------------------------------------------
463 dbms_sql.close_cursor(l_cur_hdl);
464
465
466 END IF;
467
468 return l_cc_business_mgr_id;
469
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472 RETURN NULL;
473 WHEN OTHERS THEN
474 IF (SQLCODE <> -20001) THEN
475 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
476 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
477 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getCCBusinessManager');
478 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
479 APP_EXCEPTION.RAISE_EXCEPTION;
480 ELSE
481 -- Do not need to set the token since it has been done in the
482 -- child process
483 RAISE;
484 END IF;
485 END getCCBusinessManager;
486
487 /*----------------------------------------------------------------------------*
488 | Procedure
489 | getCostCenterApprover
490 |
491 | DESCRIPTION
492 | This function is to get the Approver ID for AME post-chain-of-authority
493 | approvals
494 | Seeded logic for this function is as following:
495 | - If default cost center is not changed and the entered approver is
496 | not equal to employee's supervisor and the approver does not belong
497 | to employee's cost center then return the cost center owner's ID.
498 | - Else if default cost center is not changed then return NULL.
499 | - If default cost center is changed and the entered approver is not
500 | equal to employee's supervisor and the approver's cost center is
501 | the same as the entered cost center then return Null.
502 | - Else if default cost center is changed then return the cost center
503 | owner's ID.
504 |
505 | PARAMETERS
506 | p_report_header_id - The expense report ID.
507 |
508 | RETURNS
509 | Approver ID - Cost center owner ID.
510 | - Return NULL means no post-chain-of-authority approval
511 | is required.
512 *----------------------------------------------------------------------------*/
513 -------------------------------------------------------------------------
514 FUNCTION getCostCenterApprover(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE) RETURN VARCHAR2 IS
515 -------------------------------------------------------------------------
516 l_debug_info VARCHAR2(200);
517 l_approver_id AP_WEB_DB_EXPRPT_PKG.expHdr_overrideApprID;
518 l_employee_id NUMBER;
519 l_cost_center_approver_id NUMBER;
520 l_default_cost_center VARCHAR2(240);
521 l_approver_default_cost_center VARCHAR2(240);
522 l_cost_center VARCHAR2(240);
523 l_employee_num VARCHAR2(30);
524 l_employee_name VARCHAR2(240);
525 l_manager_id AP_WEB_DB_HR_INT_PKG.perEmp_supervisorID;
526
527 BEGIN
528
529 -----------------------------------------------------
530 -- Start getting Cost Center Owner ID based on entered Cost Center.
531 -- Please comment out the following code if it's not desired to include
532 -- the Cost Center owner in the Approver list under any circumstance.
533 -----------------------------------------------------
534
535 -----------------------------------------------------
536 l_debug_info := 'Check whether entered Cost Center equals to Employee Default Cost Center';
537 -----------------------------------------------------
538 SELECT employee_id, flex_concatenated
539 INTO l_employee_id, l_cost_center
540 FROM AP_EXPENSE_REPORT_HEADERS_ALL
541 WHERE report_header_id = p_report_header_id;
542
543 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
544 l_employee_name,
545 l_employee_num,
546 l_default_cost_center,
547 l_employee_id);
548
549 l_cost_center_approver_id := getCCBusinessManager(p_report_header_id, l_cost_center);
550
551 IF (l_cost_center_approver_id IS NULL) THEN
552 l_cost_center_approver_id := AP_WEB_CUST_AME_PKG.getCustomCostCenterOwner(p_report_header_id, l_cost_center);
553 END IF;
554
555 IF (l_default_cost_center = l_cost_center) THEN
556 -----------------------------------------------------
557 l_debug_info := 'Check whether user entered an Approver';
558 -----------------------------------------------------
559 IF (NOT (AP_WEB_DB_EXPRPT_PKG.GetOverrideApproverID(to_number(p_report_header_id), l_approver_id))) THEN
560 l_approver_id := NULL;
561 END IF;
562
563 IF (l_approver_id IS NOT NULL) THEN
564 -----------------------------------------------------
565 l_debug_info := 'Check whether the entered Approvers cost center equals to employees default cost center';
566 -----------------------------------------------------
567 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
568 l_employee_name,
569 l_employee_num,
570 l_approver_default_cost_center,
571 l_approver_id);
572 IF (l_default_cost_center <> l_approver_default_cost_center) THEN
573 RETURN checkAndGetApprover(l_cost_center_approver_id,l_employee_id);
574 ELSE
575 RETURN NULL;
576 END IF;
577 ELSE
578 RETURN NULL;
579 END IF;
580 ELSE
581 -----------------------------------------------------
582 l_debug_info := 'Check whether user entered an Approver';
583 -----------------------------------------------------
584 IF (NOT (AP_WEB_DB_EXPRPT_PKG.GetOverrideApproverID(to_number(p_report_header_id), l_approver_id))) THEN
585 l_approver_id := NULL;
586 END IF;
587
588 IF (l_approver_id IS NOT NULL) THEN
589 -----------------------------------------------------
590 l_debug_info := 'Check whether the entered Approvers cost center equals to employees default cost center';
591 -----------------------------------------------------
592 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(
593 l_employee_name,
594 l_employee_num,
595 l_approver_default_cost_center,
596 l_approver_id);
597
598 IF (l_default_cost_center <> l_approver_default_cost_center) THEN
599 RETURN checkAndGetApprover(l_cost_center_approver_id,l_employee_id);
600 ELSE
601 RETURN NULL;
602 END IF;
603 ELSE
604 RETURN checkAndGetApprover(l_cost_center_approver_id,l_employee_id);
605 END IF;
606 END IF;
607
608 -----------------------------------------------------
609 -- Return NULL if none of the above logic is enabled.
610 -----------------------------------------------------
611 RETURN NULL;
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 IF (SQLCODE <> -20001) THEN
616 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
617 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
618 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getCostCenterApprover');
619 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
620 APP_EXCEPTION.RAISE_EXCEPTION;
621 ELSE
622 -- Do not need to set the token since it has been done in the
623 -- child process
624 RAISE;
625 END IF;
626 END getCostCenterApprover;
627
628 /*----------------------------------------------------------------------------*
629 Function
630 getTransactionRequestor
631
632 Description
633 Added the function for fixing Bug : 4387063
634 called from AME attribute TRANSACTION_REQUESTOR_PERSON_ID
635
636 PARAMETERS
637 p_report_header_id - report_header_id
638
639 RETURNS
640 Return employee_id if the employee is Active i.e, Not suspended/terminated
641 Return employee's manager if the employee is inactive
642 *----------------------------------------------------------------------------*/
643 FUNCTION getTransactionRequestor(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE) RETURN NUMBER
644 IS
645 -------------------------------------------------------------------------
646 l_debug_info VARCHAR2(200);
647 l_approver_id AP_WEB_DB_EXPRPT_PKG.expHdr_overrideApprID;
648 l_employee_id NUMBER;
649 l_emp_active VARCHAR2(1);
650
651 BEGIN
652
653 select employee_id into l_employee_id
654 from ap_expense_report_headers_all
655 where report_header_id = p_report_header_id;
656
657 --------------------------------------------------------------
658 l_debug_info := 'Calling AP_WEB_DB_HR_INT_PKG.IsPersonActive';
659 --------------------------------------------------------------
660 l_emp_active := AP_WEB_DB_HR_INT_PKG.IsPersonActive(l_employee_id);
661 if (l_emp_active = 'Y') then
662 return l_employee_id;
663 else
664 --------------------------------------------------------------
665 l_debug_info := 'Calling AP_WEB_EXPENSE_WF.GetManager';
666 --------------------------------------------------------------
667 return (AP_WEB_DB_HR_INT_PKG.getFinalActiveManager(l_employee_id));
668 end if;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getTransactionRequestor',
673 l_debug_info);
674 APP_EXCEPTION.RAISE_EXCEPTION;
675 END getTransactionRequestor;
676
677 -------------------------------------------------------------------------
678 FUNCTION getProjectApprover(p_project_id IN VARCHAR2) RETURN NUMBER IS
679 -------------------------------------------------------------------------
680 l_debug_info VARCHAR2(200);
681
682 BEGIN
683
684 return AP_WEB_AME_PKG.getProjectManagerID(to_number(p_project_id), trunc(sysdate));
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getProjectApprover',
689 l_debug_info);
690 APP_EXCEPTION.RAISE_EXCEPTION;
691 END getProjectApprover;
692
693 -------------------------------------------------------------------------
694 FUNCTION getAwardApprover(p_award_id IN VARCHAR2) RETURN NUMBER IS
695 -------------------------------------------------------------------------
696 l_debug_info VARCHAR2(200);
697
698 BEGIN
699
700 return AP_WEB_AME_PKG.getAwardManagerID(to_number(p_award_id), trunc(sysdate));
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getAwardApprover',
705 l_debug_info);
706 APP_EXCEPTION.RAISE_EXCEPTION;
707 END getAwardApprover;
708
709 -------------------------------------------------------------------------
710 FUNCTION getDistCostCenterApprover(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
711 p_cost_center IN VARCHAR2) RETURN NUMBER IS
712 -------------------------------------------------------------------------
713 l_debug_info VARCHAR2(200);
714 l_cost_center_manager_id NUMBER;
715 l_cost_center_owner_id NUMBER;
716 BEGIN
717
718 l_cost_center_manager_id := to_number(getCCBusinessManager(p_report_header_id, p_cost_center));
719
720 IF (l_cost_center_manager_id IS NOT NULL) THEN
721 RETURN l_cost_center_manager_id;
722 ELSE
723 l_cost_center_owner_id := to_number(AP_WEB_CUST_AME_PKG.getCustomCostCenterOwner(p_report_header_id, p_cost_center));
724 RETURN l_cost_center_owner_id;
725 END IF;
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getDistCostCenterApprover',
730 l_debug_info);
731 APP_EXCEPTION.RAISE_EXCEPTION;
732 END getDistCostCenterApprover;
733
734 /*----------------------------------------------------------------------------*
735 | Procedure
736 | getJobSupervisorApprover
737 |
738 | DESCRIPTION
739 | This function is to get person ID of non-default first approver
740 | for job-level/supervisor authority approval types.
741 | This function is called from JOB_LEVEL_NON_DEFAULT_STARTING_POINT_PERSON_ID
742 | and SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID
743 | Seeded logic for this function is as following:
744 | - If default cost center is not changed and the entered approver is
745 | not equal to employee's supervisor and the approver does not belong
746 | to employee's cost center then return the cost center owner's ID.
747 | - Else if default cost center is not changed then return NULL.
748 | - If default cost center is changed and the entered approver is not
749 | equal to employee's supervisor and the approver's cost center is
750 | the same as the entered cost center then return Null.
751 | - Else if default cost center is changed then return the cost center
752 | owner's ID.
753 |
754 | PARAMETERS
755 | p_item_class - The item class (header,line item,project,award,cost center)
756 | p_report_header_id - The expense report ID.
757 | p_item_id -> will have report_header_id if the item_class is 'header'
758 | -> will have distribution_line_number if the item_class is 'line item'
759 | -> will have cost center if the item_class is 'cost center'
760 | -> will have project_id if the item_class is 'project'
761 | -> will have award_id if the item_class is 'award'
762 |
763 | RETURNS
764 | Approver ID - Override approver as value for header attribute
765 | - project manager id for project attribute
766 | - award manager id for award attribute
767 | - cost center business manager's ID if its not null else
768 | it returns Cost Center owner
769 | Note: When null is returned, value of Transaction_requestor_id is used as
770 | starting point person id.
771 *----------------------------------------------------------------------------*/
772 -------------------------------------------------------------------------
773 FUNCTION getJobSupervisorApprover(p_item_class IN VARCHAR2,
774 p_report_header_id IN NUMBER,
775 p_item_id IN VARCHAR2
776 ) RETURN NUMBER IS
777 -------------------------------------------------------------------------
778 l_debug_info VARCHAR2(200);
779 l_override_approver_id NUMBER;
780 l_cost_center_manager_id NUMBER;
781 l_cost_center_owner_id NUMBER;
782 l_employee_id NUMBER;
783 l_emp_active VARCHAR2(1);
784 BEGIN
785
786 l_debug_info := 'item class: ' || p_item_class || ' item_id: ' || p_item_id;
787
788 if (p_item_class = 'header') then
789
790 select override_approver_id, employee_id
791 into l_override_approver_id, l_employee_id
792 from ap_expense_report_headers_all
793 where report_header_id = p_report_header_id;
794
795 -- Bug 14158542: Terminated employee expense reports skips the first manager for approval
796 IF l_override_approver_id IS NULL THEN
797 l_emp_active := AP_WEB_DB_HR_INT_PKG.IsPersonActive(l_employee_id);
798
799 IF (l_emp_active = 'N') THEN
800 return (AP_WEB_DB_HR_INT_PKG.getFinalActiveManager(l_employee_id));
801 END IF;
802 END IF;
803
804 return l_override_approver_id;
805
806 elsif (p_item_class = 'project') then
807 return getProjectApprover(p_item_id);
808 elsif (p_item_class = 'award') then
809 return getAwardApprover(p_item_id);
810 elsif (p_item_class = 'cost center') then
811 return getDistCostCenterApprover(p_report_header_id,p_item_id);
812 else
813 return null;
814 end if;
815
816 EXCEPTION
817 WHEN OTHERS THEN
818 AP_WEB_DB_UTIL_PKG.RaiseException('AP_WEB_CUST_AME_PKG.getJobSupervisorApprover',
819 l_debug_info);
820 APP_EXCEPTION.RAISE_EXCEPTION;
821 END getJobSupervisorApprover;
822
823 END AP_WEB_CUST_AME_PKG;
[Home] [Help]