VIEW: APPS.EDW_BRES_RESOURCE_LCV

Source

View Text - Preformatted

SELECT res.resource_code||'-'|| dept.department_code||'-'|| mp.organization_code||'-'|| inst.instance_code ,inst.instance_code ,nvl(dept.department_code||'-'||mp.organization_code||'-'|| inst.instance_code, 'NA_EDW') ,nvl(dre.resource_group_name,'NA_EDW') ,nvl(res.resource_code||'-'||mp.organization_code||'-'|| inst.instance_code,'NA_EDW') ,nvl(res.resource_code||'-'|| mp.organization_code||'-'|| inst.instance_code,'NA_EDW') ,res.resource_code||'('|| dept.department_code||'('||mp.organization_code||'))' ,substrb(res.resource_code||'('|| dept.department_code ||'('||mp.organization_code||'))',1,320) ,dept.department_code||'('|| res.resource_code||'('|| mp.organization_code||'))' ,substrb(res.description,1,240) ,to_number(NULL) ,to_number(NULL) ,decode(dre.available_24_hours_flag, 1, 'Y', 'N') ,greatest(dre.last_update_date, res.last_update_date) ,dre.creation_date ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL FROM mfg_lookups rt, bom_departments dept, bom_resources res, bom_department_resources dre, mtl_parameters mp, edw_local_instance inst WHERE dept.department_id = dre.department_id AND dre.resource_id = res.resource_id AND res.organization_id = mp.organization_id + 0 AND ((res.last_update_date > to_date('1000/01/01','YYYY/MM/DD')) or (dre.last_update_date > to_date('1000/01/01','YYYY/MM/DD'))) AND rt.lookup_type = 'BOM_RESOURCE_TYPE' AND rt.lookup_code = res.resource_type UNION ALL SELECT l.line_code||'-'||mp.organization_code||'-'||inst.instance_code ,inst.instance_code ,nvl('line-'||l.line_code||'-'|| mp.organization_code||'-'|| inst.instance_code,'NA_EDW') ,'NA_EDW' ,'NA_EDW' ,'NA_EDW' ,l.line_code||'('|| mp.organization_code||')' ,substrb(l.line_code,1,320) ,l.line_code||'('|| mp.organization_code||')' ,substr(l.description,1,240) ,l.minimum_rate ,l.maximum_rate ,'N' ,l.last_update_date ,l.creation_date ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL FROM wip_lines l, mtl_parameters mp, edw_local_instance inst WHERE l.organization_id = mp.organization_id /* The following lines are for OPM */ UNION ALL SELECT RS.resources||'-'||inst.instance_code||'-OPM', inst.instance_code, 'NA_EDW' , decode(RS.resource_class, NULL, 'NA_EDW', RS.resource_class||'-'||inst.instance_code||'-OPM'), 'NA_EDW' , 'NA_EDW' , RS.resources, RS.resources, RS.resources, RS.resource_desc, to_number(NULL) , to_number(NULL) , NULL, RS.last_update_date, RS.creation_date , NULL, NULL ,NULL ,NULL ,NULL ,NULL FROM CR_RSRC_MST RS, edw_local_instance inst WITH READ ONLY

View Text - HTML Formatted

SELECT RES.RESOURCE_CODE||'-'|| DEPT.DEPARTMENT_CODE||'-'|| MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, NVL(DEPT.DEPARTMENT_CODE||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'NA_EDW')
, NVL(DRE.RESOURCE_GROUP_NAME
, 'NA_EDW')
, NVL(RES.RESOURCE_CODE||'-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'NA_EDW')
, NVL(RES.RESOURCE_CODE||'-'|| MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'NA_EDW')
, RES.RESOURCE_CODE||'('|| DEPT.DEPARTMENT_CODE||'('||MP.ORGANIZATION_CODE||'))'
, SUBSTRB(RES.RESOURCE_CODE||'('|| DEPT.DEPARTMENT_CODE ||'('||MP.ORGANIZATION_CODE||'))'
, 1
, 320)
, DEPT.DEPARTMENT_CODE||'('|| RES.RESOURCE_CODE||'('|| MP.ORGANIZATION_CODE||'))'
, SUBSTRB(RES.DESCRIPTION
, 1
, 240)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(DRE.AVAILABLE_24_HOURS_FLAG
, 1
, 'Y'
, 'N')
, GREATEST(DRE.LAST_UPDATE_DATE
, RES.LAST_UPDATE_DATE)
, DRE.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MFG_LOOKUPS RT
, BOM_DEPARTMENTS DEPT
, BOM_RESOURCES RES
, BOM_DEPARTMENT_RESOURCES DRE
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE DEPT.DEPARTMENT_ID = DRE.DEPARTMENT_ID
AND DRE.RESOURCE_ID = RES.RESOURCE_ID
AND RES.ORGANIZATION_ID = MP.ORGANIZATION_ID + 0
AND ((RES.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD')) OR (DRE.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD')))
AND RT.LOOKUP_TYPE = 'BOM_RESOURCE_TYPE'
AND RT.LOOKUP_CODE = RES.RESOURCE_TYPE UNION ALL SELECT L.LINE_CODE||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, NVL('LINE-'||L.LINE_CODE||'-'|| MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE
, 'NA_EDW')
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, L.LINE_CODE||'('|| MP.ORGANIZATION_CODE||')'
, SUBSTRB(L.LINE_CODE
, 1
, 320)
, L.LINE_CODE||'('|| MP.ORGANIZATION_CODE||')'
, SUBSTR(L.DESCRIPTION
, 1
, 240)
, L.MINIMUM_RATE
, L.MAXIMUM_RATE
, 'N'
, L.LAST_UPDATE_DATE
, L.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM WIP_LINES L
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
WHERE L.ORGANIZATION_ID = MP.ORGANIZATION_ID /* THE FOLLOWING LINES ARE FOR OPM */ UNION ALL SELECT RS.RESOURCES||'-'||INST.INSTANCE_CODE||'-OPM'
, INST.INSTANCE_CODE
, 'NA_EDW'
, DECODE(RS.RESOURCE_CLASS
, NULL
, 'NA_EDW'
, RS.RESOURCE_CLASS||'-'||INST.INSTANCE_CODE||'-OPM')
, 'NA_EDW'
, 'NA_EDW'
, RS.RESOURCES
, RS.RESOURCES
, RS.RESOURCES
, RS.RESOURCE_DESC
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, RS.LAST_UPDATE_DATE
, RS.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM CR_RSRC_MST RS
, EDW_LOCAL_INSTANCE INST WITH READ ONLY