Transferring T-log data

The transaction log (T-log) data type captures the transactional sales history to help determine demand. The demand forecast helps to determine the inventory levels that are needed to fulfill orders. The transaction log data is used for computing inventory performance costs, including estimating the chances of a stock out and key indicators predicted velocity.

Procedure

  1. To create the transaction-log .csv files, complete the following steps:
    1. For the file name, use the TLOG_[CUSTOMER NAME]_YYYYMMDDHHMISS.csv format.
    2. For the separator, use the comma (,).
    3. Upload multiple .csv files by compressing them into a tar.gz file.
  2. You must follow the order of the fields shown in the data type table by matching the same sequence for the fields in the column in the CSV file. For example, if Item ID is the first field that is defined in the data type table, it must follow the same order in the column in the CSV file. Review the following fields and descriptions for the transaction log data:
    Table 1. Transaction log data descriptions
    Sequence Field Name Required Type Description
    1. Item ID Yes STRING The item for the transaction.
    2. Item UOM No STRING The unit of measure for an item. The valid values are Each, Pallet and Pound. The default unit of measure is Each.
    3. Item class No STRING The class of an item.
    4. Ship node Yes STRING The node at which the transaction occurred.
    5. Transaction number Yes INTEGER The number to identify the transaction.
    6. Transaction workstation ID Yes INTEGER The workstation at which the transaction occurred.
    7. Transaction date Yes STRING The date on which the transaction update occurred in the following format, YYYY-MM-DDTHH:MI:SS.

    The Date TimeStamp should be in the ISO 8601 date time format.
    8. Transaction sequence number Yes INTEGER The unique sequence number to identify the transaction.
    9. Transaction line sequence number Yes INTEGER The sequence number within a transaction.
    10. Transaction quantity Yes DECIMAL The quantity of items that are sold in the transaction.
    11. Transaction type Yes STRING A type of transaction. The type of transaction includes the following options:
    • SALE: All items that scan as regular sales.
    • RETN: All Items that are returned.
    • EXCH: All Items that are exchanged.
    • ADJU: An adjustment that occurs against a line, such as discounts, gift cards.
    12. Transaction item status code Yes STRING The status code for the Item-Node pair. The status code includes the following options:
    • ACTIVE: Active
    • PROMO: On promotion
    • MKDOWN: On markdown
    • DISCONT: Discontinued
    13. Transaction total retail amount Yes DECIMAL The total regular transaction retail price without a discount, not including taxes.
    14. Transaction total retail amount with discounts Yes DECIMAL The total price of the transaction with discount, not including taxes.
    15. Transaction total charged amount Yes DECIMAL The total amount that is charged to the customer for this transaction including customer-specific discounts and without taxes.
    16. Extension Transaction code No STRING The omnichannel fulfillment code to indicate how the order was fulfilled. The fulfillment code includes the following options:
    • Ship from store
    • Buy online
    • Store
    17. Transaction currency No STRING The currency that is associated with the item total. This value needs to be in the ISO three letter currency format, such as USD, GBP, or EUR.
  3. Review the following sample .csv file: 
    
item_id,item_uom,item_class,ship_node,transaction_number,transaction_workstation_id,transaction_update_ts,transaction_sequence_number,transaction_line_sequence_number,transaction_quantity,transaction_type, transaction_item_status_code,transaction_total_retail_amount,transaction_total_retail_amount_with_discount,transaction_total_charged_amount,extension_tlog_code,transaction_currency
sku_122,EACH,GOOD,111,1,2478,2018-01-30T18:12:00.000Z,00999,2,1,SALE,MKDOWN,49.95,13.98,13.98,SFS,USD
sku_877,EACH,GOOD,222,6,2112,2018-01-30T18:47:00.000Z,00333,1,1,SALE,ACTIVE,15.95,6.00,6.00,SFS,USD
sku_747,EACH,GOOD,333,1,3344,2018-01-30T16:45:00.000Z,87878,1,-1,RETN,ACTIVE,6.95,-6.95,-6.95,SFS,USD
  4. Upload the t-log data by using one of the following methods.
    • Upload a single file to the <Input_Prefix>/UPLOAD/TLOG/ virtual folder on IBM Cloud® Object Storage.
    • Upload all files from a local directory to the <Input_Prefix>/UPLOAD/TLOG/ virtual folder on IBM Cloud Object Storage.
  5. Familiarize yourself with the file format, for example:
    • If the original file is called 2024-01-01-10-59-00.0000-3423-Availability.csv, for simplicity, you might call this file <myfilename>.csv.
    • If the data row fails to process, you receive the following error row.csv and the error summary.txt files: 
      <myfilename>_file_failed_row.csv. for the error rows only (all successful are not present here)
<myfilename>_file_failure_reason.txt are the summary file which provides the error code. Sterling Intelligent Promising automatically creates a prefix to store processed files based on their current state which includes:
      The error row.csv file is the original ingestion record that can't be processed. The error summary.txt file provides the reason for the processing failure.
  6. Understand the prefix that is created automatically by Sterling Intelligent Promising to store processed files that are based on the following current states:
    /completed
    All files that completed processing. It is important to clean up the completed files routinely to minimize the storage requirements on IBM Cloud Object Storage.
    /summary
    A data ingestion summary file that describes the error that occurred during execution. This summary file can be used in conjunction with the files that failed to process to help to understand the error code and the reason for failure.

Example

The system generates the following sample of the prefix generated by the data ingestion system:
  • /completed/<Input_Prefix>/sip-tlog/myinput
  • /summary/<Input_Prefix>/sip-tlog/myinput