[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_SCHLINE_PKG
Source
1 PACKAGE BODY AP_WEB_DB_SCHLINE_PKG AS
2 /* $Header: apwdbscb.pls 120.8 2011/05/13 10:40:59 meesubra ship $ */
3
4 --------------------------------------------------------------------------------
5 FUNCTION GetScheduleLinesCursor(
6 p_policy_id IN NUMBER,
7 p_vehicle_category_code IN VARCHAR2,
8 p_vehicle_type IN VARCHAR2,
9 p_fuel_type IN VARCHAR2,
10 p_currency_code IN VARCHAR2,
11 p_employee_id IN NUMBER,
12 p_start_expense_date IN DATE,
13 p_schedule_lines_cursor OUT NOCOPY ScheduleLinesCursor)
14 RETURN BOOLEAN IS
15 --------------------------------------------------------------------------------
16 BEGIN
17 -- 3176205: This query includes all workers except for
18 -- terminated contingent workers and terminated employees who
19 -- are now active contingent workers.
20 -- Bug: 6448540, from OIE.K a schedule can have multiple passenger rates.
21 -- passenger_flag cannot be Y.
22 OPEN p_schedule_lines_cursor FOR
23 SELECT /*+ ordered */
24 SL.RANGE_HIGH,
25 nvl(SL.RANGE_LOW, 0),
26 SP.START_DATE,
27 SP.END_DATE,
28 SL.rate,
29 decode(SH.passengers_flag,null,0,decode(calculation_method, 'AMOUNT', NVL(SL.rate_per_passenger,0), 'PERCENT', NVL(SL.rate_per_passenger,0)/100* SL.rate, 0)) rate_per_passenger
30 FROM AP_POL_HEADERS SH,
31 AP_POL_SCHEDULE_PERIODS SP,
32 AP_POL_LINES SL
33 WHERE SH.POLICY_ID = p_policy_id
34 AND SH.POLICY_ID = SL.POLICY_ID
35 AND nvl(SL.ROLE_ID,0) = getRoleId(p_policy_id,p_employee_id,p_start_expense_date)
36 AND nvl(SL.VEHICLE_CATEGORY,0) = decode(
37 SH.VEHICLE_CATEGORY_FLAG,'Y', p_vehicle_category_code, nvl(SL.VEHICLE_CATEGORY,0))
38 AND nvl(SL.VEHICLE_TYPE,0) = decode(SH.VEHICLE_TYPE_FLAG, 'Y', decode(SH.VEHICLE_CATEGORY_FLAG,'Y', decode (
39 getRequiredOptionCode(p_policy_id,p_vehicle_category_code,'VEHICLE_TYPE'), 'N', nvl(SL.VEHICLE_TYPE,0), p_vehicle_type), p_vehicle_type), nvl(SL.VEHICLE_TYPE,0))
40 AND nvl(SL.FUEL_TYPE,0) = decode(SH.FUEL_TYPE_FLAG, 'Y', decode(SH.VEHICLE_CATEGORY_FLAG,'Y', decode (
41 getRequiredOptionCode(p_policy_id,p_vehicle_category_code,'FUEL_TYPE'), 'N', nvl(SL.FUEL_TYPE,0) ,p_fuel_type), p_fuel_type), nvl(SL.FUEL_TYPE,0))
42 AND SL.CURRENCY_CODE = decode(
43 SH.CURRENCY_PREFERENCE, 'MRC', p_currency_code, SL.CURRENCY_CODE)
44 AND SL.STATUS = 'ACTIVE'
45 AND SL.SCHEDULE_PERIOD_ID = SP.SCHEDULE_PERIOD_ID
46 AND SP.POLICY_ID = SH.POLICY_ID
47 AND SP.START_DATE <= p_start_expense_date
48 AND nvl(SP.END_DATE, p_start_expense_date) >= p_start_expense_date
49 AND SL.ADDON_MILEAGE_RATE_CODE is null
50 AND SL.PARENT_LINE_ID is null
51 ORDER BY SL.RANGE_LOW;
52
53 return TRUE;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 return FALSE;
57 WHEN OTHERS THEN
58 AP_WEB_DB_UTIL_PKG.RaiseException('GetScheduleLinesCursor');
59 APP_EXCEPTION.RAISE_EXCEPTION;
60 return FALSE;
61 END GetScheduleLinesCursor;
62
63
64 --------------------------------------------------------------------------------
65 PROCEDURE getSchHeaderInfo(
66 p_policy_id IN ap_pol_headers.policy_id%TYPE,
67 p_sh_distance_uom OUT NOCOPY ap_pol_headers.distance_uom%TYPE,
68 p_sh_currency_code OUT NOCOPY ap_pol_headers.currency_code%TYPE,
69 p_sh_distance_thresholds_flag OUT NOCOPY ap_pol_headers.distance_thresholds_flag%TYPE) IS
70 --------------------------------------------------------------------------------
71 BEGIN
72
73 SELECT distance_uom,
74 currency_code,
75 distance_thresholds_flag
76 INTO p_sh_distance_uom,
77 p_sh_currency_code,
78 p_sh_distance_thresholds_flag
79 FROM AP_POL_HEADERS
80 WHERE policy_id = p_policy_id;
81
82
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 AP_WEB_DB_UTIL_PKG.RaiseException('No data found: getSchHeaderInfo' );
86 APP_EXCEPTION.RAISE_EXCEPTION;
87 WHEN OTHERS THEN
88 AP_WEB_DB_UTIL_PKG.RaiseException('getSchHeaderInfo');
89 APP_EXCEPTION.RAISE_EXCEPTION;
90 END getSchHeaderInfo;
91
92 --------------------------------------------------------------------------------
93
94 FUNCTION GetRoleId(
95 p_policy_id IN NUMBER,
96 p_employee_id IN NUMBER,
97 p_start_expense_date IN DATE)
98 RETURN NUMBER IS
99 --------------------------------------------------------------------------------
100 l_role_code NUMBER := 0;
101
102 BEGIN
103
104 SELECT decode(SH.EMPLOYEE_ROLE_FLAG,'Y',
105 (decode(SH.ROLE_CODE,'JOB_GROUP', PF.job_id,'POSITION',PF.position_id,'GRADE',PF.grade_id,0)),0) INTO l_role_code
106 FROM
107 AP_POL_HEADERS SH,
108 (SELECT EMP.ASSIGNMENT_ID ,EMP.EMPLOYEE_ID
109 FROM PER_EMPLOYEES_X EMP
110 WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)= 'Y'
111 AND EMP.EMPLOYEE_ID = p_employee_id
112 UNION
113 SELECT PCW.ASSIGNMENT_ID , PCW.PERSON_ID
114 FROM PER_CONT_WORKERS_CURRENT_X PCW
115 WHERE PCW.PERSON_ID = p_employee_id) EMPLOYEE,
116 PER_ALL_ASSIGNMENTS_F PF
117 WHERE
118 PF.ASSIGNMENT_ID = EMPLOYEE.ASSIGNMENT_ID
119 AND PF.EFFECTIVE_START_DATE <= p_start_expense_date
120 AND PF.EFFECTIVE_END_DATE >= p_start_expense_date
121 AND EMPLOYEE.EMPLOYEE_ID = p_employee_id
122 AND SH.POLICY_ID = p_policy_id
123 AND ROWNUM = 1;
124
125 IF(l_role_code > 0) THEN
126 BEGIN
127 SELECT ROLE_ID INTO l_role_code FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id and ROLE_ID = l_role_code and rownum = 1;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 RETURN -1;
131 END;
132 END IF;
133
134 RETURN l_role_code;
135
136 END GetRoleId;
137
138 --------------------------------------------------------------------------------
139
140 FUNCTION GetRequiredOptionCode(
141 p_policy_id IN NUMBER,
142 p_vehicle_category IN varchar2,
143 p_option_type IN varchar2)
144 RETURN varchar2 IS
145 --------------------------------------------------------------------------------
146 l_vehicle_type_code varchar2(4) := null;
147 l_fuel_type_code varchar2(4) := null;
148
149 BEGIN
150
151 select VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
152 into l_vehicle_type_code,l_fuel_type_code
153 from AP_POL_SCHEDULE_OPTIONS pso
154 where
155 POLICY_ID = p_policy_id
156 and OPTION_TYPE = 'OIE_VEHICLE_CATEGORY'
157 and OPTION_CODE IS NOT NULL
158 and nvl(END_DATE, SYSDATE+1) > SYSDATE
159 and option_code = p_vehicle_category;
160
161 IF (p_option_type = 'FUEL_TYPE') THEN
162 return l_fuel_type_code;
163 ELSIF (p_option_type = 'VEHICLE_TYPE') THEN
164 return l_vehicle_type_code;
165 ELSE
166 return null;
167 End IF;
168 EXCEPTION
169 WHEN OTHERS THEN
170 return null;
171
172 END GetRequiredOptionCode;
173
174 END AP_WEB_DB_SCHLINE_PKG;