GML Items Received

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Logistics
Source: Items Received Report (XML)
Short Name: PORIRUSR_XML
DB package: GML_PORIRUSR_XMLP_PKG
SELECT
	rd.to_whse, 
	im.item_no, 
	pd.item_desc, 
	rh.recv_no, 
	rh.orgn_code, 
	rd.recv_date, 
	vn.vendor_name, 
	sum(tp.trans_qty) trans_qty, 
	rd.recv_um1,
	ph.po_no, 
	tp.lot_id, 
	tp.trans_um, 
	LTRIM(RTRIM(tp.lot_status)) LOT_STATUS1 ,
 	tp.doc_id,
	tp.doc_type,
	tp.line_id,
	rd.item_id, 
	LTRIM(RTRIM(GML_PORIRUSR_XMLP_PKG.lot_noformula(rd.item_id, tp.lot_id))) LOT_NO1, 
	LTRIM(RTRIM(GML_PORIRUSR_XMLP_PKG.sublot_noformula(rd.item_id, tp.lot_id))) SUBLOT_NO1
FROM
	ic_item_mst im, 
	po_recv_hdr rh, 
	po_recv_dtl rd,            
	po_vend_mst vn, 
	po_ordr_hdr ph, 
	po_ordr_dtl pd, 
	ic_tran_pnd tp
WHERE
	im.item_id = rd.item_id 
	and	rh.recv_id = rd.recv_id 
	and rh.delete_mark = 0
	and	vn.vendor_id = rd.shipvend_id 
	and	tp.doc_type = 'RECV' 
	and	tp.doc_id = rh.recv_id 
	and	tp.line_id = rd.line_id 
	&PARAM_WHERE_CLAUSE
    and	ph.po_id(+) = rd.po_id 
	and	pd.line_id(+) = rd.poline_id
GROUP BY
	rd.to_whse, 
	im.item_no, 
	pd.item_desc, 
	rh.recv_no, 
	rh.orgn_code, 
	rd.recv_date, 
	vn.vendor_name, 
	rd.recv_um1, 
	ph.po_no, 
	tp.lot_id, 
	tp.trans_um, 
	tp.lot_status,
 	tp.doc_id,
	tp.doc_type,
	tp.line_id,
	rd.item_id,
	decode(:p_sort_by,'2',ph.po_no,'3',vn.vendor_name,'1',im.item_no),
	decode(:p_sort_by,'3',ph.po_no,'2',rd.line_no),
	decode(:p_sort_by,'3',rd.line_no)
HAVING  sum(tp.trans_qty) > 0
ORDER BY
	rd.to_whse,
	decode(:p_sort_by,'2',ph.po_no,'3',vn.vendor_name,'1',im.item_no),
	decode(:p_sort_by,'3',ph.po_no,'2',rd.line_no),
	decode(:p_sort_by,'3',rd.line_no)
Parameter Name SQL text Validation
Sort By
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
To Recv date
 
Date
From Recv date
 
Date
To Warehouse
 
LOV Oracle
From Warehouse
 
LOV Oracle