[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: */
38: );
39: */
40:
41: --6850492
42: procedure Dual_Cancel_sf52(p_sf52_data in out nocopy ghr_pa_requests%rowtype
43: ,p_first_noa_code in varchar2
44: ,p_second_noa_code in varchar2
45: ,p_pa_request_id in number
46: ,p_ovn in number
64: -- Pre-Requisities:
65: -- None.
66: --
67: -- In Parameters:
68: -- p_sf52_data -> ghr_pa_requests record of the sf52.
69: -- p_process_type -> either current action or future action.
70: -- p_validate -> flag to indicate if this is validate only mode.
71: -- p_capped_other_pay -> Capped Other Pay amount due to Update 34 changes
72: --
84: --
85: -- {End Of Comments}
86: -- ---------------------------------------------------------------------------
87: Procedure Process_SF52 (
88: p_sf52_data in out nocopy ghr_pa_requests%rowtype,
89: p_process_type in varchar2 default 'CURRENT',
90: p_validate in Boolean default FALSE,
91: p_capped_other_pay in number default NULL) is
92:
92:
93: l_noa_code varchar2(4);
94: l_noa_family_code varchar2(30);
95: l_proc varchar2(30):='process_sf52';
96: l_sf52_data ghr_pa_requests%rowtype;
97:
98: --6850492
99: l_dual_cancel varchar2(1);
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
96: l_sf52_data ghr_pa_requests%rowtype;
97:
98: --6850492
99: l_dual_cancel varchar2(1);
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
97:
98: --6850492
99: l_dual_cancel varchar2(1);
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
105: l_dual_second_noa_id ghr_pa_requests.second_noa_id%type;
98: --6850492
99: l_dual_cancel varchar2(1);
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
105: l_dual_second_noa_id ghr_pa_requests.second_noa_id%type;
106: l_dual_row_id varchar2(100);
99: l_dual_cancel varchar2(1);
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
105: l_dual_second_noa_id ghr_pa_requests.second_noa_id%type;
106: l_dual_row_id varchar2(100);
107: --6850492 /*Base Action is dual action*/
100: l_dual_first_noa_code ghr_pa_requests.first_noa_code%type;
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
105: l_dual_second_noa_id ghr_pa_requests.second_noa_id%type;
106: l_dual_row_id varchar2(100);
107: --6850492 /*Base Action is dual action*/
108:
101: l_dual_second_noa_code ghr_pa_requests.second_noa_code%type;
102: l_dual_pa_request_id ghr_pa_requests.pa_request_id%type;
103: l_dual_ovn ghr_pa_requests.object_version_number%type;
104: l_dual_first_noa_id ghr_pa_requests.first_noa_id%type;
105: l_dual_second_noa_id ghr_pa_requests.second_noa_id%type;
106: l_dual_row_id varchar2(100);
107: --6850492 /*Base Action is dual action*/
108:
109: Cursor Chk_Dual_Cancel
109: Cursor Chk_Dual_Cancel
110: is
111: select rowid,pa_request_id,first_noa_code,second_noa_code,
112: object_version_number,first_noa_id,second_noa_id
113: from ghr_pa_requests
114: where pa_request_id = p_sf52_data.altered_pa_request_id
115: and first_noa_code not in ('001','002')
116: and second_noa_code is not null
117: and pa_notification_id is not null;
117: and pa_notification_id is not null;
118: -- Bug # 8283074 removed the below validation as the
119: -- concept of breaking dual relationship is removed
120: /*and not exists (select 1
121: from ghr_pa_requests
122: where nvl(rpa_type,'-1') <> 'DUAL'
123: and mass_action_id is null
124: and first_noa_code in ('002')
125: and pa_notification_id is not null
244: -- non-correction/cancellation sf52.
245: -- Pre-Requisities:
246: -- None.
247: -- In Parameters:
248: -- p_sf52_data -> ghr_pa_requests record of the sf52.
249: --
250: -- Post Success:
251: -- The sf52 will have been processed.
252: --
257: -- Access Status:
258: -- Internal Development Use Only.
259: -- ---------------------------------------------------------------------------
260:
261: Procedure Single_Action_SF52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
262: p_process_type in varchar2 default 'CURRENT',
263: p_capped_other_pay in number default null ) is
264:
265: l_today date:=sysdate;
266: l_session_var ghr_history_api.g_session_var_type;
267: l_result varchar2(30);
268: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
269: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
270: l_sf52_shadow ghr_pa_requests%rowtype;
271: l_shadow_data ghr_pa_request_shadow%rowtype;
272: l_sf52_data ghr_pa_requests%rowtype;
273: l_proc varchar2(30):='single_action_sf52';
274: l_capped_other_pay number;
268: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
269: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
270: l_sf52_shadow ghr_pa_requests%rowtype;
271: l_shadow_data ghr_pa_request_shadow%rowtype;
272: l_sf52_data ghr_pa_requests%rowtype;
273: l_proc varchar2(30):='single_action_sf52';
274: l_capped_other_pay number;
275:
276: Begin
396: -- This procedure handles the case of a dual action.
397: -- Pre-Requisities:
398: -- None.
399: -- In Parameters:
400: -- p_sf52_data -> ghr_pa_requests record of the sf52.
401: -- Post Success:
402: -- The sf52 will have been processed.
403: -- Post Failure:
404: -- Exception will have been raised with message explaining what the problem is.
407: -- Access Status:
408: -- Internal Development Use Only.
409: -- ---------------------------------------------------------------------------
410:
411: Procedure Dual_Action_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
412: p_process_type in varchar2 default 'CURRENT') is
413:
414: l_today date:=sysdate;
415: l_sf52_data ghr_pa_requests%rowtype;
411: Procedure Dual_Action_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
412: p_process_type in varchar2 default 'CURRENT') is
413:
414: l_today date:=sysdate;
415: l_sf52_data ghr_pa_requests%rowtype;
416: l_sf52_data_save ghr_pa_requests%rowtype;
417: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
418: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
419: l_shadow_data ghr_pa_request_shadow%rowtype;
412: p_process_type in varchar2 default 'CURRENT') is
413:
414: l_today date:=sysdate;
415: l_sf52_data ghr_pa_requests%rowtype;
416: l_sf52_data_save ghr_pa_requests%rowtype;
417: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
418: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
419: l_shadow_data ghr_pa_request_shadow%rowtype;
420: l_session_var ghr_history_api.g_session_var_type;
744: -- Internal Development Use Only.
745: -- ---------------------------------------------------------------------------
746: PROCEDURE assign_new_rg (
747: p_action_num in number,
748: p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
749:
750: TYPE fields_type is record
751: (form_field_name ghr_pa_data_fields.form_field_name%TYPE,
752: process_method_code ghr_noa_fam_proc_methods.process_method_code%TYPE
756: SELECT min(pa_history_id)
757: FROM ghr_pa_history ghrpah_1
758: WHERE ghrpah_1.pa_request_id =
759: (SELECT min(pa_request_id)
760: FROM ghr_pa_requests
761: CONNECT BY pa_request_id = prior altered_pa_request_id
762: START WITH pa_request_id = cp_pa_request_id)
763: AND ghrpah_1.nature_of_action_id = cp_noa_id;
764:
777: TYPE fld_names_typ is TABLE of fields_type
778: INDEX BY BINARY_INTEGER;
779: l_fld_names_tab1 fld_names_typ;
780: l_fld_names_tab2 fld_names_typ;
781: l_pa_req ghr_pa_requests%rowtype;
782: l_column_count number := 0;
783: l_column_count1 number := 0;
784: l_column_count2 number := 0;
785: l_refresh_called boolean:= false;
783: l_column_count1 number := 0;
784: l_column_count2 number := 0;
785: l_refresh_called boolean:= false;
786: l_non_from_called boolean:= false;
787: l_non_from_pa_req ghr_pa_requests%rowtype;
788: l_correction boolean:= null;
789: l_pa_req_ref ghr_pa_requests%rowtype;
790: l_pa_req_ref2 ghr_pa_requests%rowtype;
791: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
785: l_refresh_called boolean:= false;
786: l_non_from_called boolean:= false;
787: l_non_from_pa_req ghr_pa_requests%rowtype;
788: l_correction boolean:= null;
789: l_pa_req_ref ghr_pa_requests%rowtype;
790: l_pa_req_ref2 ghr_pa_requests%rowtype;
791: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
792: tmp_varchar varchar2(150);
793:
786: l_non_from_called boolean:= false;
787: l_non_from_pa_req ghr_pa_requests%rowtype;
788: l_correction boolean:= null;
789: l_pa_req_ref ghr_pa_requests%rowtype;
790: l_pa_req_ref2 ghr_pa_requests%rowtype;
791: l_noa_family_code ghr_dual_actions.noa_family_code%type := null;
792: tmp_varchar varchar2(150);
793:
794: l_proc varchar2(30):='assign_new_rg';
842: return ret_value;
843: END;
844:
845: FUNCTION get_field_info( p_field_name IN VARCHAR2,
846: p_sf52_data IN ghr_pa_requests%rowtype) RETURN varchar2 IS
847: l_proc varchar2(30):='get_field_info';
848: l_ret_value varchar2(2000);
849: BEGIN
850: hr_utility.set_location('Entering: ' ||l_proc, 10);
2393: --
2394: -- {End Of Comments}
2395: -- ---------------------------------------------------------------------------
2396:
2397: PROCEDURE copy_2ndNoa_to_1stNoa (p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2398: l_proc varchar2(30):='copy_2ndNoa_to_1stNoa';
2399: l_pa_req ghr_pa_requests%rowtype;
2400:
2401: BEGIN
2395: -- ---------------------------------------------------------------------------
2396:
2397: PROCEDURE copy_2ndNoa_to_1stNoa (p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2398: l_proc varchar2(30):='copy_2ndNoa_to_1stNoa';
2399: l_pa_req ghr_pa_requests%rowtype;
2400:
2401: BEGIN
2402: l_pa_req :=p_pa_req ; ---NOCOPY Changes
2403:
2467: --
2468: -- {End Of Comments}
2469: -- ---------------------------------------------------------------------------
2470:
2471: PROCEDURE null_2ndNoa_cols(p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2472: l_proc varchar2(30):='null_2ndNoa_cols';
2473: l_pa_req ghr_pa_requests%rowtype;
2474:
2475: BEGIN
2469: -- ---------------------------------------------------------------------------
2470:
2471: PROCEDURE null_2ndNoa_cols(p_pa_req in out nocopy ghr_pa_requests%rowtype) IS
2472: l_proc varchar2(30):='null_2ndNoa_cols';
2473: l_pa_req ghr_pa_requests%rowtype;
2474:
2475: BEGIN
2476: l_pa_req :=p_pa_req ; ---NOCOPY Changes
2477:
2618:
2619: l_log_text varchar2(2000);
2620: l_error varchar2(512); --512
2621: l_req varchar2(10);
2622: l_sf52_rec ghr_pa_requests%rowtype;
2623: l_result varchar2(30);
2624:
2625: l_retcode Number;
2626: l_calculated Boolean;
2638: ----If p_poi is passed then use the following modified SQL.
2639:
2640: cursor c_par_pend_per_poi is
2641: select person_id,effective_date
2642: from ghr_pa_requests a, ghr_pa_routing_history b
2643: where effective_date <= sysdate
2644: and pa_notification_id is null
2645: and approval_date is not null
2646: and a.pa_request_id = b.pa_request_id
2666:
2667: cursor c_par_pend_per is
2668: ---------Added as part of fix for the bug 2180343
2669: select person_id,effective_date
2670: from ghr_pa_requests a, ghr_pa_routing_history b
2671: where effective_date <= sysdate
2672: and pa_notification_id is null
2673: and approval_date is not null
2674: and a.pa_request_id = b.pa_request_id
2686: order by 2,1;
2687:
2688: /*********** Commented out as part of fix 2180343 ***** AVR
2689: select distinct person_id,effective_date
2690: from ghr_pa_requests a
2691: where effective_date <= sysdate and
2692: pa_notification_id is null and
2693: approval_date is not null and
2694: exists (select 'exists'
2700: order by 2,1;
2701:
2702: cursor c_get_par_id is
2703: select pa_request_id ,noa.order_of_processing
2704: from ghr_pa_requests a, ghr_nature_of_actions noa
2705: where person_id = l_person_id
2706: and effective_date = l_effective_date
2707: and pa_notification_id is null
2708: and approval_date is not null
2716: order by 2 asc;
2717:
2718:
2719: Cursor get_req is
2720: select * from ghr_pa_requests
2721: where pa_request_id = l_pa_request_id;
2722: ***********/
2723:
2724:
2733: ---Local Procedure.
2734: PROCEDURE sub_proc_futr_sf52 is
2735: cursor c_get_par_id is
2736: select pa_request_id ,noa.order_of_processing
2737: from ghr_pa_requests a, ghr_nature_of_actions noa
2738: where person_id = l_person_id
2739: and effective_date = l_effective_date
2740: and pa_notification_id is null
2741: and approval_date is not null
2749: order by 2 asc;
2750:
2751:
2752: Cursor get_req is
2753: select * from ghr_pa_requests
2754: where pa_request_id = l_pa_request_id;
2755:
2756: l_rec get_req%rowtype;
2757:
2756: l_rec get_req%rowtype;
2757:
2758: -- Start of Bug 3602261
2759:
2760: l_object_version_number ghr_pa_requests.object_version_number%type;
2761:
2762: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2763: select par.object_version_number
2764: from ghr_pa_requests par
2758: -- Start of Bug 3602261
2759:
2760: l_object_version_number ghr_pa_requests.object_version_number%type;
2761:
2762: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2763: select par.object_version_number
2764: from ghr_pa_requests par
2765: where par.pa_request_id = p_pa_request_id; -- 3769917
2766:
2760: l_object_version_number ghr_pa_requests.object_version_number%type;
2761:
2762: Cursor c_ovn (p_pa_request_id ghr_pa_requests.pa_request_id%type) is -- 3769917
2763: select par.object_version_number
2764: from ghr_pa_requests par
2765: where par.pa_request_id = p_pa_request_id; -- 3769917
2766:
2767: -- End of Bug 3602261
2768: --Bug # 9329643
2780: hr_utility.set_location('l_rec ' || l_rec.first_noa_code,3);
2781:
2782: l_sf52_rec := l_rec;
2783: --Bug # 9329643
2784: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
2785: (p_person_id => l_sf52_rec.person_id,
2786: p_effective_date => l_sf52_rec.effective_date);
2787:
2788: Begin
3059:
3060: --
3061: --
3062: Procedure Route_Errorerd_SF52(
3063: p_sf52 in out nocopy ghr_pa_requests%rowtype,
3064: p_error in varchar2,
3065: p_result out nocopy varchar2) is
3066:
3067: l_u_prh_object_version_number number;
3071: l_log_text varchar2(2000);
3072: -- l_prog_name varchar2(30):='GHR Process Future SF52';
3073: l_proc varchar2(30):='Route_Errerd_SF52';
3074: l_new_line varchar2(1) := substr('',1,1);
3075: l_sf52 ghr_pa_requests%rowtype ;
3076: --Bug # 9329643
3077: l_employee_number per_people_f.employee_number%type;
3078:
3079: Begin
3089: */
3090: hr_utility.set_location( l_proc, 20);
3091:
3092: --Bug # 9329643
3093: l_employee_number := ghr_pa_requests_pkg2.get_employee_number
3094: (p_person_id => l_sf52.person_id,
3095: p_effective_date => l_sf52.effective_date);
3096: l_log_text := 'Request Number : ' || p_sf52.request_number || l_new_line ||
3097: 'PA_REQUEST_ID : ' || to_char(p_sf52.pa_request_id) ||
3133:
3134: /*--Added by Rohini
3135:
3136: Procedure fetch_update_routing_details
3137: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3138: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3139: p_position_id in ghr_pa_requests.to_position_id%type,
3140: p_effective_date in ghr_pa_requests.effective_date%type,
3141: p_retcode out nocopy number,
3134: /*--Added by Rohini
3135:
3136: Procedure fetch_update_routing_details
3137: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3138: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3139: p_position_id in ghr_pa_requests.to_position_id%type,
3140: p_effective_date in ghr_pa_requests.effective_date%type,
3141: p_retcode out nocopy number,
3142: p_route_flag out nocopy boolean
3135:
3136: Procedure fetch_update_routing_details
3137: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3138: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3139: p_position_id in ghr_pa_requests.to_position_id%type,
3140: p_effective_date in ghr_pa_requests.effective_date%type,
3141: p_retcode out nocopy number,
3142: p_route_flag out nocopy boolean
3143: )
3136: Procedure fetch_update_routing_details
3137: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
3138: p_object_version_number in out ghr_pa_requests.object_version_number%type,
3139: p_position_id in ghr_pa_requests.to_position_id%type,
3140: p_effective_date in ghr_pa_requests.effective_date%type,
3141: p_retcode out nocopy number,
3142: p_route_flag out nocopy boolean
3143: )
3144:
3144:
3145: is
3146:
3147: l_groupbox_id ghr_groupboxes.groupbox_id%type;
3148: l_routing_group_id ghr_pa_requests.routing_group_id%type;
3149: l_proc varchar2(72) := ' ghr_process_sf52.' || 'update_routing_details';
3150: l_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%type;
3151: l_pa_object_version_number ghr_pa_routing_history.object_version_number%type;
3152: l_user_name varchar2(30);
3178: l_pos_ei_data per_position_extra_info%type;
3179: l_groupbox_name ghr_groupboxes.name%type;
3180: l_groupbox_id ghr_groupboxes.groupbox_id%type;
3181: l_routing_group_id ghr_routing_groups.routing_group_id%type;
3182: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3183: l_log_text varchar2(2000);
3184:
3185: Cursor c_gpboxname is
3186: select substr(hl.description,1,30) description
3300: );
3301: commit;
3302: Else
3303: p_route_flag := TRUE;
3304: l_log_text := 'Error while updating routing_group_id to ghr_pa_requests table';
3305: ghr_par_upd.upd
3306: (p_pa_request_id => p_pa_request_id,
3307: p_object_version_number => p_object_version_number,
3308: p_routing_group_id => l_routing_group_id
3356: cp_date_Effective date
3357: ) is
3358:
3359: select *
3360: from ghr_pa_requests a
3361: where person_id = cp_person_id and
3362: effective_date > cp_date_effective and
3363: pa_notification_id is null and
3364: approval_date is not null and
3406: cp_person_id number,
3407: cp_date_Effective date
3408: ) is
3409: select *
3410: from ghr_pa_requests a
3411: where person_id = cp_person_id and
3412: effective_date = cp_date_effective and
3413: pa_notification_id is null and
3414: approval_date is not null and
3513: p_person_id in per_people_f.person_id%type,
3514: p_effective_date in date,
3515: p_from_only in boolean default FALSE,
3516: p_derive_to_cols in boolean default FALSE,
3517: p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
3518:
3519: l_agency_code ghr_pa_requests.agency_code%type;
3520: l_assignment_id NUMBER(9);
3521: l_duty_station_id NUMBER(9);
3515: p_from_only in boolean default FALSE,
3516: p_derive_to_cols in boolean default FALSE,
3517: p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
3518:
3519: l_agency_code ghr_pa_requests.agency_code%type;
3520: l_assignment_id NUMBER(9);
3521: l_duty_station_id NUMBER(9);
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3518:
3519: l_agency_code ghr_pa_requests.agency_code%type;
3520: l_assignment_id NUMBER(9);
3521: l_duty_station_id NUMBER(9);
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3519: l_agency_code ghr_pa_requests.agency_code%type;
3520: l_assignment_id NUMBER(9);
3521: l_duty_station_id NUMBER(9);
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3520: l_assignment_id NUMBER(9);
3521: l_duty_station_id NUMBER(9);
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3521: l_duty_station_id NUMBER(9);
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3522: l_duty_station_desc ghr_pa_requests.duty_station_desc%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3530: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3523: l_duty_station_code ghr_pa_requests.duty_station_code%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3530: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3531: l_position_id hr_all_positions_f.position_id%type;
3524: l_national_identifier ghr_pa_requests.employee_national_identifier%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3530: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3531: l_position_id hr_all_positions_f.position_id%type;
3532: l_from_position_id hr_all_positions_f.position_id%type;
3525: l_date_of_birth ghr_pa_requests.employee_date_of_birth%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3530: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3531: l_position_id hr_all_positions_f.position_id%type;
3532: l_from_position_id hr_all_positions_f.position_id%type;
3533: l_from_position_title hr_all_positions_f.name%type;
3526: l_employee_last_name ghr_pa_requests.employee_last_name%type;
3527: l_employee_first_name ghr_pa_requests.employee_first_name%type;
3528: l_employee_middle_names ghr_pa_requests.employee_middle_names%type;
3529: l_personnel_office_id ghr_pa_requests.personnel_office_id%type;
3530: l_office_symbol ghr_pa_requests.from_office_symbol%type;
3531: l_position_id hr_all_positions_f.position_id%type;
3532: l_from_position_id hr_all_positions_f.position_id%type;
3533: l_from_position_title hr_all_positions_f.name%type;
3534: l_location_id NUMBER(15);
3605: -- p_from_location_id hr_locations.location_id%type;
3606: l_alt_pa_req_id NUMBER;
3607: l_people_data per_all_people_f%rowtype;
3608: l_result_code varchar2(30);
3609: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3610: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3606: l_alt_pa_req_id NUMBER;
3607: l_people_data per_all_people_f%rowtype;
3608: l_result_code varchar2(30);
3609: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3610: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3607: l_people_data per_all_people_f%rowtype;
3608: l_result_code varchar2(30);
3609: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3610: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3609: l_retention_allow_percentage ghr_pa_requests.to_retention_allow_percentage%type;
3610: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3616: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3610: l_supervisory_diff_percentage ghr_pa_requests.to_supervisory_diff_percentage%type;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3616: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3611: l_staffing_diff_percentage ghr_pa_requests.to_staffing_diff_percentage%type;
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3616: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3619: l_prd varchar2(30);
3612: l_session_var ghr_history_api.g_session_var_type;
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3616: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3619: l_prd varchar2(30);
3620: l_prd1 varchar2(30);
3613: l_noa_family_code ghr_pa_requests.noa_family_code%type;
3614: l_first_noa_id ghr_pa_requests.first_noa_id%type;
3615: l_first_noa_code ghr_pa_requests.first_noa_code%type;
3616: l_second_noa_id ghr_pa_requests.second_noa_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3619: l_prd varchar2(30);
3620: l_prd1 varchar2(30);
3621: l_person_id ghr_pa_requests.person_id%type;
3617: l_second_noa_code ghr_pa_requests.second_noa_code%type;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3619: l_prd varchar2(30);
3620: l_prd1 varchar2(30);
3621: l_person_id ghr_pa_requests.person_id%type;
3622: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3623: l_pm varchar2(50);
3624:
3625: l_pos_ei_grade_data per_position_extra_info%rowtype;
3618: l_per_type per_person_types.system_person_type%type := hr_api.g_varchar2;
3619: l_prd varchar2(30);
3620: l_prd1 varchar2(30);
3621: l_person_id ghr_pa_requests.person_id%type;
3622: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3623: l_pm varchar2(50);
3624:
3625: l_pos_ei_grade_data per_position_extra_info%rowtype;
3626: l_sf52_data ghr_pa_requests%rowtype;
3622: l_temp_to_position_id ghr_pa_requests.to_position_id%type;
3623: l_pm varchar2(50);
3624:
3625: l_pos_ei_grade_data per_position_extra_info%rowtype;
3626: l_sf52_data ghr_pa_requests%rowtype;
3627:
3628:
3629:
3630: CURSOR cur_per IS
3652: noa_family_code, first_noa_code, first_noa_id,
3653: second_noa_code, second_noa_id
3654: , pay_rate_determinant
3655: /* input_pay_rate_determinant*/
3656: from ghr_pa_requests
3657: where pa_request_id = nvl(p_sf52_data.pa_request_id,l_session_var.pa_request_id);
3658: -- FWFA Changes
3659: -- Bug # 1239688 --
3660: cursor get_person_type is
3688: and pdf.form_field_name = 'TO_POSITION_TITLE'
3689: and pdf.enabled_flag = 'Y'
3690: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3691: -- Bug 2112935
3692: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3693: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3694: l_orig_person_id ghr_pa_requests.person_id%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3689: and pdf.enabled_flag = 'Y'
3690: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3691: -- Bug 2112935
3692: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3693: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3694: l_orig_person_id ghr_pa_requests.person_id%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3690: and p_effective_date between nvl(pdf.date_from,p_effective_date) and nvl(pdf.date_to,p_effective_date);
3691: -- Bug 2112935
3692: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3693: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3694: l_orig_person_id ghr_pa_requests.person_id%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3691: -- Bug 2112935
3692: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3693: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3694: l_orig_person_id ghr_pa_requests.person_id%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3692: l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
3693: l_orig_pa_notification_id ghr_pa_requests.pa_notification_id%type;
3694: l_orig_person_id ghr_pa_requests.person_id%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3700: l_orig_first_noa ghr_pa_requests.first_noa_code%type;
3695: l_orig_effective_date ghr_pa_requests.effective_date%type;
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3700: l_orig_first_noa ghr_pa_requests.first_noa_code%type;
3701: l_orig_second_noa ghr_pa_requests.second_noa_code%type;
3702:
3703: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3696: l_effective_date ghr_pa_requests.effective_date%type;
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3700: l_orig_first_noa ghr_pa_requests.first_noa_code%type;
3701: l_orig_second_noa ghr_pa_requests.second_noa_code%type;
3702:
3703: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3704: SELECT *
3697: l_retro_first_noa ghr_nature_of_actions.code%type;
3698: l_retro_second_noa ghr_nature_of_actions.code%type;
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3700: l_orig_first_noa ghr_pa_requests.first_noa_code%type;
3701: l_orig_second_noa ghr_pa_requests.second_noa_code%type;
3702:
3703: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3704: SELECT *
3705: FROM ghr_pa_requests par
3699: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3700: l_orig_first_noa ghr_pa_requests.first_noa_code%type;
3701: l_orig_second_noa ghr_pa_requests.second_noa_code%type;
3702:
3703: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3704: SELECT *
3705: FROM ghr_pa_requests par
3706: WHERE par.pa_request_id =
3707: (SELECT min(par1.pa_request_id)
3701: l_orig_second_noa ghr_pa_requests.second_noa_code%type;
3702:
3703: CURSOR c_orig_rec(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
3704: SELECT *
3705: FROM ghr_pa_requests par
3706: WHERE par.pa_request_id =
3707: (SELECT min(par1.pa_request_id)
3708: FROM ghr_pa_requests par1
3709: start with pa_request_id = c_pa_request_id
3704: SELECT *
3705: FROM ghr_pa_requests par
3706: WHERE par.pa_request_id =
3707: (SELECT min(par1.pa_request_id)
3708: FROM ghr_pa_requests par1
3709: start with pa_request_id = c_pa_request_id
3710: connect by pa_request_id = prior altered_pa_request_id);
3711:
3712: -- 8303159 Dual Actions
3712: -- 8303159 Dual Actions
3713: cursor get_740_prd
3714: is
3715: select pay_rate_determinant
3716: from ghr_pa_requests
3717: where pa_request_id = (select max(pa_request_id)
3718: from ghr_pa_requests
3719: where pa_notification_id is not null
3720: and (first_noa_code = '740' or second_noa_code = '740')
3714: is
3715: select pay_rate_determinant
3716: from ghr_pa_requests
3717: where pa_request_id = (select max(pa_request_id)
3718: from ghr_pa_requests
3719: where pa_notification_id is not null
3720: and (first_noa_code = '740' or second_noa_code = '740')
3721: start with pa_request_id = (select mass_action_id
3722: from ghr_pa_requests
3718: from ghr_pa_requests
3719: where pa_notification_id is not null
3720: and (first_noa_code = '740' or second_noa_code = '740')
3721: start with pa_request_id = (select mass_action_id
3722: from ghr_pa_requests
3723: where pa_request_id = l_session_var.pa_request_id)
3724: connect by pa_request_id = prior altered_pa_request_id);
3725:
3726: cursor chk_sec_corr
3725:
3726: cursor chk_sec_corr
3727: is
3728: select 1
3729: from ghr_pa_requests
3730: where pa_request_id = (SELECT min(par1.pa_request_id)
3731: FROM ghr_pa_requests par1
3732: start with pa_request_id = l_session_var.altered_pa_request_id
3733: connect by pa_request_id = prior altered_pa_request_id)
3727: is
3728: select 1
3729: from ghr_pa_requests
3730: where pa_request_id = (SELECT min(par1.pa_request_id)
3731: FROM ghr_pa_requests par1
3732: start with pa_request_id = l_session_var.altered_pa_request_id
3733: connect by pa_request_id = prior altered_pa_request_id)
3734: and second_noa_id =l_session_var.noa_id_correct;
3735: -- 8303159 Dual Actions
3758: l_prd_pm varchar2(50);
3759: --End Bug# 14047036, 13587903
3760:
3761: -- Procedure to get step or rate
3762: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3763: p_effective_date IN ghr_pa_requests.effective_date%type,
3764: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3765: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3766:
3759: --End Bug# 14047036, 13587903
3760:
3761: -- Procedure to get step or rate
3762: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3763: p_effective_date IN ghr_pa_requests.effective_date%type,
3764: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3765: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3766:
3767: 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
3760:
3761: -- Procedure to get step or rate
3762: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3763: p_effective_date IN ghr_pa_requests.effective_date%type,
3764: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3765: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3766:
3767: 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
3768: SELECT assignment_id, person_id
3761: -- Procedure to get step or rate
3762: PROCEDURE get_asg_step_or_rate(p_position_id IN ghr_pa_requests.to_position_id%type,
3763: p_effective_date IN ghr_pa_requests.effective_date%type,
3764: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3765: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3766:
3767: 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
3768: SELECT assignment_id, person_id
3769: FROM per_all_assignments_f
3763: p_effective_date IN ghr_pa_requests.effective_date%type,
3764: p_step_or_rate OUT NOCOPY ghr_pa_requests.to_step_or_rate%type,
3765: p_prd OUT NOCOPY ghr_pa_requests.pay_rate_determinant%type ) IS
3766:
3767: 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
3768: SELECT assignment_id, person_id
3769: FROM per_all_assignments_f
3770: WHERE position_id = c_position_id
3771: AND assignment_type <> 'B'
3908: ,p_work_schedule => l_dummy_varchar
3909:
3910: );
3911:
3912: l_capped_other_pay := ghr_pa_requests_pkg2.get_cop(l_assignment_id,
3913: nvl(p_effective_date, trunc(sysdate)));
3914:
3915: if (p_derive_to_cols) then
3916: p_sf52_data.to_organization_id := l_organization_id;
3963: -- p_sf52_data.from_staffing_diff_percentage := l_staffing_diff_percentage;
3964: -- p_sf52_data.from_organization_id := l_organization_id;
3965: END IF;
3966: if (l_noa_family_code = 'APP') then
3967: p_sf52_data.from_position_org_line1 := ghr_pa_requests_pkg2.get_agency_code_from(
3968: p_pa_request_id => p_sf52_data.pa_request_id,
3969: p_noa_id => l_first_noa_id);
3970: else
3971: p_sf52_data.from_position_org_line1 := l_position_org_line1;
3980: l_agency_code := ghr_api.get_position_agency_code(
3981: p_person_id => p_person_id,
3982: p_assignment_id => l_assignment_id,
3983: p_effective_date => p_effective_date);
3984: p_sf52_data.from_agency_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
3985: ,'GHR_US_AGENCY_CODE'
3986: ,l_agency_code);
3987: p_sf52_data.agency_code := l_agency_code;
3988: -- ????? is this the right from agency code?
3993: return;
3994: end if;
3995: if (l_first_noa_code = '352') then
3996: p_sf52_data.to_position_org_line1 :=
3997: ghr_pa_requests_pkg2.get_agency_code_to(
3998: p_pa_request_id => p_sf52_data.pa_request_id,
3999: p_noa_id => l_first_noa_id);
4000: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4001: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
3999: p_noa_id => l_first_noa_id);
4000: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4001: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4002: p_sf52_data.to_position_org_line1 :=
4003: ghr_pa_requests_pkg2.get_agency_code_to(
4004: p_pa_request_id => p_sf52_data.pa_request_id,
4005: p_noa_id => l_second_noa_id);
4006: end if;
4007:
4073: p_sf52_data.to_pay_basis := l_pay_basis;
4074: p_sf52_data.to_pay_plan := l_pay_plan;
4075: if (l_first_noa_code = '352') then
4076: p_sf52_data.to_position_org_line1 :=
4077: ghr_pa_requests_pkg2.get_agency_code_to(
4078: p_pa_request_id => p_sf52_data.pa_request_id,
4079: p_noa_id => l_first_noa_id);
4080: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4081: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4079: p_noa_id => l_first_noa_id);
4080: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4081: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4082: p_sf52_data.to_position_org_line1 :=
4083: ghr_pa_requests_pkg2.get_agency_code_to(
4084: p_pa_request_id => p_sf52_data.pa_request_id,
4085: p_noa_id => l_second_noa_id);
4086: else
4087: p_sf52_data.to_position_org_line1 := l_position_org_line1;
4230: --6850492
4231: END IF;
4232: hr_utility.set_location('bef call to to_data PRD ' || p_sf52_data.pay_rate_determinant ,15);
4233: hr_utility.set_location('bef call to to_data pay BAsis ' || p_sf52_data.to_pay_basis ,15);
4234: ghr_pa_requests_pkg.get_sf52_to_data_elements
4235: (p_position_id => p_sf52_data.to_position_id
4236: ,p_effective_date => p_sf52_data.effective_date
4237: ,p_prd => p_sf52_data.pay_rate_determinant
4238: ,p_grade_id => l_grade_id
4300:
4301: ----Temp Promo Changes by AVR 03-JUN-2000--Start---
4302:
4303: if nvl(p_sf52_data.pay_rate_determinant,hr_api.g_varchar2) in ('A','B','E','F') AND
4304: ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4305: ghr_history_fetch.fetch_positionei(
4306: p_position_id => p_sf52_data.to_position_id,
4307: p_information_type => 'GHR_US_POS_VALID_GRADE',
4308: p_date_effective => p_sf52_data.effective_date,
4311: p_sf52_data.to_pay_basis := l_pos_ei_grade_data.poei_information6;
4312: else
4313: ----Temp Promo Changes by AVR 03-JUN-2000--End---
4314:
4315: p_sf52_data.to_pay_basis := ghr_pa_requests_pkg.get_upd34_pay_basis
4316: (p_person_id => p_person_id,
4317: p_position_id => p_sf52_data.to_position_id,
4318: p_prd => p_sf52_data.pay_rate_determinant,
4319: p_pa_request_id => p_sf52_data.pa_request_id,
4325: || p_sf52_data.to_pay_basis ,1);
4326:
4327: if (l_first_noa_code = '352') then
4328: p_sf52_data.to_position_org_line1 :=
4329: ghr_pa_requests_pkg2.get_agency_code_to(
4330: p_pa_request_id => p_sf52_data.pa_request_id,
4331: p_noa_id => l_first_noa_id);
4332: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4333: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4331: p_noa_id => l_first_noa_id);
4332: -- Added by ENUNEZ (23-FEB-2000 bug# 756335)
4333: elsif (l_first_noa_code = '002' and l_second_noa_code = '352') then
4334: p_sf52_data.to_position_org_line1 :=
4335: ghr_pa_requests_pkg2.get_agency_code_to(
4336: p_pa_request_id => p_sf52_data.pa_request_id,
4337: p_noa_id => l_second_noa_id);
4338: else
4339: p_sf52_data.to_position_org_line1 := l_position_org_line1;
4404:
4405: end if;
4406: end if;
4407: -- If the refresh is for realignment then refresh position org. lines using the procedure
4408: -- ghr_pa_requests_pkg.get_rei_org_lines
4409: if p_sf52_data.first_noa_code = '790' or
4410: p_sf52_data.second_noa_code = '790' then
4411: hr_utility.set_location('Realignment NOA: ' || l_proc, 410);
4412: ghr_pa_requests_pkg.get_rei_org_lines(
4408: -- ghr_pa_requests_pkg.get_rei_org_lines
4409: if p_sf52_data.first_noa_code = '790' or
4410: p_sf52_data.second_noa_code = '790' then
4411: hr_utility.set_location('Realignment NOA: ' || l_proc, 410);
4412: ghr_pa_requests_pkg.get_rei_org_lines(
4413: P_PA_REQUEST_ID => p_sf52_data.pa_request_id,
4414: P_ORGANIZATION_ID => l_organization_id,
4415: P_POSITION_ORG_LINE1 => l_position_org_line1,
4416: P_POSITION_ORG_LINE2 => l_position_org_line2,
4431: hr_utility.set_location('Realignment NOA NOT Refreshed: ' || l_proc, 430);
4432: end if;
4433: end if;
4434:
4435: ghr_pa_requests_pkg.get_SF52_loc_ddf_details
4436: (p_location_id => p_sf52_data.duty_station_location_id
4437: ,p_duty_station_id => l_duty_station_id);
4438:
4439: p_sf52_data.duty_station_id := l_duty_station_id;
4437: ,p_duty_station_id => l_duty_station_id);
4438:
4439: p_sf52_data.duty_station_id := l_duty_station_id;
4440:
4441: ghr_pa_requests_pkg.get_duty_station_details
4442: (p_duty_station_id => l_duty_station_id
4443: ,p_effective_date => p_sf52_data.effective_date
4444: ,p_duty_station_code => l_duty_station_code
4445: ,p_duty_station_desc => l_duty_station_desc);
4447: -- p_sf52_data.duty_station_id := l_duty_station_id;
4448: p_sf52_data.duty_station_code := l_duty_station_code;
4449: p_sf52_data.duty_station_desc := l_duty_station_desc;
4450:
4451: ghr_pa_requests_pkg.get_SF52_pos_ddf_details
4452: (p_position_id => l_position_id
4453: ,p_date_effective => p_effective_date
4454: ,p_flsa_category => l_flsa_category
4455: ,p_bargaining_unit_status => l_bargaining_unit_status
4476: p_sf52_data.appropriation_code2 := l_appropriation_code2;
4477: p_sf52_data.personnel_office_id := l_personnel_office_id;
4478: p_sf52_data.part_time_hours := l_part_time_hours;
4479:
4480: p_sf52_data.work_schedule_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4481: ,'GHR_US_WORK_SCHEDULE'
4482: ,l_work_schedule);
4483: end if;
4484: hr_utility.set_location('l_work_schedule_desc is ' || p_sf52_data.work_schedule_desc, 13);
4515: p_sf52_data.employee_first_name := l_employee_first_name;
4516: p_sf52_data.employee_last_name := l_employee_last_name;
4517: p_sf52_data.employee_middle_names := l_employee_middle_names;
4518: */
4519: ghr_pa_requests_pkg.get_SF52_person_ddf_details
4520: (p_person_id => p_person_id
4521: ,p_date_effective => p_effective_date
4522: ,p_citizenship => l_citizenship
4523: ,p_veterans_preference => l_veterans_preference
4541: p_sf52_data.education_level := l_education_level;
4542: p_sf52_data.academic_discipline := l_academic_discipline;
4543: p_sf52_data.year_degree_attained := l_year_degree_attained;
4544:
4545: ghr_pa_requests_pkg.get_address_details
4546: (p_person_id => p_person_id
4547: ,p_effective_date => p_effective_date
4548: ,p_address_line1 => l_forwarding_address_line1
4549: ,p_address_line2 => l_forwarding_address_line2
4563: p_sf52_data.forwarding_country := l_forwarding_country;
4564: p_sf52_data.forwarding_country_short_name := l_forwarding_country_short_na;
4565:
4566: -- JH altered to return WS/PTH directly to p_sf52_data.
4567: ghr_pa_requests_pkg.get_SF52_asg_ddf_details
4568: (p_assignment_id => l_assignment_id
4569: ,p_date_effective => p_effective_date
4570: ,p_tenure => l_tenure
4571: ,p_annuitant_indicator => l_annuitant_indicator
4619: end if;
4620: */
4621:
4622: p_sf52_data.fegli := l_fegli ;
4623: p_sf52_data.fegli_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4624: ,'GHR_US_FEGLI'
4625: ,l_fegli);
4626:
4627: ghr_api.retrieve_element_entry_value
4638: end if;
4639: */
4640:
4641: p_sf52_data.retirement_plan := l_retirement_plan ;
4642: p_sf52_data.retirement_plan_desc := ghr_pa_requests_pkg.get_lookup_meaning(800
4643: ,'GHR_US_RETIREMENT_PLAN'
4644: ,l_retirement_plan);
4645:
4646: end if;
4661: l_pm = 'AP' THEN
4662: ----Temp Promo Changes by AVR 03-JUN-2000--Start---
4663:
4664: if p_sf52_data.pay_rate_determinant in ('A','B','E','F') AND
4665: ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4666: ghr_history_fetch.fetch_positionei(
4667: p_position_id => p_sf52_data.to_position_id,
4668: p_information_type => 'GHR_US_POS_VALID_GRADE',
4669: p_date_effective => p_sf52_data.effective_date,
4673: else
4674: ----Temp Promo Changes by AVR 03-JUN-2000--End---
4675:
4676: p_sf52_data.to_pay_basis :=
4677: ghr_pa_requests_pkg.get_upd34_pay_basis
4678: (p_person_id => p_person_id
4679: ,p_position_id => p_sf52_data.to_position_id
4680: ,p_prd => p_sf52_data.pay_rate_determinant
4681: ,p_pa_request_id => p_sf52_data.pa_request_id
4680: ,p_prd => p_sf52_data.pay_rate_determinant
4681: ,p_pa_request_id => p_sf52_data.pa_request_id
4682: ,p_effective_date => nvl(p_sf52_data.effective_date,trunc(sysdate)));
4683: end if;
4684: IF NOT ghr_pa_requests_pkg.temp_step_true(p_sf52_data.pa_request_id) THEN
4685: IF p_sf52_data.from_pay_basis <> p_sf52_data.to_pay_basis THEN
4686: p_sf52_data.to_basic_pay := ghr_pay_calc.convert_amount(p_sf52_data.from_basic_pay
4687: ,p_sf52_data.from_pay_basis
4688: ,p_sf52_data.to_pay_basis);
4747: --
4748: -- {End Of Comments}
4749: -- ---------------------------------------------------------------------------
4750:
4751: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4752: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4753: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4754: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4755:
4748: -- {End Of Comments}
4749: -- ---------------------------------------------------------------------------
4750:
4751: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4752: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4753: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4754: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4755:
4756: TYPE fields_type is record
4749: -- ---------------------------------------------------------------------------
4750:
4751: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4752: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4753: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4754: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4755:
4756: TYPE fields_type is record
4757: (form_field_name ghr_pa_data_fields.form_field_name%TYPE );
4750:
4751: PROCEDURE get_par_ap_apue_fields (p_pa_req_in in ghr_pa_requests%rowtype,
4752: p_first_noa_id in ghr_pa_requests.first_noa_id%type,
4753: p_second_noa_id in ghr_pa_requests.second_noa_id%type,
4754: p_pa_req_out out nocopy ghr_pa_requests%rowtype) IS
4755:
4756: TYPE fields_type is record
4757: (form_field_name ghr_pa_data_fields.form_field_name%TYPE );
4758:
4758:
4759: TYPE fld_names_typ is TABLE of fields_type
4760: INDEX BY BINARY_INTEGER;
4761: l_fld_names_tab fld_names_typ;
4762: l_pa_req ghr_pa_requests%rowtype;
4763: l_column_count number := 0;
4764: l_proc varchar2(30):='get_par_ap_apue_fields';
4765:
4766: --
5176: --
5177: -- {End Of Comments}
5178: -- ---------------------------------------------------------------------------
5179:
5180: PROCEDURE derive_to_columns(p_sf52_data in out nocopy ghr_pa_requests%rowtype) IS
5181:
5182: l_proc varchar2(30):='derive_to_columns';
5183: l_basic_pay NUMBER;
5184: l_locality_adj NUMBER;
5189: l_availability_pay NUMBER;
5190: l_out_step_or_rate VARCHAR2(30);
5191: l_message_set BOOLEAN;
5192: l_calculated BOOLEAN;
5193: l_sf52_data ghr_pa_requests%rowtype;
5194: l_message VARCHAR2(2000);
5195: l_out_pay_rate_determinant NUMBER;
5196: l_out_to_grade_id NUMBER;
5197: l_out_to_pay_plan VARCHAR2(2);
5405:
5406: -- This procedure can be used for refresh before update to database and for correction.
5407: --
5408: Procedure refresh_req_shadow (
5409: p_sf52_data in out nocopy ghr_pa_requests%rowtype,
5410: p_shadow_data out nocopy ghr_pa_request_shadow%rowtype,
5411: p_process_type in varchar2 default 'CURRENT'
5412: ) is
5413:
5410: p_shadow_data out nocopy ghr_pa_request_shadow%rowtype,
5411: p_process_type in varchar2 default 'CURRENT'
5412: ) is
5413:
5414: l_sf52_shadow ghr_pa_requests%rowtype;
5415: l_shadow_data ghr_pa_request_shadow%rowtype;
5416: l_sf52_refresh_data ghr_pa_requests%rowtype;
5417: l_changed boolean := FALSE;
5418: l_proc varchar2(30):='refresh_req_shadow';
5412: ) is
5413:
5414: l_sf52_shadow ghr_pa_requests%rowtype;
5415: l_shadow_data ghr_pa_request_shadow%rowtype;
5416: l_sf52_refresh_data ghr_pa_requests%rowtype;
5417: l_changed boolean := FALSE;
5418: l_proc varchar2(30):='refresh_req_shadow';
5419: l_bef_basic_pay number;
5420: l_retention_allowance number;
5422: l_supervisory_differential number;
5423: l_staffing_differential number;
5424: l_multi_error_flag boolean;
5425: l_capped_other_pay number;
5426: l_sf52_data ghr_pa_requests%rowtype;
5427:
5428: cursor c_sf52_shadow (c_pa_request_id in number) is
5429: select *
5430: from ghr_pa_request_shadow
5430: from ghr_pa_request_shadow
5431: where pa_request_id = c_pa_request_id ;
5432:
5433: -- Sundar 2112935 Added the variables below for Output
5434: l_sf52_out_shadow ghr_pa_requests%rowtype;
5435: l_sf52_refresh_out_data ghr_pa_requests%rowtype;
5436: Begin
5437: l_sf52_data := p_sf52_data ;--NOCOPY Changes
5438:
5431: where pa_request_id = c_pa_request_id ;
5432:
5433: -- Sundar 2112935 Added the variables below for Output
5434: l_sf52_out_shadow ghr_pa_requests%rowtype;
5435: l_sf52_refresh_out_data ghr_pa_requests%rowtype;
5436: Begin
5437: l_sf52_data := p_sf52_data ;--NOCOPY Changes
5438:
5439: print_sf52('Initial Data: ', p_sf52_data);
5693:
5694: End refresh_req_shadow ;
5695:
5696:
5697: Procedure Redo_Pay_calc ( p_sf52_rec in out nocopy ghr_pa_requests%rowtype,
5698: p_capped_other_pay in out nocopy number ) is
5699:
5700: l_pay_calc_in_rec ghr_pay_calc.pay_calc_in_rec_type;
5701: l_pay_calc_out_rec ghr_pay_calc.pay_calc_out_rec_type;
5711: l_pay_cap_message BOOLEAN;
5712: l_temp_retention_allowance NUMBER;
5713: l_session_var ghr_history_api.g_session_var_type;
5714: l_multi_error_flag boolean;
5715: l_sf52_rec ghr_pa_requests%rowtype;
5716: l_capped_other_pay Number;
5717:
5718: --Pradeep for Bug 3306515
5719: l_temp_ret_allo_percentage NUMBER;
6036:
6037: End redo_pay_calc;
6038:
6039:
6040: Procedure Update_rfrs_values( p_sf52_data in out nocopy ghr_pa_requests%rowtype,
6041: p_shadow_data in ghr_pa_request_shadow%rowtype) is
6042: cursor get_par_ovn is
6043: select
6044: object_version_number
6041: p_shadow_data in ghr_pa_request_shadow%rowtype) is
6042: cursor get_par_ovn is
6043: select
6044: object_version_number
6045: from ghr_pa_requests
6046: where pa_request_id = p_Sf52_data.pa_request_id;
6047:
6048: cursor get_person_type is
6049: select ppt.system_person_type
6056: and ppt.person_type_id = ppf.person_type_id;
6057:
6058:
6059: l_result Boolean;
6060: l_ovn ghr_pa_requests.object_version_number%type;
6061: l_per_type per_person_types.system_person_type%type;
6062: l_sf52_data ghr_pa_requests%rowtype;
6063:
6064:
6058:
6059: l_result Boolean;
6060: l_ovn ghr_pa_requests.object_version_number%type;
6061: l_per_type per_person_types.system_person_type%type;
6062: l_sf52_data ghr_pa_requests%rowtype;
6063:
6064:
6065: Begin
6066:
6412: ,p_shadow_data.year_degree_attained
6413: );
6414: end create_shadow_row;
6415:
6416: Procedure create_shadow_row ( p_sf52_data in ghr_pa_requests%rowtype) is
6417: Begin
6418: Insert into ghr_pa_request_shadow
6419: (pa_request_id
6420: ,academic_discipline
6572: RAISE;
6573:
6574: end update_shadow_row;
6575:
6576: procedure print_sf52(p_proc VARCHAR2, p_pa_request_rec GHR_PA_REQUESTS%ROWTYPE)
6577: IS
6578: BEGIN
6579: hr_utility.set_location ('-----------------------------------------------', 10);
6580: hr_utility.set_location (p_proc, 20);
6896:
6897: END print_sf52;
6898:
6899: --6850492
6900: procedure Dual_Cancel_sf52(p_sf52_data in out nocopy ghr_pa_requests%rowtype
6901: ,p_first_noa_code in varchar2
6902: ,p_second_noa_code in varchar2
6903: ,p_pa_request_id in number
6904: ,p_ovn in number
6906: ,p_second_noa_id in number
6907: ,p_row_id in varchar2) is
6908:
6909: l_which_noa varchar2(1);
6910: l_dual_noa_id ghr_pa_requests.first_noa_id%type;
6911: l_pa_request_id ghr_pa_requests.pa_request_id%type;
6912: l_sf52_dual_sec_rec ghr_pa_requests%rowtype;
6913: l_sf52_dual_first_rec ghr_pa_requests%rowtype;
6914: l_ovn ghr_pa_requests.object_version_number%type;
6907: ,p_row_id in varchar2) is
6908:
6909: l_which_noa varchar2(1);
6910: l_dual_noa_id ghr_pa_requests.first_noa_id%type;
6911: l_pa_request_id ghr_pa_requests.pa_request_id%type;
6912: l_sf52_dual_sec_rec ghr_pa_requests%rowtype;
6913: l_sf52_dual_first_rec ghr_pa_requests%rowtype;
6914: l_ovn ghr_pa_requests.object_version_number%type;
6915: l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
6908:
6909: l_which_noa varchar2(1);
6910: l_dual_noa_id ghr_pa_requests.first_noa_id%type;
6911: l_pa_request_id ghr_pa_requests.pa_request_id%type;
6912: l_sf52_dual_sec_rec ghr_pa_requests%rowtype;
6913: l_sf52_dual_first_rec ghr_pa_requests%rowtype;
6914: l_ovn ghr_pa_requests.object_version_number%type;
6915: l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
6916: l_object_version_number ghr_pa_remarks.object_version_number%type;
6909: l_which_noa varchar2(1);
6910: l_dual_noa_id ghr_pa_requests.first_noa_id%type;
6911: l_pa_request_id ghr_pa_requests.pa_request_id%type;
6912: l_sf52_dual_sec_rec ghr_pa_requests%rowtype;
6913: l_sf52_dual_first_rec ghr_pa_requests%rowtype;
6914: l_ovn ghr_pa_requests.object_version_number%type;
6915: l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
6916: l_object_version_number ghr_pa_remarks.object_version_number%type;
6917: --8737212
6910: l_dual_noa_id ghr_pa_requests.first_noa_id%type;
6911: l_pa_request_id ghr_pa_requests.pa_request_id%type;
6912: l_sf52_dual_sec_rec ghr_pa_requests%rowtype;
6913: l_sf52_dual_first_rec ghr_pa_requests%rowtype;
6914: l_ovn ghr_pa_requests.object_version_number%type;
6915: l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
6916: l_object_version_number ghr_pa_remarks.object_version_number%type;
6917: --8737212
6918: l_u_pa_routing_history_id ghr_pa_routing_history.pa_routing_history_id%TYPE;
6920:
6921: cursor c_dual_cancel
6922: is
6923: select *
6924: from ghr_pa_requests
6925: where pa_request_id = l_pa_request_id;
6926:
6927:
6928: cursor c_dual_first
6927:
6928: cursor c_dual_first
6929: is
6930: select *
6931: from ghr_pa_requests
6932: where pa_request_id = p_sf52_data.pa_request_id;
6933:
6934: cursor get_ovn(p_pa_request_id in number)
6935: is
6933:
6934: cursor get_ovn(p_pa_request_id in number)
6935: is
6936: select object_version_number
6937: from ghr_pa_requests
6938: where pa_request_id = p_pa_request_id;
6939: --8272695
6940: cursor c_get_remarks
6941: is