VIEW: APPS.PAY_AU_BALANCES_MATRIX_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id , pbt.balance_name , pbt.balance_type_id , paa.payroll_action_id , paa.assignment_id , hr_aubal.calc_asg_run_action( paa.assignment_action_id, pbt.balance_type_id ) asg_run_bal , hr_aubal.calc_asg_ptd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_ptd_bal , hr_aubal.calc_asg_mtd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_mtd_bal , hr_aubal.calc_asg_qtd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_qtd_bal , hr_aubal.calc_asg_ytd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_ytd_bal , hr_aubal.calc_asg_fy_ytd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_fy_ytd_bal , hr_aubal.calc_asg_fy_qtd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_fy_qtd_bal , hr_aubal.calc_asg_cal_ytd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_cal_ytd_bal , hr_aubal.calc_asg_fy_ytd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_fbt_ytd_bal , hr_aubal.calc_asg_fy_qtd_action( paa.assignment_action_id, pbt.balance_type_id ) asg_fbt_qtd_bal , hr_aubal.calc_asg_td_action( paa.assignment_action_id, pbt.balance_type_id ) asg_td_bal from pay_balance_types pbt , pay_assignment_actions paa , pay_run_result_values prrv , pay_run_results prr , pay_balance_feeds_f feed , pay_assignment_actions paa2 WHERE ( pbt.legislation_code is null or pbt.legislation_code = 'AU') and pbt.balance_type_id = feed.balance_type_id and prr.run_result_id = prrv.run_result_id and feed.input_value_id = prrv.input_value_id and prrv.result_value is not null and prrv.result_value <> '0' and paa2.action_sequence <= paa.action_sequence and paa2.assignment_action_id = prr.assignment_action_id and paa2.assignment_id = paa.assignment_id group by paa.assignment_action_id , pbt.balance_name , pbt.balance_type_id , paa.payroll_action_id , paa.assignment_id

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ID
, HR_AUBAL.CALC_ASG_RUN_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_RUN_BAL
, HR_AUBAL.CALC_ASG_PTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_PTD_BAL
, HR_AUBAL.CALC_ASG_MTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_MTD_BAL
, HR_AUBAL.CALC_ASG_QTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_QTD_BAL
, HR_AUBAL.CALC_ASG_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_YTD_BAL
, HR_AUBAL.CALC_ASG_FY_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_FY_YTD_BAL
, HR_AUBAL.CALC_ASG_FY_QTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_FY_QTD_BAL
, HR_AUBAL.CALC_ASG_CAL_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_CAL_YTD_BAL
, HR_AUBAL.CALC_ASG_FY_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_FBT_YTD_BAL
, HR_AUBAL.CALC_ASG_FY_QTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_FBT_QTD_BAL
, HR_AUBAL.CALC_ASG_TD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_TD_BAL
FROM PAY_BALANCE_TYPES PBT
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS PAA2
WHERE ( PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'AU')
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE <> '0'
AND PAA2.ACTION_SEQUENCE <= PAA.ACTION_SEQUENCE
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ID