GME Batch Yield Variance

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Process Execution
Source: Batch Yield Variance (XML)
Short Name: GMEBCHYV_XML
DB package: GME_GMEBCHYV_XMLP_PKG
' ' 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
Organization
 
Number
To Batch
 
LOV Oracle
From Batch
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
To Start Date
 
Date
From Start Date
 
Date