1. Tables and Views for SCM
  2. MSS_CAMPAIGN_WO_SUMMARIES_V

MSS_CAMPAIGN_WO_SUMMARIES_V

Details

  • Schema: FUSION

  • Object owner: MSS

  • Object type: VIEW

Columns

Name

CAMPAIGN_ID

WORK_ORDER_ID

WORK_ORDER_NUMBER

WO_OPERATION_ID

WO_OPERATION_RESOURCE_ID

ASSIGNED_TO_CAMPAIGN_FLAG

CAMPAIGN_WO_SEQ_NUMBER

WORK_METHOD_CODE

WORK_ORDER_STATUS

WORK_ORDER_STATUS_NAME

WO_SYSTEM_STATUS_CODE

REQUESTED_DATE

INVENTORY_ITEM_ID

ITEM_NUMBER

UOM_CODE

CAMPAIGN_UOM_CODE

PLANNED_START_QUANTITY

NETTABLE_PLANNED_QUANTITY

PRIMARY_PRODUCT_QUANTITY

OP_COMPLETED_QUANTITY

OP_COMPLETED_QTY_CAMP_UOM

OP_SCRAPPED_QUANTITY

OP_SCRAPPED_QTY_CAMP_UOM

OP_REMAINING_QUANTITY

OP_REMAINING_QTY_CAMP_UOM

OP_RES_REQUIRED_USAGE

OP_RES_REQUIRED_USAGE_UOM_CODE

Query

SQL_Statement

select

summary.CAMPAIGN_ID,

summary.WORK_ORDER_ID,

summary.WORK_ORDER_NUMBER,

summary.WO_OPERATION_ID,

summary.WO_OPERATION_RESOURCE_ID,

summary.ASSIGNED_TO_CAMPAIGN_FLAG,

summary.CAMPAIGN_WO_SEQ_NUMBER,

summary.WORK_METHOD_CODE,

summary.WORK_ORDER_STATUS,

summary.WORK_ORDER_STATUS_NAME,

summary.WO_SYSTEM_STATUS_CODE,

summary.REQUESTED_DATE,

summary.INVENTORY_ITEM_ID,

summary.ITEM_NUMBER,

summary.UOM_CODE,

summary.CAMPAIGN_UOM_CODE,

summary.PLANNED_START_QUANTITY,

summary.NETTABLE_PLANNED_QUANTITY,

Case

when summary.PRIMARY_PRODUCT_QUANTITY is null then null

else INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.PRIMARY_PRODUCT_QUANTITY, summary.PRIMARY_PRODUCT_UOM_CODE, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), null, null)

end as PRIMARY_PRODUCT_QUANTITY,

summary.OP_COMPLETED_QUANTITY,

Case

when summary.OP_COMPLETED_QUANTITY is null then null

when summary.CAMPAIGN_UOM_CODE is null then summary.OP_COMPLETED_QUANTITY

when INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_COMPLETED_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null) = -99999

then summary.OP_COMPLETED_QUANTITY

else INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_COMPLETED_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null)

end as OP_COMPLETED_QTY_CAMP_UOM,

summary.OP_SCRAPPED_QUANTITY,

Case

when summary.OP_SCRAPPED_QUANTITY is null then null

when summary.CAMPAIGN_UOM_CODE is null then summary.OP_SCRAPPED_QUANTITY

when INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_SCRAPPED_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null) = -99999

then summary.OP_SCRAPPED_QUANTITY

else INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_SCRAPPED_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null)

end as OP_SCRAPPED_QTY_CAMP_UOM,

summary.OP_REMAINING_QUANTITY,

Case

when summary.CAMPAIGN_UOM_CODE is null then summary.OP_REMAINING_QUANTITY

when INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_REMAINING_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null) = -99999

then summary.OP_REMAINING_QUANTITY

else INV_CONVERT.Inv_Um_Convert(summary.INVENTORY_ITEM_ID, 10, summary.OP_REMAINING_QUANTITY, Nvl(summary.ITEM_PRIMARY_UOM_CODE, 'Ea'), summary.CAMPAIGN_UOM_CODE, null, null)

end as OP_REMAINING_QTY_CAMP_UOM,

summary.OP_RES_REQUIRED_USAGE,

summary.OP_RES_REQUIRED_USAGE_UOM_CODE

from (

select

campWo.CAMPAIGN_ID,

wo.WORK_ORDER_ID,

woOp.WO_OPERATION_ID,

woOpRes.WO_OPERATION_RESOURCE_ID,

wo.WORK_ORDER_NUMBER,

campWo.ASSIGNED_TO_CAMPAIGN_FLAG,

campWo.CAMPAIGN_WO_SEQ_NUMBER,

wkMtd.WORK_METHOD_CODE,

woStatus.WO_STATUS_CODE as WORK_ORDER_STATUS,

woStatus.WO_STATUS_NAME as WORK_ORDER_STATUS_NAME,

woStatus.WO_SYSTEM_STATUS_CODE,

Nvl(wo.NEED_BY_DATE, wo.PLANNED_COMPLETION_DATE) as REQUESTED_DATE,

wo.INVENTORY_ITEM_ID,

item.ITEM_NUMBER,

wo.UOM_CODE,

item.PRIMARY_UOM_CODE as ITEM_PRIMARY_UOM_CODE,

resParam.CAMPAIGN_UOM_CODE,

wo.PLANNED_START_QUANTITY,

wo.PLANNED_START_QUANTITY * woYield.CUMULATIVE_YIELD as NETTABLE_PLANNED_QUANTITY,

wo.PRIMARY_PRODUCT_UOM_CODE,

wo.PRIMARY_PRODUCT_QUANTITY,

woOp.COMPLETED_QUANTITY as OP_COMPLETED_QUANTITY,

woOp.SCRAPPED_QUANTITY as OP_SCRAPPED_QUANTITY,

Greatest(

0.0,

Nvl(

Case

when wo.PRIMARY_PRODUCT_QUANTITY is null then null

else

INV_CONVERT.Inv_Um_Convert(wo.INVENTORY_ITEM_ID, 10, wo.PRIMARY_PRODUCT_QUANTITY, wo.PRIMARY_PRODUCT_UOM_CODE, Nvl(item.PRIMARY_UOM_CODE, 'Ea'), null, null)

end,

Nvl(wo.PLANNED_START_QUANTITY * woYield.CUMULATIVE_YIELD, wo.PLANNED_START_QUANTITY)

)

- Nvl(woOp.COMPLETED_QUANTITY, 0.0)

- Nvl(woOp.SCRAPPED_QUANTITY, 0.0)

) as OP_REMAINING_QUANTITY,

woOpRes.REQUIRED_USAGE as OP_RES_REQUIRED_USAGE,

woOpRes.UOM_CODE as OP_RES_REQUIRED_USAGE_UOM_CODE,

Row_Number() over (partition by campWo.CAMPAIGN_ID, wo.WORK_ORDER_ID order by woOp.OPERATION_SEQ_NUMBER, woOpRes.RESOURCE_SEQ_NUMBER) as rn

from MSS_CAMPAIGN_WORK_ORDERS campWo

join MSS_CAMPAIGNS camp

on campWo.CAMPAIGN_ID = camp.CAMPAIGN_ID

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

join WIE_WORK_ORDERS_B wo

on campWo.WORK_ORDER_ID = wo.WORK_ORDER_ID

join EGP_SYSTEM_ITEMS_VL item

on wo.ORGANIZATION_ID = item.ORGANIZATION_ID

and wo.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID

left outer join (

select

WORK_ORDER_ID,

Max(CUMULATIVE_YIELD) keep (dense_rank last order by OPERATION_SEQ_NUMBER) as CUMULATIVE_YIELD

from WIE_WO_OP_YIELD_V

group by WORK_ORDER_ID

) woYield

on wo.WORK_ORDER_ID = woYield.WORK_ORDER_ID

join WIS_WORK_METHODS_VL wkMtd

on wo.WORK_METHOD_ID = wkMtd.WORK_METHOD_ID

join WIE_WO_STATUSES_VL woStatus

on wo.WORK_ORDER_STATUS_ID = woStatus.WO_STATUS_ID

join WIE_WO_OPERATIONS_B woOp

on wo.WORK_ORDER_ID = woOp.WORK_ORDER_ID

join WIE_WO_OPERATION_RESOURCES woOpRes

on woOp.WO_OPERATION_ID = woOpRes.WO_OPERATION_ID

and woOpRes.RESOURCE_ID = camp.RESOURCE_ID

) summary

where summary.rn = 1