Maximo Open Forum

 View Only
  • 1.  Ad hoc reporting

    Posted 7 days ago
    Hi More Community team,

    I am trying to do a POC for ad-hoc reporting where I am adding 4 tables ( invoice, invoiceline,invoicecost and FINCNTRL) in an reporting object structure.

    select inv.apcontrolacct, inv.apsuspenseacct, inv.documenttype,
    inv.duedate, inv.externalrefid, inv.financialperiod, inv.invoicedate,
    inv.invoiceid, inv.invoicenum, inv.jhg_invreceivdate, inv.jhg_location,
    inv.orgid, inv.pluspcustomer, inv.plustclaimid, inv.plustrepwo, inv.sendersysid,
    inv.siteid, inv.status, inv.vendor, inv.vendorinvoicenum,
    inl.description, inl.invoicelineid, inl.invoicelinenum, inl.invoiceunit,
    inl.ponum, inl.tax1, inl.tax1code, inc.costlinenum, fnl.projectid as fcprojectid, fnl.taskid as fctaskid,
    inc.gldebitacct, inc.invoicecostid, inc.linecost, inc.plustservtype,
    from maximo.INVOICE inv
    left join maximo.INVOICELINE inl on inv.invoicenum=inl.invoicenum and inv.siteid = inl.siteid
    left join maximo.INVOICECOST inc on inc.invoicenum=inl.invoicenum and inc.siteid = inl.siteid
    and inc.invoicelinenum=inl.invoicelinenum
    left join maximo.FINCNTRL fnl on inc.fincntrlid = fnl.fincntrlid
    where inv.status='SUBMITTED'
    and inv.statusdate between trunc(sysdate) - interval '2' hour and trunc(sysdate) + interval '22' hour

    Requirement :If we run the result in sql developer for above sql should match with the adhoc reporting records and see whether we can have custom input paramters for adhoc reporting to filter the data.

    Filter will be on Inv.Status date

    DB is :Oracle

    I have tried  couple of custom "ad hoc report expression" for statusdate but not working, has anyone tried this before ? any pointers or help is appreciated.



    Amit Sood
    Transurban LTD