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)

Oracle EBS-SQL (BOM-14):检查工艺路线明细.sql,布布扣,bubuko.com

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