Assetsmanagement/Advanced Asset Management/Reports
Contents |
TECHNICAL DOCUMENTATION: Asset Reporting
Purpose of the Project
End user (Asset Management Team) can have different reports to understand the value of assets or ownership of assets with filtered by Client/Organization, Report Date and document type like Depreciation, Manual Depreciation, Special Depreciation, Storno Depreciation or Appreciation.
Module information
- Name: Reports for Advanced Asset Management
- Type: Module
- Description: Collection of reports for advanced assets management
- Help: This module provides the list of reports for advanced assets management module.
- Translation Required: Yes
- Module Language: English (USA)
- Is Commercial Module: Yes
- License: Openbravo Commercial License
- Project's homepage: http://forge.openbravo.com/projects/assetsmanagementreports
- Java Package Name: org.openbravo.assetsmanagement.reports
- DB Prefix: AAMREP
- Dependencies: Advanced Assets Management
Reports
1) Asset Report based on Asset Special Document Type It is filtered by Client/Organization and after the end-user has entered a Report Date; end-user could also filter by Document Type (Depreciation, Manual Depreciation, Special Depreciation, Storno Depreciation or Appreciation) and/or by the Asset Owner.
2) Asset Report based on AP/AR document Type It is filtered by Client/Organization and after the end-user has entered a Report Date; end-user could also filter by document Type (AP/AR invoice) and/or by Asset Owner:
Application Dictionary
Following these steps you'll be able to find the report in the Application Dictionary:
As System Administrator go to: 1) Asset Report based on Depreciation(Summary level)
- Application Dictionary > Report and Process
- Use the filter and search for: Summary Asset Report
- Select the found record
You should be able to see the report definition:
- Search Key: AAMREP_JR_AssetReport
- Name: Summary Asset Report
- Description: Asset Report filtered by asset
- Help/Comment: Asset Report filtered by asset
- Active: Yes
- Data Access Level: Client/Organization
- Data Access Level defines who has access to this report.
- UI Pattern: Standard
- Standard or Manual. A Standard process is used for WAD generated pop-ups which pop-ups will ask for the report parameters. A Manual UI should be handled by the developer. No window will be automaticly generated for this.
- Report: No
- Jasper Report: Yes
![]() | Notice that when you register a JasperReport based report, you don't select the Report check-box, but the Jasper Report one. These reports are handled as processes |
- JR Template name: @basedesign@/org/openbravo/erpCommon/ad_reports/AAMREP_SummaryAssetReport.jrxml
- Defines where is located the JRXML template. @basedesign@ is evaluated at run-time, and translated to something like: your_tomcat_context/src-loc/design
2) Asset Report based on AP/AR Document Type (Detailed level)
- Application Dictionary > Report and Process
- Use the filter and search for: Asset Report For AP/AR
- Select the found record
You should be able to see the report definition:
- Search Key: AAMREP_JR_ReportAPARDocType
- Name: AP/AR Doc Type Asset Report
- Description: Asset Report filtered by AP/AR Document Type
- Help/Comment: Asset Report filtered by AP/AR Document Type
- Active: Yes
- Data Access Level: Client/Organization
- Data Access Level defines who has access to this report.
- UI Pattern: Standard
- Standard or Manual. A Standard process is used for WAD generated pop-ups which pop-ups will ask for the report parameters. A Manual UI should be handled by the developer. No window will be automaticly generated for this.
- Report: No
- Jasper Report: Yes
![]() | Notice that when you register a JasperReport based report, you don't select the Report check-box, but the Jasper Report one. These reports are handled as processes |
- JR Template name: @basedesign@/org/openbravo/erpCommon/ad_reports/AAMREP_APARDocTypeAssetReport.jrxml
- Defines where is located the JRXML template. @basedesign@ is evaluated at run-time, and translated to something like: your_tomcat_context/src-loc/design
3) Asset Report based on Asset Special Document Type (Detailed level)
- Application Dictionary > Report and Process
- Use the filter and search for: Asset Report For Special Documents
- Select the found record
You should be able to see the report definition:
- Search Key: AAMREP_JR_ReportSpecialDocType
- Name: Special Doc Type Asset Report
- Description: Asset Report filtered by Special Document Type
- Help/Comment: Asset Report filtered by Special Document Type
- Active: Yes
- Data Access Level: Client/Organization
- Data Access Level defines who has access to this report.
- UI Pattern: Standard
- Standard or Manual. A Standard process is used for WAD generated pop-ups which pop-ups will ask for the report parameters. A Manual UI should be handled by the developer. No window will be automaticly generated for this.
- Report: No
- Jasper Report: Yes
![]() | Notice that when you register a JasperReport based report, you don't select the Report check-box, but the Jasper Report one. These reports are handled as processes |
- JR Template name: @basedesign@/org/openbravo/erpCommon/ad_reports/AAMREP_SpecialDocTypeAssetReport.jrxml
- Defines where is located the JRXML template. @basedesign@ is evaluated at run-time, and translated to something like: your_tomcat_context/src-loc/design
Jasper Report Template
SQL Query
1.1.1 to get summary level data for assets report
SELECT AST.ad_client_id, AST.ad_org_id, AST.purchaseddate, AST.name, AST.assetvalueamt, INV.OWNER, inv.inventoryno, inv.inventorydate, inv.labels, (SELECT sum(AMOTLN.amortizationamt) FROM aam_amortizationline AMOTLN LEFT OUTER JOIN aam_amortization AMOT ON AMOT.aam_amortization_id = AMOTLN.aam_amortization_id WHERE AMOTLN.aam_asset_id = ast.aam_asset_id AND amot.enddate<=to_date('21/04/11', 'DD/MM/YY')GROUP BY AMOTLN.aam_asset_id) AS Adjustments, (SELECT sum(AMOTLN.amortizationamt) FROM aam_amortizationline AMOTLN LEFT OUTER JOIN aam_amortization AMOT ON AMOT.aam_amortization_id = AMOTLN.aam_amortization_id WHERE AMOTLN.aam_asset_id = ast.aam_asset_id AND amot.enddate <= TO_DATE('31-12-' || TO_CHAR(TO_NUMBER(TO_CHAR(now(), 'YYYY'))-1), 'DD-MM-YYYY') GROUP BY AMOTLN.aam_asset_id) AS netValuePreviousYear, (SELECT sum(AMOTLN.amortizationamt) FROM aam_amortizationline AMOTLN LEFT OUTER JOIN aam_amortization AMOT ON AMOT.aam_amortization_id = AMOTLN.aam_amortization_id WHERE AMOTLN.aam_asset_id = ast.aam_asset_id AND TO_DATE( TO_CHAR(amot.enddate,'MM-YYYY'),'MM-YYYY') <= TO_DATE( TO_CHAR(TO_NUMBER(TO_CHAR(now(), 'MM'))-1,'00')||TO_CHAR(now(), 'YYYY'), 'MM-YYYY') GROUP BY AMOTLN.aam_asset_id) AS netValuePreviousMonth, (SELECT sum(AMOTLN.amortizationamt) FROM aam_amortizationline AMOTLN LEFT OUTER JOIN aam_amortization AMOT ON AMOT.aam_amortization_id = AMOTLN.aam_amortization_id WHERE AMOTLN.aam_asset_id = ast.aam_asset_id AND amot.enddate <= TO_DATE('31-12-' || TO_CHAR(now(), 'YYYY'), 'DD-MM-YYYY') GROUP BY AMOTLN.aam_asset_id) AS netValueCurrentYear, (SELECT sum(AMOTLN.amortizationamt) FROM aam_amortizationline AMOTLN LEFT OUTER JOIN aam_amortization AMOT ON AMOT.aam_amortization_id = AMOTLN.aam_amortization_id WHERE AMOTLN.aam_asset_id = ast.aam_asset_id AND TO_DATE(TO_CHAR(amot.enddate,'MM-YYYY'),'MM-YYYY') <= TO_DATE(TO_CHAR(now(), 'MM-YYYY'), 'MM-YYYY') GROUP BY AMOTLN.aam_asset_id) AS netValueCurrentMonth FROM aam_asset AST LEFT OUTER JOIN aam_asset_card CRD ON AST.AAM_ASSET_CARD_ID = CRD.AAM_ASSET_CARD_ID LEFT OUTER JOIN aam_inventory INV ON CRD.AAM_ASSET_CARD_ID = inv.AAM_ASSET_CARD_ID WHERE AST.ad_client_id='1000000' AND AST.ad_org_id='1000000'
- hard-code date(21/11/10) is going to come from end-user as Report Date
- client id and org id are parameters(hard code has to be removed)
1.1.2 to get detailed level data for assets report
SELECT ASTINVO.AD_CLIENT_ID, ASTINVO.AD_ORG_ID,INVO.DATEINVOICED, invln.linenetamt, INVO.documentno AS InvoiceNumber, invo.docstatus, invln.account_id AS AssetAccount, doct.name,doct.docbasetype FROM aam_invoiceline_assetcard ASTINVO LEFT OUTER JOIN aam_asset_card CRD ON ASTINVO.AAM_ASSET_CARD_ID = CRD.AAM_ASSET_CARD_ID LEFT OUTER JOIN AAM_ASSET AST ON CRD.AAM_ASSET_CARD_ID = AST.AAM_ASSET_CARD_ID LEFT OUTER JOIN aam_inventory INV ON CRD.AAM_ASSET_CARD_ID = inv.AAM_ASSET_CARD_ID LEFT OUTER JOIN c_invoiceline INVLN ON ASTINVO.C_INVOICELINE_ID = INVLN.C_INVOICELINE_ID LEFT OUTER JOIN c_invoice INVO ON INVLN.C_INVOICE_ID = INVO.C_INVOICE_ID LEFT OUTER JOIN c_doctype DOCT ON INVO.c_doctypetarget_id = DOCT.c_doctype_id WHERE ASTINVO.ad_client_id='1000000' AND ASTINVO.ad_org_id='1000000' AND CRD.AAM_ASSET_CARD_ID = 'ED4963B25E8D438D89695C638CA315DE'
- client id, org id and card id are parameters(hard code has to be removed)
1.1.2 Asset Movements report
SELECT SD.AD_CLIENT_ID, SD.AD_ORG_ID, AST.name, sd.dateacct, SD.AMOUNT, SD.DOCSTATUS, doct.name, DOCT.docbasetype FROM AAMSD_SPECIALDOCUMENTS SD LEFT OUTER JOIN c_doctype DOCT ON SD.c_doctype_id = DOCT.c_doctype_id LEFT OUTER JOIN aam_asset_card CRD ON SD.AAM_ASSET_CARD_ID = CRD.AAM_ASSET_CARD_ID LEFT OUTER JOIN AAM_ASSET AST ON CRD.AAM_ASSET_CARD_ID = AST.AAM_ASSET_CARD_ID LEFT OUTER JOIN aam_inventory INV ON CRD.AAM_ASSET_CARD_ID = inv.AAM_ASSET_CARD_ID WHERE SD.ad_client_id='1000000' AND SD.ad_org_id='1000000'
- client id, org id are parameters(hard code has to be removed)
Triggers
No triggers required
DB functions and procedures
No required
Sequences
No required
Views
No required