Projects:Assetsmanagement/Advanced Asset Management/Database
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_INVENTORY_TRG