PAY_XLA_COSTS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
EVENT_ID LINE_NUMBER PAYROLL_REL_ACTION_ID COST_ACTION_ID RUN_ACTION_ID COSTED_VALUE DEBIT_OR_CREDIT CURRENCY_CODE ELEMENT_NAME DISTRIBUTED_ELEMENT_NAME COST_ALLOCATION_KEYFLEX_ID SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6 SEGMENT7 SEGMENT8 SEGMENT9 SEGMENT10 SEGMENT11 SEGMENT12 SEGMENT13 SEGMENT14 SEGMENT15 SEGMENT16 SEGMENT17 SEGMENT18 SEGMENT19 SEGMENT20 SEGMENT21 SEGMENT22 SEGMENT23 SEGMENT24 SEGMENT25 SEGMENT26 SEGMENT27 SEGMENT28 SEGMENT29 SEGMENT30 TERM_NUMBER ASSIGNMENT_NUMBER

Name

EVENT_ID

LINE_NUMBER

PAYROLL_REL_ACTION_ID

COST_ACTION_ID

RUN_ACTION_ID

COSTED_VALUE

DEBIT_OR_CREDIT

CURRENCY_CODE

ELEMENT_NAME

DISTRIBUTED_ELEMENT_NAME

COST_ALLOCATION_KEYFLEX_ID

SEGMENT1

SEGMENT2

SEGMENT3

SEGMENT4

SEGMENT5

SEGMENT6

SEGMENT7

SEGMENT8

SEGMENT9

SEGMENT10

SEGMENT11

SEGMENT12

SEGMENT13

SEGMENT14

SEGMENT15

SEGMENT16

SEGMENT17

SEGMENT18

SEGMENT19

SEGMENT20

SEGMENT21

SEGMENT22

SEGMENT23

SEGMENT24

SEGMENT25

SEGMENT26

SEGMENT27

SEGMENT28

SEGMENT29

SEGMENT30

TERM_NUMBER

ASSIGNMENT_NUMBER

Query

SQL_Statement
SELECT PXE.EVENT_ID, PC.COST_ID LINE_NUMBER, TGL_RA.PAYROLL_REL_ACTION_ID, COST_RA.PAYROLL_REL_ACTION_ID COST_ACTION_ID, RUN_RA.PAYROLL_REL_ACTION_ID RUN_ACTION_ID, PC.COSTED_VALUE, decode(nvl(PXE.REVERSAL_FLAG, 'N'), 'N', PC.DEBIT_OR_CREDIT, decode(PC.DEBIT_OR_CREDIT, 'C', 'D', 'C')) DEBIT_OR_CREDIT, DECODE(IV.UOM, 'M', ET.OUTPUT_CURRENCY_CODE, 'STAT') CURRENCY_CODE, ETTL.ELEMENT_NAME, DIST_ETTL.ELEMENT_NAME DISTRIBUTED_ELEMENT_NAME, KEY.COST_ALLOCATION_KEYFLEX_ID, KEY.SEGMENT1, KEY.SEGMENT2, KEY.SEGMENT3, KEY.SEGMENT4, KEY.SEGMENT5, KEY.SEGMENT6, KEY.SEGMENT7, KEY.SEGMENT8, KEY.SEGMENT9, KEY.SEGMENT10, KEY.SEGMENT11, KEY.SEGMENT12, KEY.SEGMENT13, KEY.SEGMENT14, KEY.SEGMENT15, KEY.SEGMENT16, KEY.SEGMENT17, KEY.SEGMENT18, KEY.SEGMENT19, KEY.SEGMENT20, KEY.SEGMENT21, KEY.SEGMENT22, KEY.SEGMENT23, KEY.SEGMENT24, KEY.SEGMENT25, KEY.SEGMENT26, KEY.SEGMENT27, KEY.SEGMENT28, KEY.SEGMENT29, KEY.SEGMENT30, decode(TERM.RELATIONSHIP_GROUP_ID, null, null, PAY_SLA_PKG.GET_ASG_NUM(TERM.RELATIONSHIP_GROUP_ID,RUN_PA.EFFECTIVE_DATE)) TERM_NUMBER, decode(ASG.RELATIONSHIP_GROUP_ID, null, null, PAY_SLA_PKG.GET_ASG_NUM(ASG.RELATIONSHIP_GROUP_ID,RUN_PA.EFFECTIVE_DATE)) ASSIGNMENT_NUMBER 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_ACTION_CLASSIFICATIONS COST_CLA, PAY_ACTION_INTERLOCKS RUN_LOCK, PAY_PAYROLL_REL_ACTIONS RUN_RA, PAY_PAYROLL_ACTIONS RUN_PA, PAY_ACTION_CLASSIFICATIONS RUN_CLA, PAY_PAYROLL_REL_ACTIONS RUN_RA2, PAY_COSTS PC, PAY_RUN_RESULTS RR, PAY_REL_GROUPS_DN TERM, PAY_REL_GROUPS_DN ASG, PAY_INPUT_VALUES_F IV, PAY_ELEMENT_TYPES_F ET, PAY_ELEMENT_TYPES_TL ETTL, PAY_INPUT_VALUES_F DIST_IV, PAY_ELEMENT_TYPES_TL DIST_ETTL, PAY_COST_ALLOCATION_KEYFLEX KEY 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 = COST_CLA.ACTION_TYPE AND COST_CLA.CLASSIFICATION_NAME = 'TRANSGL' AND COST_PA.ACTION_TYPE in ('C', 'S','CA') AND RUN_LOCK.LOCKING_ACTION_ID = TGL_RA.PAYROLL_REL_ACTION_ID AND RUN_RA.PAYROLL_REL_ACTION_ID = RUN_LOCK.LOCKED_ACTION_ID AND RUN_PA.PAYROLL_ACTION_ID = RUN_RA.PAYROLL_ACTION_ID AND RUN_PA.ACTION_TYPE = RUN_CLA.ACTION_TYPE AND RUN_CLA.CLASSIFICATION_NAME = 'COSTED' AND RUN_RA2.PAYROLL_ACTION_ID = RUN_PA.PAYROLL_ACTION_ID AND RUN_RA2.PAYROLL_RELATIONSHIP_ID = RUN_RA.PAYROLL_RELATIONSHIP_ID AND RR.PAYROLL_REL_ACTION_ID = RUN_RA2.PAYROLL_REL_ACTION_ID AND RR.RUN_RESULT_ID = PC.RUN_RESULT_ID AND PC.PAYROLL_REL_ACTION_ID = COST_RA.PAYROLL_REL_ACTION_ID AND TERM.RELATIONSHIP_GROUP_ID (+) = nvl(RR.PAYROLL_TERM_ID, -999) AND TERM.GROUP_TYPE (+) = 'T' AND ASG.RELATIONSHIP_GROUP_ID (+) = nvl(RR.PAYROLL_ASSIGNMENT_ID, -999) AND ASG.GROUP_TYPE (+) = 'A' AND IV.INPUT_VALUE_ID = PC.INPUT_VALUE_ID AND RUN_PA.DATE_EARNED BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE AND ET.ELEMENT_TYPE_ID = RR.ELEMENT_TYPE_ID AND RUN_PA.DATE_EARNED BETWEEN ET.EFFECTIVE_START_DATE AND ET.EFFECTIVE_END_DATE AND ETTL.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID AND ETTL.LANGUAGE (+) = USERENV('LANG') AND DIST_IV.INPUT_VALUE_ID (+) = PC.DISTRIBUTED_INPUT_VALUE_ID AND RUN_PA.DATE_EARNED BETWEEN DIST_IV.EFFECTIVE_START_DATE (+) AND DIST_IV.EFFECTIVE_END_DATE (+) AND DIST_ETTL.ELEMENT_TYPE_ID (+) = DIST_IV.ELEMENT_TYPE_ID AND DIST_ETTL.LANGUAGE (+) = USERENV('LANG') AND PC.COST_ALLOCATION_KEYFLEX_ID = KEY.COST_ALLOCATION_KEYFLEX_ID AND PC.TRANSFER_TO_GL_FLAG = 'Y'

SQL_Statement

SELECT PXE.EVENT_ID, PC.COST_ID LINE_NUMBER, TGL_RA.PAYROLL_REL_ACTION_ID, COST_RA.PAYROLL_REL_ACTION_ID COST_ACTION_ID,

RUN_RA.PAYROLL_REL_ACTION_ID RUN_ACTION_ID, PC.COSTED_VALUE,

decode(nvl(PXE.REVERSAL_FLAG, 'N'), 'N', PC.DEBIT_OR_CREDIT, decode(PC.DEBIT_OR_CREDIT, 'C', 'D', 'C')) DEBIT_OR_CREDIT,

DECODE(IV.UOM, 'M', ET.OUTPUT_CURRENCY_CODE, 'STAT') CURRENCY_CODE, ETTL.ELEMENT_NAME,

DIST_ETTL.ELEMENT_NAME DISTRIBUTED_ELEMENT_NAME,

KEY.COST_ALLOCATION_KEYFLEX_ID, KEY.SEGMENT1, KEY.SEGMENT2, KEY.SEGMENT3, KEY.SEGMENT4, KEY.SEGMENT5,

KEY.SEGMENT6, KEY.SEGMENT7, KEY.SEGMENT8, KEY.SEGMENT9, KEY.SEGMENT10, KEY.SEGMENT11, KEY.SEGMENT12,

KEY.SEGMENT13, KEY.SEGMENT14, KEY.SEGMENT15, KEY.SEGMENT16, KEY.SEGMENT17, KEY.SEGMENT18,

KEY.SEGMENT19, KEY.SEGMENT20, KEY.SEGMENT21, KEY.SEGMENT22, KEY.SEGMENT23, KEY.SEGMENT24,

KEY.SEGMENT25, KEY.SEGMENT26, KEY.SEGMENT27, KEY.SEGMENT28, KEY.SEGMENT29, KEY.SEGMENT30,

decode(TERM.RELATIONSHIP_GROUP_ID, null, null, PAY_SLA_PKG.GET_ASG_NUM(TERM.RELATIONSHIP_GROUP_ID,RUN_PA.EFFECTIVE_DATE)) TERM_NUMBER,

decode(ASG.RELATIONSHIP_GROUP_ID, null, null, PAY_SLA_PKG.GET_ASG_NUM(ASG.RELATIONSHIP_GROUP_ID,RUN_PA.EFFECTIVE_DATE)) ASSIGNMENT_NUMBER

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_ACTION_CLASSIFICATIONS COST_CLA,

PAY_ACTION_INTERLOCKS RUN_LOCK,

PAY_PAYROLL_REL_ACTIONS RUN_RA,

PAY_PAYROLL_ACTIONS RUN_PA,

PAY_ACTION_CLASSIFICATIONS RUN_CLA,

PAY_PAYROLL_REL_ACTIONS RUN_RA2,

PAY_COSTS PC,

PAY_RUN_RESULTS RR,

PAY_REL_GROUPS_DN TERM,

PAY_REL_GROUPS_DN ASG,

PAY_INPUT_VALUES_F IV,

PAY_ELEMENT_TYPES_F ET,

PAY_ELEMENT_TYPES_TL ETTL,

PAY_INPUT_VALUES_F DIST_IV,

PAY_ELEMENT_TYPES_TL DIST_ETTL,

PAY_COST_ALLOCATION_KEYFLEX KEY

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 = COST_CLA.ACTION_TYPE

AND COST_CLA.CLASSIFICATION_NAME = 'TRANSGL'

AND COST_PA.ACTION_TYPE in ('C', 'S','CA')

AND RUN_LOCK.LOCKING_ACTION_ID = TGL_RA.PAYROLL_REL_ACTION_ID

AND RUN_RA.PAYROLL_REL_ACTION_ID = RUN_LOCK.LOCKED_ACTION_ID

AND RUN_PA.PAYROLL_ACTION_ID = RUN_RA.PAYROLL_ACTION_ID

AND RUN_PA.ACTION_TYPE = RUN_CLA.ACTION_TYPE

AND RUN_CLA.CLASSIFICATION_NAME = 'COSTED'

AND RUN_RA2.PAYROLL_ACTION_ID = RUN_PA.PAYROLL_ACTION_ID

AND RUN_RA2.PAYROLL_RELATIONSHIP_ID = RUN_RA.PAYROLL_RELATIONSHIP_ID

AND RR.PAYROLL_REL_ACTION_ID = RUN_RA2.PAYROLL_REL_ACTION_ID

AND RR.RUN_RESULT_ID = PC.RUN_RESULT_ID

AND PC.PAYROLL_REL_ACTION_ID = COST_RA.PAYROLL_REL_ACTION_ID

AND TERM.RELATIONSHIP_GROUP_ID (+) = nvl(RR.PAYROLL_TERM_ID, -999)

AND TERM.GROUP_TYPE (+) = 'T'

AND ASG.RELATIONSHIP_GROUP_ID (+) = nvl(RR.PAYROLL_ASSIGNMENT_ID, -999)

AND ASG.GROUP_TYPE (+) = 'A'

AND IV.INPUT_VALUE_ID = PC.INPUT_VALUE_ID

AND RUN_PA.DATE_EARNED BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE

AND ET.ELEMENT_TYPE_ID = RR.ELEMENT_TYPE_ID

AND RUN_PA.DATE_EARNED BETWEEN ET.EFFECTIVE_START_DATE AND ET.EFFECTIVE_END_DATE

AND ETTL.ELEMENT_TYPE_ID = ET.ELEMENT_TYPE_ID

AND ETTL.LANGUAGE (+) = USERENV('LANG')

AND DIST_IV.INPUT_VALUE_ID (+) = PC.DISTRIBUTED_INPUT_VALUE_ID

AND RUN_PA.DATE_EARNED BETWEEN DIST_IV.EFFECTIVE_START_DATE (+) AND DIST_IV.EFFECTIVE_END_DATE (+)

AND DIST_ETTL.ELEMENT_TYPE_ID (+) = DIST_IV.ELEMENT_TYPE_ID

AND DIST_ETTL.LANGUAGE (+) = USERENV('LANG')

AND PC.COST_ALLOCATION_KEYFLEX_ID = KEY.COST_ALLOCATION_KEYFLEX_ID

AND PC.TRANSFER_TO_GL_FLAG = 'Y'