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'm working towards this too (having Maximo close POs when Receipts are complete and all receipts have been invoiced and it's ### number of days since it was last updated). Anyone know the SQL to find these POs?
Travis Herron
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
>
> I'll and another $.02 and say we are moving to do the same thing. I've dealt with too many, 'We closed PO xxxx too early and now someone says we need to change it'.
>
>
>
> --- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@> wrote:
> >
> > To add my $.02 worth: with all of my clients, I have restricted the CLOSE
> > PO function so that only MAXADMIN can do it. Then I have an Escalation
> > record set up to close Pos on a weekly basis, under certain circumstances:
> >
> > 1. Status is APPR
> >
> > 2. No changes in xx days
> >
> > 3. Receipts are complete, i.e. PO.RECEIPTS = 'COMPLETE'
> >
> > 4. All receipts have been invoiced (this is the one that usually gets
> > people into trouble, and is harder to figure out).
> >
> >
> >
> > The Maximo rules are:
> >
> > 1. When the full quantity on a line is received, the RECEIPTSCOMPLETE
> > flag flips from "No" to "Yes"
> >
> > 2. When the RECEIPTSCOMPLETE flags on all of the PO Lines are "Yes",
> > then the RECEIPTS field on the front of the PO changes to COMPLETE.
> >
> > 3. If the full quantity on the PO Line is never going to be received,
> > you can flip the RECEIPTSCOMPLETE flag to "Yes" by going to Select Actions /
> > Complete Receipts.
> >
> >
> >
> >
> >
> > So the result for my clients is that if they
> >
> > a) receive the actual goods/services when needed; and
> >
> > b) do a Complete Receipts if needed
> >
> >
> >
> > Then Purchasing never has to worry about closing Pos, and Accounting never
> > has to worry about a PO that is Closed, but has an RBNI balance on it.
> >
> >
> >
> > If this is old news to some of you, sorry - it's just me on my soapbox :)
> >
> >
> >
> >
> >
> > Shannon
> >
> >
> >
> > From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> > Peter Camarsh
> > Sent: April-11-11 8:50 PM
> > To: MAXIMO@yahoogroups.com
> > Subject: RE: [MAXIMO List] Able to close PO with partial receipts
> >
> >
> >
> >
> >
> > In a lot of cases all the items on PO may never be delivered due to any
> > number of reasons. It is necessary for MAXIMO to allow these PO's to go
> > into history so there is the option to COMPLETE RECEIPTS (even though they
> > have not ), this then allows the PO to be closed.
> >
> > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> > Of
> > dancue23
> > Sent: Tuesday, 12 April 2011 12:07 PM
> > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > Subject: [MAXIMO List] Able to close PO with partial receipts
> >
> > Why would you be able to close a PO with partial receipts? I checked Out of
> > the box Maximo and it allows you to close a PO with partial receipts.
> >
> > I checked PO options in Administration and did not see anything that would
> > allow this. What am I missing here?
> >
> > We are not using the invoice or contracts modules.
> >
> > Thanks for your help!
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
Me again. . .this is what I've come up with to answer my own question. Anyone willing to error-check it? This assumes we're looking for PO's without changes in the last 30 days:
SELECT po.ponum
FROM po
WHERE receipts = 'COMPLETE' AND historyflag = '0' AND changedate < (getdate()-30) AND
ponum NOT IN (SELECT ponum FROM matrectrans WHERE ponum IS NOT NULL AND transdate > (getdate()-30)) AND
ponum NOT IN (SELECT ponum FROM servrectrans WHERE ponum IS NOT NULL AND transdate > (getdate()-30)) AND
ponum NOT IN (SELECT ponum FROM invoiceline WHERE ponum IS NOT NULL AND invoicenum IN (SELECT invoicenum FROM invoice WHERE changedate > (getdate()-30))) AND
(ponum IN
(SELECT srt.ponum
FROM servrectrans srt JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.status = 'COMP' AND ivl.invoicenum IN
(SELECT invoicenum
FROM invoice
WHERE status = 'PAID')
GROUP BY srt.polinenum, srt.ponum
HAVING SUM(srt.quantity) <= SUM(ivl.invoiceqty)) OR ponum IN
(SELECT srt.ponum
FROM servrectrans srt JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.status = 'COMP' AND ivl.invoicenum IN
(SELECT invoicenum
FROM invoice
WHERE status = 'PAID')
GROUP BY srt.polinenum, srt.ponum
HAVING SUM(srt.quantity) <= SUM(ivl.invoiceqty))) AND ponum NOT IN
(SELECT srt.ponum
FROM servrectrans srt LEFT JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.ponum IS NOT NULL AND srt.status = 'COMP' AND ivl.ponum IS NULL) AND ponum NOT IN
(SELECT mrt.ponum
FROM matrectrans mrt LEFT JOIN invoiceline ivl
ON mrt.ponum = ivl.ponum AND mrt.polinenum = ivl.polinenum
WHERE mrt.ponum IS NOT NULL AND mrt.status = 'COMP' AND ivl.ponum IS NULL)
ORDER BY ponum
If it works, feel free to steal that for your own use.
Travis Herron
--- In MAXIMO@yahoogroups.com, "Travis Herron" <therron@...> wrote:
>
> I'm working towards this too (having Maximo close POs when Receipts are complete and all receipts have been invoiced and it's ### number of days since it was last updated). Anyone know the SQL to find these POs?
>
> Travis Herron
>
> --- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@> wrote:
> >
> >
> > I'll and another $.02 and say we are moving to do the same thing. I've dealt with too many, 'We closed PO xxxx too early and now someone says we need to change it'.
> >
> >
> >
> > --- In MAXIMO@yahoogroups.com, "Shannon Rotz" <shannonrotz@> wrote:
> > >
> > > To add my $.02 worth: with all of my clients, I have restricted the CLOSE
> > > PO function so that only MAXADMIN can do it. Then I have an Escalation
> > > record set up to close Pos on a weekly basis, under certain circumstances:
> > >
> > > 1. Status is APPR
> > >
> > > 2. No changes in xx days
> > >
> > > 3. Receipts are complete, i.e. PO.RECEIPTS = 'COMPLETE'
> > >
> > > 4. All receipts have been invoiced (this is the one that usually gets
> > > people into trouble, and is harder to figure out).
> > >
> > >
> > >
> > > The Maximo rules are:
> > >
> > > 1. When the full quantity on a line is received, the RECEIPTSCOMPLETE
> > > flag flips from "No" to "Yes"
> > >
> > > 2. When the RECEIPTSCOMPLETE flags on all of the PO Lines are "Yes",
> > > then the RECEIPTS field on the front of the PO changes to COMPLETE.
> > >
> > > 3. If the full quantity on the PO Line is never going to be received,
> > > you can flip the RECEIPTSCOMPLETE flag to "Yes" by going to Select Actions /
> > > Complete Receipts.
> > >
> > >
> > >
> > >
> > >
> > > So the result for my clients is that if they
> > >
> > > a) receive the actual goods/services when needed; and
> > >
> > > b) do a Complete Receipts if needed
> > >
> > >
> > >
> > > Then Purchasing never has to worry about closing Pos, and Accounting never
> > > has to worry about a PO that is Closed, but has an RBNI balance on it.
> > >
> > >
> > >
> > > If this is old news to some of you, sorry - it's just me on my soapbox :)
> > >
> > >
> > >
> > >
> > >
> > > Shannon
> > >
> > >
> > >
> > > From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
> > > Peter Camarsh
> > > Sent: April-11-11 8:50 PM
> > > To: MAXIMO@yahoogroups.com
> > > Subject: RE: [MAXIMO List] Able to close PO with partial receipts
> > >
> > >
> > >
> > >
> > >
> > > In a lot of cases all the items on PO may never be delivered due to any
> > > number of reasons. It is necessary for MAXIMO to allow these PO's to go
> > > into history so there is the option to COMPLETE RECEIPTS (even though they
> > > have not ), this then allows the PO to be closed.
> > >
> > > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > > [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> > > Of
> > > dancue23
> > > Sent: Tuesday, 12 April 2011 12:07 PM
> > > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > > Subject: [MAXIMO List] Able to close PO with partial receipts
> > >
> > > Why would you be able to close a PO with partial receipts? I checked Out of
> > > the box Maximo and it allows you to close a PO with partial receipts.
> > >
> > > I checked PO options in Administration and did not see anything that would
> > > allow this. What am I missing here?
> > >
> > > We are not using the invoice or contracts modules.
> > >
> > > Thanks for your help!
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
>
Hi Travis: I created a view to look at this stuff, that I take around with
me to each client. I posted it in the files section a while ago, but that
was for an earlier version, so I re-posted it in the Version 7 folder just
now. There are instructions on how to use the view in the comments at the
top.
It's for SQL Server, but with a bit of adjustment, it should work on Oracle
as well.
For the escalation SQL, I use this view and add the extra criteria similar
to what you have. I can post that if you need it.
Hope this helps.
Shannon
P.S. I THINK (although I haven't tested it lately) that the PO.CHANGEDATE
gets updated when someone receives it.
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of
Travis Herron
Sent: November-30-11 2:14 PM
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: Able to close PO with partial receipts
Me again. . .this is what I've come up with to answer my own question.
Anyone willing to error-check it? This assumes we're looking for PO's
without changes in the last 30 days:
SELECT po.ponum
FROM po
WHERE receipts = 'COMPLETE' AND historyflag = '0' AND changedate <
(getdate()-30) AND
ponum NOT IN (SELECT ponum FROM matrectrans WHERE ponum IS NOT NULL AND
transdate > (getdate()-30)) AND
ponum NOT IN (SELECT ponum FROM servrectrans WHERE ponum IS NOT NULL AND
transdate > (getdate()-30)) AND
ponum NOT IN (SELECT ponum FROM invoiceline WHERE ponum IS NOT NULL AND
invoicenum IN (SELECT invoicenum FROM invoice WHERE changedate >
(getdate()-30))) AND
(ponum IN
(SELECT srt.ponum
FROM servrectrans srt JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.status = 'COMP' AND ivl.invoicenum IN
(SELECT invoicenum
FROM invoice
WHERE status = 'PAID')
GROUP BY srt.polinenum, srt.ponum
HAVING SUM(srt.quantity) <= SUM(ivl.invoiceqty)) OR ponum IN
(SELECT srt.ponum
FROM servrectrans srt JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.status = 'COMP' AND ivl.invoicenum IN
(SELECT invoicenum
FROM invoice
WHERE status = 'PAID')
GROUP BY srt.polinenum, srt.ponum
HAVING SUM(srt.quantity) <= SUM(ivl.invoiceqty))) AND ponum NOT IN
(SELECT srt.ponum
FROM servrectrans srt LEFT JOIN invoiceline ivl
ON srt.ponum = ivl.ponum AND srt.polinenum = ivl.polinenum
WHERE srt.ponum IS NOT NULL AND srt.status = 'COMP' AND ivl.ponum IS NULL)
AND ponum NOT IN
(SELECT mrt.ponum
FROM matrectrans mrt LEFT JOIN invoiceline ivl
ON mrt.ponum = ivl.ponum AND mrt.polinenum = ivl.polinenum
WHERE mrt.ponum IS NOT NULL AND mrt.status = 'COMP' AND ivl.ponum IS NULL)
ORDER BY ponum
If it works, feel free to steal that for your own use.
Travis Herron
--- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Travis
Herron" <therron@...> wrote:
>
> I'm working towards this too (having Maximo close POs when Receipts are
complete and all receipts have been invoiced and it's ### number of days
since it was last updated). Anyone know the SQL to find these POs?
>
> Travis Herron
>
> --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> , "Jason
Verly" <jason.verly@> wrote:
> >
> >
> > I'll and another $.02 and say we are moving to do the same thing. I've
dealt with too many, 'We closed PO xxxx too early and now someone says we
need to change it'.
> >
> >
> >
> > --- In MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ,
"Shannon Rotz" <shannonrotz@> wrote:
> > >
> > > To add my $.02 worth: with all of my clients, I have restricted the
CLOSE
> > > PO function so that only MAXADMIN can do it. Then I have an Escalation
> > > record set up to close Pos on a weekly basis, under certain
circumstances:
> > >
> > > 1. Status is APPR
> > >
> > > 2. No changes in xx days
> > >
> > > 3. Receipts are complete, i.e. PO.RECEIPTS = 'COMPLETE'
> > >
> > > 4. All receipts have been invoiced (this is the one that usually gets
> > > people into trouble, and is harder to figure out).
> > >
> > >
> > >
> > > The Maximo rules are:
> > >
> > > 1. When the full quantity on a line is received, the RECEIPTSCOMPLETE
> > > flag flips from "No" to "Yes"
> > >
> > > 2. When the RECEIPTSCOMPLETE flags on all of the PO Lines are "Yes",
> > > then the RECEIPTS field on the front of the PO changes to COMPLETE.
> > >
> > > 3. If the full quantity on the PO Line is never going to be received,
> > > you can flip the RECEIPTSCOMPLETE flag to "Yes" by going to Select
Actions /
> > > Complete Receipts.
> > >
> > >
> > >
> > >
> > >
> > > So the result for my clients is that if they
> > >
> > > a) receive the actual goods/services when needed; and
> > >
> > > b) do a Complete Receipts if needed
> > >
> > >
> > >
> > > Then Purchasing never has to worry about closing Pos, and Accounting
never
> > > has to worry about a PO that is Closed, but has an RBNI balance on it.
> > >
> > >
> > >
> > > If this is old news to some of you, sorry - it's just me on my soapbox
:)
> > >
> > >
> > >
> > >
> > >
> > > Shannon
> > >
> > >
> > >
> > > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
[mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com> ] On Behalf
Of
> > > Peter Camarsh
> > > Sent: April-11-11 8:50 PM
> > > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
> > > Subject: RE: [MAXIMO List] Able to close PO with partial receipts
> > >
> > >
> > >
> > >
> > >
> > > In a lot of cases all the items on PO may never be delivered due to
any
> > > number of reasons. It is necessary for MAXIMO to allow these PO's to
go
> > > into history so there is the option to COMPLETE RECEIPTS (even though
they
> > > have not ), this then allows the PO to be closed.
> > >
> > > From: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> > > [mailto:MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com> ] On Behalf
> > > Of
> > > dancue23
> > > Sent: Tuesday, 12 April 2011 12:07 PM
> > > To: MAXIMO@yahoogroups.com <mailto:MAXIMO%40yahoogroups.com>
<mailto:MAXIMO%40yahoogroups.com>
> > > Subject: [MAXIMO List] Able to close PO with partial receipts
> > >
> > > Why would you be able to close a PO with partial receipts? I checked
Out of
> > > the box Maximo and it allows you to close a PO with partial receipts.
> > >
> > > I checked PO options in Administration and did not see anything that
would
> > > allow this. What am I missing here?
> > >
> > > We are not using the invoice or contracts modules.
> > >
> > > Thanks for your help!
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
>