VIEW: APPS.PQH_POS_UR

Source

View Text - Preformatted

SELECT USR.USER_NAME user_name, 'PER' user_orig_system, ASS.PERSON_ID user_orig_system_id, 'POS'||':'||ASS.POSITION_ID role_name, 'POS' role_orig_system, ASS.POSITION_ID role_orig_system_id, MIN(ass.effective_start_date) start_date, MAX(ass.effective_end_date) expiration_date, NULL security_group_id, 4 PARTITION_ID FROM PER_ALL_ASSIGNMENTS_F ASS, FND_USER USR WHERE ASS.PERSON_ID = USR.EMPLOYEE_ID and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate) and ASS.POSITION_ID IS NOT NULL AND trunc(sysdate) between ASS.EFFECTIVE_START_DATE and ASS.EFFECTIVE_END_DATE and ASS.ASSIGNMENT_TYPE in ('E', 'C') GROUP BY USR.USER_NAME, 'POS'||':'||ASS.POSITION_ID , 'PER',ASS.PERSON_ID,'POS',ASS.POSITION_ID,4

View Text - HTML Formatted

SELECT USR.USER_NAME USER_NAME
, 'PER' USER_ORIG_SYSTEM
, ASS.PERSON_ID USER_ORIG_SYSTEM_ID
, 'POS'||':'||ASS.POSITION_ID ROLE_NAME
, 'POS' ROLE_ORIG_SYSTEM
, ASS.POSITION_ID ROLE_ORIG_SYSTEM_ID
, MIN(ASS.EFFECTIVE_START_DATE) START_DATE
, MAX(ASS.EFFECTIVE_END_DATE) EXPIRATION_DATE
, NULL SECURITY_GROUP_ID
, 4 PARTITION_ID
FROM PER_ALL_ASSIGNMENTS_F ASS
, FND_USER USR
WHERE ASS.PERSON_ID = USR.EMPLOYEE_ID
AND TRUNC(SYSDATE) BETWEEN USR.START_DATE
AND NVL(USR.END_DATE
, SYSDATE)
AND ASS.POSITION_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.ASSIGNMENT_TYPE IN ('E'
, 'C') GROUP BY USR.USER_NAME
, 'POS'||':'||ASS.POSITION_ID
, 'PER'
, ASS.PERSON_ID
, 'POS'
, ASS.POSITION_ID
, 4