/
Sage Timberline Client and Jobs Integration - Reference

Sage Timberline Client and Jobs Integration - Reference

Owned by Matthew Garrett
Last updated: Aug 27, 2021
6 min read

Return to Sage Timberline Client and Jobs Integration

Article Index

Integrated Fields

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

Master_Arm_Record_1

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Field Label

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Field Label

CADD1



Yes

Yes

Special #1: client street number & client street name



CAKEY1



Yes



Fixed Value: 1



CBADD1



Yes

Yes

Special #1: client street number & client street name



CBCIY



Yes

Yes

Framework - s_client_suburb



CBEMAIL



Yes

Yes

Framework - s_client_email



CBSTATE



Yes

Yes

Framework - s_client_state



CBZIP



Yes

Yes

Framework - s_client_postcode



CCITY



Yes

Yes

Framework - s_client_suburb



CDAYDUE



Yes



Fixed Value: 7



CEMAIL



Yes

Yes

Framework - s_client_email



CESTDT



Yes



Framework - d_created



CFAX



Yes

Yes

Framework - s_client_fax



CGLPREF



Yes



Special #3: depending on the criteria method from the ini file, we get a value from the ini file like this (TLJOBPREFIX(method criteria)).



CNAME



Yes

Yes

Special #7: Client Name (job num).



CPHONE



Yes

Yes

Framework - s_client_phone



CSTATE



Yes

Yes

Framework - s_client_state



CTRADE



Yes



Fixed Value: None



CTXGR



Yes



Fixed Value: GST



CTXGRPE



Yes



Fixed Value: Taxable



CTXGRPL



Yes



Fixed Value: Taxable



CTXGRPM



Yes



Fixed Value: Taxable



CTXGRPO



Yes



Fixed Value: Taxable



CTXGRPS



Yes



Fixed Value: Taxable



CTXGRPT



Yes



Fixed Value: Taxable



CTXGRTB



Yes



Fixed Value: Taxable



CTYPEA



Yes



Fixed Value: Standard



CUST



Yes



Special #2: Gets the first four or five characters from client ref name (ini file key determines harmony) appends 2 numbers to it, adds leading 0 if needed.



CZIP



Yes

Yes

Framework - s_client_postcode



Master_Arm_Record_4

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

ATITLE



Yes

Yes

Framework s_client_name



CUCUST



Yes



Special #2: Gets the first four or five characters from client ref name (ini file key determines harmony) appends 2 numbers to it, adds leading 0 if needed. 



Master_JCM_Record_1_1

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

Sage Timberline Database Field Name

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

JACTSD



Yes

Yes

Framework - d_admin_comp_act



JADDR1



Yes

Yes

Special #5: job lot numb, job street num, job street name



JADDR2



Yes



Fixed Value: 1



JARCUST



Yes



Special #2: Gets the first four or five characters from client ref name (ini file key determines harmony) appends 2 numbers to it, adds leading 0 if needed. 



JAUTH



Yes

Yes

Framework - s_super_nameRef



JBCITY



Yes

Yes

Framework - s_client_suburb



JBSTATE



Yes

Yes

Framework - s_client_state



JBZIP



Yes

Yes

Framework - s_client_postcode



JCITY



Yes

Yes

Framework - s_job_suburb



JCNAME



Yes

Yes

Framework - s_client_name



JDESC



Yes

Yes

Special #8: job lot num, job street num, job street name (client name ref)



JESTSD



Yes

Yes

Framework - d_admin_comp_fMan



JOB



Yes



Special #6: Fwork job number formatted to the Timberline format.



JOESTFN



Yes

Yes

Special #9: if fwork d_site_start_act is entered and the timberline JOESTFN field is 0 we set this to true.



JORDER1



Yes

Yes

Framework - s_supervisor_name



JORDER2



Yes

Yes

Framework - s_hType_notes



JPREFIX



Yes



Special #3: depending on the criteri method from the ini file, we get a value from the ini file like this (TLJOBPREFIX(method criteria)).



JPRJMGR



Yes

Yes

Framework - s_administrator_name



JSTATE



Yes

Yes

Framework - s_job_state



JSTATUS



Yes

Yes

Special #4: if not = closed this will be 'in progress' if job status is active/on hold and wfl stage is construction/maintenance else it will be unstarted.



JTITL1



Yes

Yes

Framework - s_estimator_name



JTITL2



Yes

Yes

Framework - s_hFacade_name



JTYPE



Yes

Yes

Framework - s_hType_name



JZIP



Yes

Yes

Framework - s_job_postcode



JACTCD



Yes

Yes

Framework - d_settle_act



JBMETH



Yes



Fixed Value: Use Quick Bill. This value applies to using Timberline Quickbill for invoicing when not using Contracts.

Fixed Value: Use Contracts. This value applies to using Timberline Contracts



Master_JCM_Record_20

FieSage TimberlineDatabase Field Nameld

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

FieSage TimberlineDatabase Field Nameld

Sage Timberline Field Label

Create

Update

Framework Database Field Name

Framework Database Field Label

DJOBCRE



Yes



Framework - d_created



JUJOB



Yes



Special #6: Fwork job number formatted to the Timberline format.



NFWCVAP



Yes

Yes

Special #17: Sum of doc.c_retail_total whose subtype is building Var or PC Var, status is final and d_signed_act is not null.



NFWCVPE



Yes

Yes

Special #16: Sum of doc.c_retail_total whose sub type in building Var or Post Contract Variations, status is final or draft and d_signed_act is null.



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. If the profile key for setClosedStatus is 1 then we get all jobs from v_sched_tlGold1 where

    1. Job.l_wfl_stgMajor_id = -5 (Maintenance)

    2. Cont.d_settle_act = within the last 6 months. Not including the current month.

  2. We then get matching Timberline Accounts from Master_Jcm_Record_1_1 where If job.s_link_boq is empty then

    1. Master_Jcm_Record_1_1.job = job.s_job_num (formatted for timberline) else use Master_Jcm_Record_1_1.job = job.s_link_boq

    2. The records that match in Timberline will have their status set to Closed

  3. Next, get eligible Framework jobs using v_sched_tlGold1 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_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation) or -8(Cancelled) and if d_cont_cancelled >= (today - 14 days).

    6. Job.l_wfl_stgMajor_id <> -5 (Maintenance)
      OR
      Job.l_wfl_stgMinor_id = -18(Maint DLP) and d_settle_act >= (today - 140) up to 20 weeks
      or
      Job.l_wfl_stgMinor_id = -19(Maint Service) and d_dlp_expiry_act >= (today - 28) up to 4 weeks.

    7. and apply Criteria Method criteria where CriteriaMethod is

    8. 1 – Single Region Division • job.l_context_id = ‘CriteriaMethodID’

    9. 2 – Single Job • job.s_job_num = ‘CriteriaMethodID’

    10. 3 – Single Team • job.l_team_id = ‘CriteriaMethodID’

    11. 4 – All Jobs • job.l_job_id <> 0

    12. 5 – Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’

  4. For each Framework Job search for the job in Timberline table Master_Jcm_Record_1_1 for a matching record where...

    1. Master_Jcm_Record_1_1.job = job.s_job_num (formatted to match Timberlines format)
      OR 
      if s_boq_link is not empty then

    2. Master_Jcm_Record_1_1.job = job.s_link_boq

  5. If the job is found in timberline then

    1. The matching job record is retrieved from Master_Jcm_Record_1_1 where Master_Jcm_Record_1_1.job = the formatted framework job number (or link BOQ)

    2. The matching record from Master_Jcm_Record_20 is found where Master_Jcm_Record_20.juJob = the formatted framework job number (or link BOQ)

    3. The matching client record is retrieved from Master_Arm_Record_1 where Master_Arm_Record_1.cust = Master_Jcm_Record_1_1.jarcust

    4. The matching record is retrieved from Master_Arm_Record_4 where Master_Arm_Record_4.cucust = Master_Jcm_Record_1_1.jarcust

    5. If all records above are retrieved successfully we update the timberline job.

    6. After updating the job successfully, then we read from Master_JCM_Record_13 to get costs for this job where IJOB = job.s_link_boq (or if empty then job.s_job_num formatted to match Timberline)

    7. If there are records returned we then update Frameworks doc_sumJob so with costs and dates. They are updated as follows

      • The cost is read from Master_jcm_record_13 and is (IAMT + IAPPCOA) all valid currency fields will have this amount

      • Updated every time...

      • -doc_sumJob.d_poCost_Current = todays date

      • -doc_sumJob.c_pocost_Current = jcm13 calculated cost

      • IFd_admin_comp_act is empty then

      • -doc_sumJob.d_pocost_AdminComp = todays date

      • -doc_sumJob.c_pocost_adminComp = jcm13 calculated cost

      • IF d_cst_comp_act is empty then

      • -doc_sumJob.d_pocost_cstComp = todays date

      • -doc_sumJob.c_pocost_cstComp = jcm13 calculated cost

      • IF d_dlp_expiry_fman is empty then

      • -doc_sumJob.d_pocost_dlpExpiry = todays date

      • -doc_sumJob.c_pocost_dlpExpiry = jcm13 calculated cost

      • IF d_dlp_expiry_act is empty then

      • -doc_sumJob.d_pocost_dlpComplete = todays date

      • -doc_sumJob.c_pocost_dlpComplete = jcm13 calculated cost

  6. If the job is not found in timberline then

    1. If the population of field 'CUST' is not the standard default outlined earlier then we will check to ensure the client is not already entered. This saves errors if the clients have multiple jobs. If the client exists only the job is created, else both client and job is created.

    2. If the job.s_link_boq field is empty we create the job in timberline.

    3. We retrieve links to the timberline tables so we can add the new job and customer records. If we can successfully retrieve the links we create the new records.