VIEW: APPS.PAY_US_STATE_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, STATE.JURISDICTION_CODE, PETV.TAX_TYPE_ID, PETV.EE_OR_ER_CODE, PETV.LIMIT_TAX_FLAG, PETV.TAX_DOMAIN_CODE, PETV.TAX_TYPE_CODE, PETV.BALANCE_CATEGORY_CODE, PETV.USER_REPORTING_NAME, PETV.ELEMENT_NAME, PETV.ELEMENT_TYPE_ID, PETV.EFFECTIVE_START_DATE, PETV.EFFECTIVE_END_DATE, PETV.BUSINESS_GROUP_ID, PETV.LEGISLATION_CODE, PETV.PROCESSING_PRIORITY, NAMES.STATE_ABBREV, NAMES.STATE_NAME, PETV.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 ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.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 ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.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 ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PTD', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'PYDATE', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) PYDATE_VAL , PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.EE_OR_ER_CODE, 'MONTH', PAA.TAX_UNIT_ID, STATE.JURISDICTION_CODE, PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.PAYROLL_ACTION_ID) MONTH_VAL, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.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 ( PETV.BALANCE_CATEGORY_CODE, PETV.TAX_TYPE_CODE, PETV.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_US_STATE_TAX_TYPES_V PETV, PAY_US_STATES NAMES, PAY_US_EMP_STATE_TAX_RULES_F STATE WHERE STATE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PPA.effective_date BETWEEN STATE.effective_start_date AND STATE.effective_end_date AND NAMES.STATE_CODE = SUBSTR(STATE.JURISDICTION_CODE,1,2) AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('Q', 'R', 'V', 'B', 'I') AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE AND PETV.EFFECTIVE_END_DATE AND PAY_US_TAX_INFO_PKG.GET_TAX_EXIST(PETV.TAX_TYPE_CODE, STATE.JURISDICTION_CODE, PETV.EE_OR_ER_CODE, PPA.EFFECTIVE_DATE) = 'Y' AND EXISTS (SELECT 'x' FROM PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASG, PAY_RUN_RESULTS RR, PAY_ELEMENT_TYPES_F PET2 WHERE RR.ELEMENT_TYPE_ID +0 = PET2.element_type_id AND RR.JURISDICTION_CODE = STATE.jurisdiction_code AND PETV.tax_type_code = decode(PET2.element_name, 'Workers Compensation EE', 'WC', 'Workers Compensation2 EE', 'WC2', 'STEIC_RS', 'STEIC', 'STEIC_WK','STEIC', 'SUI1_EE', 'SUI1', 'SDI1_EE', 'SDI1', substr(PET2.element_name,1,3)) AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE,'YEAR') AND PPA.EFFECTIVE_DATE AND PACT.ACTION_TYPE IN ('R', 'Q', 'B') /* Added 'B' for Bug 12970173 */ )

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
, PETV.TAX_TYPE_ID
, PETV.EE_OR_ER_CODE
, PETV.LIMIT_TAX_FLAG
, PETV.TAX_DOMAIN_CODE
, PETV.TAX_TYPE_CODE
, PETV.BALANCE_CATEGORY_CODE
, PETV.USER_REPORTING_NAME
, PETV.ELEMENT_NAME
, PETV.ELEMENT_TYPE_ID
, PETV.EFFECTIVE_START_DATE
, PETV.EFFECTIVE_END_DATE
, PETV.BUSINESS_GROUP_ID
, PETV.LEGISLATION_CODE
, PETV.PROCESSING_PRIORITY
, NAMES.STATE_ABBREV
, NAMES.STATE_NAME
, PETV.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 ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.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 ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.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 ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PTD'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'PYDATE'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.EE_OR_ER_CODE
, 'MONTH'
, PAA.TAX_UNIT_ID
, STATE.JURISDICTION_CODE
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.PAYROLL_ACTION_ID) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_TAX_BALANCE_VM ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.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 ( PETV.BALANCE_CATEGORY_CODE
, PETV.TAX_TYPE_CODE
, PETV.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_US_STATE_TAX_TYPES_V PETV
, PAY_US_STATES NAMES
, PAY_US_EMP_STATE_TAX_RULES_F STATE
WHERE STATE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN STATE.EFFECTIVE_START_DATE
AND STATE.EFFECTIVE_END_DATE
AND NAMES.STATE_CODE = SUBSTR(STATE.JURISDICTION_CODE
, 1
, 2)
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PETV.EFFECTIVE_START_DATE
AND PETV.EFFECTIVE_END_DATE
AND PAY_US_TAX_INFO_PKG.GET_TAX_EXIST(PETV.TAX_TYPE_CODE
, STATE.JURISDICTION_CODE
, PETV.EE_OR_ER_CODE
, PPA.EFFECTIVE_DATE) = 'Y'
AND EXISTS (SELECT 'X'
FROM PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
, PAY_ELEMENT_TYPES_F PET2
WHERE RR.ELEMENT_TYPE_ID +0 = PET2.ELEMENT_TYPE_ID
AND RR.JURISDICTION_CODE = STATE.JURISDICTION_CODE
AND PETV.TAX_TYPE_CODE = DECODE(PET2.ELEMENT_NAME
, 'WORKERS COMPENSATION EE'
, 'WC'
, 'WORKERS COMPENSATION2 EE'
, 'WC2'
, 'STEIC_RS'
, 'STEIC'
, 'STEIC_WK'
, 'STEIC'
, 'SUI1_EE'
, 'SUI1'
, 'SDI1_EE'
, 'SDI1'
, SUBSTR(PET2.ELEMENT_NAME
, 1
, 3))
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q'
, 'B') /* ADDED 'B' FOR BUG 12970173 */ )