[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYRPENP_XML_PKG
Source
4 g_package_name varchar2(30) := 'PAY_PAYRPENP_XML_PKG';
1 PACKAGE BODY PAY_PAYRPENP_XML_PKG AS
2 /* $Header: pyxmlenp.pkb 120.3 2012/01/19 11:32:02 rpahune ship $ */
3
5 g_xml_data clob;
6
7 --
8 -- Private function to get the template name.
9 --
10
11 function get_template_name(p_app_short_name varchar2
12 ,p_template_code varchar2) return varchar2 is
13
14 l_template_name xdo_templates_tl.template_name%type;
15 begin
16
17 l_template_name := 'Not Defined';
18 select template_name
19 into l_template_name
20 from xdo_templates_tl
21 where application_short_name= p_app_short_name
22 and template_code= p_template_code
23 and language=userenv('LANG');
24
25 return l_template_name;
26
27 exception
28 when no_data_found then
29 return l_template_name;
30 end get_template_name;
31
32 --
33 FUNCTION get_display_name(p_group_column VARCHAR2) RETURN VARCHAR2 IS
34 --
35 BEGIN
36 --
37 CASE p_group_column
38 WHEN 'payroll_name' THEN RETURN 'Payroll Name';
39 WHEN 'organization_name' THEN RETURN 'Organization';
40 WHEN 'location_code' THEN RETURN 'Location';
41 ELSE RETURN NULL;
42 END CASE;
43 --
44 END get_display_name;
45 --
46 procedure append_parameters_data(
47 p_organization_name in varchar2
48 ,p_payroll_name in varchar2
49 ,p_location_code in varchar2
50 ,p_consolidation_set_name in varchar2
51 ,p_business_group_name in varchar2
52 ,p_no_data_found in number
53 ,p_report_date in varchar2
54 ,p_start_date in date
55 ,p_end_date in date
56 ,p_group_column1 in varchar2
57 ,p_group_column2 in varchar2
58 ,p_group_column3 in varchar2
59 ,p_sort_option_one in varchar2
60 ,p_sort_option_two in varchar2
61 ,p_sort_option_three in varchar2
62 ,p_template_name in varchar2
63 ) is
64 l_start_date_char varchar2(60);
65 l_end_date_char varchar2(60);
66 l_tag varchar2(200);
67
68 begin
69 -- l_start_date_char := fnd_date.date_to_displaydate(p_start_date); /* COMMENTED FOR BUG 11830805 */
70 -- l_end_date_char := fnd_date.date_to_displaydate(p_end_date); /* COMMENTED FOR BUG 11830805 */
71
72 l_start_date_char := fnd_date.date_to_displaydate(p_start_date, calendar_aware=>FND_DATE.calendar_aware_alt); /* FOR BUG 11830805 */
73 l_end_date_char := fnd_date.date_to_displaydate(p_end_date, calendar_aware=>FND_DATE.calendar_aware_alt); /* FOR BUG 11830805 */
74
75
76 l_tag := pay_prl_xml_utils.getTag('CP_ORGANIZATION_NAME', p_organization_name );
77 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
78
79 l_tag := pay_prl_xml_utils.getTag('CP_PAYROLL_NAME',p_payroll_name);
80 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
81
82 l_tag := pay_prl_xml_utils.getTag('CP_LOCATION_CODE',p_location_code);
83 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
84
85 l_tag := pay_prl_xml_utils.getTag('CP_CONSOLIDATION_SET_NAME',p_consolidation_set_name);
86 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
87
88 l_tag := pay_prl_xml_utils.getTag('CONSOLIDATION_SET_NAME',p_consolidation_set_name);
89 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
90
91 l_tag := pay_prl_xml_utils.getTag('CP_BUSINESS_GROUP_NAME',p_business_group_name);
92 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
93
94 l_tag := pay_prl_xml_utils.getTag('CP_NO_DATA_FOUND', to_char(p_no_data_found) );
95 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
96
97 l_tag := pay_prl_xml_utils.getTag('CP_REPORT_DATE',p_report_date);
98 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
99
100 l_tag := pay_prl_xml_utils.getTag('CP_START_DATE',l_start_date_char);
101 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
102
103 l_tag := pay_prl_xml_utils.getTag('CP_END_DATE',l_end_date_char);
104 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
105
106 l_tag := pay_prl_xml_utils.getTag('CP_GROUP_COLUMN1',get_display_name(p_group_column1));
107 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
108
109 l_tag := pay_prl_xml_utils.getTag('CP_GROUP_COLUMN2',get_display_name(p_group_column2));
110 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
111
112 l_tag := pay_prl_xml_utils.getTag('CP_GROUP_COLUMN3',get_display_name(p_group_column3));
113 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
114
115 l_tag := pay_prl_xml_utils.getTag('CP_SORT_OPTION1',p_sort_option_one);
116 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
117
118 l_tag := pay_prl_xml_utils.getTag('CP_SORT_OPTION2',p_sort_option_two);
119 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
120
121 l_tag := pay_prl_xml_utils.getTag('CP_SORT_OPTION3',p_sort_option_three);
122 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
123
124 l_tag := pay_prl_xml_utils.getTag('CP_TEMPLATE_NAME',p_template_name);
125 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
126
127 end;
128
129 --
130 -- Private procedure to append master data to the clob.
131 --
132
133 procedure append_master_group_data(
134 p_master_column1 in varchar2,
135 p_master_column2 in varchar2,
136 p_master_column3 in varchar2
140
137 )is
138
139 l_tag varchar2(200);
141 begin
142 l_tag := pay_prl_xml_utils.getTag('REG_A',p_master_column1);
143 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
144
145 l_tag := pay_prl_xml_utils.getTag('REG_B',p_master_column2);
146 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
147
148 l_tag := pay_prl_xml_utils.getTag('REG_C',p_master_column3);
149 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
150
151 end;
152
153 --
154 -- Procedure to append detail data to the clob.
155 --
156
157 procedure append_detail_group_data(
158 p_ICX_period_start_date in varchar2
159 ,p_ICX_period_end_date in varchar2
160 ,p_order_name in varchar2
161 ,p_organization_name in varchar2
162 ,p_consolidation_set_name in varchar2
163 ,p_payroll_name in varchar2
164 ,p_location_code in varchar2
165 ,p_assignment_id in number
166 ,p_pay_basis in varchar2
167 ,p_full_name in varchar2
168 ,p_user_status in varchar2
169 ,p_person_id in number
170 ,p_assignment_number in varchar2
171 ,p_cf_absence_type in varchar2
172 ,p_cf_data_found in number
173 )is
174
175 l_tag varchar2(200);
176
177 begin
178 l_tag := pay_prl_xml_utils.getTag('ICX_PERIOD_START_DATE', to_char(p_ICX_period_start_date) );
179 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
180
181 l_tag := pay_prl_xml_utils.getTag('ICX_PERIOD_END_DATE',p_ICX_period_end_date);
182 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
183
184 l_tag := pay_prl_xml_utils.getTag('ORDER_NAME',p_order_name);
185 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
186
187 l_tag := pay_prl_xml_utils.getTag('ORGANIZATION_NAME',p_organization_name);
188 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
189
190 l_tag := pay_prl_xml_utils.getTag('CONSOLIDATION_SET_NAME',p_consolidation_set_name);
191 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
192
193 l_tag := pay_prl_xml_utils.getTag('PAYROLL_NAME',p_payroll_name);
194 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
195
196 l_tag := pay_prl_xml_utils.getTag('LOCATION_CODE',p_location_code);
197 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
198
199 l_tag := pay_prl_xml_utils.getTag('ASSIGNMENT_ID',p_assignment_id);
200 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
201
202 l_tag := pay_prl_xml_utils.getTag('PAY_BASIS',p_pay_basis);
203 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
204
205 l_tag := pay_prl_xml_utils.getTag('FULL_NAME',p_full_name);
206 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
207
208 l_tag := pay_prl_xml_utils.getTag('USER_STATUS',p_user_status);
209 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
210
211 l_tag := pay_prl_xml_utils.getTag('PERSON_ID',p_person_id);
212 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
213
214 l_tag := pay_prl_xml_utils.getTag('ASSIGNMENT_NUMBER',p_assignment_number);
215 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
216
217 l_tag := pay_prl_xml_utils.getTag('CF_ABSENCE_TYPE',p_cf_absence_type);
218 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
219
220 l_tag := pay_prl_xml_utils.getTag('CF_DATA_FOUND',p_cf_data_found);
221 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
222
223 end append_detail_group_data;
224
225 --
226 -- Function to get absence type.
227 --
228
229 function get_absence_type
230 (
231 p_period_start_date in date
232 ,p_period_end_date in date
233 ,p_person_id in number
234 ) return varchar2 is
235
236 cursor c_absence(c_person_id number,c_period_end_date date, c_period_start_date date) is
237 select typ.name
238 from per_absence_attendances att,
239 per_absence_attendance_types typ
240 where att.person_id = c_person_id
241 and att.absence_attendance_type_id = typ.absence_attendance_type_id
242 and att.date_start <= c_period_end_date
243 and att.date_end >= c_period_start_date;
244
245 l_name varchar2(30);
246
247 begin
248
249 open c_absence(p_person_id, p_period_end_date, p_period_start_date);
250 fetch c_absence into l_name;
251 close c_absence;
252
253 if l_name is null then
254
255 return(' ');
256
257 else
258
259 return(l_name);
260
261 end if;
262
263 end get_absence_type;
264
265 --
266 --
267 --
268
269 function get_additional_where_clause
270 (
271 p_payroll_id in number
272 ,p_consolidation_set_id in number
273 ,p_organization_id in number
274 ,p_location_id in number
275 ,p_business_group_id in varchar2
276 )return varchar2 is
277
278 l_additional_where_clause varchar2(4000);
279 begin
280
281 l_additional_where_clause := ' ';
282 if p_organization_id is not null then
283 l_additional_where_clause := l_additional_where_clause || ' and organization_id = '|| p_organization_id ;
284 end if;
285
286 if p_payroll_id is not null then
287 l_additional_where_clause := l_additional_where_clause || ' and payroll_id = ' ||p_payroll_id ;
288 end if;
289
293
290 if p_consolidation_set_id is not null then
291 l_additional_where_clause := l_additional_where_clause || ' and consolidation_set_id =' || p_consolidation_set_id ;
292 end if;
294 if p_business_group_id is not null then
295 l_additional_where_clause := l_additional_where_clause || ' and business_group_id = ' || p_business_group_id ;
296 end if;
297
298 if p_location_id is not null then
299 l_additional_where_clause := l_additional_where_clause || ' and location_id = ' || p_location_id ;
300 end if;
301
302 return l_additional_where_clause;
303
304 end get_additional_where_clause;
305
306
307 --
308 -- Procedure to set order by clause and set the values of group by
309 -- parameters.
310 --
311
312 Procedure set_groupby_orderby_values
313 (
314 p_sort_option_one in out nocopy varchar2
315 ,p_sort_option_two in out nocopy varchar2
316 ,p_sort_option_three in out nocopy varchar2
317 ,p_order_by_clause in out nocopy varchar2
318
319 ) is
320
321 l_order_1 varchar2(60);
322 l_order_2 varchar2(60);
323 l_order_3 varchar2(60);
324 l_group_column1 varchar2(60);
325 l_group_column2 varchar2(60);
326 l_group_column3 varchar2(60);
327
328 begin
329 l_group_column1 := 'NULL';
330 l_group_column2 := 'NULL';
331 l_group_column3 := 'NULL';
332
333 l_order_1 := 'NULL';
334 l_order_2 := 'NULL';
335 l_order_3 := 'NULL';
336
337 if p_sort_option_one is NULL then
338 p_sort_option_one := 'Payroll Name';
339 end if;
340
341 if p_sort_option_one <> 'Employee Name' and
342 p_sort_option_two is NULL then
343 p_sort_option_two := 'Employee Name';
344 end if;
345
346 if p_sort_option_one = 'Payroll Name' then
347 l_group_column1 := 'payroll_name';
348 l_order_1 := 'payroll_name';
349
350 elsif p_sort_option_one = 'Employee Name' then
351 l_order_1 := 'order_name, full_name';
352
353 elsif p_sort_option_one = 'Assignment Number' then
354 l_order_1 := 'assignment_number';
355
356 elsif p_sort_option_one = 'Organization' then
357 l_group_column1 := 'organization_name';
358 l_order_1 := 'organization_name';
359
360 elsif p_sort_option_one = 'Location' then
361 l_group_column1 := 'location_code';
362 l_order_1 := 'location_code';
363
364 elsif p_sort_option_one = 'Assignment Status' then
365 l_order_1 := 'user_status';
366 end if;
367
368
369
370 if p_sort_option_two = 'Payroll Name' then
371 l_group_column2 := 'payroll_name';
372 l_order_2 := 'payroll_name';
373
374 elsif p_sort_option_two = 'Employee Name' then
375 l_order_2 := 'order_name, full_name';
376
377 elsif p_sort_option_two = 'Assignment Number' then
378 l_order_2 := 'assignment_number';
379
380 elsif p_sort_option_two = 'Organization' then
381 l_group_column2 := 'organization_name';
382 l_order_2 := 'organization_name';
383
384 elsif p_sort_option_two = 'Location' then
385 l_group_column2 := 'location_code';
386 l_order_2 := 'location_code';
387
388 elsif p_sort_option_two = 'Assignment Status' then
389 l_order_2 := 'user_status';
390 end if;
391
392
393 if p_sort_option_three = 'Payroll Name' then
394 l_group_column3 := 'payroll_name';
395 l_order_3 := 'payroll_name';
396
397 elsif p_sort_option_three = 'Employee Name' then
398 l_order_3 := 'order_name, full_name';
399
400 elsif p_sort_option_three = 'Assignment Number' then
401 l_order_3 := 'assignment_number';
402
403 elsif p_sort_option_three = 'Organization' then
404 l_group_column3 := 'organization_name';
405 l_order_3 := 'organization_name';
406
407 elsif p_sort_option_three = 'Location' then
408 l_group_column3 := 'location_code';
409 l_order_3 := 'location_code';
410
411 elsif p_sort_option_three = 'Assignment Status' then
412 l_order_3 := 'user_status';
413 end if;
414
415 p_order_by_clause := l_order_1||', '||l_order_2||', '||l_order_3;
416
417 if l_order_1 <> 'order_name, full_name' and
418 l_order_2 <> 'order_name, full_name' and
419 l_order_3 <> 'order_name, full_name' then
420
421 p_order_by_clause := p_order_by_clause || ',order_name, full_name';
422
423 end if;
424
425 p_order_by_clause := p_order_by_clause || ',period_end_date';
426
427 p_sort_option_one := l_group_column1;
428 p_sort_option_two := l_group_column2;
429 p_sort_option_three := l_group_column3;
430
431 end set_groupby_orderby_values;
432
433 --
434 --
435 --
436 procedure emp_asg_not_processed
437 (
438 p_start_date_char in varchar2
439 ,p_end_date_char in varchar2
440 ,p_payroll_id in number default null
441 ,p_consolidation_set_id in number
442 ,p_organization_id in number default null
443 ,p_location_id in number default null
444 ,p_sort_option_one in varchar2 default null
445 ,p_sort_option_two in varchar2 default null
446 ,p_sort_option_three in varchar2 default null
447 ,p_business_group_id in varchar2 default null
448 ,p_session_date_char in varchar2 default null
449 ,p_template_name in varchar2
450 ,p_xml out nocopy clob
451 )
452 is
456 type detail_rec_type is record
453
454 type ref_cursor_type is ref cursor;
455
457 (
458 payroll_name pay_asgs_not_processed_v.payroll_name%type
459 ,assignment_number pay_asgs_not_processed_v.assignment_number%type
460 ,order_name pay_asgs_not_processed_v.order_name%type
461 ,full_name pay_asgs_not_processed_v.full_name%type
462 ,user_status pay_asgs_not_processed_v.user_status%type
463 ,period_start_date pay_asgs_not_processed_v.period_start_date%type
464 ,ICX_period_start_date varchar2(30)
465 ,period_end_date pay_asgs_not_processed_v.period_end_date%type
466 ,ICX_period_end_date varchar2(30)
467 ,location_code pay_asgs_not_processed_v.location_code%type
468 ,pay_basis pay_asgs_not_processed_v.pay_basis%type
469 ,assignment_id pay_asgs_not_processed_v.assignment_id%type
470 ,person_id per_people_f.person_id%type
471 ,organization_name pay_asgs_not_processed_v.organization_name%type
472 ,consolidation_set_name pay_asgs_not_processed_v.consolidation_set_name%type
473 );
474
475 -- Need one cursor for outer group i.e. the master group.
476 csr_master ref_cursor_type;
477
478 -- For each master record there will be detail records.
479 csr_detail ref_cursor_type;
480
481 l_group_column1 varchar2(60);
482 l_group_column2 varchar2(60);
483 l_group_column3 varchar2(60);
484 l_order_by_clause varchar2(200);
485 l_group_by_clause varchar2(200);
486 l_where_clause varchar2(2000);
487 l_additional_where_clause varchar2(2000);
488 l_payroll_name pay_payrolls_f.payroll_name%type;
489 l_location_code hr_locations.location_code%type;
490 l_business_group_name per_business_groups.name%type;
491 l_consolidation_set_name pay_consolidation_sets.consolidation_set_name%type;
492 l_organization_name hr_organization_units.name%type;
493 l_template_name xdo_templates_tl.template_name%type;
494 l_master_statement varchar2(32000);
495 l_detail_statement varchar2(32000);
496 l_master_column1 varchar2(60);
497 l_master_column2 varchar2(60);
498 l_master_column3 varchar2(60);
499 l_tag varchar2(200);
500 cf_data_found number;
501 l_no_data_found number;
502 cf_absence_type varchar2(100);
503
504 c_detail_rec detail_rec_type;
505
506 --
507 -- Cursor to get payroll name.
508 --
509 cursor csr_get_payroll_name (c_payroll_id number)
510 is
511 select payroll_name
512 from pay_payrolls_f
513 where payroll_id = c_payroll_id;
514
515 --
516 -- Cursor to get consolidation_set_name
517 --
518 cursor csr_get_consolidation_set_name(c_consolidation_set_id number)
519 is
520 select consolidation_set_name
521 from pay_consolidation_sets
522 where consolidation_set_id = c_consolidation_set_id;
523
524 --
525 -- Cursor to get organization name.
526 --
527 cursor csr_get_organization_name(c_organization_id number)
528 is
529 select name
530 from hr_organization_units
531 where organization_id = c_organization_id;
532
533 --
534 --
535 --
536 cursor csr_get_bg_name(c_business_group_id number)
537 is
538 select name
539 from per_business_groups
540 where business_group_id = c_business_group_id;
541
542
543
544 begin
545 hr_utility.set_location(g_package_name || '.emp_asg_not_processed', 10);
546
547 --
548 -- Get the parameters which will be used to group the assignments.
549 -- The parameters returned will be used to frame the master cursor query.
550 --
551
552 if ( p_payroll_id is not null ) then
553 open csr_get_payroll_name(p_payroll_id);
554 fetch csr_get_payroll_name into l_payroll_name;
555 close csr_get_payroll_name;
556 end if;
557
558 if (p_organization_id is not null) then
559 open csr_get_organization_name(p_organization_id);
560 fetch csr_get_organization_name into l_organization_name;
561 close csr_get_organization_name;
562 end if;
563
564 open csr_get_consolidation_set_name(p_consolidation_set_id);
565 fetch csr_get_consolidation_set_name into l_consolidation_set_name;
566 close csr_get_consolidation_set_name;
567
568 open csr_get_bg_name(p_business_group_id);
569 fetch csr_get_bg_name into l_business_group_name;
570 close csr_get_bg_name;
571
572 l_location_code := payrpenp.get_location_code(p_location_id);
573
574 l_template_name := get_template_name('PAY', p_template_name);
575
576 --
577 -- Procedure to set order by clause and group by parameters.
578 --
579 l_group_column1 := p_sort_option_one;
580 l_group_column2 := p_sort_option_two;
581 l_group_column3 := p_sort_option_three;
582 l_order_by_clause := ' ';
583
584 set_groupby_orderby_values
585 (
586 l_group_column1
587 ,l_group_column2
588 ,l_group_column3
589 ,l_order_by_clause
590 );
591
592 l_group_by_clause := l_group_column1 || ',' || l_group_column2 || ',' ||
593 l_group_column3;
594 l_group_by_clause := trim(',' from l_group_by_clause);
595
596 --
597 -- Get the additional where clause depending on the user inputs.
598 --
599
600 l_where_clause :=
601 get_additional_where_clause
602 (
603 p_payroll_id
604 ,p_consolidation_set_id
605 ,p_organization_id
606 ,p_location_id
607 ,p_business_group_id
608 );
612
609 --
610 -- Frame the statement for master query.
611 --
613 l_master_statement := 'select ' ||
614 l_group_column1 || ' column1,' ||
615 l_group_column2 || ' column2,' ||
616 l_group_column3 || ' column3 ' ||
617 ' from pay_asgs_not_processed_v where period_end_date between :1 and :2 ' ||
618 l_where_clause || ' group by ' ||
619 l_group_by_clause || ' order by ' ||
620 l_group_by_clause;
621
622 l_detail_statement := 'select ' ||
623 'payroll_name payroll_name,
624 assignment_number assignment_number,
625 order_name order_name,
626 full_name full_name,
627 user_status user_status,
628 period_start_date period_start_date,
629 fnd_date.date_to_displaydate(period_start_date, calendar_aware=>2) ICX_period_start_date, -- fnd_date.date_to_displaydate(period_start_date) ICX_period_start_date, /* FOR BUG 11830805 */
630 period_end_date period_end_date,
631 fnd_date.date_to_displaydate(period_end_date, calendar_aware=>2) ICX_period_end_date, -- fnd_date.date_to_displaydate(period_end_date) ICX_period_end_date, /* FOR BUG 11830805 */
632 location_code location_code,
633 pay_basis pay_basis,
634 assignment_id assignment_id,
635 person_id person_id,
636 organization_name organization_name,
637 consolidation_set_name consolidation_set_name
638 from
639 pay_asgs_not_processed_v
640 where
641 period_end_date between :1 and :2 ' ||
642 l_where_clause ||
643 ' and NVL ('|| l_group_column1 || ' , ''-1'' ) = NVL( :3, ''-1'')' ||
644 ' and NVL ('|| l_group_column2 || ' , ''-1'' ) = NVL( :4, ''-1'')' ||
645 ' and NVL ('|| l_group_column3 || ' , ''-1'' ) = NVL( :5, ''-1'')' ||
646 ' order by '||l_order_by_clause;
647
648
649 dbms_lob.createtemporary(g_xml_data,false,dbms_lob.call);
650 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
651
652 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
653 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
654
655 l_tag := '<PAYRPENP>';
656 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
657
658 l_tag := '<LIST_G_SORT1>';
659 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
660
661 cf_data_found := 0;
662
663 open csr_master for l_master_statement using fnd_date.canonical_to_date(p_start_date_char),
664 fnd_date.canonical_to_date(p_end_date_char);
665 loop
666
667 fetch csr_master into l_master_column1,l_master_column2,l_master_column3;
668 exit when csr_master%notfound;
669
670 l_tag := '<G_SORT1>';
671 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
672
673 append_master_group_data(
674 l_master_column1,
675 l_master_column2,
676 l_master_column3
677 );
678
679 open csr_detail for l_detail_statement using to_date(p_start_date_char,'YYYY/MM/DD HH24:MI:SS'),
680 to_date(p_end_date_char,'YYYY/MM/DD HH24:MI:SS'),
681 l_master_column1,
682 l_master_column2,
683 l_master_column3;
684
685 l_tag := '<LIST_G_FULL_NAME>';
686 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
687
688 loop
689
690 fetch csr_detail into c_detail_rec;
691 if csr_detail%found then
692 cf_data_found := 1;
693 else
694 exit;
695 end if;
696
697 cf_absence_type := get_absence_type(
698 c_detail_rec.period_start_date
699 ,c_detail_rec.period_end_date
700 ,c_detail_rec.person_id
701 );
702
703 l_tag := '<G_FULL_NAME>';
704 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
705 append_detail_group_data(
706 c_detail_rec.ICX_period_start_date
707 ,c_detail_rec.ICX_period_end_date
708 ,c_detail_rec.order_name
709 ,c_detail_rec.organization_name
710 ,c_detail_rec.consolidation_set_name
711 ,c_detail_rec.payroll_name
712 ,c_detail_rec.location_code
713 ,c_detail_rec.assignment_id
714 -- ,c_detail_rec.name
715 ,c_detail_rec.pay_basis
716 ,c_detail_rec.full_name
717 ,c_detail_rec.user_status
718 ,c_detail_rec.person_id
719 ,c_detail_rec.assignment_number
720 ,cf_absence_type
721 ,cf_data_found
722 );
723 l_tag := '</G_FULL_NAME>';
724 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
725
726 --dbms_output.put_line('Full Name = ' || c_detail_rec.full_name);
727 end loop;
728 close csr_detail;
729
730 l_tag := '</LIST_G_FULL_NAME>';
731 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
732
733 l_tag := '</G_SORT1>';
734 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
735
736 end loop;
737 close csr_master;
738
739 l_tag := '</LIST_G_SORT1>';
740 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
741
742 --
743 --
744 --
745 if cf_data_found = 0 then
746 l_no_data_found := 1;
747 else
748 l_no_data_found := 0;
749 end if;
750
754 ,l_location_code
751 append_parameters_data(
752 l_organization_name
753 ,l_payroll_name
755 ,l_consolidation_set_name
756 ,l_business_group_name
757 ,l_no_data_found
758 ,fnd_date.date_to_displayDT(SysDate, calendar_aware=>FND_DATE.calendar_aware_alt) -- fnd_date.date_to_displayDT(SysDate) /* FOR BUG 11830805 */
759 ,fnd_date.canonical_to_date(p_start_date_char)
760 ,fnd_date.canonical_to_date(p_end_date_char)
761 ,l_group_column1
762 ,l_group_column2
763 ,l_group_column3
764 ,p_sort_option_one
765 ,p_sort_option_two
766 ,p_sort_option_three
767 ,l_template_name
768 );
769
770 l_tag := '</PAYRPENP>';
771 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
772
773 p_xml := g_xml_data;
774
775 end emp_asg_not_processed;
776
777
778
779 end PAY_PAYRPENP_XML_PKG;