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 09-01-2024 22:57

    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 09-03-2024 17:57

    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