GME Batch Yield Variance

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Batch Yield Variance Report of OPM
Application: Process Manufacturing Process Execution
Source: Batch Yield Variance (XML)
Short Name: GMEBCHYV_XML
DB package: GME_GMEBCHYV_XMLP_PKG
Run GME Batch Yield Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
From Start Date
 
Date
To Start Date
 
Date
From Item
 
LOV Oracle
To Item
 
LOV Oracle
From Batch
 
LOV Oracle
To Batch
 
LOV Oracle
Download
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™