# XDMF_YF900_0 / 取引集計処理の起動 _言語: ja / データ版数 (設定の最終更新): 2024-07-11 11:38:19_ ## 要約 | 項目 | 値 | |------|------| | FUNC\_ID | \`XDMF\_YF900\_0\` | | 機能名 | 取引集計処理の起動 | | 機能タイプ \(APP\_KBN\) | BAT / 検索ボタン無し | | BG\_L | 4 | | BG\_M | 11 | | ブロック数 | 1 | | 項目数 | 3 | | 読み取りテーブル | 9 | | 書き込みテーブル | 8 | | DB 接続先 | 1 | ### 機能概要 入力認可過去月度以降の取引について、年度別・月度別の取引高を集計します。 集計処理は入力認可過去月度以降のデータは再作成されます。 ※実行によりデータが再作成されるテーブル | ID | テーブル名 | | --- | --- | | YT010 | 年次取引サマリ | | YT020 | 月次取引サマリ | | YT100 | 月次得意先別サマリ | | YT110 | 月次売掛取引サマリ | | YT200 | 月次仕入先別サマリ | | YT210 | 月次買掛取引サマリ | | YT300 | 月次在庫品別サマリ | | YT310 | 月次在庫取引サマリ | ## 機能設定 ### 機能説明 | 項目 | 値 | |------|----| | 機能ID | XDMF\_YF900\_0 | | 機能名 | 取引集計処理の起動 | | 機能タイプ | 検索ボタン無し \(BAT\) | | ADMIN\_ID | | | 業務分類 大 | XTEA生産管理 \(4\) | | 業務分類 中 | 統計 \(11\) | | 業務分類 小 | | | 作成 | 2024\-07\-11 11:38:09.0 demo | | 最終更新 | 2024\-07\-11 11:38:09.0 demo | | ヘルプリンク名 \(HELP\_LINK\_NM\) | | | ヘルプURL \(HELP\_URL\) | | #### 機能概要 入力認可過去月度以降の取引について、年度別・月度別の取引高を集計します。 集計処理は入力認可過去月度以降のデータは再作成されます。 ※実行によりデータが再作成されるテーブル | ID | テーブル名 | | --- | --- | | YT010 | 年次取引サマリ | | YT020 | 月次取引サマリ | | YT100 | 月次得意先別サマリ | | YT110 | 月次売掛取引サマリ | | YT200 | 月次仕入先別サマリ | | YT210 | 月次買掛取引サマリ | | YT300 | 月次在庫品別サマリ | | YT310 | 月次在庫取引サマリ | ### アプリケーション設定 #### 機能レベル設定一覧 _TLN_M_GENERAL_APP (FUNC_SEQ=-1) に登録されている全ての設定値。非表示フラグや個別セクションで重複して表示される設定も含む。_ | ボタン | 状態 | FUNC\_KBN | |------|------|------| | 機能タイプ | 検索ボタン無し | APP\_KBN=BAT | | ボタンの非表示 \> Excel出力 | ON \[1\] | EXCEL\_BUTTON\_HDN\_FLG | | ボタンの非表示 \> PDF出力 | ON \[1\] | PDF\_BUTTON\_HDN\_FLG | | 起動Window状態 | 別Window \(既存あれば上書き\) \[NORMAL\] | OPEN\_WINDOW\_MODE | #### 標準ボタン非表示設定 | ボタン | FUNC\_KBN | 状態 | |------|------|------| | PDF出力 | PDF\_BUTTON\_HDN\_FLG | 非表示 | | Excel出力 | EXCEL\_BUTTON\_HDN\_FLG | 非表示 | | Excel更新 | EXCELUPDATE\_BUTTON\_HDN\_FLG | 表示 | | クリア | CLEAR\_BUTTON\_HDN\_FLG | 表示 | | 検索 | SEARCH\_BUTTON\_HDN\_FLG | 表示 | | 戻る | BACK\_BUTTON\_HDN\_FLG | 表示 | | 確定 | EXECUTE\_BTN\_HDN\_FLG | 表示 | | 参照 | VIEW\_BUTTON\_HDN\_FLG | 表示 | | 画面設定 | COL\_BUTTON\_HDN\_FLG | 表示 | | 検索条件保存 | COND\_SAVE\_HDN\_FLG | 表示 | | 検索条件タイトル | IS\_CONDITION\_TITLE\_OFF | 表示 | | ブロック名 | BLOCK\_NAME\_HDN\_FLG | 表示 | ### ユーザグループ | FUNC\_ATH\_ID | グループ名 | |------|------| | system | システム管理 | ## 検索条件 (集約) _(条件項目なし)_ ## ブロック設定 ### ブロック構成 | \# | TYPE | TITLE | |------|------|------| | BLOCK 1 | CARD | 取引集計 | ### BLOCK 1: 取引集計 [CARD] #### SELECT 式 ```sql SELECT ZT010.TXNAME as TXNAME ,ZT010.TXVALUE as TXVALUE ,'システム変数の「入力認可過去月度」以降の取引実績にもとづいて、 年度別・月度別の取引高が集計されます。' as TXMEMO FROM ZT010 WHERE ZT010.IDVARIANT = 'ALLOWED_FISCAL_MONTH' ``` #### 項目情報 | 並び順 | 物理項目名 | 表示名 | キー | 更新 | 検索 | 必須 | 改行 | グループ名 | 表示形式 | データ型 | 桁 | 表示桁 | 行数 | コンポーネント | 識別コード | |------|------|------|------|------|------|------|------|------|------|------|------|------|------|------|------| | 1 | TXNAME | 名称 | | | | | | | TEXT:半角、全角混在\(Byte\) | VARCHAR2 | 50 | | | | | | 2 | TXVALUE | 現在の値\(年度・月序\) | | | | | ○ | | TEXT:半角、全角混在\(Byte\) | VARCHAR2 | 100 | | | | | | 3 | TXMEMO | 注意事項 | | | | | ○ | | TEXT:半角、全角混在\(Byte\) | CHAR | 500 | 100 | 3 | | | #### 詳細設定 ##### 基本設定 (全ブロック共通) (1件) | 設定種別 | 値 | FUNC\_KBN | |------|------|------| | ブロック名フィールドを非表示 | ON \[1\] | BLOCK\_NAME\_HDN\_FLG | ##### その他 (LIST/CARD/帳票/権限など) (1件) | 設定種別 | 値 | FUNC\_KBN | |------|------|------| | ボタンの非表示 \> 画面設定 | ON \[1\] | COL\_BUTTON\_HDN\_FLG | #### 処理呼出 ##### ボタン: 取引集計処理の実行 · JavaScript呼出 | 設定 | 内容 | | --- | --- | | ボタン名 | 取引集計処理の実行 | | ライブラリ全て | ON | | 処理後検索 | ON | | 確認ダイアログ | ON | | コード | (下のコードブロック参照) | _コード_ ```javascript var itemColNameTbl_YT010 = [ 'DTNEND' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'AMKIKANSEI' ,'AMURIKAKE' ,'AMKAIKAKE' ,'AMZAIKO' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT010(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT010', itemDataList , itemColNameTbl_YT010 ); } function updateYT010(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereList = new Array(); whereList.push(whereTbl1); TALON.updateByMap( 'YT010', itemDataList , itemColNameTbl_YT010 , whereList ); } var itemColNameTbl_YT020 = [ 'DTNEND' ,'DTMSEQ' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'AMKIKANSEI' ,'AMURIKAKE' ,'AMKAIKAKE' ,'AMZAIKO' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT020(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT020', itemDataList , itemColNameTbl_YT020 ); } function updateYT020(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); TALON.updateByMap( 'YT020', itemDataList , itemColNameTbl_YT020 , whereList ); } var itemColNameTbl_YT100 = [ 'DTNEND' ,'DTMSEQ' ,'IDTOKUISAKI' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMURIKAKE' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT100(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT100', itemDataList , itemColNameTbl_YT100 ); } function updateYT100(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDTOKUISAKI' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT100', itemDataList , itemColNameTbl_YT100 , whereList ); } var itemColNameTbl_YT200 = [ 'DTNEND' ,'DTMSEQ' ,'IDSIIRESAKI' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKAIKAKE' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT200(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT200', itemDataList , itemColNameTbl_YT200 ); } function updateYT200(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDSIIRESAKI' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT200', itemDataList , itemColNameTbl_YT200 , whereList ); } var itemColNameTbl_YT300 = [ 'DTNEND' ,'DTMSEQ' ,'IDHINMOKU' ,'QTKISYUZAI' ,'QTKIKANNYUKO' ,'QTKIKANSYUKKO' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'QTKIKANKAI' ,'AMKIKANKAI' ,'QTKIKANSEI' ,'AMKIKANSEI' ,'QTKIKANURI' ,'AMKIKANURI' ,'AMKIKANARARI' ,'QTZAIKO' ,'AMZAIKO' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT300(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT300', itemDataList , itemColNameTbl_YT300 ); } function updateYT300(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDHINMOKU' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT300', itemDataList , itemColNameTbl_YT300 , whereList ); } function commWhere(_NOW_NENDO, _NOW_MONTH) { var sql = "WHERE FGVALID = 'T' " + " AND KBAKADEN = '1' " + " AND ( " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + " ) "; return sql; } function exec() { //機能ＩＤセット（Ver3.1用） _FUNC_ID = 'XDMF_YF900_0'; /* 事前準備 */ if (TALON.getDbConfig().isMssql()) { var itemSelectList = TALON.select( "SELECT " + " TLN_CHAR_TO_NUMBER(SUBSTRING(TXVALUE,1,4)) AS NOW_NENDO " + ",SUBSTRING(TXVALUE,5,2) AS NOW_MONTH " + "FROM ZT010 " + "WHERE IDVARIANT = 'ALLOWED_FISCAL_MONTH' " ); } else { var itemSelectList = TALON.select( "SELECT " + " TLN_CHAR_TO_NUMBER(SUBSTR(TXVALUE,1,4)) AS NOW_NENDO " + ",SUBSTR(TXVALUE,5,2) AS NOW_MONTH " + "FROM ZT010 " + "WHERE IDVARIANT = 'ALLOWED_FISCAL_MONTH' " ); } var lineDataMap = itemSelectList[0]; var _NOW_NENDO = lineDataMap['NOW_NENDO']; var _NOW_MONTH = lineDataMap['NOW_MONTH']; var itemSelectList = TALON.select( "SELECT " + " TLN_SYSDATE() AS SYS_DATE " + "FROM TLN_M_HANYO_CODE_MAIN " + "WHERE SIKIBETU_CODE = 'TLN_GENERAL_JAVASCRIPT_ENGINE' " ); var lineDataMap = itemSelectList[0]; var _SYS_DATE = lineDataMap['SYS_DATE']; var _TRU_YEAR = getNENDO(_SYS_DATE); var _TRU_MSEQ = getGMSEQ(_SYS_DATE); /* 初期化 */ //-------------- //YT010の削除 TALON.delete(" DELETE FROM YT010 WHERE DTNEND >= " + _NOW_NENDO + " "); //-------------- //YT010の登録 if ( parseInt(_NOW_NENDO) <= parseInt(_TRU_YEAR)) { for (var i = parseInt(_NOW_NENDO); i <= parseInt(_TRU_YEAR); i++) { var itemDataList = new Array(); itemDataList['DTNEND'] = String(i); insertYT010(itemDataList); } } //-------------- //YT020の削除 //YT100の削除 //YT200の削除 //YT300の削除 TALON.delete(" DELETE FROM YT020 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT100 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT200 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT300 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT020の登録 //YT100の登録 //YT200の登録 //YT300の登録 var _NOW_NENDO_W = ((parseInt(_NOW_NENDO) * 100) + parseInt(_NOW_MONTH)); var _TRU_YEAR_W = ((parseInt(_TRU_YEAR) * 100) + parseInt(_TRU_MSEQ) ); if ( _NOW_NENDO_W <= _TRU_YEAR_W) { tln_com_log("■[_NOW_NENDO_W] : " + _NOW_NENDO_W); tln_com_log("■[_TRU_YEAR_W] : " + _TRU_YEAR_W); for (var i = parseInt(_NOW_NENDO_W); i <= parseInt(_TRU_YEAR_W); i++) { var itemDataList = new Array(); itemDataList['DTNEND'] = parseInt(String(i).substr(0,4)); itemDataList['DTMSEQ'] = parseInt(String(i).substr(4,2)); itemDataList['AMKISYUURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANJYU'] = 0; itemDataList['AMKIKANGENKA'] = 0; itemDataList['AMKISYUKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['AMKIKANSIHA'] = 0; itemDataList['AMKISYUZAI'] = 0; itemDataList['AMKIKANNYUKO'] = 0; itemDataList['AMKIKANSYUKKO'] = 0; itemDataList['AMKIKANSEI'] = 0; itemDataList['AMURIKAKE'] = 0; itemDataList['AMKAIKAKE'] = 0; itemDataList['AMZAIKO'] = 0; itemDataList['AMKIKANARARI'] = 0; insertYT020(itemDataList); var itemSelectList = TALON.select( "SELECT IDTOKUISAKI FROM ET010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANJYU'] = 0; itemDataList['AMKIKANGENKA'] = 0; itemDataList['AMURIKAKE'] = 0; itemDataList['AMKIKANARARI'] = 0; insertYT100(itemDataList); } var itemSelectList = TALON.select( "SELECT IDSIIRESAKI FROM DT010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDSIIRESAKI'] = lineDataMap['IDSIIRESAKI']; itemDataList['AMKISYUKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['AMKIKANSIHA'] = 0; itemDataList['AMKAIKAKE'] = 0; insertYT200(itemDataList); } var itemSelectList = TALON.select( "SELECT IDHINMOKU FROM BT010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKISYUZAI'] = 0; itemDataList['QTKIKANNYUKO'] = 0; itemDataList['QTKIKANSYUKKO'] = 0; itemDataList['AMKISYUZAI'] = 0; itemDataList['AMKIKANNYUKO'] = 0; itemDataList['AMKIKANSYUKKO'] = 0; itemDataList['QTKIKANKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['QTKIKANSEI'] = 0; itemDataList['AMKIKANSEI'] = 0; itemDataList['QTKIKANURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANARARI'] = 0; itemDataList['QTZAIKO'] = 0; itemDataList['AMZAIKO'] = 0; insertYT300(itemDataList); } if (String(i).substr(4,2) =="12"){ i = ((parseInt(String(i).substr(0,4)) + 1) * 100) } } } //-------------- //YT110の削除 TALON.delete(" DELETE FROM YT110 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT210の削除 TALON.delete(" DELETE FROM YT210 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT310の削除 TALON.delete(" DELETE FROM YT310 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); /* 処理１ */ /* *************************** */ //売掛増減履歴集計// //----------------------------- var tmp = "売掛増減履歴集計：YT110の登録" tln_com_log(tmp); //YT110の登録 TALON.insert( "INSERT INTO YT110( " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",KBURIHURI " + ",AMTORIHIKI " + ",AMTAX " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",case when (KBURIHURI is null) then ' ' else KBURIHURI end as KBURIHURI " + ",SUM( AMTORIHIKI ) AS AMTORIHIKI " + ",SUM( AMTAX ) AS AMTAX " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM ET100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",KBURIHURI " ) var tmp = "売掛増減履歴集計：YT110の更新" tln_com_log(tmp); //YT100の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",case when AMKIKANURI is null then 0 else AMKIKANURI end AMKIKANURI " + ",case when AMKIKANJYU is null then 0 else AMKIKANJYU end AMKIKANJYU " + ",case when AMKIKANGENKA is null then 0 else AMKIKANGENKA end AMKIKANGENKA " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDTOKUISAKI " + " ,SUM( CASE WHEN (KBURITORI = '00' OR KBURITORI = '01' OR KBURITORI = '11' OR KBURITORI = '02' OR KBURITORI = '10') THEN AMZOUGEN ELSE 0 END) AS AMKIKANURI " + " ,SUM( CASE WHEN (KBURITORI = '90' OR KBURITORI = '91' OR KBURITORI = '92') THEN AMZOUGEN ELSE 0 END) AS AMKIKANJYU " + " ,SUM( " + " CASE " + " WHEN (KBURITORI = '00' OR KBURITORI = '01' OR KBURITORI = '11') THEN AMGENKA " + " WHEN (KBURITORI = '02' OR KBURITORI = '10') THEN (AMGENKA * -1) " + " ELSE 0 END " + " ) AS AMKIKANGENKA " + " FROM ET100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDTOKUISAKI " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; updateYT100(itemDataList); } //売掛残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "売掛残高更新：YT100の更新" tln_com_log(tmp); //YT100の更新 var itemSelectList = TALON.select( "SELECT " + " YT100.DTNEND " + ",YT100.DTMSEQ " + ",YT100.IDTOKUISAKI " + ",YT100.AMKISYUURI " + ",YT100.AMKIKANURI " + ",YT100.AMKIKANJYU " + ",YT100.AMKIKANGENKA " + ",YT100.AMURIKAKE " + ",YT100.AMKIKANARARI " + ",ET010.AMKISYUURIKAKE " + ",( (YT100.DTNEND * 100) + YT100.DTMSEQ ) as KIJUN " + "FROM YT100 " + "INNER JOIN ET010 " + " ON ET010.IDTOKUISAKI = YT100.IDTOKUISAKI " + "WHERE ( (YT100.DTNEND * 100) + YT100.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT100.IDTOKUISAKI " + ",YT100.DTNEND " + ",YT100.DTMSEQ " ); var iLoop = 0; var _PRE_IDTOKUISAKI = ''; var _PRE_AMURIKAKE = 0; var _AMKISYUURI = 0; var _AMURIKAKE = 0; var _AMKIKANARARI = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDTOKUISAKI != lineDataMap['IDTOKUISAKI']) { _PRE_IDTOKUISAKI = lineDataMap['IDTOKUISAKI']; _PRE_AMURIKAKE = 0 iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0 && lineDataMap['KIJUN'] == _FR ) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_AMURIKAKE = lineDataMap['AMURIKAKE']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_AMURIKAKE = lineDataMap['AMKISYUURIKAKE']; } _AMKISYUURI = _PRE_AMURIKAKE; //期末売掛残高算出 _AMURIKAKE = _AMKISYUURI + lineDataMap['AMKIKANURI'] - lineDataMap['AMKIKANJYU']; //期間粗利額 _AMKIKANARARI = lineDataMap['AMKIKANURI'] - lineDataMap['AMKIKANGENKA']; //期末を前月期末に保持 _PRE_AMURIKAKE = _AMURIKAKE; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUURI'] = _AMKISYUURI; itemDataList['AMURIKAKE'] = _AMURIKAKE; itemDataList['AMKIKANARARI'] = _AMKIKANARARI; updateYT100(itemDataList); } iLoop = iLoop + 1; } //売掛年月集計// //----------------------------- var tmp = "売掛年月集計：YT020の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM( AMKISYUURI ) is null then 0 else SUM( AMKISYUURI ) end AS AMKISYUURI " + ",case when SUM( AMKIKANURI ) is null then 0 else SUM( AMKIKANURI ) end AS AMKIKANURI " + ",case when SUM( AMKIKANJYU ) is null then 0 else SUM( AMKIKANJYU ) end AS AMKIKANJYU " + ",case when SUM( AMKIKANGENKA ) is null then 0 else SUM( AMKIKANGENKA ) end AS AMKIKANGENKA " + ",case when SUM( AMURIKAKE ) is null then 0 else SUM( AMURIKAKE ) end AS AMURIKAKE " + ",case when SUM( AMKIKANARARI ) is null then 0 else SUM( AMKIKANARARI ) end AS AMKIKANARARI " + "FROM YT100 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUURI'] = lineDataMap['AMKISYUURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; itemDataList['AMURIKAKE'] = lineDataMap['AMURIKAKE']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT020(itemDataList); } var tmp = "売掛年月集計：YT010の更新" tln_com_log(tmp); //YT010の更新 var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMURIKAKE IS NULL THEN 0 ELSE YT010.AMURIKAKE END AS AMKISYUURI " + ",MN.AMKIKANURI " + ",MN.AMKIKANJYU " + ",MN.AMKIKANGENKA " + ",CASE WHEN YT010.AMURIKAKE IS NULL THEN 0 ELSE YT010.AMURIKAKE END + MN.AMKIKANURI - MN.AMKIKANJYU AS AMURIKAKE " + ",MN.AMKIKANARARI " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM( AMKISYUURI ) is null then 0 else SUM( AMKISYUURI ) end AS AMKISYUURI " + " ,case when SUM( AMKIKANURI ) is null then 0 else SUM( AMKIKANURI ) end AS AMKIKANURI " + " ,case when SUM( AMKIKANJYU ) is null then 0 else SUM( AMKIKANJYU ) end AS AMKIKANJYU " + " ,case when SUM( AMKIKANGENKA ) is null then 0 else SUM( AMKIKANGENKA ) end AS AMKIKANGENKA " + " ,case when SUM( AMURIKAKE ) is null then 0 else SUM( AMURIKAKE ) end AS AMURIKAKE " + " ,case when SUM( AMKIKANARARI ) is null then 0 else SUM( AMKIKANARARI ) end AS AMKIKANARARI " + " FROM YT100 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['AMKISYUURI'] = lineDataMap['AMKISYUURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; itemDataList['AMURIKAKE'] = lineDataMap['AMURIKAKE']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT010(itemDataList); } /* 処理２ */ /* *************************** */ //買掛増減履歴集計// //----------------------------- var tmp = "買掛増減履歴集計：YT210の登録" tln_com_log(tmp); //YT210の登録 TALON.insert( "INSERT INTO YT210 ( " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",KBKAIHURI " + ",AMTORIHIKI " + ",AMTAX " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",case when (KBKAIHURI is null) then ' ' else KBKAIHURI end as KBKAIHURI " + ",SUM(AMTORIHIKI) AS AMTORIHIKI " + ",SUM(AMTAX) AS AMTAX " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM DT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",KBKAIHURI " ) var tmp = "買掛増減履歴集計：YT200の更新" tln_com_log(tmp); //YT200の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",case when AMKIKANKAI is null then 0 else AMKIKANKAI end AS AMKIKANKAI " + ",case when AMKIKANSIHA is null then 0 else AMKIKANSIHA end AS AMKIKANSIHA " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDSIIRESAKI " + " ,SUM( CASE WHEN (KBKAITORI = '00' OR KBKAITORI = '01' OR KBKAITORI = '11' OR KBKAITORI = '02' OR KBKAITORI = '10') THEN AMZOUGEN ELSE 0 END ) AS AMKIKANKAI " + " ,SUM( CASE WHEN (KBKAITORI = '90') THEN AMZOUGEN END ) AS AMKIKANSIHA " + " FROM DT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDSIIRESAKI " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDSIIRESAKI'] = lineDataMap['IDSIIRESAKI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; updateYT200(itemDataList); } //買掛残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "買掛残高更新：YT200の更新" tln_com_log(tmp); //YT200の更新 var itemSelectList = TALON.select( "SELECT " + " YT200.DTNEND " + ",YT200.DTMSEQ " + ",YT200.IDSIIRESAKI " + ",YT200.AMKISYUKAI " + ",YT200.AMKIKANKAI " + ",YT200.AMKIKANSIHA " + ",YT200.AMKAIKAKE " + ",DT010.AMKISYUKAIKAKE " + ",( (YT200.DTNEND * 100) + YT200.DTMSEQ ) as KIJUN " + "FROM YT200 " + "INNER JOIN DT010 " + " ON DT010.IDSIIRESAKI = YT200.IDSIIRESAKI " + "WHERE ( (YT200.DTNEND * 100) + YT200.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT200.IDSIIRESAKI " + ",YT200.DTNEND " + ",YT200.DTMSEQ " ); var iLoop = 0; var _PRE_IDSIIRESAKI = ''; var _PRE_AMURIKAKE = 0; var _AMKISYUKAI = 0; var _AMKAIKAKE = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDSIIRESAKI != lineDataMap['IDSIIRESAKI']) { _PRE_IDSIIRESAKI = lineDataMap['IDSIIRESAKI']; _PRE_AMURIKAKE = 0; iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_AMURIKAKE = lineDataMap['AMKAIKAKE']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_AMURIKAKE = lineDataMap['AMKISYUKAIKAKE']; } _AMKISYUKAI = _PRE_AMURIKAKE; //期末売掛残高算出 _AMKAIKAKE = _AMKISYUKAI + lineDataMap['AMKIKANKAI'] - lineDataMap['AMKIKANSIHA']; //期末を前月期末に保持 _PRE_AMURIKAKE = _AMKAIKAKE; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUKAI'] = _AMKISYUKAI; itemDataList['AMKAIKAKE'] = _AMKAIKAKE; updateYT200(itemDataList); } iLoop = iLoop + 1; } //買掛年月集計// //----------------------------- var tmp = "買掛年月集計：YT200の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM(AMKISYUKAI) is null then 0 else SUM(AMKISYUKAI) end AS AMKISYUKAI " + ",case when SUM(AMKIKANKAI) is null then 0 else SUM(AMKIKANKAI) end AS AMKIKANKAI " + ",case when SUM(AMKIKANSIHA) is null then 0 else SUM(AMKIKANSIHA) end AS AMKIKANSIHA " + ",case when SUM(AMKAIKAKE) is null then 0 else SUM(AMKAIKAKE) end AS AMKAIKAKE " + "FROM YT200 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUKAI'] = lineDataMap['AMKISYUKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; itemDataList['AMKAIKAKE'] = lineDataMap['AMKAIKAKE']; updateYT020(itemDataList); } var tmp = "買掛年月集計：YT010の更新" tln_com_log(tmp); //YT010の更新 var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMKAIKAKE IS NULL THEN 0 ELSE YT010.AMKAIKAKE END AS AMKISYUKAI " + ",MN.AMKIKANKAI " + ",MN.AMKIKANSIHA " + ",CASE WHEN YT010.AMKAIKAKE IS NULL THEN 0 ELSE YT010.AMKAIKAKE END + MN.AMKIKANKAI - MN.AMKIKANSIHA AS AMKAIKAKE " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM(AMKISYUKAI) is null then 0 else SUM(AMKISYUKAI) end AS AMKISYUKAI " + " ,case when SUM(AMKIKANKAI) is null then 0 else SUM(AMKIKANKAI) end AS AMKIKANKAI " + " ,case when SUM(AMKIKANSIHA) is null then 0 else SUM(AMKIKANSIHA) end AS AMKIKANSIHA " + " ,case when SUM(AMKAIKAKE) is null then 0 else SUM(AMKAIKAKE) end AS AMKAIKAKE " + " FROM YT200 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['AMKISYUKAI'] = lineDataMap['AMKISYUKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; itemDataList['AMKAIKAKE'] = lineDataMap['AMKAIKAKE']; updateYT010(itemDataList); } /* 処理３ */ //在庫増減履歴集計// //----------------------------- var tmp = "在庫増減履歴集計：YT310の登録" tln_com_log(tmp); //YT310の登録 TALON.insert( "INSERT INTO YT310 ( " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",KBZAIHURI " + ",AMTORIHIKI " + ",QTTORIHIKI " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",case when (KBZAIHURI is null) then ' ' else KBZAIHURI end as KBZAIHURI " + ",SUM(AMTORIHIKI) AS AMTORIHIKI " + ",SUM(QTTORIHIKI) AS QTTORIHIKI " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM FT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",KBZAIHURI " ) var tmp = "在庫増減履歴集計：YT300の更新" tln_com_log(tmp); //YT300の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",case when QTKIKANNYUKO is null then 0 else QTKIKANNYUKO end AS QTKIKANNYUKO " + ",case when AMKIKANNYUKO is null then 0 else AMKIKANNYUKO end AS AMKIKANNYUKO " + ",case when QTKIKANSYUKKO is null then 0 else QTKIKANSYUKKO end AS QTKIKANSYUKKO " + ",case when AMKIKANSYUKKO is null then 0 else AMKIKANSYUKKO end AS AMKIKANSYUKKO " + ",case when QTKIKANKAI is null then 0 else QTKIKANKAI end AS QTKIKANKAI " + ",case when AMKIKANKAI is null then 0 else AMKIKANKAI end AS AMKIKANKAI " + ",case when QTKIKANSEI is null then 0 else QTKIKANSEI end AS QTKIKANSEI " + ",case when AMKIKANSEI is null then 0 else AMKIKANSEI end AS AMKIKANSEI " + ",case when QTKIKANURI is null then 0 else QTKIKANURI end AS QTKIKANURI " + ",case when AMKIKANURI is null then 0 else AMKIKANURI end AS AMKIKANURI " + ",case when AMKIKANARARI is null then 0 else AMKIKANARARI end AS AMKIKANARARI " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDHINMOKU " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANNYUKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANNYUKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN 0 ELSE QTTORIHIKI END ) AS QTKIKANSYUKKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN 0 ELSE AMTORIHIKI END ) AS AMKIKANSYUKKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '030' OR KBUKEBARAI = '040') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANKAI " + " ,SUM( CASE WHEN (KBUKEBARAI = '030' OR KBUKEBARAI = '040') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANKAI " + " ,SUM( CASE WHEN (KBUKEBARAI = '060') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANSEI " + " ,SUM( CASE WHEN (KBUKEBARAI = '060') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANSEI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN QTTORIHIKI " + " WHEN (KBUKEBARAI = '050') THEN (QTTORIHIKI * -1) " + " ELSE 0 END ) AS QTKIKANURI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN AMTORIHIKI " + " WHEN (KBUKEBARAI = '050') THEN (AMTORIHIKI * -1) " + " ELSE 0 END ) AS AMKIKANURI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN AMSAGAKU " + " WHEN (KBUKEBARAI = '050') THEN (AMSAGAKU * -1) " + " ELSE 0 END ) AS AMKIKANARARI " + " FROM FT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDHINMOKU " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKIKANNYUKO'] = lineDataMap['QTKIKANNYUKO']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['QTKIKANSYUKKO'] = lineDataMap['QTKIKANSYUKKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['QTKIKANKAI'] = lineDataMap['QTKIKANKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['QTKIKANSEI'] = lineDataMap['QTKIKANSEI']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['QTKIKANURI'] = lineDataMap['QTKIKANURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT300(itemDataList); } //在庫品別残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "在庫品別残高更新：YT300の更新" tln_com_log(tmp); //YT300の更新 var itemSelectList = TALON.select( "SELECT " + " YT300.DTNEND " + ",YT300.DTMSEQ " + ",YT300.IDHINMOKU " + ",YT300.QTKISYUZAI " + ",YT300.AMKISYUZAI " + ",QTKIKANNYUKO " + ",AMKIKANNYUKO " + ",QTKIKANSYUKKO " + ",AMKIKANSYUKKO " + ",YT300.QTZAIKO " + ",YT300.AMZAIKO " + ",FT010.QTKISYU " + ",FT010.AMKISYU " + ",( (YT300.DTNEND * 100) + YT300.DTMSEQ ) as KIJUN " + "FROM YT300 " + "INNER JOIN ( " + " SELECT " + " IDHINMOKU " + " ,SUM(QTKISYU) AS QTKISYU " + " ,SUM(AMKISYU) AS AMKISYU " + " FROM FT010 GROUP BY IDHINMOKU " + ") FT010 " + " ON FT010.IDHINMOKU = YT300.IDHINMOKU " + "WHERE ( (YT300.DTNEND * 100) + YT300.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT300.IDHINMOKU " + ",YT300.DTNEND " + ",YT300.DTMSEQ " ); var iLoop = 0; var _PRE_IDHINMOKU = ''; var _PRE_QTZAIKO = 0; var _PRE_AMZAIKO = 0; var _QTKISYUZAI = 0; var _AMKISYUZAI = 0; var _QTZAIKO = 0; var _AMZAIKO = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDHINMOKU != lineDataMap['IDHINMOKU']) { _PRE_IDHINMOKU = lineDataMap['IDHINMOKU']; _PRE_QTZAIKO = 0; _PRE_AMZAIKO = 0; iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_QTZAIKO = lineDataMap['QTZAIKO']; _PRE_AMZAIKO = lineDataMap['AMZAIKO']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_QTZAIKO = lineDataMap['QTKISYU']; _PRE_AMZAIKO = lineDataMap['AMKISYU']; } _QTKISYUZAI = _PRE_QTZAIKO; _AMKISYUZAI = _PRE_AMZAIKO; //期末売掛残高算出 _QTZAIKO = _QTKISYUZAI + lineDataMap['QTKIKANNYUKO'] - lineDataMap['QTKIKANSYUKKO']; _AMZAIKO = _AMKISYUZAI + lineDataMap['AMKIKANNYUKO'] - lineDataMap['AMKIKANSYUKKO']; //期末を前月期末に保持 _PRE_QTZAIKO = _QTZAIKO; _PRE_AMZAIKO = _AMZAIKO; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKISYUZAI'] = _QTKISYUZAI; itemDataList['AMKISYUZAI'] = _AMKISYUZAI; itemDataList['QTZAIKO'] = _QTZAIKO; itemDataList['AMZAIKO'] = _AMZAIKO; updateYT300(itemDataList); } iLoop = iLoop + 1; } //買掛年月集計// var tmp = "買掛年月集計：YT020の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM(AMKISYUZAI) is null then 0 else SUM(AMKISYUZAI) end AS AMKISYUZAI " + ",case when SUM(AMKIKANNYUKO) is null then 0 else SUM(AMKIKANNYUKO) end AS AMKIKANNYUKO " + ",case when SUM(AMKIKANSYUKKO) is null then 0 else SUM(AMKIKANSYUKKO) end AS AMKIKANSYUKKO " + ",case when SUM(AMKIKANSEI) is null then 0 else SUM(AMKIKANSEI) end AS AMKIKANSEI " + ",case when SUM(AMZAIKO) is null then 0 else SUM(AMZAIKO) end AS AMZAIKO " + "FROM YT300 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUZAI'] = lineDataMap['AMKISYUZAI']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['AMZAIKO'] = lineDataMap['AMZAIKO']; updateYT020(itemDataList); } //YT010の更新 var tmp = "買掛年月集計：YT010の更新" tln_com_log(tmp); var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMZAIKO IS NULL THEN 0 ELSE YT010.AMZAIKO END AS AMKISYUZAI " + ",MN.AMKIKANNYUKO " + ",MN.AMKIKANSYUKKO " + ",MN.AMKIKANSEI " + ",CASE WHEN YT010.AMZAIKO IS NULL THEN 0 ELSE YT010.AMZAIKO END + MN.AMKIKANNYUKO - MN.AMKIKANSYUKKO AS AMZAIKO " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM(AMKISYUZAI) is null then 0 else SUM(AMKISYUZAI) end AS AMKISYUZAI " + " ,case when SUM(AMKIKANNYUKO) is null then 0 else SUM(AMKIKANNYUKO) end AS AMKIKANNYUKO " + " ,case when SUM(AMKIKANSYUKKO) is null then 0 else SUM(AMKIKANSYUKKO) end AS AMKIKANSYUKKO " + " ,case when SUM(AMKIKANSEI) is null then 0 else SUM(AMKIKANSEI) end AS AMKIKANSEI " + " ,case when SUM(AMZAIKO) is null then 0 else SUM(AMZAIKO) end AS AMZAIKO " + " FROM YT300 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUZAI'] = lineDataMap['AMKISYUZAI']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['AMZAIKO'] = lineDataMap['AMZAIKO']; updateYT010(itemDataList); } TALON.addMsg( "取引集計処理を実行しました" ); TALON.setIsSuccess(true); } exec(); ``` ## 参照情報 ### メニュー位置 | FUNC\_ATH\_ID | グループ名 | 表示名 | AUTH\_KBN | PRINT\_SORT | APP\_URL | HELP\_URL | |------|------|------|------|------|------|------| | XTEA//XTEA\_STATISTICS | | 取引集計処理の起動 | 1 | 5790 | TALON/APPLICATION/GENERALFREE/GENERALFREE.xhtml?PARAM\_FUNC\_ID=XDMF\_YF900\_0 | | ### 依存関係 #### 参照先テーブル (書き込み) _INSERT/UPDATE/DELETE/MERGE/UPDATE_TABLE 設定_ | テーブル名 | 出典 | |------|------| | YT010 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT020 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT100 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT110 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT200 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT210 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT300 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | YT310 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | #### 参照先テーブル (読み取りのみ) _FROM / JOIN 句から抽出_ | テーブル名 | 出典 | |------|------| | BT010 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | DT010 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | DT100 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | ET010 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | ET100 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | FT010 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | FT100 | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | TLN_M_HANYO_CODE_MAIN | BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | | ZT010 | BLOCK1.BODY\_SQL / BUTTON\_BAT/ALL \(FUNC\_SEQ=0\) | > **✎ テーブル参照解析の注意事項** > > - テーブル参照は `UPDATE_TABLE` 設定 + ブロック SELECT 式 (BODY_SQL/TITLE_SQL) + JavaScript コード (CHR_TEXT) + 呼び出すストアドプロシージャ本体 (Oracle / PostgreSQL / MySQL / SQL Server) を `FROM` / `JOIN` / `INSERT INTO` / `UPDATE ... SET` / `DELETE FROM` / `MERGE INTO` / `REPLACE INTO` / `MERGE ... USING` の正規表現で抽出した結果です。 動的SQLは文字列リテラルが DML 文で始まる場合のみ抽出します。 > - **書き込み** = INSERT/UPDATE/DELETE/MERGE が検出されたテーブル / **読み取り** = FROM/JOIN のみのテーブル > - **⚠ 抜け・漏れの可能性**: コード内で動的に組み立てられた SQL、変数連結、条件分岐内の SQL は検出できない場合があります。 > - **⚠ 対象外**: ストアドプロシージャ本体は Oracle / PostgreSQL / MySQL / SQL Server に対応 (DB2 等は今後)。 プロシージャから更に呼ぶ別プロシージャ、 BODY_SQL/JS 直書きの CALL/EXEC、 外部 API 経由、 トリガ連動は含まれません。 動的SQLの変数組み立てテーブル名も対象外 (リテラル完結分は抽出)。 > - **✎ 補完 (備考タグ)**: 解析で捕捉できない依存は 機能説明タブの備考に `[R:テーブル名]` (読み) / `[W:テーブル名]` (書き) / `[WR:テーブル名]` (読み書き) と記入すると依存として取り込まれます (1タグ1テーブル、 文章中に埋め込み可)。 `@番号` で複数DB接続の接続先を明示できます (例 `[W:LOG_TBL@2]`。 省略時は機能の DB 接続先)。 ## コードブック ### JavaScript / 各種コード #### 処理呼出 サーバJS (JavaScript) (ブロック1, 並び=0) ```javascript var itemColNameTbl_YT010 = [ 'DTNEND' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'AMKIKANSEI' ,'AMURIKAKE' ,'AMKAIKAKE' ,'AMZAIKO' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT010(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT010', itemDataList , itemColNameTbl_YT010 ); } function updateYT010(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereList = new Array(); whereList.push(whereTbl1); TALON.updateByMap( 'YT010', itemDataList , itemColNameTbl_YT010 , whereList ); } var itemColNameTbl_YT020 = [ 'DTNEND' ,'DTMSEQ' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'AMKIKANSEI' ,'AMURIKAKE' ,'AMKAIKAKE' ,'AMZAIKO' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT020(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT020', itemDataList , itemColNameTbl_YT020 ); } function updateYT020(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); TALON.updateByMap( 'YT020', itemDataList , itemColNameTbl_YT020 , whereList ); } var itemColNameTbl_YT100 = [ 'DTNEND' ,'DTMSEQ' ,'IDTOKUISAKI' ,'AMKISYUURI' ,'AMKIKANURI' ,'AMKIKANJYU' ,'AMKIKANGENKA' ,'AMURIKAKE' ,'AMKIKANARARI' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT100(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT100', itemDataList , itemColNameTbl_YT100 ); } function updateYT100(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDTOKUISAKI' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT100', itemDataList , itemColNameTbl_YT100 , whereList ); } var itemColNameTbl_YT200 = [ 'DTNEND' ,'DTMSEQ' ,'IDSIIRESAKI' ,'AMKISYUKAI' ,'AMKIKANKAI' ,'AMKIKANSIHA' ,'AMKAIKAKE' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT200(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT200', itemDataList , itemColNameTbl_YT200 ); } function updateYT200(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDSIIRESAKI' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT200', itemDataList , itemColNameTbl_YT200 , whereList ); } var itemColNameTbl_YT300 = [ 'DTNEND' ,'DTMSEQ' ,'IDHINMOKU' ,'QTKISYUZAI' ,'QTKIKANNYUKO' ,'QTKIKANSYUKKO' ,'AMKISYUZAI' ,'AMKIKANNYUKO' ,'AMKIKANSYUKKO' ,'QTKIKANKAI' ,'AMKIKANKAI' ,'QTKIKANSEI' ,'AMKIKANSEI' ,'QTKIKANURI' ,'AMKIKANURI' ,'AMKIKANARARI' ,'QTZAIKO' ,'AMZAIKO' ,'CREATED_DATE' ,'CREATED_BY' ,'CREATED_PRG_NM' ,'UPDATED_DATE' ,'UPDATED_BY' ,'UPDATED_PRG_NM' ,'MODIFY_COUNT' ]; function insertYT300(itemDataList) { //INSERT itemDataList['CREATED_DATE'] = tln_com_nowdate(); itemDataList['CREATED_BY'] = tln_com_user_id(); itemDataList['CREATED_PRG_NM'] = tln_com_func_id(); itemDataList['UPDATED_DATE'] = tln_com_nowdate(); itemDataList['UPDATED_BY'] = tln_com_user_id(); itemDataList['UPDATED_PRG_NM'] = tln_com_func_id(); itemDataList['MODIFY_COUNT'] = 1; TALON.insertByMap( 'YT300', itemDataList , itemColNameTbl_YT300 ); } function updateYT300(itemDataList) { var whereTbl1 = [ null, '=' , 'DTNEND' ]; var whereTbl2 = [ null, '=' , 'DTMSEQ' ]; var whereTbl3 = [ null, '=' , 'IDHINMOKU' ]; var whereList = new Array(); whereList.push(whereTbl1); whereList.push(whereTbl2); whereList.push(whereTbl3); TALON.updateByMap( 'YT300', itemDataList , itemColNameTbl_YT300 , whereList ); } function commWhere(_NOW_NENDO, _NOW_MONTH) { var sql = "WHERE FGVALID = 'T' " + " AND KBAKADEN = '1' " + " AND ( " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + " ) "; return sql; } function exec() { //機能ＩＤセット（Ver3.1用） _FUNC_ID = 'XDMF_YF900_0'; /* 事前準備 */ if (TALON.getDbConfig().isMssql()) { var itemSelectList = TALON.select( "SELECT " + " TLN_CHAR_TO_NUMBER(SUBSTRING(TXVALUE,1,4)) AS NOW_NENDO " + ",SUBSTRING(TXVALUE,5,2) AS NOW_MONTH " + "FROM ZT010 " + "WHERE IDVARIANT = 'ALLOWED_FISCAL_MONTH' " ); } else { var itemSelectList = TALON.select( "SELECT " + " TLN_CHAR_TO_NUMBER(SUBSTR(TXVALUE,1,4)) AS NOW_NENDO " + ",SUBSTR(TXVALUE,5,2) AS NOW_MONTH " + "FROM ZT010 " + "WHERE IDVARIANT = 'ALLOWED_FISCAL_MONTH' " ); } var lineDataMap = itemSelectList[0]; var _NOW_NENDO = lineDataMap['NOW_NENDO']; var _NOW_MONTH = lineDataMap['NOW_MONTH']; var itemSelectList = TALON.select( "SELECT " + " TLN_SYSDATE() AS SYS_DATE " + "FROM TLN_M_HANYO_CODE_MAIN " + "WHERE SIKIBETU_CODE = 'TLN_GENERAL_JAVASCRIPT_ENGINE' " ); var lineDataMap = itemSelectList[0]; var _SYS_DATE = lineDataMap['SYS_DATE']; var _TRU_YEAR = getNENDO(_SYS_DATE); var _TRU_MSEQ = getGMSEQ(_SYS_DATE); /* 初期化 */ //-------------- //YT010の削除 TALON.delete(" DELETE FROM YT010 WHERE DTNEND >= " + _NOW_NENDO + " "); //-------------- //YT010の登録 if ( parseInt(_NOW_NENDO) <= parseInt(_TRU_YEAR)) { for (var i = parseInt(_NOW_NENDO); i <= parseInt(_TRU_YEAR); i++) { var itemDataList = new Array(); itemDataList['DTNEND'] = String(i); insertYT010(itemDataList); } } //-------------- //YT020の削除 //YT100の削除 //YT200の削除 //YT300の削除 TALON.delete(" DELETE FROM YT020 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT100 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT200 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); TALON.delete(" DELETE FROM YT300 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT020の登録 //YT100の登録 //YT200の登録 //YT300の登録 var _NOW_NENDO_W = ((parseInt(_NOW_NENDO) * 100) + parseInt(_NOW_MONTH)); var _TRU_YEAR_W = ((parseInt(_TRU_YEAR) * 100) + parseInt(_TRU_MSEQ) ); if ( _NOW_NENDO_W <= _TRU_YEAR_W) { tln_com_log("■[_NOW_NENDO_W] : " + _NOW_NENDO_W); tln_com_log("■[_TRU_YEAR_W] : " + _TRU_YEAR_W); for (var i = parseInt(_NOW_NENDO_W); i <= parseInt(_TRU_YEAR_W); i++) { var itemDataList = new Array(); itemDataList['DTNEND'] = parseInt(String(i).substr(0,4)); itemDataList['DTMSEQ'] = parseInt(String(i).substr(4,2)); itemDataList['AMKISYUURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANJYU'] = 0; itemDataList['AMKIKANGENKA'] = 0; itemDataList['AMKISYUKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['AMKIKANSIHA'] = 0; itemDataList['AMKISYUZAI'] = 0; itemDataList['AMKIKANNYUKO'] = 0; itemDataList['AMKIKANSYUKKO'] = 0; itemDataList['AMKIKANSEI'] = 0; itemDataList['AMURIKAKE'] = 0; itemDataList['AMKAIKAKE'] = 0; itemDataList['AMZAIKO'] = 0; itemDataList['AMKIKANARARI'] = 0; insertYT020(itemDataList); var itemSelectList = TALON.select( "SELECT IDTOKUISAKI FROM ET010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANJYU'] = 0; itemDataList['AMKIKANGENKA'] = 0; itemDataList['AMURIKAKE'] = 0; itemDataList['AMKIKANARARI'] = 0; insertYT100(itemDataList); } var itemSelectList = TALON.select( "SELECT IDSIIRESAKI FROM DT010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDSIIRESAKI'] = lineDataMap['IDSIIRESAKI']; itemDataList['AMKISYUKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['AMKIKANSIHA'] = 0; itemDataList['AMKAIKAKE'] = 0; insertYT200(itemDataList); } var itemSelectList = TALON.select( "SELECT IDHINMOKU FROM BT010 " ); for (var ii = 0; ii < itemSelectList.length; ii++) { var lineDataMap = itemSelectList[ii] itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKISYUZAI'] = 0; itemDataList['QTKIKANNYUKO'] = 0; itemDataList['QTKIKANSYUKKO'] = 0; itemDataList['AMKISYUZAI'] = 0; itemDataList['AMKIKANNYUKO'] = 0; itemDataList['AMKIKANSYUKKO'] = 0; itemDataList['QTKIKANKAI'] = 0; itemDataList['AMKIKANKAI'] = 0; itemDataList['QTKIKANSEI'] = 0; itemDataList['AMKIKANSEI'] = 0; itemDataList['QTKIKANURI'] = 0; itemDataList['AMKIKANURI'] = 0; itemDataList['AMKIKANARARI'] = 0; itemDataList['QTZAIKO'] = 0; itemDataList['AMZAIKO'] = 0; insertYT300(itemDataList); } if (String(i).substr(4,2) =="12"){ i = ((parseInt(String(i).substr(0,4)) + 1) * 100) } } } //-------------- //YT110の削除 TALON.delete(" DELETE FROM YT110 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT210の削除 TALON.delete(" DELETE FROM YT210 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); //-------------- //YT310の削除 TALON.delete(" DELETE FROM YT310 WHERE ( DTNEND > " + _NOW_NENDO + " ) OR ( DTNEND = " + _NOW_NENDO + " and DTMSEQ >= " + _NOW_MONTH + " ) "); /* 処理１ */ /* *************************** */ //売掛増減履歴集計// //----------------------------- var tmp = "売掛増減履歴集計：YT110の登録" tln_com_log(tmp); //YT110の登録 TALON.insert( "INSERT INTO YT110( " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",KBURIHURI " + ",AMTORIHIKI " + ",AMTAX " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",case when (KBURIHURI is null) then ' ' else KBURIHURI end as KBURIHURI " + ",SUM( AMTORIHIKI ) AS AMTORIHIKI " + ",SUM( AMTAX ) AS AMTAX " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM ET100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",KBURITORI " + ",KBURIHURI " ) var tmp = "売掛増減履歴集計：YT110の更新" tln_com_log(tmp); //YT100の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDTOKUISAKI " + ",case when AMKIKANURI is null then 0 else AMKIKANURI end AMKIKANURI " + ",case when AMKIKANJYU is null then 0 else AMKIKANJYU end AMKIKANJYU " + ",case when AMKIKANGENKA is null then 0 else AMKIKANGENKA end AMKIKANGENKA " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDTOKUISAKI " + " ,SUM( CASE WHEN (KBURITORI = '00' OR KBURITORI = '01' OR KBURITORI = '11' OR KBURITORI = '02' OR KBURITORI = '10') THEN AMZOUGEN ELSE 0 END) AS AMKIKANURI " + " ,SUM( CASE WHEN (KBURITORI = '90' OR KBURITORI = '91' OR KBURITORI = '92') THEN AMZOUGEN ELSE 0 END) AS AMKIKANJYU " + " ,SUM( " + " CASE " + " WHEN (KBURITORI = '00' OR KBURITORI = '01' OR KBURITORI = '11') THEN AMGENKA " + " WHEN (KBURITORI = '02' OR KBURITORI = '10') THEN (AMGENKA * -1) " + " ELSE 0 END " + " ) AS AMKIKANGENKA " + " FROM ET100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDTOKUISAKI " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; updateYT100(itemDataList); } //売掛残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "売掛残高更新：YT100の更新" tln_com_log(tmp); //YT100の更新 var itemSelectList = TALON.select( "SELECT " + " YT100.DTNEND " + ",YT100.DTMSEQ " + ",YT100.IDTOKUISAKI " + ",YT100.AMKISYUURI " + ",YT100.AMKIKANURI " + ",YT100.AMKIKANJYU " + ",YT100.AMKIKANGENKA " + ",YT100.AMURIKAKE " + ",YT100.AMKIKANARARI " + ",ET010.AMKISYUURIKAKE " + ",( (YT100.DTNEND * 100) + YT100.DTMSEQ ) as KIJUN " + "FROM YT100 " + "INNER JOIN ET010 " + " ON ET010.IDTOKUISAKI = YT100.IDTOKUISAKI " + "WHERE ( (YT100.DTNEND * 100) + YT100.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT100.IDTOKUISAKI " + ",YT100.DTNEND " + ",YT100.DTMSEQ " ); var iLoop = 0; var _PRE_IDTOKUISAKI = ''; var _PRE_AMURIKAKE = 0; var _AMKISYUURI = 0; var _AMURIKAKE = 0; var _AMKIKANARARI = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDTOKUISAKI != lineDataMap['IDTOKUISAKI']) { _PRE_IDTOKUISAKI = lineDataMap['IDTOKUISAKI']; _PRE_AMURIKAKE = 0 iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0 && lineDataMap['KIJUN'] == _FR ) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_AMURIKAKE = lineDataMap['AMURIKAKE']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_AMURIKAKE = lineDataMap['AMKISYUURIKAKE']; } _AMKISYUURI = _PRE_AMURIKAKE; //期末売掛残高算出 _AMURIKAKE = _AMKISYUURI + lineDataMap['AMKIKANURI'] - lineDataMap['AMKIKANJYU']; //期間粗利額 _AMKIKANARARI = lineDataMap['AMKIKANURI'] - lineDataMap['AMKIKANGENKA']; //期末を前月期末に保持 _PRE_AMURIKAKE = _AMURIKAKE; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUURI'] = _AMKISYUURI; itemDataList['AMURIKAKE'] = _AMURIKAKE; itemDataList['AMKIKANARARI'] = _AMKIKANARARI; updateYT100(itemDataList); } iLoop = iLoop + 1; } //売掛年月集計// //----------------------------- var tmp = "売掛年月集計：YT020の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM( AMKISYUURI ) is null then 0 else SUM( AMKISYUURI ) end AS AMKISYUURI " + ",case when SUM( AMKIKANURI ) is null then 0 else SUM( AMKIKANURI ) end AS AMKIKANURI " + ",case when SUM( AMKIKANJYU ) is null then 0 else SUM( AMKIKANJYU ) end AS AMKIKANJYU " + ",case when SUM( AMKIKANGENKA ) is null then 0 else SUM( AMKIKANGENKA ) end AS AMKIKANGENKA " + ",case when SUM( AMURIKAKE ) is null then 0 else SUM( AMURIKAKE ) end AS AMURIKAKE " + ",case when SUM( AMKIKANARARI ) is null then 0 else SUM( AMKIKANARARI ) end AS AMKIKANARARI " + "FROM YT100 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUURI'] = lineDataMap['AMKISYUURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; itemDataList['AMURIKAKE'] = lineDataMap['AMURIKAKE']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT020(itemDataList); } var tmp = "売掛年月集計：YT010の更新" tln_com_log(tmp); //YT010の更新 var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMURIKAKE IS NULL THEN 0 ELSE YT010.AMURIKAKE END AS AMKISYUURI " + ",MN.AMKIKANURI " + ",MN.AMKIKANJYU " + ",MN.AMKIKANGENKA " + ",CASE WHEN YT010.AMURIKAKE IS NULL THEN 0 ELSE YT010.AMURIKAKE END + MN.AMKIKANURI - MN.AMKIKANJYU AS AMURIKAKE " + ",MN.AMKIKANARARI " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM( AMKISYUURI ) is null then 0 else SUM( AMKISYUURI ) end AS AMKISYUURI " + " ,case when SUM( AMKIKANURI ) is null then 0 else SUM( AMKIKANURI ) end AS AMKIKANURI " + " ,case when SUM( AMKIKANJYU ) is null then 0 else SUM( AMKIKANJYU ) end AS AMKIKANJYU " + " ,case when SUM( AMKIKANGENKA ) is null then 0 else SUM( AMKIKANGENKA ) end AS AMKIKANGENKA " + " ,case when SUM( AMURIKAKE ) is null then 0 else SUM( AMURIKAKE ) end AS AMURIKAKE " + " ,case when SUM( AMKIKANARARI ) is null then 0 else SUM( AMKIKANARARI ) end AS AMKIKANARARI " + " FROM YT100 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['AMKISYUURI'] = lineDataMap['AMKISYUURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANJYU'] = lineDataMap['AMKIKANJYU']; itemDataList['AMKIKANGENKA'] = lineDataMap['AMKIKANGENKA']; itemDataList['AMURIKAKE'] = lineDataMap['AMURIKAKE']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT010(itemDataList); } /* 処理２ */ /* *************************** */ //買掛増減履歴集計// //----------------------------- var tmp = "買掛増減履歴集計：YT210の登録" tln_com_log(tmp); //YT210の登録 TALON.insert( "INSERT INTO YT210 ( " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",KBKAIHURI " + ",AMTORIHIKI " + ",AMTAX " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",case when (KBKAIHURI is null) then ' ' else KBKAIHURI end as KBKAIHURI " + ",SUM(AMTORIHIKI) AS AMTORIHIKI " + ",SUM(AMTAX) AS AMTAX " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM DT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",KBKAITORI " + ",KBKAIHURI " ) var tmp = "買掛増減履歴集計：YT200の更新" tln_com_log(tmp); //YT200の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDSIIRESAKI " + ",case when AMKIKANKAI is null then 0 else AMKIKANKAI end AS AMKIKANKAI " + ",case when AMKIKANSIHA is null then 0 else AMKIKANSIHA end AS AMKIKANSIHA " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDSIIRESAKI " + " ,SUM( CASE WHEN (KBKAITORI = '00' OR KBKAITORI = '01' OR KBKAITORI = '11' OR KBKAITORI = '02' OR KBKAITORI = '10') THEN AMZOUGEN ELSE 0 END ) AS AMKIKANKAI " + " ,SUM( CASE WHEN (KBKAITORI = '90') THEN AMZOUGEN END ) AS AMKIKANSIHA " + " FROM DT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDSIIRESAKI " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDSIIRESAKI'] = lineDataMap['IDSIIRESAKI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; updateYT200(itemDataList); } //買掛残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "買掛残高更新：YT200の更新" tln_com_log(tmp); //YT200の更新 var itemSelectList = TALON.select( "SELECT " + " YT200.DTNEND " + ",YT200.DTMSEQ " + ",YT200.IDSIIRESAKI " + ",YT200.AMKISYUKAI " + ",YT200.AMKIKANKAI " + ",YT200.AMKIKANSIHA " + ",YT200.AMKAIKAKE " + ",DT010.AMKISYUKAIKAKE " + ",( (YT200.DTNEND * 100) + YT200.DTMSEQ ) as KIJUN " + "FROM YT200 " + "INNER JOIN DT010 " + " ON DT010.IDSIIRESAKI = YT200.IDSIIRESAKI " + "WHERE ( (YT200.DTNEND * 100) + YT200.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT200.IDSIIRESAKI " + ",YT200.DTNEND " + ",YT200.DTMSEQ " ); var iLoop = 0; var _PRE_IDSIIRESAKI = ''; var _PRE_AMURIKAKE = 0; var _AMKISYUKAI = 0; var _AMKAIKAKE = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDSIIRESAKI != lineDataMap['IDSIIRESAKI']) { _PRE_IDSIIRESAKI = lineDataMap['IDSIIRESAKI']; _PRE_AMURIKAKE = 0; iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_AMURIKAKE = lineDataMap['AMKAIKAKE']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_AMURIKAKE = lineDataMap['AMKISYUKAIKAKE']; } _AMKISYUKAI = _PRE_AMURIKAKE; //期末売掛残高算出 _AMKAIKAKE = _AMKISYUKAI + lineDataMap['AMKIKANKAI'] - lineDataMap['AMKIKANSIHA']; //期末を前月期末に保持 _PRE_AMURIKAKE = _AMKAIKAKE; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDTOKUISAKI'] = lineDataMap['IDTOKUISAKI']; itemDataList['AMKISYUKAI'] = _AMKISYUKAI; itemDataList['AMKAIKAKE'] = _AMKAIKAKE; updateYT200(itemDataList); } iLoop = iLoop + 1; } //買掛年月集計// //----------------------------- var tmp = "買掛年月集計：YT200の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM(AMKISYUKAI) is null then 0 else SUM(AMKISYUKAI) end AS AMKISYUKAI " + ",case when SUM(AMKIKANKAI) is null then 0 else SUM(AMKIKANKAI) end AS AMKIKANKAI " + ",case when SUM(AMKIKANSIHA) is null then 0 else SUM(AMKIKANSIHA) end AS AMKIKANSIHA " + ",case when SUM(AMKAIKAKE) is null then 0 else SUM(AMKAIKAKE) end AS AMKAIKAKE " + "FROM YT200 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUKAI'] = lineDataMap['AMKISYUKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; itemDataList['AMKAIKAKE'] = lineDataMap['AMKAIKAKE']; updateYT020(itemDataList); } var tmp = "買掛年月集計：YT010の更新" tln_com_log(tmp); //YT010の更新 var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMKAIKAKE IS NULL THEN 0 ELSE YT010.AMKAIKAKE END AS AMKISYUKAI " + ",MN.AMKIKANKAI " + ",MN.AMKIKANSIHA " + ",CASE WHEN YT010.AMKAIKAKE IS NULL THEN 0 ELSE YT010.AMKAIKAKE END + MN.AMKIKANKAI - MN.AMKIKANSIHA AS AMKAIKAKE " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM(AMKISYUKAI) is null then 0 else SUM(AMKISYUKAI) end AS AMKISYUKAI " + " ,case when SUM(AMKIKANKAI) is null then 0 else SUM(AMKIKANKAI) end AS AMKIKANKAI " + " ,case when SUM(AMKIKANSIHA) is null then 0 else SUM(AMKIKANSIHA) end AS AMKIKANSIHA " + " ,case when SUM(AMKAIKAKE) is null then 0 else SUM(AMKAIKAKE) end AS AMKAIKAKE " + " FROM YT200 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['AMKISYUKAI'] = lineDataMap['AMKISYUKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['AMKIKANSIHA'] = lineDataMap['AMKIKANSIHA']; itemDataList['AMKAIKAKE'] = lineDataMap['AMKAIKAKE']; updateYT010(itemDataList); } /* 処理３ */ //在庫増減履歴集計// //----------------------------- var tmp = "在庫増減履歴集計：YT310の登録" tln_com_log(tmp); //YT310の登録 TALON.insert( "INSERT INTO YT310 ( " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",KBZAIHURI " + ",AMTORIHIKI " + ",QTTORIHIKI " + ",CREATED_DATE " + ",CREATED_BY " + ",CREATED_PRG_NM " + ",UPDATED_DATE " + ",UPDATED_BY " + ",UPDATED_PRG_NM " + ",MODIFY_COUNT " + ") " + "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",case when (KBZAIHURI is null) then ' ' else KBZAIHURI end as KBZAIHURI " + ",SUM(AMTORIHIKI) AS AMTORIHIKI " + ",SUM(QTTORIHIKI) AS QTTORIHIKI " + ",TLN_SYSDATE() as CREATED_DATE " + ",'" + _USER_ID + "' as CREATED_BY " + ",'" + _FUNC_ID + "' as CREATED_PRG_NM " + ",TLN_SYSDATE() as UPDATED_DATE " + ",'" + _USER_ID + "' as UPDATED_BY " + ",'" + _FUNC_ID + "' as UPDATED_PRG_NM " + ",0 as MODIFY_COUNT " + "FROM FT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + "GROUP BY " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",KBUKEBARAI " + ",KBZAIHURI " ) var tmp = "在庫増減履歴集計：YT300の更新" tln_com_log(tmp); //YT300の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",IDHINMOKU " + ",case when QTKIKANNYUKO is null then 0 else QTKIKANNYUKO end AS QTKIKANNYUKO " + ",case when AMKIKANNYUKO is null then 0 else AMKIKANNYUKO end AS AMKIKANNYUKO " + ",case when QTKIKANSYUKKO is null then 0 else QTKIKANSYUKKO end AS QTKIKANSYUKKO " + ",case when AMKIKANSYUKKO is null then 0 else AMKIKANSYUKKO end AS AMKIKANSYUKKO " + ",case when QTKIKANKAI is null then 0 else QTKIKANKAI end AS QTKIKANKAI " + ",case when AMKIKANKAI is null then 0 else AMKIKANKAI end AS AMKIKANKAI " + ",case when QTKIKANSEI is null then 0 else QTKIKANSEI end AS QTKIKANSEI " + ",case when AMKIKANSEI is null then 0 else AMKIKANSEI end AS AMKIKANSEI " + ",case when QTKIKANURI is null then 0 else QTKIKANURI end AS QTKIKANURI " + ",case when AMKIKANURI is null then 0 else AMKIKANURI end AS AMKIKANURI " + ",case when AMKIKANARARI is null then 0 else AMKIKANARARI end AS AMKIKANARARI " + "FROM ( " + " SELECT " + " DTNEND " + " ,DTMSEQ " + " ,IDHINMOKU " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANNYUKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANNYUKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN 0 ELSE QTTORIHIKI END ) AS QTKIKANSYUKKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '010' OR KBUKEBARAI = '020' OR KBUKEBARAI = '030' OR KBUKEBARAI = '040' OR KBUKEBARAI = '050' OR KBUKEBARAI = '060' OR KBUKEBARAI = '070') THEN 0 ELSE AMTORIHIKI END ) AS AMKIKANSYUKKO " + " ,SUM( CASE WHEN (KBUKEBARAI = '030' OR KBUKEBARAI = '040') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANKAI " + " ,SUM( CASE WHEN (KBUKEBARAI = '030' OR KBUKEBARAI = '040') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANKAI " + " ,SUM( CASE WHEN (KBUKEBARAI = '060') THEN QTTORIHIKI ELSE 0 END ) AS QTKIKANSEI " + " ,SUM( CASE WHEN (KBUKEBARAI = '060') THEN AMTORIHIKI ELSE 0 END ) AS AMKIKANSEI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN QTTORIHIKI " + " WHEN (KBUKEBARAI = '050') THEN (QTTORIHIKI * -1) " + " ELSE 0 END ) AS QTKIKANURI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN AMTORIHIKI " + " WHEN (KBUKEBARAI = '050') THEN (AMTORIHIKI * -1) " + " ELSE 0 END ) AS AMKIKANURI " + " ,SUM( CASE " + " WHEN (KBUKEBARAI = '130' OR KBUKEBARAI = '150') THEN AMSAGAKU " + " WHEN (KBUKEBARAI = '050') THEN (AMSAGAKU * -1) " + " ELSE 0 END ) AS AMKIKANARARI " + " FROM FT100 " + commWhere(_NOW_NENDO, _NOW_MONTH) + " GROUP BY " + " DTNEND " + " ,DTMSEQ " + " ,IDHINMOKU " + ") MN " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKIKANNYUKO'] = lineDataMap['QTKIKANNYUKO']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['QTKIKANSYUKKO'] = lineDataMap['QTKIKANSYUKKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['QTKIKANKAI'] = lineDataMap['QTKIKANKAI']; itemDataList['AMKIKANKAI'] = lineDataMap['AMKIKANKAI']; itemDataList['QTKIKANSEI'] = lineDataMap['QTKIKANSEI']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['QTKIKANURI'] = lineDataMap['QTKIKANURI']; itemDataList['AMKIKANURI'] = lineDataMap['AMKIKANURI']; itemDataList['AMKIKANARARI'] = lineDataMap['AMKIKANARARI']; updateYT300(itemDataList); } //在庫品別残高更新// //----------------------------- if (_NOW_MONTH == 1) { var _FR = (( (_NOW_NENDO - 1) * 100) + 12) } else { var _FR = ((_NOW_NENDO * 100) + ( _NOW_MONTH - 1) ) } var _TO = ((_TRU_YEAR * 100) + _TRU_MSEQ ) var tmp = "在庫品別残高更新：YT300の更新" tln_com_log(tmp); //YT300の更新 var itemSelectList = TALON.select( "SELECT " + " YT300.DTNEND " + ",YT300.DTMSEQ " + ",YT300.IDHINMOKU " + ",YT300.QTKISYUZAI " + ",YT300.AMKISYUZAI " + ",QTKIKANNYUKO " + ",AMKIKANNYUKO " + ",QTKIKANSYUKKO " + ",AMKIKANSYUKKO " + ",YT300.QTZAIKO " + ",YT300.AMZAIKO " + ",FT010.QTKISYU " + ",FT010.AMKISYU " + ",( (YT300.DTNEND * 100) + YT300.DTMSEQ ) as KIJUN " + "FROM YT300 " + "INNER JOIN ( " + " SELECT " + " IDHINMOKU " + " ,SUM(QTKISYU) AS QTKISYU " + " ,SUM(AMKISYU) AS AMKISYU " + " FROM FT010 GROUP BY IDHINMOKU " + ") FT010 " + " ON FT010.IDHINMOKU = YT300.IDHINMOKU " + "WHERE ( (YT300.DTNEND * 100) + YT300.DTMSEQ ) BETWEEN " + _FR + " AND " + _TO + " " + "ORDER BY " + " YT300.IDHINMOKU " + ",YT300.DTNEND " + ",YT300.DTMSEQ " ); var iLoop = 0; var _PRE_IDHINMOKU = ''; var _PRE_QTZAIKO = 0; var _PRE_AMZAIKO = 0; var _QTKISYUZAI = 0; var _AMKISYUZAI = 0; var _QTZAIKO = 0; var _AMZAIKO = 0; for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] if (_PRE_IDHINMOKU != lineDataMap['IDHINMOKU']) { _PRE_IDHINMOKU = lineDataMap['IDHINMOKU']; _PRE_QTZAIKO = 0; _PRE_AMZAIKO = 0; iLoop = 0; } //初回は前回期末を取得し保持 //if (iLoop == 0) { if (lineDataMap['KIJUN'] == _FR ) { _PRE_QTZAIKO = lineDataMap['QTZAIKO']; _PRE_AMZAIKO = lineDataMap['AMZAIKO']; } else { //前回期末と今期期首にセット //ただし、１月の場合は前回期末をET010より取得した内容で置き換え if (String(i).substr(4,2) =="01"){ _PRE_QTZAIKO = lineDataMap['QTKISYU']; _PRE_AMZAIKO = lineDataMap['AMKISYU']; } _QTKISYUZAI = _PRE_QTZAIKO; _AMKISYUZAI = _PRE_AMZAIKO; //期末売掛残高算出 _QTZAIKO = _QTKISYUZAI + lineDataMap['QTKIKANNYUKO'] - lineDataMap['QTKIKANSYUKKO']; _AMZAIKO = _AMKISYUZAI + lineDataMap['AMKIKANNYUKO'] - lineDataMap['AMKIKANSYUKKO']; //期末を前月期末に保持 _PRE_QTZAIKO = _QTZAIKO; _PRE_AMZAIKO = _AMZAIKO; var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['IDHINMOKU'] = lineDataMap['IDHINMOKU']; itemDataList['QTKISYUZAI'] = _QTKISYUZAI; itemDataList['AMKISYUZAI'] = _AMKISYUZAI; itemDataList['QTZAIKO'] = _QTZAIKO; itemDataList['AMZAIKO'] = _AMZAIKO; updateYT300(itemDataList); } iLoop = iLoop + 1; } //買掛年月集計// var tmp = "買掛年月集計：YT020の更新" tln_com_log(tmp); //YT020の更新 var itemSelectList = TALON.select( "SELECT " + " DTNEND " + ",DTMSEQ " + ",case when SUM(AMKISYUZAI) is null then 0 else SUM(AMKISYUZAI) end AS AMKISYUZAI " + ",case when SUM(AMKIKANNYUKO) is null then 0 else SUM(AMKIKANNYUKO) end AS AMKIKANNYUKO " + ",case when SUM(AMKIKANSYUKKO) is null then 0 else SUM(AMKIKANSYUKKO) end AS AMKIKANSYUKKO " + ",case when SUM(AMKIKANSEI) is null then 0 else SUM(AMKIKANSEI) end AS AMKIKANSEI " + ",case when SUM(AMZAIKO) is null then 0 else SUM(AMZAIKO) end AS AMZAIKO " + "FROM YT300 " + "WHERE " + " ( DTNEND > " + _NOW_NENDO + " ) " + " OR " + " ( " + " DTNEND = " + _NOW_NENDO + " " + " AND " + " DTMSEQ >= " + _NOW_MONTH + " " + " ) " + "GROUP BY " + " DTNEND " + ",DTMSEQ " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUZAI'] = lineDataMap['AMKISYUZAI']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['AMZAIKO'] = lineDataMap['AMZAIKO']; updateYT020(itemDataList); } //YT010の更新 var tmp = "買掛年月集計：YT010の更新" tln_com_log(tmp); var itemSelectList = TALON.select( "SELECT " + " MN.DTNEND " + ",CASE WHEN YT010.AMZAIKO IS NULL THEN 0 ELSE YT010.AMZAIKO END AS AMKISYUZAI " + ",MN.AMKIKANNYUKO " + ",MN.AMKIKANSYUKKO " + ",MN.AMKIKANSEI " + ",CASE WHEN YT010.AMZAIKO IS NULL THEN 0 ELSE YT010.AMZAIKO END + MN.AMKIKANNYUKO - MN.AMKIKANSYUKKO AS AMZAIKO " + "FROM ( " + " SELECT " + " DTNEND " + " ,case when SUM(AMKISYUZAI) is null then 0 else SUM(AMKISYUZAI) end AS AMKISYUZAI " + " ,case when SUM(AMKIKANNYUKO) is null then 0 else SUM(AMKIKANNYUKO) end AS AMKIKANNYUKO " + " ,case when SUM(AMKIKANSYUKKO) is null then 0 else SUM(AMKIKANSYUKKO) end AS AMKIKANSYUKKO " + " ,case when SUM(AMKIKANSEI) is null then 0 else SUM(AMKIKANSEI) end AS AMKIKANSEI " + " ,case when SUM(AMZAIKO) is null then 0 else SUM(AMZAIKO) end AS AMZAIKO " + " FROM YT300 " + " WHERE DTNEND >= " + _NOW_NENDO + " " + " GROUP BY " + " DTNEND " + ") MN " + "LEFT OUTER JOIN YT010 " + " ON YT010.DTNEND = (MN.DTNEND - 1) " ); for (var i = 0; i < itemSelectList.length; i++) { var lineDataMap = itemSelectList[i] var itemDataList = new Array(); itemDataList['DTNEND'] = lineDataMap['DTNEND']; itemDataList['DTMSEQ'] = lineDataMap['DTMSEQ']; itemDataList['AMKISYUZAI'] = lineDataMap['AMKISYUZAI']; itemDataList['AMKIKANNYUKO'] = lineDataMap['AMKIKANNYUKO']; itemDataList['AMKIKANSYUKKO'] = lineDataMap['AMKIKANSYUKKO']; itemDataList['AMKIKANSEI'] = lineDataMap['AMKIKANSEI']; itemDataList['AMZAIKO'] = lineDataMap['AMZAIKO']; updateYT010(itemDataList); } TALON.addMsg( "取引集計処理を実行しました" ); TALON.setIsSuccess(true); } exec(); ```