FAQ

This section contains answers to some of the most common questions consumers have about interfacing with ODI. A number of the questions deal with aspects of handling data that are more appropriate for a data warehouse, the requirements for which tend to be unique for each consumer product. A good thing to keep in mind is the central principal that ODI is not a data warehouse—it is a point in time snapshot of the data the provider sends to be loaded. Furthermore, ODI makes no value judgments on what data is passed to it from a provider—think of it as a pipe.

Question: I’m setup with access to ODI .CSV extract files from the test environment and am trying to make sense of the file names. What do the different parts of the extract file name represent?

Answer: This is an example file name from our DMZ Silverlake environment - ConsumerProduct.CustAcct.011001276.TEST.20240410T030017.zip. In the example above, there are 6 sections that give more detail about the file.

• Consumer – this is typically your business’ name. When you were onboarded to Jack Henry, you were given Valid Consumer Name and Valid Consumer Product values for your integration. The Consumer value here will always be the Valid Consumer Name assigned to your business, across all installs.
• Product – this is the name of your integrated product. This value will always be your Valid Consumer Product, as assigned by Jack Henry.
• CustAcct – this is the name of each individual extract. The full list of possible extracts can be found in the ODI Extracts section.
• 011001276 – This is the Institution Routing ID assigned to the FI. This value lives on the FI’s core and ODI will be setup to match. Typically, this value will be the FI’s ABA, but can also potentially be arbitrary values for TEST or UAT-type environments. Some FIs may have multiple ABAs, but the only one can be assigned per I individual core.
• TEST – This is the environment value assigned to the core. Production core environments will typically be set as PROD. There are also UAT and TEST environments, that might be assigned to cores used for testing purposes. These values are also arbitrary, but the three examples given (PROD, TEST, UAT) cover 98% of ODI integrations.
• 20240410T030017 – this is the timestamp value, and represents when the file was created on core, in UTC.

Question: As a new consumer, I want to obtain ODI extract data. What are my options?

Answer: Our ODI documentation details two ways to consume ODI extract data – via .CSV output files or via SQL access using an ODISvcCred call.

Due to security limitations, we are unable to offer SQL access to Third-Party Vendors at this time. The only option for TPVs is to consume extract data via .CSV files.

Details on how to obtain those .CSV files will be provided to you by Jack Henry staff when your integration is setup.

Question: What is the schedule for Delta vs Full extract files from core systems?

Answer: Silverlake, CIF2020, and Core Director can provide extract data to ODI on a schedule that is tied to End of Day (EOD) or End of Week (EOW) processing.

Financial Institutions will differ somewhat on scheduling, but typically the core system is configured to produce Delta extracts Monday through Friday during the EOD run, and Full extracts on Sunday during EOW.

When ODI receives a Full file from core, it wipes the corresponding table, then reloads with the new extract data. When ODI receives Deltas throughout the week, that data is appended to the existing data. The cycle repeats weekly – reloading on Sunday and appending throughout the week.

Question: What are the options for Full vs Delta extract files for products that consume CSV files?

Answer: ODI can produce either Full or Delta extract data for consumers. This setting is separate from the core Full/Delta schedule detailed above.

The ODI setting for Full vs Delta applies to all .CSV files for a specific instance, meaning that all files for a specific bank environment would be all Full or all Delta, never a mix of the two. When core sends a file into ODI, ODI processes the file, loads data into the tables, and then produces an output .CSV file.

If ODI is set to produce Full .CSV files, it will use all data loaded into the corresponding table to create the extract file. It doesn’t matter if core sent full or a delta data to ODI that day, ODI will always output all data it has for each file. If ODI is set to produce Delta files, those .CSV files will only contain the data that was sent from core on that day. Based on the core schedule question above, ODI would output true delta information Monday through Friday, then would produce what is in effect a Full extract each Sunday. Since the Full extract file from core causes the ODI tables to reload, ODI would treat all data in that table as new and send everything.

We recommend using the Delta configuration to reduce overhead.

Question: What options do we have for .CSV file layout?

Answer: ODI has two additional formatting options for .CSV output files: Delimiter and Wrap.

• The file Delimiter can be set to Comma, Pipe, Space, Tab, Colon, and Semicolon. Comma is used by default.
• Wrap characters can be used to wrap the individual data points. Options are None, Single Quote, Double Quote, and Pipe. Wrap characters are not typically used but can be set if the use case requires.

Question: What will happens if a consumer product needs to restore data to a previous point in time?

Answer: The data in ODI is controlled solely by what is sent from the provider. If the provider needs to reload data and restore to a point in time, they will have to send data feeds to ODI that will replace the contents of the ODI ODS (operational data store) with the restored data. It is up to the provider as to how this would be handled: they could choose to send a full reload, totally replacing the contents of the ODS, or they could send a delta file that will cause “newer” records to be deleted and “older” records to be re-added. ODI will not have any specific awareness that this is a restore and will not handle it any differently than it would any other load. A provider may choose to post an event signaling that data has been restored to a previous point, but that message must be discovered and processed by the consumer.

Question: If a provider restores data to a previous point in time, how will the consumer know? Will it get delete records for the history items that were removed?

Answer: As with any other load, ODI will post notifications that the data is ready. The consumer will be responsible for fetching the data and synchronizing as appropriate. In terms of the items that were removed, that will depend solely on which method the provider chooses to use to restore the data to a previous point in time (described above). If the provider chooses to do a full reload, there will be no history. If they choose to send a delta that marks some records as deleted, that information will be available as with any other delete.

Question: What if a consumer has a requirement to maintain history for a longer period of time than a provider? In that case the consumer would need to be able to differentiate deletes that originate because an item was purged by the provider from deletes that occur due to reversal or data recovery as outlined above. Will we be able to do that with ODI?

Answer: As ODI is not a data warehouse, it will not maintain that type of history. As described above, ODI will treat all loads the same—if there is history information that must be retained, the consumer will have to maintain it, or work out a method for having the provider retain that data and flag it appropriately.

Question: What is the difference between Dlt and the _DelFlag columns?

Answer: The Dlt flag is set by the provider to indicate that a record has been marked for deletion. _DelFlag is an ODI housekeeping field that is intended to interpret the value of the Dlt field so that the consumer can use it to filter out those records marked for deletion. Since _DelFlag is a bit field, this should be easier to use than the text Dlt flag.

Question: Can the load date be used to determine how new the data is? If a consumer uses it to calculate new rows since the last pull, is there a risk of any issues like accidentally pulling the next loaded data or missing data?

Answer: Since ODI is a point-in-time snapshot and not a data warehouse, it does not provide indicators that could be reliably used to determine what is new in the provider’s data set. Consumers should use data values delivered as part of the provider data feed to determine what constitutes updated information.

Question: If my consumer data plan is configured to create delta files only, why do I receive a full set of records on weekends and some other days?

Answer: Most providers are configured to send ODI a full set of records once per week over a weekend to ensure nothing has been missed in the processing. Also, if a provider delta file was not delivered for some reason during the week, the provider may have to send a full file to help ODI ‘catch up’. If the provider sends a full file to ODI, the consumers will receive all of the records, as ODI can no longer determine which records are new or updated. The consumer code processing the data files from ODI must be capable of handling a full file at any time.

Question: I am a consumer using the direct SQL access method to get to my data. If my query is designed to bring back only records with the highest _LoadSeq value (the most recent set of records), how do I know if I am retrieving a full set of records or just the most recent delta?

Answer: First, your query should really work differently. Because there might have been a provider data delivery that happened in the lag time between notification and the consumer process to pick up the data, you need to be retrieving all of the records not yet seen by the consumer. The best way to ensure that is to track the last _LoadSeq value retrieved and query for any records beyond that. However,you can query the SQL view to see how many LoadSeq values exist. If only one value is present, the data in the table must be from a full delivery, since only a full will clear older deliveries from the table.

Question: As it relates to transaction types in the DepAcctHist, LnAcctHist, and TimeDepAcctHist extracts, is there a standard list of transaction types and descriptions that we can see? I want to make sure we are choosing the right fields to be able to differentiate between ACH, Checking, Debit Purchase, ATM withdrawal, etc.

Answer: If you are asking for a standard list of transaction codes <TrnCodeCode> and their descriptions <TrnCodeDesc>. The answer is no, TranCodes are core parameter driven can vary from one FI to another, there is no standard list.

If you wanted to retrieve the complete list of TranCodes per account type for a given FI, you can do so by requesting the following extracts be delivered to you in your ODI configuration:

• Dep_TrnCodeDetail
• Ln_TrnCodeDetail
• TimeDep_TrnCodeDetail

Each of which would contain a list of TranCodes per account type as defined by contract:

<xsd:complexType name="BrdCstTrnCodeDetail_RType">
  <xsd:sequence>
    <xsd:element minOccurs="0" name="Dlt" type="Dlt_Type" />
    <xsd:element name="SrcKey" type="SrcKey_Type" />
    <xsd:element minOccurs="0" name="FornKey" type="FornKey_Type" />
    <xsd:element minOccurs="0" name="TrnCodeCode" type="TrnCodeCode_Type" />
    <xsd:element minOccurs="0" name="TrnCodeDesc" type="TrnCodeDesc_Type" />
    <xsd:element minOccurs="0" name="TrnType" type="TrnType_Type" />
    <xsd:element minOccurs="0" name="AffCode" type="AffCode_Type" />
    <xsd:element minOccurs="0" name="SynapFactor" type="SynapFactor_Type" />
    <xsd:element minOccurs="0" name="TrnStmtDesc" type="TrnStmtDesc_Type" />
    <xsd:element minOccurs="0" name="Custom" type="Custom_CType" />
  </xsd:sequence>
</xsd:complexType>

Question: Some ODI extracts have records that appear to relate to data in other extracts. How do I relate the record data between the various extract files?

Answer: JH providers (Silverlake, CIF2020, Core Director, etc.) publish data to the ODI system in the form of extracts. Extracts contain banking data related to a financial institution’s accounts, customers, etc. Individual extracts can have one of more relationships with other extracts, as defined by the provider. Each extract contains two key elements used to relate the data to other extracts: SrcKey (Source Key) and FornKey (Foreign Key).

SrcKey – this value is a unique key assigned to each record within an individual extract.

FornKey – when two extracts are related, the SrcKey value on one extract will be used as the FornKey value on another.

Typically, the SrcKey and FornKey values are either a CustID (Customer ID) or AcctId (Account ID). This makes it easy to match customer or account records from one extract to another. Some extracts relate customer and account data, and those may use a combination of CustID and AcctID as source keys.

The SrcKey and FornKey fields are unique and relatable only per each instance of a financial institution (FI) ODI integration. For example, customer data for First Test Bank’s production instance can be related to other production data for that same FI but cannot be related to that institution’s TEST instance data, or to other FIs.

See our example ODI extract files ODI-SilverlakeSampleFiles.zip, ODI_CIF2020SampleFiles.zip, or ODI-CoreDirectorSampleFiles.zip for more.

Question: Core Director: Is it true that the <SrcKey> is not guaranteed to remain the same for a record across file runs? For example, I should not assume that just because a <SrcKey> happens to be the same between a file dated 11/11/2019 and one dated 11/13/2019 that it is the same record. Each file run is distinct and it’s <SrcKey> values are unique to that run only?

Answer: That is correct. The SrcKey can differ across file runs.

Question: Core Director: DepAcctMemo and DepNSFHist both seem to contain intraday transactions, will all of those move to DepAcctHist at some point?

Answer: That is correct. The DepAcctMemo and DepNSFHist contain intraday transactions. During EOD processing the transactions will drop out of these files. During EOD processing, the hard posted transactions will appear in DepAcctHist.

Question: Core Director: Is DepExcTrnDetail an intraday extract and will those transactions also appear in DepAcctHist at some point?

Answer: The DepExcTrnDetail is an intraday file. Core Director currently only populates transactions into DepAcctHist that were successfully hard posted to the account.

Question: Core Director: If a transaction is returned (not-paid) will it still appear in DepAcctHist and, if so, what designates it as being returned unpaid.

Answer: This will not be returned in DepAcctHist. Only items that are successfully hard posted to the account will be returned.

Question: Are the PndDetail extracts for Account, Loan, and Time Deposit all intraday and/or unposted transactions? If transactions in these files ultimately post to the account, will the history reflect in the respective Hist extracts?

Answer: Core Director: The PndDetail (for example DepACHPndDetail) will contain unposted transactions. Once these transactions hard post, they will show in history (for example DepAcctHist) and will not show in PndDetail.

Answer: SilverLake and CIF 20/20: The items in these extracts represent records in the ACWARE core file, which are pending ACH records that have not been processed yet. Yes they will reflect in the respective Hist extracts once processed.

Question: Is Inst_OffCodeDetail a definitive list of Employee codes? Originally, I requested Ln_OffCodeDetail and Cust_OffCodeDetail as well, but these seem to be a mapping extracts that associates an officer with a loan/customer. I simply need a list of all employee codes so that if we receive an employee ID/code as input in our product, we can translate to the actual name of the employee/officer; want to make sure Inst_OffCodeDetail alone covers it.

Answer: Core Director doesn’t currently support Inst_OffCodeDetail. Core Director does support the Cust_OffCodeDetail which is an extract of CustId’s along with the OffCode that is associated with those CustId’s.

Answer: SilverLake and CIF 20/20: Inst_OffCodeDetail would be what you’re looking for, these records represent the Officer Codes setup within the FI.

Question: If the FTP job errors out while transmitting files to the SFTP folder, do you resend the file with the same name so that it will overwrite the incomplete file on our SFTP directory?

Answer: In generic terms, we will attempt to overwrite if it’s allowed yes. When a task fails it will retry and file names do not change in that instance.

Question: With Personally Identifiable Information (PII) some FIs don’t want to have those elements in ODI. Do we need a different extract for each FI, or is there some way they can not include PII elements?

Answer: You don’t need to have individual extracts created for each FI. For in-house the FI can control the elements that are sent. For hosted, the FI would send a For Clients Only (jSource) case requesting changes. So you still only have one set for all FIs using the specified core. The only time the mappings would most likely change is if you decided you needed something added or deleted.

Question: What information does the PlnCodeDetail provide?

Answer: The plan code table is for time deposits. It identifies if it’s an IRA or other type or tax deferred instrument.

Question: Is there any conversion between the bytes and string length? Our understanding is the standard conversion is 1 byte = 1 character but this does not seem to be the case.

Answer: For all fields listed as “String” Field Type on the portal the Length represents the max string length, for all other Field Types such as “Decimal” or “Int32” the “Digits” will represent the max length of the number.

Question: We are having trouble understanding the ODI Extract Relationships document and joining criteria between the tables. Based on the sample data analysis we have identified the joins below, can you please confirm if these relationships are correct?

Answer: These are all correct.

Question: If a party changes/gets a new ACCTID, will only the DEPACCT have a record? Or will CUSTDETAIL also have an updated record since there is a parent/child relationship? We are trying to determine if there is a table that can always be the “main” file in outer joins, or if we need to find a workaround.

Answer: In the above example only DepAcct will get the new record, CustDetail will not get the update. However, if a “join” table exists such as CUST_ACCT there will be corresponding new record as well.

Question: Assuming LENGTH represents the length of the attribute, the length of all DATE attributes in the sample file would be 10, based on the “YYYY-MM-DD” format while the ODI file specifies the length as 7. What gives?

Answer: All ODI dates are in the YYYY-MM-DD format. Any references to core formatting or length of dates should be ignored.

Question: There seems to be a discrepancy between the data type for date fields. The ODI file specifies DATETIME where the sample file only has DATE.

Answer: ODI just provides whatever date is stored on the core, some date fields have datetime while others just have date.

Question: Length of Postal Code in CUST_ADDR and Zip Code in CUSTDETAIL doesn’t match between the ODI file and the sample file. There’s a prefix of “0000” in the sample date.

Answer: The PostalCode element is formatted as the 5 digit ZIP code followed by the 4 digit add-on code. If an add-on code was not specified, four zeroes need to be appended to the end of the ZIP code, otherwise the core provider will pad four zeroes to the front of ZIP code. For example, if 92630 is passed in instead of 926300000, the postal code will be saved by the core provider as 000092630, or 00009-2630.

Question: Please provide definitions for the following on the ODIElementMapping_SL_ConsumerVersion and ODIElementMapping_CIF2020_ConsumerVersion spreadsheets.

• Difference between Length and Digits
• Provide a full description for the values in column Type with values A, L, P, S, Z?

Answer: The answer is as follows:

• The length is the number of bytes the storage takes up on disk, and the digits is precision of the number on the iSeries.

• The type of field definition on the iSeries:

  • A - Alphanumeric
  • L - Date
  • P - Packed Decimal
  • S - Zoned Decimal
  • Z - Timestamp

*Question: There seems to be a discrepancy between length of Cust_Phone field in the “present date” sample data where the ODI file mentions the length as 2 but the sample data has descriptive values such as “Business Phone” or “Home Phone” instead of abbreviated values like “BP” or “HP”.

Answer: Length is not the string length, it’s the number of bytes. While “BP” and “HP” are the required canonicals to use for the real time API calls like CustAdd/CustMod, the canonicals get mapped over and stored on the core as “Business Phone” and “Home Phone”.