[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'
120: , p_validation_end_date date) is
121: select max(pqh_psf_bus.sum_assignment_fte(p_position_id,ed))
122: from
123: (select a.effective_start_date ed
124: from per_all_assignments_f a
125: where a.position_id = p_position_id
126: and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
127: and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
128: ))
127: and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
128: ))
129: union
130: select a.effective_end_date ed
131: from per_all_assignments_f a
132: where a.position_id = p_position_id
133: and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
134: and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
135: ))
250: where position_id = p_rec.position_id;
251: --
252: cursor c_single_pos_future_asg(p_position_id number, p_effective_date date) is
253: select min(effective_start_date)
254: from per_all_assignments_f
255: where position_id = p_position_id
256: and effective_start_date > p_effective_date;
257: --
258: BEGIN
444: where assignment_id = p_rec.assignment_id;
445: --
446: cursor c_single_pos_future_asg(p_position_id number, p_effective_date date) is
447: select min(effective_start_date)
448: from per_all_assignments_f
449: where position_id = p_position_id
450: and effective_start_date > p_effective_date;
451: --
452: BEGIN
633: --
634: cursor c_changed_dates(p_position_id number,
635: p_validation_start_date date ,p_validation_end_date date) is
636: select effective_start_date, business_group_id
637: from per_all_assignments_f
638: where position_id = p_position_id
639: and effective_start_date between p_validation_start_date and p_validation_end_date
640: union
641: select effective_end_date, business_group_id
638: where position_id = p_position_id
639: and effective_start_date between p_validation_start_date and p_validation_end_date
640: union
641: select effective_end_date, business_group_id
642: from per_all_assignments_f
643: where position_id = p_position_id
644: and effective_end_date between p_validation_start_date and p_validation_end_date
645: union
646: select abv.effective_start_date, abv.business_group_id
643: where position_id = p_position_id
644: and effective_end_date between p_validation_start_date and p_validation_end_date
645: union
646: select abv.effective_start_date, abv.business_group_id
647: from per_assignment_budget_values_f abv, per_all_assignments_f asg
648: where abv.assignment_id = asg.assignment_id
649: and asg.position_id = p_position_id
650: and abv.effective_start_date between p_validation_start_date and p_validation_end_date
651: and asg.effective_start_date between p_validation_start_date and p_validation_end_date
650: and abv.effective_start_date between p_validation_start_date and p_validation_end_date
651: and asg.effective_start_date between p_validation_start_date and p_validation_end_date
652: union
653: select abv.effective_end_date, abv.business_group_id
654: from per_assignment_budget_values_f abv, per_all_assignments_f asg
655: where abv.assignment_id = asg.assignment_id
656: and asg.position_id = p_position_id
657: and abv.effective_end_date between p_validation_start_date and p_validation_end_date
658: and asg.effective_end_date between p_validation_start_date and p_validation_end_date
663: and effective_start_date between p_validation_start_date and p_validation_end_date;
664: --
665: cursor c_position_id(p_assignment_id number, p_effective_date date) is
666: select position_id
667: from per_all_assignments_f
668: where assignment_id = p_assignment_id
669: and p_effective_date between effective_start_date and effective_end_date;
670: --
671: cursor c_position_fte(p_position_id number, p_date date) is
780: l_business_group_id number;
781:
782: cursor c_asg is
783: select paf.position_id, paf.person_id, paf.business_group_id
784: from per_all_assignments_f paf
785: where paf.assignment_id = p_assignment_id
786: and p_effective_date between paf.effective_start_date and paf.effective_end_date;
787:
788: cursor c_positions is
789: select position_id, fte, max_persons, position_type, organization_id, overlap_period
790: from hr_all_positions_f psf
791: where position_id =
792: (select position_id
793: from per_all_assignments_f paf
794: where paf.assignment_id = p_assignment_id
795: and p_effective_date between paf.effective_start_date and paf.effective_end_date)
796: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
797:
796: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
797:
798: cursor c_sum_abv (p_position_id number) is
799: select sum(value)
800: from per_assignment_budget_values_f abv, per_all_assignments_f asg
801: where abv.assignment_id = asg.assignment_id
802: and asg.position_id = p_position_id
803: and p_effective_date between asg.effective_start_date and asg.effective_end_date
804: and p_effective_date between abv.effective_start_date and abv.effective_end_date
937: l_business_group_id number;
938: --
939: cursor c_asg(p_assignment_id number, p_effective_date date) is
940: select position_id, person_id, business_group_id
941: from per_all_assignments_f paf
942: where paf.assignment_id = p_assignment_id
943: and p_effective_date between paf.effective_start_date and paf.effective_end_date;
944: --
945: cursor c_positions(p_effective_date date) is
946: select position_id, fte, max_persons, position_type, organization_id, overlap_period
947: from hr_all_positions_f psf
948: where position_id =
949: (select position_id
950: from per_all_assignments_f paf
951: where paf.assignment_id = p_assignment_id
952: and p_effective_date between paf.effective_start_date and paf.effective_end_date)
953: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
954:
953: and p_effective_date between psf.effective_start_date and psf.effective_end_date;
954:
955: cursor c_sum_abv (p_position_id number,p_assignment_id number, p_effective_date date) is
956: select sum(value)
957: from per_assignment_budget_values_f abv, per_all_assignments_f asg
958: where abv.assignment_id = asg.assignment_id
959: and asg.position_id = p_position_id
960: and p_effective_date between asg.effective_start_date and asg.effective_end_date
961: and p_effective_date between abv.effective_start_date and abv.effective_end_date
1107: l_assignment_fte number(15,2):=0;
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
1149: l_assignment_fte number(15,2):=0;
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
1189: l_person_id number;
1190: l_assignment_fte number(15,2):=0;
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')
1208: and ast.per_system_status <> 'TERM_ASSIGN';
1209:
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')
1279: l_assignment_fte number(15,2);
1280:
1281: CURSOR c_budgeted_fte(p_assignment_id number) is
1282: select nvl(value,1)
1283: from per_all_assignments_f asn,FND_SESSIONS SS,
1284: per_assignment_budget_values_f abv, FND_SESSIONS SS2
1285: where abv.assignment_id(+) = asn.assignment_id
1286: and SS.SESSION_ID = USERENV('sessionid')
1287: and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
1564: between effective_start_date and effective_end_date;
1565:
1566: CURSOR C_ASSIGNMENTS IS
1567: select assignment_id
1568: from per_all_assignments_f
1569: where position_id = p_position_id
1570: and p_effective_date
1571: between effective_start_date and effective_end_date
1572: and assignment_type in ('E', 'C');
1885: l_count number(15);
1886: --
1887: cursor c1 is
1888: select count(1)
1889: from per_all_assignments_f
1890: where position_id = p_position_id
1891: and assignment_type in ('E', 'C')
1892: and p_effective_date between effective_start_date and effective_end_date;
1893: --
2026: function position_min_asg_dt(p_position_id number) return date is
2027: l_min_asg_date date;
2028: cursor c_min_asg_dt(p_position_id number) is
2029: select min(effective_start_date)
2030: from per_all_assignments_f
2031: where position_id = p_position_id
2032: and assignment_type in ('E', 'C');
2033: begin
2034: open c_min_asg_dt(p_position_id );
2040: function position_max_asg_dt(p_position_id number) return date is
2041: l_max_asg_date date;
2042: cursor c_max_asg_dt(p_position_id number) is
2043: select max(effective_start_date)
2044: from per_all_assignments_f
2045: where position_id = p_position_id
2046: and assignment_type in ('E', 'C');
2047: begin
2048: open c_max_asg_dt(p_position_id );
2219: and etp.end_date >= p_start_date
2220: and stp.start_date <= p_end_date
2221: union
2222: select effective_start_date start_date
2223: from per_all_assignments_f
2224: where p_position_id = position_id
2225: and assignment_type in ('E', 'C')
2226: and effective_start_date between p_start_date and p_end_date
2227: union
2225: and assignment_type in ('E', 'C')
2226: and effective_start_date between p_start_date and p_end_date
2227: union
2228: select abv.effective_start_date start_date
2229: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2230: where abv.assignment_id = asg.assignment_id
2231: and p_position_id = asg.position_id
2232: and asg.assignment_type in ('E', 'C')
2233: and abv.unit = 'FTE'
2288: and etp.end_date >= p_start_date
2289: and stp.start_date <= p_end_date
2290: union
2291: select effective_start_date start_date
2292: from per_all_assignments_f
2293: where p_job_id = job_id
2294: and assignment_type in ('E', 'C')
2295: and effective_start_date between p_start_date and p_end_date
2296: union
2294: and assignment_type in ('E', 'C')
2295: and effective_start_date between p_start_date and p_end_date
2296: union
2297: select abv.effective_start_date start_date
2298: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2299: where abv.assignment_id = asg.assignment_id
2300: and p_job_id = asg.job_id
2301: and asg.assignment_type in ('E', 'C')
2302: and abv.unit = 'FTE'
2358: and etp.end_date >= p_start_date
2359: and stp.start_date <= p_end_date
2360: union
2361: select effective_start_date start_date
2362: from per_all_assignments_f
2363: where p_organization_id = organization_id
2364: and assignment_type in ('E', 'C')
2365: and effective_start_date between p_start_date and p_end_date
2366: union
2364: and assignment_type in ('E', 'C')
2365: and effective_start_date between p_start_date and p_end_date
2366: union
2367: select abv.effective_start_date start_date
2368: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2369: where abv.assignment_id = asg.assignment_id
2370: and p_organization_id = asg.organization_id
2371: and asg.assignment_type in ('E', 'C')
2372: and abv.unit = 'FTE'
2429: and etp.end_date >= p_start_date
2430: and stp.start_date <= p_end_date
2431: union
2432: select effective_start_date start_date
2433: from per_all_assignments_f
2434: where p_grade_id = grade_id
2435: and assignment_type in ('E', 'C')
2436: and effective_start_date between p_start_date and p_end_date
2437: union
2435: and assignment_type in ('E', 'C')
2436: and effective_start_date between p_start_date and p_end_date
2437: union
2438: select abv.effective_start_date start_date
2439: from per_assignment_budget_values_f abv, per_all_assignments_f asg
2440: where abv.assignment_id = asg.assignment_id
2441: and p_grade_id = asg.grade_id
2442: and asg.assignment_type in ('E', 'C')
2443: and abv.unit = 'FTE'
2761: function assignment_fte(p_assignment_id number, p_effective_date date) return number is
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')
3057: l_business_group_id number;
3058: --
3059: cursor c_bus_grp_id(p_assignment_id number) is
3060: select business_group_id
3061: from per_all_assignments_f
3062: where assignment_id = p_assignment_id
3063: and p_effective_date between effective_start_date and effective_end_date;
3064: --
3065: cursor c1(p_unit_id number) is select system_type_cd from
3262: --
3263: cursor c1(p_position_id number, p_effective_date date,
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
3321: l_person_id number;
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
3362: --
3363: cursor c1(p_position_id number, p_position_extra_info_id number,
3364: p_start_date date, p_end_date date) is
3365: select abv.effective_start_date effective_date
3366: from per_assignment_budget_values_f abv, per_all_assignments_f asn
3367: where abv.assignment_id = asn.assignment_id
3368: and asn.position_id = p_position_id
3369: and abv.effective_start_date between asn.effective_start_date and asn.effective_end_date
3370: and abv.effective_start_date between
3374: and asn.assignment_type in ('E', 'C')
3375: and abv.unit(+) = 'FTE'
3376: union
3377: select effective_start_date effective_date
3378: from per_all_assignments_f asg
3379: where position_id = p_position_id
3380: and asg.effective_start_date between
3381: p_start_date and nvl(p_end_date, hr_general.end_of_time)
3382: union
3585: l_available_fte number;
3586: l_assignment_fte number;
3587: cursor c1(p_assignment_id number, p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3588: select effective_start_date
3589: from per_all_assignments_f
3590: where position_id = p_position_id
3591: and assignment_type in ('E', 'C')
3592: and effective_start_date between p_validation_start_date and p_validation_end_date
3593: union
3591: and assignment_type in ('E', 'C')
3592: and effective_start_date between p_validation_start_date and p_validation_end_date
3593: union
3594: select abv.effective_start_date effective_start_date
3595: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3596: where abv.assignment_id = asg.assignment_id
3597: and asg.position_id = p_position_id
3598: and asg.assignment_type in ('E', 'C')
3599: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3607: and information_type = 'PER_RESERVED')
3608: where effective_start_date >= p_validation_start_date
3609: union
3610: select effective_start_date
3611: from per_all_assignments_f
3612: where assignment_id = p_assignment_id
3613: and assignment_type in ('E', 'C')
3614: and effective_start_date between p_validation_start_date and p_validation_end_date
3615: union
3613: and assignment_type in ('E', 'C')
3614: and effective_start_date between p_validation_start_date and p_validation_end_date
3615: union
3616: select abv.effective_start_date effective_start_date
3617: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3618: where abv.assignment_id = asg.assignment_id
3619: and asg.assignment_id = p_assignment_id
3620: and asg.assignment_type in ('E', 'C')
3621: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3627: and effective_start_date between p_validation_start_date and p_validation_end_date;
3628: --
3629: cursor c2(p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3630: select effective_start_date
3631: from per_all_assignments_f
3632: where position_id = p_position_id
3633: and assignment_type in ('E', 'C')
3634: and effective_start_date between p_validation_start_date and p_validation_end_date
3635: union
3633: and assignment_type in ('E', 'C')
3634: and effective_start_date between p_validation_start_date and p_validation_end_date
3635: union
3636: select abv.effective_start_date effective_start_date
3637: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3638: where abv.assignment_id = asg.assignment_id
3639: and asg.position_id = p_position_id
3640: and asg.assignment_type in ('E', 'C')
3641: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3812: l_total_asg_fte number;
3813: --
3814: cursor c1(p_assignment_id number, p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3815: select effective_start_date
3816: from per_all_assignments_f
3817: where position_id = p_position_id
3818: and assignment_type in ('E', 'C')
3819: and effective_start_date between p_validation_start_date and p_validation_end_date
3820: union
3818: and assignment_type in ('E', 'C')
3819: and effective_start_date between p_validation_start_date and p_validation_end_date
3820: union
3821: select abv.effective_start_date effective_start_date
3822: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3823: where abv.assignment_id = asg.assignment_id
3824: and asg.position_id = p_position_id
3825: and asg.assignment_type in ('E', 'C')
3826: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3827: and asg.effective_start_date between p_validation_start_date and p_validation_end_date
3828: and asg.business_group_id = abv.business_group_id
3829: union
3830: select effective_start_date
3831: from per_all_assignments_f
3832: where assignment_id = p_assignment_id
3833: and assignment_type in ('E', 'C')
3834: and effective_start_date between p_validation_start_date and p_validation_end_date
3835: union
3833: and assignment_type in ('E', 'C')
3834: and effective_start_date between p_validation_start_date and p_validation_end_date
3835: union
3836: select abv.effective_start_date effective_start_date
3837: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3838: where abv.assignment_id = asg.assignment_id
3839: and asg.assignment_id = p_assignment_id
3840: and asg.assignment_type in ('E', 'C')
3841: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
3847: and effective_start_date between p_validation_start_date and p_validation_end_date;
3848: --
3849: cursor c2(p_position_id number, p_validation_start_date date, p_validation_end_date date) is
3850: select effective_start_date
3851: from per_all_assignments_f
3852: where position_id = p_position_id
3853: and assignment_type in ('E', 'C')
3854: and effective_start_date between p_validation_start_date and p_validation_end_date
3855: union
3853: and assignment_type in ('E', 'C')
3854: and effective_start_date between p_validation_start_date and p_validation_end_date
3855: union
3856: select abv.effective_start_date effective_start_date
3857: from per_assignment_budget_values_f abv, per_all_assignments_f asg
3858: where abv.assignment_id = asg.assignment_id
3859: and asg.position_id = p_position_id
3860: and asg.assignment_type in ('E', 'C')
3861: and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
4114: --
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
4246:
4247: --
4248: cursor c_assignment(p_assignment_id number, p_effective_date date) is
4249: select position_id, organization_id
4250: from per_all_assignments_f
4251: where assignment_id = p_assignment_id
4252: and p_effective_date between effective_start_date and effective_end_date;
4253: --
4254: begin
4351: --
4352: cursor c_pay_basis_info(p_assignment_id number,
4353: p_effective_date date) is
4354: select piv.element_type_id, ppb.input_value_id
4355: from per_all_assignments_f asg,
4356: per_pay_bases ppb,
4357: pay_input_values_f piv
4358: where assignment_id = p_assignment_id
4359: and asg.pay_basis_id = ppb.pay_basis_id