View: PAY_US_LOCAL_TAXES_RBR_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_US_LOCAL_TAXES_RBR_V

View Text

SELECT /*+ ORDERED */PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, CITY.JURISDICTION_CODE
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, NAMES.CITY_NAME
, NAMES.CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, NAMES.STATE_CODE
, 'INVALID' COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, NAMES.COUNTY_CODE
, TAX_TYPE_CODE || '-' || DECODE(TAX_TYPE_CODE
, 'CITY'
, NAMES.CITY_NAME
, 'HT'
, NAMES.CITY_NAME
, CITY.JURISDICTION_CODE) LOCAL_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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, CITY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, CITY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, CITY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, CITY.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_EMP_CITY_TAX_RULES_F CITY
, PAY_US_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_CITY_NAMES NAMES
, PAY_US_CITY_TAX_INFO_F CITF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN CITY.EFFECTIVE_START_DATE
AND CITY.EFFECTIVE_END_DATE
AND CITY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND NAMES.CITY_CODE = CITY.CITY_CODE
AND NAMES.COUNTY_CODE = CITY.COUNTY_CODE
AND NAMES.STATE_CODE = CITY.STATE_CODE
AND NAMES.PRIMARY_FLAG = 'Y'
AND CITF.JURISDICTION_CODE = CITY.JURISDICTION_CODE
AND DECODE(TAX_TYPE_CODE
, 'CITY'
, CITF.CITY_TAX
, 'HT'
, CITF.HEAD_TAX
, 'N') = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN CITF.EFFECTIVE_START_DATE
AND CITF.EFFECTIVE_END_DATE
AND TAX_TYPE_CODE IN ('CITY'
, 'HT')
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
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_CODE = CITY.JURISDICTION_CODE ) UNION ALL SELECT /*+ ORDERED */ PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, CNTY.JURISDICTION_CODE
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, 'INVALID' CITY_NAME
, 'INVALID' CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, NAMES.STATE_CODE
, NAMES.COUNTY_NAME COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, NAMES.COUNTY_CODE
, TAX_TYPE_CODE || '-' || DECODE(TAX_TYPE_CODE
, 'COUNTY'
, NAMES.COUNTY_NAME
, CNTY.JURISDICTION_CODE) LOCAL_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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, CNTY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, CNTY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, CNTY.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, CNTY.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_EMP_COUNTY_TAX_RULES_F CNTY
, PAY_US_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_COUNTY_TAX_INFO_F CTIF
, PAY_US_COUNTIES NAMES
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE BETWEEN CNTY.EFFECTIVE_START_DATE
AND CNTY.EFFECTIVE_END_DATE
AND CNTY.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND NAMES.COUNTY_CODE = CNTY.COUNTY_CODE
AND NAMES.STATE_CODE = CNTY.STATE_CODE
AND TAX_TYPE_CODE = 'COUNTY'
AND CTIF.JURISDICTION_CODE = CNTY.JURISDICTION_CODE
AND CTIF.COUNTY_TAX = 'Y'
AND PPA.EFFECTIVE_DATE BETWEEN CTIF.EFFECTIVE_START_DATE
AND CTIF.EFFECTIVE_END_DATE
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
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_CODE||'-0000' = CNTY.JURISDICTION_CODE ) UNION ALL SELECT /*+ ORDERED */ PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, SCHOOL.JURISDICTION_CODE
, PUTT.TAX_TYPE_ID
, EE_OR_ER_CODE
, LIMIT_TAX_FLAG
, TAX_DOMAIN_CODE
, TAX_TYPE_CODE
, BALANCE_CATEGORY_CODE
, USER_REPORTING_NAME
, PPA.BUSINESS_GROUP_ID
, 'INVALID' SCHOOL_DISTRICT_NAME
, 'INVALID' SCHOOL_DISTRICT_CODE
, 'INVALID' CITY_NAME
, 'INVALID' CITY_CODE
, 'INVALID' STATE_NAME
, 'INVALID' STATE_ABBREV
, 'INVALID' STATE_CODE
, 'INVALID' COUNTY_NAME
, 'INVALID' COUNTY_ABBREV
, 'INVALID' COUNTY_CODE
, TAX_TYPE_CODE || '-' || SCHOOL.JURISDICTION_CODE LOCAL_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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'CURRENT'
, PAA.TAX_UNIT_ID
, SCHOOL.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'RUN'
, PAA.TAX_UNIT_ID
, SCHOOL.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'QTD'
, PAA.TAX_UNIT_ID
, SCHOOL.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 ( BALANCE_CATEGORY_CODE
, TAX_TYPE_CODE
, EE_OR_ER_CODE
, 'YTD'
, PAA.TAX_UNIT_ID
, SCHOOL.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_TAX_TYPES PUTT
, PAY_US_TAX_BALANCES PUTB
, PAY_US_ASG_REPORTING SCHOOL
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND TAX_TYPE_CODE = 'SCHOOL'
AND SCHOOL.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND LENGTH(SCHOOL.JURISDICTION_CODE) = 8
AND SCHOOL.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND PUTT.TAX_TYPE_ID = PUTB.TAX_TYPE_ID
AND PUTT.TAX_DOMAIN_CODE = 'LOCALITY'
AND PUTB.BALANCE_CATEGORY_CODE = 'WITHHELD'
AND PUTB.EE_OR_ER_CODE = 'EE'
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_CODE = SCHOOL.JURISDICTION_CODE )

Columns