个人工序移动收集计划和手工票据收集计划工作量汇总SQL

      在我的工作范围中，经常需要查询一下单位各个部门人员的工作量情况，主要涉及的工作包含：

      1.工序移动收集计划；

      2.手工票据收集计划；

      下面的语句针对个人，查询出该人员在一段时间内的工作量情况，该人员有部分工作记在个人工作中，另一部分记住所属班组中，所以在收集计划中需要将两种情况分别考虑进来，最终汇总的结果才是正确的。

 

--1.工序移动收集计划-个人
select we.wip_entity_name 任务号,
       qr.character6 检验结果,
       qr.character10 员工姓名,
       qr.character27 员工号,
       qr.character17 日期,
       qr.character20 工序,
       bso.operation_description 工序说明,
       msi.segment1 物料编码,
       msi.description 物料说明,
       to_number(qr.character7) 数量,
       qr.character8 原因,
       cpp.per_price 定额,
       cpp.attribute1 工时,
       cpp.attribute1 * qr.character7 工时总数

  from qa_results              qr,
       mtl_system_items_b      msi,
       cux_process_prices      cpp,
       bom_standard_operations bso,
       wip_entities            we

 where msi.organization_id = 103
   and bso.organization_id = 103
   and we.organization_id = 103 --组织机构
   and qr.plan_id = 100 --收集计划
   and msi.inventory_item_id = qr.item_id --物料ID
   and cpp.product_code = msi.segment1 --物料编码
   and cpp.process_code = qr.character20 --工序代码
   and bso.operation_code = qr.character20 --工序代码
   and qr.wip_entity_id = we.wip_entity_id
   and qr.character27 = '05377' --员工号
      --and qr.character6='合格'
   and qr.character28 is null
   and qr.character20 in ('BP01', 'BG01') --工序代码
      --and qr.character10 like :p_xm
   and qr.character17 >= '2010/08/26' --日期
   and qr.character17 <= '2010/09/25'

union all
--2.工序移动收集计划-班组
select we.wip_entity_name 任务哈后,
       qr.character6 检验结果,
       ctl.employee_name 员工姓名,
       ctl.employee_id 员工号,
       qr.character17 日期,
       qr.character20 工序,
       bso.operation_description 工序说明,
       msi.segment1 物料编码,
       msi.description 物料说明,
       qr.character7 * ctl.distribution_quotiety / a.zs 数量,
       qr.character8 原因,
       cpp.per_price 定额,
       cpp.attribute1 工时,
       cpp.attribute1 * qr.character7 * ctl.distribution_quotiety / a.zs 工时总数

  from qa_results qr,
       mtl_system_items_b msi,
       cux_process_prices cpp,
       bom_standard_operations bso,
       cux_team_headers cth,
       cux_team_lines ctl,
       wip_entities we,
       (select ctl.header_id, sum(ctl.distribution_quotiety) zs
          from cux_team_lines ctl
         where ctl.distribution_flag = 'Y'
         group by ctl.header_id) a

 where msi.organization_id = 103
   and bso.organization_id = 103
   and we.organization_id = 103
   and qr.plan_id = 100
   and msi.inventory_item_id = qr.item_id
   and cpp.product_code = msi.segment1
   and cpp.process_code = qr.character20
   and bso.operation_code = qr.character20
   and cth.header_id = ctl.header_id
   and qr.character28 = cth.team_number
   and a.header_id = cth.header_id
   and ctl.employee_id = '05377'
   and qr.wip_entity_id = we.wip_entity_id
      --and qr.character6='合格'
   and qr.character20 in ('BP01', 'BG01')
      --and ctl.employee_name like :p_xm
   and qr.character17 >= '2010/08/26'
   and qr.character17 <= '2010/09/25'

union all
--3.手工票据收集计划-个人
select null 任务号,
       qr.character9 检验结果,
       qr.character12 员工姓名,
       qr.character11 员工号,
       qr.character20 日期,
       qr.character4 工序,
       bso.operation_description 工序说明,
       msi.segment1 物料编码,
       msi.description 物料说明,
       to_number(qr.character10) 数量,
       qr.character14 原因,
       cpp.per_price 定额,
       cpp.attribute1 工时,
       cpp.attribute1 * qr.character10 工时总数

  from qa_results              qr,
       mtl_system_items_b      msi,
       cux_process_prices      cpp,
       bom_standard_operations bso

 where msi.organization_id = 103
   and bso.organization_id = 103 --组织机构
   and qr.plan_id = 103 --收集计划
   and msi.inventory_item_id = qr.item_id --物料ID
   and cpp.product_code = msi.segment1 --物料编码
   and cpp.process_code = qr.character4 --工序代码
   and bso.operation_code = qr.character4 --工序代码
      --and qr.character9='合格'
   and qr.character23 is null
   and qr.character11 = '05377' --员工号
   and qr.character4 in ('BP01', 'BG01') --工序代码
      --and qr.character14 like :p_xm
   and qr.character20 >= '2010/08/26' --日期
   and qr.character20 <= '2010/09/25'

union all
--4.手工票据收集计划-班组
select null 任务号,
       qr.character9 检验结果,
       ctl.employee_name 员工姓名,
       ctl.employee_id 员工号,
       qr.character20 日期,
       qr.character4 工序,
       bso.operation_description 工序说明,
       msi.segment1 物料编码,
       msi.description 物料说明,
       qr.character10 * ctl.distribution_quotiety / a.zs 数量,
       qr.character14 原因,
       cpp.per_price 定额,
       cpp.attribute1 工时,
       cpp.attribute1 * qr.character10 * ctl.distribution_quotiety / a.zs 工时总数

  from qa_results qr,
       mtl_system_items_b msi,
       cux_process_prices cpp,
       bom_standard_operations bso,
       cux_team_headers cth,
       cux_team_lines ctl,
       (select ctl.header_id, sum(ctl.distribution_quotiety) zs
          from cux_team_lines ctl
         where ctl.distribution_flag = 'Y'
         group by ctl.header_id) a

 where msi.organization_id = 103
   and bso.organization_id = 103
   and qr.plan_id = 103
   and msi.inventory_item_id = qr.item_id
   and cpp.product_code = msi.segment1
   and cpp.process_code = qr.character4
   and bso.operation_code = qr.character4
   and cth.header_id = ctl.header_id
   and qr.character23 = cth.team_number
   and a.header_id = cth.header_id
   and ctl.employee_id = '05377'
      --and qr.character9='合格'
   and qr.character4 in ('BP01', 'BG01')
      --and ctl.employee_name like :p_xm
   and qr.character20 >= '2010/08/26'
   and qr.character20 <= '2010/09/25'