VIEW: APPS.PAY_CA_PROV_TAX_BAL_GRE_V

Source

View Text - Preformatted

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)) )

View Text - HTML Formatted

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)) )