1 PACKAGE BODY AP_WEB_AME_PKG AS
2 /* $Header: apwxameb.pls 120.11 2008/03/18 04:38:22 sodash ship $ */
3
4 -- Constants
5 C_HEADER VARCHAR2(50) := 'header';
6 C_LINE_ITEM VARCHAR2(50) := 'line item';
7 C_PROJECT VARCHAR2(50) := 'project';
8 C_AWARD VARCHAR2(50) := 'award';
9 C_COST_CENTER VARCHAR2(50) := 'cost center';
10
11
12 /*----------------------------------------------------------------------------*
13 | Procedure
14 | getViolationTotal
15 |
16 | DESCRIPTION
17 | Get total violation amount.
18 | This function will be for the usage of AME attribute
19 | Policy Violations Total
20 |
21 | PARAMETERS
22 | p_report_header_id - Report Header ID
23 |
24 | RETURNS
25 | Violation Total - Total Violation Amount.
26 *----------------------------------------------------------------------------*/
27 -------------------------------------------------------------------------
28 FUNCTION getViolationTotal(p_report_header_id IN VARCHAR2) RETURN NUMBER IS
29 -------------------------------------------------------------------------
30 l_debug_info VARCHAR2(200);
31 l_violation_total NUMBER;
32 l_extra_amount NUMBER;
33
34 BEGIN
35 -----------------------------------------------------
36 l_debug_info := 'Start getViolationTotal';
37 -----------------------------------------------------
38 select sum(exceeded_amount)
39 into l_violation_total
40 from ap_pol_violations_all
41 where report_header_id = p_report_header_id;
42
43 -----------------------------------------------------
44 l_debug_info := 'Get extra amount';
45 -----------------------------------------------------
46
47 /* If meals schedules are used with the Both option (both daily and
48 individual checks), only rule that results in the higher policy
49 violation should be used in the summation to prevent counting
50 these violations twice.
51 */
52
53 select nvl(sum(least(viol1.exceeded_amount, viol2.exceeded_amount)), 0)
54 into l_extra_amount
55 from ap_pol_violations_all viol1, ap_pol_violations_all viol2
56 where viol1.report_header_id = viol2.report_header_id
57 and viol1.distribution_line_number = viol2.distribution_line_number
58 and viol1.violation_type = 'DAILY_LIMIT'
59 and viol2.violation_type = 'DAILY_SUM_LIMIT'
60 and viol1.report_header_id = p_report_header_id;
61
62 l_violation_total := l_violation_total - l_extra_amount;
63
64 return l_violation_total;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 return 0;
69 END getViolationTotal;
70
71 /*----------------------------------------------------------------------------*
72 | Procedure
73 | getAwardManagerID
74 |
75 | DESCRIPTION
76 | Get Award Manager ID base on an Award ID
77 | If couldn't find an active award manager, null will be returned and
78 | the report will go to default manager for approval.
79 |
80 | PARAMETERS
81 | p_award_id - Award ID
82 |
83 | RETURNS
84 | Award Manager ID - Employee id of the Award manager.
85 *----------------------------------------------------------------------------*/
86 FUNCTION getAwardManagerID(p_award_id IN NUMBER,
87 p_as_of_date IN DATE) RETURN NUMBER
88 IS
89 l_debug_info VARCHAR2(200);
90 l_award_manager_id NUMBER;
91 BEGIN
92 -----------------------------------------------------
93 l_debug_info := 'start getAwardManagerID';
94 -----------------------------------------------------
95
96 select gp.person_id
97 into l_award_manager_id
98 from gms_personnel gp,
99 per_assignments_f pa,
100 per_assignment_status_types past
101 where gp.award_id = p_award_id
102 and gp.award_role = C_AWARD_MANAGER_ROLE
103 AND gp.start_date_active = ( select max(gp2.start_date_active)
104 from gms_personnel gp2
105 where gp2.award_role = C_AWARD_MANAGER_ROLE
106 and gp2.award_id = p_award_id
107 and gp2.start_date_active <= trunc(sysdate)
108 )
109 AND gp.person_id = pa.person_id
110 AND pa.primary_flag='Y'
111 AND trunc(sysdate) between pa.effective_start_date and
112 nvl(pa.effective_end_date, trunc(sysdate))
113 AND pa.assignment_status_type_id= past.assignment_status_type_id
114 AND past.per_system_status = C_ACTIVE_STATUS
115 AND pa.assignment_type in ('E', 'C')
116 AND rownum =1;
117
118 return l_award_manager_id;
119
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 RETURN NULL;
123
124 WHEN OTHERS THEN
125 IF (SQLCODE <> -20001) THEN
126 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
127 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
128 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getAwardManagerID');
129 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
130 END IF;
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END getAwardManagerID;
133
134 /*----------------------------------------------------------------------------*
135 | Procedure
136 | getProjectManagerID
137 |
138 | DESCRIPTION
139 | Get Project Manager ID based on a Project ID
140 | If couldn't find an active project manager, null will be returned and
141 | the report will go to default manager for approval.
142 |
143 | PARAMETERS
144 | p_project_id - Project ID
145 |
146 | RETURNS
147 | Project Manager ID - Employee id of Project manager.
148 *----------------------------------------------------------------------------*/
149 FUNCTION getProjectManagerID(p_project_id IN NUMBER,
150 p_as_of_date IN DATE) RETURN NUMBER
151 IS
152 l_debug_info VARCHAR2(200);
153 l_project_manager_id NUMBER;
154 BEGIN
155 -----------------------------------------------------
156 l_debug_info := 'start getProjectManagerID';
157 -----------------------------------------------------
158
159 select ppp.person_id
160 into l_project_manager_id
161 from pa_project_players ppp,
162 per_assignments_f pa,
163 per_assignment_status_types past
164 where ppp.project_id = p_project_id
165 and ppp.project_role_type = C_PROJECT_MANAGER_ROLE_TYPE
166 AND ppp.start_date_active = ( select max(pp2.start_date_active)
167 from pa_project_players pp2
168 where pp2.project_role_type = C_PROJECT_MANAGER_ROLE_TYPE
169 and pp2.project_id = p_project_id
170 and pp2.start_date_active <= trunc(sysdate)
171 )
172 AND ppp.person_id = pa.person_id
173 AND pa.primary_flag='Y'
174 AND trunc(sysdate) between pa.effective_start_date and
175 nvl(pa.effective_end_date, trunc(sysdate))
176 AND pa.assignment_status_type_id= past.assignment_status_type_id
177 AND past.per_system_status = C_ACTIVE_STATUS
178 AND pa.assignment_type in ('E', 'C')
179 AND rownum =1;
180
181 return l_project_manager_id;
182
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 RETURN NULL;
186
187 WHEN OTHERS THEN
188 IF (SQLCODE <> -20001) THEN
189 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
190 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
191 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getProjectManagerID');
192 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
193 END IF;
194 APP_EXCEPTION.RAISE_EXCEPTION;
195 END getProjectManagerID;
196
197 /*----------------------------------------------------------------------------*
198 | Procedure
199 | getTotalPerCostCenter
200 |
201 | DESCRIPTION
202 | Get total amount for a cost center.
203 | This function is used for AME attribute TOTAL_PER_COST_CENTER
204 |
205 | PARAMETERS
206 | p_report_header_id - Report Header ID
207 | p_line_number - Distribution Line Number
208 |
209 | RETURNS
210 | Total Reimbursable Amount
211 *----------------------------------------------------------------------------*/
212 -------------------------------------------------------------------------
213 FUNCTION getTotalPerCostCenter(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
214 p_line_number IN AP_EXPENSE_REPORT_LINES.distribution_line_number%TYPE) RETURN NUMBER
215 -------------------------------------------------------------------------
216 IS
217 l_line_item_total NUMBER := 0;
218
219 BEGIN
220
221 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'start getTotalPerCostCenter');
222
223 -- If project is enabled, then get the total reimbursable amount for the same project number. Otherwise, if line-level-accounting is enabled, get the total reimbursable amount for the same cost center
224
225 SELECT SUM(AMOUNT)
226 INTO l_line_item_total
227 FROM AP_EXPENSE_REPORT_LINES_ALL
228 WHERE REPORT_HEADER_ID = p_report_header_id
229 AND FLEX_CONCATENATED = (
230 SELECT FLEX_CONCATENATED
231 FROM AP_EXPENSE_REPORT_LINES_ALL
232 WHERE REPORT_HEADER_ID = p_report_header_id
233 AND DISTRIBUTION_LINE_NUMBER = p_line_number
234 )
235 AND itemization_parent_id <> -1;
236
237 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'end getTotalPerCostCenter');
238 return l_line_item_total;
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 RETURN 0;
243 WHEN OTHERS THEN
244 RETURN 0;
245 END getTotalPerCostCenter;
246
247 /*----------------------------------------------------------------------------*
248 | Procedure
249 | getCostCenterOwner
250 |
251 | DESCRIPTION
252 | Get total amount for a cost center.
253 | This function is used for AME attribute TOTAL_PER_COST_CENTER
254 |
255 | PARAMETERS
256 | p_report_header_id - Expense Report Header ID
257 | p_cost_center - Cost Center
258 |
259 | RETURNS
260 | Total Reimbursable Amount
261 *----------------------------------------------------------------------------*/
262 -------------------------------------------------------------------------
263 FUNCTION getCostCenterOwner(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
264 p_cost_center IN AP_EXPENSE_REPORT_LINES.FLEX_CONCATENATED%TYPE) RETURN VARCHAR2
265 -------------------------------------------------------------------------
266 IS
267 l_debug_info VARCHAR2(200);
268 l_segment_name FND_SEGMENT_ATTRIBUTE_VALUES.application_column_name%TYPE := NULL;
269 l_cc_owner_id HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%TYPE := NULL;
270 l_rows_processed NUMBER := 0;
271 l_cur_hdl INTEGER;
272 l_query_stmt VARCHAR2(4000);
273 l_errorText VARCHAR2(200);
274 l_char_of_accounts_id GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
275
276 BEGIN
277
278 -----------------------------------------------------
279 l_debug_info := 'Get the Column Name which implements the Cost Center Segment';
280 -----------------------------------------------------
281 l_segment_name := AP_WEB_OA_REPORTING_UTIL.GetCostCenterSegmentName;
282
283 IF (l_segment_name is not null) THEN
284
285 -----------------------------------------------------
286 l_debug_info := 'get char of accounts id';
287 -----------------------------------------------------
288 SELECT GS.chart_of_accounts_id
289 INTO l_char_of_accounts_id
290 FROM ap_system_parameters_all S,
291 gl_sets_of_books GS,
292 ap_expense_report_headers_all erh
293 WHERE GS.set_of_books_id = S.set_of_books_id
294 AND S.org_id = erh.org_id
295 AND erh.report_header_id = p_report_header_id;
296
297 -----------------------------------------------------
298 l_debug_info := 'open cursor';
299 -----------------------------------------------------
300 l_cur_hdl := dbms_sql.open_cursor;
301
302 -----------------------------------------------------
303 l_debug_info := 'set query statement';
304 -----------------------------------------------------
305 -- 3176205: This query will only include current employees
306 -- and contingent workers, not terminated ones.
307 l_query_stmt := 'SELECT distinct HOIP.ORG_INFORMATION2 OWNER_ID
308 FROM GL_CODE_COMBINATIONS GLCC,
309 HR_ORGANIZATION_INFORMATION HOIP,
310 HR_ORGANIZATION_INFORMATION HOIC,
311 HR_ORGANIZATION_INFORMATION HOI,
312 PER_WORKFORCE_CURRENT_X PP,
313 AP_EXPENSE_REPORT_LINES_ALL LINES
314 WHERE ENABLED_FLAG = ''Y''
315 AND GLCC.' || l_segment_name || ' = :costCenter
316 AND LINES.REPORT_HEADER_ID = :reportHeaderId
317 AND CHART_OF_ACCOUNTS_ID = :charOfAccountsId
318 AND COMPANY_COST_CENTER_ORG_ID IS NOT NULL
319 AND HOI.ORG_INFORMATION_CONTEXT = ''CLASS''
320 AND HOI.ORG_INFORMATION1 = ''CC''
321 AND HOIC.ORGANIZATION_ID = HOI.ORGANIZATION_ID
322 AND UPPER(HOIC.ORG_INFORMATION_CONTEXT) = ''COMPANY COST CENTER''
323 AND GLCC.COMPANY_COST_CENTER_ORG_ID = HOIC.ORGANIZATION_ID
324 AND HOIC.ORGANIZATION_ID = HOIP.ORGANIZATION_ID
325 AND UPPER(HOIP.ORG_INFORMATION_CONTEXT) = ''ORGANIZATION NAME ALIAS''
326 AND PP.PERSON_ID = HOIP.ORG_INFORMATION2';
327
328 -----------------------------------------------------
329 l_debug_info := 'parse cursor';
330 -----------------------------------------------------
331 dbms_sql.parse(l_cur_hdl, l_query_stmt,dbms_sql.native);
332
333 -----------------------------------------------------
334 l_debug_info := 'bind values to the placeholder';
335 -----------------------------------------------------
336 dbms_sql.bind_variable(l_cur_hdl, ':costCenter', p_cost_center);
337 dbms_sql.bind_variable(l_cur_hdl, ':reportHeaderId', p_report_header_id);
338 dbms_sql.bind_variable(l_cur_hdl, ':charOfAccountsId', l_char_of_accounts_id);
339
340 -----------------------------------------------------
341 l_debug_info := 'setup output';
342 -----------------------------------------------------
343 dbms_sql.define_column(l_cur_hdl, 1, l_cc_owner_id, 150);
344
345 -----------------------------------------------------
346 l_debug_info := 'execute cursor';
347 -----------------------------------------------------
348 l_rows_processed := dbms_sql.execute(l_cur_hdl);
349
350 -----------------------------------------------------
351 l_debug_info := 'fetch a row';
352 -----------------------------------------------------
353 IF dbms_sql.fetch_rows(l_cur_hdl) > 0 then
354 -- fetch columns from the row
355 dbms_sql.column_value(l_cur_hdl, 1, l_cc_owner_id);
356 END IF;
357
358 -----------------------------------------------------
359 l_debug_info := 'close cursor';
360 -----------------------------------------------------
361 dbms_sql.close_cursor(l_cur_hdl);
362
363 END IF;
364
365 return l_cc_owner_id;
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 IF (SQLCODE <> -20001) THEN
370 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
371 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
372 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getCostCenterOwner');
373 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
374 END IF;
375 APP_EXCEPTION.RAISE_EXCEPTION;
376 END getCostCenterOwner;
377
378
379 /*----------------------------------------------------------------------------*
380 | Procedure
381 | getViolationPercentage
382 |
383 | DESCRIPTION
384 | Get the violation percentage of total reimbursable amount.
385 | This function will be for the usage of AME attribute
386 | Policy Violations Percentage.
387 |
388 | PARAMETERS
389 | p_report_header_id - The expense report ID.
390 |
391 | RETURNS
392 | Violation percentage
393 *----------------------------------------------------------------------------*/
394 -------------------------------------------------------------------------
395 FUNCTION getViolationPercentage(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE) RETURN NUMBER
396 -------------------------------------------------------------------------
397 IS
398 l_debug_info VARCHAR2(200);
399 l_violationTotal NUMBER := 0;
400 l_reportTotal NUMBER;
401
402 BEGIN
403
404 l_violationTotal := AP_WEB_AME_PKG.getViolationTotal(p_report_header_id);
405
406 -----------------------------------------------------
407 l_debug_info := 'If violation total less than 0 then return 0 percent violated.';
408 -----------------------------------------------------
409 IF (l_violationTotal <= 0) THEN
410 return 0;
411 END IF;
412
413 -----------------------------------------------------
414 l_debug_info := 'Get report total base on the report header id.';
415 -----------------------------------------------------
416 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetHeaderTotal(p_report_header_id, l_reportTotal)) THEN
417 return 0;
418 END IF;
419
420 -----------------------------------------------------
421 -- If violation total greater than 0 but report total less than 0,
422 -- return 100 percent violated.';
423 -----------------------------------------------------
424 IF (l_reportTotal <= 0) THEN
425 return 100;
426 -----------------------------------------------------
427 -- Or return the desired violation percentage here for
428 -- report total less than 0.
429 -----------------------------------------------------
430 END IF;
431
432 return round(100*(l_violationTotal/l_reportTotal),2);
433
434
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 RETURN 0;
438 WHEN OTHERS THEN
439 IF (SQLCODE <> -20001) THEN
440 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
441 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
442 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getViolationPercentage');
443 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
444 END IF;
445 APP_EXCEPTION.RAISE_EXCEPTION;
446 END getViolationPercentage;
447
448
449 /*----------------------------------------------------------------------------*
450 | Function
451 | isMissingReceiptsShortpay
452 |
453 | DESCRIPTION
454 | Checks if this is a Missing Receipts Shortpay
455 | This function will be for the usage of AME attribute:
456 | Missing Receipts Shortpay
457 |
458 | PARAMETERS
459 | p_report_header_id - The expense report ID.
460 |
461 | RETURNS
462 | ame_util.booleanAttributeTrue if Missing Receipts Shortpay
463 | ame_util.booleanAttributeFalse if not Missing Receipts Shortpay
464 *----------------------------------------------------------------------------*/
465 -------------------------------------------------------------------------
466 FUNCTION isMissingReceiptsShortpay(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
467 -------------------------------------------------------------------------
468 l_debug_info VARCHAR2(200);
469
470 l_is_missing_shortpay varchar2(10) := ame_util.booleanAttributeFalse;
471
472 l_apexp VARCHAR2(8) := 'APEXP';
473 l_no_receipts_shortpay_process VARCHAR2(30) := 'NO_RECEIPTS_SHORTPAY_PROCESS';
474
475 BEGIN
476
477 ------------------------------------------------------------
478 l_debug_info := 'Check if in-process Missing Receipts Shortpay';
479 ------------------------------------------------------------
480 select ame_util.booleanAttributeTrue
481 into l_is_missing_shortpay
482 from ap_expense_report_headers aerh,
483 wf_items wf
484 where aerh.report_header_id = p_report_header_id
485 and aerh.shortpay_parent_id is not null
486 and wf.item_type = l_apexp
487 and wf.Item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
488 and wf.end_date is null
489 and wf.root_activity = l_no_receipts_shortpay_process
490 and rownum = 1;
491
492 return l_is_missing_shortpay;
493
494 EXCEPTION
495 WHEN NO_DATA_FOUND THEN
496 RETURN ame_util.booleanAttributeFalse;
497 WHEN OTHERS THEN
498 IF (SQLCODE <> -20001) THEN
499 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
500 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
501 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'isMissingReceiptsShortpay');
502 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
503 END IF;
504 APP_EXCEPTION.RAISE_EXCEPTION;
505 END isMissingReceiptsShortpay;
506
507 /*
508 Returns Award number based on award_id
509 */
510 -------------------------------------------------------------------
511 FUNCTION GetAwardNumber(
512 p_award_id IN NUMBER
513 ) RETURN VARCHAR2 IS
514 -------------------------------------------------------------------
515 l_award_number GMS_AWARDS.AWARD_NUMBER%type;
516 BEGIN
517
518 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start GetAwardNumber');
519
520 select award_number
521 into l_award_number
522 from GMS_AWARDS_ALL
523 where award_id = p_award_id;
524
525 RETURN l_award_number;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetAwardNumber' );
530 APP_EXCEPTION.RAISE_EXCEPTION;
531 END GetAwardNumber;
532
533 /*
534 Calculates p_amount based on p_report_header_id, p_item_class and p_item_id
535 p_item_id -> will have report_header_id if the item_class is C_HEADER
536 -> will have distribution_line_number if the item_class is C_LINE_ITEM
537 -> will have cost center if the item_class is C_COST_CENTER
538 -> will have project_id if the item_class is C_PROJECT
539 -> will have award_id if the item_class is C_AWARD
540 */
541 -------------------------------------------------------------------------
542 PROCEDURE GetItemAmount(p_report_header_id IN NUMBER,
543 p_item_class IN VARCHAR2,
544 p_item_id IN VARCHAR2,
545 p_amount OUT NOCOPY NUMBER) IS
546 -------------------------------------------------------------------------
547 BEGIN
548
549 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start GetItemAmount');
550
551 if (p_item_class = C_HEADER) then
552 select total
553 into p_amount
554 from ap_expense_report_headers_all
555 where report_header_id = p_report_header_id;
556 elsif (p_item_class = C_LINE_ITEM) then
557 select sum(amount)
558 into p_amount
559 from ap_expense_report_lines_all
560 where report_header_id = p_report_header_id
561 and distribution_line_number = p_item_id;
562 -- and report_line_id = p_item_id;
563 elsif (p_item_class = C_PROJECT) then
564 select nvl(sum(amount),0)
565 into p_amount
566 from ap_exp_report_dists_all
567 where report_header_id = p_report_header_id
568 and project_id = p_item_id;
569 elsif (p_item_class = C_AWARD) then
570 select nvl(sum(amount),0)
571 into p_amount
572 from ap_exp_report_dists_all
573 where report_header_id = p_report_header_id
574 and award_id = p_item_id;
575 elsif (p_item_class = C_COST_CENTER) then
576 select nvl(sum(amount),0)
577 into p_amount
578 from ap_exp_report_dists_all
579 where report_header_id = p_report_header_id
580 and cost_center = p_item_id;
581 end if;
582
583 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'End GetItemAmount');
584
585 EXCEPTION
586 WHEN NO_DATA_FOUND THEN
587 null;
588
589 WHEN OTHERS THEN
590 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetItemAmount' );
591 APP_EXCEPTION.RAISE_EXCEPTION;
592 END GetItemAmount;
593
594
595 /*
596 Inserts data into table OIE_AME_NOTIF_GT
597 */
598 -------------------------------------------------------------------------
599 PROCEDURE InsertToAMENotifGT(
600 p_report_header_id IN NUMBER,
601 p_orig_system IN VARCHAR2,
602 p_orig_system_id IN NUMBER,
603 p_item_class IN VARCHAR2,
604 p_item_id IN VARCHAR2,
605 p_amount IN NUMBER,
606 p_project_number IN VARCHAR2,
607 p_project_name IN VARCHAR2,
608 p_award_number IN VARCHAR2) as
609 -------------------------------------------------------------------------
610 BEGIN
611 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start InsertToAMENotifGT');
612
613 insert into OIE_AME_NOTIF_GT(
614 report_header_id,
615 orig_system,
616 orig_system_id,
617 item_class,
618 item_id ,
619 amount,
620 project_number,
621 project_name,
622 award_number
623 )
624 values
625 (
626 p_report_header_id,
627 p_orig_system,
628 p_orig_system_id,
629 p_item_class,
630 p_item_id,
631 p_amount,
632 p_project_number,
633 p_project_name,
634 p_award_number
635 );
636 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'End InsertToAMENotifGT');
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 AP_WEB_DB_UTIL_PKG.RaiseException( 'InsertToAMENotifGT' );
641 APP_EXCEPTION.RAISE_EXCEPTION;
642 END InsertToAMENotifGT;
643
644 /*
645 Populates data into OIE_AME_NOTIF_GT and OIE_AME_APPROVER_AMT_GT.
646 data in OIE_AME_NOTIF_GT will be used for displaying instructions to
647 the approver in the approval notification.
648 like, You are responsible for approving expenses that total 250.00 USD for cost center 420.
649 data in OIE_AME_APPROVER_AMT_GT will be used for displaying Approver
650 Amount in the approval notification.
651 Calls ame_api2.getallapprovers1 to get the list of approver in the approval
652 list, which would also have info on why the approver is placed on the
653 approval list.
654 p_display_instr -> is set to No if approver is on approval list due to
655 header rules.
656 Inserts only the data that is related to the p_approver_id.
657 Data is populated into OIE_AME_APPROVER_AMT_GT by the call InitOieAmeApproverAmtGT
658 */
659 -------------------------------------------------------------------------
660 PROCEDURE InitOieAmeNotifGT( p_report_header_id IN NUMBER,
661 p_approver_id IN NUMBER,
662 p_display_instr OUT NOCOPY VARCHAR2) iS
663 -------------------------------------------------------------------------
664 l_debug_info VARCHAR2(200);
665 l_approvalProcessCompleteYNOut varchar2(10);
666 l_approversOut ame_util.approversTable2;
667 l_itemIndexesOut ame_util.idList;
668 l_itemClassesOut ame_util.stringList;
669 l_itemIdsOut ame_util.stringList;
670 l_itemSourcesOut ame_util.longStringList;
671
672 l_project_number pa_projects_all.segment1%type;
673 l_project_name pa_projects_all.name%type;
674
675 l_award_number gms_awards.award_number%type;
676
677 l_item_class ame_item_classes.name%type;
678 l_item_id ame_temp_old_approver_lists.item_id%type;
679
680 l_amount number;
681
682 BEGIN
683
684 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start InitOieAmeNotifGT');
685
686 p_display_instr := 'Y';
687
688 DELETE FROM OIE_AME_NOTIF_GT;
689
690 -----------------------------------------------------------
691 l_debug_info := 'Before call to ame_api2.getallapprovers1';
692 -----------------------------------------------------------
693 ame_api2.getallapprovers1(
694 applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
695 transactionTypeIn => 'APEXP',
696 transactionIdIn => to_char(p_report_header_id),
697 approvalProcessCompleteYNOut => l_approvalProcessCompleteYNOut,
698 approversOut => l_approversOut,
699 itemIndexesOut => l_itemIndexesOut,
700 itemClassesOut => l_itemClassesOut,
701 itemIdsOut => l_itemIdsOut,
702 itemSourcesOut => l_itemSourcesOut);
703
704 -----------------------------------------------------------
705 l_debug_info := 'After call to ame_api2.getallapprovers1';
706 -----------------------------------------------------------
707
708 FOR i IN 1 .. l_approversOut.count LOOP
709
710 if ( ((l_approversOut(i).approval_status IS NULL) OR
711 (l_approversOut(i).approval_status <> 'REPEATED'))
712 AND
713 (l_approversOut(i).orig_system_id = p_approver_id) ) then
714
715 if l_approversOut(i).item_id is null then
716
717 for j in 1 .. l_itemIndexesOut.count loop
718 if l_itemIndexesOut(j) = i then
719
720 l_item_class := l_itemClassesOut(j);
721 l_item_id := l_itemIdsOut(j);
722
723 if (l_item_class = C_HEADER) then
724 p_display_instr := 'N';
725 end if;
726
727 if (l_item_class = C_PROJECT) then
728 if not AP_WEB_DB_PA_INT_PKG.GetProjectInfo(to_number(l_item_id),
729 l_project_number,
730 l_project_name) then
731 null;
732 end if;
733 end if;
734
735 if (l_item_class = C_AWARD) then
736 l_award_number := GetAwardNumber(to_number(l_item_id));
737 end if;
738
739 -----------------------------------------------------------
740 l_debug_info := 'Before call to GetItemAmount';
741 -----------------------------------------------------------
742 GetItemAmount(p_report_header_id,
743 l_item_class,
744 l_item_id,
745 l_amount);
746
747 -----------------------------------------------------------
748 l_debug_info := 'Before call to InsertToAMENotifGT';
749 -----------------------------------------------------------
750 InsertToAMENotifGT(
751 p_report_header_id,
752 l_approversOut(i).orig_system,
753 l_approversOut(i).orig_system_id,
754 l_itemClassesOut(j),
755 l_itemIdsOut(j),
756 l_amount,
757 l_project_number,
758 l_project_name,
759 l_award_number
760 );
761 end if;
762 end loop; -- l_itemIndexesOut
763
764 else -- if l_approversOut(i).item_id is null
765
766 -- logic for l_approversOut(i).item_id is null
767 l_item_class := l_approversOut(i).item_class;
768 l_item_id := l_approversOut(i).item_id;
769
770 if (l_item_class = C_HEADER) then
771 p_display_instr := 'N';
772 end if;
773
774 if (l_item_class = C_PROJECT) then
775 if not AP_WEB_DB_PA_INT_PKG.GetProjectInfo(to_number(l_item_id),
776 l_project_number,
777 l_project_name) then
778 null;
779 end if;
780 end if;
781
782 if (l_item_class = C_AWARD) then
783 l_award_number := GetAwardNumber(to_number(l_item_id));
784 end if;
785
786 -----------------------------------------------------------
787 l_debug_info := 'Before call to GetItemAmount';
788 -----------------------------------------------------------
789 GetItemAmount(p_report_header_id,
790 l_item_class,
791 l_item_id,
792 l_amount);
793
794 -----------------------------------------------------------
795 l_debug_info := 'Before call to InsertToAMENotifGT';
796 -----------------------------------------------------------
797 InsertToAMENotifGT(
798 p_report_header_id,
799 l_approversOut(i).orig_system,
800 l_approversOut(i).orig_system_id,
801 l_approversOut(i).item_class,
802 l_approversOut(i).item_id ,
803 l_amount,
804 l_project_number,
805 l_project_name,
806 l_award_number
807 );
808 end if; -- if l_approversOut(i).item_id is null
809 end if;
810
811 END LOOP; -- l_approversOut.count
812
813 -----------------------------------------------------------
814 l_debug_info := 'Before call to InitOieAmeApproverAmtGT';
815 -----------------------------------------------------------
816 InitOieAmeApproverAmtGT(p_report_header_id);
817
818 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'end InitOieAmeNotifGT');
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 IF (SQLCODE <> -20001) THEN
823 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
824 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
825 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'InitOieAmeNotifGT');
826 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
827 END IF;
828 APP_EXCEPTION.RAISE_EXCEPTION;
829 END InitOieAmeNotifGT;
830
831 /*
832 populates the global temporary table OIE_AME_APPROVER_AMT_GT
833 with approver amount for each line.
834 If the approver is on the approver list due to line item rules
835 then approver amount is same as line amount.
836 otherwise the approver amount is calculated as sum of amount
837 in the distributions for which the approver is on the approver
838 list i.e, project or cost center or award.
839 The information why the approver is on the approver list exist
840 in oie_ame_notif_gt.
841 */
842 PROCEDURE InitOieAmeApproverAmtGT(p_report_header_id IN NUMBER) IS
843
844 -- 5213228: include only itemization parent lines in the cursor as we
845 -- display only the parent lines in the notification.
846 -- 5417790: include only itemization child lines in the cursor as we
847 -- display only the child lines in the notification.
848 cursor c1(p_report_header_id in number) is
849 select report_line_id, nvl(amount,0) amount, distribution_line_number, itemization_parent_id
850 from ap_expense_report_lines_all xl
851 where xl.report_header_id = p_report_header_id
852 and (xl.itemization_parent_id is null or xl.itemization_parent_id <> -1);
853
854 l_debug_info VARCHAR2(200);
855 l_line_approver varchar2(1);
856 l_approver_amount number;
857
858 BEGIN
859
860 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'start InitOieAmeApproverAmtGT');
861
862 DELETE FROM OIE_AME_APPROVER_AMT_GT;
863
864 for i in c1(p_report_header_id) loop
865 begin
866 -- gt.item_id -> will have distribution_line_number if the gt.item_class is C_LINE_ITEM
867 select 'Y'
868 into l_line_approver
869 from oie_ame_notif_gt gt
870 where gt.item_class = C_LINE_ITEM
871 and gt.item_id = i.distribution_line_number;
872 -- and gt.item_id = i.report_line_id;
873
874 l_approver_amount := i.amount;
875
876 exception
877 when no_data_found then
878 l_line_approver := 'N';
879 when others then
880 l_line_approver := 'N';
881 end;
882
883 if (l_line_approver = 'N') then
884 /* gt.item_id -> will have cost center if the gt.item_class is C_COST_CENTER
885 -> will have project_id if the gt.item_class is C_PROJECT
886 -> will have award_id if the gt.item_class is C_AWARD
887 */
888 if i.itemization_parent_id = -1 then
889 select nvl(sum(xd.amount),0)
890 into l_approver_amount
891 from ap_exp_report_dists_all xd,
892 oie_ame_notif_gt gt
893 where xd.report_header_id = p_report_header_id
894 and xd.report_line_id in (
895 select report_line_id
896 from ap_expense_report_lines_all
897 where report_header_id = p_report_header_id
898 and itemization_parent_id = i.report_line_id)
899 and ( (xd.cost_center = gt.item_id
900 and
901 gt.item_class = C_COST_CENTER)
902 or
903 (xd.project_id = gt.item_id
904 and
905 gt.item_class = C_PROJECT)
906 or
907 (xd.award_id = gt.item_id
908 and
909 gt.item_class = C_AWARD) );
910 else
911 select nvl(sum(xd.amount),0)
912 into l_approver_amount
913 from ap_exp_report_dists_all xd,
914 oie_ame_notif_gt gt
915 where xd.report_header_id = p_report_header_id
916 and xd.report_line_id = i.report_line_id
917 and ( (xd.cost_center = gt.item_id
918 and
919 gt.item_class = C_COST_CENTER)
920 or
921 (xd.project_id = gt.item_id
922 and
923 gt.item_class = C_PROJECT)
924 or
925 (xd.award_id = gt.item_id
926 and
927 gt.item_class = C_AWARD) );
928 end if;
929
930 if (l_approver_amount > i.amount) then
931 l_approver_amount := i.amount;
932 end if;
933
934 end if; -- (l_line_approver = 'N')
935
936
937 insert into OIE_AME_APPROVER_AMT_GT(
938 report_header_id,
939 report_line_id,
940 approver_amount)
941 values (
942 p_report_header_id,
943 i.report_line_id,
944 nvl(l_approver_amount,0));
945 end loop;
946
947 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'end InitOieAmeApproverAmtGT');
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 IF (SQLCODE <> -20001) THEN
952 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
953 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
954 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'InitOieAmeApproverAmtGT');
955 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
956 END IF;
957 APP_EXCEPTION.RAISE_EXCEPTION;
958 END InitOieAmeApproverAmtGT;
959
960 END AP_WEB_AME_PKG;
[Home] [Help]