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
AAM_ASSETSUBSIDY_TRG
