WikiAdmin: 1 revision imported

2023-07-14T12:58:50Z

1 revision imported

← Older revision

Revision as of 12:58, 14 July 2023

Chanson: /* Standard Import Format / Separate Debit Credit */

2023-06-15T17:24:17Z

Standard Import Format / Separate Debit Credit

New page

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

<PRE>
"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"
</PRE>

== 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);
<i>
: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);
</i>

==Creating Imports with Export Queries==

(Based on Standard Text format)

===G/L Replicate Session Query===
<PRE>
SELECT GL_ACCOUNT,TRX_DATE,REF_NO,CHECK_NO,TRX_AMOUNT,SOURCE_CODE,DESCRIPTION_1,DESCRIPTION_2
FROM GLTTRX WHERE SESSION_NO=:SESSION_NO
</PRE>

===G/L Reverse Session Query===
This is a quick export to reverse a session...
<PRE>
SELECT GL_ACCOUNT,TRX_DATE,REF_NO,CHECK_NO,-TRX_AMOUNT,SOURCE_CODE,DESCRIPTION_1,description_2
FROM GLTTRX WHERE SESSION_NO=:SESSION_NO
</PRE>

===G/L Payroll Accrual & Reversal===
<PRE>
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')
</PRE>

===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.
<PRE>
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
</PRE>

This query will create 1 line for all active accounts (and no inactive accounts) whether or not there was any activity...
<PRE>
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
</PRE>
(For old IB remove the COALESCE)

GL Import Transactions - Revision history

WikiAdmin: 1 revision imported

Chanson: /* Standard Import Format / Separate Debit Credit */