????? 在我的工作范围中，经常需要查询一下单位各个部门人员的工作量情况，主要涉及的工作包含：
????? 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'