Import permits from excel sheets.
4 Main Steps
Excel Cleanup
Import to datagrip
import from table to main tblPermits table
Create Roles
Excel Cleanup
First thing to do is update the headers to match the following:
Subsidiary
Acronym
Facility
Program
Description
Activity
Frequency
StartDate
EndDate
DueDate
Instructions
DocsRequired
CurrentPermitNumber
Secondly, make sure there are no null Subsidiary, Acronym, Facility, Program, Activity, Frequency, StartDate, EndDate, DueDate, DocsRequired cells.
Acronym can be filled with “N/A.”
Subsidiary be the same 100% through, so just copy and paste.
The above line applies for Acronym as well.
I would send an email if Facility or Program is null.
DocsRequired can be set to 0 if it’s null.
StartDate = 1900-01-01 if null
EndDate = DueDate if null.
DueDate = EndDate if null.
If both EndDate and DueDate are null set to 2125-01-01.
You can get a rough idea of what the frequency should be based on the start date and due date if its null. The list of frequencies is in the ActionItem db as tblFrequency.
Import into datagrip
Save the excel sheet as a CSV file.
Import into datagrip.
Select the file.
Not sure if it matters but my settings are below.
Import into tblPermits
In the test environment, you can easily delete all permit data by running the following stored procedure. I do this every time I import a new excel sheet.
EXEC sp_RemovePermitData
After this, I run the following stored procedure. @TableName will be the new table you imported.
EXEC sp_ImportTable @TableName
Create Roles
Now you can create the new roles for the new subsidiary. You can find those here under email reminders.
Note: The acronym for the role should match the acronym in the tblPermits table linked to each task/permit.
Rolling the dates forward
Joseph will most likely ask for the dates to be rolled forward. All this means is he wants them to be renewed so that there are no expired tasks to start. What I do for this is start with the query below.
SELECT *
FROM tblPermits
WHERE PermitId IN (
SELECT PermitId
FROM vw_ExpiredPermits
)
Monthly: Just make it whatever the current month is. Sometimes the duedate will be in the next month at some point.
Ex: original’s EndDate is Oct. 31 and DueDate is Nov. 14th. Change to current month with duedate next month.
Yearly: Just change the year value.
Quarterly: Just change them to whatever the current quarter is. If it’s a week or so out from a new quarter or month I just change it to the next one.
Q1: Jan 1 - Mar 31
Q2: Apr 1 - Jun 30
Q3: Jul 1 - Sep 30
Q4: Oct 1 - Dec 31
I would renew the rest through plant focus.