View: EDW_MTL_ILDM_LOCATOR_LCV

Product:	OPI - Operations Intelligence
Description:	Source View for Locator Level of Inventory Locator Dimension
Implementation/DBA Data:	APPS.EDW_MTL_ILDM_LOCATOR_LCV

View Text

SELECT LOCF.INVENTORY_LOCATION_ID||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, LOCF.SUBINVENTORY_CODE||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, LOCF.CONCATENATED_SEGMENTS ||'('||MP.ORGANIZATION_CODE||')'
, LOCF.DESCRIPTION
, LOCF.ENABLED_FLAG
, 'LOCATOR'
, LOCF.CONCATENATED_SEGMENTS ||'('||HOU.NAME||')'
, LOCF.CREATION_DATE
, LOCF.LAST_UPDATE_DATE
, LOCF.INVENTORY_LOCATION_ID
, LOCF.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_ITEM_LOCATIONS_KFV LOCF
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, EDW_LOCAL_INSTANCE INST
WHERE LOCF.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND (LOCF.PHYSICAL_LOCATION_ID = LOCF.INVENTORY_LOCATION_ID OR LOCF.PHYSICAL_LOCATION_ID IS NULL) UNION ALL SELECT MSI.SECONDARY_INVENTORY_NAME||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-SUBI'
, INST.INSTANCE_CODE
, MSI.SECONDARY_INVENTORY_NAME||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, MSI.SECONDARY_INVENTORY_NAME||'('||ORG.NAME||')'
, NULL
, NULL
, 'ALL LOCATORS'
, MSI.SECONDARY_INVENTORY_NAME||'('||MP.ORGANIZATION_CODE||')'
, MSI.CREATION_DATE
, MSI.LAST_UPDATE_DATE
, TO_NUMBER(NULL)
, MP.ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_SECONDARY_INVENTORIES MSI
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID + 0
AND BG.ORGANIZATION_ID = ORG.BUSINESS_GROUP_ID
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, INST.INSTANCE_CODE
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, ORG.NAME||'('||BG.NAME||')'
, NULL
, NULL
, 'ALL LOCATORS'
, MP.ORGANIZATION_CODE||'('||ORG.NAME||')'
, MP.CREATION_DATE
, MP.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MP.ORGANIZATION_ID
, 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
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'
, INSTANCE
, OPERATING_UNIT_PK||'-OU'
, NAME
, NAME
, NULL
, 'ALL LOCATORS'
, NAME
, O.LAST_UPDATE_DATE
, O.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, 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
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' OPM_ORAGANIZATION_FK
, O.ORGN_NAME OPM_COMPANY_NAME
, O.ORGN_NAME DESCRIPTION
, NULL
, 'ALL LOCATORS'
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, EDW_LOCAL_INSTANCE I
WHERE O.ORGN_CODE = O.CO_CODE UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' ORGANIZATION_PK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM-PORG' OPM_ORAGANIZATION_FK
, O.ORGN_NAME OPM_ORGANIZATION_NAME
, O.ORGN_NAME DESCRIPTION
, NULL
, 'ALL LOCATORS'
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, EDW_LOCAL_INSTANCE I UNION ALL SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNL-'||I.INSTANCE_CODE
, I.INSTANCE_CODE
, MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK
, ILI.LOCATION
, ILI.LOCATION
, NULL
, 'LOCATOR'
, ILI.LOCATION||'('||IWM.WHSE_NAME||')'
, ILI.CREATION_DATE
, ILI.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MTL_ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM IC_LOCT_INV ILI
, IC_ITEM_MST IIM
, IC_WHSE_MST IWM
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL = 0 UNION SELECT ILI.LOCATION||'-'||MP.ORGANIZATION_CODE||'-OPMNV-'||I.INSTANCE_CODE
, I.INSTANCE_CODE
, MP.ORGANIZATION_CODE|| '-'||MP.ORGANIZATION_CODE||'-'||I.INSTANCE_CODE STOCK_ROOM_FK
, ILI.LOCATION
, ILI.LOCATION
, NULL
, 'LOCATOR'
, ILI.LOCATION||'('||IWM.WHSE_NAME||')'
, ILI.CREATION_DATE
, ILI.LAST_UPDATE_DATE
, TO_NUMBER(NULL) INVENTORY_LOCATION_ID
, MTL_ORGANIZATION_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM IC_LOCT_INV ILI
, IC_ITEM_MST IIM
, IC_WHSE_MST IWM
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL >1

Columns