oracle EBS PL/sql 查看工单的配方工艺路线

oracle ebs batch

以下语句：

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 gmbh.organization_id = 101
-- and msib.ITEM_CODE in( '123213' )
and gmbh.batch_no in (10035438)
and to_char(gmbh.ACTUAL_START_DATE,'yyyy-mm-dd') between '2019-10-01' and '2019-10-31'