Versions Compared

Version Old Version 1 New Version Current
Changes made by

Matthew Garrett

Matthew Garrett

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Insert excerpt
FWINT HEAD
FWINT HEAD
nopaneltrue

Return to Databuild Client and Jobs Integration

Article Index

Table of Contents

Integrated Fields

The integration of Databuild of Databuild Client and Jobs to Framework ECM is 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 and Framework Integration.Image Removed

...


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.