[Home] [Help]
| Product: | OPI - Operations Intelligence (Obsolete) |
|---|---|
| Description: | Source View for Inventory Organization Level of Inventory Locator Dimension |
| Implementation/DBA Data: | Not implemented in this database |
SELECT MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, DECODE(FPG.MULTI_ORG_FLAG
, 'Y'
, DECODE(HOI.ORG_INFORMATION_CONTEXT
, 'ACCOUNTING INFORMATION'
, DECODE(HOI.ORG_INFORMATION3
, NULL
, 'NA_EDW'
, HOI.ORG_INFORMATION3||'-'|| INST.INSTANCE_CODE)
, 'NA_EDW')
, 'NA_EDW') AS OPERATING_UNIT
, INST.INSTANCE_CODE
, MP.ORGANIZATION_CODE||'('||ORG.NAME||')'
, MP.ORGANIZATION_CODE
, NULL
, 'PLANT'
, ORG.NAME||'('||BG.NAME||')'
, MP.LAST_UPDATE_DATE
, MP.CREATION_DATE
, DECODE(MP.PROCESS_ENABLED_FLAG
, 'Y'
, MP.PROCESS_ORGN_CODE||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, DECODE(FPG.MULTI_ORG_FLAG
, 'Y'
, DECODE(HOI.ORG_INFORMATION_CONTEXT
, 'ACCOUNTING INFORMATION'
, DECODE(HOI.ORG_INFORMATION3
, NULL
, 'NA_EDW'
, HOI.ORG_INFORMATION3 ||'-'||INST.INSTANCE_CODE||'-'||'OU')
, 'NA_EDW')
, 'NA_EDW') )
, NULL
, NULL
, NULL
, NULL
, NULL
FROM HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ORGANIZATION_INFORMATION HOI
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
, FND_PRODUCT_GROUPS FPG
WHERE BG.ORGANIZATION_ID = ORG.BUSINESS_GROUP_ID
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='ACCOUNTING INFORMATION' UNION SELECT OPERATING_UNIT_PK||'-OU'
, OPERATING_UNIT_PK
, INSTANCE
, NAME
, ORG_CODE
, NAME
, 'ALL PLANTS'
, NAME
, O.LAST_UPDATE_DATE
, O.LAST_UPDATE_DATE
, OPERATING_UNIT_PK||'-OU'
, USER_ATTRIBUTE1
, USER_ATTRIBUTE2
, USER_ATTRIBUTE3
, USER_ATTRIBUTE4
, USER_ATTRIBUTE5
FROM EDW_MTL_ILDM_OU_LCV O UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' PLANT_PK
, DECODE(PM.ORG_ID
, NULL
, 'NA_EDW'
, PM.ORG_ID ||'-' || I.INSTANCE_CODE ) OPM_ORAGANIZATION_FK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_NAME OPM_COMPANY_NAME
, O.ORGN_CODE OPM_COMPANY_CODE
, O.ORGN_NAME DESCRIPTION
, 'ALL PLANTS' OPM_COMPANY_DP
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, O.CREATION_DATE CREATION_DATE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' OPM_ORAGANIZATION_FK
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, GL_PLCY_MST PM
, EDW_LOCAL_INSTANCE I
WHERE O.ORGN_CODE = O.CO_CODE
AND O.ORGN_CODE = PM.CO_CODE(+) UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PORG' ORGANIZATION_PK
, DECODE(PM.ORG_ID
, NULL
, 'NA_EDW'
, PM.ORG_ID ||'-' || I.INSTANCE_CODE ) OPM_ORAGANIZATION_FK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_NAME OPM_ORGANIZATION_NAME
, O.ORGN_CODE OPM_ORGANIZATION_CODE
, O.ORGN_NAME DESCRIPTION
, 'ALL PLANTS' ORGANIZATION_DP
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, O.CREATION_DATE CREATION_DATE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' OPM_ORAGANIZATION_FK
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, GL_PLCY_MST PM
, EDW_LOCAL_INSTANCE I
WHERE O.CO_CODE = PM.CO_CODE(+)