2022-06-01 / Add-ons

PAGE UPDATED

Journal-TSV Create
Creates the Journal-Slip TSV. The transfer slip TSV of the sales is generated based on the Invoice data. To generate a file that can be imported into the accounting system, place a converter such as “TSV to Excel-CSV FILE” in the downstream process.
https://support.questetra.com/addons/journal-tsv-create/
2018-12-10 (C) Questetra, Inc. (MIT License)
Configs
  • A: Select STRING DATA for Slip Description *
  • B: Select NUMERIC DATA for Sales Amount (Single Record)
  • C: Select DATE DATA for Sales Record (Single Record)
  • D: Select NUMERIC DATA for Sales Amount (Multiple Records)
  • E: Select DATE DATA for First Sales Record (Next Month 1st Day)
  • F: Select NUMERIC DATA for Division Number
  • G: Select DATE DATA for Scheduled Due Date *
  • X: Select STRING DATA for TSV Data (update) *
  • H: Set Header Row (e.g. “ID,Date,DrAcc,DrCat,Debit,CrAcc,*”) #{EL}
  • I: Set Slip-ID Column Index (e.g. “0” if case ID,[1],[2],*)
  • J: Set Date Column Index (e.g. “1” if [0],Date,[2],*)
  • K: Set Date Format (e.g. “YYYY-MM-DD” “YYYY/MM/DD” “DD/MM/YYYY”)
  • L: Set Dr-Amt Column Indexes (e.g. “6”)
  • M: Set Cr-Amt Column Indexes (e.g. “12”)
  • N: Set Number of Decimal Places for Amount Division (“2” in USD)
  • O: Set Slip Description Column Indexes (e.g. “14”)
  • P: Set Record Tpl for Receivable Sales (e.g. “,,AR,,,,,,Sa,*”) #{EL}
  • Q: Set Record Tpl for Received Sales (e.g. “,,Rvd,,,,,,Sa,*”) #{EL}
  • R: Set Record Tpl for Payment of Receivable (e.g. “Bank,,na”) #{EL}
  • S: Set Record Tpl for Payment of Received (e.g. “Bank,,na”) #{EL}
Script
// Generator from Invoice Data to Journal Slip TSV
// (c) 2018, Questetra, Inc. (the MIT License)

// ◇Money Forward (journal_sample.csv)
// https://support.biz.moneyforward.com/account/guide/import-books/ib01.html
// 取引No,取引日,借方勘定科目,借方補助科目,借方税区分,借方部門,借方金額(円),借方税額,貸方勘定科目,貸方補助科目,貸方税区分,貸方部門,貸方金額(円),貸方税額,摘要,仕訳メモ,タグ,MF仕訳タイプ,決算整理仕訳,×作成日時,×最終更新日時
// ※「取引No」「取引日」「勘定科目」「金額」については入力必須項目

// ◇Freee (仕訳インポートその他(借方金額・貸方金額あり)_for_excel.csv)
// https://support.freee.co.jp/hc/ja/articles/204847430
// 日付,伝票番号,決算整理仕訳,借方勘定科目,借方科目コード,借方補助科目,借方補助科目コード,借方部門,借方金額,借方内税/外税,借方税区分,借方税額,貸方勘定科目,貸方科目コード,貸方補助科目,貸方補助科目コード,貸方部門,貸方金額,貸方内税/外税,貸方税区分,貸方税額,摘要
// ※「日付」「勘定科目」「金額」必須


main();
function main(){ //////// main() start ////////

//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA = configs.get( "conf_DataIdA" ) + ""; // required
const dataIdB = configs.get( "conf_DataIdB" ) + "";
const dataIdC = configs.get( "conf_DataIdC" ) + "";
const dataIdD = configs.get( "conf_DataIdD" ) + "";
const dataIdE = configs.get( "conf_DataIdE" ) + "";
const dataIdF = configs.get( "conf_DataIdF" ) + "";
const dataIdG = configs.get( "conf_DataIdG" ) + ""; // required
const dataIdX = configs.get( "conf_DataIdX" ) + ""; // required

let   headerRow = configs.get( "conf_HeaderRow" ) + "";
  if( headerRow === ""){headerRow = "取引No,取引日,借方勘定科目,借方補助科目,借方税区分,借方部門,借方金額(円),借方税額,貸方勘定科目,貸方補助科目,貸方税区分,貸方部門,貸方金額(円),貸方税額,摘要,仕訳メモ,タグ,MF仕訳タイプ,決算整理仕訳,作成日時,最終更新日時";}
let   colSlipId = configs.get( "conf_ColSlipId" ) + "";
  if( colSlipId === ""){colSlipId = 0;}else{colSlipId = Number(colSlipId);}
let   colDate   = configs.get( "conf_ColDate" )   + "";
  if( colDate   === ""){colDate   = 1;}else{colDate   = Number(colDate);}
let   ymdFormat = configs.get( "conf_YmdFormat" ) + "";
  if( ymdFormat === ""){ymdFormat   = "YYYY/MM/DD";}
let   colDrAmt  = configs.get( "conf_ColDrAmt" )  + "";
  if( colDrAmt  === ""){colDrAmt = 6 ;}else{colDrAmt  = Number(colDrAmt);}
let   colCrAmt  = configs.get( "conf_ColCrAmt" )  + "";
  if( colCrAmt  === ""){colCrAmt = 12;}else{colCrAmt  = Number(colCrAmt);}
const decimalPlaces  = configs.get( "conf_DecimalPlaces" ) - 0;
let   colSlipDescr = configs.get( "conf_ColSlipDescr" )  + "";
  if( colSlipDescr === ""){colSlipDescr = 14;}else{colSlipDescr  = Number(colSlipDescr);}


let   tplReceivableSales   = configs.get( "conf_TplReceivableSales" ) + "";
  if( tplReceivableSales   === ""){tplReceivableSales = ",,売掛金,,対象外,,,0,売上高,,課税売上 8%,,,0,,,,,,,";}
  const arrTplReceivableSales   = tplReceivableSales.split(",");
let   tplReceivedSales     = configs.get( "conf_TplReceivedSales" ) + "";
  if( tplReceivedSales     === ""){tplReceivedSales   = ",,前受金,,対象外,,,0,売上高,,課税売上 8%,,,0,,,,,,,";}
  const arrTplReceivedSales     = tplReceivedSales.split(",");
let   tplPaymentReceivable = configs.get( "conf_TplPaymentReceivable" ) + "";
  if( tplPaymentReceivable === ""){tplPaymentReceivable = ",,普通預金,,対象外,,,0,売掛金,,対象外,,,0,,,,,,,";}
  const arrTplPaymentReceivable = tplPaymentReceivable.split(",");
let   tplPaymentReceived   = configs.get( "conf_TplPaymentReceived" ) + "";
  if( tplPaymentReceived   === ""){tplPaymentReceived     = ",,普通預金,,対象外,,,0,前受金,,対象外,,,0,,,,,,,";}
  const arrTplPaymentReceived   = tplPaymentReceived.split(",");
// 'java.lang.String' to javascript primitive 'string' or 'number'



//// == Data Retrieving / ワークフローデータの参照 ==
const slipDescr            = engine.findDataByNumber( dataIdA ) + ""; // required
let   salesAmtSingle       = engine.findDataByNumber( dataIdB ) - 0;  // null-0 -> 0
let   salesAmtSingleDate   = engine.findDataByNumber( dataIdC ) + "";
let   salesAmtMultiple     = engine.findDataByNumber( dataIdD ) - 0;
let   salesAmtMultipleDate = engine.findDataByNumber( dataIdE ) + "";
let   salesAmtMultipleNum  = engine.findDataByNumber( dataIdF ) - 0;
const dueDate              = engine.findDataByNumber( dataIdG ) + ""; // required
// 'java.lang.String' to javascript primitive 'string' or 'number'

const dateDueDate          = new Date( dueDate );
const myNumOfCols          = headerRow.split(",").length;

/// Input Errors
// Number of Columns
if(       myNumOfCols !== tplReceivableSales.split(",").length ){
  engine.log( "The number of commas in each Config is not the same. (tplReceivableSales)" );
  return;
}else if( myNumOfCols !== tplReceivedSales.split(",").length ){
  engine.log( "The number of commas in each Config is not the same. (tplReceivedSales)" );
  return;
}else if( myNumOfCols !== tplPaymentReceivable.split(",").length ){
  engine.log( "The number of commas in each Config is not the same. (tplPaymentReceivable)" );
  return;
}else if( myNumOfCols !== tplPaymentReceived.split(",").length ){
  engine.log( "The number of commas in each Config is not the same. (tplPaymentReceived)" );
  return;
}
// Zero Amount
if(salesAmtSingle === 0 && salesAmtMultiple === 0){ //Case 0: Error exit
  engine.log( "Total Sales Amount is zero." );
  return;
}


//// == Calculating / 演算 ==
const dateToday = new Date();
const todaySerial = (dateToday.getFullYear() % 100) * 10000 + (dateToday.getMonth()+1) * 100 + dateToday.getDate();
// e.g. 180131

let tmpSlipId = (todaySerial % 10000) * 1000000 + (processInstance.getProcessInstanceId() % 10000) * 100;
// e.g. (0)131 123400 (3 or 4digits, 4 digits, 2 digits: for Unsigned Int limit of Money Forward)

/// If the number of assumed slips per day does not exceed 100 , / 想定伝票数が日次100枚以下の場合の改変例
// let tmpSlipId = todaySerial * 10000 + (processInstance.getProcessInstanceSequenceNumber() % 100) * 100;
// e.g. 1801319900 (6 digits, 2 digits, 2 digits)

/// If the number of assumed slips per day exceeds 10000, / 想定伝票数が日次10000枚を超える場合の改変例
// e.g. 1234567 00 (max 7 digits, 2 digits) // No Date Info 日付情報を削除
// let tmpSlipId = (processInstance.getProcessInstanceId() % 10000000) * 100;

let myTsv    = "";
myTsv += headerRow.replace(/,/g, "\t") + "\n";

//Case 1: Only Single Sales / 一括売上だけの場合
if(salesAmtSingle !== 0 && salesAmtMultiple === 0){
  let dateSalesAmtSingleDate = new Date( salesAmtSingleDate );

  if( dateSalesAmtSingleDate.getTime() <= dateDueDate.getTime() ){
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivableSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
    tmpArrSales[colDrAmt]  = salesAmtSingle;
    tmpArrSales[colCrAmt]  = salesAmtSingle;
    tmpArrSales[colSlipDescr] = slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";

    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceivable.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = salesAmtSingle;
    tmpArrPay[colCrAmt]    = salesAmtSingle;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }else{
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivedSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
    tmpArrSales[colDrAmt]  = salesAmtSingle;
    tmpArrSales[colCrAmt]  = salesAmtSingle;
    tmpArrSales[colSlipDescr] = slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";

    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceived.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = salesAmtSingle;
    tmpArrPay[colCrAmt]    = salesAmtSingle;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }

}

//Case 2:  Including Multiple Sales / 分割売上を含む場合
if(salesAmtMultiple !== 0){
  if(salesAmtMultipleNum === 0){
    engine.log( "'0' can not be used for division number." );
    return;
  }

  // Subtotal of ReceivableSales and ReceivedSales
  let myReceivableSales = 0;
  let myReceivedSales = 0;


  //// Single 
  if(salesAmtSingle !== 0){
    let dateSalesAmtSingleDate = new Date( salesAmtSingleDate );

    if( dateSalesAmtSingleDate.getTime() <= dateDueDate.getTime() ){
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivableSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
      tmpArrSales[colDrAmt]  = salesAmtSingle;
      tmpArrSales[colCrAmt]  = salesAmtSingle;
      tmpArrSales[colSlipDescr] = slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivableSales += salesAmtSingle;
    }else{
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivedSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
      tmpArrSales[colDrAmt]  = salesAmtSingle;
      tmpArrSales[colCrAmt]  = salesAmtSingle;
      tmpArrSales[colSlipDescr] = slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivedSales += salesAmtSingle;
    }
  }


  //// Multiple

  // Sales for each month
  // The remainder is added to the first month. / 商の剰余は初月に加算
  // "101/10" is divided into "10.10" * 10, in the case of two decimal places.
  // "101/10" is divided into "11" * 1 and "10" * 9, in the case of 0 decimal place.
  const monthlySalesFollow = Math.floor( salesAmtMultiple / salesAmtMultipleNum * Math.pow(10, decimalPlaces) ) / Math.pow(10, decimalPlaces);
  const monthlySalesFirst  = salesAmtMultiple - monthlySalesFollow * ( salesAmtMultipleNum - 1 );

  let arrSalesAmtMultipleDate  = new Array( salesAmtMultipleNum );

  // First Month
  arrSalesAmtMultipleDate[0]   = new Date( salesAmtMultipleDate );
  if( arrSalesAmtMultipleDate[0].getTime() <= dateDueDate.getTime() ){
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivableSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[0], ymdFormat );
    tmpArrSales[colDrAmt]  = monthlySalesFirst;
    tmpArrSales[colCrAmt]  = monthlySalesFirst;
    tmpArrSales[colSlipDescr] = funcMonthStr( arrSalesAmtMultipleDate[0] ) + " @ " + slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";
    myReceivableSales += monthlySalesFirst;
  }else{
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivedSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[0], ymdFormat );
    tmpArrSales[colDrAmt]  = monthlySalesFirst;
    tmpArrSales[colCrAmt]  = monthlySalesFirst;
    tmpArrSales[colSlipDescr] = funcMonthStr( arrSalesAmtMultipleDate[0] ) + " @ " + slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";
    myReceivedSales += monthlySalesFirst;
  }

  // Following Months
  // Record on 1st of each month / 次月以降は1日に売上計上
  for( let i=1; i < salesAmtMultipleNum; i++){
    arrSalesAmtMultipleDate[i] = new Date( 
                                     arrSalesAmtMultipleDate[0].getFullYear(),
                                     arrSalesAmtMultipleDate[0].getMonth() + i,
                                     1); // 1st of each month
    if( arrSalesAmtMultipleDate[i].getTime() <= dateDueDate.getTime() ){
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivableSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[i], ymdFormat );
      tmpArrSales[colDrAmt]  = monthlySalesFollow;
      tmpArrSales[colCrAmt]  = monthlySalesFollow;
      tmpArrSales[colSlipDescr] = funcMonthStr( arrSalesAmtMultipleDate[i] ) + " @ " + slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivableSales += monthlySalesFollow;
    }else{
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivedSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[i], ymdFormat );
      tmpArrSales[colDrAmt]  = monthlySalesFollow;
      tmpArrSales[colCrAmt]  = monthlySalesFollow;
      tmpArrSales[colSlipDescr] = funcMonthStr( arrSalesAmtMultipleDate[i] ) + " @ " + slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivedSales += monthlySalesFollow;
    }
  }
  if(myReceivableSales !== 0 && myReceivedSales !== 0){
    tmpSlipId++;
    let tmpArrPay1         = arrTplPaymentReceivable.concat();
    tmpArrPay1[colSlipId]  = tmpSlipId + "";
    tmpArrPay1[colDate]    = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay1[colDrAmt]   = myReceivableSales + myReceivedSales;
    tmpArrPay1[colCrAmt]   = myReceivableSales;
    tmpArrPay1[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay1.join("\t");
    myTsv += "\n";
    let tmpArrPay2         = arrTplPaymentReceived.concat();
    tmpArrPay2[colSlipId]  = tmpSlipId + "";
    tmpArrPay2[colDate]    = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay2[colDrAmt]   = 0;
    tmpArrPay2[colCrAmt]   = myReceivedSales;
    tmpArrPay2[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay2.join("\t");
  }else if(myReceivableSales === 0){
    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceived.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = myReceivableSales + myReceivedSales;
    tmpArrPay[colCrAmt]    = myReceivedSales;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }else if(myReceivedSales === 0){
    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceivable.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = myReceivableSales + myReceivedSales;
    tmpArrPay[colCrAmt]    = myReceivableSales;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }
}

//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdX, myTsv );

} //////// main() end ////////


function funcDateStr( dateObj, formatStr ){
  formatStr = formatStr.replace(/YYYY/g, dateObj.getFullYear());
  formatStr = formatStr.replace(/MM/g,   ('00' + (dateObj.getMonth() + 1)).slice(-2));
  formatStr = formatStr.replace(/DD/g,   ('00' +  dateObj.getDate()).slice(-2));
  return formatStr;
}

function funcMonthStr( dateObj ){
  let myStr = dateObj.getFullYear() + "-" + ("00" + (dateObj.getMonth()+1)).slice(-2);
  return myStr;
}

Download

Journal-Tsv-Create.xml

Capture

Notes

  1. Each row (each record) of the TSV data corresponds to one sheet of journal slip.
  2. You can not import TSV data into the accounting system (accounting software/accounting cloud) as it is. To generate “File to be imported”, place an automatic conversion Step (converter) such as “TSV to Excel-CSV FILE” at the downstream.
  3. Regarding journal slip, if the sales recording date is the same as or before the “settlement date”, the “accounts receivable sales row template” will be applied. and if the sales recording date is after the “settlement date”, “advance payment sales Row template “will be applied
  4. The date on journal slip for payment collection to be generated is the”settlement due date” (composite entry). (The actual payment date information needs to be corrected on the accounting system side)
  5. The “header row” of the import file differs depending on the accounting system. Please set it by comma delimiter in the config item “H”. If not specified anything, “Heading row” for “MF cloud accounting” (21 items) will be automatically set.
    • MF Cloud Accounting: Transaction No., Trading date, Debit account item, Debit supplementary item, Debit tax category, Debit department, Debit amount (yen), Debit amount, Credit account item, Credit supplementary item, Credit Tax category, Credit department, Credit amount (yen), Credit tax amount, Description, Notes, Tag, MF journal type, Adjusting entry, Creation date and time, Last update date and time
    • Freee: Date, Slip number, Adjusting entry, Debit account item, Debit item code, Debit supplementary item, Debit supplementary item code, Debit department, Debit amount, Debit tax in/excluded, Debt tax category, Debit tax amount, Credit account item, Credit item code, Credit supplementary item, Credit supplementary item code, Credit department, Credit amount, Credit tax in/excluded, Credit tax category, Credit tax amount, Description
  6. The data insertion position and format in each column varies depending on the accounting system. Please set in the Config item of between “I” and “O”. When not specified, the specification of “MF cloud accounting” is automatically applied.
  7. Set the template of each row in the Config item of between “P” and “S” for the four patterns which are sales as account-receivable, sales as Received, collection of Receivable, collection of Received. When not specified, the specification for “MF cloud accounting” is automatically applied.
  8. In case if you want to reference the workflow data to be set on the template, set it by EL expression. For example, if you set “# {data [’14’]}” at the position of the supplementary subject of the account item “Accounts receivable”, the Issue data is automatically referenced and the name of the supplementary subject such as “AAA Co., Ltd.” is recorded. Alternatively, if you set “# {data [’13’]}” to the position of the supplementary item of the account item “Sales”, the item data is automatically referenced and the supplementary subject name such as “SaaS” is recorded.
    • ,, Accounts receivable, # {data [’14’]}, not eligible ,,, 0, sales, # {data [’13’]}, taxable sales 8% ,,, 0 ,, ,,,,,
  9. If you are a developer with JavaScript knowledge, you can also customize the format of “Transaction No.” (transfer slip ID). Edit the assignment expression of the variable “tmpSlipId” in the definition file.
    • In the case of “MF Cloud Accounting”, for example, an error occurs if “Maximum value of Unsigned Int” (4,294,967,295) is exceeded

See also

Converter (TSV to Excel-CSV FILE)

1 thought on “Journal-TSV Create”

  1. Pingback: Journal Slip TSV from Invoice Data – Questetra Support

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: