/
Sage Timberline AR Method 1 Integration - Reference

Sage Timberline AR Method 1 Integration - Reference

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

Integrated Fields

The integration of Sage Timberline Accounts Receivable Method 1 to Framework is fixed in the content that is exchanged between the systems. There are no user-configurable integrated fields available to this type of integration.

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. Read profile keys to determine which actions to process.
  2. Analyse Phase Codes
    1. Get eligible Framework jobs from v_sched_tlClaims where
      1. Job.l_wfl_stgMajor_id = -4(construction) or -3 (Administration)
      2. job.l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)
      3. and apply Criteria Method criteria where CriteriaMethod is
      4. 1 – Single Region Division • job.l_context_id = ‘CriteriaMethodID’
      5. 2 – Single Job • job.s_job_num = ‘CriteriaMethodID’
      6. 3 – Single Team • job.l_team_id = ‘CriteriaMethodID’
      7. 4 – All Jobs • job.l_job_id <> 0
      8. 5 – Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’
    2. The matching job record is retrieved from Master_Jcm_Record_1_1 where Master_Jcm_Record_1_1.job = the formatted framework job number
    3. Master_Jcm_Record_1_1.jbMeth is used to determine if we use phase codes or contract codes.
    4. If we use phase codes (“Use Quick Bill”) then we look for the code in table Master_Jcm_Record_3 where
      1. Master_Jcm_Record_3.pjob = Master_Jcm_Record_1_1.job
      2. Master_Jcm_Record_3.phase = value from profile key
    5. If we use contract codes then we look for the code in table Active_Cnc_Record_10 where
      1. Active_Cnc_Record_10.iic = job.s_job_num (if profile key useJobFmtForAnalysis is set to 1 then the job number will be formatted for timberline)
      2. Active_Cnc_Record_10.iici = value from profile key
    1. ActionCreateInvoices
      1. Before creating the invoice file, the analysePhaseCodes function above is called. There is slightly different criteria for job selection. Jobs selected for analysis are read from v_sched_tlClaims where
        1. Cst_claim.d_invoice is not null
        2. Cst_claim.s_link_accts <> ‘N/A’
        3. Cst_claim.f_link_exported = 0
        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)
        6. and apply Criteria Method criteria where CriteriaMethod is
        7. 1 – Single Region Division • job.l_context_id = ‘CriteriaMethodID’
        8. 2 – Single Job • job.s_job_num = ‘CriteriaMethodID’
        9. 3 – Single Team • job.l_team_id = ‘CriteriaMethodID’
        10. 4 – All Jobs • job.l_job_id <> 0
        11. 5 – Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’
        12. Then Analysis will continue as above from Step 2.ii.
      2. Read profile keys to get the output path and file name for invoices file.
      3. Check the file name does not already exist, if so prompt to delete it.
      4. Get eligible Framework jobs using the same criteria listed above in step a) for the analysing of cost codes.
      5. If an eligible job claim has an empty invoice Number, it is not included in the output file.
      6. The timberline table Master_Jcm_Record_1_1 is checked for a matching Job Number where Master_Jcm_Record_1_1.job = job.s_job_num (formatted for timberline), if it is found the claim details are added to the output file.
      7. As claims are added to the output file, their cst_claim.f_link_exported flag is set to true.
    2. ActionProcessReceipts
      1. Get eligible Timberline Receipts from table Current_Art_Record_1 where
        1. Current_Art_Record_1.tInv is not null
        2. Current_Art_Record_1.type = ‘Cash Receipt’ if profile key DepositRunEndDate is empty then
        3. Current_Art_Record_1.tdate > = ‘DepositRunStartDate’ and <= now else if DepositRunEndDate has a date entered then
        4. Current_Art_Record_1.tdate > = ‘DepositRunStartDate’ and <= ‘DepositRunEndDate’
      2. For each timberline receipt, see if the linked invoice is in Framework v_sched_tlClaims where
        1. Cst_claim.s_invoice_num = Current_Art_Record_1.tInv
        2. job.s_link_accts <> ‘N/A’
        3. Job. l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)
        4. and apply Criteria Method criteria where CriteriaMethod is
        5. 1 – Single Region Division • job.l_context_id = ‘CriteriaMethodID’
        6. 2 – Single Job • job.s_job_num = ‘CriteriaMethodID’
        7. 3 – Single Team • job.l_team_id = ‘CriteriaMethodID’
        8. 4 – All Jobs • job.l_job_id <> 0
        9. 5 – Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’
      3. Receipts will be skipped from processing if Current_Art_Record_1.tdate is empty
      4. Because some payments may have already been processed into Framework a key combination is checked against cont_pay.s_link_accts. If the key is found the payment processing is skipped. The key is made up as follows… yyyymmdd/[ Current_Art_Record_1.Trun]/[ Current_Art_Record_1.tSeq] where
        1. yyyymmdd is the year month and day format of Current_Art_Record_1.tdate
        2. the [Current_Art_Record_1.tRun] field is 8 characters long padded with zeroes
        3. the [Current_Art_Record_1.tSeq] field is 8 characters long padded with zeroes
      5. If above is all ok then the payments for the invoice are inserted in Framework and then the Framework cst_claim and cont totals are updated.
  1. ActionCheckingConsistency
    1. Eligible Framework jobs for checking are retrieved from v_sched_tlCstClaim where
      1. Job.l_wfl_stgMajor_id = -3(Administration) or -4 (Construction)
      2. Job.l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)
      3. Cst_claim.s_invoice_num is not empty
      4. Cst_claim.s_link_accts <> ‘N/A’
      5. 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’
    2. For each claim/invoice received the following occurs.
      1. If there is no date criteria set in the integration profile, processing carries on as below.
      2. If there is date criteria then each invoice is checked to see if the d_invoiced is within the date criteria.
      3. If the invoice is within the date criteria, processing carries on as below, if it is outside of the date criteria then the invoice is not processed and it will be logged under the heading ‘Unprocessed’ in the log.
      4. If there is no date criteria specified, or the invoice is within the date criteria, the following steps occur.
        • Invoice Totals are compared between Timberline and Framework
          1. Cst_claims with the same invoice numbers have their cst_claim.c_total field values summed together
          2. The matching invoice is retrieved from Current_Art_Record_1where Current_Art_ Record_1.tInv = cst_claim.s_invoice_num and Current_Art_Record_1.type = ‘Issued invoice’ or ‘Invoice’ or ‘Invoice adjustment’.
          3. If the invoice record(s) is found the sum of Current_Art_Record_1.tAmt is calculated and compared with the total of the cst_claim.c_total fields, we calculated previously, to determine if the invoice totals match.
          4. If the totals do not match then the consistency check tolerance value set in the profile is used to determine if the invoice totals are within an acceptable variation. If they are then the record is flagged as within the variation, otherwise it is flagged as not matching.
        • Receipt Totals for each invoice are compared between Arrow and Framework
          1. Cst_claims with the same invoice numbers have their cst_claim.c_paid_amt field values summed together
          2. The matching receipts are retrieved from Current_Art_Record_1where Current_Art_ Record_1.tInv = cst_claim.s_invoice_num and Current_Art_Record_1.type = ‘Cash Receipt’
          3. If there are receipts then the sum of Current_Art_Record_1.tAmt is calculated and compared with the total of the cst_claim.c_paid_amt fields, we calculated previously, to determine if the receipts match.
          4. If the totals do not match then the consistency check tolerance value set in the profile is used to determine if the receipt totals are within an acceptable variation. If they are then the record is flagged as within the variation, otherwise it is flagged as not matching.