VIEW: APPS.PAY_MX_EMPLOYEE_EARNINGS_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 earn_bal_type_id , pay_ac_utility.get_balance_name(pbt.balance_type_id) earn_bal_name , pay_ac_utility.get_bal_reporting_name(pbt.balance_type_id) earn_reporting_name , pbt.balance_uom earn_bal_uom , pay_mx_utility.get_days_bal_type_id(pbt.balance_type_id) days_bal_type_id , pay_ac_utility.get_balance_name( pay_mx_utility.get_days_bal_type_id(pbt.balance_type_id)) days_bal_name , pay_ac_utility.get_bal_reporting_name( pay_mx_utility.get_days_bal_type_id(pbt.balance_type_id)) days_reporting_name , 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 ) earn_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 ) earn_ytd_val , pay_ac_utility.get_value ( pay_mx_utility.get_days_bal_type_id(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 ) days_run_val , pay_ac_utility.get_value ( pay_mx_utility.get_days_bal_type_id(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 ) days_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 Earnings' 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 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 EARN_BAL_TYPE_ID
, PAY_AC_UTILITY.GET_BALANCE_NAME(PBT.BALANCE_TYPE_ID) EARN_BAL_NAME
, PAY_AC_UTILITY.GET_BAL_REPORTING_NAME(PBT.BALANCE_TYPE_ID) EARN_REPORTING_NAME
, PBT.BALANCE_UOM EARN_BAL_UOM
, PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(PBT.BALANCE_TYPE_ID) DAYS_BAL_TYPE_ID
, PAY_AC_UTILITY.GET_BALANCE_NAME( PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(PBT.BALANCE_TYPE_ID)) DAYS_BAL_NAME
, PAY_AC_UTILITY.GET_BAL_REPORTING_NAME( PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(PBT.BALANCE_TYPE_ID)) DAYS_REPORTING_NAME
, 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 ) EARN_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 ) EARN_YTD_VAL
, PAY_AC_UTILITY.GET_VALUE ( PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(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 ) DAYS_RUN_VAL
, PAY_AC_UTILITY.GET_VALUE ( PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(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 ) DAYS_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 EARNINGS'
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 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 )