[Home] [Help]
118: --
119: Procedure ini_person is
120: L_proc varchar2(80) := g_package||'.ini_person';
121: Begin
122: hr_utility.set_location ('Entering '|| l_proc,5);
123: g_cache_person := NULL;
124: hr_utility.set_location ('Leaving ' || l_proc,10);
125: End ini_person;
126: --
120: L_proc varchar2(80) := g_package||'.ini_person';
121: Begin
122: hr_utility.set_location ('Entering '|| l_proc,5);
123: g_cache_person := NULL;
124: hr_utility.set_location ('Leaving ' || l_proc,10);
125: End ini_person;
126: --
127: -- ============================================================================
128: -- <
130: --
131: Procedure ini_comp_obj is
132: L_proc varchar2(80) := g_package||'.ini_comp_obj';
133: Begin
134: hr_utility.set_location ('Entering ' || l_proc, 5);
135: g_cache_comp.delete;
136: g_cache_comp_cnt := 0;
137: hr_utility.set_location ('Leaving ' || l_proc, 10);
138: End ini_comp_obj ;
133: Begin
134: hr_utility.set_location ('Entering ' || l_proc, 5);
135: g_cache_comp.delete;
136: g_cache_comp_cnt := 0;
137: hr_utility.set_location ('Leaving ' || l_proc, 10);
138: End ini_comp_obj ;
139: --
140: -- ============================================================================
141: -- <
175: l_rec c1%rowtype;
176: l_cnt Number(15) := g_cache_comp_cnt;
177: L_proc varchar2(80) := g_package||'.Cache_comp_obj';
178: Begin
179: hr_utility.set_location ('Entering '||l_proc,10);
180: l_cnt := l_cnt + 1;
181: If (p_prtt_enrt_rslt_id is not NULL) then
182: Open c1;
183: fetch c1 into l_rec;
218: If (p_suspended = 'Y') then
219: g_cache_comp(l_cnt).susp_flag := TRUE;
220: End if;
221: g_cache_comp_cnt := l_cnt;
222: hr_utility.set_location ('Leaving '||l_proc,10);
223: End Cache_comp_obj;
224: --
225: -- ============================================================================
226: -- <
256: l_proc varchar2(80) := g_package||'.write_comp';
257: l_output varchar2(3000); -- UTF8 Change Bug 2254683 varchar2(1000)
258: l_first Boolean := TRUE;
259: Begin
260: hr_utility.set_location ('Entering '||l_proc,5 );
261: For i in 1..g_cache_comp_cnt loop
262: If (g_cache_comp(i).def_flag
263: or g_cache_comp(i).ins_flag
264: or g_cache_comp(i).upd_flag
324: ', End:'||to_char(g_cache_comp(i).cvg_thru_dt,'DD/MM/YYYY') ;
325: write(p_text => l_output);
326: End if;
327: End loop;
328: hr_utility.set_location ('Leaving '||l_proc,10);
329: End write_comp;
330: --
331: -- ============================================================================
332: -- <
347: --
348: Procedure ini_proc_info is
349: L_proc varchar2(80) := g_package||'.ini_proc_info';
350: Begin
351: hr_utility.set_location ('Entering '||l_proc,05);
352: g_proc_info := NULL;
353: g_proc_info.start_date := sysdate;
354: g_proc_info.start_time_numeric := dbms_utility.get_time;
355: g_proc_info.num_persons_selected := 0;
358: g_proc_info.num_persons_processed_succ := 0;
359: g_proc_info.num_persons_processed := 0;
360: g_num_processes := 0;
361: g_processes_tbl.delete;
362: hr_utility.set_location ('Leaving '||l_proc,10);
363: End ini_proc_info;
364:
365: --
366: -- ============================================================================
369: --
370: Procedure ini(p_actn_cd varchar2 default hr_api.g_varchar2) is
371: L_proc varchar2(80) := g_package||'.ini';
372: Begin
373: hr_utility.set_location ('Entering '||l_proc,05);
374: If(p_actn_cd = hr_api.g_varchar2) then
375: ini_comp_obj;
376: ini_person;
377: ini_comp_obj_name;
383: ini_comp_obj_name;
384: Elsif(upper(substr(p_actn_cd,1,9)) = 'PROC_INFO') then
385: ini_proc_info;
386: End if;
387: hr_utility.set_location ('Leaving '||l_proc,10);
388: End ini;
389: --
390: -- ============================================================================
391: -- << Rpt_error >>
399: Begin
400: If (p_rpt_flag ) then
401: write(p_text => '<<
402: End if;
403: hr_utility.set_location('> Fail in ' || p_proc, 999 );
404: hr_utility.set_location('>> While ' || p_last_actn, 999);
405: End rpt_error;
406: --
407: -- ============================================================================
400: If (p_rpt_flag ) then
401: write(p_text => '<<
402: End if;
403: hr_utility.set_location('> Fail in ' || p_proc, 999 );
404: hr_utility.set_location('>> While ' || p_last_actn, 999);
405: End rpt_error;
406: --
407: -- ============================================================================
408: -- << Function: get_pgm_name >>
426: l_actn varchar2(80);
427: l_idx binary_integer;
428: l_fetch Boolean := FALSE;
429: begin
430: hr_utility.set_location ('Entering '||l_proc,10);
431: --
432: l_actn := 'Retrieve program from cache or database...';
433: --
434: If (p_pgm_id is NULL) then
458: ret_str := g_pgm_tbl(l_idx).name;
459: End if;
460: close c1;
461: End if;
462: hr_utility.set_location ('Leaving '||l_proc,70);
463: return ret_str;
464: Exception
465: when others then
466: rpt_error(p_proc => l_proc
492: l_actn varchar2(80);
493: l_idx Binary_integer;
494: l_fetch Boolean := FALSE;
495: Begin
496: hr_utility.set_location ('Entering '||l_proc,10);
497: l_actn := 'Initializing...';
498: If (p_pl_typ_id is NULL) then
499: ret_str := 'NO PLAN TYPE';
500: Elsif (p_pl_typ_id > g_mx_binary_integer) then
521: ret_str := g_pl_typ_tbl(l_idx).name;
522: End if;
523: close c1;
524: End if;
525: hr_utility.set_location ('Leaving '||l_proc,70);
526: return ret_str;
527: Exception
528: When others then
529: rpt_error(p_proc => l_proc
555: l_actn varchar2(80);
556: l_idx binary_integer;
557: l_fetch Boolean := FALSE;
558: Begin
559: hr_utility.set_location ('Entering '||l_proc,10);
560: --
561: l_actn := 'Initializing...';
562: --
563: If (p_pl_id is NULL) then
587: ret_str := g_pl_tbl(l_idx).name;
588: End if;
589: close c1;
590: End if;
591: hr_utility.set_location ('Leaving '||l_proc,70);
592: return ret_str;
593: Exception
594: when others then
595: rpt_error(p_proc => l_proc
626: l_actn varchar2(80);
627: l_idx binary_integer;
628: l_fetch Boolean := FALSE;
629: Begin
630: hr_utility.set_location ('Entering '||l_proc,10);
631: --
632: l_actn := 'Initializing...';
633: --
634: If (p_oipl_id is NULL) then
658: ret_str := g_opt_tbl(l_idx).name;
659: End if;
660: close c1;
661: End if;
662: hr_utility.set_location ('Leaving '||l_proc,70);
663: return ret_str;
664: Exception
665: when others then
666: rpt_error(p_proc => l_proc
679: ,p_non_person_cd in varchar2 default null
680: ) is
681: l_proc varchar2(80) := g_package||'.write_logfile';
682: begin
683: hr_utility.set_location ('Entering '||l_proc,10);
684: write(p_text => benutils.g_banner_minus);
685: write(p_text => 'Benefits Statistical Information');
686: write(p_text => benutils.g_banner_minus);
687:
697: to_char(p_num_pers_processed+p_num_pers_errored));
698: end if;
699: write(p_text => benutils.g_banner_minus);
700:
701: hr_utility.set_location ('Leaving '||l_proc,99);
702: Exception
703: when others then
704: fnd_message.set_name('BEN','BEN_91663_BENMNGLE_LOGGING');
705: fnd_message.set_token('PROC',l_proc);
717: ) is
718: l_proc varchar2(80) := g_package||'.write_rec';
719: l_rec ben_type.g_report_rec := g_rec;
720: Begin
721: hr_utility.set_location ('Entering ' || l_proc,05);
722: l_rec.rep_typ_cd := p_typ_cd;
723: l_rec.text := p_text;
724: l_rec.ERROR_MESSAGE_CODE := p_err_cd;
725: benutils.write(p_rec => l_rec);
722: l_rec.rep_typ_cd := p_typ_cd;
723: l_rec.text := p_text;
724: l_rec.ERROR_MESSAGE_CODE := p_err_cd;
725: benutils.write(p_rec => l_rec);
726: hr_utility.set_location ('Leaving ' || l_proc,10);
727: End write_rec;
728: --
729: -- ============================================================================
730: -- <
747: l_proc varchar2(80) := g_package||'.End_process';
748: l_batch_proc_id number;
749: l_object_version_number number;
750: Begin
751: hr_utility.set_location ('Entering ' || l_proc,05);
752: --
753: -- Get totals for unprocessed, processed successfully and errored
754: --
755: open c_actions;
753: -- Get totals for unprocessed, processed successfully and errored
754: --
755: open c_actions;
756: Loop
757: hr_utility.set_location (l_proc,6);
758: fetch c_actions into l_actions;
759: exit when c_actions%notfound;
760: If l_actions.action_status_cd = 'P' then
761: g_proc_info.num_persons_processed_succ := l_actions.amount;
763: g_proc_info.num_persons_errored := l_actions.amount;
764: Elsif l_actions.action_status_cd in ('U', 'T') then
765: g_proc_info.num_persons_unprocessed := l_actions.amount;
766: End if;
767: hr_utility.set_location (l_proc,7);
768: End loop;
769: hr_utility.set_location (l_proc,8);
770: close c_actions;
771: g_proc_info.num_persons_selected := p_person_selected;
765: g_proc_info.num_persons_unprocessed := l_actions.amount;
766: End if;
767: hr_utility.set_location (l_proc,7);
768: End loop;
769: hr_utility.set_location (l_proc,8);
770: close c_actions;
771: g_proc_info.num_persons_selected := p_person_selected;
772: hr_utility.set_location (l_proc,9);
773: --
768: End loop;
769: hr_utility.set_location (l_proc,8);
770: close c_actions;
771: g_proc_info.num_persons_selected := p_person_selected;
772: hr_utility.set_location (l_proc,9);
773: --
774: -- Set value of number of persons processed
775: --
776: g_proc_info.num_persons_processed
775: --
776: g_proc_info.num_persons_processed
777: := g_proc_info.num_persons_errored +
778: g_proc_info.num_persons_processed_succ;
779: hr_utility.set_location (l_proc,10);
780: hr_utility.set_location (l_proc||' start_date='||g_proc_info.start_date||'.',10);
781: ben_batch_proc_info_api.create_batch_proc_info
782: (P_VALIDATE => FALSE
783: ,P_BATCH_PROC_ID => l_batch_proc_id
776: g_proc_info.num_persons_processed
777: := g_proc_info.num_persons_errored +
778: g_proc_info.num_persons_processed_succ;
779: hr_utility.set_location (l_proc,10);
780: hr_utility.set_location (l_proc||' start_date='||g_proc_info.start_date||'.',10);
781: ben_batch_proc_info_api.create_batch_proc_info
782: (P_VALIDATE => FALSE
783: ,P_BATCH_PROC_ID => l_batch_proc_id
784: ,P_BENEFIT_ACTION_ID => p_benefit_action_id
795: ,P_PER_ERR => g_proc_info.num_persons_errored
796: ,P_BUSINESS_GROUP_ID => p_business_group_id
797: ,P_OBJECT_VERSION_NUMBER=> l_object_version_number
798: );
799: hr_utility.set_location (l_proc,11);
800: benutils.write_table_and_file(p_table => true,
801: p_file => true);
802: hr_utility.set_location (l_proc,12);
803: commit;
798: );
799: hr_utility.set_location (l_proc,11);
800: benutils.write_table_and_file(p_table => true,
801: p_file => true);
802: hr_utility.set_location (l_proc,12);
803: commit;
804: hr_utility.set_location ('Leaving ' || l_proc,100);
805: End end_process;
806: --
800: benutils.write_table_and_file(p_table => true,
801: p_file => true);
802: hr_utility.set_location (l_proc,12);
803: commit;
804: hr_utility.set_location ('Leaving ' || l_proc,100);
805: End end_process;
806: --
807: -- ============================================================================
808: -- <
810: --
811: Procedure write (p_text varchar2) is
812: l_proc varchar2(80) := g_package||'.Write';
813: Begin
814: --hr_utility.set_location ('Entering '||l_proc,05);
815: If fnd_global.conc_request_id <> -1 then
816: fnd_file.put_line(which=>fnd_file.log
817: ,buff => p_text);
818: End if;
815: If fnd_global.conc_request_id <> -1 then
816: fnd_file.put_line(which=>fnd_file.log
817: ,buff => p_text);
818: End if;
819: --hr_utility.set_location ('Leaving '||l_proc,99);
820: End write;
821: --
822: -- ============================================================================
823: -- <
988: l_proc varchar2(80) := g_package||'.cache_person_information';
989: l_count number(9) := 0;
990: l_person_type c_person_type%rowtype;
991: begin
992: hr_utility.set_location ('Entering '||l_proc,05);
993: open c_person;
994: fetch c_person into g_cache_person.full_name
995: ,g_cache_person.date_of_birth
996: ,g_cache_person.date_of_death
1143: -- Put person_id into g_rec cache
1144: --
1145: g_rec.person_id := p_person_id;
1146: g_rec.national_identifier := g_cache_person.national_identifier;
1147: hr_utility.set_location ('Leaving '||l_proc,10);
1148: end cache_person_information;
1149: --
1150: -- ============================================================================
1151: -- <
1160: l_proc varchar2(80) := g_package||'.person_header';
1161: l_output_string varchar2(2000); -- UTF8 Change Bug 2254683 varchar2(100)
1162: --
1163: Begin
1164: hr_utility.set_location ('Entering '||l_proc,10);
1165: --
1166: -- Cache person data
1167: --
1168: cache_person_information
1217: ' Pyr: '||
1218: rpad(substr(g_cache_person.payroll_name,1,15),15,' ')||
1219: rpad(benutils.id(g_cache_person.payroll_id),8,' ');
1220: write(p_text => l_output_string);
1221: hr_utility.set_location ('Leaving '||l_proc,10);
1222: end person_header;
1223: --
1224: -- ============================================================================
1225: -- <
1259: l_proc varchar2(80) := g_package||'.print_parameters';
1260: l_string varchar2(80);
1261: l_actn varchar2(80);
1262: begin
1263: hr_utility.set_location ('Entering '||l_proc,10);
1264: write(p_text => 'Runtime Parameters');
1265: write(p_text => '------------------');
1266: -- bug 5450842
1267: if p_mode is not null AND p_mode <> hr_api.g_varchar2
1466: If (nvl(p_audit_log,'xxxx') <> hr_api.g_varchar2) then
1467: write(p_text => 'Audit log flag :'||
1468: hr_general.decode_lookup('YES_NO',p_audit_log));
1469: End if;
1470: hr_utility.set_location ('Leaving '||l_proc,10);
1471: exception
1472: when others then
1473: ben_batch_utils.rpt_error(p_proc => l_proc
1474: ,p_last_actn => l_actn );
1513: l_return varchar2(30);
1514: l_assignment_id number;
1515: l_actn varchar2(80);
1516: Begin
1517: hr_utility.set_location ('Entering '||l_proc,10);
1518: --
1519: -- Get assignment ID form per_assignments_f table.
1520: --
1521: l_actn := 'Opening Assignment cursor...';
1570: fnd_message.set_token('PROC',l_proc);
1571: Raise ben_batch_utils.g_record_error;
1572: End if;
1573: return l_return;
1574: hr_utility.set_location ('Leaving '||l_proc,10);
1575: Exception
1576: When ben_batch_utils.g_record_error then
1577: raise;
1578: when others then
1596: From fnd_concurrent_requests fnd
1597: Where fnd.phase_code <> 'C'
1598: And fnd.request_id = p_request_id;
1599: Begin
1600: hr_utility.set_location ('Entering '||l_proc,5);
1601: If g_num_processes <> 0 then
1602: l_actn := 'Checking Slaves.....';
1603: While l_no_slaves loop
1604: l_no_slaves := false;
1616: dbms_lock.sleep(5);
1617: End if;
1618: End loop;
1619: End if;
1620: hr_utility.set_location ('Leavinging '||l_proc,5);
1621: Exception
1622: when others then
1623: rpt_error(p_proc =>l_proc,p_last_actn=>l_actn,p_rpt_flag=>p_rpt_flag);
1624: raise;
1661: l_num_persons number := 0;
1662: l_range_id number;
1663: l_actn varchar2(80);
1664: Begin
1665: hr_utility.set_location ('Leaving '||l_proc,10);
1666: --
1667: -- Delete ranges from ben_batch_ranges table
1668: --
1669: l_actn := 'Calling ben_benmngle_purge.delete_batch_range_rows...';
1730: commit;
1731: End if;
1732: p_num_ranges := l_num_ranges;
1733: p_num_persons := l_num_persons;
1734: hr_utility.set_location ('Leaving '||l_proc,10);
1735: Exception
1736: when others then
1737: rpt_error(p_proc => l_proc, p_last_actn => l_actn);
1738: raise;
1750: ) is
1751: l_proc varchar2(80) := g_package||'.batch_reports';
1752: l_request_id number;
1753: Begin
1754: hr_utility.set_location('Entering :'||l_proc,10);
1755: If (p_subtitle is NULL) then
1756: l_request_id := fnd_request.submit_request
1757: (application => 'BEN'
1758: ,program => p_program_name
1774: raise ben_batch_utils.g_record_error;
1775: Else
1776: p_request_id := l_request_id;
1777: End if;
1778: hr_utility.set_location('Leaving :'||l_proc,10);
1779: Exception
1780: when others then
1781: rpt_error(p_proc => l_proc, p_last_actn => 'Submitting ' || p_program_name);
1782: raise;
1854: l_val_9 number :=0;
1855: l_val_10 number :=0;
1856: l_reporting c_reporting%rowtype;
1857: Begin
1858: hr_utility.set_location('Entering :'||l_proc,10);
1859: open c_reporting;
1860: loop
1861: fetch c_reporting into l_reporting;
1862: exit when c_reporting%notfound;
1882: p_val_10 := l_reporting.amount;
1883: End if;
1884: End loop;
1885: close c_reporting;
1886: hr_utility.set_location('Leaving :'||l_proc,10);
1887: End summary_by_action;
1888: --
1889: -- ============================================================================
1890: -- <
1941: l_cnt binary_integer := 1;
1942: lc binary_integer := 0;
1943: l_actn varchar2(80);
1944: Begin
1945: hr_utility.set_location('Entering :'||l_proc,05);
1946: l_actn := 'Openning C1 cursor...';
1947: open c1;
1948: fetch c1 into l_exec;
1949: If c1%found then
2026: Else
2027: l_cnt := 0;
2028: End if;
2029: close c1;
2030: hr_utility.set_location('Leaving :'||l_proc,10);
2031: Exception
2032: When others then
2033: rpt_error(p_proc => l_proc, p_last_actn => l_actn);
2034: raise;
2248: ;
2249: --
2250: begin
2251: --
2252: hr_utility.set_location('Entering :'||l_proc,10);
2253: --
2254: -- Get parameter information from batch process run
2255: --
2256: open c_benefit_actions;
2288: p_date_from; /* Bug 3517604 */
2289: --
2290: close c_benefit_actions;
2291: --
2292: hr_utility.set_location('Leaving :'||l_proc,10);
2293: --
2294: end standard_header;
2295: --
2296: --Bug 2978945
2307: g_dynamic_sql VARCHAR2(2000);
2308: g_debug BOOLEAN;
2309: --
2310: BEGIN
2311: g_debug := hr_utility.debug_enabled;
2312: IF g_debug THEN
2313: l_proc := g_package||'rows_exist';
2314: hr_utility.set_location('Entering:'||l_proc, 5);
2315: END IF;
2310: BEGIN
2311: g_debug := hr_utility.debug_enabled;
2312: IF g_debug THEN
2313: l_proc := g_package||'rows_exist';
2314: hr_utility.set_location('Entering:'||l_proc, 5);
2315: END IF;
2316: IF (p_base_key_value IS NOT NULL) THEN
2317: IF p_base_table_name IS NULL OR
2318: p_base_key_column IS NULL THEN
2338: INTO l_ret_column
2339: USING p_base_key_value;
2340: -- one row exists so return true
2341: IF g_debug THEN
2342: hr_utility.set_location('Leaving:'||l_proc, 10);
2343: END IF;
2344: RETURN(TRUE);
2345: END IF;
2346: EXCEPTION
2345: END IF;
2346: EXCEPTION
2347: WHEN NO_DATA_FOUND THEN
2348: IF g_debug THEN
2349: hr_utility.set_location('Leaving:'||l_proc, 15);
2350: END IF;
2351: -- return false as no rows exist
2352: RETURN(FALSE);
2353: WHEN TOO_MANY_ROWS THEN
2351: -- return false as no rows exist
2352: RETURN(FALSE);
2353: WHEN TOO_MANY_ROWS THEN
2354: IF g_debug THEN
2355: hr_utility.set_location('Leaving:'||l_proc, 20);
2356: END IF;
2357: -- return TRUE because more than one row exists
2358: RETURN(TRUE);
2359: WHEN OTHERS THEN
2401: l_assignment_id number;
2402: l_actn varchar2(80);
2403: value_exception exception ;
2404: Begin
2405: hr_utility.set_location ('Entering '||l_proc,10);
2406: --
2407: -- Get assignment ID form per_assignments_f table.
2408: --
2409: l_actn := 'Opening Assignment cursor...';
2446: --
2447: If upper(p_return) not in ('Y', 'N') then
2448: Raise value_exception ;
2449: End if;
2450: hr_utility.set_location ('Leaving '||l_proc,10);
2451: Exception
2452: When ben_batch_utils.g_record_error then
2453: p_return := 'N' ;
2454: fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');