1 PACKAGE BODY AP_WEB_AUDIT_UTILS AS
2 /* $Header: apwaudub.pls 120.51.12020000.2 2013/03/05 10:02:12 preshukl ship $ */
3
4 pg_personal_parameter_id number := to_number(null);
5 pg_rounding_parameter_id number := to_number(null);
6
7 -- Cache for get_report_status_code
8 grsc_old_report_header_id NUMBER := NULL;
9 grsc_old_invoice_id NUMBER := NULL;
10 grsc_old_status_code ap_lookup_codes.lookup_code%TYPE := NULL;
11
12 FUNCTION get_attribute_value(p_report_header_id IN NUMBER,
13 p_distribution_line_number IN NUMBER,
14 p_column IN VARCHAR2) RETURN VARCHAR2;
15
16 /*========================================================================
17 | PUBLIC FUNCTION get_employee_info
18 |
19 | DESCRIPTION
20 | This function returns the employee info for a employee.
21 |
22 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
23 | Called from BC4J.
24 |
25 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
26 |
27 | RETURNS
28 | Employee info for the given user as VARCHAR2.
29 |
30 | PARAMETERS
31 | p_employee_id IN Employee identifier
32 | p_column IN Column from which the data is retrieved
33 | p_data_type IN Data type of the column from which the data is retrieved.
34 | Supported values: NUMBER, VARCHAR2, DATE
35 |
36 | MODIFICATION HISTORY
37 | Date Author Description of Changes
38 | 25-May-2002 J Rautiainen Created
39 |
40 *=======================================================================*/
41 FUNCTION get_employee_info(p_employee_id IN NUMBER,
42 p_column IN VARCHAR2,
43 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
44
45 l_query_stmt VARCHAR2(4000);
46 l_result_number NUMBER;
47 l_result_varchar VARCHAR2(4000);
48 l_result_date DATE;
49 l_column1 VARCHAR2(4000);
50 l_column2 VARCHAR2(4000);
51
52 TYPE CurType IS REF CURSOR;
53 cur CurType;
54
55 BEGIN
56 IF p_employee_id is null or p_column is null THEN
57 return null;
58 END IF;
59
60 IF p_data_type not in ('NUMBER', 'VARCHAR2', 'DATE') THEN
61 return null;
62 END IF;
63
64 /* 2-Oct-2003 J Rautiainen Contingent project changes
65 * This function is used to fetch information of a employee, regardless of
66 * the status of the employee. Eg. auditor might be viewing a expense report
67 * of a terminated or suspended employee, so this method should still return
68 * the info on the employee. Also since the contingent worker can enter
69 * expense reports, the function needs to query also on those.
70 */
71 IF INSTR(UPPER(p_column),'EMPLOYEE_NUMBER') > 0 THEN
72 l_column1 := 'EMPLOYEE_NUM';
73 l_column2 := 'NPW_NUMBER';
74 ELSE
75 l_column1 := p_column;
76 l_column2 := p_column;
77 END IF;
78
79
80 l_query_stmt := 'select ' || p_column || ' ' ||
81 'from ' ||
82 '(SELECT ' || l_column1 || ' ' || p_column || ' ' ||
83 'FROM PER_EMPLOYEES_X EMP ' ||
84 'WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)=''Y'' ' ||
85 'AND EMP.EMPLOYEE_ID = :b1 ' ||
86 'UNION ALL ' ||
87 'SELECT ' || l_column2 || ' ' || p_column || ' ' ||
88 'FROM PER_CONT_WORKERS_CURRENT_X CWK ' ||
89 'WHERE CWK.PERSON_ID = :b2) wf';
90
91 OPEN cur FOR l_query_stmt USING p_employee_id, p_employee_id;
92
93 IF p_data_type = 'VARCHAR2' THEN
94 FETCH cur INTO l_result_varchar;
95 ELSIF p_data_type = 'NUMBER' THEN
96 FETCH cur INTO l_result_number;
97 l_result_varchar := to_char(l_result_number);
98 ELSIF p_data_type = 'DATE' THEN
99 FETCH cur INTO l_result_date;
100 l_result_varchar := to_char(l_result_date,'DD-MON-RRRR');
101 ELSE
102 CLOSE cur;
103 return null;
104 END IF;
105
106 CLOSE cur;
107
108 return l_result_varchar;
109
110 END get_employee_info;
111
112 /*========================================================================
113 | PUBLIC FUNCTION get_task_info
114 |
115 | DESCRIPTION
116 | This function returns the task info for a given task.
117 |
118 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
119 | Called from BC4J.
120 |
121 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
122 |
123 | RETURNS
124 | Task info for the given user as VARCHAR2.
125 |
126 | PARAMETERS
127 | p_task_id IN Task identifier
128 | p_column IN Column from which the data is retrieved
129 | p_data_type IN Data type of the column from which the data is retrieved.
130 | Supported values: NUMBER, VARCHAR2, DATE
131 |
132 | MODIFICATION HISTORY
133 | Date Author Description of Changes
134 | 22-Aug-2002 J Rautiainen Created
135 |
136 *=======================================================================*/
137 FUNCTION get_task_info(p_task_id IN NUMBER,
138 p_column IN VARCHAR2,
139 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
140
141 BEGIN
142
143 return get_object_info(to_char(p_task_id),
144 p_column,
145 p_data_type,
146 'PA_TASKS_EXPEND_V',
147 'task_id');
148
149 END get_task_info;
150
151 /*========================================================================
152 | PUBLIC FUNCTION get_project_info
153 |
154 | DESCRIPTION
155 | This function returns the project info for a given project.
156 |
157 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
158 | Called from BC4J.
159 |
160 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
161 |
162 | RETURNS
163 | Project info for the given user as VARCHAR2.
164 |
165 | PARAMETERS
166 | p_project_id IN Project identifier
167 | p_column IN Column from which the data is retrieved
168 | p_data_type IN Data type of the column from which the data is retrieved.
169 | Supported values: NUMBER, VARCHAR2, DATE
170 |
171 | MODIFICATION HISTORY
172 | Date Author Description of Changes
173 | 22-Aug-2002 J Rautiainen Created
174 |
175 *=======================================================================*/
176 FUNCTION get_project_info(p_project_id IN NUMBER,
177 p_column IN VARCHAR2,
178 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
179
180 BEGIN
181
182 return get_object_info(to_char(p_project_id),
183 p_column,
184 p_data_type,
185 'pa_projects_all',
186 'project_id');
187
188 END get_project_info;
189
190 /*========================================================================
191 | PUBLIC FUNCTION get_award_info
192 |
193 | DESCRIPTION
194 | This function returns the award info for a given award.
195 |
196 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
197 | Called from BC4J.
198 |
199 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
200 |
201 | RETURNS
202 | Project info for the given user as VARCHAR2.
203 |
204 | PARAMETERS
205 | p_award_id IN Award identifier
206 | p_column IN Column from which the data is retrieved
207 | p_data_type IN Data type of the column from which the data is retrieved.
208 | Supported values: NUMBER, VARCHAR2, DATE
209 |
210 | MODIFICATION HISTORY
211 | Date Author Description of Changes
212 | 22-Aug-2002 J Rautiainen Created
213 |
214 *=======================================================================*/
215 FUNCTION get_award_info(p_award_id IN NUMBER,
216 p_column IN VARCHAR2,
217 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
218 BEGIN
219
220 return get_object_info(to_char(p_award_id),
221 p_column,
222 p_data_type,
223 'GMS_AWARDS_ALL',
224 'award_id');
225
226 END get_award_info;
227
228 /*========================================================================
229 | PUBLIC FUNCTION get_awt_group_info
230 |
231 | DESCRIPTION
232 | This function returns the awt group info for a given awt group.
233 |
234 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
235 | Called from BC4J.
236 |
237 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
238 |
239 | RETURNS
240 | Awt Group info as VARCHAR2.
241 |
242 | PARAMETERS
243 | p_awt_group_id IN Awt group identifier
244 | p_column IN Column from which the data is retrieved
245 | p_data_type IN Data type of the column from which the data is retrieved.
246 | Supported values: NUMBER, VARCHAR2, DATE
247 |
248 | MODIFICATION HISTORY
249 | Date Author Description of Changes
250 | 22-Aug-2002 J Rautiainen Created
251 |
252 *=======================================================================*/
253 FUNCTION get_awt_group_info(p_awt_group_id IN NUMBER,
254 p_column IN VARCHAR2,
255 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
256
257 BEGIN
258
259 return get_object_info(to_char(p_awt_group_id),
260 p_column,
261 p_data_type,
262 'ap_awt_groups',
263 'group_id');
264
265 END get_awt_group_info;
266
267 /*========================================================================
268 | PUBLIC FUNCTION get_tax_code_info
269 |
270 | DESCRIPTION
271 | This function returns the tax code info for a given tax code based.
272 | on either tax code id or tax code name.
273 |
274 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
275 | Called from BC4J.
276 |
277 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
278 |
279 | RETURNS
280 | Tax code info as VARCHAR2.
281 |
282 | PARAMETERS
283 | p_tax_id IN Tax code identifier
284 | p_name IN Tax code name
285 | p_column IN Column from which the data is retrieved
286 | p_data_type IN Data type of the column from which the data is retrieved.
287 | Supported values: NUMBER, VARCHAR2, DATE
288 |
289 | MODIFICATION HISTORY
290 | Date Author Description of Changes
291 | 22-Aug-2002 J Rautiainen Created
292 |
293 *=======================================================================*/
294 FUNCTION get_tax_code_info(p_tax_id IN NUMBER,
295 p_name IN VARCHAR2,
296 p_column IN VARCHAR2,
297 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
298
299 BEGIN
300 IF p_tax_id is not null THEN
301 return get_object_info(to_char(p_tax_id),
302 p_column,
303 p_data_type,
304 'ap_tax_codes_all',
305 'tax_id');
306
307 ELSIF p_name is not null THEN
308
309 return get_object_info(p_name,
310 p_column,
311 p_data_type,
312 'ap_tax_codes_all',
313 'name');
314 ELSE
315 return null;
316 END IF;
317
318 END get_tax_code_info;
319
320 /*========================================================================
321 | PUBLIC FUNCTION get_line_status
322 |
323 | DESCRIPTION
324 | This function returns the expense report line status for auditor. Line
325 | status consists either of "OK" or of the list of policy violations if
326 | violations exist.
327 |
328 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
329 | Called from BC4J.
330 |
331 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
332 |
333 | RETURNS
334 | Expense line status as VARCHAR2.
335 |
336 | PARAMETERS
337 | p_report_header_id IN Expense report header identifier
338 | p_distribution_line_number IN Expense report line identifier
339 |
340 | MODIFICATION HISTORY
341 | Date Author Description of Changes
342 | 22-Aug-2002 J Rautiainen Created
343 |
344 *=======================================================================*/
345 FUNCTION get_line_status(p_report_header_id IN NUMBER,
346 p_distribution_line_number IN NUMBER) RETURN VARCHAR2 IS
347 CURSOR violation_cur IS
348 select violation_type,
349 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_POL_VIOLATION_TYPES',violation_type) violation
350 from AP_POL_VIOLATIONS_ALL
351 where report_header_id = p_report_header_id
352 and distribution_line_number = p_distribution_line_number
353 AND violation_type <> 'DUPLICATE_DETECTION'
354 order by violation_number;
355
356 CURSOR dup_violation_cur IS
357 SELECT violation_type, 'Duplicate Detection' violation,
358 dup_report_header_id, To_Char(dup_report_line_id) dup_report_line_id, dup_dist_line_number
359 FROM ap_pol_violations_all
360 WHERE report_header_id = p_report_header_id
361 AND distribution_line_number = p_distribution_line_number
362 AND violation_type = 'DUPLICATE_DETECTION'
363 ORDER BY violation_number;
364
365 violation_rec violation_cur%ROWTYPE;
366 dup_violation_rec dup_violation_cur%ROWTYPE;
367 l_counter NUMBER := 0;
368 l_dup_counter NUMBER := 0;
369 l_result VARCHAR2(4000);
370 l_dup_result VARCHAR2(4000) := NULL;
371
372 BEGIN
373 IF p_report_header_id is null or p_distribution_line_number is null THEN
374 return null;
375 END IF;
376
377 FOR violation_rec IN violation_cur LOOP
378 l_counter := l_counter + 1;
379 IF l_counter = 1 THEN
380 l_result := violation_rec.violation;
381 ELSE
382 l_result := l_result || ', '||violation_rec.violation;
383 END IF;
384 END LOOP;
385
386 FOR dup_violation_rec IN dup_violation_cur LOOP
387 l_dup_counter := l_dup_counter + 1;
388 IF l_dup_counter = 1 THEN
389 l_dup_result := dup_violation_rec.violation || ' (' || dup_violation_rec.dup_report_header_id || ' - Line ' || dup_violation_rec.dup_dist_line_number;
390 ELSE
391 l_dup_result := l_dup_result || ', ' || dup_violation_rec.dup_report_header_id || ' - Line ' || dup_violation_rec.dup_dist_line_number;
392 END IF;
393 END LOOP;
394
395 IF l_dup_result IS NOT NULL THEN
396 l_dup_result := l_dup_result || ')';
397 IF l_counter > 0 THEN
398 l_result := l_result || ', ' || l_dup_result;
399 ELSE
400 l_result := l_dup_result;
401 END IF;
402 END IF;
403
404 l_counter := l_counter + l_dup_counter;
405
406 IF l_counter > 0 THEN
407 return l_result;
408 ELSE
409 return fnd_message.GET_STRING('SQLAP','OIE_AUD_NO_VIOLATIONS');
410 END IF;
411
412 END get_line_status;
413
414 /*========================================================================
415 | PUBLIC FUNCTION get_expense_type
416 |
417 | DESCRIPTION
418 | This function returns the expense line type.
419 |
420 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
421 | Called from BC4J.
422 |
423 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
424 |
425 | RETURNS
426 | Expense line type as VARCHAR2.
427 |
428 | PARAMETERS
429 | p_parameter_id IN Expense type identifier
430 |
431 | MODIFICATION HISTORY
432 | Date Author Description of Changes
433 | 22-Aug-2002 J Rautiainen Created
434 |
435 *=======================================================================*/
436 FUNCTION get_expense_type(p_parameter_id IN NUMBER) RETURN VARCHAR2 IS
437
438 CURSOR expense_type_cur IS
439 SELECT nvl(WEB_FRIENDLY_PROMPT, PROMPT) expense_type_prompt
440 FROM AP_EXPENSE_REPORT_PARAMS_ALL
441 WHERE PARAMETER_ID = p_parameter_id;
442
443 expense_type_rec expense_type_cur%ROWTYPE;
444 BEGIN
445 IF p_parameter_id is null THEN
446 return null;
447 END IF;
448
449 OPEN expense_type_cur;
450 FETCH expense_type_cur INTO expense_type_rec;
451
452 IF expense_type_cur%NOTFOUND THEN
453 CLOSE expense_type_cur;
454 return null;
455 END IF;
456
457 CLOSE expense_type_cur;
458 return expense_type_rec.expense_type_prompt;
459
460 END get_expense_type;
461
462 /*========================================================================
463 | PUBLIC FUNCTION get_allowable_amount
464 |
465 | DESCRIPTION
466 | This function returns the allowable amount on an line with policy violation.
467 |
468 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
469 | Called from BC4J.
470 |
471 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
472 |
473 | RETURNS
474 | Expense line allowable amount as NUMBER.
475 |
476 | PARAMETERS
477 | p_report_header_id IN Expense report header identifier
478 | p_distribution_line_number IN Expense report line identifier
479 |
480 | MODIFICATION HISTORY
481 | Date Author Description of Changes
482 | 22-Aug-2002 J Rautiainen Created
483 |
484 *=======================================================================*/
485 FUNCTION get_allowable_amount(p_report_header_id IN NUMBER,
486 p_distribution_line_number IN NUMBER) RETURN NUMBER IS
487 CURSOR amount_cur IS
488 select min(allowable_amount) allowable_amount
489 from ap_pol_violations_all
490 where report_header_id = p_report_header_id
491 and distribution_line_number = p_distribution_line_number
492 and violation_type in ('DAILY_LIMIT','INDIVIDUAL_LIMIT');
493
494 amount_rec amount_cur%ROWTYPE;
495 BEGIN
496 IF p_report_header_id is null or p_distribution_line_number is null THEN
497 return null;
498 END IF;
499
500 OPEN amount_cur;
501 FETCH amount_cur INTO amount_rec;
502
503 IF amount_cur%NOTFOUND THEN
504 CLOSE amount_cur;
505 return null;
506 END IF;
507
508 CLOSE amount_cur;
509 return amount_rec.allowable_amount;
510
511 END get_allowable_amount;
512
513 /*========================================================================
514 | PUBLIC FUNCTION get_allowable_cc_amount
515 |
516 | DESCRIPTION
517 | This function returns the allowable credit card amount on a line with credit card violation.
518 |
519 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
520 | Called from BC4J.
521 |
522 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
523 |
524 | RETURNS
525 | Expense line credit card allowable amount as NUMBER.
526 |
527 | PARAMETERS
528 | p_report_header_id IN Expense report header identifier
529 | p_distribution_line_number IN Expense report line identifier
530 |
531 | MODIFICATION HISTORY
532 | Date Author Description of Changes
533 | 28-Jul-2004 R Langi Copied
534 |
535 *=======================================================================*/
536 FUNCTION get_allowable_cc_amount(p_report_header_id IN NUMBER,
537 p_distribution_line_number IN NUMBER) RETURN NUMBER IS
538 CURSOR amount_cur IS
539 select min(allowable_amount) allowable_cc_amount
540 from ap_pol_violations_all
541 where report_header_id = p_report_header_id
542 and distribution_line_number = p_distribution_line_number
543 and violation_type in ('CC_REQUIRED');
544
545 amount_rec amount_cur%ROWTYPE;
546 BEGIN
547 IF p_report_header_id is null or p_distribution_line_number is null THEN
548 return null;
549 END IF;
550
551 OPEN amount_cur;
552 FETCH amount_cur INTO amount_rec;
553
554 IF amount_cur%NOTFOUND THEN
555 CLOSE amount_cur;
556 return null;
557 END IF;
558
559 CLOSE amount_cur;
560 return amount_rec.allowable_cc_amount;
561
562 END get_allowable_cc_amount;
563
564 /*========================================================================
565 | PUBLIC FUNCTION get_allowable_daily_sum
566 |
567 | DESCRIPTION
568 | This function returns the allowable daily sum on an line with policy violation.
569 |
570 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
571 | Called from BC4J.
572 |
573 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
574 |
575 | RETURNS
576 | Expense line allowable daily sum as NUMBER.
577 |
578 | PARAMETERS
579 | p_report_header_id IN Expense report header identifier
580 | p_distribution_line_number IN Expense report line identifier
581 |
582 | MODIFICATION HISTORY
583 | Date Author Description of Changes
584 | 04-Apr-2003 J Rautiainen Created
585 |
586 *=======================================================================*/
587 FUNCTION get_allowable_daily_sum(p_report_header_id IN NUMBER,
588 p_distribution_line_number IN NUMBER) RETURN NUMBER IS
589 CURSOR amount_cur IS
590 select min(allowable_amount) allowable_amount
591 from ap_pol_violations_all
592 where report_header_id = p_report_header_id
593 and distribution_line_number = p_distribution_line_number
594 and violation_type in ('DAILY_SUM_LIMIT');
595
596 amount_rec amount_cur%ROWTYPE;
597 BEGIN
598 IF p_report_header_id is null or p_distribution_line_number is null THEN
599 return null;
600 END IF;
601
602 OPEN amount_cur;
603 FETCH amount_cur INTO amount_rec;
604
605 IF amount_cur%NOTFOUND THEN
606 CLOSE amount_cur;
607 return null;
608 END IF;
609
610 CLOSE amount_cur;
611 return amount_rec.allowable_amount;
612
613 END get_allowable_daily_sum;
614
615 /*========================================================================
616 | PUBLIC FUNCTION get_allowable_rate
617 |
618 | DESCRIPTION
619 | This function returns the allowable rate on an line with policy violation.
620 |
621 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
622 | Called from BC4J.
623 |
624 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
625 |
626 | RETURNS
627 | Expense line allowable amount as NUMBER.
628 |
629 | PARAMETERS
630 | p_report_header_id IN Expense report header identifier
631 | p_distribution_line_number IN Expense report line identifier
632 |
633 | MODIFICATION HISTORY
634 | Date Author Description of Changes
635 | 22-Aug-2002 J Rautiainen Created
636 |
637 *=======================================================================*/
638 FUNCTION get_allowable_rate(p_report_header_id IN NUMBER,
639 p_distribution_line_number IN NUMBER) RETURN NUMBER IS
640 CURSOR allowance_cur IS
641 select aerh.payment_currency_code reimbursement_currency_code,
642 aerl.start_expense_date,
643 aerl.receipt_currency_code,
644 eo.exchange_rate_id,
645 eo.exchange_rate_type,
646 eo.exchange_rate_allowance,
647 eo.overall_tolerance,
648 eo.org_id
649 from AP_POL_EXRATE_OPTIONS_ALL eo,
650 AP_EXPENSE_REPORT_LINES_ALL aerl,
651 AP_EXPENSE_REPORT_HEADERS_ALL aerh,
652 AP_POL_VIOLATIONS_ALL pv
653 where aerl.report_header_id = pv.report_header_id
654 and aerl.distribution_line_number = pv.distribution_line_number
655 and aerl.credit_card_trx_id is null
656 and aerh.report_header_id = aerl.report_header_id
657 and aerh.org_id = aerl.org_id
658 and eo.org_id = aerl.org_id
659 and eo.enabled = 'Y'
660 and pv.report_header_id = p_report_header_id
661 and pv.distribution_line_number = p_distribution_line_number
662 and pv.violation_type in ('EXCHANGE_RATE_LIMIT');
663
664 allowance_rec allowance_cur%ROWTYPE;
665 x_converted_amount NUMBER;
666 x_denominator NUMBER;
667 x_numerator NUMBER;
668 x_rate NUMBER;
669 ln_max_rate NUMBER := to_number(null);
670 lv_inverse_rate VARCHAR2(100) := 'N';
671 BEGIN
672 IF p_report_header_id is null or p_distribution_line_number is null THEN
673 return to_number(null);
674 END IF;
675
676 OPEN allowance_cur;
677 FETCH allowance_cur INTO allowance_rec;
678
679 IF allowance_cur%NOTFOUND THEN
680 CLOSE allowance_cur;
681 return to_number(null);
682 END IF;
683
684 CLOSE allowance_cur;
685
686 GL_CURRENCY_API.convert_closest_amount(allowance_rec.receipt_currency_code, -- x_from_currency
687 allowance_rec.reimbursement_currency_code, -- x_to_currency
688 allowance_rec.start_expense_date, -- x_conversion_date,
689 allowance_rec.exchange_rate_type, -- x_conversion_type,
690 0, -- x_user_rate
691 100, -- x_amount
692 0, -- x_max_roll_days
693 x_converted_amount,
694 x_denominator,
695 x_numerator,
696 x_rate);
697 IF x_rate is NULL THEN
698 return to_number(null);
699 END IF;
700
701 /* Bug 3966257. Removed tolerance calculations. */
702
703 ln_max_rate := x_rate * (1 + NVL(allowance_rec.exchange_rate_allowance,0)/100);
704 lv_inverse_rate := fnd_profile.VALUE('DISPLAY_INVERSE_RATE');
705
706 IF ln_max_rate is null OR ln_max_rate = 0 THEN
707 return to_number(null);
708 ELSIF lv_inverse_rate = 'Y' THEN
709 return ROUND(1/ln_max_rate,6);
710 ELSE
711 return ROUND(ln_max_rate,6);
712 END IF;
713
714 -- Bug# 8988226 - Exception should be handled and a null value should be returned
715 -- when the package GL_CURRENCY_API throws an user-defined exception
716 EXCEPTION
717 WHEN OTHERS THEN
718 return to_number(null);
719
720 END get_allowable_rate;
721
722 FUNCTION get_object_info(p_key IN VARCHAR2,
723 p_column IN VARCHAR2,
724 p_result_type IN VARCHAR2,
725 p_table IN VARCHAR2,
726 p_key_column IN VARCHAR2,
727 p_order_by_clause IN VARCHAR2) RETURN VARCHAR2 IS
728
729 l_query_stmt VARCHAR2(4000);
730 l_result_number NUMBER;
731 l_result_varchar VARCHAR2(4000);
732 l_result_date DATE;
733
734 TYPE CurType IS REF CURSOR;
735 cur CurType;
736
737 BEGIN
738 IF p_key is null or p_column is null THEN
739 return null;
740 END IF;
741
742 IF p_result_type not in ('NUMBER', 'VARCHAR2', 'DATE') THEN
743 return null;
744 END IF;
745
746 l_query_stmt := 'select '||p_column||' result from '||p_table||' where '||p_key_column||' = :b1 '||p_order_by_clause;
747
748 OPEN cur FOR l_query_stmt USING p_key;
749
750 IF p_result_type = 'VARCHAR2' THEN
751 FETCH cur INTO l_result_varchar;
752 ELSIF p_result_type = 'NUMBER' THEN
753 FETCH cur INTO l_result_number;
754 l_result_varchar := to_char(l_result_number);
755 ELSIF p_result_type = 'DATE' THEN
756 FETCH cur INTO l_result_date;
757 l_result_varchar := to_char(l_result_date,'DD-MON-RRRR');
758 ELSE
759 CLOSE cur;
760 return null;
761 END IF;
762
763 CLOSE cur;
764
765 return l_result_varchar;
766
767 END get_object_info;
768
769 /*========================================================================
770 | PUBLIC FUNCTION get_concat_desc_flex
771 |
772 | DESCRIPTION
773 | This function returns the descriptive flexfield definition related to a row.
774 |
775 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
776 | Called from BC4J.
777 |
778 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
779 |
780 | RETURNS
781 | CCID.
782 |
783 | PARAMETERS
784 | p_report_header_id IN Expense report header identifier
785 | p_distribution_line_number IN Expense report line identifier
786 |
787 | MODIFICATION HISTORY
788 | Date Author Description of Changes
789 | 22-Aug-2002 J Rautiainen Created
790 |
791 *=======================================================================*/
792 FUNCTION get_concat_desc_flex(p_report_header_id IN NUMBER,
793 p_distribution_line_number IN NUMBER) RETURN VARCHAR2 IS
794 CURSOR template_cur IS
795 select prompt web_prompt
796 from ap_expense_report_lines_all aerl,
797 ap_expense_report_params_all aerp
798 where aerl.report_header_id = p_report_header_id
799 and aerl.distribution_line_number = p_distribution_line_number
800 and aerp.parameter_id = aerl.web_parameter_id;
801
802 v_result VARCHAR2(4000);
803 v_value VARCHAR2(4000);
804 template_rec template_cur%ROWTYPE;
805 flexfield fnd_dflex.dflex_r;
806 flexinfo fnd_dflex.dflex_dr;
807 contexts fnd_dflex.contexts_dr;
808 i BINARY_INTEGER;
809 segments fnd_dflex.segments_dr;
810 first_component boolean := TRUE;
811
812 BEGIN
813 IF p_report_header_id is null OR p_distribution_line_number is null THEN
814 return null;
815 END IF;
816
817 fnd_dflex.get_flexfield('SQLAP', 'AP_EXPENSE_REPORT_LINES', flexfield, flexinfo);
818 fnd_dflex.get_contexts(flexfield, contexts);
819
820 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield, contexts.context_code(contexts.global_context)),segments,TRUE);
821
822 FOR i IN 1 .. segments.nsegments LOOP
823
824 v_value := AP_WEB_AUDIT_UTILS.get_attribute_value(p_report_header_id, p_distribution_line_number, segments.application_column_name(i));
825
826 IF v_value is not null THEN
827
828 IF not first_component THEN
829 v_result := v_result||', ';
830 ELSE
831 first_component := FALSE;
832 END IF;
833
834 v_result := v_result || segments.segment_name(i) || ': ' ||v_value;
835 END IF;
836
837 v_value := NULL;
838
839 END LOOP;
840
841 OPEN template_cur;
842 FETCH template_cur INTO template_rec;
843 IF template_cur%NOTFOUND OR template_rec.web_prompt is null THEN
844 CLOSE template_cur;
845 RETURN v_result;
846 END IF;
847 CLOSE template_cur;
848
849 /* Doing substrb since OIE uses expense type name as the context value. However the context value code
850 * definition only accepts 30 characters, but the expense type name can be 80. This was discussed with
851 * OIE team and their suggested resolution was to substr the value. */
852 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield, SUBSTRB(template_rec.web_prompt,0,30)),segments,TRUE);
853
854
855 FOR i IN 1 .. segments.nsegments LOOP
856
857 v_value := AP_WEB_AUDIT_UTILS.get_attribute_value(p_report_header_id, p_distribution_line_number, segments.application_column_name(i));
858
859 IF v_value is not null THEN
860
861 IF not first_component THEN
862 v_result := v_result||', ';
863 ELSE
864 first_component := FALSE;
865 END IF;
866
867 v_result := v_result || segments.segment_name(i) || ': ' ||v_value;
868 END IF;
869
870 v_value := NULL;
871
872 END LOOP;
873
874 RETURN v_result;
875
876 END get_concat_desc_flex;
877
878 FUNCTION get_attribute_value(p_report_header_id IN NUMBER,
879 p_distribution_line_number IN NUMBER,
880 p_column IN VARCHAR2) RETURN VARCHAR2 IS
881
882 l_query_stmt VARCHAR2(4000);
883 l_result_varchar VARCHAR2(4000);
884
885 TYPE CurType IS REF CURSOR;
886 cur CurType;
887
888 BEGIN
889 IF p_report_header_id is null OR p_distribution_line_number is null OR p_column is null THEN
890 return null;
891 END IF;
892
893 l_query_stmt := 'select '||p_column||' result from ap_expense_report_lines_all where report_header_id = :b1 and distribution_line_number = :b2';
894
895 OPEN cur FOR l_query_stmt USING p_report_header_id, p_distribution_line_number;
896 FETCH cur INTO l_result_varchar;
897 CLOSE cur;
898
899 return l_result_varchar;
900
901 END get_attribute_value;
902
903 /*========================================================================
904 | PUBLIC FUNCTION get_flex_structure_code
905 |
906 | DESCRIPTION
907 | This function returns the flex structure code for a given org.
908 |
909 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
910 | Called from BC4J.
911 |
912 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
913 |
914 | RETURNS
915 | Flex structure code.
916 |
917 | PARAMETERS
918 | p_org_id IN Organization identifier
919 |
920 | MODIFICATION HISTORY
921 | Date Author Description of Changes
922 | 26-Aug-2002 J Rautiainen Created
923 |
924 *=======================================================================*/
925 FUNCTION get_flex_structure_code(p_org_id IN NUMBER) RETURN VARCHAR2 IS
926
927 CURSOR structure_cur IS
928 select fs.id_flex_structure_code
929 from ap_system_parameters_all so, gl_sets_of_books sb, FND_ID_FLEX_STRUCTURES fs
930 where so.org_id = p_org_id
931 and sb.set_of_books_id = so.set_of_books_id
932 and application_id = 101
933 and id_flex_code = 'GL#'
934 and id_flex_num = sb.chart_of_accounts_id;
935
936 structure_rec structure_cur%ROWTYPE;
937
938 BEGIN
939 IF p_org_id is null THEN
940 return null;
941 END IF;
942
943 OPEN structure_cur;
944 FETCH structure_cur INTO structure_rec;
945
946 IF structure_cur%NOTFOUND THEN
947 CLOSE structure_cur;
948 return null;
949 END IF;
950
951 CLOSE structure_cur;
952 return structure_rec.id_flex_structure_code;
953
954 END get_flex_structure_code;
955
956 /*========================================================================
957 | PUBLIC PROCEDURE set_show_audit_header_flag
958 |
959 | DESCRIPTION
960 | This procedure auto sets the preference controlling whether the header
961 | information is shown or not on the audit page.
962 |
963 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
964 | Called from BC4J.
965 |
966 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
967 |
968 | RETURNS
969 |
970 | PARAMETERS
971 | p_show_header IN Whether the header should be shown
972 |
973 | MODIFICATION HISTORY
974 | Date Author Description of Changes
975 | 30-Aug-2002 J Rautiainen Created
976 |
977 *=======================================================================*/
978 PROCEDURE set_show_audit_header_flag(p_show_header IN VARCHAR2) IS
979 PRAGMA AUTONOMOUS_TRANSACTION;
980
981 CURSOR emp_cur IS
982 select employee_id
983 from fnd_user
984 where user_id = FND_GLOBAL.USER_ID;
985
986 CURSOR pref_cur(p_employee_id IN ap_web_preferences.employee_id%TYPE) IS
987 select employee_id, show_audit_header_flag
988 from ap_web_preferences
989 where employee_id = p_employee_id
990 FOR UPDATE OF show_audit_header_flag NOWAIT;
991
992 pref_rec pref_cur%ROWTYPE;
993 emp_rec emp_cur%ROWTYPE;
994
995 BEGIN
996
997 OPEN emp_cur;
998 FETCH emp_cur INTO emp_rec;
999 CLOSE emp_cur;
1000
1001 IF (emp_rec.employee_id is not null) THEN
1002
1003 OPEN pref_cur(emp_rec.employee_id);
1004 FETCH pref_cur INTO pref_rec;
1005
1006 IF pref_cur%NOTFOUND THEN
1007 INSERT INTO ap_web_preferences(
1008 employee_id,
1009 show_audit_header_flag,
1010 last_update_date,
1011 last_updated_by,
1012 creation_date,
1013 created_by,
1014 last_update_login
1015 ) VALUES (
1016 emp_rec.employee_id,
1017 NVL(p_show_header,'Y'),
1018 sysdate, /* last_update_date */
1019 nvl(fnd_global.user_id, -1), /* last_updated_by*/
1020 sysdate, /* creation_date */
1021 nvl(fnd_global.user_id, -1), /* created_by */
1022 fnd_global.conc_login_id /* last_update_login */
1023 );
1024 CLOSE pref_cur;
1025 ELSE
1026 UPDATE ap_web_preferences
1027 SET show_audit_header_flag = NVL(p_show_header,'Y')
1028 WHERE CURRENT OF pref_cur;
1029 CLOSE pref_cur;
1030 END IF;
1031
1032 COMMIT;
1033
1034 END IF;
1035
1036 END set_show_audit_header_flag;
1037
1038 /*========================================================================
1039 | PUBLIC FUNCTION get_show_audit_header_flag
1040 |
1041 | DESCRIPTION
1042 | This function gets the preference controlling whether the header
1043 | information is shown or not on the audit page.
1044 |
1045 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1046 | Called from BC4J.
1047 |
1048 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1049 |
1050 | RETURNS
1051 | Y / N depending whether the header should be shown
1052 |
1053 | PARAMETERS
1054 |
1055 | MODIFICATION HISTORY
1056 | Date Author Description of Changes
1057 | 30-Aug-2002 J Rautiainen Created
1058 |
1059 *=======================================================================*/
1060 FUNCTION get_show_audit_header_flag RETURN VARCHAR2 IS
1061
1062 CURSOR pref_cur IS
1063 select pref.employee_id, NVL(pref.show_audit_header_flag, 'Y') show_header_flag
1064 from ap_web_preferences pref, fnd_user usr
1065 where usr.user_id = FND_GLOBAL.USER_ID
1066 and pref.employee_id = usr.employee_id;
1067
1068 pref_rec pref_cur%ROWTYPE;
1069
1070 BEGIN
1071
1072 OPEN pref_cur;
1073 FETCH pref_cur INTO pref_rec;
1074 IF pref_cur%NOTFOUND THEN
1075 CLOSE pref_cur;
1076 RETURN 'Y';
1077 END IF;
1078
1079 CLOSE pref_cur;
1080 RETURN pref_rec.show_header_flag;
1081
1082 END get_show_audit_header_flag;
1083
1084 /*========================================================================
1085 | PUBLIC FUNCTION get_rule_set_assignment_exists
1086 |
1087 | DESCRIPTION
1088 | This function checks whether assignments exist for a given audit rule set.
1089 |
1090 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1091 | Called from BC4J.
1092 |
1093 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1094 |
1095 | RETURNS
1096 | Y / N depending whether assignment exists for the rule set
1097 |
1098 | PARAMETERS
1099 | p_rule_set_id IN Rule Set Id
1100 |
1101 | MODIFICATION HISTORY
1102 | Date Author Description of Changes
1103 | 30-Aug-2002 J Rautiainen Created
1104 |
1105 *=======================================================================*/
1106 FUNCTION get_rule_set_assignment_exists(p_rule_set_id IN NUMBER) RETURN VARCHAR2 IS
1107
1108 CURSOR assignment_cur IS
1109 select count(rule_assignment_id) assignment_count
1110 from ap_aud_rule_assignments_all
1111 where rule_set_id = p_rule_set_id;
1112
1113 assignment_rec assignment_cur%ROWTYPE;
1114
1115 BEGIN
1116
1117 IF p_rule_set_id is null THEN
1118 return 'N';
1119 END IF;
1120
1121 OPEN assignment_cur;
1122 FETCH assignment_cur INTO assignment_rec;
1123 CLOSE assignment_cur;
1124
1125 IF (assignment_rec.assignment_count > 0) THEN
1126 return 'Y';
1127 ELSE
1128 return 'N';
1129 END IF;
1130
1131 END get_rule_set_assignment_exists;
1132
1133 /*========================================================================
1134 | PUBLIC FUNCTION get_workload_info
1135 |
1136 | DESCRIPTION
1137 | This function returns the user workload info.
1138 |
1139 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1140 | Called from BC4J.
1141 |
1142 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1143 |
1144 | RETURNS
1145 | User queue info
1146 |
1147 | PARAMETERS
1148 | p_user_id IN User Id
1149 | p_column IN Column from which the data is retrieved
1150 | p_data_type IN Data type of the column from which the data is retrieved.
1151 | Supported values: NUMBER, VARCHAR2, DATE
1152 |
1153 | MODIFICATION HISTORY
1154 | Date Author Description of Changes
1155 | 11-Sep-2002 J Rautiainen Created
1156 |
1157 *=======================================================================*/
1158 FUNCTION get_workload_info(p_user_id IN NUMBER,
1159 p_column IN VARCHAR2,
1160 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
1161
1162 l_query_stmt VARCHAR2(4000);
1163 l_result_number NUMBER;
1164 l_result_varchar VARCHAR2(4000);
1165 l_result_date DATE;
1166
1167 TYPE CurType IS REF CURSOR;
1168 cur CurType;
1169
1170 BEGIN
1171 IF p_user_id is null or p_column is null THEN
1172 return null;
1173 END IF;
1174
1175 IF p_data_type not in ('NUMBER', 'VARCHAR2', 'DATE') THEN
1176 return null;
1177 END IF;
1178
1179 l_query_stmt := 'select '||p_column||' result from AP_AUD_WORKLOADS where auditor_id = :b1 and sysdate between start_date and NVL(end_date,sysdate+1)';
1180
1181 OPEN cur FOR l_query_stmt USING to_char(p_user_id);
1182
1183 IF p_data_type = 'VARCHAR2' THEN
1184 FETCH cur INTO l_result_varchar;
1185 ELSIF p_data_type = 'NUMBER' THEN
1186 FETCH cur INTO l_result_number;
1187 l_result_varchar := to_char(l_result_number);
1188 ELSIF p_data_type = 'DATE' THEN
1189 FETCH cur INTO l_result_date;
1190 l_result_varchar := to_char(l_result_date,'DD-MON-RRRR');
1191 ELSE
1192 CLOSE cur;
1193 return null;
1194 END IF;
1195
1196 CLOSE cur;
1197
1198 return l_result_varchar;
1199 END get_workload_info;
1200
1201 /*========================================================================
1202 | PUBLIC FUNCTION get_user_queue_info
1203 |
1204 | DESCRIPTION
1205 | This function returns the user queue info.
1206 |
1207 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1208 | Called from BC4J.
1209 |
1210 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1211 |
1212 | RETURNS
1213 | User queue info
1214 |
1215 | PARAMETERS
1216 | p_user_id IN User Id
1217 | p_column IN Column from which the data is retrieved
1218 | p_data_type IN Data type of the column from which the data is retrieved.
1219 | Supported values: NUMBER, VARCHAR2, DATE
1220 |
1221 | MODIFICATION HISTORY
1222 | Date Author Description of Changes
1223 | 11-Sep-2002 J Rautiainen Created
1224 |
1225 *=======================================================================*/
1226 FUNCTION get_user_queue_info(p_user_id IN NUMBER,
1227 p_column IN VARCHAR2,
1228 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
1229 BEGIN
1230 return get_object_info(to_char(p_user_id),
1231 p_column,
1232 p_data_type,
1233 'AP_AUD_QUEUE_SUMMARIES_V',
1234 'auditor_id');
1235 END get_user_queue_info;
1236
1237 /*========================================================================
1238 | PUBLIC FUNCTION get_audit_reason
1239 |
1240 | DESCRIPTION
1241 | This function returns the reason(s) why expense report line status
1242 | is audited .
1243 |
1244 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1245 | Called from BC4J.
1246 |
1247 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1248 |
1249 | RETURNS
1250 | Expense report audit reason as VARCHAR2.
1251 |
1252 | PARAMETERS
1253 | p_report_header_id IN Expense report header identifier
1254 |
1255 | MODIFICATION HISTORY
1256 | Date Author Description of Changes
1257 | 22-Aug-2002 J Rautiainen Created
1258 |
1259 *=======================================================================*/
1260 FUNCTION get_audit_reason(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1261
1262 CURSOR audit_cur IS
1263 select audit_reason_code,
1264 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_REASONS',audit_reason_code) audit_reason
1265 from AP_AUD_AUDIT_REASONS
1266 where report_header_id = p_report_header_id
1267 order by audit_reason_id;
1268
1269 audit_rec audit_cur%ROWTYPE;
1270 l_counter NUMBER := 0;
1271 l_result VARCHAR2(4000);
1272
1273 BEGIN
1274 IF p_report_header_id is null THEN
1275 return null;
1276 END IF;
1277
1278 FOR audit_rec IN audit_cur LOOP
1279 l_counter := l_counter + 1;
1280 IF l_counter = 1 THEN
1281 l_result := audit_rec.audit_reason;
1282 ELSE
1283 l_result := l_result || ', '||audit_rec.audit_reason;
1284 END IF;
1285 END LOOP;
1286
1287 return l_result;
1288
1289 END get_audit_reason;
1290
1291 /*========================================================================
1292 | PUBLIC FUNCTION get_person_org_id
1293 |
1294 | DESCRIPTION
1295 | This function returns the organization id associated to a person.
1296 |
1297 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1298 | Called from BC4J.
1299 |
1300 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1301 |
1302 | RETURNS
1303 | Organization Id as NUMBER.
1304 |
1305 | PARAMETERS
1306 | p_person_id IN Person identifier
1307 |
1308 | MODIFICATION HISTORY
1309 | Date Author Description of Changes
1310 | 22-Aug-2002 J Rautiainen Created
1311 |
1312 *=======================================================================*/
1313 FUNCTION get_person_org_id(p_person_id IN NUMBER) RETURN NUMBER IS
1314
1315 /* 2-Oct-2003 J Rautiainen Contingent project changes
1316 * This function is used to fetch organization of a employee, regardless of
1317 * the status of the employee. Eg. auditor might be viewing a expense report
1318 * of a terminated or suspended employee, so this method should still return
1319 * the info on the employee. Also since the contingent worker can enter
1320 * expense reports, the function needs to query also on those.
1321 */
1322 CURSOR person_cur IS
1323 SELECT WF.ORGANIZATION_ID
1324 FROM
1325 (SELECT EMP.ORGANIZATION_ID ORGANIZATION_ID,
1326 EMP.EMPLOYEE_ID PERSON_ID
1327 FROM PER_EMPLOYEES_X EMP
1328 WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)='Y'
1329 AND EMP.EMPLOYEE_ID = p_person_id
1330 UNION ALL
1331 SELECT CWK.ORGANIZATION_ID ORGANIZATION_ID,
1332 CWK.PERSON_ID
1333 FROM PER_CONT_WORKERS_CURRENT_X CWK
1334 WHERE CWK.PERSON_ID = p_person_id) WF;
1335
1336 person_rec person_cur%ROWTYPE;
1337
1338 BEGIN
1339
1340 IF p_person_id is null THEN
1341 return null;
1342 END IF;
1343
1344 OPEN person_cur;
1345 FETCH person_cur INTO person_rec;
1346
1347 IF person_cur%NOTFOUND THEN
1348 CLOSE person_cur;
1349 return null;
1350 END IF;
1351
1352 CLOSE person_cur;
1353 return person_rec.organization_id;
1354
1355 END get_person_org_id;
1356
1357 /*========================================================================
1358 | PUBLIC PROCEDURE set_audit_list_member
1359 |
1360 | DESCRIPTION
1361 | This procedure auto sets the given user to the audit list.
1362 |
1363 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1364 | Called from BC4J.
1365 |
1366 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1367 |
1368 | RETURNS
1369 |
1370 | PARAMETERS
1371 | p_report_header_id IN Expense report Identifier
1372 | p_reason_code IN Reason person is being added to the list
1373 |
1374 | MODIFICATION HISTORY
1375 | Date Author Description of Changes
1376 | 18-Oct-2002 J Rautiainen Created
1377 |
1378 *=======================================================================*/
1379 PROCEDURE set_audit_list_member(p_report_header_id IN NUMBER, p_reason_code IN VARCHAR2) IS
1380
1381 CURSOR report_cur IS
1382 select aerh.employee_id, aerh.org_id
1383 from AP_EXPENSE_REPORT_HEADERS_ALL aerh
1384 where aerh.report_header_id = p_report_header_id;
1385
1386 CURSOR required_cur IS
1387 select count(1) required_count
1388 from AP_EXPENSE_REPORT_LINES_ALL aerl
1389 where aerl.report_header_id = p_report_header_id
1390 and nvl(aerl.receipt_required_flag, 'N') = 'Y';
1391
1392 CURSOR rule_cur (p_org_id IN NUMBER) IS
1393 select rs.audit_term_duration_days
1394 from AP_AUD_RULE_SETS rs,
1395 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1396 where rsa.org_id = p_org_id
1397 and rsa.rule_set_id = rs.rule_set_id
1398 and rs.rule_set_type = 'AUDIT_LIST'
1399 and TRUNC(SYSDATE)
1400 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
1401 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
1402
1403 rule_rec rule_cur%ROWTYPE;
1404 report_rec report_cur%ROWTYPE;
1405 required_rec required_cur%ROWTYPE;
1406 create_record boolean := true;
1407 BEGIN
1408 IF p_report_header_id is not null THEN
1409 OPEN report_cur;
1410 FETCH report_cur INTO report_rec;
1411 CLOSE report_cur;
1412
1413 OPEN rule_cur(report_rec.org_id);
1414 FETCH rule_cur INTO rule_rec;
1415 CLOSE rule_cur;
1416
1417 IF (p_reason_code = 'RECEIPTS_LATE') THEN
1418 OPEN required_cur;
1419 FETCH required_cur INTO required_rec;
1420 CLOSE required_cur;
1421 IF (required_rec.required_count = 0) THEN
1422 create_record := false;
1423 END IF;
1424 END IF;
1425 IF (create_record) THEN
1426 AP_WEB_AUDIT_PROCESS.add_to_audit_list(report_rec.employee_id, rule_rec.audit_term_duration_days, NVL(p_reason_code,'AUDITOR_ADDED'));
1427 END IF;
1428
1429 END IF;
1430 END set_audit_list_member;
1431
1432 /*========================================================================
1433 | PUBLIC FUNCTION get_audit_list_member
1434 |
1435 | DESCRIPTION
1436 | This procedure returns whether user is on audit list.
1437 |
1438 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1439 | Called from BC4J.
1440 |
1441 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1442 |
1443 | RETURNS
1444 | Y / N depending whether user is on audit list
1445 |
1446 | PARAMETERS
1447 |
1448 | MODIFICATION HISTORY
1449 | Date Author Description of Changes
1450 | 18-Oct-2002 J Rautiainen Created
1451 |
1452 *=======================================================================*/
1453 FUNCTION get_audit_list_member(p_employee_id IN NUMBER) RETURN VARCHAR2 IS
1454
1455 CURSOR audit_cur IS
1456 SELECT auto_audit_id
1457 FROM ap_aud_auto_audits
1458 WHERE employee_id = p_employee_id
1459 AND trunc(sysdate) between trunc(start_date) and trunc(NVL(end_date, sysdate));
1460
1461 audit_rec audit_cur%ROWTYPE;
1462 BEGIN
1463 IF p_employee_id is null THEN
1464 return 'N';
1465 END IF;
1466
1467 OPEN audit_cur;
1468 FETCH audit_cur INTO audit_rec;
1469
1470 IF audit_cur%NOTFOUND THEN
1471 CLOSE audit_cur;
1472 return 'N';
1473 END IF;
1474
1475 CLOSE audit_cur;
1476 return 'Y';
1477
1478 END get_audit_list_member;
1479
1480 /*========================================================================
1481 | PUBLIC FUNCTION get_auditor_name
1482 |
1483 | DESCRIPTION
1484 | This function returns the auditor name for a auditor.
1485 |
1486 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1487 | Called from BC4J.
1488 |
1489 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1490 |
1491 | RETURNS
1492 | Auditor name for the given auditor as VARCHAR2.
1493 |
1494 | PARAMETERS
1495 | p_auditor_id IN Auditor identifier
1496 |
1497 | MODIFICATION HISTORY
1498 | Date Author Description of Changes
1499 | 19-Oct-2002 J Rautiainen Created
1500 |
1501 *=======================================================================*/
1502 FUNCTION get_auditor_name(p_auditor_id IN NUMBER) RETURN VARCHAR2 IS
1503
1504 CURSOR auditor_cur IS
1505 select DECODE(usr.user_id,
1506 -1, fnd_message.GET_STRING('SQLAP','OIE_AUD_FALLBACK_AUDITOR'),
1507 NVL(AP_WEB_AUDIT_UTILS.get_employee_info(usr.employee_id,'full_name','VARCHAR2'),
1508 usr.user_name)) auditor_name
1509 from fnd_user usr
1510 where usr.user_id = p_auditor_id;
1511
1512 auditor_rec auditor_cur%ROWTYPE;
1513 BEGIN
1514
1515 IF p_auditor_id is null THEN
1516 return null;
1517 END IF;
1518
1519 OPEN auditor_cur;
1520 FETCH auditor_cur INTO auditor_rec;
1521
1522 IF auditor_cur%NOTFOUND THEN
1523 CLOSE auditor_cur;
1524 return null;
1525 END IF;
1526
1527 CLOSE auditor_cur;
1528 return auditor_rec.auditor_name;
1529
1530 END get_auditor_name;
1531
1532 /*========================================================================
1533 | PUBLIC FUNCTION get_audit_rule_info
1534 |
1535 | DESCRIPTION
1536 | This function returns the audit rule info.
1537 |
1538 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1539 | Called from BC4J.
1540 |
1541 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1542 |
1543 | RETURNS
1544 | Audit rule info
1545 |
1546 | PARAMETERS
1547 | p_org_id IN Organization Id
1548 | p_column IN Column from which the data is retrieved
1549 | p_data_type IN Data type of the column from which the data is retrieved.
1550 | Supported values: NUMBER, VARCHAR2, DATE
1551 |
1552 | MODIFICATION HISTORY
1553 | Date Author Description of Changes
1554 | 02-Oct-2002 J Rautiainen Created
1555 |
1556 *=======================================================================*/
1557 FUNCTION get_audit_rule_info(p_org_id IN NUMBER,
1558 p_rule_type IN VARCHAR2,
1559 p_column IN VARCHAR2,
1560 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
1561
1562 CURSOR rule_cur IS
1563 select rs.rule_set_id
1564 from AP_AUD_RULE_SETS rs,
1565 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
1566 where rsa.org_id = p_org_id
1567 and rsa.rule_set_id = rs.rule_set_id
1568 and rs.rule_set_type = p_rule_type
1569 and TRUNC(SYSDATE)
1570 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
1571 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
1572
1573 rule_rec rule_cur%ROWTYPE;
1574 BEGIN
1575
1576 IF p_org_id is null OR p_rule_type is null THEN
1577 return null;
1578 END IF;
1579
1580 OPEN rule_cur;
1581 FETCH rule_cur INTO rule_rec;
1582
1583 IF rule_cur%NOTFOUND THEN
1584 CLOSE rule_cur;
1585 return null;
1586 END IF;
1587
1588 CLOSE rule_cur;
1589
1590 return get_object_info(to_char(rule_rec.rule_set_id),
1591 p_column,
1592 p_data_type,
1593 'AP_AUD_RULE_SETS',
1594 'rule_set_id');
1595
1596 END get_audit_rule_info;
1597
1598 /*========================================================================
1599 | PUBLIC FUNCTION get_security_profile_info
1600 |
1601 | DESCRIPTION
1602 | This function returns the security profile info.
1603 |
1604 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1605 | Called from BC4J.
1606 |
1607 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1608 |
1609 | RETURNS
1610 | Security Profile info
1611 |
1612 | PARAMETERS
1613 | p_security_profile_id IN Security Profile Id
1614 | p_column IN Column from which the data is retrieved
1615 | p_data_type IN Data type of the column from which the data is retrieved.
1616 | Supported values: NUMBER, VARCHAR2, DATE
1617 |
1618 | MODIFICATION HISTORY
1619 | Date Author Description of Changes
1620 | 02-Oct-2002 J Rautiainen Created
1621 |
1622 *=======================================================================*/
1623 FUNCTION get_security_profile_info(p_security_profile_id IN NUMBER,
1624 p_column IN VARCHAR2,
1625 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
1626 BEGIN
1627
1628 return get_object_info(to_char(p_security_profile_id),
1629 p_column,
1630 p_data_type,
1631 'per_security_profiles',
1632 'security_profile_id');
1633
1634 END get_security_profile_info;
1635
1636 /*========================================================================
1637 | PUBLIC FUNCTION get_security_profile_org_list
1638 |
1639 | DESCRIPTION
1640 | This function returns the security profile organization list.
1641 |
1642 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1643 | Called from BC4J.
1644 |
1645 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1646 |
1647 | RETURNS
1648 | Security Profile organization list
1649 |
1650 | PARAMETERS
1651 | p_security_profile_id IN Security Profile Id
1652 |
1653 | MODIFICATION HISTORY
1654 | Date Author Description of Changes
1655 | 02-Oct-2002 J Rautiainen Created
1656 |
1657 *=======================================================================*/
1658 FUNCTION get_security_profile_org_list(p_security_profile_id IN NUMBER) RETURN VARCHAR2 IS
1659
1660 CURSOR org_cur IS
1661 SELECT hou.name org_name
1662 FROM per_organization_list per,
1663 HR_ORGANIZATION_UNITS hou
1664 WHERE per.organization_id = hou.organization_id
1665 AND per.security_profile_id = p_security_profile_id;
1666
1667 l_counter NUMBER := 0;
1668 l_result VARCHAR2(4000);
1669
1670 BEGIN
1671
1672 IF p_security_profile_id is null THEN
1673 return null;
1674 END IF;
1675
1676 FOR org_rec IN org_cur LOOP
1677 l_counter := l_counter + 1;
1678 IF l_counter = 1 THEN
1679 l_result := org_rec.org_name;
1680 ELSIF ((length(l_result)+length(org_rec.org_name))<3995) THEN
1681 l_result := l_result || ', '||org_rec.org_name;
1682 ELSE
1683 l_result := l_result || '...';
1684 EXIT;
1685 END IF;
1686 END LOOP;
1687
1688 return l_result;
1689
1690 END get_security_profile_org_list;
1691
1692 /*========================================================================
1693 | PUBLIC FUNCTION get_default_security_profile
1694 |
1695 | DESCRIPTION
1696 | This function returns the default security profile for user.
1697 |
1698 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1699 | Called from BC4J.
1700 |
1701 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1702 |
1703 | RETURNS
1704 | Default Security Profile
1705 |
1706 | PARAMETERS
1707 | p_user_id IN User Id
1708 |
1709 | MODIFICATION HISTORY
1710 | Date Author Description of Changes
1711 | 02-Oct-2002 J Rautiainen Created
1712 |
1713 *=======================================================================*/
1714 FUNCTION get_default_security_profile(p_user_id IN NUMBER) RETURN NUMBER IS
1715
1716 CURSOR profile_cur IS
1717 SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', u.user_id, r.responsibility_id, 200/*SQLAP*/) security_profile_id
1718 FROM FND_USER u,
1719 FND_USER_RESP_GROUPS g,
1720 FND_RESPONSIBILITY r,
1721 FND_FORM_FUNCTIONS f
1722 WHERE u.user_id = g.user_id
1723 AND u.user_id = p_user_id
1724 AND g.responsibility_id = r.responsibility_id
1725 AND AP_WEB_AUDIT_QUEUE_UTILS.IS_FUNCTION_ON_MENU(r.menu_id, f.function_id, 'Y') = 'Y'
1726 AND f.function_name = 'OIE_AUD_AUDIT';
1727
1728 profile_rec profile_cur%ROWTYPE;
1729
1730 BEGIN
1731
1732 IF p_user_id is null THEN
1733 return to_number(null);
1734 END IF;
1735
1736 OPEN profile_cur;
1737 FETCH profile_cur INTO profile_rec;
1738
1739 IF profile_cur%NOTFOUND THEN
1740 CLOSE profile_cur;
1741 return to_number(null);
1742 END IF;
1743
1744 IF profile_cur%ROWCOUNT > 1 THEN
1745 CLOSE profile_cur;
1746 return to_number(null);
1747 END IF;
1748
1749 CLOSE profile_cur;
1750 return profile_rec.security_profile_id;
1751
1752 END get_default_security_profile;
1753
1754 /*========================================================================
1755 | PUBLIC FUNCTION get_advance_exists
1756 |
1757 | DESCRIPTION
1758 | This function returns whether advance exists for a given employee.
1759 |
1760 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1761 | Called from BC4J.
1762 |
1763 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1764 |
1765 | RETURNS
1766 | Y / N whether advance exists
1767 |
1768 | PARAMETERS
1769 | p_report_header_id IN Expense report header Id
1770 |
1771 | MODIFICATION HISTORY
1772 | Date Author Description of Changes
1773 | 15-Oct-2002 J Rautiainen Created
1774 |
1775 *=======================================================================*/
1776 FUNCTION get_advance_exists(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
1777
1778 CURSOR site_cur IS
1779 SELECT site.invoice_currency_code default_currency_code
1780 FROM ap_suppliers vdr,
1781 ap_supplier_sites_all site,
1782 ap_expense_report_headers_all aerh,
1783 financials_system_params_all fp
1784 WHERE aerh.report_header_id = p_report_header_id
1785 AND aerh.org_id = site.org_id
1786 AND aerh.org_id = fp.org_id
1787 AND site.vendor_id = vdr.vendor_id
1788 AND vdr.employee_id = aerh.employee_id
1789 AND upper(site.vendor_site_code) = UPPER(AP_WEB_POLICY_UTILS.get_lookup_meaning('HOME_OFFICE', fp.expense_check_address_flag));
1790
1791 CURSOR vendor_cur IS
1792 SELECT nvl(vdr.invoice_currency_code,sp.base_currency_code) default_currency_code
1793 FROM ap_suppliers vdr,
1794 ap_expense_report_headers_all aerh,
1795 ap_system_parameters_all sp
1796 WHERE vdr.employee_id = aerh.employee_id
1797 AND aerh.report_header_id = p_report_header_id
1798 AND aerh.org_id = sp.org_id;
1799
1800 CURSOR advance_cur(p_default_currency_code IN VARCHAR2) IS
1801 SELECT get_available_prepays(vdr.vendor_id) advance_count,
1802 aerh.employee_id
1803 FROM ap_invoices_all i,
1804 ap_expense_report_headers_all aerh,
1805 ap_suppliers vdr
1806 WHERE i.vendor_id = vdr.vendor_id
1807 AND aerh.report_header_id = p_report_header_id
1808 AND vdr.employee_id = aerh.employee_id
1809 AND i.org_id = aerh.org_id
1810 AND i.invoice_type_lookup_code||'' = 'PREPAYMENT'
1811 AND i.earliest_settlement_date IS NOT NULL
1812 AND i.invoice_currency_code = p_default_currency_code
1813 AND ROWNUM = 1;
1814
1815 CURSOR applied_cur(p_employee_id NUMBER, p_default_currency_code IN VARCHAR2) IS
1816 SELECT nvl(sum(maximum_amount_to_apply),0) applied_prepayment
1817 FROM ap_expense_report_headers_all aerh
1818 WHERE aerh.employee_id = p_employee_id
1819 AND aerh.vouchno = 0
1820 AND aerh.default_currency_code = p_default_currency_code
1821 GROUP BY aerh.employee_id;
1822
1823 CURSOR remaining_cur(p_employee_id NUMBER, p_default_currency_code IN VARCHAR2) IS
1824 SELECT nvl(sum( get_prepay_amount_remaining(i.invoice_id) ), 0) remaining_prepayment
1825 FROM ap_invoices_all i, ap_suppliers vdr
1826 WHERE i.vendor_id = vdr.vendor_id
1827 AND vdr.employee_id = p_employee_id
1828 AND i.invoice_type_lookup_code||'' = 'PREPAYMENT'
1829 AND i.earliest_settlement_date IS NOT NULL
1830 AND i.payment_status_flag||'' = 'Y'
1831 AND i.invoice_currency_code = p_default_currency_code
1832 GROUP BY vdr.employee_id;
1833
1834 site_rec site_cur%ROWTYPE;
1835 vendor_rec vendor_cur%ROWTYPE;
1836 advance_rec advance_cur%ROWTYPE;
1837 applied_rec applied_cur%ROWTYPE;
1838 remaining_rec remaining_cur%ROWTYPE;
1839 lv_default_currency_code VARCHAR2(15) := NULL;
1840
1841 BEGIN
1842
1843 IF p_report_header_id is null THEN
1844 return 'N';
1845 END IF;
1846
1847 OPEN site_cur;
1848 FETCH site_cur INTO site_rec;
1849
1850 IF site_cur%FOUND THEN
1851 CLOSE site_cur;
1852 lv_default_currency_code := site_rec.default_currency_code;
1853 ELSE
1854 CLOSE site_cur;
1855
1856 OPEN vendor_cur;
1857 FETCH vendor_cur INTO vendor_rec;
1858 CLOSE vendor_cur;
1859
1860 lv_default_currency_code := vendor_rec.default_currency_code;
1861 END IF;
1862
1863 IF lv_default_currency_code IS NULL THEN
1864 RETURN 'N';
1865 ELSE
1866
1867 OPEN advance_cur(lv_default_currency_code);
1868 FETCH advance_cur INTO advance_rec;
1869 CLOSE advance_cur;
1870
1871 IF (advance_rec.advance_count > 0) THEN
1872 OPEN applied_cur(advance_rec.employee_id, lv_default_currency_code);
1873 FETCH applied_cur INTO applied_rec;
1874 CLOSE applied_cur;
1875
1876 OPEN remaining_cur(advance_rec.employee_id, lv_default_currency_code);
1877 FETCH remaining_cur INTO remaining_rec;
1878 CLOSE remaining_cur;
1879
1880 IF (remaining_rec.remaining_prepayment > applied_rec.applied_prepayment) THEN
1881 RETURN 'Y';
1882 ELSE
1883 RETURN 'N';
1884 END IF;
1885 ELSE
1886 RETURN 'N';
1887 END IF;
1888
1889 END IF;
1890
1891 END get_advance_exists;
1892
1893 /*========================================================================
1894 | PUBLIC PROCEDURE is_gl_date_valid
1895 |
1896 | DESCRIPTION
1897 | This procedure returns whether give date is a valid GL date in AP periods.
1898 | Note this is not generic validation, since we have specific requirement
1899 | of only "Never Opened" and null to be flagged as invalid.
1900 |
1901 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1902 | Called from BC4J.
1903 |
1904 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1905 |
1906 | RETURNS
1907 | p_date_valid IN Y / N
1908 | p_default_date IN if given date is invalid, try to find default GL date
1909 |
1910 | PARAMETERS
1911 | p_gl_date IN GL Date
1912 | p_set_of_books_id IN Set of books identifier
1913 |
1914 | MODIFICATION HISTORY
1915 | Date Author Description of Changes
1916 | 16-Oct-2002 J Rautiainen Created
1917 |
1918 *=======================================================================*/
1919 PROCEDURE is_gl_date_valid(p_gl_date IN DATE,
1920 p_set_of_books_id IN NUMBER,
1921 p_date_valid OUT NOCOPY VARCHAR2,
1922 p_default_date OUT NOCOPY DATE) IS
1923
1924 CURSOR period_cur IS
1925 SELECT closing_status
1926 FROM gl_period_statuses_v
1927 WHERE application_id = 200
1928 and set_of_books_id = p_set_of_books_id
1929 and adjustment_period_flag = 'N'
1930 and p_gl_date between start_date and end_date;
1931
1932 CURSOR default_cur IS
1933 SELECT max(end_date) default_date
1934 FROM gl_period_statuses_v
1935 WHERE application_id = 200
1936 and set_of_books_id = p_set_of_books_id
1937 and adjustment_period_flag = 'N'
1938 and start_date < p_gl_date
1939 and closing_status in ('O', 'F');
1940
1941 period_rec period_cur%ROWTYPE;
1942 default_rec default_cur%ROWTYPE;
1943
1944 BEGIN
1945
1946 IF p_gl_date is null OR p_set_of_books_id is null THEN
1947 p_date_valid := 'N';
1948 p_default_date := to_date(null);
1949
1950 ELSE
1951 OPEN period_cur;
1952 FETCH period_cur INTO period_rec;
1953
1954 IF period_cur%NOTFOUND OR period_rec.closing_status = 'N' THEN
1955 p_date_valid := 'N';
1956 ELSE
1957 p_date_valid := 'Y';
1958 END IF;
1959
1960 CLOSE period_cur;
1961
1962 IF p_date_valid = 'N' THEN
1963 OPEN default_cur;
1964 FETCH default_cur INTO default_rec;
1965
1966 IF default_cur%FOUND THEN
1967 p_default_date := default_rec.default_date;
1968 END IF;
1969
1970 CLOSE default_cur;
1971 END IF;
1972
1973 END IF;
1974 END is_gl_date_valid;
1975
1976 /*========================================================================
1977 | PUBLIC FUNCTION get_expense_item_info
1978 |
1979 | DESCRIPTION
1980 | This function returns the expense item info.
1981 |
1982 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1983 | Called from BC4J.
1984 |
1985 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1986 |
1987 | RETURNS
1988 | Expense item info
1989 |
1990 | PARAMETERS
1991 | p_parameter_id IN Expense item Id
1992 | p_column IN Column from which the data is retrieved
1993 | p_data_type IN Data type of the column from which the data is retrieved.
1994 | Supported values: NUMBER, VARCHAR2, DATE
1995 |
1996 | MODIFICATION HISTORY
1997 | Date Author Description of Changes
1998 | 16-Sep-2002 J Rautiainen Created
1999 |
2000 *=======================================================================*/
2001 FUNCTION get_expense_item_info(p_parameter_id IN NUMBER,
2002 p_column IN VARCHAR2,
2003 p_data_type IN VARCHAR2) RETURN VARCHAR2 IS
2004 BEGIN
2005 return get_object_info(to_char(p_parameter_id),
2006 p_column,
2007 p_data_type,
2008 'AP_EXPENSE_REPORT_PARAMS_ALL',
2009 'parameter_id');
2010 END get_expense_item_info;
2011
2012 /*==================== ====================================================
2013 | PUBLIC FUNCTION is_employee_active
2014 |
2015 | DESCRIPTION
2016 | This function returns whether the employee is active for a given org.
2017 |
2018 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2019 | Called from BC4J.
2020 |
2021 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2022 |
2023 | RETURNS
2024 | Y / N depending whether employee is active
2025 |
2026 | PARAMETERS
2027 | p_employee_id IN Employee identifier
2028 | p_org_id IN Organization Id
2029 |
2030 | MODIFICATION HISTORY
2031 | Date Author Description of Changes
2032 | 20-Nov-2002 J Rautiainen Created
2033 |
2034 *=======================================================================*/
2035 FUNCTION is_employee_active(p_employee_id IN NUMBER,
2036 p_org_id IN NUMBER) RETURN VARCHAR2 IS
2037
2038 BEGIN
2039
2040 RETURN AP_WEB_DB_HR_INT_PKG.IsPersonActive(p_employee_id);
2041
2042 EXCEPTION
2043 WHEN OTHERS THEN
2044 /* Even when exception is thrown we do not want to propagate it upwards
2045 * since this method is used in SQL queries. */
2046 RETURN 'N';
2047
2048 END is_employee_active;
2049
2050 /*========================================================================
2051 | PUBLIC FUNCTION is_personal_expense
2052 |
2053 | DESCRIPTION
2054 | This function returns whether given expense is a personal expense or not.
2055 |
2056 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2057 | Called from BC4J.
2058 |
2059 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2060 |
2061 | RETURNS
2062 | Y / N Depending whether expense is personal or not
2063 |
2064 | PARAMETERS
2065 | p_parameter_id IN Expense type to check for personal expense
2066 |
2067 | MODIFICATION HISTORY
2068 | Date Author Description of Changes
2069 | 04-Feb-2003 J Rautiainen Created
2070 |
2071 *=======================================================================*/
2072 FUNCTION is_personal_expense(p_parameter_id IN NUMBER) RETURN VARCHAR2 IS
2073 ln_personal_expense_id NUMBER;
2074 BEGIN
2075
2076 IF p_parameter_id is null THEN
2077 return 'N';
2078 ELSE
2079 /**
2080 * Get the expense type parameter id for personal expense.
2081 */
2082 ln_personal_expense_id := get_personal_expense_id();
2083
2084 IF p_parameter_id = ln_personal_expense_id THEN
2085 return 'Y';
2086 ELSE
2087 return 'N';
2088 END IF;
2089 END IF;
2090
2091 return 'N';
2092
2093 END is_personal_expense;
2094
2095 /**
2096 * jrautiai ADJ Fix start
2097 */
2098
2099 /*========================================================================
2100 | PUBLIC FUNCTION is_rounding_line
2101 |
2102 | DESCRIPTION
2103 | This function returns whether given line is a rounding line or not.
2104 |
2105 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2106 | Called from BC4J.
2107 |
2108 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2109 |
2110 | RETURNS
2111 | Y / N Depending whether line is a rounding line.
2112 |
2113 | PARAMETERS
2114 | p_parameter_id IN Expense type to check for rounding
2115 |
2116 | MODIFICATION HISTORY
2117 | Date Author Description of Changes
2118 | 04-Feb-2003 J Rautiainen Created
2119 |
2120 *=======================================================================*/
2121 FUNCTION is_rounding_line(p_parameter_id IN NUMBER) RETURN VARCHAR2 IS
2122 ln_rounding_expense_id NUMBER;
2123 BEGIN
2124
2125 IF p_parameter_id is null THEN
2126 return 'N';
2127 ELSE
2128 /**
2129 * Get the expense type parameter id for rounding.
2130 */
2131 ln_rounding_expense_id := AP_WEB_AUDIT_UTILS.get_seeded_expense_id('ROUNDING');
2132
2133 IF p_parameter_id = ln_rounding_expense_id THEN
2134 return 'Y';
2135 ELSE
2136 return 'N';
2137 END IF;
2138 END IF;
2139
2140 return 'N';
2141
2142 END is_rounding_line;
2143
2144 /*========================================================================
2145 | PUBLIC FUNCTION get_personal_expense_id
2146 |
2147 | DESCRIPTION
2148 | This function returns personal expense parameter id.
2149 |
2150 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2151 | process_audit_actions
2152 |
2153 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2154 |
2155 | RETURNS
2156 | Expense type parameter if for a personal expense
2157 |
2158 | PARAMETERS
2159 | None
2160 | MODIFICATION HISTORY
2161 | Date Author Description of Changes
2162 | 21-Jul-2003 J Rautiainen Created
2163 |
2164 *=======================================================================*/
2165 FUNCTION get_personal_expense_id RETURN NUMBER IS
2166
2167 BEGIN
2168
2169 RETURN get_seeded_expense_id('PERSONAL');
2170
2171 END get_personal_expense_id;
2172
2173 /*========================================================================
2174 | PUBLIC FUNCTION is_cc_expense_adjusted
2175 |
2176 | DESCRIPTION
2177 | This function returns whether CC expense has been adjusted.
2178 |
2179 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2180 | Called from BC4J.
2181 |
2182 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2183 |
2184 | RETURNS
2185 | Y / N Depending whether CC expense has been adjusted
2186 |
2187 | PARAMETERS
2188 | p_report_header_id IN Expense report to be checked
2189 |
2190 | MODIFICATION HISTORY
2191 | Date Author Description of Changes
2192 | 15-Jul-2003 J Rautiainen Created
2193 |
2194 *=======================================================================*/
2195 FUNCTION is_cc_expense_adjusted(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
2196
2197 CURSOR cc_cur IS
2198 SELECT erl.report_header_id
2199 FROM ap_expense_report_lines_all erl
2200 WHERE erl.report_header_id = p_report_header_id
2201 AND erl.credit_card_trx_id is not null
2202 AND erl.amount <> NVL(erl.submitted_amount,erl.amount);
2203
2204 cc_rec cc_cur%ROWTYPE;
2205
2206 BEGIN
2207
2208 IF p_report_header_id is null THEN
2209 return 'N';
2210 ELSE
2211 OPEN cc_cur;
2212 FETCH cc_cur INTO cc_rec;
2213 IF cc_cur%FOUND THEN
2214 CLOSE cc_cur;
2215 return 'Y';
2216 ELSE
2217 CLOSE cc_cur;
2218 return 'N';
2219 END IF;
2220 END IF;
2221
2222 return 'N';
2223
2224 END is_cc_expense_adjusted;
2225
2226 /*========================================================================
2227 | PUBLIC FUNCTION is_itemized_expense_shortpaid
2228 |
2229 | DESCRIPTION
2230 | This function returns whether itemized expense has been shortpaid.
2231 |
2232 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2233 | Called from BC4J.
2234 |
2235 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2236 |
2237 | RETURNS
2238 | Y / N Depending whether itemized expense has been shortpaid
2239 |
2240 | PARAMETERS
2241 | p_report_header_id IN Expense report to be checked
2242 |
2243 | MODIFICATION HISTORY
2244 | Date Author Description of Changes
2245 | 15-Jul-2003 J Rautiainen Created
2246 |
2247 *=======================================================================*/
2248 FUNCTION is_itemized_expense_shortpaid(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
2249
2250 CURSOR itemized_cur IS
2251 SELECT erl.report_header_id
2252 FROM ap_expense_report_lines_all erl
2253 WHERE erl.report_header_id = p_report_header_id
2254 AND erl.itemization_parent_id is not null
2255 AND erl.itemization_parent_id <> -1
2256 AND NVL(erl.policy_shortpay_flag,'N') = 'Y';
2257
2258 itemized_rec itemized_cur%ROWTYPE;
2259
2260 BEGIN
2261
2262 IF p_report_header_id is null THEN
2263 return 'N';
2264 ELSE
2265 OPEN itemized_cur;
2266 FETCH itemized_cur INTO itemized_rec;
2267 IF itemized_cur%FOUND THEN
2268 CLOSE itemized_cur;
2269 return 'Y';
2270 ELSE
2271 CLOSE itemized_cur;
2272 return 'N';
2273 END IF;
2274 END IF;
2275
2276 return 'N';
2277
2278 END is_itemized_expense_shortpaid;
2279
2280
2281 /*========================================================================
2282 | PUBLIC FUNCTION get_expense_clearing_ccid
2283 |
2284 | DESCRIPTION
2285 | This function returns the expense clearing account for a given transaction.
2286 |
2287 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2288 | process_audit_actions
2289 |
2290 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2291 |
2292 | RETURNS
2293 | Expense clearing account for a given transaction
2294 |
2295 | PARAMETERS
2296 | Transaction id to fetch the expense clearing account for.
2297 |
2298 | MODIFICATION HISTORY
2299 | Date Author Description of Changes
2300 | 21-Jul-2003 J Rautiainen Created
2301 |
2302 *=======================================================================*/
2303 FUNCTION get_expense_clearing_ccid(p_trx_id IN NUMBER) RETURN NUMBER IS
2304
2305 CURSOR program_c IS
2306 SELECT cp.org_id, cp.expense_clearing_ccid
2307 FROM ap_card_programs_all cp,
2308 ap_credit_card_trxns_all cct
2309 WHERE cp.card_program_id = cct.card_program_id
2310 AND cct.trx_id = p_trx_id;
2311
2312 CURSOR ccid_c(p_org_id IN NUMBER) IS
2313 SELECT EXPENSE_CLEARING_CCID
2314 FROM FINANCIALS_SYSTEM_PARAMS_ALL
2315 WHERE org_id = p_org_id;
2316
2317 ln_ccid NUMBER;
2318 ln_org_id NUMBER;
2319 BEGIN
2320 IF p_trx_id IS NULL THEN
2321 RETURN -1;
2322 END IF;
2323
2324 OPEN program_c;
2325 FETCH program_c into ln_org_id, ln_ccid;
2326 CLOSE program_c;
2327
2328 IF (ln_ccid IS NULL) THEN
2329 OPEN ccid_c(ln_org_id);
2330 FETCH ccid_c into ln_ccid;
2331 CLOSE ccid_c;
2332 END IF;
2333
2334 IF (ln_ccid IS NULL) THEN
2335 RETURN -1;
2336 ELSE
2337 RETURN ln_ccid;
2338 END IF;
2339
2340 END get_expense_clearing_ccid;
2341
2342 /*========================================================================
2343 | PUBLIC FUNCTION get_payment_due_from
2344 |
2345 | DESCRIPTION
2346 | This function returns the payment due from for a given transaction.
2347 | If the payment due from column is not populated on the cc transaction,
2348 | then the value of the related profile option is returned.
2349 |
2350 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2351 | process_audit_actions
2352 |
2353 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2354 |
2355 | RETURNS
2356 | Payment due from for a given transaction
2357 |
2358 | PARAMETERS
2359 | Transaction id for which to fetch the payment due from.
2360 |
2361 | MODIFICATION HISTORY
2362 | Date Author Description of Changes
2363 | 21-Jul-2003 J Rautiainen Created
2364 |
2365 *=======================================================================*/
2366 FUNCTION get_payment_due_from(p_trx_id IN NUMBER) RETURN VARCHAR2 IS
2367 CURSOR trx_c IS
2368 SELECT cct.payment_due_from_code
2369 FROM ap_credit_card_trxns_all cct
2370 WHERE cct.trx_id = p_trx_id;
2371
2372 lv_payment_due_from_prof VARCHAR2(100);
2373 lv_payment_due_from VARCHAR2(100);
2374
2375 BEGIN
2376 lv_payment_due_from_prof := FND_PROFILE.VALUE('SSE_CC_PAYMENT_DUE_FROM');
2377
2378 IF p_trx_id IS NULL THEN
2379 RETURN lv_payment_due_from_prof;
2380 END IF;
2381
2382 OPEN trx_c;
2383 FETCH trx_c into lv_payment_due_from;
2384 CLOSE trx_c;
2385
2386 RETURN NVL(lv_payment_due_from,lv_payment_due_from_prof);
2387 END get_payment_due_from;
2388
2389 /*========================================================================
2390 | PUBLIC FUNCTION get_seeded_expense_id
2391 |
2392 | DESCRIPTION
2393 | This function returns a seeded expense type id. It is used to get the ID
2394 | for personal and rounding expense types.
2395 |
2396 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2397 |
2398 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2399 |
2400 | RETURNS
2401 | Expense type parameter id for the seeded expense
2402 |
2403 | PARAMETERS
2404 | Expense type code for the seeded expense type.
2405 | MODIFICATION HISTORY
2406 | Date Author Description of Changes
2407 | 21-Jul-2003 J Rautiainen Created
2408 |
2409 *=======================================================================*/
2410 FUNCTION get_seeded_expense_id(p_expense_type_code IN VARCHAR2) RETURN NUMBER IS
2411
2412 CURSOR seeded_expense_cur IS
2413 SELECT parameter_id
2414 FROM ap_expense_report_params erp
2415 WHERE erp.expense_type_code = p_expense_type_code;
2416
2417 seeded_expense_rec seeded_expense_cur%ROWTYPE;
2418
2419 BEGIN
2420 IF (p_expense_type_code = 'PERSONAL' AND pg_personal_parameter_id is null)
2421 OR (p_expense_type_code = 'ROUNDING' AND pg_rounding_parameter_id is null) THEN
2422 OPEN seeded_expense_cur;
2423 FETCH seeded_expense_cur INTO seeded_expense_rec;
2424 CLOSE seeded_expense_cur;
2425
2426 IF p_expense_type_code = 'PERSONAL' THEN
2427 pg_personal_parameter_id := seeded_expense_rec.parameter_id;
2428 ELSIF p_expense_type_code = 'ROUNDING' THEN
2429 pg_rounding_parameter_id := seeded_expense_rec.parameter_id;
2430 END IF;
2431 END IF;
2432
2433 IF p_expense_type_code = 'PERSONAL' THEN
2434 RETURN pg_personal_parameter_id;
2435 ELSIF p_expense_type_code = 'ROUNDING' THEN
2436 RETURN pg_rounding_parameter_id;
2437 ELSE
2438 RETURN to_number(null);
2439 END IF;
2440
2441 END get_seeded_expense_id;
2442
2443
2444 /*========================================================================
2445 | PUBLIC FUNCTION get_next_distribution_line_id
2446 |
2447 | DESCRIPTION
2448 | This function returns a the next distribution number for the report.
2449 |
2450 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2451 |
2452 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2453 |
2454 | RETURNS
2455 | The next distribution line number for the expense report.
2456 |
2457 | PARAMETERS
2458 | Expense report identifier.
2459 | MODIFICATION HISTORY
2460 | Date Author Description of Changes
2461 | 21-Jul-2003 J Rautiainen Created
2462 |
2463 *=======================================================================*/
2464 FUNCTION get_next_distribution_line_id(p_report_header_id IN NUMBER) RETURN NUMBER IS
2465
2466 CURSOR next_distribution_line_c(p_report_header_id NUMBER) IS
2467 SELECT max(distribution_line_number) + 1
2468 FROM AP_EXPENSE_REPORT_LINES_ALL
2469 WHERE report_header_id = p_report_header_id;
2470
2471 ln_next_dist_line_number NUMBER;
2472
2473 BEGIN
2474 IF p_report_header_id IS NULL THEN
2475 RETURN to_number(NULL);
2476 ELSE
2477 OPEN next_distribution_line_c(p_report_header_id);
2478 FETCH next_distribution_line_c into ln_next_dist_line_number;
2479 CLOSE next_distribution_line_c;
2480
2481 RETURN ln_next_dist_line_number;
2482 END IF;
2483
2484 END get_next_distribution_line_id;
2485
2486 /*========================================================================
2487 | PUBLIC FUNCTION get_rounding_error_ccid
2488 |
2489 | DESCRIPTION
2490 | This function returns the rounding error account for a given org.
2491 |
2492 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2493 | process_audit_actions
2494 |
2495 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2496 |
2497 | RETURNS
2498 | Rounding error account for a given org
2499 |
2500 | PARAMETERS
2501 | Organization id to fetch the rounding error account for.
2502 |
2503 | MODIFICATION HISTORY
2504 | Date Author Description of Changes
2505 | 21-Jul-2003 J Rautiainen Created
2506 |
2507 *=======================================================================*/
2508 FUNCTION get_rounding_error_ccid(p_org_id IN NUMBER) RETURN NUMBER IS
2509
2510 CURSOR ccid_c IS
2511 SELECT ROUNDING_ERROR_CCID
2512 FROM ap_system_parameters_all
2513 WHERE org_id = p_org_id;
2514
2515 ln_ccid NUMBER;
2516 BEGIN
2517 IF p_org_id IS NULL THEN
2518 RETURN -1;
2519 END IF;
2520
2521 OPEN ccid_c;
2522 FETCH ccid_c into ln_ccid;
2523 CLOSE ccid_c;
2524
2525 IF (ln_ccid IS NULL) THEN
2526 RETURN -1;
2527 ELSE
2528 RETURN ln_ccid;
2529 END IF;
2530
2531 END get_rounding_error_ccid;
2532
2533 /**
2534 * jrautiai ADJ Fix end
2535 */
2536
2537 /*========================================================================
2538 | PUBLIC FUNCTION get_user_name
2539 |
2540 | DESCRIPTION
2541 | This function returns the name for a given FND user.
2542 |
2543 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2544 |
2545 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2546 |
2547 | RETURNS
2548 | Name for a given FND user
2549 |
2550 | PARAMETERS
2551 | FND user ID.
2552 |
2553 | MODIFICATION HISTORY
2554 | Date Author Description of Changes
2555 | 18-Aug-2003 J Rautiainen Created
2556 |
2557 *=======================================================================*/
2558 FUNCTION get_user_name(p_user_id IN NUMBER) RETURN VARCHAR2 IS
2559
2560 CURSOR user_c IS
2561 SELECT DECODE(usr.employee_id,
2562 null, usr.user_name,
2563 AP_WEB_AUDIT_UTILS.get_employee_info(usr.employee_id,'full_name','VARCHAR2')) last_audited_by_name
2564 FROM fnd_user usr
2565 WHERE usr.user_id = p_user_id;
2566
2567 user_rec user_c%ROWTYPE;
2568 BEGIN
2569 IF p_user_id IS NULL THEN
2570 return null;
2571 END IF;
2572
2573 OPEN user_c;
2574 FETCH user_c into user_rec;
2575 CLOSE user_c;
2576
2577 RETURN user_rec.last_audited_by_name;
2578
2579 END get_user_name;
2580
2581 /*========================================================================
2582 | PUBLIC FUNCTION get_lookup_description
2583 |
2584 | DESCRIPTION
2585 | This function returns the description of a lookup code.
2586 |
2587 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2588 |
2589 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2590 |
2591 | RETURNS
2592 | Description of a lookup code
2593 |
2594 | PARAMETERS
2595 | Lookup type and lookup code
2596 |
2597 | MODIFICATION HISTORY
2598 | Date Author Description of Changes
2599 | 24-Oct-2003 J Rautiainen Created
2600 |
2601 *=======================================================================*/
2602 FUNCTION get_lookup_description(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
2603 l_meaning fnd_lookup_values_vl.description%TYPE;
2604 BEGIN
2605
2606 IF p_lookup_code IS NOT NULL AND
2607 p_lookup_type IS NOT NULL THEN
2608
2609 SELECT description
2610 INTO l_meaning
2611 FROM fnd_lookup_values_vl
2612 WHERE lookup_type = p_lookup_type
2613 AND lookup_code = p_lookup_code;
2614
2615 return l_meaning;
2616 END IF;
2617
2618 return to_char(null);
2619
2620 EXCEPTION
2621 WHEN no_data_found THEN
2622 return(null);
2623 WHEN OTHERS THEN
2624 raise;
2625 END get_lookup_description;
2626
2627 /*========================================================================
2628 | PUBLIC FUNCTION get_non_project_ccid
2629 |
2630 | DESCRIPTION
2631 | This function returns a CCID with segments overridden by the expense
2632 | type definitions.
2633 |
2634 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2635 |
2636 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2637 |
2638 | RETURNS
2639 | CCID with segments overridden by the expense type definitions
2640 |
2641 | PARAMETERS
2642 |
2643 | MODIFICATION HISTORY
2644 | Date Author Description of Changes
2645 | 07-Nov-2003 J Rautiainen Created
2646 |
2647 *=======================================================================*/
2648 FUNCTION get_non_project_ccid(p_report_header_id IN NUMBER,
2649 p_report_distribution_id IN NUMBER,
2650 p_parameter_id IN NUMBER,
2651 p_ccid IN NUMBER) RETURN NUMBER IS
2652
2653 l_new_segments AP_OIE_KFF_SEGMENTS_T;
2654 l_new_ccid NUMBER := to_number(null);
2655 -- Bug: 7039477, sync error message length with fnd_flex_keyval.err_text
2656 l_return_error_message VARCHAR2(2000);
2657
2658 CURSOR expense_c IS
2659 SELECT
2660 aerh.employee_id employee_id,
2661 aerh.flex_concatenated header_cost_center,
2662 aerd.report_line_id report_line_id,
2663 aerd.cost_center line_cost_center
2664 FROM
2665 ap_expense_report_headers_all aerh,
2666 ap_exp_report_dists_all aerd
2667 WHERE aerh.report_header_id = p_report_header_id
2668 AND aerd.report_header_id = aerh.report_header_id
2669 AND aerd.report_distribution_id = p_report_distribution_id;
2670
2671 expense_rec expense_c%ROWTYPE;
2672 BEGIN
2673
2674 /* All the parameters are required, if any is missing return null */
2675 IF (p_report_header_id IS NULL OR p_report_distribution_id IS NULL OR p_parameter_id IS NULL OR p_ccid IS NULL) THEN
2676 RETURN l_new_ccid;
2677 END IF;
2678
2679 OPEN expense_c;
2680 FETCH expense_c into expense_rec;
2681 IF expense_c%NOTFOUND THEN
2682 CLOSE expense_c;
2683 RETURN l_new_ccid;
2684 END IF;
2685 CLOSE expense_c;
2686
2687 AP_WEB_ACCTG_PKG.BuildAccount(
2688 p_report_header_id => p_report_header_id,
2689 p_report_line_id => expense_rec.report_line_id,
2690 p_employee_id => expense_rec.employee_id,
2691 p_cost_center => expense_rec.header_cost_center,
2692 p_line_cost_center => expense_rec.line_cost_center,
2693 p_exp_type_parameter_id => p_parameter_id,
2694 p_segments => null,
2695 p_ccid => p_ccid,
2696 p_build_mode => AP_WEB_ACCTG_PKG.C_BUILD_VALIDATE,
2697 p_new_segments => l_new_segments,
2698 p_new_ccid => l_new_ccid,
2699 p_return_error_message => l_return_error_message);
2700
2701 if (l_return_error_message is not null) then
2702 return to_number(null);
2703 else
2704 return l_new_ccid;
2705 end if;
2706
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 return to_number(null);
2710
2711 END get_non_project_ccid;
2712
2713
2714 /*========================================================================
2715 | PUBLIC FUNCTION get_report_profile_value
2716 |
2717 | DESCRIPTION
2718 | This function returns profile option value for a submitted report.
2719 |
2720 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2721 |
2722 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2723 |
2724 | RETURNS
2725 | Profile option value.
2726 |
2727 | PARAMETERS
2728 |
2729 | MODIFICATION HISTORY
2730 | Date Author Description of Changes
2731 | 09-Dec-2003 J Rautiainen Created
2732 |
2733 *=======================================================================*/
2734 FUNCTION get_report_profile_value(p_report_header_id IN NUMBER,
2735 p_profile_name IN VARCHAR2) RETURN VARCHAR2 IS
2736
2737 l_item_type VARCHAR2(100) := 'APEXP';
2738 l_item_key VARCHAR2(100) := to_char(p_report_header_id);
2739
2740 l_n_org_id NUMBER;
2741 l_n_user_id NUMBER;
2742 l_n_resp_id NUMBER;
2743 l_n_resp_appl_id NUMBER;
2744 lv_return_value VARCHAR2(255);
2745 BEGIN
2746 IF p_report_header_id IS NULL OR p_profile_name IS NULL THEN
2747 return NULL;
2748 END IF;
2749
2750 l_n_org_id := WF_ENGINE.GetItemAttrNumber(l_item_type,
2751 l_item_key,
2752 'ORG_ID');
2753
2754 l_n_user_id := WF_ENGINE.GetItemAttrNumber(l_item_type,
2755 l_item_key,
2756 'USER_ID');
2757
2758 l_n_resp_id := WF_ENGINE.GetItemAttrNumber(l_item_type,
2759 l_item_key,
2760 'RESPONSIBILITY_ID');
2761
2762 l_n_resp_appl_id := WF_ENGINE.GetItemAttrNumber(l_item_type,
2763 l_item_key,
2764 'APPLICATION_ID');
2765
2766 lv_return_value := fnd_profile.VALUE_SPECIFIC(p_profile_name,
2767 l_n_user_id,
2768 l_n_resp_id,
2769 l_n_resp_appl_id,
2770 l_n_org_id);
2771 RETURN lv_return_value;
2772
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 /* Something threw an exception, we cannot fix the issue so return null
2776 * to indicate that value could not be fetched. */
2777 RETURN NULL;
2778 END get_report_profile_value;
2779
2780 /*========================================================================
2781 | PUBLIC FUNCTION get_average_pdm_rate
2782 |
2783 | DESCRIPTION
2784 | This function returns the average pdm rate on an line.
2785 | The logic has been copied from BC4J VO method:
2786 | PerDiemLinesVORowImpl.calculateTransientValues
2787 |
2788 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2789 | Called from BC4J.
2790 |
2791 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2792 |
2793 | RETURNS
2794 | Average PDM rate as NUMBER.
2795 |
2796 | PARAMETERS
2797 | p_report_header_id IN Expense report header identifier
2798 | p_distribution_line_number IN Expense report line identifier
2799 |
2800 | MODIFICATION HISTORY
2801 | Date Author Description of Changes
2802 | 13-Jan-2004 J Rautiainen Created
2803 |
2804 *=======================================================================*/
2805 FUNCTION get_average_pdm_rate(p_report_header_id IN NUMBER,
2806 p_distribution_line_number IN NUMBER) RETURN NUMBER IS
2807 CURSOR pdm_cur IS
2808 select NVL(aerl.NUM_PDM_DAYS1,0) NUM_PDM_DAYS1,
2809 NVL(aerl.NUM_PDM_DAYS2,0) NUM_PDM_DAYS2,
2810 NVL(aerl.NUM_PDM_DAYS3,0) NUM_PDM_DAYS3,
2811 NVL(aerl.PER_DIEM_RATE1,0) PER_DIEM_RATE1,
2812 NVL(aerl.PER_DIEM_RATE2,0) PER_DIEM_RATE2,
2813 NVL(aerl.PER_DIEM_RATE3,0) PER_DIEM_RATE3,
2814 NVL(end_expense_date - start_expense_date,0)+1 number_of_days,
2815 ph.day_period_code
2816 from ap_expense_report_lines_all aerl,
2817 ap_pol_headers ph,
2818 ap_expense_report_params_all erp
2819 where aerl.report_header_id = p_report_header_id
2820 and aerl.distribution_line_number = p_distribution_line_number
2821 and aerl.category_code = AP_WEB_POLICY_UTILS.c_PER_DIEM
2822 and erp.parameter_id = aerl.web_parameter_id
2823 and ph.policy_id = erp.company_policy_id;
2824
2825 pdm_rec pdm_cur%ROWTYPE;
2826 l_average NUMBER := 0;
2827 l_total NUMBER := 0;
2828 l_days NUMBER := 1;
2829 BEGIN
2830 IF p_report_header_id is null or p_distribution_line_number is null THEN
2831 return to_number(null);
2832 END IF;
2833
2834 OPEN pdm_cur;
2835 FETCH pdm_cur INTO pdm_rec;
2836
2837 IF pdm_cur%NOTFOUND THEN
2838 CLOSE pdm_cur;
2839 return to_number(null);
2840 END IF;
2841
2842 CLOSE pdm_cur;
2843
2844 IF pdm_rec.day_period_code is NULL THEN
2845 l_total := pdm_rec.NUM_PDM_DAYS1 * pdm_rec.PER_DIEM_RATE1;
2846 l_days := pdm_rec.NUM_PDM_DAYS1;
2847 ELSIF pdm_rec.day_period_code = 'START_TIME' THEN
2848 l_total := (pdm_rec.NUM_PDM_DAYS1 * pdm_rec.PER_DIEM_RATE1) + pdm_rec.PER_DIEM_RATE2;
2849 l_days := pdm_rec.NUM_PDM_DAYS1 + pdm_rec.NUM_PDM_DAYS2;
2850 ELSIF pdm_rec.day_period_code = 'MIDNIGHT' THEN
2851 l_total := pdm_rec.PER_DIEM_RATE1 + (pdm_rec.NUM_PDM_DAYS2 * pdm_rec.PER_DIEM_RATE2) + pdm_rec.PER_DIEM_RATE3;
2852 l_days := pdm_rec.NUM_PDM_DAYS1 + pdm_rec.NUM_PDM_DAYS2 + pdm_rec.NUM_PDM_DAYS3;
2853 ELSE
2854 return to_number(null);
2855 END IF;
2856
2857 IF l_days = 0 OR l_days IS NULL THEN
2858 l_days := 1;
2859 END IF;
2860
2861 l_average := l_total / l_days;
2862
2863 return l_average;
2864
2865 EXCEPTION
2866 WHEN OTHERS THEN
2867 /* If an unhandled exception occurs we do not want to propagate the exception
2868 * upwards, since this is display data only. */
2869 return to_number(null);
2870
2871 END get_average_pdm_rate;
2872
2873 /*========================================================================
2874 | PUBLIC FUNCTION get_audit_indicator
2875 |
2876 | DESCRIPTION
2877 | This function returns audit indicator displayed on the confirmation page.
2878 |
2879 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2880 | Called from BC4J.
2881 |
2882 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2883 |
2884 | RETURNS
2885 | Audit indicator as VARCHAR2.
2886 |
2887 | PARAMETERS
2888 | p_report_header_id IN Expense report header identifier
2889 |
2890 | MODIFICATION HISTORY
2891 | Date Author Description of Changes
2892 | 26-Apr-2004 J Rautiainen Created
2893 |
2894 *=======================================================================*/
2895 FUNCTION get_audit_indicator(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
2896
2897 CURSOR rule_cur IS
2898 select rs.auto_approval_tag,
2899 rs.requires_audit_tag,
2900 rs.paperless_audit_tag,
2901 aerh.audit_code,
2902 rs.image_audit_tag,
2903 nvl(rs.aud_img_receipt_required,'X') aud_img_receipt_required,
2904 nvl(rs.aud_paper_receipt_required, 'X') aud_paper_receipt_required,
2905 nvl(aerh.image_receipts_status,'NOT_REQUIRED') hdr_img_receipt_required,
2906 nvl(aerh.receipts_status,'NOT_REQUIRED') hdr_paper_receipt_required
2907 from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
2908 AP_AUD_RULE_SETS rs,
2909 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
2910 where aerh.report_header_id = p_report_header_id
2911 and aerh.org_id = rsa.org_id
2912 and rsa.rule_set_id = rs.rule_set_id
2913 and rs.rule_set_type = 'RULE'
2914 and TRUNC(SYSDATE)
2915 BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
2916 AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
2917
2918 rule_rec rule_cur%ROWTYPE;
2919 BEGIN
2920
2921 IF p_report_header_id is null THEN
2922 return null;
2923 END IF;
2924
2925 OPEN rule_cur;
2926 FETCH rule_cur INTO rule_rec;
2927
2928 IF rule_cur%NOTFOUND THEN
2929 CLOSE rule_cur;
2930 return null;
2931 END IF;
2932
2933 CLOSE rule_cur;
2934
2935 IF rule_rec.audit_code = 'PAPERLESS_AUDIT' THEN
2936 RETURN rule_rec.paperless_audit_tag;
2937 ELSIF rule_rec.audit_code = 'AUTO_APPROVE' THEN
2938 RETURN rule_rec.auto_approval_tag;
2939 ELSIF rule_rec.audit_code = 'RECEIPT_BASED' THEN
2940 RETURN rule_rec.image_audit_tag;
2941 ELSE
2942 RETURN rule_rec.requires_audit_tag;
2943 END IF;
2944
2945 END get_audit_indicator;
2946
2947 /*========================================================================
2948 | PUBLIC FUNCTION get_report_status_code
2949 |
2950 | DESCRIPTION
2951 | This function returns expense report status code.
2952 |
2953 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2954 | Called from BC4J.
2955 |
2956 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2957 |
2958 | RETURNS
2959 | Expense report status code as VARCHAR2.
2960 |
2961 | PARAMETERS
2962 | p_report_header_id IN Expense report header identifier
2963 |
2964 | MODIFICATION HISTORY
2965 | Date Author Description of Changes
2966 | 13-May-2004 J Rautiainen Created
2967 |
2968 *=======================================================================*/
2969 FUNCTION get_report_status_code(p_report_header_id IN NUMBER,
2970 p_invoice_id IN NUMBER DEFAULT NULL,
2971 p_cache IN VARCHAR2 DEFAULT 'N') RETURN VARCHAR2 IS
2972 CURSOR status_cur IS
2973 select aerh.source,
2974 AERH.Expense_Status_Code,
2975 AERH.Workflow_approved_flag,
2976 AERH.AMT_DUE_CCARD_COMPANY,
2977 AERH.AMT_DUE_EMPLOYEE,
2978 AI.Payment_status_flag,
2979 APS.GROSS_AMOUNT,
2980 AI.CANCELLED_DATE
2981 from ap_expense_report_headers_all aerh,
2982 AP_INVOICES_ALL AI,
2983 AP_PAYMENT_SCHEDULES_ALL APS
2984 where AI.INVOICE_ID(+) = AERH.VOUCHNO
2985 and APS.INVOICE_ID(+) = AI.INVOICE_ID
2986 and aerh.report_header_id = p_report_header_id;
2987
2988 CURSOR invoice_cur IS
2989 SELECT AI.Payment_status_flag,
2990 APS.GROSS_AMOUNT,
2991 AI.CANCELLED_DATE
2992 from AP_INVOICES_ALL AI,
2993 AP_PAYMENT_SCHEDULES_ALL APS
2994 where AI.INVOICE_ID= APS.INVOICE_ID
2995 AND AI.INVOICE_ID = p_invoice_id;
2996
2997 status_rec status_cur%ROWTYPE;
2998 invoice_rec invoice_cur%ROWTYPE;
2999
3000 l_status_code VARCHAR(30);
3001 l_payment_due_from_report VARCHAR(30);
3002 BEGIN
3003
3004 -- Check cache
3005 IF ((p_report_header_id = grsc_old_report_header_id) AND (p_invoice_id = grsc_old_invoice_id) AND (p_cache = 'Y'))THEN
3006 RETURN grsc_old_status_code;
3007 END IF;
3008
3009 l_status_code := null;
3010
3011 IF p_report_header_id is null THEN
3012
3013 IF p_invoice_id is null THEN
3014
3015 return null;
3016
3017 ELSE
3018
3019 OPEN invoice_cur;
3020 FETCH invoice_cur INTO invoice_rec;
3021
3022 IF invoice_cur%NOTFOUND THEN
3023 CLOSE invoice_cur;
3024 return null;
3025 END IF;
3026
3027 IF invoice_rec.cancelled_date is not null THEN
3028 l_status_code := 'CANCELLED';
3029 END IF;
3030
3031 IF invoice_rec.GROSS_AMOUNT = 0 OR invoice_rec.Payment_status_flag = 'Y' THEN
3032 l_status_code := 'PAID';
3033 END IF;
3034
3035
3036 IF invoice_rec.Payment_status_flag = 'P' THEN
3037 l_status_code := 'PARPAID';
3038 END IF;
3039
3040
3041 IF invoice_rec.Payment_status_flag = 'N' THEN
3042 l_status_code := 'INVOICED';
3043 END IF;
3044
3045 -- Update cache
3046 grsc_old_status_code := l_status_code;
3047 grsc_old_report_header_id := p_report_header_id;
3048 grsc_old_invoice_id := p_invoice_id;
3049
3050 RETURN l_status_code;
3051
3052 END IF;
3053
3054 END IF;
3055
3056 OPEN status_cur;
3057 FETCH status_cur INTO status_rec;
3058
3059 IF status_cur%NOTFOUND THEN
3060 CLOSE status_cur;
3061 return null;
3062 END IF;
3063
3064 CLOSE status_cur;
3065
3066 IF status_rec.cancelled_date is not null THEN
3067 l_status_code := 'CANCELLED';
3068 END IF;
3069
3070 IF status_rec.GROSS_AMOUNT = 0 OR status_rec.Payment_status_flag = 'Y' THEN
3071 l_status_code := 'PAID';
3072 END IF;
3073
3074
3075 IF status_rec.Payment_status_flag = 'P' THEN
3076 l_status_code := 'PARPAID';
3077 END IF;
3078
3079
3080 IF status_rec.Payment_status_flag = 'N' THEN
3081 l_status_code := 'INVOICED';
3082 END IF;
3083
3084
3085 IF l_status_code IS NULL THEN
3086
3087 IF status_rec.expense_status_code is not null THEN
3088 l_status_code := status_rec.expense_status_code;
3089 ELSE
3090 l_status_code := AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(status_rec.Source,
3091 status_rec.Workflow_approved_flag,
3092 p_report_header_id,
3093 'N');
3094 END IF;
3095
3096 END IF;
3097
3098
3099 --Checked if report is both pay, then get the both pay status code
3100 l_payment_due_from_report := AP_WEB_DB_EXPRPT_PKG.getPaymentDueFromReport(p_report_header_id);
3101
3102 IF l_payment_due_from_report IS NOT NULL AND l_payment_due_from_report = 'BOTH' THEN
3103
3104 l_status_code := AP_WEB_OA_ACTIVE_PKG.GetBothPayStatusCode(p_report_header_id, l_status_code,
3105 status_rec.AMT_DUE_CCARD_COMPANY,
3106 status_rec.AMT_DUE_EMPLOYEE);
3107
3108 END IF;
3109
3110 -- Update cache
3111 grsc_old_status_code := l_status_code;
3112 grsc_old_report_header_id := p_report_header_id;
3113 grsc_old_invoice_id := p_invoice_id;
3114
3115 RETURN l_status_code;
3116
3117 END get_report_status_code;
3118
3119 FUNCTION get_prepay_amount_remaining (P_invoice_id IN NUMBER) RETURN NUMBER IS
3120
3121 l_prepay_amount_remaining NUMBER := 0;
3122
3123 CURSOR c_prepay_amount_remaining IS
3124 SELECT SUM(nvl(aid.prepay_amount_remaining,ail.amount))
3125 FROM ap_invoice_lines_all ail,
3126 ap_invoice_distributions aid
3127 WHERE ail.invoice_id = P_invoice_id
3128 AND aid.invoice_id = ail.invoice_id
3129 AND aid.invoice_line_number = ail.line_number
3130 AND ail.line_type_lookup_code IN ('ITEM','TAX')
3131 AND nvl(aid.reversal_flag,'N') <> 'Y';
3132
3133 BEGIN
3134
3135 OPEN c_prepay_amount_remaining;
3136 FETCH c_prepay_amount_remaining INTO l_prepay_amount_remaining;
3137 CLOSE c_prepay_amount_remaining;
3138
3139 RETURN(l_prepay_amount_remaining);
3140
3141 END get_prepay_amount_remaining;
3142
3143 FUNCTION get_available_prepays(l_vendor_id IN NUMBER) RETURN NUMBER IS
3144 prepay_count NUMBER := 0;
3145 BEGIN
3146
3147 SELECT SUM(DECODE(payment_status_flag,
3148 'Y', DECODE(SIGN(earliest_settlement_date - SYSDATE),
3149 1,0,
3150 1),
3151 0))
3152 INTO prepay_count
3153 FROM ap_invoices_all ai
3154 WHERE vendor_id = l_vendor_id
3155 AND invoice_type_lookup_code = 'PREPAYMENT'
3156 AND earliest_settlement_date IS NOT NULL
3157 AND get_prepay_amount_remaining(ai.invoice_id) > 0;
3158
3159 return(prepay_count);
3160
3161 END get_available_prepays;
3162
3163 /*========================================================================
3164 | PUBLIC PROCEDURE get_rule
3165 |
3166 | DESCRIPTION
3167 | This procedures finds a audit rule matching the criteria provided.
3168 |
3169 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3170 | Called from WF.
3171 |
3172 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3173 |
3174 | RETURNS
3175 | None
3176 |
3177 | PARAMETERS
3178 | p_org_id IN organization identifier
3179 | p_date IN date that the rule is effective
3180 | p_rule_type IN rule type; 'RULE', 'AUDIT_LIST, 'NOTIFY' or 'HOLD'
3181 |
3182 | MODIFICATION HISTORY
3183 | Date Author Description of Changes
3184 | 11-Oct-2004 J Rautiainen Created
3185 |
3186 *=======================================================================*/
3187 PROCEDURE get_rule(p_org_id IN NUMBER, p_date IN DATE, p_rule_type IN VARCHAR2, p_rule OUT NOCOPY AP_AUD_RULE_SETS%ROWTYPE) IS
3188
3189 CURSOR rule_cur IS
3190 select rs.*
3191 from AP_AUD_RULE_SETS rs,
3192 AP_AUD_RULE_ASSIGNMENTS_ALL rsa
3193 where rsa.org_id = p_org_id
3194 and rsa.rule_set_id = rs.rule_set_id
3195 and rs.rule_set_type = p_rule_type
3196 and TRUNC(p_date)
3197 BETWEEN TRUNC(NVL(rsa.START_DATE,p_date))
3198 AND TRUNC(NVL(rsa.END_DATE,p_date));
3199
3200 rule_rec AP_AUD_RULE_SETS%ROWTYPE;
3201 BEGIN
3202 IF (p_org_id is null OR p_date IS NULL OR p_rule_type IS NULL ) THEN
3203 return;
3204 END IF;
3205
3206 OPEN rule_cur;
3207 FETCH rule_cur INTO rule_rec;
3208 IF rule_cur%FOUND THEN
3209 p_rule := rule_rec;
3210 END IF;
3211 CLOSE rule_cur;
3212
3213 END get_rule;
3214
3215
3216 /*========================================================================
3217 | PUBLIC FUNCTION has_default_cc_itemization
3218 |
3219 | DESCRIPTION
3220 | This function finds if the credit card transaction has level3 data
3221 | from the card provider.
3222 |
3223 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3224 | Called from AuditReportLinesVO.xml.
3225 |
3226 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3227 |
3228 | RETURNS
3229 | VARCHAR2 (Y/N)
3230 |
3231 | PARAMETERS
3232 | p_cc_trx_id IN credit card transaction id
3233 |
3234 | MODIFICATION HISTORY
3235 | Date Author Description of Changes
3236 | 02-Dec-2004 Krish Menon Created
3237 |
3238 *=======================================================================*/
3239 FUNCTION has_default_cc_itemization(p_cc_trx_id IN NUMBER) RETURN VARCHAR2 IS
3240
3241 CURSOR cc_trx IS
3242 select nvl(trxn_detail_flag, 'N') as trxn_detail_flag
3243 from ap_credit_card_trxns_all
3244 where trx_id = p_cc_trx_id;
3245
3246 l_trx_detail_flag VARCHAR2(1);
3247
3248 BEGIN
3249 l_trx_detail_flag := 'N';
3250
3251 IF ( p_cc_trx_id IS NULL ) THEN
3252 RETURN l_trx_detail_flag;
3253 END IF;
3254
3255 FOR cc_rec IN cc_trx
3256 LOOP
3257 l_trx_detail_flag := cc_rec.trxn_detail_flag;
3258 END LOOP;
3259
3260 RETURN l_trx_detail_flag;
3261
3262 END has_default_cc_itemization;
3263
3264
3265 /*========================================================================
3266 |
3267 | DESCRIPTION
3268 | This function returns 'Y' if there is capture rule and 'N' otherwise.
3269 |
3270 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3271 | Called from BC4J.
3272 |
3273 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3274 |
3275 | RETURNS
3276 | 'Y' or 'N' as VARCHAR2.
3277 |
3278 | PARAMETERS
3279 | p_reportLineId IN report line identifier
3280 |
3281 | MODIFICATION HISTORY
3282 | Date Author Description of Changes
3283 | 31-01-2005 Quan Le Created
3284 |
3285 *=======================================================================*/
3286 FUNCTION isAttendeeAvailable(p_reportLineId IN NUMBER) RETURN VARCHAR2 IS
3287 l_return VARCHAR2(1);
3288 BEGIN
3289
3290 select 'Y'
3291 into l_return
3292 from OIE_ATTENDEES_ALL
3293 where p_reportLineId = report_line_id
3294 and rownum = 1;
3295
3296 return 'Y';
3297
3298 EXCEPTION
3299 WHEN NO_DATA_FOUND THEN
3300 return 'N';
3301 WHEN OTHERS THEN
3302 return 'N';
3303
3304 END isAttendeeAvailable;
3305
3306
3307 /*========================================================================
3308 |
3309 | DESCRIPTION
3310 | This function returns attendee type from lookup code
3311 |
3312 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3313 | Called from BC4J.
3314 |
3315 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3316 |
3317 | RETURNS
3318 | Attendee type as VARCHAR2.
3319 |
3320 | PARAMETERS
3321 | p_attendeeCode IN attendee type code
3322 |
3323 | MODIFICATION HISTORY
3324 | Date Author Description of Changes
3325 | 31-01-2005 Quan Le Created
3326 |
3327 *=======================================================================*/
3328 FUNCTION getAttendeeType(p_attendeeCode IN VARCHAR2) RETURN VARCHAR2 IS
3329 l_return AP_LOOKUP_CODES.displayed_field%type := null;
3330 BEGIN
3331
3332 select displayed_field
3333 into l_return
3334 from AP_LOOKUP_CODES
3335 where lookup_type = 'OIE_ATTENDEE_TYPE'
3336 and lookup_code = p_attendeeCode;
3337
3338 return l_return;
3339
3340 EXCEPTION
3341 WHEN NO_DATA_FOUND THEN
3342 return null;
3343 WHEN OTHERS THEN
3344 return null;
3345
3346 END getAttendeeType;
3347
3348 /*========================================================================
3349 | PUBLIC PROCEDURE clear_audit_reason_codes
3350 |
3351 | DESCRIPTION
3352 | This procedures clears the data from AP_AUD_AUDIT_REASONS table
3353 | for specified expense report.
3354 |
3355 | RETURNS
3356 |
3357 | PARAMETERS
3358 | p_report_header_id : report header id of the expense report
3359 |
3360 | MODIFICATION HISTORY
3361 |
3362 *=======================================================================*/
3363 PROCEDURE clear_audit_reason_codes(p_report_header_id IN NUMBER) IS
3364 BEGIN
3365
3366 delete
3367 from ap_aud_audit_reasons
3368 where report_header_id = p_report_header_id
3369 and audit_reason_code <> 'RANDOM';
3370
3371 EXCEPTION
3372 when others then null;
3373
3374 END clear_audit_reason_codes;
3375
3376 PROCEDURE get_dist_project_ccid( p_parameter_id IN NUMBER,
3377 p_report_distribution_id IN NUMBER,
3378 p_new_ccid OUT NOCOPY NUMBER,
3379 p_return_status OUT NOCOPY VARCHAR2) is
3380
3381 l_new_segments AP_OIE_KFF_SEGMENTS_T;
3382 l_return_error_message varchar2(2000);
3383
3384 l_report_header_id NUMBER;
3385 l_report_line_id NUMBER;
3386
3387 BEGIN
3388
3389 SELECT
3390 report_header_id,
3391 report_line_id
3392 INTO
3393 l_report_header_id,
3394 l_report_line_id
3395 FROM
3396 ap_exp_report_dists_all
3397 WHERE
3398 report_distribution_id = p_report_distribution_id;
3399
3400 AP_WEB_ACCTG_PKG.BuildDistProjectAccount(
3401 p_report_header_id => l_report_header_id,
3402 p_report_line_id => l_report_line_id,
3403 p_report_distribution_id => p_report_distribution_id,
3404 p_exp_type_parameter_id => p_parameter_id,
3405 p_new_segments => l_new_segments,
3406 p_new_ccid => p_new_ccid,
3407 p_return_error_message => l_return_error_message,
3408 p_return_status => p_return_status);
3409
3410 EXCEPTION
3411 WHEN OTHERS THEN
3412 /* could not generate the projects CCID */
3413 p_new_ccid := to_number(null);
3414 p_return_status := 'ERROR';
3415
3416 END get_dist_project_ccid;
3417
3418
3419 PROCEDURE GetDefaultAcctgSegValues(
3420 P_REPORT_HEADER_ID IN NUMBER,
3421 P_REPORT_LINE_ID IN NUMBER,
3422 P_EMPLOYEE_ID IN NUMBER,
3423 P_HEADER_COST_CENTER IN AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE,
3424 P_PARAMETER_ID IN NUMBER,
3425 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
3426 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
3427 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
3428 X_MSG_COUNT OUT NOCOPY NUMBER,
3429 X_MSG_DATA OUT NOCOPY VARCHAR2,
3430 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
3431 ----------------------------------------------------------------------
3432 -- Bug: 7039477, sync error message length with fnd_flex_keyval.err_text
3433 l_return_error_message VARCHAR2(2000);
3434 l_debug_info varchar2(200);
3435 l_ccid NUMBER;
3436
3437 BEGIN
3438
3439 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AUDIT_UTILS', 'Start GetDefaultAcctgSegValues');
3440
3441 -- Initialize API return status to success
3442 x_return_status := FND_API.G_RET_STS_SUCCESS;
3443
3444
3445
3446 l_debug_info := 'Call build account to get new segments';
3447 AP_WEB_ACCTG_PKG.BuildAccount(
3448 p_report_header_id => p_report_header_id,
3449 p_report_line_id => p_report_line_id,
3450 p_employee_id => p_employee_id,
3451 p_cost_center => p_header_cost_center,
3452 p_line_cost_center => null,
3453 p_exp_type_parameter_id => p_parameter_id,
3454 p_segments => p_segments,
3455 p_ccid => null,
3456 p_build_mode => AP_WEB_ACCTG_PKG.C_DEFAULT_VALIDATE,
3457 p_new_segments => x_segments,
3458 p_new_ccid => x_combination_id,
3459 p_return_error_message => l_return_error_message);
3460
3461
3462 if (l_return_error_message is not null) then
3463 raise G_EXC_ERROR;
3464 end if;
3465
3466 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_AUDIT_UTILS', 'end GetDefaultAcctgSegValues');
3467
3468 EXCEPTION
3469 WHEN G_EXC_ERROR THEN
3470 x_return_status := FND_API.G_RET_STS_ERROR;
3471 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3472 p_data => x_msg_data);
3473
3474 WHEN OTHERS THEN
3475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3476 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3477 p_data => x_msg_data);
3478 END GetDefaultAcctgSegValues;
3479
3480
3481 END AP_WEB_AUDIT_UTILS;
[Home] [Help]