1. Field MUST have
1.1. created_by
1.2. created_on
1.3. modified_by
1.4. modified_on
1.5. status
1.6. If choose soft deleted
1.6.1. deleted_by
1.6.2. deleted_on
1.6.3. is_deleted
2. Loyalty
2.1. ain_loyalty_level_points
2.1.1. name
2.1.2. point_type_value
2.1.3. point_type_label: Earn Point
2.1.4. currency_id
2.1.5. amount: money
2.1.6. point: int
2.2. ain_loyalty_cycles
2.2.1. name
2.2.2. from_date: bigint
2.2.3. to_date: bigint
2.2.4. extend_expired_day: int (extend to_date)
2.2.5. owner_id
2.2.6. owner_type
2.3. ain_loyalty_levels
2.3.1. name
2.3.2. sequence: int
2.3.3. start_point: int
2.3.4. end_point: int
2.4. ain_loyalty_programs
2.4.1. name
2.4.2. from_date
2.4.3. to_date
2.4.4. level_point_id
2.4.5. promotion_type_value
2.4.6. promotion_type_label: (invoice/birthday/special)
2.4.7. loyalty_applied_value
2.4.8. loyalty_applied_label
2.4.9. product_applied_value
2.4.10. product_applied_label : (missing when design)
2.4.11. bonus_point_type_value
2.4.12. bonus_point_type_label: (Plus -- Point / Double Point)
2.4.13. bonus_point: int
2.4.14. owner_id
2.4.15. owner_type
2.4.16. status_value
2.4.17. status_label
2.5. ain_loyalty_points
2.5.1. contact_id
2.5.1.1. joined_date - bigint
2.5.1.2. earn_point - int
2.5.1.3. redemption_point - int
2.5.1.4. bonus_point - int
2.5.1.5. level - int
2.5.1.6. cycle_point_start - bigint
2.5.1.7. cycle_point_end - bigint
2.5.1.8. level_point - int
2.5.1.9. balance_point - int
2.5.1.10. expired_date - bigint
2.5.1.11. previous_cycle_point_start - bigint
2.5.1.12. previous_cycle_point_end - bigint
2.5.1.13. previous_earn_point - int
2.5.1.14. previous_bonus_point - int
2.5.1.15. previous_redemption_point - int
2.5.1.16. previous_balance_point - int
2.6. ain_loyalty_transactions
2.6.1. loyalty_point_id
2.6.2. invoice_id
2.6.3. transaction_date
2.6.4. invoice_name
2.6.5. invoice_number: (invoice_code in ain_invoices)
2.6.6. earn_point - int
2.6.7. bonus_point int
2.6.8. redemption_point - int
2.6.9. balance_point - int
2.6.10. previous_point: int
2.6.11. level_name
2.6.12. loyalty_cycle_id: (for calculate loyalty_point when a cycle is end)
2.6.13. description
2.6.14. contact_id
2.7. ain_loyalty_promotion_rules
2.7.1. loyalty_program_id
2.7.2. name
2.7.3. from_date
2.7.4. to_date
2.7.5. marketing_list_id
2.7.6. apply_for_category_product_value
2.7.7. apply_for_category_product_label
2.7.8. product_applied_value
2.7.9. product_applied_label
2.7.10. category_applied_value
2.7.11. category_applied_label
2.7.12. applied_amount_quantity_label
2.7.13. applied_amount_quantity_value
2.7.14. amount_from : money (instead of invoice_amount_from )
2.7.15. amount_to : money
2.7.16. quantity_from
2.7.17. quantity_to
2.7.18. CURRENCY : not column - get from Loyalty Program > Level Point
2.8. ain_loyalty_promotion_rule_products
2.8.1. loyalty_promotion_rule_id
2.8.2. product_id
2.9. ain_loyalty_promotion_rule_categories
2.9.1. loyalty_promotion_rule_id
2.9.2. category_id
3. Service
3.1. ain_case_sla_details
3.1.1. name
3.1.2. case_id
3.1.3. sla_detail_id
3.1.4. failure_after: bigint (minutes)
3.1.5. warn_after: bigint (minutes)
3.1.6. is_pause: bit
3.1.7. business_process_flow_id
3.1.8. business_process_stage_id
3.2. ain_case_similars
3.2.1. case_id
3.2.2. case_similar_id
3.3. ain_case_mergeds
3.3.1. case_id
3.3.2. case_merged_id
3.4. ain_sla_details
3.4.1. name
3.4.2. service_level_agreement_id
3.4.3. subject_value
3.4.4. subject_label
3.4.5. priority_value
3.4.6. priority_label
3.4.7. business_process_flow_id
3.4.8. business_process_stage_id
3.4.9. warn_after: bigint (miliseconds)
3.4.10. failure_after: bigint (miliseconds)
3.5. ain_contracts
3.5.1. contract_code
3.5.2. contract_name
3.5.3. customer_record_id
3.5.4. customer_entity_id
3.5.5. customer_entity_type
3.5.6. CONTACT_ADDRESS: same with accoutnts
3.5.7. start_date
3.5.8. end_date
3.5.9. duration_int_days: int
3.5.10. discount_type_value
3.5.11. discount_type_label
3.5.12. service_level_value
3.5.13. service_level_label
3.5.14. originating_contract_id: (action contract renew)
3.5.15. owner_id
3.5.16. owner_type
3.5.17. description: 2000
3.5.18. bill_to_customer_record_id
3.5.19. bill_to_customer_entity_id
3.5.20. bill_to_customer_entity_type
3.5.21. BILL_TO_ADDRESS (same with accounts)
3.5.22. billing_start_date
3.5.23. billing_end_date
3.5.24. billing_frequency_value
3.5.25. billing_frequency_label
3.5.26. cancellation_date: bigint
3.5.27. currency_id
3.5.28. total_price: money
3.5.29. total_discount: money
3.5.30. net_price: money
3.5.31. allotment_type_value
3.5.32. allotment_type_label
3.5.33. status_value
3.5.34. status_label
3.6. ain_knowledge_base_entities
3.6.1. entity_id
3.6.2. is_selected: bit
3.7. ain_cases
3.7.1. title - nvarchar(255)
3.7.2. case_code (auto generate)
3.7.3. subject_label
3.7.4. subject_value
3.7.5. customer_record_id
3.7.6. customer_entity_id
3.7.7. customer_entity_type
3.7.8. origin_label
3.7.9. origin_value
3.7.10. contact_id
3.7.11. entitlement_id
3.7.12. product_id
3.7.13. description
3.7.14. case_type_label
3.7.15. case_type_value
3.7.16. parent_id
3.7.17. sla_id
3.7.18. is_escalated
3.7.19. escalated_on
3.7.20. follow_up_on
3.7.21. is_first_response_sent
3.7.22. article_id
3.7.23. contract_id
3.7.24. contract_line_id
3.7.25. serial_number (nvarchar 255)
3.7.26. service_level_label
3.7.27. service_level_value
3.7.28. priority_label
3.7.29. priority_value
3.7.30. status_label
3.7.31. status_value
3.7.32. status_reason_label
3.7.33. status_reason_value
3.7.34. owner_id
3.7.35. owner_type
3.8. ain_articles
3.8.1. title nvarchar(255)
3.8.2. subject_label
3.8.3. subject_value
3.8.4. keywords
3.8.5. code
3.8.6. language_label
3.8.7. language_value
3.8.8. content - ntext
3.8.9. status_label
3.8.10. status_value
3.9. ain_article_comments
3.9.1. article_id
3.9.2. title
3.9.3. content
3.10. ain_service_level_agreements
3.10.1. name - 255
3.10.2. entity_id
3.10.3. applicable_form_label
3.10.4. applicable_from_value
3.10.5. business_hour_id: (sample data for Service Calendar)
3.10.6. sla_type_value
3.10.7. sla_type_label
3.10.8. is_allow_pause
3.10.9. description
3.10.10. status_value
3.10.11. status_label
3.10.12. owner_id
3.10.13. owner_type
3.10.14. is_default: (only 1 SLA default)
3.11. ain_flow_sla_trackings
3.11.1. entity_id
3.11.2. entity_record_id
3.11.3. entity_type
3.11.4. sla_detail_id
3.11.5. business_process_stage_id
3.11.6. first_response_on
3.11.7. resolve_on
3.11.8. warn_after: bigint (miliseconds)
3.11.9. failure_after: bigint (miliseconds)
3.11.10. pause_on
3.11.11. pause_duration_time: int (view flow image in Jira) https://jr.ainovation.com/browse/AINCRM-696
3.12. ain_sla_workdays
3.12.1. sla_workplan_id
3.12.2. week_day: tinyint (0: CN, 1:Mon, ....)
3.12.3. name: string concat from start/end/duration
3.13. ain_activities_service_customers
3.13.1. activities_service_id
3.13.2. entity_record_id
3.13.3. entity_id
3.13.4. entity_type
3.14. ain_activities_service_resources
3.14.1. activities_service_id
3.14.2. entity_record_id
3.14.3. entity_id
3.14.4. entity_type
3.15. ain_sla_working_day_details
3.15.1. sla_working_day_id
3.15.2. work_hour_from: bigint
3.15.3. work_hour_to: bigint
3.15.4. type: (1: WORKDAYS, 2: BREAK)
3.16. ain_sla_workplans
3.16.1. name
3.16.2. description
3.16.3. work_hours_value
3.16.4. work_hours_label
3.16.5. timezone_id
3.16.6. status_value
3.16.7. status_label
3.16.8. holiday_schedule_id
3.17. ain_activities_services
3.17.1. subject
3.17.2. service_id: (reference to module Service)
3.17.3. site_id
3.17.4. location
3.17.5. show_time_as_label
3.17.6. show_time_as_value
3.17.7. start_time
3.17.8. end_time
3.17.9. is_all_day_event: bit
3.17.10. duration => DELETE
3.17.11. regarding_entity_id
3.17.12. regarding_entity_record_id
3.17.13. regarding_entity_type
3.17.14. owner_id
3.17.15. owner_type
3.17.16. priority_label
3.17.17. priority_value
3.17.18. category: nvarchar
3.17.19. sub_category: nvarchar
3.17.20. status_value
3.17.21. status_label
3.17.22. status_reason_label: *** (NOT USE)
3.17.23. status_reason_value: *** (NOT USE)
3.18. ain_holidays
3.18.1. name
3.18.2. start_date
3.18.3. end_date
3.18.4. year_of_start_date: (get from start_date for display and filter)
3.19. ain_holiday_schedules
3.19.1. name
3.19.2. description
3.19.3. owner_id
3.19.4. owner_type
3.20. ain_services
3.20.1. name
3.20.2. status_reason_value
3.20.3. status_reason_label
3.20.4. duration_value
3.20.5. duration_labe
3.20.6. activities_every_value
3.20.7. activities_every_label
3.20.8. begin_at_value
3.20.9. begin_at_label
3.20.10. description
4. Menus
4.1. ain_menu_entities
4.1.1. menu_id
4.1.2. entity_id
4.2. ain_menus
4.2.1. id
4.2.2. name
4.2.3. url
4.2.4. order_by
4.2.5. menu_type
4.2.5.1. 1: modules
4.2.5.2. 2: group
4.2.5.3. 3: function
4.2.6. parent_id
4.2.7. icon
5. Sales Literature
5.1. ain_sale_literatures
5.1.1. title
5.1.2. subject: smallint (FE hardcode)
5.1.3. type_value
5.1.4. type_label
5.1.5. description
5.1.6. expiration_date: bigint
5.1.7. employee_contact_id: (User)
5.2. ain_sale_attachments
5.2.1. title
5.2.2. author
5.2.3. keywords
5.2.4. abstract: 2000
5.2.5. file_name
5.2.6. file_size
5.2.7. file_path
5.2.8. sale_literature_id PK
5.2.9. status_value
5.2.10. status_label
5.3. ain_sale_literature_products
5.3.1. sale_literature_id
5.3.2. product_id
5.4. ain_sale_literature_competitors
5.4.1. sale_literature_id
5.4.2. competitor_id
6. Options Set
6.1. ain_option_set_fields
6.1.1. id
6.1.2. option_set_pool_id
6.1.3. entity_field_id
6.1.4. entity_id
6.2. ain_option_set_pools
6.2.1. id
6.2.2. label - nvarchar (255)
6.2.3. value - bigint
6.2.4. order_by - smallint
7. Orders
7.1. ain_orders
7.1.1. order_code: n 20
7.1.2. name
7.1.3. currency_id
7.1.4. price_list_id
7.1.5. prices_locked: bit
7.1.6. requested_delivery: bigint
7.1.7. date_fulfilled: bigint
7.1.8. shipping_method_value
7.1.9. shipping_method_label
7.1.10. payment_terms_value
7.1.11. payment_terms_label
7.1.12. bill_to_address: same account address -7 fields
7.1.13. ship_to_value
7.1.14. ship_to_label
7.1.15. ship_to_address: same bill_to_address above
7.1.16. detail_amount: same detail amount opportunity - 7 fields
7.1.17. total_amount: money
7.1.18. opportunity_id
7.1.19. quotes_id
7.1.20. potential_entity_id
7.1.21. potential_entity_type
7.1.22. potential_record_id
7.1.23. description
7.1.24. order_status_label
7.1.25. order_status_value
7.1.26. owner_id
7.1.27. owner_type
7.2. ain_order_products
7.2.1. orders_id
7.2.2. product_id
7.2.3. unit_id
7.2.4. price_per_unit: money
7.2.5. quantity: decimal
7.2.6. discount: money
7.2.7. extend_amount: money
8. Entities
8.1. ain_entity_fields
8.1.1. id
8.1.2. display_name
8.1.3. name
8.1.4. requirement_value
8.1.5. requirement_label
8.1.6. is_searchable - bit
8.1.7. field_security - bit
8.1.8. auditing - bit
8.1.9. description - nvarchar(2000)
8.1.10. data_type - smallint
8.1.10.1. single line of text
8.1.10.2. option set
8.1.10.3. two options
8.1.10.4. images
8.1.10.5. whole number
8.1.10.6. floating point number
8.1.10.7. decimal number
8.1.10.8. currency
8.1.10.9. multiple lines of text
8.1.10.10. date and time
8.1.10.11. lookup
8.1.11. min_value
8.1.12. max_value
8.1.13. format - smallint
8.1.13.1. text
8.1.13.2. email
8.1.13.3. text area
8.1.13.4. url
8.1.13.5. ticket symbol
8.1.13.6. phone
8.1.14. maximum_length
8.1.15. entity_id - guid
8.2. ain_entities
8.2.1. id
8.2.2. display_name - nvarchar(255)
8.2.3. plural_name - nvarchar
8.2.4. name_in_db
8.2.4.1. default: ain_
8.2.5. icon
8.2.5.1. upload file
8.2.6. owner_type - smallint
8.2.6.1. 1: user or team
8.2.6.2. 2: organization
8.2.7. description - nvarchar(2000)
8.2.8. business_process_flow - bit
8.2.9. note - bit
8.2.10. activities - bit
8.2.11. connections - bit
8.2.12. sending_email - bit
8.2.13. document_management - bit
8.2.14. knowledge_management - bit
8.2.15. enable_for_sla - bit
8.2.16. allow_quick_create - bit
8.2.17. duplicate_detection - bit
8.2.18. auditing - bit
8.2.19. is_system - bit
9. Products
9.1. ain_units
9.1.1. name - n - 255
9.1.2. quantity - decimal(18,5)
9.1.3. parent_id : guid
9.1.4. unit_group_id - guid
9.2. ain_unit_groups
9.2.1. name - n - 255
9.3. ain_notes
9.3.1. entity_id
9.3.2. entity_record_id
9.3.3. entity_type
9.3.4. title - n - 255
9.3.5. content - n - 2000
9.3.6. attachment - n - 1000 (url file)
9.4. ain_products
9.4.1. name - n - 255
9.4.2. product_code -n - 255
9.4.3. parent_id - guid
9.4.4. valid_from - bigint
9.4.5. valid_to - bigint
9.4.6. description - n - 2000
9.4.7. unit_group_id - guid
9.4.8. unit_id - guid
9.4.9. price_list_id - guid
9.4.10. decimal_supported - byte
9.4.11. subject - smallint (FE hardcode)
9.4.12. status_label
9.4.13. status_value
9.4.14. product_structure_value : (1: Product Family, 2: Product, 3: Product Bundle)
9.4.15. product_structure_label
9.5. ain_properties
9.5.1. name -n 255
9.5.2. read_only - bit
9.5.3. required - bit
9.5.4. hidden - bit
9.5.5. regarding_product_id
9.5.6. decription - n 2000
9.5.7. datatype_value - smallint
9.5.8. datatype_label
9.5.9. min_value: decimal
9.5.10. max_value:decimal
9.5.11. default_value: decimal
9.5.12. precision:smallint
9.5.13. text_default: n 1000
9.5.14. max_length: smallint
9.6. ain_property_optionset
9.6.1. property_id
9.6.2. name
9.6.3. value
9.6.4. description: n 2000
9.6.5. is_default: bit
9.7. ain_product_properties
9.7.1. product_id
9.7.2. property_id
9.8. ain_territories
9.8.1. state_id
9.8.2. city_id
9.8.3. province_id
9.8.4. name
9.9. ain_price_list_territories
9.9.1. price_list_id
9.9.2. territory_id
9.10. ain_discount
9.10.1. name
9.10.2. discount_type_value
9.10.3. discount_type_label
9.10.4. currency_id
9.10.5. description: n 2000
9.11. ain_discount_details
9.11.1. begin_quantity: decimal
9.11.2. end_quantity: decimal
9.11.3. amount: money
9.11.4. percent: decimal
10. Competitors
10.1. ain_opportunity_competitors
10.1.1. opportunity_id
10.1.2. competitor_id
10.2. ain_lead_competitors
10.2.1. lead_id
10.2.2. competitor_id
10.3. ain_competitors
10.3.1. name - n - 255
10.3.2. reported_revenue - money
10.3.3. website -n - 255
10.3.4. currency_id
10.3.5. address : same table account
10.3.6. strength -n - 2000
10.3.7. weakness - n - 2000
10.3.8. ticker_symbol -n - 50
11. StackHolders
11.1. ain_lead_stackholders
11.1.1. lead_id
11.2. ain_opportunity_stackholders
11.2.1. opportunity_id
12. Opportunities
12.1. ain_opportunities
12.1.1. topic - n - 255
12.1.2. contact_id
12.1.3. account_id
12.1.4. purchase_time_frame_label
12.1.5. purchase_time_frame_value
12.1.6. currency_id
12.1.7. budget_amount - money
12.1.8. purchase_process_label
12.1.9. purchase_process_value
12.1.10. description
12.1.11. current_situation - n - 2000
12.1.12. customer_need - n - 2000
12.1.13. proposed_solution -n - 2000
12.1.14. price_list_id
12.1.15. revenue_label
12.1.16. revenue_value
12.1.17. detail_amount - money
12.1.18. discount_percent - decimal
12.1.19. discount - money
12.1.20. pre_freight_amount - money
12.1.21. preight_amount: money
12.1.22. total_tax - money
12.1.23. total_amount - money
12.1.24. est_close_date - bigint
12.1.25. est_revenue - money
12.1.26. owner_type
12.1.27. owner_id
12.1.28. status_label
12.1.29. status_value
12.2. ain_opportunity_saleteams
12.2.1. opportunity_id
12.2.2. user_id
12.2.3. sale_role_label
12.2.4. sale_role_value
12.3. ain_opportunity_products
12.3.1. opportunity_id
12.3.2. product_id
12.3.3. price_per_unit: money
12.3.4. quantity: int
12.3.5. discount: money
12.4. ain_price_list
12.4.1. name
12.4.2. start_date: bigint
12.4.3. end_date: bigint
12.4.4. currency_id
12.4.5. description: n 2000
12.5. ain_price_list_items
12.5.1. price_list_id
12.5.2. product_id
12.5.3. unit_id
12.5.4. currency_id
12.5.5. discount_id
12.5.6. quantity_selling_value
12.5.7. quantity_selling_label
12.5.8. pricing_method_value
12.5.9. pricing_method_label
12.5.10. amount: money
12.5.11. percentage: decimal
12.5.12. rounding_policy_value
12.5.13. rounding_policy_label
12.5.14. rounding_amount: money
12.6. ain_opportunity_quotes
12.6.1. opportunity_id
12.6.2. quote_id
12.6.3. name
12.6.4. status_value
12.6.5. status_label
12.6.6. total_amount: money
12.6.7. effect_from: bigint
12.6.8. effect_to: bigint
13. PHRASE 2
13.1. Marketing
14. LEAD
14.1. ain_leads
14.1.1. topic - nvarchar _ 255
14.1.2. first_name - n - 255
14.1.3. last_name - n - 255
14.1.4. full_name - n - 600
14.1.5. job_title - n - 255
14.1.6. business_phone - n - 30
14.1.7. mobile_phone - n - 30
14.1.8. email - n - 255
14.1.9. company_name - n - 255
14.1.10. address: same contact
14.1.11. description - n - 2000
14.1.12. industry_value
14.1.13. industry_label
14.1.14. annual_revenue - money
14.1.15. no_of_employees - int
14.1.16. sic_code - nvarchar(20)
14.1.17. currency_id
14.1.18. campaign_id
14.1.19. marketing_material - bit
14.1.20. last_campaign_date - big int
14.1.21. prefered_method_value
14.1.22. prefered_method_label
14.1.23. allow_send_email - bit
14.1.24. allow_follow_email
14.1.25. allow_bulk_email
14.1.26. allow_phone
14.1.27. allow_mail
14.1.28. lead_source_value
14.1.29. lead_source_label
14.1.30. rating_value
14.1.31. rating_label
14.1.32. lead_status_value
14.1.33. lead_status_label
14.1.34. owner_type
14.1.35. owner_id
14.1.36. business_process_flow_id
14.1.37. stage_pending_on
14.1.38. BUSINEES FLOW: phan tich sau
14.2. ain_lead_campaigns
14.2.1. id
14.2.2. lead_id
14.2.3. campaign_id
15. CASES
15.1. ain_cases
15.1.1. customer_entity_id
15.1.2. customer_entity_type
15.1.3. customer_entity_record_id
15.1.4. title - n - 255
15.1.5. subject - smallint (hard code FE)
15.1.6. case_type_value
15.1.7. case_type_label
15.1.8. contact_id
15.1.9. assign_id
15.1.10. assign_type
15.1.11. parent_id
15.1.12. origin_value
15.1.13. origin_label
15.1.14. product_id
15.1.15. entitlement_id
15.1.16. first_response_by_id
15.1.17. first_response_by_type
15.1.18. resolve_by_id
15.1.19. resolve_by_type
15.1.20. description
16. Contact
16.1. ain_contacts
16.1.1. dsads
16.1.2. full_name - nvarchar(600)
16.1.3. last_name - n - 255
16.1.4. fist_name - n - 255
16.1.5. job_title - n - 255
16.1.6. account_contact_id
16.1.7. email - n- 255
16.1.8. business_phone - n - 30
16.1.9. mobile_phone - n - 30
16.1.10. fax - n - 30
16.1.11. prefered_method_value: smallint
16.1.12. perfered_method_label - n - 255
16.1.13. address_stree_1(2)(3) - n - 1000
16.1.14. city (input ) - n - 255
16.1.15. state_id
16.1.16. province_id
16.1.17. country_id
16.1.18. postal_code - n - 10
16.1.19. lat - n - 50
16.1.20. lng - n - 50
16.1.21. company_record_id
16.1.22. company_entity_id
16.1.23. company_entity_type
16.1.24. gender_value
16.1.25. gender_label
16.1.26. marital_status_label
16.1.27. marital_status_value
16.1.28. partner_name - n - 255
16.1.29. birthday: bigint
16.1.30. anniversary: bigint
16.1.31. notes - n - 2000
16.1.32. original_lead_entity_id
16.1.33. original_lead_entity_type
16.1.34. original_lead_record_id
16.1.35. last_campaign_date: bigint
16.1.36. marketing_material: bit
16.1.37. currency_id
16.1.38. credit_limit :money
16.1.39. credit_hold: bit
16.1.40. payment_terms_value
16.1.41. payment_terms_label
16.1.42. shipping_method_value
16.1.43. shipping_method_label
16.1.44. contact_method_value
16.1.45. contact_method_label
16.1.46. allow_send_email - bit
16.1.47. allow_follow_email - bit
16.1.48. allow_bulk_email - bit
16.1.49. allow_phone - bit
16.1.50. allow_fax - bit
16.1.51. allow_mail - bit
16.1.52. owner_id
16.1.53. owner_type - smallint
16.1.54. follow - bit
16.1.55. lead_id
16.2. ain_contact_cases
16.2.1. ain_contact_id
16.2.2. ain_case_id
16.3. ain_contact_opportunities
16.3.1. ain_contact_id
16.3.2. ain_opportunity_id
16.4. ain_contact_entitilements
16.4.1. ain_contact_id
16.4.2. ain_entitilement_id
17. Dynamic
17.1. ain_dynamic_pages
17.1.1. id
17.1.2. title
17.1.3. url
17.1.4. layout - ntext
17.1.4.1. Definition Json
17.1.4.1.1. [1]: Component
18. BUSINESS FLOW
18.1. ain_business_process_flows
18.1.1. entity_id
18.1.2. name - n - 255
18.2. ain_business_process_stages
18.2.1. business_process_flow_id
18.2.2. name
18.2.3. stage_order - int
18.2.3.1. stage 1,2,3,4...
18.2.4. description
18.3. ain_business_process_steps
18.3.1. business_process_stage_id
18.3.2. entity_field_id
18.3.3. is_required - bit
18.4. ain_business_process_trackings
18.4.1. entity_id
18.4.2. business_process_flow_id
18.4.3. business_process_stage_id
18.4.4. entity_record_id
18.4.5. name_entity
19. Activities
19.1. ain_activities_appointment_detail
19.1.1. activities_appointment_id
19.1.2. entity_id
19.1.3. entity_type
19.1.4. entity_record_id
19.1.5. type
19.1.5.1. required
19.1.5.2. regarding
19.1.5.3. optional
19.2. ain_activities_task
19.2.1. subject
19.2.2. description
19.2.3. due_date
19.2.4. priority
19.2.4.1. normal
19.2.4.2. high
19.2.4.3. low
19.2.5. owner_type
19.3. ain_activities_phone_calls
19.3.1. description - nvarchar(2000)
19.3.2. due_date - bigint
19.3.3. call_with
19.3.4. entity_id
19.3.5. entity_type
19.3.6. entity_record_id
19.3.7. direction
19.3.7.1. incoming
19.3.7.2. outgoing
19.4. ain_activities_posts
19.4.1. post_content - nvarchar(1000)
19.4.2. entity_id
19.4.3. entity_type
19.4.4. entity_record_id
19.4.5. parent_id
19.5. ain_activities_email
19.5.1. from_email - (map with userID) - guid
19.5.2. subject_name
19.5.3. due_date: bigint
19.5.4. priority
19.5.5. owner_type
19.5.6. body - nvarchar(max)
19.5.7. regarding_entity_id
19.5.8. regarding_entity_type
19.5.9. regarding_entity_record_id
19.5.10. sender_id - guid
19.6. ain_activities_appointment
19.6.1. subject
19.6.2. location
19.6.3. priority
19.6.4. owner_type
19.6.5. start_time
19.6.6. end_time
19.6.7. duration_value
19.6.8. duration_label
19.6.9. description
19.7. ain_activities_email_header
19.7.1. activities_email_id
19.7.2. email
19.7.3. email_type
19.7.3.1. cc
19.7.3.2. to
19.7.3.3. bcc
19.7.4. entity_type
19.7.5. entity_id
19.8. ain_activities_attachment
19.8.1. file_name
19.8.2. file_size
19.8.3. file_path
19.8.4. type
19.8.4.1. activity_email
19.8.4.2. activity_appointment
19.8.5. activities_mail_id
19.9. ain_activities_appointment
19.9.1. subject
19.9.2. location
19.9.3. priority
19.9.4. owner_type
19.9.5. start_time
19.9.6. end_time
19.9.7. duration_value
19.9.8. duration_label
19.9.9. description
20. Database Convention
20.1. active = 1
20.2. deactive = 0
20.3. id : guid
20.4. date: timestamp
20.5. string: nvarchar
20.5.1. email,file: nvarchar(255)
20.6. avatar: base 64
20.7. ain_crm_[tablename]
20.8. colume name: use lower case
20.9. Words are separated with underscores
21. Relation Ship
21.1. Sites (1) - (n) User
21.2. Company (n) - (n) Site
21.3. Business_unit_id (1) - (0..n) User
21.4. User (1) - (0..1) Position
21.5. Security_role (1) - (1) Business Unit
22. Not Complete
22.1. ain_account_connection
22.2. ain_industries
22.3. ain_leads
22.4. ain_entititlements
22.5. ain_marketing_lists
22.6. ain_account_childs
23. Quotes
23.1. ain_quotes
23.1.1. quote_code: n 20
23.1.2. revision_id: int
23.1.3. name
23.1.4. currency_id
23.1.5. price_list_id
23.1.6. shipping_method_value
23.1.7. shipping_method_label
23.1.8. payment_terms_value
23.1.9. payment_terms_label
23.1.10. bill_to_address: same account address - 7 fields
23.1.11. ship_to_value
23.1.12. ship_to_label
23.1.13. ship_to_address: same bill_to_address above
23.1.14. detail_amount: same detail amount opportunity - 7 fields
23.1.15. total_amount: money
23.1.16. effective_from: bigint
23.1.17. effective_to: bigint
23.1.18. owner_id
23.1.19. owner_type: smallint
23.1.20. opportunity_id
23.1.21. potential_entity_id
23.1.22. potential_entity_type: smallint
23.1.23. potential_entity_recored_id
23.1.24. description: n 2000
23.2. ain_quote_products
23.2.1. quotes_id
23.2.2. product_id
23.2.3. unit_id
23.2.4. price_per_unit: money
23.2.5. quantity: decimal
23.2.6. discount: money
23.2.7. extend_amount: money
24. Invoices
24.1. ain_invoices
24.1.1. invoice_code: n 20
24.1.2. name
24.1.3. currency_id
24.1.4. price_list_id
24.1.5. prices_locked: bit
24.1.6. date_delivery: bigint
24.1.7. due_date: bigint
24.1.8. shipping_method_value
24.1.9. shipping_method_label
24.1.10. payment_terms_value
24.1.11. payment_terms_label
24.1.12. ADDRESS : same quotes
24.1.13. SHIP_TO: same quotes
24.1.14. DETAIL_AMOUNT: same quotes
24.1.15. opportunity_id
24.1.16. order_id
24.1.17. potential_entity_id
24.1.18. potential_entity_type
24.1.19. potential_record_id
24.1.20. description
24.1.21. total_amount: money
24.1.22. invoice_status_label
24.1.23. invoice_status_value
24.1.24. status_reason_label
24.1.25. status_reason_value
24.1.26. owner_id
24.1.27. owner_type
25. Logic Search View
25.1. ain_view_conditions
25.1.1. view_id
25.1.2. conditions
25.2. ain_views
25.2.1. name
25.2.2. scripts: n max
25.2.3. entity_id
25.2.4. type : smallint (1: viewlist, 2:detail)
25.3. ain_view_fields
25.3.1. field_name: <key>
25.3.2. view_id
25.3.3. entity_id
25.4. ain_languages
25.4.1. name
25.5. ain_language_values
25.5.1. language_id
25.5.2. key
25.5.3. value
26. Assign & Follows
26.1. ain_user_follows
26.1.1. user_team_id - guid
26.1.2. entity_id - guid
26.1.2.1. Account, contact...., reference to table entity
26.1.3. entity_type - smallint
26.1.3.1. user or team
26.1.4. entity_record_id - guid
26.2. ain_user_assigns
26.2.1. user__team_id - guid
26.2.2. entity_id - guid
26.2.2.1. Account, contact...., reference to table entity
26.2.3. entity_type - smallint
26.2.3.1. user or team
26.2.4. entity_record_id - guid
27. Marketing
27.1. ain_marketing_list
27.1.1. name - nvarchar(255)
27.1.2. list_type_value
27.1.3. list_type_label
27.1.4. purpose - nvarchar(500)
27.1.5. targeted_at_value - uuid
27.1.6. targeted_at_label
27.1.7. source - nvarchar(500)
27.1.8. currency_id - uuid
27.1.9. cost: money
27.1.10. owner_id
27.1.11. owner_type
27.1.12. is_locked - bit
27.1.13. description - nvarchar(2000)
27.1.14. last_used_on - bigint
27.2. ain_marketing_list_members
27.2.1. member_id - uuid
27.2.2. member_type
27.2.3. member_entity
27.3. ain_marketing_list_campaigns
27.3.1. marketing_list_id
27.3.2. campaign_id
27.4. ain_marketing_list_quick_campaigns
27.4.1. marketing_list_id
27.4.2. quick_campaign_id
27.5. ain_view_mapping_combos
27.5.1. code - nvarchar(255)
27.5.2. view_id - uuid
27.5.3. entity - nvarchar(255)
27.5.4. display: nvarchar(255)
27.6. ain_campaigns
27.6.1. name - nvarchar(255)
27.6.2. campaign_code - nvarchar(255)
27.6.3. currency_id - uuid
27.6.4. campaign_type_value
27.6.5. campaign_type_label
27.6.6. expected_response - int (0 - 100)
27.6.7. proposed_start - bigint
27.6.8. proposed_end - bigint
27.6.9. actual_start - bigint
27.6.10. actual_end - bigint
27.6.11. offer - nvarchar(2000)
27.6.12. activity_cost - money
27.6.13. misc_cost - money
27.6.14. allocated_budget - money
27.6.15. total_cost - money
27.6.16. description - nvarchar(2000)
27.6.17. owner_id
27.6.18. owner_type
27.6.19. is_template - bit
27.6.20. estimated_revenue - money
27.6.21. status_label
27.6.22. status_value
27.7. ain_campaign_responses
27.7.1. subject - nvarchar(255)
27.7.2. customer_id
27.7.3. customer_type
27.7.4. customer_entity
27.7.5. company_name - nvarchar(255)
27.7.6. last_name - nvarchar(255)
27.7.7. first_name - nvarchar(255)
27.7.8. phone - nvarchar(30)
27.7.9. email - nvarchar(255)
27.7.10. description - nvarchar(2000)
27.7.11. promotion_code - nvarchar(255)
27.7.12. related_campaign_id
27.7.13. response_code_value
27.7.14. response_code_label
27.7.15. channel_label
27.7.16. channel_value
27.7.17. outsourced_vendor_id
27.7.18. outsourced_vendor_type
27.7.19. outsourced_vendor_entity
27.7.20. owner_id
27.7.21. owner_type
27.7.22. priority_value
27.7.23. priority_label
27.7.24. received_on - bigint
27.7.25. close_on - bigint
27.7.26. status_value
27.7.27. status_label
27.8. ain_campaign_communities
27.8.1. record_id
27.8.2. campaign_id
27.8.3. type
27.8.4. entity
27.9. ain_campaign_activities
27.9.1. subject - nvarchar(255)
27.9.1.1. campaign_id
27.9.1.2. type_value
27.9.1.3. type_label
27.9.1.4. channel_value
27.9.1.5. channel_label
27.9.1.6. outsourced_vendors_id
27.9.1.7. outsourced_vendors_type
27.9.1.8. outsourced_vendors_entity
27.9.1.9. description - nvarchar(2000)
27.9.1.10. scheduled_start - bigint
27.9.1.11. scheduled_end - bigint
27.9.1.12. actual_start - bigint
27.9.1.13. actual_end - bigint
27.9.1.14. currency_id
27.9.1.15. allocated_budget - money
27.9.1.16. actual_cost - money
27.9.1.17. no_of_days - int
27.9.1.18. priority_value
27.9.1.19. priority_label
27.9.1.20. status_value
27.9.1.21. status_label
27.9.1.22. owner_id
27.9.1.23. owner_type
27.10. ain_campaign_activity_marketing_list
27.10.1. marketing_list_id
27.10.2. campaign_activity_id
27.11. ain_quick_campaigns
27.11.1. name - nvarchar(255)
27.11.2. activity_type_value
27.11.3. activity_type_label
27.11.4. owner_id
27.11.5. owner_type
27.11.6. is_mark_email - bit
27.11.7. no_of_success - int
27.11.8. no_of_failures - int
27.11.9. error_details - nvarchar(500)
27.11.10. member_type_value
27.11.11. member_type_label
27.11.12. status_reason_value
27.11.13. status_reason_label
28. das
29. Business
29.1. business_unit _CAN REVIEW LAI COT CHUA json
29.1.1. id
29.1.2. name
29.1.3. parent_id
29.1.4. main_phone
29.1.5. other_phone
29.1.6. fax
29.1.7. email
29.1.8. website
29.1.9. bill_to_address_street_1
29.1.10. bill_to_address_street_2
29.1.11. bill_to_address_street_3
29.1.12. ship_to_address_street_1
29.1.13. ship_to_address_street_2
29.1.14. ship_to_address_street_3
29.1.15. status
29.1.15.1. active
29.1.15.2. deactive
29.1.16. team_id
29.1.17. (list_facility_equipment)
29.2. business_closure
29.2.1. id
29.2.2. name
29.2.3. start_time
29.2.4. end_time
29.2.5. duration
29.2.6. status
29.3. fiscal_year
29.3.1. id
29.3.2. fiscal_year
29.3.3. start_time
29.3.4. end_time
29.3.5. prefix
29.3.6. postfix
29.3.7. year_format (const)
29.3.7.1. YY
29.3.7.2. YYYY
29.3.8. period (const)
29.3.8.1. Q1
29.3.8.2. P1
29.3.9. display_format (const)
30. Common
30.1. timezones (gmt)
30.1.1. id
30.1.2. name
30.1.3. display_order
30.1.4. status
30.1.4.1. active
30.1.4.2. deactive
30.1.5. Country
30.2. ain_data_type
30.2.1. field_name
30.3. ain_form
30.3.1. id
30.3.2. entity_id : (Account, Contact)
30.3.3. name
30.3.4. fields : ntext
30.4. ain_layout
30.4.1. id
30.4.2. entity_id
30.4.3. page_type : nvarchar
30.4.4. content : ntext
31. Facility Equipment
31.1. facility_equipment_detail
31.1.1. id
31.1.2. date
31.1.3. work_hour_from / work_hour_to (unixtime)
31.1.3.1. case one day
31.1.3.1.1. Minwork: 1h
31.1.3.1.2. Validate
31.1.3.1.3. Break: 15' <= time <= minwork
31.1.3.2. case same working day
31.1.3.3. case not same working day
31.1.4. type (constant)
31.1.4.1. WORKING_HOUR
31.1.4.2. BREAK_TIME
31.1.5. timezone
31.2. facility_equipments
31.2.1. id
31.2.2. name
31.2.3. site_id
31.2.4. business_unit_id
31.2.5. primary_email
31.2.6. timezone
31.2.7. description
31.2.8. status
32. Security
32.1. role_field_member (step 3)
32.1.1. id
32.1.2. role_field_id
32.1.3. user_id
32.2. security_role_field_action (step 2)
32.2.1. id
32.2.2. role_field_id
32.2.3. resource_name
32.2.4. field_name
32.2.5. permission (bitwise)
32.2.6. status
32.3. user_security_roles
32.3.1. id
32.3.2. user_id
32.3.3. security_role_id
32.4. security_role_fields (step 1)
32.4.1. id
32.4.2. name
32.4.3. status
32.5. security_role_permission_action
32.5.1. id
32.5.2. security_role_id
32.5.3. resource_name
32.5.4. entity_id
32.5.5. permission (bitwise)
32.5.6. status
32.5.6.1. active
32.5.6.2. deactive
32.6. security_roles
32.6.1. id
32.6.2. name
32.6.3. status
32.6.3.1. active
32.6.3.2. deactive
33. Accounts
33.1. ain_account_entitlements
33.1.1. account_id
33.1.2. entitlement_id
33.2. ain_account
33.2.1. 1. id (nvarchar(160))
33.2.2. 2. name (nvarchar)
33.2.3. 3. phone - nvarchar(50)
33.2.4. 4. fax - nvarchar(50)
33.2.5. 5. website - nvarchar(255)
33.2.6. 6. account_parent_id - guid
33.2.7. 7. ticket_symbol - nvarchar(10)
33.2.8. 8. address_street_1 - nvarchar(1000)
33.2.9. 9. address_street_2 - varchar(1000)
33.2.10. 10. address_street_3 - varchar(1000)
33.2.11. 11. city (input) - nvarchar(255)
33.2.12. 12. state_id - guid
33.2.13. 13. province_id - guid
33.2.14. 14. postal_code - nvarchar(10)
33.2.15. 15. country_id - guid
33.2.16. 16. lat - nvarchar(50)
33.2.17. 17. lng - nvarchar(50)
33.2.18. 18. industry_value
33.2.19. 19. industry_label
33.2.20. 20. sic_code - nvarchar(20)
33.2.21. 21. description - nvarchar(1000)
33.2.22. 22. annual_revenue - money
33.2.23. 23. number_of_employees - int
33.2.24. 24. owner - guid
33.2.25. 25. owner_type - smallint
33.2.25.1. 1: user
33.2.25.1.1. 2: team
33.2.26. 26. primary_contact_id - guid
33.2.27. 27. email - nvarchar(255)
33.2.28. 28. business_phone - nvarchar(50)
33.2.29. 29. lead_id - guid
33.2.30. 30. last_campaign_date - bigint
33.2.31. 31. marketing_materials - bit
33.2.32. 32. contact_method_value - bigint
33.2.33. 33. contact_method_label - nvarchar(300)
33.2.34. 34. payment_term_value - bigint
33.2.35. 35. payment_term_label - nvarchar(255)
33.2.36. 36. shipping_method_value - bigint
33.2.37. 37. shipping_method_label - nvarchar(255)
33.2.38. 38. allow_send_email - bit
33.2.39. 39. allow_follow_email - bit
33.2.40. 40. allow_bulk_email - bit
33.2.41. 41. allow_phone - bit
33.2.42. 42. allow_fax - bit
33.2.43. 43. allow_mail - bit
33.2.44. 44. currency_id - guid
33.2.45. 45. credit_limit - money
33.2.46. 46. credit_hold - bit
33.2.47. 49. follow - bit
33.3. ain_account_contacts
33.3.1. account_id
33.3.2. contact_id
33.4. ain_account_opportunities
33.4.1. account_id
33.4.2. oppotunity_id
33.5. ain_account_cases
33.5.1. account_id
33.5.2. case_id
34. Country
34.1. provinces
34.1.1. id
34.1.2. name
34.1.3. code
34.1.4. lat
34.1.5. long
34.1.6. status
34.1.6.1. active
34.1.6.2. deactive
34.1.7. country_id
34.2. wards
34.2.1. id
34.2.2. name
34.2.3. status
34.2.4. district_id
34.3. districts
34.3.1. id
34.3.2. name
34.3.3. status
34.3.3.1. active
34.3.3.2. deactive
34.3.4. province_id
34.4. states
34.4.1. id
34.4.2. name
34.4.3. code
34.4.4. lat
34.4.5. long
34.4.6. status
34.4.6.1. active
34.4.6.2. deactive
34.4.7. country_id
34.5. countries
34.5.1. id
34.5.2. name
34.5.3. code
34.5.4. lat
34.5.5. long
34.5.6. status
34.5.6.1. active
34.5.6.2. deactive
34.5.7. is_has_state
34.5.8. is_has_province
34.6. sites
34.6.1. name
34.6.2. main_phone
34.6.3. other_phone
34.6.4. fax
34.6.5. timezone
34.6.6. address_street_1
34.6.7. address_street_2
34.6.8. address_street_3
34.6.9. city (input)
34.6.10. state/province
34.6.11. country_id
34.6.12. postcode
34.6.13. status
34.6.13.1. active
34.6.13.2. deactive
34.6.14. email
34.6.15. id
35. Currencies
35.1. currencies
35.1.1. currency_master_data_id
35.1.2. id
35.1.3. exchange_rate
35.2. currency_master_data
35.2.1. id
35.2.2. country
35.2.3. code
35.2.4. name
35.2.5. symbol
35.2.6. currency_precision
36. Organization
36.1. positions
36.1.1. id
36.1.2. name
36.1.3. parent_id
36.1.4. description
36.1.5. status
36.1.5.1. active
36.1.5.2. deactive
36.2. companies
36.2.1. id
36.2.2. name
36.2.3. address
36.2.4. prefer_address
36.2.5. other_address
36.2.6. main_phone
36.2.7. fax
36.2.8. status
36.2.8.1. active
36.2.8.2. deactive
36.3. user_profiles
36.3.1. id
36.3.2. username
36.3.3. fullname
36.3.4. title
36.3.5. primary_email
36.3.6. mobile_phone
36.3.7. main_phone
36.3.8. home_phone
36.3.9. order_phone
36.3.10. second_email
36.3.11. prefered_phone
36.3.12. fax
36.3.13. avatar
36.3.13.1. scale image?
36.3.14. address
36.3.15. prefer_address
36.3.16. other_address
36.3.17. company_id
36.3.18. sites_id
36.3.19. business_unit_id
36.3.20. manager_id
36.3.21. position_id
36.3.22. *teams: save to list/json, reference to table TEAM
36.3.23. *roles: save to list/json, reference to table ROLE
36.3.24. last_login_on
36.3.25. status
36.3.25.1. active
36.3.25.2. deactive
36.4. users
36.4.1. id
36.4.2. user_domain
36.5. teams
36.5.1. id
36.5.2. name
36.5.3. business_unit_id
36.5.4. administrator_user_id
36.5.5. description