VIEW: APPS.PAY_MX_EMPLOYEE_TAXES_V

Source

View Text - Preformatted

SELECT paa.assignment_id , paa.tax_unit_id , paa.assignment_action_id , paa.action_status , paa.payroll_action_id , ppa.action_type , ppa.effective_date date_paid , ppa.date_earned , ppa.business_group_id , paa.run_type_id , pbt.balance_type_id balance_type_id , pay_ac_utility.get_balance_name(pbt.balance_type_id) balance_name , pay_ac_utility.get_bal_reporting_name(pbt.balance_type_id) reporting_name , pbt.balance_uom , pay_ac_utility.get_value ( pbt.balance_type_id , '_ASG_GRE_RUN' , pbt.business_group_id , pbt.legislation_code , paa.assignment_action_id , paa.tax_unit_id , ppa.effective_date ) run_val , pay_ac_utility.get_value ( pbt.balance_type_id , '_ASG_GRE_YTD' , pbt.business_group_id , pbt.legislation_code , paa.assignment_action_id , paa.tax_unit_id , ppa.effective_date ) ytd_val from pay_payroll_actions ppa, pay_assignment_actions paa, pay_balance_types pbt, pay_defined_balances pdb, pay_balance_attributes pba, pay_bal_attribute_definitions pbad WHERE pbad.attribute_name = 'Employee Taxes' and pbad.business_group_id is null and pbad.legislation_code = 'MX' and pba.attribute_id = pbad.attribute_id and ( ( pba.business_group_id is null ) or ( pba.business_group_id is not null and pba.business_group_id = ppa.business_group_id ) ) and pdb.defined_balance_id = pba.defined_balance_id and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_type_id = pbt.balance_type_id and paa.payroll_action_id = ppa.payroll_action_id and exists ( select 1 from pay_run_balances prb where prb.assignment_id = paa.assignment_id and prb.tax_unit_id = paa.tax_unit_id and prb.effective_date between trunc(ppa.effective_date,'Y' ) and ppa.effective_date and prb.defined_balance_id = pba.defined_balance_id )

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE DATE_PAID
, PPA.DATE_EARNED
, PPA.BUSINESS_GROUP_ID
, PAA.RUN_TYPE_ID
, PBT.BALANCE_TYPE_ID BALANCE_TYPE_ID
, PAY_AC_UTILITY.GET_BALANCE_NAME(PBT.BALANCE_TYPE_ID) BALANCE_NAME
, PAY_AC_UTILITY.GET_BAL_REPORTING_NAME(PBT.BALANCE_TYPE_ID) REPORTING_NAME
, PBT.BALANCE_UOM
, PAY_AC_UTILITY.GET_VALUE ( PBT.BALANCE_TYPE_ID
, '_ASG_GRE_RUN'
, PBT.BUSINESS_GROUP_ID
, PBT.LEGISLATION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PPA.EFFECTIVE_DATE ) RUN_VAL
, PAY_AC_UTILITY.GET_VALUE ( PBT.BALANCE_TYPE_ID
, '_ASG_GRE_YTD'
, PBT.BUSINESS_GROUP_ID
, PBT.LEGISLATION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PPA.EFFECTIVE_DATE ) YTD_VAL
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_BALANCE_TYPES PBT
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_ATTRIBUTES PBA
, PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD
WHERE PBAD.ATTRIBUTE_NAME = 'EMPLOYEE TAXES'
AND PBAD.BUSINESS_GROUP_ID IS NULL
AND PBAD.LEGISLATION_CODE = 'MX'
AND PBA.ATTRIBUTE_ID = PBAD.ATTRIBUTE_ID
AND ( ( PBA.BUSINESS_GROUP_ID IS NULL ) OR ( PBA.BUSINESS_GROUP_ID IS NOT NULL
AND PBA.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID ) )
AND PDB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND EXISTS ( SELECT 1
FROM PAY_RUN_BALANCES PRB
WHERE PRB.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PRB.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'Y' )
AND PPA.EFFECTIVE_DATE
AND PRB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID )