| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
Return to Sage Timberline Client and Jobs Integration
Article Index
| Table of Contents |
|---|
Integrated Fields
The integration of Sage of Sage Timberline Client and Jobs to Framework ECM is 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 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.
If the profile key for setClosedStatus is 1 then we get all jobs from v_sched_tlGold1 where
Job.l_wfl_stgMajor_id = -5 (Maintenance)
Cont.d_settle_act = within the last 6 months. Not including the current month.
We then get matching Timberline Accounts from Master_Jcm_Record_1_1 where If job.s_link_boq is empty then
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
The records that match in Timberline will have their status set to Closed
Next, get eligible Framework jobs using v_sched_tlGold1 where
Client.s_name_ref is not null
Client.s_name is not null
Job.s_link_boq <> ‘N/A’
Job.s_link_accts <> ‘N/A’
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).
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.and apply Criteria Method criteria where CriteriaMethod is
1 – Single Region Division • job.l_context_id = ‘CriteriaMethodID’
2 – Single Job • job.s_job_num = ‘CriteriaMethodID’
3 – Single Team • job.l_team_id = ‘CriteriaMethodID’
4 – All Jobs • job.l_job_id <> 0
5 – Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’
For each Framework Job search for the job in Timberline table Master_Jcm_Record_1_1 for a matching record where...
Master_Jcm_Record_1_1.job = job.s_job_num (formatted to match Timberlines format)
OR
if s_boq_link is not empty thenMaster_Jcm_Record_1_1.job = job.s_link_boq
If the job is found in timberline then
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)
The matching record from Master_Jcm_Record_20 is found where Master_Jcm_Record_20.juJob = the formatted framework job number (or link BOQ)
The matching client record is retrieved from Master_Arm_Record_1 where Master_Arm_Record_1.cust = Master_Jcm_Record_1_1.jarcust
The matching record is retrieved from Master_Arm_Record_4 where Master_Arm_Record_4.cucust = Master_Jcm_Record_1_1.jarcust
If all records above are retrieved successfully we update the timberline job.
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)
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
If the job is not found in timberline then
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.
If the job.s_link_boq field is empty we create the job in timberline.
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.