工作台未发放任务工时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?
