HCM Extract - Payroll Balances

Balance Group

Balance Group Usage

UEs & Connection

Parameter

Balance DG - Filter Criteria

ppa.effective_date between pay_report_utils.get_parameter_value_date('FROMDATE') and pay_report_utils.get_parameter_value_date('THRUDATE')
AND PPA.ACTION_TYPE IN ('Q','R','V','I')
AND PPA.ACTION_STATUS = 'C'
AND EXISTS (SELECT 'x' FROM PAY_RUN_RESULTS PRR
                , PAY_RUN_RESULT_VALUES PRRV
                , PAY_BALANCE_FEEDS_F PBF
                , PAY_BALANCE_TYPES_TL PB
            WHERE PRR.PAYROLL_REL_ACTION_ID    = PRA.PAYROLL_REL_ACTION_ID
                AND PRRV.RUN_RESULT_ID                = PRR.RUN_RESULT_ID
                AND PBF.INPUT_VALUE_ID                = PRRV.INPUT_VALUE_ID
                AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE AND PBF.EFFECTIVE_END_DATE
                AND PB.BALANCE_TYPE_ID                = PBF.BALANCE_TYPE_ID
                AND PB.LANGUAGE                        = 'US'
                AND (PB.BALANCE_NAME LIKE '%401k%' OR PB.BALANCE_NAME IN ('Hours Worked','Hours Paid'))
            )

Balance DG - Records

Bal_PPRA Record

Pay Rel Action ID - PAYROLL REL ACTION RELATIONSHIP IDENTIFIER

Pay Action EFFDT - PAYROLL ACTION EFFECTIVE DATE

Balance Record

YTD Balance

YTD Balance DG - Filter Criteria

ppa.effective_date BETWEEN pay_report_utils.get_parameter_value_date('YEARBEGIN') AND pay_report_utils.get_parameter_value_date('THRUDATE')

AND PPA.ACTION_TYPE IN ('Q','R','V','I')

AND PPA.ACTION_STATUS = 'C'

AND EXISTS (SELECT 'x' FROM PAY_RUN_RESULTS PRR

, PAY_RUN_RESULT_VALUES PRRV

, PAY_BALANCE_FEEDS_F PBF

, PAY_BALANCE_TYPES_TL PB

WHERE PRR.PAYROLL_REL_ACTION_ID = PRA.PAYROLL_REL_ACTION_ID

AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID

AND PBF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID

AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE AND PBF.EFFECTIVE_END_DATE 

AND PB.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID

AND PB.LANGUAGE = 'US'

AND (PB.BALANCE_NAME IN ('IC', 'Hours worked', 'Hours paid'))

)

AND (PRA.PAYROLL_RELATIONSHIP_ID, PPA.EFFECTIVE_DATE, PRA.ACTION_SEQUENCE) IN (

SELECT PRA1.PAYROLL_RELATIONSHIP_ID, MAX(PPA1.EFFECTIVE_DATE), MAX(PRA1.ACTION_SEQUENCE)

FROM PAY_PAY_RELATIONSHIPS_DN PRD1

, PAY_PAYROLL_REL_ACTIONS PRA1

, PAY_PAYROLL_ACTIONS PPA1

, PAY_RUN_RESULTS PRR1

, PAY_RUN_RESULT_VALUES PRRV1

, PAY_BALANCE_FEEDS_F PBF1

, PAY_BALANCE_TYPES_TL PB1

WHERE 1 = 1

AND PRA1.PAYROLL_RELATIONSHIP_ID = PRD1.PAYROLL_RELATIONSHIP_ID

AND PRA1.RETRO_COMPONENT_ID IS NULL

AND PRA1.ACTION_STATUS = 'C'

AND PPA1.PAYROLL_ACTION_ID = PRA1.PAYROLL_ACTION_ID

-- AND PPA1.EFFECTIVE_DATE BETWEEN TO_DATE('01012021','MMDDYYYY') AND TO_DATE('03262021','MMDDYYYY')

AND PPA1.EFFECTIVE_DATE BETWEEN pay_report_utils.get_parameter_value_date('YEARBEGIN') AND pay_report_utils.get_parameter_value_date('THRUDATE')

AND PPA1.ACTION_TYPE IN ('Q','R','V','I')

AND PPA1.ACTION_STATUS = 'C'

AND PRR1.PAYROLL_REL_ACTION_ID = PRA1.PAYROLL_REL_ACTION_ID

AND PRRV1.RUN_RESULT_ID = PRR1.RUN_RESULT_ID

AND PBF1.INPUT_VALUE_ID = PRRV1.INPUT_VALUE_ID

AND PPA1.EFFECTIVE_DATE BETWEEN PBF1.EFFECTIVE_START_DATE AND PBF1.EFFECTIVE_END_DATE 

AND PB1.BALANCE_TYPE_ID = PBF1.BALANCE_TYPE_ID

AND PB1.LANGUAGE = 'US'

AND (PB1.BALANCE_NAME LIKE '%401K%' 

OR PB1.BALANCE_NAME IN ('IC', 'Hours worked', 'Hours paid'))

and PRD1.person_id = REL.PERSON_ID

GROUP BY PRA1.PAYROLL_RELATIONSHIP_ID

)

HCM Extract - Sample | XSL Template