1. Tables and Views for HCM
  2. WLF_PLANNING_PRIORITIZE_V

WLF_PLANNING_PRIORITIZE_V

Details

  • Schema: FUSION

  • Object owner: WLF

  • Object type: VIEW

Columns

Name

LEARNER_ID

LEARNING_ITEM_ID

AREC_STATUS

PLAN_PROFILE_TYPE

PRIORITY

IS_ADHOC

MAX_PRIORITY_FLAG

PLAN_PROFILE_ID

LEARNING_PLAN_ID

PLAN_RECORD_ID

ASSIGNMENT_RECORD_ID

PLAN_REC_STATUS

Query

SQL_Statement

SELECT LEARNER_ID,

LEARNING_ITEM_ID,

AREC_STATUS,

PLAN_PROFILE_TYPE,

PRIORITY,

IS_ADHOC,

MAX_PRIORITY_FLAG,

PLAN_PROFILE_ID,

LEARNING_PLAN_ID,

PLAN_RECORD_ID,

ASSIGNMENT_RECORD_ID,

PLAN_REC_STATUS

FROM (SELECT

AssignmentRecordDEO.LEARNER_ID,

AssignmentRecordDEO.LEARNING_ITEM_ID,

AssignmentRecordDEO.STATUS as AREC_STATUS,

PlanProfileDEO.PLAN_PROFILE_TYPE,

EventAssignmentDEO.PRIORITY,

EventAssignmentDEO.IS_ADHOC,

/*CASE WHEN (ROW_NUMBER() OVER (PARTITION BY AssignmentRecordDEO.LEARNER_ID, AssignmentRecordDEO.LEARNING_ITEM_ID ORDER BY EventAssignmentDEO.PRIORITY ASC)) = 1 THEN 'Y' ELSE 'N' END AS MAX_PRIORITY_FLAG,*/

CASE WHEN (DENSE_RANK() OVER( PARTITION BY nvl(AssignmentRecordDEO.learner_id,dbms_random.value(1,9) ),AssignmentRecordDEO.learning_item_id,AssignmentRecordDEO.event_type, PlanRecordsDEO.LEARNING_PLAN_ID

ORDER BY EventAssignmentDEO.priority ASC ) ) = 1 THEN 'Y' ELSE 'N' END AS MAX_PRIORITY_FLAG,

PlanRecordsDEO.PLAN_PROFILE_ID,

PlanRecordsDEO.LEARNING_PLAN_ID,

PlanRecordsDEO.PLAN_RECORD_ID,

AssignmentRecordDEO.ASSIGNMENT_RECORD_ID,

PlanRecordsDEO.STATUS AS PLAN_REC_STATUS

FROM WLF_ASSIGNMENT_RECORDS_F AssignmentRecordDEO,

WLF_EVENT_ASSIGNMENTS_F EventAssignmentDEO,

WLF_PLAN_PROFILES_F PlanProfileDEO,

WLF_PLAN_RECORDS_F PlanRecordsDEO

WHERE

TRUNC(SYSDATE) BETWEEN AssignmentRecordDEO.EFFECTIVE_START_DATE AND AssignmentRecordDEO.EFFECTIVE_END_DATE

AND PlanRecordsDEO.ASSIGNMENT_RECORD_ID = AssignmentRecordDEO.ASSIGNMENT_RECORD_ID

AND TRUNC(SYSDATE) BETWEEN PlanRecordsDEO.EFFECTIVE_START_DATE AND PlanRecordsDEO.EFFECTIVE_END_DATE

AND PlanRecordsDEO.PLAN_PROFILE_ID = PlanProfileDEO.PLAN_PROFILE_ID

AND TRUNC(SYSDATE) BETWEEN PlanProfileDEO.EFFECTIVE_START_DATE AND PlanProfileDEO.EFFECTIVE_END_DATE

AND PlanProfileDEO.EVENT_ASSIGNMENT_ID = EventAssignmentDEO.EVENT_ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN EventAssignmentDEO.EFFECTIVE_START_DATE AND EventAssignmentDEO.EFFECTIVE_END_DATE

)

WHERE MAX_PRIORITY_FLAG='Y'