335: national_identifier, full_name, employee_number, normal_hours,
336: hourly_salaried_code, hsc.segment3 position_code, frequency
337: from per_all_people_f papf,
338: per_all_assignments_f paaf,
339: hr_soft_coding_keyflex hsc,
340: per_assignment_status_types past
341: where papf.person_id between p_start_person and p_end_person
342: and paaf.person_id = papf.person_id
343: and paaf.business_group_id = papf.business_group_id
348: past.assignment_status_type_id
349: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
350: and paaf.assignment_id = (select min (assignment_id)
351: from per_all_assignments_f asg,
352: hr_soft_coding_keyflex hsck
353: where person_id = papf.person_id
354: and hsck.soft_coding_keyflex_id =
355: asg.soft_coding_keyflex_id
356: and hsck.segment2 =
373: national_identifier, full_name, employee_number, normal_hours,
374: hourly_salaried_code, hsc.segment3 position_code, frequency
375: from per_all_people_f papf,
376: per_all_assignments_f paaf,
377: hr_soft_coding_keyflex hsc,
378: per_assignment_status_types past
379: where paaf.person_id = papf.person_id
380: and papf.person_id between p_start_person and p_end_person
381: and paaf.business_group_id = papf.business_group_id
385: and paaf.assignment_status_type_id =
386: past.assignment_status_type_id
387: and paaf.assignment_id = (select min (assignment_id)
388: from per_all_assignments_f asg,
389: hr_soft_coding_keyflex hsck
390: where person_id = papf.person_id
391: and hsck.soft_coding_keyflex_id =
392: asg.soft_coding_keyflex_id
393: and hsck.segment2 =
491:
492: /* Cursor to get the Organization No for the Local Unit based on the soft
493: coding keyflex id */
494: cursor csr_get_org_no (
495: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
496: ) is
497: select org_information1
498: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
499: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
494: cursor csr_get_org_no (
495: p_soft_coding_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%type
496: ) is
497: select org_information1
498: from hr_organization_information hoi, hr_soft_coding_keyflex hsc
499: where org_information_context = 'NO_LOCAL_UNIT_DETAILS'
500: and hsc.segment2 = organization_id
501: and soft_coding_keyflex_id = p_soft_coding_keyflex_id;
502:
509: p_effective_date date,
510: p_job_id number
511: ) is
512: select segment3
513: from hr_soft_coding_keyflex hsc, per_all_assignments_f paaf
514: where paaf.job_id = p_job_id
515: and assignment_id = p_assignment_id
516: and p_effective_date between paaf.effective_start_date
517: and paaf.effective_end_date
553: cursor csr_get_lu_scl (
554: p_soft_coding_keyflex_id number
555: ) is
556: select nvl (segment2, '0')
557: from hr_soft_coding_keyflex
558: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
559:
560: --
561: --
563: cursor csr_get_pos_scl (
564: p_soft_coding_keyflex_id number
565: ) is
566: select nvl (segment3, 0)
567: from hr_soft_coding_keyflex
568: where soft_coding_keyflex_id = (p_soft_coding_keyflex_id);
569:
570: --
571: --
686: p_assignment_id per_all_assignments_f.assignment_id%type,
687: p_local_unit hr_all_organization_units.organization_id%type)
688: is
689: select assignment_id
690: from per_all_assignments_f paaf ,hr_soft_coding_keyflex hsck
691: where person_id = p_person_id
692: and assignment_id <> p_assignment_id
693: and hsck.segment2 = to_char (p_local_unit)
694: and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
700: p_local_unit hr_all_organization_units.organization_id%type)
701: is
702: select normal_hours,
703: hourly_salaried_code, hsc.segment3 position_code, frequency
704: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
705: where paaf.assignment_id = p_assignment_id
706: and hsc.segment2 = to_char (p_local_unit)
707: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
708: and p_effective_date between paaf.effective_start_date and paaf.effective_End_date;
780: l_job_id number;
781: l_empl_start_date date;
782: l_old_scl varchar2 (30);
783: l_new_scl varchar2 (30);
784: l_new_lu hr_soft_coding_keyflex.segment3%type;
785: l_old_lu hr_soft_coding_keyflex.segment3%type;
786: l_sickness_unpaid_start date;
787: l_sickness_unpaid_end date;
788: l_prev_hour_flag char (1);
781: l_empl_start_date date;
782: l_old_scl varchar2 (30);
783: l_new_scl varchar2 (30);
784: l_new_lu hr_soft_coding_keyflex.segment3%type;
785: l_old_lu hr_soft_coding_keyflex.segment3%type;
786: l_sickness_unpaid_start date;
787: l_sickness_unpaid_end date;
788: l_prev_hour_flag char (1);
789: /* Flag to set whether the hours have got changed during the period or not*/
2534: p_effective_date date,
2535: p_local_unit number
2536: ) is
2537: select normal_hours, frequency
2538: from per_all_assignments_f paaf, hr_soft_coding_keyflex hsc
2539: where paaf.person_id = p_person_id
2540: and paaf.assignment_id <> p_assignment_id
2541: and paaf.normal_hours is not null
2542: and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id