ORA_HRX_CA_WC_ACCOUNT

Details

Schema: FUSION
Object owner: HRX
Object type: VIEW

Columns

Name
CODE MEANING ORGANIZATION_ID PROVINCE EFFECTIVE_START_DATE EFFECTIVE_END_DATE

Name

CODE

MEANING

ORGANIZATION_ID

PROVINCE

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement
SELECT a.CODE,a.MEANING,a.ORGANIZATION_ID,a.PROVINCE,min(a.effective_start_date) EFFECTIVE_START_DATE,max(a.effective_end_date) EFFECTIVE_END_DATE FROM (SELECT DISTINCT h.org_information3 CODE, us.IDENTIFIER_VALUE\|\|' ,h.organization_id ,h1.ORG_INFORMATION_NUMBER1 PROVINCE ,h.effective_start_date ,h.effective_end_date FROM HR_ORGANIZATION_INFORMATION_F h,HR_ORGANIZATION_INFORMATION_F h1,HZ_GEOGRAPHIES G, HZ_GEOGRAPHY_IDENTIFIERS us,HZ_GEOGRAPHY_IDENTIFIERS gc WHERE h.ORGANIZATION_ID =h1.ORGANIZATION_ID AND h1.ORG_INFORMATION_CONTEXT = (SELECT decode(h1.ORG_INFORMATION_NUMBER1, 1,'ORA_HRX_CA_PSU_WC_AB_HEADER',3,'ORA_HRX_CA_PSU_WC_BC_HEADER', 5,'ORA_HRX_CA_PSU_WC_MB_HEADER',7,'ORA_HRX_CA_PSU_WC_NB_HEADER',9,'ORA_HRX_CA_PSU_WC_NL_HEADER', 11,'ORA_HRX_CA_PSU_WC_NT_HEADER',13,'ORA_HRX_CA_PSU_WC_NS_HEADER',15,'ORA_HRX_CA_PSU_WC_ON_HEADER', 17,'ORA_HRX_CA_PSU_WC_PE_HEADER',19,'ORA_HRX_CA_PSU_WC_QC_HEADER',21,'ORA_HRX_CA_PSU_WC_SK_HEADER', 23,'ORA_HRX_CA_PSU_WC_YT_HEADER',25,'ORA_HRX_CA_PSU_WC_NU_HEADER') from dual) AND h.ORG_INFORMATION_CONTEXT = REPLACE(h1.org_information_context,'HEADER','DETAIL') AND G.GEOGRAPHY_ID = us.GEOGRAPHY_ID AND G.COUNTRY_CODE = 'CA' AND G.GEOGRAPHY_TYPE = 'PROVINCE' AND G.GEOGRAPHY_USE = 'MASTER_REF' AND us.geography_id = gc.geography_id AND us.GEOGRAPHY_ID IN (SELECT GEOGRAPHY_ID FROM HZ_GEOGRAPHIES where COUNTRY_CODE = 'CA' AND GEOGRAPHY_TYPE = 'PROVINCE' AND GEOGRAPHY_USE = 'MASTER_REF') AND us.IDENTIFIER_SUBTYPE = 'STANDARD_NAME' AND us.IDENTIFIER_TYPE = 'NAME' AND us.PRIMARY_FLAG = 'Y' AND gc.IDENTIFIER_TYPE = 'CODE' AND gc.GEOGRAPHY_USE = 'MASTER_REF' AND gc.IDENTIFIER_SUBTYPE = 'GEO_CODE' AND gc.IDENTIFIER_VALUE = '70-'\|\|TO_CHAR(h1.ORG_INFORMATION_NUMBER1)\|\|'-0') a GROUP BY a.code,meaning,a.organization_id,a.province

SQL_Statement

SELECT a.CODE,a.MEANING,a.ORGANIZATION_ID,a.PROVINCE,min(a.effective_start_date) EFFECTIVE_START_DATE,max(a.effective_end_date) EFFECTIVE_END_DATE FROM

(SELECT DISTINCT

h.org_information3 CODE,

us.IDENTIFIER_VALUE||'

,h.organization_id

,h1.ORG_INFORMATION_NUMBER1 PROVINCE

,h.effective_start_date

,h.effective_end_date

FROM HR_ORGANIZATION_INFORMATION_F h,HR_ORGANIZATION_INFORMATION_F h1,HZ_GEOGRAPHIES G, HZ_GEOGRAPHY_IDENTIFIERS us,HZ_GEOGRAPHY_IDENTIFIERS gc

WHERE h.ORGANIZATION_ID =h1.ORGANIZATION_ID

AND h1.ORG_INFORMATION_CONTEXT = (SELECT decode(h1.ORG_INFORMATION_NUMBER1, 1,'ORA_HRX_CA_PSU_WC_AB_HEADER',3,'ORA_HRX_CA_PSU_WC_BC_HEADER',

5,'ORA_HRX_CA_PSU_WC_MB_HEADER',7,'ORA_HRX_CA_PSU_WC_NB_HEADER',9,'ORA_HRX_CA_PSU_WC_NL_HEADER',

11,'ORA_HRX_CA_PSU_WC_NT_HEADER',13,'ORA_HRX_CA_PSU_WC_NS_HEADER',15,'ORA_HRX_CA_PSU_WC_ON_HEADER',

17,'ORA_HRX_CA_PSU_WC_PE_HEADER',19,'ORA_HRX_CA_PSU_WC_QC_HEADER',21,'ORA_HRX_CA_PSU_WC_SK_HEADER',

23,'ORA_HRX_CA_PSU_WC_YT_HEADER',25,'ORA_HRX_CA_PSU_WC_NU_HEADER') from dual)

AND h.ORG_INFORMATION_CONTEXT = REPLACE(h1.org_information_context,'HEADER','DETAIL')

AND G.GEOGRAPHY_ID = us.GEOGRAPHY_ID

AND G.COUNTRY_CODE = 'CA'

AND G.GEOGRAPHY_TYPE = 'PROVINCE'

AND G.GEOGRAPHY_USE = 'MASTER_REF'

AND us.geography_id = gc.geography_id

AND us.GEOGRAPHY_ID IN (SELECT GEOGRAPHY_ID FROM HZ_GEOGRAPHIES

where COUNTRY_CODE = 'CA'

AND GEOGRAPHY_TYPE = 'PROVINCE'

AND GEOGRAPHY_USE = 'MASTER_REF')

AND us.IDENTIFIER_SUBTYPE = 'STANDARD_NAME'

AND us.IDENTIFIER_TYPE = 'NAME'

AND us.PRIMARY_FLAG = 'Y'

AND gc.IDENTIFIER_TYPE = 'CODE'

AND gc.GEOGRAPHY_USE = 'MASTER_REF'

AND gc.IDENTIFIER_SUBTYPE = 'GEO_CODE'

AND gc.IDENTIFIER_VALUE = '70-'||TO_CHAR(h1.ORG_INFORMATION_NUMBER1)||'-0') a

GROUP BY a.code,meaning,a.organization_id,a.province