Arrow Accounts Receivable Integration - Profile Configuration
- Julie Doyle-Whitelock (Deactivated)
The following outlines the settings for Arrow Accounts Receivable integration .ini file profiles.
Default initialisation filename:
- Int_ArrowClaims_Default.ini
Settings
Key | Description/Setting |
|---|---|
IntegrationType | Description: Specifies the type of integration to be performed. This is a fixed value that should not be changed. Setting: Arrow Claims |
Databases
Key | Description/Setting |
|---|---|
ODBCDSN_Framework | Description: ODBC DSN name for the Framework database. Setting: FworkSQLECM |
ODBCConnect_Framework | Description: ODBC DSN connection string for the Framework database. Setting: None |
ODBCDSN_FrameworkLicense | Description: ODBC DSN name for the Framework Licence database. Setting: FworkSQLLIC |
ODBCDSN_Destination | Description: ODBC DSN name for the Arrow Claims database. Setting: FworkArrowAccts |
ODBCConnect_Destination | Description: ODBC DSN connection string for the Arrow Claims database. Setting: "uid=enterUserName;pwd=enterPassword" |
Mail Configuration
Key | Description/Setting |
|---|---|
Mail_System | Description: Email delivery method. Setting: 0: No email will be sent. 1: Email will be sent using MAPI. 2 (default): Email will be sent using SMTP. |
Mapi_Profile | Description: Microsoft Outlook user profile name. Setting: (default): Outlook |
Mail_From | Description: The name or email address of who/where the email is being sent from. Setting: FrameworkIntegration |
Mail_Recipient | Description: These are the mail recipients of integration logs. Multiple addresses can be added if separated with a semi-colon (;). Spaces are not necessary before or after the semi-colon. Setting: enterRecipientEmail@yourDomain.com.au;integrationsupport@insulagroup.com.au |
Mail_Subject | Description: Email subject heading. Setting: "Framework Integration : Arrow Claims" |
SMTP_Server | Description: If using Mail_System 2 (SMTP), add the SMTP Server name. Setting: enterSMTPServerName (replace this text with your SMTP Server name) |
SMTP_UseSecurity | Description: Is security used for the STMP server? Setting: 0 (default): A username or password is not required. 1: A username and password is required. If this setting is used, the SMTP_UserName and SMTP_Pwd fields must have a valid username and password. |
SMTP_UserName | Description: A valid username for the SMTP server. Setting: enterUserName (replace this text with a valid SMTP Server User Name) |
SMTP_PWD | Description: A valid password for the SMTP username. Setting: enterPassword (replace this text with a valid SMTP Server Password) |
Criteria
Key | Description/Setting |
|---|---|
CriteriaMethod | Description: Job selection criteria for retrieving records from Framework to process. Setting: 1: Single region/division. 2: Single job. 3: Single team. 4 (default): All jobs. 5: Accounts entity. |
CriteriaData | Description: Input criteria data for the criteria method. Setting: 0 |
DepositRunStartDate | Description: Date used to select receipts that were entered into Arrow after or on this date. Setting: 01/01/2006 |
DepositRunEndDate | Description: Date used to select receipts that were entered into Arrow before or on this date. Setting: 01/01/2006 |
Actions
Key | Description/Setting |
|---|---|
ActionCreateInvoices | Description: Creates a file of Framework Claims Invoices in a format that can be inserted into Arrow. Setting: 0: Framework Integration will not create invoices. 1 (default): Framework Integration will create invoices. |
ActionProcessReceipts | Description: Retrieves Arrow Receipts and inserts them into Framework, updating invoices in the process. Setting: 0 (default): Framework Integration will not process receipts. 1: Framework Integration will process receipts. |
ActionCheckingConsistency | Description: Checks that Framework and Arrow have matching invoices and receipts. Setting: 0 (default): Framework Integration will not check consistency. 1: Framework Integration will check consistency. |
Arrow Settings
Key | Description/Setting |
|---|---|
InitialDeposit | Description: Arrow Cost Code for the Initial Deposit. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
BalanceDeposit | Description: Arrow Cost Code for the Balance Deposit. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
BaseClaim | Description: Arrow Cost Code for the Base Claim. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
FrameClaim | Description: Arrow Cost Code for the Frame Claim. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
LockUpClaim | Description: Arrow Cost Code for the Lock Up Claim. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
FixOutClaim | Description: Arrow Cost Code for the Fix Out Claim. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
FinalClaim | Description: Arrow Cost Code for the Final Claim. Setting: 0 These need to have values that are from Arrow and indicate the Cost Code for each stage. |
File/Path Location
Key | Description/Setting |
|---|---|
OutputPath | Description: Path where invoice file will be created. Setting: c:\Temp |
OutputFilename | Description: Invoice file name that is created storing invoices in a format that can be imported into Arrow Claims. Setting: ARInvImport.txt |
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 Model
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.
- Read profile keys to determine which actions to process.
- ActionCreateInvoices
- Read profile keys to get the output path and file name for invoices file.
- Check the file name does not already exist, if so prompt to delete it.
- Get eligible Framework jobs using v_sched_tlClaims where
- Cst_claim.d_raised is not null
- Cst_claim.s_link_accts <> ‘N/A’
- Cst_claim.f_link_exported = 0
- Client.s_name_ref is not null
- Client.s_name is not null
- 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)
- 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’
- If an eligible job claim has an empty invoice Number, it is not included in the output file.
- 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.
- As claims are added to the output file, their cst_claim.f_link_exported flag is set to true.
- ActionProcessReceipts
- Eligible receipts are read from Arrow tables Drstran and JobMast joined on drsTran.customer_code = jobMast.customer_code where
- drsTran.trans_type = ‘CBREC’ if profile key DepositRunEndDate is empty then
- drsTran.date > = ‘DepositRunStartDate’ and <= now else if DepositRunEndDate has a date entered then
- drsTran.date > = ‘DepositRunStartDate’ and <= ‘DepositRunEndDate’
- A framework record from v_sched_tlClaims is searched where
- Cst_claim.s_invoice_num = drsTran.reference
- Cst_claim.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) 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’
- If a Framework Claim meets the above criteria it will be processed.
- Receipts will be skipped from processing if drsTran.date is empty
- 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
- yyyymmdd is the year month and day format of drsTran.date
- the [drsTran.apply_reference] field is 8 characters long padded with zeroes
- the [drstran.dfrecnum] field is 8 characters long padded with zeroes
- 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.
- Eligible receipts are read from Arrow tables Drstran and JobMast joined on drsTran.customer_code = jobMast.customer_code where
- ActionCheckingConsistency
- Eligible Framework jobs for checking are retrieved from v_sched_tlCstClaim where
- Job.l_wfl_stgMajor_id = -3(Administration) or -4 (Construction)
- Job.l_job_status_gl_id = -7(Active) or -10(On Hold) or l_job_status_gl_id = -946 (Pending Cancellation)
- Cst_claim.s_invoice_num is not empty
- Cst_claim.s_link_accts <> ‘N/A’
- 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’
- Invoice Totals are compared between Arrow and Framework
- Cst_claims with the same invoice numbers have their cst_claim.c_total field values summed together
- The matching invoice is retrieved from drsTran where drsTran.reference = cst_claim.s_invoice_num and drsTran.trans_type = ‘DRINV’.
- 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.
- Receipt Totals for each invoice are compared between Arrow and Framework
- Cst_claims with the same invoice numbers have their cst_claim.c_paid_amt field values summed together
- The matching receipts are retrieved from drsTran where drsTran.reference = cst_claim.s_invoice_num and drsTran.trans_type = ‘CBREC’
- 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.
- Eligible Framework jobs for checking are retrieved from v_sched_tlCstClaim where