[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYRPANP_XML_PKG
Source
1 PACKAGE BODY PAY_PAYRPANP_XML_PKG AS
2 /* $Header: pyxmlanp.pkb 120.4 2011/05/17 13:13:29 nchinnam ship $ */
3
4 g_package_name varchar2(30) := 'PAY_PAYRPANP_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 --
34 --
35
36 function get_action_type( p_action_type_code varchar2) return varchar2 is
37
38 cursor c_action_type_meaning(c_action_type_code varchar2) is
39 select meaning
40 from hr_lookups
41 where lookup_type = 'ACTION_TYPE'
42 and lookup_code = c_action_type_code ;
43
44 l_action_type_meaning VARCHAR2(80);
45
46 begin
47
48 open c_action_type_meaning(p_action_type_code);
49 fetch c_action_type_meaning into l_action_type_meaning;
50 close c_action_type_meaning;
51
52 return l_action_type_meaning;
53
54 end get_action_type;
55
56 --
57 --
58 --
59
60 procedure append_parameters_data
61 (
62 p_consolidation_set_name in varchar2
63 ,p_payroll_name in varchar2
64 ,p_business_group_name in varchar2
65 ,p_record_counter in number
66 ,p_report_name in varchar2
67 ,p_mode_desc in varchar2
68 ,p_report_date in varchar2
69 ,p_start_date in date
70 ,p_end_date in date
71 ,p_template_name in varchar2
72 ) is
73
74 l_start_date_char varchar2(60);
75 l_end_date_char varchar2(60);
76 l_tag varchar2(200);
77
78 begin
79 -- l_start_date_char := fnd_date.date_to_displaydate(p_start_date); /*COMMENTED FOR BUG 11830805 */
80
81 l_start_date_char := fnd_date.date_to_displaydate(p_start_date, calendar_aware=>FND_DATE.calendar_aware_alt); /* BUG 11830805 */
82
83
84 -- l_end_date_char := fnd_date.date_to_displaydate(p_end_date); /*COMMENTED FOR BUG 11830805 */
85
86 l_end_date_char := fnd_date.date_to_displaydate(p_end_date, calendar_aware=>FND_DATE.calendar_aware_alt); /* BUG 11830805 */
87
88
89 l_tag := pay_prl_xml_utils.getTag('CP_CONSOLIDATION_SET_NAME', p_consolidation_set_name );
90 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
91
92 l_tag := pay_prl_xml_utils.getTag('CP_PAYROLL_NAME',p_payroll_name);
93 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
94
95 l_tag := pay_prl_xml_utils.getTag('CP_BUSINESS_GROUP_NAME',p_business_group_name);
96 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
97
98 l_tag := pay_prl_xml_utils.getTag('CP_RECORD_COUNT',p_record_counter);
99 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
100
101 l_tag := pay_prl_xml_utils.getTag('CP_REPORT_NAME',p_report_name);
102 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
103
104 l_tag := pay_prl_xml_utils.getTag('CP_MODE_DESC',p_mode_desc);
105 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
106
107 l_tag := pay_prl_xml_utils.getTag('CP_REPORT_DATE', p_report_date );
108 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
109
110 l_tag := pay_prl_xml_utils.getTag('CP_START_DATE',l_start_date_char);
111 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
112
113 l_tag := pay_prl_xml_utils.getTag('CP_END_DATE',l_end_date_char);
114 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
115
116 l_tag := pay_prl_xml_utils.getTag('CP_TEMPLATE_NAME',p_template_name);
117 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
118
119 end;
120
121 --
122 -- Private procedure to append master data to the clob.
123 --
124
125 procedure append_group_data(
126 p_consolidation_set_name varchar2
127 ,p_payroll_name varchar2
128 ,p_effective_date varchar
129 ,p_assignment_number varchar2
130 ,p_action_type varchar2
131 ,p_action_number number
132 ,p_person_id number
133 ,p_full_name varchar2
134 ,p_employee_number varchar2
135 ,p_action_type_meaning varchar2
136 ,p_record_counter number
137 )is
138
139 l_tag varchar2(200);
140
141 begin
142 l_tag := pay_prl_xml_utils.getTag('CONSOLIDATION_SET_NAME',p_consolidation_set_name);
143 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
144
145 l_tag := pay_prl_xml_utils.getTag('PAYROLL_NAME',p_payroll_name);
146 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
147
148 l_tag := pay_prl_xml_utils.getTag('EFFECTIVE_DATE',p_effective_date);
149 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
150
151 l_tag := pay_prl_xml_utils.getTag('ASSIGNMENT_NUMBER',p_assignment_number);
152 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
153
154 l_tag := pay_prl_xml_utils.getTag('ACTION_TYPE',p_action_type);
155 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
156
157 l_tag := pay_prl_xml_utils.getTag('ACTION_NUMBER',p_action_number);
158 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
159
160 l_tag := pay_prl_xml_utils.getTag('PERSON_ID',p_person_id);
161 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
162
163 l_tag := pay_prl_xml_utils.getTag('FULL_NAME',p_full_name);
164 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
165
166 l_tag := pay_prl_xml_utils.getTag('EMPLOYEE_NUMBER',p_employee_number);
167 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
168
169 l_tag := pay_prl_xml_utils.getTag('CF_ACTION_TYPE',p_action_type_meaning);
170 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
171
172 l_tag := pay_prl_xml_utils.getTag('CF_RECORD_COUNTER',p_record_counter);
173 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
174
175 end append_group_data;
176
177 --
178 --
179 --
180
181 Procedure set_condition_clauses
182 (
183 p_mode in varchar2
184 ,p_locking_payroll_condition out nocopy varchar2
185 ,p_payroll_action_condition out nocopy varchar2
186 ,p_payroll_consolidation_set out nocopy varchar2
187 ,p_order_by out nocopy varchar2
188 ) is
189
190 begin
191 /* Checking if picked by be Archiver */
192 if p_mode = '01' then
193 p_payroll_action_condition := ' ppa.action_type in (''P'',''U'',''B'')
194 and paa.source_action_id is null
195 and paa.action_status = ''C''';
196
197 p_locking_payroll_condition := ' ppa_lock.action_type = ''X''
198 and ppa_lock.report_type = ''XFR_INTERFACE''
199 and report_category = ''RT''
200 and report_qualifier = ''FED''
201 and paa_lock.action_status = ''C''';
202 p_payroll_consolidation_set :=
203 ' ppa.payroll_id = nvl(:4, ppa.payroll_id)
204 and ppa.consolidation_set_id = nvl(:5,
205 ppa.consolidation_set_id)
206 and pay.payroll_id = ppa.payroll_id
207 and pcs.consolidation_set_id = ppa.consolidation_set_id';
208
209 p_order_by := 'ppf.order_name, ppf.full_name, ppa.effective_date,
210 ppa.action_type, paa.assignment_action_id';
211
212
213 /* Checking if transfered in the FLS Tape */
214 elsif p_mode = '02' then
215
216 p_payroll_action_condition := ' ppa.action_type = ''X''
217 and ppa.report_type = ''XFR_INTERFACE''
218 and paa.action_status = ''C''';
219
220 p_locking_payroll_condition := ' ppa_lock.action_type = ''X''
221 and ppa_lock.report_type = ''FLS''
222 and report_category = ''RT''
223 and report_qualifier = ''PERIODIC''
224 and paa_lock.action_status = ''C''';
225 p_payroll_consolidation_set :=
226 ' rtrim(pay_mag_utils.get_parameter(
227 ''TRANSFER_PAYROLL_ID''
228 ,''TRANSFER_CONSOLIDATION_SET_ID''
229 ,ppa.legislative_parameters)) =
230 nvl(:4 , rtrim(pay_mag_utils.get_parameter(
231 ''TRANSFER_PAYROLL_ID''
232 ,''TRANSFER_CONSOLIDATION_SET_ID''
233 ,ppa.legislative_parameters)))
234 and rtrim(pay_mag_utils.get_parameter(
235 ''TRANSFER_CONSOLIDATION_SET_ID''
236 ,null
237 ,ppa.legislative_parameters)) =
238 nvl(:5 , rtrim(pay_mag_utils.get_parameter(
239 ''TRANSFER_CONSOLIDATION_SET_ID''
240 ,null
241 ,ppa.legislative_parameters)))
242 and pay.payroll_id = rtrim(pay_mag_utils.get_parameter(
243 ''TRANSFER_PAYROLL_ID''
244 ,''TRANSFER_CONSOLIDATION_SET_ID''
245 ,ppa.legislative_parameters))
246 and pcs.consolidation_set_id = rtrim(pay_mag_utils.get_parameter(
247 ''TRANSFER_CONSOLIDATION_SET_ID''
248 ,null
249 ,ppa.legislative_parameters))';
250
251
252 p_order_by := 'ppf.order_name, ppf.full_name, ppa.effective_date,
253 ppa.action_type, paa.assignment_action_id';
254
255 elsif p_mode = '03' then
256 p_payroll_action_condition := ' (ppa.action_type in (''R'',''Q'', ''V'') or
257 (ppa.action_type = ''B'' and
258 nvl(ppa.future_process_mode, ''N'') = ''Y''))
259 and paa.source_action_id is null
260 and paa.action_status = ''C''';
261
262 p_locking_payroll_condition := ' ppa_lock.action_type = ''C''
263 and paa_lock.action_status = ''C''';
264 p_payroll_consolidation_set :=
265 ' ppa.payroll_id = nvl(:4, ppa.payroll_id)
266 and ppa.consolidation_set_id = nvl(:5,
267 ppa.consolidation_set_id)
268 and pay.payroll_id = ppa.payroll_id
269 and pcs.consolidation_set_id = ppa.consolidation_set_id';
270
271 p_order_by := 'ppf.order_name, ppf.full_name, ppa.effective_date,
272 ppa.action_type, paa.assignment_action_id';
273
274 elsif p_mode = '04' then
275 p_payroll_action_condition := ' (ppa.action_type in (''R'',''Q'') or
276 (ppa.action_type = ''B'' and
277 nvl(ppa.future_process_mode,''N'') = ''Y''))
278 and paa.source_action_id is null
279 and paa.action_status = ''C''';
280
281 p_locking_payroll_condition := ' ppa_lock.action_type in (''P'', ''U'')
282 and paa_lock.action_status = ''C''';
283 p_payroll_consolidation_set :=
284 ' ppa.payroll_id = nvl(:4, ppa.payroll_id)
285 and ppa.consolidation_set_id = nvl(:5,
286 ppa.consolidation_set_id)
287 and pay.payroll_id = ppa.payroll_id
288 and pcs.consolidation_set_id = ppa.consolidation_set_id';
289
290 p_order_by := 'ppf.order_name, ppf.full_name, ppa.effective_date,
291 ppa.action_type, paa.assignment_action_id';
292 end if;
293
294
295 end set_condition_clauses;
296
297 --
298 --
299 --
300
301 procedure actions_not_processed
302 (
303 p_start_date_char in varchar2
304 ,p_end_date_char in varchar2
305 ,p_payroll_id in number default null
306 ,p_consolidation_set_id in number default null
307 ,p_report_name in varchar2
308 ,p_mode in varchar2
309 ,p_business_group_id in varchar2
310 ,p_session_date_char in varchar2
311 ,p_template_name in varchar2
312 ,p_xml out nocopy clob
313 )
314 is
315
316 l_tag varchar2(200);
317 l_statement varchar2(32000);
318 l_locking_payroll_condition varchar2(2000);
319 l_mode_desc varchar2(2000);
320 l_order_by varchar2(2000);
321 l_payroll_action_condition varchar2(2000);
322 l_consolidation_set_name pay_consolidation_sets.consolidation_set_name%type;
323 l_consolidation_set_condition varchar2(2000);
324 l_template_name varchar2(2000);
325 l_payroll_name pay_payrolls_f.payroll_name%type;
326 l_business_group_name per_business_groups.name%type;
327 l_session_date date;
328 l_start_date date;
329 l_end_date date;
330 cf_record_counter number;
331 cf_action_type varchar2(80);
332
333 type ref_cursor_type is ref cursor;
334
335 type report_data_record is record
336 (
337 person_id number
338 ,full_name per_people_f.full_name%type
339 ,employee_number per_people_f.employee_number%type
340 ,effective_date varchar2(20)
341 ,action_type varchar2(30)
342 ,action_number number
343 ,assignment_number per_assignments_f.assignment_number%type
344 ,payroll_name pay_all_payrolls_f.payroll_name%type
345 ,consolidation_set_name pay_consolidation_sets.consolidation_set_name%type
346 );
347
348 l_report_data report_data_record;
349
350 -- Need one cursor for outer group i.e. the master group.
351 csr_report_data ref_cursor_type;
352
353 --
354 -- Cursor to get payroll name.
355 --
356 cursor csr_get_payroll_name (c_payroll_id number)
357 is
358 select payroll_name
359 from pay_payrolls_f
360 where payroll_id = c_payroll_id;
361
362 --
363 -- Cursor to get consolidation_set_name
364 --
365 cursor csr_get_consolidation_set_name(c_consolidation_set_id number)
366 is
367 select consolidation_set_name
368 from pay_consolidation_sets
369 where consolidation_set_id = c_consolidation_set_id;
370
371 --
372 --
373 --
374 cursor csr_get_bg_name(c_business_group_id number)
375 is
376 select name
377 from per_business_groups
378 where business_group_id = c_business_group_id;
379
380
381 cursor c_report_mode(c_mode hr_lookups.lookup_code%type) is
382 select h.meaning from hr_lookups h
383 where h.lookup_type = 'PAY_ACTIONS_NOT_PROCESSED'
384 and h.lookup_code = c_mode
385 and h.enabled_flag = 'Y';
386
387
388 begin
389
390 hr_utility.set_location(g_package_name || '.actions not processed', 10);
391
392 --
393 -- Get the parameters which will be used to group the assignments.
394 -- The parameters returned will be used to frame the master cursor query.
395 --
396
397 open csr_get_payroll_name(p_payroll_id);
398 fetch csr_get_payroll_name into l_payroll_name;
399 close csr_get_payroll_name;
400
401
402 open csr_get_consolidation_set_name(p_consolidation_set_id);
403 fetch csr_get_consolidation_set_name into l_consolidation_set_name;
404 close csr_get_consolidation_set_name;
405
406 open csr_get_bg_name(p_business_group_id);
407 fetch csr_get_bg_name into l_business_group_name;
408 close csr_get_bg_name;
409
410
411 l_template_name := get_template_name('PAY', p_template_name);
412
413 --
414 -- Procedure to set order by clause and group by parameters.
415 --
416
417 set_condition_clauses
418 (
419 p_mode => p_mode
420 ,p_locking_payroll_condition => l_locking_payroll_condition
421 ,p_payroll_action_condition => l_payroll_action_condition
422 ,p_payroll_consolidation_set => l_consolidation_set_condition
423 ,p_order_by => l_order_by
424 );
425
426
427 --
428 -- Frame the statement for query.
429 --
430
431 l_statement :=
432 'select
433 ppf.person_id person_id,
434 ppf.full_name full_name,
435 ppf.employee_number employee_number,
436 fnd_date.date_to_displaydate(ppa.effective_date, calendar_aware => 2) effective_date, -- fnd_date.date_to_displaydate(ppa.effective_date) effective_date, /* BUG 11830805 */
437 ppa.action_type action_type,
438 paa.assignment_action_id action_number,
439 paf.assignment_number assignment_number,
440 pay.payroll_name payroll_name,
441 pcs.consolidation_set_name consolidation_set_name
442
443 from
444 pay_consolidation_sets pcs,
445 pay_all_payrolls_f pay,
446 per_all_people_f ppf,
447 per_all_assignments_f paf,
448 pay_assignment_actions paa,
449 pay_payroll_actions ppa
450
451 where ' || l_payroll_action_condition ||
452 ' and ppa.effective_date between :1 and :2
453 and ppa.business_group_id = :3
454 and paa.payroll_action_id = ppa.payroll_action_id
455 and paf.assignment_id = paa.assignment_id
456 and ppf.person_id = paf.person_id
457 and ' || l_consolidation_set_condition ||
458 ' and ppa.effective_date between paf.effective_start_date
459 and paf.effective_end_date
460 and ppa.effective_date between ppf.effective_start_date
461 and ppf.effective_end_date
462 and ppa.effective_date between pay.effective_start_date
463 and pay.effective_end_date
464 and not exists(
465 select null
466 from pay_action_interlocks pai,
467 pay_assignment_actions paa_lock,
468 pay_payroll_actions ppa_lock
469 where pai.locked_action_id = paa.assignment_action_id
470 /* Action from main Query */
471 and pai.locking_action_id = paa_lock.assignment_action_id
472 and ppa_lock.payroll_action_id = paa_lock.payroll_action_id
473 and ' || l_locking_payroll_condition || ' ) order by ' || l_order_by;
474
475
476
477 dbms_lob.createtemporary(g_xml_data,false,dbms_lob.call);
478 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
479
480 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
481 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
482
483 l_tag := '<PAYRPANP>';
484 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
485
486 l_tag := '<LIST_G_PERSON_ID>';
487 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
488
489
490 l_session_date := fnd_date.canonical_to_date(p_session_date_char);
491 l_start_date := fnd_date.canonical_to_date(p_start_date_char);
492 l_end_date := fnd_date.canonical_to_date(p_end_date_char);
493
494
495 cf_record_counter := 0;
496
497 open csr_report_data for l_statement using l_start_date,l_end_date,p_business_group_id,
498 p_payroll_id,p_consolidation_set_id;
499
500 loop
501
502 fetch csr_report_data into l_report_data;
503 exit when csr_report_data%notfound;
504
505 cf_record_counter := cf_record_counter + 1;
506
507 cf_action_type := get_action_type(l_report_data.action_type);
508
509 l_tag := '<G_PERSON_ID>';
510 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
511
512 append_group_data(
513 l_report_data.consolidation_set_name
514 ,l_report_data.payroll_name
515 ,l_report_data.effective_date
516 ,l_report_data.assignment_number
517 ,l_report_data.action_type
518 ,l_report_data.action_number
519 ,l_report_data.person_id
520 ,l_report_data.full_name
521 ,l_report_data.employee_number
522 ,cf_action_type
523 ,cf_record_counter
524 );
525
526
527 l_tag := '</G_PERSON_ID>';
528 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
529
530 end loop;
531 close csr_report_data;
532
533 l_tag := '</LIST_G_PERSON_ID>';
534 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
535
536 --
537 --
538 --
539 open c_report_mode(p_mode);
540 fetch c_report_mode into l_mode_desc;
541 close c_report_mode;
542
543 append_parameters_data(
544 l_consolidation_set_name
545 ,l_payroll_name
546 ,l_business_group_name
547 ,cf_record_counter
548 ,p_report_name
549 ,l_mode_desc
550 ,fnd_date.date_to_displayDT(SysDate, calendar_aware=>FND_DATE.calendar_aware_alt) -- ,fnd_date.date_to_displayDT(SysDate) /* BUG 11830805 */
551 ,l_start_date
552 ,l_end_date
553 ,l_template_name
554 );
555
556 l_tag := '</PAYRPANP>';
557 dbms_lob.writeappend(g_xml_data, length(l_tag), l_tag);
558
559 p_xml := g_xml_data;
560
561 end actions_not_processed;
562
563
564 end PAY_PAYRPANP_XML_PKG;