[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_BIK_UTILITY
Source
1 package body pay_dk_bik_utility as
2 /* $Header: pydkbiku.pkb 120.4 2012/01/19 08:59:53 rpahune ship $ */
3 --
4 g_leg_code varchar2(2);
5 cached boolean := FALSE;
6 g_effective_date date := null;
7 --
8 plsql_value_error exception;
9 pragma exception_init(plsql_value_error, -6502);
10 --
11 --
12 -- This procedure sets the g_effective_date cache
13 --
14
15 function get_table_value (p_bus_group_id in number,
16 p_table_name in varchar2,
17 p_col_name in varchar2,
18 p_row_value in varchar2,
19 p_effective_date in date default null)
20 return varchar2 is
21 l_effective_date date;
22 l_range_or_match pay_user_tables.range_or_match%type;
23 l_table_id pay_user_tables.user_table_id%type;
24 l_value pay_user_column_instances_f.value%type;
25 l_value_num NUMBER;
26
27 begin
28 --
29 -- Use either the supplied date, or the date from fnd_sessions
30 --
31 if (p_effective_date is null) then
32 if (g_effective_date is null) then
33 begin
34 select effective_date
35 into l_effective_date
36 from fnd_sessions
37 where session_id = userenv('sessionid');
38 end;
39 else
40 l_effective_date := g_effective_date;
41 end if;
42 else
43 l_effective_date := p_effective_date;
44 end if;
45 --
46 -- get the legislation code:
47 --
48 begin
49 if cached = FALSE THEN
50 select legislation_code
51 into g_leg_code
52 from per_business_groups
53 where business_group_id = p_bus_group_id;
54 cached := TRUE;
55 end if;
56 end;
57 --
58 -- get the type of query to be performed, either range or match
59 --
60 select range_or_match, user_table_id
61 into l_range_or_match, l_table_id
62 from pay_user_tables
63 where upper(user_table_name) = upper(p_table_name)
64 and nvl (business_group_id,
65 p_bus_group_id) = p_bus_group_id
66 and nvl(legislation_code, g_leg_code) = g_leg_code;
67 --
68 if (l_range_or_match = 'M') then -- matched
69 begin
70 select CINST.value
71 into l_value
72 from pay_user_column_instances_f CINST
73 , pay_user_columns C
74 , pay_user_rows_f R
75 , pay_user_tables TAB
76 where TAB.user_table_id = l_table_id
77 and C.user_table_id = TAB.user_table_id
78 and nvl (C.business_group_id,
79 p_bus_group_id) = p_bus_group_id
80 and nvl (C.legislation_code,
81 g_leg_code) = g_leg_code
82 and upper (C.user_column_name) = upper (p_col_name)
83 and CINST.user_column_id = C.user_column_id
84 and R.user_table_id = TAB.user_table_id
85 and l_effective_date between R.effective_start_date
86 and R.effective_end_date
87 and nvl (R.business_group_id,
88 p_bus_group_id) = p_bus_group_id
89 and nvl (R.legislation_code,
90 g_leg_code) = g_leg_code
91 and decode
92 (TAB.user_key_units,
93 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
94 'N', to_char(fnd_number.canonical_to_number(p_row_value)),
95 'T', upper (p_row_value),
96 null) =
97 decode
98 (TAB.user_key_units,
99 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
100 'N', to_char(fnd_number.canonical_to_number(R.row_low_range_or_name)),
101 'T', upper (R.row_low_range_or_name),
102 null)
103 and CINST.user_row_id = R.user_row_id
104 and l_effective_date between CINST.effective_start_date
105 and CINST.effective_end_date
106 and nvl (CINST.business_group_id,
107 p_bus_group_id) = p_bus_group_id
108 and nvl (CINST.legislation_code,
109 g_leg_code) = g_leg_code;
110 --
111 exception
112 when plsql_value_error then
113 select CINST.value
114 into l_value
115 from pay_user_column_instances_f CINST
116 , pay_user_columns C
117 , pay_user_rows_f R
118 , pay_user_tables TAB
119 where TAB.user_table_id = l_table_id
120 and C.user_table_id = TAB.user_table_id
121 and nvl (C.business_group_id,
122 p_bus_group_id) = p_bus_group_id
123 and nvl (C.legislation_code,
124 g_leg_code) = g_leg_code
125 and upper (C.user_column_name) = upper (p_col_name)
126 and CINST.user_column_id = C.user_column_id
127 and R.user_table_id = TAB.user_table_id
128 and l_effective_date between R.effective_start_date
129 and R.effective_end_date
130 and nvl (R.business_group_id,
131 p_bus_group_id) = p_bus_group_id
132 and nvl (R.legislation_code,
133 g_leg_code) = g_leg_code
134 and decode
135 (TAB.user_key_units,
136 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
137 'N', p_row_value,
138 'T', upper (p_row_value),
139 null) =
140 decode
141 (TAB.user_key_units,
142 'D', to_char(fnd_date.canonical_to_date(R.row_low_range_or_name)),
143 'N', R.row_low_range_or_name,
144 'T', upper (R.row_low_range_or_name),
145 null)
146 and CINST.user_row_id = R.user_row_id
147 and l_effective_date between CINST.effective_start_date
148 and CINST.effective_end_date
149 and nvl (CINST.business_group_id,
150 p_bus_group_id) = p_bus_group_id
151 and nvl (CINST.legislation_code,
152 g_leg_code) = g_leg_code;
153 --
154 when others then
155 raise;
156 end;
157 --
158 return l_value;
159 --
160 else -- range
161 begin
162 select CINST.value
163 into l_value
164 from pay_user_column_instances_f CINST
165 , pay_user_columns C
166 , pay_user_rows_f R
167 , pay_user_tables TAB
168 where TAB.user_table_id = l_table_id
169 and C.user_table_id = TAB.user_table_id
170 and nvl (C.business_group_id,
171 p_bus_group_id) = p_bus_group_id
172 and nvl (C.legislation_code,
173 g_leg_code) = g_leg_code
174 and upper (C.user_column_name) = upper (p_col_name)
175 and CINST.user_column_id = C.user_column_id
176 and R.user_table_id = TAB.user_table_id
177 and l_effective_date between R.effective_start_date
178 and R.effective_end_date
179 and nvl (R.business_group_id,
180 p_bus_group_id) = p_bus_group_id
181 and nvl (R.legislation_code,
182 g_leg_code) = g_leg_code
183 and fnd_number.canonical_to_number (p_row_value)
184 between fnd_number.canonical_to_number (R.row_low_range_or_name)
185 and fnd_number.canonical_to_number (R.row_high_range)
186 and TAB.user_key_units = 'N'
187 and CINST.user_row_id = R.user_row_id
188 and l_effective_date between CINST.effective_start_date
189 and CINST.effective_end_date
190 and nvl (CINST.business_group_id,
191 p_bus_group_id) = p_bus_group_id
192 and nvl (CINST.legislation_code,
193 g_leg_code) = g_leg_code;
194
195 l_value_num := fnd_number.canonical_to_number(l_value);
196
197
198 if fnd_number.canonical_to_number(l_value) <= 0 then
199 l_value := 'N';
200 end if;
201
202
203
204 exception
205 when NO_DATA_FOUND then
206 l_value := 'NE';
207
208 when others then
209 l_value := 'ERR';
210 end;
211 --
212 return l_value;
213
214 end if;
215
216
217 end get_table_value;
218
219
220 FUNCTION get_vehicle_info
221 ( p_assignment_id per_all_assignments_f.assignment_id%TYPE,
222 p_business_group_id in number,
223 p_date_earned DATE,
224 p_vehicle_allot_id pqp_vehicle_allocations_f.VEHICLE_ALLOCATION_ID%TYPE,
225 p_lic_reg_date OUT NOCOPY pqp_vehicle_repository_f.INITIAL_REGISTRATION%TYPE,
226 p_buying_date OUT NOCOPY pqp_vehicle_repository_f.LAST_REGISTRATION_RENEW_DATE%TYPE,
227 p_buying_price OUT NOCOPY pqp_vehicle_repository_f.LIST_PRICE%TYPE,
228 p_green_environment_fee OUT NOCOPY number -- 9676720 fix
229 )
230 return NUMBER
231 IS
232 l_value NUMBER;
233 BEGIN
234 BEGIN
235
236 select pvr.INITIAL_REGISTRATION
237 ,pvr.LAST_REGISTRATION_RENEW_DATE
238 ,pvr.LIST_PRICE
239 ,fnd_number.canonical_to_number(pvr.vre_information2) -- 9676720 fix
240 INTO p_lic_reg_date
241 ,p_buying_date
242 ,p_buying_price
243 ,p_green_environment_fee -- 9079593 fix
244 from pqp_vehicle_allocations_f pva
245 ,pqp_vehicle_repository_f pvr
246 where pva.assignment_id = p_assignment_id
247 and pvr.vehicle_repository_id = pva.vehicle_repository_id
248 and pva.business_group_id = p_business_group_id
249 and pva.vehicle_allocation_id = p_vehicle_allot_id
250 and p_date_earned between pva.EFFECTIVE_START_DATE and pva.EFFECTIVE_END_DATE
251 and p_date_earned between pvr.EFFECTIVE_START_DATE and pvr.EFFECTIVE_END_DATE;
252
253 l_value := 1;
254
255 EXCEPTION
256 WHEN NO_DATA_FOUND
257 THEN
258 l_value := 0;
259
260 END;
261
262 RETURN l_value;
263 END get_vehicle_info;
264
265 END pay_dk_bik_utility;