[Home] [Help]
7:
8: -- Declaring local procedures and functions
9: Function get_information_type(p_noa_id in number) return varchar2;
10: Procedure Route_Errorerd_SF52(
11: p_sf52 in out nocopy ghr_pa_requests%rowtype,
12: p_error in varchar2,
13: p_result out nocopy varchar2);
14:
15: Procedure Single_Action_SF52(p_sf52_data in out nocopy ghr_pa_requests%rowtype,
11: p_sf52 in out nocopy ghr_pa_requests%rowtype,
12: p_error in varchar2,
13: p_result out nocopy varchar2);
14:
15: Procedure Single_Action_SF52(p_sf52_data in out nocopy ghr_pa_requests%rowtype,
16: p_process_type in varchar2 default 'CURRENT',
17: p_capped_other_pay in number default null);
18: Procedure Dual_Action_SF52(p_sf52_data in out nocopy ghr_pa_requests%rowtype,
19: p_process_type in varchar2 default 'CURRENT');
14:
15: Procedure Single_Action_SF52(p_sf52_data in out nocopy ghr_pa_requests%rowtype,
16: p_process_type in varchar2 default 'CURRENT',
17: p_capped_other_pay in number default null);
18: Procedure Dual_Action_SF52(p_sf52_data in out nocopy ghr_pa_requests%rowtype,
19: p_process_type in varchar2 default 'CURRENT');
20:
21: procedure create_ghr_errorlog(
22: p_program_name in ghr_process_log.program_name%type,
28: Procedure Update_shadow_row ( p_shadow_data in ghr_pa_request_shadow%rowtype,
29: p_result out nocopy Boolean);
30:
31: /*Procedure fetch_update_routing_details
32: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
33: p_object_version_number in out ghr_pa_requests.object_version_number%type,
34: p_position_id in ghr_pa_requests.to_position_id%type,
35: p_effective_date in ghr_pa_requests.effective_date%type,
36: p_retcode out number,
29: p_result out nocopy Boolean);
30:
31: /*Procedure fetch_update_routing_details
32: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
33: p_object_version_number in out ghr_pa_requests.object_version_number%type,
34: p_position_id in ghr_pa_requests.to_position_id%type,
35: p_effective_date in ghr_pa_requests.effective_date%type,
36: p_retcode out number,
37: p_route_flag out boolean
30:
31: /*Procedure fetch_update_routing_details
32: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
33: p_object_version_number in out ghr_pa_requests.object_version_number%type,
34: p_position_id in ghr_pa_requests.to_position_id%type,
35: p_effective_date in ghr_pa_requests.effective_date%type,
36: p_retcode out number,
37: p_route_flag out boolean
38: );
31: /*Procedure fetch_update_routing_details
32: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
33: p_object_version_number in out ghr_pa_requests.object_version_number%type,
34: p_position_id in ghr_pa_requests.to_position_id%type,
35: p_effective_date in ghr_pa_requests.effective_date%type,
36: p_retcode out number,
37: p_route_flag out boolean
38: );
39: */
53: -- Pre-Requisities:
54: -- None.
55: --
56: -- In Parameters:
57: -- p_sf52_data -> ghr_pa_requests record of the sf52.
58: -- p_process_type -> either current action or future action.
59: -- p_validate -> flag to indicate if this is validate only mode.
60: -- p_capped_other_pay -> Capped Other Pay amount due to Update 34 changes
61: --
73: --
74: -- {End Of Comments}
75: -- ---------------------------------------------------------------------------
76: Procedure Process_SF52 (
77: p_sf52_data in out nocopy ghr_pa_requests%rowtype,
78: p_process_type in varchar2 default 'CURRENT',
79: p_validate in Boolean default FALSE,
80: p_capped_other_pay in number default NULL) is
81:
81:
82: l_noa_code varchar2(4);
83: l_noa_family_code varchar2(30);
84: l_proc varchar2(30):='process_sf52';
85: l_sf52_data ghr_pa_requests%rowtype;
86: Begin
87:
88: l_sf52_data := p_sf52_data ; --NOCOPY Changes
89:
170: -- non-correction/cancellation sf52.
171: -- Pre-Requisities:
172: -- None.
173: -- In Parameters:
174: -- p_sf52_data -> ghr_pa_requests record of the sf52.
175: --
176: -- Post Success:
177: -- The sf52 will have been processed.
178: --
183: -- Access Status:
184: -- Internal Development Use Only.
185: -- ---------------------------------------------------------------------------
186:
187: Procedure Single_Action_SF52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
188: p_process_type in varchar2 default 'CURRENT',
189: p_capped_other_pay in number default null ) is
190:
191: l_today date:=sysdate;
192: l_session_var ghr_history_api.g_session_var_type;
193: l_result varchar2(30);
194: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
195: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
196: l_sf52_shadow ghr_pa_requests%rowtype;
197: l_shadow_data ghr_pa_request_shadow%rowtype;
198: l_sf52_data ghr_pa_requests%rowtype;
199: l_proc varchar2(30):='single_action_sf52';
200: l_capped_other_pay number;
194: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
195: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
196: l_sf52_shadow ghr_pa_requests%rowtype;
197: l_shadow_data ghr_pa_request_shadow%rowtype;
198: l_sf52_data ghr_pa_requests%rowtype;
199: l_proc varchar2(30):='single_action_sf52';
200: l_capped_other_pay number;
201:
202: Begin
322: -- This procedure handles the case of a dual action.
323: -- Pre-Requisities:
324: -- None.
325: -- In Parameters:
326: -- p_sf52_data -> ghr_pa_requests record of the sf52.
327: -- Post Success:
328: -- The sf52 will have been processed.
329: -- Post Failure:
330: -- Exception will have been raised with message explaining what the problem is.
333: -- Access Status:
334: -- Internal Development Use Only.
335: -- ---------------------------------------------------------------------------
336:
337: Procedure Dual_Action_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
338: p_process_type in varchar2 default 'CURRENT') is
339:
340: l_today date:=sysdate;
341: l_sf52_data ghr_pa_requests%rowtype;
337: Procedure Dual_Action_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
338: p_process_type in varchar2 default 'CURRENT') is
339:
340: l_today date:=sysdate;
341: l_sf52_data ghr_pa_requests%rowtype;
342: l_sf52_data_save ghr_pa_requests%rowtype;
343: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
344: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
345: l_shadow_data ghr_pa_request_shadow%rowtype;
338: p_process_type in varchar2 default 'CURRENT') is
339:
340: l_today date:=sysdate;
341: l_sf52_data ghr_pa_requests%rowtype;
342: l_sf52_data_save ghr_pa_requests%rowtype;
343: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
344: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
345: l_shadow_data ghr_pa_request_shadow%rowtype;
346: l_session_var ghr_history_api.g_session_var_type;
652: -- Internal Development Use Only.
653: -- ---------------------------------------------------------------------------
654: PROCEDURE assign_new_rg (
655: p_action_num in number,
656: p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
657:
658: TYPE fields_type is record
659: (form_field_name ghr_pa_data_fields.form_field_name%TYPE,
660: process_method_code ghr_noa_fam_proc_methods.process_method_code%TYPE
664: SELECT min(pa_history_id)
665: FROM ghr_pa_history ghrpah_1
666: WHERE ghrpah_1.pa_request_id =
667: (SELECT min(pa_request_id)
668: FROM ghr_pa_requests
669: CONNECT BY pa_request_id = prior altered_pa_request_id
670: START WITH pa_request_id = cp_pa_request_id)
671: AND ghrpah_1.nature_of_action_id = cp_noa_id;
672:
685: TYPE fld_names_typ is TABLE of fields_type
686: INDEX BY BINARY_INTEGER;
687: l_fld_names_tab1 fld_names_typ;
688: l_fld_names_tab2 fld_names_typ;
689: l_pa_req ghr_pa_requests%rowtype;
690: l_column_count number := 0;
691: l_column_count1 number := 0;
692: l_column_count2 number := 0;
693: l_refresh_called boolean:= false;
691: l_column_count1 number := 0;
692: l_column_count2 number := 0;
693: l_refresh_called boolean:= false;
694: l_non_from_called boolean:= false;
695: l_non_from_pa_req ghr_pa_requests%rowtype;
696: l_correction boolean:= null;
697: l_pa_req_ref ghr_pa_requests%rowtype;
698: l_pa_req_ref2 ghr_pa_requests%rowtype;
699: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
693: l_refresh_called boolean:= false;
694: l_non_from_called boolean:= false;
695: l_non_from_pa_req ghr_pa_requests%rowtype;
696: l_correction boolean:= null;
697: l_pa_req_ref ghr_pa_requests%rowtype;
698: l_pa_req_ref2 ghr_pa_requests%rowtype;
699: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
700: tmp_varchar varchar2(150);
701:
694: l_non_from_called boolean:= false;
695: l_non_from_pa_req ghr_pa_requests%rowtype;
696: l_correction boolean:= null;
697: l_pa_req_ref ghr_pa_requests%rowtype;
698: l_pa_req_ref2 ghr_pa_requests%rowtype;
699: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
700: tmp_varchar varchar2(150);
701:
702: l_proc varchar2(30):='assign_new_rg';
750: return ret_value;
751: END;
752:
753: FUNCTION get_field_info( p_field_name IN VARCHAR2,
754: p_sf52_data IN ghr_pa_requests%rowtype) RETURN varchar2 IS
755: l_proc varchar2(30):='get_field_info';
756: l_ret_value varchar2(2000);
757: BEGIN
758: hr_utility.set_location('Entering: ' ||l_proc, 10);
2283: --
2284: -- {End Of Comments}
2285: -- ---------------------------------------------------------------------------
2286:
2287: PROCEDURE copy_2ndNoa_to_1stNoa (p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2288: l_proc varchar2(30):='copy_2ndNoa_to_1stNoa';
2289: l_pa_req ghr_pa_requests%rowtype;
2290:
2291: BEGIN
2285: -- ---------------------------------------------------------------------------
2286:
2287: PROCEDURE copy_2ndNoa_to_1stNoa (p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2288: l_proc varchar2(30):='copy_2ndNoa_to_1stNoa';
2289: l_pa_req ghr_pa_requests%rowtype;
2290:
2291: BEGIN
2292: l_pa_req :=p_pa_req ; ---NOCOPY Changes
2293:
2357: --
2358: -- {End Of Comments}
2359: -- ---------------------------------------------------------------------------
2360:
2361: PROCEDURE null_2ndNoa_cols(p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2362: l_proc varchar2(30):='null_2ndNoa_cols';
2363: l_pa_req ghr_pa_requests%rowtype;
2364:
2365: BEGIN
2359: -- ---------------------------------------------------------------------------
2360:
2361: PROCEDURE null_2ndNoa_cols(p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2362: l_proc varchar2(30):='null_2ndNoa_cols';
2363: l_pa_req ghr_pa_requests%rowtype;
2364:
2365: BEGIN
2366: l_pa_req :=p_pa_req ; ---NOCOPY Changes
2367:
2508:
2509: l_log_text varchar2(2000);
2510: l_error varchar2(512); --512
2511: l_req varchar2(10);
2512: l_sf52_rec ghr_pa_requests%rowtype;
2513: l_result varchar2(30);
2514:
2515: l_retcode Number;
2516: l_calculated Boolean;
2528: ----If p_poi is passed then use the following modified SQL.
2529:
2530: cursor c_par_pend_per_poi is
2531: select person_id,effective_date
2532: from ghr_pa_requests a, ghr_pa_routing_history b
2533: where effective_date <= sysdate
2534: and pa_notification_id is null
2535: and approval_date is not null
2536: and a.pa_request_id = b.pa_request_id
2556:
2557: cursor c_par_pend_per is
2558: ---------Added as part of fix for the bug 2180343
2559: select person_id,effective_date
2560: from ghr_pa_requests a, ghr_pa_routing_history b
2561: where effective_date <= sysdate
2562: and pa_notification_id is null
2563: and approval_date is not null
2564: and a.pa_request_id = b.pa_request_id
2576: order by 2,1;
2577:
2578: /*********** Commented out as part of fix 2180343 ***** AVR
2579: select distinct person_id,effective_date
2580: from ghr_pa_requests a
2581: where effective_date <= sysdate and
2582: pa_notification_id is null and
2583: approval_date is not null and
2584: exists (select 'exists'
2590: order by 2,1;
2591:
2592: cursor c_get_par_id is
2593: select pa_request_id ,noa.order_of_processing
2594: from ghr_pa_requests a, ghr_nature_of_actions noa
2595: where person_id = l_person_id
2596: and effective_date = l_effective_date
2597: and pa_notification_id is null
2598: and approval_date is not null
2606: order by 2 asc;
2607:
2608:
2609: Cursor get_req is
2610: select * from ghr_pa_requests
2611: where pa_request_id = l_pa_request_id;
2612: ***********/
2613:
2614:
2623: ---Local Procedure.
2624: PROCEDURE sub_proc_futr_sf52 is
2625: cursor c_get_par_id is
2626: select pa_request_id ,noa.order_of_processing
2627: from ghr_pa_requests a, ghr_nature_of_actions noa
2628: where person_id = l_person_id
2629: and effective_date = l_effective_date
2630: and pa_notification_id is null
2631: and approval_date is not null
2639: order by 2 asc;
2640:
2641:
2642: Cursor get_req is
2643: select * from ghr_pa_requests
2644: where pa_request_id = l_pa_request_id;
2645:
2646: l_rec get_req%rowtype;
2647:
2646: l_rec get_req%rowtype;
2647:
2648: -- Start of Bug 3602261
2649:
2650: l_object_version_number ghr_pa_requests.object_version_number%type;
2651:
2652: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2653: select par.object_version_number
2654: from ghr_pa_requests par
2648: -- Start of Bug 3602261
2649:
2650: l_object_version_number ghr_pa_requests.object_version_number%type;
2651:
2652: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2653: select par.object_version_number
2654: from ghr_pa_requests par
2655: where par.pa_request_id = p_pa_request_id; -- 3769917
2656:
2650: l_object_version_number ghr_pa_requests.object_version_number%type;
2651:
2652: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2653: select par.object_version_number
2654: from ghr_pa_requests par
2655: where par.pa_request_id = p_pa_request_id; -- 3769917
2656:
2657: -- End of Bug 3602261
2658:
2941:
2942: --
2943: --
2944: Procedure Route_Errorerd_SF52(
2945: p_sf52 in out nocopy ghr_pa_requests%rowtype,
2946: p_error in varchar2,
2947: p_result out nocopy varchar2) is
2948:
2949: l_u_prh_object_version_number number;
2953: l_log_text varchar2(2000);
2954: -- l_prog_name varchar2(30):='GHR Process Future SF52';
2955: l_proc varchar2(30):='Route_Errerd_SF52';
2956: l_new_line varchar2(1) := substr('',1,1);
2957: l_sf52 ghr_pa_requests%rowtype ;
2958:
2959: Begin
2960: l_sf52 :=p_sf52; --NOCOPY Changes
2961: hr_utility.set_location( 'Entering : ' || l_proc, 10);
3007:
3008: /*--Added by Rohini
3009:
3010: Procedure fetch_update_routing_details
3011: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3012: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3013: p_position_id in ghr_pa_requests.to_position_id%type,
3014: p_effective_date in ghr_pa_requests.effective_date%type,
3015: p_retcode out nocopy number,
3008: /*--Added by Rohini
3009:
3010: Procedure fetch_update_routing_details
3011: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3012: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3013: p_position_id in ghr_pa_requests.to_position_id%type,
3014: p_effective_date in ghr_pa_requests.effective_date%type,
3015: p_retcode out nocopy number,
3016: p_route_flag out nocopy boolean
3009:
3010: Procedure fetch_update_routing_details
3011: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3012: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3013: p_position_id in ghr_pa_requests.to_position_id%type,
3014: p_effective_date in ghr_pa_requests.effective_date%type,
3015: p_retcode out nocopy number,
3016: p_route_flag out nocopy boolean
3017: )
3010: Procedure fetch_update_routing_details
3011: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3012: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3013: p_position_id in ghr_pa_requests.to_position_id%type,
3014: p_effective_date in ghr_pa_requests.effective_date%type,
3015: p_retcode out nocopy number,
3016: p_route_flag out nocopy boolean
3017: )
3018:
3018:
3019: is
3020:
3021: l_groupbox_id ghr_groupboxes.groupbox_id%type;
3022: l_routing_group_id ghr_pa_requests.routing_group_id%type;
3023: l_proc varchar2(72) := ' ghr_process_sf52.' || 'update_routing_details';
3024: l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
3025: l_pa_object_version_number ghr_pa_routing_history.object_version_number%type;
3026: l_user_name varchar2(30);
3052: l_pos_ei_data per_position_extra_info%type;
3053: l_groupbox_name ghr_groupboxes.name%type;
3054: l_groupbox_id ghr_groupboxes.groupbox_id%type;
3055: l_routing_group_id ghr_routing_groups.routing_group_id%type;
3056: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3057: l_log_text varchar2(2000);
3058:
3059: Cursor c_gpboxname is
3060: select substr(hl.description,1,30) description
3174: );
3175: commit;
3176: Else
3177: p_route_flag := TRUE;
3178: l_log_text := 'Error while updating routing_group_id to ghr_pa_requests table';
3179: ghr_par_upd.upd
3180: (p_pa_request_id => p_pa_request_id,
3181: p_object_version_number => p_object_version_number,
3182: p_routing_group_id => l_routing_group_id
3230: cp_date_Effective date
3231: ) is
3232:
3233: select *
3234: from ghr_pa_requests a
3235: where person_id = cp_person_id and
3236: effective_date > cp_date_effective and
3237: pa_notification_id is null and
3238: approval_date is not null and
3280: cp_person_id number,
3281: cp_date_Effective date
3282: ) is
3283: select *
3284: from ghr_pa_requests a
3285: where person_id = cp_person_id and
3286: effective_date = cp_date_effective and
3287: pa_notification_id is null and
3288: approval_date is not null and
3387: p_person_id in per_people_f.person_id%type,
3388: p_effective_date in date,
3389: p_from_only in boolean default FALSE,
3390: p_derive_to_cols in boolean default FALSE,
3391: p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
3392:
3393: l_agency_code ghr_pa_requests.agency_code%type;
3394: l_assignment_id NUMBER(9);
3395: l_duty_station_id NUMBER(9);
3389: p_from_only in boolean default FALSE,
3390: p_derive_to_cols in boolean default FALSE,
3391: p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
3392:
3393: l_agency_code ghr_pa_requests.agency_code%type;
3394: l_assignment_id NUMBER(9);
3395: l_duty_station_id NUMBER(9);
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3392:
3393: l_agency_code ghr_pa_requests.agency_code%type;
3394: l_assignment_id NUMBER(9);
3395: l_duty_station_id NUMBER(9);
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3393: l_agency_code ghr_pa_requests.agency_code%type;
3394: l_assignment_id NUMBER(9);
3395: l_duty_station_id NUMBER(9);
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3394: l_assignment_id NUMBER(9);
3395: l_duty_station_id NUMBER(9);
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3395: l_duty_station_id NUMBER(9);
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3396: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3404: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3397: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3404: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3405: l_position_id hr_all_positions_f.position_id%type;
3398: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3404: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3405: l_position_id hr_all_positions_f.position_id%type;
3406: l_from_position_id hr_all_positions_f.position_id%type;
3399: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3404: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3405: l_position_id hr_all_positions_f.position_id%type;
3406: l_from_position_id hr_all_positions_f.position_id%type;
3407: l_from_position_title hr_all_positions_f.name%type;
3400: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3401: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3402: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3403: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3404: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3405: l_position_id hr_all_positions_f.position_id%type;
3406: l_from_position_id hr_all_positions_f.position_id%type;
3407: l_from_position_title hr_all_positions_f.name%type;
3408: l_location_id NUMBER(15);
3477: -- p_from_location_id hr_locations.location_id%type;
3478: l_alt_pa_req_id NUMBER;
3479: l_people_data per_all_people_f%rowtype;
3480: l_result_code varchar2(30);
3481: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3482: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3478: l_alt_pa_req_id NUMBER;
3479: l_people_data per_all_people_f%rowtype;
3480: l_result_code varchar2(30);
3481: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3482: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3479: l_people_data per_all_people_f%rowtype;
3480: l_result_code varchar2(30);
3481: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3482: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3481: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3482: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3482: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3483: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3491: l_prd varchar2(30);
3484: l_session_var ghr_history_api.g_session_var_type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3491: l_prd varchar2(30);
3492: l_person_id ghr_pa_requests.person_id%type;
3485: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3486: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3487: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3491: l_prd varchar2(30);
3492: l_person_id ghr_pa_requests.person_id%type;
3493: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3488: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3491: l_prd varchar2(30);
3492: l_person_id ghr_pa_requests.person_id%type;
3493: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3494: l_pm varchar2(50);
3495:
3496: l_pos_ei_grade_data per_position_extra_info%rowtype;
3489: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3490: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3491: l_prd varchar2(30);
3492: l_person_id ghr_pa_requests.person_id%type;
3493: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3494: l_pm varchar2(50);
3495:
3496: l_pos_ei_grade_data per_position_extra_info%rowtype;
3497: l_sf52_data ghr_pa_requests%rowtype;
3493: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3494: l_pm varchar2(50);
3495:
3496: l_pos_ei_grade_data per_position_extra_info%rowtype;
3497: l_sf52_data ghr_pa_requests%rowtype;
3498:
3499:
3500:
3501: CURSOR cur_per IS
3522: noa_family_code, first_noa_code, first_noa_id,
3523: second_noa_code, second_noa_id
3524: , pay_rate_determinant
3525: /* input_pay_rate_determinant*/
3526: from ghr_pa_requests
3527: where pa_request_id = p_sf52_data.pa_request_id;
3528: -- FWFA Changes
3529: -- Bug # 1239688 --
3530: cursor get_person_type is
3558: and pdf.form_field_name = 'TO_POSITION_TITLE'
3559: and pdf.enabled_flag = 'Y'
3560: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3561: -- Bug 2112935
3562: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3563: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3564: l_orig_person_id ghr_pa_requests.person_id%type;
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3559: and pdf.enabled_flag = 'Y'
3560: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3561: -- Bug 2112935
3562: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3563: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3564: l_orig_person_id ghr_pa_requests.person_id%type;
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3560: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3561: -- Bug 2112935
3562: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3563: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3564: l_orig_person_id ghr_pa_requests.person_id%type;
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3568: l_retro_second_noa ghr_nature_of_actions.code%type;
3561: -- Bug 2112935
3562: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3563: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3564: l_orig_person_id ghr_pa_requests.person_id%type;
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3568: l_retro_second_noa ghr_nature_of_actions.code%type;
3569: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3562: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3563: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3564: l_orig_person_id ghr_pa_requests.person_id%type;
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3568: l_retro_second_noa ghr_nature_of_actions.code%type;
3569: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3570:
3565: l_orig_effective_date ghr_pa_requests.effective_date%type;
3566: l_effective_date ghr_pa_requests.effective_date%type;
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3568: l_retro_second_noa ghr_nature_of_actions.code%type;
3569: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3570:
3571: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3572: SELECT *
3573: FROM ghr_pa_requests par
3567: l_retro_first_noa ghr_nature_of_actions.code%type;
3568: l_retro_second_noa ghr_nature_of_actions.code%type;
3569: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3570:
3571: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3572: SELECT *
3573: FROM ghr_pa_requests par
3574: WHERE par.pa_request_id =
3575: (SELECT min(par1.pa_request_id)
3569: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3570:
3571: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3572: SELECT *
3573: FROM ghr_pa_requests par
3574: WHERE par.pa_request_id =
3575: (SELECT min(par1.pa_request_id)
3576: FROM ghr_pa_requests par1
3577: start with pa_request_id = c_pa_request_id
3572: SELECT *
3573: FROM ghr_pa_requests par
3574: WHERE par.pa_request_id =
3575: (SELECT min(par1.pa_request_id)
3576: FROM ghr_pa_requests par1
3577: start with pa_request_id = c_pa_request_id
3578: connect by pa_request_id = prior altered_pa_request_id);
3579:
3580: -- Procedure to get step or rate
3577: start with pa_request_id = c_pa_request_id
3578: connect by pa_request_id = prior altered_pa_request_id);
3579:
3580: -- Procedure to get step or rate
3581: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3582: p_effective_date IN ghr_pa_requests.effective_date%type,
3583: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3584: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3585:
3578: connect by pa_request_id = prior altered_pa_request_id);
3579:
3580: -- Procedure to get step or rate
3581: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3582: p_effective_date IN ghr_pa_requests.effective_date%type,
3583: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3584: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3585:
3586: CURSOR cur_ass_id(c_position_id ghr_pa_requests.to_position_id%type, c_effective_date IN ghr_pa_requests.effective_date%type) IS
3579:
3580: -- Procedure to get step or rate
3581: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3582: p_effective_date IN ghr_pa_requests.effective_date%type,
3583: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3584: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3585:
3586: CURSOR cur_ass_id(c_position_id ghr_pa_requests.to_position_id%type, c_effective_date IN ghr_pa_requests.effective_date%type) IS
3587: SELECT assignment_id, person_id
3580: -- Procedure to get step or rate
3581: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3582: p_effective_date IN ghr_pa_requests.effective_date%type,
3583: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3584: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3585:
3586: CURSOR cur_ass_id(c_position_id ghr_pa_requests.to_position_id%type, c_effective_date IN ghr_pa_requests.effective_date%type) IS
3587: SELECT assignment_id, person_id
3588: FROM per_all_assignments_f
3582: p_effective_date IN ghr_pa_requests.effective_date%type,
3583: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3584: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3585:
3586: CURSOR cur_ass_id(c_position_id ghr_pa_requests.to_position_id%type, c_effective_date IN ghr_pa_requests.effective_date%type) IS
3587: SELECT assignment_id, person_id
3588: FROM per_all_assignments_f
3589: WHERE position_id = c_position_id
3590: AND assignment_type <> 'B'
3726: ,p_work_schedule => l_dummy_varchar
3727:
3728: );
3729:
3730: l_capped_other_pay := ghr_pa_requests_pkg2.get_cop(l_assignment_id,
3731: nvl(p_effective_date, trunc(sysdate)));
3732:
3733: if (p_derive_to_cols) then
3734: p_sf52_data.to_organization_id := l_organization_id;
3781: -- p_sf52_data.from_staffing_diff_percentage := l_staffing_diff_percentage;
3782: -- p_sf52_data.from_organization_id := l_organization_id;
3783: END IF;
3784: if (l_noa_family_code = 'APP') then
3785: p_sf52_data.from_position_org_line1 := ghr_pa_requests_pkg2.get_agency_code_from(
3786: p_pa_request_id => p_sf52_data.pa_request_id,
3787: p_noa_id => l_first_noa_id);
3788: else
3789: p_sf52_data.from_position_org_line1 := l_position_org_line1;
3798: l_agency_code := ghr_api.get_position_agency_code(
3799: p_person_id => p_person_id,
3800: p_assignment_id => l_assignment_id,
3801: p_effective_date => p_effective_date);
3802: p_sf52_data.from_agency_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
3803: ,'GHR_US_AGENCY_CODE'
3804: ,l_agency_code);
3805: p_sf52_data.agency_code := l_agency_code;
3806: -- ????? is this the right from agency code?
3811: return;
3812: end if;
3813: if (l_first_noa_code = '352') then
3814: p_sf52_data.to_position_org_line1 :=
3815: ghr_pa_requests_pkg2.get_agency_code_to(
3816: p_pa_request_id => p_sf52_data.pa_request_id,
3817: p_noa_id => l_first_noa_id);
3818: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
3819: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
3817: p_noa_id => l_first_noa_id);
3818: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
3819: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
3820: p_sf52_data.to_position_org_line1 :=
3821: ghr_pa_requests_pkg2.get_agency_code_to(
3822: p_pa_request_id => p_sf52_data.pa_request_id,
3823: p_noa_id => l_second_noa_id);
3824: end if;
3825:
3891: p_sf52_data.to_pay_basis := l_pay_basis;
3892: p_sf52_data.to_pay_plan := l_pay_plan;
3893: if (l_first_noa_code = '352') then
3894: p_sf52_data.to_position_org_line1 :=
3895: ghr_pa_requests_pkg2.get_agency_code_to(
3896: p_pa_request_id => p_sf52_data.pa_request_id,
3897: p_noa_id => l_first_noa_id);
3898: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
3899: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
3897: p_noa_id => l_first_noa_id);
3898: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
3899: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
3900: p_sf52_data.to_position_org_line1 :=
3901: ghr_pa_requests_pkg2.get_agency_code_to(
3902: p_pa_request_id => p_sf52_data.pa_request_id,
3903: p_noa_id => l_second_noa_id);
3904: else
3905: p_sf52_data.to_position_org_line1 := l_position_org_line1;
3995: END IF;
3996: END IF;
3997: hr_utility.set_location('bef call to to_data PRD ' || p_sf52_data.pay_rate_determinant ,15);
3998: hr_utility.set_location('bef call to to_data pay BAsis ' || p_sf52_data.to_pay_basis ,15);
3999: ghr_pa_requests_pkg.get_sf52_to_data_elements
4000: (p_position_id => p_sf52_data.to_position_id
4001: ,p_effective_date => p_sf52_data.effective_date
4002: ,p_prd => p_sf52_data.pay_rate_determinant
4003: ,p_grade_id => l_grade_id
4050:
4051: ----Temp Promo Changes by AVR 03-JUN-2000--Start---
4052:
4053: if nvl(p_sf52_data.pay_rate_determinant,hr_api.g_varchar2) in ('A','B','E','F') AND
4054: ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4055: ghr_history_fetch.fetch_positionei(
4056: p_position_id => p_sf52_data.to_position_id,
4057: p_information_type => 'GHR_US_POS_VALID_GRADE',
4058: p_date_effective => p_sf52_data.effective_date,
4061: p_sf52_data.to_pay_basis := l_pos_ei_grade_data.poei_information6;
4062: else
4063: ----Temp Promo Changes by AVR 03-JUN-2000--End---
4064:
4065: p_sf52_data.to_pay_basis := ghr_pa_requests_pkg.get_upd34_pay_basis
4066: (p_person_id => p_person_id,
4067: p_position_id => p_sf52_data.to_position_id,
4068: p_prd => p_sf52_data.pay_rate_determinant,
4069: p_pa_request_id => p_sf52_data.pa_request_id,
4075: || p_sf52_data.to_pay_basis ,1);
4076:
4077: if (l_first_noa_code = '352') then
4078: p_sf52_data.to_position_org_line1 :=
4079: ghr_pa_requests_pkg2.get_agency_code_to(
4080: p_pa_request_id => p_sf52_data.pa_request_id,
4081: p_noa_id => l_first_noa_id);
4082: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4083: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4081: p_noa_id => l_first_noa_id);
4082: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4083: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4084: p_sf52_data.to_position_org_line1 :=
4085: ghr_pa_requests_pkg2.get_agency_code_to(
4086: p_pa_request_id => p_sf52_data.pa_request_id,
4087: p_noa_id => l_second_noa_id);
4088: else
4089: p_sf52_data.to_position_org_line1 := l_position_org_line1;
4155: end if;
4156: end if;
4157:
4158: -- If the refresh is for realignment then refresh position org. lines using the procedure
4159: -- ghr_pa_requests_pkg.get_rei_org_lines
4160: if p_sf52_data.first_noa_code = '790' or
4161: p_sf52_data.second_noa_code = '790' then
4162: hr_utility.set_location('Realignment NOA: ' || l_proc, 410);
4163: ghr_pa_requests_pkg.get_rei_org_lines(
4159: -- ghr_pa_requests_pkg.get_rei_org_lines
4160: if p_sf52_data.first_noa_code = '790' or
4161: p_sf52_data.second_noa_code = '790' then
4162: hr_utility.set_location('Realignment NOA: ' || l_proc, 410);
4163: ghr_pa_requests_pkg.get_rei_org_lines(
4164: P_PA_REQUEST_ID => p_sf52_data.pa_request_id,
4165: P_ORGANIZATION_ID => l_organization_id,
4166: P_POSITION_ORG_LINE1 => l_position_org_line1,
4167: P_POSITION_ORG_LINE2 => l_position_org_line2,
4182: hr_utility.set_location('Realignment NOA NOT Refreshed: ' || l_proc, 430);
4183: end if;
4184: end if;
4185:
4186: ghr_pa_requests_pkg.get_SF52_loc_ddf_details
4187: (p_location_id => p_sf52_data.duty_station_location_id
4188: ,p_duty_station_id => l_duty_station_id);
4189:
4190: p_sf52_data.duty_station_id := l_duty_station_id;
4188: ,p_duty_station_id => l_duty_station_id);
4189:
4190: p_sf52_data.duty_station_id := l_duty_station_id;
4191:
4192: ghr_pa_requests_pkg.get_duty_station_details
4193: (p_duty_station_id => l_duty_station_id
4194: ,p_effective_date => p_sf52_data.effective_date
4195: ,p_duty_station_code => l_duty_station_code
4196: ,p_duty_station_desc => l_duty_station_desc);
4198: -- p_sf52_data.duty_station_id := l_duty_station_id;
4199: p_sf52_data.duty_station_code := l_duty_station_code;
4200: p_sf52_data.duty_station_desc := l_duty_station_desc;
4201:
4202: ghr_pa_requests_pkg.get_SF52_pos_ddf_details
4203: (p_position_id => l_position_id
4204: ,p_date_effective => p_effective_date
4205: ,p_flsa_category => l_flsa_category
4206: ,p_bargaining_unit_status => l_bargaining_unit_status
4227: p_sf52_data.appropriation_code2 := l_appropriation_code2;
4228: p_sf52_data.personnel_office_id := l_personnel_office_id;
4229: p_sf52_data.part_time_hours := l_part_time_hours;
4230:
4231: p_sf52_data.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4232: ,'GHR_US_WORK_SCHEDULE'
4233: ,l_work_schedule);
4234: end if;
4235: hr_utility.set_location('l_work_schedule_desc is ' || p_sf52_data.work_schedule_desc, 13);
4267: p_sf52_data.employee_first_name := l_employee_first_name;
4268: p_sf52_data.employee_last_name := l_employee_last_name;
4269: p_sf52_data.employee_middle_names := l_employee_middle_names;
4270: */
4271: ghr_pa_requests_pkg.get_SF52_person_ddf_details
4272: (p_person_id => p_person_id
4273: ,p_date_effective => p_effective_date
4274: ,p_citizenship => l_citizenship
4275: ,p_veterans_preference => l_veterans_preference
4293: p_sf52_data.education_level := l_education_level;
4294: p_sf52_data.academic_discipline := l_academic_discipline;
4295: p_sf52_data.year_degree_attained := l_year_degree_attained;
4296:
4297: ghr_pa_requests_pkg.get_address_details
4298: (p_person_id => p_person_id
4299: ,p_effective_date => p_effective_date
4300: ,p_address_line1 => l_forwarding_address_line1
4301: ,p_address_line2 => l_forwarding_address_line2
4315: p_sf52_data.forwarding_country := l_forwarding_country;
4316: p_sf52_data.forwarding_country_short_name := l_forwarding_country_short_na;
4317:
4318: -- JH altered to return WS/PTH directly to p_sf52_data.
4319: ghr_pa_requests_pkg.get_SF52_asg_ddf_details
4320: (p_assignment_id => l_assignment_id
4321: ,p_date_effective => p_effective_date
4322: ,p_tenure => l_tenure
4323: ,p_annuitant_indicator => l_annuitant_indicator
4371: end if;
4372: */
4373:
4374: p_sf52_data.fegli := l_fegli ;
4375: p_sf52_data.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4376: ,'GHR_US_FEGLI'
4377: ,l_fegli);
4378:
4379: ghr_api.retrieve_element_entry_value
4390: end if;
4391: */
4392:
4393: p_sf52_data.retirement_plan := l_retirement_plan ;
4394: p_sf52_data.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4395: ,'GHR_US_RETIREMENT_PLAN'
4396: ,l_retirement_plan);
4397:
4398: end if;
4413: l_pm = 'AP' THEN
4414: ----Temp Promo Changes by AVR 03-JUN-2000--Start---
4415:
4416: if p_sf52_data.pay_rate_determinant in ('A','B','E','F') AND
4417: ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4418: ghr_history_fetch.fetch_positionei(
4419: p_position_id => p_sf52_data.to_position_id,
4420: p_information_type => 'GHR_US_POS_VALID_GRADE',
4421: p_date_effective => p_sf52_data.effective_date,
4425: else
4426: ----Temp Promo Changes by AVR 03-JUN-2000--End---
4427:
4428: p_sf52_data.to_pay_basis :=
4429: ghr_pa_requests_pkg.get_upd34_pay_basis
4430: (p_person_id => p_person_id
4431: ,p_position_id => p_sf52_data.to_position_id
4432: ,p_prd => p_sf52_data.pay_rate_determinant
4433: ,p_pa_request_id => p_sf52_data.pa_request_id
4432: ,p_prd => p_sf52_data.pay_rate_determinant
4433: ,p_pa_request_id => p_sf52_data.pa_request_id
4434: ,p_effective_date => nvl(p_sf52_data.effective_date,trunc(sysdate)));
4435: end if;
4436: IF NOT ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4437: IF p_sf52_data.from_pay_basis <> p_sf52_data.to_pay_basis THEN
4438: p_sf52_data.to_basic_pay := ghr_pay_calc.convert_amount(p_sf52_data.from_basic_pay
4439: ,p_sf52_data.from_pay_basis
4440: ,p_sf52_data.to_pay_basis);
4499: --
4500: -- {End Of Comments}
4501: -- ---------------------------------------------------------------------------
4502:
4503: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4504: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4505: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4506: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4507:
4500: -- {End Of Comments}
4501: -- ---------------------------------------------------------------------------
4502:
4503: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4504: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4505: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4506: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4507:
4508: TYPE fields_type is record
4501: -- ---------------------------------------------------------------------------
4502:
4503: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4504: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4505: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4506: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4507:
4508: TYPE fields_type is record
4509: (form_field_name ghr_pa_data_fields.form_field_name%TYPE );
4502:
4503: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4504: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4505: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4506: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4507:
4508: TYPE fields_type is record
4509: (form_field_name ghr_pa_data_fields.form_field_name%TYPE );
4510:
4510:
4511: TYPE fld_names_typ is TABLE of fields_type
4512: INDEX BY BINARY_INTEGER;
4513: l_fld_names_tab fld_names_typ;
4514: l_pa_req ghr_pa_requests%rowtype;
4515: l_column_count number := 0;
4516: l_proc varchar2(30):='get_par_ap_apue_fields';
4517:
4518: --
4928: --
4929: -- {End Of Comments}
4930: -- ---------------------------------------------------------------------------
4931:
4932: PROCEDURE derive_to_columns(p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
4933:
4934: l_proc varchar2(30):='derive_to_columns';
4935: l_basic_pay NUMBER;
4936: l_locality_adj NUMBER;
4941: l_availability_pay NUMBER;
4942: l_out_step_or_rate VARCHAR2(30);
4943: l_message_set BOOLEAN;
4944: l_calculated BOOLEAN;
4945: l_sf52_data ghr_pa_requests%rowtype;
4946: l_message VARCHAR2(2000);
4947: l_out_pay_rate_determinant NUMBER;
4948: l_out_to_grade_id NUMBER;
4949: l_out_to_pay_plan VARCHAR2(2);
5157:
5158: -- This procedure can be used for refresh before update to database and for correction.
5159: --
5160: Procedure refresh_req_shadow (
5161: p_sf52_data in out nocopy ghr_pa_requests%rowtype,
5162: p_shadow_data out nocopy ghr_pa_request_shadow%rowtype,
5163: p_process_type in varchar2 default 'CURRENT'
5164: ) is
5165:
5162: p_shadow_data out nocopy ghr_pa_request_shadow%rowtype,
5163: p_process_type in varchar2 default 'CURRENT'
5164: ) is
5165:
5166: l_sf52_shadow ghr_pa_requests%rowtype;
5167: l_shadow_data ghr_pa_request_shadow%rowtype;
5168: l_sf52_refresh_data ghr_pa_requests%rowtype;
5169: l_changed boolean := FALSE;
5170: l_proc varchar2(30):='refresh_req_shadow';
5164: ) is
5165:
5166: l_sf52_shadow ghr_pa_requests%rowtype;
5167: l_shadow_data ghr_pa_request_shadow%rowtype;
5168: l_sf52_refresh_data ghr_pa_requests%rowtype;
5169: l_changed boolean := FALSE;
5170: l_proc varchar2(30):='refresh_req_shadow';
5171: l_bef_basic_pay number;
5172: l_retention_allowance number;
5174: l_supervisory_differential number;
5175: l_staffing_differential number;
5176: l_multi_error_flag boolean;
5177: l_capped_other_pay number := hr_api.g_number;
5178: l_sf52_data ghr_pa_requests%rowtype;
5179:
5180: cursor c_sf52_shadow (c_pa_request_id in number) is
5181: select *
5182: from ghr_pa_request_shadow
5182: from ghr_pa_request_shadow
5183: where pa_request_id = c_pa_request_id ;
5184:
5185: -- Sundar 2112935 Added the variables below for Output
5186: l_sf52_out_shadow ghr_pa_requests%rowtype;
5187: l_sf52_refresh_out_data ghr_pa_requests%rowtype;
5188: Begin
5189: l_sf52_data := p_sf52_data ;--NOCOPY Changes
5190:
5183: where pa_request_id = c_pa_request_id ;
5184:
5185: -- Sundar 2112935 Added the variables below for Output
5186: l_sf52_out_shadow ghr_pa_requests%rowtype;
5187: l_sf52_refresh_out_data ghr_pa_requests%rowtype;
5188: Begin
5189: l_sf52_data := p_sf52_data ;--NOCOPY Changes
5190:
5191: print_sf52('Initial Data: ', p_sf52_data);
5447:
5448: End refresh_req_shadow ;
5449:
5450:
5451: Procedure Redo_Pay_calc ( p_sf52_rec in out nocopy ghr_pa_requests%rowtype,
5452: p_capped_other_pay in out nocopy number ) is
5453:
5454: l_pay_calc_in_rec ghr_pay_calc.pay_calc_in_rec_type;
5455: l_pay_calc_out_rec ghr_pay_calc.pay_calc_out_rec_type;
5465: l_pay_cap_message BOOLEAN;
5466: l_temp_retention_allowance NUMBER;
5467: l_session_var ghr_history_api.g_session_var_type;
5468: l_multi_error_flag boolean;
5469: l_sf52_rec ghr_pa_requests%rowtype;
5470: l_capped_other_pay Number;
5471:
5472: --Pradeep for Bug 3306515
5473: l_temp_ret_allo_percentage NUMBER;
5758:
5759: End redo_pay_calc;
5760:
5761:
5762: Procedure Update_rfrs_values( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
5763: p_shadow_data in ghr_pa_request_shadow%rowtype) is
5764: cursor get_par_ovn is
5765: select
5766: object_version_number
5763: p_shadow_data in ghr_pa_request_shadow%rowtype) is
5764: cursor get_par_ovn is
5765: select
5766: object_version_number
5767: from ghr_pa_requests
5768: where pa_request_id = p_Sf52_data.pa_request_id;
5769:
5770: cursor get_person_type is
5771: select ppt.system_person_type
5778: and ppt.person_type_id = ppf.person_type_id;
5779:
5780:
5781: l_result Boolean;
5782: l_ovn ghr_pa_requests.object_version_number%type;
5783: l_per_type per_person_types.system_person_type%type;
5784: l_sf52_data ghr_pa_requests%rowtype;
5785:
5786:
5780:
5781: l_result Boolean;
5782: l_ovn ghr_pa_requests.object_version_number%type;
5783: l_per_type per_person_types.system_person_type%type;
5784: l_sf52_data ghr_pa_requests%rowtype;
5785:
5786:
5787: Begin
5788:
6134: ,p_shadow_data.year_degree_attained
6135: );
6136: end create_shadow_row;
6137:
6138: Procedure create_shadow_row ( p_sf52_data in ghr_pa_requests%rowtype) is
6139: Begin
6140: Insert into ghr_pa_request_shadow
6141: (pa_request_id
6142: ,academic_discipline
6294: RAISE;
6295:
6296: end update_shadow_row;
6297:
6298: procedure print_sf52(p_proc VARCHAR2, p_pa_request_rec GHR_PA_REQUESTS%ROWTYPE)
6299: IS
6300: BEGIN
6301: hr_utility.set_location ('-----------------------------------------------', 10);
6302: hr_utility.set_location (p_proc, 20);