[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_HRMS_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_HRMS_EXPORT_PKG AS
2 --$Header: JACNPHRB.pls 120.4 2010/09/25 02:48:20 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPHRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export department records, employee records |
13 --| and Employee Category for public sector |
14 --| |
15 --| |
16 --| PROCEDURE LIST |
17 --| PROCEDURE Add_Department_Records |
18 --| PROCEDURE Add_Employee_Records |
19 --| PROCEDURE Add_Employee_Category |
20 --| FUNCTION Get_Lookup_Meaning |
21 --| FUNCTION Get_Doc_Details |
22 --| FUNCTION Get_Parent_Org_ID |
23 --| FUNCTION Get_Cadre_Job_Details |
24 --| FUNCTION Get_Tech_Post_Details |
25 --| |
26 --| |
27 --| |
28 --| HISTORY |
29 --| 13-AUG-2010 Wuhua Liu Created |
30 --| 17-Sep-2010 Wuhua Liu bug#10120576 duplicated |
31 --| employee category records |
32 --+======================================================================*/
33 --==========================================================================
34 -- PROCEDURE NAME:
35 --
36 -- Get_Lookup_Meaning
37 --
38 -- DESCRIPTION:
39 --
40 -- This procedure is to Get the Meaning of Lookup Code in the HR
41 --
42 -- PARAMETERS:
43 -- In: pv_code NUMBER Lookup Code
44 -- pv_type NUMBER Lookup code type
45 --
46 --
47 -- DESIGN REFERENCES:
48 --
49 --
50 -- CHANGE HISTORY:
51 -- 16-Aug-2010 Wuhua Liu created
52 --==========================================================================
53 FUNCTION Get_Lookup_Meaning
54 ( pv_code IN VARCHAR2
55 , pv_type IN VARCHAR2
56 )
57 RETURN VARCHAR2
58 IS
59 CURSOR get_meaning
60 IS
61 SELECT
62 meaning
63 FROM
64 fnd_lookup_values
65 WHERE lookup_type = pv_type
66 AND enabled_flag = 'Y'
67 AND lookup_code = pv_code
68 AND language = userenv('lang')
69 ORDER BY lookup_code;
70 l_meaning fnd_lookup_values.meaning%type;
71 BEGIN
72 OPEN get_meaning;
73 FETCH
74 get_meaning
75 INTO
76 l_meaning;
77 CLOSE get_meaning;
78
79 IF (pv_type IN ('CN_MINISTRY_LABELS','CN_SOE_LABELS'))
80 THEN
81 l_meaning := REPLACE(l_meaning,' ','_');
82 END IF; --(pv_type IN ('CN_MINISTRY_LABELS','CN_SOE_LABELS'))
83 RETURN l_meaning;
84 END Get_Lookup_Meaning ;
85 --==========================================================================
86 -- PROCEDURE NAME:
87 --
88 -- Get_Doc_Detail
89 --
90 -- DESCRIPTION:
91 --
92 -- This procedure is to Get the document detail of the person.
93 --
94 -- PARAMETERS:
95 -- In: pn_person_id NUMBER Person Identifier
96 -- pd_date DATE The given date
97 -- pv_type VARCHAR2 document type ??
98 --
99 --
100 -- DESIGN REFERENCES:
101 --
102 --
103 -- CHANGE HISTORY:
104 -- 16-Aug-2010 Wuhua Liu created
105 --==========================================================================
106 Function Get_Doc_Details
107 ( pn_person_id IN NUMBER
108 , pd_date IN DATE
109 , pv_type IN VARCHAR2
110 )
111 RETURN VARCHAR2
112 IS
113 /* define all cursors and variables */
114 lv_val VARCHAR2(1);
115 lv_cin VARCHAR2(100);
116 ln_hk NUMBER;
117 ln_tw NUMBER;
118 lv_passport VARCHAR2(100);
119 lv_doc_type VARCHAR2(100);
120 lv_doc_num VARCHAR2(100);
121
122 CURSOR cur_check_person
123 ( pn_person_id IN NUMBER
124 , pd_date IN DATE
125 )
126 IS
127 SELECT
128 '1'
129 FROM
130 per_all_people_f
131 WHERE person_id = pn_person_id
132 AND pd_date BETWEEN effective_start_date AND effective_end_date;
133
134 CURSOR c_get_cit_ident_no
135 ( pn_person_id IN NUMBER
136 , pd_date IN DATE
137 )
138 IS
139 SELECT
140 national_identifier
141 FROM
142 per_all_people_f
143 WHERE person_id = pn_person_id
144 AND pd_date BETWEEN effective_start_date AND effective_end_date;
145
146 CURSOR cur_get_pass_no
147 ( pn_person_id IN NUMBER)
148 IS
149 SELECT
150 pei_information1 pei_info1
151 , pei_information2 pei_info2
152 FROM
153 per_people_extra_info
154 WHERE person_id = pn_person_id
155 AND information_type = 'PER_PASS_INFO_CN'
156 AND pei_information_category = 'PER_PASS_INFO_CN'
157 ORDER BY pei_info1;
158
159 CURSOR c_get_passport_no
160 ( pn_person_id IN NUMBER)
161 IS
162 SELECT
163 pei_information2
164 FROM
165 per_people_extra_info
166 WHERE person_id = pn_person_id
167 AND information_type = 'PER_PASSPORT_INFO_CN'
168 AND pei_information_category = 'PER_PASSPORT_INFO_CN';
169
170 BEGIN
171 ln_hk := 0;
172 ln_tw := 0;
173
174 /* No need to check if the person is effective on the date provided because
175 the input date is always the effective start date of the person.*/
176
177 OPEN c_get_cit_ident_no( pn_person_id
178 , pd_date);
179 FETCH
180 c_get_cit_ident_no
181 INTO
182 lv_cin;
183 /* At any point of time only 1 CIN is available to the person */
184 IF (lv_cin IS NULL)
185 THEN
186 CLOSE c_get_cit_ident_no;
187 /* Search for Pass Information. There can be multiple pass information */
188 /* First check for HongKong Pass Info and if not present check for Taiwanese Pass Info */
189 FOR itr IN cur_get_pass_no(pn_person_id)
190 LOOP
191 IF (itr.pei_info1 = 'PHM')
192 THEN
193 ln_hk := 1;
194 lv_doc_type := Get_Lookup_Meaning( 'PASS_HK_MACAO' , 'CN_AUDIT_DATA');
195 lv_doc_num := itr.pei_info2;
196 EXIT;
197 ELSIF (itr.pei_info1 = 'PTR')
198 THEN
199 ln_tw := 1;
200 lv_doc_type := Get_Lookup_Meaning( 'PASS_TAIWAN' , 'CN_AUDIT_DATA');
201 lv_doc_num := itr.pei_info2;
202 EXIT;
203 ELSE
204 NULL;
205 END IF; --(itr.A = 'PHM')
206 END LOOP; -- FOR itr IN c_get_pass_no(pn_person_id)
207 IF (ln_hk = 1 OR ln_tw = 1)
208 THEN
209 IF (pv_type='TYPE')
210 THEN
211 RETURN lv_doc_type;
212 ELSE
213 RETURN lv_doc_num;
214 END IF; --(pv_type = 'TYPE')
215 ELSE
216 /* Search for Passport Information. Assume only 1 passport information is available */
217 OPEN c_get_passport_no(pn_person_id);
218 FETCH c_get_passport_no INTO lv_passport;
219 IF (c_get_passport_no%notfound)
220 THEN
221 CLOSE c_get_passport_no;
222 lv_doc_type := NULL;
223 lv_doc_num := NULL;
224 RETURN NULL;
225 ELSE
226 CLOSE c_get_passport_no;
227 lv_doc_type := Get_Lookup_Meaning('PASSPORT','CN_AUDIT_DATA');
228 lv_doc_num := lv_passport;
229 IF (pv_type='TYPE')
230 THEN
231 RETURN lv_doc_type;
232 ELSE
233 RETURN lv_doc_num;
234 END IF; -- (pv_type ='TYPE')
235 END IF; -- (c_get_passport_no%notfound)
236 END IF; --(ln_hk = 1 OR ln_tw = 1)
237 ELSE
238 /* Citizen Identification Number found .*/
239 CLOSE c_get_cit_ident_no;
240 lv_doc_type := Get_Lookup_Meaning('CIN','CN_AUDIT_DATA');
241 lv_doc_num := lv_cin;
242 IF (pv_type='TYPE')
243 THEN
244 RETURN lv_doc_type;
245 ELSE
246 RETURN lv_doc_num;
247 END IF; --(pv_type='TYPE')
248 END IF; -- (lv_cin IS NULL)
249 RETURN NULL;
250 END Get_Doc_Details;
251
252 /* Function to get parent organization id */
253 --==========================================================================
254 -- PROCEDURE NAME:
255 --
256 -- Get_Parent_Org_ID
257 --
258 -- DESCRIPTION:
259 --
260 -- This procedure is to Get the parent organization id of the give org.
261 --
262 -- PARAMETERS:
263 -- In: pn_organization_id NUMBER Given Organization ID.
264 --
265 --
266 --
267 -- DESIGN REFERENCES:
268 --
269 --
270 -- CHANGE HISTORY:
271 -- 16-Aug-2010 Wuhua Liu created
272 --==========================================================================
273 FUNCTION Get_Parent_Org_ID
274 ( pn_organization_id IN NUMBER
275 )
276 RETURN NUMBER IS
277 CURSOR
278 c_parent_org_id
279 IS
280 SELECT
281 pose.organization_id_parent organization_id
282 FROM
283 per_org_structure_elements pose
284 , per_organization_structures pos
285 , per_org_structure_versions posv
286 WHERE posv.org_structure_version_id = pose.org_structure_version_id
287 AND pos.primary_structure_flag='Y'
288 AND pos.organization_structure_id = posv.organization_structure_id
289 AND pose.organization_id_child = pn_organization_id
290 AND EXISTS (SELECT 1
291 FROM hr_organization_information info
292 WHERE info.org_information1 = 'HR_ORG'
293 AND info.org_information_context = 'CLASS'
294 AND info.organization_id = pose.organization_id_parent
295 AND info.org_information2 = 'Y'
296 );
297 /* Need to understand Primary_Structure_Flag importance and what if the parent is a non-HR org */
298 /* Can we pass BG id from Fin Responsibility */
299 ln_parent_org_id NUMBER;
300 BEGIN
301 OPEN c_parent_org_id;
302 FETCH
303 c_parent_org_id
304 INTO ln_parent_org_id;
305 CLOSE c_parent_org_id;
306 RETURN ln_parent_org_id;
307 END Get_Parent_Org_ID;
308
309 --==========================================================================
310 -- PROCEDURE NAME:
311 --
312 -- Get_Cadre_Job_Detail
313 --
314 -- DESCRIPTION:
315 --
316 -- This procedure is to get the position level name of the person.
317 --
318 -- PARAMETERS:
319 -- In: pn_person_id NUMBER Person Identifier
320 -- pd_date DATE The given Date.
321 --
322 --
323 -- DESIGN REFERENCES:
324 --
325 --
326 -- CHANGE HISTORY:
327 -- 16-Aug-2010 Wuhua Liu created
328 --==========================================================================
329 Function Get_Cadre_Job_Details
330 ( pn_person_id IN NUMBER
331 , pd_date IN DATE
332 )
333 RETURN VARCHAR2
334 IS
335 CURSOR
336 get_anal_cri_id
337 IS
338 SELECT
339 analysis_criteria_id
340 FROM
341 per_person_analyses
342 WHERE person_id = pn_person_id
343 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
344 AND pd_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
345 AND id_flex_num = (SELECT id_flex_num
346 FROM fnd_id_flex_structures_vl
347 WHERE id_flex_code='PEA'
348 AND id_flex_structure_code = 'PER_JOB_CLASS_INFO_CN'
349 )
350 ORDER BY date_from DESC;
351
352 CURSOR get_details
353 ( p_anal_cri_id NUMBER
354 )
355 IS
356 SELECT
357 segment1 cad_job_class
358 FROM
359 per_analysis_criteria
360 WHERE analysis_criteria_id = p_anal_cri_id
361 AND enabled_flag = 'Y';
362
363 vl_cad_job_class VARCHAR2(1000);
364 ln_cri_id NUMBER;
365
366 BEGIN
367 OPEN get_anal_cri_id;
368 FETCH
369 get_anal_cri_id
370 INTO ln_cri_id;
371 CLOSE get_anal_cri_id;
372
373 IF (ln_cri_id IS NOT NULL)
374 THEN
375 OPEN get_details(ln_cri_id);
376 FETCH get_details INTO vl_cad_job_class;
377 CLOSE get_details;
378 ELSE
379 RETURN NULL;
380 END IF; --(ln_cri_id IS NOT NULL)
381 RETURN Get_Lookup_Meaning(vl_cad_job_class,'CN_CADRE_JOB_CLASS');
382 END Get_Cadre_Job_Details;
383 --==========================================================================
384 -- PROCEDURE NAME:
385 --
386 -- Get_Tech_Post_Details
387 --
388 -- DESCRIPTION:
389 --
390 -- This procedure is to Get the technical position of the person
391 --
392 -- PARAMETERS:
393 -- In: pn_person_id NUMBER Person Identifier
394 -- pd_date DATE The given Date
395 --
396 --
397 -- DESIGN REFERENCES:
398 --
399 --
400 -- CHANGE HISTORY:
401 -- 16-Aug-2010 Wuhua Liu created
402 --==========================================================================
403 FUNCTION Get_Tech_Post_Details
404 ( pn_person_id IN NUMBER
405 , pd_date IN DATE
406 )
407 RETURN VARCHAR2
408 IS
409 CURSOR get_anal_cri_id
410 IS
411 SELECT
412 analysis_criteria_id
413 FROM
414 per_person_analyses
415 WHERE person_id = pn_person_id
416 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
417 AND pd_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','dd-mm-yyyy'))
418 AND id_flex_num = (SELECT id_flex_num
419 FROM fnd_id_flex_structures_vl
420 WHERE id_flex_code='PEA'
421 AND id_flex_structure_code = 'PER_TECH_PROF_POST_CN'
422 )
423 ORDER BY date_from DESC;
424
425 CURSOR get_details( p_anal_cri_id IN NUMBER)
426 IS
427 SELECT
428 segment1 tech_post_detail
429 FROM
430 PER_ANALYSIS_CRITERIA
431 WHERE ANALYSIS_CRITERIA_ID = p_anal_cri_id
432 AND enabled_flag = 'Y';
433
434 ln_cri_id NUMBER;
435 lv_tech_post_detail VARCHAR2(1000);
436
437 BEGIN
438 OPEN get_anal_cri_id;
439 FETCH get_anal_cri_id INTO ln_cri_id;
440 CLOSE get_anal_cri_id;
441
442 IF(ln_cri_id IS NOT NULL)
443 THEN
444 OPEN get_details(ln_cri_id);
445 FETCH get_details INTO lv_tech_post_detail ;
446 CLOSE get_details;
447 ELSE
448 RETURN NULL;
449 END IF; --(ln_cri IS NOT NULL)
450 RETURN Get_Lookup_Meaning( lv_tech_post_detail
451 , 'CN_TECH_TITLE'
452 );
453 END Get_Tech_Post_Details;
454 --==========================================================================
455 -- PROCEDURE NAME:
456 --
457 -- Add_Department_Records Public
458 --
459 -- DESCRIPTION:
460 --
461 -- This procedure is to export department records
462 --
463 -- PARAMETERS:
464 -- In: pn_legal_entity_id NUMBER identifier of legal entity
465 -- pn_ledger_id NUMBER identifier of ledger
466 -- pv_accounting_year VARCHAR2 accounting year
467 --
468 -- DESIGN REFERENCES:
469 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
470 --
471 -- CHANGE HISTORY:
472 -- 13-AUG-2010 Wuhua Liu created
473 --==========================================================================
474 PROCEDURE Add_Department_Records
475 ( pn_legal_entity_id IN NUMBER
476 , pn_ledger_id IN NUMBER
477 , pv_accounting_year IN VARCHAR2
478 )
479 AS
480 lv_procedure_name VARCHAR2(40) := 'Add_Department_Records';
481 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
482 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
483 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
484 lv_error_msg VARCHAR2(2000) := NULL;
485 ln_rec_count NUMBER := 0;
486 ld_acc_start_date DATE := NULL;
487 ld_acc_end_date DATE := NULL;
488
489 -- Get accounting start date and end date
490 CURSOR acc_date_cur
491 IS
492 SELECT
493 MIN(gp.start_date)
494 , MAX(gp.end_date)
495 FROM
496 gl_periods gp
497 , gl_ledgers led
498 WHERE led.period_set_name = gp.period_set_name
499 AND led.accounted_period_type = gp.period_type
500 AND led.ledger_id = pn_ledger_id --parameter: pn_ledger_id
501 --parameter: pv_accounting_year
502 AND gp.period_year = TO_NUMBER(pv_accounting_year);
503
504 --Cursor for getting department records
505 CURSOR department_cur
506 IS
507 SELECT
508 orgtl.name department_name
509 , org.organization_id department_id
510 FROM
511 hr_organization_units org
512 , hr_all_organization_units_tl orgtl
513 WHERE orgtl.organization_id = org.organization_id
514 AND orgtl.LANGUAGE = userenv('LANG')
515 AND org.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
516 AND date_from <= ld_acc_end_date
517 AND nvl( date_to, ld_acc_end_date) >= ld_acc_start_date
518 AND EXISTS (SELECT 1
519 FROM hr_organization_information info
520 WHERE info.org_information1 = 'HR_ORG'
521 AND info.org_information_context = 'CLASS'
522 AND info.organization_id = org.organization_id
523 AND info.org_information2 = 'Y'
524 );
525
526 BEGIN
527 --logging for debug
528 IF (ln_proc_level >= ln_dbg_level)
529 THEN
530 FND_LOG.STRING( ln_proc_level
531 , GV_MODULE_PREFIX || '.' || lv_procedure_name
532 || '.begin'
533 , 'Enter procedure');
534 -- logging the parameters
535 FND_LOG.STRING( ln_proc_level
536 , lv_procedure_name || '.parameters'
537 , 'pn_legal_entity_id=' || pn_legal_entity_id || ','
538 || 'pn_ledger_id=' || pn_ledger_id || ','
539 || 'pv_accounting_year=' || pv_accounting_year);
540 END IF; --(ln_proc_level>=ln_dbg_level)
541 FND_FILE.put_line( FND_FILE.log
542 , lv_procedure_name || '.parameters:'
543 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
544 || 'pn_ledger_id=' || pn_ledger_id || ','
545 || 'pv_accounting_year=' || pv_accounting_year);
546
547 OPEN acc_date_cur;
548 FETCH
549 acc_date_cur
550 INTO
551 ld_acc_start_date
552 , ld_acc_end_date;
553 CLOSE acc_date_cur;
554 FND_FILE.put_line( FND_FILE.log
555 , lv_procedure_name || '.variables:'
556 || 'pd_acc_start_date=' || ld_acc_start_date || ','
557 || 'pd_acc_end_date=' || ld_acc_end_date);
558 FOR v_row IN department_cur
559 LOOP
560 Ja_Cn_Utility.Add_Sub_Root_Node( 'INNER_ORGANIZATION_RECORDS'
561 , Ja_Cn_Utility.GV_TAG_TYPE_START
562 );
563 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_ID'
564 , v_row.department_id
565 );
566 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_NAME'
567 , v_row.department_name
568 );
569 Ja_Cn_Utility.Add_Child_Node( 'PARENT_ORGANIZATION_ID'
570 , Get_Parent_Org_ID(v_row.department_id)
571 , Ja_Cn_Utility.GV_TYPE_VARCHAR2
572 , Ja_Cn_Utility.GV_REQUIRED_NO
573 );
574 Ja_Cn_Utility.Add_Sub_Root_Node( 'INNER_ORGANIZATION_RECORDS'
575 , Ja_Cn_Utility.GV_TAG_TYPE_END
576 );
577 ln_rec_count := ln_rec_count + 1;
578 END LOOP; -- FOR v_row IN department_cur
579
580 --logging the variables
581 IF (ln_statement_level >= ln_dbg_level)
582 THEN
583 FND_LOG.STRING( ln_statement_level
584 , lv_procedure_name
585 , 'ln_rec_count:' || ln_rec_count);
586 END IF; --(ln_statement_level >= ln_dbg_level)
587 FND_FILE.put_line( FND_FILE.log
588 , lv_procedure_name || '.variable:'
589 || 'ln_rec_count=' || ln_rec_count);
590 -- There is no data found for department record
591 IF (ln_rec_count = 0)
592 THEN
593 Ja_Cn_Utility.Print_No_Data_Found_For_Log( 'INNER_ORGANIZATION_RECORDS');
594 Ja_Cn_Utility.Add_Sub_Root_Node( 'INNER_ORGANIZATION_RECORDS'
595 , Ja_Cn_Utility.GV_TAG_TYPE_START
596 );
597 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_ID'
598 , NULL
599 );
600 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_NAME'
601 , NULL
602 );
603 Ja_Cn_Utility.Add_Child_Node( 'PARENT_ORGANIZATION_ID'
604 , NULL
605 , Ja_Cn_Utility.GV_TYPE_VARCHAR2
606 , Ja_Cn_Utility.GV_REQUIRED_NO
607 );
608 Ja_Cn_Utility.Add_Sub_Root_Node( 'INNER_ORGANIZATION_RECORDS'
609 , Ja_Cn_Utility.GV_TAG_TYPE_END
610 );
611 END IF; -- (ln_rec_count = 0)
612 --logging for debug
613 IF (ln_proc_level >= ln_dbg_level)
614 THEN
615 FND_LOG.STRING( ln_proc_level
616 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
617 , 'Exit procedure');
618 END IF; -- (ln_proc_level>=ln_dbg_level)
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
623 THEN
624 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
625 , GV_MODULE_PREFIX || '.' || lv_procedure_name
626 || '.Other_Exception '
627 , SQLCODE || SQLERRM);
628 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
629 FND_FILE.put_line( FND_FILE.log
630 , lv_procedure_name || SQLCODE || SQLERRM);
631 RAISE;
632 END Add_Department_Records;
633 --==========================================================================
634 -- PROCEDURE NAME:
635 --
636 -- Add_Employee_Records Public
637 --
638 -- DESCRIPTION:
639 --
640 -- This procedure is to export employee records
641 --
642 -- PARAMETERS:
643 -- In: pn_legal_entity_id NUMBER identifier of legal entity
644 -- pn_ledger_id NUMBER identifier of ledger
645 -- pv_accounting_year VARCHAR2 accounting year
646 --
647 -- DESIGN REFERENCES:
648 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
649 --
650 -- CHANGE HISTORY:
651 -- 13-Aug-2010 Wuhua Liu created
652 -- 25-Sep-2010 Wuhua Liu bug#10146613 Get shared Information values
653 -- exported properly.
654 --==========================================================================
655
656 PROCEDURE Add_Employee_Records
657 ( pn_legal_entity_id IN NUMBER
658 , pn_ledger_id IN NUMBER
659 , pv_accounting_year IN VARCHAR2
660 )
661 AS
662 lv_procedure_name VARCHAR2(40) := 'Add_Employee_Records';
663 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
664 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
665 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
666 lv_error_msg VARCHAR2(2000) := NULL;
667 pd_acc_start_date DATE := NULL;
668 pd_acc_end_date DATE := NULL;
669 ln_rec_count NUMBER := 0;
670
671 -- Get accounting start date and end date
672 CURSOR acc_date_cur IS
673 SELECT
674 MIN(gp.start_date)
675 , MAX(gp.end_date)
676 FROM
677 gl_periods gp
678 , gl_ledgers led
679 WHERE led.period_set_name = gp.period_set_name
680 AND led.accounted_period_type = gp.period_type
681 AND led.ledger_id = pn_ledger_id --parameter: pn_ledger_id
682 --parameter: pv_accounting_year
683 AND gp.period_year = TO_NUMBER(pv_accounting_year);
684 -- This will list all HR Organizations that can be
685 --viewed from current security profile
686 CURSOR employee_cur IS
687 SELECT
688 ppf.employee_number employee_id
689 , ppf.full_name employee_name
690 , ppf.person_id person_id
691 , ppf.effective_start_date effective_start_date
692 , ppf.sex gender
693 , ppf.per_information17 race -- is it flex field. need change or not.
694 , ppf.per_information6 education_level
695 , ppf.per_information20 degree
696 , TO_CHAR(ppf.date_of_birth ,'YYYYMMDD') date_of_birth
697 , paf.employee_category employee_category
698 , paf.organization_id department_id
699 , TO_CHAR(pos.date_start ,'YYYYMMDD') date_of_hire
700 , TO_CHAR(pos.actual_termination_date ,'YYYYMMDD') date_of_termination
701 FROM
702 per_people_f ppf
703 , per_assignments_f paf
704 , per_periods_of_service pos
705 WHERE ppf.business_group_id = paf.business_group_id
706 AND pos.period_of_service_id = paf.period_of_service_id
707 AND paf.person_id = ppf.person_id
708 AND (
709 pd_acc_end_date BETWEEN paf.effective_start_date AND
710 paf.effective_end_date
711 OR
712 ( pos.final_process_date BETWEEN pd_acc_start_date AND
713 pd_acc_end_date
714 AND
715 pos.final_process_date BETWEEN paf.effective_start_date AND
716 paf.effective_end_date)
717 )
718 AND (
719 (nvl( pos.final_process_date
720 , pd_acc_end_date) >= pd_acc_end_date
721 AND
722 pd_acc_end_date BETWEEN ppf.effective_start_date AND
723 ppf.effective_end_date)
724 OR
725 (pos.final_process_date BETWEEN pd_acc_start_date AND
726 pd_acc_end_date
727 AND
728 pos.final_process_date BETWEEN ppf.effective_start_date AND
729 ppf.effective_end_date)
730 AND pos.period_of_service_id =
731 ( SELECT MAX(ppos.period_of_service_id)
732 FROM per_periods_of_service ppos
733 WHERE ppos.person_id = ppf.person_id)
734 )
735
736 AND ppf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
737 AND paf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
738
739 BEGIN
740 --logging for debug
741 IF (ln_proc_level >= ln_dbg_level)
742 THEN
743 FND_LOG.STRING( ln_proc_level
744 , GV_MODULE_PREFIX || '.' || lv_procedure_name
745 || '.begin'
746 , 'Enter procedure');
747 -- logging the parameters
748 FND_LOG.STRING( ln_proc_level
749 , lv_procedure_name || '.parameters'
750 , 'pn_legal_entity_id=' || pn_legal_entity_id || ','
751 || 'pn_ledger_id=' || pn_ledger_id || ','
752 || 'pv_accounting_year=' || pv_accounting_year);
753 END IF; --ln_proc_level>=ln_dbg_level
754 FND_FILE.put_line( FND_FILE.log
755 , lv_procedure_name || '.parameters:'
756 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
757 || 'pn_ledger_id=' || pn_ledger_id || ','
758 || 'pv_accounting_year=' || pv_accounting_year);
759
760 OPEN acc_date_cur;
761 FETCH
762 acc_date_cur
763 INTO
764 pd_acc_start_date
765 , pd_acc_end_date;
766 CLOSE acc_date_cur;
767 FND_FILE.put_line( FND_FILE.log
768 , lv_procedure_name || '.variables:'
769 || 'pd_acc_start_date='||pd_acc_start_date || ','
770 || 'pd_acc_end_date='||pd_acc_end_date);
771 FOR v_row IN employee_cur
772 LOOP
773 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_RECORDS'
774 , Ja_Cn_Utility.GV_TAG_TYPE_START
775 );
776 Ja_Cn_Utility.Add_Child_Node( 'STAFF_ID'
777 , v_row.employee_id
778 );
779 Ja_Cn_Utility.Add_Child_Node( 'STAFF_NAME'
780 , v_row.employee_name
781 );
782 Ja_Cn_Utility.
783 Add_Child_Node( 'DOCUMENT_TYPE'
784 , Get_Doc_Details( v_row.person_id
785 , v_row.effective_start_date
786 , 'TYPE')
787 );
788 Ja_Cn_Utility.
789 Add_Child_Node( 'DOCUMENT_NUMBER'
790 , Get_Doc_Details( v_row.person_id
791 , v_row.effective_start_date
792 , 'NAME')
793 );
794 Ja_Cn_Utility.
795 Add_Child_Node( 'GENDER'
796 , Get_Lookup_Meaning( v_row.gender
797 , 'SEX')
798 );
799 Ja_Cn_Utility.Add_Child_Node( 'STAFF_RACE'
800 , Get_Lookup_Meaning( v_row.race
801 , 'CN_RACE')
802 );
803 Ja_Cn_Utility.Add_Fixed_Child_Node( 'DATE_OF_BIRTH'
804 , v_row.date_of_birth
805 , 8
806 );
807 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_CODE'
808 , v_row.employee_category
809 );
810 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_ID'
811 , v_row.department_id
812 );
813 Ja_Cn_Utility.
814 Add_Child_Node( 'STAFF_POSITION_LEVEL'
815 , Get_Cadre_Job_Details( v_row.person_id
816 --v_row.effective_start_date
817 --bug#10146613
818 , pd_acc_end_date
819 )
820 );
821 Ja_Cn_Utility.
822 Add_Child_Node( 'STAFF_TECHNICAL_POSITION'
823 , Get_Tech_Post_Details( v_row.person_id
824 --v_row.effective_start_date
825 --bug#10146613
826 , pd_acc_end_date
827 )
828 );
829 Ja_Cn_Utility.Add_Child_Node( 'STAFF_HIGH_EDU_LEVEL'
830 , Get_Lookup_Meaning( v_row.education_level
831 , 'CN_HIGH_EDU_LEVEL')
832 );
833 Ja_Cn_Utility.Add_Child_Node( 'STAFF_DEGREE'
834 , Get_Lookup_Meaning( v_row.degree
835 , 'CN_DEGREE')
836 );
837 Ja_Cn_Utility.Add_Fixed_Child_Node( 'HIRE_DATE'
838 , v_row.date_of_hire
839 , 8
840 );
841 Ja_Cn_Utility.Add_Fixed_Child_Node( 'TERMINATION_DATE'
842 , v_row.date_of_termination
843 , 8
844 , Ja_Cn_Utility.GV_REQUIRED_NO
845 );
846 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_RECORDS'
847 , Ja_Cn_Utility.GV_TAG_TYPE_END
848 );
849 ln_rec_count := ln_rec_count + 1;
850
851 END LOOP; --(v_row IN employee_cur)
852
853 --logging the variables
854 IF (ln_statement_level >= ln_dbg_level)
855 THEN
856 FND_LOG.STRING( ln_statement_level
857 , lv_procedure_name
858 , 'ln_rec_count:' || ln_rec_count);
859 END IF; --(ln_statement_level >= ln_dbg_level)
860 FND_FILE.put_line( FND_FILE.log
861 , lv_procedure_name || '.variable:'
862 || 'ln_rec_count=' || ln_rec_count);
863
864 -- There is no data found for employee record
865 IF ln_rec_count = 0
866 THEN
867 --Begin: Added for bug# 9722570
868 Ja_Cn_Utility.Print_No_Data_Found_For_Log( 'STAFF_RECORDS');
869 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_RECORDS'
870 , Ja_Cn_Utility.GV_TAG_TYPE_START
871 );
872 Ja_Cn_Utility.Add_Child_Node( 'STAFF_ID'
873 , NULL
874 );
875 Ja_Cn_Utility.Add_Child_Node( 'STAFF_NAME'
876 , NULL
877 );
878 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE'
879 , NULL
880 );
881 Ja_Cn_Utility.Add_Child_Node( 'DOCUMENT_NUMBER'
882 , NULL
883 );
884 Ja_Cn_Utility.Add_Child_Node( 'GENDER'
885 , NULL
886 );
887 Ja_Cn_Utility.Add_Fixed_Child_Node( 'DATE_OF_BIRTH'
888 , NULL
889 , 8
890 );
891 Ja_Cn_Utility.Add_Child_Node( 'INNER_ORGANIZATION_ID'
892 , NULL
893 );
894 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_CODE'
895 , NULL);
896 Ja_Cn_Utility.Add_Child_Node( 'STAFF_POSITION_LEVEL'
897 , NULL
898 );
899 Ja_Cn_Utility.Add_Child_Node( 'STAFF_TECHNICAL_POSITION'
900 , NULL
901 );
902 Ja_Cn_Utility.Add_Child_Node( 'STAFF_HIGH_EDU_LEVEL'
903 , NULL
904 );
905 Ja_Cn_Utility.Add_Child_Node( 'STAFF_DEGREE'
906 , NULL);
907 Ja_Cn_Utility.Add_Fixed_Child_Node( 'HIRE_DATE'
908 , NULL
909 , 8
910 );
911 Ja_Cn_Utility.Add_Fixed_Child_Node( 'TERMINATION_DATE'
912 , NULL
913 , 8
914 , Ja_Cn_Utility.GV_REQUIRED_NO
915 );
916 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_RECORDS'
917 , Ja_Cn_Utility.GV_TAG_TYPE_END
918 );
919 --RAISE NO_DATA;
920 --End: Added for bug# 9722570
921 END IF; --ln_rec_count = 0
922
923 --logging for debug
924 IF (ln_proc_level >= ln_dbg_level)
925 THEN
926 FND_LOG.STRING( ln_proc_level
927 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
928 , 'Exit procedure');
929 END IF; -- (ln_proc_level>=ln_dbg_level)
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
934 THEN
935 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
936 , GV_MODULE_PREFIX || '.' || lv_procedure_name
937 || '.Other_Exception '
938 , SQLCODE || SQLERRM);
939 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
940 FND_FILE.put_line( FND_FILE.log
941 , lv_procedure_name || SQLCODE || SQLERRM);
942 RAISE;
943
944 END Add_Employee_Records;
945 --==========================================================================
946 -- PROCEDURE NAME:
947 --
948 -- Add_Employee_Category Public
949 --
950 -- DESCRIPTION:
951 --
952 -- This procedure is to export employee Category
953 --
954 -- PARAMETERS:
955 -- In: pn_legal_entity_id NUMBER identifier of legal entity
956 -- pn_ledger_id NUMBER identifier of ledger
957 -- pv_accounting_year VARCHAR2 accounting year
958 --
959 -- DESIGN REFERENCES:
960 -- TDD_1213_FIN_GL_P_CNAOV2_SI.doc
961 --
962 -- CHANGE HISTORY:
963 -- 13-Aug-2010 Wuhua Liu created
964 -- 17-Sep-2010 Wuhua Liu bug#10120576 duplicated employee
965 -- category records
966 --==========================================================================
967 PROCEDURE Add_Employee_Category
968 ( pn_legal_entity_id IN NUMBER
969 , pn_ledger_id IN NUMBER
970 , pv_accounting_year IN VARCHAR2
971 )
972 IS
973 lv_procedure_name VARCHAR2(30) := 'Add_Employee_Category';
974 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
975 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
976 ln_row_count NUMBER := 0;
977 ld_acc_start_date DATE;
978 ld_acc_end_date DATE;
979
980 CURSOR cur_employee_category
981 IS
982 SELECT
983 --bug#10120576 added the DISTINCT
984 DISTINCT paf.employee_category employee_category
985 FROM
986 per_assignments_f paf
987 WHERE paf.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
988 AND ld_acc_end_date
989 BETWEEN paf.effective_start_date AND paf.effective_end_date
990 AND EXISTS (SELECT
991 1
992 FROM
993 hr_organization_units hou
994 , hr_soft_coding_keyflex scl
995 WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
996 AND scl.segment1 = hou.organization_id
997 AND paf.business_group_id = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
998 )
999 AND paf.employee_category IS NOT NULL;
1000 CURSOR acc_date_cur IS
1001 SELECT
1002 MIN(gp.start_date)
1003 , MAX(gp.end_date)
1004 FROM
1005 gl_periods gp
1006 , gl_ledgers led
1007 WHERE led.period_set_name = gp.period_set_name
1008 AND led.accounted_period_type = gp.period_type
1009 AND led.ledger_id = pn_ledger_id
1010 AND gp.period_year = TO_NUMBER(pv_accounting_year);
1011 BEGIN
1012 --logging for debug
1013 IF (ln_proc_level >= ln_dbg_level)
1014 THEN
1015 FND_LOG.STRING( ln_proc_level
1016 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.begin'
1017 , 'Enter procedure');
1018 FND_LOG.STRING( ln_proc_level
1019 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.parameters'
1020 , 'pn_ledger_id=' || pn_ledger_id || ','
1021 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
1022 || 'pv_accounting_year=' || pv_accounting_year);
1023 END IF ;-- (ln_proc_level >= ln_dbg_level)
1024 FND_FILE.put_line( FND_FILE.log
1025 , lv_procedure_name || '.parameters:'
1026 || 'pn_ledger_id=' || pn_ledger_id || ','
1027 || 'pn_legal_entity_id=' || pn_legal_entity_id || ','
1028 || 'pv_accounting_year=' || pv_accounting_year);
1029 OPEN acc_date_cur;
1030 FETCH
1031 acc_date_cur
1032 INTO
1033 ld_acc_start_date
1034 , ld_acc_end_date;
1035 CLOSE acc_date_cur;
1036
1037 FOR v_row IN cur_employee_category
1038 LOOP
1039 ln_row_count := ln_row_count + 1;
1040 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_CATEGORY'
1041 , Ja_Cn_Utility.GV_TAG_TYPE_START
1042 );
1043 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_CODE'
1044 , v_row.employee_category
1045 );
1046 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_NAME'
1047 , Get_Lookup_Meaning( v_row.employee_category
1048 , 'EMPLOYEE_CATG')
1049 );
1050 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_CATEGORY'
1051 , Ja_Cn_Utility.GV_TAG_TYPE_END
1052 );
1053 END LOOP; -- FOR v_row IN cur_employee_category
1054
1055 IF(ln_row_count = 0)
1056 THEN
1057 Ja_Cn_Utility.Print_No_Data_Found_For_Log( 'STAFF_CATEGORY'
1058 );
1059 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_CATEGORY'
1060 , Ja_Cn_Utility.GV_TAG_TYPE_START
1061 );
1062 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_CODE'
1063 , NULL
1064 );
1065 Ja_Cn_Utility.Add_Child_Node( 'STAFF_CATEGORY_NAME'
1066 , NULL
1067 );
1068 Ja_Cn_Utility.Add_Sub_Root_Node( 'STAFF_CATEGORY'
1069 , Ja_Cn_Utility.GV_TAG_TYPE_END
1070 );
1071 END IF; -- (ln_row_count=0)
1072 --logging for debug
1073 IF (ln_proc_level >= ln_dbg_level)
1074 THEN
1075 FND_LOG.STRING( ln_proc_level
1076 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end'
1077 , 'Exit procedure');
1078 END IF; -- (ln_proc_level>=ln_dbg_level)
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1082 THEN
1083 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1084 , GV_MODULE_PREFIX || '.' || lv_procedure_name
1085 || '.Other_Exception '
1086 , SQLCODE || SQLERRM);
1087 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1088 FND_FILE.put_line( FND_FILE.log
1089 , lv_procedure_name || SQLCODE || SQLERRM );
1090 RAISE;
1091 END Add_Employee_Category;
1092
1093 END JA_CN_PS_HRMS_EXPORT_PKG;