oracle-EBS-PL/sql 生产 工单明细开始结束时间工艺路线处方

时长需要查看一张工单的整体状态，时间，所用的产品编号，工艺路线，处方等，可以通过下列语句来捞取数据

SELECT  
  org.operating_unit  ou_id,
  Hou.Name Ou_Name,
  Hou.Short_Code ou_code,
  gmbh.organization_id "IO_ID",
  org.organization_code "IO_Code", 
   gmbh.Batch_No,gmbh.batch_id,
    to_char(gmbh.CREATION_DATE,'yyyy-mm-dd hh24:mi:ss') create_date,
   to_char(gmbh.actual_start_date,'yyyy-mm-dd hh24:mi:ss') actual_start_date, 
   to_char(gmbh.actual_cmplt_date,'yyyy-mm-dd hh24:mi:ss') actual_cmplt_date, 
  to_char(gbs.actual_start_date,'yyyy-mm-dd hh24:mi:ss')  STEP_actual_start_date, 
  to_char(gbs.actual_cmplt_date,'yyyy-mm-dd hh24:mi:ss')  STEP_actual_cmplt_date, 
    ROUND((gmbh.actual_cmplt_date-gmbh.actual_start_date)*24,2) diffhours,
  ROUND((gmbh.actual_cmplt_date-gmbh.actual_start_date)*24*60)-ROUND((gbs.actual_cmplt_date-gbs.actual_start_date)*24*60) DIFF,
  gmbh.batch_status,
  DECODE(gmbh.batch_status,-1,'CANCELLED',1,'PENDING',2,'WIP',3,'Completed','4','CLOSED') "Batch_Status", 
  GMBH.RECIPE_VALIDITY_RULE_ID,   
  rec.recipe_no, 
  rec.recipe_version, 
  rec.RECIPE_DESCRIPTION, 
  fm.scale_type, 
  fm.formula_no, 
  fm.formula_vers, 
  fm.formula_desc1 formula_desc, 
  grout.routing_no, 
  grout.routing_vers, 
  grout.routing_desc, 
  gor.RESOURCES,
  gor.RESOURCE_COUNT,
  gbs.ATTRIBUTE1 manul_count,
  mtr.reason_name, 
  msib.segment1 item_number, 
  msib.DESCRIPTION Item_description, 
  GMD.SUBINVENTORY, 
  GMD.LOCATOR_ID, 
  CASE 
		WHEN GMD.locator_id IS NOT NULL 
		THEN mil.segment1 
			||'.' 
			||mil.segment2 
			||'.' 
			||mil.segment3 
			||'.' 
			||mil.segment4 
		ELSE NULL 
	END locator , 
	mil.description locator_description, 
  DECODE(gmd.Line_type,-1,'配料',1,'产品',2,'副产品',NULL) "Item_Type",
  gmd.Line_type,
  gmd.dtl_um UOM, 
  gmd.plan_qty planned_qty, 
  gmd.wip_plan_qty wip_planned_qty, 
  gmd.actual_qty Actual_Qty, 
  ROUND((NVL(gmd.plan_qty,0)-(NVL(gmd.actual_qty,0)))/(DECODE(gmd.plan_qty,0,NULL,gmd.plan_qty))*100,4) "Percentage Qty Variance" 
FROM  
  GME_BATCH_HEADER gmbh 
  LEFT join org_organization_definitions org on org.organization_id = gmbh.organization_id  
  LEFT join gme_material_details gmd on gmd.batch_id = gmbh.batch_id and gmd.organization_id = gmbh.organization_id 
  left join Hr_Operating_Units Hou on org.operating_unit=Hou.organization_id
  left join gmd_routings grout on grout.routing_id = gmbh.routing_id 
  left join FM_ROUT_DTL frd on grout.ROUTING_ID =frd.ROUTING_ID  
  left join GMD_OPERATION_ACTIVITIES goa on  frd.OPRN_ID = goa.OPRN_ID and goa.ACTIVITY like 'L%'
  left join GMD_OPERATION_RESOURCES gor on goa.OPRN_LINE_ID = gor.OPRN_LINE_ID and gor.RESOURCES like '%DL%'
  left join gmd_recipe_validity_rules rec_val on rec_val.recipe_validity_rule_id = gmbh.recipe_validity_rule_id 
  left join gmd_recipes rec on rec.recipe_id = rec_val.recipe_id 
  left join fm_form_mst  fm on FM.FORMULA_ID = gmbh.formula_id and FM.FORMULA_ID = Rec.FORMULA_ID   
  left join mtl_transaction_reasons mtr on mtr.reason_id = GMBH.TERMINATE_REASON_ID 
  LEFT join mtl_system_items_b msib on gmd.inventory_item_id = msib.INVENTORY_ITEM_ID and gmd.organization_id = msib.organization_id
  left Join Mtl_Item_Locations Mil	On gmd.Organization_Id     = Mil.Organization_Id 
  and gmd.subinventory = mil.Subinventory_Code 
  and mil.Inventory_Location_Id = gmd.locator_id
  LEFT join gme_batch_steps gbs on gbs.batch_id = gmbh.batch_id   
  where 1=1
 -- gmbh.organization_id = 119  
  -- and msib.ITEM_CODE in( '102110552' )
  --and gmbh.batch_no in (10035438)
  -- and trunc(gmbh.ACTUAL_START_DATE) between to_date('20190401', 'RRRRMMDD')  and to_date('20190430', 'RRRRMMDD') 
   and to_char(gmbh.ACTUAL_START_DATE,'yyyy-mm-dd') between '2020-12-01'  and '2020-12-31'
  --and gbs.actual_start_date <> gbs.actual_cmplt_date
   --and gmbh.batch_status not in ( 3,4)