[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(DISTINCT E.PERSON_ID),
MAX(E.FULL_NAME)
INTO
NO_OF_MANAGERS,
MANAGER_NAME
FROM
PER_ALL_PEOPLE_F E,
PER_ALL_ASSIGNMENTS_F A
WHERE (E.CURRENT_EMPLOYEE_FLAG = 'Y' OR
E.CURRENT_NPW_FLAG = 'Y')
AND A.PERSON_ID = E.PERSON_ID
AND A.ORGANIZATION_ID = P_ORGANIZATION_ID
AND ((A.ASSIGNMENT_TYPE = 'E' AND
A.MANAGER_FLAG = 'Y') OR
(A.ASSIGNMENT_TYPE = 'C' AND
A.MANAGER_FLAG = 'Y'))
AND P_SESSION_DATE BETWEEN
E.EFFECTIVE_START_DATE AND E.EFFECTIVE_END_DATE
AND P_SESSION_DATE BETWEEN
A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE;
SELECT
COUNT(DISTINCT(TO_CHAR(A.ORGANIZATION_ID)||NVL(MANAGER_FLAG,'N'))),
MAX(OTL.NAME),
MAX(L.MEANING),
MAX(NVL(A.MANAGER_FLAG,'N'))
INTO NO_OF_ORGS,
ORGANIZATION_NAME,
MANAGER_FLAG_DESC,
MANAGER_FLAG
FROM PER_ALL_ASSIGNMENTS_F A,
HR_ALL_ORGANIZATION_UNITS O,
HR_ALL_ORGANIZATION_UNITS_TL OTL,
FND_LOOKUPS L
WHERE A.PERSON_ID = P_PERSON_ID
AND (A.ASSIGNMENT_TYPE = 'E' OR A.ASSIGNMENT_TYPE = 'C')
--Added for Bug 6010637
AND (SELECT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE A.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND A.ASSIGNMENT_TYPE IN ('E','C')) IN ('ACTIVE_ASSIGN','ACTIVE_CWK')
--
AND P_SESSION_DATE BETWEEN
A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND O.ORGANIZATION_ID = A.ORGANIZATION_ID
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = USERENV('LANG')
AND ((O.ORGANIZATION_ID = P_ORGANIZATION_ID AND
P_ORGANIZATION_STRUCTURE_ID IS NULL)
OR (P_ORGANIZATION_STRUCTURE_ID IS NOT NULL AND
EXISTS
(SELECT 1
FROM PER_ORG_STRUCTURE_ELEMENTS E
WHERE O.ORGANIZATION_ID
IN (E.ORGANIZATION_ID_CHILD,E.ORGANIZATION_ID_PARENT)
AND E.ORG_STRUCTURE_VERSION_ID = P_VERSION_ID)))
AND L.LOOKUP_CODE = NVL(A.MANAGER_FLAG,'N')
AND L.LOOKUP_TYPE = 'YES_NO';