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.
Maximo 6.2.6, SQL Server
We have some "old habits die hard" issues I'm running into, and I'm trying to combat them, but need some help.
Purchasing used to make PO's as Excel spreadsheets (one Excel file per PO) AND then had an Excel file to summarize those purchases ("the PO log").
So we moved them to Maximo, now they do BOTH a Maximo PO and the Excel PO Log.
Then the "Invoicing" Department would make their own log, borrowing from the PO Log and adding Invoice info to it as invoices came in ("the Invoice Log"). They also enter Invoice info into Maximo.
Where my problem begins to creep in is carrying over from the way things used to be, pre-Maximo when we couldn't do any better. They decided that each PO should be for one project, one place, one person, one account. These are a lot of things that Maximo handles at the PO Line level (and rightfully so). So if I need to buy two items for two jobs that have been assigned to me, and I'm buying them from the same store, I need two PO's. Or if I need to buy stock/inventory, and you need to buy inventory for your storeroom from the same vendor, we'd still need two separate POs.
So both departments are entering the same data, TWICE. Lots of wasted effort.
I want to eradicate the Invoice Log. One of the pieces of info they put in this Log is the Location. To the people making the Log, that's a PO-level piece of data. To Maximo, it's on the POLine. So when I try to join these together in Maximo, I get as many rows in my result set as there were POLines. I need it to return one row.
There are other tables being joined too, to correctly replicate this Invoice Log. Here's what I have so far:
SELECT
po.ponum, companies.name, locations.description, poline.gldebitacct, invoice.description, poline.polaln3, invoice.totalcost, invoice.invoicedate, invoice.vendorinvoicenum, invoice.invoicenum, poline.refwo
FROM
po
LEFT JOIN
companies
ON
po.vendor = companies.company
LEFT JOIN
poline
ON
po.ponum = poline.ponum AND po.siteid = poline.siteid
LEFT JOIN
locations
ON poline.location = locations.location AND poline.siteid = locations.siteid
LEFT JOIN
invoiceline
ON
po.ponum = invoiceline.ponum AND po.siteid = invoiceline.siteid
LEFT JOIN
invoice
ON
invoiceline.invoicenum = invoice.invoicenum AND invoiceline.siteid = invoice.siteid
WHERE
po.ponum = '11191319H'
That WHERE clause is just to help me check how my query is working. If it works for that one, it should work for all, and I would eliminate the clause. Anyways, that PO # has two invoices against it. The PO has 11 line items. They have all been invoiced. The result set from this query returns 121 lines.
I've tried some tricks with grouping, and the best I've gotten it down to is 22 rows.
Since any of the data that's on the POLines or InvoiceLines **should** be the same for each row on a PO or Invoice, I'd think I should be able to take just the first POLine's data and go with that. If that were always POLine #1, this would be easy. But I can't make that assumption. At least back on this version of Maximo, the POLines don't get re-numbered sequentially; it's possible that 2 (or any number) could be the lowest POLineNum for any PO.
I've tried throwing in DISTINCT and MIN operators in there and still can't get it to work. The result I expect from this is two rows, one row per invoice. Any ideas?
Or if anyone can help me on a related issue...
I'm trying to create a view for this. I put in some SQL for the "View Select" attribute to specify the fields I wanted, and some SQL in the "View From" attribute to tell it the tables to join, and how to join them.
I didn't want to tell it to extend a particular object, but it would not let me save without filling in the "Extends Object" field, so I chose PO.
That AUTOMATICALLY makes it so that the Attributes I get (shown on the Attributes tab of the DBConfig app) are all the Attributes of the PO table. It isn't looking at my custom Select list. (Unless maybe it doesn't like something I put in there -- I did put a CASE statement in there -- and is choosing to ignore it).
Anyways, how do I make it use the fields I want it to use?
Create the view in sql server first, then in maximo when you create the view, just enter the view name of the one you created. It should then populate..
________________________________
From: "therron@pcci.edu" <therron@pcci.edu>
To: MAXIMO@yahoogroups.com
Sent: Monday, December 23, 2013 1:11 PM
Subject: [MAXIMO List] RE: I need SQL statement help too
Or if anyone can help me on a related issue...
I'm trying to create a view for this. I put in some SQL for the "View Select" attribute to specify the fields I wanted, and some SQL in the "View From" attribute to tell it the tables to join, and how to join them.
I didn't want to tell it to extend a particular object, but it would not let me save without filling in the "Extends Object" field, so I chose PO.
That AUTOMATICALLY makes it so that the Attributes I get (shown on the Attributes tab of the DBConfig app) are all the Attributes of the PO table. It isn't looking at my custom Select list. (Unless maybe it doesn't like something I put in there -- I did put a CASE statement in there -- and is choosing to ignore it).
Anyways, how do I make it use the fields I want it to use?
Not working.
I tried this, only to find out that the database had already created this view correctly. But within Maximo, it is acting incorrectly.
So, in SQL Management Studio, I can query:
select * from pcc_pologview
and get EXACTLY what I intended for it to find. That part works.
So I go into Maximo to create this, and it still wants me to extend some particular object, so I again chose PO. And it again adds all the columns of the PO object to the attributes tab.
So I stop Maximo, run configDB, start Maximo again. I refresh SQL Management Studio and look at my Tables listings, and it is all correct (it didn't add a table called pcc_pologview). I look at the Views listings, and there's my view, the way I made it. It is right too.
Now I'm trying to design an app, basing it on this view. I have the attribute PO.STATUS aliased in my view as POSTATUS. I try to add that to the screen, and I keep getting "Invalid Binding" no matter how I reference it.
Not at a computer with Maximo right now and I was using 7, it has been awhile, but it should work.
________________________________
From: "therron@pcci.edu" <therron@pcci.edu>
To: MAXIMO@yahoogroups.com
Sent: Monday, December 23, 2013 3:19 PM
Subject: Re: [MAXIMO List] RE: I need SQL statement help too
Not working.
I tried this, only to find out that the database had already created this view correctly. But within Maximo, it is acting incorrectly.
So, in SQL Management Studio, I can query:
select * from pcc_pologview
and get EXACTLY what I intended for it to find. That part works.
So I go into Maximo to create this, and it still wants me to extend some particular object, so I again chose PO. And it again adds all the columns of the PO object to the attributes tab.
So I stop Maximo, run configDB, start Maximo again. I refresh SQL Management Studio and look at my Tables listings, and it is all correct (it didn't add a table called pcc_pologview). I look at the Views listings, and there's my view, the way I made it. It is right too.
Now I'm trying to design an app, basing it on this view. I have the attribute PO.STATUS aliased in my view as POSTATUS. I try to add that to the screen, and I keep getting "Invalid Binding" no matter how I reference it.
After the Object Name (MAXOBJECTCFG.OBJECTNAME) is populated, all the
corresponding information - View? will be checked, View Select, View
From, View Where, etc will be populated with the information from Step
The Key is to also is theExtends Object will now NOT be required and
the Imported? (MAXOBJECTCFG.IMPORTED) will be checked.
Click on Save and the Object will be marked 'To Be Added'
________________________________
From: "wwilliams@rocketmail.com" <wwilliams@rocketmail.com>
To: "MAXIMO@yahoogroups.com" <MAXIMO@yahoogroups.com>
Sent: Monday, December 23, 2013 5:56 PM
Subject: Re: [MAXIMO List] RE: I need SQL statement help too
Not at a computer with Maximo right now and I was using 7, it has been awhile, but it should work.
________________________________
From: "therron@pcci.edu" <therron@pcci.edu>
To: MAXIMO@yahoogroups.com
Sent: Monday, December 23, 2013 3:19 PM
Subject: Re: [MAXIMO List] RE: I need SQL statement help too
Not working.
I tried this, only to find out that the database had already created this view correctly. But within Maximo, it is acting incorrectly.
So, in SQL Management Studio, I can query:
select * from pcc_pologview
and get EXACTLY what I intended for it to find. That part works.
So I go into Maximo to create this, and it still wants me to extend some particular object, so I again chose PO. And it again adds all the columns of the PO object to the attributes tab.
So I stop Maximo, run configDB, start Maximo again. I refresh SQL Management Studio and look at my Tables listings, and it is all correct (it didn't add a table called pcc_pologview). I look at the Views listings, and there's my view, the way I made it. It is right too.
Now I'm trying to design an app, basing it on this view. I have the attribute PO.STATUS aliased in my view as POSTATUS. I try to add that to the screen, and I keep getting "Invalid Binding" no matter how I reference it.
Wes,
I don't follow. . .?
I'm pretty sure this must be a bug, but since it is past end-of-life I hesitate to even bother asking IBM.
Basically, if I mark this as a View, it ends up requiring me to choose an "Extends Object." Once I do that, then all the attributes from that object are copied over to the Attributes tab of my View.
Then if you go in the back-end and look at the database, the view is there, created correctly. There is no table created for it. Yet there are entries in the MAXATTRIBUTECFG for all of those copied attributes. And they are NOT marked as user-created, so you can't delete them.
I even then went in the back-end and UPDATED them all as user-created, then went to the front end and deleted them all. Tried to run configdb, but it failed, complaining that my object didn't have any attributes.
I also had found something in the IBM KB saying the view definition would need to have the Rowstamp included in the SELECT statement if you un-checked the Automatically Select option.
The "kicker" in all this is that there's an OOTB view in there, WMASSIGNMENT, that has a custom SELECT statement, a custom FROM clause, and DOES NOT extend an object. I'm guessing they made that work because it uses it's own class file.
I guess at this point I'm going to have to park this project till after we finally upgrade to version 7, and hope they have this fixed. I have the SQL statement created that can make get the info I want, how I want it, from the back end. So I'll either leave it at that, or write a report.
Travis Herron
You did this -- Create the View from the backend (via a SQL tool) using the Syntax as per your Database. When this view has been created, access the Database Configuration application, click on NEW,in the Object Name field, input your View Name then tab.
After
the Object Name (MAXOBJECTCFG.OBJECTNAME) is populated, all the
corresponding information - View? will be checked, View Select, View
From, View Where, etc will be populated with the information from the first step.
The Key is to also is the Extends Object will now NOT be required and the Imported? (MAXOBJECTCFG.IMPORTED) will be checked.
Click on Save and the Object will be marked 'To Be Added'
Ensure you have a current backup of the Database, Turn on Admin Mode,
Apply Configuration Changes, Turn Off Admin Mode and test the View.
so it won't allow you to uncheck?
Did you start from scratch on the Maximo side?
________________________________
From: "therron@pcci.edu" <therron@pcci.edu>
To: MAXIMO@yahoogroups.com
Sent: Tuesday, December 24, 2013 8:22 AM
Subject: Re: [MAXIMO List] RE: I need SQL statement help too
Wes,
I don't follow. . .?
I'm pretty sure this must be a bug, but since it is past end-of-life I hesitate to even bother asking IBM.
Basically, if I mark this as a View, it ends up requiring me to choose an "Extends Object." Once I do that, then all the attributes from that object are copied over to the Attributes tab of my View.
Then if you go in the back-end and look at the database, the view is there, created correctly. There is no table created for it. Yet there are entries in the MAXATTRIBUTECFG for all of those copied attributes. And they are NOT marked as user-created, so you can't delete them.
I even then went in the back-end and UPDATED them all as user-created, then went to the front end and deleted them all. Tried to run configdb, but it failed, complaining that my object didn't have any attributes.
I also had found something in the IBM KB saying the view definition would need to have the Rowstamp included in the SELECT statement if you un-checked the Automatically Select option.
The "kicker" in all this is that there's an OOTB view in there, WMASSIGNMENT, that has a custom SELECT statement, a custom FROM clause, and DOES NOT extend an object. I'm guessing they made that work because it uses it's own class file.
I guess at this point I'm going to have to park this project till after we finally upgrade to version 7, and hope they have this fixed. I have the SQL statement created that can make get the info I want, how I want it, from the back end. So I'll either leave it at that, or write a report.
Travis Herron
Will this work?
select po.ponum, pl.gldebitacct, invoice.description, invoice.totalcost, (select description from locations where location = pl.location) locationdescrip,
invoice.invoicedate, invoice.vendorinvoicenum, invoice.invoicenum, pl.refwo, (select companies.name from companies where company = po.vendor) coname
from po
Left join invoice on po.ponum = invoice.ponum and po.siteid = invoice.positeid
join (select ponum, gldebitacct, refwo, siteid, location from poline group by ponum, gldebitacct, refwo, siteid, location) pl on pl.ponum = po.ponum and po.siteid = pl.siteid
where po.ponum = '11191319H'
________________________________
From: "therron@pcci.edu" <therron@pcci.edu>
To: MAXIMO@yahoogroups.com
Sent: Monday, December 23, 2013 9:47 AM
Subject: [MAXIMO List] I need SQL statement help too
Maximo 6.2.6, SQL Server
We have some "old habits die hard" issues I'm running into, and I'm trying to combat them, but need some help.
Purchasing used to make PO's as Excel spreadsheets (one Excel file per PO) AND then had an Excel file to summarize those purchases ("the PO log").
So we moved them to Maximo, now they do BOTH a Maximo PO and the Excel PO Log.
Then the "Invoicing" Department would make their own log, borrowing from the PO Log and adding Invoice info to it as invoices came in ("the Invoice Log"). They also enter Invoice info into Maximo.
Where my problem begins to creep in is carrying over from the way things used to be, pre-Maximo when we couldn't do any better. They decided that each PO should be for one project, one place, one person, one account. These are a lot of things that Maximo handles at the PO Line level (and rightfully so). So if I need to buy two items for two jobs that have been assigned to me, and I'm buying them from the same store, I need two PO's. Or if I need to buy stock/inventory, and you need to buy inventory for your storeroom from the same vendor, we'd still need two separate POs.
So both departments are entering the same data, TWICE. Lots of wasted effort.
I want to eradicate the Invoice Log. One of the pieces of info they put in this Log is the Location. To the people making the Log, that's a PO-level piece of data. To Maximo, it's on the POLine. So when I try to join these together in Maximo, I get as many rows in my result set as there were POLines. I need it to return one row.
There are other tables being joined too, to correctly replicate this Invoice Log. Here's what I have so far:
SELECT
po.ponum,companies.name,locations.description,poline.gldebitacct,invoice.description,poline.polaln3,invoice.totalcost,invoice.invoicedate,invoice.vendorinvoicenum,invoice.invoicenum,poline.refwo
FROM
po
LEFT JOIN
companies
ON
po.vendor =companies.company
LEFT JOIN
poline
ON
po.ponum =poline.ponum AND po.siteid =poline.siteid
LEFT JOIN
locations
ON poline.location =locations.location AND poline.siteid =locations.siteid
LEFT JOIN
invoiceline
ON
po.ponum =invoiceline.ponum AND po.siteid =invoiceline.siteid
LEFT JOIN
invoice
ON
invoiceline.invoicenum =invoice.invoicenum AND invoiceline.siteid =invoice.siteid
WHERE
po.ponum ='11191319H'
That WHERE clause is just to help me check how my query is working. If it works for that one, it should work for all, and I would eliminate the clause. Anyways, that PO # has two invoices against it. The PO has 11 line items. They have all been invoiced. The result set from this query returns 121 lines.
I've tried some tricks with grouping, and the best I've gotten it down to is 22 rows.
Since any of the data that's on the POLines or InvoiceLines **should** be the same for each row on a PO or Invoice, I'd think I should be able to take just the first POLine's data and go with that. If that were always POLine #1, this would be easy. But I can't make that assumption. At least back on this version of Maximo, the POLines don't get re-numbered sequentially; it's possible that 2 (or any number) could be the lowest POLineNum for any PO.
I've tried throwing in DISTINCT and MIN operators in there and still can't get it to work. The result I expect from this is two rows, one row per invoice. Any ideas?
Yep, that looks like it'll work! Thanks!
Pretty sure I did all that. I'm guessing you're thinking of this from a 7.x perspective -- which is good, maybe that means they've got this fixed in future versions -- but I don't think it was letting me do this on 6.2.6.
Made the view in the back-end
Went to Database Configuration app
Added the new object, named it the same name as the view I had created in the back-end
At that point, View? was still unchecked, and there were two Attributes for the object -- I think they were an ID field and a rowstamp. I went ahead and saved it and ran configdb. I ended up with a table.
As a side note -- aren't we told to make EVERYTHING in the front-end, else we risk it not being carried forward properly in an upgrade?
Travis Herron