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