照合と差額清算、そして照合では捕まらないもの
目標
こちらの元帳と相手機関の清算ファイルを突合して差異を件単位まで絞り込み、差額清算ポジションを出し、突合では原理的に捕まらない二重送金まで見つけ出せるようになります。そして、その結果を顧客企業の外に出せる形で書けるようになります。
なぜ重要なのか
突合は、銀行の現場でFDEが最もよく行う仕事です。そして、最もよく間違える仕事でもあります。
1つ目の落とし穴は、突合の対象です。こちらの元帳には、オーソリだけされて売上確定が来ていない件が混ざっていますが、これは相手の清算ファイルにないのが正常です。状態を区別せずに突合すると、正常な数十件が異常な件として上がり、本当の差異がその中に埋もれます。
2つ目は、ずれの読み方です。件数と金額を別々に見る必要があります。件数が同じで金額だけが違えば、同じ件を互いに異なる金額で把握しているという意味で、金額が同じで件数だけが違えば、片側が複数の件をまとめたか分割したという意味です。原因が違うので、顧客企業に上げる窓口も違います。
3つ目が最も重要です。突合は「2つの帳簿が同じか」を問うもので、「帳簿が正しいか」は問いません。タイムアウトのあとの再送で同じお金が2回出たなら、こちらの元帳にも相手のファイルにも、まったく同じように2件が残ります。総計の突合は完璧に通ります。捕まる場所は冪等キーだけです。
今回の事案は次のとおりです。2026-08-25の清算ファイルを受け取ったところ、総額が合わないという連絡が来ました。機関は3つで、そのうち2つが互いに異なる方式でずれています。
ステップ
/root/bank/gen_settle.pyを作成して実行し、/root/bank/settle.dbと/root/bank/partner_20260825.csvを作ってください。- 清算ファイルを
settle.dbにpartnerテーブルとして取り込み、/root/bank/recon_totals.csvに機関ごとの総計を出力してください。ヘッダー行はcounterparty,our_count,our_amount,partner_count,partner_amount,count_gap,amount_gapで、こちら側はstateがSETTLEDの件だけを数えます。 /root/bank/only_ours.csvと/root/bank/only_partner.csvをmsg_id,counterparty,amountのヘッダー行で出力し、/root/bank/side_counts.txtにonly_ours=、only_partner=、naive_only_ours=の3行を書いてください。最後の値は、stateを区別せずに突合したときの数です。/root/bank/amount_mismatch.csvに、msg_id,our_amount,partner_amount,gapのヘッダー行で、金額が異なる件を出力してください。/root/bank/dup_msg.csvに、idem_key,msg_id,amountのヘッダー行で、重複した冪等キーにぶら下がる電文をすべて残し、/root/bank/dup.txtにdup_keys=、dup_msgs=、extra_amount=の3行を書いてください。/root/bank/net_position.csvに、counterparty,out_amount,in_amount,netのヘッダー行で、機関ごとの差額清算ポジションを出してください。netは受取から支払を引いた値です。/root/bank/pending.txtにauth_count=、auth_amount=、auth_out_amount=、auth_in_amount=、stale_auth=、suspense_account=の6行を書いてください。stale_authはsent_atが2026-08-22より前のオーソリの件で、suspense_accountは仮受金の勘定科目コード2210です。/root/bank/recon_report.mdに## 대사 결과 요약、## 건수는 같은데 금액이 다른 건、## 금액은 같은데 건수가 다른 건、## 대사로는 잡히지 않는 것、## 차액정산 포지션、## 개인정보 취급の6つの節でレポートを書いてください(見出しは韓国語で、順に「突合結果の要約」「件数は同じで金額が違う件」「金額は同じで件数が違う件」「突合では捕まらないもの」「差額清算ポジション」「個人情報の取り扱い」という意味です)。口座番号は先頭3桁と末尾4桁だけを残して110-****-**1234の形にマスキングし、住民登録番号と顧客の実名はどんな形でも書かないでください。
参考
- CSVをSQLで扱うには、先にテーブルを作ってから取り込んでください:
CREATE TABLE partner(...)のあとに.import --csv --skip 1 <파일> partner(プレースホルダーはファイル名です)。 - 同じファイルを2回取り込むと、行が2倍になります。取り込みの前に
DROP TABLE IF EXISTS partnerを入れておけば、何回回しても同じです。 - 二重送金の超過分は
SUM(amount * (건수 - 1))で出します(プレースホルダーは件数です)。最初の送信分は正常なので、引きません。 - よくある間違い1: ステップ3で
stateで絞り込まないと、差異が2件ではなく18件に見えます。 - よくある間違い2: ステップ5で
msg_idで重複を探しても、電文番号は再送のたびに新しく採番されるので、何も出てきません。 - よくある間違い3: ステップ8で、調査画面に表示されていたからという理由で口座番号をそのまま写してしまいます。レポートに必要のない個人情報を残すこと自体が事故です。
こちらの元帳と相手の清算ファイルを作る
/root/bank/gen_settle.pyを作成して実行し、/root/bank/settle.dbと/root/bank/partner_20260825.csvを作ってください。
python3で/root/bank/settle.dbと/root/bank/partner_20260825.csvを作ります。電文259件のうち16件は、オーソリだけの状態です。
清算ファイルを取り込み、機関ごとの総計を合わせる
清算ファイルをsettle.dbにpartnerテーブルとして取り込み、/root/bank/recon_totals.csvに機関ごとの総計を出力してください。ヘッダー行はcounterparty,our_count,our_amount,partner_count,partner_amount,count_gap,amount_gapで、こちら側はstateがSETTLEDの件だけを数えます。
相手のファイルをsettle.dbにpartnerテーブルとして取り込めば、SQL 1回で突合できます。こちら側はstateがSETTLEDの件だけを数え、件数と金額を別々に見てください。
片側にしかない件を切り分ける
/root/bank/only_ours.csvと/root/bank/only_partner.csvをmsg_id,counterparty,amountのヘッダー行で出力し、/root/bank/side_counts.txtにonly_ours=、only_partner=、naive_only_ours=の3行を書いてください。最後の値は、stateを区別せずに突合したときの数です。
突合の対象は売上確定された件だけです。オーソリだけの件は、相手のファイルにないのが正常なので、stateで絞り込まないと、正常な16件がすべて異常な件として上がってきます。
金額が異なる件を探す
/root/bank/amount_mismatch.csvに、msg_id,our_amount,partner_amount,gapのヘッダー行で、金額が異なる件を出力してください。
両側にある電文番号どうしを結合して、金額が違うものだけを残してください。gapはこちらの金額から相手の金額を引いた値で、その合計は機関総計の金額差と一致するはずです。
冪等キーで二重送金を見つける
/root/bank/dup_msg.csvに、idem_key,msg_id,amountのヘッダー行で、重複した冪等キーにぶら下がる電文をすべて残し、/root/bank/dup.txtにdup_keys=、dup_msgs=、extra_amount=の3行を書いてください。
電文番号ではなく冪等キーでまとめてください。同じ冪等キーに電文が2つ以上ぶら下がっていれば、再送が重複処理されたということです。超過分は、キーごとに1件だけ残した残りです。
差額清算ポジションを出す
/root/bank/net_position.csvに、counterparty,out_amount,in_amount,netのヘッダー行で、機関ごとの差額清算ポジションを出してください。netは受取から支払を引いた値です。
機関ごとに支払(OUT)と受取(IN)をそれぞれ合算し、netは受取から支払を引いた値にします。対象は清算が完了した件です。
未決済と仮受金の残高を数える
/root/bank/pending.txtにauth_count=、auth_amount=、auth_out_amount=、auth_in_amount=、stale_auth=、suspense_account=の6行を書いてください。stale_authはsent_atが2026-08-22より前のオーソリの件で、suspense_accountは仮受金の勘定科目コード2210です。
オーソリだけの件を方向別に分けて数え、オーソリ日が古い件も別に数えてください。未決済は件数ではなく、経過日数で管理します。
マスキングした突合レポートを書く
/root/bank/recon_report.mdに## 대사 결과 요약、## 건수는 같은데 금액이 다른 건、## 금액은 같은데 건수가 다른 건、## 대사로는 잡히지 않는 것、## 차액정산 포지션、## 개인정보 취급の6つの節でレポートを書いてください(見出しは韓国語で、順に「突合結果の要約」「件数は同じで金額が違う件」「金額は同じで件数が違う件」「突合では捕まらないもの」「差額清算ポジション」「個人情報の取り扱い」という意味です)。口座番号は先頭3桁と末尾4桁だけを残して110-****-**1234の形にマスキングし、住民登録番号と顧客の実名はどんな形でも書かないでください。
6つの節が必要です。そして、この文書は顧客企業の外に出ます。口座番号はマスキングし、住民登録番号と実名はそもそも書かないでください。