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