1. Tables and Views for HCM
  2. CMP_ASG_SAL_REPORTING_HIST_V

CMP_ASG_SAL_REPORTING_HIST_V

Details

  • Schema: FUSION

  • Object owner: CMP

  • Object type: VIEW

Columns

Name

ASSIGNMENT_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

EFFECTIVE_SEQUENCE

EFFECTIVE_LATEST_CHANGE

PERSON_ID

ASSIGNMENT_TYPE

ASG_ACTION_CODE

ASG_BUSINESS_GROUP_ID

ASG_ACTION_REASON_CODE

ASG_PROPOSED_WORKER_TYPE

SAL_ACTION_ID

SAL_ACTION_REASON_ID

SALARY_CLASSIFICATION_TYPE

SAL_SALARY_ID

SAL_DATE_FROM

SAL_DATE_TO

SAL_HISTORY_DATE

IS_REPEATED_ASSIGNMENT

IS_REPEATED_SALARY

IS_LATEST

ANCHOR_START_DATE

ANCHOR_END_DATE

Query

SQL_Statement

SELECT

ASSIGNMENT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, EFFECTIVE_SEQUENCE, EFFECTIVE_LATEST_CHANGE,PERSON_ID,

ASSIGNMENT_TYPE, ACTION_CODE ASG_ACTION_CODE,

ASG_BUSINESS_GROUP_ID, ASG_ACTION_REASON_CODE,

ASG_PROPOSED_WORKER_TYPE,

SAL_ACTION_ID, SAL_ACTION_REASON_ID,

( CASE

WHEN NEVER_HAD_SALARY_IND = 'Y' THEN 'NEVER_HAD_SALARY'

WHEN PAST_SALARY_EXISTS_IND = 'Y' THEN 'PAST_SALARY_EXISTS'

WHEN (

( NEXT_ROW_SALARY_GAP_IND = 'Y' OR PREV_ROW_SALARY_GAP_IND = 'Y' OR RANK_SALARY_GAP = 1 )

AND

( FIRST_SAL_ROW_IND = 'N' AND RANK_WAS_ROW <> 1 )

)

THEN 'GAP_IN_SALARY'

WHEN ASOFDATE_SALARY_EXISTS_IND = 'Y' THEN 'AS_OF_DATE_SALARY'

WHEN ESD_SATISFIED_IND = 'Y' THEN 'AS_OF_DATE_SALARY'

WHEN FUTURE_SALARY_EXISTS_IND = 'Y' THEN 'FUTURE_SALARY_EXISTS'

END ) SALARY_CLASSIFICATION_TYPE,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' )

AND

( ASOFDATE_SALARY_EXISTS_IND = 'N' AND ESD_SATISFIED_IND = 'N' )

THEN NULL

ELSE SALARY_ID

END ) SAL_SALARY_ID,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' )

AND

( ASOFDATE_SALARY_EXISTS_IND = 'N' AND ESD_SATISFIED_IND = 'N' )

THEN NULL

ELSE DATE_FROM

END ) SAL_DATE_FROM,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' )

AND

( ASOFDATE_SALARY_EXISTS_IND = 'N' AND ESD_SATISFIED_IND = 'N' )

THEN NULL

ELSE DATE_TO

END ) SAL_DATE_TO,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' )

AND

( ASOFDATE_SALARY_EXISTS_IND = 'N' AND ESD_SATISFIED_IND = 'N' )

THEN NULL

ELSE HISTORY_DATE

END ) SAL_HISTORY_DATE,

( CASE

WHEN DENSE_RANK() OVER(PARTITION BY ASSIGNMENT_ID, EFFECTIVE_START_DATE ORDER BY DATE_FROM ASC) >= 2

THEN 'Y'

ELSE 'N'

END ) IS_REPEATED_ASSIGNMENT,

( CASE

WHEN ( HISTORY_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE )

THEN 'N'

ELSE 'Y'

END ) IS_REPEATED_SALARY,

( CASE

WHEN RANK_WAS_ROW = 1

THEN 'Y'

ELSE 'N'

END ) IS_LATEST,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' )

THEN EFFECTIVE_START_DATE

WHEN ( DATE_FROM < EFFECTIVE_START_DATE AND DATE_FROM < HISTORY_DATE )

THEN DATE_FROM

WHEN ( HISTORY_DATE < EFFECTIVE_START_DATE AND HISTORY_DATE < DATE_FROM )

THEN HISTORY_DATE

ELSE EFFECTIVE_START_DATE

END ) ANCHOR_START_DATE,

( CASE

WHEN ( NEVER_HAD_SALARY_IND = 'Y' OR PAST_SALARY_EXISTS_IND = 'Y' OR FUTURE_SALARY_EXISTS_IND = 'Y' )

THEN EFFECTIVE_END_DATE

WHEN ( DATE_TO > EFFECTIVE_END_DATE AND DATE_TO > HISTORY_DATE )

THEN DATE_TO

WHEN ( HISTORY_DATE > EFFECTIVE_END_DATE AND HISTORY_DATE > DATE_TO )

THEN HISTORY_DATE

ELSE EFFECTIVE_END_DATE

END ) ANCHOR_END_DATE

FROM (

SELECT

A.ASSIGNMENT_ID, A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE, A.EFFECTIVE_SEQUENCE, A.EFFECTIVE_LATEST_CHANGE , A.PERSON_ID,

A.ASSIGNMENT_TYPE, A.ACTION_CODE, S.SALARY_ID, S.DATE_FROM,S.DATE_TO,

GREATEST(DATE_FROM,LEAST(TRUNC(SYSDATE),DATE_TO)) HISTORY_DATE,

A.BUSINESS_GROUP_ID ASG_BUSINESS_GROUP_ID, A.REASON_CODE ASG_ACTION_REASON_CODE,

S.ACTION_ID SAL_ACTION_ID, S.ACTION_REASON_ID SAL_ACTION_REASON_ID,

A.PROPOSED_WORKER_TYPE ASG_PROPOSED_WORKER_TYPE,

( CASE WHEN A.EFFECTIVE_START_DATE > S.DATE_TO THEN 'Y' ELSE 'N' END ) PAST_SALARY_EXISTS_IND,

( CASE WHEN A.EFFECTIVE_END_DATE < S.DATE_TO THEN 'Y' ELSE 'N' END ) FUTURE_SALARY_EXISTS_IND,

( CASE WHEN S.SALARY_ID IS NULL THEN 'Y' ELSE 'N' END ) NEVER_HAD_SALARY_IND,

( CASE WHEN S.SALARY_ID IS NULL OR A.EFFECTIVE_START_DATE > S.DATE_TO OR A.EFFECTIVE_END_DATE < S.DATE_TO THEN 'N' ELSE 'Y' END ) ASOFDATE_SALARY_EXISTS_IND,

( CASE WHEN A.EFFECTIVE_START_DATE BETWEEN S.DATE_FROM AND S.DATE_TO THEN 'Y' ELSE 'N' END ) ESD_SATISFIED_IND,

DENSE_RANK() OVER(PARTITION BY A.ASSIGNMENT_ID ORDER BY S.DATE_FROM ASC)

RANK_FIRST_NOTNULL_SALARY,

DENSE_RANK() OVER(PARTITION BY A.ASSIGNMENT_ID ORDER BY S.DATE_FROM DESC)

RANK_LAST_NOTNULL_SALARY,

DENSE_RANK() OVER(PARTITION BY A.ASSIGNMENT_ID ORDER BY A.EFFECTIVE_START_DATE DESC, A.EFFECTIVE_SEQUENCE DESC, S.DATE_FROM DESC)

RANK_WAS_ROW,

DENSE_RANK() OVER(PARTITION BY A.ASSIGNMENT_ID,A.EFFECTIVE_START_DATE, A.EFFECTIVE_SEQUENCE, A.ACTION_CODE ORDER BY S.DATE_FROM DESC) RANK_SALARY_GAP,

( CASE WHEN NOT EXISTS (

SELECT NULL

FROM CMP_SALARY R

WHERE R.ASSIGNMENT_ID = S.ASSIGNMENT_ID

AND R.DATE_FROM = S.DATE_TO+1 )

THEN 'Y'

ELSE 'N'

END

)

NEXT_ROW_SALARY_GAP_IND,

( CASE WHEN NOT EXISTS (

SELECT NULL

FROM CMP_SALARY R

WHERE R.ASSIGNMENT_ID = S.ASSIGNMENT_ID

AND R.DATE_TO = S.DATE_FROM-1 )

THEN 'Y'

ELSE 'N'

END

)

PREV_ROW_SALARY_GAP_IND,

( CASE WHEN S.DATE_FROM = MIN(S.DATE_FROM) OVER(PARTITION BY S.ASSIGNMENT_ID)

THEN 'Y'

ELSE 'N'

END

) FIRST_SAL_ROW_IND

FROM

PER_ALL_ASSIGNMENTS_M A,

CMP_SALARY S

WHERE

A.ASSIGNMENT_ID = S.ASSIGNMENT_ID (+)

AND A.ASSIGNMENT_TYPE IN ('E', 'C', 'N','P' )

) INNER_VIEW

WHERE

( ( NEVER_HAD_SALARY_IND = 'Y' )

OR ( ASOFDATE_SALARY_EXISTS_IND = 'Y' )

OR ( FUTURE_SALARY_EXISTS_IND = 'Y' AND RANK_FIRST_NOTNULL_SALARY = 1 )

OR ( PAST_SALARY_EXISTS_IND = 'Y' AND RANK_LAST_NOTNULL_SALARY = 1 )

OR ( ESD_SATISFIED_IND = 'Y' )

OR ( NEXT_ROW_SALARY_GAP_IND = 'Y' AND PREV_ROW_SALARY_GAP_IND = 'Y' AND RANK_SALARY_GAP = 1 )

)