Return to Arrow Integration

Article Index

Integrated Fields

The integration of Arrow Accounts Receivable to Framework ECM is fixed in the content that is exchanged between the systems. There are no user-configurable integrated fields available for 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.

Open

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. Read profile keys to get the output path and file name for invoices file.

   2. Check the file name does not already exist, if so prompt to delete it.

   3. Get eligible Framework jobs using v_sched_tlClaims where

      1. Cst_claim.d_raised is not null

      2. Cst_claim.s_link_accts <> ‘N/A’

      3. Cst_claim.f_link_exported = 0

      4. Client.s_name_ref is not null

      5. Client.s_name is not null

      6. Job.s_link_accts <> ‘N/A’

      7. Job. l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)

      8. and apply Criteria Method criteria where CriteriaMethod is

      9. 1 – Single Region Division

      10. job.l_context_id = ‘CriteriaMethodID’

      11. 2 – Single Job

      12. job.s_job_num = ‘CriteriaMethodID’

      13. 3 – Single Team

      14. job.l_team_id = ‘CriteriaMethodID’

      15. 4 – All Jobs

      16. job.l_job_id <> 0

      17. 5 – Accounts Entity

      18. job.l_accounts_e_id = ‘CriteriaMethodID’

   1. If an eligible job claim has an empty invoice Number, it is not included in the output file.

   2. The Arrow table jobMast is checked for a matching Job Number where JobMast.job_code = “job.s_job_num’, if it is found the claim details are added to the output file.

   3. As claims are added to the output file, their cst_claim.f_link_exported flag is set to true.

3. ActionProcessReceipts

   1. Eligible receipts are read from Arrow tables Drstran and JobMast joined on drsTran.customer_code = jobMast.customer_code where

      1. drsTran.trans_type = ‘CBREC’ if profile key DepositRunEndDate is empty then

      2. drsTran.date > = ‘DepositRunStartDate’ and <= now else if DepositRunEndDate has a date entered then

      3. drsTran.date > = ‘DepositRunStartDate’ and <= ‘DepositRunEndDate’

   2. A framework record from v_sched_tlClaims is searched where

      1. Cst_claim.s_invoice_num = drsTran.reference

      2. Cst_claim.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) and apply Criteria Method criteria where CriteriaMethod is

      4. 1 – Single Region Division

      5. job.l_context_id = ‘CriteriaMethodID’

      6. 2 – Single Job

      7. job.s_job_num = ‘CriteriaMethodID’

      8. 3 – Single Team

      9. job.l_team_id = ‘CriteriaMethodID’

      10. 4 – All Jobs

      11. job.l_job_id <> 0

      12. 5 – Accounts Entity

      13. job.l_accounts_e_id = ‘CriteriaMethodID’

      14. If a Framework Claim meets the above criteria it will be processed.

   3. Receipts will be skipped from processing if drsTran.date 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/[drstran.apply_reference]/[drstran.dfrecnum] where

      1. yyyymmdd is the year month and day format of drsTran.date

      2. the [drsTran.apply_reference] field is 8 characters long padded with zeroes

      3. the [drstran.dfrecnum] 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.

4. 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

      6. 1 – Single Region Division

      7. job.l_context_id = ‘CriteriaMethodID’

      8. 2 – Single Job

      9. job.s_job_num = ‘CriteriaMethodID’

      10. 3 – Single Team

      11. job.l_team_id = ‘CriteriaMethodID’

      12. 4 – All Jobs

      13. job.l_job_id <> 0

      14. 5 – Accounts Entity

      15. job.l_accounts_e_id = ‘CriteriaMethodID’

   2. Invoice Totals are compared between Arrow 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 drsTran where drsTran.reference = cst_claim.s_invoice_num and drsTran.trans_type = ‘DRINV’.

      3. If the invoice record(s) is found the sum of drsTran.net_value is calculated and compared with the total of the cst_claim.c_total fields, we calculated previously, to determine if the invoice totals match.

   3. 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 drsTran where drsTran.reference = cst_claim.s_invoice_num and drsTran.trans_type = ‘CBREC’

      3. If there are receipts then the sum of drsTran.net_value is calculated and compared with the total of the cst_claim.c_paid_amt fields, we calculated previously, to determine if the receipts match.