Hi Maximo Experts,
PLease help me query only those POLINE transaction (SERVRETRAN OBJECT) that are valid, meaning not return or void by the enduser.
My BIRT code is as follows
sqlText = sqlText + "LEFT JOIN servrectrans ON ";
sqlText = sqlText + "poline.ponum = servrectrans.ponum and poline.polinenum = servrectrans.polinenum and servrectrans.belongsto is null and poline.siteid = servrectrans.positeid ";
sqlText = sqlText + "and servrectrans.POREVISIONNUM = (select max(p.revisionnum)";
sqlText = sqlText + " from po p ";
sqlText = sqlText + " where p.ponum = servrectrans.ponum ";
sqlText = sqlText + " and p.siteid = servrectrans.siteid ";
sqlText = sqlText + " and p.status in (select synonymdomain.value from synonymdomain where synonymdomain.domainid = ";
sqlText = sqlText + " 'POSTATUS' and synonymdomain.maxvalue not in ('CAN','PNDREV')) ";
sqlText = sqlText + " group by p.ponum) and servrectrans.issuetype = 'RECEIPT' ";
and the result of the report is:
it all captures servrectrans receiving transaction with "RECEIPT", makes it repeat the poline 3x in the birt report.
Appreciate any help.
thanks
#Procurement#Reporting------------------------------
DarwinB
GNP
------------------------------