GL Import Transactions

Import Transactions / Import Budget Transactions

Standard Import Format

File Layout

GLAccount := Copy(CurTrx.Strings[0], 1, 20);

TrxDate := HStrToDate(CurTrx.Strings[1]);

RefNo := StrToIntDef(CurTrx.Strings[2], 0);

CheckNo := Copy(CurTrx.Strings[3], 1, 10);

TrxAmount := StrToCurrDef(CurTrx.Strings[4], 0);

SourceCode := Copy(CurTrx.Strings[5], 1, 2);

Description1 := Copy(CurTrx.Strings[6], 1, 35);

Description2 := Copy(CurTrx.Strings[6], 1, 35); <Optional>

Job ID

Budget Category

Transaction Type

Asset

"G/L ACCOUNT","TRANS DATE","REF NO.","CHECK NO.","TRANS AMT","SOURCE CODE","DESCRIPTION"
"718.00","11/29/02",0,0,-137.5,"PR","401K DED"
"718.00","11/29/02",0,0,34.38,"PR","CO 401K"
"647.00","11/29/02",0,0,-400.25,"PR","MEDICAL"
"645.00","11/29/02",0,0,-102.3,"PR","FICA"
"645.00","11/29/02",0,0,71.99,"PR","KSsutaEX"
"645.00","11/29/02",0,0,9.6,"PR","FUTA EXP"
"645.00","11/29/02",0,0,102.3,"PR","CFICA EX"
"639.00","11/29/02",0,0,1737.5,"PR","GROSS PY"
"220.00","11/29/02",0,0,-71.99,"PR","KSsutaWH"
"220.00","11/29/02",0,0,-9.6,"PR","FUTA W/H"
"215.00","11/29/02",0,0,-46.74,"PR","KS TAX"
"212.00","11/29/02",0,0,-94.72,"PR","FED TAX"
"212.00","11/29/02",0,0,-102.3,"PR","CFICA PB"
"110.00","11/29/02",0,0,-955.99,"PR","NET PAY"

Standard Import Format / Separate Debit Credit

Select Standard Text (Separate DB/CR) from the option menu.

File Layout

GLBDet.GLAccount := Copy(CurTrx.Strings[0], 1, 20);

GLBDet.TrxDate := HStrToDate(CurTrx.Strings[1]);

GLBDet.CheckNo := Copy(CurTrx.Strings[3], 1, 10);

GLBDet.DebitAmount := StrToCurrDef(CurTrx.Strings[4], 0);

GLBDet.CreditAmount := StrToCurrDef(CurTrx.Strings[5], 0);

GLBDet.SourceCode := Copy(CurTrx.Strings[6], 1, 2);

GLBDet.Description1 := Copy(CurTrx.Strings[7], 1, 35);

GLBDet.Description2 := Copy(CurTrx.Strings[8], 1, 35);

GLBDet.JobId := Copy(CurTrx.Strings[9], 1, 18);

GLBDet.JCBudCat := Copy(CurTrx.Strings[10], 1, 10);

GLBDet.JCTypeCode := Copy(CurTrx.Strings[11], 1, 10);

GLBDet.JCAssetId := Copy(CurTrx.Strings[12], 1, 20);

Creating Imports with Export Queries

(Based on Standard Text format)

G/L Replicate Session Query

SELECT GL_ACCOUNT,TRX_DATE,REF_NO,CHECK_NO,TRX_AMOUNT,SOURCE_CODE,DESCRIPTION_1,DESCRIPTION_2
 FROM GLTTRX WHERE SESSION_NO=:SESSION_NO

G/L Reverse Session Query

This is a quick export to reverse a session...

SELECT GL_ACCOUNT,TRX_DATE,REF_NO,CHECK_NO,-TRX_AMOUNT,SOURCE_CODE,DESCRIPTION_1,description_2 
FROM GLTTRX WHERE SESSION_NO=:SESSION_NO

G/L Payroll Accrual & Reversal

select 
cast(gl_account as char(20)) GL_ACCOUNT,max(TRX_DATE) trx_date,0 REF_NO,0 CHECK_NO,sum(-TRX_AMOUNT),'PR' SOURCE_CODE,'P/R Accrual Reversing' DESCRIPTION_1,'' description_2
 from glttrx T
INNER JOIN GLTACCT A ON A.GL_ACCOUNT=T.GL_ACCOUNT
where source_app='Y' and trans_type<>'B' AND account_type='I' AND TRX_DATE=:"Payroll Date"
and a.segment_1 not in ('a','b', 'c')
group by gl_account
union
select 
cast('<p/r accrual account>' as char(20)) GL_ACCOUNT,max(TRX_DATE) trx_date,0 REF_NO,0 CHECK_NO,sum(TRX_AMOUNT),'PR' SOURCE_CODE,'P/R Accrual Reversing' DESCRIPTION_1,'' description_2
 from glttrx T
INNER JOIN GLTACCT A ON A.GL_ACCOUNT=T.GL_ACCOUNT
where source_app='Y' and trans_type<>'B' AND account_type='I' AND TRX_DATE=:"Payroll Date"
and a.segment_1 not in ('a','b', 'c')
union
select 
cast(gl_account as char(20)) GL_ACCOUNT,
cast(extract(month from max(trx_date))||'/01/'||extract(year from max(trx_date)) as date)-1
,0 REF_NO,0 CHECK_NO,sum(TRX_AMOUNT),'PR' SOURCE_CODE,'P/R Accrual Reversing' DESCRIPTION_1,'' description_2
 from glttrx T
INNER JOIN GLTACCT A ON A.GL_ACCOUNT=T.GL_ACCOUNT
where source_app='Y' and trans_type<>'B' AND account_type='I' AND TRX_DATE=:"Payroll Date"
and a.segment_1 not in ('a','b', 'c')
group by gl_account
union
select 
cast('<p/r accrual account>' as char(20)) GL_ACCOUNT, 
cast(extract(month from max(trx_date))||'/01/'||extract(year from max(trx_date)) as date)-1
,0 REF_NO,0 CHECK_NO,sum(-TRX_AMOUNT),'PR' SOURCE_CODE,'P/R Accrual Reversing' DESCRIPTION_1,'' description_2
 from glttrx T
INNER JOIN GLTACCT A ON A.GL_ACCOUNT=T.GL_ACCOUNT
where source_app='Y' and trans_type<>'B' AND account_type='I' AND TRX_DATE=:"Payroll Date"
and a.segment_1 not in ('a','b', 'c')

Example Budget Maker Query

This query will create 1 line per account per period for the selected year where there is activity for Income Statement account whether or not the account is active. It can be edited and imported as a budget.

SELECT A.GL_ACCOUNT,pd.End_Date "TRX_DATE",0 REF_NO,0 CHECK_NO,SUM(TRX_AMOUNT) TRX_AMOUNT,'BE' SOURCE_CODE,'BUDGET' DESCRIPTION_1,null DESCRIPTION_2
FROM cctperiod pd
INNER JOIN GLTACCT A ON ACCOUNT_TYPE='I'
INNER JOIN GLTTRX X ON X.GL_ACCOUNT=A.GL_ACCOUNT AND X.TRX_DATE BETWEEN PD.BEGIN_DATE AND PD.END_DATE AND TRANS_TYPE NOT IN ('B','R') 
 WHERE pd.period_year=:"Year" AND pd.app_code='G'
GROUP BY A.GL_ACCOUNT,PD.END_DATE

This query will create 1 line for all active accounts (and no inactive accounts) whether or not there was any activity...

SELECT A.GL_ACCOUNT,pd.End_Date "TRX_DATE",0 REF_NO,0 CHECK_NO,
COALESCE(SUM(TRX_AMOUNT),0)
 TRX_AMOUNT,'BE' SOURCE_CODE,'BUDGET' DESCRIPTION_1,null DESCRIPTION_2
FROM cctperiod pd
INNER JOIN GLTACCT A ON A.ACTIVE_FLAG='Y' AND ACCOUNT_TYPE='I'
Left outer JOIN GLTTRX X ON X.GL_ACCOUNT=A.GL_ACCOUNT AND X.TRX_DATE BETWEEN PD.BEGIN_DATE AND PD.END_DATE AND TRANS_TYPE NOT IN ('B','R')
 WHERE pd.period_year=:"Year" AND pd.app_code='G'
GROUP BY A.GL_ACCOUNT,PD.END_DATE

(For old IB remove the COALESCE)