1. Tables and Views for HCM
  2. CMP_CWB_PERSON_INFO_V

CMP_CWB_PERSON_INFO_V

Details

  • Schema: FUSION

  • Object owner: CMP

  • Object type: VIEW

Columns

Name

PERSON_EVENT_ID

PLAN_ID

PERIOD_ID

PERSON_ID

ASSIGNMENT_ID

ASSIGNMENT_ASSIGNMENT_ID

EMAIL_ADDRESS

WORKER_NUMBER

PERSON_NUMBER

WS_MGR_PERSON_ID

WS_MGR_ASSIGNMENT_ID

WS_MGR_PERSON_EVENT_ID

SUPERVISOR_ID

BUSINESS_UNIT_ID

LEGAL_ENTITY_ID

ORGANIZATION_ID

LOCATION_ID

JOB_ID

POSITION_ID

GRADE_ID

DUE_DATE

ACCESS_CODE

EMP_STATUS_CODE

MGR_STATUS_CODE

APPROVAL_CODE

APPROVAL_DATE

SUBMIT_DATE

ASSIGNMENT_STATUS_TYPE_ID

PLACEHOLDER_FLAG

PLACEHOLDER_FLAG_INT

DATE_OF_BIRTH

SEX

ETHNICITY

RELIGION

DISABILITY_STATUS

WORK_TELEPHONE

NATIONALITY

LEGISLATION_CODE

START_DATE

ORIGINAL_START_DATE

ADJUSTED_SVC_DATE

EMPLOYMENT_CATEGORY

YEARS_EMPLOYED

YEARS_IN_JOB

YEARS_IN_POSITION

YEARS_IN_GRADE

JOB_CHANGE_DATE

POSITION_CHANGE_DATE

GRADE_CHANGE_DATE

JOB_ATTRIBUTE1

JOB_ATTRIBUTE2

JOB_ATTRIBUTE3

JOB_ATTRIBUTE4

JOB_ATTRIBUTE5

JOB_ATTRIBUTE6

GRADE_ATTRIBUTE1

GRADE_ATTRIBUTE2

GRADE_ATTRIBUTE3

NORMAL_HOURS

WORKING_HOURS

FTE_FACTOR

BASE_SALARY

BASE_SALARY_CURRENCY

BASE_SALARY_FREQUENCY

BASE_SALARY_CHANGE_DATE

BASE_SALARY_CHANGE_VAL

SALARY_BASIS_ID

PAYROLL_ID

FREQUENCY

GRADE_ANNUALIZATION_FACTOR

PAY_ANNUALIZATION_FACTOR

GRADE_RATE_ID

GRADE_MIN_VAL

GRADE_MID_POINT

GRADE_MAX_VAL

GRADE_QUARTILE

GRADE_QUINTILE

GRADE_DECILE

GRADE_COMPARATIO

GRADE_PCT_IN_RANGE

PERFORMANCE_RATING

PERFORMANCE_RATING_DATE

PERFORMANCE_RATING_TYPE

MGR_OVERRIDE_DATE

MGR_OVERRIDE_PERSON_ID

CUSTOM_SEGMENT1

CUSTOM_SEGMENT2

CUSTOM_SEGMENT3

CUSTOM_SEGMENT4

CUSTOM_SEGMENT5

CUSTOM_SEGMENT6

CUSTOM_SEGMENT7

CUSTOM_SEGMENT8

CUSTOM_SEGMENT9

CUSTOM_SEGMENT10

CUSTOM_SEGMENT11

CUSTOM_SEGMENT12

CUSTOM_SEGMENT13

CUSTOM_SEGMENT14

CUSTOM_SEGMENT15

CUSTOM_SEGMENT16

CUSTOM_SEGMENT17

CUSTOM_SEGMENT18

CUSTOM_SEGMENT19

CUSTOM_SEGMENT20

CUSTOM_SEGMENT21

CUSTOM_SEGMENT22

CUSTOM_SEGMENT23

CUSTOM_SEGMENT24

CUSTOM_SEGMENT25

CUSTOM_SEGMENT26

CUSTOM_SEGMENT27

CUSTOM_SEGMENT28

CUSTOM_SEGMENT29

CUSTOM_SEGMENT30

CUSTOM_SEGMENT31

CUSTOM_SEGMENT32

CUSTOM_SEGMENT33

CUSTOM_SEGMENT34

CUSTOM_SEGMENT35

CUSTOM_SEGMENT36

CUSTOM_SEGMENT37

CUSTOM_SEGMENT38

CUSTOM_SEGMENT39

CUSTOM_SEGMENT40

CUSTOM_SEGMENT41

CUSTOM_SEGMENT42

CUSTOM_SEGMENT43

CUSTOM_SEGMENT44

CUSTOM_SEGMENT45

CUSTOM_SEGMENT46

CUSTOM_SEGMENT47

CUSTOM_SEGMENT48

CUSTOM_SEGMENT49

CUSTOM_SEGMENT50

WORKER_POTENTIAL

RISK_OF_LOSS

VETERAN_STATUS

NON_DISCRIMINATION_CODE

NEW_BASE_SALARY

NEW_GRADE_QUARTILE

NEW_GRADE_QUINTILE

NEW_GRADE_DECILE

NEW_GRADE_COMPARATIO

NEW_GRADE_PCT_IN_RANGE

ACTION_REASON_ID

DO_NOT_POST_FLAG

ASSIGNMENT_POSTING_DATE

ASSIGNMENT_POSTED_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

BUSINESS_GROUP_ID

RVW_MGR_OVERRIDE_PERSON_ID

RVW_MGR_OVERRIDE_DATE

RVW_MGR_PERSON_ID

SEC_MGR_PERSON_ID

EFFECTIVE_DATE

PLAN_PERIOD_ID

PERIOD_COMP_TYPE

ASSIGNMENT_CHANGE_DATE

PERFORMANCE_DATE

PRIOR_SALARY

PRIOR_SALARY_CHG_PCT

ADJUSTMENT_FACTOR

ADJUSTED_SALARY_CURRENT

ADJUSTED_SALARY_NEW

ADJUSTED_CHANGE_AMOUNT

FTE_ANNUALIZATION_FACTOR

FTE_ANNUAL_SALARY_CURRENT

FTE_ANNUAL_SALARY_NEW

FTE_ANNUAL_CHANGE_AMT

XCHG_RATE

USER_PREFERRED_EXCHANGE_RATE

ASSIGNMENT_NAME

Query

SQL_Statement

Select

PersonInformationPEO.PERSON_EVENT_ID,

PersonInformationPEO.PLAN_ID,

PersonInformationPEO.PERIOD_ID,

PersonInformationPEO.PERSON_ID ,

PersonInformationPEO.ASSIGNMENT_ID,

PersonInformationPEO.ASSIGNMENT_ASSIGNMENT_ID,

PersonInformationPEO.EMAIL_ADDRESS,

PersonInformationPEO.WORKER_NUMBER AS WORKER_NUMBER,

PersonInformationPEO.PERSON_NUMBER AS PERSON_NUMBER,

PersonInformationPEO.WS_MGR_PERSON_ID,

PersonInformationPEO.WS_MGR_ASSIGNMENT_ID,

PersonInformationPEO.WS_MGR_PERSON_EVENT_ID,

PersonInformationPEO.SUPERVISOR_ID,

PersonInformationPEO.BUSINESS_UNIT_ID,

PersonInformationPEO.LEGAL_ENTITY_ID,

PersonInformationPEO.ORGANIZATION_ID,

PersonInformationPEO.LOCATION_ID,

PersonInformationPEO.JOB_ID,

PersonInformationPEO.POSITION_ID,

PersonInformationPEO.GRADE_ID,

PersonInformationPEO.DUE_DATE,

PersonInformationPEO.ACCESS_CODE,

PersonInformationPEO.EMP_STATUS_CODE,

PersonInformationPEO.MGR_STATUS_CODE,

PersonInformationPEO.APPROVAL_CODE,

PersonInformationPEO.APPROVAL_DATE,

PersonInformationPEO.SUBMIT_DATE,

PersonInformationPEO.ASSIGNMENT_STATUS_TYPE_ID,

decode(NVL(PersonRatesPEO.ELIG_FLAG,'L'),'L','Y','N') PLACEHOLDER_FLAG,

PersonInformationPEO.PLACEHOLDER_FLAG PLACEHOLDER_FLAG_INT,

PersonInformationPEO.DATE_OF_BIRTH,

PersonInformationPEO.SEX,

PersonInformationPEO.ETHNICITY,

PersonInformationPEO.RELIGION,

PersonInformationPEO.DISABILITY_STATUS,

PersonInformationPEO.WORK_TELEPHONE,

PersonInformationPEO.NATIONALITY,

PersonInformationPEO.LEGISLATION_CODE,

PersonInformationPEO.START_DATE,

PersonInformationPEO.ORIGINAL_START_DATE,

PersonInformationPEO.ADJUSTED_SVC_DATE,

PersonInformationPEO.EMPLOYMENT_CATEGORY,

PersonInformationPEO.YEARS_EMPLOYED,

PersonInformationPEO.YEARS_IN_JOB,

PersonInformationPEO.YEARS_IN_POSITION,

PersonInformationPEO.YEARS_IN_GRADE,

PersonInformationPEO.JOB_CHANGE_DATE,

PersonInformationPEO.POSITION_CHANGE_DATE,

PersonInformationPEO.GRADE_CHANGE_DATE,

PersonInformationPEO.JOB_ATTRIBUTE1,

PersonInformationPEO.JOB_ATTRIBUTE2,

PersonInformationPEO.JOB_ATTRIBUTE3,

PersonInformationPEO.JOB_ATTRIBUTE4,

PersonInformationPEO.JOB_ATTRIBUTE5,

PersonInformationPEO.JOB_ATTRIBUTE6,

PersonInformationPEO.GRADE_ATTRIBUTE1,

PersonInformationPEO.GRADE_ATTRIBUTE2,

PersonInformationPEO.GRADE_ATTRIBUTE3,

PersonInformationPEO.NORMAL_HOURS,

PersonInformationPEO.WORKING_HOURS,

PersonInformationPEO.FTE_FACTOR,

PersonInformationPEO.BASE_SALARY,

PersonInformationPEO.BASE_SALARY_CURRENCY,

PersonInformationPEO.BASE_SALARY_FREQUENCY,

PersonInformationPEO.BASE_SALARY_CHANGE_DATE,

PersonInformationPEO.BASE_SALARY_CHANGE_VAL,

PersonInformationPEO.SALARY_BASIS_ID,

PersonInformationPEO.PAYROLL_ID,

PersonInformationPEO.FREQUENCY,

PersonInformationPEO.GRADE_ANNUALIZATION_FACTOR,

PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,

PersonInformationPEO.GRADE_RATE_ID,

PersonInformationPEO.GRADE_MIN_VAL,

PersonInformationPEO.GRADE_MID_POINT,

PersonInformationPEO.GRADE_MAX_VAL,

PersonInformationPEO.GRADE_QUARTILE,

PersonInformationPEO.GRADE_QUINTILE,

PersonInformationPEO.GRADE_DECILE,

PersonInformationPEO.GRADE_COMPARATIO,

PersonInformationPEO.GRADE_PCT_IN_RANGE,

PersonInformationPEO.PERFORMANCE_RATING,

PersonInformationPEO.PERFORMANCE_RATING_DATE,

PersonInformationPEO.PERFORMANCE_RATING_TYPE,

PersonInformationPEO.MGR_OVERRIDE_DATE,

PersonInformationPEO.MGR_OVERRIDE_PERSON_ID,

PersonInformationPEO.CUSTOM_SEGMENT1,

PersonInformationPEO.CUSTOM_SEGMENT2,

PersonInformationPEO.CUSTOM_SEGMENT3,

PersonInformationPEO.CUSTOM_SEGMENT4,

PersonInformationPEO.CUSTOM_SEGMENT5,

PersonInformationPEO.CUSTOM_SEGMENT6,

PersonInformationPEO.CUSTOM_SEGMENT7,

PersonInformationPEO.CUSTOM_SEGMENT8,

PersonInformationPEO.CUSTOM_SEGMENT9,

PersonInformationPEO.CUSTOM_SEGMENT10,

PersonInformationPEO.CUSTOM_SEGMENT11,

PersonInformationPEO.CUSTOM_SEGMENT12,

PersonInformationPEO.CUSTOM_SEGMENT13,

PersonInformationPEO.CUSTOM_SEGMENT14,

PersonInformationPEO.CUSTOM_SEGMENT15,

PersonInformationPEO.CUSTOM_SEGMENT16,

PersonInformationPEO.CUSTOM_SEGMENT17,

PersonInformationPEO.CUSTOM_SEGMENT18,

PersonInformationPEO.CUSTOM_SEGMENT19,

PersonInformationPEO.CUSTOM_SEGMENT20,

PersonInformationPEO.CUSTOM_SEGMENT21,

PersonInformationPEO.CUSTOM_SEGMENT22,

PersonInformationPEO.CUSTOM_SEGMENT23,

PersonInformationPEO.CUSTOM_SEGMENT24,

PersonInformationPEO.CUSTOM_SEGMENT25,

PersonInformationPEO.CUSTOM_SEGMENT26,

PersonInformationPEO.CUSTOM_SEGMENT27,

PersonInformationPEO.CUSTOM_SEGMENT28,

PersonInformationPEO.CUSTOM_SEGMENT29,

PersonInformationPEO.CUSTOM_SEGMENT30,

PersonInformationPEO.CUSTOM_SEGMENT31,

PersonInformationPEO.CUSTOM_SEGMENT32,

PersonInformationPEO.CUSTOM_SEGMENT33,

PersonInformationPEO.CUSTOM_SEGMENT34,

PersonInformationPEO.CUSTOM_SEGMENT35,

PersonInformationPEO.CUSTOM_SEGMENT36,

PersonInformationPEO.CUSTOM_SEGMENT37,

PersonInformationPEO.CUSTOM_SEGMENT38,

PersonInformationPEO.CUSTOM_SEGMENT39,

PersonInformationPEO.CUSTOM_SEGMENT40,

PersonInformationPEO.CUSTOM_SEGMENT41,

PersonInformationPEO.CUSTOM_SEGMENT42,

PersonInformationPEO.CUSTOM_SEGMENT43,

PersonInformationPEO.CUSTOM_SEGMENT44,

PersonInformationPEO.CUSTOM_SEGMENT45,

PersonInformationPEO.CUSTOM_SEGMENT46,

PersonInformationPEO.CUSTOM_SEGMENT47,

PersonInformationPEO.CUSTOM_SEGMENT48,

PersonInformationPEO.CUSTOM_SEGMENT49,

PersonInformationPEO.CUSTOM_SEGMENT50,

PersonInformationPEO.WORKER_POTENTIAL,

PersonInformationPEO.RISK_OF_LOSS,

PersonInformationPEO.VETERAN_STATUS,

PersonInformationPEO.NON_DISCRIMINATION_CODE,

PersonInformationPEO.NEW_BASE_SALARY,

PersonInformationPEO.NEW_GRADE_QUARTILE,

PersonInformationPEO.NEW_GRADE_QUINTILE,

PersonInformationPEO.NEW_GRADE_DECILE,

PersonInformationPEO.NEW_GRADE_COMPARATIO,

PersonInformationPEO.NEW_GRADE_PCT_IN_RANGE,

PersonInformationPEO.ACTION_REASON_ID,

PersonInformationPEO.DO_NOT_POST_FLAG,

PersonInformationPEO.ASSIGNMENT_POSTING_DATE,

PersonInformationPEO.ASSIGNMENT_POSTED_DATE,

PersonInformationPEO.LAST_UPDATED_BY,

PersonInformationPEO.LAST_UPDATE_DATE,

PersonInformationPEO.BUSINESS_GROUP_ID,

PersonInformationPEO.RVW_MGR_OVERRIDE_PERSON_ID,

PersonInformationPEO.RVW_MGR_OVERRIDE_DATE,

PersonInformationPEO.RVW_MGR_PERSON_ID,

PersonInformationPEO.SEC_MGR_PERSON_ID,

PersonInformationPEO.EFFECTIVE_DATE,

PlanPeriodPEO.PERIOD_ID AS PLAN_PERIOD_ID,

PlanPeriodPEO.COMP_TYPE AS PERIOD_COMP_TYPE,

PlanPeriodPEO.ASSIGNMENT_CHANGE_DATE,

PlanPeriodPEO.PERFORMANCE_DATE,

(PersonInformationPEO.BASE_SALARY -

PersonInformationPEO.BASE_SALARY_CHANGE_VAL) PRIOR_SALARY,

(PersonInformationPEO.BASE_SALARY_CHANGE_VAL / (decode((PersonInformationPEO.BASE_SALARY - PersonInformationPEO.BASE_SALARY_CHANGE_VAL),0,null,(PersonInformationPEO.BASE_SALARY - PersonInformationPEO.BASE_SALARY_CHANGE_VAL))))*100 PRIOR_SALARY_CHG_PCT,

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR *PersonInformationPEO.FTE_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR),1)/nvl(pl.annualization_factor,1) adjustment_factor,

PersonInformationPEO.BASE_SALARY *

(nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR

*PersonInformationPEO.FTE_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR),1)/nvl(pl.annualization_factor,1))

adjusted_salary_current,

PersonInformationPEO.NEW_BASE_SALARY *

(nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR

*PersonInformationPEO.FTE_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR),1)/nvl(pl.annualization_factor,1))

adjusted_salary_new,

(PersonInformationPEO.NEW_BASE_SALARY *

(nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR

*PersonInformationPEO.FTE_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR),1)/nvl(pl.annualization_factor,1)))

-

(PersonInformationPEO.BASE_SALARY *

(nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR

*PersonInformationPEO.FTE_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR),1)/nvl(pl.annualization_factor,1)))

adjusted_change_amount,

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR/nvl(PersonInformationPEO.FTE_FACTOR,1)),1) fte_annualization_factor,

PersonInformationPEO.BASE_SALARY *

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR/nvl(PersonInformationPEO.FTE_FACTOR,1)),1)

fte_annual_salary_current,

PersonInformationPEO.NEW_BASE_SALARY *

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR/nvl(PersonInformationPEO.FTE_FACTOR,1)),1)

fte_annual_salary_new ,

(PersonInformationPEO.NEW_BASE_SALARY *

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR/nvl(PersonInformationPEO.FTE_FACTOR,1)),1))-

(PersonInformationPEO.BASE_SALARY *

nvl(decode(PersonInformationPEO.base_salary_frequency,'HOURLY',PersonInformationPEO.PAY_ANNUALIZATION_FACTOR,PersonInformationPEO.PAY_ANNUALIZATION_FACTOR/nvl(PersonInformationPEO.FTE_FACTOR,1)),1))

fte_annual_change_amt,

ExchangeRatePEO.XCHG_RATE,

CASE

WHEN PersonInformationPEO.BASE_SALARY_CURRENCY = FND_GLOBAL.CURRENCY

THEN 1

WHEN pl.CORPORATE_CURRENCY = FND_GLOBAL.CURRENCY

THEN nvl(1/ExchangeRatePEO.XCHG_RATE,1)

ELSE

decode(nvl(1/ExchangeRatePEO.XCHG_RATE,-1),

-1,1,

decode(nvl (UserPrfXchgRate.XCHG_RATE,GL_CURRENCY_API.GET_CLOSEST_RATE_SQL(pl.corporate_currency,FND_GLOBAL.CURRENCY,sysdate,'Corporate',366)),

0,1,

-1,1,

-2,1,

(1/ExchangeRatePEO.XCHG_RATE) * nvl (UserPrfXchgRate.XCHG_RATE,GL_CURRENCY_API.GET_CLOSEST_RATE_SQL(pl.corporate_currency,FND_GLOBAL.CURRENCY,sysdate,'Corporate',366))

))

END AS USER_PREFERRED_EXCHANGE_RATE,

PersonInformationPEO.ASSIGNMENT_NAME

From

CMP_CWB_PERSON_INFO PersonInformationPEO,

cmp_plans_b pl,

CMP_PLAN_PERIODS PlanPeriodPEO,

CMP_CWB_XCHG ExchangeRatePEO,

CMP_CWB_PERSON_RATES PersonRatesPEO,

CMP_CWB_XCHG UserPrfXchgRate

Where PersonInformationPEO.plan_id = pl.plan_id

AND PersonInformationPEO.plan_id = PlanPeriodPEO.plan_id

AND PersonInformationPEO.period_id = PlanPeriodPEO.period_id

AND PersonInformationPEO.PLAN_ID = ExchangeRatePEO.PLAN_ID(+)

AND PersonInformationPEO.PERIOD_ID = ExchangeRatePEO.PERIOD_ID(+)

AND PersonInformationPEO.BASE_SALARY_CURRENCY = ExchangeRatePEO.CURRENCY(+)

AND PersonInformationPEO.PERSON_EVENT_ID = PersonRatesPEO.PERSON_EVENT_ID (+)

AND PersonRatesPEO.component_id (+) = -1

AND UserPrfXchgRate.plan_id(+) = PersonInformationPEO.plan_id

AND UserPrfXchgRate.period_id(+)= PersonInformationPEO.period_id

AND UserPrfXchgRate.currency(+) = FND_GLOBAL.CURRENCY