Query to fetch mid-month assignment change in per_all_assignments_f

Below query is to fetch mid-month grade changes (grade_id), but this same SQL can be modified to fetch any mid-month assignment change (e.g. payroll, job, organization etc.)

SELECT
    paaf.assignment_number,
    (
        SELECT
            paaf2.effective_start_date
        FROM
            per_all_assignments_f paaf2
        WHERE
            paaf2.assignment_id = paaf.assignment_id
            AND paaf2.effective_end_date = paaf.effective_start_date - 1
    )                         old_eff_start_date,

    (
        SELECT
            paaf2.effective_end_date
        FROM
            per_all_assignments_f paaf2
        WHERE
            paaf2.assignment_id = paaf.assignment_id
            AND paaf2.effective_end_date = paaf.effective_start_date - 1
    )                         old_eff_end_date,
    (
        SELECT
            apps.hr_general.decode_grade(
                paaf2.grade_id
            )
        FROM
            per_all_assignments_f paaf2
        WHERE
            paaf2.assignment_id = paaf.assignment_id
            AND paaf2.effective_end_date = paaf.effective_start_date - 1
    )                         old_grade,
    paaf.effective_start_date current_eff_start_date,
    paaf.effective_end_date   current_eff_end_date,
    apps.hr_general.decode_grade(
        paaf.grade_id
    )                         current_grade
FROM
    per_all_assignments_f paaf
WHERE
    1 = 1
    AND paaf.primary_flag = 'Y'
   AND paaf.assignment_type = 'E'
    AND paaf.payroll_id IS NOT NULL
    AND to_char(
        trunc(
            paaf.last_update_date
        ), 'MON-YYYY'
    ) = 'MAR-2025' /*Pass the month for which you want to fetch the changes*/
    AND to_char(
        paaf.effective_start_date, 'MON-YYYY'
    ) = (
        SELECT
            to_char(
                effective_end_date, 'MON-YYYY'
            )
        FROM
            per_all_assignments_f paaf1
        WHERE
            paaf1.assignment_id = paaf.assignment_id
            AND paaf1.effective_end_date = paaf.effective_start_date - 1
            AND paaf.grade_id <> paaf1.grade_id
    );

For more such queries, please provide a comment !!

How to store balance values in PAY_RUN_BALANCES table?

To store a balance in PAY_RUN_BALANCES, the column SAVE_RUN_BALANCE on PAY_DEFINED_BALANCES must be set to ‘Y’. This column will not be displayed on the user interface.

Reference : Balance Reporting Architecture Technical Brief (Phase 1) (Doc ID 245295.1)

By default, the Status field will be blank.

Query to get balance dimension details (Q1):

select pbt.balance_name,pbd.DIMENSION_NAME,pdb.DEFINED_BALANCE_ID,pdb.SAVE_RUN_BALANCE from
pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pbd.BALANCE_DIMENSION_ID = pdb.BALANCE_DIMENSION_ID
and pbt.balance_name = ‘XX Test Earnings’;

Run payroll.

Check balance value in PAY_RUN_BALANCES table

Query to fetch balance value for an assignment_action_id (Q2):

select pbt.balance_name,pdb.SAVE_RUN_BALANCE,prb.* from PAY_RUN_BALANCES prb
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_defined_balances pdb
,pay_balance_types pbt
where 1=1
and paa.assignment_action_id = :p_assignment_action_id
and prb.assignment_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and pdb.DEFINED_BALANCE_ID = prb.DEFINED_BALANCE_ID
and pbt.balance_type_id = pdb.balance_type_id;

The new balance is not inserted into PAY_RUN_BALANCES.

Update SAVE_RUN_BALANCE column to Y in table PAY_DEFINED_BALANCES.

update pay_defined_balances
set SAVE_RUN_BALANCE = ‘Y’
where DEFINED_BALANCE_ID = :p_defined_balance_id;  /*get defined_balance_id from Q1*/

Re-query the Balance and click on Dimensions

The Status field will become Invalid

The Status will show as Invalid because of the below reason (Reference Doc ID 245295.1):

Submit the concurrent program Generate Run Balances

After successful completion of the program, Re-query the balance and click on Dimensions

The Status field will become Valid

 Rerun Payroll

Query the PAY_RUN_BALANCES table through the query Q2

The new balance is inserted into PAY_RUN_BALANCES.