Introduction/ Issue:
This SQL query is designed to extract Batch Header and Material Details from Oracle Process Manufacturing (OPM). It consolidates information such as company code, plant, batch number, recipe, routing, and formula details along with summarized ingredient, product, and by-product quantities.
Why we need:
This SQL query is designed to extract Batch Header and Material Details from Oracle Process Manufacturing (OPM).
How do we solve:
SELECT
xep.name AS co_code, — Co Code
bh.plant_code AS plant_code, — Plant Code
bh.batch_no AS batch_no, — Batch No
bh.BATCH_STATUS_DESC AS batch_status_desc, — Batch Status Desc
bh.recipe_no AS recipe_no, — Recipe No
bh.recipe_version AS recipe_version, — Recipe Version
bh.recipe_description AS recipe_description, — Recipe Description
bh.routing_no AS routing_no, — Routing No
bh.routing_vers AS routing_vers, — Routing Vers
bh.routing_desc AS routing_desc, — Routing Desc
bh.formula_no AS formula_no, — Formula No
bh.formula_vers AS formula_vers, — Formula Vers
bh.formula_desc1 AS formula_desc1, — Formula Desc1
— Original Qty Sums if wip then wip qty, pending plan qty
SUM(CASE WHEN bh.batch_status = 1 and md.line_type = -1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = -1 THEN NVL(md.wip_plan_qty,0)
end) AS ing_qty_sum,
SUM(CASE WHEN bh.batch_status = 1 and md.line_type = 1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = 1 THEN NVL(md.wip_plan_qty,0)
end) AS prd_qty_sum,
SUM(CASE WHEN bh.batch_status = 1 and md.line_type = 1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = 1 THEN NVL(md.wip_plan_qty,0)
end) AS byprd_qty_sum,
— Ingredient Qty in KG
SUM(
CASE
WHEN UPPER(md.DTL_UM) <> ‘KG’
THEN apps.inv_convert.inv_um_convert (
md.Inventory_Item_Id,
NULL,
NVL(md.original_qty, 0),
md.DTL_UM,
‘KG’,
NULL,
NULL
)
ELSE (CASE WHEN bh.batch_status = 1 and md.line_type = -1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = -1 THEN NVL(md.wip_plan_qty,0)
end)
END
) AS ing_qty_kg_sum,
— Product Qty in KG
SUM(
CASE
WHEN UPPER(md.DTL_UM) <> ‘KG’
THEN apps.inv_convert.inv_um_convert (
md.Inventory_Item_Id,
NULL,
NVL(md.original_qty, 0),
md.DTL_UM,
‘KG’,
NULL,
NULL
)
ELSE (CASE WHEN bh.batch_status = 1 and md.line_type = 1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = 1 THEN NVL(md.wip_plan_qty,0)
end)
END
) AS prd_qty_kg_sum,
— Byproduct Qty in KG
SUM(
CASE
WHEN UPPER(md.DTL_UM) <> ‘KG’
THEN apps.inv_convert.inv_um_convert (
md.Inventory_Item_Id,
NULL,
NVL(md.original_qty, 0),
md.DTL_UM,
‘KG’,
NULL,
NULL
)
ELSE (CASE WHEN bh.batch_status = 1 and md.line_type = 1 THEN NVL(md.plan_qty,0)
WHEN bh.batch_status = 2 and md.line_type = 1 THEN NVL(md.wip_plan_qty,0)
end)
END
) AS byprd_qty_kg_sum,
— Actual Product Qty
SUM(DECODE(md.line_type, 1, NVL(md.actual_qty, 0), 0)) AS act_prd_qty_sum,
NULL AS gl_class,
NULL AS gl_class_desc,
NULL AS gl_business_class,
NULL AS gl_business_class_desc,
bh.actual_start_date,
bh.actual_cmplt_date,
bh.batch_id,
bh.batch_status
FROM
gme_batch_header_vw bh,
gme_material_details md,
org_organization_definitions ood,
xle_entity_profiles xep
WHERE
bh.batch_id = md.batch_id
AND md.organization_id = ood.organization_id
AND bh.organization_id = md.organization_id
AND ood.legal_entity = xep.legal_entity_id
AND bh.batch_status in (1,2)
AND bh.plant_code = :Plant_Code
AND bh.delete_mark = 0
GROUP BY
xep.name,
bh.plant_code,
bh.batch_no,
bh.BATCH_STATUS_DESC,
bh.recipe_no,
bh.recipe_version,
bh.recipe_description,
bh.routing_no,
bh.routing_vers,
bh.routing_desc,
bh.formula_no,
bh.formula_vers,
bh.formula_desc1,
bh.actual_start_date,
bh.actual_cmplt_date,
bh.batch_id,
bh.batch_status,
NULL
ORDER BY
bh.plant_code,
bh.batch_no
