VIEW: APPS.EDW_MTL_ILDM_SUB_INV_LCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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