VIEW: APPS.PAY_US_ASG_RUN_STATE_TAX_RBR_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, state.jurisdiction_code, putt.tax_type_id, putb.ee_or_er_code, putt.limit_tax_flag, putt.tax_domain_code, putt.tax_type_code, putb.balance_category_code, putb.user_reporting_name, ppa.business_group_id, names.state_abbrev, names.state_name, putt.tax_type_code || '-' || names.state_abbrev state_tax_name, pay_us_balance_view_pkg.get_view_mode view_mode, pay_us_balance_view_pkg.get_calc_all_timetypes_flag calc_all_timetypes, pay_us_taxbal_view_pkg.us_tax_balance_vm (putb.balance_category_code, putt.tax_type_code, putb.ee_or_er_code, 'RUN', paa.tax_unit_id, state.jurisdiction_code, paa.assignment_action_id, paa.assignment_id, ppa.date_earned, paa.payroll_action_id ) run_val FROM pay_assignment_actions paa, pay_payroll_actions ppa, pay_bal_attribute_definitions pbad, pay_balance_attributes pba, pay_defined_balances pdb, pay_us_tax_balances putb, pay_us_tax_types putt, pay_us_emp_state_tax_rules_f state, pay_us_states names, pay_run_balances prb WHERE pbad.attribute_name = 'PAY_US_TAX_DEDN_STATE' AND pbad.attribute_id = pba.attribute_id AND pdb.defined_balance_id = pba.defined_balance_id AND putb.balance_type_id = pdb.balance_type_id AND state.assignment_id = paa.assignment_id AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date AND names.state_code = state.state_code AND ppa.payroll_action_id = paa.payroll_action_id AND putt.tax_type_id = putb.tax_type_id AND putt.tax_domain_code = 'STATE' AND putb.balance_category_code IN ('WITHHELD', 'ADVANCED') AND putb.ee_or_er_code = 'EE' AND pay_us_tax_info_pkg.get_tax_exist (putt.tax_type_code, state.jurisdiction_code, putb.ee_or_er_code, ppa.effective_date ) = 'Y' AND prb.assignment_action_id = paa.assignment_action_id AND prb.tax_unit_id = paa.tax_unit_id AND SUBSTR(prb.jurisdiction_code, 1, 2) = state.state_code AND prb.defined_balance_id = pdb.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
, STATE.JURISDICTION_CODE
, PUTT.TAX_TYPE_ID
, PUTB.EE_OR_ER_CODE
, PUTT.LIMIT_TAX_FLAG
, PUTT.TAX_DOMAIN_CODE
, PUTT.TAX_TYPE_CODE
, PUTB.BALANCE_CATEGORY_CODE
, PUTB.USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, NAMES.STATE_ABBREV
, NAMES.STATE_NAME
, PUTT.TAX_TYPE_CODE || '-' || NAMES.STATE_ABBREV STATE_TAX_NAME
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM (PUTB.BALANCE_CATEGORY_CODE
, PUTT.TAX_TYPE_CODE
, PUTB.EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID ) RUN_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BAL_ATTRIBUTE_DEFINITIONS PBAD
, PAY_BALANCE_ATTRIBUTES PBA
, PAY_DEFINED_BALANCES PDB
, PAY_US_TAX_BALANCES PUTB
, PAY_US_TAX_TYPES PUTT
, PAY_US_EMP_STATE_TAX_RULES_F STATE
, PAY_US_STATES NAMES
, PAY_RUN_BALANCES PRB
WHERE PBAD.ATTRIBUTE_NAME = 'PAY_US_TAX_DEDN_STATE'
AND PBAD.ATTRIBUTE_ID = PBA.ATTRIBUTE_ID
AND PDB.DEFINED_BALANCE_ID = PBA.DEFINED_BALANCE_ID
AND PUTB.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND STATE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN STATE.EFFECTIVE_START_DATE
AND STATE.EFFECTIVE_END_DATE
AND NAMES.STATE_CODE = STATE.STATE_CODE
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'STATE'
AND PUTB.BALANCE_CATEGORY_CODE IN ('WITHHELD'
, 'ADVANCED')
AND PUTB.EE_OR_ER_CODE = 'EE'
AND PAY_US_TAX_INFO_PKG.GET_TAX_EXIST (PUTT.TAX_TYPE_CODE
, STATE.JURISDICTION_CODE
, PUTB.EE_OR_ER_CODE
, PPA.EFFECTIVE_DATE ) = 'Y'
AND PRB.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRB.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND SUBSTR(PRB.JURISDICTION_CODE
, 1
, 2) = STATE.STATE_CODE
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID