/
Databuild Accounts Receivable Integration - Reference

Databuild Accounts Receivable Integration - Reference

Owned by Julie Doyle-Whitelock (Deactivated)
Last updated: Aug 09, 2017

Integrated Fields

The integration of Databuild Accounts Receivable 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. ActionCreateInvoices
    1. Get eligible Framework jobs using v_sched_tlClaims where
      1. csg_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. Single Region Division • job.l_context_id = ‘CriteriaMethodID’
      8. Single Job • job.s_job_num = ‘CriteriaMethodID’
      9. Single Team • job.l_team_id = ‘CriteriaMethodID’
      10. All Jobs • job.l_job_id <> 0
      11. Accounts Entity • job.l_accounts_e_id = ‘CriteriaMethodID’ b.
      12. If an eligible job claim has an empty invoice Number, it is not included in the output file.
    2. The Databuild table jobs is checked for a matching Job Number where jobs.job_no = ‘job.s_link_boq’, or if the s_link_boq field is empty then the match is done on jobs.job_no = ‘job.s_job_num’,
    3. If the job is found then a new record is added to the transactions table holding details of the framework claim.
    4. The Framework cst_claim is updated so that…
      1. cstClaim.f_link_exported is set to true
      2. cstClaim.s_link_accts is set to transactions.tranNo
  3. ActionProcessReceipts
    1. First, we get unlinked payment records from Framework and see if they’re now linked in Databuild to a claim. We get all records from cont_pay where
      1. Cont_pay.l_cst_claim_id = 0
      2. Cont_pay.s_link_accts is not empty
    2. For each payment that is unlinked we look for a matching record from the databuild transactions table where
      1. transactions.tranNo = cont_pay.s_link_accts
      2. transactions.closingTran > 0
    3. Next we find the Framework Claim where
      1. cst_claim.s_link_accts = transactions.closingTran
    4. If the claim is found the cont_pay record is linked to the claim and the claim has it’s paid amount field updated.
    5. Now we process Databuild Receipts. Eligible receipts are read from Databuild table Transactions where
      1. transactions.tType = 2 if profile key DepositRunEndDate is empty then
      2. transactions.date > = ‘DepositRunStartDate’ and <= now else if DepositRunEndDate has a date entered then
      3. transactions.date > = ‘DepositRunStartDate’ and <= ‘DepositRunEndDate’
    6. If the transactions.closingTran is > 0 we
      1. Make sure the receipt does not exist in the cont_pay table where cont_pay.s_link_accts = the transactions.tranNo
      2. if the payment does not exist then we can create it. First get the Framework job where job.s_job_num = transactions.jobNo
      3. Then Insert the new cont_pay receipt record and update the cont payments.
      4. Else if the transactions.closingTran = 0 then
      5. Make sure the receipt does not exist in the cont_pay table where cont_pay.s_link_accts = the transactions.tranNo
      6. If the payment does not exist, get the framework claim where cst_claim.s_link_accts = transactions.closingTran
      7. Then insert the new cont_pay receipt record and update the cont payments.
      8. After processing receipts the profile key depositRunStartDate is updated.
  4. ActionCheckingConsistency
    1. Eligible Framework jobs for checking are retrieved from v_sched_tlCstClaim where
      1. Check Consistency Level claim by claim
        1. Job.l_wfl_stgMajor_id = -3(Administration) or -4 (Construction)
        2. Job. l_job_status_gl_id = -7(Active) or -10(On Hold)
        3. Cst_claim.s_invoice_num is not empty
        4. Cst_claim.s_link_accts <> ‘N/A’
        5. job.s_link_accts <> ‘N/A’
      2. Check Consistency Level is job by Job
        1. Job.l_wfl_stgMajor_id = -3(Administration) or -4 (Construction)
        2. Job. l_job_status_gl_id = -7(Active) or -10(On Hold)
        3. job.s_link_accts <> ‘N/A’
      3. 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. Invoice Totals are compared between Databuild and Framework
      1. Check Consistency Level is Claim by Claim
        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 transactions where transactions.tranNo = cst_claim.s_link_accts and transactions.tType = 1
        3. If the invoice record is found the transactions.amount is compared with the total of the cst_claim.c_total fields, we calculated previously, to determine if the invoice totals match.
      1. Check Consistency Level is Job by Job
        1. All eligible claims for the job are read and the sum of cst_claim.c_total is determined where
          • - cst_claim.s_invoice_num is not empty
          • - cst_claim.s_link_accts <> ‘N/A’
          • - f_link_exported = -1
          • -d_invoice is not empty
        2. The sum is compared to Databuild where the Amount is read from the transactions table where JobNo = (job.s_link_boq or if empty job.s_job_num) and ttype = 1
    1. Receipt Totals for each invoice are compared between Databuild and Framework
      1. Check Consistency Level is Claim by Claim
        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 transactions where transactions.closingTran = cst_claim.s_link_accts and transactions.tType = 2
        3. If there are receipts then the sum of transactions.amount is calculated and compared with the total of the cst_claim.c_paid_amt fields, we calculated previously, to determine if the receipts match.
      2. Check Consistency Level is Job by Job
        1. The Framework Payments are summed together, the sum of c_pay from cont_pay is added up.Then because some cst_claims can indicate a refund we have to sum any cst_claims for the job where the c_paid_amt < 0 and remove this amount from the total payments.
        2. The matching receipts are retrieved from transactions where transactions.jobNo = (job.s_link_boq or if empty job.s_job_num)and transactions.tType = 2
        3. If there are receipts then the sum of transactions.amount is calculated and compared with the total of the Framework payments, to determine if the receipts match.