View: OKX_ORGANIZATION_DEFS_V

Product:	OKX - Contracts Integration
Description:	Organization unit definitions
Implementation/DBA Data:	APPS.OKX_ORGANIZATION_DEFS_V

View Text

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

Columns