CN_SRP_ASSIGNMENTS_V

Details

• Schema: FUSION

• Object owner: CN

• Object type: VIEW

Columns

Name

PARTICIPANT_NAME PARTICIPANT_FIRST_NAME PARTICIPANT_LAST_NAME HR_PERSON_NUMBER PARTICIPANT_NAME1 PARTICIPANT_ID ANALYST_ID ORG_ID ROLE_NAME COMP_PLAN_ID SRP_COMP_PLAN_ID ROLE_ID PARTICIPANT_ROLE_ID START_DATE END_DATE COMPENSATION_END_DATE HOLD_PAYMENT_FLAG START_DATE_ACTIVE END_DATE_ACTIVE START_DATE1 END_DATE1 PARTICIPANT_ID1 COMP_PLAN_ID1 COMP_PLAN_NAME ROLE_ID1 PARTICIPANT_TYPE OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER1 EMAIL_ADDRESS ORIG_SYSTEM ORIG_SYSTEM_REFERENCE PARTY_ID HR_PRIMARY_WORKER_NUMBER OBJECT_VERSION_NUMBER2 PARTICIPANT_PLAN_NUMBER

Query

SQL_Statement

select a.PARTICIPANT_NAME, a.PARTICIPANT_FIRST_NAME, a.PARTICIPANT_LAST_NAME, a.HR_PERSON_NUMBER, a.PARTICIPANT_NAME1, a.PARTICIPANT_ID, a.ANALYST_ID, a.ORG_ID, b.ROLE_NAME, b.COMP_PLAN_ID, b.SRP_COMP_PLAN_ID, b.ROLE_ID, b.PARTICIPANT_ROLE_ID, a.START_DATE, a.END_DATE, a.COMPENSATION_END_DATE, a.HOLD_PAYMENT_FLAG, b.START_DATE_ACTIVE, b.END_DATE_ACTIVE, b.START_DATE1, b.END_DATE1, a.PARTICIPANT_ID1, b.COMP_PLAN_ID1, b.COMP_PLAN_NAME, b.ROLE_ID AS ROLE_ID1, a.PARTICIPANT_TYPE, a.OBJECT_VERSION_NUMBER, b.OBJECT_VERSION_NUMBER1, a.EMAIL_ADDRESS, a.ORIG_SYSTEM, a.ORIG_SYSTEM_REFERENCE, a.PARTY_ID, a.HR_PRIMARY_WORKER_NUMBER, a.OBJECT_VERSION_NUMBER2, b.PARTICIPANT_PLAN_NUMBER FROM ( select ParticipantEO.PARTICIPANT_NAME, ParticipantEO.PARTICIPANT_FIRST_NAME, ParticipantEO.PARTICIPANT_LAST_NAME, ParticipantEO.HR_PERSON_NUMBER, (SELECT hzp2.party_name FROM fusion.hz_parties hzp2, fusion.cn_srp_participants_all p2 WHERE p2.party_id = hzp2.party_id AND p2.participant_type = 'ANALYST' AND p2.participant_id = ParticipantEO.analyst_id ) AS PARTICIPANT_NAME1, ParticipantEO.PARTICIPANT_ID, ParticipantEO.ANALYST_ID, ParticipantEO.ORG_ID, ParticipantEO.START_DATE, ParticipantEO.END_DATE, ParticipantEO.COMPENSATION_END_DATE, ParticipantEO.HOLD_PAYMENT_FLAG, (SELECT p2.PARTICIPANT_ID FROM fusion.cn_srp_participants_all p2 WHERE p2.participant_type = 'ANALYST' AND p2.participant_id = ParticipantEO.analyst_id ) AS PARTICIPANT_ID1, ParticipantEO.PARTICIPANT_TYPE, ParticipantEO.OBJECT_VERSION_NUMBER, ParticipantEO.EMAIL_ADDRESS, ParticipantEO.ORIG_SYSTEM, ParticipantEO.ORIG_SYSTEM_REFERENCE, ParticipantEO.PARTY_ID, ParticipantEO.HR_PRIMARY_WORKER_NUMBER, (SELECT p2.OBJECT_VERSION_NUMBER FROM fusion.cn_srp_participants_all p2 WHERE p2.participant_type = 'ANALYST' AND p2.participant_id = ParticipantEO.analyst_id ) AS OBJECT_VERSION_NUMBER2 from fusion.CN_SRP_PARTICIPANT_HDR_RO_V ParticipantEO ) a, (select b.*,CompPlansPEO.COMP_PLAN_ID AS COMP_PLAN_ID1, CompPlansPEO.COMP_PLAN_NAME FROM ( SELECT ParticipantRoleEO.PARTICIPANT_ID, CompPlanEO.COMP_PLAN_ID, CompPlanEO.SRP_COMP_PLAN_ID, CompRoleEO.ROLE_NAME, ParticipantRoleEO.ROLE_ID, ParticipantRoleEO.PARTICIPANT_ROLE_ID, ParticipantRoleEO.START_DATE_ACTIVE, ParticipantRoleEO.END_DATE_ACTIVE, ParticipantRoleEO.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1, CompPlanEO.START_DATE AS START_DATE1, CompPlanEO.END_DATE AS END_DATE1, CompPlanEO.PARTICIPANT_PLAN_NUMBER FROM fusion.CN_ROLES_VL CompRoleEO, fusion.CN_PARTICIPANT_ROLES ParticipantRoleEO, (select * from fusion.CN_SRP_COMP_PLANS_ALL WHERE SRP_RULE_ID IS NOT NULL) CompPlanEO WHERE ParticipantRoleEO.PARTICIPANT_ROLE_ID = CompPlanEO.SRP_RULE_ID(+) AND ParticipantRoleEO.ROLE_ID = CompRoleEO.ROLE_ID UNION SELECT CompPlanEO.PARTICIPANT_ID, CompPlanEO.COMP_PLAN_ID, CompPlanEO.SRP_COMP_PLAN_ID, NULL ROLE_NAME, NULL ROLE_ID, NULL PARTICIPANT_ROLE_ID, NULL START_DATE_ACTIVE, NULL END_DATE_ACTIVE, NULL OBJECT_VERSION_NUMBER1, CompPlanEO.START_DATE AS START_DATE1, CompPlanEO.END_DATE AS END_DATE1, CompPlanEO.PARTICIPANT_PLAN_NUMBER FROM fusion.CN_SRP_COMP_PLANS_ALL CompPlanEO WHERE CompPlanEO.SRP_RULE_ID IS NULL ) b, fusion.CN_COMP_PLANS_ALL_VL CompPlansPEO WHERE b.COMP_PLAN_ID = CompPlansPEO.COMP_PLAN_ID(+) ) b WHERE a.PARTICIPANT_ID = b.PARTICIPANT_ID(+)