GME Batch Pick List

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Process Execution
Source: Batch Pick List (XML)
Short Name: GMEBCHPL_XML
DB package: GME_GMEBCHPL_XMLP_PKG
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
Parameter Name SQL text Validation
Organization
 
Number
Sort By
 
LOV Oracle
To Planned Start Date
 
Date
From Planned Start Date
 
Date
To Subinventory
 
LOV Oracle
From Subinventory
 
LOV Oracle
To Batch
 
LOV Oracle
From Batch
 
LOV Oracle