Assetsmanagement/Advanced Asset Management/Reports

Contents 1 TECHNICAL DOCUMENTATION: Asset Reporting 1.1 Purpose of the Project 1.2 Module information 1.3 Reports 1.3.1 Application Dictionary 1.3.2 Jasper Report Template 1.3.2.1 SQL Query 1.3.3 Triggers 1.3.4 DB functions and procedures 1.3.5 Sequences 1.3.6 Views

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

• 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

• 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

• 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