GME Batch Ticket

select       mp.organization_code plant_code,
                            pm_btch_hdr.batch_id,
                            pm_btch_hdr.routing_id,
                pm_btch_hdr.batch_no,
                fm_form_mst.formula_no,
                fm_form_mst.formula_vers,
                rt.routing_no,
                rt.routing_vers,
                rcp.recipe_no,
                rcp.recipe_version,
                pm_btch_hdr.plan_start_date,
                pm_btch_hdr.plan_cmplt_date expct_cmplt_date,
                pm_btch_hdr.due_date,
                pm_btch_hdr.wip_whse_code,
                pm_matl_dtl.line_no,
                msik.concatenated_segments item_no,
                            pm_matl_dtl.revision,
                msik.description item_desc1,
                            pm_matl_dtl.subinventory,
                            mil.concatenated_segments locator,
                pm_matl_dtl.plan_qty,
                pm_matl_dtl.dtl_um item_um,
                            pm_matl_dtl.line_type,
                gem_lookups.meaning,
                pm_btch_hdr.text_code textcode1,
                pm_matl_dtl.text_code textcode2,
                GME_GMEBCHTK_XMLP_PKG.cf_subinv_labelformula(pm_matl_dtl.line_type) CF_subinv_label,
                gem_lookups.meaning meaningCF,
                GME_GMEBCHTK_XMLP_PKG.flagcfformula(pm_matl_dtl.text_code) flagCF,
                GME_GMEBCHTK_XMLP_PKG.R_batchFormatTrigger(pm_btch_hdr.batch_no) R_batch_ft
            from    fm_form_mst,
                mtl_system_items_kfv msik,
                gmd_routings_b rt,
                gmd_recipes rcp,
                gmd_recipe_validity_rules rcpv,
                gme_batch_header pm_btch_hdr,
                gme_material_details pm_matl_dtl,
                gem_lookups,
                            mtl_item_locations_kfv mil,
                            mtl_parameters mp
            where   pm_btch_hdr.organization_id = mp.organization_id
               and        mp.organization_id = :P_ORG_ID
               and  fm_form_mst.formula_id  (+) = pm_btch_hdr.formula_id
               and       pm_matl_dtl.batch_id = pm_btch_hdr.batch_id
               and      gem_lookups.lookup_type= 'LINE_TYPE'
               and      gem_lookups.lookup_code = pm_matl_dtl.line_type
               and        pm_matl_dtl.locator_id = mil.inventory_location_id(+)
               and        pm_matl_dtl.organization_id = mil.organization_id(+)
               and        msik.organization_id = pm_matl_dtl.organization_id
               and      msik.inventory_item_id = pm_matl_dtl.inventory_item_id
               and      pm_btch_hdr.delete_mark = 0
               and      pm_btch_hdr.batch_type = 0
               and      pm_btch_hdr.batch_status != -1
               and        pm_btch_hdr.routing_id = rt.routing_id(+)
               and        pm_btch_hdr.recipe_validity_rule_id = rcpv.recipe_validity_rule_id(+)
               and        rcpv.recipe_id = rcp.recipe_id(+)
               and      &RangeCF1
               and      ( pm_btch_hdr.plan_start_date >= :from_start_date_1 OR :from_start_date_1 IS NULL)
               and         ( pm_btch_hdr.plan_start_date < (:to_start_date_1+1) OR :to_start_date IS NULL)
            order by &OrdCF