eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GHR_APPROVED_PA_REQUESTS dependencies on GHR_PA_REQUESTS

Line 160: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;

156: , p_rpas in out nocopy number
157: )
158: IS
159: l_proc varchar2(61) := g_package_name || 'CAN_CANCEL_OR_CORRECT';
160: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
161: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
162:
163:
164: --5725885 vmididho to improve performance

Line 161: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;

157: )
158: IS
159: l_proc varchar2(61) := g_package_name || 'CAN_CANCEL_OR_CORRECT';
160: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
161: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
162:
163:
164: --5725885 vmididho to improve performance
165: --5725885 modified the cursor instead of using view changed to use the query getting

Line 172: FROM GHR_PA_REQUESTS_V1

168: -- , p_pa_request_id IN NUMBER
169: , p_effective_date IN DATE) IS
170: --SELECT *
171: SELECT PA_NOTIFICATION_ID
172: FROM GHR_PA_REQUESTS_V1
173: WHERE person_id = p_person_id
174: AND effective_date >= p_effective_date;*/
175:
176: CURSOR c_pa_requests(p_person_id IN NUMBER)

Line 179: from ghr_pa_requests par

175:
176: CURSOR c_pa_requests(p_person_id IN NUMBER)
177: IS
178: select PA_NOTIFICATION_ID,effective_date
179: from ghr_pa_requests par
180: where (level = 1 and pa_notification_id is not null) or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
181: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
182: start with altered_pa_request_id is null
183: and person_id = p_person_id

Line 191: from ghr_pa_requests par

187: and
188: prior decode(first_noa_code, '002', second_noa_code, '001' , second_noa_code ,first_noa_code) = second_noa_code
189: UNION ALL
190: select PA_NOTIFICATION_ID,effective_date
191: from ghr_pa_requests par
192: where (level = 1 and pa_notification_id is not null)
193: or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
194: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
195: start with altered_pa_request_id is null

Line 206: from ghr_pa_requests pa

202:
203: -- Bug# 4005884 Added cursor to verify the prior person type.
204: Cursor c_prior_person_type(p_Person_id NUMBER, p_effective_Date DATE) is
205: select 'EX_EMP' system_person_type
206: from ghr_pa_requests pa
207: where pa.noa_family_code = 'SEPARATION'
208: and pa.effective_date < p_effective_date
209: and pa.person_id = p_person_id
210: and exists (select '1'

Line 301: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE

297: -- ---------------------------------
298: p_pa_request_id in number
299: , p_which_noa in number
300: , p_row_id in varchar
301: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
302: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
303: , p_number_of_requests in out nocopy number
304: )
305: is

Line 302: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE

298: p_pa_request_id in number
299: , p_which_noa in number
300: , p_row_id in varchar
301: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
302: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
303: , p_number_of_requests in out nocopy number
304: )
305: is
306: l_proc varchar2(61) := g_package_name || 'FIND_LAST_REQUEST';

Line 307: l_pa_req GHR_PA_REQUESTS%ROWTYPE;

303: , p_number_of_requests in out nocopy number
304: )
305: is
306: l_proc varchar2(61) := g_package_name || 'FIND_LAST_REQUEST';
307: l_pa_req GHR_PA_REQUESTS%ROWTYPE;
308:
309: -- 5925784 The below cursor is splitted into two due to UnionALL performance in 10g
310: /* CURSOR c_get_last_request IS
311: SELECT

Line 328: FROM ghr_pa_requests par

324: , ROWID ROW_ID
325: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
326: , altered_pa_request_id
327: , status
328: FROM ghr_pa_requests par
329: WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
330: or (level > 1
331: and ( nvl(status, 'CANCELED') <> 'CANCELED'
332: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 359: FROM ghr_pa_requests par

355: , par.ROWID
356: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
357: , altered_pa_request_id
358: , par.status
359: FROM ghr_pa_requests par
360: WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
361: or (level > 1
362: and ( nvl(status, 'CANCELED') <> 'CANCELED'
363: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 396: FROM ghr_pa_requests par

392: , ROWID ROW_ID
393: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
394: , altered_pa_request_id
395: , status
396: FROM ghr_pa_requests par
397: WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
398: or (level > 1
399: and ( nvl(status, 'CANCELED') <> 'CANCELED'
400: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 429: FROM ghr_pa_requests par

425: , par.ROWID
426: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
427: , altered_pa_request_id
428: , par.status
429: FROM ghr_pa_requests par
430: WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
431: or (level > 1
432: and ( nvl(status, 'CANCELED') <> 'CANCELED'
433: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 450: FROM ghr_pa_requests

446:
447: --
448: CURSOR c_pa_request(p_rowid IN ROWID) IS
449: SELECT *
450: FROM ghr_pa_requests
451: WHERE ROWID = p_rowid;
452: --
453: l_last_row c_get_last_request_1%ROWTYPE;
454: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

Line 454: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

450: FROM ghr_pa_requests
451: WHERE ROWID = p_rowid;
452: --
453: l_last_row c_get_last_request_1%ROWTYPE;
454: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
455: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
456: l_number_of_requests number;
457:
458: BEGIN

Line 455: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

451: WHERE ROWID = p_rowid;
452: --
453: l_last_row c_get_last_request_1%ROWTYPE;
454: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
455: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
456: l_number_of_requests number;
457:
458: BEGIN
459:

Line 526: l_pa_req_rec ghr_pa_requests%rowtype;

522: return number
523: is
524: --
525: l_proc varchar2(61) := g_package_name || 'GHR_CANCEL_SF52';
526: l_pa_req_rec ghr_pa_requests%rowtype;
527: l_par_object_version_number number;
528: l_1_pa_routing_history_id number;
529: l_1_prh_object_version_number number;
530: l_2_pa_routing_history_id number;

Line 541: from ghr_pa_requests

537: l_dummy number;
538: --
539: cursor c_pa_req is
540: select *
541: from ghr_pa_requests
542: where rowid = chartorowid(p_row_id);
543: --
544: cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
545: select nature_of_action_id, description

Line 613: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);

609: close c_noa_code;
610: --
611: l_pa_req_rec.altered_pa_request_id := p_pa_request_id;
612: l_pa_req_rec.noa_family_code :=
613: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
614: -- l_pa_req_rec.notification_id := null;
615: --
616: -- Added these checks for bug #2951865
617: -- Check if additional_info person is present

Line 1027: l_pa_req_rec ghr_pa_requests%rowtype;

1023: return number -- PA Request ID
1024: is
1025: --
1026: l_proc varchar2(61) := g_package_name || 'GHR_CORRECT_SF52';
1027: l_pa_req_rec ghr_pa_requests%rowtype;
1028: l_noa_cancel_or_correct varchar2(10);
1029: l_1_pa_routing_history_id number;
1030: l_1_prh_object_version_number number;
1031: l_2_pa_routing_history_id number;

Line 1037: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;

1033: l_U_PRH_OBJECT_VERSION_NUMBER number;
1034: l_I_PA_ROUTING_HISTORY_ID number;
1035: l_I_PRH_OBJECT_VERSION_NUMBER number;
1036: l_par_object_version_number number;
1037: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1038: -- l_U_PRH_OBJECT_VERSION_NUMBER number;
1039: --
1040: cursor c_pa_req(p_pa_request_id NUMBER) is
1041: select *

Line 1042: from ghr_pa_requests

1038: -- l_U_PRH_OBJECT_VERSION_NUMBER number;
1039: --
1040: cursor c_pa_req(p_pa_request_id NUMBER) is
1041: select *
1042: from ghr_pa_requests
1043: where pa_request_id = p_pa_request_id;
1044:
1045: cursor c_retro_pa_req is
1046: select * from ghr_pa_requests

Line 1046: select * from ghr_pa_requests

1042: from ghr_pa_requests
1043: where pa_request_id = p_pa_request_id;
1044:
1045: cursor c_retro_pa_req is
1046: select * from ghr_pa_requests
1047: where pa_request_id = l_retro_pa_request_id;
1048:
1049:
1050: -- Cursor for Bug 3381960

Line 1052: l_prev_retro_pa_rec ghr_pa_requests%rowtype;

1048:
1049:
1050: -- Cursor for Bug 3381960
1051: -- Need to find the RPA previous to the cancellation action
1052: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1053: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1054: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1055: c_person_id ghr_pa_requests.person_id%type) IS
1056: SELECT *

Line 1054: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,

1050: -- Cursor for Bug 3381960
1051: -- Need to find the RPA previous to the cancellation action
1052: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1053: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1054: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1055: c_person_id ghr_pa_requests.person_id%type) IS
1056: SELECT *
1057: FROM ghr_pa_requests
1058: WHERE person_id = c_person_id

Line 1055: c_person_id ghr_pa_requests.person_id%type) IS

1051: -- Need to find the RPA previous to the cancellation action
1052: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1053: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1054: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1055: c_person_id ghr_pa_requests.person_id%type) IS
1056: SELECT *
1057: FROM ghr_pa_requests
1058: WHERE person_id = c_person_id
1059: AND pa_notification_id IS NOT NULL

Line 1057: FROM ghr_pa_requests

1053: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1054: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1055: c_person_id ghr_pa_requests.person_id%type) IS
1056: SELECT *
1057: FROM ghr_pa_requests
1058: WHERE person_id = c_person_id
1059: AND pa_notification_id IS NOT NULL
1060: AND effective_date < c_retro_eff_date
1061: AND first_noa_code <> '001'

Line 1069: from ghr_pa_requests

1065:
1066: cursor c_pa_req1(p_par_id number ) is
1067: select pa_notification_id,noa_family_code,
1068: second_noa_code
1069: from ghr_pa_requests
1070: where pa_request_id = p_par_id;
1071: --
1072: cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
1073: select nature_of_action_id, description

Line 1098: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;

1094: --
1095:
1096:
1097: --
1098: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
1099: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
1100: l_corrections NUMBER;
1101: l_which_noa NUMBER;
1102: l_dummy_number NUMBER;

Line 1099: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;

1095:
1096:
1097: --
1098: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
1099: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
1100: l_corrections NUMBER;
1101: l_which_noa NUMBER;
1102: l_dummy_number NUMBER;
1103: l_dummy_varchar VARCHAR2(10);

Line 1106: l_effective_date ghr_pa_requests.effective_date%type;

1102: l_dummy_number NUMBER;
1103: l_dummy_varchar VARCHAR2(10);
1104: l_noa_id_correct NUMBER;
1105: l_altered_pa_request_id NUMBER;
1106: l_effective_date ghr_pa_requests.effective_date%type;
1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;

Line 1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;

1103: l_dummy_varchar VARCHAR2(10);
1104: l_noa_id_correct NUMBER;
1105: l_altered_pa_request_id NUMBER;
1106: l_effective_date ghr_pa_requests.effective_date%type;
1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1111: l_retro_first_noa ghr_nature_of_actions.code%type;

Line 1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;

1104: l_noa_id_correct NUMBER;
1105: l_altered_pa_request_id NUMBER;
1106: l_effective_date ghr_pa_requests.effective_date%type;
1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1111: l_retro_first_noa ghr_nature_of_actions.code%type;
1112: l_retro_second_noa ghr_nature_of_actions.code%type;

Line 1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;

1105: l_altered_pa_request_id NUMBER;
1106: l_effective_date ghr_pa_requests.effective_date%type;
1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1111: l_retro_first_noa ghr_nature_of_actions.code%type;
1112: l_retro_second_noa ghr_nature_of_actions.code%type;
1113: l_ia_flag varchar2(30);

Line 1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;

1106: l_effective_date ghr_pa_requests.effective_date%type;
1107: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1108: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1109: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1110: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1111: l_retro_first_noa ghr_nature_of_actions.code%type;
1112: l_retro_second_noa ghr_nature_of_actions.code%type;
1113: l_ia_flag varchar2(30);
1114: --bug 5172710

Line 1115: l_to_total_salary ghr_pa_requests.to_total_salary%type;

1111: l_retro_first_noa ghr_nature_of_actions.code%type;
1112: l_retro_second_noa ghr_nature_of_actions.code%type;
1113: l_ia_flag varchar2(30);
1114: --bug 5172710
1115: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1116: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;

Line 1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;

1113: l_ia_flag varchar2(30);
1114: --bug 5172710
1115: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1116: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;

Line 1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;

1114: --bug 5172710
1115: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1116: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;

Line 1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;

1115: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1116: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123:

Line 1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;

1116: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123:
1124: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS

Line 1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;

1117: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123:
1124: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1125: SELECT to_position_org_line1,

Line 1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;

1118: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1119: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123:
1124: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1125: SELECT to_position_org_line1,
1126: to_position_org_line2,

Line 1124: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS

1120: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1121: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1122: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1123:
1124: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1125: SELECT to_position_org_line1,
1126: to_position_org_line2,
1127: to_position_org_line3,
1128: to_position_org_line4,

Line 1131: FROM ghr_pa_requests

1127: to_position_org_line3,
1128: to_position_org_line4,
1129: to_position_org_line5,
1130: to_position_org_line6
1131: FROM ghr_pa_requests
1132: WHERE pa_request_id = c_pa_request_id;
1133: -- End Bug 2681842 and 3191676
1134:
1135: begin

Line 1203: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);

1199: --
1200: l_pa_req_rec.altered_pa_request_id := l_pa_req_rec.pa_request_id;
1201: l_pa_req_rec.pa_request_id := null;
1202: l_pa_req_rec.noa_family_code :=
1203: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
1204: -- --------
1205: -- Pay Calc
1206: -- --------
1207: -- l_pa_req_rec.custom_pay_calc_flag := 'Y';

Line 1785: from ghr_pa_requests

1781: --
1782:
1783: cursor c_multi_ia is
1784: select count(*)
1785: from ghr_pa_requests
1786: where pa_notification_id is not null
1787: and person_id = p_person_id
1788: and effective_date = p_effective_date
1789: group by person_id

Line 1794: ghr_pa_requests a

1790: having count(*) > 1
1791: ;
1792: cursor c_out_of_seq is
1793: select 'Y' from
1794: ghr_pa_requests a
1795: where person_id = p_person_id
1796: and effective_date = p_effective_date
1797: and pa_notification_id is not null
1798: and pa_notification_id < p_pa_notification_id

Line 1799: and not exists ( select 'Y' from ghr_pa_requests b

1795: where person_id = p_person_id
1796: and effective_date = p_effective_date
1797: and pa_notification_id is not null
1798: and pa_notification_id < p_pa_notification_id
1799: and not exists ( select 'Y' from ghr_pa_requests b
1800: where b.person_id = a.person_id
1801: and b.pa_notification_id is not null
1802: and b.pa_notification_id > a.pa_notification_id
1803: and b.altered_pa_request_id = a.pa_request_id

Line 1809: l_effective_date ghr_pa_requests.effective_date%type;

1805: and b.second_noa_code = a.first_noa_code )
1806: ;
1807:
1808: l_proc varchar2(61) := g_package_name || 'chk_intervene_seq';
1809: l_effective_date ghr_pa_requests.effective_date%type;
1810: l_retro_first_noa ghr_nature_of_actions.code%type;
1811: l_retro_second_noa ghr_nature_of_actions.code%type;
1812: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1813: BEGIN

Line 1812: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;

1808: l_proc varchar2(61) := g_package_name || 'chk_intervene_seq';
1809: l_effective_date ghr_pa_requests.effective_date%type;
1810: l_retro_first_noa ghr_nature_of_actions.code%type;
1811: l_retro_second_noa ghr_nature_of_actions.code%type;
1812: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1813: BEGIN
1814: hr_utility.set_location('Entering '|| l_proc, 5);
1815: -- Determine Intervening Action
1816: --BUG # 7216635 Added the parameter p_noa_id_correct

Line 1859: from ghr_pa_requests a

1855: --
1856: cursor c_determine_ia is
1857: select effective_date,first_noa_code,
1858: second_noa_code,pa_notification_id,pa_request_id
1859: from ghr_pa_requests a
1860: where pa_notification_id is not null
1861: and person_id = p_person_id
1862: and pa_notification_id > p_pa_notification_id
1863: and effective_date <= p_effective_date

Line 1867: from ghr_pa_requests b

1863: and effective_date <= p_effective_date
1864: -- and first_noa_code not in ('001') -- Exclude all cancellations
1865: and pa_request_id not in ( -- Exclude all cancellation of correction actions
1866: select nvl(altered_pa_request_id,0)
1867: from ghr_pa_requests b
1868: where a.person_id = b.person_id
1869: and b.first_noa_code in ('001')
1870: and b.pa_notification_id is not null )
1871: and pa_request_id not in ( -- Exclude all the corrections on the current action

Line 1873: from ghr_pa_requests c

1869: and b.first_noa_code in ('001')
1870: and b.pa_notification_id is not null )
1871: and pa_request_id not in ( -- Exclude all the corrections on the current action
1872: select nvl(pa_request_id,0)
1873: from ghr_pa_requests c
1874: connect by prior pa_request_id = altered_pa_request_id
1875: start with altered_pa_request_id = p_pa_request_id )
1876: order by pa_notification_id desc;
1877: