DROP PROCEDURE ImportCellModemPlans_ATnT ;
SET @OLD_SQL_MODE = @@SQL_MODE , SQL_MODE = 'STRICT_TRANS_TABLES' ;
DELIMITER &&
CREATE PROCEDURE `ImportCellModemPlans_ATnT`(IN param_invoice_id INTEGER, IN param_invoice_identifier VARCHAR(50), IN take_notice TINYINT )
BEGIN
... 1300 lines later...
END &&
DELIMITER ;
SET SQL_MODE = @OLD_SQL_MODE ;
3 159 16:28:40 DROP PROCEDURE `Census_ImportPlanChange` 0 row(s) affected 0.000 sec
3 160 16:28:40 CREATE PROCEDURE `Census_ImportPlanChange`(IN Pre_OR_Post VARCHAR(50), IN posting_period VARCHAR(50))
BEGIN
# -------------------------------------------------------------------------------------
DROP TABLE IF EXISTS yPlan_Data_Change ;
# =====================================================================================
IF UPPER(Pre_OR_Post) = 'PRE' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_before_invoice = mc.intlplan_code
, z.domplancode_before_invoice = mc.domplan_code ;
COMMIT ;
END IF ;
# -------------------------------------------------------------------------------------
IF UPPER(Pre_OR_Post) = 'POST' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_after_invoice = mc.intlplan_code
, z.domplancode_after_invoice = mc.domplan_code ;
COMMIT ;
# ===================================================================================
UPDATE zModem_Census a
JOIN zModem_Census b
ON a.posting_period = b.posting_period
AND a.modem_config_id = b.modem_config_id
SET a.import_changed_plans_data = (b.intlplancode_before_invoice != b.intlplancode_after_invoice) OR (b.domplancode_before_invoice != b.domplancode_after_invoice)
WHERE a.posting_period = posting_period ;
COMMIT ;
END IF ;
END 0 row(s) affected 0.000 sec
3 155 16:28:27 DROP PROCEDURE ImportCellModemPlans_ATnT 0 row(s) affected 0.000 sec
3 155 16:28:27
DELIMITER $$
DROP PROCEDURE IF EXISTS `Census_ImportPlanChange` $$
CREATE DEFINER=`root`@`%` PROCEDURE `Census_ImportPlanChange`(
IN Pre_OR_Post VARCHAR(50),
IN posting_period VARCHAR(50)
)
BEGIN
IF UPPER(Pre_OR_Post) = 'PRE' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_before_invoice = mc.intlplan_code
, z.domplancode_before_invoice = mc.domplan_code ;
COMMIT ;
END IF ;
IF UPPER(Pre_OR_Post) = 'POST' THEN
UPDATE zModem_Census AS z
JOIN ModemConfig AS mc
ON mc.modem_config_id = z.modem_config_id
AND z.posting_period = posting_period
SET z.intlplancode_after_invoice = mc.intlplan_code
, z.domplancode_after_invoice = mc.domplan_code ;
COMMIT ;
UPDATE zModem_Census a
JOIN zModem_Census b
ON a.posting_period = b.posting_period
AND a.modem_config_id = b.modem_config_id
SET a.import_changed_plans_data = (b.intlplancode_before_invoice != b.intlplancode_after_invoice) OR (b.domplancode_before_invoice != b.domplancode_after_invoice)
WHERE a.posting_period = posting_period ;
COMMIT ;
END IF ;
END $$
DELIMITER ;
Maybe you can delete the definer part.