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