[Home] [Help]
186: p_people_group_id number
187: ) is
188: select ASG.effective_start_date,
189: ASG.effective_end_date
190: from per_all_assignments_f ASG
191: where ASG.assignment_id = p_assignment_id
192: and ASG.assignment_type = 'E'
193: and ASG.effective_start_date <= p_range_end_date
194: and ASG.effective_end_date >= p_range_start_date
428: select asg.effective_start_date,
429: asg.effective_end_date,
430: asg.people_group_id,
431: ppg.id_flex_num
432: from per_all_assignments_f asg,
433: pay_people_groups ppg
434: where asg.assignment_id = p_assignment_id
435: and asg.people_group_id is not null
436: and asg.assignment_type not in ('A' ,'O') -- non-applicant assignments only
877: (pa.date_earned between p_validation_start_date
878: and p_validation_end_date))
879: and not (exists
880: (select null
881: from per_all_assignments_f asg
882: where asg.assignment_id = p_assignment_id
883: and pa.effective_date
884: between asg.effective_start_date
885: and asg.effective_end_date
885: and asg.effective_end_date
886: and asg.payroll_id + 0 = p_payroll_id)
887: and exists
888: (select null
889: from per_all_assignments_f asg
890: where asg.assignment_id = p_assignment_id
891: and nvl(pa.date_earned,pa.effective_date)
892: between asg.effective_start_date
893: and asg.effective_end_date
897: select 'Y'
898: from sys.dual
899: where not exists
900: (select null
901: from per_all_assignments_f asg
902: where asg.assignment_id = p_assignment_id
903: and p_date
904: between asg.effective_start_date
905: and asg.effective_end_date
1034: -- (pa.date_earned between p_validation_start_date
1035: -- and p_validation_end_date))
1036: -- and not (exists
1037: -- (select null
1038: -- from per_all_assignments_f asg
1039: -- where asg.assignment_id = p_assignment_id
1040: -- and pa.effective_date
1041: -- between asg.effective_start_date
1042: -- and asg.effective_end_date
1042: -- and asg.effective_end_date
1043: -- and asg.payroll_id + 0 = p_payroll_id)
1044: -- and exists
1045: -- (select null
1046: -- from per_all_assignments_f asg
1047: -- where asg.assignment_id = p_assignment_id
1048: -- and nvl(pa.date_earned,pa.effective_date)
1049: -- between asg.effective_start_date
1050: -- and asg.effective_end_date
1820: asg.employment_category,
1821: asg.payroll_id,
1822: asg.pay_basis_id,
1823: asg.business_group_id
1824: from per_all_assignments_f asg
1825: where asg.assignment_id = p_assignment_id
1826: and p_validation_start_date between
1827: asg.effective_start_date and asg.effective_end_date;
1828: --
2323: and et.processing_type='R'
2324: and (
2325: not exists
2326: (select null
2327: from per_all_assignments_f asg
2328: where asg.assignment_id = ee.assignment_id
2329: /* Added Benefits assignment type to the below code to ensure
2330: removal of entries wont happen in the case of benifits
2331: assignment type also */
2577: is
2578: --
2579: CURSOR csr_bus_group(p_assignment_id number) IS
2580: SELECT business_group_id
2581: FROM per_all_assignments_f
2582: WHERE assignment_id = p_assignment_id;
2583:
2584: /*Cursor added for bug:7440183for getting leg code */
2585: CURSOR csr_leg_grp(p_business_group_id number) IS
2986: and el.element_type_id = et.element_type_id
2987: and et.processing_type = 'N')))
2988: and not exists
2989: (select null
2990: from per_all_assignments_f asg,
2991: pay_element_links_f el
2992: where el.element_link_id = ee.element_link_id
2993: and asg.assignment_id = ee.assignment_id
2994: and asg.assignment_type = 'E'
3391: -- criteria.
3392: --
3393: and not exists
3394: (select null
3395: from per_all_assignments_f asg,
3396: pay_element_links_f el
3397: where asg.assignment_id = ee.assignment_id
3398: and el.element_link_id = ee.element_link_id
3399: and asg.assignment_type = 'E'
3487: select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/ plr.rule_mode
3488: into l_adjust_ee_source
3489: from pay_legislation_rules plr,
3490: per_business_groups_perf pbg,
3491: per_all_assignments_f paf
3492: where paf.assignment_id = p_assignment_id
3493: and p_validation_start_date between paf.effective_start_date
3494: and paf.effective_end_date
3495: and paf.business_group_id = pbg.business_group_id
3767: end if;
3768: --
3769:
3770: select count(*) into v_start_date_check from per_time_periods where payroll_id in(
3771: select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3772: and p_val_start_date_minus_one between effective_start_date and effective_end_date)
3773: and p_val_start_date_minus_one between start_date and end_date;
3774:
3775: select count(*) into v_end_date_check from per_time_periods where payroll_id in(
3772: and p_val_start_date_minus_one between effective_start_date and effective_end_date)
3773: and p_val_start_date_minus_one between start_date and end_date;
3774:
3775: select count(*) into v_end_date_check from per_time_periods where payroll_id in(
3776: select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3777: and p_val_end_date_plus_one between effective_start_date and effective_end_date)
3778: and p_val_end_date_plus_one between start_date and end_date;
3779:
3780: if g_debug then
3793: end if;
3794: --
3795: if v_start_date_check<>0 or v_end_date_check<>0 then
3796: select end_date into v_vale_start_date from per_time_periods where payroll_id in(
3797: select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
3798: and v_chng_date between effective_start_date and effective_end_date)
3799: and v_chng_date between start_date and end_date;
3800: end if;
3801:
5555: p_people_group_id number
5556: ) is
5557: select asg.effective_start_date,
5558: asg.effective_end_date
5559: from per_all_assignments_f asg
5560: where asg.assignment_id = p_assignment_id
5561: and asg.assignment_type = 'E'
5562: and asg.effective_start_date <= p_val_end_date_plus_one
5563: and asg.effective_end_date >= p_val_start_date_minus_one
6042: ) is
6043: select asg.assignment_id,
6044: asg.effective_start_date,
6045: asg.effective_end_date
6046: from per_all_assignments_f asg
6047: where ( ( p_assignment_id is null
6048: and asg.business_group_id = p_business_group_id)
6049: or (asg.assignment_id = p_assignment_id))
6050: and asg.assignment_type = 'E'
6208: --
6209: -- Failed to lock the assignment.
6210: --
6211: hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
6212: hr_utility.set_message_token('TABLE_NAME', 'per_all_assignments_f');
6213: hr_utility.raise_error;
6214: end;
6215: --
6216: -- Get first assignment piece matching the standard link.
6386: asg.pay_basis_id
6387: --
6388: -- end criteria used for EL matching
6389: --
6390: FROM PER_ALL_ASSIGNMENTS_F asg
6391: WHERE asg.assignment_id = b_assignment_id
6392: and asg.business_group_id = b_business_group_id
6393: ORDER BY
6394: asg.assignment_id,
6998: ee.creator_type,
6999: et.processing_type,
7000: ee.updating_action_id,
7001: ee.object_version_number
7002: from per_all_assignments_f asg
7003: , pay_element_entries_f ee
7004: , pay_element_types_f et
7005: where asg.assignment_id = c_assignment_id
7006: and asg.assignment_id = ee.assignment_id