[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.
515: into duplicate
516: from sys.dual
517: where exists
518: ( select 'Y'
519: from per_all_assignments_f
520: -- from per_assignments_f commented for bug 4232539
521: where ((p_assignment_id is null)
522: or
523: ( p_assignment_id is not null
574: when replace(assignment_number,p_worker_number) is null then 2
575: else to_number(replace(assignment_number,p_worker_number||'-'))
576: end
577: ) into ass_seq
578: from per_all_assignments_f
579: where person_id = (select distinct person_id
580: from per_all_people_f
581: where employee_number = p_worker_number
582: and business_group_id = p_business_group_id)
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: ),2) 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 decode(p_assignment_type,'E',employee_number,'C',npw_number) = p_worker_number --Modified for bug 6711256
595: and business_group_id = p_business_group_id)
723: p_flag VARCHAR2(1);
724: p_next_eff_start_date DATE;
725: p_next_eff_end_date DATE;
726: l_person_id NUMBER;
727: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
728: l_pdp_date_start DATE;
729:
730: --
731: -- Fetch the person ID and assignment type so the
735: CURSOR csr_get_assignment_info IS
736: SELECT paaf.person_id
737: ,paaf.assignment_type
738: ,paaf.period_of_placement_date_start
739: FROM per_all_assignments_f paaf
740: WHERE paaf.assignment_id = p_assignment_id
741: AND paaf.assignment_type IN ('E', 'C');
742:
743: --
1408: p_first_term_date DATE;
1409: p_start_date DATE;
1410: l_pdp_date_start DATE;
1411: l_person_id NUMBER;
1412: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
1413:
1414: CURSOR csr_get_assignment_info IS
1415: SELECT paaf.person_id
1416: ,paaf.assignment_type
1414: CURSOR csr_get_assignment_info IS
1415: SELECT paaf.person_id
1416: ,paaf.assignment_type
1417: ,paaf.period_of_placement_date_start
1418: FROM per_all_assignments_f paaf
1419: WHERE paaf.assignment_id = p_assignment_id
1420: AND paaf.assignment_type IN ('E', 'C');
1421:
1422: CURSOR csr_get_term_dates IS
2134: p_atd DATE;
2135: p_fpd DATE;
2136: p_start_date DATE;
2137: l_person_id NUMBER;
2138: l_assignment_type per_all_assignments_f.assignment_type%TYPE;
2139: l_pdp_date_start DATE;
2140:
2141: --
2142: -- Fetch the person ID and assignment type so the
2146: CURSOR csr_get_assignment_info IS
2147: SELECT paaf.person_id
2148: ,paaf.assignment_type
2149: ,paaf.period_of_placement_date_start
2150: FROM per_all_assignments_f paaf
2151: WHERE paaf.assignment_id = p_assignment_id
2152: AND paaf.assignment_type IN ('E', 'C');
2153:
2154: --
3366: hr_utility.set_location(' No of rows deleted '||sql%rowcount,48);
3367: hr_utility.set_location('hr_assignment.del_ref_int_delete',49);
3368:
3369: select effective_end_date into l_ass_end_date
3370: from per_all_assignments_f
3371: where assignment_id = p_assignment_id
3372: and p_end_date between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
3373:
3374: hr_utility.set_location('l_ass_end_date '||l_ass_end_date,50);
4960: END load_assignment_allocation;
4961: -----------------------------------------------------------------------
4962: -- update_assgn_context_value
4963: --
4964: -- populates the per_all_assignments_f.ass_attribute_category value
4965: --
4966: PROCEDURE update_assgn_context_value(
4967: p_business_group_id IN number
4968: ,p_person_id IN number
4985: where DESCRIPTIVE_FLEXFIELD_NAME='PER_ASSIGNMENTS';
4986:
4987: cursor csr_ass_data is
4988: select ass_attribute_category
4989: from per_all_assignments_f
4990: where business_group_id = p_business_group_id
4991: and person_id = p_person_id
4992: and assignment_id = p_assignment_id
4993: and effective_start_date = p_effective_start_date
5050:
5051:
5052: if l_output_context_val is not null then
5053:
5054: update per_all_assignments_f
5055: set ass_attribute_category = l_output_context_val
5056: where business_group_id = p_business_group_id
5057: and person_id = p_person_id
5058: and assignment_id = p_assignment_id
5246: PA.GRADE_LADDER_PGM_ID ,
5247: PA.SUPERVISOR_ASSIGNMENT_ID ,
5248: PGM.NAME GRADE_LADDER_NAME ,
5249: PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
5250: FROM PER_ALL_ASSIGNMENTS_F PA ,
5251: PER_ALL_ASSIGNMENTS_F PA2 ,
5252: PER_GRADES PG ,
5253: PER_JOBS J ,
5254: PER_GRADES_TL GDT ,
5247: PA.SUPERVISOR_ASSIGNMENT_ID ,
5248: PGM.NAME GRADE_LADDER_NAME ,
5249: PA2.ASSIGNMENT_NUMBER SUPERVISOR_ASSIGNMENT_NUMBER
5250: FROM PER_ALL_ASSIGNMENTS_F PA ,
5251: PER_ALL_ASSIGNMENTS_F PA2 ,
5252: PER_GRADES PG ,
5253: PER_JOBS J ,
5254: PER_GRADES_TL GDT ,
5255: PER_JOBS_TL JBT ,
5303: OR (PA2.EFFECTIVE_START_DATE IS NOT NULL
5304: AND PA2.EFFECTIVE_END_DATE IS NOT NULL
5305: AND PA2.EFFECTIVE_END_DATE =
5306: (SELECT MAX(PA3.EFFECTIVE_END_DATE)
5307: FROM PER_ALL_ASSIGNMENTS_F PA3
5308: WHERE PA3.ASSIGNMENT_ID = PA.SUPERVISOR_ASSIGNMENT_ID
5309: )
5310: )
5311: )