VIEW: APPS.PER_SEC_PROFILE_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT f1.user_id ,f1.responsibility_id ,f1.responsibility_application_id ,f1.security_group_id ,p3.start_date ,fnd_date.canonical_to_date(decode(fnd_date.date_to_canonical(p3.end_date), '4712/12/31 00:00:00', NULL,fnd_date.date_to_canonical(p3.end_date))) end_date ,f2.user_name ,f3.responsibility_name ,p1.full_name ,decode(p1.current_employee_flag, 'Y', '*', 'y', '*','') current_employee ,p1.current_employee_flag ,p1.person_id employee_id ,p3.business_group_id ,hr_general.decode_organization(p3.business_group_id) ,p3.object_version_number ,p4.security_profile_name ,f4.application_name ,p4.security_profile_id ,p1.employee_number ,p3.sec_profile_assignment_id FROM (SELECT U.user_id user_id, wur.role_orig_system_id RESPONSIBILITY_ID, (SELECT application_id FROM fnd_application WHERE application_short_name = /* Val between 1st and 2nd separator */ replace( substr(WUR.ROLE_NAME, INSTR(WUR.ROLE_NAME, '|', 1, 1)+1, ( INSTR(WUR.ROLE_NAME, '|', 1, 2) -INSTR(WUR.ROLE_NAME, '|', 1, 1)-1) ) ,'%col', ':') ) RESPONSIBILITY_APPLICATION_ID, (SELECT security_group_id FROM fnd_security_groups WHERE security_group_key = /* Val after 3rd separator */ replace( substr(WUR.ROLE_NAME, INSTR(WUR.ROLE_NAME, '|', 1, 3)+1 ) ,'%col', ':') ) SECURITY_GROUP_ID, fnd_date.canonical_to_date('1000/01/01') START_DATE, fnd_date.canonical_to_date('9999/01/01') END_DATE, to_char( NULL) DESCRIPTION, to_number(NULL) CREATED_BY, to_date( NULL) CREATION_DATE, to_number(NULL) LAST_UPDATED_BY, to_date( NULL) LAST_UPDATE_DATE, to_number(NULL) LAST_UPDATE_LOGIN FROM fnd_user u, wf_local_user_roles wur WHERE wur.user_name = u.user_name AND wur.role_orig_system = 'FND_RESP' AND wur.partition_id = 2 ) F1 ,fnd_user f2 ,fnd_responsibility_vl f3 ,fnd_application_vl f4 ,per_all_people_f p1 ,per_sec_profile_assignments p3 ,per_security_profiles p4 WHERE f1.security_group_id = p3.security_group_id (+) AND f3.application_id = f4.application_id AND f1.user_id = f2.user_id (+) AND f1.user_id = p3.user_id (+) AND (f1.responsibility_id = f3.responsibility_id AND f1.responsibility_application_id = f3.application_id) AND f1.responsibility_id = p3.responsibility_id (+) AND f1.responsibility_application_id = p3.responsibility_application_id (+) AND f2.employee_id = p1.person_id (+) AND p4.security_profile_id (+) = p3.security_profile_id AND TRUNC(SYSDATE) BETWEEN p1.effective_start_date (+) AND p1.effective_end_date (+)

View Text - HTML Formatted

SELECT F1.USER_ID
, F1.RESPONSIBILITY_ID
, F1.RESPONSIBILITY_APPLICATION_ID
, F1.SECURITY_GROUP_ID
, P3.START_DATE
, FND_DATE.CANONICAL_TO_DATE(DECODE(FND_DATE.DATE_TO_CANONICAL(P3.END_DATE)
, '4712/12/31 00:00:00'
, NULL
, FND_DATE.DATE_TO_CANONICAL(P3.END_DATE))) END_DATE
, F2.USER_NAME
, F3.RESPONSIBILITY_NAME
, P1.FULL_NAME
, DECODE(P1.CURRENT_EMPLOYEE_FLAG
, 'Y'
, '*'
, 'Y'
, '*'
, '') CURRENT_EMPLOYEE
, P1.CURRENT_EMPLOYEE_FLAG
, P1.PERSON_ID EMPLOYEE_ID
, P3.BUSINESS_GROUP_ID
, HR_GENERAL.DECODE_ORGANIZATION(P3.BUSINESS_GROUP_ID)
, P3.OBJECT_VERSION_NUMBER
, P4.SECURITY_PROFILE_NAME
, F4.APPLICATION_NAME
, P4.SECURITY_PROFILE_ID
, P1.EMPLOYEE_NUMBER
, P3.SEC_PROFILE_ASSIGNMENT_ID
FROM (SELECT U.USER_ID USER_ID
, WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID
, (SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = /* VAL BETWEEN 1ST
AND 2ND SEPARATOR */ REPLACE( SUBSTR(WUR.ROLE_NAME
, INSTR(WUR.ROLE_NAME
, '|'
, 1
, 1)+1
, ( INSTR(WUR.ROLE_NAME
, '|'
, 1
, 2) -INSTR(WUR.ROLE_NAME
, '|'
, 1
, 1)-1) )
, '%COL'
, ':') ) RESPONSIBILITY_APPLICATION_ID
, (SELECT SECURITY_GROUP_ID
FROM FND_SECURITY_GROUPS
WHERE SECURITY_GROUP_KEY = /* VAL AFTER 3RD SEPARATOR */ REPLACE( SUBSTR(WUR.ROLE_NAME
, INSTR(WUR.ROLE_NAME
, '|'
, 1
, 3)+1 )
, '%COL'
, ':') ) SECURITY_GROUP_ID
, FND_DATE.CANONICAL_TO_DATE('1000/01/01') START_DATE
, FND_DATE.CANONICAL_TO_DATE('9999/01/01') END_DATE
, TO_CHAR( NULL) DESCRIPTION
, TO_NUMBER(NULL) CREATED_BY
, TO_DATE( NULL) CREATION_DATE
, TO_NUMBER(NULL) LAST_UPDATED_BY
, TO_DATE( NULL) LAST_UPDATE_DATE
, TO_NUMBER(NULL) LAST_UPDATE_LOGIN
FROM FND_USER U
, WF_LOCAL_USER_ROLES WUR
WHERE WUR.USER_NAME = U.USER_NAME
AND WUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
AND WUR.PARTITION_ID = 2 ) F1
, FND_USER F2
, FND_RESPONSIBILITY_VL F3
, FND_APPLICATION_VL F4
, PER_ALL_PEOPLE_F P1
, PER_SEC_PROFILE_ASSIGNMENTS P3
, PER_SECURITY_PROFILES P4
WHERE F1.SECURITY_GROUP_ID = P3.SECURITY_GROUP_ID (+)
AND F3.APPLICATION_ID = F4.APPLICATION_ID
AND F1.USER_ID = F2.USER_ID (+)
AND F1.USER_ID = P3.USER_ID (+)
AND (F1.RESPONSIBILITY_ID = F3.RESPONSIBILITY_ID
AND F1.RESPONSIBILITY_APPLICATION_ID = F3.APPLICATION_ID)
AND F1.RESPONSIBILITY_ID = P3.RESPONSIBILITY_ID (+)
AND F1.RESPONSIBILITY_APPLICATION_ID = P3.RESPONSIBILITY_APPLICATION_ID (+)
AND F2.EMPLOYEE_ID = P1.PERSON_ID (+)
AND P4.SECURITY_PROFILE_ID (+) = P3.SECURITY_PROFILE_ID
AND TRUNC(SYSDATE) BETWEEN P1.EFFECTIVE_START_DATE (+)
AND P1.EFFECTIVE_END_DATE (+)