Last active
September 30, 2025 13:45
-
-
Save psulightning/a18fb1ad621e6944b5fbc11eaa9d7acf to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DELIMITER $$ | |
| CREATE PROCEDURE sp_mass_payee_update (IN id_list JSON) | |
| BEGIN | |
| -- Declare variables and cursor for notification loop (must be before any SQL statements) | |
| DECLARE done INT DEFAULT 0; | |
| DECLARE cur_id BIGINT UNSIGNED; | |
| DECLARE cur_xpayer_id BIGINT UNSIGNED; | |
| DECLARE cur_notify_code INT; | |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; | |
| DECLARE cur CURSOR FOR | |
| SELECT id, xpayer_id, notify_code FROM tmp_payee_updates; | |
| -- Drop temp table if it exists | |
| DROP TEMPORARY TABLE IF EXISTS tmp_payee_updates; | |
| -- Build staging table with computed actions | |
| CREATE TEMPORARY TABLE tmp_payee_updates ( | |
| id BIGINT UNSIGNED PRIMARY KEY, | |
| xpayer_id BIGINT UNSIGNED, | |
| new_delivery_type VARCHAR(16), | |
| new_payee_status VARCHAR(32), | |
| notify_code INT | |
| ); | |
| INSERT INTO tmp_payee_updates (id, xpayer_id, new_delivery_type, new_payee_status, notify_code) | |
| SELECT | |
| p.id, | |
| p.xpayer_id, | |
| CASE | |
| WHEN p.usps_street_1 IS NOT NULL AND p.usps_city IS NOT NULL | |
| AND p.usps_zip IS NOT NULL AND p.zusps_state_id_str IS NOT NULL | |
| THEN 'check' | |
| END AS new_delivery_type, | |
| CASE | |
| WHEN p.usps_street_1 IS NULL OR p.usps_city IS NULL | |
| OR p.usps_zip IS NULL OR p.zusps_state_id_str IS NULL | |
| THEN 'undeliverable' | |
| END AS new_payee_status, | |
| CASE | |
| WHEN p.usps_street_1 IS NOT NULL AND p.usps_city IS NOT NULL | |
| AND p.usps_zip IS NOT NULL AND p.zusps_state_id_str IS NOT NULL | |
| THEN 3130 | |
| ELSE 4038 | |
| END AS notify_code | |
| FROM xpayee p | |
| JOIN JSON_TABLE(id_list, '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$')) jt ON p.id = jt.id | |
| WHERE p.payee_status = 'active'; | |
| -- Set-based update xpayee for checkStd | |
| UPDATE xpayee p | |
| JOIN tmp_payee_updates u ON p.id = u.id | |
| SET | |
| p.deliver_check_std = IF(u.new_delivery_type = 'check', 'on', p.deliver_check_std), | |
| p.deliver_elec_std = IF(u.new_delivery_type = 'check', 'off', p.deliver_elec_std), | |
| p.deliver_check_expedite = IF(u.new_delivery_type = 'check', 'off', p.deliver_check_expedite), | |
| p.deliver_elec_now = IF(u.new_delivery_type = 'check', 'off', p.deliver_elec_now), | |
| p.deliver_elec_today = IF(u.new_delivery_type = 'check', 'off', p.deliver_elec_today), | |
| p.zbillernet_id_1 = IF(u.new_delivery_type = 'check', zfunc_get_check_network_id(p.zfsp_id), p.zbillernet_id_1) | |
| WHERE u.new_delivery_type = 'check'; | |
| -- Set-based update xpayee for undeliverable | |
| UPDATE xpayee p | |
| JOIN tmp_payee_updates u ON p.id = u.id | |
| SET | |
| p.payee_status = 'undeliverable', | |
| p.payee_status_at = NOW(6) | |
| WHERE u.new_payee_status = 'undeliverable'; | |
| -- Set-based update xsched for checkStd | |
| UPDATE xsched s | |
| JOIN tmp_payee_updates u ON s.xpayee_id = u.id | |
| SET s.zdeliver_id_str = 'check' | |
| WHERE u.new_delivery_type = 'check' | |
| AND s.zsched_status_id_str NOT IN ('lock', 'cancelPending'); | |
| -- Set-based update xsched for undeliverable | |
| UPDATE xsched s | |
| JOIN tmp_payee_updates u ON s.xpayee_id = u.id | |
| SET s.zsched_status_id_str = 'onholdValidate' | |
| WHERE u.new_payee_status = 'undeliverable' | |
| AND s.zsched_status_id_str NOT IN ('lock', 'cancelPending'); | |
| -- Set-based update xmodel for undeliverable | |
| UPDATE xmodel m | |
| JOIN tmp_payee_updates u ON m.xpayee_id = u.id | |
| SET m.zmodel_status_id_str = 'onholdValidate' | |
| WHERE u.new_payee_status = 'undeliverable'; | |
| -- Notification loop (minimal, only for notification) | |
| OPEN cur; | |
| read_loop: LOOP | |
| FETCH cur INTO cur_id, cur_xpayer_id, cur_notify_code; | |
| IF done THEN | |
| LEAVE read_loop; | |
| END IF; | |
| IF cur_notify_code = 3130 THEN | |
| CALL zproc_msg_user_prep(3130, cur_xpayer_id, cur_id, 0, 'payee delivery method updated'); | |
| ELSE | |
| CALL zproc_msg_user_prep(4038, cur_xpayer_id, cur_id, 0, 'payee_undeliverable'); | |
| END IF; | |
| END LOOP; | |
| CLOSE cur; | |
| DROP TEMPORARY TABLE IF EXISTS tmp_payee_updates; | |
| END $$ | |
| DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment