[Home] [Help]
1256: X_return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1257: X_error_message_code OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
1258:
1259: v_validated VARCHAR2(1);
1260: v_orgid per_all_assignments_f.organization_id%TYPE;
1261: v_roleid fnd_grants.menu_id%TYPE;
1262: v_objid fnd_grants.object_id%TYPE;
1263: v_resid fnd_grants.grantee_key%TYPE;
1264: v_objname VARCHAR2(25) := 'ORGANIZATION';
1269: missing_role EXCEPTION;
1270: j NUMBER;
1271:
1272: cursor analystid(objid fnd_grants.object_id%TYPE,
1273: orgid per_all_assignments_f.organization_id%TYPE,
1274: roleid fnd_grants.menu_id%TYPE ) is
1275: SELECT distinct per.person_id
1276: FROM fnd_grants fg, wf_roles wfr, per_all_people_f per
1277: WHERE fg.object_id = objid
1317: -- it may raise no data found exception if person is not on
1318: -- current assignment
1319: SELECT organization_id
1320: INTO v_orgid
1321: FROM per_all_assignments_f -- Bug 4684198 - use base table not view
1322: WHERE person_id = P_PersonId
1323: AND trunc(sysdate) BETWEEN effective_start_date
1324: AND effective_end_date
1325: -- Bug 4684198 - remove nvl on effective_end_date - col is not null
1541: --Modified code for future dated person
1542: --Manager check is still as of sysdate
1543: SELECT assn.supervisor_id,pep.full_name
1544: INTO x_ManagerId,x_ManagerName
1545: FROM per_all_assignments_f assn,per_all_people_f pep
1546: WHERE assn.person_id = P_personId
1547: AND pep.person_id = assn.supervisor_id
1548: AND p_start_date BETWEEN assn.effective_start_date
1549: AND assn.effective_end_date
1567: /*bug 3737529 - Code addition starts*/
1568: BEGIN
1569: SELECT assn.supervisor_id,pep.full_name
1570: INTO x_ManagerId,x_ManagerName
1571: FROM per_all_assignments_f assn,
1572: per_all_people_f pep
1573: WHERE assn.person_id = P_personId
1574: AND pep.person_id = assn.supervisor_id
1575: AND p_start_date BETWEEN assn.effective_start_date
1618: -- where assignment type is primary type.
1619: -- may raise exception if the person is not on current assnment
1620: SELECT organization_id
1621: INTO x_orgid
1622: FROM per_all_assignments_f
1623: WHERE person_id = P_personId
1624: AND p_start_date BETWEEN effective_start_date
1625: AND effective_end_date
1626: AND primary_flag = 'Y'
1786: ) is
1787:
1788: v_personId NUMBER;
1789: v_numrows NUMBER;
1790: v_orgid per_all_assignments_f.organization_id%TYPE;
1791: v_objid fnd_grants.object_id%TYPE;
1792: --v_resid fnd_grants.grantee_key%TYPE;
1793: v_objname VARCHAR2(25) := 'ORGANIZATION';
1794: v_primrolename VARCHAR2(30) := 'PA_PRM_RES_PRMRY_CONTACT';
3529:
3530: cursor get_active_assignment
3531: is
3532: select asgn.assignment_id
3533: from per_all_assignments_f asgn,
3534: per_assignment_status_types status,
3535: (select person_id, actual_termination_date
3536: from per_periods_of_service
3537: union all
3549:
3550: cursor get_primary_assignment
3551: is
3552: select asgn.assignment_id
3553: from per_all_assignments_f asgn,
3554: (select person_id, actual_termination_date
3555: from per_periods_of_service
3556: union all
3557: select person_id, actual_termination_date
3567:
3568: cursor get_job_on_assignment
3569: is
3570: select asgn.job_id
3571: from per_all_assignments_f asgn,
3572: (select person_id, actual_termination_date
3573: from per_periods_of_service
3574: union all
3575: select person_id, actual_termination_date
4276: IF p_person_id IS NOT NULL THEN
4277:
4278: SELECT pp.projected_termination_date
4279: INTO l_term_date
4280: FROM per_all_assignments_f asg,
4281: per_periods_of_placement pp
4282: WHERE asg.person_id = p_person_id
4283: AND pp.person_id = p_person_id
4284: AND asg.primary_flag = 'Y'