View source | View content page | Page history | Printable version   

Projects:Assetsmanagement/Advanced Asset Management/Database

Contents

Added artifacts to core tables

Table Column Type Nullable Default
AD_Client_Info EM_AAM_IsAutoInvNo char(1) No 'Y'
AD_Client_Info EM_AAM_InventoryLimit number Yes


New Tables

AAM_ASSETCARD

Column Type Nullable Default Keys
AAM_Asset_Card_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy Char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
Name char(60) No
Description char(255) Yes
AssetNo char(60) Yes
InventoryNo cher(60) Yes

AAM_ASSET

Column Type Nullable Default Keys
AAM_Asset_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Asset_Card_ID char(32) No Foreign key to AAM_Asset_Card.AAM_Asset_Card_ID
AAM_Asset_Class_ID char(32) No Foreign key to AAM_Asset_Class.AAM_Asset_Class_ID
C_Currency_ID char(32) No Foreign key to C_Currency.C_Currency_ID
Name char(60) No
AssetNo char(60) No
UseLifeYears number No
AmortizationValueAmt number No
DateCancelled date Yes
ResidualAssetValueAmt number Yes
AnnualAmortizationPercentage number Yes
DatePurchased date No
AmortizationStartDate date No
AssetValueAmt number No
DepreciatedValue number No
DepreciatedPlan number No
AmortizationEndDate date Yes
DepreciatedPreviousAmt number Yes
IsFullyDepreciated char(1) No
UseLifeMonths number No
Processed char(1) No 'N'
ParentView char(60) No 'N'
IsSummary char(1) No 'N'
MainAsset char(32) Yes Foreign Key to AAM_Asset.AAM_Asset_ID
IsDepreciationRangeChanged char(1) 'N' No
HasSubsidy char(1) No 'N'
CommissioningDate date Yes
OrganizationUnit char(32) Yes Foreign Key to AD_Org.AD_Org_ID
OrgUnitRateOwner char(32) Yes Foreign Key to AD_Org.AD_Org_ID
LastDepreciationDate date Yes
CurrentTotalAmount number Yes


AAM_INVENTORY

Column Type Nullable Default Keys
AAM_Inventory_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_AssetCard_ID char(32) No Foreign key to AAM_AssetCard.AAM_AssetCard_ID
InventoryNo char(60) No
Name char(60) No
IsPrinted char(1) No 'N'
InventoryDate date No
Status char(60) No
LocationBuilding char(60) Yes
LocationFloor char(60) Yes
LocationRoom char(60) Yes
Factory char(60) Yes
StockTakingDate1 date Yes
StockTakingDate2 date Yes
Labels number Yes
IsElectric char(1) No
Inspector char(60) Yes
CheckDate1 date Yes
CheckDate2 date Yes
GuaranteeBegin date Yes
GuaranteePeriod char(60) Yes
GuaranteeEnd date Yes
GuaranteeWarrantyBegin date Yes
GuaranteeWarrantyPeriod number Yes
GuaranteeWarantyEnd date Yes
MaintenanceContract char(60) Yes
MaintenanceBegin date Yes
MaintenanceInterval char(60) Yes
MaintenancePeriodicalCost char(60) Yes
InsuredAmt number Yes
PolicyNo char(60) Yes
LeasingEnd date Yes
Processed char(1) No


AAM_CONTROLLING

Column Type Nullable Default Keys
AAM_Controlling_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Asset_ID char(32) No Foreign Key to AAM_Asset.AAM_Asset_ID
AAM_AssetClass_ID char(32) No Foreign Key to AAM_AssetClass.AAM_AssetClass_ID
Name char(60) No
IsSummary char(1) No 'N'
AAM_ParentAsset_ID char(32) Yes Foreign key to AAM_Asset.AAM_Asset_ID
CurrentValue number No
DatePurchased date Yes
ComissioningDate date Yes
AmoritzationValueAmt number Yes
UseLifeMonths number Yes
UseLifeYears number Yes
DepreciationMethod char(60) Yes
DepreciationType char(60) Yes
DepreciationAccount_ID char(32) Yes Foreign Key to C_ElementValue.C_ElementValue_ID
Processed char(1) No

AAM_ASSET_ACCT

Column Type Nullable Default Keys
AAM_ASSET_ACCT_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Asset_ID char(32) No Foreign Key to AAM_Asset.AAM_Asset_ID
Name char(60) No
AssetAccount_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
AdjustmentAccount_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
AccumulatedAdjustments_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
CostFromAssetDisposal_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
RevenueFromAssetDisposal_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID


AAM_ASSETSUBSIDY

Column Type Nullable Default Keys
AAM_AssetSubsidy_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Subsidy_ID char(32) No Foreign Key to AAM_Subsidy.AAM_Subsidy_ID
AAM_Asset_ID char(32) No Foreign Key to AAM_Asset.AAM_Asset_ID
SubsidyDate date No
SubsidyEntity char(60) No
SubsidyPecentage number Yes
SubsidyAmt number Yes
DepreciationMethod char(60) No
DepreciationType char(60) No
DepreciationInterval char(60) No
CurrentValue number No
SubsidyAccount char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
AdjustmentAccount char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID


AAM_AMORTIZATION

Column Type Nullable Default Keys
AAM_Amortization_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Asset_ID char(32) No Foreign Key to AAM_Asset.AAM_Asset_ID
C_Currency_ID char(32) No Foreign Key to C_Currency.C_Currency_ID
Line number No
SeqNo number No
Percentage number No
Amount number No


AAM_ASSETCLASS

Column Type Nullable Default Keys
AAM_AssetClass_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_DepreciationMethod_ID char(32) No Foreign Key to AAM_DepreciationMethod.AAM_DepreciationMethod_ID
AAM_DepreciationType_ID char(32) No Foreign Key to AAM_DepreciationType.AAM_DepreciationType_ID
AAM_DepreciationTimming_ID char(32) No Foreign Key to AAM_DepreciationTimming_ID
Name char(60) No
Description char(255) Yes
Help char(255) Yes



AAM_ASSETCLASS_ACCT

Column Type Nullable Default Keys
AAM_AssetClass_Acct_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_AssetClass_ID char(32) No Foreign Key to AAM_AssetClass.AAM_AssetClass_ID
AssetAccount_ID char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
AdjustmentAccount char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
AccumulatedAdjustments char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
CostFromAssetDisposal char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID
RevenueFromAssetDisposal char(32) No Foreign Key to C_ElementValue.C_ElementValue_ID


AAM_DEPRECIATIONMETHOD

Column Type Nullable Default Keys
AAM_DepreciationMethod_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
Name char(60) No
Description char(255) Yes
Help char(255) Yes
JavaClass char(255) No


AAM_DEPRECIATIONTYPE

Column Type Nullable Default Keys
AAM_DepreciationType_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_DepreciationMethod_ID char(32) No Foreign Key to AAM_DepreciationMethod.AAM_DepreciationMethod_ID
Name char(60) No
Description char(255) Yes
Help char(255) Yes



AAM_DEPRECIATIONTIMING

Column Type Nullable Default Keys
AAM_DepreciationTiming_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_DepreciationType_ID char(32) No Foreign Key to AAM_DepreciationType.AAM_DepreciationType_ID
Name char(60) No
Description char(255) Yes
Help char(255) Yes


AAM_SUBSIDY

Column Type Nullable Default Keys
AAM_Subsidy_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
SubsidyEntity char(255) No
SubsidyDate date No
SubsidyAmt number No


AAM_TODOLIST

Column Type Nullable Default Keys
AAM_ToDoList_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
C_InvoiceLine_ID char(32) No Foreign Key to C_InvoiceLine.C_InvoiceLine_ID
C_BPartner_ID char(32) No Foreign Key to C_BPartner.C_BPartner_ID
C_GLItem_ID char(32) No Foreign Key to C_GLItem.C_GLItem_ID
AAM_AssetCard_ID char(32) Yes Foreign Key to AAM_AssetCard.AAM_AssetCard_ID
InvoiceNo char(30) No
Line number No
Processed char(1) No 'N'
InvoiceDate date Yes
BookingDate date Yes
Purpose char(60) No
InventoryNo char(60) Yes
HasAssetCard char(1) No 'Y'
IsVerified char(1) No 'N'
IsSales char(1) No 'N'



AAM_SEQUENCE

Column Type Nullable Default Keys
AAM_Sequence_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
Name char(60) No
Description char(255) Yes
Type char(60) No


AAM_SEQUENCELINE

Column Type Nullable Default Keys
AAM_SequenceLine_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_Sequence_ID char(32) No Foreign Key to AAM_Sequence.AAM_Sequence_ID
AD_Sequence_ID char(32) Yes Foreign Key to AD_Sequence.AD_Sequence_ID
Type char(60) No
LineNo number No
Content char(60) No
TextField char(60) Yes


AAM_INVOICELINE_ASSETCARD

Column Type Nullable Default Keys
AAM_InvoiceLineAssetCard_ID char(32) No Primary Key
AD_Client_ID char(32) No Foreign key to AD_Client.AD_Client_ID
AD_Org_ID char(32) No Foreign key to AD_Org.AD_Org_ID
Created date No SysDate()
CreatedBy char(32) No Foreign Key to AD_User.AD_User_ID
Updated date No SysDate()
UpdatedBy char(32) No Foreign Key to AD_User.AD_User_ID
IsActive char(1) No 'Y'
AAM_AssetCard_ID char(32) No Foreign Key to AAM_AssetCard.AAM_AssetCard_ID
C_InvoiceLine_ID char(32) No Foreign Key to C_InvoiceLine.C_InvoiceLine_ID


* As usual: 
1) char(1) fields will have a constraint so it just allows 'Y' and 'N' values.

New Views

AAM_BOOKING_V

SELECT A.AD_CLIENT_ID, A.AD_ORG_ID, A.CREATED, A.CREATEDBY, A.UPDATED, A.UPDATEDBY, A.ISACTIVE,
  O.AMOUNT, O.DOCUMENTNO, I.DATEACCT, I.DATEINVOICED
FROM AAM_ASSETCARD A, OBBO_BOOKING B, C_INVOICELINE L, C_INVOICE I, AAM_INVOICELINE_ASSETCARD AI
WHERE AI.C_INVOICELINE_ID = L.C_INVOICELINE_ID
 AND I.C_INVOICE_ID = L.C_INVOICE_ID
 AND L.C_INVOICE_ID = B.RECORD_ID
 AND AI.ASSETCARD_ID = A.ASSETCARD_ID


New Triggers

AAM_Asset_TRG

AAM Asset TRG.jpg


AAM_INVENTORY_TRG

AAM INVENTORY TRG.jpg


AAM_ASSETSUBSIDY_TRG

AAM ASSETSUBSIDY TRG.jpg

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

This page has been accessed 2,615 times. This page was last modified on 8 June 2012, at 05:26. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.