Difference between revisions of "ManPageW FinancialReport"
(→Contributions) |
|||
Line 11: | Line 11: | ||
<!-- END Note to editors: NOT MODIFY THIS CODE --> | <!-- END Note to editors: NOT MODIFY THIS CODE --> | ||
=Contributions= | =Contributions= | ||
+ | |||
+ | === Functional Description === | ||
+ | |||
+ | '''''To be filled, please complete it''''' | ||
+ | |||
+ | === Technical Description === | ||
+ | |||
+ | This reports are based on tables: | ||
+ | * PA_Report | ||
+ | ** PA_ReportColumnSet | ||
+ | *** PA_ReportColumn | ||
+ | ** PA_ReportLineSet | ||
+ | *** PA_ReportLine | ||
+ | **** PA_ReportSource | ||
+ | * C_AcctSchema | ||
+ | * C_Calendar | ||
+ | |||
+ | The report generator looks on table T_REPORT and invokes org.compiere.report.FinReport process to fill temporary table. | ||
+ | |||
+ | T_REPORT has only 21 columns (0 to 20) | ||
+ | |||
+ | ==== org.compiere.report.FinReport ==== | ||
+ | ===== prepare ===== | ||
+ | Fix parameters | ||
+ | |||
+ | Fix where clause based on parameters and hierarchy. | ||
+ | |||
+ | Create a list of accounting periods of tables C_Period and C_Year with fields C_Period_ID, Name, StartDate (first day of month), EndDate (last day of month), YearStartDate (first day of year) | ||
+ | |||
+ | Query: | ||
+ | <pre> | ||
+ | SELECT p.c_period_id, p.NAME, p.startdate, p.enddate, MIN (p1.startdate) | ||
+ | FROM c_period p INNER JOIN c_year y ON (p.c_year_id = y.c_year_id), | ||
+ | c_period p1 | ||
+ | WHERE y.c_calendar_id = ? | ||
+ | AND p.periodtype = 'S' | ||
+ | AND p1.c_year_id = y.c_year_id | ||
+ | AND p1.periodtype = 'S' | ||
+ | GROUP BY p.c_period_id, p.NAME, p.startdate, p.enddate | ||
+ | ORDER BY p.startdate | ||
+ | </pre> | ||
+ | |||
+ | If period field is left blank process takes the period corresponding to TODAY | ||
+ | |||
+ | ===== doIt ===== | ||
+ | This method insert in T_Report records for the instance of process AD_PInstance_ID, according to the defined ReportLineSet | ||
+ | |||
+ | Query: | ||
+ | <pre> | ||
+ | INSERT INTO t_report | ||
+ | (ad_pinstance_id, pa_reportline_id, record_id, fact_acct_id, | ||
+ | seqno, levelno, NAME, description) | ||
+ | SELECT ?, pa_reportline_id, 0, 0, seqno, 0, NAME, description | ||
+ | FROM pa_reportline | ||
+ | WHERE isactive = 'Y' AND pa_reportlineset_id = ? | ||
+ | </pre> | ||
+ | |||
+ | If "Update Balance" is seleted then the process FinBalance.updateBalance is executed, this process update existing Fact_Acct_Balance records from Fact_Acct, and then insert into Fact_Acct_Balance the non existing records in Fact_Acct. | ||
+ | |||
+ | For each line of report type SegmentValue (S) | ||
+ | |||
+ | For each column not calculation gets the select according with the AmountType of line, or of AmountType of column if line has that field blank. | ||
+ | |||
+ | First letter of Amount Type determines the query: | ||
+ | ; B : Balance - acctBalance(Account_ID,AmtAcctDr,AmtAcctCr) | ||
+ | ; C : Credit - AmtAcctCr | ||
+ | ; D : Debit - AmtAcctDr | ||
+ | ; Q : Qty - Qty | ||
+ | |||
+ | Then process define the work period, if it's relative subtract/add the relative value of current period for report. | ||
+ | |||
+ | Second letter of Amount Type determines the period: | ||
+ | ; P : Period - BETWEEN StartDate AND EndDate | ||
+ | ; Y : Year - BETWEEN YearStartDate AND EndDate | ||
+ | ; T : Total - <= EndDate | ||
+ | |||
+ | Example of a final query: | ||
+ | <pre> | ||
+ | SELECT SUM (acctbalance (account_id, amtacctdr, amtacctcr)) | ||
+ | FROM fact_acct_balance | ||
+ | WHERE dateacct BETWEEN DATE '2004-01-01' AND DATE '2004-05-31' | ||
+ | </pre> | ||
+ | Moreover to this query conditions are added according to hierarchy and PostingType. | ||
+ | |||
+ | Function acctBalance does the following: returns DB-CR, or returns CR-DB if account is of credit nature.<br> | ||
+ | If account is type N (Natural), then is credit with type is not A or E.<br> | ||
+ | |||
+ | With founded value it updates the columns COL_X of table T_Report. | ||
+ | |||
+ | If the report must list sources then it proceeds to insert details on T_Report.<br> | ||
+ | These lines on T_Report with Level = 1 (or -1 if must be reported before)<br> | ||
+ | Transactions are level = 2 (or -2 if must be reported before)<br> | ||
+ | |||
+ | Then process executes calculations over calculated columns. | ||
+ | |||
+ | === Reported Bugs === | ||
+ | Although this bug appears as closed and fixed, current code still has the bug.<br> | ||
+ | https://sourceforge.net/tracker/index.php?func=detail&aid=839094&group_id=29057&atid=573828 | ||
+ | |||
+ | === Enhancement Requests === | ||
+ | https://sourceforge.net/tracker/index.php?func=detail&aid=1306653&group_id=29057&atid=410215 | ||
+ | https://sourceforge.net/tracker/index.php?func=detail&aid=1557707&group_id=176962&atid=879335 | ||
+ | https://sourceforge.net/forum/forum.php?thread_id=1495363&forum_id=128080 |
Revision as of 10:43, 23 October 2006
Enjoy it, and help to fill it! But please, always respecting copyright.
Please write your contributions under the Contributions Section
Contents
Contributions
Functional Description
To be filled, please complete it
Technical Description
This reports are based on tables:
- PA_Report
- PA_ReportColumnSet
- PA_ReportColumn
- PA_ReportLineSet
- PA_ReportLine
- PA_ReportSource
- PA_ReportLine
- PA_ReportColumnSet
- C_AcctSchema
- C_Calendar
The report generator looks on table T_REPORT and invokes org.compiere.report.FinReport process to fill temporary table.
T_REPORT has only 21 columns (0 to 20)
org.compiere.report.FinReport
prepare
Fix parameters
Fix where clause based on parameters and hierarchy.
Create a list of accounting periods of tables C_Period and C_Year with fields C_Period_ID, Name, StartDate (first day of month), EndDate (last day of month), YearStartDate (first day of year)
Query:
SELECT p.c_period_id, p.NAME, p.startdate, p.enddate, MIN (p1.startdate) FROM c_period p INNER JOIN c_year y ON (p.c_year_id = y.c_year_id), c_period p1 WHERE y.c_calendar_id = ? AND p.periodtype = 'S' AND p1.c_year_id = y.c_year_id AND p1.periodtype = 'S' GROUP BY p.c_period_id, p.NAME, p.startdate, p.enddate ORDER BY p.startdate
If period field is left blank process takes the period corresponding to TODAY
doIt
This method insert in T_Report records for the instance of process AD_PInstance_ID, according to the defined ReportLineSet
Query:
INSERT INTO t_report (ad_pinstance_id, pa_reportline_id, record_id, fact_acct_id, seqno, levelno, NAME, description) SELECT ?, pa_reportline_id, 0, 0, seqno, 0, NAME, description FROM pa_reportline WHERE isactive = 'Y' AND pa_reportlineset_id = ?
If "Update Balance" is seleted then the process FinBalance.updateBalance is executed, this process update existing Fact_Acct_Balance records from Fact_Acct, and then insert into Fact_Acct_Balance the non existing records in Fact_Acct.
For each line of report type SegmentValue (S)
For each column not calculation gets the select according with the AmountType of line, or of AmountType of column if line has that field blank.
First letter of Amount Type determines the query:
- B
- Balance - acctBalance(Account_ID,AmtAcctDr,AmtAcctCr)
- C
- Credit - AmtAcctCr
- D
- Debit - AmtAcctDr
- Q
- Qty - Qty
Then process define the work period, if it's relative subtract/add the relative value of current period for report.
Second letter of Amount Type determines the period:
- P
- Period - BETWEEN StartDate AND EndDate
- Y
- Year - BETWEEN YearStartDate AND EndDate
- T
- Total - <= EndDate
Example of a final query:
SELECT SUM (acctbalance (account_id, amtacctdr, amtacctcr)) FROM fact_acct_balance WHERE dateacct BETWEEN DATE '2004-01-01' AND DATE '2004-05-31'
Moreover to this query conditions are added according to hierarchy and PostingType.
Function acctBalance does the following: returns DB-CR, or returns CR-DB if account is of credit nature.
If account is type N (Natural), then is credit with type is not A or E.
With founded value it updates the columns COL_X of table T_Report.
If the report must list sources then it proceeds to insert details on T_Report.
These lines on T_Report with Level = 1 (or -1 if must be reported before)
Transactions are level = 2 (or -2 if must be reported before)
Then process executes calculations over calculated columns.
Reported Bugs
Although this bug appears as closed and fixed, current code still has the bug.
https://sourceforge.net/tracker/index.php?func=detail&aid=839094&group_id=29057&atid=573828
Enhancement Requests
https://sourceforge.net/tracker/index.php?func=detail&aid=1306653&group_id=29057&atid=410215 https://sourceforge.net/tracker/index.php?func=detail&aid=1557707&group_id=176962&atid=879335 https://sourceforge.net/forum/forum.php?thread_id=1495363&forum_id=128080