CRM CORE

CRM Database

Get Started. It's Free
or sign up with your email address
CRM CORE by Mind Map: CRM CORE

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