[Home] [Help]
27: select 'x'
28: from dual
29: where exists (
30: select null
31: from per_all_assignments_f asg, per_assignment_status_types ast
32: where asg.position_id = p_position_id
33: and asg.assignment_type in ('E', 'C','A') -- changes made for the bug 5680305
34: and asg.assignment_status_type_id = ast.assignment_status_type_id
35: and ast.per_system_status <> 'TERM_ASSIGN'
1108: --
1109: CURSOR c_budgeted_fte(p_position_id number) is
1110: select sum(nvl(value,1))
1111: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1112: per_assignment_status_types ast
1113: where abv.assignment_id(+) = asn.assignment_id
1114: and p_effective_date between asn.effective_start_date and asn.effective_end_date
1115: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1116: and asn.position_id = p_position_id
1150: --
1151: CURSOR c_budgeted_fte(p_position_id number) is
1152: select sum(nvl(value,1))
1153: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1154: per_assignment_status_types ast
1155: where abv.assignment_id(+) = asn.assignment_id
1156: and asn.assignment_id <> p_assignment_id
1157: and p_effective_date between asn.effective_start_date and asn.effective_end_date
1158: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1191: CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
1192: select nvl(sum(nvl(value,1)),0)
1193: from per_all_assignments_f asn,FND_SESSIONS SS,
1194: per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1195: per_assignment_status_types ast
1196: where abv.assignment_id(+) = asn.assignment_id
1197: and SS.SESSION_ID = USERENV('sessionid')
1198: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1199: and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1210: CURSOR c_budgeted_fte_ex_asg(p_person_id number, p_position_id number, p_ex_assignment_id number) is
1211: select nvl(sum(nvl(value,1)),0)
1212: from per_all_assignments_f asn,FND_SESSIONS SS,
1213: per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1214: per_assignment_status_types ast
1215: where abv.assignment_id(+) = asn.assignment_id
1216: and SS.SESSION_ID = USERENV('sessionid')
1217: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1218: and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
2762: l_fte number := 0;
2763: cursor c1(p_assignment_id number, p_effective_date date) is
2764: select nvl(abv.value,0)
2765: from per_assignment_budget_values_f abv, per_all_assignments_f asg,
2766: per_assignment_status_types ast
2767: where asg.assignment_id = p_assignment_id
2768: and abv.assignment_id = asg.assignment_id
2769: and asg.assignment_type in ('E', 'C')
2770: and abv.unit = 'FTE'
3264: p_ex_position_extra_info_id number, p_ex_person_id number) is
3265: select sum(nvl(value,0))
3266: from per_all_assignments_f asn,
3267: per_assignment_budget_values_f abv,
3268: per_assignment_status_types ast
3269: where abv.assignment_id = asn.assignment_id
3270: and asn.EFFECTIVE_START_DATE <= p_effective_date
3271: and asn.EFFECTIVE_END_DATE >= p_effective_date
3272: and abv.EFFECTIVE_START_DATE <= p_effective_date
3322: l_assignment_fte number(15,2):=0;
3323: CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
3324: select nvl(sum(nvl(value,1)),0)
3325: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
3326: per_assignment_status_types ast
3327: where abv.assignment_id(+) = asn.assignment_id
3328: and asn.position_id = p_position_id
3329: and asn.person_id = p_person_id
3330: and asn.assignment_id <> nvl(p_ex_assignment_id, -1)
4115: --
4116: CURSOR c_sum_asg_fte(p_position_id number) is
4117: select sum(value)
4118: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
4119: per_assignment_status_types ast
4120: where abv.assignment_id(+) = asn.assignment_id
4121: and p_effective_start_date between asn.effective_start_date and asn.effective_end_date
4122: and p_effective_start_date between abv.effective_start_date and abv.effective_end_date
4123: and asn.position_id = p_position_id