1. PHRASE 2
1.1. Marketing
2. Products
2.1. ain_units
2.1.1. name - n - 255
2.1.2. quantity - decimal(18,5)
2.1.3. parent_id : guid
2.1.4. unit_group_id - guid
2.2. ain_unit_groups
2.2.1. name - n - 255
2.3. ain_notes
2.3.1. entity_id
2.3.2. entity_record_id
2.3.3. entity_type
2.3.4. title - n - 255
2.3.5. content - n - 2000
2.3.6. attachment - n - 1000 (url file)
2.4. ain_products
2.4.1. name - n - 255
2.4.2. product_code -n - 255
2.4.3. parent_id - guid
2.4.4. valid_from - bigint
2.4.5. valid_to - bigint
2.4.6. description - n - 2000
2.4.7. unit_group_id - guid
2.4.8. unit_id - guid
2.4.9. price_list_id - guid
2.4.10. decimal_supported - byte
2.4.11. subject - smallint (FE hardcode)
2.4.12. status_label
2.4.13. status_value
2.4.14. product_structure_value : (1: Product Family, 2: Product, 3: Product Bundle)
2.4.15. product_structure_label
2.5. ain_properties
2.5.1. name -n 255
2.5.2. read_only - bit
2.5.3. required - bit
2.5.4. hidden - bit
2.5.5. regarding_product_id
2.5.6. decription - n 2000
2.5.7. datatype_value - smallint
2.5.8. datatype_label
2.5.9. min_value: decimal
2.5.10. max_value:decimal
2.5.11. default_value: decimal
2.5.12. precision:smallint
2.5.13. text_default: n 1000
2.5.14. max_length: smallint
2.6. ain_property_optionset
2.6.1. property_id
2.6.2. name
2.6.3. value
2.6.4. description: n 2000
2.6.5. is_default: bit
2.7. ain_product_properties
2.7.1. product_id
2.7.2. property_id
2.8. ain_territories
2.8.1. state_id
2.8.2. city_id
2.8.3. province_id
2.8.4. name
2.9. ain_price_list_territories
2.9.1. price_list_id
2.9.2. territory_id
2.10. ain_discount
2.10.1. name
2.10.2. discount_type_value
2.10.3. discount_type_label
2.10.4. currency_id
2.10.5. description: n 2000
2.11. ain_discount_details
2.11.1. begin_quantity: decimal
2.11.2. end_quantity: decimal
2.11.3. amount: money
2.11.4. percent: decimal
3. Loyalty
3.1. ain_loyalty_level_points
3.1.1. name
3.1.2. point_type_value
3.1.3. point_type_label: Earn Point
3.1.4. currency_id
3.1.5. amount: money
3.1.6. point: int
3.2. ain_loyalty_cycles
3.2.1. name
3.2.2. from_date: bigint
3.2.3. to_date: bigint
3.2.4. extend_expired_day: int (extend to_date)
3.2.5. owner_id
3.2.6. owner_type
3.3. ain_loyalty_levels
3.3.1. name
3.3.2. sequence: int
3.3.3. start_point: int
3.3.4. end_point: int
3.4. ain_loyalty_programs
3.4.1. name
3.4.2. from_date
3.4.3. to_date
3.4.4. level_point_id
3.4.5. promotion_type_value
3.4.6. promotion_type_label: (invoice/birthday/special)
3.4.7. loyalty_applied_value
3.4.8. loyalty_applied_label
3.4.9. product_applied_value
3.4.10. product_applied_label : (missing when design)
3.4.11. bonus_point_type_value
3.4.12. bonus_point_type_label: (Plus -- Point / Double Point)
3.4.13. bonus_point: int
3.4.14. owner_id
3.4.15. owner_type
3.4.16. status_value
3.4.17. status_label
3.5. ain_loyalty_points
3.5.1. contact_id
3.5.1.1. joined_date - bigint
3.5.1.2. earn_point - int
3.5.1.3. redemption_point - int
3.5.1.4. bonus_point - int
3.5.1.5. level - int
3.5.1.6. cycle_point_start - bigint
3.5.1.7. cycle_point_end - bigint
3.5.1.8. level_point - int
3.5.1.9. balance_point - int
3.5.1.10. expired_date - bigint
3.5.1.11. previous_cycle_point_start - bigint
3.5.1.12. previous_cycle_point_end - bigint
3.5.1.13. previous_earn_point - int
3.5.1.14. previous_bonus_point - int
3.5.1.15. previous_redemption_point - int
3.5.1.16. previous_balance_point - int
3.6. ain_loyalty_transactions
3.6.1. loyalty_point_id
3.6.2. invoice_id
3.6.3. transaction_date
3.6.4. invoice_name
3.6.5. invoice_number: (invoice_code in ain_invoices)
3.6.6. earn_point - int
3.6.7. bonus_point int
3.6.8. redemption_point - int
3.6.9. balance_point - int
3.6.10. previous_point: int
3.6.11. level_name
3.6.12. loyalty_cycle_id: (for calculate loyalty_point when a cycle is end)
3.6.13. description
3.6.14. contact_id
3.7. ain_loyalty_promotion_rules
3.7.1. loyalty_program_id
3.7.2. name
3.7.3. from_date
3.7.4. to_date
3.7.5. marketing_list_id
3.7.6. apply_for_category_product_value
3.7.7. apply_for_category_product_label
3.7.8. product_applied_value
3.7.9. product_applied_label
3.7.10. category_applied_value
3.7.11. category_applied_label
3.7.12. applied_amount_quantity_label
3.7.13. applied_amount_quantity_value
3.7.14. amount_from : money (instead of invoice_amount_from )
3.7.15. amount_to : money
3.7.16. quantity_from
3.7.17. quantity_to
3.7.18. CURRENCY : not column - get from Loyalty Program > Level Point
3.8. ain_loyalty_promotion_rule_products
3.8.1. loyalty_promotion_rule_id
3.8.2. product_id
3.9. ain_loyalty_promotion_rule_categories
3.9.1. loyalty_promotion_rule_id
3.9.2. category_id
4. Sales Literature
4.1. ain_sale_literatures
4.1.1. title
4.1.2. subject: smallint (FE hardcode)
4.1.3. type_value
4.1.4. type_label
4.1.5. description
4.1.6. expiration_date: bigint
4.1.7. employee_contact_id: (User)
4.2. ain_sale_attachments
4.2.1. title
4.2.2. author
4.2.3. keywords
4.2.4. abstract: 2000
4.2.5. file_name
4.2.6. file_size
4.2.7. file_path
4.2.8. sale_literature_id PK
4.2.9. status_value
4.2.10. status_label
4.3. ain_sale_literature_products
4.3.1. sale_literature_id
4.3.2. product_id
4.4. ain_sale_literature_competitors
4.4.1. sale_literature_id
4.4.2. competitor_id
5. Competitors
5.1. ain_opportunity_competitors
5.1.1. opportunity_id
5.1.2. competitor_id
5.2. ain_lead_competitors
5.2.1. lead_id
5.2.2. competitor_id
5.3. ain_competitors
5.3.1. name - n - 255
5.3.2. reported_revenue - money
5.3.3. website -n - 255
5.3.4. currency_id
5.3.5. address : same table account
5.3.6. strength -n - 2000
5.3.7. weakness - n - 2000
5.3.8. ticker_symbol -n - 50
6. StackHolders
6.1. ain_lead_stackholders
6.1.1. lead_id
6.2. ain_opportunity_stackholders
6.2.1. opportunity_id
7. Opportunities
7.1. ain_opportunities
7.1.1. topic - n - 255
7.1.2. contact_id
7.1.3. account_id
7.1.4. purchase_time_frame_label
7.1.5. purchase_time_frame_value
7.1.6. currency_id
7.1.7. budget_amount - money
7.1.8. purchase_process_label
7.1.9. purchase_process_value
7.1.10. description
7.1.11. current_situation - n - 2000
7.1.12. customer_need - n - 2000
7.1.13. proposed_solution -n - 2000
7.1.14. price_list_id
7.1.15. revenue_label
7.1.16. revenue_value
7.1.17. detail_amount - money
7.1.18. discount_percent - decimal
7.1.19. discount - money
7.1.20. pre_freight_amount - money
7.1.21. preight_amount: money
7.1.22. total_tax - money
7.1.23. total_amount - money
7.1.24. est_close_date - bigint
7.1.25. est_revenue - money
7.1.26. owner_type
7.1.27. owner_id
7.1.28. status_label
7.1.29. status_value
7.2. ain_opportunity_saleteams
7.2.1. opportunity_id
7.2.2. user_id
7.2.3. sale_role_label
7.2.4. sale_role_value
7.3. ain_opportunity_products
7.3.1. opportunity_id
7.3.2. product_id
7.3.3. price_per_unit: money
7.3.4. quantity: int
7.3.5. discount: money
7.4. ain_price_list
7.4.1. name
7.4.2. start_date: bigint
7.4.3. end_date: bigint
7.4.4. currency_id
7.4.5. description: n 2000
7.5. ain_price_list_items
7.5.1. price_list_id
7.5.2. product_id
7.5.3. unit_id
7.5.4. currency_id
7.5.5. discount_id
7.5.6. quantity_selling_value
7.5.7. quantity_selling_label
7.5.8. pricing_method_value
7.5.9. pricing_method_label
7.5.10. amount: money
7.5.11. percentage: decimal
7.5.12. rounding_policy_value
7.5.13. rounding_policy_label
7.5.14. rounding_amount: money
7.6. ain_opportunity_quotes
7.6.1. opportunity_id
7.6.2. quote_id
7.6.3. name
7.6.4. status_value
7.6.5. status_label
7.6.6. total_amount: money
7.6.7. effect_from: bigint
7.6.8. effect_to: bigint
8. Orders
8.1. ain_orders
8.1.1. order_code: n 20
8.1.2. name
8.1.3. currency_id
8.1.4. price_list_id
8.1.5. prices_locked: bit
8.1.6. requested_delivery: bigint
8.1.7. date_fulfilled: bigint
8.1.8. shipping_method_value
8.1.9. shipping_method_label
8.1.10. payment_terms_value
8.1.11. payment_terms_label
8.1.12. bill_to_address: same account address -7 fields
8.1.13. ship_to_value
8.1.14. ship_to_label
8.1.15. ship_to_address: same bill_to_address above
8.1.16. detail_amount: same detail amount opportunity - 7 fields
8.1.17. total_amount: money
8.1.18. opportunity_id
8.1.19. quotes_id
8.1.20. potential_entity_id
8.1.21. potential_entity_type
8.1.22. potential_record_id
8.1.23. description
8.1.24. order_status_label
8.1.25. order_status_value
8.1.26. owner_id
8.1.27. owner_type
8.2. ain_order_products
8.2.1. orders_id
8.2.2. product_id
8.2.3. unit_id
8.2.4. price_per_unit: money
8.2.5. quantity: decimal
8.2.6. discount: money
8.2.7. extend_amount: money
9. Invoices
9.1. ain_invoices
9.1.1. invoice_code: n 20
9.1.2. name
9.1.3. currency_id
9.1.4. price_list_id
9.1.5. prices_locked: bit
9.1.6. date_delivery: bigint
9.1.7. due_date: bigint
9.1.8. shipping_method_value
9.1.9. shipping_method_label
9.1.10. payment_terms_value
9.1.11. payment_terms_label
9.1.12. ADDRESS : same quotes
9.1.13. SHIP_TO: same quotes
9.1.14. DETAIL_AMOUNT: same quotes
9.1.15. opportunity_id
9.1.16. order_id
9.1.17. potential_entity_id
9.1.18. potential_entity_type
9.1.19. potential_record_id
9.1.20. description
9.1.21. total_amount: money
9.1.22. invoice_status_label
9.1.23. invoice_status_value
9.1.24. status_reason_label
9.1.25. status_reason_value
9.1.26. owner_id
9.1.27. owner_type
10. Quotes
10.1. ain_quotes
10.1.1. quote_code: n 20
10.1.2. revision_id: int
10.1.3. name
10.1.4. currency_id
10.1.5. price_list_id
10.1.6. shipping_method_value
10.1.7. shipping_method_label
10.1.8. payment_terms_value
10.1.9. payment_terms_label
10.1.10. bill_to_address: same account address - 7 fields
10.1.11. ship_to_value
10.1.12. ship_to_label
10.1.13. ship_to_address: same bill_to_address above
10.1.14. detail_amount: same detail amount opportunity - 7 fields
10.1.15. total_amount: money
10.1.16. effective_from: bigint
10.1.17. effective_to: bigint
10.1.18. owner_id
10.1.19. owner_type: smallint
10.1.20. opportunity_id
10.1.21. potential_entity_id
10.1.22. potential_entity_type: smallint
10.1.23. potential_entity_recored_id
10.1.24. description: n 2000
10.2. ain_quote_products
10.2.1. quotes_id
10.2.2. product_id
10.2.3. unit_id
10.2.4. price_per_unit: money
10.2.5. quantity: decimal
10.2.6. discount: money
10.2.7. extend_amount: money
11. Dynamic
11.1. ain_dynamic_pages
11.1.1. id
11.1.2. title
11.1.3. url
11.1.4. layout - ntext
11.1.4.1. Definition Json
11.1.4.1.1. [1]: Component
12. LEAD
12.1. ain_leads
12.1.1. topic - nvarchar _ 255
12.1.2. first_name - n - 255
12.1.3. last_name - n - 255
12.1.4. full_name - n - 600
12.1.5. job_title - n - 255
12.1.6. business_phone - n - 30
12.1.7. mobile_phone - n - 30
12.1.8. email - n - 255
12.1.9. company_name - n - 255
12.1.10. address: same contact
12.1.11. description - n - 2000
12.1.12. industry_value
12.1.13. industry_label
12.1.14. annual_revenue - money
12.1.15. no_of_employees - int
12.1.16. sic_code - nvarchar(20)
12.1.17. currency_id
12.1.18. campaign_id
12.1.19. marketing_material - bit
12.1.20. last_campaign_date - big int
12.1.21. prefered_method_value
12.1.22. prefered_method_label
12.1.23. allow_send_email - bit
12.1.24. allow_follow_email
12.1.25. allow_bulk_email
12.1.26. allow_phone
12.1.27. allow_mail
12.1.28. lead_source_value
12.1.29. lead_source_label
12.1.30. rating_value
12.1.31. rating_label
12.1.32. lead_status_value
12.1.33. lead_status_label
12.1.34. owner_type
12.1.35. owner_id
12.1.36. business_process_flow_id
12.1.37. stage_pending_on
12.1.38. BUSINEES FLOW: phan tich sau
12.2. ain_lead_campaigns
12.2.1. id
12.2.2. lead_id
12.2.3. campaign_id
13. BUSINESS FLOW
13.1. ain_business_process_flows
13.1.1. entity_id
13.1.2. name - n - 255
13.2. ain_business_process_stages
13.2.1. business_process_flow_id
13.2.2. name
13.2.3. stage_order - int
13.2.3.1. stage 1,2,3,4...
13.2.4. description
13.3. ain_business_process_steps
13.3.1. business_process_stage_id
13.3.2. entity_field_id
13.3.3. is_required - bit
13.4. ain_business_process_trackings
13.4.1. entity_id
13.4.2. business_process_flow_id
13.4.3. business_process_stage_id
13.4.4. entity_record_id
13.4.5. name_entity
14. CASES
14.1. ain_cases
14.1.1. customer_entity_id
14.1.2. customer_entity_type
14.1.3. customer_entity_record_id
14.1.4. title - n - 255
14.1.5. subject - smallint (hard code FE)
14.1.6. case_type_value
14.1.7. case_type_label
14.1.8. contact_id
14.1.9. assign_id
14.1.10. assign_type
14.1.11. parent_id
14.1.12. origin_value
14.1.13. origin_label
14.1.14. product_id
14.1.15. entitlement_id
14.1.16. first_response_by_id
14.1.17. first_response_by_type
14.1.18. resolve_by_id
14.1.19. resolve_by_type
14.1.20. description
15. Contact
15.1. ain_contacts
15.1.1. dsads
15.1.2. full_name - nvarchar(600)
15.1.3. last_name - n - 255
15.1.4. fist_name - n - 255
15.1.5. job_title - n - 255
15.1.6. account_contact_id
15.1.7. email - n- 255
15.1.8. business_phone - n - 30
15.1.9. mobile_phone - n - 30
15.1.10. fax - n - 30
15.1.11. prefered_method_value: smallint
15.1.12. perfered_method_label - n - 255
15.1.13. address_stree_1(2)(3) - n - 1000
15.1.14. city (input ) - n - 255
15.1.15. state_id
15.1.16. province_id
15.1.17. country_id
15.1.18. postal_code - n - 10
15.1.19. lat - n - 50
15.1.20. lng - n - 50
15.1.21. company_record_id
15.1.22. company_entity_id
15.1.23. company_entity_type
15.1.24. gender_value
15.1.25. gender_label
15.1.26. marital_status_label
15.1.27. marital_status_value
15.1.28. partner_name - n - 255
15.1.29. birthday: bigint
15.1.30. anniversary: bigint
15.1.31. notes - n - 2000
15.1.32. original_lead_entity_id
15.1.33. original_lead_entity_type
15.1.34. original_lead_record_id
15.1.35. last_campaign_date: bigint
15.1.36. marketing_material: bit
15.1.37. currency_id
15.1.38. credit_limit :money
15.1.39. credit_hold: bit
15.1.40. payment_terms_value
15.1.41. payment_terms_label
15.1.42. shipping_method_value
15.1.43. shipping_method_label
15.1.44. contact_method_value
15.1.45. contact_method_label
15.1.46. allow_send_email - bit
15.1.47. allow_follow_email - bit
15.1.48. allow_bulk_email - bit
15.1.49. allow_phone - bit
15.1.50. allow_fax - bit
15.1.51. allow_mail - bit
15.1.52. owner_id
15.1.53. owner_type - smallint
15.1.54. follow - bit
15.1.55. lead_id
15.2. ain_contact_cases
15.2.1. ain_contact_id
15.2.2. ain_case_id
15.3. ain_contact_opportunities
15.3.1. ain_contact_id
15.3.2. ain_opportunity_id
15.4. ain_contact_entitilements
15.4.1. ain_contact_id
15.4.2. ain_entitilement_id
16. Menus
16.1. ain_menu_entities
16.1.1. menu_id
16.1.2. entity_id
16.2. ain_menus
16.2.1. id
16.2.2. name
16.2.3. url
16.2.4. order_by
16.2.5. menu_type
16.2.5.1. 1: modules
16.2.5.2. 2: group
16.2.5.3. 3: function
16.2.6. parent_id
16.2.7. icon
17. Options Set
17.1. ain_option_set_fields
17.1.1. id
17.1.2. option_set_pool_id
17.1.3. entity_field_id
17.1.4. entity_id
17.2. ain_option_set_pools
17.2.1. id
17.2.2. label - nvarchar (255)
17.2.3. value - bigint
17.2.4. order_by - smallint
18. Entities
18.1. ain_entity_fields
18.1.1. id
18.1.2. display_name
18.1.3. name
18.1.4. requirement_value
18.1.5. requirement_label
18.1.6. is_searchable - bit
18.1.7. field_security - bit
18.1.8. auditing - bit
18.1.9. description - nvarchar(2000)
18.1.10. data_type - smallint
18.1.10.1. single line of text
18.1.10.2. option set
18.1.10.3. two options
18.1.10.4. images
18.1.10.5. whole number
18.1.10.6. floating point number
18.1.10.7. decimal number
18.1.10.8. currency
18.1.10.9. multiple lines of text
18.1.10.10. date and time
18.1.10.11. lookup
18.1.11. min_value
18.1.12. max_value
18.1.13. format - smallint
18.1.13.1. text
18.1.13.2. email
18.1.13.3. text area
18.1.13.4. url
18.1.13.5. ticket symbol
18.1.13.6. phone
18.1.14. maximum_length
18.1.15. entity_id - guid
18.2. ain_entities
18.2.1. id
18.2.2. display_name - nvarchar(255)
18.2.3. plural_name - nvarchar
18.2.4. name_in_db
18.2.4.1. default: ain_
18.2.5. icon
18.2.5.1. upload file
18.2.6. owner_type - smallint
18.2.6.1. 1: user or team
18.2.6.2. 2: organization
18.2.7. description - nvarchar(2000)
18.2.8. business_process_flow - bit
18.2.9. note - bit
18.2.10. activities - bit
18.2.11. connections - bit
18.2.12. sending_email - bit
18.2.13. document_management - bit
18.2.14. knowledge_management - bit
18.2.15. enable_for_sla - bit
18.2.16. allow_quick_create - bit
18.2.17. duplicate_detection - bit
18.2.18. auditing - bit
18.2.19. is_system - bit
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. Service
20.1. ain_case_sla_details
20.1.1. name
20.1.2. case_id
20.1.3. sla_detail_id
20.1.4. failure_after: bigint (minutes)
20.1.5. warn_after: bigint (minutes)
20.1.6. is_pause: bit
20.1.7. business_process_flow_id
20.1.8. business_process_stage_id
20.2. ain_case_similars
20.2.1. case_id
20.2.2. case_similar_id
20.3. ain_case_mergeds
20.3.1. case_id
20.3.2. case_merged_id
20.4. ain_sla_details
20.4.1. name
20.4.2. service_level_agreement_id
20.4.3. subject_value
20.4.4. subject_label
20.4.5. priority_value
20.4.6. priority_label
20.4.7. business_process_flow_id
20.4.8. business_process_stage_id
20.4.9. warn_after: bigint (miliseconds)
20.4.10. failure_after: bigint (miliseconds)
20.5. ain_contracts
20.5.1. contract_code
20.5.2. contract_name
20.5.3. customer_record_id
20.5.4. customer_entity_id
20.5.5. customer_entity_type
20.5.6. CONTACT_ADDRESS: same with accoutnts
20.5.7. start_date
20.5.8. end_date
20.5.9. duration_int_days: int
20.5.10. discount_type_value
20.5.11. discount_type_label
20.5.12. service_level_value
20.5.13. service_level_label
20.5.14. originating_contract_id: (action contract renew)
20.5.15. owner_id
20.5.16. owner_type
20.5.17. description: 2000
20.5.18. bill_to_customer_record_id
20.5.19. bill_to_customer_entity_id
20.5.20. bill_to_customer_entity_type
20.5.21. BILL_TO_ADDRESS (same with accounts)
20.5.22. billing_start_date
20.5.23. billing_end_date
20.5.24. billing_frequency_value
20.5.25. billing_frequency_label
20.5.26. cancellation_date: bigint
20.5.27. currency_id
20.5.28. total_price: money
20.5.29. total_discount: money
20.5.30. net_price: money
20.5.31. allotment_type_value
20.5.32. allotment_type_label
20.5.33. status_value
20.5.34. status_label
20.6. ain_knowledge_base_entities
20.6.1. entity_id
20.6.2. is_selected: bit
20.7. ain_cases
20.7.1. title - nvarchar(255)
20.7.2. case_code (auto generate)
20.7.3. subject_label
20.7.4. subject_value
20.7.5. customer_record_id
20.7.6. customer_entity_id
20.7.7. customer_entity_type
20.7.8. origin_label
20.7.9. origin_value
20.7.10. contact_id
20.7.11. entitlement_id
20.7.12. product_id
20.7.13. description
20.7.14. case_type_label
20.7.15. case_type_value
20.7.16. parent_id
20.7.17. sla_id
20.7.18. is_escalated
20.7.19. escalated_on
20.7.20. follow_up_on
20.7.21. is_first_response_sent
20.7.22. article_id
20.7.23. contract_id
20.7.24. contract_line_id
20.7.25. serial_number (nvarchar 255)
20.7.26. service_level_label
20.7.27. service_level_value
20.7.28. priority_label
20.7.29. priority_value
20.7.30. status_label
20.7.31. status_value
20.7.32. status_reason_label
20.7.33. status_reason_value
20.7.34. owner_id
20.7.35. owner_type
20.8. ain_articles
20.8.1. title nvarchar(255)
20.8.2. subject_label
20.8.3. subject_value
20.8.4. keywords
20.8.5. code
20.8.6. language_label
20.8.7. language_value
20.8.8. content - ntext
20.8.9. status_label
20.8.10. status_value
20.9. ain_article_comments
20.9.1. article_id
20.9.2. title
20.9.3. content
20.10. ain_service_level_agreements
20.10.1. name - 255
20.10.2. entity_id
20.10.3. applicable_form_label
20.10.4. applicable_from_value
20.10.5. business_hour_id: (sample data for Service Calendar)
20.10.6. sla_type_value
20.10.7. sla_type_label
20.10.8. is_allow_pause
20.10.9. description
20.10.10. status_value
20.10.11. status_label
20.10.12. owner_id
20.10.13. owner_type
20.10.14. is_default: (only 1 SLA default)
20.11. ain_flow_sla_trackings
20.11.1. entity_id
20.11.2. entity_record_id
20.11.3. entity_type
20.11.4. sla_detail_id
20.11.5. business_process_stage_id
20.11.6. first_response_on
20.11.7. resolve_on
20.11.8. warn_after: bigint (miliseconds)
20.11.9. failure_after: bigint (miliseconds)
20.11.10. pause_on
20.11.11. pause_duration_time: int (view flow image in Jira) https://jr.ainovation.com/browse/AINCRM-696
20.12. ain_sla_workdays
20.12.1. sla_workplan_id
20.12.2. week_day: tinyint (0: CN, 1:Mon, ....)
20.12.3. name: string concat from start/end/duration
20.13. ain_activities_service_customers
20.13.1. activities_service_id
20.13.2. entity_record_id
20.13.3. entity_id
20.13.4. entity_type
20.14. ain_activities_service_resources
20.14.1. activities_service_id
20.14.2. entity_record_id
20.14.3. entity_id
20.14.4. entity_type
20.15. ain_sla_working_day_details
20.15.1. sla_working_day_id
20.15.2. work_hour_from: bigint
20.15.3. work_hour_to: bigint
20.15.4. type: (1: WORKDAYS, 2: BREAK)
20.16. ain_sla_workplans
20.16.1. name
20.16.2. description
20.16.3. work_hours_value
20.16.4. work_hours_label
20.16.5. timezone_id
20.16.6. status_value
20.16.7. status_label
20.16.8. holiday_schedule_id
20.17. ain_activities_services
20.17.1. subject
20.17.2. service_id: (reference to module Service)
20.17.3. site_id
20.17.4. location
20.17.5. show_time_as_label
20.17.6. show_time_as_value
20.17.7. start_time
20.17.8. end_time
20.17.9. is_all_day_event: bit
20.17.10. duration => DELETE
20.17.11. regarding_entity_id
20.17.12. regarding_entity_record_id
20.17.13. regarding_entity_type
20.17.14. owner_id
20.17.15. owner_type
20.17.16. priority_label
20.17.17. priority_value
20.17.18. category: nvarchar
20.17.19. sub_category: nvarchar
20.17.20. status_value
20.17.21. status_label
20.17.22. status_reason_label: *** (NOT USE)
20.17.23. status_reason_value: *** (NOT USE)
20.18. ain_holidays
20.18.1. name
20.18.2. start_date
20.18.3. end_date
20.18.4. year_of_start_date: (get from start_date for display and filter)
20.19. ain_holiday_schedules
20.19.1. name
20.19.2. description
20.19.3. owner_id
20.19.4. owner_type
20.20. ain_services
20.20.1. name
20.20.2. status_reason_value
20.20.3. status_reason_label
20.20.4. duration_value
20.20.5. duration_labe
20.20.6. activities_every_value
20.20.7. activities_every_label
20.20.8. begin_at_value
20.20.9. begin_at_label
20.20.10. description
21. Accounts
21.1. ain_account_entitlements
21.1.1. account_id
21.1.2. entitlement_id
21.2. ain_account
21.2.1. 1. id (nvarchar(160))
21.2.2. 2. name (nvarchar)
21.2.3. 3. phone - nvarchar(50)
21.2.4. 4. fax - nvarchar(50)
21.2.5. 5. website - nvarchar(255)
21.2.6. 6. account_parent_id - guid
21.2.7. 7. ticket_symbol - nvarchar(10)
21.2.8. 8. address_street_1 - nvarchar(1000)
21.2.9. 9. address_street_2 - varchar(1000)
21.2.10. 10. address_street_3 - varchar(1000)
21.2.11. 11. city (input) - nvarchar(255)
21.2.12. 12. state_id - guid
21.2.13. 13. province_id - guid
21.2.14. 14. postal_code - nvarchar(10)
21.2.15. 15. country_id - guid
21.2.16. 16. lat - nvarchar(50)
21.2.17. 17. lng - nvarchar(50)
21.2.18. 18. industry_value
21.2.19. 19. industry_label
21.2.20. 20. sic_code - nvarchar(20)
21.2.21. 21. description - nvarchar(1000)
21.2.22. 22. annual_revenue - money
21.2.23. 23. number_of_employees - int
21.2.24. 24. owner - guid
21.2.25. 25. owner_type - smallint
21.2.25.1. 1: user
21.2.25.1.1. 2: team
21.2.26. 26. primary_contact_id - guid
21.2.27. 27. email - nvarchar(255)
21.2.28. 28. business_phone - nvarchar(50)
21.2.29. 29. lead_id - guid
21.2.30. 30. last_campaign_date - bigint
21.2.31. 31. marketing_materials - bit
21.2.32. 32. contact_method_value - bigint
21.2.33. 33. contact_method_label - nvarchar(300)
21.2.34. 34. payment_term_value - bigint
21.2.35. 35. payment_term_label - nvarchar(255)
21.2.36. 36. shipping_method_value - bigint
21.2.37. 37. shipping_method_label - nvarchar(255)
21.2.38. 38. allow_send_email - bit
21.2.39. 39. allow_follow_email - bit
21.2.40. 40. allow_bulk_email - bit
21.2.41. 41. allow_phone - bit
21.2.42. 42. allow_fax - bit
21.2.43. 43. allow_mail - bit
21.2.44. 44. currency_id - guid
21.2.45. 45. credit_limit - money
21.2.46. 46. credit_hold - bit
21.2.47. 49. follow - bit
21.3. ain_account_contacts
21.3.1. account_id
21.3.2. contact_id
21.4. ain_account_opportunities
21.4.1. account_id
21.4.2. oppotunity_id
21.5. ain_account_cases
21.5.1. account_id
21.5.2. case_id
22. das
23. Assign & Follows
23.1. ain_user_follows
23.1.1. user_team_id - guid
23.1.2. entity_id - guid
23.1.2.1. Account, contact...., reference to table entity
23.1.3. entity_type - smallint
23.1.3.1. user or team
23.1.4. entity_record_id - guid
23.2. ain_user_assigns
23.2.1. user__team_id - guid
23.2.2. entity_id - guid
23.2.2.1. Account, contact...., reference to table entity
23.2.3. entity_type - smallint
23.2.3.1. user or team
23.2.4. entity_record_id - guid
24. Currencies
24.1. currencies
24.1.1. currency_master_data_id
24.1.2. id
24.1.3. exchange_rate
24.2. currency_master_data
24.2.1. id
24.2.2. country
24.2.3. code
24.2.4. name
24.2.5. symbol
24.2.6. currency_precision
25. Business
25.1. business_unit _CAN REVIEW LAI COT CHUA json
25.1.1. id
25.1.2. name
25.1.3. parent_id
25.1.4. main_phone
25.1.5. other_phone
25.1.6. fax
25.1.7. email
25.1.8. website
25.1.9. bill_to_address_street_1
25.1.10. bill_to_address_street_2
25.1.11. bill_to_address_street_3
25.1.12. ship_to_address_street_1
25.1.13. ship_to_address_street_2
25.1.14. ship_to_address_street_3
25.1.15. status
25.1.15.1. active
25.1.15.2. deactive
25.1.16. team_id
25.1.17. (list_facility_equipment)
25.2. business_closure
25.2.1. id
25.2.2. name
25.2.3. start_time
25.2.4. end_time
25.2.5. duration
25.2.6. status
25.3. fiscal_year
25.3.1. id
25.3.2. fiscal_year
25.3.3. start_time
25.3.4. end_time
25.3.5. prefix
25.3.6. postfix
25.3.7. year_format (const)
25.3.7.1. YY
25.3.7.2. YYYY
25.3.8. period (const)
25.3.8.1. Q1
25.3.8.2. P1
25.3.9. display_format (const)
26. Facility Equipment
26.1. facility_equipment_detail
26.1.1. id
26.1.2. date
26.1.3. work_hour_from / work_hour_to (unixtime)
26.1.3.1. case one day
26.1.3.1.1. Minwork: 1h
26.1.3.1.2. Validate
26.1.3.1.3. Break: 15' <= time <= minwork
26.1.3.2. case same working day
26.1.3.3. case not same working day
26.1.4. type (constant)
26.1.4.1. WORKING_HOUR
26.1.4.2. BREAK_TIME
26.1.5. timezone
26.2. facility_equipments
26.2.1. id
26.2.2. name
26.2.3. site_id
26.2.4. business_unit_id
26.2.5. primary_email
26.2.6. timezone
26.2.7. description
26.2.8. status
27. Security
27.1. role_field_member (step 3)
27.1.1. id
27.1.2. role_field_id
27.1.3. user_id
27.2. security_role_field_action (step 2)
27.2.1. id
27.2.2. role_field_id
27.2.3. resource_name
27.2.4. field_name
27.2.5. permission (bitwise)
27.2.6. status
27.3. user_security_roles
27.3.1. id
27.3.2. user_id
27.3.3. security_role_id
27.4. security_role_fields (step 1)
27.4.1. id
27.4.2. name
27.4.3. status
27.5. security_role_permission_action
27.5.1. id
27.5.2. security_role_id
27.5.3. resource_name
27.5.4. entity_id
27.5.5. permission (bitwise)
27.5.6. status
27.5.6.1. active
27.5.6.2. deactive
27.6. security_roles
27.6.1. id
27.6.2. name
27.6.3. status
27.6.3.1. active
27.6.3.2. deactive
28. Common
28.1. timezones (gmt)
28.1.1. id
28.1.2. name
28.1.3. display_order
28.1.4. status
28.1.4.1. active
28.1.4.2. deactive
28.1.5. Country
28.2. ain_data_type
28.2.1. field_name
28.3. ain_form
28.3.1. id
28.3.2. entity_id : (Account, Contact)
28.3.3. name
28.3.4. fields : ntext
28.4. ain_layout
28.4.1. id
28.4.2. entity_id
28.4.3. page_type : nvarchar
28.4.4. content : ntext
29. Organization
29.1. positions
29.1.1. id
29.1.2. name
29.1.3. parent_id
29.1.4. description
29.1.5. status
29.1.5.1. active
29.1.5.2. deactive
29.2. companies
29.2.1. id
29.2.2. name
29.2.3. address
29.2.4. prefer_address
29.2.5. other_address
29.2.6. main_phone
29.2.7. fax
29.2.8. status
29.2.8.1. active
29.2.8.2. deactive
29.3. user_profiles
29.3.1. id
29.3.2. username
29.3.3. fullname
29.3.4. title
29.3.5. primary_email
29.3.6. mobile_phone
29.3.7. main_phone
29.3.8. home_phone
29.3.9. order_phone
29.3.10. second_email
29.3.11. prefered_phone
29.3.12. fax
29.3.13. avatar
29.3.13.1. scale image?
29.3.14. address
29.3.15. prefer_address
29.3.16. other_address
29.3.17. company_id
29.3.18. sites_id
29.3.19. business_unit_id
29.3.20. manager_id
29.3.21. position_id
29.3.22. *teams: save to list/json, reference to table TEAM
29.3.23. *roles: save to list/json, reference to table ROLE
29.3.24. last_login_on
29.3.25. status
29.3.25.1. active
29.3.25.2. deactive
29.4. users
29.4.1. id
29.4.2. user_domain
29.5. teams
29.5.1. id
29.5.2. name
29.5.3. business_unit_id
29.5.4. administrator_user_id
29.5.5. description
30. Not Complete
30.1. ain_account_connection
30.2. ain_industries
30.3. ain_leads
30.4. ain_entititlements
30.5. ain_marketing_lists
30.6. ain_account_childs
31. Logic Search View
31.1. ain_view_conditions
31.1.1. view_id
31.1.2. conditions
31.2. ain_views
31.2.1. name
31.2.2. scripts: n max
31.2.3. entity_id
31.2.4. type : smallint (1: viewlist, 2:detail)
31.3. ain_view_fields
31.3.1. field_name: <key>
31.3.2. view_id
31.3.3. entity_id
31.4. ain_languages
31.4.1. name
31.5. ain_language_values
31.5.1. language_id
31.5.2. key
31.5.3. value
32. Database Convention
32.1. active = 1
32.2. deactive = 0
32.3. id : guid
32.4. date: timestamp
32.5. string: nvarchar
32.5.1. email,file: nvarchar(255)
32.6. avatar: base 64
32.7. ain_crm_[tablename]
32.8. colume name: use lower case
32.9. Words are separated with underscores
33. Field MUST have
33.1. created_by
33.2. created_on
33.3. modified_by
33.4. modified_on
33.5. status
33.6. If choose soft deleted
33.6.1. deleted_by
33.6.2. deleted_on
33.6.3. is_deleted
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. Relation Ship
35.1. Sites (1) - (n) User
35.2. Company (n) - (n) Site
35.3. Business_unit_id (1) - (0..n) User
35.4. User (1) - (0..1) Position
35.5. Security_role (1) - (1) Business Unit
36. Marketing
36.1. ain_marketing_list
36.1.1. name - nvarchar(255)
36.1.2. list_type_value
36.1.3. list_type_label
36.1.4. purpose - nvarchar(500)
36.1.5. targeted_at_value - uuid
36.1.6. targeted_at_label
36.1.7. source - nvarchar(500)
36.1.8. currency_id - uuid
36.1.9. cost: money
36.1.10. owner_id
36.1.11. owner_type
36.1.12. is_locked - bit
36.1.13. description - nvarchar(2000)
36.1.14. last_used_on - bigint
36.2. ain_marketing_list_members
36.2.1. member_id - uuid
36.2.2. member_type
36.2.3. member_entity
36.3. ain_marketing_list_campaigns
36.3.1. marketing_list_id
36.3.2. campaign_id
36.4. ain_marketing_list_quick_campaigns
36.4.1. marketing_list_id
36.4.2. quick_campaign_id
36.5. ain_view_mapping_combos
36.5.1. code - nvarchar(255)
36.5.2. view_id - uuid
36.5.3. entity - nvarchar(255)
36.5.4. display: nvarchar(255)
36.6. ain_campaigns
36.6.1. name - nvarchar(255)
36.6.2. campaign_code - nvarchar(255)
36.6.3. currency_id - uuid
36.6.4. campaign_type_value
36.6.5. campaign_type_label
36.6.6. expected_response - int (0 - 100)
36.6.7. proposed_start - bigint
36.6.8. proposed_end - bigint
36.6.9. actual_start - bigint
36.6.10. actual_end - bigint
36.6.11. offer - nvarchar(2000)
36.6.12. activity_cost - money
36.6.13. misc_cost - money
36.6.14. allocated_budget - money
36.6.15. total_cost - money
36.6.16. description - nvarchar(2000)
36.6.17. owner_id
36.6.18. owner_type
36.6.19. is_template - bit
36.6.20. estimated_revenue - money
36.6.21. status_label
36.6.22. status_value
36.7. ain_campaign_responses
36.7.1. subject - nvarchar(255)
36.7.2. customer_id
36.7.3. customer_type
36.7.4. customer_entity
36.7.5. company_name - nvarchar(255)
36.7.6. last_name - nvarchar(255)
36.7.7. first_name - nvarchar(255)
36.7.8. phone - nvarchar(30)
36.7.9. email - nvarchar(255)
36.7.10. description - nvarchar(2000)
36.7.11. promotion_code - nvarchar(255)
36.7.12. related_campaign_id
36.7.13. response_code_value
36.7.14. response_code_label
36.7.15. channel_label
36.7.16. channel_value
36.7.17. outsourced_vendor_id
36.7.18. outsourced_vendor_type
36.7.19. outsourced_vendor_entity
36.7.20. owner_id
36.7.21. owner_type
36.7.22. priority_value
36.7.23. priority_label
36.7.24. received_on - bigint
36.7.25. close_on - bigint
36.7.26. status_value
36.7.27. status_label
36.8. ain_campaign_communities
36.8.1. record_id
36.8.2. campaign_id
36.8.3. type
36.8.4. entity
36.9. ain_campaign_activities
36.9.1. subject - nvarchar(255)
36.9.1.1. campaign_id
36.9.1.2. type_value
36.9.1.3. type_label
36.9.1.4. channel_value
36.9.1.5. channel_label
36.9.1.6. outsourced_vendors_id
36.9.1.7. outsourced_vendors_type
36.9.1.8. outsourced_vendors_entity
36.9.1.9. description - nvarchar(2000)
36.9.1.10. scheduled_start - bigint
36.9.1.11. scheduled_end - bigint
36.9.1.12. actual_start - bigint
36.9.1.13. actual_end - bigint
36.9.1.14. currency_id
36.9.1.15. allocated_budget - money
36.9.1.16. actual_cost - money
36.9.1.17. no_of_days - int
36.9.1.18. priority_value
36.9.1.19. priority_label
36.9.1.20. status_value
36.9.1.21. status_label
36.9.1.22. owner_id
36.9.1.23. owner_type
36.10. ain_campaign_activity_marketing_list
36.10.1. marketing_list_id
36.10.2. campaign_activity_id
36.11. ain_quick_campaigns
36.11.1. name - nvarchar(255)
36.11.2. activity_type_value
36.11.3. activity_type_label
36.11.4. owner_id
36.11.5. owner_type
36.11.6. is_mark_email - bit
36.11.7. no_of_success - int
36.11.8. no_of_failures - int
36.11.9. error_details - nvarchar(500)
36.11.10. member_type_value
36.11.11. member_type_label
36.11.12. status_reason_value
36.11.13. status_reason_label