1 PACKAGE BODY AP_WEB_AME_PKG AS
2 /* $Header: apwxameb.pls 120.13.12020000.3 2012/08/14 09:49:56 rveliche 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 in (C_ACTIVE_STATUS, C_ACTIVE_CWK_STATUS) -- Bug: 14360780, Unable to define CWK as Project Manager
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 in (C_ACTIVE_STATUS, C_ACTIVE_CWK_STATUS) -- Bug: 14360780, Unable to define CWK as Project Manager
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 (Select Organization_Id,Org_Information2 From Hr_Organization_Information Where Upper(Org_Information_Context) = ''ORGANIZATION NAME ALIAS'')HOIP,
310 (Select ORGANIZATION_ID From Hr_Organization_Information Where Upper(Org_Information_Context) = ''COMPANY COST CENTER'') HOIC,
311 (Select Organization_Id From Hr_Organization_Information Where Upper(Org_Information_Context) = ''CLASS'' And Org_Information1 = ''CC'') HOI,
312 PER_WORKFORCE_CURRENT_X PP
313 WHERE ENABLED_FLAG = ''Y''
314 AND GLCC.' || l_segment_name || ' = :costCenter
315 AND CHART_OF_ACCOUNTS_ID = :charOfAccountsId
316 AND COMPANY_COST_CENTER_ORG_ID IS NOT NULL
317 AND HOIC.ORGANIZATION_ID = HOI.ORGANIZATION_ID
318 AND GLCC.COMPANY_COST_CENTER_ORG_ID = HOIC.ORGANIZATION_ID
319 AND HOIC.ORGANIZATION_ID = HOIP.ORGANIZATION_ID
320 AND PP.PERSON_ID = HOIP.ORG_INFORMATION2';
321
322 -----------------------------------------------------
323 l_debug_info := 'parse cursor';
324 -----------------------------------------------------
325 dbms_sql.parse(l_cur_hdl, l_query_stmt,dbms_sql.native);
326
327 -----------------------------------------------------
328 l_debug_info := 'bind values to the placeholder';
329 -----------------------------------------------------
330 dbms_sql.bind_variable(l_cur_hdl, ':costCenter', p_cost_center);
331 -- Bug: 7558034dbms_sql.bind_variable(l_cur_hdl, ':reportHeaderId', p_report_header_id);
332 dbms_sql.bind_variable(l_cur_hdl, ':charOfAccountsId', l_char_of_accounts_id);
333
334 -----------------------------------------------------
335 l_debug_info := 'setup output';
336 -----------------------------------------------------
337 dbms_sql.define_column(l_cur_hdl, 1, l_cc_owner_id, 150);
338
339 -----------------------------------------------------
340 l_debug_info := 'execute cursor';
341 -----------------------------------------------------
342 l_rows_processed := dbms_sql.execute(l_cur_hdl);
343
344 -----------------------------------------------------
345 l_debug_info := 'fetch a row';
346 -----------------------------------------------------
347 IF dbms_sql.fetch_rows(l_cur_hdl) > 0 then
348 -- fetch columns from the row
349 dbms_sql.column_value(l_cur_hdl, 1, l_cc_owner_id);
350 END IF;
351
352 -----------------------------------------------------
353 l_debug_info := 'close cursor';
354 -----------------------------------------------------
355 dbms_sql.close_cursor(l_cur_hdl);
356
357 END IF;
358
359 return l_cc_owner_id;
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 IF (SQLCODE <> -20001) THEN
364 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
365 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
366 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getCostCenterOwner');
367 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
368 END IF;
369 APP_EXCEPTION.RAISE_EXCEPTION;
370 END getCostCenterOwner;
371
372
373 /*----------------------------------------------------------------------------*
374 | Procedure
375 | getViolationPercentage
376 |
377 | DESCRIPTION
378 | Get the violation percentage of total reimbursable amount.
379 | This function will be for the usage of AME attribute
380 | Policy Violations Percentage.
381 |
382 | PARAMETERS
383 | p_report_header_id - The expense report ID.
384 |
385 | RETURNS
386 | Violation percentage
387 *----------------------------------------------------------------------------*/
388 -------------------------------------------------------------------------
389 FUNCTION getViolationPercentage(p_report_header_id IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE) RETURN NUMBER
390 -------------------------------------------------------------------------
391 IS
392 l_debug_info VARCHAR2(200);
393 l_violationTotal NUMBER := 0;
394 l_reportTotal NUMBER;
395
396 BEGIN
397
398 l_violationTotal := AP_WEB_AME_PKG.getViolationTotal(p_report_header_id);
399
400 -----------------------------------------------------
401 l_debug_info := 'If violation total less than 0 then return 0 percent violated.';
402 -----------------------------------------------------
403 IF (l_violationTotal <= 0) THEN
404 return 0;
405 END IF;
406
407 -----------------------------------------------------
408 l_debug_info := 'Get report total base on the report header id.';
409 -----------------------------------------------------
410 IF (NOT AP_WEB_DB_EXPRPT_PKG.GetHeaderTotal(p_report_header_id, l_reportTotal)) THEN
411 return 0;
412 END IF;
413
414 -----------------------------------------------------
415 -- If violation total greater than 0 but report total less than 0,
416 -- return 100 percent violated.';
417 -----------------------------------------------------
418 IF (l_reportTotal <= 0) THEN
419 return 100;
420 -----------------------------------------------------
421 -- Or return the desired violation percentage here for
422 -- report total less than 0.
423 -----------------------------------------------------
424 END IF;
425
426 return round(100*(l_violationTotal/l_reportTotal),2);
427
428
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 RETURN 0;
432 WHEN OTHERS THEN
433 IF (SQLCODE <> -20001) THEN
434 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
435 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
436 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'getViolationPercentage');
437 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
438 END IF;
439 APP_EXCEPTION.RAISE_EXCEPTION;
440 END getViolationPercentage;
441
442
443 /*----------------------------------------------------------------------------*
444 | Function
445 | isMissingReceiptsShortpay
446 |
447 | DESCRIPTION
448 | Checks if this is a Missing Receipts Shortpay
449 | This function will be for the usage of AME attribute:
450 | Missing Receipts Shortpay
451 |
452 | PARAMETERS
453 | p_report_header_id - The expense report ID.
454 |
455 | RETURNS
456 | ame_util.booleanAttributeTrue if Missing Receipts Shortpay
457 | ame_util.booleanAttributeFalse if not Missing Receipts Shortpay
458 *----------------------------------------------------------------------------*/
459 -------------------------------------------------------------------------
460 FUNCTION isMissingReceiptsShortpay(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
461 -------------------------------------------------------------------------
462 l_debug_info VARCHAR2(200);
463
464 l_is_missing_shortpay varchar2(10) := ame_util.booleanAttributeFalse;
465
466 l_apexp VARCHAR2(8) := 'APEXP';
467 l_no_receipts_shortpay_process VARCHAR2(30) := 'NO_RECEIPTS_SHORTPAY_PROCESS';
468
469 BEGIN
470
471 ------------------------------------------------------------
472 l_debug_info := 'Check if in-process Missing Receipts Shortpay';
473 ------------------------------------------------------------
474 select ame_util.booleanAttributeTrue
475 into l_is_missing_shortpay
476 from ap_expense_report_headers aerh,
477 wf_items wf
478 where aerh.report_header_id = p_report_header_id
479 and aerh.shortpay_parent_id is not null
480 and wf.item_type = l_apexp
481 and wf.Item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
482 and wf.end_date is null
483 and wf.root_activity = l_no_receipts_shortpay_process
484 and rownum = 1;
485
486 return l_is_missing_shortpay;
487
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 RETURN ame_util.booleanAttributeFalse;
491 WHEN OTHERS THEN
492 IF (SQLCODE <> -20001) THEN
493 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
494 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
495 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'isMissingReceiptsShortpay');
496 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
497 END IF;
498 APP_EXCEPTION.RAISE_EXCEPTION;
499 END isMissingReceiptsShortpay;
500
501 /*
502 Returns Award number based on award_id
503 */
504 -------------------------------------------------------------------
505 FUNCTION GetAwardNumber(
506 p_award_id IN NUMBER
507 ) RETURN VARCHAR2 IS
508 -------------------------------------------------------------------
509 l_award_number GMS_AWARDS.AWARD_NUMBER%type;
510 BEGIN
511
512 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start GetAwardNumber');
513
514 select award_number
515 into l_award_number
516 from GMS_AWARDS_ALL
517 where award_id = p_award_id;
518
519 RETURN l_award_number;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetAwardNumber' );
524 APP_EXCEPTION.RAISE_EXCEPTION;
525 END GetAwardNumber;
526
527 /*
528 Calculates p_amount based on p_report_header_id, p_item_class and p_item_id
529 p_item_id -> will have report_header_id if the item_class is C_HEADER
530 -> will have distribution_line_number if the item_class is C_LINE_ITEM
531 -> will have cost center if the item_class is C_COST_CENTER
532 -> will have project_id if the item_class is C_PROJECT
533 -> will have award_id if the item_class is C_AWARD
534 */
535 -------------------------------------------------------------------------
536 PROCEDURE GetItemAmount(p_report_header_id IN NUMBER,
537 p_item_class IN VARCHAR2,
538 p_item_id IN VARCHAR2,
539 p_amount OUT NOCOPY NUMBER) IS
540 -------------------------------------------------------------------------
541 BEGIN
542
543 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start GetItemAmount');
544
545 if (p_item_class = C_HEADER) then
546 select total
547 into p_amount
548 from ap_expense_report_headers_all
549 where report_header_id = p_report_header_id;
550 elsif (p_item_class = C_LINE_ITEM) then
551 select sum(amount)
552 into p_amount
553 from ap_expense_report_lines_all
554 where report_header_id = p_report_header_id
555 and distribution_line_number = p_item_id;
556 -- and report_line_id = p_item_id;
557 elsif (p_item_class = C_PROJECT) then
558 select nvl(sum(amount),0)
559 into p_amount
560 from ap_exp_report_dists_all
561 where report_header_id = p_report_header_id
562 and project_id = p_item_id;
563 elsif (p_item_class = C_AWARD) 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 award_id = p_item_id;
569 elsif (p_item_class = C_COST_CENTER) 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 cost_center = p_item_id;
575 end if;
576
577 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'End GetItemAmount');
578
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN
581 null;
582
583 WHEN OTHERS THEN
584 AP_WEB_DB_UTIL_PKG.RaiseException( 'GetItemAmount' );
585 APP_EXCEPTION.RAISE_EXCEPTION;
586 END GetItemAmount;
587
588
589 /*
590 Inserts data into table OIE_AME_NOTIF_GT
591 */
592 -------------------------------------------------------------------------
593 PROCEDURE InsertToAMENotifGT(
594 p_report_header_id IN NUMBER,
595 p_orig_system IN VARCHAR2,
596 p_orig_system_id IN NUMBER,
597 p_item_class IN VARCHAR2,
598 p_item_id IN VARCHAR2,
599 p_amount IN NUMBER,
600 p_project_number IN VARCHAR2,
601 p_project_name IN VARCHAR2,
602 p_award_number IN VARCHAR2) as
603 -------------------------------------------------------------------------
604 BEGIN
605 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start InsertToAMENotifGT');
606
607 insert into OIE_AME_NOTIF_GT(
608 report_header_id,
609 orig_system,
610 orig_system_id,
611 item_class,
612 item_id ,
613 amount,
614 project_number,
615 project_name,
616 award_number
617 )
618 values
619 (
620 p_report_header_id,
621 p_orig_system,
622 p_orig_system_id,
623 p_item_class,
624 p_item_id,
625 p_amount,
626 p_project_number,
627 p_project_name,
628 p_award_number
629 );
630 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'End InsertToAMENotifGT');
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 AP_WEB_DB_UTIL_PKG.RaiseException( 'InsertToAMENotifGT' );
635 APP_EXCEPTION.RAISE_EXCEPTION;
636 END InsertToAMENotifGT;
637
638 /*
639 Populates data into OIE_AME_NOTIF_GT and OIE_AME_APPROVER_AMT_GT.
640 data in OIE_AME_NOTIF_GT will be used for displaying instructions to
641 the approver in the approval notification.
642 like, You are responsible for approving expenses that total 250.00 USD for cost center 420.
643 data in OIE_AME_APPROVER_AMT_GT will be used for displaying Approver
644 Amount in the approval notification.
645 Calls ame_api2.getallapprovers1 to get the list of approver in the approval
646 list, which would also have info on why the approver is placed on the
647 approval list.
648 p_display_instr -> is set to No if approver is on approval list due to
649 header rules.
650 Inserts only the data that is related to the p_approver_id.
651 Data is populated into OIE_AME_APPROVER_AMT_GT by the call InitOieAmeApproverAmtGT
652 */
653 -------------------------------------------------------------------------
654 PROCEDURE InitOieAmeNotifGT( p_report_header_id IN NUMBER,
655 p_approver_id IN NUMBER,
656 p_display_instr OUT NOCOPY VARCHAR2) iS
657 -------------------------------------------------------------------------
658 l_debug_info VARCHAR2(200);
659 l_approvalProcessCompleteYNOut varchar2(10);
660 l_approversOut ame_util.approversTable2;
661 l_itemIndexesOut ame_util.idList;
662 l_itemClassesOut ame_util.stringList;
663 l_itemIdsOut ame_util.stringList;
664 l_itemSourcesOut ame_util.longStringList;
665
666 l_project_number pa_projects_all.segment1%type;
667 l_project_name pa_projects_all.name%type;
668
669 l_award_number gms_awards.award_number%type;
670
671 l_item_class ame_item_classes.name%type;
672 l_item_id ame_temp_old_approver_lists.item_id%type;
673
674 l_amount number;
675
676 BEGIN
677
678 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'Start InitOieAmeNotifGT');
679
680 p_display_instr := 'Y';
681
682 DELETE FROM OIE_AME_NOTIF_GT;
683
684 -----------------------------------------------------------
685 l_debug_info := 'Before call to ame_api2.getallapprovers1';
686 -----------------------------------------------------------
687 ame_api2.getallapprovers1(
688 applicationIdIn => AP_WEB_DB_UTIL_PKG.GetApplicationID,
689 transactionTypeIn => 'APEXP',
690 transactionIdIn => to_char(p_report_header_id),
691 approvalProcessCompleteYNOut => l_approvalProcessCompleteYNOut,
692 approversOut => l_approversOut,
693 itemIndexesOut => l_itemIndexesOut,
694 itemClassesOut => l_itemClassesOut,
695 itemIdsOut => l_itemIdsOut,
696 itemSourcesOut => l_itemSourcesOut);
697
698 -----------------------------------------------------------
699 l_debug_info := 'After call to ame_api2.getallapprovers1';
700 -----------------------------------------------------------
701
702 FOR i IN 1 .. l_approversOut.count LOOP
703
704 if ( ((l_approversOut(i).approval_status IS NULL) OR
705 (l_approversOut(i).approval_status <> 'REPEATED'))
706 AND
707 (l_approversOut(i).orig_system_id = p_approver_id) ) then
708
709 if l_approversOut(i).item_id is null then
710
711 for j in 1 .. l_itemIndexesOut.count loop
712 if l_itemIdsOut(j) = i then
713
714 l_item_class := l_itemClassesOut(j);
715 l_item_id := l_itemIdsOut(j);
716
717 if (l_item_class = C_HEADER) then
718 p_display_instr := 'N';
719 end if;
720
721 if (l_item_class = C_PROJECT) then
722 if not AP_WEB_DB_PA_INT_PKG.GetProjectInfo(to_number(l_item_id),
723 l_project_number,
724 l_project_name) then
725 null;
726 end if;
727 end if;
728
729 if (l_item_class = C_AWARD) then
730 l_award_number := GetAwardNumber(to_number(l_item_id));
731 end if;
732
733 -----------------------------------------------------------
734 l_debug_info := 'Before call to GetItemAmount';
735 -----------------------------------------------------------
736 GetItemAmount(p_report_header_id,
737 l_item_class,
738 l_item_id,
739 l_amount);
740
741 -----------------------------------------------------------
742 l_debug_info := 'Before call to InsertToAMENotifGT';
743 -----------------------------------------------------------
744 InsertToAMENotifGT(
745 p_report_header_id,
746 l_approversOut(i).orig_system,
747 l_approversOut(i).orig_system_id,
748 l_itemClassesOut(j),
749 l_itemIdsOut(j),
750 l_amount,
751 l_project_number,
752 l_project_name,
753 l_award_number
754 );
755 end if;
756 end loop; -- l_itemIndexesOut
757
758 else -- if l_approversOut(i).item_id is null
759
760 -- logic for l_approversOut(i).item_id is null
761 l_item_class := l_approversOut(i).item_class;
762 l_item_id := l_approversOut(i).item_id;
763
764 if (l_item_class = C_HEADER) then
765 p_display_instr := 'N';
766 end if;
767
768 if (l_item_class = C_PROJECT) then
769 if not AP_WEB_DB_PA_INT_PKG.GetProjectInfo(to_number(l_item_id),
770 l_project_number,
771 l_project_name) then
772 null;
773 end if;
774 end if;
775
776 if (l_item_class = C_AWARD) then
777 l_award_number := GetAwardNumber(to_number(l_item_id));
778 end if;
779
780 -----------------------------------------------------------
781 l_debug_info := 'Before call to GetItemAmount';
782 -----------------------------------------------------------
783 GetItemAmount(p_report_header_id,
784 l_item_class,
785 l_item_id,
786 l_amount);
787
788 -----------------------------------------------------------
789 l_debug_info := 'Before call to InsertToAMENotifGT';
790 -----------------------------------------------------------
791 InsertToAMENotifGT(
792 p_report_header_id,
793 l_approversOut(i).orig_system,
794 l_approversOut(i).orig_system_id,
795 l_approversOut(i).item_class,
796 l_approversOut(i).item_id ,
797 l_amount,
798 l_project_number,
799 l_project_name,
800 l_award_number
801 );
802 end if; -- if l_approversOut(i).item_id is null
803 end if;
804
805 END LOOP; -- l_approversOut.count
806
807 -----------------------------------------------------------
808 l_debug_info := 'Before call to InitOieAmeApproverAmtGT';
809 -----------------------------------------------------------
810 InitOieAmeApproverAmtGT(p_report_header_id);
811
812 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'end InitOieAmeNotifGT');
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 IF (SQLCODE <> -20001) THEN
817 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
818 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
819 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'InitOieAmeNotifGT');
820 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
821 END IF;
822 APP_EXCEPTION.RAISE_EXCEPTION;
823 END InitOieAmeNotifGT;
824
825 /*
826 populates the global temporary table OIE_AME_APPROVER_AMT_GT
827 with approver amount for each line.
828 If the approver is on the approver list due to line item rules
829 then approver amount is same as line amount.
830 otherwise the approver amount is calculated as sum of amount
831 in the distributions for which the approver is on the approver
832 list i.e, project or cost center or award.
833 The information why the approver is on the approver list exist
834 in oie_ame_notif_gt.
835 */
836 PROCEDURE InitOieAmeApproverAmtGT(p_report_header_id IN NUMBER) IS
837
838 -- 5213228: include only itemization parent lines in the cursor as we
839 -- display only the parent lines in the notification.
840 -- 5417790: include only itemization child lines in the cursor as we
841 -- display only the child lines in the notification.
842 cursor c1(p_report_header_id in number) is
843 select report_line_id, nvl(amount,0) amount, distribution_line_number, itemization_parent_id
844 from ap_expense_report_lines_all xl
845 where xl.report_header_id = p_report_header_id
846 and (xl.itemization_parent_id is null or xl.itemization_parent_id <> -1);
847
848 l_debug_info VARCHAR2(200);
849 l_line_approver varchar2(1);
850 l_approver_amount number;
851
852 BEGIN
853
854 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'start InitOieAmeApproverAmtGT');
855
856 DELETE FROM OIE_AME_APPROVER_AMT_GT;
857
858 for i in c1(p_report_header_id) loop
859 begin
860 -- gt.item_id -> will have distribution_line_number if the gt.item_class is C_LINE_ITEM
861 select 'Y'
862 into l_line_approver
863 from oie_ame_notif_gt gt
864 where gt.item_class = C_LINE_ITEM
865 and gt.item_id = i.distribution_line_number;
866 -- and gt.item_id = i.report_line_id;
867
868 l_approver_amount := i.amount;
869
870 exception
871 when no_data_found then
872 l_line_approver := 'N';
873 when others then
874 l_line_approver := 'N';
875 end;
876
877 if (l_line_approver = 'N') then
878 /* gt.item_id -> will have cost center if the gt.item_class is C_COST_CENTER
879 -> will have project_id if the gt.item_class is C_PROJECT
880 -> will have award_id if the gt.item_class is C_AWARD
881 */
882 select sum(xd.amount)
883 into l_approver_amount
884 from ap_exp_report_dists_all xd,
885 oie_ame_notif_gt gt
886 where xd.report_header_id = p_report_header_id
887 and xd.report_line_id = i.report_line_id
888 and ( (xd.cost_center = gt.item_id
889 and
890 gt.item_class = C_COST_CENTER)
891 or
892 (xd.project_id = gt.item_id
893 and
894 gt.item_class = C_PROJECT)
895 or
896 (xd.award_id = gt.item_id
897 and
898 gt.item_class = C_AWARD) );
899
900 if (l_approver_amount > i.amount) then
901 l_approver_amount := i.amount;
902 end if;
903
904 end if; -- (l_line_approver = 'N')
905
906
907 insert into OIE_AME_APPROVER_AMT_GT(
908 report_header_id,
909 report_line_id,
910 approver_amount)
911 values (
912 p_report_header_id,
913 i.report_line_id,
914 nvl(l_approver_amount,0));
915 end loop;
916
917 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AME_PKG', 'end InitOieAmeApproverAmtGT');
918
919 EXCEPTION
920 WHEN OTHERS THEN
921 IF (SQLCODE <> -20001) THEN
922 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
923 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
924 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'InitOieAmeApproverAmtGT');
925 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
926 END IF;
927 APP_EXCEPTION.RAISE_EXCEPTION;
928 END InitOieAmeApproverAmtGT;
929
930 END AP_WEB_AME_PKG;
[Home] [Help]