Hi everyone, 

I recently built a chain in wdata to automate the preparation of a monthly journal entry using transaction details from a group of excel files created by a third party system. The chain then automatically uploads that journal entry to Oracle. Because this is a very common use case, I am sharing the recipe for how I did this because it might be useful to you and others.  

1. Save the file on an FTP server. Use security best practices to secure the FTP server connection to prevent unauthorized access (public/private certificates, username/password, block unrecognized ip addresses, etc)
2. Create a chain in Workiva and use the List Directory command in the SFTP library to get a list of the files.
3. Use the command group to iterate through the files. Iteratively use these commands to download the excel file, convert the file to a csv, run a pipeline to do any data prep needed on the file prior to saving it a table in wdata, and finally save the data to a wdata table. The names of the commands to achieve this are Download File, Worksheet to CSV, Run Pipeline, and Refresh Wdata table. 
4. Jumping out of the chain for this item. In the wdesk workspace create a table to hold your file data and then create a query in wdata that will produce a journal entry. Some intermediate to advanced SQL skills are needed here. Below is a skeleton of a query that I wrote to prepare a journal entry into the format needed for Oracle Cloud Financials. The basic premise would be the same for other systems, although the schema will be different.   

   SELECT --these are the fields needed for the oracle api to load a journal file to the interface table, this is a command within the Oracle connector library. 
   'NEW' as status_code,
   '300000001564007' as ledger_id,
   cast(DATE_FORMAT(t1."Report End Date", '%Y/%m/%d') as varchar(10)) as accounting_date,
   'Workiva' as je_source,
   '[journal entry category name]' as je_category,
   'USD' as currency,
   cast(DATE_FORMAT(now(), '%Y/%m/%d') as varchar(10)) as creation_date,
   'A' as actual_flag,
   t1.[field name] AS account_segment_1,
   t1.[field name] AS account_segment_2,
   t1.[field name] AS account_segment_3,
   t1.[field name] AS account_segment_4,
   t1.[field name] AS account_segment_5,
   t1.[field name] AS account_segment_6,
   t1.[field name] AS account_segment_7, -- etc. etc. 
   NULL as c_p,
   NULL as c_q,
   NULL as c_r,
   NULL as c_s,
   NULL as c_t,
   NULL as c_u,
   NULL as c_v,
   NULL as c_w,
   NULL as c_x,
   NULL as c_y,
   NULL as c_z,
   NULL as c_aa,
   NULL as c_ab,
   NULL as c_ac,
   NULL as c_ad,
   NULL as c_ae,
   NULL as c_af,
   NULL as c_ag,
   NULL as c_ah,
   NULL as c_ai,
   NULL as c_aj,
   NULL as c_ak,
   NULL as c_al,
   CASE
   WHEN t1.DR_amount > 0 THEN t1.DR_amount
   WHEN t1.CR_amount < 0 THEN t1.CR_amount*(-1)
   ELSE 0
   END AS DR,
   CASE
   WHEN t1.CR_amount > 0 THEN t1.CR_amount
   WHEN t1.DR_amount < 0 THEN t1.DR_amount*(-1)
   ELSE 0
   END AS CR,
   NULL as c_ao,
   NULL as c_ap,
   CONCAT('DC activity entry for period ended ',DATE_FORMAT(t1."Report End Date", '%m/%d/%Y')) as je_batch_name,
   CONCAT('DC activity entry for period ended ',DATE_FORMAT(t1."Report End Date", '%m/%d/%Y')) as je_batch_desc,
   NULL as c_as,
   CONCAT('DC activity entry for period ended ',DATE_FORMAT(t1."Report End Date", '%m/%d/%Y')) as je_name,
   CONCAT('DC activity entry for period ended ',DATE_FORMAT(t1."Report End Date", '%m/%d/%Y')) as je_desc,
   NULL as c_av,
   NULL as c_aw,
   NULL as c_ax,
   NULL as c_ay,
   t1."Line Description" as je_line_desc,
   NULL as c_ba,
   NULL as c_bb,
   NULL as c_bc,
   NULL as c_bd,
   NULL as c_be,
   NULL as c_bf,
   NULL as c_bg,
   NULL as c_bh,
   NULL as c_bi,
   NULL as c_bj,
   NULL as c_bk,
   NULL as c_bl,
   NULL as c_bm,
   NULL as c_bn,
   '10001' as "GROUP ID",
   NULL as c_bp,
   NULL as c_bq,
   NULL as c_br,
   NULL as c_bs,
   NULL as c_bt,
   NULL as c_bu,
   NULL as c_bv,
   NULL as c_bw,
   NULL as c_bx,
   NULL as c_by,
   NULL as c_bx,
   NULL as c_ca,
   NULL as c_cb,
   NULL as c_cc,
   NULL as c_cd,
   NULL as c_ce,
   NULL as c_cf,
   NULL as c_cg,
   NULL as c_ch,
   NULL as c_ci,
   NULL as c_cj,
   NULL as c_ck,
   NULL as c_cl,
   NULL as c_cm,
   NULL as c_cn,
   NULL as c_co,
   NULL as c_cp,
   DATE_FORMAT(t1."Accounting Date", '%b-%y') as accounting_period, -- the date format should match the format of your specific accounting period name configuration in Oracle. Our system has period names with abbreviated month names instead of numbers, which screws up sorted lists.  
   NULL as cr,
   NULL as cs,
   NULL as ct,
   NULL as cu,
   NULL as cv,
   NULL as cw,
   NULL as cx,
   NULL as cy,
   NULL as cz,
   NULL as da,
   NULL as db,
   NULL as dc,
   NULL as dd,
   NULL as de,
   NULL as df,
   NULL as dg,
   NULL as dh,
   NULL as di,
   NULL as dj,
   NULL as dk,
   NULL as dl,
   NULL as dm,
   NULL as dn,
   NULL as do,
   NULL as dp,
   NULL as dq,
   NULL as dr,
   NULL as ds,
   NULL as dt,
   NULL as du,
   NULL as dv,
   NULL as dw,
   NULL as dx,
   NULL as dy,
   NULL as dz,
   NULL as ea,
   NULL as eb,
   NULL as ec,
   NULL as ed,
   NULL as ee,
   NULL as ef,
   NULL as eg,
   NULL as eh,
   NULL as ei,
   NULL as ej,
   NULL as ek,
   NULL as el,
   NULL as em,
   NULL as en,
   NULL as eo,
   NULL as ep,
   NULL as eq,
   NULL as er,
   NULL as es,
   'END' as et
    FROM 
   (
   --the particular data we are pulling for this journal entry is organized in columns in addition to rows like a pivot table, 
   --so we used UNION to join multiple queries together to sum up the activity in columns and place it into rows for journal lines.
   --revenue transaction LINES
   SELECT
    "[fact table name]"."[field name]" AS "report column name", -- add whatever other fields you want to group data by 
    "[fact table name]"."[journal entry date field]" AS "Accounting Date", -- grab the accounting date
    'description of revenue transaction' AS "Line Description", --describe the revenue transaction in such a way that you can cross walk it to your chart of accounts using a table join.  
    NULL AS DR_amount,
    round(cast(sum("[table name]"."[example revenue column]") as double),2) AS CR_amount --sum up all of the financial activity across all rows for the column named "example revenue column"
   FROM
    "[environment id]"."[fact table id]" AS "[fact table name]" 
   GROUP BY [fields in select statement]
   
   --now we need to build the offsetting journal line, presumably to an asset account. 
   --to do this I am doing the same query as above but changing the line description
   -- and recording this as a debit instead of a credit and appending the two with a UNION
   UNION ALL
   SELECT
    "[fact table name]"."[field name]" AS "report column name",
    "[fact table name]"."[journal entry date field]" AS "Accounting Date",
    'description of asset transaction' AS "Line Description",
    round(cast(sum("[table name]"."[transaction amount field]") as double),2) AS DR_amount,
    NULL AS CR_amount
   FROM
    "[environment id]"."[table id]" AS "transaction type description" 
   GROUP BY [fields in select statement]
   ) t1 -- define the temporary table name as t1
   --next join the chart of accounts table using the transaction description present in t1.
   LEFT JOIN "[environment id]"."[dimension table id]" AS ledger_account_crosswalk ON ledger_account_crosswalk.activity_description = t1."Line Description"
   WHERE (DR_amount IS NOT NULL OR CR_amount IS NOT NULL)
   LIMIT 1000000

   This sql produces a query that exactly matches the upload format needed for our accounting system. 
5.  Jumping back to the chain. After completing the iterations for each file, I used the Run Query command to execute the query using the newly uploaded data. 
6. After executing the query, I used the Download Query Result command to save the query result to a csv file to be used later in the chain.
7. The sql query will throw the column headers in the result set, which is normally great, but Oracle Financials Cloud is picky and the journal entry import will fail if it has column headers, so I used the Overwrite Sheet Data command to save the journal entry to a spreadsheet in Workiva. This is also helpful for reviewing the journal entry for troubleshooting down the road if/when errors occur. 
8. Then I used the Get Sheet Data command to read that journal entry data back from the sheet where I just saved it (but reading the sheet from row 2 to remove the column headers) and convert it back to a csv file. 
9. From the Oracle Cloud Financials connector library, I used the Import Journals from Interface File command to take the journal entry and ship it off to Oracle Cloud Financials. 
10. I used the Import Journals command (also in the Oracle Cloud Financials connector library) to make Oracle read the newly uploaded file from their staging interface table and import the journal. 
11. Lastly, use the SFTP Connector - Move command to move the excel files from an 'unprocessed' folder to a 'processed' folder to avoid the same files from being processed multiple times, which would result in duplicate journals. 

Please let me know if you found this helpful, or if you would like to chat with me to discuss any questions you might have. Comment below or send me a message on linked in. www.linkedin.com/in/jeremy-pond-cpa-cfe-cgfm-36839227