GMI Daily Item Usage

(
select
	 t.item_id		item_id,
	 i.item_no		item_no,
	 i.item_desc1	item_desc1,
	 i.inv_class	class_code,
	 t.whse_code	whse_code,
	 i.item_um		uom,
	 t.doc_id		doc_id,
	 t.line_id		line_id,
	 t.doc_type	doc_type,
	 t.reason_code	reason_code,
	 100 		flow_type,
	 t.trans_qty	quantity,
	t.orgn_code,
	 s.orgn_name		name,
	 GMI_ICR04_XMLP_PKG.act_usageformula(t.doc_type, t.reason_code, t.item_id, t.whse_code) ACT_UsageCF, 
	GMI_ICR04_XMLP_PKG.ytd_usagecfformula(t.doc_type, t.reason_code, t.item_id, t.whse_code) YTD_UsageCF, 
	GMI_ICR04_XMLP_PKG.ytd_valuecfformula() YTD_ValueCF,
	GMI_ICR04_XMLP_PKG.Doc_Type_InCP_p Doc_Type_InCP,
	GMI_ICR04_XMLP_PKG.Reason_Code_InCP_p Reason_Code_InCP,
	GMI_ICR04_XMLP_PKG.Quantity_InCP_p Quantity_InCP,
	GMI_ICR04_XMLP_PKG.Doc_Type_OutCP_p Doc_Type_OutCP,
	GMI_ICR04_XMLP_PKG.Reason_Code_OutCP_p Reason_Code_OutCP,
	GMI_ICR04_XMLP_PKG.Quantity_OutCP_p Quantity_OutCP,
     GMI_ICR04_XMLP_PKG.R_Daily_Item_Usage(t.doc_type,t.reason_code) R_Daily_Item,
	 GMI_ICR04_XMLP_PKG.F_Doc_Type_InCP(t.doc_type,t.reason_code, t.line_id,t.doc_id,t.trans_qty) F_Doc_Type_In,
	 GMI_ICR04_XMLP_PKG.F_Doc_Type_OutCP(t.doc_type,t.reason_code, t.line_id,t.doc_id,t.trans_qty) F_Doc_Type_Out
from 
	 ic_tran_cmp t,
	 ic_item_mst i
	 ,sy_orgn_mst  s
where
	 t.item_id=i.item_id
and	 t.orgn_code=s.orgn_code
and	 t.doc_type not in ('GRDI','GRDR','STST','STSR','MGRI','MSTI','PICY','PIPH','REPI','REPR','MTRI','STSI')
and	 i.item_no >= NVL(:from_item,'0')
and	 i.item_no <= NVL(:to_item,'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz')
and	 t.whse_code >= NVL(:from_whse,'0')
and	 t.whse_code <= NVL(:to_whse,'zzzz')
and	 t.trans_date >= :from_date
and	 t.trans_date <=:to_date
and 	 i.delete_mark=0 
and	t.orgn_code=:P_ORGN
)
union
(
select
	 t.item_id		item_id,
	 i.item_no		item_no,
	 i.item_desc1	item_desc1,
	 i.inv_class	class_code,
	 t.whse_code	whse_code,
	 i.item_um		uom,
	 t.doc_id		doc_id,
	 t.line_id		line_id,
	 t.doc_type	doc_type,
	 t.reason_code	reason_code,
	 100 		flow_type,
	 t.trans_qty		quantity,
	t.orgn_code,
	 s.orgn_name		name,
	GMI_ICR04_XMLP_PKG.act_usageformula(t.doc_type, t.reason_code, t.item_id, t.whse_code) ACT_UsageCF, 
	GMI_ICR04_XMLP_PKG.ytd_usagecfformula(t.doc_type, t.reason_code, t.item_id, t.whse_code) YTD_UsageCF, 
	GMI_ICR04_XMLP_PKG.ytd_valuecfformula() YTD_ValueCF,
	GMI_ICR04_XMLP_PKG.Doc_Type_InCP_p Doc_Type_InCP,
	GMI_ICR04_XMLP_PKG.Reason_Code_InCP_p Reason_Code_InCP,
	GMI_ICR04_XMLP_PKG.Quantity_InCP_p Quantity_InCP,
	GMI_ICR04_XMLP_PKG.Doc_Type_OutCP_p Doc_Type_OutCP,
	GMI_ICR04_XMLP_PKG.Reason_Code_OutCP_p Reason_Code_OutCP,
	GMI_ICR04_XMLP_PKG.Quantity_OutCP_p Quantity_OutCP,
	GMI_ICR04_XMLP_PKG.R_Daily_Item_Usage(t.doc_type,t.reason_code) R_DAILY_ITEM,
	 GMI_ICR04_XMLP_PKG.F_Doc_Type_InCP(t.doc_type,t.reason_code, t.line_id,t.doc_id,t.trans_qty) F_DOC_TYPE_IN,
	 GMI_ICR04_XMLP_PKG.F_Doc_Type_OutCP(t.doc_type,t.reason_code, t.line_id,t.doc_id,t.trans_qty) F_DOC_TYPE_OUT
from
	  ic_tran_pnd t,
	  ic_item_mst i,
	 sy_orgn_mst s
where 	 t.item_id=i.item_id 
and	 t.orgn_code=s.orgn_code
and	 t.doc_type not in ('GRDI','GRDR','STST','STSR','MGRI','MSTI','PIPH','PICY','REPR','REPI','MTRI')  
and	 i.item_no >= NVL(:from_item,'0')
and	 i.item_no <= NVL(:to_item,'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz')
and	 t.whse_code >= NVL(:from_whse,'0')
and	 t.whse_code <= NVL(:to_whse,'zzzz')
and	 t.trans_date >= :from_date
and	 t.trans_date <=:to_date
and 	 i.delete_mark=0  
and	 t.delete_mark=0 
and	 t.completed_ind=1
and	t.orgn_code=:P_ORGN
)