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,
inc.quantity
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.
Thanks
Amit
#Reporting------------------------------
Amit Sood
Transurban LTD
------------------------------