How to Develop a Salary Change Report in HCM Fusion R13

How to Develop a Salary Change Report in HCM Fusion R13
----------------------------------------------------------------------

Lets start with Master Tables, which has Data Elements related to identifying salary.

Table: pay_balance_types
       -> Pass:  BASE_BALANCE_NAME = 'XX_GROSS_SALARY_NPV'
       -> Get :  BALANCE_TYPE_ID -> 300000006261657
     
Table: pay_balance_dimensions:
       -> Pass : base_dimension_name = 'Core Assignment Run'
       -> Get balance_dimension_id -> 300000000809093

Table: pay_defined_balances
       -> Pass [JOIN balance_dimension_id AND BALANCE_TYPE_ID]
       -> Get : defined_balance_id

Table: Pay_run_balances: this table contains person specfic balances for payroll runs
       -> Pass: defined_balance_id : [filter data based on base balance and dimension]
       -> Pass: other parameters like payroll_relationship_id, and pay_rel_action_id
     
--------------
To filter data based on person: Lets start from person details

Table: per_all_people_f : pass person_number -> get person_id

Table: per_all_assignments_f : pass person_id and -> get assignment_id [filter: primary_flag =Y and record is active]

Table: Pay_pay_relationships_dn [pprd]: 1 record: specific to person and non-trx table
       -> Pass: Person_id
       -> Get:  payroll_relationship_id

Table: pay_payroll_rel_actions [ppra] : This table contains details of the individuals processed in a payroll process: more than 1 record for 1 person as its based on payroll ran 
       -> Pass: Payroll_relationship_id
       -> get payroll_action_id -> specif to a person_id
     
Table: pay_payroll_actions
       -> Pass: payroll_action_id
       -> Pass: action_types [Q,R]
       -> you may get multiple records of person should join with pay_time_periods with EARN_TIME_PERIOD_ID
       -> get PAYROLL_ID, EARN_TIME_PERIOD_ID, PAYROLL_ACTION_ID

Table: pay_time_periods     
       -> Pass time_period_id , payroll_action_id

---------------
again come to Table: Pay_run_balances:

-> you have : defined_balance_id
            : payroll_relationship_id
            : payroll_action_id
            : pay_rel_action_id


-> after passing above: you will get balance value for the period.

Oracle Reports: Bind Vs Lexical, Summary, PlaceHolder, Formula Columns

Bind Vs Lexicals, Summary, PlaceHolder, Formula Columns

Bind-Lexical Parameters in Report:
-----------------------------
bind parameters: input dept number at runtime: bind parameters. prefix with colon :
lexical parameters: input conditions at run time., prefixed with (ampersand) &

summary columns and formula columns in Report:
----------------------------------------------
Each of below can be used two ways: inside a group, or outside a group
                                Inside the group: repeated for each row
                                Outside Group: Once for report

Summary Columns: [CS_] are used for calculating aggregates: sum, min, max [these are predefined]
Formula Columns: [CF_] If you want to sum salary and commision , or any calculated value based on formula:
PlaceHolder Column: [CP_]: Placeholder Columns acts like a variable. 



Ex: find the name of the employee earning the maximum salary. 
---------------------------------------------------------------
Add a summary col outside group: Maximum salary can be stored in CS_MaxSal (Summary col),
Add a placeholder column outside group to hold employee name. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY = :CS_MaxSal THEN
    :CP_1 := :EMPLOYEE_NAME
END IF;
RETURN (:CP_1)   



Ex2: find the Total Salary , where salary > 10000. Only salary > 10000 we should add
-----------------------------------------------------------------------------------
Add a placeholder column outside group to hold Total Salary. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY > 10000 THEN
    :CP_1 := NVL(:CP_1,0) + :SALARY
END IF;
RETURN (:CP_1)