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