Max-681 - update work order columns in granite from a spreadsheet

1 - review the columns in the spreadsheet against the gnet.dbo.maximobuffer table. (we are mostly interested in the WONUM column) Rename if it doesn't exist.

develop Process

Save the intended spreadsheet to c:\t
prepare Step2, 3, and 4 scripts.
go into SSMS - log into sasddmxsql1\mxdev GNET database
- execute "delete from maximobuffer"
- In GNET database - import spreadsheet into gnet database.
- Copy data from one or more tables from the Work Orders for Maximo$ source to the [dbo].[MaximoBuffer] destination
  - On the mapping tab you want to make sure the destination column WONUM is being populated (likely from source column WoNum)
  - On the 'this execution was successful' examine the 'Copying to dbo.maximo buffer - number should be greater than 0 (9/28 - 7) (11/23 - 8495 imported)
- execute "select * from MaximoBuffer" - confirm wonum has a value
execute max681b-step2
- Rows Returned should match number imported (9/28 - 7 rows)(11/23 - 8487 rows)
- Work_order should be null, newWONO should have a value
execute max681b-step3
- Rows affected should match the number import (9/28 - 7 rows)(11/23 - 8441 rows)
execute max681b-step4
- Rows Returned should be 0 (to indicate step 3 performed properly) (11/23 - 108 rows)
- add this output to the spreadsheet

testing

go into SSMS - log into sasddmxsql1\mxqa, and then GNET database. Import spreadsheet into gnet database
- execute "Delete from maximobuffer" (11/23 - 7 rows affected)
- On the mapping tab you want to make sure the
  - - destination column WONUM is being populated (likely from source column WoNum)
  - On the 'this execution was successful' examine the 'Copying to dbo.maximo buffer - number should be greater than 0 (9/28 - 7) (11/23 - 8495 rows transferred)
    - 9/28 - no security to create table. Must create ticket
    - 9/30 - created ticket.
    - 10/1 - 7 imported
execute max681b-step2
- Rows Returned should match number imported (10/1 - 7 rows)(8539 rows)
execute max681b-step3
- Rows affected should match the number import (10/1 - 7 rows)(8493 rows)
execute max681b-step4
- Rows Returned should be 0 (to indicate step 3 performed properly) (10/1 - 0 rows)(11/24 - 56 rows)
- Add this output to this spreadsheet

Notify Phani - need needs to take action

--

Good Morning [~kotlap],

Inspections in the Gnet testing database has been updated with the workorder numbers that were added to maximo for this project.

I think the next steps are

Phani notify's George that the contents of the first tab of "Max-681B - Inspections with no work orders_2014 New Construction_noAsset (1).xlsx" have been imported into both Maximo and Granite (Dev and QA instances) - George should test the QA instance.
George will check to make sure everything is alright. He'll reply back that things look good.
I'll somehow be notified to proceed to get update production.

--

When Phani gives approval proceed to the next step.

production

Delete contents of gnetprod/mxprod.gnet.dbo.MaximoBuffer (10/20 - 33 rows deleted)
Import the spreadsheet into gnetprod/mxprod.gnet.dbo.MaximoBuffer (Confirm WONUM has a value) (10/20 - 7 rows added)
Create a task for Tracey

--

~~Import the attached spreadsheet into the Granite database on the production server.~~

I would like some of the work order numbers in GNET to be updated. Can you ...

--

Tracey,

I would like have some data in GNET updated. So can you...

1 - I've attached a spreadsheet, can you Import the Contents of the 'Work Orders for Maximo$' tab into a new table named MaximoBuffer on the GNET database of the production server (I believe sasdpmxsql1\mxprod).

~~The process for doing this has been documented in a confluence page:~~
~~https://confluence.sacsewer.com/pages/viewpage.action?pageId=45187716~~

2 - Execute the SQL contained in the attachment 'max722-step2.sql'. Execute this in the GNET database on the production server. This is a test, can you add the number of rows return count to this ticket? (thanks)

3 - Execute the SQL contained in the attachment 'max722-step3.sql'. Execute this in the GNET database on the production server. This is the actual update statement, can you add the number of rows return count to this ticket? (thanks)

4 - Execute the SQL contained in the attachment 'max722-step4.sql'. Execute this in the GNET database on the production server. This is a list of workorders in maximo associated with this project that did not get updated. Place the output of this into a new tab in either a new spreadsheet, or the spreadsheet attached to this task? Actually, with this batch I'm hoping 0 records will be returned. (thanks)

Thanks

Mark

~~(Note: this ticket is similar to ~~ITOPS-1312~~ except with a different set of data)~~

--

Finally

Copy all SQL Scripts and associated spreadsheets to max-681
Notify Phani - he needs to take action