GMI Daily Transaction Detail

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Inventory
Source: Daily Transaction Detail Report (XML)
Short Name: ICR03_XML
DB package: GMI_ICR03_XMLP_PKG
		 (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
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
Cost Method
 
LOV Oracle
To Transdate
 
Date
From Transdate
 
Date
To Whse
 
LOV Oracle
From Whse
 
LOV Oracle
Organization
 
LOV Oracle
Ask a question