[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM per_all_people_f
WHERE full_name = p_resource_name
and rownum=1;
SELECT 'Y'
FROM pa_party_resource_details_v
WHERE party_name = p_resource_name
and rownum=1;
SELECT person_id
FROM per_all_people_f
WHERE full_name = p_resource_name
AND trunc(p_date) between trunc(effective_start_date)
and trunc(effective_end_date);
SELECT pa_resource_utils.get_resource_id(p_resource_id)
INTO l_pa_resource_id
FROM dual;
SELECT nvl(future_term_wf_flag,'N')
INTO l_future_term_wf_flag
FROM pa_resources
WHERE resource_id = l_pa_resource_id;
SELECT DISTINCT(prd.person_id)
INTO x_resource_id
FROM pa_resources_denorm prd
WHERE prd.person_id = p_resource_id
AND trunc(p_date) > = (Select trunc(min(prd1.resource_effective_start_date))
from pa_resources_denorm prd1
where prd1.person_id = prd.person_id)
AND (trunc(p_end_date) is null
OR
trunc(p_end_date) < = (Select trunc(max(prd2.resource_effective_end_date))
from pa_resources_denorm prd2
where prd2.person_id = prd.person_id) );
SELECT per.person_id
-- type.system_person_type -- FP M CWK
INTO x_resource_id
-- l_sys_per_type
FROM per_all_people_f per
-- per_person_types type
WHERE per.person_id = p_resource_id
-- AND per.person_type_id = type.person_type_id
AND trunc(p_date) between trunc(per.effective_start_date) and trunc(per.effective_end_date)
AND (p_end_date is null
OR
-- Start changes for Bug 6828493
--(trunc(p_end_date) between trunc(per.effective_start_date) and trunc(per.effective_end_date))
(trunc(p_end_date) < = (Select trunc(max(per2.effective_end_date))
from per_all_people_f per2
where per2.person_id = p_resource_id
AND (per2.current_employee_flag = 'Y' OR per2.current_npw_flag = 'Y')) -- AND Codn added for bug 6851095
AND trunc(p_end_date) > = (Select trunc(min(per3.effective_start_date))
from per_all_people_f per3
where per3.person_id = p_resource_id
AND (per3.current_employee_flag = 'Y' OR per3.current_npw_flag = 'Y')))) -- AND Codn added for bug 6851095
-- End changes for Bug 6828493
AND (per.current_employee_flag = 'Y'
OR
per.current_npw_flag = 'Y'); -- FP M CWK
/* SELECT type.system_person_type
INTO l_sys_per_type
FROM per_all_people_f per,
per_person_types type
WHERE per.person_id = x_resource_id
AND per.person_type_id = type.person_type_id
AND trunc(p_date) between trunc(per.effective_start_date)
and trunc(per.effective_end_date)
AND (per.current_employee_flag = 'Y' OR -- Added this check for bug#2683266
per.current_npw_flag = 'Y'); -- FP M CWK */
/* SELECT type.system_person_type
INTO l_sys_per_type
FROM per_all_people_f per,
per_person_types type
WHERE per.person_id = x_resource_id
AND per.person_type_id = type.person_type_id
AND trunc(p_date) between trunc(per.effective_start_date) and trunc(per.effective_end_date)
AND (per.current_employee_flag = 'Y' OR -- Added this check for bug#2683266
per.current_npw_flag = 'Y'); -- FP M CWK */
SELECT per.person_id
-- type.system_person_type -- FP M CWK
INTO x_resource_id
-- l_sys_per_type -- FP M CWK
FROM per_all_people_f per
-- per_person_types type -- FP M CWK
WHERE per.full_name = p_resource_name
-- AND per.person_type_id = type.person_type_id -- FP M CWK
AND trunc(p_date) between trunc(per.effective_start_date)
and trunc(per.effective_end_date)
AND (p_end_date is null OR (trunc(p_end_date) between trunc(per.effective_start_date) -- 3235018 Added end date condition
and trunc(per.effective_end_date)))
AND (per.current_employee_flag = 'Y' OR /* Added this check for bug#2683266 */
per.current_npw_flag = 'Y'); -- FP M CWK
select resource_type_id
into x_resource_type_id
from pa_resource_types
where resource_type_code = decode(l_sys_per_type,
'EMP','EMPLOYEE');
x_resource_type_id := l_resource_type_id; /* Added for bug#2683266 as the earlier select is commented */
SELECT party_id
INTO x_resource_id
FROM pa_party_resource_details_v hz
WHERE hz.party_id = p_resource_id
AND trunc(p_date) between trunc(hz.start_date)
and trunc(nvl(hz.end_date, to_date('31-12-4712', 'DD-MM-YYYY')));
SELECT party_id
INTO x_resource_id
FROM pa_party_resource_details_v hz
WHERE hz.party_name = p_resource_name
AND trunc(p_date) between trunc(hz.start_date)
and trunc(nvl(hz.end_date, to_date('31-12-4712', 'DD-MM-YYYY')));
select jtf_resource_id
into x_jtf_resource_id
from pa_resources
where resource_id = l_resource_id;
select a.jtf_resource_id
into x_jtf_resource_id
from pa_resources a, pa_project_parties b
where a.resource_id = b.resource_id
and b.project_party_id = l_project_player_id;
select
resource_org_id
from
pa_resources_denorm
where
resource_id = p_resource_id
and p_start_date between resource_effective_start_date
and resource_effective_end_date;
select
'Y'
into
X_CC_OK
FROM HR_ORGANIZATION_INFORMATION PLE,
HR_ORGANIZATION_INFORMATION RLE,
pa_project_types_all PT,
pa_projects_all P,
pa_implementations_all iprv,
pa_implementations_all irecv
WHERE P.project_type = PT.project_type
AND NVL(P.template_flag, 'N') <> 'Y'
--Bug2538692 AND pa_security.allow_query(P.project_id) = 'Y'
AND ((iprv.business_group_id = irecv.business_group_id
and pa_cross_business_grp.IsCrossBGProfile='N')
OR pa_cross_business_grp.IsCrossBGProfile ='Y')
-- bug 8967761 .. below 2 are changed to remove IS NULL
--AND (irecv.org_id IS NULL OR irecv.org_id = P.org_id)
--AND (PT.org_id IS NULL or PT.org_id = P.org_id)
AND (irecv.org_id = P.org_id)
AND (PT.org_id = P.org_id)
AND PT.project_type <> 'AWARD_PROJECT'
AND nvl(PT.cc_prvdr_flag, 'N') <> 'Y'
AND PLE.organization_id (+) = iprv.org_id
AND PLE.org_information_context (+) = 'Operating Unit Information'
AND RLE.organization_id (+) = irecv.org_id
AND RLE.org_information_context (+) = 'Operating Unit Information'
AND ( P.org_id = iprv.org_id
OR
( PLE.org_information2 = RLE.org_information2
AND ( EXISTS ( SELECT null FROM PA_CC_ORG_RELATIONSHIPS CO
WHERE CO.prvdr_org_id = iprv.org_id
AND CO.recvr_org_id = irecv.org_id
AND CO.prvdr_allow_cc_flag = 'Y')
OR
(iprv.cc_allow_iu_flag = 'Y'
AND NOT EXISTS ( SELECT null FROM
PA_CC_ORG_RELATIONSHIPS CO
WHERE CO.prvdr_org_id = iprv.org_id
AND CO.recvr_org_id = irecv.org_id
AND CO.prvdr_allow_cc_flag = 'N')
)
)
)
OR
( PLE.org_information2 <> RLE.org_information2
AND PT.project_type_class_code <> 'CAPITAL'
AND EXISTS ( SELECT null FROM PA_CC_ORG_RELATIONSHIPS CO
WHERE CO.prvdr_org_id = iprv.org_id
AND CO.recvr_org_id = irecv.org_id
AND CO.prvdr_allow_cc_flag = 'Y'
AND (( CO.prvdr_project_id IS NOT NULL
AND CO.vendor_site_id IS NOT NULL
AND CO.cross_charge_code = 'I')
OR CO.cross_charge_code = 'N' )
)
)
)
AND P.project_id = p_project_id
AND iprv.org_id = l_prvdr_org_id;
select 'X'
from dual
where exists (select res.resource_id
from pa_resources_denorm res,
pa_all_organizations org
where org.pa_org_use_type = 'EXPENDITURES'
and org.inactive_date IS NULL
and org.organization_id = res.resource_organization_id
and res.resource_id = p_resource_id
and TRUNC(l_date) between TRUNC(res.resource_effective_start_date) and TRUNC(res.resource_effective_end_date));
select 'X'
from dual
where exists (select res.resource_id
from pa_resources_denorm res,
pa_all_organizations org
where org.pa_org_use_type = 'EXPENDITURES'
and org.inactive_date IS NULL
and org.organization_id = res.resource_organization_id
and res.resource_id = p_resource_id
and l_start_date_active between get_resource_start_date(p_resource_id) and get_resource_end_date(p_resource_id)
and l_end_date_active <= get_resource_end_date(p_resource_id));
PROCEDURE set_global_variables( p_selected_flag IN VARCHAR2
,p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE
,p_version_id IN PER_ORG_STRUCTURE_ELEMENTS.ORG_STRUCTURE_VERSION_ID%TYPE
,p_start_org_id IN PER_ORG_STRUCTURE_ELEMENTS.ORGANIZATION_ID_PARENT%TYPE
)
IS
BEGIN
G_SELECTED_FLAG := p_selected_flag;
FUNCTION get_selected_flag RETURN VARCHAR2
IS
BEGIN
RETURN G_SELECTED_FLAG;
END get_selected_flag;
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = p_org_id;
PROCEDURE insert_grant( p_person_id IN NUMBER
,p_org_id IN NUMBER
,p_role_name IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_grant_id FND_GRANTS.GRANT_GUID%TYPE;
, p_procedure_name => 'insert_grant'
, p_error_text => SUBSTRB(SQLERRM,1,100));
END insert_grant;
PROCEDURE delete_grant( p_person_id IN NUMBER
,p_org_id IN NUMBER
,p_role_name IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_set_id FND_GRANTS.INSTANCE_SET_ID%TYPE;
, p_procedure_name => 'delete_grant'
, p_error_text => SUBSTRB(SQLERRM,1,100));
END delete_grant;
select
distinct pg.grade_id,pg.sequence
from
per_job_groups pjg,
per_grades pg,
per_valid_grades pvg
where
pjg.master_flag = 'Y'
and pjg.job_group_id = P_Job_Grp_Id
and pvg.job_id = P_Job_Id
and pg.grade_id = pvg.grade_id
and trunc(sysdate) between pvg.date_from and nvl(pvg.date_to,trunc(sysdate))
UNION
select
distinct pg.grade_id,pg.sequence
from
per_valid_grades pvg,
pa_job_relationships pjr,
per_job_groups pjg,
per_grades pg
where
pjg.master_flag = 'Y'
and pjr.from_job_id = P_Job_Id
and pjr.to_job_id = pvg.job_id
and pjr.to_job_group_id = pjg.job_group_id
and pg.grade_id = pvg.grade_id
and trunc(sysdate) between pvg.date_from and nvl(pvg.date_to,trunc(sysdate))
UNION
select
distinct pg.grade_id,pg.sequence
from
per_valid_grades pvg,
pa_job_relationships pjr,
per_job_groups pjg,
per_grades pg
where
pjg.master_flag = 'Y'
and pjr.to_job_id = P_Job_Id
and pjr.from_job_id = pvg.job_id
and pjr.from_job_group_id = pjg.job_group_id
and pg.grade_id = pvg.grade_id
and trunc(sysdate) between pvg.date_from and nvl(pvg.date_to,trunc(sysdate))
UNION
select
distinct pg.grade_id,pg.sequence
from
per_job_groups pjg,
per_grades pg,
per_valid_grades pvg
where pjg.master_flag = 'N'
and pjg.job_group_id = P_Job_Grp_Id
and pvg.job_id = P_Job_Id
and pg.grade_id = pvg.grade_id
and not exists (select null
from per_job_groups
where master_flag = 'Y')
and trunc(sysdate) between pvg.date_from and nvl(pvg.date_to,trunc(sysdate));*/
select to_job_id
into X_To_JobId
from pa_job_relationships_view
where from_job_group_id = P_From_Forecast_JobGrpId
and to_job_group_id = P_To_Proj_Cost_JobGrpId
and from_job_id = P_From_JobId;
SELECT name
INTO X_To_JobName
FROM per_jobs
WHERE job_id = P_From_JobId
AND ROWNUM = 1;
select to_job_name
into X_To_JobName
from pa_job_relationships_view
where from_job_group_id = P_From_Forecast_JobGrpId
and to_job_group_id = P_To_Proj_Cost_JobGrpId
and from_job_id = P_From_JobId;
SELECT distinct per.person_id
FROM fnd_grants fg, wf_roles wfr, per_all_people_f per
WHERE fg.object_id = objid
AND fg.instance_pk1_value = TO_CHAR(orgid)
AND fg.menu_id = roleid
AND fg.instance_type = 'INSTANCE'
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND per.party_id = wfr.orig_system_id
AND sysdate between per.effective_start_date and
per.effective_end_date
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
AND trunc(NVL(fg.end_date, SYSDATE+1));
SELECT 'Y'
INTO v_validated
FROM per_all_people_f -- Bug 4684198 - use base table not view
WHERE person_id = P_PersonId
AND trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date
-- Bug 4684198 - remove nvl on effective_end_date - col is not null
AND rownum = 1;
SELECT organization_id
INTO v_orgid
FROM per_all_assignments_f -- Bug 4684198 - use base table not view
WHERE person_id = P_PersonId
AND trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date
-- Bug 4684198 - remove nvl on effective_end_date - col is not null
AND primary_flag = 'Y'
AND assignment_type in ('E', 'C'); /* added for bug 2745823 */
SELECT distinct object_id
INTO v_objid
FROM fnd_objects
WHERE obj_name = v_objname;
SELECT pep.person_id,
--distinct to_number(substr(fg.grantee_key,instr(fg.grantee_key,':')+1)),
pep.full_name
INTO P_PrimaryContactId,P_PrimaryContactName
FROM fnd_grants fg,
per_all_people_f pep, -- Bug 4684198 - use table
wf_roles wfr
WHERE fg.object_id = v_objid
AND fg.instance_pk1_value = TO_CHAR(v_orgid)
AND fg.menu_id = v_Primroleid
AND fg.instance_type = 'INSTANCE'
AND wfr.orig_system = 'HZ_PARTY'
AND pep.party_id = wfr.orig_system_id
-- AND 'PER:' || pep.person_id = fg.grantee_key
-- AND pep.person_id = substr(fg.grantee_key,instr(fg.grantee_key,':')+1)
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
AND trunc(NVL(fg.end_date, SYSDATE+1))
AND trunc(sysdate) BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND wfr.name = fg.grantee_key; -- added for perf bug 4887312
SELECT full_name
INTO P_ResourceAnalystTab(j)
FROM per_all_people_f -- Bug 4684198
WHERE person_id = P_ResourceIdTab(j)
AND trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date;
SELECT distinct pep.person_id -- changes for 11.5.10 security
-- to_number(substr(fg.grantee_key,instr(fg.grantee_key,':')+1))
,pep.full_name
INTO x_PrimaryContactId,x_PrimaryContactName
FROM fnd_grants fg,
per_all_people_f pep, -- Bug 4684198 - use table
wf_roles wfr
WHERE fg.object_id = P_objid
AND fg.instance_pk1_value = to_char(P_orgid)
AND fg.menu_id = P_Primroleid
AND fg.instance_type = 'INSTANCE'
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND pep.party_id = wfr.orig_system_id
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
AND trunc(NVL(fg.end_date, SYSDATE+1))
-- AND 'PER:' || pep.person_id = fg.grantee_key --bug 2795616:perfomance
-- AND pep.person_id = substr(fg.grantee_key,instr(fg.grantee_key,':')+1)
AND p_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
and (PEP.current_employee_flag = 'Y' or PEP.current_npw_flag = 'Y');
SELECT assn.supervisor_id,pep.full_name
INTO x_ManagerId,x_ManagerName
FROM per_all_assignments_f assn,per_all_people_f pep
WHERE assn.person_id = P_personId
AND pep.person_id = assn.supervisor_id
AND trunc(p_start_date) BETWEEN assn.effective_start_date /*Bug 8817301 */
AND assn.effective_end_date
AND trunc(sysdate) BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND primary_flag = 'Y'
AND assignment_type in ('C', 'E');
SELECT assn.supervisor_id,pep.full_name
INTO x_ManagerId,x_ManagerName
FROM per_all_assignments_f assn,
per_all_people_f pep
WHERE assn.person_id = P_personId
AND pep.person_id = assn.supervisor_id
AND trunc(p_start_date) BETWEEN assn.effective_start_date /*Bug 8817301 */
AND assn.effective_end_date
AND trunc(sysdate) BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND primary_flag = 'Y'
AND assignment_type = 'B';
SELECT assn.supervisor_id,pep.full_name
INTO x_ManagerId,x_ManagerName
FROM per_all_assignments_f assn,per_all_people_f pep
WHERE assn.person_id = P_personId
AND pep.person_id = assn.supervisor_id
AND trunc(p_start_date) BETWEEN assn.effective_start_date /*Bug 8817301 */
AND assn.effective_end_date
AND trunc(sysdate) BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND primary_flag = 'Y'
AND assignment_type in ('C', 'E') /* added for bug 2745823 */
AND ((SELECT per_system_status
FROM per_assignment_status_types past
WHERE past.assignment_status_type_id = assn.assignment_status_type_id) IN ('ACTIVE_ASSIGN','ACTIVE_CWK')); --Bug#8879958
SELECT organization_id
INTO x_orgid
FROM per_all_assignments_f
WHERE person_id = P_personId
AND trunc(p_start_date) BETWEEN effective_start_date --bug 9403051
AND effective_end_date
AND primary_flag = 'Y'
AND assignment_type in ('E', 'C'); /* added for bug 2745823 */
SELECT Person_id
INTO x_personId
FROM pa_resource_txn_attributes
WHERE resource_id = P_ResourceId;
SELECT 'Y'
INTO v_validated
FROM per_people_f
WHERE person_id = x_personId
AND trunc(sysdate) BETWEEN effective_start_date
AND NVL(effective_end_date,sysdate + 1)
AND rownum = 1;
SELECT distinct object_id
INTO x_objid
FROM fnd_objects
WHERE obj_name = P_objname;
SELECT start_date
INTO l_start_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id
;
SELECT resource_id
INTO l_resource_id
FROM pa_resource_txn_attributes
WHERE person_id = p_person_id;
select count(*) into l_count
from pa_resources_denorm
where resource_organization_id = p_org_id
and p_date between resource_effective_start_date and resource_effective_end_date;
select count(*) into l_count
from pa_resources_denorm
where resource_organization_id = p_org_id
and p_date between resource_effective_start_date and resource_effective_end_date
AND RESOURCE_PERSON_TYPE = 'EMP';
select count(*) into l_count
from pa_resources_denorm
where resource_organization_id = p_org_id
and p_date between resource_effective_start_date and resource_effective_end_date
AND RESOURCE_PERSON_TYPE = 'CWK';
select count(*) into l_count
from pa_resources_denorm
where resource_organization_id = p_org_id
and p_date between resource_effective_start_date and resource_effective_end_date;
select org.child_organization_id c_org
from pa_org_hierarchy_denorm org,
pa_implementations imp
where org.parent_organization_id = p_org_id
and org.parent_level - org.child_level = 1
and org.pa_org_use_type = 'REPORTING'
and org.org_hierarchy_version_id = imp.org_structure_version_id
and nvl(org.org_id,nvl(to_number(decode(substr(userenv('client_info'),1,1),' ',null,
substr(userenv('client_info'),1,10))), -99)) =
nvl(to_number(decode(substr(userenv('client_info'),1,1),' ',null,substr(userenv('client_info'),1,10))),-99)
order by org.child_organization_id; */
select org.child_organization_id c_org
from pa_org_hierarchy_denorm org,
pa_implementations imp
where org.parent_organization_id = p_org_id
and org.parent_level - org.child_level = 1
and org.pa_org_use_type = 'REPORTING'
and org.org_hierarchy_version_id = imp.org_structure_version_id
and nvl(org.org_id,NVL(PA_MOAC_UTILS.GET_CURRENT_ORG_ID,-99)) = NVL(PA_MOAC_UTILS.GET_CURRENT_ORG_ID,-99)
order by org.child_organization_id;
select glp.end_date into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.period_name = p_period_name;
select pap.pa_end_date into l_date
from pa_periods_v pap,
pa_implementations pai
where pap.period_name = p_period_name
and pai.set_of_books_id = pap.set_of_books_id;
/* Select from pa_periods_v is replaced by view definition for Perfomance
Bug 2634995 */
SELECT pap.end_date
INTO l_date
FROM PA_PERIODS PAP,
GL_PERIOD_STATUSES GLP,
PA_IMPLEMENTATIONS PAIMP,
PA_LOOKUPS PAL
WHERE PAP.period_name = p_period_name
AND PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
AND GLP.APPLICATION_ID = Pa_Period_Process_Pkg.Application_id
AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GLP.CLOSING_STATUS = PAL.LOOKUP_CODE /*Added for bug 5484203*/
AND PAL.LOOKUP_TYPE = 'CLOSING STATUS';
select max(glp.end_date)
into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.period_year = p_year;
select max(glp.end_date)
into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.quarter_num = to_number(p_period_name)
and glp.period_year = p_year;
select glp.start_date into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.period_name = p_period_name;
select pap.pa_start_date into l_date
from pa_periods_v pap,
pa_implementations pai
where pap.period_name = p_period_name
and pai.set_of_books_id = pap.set_of_books_id;
/* Select from pa_periods_v is replaced by view definition for Perfomance
Bug 2634995 */
SELECT pap.start_date
INTO l_date
FROM PA_PERIODS PAP,
GL_PERIOD_STATUSES GLP,
PA_IMPLEMENTATIONS PAIMP,
PA_LOOKUPS PAL
WHERE PAP.period_name = p_period_name
AND PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
AND GLP.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
AND GLP.APPLICATION_ID = Pa_Period_Process_Pkg.Application_id
AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND PAL.LOOKUP_TYPE = 'CLOSING STATUS';
select min(glp.start_date)
into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.period_year = p_year;
select min(glp.start_date)
into l_date
from gl_periods glp,
gl_sets_of_books glsob,
pa_implementations pai
where pai.set_of_books_id = glsob.set_of_books_id
and glsob.period_set_name = glp.period_set_name
and glsob.accounted_period_type = glp.period_type
and glp.quarter_num = to_number(p_period_name)
and glp.period_year = p_year;
SELECT employee_id
INTO l_root_manager_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT sum(capacity_quantity)
INTO capacity
FROM pa_forecast_items
WHERE resource_id = res_id
AND delete_flag = 'N'
AND forecast_item_type = 'U'
AND item_date between week_start_date
and week_start_date + 6;
SELECT proj.name || '(' || proj.segment1 || ')',
proj.project_id
INTO l_proj_name_number,
l_project_id
FROM --pa_resources_denorm res,
pa_project_assignments asgmt,
pa_projects_all proj,
pa_project_statuses ps
WHERE --trunc(sysdate) between trunc(res.resource_effective_start_date)
-- and trunc(res.resource_effective_end_date)
--AND res.resource_id = asgmt.resource_id
--AND res.resource_id = p_resource_id
asgmt.resource_id = p_resource_id
AND trunc(sysdate) BETWEEN trunc(asgmt.start_date) and trunc(asgmt.end_date)
AND asgmt.project_id = proj.project_id
AND asgmt.status_code = ps.project_status_code
AND ps.project_system_status_code = 'STAFFED_ASGMT_CONF'
AND rownum=1;
/* changed the select statement to be based on per_all_people_f and added
the sysdate condition for bug 2983491 */
SELECT full_name
INTO x_person_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND EFFECTIVE_START_DATE = (SELECT MIN(EFFECTIVE_START_DATE) FROM per_all_people_f
WHERE person_id = p_person_id
and trunc(EFFECTIVE_END_DATE) >= trunc(sysdate));
SELECT address_line_1,
address_line_2,
address_line_3,
town_or_city,
postal_code,
country
INTO x_address_line_1,
x_address_line_2,
x_address_line_3,
x_town_or_city,
x_postal_code,
x_country
FROM hr_locations
WHERE location_id = p_location_id;
SELECT to_number(ou.org_information1)
INTO x_default_ou
FROM hr_organization_information ou
WHERE ou.org_information_context = 'Exp Organization Defaults'
AND ou.organization_id = p_organization_id
AND rownum = 1;
SELECT to_number(cal.org_information1)
INTO x_default_cal_id
FROM hr_organization_information cal-- R12 HR Org Info change
WHERE cal.organization_id = p_organization_id
AND cal.org_information_context = 'Resource Defaults'
AND rownum = 1;
SELECT 'Y'
INTO x_valid
FROM pa_all_organizations o,
pa_implementations i -- Added for bug 2673140
WHERE o.pa_org_use_type = 'EXPENDITURES'
AND o.inactive_date is null
AND o.organization_id = p_organization_id
AND rownum = 1
AND o.org_id = i.org_id; -- Added for bug 2673140
SELECT 'Y'
INTO x_valid
FROM pa_all_organizations
WHERE pa_org_use_type = 'EXPENDITURES'
AND inactive_date is null
AND organization_id = p_organization_id
AND rownum = 1;
SELECT 'Y'
INTO x_valid
FROM pa_resource_txn_attributes
WHERE person_id = p_person_id
AND rownum = 1;
SELECT 'Y'
INTO x_valid
FROM pa_resource_txn_attributes
WHERE party_id = p_party_id
AND rownum = 1;
select pa_security_pvt.get_menu_id(p_prim_role_name)
into l_menu_id
from dual;
SELECT pep.full_name,
pep.person_id
INTO l_primary_contact_name,
l_primary_contact_id
FROM fnd_grants fg,
fnd_objects fob,
per_all_people_f pep, -- Bug 4684198 - use base table not secure view
wf_roles wfr
/* Bug# 2499051 - Moved the function call to fetch to a local variable at the start of the procedure
(select pa_security_pvt.get_menu_id(p_prim_role_name) menu_id from dual) temp */
WHERE fg.object_id = fob.object_id
AND fob.obj_name = 'ORGANIZATION'
AND fg.instance_pk1_value = to_char(p_org_id)
/* AND fg.menu_id = temp.menu_id -- Bug# 2499051 - Using local variable */
AND fg.menu_id = l_menu_id /* Bug# 2499051 */
AND fg.instance_type = 'INSTANCE'
AND fg.grantee_type = 'USER'
AND trunc(SYSDATE) BETWEEN trunc(fg.start_date)
AND trunc(NVL(fg.end_date, SYSDATE+1))
-- AND 'PER:' || pep.person_id = fg.grantee_key --bug 2795616:perfomance
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND pep.party_id = wfr.orig_system_id -- Added for 11.5.10 security
-- AND PEP.PERSON_ID = substr(fg.grantee_key,instr(fg.grantee_key,':')+1)
AND sysdate BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND (pep.current_employee_flag = 'Y' OR pep.current_npw_flag = 'Y'); -- Added for bug 4938392
SELECT min(resource_effective_start_date)
INTO l_start_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
;
SELECT min(resource_effective_start_date)
INTO l_start_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
and resource_effective_start_date > sysdate
;
SELECT max(resource_effective_end_date)
INTO l_end_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id ;
SELECT min(resource_effective_start_date)
INTO l_start_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
;
SELECT min(resource_effective_start_date)
INTO l_start_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
and resource_effective_start_date > sysdate
;
SELECT max(resource_effective_end_date)
INTO l_end_date
FROM pa_resources_denorm
WHERE resource_id = p_resource_id
;
SELECT P.EFFECTIVE_START_DATE
INTO l_start_date
FROM PER_ALL_PEOPLE_F P
WHERE P.PERSON_ID = p_person_id
AND p.EFFECTIVE_START_DATE = (SELECT MIN(PP.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = p_person_id
AND PP.EFFECTIVE_END_DATE >= SYSDATE)
AND (P.EMPLOYEE_NUMBER IS NOT NULL OR
P.npw_number is not null); -- FP M CWK
SELECT MIN(PP.EFFECTIVE_START_DATE)
INTO l_start_date
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = p_person_id
AND (PP.CURRENT_EMPLOYEE_FLAG='Y' OR PP.CURRENT_NPW_FLAG = 'Y')
AND (PP.EMPLOYEE_NUMBER IS NOT NULL OR
PP.npw_number is not null);-- FP M CWK
SELECT sum(capacity_quantity)
INTO l_capacity
FROM pa_forecast_items
WHERE resource_id = p_resource_id
AND delete_flag = 'N'
AND forecast_item_type = 'U'
AND item_date between p_start_date
and p_end_date;
SELECT resource_organization_id
INTO l_organization_id
FROM pa_resources_denorm
WHERE p_wk_date BETWEEN resource_effective_start_date AND resource_effective_end_date
AND resource_id = p_resource_id ;
SELECT jcra.calendar_id
INTO l_calendar_id
FROM pa_resources par,
jtf_cal_resource_assign jcra
WHERE par.jtf_resource_id = jcra.resource_id
AND par.resource_id = p_resource_id
AND p_wk_date between jcra.start_date_time and nvl(jcra.end_date_time,to_date('31-12-4712', 'DD-MM-YYYY'));
SELECT decode( to_char( p_wk_date,'D'),
'1',SUNDAY_HOURS,
'2',MONDAY_HOURS,
'3',TUESDAY_HOURS,
'4',WEDNESDAY_HOURS,
'5',THURSDAY_HOURS,
'6',FRIDAY_HOURS,
SATURDAY_HOURS )
INTO x_capacity
FROM pa_schedules pas
WHERE p_wk_date BETWEEN pas.start_date AND pas.end_date
AND pas.SCHEDULE_TYPE_CODE = 'CALENDAR'
AND pas.CALENDAR_ID = l_calendar_id
;
select menu_name
into l_menu_name
from fnd_menus menu,
fnd_responsibility resp
where resp.responsibility_id = fnd_global.resp_id
and resp.menu_id = menu.menu_id
and resp.application_id = fnd_global.resp_appl_id ;
select pfi.global_exp_period_end_date,
pfi.item_date,
sum(decode(pfi.provisional_flag,'Y',pfi.item_quantity,0)),
sum(decode(pfi.provisional_flag,'N',pfi.item_quantity,0)),
pfi.resource_id
into l_date,
l_date2,
g_provisional_hours,
g_confirmed_hours,
l_resource_id
from pa_forecast_items pfi
where pfi.forecast_item_type = 'A'
and pfi.delete_flag = 'N'
and pfi.resource_id = p_resource_id
and pfi.item_date = p_week_date
group by pfi.global_exp_period_end_date,
pfi.item_date,
pfi.resource_id;
SELECT 'Y'
FROM pa_resources_denorm
WHERE sysdate < resource_effective_end_date
AND manager_id = l_manager_id
START WITH resource_id = l_resource_id
CONNECT BY
prior manager_id = person_id
and manager_id <> prior person_id
and sysdate < resource_effective_end_date
and sysdate < prior resource_effective_end_date;
SELECT 'Y'
FROM pa_resources_denorm res,
fnd_grants fg,
fnd_objects fob,
per_all_people_f per,
wf_roles wfr,
(select pa_security_pvt.get_menu_id('PA_PRM_RES_AUTH') menu_id
from dual) res_auth_menu
WHERE fob.obj_name = 'ORGANIZATION'
and res.resource_id = l_resource_id
and trunc(sysdate) < res.resource_effective_end_date --Bug 9403051
and fg.instance_pk1_value = to_char(res.resource_organization_id)
and fg.instance_type = 'INSTANCE'
and fg.object_id = fob.object_id
and fg.grantee_type = 'USER'
and fg.menu_id = res_auth_menu.menu_id
and trunc(SYSDATE) between trunc(fg.start_date)
and trunc(NVL(fg.end_date, SYSDATE+1))
-- and fg.grantee_key = 'PER:'|| per.person_id
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND per.party_id = wfr.orig_system_id -- Added for 11.5.10 security
and trunc(SYSDATE) between per.effective_start_date and per.effective_end_date --Bug 9403051
and per.person_id <> res.manager_id
and per.person_id = l_manager_id;
SELECT 'Y' FROM pa_resources_denorm d
WHERE manager_id =l_manager_id
START WITH resource_id =l_resource_id
AND resource_effective_end_date =
(SELECT MAX(resource_effective_end_date)
FROM pa_resources_denorm t1
WHERE t1.resource_id = l_resource_id
)
CONNECT BY prior manager_id = person_id
AND NVL(manager_id,-1) <> prior person_id -- Top most level manager's manager_id will be null.
AND prior resource_effective_end_date between resource_effective_start_date
and resource_effective_end_date; -- will pull the manager of resource at the time of resource end date
SELECT person_id
INTO l_person_id
FROM pa_resource_txn_attributes
WHERE resource_id = p_resource_id;
SELECT person_id
INTO l_person_id
FROM per_all_people_f
WHERE party_id = p_party_id
AND trunc(sysdate) between trunc(effective_start_date)
and trunc(effective_end_date);
SELECT person_id
FROM per_all_people_f
WHERE party_id = p_party_id
AND CURRENT_EMPLOYEE_FLAG = 'Y'
AND trunc(sysdate) between trunc(effective_start_date)
and trunc(effective_end_date);
SELECT person_id
FROM per_all_people_f
WHERE party_id = p_party_id
and trunc(effective_end_date) =
(select max(trunc(effective_end_date))
from per_all_people_f
WHERE trunc(sysdate) not between trunc(effective_start_date)
and trunc(effective_end_date)
and party_id = p_party_id);
select 'Y'
from per_all_people_f per
where per.person_id = p_person_id
and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
and p_effective_start_date between per.effective_start_date
and per.effective_end_date;
select person_id
from per_people_f per,
per_person_types ptype
where per.person_id = p_person_id
and per.person_type_id = ptype.person_type_id
and (ptype.system_person_type = 'EMP'
OR ptype.system_person_type = 'EMP_APL');
select person_id
from per_all_people_f per
where per.person_id = p_person_id
and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y');
select asgn.assignment_id
from per_all_assignments_f asgn,
per_assignment_status_types status,
(select person_id, actual_termination_date
from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po -- FP M CWK
where asgn.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
and po.person_id = p_person_id
and asgn.assignment_status_type_id = status.assignment_status_type_id
and status.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
and p_start_date between asgn.effective_start_date
and asgn.effective_end_date
and asgn.assignment_type in ('E', 'C'); /* Bug 2777643 */
select asgn.assignment_id
from per_all_assignments_f asgn,
(select person_id, actual_termination_date
from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po -- FP M CWK
where asgn.person_id = p_person_id
and asgn.primary_flag = 'Y'
and po.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
-- and po.period_of_service_id = asgn.period_of_service_id
and p_start_date between asgn.effective_start_date and asgn.effective_end_date
and asgn.assignment_type in ('E', 'C'); /* Bug 2777643 */
select asgn.job_id
from per_all_assignments_f asgn,
(select person_id, actual_termination_date
from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po -- FP M CWK
where asgn.person_id = p_person_id
and asgn.primary_flag = 'Y'
and po.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
-- and pos.period_of_service_id = asgn.period_of_service_id
and asgn.job_id is not null
and p_start_date between asgn.effective_start_date
and asgn.effective_end_date
and asgn.assignment_type in ('E', 'C'); /* Bug 2777643 */
select person_id
from pa_resources_denorm
where person_id = p_person_id
and p_start_date between resource_effective_start_date
and resource_effective_end_date;
SELECT party_id
INTO l_party_id
FROM pa_resource_txn_attributes
WHERE resource_id = p_resource_id;
SELECT resource_type_code
INTO l_resource_type
FROM pa_resources pr, pa_resource_types pt
WHERE pr.resource_id = p_resource_id
AND pr.resource_type_id = pt.resource_type_id;
SELECT employee_id, customer_id
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_name = nvl(p_user_name, user_name)
AND user_id = nvl(p_user_id, user_id);
SELECT employee_id,person_party_id
INTO l_emp_id,l_cust_id
FROM fnd_user
WHERE user_name = nvl(p_user_name,user_name)
AND user_id =nvl(p_user_id,user_id);*/
SELECT employee_id, person_party_id
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT employee_id, person_party_id
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_name = p_user_name;
SELECT employee_id, person_party_id
INTO l_emp_id, l_cust_id
FROM fnd_user
WHERE user_name = p_user_name
AND user_id = p_user_id;
SELECT resource_id
INTO l_res_id
FROM pa_resource_txn_attributes
WHERE party_id = l_cust_id;
SELECT resource_type_code
INTO l_resource_type_code
FROM pa_resource_types
WHERE resource_type_id = p_resource_type_id;
SELECT hzp.party_name
INTO l_name
FROM per_all_people_f peo, hz_parties hzp
WHERE peo.person_id = p_resource_source_id
AND sysdate BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND peo.party_id = hzp.party_id;
SELECT hzp.party_name
INTO l_name
FROM hz_parties hzp
WHERE hzp.party_id = p_resource_source_id;
SELECT name
INTO l_name
FROM pa_resources
WHERE resource_id = p_resource_id;
select menu_name, resp.menu_id
into l_menu_name, l_menu_id
from fnd_menus menu,
fnd_responsibility resp
where resp.responsibility_id = fnd_global.resp_id
and resp.menu_id = menu.menu_id
and resp.application_id = fnd_global.resp_appl_id ;
select function_id
into l_function_id
from fnd_form_functions
where function_name='PA_RES_LIST';
/* Updated following code since the menu got changed and
'PA_RES_LIST' is not loger direct under the main menu.
elsif FND_FUNCTION_SECURITY.MENU_ENTRY_EXISTS(
menu_name => l_menu_name,
sub_menu_name => '',
function_name => 'PA_RES_LIST') */
elsif FND_FUNCTION.Is_function_on_menu (
p_menu_id => l_menu_id,
p_function_id => l_function_id )
then
l_pa_logged_resp := 'SU';
SELECT person_id, resource_name
INTO x_person_id, x_person_name
FROM pa_resources_denorm
WHERE resource_name like p_person_name
AND rownum=1;
select /* +index PA_RESOURCES_DENORM PA_RESOURCES_DENORM_N2 */
manager_id into x_manager_id
from pa_resources_denorm --pa_rep_res_mgr_v
where manager_name = l_manager_name
and rownum = 1; -- to stop multiple rows error
select distinct manager_id into x_manager_id
from pa_managers_v
where manager_full_name = l_manager_name;
select manager_id into x_manager_id
from pa_resources_denorm --pa_rep_res_mgr_v
where manager_name = l_manager_name
and manager_id = l_manager_id
and rownum = 1;
select distinct manager_id into x_manager_id
from pa_managers_v
where manager_full_name = l_manager_name
and manager_id = l_manager_id;
SELECT full_name
INTO l_person_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND effective_end_date = (SELECT MAX(effective_end_date)
FROM per_all_people_f
WHERE person_id = p_person_id);
SELECT NVL(pp.projected_termination_date, asg.projected_assignment_end) -- Bug#14594489
INTO l_term_date
FROM per_all_assignments_f asg,
per_periods_of_placement pp
WHERE asg.person_id = p_person_id
AND pp.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'C'
AND asg.period_of_placement_date_start = pp.date_start
AND trunc(SYSDATE) BETWEEN trunc(asg.effective_start_date)
AND trunc(asg.effective_end_date);
SELECT start_date
INTO l_start_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id
;
select effective_start_date into l_manager_start_date from pa_employees
where person_id=x_ManagerId
and active='*';
SELECT 'Y'
INTO l_in_use
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM pa_resource_list_members
WHERE vendor_id = p_supplier_id
UNION ALL
SELECT 'Y'
FROM pa_rbs_elements
WHERE supplier_id = p_supplier_id);
SELECT formula_id
FROM ff_formulas_f
--WHERE business_group_id+0 = 0 -- commented as part of bug 7613549
WHERE nvl(business_group_id,0) = 0 -- added as part of bug 7613549
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
AND formula_name = 'HR_PA_MOVE'
AND formula_type_id
= HR_PERSON_FLEX_LOGIC.GetFormulaTypeID('Oracle Payroll');
SELECT leaving_reason
FROM (SELECT leaving_reason
FROM per_periods_of_service
WHERE person_id = c_person_id
AND actual_termination_date IS NOT NULL
ORDER BY actual_termination_date DESC)
WHERE ROWNUM = 1;
SELECT termination_reason
FROM (SELECT termination_reason
FROM per_periods_of_placement
WHERE person_id = c_person_id
AND actual_termination_date IS NOT NULL
ORDER BY actual_termination_date DESC)
WHERE ROWNUM = 1;
SELECT distinct(resource_person_type)
FROM pa_resources_denorm
WHERE person_id = c_person_id;
/*SELECT distinct(resource_person_type)
INTO l_resource_person_type
FROM pa_resources_denorm
WHERE person_id = p_person_id;*/
SELECT max(resource_effective_end_date)
INTO l_resource_effective_end_date
FROM pa_resources_denorm
WHERE person_id = G_TERM_PERSON_ID; --l_person_id
UPDATE pa_resources_denorm
SET resource_effective_end_date = G_FTE_DATE,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE person_id = G_TERM_PERSON_ID --l_person_id
AND resource_effective_end_date = l_resource_effective_end_date;
PA_HR_UPDATE_PA_ENTITIES.create_fte_sync_wf(p_person_id => G_TERM_PERSON_ID,
p_wait_days => l_wait_days,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE pa_resources
SET future_term_wf_flag = p_future_term_wf_flag,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE
resource_id = l_resource_id;
SELECT pa_resources.future_term_wf_flag
INTO l_future_term_wf_flag
FROM pa_resources
WHERE resource_id = l_resource_id;
SELECT distinct(resource_person_type)
INTO l_resource_person_type
FROM pa_resources_denorm
WHERE person_id = p_person_id;
SELECT TRUNC (NVL( MAX(actual_termination_date), SYSDATE ))
INTO l_end_date
FROM per_periods_of_service
WHERE person_id = p_person_id
AND actual_termination_date IS NOT NULL;
SELECT TRUNC (NVL( MAX(actual_termination_date), SYSDATE ))
INTO l_end_date
FROM per_periods_of_placement
WHERE person_id = p_person_id
AND actual_termination_date IS NOT NULL;
SELECT TRUNC (MAX (resource_effective_end_date))
INTO l_max_res_denorm_end_date
FROM pa_resources_denorm
WHERE person_id = p_person_id;
SELECT TRUNC (NVL (ACT_TERM_DATE, sysdate))
FROM
(SELECT MAX(actual_termination_date)"ACT_TERM_DATE"
FROM per_periods_of_service
WHERE person_id = c_person_id
AND actual_termination_date IS NOT NULL
UNION
SELECT MAX(actual_termination_date)"ACT_TERM_DATE"
FROM per_periods_of_placement
WHERE person_id = c_person_id
AND actual_termination_date IS NOT NULL )
WHERE ACT_TERM_DATE IS NOT NULL ;