GME Material Usage and Substitution Variance

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