[Home] [Help]
PACKAGE BODY: APPS.PAY_KW_PAYROLL_REGISTER
Source
1 PACKAGE BODY PAY_KW_PAYROLL_REGISTER AS
2 /* $Header: pykwpyrg.pkb 120.12 2012/01/19 10:28:50 rpahune ship $ */
3 lg_format_mask varchar2(50);
4 ----------------------------------------------------------
5 PROCEDURE set_currency_mask
6 (p_business_group_id IN NUMBER) IS
7 /* Cursor to retrieve Currency */
8 CURSOR csr_currency IS
9 SELECT org_information10
10 FROM hr_organization_information
11 WHERE organization_id = p_business_group_id
12 AND org_information_context = 'Business Group Information';
13 l_currency VARCHAR2(40);
14 BEGIN
15 OPEN csr_currency;
16 FETCH csr_currency into l_currency;
17 CLOSE csr_currency;
18 lg_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
19 END set_currency_mask;
20 ----------------------------------------------------------
21 PROCEDURE GET_PAYROLL_REGISTER_DATA ( p_report IN varchar2,
22 p_organization_id IN number,
23 p_org_structure_version_id IN number,
24 p_payroll_id IN number,
25 p_effective_char_date IN varchar2,
26 p_sort_order1 IN varchar2,
27 p_sort_order2 In varchar2,
28 p_sort_order3 IN varchar2,
29 l_xfdf_blob OUT NOCOPY BLOB)
30 IS
31 TYPE rec_orgs IS RECORD (org_name varchar2(240),payroll_id number(9));
32 TYPE tab_orgs IS TABLE OF rec_orgs INDEX BY BINARY_INTEGER;
33 tab_org_data tab_orgs;
34 tab_org_data_init tab_orgs;
35 TYPE rec_det IS RECORD ( r_assact_id number(15),
36 r_org_pay_id number(9),
37 r_full_name varchar2(240),
38 r_emp_no varchar2(240),
39 r_org_name varchar2(240),
40 r_position varchar2(240),
41 r_title varchar2(30),
42 r_first_name varchar2(150),
43 r_family_name varchar2(150),
44 r_payroll_name varchar2(80),
45 r_nationality varchar2(30),
46 r_cost_center varchar2(2000),
47 r_job varchar2(240),
48 r_ytd_earning varchar2(40),
49 r_ytd_deduction varchar2(40));
50 TYPE tab_dets IS TABLE OF rec_det INDEX BY BINARY_INTEGER;
51 tab_dets_data tab_dets;
52 tab_dets_data_init tab_dets;
53 TYPE rec_earn IS RECORD ( r_payact_earn_id number(15),
54 r_assact_earn_id number(15),
55 r_earn_narrative varchar2(240),
56 r_earn_numeric_value varchar2(40),
57 r_earn_element_type varchar2(30));
58 TYPE tab_earn IS TABLE OF rec_earn INDEX BY BINARY_INTEGER;
59 tab_earn_data tab_earn;
60 tab_earn_data_init tab_earn;
61 TYPE rec_ded IS RECORD ( r_payact_ded_id number(15),
62 r_assact_ded_id number(15),
63 r_ded_narrative varchar2(240),
64 r_ded_numeric_value varchar2(40),
65 r_ded_element_type varchar2(30));
66 TYPE tab_ded IS TABLE OF rec_ded INDEX BY BINARY_INTEGER;
67 tab_ded_data tab_ded;
68 tab_ded_data_init tab_ded;
69 TYPE rec_paymeth IS RECORD ( r_org_paymeth_name varchar2(240),
70 r_bank_name varchar2(240),
71 r_branch_name varchar2(240),
72 r_account_number varchar2(240),
73 r_amount varchar2(40),
74 r_act_con_id number(15),
75 r_pay_status varchar2(240));
76 TYPE tab_paymeth IS TABLE OF rec_paymeth INDEX BY BINARY_INTEGER;
77 tab_paymeth_data tab_paymeth;
78 tab_paymeth_data_init tab_paymeth;
79 TYPE rec_pyrl_sum IS RECORD (payroll_id number(9));
80 TYPE tab_sum IS TABLE OF rec_pyrl_sum INDEX BY BINARY_INTEGER;
81 tab_sum_data tab_sum;
82 tab_sum_data_init tab_sum;
83 l_org_count number :=1;
84 l_temp_count number := 1;
85 p_org_id_child number(9);
86 i number := 1;
87 j number := 1;
88 k number := 1;
89 l number := 1;
90 m number := 1;
91 t number := 1;
92 f number := 1;
93 l_ret number;
94 l_w_indicator number := 0;
95 l_parent_id number;
96 l_err number := 0;
97 l_emp_count number := 0;
98 l_org_condition LONG;
99 l_order_by varchar2(2000);
100 statem LONG;
101 sql_cur number;
102 ignore number;
103 l_v1 varchar2(240);
104 l_v2 varchar2(240);
105 p_org_child_id number;
106 p_effective_date date;
107 l_header_payroll_name varchar2(240);
108 emp_earn_sum number(12,3):=0;
109 emp_ded_sum number(12,3):=0;
110 org_ded_sum_try number:=0;
111 org_ded_sum_1 number:=0;
112 org_tot_pay number:=0;
113 org_ded_sum_tot number:=0;
114 org_earn_sum_tot number:=0;
115 --org_earn_sum_last number(12,3) :=0;
116 org_earn_sum_last varchar2(40);
117 --org_ded_sum_last number(12,3) :=0;
118 org_ded_sum_last varchar2(40);
119 l_sum_flag number:=0;
120 l_order_1 varchar2(30);
121 l_order_2 varchar2(30);
122 l_order_3 varchar2(30);
123 l_header_pyrl_name varchar2(240);
124 l_header_organization_name varchar2(240);
125 l_org_bg_id number;
126 l_pay_bg_id number;
127
128 l_e_temp_sum number;
129 l_e_tot_sum number;
130 l_e_arch_assact_1 number;
131 l_d_temp_sum number;
132 l_d_tot_sum number;
133 l_d_arch_assact_1 number;
134
135
136 /* SELECTS BUSINESS GROUP ID FOR ORGANIZATION SPECIFIED */
137 CURSOR csr_get_bg_id_org (l_org_id number) IS
138 select business_group_id
139 from hr_all_organization_units
140 where ORGANIZATION_ID = l_org_id;
141 /* SELECTS BUSINESS GROUP ID FROM THE PAYROLL SPECIFIED */
142 CURSOR csr_get_bg_id_pay (l_payroll_id number , l_effective_date date) IS
143 select business_group_id
144 from pay_all_payrolls_f
145 where payroll_id = l_payroll_id
146 AND trunc(l_effective_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
147 /* SELECTS PAYROLL NAME FOR HEADER */
148 CURSOR csr_get_payroll_name(l_pyrl_id number , l_eff_date date) IS
149 select payroll_name
150 from pay_all_payrolls_f
151 where PAYROLL_ID = l_pyrl_id
152 and l_eff_date between effective_start_date and effective_end_date;
153 /* SELECTS ORGANIZATION NAME FOR HEADER */
154 CURSOR csr_get_organization_name (l_org_id number) IS
155 select name
156 from hr_all_organization_units
157 where ORGANIZATION_ID = l_org_id;
158 /* SELECTS ORGANIZATIONS COMING UNDER A PAYROLL */
159 /* Modifyig the cursor for performance issue for Bug 7632337 */
160 /*CURSOR csr_get_orgs_for_payroll (l_payroll_id number , l_effective_date date) is
161 SELECT distinct pai_emp.action_information15 organization
162 ,ppf.payroll_id
163 FROM per_time_periods ptp
164 ,pay_action_information pai_emp
165 ,pay_assignment_actions paa1
166 ,pay_action_interlocks lck
167 ,pay_payroll_actions ppa1
168 ,pay_all_payrolls_f ppf
169 WHERE ptp.payroll_id = l_payroll_id
170 AND ptp.time_period_id = pai_emp.action_information16
171 AND pai_emp.action_context_type = 'AAP'
172 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
173 AND lck.locking_action_id = pai_emp.action_context_id
174 AND lck.locked_action_id = paa1.assignment_action_id
175 AND paa1.payroll_action_id = ppa1.payroll_action_id
176 AND ppa1.action_type in ('R','Q')
177 AND ppa1.action_status = 'C'
178 AND paa1.action_status = 'C'
179 AND ptp.end_date = l_effective_date
180 AND ppf.payroll_id = ptp.payroll_id
181 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date; */
182
183 CURSOR csr_get_orgs_for_payroll (l_payroll_id number , l_effective_date date) is
184 SELECT distinct pai_emp.action_information15 organization
185 ,ppf.payroll_id
186 FROM per_time_periods ptp
187 ,pay_action_information pai_emp
188 ,pay_assignment_actions paa1
189 ,pay_payroll_actions ppa1
190 ,pay_all_payrolls_f ppf
191 WHERE ptp.payroll_id = l_payroll_id
192 AND ptp.time_period_id = pai_emp.action_information16
193 AND pai_emp.action_context_type = 'AAP'
194 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
195 AND pai_emp.action_context_id = paa1.assignment_action_id
196 AND paa1.payroll_action_id = ppa1.payroll_action_id
197 AND ppa1.action_type = 'X'
198 and ppa1.report_type = 'KW_ARCHIVE'
199 AND ppa1.action_status = 'C'
200 AND paa1.action_status = 'C'
201 AND ptp.end_date = l_effective_date
202 AND ppf.payroll_id = ptp.payroll_id
203 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date;
204 /* CURSOR ONE */
205 /* Modifyig the cursor for performance issue for Bug 7632337 */
206 /*cursor csr_condition_one(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
207 SELECT distinct pai_emp.action_information15 organization
208 ,ppf.payroll_id
209 FROM per_time_periods ptp
210 ,pay_action_information pai_emp
211 ,pay_assignment_actions paa1
212 ,pay_action_interlocks lck
213 ,pay_payroll_actions ppa1
214 ,pay_all_payrolls_f ppf
215 WHERE ptp.time_period_id = pai_emp.action_information16
216 AND pai_emp.action_context_type = 'AAP'
217 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
218 AND lck.locking_action_id = pai_emp.action_context_id
219 AND lck.locked_action_id = paa1.assignment_action_id
220 AND paa1.payroll_action_id = ppa1.payroll_action_id
221 AND ppa1.action_type in ('R','Q')
222 AND ppa1.action_status = 'C'
223 AND paa1.action_status = 'C'
224 AND ptp.end_date = l_effective_date
225 AND ppf.payroll_id = ptp.payroll_id
226 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
227 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
228 from per_org_structure_elements pose
229 connect by pose.organization_id_parent =
230 prior pose.organization_id_child
231 and pose.org_structure_version_id =
232 to_char (l_org_structure_version_id)
233 start with pose.organization_id_parent =
234 to_char(nvl(l_organization_id,l_parent_id))
235 and pose.org_structure_version_id =
236 to_char(l_org_structure_version_id)
237 union select to_char(nvl(l_organization_id,l_parent_id))
238 from sys.dual) ; */
239 cursor csr_condition_one(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
240 SELECT distinct pai_emp.action_information15 organization
241 ,ppf.payroll_id
242 FROM per_time_periods ptp
243 ,pay_action_information pai_emp
244 ,pay_assignment_actions paa1
245 ,pay_payroll_actions ppa1
246 ,pay_all_payrolls_f ppf
247 WHERE ptp.time_period_id = pai_emp.action_information16
248 AND pai_emp.action_context_type = 'AAP'
249 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
250 AND pai_emp.action_context_id = paa1.assignment_action_id
251 AND paa1.payroll_action_id = ppa1.payroll_action_id
252 AND ppa1.action_type = 'X'
253 AND ppa1.report_type = 'KW_ARCHIVE'
254 AND ppa1.action_status = 'C'
255 AND paa1.action_status = 'C'
256 AND ptp.end_date = l_effective_date
257 AND ppf.payroll_id = ptp.payroll_id
258 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
259 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
260 from per_org_structure_elements pose
261 connect by pose.organization_id_parent =
262 prior pose.organization_id_child
263 and pose.org_structure_version_id =
264 to_char (l_org_structure_version_id)
265 start with pose.organization_id_parent =
266 to_char(nvl(l_organization_id,l_parent_id))
267 and pose.org_structure_version_id =
268 to_char(l_org_structure_version_id)
269 union select to_char(nvl(l_organization_id,l_parent_id))
270 from sys.dual) ;
271 /* Modifyig the cursor for performance issue for Bug 7632337 */
272 /*cursor csr_condition_two(l_organization_id number , l_effective_date date) is
273 SELECT distinct pai_emp.action_information15 organization
274 ,ppf.payroll_id
275 FROM per_time_periods ptp
276 ,pay_action_information pai_emp
277 ,pay_assignment_actions paa1
278 ,pay_action_interlocks lck
279 ,pay_payroll_actions ppa1
280 ,pay_all_payrolls_f ppf
281 WHERE ptp.time_period_id = pai_emp.action_information16
282 AND pai_emp.action_context_type = 'AAP'
283 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
284 AND lck.locking_action_id = pai_emp.action_context_id
285 AND lck.locked_action_id = paa1.assignment_action_id
286 AND paa1.payroll_action_id = ppa1.payroll_action_id
287 AND ppa1.action_type in ('R','Q')
288 AND ppa1.action_status = 'C'
289 AND paa1.action_status = 'C'
290 AND ptp.end_date = l_effective_date
291 AND ppf.payroll_id = ptp.payroll_id
292 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
293 AND pai_emp.action_information2 = l_organization_id; */
294 cursor csr_condition_two(l_organization_id number , l_effective_date date) is
295 SELECT distinct pai_emp.action_information15 organization
296 ,ppf.payroll_id
297 FROM per_time_periods ptp
298 ,pay_action_information pai_emp
299 ,pay_assignment_actions paa1
300 ,pay_payroll_actions ppa1
301 ,pay_all_payrolls_f ppf
302 WHERE ptp.time_period_id = pai_emp.action_information16
303 AND pai_emp.action_context_type = 'AAP'
304 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
305 AND pai_emp.action_context_id = paa1.assignment_action_id
306 AND paa1.payroll_action_id = ppa1.payroll_action_id
307 AND ppa1.action_type = 'X'
308 AND ppa1.report_type = 'KW_ARCHIVE'
309 AND ppa1.action_status = 'C'
310 AND paa1.action_status = 'C'
311 AND ptp.end_date = l_effective_date
312 AND ppf.payroll_id = ptp.payroll_id
313 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
314 AND pai_emp.action_information2 = l_organization_id;
315 /* Modifyig the cursor for performance issue for Bug 7632337 */
316 /* It looks like dummy cursor as it has conditions 1=2 */
317 /*cursor csr_condition_three(l_effective_date date) is
318 SELECT distinct pai_emp.action_information15 organization
319 ,ppf.payroll_id
320 FROM per_time_periods ptp
321 ,pay_action_information pai_emp
322 ,pay_assignment_actions paa1
323 ,pay_action_interlocks lck
324 ,pay_payroll_actions ppa1
325 ,pay_all_payrolls_f ppf
326 WHERE ptp.time_period_id = pai_emp.action_information16
327 AND pai_emp.action_context_type = 'AAP'
328 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
329 AND lck.locking_action_id = pai_emp.action_context_id
330 AND lck.locked_action_id = paa1.assignment_action_id
331 AND paa1.payroll_action_id = ppa1.payroll_action_id
332 AND ppa1.action_type in ('R','Q')
333 AND ppa1.action_status = 'C'
334 AND paa1.action_status = 'C'
335 AND ptp.end_date = l_effective_date
336 AND ppf.payroll_id = ptp.payroll_id
337 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
338 AND 1 = 2; */
339 cursor csr_condition_three(l_effective_date date) is
340 SELECT '123' organization
341 ,'213' payroll_id
342 FROM dual
343 where 1 = 2;
344 /* CURSOR TO GET PAYROLL IDS DISTINCT */
345 /* Modifyig the cursor for performance issue for Bug 7632337 */
346 /*cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
347 SELECT distinct ppf.payroll_id
348 FROM per_time_periods ptp
349 ,pay_action_information pai_emp
350 ,pay_assignment_actions paa1
351 ,pay_action_interlocks lck
352 ,pay_payroll_actions ppa1
353 ,pay_all_payrolls_f ppf
354 WHERE ptp.time_period_id = pai_emp.action_information16
355 AND pai_emp.action_context_type = 'AAP'
356 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
357 AND lck.locking_action_id = pai_emp.action_context_id
358 AND lck.locked_action_id = paa1.assignment_action_id
359 AND paa1.payroll_action_id = ppa1.payroll_action_id
360 AND ppa1.action_type in ('R','Q')
361 AND ppa1.action_status = 'C'
362 AND paa1.action_status = 'C'
363 AND ptp.end_date = l_effective_date
364 AND ppf.payroll_id = ptp.payroll_id
365 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
366 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
367 from per_org_structure_elements pose
368 connect by pose.organization_id_parent =
369 prior pose.organization_id_child
370 and pose.org_structure_version_id =
371 to_char (l_org_structure_version_id)
372 start with pose.organization_id_parent =
373 to_char(nvl(l_organization_id,l_parent_id))
374 and pose.org_structure_version_id =
375 to_char(l_org_structure_version_id)
376 union select to_char(nvl(l_organization_id,l_parent_id))
377 from sys.dual) ; */
378 cursor csr_distinct_pyrl(l_organization_id number ,l_org_structure_version_id number,l_parent_id number, l_effective_date date ) is
379 SELECT distinct ppf.payroll_id
380 FROM per_time_periods ptp
381 ,pay_action_information pai_emp
382 ,pay_assignment_actions paa1
383 ,pay_payroll_actions ppa1
384 ,pay_all_payrolls_f ppf
385 WHERE ptp.time_period_id = pai_emp.action_information16
386 AND pai_emp.action_context_type = 'AAP'
387 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
388 AND pai_emp.action_context_id = paa1.assignment_action_id
389 AND paa1.payroll_action_id = ppa1.payroll_action_id
390 AND ppa1.action_type = 'X'
391 AND ppa1.report_type = 'KW_ARCHIVE'
392 AND ppa1.action_status = 'C'
393 AND paa1.action_status = 'C'
394 AND ptp.end_date = l_effective_date
395 AND ppf.payroll_id = ptp.payroll_id
396 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
397 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
398 from per_org_structure_elements pose
399 connect by pose.organization_id_parent =
400 prior pose.organization_id_child
401 and pose.org_structure_version_id =
402 to_char (l_org_structure_version_id)
403 start with pose.organization_id_parent =
404 to_char(nvl(l_organization_id,l_parent_id))
405 and pose.org_structure_version_id =
406 to_char(l_org_structure_version_id)
407 union select to_char(nvl(l_organization_id,l_parent_id))
408 from sys.dual) ;
409 /* CURSOR TO GET PAYROLL IDS DISTINCT FOR ORGANIZATION */
410 cursor csr_org_only_distinct_pyrl(l_organization_id number , l_effective_date date) is
411 SELECT distinct ppf.payroll_id
412 FROM per_time_periods ptp
413 ,pay_action_information pai_emp
414 ,pay_assignment_actions paa1
415 ,pay_action_interlocks lck
416 ,pay_payroll_actions ppa1
417 ,pay_all_payrolls_f ppf
418 WHERE ptp.time_period_id = pai_emp.action_information16
419 AND pai_emp.action_context_type = 'AAP'
420 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
421 AND lck.locking_action_id = pai_emp.action_context_id
422 AND lck.locked_action_id = paa1.assignment_action_id
423 AND paa1.payroll_action_id = ppa1.payroll_action_id
424 AND ppa1.action_type in ('R','Q')
425 AND ppa1.action_status = 'C'
426 AND paa1.action_status IN ('C','S')
427 AND ptp.end_date = l_effective_date
428 AND ppf.payroll_id = ptp.payroll_id
429 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
430 AND pai_emp.action_information2 = l_organization_id;
431 /* Cursor to tune the condition one sql performance */
432 Cursor csr_cond_1_tune (l_org_structure_version_id number,l_organization_id number, l_parent_id number) is
433 select to_char(pose.organization_id_child)
434 from per_org_structure_elements pose
435 connect by pose.organization_id_parent =
436 prior pose.organization_id_child
437 and pose.org_structure_version_id = to_char (l_org_structure_version_id)
438 start with pose.organization_id_parent = to_char(nvl(l_organization_id,l_parent_id))
439 and pose.org_structure_version_id = to_char(l_org_structure_version_id)
440 union select to_char(nvl(l_organization_id,l_parent_id)) from sys.dual;
441 /* SELECTS DATA FOR A GIVEN ORGANIZATION AND A PAYROLL COMBINATION */
442 /* Modifyig the cursor for performance issue for Bug 7632337 */
443 /*CURSOR csr_get_details (l_payroll_id number , l_effective_date date, l_org_name varchar2,p_order_1 varchar2,p_order_2 varchar2,p_order_3 varchar2) Is
444 SELECT distinct pai_emp.action_context_id arch_assact
445 ,to_char(ptp.payroll_id) org_pay
446 ,pai_emp.action_information1 full_name
447 ,pai_emp.action_information10
448 ,pai_emp.action_information15 organization
449 ,pai_emp.action_information19 position
450 ,pai_emp.action_information5 cost_center
451 ,pai_emp.action_information9 nationality
452 ,pai_emp.action_information17 job
453 ,pai_emp1.action_information9 title
454 ,pai_emp1.action_information10
455 ,pai_emp1.action_information11
456 ,ppf.payroll_name
457 ,nvl(pai_emp1.action_information13,0) ytd_earning
458 ,nvl(pai_emp1.action_information4,0) ytd_deduction
459 FROM per_time_periods ptp
460 ,pay_action_information pai_emp
461 ,pay_action_information pai_emp1
462 ,pay_assignment_actions paa1
463 ,pay_action_interlocks lck
464 ,pay_payroll_actions ppa1
465 ,pay_all_payrolls_f ppf
466 WHERE ptp.payroll_id = l_payroll_id
467 AND ptp.time_period_id = pai_emp.action_information16
468 AND pai_emp.action_context_id = pai_emp1.action_context_id
469 AND pai_emp.action_context_type = 'AAP'
470 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
471 AND pai_emp.action_information15 = l_org_name
472 AND pai_emp1.action_context_type = 'AAP'
473 AND pai_emp1.action_information_category(+) = 'ADDL EMPLOYEE DETAILS'
474 AND lck.locking_action_id = pai_emp.action_context_id
475 AND lck.locked_action_id = paa1.assignment_action_id
476 AND paa1.payroll_action_id = ppa1.payroll_action_id
477 AND ppa1.action_type in ('R','Q')
478 AND ppa1.action_status = 'C'
479 AND paa1.action_status = 'C'
480 AND ptp.end_date = l_effective_date
481 AND ppf.payroll_id = ptp.payroll_id
482 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
483 ORDER BY decode(p_order_1,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11),
484 decode(p_order_2,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1),
485 decode(p_order_3,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1); */
486
487 CURSOR csr_get_details (l_payroll_id number , l_effective_date date, l_org_name varchar2,p_order_1 varchar2,p_order_2 varchar2,p_order_3 varchar2) Is
488 SELECT distinct pai_emp.action_context_id arch_assact
489 ,to_char(ptp.payroll_id) org_pay
490 ,pai_emp.action_information1 full_name
491 ,pai_emp.action_information10
492 ,pai_emp.action_information15 organization
493 ,pai_emp.action_information19 position
494 ,pai_emp1.action_information7 cost_center
495 ,pai_emp.action_information9 nationality
496 ,pai_emp.action_information17 job
497 ,pai_emp1.action_information9 title
498 ,pai_emp1.action_information6
499 ,pai_emp1.action_information11
500 ,ppf.payroll_name
501 ,nvl(pai_emp1.action_information13,0) ytd_earning
502 ,nvl(pai_emp1.action_information4,0) ytd_deduction
503 FROM per_time_periods ptp
504 ,pay_action_information pai_emp
505 ,pay_action_information pai_emp1
506 ,pay_assignment_actions paa1
507 ,pay_payroll_actions ppa1
508 ,pay_all_payrolls_f ppf
509 WHERE ptp.payroll_id = l_payroll_id
510 AND ptp.time_period_id = pai_emp.action_information16
511 AND pai_emp.action_context_id = pai_emp1.action_context_id
512 AND pai_emp.action_context_type = 'AAP'
513 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
514 AND pai_emp.action_information15 = l_org_name
515 AND pai_emp1.action_context_type = 'AAP'
516 AND pai_emp1.action_information_category(+) = 'ADDL EMPLOYEE DETAILS'
517 AND ptp.end_date = l_effective_date
518 AND pai_emp.action_context_id = paa1.assignment_action_id
519 AND paa1.payroll_action_id = ppa1.payroll_action_id
520 AND ppa1.action_type = 'X'
521 and ppa1.report_type = 'KW_ARCHIVE'
522 AND ppa1.action_status = 'C'
523 AND paa1.action_status = 'C'
524 AND ppf.payroll_id = ptp.payroll_id
525 AND l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
526 ORDER BY decode(p_order_1,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11),
527 decode(p_order_2,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1),
528 decode(p_order_3,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1);
529 /* SELECT ELEMENTS AND DEDUCTIONS DETAILS */
530 CURSOR csr_get_earn_det (l_assact_id number) IS
531 /*The select statement is changed for fixing bug 6081731
532 SELECT pai_ele.action_context_id arch_payact
533 ,pay_v.action_context_id arch_assact
534 ,pay_v.narrative earn_element
535 ,pay_v.numeric_value earn_value
536 ,pai_ele.action_information7
537 FROM pay_action_information pai_ele
538 ,pay_emea_paymnts_action_info_v pay_v
539 ,pay_assignment_actions paa
540 WHERE paa.assignment_action_id = l_assact_id
541 AND paa.payroll_action_id = pai_ele.action_context_id
542 AND pai_ele.action_context_type = 'PA'
543 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
544 AND pai_ele.action_information7 IN ('E')
545 AND pay_v.action_context_id = paa.assignment_action_id
546 AND pay_v.narrative = pai_ele.action_information4
547 AND pay_v.payment_type NOT IN ('F');*/
548 SELECT ppa.payroll_action_id arch_payact
549 ,paa2.assignment_action_id arch_assact
550 ,pai.action_information4 earn_element
551 ,pet.result_value earn_value
552 ,pai.action_information7
553 FROM
554 pay_action_interlocks lck,
555 pay_assignment_actions paa1,
556 pay_assignment_actions paa2,
557 pay_payroll_actions ppa,
558 pay_action_information pai,
559 pay_emea_payment_values_v pet
560 WHERE
561 lck.locked_action_id = paa1.assignment_action_id AND
562 paa1.source_action_id IS NULL AND
563 paa1.payroll_action_id = ppa.payroll_action_id AND
564 ppa.action_type IN ('P','U') AND
565 ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id) AND
566 pai.action_context_type = 'PA' AND
567 pai.action_information_category = 'EMEA ELEMENT DEFINITION' AND
568 paa1.assignment_action_id = pet.assignment_action_id AND
569 pet.element_type_id = pai.action_information2 AND
570 pet.input_value_id = pai.action_information3 AND
571 lck.locking_action_id = paa2.assignment_action_id AND
572 paa2.payroll_action_id = pai.action_context_id AND
573 pai.action_information5 NOT IN ('F') AND
574 pai.action_information7 IN ('E') AND
575 paa2.assignment_action_id = l_assact_id;
576
577 CURSOR csr_get_ded_det (l_assact_id number) IS
578 /*SELECT pai_ele.action_context_id arch_payact
579 ,pay_v.action_context_id arch_assact
580 ,pay_v.narrative ded_element
581 ,pay_v.numeric_value ded_value
582 ,pai_ele.action_information7
583 FROM pay_action_information pai_ele
584 ,pay_emea_paymnts_action_info_v pay_v
585 ,pay_assignment_actions paa
586 WHERE paa.assignment_action_id= l_assact_id
587 AND paa.payroll_action_id = pai_ele.action_context_id
588 AND pai_ele.action_context_type = 'PA'
589 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
590 AND pai_ele.action_information7 IN ('D')
591 AND pay_v.action_context_id = paa.assignment_action_id
592 AND pay_v.narrative = pai_ele.action_information4
593 AND pay_v.payment_type NOT IN ('F');*/
594 SELECT ppa.payroll_action_id arch_payact
595 ,paa2.assignment_action_id arch_assact
596 ,pai.action_information4 ded_element
597 ,pet.result_value ded_value
598 ,pai.action_information7
599 FROM
600 pay_action_interlocks lck,
601 pay_assignment_actions paa1,
602 pay_assignment_actions paa2,
603 pay_payroll_actions ppa,
604 pay_action_information pai,
605 pay_emea_payment_values_v pet
606 WHERE
607 lck.locked_action_id = paa1.assignment_action_id AND
608 paa1.source_action_id IS NULL AND
609 paa1.payroll_action_id = ppa.payroll_action_id AND
610 ppa.action_type IN ('P','U') AND
611 ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id) AND
612 pai.action_context_type = 'PA' AND
613 pai.action_information_category = 'EMEA ELEMENT DEFINITION' AND
614 paa1.assignment_action_id = pet.assignment_action_id AND
615 pet.element_type_id = pai.action_information2 AND
616 pet.input_value_id = pai.action_information3 AND
617 lck.locking_action_id = paa2.assignment_action_id AND
618 paa2.payroll_action_id = pai.action_context_id AND
619 pai.action_information5 NOT IN ('F') AND
620 pai.action_information7 IN ('D') AND
621 paa2.assignment_action_id = l_assact_id;
622
623
624
625 /* SELECT PAYMENT METHOD DETAILS */
626 CURSOR csr_get_paymeth_det (l_assact_id number) IS
627 SELECT pen.org_payment_method_name
628 ,pen.segment1 bank_name
629 ,pen.segment2 branch_name
630 ,pen.segment4 account_number
631 ,pen.value pay_amount
632 ,pen.action_context_id
633 ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
634 FROM pay_emp_net_dist_action_info_v pen
635 ,pay_action_interlocks pai
636 ,pay_assignment_actions paa
637 ,pay_payroll_actions ppa
638 ,pay_pre_payments ppp
639 WHERE pen.action_context_id = l_assact_id
640 AND pen.action_context_id = pai.locking_action_id
641 AND pai.locked_action_id = paa.assignment_action_id
642 AND paa.payroll_action_id = ppa.payroll_action_id
643 AND ppa.action_type in ('P','U')
644 AND ppa.action_status = 'C'
645 AND paa.assignment_action_id = ppp.assignment_action_id
646 AND (ppp.personal_payment_method_id = pen.personal_payment_method_id
647 OR ppp.org_payment_method_id = pen.org_payment_method_id )
648 ORDER BY status, pay_amount;
649 /* Cursor to get the sum of the Earnings for particular payroll for summary */
650 /*CURSOR csr_get_sum_earn_summary (l_org_name varchar2 , l_payroll_id number , l_effective_date date) IS
651 SELECT sum(pay_v.numeric_value)
652 FROM pay_action_information pai_ele
653 ,pay_emea_paymnts_action_info_v pay_v
654 ,pay_assignment_actions paa
655 WHERE paa.payroll_action_id = pai_ele.action_context_id
656 AND pai_ele.action_context_type = 'PA'
657 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
658 AND pai_ele.action_information7 IN ('E')
659 AND pay_v.action_context_id = paa.assignment_action_id
660 AND pay_v.narrative = pai_ele.action_information4
661 AND pay_v.payment_type NOT IN ('F')
662 AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
663 FROM per_time_periods ptp
664 ,pay_action_information pai_emp
665 ,pay_assignment_actions paa1
666 ,pay_action_interlocks lck
667 ,pay_payroll_actions ppa1
668 WHERE ptp.payroll_id = l_payroll_id
669 AND ptp.time_period_id = pai_emp.action_information16
670 AND pai_emp.action_context_type = 'AAP'
671 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
672 AND pai_emp.action_information15 = l_org_name
673 AND lck.locking_action_id = pai_emp.action_context_id
674 AND lck.locked_action_id = paa1.assignment_action_id
675 AND paa1.payroll_action_id = ppa1.payroll_action_id
676 AND ppa1.action_type in ('R','Q')
677 AND ppa1.action_status = 'C'
678 AND paa1.action_status = 'C'
679 AND ptp.end_date = l_effective_date);*/
680 CURSOR csr_get_sum_earn (l_org_structure_version_id number,
681 l_organization_id number , l_payroll_id number , l_effective_date date) IS
682 SELECT sum(pay_v.numeric_value)
683 FROM pay_action_information pai_ele
684 ,pay_emea_paymnts_action_info_v pay_v
685 ,pay_assignment_actions paa
686 WHERE paa.payroll_action_id = pai_ele.action_context_id
687 AND pai_ele.action_context_type = 'PA'
688 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
689 AND pai_ele.action_information7 IN ('E')
690 AND pay_v.action_context_id = paa.assignment_action_id
691 AND pay_v.narrative = pai_ele.action_information4
692 AND pay_v.payment_type NOT IN ('F')
693 AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
694 FROM per_time_periods ptp
695 ,pay_action_information pai_emp
696 ,pay_assignment_actions paa1
697 ,pay_action_interlocks lck
698 ,pay_payroll_actions ppa1
699 WHERE ptp.payroll_id = l_payroll_id
700 AND ptp.time_period_id = pai_emp.action_information16
701 AND pai_emp.action_context_type = 'AAP'
702 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
703 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
704 from per_org_structure_elements pose
705 connect by pose.organization_id_parent =
706 prior pose.organization_id_child
707 and pose.org_structure_version_id =
708 to_char (l_org_structure_version_id)
709 start with pose.organization_id_parent = to_char(l_organization_id)
710 and pose.org_structure_version_id = to_char(l_org_structure_version_id)
711 union select to_char(l_organization_id) from sys.dual)
712 /*AND pai_emp.action_information15 = l_org_name*/
713 AND lck.locking_action_id = pai_emp.action_context_id
714 AND lck.locked_action_id = paa1.assignment_action_id
715 AND paa1.payroll_action_id = ppa1.payroll_action_id
716 AND ppa1.action_type in ('R','Q')
717 AND ppa1.action_status = 'C'
718 AND paa1.action_status IN ('C','S')
719 AND ptp.end_date = l_effective_date);
720 /****Cursor split into 2 for fixing performance bug
721 CURSOR csr_get_sum_earn_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
722 --*******Fixed during performance bugs*****
723 SELECT sum(pay_v.numeric_value)
724 FROM pay_assignment_actions paa
725 ,per_time_periods ptp
726 ,pay_assignment_actions paa1
727 ,pay_action_interlocks lck
728 ,pay_payroll_actions ppa1
729 ,pay_action_information pai_ele
730 ,pay_action_information pai_emp
731 ,pay_emea_paymnts_action_info_v pay_v
732 WHERE paa.payroll_action_id = pai_ele.action_context_id
733 AND pai_ele.action_context_type = 'PA'
734 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
735 AND pai_ele.action_information7 IN ('E')
736 AND pay_v.action_context_id = paa.assignment_action_id
737 AND pay_v.narrative = pai_ele.action_information4
738 AND pay_v.payment_type NOT IN ('F')
739 AND paa.assignment_action_id = pai_emp.action_context_id
740 AND ptp.payroll_id = l_payroll_id
741 AND ptp.time_period_id = pai_emp.action_information16
742 AND pai_emp.action_context_type = 'AAP'
743 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
744 AND pai_emp.action_information2 = l_organization_id
745 --AND pai_emp.action_information15 = l_org_name
746 AND lck.locking_action_id = pai_emp.action_context_id
747 AND lck.locked_action_id = paa1.assignment_action_id
748 AND paa1.payroll_action_id = ppa1.payroll_action_id
749 AND ppa1.action_type in ('R','Q')
750 AND ppa1.action_status = 'C'
751 AND paa1.action_status = 'C'
752 AND ptp.end_date = l_effective_date;
753 --***** End of fixed during performance bugs ****
754 */
755
756 CURSOR csr_seoo_split_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
757 select pai_emp.action_context_id arch_assact
758 from per_time_periods ptp
759 ,pay_action_information pai_emp
760 ,pay_assignment_actions paa1
761 ,pay_action_interlocks lck
762 ,pay_payroll_actions ppa1
763 where ptp.payroll_id = l_payroll_id
764 and ptp.time_period_id = pai_emp.action_information16
765 and pai_emp.action_context_type = 'AAP'
766 and pai_emp.action_information_category = 'EMPLOYEE DETAILS'
767 and pai_emp.action_information2 = l_organization_id
768 /*and pai_emp.action_information15 = l_org_name*/
769 and lck.locking_action_id = pai_emp.action_context_id
770 and lck.locked_action_id = paa1.assignment_action_id
771 and paa1.payroll_action_id = ppa1.payroll_action_id
772 and ptp.payroll_id = ppa1.payroll_id
773 and ppa1.action_type in ('R','Q')
774 and ppa1.action_status = 'C'
775 and paa1.action_status IN ('C','S')
776 and ptp.end_date = l_effective_date;
777 CURSOR csr_seoo_split_2 (l_arch_assact number) IS
778 select SUM(pay_v.numeric_value)
779 from pay_action_information pai_ele
780 ,pay_emea_paymnts_action_info_v pay_v
781 ,pay_assignment_actions paa
782 where paa.payroll_action_id = pai_ele.action_context_id
783 and pai_ele.action_context_type = 'PA'
784 and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
785 and pai_ele.action_information7 in ('E')
786 and pay_v.action_context_id = paa.assignment_action_id
787 and pay_v.narrative = pai_ele.action_information4
788 and pay_v.payment_type not in ('F')
789 and paa.assignment_action_id = l_arch_assact;
790
791
792
793 /* Cursor to get the sum of the Deductions for particular payroll for summary */
794 --CURSOR csr_get_sum_ded_summary (l_org_name varchar2 , l_payroll_id number , l_effective_date date) IS
795 CURSOR csr_get_sum_ded (l_org_structure_version_id number,
796 l_organization_id number , l_payroll_id number , l_effective_date date) IS
797 SELECT sum(pay_v.numeric_value)
798 FROM pay_action_information pai_ele
799 ,pay_emea_paymnts_action_info_v pay_v
800 ,pay_assignment_actions paa
801 WHERE paa.payroll_action_id = pai_ele.action_context_id
802 AND pai_ele.action_context_type = 'PA'
803 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
804 AND pai_ele.action_information7 IN ('D')
805 AND pay_v.action_context_id = paa.assignment_action_id
806 AND pay_v.narrative = pai_ele.action_information4
807 AND pay_v.payment_type NOT IN ('F')
808 AND paa.assignment_action_id in (SELECT pai_emp.action_context_id arch_assact
809 FROM per_time_periods ptp
810 ,pay_action_information pai_emp
811 ,pay_assignment_actions paa1
812 ,pay_action_interlocks lck
813 ,pay_payroll_actions ppa1
814 WHERE ptp.payroll_id = l_payroll_id
815 AND ptp.time_period_id = pai_emp.action_information16
816 AND pai_emp.action_context_type = 'AAP'
817 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
818 AND pai_emp.action_information2 in (select to_char(pose.organization_id_child)
819 from per_org_structure_elements pose
820 connect by pose.organization_id_parent =
821 prior pose.organization_id_child
822 and pose.org_structure_version_id =
823 to_char (l_org_structure_version_id)
824 start with pose.organization_id_parent = to_char(l_organization_id)
825 and pose.org_structure_version_id = to_char(l_org_structure_version_id)
826 union select to_char(l_organization_id) from sys.dual)
827 /*AND pai_emp.action_information15 = l_org_name*/
828 AND lck.locking_action_id = pai_emp.action_context_id
829 AND lck.locked_action_id = paa1.assignment_action_id
830 AND paa1.payroll_action_id = ppa1.payroll_action_id
831 AND ppa1.action_type in ('R','Q')
832 AND ppa1.action_status = 'C'
833 AND paa1.action_status IN ('C','S')
834 AND ptp.end_date = l_effective_date);
835 /****Cursor split into 2 for fixing performance bug
836 CURSOR csr_get_sum_ded_only_org (l_organization_id number , l_payroll_id number , l_effective_date date) IS
837 --******* Fixed during performance bugs*******
838 SELECT sum(pay_v.numeric_value)
839 FROM pay_assignment_actions paa
840 ,per_time_periods ptp
841 ,pay_assignment_actions paa1
842 ,pay_action_interlocks lck
843 ,pay_payroll_actions ppa1
844 ,pay_action_information pai_ele
845 ,pay_action_information pai_emp
846 ,pay_emea_paymnts_action_info_v pay_v
847 WHERE paa.payroll_action_id = pai_ele.action_context_id
848 AND pai_ele.action_context_type = 'PA'
849 AND pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
850 AND pai_ele.action_information7 IN ('D')
851 AND pay_v.action_context_id = paa.assignment_action_id
852 AND pay_v.narrative = pai_ele.action_information4
853 AND pay_v.payment_type NOT IN ('F')
854 AND paa.assignment_action_id = pai_emp.action_context_id
855 AND ptp.payroll_id = l_payroll_id
856 AND ptp.time_period_id = pai_emp.action_information16
857 AND pai_emp.action_context_type = 'AAP'
858 AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
859 AND pai_emp.action_information2 = l_organization_id
860 --AND pai_emp.action_information15 = l_org_name
861 AND lck.locking_action_id = pai_emp.action_context_id
862 AND lck.locked_action_id = paa1.assignment_action_id
863 AND paa1.payroll_action_id = ppa1.payroll_action_id
864 AND ppa1.action_type in ('R','Q')
865 AND ppa1.action_status = 'C'
866 AND paa1.action_status = 'C'
867 AND ptp.end_date = l_effective_date;
868 --********** End of fixed during performance bugs ********
869 */
870
871 CURSOR csr_sdoo_split_1 (l_organization_id number , l_payroll_id number , l_effective_date date) IS
872 select pai_emp.action_context_id arch_assact
873 from per_time_periods ptp
874 ,pay_action_information pai_emp
875 ,pay_assignment_actions paa1
876 ,pay_action_interlocks lck
877 ,pay_payroll_actions ppa1
878 where ptp.payroll_id = l_payroll_id
879 and ptp.time_period_id = pai_emp.action_information16
880 and pai_emp.action_context_type = 'AAP'
881 and pai_emp.action_information_category = 'EMPLOYEE DETAILS'
882 and pai_emp.action_information2 = l_organization_id
883 /*and pai_emp.action_information15 = l_org_name*/
884 and lck.locking_action_id = pai_emp.action_context_id
885 and lck.locked_action_id = paa1.assignment_action_id
886 and paa1.payroll_action_id = ppa1.payroll_action_id
887 and ptp.payroll_id = ppa1.payroll_id
888 and ppa1.action_type in ('R','Q')
889 and ppa1.action_status = 'C'
890 and paa1.action_status IN ('C','S')
891 and ptp.end_date = l_effective_date;
892 CURSOR csr_sdoo_split_2 (l_arch_assact number) IS
893 select SUM(pay_v.numeric_value)
894 from pay_action_information pai_ele
895 ,pay_emea_paymnts_action_info_v pay_v
896 ,pay_assignment_actions paa
897 where paa.payroll_action_id = pai_ele.action_context_id
898 and pai_ele.action_context_type = 'PA'
899 and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
900 and pai_ele.action_information7 in ('D')
901 and pay_v.action_context_id = paa.assignment_action_id
902 and pay_v.narrative = pai_ele.action_information4
903 and pay_v.payment_type not in ('F')
904 and paa.assignment_action_id = l_arch_assact;
905
906
907 begin
908 p_effective_date := to_date(p_effective_char_date,'YYYY/MM/DD HH24:MI:SS');
909 vXMLtable.DELETE;
910 vXMLTable_summary.DELETE;
911 vCtr_summary :=1;
912 vCtr :=1;
913 if p_sort_order1 = 'EMP_NO' then
914 l_order_1 := 'employee_number';
915 elsif p_sort_order1 = 'EMP_FIRST' then
916 l_order_1 := 'first_name';
917 else
918 l_order_1 := 'family_name';
919 end if;
920 if p_sort_order2 = 'EMP_NO' then
921 l_order_2 := 'employee_number';
922 elsif p_sort_order2 = 'EMP_FIRST' then
923 l_order_2 := 'first_name';
924 elsif p_sort_order2 = 'EMP_FAMILY' then
925 l_order_2 := 'family_name';
926 else
927 l_order_2 := null;
928 end if;
929 if p_sort_order3 = 'EMP_NO' then
930 l_order_3 := 'employee_number';
931 elsif p_sort_order3 = 'EMP_FIRST' then
932 l_order_3 := 'first_name';
933 elsif p_sort_order3 = 'EMP_FAMILY' then
934 l_order_3 := 'family_name';
935 else
936 l_order_3 := null;
937 end if;
938 open csr_get_payroll_name(p_payroll_id , p_effective_date);
939 fetch csr_get_payroll_name into l_header_pyrl_name;
940 close csr_get_payroll_name;
941 open csr_get_organization_name(p_organization_id);
942 fetch csr_get_organization_name into l_header_organization_name;
943 close csr_get_organization_name;
944 vXMLTable(vCtr).TagName := 'page_number_label';
945 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAGE_NUMBER_LABEL');
946 vCtr := vCtr + 1;
947 vXMLTable(vCtr).TagName := 'of_label';
948 vXMLTable(vCtr).TagValue :=get_lookup_meaning('KW_FORM_LABELS','OF_LABEL');
949 vCtr := vCtr + 1;
950 If p_payroll_id is null then
951 open csr_get_bg_id_org(p_organization_id);
952 fetch csr_get_bg_id_org into l_org_bg_id;
953 close csr_get_bg_id_org;
954 set_currency_mask(l_org_bg_id);
955 if p_org_structure_version_id is not null then
956 if p_organization_id is null then
957 begin
958 select distinct pose.organization_id_parent
959 into l_parent_id
960 from per_org_structure_elements pose
961 where pose.org_structure_version_id = p_org_structure_version_id
962 and pose.organization_id_parent not in (select pose1.organization_id_child
963 from per_org_structure_elements pose1
964 where pose1.org_structure_version_id = p_org_structure_version_id);
965 exception
966 when others then
967 l_err := 1;
968 end;
969 end if;
970 if l_err = 0 then
971 /* CONDITION ONE */
972 i:=1;
973 f:=1;
974 open csr_condition_one (p_organization_id ,p_org_structure_version_id,l_parent_id,p_effective_date );
975 fetch csr_condition_one into tab_org_data_init(i).org_name,tab_org_data_init(i).payroll_id;
976 if csr_condition_one %notfound then
977 close csr_condition_one;
978 else
979 close csr_condition_one ;
980 open csr_condition_one (p_organization_id , p_org_structure_version_id,l_parent_id ,p_effective_date);
981 loop
982 fetch csr_condition_one into tab_org_data(i).org_name,tab_org_data(i).payroll_id;
983 i := i + 1;
984 if csr_condition_one%notfound then
985 close csr_condition_one;
986 EXIT;
987 end if;
988 end loop;
989 end if;
990 open csr_distinct_pyrl (p_organization_id ,p_org_structure_version_id,l_parent_id,p_effective_date );
991 fetch csr_distinct_pyrl into tab_sum_data_init(f).payroll_id;
992 if csr_distinct_pyrl %notfound then
993 close csr_distinct_pyrl;
994 else
995 close csr_distinct_pyrl ;
996 open csr_distinct_pyrl (p_organization_id , p_org_structure_version_id,l_parent_id ,p_effective_date);
997 loop
998 fetch csr_distinct_pyrl into tab_sum_data(f).payroll_id;
999 f := f + 1;
1000 if csr_distinct_pyrl%notfound then
1001 close csr_distinct_pyrl;
1002 EXIT;
1003 end if;
1004 end loop;
1005 end if;
1006 else
1007 /* CONDITION TWO */
1008 i:=1;
1009 f:=1;
1010 open csr_condition_two (p_organization_id ,p_effective_date);
1011 fetch csr_condition_two into tab_org_data_init(i).org_name,tab_org_data_init(i).payroll_id;
1012 if csr_condition_two %notfound then
1013 close csr_condition_two;
1014 else
1015 close csr_condition_two ;
1016 open csr_condition_two (p_organization_id ,p_effective_date);
1017 loop
1018 fetch csr_condition_two into tab_org_data(i).org_name,tab_org_data(i).payroll_id;
1019 i := i + 1;
1020 if csr_condition_two%notfound then
1021 close csr_condition_two;
1022 EXIT;
1023 end if;
1024 end loop;
1025 end if;
1026 open csr_org_only_distinct_pyrl (p_organization_id ,p_effective_date);
1027 fetch csr_org_only_distinct_pyrl into tab_sum_data_init(f).payroll_id;
1028 if csr_org_only_distinct_pyrl %notfound then
1029 close csr_org_only_distinct_pyrl;
1030 else
1031 close csr_org_only_distinct_pyrl ;
1032 open csr_org_only_distinct_pyrl (p_organization_id ,p_effective_date);
1033 loop
1034 fetch csr_org_only_distinct_pyrl into tab_sum_data(f).payroll_id;
1035 f := f + 1;
1036 if csr_org_only_distinct_pyrl%notfound then
1037 close csr_org_only_distinct_pyrl;
1038 EXIT;
1039 end if;
1040 end loop;
1041 end if;
1042 end if;
1043 elsif p_organization_id is null then
1044 /* CONDITION THREE */
1045 i:=1;
1046 open csr_condition_three (p_effective_date);
1047 fetch csr_condition_three into tab_org_data_init(i).org_name,tab_org_data_init(i).payroll_id;
1048 if csr_condition_three %notfound then
1049 close csr_condition_three;
1050 else
1051 close csr_condition_three ;
1052 open csr_condition_three (p_effective_date);
1053 loop
1054 fetch csr_condition_three into tab_org_data(i).org_name,tab_org_data(i).payroll_id;
1055 i := i + 1;
1056 if csr_condition_three%notfound then
1057 close csr_condition_three;
1058 EXIT;
1059 end if;
1060 end loop;
1061 end if;
1062 else
1063 /* CONDITION TWO */
1064 i:=1;
1065 f:=1;
1066 open csr_condition_two (p_organization_id ,p_effective_date);
1067 fetch csr_condition_two into tab_org_data_init(i).org_name,tab_org_data_init(i).payroll_id;
1068 if csr_condition_two %notfound then
1069 close csr_condition_two;
1070 else
1071 close csr_condition_two ;
1072 open csr_condition_two (p_organization_id ,p_effective_date);
1073 loop
1074 fetch csr_condition_two into tab_org_data(i).org_name,tab_org_data(i).payroll_id;
1075 i := i + 1;
1076 if csr_condition_two%notfound then
1077 close csr_condition_two;
1078 EXIT;
1079 end if;
1080 end loop;
1081 end if;
1082 open csr_org_only_distinct_pyrl (p_organization_id ,p_effective_date);
1083 fetch csr_org_only_distinct_pyrl into tab_sum_data_init(f).payroll_id;
1084 if csr_org_only_distinct_pyrl %notfound then
1085 close csr_org_only_distinct_pyrl;
1086 else
1087 close csr_org_only_distinct_pyrl ;
1088 open csr_org_only_distinct_pyrl (p_organization_id ,p_effective_date);
1089 loop
1090 fetch csr_org_only_distinct_pyrl into tab_sum_data(f).payroll_id;
1091 f := f + 1;
1092 if csr_org_only_distinct_pyrl%notfound then
1093 close csr_org_only_distinct_pyrl;
1094 EXIT;
1095 end if;
1096 end loop;
1097 end if;
1098
1099 end if;
1100 ELSE
1101 open csr_get_bg_id_pay(p_payroll_id , p_effective_date);
1102 fetch csr_get_bg_id_pay into l_pay_bg_id;
1103 close csr_get_bg_id_pay;
1104 set_currency_mask(l_pay_bg_id);
1105 i:=1;
1106 open csr_get_orgs_for_payroll (p_payroll_id , p_effective_date);
1107 fetch csr_get_orgs_for_payroll into tab_org_data_init(i).org_name,tab_org_data_init(i).payroll_id;
1108 if csr_get_orgs_for_payroll%notfound then
1109 close csr_get_orgs_for_payroll ;
1110 else
1111 close csr_get_orgs_for_payroll ;
1112 open csr_get_orgs_for_payroll (p_payroll_id , p_effective_date);
1113 loop
1114 fetch csr_get_orgs_for_payroll into tab_org_data(i).org_name,tab_org_data(i).payroll_id;
1115 if csr_get_orgs_for_payroll%notfound then
1116 close csr_get_orgs_for_payroll ;
1117 EXIT;
1118 end if;
1119 i := i + 1;
1120 end loop;
1121 end if;
1122 END IF;
1123 If tab_org_data.count <>0 then
1124 For i in tab_org_data.first..tab_org_data.last
1125 LOOP
1126 if i = tab_org_data.first then
1127 /*if l_w_indicator = 2 then
1128 l_w_indicator := 0;
1129 end if;*/
1130 open csr_get_payroll_name (tab_org_data(i).payroll_id,p_effective_date);
1131 fetch csr_get_payroll_name into l_header_pyrl_name;
1132 close csr_get_payroll_name;
1133 l_header_organization_name := tab_org_data(i).org_name;
1134 vXMLTable(vCtr).TagName := 'payroll_register_label';
1135 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_REGISTER_LABEL');
1136 vCtr := vCtr + 1;
1137 vXMLTable(vCtr).TagName := 'period_start_date_label';
1138 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_START_DATE_LABEL');
1139 vCtr := vCtr + 1;
1140 vXMLTable(vCtr).TagName := 'period_start_date_value';
1141 vXMLTable(vCtr).TagValue := trunc(p_effective_date,'MM');
1142 vCtr := vCtr + 1;
1143 vXMLTable(vCtr).TagName := 'period_end_date_label';
1144 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_END_DATE_LABEL');
1145 vCtr := vCtr + 1;
1146 vXMLTable(vCtr).TagName := 'period_end_date_value';
1147 vXMLTable(vCtr).TagValue := last_day(p_effective_date);
1148 vCtr := vCtr + 1;
1149 vXMLTable(vCtr).TagName := 'date_label';
1150 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DATE_LABEL');
1151 vCtr := vCtr + 1;
1152 vXMLTable(vCtr).TagName := 'date_value';
1153 vXMLTable(vCtr).TagValue := p_effective_date;
1154 vCtr := vCtr + 1;
1155 vXMLTable(vCtr).TagName := 'organization_value';
1156 vXMLTable(vCtr).TagValue := l_header_organization_name;
1157 vCtr := vCtr + 1;
1158 vXMLTable(vCtr).TagName := 'organization_label';
1159 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_LABEL');
1160 vCtr := vCtr + 1;
1161 vXMLTable(vCtr).TagName := 'payroll_name_label';
1162 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_NAME_LABEL');
1163 vCtr := vCtr + 1;
1164 vXMLTable(vCtr).TagName := 'payroll_name_value';
1165 vXMLTable(vCtr).TagValue := nvl(l_header_pyrl_name,' ');
1166 vCtr := vCtr + 1;
1167 vXMLTable(vCtr).TagName := 'Employee_data_label';
1168 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_DATA_LABEL');
1169 vCtr := vCtr + 1;
1170 elsif tab_org_data(i).org_name<>tab_org_data(i-1).org_name or tab_org_data(i).payroll_id<>tab_org_data(i-1).payroll_id then
1171 if l_emp_count <>0 then
1172 vXMLTable(vCtr).TagName := 'break_dummy';
1173 vXMLTable(vCtr).TagValue := ' ';
1174 vCtr := vCtr + 1;
1175 end if;
1176 l_emp_count := 0;
1177 l_w_indicator := 0;
1178 open csr_get_payroll_name (tab_org_data(i).payroll_id,p_effective_date);
1179 fetch csr_get_payroll_name into l_header_pyrl_name;
1180 close csr_get_payroll_name;
1181 l_header_organization_name := tab_org_data(i).org_name;
1182 vXMLTable(vCtr).TagName := 'payroll_register_label';
1183 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_REGISTER_LABEL');
1184 vCtr := vCtr + 1;
1185 vXMLTable(vCtr).TagName := 'period_start_date_label';
1186 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_START_DATE_LABEL');
1187 vCtr := vCtr + 1;
1188 vXMLTable(vCtr).TagName := 'period_start_date_value';
1189 vXMLTable(vCtr).TagValue := trunc(p_effective_date,'MM');
1190 vCtr := vCtr + 1;
1191 vXMLTable(vCtr).TagName := 'period_end_date_label';
1192 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_END_DATE_LABEL');
1193 vCtr := vCtr + 1;
1194 vXMLTable(vCtr).TagName := 'period_end_date_value';
1195 vXMLTable(vCtr).TagValue := last_day(p_effective_date);
1196 vCtr := vCtr + 1;
1197 vXMLTable(vCtr).TagName := 'date_label';
1198 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DATE_LABEL');
1199 vCtr := vCtr + 1;
1200 vXMLTable(vCtr).TagName := 'date_value';
1201 vXMLTable(vCtr).TagValue := p_effective_date;
1202 vCtr := vCtr + 1;
1203 vXMLTable(vCtr).TagName := 'organization_value';
1204 vXMLTable(vCtr).TagValue := l_header_organization_name;
1205 vCtr := vCtr + 1;
1206 vXMLTable(vCtr).TagName := 'organization_label';
1207 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_LABEL');
1208 vCtr := vCtr + 1;
1209 vXMLTable(vCtr).TagName := 'payroll_name_label';
1210 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_NAME_LABEL');
1211 vCtr := vCtr + 1;
1212 vXMLTable(vCtr).TagName := 'payroll_name_value';
1213 vXMLTable(vCtr).TagValue := nvl(l_header_pyrl_name,' ');
1214 vCtr := vCtr + 1;
1215 vXMLTable(vCtr).TagName := 'Employee_data_label';
1216 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_DATA_LABEL');
1217 vCtr := vCtr + 1;
1218 end if;
1219 open csr_get_details (tab_org_data(i).payroll_id, p_effective_date , tab_org_data(i).org_name , l_order_1 , l_order_2 , l_order_3);
1220 fetch csr_get_details into tab_dets_data_init(j).r_assact_id,
1221 tab_dets_data_init(j).r_org_pay_id,tab_dets_data_init(j).r_full_name,tab_dets_data_init(j).r_emp_no,
1222 tab_dets_data_init(j).r_org_name,tab_dets_data_init(j).r_position,tab_dets_data_init(j).r_cost_center,
1223 tab_dets_data_init(j).r_nationality,tab_dets_data_init(j).r_job,tab_dets_data_init(j).r_title,
1224 tab_dets_data_init(j).r_first_name ,tab_dets_data_init(j).r_family_name ,
1225 tab_dets_data_init(j).r_payroll_name,tab_dets_data_init(j).r_ytd_earning,tab_dets_data_init(j).r_ytd_deduction;
1226 If csr_get_details % notfound then
1227 close csr_get_details;
1228 else
1229 j := 1;
1230 close csr_get_details;
1231 open csr_get_details (tab_org_data(i).payroll_id,p_effective_date,tab_org_data(i).org_name, l_order_1 , l_order_2 , l_order_3);
1232 LOOP
1233 if l_w_indicator = 2 then
1234 l_w_indicator := 0;
1235 if l_emp_count <>0 then /***************???????????????????????***************/
1236 open csr_get_payroll_name (tab_org_data(i).payroll_id,p_effective_date);
1237 fetch csr_get_payroll_name into l_header_pyrl_name;
1238 close csr_get_payroll_name;
1239 l_header_organization_name := tab_org_data(i).org_name;
1240 vXMLTable(vCtr).TagName := 'payroll_register_label';
1241 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_REGISTER_LABEL');
1242 vCtr := vCtr + 1;
1243 vXMLTable(vCtr).TagName := 'period_start_date_label';
1244 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_START_DATE_LABEL');
1245 vCtr := vCtr + 1;
1246 vXMLTable(vCtr).TagName := 'period_start_date_value';
1247 vXMLTable(vCtr).TagValue := trunc(p_effective_date,'MM');
1248 vCtr := vCtr + 1;
1249 vXMLTable(vCtr).TagName := 'period_end_date_label';
1250 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_END_DATE_LABEL');
1251 vCtr := vCtr + 1;
1252 vXMLTable(vCtr).TagName := 'period_end_date_value';
1253 vXMLTable(vCtr).TagValue := last_day(p_effective_date);
1254 vCtr := vCtr + 1;
1255 vXMLTable(vCtr).TagName := 'date_label';
1256 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DATE_LABEL');
1257 vCtr := vCtr + 1;
1258 vXMLTable(vCtr).TagName := 'date_value';
1259 vXMLTable(vCtr).TagValue := p_effective_date;
1260 vCtr := vCtr + 1;
1261 vXMLTable(vCtr).TagName := 'organization_label';
1262 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_LABEL');
1263 vCtr := vCtr + 1;
1264 vXMLTable(vCtr).TagName := 'organization_value';
1265 vXMLTable(vCtr).TagValue := l_header_organization_name;
1266 vCtr := vCtr + 1;
1267 vXMLTable(vCtr).TagName := 'payroll_name_label';
1268 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_NAME_LABEL');
1269 vCtr := vCtr + 1;
1270 vXMLTable(vCtr).TagName := 'payroll_name_value';
1271 vXMLTable(vCtr).TagValue := nvl(l_header_pyrl_name,' ');
1272 vCtr := vCtr + 1;
1273 end if; /*******????????????????????????????????**********/
1274 /*
1275 vXMLTable(vCtr).TagName := 'Employee_data_label';
1276 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_DATA_LABEL');
1277 vCtr := vCtr + 1;
1278 */
1279 end if;
1280 fetch csr_get_details into tab_dets_data(j).r_assact_id,
1281 tab_dets_data(j).r_org_pay_id,tab_dets_data(j).r_full_name,tab_dets_data(j).r_emp_no,
1282 tab_dets_data(j).r_org_name,tab_dets_data(j).r_position,tab_dets_data(j).r_cost_center,
1283 tab_dets_data(j).r_nationality,tab_dets_data(j).r_job,tab_dets_data(j).r_title,
1284 tab_dets_data(j).r_first_name ,tab_dets_data(j).r_family_name ,tab_dets_data(j).r_payroll_name,
1285 tab_dets_data(j).r_ytd_earning,tab_dets_data(j).r_ytd_deduction;
1286 exit when csr_get_details%notfound;
1287 /* POPULATE THE XML for emp details*/
1288 vXMLTable(vCtr).TagName := 'employee_name_label';
1289 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_NAME_LABEL');
1290 vCtr := vCtr + 1;
1291 vXMLTable(vCtr).TagName := 'employee_name_value';
1292 vXMLTable(vCtr).TagValue := nvl(substr(tab_dets_data(j).r_full_name,1,120),' ');
1293 vCtr := vCtr + 1;
1294 vXMLTable(vCtr).TagName := 'ul_1';
1295 vXMLTable(vCtr).TagValue := '- -';
1296 vCtr := vCtr + 1;
1297 vXMLTable(vCtr).TagName := 'alternate_name_label';
1298 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ALTERNATE_NAME_LABEL');
1299 vCtr := vCtr + 1;
1300 vXMLTable(vCtr).TagName := 'alternate_name_value';
1301 vXMLTable(vCtr).TagValue := substr((tab_dets_data(j).r_first_name || ' '||tab_dets_data(j).r_family_name),1,120) ;
1302 vCtr := vCtr + 1;
1303 vXMLTable(vCtr).TagName := 'cost_center_label';
1304 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','COST_CENTER_LABEL');
1305 vCtr := vCtr + 1;
1306 vXMLTable(vCtr).TagName := 'cost_center_value';
1307 vXMLTable(vCtr).TagValue := tab_dets_data(j).r_cost_center;
1308 vCtr := vCtr + 1;
1309 vXMLTable(vCtr).TagName := 'organization_name_label';
1310 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_NAME_LABEL');
1311 vCtr := vCtr + 1;
1312 vXMLTable(vCtr).TagName := 'organization_name_value';
1313 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_org_name,' ');
1314 vCtr := vCtr + 1;
1315 vXMLTable(vCtr).TagName := 'nationality_label';
1316 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','NATIONALITY_LABEL');
1317 vCtr := vCtr + 1;
1318 vXMLTable(vCtr).TagName := 'nationality_value';
1319 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_nationality,' ');
1320 vCtr := vCtr + 1;
1321 vXMLTable(vCtr).TagName := 'job_label';
1322 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','JOB_LABEL_PYRG');
1323 vCtr := vCtr + 1;
1324 vXMLTable(vCtr).TagName := 'job_value';
1325 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_job,' ');
1326 vCtr := vCtr + 1;
1327 vXMLTable(vCtr).TagName := 'position_label';
1328 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','POSITION_LABEL');
1329 vCtr := vCtr + 1;
1330 vXMLTable(vCtr).TagName := 'position_value';
1331 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_position,' ');
1332 vCtr := vCtr + 1;
1333 vXMLTable(vCtr).TagName := 'employee_number_label';
1334 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_NUMBER_PYRG');
1335 vCtr := vCtr + 1;
1336 vXMLTable(vCtr).TagName := 'employee_number_value';
1337 vXMLTable(vCtr).TagValue := tab_dets_data(j).r_emp_no;
1338 vCtr := vCtr + 1;
1339 vXMLTable(vCtr).TagName := 'ul_2';
1340 vXMLTable(vCtr).TagValue := '- -';
1341 vCtr := vCtr + 1;
1342 /* END POPULATE THE XML for emp details*/
1343 open csr_get_earn_det (tab_dets_data(j).r_assact_id);
1344 fetch csr_get_earn_det into tab_earn_data_init(k).r_payact_earn_id,
1345 tab_earn_data_init(k).r_assact_earn_id,tab_earn_data_init(k).r_earn_narrative,
1346 tab_earn_data_init(k).r_earn_numeric_value ,tab_earn_data_init(k).r_earn_element_type;
1347 If csr_get_earn_det % notfound then
1348 close csr_get_earn_det;
1349 else
1350 k := 1;
1351 vXMLTable(vCtr).TagName := 'earnings_label';
1352 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','EARNINGS_LABEL');
1353 vCtr := vCtr + 1;
1354 vXMLTable(vCtr).TagName := 'amount_e_label';
1355 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','AMOUNT_E_LABEL');
1356 vCtr := vCtr + 1;
1357 close csr_get_earn_det;
1358 open csr_get_earn_det(tab_dets_data(j).r_assact_id);
1359 LOOP
1360 fetch csr_get_earn_det into tab_earn_data(k).r_payact_earn_id,
1361 tab_earn_data(k).r_assact_earn_id,tab_earn_data(k).r_earn_narrative,
1362 tab_earn_data(k).r_earn_numeric_value ,tab_earn_data(k).r_earn_element_type;
1363 exit when csr_get_earn_det % notfound;
1364 emp_earn_sum := emp_earn_sum + nvl(to_number(tab_earn_data(k).r_earn_numeric_value,'FM9999999999999999999999990D000'),0);
1365 /* POPULATE THE XML for earnings details */
1366 /* END POPULATE THE XML for earnings details*/
1367 k := k + 1;
1368 END LOOP;
1369 close csr_get_earn_det;
1370 end if;
1371 open csr_get_ded_det (tab_dets_data(j).r_assact_id);
1372 fetch csr_get_ded_det into tab_ded_data_init(l).r_payact_ded_id,
1373 tab_ded_data_init(l).r_assact_ded_id,tab_ded_data_init(l).r_ded_narrative,
1374 tab_ded_data_init(l).r_ded_numeric_value ,tab_ded_data_init(l).r_ded_element_type;
1375 If csr_get_ded_det % notfound then
1376 close csr_get_ded_det;
1377 else
1378 l := 1;
1379 close csr_get_ded_det;
1380 open csr_get_ded_det(tab_dets_data(j).r_assact_id);
1381 vXMLTable(vCtr).TagName := 'deductions_label';
1382 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DEDUCTIONS_LABEL');
1383 vCtr := vCtr + 1;
1384 vXMLTable(vCtr).TagName := 'amount_d_label';
1385 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','AMOUNT_E_LABEL');
1386 vCtr := vCtr + 1;
1387 LOOP
1388 fetch csr_get_ded_det into tab_ded_data(l).r_payact_ded_id,
1389 tab_ded_data(l).r_assact_ded_id,tab_ded_data(l).r_ded_narrative,
1390 tab_ded_data(l).r_ded_numeric_value ,tab_ded_data(l).r_ded_element_type;
1391 exit when csr_get_ded_det % notfound;
1392 emp_ded_sum := emp_ded_sum + nvl(to_number(tab_ded_data(l).r_ded_numeric_value,'FM9999999999999999999999990D000'),0);
1393 /* POPULATE THE XML for deductions details */
1394 /*END POPULATE THE XML for deductions details */
1395 l := l + 1;
1396 END LOOP;
1397 close csr_get_ded_det;
1398 end if;
1399 if nvl(tab_ded_data.count,0) > nvl(tab_earn_data.count,0) then
1400 t := 1 ;
1401 if nvl(tab_earn_data.count,0) > 0 then
1402 For k in tab_earn_data.first..tab_earn_data.last
1403 LOOP
1404 if tab_dets_data(j).r_assact_id = tab_earn_data(k).r_assact_earn_id and tab_dets_data(j).r_assact_id = tab_ded_data(k).r_assact_ded_id then
1405 vXMLTable(vCtr).TagName := 'earnings_narrative';
1406 vXMLTable(vCtr).TagValue :=nvl( tab_earn_data(k).r_earn_narrative,' ');
1407 vCtr := vCtr + 1;
1408 vXMLTable(vCtr).TagName := 'earnings_value';
1409 --vXMLTable(vCtr).TagValue := to_char(tab_earn_data(k).r_earn_numeric_value,lg_format_mask);
1410 vXMLTable(vCtr).TagValue := to_char(to_number(tab_earn_data(k).r_earn_numeric_value),lg_format_mask);
1411 vCtr := vCtr + 1;
1412 IF upper(tab_ded_data(k).r_ded_narrative) <> upper('Social Insurance') then
1413 vXMLTable(vCtr).TagName := 'deductions_narrative';
1414 vXMLTable(vCtr).TagValue := nvl(tab_ded_data(k).r_ded_narrative,' ');
1415 vCtr := vCtr + 1;
1416 vXMLTable(vCtr).TagName := 'deductions_value';
1417 --vXMLTable(vCtr).TagValue :=to_char(tab_ded_data(k).r_ded_numeric_value,lg_format_mask);
1418 vXMLTable(vCtr).TagValue := to_char(to_number(tab_ded_data(k).r_ded_numeric_value),lg_format_mask);
1419 vCtr := vCtr + 1;
1420 END IF;
1421 t := t + 1;
1422 else
1423 EXIT;
1424 end if;
1425 END LOOP;
1426 end if;
1427 --if tab_earn_data.count > 0 then
1428 FOR k in /*tab_earn_data.last+1*/t..nvl(tab_ded_data.last,t)
1429 LOOP
1430 IF nvl(tab_ded_data.count,0) > 0 THEN
1431 if tab_dets_data(j).r_assact_id = tab_ded_data(k).r_assact_ded_id then
1432 IF upper(tab_ded_data(k).r_ded_narrative) <> upper('Social Insurance') then
1433 vXMLTable(vCtr).TagName := 'deductions_narrative';
1434 vXMLTable(vCtr).TagValue :=nvl( tab_ded_data(k).r_ded_narrative,' ');
1435 vCtr := vCtr + 1;
1436 vXMLTable(vCtr).TagName := 'deductions_value';
1437 --vXMLTable(vCtr).TagValue := to_char(tab_ded_data(k).r_ded_numeric_value,lg_format_mask);
1438 vXMLTable(vCtr).TagValue := to_char(to_number(nvl(tab_ded_data(k).r_ded_numeric_value,0)),lg_format_mask);
1439 vCtr := vCtr + 1;
1440 --org_ded_sum_try := org_ded_sum_try + tab_ded_data(k).r_ded_numeric_value;
1441 END IF;
1442 end if;
1443 END IF;
1444 END LOOP;
1445 --end if;
1446 elsif nvl(tab_ded_data.count,0) <= nvl(tab_earn_data.count,0) then
1447 t:=1;
1448 if nvl(tab_ded_data.count,0) > 0 then
1449 For k in nvl(tab_ded_data.first,0)..nvl(tab_ded_data.last,0)
1450 LOOP
1451 if tab_dets_data(j).r_assact_id = tab_earn_data(k).r_assact_earn_id and tab_dets_data(j).r_assact_id = tab_ded_data(k).r_assact_ded_id then
1452 vXMLTable(vCtr).TagName := 'earnings_narrative';
1453 vXMLTable(vCtr).TagValue :=nvl( tab_earn_data(k).r_earn_narrative,' ');
1454 vCtr := vCtr + 1;
1455 vXMLTable(vCtr).TagName := 'earnings_value';
1456 --vXMLTable(vCtr).TagValue := to_char(tab_earn_data(k).r_earn_numeric_value,lg_format_mask);
1457 vXMLTable(vCtr).TagValue := to_char(to_number(nvl(tab_earn_data(k).r_earn_numeric_value,0)),lg_format_mask);
1458 vCtr := vCtr + 1;
1459 IF upper(tab_ded_data(k).r_ded_narrative) <> upper('Social Insurance') then
1460 vXMLTable(vCtr).TagName := 'deductions_narrative';
1461 vXMLTable(vCtr).TagValue := nvl(tab_ded_data(k).r_ded_narrative,' ');
1462 vCtr := vCtr + 1;
1463 vXMLTable(vCtr).TagName := 'deductions_value';
1464 --vXMLTable(vCtr).TagValue :=to_char(tab_ded_data(k).r_ded_numeric_value,lg_format_mask);
1465 vXMLTable(vCtr).TagValue := to_char(to_number(nvl(tab_ded_data(k).r_ded_numeric_value,0)),lg_format_mask);
1466 vCtr := vCtr + 1;
1467 END IF;
1468 t:=t+1;
1469 else
1470 EXIT;
1471 end if;
1472 END LOOP;
1473 end if;
1474 --if tab_ded_data.count > 0 then
1475 FOR k in /*tab_ded_data.last+1*/t..nvl(tab_earn_data.last,t)
1476 LOOP
1477 IF nvl(tab_earn_data.count,0) > 0 THEN
1478 if tab_dets_data(j).r_assact_id = tab_earn_data(k).r_assact_earn_id then
1479 vXMLTable(vCtr).TagName := 'earnings_narrative';
1480 vXMLTable(vCtr).TagValue :=nvl( tab_earn_data(k).r_earn_narrative,' ');
1481 vCtr := vCtr + 1;
1482 vXMLTable(vCtr).TagName := 'earnings_value';
1483 --vXMLTable(vCtr).TagValue := to_char(tab_earn_data(k).r_earn_numeric_value,lg_format_mask);
1484 vXMLTable(vCtr).TagValue := to_char(to_number(nvl(tab_earn_data(k).r_earn_numeric_value,0)),lg_format_mask);
1485 vCtr := vCtr + 1;
1486 end if;
1487 END IF;
1488 END LOOP;
1489 --end if;
1490 end if;
1491 vXMLTable(vCtr).TagName := 'total_earnings_label';
1492 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_EARNINGS_LABEL');
1493 vCtr := vCtr + 1;
1494 vXMLTable(vCtr).TagName := 'total_amount_value';
1495 vXMLTable(vCtr).TagValue := to_char(emp_earn_sum,lg_format_mask);
1496 vCtr := vCtr + 1;
1497 vXMLTable(vCtr).TagName := 'total_deductions_label';
1498 vXMLTable(vCtr).TagValue :=get_lookup_meaning('KW_FORM_LABELS','TOTAL_DEDUCTIONS_LABEL');
1499 vCtr := vCtr + 1;
1500 vXMLTable(vCtr).TagName := 'total_deductions_value';
1501 vXMLTable(vCtr).TagValue := to_char(emp_ded_sum,lg_format_mask);
1502 vCtr := vCtr + 1;
1503 vXMLTable(vCtr).TagName := 'net_pay_label_emp';
1504 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','NET_PAY_LABEL_EMP');
1505 vCtr := vCtr + 1;
1506 vXMLTable(vCtr).TagName := 'net_pay_value_emp';
1507 vXMLTable(vCtr).TagValue := to_char((emp_earn_sum - emp_ded_sum),lg_format_mask);
1508 vCtr := vCtr + 1;
1509 vXMLTable(vCtr).TagName := 'YTD_earnings';
1510 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','YTD_EARNINGS');
1511 vCtr := vCtr + 1;
1512 vXMLTable(vCtr).TagName := 'YTD_earning_value';
1513 --vXMLTable(vCtr).TagValue := to_char(nvl(tab_dets_data(j).r_ytd_earning,0),lg_format_mask);
1514 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_ytd_earning,0);
1515 vCtr := vCtr + 1;
1516 vXMLTable(vCtr).TagName := 'YTD_deduction';
1517 vXMLTable(vCtr).TagValue :=get_lookup_meaning('KW_FORM_LABELS','YTD_DEDUCTION');
1518 vCtr := vCtr + 1;
1519 vXMLTable(vCtr).TagName := 'YTD_deduction_value';
1520 --vXMLTable(vCtr).TagValue := to_char(nvl(tab_dets_data(j).r_ytd_deduction,0),lg_format_mask);
1521 vXMLTable(vCtr).TagValue := nvl(tab_dets_data(j).r_ytd_deduction,0);
1522 vCtr := vCtr + 1;
1523 open csr_get_paymeth_det(tab_dets_data(j).r_assact_id);
1524 fetch csr_get_paymeth_det into tab_paymeth_data_init(m).r_org_paymeth_name,
1525 tab_paymeth_data_init(m).r_bank_name ,tab_paymeth_data_init(m).r_branch_name,
1526 tab_paymeth_data_init(m).r_account_number,tab_paymeth_data_init(m).r_amount,
1527 tab_paymeth_data_init(m).r_act_con_id,tab_paymeth_data_init(m).r_pay_status;
1528 If csr_get_paymeth_det%notfound then
1529 close csr_get_paymeth_det;
1530 else
1531 m := 1;
1532 close csr_get_paymeth_det;
1533 open csr_get_paymeth_det(tab_dets_data(j).r_assact_id);
1534 /* POPULATE THE XML for payment method details */
1535 vXMLTable(vCtr).TagName := 'pay_method_label';
1536 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAY_METHOD_LABEL');
1537 vCtr := vCtr + 1;
1538 vXMLTable(vCtr).TagName := 'status_label';
1539 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','STATUS_LABEL');
1540 vCtr := vCtr + 1;
1541 vXMLTable(vCtr).TagName := 'bank_name_label';
1542 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','BANK_NAME_LABEL');
1543 vCtr := vCtr + 1;
1544 vXMLTable(vCtr).TagName := 'branch_label';
1545 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','BRANCH_LABEL');
1546 vCtr := vCtr + 1;
1547 vXMLTable(vCtr).TagName := 'account_number_label';
1548 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ACCOUNT_NUMBER_LABEL');
1549 vCtr := vCtr + 1;
1550 vXMLTable(vCtr).TagName := 'amount_label';
1551 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','AMOUNT_E_LABEL');
1552 vCtr := vCtr + 1;
1553 LOOP
1554 fetch csr_get_paymeth_det into tab_paymeth_data(m).r_org_paymeth_name,
1555 tab_paymeth_data(m).r_bank_name ,tab_paymeth_data(m).r_branch_name,
1556 tab_paymeth_data(m).r_account_number,tab_paymeth_data(m).r_amount,
1557 tab_paymeth_data(m).r_act_con_id,tab_paymeth_data(m).r_pay_status;
1558 exit when csr_get_paymeth_det%notfound;
1559 vXMLTable(vCtr).TagName := 'pay_method_value';
1560 vXMLTable(vCtr).TagValue := nvl(tab_paymeth_data(m).r_org_paymeth_name,' ');
1561 vCtr := vCtr + 1;
1562 vXMLTable(vCtr).TagName := 'status_value';
1563 vXMLTable(vCtr).TagValue := nvl(tab_paymeth_data(m).r_pay_status,' ');
1564 vCtr := vCtr + 1;
1565 vXMLTable(vCtr).TagName := 'bank_name_value';
1566 vXMLTable(vCtr).TagValue := nvl(tab_paymeth_data(m).r_bank_name ,' ');
1567 vCtr := vCtr + 1;
1568 vXMLTable(vCtr).TagName := 'branch_value';
1569 vXMLTable(vCtr).TagValue := nvl(tab_paymeth_data(m).r_branch_name,' ');
1570 vCtr := vCtr + 1;
1571 vXMLTable(vCtr).TagName := 'account_number_value';
1572 vXMLTable(vCtr).TagValue :=tab_paymeth_data(m).r_account_number;
1573 vCtr := vCtr + 1;
1574 vXMLTable(vCtr).TagName := 'amount_value';
1575 --vXMLTable(vCtr).TagValue :=to_char(tab_paymeth_data(m).r_amount,lg_format_mask);
1576 vXMLTable(vCtr).TagValue := to_char(to_number(tab_paymeth_data(m).r_amount),lg_format_mask);
1577 vCtr := vCtr + 1;
1578 /* END POPULATE THE XML for payment method details */
1579 m := m + 1;
1580 END LOOP;
1581 close csr_get_paymeth_det;
1582 end if;
1583 vXMLTable(vCtr).TagName := 'break_line1';
1584 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1585 vCtr := vCtr + 1;
1586 vXMLTable(vCtr).TagName := 'break_line2';
1587 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1588 vCtr := vCtr + 1;
1589 l_emp_count := l_emp_count+1;
1590 l_w_indicator := l_w_indicator + 1;
1591 if l_emp_count = 2 then
1592 vXMLTable(vCtr).TagName := 'break_dummy';
1593 vXMLTable(vCtr).TagValue := ' ';
1594 vCtr := vCtr + 1;
1595 l_emp_count := 0;
1596 end if;
1597 emp_earn_sum := 0;
1598 emp_ded_sum := 0;
1599 if tab_ded_data.count > 0 then
1600 FOR i in tab_ded_data.first..tab_ded_data.last
1601 LOOP
1602 org_ded_sum_tot := org_ded_sum_tot + nvl(to_number(tab_ded_data(i).r_ded_numeric_value,'FM9999999999999999999999990D000'),0);
1603 END LOOP;
1604 end if;
1605 if tab_earn_data.count > 0 then
1606 FOR i in tab_earn_data.first..tab_earn_data.last
1607 LOOP
1608 org_earn_sum_tot := org_earn_sum_tot + nvl(to_number(tab_earn_data(i).r_earn_numeric_value,'FM9999999999999999999999990D000'),0);
1609 END LOOP;
1610 end if;
1611 tab_earn_data.delete;
1612 tab_ded_data .delete;
1613 j := j + 1;
1614 END LOOP;
1615 end if;
1616 close csr_get_details;
1617 END LOOP;
1618 end if;
1619 if l_emp_count <> 0 then
1620 vXMLTable(vCtr).TagName := 'break_dummy';
1621 vXMLTable(vCtr).TagValue := ' ';
1622 vCtr := vCtr + 1;
1623 end if;
1624 /******************/
1625 vXMLTable(vCtr).TagName := 'payroll_register_label';
1626 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_REGISTER_LABEL');
1627 vCtr := vCtr + 1;
1628 vXMLTable(vCtr).TagName := 'period_start_date_label';
1629 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_START_DATE_LABEL');
1630 vCtr := vCtr + 1;
1631 vXMLTable(vCtr).TagName := 'period_start_date_value';
1632 vXMLTable(vCtr).TagValue := trunc(p_effective_date,'MM');
1633 vCtr := vCtr + 1;
1634 vXMLTable(vCtr).TagName := 'period_end_date_label';
1635 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_END_DATE_LABEL');
1636 vCtr := vCtr + 1;
1637 vXMLTable(vCtr).TagName := 'period_end_date_value';
1638 vXMLTable(vCtr).TagValue := last_day(p_effective_date);
1639 vCtr := vCtr + 1;
1640 vXMLTable(vCtr).TagName := 'date_label';
1641 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DATE_LABEL');
1642 vCtr := vCtr + 1;
1643 vXMLTable(vCtr).TagName := 'date_value';
1644 vXMLTable(vCtr).TagValue := p_effective_date;
1645 vCtr := vCtr + 1;
1646 /******************/
1647 l_sum_flag := l_sum_flag + 1 ;
1648 if p_payroll_id is null then
1649 /********* Summary Organization Region *********/
1650 /*
1651 vXMLTable(vCtr).TagName := 'payroll_register_label';
1652 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_REGISTER_LABEL');
1653 vCtr := vCtr + 1;
1654 vXMLTable(vCtr).TagName := 'period_start_date_label';
1655 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_START_DATE_LABEL');
1656 vCtr := vCtr + 1;
1657 vXMLTable(vCtr).TagName := 'period_start_date_value';
1658 vXMLTable(vCtr).TagValue := trunc(p_effective_date,'MM');
1659 vCtr := vCtr + 1;
1660 vXMLTable(vCtr).TagName := 'period_end_date_label';
1661 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PERIOD_END_DATE_LABEL');
1662 vCtr := vCtr + 1;
1663 vXMLTable(vCtr).TagName := 'period_end_date_value';
1664 vXMLTable(vCtr).TagValue := last_day(p_effective_date);
1665 vCtr := vCtr + 1;
1666 vXMLTable(vCtr).TagName := 'date_label';
1667 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','DATE_LABEL');
1668 vCtr := vCtr + 1;
1669 vXMLTable(vCtr).TagName := 'date_value';
1670 vXMLTable(vCtr).TagValue := p_effective_date;
1671 vCtr := vCtr + 1;
1672 */
1673 vXMLTable(vCtr).TagName := 'organization_summary_label';
1674 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_SUMMARY_LABEL');
1675 vCtr := vCtr + 1;
1676 vXMLTable(vCtr).TagName := 'organization_name_summary_label';
1677 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_NAME_SUM_LABEL');
1678 vCtr := vCtr + 1;
1679 vXMLTable(vCtr).TagName := 'organization_name_summary_value';
1680 vXMLTable(vCtr).TagValue := nvl(l_header_organization_name,' ');
1681 vCtr := vCtr + 1;
1682 vXMLTable(vCtr).TagName := 'total_earnings_s_label';
1683 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_EARNINGS_S_LABEL');
1684 vCtr := vCtr + 1;
1685 vXMLTable(vCtr).TagName := 'total_earnings_s_value';
1686 vXMLTable(vCtr).TagValue := to_char(org_earn_sum_tot,lg_format_mask);
1687 vCtr := vCtr + 1;
1688 vXMLTable(vCtr).TagName := 'total_deductions_s_label';
1689 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_DEDUCTIONS_S_LABEL');
1690 vCtr := vCtr + 1;
1691 vXMLTable(vCtr).TagName := 'total_deductions_s_value';
1692 vXMLTable(vCtr).TagValue := to_char(org_ded_sum_tot,lg_format_mask);
1693 vCtr := vCtr + 1;
1694 vXMLTable(vCtr).TagName := 'total_pay_s_label';
1695 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_PAY_LABEL');
1696 vCtr := vCtr + 1;
1697 vXMLTable(vCtr).TagName := 'total_pay_s_value';
1698 vXMLTable(vCtr).TagValue := to_char((org_earn_sum_tot - org_ded_sum_tot),lg_format_mask);
1699 vCtr := vCtr + 1;
1700 vXMLTable(vCtr).TagName := 'break_line3';
1701 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1702 vCtr := vCtr + 1;
1703 vXMLTable(vCtr).TagName := 'break_line4';
1704 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1705 vCtr := vCtr + 1;
1706 /********* Summary Payroll Region *********/
1707 vXMLTable(vCtr).TagName := 'payroll_summary';
1708 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_SUMMARY');
1709 vCtr := vCtr + 1;
1710 if nvl(tab_sum_data.count,0) > 0 then
1711 FOR i in tab_sum_data.first..tab_sum_data.last
1712 LOOP
1713 open csr_get_payroll_name (tab_sum_data(i).payroll_id, p_effective_date);
1714 fetch csr_get_payroll_name into l_header_pyrl_name;
1715 close csr_get_payroll_name;
1716 IF p_org_structure_version_id IS NOT NULL THEN
1717 open csr_get_sum_earn (p_org_structure_version_id, p_organization_id, tab_sum_data(i).payroll_id, p_effective_date);
1718 fetch csr_get_sum_earn into org_earn_sum_last;
1719 close csr_get_sum_earn;
1720 ELSE
1721 /*open csr_get_sum_earn_only_org (p_organization_id , tab_sum_data(i).payroll_id, p_effective_date);
1722 fetch csr_get_sum_earn_only_org into org_earn_sum_last;
1723 close csr_get_sum_earn_only_org;*/
1724 l_e_temp_sum := 0;
1725 l_e_tot_sum := 0;
1726 l_e_arch_assact_1 := NULL;
1727 OPEN csr_seoo_split_1 (p_organization_id , tab_sum_data(i).payroll_id, p_effective_date);
1728 LOOP
1729 FETCH csr_seoo_split_1 INTO l_e_arch_assact_1;
1730 IF csr_seoo_split_1%NOTFOUND then
1731 CLOSE csr_seoo_split_1;
1732 EXIT;
1733 END IF;
1734 IF l_e_arch_assact_1 IS NOT NULL THEN
1735 OPEN csr_seoo_split_2(l_e_arch_assact_1);
1736 FETCH csr_seoo_split_2 INTO l_e_temp_sum;
1737 CLOSE csr_seoo_split_2;
1738 l_e_tot_sum := l_e_tot_sum + l_e_temp_sum;
1739 l_e_temp_sum := 0;
1740 END IF;
1741 END LOOP;
1742 org_earn_sum_last := TO_CHAR(l_e_tot_sum);
1743 END IF;
1744 IF p_org_structure_version_id IS NOT NULL THEN
1745 open csr_get_sum_ded (p_org_structure_version_id, p_organization_id, tab_sum_data(i).payroll_id, p_effective_date);
1746 fetch csr_get_sum_ded into org_ded_sum_last;
1747 close csr_get_sum_ded;
1748 ELSE
1749 /*open csr_get_sum_ded_only_org (p_organization_id , tab_sum_data(i).payroll_id, p_effective_date);
1750 fetch csr_get_sum_ded_only_org into org_ded_sum_last;
1751 close csr_get_sum_ded_only_org;*/
1752 l_d_temp_sum := 0;
1753 l_d_tot_sum := 0;
1754 l_d_arch_assact_1 := NULL;
1755 OPEN csr_sdoo_split_1(p_organization_id , tab_sum_data(i).payroll_id, p_effective_date);
1756 LOOP
1757 FETCH csr_sdoo_split_1 INTO l_d_arch_assact_1;
1758 IF csr_sdoo_split_1%NOTFOUND THEN
1759 CLOSE csr_sdoo_split_1;
1760 EXIT;
1761 END IF;
1762 IF l_d_arch_assact_1 IS NOT NULL THEN
1763 OPEN csr_sdoo_split_2(l_d_arch_assact_1);
1764 FETCH csr_sdoo_split_2 INTO l_d_temp_sum;
1765 CLOSE csr_sdoo_split_2;
1766 l_d_tot_sum := l_d_tot_sum + l_d_temp_sum;
1767 l_d_temp_sum := 0;
1768 END IF;
1769 END LOOP;
1770 org_ded_sum_last := to_char(l_d_tot_sum);
1771 END IF;
1772 vXMLTable(vCtr).TagName := 'payroll_summary_label';
1773 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_SUMMARY_LABEL');
1774 vCtr := vCtr + 1;
1775 vXMLTable(vCtr).TagName := 'payroll_summary_value';
1776 vXMLTable(vCtr).TagValue := nvl(l_header_pyrl_name,' ');
1777 vCtr := vCtr + 1;
1778 vXMLTable(vCtr).TagName := 'total_earnings_p_label';
1779 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_EARNINGS_S_LABEL');
1780 vCtr := vCtr + 1;
1781 vXMLTable(vCtr).TagName := 'total_earnings_p_value';
1782 --vXMLTable(vCtr).TagValue := org_earn_sum_last;
1783 vXMLTable(vCtr).TagValue := to_char(to_number(org_earn_sum_last),lg_format_mask);
1784 vCtr := vCtr + 1;
1785 vXMLTable(vCtr).TagName := 'total_deductions_p_label';
1786 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_DEDUCTIONS_S_LABEL');
1787 vCtr := vCtr + 1;
1788 vXMLTable(vCtr).TagName := 'total_deductions_p_value';
1789 --vXMLTable(vCtr).TagValue := to_char(org_ded_sum_last,lg_format_mask);
1790 vXMLTable(vCtr).TagValue := to_char(to_number(org_ded_sum_last),lg_format_mask);
1791 vCtr := vCtr + 1;
1792 vXMLTable(vCtr).TagName := 'total_pay_p_label';
1793 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_PAY_LABEL');
1794 vCtr := vCtr + 1;
1795 vXMLTable(vCtr).TagName := 'total_pay_p_value';
1796 vXMLTable(vCtr).TagValue := to_char(to_number((org_earn_sum_last- org_ded_sum_last)),lg_format_mask);
1797 vCtr := vCtr + 1;
1798 vXMLTable(vCtr).TagName := 'break_line5';
1799 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1800 vCtr := vCtr + 1;
1801 vXMLTable(vCtr).TagName := 'break_line6';
1802 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1803 vCtr := vCtr + 1;
1804 END LOOP;
1805 end if;
1806 end if;
1807 if p_payroll_id is not null then
1808 /********* Summary Payroll Region *********/
1809 vXMLTable(vCtr).TagName := 'payroll_summary';
1810 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_SUMMARY');
1811 vCtr := vCtr + 1;
1812 vXMLTable(vCtr).TagName := 'payroll_summary_label';
1813 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','PAYROLL_SUMMARY_LABEL');
1814 vCtr := vCtr + 1;
1815 vXMLTable(vCtr).TagName := 'payroll_summary_value';
1816 vXMLTable(vCtr).TagValue := nvl(l_header_pyrl_name,' ');
1817 vCtr := vCtr + 1;
1818 vXMLTable(vCtr).TagName := 'total_earnings_p_label';
1819 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_EARNINGS_S_LABEL');
1820 vCtr := vCtr + 1;
1821 vXMLTable(vCtr).TagName := 'total_earnings_p_value';
1822 vXMLTable(vCtr).TagValue := to_char(org_earn_sum_tot,lg_format_mask);
1823 vCtr := vCtr + 1;
1824 vXMLTable(vCtr).TagName := 'total_deductions_p_label';
1825 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_DEDUCTIONS_S_LABEL');
1826 vCtr := vCtr + 1;
1827 vXMLTable(vCtr).TagName := 'total_deductions_p_value';
1828 vXMLTable(vCtr).TagValue := to_char(org_ded_sum_tot,lg_format_mask);
1829 vCtr := vCtr + 1;
1830 vXMLTable(vCtr).TagName := 'total_pay_p_label';
1831 vXMLTable(vCtr).TagValue := get_lookup_meaning('KW_FORM_LABELS','TOTAL_PAY_LABEL');
1832 vCtr := vCtr + 1;
1833 vXMLTable(vCtr).TagName := 'total_pay_p_value';
1834 vXMLTable(vCtr).TagValue := to_char((org_earn_sum_tot- org_ded_sum_tot),lg_format_mask);
1835 vCtr := vCtr + 1;
1836 vXMLTable(vCtr).TagName := 'break_line5';
1837 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1838 vCtr := vCtr + 1;
1839 vXMLTable(vCtr).TagName := 'break_line6';
1840 vXMLTable(vCtr).TagValue := '- - - - - - - - - - - - - - - - - - ';
1841 vCtr := vCtr + 1;
1842 end if;
1843 WritetoCLOB(p_report,l_xfdf_blob);
1844 END GET_PAYROLL_REGISTER_DATA;
1845 ------------------------------------------------
1846 ----------------------------------------------
1847 PROCEDURE WritetoCLOB (p_report in varchar2,
1848 p_xfdf_blob out nocopy blob)
1849 IS
1850 l_xfdf_string clob;
1851 l_str1 varchar2(1000);
1852 l_str2 varchar2(20);
1853 l_str3 varchar2(20);
1854 l_str4 varchar2(20);
1855 l_str5 varchar2(20);
1856 l_str6 varchar2(30);
1857 l_str7 varchar2(1000);
1858 l_str8 varchar2(240);
1859 l_str9 varchar2(240);
1860 begin
1861 hr_utility.set_location('Entered Procedure Write to clob ',100);
1862 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
1863 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1864 <fields> ' ;
1865 l_str2 := '<field name="';
1866 l_str3 := '">';
1867 l_str4 := '<value>' ;
1868 l_str5 := '</value> </field>' ;
1869 l_str6 := '</fields> </xfdf>';
1870 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
1871 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1872 <fields>
1873 </fields> </xfdf>';
1874 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1875 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1876 if p_report = 'MAIN' then
1877 if vXMLTable.count > 0 then
1878 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1879 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
1880 l_str8 := vXMLTable(ctr_table).TagName;
1881 l_str9 := vXMLTable(ctr_table).TagValue;
1882 if (l_str9 is not null) then
1883 /* Added CDATA to handle special characters Bug No:6685975 */
1884 l_str9 := '<![CDATA['||l_str9||']]>';
1885 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
1886 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
1887 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
1888 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
1889 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
1890 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
1891 elsif (l_str9 is null and l_str8 is not null) then
1892 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
1893 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
1894 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
1895 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
1896 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
1897 else
1898 null;
1899 end if;
1900 END LOOP;
1901 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
1902 else
1903 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
1904 end if;
1905 else
1906 if vXMLTable_summary.count > 0 then
1907 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1908 FOR ctr_table IN vXMLTable_summary.FIRST .. vXMLTable_summary.LAST LOOP
1909 l_str8 := vXMLTable_summary(ctr_table).TagName;
1910 l_str9 := vXMLTable_summary(ctr_table).TagValue;
1911 if (l_str9 is not null) then
1912 /* Added CDATA to handle special characters Bug No:6685975 */
1913 l_str9 := '<![CDATA['||l_str9||']]>';
1914 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
1915 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
1916 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
1917 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
1918 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
1919 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
1920 elsif (l_str9 is null and l_str8 is not null) then
1921 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
1922 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
1923 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
1924 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
1925 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
1926 else
1927 null;
1928 end if;
1929 END LOOP;
1930 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
1931 else
1932 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
1933 end if;
1934 end if;
1935 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
1936 clob_to_blob(l_xfdf_string,p_xfdf_blob);
1937 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
1938 --return p_xfdf_blob;
1939 EXCEPTION
1940 WHEN OTHERS then
1941 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1942 HR_UTILITY.RAISE_ERROR;
1943 END WritetoCLOB;
1944 ----------------------------------------------------------------
1945 Procedure clob_to_blob(p_clob clob,
1946 p_blob IN OUT NOCOPY Blob)
1947 is
1948 l_length_clob number;
1949 l_offset pls_integer;
1950 l_varchar_buffer varchar2(32767);
1951 l_raw_buffer raw(32767);
1952 l_buffer_len number:= 20000;
1953 l_chunk_len number;
1954 l_blob blob;
1955 g_nls_db_char varchar2(60);
1956
1957 l_raw_buffer_len pls_integer;
1958 l_blob_offset pls_integer := 1;
1959
1960 begin
1961 hr_utility.set_location('Entered Procedure clob to blob',120);
1962 select userenv('LANGUAGE') into g_nls_db_char from dual;
1963 l_length_clob := dbms_lob.getlength(p_clob);
1964 l_offset := 1;
1965 while l_length_clob > 0 loop
1966 hr_utility.trace('l_length_clob '|| l_length_clob);
1967 if l_length_clob < l_buffer_len then
1968 l_chunk_len := l_length_clob;
1969 else
1970 l_chunk_len := l_buffer_len;
1971 end if;
1972 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
1973 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
1974 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
1975 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char));
1976
1977 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
1978 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
1979 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
1980 l_blob_offset := l_blob_offset + l_raw_buffer_len;
1981
1982 l_offset := l_offset + l_chunk_len;
1983 l_length_clob := l_length_clob - l_chunk_len;
1984 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
1985 end loop;
1986 hr_utility.set_location('Finished Procedure clob to blob ',130);
1987 end;
1988
1989 ----------------------------------------------------------------
1990 Procedure fetch_pdf_blob
1991 (p_report in varchar2,p_pdf_blob OUT NOCOPY blob)
1992 IS
1993 BEGIN
1994 If p_report = 'MAIN' then
1995 /*Changing thequery for performance issue for bug 7632337 */
1996 /* trying to use FND_LOBS_N1 index */
1997 /*SELECT file_data
1998 INTO p_pdf_blob
1999 FROM fnd_lobs
2000 WHERE file_id = (SELECT MAX(file_id)
2001 FROM fnd_lobs
2002 WHERE file_name like '%PAY_PRG_ar_KW.pdf'); */
2003 SELECT file_data
2004 INTO p_pdf_blob
2005 FROM fnd_lobs
2006 WHERE file_id =
2007 ( SELECT MAX(file_id)
2008 from FND_LOBS
2009 WHERE PROGRAM_NAME = 'PAY_PRG_ar_KW.pdf'
2010 and program_tag= 'TMP:XDO:XDOTMPLATE1:SEED'
2011 and nvl(EXPIRATION_DATE ,trunc(sysdate)) = trunc(sysdate)
2012 );
2013 /* Else
2014 SELECT file_data
2015 INTO p_pdf_blob
2016 FROM fnd_lobs
2017 WHERE file_id = (SELECT MAX(file_id)
2018 FROM fnd_lobs
2019 WHERE file_name like '%PAY_PRG_SUMMARY_ar_KW.pdf');*/
2020 End If;
2021 EXCEPTION
2022 when no_data_found then
2023 null;
2024 END fetch_pdf_blob;
2025 -----------------------------------------------------------------
2026 FUNCTION get_lookup_meaning
2027 (p_lookup_type varchar2
2028 ,p_lookup_code varchar2)
2029 RETURN VARCHAR2 IS
2030 CURSOR csr_lookup IS
2031 select meaning
2032 from hr_lookups
2033 where lookup_type = p_lookup_type
2034 and lookup_code = p_lookup_code;
2035 l_meaning hr_lookups.meaning%type;
2036 BEGIN
2037 OPEN csr_lookup;
2038 FETCH csr_lookup INTO l_Meaning;
2039 CLOSE csr_lookup;
2040 RETURN l_meaning;
2041 END get_lookup_meaning;
2042 -----------------------------------------------------------------
2043 END PAY_KW_PAYROLL_REGISTER ;