GME Batch Pick List

SELECT 
mr.subinventory_code whse_code ,
mil.concatenated_segments location,
msik.concatenated_segments item_no,
'['||msik.description||']' item_desc1,
mp.organization_code plant_code,
h.batch_no,
rcp.recipe_no,
rcp.recipe_version,
d.line_no,
d.inventory_item_id item_id,
to_char(h.plan_start_date ,'DD-MON-YYYY HH24:MI:SS') PLAN_START_DATE,
to_char(h.actual_start_date ,'DD-MON-YYYY HH24:MI:SS') actual_start_date,
d.plan_qty ,
d.dtl_um item_um1_1, 
d.plan_qty plan_qty2,
d.item_um2 item_um2 ,
d.revision,
mr.lot_number lot_no,
mr.primary_reservation_quantity trans_qty,
msik.primary_uom_code item_um4, 
mr.secondary_reservation_quantity trans_qty2,
msik.secondary_uom_code item_um2_1, 
	GME_GMEBCHPL_XMLP_PKG.cf_plan_qty2formula(d.dtl_um, d.item_um2, d.inventory_item_id, d.plan_qty) CF_plan_qty2, 
	GME_GMEBCHPL_XMLP_PKG.cf_actual_start_dateformula(h.actual_start_date) CF_actual_start_date, 
--	GME_GMEBCHPL_XMLP_PKG.cf_sum_trans_qtyformula(:Sum_trans_qty) CF_sum_trans_qty, 
--	GME_GMEBCHPL_XMLP_PKG.cf_sum_trans_qty2formula(:Sum_trans_qty2) CF_sum_trans_qty2, 
	GME_GMEBCHPL_XMLP_PKG.cf_inv_plan_qtyformula(d.dtl_um, msik.primary_uom_code, d.inventory_item_id, d.plan_qty) CF_inv_plan_qty, 
	GME_GMEBCHPL_XMLP_PKG.cf_trans_qtyformula(mr.primary_reservation_quantity) CF_trans_qty, 
	GME_GMEBCHPL_XMLP_PKG.cf_trans_qty2formula(mr.secondary_reservation_quantity) CF_trans_qty2
FROM 
mtl_system_items_kfv msik,
gmd_recipes rcp,
gmd_recipe_validity_rules val, 
gme_batch_header h, 
gme_material_details d, 
mtl_reservations mr,
mtl_item_locations_kfv mil,
mtl_parameters mp                  
WHERE  
	h.recipe_validity_rule_id = val.recipe_validity_rule_id(+)
and           mp.organization_id = h.organization_id
and           val.recipe_id = rcp.recipe_id (+)
and           mr.organization_id = mil.organization_id(+)
and           mr.locator_id = mil.inventory_location_id(+)
and           mr.inventory_item_id (+) = d.inventory_item_id
and           mr.demand_source_type_id (+) = 5
and           mr.demand_source_header_id (+) = d.batch_id
and 	mr.demand_source_line_id (+) = d.material_detail_id
and           d.organization_id = msik.organization_id 
and           d.inventory_item_id = msik.inventory_item_id 
and           d.line_type = -1
and           h.batch_id = d.batch_id  
and           h.delete_mark = 0 
and           (h.batch_status = 1 or h.batch_status = 2) 
and 	h.organization_id = :P_ORG_ID 
&CF_BatchRange			
&CF_WhseRange
and ((plan_start_date >= :c_fromdate) or (:c_fromdate IS NULL ))
and ((plan_start_date < :c_todate+1) or (:c_todate IS NULL))
&CF_SortBy