Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

Integration Profile Configuration

The following outlines the settings for Databuild Client and Jobs integration .ini file profiles.

Default initialisation filename: 

  • Int_DatabuildAccounts_Default.ini

Settings

Key

Description/Setting

IntegrationType

Description: 

Specifies the type of integration to be performed. This is a fixed value that should not be changed.

Setting: 

Databuild Accounts

ODBCConnectionType

Description: 

Indicates if the Databuild database is SQL Server or Microsoft Access.

Setting: 

1: SQL Server

2: Microsoft Access

Databases

Key

Description/Setting

ODBCDSN_Framework

Description:

ODBC DSN name for the Framework database.

Setting: 

FworkSQLEcm

ODBCConnect_Framework

Description:

ODBC DSN connection string for the Framework database.

Setting: 

None

ODBCDSN_FrameworkLicense

Description:

ODBC DSN name for the Framework Licence database.

Setting:

FworkSQLLic

ODBCDSN_IntMapping

Description:

ODBC DSN name for the Framework Integration Mapping database.

Setting: 

FworkSQLIntMap

ODBCConnect_IntMapping

Description:

ODBC DSN connection string for the Framework Integration Mapping database.

Setting: 

None

ODBCDSN_DataBuildSys

Description:

ODBC DSN name for the Databuild system database.

Setting:

FworkDBuildSys

DatabaseName_DataBuildSys

Description:

Databuild system database name.

Setting:

DataBuildSys

ODBCConnect_DataBuildSys

Description:

ODBC DSN connection string for the Databuild System database.

Setting:

"uid=enterUserName;pwd=enterPassword"

ODBCDSN_DataBuildJob

Description:

ODBC DSN name of the Databuild Job database.

Setting:

FworkDBuildJob

DatabaseName_DataBuildJob

Description:

Databuild Job database name.

Setting:

DataBuildJob

ODBCConnect_DataBuildJob

Description:

ODBC DSN connection string for the Databuild Job database.

Setting:

"uid=enterUserName;pwd=enterPassword"

Mail Configuration

Key

Description/Setting

Mail_System

Description:

Email delivery method.

Setting: 

0: no email will be sent.

1: email will be sent using MAPI.

2 (default): email will be sent using SMTP.

Mapi_Profile

Description:

Microsoft Outlook user profile name.

Setting:

(default): Outlook

Mail_From

Description:

The name or email address of who/where the email is being sent from.

Setting:

FrameworkIntegration

Mail_Recipient

Description:

These are the mail recipients of integration logs. Multiple addresses can be added if separated with a semi-colon (;). Spaces are not necessary before or after the semi-colon.

Setting:

enterRecipientEmail@yourDomain.com.au;integrationsupport@insulagroup.com.au

Mail_Subject

Description:

Email subject heading.

Setting:

"Framework Integration : Databuild Accounts"

SMTP_Server

Description:

If using Mail_System 2 (SMTP), add the SMTP Server name.

Setting:

enterSMTPServerName (replace this text with your SMTP Server name)

SMTP_UseSecurity

Description:

Is security used for the STMP server?

Setting:

0 (default): A username or password is not required.

1: A username and password is required. If this setting is used, the SMTP_UserName and SMTP_Pwd fields must have a valid username and password.

SMTP_UserName

Description:

A valid username for the SMTP server.

Setting:

enterUserName (replace this text with a valid SMTP Server User Name)

SMTP_PWD

Description:

A valid password for the SMTP username.

Setting:

enterPassword (replace this text with a valid SMTP Server Password)

Criteria

Key

Description/Setting

CriteriaMethod

Description:

Job selection criteria for retrieving records from Framework to process.

Setting:

1: Single region/division.

2: Single job.

3: Single team.

4 (default): All jobs.

5: Accounts entity.

CriteriaData

Description:

Input criteria data for the criteria method.

Setting:

insert number; for example, if using CriteriaMethod 2, the setting for CriteriaData will be the specific job number to integrate on.

Special Values

Key

Description/Setting

dBuild_ContactGroupCode

Description:

Code for the Databuild Contact group.

Setting:

2 (default): Databuild value that indicates the Contact group.

dBuild_GstRate

Description:

GST percentage rate.

Setting:

10 (default)

dBuild_CostCentreGroupCode

Description:

Code for the Databuild Cost Centre group.

Setting:

1 (default): Databuild value that indicates the Cost Centre group.

Actions

Key

Description/Setting

SaveClientLink

Description:

Determines if we add a reference to the Databuild customer in Framework's client record.

Setting:

0: Do not link the client. Each subsequent job will have a new customer record in Databuild.

1: Link the client Framework to the matching Databuild customer.

Databuild Settings

Key

Description/Setting

DBccBanks(-1)

Description:

n/a

Setting:

"N/A"

DBstatus(-1)

Description:

n/a

Setting:

"N/A"

Integrated Fields

The integration of Databuild Client and Jobs to Framework ECM is fully customisable via the integration mapping database.

Dbo.Contacts

Databuild Database Field Name

Databuild Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Address

 

Yes

Yes

Special #1: Street Number and Street Name

 

City

 

Yes

Yes

Framework - s_client_suburb

 

Code

 

Yes

 

Special #14: Framework Client Ref Name shortened to 4 Uppercase characters with two numbers added on the end making a unique code.

 

Contact

 

Yes

Yes

Framework - s_clientCont_name

 

Dear

 

Yes

Yes

Framework - s_clientCont_salutation

 

Debtor

 

Yes

 

Fixed Value: -1

 

Email

 

Yes

Yes

Framework - s_client_email

 

Fax

 

Yes

Yes

Framework - s_client_fax

 

Group_

 

Yes

Yes

Special #10: Value from ini file for key dbuild_ContactGroupCode

 

Mobile

 

Yes

Yes

Framework - s_client_mobile

 

Name

 

Yes

Yes

Framework - s_client_name

 

Phone

 

Yes

Yes

Framework - s_client_phone

 

Postcode

 

Yes

Yes

Special #15: Framework - s_client_postcode

 

Sort

 

Yes

Yes

Special #2: Client reference name shortened to 4 characters

 

State

 

Yes

Yes

Framework - s_client_state

 

 Dbo.Jobs

Databuild Database Field Name

Databuild Field Label

Create

Update

Framework Database Field Name

Framework Field Label

AnyQuantities

 

Yes

 

Fixed Value: 0

 

CCBank

 

Yes

Yes

Special #11: Value from ini file for key dbuild_CostCentreGroupCode

 

ContractGST

 

Yes

Yes

Special #13: GST Rate is retrieved from ini file and the GST amount is calculated based on c_retail_external (total price inc GST) from Framework.

 

ContractPrice

 

Yes

Yes

Special #12: GST Rate is retrieved from ini file and the Net amount is c_retail_external minus GST amount calculated above.

 

Debtor

 

Yes

 

Special #14: Same as above for 14 (code).

 

DebtorName

 

Yes

Yes

Framework - s_client_name

 

EndDate

 

Yes

Yes

Framework - d_antComp_rev_fman

 

Estimator

 

Yes

Yes

Framework - s_estimator_name

 

Forecast

 

Yes

Yes

Special #12: GST Rate is retrieved from ini file and the Net amount is c_retail_external minus GST amount calculated above.

 

GST

 

Yes

 

Fixed Value: 0

 

Job_No

 

Yes

 

Special #6: Framework Job Number shortened to 8 characters.

 

JobPostCode

 

Yes

Yes

Special #15: fwork s_job_postcode

 

LiveOrders

 

Yes

 

Fixed Value: 0

 

LockBudgets

 

Yes

 

Fixed Value: 0

 

PreventBill

 

Yes

 

Fixed Value: 0

 

PreventPosting

 

Yes

 

Fixed Value: 0

 

Salesperson

 

Yes

Yes

Framework - s_salesPerson_name

 

ScheduleProfile

 

Yes

Yes

Framework - s_hType_name

 

Site_Address

 

Yes

Yes

Special #5: lot num, street num, street name suburbName, stateName

 

StartDate

 

Yes

Yes

Framework - d_site_start_act

 

Supervisor

 

Yes

Yes

Framework - s_supervisor_name

 

Database Models

The following database model(s) displays the manner in which data is integrated between the third party application and Framework Integration.


Integration Process

The following information is a low level account of the integration process including criteria, decisions, and outcomes. This information is technical in nature and is provided "as-is" for a detailed analysis of the integration process for system administrators.

  1. Get eligible Framework jobs using v_sched_dbJob where
    1. Client.s_name_ref is not null
    2. Client.s_name is not null
    3. Job.s_link_boq <> ‘N/A’
    4. Job.s_link_accts <> ‘N/A’
    5. Job.l_wfl_stgMajor_id <> =5(Maintenance)
    6. Job. l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)
    7. and apply Criteria Method criteria where CriteriaMethod is
    8. 1 – Single Region Division
    9. job.l_context_id = ‘CriteriaMethodID’
    10. 2 – Single Job
    11. job.s_job_num = ‘CriteriaMethodID’
    12. 3 – Single Team
    13. job.l_team_id = ‘CriteriaMethodID’
    14. 4 – All Jobs
    15. job.l_job_id <> 0
    16. 5 – Accounts Entity
    17. job.l_accounts_e_id = ‘CriteriaMethodID’
  2. For each Framework Job search for the job in Databuild table jobs where
    1. If job.s_link_boq is empty then
    2. Jobs.job_no = job.s_job_num else
    3. Jobs.job_no = job.s_link_boq
  3. If the job is found in databuild then
    1. The matching job record is retrieved from jobs
    2. The matching client record is retrieved from contacts where contacts.code = jobs.debtor
    3. If both records above are retrieved successfully we update the databuild job.
    4. If there are records returned we then update Frameworks doc_sumJob so with costs and dates. They are updated as follows:
    5. The cost is read from dbo.Orders and is (sum the OrderValue where the Bload is greater than 0) all valid currency fields will have this amount
    6. Updated every time:
      • doc_sumJob.d_poCost_Current = todays date
      • doc_sumJob.c_pocost_Current = dbo.Orders calculated cost
      • IF d_admin_comp_act is empty then
      • doc_sumJob.d_pocost_AdminComp = todays date
      • doc_sumJob.c_pocost_adminComp = dbo.Orders calculated cost
      • IF d_cst_comp_act is empty then
      • doc_sumJob.d_pocost_cstComp = todays date
      • doc_sumJob.c_pocost_cstComp = dbo.Orders calculated cost
      • IF d_dlp_expiry_fman is empty then
      • doc_sumJob.d_pocost_dlpExpiry = todays date
      • doc_sumJob.c_pocost_dlpExpiry = dbo.Orders calculated cost
      • IF d_dlp_expiry_act is empty then
      • doc_sumJob.d_pocost_dlpComplete = todays date
      • doc_sumJob.c_pocost_dlpComplete = dbo.Orders calculated cost
  4. If the job is not found in databuild then
    1. If the job.s_link_boq field is empty we create the job in databuild.
    2. The framework job.s_job_num value is checked. It must be <= 7 characters for us to be able to create the record.
    3. We retrieve a link to the jobs table so we can add the new job
    4. If we have a value in framework client.s_link_accts then we try to find the matching client record where contacts.code = client.s_link_accts. Otherwise we retrieve a link to the contacts table so we can add a new client. If there is a value in client.s_link_accts but we can’t find the matching record in contacts, then we retrieve a link to the contacts table so we can add the record as a new client.
    5. If both tables are accessed successfully then we create the databuild job and client.


  • No labels