VIEW: APPS.PAY_ZA_BALANCES_MATRIX_V

Source

View Text - Preformatted

SELECT PAA.ASSIGNMENT_ID, PBT.BALANCE_NAME, py_za_bal.calc_asg_run_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_RUN_BAL, py_za_bal.calc_asg_tax_ptd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_TAX_PTD_BAL, py_za_bal.calc_asg_tax_mtd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_TAX_MTD_BAL, py_za_bal.calc_asg_tax_qtd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_TAX_QTD_BAL, py_za_bal.calc_asg_tax_ytd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_TAX_YTD_BAL, py_za_bal.calc_asg_cal_ytd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_CAL_YTD_BAL, py_za_bal.calc_asg_itd_action( PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID ) ASG_ITD_BAL, PAA.ASSIGNMENT_ACTION_ID, PBT.BALANCE_TYPE_ID, PAA.PAYROLL_ACTION_ID FROM PAY_BALANCE_TYPES PBT, PAY_ASSIGNMENT_ACTIONS PAA WHERE (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'ZA') AND EXISTS( SELECT NULL FROM PAY_INPUT_VALUES_F PIV, PAY_RUN_RESULTS PRR , PAY_BALANCE_FEEDS_F FEED , PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_PAYROLL_ACTIONS PPA WHERE PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID AND PRR.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID AND PPA.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND FEED.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PAA2.ASSIGNMENT_ACTION_ID <= PAA.ASSIGNMENT_ACTION_ID AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID)

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID
, PBT.BALANCE_NAME
, PY_ZA_BAL.CALC_ASG_RUN_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_RUN_BAL
, PY_ZA_BAL.CALC_ASG_TAX_PTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_TAX_PTD_BAL
, PY_ZA_BAL.CALC_ASG_TAX_MTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_TAX_MTD_BAL
, PY_ZA_BAL.CALC_ASG_TAX_QTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_TAX_QTD_BAL
, PY_ZA_BAL.CALC_ASG_TAX_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_TAX_YTD_BAL
, PY_ZA_BAL.CALC_ASG_CAL_YTD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_CAL_YTD_BAL
, PY_ZA_BAL.CALC_ASG_ITD_ACTION( PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID ) ASG_ITD_BAL
, PAA.ASSIGNMENT_ACTION_ID
, PBT.BALANCE_TYPE_ID
, PAA.PAYROLL_ACTION_ID
FROM PAY_BALANCE_TYPES PBT
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'ZA')
AND EXISTS( SELECT NULL
FROM PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULTS PRR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA
WHERE PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PPA.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND FEED.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PAA2.ASSIGNMENT_ACTION_ID <= PAA.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID)