[Home] [Help]
267: 115.41 16-Feb-05 jpthomas 4186091 Backedout the changes made for the bug 4040403
268: 115.42 13-Jun-05 hsajja Changed cursors c0, c1, c2 in procedure
269: load_assignment_allocation
270: 115.43 25-Jan-06 bshukla 4946199 Modified DEL_REF_INT_CHECK()
271: 115.44 02-Mar-06 risgupta 4232539 used per_all_assignments_f in place of
272: per_assignments_f in the function
273: validate_ass_number of procedure
274: gen_new_ass_number.
275: 115.47 12-Sep-06 ghshanka 5498344 Modified the procedure gen_new_ass_number.
528: into duplicate
529: from sys.dual
530: where exists
531: ( select 'Y'
532: from per_all_assignments_f
533: -- from per_assignments_f commented for bug 4232539
534: where ((p_assignment_id is null)
535: or
536: ( p_assignment_id is not null
587: when replace(assignment_number,p_worker_number) is null then 2
588: else to_number(replace(assignment_number,p_worker_number||'-'))
589: end
590: ) into ass_seq
591: from per_all_assignments_f
592: where person_id = (select distinct person_id
593: from per_all_people_f
594: where employee_number = p_worker_number
595: and business_group_id = p_business_group_id)
602: when replace(assignment_number,p_worker_number) is null then 2
603: else to_number(replace(assignment_number,p_worker_number||'-'))
604: end
605: ),2) into ass_seq
606: from per_all_assignments_f
607: where person_id = (select distinct person_id
608: from per_all_people_f
609: where employee_number = p_worker_number
610: and business_group_id = p_business_group_id)
616: when replace(assignment_number,p_worker_number) is null then 2
617: else to_number(replace(assignment_number,p_worker_number||'-'))
618: end
619: ),2) into ass_seq
620: from per_all_assignments_f
621: where person_id = (select distinct person_id
622: from per_all_people_f
623: where npw_number = p_worker_number
624: and business_group_id = p_business_group_id)
753: p_flag VARCHAR2(1);
754: p_next_eff_start_date DATE;
755: p_next_eff_end_date DATE;
756: l_person_id NUMBER;
757: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
758: l_pdp_date_start DATE;
759:
760: --
761: -- Fetch the person ID and assignment type so the
765: CURSOR csr_get_assignment_info IS
766: SELECT paaf.person_id
767: ,paaf.assignment_type
768: ,paaf.period_of_placement_date_start
769: FROM per_all_assignments_f paaf
770: WHERE paaf.assignment_id = p_assignment_id
771: AND paaf.assignment_type IN ('E', 'C');
772:
773: --
1438: p_first_term_date DATE;
1439: p_start_date DATE;
1440: l_pdp_date_start DATE;
1441: l_person_id NUMBER;
1442: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
1443:
1444: CURSOR csr_get_assignment_info IS
1445: SELECT paaf.person_id
1446: ,paaf.assignment_type
1444: CURSOR csr_get_assignment_info IS
1445: SELECT paaf.person_id
1446: ,paaf.assignment_type
1447: ,paaf.period_of_placement_date_start
1448: FROM per_all_assignments_f paaf
1449: WHERE paaf.assignment_id = p_assignment_id
1450: AND paaf.assignment_type IN ('E', 'C');
1451:
1452: CURSOR csr_get_term_dates IS
2171: p_atd DATE;
2172: p_fpd DATE;
2173: p_start_date DATE;
2174: l_person_id NUMBER;
2175: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
2176: l_pdp_date_start DATE;
2177:
2178: --
2179: -- Fetch the person ID and assignment type so the
2183: CURSOR csr_get_assignment_info IS
2184: SELECT paaf.person_id
2185: ,paaf.assignment_type
2186: ,paaf.period_of_placement_date_start
2187: FROM per_all_assignments_f paaf
2188: WHERE paaf.assignment_id = p_assignment_id
2189: AND paaf.assignment_type IN ('E', 'C');
2190:
2191: --
3404: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3405: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3406:
3407: select effective_end_date into l_ass_end_date
3408: from per_all_assignments_f
3409: where assignment_id = p_assignment_id
3410: and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3411:
3412: hr_utility.set_location('l_ass_end_date '||l_ass_end_date,50);
5000: END load_assignment_allocation;
5001: -----------------------------------------------------------------------
5002: -- update_assgn_context_value
5003: --
5004: -- populates the per_all_assignments_f.ass_attribute_category value
5005: --
5006: PROCEDURE update_assgn_context_value(
5007: p_business_group_id IN number
5008: ,p_person_id IN number
5029: -- fix for the bug 8252045 ends here
5030:
5031: cursor csr_ass_data is
5032: select ass_attribute_category
5033: from per_all_assignments_f
5034: where business_group_id = p_business_group_id
5035: and person_id = p_person_id
5036: and assignment_id = p_assignment_id
5037: and effective_start_date = p_effective_start_date
5094:
5095:
5096: if l_output_context_val is not null then
5097:
5098: update per_all_assignments_f
5099: set ass_attribute_category = l_output_context_val
5100: where business_group_id = p_business_group_id
5101: and person_id = p_person_id
5102: and assignment_id = p_assignment_id
5290: PA.GRADE_LADDER_PGM_ID ,
5291: PA.SUPERVISOR_ASSIGNMENT_ID ,
5292: PGM.NAME GRADE_LADDER_NAME ,
5293: PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
5294: FROM PER_ALL_ASSIGNMENTS_F PA ,
5295: PER_ALL_ASSIGNMENTS_F PA2 ,
5296: PER_GRADES PG ,
5297: PER_JOBS J ,
5298: PER_GRADES_TL GDT ,
5291: PA.SUPERVISOR_ASSIGNMENT_ID ,
5292: PGM.NAME GRADE_LADDER_NAME ,
5293: PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
5294: FROM PER_ALL_ASSIGNMENTS_F PA ,
5295: PER_ALL_ASSIGNMENTS_F PA2 ,
5296: PER_GRADES PG ,
5297: PER_JOBS J ,
5298: PER_GRADES_TL GDT ,
5299: PER_JOBS_TL JBT ,
5347: OR (PA2.EFFECTIVE_START_DATE IS NOT NULL
5348: AND PA2.EFFECTIVE_END_DATE IS NOT NULL
5349: AND PA2.EFFECTIVE_END_DATE =
5350: (SELECT MAX(PA3.EFFECTIVE_END_DATE)
5351: FROM PER_ALL_ASSIGNMENTS_F PA3
5352: WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID
5353: )
5354: )
5355: )