Projects:Asset Report Depreciation Schedule/Technical Specifications
TECHNICAL SPECIFICATION OF ASSET REPORT FOR DEPRECIATION SCHEDULE
PURPOSE OF THE PROJECT
The Depreciation Schedule Fixed Assets report is a new report which will show the depreciation schedule for a specific fixed asset or a specific fixed asset category.
INTRODUCTION
This technical specifications document outlines the implementation of asset report for Depreciation Schedule.
TECHNICAL IMPLEMENTATION :
REQUIRED FILES
The needed files for our "Asset Report for Depreciation Schedule" development
- Controller:
- openbravo/erpCommon/ad_reports/ReportAssetDepreciationSchedule.java
- Model:
- openbravo/erpCommon/ad_reports/ReportAssetDepreciationSchedule_data.xsql
- View: Template for the filter/result page
- openbravo/erpCommon/ad_reports/ReportAssetDepreciationSchedule.xml
- openbravo/erpCommon/ad_reports/ReportAssetDepreciationSchedule.html
- openbravo/erpCommon/ad_reports/ReportAssetDepreciationSchedule.jrxml
Report Creation in Application Dictionary
- Login Openbravo as System Administrator user.
- Go to Application Dictionary || Report and Process || Report & Process
- Create 1 new Report & Process
- Search Key = Asset_DepreciationSchedule
- Name = Assets
- Description = Report for Asset Depreciation Schedule
- Help/Comment = Report for Asset Depreciation Schedule
- Active = Yes
- Data Access Level = Client/Organization
- UI Pattern = Manual
- Save
- Go To Tab Application Dictionary || Report and Process || Report & Process >> Process Class
- Create 1 new Process Class
- Default = Yes
- Java Class Name = org.openbravo.erpCommon.ad_reports.ReportAssetDepreciationSchedule
- Go To Application Dictionary || Report and Process || Report & Process >> Process Class >> Process Mapping
- Create 2 new Process Mapping
- Mapping Name = /ad_reports/ReportAssetDepreciationSchedule.html
- Default = Yes
- Save
- Mapping Name = /ad_reports/ReportAssetDepreciationSchedule.jrxml
- Default = No
- Save
Menu Creation in Application Dictionary
- Login Openbravo ERP as System Administrator.
- Goto General Setup || Application || Menu || Menu
- Create a New Record "Menu"
- Name = Depreciation Schedule
- Description = Depreciation Schedule
- Active = Yes
- Action = "Report"
- Process = Depreciation Schedule
- Save
- Click "Tree" icon in the tool bar (Pop up window will open).
- If exist “Analysis Tools” menu on [Financial Management >> Assets], Drag & Drop newly created menu (Depreciation Schedule report) on the appropriated place [Financial Management >> Assets >> Analysis Tools].
- Otherwise Create new menu
- Name = Analysis Tools
- Description = Open a folder where you can find reports and tools to analyze data.
- Active = Yes
- Summary Level = Yes
- Save
- Click "Tree" icon in the tool bar (Pop up window will open).
- Drag & Drop newly created menu (Analysis Tools) on the appropriated place [Financial Management >> Assets].
- Next Drag & Drop newly created menu (Depreciation Schedule report) on the appropriated place [Financial Management >> Assets >> Analysis Tools].
Controller:JAVA file
The controller java file should have the appropriate methods for generating the appropriate output format. The Asset report for Depreciate Schedule has different output formats.
1. Search (HTML). 2. PDF.
Controller servlet should have two methods as printPageDataSheet() and printPageDataPdf() to generate the html output and pdf output respectively which is called from "doPost" based on user selection. The Asset Report is initially loaded by clicking the "Depreciation Schedule" menu element, and the "DEFAULT" block inside the java controller will be executed. If the user clicks on the "Search" Button, the "FIND" method will be executed and result will be displayed on the bottom of the page in html format. If the user clicks on the "PDF Format" button, the result of the search will be displayed in pdf format in pop up window.
Java condition blocks
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
VariablesSecureApp vars = new VariablesSecureApp(request); if (vars.commandIn("DEFAULT")) { } else if (vars.commandIn("FIND")) { } else if (vars.commandIn("PRINT_PDF")) { } else pageError(response); }
USER INTERFACE IMPLEMENTATION:
[Financial Management || Assets || Analysis Tools || Asset || Asset ]
- The "Depreciation Schedule" report have seven filter criteria.
- From Date
- A_AMORTIZATION Table
- STARTDATE
- A_AMORTIZATION Table
- To Date
- A_AMORTIZATION Table
- ENDDATE
- A_AMORTIZATION Table
- Asset Search Key
- A_ASSET Table
- Value character varying(40)
- A_ASSET Table
- Asset Description
- A_ASSET Table
- Description character varying(255)
- A_ASSET Table
- Asset Category
- A_ASSET_GROUP Table
- A_Asset_Group_ID character varying(32)
- Name
- A_ASSET_GROUP Table
- Accounting Schema
- A_ACCTSCHEMA Table
- Organization (mandatory)
- AD_ORG Table
- AD_Org_ID character varying(32)
- Name character varying(60)
- AD_ORG Table
- From Date
The result is display on html & PDF file based on the filter condition.
Model: Create XSQL file
Next step is the creation of the xsql file in order to retrieve the data that will be shown in the report.
- Column displayed in view result
- Fiscal Year
- C_YEAR Table
- YEAR
- C_YEAR Table
- From Date
- A_AMORTIZATION Table
- STARTDATE
- A_AMORTIZATION Table
- To Date
- A_AMORTIZATION Table
- ENDDATE
- A_AMORTIZATION Table
- Depreciation amount
- FACT_ACCT Table
- AMTACCTDR
- FACT_ACCT Table
- Currency
- C_CURRENCY Table
- ISO_CODE
- C_CURRENCY Table
- Fiscal Year
<SqlClass name="ReportAssetDepreciationScheduleData" package="org.openbravo.erpCommon.ad_reports"> <SqlClassComment>Report for asset depreciation schedule</SqlClassComment> <SqlMethod name="select" type="preparedStatement" return="multiple"> <SqlMethodComment>Method used for search(HTML) and PDF </SqlMethodComment> <Sql><![CDATA[ SELECT A.NAME AS ASSETNAME,A.DESCRIPTION AS ASSETDESCRIPTION,Y.YEAR AS FIELDFISCALYEAR,AM.STARTDATE AS STARTDATE, AM.ENDDATE AS ENDDATE,F.AMTACCTDR AS AMOUNT,C.ISO_CODE AS CURRENCY FROM A_ASSET_GROUP AG, A_ASSET A, A_AMORTIZATION AM,A_AMORTIZATIONLINE AML,FACT_ACCT F,C_CURRENCY C,C_PERIOD P,C_YEAR Y,C_ACCTSCHEMA S WHERE AG.A_ASSET_GROUP_ID = A.A_ASSET_GROUP_ID AND A.A_ASSET_ID=AML.A_ASSET_ID AND AM.A_AMORTIZATION_ID=AML.A_AMORTIZATION_ID AND AML.A_AMORTIZATION_ID=F.RECORD_ID AND F.A_ASSET_ID=A.A_ASSET_ID AND F.AMTACCTDR<>'0' AND C.C_CURRENCY_ID= F.C_CURRENCY_ID AND P.C_PERIOD_ID = F.C_PERIOD_ID AND P.C_YEAR_ID = Y.C_YEAR_ID AND S.C_ACCTSCHEMA_ID=F.C_ACCTSCHEMA_ID AND A.AD_CLIENT_ID = ? AND 1=1 AND A.AD_ORG_ID IN ('1') ORDER BY A.NAME, Y.YEAR, AM.STARTDATE, AM.ENDDATE]]></Sql> <Parameter name="client"></Parameter> <Parameter name="DateFrom" optional="true" after="AND 1=1"><![CDATA[ AND AM.STARTDATE >= TO_DATE(?)]]></Parameter> <Parameter name="DateTo" optional="true" after="AND 1=1"><![CDATA[ AND AM.ENDDATE <= TO_DATE(?)]]></Parameter> <Parameter name="assetSeaKey" ignoreValue="%" optional="true" after="AND 1=1"><![CDATA[ AND UPPER(A.VALUE) LIKE UPPER(?)]]></Parameter> <Parameter name="assetDescription" ignoreValue="%" optional="true" after="AND 1=1"><![CDATA[ AND UPPER(A.DESCRIPTION) LIKE UPPER(?)]]></Parameter> <Parameter name="cAssetCategoryId" optional="true" after="AND 1=1"><![CDATA[ AND AG.A_ASSET_GROUP_ID = ?]]></Parameter> <Parameter name="cAcctSchemaId" optional="true" after="AND 1=1"><![CDATA[ AND S.C_ACCTSCHEMA_ID = ?]]></Parameter> <Parameter name="Org" type="replace" optional="true" after="AND A.AD_ORG_ID IN (" text="'1'"/> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql></Sql> </SqlMethod>
Database Information
- List of the tables involved in the Asset Report
- ASSETS WINDOW
- A_ASSET
- A_ASSET_ACCT
- ASSETS WINDOW
- ASSET GROUP WINDOW
- A_ASSET_GROUP
- A_ASSET_GROUP_ACCT
- ASSET GROUP WINDOW
- FACT TABLE
- FACT_ACCT
- FACT TABLE
- FISCAL YEAR
- C_PERIOD
- C_YEAR
- FISCAL YEAR
- The asset depreciation schedule report implementation retrieve data required from accounting, which means the FACT_ACCT table and it is also based on asset and asset accounting schema data.
To test the Depreciation Schedule report
- Login Openbravo ERP as Openbravo Admin.
- Go to "Depreciation Schedule" report [Financial Management || Assets || Analysis Tools || Depreciation Schedule || Depreciation Schedule ] to test.
OPEN DISCUSSION ITEMS
The other report in system has other options as below.
Export to Excel. Printer Icon to print the report and so on.
what are all the options we need to have on "Asset Report for depreciation schedule" should have ?.