View: EDW_MTL_ILDM_SUB_INV_LCV

View Text

SELECT MSI.SECONDARY_INVENTORY_NAME||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, NVL(MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, 'NA_EDW')
, INST.INSTANCE_CODE
, MSI.SECONDARY_INVENTORY_NAME||'('||MP.ORGANIZATION_CODE||')'
, MSI.DESCRIPTION
, 'SUB_INVENTORY'
, MSI.SECONDARY_INVENTORY_NAME||'('||MP.ORGANIZATION_CODE||')'
, MSI.CREATION_DATE
, MSI.LAST_UPDATE_DATE
, 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 SELECT MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, MP.ORGANIZATION_CODE
, NULL
, 'ALL SUB INVENTORIES'
, MP.ORGANIZATION_CODE||'('||ORG.NAME||')'
, MP.CREATION_DATE
, MP.LAST_UPDATE_DATE
, 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||'-OU'
, INSTANCE
, ORG_CODE
, NAME
, 'ALL SUB INVENTORIES'
, NAME
, OU.LAST_UPDATE_DATE
, OU.LAST_UPDATE_DATE
, USER_ATTRIBUTE1
, USER_ATTRIBUTE2
, USER_ATTRIBUTE3
, USER_ATTRIBUTE4
, USER_ATTRIBUTE5
FROM EDW_MTL_ILDM_OU_LCV OU UNION SELECT O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' PLANT_PK
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PCMP' OPM_ORAGANIZATION_FK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE OPM_COMPANY_CODE
, O.ORGN_NAME OPM_COMPANY_NAME
, 'ALL SUB INVENTORIES' OPM_COMPANY_DP
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, 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
, O.ORGN_CODE || '-' || I.INSTANCE_CODE ||'-'||'OPM' ||'-PORG' OPM_ORAGANIZATION_FK
, I.INSTANCE_CODE INSTANCE_CODE
, O.ORGN_CODE OPM_ORGANIZATION_CODE
, O.ORGN_NAME DESCRIPTION
, 'ALL SUB INVENTORIES' ORGANIZATION_DP
, O.ORGN_CODE||'('||O.ORGN_NAME||')'
, O.CREATION_DATE CREATION_DATE
, O.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM SY_ORGN_MST O
, EDW_LOCAL_INSTANCE I

Columns