VIEW: APPS.PAY_US_STATE_TAXES_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, 'CURRENT', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) CURRENT_VAL , 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 , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PUTB.BALANCE_CATEGORY_CODE, PUTT.TAX_TYPE_CODE, PUTB.EE_OR_ER_CODE, 'QTD', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) QTD_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PUTB.BALANCE_CATEGORY_CODE, PUTT.TAX_TYPE_CODE, PUTB.EE_OR_ER_CODE, 'YTD', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) YTD_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 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 EXISTS ( SELECT 'x' 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.jurisdiction_comp1 = 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
, 'CURRENT'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) CURRENT_VAL
, 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
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PUTB.BALANCE_CATEGORY_CODE
, PUTT.TAX_TYPE_CODE
, PUTB.EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PUTB.BALANCE_CATEGORY_CODE
, PUTT.TAX_TYPE_CODE
, PUTB.EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) YTD_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
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 EXISTS ( SELECT 'X'
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.JURISDICTION_COMP1 = STATE.STATE_CODE
AND PRB.DEFINED_BALANCE_ID = PDB.DEFINED_BALANCE_ID )