Integrated Fields

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

Table: File23 (Jobs)

Octal8 BizPrac Database Field Name	Octal8 BizPrac Field Label	Create	Update	Framework Database Field Name
C_code	Unique Client Code	Yes	No	Special # 14: Gets next unique number from file903.tps
Job_no	Job Number	Yes	No	Framework – s_job_num
Address1	Job Address	Yes	Yes	Special # 5: Job lot number, job street number, job street name
Suburb	Fkid number to the correct suburb in file35	Yes	Yes	Special #41: Searches for the matching suburb and returns the linkCode, otherwise creates the suburb in the table if it does not exist and returns the new linkCode
Books	BookSet	Yes	No	Fixed Value 1
Contact	Primary Contact Name	Yes	Yes	Framework - s_clientCont_name
Contract	Contract inc GST	Yes	Yes	Framework - c_retail_external
Phone	Primary Contact Business Hours Phone	Yes	Yes	Framework – s_client_phone
Fax	Primary Contact Fax	Yes	Yes	Framework - s_client_fax
Mobile	Primary Contact Mobile	Yes	Yes	Framework - s_client_mobile
Job_name	Client Reference Name	Yes	Yes	Framework - s_client_nameRef
jobType	New Home	Yes	No	Fixed Value ‘New Home’

Table: File816 (Clients)

Octal8 BizPrac Database Field Name	Octal8 BizPrac Field Label	Create	Update	Framework Database Field Name
C_code	Unique Client Code	Yes	No	Special # 14: Gets next unique number from file903.tps
Client_name	Client Reference Name	Yes	Yes	Framework - s_client_nameRef
Contactname	Client Salutation	Yes	Yes	Framework - s_client_salutation
Books	BookSet	Yes	No	Fixed Value 1
cliAddress1	Client Address	Yes	Yes	Special #1: Client street number and client street name
cliSuburb	Fkid number to the correct suburb in file35	Yes	Yes	Special # 41: Searches for the matching suburb and returns the linkCode, otherwise creates the suburb in the table if it does not exist and returns the new linkCode
Country	Country	Yes	Yes	Framework – s_client_country
cliPhone	Primary Contact Business Hours Phone	Yes	Yes	Framework – s_client_phone
cliFax	Primary Contact Fax	Yes	Yes	Framework - s_client_fax
Mobile	Primary Contact Mobile	Yes	Yes	Framework - s_client_mobile
Alt_name	Client Name	Yes	Yes	Framework - s_client_name
Alt_contactName	Client Salutation	Yes	Yes	Framework - s_client_salutation
Alt_address1	Client Address	Yes	Yes	Special # 1: Client street number and client street name
Alt_suburb	Fkid number to the correct suburb in file35	Yes	Yes	Special # 41: Searches for the matching suburb and returns thel inkCode, otherwise creates the suburb in the table if it does not exist and returns the new linkCode.
Alt_country	Country	Yes	Yes	Framework- s_client_country

Table: File35 (Suburb)

Octal8 BizPrac Database Field Name	Octal8 BizPrac Field Label	Create	Update	Framework Database Field Name
Linkcode	Unique Suburb Code	Yes	No	Gets next unique number from file903.tps
City_name	Suburb Name	Yes	No	Framework – s_client_suburb or s_job_suburb
State	Suburb State (short version)	Yes	No	Framework – s_client_state or s_job_state
postcode	Postcode	Yes	No	Framework – s_client_postcode or s_job_postcode

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.

Initial Checks
1. License for BizPrac Accounts integration is checked.
2. Framework DSN is opened
3. Framework shutdown flag is checked, if not set for shutdown we continue.
4. Framework Integration Mapping DSN is opened.
5. BizPrac DSN is opened.
Profile values are read from the integration profile. If reads are successful we continue.
Framework jobs eligible for integration are retrieved from v_sched_tlGold1 with the following criteria
1. S_client_nameRef is not null
2. S_client_name is not null
3. Job.s_link_accts <> N/A
4. Job.s_link_boq <> N/A
5. Job.l_job_status_gl_id = -7 (active)
  Or
  Job.l_job_status_gl_id = -10(on hold)
  Or
  or l_job_status_gl_id = -946 (Pending Cancellation)
  Or
  Job.l_job_status_gl_id = -8 and (cont.d_cont_cancelled < 14 days ago)(cancelled in the last 2 weeks)
6. Job.l_wfl_stgmajor_id <> -5 (job not in maintenance)
  Or
  Job.l_wfl_stgMinor_id = -18 and (cont.d_settle_act < 140 days ago) (job in minor stage of maint dlp and not longer than 20 weeks since settlement)
  Or
  Job.l_wfl_stgMinor_id = -19 and maint.d_dlp_expiry_act <28 days ago)(job in minor stage of maint service and not longer than 28 days since dlp complete actual.)
7. And apply criteria method criteria where CriteriaMethod is...
  1. Single Region Division – job.l_context_id = criteriaData
  2. Single Job – job.s_job_num = criteriaData
  3. Single Team – job.l_team_id = criteriaData
  4. All Jobs – job.l_job_id <> 0
  5. Accounts Entity – job.l_accounts_e_id = criteriaData
For each Framework Job, search for the matching BizPrac accounts record from file23 where
1. If job.s_link_boq is empty then (job_no = job.s_job_num)
2. Else (job_no = job.s_link_boq)
If there is a match we update the record in BizPrac
1. Open recordsets in edit mode for file23 (BizPrac's job file) and file816 (BizPrac's client file)
  1. i. Get the matching record from file23 then use that records c_code to retrieve the correct record from file816.
2. Copy framework data to BizPrac using the settings in the integration mapping database.
If there is no match we create the record in BizPrac
1. Open recordsets and create new records in file23 (BizPrac's job file) and file816 (BizPrac's client file)
2. Copy framework data to BizPrac using the settings in the integration mapping database.
Update Output with summary details and email if specified.

Octal8 BizPrac Client and Jobs Integration - Reference