VIEW: APPS.EDW_MTL_ILDM_PLANT_LCV

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)