File: /www/exchange2/sql/schema/Exchange-2.0-Routines.sql
SET GLOBAL log_bin_trust_function_creators = 1;
/* Function for handling Total Reserves for Back Office */
DELIMITER $$
--
-- Functions
--
DROP FUNCTION IF EXISTS `getReserve`$$
CREATE DEFINER='root'@`%` FUNCTION `getReserve`(`expected_date` DATE,
`issue_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`reserve` DOUBLE(10, 2),
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`trade_comission` DOUBLE(7, 4),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`credebt_facility_type` VARCHAR(20),
`face_value_charge_applied` TINYINT(1),
`ldc_premium` DOUBLE(6, 3),
`agent_commission` DOUBLE(6, 3)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(issue_date)), 0),
@rdso = DATEDIFF(IF(reserve IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(issue_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (reserve IS NOT NULL AND (system_date = CURDATE() OR system_date >= payment_date)) THEN
RETURN reserve;
ELSEIF (`face_value_charge_applied` = 1) THEN
RETURN face_value - face_value * pp / 100 - face_value * @face_value_charge / 100 -
face_value * ldc_premium / 100 - trade_comission;
ELSEIF (@adso >= 180 AND override_180_day_rule = 0) THEN
RETURN face_value * @sr / 100 * 12 * (180 - @adso) / 360 - face_value * ldc_premium / 100 - trade_comission;
ELSE
RETURN face_value - face_value * @sr / 100 * 12 * 180 / 360 - face_value * pp / 100 +
face_value * @sr / 100 * 12 * (180 - @adso) / 360 - face_value * ldc_premium / 100 - trade_comission;
END IF;
END$$
DELIMITER ;
/* Function for handling Outstanding Reserves for Financial Statements */
DELIMITER $$
--
-- Functions
--
DROP FUNCTION IF EXISTS `getReserveOs`$$
CREATE DEFINER='root'@`%` FUNCTION `getReserveOs` (`expected_date` DATE,
`issue_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`reserve` DOUBLE(10,2),
`face_value` DOUBLE,
`sr` DOUBLE(4,3),
`pp` DOUBLE(5,3),
`trade_comission` DOUBLE(7,4),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5,3),
`credebt_facility_type` VARCHAR(20),
`face_value_charge_applied` TINYINT(1),
`ldc_premium` DOUBLE(6,3),
`agent_commission` DOUBLE(6,3)) RETURNS DECIMAL(20,10) BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(issue_date)), 0),
@rdso = DATEDIFF(IF(reserve IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(issue_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (reserve IS NOT NULL AND payment_date <= system_date) THEN
RETURN 0;
ELSEIF (`face_value_charge_applied` = 1) THEN
RETURN face_value - face_value * pp / 100 - face_value * @face_value_charge / 100 -
face_value * ldc_premium / 100 - trade_comission;
ELSEIF (@adso >= 180 AND override_180_day_rule = 0) THEN
RETURN 0;
ELSE
SET @retReserveOs = face_value - face_value * @sr / 100 * 12 * 180 / 360 - face_value * pp / 100 +
face_value * @sr / 100 * 12 * (180 - @adso) / 360 - face_value * ldc_premium / 100 - trade_comission;
IF (@retReserveOs < 0) THEN
RETURN 0;
ELSE
RETURN @retReserveOs;
END IF;
END IF;
END$$
DELIMITER ;
/* Function for handling d-ETR Calculations for Financial Statements */
DELIMITER $$
--
-- Functions
--
/* 1: Deferred Commission FS,
2: 3132 - LDC Reserve FS,
3: EDSO Reserves FS,
4: 8330 - LDC Reserves FS,
5: 1701 - LDC Accruals FS,
6: 8320 - Reserves Outstanding FS
7: 8320 - Reserves Outstanding (Back Office)
8: 8310 - Reserves Due
*/
DROP FUNCTION IF EXISTS `dETR_FS_Calculations`$$
CREATE DEFINER='root'@`%` FUNCTION `dETR_FS_Calculations`(
`ret_type` TINYINT(1),
`expected_date` DATE,
`issue_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`reserve` DOUBLE(10, 2),
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`trade_comission` DOUBLE(7, 4),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`credebt_facility_type` VARCHAR(20),
`face_value_charge_applied` TINYINT(1),
`ldc_premium` DOUBLE(6, 3),
`agent_commission` DOUBLE(6, 3)) RETURNS decimal(20,10)
BEGIN
SET
@mm = 180,
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(issue_date)), 0),
@rdso = DATEDIFF(IF(reserve IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(issue_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@pp = face_value * pp / 100,
@face_value_charge = face_value_charge * 100 / (100 - agent_commission),
@dc = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
face_value * @face_value_charge / 100,
face_value * @sr / 100 / 30 * LEAST(@edso,@mm)),
@ldc_reserve_3132 = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
face_value - @pp - face_value * @face_value_charge / 100,
face_value - (face_value * @sr / 100 / 30 * @mm) - @pp),
@edso_reserve = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
0.00,
IF(@edso >= @mm, 0, (face_value * @sr / 100 / 30) * (@mm - @edso))),
@ldc_accruals_1701 = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
0.00,
IF(override_180_day_rule > 0,
IF(@adso > @edso AND (LEAST(face_value * @sr / 100 / 30 * (@adso - @edso), @ldc_reserve_3132 + @edso_reserve) >= @ldc_reserve_3132 + @edso_reserve),
face_value * @sr / 100 / 30 * (@adso - @edso) - (@ldc_reserve_3132 + @edso_reserve), 0.00),
IF(@adso > @mm,(face_value * @sr / 100 / 30) * (@adso - LEAST(@edso, @mm)), 0.00))),
@ldc_reserve_8330 = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
0.00,
IF(override_180_day_rule > 0,
IF(@adso > @edso, LEAST(face_value * @sr / 100 / 30 * (@adso - @edso), @ldc_reserve_3132 + @edso_reserve), 0), /* + @ldc_accruals_1701 */
IF(@adso > @edso OR @adso >= @mm,
IF(@adso >= @mm, @ldc_reserve_3132, 0.00) + IF(@edso >= @mm, 0.00, face_value * @sr / 100 / 30 * (LEAST(@adso, @mm) - @edso)), 0.00))), /* + @ldc_accruals_1701 */
@ldc_premium = IF(face_value * ldc_premium / 100 > @ldc_reserve_3132 + @edso_reserve + trade_comission, 0.00, face_value * ldc_premium / 100),
@reserveOutstanding_8320 = @ldc_reserve_3132 + @edso_reserve - @ldc_reserve_8330, /* + @ldc_accruals_1701 */
@trade_commission = IF(@reserveOutstanding_8320 - @ldc_premium <= trade_comission, 0.00, trade_comission),
@reserveOutstanding_BO = @ldc_reserve_3132 + @edso_reserve - @ldc_reserve_8330 - @ldc_premium - @trade_commission; /* + @ldc_accruals_1701 */
IF (ret_type=1) THEN
RETURN @dc;
ELSEIF (ret_type=2) THEN
RETURN @ldc_reserve_3132;
ELSEIF (ret_type=3) THEN
RETURN @edso_reserve;
ELSEIF (ret_type=4) THEN
RETURN @ldc_reserve_8330;
ELSEIF (ret_type=5) THEN
RETURN @ldc_accruals_1701;
ELSEIF (ret_type=6) THEN
IF (reserve IS NOT NULL AND (system_date = CURDATE() OR system_date >= payment_date)) THEN
RETURN 0.0;
ELSE
RETURN @reserveOutstanding_8320;
END IF;
ELSEIF (ret_type=7) THEN
IF (reserve IS NOT NULL AND (system_date = CURDATE() OR system_date >= payment_date)) THEN
RETURN 0.00;
ELSE
RETURN @reserveOutstanding_BO;
END IF;
ELSEIF (ret_type=8) THEN
IF (reserve IS NOT NULL AND (system_date = CURDATE() OR system_date >= payment_date)) THEN
RETURN reserve;
ELSE
RETURN @reserveOutstanding_BO;
END IF;
ELSE
RETURN 0.00;
END IF;
END$$
DELIMITER ;
/* Function for handling b/c-ETR Calculations for Financial Statements */
DELIMITER $$
--
-- Functions
--
/* 1: Deferred Commission FS,
2: 8330 - LDC Reserves FS */
DROP FUNCTION IF EXISTS `bcETR_FS_Calculations`$$
CREATE DEFINER='root'@`%` FUNCTION `bcETR_FS_Calculations`(
`ret_type` TINYINT(1),
`expected_date` DATE,
`issue_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`reserve` DOUBLE(10, 2),
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`trade_comission` DOUBLE(7, 4),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`credebt_facility_type` VARCHAR(20),
`face_value_charge_applied` TINYINT(1),
`ldc_premium` DOUBLE(6, 3),
`agent_commission` DOUBLE(6, 3)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(issue_date)), 0),
@rdso = DATEDIFF(IF(reserve IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(issue_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@pp = face_value * pp / 100,
@face_value_charge = face_value_charge * 100 / (100 - agent_commission),
@ldc_premium = face_value * ldc_premium / 100,
@dc = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
(face_value * @face_value_charge / 100) + trade_comission + @ldc_premium,
IF(reserve IS NOT NULL, reserve, face_value * @sr / 100 / 30 * @edso) + trade_comission + @ldc_premium),
@ldc_reserve_8330 = IF(face_value_charge > 0 AND face_value_charge_applied = 1,
0.00,
IF(@adso > @edso, face_value * @sr / 100 / 30 * (@adso - @edso), 0.00));
IF (ret_type=1) THEN
RETURN @dc;
ELSEIF (ret_type=2) THEN
RETURN @ldc_reserve_8330;
ELSE
RETURN 0.00;
END IF;
END$$
DELIMITER ;
/* SET GLOBAL log_bin_trust_function_creators = 1; */
/* Function to calculate maximum Purchase Price % for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getMaxPurchasePrice`$$
CREATE DEFINER=`root`@`%` FUNCTION `getMaxPurchasePrice`(
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`agent_commission` DOUBLE(6, 3),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1),
`ldc_premium` DOUBLE(6, 3)) RETURNS decimal(20,10)
BEGIN
SET
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (`face_value_charge_applied` = 1) THEN
RETURN (face_value - (face_value * @face_value_charge / 100)) / face_value;
ELSE
RETURN (face_value - (face_value * @sr / 100 / 30 * 180)) / face_value;
END IF;
END$$
DELIMITER ;
/* Function to calculate EDSO Commission for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getEDSOCommission`$$
CREATE DEFINER=`root`@`%` FUNCTION `getEDSOCommission`(
`expected_date` DATE,
`start_date` DATE,
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`agent_commission` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(start_date)), 0),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (`face_value_charge_applied` = 1) THEN
RETURN face_value * @face_value_charge / 100;
ELSEIF (@edso >= 180 AND override_180_day_rule = 1) THEN
RETURN face_value * @sr / 100 / 30 * @edso;
ELSE
RETURN face_value * @sr / 100 / 30 * LEAST(@edso, 180);
END IF;
END$$
DELIMITER ;
/* Function to calculate LDC Reserve for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getLDCReserve`$$
CREATE DEFINER=`root`@`%` FUNCTION `getLDCReserve`(
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`agent_commission` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@sr = sr * 100 / (100 - agent_commission),
@pp = face_value * pp / 100,
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (`face_value_charge_applied` = 1) THEN
RETURN face_value - @pp - (face_value * @face_value_charge / 100);
ELSEIF (override_180_day_rule = 1) THEN
RETURN face_value - (face_value * @sr / 100 / 30 * 180) - @pp;
ELSE
RETURN face_value - (face_value * @sr / 100 / 30 * 180) - @pp;
END IF;
END$$
DELIMITER ;
/* Function to calculate EDSO Reserve for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getEDSOReserve`$$
CREATE DEFINER=`root`@`%` FUNCTION `getEDSOReserve`(
`expected_date` DATE,
`start_date` DATE,
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`agent_commission` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(start_date)), 0),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission);
IF (@edso >= 180 OR `face_value_charge_applied` = 1) THEN
RETURN 0;
ELSEIF (override_180_day_rule = 1) THEN
RETURN (face_value * @sr / 100 / 30) * (180 - @edso);
ELSE
RETURN (face_value * @sr / 100 / 30) * (180 - @edso);
END IF;
END$$
DELIMITER ;
/* Function to calculate LDC Accruals for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getLDCAccruals`$$
CREATE DEFINER=`root`@`%` FUNCTION `getLDCAccruals`(
`expected_date` DATE,
`start_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`agent_commission` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(start_date)), 0),
@rdso = DATEDIFF(IF(payment_date IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(start_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission),
@ldc_reserve = getLDCReserve(
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@edso_reserve = getEDSOReserve(
expected_date,
start_date,
face_value,
sr,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
);
IF (face_value_charge > 0 AND `face_value_charge_applied` = 1) THEN
RETURN 0;
ELSEIF (override_180_day_rule = 1) THEN
IF (@adso > @edso AND LEAST((face_value * @sr / 100 / 30) * (@adso - @edso), @ldc_reserve + @edso_reserve) >= @ldc_reserve + @edso_reserve) THEN
RETURN (face_value * @sr / 100 / 30) * (@adso - @edso) - (@ldc_reserve + @edso_reserve);
ELSE
RETURN 0;
END IF;
ELSE
IF (@adso > 180) THEN
RETURN (face_value * @sr / 100 / 30) * (@adso - LEAST(@edso, 180));
ELSE
RETURN 0;
END IF;
END IF;
END$$
DELIMITER ;
/* Function to calculate Settled Commission for d-ETR Trades */
DELIMITER $$
DROP FUNCTION IF EXISTS `getSettledCommission`$$
CREATE DEFINER=`root`@`%` FUNCTION `getSettledCommission`(
`expected_date` DATE,
`start_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`agent_commission` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(start_date)), 0),
@rdso = DATEDIFF(IF(payment_date IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(start_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission),
@ldc_reserve = getLDCReserve(
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@edso_reserve = getEDSOReserve(
expected_date,
start_date,
face_value,
sr,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@ldc_accruals = getLDCAccruals(
expected_date,
start_date,
payment_date,
system_date,
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
);
IF (`face_value_charge_applied` = 1) THEN
RETURN 0;
ELSEIF (override_180_day_rule = 1) THEN
IF (@adso > @edso) THEN
RETURN LEAST((face_value * @sr / 100 / 30) * (@adso - @edso), @ldc_reserve + @edso_reserve) + @ldc_accruals;
ELSE
RETURN 0;
END IF;
ELSE
IF (@adso > @edso OR @adso >= 180) THEN
IF (@adso >= 180) THEN
SET @var1 = @ldc_reserve;
ELSE
SET @var1 = 0;
END IF;
IF (@edso >= 180) THEN
SET @var2 = 0;
ELSE
SET @var2 = (face_value * @sr / 100 / 30) * (LEAST(@adso, 180) - @edso);
END IF;
RETURN @var1 + @var2 + @ldc_accruals;
ELSE
RETURN 0;
END IF;
END IF;
END$$
DELIMITER ;
/* Function to calculate Reserves available for d-ETR Trades at a given point in time */
DELIMITER $$
DROP FUNCTION IF EXISTS `getADSOReserve`$$
CREATE DEFINER=`root`@`%` FUNCTION `getADSOReserve`(
`expected_date` DATE,
`start_date` DATE,
`payment_date` DATE,
`system_date` DATE,
`reserve` DOUBLE(10, 2),
`face_value` DOUBLE,
`sr` DOUBLE(4, 3),
`pp` DOUBLE(5, 3),
`trade_comission` DOUBLE(7, 4),
`agent_commission` DOUBLE(6, 3),
`ldc_premium` DOUBLE(6, 3),
`override_180_day_rule` TINYINT(1),
`face_value_charge` DOUBLE(5, 3),
`face_value_charge_applied` TINYINT(1)) RETURNS decimal(20,10)
BEGIN
SET
@edso = IFNULL(DATEDIFF(DATE(expected_date), DATE(start_date)), 0),
@rdso = DATEDIFF(IF(payment_date IS NOT NULL AND payment_date <= system_date, payment_date, system_date),
DATE(start_date)),
@adso = IF(@edso > @rdso, @edso, @rdso),
@sr = sr * 100 / (100 - agent_commission),
@face_value_charge = face_value_charge * 100 / (100 - agent_commission),
@ldc_premium = face_value * ldc_premium / 100,
@ldc_reserve = getLDCReserve(
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@edso_reserve = getEDSOReserve(
expected_date,
start_date,
face_value,
sr,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@ldc_accruals = getLDCAccruals(
expected_date,
start_date,
payment_date,
system_date,
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@settled_commission = getSettledCommission(
expected_date,
start_date,
payment_date,
system_date,
face_value,
sr,
pp,
agent_commission,
override_180_day_rule,
face_value_charge,
face_value_charge_applied
),
@reserve_outstanding = ROUND(@ldc_reserve + @edso_reserve - @settled_commission + @ldc_accruals, 2);
IF (reserve IS NOT NULL AND (system_date = CURDATE() OR system_date >= payment_date)) THEN
RETURN reserve;
ELSEIF (@reserve_outstanding > 0) THEN
RETURN @reserve_outstanding - @ldc_premium - trade_comission;
ELSEIF (@ldc_reserve + @edso_reserve < 0) THEN
IF (@adso = @edso) THEN
RETURN @ldc_reserve + @edso_reserve - @ldc_premium - trade_comission;
ELSE
RETURN @ldc_reserve + @edso_reserve - @settled_commission - @ldc_premium - trade_comission;
END IF;
ELSE
RETURN (-1 * @ldc_accruals) - @ldc_premium - trade_comission;
END IF;
END$$
DELIMITER ;