174 lines
12 KiB
SQL
174 lines
12 KiB
SQL
-- ============================================================
|
|
-- Facility Charge Auto-Seed Migration
|
|
-- Step 1: Create mapping table
|
|
-- Step 2: Insert charges into m_charge
|
|
-- Step 3: Insert mapping with charge IDs
|
|
-- charge_applies_to_enum: 2=SAVINGS, 5=CURRENT_ACCOUNT
|
|
-- charge_time_enum: 3=SAVINGS_ACTIVATION, 6=ANNUAL_FEE, 7=MONTHLY_FEE
|
|
-- charge_calculation_enum: 1=FLAT
|
|
-- ============================================================
|
|
|
|
-- Step 1: Mapping table
|
|
CREATE TABLE IF NOT EXISTS m_facility_charge_mapping (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
facility_type VARCHAR(60) NOT NULL UNIQUE,
|
|
charge_id BIGINT,
|
|
charge_name VARCHAR(200) NOT NULL,
|
|
default_amount DECIMAL(19,6) NOT NULL DEFAULT 0.000000,
|
|
charge_time_type INT NOT NULL DEFAULT 3,
|
|
charge_applies_to INT NOT NULL DEFAULT 2,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
FOREIGN KEY (charge_id) REFERENCES m_charge(id)
|
|
);
|
|
|
|
-- Step 2: Insert charges into m_charge (only if not already exists)
|
|
-- income_or_liability_account_id = first available INCOME DETAIL GL account
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'ATM Facility Charge', 'INR', 100.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'ATM Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'ATM Withdrawal Charge', 'INR', 20.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'ATM Withdrawal Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'UPI Facility Charge', 'INR', 100.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'UPI Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'POS Transaction Charge', 'INR', 100.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'POS Transaction Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Cheque Book Charge', 'INR', 300.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Cheque Book Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'IMPS Facility Charge', 'INR', 5.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'IMPS Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'SMS Alert Charge', 'INR', 15.00, 2, 7, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'SMS Alert Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'BBPS Facility Charge', 'INR', 2.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'BBPS Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Insurance Cover Charge', 'INR', 500.00, 2, 6, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Insurance Cover Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'BEPG Facility Charge', 'INR', 5.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'BEPG Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'NEFT Facility Charge', 'INR', 5.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'NEFT Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'RTGS Facility Charge', 'INR', 25.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'RTGS Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Inter-Bank Cap Charge', 'INR', 0.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Inter-Bank Cap Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Withdrawal Facility Charge', 'INR', 5.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Withdrawal Facility Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Deposit KYC Charge', 'INR', 5.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Deposit KYC Charge' AND is_deleted = 0);
|
|
|
|
INSERT IGNORE INTO m_charge
|
|
(name, currency_code, amount, charge_applies_to_enum, charge_time_enum,
|
|
charge_calculation_enum, is_penalty, is_active, is_deleted,
|
|
is_free_withdrawal, is_payment_type, income_or_liability_account_id)
|
|
SELECT 'Virtual Debit Card Charge', 'INR', 199.00, 2, 3, 1, 0, 1, 0, 0, 0,
|
|
(SELECT id FROM acc_gl_account WHERE classification_enum = 4 AND usage_enum = 1 AND disabled = 0 ORDER BY id LIMIT 1)
|
|
WHERE NOT EXISTS (SELECT 1 FROM m_charge WHERE name = 'Virtual Debit Card Charge' AND is_deleted = 0);
|
|
|
|
-- Step 3: Insert mapping linking facility_type → charge_id
|
|
INSERT IGNORE INTO m_facility_charge_mapping
|
|
(facility_type, charge_name, default_amount, charge_time_type, charge_applies_to, charge_id)
|
|
VALUES
|
|
('ATM_FACILITY', 'ATM Facility Charge', 100.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'ATM Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('ATM_WITHDRAWAL', 'ATM Withdrawal Charge', 20.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'ATM Withdrawal Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('UPI_FACILITY', 'UPI Facility Charge', 100.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'UPI Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('POS_TRANSACTIONS', 'POS Transaction Charge', 100.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'POS Transaction Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('CHEQUE_BOOK', 'Cheque Book Charge', 300.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'Cheque Book Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('IMPS_FACILITY', 'IMPS Facility Charge', 5.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'IMPS Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('SMS_ALERTS', 'SMS Alert Charge', 15.00, 7, 2, (SELECT id FROM m_charge WHERE name = 'SMS Alert Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('BBPS_FACILITY', 'BBPS Facility Charge', 2.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'BBPS Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('INSURANCE_COVER', 'Insurance Cover Charge', 500.00, 6, 2, (SELECT id FROM m_charge WHERE name = 'Insurance Cover Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('BEPG_FACILITY', 'BEPG Facility Charge', 5.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'BEPG Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('NEFT_FACILITY', 'NEFT Facility Charge', 5.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'NEFT Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('RTGS_FACILITY', 'RTGS Facility Charge', 25.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'RTGS Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('INTER_BANK_TOTAL_CAP','Inter-Bank Cap Charge', 0.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'Inter-Bank Cap Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('WITHDRAWAL_FACILITY', 'Withdrawal Facility Charge', 5.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'Withdrawal Facility Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('DEPOSIT_FACILITY_KYC','Deposit KYC Charge', 5.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'Deposit KYC Charge' AND is_deleted = 0 LIMIT 1)),
|
|
('VIRTUAL_DEBIT_CARD', 'Virtual Debit Card Charge', 199.00, 3, 2, (SELECT id FROM m_charge WHERE name = 'Virtual Debit Card Charge' AND is_deleted = 0 LIMIT 1));
|