PAY_XLA_ENC_ACTIONS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
EVENT_ID PAYROLL_REL_ACTION_ID COST_ACTION_ID COST_ACTION_TYPE COST_PROCESS_EFFECTIVE_DATE COST_PROCESS_FLOW_NAME COST_PROCESS_NAME BASE_PROCESS_FLOW_NAME BASE_PROCESS_NAME ACCOUNTING_DATE TIME_PERIOD_ID PERSON_NAME CONVERSION_RATE CONVERSION_RATE_TYPE ENCUMBRANCE_TYPE_ID TRANSACTION_DATE PAYROLL_NAME PAYROLL_ID REVERSAL_FLAG PERIOD_NAME PERIOD_END_DATE NEXT_PERIOD_START_DATE REGULAR_PROCESS_DATE REGULAR_PROCESS_DATE_PLUS_1

Name

EVENT_ID

PAYROLL_REL_ACTION_ID

COST_ACTION_ID

COST_ACTION_TYPE

COST_PROCESS_EFFECTIVE_DATE

COST_PROCESS_FLOW_NAME

COST_PROCESS_NAME

BASE_PROCESS_FLOW_NAME

BASE_PROCESS_NAME

ACCOUNTING_DATE

TIME_PERIOD_ID

PERSON_NAME

CONVERSION_RATE

CONVERSION_RATE_TYPE

ENCUMBRANCE_TYPE_ID

TRANSACTION_DATE

PAYROLL_NAME

PAYROLL_ID

REVERSAL_FLAG

PERIOD_NAME

PERIOD_END_DATE

NEXT_PERIOD_START_DATE

REGULAR_PROCESS_DATE

REGULAR_PROCESS_DATE_PLUS_1

Query

SQL_Statement
SELECT PXE.EVENT_ID, TGL_RA.PAYROLL_REL_ACTION_ID, COST_RA.PAYROLL_REL_ACTION_ID COST_ACTION_ID, COST_PA.ACTION_TYPE COST_ACTION_TYPE, COST_PA.EFFECTIVE_DATE COST_PROCESS_EFFECTIVE_DATE, PAY_SLA_PKG.GET_FLOW_NAME(COST_PA.PAYROLL_ACTION_ID) COST_PROCESS_FLOW_NAME, PAY_SLA_PKG.GET_PROCESS_NAME(COST_PA.ACTION_TYPE) COST_PROCESS_NAME, PAY_SLA_PKG.GET_FLOW_NAME(COST_PA.PAYROLL_ACTION_ID) BASE_PROCESS_FLOW_NAME, PAY_SLA_PKG.GET_PROCESS_NAME(COST_PA.ACTION_TYPE) BASE_PROCESS_NAME, PXE.ACCOUNTING_DATE, PXE.TIME_PERIOD_ID, PAY_SLA_PKG.GET_PERSON_NAME(PER.FULL_NAME) PERSON_NAME, null CONVERSION_RATE, PAY_SLA_PKG.GET_CONVERSION_TYPE(COST_PA.LEGISLATIVE_DATA_GROUP_ID, PXE.ACCOUNTING_DATE) CONVERSION_RATE_TYPE, PAY_SLA_PKG.GET_ENCUMBRANCE_TYPE_ID(COST_PA.PAYROLL_ACTION_ID, PXE.ACCOUNTING_DATE) ENCUMBRANCE_TYPE_ID, to_char(PXE.ACCOUNTING_DATE, 'YYYY/MM/DD') TRANSACTION_DATE, PAY.PAYROLL_NAME, PXE.PAYROLL_ID, PXE.REVERSAL_FLAG, PTP.PERIOD_NAME, PTP.END_DATE PERIOD_END_DATE, (PTP.END_DATE+1) NEXT_PERIOD_START_DATE, PTP.REGULAR_PROCESS_DATE, (PTP.REGULAR_PROCESS_DATE+1) REGULAR_PROCESS_DATE_PLUS_1 FROM PAY_XLA_EVENTS PXE, PAY_PAYROLL_REL_ACTIONS TGL_RA, PAY_ACTION_INTERLOCKS COST_LOCK, PAY_PAYROLL_REL_ACTIONS COST_RA, PAY_PAYROLL_ACTIONS COST_PA, PAY_PAY_RELATIONSHIPS_DN PPR, PER_PERSON_NAMES_F_V PER, PAY_ALL_PAYROLLS_F PAY, PAY_TIME_PERIODS PTP WHERE TGL_RA.PAYROLL_REL_ACTION_ID = PXE.PAYROLL_REL_ACTION_ID AND COST_LOCK.LOCKING_ACTION_ID = TGL_RA.PAYROLL_REL_ACTION_ID AND COST_RA.PAYROLL_REL_ACTION_ID = COST_LOCK.LOCKED_ACTION_ID AND COST_PA.PAYROLL_ACTION_ID = COST_RA.PAYROLL_ACTION_ID AND COST_PA.ACTION_TYPE = 'ENC' AND PPR.PAYROLL_RELATIONSHIP_ID = TGL_RA.PAYROLL_RELATIONSHIP_ID AND PER.PERSON_ID (+) = PPR.PERSON_ID AND PXE.ACCOUNTING_DATE BETWEEN PER.EFFECTIVE_START_DATE (+) AND PER.EFFECTIVE_END_DATE (+) AND PAY.PAYROLL_ID = COST_PA.PAYROLL_ID AND PXE.ACCOUNTING_DATE BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE AND PTP.TIME_PERIOD_ID = PXE.TIME_PERIOD_ID

SQL_Statement

SELECT PXE.EVENT_ID,

TGL_RA.PAYROLL_REL_ACTION_ID,

COST_RA.PAYROLL_REL_ACTION_ID COST_ACTION_ID,

COST_PA.ACTION_TYPE COST_ACTION_TYPE,

COST_PA.EFFECTIVE_DATE COST_PROCESS_EFFECTIVE_DATE,

PAY_SLA_PKG.GET_FLOW_NAME(COST_PA.PAYROLL_ACTION_ID) COST_PROCESS_FLOW_NAME,

PAY_SLA_PKG.GET_PROCESS_NAME(COST_PA.ACTION_TYPE) COST_PROCESS_NAME,

PAY_SLA_PKG.GET_FLOW_NAME(COST_PA.PAYROLL_ACTION_ID) BASE_PROCESS_FLOW_NAME,

PAY_SLA_PKG.GET_PROCESS_NAME(COST_PA.ACTION_TYPE) BASE_PROCESS_NAME,

PXE.ACCOUNTING_DATE,

PXE.TIME_PERIOD_ID,

PAY_SLA_PKG.GET_PERSON_NAME(PER.FULL_NAME) PERSON_NAME,

null CONVERSION_RATE,

PAY_SLA_PKG.GET_CONVERSION_TYPE(COST_PA.LEGISLATIVE_DATA_GROUP_ID, PXE.ACCOUNTING_DATE) CONVERSION_RATE_TYPE,

PAY_SLA_PKG.GET_ENCUMBRANCE_TYPE_ID(COST_PA.PAYROLL_ACTION_ID, PXE.ACCOUNTING_DATE) ENCUMBRANCE_TYPE_ID,

to_char(PXE.ACCOUNTING_DATE, 'YYYY/MM/DD') TRANSACTION_DATE,

PAY.PAYROLL_NAME,

PXE.PAYROLL_ID,

PXE.REVERSAL_FLAG,

PTP.PERIOD_NAME,

PTP.END_DATE PERIOD_END_DATE,

(PTP.END_DATE+1) NEXT_PERIOD_START_DATE,

PTP.REGULAR_PROCESS_DATE,

(PTP.REGULAR_PROCESS_DATE+1) REGULAR_PROCESS_DATE_PLUS_1

FROM

PAY_XLA_EVENTS PXE,

PAY_PAYROLL_REL_ACTIONS TGL_RA,

PAY_ACTION_INTERLOCKS COST_LOCK,

PAY_PAYROLL_REL_ACTIONS COST_RA,

PAY_PAYROLL_ACTIONS COST_PA,

PAY_PAY_RELATIONSHIPS_DN PPR,

PER_PERSON_NAMES_F_V PER,

PAY_ALL_PAYROLLS_F PAY,

PAY_TIME_PERIODS PTP

WHERE TGL_RA.PAYROLL_REL_ACTION_ID = PXE.PAYROLL_REL_ACTION_ID

AND COST_LOCK.LOCKING_ACTION_ID = TGL_RA.PAYROLL_REL_ACTION_ID

AND COST_RA.PAYROLL_REL_ACTION_ID = COST_LOCK.LOCKED_ACTION_ID

AND COST_PA.PAYROLL_ACTION_ID = COST_RA.PAYROLL_ACTION_ID

AND COST_PA.ACTION_TYPE = 'ENC'

AND PPR.PAYROLL_RELATIONSHIP_ID = TGL_RA.PAYROLL_RELATIONSHIP_ID

AND PER.PERSON_ID (+) = PPR.PERSON_ID

AND PXE.ACCOUNTING_DATE BETWEEN PER.EFFECTIVE_START_DATE (+) AND PER.EFFECTIVE_END_DATE (+)

AND PAY.PAYROLL_ID = COST_PA.PAYROLL_ID

AND PXE.ACCOUNTING_DATE BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE

AND PTP.TIME_PERIOD_ID = PXE.TIME_PERIOD_ID