This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I am attempting to retrieve all POs with a status of APPR and the status date is greater than 60 days which have no invoices associated. I will then send out an email to the company.remitcontact notifying him/her that they have 30 days to submit their invoice for said PO. Failure to do so will result in the PO being cancelled and invoices will not be processed. I am trying to perform the following query in the Maximo UI:
select p.ponum,
p.vendor,
p.statusdate
from po p
left outer join invoice i
on p.ponum = i.ponum
and p.siteid = i.siteid
where p.status = 'APPR'
and p.statusdate <= GETDATE() - 60
and i.invoiceid is null
This works fine on SQL Server, but when I attempt to translate it to Maximo UI where clause syntax:
(status = 'APPR'
and historyflag = 0
and statusdate <= GETDATE()-60
and exists (select po.ponum
from po
left outer join invoice
on po.ponum = invoice.ponum
where po.status = 'APPR'
and invoice.invoiceid is null))
It appears as though the Maximo UI is ignoring the additional filter I am trying to implement with the exists statement. The same result set comes back as if the exists statement were not there.
Any suggestions would be greatly appreciated.
Thanks in advance,
Derek
OK. Sometimes the KISS principle is a good thing. Here is what I came up with instead:
(status = 'APPR'
and historyflag = 0
and statusdate <= GETDATE()-60
and receipts = 'NONE')
To the best of my knowledge, if there are no receipts (po.receipts = 'NONE'), an invoice has not been received. Sorry for the unnecessary chatter.
--- In MAXIMO@yahoogroups.com, "Derek" <derek.kitzelman@...> wrote:
>
> I am attempting to retrieve all POs with a status of APPR and the status date is greater than 60 days which have no invoices associated. I will then send out an email to the company.remitcontact notifying him/her that they have 30 days to submit their invoice for said PO. Failure to do so will result in the PO being cancelled and invoices will not be processed. I am trying to perform the following query in the Maximo UI:
>
> select p.ponum,
> p.vendor,
> p.statusdate
> from po p
> left outer join invoice i
> on p.ponum = i.ponum
> and p.siteid = i.siteid
> where p.status = 'APPR'
> and p.statusdate <= GETDATE() - 60
> and i.invoiceid is null
>
> This works fine on SQL Server, but when I attempt to translate it to Maximo UI where clause syntax:
>
> (status = 'APPR'
> and historyflag = 0
> and statusdate <= GETDATE()-60
> and exists (select po.ponum
> from po
> left outer join invoice
> on po.ponum = invoice.ponum
> where po.status = 'APPR'
> and invoice.invoiceid is null))
>
> It appears as though the Maximo UI is ignoring the additional filter I am trying to implement with the exists statement. The same result set comes back as if the exists statement were not there.
>
> Any suggestions would be greatly appreciated.
>
> Thanks in advance,
> Derek
>
Hi Derek: I was going to advise against doing it your original way, on the
basis of the fact that sometimes 2 or more POs can be combined on one
invoice, if they're for the same vendor.
But with the new way: for materials, the users have no choice but to
receive first, but for services, they do have the option of not requiring
them. I'd triple-check before going ahead.
Shannon Rotz
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Derek
Sent: May-07-13 11:17 AM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: LEFT OUTER JOIN in a Where Clause - Escalation
OK. Sometimes the KISS principle is a good thing. Here is what I came up
with instead:
(status = 'APPR'
and historyflag = 0
and statusdate <= GETDATE()-60
and receipts = 'NONE')
To the best of my knowledge, if there are no receipts (po.receipts =
'NONE'), an invoice has not been received. Sorry for the unnecessary
chatter.
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Derek"
<derek.kitzelman@...> wrote:
>
> I am attempting to retrieve all POs with a status of APPR and the status
date is greater than 60 days which have no invoices associated. I will then
send out an email to the company.remitcontact notifying him/her that they
have 30 days to submit their invoice for said PO. Failure to do so will
result in the PO being cancelled and invoices will not be processed. I am
trying to perform the following query in the Maximo UI:
>
> select p.ponum,
> p.vendor,
> p.statusdate
> from po p
> left outer join invoice i
> on p.ponum = i.ponum
> and p.siteid = i.siteid
> where p.status = 'APPR'
> and p.statusdate <= GETDATE() - 60
> and i.invoiceid is null
>
> This works fine on SQL Server, but when I attempt to translate it to
Maximo UI where clause syntax:
>
> (status = 'APPR'
> and historyflag = 0
> and statusdate <= GETDATE()-60
> and exists (select po.ponum
> from po
> left outer join invoice
> on po.ponum = invoice.ponum
> where po.status = 'APPR'
> and invoice.invoiceid is null))
>
> It appears as though the Maximo UI is ignoring the additional filter I am
trying to implement with the exists statement. The same result set comes
back as if the exists statement were not there.
>
> Any suggestions would be greatly appreciated.
>
> Thanks in advance,
> Derek
>