View source | Discuss this page | Page history | Printable version   

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

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)

You should be able to see the report definition:

Bulbgraph.png   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

2) Asset Report based on AP/AR Document Type (Detailed level)

You should be able to see the report definition:

Bulbgraph.png   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

3) Asset Report based on Asset Special Document Type (Detailed level)

You should be able to see the report definition:

Bulbgraph.png   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

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'

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'

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'

Triggers

No triggers required

DB functions and procedures

No required

Sequences

No required

Views

No required

Retrieved from "http://wiki.openbravo.com/wiki/Assetsmanagement/Advanced_Asset_Management/Reports"

This page has been accessed 2,816 times. This page was last modified on 20 September 2010, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.