[Home] [Help]
652: where position_id = p_position_id
653: and effective_end_date between p_validation_start_date and p_validation_end_date
654: union
655: select abv.effective_start_date, abv.business_group_id
656: from per_assignment_budget_values_f abv, per_all_assignments_f asg
657: where abv.assignment_id = asg.assignment_id
658: and asg.position_id = p_position_id
659: and abv.effective_start_date between p_validation_start_date and p_validation_end_date
660: and asg.effective_start_date between p_validation_start_date and p_validation_end_date
659: and abv.effective_start_date between p_validation_start_date and p_validation_end_date
660: and asg.effective_start_date between p_validation_start_date and p_validation_end_date
661: union
662: select abv.effective_end_date, abv.business_group_id
663: from per_assignment_budget_values_f abv, per_all_assignments_f asg
664: where abv.assignment_id = asg.assignment_id
665: and asg.position_id = p_position_id
666: and abv.effective_end_date between p_validation_start_date and p_validation_end_date
667: and asg.effective_end_date between p_validation_start_date and p_validation_end_date
808: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
809:
810: cursor c_sum_abv (p_position_id number) is
811: select sum(value)
812: from per_assignment_budget_values_f abv, per_all_assignments_f asg
813: where abv.assignment_id = asg.assignment_id
814: and asg.position_id = p_position_id
815: and p_effective_date between asg.effective_start_date and asg.effective_end_date
816: and p_effective_date between abv.effective_start_date and abv.effective_end_date
970: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
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
1132: l_assignment_fte number(15,2):=0;
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
1174: l_assignment_fte number(15,2):=0;
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
1215: l_assignment_fte number(15,2):=0;
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
1234:
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
1305:
1306: CURSOR c_budgeted_fte(p_assignment_id number) is
1307: select nvl(value,1)
1308: from per_all_assignments_f asn,FND_SESSIONS SS,
1309: per_assignment_budget_values_f abv, FND_SESSIONS SS2
1310: where abv.assignment_id(+) = asn.assignment_id
1311: and SS.SESSION_ID = USERENV('sessionid')
1312: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1313: and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
2418: and assignment_type in ('E', 'C')
2419: and effective_start_date between p_start_date and p_end_date
2420: union
2421: select abv.effective_start_date start_date
2422: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2423: where abv.assignment_id = asg.assignment_id
2424: and p_position_id = asg.position_id
2425: and asg.assignment_type in ('E', 'C')
2426: and abv.unit = 'FTE'
2487: and assignment_type in ('E', 'C')
2488: and effective_start_date between p_start_date and p_end_date
2489: union
2490: select abv.effective_start_date start_date
2491: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2492: where abv.assignment_id = asg.assignment_id
2493: and p_job_id = asg.job_id
2494: and asg.assignment_type in ('E', 'C')
2495: and abv.unit = 'FTE'
2557: and assignment_type in ('E', 'C')
2558: and effective_start_date between p_start_date and p_end_date
2559: union
2560: select abv.effective_start_date start_date
2561: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2562: where abv.assignment_id = asg.assignment_id
2563: and p_organization_id = asg.organization_id
2564: and asg.assignment_type in ('E', 'C')
2565: and abv.unit = 'FTE'
2628: and assignment_type in ('E', 'C')
2629: and effective_start_date between p_start_date and p_end_date
2630: union
2631: select abv.effective_start_date start_date
2632: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2633: where abv.assignment_id = asg.assignment_id
2634: and p_grade_id = asg.grade_id
2635: and asg.assignment_type in ('E', 'C')
2636: and abv.unit = 'FTE'
3009: function assignment_fte(p_assignment_id number, p_effective_date date) return number is
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')
3529: cursor c1(p_position_id number, p_effective_date date,
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
3589: l_person_id number;
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
3630: --
3631: cursor c1(p_position_id number, p_position_extra_info_id number,
3632: p_start_date date, p_end_date date) is
3633: select abv.effective_start_date effective_date
3634: from per_assignment_budget_values_f abv, per_all_assignments_f asn
3635: where abv.assignment_id = asn.assignment_id
3636: and asn.position_id = p_position_id
3637: and abv.effective_start_date between asn.effective_start_date and asn.effective_end_date
3638: and abv.effective_start_date between
3900: and assignment_type in ('E', 'C')
3901: and effective_start_date between p_validation_start_date and p_validation_end_date
3902: union
3903: select abv.effective_start_date effective_start_date
3904: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3905: where abv.assignment_id = asg.assignment_id
3906: and asg.position_id = p_position_id
3907: and asg.assignment_type in ('E', 'C')
3908: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3922: and assignment_type in ('E', 'C')
3923: and effective_start_date between p_validation_start_date and p_validation_end_date
3924: union
3925: select abv.effective_start_date effective_start_date
3926: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3927: where abv.assignment_id = asg.assignment_id
3928: and asg.assignment_id = p_assignment_id
3929: and asg.assignment_type in ('E', 'C')
3930: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3942: and assignment_type in ('E', 'C')
3943: and effective_start_date between p_validation_start_date and p_validation_end_date
3944: union
3945: select abv.effective_start_date effective_start_date
3946: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3947: where abv.assignment_id = asg.assignment_id
3948: and asg.position_id = p_position_id
3949: and asg.assignment_type in ('E', 'C')
3950: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
4180: or p_validation_end_date between effective_start_date and effective_end_date)
4181: --11059779
4182: union
4183: select abv.effective_start_date effective_start_date
4184: from per_assignment_budget_values_f abv, per_all_assignments_f asg
4185: where abv.assignment_id = asg.assignment_id
4186: and asg.position_id = p_position_id
4187: and asg.assignment_type in ('E', 'C')
4188: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
4195: and assignment_type in ('E', 'C')
4196: and effective_start_date between p_validation_start_date and p_validation_end_date
4197: union
4198: select abv.effective_start_date effective_start_date
4199: from per_assignment_budget_values_f abv, per_all_assignments_f asg
4200: where abv.assignment_id = asg.assignment_id
4201: and asg.assignment_id = p_assignment_id
4202: and asg.assignment_type in ('E', 'C')
4203: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
4215: and assignment_type in ('E', 'C')
4216: and effective_start_date between p_validation_start_date and p_validation_end_date
4217: union
4218: select abv.effective_start_date effective_start_date
4219: from per_assignment_budget_values_f abv, per_all_assignments_f asg
4220: where abv.assignment_id = asg.assignment_id
4221: and asg.position_id = p_position_id
4222: and asg.assignment_type in ('E', 'C')
4223: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
4493: --
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