File: /www/exchange2/sql/custom/TK60916-fix-sell-rate-for-rsa.sql
/* TK60916: script to correct the Sell Rate in all RSAs for all Debtors for Originator: Duo Operations Limited [12105001835] */
SELECT
@originator_reference_list := GROUP_CONCAT(organisation_reference_id)
FROM (
SELECT
od.organisation_reference_id as organisation_reference_id/*,
al.organisation_id,
al.created_at,
al.title,
CAST(al.old_value as DECIMAL(12, 4)) as old_value,
od.organisation_name */
FROM
activity_logs al
LEFT JOIN organisation_details od ON od.organisation_id = al.organisation_id
JOIN (
SELECT
organisation_id,
MAX(created_at) as created_at
FROM
activity_logs
WHERE
title = 'UPDATE_RISK_SELLRATE'
AND created_at >= "2021-03-03 00:00:00"
GROUP BY organisation_id
) as al_bis
ON al.organisation_id = al_bis.organisation_id AND al.created_at = al_bis.created_at
WHERE
al.title = 'UPDATE_RISK_SELLRATE'
/*AND al.organisation_id = 1835*/
ORDER BY al.created_at DESC
) as T;
SET session group_concat_max_len=15000;
/* get all Debtor Reference IDs */
SELECT @debtor_id_list := GROUP_CONCAT(debtor_id)
FROM (
SELECT
dd.debtor_id as debtor_id/*,
dd.debtor_reference_id,
dd.debtor_name,
od.organisation_name,
od.organisation_reference_id*/
FROM organisation_details as od
LEFT JOIN debtor_relation as dr ON dr.organisation_id = od.organisation_id
LEFT JOIN debtors_detail as dd ON dd.debtor_id = dr.debtor_id
WHERE
dd.type = 'debtor'
AND FIND_IN_SET(od.organisation_reference_id, @originator_reference_list)
GROUP BY dd.debtor_id ASC
) as T;
/* get RSA IDs */
SELECT
@rsa_id_list := GROUP_CONCAT(invoice_id)
FROM (
SELECT
im.invoice_id as invoice_id /*,
od.organisation_name,
od.organisation_reference_id,
dd.debtor_name,
dd.debtor_reference_id,
im.debtor_id,
im.max_thirty_day_reserve,
im.buy_max_thirty_day_reserve,
im.discount_rate,
im.orpa_id,
dr.status,
dr.revolving_etr,
od.agreed_rate*/
FROM
invoice_master im
LEFT JOIN debtors_detail dd ON dd.debtor_id = im.debtor_id
LEFT JOIN debtor_relation dr ON dr.debtor_id = im.debtor_id
LEFT JOIN organisation_details od ON od.user_id = im.user_id
WHERE
FIND_IN_SET(im.debtor_id, @debtor_id_list)
AND im.revolving = 1
AND im.root_invoice_id = 0
AND im.draft = 0
AND ROUND(im.max_thirty_day_reserve, 7) != ROUND(od.agreed_rate, 7)
) as T;
/* update Sell Rate in RSAs */
UPDATE
invoice_master im,
organisation_details od
SET
im.max_thirty_day_reserve = od.agreed_rate,
im.buy_max_thirty_day_reserve = od.agreed_rate,
im.discount_rate = od.agreed_rate
WHERE
im.user_id = od.user_id
AND FIND_IN_SET(im.invoice_id, @rsa_id_list);
/* backup Sell Rates */
SELECT
im.invoice_id,
im.max_thirty_day_reserve,
im.buy_max_thirty_day_reserve,
im.discount_rate
FROM
invoice_master as im
WHERE
im.invoice_id IN (177198,177199,177203,177204,177208,177210,177218,177219,177221,177229,177230,177231,177232,177233,177234,177235,177236,177249,177270,177271,177272,177273,177274,177275,177276,177278,177280,177295,177314,177315,177343,177344,177345,177346,177353,177357,177358,177361,177370,177371,177372,177373,177375,177379,177380,177389,177390,177391,177401,177427,177431,177433,177434,177435,177437,177438,177492,177516,177518,177519,177520,177521,177522,177523,177524,177555,177569,177570,177594,177595,177606,177607,177608,177611,177613,177615,177616,177617,177618,177619,177620,177621,177622,177623,177625,177626,177627,177628,177629,177631,177632,177633,177634,177635,177636,177637,177679,177708,177718,177719,177720,177721,177722,177723,177724,177725,177727,177728,177758,177764,177765,177766,177767,177768,177769,177772,177778,177781,177782,177815,177816,177827,177838,177880,177881,177882,177883,177884,177885,177886,177887,177888,177889,177890,177891,177892,177893,177894,177895,177896,177897,177898,177903,177918,177937,177938,177939,177940,177941,177942,177943,177946,177947,177948,177949,177950,177952,177953,177982,177984,178043,178044,178045,178047,178048,178081,178082,178083,178084,178085,178086,178087,178088,178089,178090,178091,178092,178093,178094,178095,178096,178097,178098,178100,178115,178140,178141,178142,178216,178277,178288,178290,178291,178292,178293,178295,178317,178318,178319);
/* update d-ETR Trades that were traded with inorrect Sell Rate */
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177198;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177199;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177203;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177204;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177208;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177210;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177218;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177219;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177221;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177229;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177230;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177231;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177232;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177233;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177234;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177235;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177236;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177249;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177270;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177271;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177272;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177273;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177274;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177275;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177276;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177278;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177280;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177295;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177314;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177315;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177343;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177344;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177345;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177346;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177353;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177357;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177358;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177361;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177370;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177371;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177372;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177373;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177375;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177379;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177380;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177389;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177390;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177391;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177401;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177427;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177431;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177433;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177434;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177435;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177437;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177438;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177492;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177516;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177518;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177519;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177520;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177521;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177522;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177523;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177524;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177555;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177569;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177570;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177594;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177595;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177606;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177607;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177608;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177611;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177613;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177615;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177616;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177617;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177618;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177619;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177620;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177621;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177622;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177623;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177625;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177626;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177627;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177628;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177629;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177631;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177632;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177633;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177634;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177635;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177636;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177637;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177679;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177708;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177718;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177719;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177720;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177721;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177722;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177723;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177724;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177725;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177727;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177728;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177758;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177764;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177765;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177766;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177767;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177768;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177769;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177772;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177778;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177781;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177782;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177815;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177816;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177827;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177838;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177880;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177881;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177882;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177883;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177884;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177885;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177886;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177887;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177888;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177889;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177890;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177891;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177892;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177893;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177894;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177895;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177896;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177897;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177898;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177903;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177918;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177937;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177938;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177939;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177940;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177941;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177942;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177943;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177946;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177947;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177948;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177949;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177950;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177952;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 177953;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177982;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 177984;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178043;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178044;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178045;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178047;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178048;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178081;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178082;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178083;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178084;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178085;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178086;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178087;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178088;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178089;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178090;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178091;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178092;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178093;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178094;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178095;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178096;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178097;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178098;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178100;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178115;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178140;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178141;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178142;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178216;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178277;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178288;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178290;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178291;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178292;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178293;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178295;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178317;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178318;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178319;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178330;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178331;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178332;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178342;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178344;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 178346;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178367;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 178368;
/* load all latest d-ETR Sell Rate changes for all Originators after 2021-03-03 00:00:00 */
DROP TABLE IF EXISTS tk60916_detr_sell_rates_changes;
CREATE TABLE IF NOT EXISTS tk60916_detr_sell_rates_changes(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
organisation_id BIGINT(11) NOT NULL UNIQUE,
organisation_reference_id BIGINT(11) NOT NULL UNIQUE,
organisation_name VARCHAR(64) NOT NULL,
updated_at DATETIME NOT NULL,
activity_title VARCHAR(100) NOT NULL,
sell_rate DOUBLE(6,4) NOT NULL
);
ALTER TABLE tk60916_detr_sell_rates_changes
ADD INDEX(organisation_name),
ADD INDEX(updated_at),
ADD INDEX(activity_title),
ADD INDEX(sell_rate);
INSERT INTO tk60916_detr_sell_rates_changes
(
organisation_id,
organisation_reference_id,
organisation_name,
updated_at,
activity_title,
sell_rate
) SELECT
al.organisation_id as organisation_id,
od.organisation_reference_id as organisation_reference_id,
od.organisation_name as organisation_name,
al.created_at as updated_at,
al.title as activity_title,
CAST(al.old_value as DECIMAL(12, 4)) as sell_rate
FROM
activity_logs al
LEFT JOIN organisation_details od ON od.organisation_id = al.organisation_id
JOIN (
SELECT
organisation_id,
MAX(created_at) as created_at
FROM
activity_logs
WHERE
title = 'UPDATE_RISK_SELLRATE'
AND created_at >= "2021-03-03 00:00:00"
GROUP BY organisation_id
) as al_bis
ON al.organisation_id = al_bis.organisation_id AND al.created_at = al_bis.created_at
WHERE
al.title = 'UPDATE_RISK_SELLRATE'
ORDER BY al.created_at DESC;
SET session group_concat_max_len = 15000;
/* get all Debtor Reference IDs */
SELECT @debtor_id_list := GROUP_CONCAT(debtor_id)
FROM (
SELECT
dd.debtor_id as debtor_id/*,
dd.debtor_reference_id,
dd.debtor_name,
od.organisation_name,
od.organisation_reference_id*/
FROM organisation_details as od
LEFT JOIN debtor_relation as dr ON dr.organisation_id = od.organisation_id
LEFT JOIN debtors_detail as dd ON dd.debtor_id = dr.debtor_id
WHERE
dd.type = 'debtor'
AND od.organisation_reference_id IN
(
SELECT ALL
organisation_reference_id
FROM
tk60916_detr_sell_rates_changes
)
GROUP BY dd.debtor_id ASC
) as T;
/* get invoice IDs */
SELECT
invoice_master.invoice_id as ex20_id,
tr.cm_id as related_entity_id,
organisation_details.organisation_reference_id as ex20_related_entity_id,
'Originator' as related_role_id,
IF(organisation_details.organisation_trade_name,
organisation_details.organisation_trade_name,
organisation_details.organisation_name) as originator_name,
debtors_detail.cm_id as entity_id,
debtors_detail.debtor_reference_id as ex20_entity_id,
IF(debtors_detail.trade_name, debtors_detail.trade_name,
debtors_detail.debtor_name) as entity_name,
'Organisation' as entity_type,
'Debtor' as role_id,
debtors_master.master_reference_id as parent_entity_id,
IF(debtors_master.debtor_trade_name, debtors_master.debtor_trade_name,
debtors_master.debtor_name) as parent_entity_name,
invoice_master.trade_reference_id as exchange_ref_id,
DATE(invoice_master.actual_date) as issued_on,
convert(binary convert(invoice_master.invoice_no using latin1) using utf8) as reference,
DATE(DATE(invoice_master.created_at)) as posted_on,
DATE(DATE(invoice_master.logapprove_date)) as traded_on,
'1' as period_type,
IFNULL(DATEDIFF(DATE(expected_date), DATE(actual_date)), 0) as period,
IFNULL(DATEDIFF(DATE(expected_date), DATE(actual_date)), 0) as edso,
DATEDIFF(IF(payment_trans.transaction_date <= DATE(NOW()),
payment_trans.transaction_date, DATE(NOW())), DATE(actual_date)) as rdso,
currencies.currency_code as currency_id,
ROUND(invoice_master.face_value, 2) as face_value,
IF(currencies.currency_code = 'EUR', 1.000, traded_rates.currency_rate) as traded_fx,
IF(IF(invoice_master.face_value_charge_applied = 1,
IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge,
IF(debtors_detail.type = 'debtor', organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100, '') > 0,
2, 1) as finance_type,
IF(invoice_master.face_value_charge_applied = 1, IFNULL(invoice_master.face_value_charge,
IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.`type` = 'debtor',
organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100, 0) as fixed_charge,
(IFNULL(invoice_closed_attributes.purchase_discount, 0) / 100) as purchased,
(invoice_master.max_thirty_day_reserve / 100) as sell_rate,
(invoice_master.ldc_premium / 100) as ldc_premium,
(invoice_master.agent_commission / 100) as agent_premium,
IFNULL(invoice_closed_attributes.processing_fee, 0) as trade_commission,
IFNULL(invoice_master.traded_processing_fee, 0) as processing_commission,
IFNULL(invoice_master.drp_processing_fee, 0) as drp_processing_commission,
IF(main_trans.transaction_type = 6,
DATE(payment_trans.transaction_date), '') as settled_on,
IF(currencies.currency_code='EUR', 1.000,
IF(invoice_master.reserve IS NULL,
currencies.currency_rate, reserve_rates.currency_rate)) as settled_fx,
DATE(drp.transaction_date) as closed_on,
IF(currencies.currency_code='EUR', 1.000,
IF(invoice_master.reserve IS NOT NULL
AND invoice_master.reserve_on = 1, drp_rates.currency_rate,
currencies.currency_rate)) as closed_fx,
IF(invoice_master.override_180_day_rule, 1, 0) as disable_180_days,
IF(debtor_address_details.address_country = 104
OR debtor_address_details_bill.address_country = 104,
IF(originator_address_details.address_country = 104, 1, 0), 0) as tax_type_inc_or_exc,
3 as tax_id_on_purchase,
3 as tax_id_on_sales,
IF(invoice_master.is_aetr = 1, 'a', invoice_master.etr_type) as etr_type,
organisation_details.agreed_rate / 100 as org_sell_rate,
tktable.updated_at as sell_rate_update
FROM invoice_master
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id
LEFT JOIN temp_registration as tr ON tr.user_id = organisation_details.user_id
LEFT JOIN address_details as debtor_address_details
ON debtor_address_details.address_id = debtors_detail.address_id
LEFT JOIN address_details as debtor_address_details_bill
ON debtor_address_details_bill.address_id = debtors_detail.billing_address_id
LEFT JOIN address_details as originator_address_details
ON originator_address_details.address_id = organisation_details.organisation_regoffice_id
LEFT JOIN debtors_master ON debtors_master.debtor_master_id = debtors_detail.parent_id
LEFT JOIN invoice_closed ON invoice_closed.invoice_id = invoice_master.invoice_id
LEFT JOIN invoice_closed_attributes
ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions as main_trans
ON main_trans.manual_transaction_id = invoice_master.manual_transaction_id
AND main_trans.status = 1
LEFT JOIN manual_transactions as payment_trans
ON main_trans.reconcile_payment = payment_trans.manual_transaction_id
AND payment_trans.status = 1
LEFT JOIN manual_transactions as drp
ON drp.manual_transaction_id = invoice_master.drp_id AND drp.status = 1
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates as traded_rates
ON traded_rates.currency_id = debtors_detail.currency_id
AND traded_rates.currency_date = DATE(invoice_master.logapprove_date)
LEFT JOIN currency_rates as drp_rates
ON drp_rates.currency_id = debtors_detail.currency_id
AND drp_rates.currency_date = drp.transaction_date
LEFT JOIN currency_rates as reserve_rates
ON reserve_rates.currency_id = debtors_detail.currency_id
AND reserve_rates.currency_date = payment_trans.transaction_date
LEFT JOIN currency_rates as r_trade_rates
ON r_trade_rates.currency_id = debtors_detail.currency_id
AND r_trade_rates.currency_date = main_trans.transaction_date
LEFT JOIN organisation_currencies
ON organisation_currencies.user_id = invoice_master.user_id
AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur
ON oc_eur.user_id = invoice_master.user_id AND oc_eur.currency_code = 'EUR'
LEFT JOIN tk60916_detr_sell_rates_changes tktable ON tktable.organisation_id = organisation_details.organisation_id
WHERE invoice_master.root_invoice_id > 0
AND invoice_master.log_status = 1
AND debtors_detail.type = 'debtor'
AND invoice_master.logapprove_date >= tktable.updated_at
AND FIND_IN_SET(invoice_master.debtor_id, @debtor_id_list)
AND ROUND(invoice_master.max_thirty_day_reserve, 7) != ROUND(organisation_details.agreed_rate, 7)
GROUP BY invoice_master.invoice_id;
DROP TABLE IF EXISTS tk60916_detr_sell_rates_changes;
/* load all latest b/c-ETR Sell Rate changes for all Originators after 2021-03-03 00:00:00 */
DROP TABLE IF EXISTS tk60916_betr_sell_rates_changes;
CREATE TABLE IF NOT EXISTS tk60916_betr_sell_rates_changes(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
organisation_id BIGINT(11) NOT NULL UNIQUE,
organisation_reference_id BIGINT(11) NOT NULL UNIQUE,
organisation_name VARCHAR(64) NOT NULL,
updated_at DATETIME NOT NULL,
activity_title VARCHAR(100) NOT NULL,
sell_rate DOUBLE(6,4) NOT NULL
);
ALTER TABLE tk60916_betr_sell_rates_changes
ADD INDEX(organisation_name),
ADD INDEX(updated_at),
ADD INDEX(activity_title),
ADD INDEX(sell_rate);
INSERT INTO tk60916_betr_sell_rates_changes
(
organisation_id,
organisation_reference_id,
organisation_name,
updated_at,
activity_title,
sell_rate
) SELECT
al.organisation_id as organisation_id,
od.organisation_reference_id as organisation_reference_id,
od.organisation_name as organisation_name,
al.created_at as updated_at,
al.title as activity_title,
CAST(al.old_value as DECIMAL(12, 4)) as sell_rate
FROM
activity_logs al
LEFT JOIN organisation_details od ON od.organisation_id = al.organisation_id
JOIN (
SELECT
organisation_id,
MAX(created_at) as created_at
FROM
activity_logs
WHERE
title = 'UPDATE_CREDIT_RISK_SELLRATE'
AND created_at >= "2021-03-03 00:00:00"
GROUP BY organisation_id
) as al_bis
ON al.organisation_id = al_bis.organisation_id AND al.created_at = al_bis.created_at
WHERE
al.title = 'UPDATE_CREDIT_RISK_SELLRATE'
ORDER BY al.created_at DESC;
SET session group_concat_max_len = 15000;
/* get all Creditor Reference IDs */
SELECT @creditor_id_list := GROUP_CONCAT(debtor_id)
FROM (
SELECT
dd.debtor_id as debtor_id/*,
dd.debtor_reference_id,
dd.debtor_name,
od.organisation_name,
od.organisation_reference_id*/
FROM organisation_details as od
LEFT JOIN debtor_relation as dr ON dr.organisation_id = od.organisation_id
LEFT JOIN debtors_detail as dd ON dd.debtor_id = dr.debtor_id
WHERE
dd.type = 'creditor'
AND od.organisation_reference_id IN
(
SELECT ALL
organisation_reference_id
FROM
tk60916_betr_sell_rates_changes
)
GROUP BY dd.debtor_id ASC
) as T;
/* get invoice IDs */
SELECT
invoice_master.invoice_id as ex20_id,
tr.cm_id as related_entity_id,
organisation_details.organisation_reference_id as ex20_related_entity_id,
'Originator' as related_role_id,
IF(organisation_details.organisation_trade_name,
organisation_details.organisation_trade_name,
organisation_details.organisation_name) as originator_name,
debtors_detail.cm_id as entity_id,
debtors_detail.debtor_reference_id as ex20_entity_id,
IF(debtors_detail.trade_name,debtors_detail.trade_name,
debtors_detail.debtor_name) as entity_name,
'Organisation' as entity_type,
'Creditor' as role_id,
debtors_master.master_reference_id as parent_entity_id,
IF(debtors_master.debtor_trade_name, debtors_master.debtor_trade_name,
debtors_master.debtor_name) as parent_entity_name,
invoice_master.trade_reference_id as exchange_ref_id,
DATE(invoice_master.actual_date) as issued_on,
convert(binary convert(invoice_master.invoice_no using latin1) using utf8) as reference,
DATE(invoice_master.created_at) as posted_on,
DATE(invoice_master.logapprove_date) as traded_on,
'1' as period_type,
IFNULL(DATEDIFF(DATE(expected_date), DATE(logapprove_date)), 0) as period,
IFNULL(DATEDIFF(DATE(expected_date), DATE(logapprove_date)), 0) as edso,
DATEDIFF(IF(payment_trans.transaction_date IS NOT NULL
AND payment_trans.transaction_date <= DATE(NOW()),
payment_trans.transaction_date, DATE(NOW())), DATE(logapprove_date)) as rdso,
currencies.currency_code as currency_id,
invoice_master.face_value as face_value,
IF(currencies.currency_code = 'EUR', 1.000, currency_rates.currency_rate) as traded_fx,
IF(
IF(invoice_master.face_value_charge_applied = 0, 0,
IFNULL(invoice_master.face_value_charge,
IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = 'debtor',
organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100) > 0,
IF(invoice_master.face_value_charge_applied = 0, 0,
IFNULL(invoice_master.face_value_charge, IFNULL(debtors_detail.face_value_charge,
IF(debtors_detail.type = 'debtor', organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100),
IFNULL(invoice_closed.max_thirty_reserve, 0) / 100
) as sell_rate,
IF(
IF(invoice_master.face_value_charge_applied = 0, 0,
IFNULL(invoice_master.face_value_charge,
IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = 'debtor',
organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100) > 0,
2,
1
) as finance_type,
IF(invoice_master.face_value_charge_applied = 0, 0, IFNULL(invoice_master.face_value_charge,
IFNULL(debtors_detail.face_value_charge, IF(debtors_detail.type = 'debtor',
organisation_details.face_value_charge,
organisation_details.creditor_face_value_charge))) / 100) as fixed_charge,
IF(invoice_master.payment_discount / 100 = 0, 1.0,
invoice_master.payment_discount / 100) as purchased,
IFNULL(invoice_closed_attributes.processing_fee, 0) as trade_commission,
IFNULL(invoice_master.traded_processing_fee, 0) as processing_commission,
IF(main_trans.transaction_type = 20,
DATE(payment_trans.transaction_date), '') as settled_on,
IF(currencies.currency_code = 'EUR', 1.000, IF(payment_trans.transaction_date IS NULL,
currencies.currency_rate, reserve_rates.currency_rate)) as settled_fx,
IF(main_trans.transaction_type = 20,
DATE(payment_trans.transaction_date), '') as closed_on,
IF(currencies.currency_code = 'EUR', 1.000, IF(payment_trans.transaction_date IS NULL,
currencies.currency_rate, reserve_rates.currency_rate)) as closed_fx,
IF(invoice_master.reserve IS NULL, 0.00, invoice_master.reserve) as commission,
IF(invoice_master.ldc_premium IS NULL, 0.00, invoice_master.ldc_premium) as ldc_premium,
IF(debtor_address_details.address_country = 104
OR debtor_address_details_bill.address_country = 104,
IF(originator_address_details.address_country = 104, 1, 0), 0) as tax_type_inc_or_exc,
3 as tax_id_on_purchase,
3 as tax_id_on_sales,
IF(invoice_master.is_aetr = 1, 'a', invoice_master.etr_type) as etr_type,
organisation_details.credit_agreed_rate / 100 as org_sell_rate,
tktable.updated_at as sell_rate_update
FROM invoice_master
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id
LEFT JOIN temp_registration as tr ON tr.user_id = organisation_details.user_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id
LEFT JOIN address_details as debtor_address_details
ON debtor_address_details.address_id = debtors_detail.address_id
LEFT JOIN address_details as debtor_address_details_bill
ON debtor_address_details_bill.address_id = debtors_detail.billing_address_id
LEFT JOIN address_details as originator_address_details
ON originator_address_details.address_id = organisation_details.organisation_regoffice_id
LEFT JOIN debtors_master ON debtors_master.debtor_master_id = debtors_detail.parent_id
LEFT JOIN invoice_closed ON invoice_closed.invoice_id = invoice_master.invoice_id
LEFT JOIN invoice_closed_attributes
ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions as main_trans
ON main_trans.manual_transaction_id = invoice_master.manual_transaction_id
AND main_trans.status = 1
LEFT JOIN manual_transactions as payment_trans
ON main_trans.reconcile_payment = payment_trans.manual_transaction_id
AND payment_trans.status = 1
LEFT JOIN currencies ON currencies.currency_id = debtors_detail.currency_id
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id
AND currency_rates.currency_date = DATE(invoice_master.logapprove_date)
LEFT JOIN currency_rates as reserve_rates
ON reserve_rates.currency_id = debtors_detail.currency_id
AND reserve_rates.currency_date = payment_trans.transaction_date
LEFT JOIN currency_rates as r_trade_rates
ON r_trade_rates.currency_id = debtors_detail.currency_id
AND r_trade_rates.currency_date = main_trans.transaction_date
LEFT JOIN organisation_currencies
ON organisation_currencies.user_id = invoice_master.user_id
AND currencies.currency_id = organisation_currencies.currency_id
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = invoice_master.user_id
AND oc_eur.currency_code = 'EUR'
LEFT JOIN site_settings ON site_settings.site_setting_id = 1
LEFT JOIN tk60916_betr_sell_rates_changes tktable ON tktable.organisation_id = organisation_details.organisation_id
WHERE invoice_master.root_invoice_id > 0
AND invoice_master.log_status = 1
AND debtors_detail.type = 'creditor'
AND invoice_master.logapprove_date >= tktable.updated_at
AND FIND_IN_SET(invoice_master.debtor_id, @creditor_id_list)
AND ROUND(invoice_closed.max_thirty_reserve, 7) != ROUND(organisation_details.credit_agreed_rate, 7)
GROUP BY invoice_master.invoice_id;
DROP TABLE IF EXISTS tk60916_betr_sell_rates_changes;
/* part two update of d-ETR Sell Rates */
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 158931;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159130;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159131;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159133;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159488;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159489;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159490;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159491;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159492;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159493;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159495;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159497;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159498;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159499;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159500;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159501;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159502;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159503;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159565;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159566;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159639;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159640;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159641;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159642;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159828;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159829;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159830;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159904;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 159905;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160035;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160082;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160083;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160299;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160300;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160302;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160303;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160376;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160377;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160602;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160603;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160669;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160670;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160671;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160787;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160848;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160849;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160850;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160857;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160890;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 160946;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161001;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161003;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161009;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161136;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161248;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161249;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161395;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161396;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161397;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161398;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161439;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161440;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161441;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161594;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161773;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161774;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161914;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161915;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161919;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161969;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161970;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161971;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 161973;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162032;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162057;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162058;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162132;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162133;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162191;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162193;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162194;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162213;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162214;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162226;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162238;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162242;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162260;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162268;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162354;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162355;
UPDATE invoice_master SET max_thirty_day_reserve = 1.271, buy_max_thirty_day_reserve = 1.271, discount_rate = 1.271 WHERE invoice_id = 162415;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174659;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174660;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174777;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174778;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174822;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174823;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174829;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174835;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174837;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174844;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174893;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174897;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 174992;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175026;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175027;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175028;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175103;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175108;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175138;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175170;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175171;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175173;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175309;
UPDATE invoice_master SET max_thirty_day_reserve = 1.398, buy_max_thirty_day_reserve = 1.398, discount_rate = 1.398 WHERE invoice_id = 175315;