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