[Home] [Help]
324: ,p_rating_of_record_pattern OUT NOCOPY VARCHAR2
325: ,p_rating_of_record_period OUT NOCOPY DATE) IS
326: --
327: l_special_info ghr_api.special_information_type;
328: l_emp_number per_people_f.employee_number%TYPE;
329: CURSOR c_per IS
330: SELECT per.employee_number
331: FROM per_people_f per
332: WHERE per.person_id = p_person_id
327: l_special_info ghr_api.special_information_type;
328: l_emp_number per_people_f.employee_number%TYPE;
329: CURSOR c_per IS
330: SELECT per.employee_number
331: FROM per_people_f per
332: WHERE per.person_id = p_person_id
333: AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN per.effective_start_date
334: AND per.effective_end_date;
335: BEGIN
1181: -- This cursor drives of the PAR table to first see which PA Requests had 'Update HR'
1182: -- selected by a user in the given period
1183: -- may as well select everyting from the PA Request table (saves going back!)
1184:
1185: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
1186: -- records based on business group id/security group id
1187: -- Not added outer join for per_people_f because we are expecting existence of person_id
1188: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
1189:
1183: -- may as well select everyting from the PA Request table (saves going back!)
1184:
1185: -- 3/13/02 -- Joined the ghr_pa_requests table with per_people_f table to view the
1186: -- records based on business group id/security group id
1187: -- Not added outer join for per_people_f because we are expecting existence of person_id
1188: -- in ghr_pa_requests for the actions with status in ('UPDATE_HR_COMPLETE','FUTURE_ACTION')
1189:
1190: -- 24-OCT-2002 JH truncated sf50_approval_date because it does contain time on the db.
1191: -- which causes some rows to not be included on last day.
1192:
1193: CURSOR cur_get_pars IS
1194: SELECT par.*
1195: FROM ghr_pa_requests par,
1196: per_people_f per
1197: WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
1198: AND par.person_id = per.person_id
1199: AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
1200: AND per.effective_end_date
1209: --Bug # 10158391 added the union condition to consider the records approved in last month and effective in the reporting period
1210: UNION
1211: SELECT par.*
1212: FROM ghr_pa_requests par,
1213: per_people_f per
1214: WHERE NVL(par.agency_code,par.from_agency_code) LIKE p_agency
1215: AND par.person_id = per.person_id
1216: AND trunc(par.sf50_approval_date) BETWEEN per.effective_start_date
1217: AND per.effective_end_date
1303: -- For Dual Actions PRD is becoming null so preserving it using a local variable.
1304: l_pay_rate_determinant ghr_pa_requests.pay_rate_determinant%TYPE;
1305:
1306: --Bug # 9329643
1307: l_employee_number per_people_f.employee_number%TYPE;
1308:
1309: BEGIN
1310: --
1311: l_records_found:=FALSE;