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) |