{"action":"created","issue":{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/154","labels_url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/154/labels{/name}","comments_url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/154/comments","events_url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/154/events","html_url":"https://github.com/kumulsoft/Fixed-Assets/issues/154","id":114400489,"number":154,"title":"Changes to Import Utility","user":{"login":"mnarewec","id":10192106,"avatar_url":"https://avatars.githubusercontent.com/u/10192106?v=3","gravatar_id":"","url":"https://api.github.com/users/mnarewec","html_url":"https://github.com/mnarewec","followers_url":"https://api.github.com/users/mnarewec/followers","following_url":"https://api.github.com/users/mnarewec/following{/other_user}","gists_url":"https://api.github.com/users/mnarewec/gists{/gist_id}","starred_url":"https://api.github.com/users/mnarewec/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mnarewec/subscriptions","organizations_url":"https://api.github.com/users/mnarewec/orgs","repos_url":"https://api.github.com/users/mnarewec/repos","events_url":"https://api.github.com/users/mnarewec/events{/privacy}","received_events_url":"https://api.github.com/users/mnarewec/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/labels/Discussion","name":"Discussion","color":"bfe5bf"},{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/labels/enhancement","name":"enhancement","color":"bfe5bf"},{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/labels/NORMAL","name":"NORMAL","color":"006b75"}],"state":"open","locked":false,"assignee":{"login":"mnarewec","id":10192106,"avatar_url":"https://avatars.githubusercontent.com/u/10192106?v=3","gravatar_id":"","url":"https://api.github.com/users/mnarewec","html_url":"https://github.com/mnarewec","followers_url":"https://api.github.com/users/mnarewec/followers","following_url":"https://api.github.com/users/mnarewec/following{/other_user}","gists_url":"https://api.github.com/users/mnarewec/gists{/gist_id}","starred_url":"https://api.github.com/users/mnarewec/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mnarewec/subscriptions","organizations_url":"https://api.github.com/users/mnarewec/orgs","repos_url":"https://api.github.com/users/mnarewec/repos","events_url":"https://api.github.com/users/mnarewec/events{/privacy}","received_events_url":"https://api.github.com/users/mnarewec/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/milestones/11","html_url":"https://github.com/kumulsoft/Fixed-Assets/milestones/FAMS%202.0%20-%20Asset%20Administration","labels_url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/milestones/11/labels","id":1386452,"number":11,"title":"FAMS 2.0 - Asset Administration","description":"","creator":{"login":"mnarewec","id":10192106,"avatar_url":"https://avatars.githubusercontent.com/u/10192106?v=3","gravatar_id":"","url":"https://api.github.com/users/mnarewec","html_url":"https://github.com/mnarewec","followers_url":"https://api.github.com/users/mnarewec/followers","following_url":"https://api.github.com/users/mnarewec/following{/other_user}","gists_url":"https://api.github.com/users/mnarewec/gists{/gist_id}","starred_url":"https://api.github.com/users/mnarewec/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mnarewec/subscriptions","organizations_url":"https://api.github.com/users/mnarewec/orgs","repos_url":"https://api.github.com/users/mnarewec/repos","events_url":"https://api.github.com/users/mnarewec/events{/privacy}","received_events_url":"https://api.github.com/users/mnarewec/received_events","type":"User","site_admin":false},"open_issues":4,"closed_issues":5,"state":"open","created_at":"2015-10-31T01:47:38Z","updated_at":"2015-11-27T05:12:20Z","due_on":"2015-11-26T14:00:00Z","closed_at":null},"comments":3,"created_at":"2015-10-31T09:38:28Z","updated_at":"2015-12-04T13:28:38Z","closed_at":null,"body":"**Briefly:**\r\nNormally, an organisation will already have a ERP (Financial System) that will be in place. They will use that system to purchase assets or do maintenance or development on these assets.  When they purchase/maintain an item it must come out from a GL account. There are GL Accounts specially for assets which they should use but sometimes when monies are not in that account they use other accounts to buy or maintain assets. That is the reason why we will have to import all transactions and provide a feature/staging so user can decide which transactions they do not need to import.\r\n\r\nThe transactions (data) that we are going to import are actually generated AFTER a cheque ran (printing of cheques). These transactions are actually cheque transactions. A cheque contain one or more transaction lines. These transaction lines represent the GL Account and Section where the monies came out from to form the total cheque Amount. If monies came out from One GL Account then it will be one line for one cheque number. If monies came out from two different GL Accounts OR two different Sections then it will be two transaction lines for one cheque Number.\r\n\r\n**Asset Transactions:**\r\n1. One GL Account represents one Transaction line\r\n2. A cheque number can have multiple GL Accounts which equates to multiple transaction lines\r\n3. One transaction Line -  can mean  purchase (or maintenance,improvement or construction) of one asset or multiple assets and or including other non-assets\r\n4. An asset can be created on multiple transactions. Two cheques can be raised to purchase (or maintenance,improvement or construction)  of an asset. First it is created singly, then it is updated.\r\n\r\n**Raw Data or Data from the ERP System**\r\nYou already have the text file. For the benefit of readers, below is the excel image. The fields that are highlighted are to be imported.\r\n![image](https://cloud.githubusercontent.com/assets/10192106/10862907/af66823a-8007-11e5-8bfe-7329afad2bca.png)\r\n\r\n**Our requirements.**\r\nBasically to be able to import these transactions into the system and then base on these transactions to create new assets OR update existing asset data.\r\nHere are the steps : \r\n1. Import data as it is into a database table\r\n2. Bulk Update the Imported Transactions\r\n3. Detail Update the Imported Transactions (Split, Merge etc)\r\n4. Create Asset Transaction, Asset Registry, Asset Allocation records\r\n\r\n**STEP 1 - IMPORT DATA**\r\nImport data as it is into a database table MstAssetImport, so user can update the imported transactions.  Following are the fields of the table\r\n\r\n*STEP ONE Fields as per import file *\r\n(a). ImportId (Auto)\r\n(b). GL Nbr (Import file field: GE No)\r\n(c). Cheque Date (Import file field: GE Date) - will become the Transaction Date and or Acquired date for new asset.\r\n(s). Account Source ( part of Import file field:GE Account) - validate with existing Account Source\r\n(d). GL Account  Code( part of Import file field:GE Account) - validate with existing GL Code\r\n(e). Section  Code( part of Import file field:GE Account) - validate with existing section\r\n(f). Payee (Import file field: Payee) - will have to map to Vendor\r\n(g). Detail (Import file field: Detail) - This will form the Trans Detail\r\n(h). ChequeNumber (Import file field: Chq/Ref no)  - Cheque Number\r\n(i). Tran Amount (Import file field: Credits-Pay) - This is the Transaction Amount\r\n\r\n*STEP TWO fields that got to be updated before proceeding to next STEP 3**\r\n(j). Centre - field to be update\r\n(k). Asset Type - field to be updated\r\n(l). Vendor - Field to be updated base on Contacts Vendors\r\n(m). Transaction Type - Field to be update. Allowable values are (PURCHASE, MAINTENANCE, IMPROVEMENT, CONSTRUCTION,NON-ASSET)\r\n(n). Record (NEW, EXIST)\r\n\r\n*STEP THREE fields that got to be updated before proceeding to next STEP 4**\r\n(j). Asset Number - if Record= EXIST\r\n(o). Description - ONLY when Record=NEW\r\n(l). Asset Category - ONLY when Record=NEW\r\n(m). Asset Amount  - Required \r\n(n). Custodian (Staff) - ONLY when Record=NEW\r\n\r\n*System Fields*\r\n(o). ErrorStatus (0=No Error, 1=Error) - Marked error if validation/Import fail\r\n(P). Status (0=Imported, 1=Bulk Updated, 2=Detailed Updated, 3=Posted)\r\n(q). AssetTransId - updated when Asset Transaction is created\r\n(r). AssetId = updated when asset record created or updated.\r\n(s). User Id - user who updated/processed records.\r\n\r\n*Import Reference fields*\r\ns. Import Date\r\nt. Import Userid\r\nt. Import Reference - User to enter this when creating a new import.\r\n\r\n**STEP 2 - BULK UPDATE**\r\n(a). Filter records by GE Account, Payee, Amount (use Greater than or less than filter functionality)\r\n(b). Search Detail or Cheque Number, to filter records\r\n(c). Base on these filtered/search records i.e. a and b above, the user can do multiple selection and perform deletion of unwanted transactions.\r\n(d). Base on these filtered/search records i.e. a and b above, the user can do multiple selection and perform updates of for the following fields  Centre, Asset Type, Vendor, Transaction Type, Asset Record - all these are mandatory for a asset record to be created or updated. \r\n\r\n**UI Expection**\r\nI ideally, we want a feature when user select multiple transactions and click update, it will show fields Centre, Asset Type,Vendor, Transaction Type, Asset Record. User chose one field and select the existing data e.g. if user select Centre then it will show list of existing centres, to chose from. After selecting, user click update button, and all the multiple records are updated with same centre. The same applies to all other fields. User can ONLY update one field at a time. For a transaction line to proceed to next stage, its related fields must ALL be updated. So the status field is only updated (Status=1) when all the required fields are populated.\r\n\r\n**Some Update Rules**\r\n1. When Transaction Type='MAINTENANCE' the Asset Record Type Must always be 'EXIST'\r\n2. When Transaction Type='PURCHASE' the Asset Record Type must always be 'NEW'\r\n3. For a transaction line to proceed to next stage, its related fields must ALL be updated. So the status field is only updated (Status=1) when all the required fields are populated.\r\n\r\n**STEP 3 - DETAIL UPDATE**\r\n\r\nUser can do one of the following on selected Bulk Updated (Status=2) transactions\r\n\r\n1. Single Transaction  - based on one line of transaction (1 Line=1 Asset). Multiple Imported transactions can be selected and and updated. User intervention is required to enter Asset Number,Description,Asset Type,Asset Category, Asset Amount,Custodian. Ideally a pop window with selected transactions will show and only the *detail update fields* will be editable. When saving it will update the respective selected records\r\n\r\n2. Split Transaction - can only be used on Single Transaction Line. Does not allow multiple transactions selection. Ideally, a pop up window will allow user to create multiple same transactions with the following fields Asset Number,Description,Asset Type,Asset Category, Asset Amount,Custodian editable. When saving it will delete the original transaction but create the multiple transactions with updated fields. One Rule is that the Tran Amount original transaction line must equal to the Sum of Asset Amount all new transaction lines. If move then advise user to adjust Asset Amount to Balance to Tran Amount. If Less then prompt user to adjust Tran Amount or to Create NON-ASSET type transaction automatically to balance with Tran Amount.\r\n\r\n**STEP 4 - CREATE ASSET REGISTRY, TRANSACTIONS AND ALLOCATION RECORDS**\r\n(For Asset Transactions refer to #153 for more information)\r\n\r\n**Steps for automatic creation**\r\n1. Create Asset Registry Record without Acquired Cost for new assets. The asset ID will be used to create the Asset Transaction\r\n2. Create Asset Allocation if staff  name is selected\r\n3. Create Related Asset Transactions base on the #153 business rules.\r\n\r\n\r\n**Import table mapping to Asset Transaction**\r\nCheque Date = Trans Date\r\nGL Account = GL Account\r\nAccount Source = Account Source\r\nSection=Section\r\nTransaction Type = Transaction Type. Rules in #153 applies here too.\r\nDetail = Tran Detail\r\nAmount=Tran Amount and or AssetValue depending on Transaction Type Rule.\r\nImport Reference = Other Reference.\r\nVendor=Vendor\r\n\r\n**Import table mapping to Asset Master (Registry)**\r\nCheque Date = Acquired Date\r\nGL Account = GL Account\r\nAccount Source = Account Source\r\nSection=Section\r\nDetail =Purchase Reference\r\nCentre=Centre\r\nAsset Type = Asset Type\r\nVendor=Vendor.\r\n\r\n**Import table mapping to Asset Allocation**\r\nCustodian = Staff\r\nCheque Date = Allocated Date\r\n\r\n** UI REQUIREMENTS **\r\n\r\n1. Create new menu item under Asset Administration and called 'Import Asset Transactions'\r\n\r\n2. The Data Grid Will show the contents of table MstAssetImport, Ideally show the following fields on the grid\r\n(c). Chq Date (Cheque Date )\r\n(s). Acc Src (Account Source) \r\n(d). GL Acc (GL Account)\r\n(e). Section\r\n(f). Payee\r\n(g). Detail\r\n(h). CHQ (ChequeNumber)\r\n(i). Trn Amt (Tran Amount)\r\n(j). Centre\r\n(k). Type (Asset Type)\r\n(l). Vendor\r\n(m). Trans Type (Transaction Type)\r\n(n). Record\r\n(j). Asset Number\r\n(o). Description\r\n(l). Cat (Cat)\r\n(m). Asset Amt (Asset Amount)\r\n(n). Staff (Custodian)\r\n(o). Sts (Status)\r\n\r\n3. Do not show processed (Status=3) records.\r\n"},"comment":{"url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/comments/161967860","html_url":"https://github.com/kumulsoft/Fixed-Assets/issues/154#issuecomment-161967860","issue_url":"https://api.github.com/repos/kumulsoft/Fixed-Assets/issues/154","id":161967860,"user":{"login":"mnarewec","id":10192106,"avatar_url":"https://avatars.githubusercontent.com/u/10192106?v=3","gravatar_id":"","url":"https://api.github.com/users/mnarewec","html_url":"https://github.com/mnarewec","followers_url":"https://api.github.com/users/mnarewec/followers","following_url":"https://api.github.com/users/mnarewec/following{/other_user}","gists_url":"https://api.github.com/users/mnarewec/gists{/gist_id}","starred_url":"https://api.github.com/users/mnarewec/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mnarewec/subscriptions","organizations_url":"https://api.github.com/users/mnarewec/orgs","repos_url":"https://api.github.com/users/mnarewec/repos","events_url":"https://api.github.com/users/mnarewec/events{/privacy}","received_events_url":"https://api.github.com/users/mnarewec/received_events","type":"User","site_admin":false},"created_at":"2015-12-04T13:28:38Z","updated_at":"2015-12-04T13:28:38Z","body":"@agilecubeteam , userId should of the user who imported the data."}}