eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_RESOURCE_UTILS dependencies on PA_RESOURCES_DENORM

Line 162: FROM pa_resources_denorm prd

158: IF (nvl(l_future_term_wf_flag,'N') = 'Y') THEN
159:
160: SELECT DISTINCT(prd.person_id)
161: INTO x_resource_id
162: FROM pa_resources_denorm prd
163: WHERE prd.person_id = p_resource_id
164: AND trunc(p_date) > = (Select trunc(min(prd1.resource_effective_start_date))
165: from pa_resources_denorm prd1
166: where prd1.person_id = prd.person_id)

Line 165: from pa_resources_denorm prd1

161: INTO x_resource_id
162: FROM pa_resources_denorm prd
163: WHERE prd.person_id = p_resource_id
164: AND trunc(p_date) > = (Select trunc(min(prd1.resource_effective_start_date))
165: from pa_resources_denorm prd1
166: where prd1.person_id = prd.person_id)
167: AND (trunc(p_end_date) is null
168: OR
169: trunc(p_end_date) < = (Select trunc(max(prd2.resource_effective_end_date))

Line 170: from pa_resources_denorm prd2

166: where prd1.person_id = prd.person_id)
167: AND (trunc(p_end_date) is null
168: OR
169: trunc(p_end_date) < = (Select trunc(max(prd2.resource_effective_end_date))
170: from pa_resources_denorm prd2
171: where prd2.person_id = prd.person_id) );
172: ELSE
173:
174: SELECT per.person_id

Line 434: pa_resources_denorm

430: cursor get_org_id_from_resource is
431: select
432: resource_org_id
433: from
434: pa_resources_denorm
435: where
436: resource_id = p_resource_id
437: and p_start_date between resource_effective_start_date
438: and resource_effective_end_date;

Line 461: * exist in pa_resources_denorm table and is therefore

457: close get_org_id_from_resource;
458:
459: If l_prvdr_org_id IS NULL Then
460: /* A resource identifier was passed that does not
461: * exist in pa_resources_denorm table and is therefore
462: * invalid so raise exception.
463: */
464: raise NULL_INVALID_PARAMS;
465: End If;

Line 586: from pa_resources_denorm res,

582: cursor cur_exist(l_res_id in NUMBER, l_date IN date) is
583: select 'X'
584: from dual
585: where exists (select res.resource_id
586: from pa_resources_denorm res,
587: pa_all_organizations org
588: where org.pa_org_use_type = 'EXPENDITURES'
589: and org.inactive_date IS NULL
590: and org.organization_id = res.resource_organization_id

Line 671: from pa_resources_denorm res,

667: l_end_date_active IN DATE) is
668: select 'X'
669: from dual
670: where exists (select res.resource_id
671: from pa_resources_denorm res,
672: pa_all_organizations org
673: where org.pa_org_use_type = 'EXPENDITURES'
674: and org.inactive_date IS NULL
675: and org.organization_id = res.resource_organization_id

Line 2081: -- Logic uses PA_RESOURCES_DENORM table instead of PER_ASSIGNMENTS_F

2077: -- Added parameter p_date in DATE
2078: -- The above parameter p_date stands for period_end_date
2079: -- and is used instead of sysdate used before
2080: -- Changes for Organization Utilization performance improvements
2081: -- Logic uses PA_RESOURCES_DENORM table instead of PER_ASSIGNMENTS_F
2082: FUNCTION Get_People_Assigned(p_org_id in pa_resources_denorm.resource_organization_id%TYPE,
2083: p_date in DATE,
2084: p_emp_type IN VARCHAR DEFAULT 'EMP') RETURN NUMBER --Added p_emp_type for bug 5680366
2085: IS

Line 2082: FUNCTION Get_People_Assigned(p_org_id in pa_resources_denorm.resource_organization_id%TYPE,

2078: -- The above parameter p_date stands for period_end_date
2079: -- and is used instead of sysdate used before
2080: -- Changes for Organization Utilization performance improvements
2081: -- Logic uses PA_RESOURCES_DENORM table instead of PER_ASSIGNMENTS_F
2082: FUNCTION Get_People_Assigned(p_org_id in pa_resources_denorm.resource_organization_id%TYPE,
2083: p_date in DATE,
2084: p_emp_type IN VARCHAR DEFAULT 'EMP') RETURN NUMBER --Added p_emp_type for bug 5680366
2085: IS
2086: l_count NUMBER;

Line 2090: from pa_resources_denorm

2086: l_count NUMBER;
2087: BEGIN
2088: /* Commented for bug 5680366
2089: select count(*) into l_count
2090: from pa_resources_denorm
2091: where resource_organization_id = p_org_id
2092: and p_date between resource_effective_start_date and resource_effective_end_date;
2093: */
2094:

Line 2097: from pa_resources_denorm

2093: */
2094:
2095: IF p_emp_type = 'EMP' THEN -- Added IF for 5680366
2096: select count(*) into l_count
2097: from pa_resources_denorm
2098: where resource_organization_id = p_org_id
2099: and p_date between resource_effective_start_date and resource_effective_end_date
2100: AND RESOURCE_PERSON_TYPE = 'EMP';
2101: ELSIF p_emp_type = 'CWK' THEN -- Added ELSE for 5680366

Line 2103: from pa_resources_denorm

2099: and p_date between resource_effective_start_date and resource_effective_end_date
2100: AND RESOURCE_PERSON_TYPE = 'EMP';
2101: ELSIF p_emp_type = 'CWK' THEN -- Added ELSE for 5680366
2102: select count(*) into l_count
2103: from pa_resources_denorm
2104: where resource_organization_id = p_org_id
2105: and p_date between resource_effective_start_date and resource_effective_end_date
2106: AND RESOURCE_PERSON_TYPE = 'CWK';
2107: ELSE ---Same as earlier

Line 2109: from pa_resources_denorm

2105: and p_date between resource_effective_start_date and resource_effective_end_date
2106: AND RESOURCE_PERSON_TYPE = 'CWK';
2107: ELSE ---Same as earlier
2108: select count(*) into l_count
2109: from pa_resources_denorm
2110: where resource_organization_id = p_org_id
2111: and p_date between resource_effective_start_date and resource_effective_end_date;
2112: END IF;
2113:

Line 2154: /* CURSOR c_suborg(p_org_id in pa_resources_denorm.resource_organization_id%TYPE) IS

2150: p_end_date in DATE,
2151: p_year in NUMBER) RETURN NUMBER
2152: IS
2153: --MOAC Changes : Bug 4363092: Get the value of org from PA_MOAC_UTILS.GET_CURRENT_ORG_ID
2154: /* CURSOR c_suborg(p_org_id in pa_resources_denorm.resource_organization_id%TYPE) IS
2155: select org.child_organization_id c_org
2156: from pa_org_hierarchy_denorm org,
2157: pa_implementations imp
2158: where org.parent_organization_id = p_org_id

Line 2166: CURSOR c_suborg(p_org_id in pa_resources_denorm.resource_organization_id%TYPE) IS

2162: and nvl(org.org_id,nvl(to_number(decode(substr(userenv('client_info'),1,1),' ',null,
2163: substr(userenv('client_info'),1,10))), -99)) =
2164: nvl(to_number(decode(substr(userenv('client_info'),1,1),' ',null,substr(userenv('client_info'),1,10))),-99)
2165: order by org.child_organization_id; */
2166: CURSOR c_suborg(p_org_id in pa_resources_denorm.resource_organization_id%TYPE) IS
2167: select org.child_organization_id c_org
2168: from pa_org_hierarchy_denorm org,
2169: pa_implementations imp
2170: where org.parent_organization_id = p_org_id

Line 2499: -- 4778041 : For performance , removed join of pa_resources_denorm

2495: BEGIN
2496: -- reset current_project_id before set it up again
2497: PA_RESOURCE_UTILS.G_CURRENT_PROJECT_ID := NULL;
2498:
2499: -- 4778041 : For performance , removed join of pa_resources_denorm
2500: SELECT proj.name || '(' || proj.segment1 || ')',
2501: proj.project_id
2502: INTO l_proj_name_number,
2503: l_project_id

Line 2504: FROM --pa_resources_denorm res,

2500: SELECT proj.name || '(' || proj.segment1 || ')',
2501: proj.project_id
2502: INTO l_proj_name_number,
2503: l_project_id
2504: FROM --pa_resources_denorm res,
2505: pa_project_assignments asgmt,
2506: pa_projects_all proj,
2507: pa_project_statuses ps
2508: WHERE --trunc(sysdate) between trunc(res.resource_effective_start_date)

Line 2988: -- records in pa_resources_denorm

2984: -- FUNCTION
2985: -- Is_Future_Resource
2986: -- PURPOSE
2987: -- This procedure checks if a person has only future
2988: -- records in pa_resources_denorm
2989: --
2990: -- HISTORY
2991: -- 31-AUG-2001 created virangan
2992: --

Line 3002: FROM pa_resources_denorm

2998: BEGIN
2999:
3000: SELECT min(resource_effective_start_date)
3001: INTO l_start_date
3002: FROM pa_resources_denorm
3003: WHERE resource_id = p_resource_id
3004: ;
3005:
3006: IF l_start_date <= sysdate THEN

Line 3026: -- records in pa_resources_denorm for a rehire scenario.

3022: -- FUNCTION
3023: -- Is_Future_Rehire -- Added for bug 8988264
3024: -- PURPOSE
3025: -- This procedure checks if a person has
3026: -- records in pa_resources_denorm for a rehire scenario.
3027: --
3028: -- HISTORY
3029: -- 28-DEC-2009 created skkoppul
3030: --

Line 3040: FROM pa_resources_denorm

3036: BEGIN
3037:
3038: SELECT min(resource_effective_start_date)
3039: INTO l_start_date
3040: FROM pa_resources_denorm
3041: WHERE resource_id = p_resource_id
3042: and resource_effective_start_date > sysdate
3043: ;
3044:

Line 3064: -- records in pa_resources_denorm

3060: -- FUNCTION
3061: -- Is_Past_Resource
3062: -- PURPOSE
3063: -- This procedure checks if a person has only past
3064: -- records in pa_resources_denorm
3065: --
3066: -- HISTORY
3067: -- 01-JAN-2009 created asahoo
3068: --

Line 3078: FROM pa_resources_denorm

3074: BEGIN
3075:
3076: SELECT max(resource_effective_end_date)
3077: INTO l_end_date
3078: FROM pa_resources_denorm
3079: WHERE resource_id = p_resource_id ;
3080:
3081: IF l_end_date < sysdate THEN
3082: l_past_res := 'Y';

Line 3100: -- in pa_resources_denorm

3096: -- FUNCTION
3097: -- Get_Resource_Start_date
3098: -- PURPOSE
3099: -- This procedure returns the start date of the resource
3100: -- in pa_resources_denorm
3101: --
3102: -- HISTORY
3103: -- 31-AUG-2001 created virangan
3104: --

Line 3115: FROM pa_resources_denorm

3111: BEGIN
3112:
3113: SELECT min(resource_effective_start_date)
3114: INTO l_start_date
3115: FROM pa_resources_denorm
3116: WHERE resource_id = p_resource_id
3117: ;
3118:
3119: RETURN l_start_date;

Line 3132: -- in pa_resources_denorm for rehires

3128: -- FUNCTION
3129: -- Get_Resource_Start_date_rehire -- Added for bug 8988264
3130: -- PURPOSE
3131: -- This procedure returns the start date of the resource
3132: -- in pa_resources_denorm for rehires
3133: --
3134: -- HISTORY
3135: -- 28-DEC-2009 created skkoppul
3136: --

Line 3147: FROM pa_resources_denorm

3143: BEGIN
3144:
3145: SELECT min(resource_effective_start_date)
3146: INTO l_start_date
3147: FROM pa_resources_denorm
3148: WHERE resource_id = p_resource_id
3149: and resource_effective_start_date > sysdate
3150: ;
3151:

Line 3166: -- in pa_resources_denorm

3162: -- FUNCTION
3163: -- Get_Resource_end_date
3164: -- PURPOSE
3165: -- This procedure returns the end date of the resource
3166: -- in pa_resources_denorm
3167: --
3168: -- HISTORY
3169: -- 28-10-2009 NISINHA Created
3170: --

Line 3181: FROM pa_resources_denorm

3177: BEGIN
3178:
3179: SELECT max(resource_effective_end_date)
3180: INTO l_end_date
3181: FROM pa_resources_denorm
3182: WHERE resource_id = p_resource_id
3183: ;
3184:
3185: RETURN l_end_date;

Line 3199: -- in pa_resources_denorm. This is the resource_effective_start_date

3195: -- FUNCTION
3196: -- Get_Resource_Effective_date
3197: -- PURPOSE
3198: -- This procedure returns the effective date of the resource
3199: -- in pa_resources_denorm. This is the resource_effective_start_date
3200: -- for a future resource or sysdate for active resources
3201: --
3202: -- HISTORY
3203: -- 17-SEP-2001 created virangan

Line 3357: FROM pa_resources_denorm

3353:
3354: BEGIN
3355: SELECT resource_organization_id
3356: INTO l_organization_id
3357: FROM pa_resources_denorm
3358: WHERE p_wk_date BETWEEN resource_effective_start_date AND resource_effective_end_date
3359: AND resource_id = p_resource_id ;
3360:
3361: EXCEPTION

Line 3599: FROM pa_resources_denorm

3595: -- the resource in the supervisor hierarchy
3596: -- Bug # 10323891 begin. Reverting back the changes.
3597: CURSOR check_res_mgr IS
3598: SELECT 'Y'
3599: FROM pa_resources_denorm
3600: WHERE sysdate < resource_effective_end_date
3601: AND manager_id = l_manager_id
3602: START WITH resource_id = l_resource_id
3603: CONNECT BY

Line 3613: FROM pa_resources_denorm res,

3609: -- this cursor checks whether the person is the Staffing Manager of
3610: -- the resource
3611: CURSOR check_staff_mgr IS
3612: SELECT 'Y'
3613: FROM pa_resources_denorm res,
3614: fnd_grants fg,
3615: fnd_objects fob,
3616: per_all_people_f per,
3617: wf_roles wfr,

Line 3642: SELECT 'Y' FROM pa_resources_denorm d

3638: --Bug 10323891 begin.
3639: -- this cursor checks whether the person is an ex-employee and if yes
3640: -- whether person was the last resource manager the supervisor hierarchy
3641: CURSOR check_ex_emp IS
3642: SELECT 'Y' FROM pa_resources_denorm d
3643: WHERE manager_id =l_manager_id
3644: START WITH resource_id =l_resource_id
3645: AND resource_effective_end_date =
3646: (SELECT MAX(resource_effective_end_date)

Line 3647: FROM pa_resources_denorm t1

3643: WHERE manager_id =l_manager_id
3644: START WITH resource_id =l_resource_id
3645: AND resource_effective_end_date =
3646: (SELECT MAX(resource_effective_end_date)
3647: FROM pa_resources_denorm t1
3648: WHERE t1.resource_id = l_resource_id
3649: )
3650: CONNECT BY prior manager_id = person_id
3651: AND NVL(manager_id,-1) <> prior person_id -- Top most level manager's manager_id will be null.

Line 3830: -- start date in the pa_resources_denorm table

3826: -- PROCEDURE
3827: -- validate_person
3828: -- PURPOSE
3829: -- This procedure checks if the resource is valid as of the assignment
3830: -- start date in the pa_resources_denorm table
3831: -- HISTORY
3832: -- 26-FEB-2002 adabdull Created
3833: --
3834: PROCEDURE validate_person ( p_person_id IN NUMBER,

Line 3926: from pa_resources_denorm

3922: and asgn.assignment_type in ('E', 'C'); /* Bug 2777643 */
3923:
3924: cursor validate_resource is
3925: select person_id
3926: from pa_resources_denorm
3927: where person_id = p_person_id
3928: and p_start_date between resource_effective_start_date
3929: and resource_effective_end_date;
3930:

Line 3936: --Cursor which checks if record exists in pa_resources_denorm

3932: BEGIN
3933: PA_DEBUG.set_err_stack('Validate_person');
3934:
3935: -------------------------------------------------------------------------
3936: --Cursor which checks if record exists in pa_resources_denorm
3937: --as of the p_Start_date
3938: -------------------------------------------------------------------------
3939: OPEN validate_resource;
3940: FETCH validate_resource into l_person_id;

Line 4438: FROM pa_resources_denorm

4434: BEGIN
4435:
4436: SELECT person_id, resource_name
4437: INTO x_person_id, x_person_name
4438: FROM pa_resources_denorm
4439: WHERE resource_name like p_person_name
4440: AND rownum=1;
4441:
4442: EXCEPTION

Line 4471: l_manager_name PA_RESOURCES_DENORM.RESOURCE_NAME%TYPE;

4467: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4468: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4469: ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4470: IS
4471: l_manager_name PA_RESOURCES_DENORM.RESOURCE_NAME%TYPE;
4472: l_manager_id NUMBER;
4473: BEGIN
4474:
4475: -- this comes from My Resources page because the manager_name passed

Line 4499: select /* +index PA_RESOURCES_DENORM PA_RESOURCES_DENORM_N2 */

4495: -- is Resource Manager
4496: IF p_responsibility = 'RM' THEN
4497: -- Changes for bug 3616010 - added hint and removed use
4498: -- of view for performance
4499: select /* +index PA_RESOURCES_DENORM PA_RESOURCES_DENORM_N2 */
4500: manager_id into x_manager_id
4501: from pa_resources_denorm --pa_rep_res_mgr_v
4502: where manager_name = l_manager_name
4503: and rownum = 1; -- to stop multiple rows error

Line 4501: from pa_resources_denorm --pa_rep_res_mgr_v

4497: -- Changes for bug 3616010 - added hint and removed use
4498: -- of view for performance
4499: select /* +index PA_RESOURCES_DENORM PA_RESOURCES_DENORM_N2 */
4500: manager_id into x_manager_id
4501: from pa_resources_denorm --pa_rep_res_mgr_v
4502: where manager_name = l_manager_name
4503: and rownum = 1; -- to stop multiple rows error
4504: --dbms_output.put_line('mgr id RM - id null ' || x_manager_id);
4505:

Line 4524: from pa_resources_denorm --pa_rep_res_mgr_v

4520: IF p_responsibility = 'RM' THEN
4521: -- Changes for bug 3616010 - removed use
4522: -- of view for performance
4523: select manager_id into x_manager_id
4524: from pa_resources_denorm --pa_rep_res_mgr_v
4525: where manager_name = l_manager_name
4526: and manager_id = l_manager_id
4527: and rownum = 1;
4528: --dbms_output.put_line('mgr id RM - id not null ' || x_manager_id);

Line 4799: l_resource_person_type PA_RESOURCES_DENORM.RESOURCE_PERSON_TYPE%TYPE ;

4795: IS
4796:
4797: l_formula_id NUMBER;
4798: l_term_type VARCHAR2(1);
4799: l_resource_person_type PA_RESOURCES_DENORM.RESOURCE_PERSON_TYPE%TYPE ;
4800:
4801: l_leav_reas PER_PERIODS_OF_SERVICE.LEAVING_REASON%TYPE ;
4802: l_term_reas PER_PERIODS_OF_PLACEMENT.TERMINATION_REASON%TYPE ;
4803:

Line 4839: FROM pa_resources_denorm

4835: -- Bug 7588937
4836: CURSOR c_res_person_type (c_person_id PA_EMPLOYEES.PERSON_ID%TYPE)
4837: IS
4838: SELECT distinct(resource_person_type)
4839: FROM pa_resources_denorm
4840: WHERE person_id = c_person_id;
4841:
4842: BEGIN
4843:

Line 4857: FROM pa_resources_denorm

4853: -- Start Changes for Bug 7588937
4854:
4855: /*SELECT distinct(resource_person_type)
4856: INTO l_resource_person_type
4857: FROM pa_resources_denorm
4858: WHERE person_id = p_person_id;*/
4859:
4860: OPEN c_res_person_type(p_person_id);
4861: FETCH c_res_person_type INTO l_resource_person_type;

Line 4973: FROM pa_resources_denorm

4969: IF (NVL(G_FTE_FLAG,'N') = 'Y') THEN
4970:
4971: SELECT max(resource_effective_end_date)
4972: INTO l_resource_effective_end_date
4973: FROM pa_resources_denorm
4974: WHERE person_id = G_TERM_PERSON_ID; --l_person_id
4975:
4976: UPDATE pa_resources_denorm
4977: SET resource_effective_end_date = G_FTE_DATE,

Line 4976: UPDATE pa_resources_denorm

4972: INTO l_resource_effective_end_date
4973: FROM pa_resources_denorm
4974: WHERE person_id = G_TERM_PERSON_ID; --l_person_id
4975:
4976: UPDATE pa_resources_denorm
4977: SET resource_effective_end_date = G_FTE_DATE,
4978: last_update_date = sysdate,
4979: last_updated_by = fnd_global.user_id,
4980: last_update_login = fnd_global.login_id

Line 5217: l_resource_person_type PA_RESOURCES_DENORM.RESOURCE_PERSON_TYPE%TYPE ;

5213: x_return_status OUT NOCOPY VARCHAR2,
5214: x_msg_data OUT NOCOPY VARCHAR2,
5215: x_msg_count OUT NOCOPY NUMBER)
5216: IS
5217: l_resource_person_type PA_RESOURCES_DENORM.RESOURCE_PERSON_TYPE%TYPE ;
5218: l_end_date DATE ;
5219: l_valid_end_date DATE ;
5220: l_time_left NUMBER ;
5221:

Line 5232: FROM pa_resources_denorm

5228: x_return_status := FND_API.G_RET_STS_SUCCESS;
5229:
5230: SELECT distinct(resource_person_type)
5231: INTO l_resource_person_type
5232: FROM pa_resources_denorm
5233: WHERE person_id = p_person_id;
5234:
5235: IF ( l_resource_person_type = 'EMP') THEN
5236:

Line 5327: FROM pa_resources_denorm

5323:
5324:
5325: SELECT TRUNC (MAX (resource_effective_end_date))
5326: INTO l_max_res_denorm_end_date
5327: FROM pa_resources_denorm
5328: WHERE person_id = p_person_id;
5329:
5330: IF ( (l_max_res_denorm_end_date <> to_date('31/12/4712','DD/MM/YYYY'))
5331: AND