[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'
971:
972: cursor c_sum_abv (p_position_id number,p_assignment_id number, p_effective_date date) is
973: select sum(value)
974: from per_assignment_budget_values_f abv, per_all_assignments_f asg,
975: per_assignment_status_types ast
976: where abv.assignment_id = asg.assignment_id
977: and asg.position_id = p_position_id
978: and p_effective_date between asg.effective_start_date and asg.effective_end_date
979: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1133: --
1134: CURSOR c_budgeted_fte(p_position_id number) is
1135: select sum(nvl(value,1))
1136: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1137: per_assignment_status_types ast
1138: where abv.assignment_id(+) = asn.assignment_id
1139: and p_effective_date between asn.effective_start_date and asn.effective_end_date
1140: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1141: and asn.position_id = p_position_id
1175: --
1176: CURSOR c_budgeted_fte(p_position_id number) is
1177: select sum(nvl(value,1))
1178: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
1179: per_assignment_status_types ast
1180: where abv.assignment_id(+) = asn.assignment_id
1181: and asn.assignment_id <> p_assignment_id
1182: and p_effective_date between asn.effective_start_date and asn.effective_end_date
1183: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1216: CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
1217: select nvl(sum(nvl(value,1)),0)
1218: from per_all_assignments_f asn,FND_SESSIONS SS,
1219: per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1220: per_assignment_status_types ast
1221: where abv.assignment_id(+) = asn.assignment_id
1222: and SS.SESSION_ID = USERENV('sessionid')
1223: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1224: and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1235: CURSOR c_budgeted_fte_ex_asg(p_person_id number, p_position_id number, p_ex_assignment_id number) is
1236: select nvl(sum(nvl(value,1)),0)
1237: from per_all_assignments_f asn,FND_SESSIONS SS,
1238: per_assignment_budget_values_f abv, FND_SESSIONS SS2,
1239: per_assignment_status_types ast
1240: where abv.assignment_id(+) = asn.assignment_id
1241: and SS.SESSION_ID = USERENV('sessionid')
1242: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1243: and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
1769: select
1770: paaf.assignment_number,
1771: paaf.effective_start_date,
1772: paaf.effective_end_date
1773: from per_all_assignments_f paaf, per_assignment_status_types past
1774: where paaf.assignment_status_type_id = past.assignment_status_type_id
1775: and paaf.assignment_type in ('E','C') -- Added For Bug#14173834
1776: --and paaf.assignment_type = 'E' -- Commented For Bug#14173834
1777: and past.per_system_status <> 'TERM_ASSIGN'
2076: --
2077: -- Cursor modified for bug 9804756
2078: cursor c1 is
2079: select count(1)
2080: from per_all_assignments_f paaf, per_assignment_status_types past
2081: where paaf.assignment_status_type_id = past.assignment_status_type_id
2082: and past.per_system_status <> 'TERM_ASSIGN'
2083: and paaf.position_id = p_position_id
2084: and paaf.assignment_type in ('E', 'C')
3010: l_fte number := 0;
3011: cursor c1(p_assignment_id number, p_effective_date date) is
3012: select nvl(abv.value,0)
3013: from per_assignment_budget_values_f abv, per_all_assignments_f asg,
3014: per_assignment_status_types ast
3015: where asg.assignment_id = p_assignment_id
3016: and abv.assignment_id = asg.assignment_id
3017: and asg.assignment_type in ('E', 'C')
3018: and abv.unit = 'FTE'
3530: p_ex_position_extra_info_id number, p_ex_person_id number) is
3531: select sum(nvl(value,0))
3532: from per_all_assignments_f asn,
3533: per_assignment_budget_values_f abv,
3534: per_assignment_status_types ast
3535: where abv.assignment_id = asn.assignment_id
3536: and asn.EFFECTIVE_START_DATE <= p_effective_date
3537: and asn.EFFECTIVE_END_DATE >= p_effective_date
3538: and abv.EFFECTIVE_START_DATE <= p_effective_date
3590: l_assignment_fte number(15,2):=0;
3591: CURSOR c_budgeted_fte(p_person_id number, p_position_id number) is
3592: select nvl(sum(nvl(value,1)),0)
3593: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
3594: per_assignment_status_types ast
3595: where abv.assignment_id(+) = asn.assignment_id
3596: and asn.position_id = p_position_id
3597: and asn.person_id = p_person_id
3598: and asn.assignment_id <> nvl(p_ex_assignment_id, -1)
4494: --
4495: CURSOR c_sum_asg_fte(p_position_id number) is
4496: select sum(value)
4497: from per_assignment_budget_values_f abv, per_all_assignments_f asn,
4498: per_assignment_status_types ast
4499: where abv.assignment_id(+) = asn.assignment_id
4500: and p_effective_start_date between asn.effective_start_date and asn.effective_end_date
4501: and p_effective_start_date between abv.effective_start_date and abv.effective_end_date
4502: and asn.position_id = p_position_id