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