[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_USER_PREF_PKG
Source
1 PACKAGE BODY AP_WEB_DB_USER_PREF_PKG AS
2 /* $Header: apwdbupb.pls 120.7 2006/02/24 07:33:48 sbalaji noship $ */
3
4 -------------------------------------------------------------------
5 FUNCTION GetUserPrefs(p_employee_id IN HR_EMPLOYEES_CURRENT_V.employee_id%TYPE,
6 p_userPrefs_rec OUT NOCOPY UserPrefsInfoRec)
7 RETURN BOOLEAN IS
8 -------------------------------------------------------------------
9 BEGIN
10
11 SELECT default_approver_id,
12 default_expense_template_id,
13 default_purpose,
14 NVL(validate_details_flag,'N'), -- should this be N by default?
15 NVL(default_foreign_curr_flag,'N'), -- should this be N by default?
16 NVL(default_exchange_rate_flag,'N')
17 INTO p_userPrefs_rec.default_approver_id,
18 p_userPrefs_rec.default_expense_template_id,
19 p_userPrefs_rec.default_purpose,
20 p_userPrefs_rec.validate_details_flag,
21 p_userPrefs_rec.default_foreign_curr_flag,
22 p_userPrefs_rec.default_exchange_rate_flag
23 FROM ap_web_preferences
24 WHERE employee_id = p_employee_id;
25
26 RETURN TRUE;
27
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 -- Initialize preferences values
31 p_userPrefs_rec.default_approver_id := NULL;
32 p_userPrefs_rec.default_expense_template_id := NULL;
33 p_userPrefs_rec.default_purpose := NULL;
34 p_userPrefs_rec.validate_details_flag := 'N';
35 p_userPrefs_rec.default_exchange_rate_flag := 'N';
36
37 RETURN FALSE;
38 WHEN OTHERS THEN
39 AP_WEB_DB_UTIL_PKG.RaiseException('GetUserPrefs');
40 APP_EXCEPTION.RAISE_EXCEPTION;
41 return FALSE;
42 END GetUserPrefs;
43
44
45 PROCEDURE getCumulativeMileage(
46 p_policy_id IN AP_POL_LINES.POLICY_ID%TYPE,
47 p_start_date IN AP_EXPENSE_REPORT_LINES.start_expense_date%TYPE,
48 p_end_date IN AP_EXPENSE_REPORT_LINES.end_expense_date%TYPE,
49 p_employee_id IN AP_WEB_EMPLOYEE_INFO.EMPLOYEE_ID%TYPE,
50 p_cumulative_mileage OUT NOCOPY AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
51 p_period_id OUT NOCOPY AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE
52 )IS
53 --------------------------------------------------------------------------------
54 l_period_id AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE;
55
56 BEGIN
57
58 SELECT ai.numeric_value, ai.period_id
59 INTO p_cumulative_mileage, p_period_id
60 FROM ap_web_employee_info_all ai,
61 ap_pol_schedule_periods ap
62 WHERE ap.policy_id = p_policy_id
63 AND ap.start_date <= p_start_date
64 AND nvl(ap.end_date, p_end_date + 1) >= p_end_date
65 AND ap.schedule_period_id = ai.period_id
66 AND value_type = 'CUM_REIMB_DISTANCE'
67 AND employee_id = p_employee_id;
68
69
70
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73
74 SELECT ap.schedule_period_id
75 INTO l_period_id
76 FROM ap_pol_schedule_periods ap
77 WHERE ap.start_date <= p_start_date
78 AND nvl(ap.end_date, p_end_date + 1) >= p_end_date
79 AND policy_id = p_policy_id;
80
81 p_cumulative_mileage := 0;
82 p_period_id := l_period_id;
83
84 WHEN OTHERS THEN
85 AP_WEB_DB_UTIL_PKG.RaiseException('getCumulativeMileage');
86 APP_EXCEPTION.RAISE_EXCEPTION;
87 END getCumulativeMileage;
88 --------------------------------------------------------------------------------
89
90 END AP_WEB_DB_USER_PREF_PKG;