GME Material Usage and Substitution Variance

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Process Execution
Source: Material Usage and Substitution Variance (XML)
Short Name: GMEMUSBV_XML
DB package: GME_GMEMUSBV_XMLP_PKG
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,*/'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,*/'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
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