1. Tables and Views for HCM
  2. PAY_BALANCE_BATCH_LINES

PAY_BALANCE_BATCH_LINES

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

BATCH_ID

BATCH_LINE_ID

BATCH_LINE_STATUS

GROUPING_ID

GROUPING_VALUE

PAYROLL_RELATIONSHIP_ID

BALANCE_DIMENSION_ID

BALANCE_TYPE_ID

PAYROLL_REL_ACTION_ID

VALUE

UPLOAD_DATE

BALANCE_NAME

DIMENSION_NAME

PAYROLL_RELATIONSHIP_NUMBER

TERM_NUMBER

PAYROLL_TERM_ID

ASSIGNMENT_NUMBER

PAYROLL_ASSIGNMENT_ID

LEGAL_EMPLOYER_NAME

LEGAL_EMPLOYER_ID

ELEMENT_ENTRY_ID

PAYROLL_NAME

PAYROLL_ID

AREA1

AREA2

AREA3

AREA4

THIRD_PARTY_PAYEE_NAME

THIRD_PARTY_PAYEE_ID

TIME_DEFINITION_NAME

TIME_DEFINITION_ID

CALC_BREAKDOWN_ID

BALANCE_DATE

TAX_UNIT_NAME

TAX_UNIT_ID

RUN_TYPE_NAME

RUN_TYPE_ID

CONTEXT1_NAME

CONTEXT1_ID

CONTEXT1_VALUE

CONTEXT2_NAME

CONTEXT2_ID

CONTEXT2_VALUE

CONTEXT3_NAME

CONTEXT3_ID

CONTEXT3_VALUE

CONTEXT4_NAME

CONTEXT4_ID

CONTEXT4_VALUE

CONTEXT5_NAME

CONTEXT5_ID

CONTEXT5_VALUE

CONTEXT6_NAME

CONTEXT6_ID

CONTEXT6_VALUE

Query

SQL_Statement

SELECT

pbl.BATCH_ID,

pbl.BATCH_LINE_ID,

pbl.BATCH_LINE_STATUS,

pbl.GROUPING_ID,

pbl.GROUPING_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL_RELATIONSHIP'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS PAYROLL_RELATIONSHIP_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','BALANCE_DIMENSION'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS BALANCE_DIMENSION_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','BALANCE_TYPE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS BALANCE_TYPE_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PERSON_PROCESS'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS PAYROLL_REL_ACTION_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','VALUE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','UPLOAD_DATE'), TO_DATE(pblv.ACTION_PARAMETER_VALUE, 'YYYY/MM/DD HH24:MI:SS'), NULL)) AS UPLOAD_DATE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','BALANCE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS BALANCE_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','DIMENSION_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS DIMENSION_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL_RELATIONSHIP_NUMBER'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS PAYROLL_RELATIONSHIP_NUMBER,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','TERM_NUMBER'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS TERM_NUMBER,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL_TERM'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS PAYROLL_TERM_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','ASSIGNMENT_NUMBER'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS ASSIGNMENT_NUMBER,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL_ASSIGNMENT'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS PAYROLL_ASSIGNMENT_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','LEGAL_EMPLOYER_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS LEGAL_EMPLOYER_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','LEGAL_EMPLOYER'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS LEGAL_EMPLOYER_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','ELEMENT_ENTRY'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS ELEMENT_ENTRY_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS PAYROLL_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','PAYROLL'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS PAYROLL_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','AREA_ONE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS AREA1,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','AREA_TWO'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS AREA2,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','AREA_THREE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS AREA3,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','AREA_FOUR'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS AREA4,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','THIRD_PARTY_PAYEE_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS THIRD_PARTY_PAYEE_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','THIRD_PARTY_PAYEE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS THIRD_PARTY_PAYEE_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','TIME_DEFINITION_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS TIME_DEFINITION_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','TIME_DEFINITION'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS TIME_DEFINITION_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CALCULATION_BREAKDOWN'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CALC_BREAKDOWN_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','BALANCE_DATE'), TO_DATE(pblv.ACTION_PARAMETER_VALUE, 'YYYY/MM/DD HH24:MI:SS'), NULL)) AS BALANCE_DATE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','TAX_REPORTING_UNIT_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS TAX_UNIT_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','TAX_REPORTING_UNIT'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS TAX_UNIT_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','RUN_TYPE_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS RUN_TYPE_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','RUN_TYPE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS RUN_TYPE_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_ONE_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT1_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_ONE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT1_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_ONE_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT1_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_TWO_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT2_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_TWO'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT2_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_TWO_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT2_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_THREE_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT3_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_THREE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT3_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_THREE_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT3_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FOUR_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT4_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FOUR'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT4_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FOUR_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT4_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FIVE_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT5_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FIVE'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT5_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_FIVE_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT5_VALUE,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_SIX_NAME'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT6_NAME,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_SIX'), TO_NUMBER(pblv.ACTION_PARAMETER_VALUE), NULL)) AS CONTEXT6_ID,

MAX(DECODE(pblv.ACTION_PARAMETER_ID, PAY_BATCH_LDR_PKG.get_param_id(pbh.legislative_data_group_id,'BALANCE','BALANCE_INITIALIZATION','CONTEXT_SIX_VALUE'), pblv.ACTION_PARAMETER_VALUE, NULL)) AS CONTEXT6_VALUE

FROM

PAY_BATCH_LINES pbl,

PAY_BATCH_LINE_VALUES pblv,

pay_batch_headers pbh

WHERE

pbl.BATCH_LINE_ID = pblv.BATCH_LINE_ID

AND pbh.batch_id=pbl.batch_id

GROUP BY

pbl.BATCH_ID,

pbl.BATCH_LINE_ID,

pbl.BATCH_LINE_STATUS,

pbl.GROUPING_ID,

pbl.GROUPING_VALUE