FAME - Create JE Upload files from OneSource data

JE Upload Files from OneSource

(Check Important Notes at bottom of document for calendar year changes)

Expenditure Control runs an extraction process in OneSource that creates an Excel data file. There are 3 changes made to the file before it is placed in the share folder.

Delete row one - header record with Journal in A1

Delete the last column – header Posted

Round off the Amount column to 2 decimal places

The file must be named UGADataYYYYMMDD.xlsx and placed in the Everybody\OneSource Data to JE Upload folder.

Step 1 - The SQL Agent job OneSource Data to JE Upload runs daily looking for files with the current date.

Step 2 - copies the file to C:\Pres JE folder on the SQL server and moves the original file into the Everybody\OneSource Data to JE Upload\Processed Files folder.

# Variables

$date = Get-Date

$dateStr = $date.ToString("yyyyMMdd")

$fileName = "UGAData$dateStr.xlsx"

$everybodyfolder = "\\msmyid.uga.edu\DAR\files\Everybody\OneSource Data to JE Upload"

Copy-Item -Path "Microsoft.PowerShell.Core\FileSystem::$everybodyFolder\$fileName" -Destination "Microsoft.PowerShell.Core\FileSystem::C:\Pres JE"

Rename-Item -Path "C:\Pres JE\$filename" -NewName "C:\Pres JE\UGAData.xlsx"

Move-Item -Path "Microsoft.PowerShell.Core\FileSystem::$everybodyFolder\$fileName" -Destination "Microsoft.PowerShell.Core\FileSystem::$everybodyFolder\Processed Files"

 

Step 3 Deletes records in the onesource_raw_data2 table

Step 4  SSIS package loads the data into the onesource_raw_data2 table

Step 5 runs SQL to set current date, create a unique key for each record, clears out onsource_raw_data3 table and inserts records from onesource_raw_data2 that are not in onesource_raw_data table, adds those records to onesource_raw_data and clears out onesource_raw_data4, inserts records in onesource_raw_data3 into onesource_raw_data4.

UPDATE onesource_raw_data2

SET CURRDATE = (CONVERT(VARCHAR(10), GETDATE(),101))

 

UPDATE onesource_raw_data2

SET UNIQUEKEY = CONCAT(RTRIM(DEPT),RTRIM(CLASS),

RTRIM(JOURNAL_ID),RTRIM(LINE_NO),RTRIM(AMOUNT))

 

DELETE onesource_raw_data3

INSERT INTO onesource_raw_data3

SELECT * FROM onesource_raw_data2 WHERE onesource_raw_data2.UNIQUEKEY

NOT IN (SELECT UNIQUEKEY FROM onesource_raw_data)

 

INSERT INTO onesource_raw_data

SELECT * FROM onesource_raw_data3

 

DELETE FROM onesource_raw_data4

INSERT INTO onesource_raw_data4

SELECT * from onesource_raw_data3

 

Step 6 – Updates data in onesource_raw_data4 for JE Upload utility layout

 

USE production_finance

 

 

UPDATE onesource_raw_data4

SET REF = 'XX'

WHERE REF IS NULL

 

UPDATE onesource_raw_data4

SET LINE_DESCR = 'UGA Activity'

WHERE LINE_DESCR IS NULL

 

UPDATE onesource_raw_data4

SET PERIOD =

CASE

WHEN PERIOD = 1 THEN 'Jl' + REF

WHEN PERIOD = 2 THEN 'Au' + REF

WHEN PERIOD = 3 THEN 'Se' + REF

WHEN PERIOD = 4 THEN 'Oc' + REF

WHEN PERIOD = 5 THEN 'No' + REF

WHEN PERIOD = 6 THEN 'De' + REF

WHEN PERIOD = 7 THEN 'Ja' + REF

WHEN PERIOD = 8 THEN 'Fe' + REF

WHEN PERIOD = 9 THEN 'Mr' + REF

WHEN PERIOD = 10 THEN 'Ap' + REF

WHEN PERIOD = 11 THEN 'My' + REF

WHEN PERIOD = 12 THEN 'Jn' + REF

END

 

 

UPDATE onesource_raw_data4

SET PERIOD =

CASE

WHEN SUBSTRING(PERIOD,1,2)  = 'Jl' THEN 'July'

WHEN SUBSTRING(PERIOD,1,2)  = 'Au' THEN 'August'

WHEN SUBSTRING(PERIOD,1,2) = 'Se' THEN 'September'

WHEN SUBSTRING(PERIOD,1,2) = 'Oc' THEN 'October'

WHEN SUBSTRING(PERIOD,1,2) = 'No' THEN 'November'

WHEN SUBSTRING(PERIOD,1,2) = 'De' THEN 'December'

WHEN SUBSTRING(PERIOD,1,2) = 'Ja' THEN 'January'

WHEN SUBSTRING(PERIOD,1,2) = 'Fe' THEN 'February'

WHEN SUBSTRING(PERIOD,1,2) = 'Mr' THEN 'March'

WHEN SUBSTRING(PERIOD,1,2) = 'Ap' THEN 'April'

WHEN SUBSTRING(PERIOD,1,2) = 'My' THEN 'May'

WHEN SUBSTRING(PERIOD,1,2) = 'Jn' THEN 'June'

END

WHERE REF = 'XX'

 

 

DELETE Simpler_Data

INSERT INTO Simpler_Data

SELECT LINE_DESCR,SUBSTRING(ACCOUNT,1,1),CLASS,AMOUNT,

PERIOD,CHARTFIELD_1, UNIQUEKEY 

FROM onesource_raw_data4 JOIN glk_key_mstr ON CLASS = glk_sec_part13

WHERE glk_gr = 'GL'

 

 

INSERT INTO onesource_data_excludes

SELECT * FROM onesource_raw_data4

WHERE onesource_raw_data4.CLASS

NOT IN (Select ClassCode from Simpler_data)

 

 

UPDATE Simpler_Data

SET TransDesc =

CASE

WHEN AcctCode = 5 THEN 'UGA Personnel'

WHEN AcctCode = 6 THEN 'UGA Travel'

WHEN AcctCode = 7 THEN 'UGA Operating'

WHEN AcctCode = 8 THEN 'UGA Equipment'

ELSE 'UGA Transactions'

END

 

UPDATE Simpler_Data

SET AcctCode =

CASE

WHEN AcctCode = 5 THEN '96120'

WHEN AcctCode = 6 THEN '96130'

WHEN AcctCode = 7 THEN '96110'

WHEN AcctCode = 8 THEN '96140'

ELSE 'XXXXX'

END

 

DELETE Simpler_JE

INSERT INTO Simpler_JE

SELECT ClassCode, TransAmt, TransDesc, AcctCode, PayPeriod, ChartField1, 00, CONVERT(varchar(8),GETDATE(), 1), UNIQUEKEY

FROM Simpler_Data LEFT JOIN glk_key_mstr ON ClassCode = glk_sec_part13

WHERE (glk_key LIKE '1%' OR glk_key LIKE '2%')

AND glk_gr = 'GL'

 

UPDATE Simpler_JE

SET Simpler_JE.TransDesc = onesource_raw_data4.LINE_DESCR

FROM Simpler_JE INNER JOIN onesource_raw_data4 ON Simpler_JE.UNIQUEKEY = onesource_raw_data4.UNIQUEKEY

 

DELETE FROM Simpler_Data

WHERE Simpler_Data.ClassCode IN (Select ClassCode from Simpler_JE)

 

 

UPDATE Simpler_Data

SET PayPeriod =

CASE

WHEN SUBSTRING(PayPeriod,1,2)  = 'Jl' THEN 'July'

WHEN SUBSTRING(PayPeriod,1,3)  = 'Jul' THEN 'July'

WHEN SUBSTRING(PayPeriod,1,2)  = 'Au' THEN 'August'

WHEN SUBSTRING(PayPeriod,1,2) = 'Se' THEN 'September'

WHEN SUBSTRING(PayPeriod,1,2) = 'Oc' THEN 'October'

WHEN SUBSTRING(PayPeriod,1,2) = 'No' THEN 'November'

WHEN SUBSTRING(PayPeriod,1,2) = 'De' THEN 'December'

WHEN SUBSTRING(PayPeriod,1,2) = 'Ja' THEN 'January'

WHEN SUBSTRING(PayPeriod,1,2) = 'Fe' THEN 'February'

WHEN SUBSTRING(PayPeriod,1,2) = 'Mr' THEN 'March'

WHEN SUBSTRING(PayPeriod,1,3) = 'Mar' THEN 'March'

WHEN SUBSTRING(PayPeriod,1,2) = 'Ap' THEN 'April'

WHEN SUBSTRING(PayPeriod,1,2) = 'My' THEN 'May'

WHEN SUBSTRING(PayPeriod,1,3) = 'May' THEN 'May'

WHEN SUBSTRING(PayPeriod,1,2) = 'Jn' THEN 'June'

WHEN SUBSTRING(PayPeriod,1,3) = 'Jun' THEN 'June'

END

 

 

INSERT INTO Simpler_JE

SELECT DISTINCT ClassCode, SUM(TransAmt), TransDesc, MAX(AcctCode),

MAX(PayPeriod), MAX(ChartField1), 00,  CONVERT(varchar(8),GETDATE(), 1), MAX(UNIQUEKEY)

FROM Simpler_Data

GROUP BY TransDesc, ClassCode, ChartField1

 

UPDATE Simpler_JE

SET MonthNum =

CASE

WHEN SUBSTRING(PayPeriod,1,2)  = 'Jl' THEN '07'

WHEN SUBSTRING(PayPeriod,1,3)  = 'Jul' THEN '07'

WHEN SUBSTRING(PayPeriod,1,2)  = 'Au' THEN '08'

WHEN SUBSTRING(PayPeriod,1,2) = 'Se' THEN '09'

WHEN SUBSTRING(PayPeriod,1,2) = 'Oc' THEN '10'

WHEN SUBSTRING(PayPeriod,1,2) = 'No' THEN '11'

WHEN SUBSTRING(PayPeriod,1,2) = 'De' THEN '12'

WHEN SUBSTRING(PayPeriod,1,2) = 'Ja' THEN '01'

WHEN SUBSTRING(PayPeriod,1,2) = 'Fe' THEN '02'

WHEN SUBSTRING(PayPeriod,1,2) = 'Mr' THEN '03'

WHEN SUBSTRING(PayPeriod,1,3) = 'Mar' THEN '03'

WHEN SUBSTRING(PayPeriod,1,2) = 'Ap' THEN '04'

WHEN SUBSTRING(PayPeriod,1,2) = 'My' THEN '05'

WHEN SUBSTRING(PayPeriod,1,3) = 'May' THEN '05'

WHEN SUBSTRING(PayPeriod,1,2) = 'Jn' THEN '06'

WHEN SUBSTRING(PayPeriod,1,3) = 'Jun' THEN '06'

END

 

 

UPDATE Simpler_JE

SET Simpler_JE.JEDATE = REPLACE(onesource_raw_data4.[DATE],'2023','23')

FROM Simpler_JE INNER JOIN onesource_raw_data4 ON Simpler_JE.UNIQUEKEY = onesource_raw_data4.UNIQUEKEY

 

DELETE Simpler_JE1

INSERT INTO Simpler_JE1

SELECT  'UG' + MIN(MonthNum) + SUBSTRING(CONVERT(varchar(10),GETDATE(), 110),7,4) AS SETID,

'SYSTEM', MIN(PayPeriod), MIN(PayPeriod),'V059822','UNIVERSITY OF GEORGIA – GRANTS','','',MAX(JEDATE),

'10000000', '30430','','','NY','','GL','','','','','',SUM(TransAmt),'','','',MIN(ChartField1),'KMNORMAN',MIN(ClassCode),'','','',''

FROM Simpler_JE

 

 

UPDATE Simpler_JE1

SET [DESC] =

CASE

WHEN SUBSTRING([DESC],1,2) = 'Jl' THEN 'UGA Grant Activity – July'

WHEN SUBSTRING([DESC],1,3) = 'Jul' THEN 'UGA Grant Activity – July'

WHEN SUBSTRING([DESC],1,2) = 'Au' THEN 'UGA Grant Activity – August'

WHEN SUBSTRING([DESC],1,2) = 'Se' THEN 'UGA Grant Activity – September'

WHEN SUBSTRING([DESC],1,2) = 'Oc' THEN 'UGA Grant Activity – October'

WHEN SUBSTRING([DESC],1,2) = 'No' THEN 'UGA Grant Activity – November'

WHEN SUBSTRING([DESC],1,2) = 'De' THEN 'UGA Grant Activity – December'

WHEN SUBSTRING([DESC],1,2) = 'Ja' THEN 'UGA Grant Activity – January'

WHEN SUBSTRING([DESC],1,2) = 'Fe' THEN 'UGA Grant Activity – February'

WHEN SUBSTRING([DESC],1,2) = 'Mr' THEN 'UGA Grant Activity – March'

WHEN SUBSTRING([DESC],1,3) = 'Mar' THEN 'UGA Grant Activity – March'

WHEN SUBSTRING([DESC],1,2) = 'Ap' THEN 'UGA Grant Activity – April'

WHEN SUBSTRING([DESC],1,2) = 'My' THEN 'UGA Grant Activity – May'

WHEN SUBSTRING([DESC],1,3) = 'May' THEN 'UGA Grant Activity – May'

WHEN SUBSTRING([DESC],1,2) = 'Jn' THEN 'UGA Grant Activity – June'

WHEN SUBSTRING([DESC],1,3) = 'Jun' THEN 'UGA Grant Activity – June'

END

 

UPDATE Simpler_JE1

SET SECREF =

CASE

WHEN SUBSTRING(SECREF,1,2) = 'Jl' THEN 'July'

WHEN SUBSTRING(SECREF,1,3) = 'Jul' THEN 'July'

WHEN SUBSTRING(SECREF,1,2) = 'Au' THEN 'August'

WHEN SUBSTRING(SECREF,1,2) = 'Se' THEN 'September'

WHEN SUBSTRING(SECREF,1,2) = 'Oc' THEN 'October'

WHEN SUBSTRING(SECREF,1,2) = 'No' THEN 'November'

WHEN SUBSTRING(SECREF,1,2) = 'De' THEN 'December'

WHEN SUBSTRING(SECREF,1,2) = 'Ja' THEN 'January'

WHEN SUBSTRING(SECREF,1,2) = 'Fe' THEN 'February'

WHEN SUBSTRING(SECREF,1,2) = 'Mr' THEN 'March'

WHEN SUBSTRING(SECREF,1,3) = 'Mar' THEN 'March'

WHEN SUBSTRING(SECREF,1,2) = 'Ap' THEN 'April'

WHEN SUBSTRING(SECREF,1,2) = 'My' THEN 'May'

WHEN SUBSTRING(SECREF,1,3) = 'May' THEN 'May'

WHEN SUBSTRING(SECREF,1,2) = 'Jn' THEN 'June'

WHEN SUBSTRING(SECREF,1,3) = 'Jun' THEN 'June'

END

 

 

INSERT INTO Simpler_JE1

SELECT  'UG' + MonthNum + SUBSTRING(CONVERT(varchar(10),GETDATE(), 110),7,4) AS SETID,

'SYSTEM', TransDesc, PayPeriod,'V059822','UNIVERSITY OF GEORGIA – GRANTS','','',JEDATE,

glk_key, AcctCode,'','','NY','','GL','','','','',TransAmt,'','','','',ChartField1,'KMNORMAN',ClassCode,'','','',''

FROM Simpler_JE LEFT JOIN glk_key_mstr ON ClassCode = glk_sec_part13

WHERE TransAmt <> '0.00'

AND glk_gr = 'GL'

 

 

UPDATE Simpler_JE1

SET WORKORD = ''

WHERE WORKORD IS NULL

 

UPDATE Simpler_JE1

SET JLOBJ = WORKORD

WHERE (GLKEY LIKE '1%' OR GLKEY LIKE '2%')

 

UPDATE Simpler_JE1

SET JLKEY = GLKEY

WHERE (GLKEY LIKE '1%' OR GLKEY LIKE '2%')

AND CRAMT = ''

 

UPDATE Simpler_JE1

SET JLOBJ = '9999'

WHERE (GLKEY LIKE '1%' OR GLKEY LIKE '2%')

AND JLOBJ = ''

 

UPDATE Simpler_JE1

SET JLOBJ = '', JLKEY = '', WORKORD = '', CONTRNO = ''

WHERE CRAMT <> ''

 

DELETE Simpler_JE2

INSERT INTO Simpler_JE2

SELECT *

FROM Simpler_JE1

 

UPDATE Simpler_JE2

SET Simpler_JE2.JEDATE = REPLACE(Simpler_JE2.JEDATE,'23  ','2023')

 

Step 7 – SSIS package creates Excel file from onesource_raw_data4 table

 

Step 8 – Rename file and move to file to Everybody\OneSource data to JE Upload\JE Upload Files\YYYY\YYYMM folders. These files are already created on the server.

 

# Variables

$date = Get-Date

$dateStr1 = $date.ToString("yyyy")

$dateStr2 = $date.ToString("yyyyMM")

$dateStr3 = $date.ToString("yyyyMMdd")

$everybodyUpFiles = "\\msmyid.uga.edu\DAR\files\Everybody\OneSource Data to JE Upload\JE Upload Files\$dateStr1\$dateStr2"

$fileName = "JE Upload File.xlsx"

 

Rename-Item -Path "C:\Daily JE\$fileName" -NewName "C:\Daily JE\JE Upload$dateStr3.csv"

Move-Item -Path "Microsoft.PowerShell.Core\FileSystem::C:\Daily JE\JE Upload$dateStr3.csv" -Destination "Microsoft.PowerShell.Core\FileSystem::$everybodyUpFiles"

Remove-Item -Path "C:\Pres JE\UGAData.xlsx"

 

 

IMPORTANT NOTES:

 

  • File from EC must have current date in name and header record must be removed.

 

  • If the process needs to be rerun, move the file in the Processed Files folder back to the Everybody\OneSource data to JE Upload folder and change the date if needed. Delete the records in the onesource_raw_data files that have the date it was processed as current date.

 

DELETE onesource_raw_data

WHERE CURRDATE = 'YYYY-MM-DD'

 

  • There are 2 places in SQL code Step 5 where the year is forced into a field in the table before the file is created. This reflects the calendar year and must be adjusted before the first file with January transactions is run.

 

UPDATE Simpler_JE

SET Simpler_JE.JEDATE = REPLACE(onesource_raw_data4.[DATE],'YYYY','YY')

 

UPDATE Simpler_JE2

SET Simpler_JE2.JEDATE = REPLACE(Simpler_JE2.JEDATE,'YY  ','YYYY')

 

Details

Article ID: 158381
Created
Thu 10/19/23 9:37 AM
Modified
Mon 11/13/23 11:26 AM