MSS_PEGGING_RELATIONSHIPS

Details

Schema: FUSION
Object owner: MSS
Object type: VIEW

Columns

Name
PLAN_ID PEGGING_ID INVENTORY_ITEM_ID ITEM_CODE UOM_CODE UOM_NAME LOT_NUMBER QUANTITY SUPPLY_BY_TYPE SUPPLY_BY_ID SUPPLY_NUMBER SUPPLY_LINE_ITEM_NUMBER SUPPLY_BY_WORK_ORDER_ID SUPPLY_BY_WORK_ORDER_NUMBER SUPPLY_BY_OPERATION_CODE SUPPLY_BY_OPERATION_NAME SUPPLY_BY_OP_SEQ_NUMBER SUPPLY_BY_RESOURCE_SEQ_NUMBER SUPPLY_BY_WORK_CENTER_ID SUPPLY_BY_WORK_CENTER_CODE SUPPLY_BY_DURATION_RES_ID SUPPLY_BY_DURATION_RES_CODE CONSUME_BY_TYPE CONSUME_BY_ID DEMAND_NUMBER DEMAND_LINE_ITEM_NUMBER CONSUME_BY_WORK_ORDER_ID CONSUME_BY_WORK_ORDER_NUMBER CONSUME_BY_OPERATION_CODE CONSUME_BY_OPERATION_NAME CONSUME_BY_OP_SEQ_NUMBER CONSUME_BY_RESOURCE_SEQ_NUMBER CONSUME_BY_WORK_CENTER_ID CONSUME_BY_WORK_CENTER_CODE CONSUME_BY_DURATION_RES_ID CONSUME_BY_DURATION_RES_CODE SUPPLY_DATE CONSUME_DATE END_DEMAND_ID END_DEMAND_NUMBER END_DEMAND_LINE_ITEM_NUMBER

Name

PLAN_ID

PEGGING_ID

INVENTORY_ITEM_ID

ITEM_CODE

UOM_CODE

UOM_NAME

LOT_NUMBER

QUANTITY

SUPPLY_BY_TYPE

SUPPLY_BY_ID

SUPPLY_NUMBER

SUPPLY_LINE_ITEM_NUMBER

SUPPLY_BY_WORK_ORDER_ID

SUPPLY_BY_WORK_ORDER_NUMBER

SUPPLY_BY_OPERATION_CODE

SUPPLY_BY_OPERATION_NAME

SUPPLY_BY_OP_SEQ_NUMBER

SUPPLY_BY_RESOURCE_SEQ_NUMBER

SUPPLY_BY_WORK_CENTER_ID

SUPPLY_BY_WORK_CENTER_CODE

SUPPLY_BY_DURATION_RES_ID

SUPPLY_BY_DURATION_RES_CODE

CONSUME_BY_TYPE

CONSUME_BY_ID

DEMAND_NUMBER

DEMAND_LINE_ITEM_NUMBER

CONSUME_BY_WORK_ORDER_ID

CONSUME_BY_WORK_ORDER_NUMBER

CONSUME_BY_OPERATION_CODE

CONSUME_BY_OPERATION_NAME

CONSUME_BY_OP_SEQ_NUMBER

CONSUME_BY_RESOURCE_SEQ_NUMBER

CONSUME_BY_WORK_CENTER_ID

CONSUME_BY_WORK_CENTER_CODE

CONSUME_BY_DURATION_RES_ID

CONSUME_BY_DURATION_RES_CODE

SUPPLY_DATE

CONSUME_DATE

END_DEMAND_ID

END_DEMAND_NUMBER

END_DEMAND_LINE_ITEM_NUMBER

Query

SQL_Statement
select pr.PLAN_ID, pr.PEGGING_ID, pr.INVENTORY_ITEM_ID, it.ITEM_CODE, it.UOM_CODE, it.UOM_NAME, pr.LOT_NUMBER, pr.QUANTITY, decode(pr.SUPPLY_BY_TYPE, 'Supply Event', s.SUPPLY_TYPE, pr.SUPPLY_BY_TYPE) SUPPLY_BY_TYPE, pr.SUPPLY_BY_ID, s.SUPPLY_NUMBER, s.SUPPLY_LINE_ITEM_NUMBER, swo.WORK_ORDER_ID SUPPLY_BY_WORK_ORDER_ID, swo.WORK_ORDER_NUMBER SUPPLY_BY_WORK_ORDER_NUMBER, sdr.OPERATION_CODE SUPPLY_BY_OPERATION_CODE, sdr.OPERATION_NAME SUPPLY_BY_OPERATION_NAME, sdr.OPERATION_SEQ_NUMBER SUPPLY_BY_OP_SEQ_NUMBER, pr.SUPPLY_BY_RESOURCE_SEQ_NUMBER, sdr.WORK_CENTER_ID SUPPLY_BY_WORK_CENTER_ID, sdr.WORK_CENTER_CODE SUPPLY_BY_WORK_CENTER_CODE, sdr.DURATION_RES_ID SUPPLY_BY_DURATION_RES_ID, sdr.DURATION_RES_CODE SUPPLY_BY_DURATION_RES_CODE, decode(pr.CONSUME_BY_TYPE, 'Demand Event', d.DEMAND_TYPE, pr.CONSUME_BY_TYPE) CONSUME_BY_TYPE, pr.CONSUME_BY_ID, d.DEMAND_NUMBER, d.DEMAND_LINE_ITEM_NUMBER, dwo.WORK_ORDER_ID CONSUME_BY_WORK_ORDER_ID, dwo.WORK_ORDER_NUMBER CONSUME_BY_WORK_ORDER_NUMBER, ddr.OPERATION_CODE CONSUME_BY_OPERATION_CODE, ddr.OPERATION_NAME CONSUME_BY_OPERATION_NAME, ddr.OPERATION_SEQ_NUMBER CONSUME_BY_OP_SEQ_NUMBER, pr.CONSUME_BY_RESOURCE_SEQ_NUMBER, ddr.WORK_CENTER_ID CONSUME_BY_WORK_CENTER_ID, ddr.WORK_CENTER_CODE CONSUME_BY_WORK_CENTER_CODE, ddr.DURATION_RES_ID CONSUME_BY_DURATION_RES_ID, ddr.DURATION_RES_CODE CONSUME_BY_DURATION_RES_CODE, pr.SUPPLY_DATE, pr.CONSUME_DATE, pr.END_DEMAND_ID, dd.DEMAND_NUMBER END_DEMAND_NUMBER, dd.DEMAND_LINE_ITEM_NUMBER END_DEMAND_LINE_ITEM_NUMBER from MSS_PEGGING_RELATIONSHIPS pr left outer join MSS_ITEMS it on pr.PLAN_ID = it.PLAN_ID and pr.INVENTORY_ITEM_ID = it.INVENTORY_ITEM_ID left outer join MSS_SUPPLIES s on pr.SUPPLY_BY_TYPE='Supply Event' and pr.PLAN_ID = s.PLAN_ID and pr.SUPPLY_BY_ID = s.SUPPLY_ID left outer join MSS_WO_DURATION_RESOURCES_V sdr on pr.SUPPLY_BY_TYPE='Work Order Operation' and pr.PLAN_ID=sdr.PLAN_ID and pr.SUPPLY_BY_ID=sdr.WO_OPERATION_ID and pr.SUPPLY_BY_RESOURCE_SEQ_NUMBER=sdr.RESOURCE_SEQ_NUMBER left outer join MSS_WORK_ORDERS swo on sdr.PLAN_ID = swo.PLAN_ID and sdr.WORK_ORDER_ID=swo.WORK_ORDER_ID left outer join MSS_DEMANDS d on pr.CONSUME_BY_TYPE='Demand Event' and pr.PLAN_ID = d.PLAN_ID and pr.CONSUME_BY_ID = d.DEMAND_ID left outer join MSS_WO_DURATION_RESOURCES_V ddr on pr.CONSUME_BY_TYPE='Work Order Operation' and pr.PLAN_ID=ddr.PLAN_ID and pr.CONSUME_BY_ID=ddr.WO_OPERATION_ID and pr.CONSUME_BY_RESOURCE_SEQ_NUMBER=ddr.RESOURCE_SEQ_NUMBER left outer join MSS_WORK_ORDERS dwo on ddr.PLAN_ID = dwo.PLAN_ID and ddr.WORK_ORDER_ID=dwo.WORK_ORDER_ID left outer join MSS_DEMANDS dd on pr.PLAN_ID = dd.PLAN_ID and pr.END_DEMAND_ID = dd.DEMAND_ID

SQL_Statement

select

pr.PLAN_ID,

pr.PEGGING_ID,

pr.INVENTORY_ITEM_ID,

it.ITEM_CODE,

it.UOM_CODE,

it.UOM_NAME,

pr.LOT_NUMBER,

pr.QUANTITY,

decode(pr.SUPPLY_BY_TYPE, 'Supply Event', s.SUPPLY_TYPE, pr.SUPPLY_BY_TYPE) SUPPLY_BY_TYPE,

pr.SUPPLY_BY_ID,

s.SUPPLY_NUMBER,

s.SUPPLY_LINE_ITEM_NUMBER,

swo.WORK_ORDER_ID SUPPLY_BY_WORK_ORDER_ID,

swo.WORK_ORDER_NUMBER SUPPLY_BY_WORK_ORDER_NUMBER,

sdr.OPERATION_CODE SUPPLY_BY_OPERATION_CODE,

sdr.OPERATION_NAME SUPPLY_BY_OPERATION_NAME,

sdr.OPERATION_SEQ_NUMBER SUPPLY_BY_OP_SEQ_NUMBER,

pr.SUPPLY_BY_RESOURCE_SEQ_NUMBER,

sdr.WORK_CENTER_ID SUPPLY_BY_WORK_CENTER_ID,

sdr.WORK_CENTER_CODE SUPPLY_BY_WORK_CENTER_CODE,

sdr.DURATION_RES_ID SUPPLY_BY_DURATION_RES_ID,

sdr.DURATION_RES_CODE SUPPLY_BY_DURATION_RES_CODE,

decode(pr.CONSUME_BY_TYPE, 'Demand Event', d.DEMAND_TYPE, pr.CONSUME_BY_TYPE) CONSUME_BY_TYPE,

pr.CONSUME_BY_ID,

d.DEMAND_NUMBER,

d.DEMAND_LINE_ITEM_NUMBER,

dwo.WORK_ORDER_ID CONSUME_BY_WORK_ORDER_ID,

dwo.WORK_ORDER_NUMBER CONSUME_BY_WORK_ORDER_NUMBER,

ddr.OPERATION_CODE CONSUME_BY_OPERATION_CODE,

ddr.OPERATION_NAME CONSUME_BY_OPERATION_NAME,

ddr.OPERATION_SEQ_NUMBER CONSUME_BY_OP_SEQ_NUMBER,

pr.CONSUME_BY_RESOURCE_SEQ_NUMBER,

ddr.WORK_CENTER_ID CONSUME_BY_WORK_CENTER_ID,

ddr.WORK_CENTER_CODE CONSUME_BY_WORK_CENTER_CODE,

ddr.DURATION_RES_ID CONSUME_BY_DURATION_RES_ID,

ddr.DURATION_RES_CODE CONSUME_BY_DURATION_RES_CODE,

pr.SUPPLY_DATE,

pr.CONSUME_DATE,

pr.END_DEMAND_ID,

dd.DEMAND_NUMBER END_DEMAND_NUMBER,

dd.DEMAND_LINE_ITEM_NUMBER END_DEMAND_LINE_ITEM_NUMBER

from

MSS_PEGGING_RELATIONSHIPS pr

left outer join MSS_ITEMS it

on pr.PLAN_ID = it.PLAN_ID and pr.INVENTORY_ITEM_ID = it.INVENTORY_ITEM_ID

left outer join MSS_SUPPLIES s

on pr.SUPPLY_BY_TYPE='Supply Event' and pr.PLAN_ID = s.PLAN_ID and pr.SUPPLY_BY_ID = s.SUPPLY_ID

left outer join MSS_WO_DURATION_RESOURCES_V sdr

on pr.SUPPLY_BY_TYPE='Work Order Operation' and pr.PLAN_ID=sdr.PLAN_ID and pr.SUPPLY_BY_ID=sdr.WO_OPERATION_ID and pr.SUPPLY_BY_RESOURCE_SEQ_NUMBER=sdr.RESOURCE_SEQ_NUMBER

left outer join MSS_WORK_ORDERS swo

on sdr.PLAN_ID = swo.PLAN_ID and sdr.WORK_ORDER_ID=swo.WORK_ORDER_ID

left outer join MSS_DEMANDS d

on pr.CONSUME_BY_TYPE='Demand Event' and pr.PLAN_ID = d.PLAN_ID and pr.CONSUME_BY_ID = d.DEMAND_ID

left outer join MSS_WO_DURATION_RESOURCES_V ddr

on pr.CONSUME_BY_TYPE='Work Order Operation' and pr.PLAN_ID=ddr.PLAN_ID and pr.CONSUME_BY_ID=ddr.WO_OPERATION_ID and pr.CONSUME_BY_RESOURCE_SEQ_NUMBER=ddr.RESOURCE_SEQ_NUMBER

left outer join MSS_WORK_ORDERS dwo

on ddr.PLAN_ID = dwo.PLAN_ID and ddr.WORK_ORDER_ID=dwo.WORK_ORDER_ID

left outer join MSS_DEMANDS dd

on pr.PLAN_ID = dd.PLAN_ID and pr.END_DEMAND_ID = dd.DEMAND_ID