Update Expense Accrual descriptions in Transaction records

Expense Accrual Update Process

After OneSource Data to JE Upload agent job completes, Expenditure Control checks the data file and creates a JE batch to load into FAME. The transaction descriptions on many of the records are ‘Expense Accruals’. The Budgeting Department will request these records so they can research what the expenses were and then we update the descriptions in FAME. After the batch is posted run this script:

SELECT DISTINCT glt_gl_gr,glt_gl_key,glt_gl_obj,glt_jl_key,glt_jl_obj,glt_gl_fy,glt_desc,glt_dr,glt_cr,glt_ref2,

glk_sec_part13, glk_sec_part14,glk_sec_part15, glt_date, t.unique_id

FROM glt_trns_dtl t

JOIN glk_key_mstr ON glk_key = glt_gl_key

WHERE glt_gl_fy ='2024' AND glt_desc = 'Expense Accruals' AND glk_sec_part15 <> '' AND glk_grp_part04 = '71' AND glk_sec_part14 <> '71597000'

Note: the FY is hardcoded and needs to be updated with new FY.

Copy data with headers to Excel sheet and name Expense Accruals MMDDYY.xlsx and send to Budgeting.

File will be returned with description changes to be uploaded into FAME.

Copy descriptions to a new sheet column A and unique ids to column B.

Find and replace all single quotes in column A with two single quotes.

Put this statement in Column C and copy for all records:

=CONCATENATE("update glt_trns_dtl set glt_desc = '"& A1 &"' where unique_id = '"& B1 &"';")

 

Set ANSI_WARNINGS OFF and paste column C data in the script:

SET ANSI_WARNINGS OFF

update glt_trns_dtl set glt_desc = 'New Description' where unique_id = '9509147';

update glt_trns_dtl set glt_desc = 'New Description' where unique_id = '9509148';

update glt_trns_dtl set glt_desc = 'New Description' where unique_id = '9509149';

SET ANSI_WARNINGS ON

 

Notify Budgeting Department that the descriptions have been uploaded.

Details

Article ID: 158388
Created
Thu 10/19/23 11:22 AM
Modified
Mon 11/13/23 11:25 AM