1. Tables and Views for SCM
  2. MSC_AP_WD_OP_MATERIALS_V

MSC_AP_WD_OP_MATERIALS_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

WD_OPERATION_MATERIAL_ID

MATERIAL_SEQ_NUMBER

MATERIAL_TYPE

OUTPUT_OP_EFF_FROM_DATE

OUTPUT_OP_EFF_TO_DATE

MATERIAL_QUANTITY

UOM_CODE

YIELD_FACTOR

BASIS_TYPE

SUPPLY_TYPE

WORK_DEFINITION_ID

WD_OPERATION_ID

BILL_SEQUENCE_ID

OPERATION_SEQ_NUMBER

FIND_NUMBER

EFFECTIVE_FROM_DATE

OP_EFFECTIVE_FROM_DATE

OP_EFFECTIVE_TO_DATE

EFFECTIVE_TO_DATE

COMPONENT_SEQUENCE_ID

ORGANIZATION_ID

INVENTORY_ITEM_ID

SUPPLY_SUBINVENTORY_NAME

SUPPLY_LOCATOR_ID

COMPONENT_SEQUENCE_ID_PATH

WORK_DEFINITION_CODE

ORGANIZATION_CODE

OPTIONAL

CHECK_ATP

OPERATION_OFFSET_PERCENT

REVISION

PLANNING_FACTOR

COMPONENT_TYPE

ITEM_STRUCT_COMPONENT_REF_FLAG

ROUNDING_DIRECTION

COMPONENT_NAME

ASSEMBLY_NAME

LINE_OP_SEQ_NUMBER

Query

SQL_Statement

with first_version as (

select

wv.work_definition_id, min(wv.effective_from_date) effective_from_date

from WIS_WD_VERSIONS wv

WHERE WV.APPROVAL_STATUS_CODE='APPROVED'

group by wv.work_definition_id

),

last_version as (

select

wv.work_definition_id,

MAX(NVL(WV.EFFECTIVE_TO_DATE, TO_DATE(3457147, 'J'))) effective_to_date

from WIS_WD_VERSIONS_V wv

group by wv.work_definition_id

)

SELECT wom.wd_operation_material_id,

wom.material_seq_number,

wom.material_type,

woo1.output_op_eff_from_date output_op_eff_from_date,

woo1.output_op_eff_to_date output_op_eff_to_date,

(

CASE WHEN (DECODE(wd.WORK_METHOD_CODE,'PROCESS_MANUFACTURING', 2, 1) = 2 and woo1.output_quantity is not null and wom.basis_type=1)

THEN nvl(CASE WHEN woo1.output_quantity <> 0

THEN (

case when wom.uom_code= esi1.uom_code and woo1.uom_code= esi2.uom_code then

(wom.material_quantity/woo1.output_quantity)

else

( decode(wom.uom_code, esi1.uom_code, wom.material_quantity,

inv_convert.inv_um_convert(esi1.inventory_item_id, 10, wom.material_quantity, wom.uom_code, esi1.uom_code,null, null))/

decode(woo1.uom_code, esi2.uom_code, woo1.output_quantity,

inv_convert.inv_um_convert(esi2.inventory_item_id, 10,woo1.output_quantity, woo1.uom_code, esi2.uom_code,null, null))

)

end) ELSE -99999 END,-99999)

/* nvl(decode(wom.uom_code, esi1.primary_uom_code, (wom.material_quantity/woo1.output_quantity),

decode((wom.material_quantity/woo1.output_quantity), null, null,

inv_convert.inv_um_convert(esi1.inventory_item_id, 10, wom.material_quantity, wom.uom_code, esi1.primary_uom_code,null, null)/

inv_convert.inv_um_convert(esi2.inventory_item_id, 10, woo1.output_quantity, woo1.uom_code, esi2.primary_uom_code,null, null)) ), -99999)

ELSE -99999 END)*/

ELSE

nvl(decode(wom.uom_code, esi1.uom_code, wom.material_quantity,

decode(wom.material_quantity, null, null, inv_convert.inv_um_convert(esi1.inventory_item_id, 10, NVL(wom.material_quantity,0), wom.uom_code, esi1.uom_code,null, null)) ), -99999) END)material_quantity,

esi1.uom_code,

nvl(wom.yield_factor,1) yield_factor,

wom.basis_type,

wom.supply_type,

wd.work_definition_id,

wom.wd_operation_id,

wd.bill_sequence_id,

wob.operation_seq_number,

wom.find_number,

greatest( nvl(wom.EFFECTIVITY_DATE ,greatest(wob.effective_from_date, first_version.effective_from_date)),greatest(wob.effective_from_date, first_version.effective_from_date)) EFFECTIVE_FROM_DATE,

greatest(wob.effective_from_date, first_version.effective_from_date) op_EFFECTIVE_FROM_DATE,

wob.effective_to_date op_EFFECTIVE_TO_DATE,

(case when (wom.disable_date is null and WOB.EFFECTIVE_TO_DATE is null )

then null

else

least(NVL(wom.disable_date,TO_DATE(3457147, 'J')),LEAST(nvl(wob.effective_to_date,TO_DATE(3457147, 'J')), last_version.effective_to_date) )END) effective_to_date,

wom.component_sequence_id,

wd.organization_id,

wom.inventory_item_id,

wom.supply_subinventory_name,

wom.supply_locator_id,

TO_CHAR(wom.component_sequence_id_path) component_sequence_id_path,

wd.work_definition_code,

esi2.organization_code,

NVL(wom.optional, 2) optional,

NVL(wom.check_atp, 2) check_atp,

NULL operation_offset_percent,

NULL revision,

wom.planning_factor,

1 component_type,

DECODE(wom.adhoc_flag,'Y',2, 'N', 1, 1) item_struct_component_ref_flag,

NVL(wom.enforce_int_requirements,0) rounding_direction,

esi1.item_number component_name,

esi2.item_number assembly_name,

wob.line_op_seq_number

FROM wis_work_definitions_v wd,

wis_wd_operations_v wob,

first_version ,

wis_wd_operation_materials_v wom,

msc_planned_egp_system_items esi1,

msc_planned_egp_system_items esi2,

(

SELECT

woo.*,

wop1.effective_from_date output_op_eff_from_date,

nvl(wop1.effective_to_date, sysdate+36500) output_op_eff_to_date

FROM

wis_wd_operation_outputs woo ,

wis_work_definitions wd1,

wis_work_methods_b wwm,

wis_wd_operations_v wop1

WHERE woo.primary_flag = 'Y'

AND wd1.work_Definition_id = woo.work_Definition_id

AND wd1.organization_id = woo.organization_id

AND wd1.work_method_id = wwm.work_method_id

AND wwm.work_method_code = 'PROCESS_MANUFACTURING'

AND wop1.wd_operation_id = woo.wd_operation_id

) woo1,

last_version

WHERE wob.wd_operation_id = wom.wd_operation_id

AND wom.organization_id = esi1.organization_id

AND wom.inventory_item_id = esi1.inventory_item_id

AND wd.organization_id = esi2.organization_id

AND wd.inventory_item_id = esi2.inventory_item_id

AND wd.work_definition_id = wob.work_definition_id

AND wd.work_definition_id = first_version.work_definition_id

AND wd.work_definition_id = last_version.work_definition_id

AND wd.used_in_planning_flag = 'Y'

AND wd.STATUS_CODE IN ('ACTIVE','PENDING_INACTIVE')

AND (wd.work_method_code IN ('DISCRETE_MANUFACTURING','PROCESS_MANUFACTURING') OR (wd.work_method_code = 'FLOW_MANUFACTURING' and nvl(wob.auto_transact_flag,'N') != 'Y'))

AND wd.work_definition_id = woo1.work_definition_id(+)

AND wd.organization_id = woo1.organization_id(+)

AND nvl(wob.effective_to_date, sysdate+36500) >= decode(wd.WORK_METHOD_CODE,'PROCESS_MANUFACTURING',woo1.output_op_eff_from_date, sysdate-36500)

and wob.effective_from_date <= decode(wd.WORK_METHOD_CODE,'PROCESS_MANUFACTURING',woo1.output_op_eff_to_date,wob.effective_from_date)