1. Tables and Views for SCM
  2. MSC_AP_WD_OP_MATERIALS2_V

MSC_AP_WD_OP_MATERIALS2_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

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

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(wm.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 = wom.comp_uom_code and woo1.uom_code= assm.uom_code then

(wom.material_quantity/woo1.output_quantity)

else

( decode(wom.uom_code, wom.comp_uom_code, wom.material_quantity,

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

decode(woo1.uom_code, assm.uom_code, woo1.output_quantity,

inv_convert.inv_um_convert(wom.assembly_item_id, 10,woo1.output_quantity, woo1.uom_code, assm.uom_code,null, null))

)

end) ELSE -99999 END,-99999)

ELSE

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

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

wom.comp_uom_code uom_code,

nvl(wom.yield_factor,1) yield_factor,

wom.basis_type,

wom.supply_type,

wom.work_definition_id,

wom.wd_operation_id,

wom.bill_sequence_id,

wom.operation_seq_number,

wom.find_number,

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

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

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

then null

else

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

wom.component_sequence_id,

wom.assembly_organization_id 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,

wdn.work_definition_code,

assm.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,

wom.component_name,

assm.item_number assembly_name

FROM first_version ,

msc_wis_wd_op_materials_v wom,

msc_planned_egp_system_items assm,

wis_work_definition_names_b wdn,

wis_work_methods_b wm,

(

select woo.*,wob1.effective_from_date output_op_eff_from_date,

nvl(wob1.effective_to_date, sysdate+36500) output_op_eff_to_date

from

wis_wd_operation_outputs woo ,

wis_wd_operations_v wob1,

wis_work_definitions_v wd1

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_CODE = 'PROCESS_MANUFACTURING'

and wob1.wd_operation_id = woo.wd_operation_id

) woo1,

last_version

WHERE

wom.work_definition_id = first_version.work_definition_id

AND wom.work_definition_id = last_version.work_definition_id

AND wom.assembly_item_id = assm.inventory_item_id

AND wom.assembly_organization_id = assm.organization_id

AND wom.status_code IN ('ACTIVE','PENDING_INACTIVE')

AND wdn.work_definition_name_id = wom.work_definition_name_id

AND wdn.used_in_planning_flag = 'Y'

AND wm.work_method_id = wom.work_method_id

AND wm.work_method_code NOT IN ('MAINTENANCE')

AND wom.work_definition_id = woo1.work_definition_id(+)

AND wom.assembly_organization_id = woo1.organization_id(+)

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

and wom.effective_from_date <= decode(wm.WORK_METHOD_CODE,'PROCESS_MANUFACTURING',woo1.output_op_eff_to_date,wom.effective_from_date)