[Home] [Help]
303:
304: cursor get_active_assignment
305: is
306: select asgn.assignment_id
307: from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
308: per_assignment_status_types status,
309: (select person_id, actual_termination_date from per_periods_of_service
310: union all
311: select person_id, actual_termination_date
321:
322: cursor get_primary_assignment
323: is
324: select asgn.assignment_id
325: from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
326: (select person_id, actual_termination_date from per_periods_of_service
327: union all
328: select person_id, actual_termination_date
329: from per_periods_of_placement) po
338:
339: cursor get_job_on_assignment
340: is
341: select asgn.job_id
342: from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
343: (select person_id, actual_termination_date from per_periods_of_service
344: union all
345: select person_id, actual_termination_date
346: from per_periods_of_placement) po
477: */
478: CURSOR c_per_active IS
479: SELECT PER.PERSON_ID
480: FROM PER_ALL_PEOPLE_F PER, /*for bug 2983491 Replaced PER_PEOPLE_F PER , */
481: PER_ALL_ASSIGNMENTS_F ASGN
482: WHERE ASGN.PERSON_ID = PER.PERSON_ID
483: AND ASGN.PRIMARY_FLAG = 'Y'
484: AND ASGN.ASSIGNMENT_TYPE in ('E', 'C')
485: AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
1405:
1406: PROCEDURE CREATE_INTERNAL_RESOURCE(
1407: P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
1408: P_NAME IN PA_RESOURCES.NAME%TYPE,
1409: P_ORGANIZATION_ID IN PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
1410: P_ASSIGNMENT_START_DATE IN DATE,
1411: P_ASSIGNMENT_END_DATE IN DATE,
1412: P_START_DATE IN DATE,
1413: P_DEFAULT_OU IN NUMBER,
1971:
1972: x_resource_number NUMBER;
1973:
1974: l_name PA_RESOURCES.NAME%TYPE;
1975: l_organization_id PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE;
1976: l_assignment_start_date DATE;
1977: l_assignment_end_date DATE;
1978: l_start_date DATE;
1979: l_default_OU NUMBER;
2127: SELECT prd.person_id,
2128: prd.resource_effective_start_date,
2129: prd.resource_effective_end_date
2130: FROM pa_resources_denorm prd,
2131: per_all_assignments_f paf,
2132: per_assignment_status_types past
2133: WHERE prd.person_id = paf.person_id
2134: AND prd.person_id = p_person_id
2135: AND paf.assignment_status_type_id = past.assignment_status_type_id
2802: (l_from_emp_num IS NOT NULL AND
2803: l_to_emp_num IS NOT NULL ) THEN
2804:
2805: l_sel_clause := ' SELECT DISTINCT p.person_id ';
2806: l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2807: -- changed l_where_clause for bug4954896
2808: -- l_where_clause := ' WHERE a.person_id = p.person_id '||
2809: -- ' AND a.organization_id = ' || l_p_org_id ||
2810: -- ' AND p.employee_number >= ''' || l_from_emp_num || ''''||
2815: ' AND p.employee_number <= :to_emp_num ';
2816: ELSE
2817: -- Employee range is not specified
2818: l_sel_clause := ' SELECT DISTINCT a.person_id ';
2819: l_from_clause := ' FROM per_all_assignments_f a';
2820: -- changed l_where_clause for Bug4954896
2821: --l_where_clause := ' WHERE a.organization_id = ' || l_p_org_id ;
2822: l_where_clause := ' WHERE a.organization_id = :org_id' ;
2823: END IF;
2851: IF l_selection_option = 'EMP_RANGE' THEN
2852: -- Employee range is specified
2853: l_sel_clause := ' SELECT DISTINCT p.person_id ';
2854: --l_from_clause := ' FROM per_all_people_f p '; /*Commented for Bug 7012687 */
2855: l_from_clause := ' FROM per_all_people_f p , per_all_assignments_f a'; /*Added per_all_asgn_f for Bug 7012687 */
2856: l_where_clause := ' WHERE p.employee_number >= :from_emp_num AND p.employee_number <= :to_emp_num '||
2857: ' AND p.person_id = a.person_id '|| /*Added condition for Bug 7012687 */
2858: ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2859: ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2860: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2861: ELSIF l_selection_option = 'EMP_RANGE_ORG' THEN
2862: -- Employee range and organization is specified
2863: l_sel_clause := ' SELECT DISTINCT p.person_id ';
2864: l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2865: l_where_clause := ' WHERE a.person_id = p.person_id '||
2866: ' AND a.organization_id = :org_id '||
2867: ' AND p.employee_number >= :from_emp_num '||
2868: ' AND p.employee_number <= :to_emp_num '||
2871: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2872: ELSIF l_selection_option = 'SINGLE_ORG' THEN
2873: -- Organization is specified
2874: l_sel_clause := ' SELECT DISTINCT a.person_id ';
2875: l_from_clause := ' FROM per_all_assignments_f a';
2876: l_where_clause := ' WHERE a.organization_id = :org_id'||
2877: ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2878: ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2879: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2879: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2880: ELSIF l_selection_option = 'START_ORG' THEN
2881: -- Organization hierarchy and starting organization is specified
2882: l_sel_clause := ' SELECT DISTINCT a.person_id ';
2883: l_from_clause := ' FROM per_all_assignments_f a';
2884: l_where_clause := ' WHERE a.organization_id IN ( '||
2885: ' SELECT hrorg.organization_id '||
2886: ' FROM hr_all_organization_units_tl hrorg, hr_organization_information orginfo '||
2887: ' WHERE hrorg.language = userenv(''LANG'') '||
2907: IF l_p_org_id IS NULL THEN
2908: -- Employee range is specified
2909: l_sel_clause := ' SELECT DISTINCT p.person_id ';
2910: --l_from_clause := ' FROM per_all_people_f p '; /*Commented for Bug 7012687 */
2911: l_from_clause := ' FROM per_all_people_f p , per_all_assignments_f a '; /*Added per_all_asgn_f for Bug 7012687 */
2912: l_where_clause := ' WHERE p.employee_number >= :from_emp_num AND p.employee_number <= :to_emp_num '||
2913: ' AND p.person_id = a.person_id '|| /*Added condition for Bug 7012687 */
2914: ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2915: ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2916: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
2917: ELSIF (l_from_emp_num IS NOT NULL AND l_to_emp_num IS NOT NULL) THEN
2918: -- Employee range and Organization is specified
2919: l_sel_clause := ' SELECT DISTINCT p.person_id ';
2920: l_from_clause := ' FROM per_all_people_f p, per_all_assignments_f a';
2921: l_where_clause := ' WHERE a.person_id = p.person_id '||
2922: ' AND a.organization_id = :org_id '||
2923: ' AND p.employee_number >= :from_emp_num '||
2924: ' AND p.employee_number <= :to_emp_num '||
2927: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 6850503 */
2928: ELSE
2929: -- Only Organization is specified
2930: l_sel_clause := ' SELECT DISTINCT a.person_id ';
2931: l_from_clause := ' FROM per_all_assignments_f a';
2932: l_where_clause := ' WHERE a.organization_id = :org_id'||
2933: ' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
2934: ' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
2935: ' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
3990:
3991: BEGIN
3992: SELECT min(effective_start_date)
3993: INTO l_current_asgn_start_date
3994: FROM per_all_assignments_f
3995: WHERE person_id = l_person_id
3996: AND primary_flag = 'Y'
3997: AND job_id IS NOT NULL
3998: AND assignment_type in ('E', 'C');