[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_MIA_REPORT_PKG
Source
4 --Global parameters
1 PACKAGE BODY PAY_DK_MIA_REPORT_PKG as
2 /* $Header: pydkmiar.pkb 120.0 2006/01/18 05:20:27 pgopal noship $ */
3
5 g_package CONSTANT varchar2(33) := 'PAY_DK_MIA_REPORT_PKG.';
6
7 -----------------------------------------------------------------------------
8 --RANGE CODE
9 -----------------------------------------------------------------------------
10 PROCEDURE range_cursor(p_payroll_action_id IN NUMBER,
11 p_sqlstr OUT NOCOPY VARCHAR2)
12 IS
13 BEGIN
14 p_sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
15 END range_cursor;
16
17 -----------------------------------------------------------------------------
18 --ASSIGNMENT ACTION CODE
22 stperson IN NUMBER,
19 -----------------------------------------------------------------------------
20 PROCEDURE assignment_action_code(
21 pactid IN NUMBER,
23 endperson IN NUMBER,
24 chunk IN NUMBER)
25 IS
26 BEGIN
27 null;
28 END assignment_action_code;
29
30
31 -----------------------------------------------------------------------------
32 -- GET_PARAMETER used in SQL to decode legislative parameters
33 -----------------------------------------------------------------------------
34 FUNCTION get_parameter(
35 p_parameter_string IN VARCHAR2
36 ,p_token IN VARCHAR2
37 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
38 IS
39 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
40 l_start_pos NUMBER;
41 l_delimiter varchar2(1);
42 l_proc VARCHAR2(60);
43
44 BEGIN
45 l_delimiter :=' ';
46 l_proc := g_package||' get parameter ';
47 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
48
49 IF l_start_pos = 0 THEN
50 l_delimiter := '|';
51 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
52 END IF;
53
54 IF l_start_pos <> 0 THEN
55 l_start_pos := l_start_pos + length(p_token||'=');
56 l_parameter := substr(p_parameter_string,
57 l_start_pos,
58 instr(p_parameter_string||' ',
59 ',',l_start_pos)
60 - l_start_pos);
61
62 IF p_segment_number IS NOT NULL THEN
63 l_parameter := ':'||l_parameter||':';
64 l_parameter := substr(l_parameter,
65 instr(l_parameter,':',1,p_segment_number)+1,
66 instr(l_parameter,':',1,p_segment_number+1) -1
67 - instr(l_parameter,':',1,p_segment_number));
68 END IF;
69
70 END IF;
71
72 RETURN l_parameter;
73 END get_parameter;
74
75
76 FUNCTION get_cp_parameter(
77 p_payroll_action_id NUMBER
78 ,p_token_name VARCHAR2) RETURN VARCHAR2 AS
79
80 CURSOR csr_parameter_info(p_pact_id IN NUMBER) IS
81 SELECT legislative_parameters
82 FROM pay_payroll_actions
83 WHERE payroll_action_id = p_pact_id;
84
85 l_token_value VARCHAR2(50);
86 l_parameter pay_payroll_actions.legislative_parameters%TYPE ;
87 l_delimiter varchar2(1);
88 l_start_pos NUMBER;
89
90 BEGIN
91 --
92 l_parameter := NULL;
93 l_delimiter :=',';
94 hr_utility.set_location('p_token_name = ' || p_token_name,20);
95
96 OPEN csr_parameter_info(p_payroll_action_id);
97 FETCH csr_parameter_info INTO l_parameter;
98 CLOSE csr_parameter_info;
99
100 l_start_pos := instr(l_parameter,p_token_name||'=');
101 IF l_start_pos = 0 THEN
102 l_delimiter := '|';
103 l_start_pos := instr(l_parameter,p_token_name||'=');
104 END IF;
105
106 IF l_start_pos <> 0 THEN
107 l_start_pos := l_start_pos + length(p_token_name||'=');
108 l_token_value := substr(l_parameter,
109 l_start_pos,
110 instr(l_parameter||' ',
111 l_delimiter,l_start_pos)
112 - l_start_pos);
113 END IF;
114 --
115 l_token_value := trim(l_token_value);
116 l_token_value := l_token_value || ' ';
117
118 if length(l_token_value) = 1 then
119 l_token_value :='-1';
120 else
121 l_token_value := trim(l_token_value);
122 end if;
123
124 --
125 hr_utility.set_location('l_token_value = ' || l_token_value,20);
126 hr_utility.set_location('Leaving ' || 'get_parameters',30);
127
128 RETURN l_token_value;
129 END get_cp_parameter;
130
131
132 FUNCTION get_period_dates(
133 p_payroll_id IN VARCHAR2
134 ,p_payroll_action_id IN VARCHAR2
135 ,p_start_date OUT NOCOPY VARCHAR2
136 ,p_end_date OUT NOCOPY VARCHAR2
137 ,p_direct_dd_date OUT NOCOPY VARCHAR2)RETURN VARCHAR2 AS
138
139 cursor csr_get_eff_date(l_payroll_action_id IN NUMBER) is
140 select effective_date from pay_payroll_actions
141 where payroll_action_id = l_payroll_action_id;
142
143 cursor csr_get_period_dates(l_payroll_id IN NUMBER, l_effective_date IN DATE) is
144 select to_char(start_date,'YYYYMMDD'), to_char(end_date,'YYYYMMDD'), to_char(default_dd_date,'YYYYMMDD')
145 from per_time_periods
146 where payroll_id = l_payroll_id
147 and l_effective_date between start_date and end_date;
148
149 l_payroll_id NUMBER;
150 l_payroll_action_id NUMBER;
151 l_effective_date DATE;
152 l_start_date VARCHAR2(8);
153 l_end_date VARCHAR2(8);
154 l_direct_dd_date VARCHAR2(8);
155
156 begin
157 l_payroll_id := to_number(p_payroll_id);
158 l_payroll_action_id := to_number(p_payroll_action_id);
159
160 open csr_get_eff_date(l_payroll_action_id);
161 fetch csr_get_eff_date into l_effective_date;
162 close csr_get_eff_date;
163
164 open csr_get_period_dates(l_payroll_id,l_effective_date);
165 fetch csr_get_period_dates into l_start_date,l_end_date,l_direct_dd_date;
169 p_end_date := l_end_date;
166 close csr_get_period_dates;
167
168 p_start_date := l_start_date;
170 p_direct_dd_date := l_direct_dd_date;
171 return '1';
172
173 exception
174 when others then
175 return '0';
176
177 end get_period_dates;
178
179 /*
180 FUNCTION get_payroll_period(
181 p_payroll_id IN VARCHAR2
182 ,p_payroll_action_id IN VARCHAR2)RETURN VARCHAR2 AS
183
184 cursor csr_get_eff_date(l_payroll_action_id IN NUMBER) is
185 select effective_date from pay_payroll_actions
186 where payroll_action_id = l_payroll_action_id;
187
188 cursor csr_get_payroll_period(l_payroll_id IN NUMBER, l_effective_date IN DATE) is
189 select period_name,period_num
190 from per_time_periods
191 where payroll_id = l_payroll_id
192 and l_effective_date between start_date and end_date;
193
194 l_payroll_id NUMBER(9);
195 l_payroll_action_id NUMBER(9);
196 l_effective_date DATE;
197 l_period_name VARCHAR2(70);
198 l_period_num NUMBER(15);
199 l_year varchar2(10);
200 l_pp varchar2(5);
201 l_period VARCHAR2(20);
202
203 begin
204 l_payroll_id := to_number(p_payroll_id);
205 l_payroll_action_id := to_number(p_payroll_action_id);
206
207 open csr_get_eff_date(l_payroll_action_id);
208 fetch csr_get_eff_date into l_effective_date;
209 close csr_get_eff_date;
210
211 open csr_get_payroll_period(l_payroll_id,l_effective_date);
212 fetch csr_get_payroll_period into l_period_name,l_period_num;
213 close csr_get_payroll_period;
214
215 l_year := substr(l_period_name,1,10);
216 l_year := substr(l_year, instr(l_year,' ')+1,4);
217
218 if l_period_num <= 9 then
219 l_pp := substr('0'||ltrim(rtrim(to_char(l_period_num))),1,2);
220 else
221 l_pp := substr(ltrim(rtrim(to_char(l_period_num))),1,2);
222 end if;
223
224 l_period := l_year || l_pp;
225 l_period := substr(l_period,1,6);
226 return l_period;
227
228 end get_payroll_period;
229 */
230
231 FUNCTION get_taxable_pay
232 (p_assignment_action_id IN VARCHAR2) RETURN NUMBER as
233 /* cursor to get defined balance id */
234
235 cursor csr_get_defined_balance_id(p_balance_name IN VARCHAR2, p_dbi_suffix IN VARCHAR2) is
236 SELECT pdb.defined_balance_id
237 FROM pay_defined_balances pdb
238 ,pay_balance_types pbt
239 ,pay_balance_dimensions pbd
240 WHERE pbd.database_item_suffix = p_dbi_suffix
241 AND pbd.legislation_code = 'DK'
242 AND pbt.balance_name = p_balance_name
243 AND pbt.legislation_code = 'DK'
244 AND pdb.balance_type_id = pbt.balance_type_id
245 AND pdb.balance_dimension_id = pbd.balance_dimension_id
246 AND pdb.legislation_code = 'DK';
247
248 l_defined_balance_id NUMBER;
249 l_balance_name VARCHAR2(30);
250 l_dbi_suffix VARCHAR2(30);
251 l_taxable_pay NUMBER;
252
253 BEGIN
254 l_balance_name :='Taxable Pay';
255 l_dbi_suffix := '_ASG_PTD';
256
257 open csr_get_defined_balance_id(l_balance_name,l_dbi_suffix);
258 fetch csr_get_defined_balance_id into l_defined_balance_id;
259 close csr_get_defined_balance_id;
260
261 l_defined_balance_id := NVL(l_defined_balance_id,0);
262 l_taxable_pay := pay_balance_pkg.get_value(l_defined_balance_id,p_assignment_action_id);
263
264 RETURN l_taxable_pay ;
265 END get_taxable_pay;
266
267
268 FUNCTION get_sp_name(p_business_group_id IN NUMBER) RETURN VARCHAR2 as
269
270 CURSOR csr_get_sp_name(p_business_group_id NUMBER) is
271 SELECT name from hr_organization_units where organization_id =
272 (select organization_id from hr_organization_information where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS')
273 and business_group_id = p_business_group_id;
274
275 l_sp_name VARCHAR2(50);
276
277 begin
278 OPEN csr_get_sp_name(p_business_group_id);
279 FETCH csr_get_sp_name INTO l_sp_name;
280
281 IF csr_get_sp_name%NOTFOUND then
282 l_sp_name := '-1';
283 END IF;
284
285 CLOSE csr_get_sp_name;
286
287 RETURN l_sp_name;
288
289 END get_sp_name;
290
291 FUNCTION get_sp_details(p_payroll_action_id IN number
292 ,p_cvr_no OUT NOCOPY varchar2
293 ,p_sp_name OUT NOCOPY varchar2
294 ,p_org_address OUT NOCOPY varchar2
295 ,p_town OUT NOCOPY varchar2) RETURN VARCHAR2 as
296
297 CURSOR csr_get_sp_details(p_payroll_action_id NUMBER) is
298 SELECT hou.name , hoi.ORG_INFORMATION1,
299 substr((loc.ADDRESS_LINE_1||' '||loc.ADDRESS_LINE_2||' '||loc.ADDRESS_LINE_3),1,40),
300 substr((loc.POSTAL_CODE ||' ' || loc.TOWN_OR_CITY),1,40)
301 from hr_organization_units hou, hr_organization_information hoi, hr_locations loc
302 where hou.business_group_id = get_business_group_id(p_payroll_action_id) -- change the bg id
303 AND hoi.organization_id = hou.organization_id
304 and hoi.org_information_context='DK_SERVICE_PROVIDER_DETAILS'
305 and hou.location_id=loc.location_id;
306
307 l_cvr_no VARCHAR2(8);
308 l_sp_name VARCHAR2(40);
309 l_org_address varchar2(40);
310 l_town varchar2(40);
311
312 BEGIN
313 OPEN csr_get_sp_details(p_payroll_action_id);
314 FETCH csr_get_sp_details INTO l_sp_name,l_cvr_no,l_org_address,l_town;
315 IF csr_get_sp_details%NOTFOUND then
316 l_cvr_no :='-1';
317 l_sp_name :='-1';
318 l_org_address :='-1';
319 l_town :='-1';
320 END IF;
321
322 CLOSE csr_get_sp_details;
323 p_cvr_no := l_cvr_no;
324 p_sp_name := l_sp_name;
325 p_org_address := l_org_address;
326 p_town := l_town;
327
328
329 RETURN '1';
330
331 END get_sp_details;
332
333
334 FUNCTION get_business_group_id(p_payroll_action_id IN number) RETURN NUMBER as
335 CURSOR csr_get_bg_id(p_payroll_action_id number) is
336 SELECT business_group_id
337 FROM pay_payroll_actions
338 WHERE payroll_action_id = p_payroll_action_id;
339
340 l_bg_id NUMBER(15);
341
342 begin
343 OPEN csr_get_bg_id(p_payroll_action_id);
344 FETCH csr_get_bg_id INTO l_bg_id;
345 CLOSE csr_get_bg_id;
346
347 RETURN l_bg_id;
348
349 END get_business_group_id;
350
351
352 FUNCTION get_dd_date(p_payroll_id IN NUMBER,
353 p_effective_date IN DATE) RETURN VARCHAR2 as
354
355 CURSOR csr_get_dd_date(p_payroll_id NUMBER,p_effective_date DATE) is
356 SELECT TO_CHAR(to_date(substr(legislative_parameters,instr(legislative_parameters,'=')+1,10),'YYYY/MM/DD'),'YYYYMMDD')
357 FROM pay_payroll_actions
358 where payroll_action_id = (select min(payroll_action_id) from pay_payroll_actions
359 where payroll_id = p_payroll_id
360 and action_type ='M'
361 AND action_status ='C'
362 AND p_effective_date BETWEEN start_date AND effective_date);
363
364
365 l_dd_date VARCHAR2(8);
366
367 BEGIN
368
369 OPEN csr_get_dd_date(p_payroll_id,p_effective_date);
370 FETCH csr_get_dd_date INTO l_dd_date;
371
372 IF csr_get_dd_date%NOTFOUND then
373 l_dd_date := lpad(' ',8);
374 END IF;
375
376 CLOSE csr_get_dd_date;
377
378 RETURN l_dd_date;
379
380 END get_dd_date;
381
382
383 FUNCTION check_termination_date(p_start_date varchar2,
384 p_end_date varchar2,
385 p_termination_date varchar2) RETURN varchar2 as
386
387 CURSOR csr_check_termination(p_start_date DATE,p_end_date DATE,p_termination_date DATE) is
388 SELECT '1' FROM dual
389 WHERE p_termination_date BETWEEN p_start_date AND p_end_date;
390
391 l_start_date DATE;
392 l_end_date DATE;
393 l_termination_date DATE;
394 l_value varchar2(1);
395 --l_return number;
396
397 begin
398
399 l_start_date := to_date(p_start_date,'YYYYMMDD');
400 l_end_date:= to_date(p_end_date,'YYYYMMDD');
401 l_termination_date := to_date(p_termination_date,'YYYYMMDD');
402
403 OPEN csr_check_termination(l_start_date,l_end_date,l_termination_date);
404 FETCH csr_check_termination INTO l_value;
405 IF csr_check_termination%NOTFOUND then
406 l_value := '0';
407 END if;
408 CLOSE csr_check_termination;
409
410
411 RETURN l_value;
412
413 END check_termination_date;
414
415
416 END PAY_DK_MIA_REPORT_PKG;
417