http://in.compucrete.com/index.php?action=history&feed=atom&title=DataScope_G%2FL_Finanacial_Statements 2026-05-15T15:29:14Z Revision history for this page on the wiki MediaWiki 1.40.0 http://in.compucrete.com/index.php?title=DataScope_G/L_Finanacial_Statements&diff=108&oldid=prev 2023-07-14T12:58:48Z

<p>1 revision imported</p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 12:58, 14 July 2023</td> </tr> <!-- diff cache key my_wiki:diff::1.12:old-107:rev-108 --> </table>

WikiAdmin http://in.compucrete.com/index.php?title=DataScope_G/L_Finanacial_Statements&diff=107&oldid=prev 2017-01-10T00:01:30Z

<p>1 revision imported</p> <p><b>New page</b></p><div>==Data Setup==<br /> The Keys to<br /> <br /> * Assign all Accounts to meaningful groups. For example:<br /> **EXPCOGS<br /> **EXPGA<br /> **ASTCUR<br /> **ASTFIX<br /> *Assign all groups Heading group to top level Groups:<br /> **INC: Income<br /> **EXP: Expenses<br /> **AST: Assets<br /> *Setup Sequence Numbers for all Groups. For Example<br /> ** AST: 100<br /> ** ASTCUR: 110<br /> ** ASTFIX: 150<br /> ** INCSALES: 410<br /> <br /> <br /> ==Query==<br /> ===Income Statement Only:===<br /> &lt;PRE&gt;<br /> select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||' '||A.Description Account,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq,a.gl_group,<br /> t.trx_date,-t.trx_amount trx_amount,t.source_app,description_1, description_2,a.gl_account account,a.description,session_no,trans_no,a.segment_1,a.segment_2,Trx_Date Mo,trx_date yr, trx_date qtr from glttrx t<br /> inner join gltacct a on a.gl_account=t.gl_account <br /> left outer join gltacgrp g on g.group_id = a.gl_group <br /> where trx_date between :beg_date and :end_date and trans_type not in ('B','R') and a.account_type='I' <br /> order by g.sequence&lt;/PRE&gt;<br /> <br /> [[Image:Ds gl fs1.png]]<br /> <br /> <br /> ===Income Statement and Balance Sheet:===<br /> &lt;PRE&gt;<br /> select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||' '||A.Description Account_name,t.trx_date yr,t.trx_date mo,t.trx_date,-t.trx_amount trx_amount,t.source_app,session_no,trans_no,description_1, description_2,a.gl_account account,a.description ,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq<br /> ,a.segment_1,a.segment_2,A.ACCOUNT_TYPE IB,a.NOTES_1||' '||a.notes_2 &quot;GL Account&quot; from glttrx t<br /> inner join gltacct a on a.gl_account=t.gl_account <br /> left outer join gltacgrp g on g.group_id = a.gl_group<br /> where (trx_date between :beg_date and :end_date and trans_type not in ('B','R')) and gl_account not between 'Y' AND 'Z'<br /> union<br /> select g.sequence||' '||g.group_id||' '||g.line_description Group_Name,t.gl_account||' '||A.Description Account,<br /> cast ('1/1/2000' as date),cast ('1/1/2000' as date),trx_date,-t.trx_amount,t.source_app,session_no,trans_no,description_1, description_2,a.gl_account account,a.description<br /> ,G.PARENT_GROUP_ID PARGRP,(select sequence from gltacgrp gp where gp.group_id=g.parent_group_id) pgseq<br /> ,a.segment_1,a.segment_2,A.ACCOUNT_TYPE,NOTES_1||' '||notes_2 from glttrx t<br /> inner join gltacct a on a.gl_account=t.gl_account <br /> left outer join gltacgrp g on g.group_id = a.gl_group<br /> where (trx_date &lt; :beg_date and trans_type not in ('B') and a.account_type='B')<br /> &lt;/PRE&gt;<br /> <br /> <br /> [[Image:Ds gl fs2.png]]<br /> <br /> <br /> ===Income Statement with separate drilldown query:&lt;BR&gt;&lt;BR&gt;===<br /> &lt;I&gt;Main Query&lt;/I&gt;<br /> &lt;PRE&gt;<br /> select <br /> gp.sequence pgseq,<br /> G.PARENT_GROUP_ID PARGRP,<br /> g.sequence, g.group_id, g.line_description,<br /> t.gl_account, A.Description,<br /> t.trx_date,<br /> t.Trx_Date Mo,t.trx_date yr, t.trx_date qtr,<br /> sum(-t.trx_amount) trx_amount,<br /> sum(-t.trx_amount) pct_comp<br /> from glttrx t<br /> inner join gltacct a on a.gl_account=t.gl_account <br /> left outer join gltacgrp g on g.group_id = a.gl_group <br /> left outer join gltacgrp gp on gp.group_id = g.parent_group_id<br /> where (t.trx_date between :beg_date and :end_date) and <br /> (t.trans_type not in ('B','R')) and<br /> (a.account_type='I') and<br /> (t.gl_account not between 'Y' AND 'Z')<br /> group by gp.sequence, g.parent_group_id, g.sequence, g.group_id, g.line_description,<br /> t.gl_account, a.description, t.trx_date<br /> &lt;/PRE&gt;<br /> <br /> <br /> [[Image:Ds gl fs3a.png]]<br /> <br /> <br /> &lt;I&gt;Drilldown Query&lt;/I&gt;<br /> &lt;PRE&gt;<br /> select<br /> a.gl_group,<br /> t.trx_date, t.trx_amount, t.source_app,<br /> t.description_1, t.description_2,<br /> t.gl_account, a.description,<br /> t.session_no, t.trans_no,<br /> a.segment_1, a.segment_2<br /> from glttrx t<br /> inner join gltacct a on a.gl_account=t.gl_account <br /> left outer join gltacgrp g on g.group_id = a.gl_group <br /> left outer join gltacgrp gp on gp.group_id = g.parent_group_id<br /> where (t.trx_date between :beg_date and :end_date) and <br /> (t.trans_type not in ('B','R')) and<br /> (a.account_type='I') and<br /> (t.gl_account not between 'Y' AND 'Z') and<br /> [WHERE pgseq=gp.sequence]<br /> [WHERE pargrp=g.parent_group_id]<br /> [WHERE sequence=g.sequence]<br /> [WHERE group_id=g.group_id]<br /> [WHERE line_description=g.line_description]<br /> [WHERE gl_account=t.gl_account]<br /> [WHERE description=a.description]<br /> [WHERE yr=extract(year from t.trx_date)]<br /> [WHERE mo=extract(month from t.trx_date)]<br /> &lt;/PRE&gt;<br /> <br /> <br /> [[Image:Ds gl fs3b.png]]</div>

Intra>User