工作台未发放任务工时SQL

      各个部门经常需要查询一段时间范围内任务的工时，平时进行查询时，只需要计划任务中对工时进行统计即可，但这样就不会包含在工作台上未发放的任务，下面语句将工作台上的工时也计算出来，与计划工时汇总，即可得出所要求的工时。

 

select rou.standard_operation_code 标准工序,
       bso.operation_description 工序参考说明,
       sum(mos.quantity_rate) 数量,
       round(sum(mos.quantity_rate * cpp.attribute1), 0) 工时_分钟,
       round(sum(mos.quantity_rate * cpp.attribute1) / 60, 2) 工时_小时,
       '工作台未发放' 标志
  from xxxxx_workbench_new mos,--工作台任务视图
       (select msi.inventory_item_id,
               msi.description,
               bos.operation_seq_num,
               bos.standard_operation_code,
               bos.operation_description
          from mtl_system_items_b        msi, --物料主表
               bom_operational_routings  bor, --工艺路线表
               bom_operation_sequences_v bos --工艺路线明细表
         where msi.organization_id = 104
           and msi.inventory_item_status_code = 'Active'
              
           and msi.inventory_item_id = bor.assembly_item_id
           and bor.organization_id = 104
           and bor.alternate_routing_designator is null
              
           and bor.routing_sequence_id = bos.routing_sequence_id
           and bos.disable_date is null) rou,
       apps.cux_process_prices cpp, --存放工时及定额表
       bom_standard_operations bso --标准工序
 where 1 = 1
      -------------------------------------------------------------------------------------------
      --and mos.new_due_date < trunc(:p_end_date + 1)
   and mos.new_due_date < trunc(sysdate + 1)
      -------------------------------------------------------------------------------------------
   and mos.order_type = 5 --计划单
   and mos.organization_id = 104 --组织机构
   and mos.inventory_item_id = rou.inventory_item_id
      
   and mos.item_segments = cpp.product_code --物料编码
   and rou.standard_operation_code = cpp.process_code --工序
      
   and rou.standard_operation_code = bso.operation_code --工序
   and bso.organization_id = 104 --组织机构
 group by rou.standard_operation_code, bso.operation_description