GME Material Usage and Substitution Variance

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Material Usage and Substitution Variance Report of OPM
Application: Process Manufacturing Process Execution
Source: Material Usage and Substitution Variance (XML)
Short Name: GMEMUSBV_XML
DB package: GME_GMEMUSBV_XMLP_PKG
Run GME Material Usage and Substitution Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
                h.batch_no,
                im.concatenated_segments item_no,
                im.description item_desc1,
               d.revision,
                h.batch_id,
                h.wip_whse_code,
                h.formula_id ,
                h.actual_cmplt_date,
                h.recipe_validity_rule_id,
                rcp.recipe_no,
                rcp.recipe_version,
                im.primary_uom_code item_um ,
                d.plan_qty pqty,
                d.actual_qty aqty,
                d.dtl_um batchum,
                d.inventory_item_id ditemid,
                d.material_detail_id line_id,
                'U' var_type,
                    fd.formulaline_id,
                fd.detail_uom formum,
                GME_GMEMUSBV_XMLP_PKG.cf_batch_sizeformula(h.recipe_validity_rule_id, h.batch_id) CF_batch_size,
                GME_GMEMUSBV_XMLP_PKG.cf_itemumformula(h.recipe_validity_rule_id, h.batch_id) CF_itemum,
                /*GME_GMEMUSBV_XMLP_PKG.cf_total_varianceformula(:CS_tot_U_value_variance, :CS_tot_S_value_variance) CF_total_variance,*/
                GME_GMEMUSBV_XMLP_PKG.cf_wipcode() CF_wipcode,
                GME_GMEMUSBV_XMLP_PKG.cf_delete_transformula() CF_delete_trans,
                GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id) trans_qty,
                GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id) CF_actl_qty_itemum,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date) CF_acctg_cost,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctg_cost2,
                GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date)) CF_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_u_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date))) CF_U_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_s_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date))) CF_S_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_variance_pct(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, fd.detail_uom, d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct,
                GME_GMEMUSBV_XMLP_PKG.cf_uomformula(fd.detail_uom, d.dtl_um) CF_UOM,
                GME_GMEMUSBV_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
                (h.batch_status =  3  or h.batch_status =  4)
            and     h.batch_type = 0
            and     d.batch_id= h.batch_id
            and h.recipe_validity_rule_id = val.recipe_validity_rule_id
            and val.recipe_id = rcp.recipe_id
            and     ( d.line_type = -1   )
            and     f.formula_id= h.formula_id
            and     f.formula_id= fd.formula_id
            and     im.organization_id = d.organization_id
            and     im.inventory_item_id = d.inventory_item_id
            and     fd.line_type=-1
            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_1
            &CP_ItemRange_1
            UNION
            SELECT
                h.batch_no,
                im.concatenated_segments item_no,
                im.description item_desc1,
               d.revision,
                h.batch_id,
                h.wip_whse_code,
                h.formula_id ,
                h.actual_cmplt_date,
                h.recipe_validity_rule_id,
                rcp.recipe_no,
                rcp.recipe_version,
                im.primary_uom_code ,
                d.plan_qty pqty,
                d.actual_qty aqty,
                d.dtl_um batchum,
                d.inventory_item_id ditemid,
                d.material_detail_id line_id,
                'S' var_type,
                            0 formulaline_id,
                d.dtl_um formum,
                GME_GMEMUSBV_XMLP_PKG.cf_batch_sizeformula(h.recipe_validity_rule_id, h.batch_id) CF_batch_size,
                GME_GMEMUSBV_XMLP_PKG.cf_itemumformula(h.recipe_validity_rule_id, h.batch_id) CF_itemum,
                /*GME_GMEMUSBV_XMLP_PKG.cf_total_varianceformula(:CS_tot_U_value_variance, :CS_tot_S_value_variance) CF_total_variance,*/
                GME_GMEMUSBV_XMLP_PKG.cf_wipcode() CF_wipcode,
                GME_GMEMUSBV_XMLP_PKG.cf_delete_transformula() CF_delete_trans,
                GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id) trans_qty,
                GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id) CF_actl_qty_itemum,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date) CF_acctg_cost,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctg_cost2,
                GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date)) CF_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_u_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date))) CF_U_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_s_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(d.dtl_um, im.primary_uom_code, d.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(d.inventory_item_id, h.actual_cmplt_date))) CF_S_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_variance_pct(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(d.dtl_um, d.dtl_um , d.actual_qty, d.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct,
                GME_GMEMUSBV_XMLP_PKG.cf_uomformula(d.dtl_um, d.dtl_um) CF_UOM,
                GME_GMEMUSBV_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
                (h.batch_status =  3  or h.batch_status =  4)
            and     h.batch_type = 0
            and h.recipe_validity_rule_id = val.recipe_validity_rule_id
            and val.recipe_id = rcp.recipe_id
            and     d.batch_id= h.batch_id
            and     ( d.line_type = -1   )
            and     f.formula_id= h.formula_id
            and     im.organization_id = d.organization_id
            and im.inventory_item_id = d.inventory_item_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_1
            &CP_ItemRange_1
            UNION
            SELECT
                h.batch_no,
                im.concatenated_segments item_no,
                im.description item_desc1,
               fd.revision,
                h.batch_id,
                h.wip_whse_code,
                h.formula_id ,
                h.actual_cmplt_date,
                h.recipe_validity_rule_id,
                rcp.recipe_no,
                rcp.recipe_version,
                im.primary_uom_code item_um ,
                0 pqty,
                0 aqty,
                fd.detail_uom batchum,
                fd.inventory_item_id ditemid,
                0         line_id,
                'S' var_type,
                    fd.formulaline_id,
                fd.detail_uom formum,
                GME_GMEMUSBV_XMLP_PKG.cf_batch_sizeformula(h.recipe_validity_rule_id, h.batch_id) CF_batch_size,
                GME_GMEMUSBV_XMLP_PKG.cf_itemumformula(h.recipe_validity_rule_id, h.batch_id) CF_itemum,
                /*GME_GMEMUSBV_XMLP_PKG.cf_total_varianceformula(:CS_tot_U_value_variance, :CS_tot_S_value_variance) CF_total_variance,*/
                GME_GMEMUSBV_XMLP_PKG.cf_wipcode() CF_wipcode,
                GME_GMEMUSBV_XMLP_PKG.cf_delete_transformula() CF_delete_trans,
                GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id) trans_qty,
                GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id) CF_actl_qty_itemum,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(fd.inventory_item_id, h.actual_cmplt_date) CF_acctg_cost,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctg_cost2,
                GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_value_variance(fd.detail_uom, im.primary_uom_code, fd.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(fd.inventory_item_id, h.actual_cmplt_date)) CF_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_u_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(fd.detail_uom, im.primary_uom_code, fd.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(fd.inventory_item_id, h.actual_cmplt_date))) CF_U_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_s_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance(fd.detail_uom, im.primary_uom_code, fd.inventory_item_id, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(fd.inventory_item_id, h.actual_cmplt_date))) CF_S_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_variance_pct(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula(fd.detail_uom, fd.detail_uom, 0, fd.inventory_item_id), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(fd.formulaline_id, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct,
                GME_GMEMUSBV_XMLP_PKG.cf_uomformula(fd.detail_uom, fd.detail_uom) CF_UOM,
                GME_GMEMUSBV_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
                (h.batch_status =  3  or h.batch_status =  4)
            and     h.batch_type = 0
            and h.recipe_validity_rule_id = val.recipe_validity_rule_id
            and val.recipe_id = rcp.recipe_id
            and     f.formula_id= fd.formula_id
            and     ( fd.line_type = -1   )
            and     f.formula_id(+)= h.formula_id
            and     im.organization_id = fd.organization_id
            and im.inventory_item_id = fd.inventory_item_id
            and fd.formulaline_id NOT IN
                    (
					select formulaline_id
					from gme_material_details
							 where batch_id = h.batch_id
							  and line_type = -1
							  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_1
            &CP_ItemRange_1
            UNION
            SELECT
                h.batch_no,
                '0' item_no,
                '0' item_desc1,
               '' revision,
                h.batch_id,
                h.wip_whse_code,
                h.formula_id ,
                h.actual_cmplt_date,
                h.recipe_validity_rule_id,
                null recipe_no,
                null recipe_version,
                '0' item_um ,
                0 pqty,
                0 aqty,
                '0' batchum,
                0 ditemid,
                0   line_id,
                'L' var_type,
                    0 formulaline_id,
                '0' formum,
                GME_GMEMUSBV_XMLP_PKG.cf_batch_sizeformula(h.recipe_validity_rule_id, h.batch_id) CF_batch_size,
                GME_GMEMUSBV_XMLP_PKG.cf_itemumformula(h.recipe_validity_rule_id, h.batch_id) CF_itemum,
                /*GME_GMEMUSBV_XMLP_PKG.cf_total_varianceformula(:CS_tot_U_value_variance, :CS_tot_S_value_variance) CF_total_variance,*/
                GME_GMEMUSBV_XMLP_PKG.cf_wipcode() CF_wipcode,
                GME_GMEMUSBV_XMLP_PKG.cf_delete_transformula() CF_delete_trans,
                GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id) trans_qty,
                GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0) CF_actl_qty_itemum,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(0, h.actual_cmplt_date) CF_acctg_cost,
                GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost2formula(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_acctg_cost2,
                GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)) CF_quantity_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_value_variance('0', '0', 0, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(0, h.actual_cmplt_date)) CF_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_u_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance('0', '0', 0, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(0, h.actual_cmplt_date))) CF_U_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_s_value_varianceformula(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_value_variance('0', '0', 0, GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id)), GME_GMEMUSBV_XMLP_PKG.cf_acctg_cost(0, h.actual_cmplt_date))) CF_S_value_variance,
                GME_GMEMUSBV_XMLP_PKG.cf_variance_pct(GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id), GME_GMEMUSBV_XMLP_PKG.cf_quantity_variance(GME_GMEMUSBV_XMLP_PKG.cf_actl_qty_itemumformula('0', '0', 0, 0), GME_GMEMUSBV_XMLP_PKG.trans_qtyformula_005(0, h.recipe_validity_rule_id, h.formula_id, h.batch_id))) CF_variance_pct,
                GME_GMEMUSBV_XMLP_PKG.cf_uomformula('0', '0') CF_UOM,
                GME_GMEMUSBV_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.batch_type = 0
            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_1
            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 = -1
									 and d.inventory_item_id = im.inventory_item_id
									 &CP_ItemRange_1
                     	  )
            ORDER BY 1,2
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: