[Home] [Help]
180: l_cannot_cancel_sevpay boolean;
181: l_interv_on_table boolean;
182: l_interv_on_eff_date boolean;
183: l_hist_data_as_of_date ghr_pa_history%rowtype;
184: l_session_var ghr_history_api.g_session_var_type;
185: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
186: l_imm_asg_sf52 ghr_api.asg_sf52_type;
187: l_imm_asg_non_sf52 ghr_api.asg_non_sf52_type;
188: l_imm_asg_nte_dates ghr_api.asg_nte_dates_type;
276: l_sf52_data_rec := p_sf52_data;
277: --
278: --
279: -- reinitialise session variables
280: ghr_history_api.reinit_g_session_var;
281: -- set values of session variables
282:
283: l_session_var.pa_request_id := p_sf52_data.pa_request_id;
284: l_session_var.noa_id := p_sf52_data.second_noa_id;
290: l_session_var.program_name := 'sf50';
291: l_session_var.assignment_id := p_sf52_data.employee_assignment_id;
292: l_session_var.altered_pa_request_id := p_sf52_data.altered_pa_request_id;
293: l_session_var.noa_id_correct := p_sf52_data.second_noa_id;
294: ghr_history_api.set_g_session_var(l_session_var);
295:
296: ghr_process_Sf52.Fetch_extra_info(
297: p_pa_request_id => p_sf52_data.pa_request_id,
298: p_noa_id => p_sf52_data.second_noa_id,
484: INTO l_hist_rec
485: FROM ghr_pa_history
486: WHERE pa_request_id = p_sf52_data.altered_pa_request_id
487: AND information5 = 'GHR_US_PER_SEPARATE_RETIRE'
488: AND table_name = ghr_history_api.g_peopei_table
489: AND nature_of_action_id = (select nature_of_action_id from ghr_nature_of_actions where
490: code = '352');
491: ghr_history_api.fetch_history_info(
492: p_table_name => ghr_history_api.g_peopei_table,
487: AND information5 = 'GHR_US_PER_SEPARATE_RETIRE'
488: AND table_name = ghr_history_api.g_peopei_table
489: AND nature_of_action_id = (select nature_of_action_id from ghr_nature_of_actions where
490: code = '352');
491: ghr_history_api.fetch_history_info(
492: p_table_name => ghr_history_api.g_peopei_table,
493: p_hist_data => l_hist_pre,
494: p_pa_history_id => l_hist_rec.pa_history_id,
495: p_table_pk_id => l_hist_rec.information1,
488: AND table_name = ghr_history_api.g_peopei_table
489: AND nature_of_action_id = (select nature_of_action_id from ghr_nature_of_actions where
490: code = '352');
491: ghr_history_api.fetch_history_info(
492: p_table_name => ghr_history_api.g_peopei_table,
493: p_hist_data => l_hist_pre,
494: p_pa_history_id => l_hist_rec.pa_history_id,
495: p_table_pk_id => l_hist_rec.information1,
496: p_person_id => p_sf52_data.person_id,
506: fetch c_follow_rec into l_buf;
507: if c_follow_rec%NOTFOUND then
508: hr_utility.set_location('no following records. Deleting appl table. '|| l_proc,915);
509: delete_appl_row(
510: p_table_name => ghr_history_api.g_peopei_table,
511: p_table_pk_id => l_hist_rec.information1,
512: p_dt_mode => null,
513: p_date_effective => l_hist_rec.effective_date);
514: end if;
539:
540: end if;
541:
542: -- Bug#2082535
543: Open c_hist_sevpay(p_sf52_data.altered_pa_request_id,ghr_history_api.g_eleent_table);
544: fetch c_hist_sevpay into l_hist_sevpay;
545: if c_hist_sevpay%notfound then
546: -- raise error;
547: close c_hist_sevpay;
577: exit when c_hist%notfound;
578: -- Bug#3780671 Added the Assignment Extra Info condition as
579: -- the EIT "GHR_US_ASG_NTE_DATES" requires the process similar to
580: -- Address table.
581: IF (l_hist.table_name = ghr_history_api.g_addres_table) OR
582: (l_hist.table_name = 'PER_ASSIGNMENT_EXTRA_INFO' and
583: l_hist.information5 = 'GHR_US_ASG_NTE_DATES') THEN
584:
585: SELECT *
589:
590: hr_utility.set_location('Non 352 and Address '||l_hist.table_name,26);
591: -- Bug#3780671 Passed the parameter l_hist.table_name instead of Address table
592: -- to handle PER_ASSIGNMENT_EXTRA_INFO table.
593: ghr_history_api.fetch_history_info(
594: p_table_name => l_hist.table_name,
595: p_hist_data => l_hist_pre,
596: p_pa_history_id => l_hist_rec.pa_history_id,
597: p_table_pk_id => l_hist_rec.information1,
775: -- ---------------------------------------------------------------------------
776:
777: Procedure Cancel_Appt_SF52( p_sf52_data in out nocopy ghr_pa_requests%rowtype) is
778:
779: l_session_var ghr_history_api.g_session_var_type;
780: l_hist_post ghr_pa_history%rowtype;
781: l_business_group_id number;
782: l_u_prh_object_version_number number;
783: l_i_pa_routing_history_id number;
924: l_sf52_data_rec := p_sf52_data;
925:
926:
927: -- reinitialise session variables
928: ghr_history_api.reinit_g_session_var;
929:
930: -- set values of session variables
931: l_session_var.pa_request_id := p_sf52_data.pa_request_id;
932: l_session_var.noa_id := p_sf52_data.second_noa_id;
939: l_session_var.altered_pa_request_id := p_sf52_data.altered_pa_request_id;
940: l_session_var.noa_id_correct := p_sf52_data.second_noa_id;
941: l_session_var.assignment_id := p_sf52_data.employee_assignment_id;
942:
943: ghr_history_api.set_g_session_var(l_session_var);
944:
945: ghr_process_Sf52.Fetch_extra_info(
946: p_pa_request_id => p_sf52_data.pa_request_id,
947: p_noa_id => p_sf52_data.second_noa_id,
1104: exit when c_hist%notfound;
1105: delete_hist_row( l_hist.row_id);
1106: hr_utility.set_location( 'checking if table needs deleting: ' || l_hist.table_name|| l_proc, 58);
1107: -- Delete all extraInfo table records which were created by the SF52.
1108: if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
1109: upper(ghr_history_api.g_posnei_table),
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1105: delete_hist_row( l_hist.row_id);
1106: hr_utility.set_location( 'checking if table needs deleting: ' || l_hist.table_name|| l_proc, 58);
1107: -- Delete all extraInfo table records which were created by the SF52.
1108: if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
1109: upper(ghr_history_api.g_posnei_table),
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1113: hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
1106: hr_utility.set_location( 'checking if table needs deleting: ' || l_hist.table_name|| l_proc, 58);
1107: -- Delete all extraInfo table records which were created by the SF52.
1108: if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
1109: upper(ghr_history_api.g_posnei_table),
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1113: hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
1114: if l_hist.DML_operation = ghr_history_api.g_ins_operation then
1107: -- Delete all extraInfo table records which were created by the SF52.
1108: if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
1109: upper(ghr_history_api.g_posnei_table),
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1113: hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
1114: if l_hist.DML_operation = ghr_history_api.g_ins_operation then
1115: hr_utility.set_location( 'delete appl row table_name: ' || l_hist.table_name|| l_proc, 51);
1108: if upper(l_hist.table_name) in (upper(ghr_history_api.g_peopei_table),
1109: upper(ghr_history_api.g_posnei_table),
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1113: hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
1114: if l_hist.DML_operation = ghr_history_api.g_ins_operation then
1115: hr_utility.set_location( 'delete appl row table_name: ' || l_hist.table_name|| l_proc, 51);
1116: hr_utility.set_location( 'delete appl row information1: ' || l_hist.information1|| l_proc, 52);
1110: upper(ghr_history_api.g_asgnei_table),
1111: upper(ghr_history_api.g_addres_table),
1112: upper(ghr_history_api.g_perana_table)) then
1113: hr_utility.set_location( 'table_name qualifies: ' || l_hist.table_name|| l_proc, 57);
1114: if l_hist.DML_operation = ghr_history_api.g_ins_operation then
1115: hr_utility.set_location( 'delete appl row table_name: ' || l_hist.table_name|| l_proc, 51);
1116: hr_utility.set_location( 'delete appl row information1: ' || l_hist.information1|| l_proc, 52);
1117: hr_utility.set_location( 'delete appl row effective_date: ' || l_hist.effective_date|| l_proc, 53);
1118: delete_appl_row(
1119: p_table_name => l_hist.table_name,
1120: p_table_pk_id => l_hist.information1,
1121: p_dt_mode => null,
1122: p_date_effective => l_hist.effective_date);
1123: elsif l_hist.DML_operation = ghr_history_api.g_upd_operation then
1124: l_hist_post.information1 := l_hist.information1;
1125: l_hist_post.person_id := l_hist.person_id;
1126: hr_utility.set_location(l_proc || 'l_hist.information1: ' || l_hist.information1, 1200);
1127: hr_utility.set_location(l_proc || 'l_hist.table_name: ' || l_hist.table_name, 1210);
1319: -- NVL(l_par.first_noa_cancel_or_correct, '***') = 'CORRECT' then"
1320: if l_par.first_noa_cancel_or_correct is null or
1321: NVL(l_par.first_noa_cancel_or_correct, '***') = 'CORRECT'
1322: then
1323: l_par.first_noa_cancel_or_correct := ghr_history_api.g_cancel;
1324: end if;
1325: end if;
1326: if l_par.second_noa_code is not NULL then
1327: -- handle dual actions
1325: end if;
1326: if l_par.second_noa_code is not NULL then
1327: -- handle dual actions
1328: if l_par.second_noa_cancel_or_correct is null then
1329: l_par.second_noa_cancel_or_correct := ghr_history_api.g_cancel;
1330: end if;
1331: end if;
1332:
1333: -- Special handling is required for the termination action 352.
1467: l_last_row boolean;
1468: l_cannot_cancel boolean;
1469: l_datetrack_table boolean;
1470: l_datetrack_mode varchar2(30);
1471: l_session_var ghr_history_api.g_session_var_type;
1472: l_result_code varchar2(30);
1473: l_rec_created_flag boolean;
1474: l_del_mode varchar2(30);
1475: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
1627: rowid row_id, table_name,pa_history_id,information5
1628: ,information9, information10
1629: from ghr_pa_history
1630: where pa_request_id = c_pa_request_id
1631: and table_name = ghr_history_api.g_addres_table
1632: and nature_of_action_id = c_noa_id
1633: for update of person_id
1634: order by pa_history_id desc;
1635: --
1711: hr_utility.set_location('Cancel of Ex_EMP conversion',1);
1712: ghr_corr_canc_sf52.cancel_appt_sf52(p_sf52_data);
1713: Else
1714: -- reinitialise session variables
1715: ghr_history_api.reinit_g_session_var;
1716: -- set values of session variables
1717: l_session_var.pa_request_id := p_sf52_data.pa_request_id;
1718: l_session_var.noa_id := p_sf52_data.second_noa_id;
1719: l_session_var.fire_trigger := 'Y';
1723: l_session_var.altered_pa_request_id := p_sf52_data.altered_pa_request_id;
1724: l_session_var.noa_id_correct := p_sf52_data.second_noa_id;
1725: l_session_var.assignment_id := p_sf52_data.employee_assignment_id;
1726:
1727: ghr_history_api.set_g_session_var(l_session_var);
1728:
1729: ghr_process_Sf52.Fetch_extra_info(
1730: p_pa_request_id => p_sf52_data.pa_request_id,
1731: p_noa_id => p_sf52_data.second_noa_id,
1886: INTO l_hist_address_rec
1887: FROM ghr_pa_history
1888: WHERE pa_history_id = l_hist.pa_history_id;
1889:
1890: ghr_history_api.fetch_history_info(
1891: p_table_name => ghr_history_api.g_addres_table,
1892: p_hist_data => l_hist_pre,
1893: p_pa_history_id => l_hist_address_rec.pa_history_id,
1894: p_table_pk_id => l_hist_address_rec.information1,
1887: FROM ghr_pa_history
1888: WHERE pa_history_id = l_hist.pa_history_id;
1889:
1890: ghr_history_api.fetch_history_info(
1891: p_table_name => ghr_history_api.g_addres_table,
1892: p_hist_data => l_hist_pre,
1893: p_pa_history_id => l_hist_address_rec.pa_history_id,
1894: p_table_pk_id => l_hist_address_rec.information1,
1895: p_person_id => p_sf52_data.person_id,
1903: c_pa_history_id => l_hist_address_rec.pa_history_id);
1904: fetch c_follow_rec into l_buf;
1905: if c_follow_rec%NOTFOUND then
1906: delete_appl_row(
1907: p_table_name => ghr_history_api.g_addres_table,
1908: p_table_pk_id => l_hist_address_rec.information1,
1909: p_dt_mode => null,
1910: p_date_effective => l_hist_address_rec.effective_date);
1911: end if;
1944: -- initialize pre-record to all nulls
1945: l_hist_pre := l_hist_dummy;
1946: -- get pre-record for the record fetched
1947: hr_utility.set_location('before fetch history info',12345);
1948: ghr_history_api.fetch_history_info(
1949: p_table_name => l_hist_rec.table_name,
1950: p_hist_data => l_hist_pre,
1951: p_pa_history_id => l_hist_rec.pa_history_id,
1952: p_table_pk_id => l_hist_rec.information1,
1978: hr_utility.set_location('datetrack table'|| l_proc,20);
1979: l_datetrack_table := FALSE;
1980: end if;
1981: -- PAY_ELEMENT_ENTRY needs to be handled differently
1982: if lower(l_hist_rec.table_name) = lower(ghr_history_api.g_eleent_table) then
1983: hr_utility.set_location('Processing element entry record'|| l_proc,25);
1984: if l_hist_rec.DML_operation = ghr_history_api.g_ins_operation then
1985: hr_utility.set_location('Record was created'|| l_proc,30);
1986: -- Call Delete_element_entry;
1980: end if;
1981: -- PAY_ELEMENT_ENTRY needs to be handled differently
1982: if lower(l_hist_rec.table_name) = lower(ghr_history_api.g_eleent_table) then
1983: hr_utility.set_location('Processing element entry record'|| l_proc,25);
1984: if l_hist_rec.DML_operation = ghr_history_api.g_ins_operation then
1985: hr_utility.set_location('Record was created'|| l_proc,30);
1986: -- Call Delete_element_entry;
1987: -- delete all entry values from history;
1988: -- VSM Changes made for BUG # 611161
2059: -- Delete history record
2060: delete_hist_row ( l_hist_rec.pa_history_id);
2061:
2062: -- PAY_ELEMENT_ENTRY_VALUE needs to be handled differently
2063: elsif lower(l_hist_rec.table_name) = lower (ghr_history_api.g_eleevl_table) then
2064: hr_utility.set_location('Processing element entry value record'|| l_proc,47);
2065: if l_hist_rec.DML_operation = ghr_history_api.g_upd_operation then
2066: hr_utility.set_location('Record was updated' || l_proc,48);
2067: if (l_pre_record = FALSE) then
2061:
2062: -- PAY_ELEMENT_ENTRY_VALUE needs to be handled differently
2063: elsif lower(l_hist_rec.table_name) = lower (ghr_history_api.g_eleevl_table) then
2064: hr_utility.set_location('Processing element entry value record'|| l_proc,47);
2065: if l_hist_rec.DML_operation = ghr_history_api.g_upd_operation then
2066: hr_utility.set_location('Record was updated' || l_proc,48);
2067: if (l_pre_record = FALSE) then
2068: -- Bug#2521744 Added if condition
2069: for element_name_rec1 in c_element_name1(l_hist_rec.information5)
2132:
2133: -- Determine if this record was created or updated.
2134: if l_datetrack_table then
2135: hr_utility.set_location('In Cancel - Date track table' ,1);
2136: if l_hist_rec.DML_operation = ghr_history_api.g_ins_operation then
2137: hr_utility.set_location('in Cancel - DML - Ins' ,1);
2138: l_rec_created_flag := TRUE;
2139:
2140: -- if this sf50 updated the row, then the pre must have the same date.
2144: -- a cancellation. In this case, the current row becomes the row that was created
2145: -- by the sf52. So, we must set the rec_created_flag to true for this row. Note
2146: -- this stands true with date tracked tables only as with non-date tracked tables there
2147: -- is only one row in a record, so this case will never occur.
2148: elsif l_hist_rec.DML_operation = ghr_history_api.g_upd_operation then
2149: hr_utility.set_location('in Cancel - DML - Upd' ,1);
2150: if l_hist_rec.effective_date <> l_hist_pre.effective_date then
2151: l_rec_created_flag := TRUE;
2152: hr_utility.set_location('in Cancel - Created' ,1);
2156: end if;
2157: end if;
2158: else
2159: hr_utility.set_location('In Cancel - Not a Date track table' ,1);
2160: l_rec_created_flag := (l_hist_rec.DML_operation = ghr_history_api.g_ins_operation);
2161: end if;
2162:
2163: what_to_do(
2164: p_datetrack_table => l_datetrack_table,
2382: l_root_sf52 ghr_pa_requests%rowtype;
2383:
2384: l_pa_request_id number;
2385: l_today date :=sysdate;
2386: l_session_var ghr_history_api.g_session_var_type;
2387: l_sf52_ei_data ghr_pa_request_extra_info%rowtype;
2388: l_agency_ei_data ghr_pa_request_extra_info%rowtype;
2389: l_sf52_data ghr_pa_requests%rowtype;
2390: l_sf52_data1 ghr_pa_requests%rowtype;
2510: begin
2511:
2512: hr_utility.set_location('Entering:'|| l_proc, 5);
2513: -- reinitialise session variables
2514: ghr_history_api.reinit_g_session_var;
2515:
2516: -- set values of session variables
2517: l_session_var.pa_request_id := p_sf52_data.pa_request_id;
2518: l_session_var.noa_id := p_sf52_data.second_noa_id;
2522: l_session_var.program_name := 'sf50';
2523: l_session_var.altered_pa_request_id := p_sf52_data.altered_pa_request_id;
2524: l_session_var.noa_id_correct := p_sf52_data.second_noa_id;
2525: l_session_var.assignment_id := p_sf52_data.employee_assignment_id;
2526: ghr_history_api.set_g_session_var(l_session_var);
2527:
2528:
2529: -- .47
2530:
2572: -- We are setting pa_history_id in session var to be able to fetch
2573: -- Pre-record values of the root SF52 for refresh purpose.
2574: -- It'll be reset after refresh has been done
2575:
2576: ghr_history_api.set_g_session_var(l_session_var);
2577: ghr_process_sf52.refresh_req_shadow(p_sf52_data => l_root_sf52,
2578: p_shadow_data => l_shadow_data);
2579: ghr_process_sf52.redo_pay_calc( p_sf52_rec => l_root_sf52);
2580: ghr_process_sf52.update_rfrs_values(p_sf52_data => l_root_sf52,
2581: p_shadow_data => l_shadow_data);
2582:
2583: -- reset pa_history_id in session variable.
2584: l_session_var.pa_history_id := null;
2585: ghr_history_api.set_g_session_var(l_session_var);
2586:
2587: -- .47
2588: */
2589:
2628: hr_utility.set_location('national_identifier right before update sf52: '|| l_sf52_data_result.employee_national_identifier || l_proc, 915);
2629: hr_utility.set_location('from_step_or_rate right before update sf52: '|| l_sf52_data_result.from_step_or_rate || l_proc, 915);
2630: hr_utility.set_location('to_step_or_rate right before update sf52: '|| l_sf52_data_result.to_step_or_rate || l_proc, 915);
2631:
2632: ghr_history_api.display_g_session_var;
2633: -- Check if atleast the min. required items exist in the pa_request
2634: ghr_sf52_validn_pkg.prelim_req_chk_for_update_hr(p_pa_request_rec => l_sf52_data_result);
2635: if (l_session_var.date_Effective > l_today) then
2636: -- issue savepoint
3171: l_sf52_data_step ghr_pa_requests%rowtype;
3172: l_sf52_ia_rec ghr_pa_requests%rowtype;
3173: l_sf52_dummy ghr_pa_requests%rowtype;
3174: l_sf52_cursor_step_indx number;
3175: l_session_var ghr_history_api.g_session_var_type;
3176: -- Bug#5435374 added l_session_var1.
3177: l_session_var1 ghr_history_api.g_session_var_type;
3178: l_capped_other_pay number := hr_api.g_number;
3179: l_retro_eff_date ghr_pa_requests.effective_date%type;
3173: l_sf52_dummy ghr_pa_requests%rowtype;
3174: l_sf52_cursor_step_indx number;
3175: l_session_var ghr_history_api.g_session_var_type;
3176: -- Bug#5435374 added l_session_var1.
3177: l_session_var1 ghr_history_api.g_session_var_type;
3178: l_capped_other_pay number := hr_api.g_number;
3179: l_retro_eff_date ghr_pa_requests.effective_date%type;
3180: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
3181: l_retro_first_noa ghr_nature_of_actions.code%type;
3244: l_proc varchar2(30):='apply_noa_corrections';
3245: begin
3246: hr_utility.set_location('Entering:'|| l_proc, 5);
3247: l_sf52_data_result := p_sf52_data_result;
3248: ghr_history_api.get_g_session_var(l_session_var);
3249: -- loop through all corrections in the correction chain, incrementally applying them by
3250: -- calling apply_corrections procedure.
3251: open l_sf52_cursor ;
3252: l_sf52_cursor_step_indx := 0;
3283: -- We are setting pa_history_id in session var to be able to fetch
3284: -- Pre-record values of the root SF52 for refresh purpose.
3285: -- It'll be reset after refresh has been done
3286:
3287: ghr_history_api.set_g_session_var(l_session_var);
3288: ghr_process_sf52.refresh_req_shadow(p_sf52_data => p_sf52_data_result,
3289: p_shadow_data => l_shadow_data);
3290: ghr_process_sf52.redo_pay_calc( p_sf52_rec => p_sf52_data_result,
3291: p_capped_other_pay => l_capped_other_pay);
3291: p_capped_other_pay => l_capped_other_pay);
3292:
3293: -- reset pa_history_id in session variable.
3294: l_session_var.pa_history_id := null;
3295: ghr_history_api.set_g_session_var(l_session_var);
3296: -- .47
3297: -- Bug#3543213 For PRD U,V and NOA 894(Pay Adjustment) get the PRD Value from assignment
3298: IF p_sf52_data_result.first_noa_code = '894' AND
3299: p_sf52_data_result.pay_rate_determinant IN ('U','V') THEN
3424: open core_chg_check( p_sf52_data_result.to_position_id,
3425: c_orig_det_rec.effective_date);
3426: fetch core_chg_check into l_core_chg_avbl;
3427: if core_chg_check%found then
3428: ghr_history_api.get_g_session_var(l_session_var);
3429: ghr_history_api.reinit_g_session_var;
3430: l_session_var1.date_Effective := l_session_var.date_Effective;
3431: l_session_var1.person_id := l_session_var.person_id;
3432: l_session_var1.assignment_id := l_session_var.assignment_id;
3425: c_orig_det_rec.effective_date);
3426: fetch core_chg_check into l_core_chg_avbl;
3427: if core_chg_check%found then
3428: ghr_history_api.get_g_session_var(l_session_var);
3429: ghr_history_api.reinit_g_session_var;
3430: l_session_var1.date_Effective := l_session_var.date_Effective;
3431: l_session_var1.person_id := l_session_var.person_id;
3432: l_session_var1.assignment_id := l_session_var.assignment_id;
3433: l_session_var1.fire_trigger := 'N';
3431: l_session_var1.person_id := l_session_var.person_id;
3432: l_session_var1.assignment_id := l_session_var.assignment_id;
3433: l_session_var1.fire_trigger := 'N';
3434: l_session_var1.program_name := 'sf50';
3435: ghr_history_api.set_g_session_var(l_session_var1);
3436: ghr_history_fetch.fetch_positionei(
3437: p_position_id => p_sf52_data_result.to_position_id,
3438: p_information_type => 'GHR_US_POS_GRP1',
3439: p_date_effective => p_sf52_data_result.effective_date,
3439: p_date_effective => p_sf52_data_result.effective_date,
3440: p_pos_ei_data => l_pos_ei_grp1_data);
3441: p_sf52_data_result.supervisory_status := l_pos_ei_grp1_data.poei_information16;
3442: p_sf52_data_result.part_time_hours := l_pos_ei_grp1_data.poei_information23;
3443: ghr_history_api.reinit_g_session_var;
3444: ghr_history_api.set_g_session_var(l_session_var);
3445: end if;
3446: close core_chg_check;
3447: END IF;
3440: p_pos_ei_data => l_pos_ei_grp1_data);
3441: p_sf52_data_result.supervisory_status := l_pos_ei_grp1_data.poei_information16;
3442: p_sf52_data_result.part_time_hours := l_pos_ei_grp1_data.poei_information23;
3443: ghr_history_api.reinit_g_session_var;
3444: ghr_history_api.set_g_session_var(l_session_var);
3445: end if;
3446: close core_chg_check;
3447: END IF;
3448: --bug #6356058 end
3493: IF p_sf52_data_result.from_position_id = p_sf52_data_result.to_position_id THEN
3494:
3495: -- Reinitializing the session variables to get the valid grade as on the
3496: -- effective date.
3497: ghr_history_api.get_g_session_var(l_session_var);
3498: ghr_history_api.reinit_g_session_var;
3499: l_session_var1.date_Effective := l_session_var.date_Effective;
3500: l_session_var1.person_id := l_session_var.person_id;
3501: l_session_var1.assignment_id := l_session_var.assignment_id;
3494:
3495: -- Reinitializing the session variables to get the valid grade as on the
3496: -- effective date.
3497: ghr_history_api.get_g_session_var(l_session_var);
3498: ghr_history_api.reinit_g_session_var;
3499: l_session_var1.date_Effective := l_session_var.date_Effective;
3500: l_session_var1.person_id := l_session_var.person_id;
3501: l_session_var1.assignment_id := l_session_var.assignment_id;
3502: l_session_var1.fire_trigger := 'N';
3500: l_session_var1.person_id := l_session_var.person_id;
3501: l_session_var1.assignment_id := l_session_var.assignment_id;
3502: l_session_var1.fire_trigger := 'N';
3503: l_session_var1.program_name := 'sf50';
3504: ghr_history_api.set_g_session_var(l_session_var1);
3505:
3506: -- Retrieve the Grade info from the POI history table
3507: ghr_history_fetch.fetch_positionei(
3508: p_position_id => p_sf52_data_result.to_position_id,
3511: p_pos_ei_data => l_pos_ei_grade_data);
3512:
3513: -- Reset the session variables after getting the date effective valid grade
3514: -- to continue with the correction process.
3515: ghr_history_api.reinit_g_session_var;
3516: ghr_history_api.set_g_session_var(l_session_var);
3517:
3518: IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
3519: hr_utility.set_location('GL: to grd id:'||p_sf52_data_result.to_grade_id,30);
3512:
3513: -- Reset the session variables after getting the date effective valid grade
3514: -- to continue with the correction process.
3515: ghr_history_api.reinit_g_session_var;
3516: ghr_history_api.set_g_session_var(l_session_var);
3517:
3518: IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
3519: hr_utility.set_location('GL: to grd id:'||p_sf52_data_result.to_grade_id,30);
3520: hr_utility.set_location('GL: pos ei grd:'||l_pos_ei_grade_data.poei_information3,40);
3904: hr_utility.set_location( 'information4 : ' || p_hist_pre.information4 || l_proc, 11);
3905: hr_utility.set_location( 'Effective Date : ' || p_hist_pre.information2 || l_proc, 12);
3906: hr_utility.set_location( 'Business Group id : ' || ll_bg_id, 100000002);
3907:
3908: l_eff_date := to_date(p_hist_pre.information2, ghr_history_api.g_hist_date_format);
3909: hr_utility.set_location('Converted eff Date: ' || l_eff_date || l_proc,18);
3910: -- get input value name.
3911: open c_input_value( cp_input_value_id => p_hist_pre.information4,
3912: cp_eff_date => l_eff_date,
3939: -- get assignment id.
3940: open c_asgmt_id (cp_information1 => p_hist_pre.information1,
3941: cp_information2 => p_hist_pre.information2,
3942: cp_information3 => p_hist_pre.information3,
3943: cp_table_name => ghr_history_api.g_eleevl_table);
3944: fetch c_asgmt_id into l_asg_id;
3945: if c_asgmt_id%NOTFOUND then
3946: hr_utility.set_location( 'assignment_id notfound : ' || l_proc, 41);
3947: close c_asgmt_id;
4032: -- to us.
4033: cursor c_hist is
4034: select rowid row_id
4035: from ghr_pa_history
4036: where table_name = ghr_history_api.g_eleevl_table and
4037: pa_request_id = p_hist_rec.pa_request_id and
4038: nature_of_action_id = p_hist_rec.nature_of_action_id
4039: for update of table_name;
4040: l_proc varchar2(30):='delete_eleentval';
4737: l_proc varchar2(72) := 'delete_appl_row';
4738: BEGIN
4739: hr_utility.set_location( 'Entering : ' || l_proc, 10);
4740: hr_utility.set_location(' P_TABLE_NAME:' || p_table_name, 15);
4741: if (lower(p_table_name) = lower(ghr_history_api.g_peop_table)) then
4742: delete_peop_row( p_person_id => p_table_pk_id,
4743: p_dt_mode => p_dt_mode,
4744: p_date_effective => p_date_effective);
4745: -- Added Handling of Position table. Bug# 1252481 11-MAY-2000
4742: delete_peop_row( p_person_id => p_table_pk_id,
4743: p_dt_mode => p_dt_mode,
4744: p_date_effective => p_date_effective);
4745: -- Added Handling of Position table. Bug# 1252481 11-MAY-2000
4746: elsif (lower(p_table_name) = lower(ghr_history_api.g_posn_table)) then
4747: delete_posn_row( p_position_id => p_table_pk_id,
4748: p_dt_mode => p_dt_mode,
4749: p_date_effective => p_date_effective);
4750: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgn_table)) then
4746: elsif (lower(p_table_name) = lower(ghr_history_api.g_posn_table)) then
4747: delete_posn_row( p_position_id => p_table_pk_id,
4748: p_dt_mode => p_dt_mode,
4749: p_date_effective => p_date_effective);
4750: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgn_table)) then
4751: delete_asgn_row( p_assignment_id => p_table_pk_id,
4752: p_dt_mode => p_dt_mode,
4753: p_date_effective => p_date_effective);
4754: elsif (lower(p_table_name) = lower(ghr_history_api.g_peopei_table)) then
4750: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgn_table)) then
4751: delete_asgn_row( p_assignment_id => p_table_pk_id,
4752: p_dt_mode => p_dt_mode,
4753: p_date_effective => p_date_effective);
4754: elsif (lower(p_table_name) = lower(ghr_history_api.g_peopei_table)) then
4755: delete_peopei_row( p_person_extra_info_id => p_table_pk_id);
4756: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgnei_table)) then
4757: delete_asgnei_row( p_assignment_extra_info_id => p_table_pk_id);
4758: elsif (lower(p_table_name) = lower(ghr_history_api.g_posnei_table)) then
4752: p_dt_mode => p_dt_mode,
4753: p_date_effective => p_date_effective);
4754: elsif (lower(p_table_name) = lower(ghr_history_api.g_peopei_table)) then
4755: delete_peopei_row( p_person_extra_info_id => p_table_pk_id);
4756: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgnei_table)) then
4757: delete_asgnei_row( p_assignment_extra_info_id => p_table_pk_id);
4758: elsif (lower(p_table_name) = lower(ghr_history_api.g_posnei_table)) then
4759: delete_posnei_row( p_position_extra_info_id => p_table_pk_id);
4760: elsif (lower(p_table_name) = lower(ghr_history_api.g_addres_table)) then
4754: elsif (lower(p_table_name) = lower(ghr_history_api.g_peopei_table)) then
4755: delete_peopei_row( p_person_extra_info_id => p_table_pk_id);
4756: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgnei_table)) then
4757: delete_asgnei_row( p_assignment_extra_info_id => p_table_pk_id);
4758: elsif (lower(p_table_name) = lower(ghr_history_api.g_posnei_table)) then
4759: delete_posnei_row( p_position_extra_info_id => p_table_pk_id);
4760: elsif (lower(p_table_name) = lower(ghr_history_api.g_addres_table)) then
4761: delete_address_row( p_address_id => p_table_pk_id);
4762: elsif (lower(p_table_name) = lower(ghr_history_api.g_perana_table)) then
4756: elsif (lower(p_table_name) = lower(ghr_history_api.g_asgnei_table)) then
4757: delete_asgnei_row( p_assignment_extra_info_id => p_table_pk_id);
4758: elsif (lower(p_table_name) = lower(ghr_history_api.g_posnei_table)) then
4759: delete_posnei_row( p_position_extra_info_id => p_table_pk_id);
4760: elsif (lower(p_table_name) = lower(ghr_history_api.g_addres_table)) then
4761: delete_address_row( p_address_id => p_table_pk_id);
4762: elsif (lower(p_table_name) = lower(ghr_history_api.g_perana_table)) then
4763: delete_person_analyses_row( p_person_analysis_id => p_table_pk_id);
4764: end if;
4758: elsif (lower(p_table_name) = lower(ghr_history_api.g_posnei_table)) then
4759: delete_posnei_row( p_position_extra_info_id => p_table_pk_id);
4760: elsif (lower(p_table_name) = lower(ghr_history_api.g_addres_table)) then
4761: delete_address_row( p_address_id => p_table_pk_id);
4762: elsif (lower(p_table_name) = lower(ghr_history_api.g_perana_table)) then
4763: delete_person_analyses_row( p_person_analysis_id => p_table_pk_id);
4764: end if;
4765: hr_utility.set_location( 'Leaving : ' || l_proc, 20);
4766: END delete_appl_row;
5017: l_shadow_data ghr_pa_request_shadow%rowtype;
5018: l_proc varchar2(30):='build_corrected_sf52';
5019:
5020: --Bug 2141522
5021: l_session_var ghr_history_api.g_session_var_type;
5022: -- Bug#5435374 added l_session_var1.
5023: l_session_var1 ghr_history_api.g_session_var_type;
5024: l_session_sf52_rec ghr_pa_requests%rowtype;
5025: cursor c_rpa is
5019:
5020: --Bug 2141522
5021: l_session_var ghr_history_api.g_session_var_type;
5022: -- Bug#5435374 added l_session_var1.
5023: l_session_var1 ghr_history_api.g_session_var_type;
5024: l_session_sf52_rec ghr_pa_requests%rowtype;
5025: cursor c_rpa is
5026: select *
5027: from ghr_pa_requests
5056:
5057: begin
5058: hr_utility.set_location('Entering:'|| l_proc, 5);
5059: l_sf52_data_result_rec := p_sf52_data_result;
5060: -- ghr_history_api.get_g_session_var(l_session_var);
5061: --Bug 2141522
5062: -- set values of session variables
5063: open c_rpa;
5064: fetch c_rpa into l_session_sf52_rec;
5072: l_session_var.program_name := 'sf50';
5073: l_session_var.assignment_id := l_session_sf52_rec.employee_assignment_id;
5074: l_session_var.altered_pa_request_id := l_session_sf52_rec.altered_pa_request_id;
5075: l_session_var.noa_id_correct := l_session_sf52_rec.second_noa_id;
5076: ghr_history_api.set_g_session_var(l_session_var);
5077: --Bug 2141522
5078:
5079: -- loop through all corrections in the correction chain, incrementally applying them by
5080: -- calling apply_corrections procedure.
5269: IF p_sf52_data_result.from_position_id = p_sf52_data_result.to_position_id THEN
5270:
5271: -- Reinitializing the session variables to get the valid grade as on the
5272: -- effective date.
5273: ghr_history_api.get_g_session_var(l_session_var);
5274: ghr_history_api.reinit_g_session_var;
5275: l_session_var1.date_Effective := l_session_var.date_Effective;
5276: l_session_var1.person_id := l_session_var.person_id;
5277: l_session_var1.assignment_id := l_session_var.assignment_id;
5270:
5271: -- Reinitializing the session variables to get the valid grade as on the
5272: -- effective date.
5273: ghr_history_api.get_g_session_var(l_session_var);
5274: ghr_history_api.reinit_g_session_var;
5275: l_session_var1.date_Effective := l_session_var.date_Effective;
5276: l_session_var1.person_id := l_session_var.person_id;
5277: l_session_var1.assignment_id := l_session_var.assignment_id;
5278: l_session_var1.fire_trigger := 'N';
5276: l_session_var1.person_id := l_session_var.person_id;
5277: l_session_var1.assignment_id := l_session_var.assignment_id;
5278: l_session_var1.fire_trigger := 'N';
5279: l_session_var1.program_name := 'sf50';
5280: ghr_history_api.set_g_session_var(l_session_var1);
5281:
5282: -- Retrieve the Grade info from the POI history table
5283: ghr_history_fetch.fetch_positionei(
5284: p_position_id => p_sf52_data_result.to_position_id,
5286: p_date_effective => p_sf52_data_result.effective_date,
5287: p_pos_ei_data => l_pos_ei_grade_data);
5288:
5289: -- Reset the session variables after getting the date effective grade value.
5290: ghr_history_api.reinit_g_session_var;
5291: ghr_history_api.set_g_session_var(l_session_var);
5292:
5293: IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
5294: hr_utility.set_location('GL: to grd id:'||p_sf52_data_result.to_grade_id,30);
5287: p_pos_ei_data => l_pos_ei_grade_data);
5288:
5289: -- Reset the session variables after getting the date effective grade value.
5290: ghr_history_api.reinit_g_session_var;
5291: ghr_history_api.set_g_session_var(l_session_var);
5292:
5293: IF l_pos_ei_grade_data.position_extra_info_id IS NOT NULL THEN
5294: hr_utility.set_location('GL: to grd id:'||p_sf52_data_result.to_grade_id,30);
5295: hr_utility.set_location('GL: pos ei grd:'||l_pos_ei_grade_data.poei_information3,40);
5326:
5327: Procedure populate_corrected_sf52(p_pa_request_id in number,
5328: p_noa_code_correct in varchar2)
5329: is
5330: l_session_var ghr_history_api.g_session_var_type;
5331: l_sf52_data ghr_pa_requests%ROWTYPE;
5332:
5333:
5334: FUNCTION get_record_category(p_sf52_record IN ghr_pa_requests%ROWTYPE)
5503: l_session_var.program_name := 'GHRSF50';
5504: l_session_var.assignment_id := l_sf52_data.employee_assignment_id;
5505: l_session_var.altered_pa_request_id := l_sf52_data.altered_pa_request_id;
5506: l_session_var.noa_id_correct := l_sf52_data.second_noa_id;
5507: ghr_history_api.set_g_session_var(l_session_var);
5508: ---Bug 5220727
5509: IF l_sf52_data.second_noa_code <> '825' then
5510: build_corrected_sf52(p_pa_request_id, p_noa_code_correct, sf52_corr_rec,'NPA');
5511: ELSE
5625: c_effective_date date,
5626: c_root_hist_id number) is
5627: Select pa_history_id
5628: from ghr_pa_history pah
5629: where table_name = ghr_history_api.g_eleevl_table
5630: and information5 = c_information1
5631: and (effective_date > c_effective_date or
5632: (effective_date = c_effective_date and
5633: c_root_hist_id <
5713: ,status
5714: ,rowid row_id
5715: from ghr_pa_requests
5716: where (level = 1 or ( level > 1 and
5717: nvl(second_noa_cancel_or_correct, '@#$') <> ghr_history_api.g_cancel)) and
5718: first_noa_code <> '001' and
5719: nvl(status, '@!#') <> 'CANCELED'
5720: start with pa_request_id = c_pa_request_id
5721: connect by prior pa_request_id = altered_pa_request_id and
5822: hr_utility.set_location('Fetching original SF52 ' || l_proc , 110);
5823:
5824: -- Set column values in the SF50 being Cancelled;
5825: -- copy first/second_noa_canc_pa_request_id to first/second_noa_pa_request_id
5826: -- set first/second_noa_canel_correct_flag = 'ghr_history_api.g_cancel
5827:
5828: l_sf52.object_version_number := get_sf52_ovn ( p_pa_request_id => l_sf52.pa_request_id);
5829:
5830: if l_sf52.first_noa_code = p_sf52_data.second_noa_code then
5833: ghr_par_upd.upd(
5834: p_pa_request_id => l_sf52.pa_request_id
5835: ,p_object_version_number => l_sf52.object_version_number
5836: ,p_first_noa_pa_request_id => l_sf52.first_noa_canc_pa_request_id
5837: ,p_first_noa_cancel_or_correct => ghr_history_api.g_cancel
5838: );
5839:
5840: end if;
5841: else
5844: ghr_par_upd.upd(
5845: p_pa_request_id => l_sf52.pa_request_id
5846: ,p_object_version_number => l_sf52.object_version_number
5847: ,p_second_noa_pa_request_id => l_sf52.second_noa_canc_pa_request_id
5848: ,p_second_noa_cancel_or_correct => ghr_history_api.g_cancel
5849: );
5850: end if;
5851: end if;
5852: