[Home] [Help]
1: PACKAGE BODY GHR_SF52_DO_UPDATE AS
2: /* $Header: gh52doup.pkb 120.61.12010000.5 2008/10/22 12:42:51 utokachi ship $ */
3: g_effective_date date;
4: g_old_user_status per_assignment_status_types.user_status%type;
5: --
6: -- Removed all cursors required to fetch noa_code, as it is already passed from ghr_pa_requests (FRONT END)
7: --
8: -- *******************************
2400: Procedure get_asg_status_type
2401: (p_noa_code in ghr_nature_of_actions.code%type,
2402: p_business_group_id in per_people_f.business_group_id%type,
2403: p_assignment_id in number,
2404: p_status_type_id out nocopy per_assignment_status_types.assignment_status_type_id%type,
2405: p_activate_flag out nocopy varchar2,
2406: p_suspend_flag out nocopy varchar2,
2407: p_terminate_flag out nocopy varchar2
2408: )
2408: )
2409: is
2410:
2411: l_proc varchar2(70) := 'get_asg_status_type';
2412: l_system_status per_assignment_status_types.per_system_status%type;
2413: l_user_status per_assignment_status_types.user_status%type;
2414: l_asg_status_type_id number;
2415: l_active_flag varchar2(1) := 'N';
2416:
2409: is
2410:
2411: l_proc varchar2(70) := 'get_asg_status_type';
2412: l_system_status per_assignment_status_types.per_system_status%type;
2413: l_user_status per_assignment_status_types.user_status%type;
2414: l_asg_status_type_id number;
2415: l_active_flag varchar2(1) := 'N';
2416:
2417:
2417:
2418: cursor c_asg_status_type is
2419: select ast.assignment_status_type_id,
2420: ast.active_flag
2421: from per_assignment_status_types ast
2422: where ast.per_system_status = l_system_status
2423: and ast.user_status like '%' || l_user_status || '%'
2424: and nvl(ast.business_group_id,hr_api.g_number) = hr_api.g_number
2425: and ast.legislation_code = 'US';
2426:
2427: cursor c_asg_status_type_471 is
2428: select ast.assignment_status_type_id,
2429: ast.active_flag
2430: from per_assignment_status_types ast
2431: where ast.per_system_status = l_system_status
2432: and ast.user_status like '%' || l_user_status || '%'
2433: and instr(ast.user_status,'NTE') = 0
2434: and nvl(ast.business_group_id,hr_api.g_number) = hr_api.g_number
2433: and instr(ast.user_status,'NTE') = 0
2434: and nvl(ast.business_group_id,hr_api.g_number) = hr_api.g_number
2435: and ast.legislation_code = 'US';
2436: -- Bug# 4672772 Begin
2437: l_user_apnt_status per_assignment_status_types.user_status%type;
2438: l_user_apnt_eff_date date;
2439: CURSOR c_user_apnt_status IS
2440: select ast.user_status,asg.effective_start_date
2441: from per_assignment_status_types ast,
2437: l_user_apnt_status per_assignment_status_types.user_status%type;
2438: l_user_apnt_eff_date date;
2439: CURSOR c_user_apnt_status IS
2440: select ast.user_status,asg.effective_start_date
2441: from per_assignment_status_types ast,
2442: per_all_assignments_f asg
2443: where ast.assignment_status_type_id = asg.assignment_status_type_id
2444: and asg.assignment_id = p_assignment_id
2445: and asg.primary_flag = 'Y'
2446: order by asg.effective_start_date;
2447:
2448: -- Bug# 4672772 End
2449: --Begin Bug# 6083404
2450: l_user_actv_apnt_status per_assignment_status_types.user_status%type;
2451: CURSOR c_user_actv_appt IS
2452: select ast.user_status
2453: from per_assignment_status_types ast,
2454: per_all_assignments_f asg
2449: --Begin Bug# 6083404
2450: l_user_actv_apnt_status per_assignment_status_types.user_status%type;
2451: CURSOR c_user_actv_appt IS
2452: select ast.user_status
2453: from per_assignment_status_types ast,
2454: per_all_assignments_f asg
2455: where ast.assignment_status_type_id = asg.assignment_status_type_id
2456: and asg.assignment_id = p_assignment_id
2457: and asg.primary_flag = 'Y'
2937: l_date1 ghr_pa_requests.effective_date%type;
2938: l_address_id per_addresses.address_id%type;
2939: l_person_type per_person_types.system_person_type%type;
2940: --l_sec_assignmemt_id per_assignments_f.assignment_id%type;
2941: --l_assignment_status_type_id per_assignment_status_types.assignment_status_type_id%type;
2942: --
2943: l_asg_trm_eff_start_date per_assignments_f.effective_start_date%type;
2944: l_asg_trm_eff_end_date per_assignments_f.effective_end_date%type;
2945: l_emp_trm_eff_end_date per_assignments_f.effective_end_date%type;
3012: l_result_code varchar2(100);
3013: l_address_data per_addresses%rowtype;
3014: l_hr_user_type varchar2(20);
3015:
3016: l_old_system_status per_assignment_status_types.per_system_status%type;
3017:
3018: l_remark_id ghr_remarks.remark_id%type;
3019: l_remark_description ghr_remarks.description%type;
3020: l_pa_remark_id ghr_pa_remarks.pa_remark_id%type;
3278: select ast.user_status,
3279: ast.per_system_status,
3280: asg.effective_start_date
3281: from
3282: per_assignment_status_types ast,
3283: per_all_assignments_f asg
3284: where asg.assignment_id = l_assignment_id
3285: and ast.assignment_status_type_id = asg.assignment_status_type_id
3286: and g_effective_date
3301: --- Bug 5923426 start
3302:
3303: Cursor c_user_old_status is
3304: Select past.per_system_status ,asg.assignment_status_type_id
3305: from per_assignments_f asg,per_assignment_status_types past
3306: where asg.assignment_id = l_assignment_id
3307: and asg.assignment_status_type_id = past.assignment_status_type_id
3308: order by asg.effective_start_date desc;
3309: