[Home] [Help]
8: --
9:
10: --
11: TYPE pa_req_info is record (
12: pa_request_id ghr_pa_requests.pa_request_id%type,
13: object_version_number ghr_pa_requests.object_version_number%type,
14: status ghr_pa_requests.status%type,
15: row_id varchar2(20),
16: cancel_legal_authority ghr_pa_requests.first_action_la_code1%type
9:
10: --
11: TYPE pa_req_info is record (
12: pa_request_id ghr_pa_requests.pa_request_id%type,
13: object_version_number ghr_pa_requests.object_version_number%type,
14: status ghr_pa_requests.status%type,
15: row_id varchar2(20),
16: cancel_legal_authority ghr_pa_requests.first_action_la_code1%type
17: );
10: --
11: TYPE pa_req_info is record (
12: pa_request_id ghr_pa_requests.pa_request_id%type,
13: object_version_number ghr_pa_requests.object_version_number%type,
14: status ghr_pa_requests.status%type,
15: row_id varchar2(20),
16: cancel_legal_authority ghr_pa_requests.first_action_la_code1%type
17: );
18:
12: pa_request_id ghr_pa_requests.pa_request_id%type,
13: object_version_number ghr_pa_requests.object_version_number%type,
14: status ghr_pa_requests.status%type,
15: row_id varchar2(20),
16: cancel_legal_authority ghr_pa_requests.first_action_la_code1%type
17: );
18:
19: cursor c_get_sf52 (c_pa_request_id in number) is
20: select
18:
19: cursor c_get_sf52 (c_pa_request_id in number) is
20: select
21: *
22: from ghr_pa_requests
23: where pa_request_id = c_pa_request_id;
24:
25: --
26:
54: p_row_id in rowid);
55: Procedure delete_hist_row (
56: p_pa_history_id in ghr_pa_history.pa_history_id%type);
57: Procedure apply_correction(
58: p_sf52rec_correct in ghr_pa_requests%rowtype,
59: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
60: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) ;
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
55: Procedure delete_hist_row (
56: p_pa_history_id in ghr_pa_history.pa_history_id%type);
57: Procedure apply_correction(
58: p_sf52rec_correct in ghr_pa_requests%rowtype,
59: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
60: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) ;
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
63: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
56: p_pa_history_id in ghr_pa_history.pa_history_id%type);
57: Procedure apply_correction(
58: p_sf52rec_correct in ghr_pa_requests%rowtype,
59: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
60: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) ;
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
63: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
64: Procedure Undo_Mark_Cancel(
58: p_sf52rec_correct in ghr_pa_requests%rowtype,
59: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
60: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) ;
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
63: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
64: Procedure Undo_Mark_Cancel(
65: p_sf52_data in ghr_pa_requests%rowtype);
66:
59: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
60: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) ;
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
63: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
64: Procedure Undo_Mark_Cancel(
65: p_sf52_data in ghr_pa_requests%rowtype);
66:
67: Procedure what_to_do( p_datetrack_table in boolean,
61: Procedure apply_noa_corrections(
62: p_sf52_data in ghr_pa_requests%rowtype,
63: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
64: Procedure Undo_Mark_Cancel(
65: p_sf52_data in ghr_pa_requests%rowtype);
66:
67: Procedure what_to_do( p_datetrack_table in boolean,
68: p_pre_record_exists in boolean,
69: p_interv_on_table in boolean,
74: p_cannot_cancel out nocopy boolean) ;
75:
76: Procedure convert_shadow_to_sf52 (
77: p_shadow in ghr_pa_request_shadow%rowtype,
78: p_sf52 out nocopy ghr_pa_requests%rowtype);
79:
80: Procedure IF_ZAP_ELE_ENT(
81: p_element_entry_id in number,
82: p_effective_start_date in date,
84: p_result out nocopy Boolean);
85:
86: -- VSM [Procedures for delete subsequent correction functionality]
87: Procedure Process_Cancel (
88: p_sf52_data in out nocopy ghr_pa_requests%rowtype);
89:
90: Procedure Cancel_subs_correction (
91: p_corr_sf52_detail in out nocopy pa_req_info,
92: p_which_noa in number);
99:
100: -- This procedure will get the other pay component values
101: -- at the time of intervening correction.
102: PROCEDURE get_sf52_to_othpays_for_ia
103: (p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype);
104: -- End of Bug#2521744
105:
106: Function get_sf52_ovn ( p_pa_request_id in number) return number;
107: --
106: Function get_sf52_ovn ( p_pa_request_id in number) return number;
107: --
108:
109: -- 6850492 Added this procedure for dual action corrections
110: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
111: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
112:
113:
114:
107: --
108:
109: -- 6850492 Added this procedure for dual action corrections
110: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
111: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype );
112:
113:
114:
115: ----- End of Local procedure declaration -------
125: -- Pre-Requisities:
126: -- None.
127: --
128: -- In Parameters:
129: -- p_sf52_data -> ghr_pa_requests record of the cancellation sf52.
130: --
131: -- Post Success:
132: -- The termination sf52 will have been cancelled.
133: --
142: --
143: -- {End Of Comments}
144: -- ---------------------------------------------------------------------------
145:
146: Procedure cancel_term_SF52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
147:
148: -- this cursor gets the rowids from ghr_pa_history for the given pa_request_id.
149: cursor c_hist ( c_pa_request_id number,
150: c_noa_id number) is
259: l_imm_retirement_info ghr_api.per_retirement_info_type;
260: --BUG # 13571004
261: l_imm_roth_tsp ghr_api.tsp_roth_type;
262:
263: l_sf52_data ghr_pa_requests%rowtype;
264: l_sf52_data_rec ghr_pa_requests%rowtype;
265: l_health_plan varchar2(30);
266: l_error_flag boolean;
267: l_return_status varchar2(30);
260: --BUG # 13571004
261: l_imm_roth_tsp ghr_api.tsp_roth_type;
262:
263: l_sf52_data ghr_pa_requests%rowtype;
264: l_sf52_data_rec ghr_pa_requests%rowtype;
265: l_health_plan varchar2(30);
266: l_error_flag boolean;
267: l_return_status varchar2(30);
268:
786: -- Pre-Requisities:
787: -- None.
788: --
789: -- In Parameters:
790: -- p_sf52_data -> ghr_pa_requests record of the cancellation sf52.
791: --
792: -- Post Success:
793: -- The appointment sf52 will have been cancelled.
794: --
803: --
804: -- {End Of Comments}
805: -- ---------------------------------------------------------------------------
806:
807: Procedure Cancel_Appt_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
808:
809: l_session_var ghr_history_api.g_session_var_type;
810: l_hist_post ghr_pa_history%rowtype;
811: l_business_group_id number;
878: l_imm_thrift_saving_plan ghr_api.thrift_saving_plan;
879: l_imm_retention_allow_review ghr_api.retention_allow_review_type;
880: l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
881: l_imm_per_race_ethnic_info ghr_api.per_race_ethnic_type; -- Bug 4724337 Race or National Origin changes
882: l_sf52_data ghr_pa_requests%rowtype;
883: l_sf52_data_rec ghr_pa_requests%rowtype;
884: l_health_plan varchar2(30);
885: l_error_flag boolean;
886: l_return_status varchar2(30);
879: l_imm_retention_allow_review ghr_api.retention_allow_review_type;
880: l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
881: l_imm_per_race_ethnic_info ghr_api.per_race_ethnic_type; -- Bug 4724337 Race or National Origin changes
882: l_sf52_data ghr_pa_requests%rowtype;
883: l_sf52_data_rec ghr_pa_requests%rowtype;
884: l_health_plan varchar2(30);
885: l_error_flag boolean;
886: l_return_status varchar2(30);
887: l_result varchar2(30);
951: c_eff_date date) is
952: select
953: par.rowid,
954: par.*
955: from ghr_pa_requests par
956: where person_id = c_person_id and
957: effective_date >= c_eff_date and
958: pa_notification_id is not null and
959: approval_date is not null
1556: -- Pre-Requisities:
1557: -- None.
1558: --
1559: -- In Parameters:
1560: -- p_sf52_data -> ghr_pa_requests record of the correction sf52.
1561: --
1562: -- Post Success:
1563: -- The correction sf52 will have been cancelled.
1564: --
1573: -- Internal Development Use Only.
1574: --
1575: -- {End Of Comments}
1576: -- ---------------------------------------------------------------------------
1577: Procedure Cancel_Correction_SF52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
1578: l_proc varchar2(30):='Cancel_Correction_SF52';
1579: l_sf52_data ghr_pa_requests%rowtype;
1580: Begin
1581: hr_utility.set_location('entering : ' || l_proc, 10);
1575: -- {End Of Comments}
1576: -- ---------------------------------------------------------------------------
1577: Procedure Cancel_Correction_SF52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
1578: l_proc varchar2(30):='Cancel_Correction_SF52';
1579: l_sf52_data ghr_pa_requests%rowtype;
1580: Begin
1581: hr_utility.set_location('entering : ' || l_proc, 10);
1582: l_sf52_data := p_sf52_data;
1583: Cancel_Other_Family_Sf52 (p_sf52_data);
1601: -- Pre-Requisities:
1602: -- None.
1603: --
1604: -- In Parameters:
1605: -- p_sf52_data -> ghr_pa_requests record of the correction sf52.
1606: -- Post Success:
1607: -- The cancellation will have been applied.
1608: --
1609: -- Post Failure:
1617: --
1618: -- {End Of Comments}
1619: -- ---------------------------------------------------------------------------
1620:
1621: PROCEDURE Cancel_Other_Family_Sf52 ( p_sf52_data in out nocopy ghr_pa_requests%rowtype) AS
1622: l_buf number;
1623: l_eff_date date;
1624: l_proc varchar2(72) := 'Cancel_Other_Family_Sf52' ;
1625: l_date_eff_rec pay_element_entry_values_f%rowtype;
1623: l_eff_date date;
1624: l_proc varchar2(72) := 'Cancel_Other_Family_Sf52' ;
1625: l_date_eff_rec pay_element_entry_values_f%rowtype;
1626: l_hist_dummy ghr_pa_history%rowtype;
1627: l_hist_rec ghr_pa_requests%rowtype;
1628: l_hist_address_rec ghr_pa_history%rowtype;
1629: l_hist_pre ghr_pa_history%rowtype;
1630: l_hist_data_as_of_date ghr_pa_history%rowtype;
1631: l_return_status varchar2(100);
1707: l_imm_thrift_saving_plan ghr_api.thrift_saving_plan;
1708: l_imm_retention_allow_review ghr_api.retention_allow_review_type;
1709: l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
1710: l_imm_per_race_ethnic_info ghr_api.per_race_ethnic_type; -- Bug 4724337 Race or National Origin changes
1711: l_sf52_data ghr_pa_requests%rowtype;
1712: l_sf52_data_rec ghr_pa_requests%rowtype;
1713: l_health_plan varchar2(30);
1714: l_error_flag boolean;
1715: l_pa_history_id number;
1708: l_imm_retention_allow_review ghr_api.retention_allow_review_type;
1709: l_imm_health_ben_pre_tax ghr_api.health_ben_pre_tax_type;
1710: l_imm_per_race_ethnic_info ghr_api.per_race_ethnic_type; -- Bug 4724337 Race or National Origin changes
1711: l_sf52_data ghr_pa_requests%rowtype;
1712: l_sf52_data_rec ghr_pa_requests%rowtype;
1713: l_health_plan varchar2(30);
1714: l_error_flag boolean;
1715: l_pa_history_id number;
1716: l_result boolean;
1733: -- note that this cursor orders by table name, this is due to the fact that we need to
1734: -- handle PAY_ELEMENT_ENTRY_VALUES_F history records before PAY_ELEMENT_ENTRIES_F history
1735: -- records due to the parent-child relationship of these two tables. Fetch_history routine
1736: -- for elements relies on the parent record in history being intact.
1737: cursor c_history_info (cp_pa_request_id in ghr_pa_requests.pa_request_id%type,
1738: cp_noa_id in ghr_pa_requests.first_noa_id%type) is
1739: SELECT *
1740: FROM GHR_PA_HISTORY
1741: WHERE pa_request_id = cp_pa_request_id
1734: -- handle PAY_ELEMENT_ENTRY_VALUES_F history records before PAY_ELEMENT_ENTRIES_F history
1735: -- records due to the parent-child relationship of these two tables. Fetch_history routine
1736: -- for elements relies on the parent record in history being intact.
1737: cursor c_history_info (cp_pa_request_id in ghr_pa_requests.pa_request_id%type,
1738: cp_noa_id in ghr_pa_requests.first_noa_id%type) is
1739: SELECT *
1740: FROM GHR_PA_HISTORY
1741: WHERE pa_request_id = cp_pa_request_id
1742: AND nature_of_action_id = cp_noa_id
1769:
1770: Cursor c_prior_noa_code is
1771: Select par.first_noa_code,
1772: par.first_noa_id
1773: from ghr_pa_requests par
1774: where par.pa_request_id = p_sf52_data.altered_pa_request_id;
1775:
1776: Cursor c_noa_family(p_noa_id in number) is
1777: Select fam.noa_family_code
1852: p_information VARCHAR2)
1853: IS
1854: SELECT 1
1855: FROM ghr_pa_history
1856: WHERE pa_request_id IN (SELECT pa_request_id FROM ghr_pa_requests
1857: CONNECT BY pa_request_id = PRIOR altered_pa_request_id
1858: START WITH pa_request_id = p_pa_request_id)
1859: AND pa_history_id < p_pa_history_id
1860: AND table_name = p_table_name
2615: -- Pre-Requisities:
2616: -- None.
2617: --
2618: -- In Parameters:
2619: -- p_sf52_data -> ghr_pa_requests record of the correction sf52.
2620: -- Post Success:
2621: -- The correction will have been applied.
2622: --
2623: -- Post Failure:
2633: -- ---------------------------------------------------------------------------
2634:
2635: -- This is a sf52 wrapper procedure. It gets the sf52 and the sf52 extra info
2636: -- record and calls db_update procedures.
2637: Procedure correction_sf52( p_sf52_data in ghr_pa_requests%rowtype,
2638: p_process_type in varchar2 default 'CURRENT',
2639: p_capped_other_pay in number default null ) is
2640:
2641: l_sf52_data_result ghr_pa_requests%rowtype;
2637: Procedure correction_sf52( p_sf52_data in ghr_pa_requests%rowtype,
2638: p_process_type in varchar2 default 'CURRENT',
2639: p_capped_other_pay in number default null ) is
2640:
2641: l_sf52_data_result ghr_pa_requests%rowtype;
2642: l_sf52 ghr_pa_requests%rowtype;
2643: l_root_sf52 ghr_pa_requests%rowtype;
2644:
2645: l_pa_request_id number;
2638: p_process_type in varchar2 default 'CURRENT',
2639: p_capped_other_pay in number default null ) is
2640:
2641: l_sf52_data_result ghr_pa_requests%rowtype;
2642: l_sf52 ghr_pa_requests%rowtype;
2643: l_root_sf52 ghr_pa_requests%rowtype;
2644:
2645: l_pa_request_id number;
2646: l_today date :=sysdate;
2639: p_capped_other_pay in number default null ) is
2640:
2641: l_sf52_data_result ghr_pa_requests%rowtype;
2642: l_sf52 ghr_pa_requests%rowtype;
2643: l_root_sf52 ghr_pa_requests%rowtype;
2644:
2645: l_pa_request_id number;
2646: l_today date :=sysdate;
2647: l_session_var ghr_history_api.g_session_var_type;
2646: l_today date :=sysdate;
2647: l_session_var ghr_history_api.g_session_var_type;
2648: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
2649: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
2650: l_sf52_data ghr_pa_requests%rowtype;
2651: l_sf52_data1 ghr_pa_requests%rowtype;
2652: l_shadow_data ghr_pa_request_shadow%rowtype;
2653: l_result varchar2(1000);
2654:
2647: l_session_var ghr_history_api.g_session_var_type;
2648: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
2649: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
2650: l_sf52_data ghr_pa_requests%rowtype;
2651: l_sf52_data1 ghr_pa_requests%rowtype;
2652: l_shadow_data ghr_pa_request_shadow%rowtype;
2653: l_result varchar2(1000);
2654:
2655: -- this cursor gets the noa_family_code for noa_id (nature of action ID) passed.
2665: fams.update_hr_flag = 'Y';
2666:
2667: cursor c_get_root (c_pa_request_id in number) is
2668: select *
2669: from ghr_pa_requests
2670: connect by pa_request_id = prior altered_pa_request_id
2671: start with pa_request_id = c_pa_request_id
2672: order by level desc;
2673:
2688: --6850492
2689: cursor chk_dual_action(c_pa_request_id in number)
2690: is
2691: select 'Y'
2692: from ghr_pa_requests
2693: where pa_request_id = (select min(pa_request_id)
2694: from ghr_pa_requests
2695: where pa_notification_id is not null
2696: connect by pa_request_id = prior altered_pa_request_id
2690: is
2691: select 'Y'
2692: from ghr_pa_requests
2693: where pa_request_id = (select min(pa_request_id)
2694: from ghr_pa_requests
2695: where pa_notification_id is not null
2696: connect by pa_request_id = prior altered_pa_request_id
2697: start with pa_request_id = c_pa_request_id)
2698: and second_noa_code is not null
2703: --6850492
2704:
2705:
2706:
2707: Procedure Refresh_Cascade_Name( p_sf52_rec in out nocopy ghr_pa_requests%rowtype,
2708: p_shadow_rec in out nocopy ghr_pa_request_shadow%rowtype) is
2709:
2710: l_result_code varchar2(30);
2711: l_people_data per_all_people_f%rowtype;
2718: from ghr_pa_history
2719: where pa_request_id = p_sf52_rec.altered_pa_request_id and
2720: nature_of_action_id = p_sf52_rec.second_noa_id;
2721:
2722: l_sf52_rec ghr_pa_requests%rowtype;
2723: l_shadow_rec ghr_pa_request_shadow%rowtype;
2724: Begin
2725:
2726: hr_utility.set_location( 'Entering ' || l_proc, 10);
3051: -- Pre-Requisities:
3052: -- None.
3053: --
3054: -- In Parameters:
3055: -- p_sf52_rec_correct -> the ghr_pa_requests record that we are
3056: -- copying from.
3057: -- p_corr_pa_request_id -> the pa_request_id of the correction
3058: -- that is currently being processed.
3059: -- p_sf52rec -> results of the applied correction are put
3077:
3078: -- This procedure copies the fields from the p_sf52rec_correct record to
3079: -- p_sf52rec record and puts te result in sf52rec_result.
3080: PROCEDURE apply_correction (
3081: p_sf52rec_correct in ghr_pa_requests%rowtype,
3082: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
3083: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) is
3084:
3085: l_proc varchar2(30):='apply_correction';
3078: -- This procedure copies the fields from the p_sf52rec_correct record to
3079: -- p_sf52rec record and puts te result in sf52rec_result.
3080: PROCEDURE apply_correction (
3081: p_sf52rec_correct in ghr_pa_requests%rowtype,
3082: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
3083: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) is
3084:
3085: l_proc varchar2(30):='apply_correction';
3086: l_sf52rec ghr_pa_requests%rowtype;
3079: -- p_sf52rec record and puts te result in sf52rec_result.
3080: PROCEDURE apply_correction (
3081: p_sf52rec_correct in ghr_pa_requests%rowtype,
3082: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
3083: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) is
3084:
3085: l_proc varchar2(30):='apply_correction';
3086: l_sf52rec ghr_pa_requests%rowtype;
3087:
3082: p_corr_pa_request_id in ghr_pa_requests.pa_request_id%type,
3083: p_sf52rec in out nocopy ghr_pa_requests%rowtype ) is
3084:
3085: l_proc varchar2(30):='apply_correction';
3086: l_sf52rec ghr_pa_requests%rowtype;
3087:
3088: -- Begin Bug# 5014663
3089: l_asg_ei_data per_assignment_extra_info%rowtype;
3090: l_award_salary number;
3448: -- Pre-Requisities:
3449: -- None.
3450: --
3451: -- In Parameters:
3452: -- p_sf52_data -> the ghr_pa_requests record for the
3453: -- correction we are currently processing.
3454: -- p_sf52_data_result -> the ghr_pa_requests record that will hold the final
3455: -- corrected data for all corrections in the correction
3456: -- chain upon successful completion of this procedure.
3450: --
3451: -- In Parameters:
3452: -- p_sf52_data -> the ghr_pa_requests record for the
3453: -- correction we are currently processing.
3454: -- p_sf52_data_result -> the ghr_pa_requests record that will hold the final
3455: -- corrected data for all corrections in the correction
3456: -- chain upon successful completion of this procedure.
3457: -- Post Success:
3458: -- All the corrections in the correction chain will have been applied and the
3469: --
3470: -- {End Of Comments}
3471: -- ---------------------------------------------------------------------------
3472:
3473: Procedure apply_noa_corrections( p_sf52_data in ghr_pa_requests%rowtype,
3474: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3470: -- {End Of Comments}
3471: -- ---------------------------------------------------------------------------
3472:
3473: Procedure apply_noa_corrections( p_sf52_data in ghr_pa_requests%rowtype,
3474: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3478: l_sf52_ia_rec ghr_pa_requests%rowtype;
3472:
3473: Procedure apply_noa_corrections( p_sf52_data in ghr_pa_requests%rowtype,
3474: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3478: l_sf52_ia_rec ghr_pa_requests%rowtype;
3479: l_sf52_dummy ghr_pa_requests%rowtype;
3480: l_sf52_cursor_step_indx number;
3473: Procedure apply_noa_corrections( p_sf52_data in ghr_pa_requests%rowtype,
3474: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3478: l_sf52_ia_rec ghr_pa_requests%rowtype;
3479: l_sf52_dummy ghr_pa_requests%rowtype;
3480: l_sf52_cursor_step_indx number;
3481: l_session_var ghr_history_api.g_session_var_type;
3474: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3478: l_sf52_ia_rec ghr_pa_requests%rowtype;
3479: l_sf52_dummy ghr_pa_requests%rowtype;
3480: l_sf52_cursor_step_indx number;
3481: l_session_var ghr_history_api.g_session_var_type;
3482: -- Bug#5435374 added l_session_var1.
3475:
3476: l_sf52_data_orig ghr_pa_requests%rowtype;
3477: l_sf52_data_step ghr_pa_requests%rowtype;
3478: l_sf52_ia_rec ghr_pa_requests%rowtype;
3479: l_sf52_dummy ghr_pa_requests%rowtype;
3480: l_sf52_cursor_step_indx number;
3481: l_session_var ghr_history_api.g_session_var_type;
3482: -- Bug#5435374 added l_session_var1.
3483: l_session_var1 ghr_history_api.g_session_var_type;
3481: l_session_var ghr_history_api.g_session_var_type;
3482: -- Bug#5435374 added l_session_var1.
3483: l_session_var1 ghr_history_api.g_session_var_type;
3484: l_capped_other_pay number := hr_api.g_number;
3485: l_retro_eff_date ghr_pa_requests.effective_date%type;
3486: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3487: l_retro_first_noa ghr_nature_of_actions.code%type;
3488: l_retro_second_noa ghr_nature_of_actions.code%type;
3489: l_sf52_data_result ghr_pa_requests%rowtype;
3482: -- Bug#5435374 added l_session_var1.
3483: l_session_var1 ghr_history_api.g_session_var_type;
3484: l_capped_other_pay number := hr_api.g_number;
3485: l_retro_eff_date ghr_pa_requests.effective_date%type;
3486: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3487: l_retro_first_noa ghr_nature_of_actions.code%type;
3488: l_retro_second_noa ghr_nature_of_actions.code%type;
3489: l_sf52_data_result ghr_pa_requests%rowtype;
3490: -- Bug#3543213 Created l_dummy variable
3485: l_retro_eff_date ghr_pa_requests.effective_date%type;
3486: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3487: l_retro_first_noa ghr_nature_of_actions.code%type;
3488: l_retro_second_noa ghr_nature_of_actions.code%type;
3489: l_sf52_data_result ghr_pa_requests%rowtype;
3490: -- Bug#3543213 Created l_dummy variable
3491: l_dummy VARCHAR2(30);
3492: -- this cursor selects all rows in the correction chain from ghr_pa_requests
3493: cursor l_sf52_cursor is
3488: l_retro_second_noa ghr_nature_of_actions.code%type;
3489: l_sf52_data_result ghr_pa_requests%rowtype;
3490: -- Bug#3543213 Created l_dummy variable
3491: l_dummy VARCHAR2(30);
3492: -- this cursor selects all rows in the correction chain from ghr_pa_requests
3493: cursor l_sf52_cursor is
3494: select *
3495: from ghr_pa_requests
3496: connect by prior altered_pa_request_id = pa_request_id
3491: l_dummy VARCHAR2(30);
3492: -- this cursor selects all rows in the correction chain from ghr_pa_requests
3493: cursor l_sf52_cursor is
3494: select *
3495: from ghr_pa_requests
3496: connect by prior altered_pa_request_id = pa_request_id
3497: start with pa_request_id = p_sf52_data.pa_request_id
3498: order by level desc;
3499:
3500: cursor c_orig_details_for_ia is
3501: select pa_request_id,pa_notification_id,person_id,
3502: effective_date,from_position_id,
3503: to_position_id
3504: from ghr_pa_requests
3505: where pa_request_id = p_sf52_data.altered_pa_request_id;
3506:
3507: cursor c_get_hist_id (c_pa_request_id in number) is
3508: select
3604: -- .47
3605: -- Bug#3543213 For PRD U,V and NOA 894(Pay Adjustment) get the PRD Value from assignment
3606: IF p_sf52_data_result.first_noa_code = '894' AND
3607: p_sf52_data_result.pay_rate_determinant IN ('U','V') THEN
3608: ghr_pa_requests_pkg.get_SF52_asg_ddf_details
3609: (p_assignment_id => p_sf52_data_result.employee_assignment_id
3610: ,p_date_effective => p_sf52_data_result.effective_date
3611: ,p_tenure => l_dummy
3612: ,p_annuitant_indicator => l_dummy
5121: -- Pre-Requisities:
5122: -- None.
5123: --
5124: -- In Parameters:
5125: -- p_sf52_data -> ghr_pa_requests row of the cancellation being processed.
5126: --
5127: -- Post Success:
5128: -- If this is a cancellation of correction, the parent sf52 will have been marked as
5129: -- available for cancellation/correction again.
5139: --
5140: -- {End Of Comments}
5141: -- ---------------------------------------------------------------------------
5142:
5143: Procedure Undo_Mark_Cancel ( p_sf52_data in ghr_pa_requests%rowtype) is
5144: cursor get_req (cp_pa_request_id number) is
5145: select
5146: pa_request_id,
5147: altered_pa_request_id,
5147: altered_pa_request_id,
5148: first_noa_id,
5149: second_noa_id,
5150: object_version_number ovn
5151: from ghr_pa_requests
5152: where pa_request_id = cp_pa_request_id;
5153: l_proc varchar2(30):='Undo_Mark_Cancel';
5154: l_sf52_data get_req%rowtype;
5155: Begin
5211: End Undo_Mark_Cancel;
5212:
5213: Procedure convert_shadow_to_sf52 (
5214: p_shadow in ghr_pa_request_shadow%rowtype,
5215: p_sf52 out nocopy ghr_pa_requests%rowtype) is
5216: l_sf52 ghr_pa_requests%rowtype;
5217: Begin
5218:
5219: l_sf52 := p_sf52;
5212:
5213: Procedure convert_shadow_to_sf52 (
5214: p_shadow in ghr_pa_request_shadow%rowtype,
5215: p_sf52 out nocopy ghr_pa_requests%rowtype) is
5216: l_sf52 ghr_pa_requests%rowtype;
5217: Begin
5218:
5219: l_sf52 := p_sf52;
5220: p_sf52.pa_request_id := p_shadow.pa_request_id ;
5273:
5274: End;
5275:
5276: Procedure convert_sf52_to_shadow (
5277: p_sf52 in ghr_pa_requests%rowtype,
5278: p_shadow out nocopy ghr_pa_request_shadow%rowtype
5279: ) is
5280:
5281: Begin
5338: End;
5339:
5340: Procedure build_corrected_sf52(p_pa_request_id in number,
5341: p_noa_code_correct in varchar2,
5342: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype,
5343: p_called_from in varchar2 default null ) is
5344:
5345: l_sf52_data_orig ghr_pa_requests%rowtype;
5346: l_sf52_data_step ghr_pa_requests%rowtype;
5341: p_noa_code_correct in varchar2,
5342: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype,
5343: p_called_from in varchar2 default null ) is
5344:
5345: l_sf52_data_orig ghr_pa_requests%rowtype;
5346: l_sf52_data_step ghr_pa_requests%rowtype;
5347: l_sf52_data_result_rec ghr_pa_requests%rowtype;
5348: l_sf52_dummy ghr_pa_requests%rowtype;
5349: l_sf52_cursor_step_indx number;
5342: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype,
5343: p_called_from in varchar2 default null ) is
5344:
5345: l_sf52_data_orig ghr_pa_requests%rowtype;
5346: l_sf52_data_step ghr_pa_requests%rowtype;
5347: l_sf52_data_result_rec ghr_pa_requests%rowtype;
5348: l_sf52_dummy ghr_pa_requests%rowtype;
5349: l_sf52_cursor_step_indx number;
5350: l_capped_other_pay number := hr_api.g_number;
5343: p_called_from in varchar2 default null ) is
5344:
5345: l_sf52_data_orig ghr_pa_requests%rowtype;
5346: l_sf52_data_step ghr_pa_requests%rowtype;
5347: l_sf52_data_result_rec ghr_pa_requests%rowtype;
5348: l_sf52_dummy ghr_pa_requests%rowtype;
5349: l_sf52_cursor_step_indx number;
5350: l_capped_other_pay number := hr_api.g_number;
5351:
5344:
5345: l_sf52_data_orig ghr_pa_requests%rowtype;
5346: l_sf52_data_step ghr_pa_requests%rowtype;
5347: l_sf52_data_result_rec ghr_pa_requests%rowtype;
5348: l_sf52_dummy ghr_pa_requests%rowtype;
5349: l_sf52_cursor_step_indx number;
5350: l_capped_other_pay number := hr_api.g_number;
5351:
5352: -- this cursor selects all rows in the correction chain from ghr_pa_requests
5348: l_sf52_dummy ghr_pa_requests%rowtype;
5349: l_sf52_cursor_step_indx number;
5350: l_capped_other_pay number := hr_api.g_number;
5351:
5352: -- this cursor selects all rows in the correction chain from ghr_pa_requests
5353: cursor l_sf52_cursor is
5354: select *
5355: from ghr_pa_requests
5356: connect by prior altered_pa_request_id = pa_request_id
5351:
5352: -- this cursor selects all rows in the correction chain from ghr_pa_requests
5353: cursor l_sf52_cursor is
5354: select *
5355: from ghr_pa_requests
5356: connect by prior altered_pa_request_id = pa_request_id
5357: start with pa_request_id = p_pa_request_id
5358: order by level desc;
5359: l_shadow_data ghr_pa_request_shadow%rowtype;
5362: --Bug 2141522
5363: l_session_var ghr_history_api.g_session_var_type;
5364: -- Bug#5435374 added l_session_var1.
5365: l_session_var1 ghr_history_api.g_session_var_type;
5366: l_session_sf52_rec ghr_pa_requests%rowtype;
5367: cursor c_rpa is
5368: select *
5369: from ghr_pa_requests
5370: where pa_request_id = p_pa_request_id;
5365: l_session_var1 ghr_history_api.g_session_var_type;
5366: l_session_sf52_rec ghr_pa_requests%rowtype;
5367: cursor c_rpa is
5368: select *
5369: from ghr_pa_requests
5370: where pa_request_id = p_pa_request_id;
5371: --Bug 2141522
5372: cursor c_orig_details_for_ia is
5373: select pa_request_id,pa_notification_id,person_id,
5372: cursor c_orig_details_for_ia is
5373: select pa_request_id,pa_notification_id,person_id,
5374: effective_date,from_position_id,
5375: to_position_id
5376: from ghr_pa_requests
5377: where pa_request_id in (
5378: select altered_pa_request_id from
5379: ghr_pa_requests where pa_request_id = p_pa_request_id);
5380:
5375: to_position_id
5376: from ghr_pa_requests
5377: where pa_request_id in (
5378: select altered_pa_request_id from
5379: ghr_pa_requests where pa_request_id = p_pa_request_id);
5380:
5381: -- Bug#5435374
5382: l_pos_ei_grade_data per_position_extra_info%rowtype;
5383:
5389: where grd.grade_id = grd_id
5390: and grd.grade_definition_id = gdf.grade_definition_id;
5391:
5392: l_retro_eff_date date;
5393: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
5394: l_retro_first_noa ghr_nature_of_actions.code%type;
5395: l_retro_second_noa ghr_nature_of_actions.code%type;
5396: l_sf52_ia_rec ghr_pa_requests%rowtype;
5397:
5392: l_retro_eff_date date;
5393: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
5394: l_retro_first_noa ghr_nature_of_actions.code%type;
5395: l_retro_second_noa ghr_nature_of_actions.code%type;
5396: l_sf52_ia_rec ghr_pa_requests%rowtype;
5397:
5398:
5399: begin
5400: hr_utility.set_location('Entering:'|| l_proc, 5);
5565: p_sf52rec => p_sf52_data_result );
5566:
5567: if (l_sf52_data_step.first_noa_code = '352') then
5568: p_sf52_data_result.to_position_org_line1 :=
5569: ghr_pa_requests_pkg2.get_agency_code_to(
5570: p_pa_request_id => l_sf52_data_step.pa_request_id,
5571: p_noa_id => l_sf52_data_step.first_noa_id);
5572: -- Bug#2681726 In case of Realignment print all the Position Org Lines.
5573: elsif (l_sf52_data_step.first_noa_code = '790' ) then
5570: p_pa_request_id => l_sf52_data_step.pa_request_id,
5571: p_noa_id => l_sf52_data_step.first_noa_id);
5572: -- Bug#2681726 In case of Realignment print all the Position Org Lines.
5573: elsif (l_sf52_data_step.first_noa_code = '790' ) then
5574: ghr_pa_requests_pkg.get_rei_org_lines(
5575: p_pa_request_id => p_sf52_data_result.pa_request_id,
5576: p_organization_id => p_sf52_data_result.to_organization_id,
5577: p_position_org_line1 => p_sf52_data_result.to_position_org_line1,
5578: p_position_org_line2 => p_sf52_data_result.to_position_org_line2,
5677: Procedure populate_corrected_sf52(p_pa_request_id in number,
5678: p_noa_code_correct in varchar2)
5679: is
5680: l_session_var ghr_history_api.g_session_var_type;
5681: l_sf52_data ghr_pa_requests%ROWTYPE;
5682:
5683:
5684: FUNCTION get_record_category(p_sf52_record IN ghr_pa_requests%ROWTYPE)
5685: RETURN NUMBER IS
5680: l_session_var ghr_history_api.g_session_var_type;
5681: l_sf52_data ghr_pa_requests%ROWTYPE;
5682:
5683:
5684: FUNCTION get_record_category(p_sf52_record IN ghr_pa_requests%ROWTYPE)
5685: RETURN NUMBER IS
5686: CURSOR c_noa_fam_code(c_noa_id NUMBER) is
5687: select fam.noa_family_code family_code
5688: from ghr_noa_families fam
5826: END IF;
5827: END get_record_category;
5828:
5829: BEGIN
5830: -- Get the CORRECTION RPA record from ghr_pa_requests table.
5831: SELECT *
5832: INTO l_sf52_data
5833: FROM ghr_pa_requests
5834: WHERE pa_request_id = p_pa_request_id;
5829: BEGIN
5830: -- Get the CORRECTION RPA record from ghr_pa_requests table.
5831: SELECT *
5832: INTO l_sf52_data
5833: FROM ghr_pa_requests
5834: WHERE pa_request_id = p_pa_request_id;
5835: -- Check the record's category..
5836: -- If record belongs to category 1 or 2 then call build_corrected_sf52 with NPA parameter.
5837: -- BUG#3958914 Added the OR Conidtions
5944: /* cursor get_root_min_hist_id (cp_pa_history_id in number) is
5945: select min(pa_history_id)
5946: from ghr_pa_history
5947: where pa_request_id = (select min(pa_request_id)
5948: from ghr_pa_requests
5949: start with pa_request_id = (select pa_request_id -- Bug# 1253981
5950: from ghr_pa_history
5951: where pa_history_id = cp_pa_history_id)
5952: connect by prior altered_pa_request_id = pa_request_id)
5985: (select min(pa_history_id)
5986: from ghr_pa_history
5987: where pa_request_id =
5988: (select min(pa_request_id)
5989: from ghr_pa_requests
5990: connect by prior altered_pa_request_id = pa_request_id
5991: start with pa_request_id = pah.pa_request_id))));
5992:
5993: l_ele c_get_ele_ent%rowtype;
6047:
6048: End IF_ZAP_ELE_ENT;
6049:
6050: -- VSM
6051: Procedure Cancel_Routine (p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
6052:
6053: l_proc varchar2(30):='cancel_routine';
6054: l_sf52_data_rec ghr_pa_requests%rowtype;
6055:
6050: -- VSM
6051: Procedure Cancel_Routine (p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
6052:
6053: l_proc varchar2(30):='cancel_routine';
6054: l_sf52_data_rec ghr_pa_requests%rowtype;
6055:
6056: --
6057:
6058: -- This cursor would fetch active chain of subsequent corrections.
6062: pa_request_id
6063: ,object_version_number
6064: ,status
6065: ,rowid row_id
6066: from ghr_pa_requests
6067: where (level = 1 or ( level > 1 and
6068: nvl(second_noa_cancel_or_correct, '@#$') <> ghr_history_api.g_cancel)) and
6069: first_noa_code <> '001' and
6070: nvl(status, '@!#') <> 'CANCELED'
6076: TYPE array_pa_req is table of pa_req_info index by binary_integer;
6077: l_subsequent_pa_req array_pa_req;
6078: l_count number:=0;
6079: l_rev_count number;
6080: l_noa_id ghr_pa_requests.first_noa_id%type;
6081: l_sf52_canc ghr_pa_requests%rowtype;
6082: l_sf52 ghr_pa_requests%rowtype;
6083: l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
6084: --BUG # 13655849 increased length
6077: l_subsequent_pa_req array_pa_req;
6078: l_count number:=0;
6079: l_rev_count number;
6080: l_noa_id ghr_pa_requests.first_noa_id%type;
6081: l_sf52_canc ghr_pa_requests%rowtype;
6082: l_sf52 ghr_pa_requests%rowtype;
6083: l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
6084: --BUG # 13655849 increased length
6085: l_username fnd_user.user_name%type :=fnd_global.user_name;
6078: l_count number:=0;
6079: l_rev_count number;
6080: l_noa_id ghr_pa_requests.first_noa_id%type;
6081: l_sf52_canc ghr_pa_requests%rowtype;
6082: l_sf52 ghr_pa_requests%rowtype;
6083: l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
6084: --BUG # 13655849 increased length
6085: l_username fnd_user.user_name%type :=fnd_global.user_name;
6086: l_null_sf52 ghr_pa_requests%rowtype;
6079: l_rev_count number;
6080: l_noa_id ghr_pa_requests.first_noa_id%type;
6081: l_sf52_canc ghr_pa_requests%rowtype;
6082: l_sf52 ghr_pa_requests%rowtype;
6083: l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
6084: --BUG # 13655849 increased length
6085: l_username fnd_user.user_name%type :=fnd_global.user_name;
6086: l_null_sf52 ghr_pa_requests%rowtype;
6087: l_ovn number;
6082: l_sf52 ghr_pa_requests%rowtype;
6083: l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
6084: --BUG # 13655849 increased length
6085: l_username fnd_user.user_name%type :=fnd_global.user_name;
6086: l_null_sf52 ghr_pa_requests%rowtype;
6087: l_ovn number;
6088: l_which_noa number;
6089: --
6090: Begin
6214:
6215: End Cancel_routine;
6216:
6217: Procedure Process_Cancel (
6218: p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
6219:
6220: l_noa_family_code varchar2(30);
6221: l_proc varchar2(30):='Process_Cancel';
6222: l_prev_sf52_data ghr_pa_requests%rowtype;
6218: p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
6219:
6220: l_noa_family_code varchar2(30);
6221: l_proc varchar2(30):='Process_Cancel';
6222: l_prev_sf52_data ghr_pa_requests%rowtype;
6223: l_sf52_data ghr_pa_requests%rowtype;
6224:
6225: l_posn_eff_start_date date;
6226: l_posn_eff_end_date date;
6219:
6220: l_noa_family_code varchar2(30);
6221: l_proc varchar2(30):='Process_Cancel';
6222: l_prev_sf52_data ghr_pa_requests%rowtype;
6223: l_sf52_data ghr_pa_requests%rowtype;
6224:
6225: l_posn_eff_start_date date;
6226: l_posn_eff_end_date date;
6227: l_prior_posn_ovn number;
6230: -- Cursor to fetch noa_codes for given pa_request_id
6231: cursor c_sf52 (c_pa_request_id in number) is
6232: select
6233: *
6234: from ghr_pa_requests
6235: where pa_request_id = c_pa_request_id;
6236:
6237: -- cursor to fetch noa_family_code for the given noa_id.
6238: cursor c_fam (c_noa_id in number) is
6284: Cursor c_chk_pa_request(p_person_id in number
6285: ,p_effective_date in date)
6286: is
6287: select 1
6288: from ghr_pa_requests par
6289: where person_id = p_person_id
6290: and effective_date <= p_effective_date
6291: and first_noa_code not in ('100')
6292: and pa_notification_id is not null
6290: and effective_date <= p_effective_date
6291: and first_noa_code not in ('100')
6292: and pa_notification_id is not null
6293: and not exists (select 1
6294: from ghr_pa_requests b
6295: where person_id = p_person_id
6296: and altered_pa_request_id = par.pa_request_id
6297: and first_noa_code = '001'
6298: and second_noa_code = par.first_noa_code);
6551: l_noa_id number;
6552: --BUG # 13655849 increased length
6553: l_username fnd_user.user_name%type;
6554: l_dummy_number number;
6555: l_sf52_canc ghr_pa_requests%rowtype;
6556: l_corr_sf52_detail pa_req_info;
6557:
6558: cursor c_get_asg_id is
6559: select employee_assignment_id
6556: l_corr_sf52_detail pa_req_info;
6557:
6558: cursor c_get_asg_id is
6559: select employee_assignment_id
6560: from ghr_pa_requests
6561: where pa_request_id in
6562: ( select altered_pa_request_id from
6563: ghr_pa_requests where
6564: pa_request_id = p_corr_sf52_detail.pa_request_id);
6559: select employee_assignment_id
6560: from ghr_pa_requests
6561: where pa_request_id in
6562: ( select altered_pa_request_id from
6563: ghr_pa_requests where
6564: pa_request_id = p_corr_sf52_detail.pa_request_id);
6565: l_asg_id per_assignments_f.assignment_id%type;
6566:
6567:
6735:
6736: -- CURSOR to verify whether any future other pay action is existing or not.
6737: CURSOR c_future_other_pay_exists(p_assignment_id NUMBER,p_effective_date DATE) IS
6738: SELECT min(effective_date) effective_date
6739: FROM ghr_pa_requests
6740: where noa_family_code = 'OTHER_PAY'
6741: and pa_notification_id is not null
6742: and effective_date > p_effective_date
6743: and status = 'UPDATE_HR_COMPLETE'
6865:
6866: cursor c_get_sf52 (c_pa_request_id in number) is
6867: select
6868: object_version_number
6869: from ghr_pa_requests
6870: where pa_request_id = c_pa_request_id;
6871:
6872: l_ovn number;
6873: l_proc varchar2(30):='get_sf52_ovn';
6882: return l_ovn;
6883: End get_sf52_ovn;
6884:
6885: PROCEDURE get_sf52_to_details_for_ia
6886: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
6887: p_retro_eff_date in ghr_pa_requests.effective_date%type,
6888: p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype)
6889: IS
6890:
6883: End get_sf52_ovn;
6884:
6885: PROCEDURE get_sf52_to_details_for_ia
6886: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
6887: p_retro_eff_date in ghr_pa_requests.effective_date%type,
6888: p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype)
6889: IS
6890:
6891: CURSOR c_from_data IS
6884:
6885: PROCEDURE get_sf52_to_details_for_ia
6886: (p_pa_request_id in ghr_pa_requests.pa_request_id%type,
6887: p_retro_eff_date in ghr_pa_requests.effective_date%type,
6888: p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype)
6889: IS
6890:
6891: CURSOR c_from_data IS
6892: SELECT * FROM ghr_pa_requests
6888: p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype)
6889: IS
6890:
6891: CURSOR c_from_data IS
6892: SELECT * FROM ghr_pa_requests
6893: WHERE pa_request_id = p_pa_request_id;
6894:
6895: CURSOR c_grade_id(c_business_group_id per_grades.business_group_id%type,
6896: c_pay_plan per_grades.name%type,
6904: AND c_effective_date BETWEEN NVL(date_from,to_date('01/01/1951','dd/mm/yyyy')) AND NVL(date_to,to_date('31/12/4712','dd/mm/yyyy'));
6905:
6906: l_proc varchar2(30):='get_sf52_to_details_for_ia';
6907: l_dummy_varchar varchar2(30);
6908: l_sf52_ia_rec ghr_pa_requests%rowtype;
6909: l_business_group_id per_grades.business_group_id%type;
6910:
6911: BEGIN
6912: -- First assign the current From pos items to To pos items
6921: p_sf52_ia_rec.to_occ_code := c_sf52_data.from_occ_code;
6922: p_sf52_ia_rec.to_office_symbol := c_sf52_data.from_office_symbol;
6923: -- Bug#4696860 Added the following IF Condition.
6924: IF NOT (c_sf52_data.noa_family_code IN ('AWARD','GHR_INCENTIVE') OR
6925: ghr_pa_requests_pkg.GET_NOA_PM_FAMILY(c_sf52_data.second_noa_id) IN ('AWARD','GHR_INCENTIVE')) THEN
6926: p_sf52_ia_rec.to_pay_basis := c_sf52_data.from_pay_basis;
6927: p_sf52_ia_rec.to_total_salary := c_sf52_data.from_total_salary;
6928: p_sf52_ia_rec.to_other_pay_amount := c_sf52_data.from_other_pay_amount;
6929: p_sf52_ia_rec.to_adj_basic_pay := c_sf52_data.from_adj_basic_pay;
6962: p_sf52_ia_rec.from_position_org_line5 := c_sf52_data.from_position_org_line5;
6963: p_sf52_ia_rec.from_position_org_line6 := c_sf52_data.from_position_org_line6;
6964: END IF;
6965: -- Get position ddf data related to from_position_id
6966: ghr_pa_requests_pkg.get_SF52_pos_ddf_details
6967: (p_position_id => c_sf52_data.from_position_id
6968: ,p_date_effective => c_sf52_data.effective_date
6969: ,p_flsa_category => p_sf52_ia_rec.flsa_category
6970: ,p_bargaining_unit_status => p_sf52_ia_rec.bargaining_unit_status
6992: -- Bug#2521744
6993: -- This procedure will get the other pay component values
6994: -- at the time of intervening correction.
6995: PROCEDURE get_sf52_to_othpays_for_ia
6996: (p_sf52_ia_rec in out nocopy ghr_pa_requests%rowtype) IS
6997:
6998: CURSOR C_element_exists(p_element_name IN VARCHAR2,
6999: p_assignment_id IN number,
7000: p_effective_date IN DATE) IS
7016:
7017:
7018: l_proc varchar2(30):='get_sf52_to_othpays_for_ia';
7019: l_dummy_varchar varchar2(1);
7020: l_sf52_ia_rec ghr_pa_requests%rowtype;
7021: l_noa_id ghr_nature_of_actions.nature_of_action_id%type;
7022: l_noa_fam_code ghr_noa_families.noa_family_code%type;
7023:
7024: BEGIN
7130:
7131: END posn_not_active;
7132:
7133: --6850492
7134: procedure apply_dual_correction(p_sf52_data in ghr_pa_requests%rowtype,
7135: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype,
7136: p_retro_action_exists in varchar2) is
7137: cursor get_first_corr(p_pa_request_id in number)
7138: is
7131: END posn_not_active;
7132:
7133: --6850492
7134: procedure apply_dual_correction(p_sf52_data in ghr_pa_requests%rowtype,
7135: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype,
7136: p_retro_action_exists in varchar2) is
7137: cursor get_first_corr(p_pa_request_id in number)
7138: is
7139: select *
7136: p_retro_action_exists in varchar2) is
7137: cursor get_first_corr(p_pa_request_id in number)
7138: is
7139: select *
7140: from ghr_pa_requests
7141: where pa_request_id = p_pa_request_id;
7142:
7143: l_first_corr ghr_pa_requests%rowtype;
7144: begin
7139: select *
7140: from ghr_pa_requests
7141: where pa_request_id = p_pa_request_id;
7142:
7143: l_first_corr ghr_pa_requests%rowtype;
7144: begin
7145:
7146: open get_first_corr(p_pa_request_id => p_sf52_data.mass_action_id);
7147: fetch get_first_corr into l_first_corr;
7237: end apply_dual_correction;
7238:
7239: -- Bug 8264475 Added the procedure for dual correction for dual actions inplace of apply noa corrections
7240: -- this will be called
7241: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
7242: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7238:
7239: -- Bug 8264475 Added the procedure for dual correction for dual actions inplace of apply noa corrections
7240: -- this will be called
7241: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
7242: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7246: l_sf52_ia_rec ghr_pa_requests%rowtype;
7240: -- this will be called
7241: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
7242: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7246: l_sf52_ia_rec ghr_pa_requests%rowtype;
7247: l_sf52_dummy ghr_pa_requests%rowtype;
7248: l_sf52_cursor_step_indx number;
7241: Procedure apply_dual_noa_corrections(p_sf52_data in ghr_pa_requests%rowtype,
7242: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7246: l_sf52_ia_rec ghr_pa_requests%rowtype;
7247: l_sf52_dummy ghr_pa_requests%rowtype;
7248: l_sf52_cursor_step_indx number;
7249: l_session_var ghr_history_api.g_session_var_type;
7242: p_sf52_data_result in out nocopy ghr_pa_requests%rowtype ) is
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7246: l_sf52_ia_rec ghr_pa_requests%rowtype;
7247: l_sf52_dummy ghr_pa_requests%rowtype;
7248: l_sf52_cursor_step_indx number;
7249: l_session_var ghr_history_api.g_session_var_type;
7250: -- Bug#5435374 added l_session_var1.
7243:
7244: l_sf52_data_orig ghr_pa_requests%rowtype;
7245: l_sf52_data_step ghr_pa_requests%rowtype;
7246: l_sf52_ia_rec ghr_pa_requests%rowtype;
7247: l_sf52_dummy ghr_pa_requests%rowtype;
7248: l_sf52_cursor_step_indx number;
7249: l_session_var ghr_history_api.g_session_var_type;
7250: -- Bug#5435374 added l_session_var1.
7251: l_session_var1 ghr_history_api.g_session_var_type;
7249: l_session_var ghr_history_api.g_session_var_type;
7250: -- Bug#5435374 added l_session_var1.
7251: l_session_var1 ghr_history_api.g_session_var_type;
7252: l_capped_other_pay number := hr_api.g_number;
7253: l_retro_eff_date ghr_pa_requests.effective_date%type;
7254: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
7255: l_retro_first_noa ghr_nature_of_actions.code%type;
7256: l_retro_second_noa ghr_nature_of_actions.code%type;
7257: l_sf52_data_result ghr_pa_requests%rowtype;
7250: -- Bug#5435374 added l_session_var1.
7251: l_session_var1 ghr_history_api.g_session_var_type;
7252: l_capped_other_pay number := hr_api.g_number;
7253: l_retro_eff_date ghr_pa_requests.effective_date%type;
7254: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
7255: l_retro_first_noa ghr_nature_of_actions.code%type;
7256: l_retro_second_noa ghr_nature_of_actions.code%type;
7257: l_sf52_data_result ghr_pa_requests%rowtype;
7258: -- Bug#3543213 Created l_dummy variable
7253: l_retro_eff_date ghr_pa_requests.effective_date%type;
7254: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
7255: l_retro_first_noa ghr_nature_of_actions.code%type;
7256: l_retro_second_noa ghr_nature_of_actions.code%type;
7257: l_sf52_data_result ghr_pa_requests%rowtype;
7258: -- Bug#3543213 Created l_dummy variable
7259: l_dummy VARCHAR2(30);
7260: -- this cursor selects all rows in the correction chain from ghr_pa_requests
7261: cursor l_sf52_cursor
7256: l_retro_second_noa ghr_nature_of_actions.code%type;
7257: l_sf52_data_result ghr_pa_requests%rowtype;
7258: -- Bug#3543213 Created l_dummy variable
7259: l_dummy VARCHAR2(30);
7260: -- this cursor selects all rows in the correction chain from ghr_pa_requests
7261: cursor l_sf52_cursor
7262: is
7263: select *
7264: from ghr_pa_requests
7260: -- this cursor selects all rows in the correction chain from ghr_pa_requests
7261: cursor l_sf52_cursor
7262: is
7263: select *
7264: from ghr_pa_requests
7265: connect by prior altered_pa_request_id = pa_request_id
7266: start with pa_request_id = p_sf52_data.pa_request_id
7267: order by level desc;
7268:
7270: is
7271: select pa_request_id,pa_notification_id,person_id,
7272: effective_date,from_position_id,
7273: to_position_id,noa_family_code,second_noa_code
7274: from ghr_pa_requests
7275: where pa_request_id = p_sf52_data.altered_pa_request_id;
7276:
7277: cursor c_get_hist_id (c_pa_request_id in number)
7278: is
7348:
7349: cursor chk_dual_sec_corr(p_pa_request_id in number)
7350: is
7351: select 1
7352: from ghr_pa_requests
7353: where pa_request_id = p_pa_request_id
7354: and pa_request_id > mass_action_id
7355: and second_noa_code = (select second_noa_code
7356: from ghr_pa_requests
7352: from ghr_pa_requests
7353: where pa_request_id = p_pa_request_id
7354: and pa_request_id > mass_action_id
7355: and second_noa_code = (select second_noa_code
7356: from ghr_pa_requests
7357: where pa_request_id = (select min(pa_request_id)
7358: from ghr_pa_requests
7359: where pa_notification_id is not null
7360: connect by pa_request_id = prior altered_pa_request_id
7354: and pa_request_id > mass_action_id
7355: and second_noa_code = (select second_noa_code
7356: from ghr_pa_requests
7357: where pa_request_id = (select min(pa_request_id)
7358: from ghr_pa_requests
7359: where pa_notification_id is not null
7360: connect by pa_request_id = prior altered_pa_request_id
7361: start with pa_request_id = p_pa_request_id));
7362:
7428: -- .47
7429: -- Bug#3543213 For PRD U,V and NOA 894(Pay Adjustment) get the PRD Value from assignment
7430: IF p_sf52_data_result.first_noa_code = '894' AND
7431: p_sf52_data_result.pay_rate_determinant IN ('U','V') THEN
7432: ghr_pa_requests_pkg.get_SF52_asg_ddf_details
7433: (p_assignment_id => p_sf52_data_result.employee_assignment_id
7434: ,p_date_effective => p_sf52_data_result.effective_date
7435: ,p_tenure => l_dummy
7436: ,p_annuitant_indicator => l_dummy