GME Batch Yield Variance
Description
- Categories
- BI Publisher
- Application
- Process Manufacturing Process Execution
- Source
- Batch Yield Variance (XML)
- Short Name
- GMEBCHYV_XML
- DB package
- GME_GMEBCHYV_XMLP_PKG
Imported from BI Publisher
Batch Yield Variance Report of OPM
Batch Yield Variance Report of OPM
SQL 231 lines
select h.batch_id, d.material_detail_id line_id, h.formula_id, h.batch_no, d.inventory_item_id ditemid, d.revision, h.wip_whse_code HWIP, h.recipe_validity_rule_id, fd.qty pqty, d.actual_qty aqty, d.dtl_um md_batchum, fd.detail_uom md_formum, rcp.recipe_no, rcp.recipe_version, im.concatenated_segments item_no, im.description item_desc1, im.primary_uom_code itemum, d.inventory_item_id item_id, h.actual_cmplt_date actual_cmplt_date, fd.formulaline_id, GME_GMEBCHYV_XMLP_PKG.cf_formula_qty(h.recipe_validity_rule_id, h.batch_id) CF_formula_qty, GME_GMEBCHYV_XMLP_PKG.cf_delete_transformula() CF_delete_trans, GME_GMEBCHYV_XMLP_PKG.batchum_p batchum, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id) CF_plan_qty_itemum, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id) CF_actual_qty_um, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um) CF_UOM, GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(d.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctng_cost1, GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um), im.primary_uom_code, d.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id)) CF_acctg_cost2, GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance, GME_GMEBCHYV_XMLP_PKG.cf_value_variance(GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um), im.primary_uom_code, d.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id)), GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(d.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_value_variance, GME_GMEBCHYV_XMLP_PKG.cf_variance_pct(GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct, GME_GMEBCHYV_XMLP_PKG.CP_item_cost_p CP_item_cost from gme_batch_header h, gme_material_details d, fm_form_mst f, fm_matl_dtl fd, gmd_recipe_validity_rules val, gmd_recipes rcp, mtl_system_items_kfv im where d.batch_id = h.batch_id and (h.batch_status = 3 or h.batch_status = 4) and d.line_type between 1 and 2 and h.formula_id=f.formula_id and f.formula_id=fd.formula_id and h.recipe_validity_rule_id = val.recipe_validity_rule_id and val.recipe_id = rcp.recipe_id and im.inventory_item_id = d.inventory_item_id and im.organization_id = d.organization_id and d.inventory_item_id=fd.inventory_item_id and d.organization_id = fd.organization_id and d.formulaline_id = fd.formulaline_id and h.organization_id = :P_ORG_ID and ( h.plan_start_date >= :fromdate OR :fromdate IS NULL ) and (h.plan_start_date <= :todate OR :todate IS NULL ) &CP_BatchRange &CP_ItemRange UNION select h.batch_id, d.material_detail_id line_id, h.formula_id, h.batch_no, d.inventory_item_id ditemid, d.revision, h.wip_whse_code HWIP, h.recipe_validity_rule_id, 0 pqty, d.actual_qty aqty, d.dtl_um md_batchum, d.dtl_um md_formum, rcp.recipe_no, rcp.recipe_version, im.concatenated_segments item_no, im.description item_desc1, im.primary_uom_code itemum, d.inventory_item_id item_id, h.actual_cmplt_date actual_cmplt_date, 0 formulaline_id, GME_GMEBCHYV_XMLP_PKG.cf_formula_qty(h.recipe_validity_rule_id, h.batch_id) CF_formula_qty, GME_GMEBCHYV_XMLP_PKG.cf_delete_transformula() CF_delete_trans, GME_GMEBCHYV_XMLP_PKG.batchum_p batchum, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id) CF_plan_qty_itemum, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, d.dtl_um, d.actual_qty, d.inventory_item_id) CF_actual_qty_um, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um) CF_UOM, GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(d.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctng_cost1, GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um), im.primary_uom_code, d.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, d.dtl_um, d.actual_qty, d.inventory_item_id)) CF_acctg_cost2, GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, d.dtl_um, d.actual_qty, d.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance, GME_GMEBCHYV_XMLP_PKG.cf_value_variance(GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um), im.primary_uom_code, d.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, d.dtl_um, d.actual_qty, d.inventory_item_id)), GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(d.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_value_variance, GME_GMEBCHYV_XMLP_PKG.cf_variance_pct(GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(d.dtl_um, d.dtl_um, d.actual_qty, d.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct, GME_GMEBCHYV_XMLP_PKG.CP_item_cost_p CP_item_cost from gme_batch_header h, gme_material_details d, fm_form_mst f, gmd_recipe_validity_rules val, gmd_recipes rcp, mtl_system_items_kfv im where d.batch_id = h.batch_id and (h.batch_status = 3 or h.batch_status = 4) and d.line_type between 1 and 2 and h.formula_id=f.formula_id and h.recipe_validity_rule_id = val.recipe_validity_rule_id and val.recipe_id = rcp.recipe_id and im.inventory_item_id = d.inventory_item_id and im.organization_id = d.organization_id and d.formulaline_id IS NULL and h.organization_id = :P_ORG_ID and ( h.plan_start_date >= :fromdate OR :fromdate IS NULL ) and ( h.plan_start_date <= :todate OR :todate IS NULL ) &CP_BatchRange &CP_ItemRange UNION select h.batch_id, fd.formulaline_id, h.formula_id, h.batch_no, fd.inventory_item_id ditemid, fd.revision, h.wip_whse_code HWIP, h.recipe_validity_rule_id, fd.qty pqty, 0 aqty, ' ' md_batchum, fd.detail_uom md_formum, rcp.recipe_no, rcp.recipe_version, im.concatenated_segments item_no, im.description item_desc1, im.primary_uom_code itemum, fd.inventory_item_id item_id, h.actual_cmplt_date actual_cmplt_date, fd.formulaline_id, GME_GMEBCHYV_XMLP_PKG.cf_formula_qty(h.recipe_validity_rule_id, h.batch_id) CF_formula_qty, GME_GMEBCHYV_XMLP_PKG.cf_delete_transformula() CF_delete_trans, GME_GMEBCHYV_XMLP_PKG.batchum_p batchum, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id) CF_plan_qty_itemum, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', fd.detail_uom, 0, fd.inventory_item_id) CF_actual_qty_um, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, ' ') CF_UOM, GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(fd.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, ' '), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctng_cost1, GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, ' '), im.primary_uom_code, fd.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', fd.detail_uom, 0, fd.inventory_item_id)) CF_acctg_cost2, GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', fd.detail_uom, 0, fd.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance, GME_GMEBCHYV_XMLP_PKG.cf_value_variance(GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, ' '), im.primary_uom_code, fd.inventory_item_id, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', fd.detail_uom, 0, fd.inventory_item_id)), GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(fd.inventory_item_id, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(fd.detail_uom, ' '), im.primary_uom_code, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_value_variance, GME_GMEBCHYV_XMLP_PKG.cf_variance_pct(GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', fd.detail_uom, 0, fd.inventory_item_id), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct, GME_GMEBCHYV_XMLP_PKG.CP_item_cost_p CP_item_cost from gme_batch_header h, fm_matl_dtl fd, fm_form_mst f, gmd_recipe_validity_rules val, gmd_recipes rcp, mtl_system_items_kfv im where f.formula_id=fd.formula_id and h.recipe_validity_rule_id = val.recipe_validity_rule_id and val.recipe_id = rcp.recipe_id and (h.batch_status = 3 or h.batch_status = 4) and fd.line_type between 1 and 2 and h.formula_id=f.formula_id and im.inventory_item_id = fd.inventory_item_id and im.organization_id = fd.organization_id and fd.formulaline_id NOT IN ( select formulaline_id from gme_material_details where batch_id = h.batch_id and (line_type between 1 and 2) and formulaline_id IS NOT NULL ) and h.organization_id = :P_ORG_ID and ( h.plan_start_date >= :fromdate OR :fromdate IS NULL ) and ( h.plan_start_date <= :todate OR :todate IS NULL ) &CP_BatchRange &CP_ItemRange UNION select h.batch_id, 0 line_id, 0 formula_id, h.batch_no, 0 ditemid, ' ' revision, h.wip_whse_code HWIP, h.recipe_validity_rule_id, 0 pqty, 0 aqty, ' ' md_batchum, ' ' md_formum, null recipe_no, null recipe_version, ' ' item_no, ' ' item_desc1, ' ' itemum, 0 item_id, h.actual_cmplt_date actual_cmplt_date, 0 formulaline_id, GME_GMEBCHYV_XMLP_PKG.cf_formula_qty(h.recipe_validity_rule_id, h.batch_id) CF_formula_qty, GME_GMEBCHYV_XMLP_PKG.cf_delete_transformula() CF_delete_trans, GME_GMEBCHYV_XMLP_PKG.batchum_p batchum, GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id) CF_plan_qty_itemum, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', ' ', 0, 0) CF_actual_qty_um, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(' ', ' ') CF_UOM, GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(0, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(' ', ' '), ' ', GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id)) CF_acctng_cost1, GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(' ', ' '), ' ', 0, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', ' ', 0, 0)) CF_acctg_cost2, GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', ' ', 0, 0), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id)) CF_quantity_variance, GME_GMEBCHYV_XMLP_PKG.cf_value_variance(GME_GMEBCHYV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEBCHYV_XMLP_PKG.cf_uomformula(' ', ' '), ' ', 0, GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', ' ', 0, 0)), GME_GMEBCHYV_XMLP_PKG.cf_acctng_cost1formula(0, h.actual_cmplt_date, GME_GMEBCHYV_XMLP_PKG.cf_uomformula(' ', ' '), ' ', GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id))) CF_value_variance, GME_GMEBCHYV_XMLP_PKG.cf_variance_pct(GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id), GME_GMEBCHYV_XMLP_PKG.cf_quantity_variance(GME_GMEBCHYV_XMLP_PKG.cf_actual_qty_umformula(' ', ' ', 0, 0), GME_GMEBCHYV_XMLP_PKG.cf_plan_qty_itemumformula(0, h.recipe_validity_rule_id, 0, h.batch_id))) CF_variance_pct, GME_GMEBCHYV_XMLP_PKG.CP_item_cost_p CP_item_cost from gme_batch_header h where (h.batch_status = 3 or h.batch_status = 4) and h.recipe_validity_rule_id IS NULL and h.organization_id = :P_ORG_ID and ( h.plan_start_date >= :fromdate OR :fromdate IS NULL ) and ( h.plan_start_date <= :todate OR :todate IS NULL ) &CP_BatchRange and EXISTS ( SELECT 1 FROM gme_material_details d,mtl_system_items_kfv im WHERE d.batch_id = h.batch_id and d.organization_id = :P_ORG_ID and d.line_type IN (1,2) and d.inventory_item_id = im.inventory_item_id &CP_ItemRange ) ORDER BY 4, 14 |
Parameters 6
| Parameter | Type | List of values SQL |
|---|---|---|
| From Start Date | Date | |
| To Start Date | Date | |
| From Item | LOV Oracle | |
| To Item | LOV Oracle | |
| From Batch | LOV Oracle | |
| To Batch | LOV Oracle |