Product: | ENG - Engineering |
---|---|
Description: | This view holds resource department information |
Implementation/DBA Data: | APPS.EDW_BRES_DEPT_LCV |
SELECT DE.DEPARTMENT_CODE||'-'|| MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, DECODE(DE.DEPARTMENT_CLASS_CODE
, NULL
, 'NA_EDW'
, DE.DEPARTMENT_CLASS_CODE ||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE)
, NVL(DE.DEPARTMENT_CODE||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'NA_EDW')
, DE.DEPARTMENT_CODE||'('||MP.ORGANIZATION_CODE||')'
, DE.DEPARTMENT_CODE
, DE.DEPARTMENT_CODE||'('||MP.ORGANIZATION_CODE||')'
, DE.DESCRIPTION
, DE.LAST_UPDATE_DATE
, DE.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM BOM_DEPARTMENTS DE
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE DE.ORGANIZATION_ID = MP.ORGANIZATION_ID + 0 UNION ALL SELECT 'LINE-'||LINE.LINE_CODE||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, 'LINE-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'LINE-'||LINE.LINE_CODE||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, ML.MEANING||':'||LINE.LINE_CODE||'('|| MP.ORGANIZATION_CODE||')'
, ML.MEANING||':'||LINE.LINE_CODE
, ML.MEANING||':'||LINE.LINE_CODE||'('|| MP.ORGANIZATION_CODE||')'
, LINE.DESCRIPTION
, LINE.LAST_UPDATE_DATE
, LINE.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM WIP_LINES LINE
, MFG_LOOKUPS ML
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE ML.LOOKUP_TYPE = 'CRP_REPORT_TYPE'
AND ML.LOOKUP_CODE = 1
AND LINE.ORGANIZATION_ID = MP.ORGANIZATION_ID WITH READ ONLY