GMI Daily Transaction Detail

		 (select	t.doc_type,			
	t.whse_code	Warehouse,			
	w.whse_name	Whse_name,			
	t.trans_id		Trans_no,		
	t.item_id,				
	i.item_no		Item,				
	t.doc_id,				
	t.line_id,				
	to_char(t.trans_date,'DD-MON-YYYY')	Trans_Date,			
	t.trans_qty	Quantity,			
	t.trans_um	UOM,			
	t.trans_qty2	Quantity2,			
	t.trans_um2	Uom2,			
	t.location		Location,				
	t.lot_id,
	GMI_ICR03_XMLP_PKG.unit_costcfformula(t.item_id, t.whse_code, t.trans_date) unit_costCF, 
	--GMI_ICR03_XMLP_PKG.cost_accumcfformula(:unit_costCF, :count_QtySC) cost_accumCF, 
	--GMI_ICR03_XMLP_PKG.defaultcurrcfformula(:cost_accumCF) defaultcurrCF, 
	GMI_ICR03_XMLP_PKG.doc_typecfformula(t.doc_type) doc_typeCF, 
	GMI_ICR03_XMLP_PKG.document_nocfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Document_NoCF, 
	GMI_ICR03_XMLP_PKG.formula_nocfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Formula_NoCF, 
	GMI_ICR03_XMLP_PKG.verscfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) VersCF, 
	GMI_ICR03_XMLP_PKG.vend_ship_locationcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Vend_Ship_LocationCF, 
	GMI_ICR03_XMLP_PKG.frtbill_mthdcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Frtbill_MthdCF, 
	GMI_ICR03_XMLP_PKG.shipping_mthdcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Shipping_mthdCF, 
	GMI_ICR03_XMLP_PKG.shipper_namecfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Shipper_nameCF,
	GMI_ICR03_XMLP_PKG.doc_typeCP_p doc_typeCP,
	GMI_ICR03_XMLP_PKG.Document_NoCP_p Document_NoCP,
	GMI_ICR03_XMLP_PKG.Formula_NoCP_p Formula_NoCP,
	GMI_ICR03_XMLP_PKG.VersCP_p VersCP,
	GMI_ICR03_XMLP_PKG.Vend_Ship_LocationCP_p Vend_Ship_LocationCP,
	GMI_ICR03_XMLP_PKG.Frtbill_MthdCP_p Frtbill_MthdCP,
	GMI_ICR03_XMLP_PKG.Shipping_MthdCP_p Shipping_MthdCP,
	GMI_ICR03_XMLP_PKG.Shipper_NameCP_p Shipper_NameCP
from 	ic_tran_pnd t,
	ic_item_mst i,
	ic_whse_mst w,
	cm_mthd_mst cm
where		t.whse_code >=NVL( :FROM_WHSE,'0')
	and	t.whse_code <= NVL(:TO_WHSE,'zzzz')
	and	t.trans_date >= :FROM_TRANSDATE
	and	t.trans_date < :TO_TRANSDATE+1
	and 	cm.cost_mthd_code = :cost_method
	and	t.delete_mark = 0
	and	t.item_id = i.item_id
	and	t.whse_code = w.whse_code
	and	t.completed_ind = 1
)
UNION
(select	t.doc_type,		
	t.whse_code	Warehouse,			
	w.whse_name	Whse_Name,			
	t.trans_id		Trans_No,		
	t.item_id,			
	i.item_no		Item,				
	t.doc_id,				
	t.line_id,
	to_char(t.trans_date,'DD-MON-YYYY')	Trans_Date,
	t.trans_qty	Qty,			
	t.trans_um	UOM,			
	t.trans_qty2	Qty2,			
	t.trans_um2	UOM2,			
	t.location		Location,				
	t.lot_id,
	GMI_ICR03_XMLP_PKG.unit_costcfformula(t.item_id, t.whse_code, t.trans_date) unit_costCF, 
	--GMI_ICR03_XMLP_PKG.cost_accumcfformula(:unit_costCF, :count_QtySC) cost_accumCF, 
	--GMI_ICR03_XMLP_PKG.defaultcurrcfformula(:cost_accumCF) defaultcurrCF, 
	GMI_ICR03_XMLP_PKG.doc_typecfformula(t.doc_type) doc_typeCF, 
	GMI_ICR03_XMLP_PKG.document_nocfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Document_NoCF, 
	GMI_ICR03_XMLP_PKG.formula_nocfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Formula_NoCF, 
	GMI_ICR03_XMLP_PKG.verscfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) VersCF, 
	GMI_ICR03_XMLP_PKG.vend_ship_locationcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Vend_Ship_LocationCF, 
	GMI_ICR03_XMLP_PKG.frtbill_mthdcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Frtbill_MthdCF, 
	GMI_ICR03_XMLP_PKG.shipping_mthdcfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Shipping_mthdCF, 
	GMI_ICR03_XMLP_PKG.shipper_namecfformula(t.doc_type, t.doc_id, t.line_id, t.item_id) Shipper_nameCF,
	GMI_ICR03_XMLP_PKG.doc_typeCP_p doc_typeCP,
	GMI_ICR03_XMLP_PKG.Document_NoCP_p Document_NoCP,
	GMI_ICR03_XMLP_PKG.Formula_NoCP_p Formula_NoCP,
	GMI_ICR03_XMLP_PKG.VersCP_p VersCP,
	GMI_ICR03_XMLP_PKG.Vend_Ship_LocationCP_p Vend_Ship_LocationCP,
	GMI_ICR03_XMLP_PKG.Frtbill_MthdCP_p Frtbill_MthdCP,
	GMI_ICR03_XMLP_PKG.Shipping_MthdCP_p Shipping_MthdCP,
	GMI_ICR03_XMLP_PKG.Shipper_NameCP_p Shipper_NameCP
from 	ic_tran_cmp t,
	ic_item_mst i,
	ic_whse_mst w,
	cm_mthd_mst cm
--Bug#3315846 Modified the following where clause 
where		((t.whse_code >= NVL(:FROM_WHSE,'0')
	and	t.whse_code <= NVL(:TO_WHSE,'zzzz')) or 
                                 (t.whse_code is null and t.doc_type  in ('GRDI','GRDR')))  
	and	t.trans_date >= :FROM_TRANSDATE
	and	t.trans_date < :TO_TRANSDATE+1
	and 	cm.cost_mthd_code = :cost_method
	and	t.item_id = i.item_id
	and	t.whse_code = w.whse_code(+)
)
order by 2,3,6,5,1,7,4