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