GMI Inventory Transfer

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Inventory
Source: Inventory Transfer Report (XML)
Short Name: CMXFER01_XML
DB package: GMI_CMXFER01_XMLP_PKG
select xf.transfer_status ST,
        ge.meaning Status,
       xf.from_warehouse x,
       wh.whse_name From_Whse,
       xf.from_location From_Location,
       ic.item_no Item,
       lt.lot_no Lot,
       lt.sublot_no Sublot,
       xf.to_warehouse x1,
       wh1.whse_name To_Whse,
       xf.release_quantity1 Release_Quantity,
       xf.release_uom1 UOM,
       to_char(decode(xf.transfer_status,1,xf.last_update_date,xf.actual_release_date),'mm/dd/yyyy HH:MI AM') date1,
      to_char(decode(xf.transfer_status,1,xf.scheduled_release_date,
                                                   2,xf.scheduled_receive_date,
                                                   5, xf.cancel_date,
                                                   3, xf.actual_receive_date,
                                                       xf.scheduled_release_date),'mm/dd/yyyy HH:MI AM') date2,                                                 
       xf.orgn_code Orgn_Code,
       xf.transfer_no Transfer_No
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 whse_code from ic_whse_mst a, sy_orgn_usr b
                                          where a.orgn_code=b.orgn_code and
                                          b.user_id=:user_id and
                                          a.delete_mark=0)
and xf.to_warehouse in (select whse_code from ic_whse_mst a, sy_orgn_usr b
                                      where a.orgn_code=b.orgn_code and
                                       b.user_id=:user_id and
                                       a.delete_mark=0)                    
&ReleaseDateCF
&SourceWareHouseCF
&DestinationWarehouseCF
&TreansferStatusCF
&ScheduledShipDateCF
&CancelDateCF
&ActualReceiveDateCF
order by     xf.transfer_status ,
	 xf.from_warehouse,
	 xf.from_location,
	 ic.item_no,
	 lt.lot_no,
	 lt.sublot_no,
	 xf.to_warehouse
Parameter Name SQL text Validation
User Id
 
Number
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 Cancel Date
 
Date
From Cancel Date
 
Date
To Actual Receive Date
 
Date
From Actual Receive Date
 
Date
To Scheduled Release Date
 
Date
From Scheduled Release Date
 
Date
To Actual Release Date
 
Date
From Actual Release Date
 
Date