1. Tables and Views for SCM
  2. MSS_CAMPAIGN_SUMMARIES_V

MSS_CAMPAIGN_SUMMARIES_V

Details

  • Schema: FUSION

  • Object owner: MSS

  • Object type: VIEW

Columns

Name

CAMPAIGN_ID

ELAPSED_DURATION_HOURS

RUN_RATE_PER_HOUR

ASSIGNED_QUANTITY

UNASSIGNED_QUANTITY

COMPLETED_QUANTITY

CAMPAIGN_UOM_CODE

Query

SQL_Statement

select

camp.CAMPAIGN_ID,

Case

when camp.START_DATE is null or camp.END_DATE is null then null

else (Cast(camp.END_DATE as date) - Cast(camp.START_DATE as date)) * 24

end as ELAPSED_DURATION_HOURS,

camp.RUN_RATE_UNITS / camp.RUN_RATE_HOURS as RUN_RATE_PER_HOUR,

Nvl(Sum(campWoSummary.OP_REMAINING_QTY_CAMP_UOM), 0.0) as ASSIGNED_QUANTITY,

camp.PLANNED_QUANTITY - Nvl(Sum(campWoSummary.OP_REMAINING_QTY_CAMP_UOM), 0.0) as UNASSIGNED_QUANTITY,

Sum(campWoSummary.OP_COMPLETED_QTY_CAMP_UOM) as COMPLETED_QUANTITY,

resParam.CAMPAIGN_UOM_CODE

from MSS_CAMPAIGNS camp

join MSS_ORG_RESOURCE_PARAMETERS resParam

on camp.ORGANIZATION_ID = resParam.ORGANIZATION_ID

and camp.WORK_CENTER_ID = resParam.WORK_CENTER_ID

and camp.RESOURCE_ID = resParam.RESOURCE_ID

left outer join MSS_CAMPAIGN_WO_SUMMARIES_V campWoSummary

on camp.CAMPAIGN_ID = campWoSummary.CAMPAIGN_ID

and campWoSummary.ASSIGNED_TO_CAMPAIGN_FLAG = 'Y'

and campWoSummary.WO_SYSTEM_STATUS_CODE not in ('CANCELED', 'ON_HOLD')

group by camp.CAMPAIGN_ID,

camp.PLANNED_QUANTITY,

camp.RUN_RATE_UNITS,

camp.RUN_RATE_HOURS,

resParam.CAMPAIGN_UOM_CODE,

camp.START_DATE,

camp.END_DATE