1 PACKAGE BODY pay_cn_audit_xmlgen AS
2 /* $Header: pycnauxml.pkb 120.6 2010/10/27 11:15:36 mdubasi noship $ */
3
4 /*
5 ===========================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +============================================================================+
10 Name
11 pay_cn_audit_xmlgen
12 File
13 pycnauxml.pkb
14 Purpose
15
16 The purpose of this package is to support the generation of XML for the process
17 China Payroll Data Export.
18
19 Date Author Verion Bug Details
20 ============================================================================
21 13-APR-2010 DDUVVURI 1.0 9469668 Initial file created
22 13-APR-2010 DDUVVURI 1.1 9469668 Resolved GSCC issues.
23 16-APR-2010 DDUVVURI 1.2 9469668 Modified cursor csr_assignments in procedure assact_xml to pick up
24 ptp.payroll_id instead of arch_ppa.payroll_id
25 26-APR-2010 DDUVVURI 1.3 9648944 Modified cursor csr_payroll_elements in procedure gen_xml_header_pay
26 and added 2 new cursors csr_arch_balances and csr_arch_stat_balances in
27 procedure assact_xml
28 27-APR-2010 DDUVVURI 1.4 9648944 Modified cursor csr_arch_balances to pick up balances which have
29 _ASG_PTD dimension only
30 12-May-2010 DDUVVURI 1.5 9696416 Modified issued found out post dev QA
31 1. modified header info for ministry
32 2. made formatting changes to Amount column in all cursors in PROCEDURE assact_xml
33 3. fixed issue of certain balances not being picked in CURSOR csr_arch_balances
34 4. fixed issues with exception report for SOE and Ministry
35 22-May-2010 DDUVVURI 1.6 9723217,9723261 Fixed all issues raised by QA
36 26-May-2010 DDUVVURI 1.7 9743013,9742923,9742617 Below are the issues fixed
37 1. Formatting in amounts removed
38 2. Exception Report for Ministry
39 - payroll name is also archived in action_information_5
40 - function get_message modified to add new parameter for payroll name
41 3. Sorting in Group3 now on accounting period , payroll name , Employee number
42 4. Sorting issues in Group4 corrected for multiple payrolls within
43 single period. Sorting in Group 4 is based on
44 Payroll Period number , Payroll Name , Employee number
45 5. Issue of Balance not displayed if Display Value is entered at org level.
46 01-Jun-2010 DDUVVURI 1.8 9679498,9742617 Added character set conversion changes in deinitialization procedure and
47 Group3 cursors have been modified completely. Group3 only shows employees who
48 have payrolls run for the period. Layout changes done in deinitialization procedure
49 for Chinese session. Also some error cases have also been checked.
50 27-Oct-2010 MDUBASI 1.11 10115998 Modified function get_cost_alloc_key_flex
51 ============================================================================
52 */
53 --
54 -- Global Variables
55 --
56 g_char_set VARCHAR2(100);
57 g_proc_name VARCHAR2(240);
58 g_debug BOOLEAN;
59 g_document_type VARCHAR2(50);
60 EOL VARCHAR2(5) := fnd_global.local_chr(10);
61 g_opt_soe_or_min VARCHAR2(10);
62 g_trfr_date_used VARCHAR2(5);
63 l_proc VARCHAR2(200);
64
65 /****************************************************************************
66 Name : get_message_text
67 Description : This is called from inside the datatemplate query to
68 report missing data in audit report.
69 *****************************************************************************/
70
71 FUNCTION get_message_text(p_act_info1 VARCHAR2,
72 p_act_info2 VARCHAR2,
73 p_act_info3 VARCHAR2,
74 p_act_info4 VARCHAR2,
75 p_act_info5 VARCHAR2) RETURN VARCHAR2
76 IS
77 l_text VARCHAR2(1000);
78 BEGIN
79 l_proc := g_proc_name||'get_message_text';
80 IF g_debug THEN
81 hr_utility.trace( 'Entering '||l_proc);
82 END IF;
83 IF p_act_info3 = 'CN_EMP_CATEGORY' THEN
84 fnd_message.set_name('PER', 'CN_EMP_CATEGORY');
85 fnd_message.set_token('EMPNUM', p_act_info1);
86 fnd_message.set_token('PERIOD', p_act_info2);
87 ELSIF p_act_info3 ='CN_EXPENDITURE_CODE' THEN
88 fnd_message.set_name('PER', 'CN_EXPENDITURE_CODE');
89 fnd_message.set_token('ENAME', p_act_info4);
90 fnd_message.set_token('PNAME', p_act_info5);
91
92 END IF;
93
94 l_text := hr_utility.get_message();
95 IF g_debug THEN
96 hr_utility.trace( 'Leaving '||l_proc);
97 END IF;
98 RETURN l_text;
99 END;
100
101 /****************************************************************************
102 Name : RANGE_CURSOR
103 Description : This procedure prepares range of persons to be processed for process
104 'China Payroll Data Extract'. This procedure defines a SQL statement
105 to fetch all the people to be included in the generic XML extract. This SQL
106 statement is used to define the 'chunks' for multi-threaded operation
107 Arguments
108 p_pactid payroll action id for the report
109 p_sqlstr the SQL statement to fetch the people
110 *****************************************************************************/
111
112 PROCEDURE range_cursor ( p_pactid IN NUMBER,
113 p_sqlstr OUT nocopy VARCHAR2
114 )
115 IS
116
117 p_year_start DATE;
118 p_year_end DATE;
119 p_business_group_id NUMBER;
120 l_file VARCHAR2(100);
121 BEGIN
122
123 l_proc := g_proc_name||'range_cursor';
124
125 IF g_debug THEN
126 hr_utility.trace ('Entering '||l_proc);
127 END IF ;
128
129 p_sqlstr := 'SELECT DISTINCT person_id
130 FROM per_people_f ppf,
131 pay_payroll_actions ppa
132 WHERE ppa.payroll_action_id = :payroll_action_id
133 AND ppa.business_group_id +0 = ppf.business_group_id
134 ORDER BY ppf.person_id';
135
136 initialization_code (p_pactid);
137
138 IF g_debug THEN
139 hr_utility.trace ('Leaving '||l_proc);
140 END IF ;
141
142 END range_cursor;
143 --
144
145 /****************************************************************************
146 Name : ACTION_CREATION
147 Description : This procedure creates assignment actions for the payroll action associated
148 process <China Payroll Data Extract>
149
150 The procedure processes assignments in 'chunks' to facilitate multi-threaded
151 operation. The chunk is defined by the size and the starting and ending person id.
152
153 One assignment action is created for each payroll period for each assignment
154 Creates action for Group4 XML - Individual_Payroll_Detailed_Records
155 Best way is to do Group3 also in multithreaded level. But Sorting
156 requirements won't be met.
157
158 *****************************************************************************/
159
160 PROCEDURE action_creation(
161 p_pactid IN NUMBER,
162 p_stperson IN NUMBER,
163 p_endperson IN NUMBER,
164 p_chunk IN NUMBER
165 )
166 IS
167 -- Cursor to get the assignments Who were active for some period in the current year
168 -- Auditing Interested only for employees paid in current year, so check that atleast it has one payroll run
169 -- g_start_date AND g_end_date are accounting period start and end
170 -- Pick up payroll runs, use g_trfr_date_used to determine the accounting_date on which they will be posted
171 -- Ensure those are within the accounting period range
172 --
173 CURSOR csr_assignments IS
174 SELECT paf.assignment_id
175 FROM per_people_f ppf,
176 per_assignments_f paf,
177 per_periods_of_service pos
178 WHERE ppf.business_group_id = paf.business_group_id
179 AND pos.period_of_service_id = paf.period_of_service_id
180 AND paf.person_id =ppf.person_id
181 AND paf.person_id BETWEEN p_stperson AND p_endperson
182 AND (
183 g_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
184 OR
185 (
186 pos.final_process_date BETWEEN g_start_date AND g_end_date AND
187 pos.final_process_date BETWEEN paf.effective_start_date AND paf.effective_end_date
188 )
189 )
190 AND (
191 ( nvl(pos.final_process_date,g_end_date) >= g_end_date
192 AND g_end_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
193 OR
194 (
195 pos.final_process_date BETWEEN g_start_date AND g_end_date AND
196 pos.final_process_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
197 )
198 )
199 AND ppf.business_group_id = g_bg_id
200 AND paf.business_group_id =g_bg_id
201 AND EXISTS (SELECT 1
202 FROM pay_payroll_actions ppa,
203 pay_assignment_actions paa
204 WHERE paa.assignment_id =paf.assignment_id
205 AND paa.payroll_action_id = ppa.payroll_action_id
206 AND ppa.action_type in('R','Q')
207 AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) BETWEEN g_start_date AND g_end_date
208 );
209 --
210 -- LOCAL VARIABLES
211 --
212 l_actid NUMBER;
213
214
215 -- Group4 is sorted by payroll_period
216 -- so add 1 assignment_action_id for each distinct payroll period
217 -- ppa.effective_date gives payroll year and period
218 -- Use this and populate serial_number
219 -- do not use accounting period to define serial_number
220 -- use serial number in sort_code to ensure XML sequence in output
221 -- 2010 01 Should come after 2009 12. So use period_num and year combination
222
223 CURSOR c_get_periods(p_assignment_id IN NUMBER) IS
224 select distinct(to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')||ppf.payroll_id) pnum
225 from pay_payroll_Actions ppa,
226 pay_assignment_actions paa,
227 pay_payrolls_f ppf
228 where paa.payroll_action_id = ppa.payroll_Action_id
229 and ppa.action_type in('R','Q')
230 and ppa.payroll_id = ppf.payroll_id
231 and paa.assignment_id = p_assignment_id
232 and paa.source_action_id is null
233 and decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) BETWEEN g_start_date AND g_end_date
234 and g_end_date between ppf.effective_start_date and ppf.effective_end_date
235 ORDER BY pnum;
236
237 --
238 BEGIN
239
240 l_proc := g_proc_name||'action_creation';
241
242 IF g_debug THEN
243 hr_utility.trace ('Entering '||l_proc);
244 END IF ;
245
246 IF g_bg_id IS NULL THEN
247 initialization_code (p_pactid);
248 END IF;
249
250
251 FOR i IN csr_assignments LOOP
252 IF g_debug THEN
253 hr_utility.trace (' Picking Assignment ID '||i.assignment_id);
254 END IF;
255
256 FOR q IN c_get_periods(i.assignment_id) LOOP
257 IF g_debug THEN
258 hr_utility.trace (' Picking Period NUmber '||q.pnum);
259 END IF;
260
261 SELECT pay_assignment_actions_s.NEXTVAL
262 INTO l_actid
263 FROM dual;
264
265
266 hr_nonrun_asact.insact(l_actid,i.assignment_id,p_pactid,p_chunk,NULL);
267
268 UPDATE pay_assignment_actions
269 SET serial_number = q.pnum
270 WHERE assignment_action_id = l_actid
271 AND assignment_id = i.assignment_id
272 AND payroll_action_id = p_pactid;
273
274 END LOOP;
275 END LOOP;
276
277 IF g_debug THEN
278 hr_utility.trace ('Leaving '||l_proc);
279 END IF ;
280
281 END action_creation;
282
283 /****************************************************************************
284 Name : get_parameters
285 Description : This procedure gets the token value of a token from the
286 legislative parameters string for a given payroll action id.
287 *****************************************************************************/
288 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
289 p_token_name IN VARCHAR2,
290 p_token_value OUT NOCOPY VARCHAR2)
291 IS
292
293 CURSOR csr_parameter_info(p_pact_id NUMBER)
294 IS
295 SELECT legislative_parameters
296 ,business_group_id
297 FROM pay_payroll_actions
298 WHERE payroll_action_id = p_pact_id;
299
300 l_token_value VARCHAR2(150);
301 l_bg_id NUMBER;
302 l_proc VARCHAR2(100);
303 l_message VARCHAR2(255);
304 l_param_string VARCHAR2(1000);
305 start_ptr NUMBER;
306 end_ptr NUMBER;
307 token_val pay_payroll_actions.legislative_parameters%TYPE;
308
309 BEGIN
310
311 l_proc := g_proc_name||'get_parameters';
312
313 IF g_debug THEN
314 hr_utility.trace ('Entering '||l_proc);
315 END IF ;
316
317 OPEN csr_parameter_info(p_payroll_action_id);
318 FETCH csr_parameter_info INTO l_param_string,l_bg_id;
319 CLOSE csr_parameter_info;
320
321 token_val := p_token_name||'=';
322
323 start_ptr := INSTR(l_param_string, token_val) + LENGTH(token_val);
324 end_ptr := INSTR(l_param_string, ' ',start_ptr);
325
326 IF end_ptr = 0 THEN
327 end_ptr := LENGTH(l_param_string)+1;
328 END IF;
329
330 IF INSTR(l_param_string, token_val) = 0 THEN
331 l_token_value := NULL;
332 ELSE
333 l_token_value := SUBSTR(l_param_string, start_ptr, end_ptr - start_ptr);
334 END IF;
335
336 p_token_value := TRIM(l_token_value);
337
338 IF (p_token_name = 'BG_ID') THEN
339 p_token_value := l_bg_id;
340 END IF;
341
342 IF (p_token_value IS NULL) THEN
343 p_token_value := '%';
344 END IF;
345
346 IF g_debug THEN
347 hr_utility.trace ('Leaving '||l_proc);
348 END IF ;
349
350 END get_parameters;
351
352 --
353
354 /****************************************************************************
355 Name : generate_xml
356 Description : This procedure fetches archived data, converts it to XML
357 format and appends to pay_mag_tape.g_BLOB_value.
358 *****************************************************************************/
359 PROCEDURE generate_xml AS
360 l_old_assact_id NUMBER;
361 l_final_xml_string BLOB;
362 xml_string1 VARCHAR2(2000);
363 l_pact_id NUMBER;
364 l_cur_pact NUMBER;
365 l_legislative_parameters VARCHAR(2000);
366 l_cur_assact NUMBER ;
367 l_proc_name VARCHAR2(60) ;
368 l_offset NUMBER;
369 l_amount NUMBER;
370 l_count NUMBER;
371 --
372 BEGIN
373 IF g_debug THEN
374 l_proc_name := g_proc_name || 'GENERATE_XML';
375 hr_utility.trace ('Entering '||l_proc_name);
376 END IF ;
377
378 l_cur_assact := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID' );
379 l_cur_pact := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID' );
380
381 assact_xml(l_cur_assact);
382
383 EXCEPTION
384 WHEN no_data_found THEN
385 hr_utility.trace ('exiting from loop');
386 --
387 IF g_debug THEN
388 hr_utility.trace ('Leaving '||l_proc_name);
389 END IF ;
390 END generate_xml;
391
392 /****************************************************************************
393 Name : initialization_code
394 Description : This procedure initialises global values
395 We call it at the beginning of the archive
396 *****************************************************************************/
397
398 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
399 IS
400 --
401 l_proc VARCHAR2(100) ;
402 l_message VARCHAR2(255);
403 l_chardate_start VARCHAR2(255);
404 l_chardate_end VARCHAR2(255);
405 l_tag varchar2(10);
406
407 l_token_name pay_in_utils.char_tab_type;
408 l_token_value pay_in_utils.char_tab_type;
409
410
411 BEGIN
412 --
413 l_proc := g_proc_name || 'initialization_code';
414
415 if g_debug then
416 hr_utility.trace ('Entering '||l_proc);
417 end if ;
418
419 g_payroll_action_id := p_payroll_action_id;
420 get_parameters(p_payroll_action_id,'YR',g_year);
421 get_parameters(p_payroll_action_id,'START_DATE',l_chardate_start);
422 g_start_date := fnd_date.canonical_to_date(l_chardate_start);
423 get_parameters(p_payroll_action_id,'END_DATE',l_chardate_end);
424 g_end_date := fnd_date.canonical_to_date(l_chardate_end);
425 get_parameters(p_payroll_action_id,'BG_ID',g_bg_id);
426 get_parameters(p_payroll_action_id,'XML_REPORT_TAG',l_tag);
427 get_parameters(p_payroll_action_id,'DEST_CHAR_SET',g_char_set);
428
429 set_globals;
430 g_start_period := to_char(g_start_date,'MM');
431 g_end_period := to_char(g_end_date,'MM');
432
433
434
435 if g_debug then
436 hr_utility.trace ('YR '||g_year);
437 hr_utility.trace ('g_bg_id '||g_bg_id);
438 hr_utility.trace ('g_start_period '||g_start_period);
439 hr_utility.trace ('g_end_period '||g_end_period);
440 hr_utility.trace ('g_start_date '||g_start_date);
441 hr_utility.trace ('g_end_date '||g_end_date);
442 hr_utility.trace ('XML REPORT TAG '||l_tag);
443 hr_utility.trace ('SOE/MINISTRY Flag '||g_opt_soe_or_min);
444 hr_utility.trace ('TGL Date Used '||g_trfr_date_used);
445 hr_utility.trace ('Destination character set '||g_char_set);
446 hr_utility.trace ('Leaving '||l_proc);
447 end if ;
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 RAISE;
452 END initialization_code;
453
454 /****************************************************************************
455 Name : gen_xml_header_pay
456 Description : This procedure generates XML header information and appends to
457 magtape lob
458 *****************************************************************************/
459 PROCEDURE gen_xml_header_pay AS
460 l_proc_name varchar2(100);
461 l_buf varchar2(2000);
462 --
463 /* Group1 - Payroll Periods */
464 /* Fetch payroll periods whose date_earned/regular_payment_date is between accounting reporting periods*/
465 /* Accounting date depends on g_trfr_date_used, hence the join*/
466 CURSOR csr_payroll_periods IS
467 SELECT to_char(ptp.period_num) pno,
468 to_CHAR(ptp.start_date,'YYYYMMDD') strt,
469 to_CHAR(ptp.end_date,'YYYYMMDD') ende,
470 to_CHAR(ptp.regular_payment_date,'YYYY') yr
471 FROM per_time_periods ptp,
472 pay_payrolls_f ppf
473 WHERE ptp.payroll_id = ppf.payroll_id
474 AND decode(g_trfr_date_used,'E',ptp.end_date,ptp.regular_payment_date) BETWEEN g_start_date and g_end_date
475 AND EXISTS(SELECT 1
476 FROM per_assignments_f paf
477 WHERE paf.payroll_id = ppf.payroll_id
478 AND paf.business_group_id = g_bg_id)
479 order by ppf.payroll_name,yr,ptp.period_num;
480
481 /* Group2 - Payroll Elements */
482 /* Fetch payroll elements from Org level Setup*/
483 CURSOR csr_payroll_elements IS
484 SELECT DISTINCT pap.payroll_name payroll_name,
485 fnd_number.canonical_to_number(hoi.org_information3) element_id,
486 nvl(petl.reporting_name,petl.element_name) element_name,
487 get_cost_alloc_key_flex(hoi.org_information3) exp_cat_code
488 FROM hr_organization_information hoi
489 ,hr_organization_units hou
490 ,pay_payrolls_f pap
491 ,pay_element_types_f_tl petl
492 ,pay_element_types_f pet
493 WHERE hoi.org_information_context = 'PER_CNAO_ORG_INFO'
494 AND hou.organization_id = hoi.organization_id
495 AND hou.business_group_id = g_bg_id
496 AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information2)
497 AND EXISTS (SELECT 1
498 FROM per_assignments_f paf
499 WHERE paf.payroll_id = pap.payroll_id
500 and paf.effective_end_date >= g_start_date
501 and paf.effective_start_date <= g_end_date)
502 AND EXISTS (SELECT 1
503 FROM per_assignments_f paf,
504 hr_soft_coding_keyflex scl
505 WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
506 AND scl.segment1 = hou.organization_id
507 AND paf.business_group_id = g_bg_id
508 )
509 AND petl.element_type_id = fnd_number.canonical_to_number(hoi.org_information3)
510 AND pet.element_type_id = petl.element_type_id
511 AND petl.language = userenv('LANG')
512 AND hou.date_from <= g_end_date
513 AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD')) >= g_start_date
514 AND g_end_date >= fnd_date.canonical_to_date(hoi.org_information4)
515 AND g_start_date <= NVL(fnd_date.canonical_to_date(hoi.org_information5),TO_DATE('4712/12/31','YYYY/MM/DD'))
516 AND g_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
517 AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date
518 UNION
519 SELECT DISTINCT pap.payroll_name payroll_name,
520 fnd_number.canonical_to_number(hoi.org_information2) element_id,
521 nvl(PBT_TL.REPORTING_NAME,PBT_TL.BALANCE_NAME) element_name,
522 'X' exp_cat_code
523 FROM hr_organization_information hoi
524 ,hr_organization_units hou
525 ,pay_payrolls_f pap
526 ,PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
527 WHERE hoi.org_information_context = 'PER_CNAO_BAL_INFO'
528 AND hou.organization_id = hoi.organization_id
529 AND hou.business_group_id = g_bg_id
530 AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information4)
531 AND EXISTS (SELECT 1
532 FROM per_assignments_f paf
533 WHERE paf.payroll_id = pap.payroll_id
534 and paf.effective_end_date >= g_start_date
535 and paf.effective_start_date <= g_end_date)
536 AND EXISTS (SELECT 1
537 FROM per_assignments_f paf,
538 hr_soft_coding_keyflex scl
539 WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
540 AND scl.segment1 = hou.organization_id
541 AND paf.business_group_id = g_bg_id
542 )
543 AND PBT_TL.balance_type_id = fnd_number.canonical_to_number(hoi.org_information2)
544 AND PBT.balance_type_id = PBT_TL.balance_type_id
545 AND PBT_TL.language = userenv('LANG')
546 AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
547
548
549 /* Group 3 Individual Payroll Records */
550 -- Group each assignment by payroll
551 CURSOR csr_asg IS
552 SELECT paf.assignment_id ,
553 paf.assignment_number,
554 max(paa.assignment_action_id) assignment_action_id,
555 ppf.payroll_name
556 FROM per_assignments_f paf,
557 pay_payroll_actions ppa,
558 pay_assignment_actions paa,
559 pay_payrolls_f ppf
560 WHERE paf.effective_start_date <= g_end_date
561 AND paf.effective_end_date >= g_start_date
562 AND paf.business_group_id = g_bg_id
563 AND paa.assignment_id =paf.assignment_id
564 AND paa.payroll_action_id = ppa.payroll_action_id
565 AND ppa.action_type in('R','Q')
566 and ppa.action_status='C'
567 and ppf.payroll_id = ppa.payroll_id
568 AND paf.payroll_id = ppa.payroll_id
569 AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date)
570 BETWEEN ppf.effective_start_date AND ppf.effective_end_date
571 AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date)
572 BETWEEN g_start_date AND g_end_date
573 group by paf.assignment_id,paf.assignment_number,ppf.payroll_name, to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')
574 order by to_number(to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')),ppf.payroll_name, paf.assignment_number;
575
576
577 /* cursor to get lookup values based on lookup_type and lookup_code */
578 CURSOR get_lookup_values(p_type IN VARCHAR2, p_code IN VARCHAR2)
579 IS
580 SELECT lookup_code,meaning FROM fnd_lookup_values
581 WHERE lookup_type = p_type
582 AND enabled_flag = 'Y'
583 AND lookup_code LIKE p_code
584 AND language = USERENV('LANG')
585 ORDER BY lookup_code;
586
587 CURSOR csr_group3_data(p_assignment_id IN NUMBER,
588 p_assignment_action_id IN NUMBER)
589 IS
590 SELECT TO_CHAR(get_employee_number(paf.person_id,ppa.effective_date)) eno,
591 per_cn_shared_info.get_lookup_meaning(paf.employee_category,'EMPLOYEE_CATG') asg_cat ,
592 get_employee_name(paf.person_id,ppa.effective_date) emp_name,
593 to_char(paf.organization_id) asg_org_id,
594 (select distinct p.payroll_name from pay_payrolls_f p where p.payroll_id = paf.payroll_id) pname,
595 to_CHAR(ppa.effective_date,'YYYY') yr,
596 to_number(TO_CHAR(ppa.effective_date,'MM')) pno,
597 to_CHAR(decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) ,'YYYY') acct_yr,
598 to_number(to_CHAR(decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) ,'MM')) accnt_prd,
599 'CNY' currency
600 from per_all_assignments_f paf,
601 pay_payroll_actions ppa,
602 pay_assignment_actions paa
603 where ppa.effective_date between paf.effective_start_date and paf.effective_end_date
604 and paf.assignment_id = p_assignment_id
605 and paa.assignment_id = paf.assignment_id
606 and paa.payroll_action_id = ppa.payroll_action_id
607 and paa.assignment_action_id = p_assignment_action_id;
608
609 CURSOR get_payroll_id(p_payroll_name IN VARCHAR2)
610 IS
611 select distinct payroll_id
612 from pay_payrolls_f
613 where payroll_name = p_payroll_name;
614
615 v_pay_prd tab_pay_prd;
616 l_ctr NUMBER;
617 flag NUMBER;
618 rec_count NUMBER;
619 v_ptp_rec ptp_rec;
620 payrec tab_pay_prd_dis;
621 result tab_pay_prd_dis;
622 l_soe_header VARCHAR2(1000);
623 l_min_header VARCHAR2(1000);
624 l_action_info_id pay_action_information.action_information_id%TYPE;
625 l_ovn NUMBER;
626 j NUMBER;
627 i NUMBER;
628 TYPE l_asg_bulk is TABLE OF csr_asg%ROWTYPE INDEX BY BINARY_INTEGER;
629 asgrec l_asg_bulk;
630
631 group3rec csr_group3_data%ROWTYPE;
632 l_payroll_id NUMBER;
633
634 l_head_trans1 VARCHAR2(100);
635 l_head_trans2 VARCHAR2(100);
636
637 BEGIN
638 IF g_debug THEN
639 l_proc_name := g_proc_name || 'gen_xml_header_pay';
640 hr_utility.trace ('Entering '||l_proc_name);
641 END IF ;
642
643 IF g_opt_soe_or_min IS NULL OR g_opt_soe_or_min ='ENT' THEN
644 l_head_trans1 := per_cn_shared_info.get_lookup_meaning('HEADER_1','CN_SOE_LABELS');
645 l_head_trans2 := per_cn_shared_info.get_lookup_meaning('HEADER_2','CN_SOE_LABELS');
646 l_soe_header := l_head_trans1||' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema '||
647 l_head_trans1||'.xsd" xmlns:'||l_head_trans2||
648 '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
649 load_xml_internal('SG',l_soe_header,null);
650 ELSE
651 l_head_trans1 := per_cn_shared_info.get_lookup_meaning('HEADER_1','CN_MINISTRY_LABELS');
652 l_head_trans2 := per_cn_shared_info.get_lookup_meaning('HEADER_2','CN_MINISTRY_LABELS');
653 l_min_header := l_head_trans1||' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema '||
654 l_head_trans1||'.xsd" xmlns:'||l_head_trans2||
655 '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema" xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
656 load_xml_internal('SG',l_min_header,null);
657 END IF;
658
659 l_ctr := 0;
660
661 OPEN csr_payroll_periods;
662 FETCH csr_payroll_periods BULK COLLECT INTO payrec;
663 CLOSE csr_payroll_periods;
664
665 l_ctr := 0;
666 flag := 0;
667
668 if payrec.COUNT > 0 THEN
669 FOR i in payrec.FIRST .. payrec.LAST LOOP
670 flag := 0;
671 FOR j in 0 .. l_ctr-1 LOOP
672 if (payrec(i).p_num = result(j).p_num and payrec(i).p_start = result(j).p_start and
673 payrec(i).p_end = result(j).p_end and payrec(i).p_year = result(j).p_year) then
674 flag := 1;
675 EXIT;
676 END IF;
677 END LOOP;
678 if flag <> 1 then
679 result(l_ctr).p_num := payrec(i).p_num;
680 result(l_ctr).p_start := payrec(i).p_start;
681 result(l_ctr).p_end := payrec(i).p_end;
682 result(l_ctr).p_year := payrec(i).p_year;
683 l_ctr := l_ctr + 1;
684 end if;
685 END LOOP;
686
687 /* Below code to add Payroll information to the XML */
688 FOR p IN result.FIRST .. result.LAST LOOP
689 load_xml_internal('SG', g_pay_prd(0).p_meaning , NULL);
690 load_xml_internal('CG', g_pay_prd(1).p_meaning , result(p).p_year);
691 load_xml_internal('CG', g_pay_prd(2).p_meaning , result(p).p_num);
692 load_xml_internal('CG', g_pay_prd(3).p_meaning , result(p).p_start);
693 load_xml_internal('CG', g_pay_prd(4).p_meaning , result(p).p_end);
694 load_xml_internal('EG', g_pay_prd(0).p_meaning , NULL);
695 END LOOP;
696
697 END IF;
698 /* Below code to add Element information to the XML */
699
700 result.DELETE;
701 payrec.DELETE;
702 l_ctr := 0;
703
704 for j in csr_payroll_elements LOOP
705 load_xml_internal('SG', g_pay_ele(0).p_meaning , NULL);
706 load_xml_internal('CG', g_pay_ele(1).p_meaning , j.payroll_name);
707 load_xml_internal('CG', g_pay_ele(2).p_meaning , j.element_id);
708 load_xml_internal('CG', g_pay_ele(3).p_meaning , j.element_name);
709 IF g_opt_soe_or_min = 'NPG' THEN
710 IF j.exp_cat_code IS NULL THEN
711
712 pay_action_information_api.create_action_information
713 (p_action_context_id => g_payroll_action_id
714 ,p_action_context_type => 'PA'
715 ,p_action_information_category => 'CN_AUDIT_MESSAGES'
716 ,p_action_information3 => 'CN_EXPENDITURE_CODE'
717 ,p_action_information4 => j.element_name
718 ,p_action_information5 => j.payroll_name
719 ,p_action_information_id => l_action_info_id
720 ,p_object_version_number => l_ovn
721 );
722
723 load_xml_internal('CG', g_pay_ele(4).p_meaning , per_cn_shared_info.get_lookup_meaning('NODATA','CN_AUDIT_DATA'));
724 ELSE
725 IF j.exp_cat_code = 'X' THEN
726 load_xml_internal('CG', g_pay_ele(4).p_meaning , NULL);
727 ELSE
728 load_xml_internal('CG', g_pay_ele(4).p_meaning , j.exp_cat_code);
729 END IF;
730 END IF;
731
732 END IF;
733 load_xml_internal('EG', g_pay_ele(0).p_meaning , null);
734 END LOOP;
735
736 v_pay_prd.DELETE;
737 v_ptp_rec.DELETE;
738 l_ctr := 0;
739
740 OPEN csr_asg;
741 FETCH csr_asg BULK COLLECT INTO asgrec;
742 CLOSE csr_asg;
743
744
745 IF asgrec.COUNT > 0 THEN
746 for i in asgrec.first..asgrec.last LOOP
747
748
749 OPEN csr_group3_data(asgrec(i).assignment_id,asgrec(i).assignment_action_id);
750 FETCH csr_group3_data INTO group3rec;
751 IF csr_group3_data%FOUND THEN
752 IF group3rec.asg_cat IS NULL AND g_opt_soe_or_min ='ENT' then
753
754 pay_action_information_api.create_action_information
755 (p_action_context_id => g_payroll_action_id
756 ,p_action_context_type => 'PA'
757 ,p_action_information_category => 'CN_AUDIT_MESSAGES'
758 ,p_assignment_id => asgrec(i).assignment_id
759 ,p_action_information1 => group3rec.eno
760 ,p_action_information2 => group3rec.pno
761 ,p_action_information3 => 'CN_EMP_CATEGORY'
762 ,p_action_information_id => l_action_info_id
763 ,p_object_version_number => l_ovn
764 );
765 hr_utility.trace('Emp Cat Code for '||group3rec.eno||' for period '||group3rec.accnt_prd);
766 end if;
767 load_xml_internal('SG', g_ind_asg(0).p_meaning , null);
768 load_xml_internal('CG', g_ind_asg(1).p_meaning , group3rec.eno);
769
770 IF g_opt_soe_or_min IS NULL OR g_opt_soe_or_min ='ENT' THEN
771 load_xml_internal('CG', g_ind_asg(2).p_meaning , nvl(group3rec.asg_cat,per_cn_shared_info.get_lookup_meaning('NODATA','CN_AUDIT_DATA')));
772 ELSE
773 load_xml_internal('CG', g_ind_asg(2).p_meaning , group3rec.emp_name);
774 END IF;
775
776 load_xml_internal('CG', g_ind_asg(3).p_meaning , group3rec.asg_org_id);
777 load_xml_internal('CG', g_ind_asg(4).p_meaning , group3rec.pname);
778 load_xml_internal('CG', g_ind_asg(5).p_meaning , group3rec.yr);
779 load_xml_internal('CG', g_ind_asg(6).p_meaning , group3rec.pno);
780 load_xml_internal('CG', g_ind_asg(7).p_meaning , group3rec.acct_yr);
781 load_xml_internal('CG', g_ind_asg(8).p_meaning , group3rec.accnt_prd);
782 load_xml_internal('CG', g_ind_asg(9).p_meaning , group3rec.currency);
783 load_xml_internal('EG', g_ind_asg(0).p_meaning , null);
784 END IF;
785
786 CLOSE csr_group3_data;
787 END LOOP;
788 END IF;
789 if g_debug then
790 hr_utility.trace ('Leaving '||l_proc_name);
791 end if ;
792
793 END gen_xml_header_pay;
794
795 /****************************************************************************
796 Name : gen_xml_footer
797 Desc : Footer
798 *****************************************************************************/
799 PROCEDURE gen_xml_footer AS
800 l_buf varchar2(2000) ;
801 l_proc_name varchar2(100);
802 l_head_trans1 VARCHAR2(1000);
803 BEGIN
804 if g_debug then
805 l_proc_name := g_proc_name || 'gen_xml_footer';
806 hr_utility.trace ('Entering '||l_proc_name);
807 end if ;
808 --
809 IF g_opt_soe_or_min IS NULL OR g_opt_soe_or_min ='ENT' THEN
810 l_head_trans1 := per_cn_shared_info.get_lookup_meaning('HEADER_1','CN_SOE_LABELS');
811 load_xml_internal('EG',l_head_trans1,null);
812 ELSE
813 l_head_trans1 := per_cn_shared_info.get_lookup_meaning('HEADER_1','CN_MINISTRY_LABELS');
814 load_xml_internal('EG',l_head_trans1,null);
815 END IF;
816 if g_debug then
817 hr_utility.trace ('Leaving '||l_proc_name);
818 end if ;
819
820 END gen_xml_footer;
821
822 /****************************************************************************
823 Name : assact_xml
824 Arguments : p_assignment_action_id
825 Description : This procedure creates xml for the assignment_action_id passed
826 as parameter. It then writes the xml into magtape lob
827 We know the payroll period and assignment.
828 Get payroll runs in this period
829 Use payslip archive data to report the values
830 *****************************************************************************/
831 PROCEDURE assact_xml(p_assignment_action_id IN NUMBER) IS
832
833 -- p_pprd_num is payroll period . p_pyear is payroll year
834 -- Get payslip archive id that locks payroll runs within the chosen payroll period
835 -- MULTIPLE PAYROLLS and single archive - issue may come
836 -- Not using ptp.time_period_id joins due to Bug 9782488
837 CURSOR csr_assignments(p_assignment_id IN NUMBER,
838 p_pprd_num IN varchar2,
839 p_pyear IN varchar2,
840 p_payroll_id IN NUMBER) IS
841 select arch_paa.assignment_action_id asg_action_id ,
842 ppa.payroll_id payroll_id,
843 ppa.date_earned earn_date,
844 ppa.effective_date eff_date
845 from pay_payroll_actions arch_ppa,
846 pay_assignment_actions arch_paa,
847 pay_action_interlocks intl,
848 pay_payroll_Actions ppa,
849 pay_assignment_actions paa
850 where arch_paa.assignment_action_id = intl.locking_action_id
851 and arch_paa.payroll_Action_id = arch_ppa.payroll_action_id
852 and arch_paa.source_action_id is not null
853 and arch_ppa.action_type='X'
854 and ARCH_ppa.report_type='CN_PAYSLIP_ARCHIVE'
855 and arch_paa.assignment_id = p_assignment_id
856 and intl.locked_action_id = paa.assignment_action_id
857 and paa.payroll_action_id = ppa.payroll_Action_id
858 and ppa.action_type in('R','Q')
859 and ppa.payroll_id = p_payroll_id
860 and paa.assignment_id = p_assignment_id
861 and paa.source_action_id is null
862 and to_number(to_char(ppa.effective_date,'MM')) = p_pprd_num
863 and to_number(to_char(ppa.effective_date,'YYYY')) = p_pyear
864 order by payroll_id,asg_action_id;
865
866 -- Archive element details
867 CURSOR csr_arch_elements(p_assignment_id IN NUMBER,
868 p_payroll_id IN NUMBER,
869 p_action_context_id IN NUMBER,
870 p_date_earned IN DATE,
871 p_effective_date IN DATE) IS
872 select person.employee_number eno,
873 pname.payroll_name pay_name,
874 to_char(p_effective_date,'YYYY') pyear,
875 to_number(to_char(p_effective_date,'MM')) pnum,
876 pet.element_type_id eid,
877 trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
878 from pay_action_information pai,
879 (select pap.payroll_name
880 from pay_all_payrolls_f pap
881 where pap.payroll_id = p_payroll_id
882 AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
883 (SELECT ppf.employee_number
884 FROM per_all_people_f ppf,
885 per_all_assignments_f paf
886 WHERE paf.person_id = ppf.person_id
887 AND paf.assignment_id = p_assignment_id
888 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
889 AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
890 hr_organization_information hoi,
891 hr_organization_units hou,
892 pay_element_types_f_tl petl,
893 pay_element_types_f pet
894 where action_context_id = p_action_context_id
895 and pai.action_information_category = 'APAC ELEMENTS'
896 and pai.action_information1 = nvl(petl.reporting_name,petl.element_name)
897 and hoi.org_information_context = 'PER_CNAO_ORG_INFO'
898 AND hou.organization_id = hoi.organization_id
899 AND hou.business_group_id = g_bg_id
900 AND hoi.org_information2 = p_payroll_id
901 and petl.element_type_id = hoi.org_information3
902 and pet.element_type_id = petl.element_type_id
903 and petl.language = userenv('LANG')
904 AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
905 AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information4) AND NVL(fnd_date.canonical_to_date(hoi.org_information5),TO_DATE('4712/12/31','YYYY/MM/DD'))
906 AND g_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
907
908 -- Archive Other balance details
909 CURSOR csr_arch_balances(p_assignment_id IN NUMBER,
910 p_payroll_id IN NUMBER,
911 p_action_context_id IN NUMBER,
912 p_date_earned IN DATE,
913 p_effective_date IN DATE) IS
914 select person.employee_number eno,
915 pname.payroll_name pay_name,
916 to_char(p_effective_date,'YYYY') pyear,
917 to_number(to_char(p_effective_date,'MM')) pnum,
918 pbt.balance_type_id eid,
919 trim(to_char(fnd_number.canonical_to_number(pai.action_information6),'999999990.99')) current_amount
920 from pay_action_information pai,
921 (select pap.payroll_name
922 from pay_all_payrolls_f pap
923 where pap.payroll_id = p_payroll_id
924 AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
925 (SELECT ppf.employee_number
926 FROM per_all_people_f ppf,
927 per_all_assignments_f paf
928 WHERE paf.person_id = ppf.person_id
929 AND paf.assignment_id = p_assignment_id
930 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
931 AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
932 hr_organization_information hoi,
933 hr_organization_units hou,
934 PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
935 where action_context_id = p_action_context_id
936 and pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
937 and pai.action_information4 = (select nvl(h.ORG_INFORMATION7,PBT_TL.BALANCE_NAME)
938 from hr_organization_information h
939 where h.org_information_context = 'Business Group:Payslip Info'
940 and h.organization_id = g_bg_id
941 and h.ORG_INFORMATION1 = 'BALANCE'
942 and h.ORG_INFORMATION4 = PBT_TL.BALANCE_TYPE_ID
943 and h.ORG_INFORMATION5 = (select pbd.balance_dimension_id
944 from pay_balance_dimensions pbd
945 where legislation_code = 'CN'
946 and dimension_name = '_ASG_PTD')
947 )
948 and pai.action_information5 = 'ASG_PTD'
949 and hoi.org_information_context = 'PER_CNAO_BAL_INFO'
950 AND hou.organization_id = hoi.organization_id
951 AND hou.business_group_id = g_bg_id
952 AND hoi.org_information4 = p_payroll_id
953 and PBT_TL.balance_type_id = hoi.org_information2
954 and PBT.balance_type_id = PBT_TL.balance_type_id
955 and PBT_TL.language = userenv('LANG')
956 AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
957 AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
958
959 -- Archive Statutory balance details
960 CURSOR csr_arch_stat_balances(p_assignment_id IN NUMBER,
961 p_payroll_id IN NUMBER,
962 p_action_context_id IN NUMBER,
963 p_date_earned IN DATE,
964 p_effective_date IN DATE) IS
965 select person.employee_number eno,
966 pname.payroll_name pay_name,
967 to_char(p_effective_date,'YYYY') pyear,
968 to_number(to_char(p_effective_date,'MM')) pnum,
969 pbt.balance_type_id eid,
970 trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
971 from pay_action_information pai,
972 (select pap.payroll_name
973 from pay_all_payrolls_f pap
974 where pap.payroll_id = p_payroll_id
975 AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
976 (SELECT ppf.employee_number
977 FROM per_all_people_f ppf,
978 per_all_assignments_f paf
979 WHERE paf.person_id = ppf.person_id
980 AND paf.assignment_id = p_assignment_id
981 AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
982 AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
983 hr_organization_information hoi,
984 hr_organization_units hou,
985 PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
986 where action_context_id = p_action_context_id
987 and pai.action_information_category = 'APAC BALANCES'
988 and pai.action_information1 = nvl(PBT_TL.REPORTING_NAME,PBT_TL.BALANCE_NAME)
989 and hoi.org_information_context = 'PER_CNAO_BAL_INFO'
990 AND hou.organization_id = hoi.organization_id
991 AND hou.business_group_id = g_bg_id
992 AND hoi.org_information4 = p_payroll_id
993 and PBT_TL.balance_type_id = hoi.org_information2
994 and PBT.balance_type_id = PBT_TL.balance_type_id
995 and PBT_TL.language = userenv('LANG')
996 AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
997 AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
998
999 CURSOR get_lookup_values(p_type IN VARCHAR2, p_code IN VARCHAR2)
1000 IS
1001 select lookup_code,meaning from fnd_lookup_values
1002 where lookup_type = p_type
1003 and enabled_flag = 'Y'
1004 and lookup_code like p_code
1005 and language = userenv('lang')
1006 order by lookup_code;
1007
1008 --Variables-----
1009 l_common_xml VARCHAR2(4000);
1010 l_xml_begin VARCHAR2(200);
1011 l_xml2 VARCHAR2(40);
1012 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1013 l_assignment_id number;
1014 l_serial_number varchar2(500);
1015 l_pact number;
1016 l_ctr number;
1017 isdf_emp_c csr_assignments%ROWTYPE;
1018 v_pay_prd tab_pay_prd;
1019 l_year VARCHAR2(40);
1020 l_period number;
1021 l_assact_id number;
1022 l_payr_id number;
1023
1024
1025 BEGIN
1026
1027 select assignment_id,payroll_action_id,serial_number
1028 into l_assignment_id,l_pact,l_serial_number
1029 from
1030 pay_assignment_Actions
1031 where assignment_Action_id = p_assignment_action_id;
1032
1033 IF g_year IS NULL then
1034 initialization_code(l_pact);
1035 END IF;
1036
1037 -- l_serial_number is combination of payroll year + payroll period
1038 l_year := SUBSTR(l_serial_number,1,4);
1039 l_period := SUBSTR(l_serial_number,5,2);
1040 l_payr_id := to_number(substr(l_serial_number,7));
1041
1042
1043 FOR i IN csr_assignments(l_assignment_id,l_period,l_year,l_payr_id) LOOP
1044
1045 hr_utility.trace('Payroll YearPeriod for assignment action'||l_serial_number);
1046 if(l_assact_id is null or l_assact_id <> i.asg_action_id) then
1047
1048 FOR j in csr_arch_elements(l_assignment_id,i.payroll_id, i.asg_action_id,i.earn_date,i.eff_date) LOOP
1049 hr_utility.trace('XML elements being created for assignment_id id '||l_assignment_id);
1050 hr_utility.trace('Period for assignment action'||l_period);
1051
1052 load_xml_internal('SG',g_ind_detail(0).p_meaning,NULL);
1053 load_xml_internal('CG',g_ind_detail(1).p_meaning,j.eno);
1054 load_xml_internal('CG',g_ind_detail(2).p_meaning,j.pay_name);
1055 load_xml_internal('CG',g_ind_detail(3).p_meaning,j.pyear);
1056 load_xml_internal('CG',g_ind_detail(4).p_meaning,j.pnum);
1057 load_xml_internal('CG',g_ind_detail(5).p_meaning,j.eid);
1058 load_xml_internal('CG',g_ind_detail(6).p_meaning,j.current_amount);
1059 load_xml_internal('EG',g_ind_detail(0).p_meaning,NULL);
1060 l_assact_id := i.asg_action_id;
1061
1062 END LOOP;
1063 FOR k in csr_arch_balances(l_assignment_id,i.payroll_id, i.asg_action_id,i.earn_date,i.eff_date) LOOP
1064 hr_utility.trace('XML balances being created for assignment_id id '||l_assignment_id);
1065 hr_utility.trace('Period for assignment action'||l_period);
1066
1067 load_xml_internal('SG',g_ind_detail(0).p_meaning,NULL);
1068 load_xml_internal('CG',g_ind_detail(1).p_meaning,k.eno);
1069 load_xml_internal('CG',g_ind_detail(2).p_meaning,k.pay_name);
1070 load_xml_internal('CG',g_ind_detail(3).p_meaning,k.pyear);
1071 load_xml_internal('CG',g_ind_detail(4).p_meaning,k.pnum);
1072 load_xml_internal('CG',g_ind_detail(5).p_meaning,k.eid);
1073 load_xml_internal('CG',g_ind_detail(6).p_meaning,k.current_amount);
1074 load_xml_internal('EG',g_ind_detail(0).p_meaning,NULL);
1075 l_assact_id := i.asg_action_id;
1076
1077 END LOOP;
1078
1079 FOR x in csr_arch_stat_balances(l_assignment_id,i.payroll_id, i.asg_action_id,i.earn_date,i.eff_date) LOOP
1080 hr_utility.trace('XML stat balances being created for assignment_id id '||l_assignment_id);
1081 hr_utility.trace('Period for assignment action'||l_period);
1082
1083 load_xml_internal('SG',g_ind_detail(0).p_meaning,NULL);
1084 load_xml_internal('CG',g_ind_detail(1).p_meaning,x.eno);
1085 load_xml_internal('CG',g_ind_detail(2).p_meaning,x.pay_name);
1086 load_xml_internal('CG',g_ind_detail(3).p_meaning,x.pyear);
1087 load_xml_internal('CG',g_ind_detail(4).p_meaning,x.pnum);
1088 load_xml_internal('CG',g_ind_detail(5).p_meaning,x.eid);
1089 load_xml_internal('CG',g_ind_detail(6).p_meaning,x.current_amount);
1090 load_xml_internal('EG',g_ind_detail(0).p_meaning,NULL);
1091 l_assact_id := i.asg_action_id;
1092
1093 END LOOP;
1094
1095 end if;
1096 END LOOP;
1097
1098 END assact_xml;
1099
1100 /****************************************************************************
1101 Name : load_xml_internal
1102 Arguments : p_node_type ( starting tag / ending tag / centre data )
1103 p_node ( Node name )
1104 p_data ( Node Value = NULL for starting and ending tags)
1105 Description : This procedure writes the xml tag and its value to magtape lob
1106 *****************************************************************************/
1107
1108 PROCEDURE load_xml_internal ( p_node_type IN VARCHAR2
1109 ,p_node IN VARCHAR2
1110 ,p_data IN VARCHAR2) IS
1111 l_proc_name VARCHAR2(100);
1112 l_data VARCHAR2(240);
1113 l_xml VARCHAR2(1000);
1114 l_node varchar2(2000);
1115
1116 BEGIN
1117 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
1118
1119 IF g_debug THEN
1120 hr_utility.trace ('Entering '||l_proc_name);
1121 END IF;
1122
1123 IF p_node_type = 'ROOT' THEN
1124
1125 l_xml := '<![CDATA['||p_node||']]>'||EOL;
1126
1127 ELSIF p_node_type = 'SG' THEN
1128
1129 l_xml := '<'||p_node||'>'||EOL;
1130
1131 ELSIF p_node_type = 'EG' THEN
1132
1133 l_xml := '</'||p_node||'>'||EOL;
1134
1135 ELSIF p_node_type = 'CG' THEN
1136
1137 /* Handle special charaters in node value */
1138 l_data := REPLACE (p_data, '&', '&');
1139 l_data := REPLACE (l_data, '>', '>');
1140 l_data := REPLACE (l_data, '<', '<');
1141 l_data := REPLACE (l_data, '''', ''');
1142 l_data := REPLACE (l_data, '"', '"');
1143 l_xml := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
1144
1145 END IF;
1146
1147 pay_core_files.write_to_magtape_lob(l_xml);
1148
1149 IF g_debug THEN
1150 hr_utility.trace ('Leaving '||l_proc_name);
1151 hr_utility.trace ('XML Data being written is '||l_xml);
1152 END IF;
1153
1154 END load_xml_internal;
1155
1156 /****************************************************************************
1157 Name : set_globals
1158 Description : This procedure sets the values of certain globals which will
1159 be used during the archive process multiple times. Following
1160 are the globals which will be set:
1161 1. Global to check if Date Earned/Date Paid is used for Ledger Transfer
1162 2. Global to check if Auditing is for SOE / MINISTRY
1163 3. Global table to store SOE/MINISTRY XML Tags for all the 4 groups
1164 *****************************************************************************/
1165
1166 PROCEDURE set_globals IS
1167
1168 CURSOR csr_transfer_to_gl_date IS
1169 SELECT parameter_value
1170 FROM pay_action_parameters
1171 WHERE parameter_name ='TGL_DATE_USED';
1172
1173 CURSOR gen_xml_opt_soe_min
1174 IS
1175 SELECT hoi.org_information16
1176 FROM hr_organization_information hoi
1177 ,hr_organization_units hou
1178 WHERE hoi.org_information_context = 'PER_CORPORATE_INFO_CN'
1179 AND hou.organization_id = hoi.organization_id
1180 AND hou.business_group_id = g_bg_id
1181 AND EXISTS (SELECT 1
1182 FROM per_assignments_f paf,
1183 hr_soft_coding_keyflex scl
1184 WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1185 AND scl.segment1 = hou.organization_id
1186 AND paf.business_group_id = g_bg_id
1187 )
1188 ORDER BY org_information16;
1189
1190 l_lookup_type hr_lookups.lookup_type%TYPE;
1191
1192
1193 BEGIN
1194
1195 IF g_debug THEN
1196 hr_utility.trace('Entering set_globals procedure');
1197 END IF;
1198
1199 -- capture the value of g_opt_soe_or_min
1200 OPEN gen_xml_opt_soe_min;
1201 FETCH gen_xml_opt_soe_min INTO g_opt_soe_or_min;
1202 IF gen_xml_opt_soe_min%NOTFOUND THEN
1203 g_opt_soe_or_min := NULL;
1204 END IF;
1205 CLOSE gen_xml_opt_soe_min;
1206
1207 -- capture the value of g_trfr_date_used
1208 OPEN csr_transfer_to_gl_date;
1209 FETCH csr_transfer_to_gl_date into g_trfr_date_used;
1210 IF csr_transfer_to_gl_date%NOTFOUND THEN
1211 g_trfr_date_used :='E';
1212 END IF;
1213 CLOSE csr_transfer_to_gl_date;
1214
1215 IF g_opt_soe_or_min ='ENT' THEN
1216 l_lookup_type := 'CN_SOE_LABELS';
1217 ELSIF g_opt_soe_or_min ='NPG' THEN
1218 l_lookup_type := 'CN_MINISTRY_LABELS';
1219 ELSE
1220 l_lookup_type := 'CN_SOE_LABELS';
1221 END IF;
1222
1223
1224 IF l_lookup_type IS NOT NULL THEN
1225 g_pay_prd(0).p_meaning := per_cn_shared_info.get_lookup_meaning('PERIOD',l_lookup_type);
1226 g_pay_prd(1).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_13',l_lookup_type);
1227 g_pay_prd(2).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_14',l_lookup_type);
1228 g_pay_prd(3).p_meaning := per_cn_shared_info.get_lookup_meaning('PERIOD_13',l_lookup_type);
1229 g_pay_prd(4).p_meaning := per_cn_shared_info.get_lookup_meaning('PERIOD_14',l_lookup_type);
1230
1231 g_pay_ele(0).p_meaning := per_cn_shared_info.get_lookup_meaning('ELEMENT',l_lookup_type);
1232 g_pay_ele(1).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_12',l_lookup_type);
1233 g_pay_ele(2).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_15',l_lookup_type);
1234 g_pay_ele(3).p_meaning := per_cn_shared_info.get_lookup_meaning('ELEMENT_13',l_lookup_type);
1235 g_pay_ele(4).p_meaning := per_cn_shared_info.get_lookup_meaning('ELEMENT_14','CN_MINISTRY_LABELS');
1236
1237 g_ind_asg(0).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD',l_lookup_type);
1238 g_ind_asg(1).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_11',l_lookup_type);
1239 g_ind_asg(2).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD_12',l_lookup_type);
1240 g_ind_asg(3).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD_13',l_lookup_type);
1241 g_ind_asg(4).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_12',l_lookup_type);
1242 g_ind_asg(5).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_13',l_lookup_type);
1243 g_ind_asg(6).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_14',l_lookup_type);
1244 g_ind_asg(7).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD_17',l_lookup_type);
1245 g_ind_asg(8).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD_18',l_lookup_type);
1246 g_ind_asg(9).p_meaning := per_cn_shared_info.get_lookup_meaning('RECORD_19',l_lookup_type);
1247
1248
1249 g_ind_detail(0).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL',l_lookup_type);
1250 g_ind_detail(1).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_11',l_lookup_type);
1251 g_ind_detail(2).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_12',l_lookup_type);
1252 g_ind_detail(3).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_13',l_lookup_type);
1253 g_ind_detail(4).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_14',l_lookup_type);
1254 g_ind_detail(5).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_15',l_lookup_type);
1255 g_ind_detail(6).p_meaning := per_cn_shared_info.get_lookup_meaning('DETAIL_16',l_lookup_type);
1256
1257
1258 END IF;
1259
1260 IF g_debug THEN
1261 hr_utility.trace('Leaving set_globals procedure');
1262 END IF;
1263
1264 END set_globals;
1265
1266 /****************************************************************************
1267 Name : get_employee_number
1268 Arguments : 1. Person ID
1269 2. Effective Date
1270 Description : This function fetches the employee number based on a given
1271 person id and effective date and is used in some cursors above.
1272 *****************************************************************************/
1273 FUNCTION get_employee_number (p_person_id in number,
1274 p_effective_date in date)
1275 RETURN VARCHAR2 IS
1276
1277 CURSOR c_employee_number
1278 IS
1279 SELECT employee_number
1280 FROM per_all_people_f
1281 WHERE person_id = p_person_id
1282 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1283
1284 l_emp_num per_all_people_f.employee_number%TYPE;
1285
1286 BEGIN
1287
1288 IF g_debug THEN
1289 hr_utility.trace('Entering get_employee_number function');
1290 END IF;
1291
1292 OPEN c_employee_number;
1293 FETCH c_employee_number INTO l_emp_num;
1294 CLOSE c_employee_number;
1295
1296 IF g_debug THEN
1297 hr_utility.trace('Leaving get_employee_number function');
1298 END IF;
1299
1300 RETURN l_emp_num ;
1301
1302 END get_employee_number;
1303
1304 /****************************************************************************
1305 Name : get_employee_name
1306 Arguments : 1. Person ID
1307 2. Effective Date
1308 Description : This function fetches the employee name based on a given
1309 person id and effective date and is used in some cursors above.
1310 *****************************************************************************/
1311 FUNCTION get_employee_name(p_person_id in number,
1312 p_effective_date in date)
1313 RETURN VARCHAR2 IS
1314
1315 CURSOR c_employee_name
1316 IS
1317 SELECT full_name
1318 FROM per_all_people_f
1319 WHERE person_id = p_person_id
1320 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1321
1322 l_emp_name VARCHAR2(1000);
1323 BEGIN
1324
1325 OPEN c_employee_name;
1326 FETCH c_employee_name INTO l_emp_name;
1327 CLOSE c_employee_name;
1328
1329 RETURN l_emp_name ;
1330
1331 END get_employee_name;
1332
1333 /****************************************************************************
1334 Name : get_cost_alloc_key_flex
1335 Arguments : 1. Element Type ID
1336 Description : This function fetches the expenditure category code
1337 from the element links window based on the payroll
1338 costing segment
1339 *****************************************************************************/
1340 FUNCTION get_cost_alloc_key_flex(p_element_type_id IN NUMBER)
1341 RETURN VARCHAR2 IS
1342
1343 /* cursor to get cost allocation segment number */
1344 CURSOR c_get_cost_seg IS
1345 select ORG_INFORMATION2
1346 from HR_ORGANIZATION_INFORMATION
1347 where ORG_INFORMATION_CONTEXT='PER_CNAO_EXPEND_CODE_INFO'
1348 and ORGANIZATION_ID = g_bg_id;
1349
1350 /* cursor to fetch the expense category code segment*/
1351 CURSOR c_exp_cat_code(p_element_id IN NUMBER) IS
1352 select COST_ALLOCATION_KEYFLEX_ID
1353 from pay_element_links_f
1354 where business_group_id = g_bg_id
1355 and element_type_id = p_element_id
1356 and effective_start_date =
1357 (select min(effective_start_date) from pay_element_links_f where business_group_id = g_bg_id
1358 and element_type_id = p_element_id);
1359
1360 l_cost_seg VARCHAR2(100);
1361 l_cost_id NUMBER;
1362 l_segment VARCHAR2(100);
1363 statem varchar2(256);
1364 y varchar2(100);
1365
1366 BEGIN
1367
1368 OPEN c_get_cost_seg;
1369 FETCH c_get_cost_seg INTO l_cost_seg;
1370 CLOSE c_get_cost_seg;
1371
1372 OPEN c_exp_cat_code(p_element_type_id);
1373 FETCH c_exp_cat_code INTO l_cost_id;
1374 CLOSE c_exp_cat_code;
1375
1376 IF l_cost_seg IS NOT NULL AND l_cost_id IS NOT NULL THEN
1377 statem := 'select '||l_cost_seg||' FROM PAY_COST_ALLOCATION_KEYFLEX '||' where COST_ALLOCATION_KEYFLEX_ID = :l_cost ';
1378
1379 EXECUTE immediate statem INTO y USING l_cost_id;
1380 END IF;
1381
1382 RETURN y;
1383
1384 END get_cost_alloc_key_flex;
1385 /****************************************************************************
1386 Name : deinitialization_code
1387 Arguments : 1. payroll_action_id
1388 Description : This procedure is called in last phase of archive process
1389 where we spawn the CNAO Exception Listing report and
1390 print the PDF listing all the employees who have NULL assignment
1391 category
1392 *****************************************************************************/
1393
1394 PROCEDURE deinitialization_code (p_pactid IN NUMBER)
1395 IS
1396
1397 /* cursor to check if there are any assignments with null assignment category
1398 for a given payroll action id */
1399 CURSOR get_error is
1400 SELECT 1
1401 FROM pay_action_information
1402 WHERE action_context_id = p_pactid;
1403
1404 l_count NUMBER;
1405 i NUMBER;
1406 l_set_layout BOOLEAN;
1407 ln_reqid_gle NUMBER;
1408 ln_reqid_cvt NUMBER;
1409 lv_cvt_req_status VARCHAR2(100);
1410 l_file VARCHAR2(1000);
1411 ln_reqid_chg NUMBER;
1412 lv_chg_req_status VARCHAR2(100);
1413 lv_error_flag VARCHAR2(1);
1414 lv_error_status BOOLEAN;
1415 l_lang VARCHAR2(100);
1416
1417 BEGIN
1418
1419 IF g_debug THEN
1420 hr_utility.trace('Entering deinitialization_code procedure');
1421 END IF;
1422
1423 OPEN get_error;
1424 FETCH get_error INTO l_count;
1425 CLOSE get_error;
1426
1427 IF nvl(l_count,0) >0 THEN
1428
1429 SELECT USERENV('LANG')
1430 into l_lang
1431 from dual;
1432
1433 IF l_lang='US' THEN
1434 l_set_layout := fnd_request.add_layout('PAY','PYCNAOT','en','US','PDF');
1435 ELSE
1436 l_set_layout := fnd_request.add_layout('PAY','PYCNAOT','zh','CN','PDF');
1437 END IF;
1438
1439 i := FND_REQUEST.SUBMIT_REQUEST ( APPLICATION => 'PAY',
1440 PROGRAM => 'PYCNAOESP',
1441 ARGUMENT1 => p_pactid);
1442 END IF;
1443
1444 COMMIT;
1445
1446 SELECT
1447 request_id
1448 INTO
1449 ln_reqid_gle
1450 FROM
1451 pay_payroll_actions
1452 WHERE payroll_action_id = p_pactid;
1453
1454 IF g_opt_soe_or_min IS NULL OR g_opt_soe_or_min = 'ENT' THEN
1455 l_file := per_cn_shared_info.get_lookup_meaning('SOE_NAME','CN_AUDIT_DATA');
1456 ELSE
1457 l_file := per_cn_shared_info.get_lookup_meaning('PSM_NAME','CN_AUDIT_DATA');
1458 END IF;
1459
1460 l_file := l_file || g_year || lpad(to_char(g_start_period),2,'0') || lpad(to_char(g_end_period),2,'0');
1461
1462 --Submit characrter set conversion program
1463 --to convert charaterset of output file
1464 JA_CN_UTILITY.Submit_XML_Charset_Conversion(p_xml_request_id => ln_reqid_gle
1465 ,p_source_charset => JA_CN_UTILITY.Get_XML_Encoding
1466 ,p_destination_charset => g_char_set --'UTF-8'
1467 ,p_source_separator => NULL
1468 ,x_charset_request_id => ln_reqid_cvt
1469 ,x_result_flag => lv_cvt_req_status
1470 );
1471 IF lv_cvt_req_status='Success'
1472 THEN
1473
1474
1475 JA_CN_UTILITY.Change_Output_Filename(p_xml_request_id => ln_reqid_gle
1476 ,p_destination_charset => g_char_set --'UTF-8'
1477 ,p_destination_filename => l_file||'.xml'
1478 ,x_filename_request_id => ln_reqid_chg
1479 ,x_result_flag => lv_chg_req_status
1480 );
1481 IF lv_chg_req_status='Success'
1482 THEN
1483 NULL;
1484 ELSIF lv_chg_req_status='Warning'
1485 THEN
1486 lv_error_flag:='W';
1487 ELSIF lv_chg_req_status='Error'
1488 THEN
1489 lv_error_flag:='E';
1490 END IF;
1491
1492 ELSIF lv_cvt_req_status='Warning'
1493 THEN
1494 lv_error_flag:='W';
1495 ELSIF lv_cvt_req_status='Error'
1496 THEN
1497 lv_error_flag:='E';
1498 END IF;
1499
1500 IF lv_error_flag='W'
1501 THEN
1502 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'WARNING'
1503 , message => ''
1504 );
1505 ELSIF lv_error_flag='E'
1506 THEN
1507 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'ERROR'
1508 , message => ''
1509 );
1510 END IF; --lv_error_flag='W'
1511
1512 IF g_debug THEN
1513 hr_utility.trace('Leaving deinitialization_code procedure');
1514 END IF;
1515
1516 END;
1517
1518
1519 /****************************************************************************
1520 Name : sort_action
1521 Arguments : 1. payroll_action_id
1522 2. sql_string for deciding the sort order of data.
1523 3. length of the sql_string
1524 Description : This procedure sorts the individual xml's generated by
1525 assact_xml procedure based on SERIAL_NUMBER column which
1526 is populated with payroll year, payroll period and payroll id.
1527 *****************************************************************************/
1528
1529 PROCEDURE sort_action
1530 (
1531 payactid IN VARCHAR2, /* payroll action id */
1532 sqlstr IN OUT NOCOPY VARCHAR2, /* string holding the sql statement */
1533 len OUT NOCOPY NUMBER /* length of the sql string */
1534 ) IS
1535 l_chardate_start VARCHAR2(255);
1536 l_chardate_end VARCHAR2(255);
1537 BEGIN
1538
1539 IF g_debug THEN
1540 hr_utility.trace('Entering sort action procedure');
1541 END IF;
1542
1543 initialization_code(payactid);
1544
1545
1546 sqlstr := ' select paa.rowid
1547 from pay_assignment_actions paa,
1548 pay_payroll_actions ppa ,
1549 per_people_f ppf,
1550 pay_payrolls_f p
1551 where ppa.payroll_action_id = :pactid
1552 and paa.payroll_action_id = ppa.payroll_action_id
1553 AND '''||g_end_date||''' between p.effective_start_date and p.effective_end_date
1554 and '''||g_end_date||''' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1555 AND substr(paa.serial_number,7) = p.payroll_id
1556 and ppf.person_id = (select paf.person_id
1557 from per_assignments_f paf
1558 where paf.assignment_id = paa.assignment_id
1559 and rownum =1)
1560 order by to_number(substr(paa.serial_number,1,6)),p.payroll_name,ppf.employee_number
1561 for update of paa.assignment_id';
1562
1563 len := length(sqlstr); -- return the length of the string.
1564
1565 IF g_debug THEN
1566 hr_utility.trace('Leaving sort action procedure');
1567 END IF;
1568
1569 END sort_action;
1570
1571 BEGIN
1572
1573 g_proc_name := 'pay_cn_audit_xmlgen.';
1574 g_debug := hr_utility.debug_enabled;
1575 g_document_type := 'CNAO_XML_FOR_SOE_AND_MINISTRY';
1576
1577 END pay_cn_audit_xmlgen;
[Home] [Help]