id: vendor-bank-payout-precheck
namespace: company.team
description: |
Check a vendor payout batch before it is uploaded to the bank: IFSC and
account number format, NEFT/RTGS/IMPS limits, duplicate payments, and bank
details that differ from the approved vendor master. Only clean rows go into
the bank upload file.
inputs:
- id: payout_batch
type: FILE
required: false
description: CSV of the payments to make with the columns payment_id, vendor_id,
beneficiary_name, account_number, ifsc, amount (in rupees) and mode (NEFT,
RTGS or IMPS). Leave empty to use the built-in sample batch.
- id: vendor_master
type: FILE
required: false
description: CSV of the approved vendor bank details with the columns vendor_id,
vendor_name, account_number and ifsc. Leave empty to use the built-in
sample.
- id: rtgs_minimum
type: FLOAT
defaults: 200000
description: Smallest amount (in rupees) the bank accepts over RTGS.
- id: imps_maximum
type: FLOAT
defaults: 500000
description: Largest amount (in rupees) the bank accepts over IMPS.
- id: notify_slack
type: BOOL
defaults: false
description: Post the findings to Slack when at least one payment is blocked.
Requires the SLACK_WEBHOOK_URL secret.
triggers:
- id: weekly_payment_run
type: io.kestra.plugin.core.trigger.Schedule
description: Check the batch every Friday at 10:00 IST, before the weekly vendor
payment run. Shipped disabled; enable it once the flow reads your real
payout batch and vendor master instead of the samples.
cron: "0 10 * * FRI"
timezone: Asia/Kolkata
disabled: true
tasks:
- id: sample_payout_batch
type: io.kestra.plugin.core.storage.Write
description: A payout batch with one example of every problem the check catches.
Account numbers and IFSC codes are synthetic. Only used when payout_batch
is empty.
extension: .csv
content: |
payment_id,vendor_id,beneficiary_name,account_number,ifsc,amount,mode
P001,V001,Sahyadri Packaging LLP,50200012345678,HDFC0001234,185000.00,NEFT
P002,V002,Kaveri Agro Foods Pvt Ltd,30112233445,SBIN0004567,250000.00,RTGS
P003,V003,Deccan Steel Traders,123405001234,ICIC0002345,95000.00,RTGS
P004,V004,Ganga Textiles Pvt Ltd,917020045678901,UTIB0000123,620000.00,IMPS
P005,V005,Coromandel Logistics Pvt Ltd,4512369870,KKBK0000456,48000.00,NEFT
P006,V005,Coromandel Logistics Pvt Ltd,4512369870,KKBK0000456,48000.00,NEFT
P007,V006,Malabar Spice Exports,00601450001234, hdfc0000060 ,32500.50,neft
P008,V007,Thar Solar Systems Pvt Ltd,99887766554,SBIN0009876,410000.00,RTGS
P009,V008,Konkan Fisheries,2001234567X,BARB0VJKONK,15000.00,IMPS
P010,V009,Nilgiri Handicrafts,777705551234,ICIC1001111,22000.00,NEFT
P011,V011,Shree Ganesh Traders,50100099887766,HDFC0004321,12000.00,NEFT
P012,V010,Ladakh Apricot Collective,6012345678,IDIB000L001,7800.00,IMPS
P013,V003,Deccan Steel Traders,123405001234,ICIC0002345,0.00,NEFT
P014,V004,Ganga Textiles Pvt Ltd,50200012345678,HDFC0001234,9000.00,NEFT
- id: sample_vendor_master
type: io.kestra.plugin.core.storage.Write
description: The approved vendor bank details the batch is checked against. Only
used when vendor_master is empty.
extension: .csv
content: |
vendor_id,vendor_name,account_number,ifsc
V001,Sahyadri Packaging LLP,50200012345678,HDFC0001234
V002,Kaveri Agro Foods Pvt Ltd,30112233445,SBIN0004567
V003,Deccan Steel Traders,123405001234,ICIC0002345
V004,Ganga Textiles Pvt Ltd,917020045678901,UTIB0000123
V005,Coromandel Logistics Pvt Ltd,4512369870,KKBK0000456
V006,Malabar Spice Exports,00601450001234,HDFC0000060
V007,Thar Solar Systems Pvt Ltd,38765432109,SBIN0001234
V008,Konkan Fisheries,20012345678,BARB0VJKONK
V009,Nilgiri Handicrafts,777705551234,ICIC0001111
V010,Ladakh Apricot Co-operative,6012345678,IDIB000L001
- id: check_payouts
type: io.kestra.plugin.jdbc.duckdb.Queries
description: Normalise every payment and run all checks in DuckDB, without
calling any bank or external service. The per-payment report and the bank
upload file (clean payments only) are exported to CSV, and the last query
returns the summary.
inputFiles:
payouts.csv: "{{ inputs.payout_batch ?? outputs.sample_payout_batch.uri }}"
vendors.csv: "{{ inputs.vendor_master ?? outputs.sample_vendor_master.uri }}"
outputFiles:
- payout_report.csv
- bank_upload.csv
fetchType: FETCH_ONE
sql: |
CREATE TABLE payouts AS
SELECT
row_number() OVER () AS line,
trim(payment_id) AS payment_id,
trim(vendor_id) AS vendor_id,
trim(coalesce(beneficiary_name, '')) AS beneficiary_name,
regexp_replace(coalesce(account_number, ''), '\s', '', 'g') AS account_number,
upper(regexp_replace(coalesce(ifsc, ''), '\s', '', 'g')) AS ifsc,
coalesce(ifsc, '') AS ifsc_raw,
trim(amount) AS amount_raw,
TRY_CAST(trim(amount) AS DECIMAL(15, 2)) AS amount,
upper(trim(coalesce(mode, ''))) AS mode
FROM read_csv('{{ workingDir }}/payouts.csv', header = true, all_varchar = true);
CREATE TABLE vendors AS
SELECT
trim(vendor_id) AS vendor_id,
trim(vendor_name) AS vendor_name,
regexp_replace(coalesce(account_number, ''), '\s', '', 'g') AS account_number,
upper(regexp_replace(coalesce(ifsc, ''), '\s', '', 'g')) AS ifsc
FROM read_csv('{{ workingDir }}/vendors.csv', header = true, all_varchar = true);
CREATE TABLE enriched AS
SELECT
p.*,
v.vendor_name AS master_name,
v.account_number AS master_account,
v.ifsc AS master_ifsc,
v.vendor_id IS NOT NULL AS known_vendor,
row_number() OVER (PARTITION BY p.account_number, p.ifsc, p.amount ORDER BY p.line) AS repeat_no,
first_value(p.payment_id) OVER (PARTITION BY p.account_number, p.ifsc, p.amount ORDER BY p.line) AS first_payment,
(SELECT string_agg(DISTINCT o.vendor_id, ', ')
FROM vendors o
WHERE o.account_number = p.account_number AND o.vendor_id <> p.vendor_id) AS other_vendors
FROM payouts p
LEFT JOIN vendors v ON v.vendor_id = p.vendor_id;
CREATE TABLE report AS
SELECT
payment_id,
vendor_id,
beneficiary_name,
account_number,
ifsc,
amount,
mode,
CASE
WHEN len(errors) > 0 THEN 'ERROR'
WHEN len(warnings) > 0 THEN 'WARNING'
ELSE 'OK'
END AS status,
array_to_string(list_concat(errors, warnings), '; ') AS issues,
array_to_string(notes, '; ') AS notes
FROM (
SELECT
*,
list_filter([
CASE WHEN NOT regexp_full_match(ifsc, '[A-Z]{4}0[A-Z0-9]{6}') THEN 'IFSC "' || ifsc || '" is not 4 letters + 0 + 6 letters or digits' END,
CASE WHEN NOT regexp_full_match(account_number, '[0-9]{9,18}') THEN 'Account number "' || account_number || '" must be 9 to 18 digits' END,
CASE WHEN amount IS NULL THEN 'Amount "' || amount_raw || '" is not a number with at most 2 decimals' END,
CASE WHEN amount <= 0 THEN 'Amount must be more than zero' END,
CASE WHEN mode NOT IN ('NEFT', 'RTGS', 'IMPS') THEN 'Mode "' || mode || '" must be NEFT, RTGS or IMPS' END,
CASE WHEN mode = 'RTGS' AND amount > 0 AND amount < {{ inputs.rtgs_minimum }} THEN 'RTGS needs at least Rs ' || CAST({{ inputs.rtgs_minimum }} AS DECIMAL(15, 2)) || ', so send Rs ' || amount || ' by NEFT' END,
CASE WHEN mode = 'IMPS' AND amount > {{ inputs.imps_maximum }} THEN 'IMPS allows at most Rs ' || CAST({{ inputs.imps_maximum }} AS DECIMAL(15, 2)) || ', so send Rs ' || amount || ' by NEFT or RTGS' END,
CASE WHEN beneficiary_name = '' THEN 'Beneficiary name is empty' END,
CASE WHEN repeat_no > 1 THEN 'Same account, IFSC and amount as ' || first_payment || ' (paid twice?)' END,
CASE WHEN NOT known_vendor THEN 'Vendor ' || vendor_id || ' is not in the approved vendor master' END,
CASE WHEN known_vendor AND (account_number <> master_account OR ifsc <> master_ifsc)
THEN 'Bank details differ from the vendor master (' || master_account || ' / ' || master_ifsc || '): confirm the change with the vendor by phone before paying' END,
CASE WHEN other_vendors IS NOT NULL THEN 'Account ' || account_number || ' belongs to vendor ' || other_vendors || ' in the vendor master' END
], lambda x: x IS NOT NULL) AS errors,
list_filter([
CASE WHEN known_vendor AND beneficiary_name <> '' AND lower(beneficiary_name) <> lower(master_name)
THEN 'Beneficiary name "' || beneficiary_name || '" differs from the vendor master name "' || master_name || '"' END
], lambda x: x IS NOT NULL) AS warnings,
list_filter([
CASE WHEN ifsc_raw <> ifsc THEN 'IFSC normalised from "' || ifsc_raw || '"' END
], lambda x: x IS NOT NULL) AS notes
FROM enriched
)
ORDER BY CASE status WHEN 'ERROR' THEN 0 WHEN 'WARNING' THEN 1 ELSE 2 END, payment_id;
COPY report TO '{{ outputFiles["payout_report.csv"] }}' (HEADER, DELIMITER ',');
COPY (
SELECT payment_id, beneficiary_name, account_number, ifsc, amount, mode
FROM report
WHERE status <> 'ERROR'
ORDER BY payment_id
) TO '{{ outputFiles["bank_upload.csv"] }}' (HEADER, DELIMITER ',');
SELECT
count(*) AS total_payments,
coalesce(sum(amount), 0) AS total_amount,
count(*) FILTER (WHERE status <> 'ERROR') AS ready_count,
coalesce(sum(amount) FILTER (WHERE status <> 'ERROR'), 0) AS ready_amount,
count(*) FILTER (WHERE status = 'ERROR') AS blocked_count,
coalesce(sum(amount) FILTER (WHERE status = 'ERROR'), 0) AS blocked_amount,
count(*) FILTER (WHERE status = 'WARNING') AS warning_count,
coalesce(string_agg(payment_id || ' ' || vendor_id || ': ' || issues, chr(10)) FILTER (WHERE status = 'ERROR'), '') AS error_details,
coalesce(string_agg(payment_id || ' ' || vendor_id || ': ' || issues, chr(10)) FILTER (WHERE status = 'WARNING'), '') AS warning_details
FROM report;
- id: route_findings
type: io.kestra.plugin.core.flow.If
description: Branch on the result. Blocked payments are left out of the bank
upload file, logged with the reason and optionally sent to Slack; a clean
batch is logged so the execution history doubles as an audit trail of
every payment run.
condition: "{{ outputs.check_payouts.outputs[0].row.blocked_count > 0 }}"
then:
- id: log_blocked
type: io.kestra.plugin.core.log.Log
level: WARN
description: Write every blocked payment and the exact reason to the execution logs.
message: |
Payout check: {{ outputs.check_payouts.outputs[0].row.blocked_count }} of {{ outputs.check_payouts.outputs[0].row.total_payments }} payments blocked (Rs {{ outputs.check_payouts.outputs[0].row.blocked_amount | numberFormat('#,##0.00') }}). {{ outputs.check_payouts.outputs[0].row.ready_count }} ready for upload (Rs {{ outputs.check_payouts.outputs[0].row.ready_amount | numberFormat('#,##0.00') }}), {{ outputs.check_payouts.outputs[0].row.warning_count }} with warnings.
Blocked:
{{ outputs.check_payouts.outputs[0].row.error_details }}
Warnings:
{{ outputs.check_payouts.outputs[0].row.warning_details }}
- id: slack_enabled
type: io.kestra.plugin.core.flow.If
description: Only call Slack when the notify_slack input is true, so the flow
runs out of the box without any secret.
condition: "{{ inputs.notify_slack }}"
then:
- id: alert_accounts_payable
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
description: Tell the accounts payable team which payments to fix before the
batch is uploaded.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
messageText: |
:warning: Payout check blocked {{ outputs.check_payouts.outputs[0].row.blocked_count }} of {{ outputs.check_payouts.outputs[0].row.total_payments }} payments (Rs {{ outputs.check_payouts.outputs[0].row.blocked_amount | numberFormat('#,##0.00') }}).
{{ outputs.check_payouts.outputs[0].row.error_details }}
Full report: execution {{ execution.id }} in flow {{ flow.namespace }}.{{ flow.id }}
else:
- id: log_clean
type: io.kestra.plugin.core.log.Log
description: Record a clean batch.
message: "Payout check passed: {{
outputs.check_payouts.outputs[0].row.total_payments }} payments (Rs {{
outputs.check_payouts.outputs[0].row.total_amount |
numberFormat('#,##0.00') }}) ready for upload, {{
outputs.check_payouts.outputs[0].row.warning_count }} warning(s)."
errors:
- id: log_check_failure
type: io.kestra.plugin.core.log.Log
level: ERROR
description: Make a failed check loud. If a file is missing or malformed, no
payment was checked, and a silent failure must not be mistaken for a clean
batch.
message: "Payout check FAILED in {{ flow.namespace }}.{{ flow.id }} (execution
{{ execution.id }}). No payment was checked; do not upload the batch.
Check the columns of payout_batch and vendor_master."
outputs:
- id: payout_report
type: FILE
description: Per-payment CSV report with status (ERROR, WARNING or OK), the
reasons and the normalised bank details.
value: "{{ outputs.check_payouts.outputFiles['payout_report.csv'] }}"
- id: bank_upload
type: FILE
description: The payments that passed (OK or WARNING) with payment_id,
beneficiary_name, account_number, ifsc, amount and mode, ready to map to
your bank's bulk upload format.
value: "{{ outputs.check_payouts.outputFiles['bank_upload.csv'] }}"
- id: check_summary
type: JSON
description: 'Counts, amounts and details, e.g. {"total_payments": 14,
"ready_count": 5, "blocked_count": 9, ...}.'
value: "{{ outputs.check_payouts.outputs[0].row | toJson }}"