/
Sage Timberline Client and Jobs Integration - Reference

Sage Timberline Client and Jobs Integration - Reference

Owned by Julie Doyle-Whitelock (Deactivated)
Aug 22, 2017

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

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

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

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

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.