View: PAY_CA_PROV_TAX_BAL_GRE_V

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

View Text

SELECT BAL.BALANCE_SET_NAME BALANCE_SET_NAME
, BAL.LEGISLATION_CODE LEGISLATION_CODE
, BAL.ATTRIBUTE_1 WAGE_BALANCE_NAME
, BAL.ATTRIBUTE_2 TAX_BALANCE_NAME
, BAL.ATTRIBUTE_3 DIMENSION
, BAL.ATTRIBUTE_4 REPORTING_NAME
, BAL.ATTRIBUTE_5 FORMAT_CODE
, TAX.TAX_UNIT_ID TAX_UNIT_ID
, TAX.NAME GRE_NAME
, OIN.ORG_INFORMATION4 TAX_GROUP_ID
, AOU.NAME TAX_GROUP_NAME
, TAX.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, OIN.ORG_INFORMATION1 BUSINESS_NO
, OIN.ORG_INFORMATION2 QUEBEC_EIN
, PRV.PROVINCE_NAME PROVINCE_NAME
, PRV.PROVINCE_ABBREV PROVINCE_CODE
, SES.SESSION_ID SESSION_ID
, SES.EFFECTIVE_DATE EFFECTIVE_DATE
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE (BAL.ATTRIBUTE_1
, 'PYDATE'
, SES.EFFECTIVE_DATE
, NULL
, NULL
, TAX.TAX_UNIT_ID
, PRV.PROVINCE_ABBREV
, NULL
, NULL
, NULL
, NULL) TAX_CTD_VALUE
, DECODE(BAL.ATTRIBUTE_2
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE (BAL.ATTRIBUTE_2
, 'PYDATE'
, SES.EFFECTIVE_DATE
, NULL
, NULL
, TAX.TAX_UNIT_ID
, PRV.PROVINCE_ABBREV
, NULL
, NULL
, NULL
, NULL)) WAGE_CTD_VALUE
, DECODE(BAL.ATTRIBUTE_3
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE (BAL.ATTRIBUTE_1
, BAL.ATTRIBUTE_3
, SES.EFFECTIVE_DATE
, NULL
, NULL
, TAX.TAX_UNIT_ID
, PRV.PROVINCE_ABBREV
, NULL
, NULL
, NULL
, NULL)) TAX_OTD_VALUE
, DECODE(BAL.ATTRIBUTE_3
, NULL
, 0
, DECODE(BAL.ATTRIBUTE_2
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE (BAL.ATTRIBUTE_2
, BAL.ATTRIBUTE_3
, SES.EFFECTIVE_DATE
, NULL
, NULL
, TAX.TAX_UNIT_ID
, PRV.PROVINCE_ABBREV
, NULL
, NULL
, NULL
, NULL))) WAGE_OTD_VALUE
FROM HR_ORGANIZATION_INFORMATION OIN
, HR_ALL_ORGANIZATION_UNITS AOU
, PAY_BALANCE_SETS BAL
, HR_CA_TAX_UNITS_V TAX
, PAY_CA_PROVINCES_V PRV
, FND_SESSIONS SES
WHERE TAX.TAX_UNIT_ID = OIN.ORGANIZATION_ID
AND OIN.ORG_INFORMATION_CONTEXT||'' = 'CANADA EMPLOYER IDENTIFICATION'
AND AOU.ORGANIZATION_ID (+) = OIN.ORG_INFORMATION4
AND BAL.LEGISLATION_CODE = 'CA'
AND ((SUBSTR(BAL.ATTRIBUTE_1
, 1
, 4) = 'PROV'
AND EXISTS (SELECT 1
FROM PAY_CA_LEGISLATION_INFO LGI
WHERE LGI.INFORMATION_TYPE = 'TAX_RELATIONSHIPS'
AND LGI.INFORMATION_VALUE = 'PRV'
AND LGI.JURISDICTION_CODE = PRV.PROVINCE_ABBREV
AND SES.EFFECTIVE_DATE BETWEEN LGI.START_DATE
AND LGI.END_DATE)) OR (SUBSTR(BAL.ATTRIBUTE_1
, 1
, 3) = 'QPP'
AND EXISTS (SELECT 1
FROM PAY_CA_LEGISLATION_INFO LGI2
WHERE LGI2.INFORMATION_TYPE = 'TAX_RELATIONSHIPS'
AND LGI2.INFORMATION_VALUE = 'QPP'
AND LGI2.JURISDICTION_CODE = PRV.PROVINCE_ABBREV
AND LGI2.JURISDICTION_CODE = 'QC'
AND SES.EFFECTIVE_DATE BETWEEN LGI2.START_DATE
AND LGI2.END_DATE)) OR ( SUBSTR(BAL.ATTRIBUTE_1
, 1
, 4) = 'PPIP'
AND EXISTS (SELECT 1
FROM PAY_CA_LEGISLATION_INFO LGI2
WHERE LGI2.INFORMATION_TYPE = 'TAX_RELATIONSHIPS'
AND LGI2.INFORMATION_VALUE = 'PPIP'
AND LGI2.JURISDICTION_CODE = PRV.PROVINCE_ABBREV
AND LGI2.JURISDICTION_CODE = 'QC'
AND SES.EFFECTIVE_DATE BETWEEN LGI2.START_DATE
AND LGI2.END_DATE)) )

Columns