VIEW: APPS.HR_CA_TAX_UNITS_V

Source

View Text - Preformatted

SELECT O.ORGANIZATION_ID , O.BUSINESS_GROUP_ID , O.NAME , nvl(hoi2.org_information9,O.NAME) , O.DATE_FROM , O.DATE_TO , O.TYPE , O.INTERNAL_ADDRESS_LINE , O.COMMENTS , O.LOCATION_ID , L.ADDRESS_LINE_1 , L.ADDRESS_LINE_2 , L.ADDRESS_LINE_3 ,L.TELEPHONE_NUMBER_1, L.TOWN_OR_CITY , DECODE(L.STYLE , 'US' , L.REGION_2 , 'CA' , L.REGION_1 , 'CA_GLB' , L.REGION_1, ' ') , L.POSTAL_CODE , L.COUNTRY , pbg.legislation_code , hoi1.ORG_INFORMATION1 , hoi1.ORG_INFORMATION2 , hoi1.ORG_INFORMATION3 , hoi1.ORG_INFORMATION4 , hoi1.ORG_INFORMATION5 , hoi1.ORG_INFORMATION6 , hoi1.ORG_INFORMATION7 , hoi1.ORG_INFORMATION8 , hoi1.ORG_INFORMATION9 , hoi1.ORG_INFORMATION10 , hoi1.ORG_INFORMATION11 , hoi1.ORG_INFORMATION12 , hoi1.ORG_INFORMATION13 , hoi1.ORG_INFORMATION14 , hoi1.ORG_INFORMATION15 , hoi1.ORG_INFORMATION16 , hoi4.ORG_INFORMATION2 , hoi4.ORG_INFORMATION3 , hoi4.ORG_INFORMATION4 , hoi4.ORG_INFORMATION5 , hoi4.ORG_INFORMATION6 , hoi4.ORG_INFORMATION7 , hoi4.ORG_INFORMATION8 , hoi4.ORG_INFORMATION9 , hoi4.ORG_INFORMATION10 , hoi4.ORG_INFORMATION11 , hoi4.ORG_INFORMATION12 , hoi4.ORG_INFORMATION13 , hoi4.ORG_INFORMATION14 , hoi4.ORG_INFORMATION15 , hoi4.ORG_INFORMATION16 , hoi4.ORG_INFORMATION17 , hoi2.ORG_INFORMATION2 , hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION5,hoi4.org_information18,hoi4.org_information19 FROM HR_ALL_ORGANIZATION_UNITS_VL O , HR_ORGANIZATION_INFORMATION hoi , HR_ORGANIZATION_INFORMATION hoi1 , HR_ORGANIZATION_INFORMATION hoi2 , HR_ORGANIZATION_INFORMATION hoi3 , HR_ORGANIZATION_INFORMATION hoi4 , HR_LOCATIONS L , PER_BUSINESS_GROUPS pbg WHERE O.organization_id = hoi.organization_id AND pbg.business_group_id = o.business_group_id AND pbg.legislation_code = 'CA' AND hoi.ORG_INFORMATION_CONTEXT || '' = 'CLASS' AND hoi.ORG_INFORMATION1 = 'HR_LEGAL' AND hoi.ORG_INFORMATION2 = 'Y' AND L.LOCATION_ID(+) = O.LOCATION_ID AND O.ORGANIZATION_ID = hoi1.ORGANIZATION_ID (+) AND hoi1.ORG_INFORMATION_CONTEXT (+) || '' = 'Fed Magnetic Reporting' AND O.ORGANIZATION_ID = hoi2.ORGANIZATION_ID (+) AND hoi2.ORG_INFORMATION_CONTEXT (+) || '' = 'Canada Employer Identification' AND O.ORGANIZATION_ID = hoi3.ORGANIZATION_ID (+) AND hoi3.ORG_INFORMATION_CONTEXT (+) || '' = 'CLASS' AND hoi3.ORG_INFORMATION1(+) = 'HR_CA_PROV_REM_INFO' AND hoi3.ORG_INFORMATION2(+) = 'Y' AND O.ORGANIZATION_ID = hoi4.ORGANIZATION_ID (+) AND hoi4.ORG_INFORMATION_CONTEXT (+) || '' = 'Prov Reporting Est' AND hoi4.ORG_INFORMATION1(+) = 'QC'

View Text - HTML Formatted

SELECT O.ORGANIZATION_ID
, O.BUSINESS_GROUP_ID
, O.NAME
, NVL(HOI2.ORG_INFORMATION9
, O.NAME)
, O.DATE_FROM
, O.DATE_TO
, O.TYPE
, O.INTERNAL_ADDRESS_LINE
, O.COMMENTS
, O.LOCATION_ID
, L.ADDRESS_LINE_1
, L.ADDRESS_LINE_2
, L.ADDRESS_LINE_3
, L.TELEPHONE_NUMBER_1
, L.TOWN_OR_CITY
, DECODE(L.STYLE
, 'US'
, L.REGION_2
, 'CA'
, L.REGION_1
, 'CA_GLB'
, L.REGION_1
, ' ')
, L.POSTAL_CODE
, L.COUNTRY
, PBG.LEGISLATION_CODE
, HOI1.ORG_INFORMATION1
, HOI1.ORG_INFORMATION2
, HOI1.ORG_INFORMATION3
, HOI1.ORG_INFORMATION4
, HOI1.ORG_INFORMATION5
, HOI1.ORG_INFORMATION6
, HOI1.ORG_INFORMATION7
, HOI1.ORG_INFORMATION8
, HOI1.ORG_INFORMATION9
, HOI1.ORG_INFORMATION10
, HOI1.ORG_INFORMATION11
, HOI1.ORG_INFORMATION12
, HOI1.ORG_INFORMATION13
, HOI1.ORG_INFORMATION14
, HOI1.ORG_INFORMATION15
, HOI1.ORG_INFORMATION16
, HOI4.ORG_INFORMATION2
, HOI4.ORG_INFORMATION3
, HOI4.ORG_INFORMATION4
, HOI4.ORG_INFORMATION5
, HOI4.ORG_INFORMATION6
, HOI4.ORG_INFORMATION7
, HOI4.ORG_INFORMATION8
, HOI4.ORG_INFORMATION9
, HOI4.ORG_INFORMATION10
, HOI4.ORG_INFORMATION11
, HOI4.ORG_INFORMATION12
, HOI4.ORG_INFORMATION13
, HOI4.ORG_INFORMATION14
, HOI4.ORG_INFORMATION15
, HOI4.ORG_INFORMATION16
, HOI4.ORG_INFORMATION17
, HOI2.ORG_INFORMATION2
, HOI2.ORG_INFORMATION1
, HOI2.ORG_INFORMATION5
, HOI4.ORG_INFORMATION18
, HOI4.ORG_INFORMATION19
FROM HR_ALL_ORGANIZATION_UNITS_VL O
, HR_ORGANIZATION_INFORMATION HOI
, HR_ORGANIZATION_INFORMATION HOI1
, HR_ORGANIZATION_INFORMATION HOI2
, HR_ORGANIZATION_INFORMATION HOI3
, HR_ORGANIZATION_INFORMATION HOI4
, HR_LOCATIONS L
, PER_BUSINESS_GROUPS PBG
WHERE O.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND PBG.BUSINESS_GROUP_ID = O.BUSINESS_GROUP_ID
AND PBG.LEGISLATION_CODE = 'CA'
AND HOI.ORG_INFORMATION_CONTEXT || '' = 'CLASS'
AND HOI.ORG_INFORMATION1 = 'HR_LEGAL'
AND HOI.ORG_INFORMATION2 = 'Y'
AND L.LOCATION_ID(+) = O.LOCATION_ID
AND O.ORGANIZATION_ID = HOI1.ORGANIZATION_ID (+)
AND HOI1.ORG_INFORMATION_CONTEXT (+) || '' = 'FED MAGNETIC REPORTING'
AND O.ORGANIZATION_ID = HOI2.ORGANIZATION_ID (+)
AND HOI2.ORG_INFORMATION_CONTEXT (+) || '' = 'CANADA EMPLOYER IDENTIFICATION'
AND O.ORGANIZATION_ID = HOI3.ORGANIZATION_ID (+)
AND HOI3.ORG_INFORMATION_CONTEXT (+) || '' = 'CLASS'
AND HOI3.ORG_INFORMATION1(+) = 'HR_CA_PROV_REM_INFO'
AND HOI3.ORG_INFORMATION2(+) = 'Y'
AND O.ORGANIZATION_ID = HOI4.ORGANIZATION_ID (+)
AND HOI4.ORG_INFORMATION_CONTEXT (+) || '' = 'PROV REPORTING EST'
AND HOI4.ORG_INFORMATION1(+) = 'QC'