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 169: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;

165: , p_rpas in out nocopy number
166: )
167: IS
168: l_proc varchar2(61) := g_package_name || 'CAN_CANCEL_OR_CORRECT';
169: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
170: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
171:
172:
173: --5725885 vmididho to improve performance

Line 170: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;

166: )
167: IS
168: l_proc varchar2(61) := g_package_name || 'CAN_CANCEL_OR_CORRECT';
169: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
170: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
171:
172:
173: --5725885 vmididho to improve performance
174: --5725885 modified the cursor instead of using view changed to use the query getting

Line 181: FROM GHR_PA_REQUESTS_V1

177: -- , p_pa_request_id IN NUMBER
178: , p_effective_date IN DATE) IS
179: --SELECT *
180: SELECT PA_NOTIFICATION_ID
181: FROM GHR_PA_REQUESTS_V1
182: WHERE person_id = p_person_id
183: AND effective_date >= p_effective_date;*/
184:
185: CURSOR c_pa_requests(p_person_id IN NUMBER)

Line 188: from ghr_pa_requests par

184:
185: CURSOR c_pa_requests(p_person_id IN NUMBER)
186: IS
187: select PA_NOTIFICATION_ID,effective_date
188: from ghr_pa_requests par
189: where (level = 1 and pa_notification_id is not null) or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
190: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
191: start with altered_pa_request_id is null
192: and person_id = p_person_id

Line 200: from ghr_pa_requests par

196: and
197: prior decode(first_noa_code, '002', second_noa_code, '001' , second_noa_code ,first_noa_code) = second_noa_code
198: UNION ALL
199: select PA_NOTIFICATION_ID,effective_date
200: from ghr_pa_requests par
201: where (level = 1 and pa_notification_id is not null)
202: or (level > 1 and ( nvl(status, 'CANCELED') <> 'CANCELED'
203: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL' AND first_noa_code <> '001' ) )
204: start with altered_pa_request_id is null

Line 215: from ghr_pa_requests pa

211:
212: -- Bug# 4005884 Added cursor to verify the prior person type.
213: Cursor c_prior_person_type(p_Person_id NUMBER, p_effective_Date DATE) is
214: select 'EX_EMP' system_person_type
215: from ghr_pa_requests pa
216: where pa.noa_family_code = 'SEPARATION'
217: and pa.effective_date < p_effective_date
218: and pa.person_id = p_person_id
219: and exists (select '1'

Line 310: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE

306: -- ---------------------------------
307: p_pa_request_id in number
308: , p_which_noa in number
309: , p_row_id in varchar
310: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
311: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
312: , p_number_of_requests in out nocopy number
313: )
314: is

Line 311: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE

307: p_pa_request_id in number
308: , p_which_noa in number
309: , p_row_id in varchar
310: , p_first_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
311: , p_last_pa_request_rec in out nocopy GHR_PA_REQUESTS%ROWTYPE
312: , p_number_of_requests in out nocopy number
313: )
314: is
315: l_proc varchar2(61) := g_package_name || 'FIND_LAST_REQUEST';

Line 316: l_pa_req GHR_PA_REQUESTS%ROWTYPE;

312: , p_number_of_requests in out nocopy number
313: )
314: is
315: l_proc varchar2(61) := g_package_name || 'FIND_LAST_REQUEST';
316: l_pa_req GHR_PA_REQUESTS%ROWTYPE;
317:
318: -- 5925784 The below cursor is splitted into two due to UnionALL performance in 10g
319: /* CURSOR c_get_last_request IS
320: SELECT

Line 337: FROM ghr_pa_requests par

333: , ROWID ROW_ID
334: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
335: , altered_pa_request_id
336: , status
337: FROM ghr_pa_requests par
338: WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
339: or (level > 1
340: and ( nvl(status, 'CANCELED') <> 'CANCELED'
341: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 368: FROM ghr_pa_requests par

364: , par.ROWID
365: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
366: , altered_pa_request_id
367: , par.status
368: FROM ghr_pa_requests par
369: WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
370: or (level > 1
371: and ( nvl(status, 'CANCELED') <> 'CANCELED'
372: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 405: FROM ghr_pa_requests par

401: , ROWID ROW_ID
402: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed') action_type
403: , altered_pa_request_id
404: , status
405: FROM ghr_pa_requests par
406: WHERE (LEVEL = 1 and pa_notification_id IS NOT NULL)
407: or (level > 1
408: and ( nvl(status, 'CANCELED') <> 'CANCELED'
409: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 438: FROM ghr_pa_requests par

434: , par.ROWID
435: , DECODE(pa_notification_id, NULL, 'Routed', 'Processed')
436: , altered_pa_request_id
437: , par.status
438: FROM ghr_pa_requests par
439: WHERE (LEVEL = 1 AND pa_notification_id IS NOT NULL)
440: or (level > 1
441: and ( nvl(status, 'CANCELED') <> 'CANCELED'
442: AND nvl(second_noa_cancel_or_correct, 'NULL') <> 'CANCEL'

Line 459: FROM ghr_pa_requests

455:
456: --
457: CURSOR c_pa_request(p_rowid IN ROWID) IS
458: SELECT *
459: FROM ghr_pa_requests
460: WHERE ROWID = p_rowid;
461: --
462: l_last_row c_get_last_request_1%ROWTYPE;
463: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

Line 463: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

459: FROM ghr_pa_requests
460: WHERE ROWID = p_rowid;
461: --
462: l_last_row c_get_last_request_1%ROWTYPE;
463: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
464: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
465: l_number_of_requests number;
466:
467: BEGIN

Line 464: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;

460: WHERE ROWID = p_rowid;
461: --
462: l_last_row c_get_last_request_1%ROWTYPE;
463: l_first_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
464: l_last_pa_request_rec GHR_PA_REQUESTS%ROWTYPE;
465: l_number_of_requests number;
466:
467: BEGIN
468:

Line 535: l_pa_req_rec ghr_pa_requests%rowtype;

531: return number
532: is
533: --
534: l_proc varchar2(61) := g_package_name || 'GHR_CANCEL_SF52';
535: l_pa_req_rec ghr_pa_requests%rowtype;
536: l_par_object_version_number number;
537: l_1_pa_routing_history_id number;
538: l_1_prh_object_version_number number;
539: l_2_pa_routing_history_id number;

Line 561: from ghr_pa_requests

557: --End Bug# 8344672
558: --
559: cursor c_pa_req is
560: select *
561: from ghr_pa_requests
562: where rowid = chartorowid(p_row_id);
563: --
564: cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
565: select nature_of_action_id, description

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

648: close c_noa_code;
649: --
650: l_pa_req_rec.altered_pa_request_id := p_pa_request_id;
651: l_pa_req_rec.noa_family_code :=
652: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
653: -- l_pa_req_rec.notification_id := null;
654: --
655: -- Added these checks for bug #2951865
656: -- Check if additional_info person is present

Line 1072: l_pa_req_rec ghr_pa_requests%rowtype;

1068: return number -- PA Request ID
1069: is
1070: --
1071: l_proc varchar2(61) := g_package_name || 'GHR_CORRECT_SF52';
1072: l_pa_req_rec ghr_pa_requests%rowtype;
1073: l_noa_cancel_or_correct varchar2(10);
1074: l_1_pa_routing_history_id number;
1075: l_1_prh_object_version_number number;
1076: l_2_pa_routing_history_id number;

Line 1082: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;

1078: l_U_PRH_OBJECT_VERSION_NUMBER number;
1079: l_I_PA_ROUTING_HISTORY_ID number;
1080: l_I_PRH_OBJECT_VERSION_NUMBER number;
1081: l_par_object_version_number number;
1082: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1083: --Begin Bug# 8344672
1084: l_first_action_la_code1 ghr_pa_request_shadow.first_action_la_code1%type;
1085: l_first_action_la_code2 ghr_pa_request_shadow.first_action_la_code2%type;
1086: l_second_action_la_code1 ghr_pa_request_shadow.second_action_la_code1%type;

Line 1098: from ghr_pa_requests

1094: -- l_U_PRH_OBJECT_VERSION_NUMBER number;
1095: --
1096: cursor c_pa_req(p_pa_request_id NUMBER) is
1097: select *
1098: from ghr_pa_requests
1099: where pa_request_id = p_pa_request_id;
1100:
1101: cursor c_retro_pa_req is
1102: select * from ghr_pa_requests

Line 1102: select * from ghr_pa_requests

1098: from ghr_pa_requests
1099: where pa_request_id = p_pa_request_id;
1100:
1101: cursor c_retro_pa_req is
1102: select * from ghr_pa_requests
1103: where pa_request_id = l_retro_pa_request_id;
1104:
1105:
1106: -- Cursor for Bug 3381960

Line 1108: l_prev_retro_pa_rec ghr_pa_requests%rowtype;

1104:
1105:
1106: -- Cursor for Bug 3381960
1107: -- Need to find the RPA previous to the cancellation action
1108: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1109: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1110: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1111: c_person_id ghr_pa_requests.person_id%type) IS
1112: SELECT *

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

1106: -- Cursor for Bug 3381960
1107: -- Need to find the RPA previous to the cancellation action
1108: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1109: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1110: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1111: c_person_id ghr_pa_requests.person_id%type) IS
1112: SELECT *
1113: FROM ghr_pa_requests
1114: WHERE person_id = c_person_id

Line 1111: c_person_id ghr_pa_requests.person_id%type) IS

1107: -- Need to find the RPA previous to the cancellation action
1108: l_prev_retro_pa_rec ghr_pa_requests%rowtype;
1109: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1110: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1111: c_person_id ghr_pa_requests.person_id%type) IS
1112: SELECT *
1113: FROM ghr_pa_requests
1114: WHERE person_id = c_person_id
1115: AND pa_notification_id IS NOT NULL

Line 1113: FROM ghr_pa_requests

1109: --Bug#4116407 Modified the cursor. Added second_noa_cancel_or_correct CONDITION.
1110: CURSOR c_pa_before_retro(c_retro_eff_date IN ghr_pa_requests.effective_date%type,
1111: c_person_id ghr_pa_requests.person_id%type) IS
1112: SELECT *
1113: FROM ghr_pa_requests
1114: WHERE person_id = c_person_id
1115: AND pa_notification_id IS NOT NULL
1116: AND effective_date < c_retro_eff_date
1117: AND first_noa_code <> '001'

Line 1125: from ghr_pa_requests

1121:
1122: cursor c_pa_req1(p_par_id number ) is
1123: select pa_notification_id,noa_family_code,
1124: second_noa_code
1125: from ghr_pa_requests
1126: where pa_request_id = p_par_id;
1127: --
1128: cursor C_noa_id(p_noa_code varchar2, p_effective_date date) is
1129: select nature_of_action_id, description

Line 1154: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;

1150: --
1151:
1152:
1153: --
1154: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
1155: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
1156: l_corrections NUMBER;
1157: l_which_noa NUMBER;
1158: l_dummy_number NUMBER;

Line 1155: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;

1151:
1152:
1153: --
1154: l_first_pa_request_rec ghr_pa_requests%ROWTYPE;
1155: l_last_pa_request_rec ghr_pa_requests%ROWTYPE;
1156: l_corrections NUMBER;
1157: l_which_noa NUMBER;
1158: l_dummy_number NUMBER;
1159: l_dummy_varchar VARCHAR2(10);

Line 1162: l_effective_date ghr_pa_requests.effective_date%type;

1158: l_dummy_number NUMBER;
1159: l_dummy_varchar VARCHAR2(10);
1160: l_noa_id_correct NUMBER;
1161: l_altered_pa_request_id NUMBER;
1162: l_effective_date ghr_pa_requests.effective_date%type;
1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;

Line 1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;

1159: l_dummy_varchar VARCHAR2(10);
1160: l_noa_id_correct NUMBER;
1161: l_altered_pa_request_id NUMBER;
1162: l_effective_date ghr_pa_requests.effective_date%type;
1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1167: l_retro_first_noa ghr_nature_of_actions.code%type;

Line 1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;

1160: l_noa_id_correct NUMBER;
1161: l_altered_pa_request_id NUMBER;
1162: l_effective_date ghr_pa_requests.effective_date%type;
1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1167: l_retro_first_noa ghr_nature_of_actions.code%type;
1168: l_retro_second_noa ghr_nature_of_actions.code%type;

Line 1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;

1161: l_altered_pa_request_id NUMBER;
1162: l_effective_date ghr_pa_requests.effective_date%type;
1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1167: l_retro_first_noa ghr_nature_of_actions.code%type;
1168: l_retro_second_noa ghr_nature_of_actions.code%type;
1169: l_ia_flag varchar2(30);

Line 1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;

1162: l_effective_date ghr_pa_requests.effective_date%type;
1163: l_to_step_or_rate ghr_pa_requests.to_step_or_rate%type;
1164: l_pa_notification_id ghr_pa_requests.pa_notification_id%type;
1165: l_noa_family_code ghr_pa_requests.noa_family_code%type;
1166: l_retro_noa_family_code ghr_pa_requests.noa_family_code%type;
1167: l_retro_first_noa ghr_nature_of_actions.code%type;
1168: l_retro_second_noa ghr_nature_of_actions.code%type;
1169: l_ia_flag varchar2(30);
1170: --bug 5172710

Line 1171: l_to_total_salary ghr_pa_requests.to_total_salary%type;

1167: l_retro_first_noa ghr_nature_of_actions.code%type;
1168: l_retro_second_noa ghr_nature_of_actions.code%type;
1169: l_ia_flag varchar2(30);
1170: --bug 5172710
1171: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1172: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;

Line 1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;

1169: l_ia_flag varchar2(30);
1170: --bug 5172710
1171: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1172: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;

Line 1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;

1170: --bug 5172710
1171: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1172: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;

Line 1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;

1171: l_to_total_salary ghr_pa_requests.to_total_salary%type;
1172: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1179:

Line 1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;

1172: -- Bug 2681842 and 3191676 Added variables for To Position Org lines
1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1179:
1180: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS

Line 1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;

1173: l_to_position_org_line1 ghr_pa_requests.to_position_org_line1%type;
1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1179:
1180: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1181: SELECT to_position_org_line1,

Line 1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;

1174: l_to_position_org_line2 ghr_pa_requests.to_position_org_line1%type;
1175: l_to_position_org_line3 ghr_pa_requests.to_position_org_line1%type;
1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1179:
1180: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1181: SELECT to_position_org_line1,
1182: to_position_org_line2,

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

1176: l_to_position_org_line4 ghr_pa_requests.to_position_org_line1%type;
1177: l_to_position_org_line5 ghr_pa_requests.to_position_org_line1%type;
1178: l_to_position_org_line6 ghr_pa_requests.to_position_org_line1%type;
1179:
1180: CURSOR c_get_pos_org_lines(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1181: SELECT to_position_org_line1,
1182: to_position_org_line2,
1183: to_position_org_line3,
1184: to_position_org_line4,

Line 1187: FROM ghr_pa_requests

1183: to_position_org_line3,
1184: to_position_org_line4,
1185: to_position_org_line5,
1186: to_position_org_line6
1187: FROM ghr_pa_requests
1188: WHERE pa_request_id = c_pa_request_id;
1189: -- End Bug 2681842 and 3191676
1190:
1191: begin

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

1274: --
1275: l_pa_req_rec.altered_pa_request_id := l_pa_req_rec.pa_request_id;
1276: l_pa_req_rec.pa_request_id := null;
1277: l_pa_req_rec.noa_family_code :=
1278: ghr_pa_requests_pkg.get_noa_pm_family(l_pa_req_rec.first_noa_id);
1279: -- --------
1280: -- Pay Calc
1281: -- --------
1282: -- l_pa_req_rec.custom_pay_calc_flag := 'Y';

Line 1875: from ghr_pa_requests

1871: --
1872:
1873: cursor c_multi_ia is
1874: select count(*)
1875: from ghr_pa_requests
1876: where pa_notification_id is not null
1877: and person_id = p_person_id
1878: and effective_date = p_effective_date
1879: group by person_id

Line 1884: ghr_pa_requests a

1880: having count(*) > 1
1881: ;
1882: cursor c_out_of_seq is
1883: select 'Y' from
1884: ghr_pa_requests a
1885: where person_id = p_person_id
1886: and effective_date = p_effective_date
1887: and pa_notification_id is not null
1888: and pa_notification_id < p_pa_notification_id

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

1885: where person_id = p_person_id
1886: and effective_date = p_effective_date
1887: and pa_notification_id is not null
1888: and pa_notification_id < p_pa_notification_id
1889: and not exists ( select 'Y' from ghr_pa_requests b
1890: where b.person_id = a.person_id
1891: and b.pa_notification_id is not null
1892: and b.pa_notification_id > a.pa_notification_id
1893: and b.altered_pa_request_id = a.pa_request_id

Line 1899: l_effective_date ghr_pa_requests.effective_date%type;

1895: and b.second_noa_code = a.first_noa_code )
1896: ;
1897:
1898: l_proc varchar2(61) := g_package_name || 'chk_intervene_seq';
1899: l_effective_date ghr_pa_requests.effective_date%type;
1900: l_retro_first_noa ghr_nature_of_actions.code%type;
1901: l_retro_second_noa ghr_nature_of_actions.code%type;
1902: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1903: BEGIN

Line 1902: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;

1898: l_proc varchar2(61) := g_package_name || 'chk_intervene_seq';
1899: l_effective_date ghr_pa_requests.effective_date%type;
1900: l_retro_first_noa ghr_nature_of_actions.code%type;
1901: l_retro_second_noa ghr_nature_of_actions.code%type;
1902: l_retro_pa_request_id ghr_pa_requests.pa_request_id%type;
1903: BEGIN
1904: hr_utility.set_location('Entering '|| l_proc, 5);
1905: -- Determine Intervening Action
1906: --BUG # 7216635 Added the parameter p_noa_id_correct

Line 1951: from ghr_pa_requests a

1947: cursor c_determine_ia is
1948: select effective_date,first_noa_code,
1949: second_noa_code,pa_notification_id,pa_request_id,
1950: first_noa_id,second_noa_id,rpa_type,mass_action_id
1951: from ghr_pa_requests a
1952: where pa_notification_id is not null
1953: and person_id = p_person_id
1954: and pa_notification_id > p_pa_notification_id
1955: and effective_date <= p_effective_date

Line 1959: from ghr_pa_requests b

1955: and effective_date <= p_effective_date
1956: -- and first_noa_code not in ('001') -- Exclude all cancellations
1957: and pa_request_id not in ( -- Exclude all cancellation of correction actions
1958: select nvl(altered_pa_request_id,0)
1959: from ghr_pa_requests b
1960: where a.person_id = b.person_id
1961: and b.first_noa_code in ('001')
1962: and b.pa_notification_id is not null )
1963: and pa_request_id not in ( -- Exclude all the corrections on the current action

Line 1965: from ghr_pa_requests c

1961: and b.first_noa_code in ('001')
1962: and b.pa_notification_id is not null )
1963: and pa_request_id not in ( -- Exclude all the corrections on the current action
1964: select nvl(pa_request_id,0)
1965: from ghr_pa_requests c
1966: connect by prior pa_request_id = altered_pa_request_id
1967: start with altered_pa_request_id = p_pa_request_id )
1968: order by pa_notification_id desc;
1969:

Line 1987: from ghr_pa_requests

1983: --6850492
1984: cursor get_parent_action(p_pa_request_id in number)
1985: is
1986: select min(pa_request_id)
1987: from ghr_pa_requests
1988: where pa_notification_id is not null
1989: and altered_pa_request_id is null
1990: start with pa_request_id = p_pa_request_id
1991: connect by pa_request_id = prior altered_pa_request_id;

Line 2000: l_ia_noac ghr_pa_requests.first_noa_code%type;

1996:
1997: l_proc varchar2(61) := g_package_name || 'determine_ia';
1998: chk_order boolean;
1999:
2000: l_ia_noac ghr_pa_requests.first_noa_code%type;
2001:
2002: BEGIN
2003: hr_utility.set_location('Entering '|| l_proc, 5);
2004:

Line 2070: l_ovn1 ghr_pa_requests.object_version_number%type;

2066: p_first_dual_action_id in number,
2067: p_second_dual_action_id in number)
2068: is
2069:
2070: l_ovn1 ghr_pa_requests.object_version_number%type;
2071: l_ovn2 ghr_pa_requests.object_version_number%type;
2072:
2073: cursor get_ovn(p_pa_request_id in number)
2074: is

Line 2071: l_ovn2 ghr_pa_requests.object_version_number%type;

2067: p_second_dual_action_id in number)
2068: is
2069:
2070: l_ovn1 ghr_pa_requests.object_version_number%type;
2071: l_ovn2 ghr_pa_requests.object_version_number%type;
2072:
2073: cursor get_ovn(p_pa_request_id in number)
2074: is
2075: select object_version_number

Line 2076: from ghr_pa_requests

2072:
2073: cursor get_ovn(p_pa_request_id in number)
2074: is
2075: select object_version_number
2076: from ghr_pa_requests
2077: where pa_request_id = p_pa_request_id;
2078:
2079: begin
2080: