[Home] [Help]
PACKAGE BODY: APPS.JA_CN_HRMS_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_HRMS_EXPORT_PKG AS
2 --$Header: JACNHREB.pls 120.6 2010/09/14 01:59:15 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNHREB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export department records and employee records | |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Department_Records |
16 --| PROCEDURE Add_Employee_Records |
17 --| FUNCTION Get_Lookup_Meaning |
18 --| FUNCTION CN_Get_Doc_Details |
19 --| FUNCTION Get_Parent_Org_ID |
20 --|
21 --| |
22 --| |
23 --| HISTORY |
24 --| 16-Mar-2010 Chaoqun Wu Created |
25 --| 18-May-2010 Chaoqun Wu Bug# 9722570, new way to handle|
26 --| no data found case |
27 --| 24-May-2010 Chaoqun Wu Bug# 9742065, incorrect |
28 --| document type. |
29 --| 01-SEP-2010 Wuhua Liu Bug# 10066598, handle employee |
30 --| temination and rehire in |
31 --| employee records |
32 --+======================================================================*/
33
34 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_HRMS_EXPORT_PKG';
35
36 FUNCTION Get_Lookup_Meaning
37 (p_code IN VARCHAR2
38 ,p_type IN VARCHAR2)
39 RETURN VARCHAR2
40 IS
41
42 CURSOR get_meaning
43 IS
44 SELECT meaning
45 FROM fnd_lookup_values
46 WHERE lookup_type = p_type
47 AND enabled_flag = 'Y'
48 AND lookup_code = p_code
49 AND LANGUAGE = userenv('lang')
50 ORDER BY lookup_code;
51
52 l_meaning fnd_lookup_values.meaning%type;
53
54 BEGIN
55
56 OPEN get_meaning;
57 FETCH get_meaning INTO l_meaning;
58 CLOSE get_meaning;
59
60 RETURN l_meaning;
61
62 END Get_Lookup_Meaning ;
63
64 FUNCTION CN_Get_Doc_Details
65 (p_person_id IN NUMBER
66 ,p_date IN DATE
67 ,p_type IN VARCHAR2
68 )
69 RETURN VARCHAR2
70 IS
71 /* define all cursors and variables */
72 l_val VARCHAR2(1);
73 l_cin VARCHAR2(100);
74 l_hk NUMBER;
75 l_tw NUMBER;
76 l_passport VARCHAR2(100);
77 l_doc_type VARCHAR2(100);
78 l_doc_num VARCHAR2(100);
79
80 CURSOR c_check_person(p_person_id IN NUMBER,p_date IN DATE)
81 IS
82 SELECT '1'
83 FROM PER_ALL_PEOPLE_F
84 WHERE PERSON_ID = p_person_id
85 AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
86
87 CURSOR c_get_cit_ident_no(p_person_id IN NUMBER,p_date IN DATE)
88 IS
89 SELECT NATIONAL_IDENTIFIER
90 FROM PER_ALL_PEOPLE_F
91 WHERE PERSON_ID = p_person_id
92 AND p_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
93
94 CURSOR c_get_pass_no(p_person_id IN NUMBER)
95 IS
96 SELECT PEI_INFORMATION1 A, PEI_INFORMATION2 B
97 FROM PER_PEOPLE_EXTRA_INFO
98 WHERE PERSON_ID = p_person_id
99 AND INFORMATION_TYPE = 'PER_PASS_INFO_CN'
100 AND PEI_INFORMATION_CATEGORY = 'PER_PASS_INFO_CN'
101 ORDER BY A;
102
103 CURSOR c_get_passport_no(p_person_id IN NUMBER)
104 IS
105 SELECT PEI_INFORMATION2
106 FROM PER_PEOPLE_EXTRA_INFO
107 WHERE PERSON_ID = p_person_id
108 AND INFORMATION_TYPE = 'PER_PASSPORT_INFO_CN'
109 AND PEI_INFORMATION_CATEGORY = 'PER_PASSPORT_INFO_CN';
110
111 BEGIN
112
113 l_hk := 0;
114 l_tw := 0;
115
116 /* No need to check if the person is effective on the date provided because
117 the input date is always the effective start date of the person.*/
118
119 OPEN c_get_cit_ident_no(p_person_id ,p_date);
120 FETCH c_get_cit_ident_no INTO l_cin;
121 /* At any point of time only 1 CIN is available to the person */
122 IF l_cin IS NULL --c_get_cit_ident_no%notfound --Fixing bug 9742065
123 THEN
124 CLOSE c_get_cit_ident_no;
125 /* Search for Pass Information. There can be multiple pass information */
126 /* First check for HongKong Pass Info and if not present check for Taiwanese Pass Info */
127 FOR itr in c_get_pass_no(p_person_id)
128 LOOP
129 IF itr.A = 'PHM'
130 THEN
131 l_hk := 1;
132 l_doc_type := Get_Lookup_Meaning('PASS_HK_MACAO', 'CN_AUDIT_DATA');
133 l_doc_num := itr.B;
134 EXIT;
135 ELSIF itr.A = 'PTR'
136 THEN
137 l_tw := 1;
138 l_doc_type := Get_Lookup_Meaning('PASS_TAIWAN', 'CN_AUDIT_DATA');
139 l_doc_num := itr.B;
140 EXIT;
141 ELSE
142 NULL;
143 END IF;
144 END LOOP;
145 IF l_hk = 1 OR l_tw = 1
146 THEN
147 IF p_type='TYPE'
148 THEN
149 RETURN l_doc_type;
150 ELSE
151 RETURN l_doc_num;
152 END IF;
153
154 ELSE
155 /* Search for Passport Information. Assume only 1 passport information is available */
156 OPEN c_get_passport_no(p_person_id);
157 FETCH c_get_passport_no INTO l_passport;
158 IF l_passport IS NULL THEN --Updated for fixing bug# 9742065
159 CLOSE c_get_passport_no;
160 l_doc_type := NULL;
161 l_doc_num := NULL;
162 return NULL;
163 ELSE
164 CLOSE c_get_passport_no;
165 l_doc_type := get_lookup_meaning('PASSPORT','CN_AUDIT_DATA');
166 l_doc_num := l_passport;
167 IF p_type='TYPE' then
168 RETURN l_doc_type;
169 ELSE
170 RETURN l_doc_num;
171 END IF;
172 END IF;
173 END IF;
174 ELSE
175 /* Citizen Identification Number found .*/
176 CLOSE c_get_cit_ident_no;
177 l_doc_type := Get_Lookup_Meaning('CIN', 'CN_AUDIT_DATA');
178 l_doc_num := l_cin;
179 IF p_type='TYPE' then
180 RETURN l_doc_type;
181 ELSE
182 RETURN l_doc_num;
183 END IF;
184 END IF;
185
186 RETURN NULL;
187 END CN_Get_Doc_Details;
188
189 /* Function to get parent organization id */
190 FUNCTION Get_Parent_Org_ID
191 (p_organization_id IN NUMBER)
192 RETURN NUMBER
193 IS
194 l_parent_org_id NUMBER;
195
196 CURSOR c_parent_org_id
197 IS
198 SELECT POSE.organization_id_parent organization_id
199 FROM per_org_structure_elements POSE
200 ,per_organization_structures POS
201 ,per_org_structure_versions POSV
202 WHERE POSV.org_structure_version_id = POSE.org_structure_version_id
203 AND POS.primary_structure_flag='Y'
204 AND POS.organization_structure_id = POSV.organization_structure_id
205 AND POSE.organization_id_child = p_organization_id
206 AND EXISTS (SELECT 1
207 FROM hr_organization_information info
208 WHERE info.org_information1 = 'HR_ORG'
209 AND info.org_information_context = 'CLASS'
210 AND info.organization_id = POSE.organization_id_parent
211 AND info.org_information2 = 'Y');
212 /* Need to understand Primary_Structure_Flag importance and what if the parent is a non-HR org */
213 /* Can we pass BG id from Fin Responsibility */
214
215 BEGIN
216
217 OPEN c_parent_org_id;
218 FETCH c_parent_org_id INTO l_parent_org_id;
219 CLOSE c_parent_org_id;
220
221 RETURN l_parent_org_id;
222 END Get_Parent_Org_ID;
223
224 --==========================================================================
225 -- PROCEDURE NAME:
226 --
227 -- Add_Department_Records Public
228 --
229 -- DESCRIPTION:
230 --
231 -- This procedure is to export department records
232 --
233 -- PARAMETERS:
234 -- In: pn_legal_entity_id NUMBER identifier of legal entity
235 -- pn_ledger_id NUMBER identifier of ledger
236 -- pv_accounting_year VARCHAR2 accounting year
237 --
238 -- DESIGN REFERENCES:
239 -- GL_Chaoqun.doc
240 --
241 -- CHANGE HISTORY:
242 -- 16-Mar-2010 Chaoqun Wu created
243 -- 18-May-2010 Chaoqun Wu Bug# 9722570, new way to handle no data found case
244 --==========================================================================
245
246 PROCEDURE Add_Department_Records
247 (pn_legal_entity_id IN NUMBER
248 ,pn_ledger_id IN NUMBER
249 ,pv_accounting_year IN VARCHAR2
250 )
251 IS
252 lv_procedure_name VARCHAR2(40) := 'Add_Department_Records';
253 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
254 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
255 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
256 lv_error_msg VARCHAR2(2000) := NULL;
257 ln_rec_count NUMBER := 0;
258 pd_acc_start_date DATE := NULL;
259 pd_acc_end_date DATE := NULL;
260
261 -- Get accounting start date and end date based on accounting year and ledger id
262 --
263 CURSOR acc_date_cur
264 IS
265 SELECT MIN(GP.START_DATE),
266 MAX(GP.END_DATE)
267 FROM GL_PERIODS GP,
268 GL_LEDGERS LED
269 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
270 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
271 AND LED.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
272 AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year); --parameter: pv_accounting_year
273
274 --Cursor for getting department records
275 --
276 CURSOR department_cur
277 IS
278 SELECT orgtl.NAME DEPARTMENT_NAME,
279 org.organization_id DEPARMENT_ID,
280 NULL PARENT_DEPARTMENT_ID
281 FROM hr_organization_units org, hr_all_organization_units_tl orgtl
282 WHERE orgtl.organization_id = org.organization_id
283 AND orgtl.LANGUAGE = userenv('LANG')
284 AND org.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
285 AND date_from <= pd_acc_end_date
286 AND nvl(date_to,
287 pd_acc_end_date) >= pd_acc_start_date
288 AND EXISTS (SELECT 1
289 FROM hr_organization_information info
290 WHERE info.org_information1 = 'HR_ORG'
291 AND info.org_information_context = 'CLASS'
292 AND info.organization_id = org.organization_id
293 AND info.org_information2 = 'Y');
294
295 BEGIN
296 --logging for debug
297 IF (ln_proc_level >= ln_dbg_level)
298 THEN
299 FND_LOG.STRING(ln_proc_level,
300 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
301 '.begin',
302 'Enter procedure');
303 -- logging the parameters
304 FND_LOG.STRING(ln_proc_level,
305 lv_procedure_name ||
306 '.parameters',
307 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
308 'pn_ledger_id=' || pn_ledger_id || ',' ||
309 'pv_accounting_year=' || pv_accounting_year);
310 END IF; --ln_proc_level>=ln_dbg_level
311 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
312 '.parameters:' ||
313 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
314 'pn_ledger_id=' || pn_ledger_id || ',' ||
315 'pv_accounting_year=' || pv_accounting_year);
316
317 OPEN acc_date_cur;
318 FETCH acc_date_cur INTO pd_acc_start_date, pd_acc_end_date;
319 CLOSE acc_date_cur;
320
321 FOR v_row IN department_cur
322 LOOP
323 Ja_Cn_Utility.Add_Sub_Root_Node('DEPARTMENT_RECORDS'
324 ,Ja_Cn_Utility.GV_TAG_TYPE_START
325 );
326 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_ID'
327 ,v_row.DEPARMENT_ID
328 );
329 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_NAME'
330 ,v_row.DEPARTMENT_NAME
331 );
332 Ja_Cn_Utility.Add_Child_Node('PARENT_DEPARTMENT_ID'
333 ,Get_Parent_Org_ID(v_row.DEPARMENT_ID)
334 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
335 ,Ja_Cn_Utility.GV_REQUIRED_NO
336 );
337 Ja_Cn_Utility.Add_Sub_Root_Node('DEPARTMENT_RECORDS'
338 ,Ja_Cn_Utility.GV_TAG_TYPE_END
339 );
340 ln_rec_count := ln_rec_count + 1;
341 END LOOP;
342
343 --logging the variables
344 IF (ln_statement_level >= ln_dbg_level)
345 THEN
346 FND_LOG.STRING(ln_statement_level,
347 lv_procedure_name,
348 'ln_rec_count:' || ln_rec_count);
349 END IF; --(ln_statement_level >= ln_dbg_level)
350 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
351 '.variable:' ||
352 'ln_rec_count=' || ln_rec_count);
353
354 -- There is no data found for department record
355 IF ln_rec_count = 0
356 THEN
357 --Begin: Added for bug# 9722570
358 Ja_Cn_Utility.Print_No_Data_Found_For_Log('DEPARTMENT_RECORDS');
359 Ja_Cn_Utility.Add_Sub_Root_Node('DEPARTMENT_RECORDS'
360 ,Ja_Cn_Utility.GV_TAG_TYPE_START
361 );
362 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_ID'
363 ,NULL
364 );
365 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_NAME'
366 ,NULL
367 );
368 Ja_Cn_Utility.Add_Child_Node('PARENT_DEPARTMENT_ID'
369 ,NULL
370 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
371 ,Ja_Cn_Utility.GV_REQUIRED_NO
372 );
373 Ja_Cn_Utility.Add_Sub_Root_Node('DEPARTMENT_RECORDS'
374 ,Ja_Cn_Utility.GV_TAG_TYPE_END
375 );
376 --RAISE NO_DATA;
377 --End: Added for bug# 9722570
378 END IF;
379
380 --logging for debug
381
382 IF (ln_proc_level >= ln_dbg_level)
383 THEN
384 FND_LOG.STRING(ln_proc_level,
385 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
386 'Exit procedure');
387 END IF; -- (ln_proc_level>=ln_dbg_level)
388
389 EXCEPTION
390 --Begin: Deleted for bug# 9722570
391 /* WHEN NO_DATA THEN
392 --logging for debug
393 IF (ln_proc_level >= ln_dbg_level)
394 THEN
395 FND_LOG.STRING(ln_proc_level,
396 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
397 'exception',
398 'No data for Department Records');
399 END IF; --ln_proc_level>=ln_dbg_level
400 Ja_Cn_Utility.Print_No_Data_Found_For_Log('DEPARTMENT_RECORDS');
401 RAISE;*/
402 --End: Deleted for bug# 9722570
403 WHEN OTHERS THEN
404 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
405 THEN
406 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
407 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
408 '.Other_Exception ',
409 SQLCODE || SQLERRM);
410 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
411 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
412 RAISE;
413
414 END Add_Department_Records;
415
416 --==========================================================================
417 -- PROCEDURE NAME:
418 --
419 -- Add_Employee_Records Public
420 --
421 -- DESCRIPTION:
422 --
423 -- This procedure is to export employee records
424 --
425 -- PARAMETERS:
426 -- In: pn_legal_entity_id NUMBER identifier of legal entity
427 -- pn_ledger_id NUMBER identifier of ledger
428 -- pv_accounting_year VARCHAR2 accounting year
429 --
430 -- DESIGN REFERENCES:
431 -- GL_Chaoqun.doc
432 --
433 -- CHANGE HISTORY:
434 -- 02-Mar-2010 Chaoqun Wu created
435 -- 18-May-2010 Chaoqun Wu Bug# 9722570, new way to handle no data found case
436 -- 24-May-2010 Chaoqun Wu Bug# 9742065, incorrect document type
437 -- 01-Sep-2010 Wuhua Liu Bug# 10066598, employee temination and re-hire
438 --==========================================================================
439
440 PROCEDURE Add_Employee_Records
441 (pn_legal_entity_id IN NUMBER
442 ,pn_ledger_id IN NUMBER
443 ,pv_accounting_year IN VARCHAR2
444 )
445 IS
446 lv_procedure_name VARCHAR2(40) := 'Add_Employee_Records';
447 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
448 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
449 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
450 lv_error_msg VARCHAR2(2000) := NULL;
451 pd_acc_start_date DATE := NULL;
452 pd_acc_end_date DATE := NULL;
453 ln_rec_count NUMBER := 0;
454
455 -- Get accounting start date and end date based on accounting year and ledger id
456 --
457 CURSOR acc_date_cur
458 IS
459 SELECT MIN(GP.START_DATE),
460 MAX(GP.END_DATE)
461 FROM GL_PERIODS GP,
462 GL_LEDGERS LED
463 WHERE LED.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
464 AND LED.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
465 AND LED.LEDGER_ID = pn_ledger_id --parameter: pn_ledger_id
466 AND GP.PERIOD_YEAR = TO_NUMBER(pv_accounting_year); --parameter: pv_accounting_year
467
468 -- Get Shared Information - Employee Records for SOE
469 -- This will list all HR Organizations that can be viewed from current security profile
470 --
471 CURSOR employee_cur
472 IS
473 SELECT ppf.employee_number EMPLOYEE_ID,
474 ppf.full_name EMPLOYEE_NAME,
475 ppf.person_id PERSON_ID,
476 --paf.effective_start_date EFFECTIVE_START_DATE, --update for bug 9742065
477 ppf.effective_start_date EFFECTIVE_START_DATE,
478 NULL DOCUMENT_TYPE,
479 NULL DOCUMENT_NUMBER,
480 ppf.sex GENDER,
481 to_char(ppf.date_of_birth,
482 'YYYYMMDD') DATE_OF_BIRTH,
483 paf.organization_id DEPARTMENT_ID,
484 to_char(pos.date_start,
485 'YYYYMMDD') HIRE_DATE,
486 to_char(pos.actual_termination_date,
487 'YYYYMMDD') TERMINATION_DATE
488 FROM per_people_f ppf,
489 per_assignments_f paf,
490 per_periods_of_service pos
491 WHERE ppf.business_group_id = paf.business_group_id
492 AND pos.period_of_service_id = paf.period_of_service_id
493 AND paf.person_id = ppf.person_id
494 AND (pd_acc_end_date BETWEEN paf.effective_start_date AND
495 paf.effective_end_date OR
496 (pos.final_process_date BETWEEN pd_acc_start_date AND
497 pd_acc_end_date AND
498 pos.final_process_date BETWEEN paf.effective_start_date AND
499 paf.effective_end_date))
500 AND (
501 (nvl(pos.final_process_date,
502 pd_acc_end_date) >= pd_acc_end_date AND
503 pd_acc_end_date BETWEEN ppf.effective_start_date AND
504 ppf.effective_end_date) OR
505 (pos.final_process_date BETWEEN pd_acc_start_date AND
506 pd_acc_end_date AND
507 pos.final_process_date BETWEEN ppf.effective_start_date AND
508 ppf.effective_end_date)
509 --bug# 10066598, employment temination and rehire
510 AND pos.period_of_service_id =
511 ( SELECT MAX(ppos.period_of_service_id)
512 FROM per_periods_of_service ppos
513 WHERE ppos.person_id = ppf.person_id)
514 )
515 AND ppf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
516 AND paf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
517
518 BEGIN
519 --logging for debug
520 IF (ln_proc_level >= ln_dbg_level)
521 THEN
522 FND_LOG.STRING(ln_proc_level,
523 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
524 '.begin',
525 'Enter procedure');
526 -- logging the parameters
527 FND_LOG.STRING(ln_proc_level,
528 lv_procedure_name ||
529 '.parameters',
530 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
531 'pn_ledger_id=' || pn_ledger_id || ',' ||
532 'pv_accounting_year=' || pv_accounting_year);
533 END IF; --ln_proc_level>=ln_dbg_level
534 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
535 '.parameters:' ||
536 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
537 'pn_ledger_id=' || pn_ledger_id || ',' ||
538 'pv_accounting_year=' || pv_accounting_year);
539
540 OPEN acc_date_cur;
541 FETCH acc_date_cur INTO pd_acc_start_date, pd_acc_end_date;
542 CLOSE acc_date_cur;
543
544 FOR v_row IN employee_cur
545 LOOP
546 Ja_Cn_Utility.Add_Sub_Root_Node('EMPLOYEE_RECORDS'
547 ,Ja_Cn_Utility.GV_TAG_TYPE_START
548 );
549 Ja_Cn_Utility.Add_Child_Node('EMPLOYEE_ID'
550 ,v_row.EMPLOYEE_ID
551 );
552 Ja_Cn_Utility.Add_Child_Node('EMPLOYEE_NAME'
553 ,v_row.EMPLOYEE_NAME
554 );
555 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE'
556 ,CN_Get_Doc_Details(v_row.PERSON_ID
557 ,v_row.EFFECTIVE_START_DATE
558 ,'TYPE'
559 )
560 );
561 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_NUMBER'
562 ,CN_Get_Doc_Details(v_row.PERSON_ID
563 ,v_row.EFFECTIVE_START_DATE
564 ,'NAME'
565 )
566 );
567 Ja_Cn_Utility.Add_Child_Node('GENDER'
568 ,Get_Lookup_Meaning(v_row.GENDER, 'SEX')
569 );
570 Ja_Cn_Utility.Add_Fixed_Child_Node('DATE_OF_BIRTH'
571 ,v_row.DATE_OF_BIRTH
572 ,8
573 );
574 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_ID'
575 ,v_row.DEPARTMENT_ID
576 );
577 Ja_Cn_Utility.Add_Fixed_Child_Node('HIRE_DATE'
578 ,v_row.HIRE_DATE
579 ,8
580 );
581 Ja_Cn_Utility.Add_Fixed_Child_Node('TERMINATION_DATE'
582 ,v_row.TERMINATION_DATE
583 ,8
584 ,Ja_Cn_Utility.GV_REQUIRED_NO
585 );
586 Ja_Cn_Utility.Add_Sub_Root_Node('EMPLOYEE_RECORDS'
587 ,Ja_Cn_Utility.GV_TAG_TYPE_END
588 );
589 ln_rec_count := ln_rec_count + 1;
590
591 END LOOP;
592
593 --logging the variables
594 IF (ln_statement_level >= ln_dbg_level)
595 THEN
596 FND_LOG.STRING(ln_statement_level,
597 lv_procedure_name,
598 'ln_rec_count:' || ln_rec_count);
599 END IF; --(ln_statement_level >= ln_dbg_level)
600 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
601 '.variable:' ||
602 'ln_rec_count=' || ln_rec_count);
603
604 -- There is no data found for employee record
605 IF ln_rec_count = 0
606 THEN
607 --Begin: Added for bug# 9722570
608 Ja_Cn_Utility.Print_No_Data_Found_For_Log('EMPLOYEE_RECORDS');
609 Ja_Cn_Utility.Add_Sub_Root_Node('EMPLOYEE_RECORDS'
610 ,Ja_Cn_Utility.GV_TAG_TYPE_START
611 );
612 Ja_Cn_Utility.Add_Child_Node('EMPLOYEE_ID'
613 ,NULL
614 );
615 Ja_Cn_Utility.Add_Child_Node('EMPLOYEE_NAME'
616 ,NULL
617 );
618 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_TYPE'
619 ,NULL
620 );
621 Ja_Cn_Utility.Add_Child_Node('DOCUMENT_NUMBER'
622 ,NULL
623 );
624 Ja_Cn_Utility.Add_Child_Node('GENDER'
625 ,NULL
626 );
627 Ja_Cn_Utility.Add_Fixed_Child_Node('DATE_OF_BIRTH'
628 ,NULL
629 ,8
630 );
631 Ja_Cn_Utility.Add_Child_Node('DEPARTMENT_ID'
632 ,NULL
633 );
634 Ja_Cn_Utility.Add_Fixed_Child_Node('HIRE_DATE'
635 ,NULL
636 ,8
637 );
638 Ja_Cn_Utility.Add_Fixed_Child_Node('TERMINATION_DATE'
639 ,NULL
640 ,8
641 ,Ja_Cn_Utility.GV_REQUIRED_NO
642 );
643 Ja_Cn_Utility.Add_Sub_Root_Node('EMPLOYEE_RECORDS'
644 ,Ja_Cn_Utility.GV_TAG_TYPE_END
645 );
646 --RAISE NO_DATA;
647 --End: Added for bug# 9722570
648 END IF; --ln_rec_count = 0
649
650 --logging for debug
651 IF (ln_proc_level >= ln_dbg_level)
652 THEN
653 FND_LOG.STRING(ln_proc_level,
654 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
655 'Exit procedure');
656 END IF; -- (ln_proc_level>=ln_dbg_level)
657
658 EXCEPTION
659 --Begin: Deleted for bug# 9722570
660 /* WHEN NO_DATA THEN
661 --logging for debug
662 IF (ln_proc_level >= ln_dbg_level)
663 THEN
664 FND_LOG.STRING(ln_proc_level,
665 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
666 'exception',
667 'No data for Employee Records');
668 END IF; --ln_proc_level>=ln_dbg_level
669 Ja_Cn_Utility.Print_No_Data_Found_For_Log('EMPLOYEE_RECORDS');
670 RAISE;*/
671 --End: Deleted for bug# 9722570
672 WHEN OTHERS THEN
673 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
674 THEN
675 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
676 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
677 '.Other_Exception ',
678 SQLCODE || SQLERRM);
679 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
680 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
681 RAISE;
682
683 END Add_Employee_Records;
684
685 END JA_CN_HRMS_EXPORT_PKG;