GMI Inventory Transfer Report By Batch

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Inventory
Source: Inventory Transfer Report By Batch (XML)
Short Name: CMXFER02_XML
DB package: GMI_CMXFER02_XMLP_PKG
select xf.transfer_status Stat,
       xf.transfer_batch batch,	
       ge.meaning Status,
       xf.from_warehouse||decode(xf.from_location,null,' ' , 		'/'||xf.from_location) from_ware_loc,
             ic.item_no||decode(lt.lot_no,null,' ','/'||lt.lot_no)||decode( lt.sublot_no,null,' ','/'||lt.sublot_no) item_lot_sub,
       xf.to_warehouse||decode(xf.to_location,null,' ','/'||xf.to_location) To_ware_loc,
       xf.release_quantity1||' '||xf.release_uom1 Release_qty,       decode(xf.transfer_status,1,to_char(xf.scheduled_release_date,'MM/DD/RR HH24:MI'),2,to_char(xf.actual_release_date,'MM/DD/RR HH24:MI'),
	3,to_char(xf.actual_release_Date,'MM/DD/RR HH24:MI'),4,to_char(xf.scheduled_release_Date,'MM/DD/RR HH24:MI'), 5,to_char(xf.actual_release_date,'MM/DD/RR HH24:MI'),null) 
                actual_rel_date,
      decode(xf.transfer_status,1,to_char(xf.scheduled_receive_date,'MM/DD/RR HH24:MI'),2,to_char(xf.scheduled_receive_Date,'MM/DD/RR HH24:MI'),
	3,to_char(xf.actual_receive_date,'MM/DD/RR HH24:MI'),4,to_char(xf.scheduled_receive_Date,'MM/DD/RR HH24:MI'),5,to_char(xf.cancel_Date,'MM/DD/RR HH24:MI'),null)
     	receive_cancel_date,                                                 
      xf.orgn_code||' '||xf.transfer_no Transfer_No, 
	GMI_CMXFER02_XMLP_PKG.release_cfformula(xf.transfer_status) RELEASE_CF, 
	GMI_CMXFER02_XMLP_PKG.receive_cfformula(xf.transfer_status) RECEIVE_CF
from   ic_xfer_mst xf,
       ic_whse_mst wh,
       ic_whse_mst wh1,
       ic_item_mst ic,
       ic_lots_mst lt,
       gem_lookups ge
where  xf.item_id=ic.item_id
and    xf.from_warehouse=wh.whse_code
and    xf.to_warehouse=wh1.whse_code
and    xf.lot_id=lt.lot_id
and    xf.item_id=lt.item_id
and    xf.transfer_status=ge.lookup_code
and    ge.lookup_type='TRANSFER_STATUS'
and (xf.from_warehouse IN 
(SELECT w.whse_code FROM ic_whse_mst w, sy_orgn_usr u 
WHERE w.orgn_code = u.orgn_code AND u.user_id = :v_user_id AND w.delete_mark = 0) 
OR xf.to_warehouse IN (SELECT w.whse_code FROM ic_whse_mst w, sy_orgn_usr u 
WHERE w.orgn_code = u.orgn_code AND u.user_id =:v_user_id AND w.delete_mark = 0) 
OR xf.orgn_code IN (SELECT orgn_code FROM sy_orgn_usr WHERE user_id = :v_user_id)) 
&ReleaseDateCP
&SourceWareHouseCP
&DestinationWarehouseCP
&TransferStatusCP
&ScheduledShipDateCP
&CancelDateCP
&ActualReceiveDateCP
&BatchNoCP
order by   xf.transfer_batch,3,
	xf.transfer_status,
	wh1.orgn_code,
	xf.transfer_no,
	xf.from_warehouse,
	xf.from_location,
	 ic.item_no,
	 lt.lot_no,
	 lt.sublot_no,
	 xf.to_warehouse
Parameter Name SQL text Validation
To Transfer Status
 
LOV Oracle
From Transfer Status
 
LOV Oracle
To Destination Warehouse
 
LOV Oracle
From Destination Warehouse
 
LOV Oracle
To Source Warehouse
 
LOV Oracle
From Source Warehouse
 
LOV Oracle
To Actual Receive Date
 
Date
From Actual Receive Date
 
Date
To Cancel Date
 
Date
From Cancel Date
 
Date
To Scheduled Release Date
 
Date
From Scheduled Release Date
 
Date
To Actual Release Date
 
Date
From Actual Release Date
 
Date
To Transfer Batch
 
LOV Oracle
From Transfer Batch
 
LOV Oracle
Ask a question