GMI Daily Item Usage

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Inventory
Source: Daily Item Usage Report (XML)
Short Name: ICR04_XML
DB package: GMI_ICR04_XMLP_PKG
(
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
)
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
To Date
 
Date
From Date
 
Date
To Whse
 
LOV Oracle
From Whse
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
Cost Method
 
LOV Oracle
Fiscal Year
 
LOV Oracle
Organization
 
LOV Oracle