Maximo Open Forum

 View Only
Back to discussions
Expand all | Collapse all

How can i query in report those valid servrentrans in a PO - not including the return

  • 1.  How can i query in report those valid servrentrans in a PO - not including the return

    Posted 7 days ago

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


  • 2.  RE: How can i query in report those valid servrentrans in a PO - not including the return

    Posted 6 days ago

    Hi @Darwin Bagangan!

    After the where should be:
     
    po.revisionnum = servrectrans.porevisionnum
    Regards!


    ------------------------------
    Martin Fabra
    ARSAT S.A. - Actively seeking employment
    ------------------------------

    Original Message


Related Content