[Home] [Help]
128: cursor cur_jobs is
129: select job_id,
130: job_information7
131: from per_jobs,
132: hr_lookups
133: where lookup_type = 'EEOG'
134: and upper(ltrim(rtrim(lookup_code)))
135: =upper(ltrim(rtrim(job_information1)))
136: and upper(ltrim(rtrim(job_information_category))) = 'CA'
146:
147: /*Cursor for checking the NAIC code validation */
148: cursor cur_naic_code (p_keyflex_id number) is
149: select hl.lookup_code
150: from hr_lookups hl,
151: hr_soft_coding_keyflex hsck
152: where hsck.soft_coding_keyflex_id = p_keyflex_id and
153: hl.lookup_type = 'NAIC' and
154: (
232: paf.effective_start_date st_dt
233: from
234: per_all_assignments_f paf,
235: hr_soft_coding_keyflex hsck,
236: hr_lookups hl
237: where paf.person_id = p_person_id and
238: paf.business_group_id = p_business_group_id and
239: paf.effective_end_date >= p_start_date and
240: paf.primary_flag = 'Y' and
361: per_all_assignments_f paf,
362: per_all_people_f ppf,
363: per_pay_proposals_v2 pppv,
364: per_assignment_status_types past,
365: hr_lookups hrl
366: where paf.pay_basis_id = ppb.pay_basis_id and
367: paf.person_id=ppf.person_id and
368: past.assignment_status_type_id = paf.assignment_status_type_id and
369: pppv.assignment_id = paf.assignment_id and
377: pppv.approved = 'Y' and -- to calculate only approved salaries
378: paf.effective_start_date = ( select max(effective_start_date)
379: from per_all_assignments_f paf1,
380: per_assignment_status_types past1,
381: hr_lookups hrl1
382: where paf1.business_group_id = p_business_group_id
383: and paf1.assignment_id = pppv.assignment_id
384: and paf1.person_id = p_person_id
385: and past1.assignment_status_type_id = paf1.assignment_status_type_id
418: 'NT','21',
419: 'NU','22',
420: '98')
421: from hr_locations_all hloc,
422: hr_lookups hl1
423: where hloc.location_id = p_location_code
424: and hl1.lookup_code = hloc.region_1
425: and hl1.lookup_type = 'CA_PROVINCE';
426:
441: 'NEW BRUNSWICK LESS CMA','88','NOVA SCOTIA LESS CMA','89',
442: 'NORTH WEST TERRITORIES','90','NEWFOUNDLAND LESS CMA','91',
443: 'NUNAVUT','92','ONTORIA LESS CMA','93','PRINCE EDWARD ISLAND','94',
444: 'QUEBEC LESS CMA','95','SASKATCHEWAN LESS CMA','96','YUKON TERRITORY','97','OUTSIDE OF CANADA','98')
445: FROM hr_lookups hl1,
446: hr_locations_all hloc
447: WHERE hloc.location_id = p_location_code
448: AND hloc.region_2 = hl1.lookup_code
449: AND hl1.lookup_type = 'CA_CMA';