GML PO/Stock Return History

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Logistics
Source: PO/Stock Return History Report (XML)
Short Name: POR03USR_XML
DB package: GML_POR03USR_XMLP_PKG
SELECT
	rh.orgn_code, 
	rh.return_no,
	rh.return_date,
	vm.vendor_no,
	rh.shipper_code, 
	rd.line_no,
	rd.item_id,
	LTRIM(RTRIM(rd.RMA)) RMA1,
	rd.return_code,
	reas.reason_desc1,
	rd.reorder_ind, 
	rd.return_qty1,
	rd.return_qty2, 
	rd.return_um1,
	rd.return_um2, 
	rcvh.recv_no, 
	rcvd.line_no  recvline_no,
	rcvd.recv_qty1, 
	rcvd.recv_qty2, 
	rcvd.recv_um1,
	rcvd.recv_um2, 
	ordh.po_no,
	ordd.line_no  poline_no,
	ordd.order_qty1,
	ordd.order_qty2,
	ordd.order_um1,
	ordd.order_um2,
	tp.lot_id,
	tp.whse_code, 
	tp.location,
	tp.trans_qty*(-1)  trans_qty, 
	tp.trans_qty2*(-1)  trans_qty2,
	tp.trans_um, 
	tp.trans_um2,
	im.item_no, 
	rd.line_id,
	lm.lot_no,
	lm.sublot_no
FROM
	po_rtrn_hdr      rh,
	po_rtrn_dtl       rd, 
	ic_tran_pnd     tp, 
	ic_item_mst      im, 
	po_vend_mst   vm,                  
	ic_lots_mst       lm,
	po_recv_hdr     rcvh,
	po_recv_dtl      rcvd,
	po_ordr_hdr      ordh,
	po_ordr_dtl       ordd	,
	sy_reas_cds     reas 
WHERE        	
		rh.return_id = rd.return_id     
	and	lm.lot_id(+) = tp.lot_id
	and 	lm.item_id(+)  =  tp.item_id
	and	rh.delete_mark = 0 
	and	tp.doc_type = 'RTRN' 
	and	tp.delete_mark = 0 
	and	rd.return_id = tp.doc_id 
	and	rd.line_id = tp.line_id 
	and	rd.item_id = im.item_id 
	and	rh.return_vendor_id = vm.vendor_id (+) 
	and	rcvd.line_id = rd.recvline_id
	and	rcvh.recv_id  =  rd.recv_id
	and 	reas.reason_code(+)  =  rd.return_code 
	and	ordd.line_id(+)  =   rd.poline_id
	and	ordh.po_id(+)  =  rd.po_id
	&PARAM_WHERE_CLAUSE
ORDER BY
	rh.return_no,rd.line_no
Parameter Name SQL text Validation
User Id
 
Number
ORGN_CODE_CHAR4
 
LOV Oracle
To Warehouse
 
LOV Oracle
From Warehouse
 
LOV Oracle
To Sublot No
 
LOV Oracle
From sublot No
 
LOV Oracle
To Lot No
 
LOV Oracle
From Lot No
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
To Return Reason
 
LOV Oracle
From Return Reason
 
LOV Oracle
To Return Date
 
Date
From Return Date
 
Date
To Vendor No
 
LOV Oracle
From Vendor No
 
LOV Oracle
To Return No
 
LOV Oracle
From Return No
 
LOV Oracle
Include Stock Returns
 
LOV Oracle
Include PO Returns
 
LOV Oracle