Oracle EBS-SQL ，BOM-14:检查工艺路线明细.sql

select

msi.segment1 装配件编码,

msi.description 装配件描述,

bos.operation_seq_num 序号,

bso.operation_code 工序代码,

bd.department_code 部门,

bos.operation_description 工序说明,

bor.completion_subinventory 完工子库,

bors.resource_seq_num 资源序号,

br.resource_code 资源代码,

br.description 资源说明,

br.unit_of_measure 资源单位,

bors.assigned_units 人数,

decode(bors.basis_type, '1', '物料', '2', '批次') 计费基准,

bors.usage_rate_or_amount 单位使用量,

bors.usage_rate_or_amount_inverse 倒数,

decode(bors.autocharge_type, '1', 'WIP移动', '2', '人工', '3','PO接收', '4', 'PO移动') 计费类型,

decode(bors.attribute1, 'Y', '是', 'N', '否') 是否预估

from inv.mtl_system_items_b msi,

bom.bom_departments bd,

bom.bom_resources br,

bom.bom_operational_routings bor,

bom.bom_operation_sequences bos,

bom.bom_operation_resources bors,

bom.bom_standard_operations bso

where msi.inventory_item_id = bor.assembly_item_id

and msi.organization_id = bor.organization_id

and bor.routing_sequence_id = bos.routing_sequence_id

and bso.standard_operation_id(+) = bos.standard_operation_id

and bos.department_id = bd.department_id

and bd.organization_id = bor.organization_id

and bors.operation_sequence_id = bos.operation_sequence_id(+)

and bors.resource_id = br.resource_id(+)

and bor.organization_id(+) = br.organization_id

and msi.organization_id = x

and msi.inventory_item_status_code <> 'Inactive'

and bos.disable_date is null

union

select msi.segment1 装配件编码,

msi.description 装配件描述,

bos.operation_seq_num 序号,

bso.operation_code 工序代码,

bd.department_code 部门,

bos.operation_description 工序说明,

bor.completion_subinventory 完工子库,

to_number('') 资源序号,

'' 资源代码,

'' 资源说明,

'' 资源单位,

to_number('') 人数,

'' 计费基准,

to_number('') 单位使用量,

to_number('') 倒数,

'' 计费类型,

'' 是否预估

from inv.mtl_system_items_b msi,

bom.bom_departments bd,

bom.bom_operational_routings bor,

bom.bom_operation_sequences bos,

bom.bom_standard_operations bso

where msi.inventory_item_id = bor.assembly_item_id

and msi.organization_id = bor.organization_id

and bor.routing_sequence_id = bos.routing_sequence_id

and bso.standard_operation_id(+) = bos.standard_operation_id

and bos.department_id = bd.department_id

and bd.organization_id = bor.organization_id

and msi.organization_id = x

and msi.inventory_item_status_code <> 'Inactive'

and bos.disable_date is null

and not exists

(select 'X'

from bom.bom_operation_resources bors

where bors.operation_sequence_id = bos.operation_sequence_id)