VIEW: APPS.PAY_CA_SOE_SUMM_BALANCES_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id, paa.tax_unit_id, paa.assignment_id, hr_balances.decode_balance(pbt.balance_type_id), decode(pbt.balance_name, 'Involuntary Deductions', pycadar_pkg.get_labels('CA_ELEMENT_GROUP','OTHDED',pbttl.language), 'Voluntary Deductions', pycadar_pkg.get_labels('CA_ELEMENT_GROUP','OTHDED',pbttl.language), pbttl.reporting_name), ppa.action_type, decode(ppa.action_type, 'R', pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'RUN' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ) , 'Q', pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'RUN' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ) , 'V', pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'RUN' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ) , 'U', pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'CURRENT' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ) , 'P' , pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'CURRENT' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL )) amount_run, pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'CURRENT' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ), pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'QTD' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ), pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'PTD' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ), pay_ca_balance_pkg.call_ca_balance_get_value ( pbt.balance_name, 'YTD' , paa.assignment_action_id, paa.assignment_id , NULL , 'ASG' , paa.tax_unit_id, pbt.business_group_id, NULL ) amount_ytd, decode( pbt.balance_name, 'Gross Earnings', 1, 'Gross Pay', 2, 'Pre Tax Deductions', 3, 'Tax Deductions', 4, 'Involuntary Deductions', 5, 'Voluntary Deductions', 5, 'Payments', 6, 'Taxable Benefits', 7), pbt.balance_name base_bal_name FROM pay_balance_types pbt, pay_balance_types_tl pbttl, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.payroll_action_id = paa.payroll_action_id and ppa.action_type in ('Q', 'R', 'V', 'B', 'I') and pbt.legislation_code = 'CA' and pbt.balance_type_id = pbttl.balance_type_id AND pbttl.language = userenv('LANG') and pbt.balance_name in ('Gross Earnings', 'Gross Pay', 'Pre Tax Deductions', 'Tax Deductions', 'Involuntary Deductions', 'Voluntary Deductions', 'Payments', 'Taxable Benefits')

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ID
, HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)
, DECODE(PBT.BALANCE_NAME
, 'INVOLUNTARY DEDUCTIONS'
, PYCADAR_PKG.GET_LABELS('CA_ELEMENT_GROUP'
, 'OTHDED'
, PBTTL.LANGUAGE)
, 'VOLUNTARY DEDUCTIONS'
, PYCADAR_PKG.GET_LABELS('CA_ELEMENT_GROUP'
, 'OTHDED'
, PBTTL.LANGUAGE)
, PBTTL.REPORTING_NAME)
, PPA.ACTION_TYPE
, DECODE(PPA.ACTION_TYPE
, 'R'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, 'Q'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, 'V'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, 'U'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, 'P'
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )) AMOUNT_RUN
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'PTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL )
, PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE ( PBT.BALANCE_NAME
, 'YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, NULL
, 'ASG'
, PAA.TAX_UNIT_ID
, PBT.BUSINESS_GROUP_ID
, NULL ) AMOUNT_YTD
, DECODE( PBT.BALANCE_NAME
, 'GROSS EARNINGS'
, 1
, 'GROSS PAY'
, 2
, 'PRE TAX DEDUCTIONS'
, 3
, 'TAX DEDUCTIONS'
, 4
, 'INVOLUNTARY DEDUCTIONS'
, 5
, 'VOLUNTARY DEDUCTIONS'
, 5
, 'PAYMENTS'
, 6
, 'TAXABLE BENEFITS'
, 7)
, PBT.BALANCE_NAME BASE_BAL_NAME
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PBT.LEGISLATION_CODE = 'CA'
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PBT.BALANCE_NAME IN ('GROSS EARNINGS'
, 'GROSS PAY'
, 'PRE TAX DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'PAYMENTS'
, 'TAXABLE BENEFITS')