VIEW: APPS.OKX_ORGANIZATION_DEFS_V

Source

View Text - Preformatted

SELECT OU.ORGANIZATION_ID ID1, '#' ID2, OU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID, OU.ORGANIZATION_ID ORGANIZATION_ID, OUTL.NAME NAME, OI1.ORG_INFORMATION2 B_STATUS, DECODE(SIGN(trunc(sysdate)-NVL(ou.date_from,trunc(sysdate))),-1,'I', DECODE(SIGN(trunc(sysdate) - NVL(ou.date_to,trunc(sysdate))), 1,'I','A')) STATUS, MTLP.ORGANIZATION_CODE ORGANIZATION_CODE, OI1.ORG_INFORMATION1 ORGANIZATION_TYPE, DECODE( OI1.ORG_INFORMATION1, 'INV', OI2.ORG_INFORMATION1, 'OPERATING_UNIT',OI2.ORG_INFORMATION3,OI2.ORG_INFORMATION1) SET_OF_BOOKS_ID, DECODE( OI1.ORG_INFORMATION1, 'INV', OI2.ORG_INFORMATION2,'OPERATING_UNIT',OI2.ORG_INFORMATION2, NULL) LEGAL_ENTITY, DECODE( OI1.ORG_INFORMATION1, 'INV', OI2.ORG_INFORMATION3, NULL) OPERATING_UNIT, MTLP.ORGANIZATION_CODE DESCRIPTION, OI2.ORG_INFORMATION_CONTEXT INFORMATION_TYPE, OU.DATE_FROM START_DATE_ACTIVE, OU.DATE_TO END_DATE_ACTIVE, MTLP.ORGANIZATION_CODE PARTY_NUMBER, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,LOC.ADDRESS_LINE_1,LOC.ADDRESS_LINE_2,LOC.ADDRESS_LINE_3,NULL,LOC.TOWN_OR_CITY,NULL,NULL,LOC.REGION_1,LOC.POSTAL_CODE,NULL,LOC.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL, NULL,'N','N',80,1,1),1,80) ADDRESS, NULL WOMEN_OWNED_FLAG, NULL SMALL_BUSINESS_FLAG, NULL MINORITY_GROUP_LOOKUP_CODE, LOC.COUNTRY COUNTRY, LOC.ADDRESS_LINE_1 ADDRESS1, LOC.ADDRESS_LINE_2 ADDRESS2, LOC.ADDRESS_LINE_3 ADDRESS3, NULL ADDRESS4, LOC.POSTAL_CODE POSTAL_CODE, NULL STATE, NULL COUNTY, LOC.TOWN_OR_CITY CITY FROM MTL_PARAMETERS MTLP,HR_ORGANIZATION_INFORMATION OI1,HR_ORGANIZATION_INFORMATION OI2,HR_LOCATIONS_ALL LOC,HR_ALL_ORGANIZATION_UNITS_TL OUTL,HR_ALL_ORGANIZATION_UNITS OU WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID AND OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID AND MTLP.ORGANIZATION_ID (+) = OU.ORGANIZATION_ID AND OUTL.ORGANIZATION_ID = OU.ORGANIZATION_ID AND OUTL.LANGUAGE = USERENV('LANG') AND OI1.ORG_INFORMATION_CONTEXT = 'CLASS' AND LOC.LOCATION_ID(+) = OU.LOCATION_ID

View Text - HTML Formatted

SELECT OU.ORGANIZATION_ID ID1
, '#' ID2
, OU.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, OU.ORGANIZATION_ID ORGANIZATION_ID
, OUTL.NAME NAME
, OI1.ORG_INFORMATION2 B_STATUS
, DECODE(SIGN(TRUNC(SYSDATE)-NVL(OU.DATE_FROM
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(OU.DATE_TO
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')) STATUS
, MTLP.ORGANIZATION_CODE ORGANIZATION_CODE
, OI1.ORG_INFORMATION1 ORGANIZATION_TYPE
, DECODE( OI1.ORG_INFORMATION1
, 'INV'
, OI2.ORG_INFORMATION1
, 'OPERATING_UNIT'
, OI2.ORG_INFORMATION3
, OI2.ORG_INFORMATION1) SET_OF_BOOKS_ID
, DECODE( OI1.ORG_INFORMATION1
, 'INV'
, OI2.ORG_INFORMATION2
, 'OPERATING_UNIT'
, OI2.ORG_INFORMATION2
, NULL) LEGAL_ENTITY
, DECODE( OI1.ORG_INFORMATION1
, 'INV'
, OI2.ORG_INFORMATION3
, NULL) OPERATING_UNIT
, MTLP.ORGANIZATION_CODE DESCRIPTION
, OI2.ORG_INFORMATION_CONTEXT INFORMATION_TYPE
, OU.DATE_FROM START_DATE_ACTIVE
, OU.DATE_TO END_DATE_ACTIVE
, MTLP.ORGANIZATION_CODE PARTY_NUMBER
, SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL
, LOC.ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3
, NULL
, LOC.TOWN_OR_CITY
, NULL
, NULL
, LOC.REGION_1
, LOC.POSTAL_CODE
, NULL
, LOC.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) ADDRESS
, NULL WOMEN_OWNED_FLAG
, NULL SMALL_BUSINESS_FLAG
, NULL MINORITY_GROUP_LOOKUP_CODE
, LOC.COUNTRY COUNTRY
, LOC.ADDRESS_LINE_1 ADDRESS1
, LOC.ADDRESS_LINE_2 ADDRESS2
, LOC.ADDRESS_LINE_3 ADDRESS3
, NULL ADDRESS4
, LOC.POSTAL_CODE POSTAL_CODE
, NULL STATE
, NULL COUNTY
, LOC.TOWN_OR_CITY CITY
FROM MTL_PARAMETERS MTLP
, HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
, HR_LOCATIONS_ALL LOC
, HR_ALL_ORGANIZATION_UNITS_TL OUTL
, HR_ALL_ORGANIZATION_UNITS OU
WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID
AND OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID
AND MTLP.ORGANIZATION_ID (+) = OU.ORGANIZATION_ID
AND OUTL.ORGANIZATION_ID = OU.ORGANIZATION_ID
AND OUTL.LANGUAGE = USERENV('LANG')
AND OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND LOC.LOCATION_ID(+) = OU.LOCATION_ID