/* TK18814 - reversal of EPR */

/* Backup IDP cash receipt EUR 60,525.58 (ID: 101339) */

INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101339, 1, 1567, 335, 0, '60525.58', '', 0, '', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 0, 0, 0, 1, 0, 0, '', 0, 1, 0, '2018-05-11', 0, '', '0000-00-00', '2018-05-11 11:07:33', 22, '0000-00-00 00:00:00', 0, '2018-05-11 11:10:02', 22, 0, 0, 0, NULL, 0, '0', NULL, 0, NULL, NULL);
COMMIT;

/* Remove IDP cash receipt EUR 60,525.58 (ID: 101339) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101339;

/* Backup d-ETR Trade EUR 1,257,000.00 (ID: 97814) */
INSERT INTO `invoice_master` (`invoice_id`, `po_id`, `invoice_no`, `root_invoice_id`, `debtor_id`, `user_id`, `trade_reference_id`, `face_value`, `document_value`, `revolving`, `revolving_total`, `revolving_months`, `instalment`, `paid_instalments`, `per_instalment_amt`, `no_of_instalments`, `payments_remaining`, `paid_to_debtors`, `buy_out`, `description`, `payment_terms`, `actual_date`, `approved_date`, `expected_date`, `trading_close_date`, `min_advance`, `max_thirty_day_reserve`, `buy_min_advance`, `buy_max_thirty_day_reserve`, `purchase_price_percent`, `debx`, `invoice_status`, `revolving_status`, `invoice_authorise`, `draft`, `instalment_draft`, `revolving_draft`, `closing_type`, `modified_at`, `modified_by`, `bids`, `sales_type`, `yield`, `buy_yield`, `bid_yield`, `return`, `buy_return`, `bid_return`, `noa_link`, `etr_link`, `nopo_link`, `noa_status`, `noa_id`, `noa_notifed`, `noa_contact_id`, `noa_need_send`, `discount_rate`, `mail_status`, `response_mail_status`, `posting_interface`, `manual_transaction_id`, `auto_assign`, `reserve_on`, `payment_status`, `amendment_status`, `multiple_count`, `created_at`, `authorised_at`, `log_status`, `logapprove_date`, `logapproved_by`, `rejected_date`, `reject_message`, `payment_discount`, `is_auto_send`, `reserve`, `drp_id`, `rerouted`, `spooled`, `c_etr_commission`, `trade_commission`, `processing_fee`, `traded_processing_fee`, `drp_processing_fee`, `orpa_id`, `amount_rerouted`, `vat_included`, `unscheduled_cetr`, `is_synced`, `synced_date`, `is_claimabill`, `etr_type`, `override_180_day_rule`, `face_value_charge_applied`, `face_value_charge`, `is_aetr`, `ldc_premium`, `agent_commission`) VALUES
(97814, '20180419 LoI', '20180419 PO', 97813, 4886, 335, 180503097814, 1257000, 0, 0, 0, 0, 0, 0, 0.00, 0, 0, 0, 1, '', 883, '2018-05-03', '2018-05-03', '2020-10-02', '2018-05-03 18:22:48', 0, '0.875', 0, '0.875', 30, 0, 3, 0, 1, 0, 0, 0, 2, '2018-05-03 18:22:48', 335, 1, 2, 0.01051, 0.01051, 0.021000000000000005, 1256993, 1256993, 1256993, 'samples/tmp/2018/05/03/NoA-Carysfort-Healthcare-Limited-Inv-Gen-20180419PO.pdf', 'samples/tmp/2018/05/03/NoA-Carysfort-Healthcare-Limited-Inv-Ref-20180419PO.pdf', NULL, 0, 4886, '2018-05-03 18:22:49', 0, 0, '0.87500', 0, 0, 0, 0, 0, 0, 0, 0, 0, '2018-05-03 18:22:48', '0000-00-00 00:00:00', 1, '2018-05-03 18:32:12', NULL, '0000-00-00 00:00:00', '', 0.00, 1, NULL, 0, 0, 1, NULL, NULL, '2.5000', '10.0000', NULL, 0, NULL, '0', '0', '0', NULL, 0, 'd', 1, 0, 0.000, 0, 0.000, 0.000);

INSERT INTO `invoice_closed` (`close_id`, `closed_ref_id`, `bulk_closed_id`, `invoice_id`, `won_by`, `min_advance`, `max_thirty_reserve`, `inv_purchase_price`, `org_purchase_payment`, `purchase_price`, `closed_yield`, `yield_value`, `purchase_payment`, `value_return`, `invoice_bidding_id`, `payment_status`, `close_time`, `accept_status`, `bought_by`) VALUES
(96077, '2018-05-04-0000026', 61234, 97814, 191, 0, '0.87500', 1191007.50, 0.00, 1191007.50, 0.021000000000000005, 4168.53, 377097.50, 1195176.03, 96067, 0, '2018-05-04 13:33:24', 0, 22);

INSERT INTO `invoice_closed_attributes` (`attribute_id`, `invoice_closed_id`, `invoice_id`, `maturity_days`, `trade_commission`, `ex_commission`, `agent_commission`, `intermed_commission`, `cdp_cost`, `agreed_buy_rate`, `agreed_sell_rate`, `purchase_discount`, `active_debtors`, `posting_fee`, `processing_fee`, `created_at`) VALUES
(95891, 61234, 97814, 180, 0.0625, 0.0625, 0.5, 1.5, 1.5, 2.1, 0.979, 30, 2, 0, 2.5, '2018-05-04 13:33:24');
COMMIT;

/* Remove d-ETR Trade EUR 1,257,000.00 (ID: 97814) */
DELETE FROM `invoice_master` WHERE `invoice_master`.`invoice_id` = 97814;
DELETE FROM `invoice_closed` WHERE `invoice_closed`.`close_id` = 96077;
DELETE FROM `invoice_closed_attributes` WHERE `invoice_closed_attributes`.`attribute_id` = 95891;

/* Backup ICP cash receipt  EUR  12,185.42 (ID: 101162) */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101162, 19, 1442, 335, 0, '12185.42', '', 0, 'Splitted from #101159 with amount 100372.76 and authorised at 2018-05-09 13:29:26', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 0, 0, 0, 1, 0, 0, '', 0, 1, 0, '2018-05-08', 0, '', '0000-00-00', '2018-05-09 13:29:16', 22, '0000-00-00 00:00:00', 0, '2018-05-09 13:29:26', 22, 0, 0, 1, 0, 0, '0', '0000-00-00 00:00:00', 0, NULL, NULL);
COMMIT;

/* Remove ICP cash receipt EUR  12,185.42 (ID: 101162) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101162;

/* Backup ICP cash receipt EUR 226,986.67 (ID: 101158) */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101158, 19, 1114, 335, 0, '226986.67', '', 0, 'Splitted from #101156 with amount 327359.43 and authorised at 2018-05-09 13:29:26', NULL, '', 0, '0.00', 0, '0.00', '', 0, 101160, 0, 0, 0, 1, 1, 1, '', 0, 1, 0, '2018-05-08', 0, '', '0000-00-00', '2018-05-09 13:29:16', 22, '0000-00-00 00:00:00', 0, '2018-05-09 13:29:26', 22, 0, 0, 1, 0, 0, '0', '0000-00-00 00:00:00', 0, NULL, NULL);
COMMIT;

/* Remove ICP cash receipt EUR 226,986.67 (ID: 101158) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101158;

/* Backup previously archived merged ICP cash receipt EUR 259.43 */
INSERT INTO `manual_transactions_rejected` (`trans_reject_id`, `manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `debtors`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `delete_notes`, `reject_status`, `created_at`, `created_by`, `reject_created_at`, `reject_created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(19584, 89314, 7, 0, 335, 1, '259.43', '', '', 0, 'TB from #89311 with amount 37459.43 and authorised at 2017-12-07 13:42:05', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 0, 0, 0, 1, 0, 0, '', 0, 1, 0, '2017-12-06', 0, '', '0000-00-00', 'Merged into:101156 with amount 327359.42999999999302', '0', '2018-05-09 13:29:26', 22, '2018-05-09 13:29:26', 22, '0000-00-00 00:00:00', 0, '2017-12-07 13:42:05', 22, 0, 0, 1, 0, 0, '0', NULL, 0, NULL, NULL);
COMMIT;

/* Restore previously archived merged ICP cash receipt EUR 259.43 */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES 
('89314', '19', '1114', '335', '0', '259.43', '', '', 'TB from #89311 with amount 37459.43 and authorised at 2017-12-07 13:42:05', NULL, '', '0', '0.00', '0', '0.00', '', '0', '0', '0', '0', '0', '1', '0', '0', '', '0', '1', '0', '2017-12-06', '', '', '0000-00-00', '2018-05-09 13:29:26', '22', '0000-00-00 00:00:00', '0', '2017-12-07 13:42:05', '22', '0', '0', '1', '0', '0', '0', NULL, '0', NULL, NULL);
COMMIT;

/* Backup OCPA EPR cash receipt EUR 88,187.34 (ID: 101163) */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101163, 7, 0, 335, 1, '88187.34', '', 0, 'TB from #101159 with amount 100372.76 and authorised at 2018-05-09 13:29:26', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 0, 101165, 0, 1, 0, 0, '', 0, 1, 0, '2018-05-08', 0, '', '0000-00-00', '2018-05-09 13:29:16', 22, '0000-00-00 00:00:00', 0, '2018-05-09 13:29:26', 22, 0, 0, 1, 0, 0, '0', '0000-00-00 00:00:00', 0, NULL, NULL);
COMMIT;

/* Remove OCPA EPR cash receipt EUR 88,187.34 (ID: 101163) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101163;

/* Backup OCPA EPR cash receipt Return EUR 88,187.34 (ID: 101165) */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101165, 107, 0, 335, 1, '88187.34', '', 0, '', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 101163, 0, 0, 1, 0, 1, '', 0, 1, 0, '2018-05-09', 0, '', '0000-00-00', '2018-05-09 13:33:37', 22, '0000-00-00 00:00:00', 0, '2018-05-09 13:34:28', 22, 0, 1, 1, NULL, 0, '0', NULL, 0, NULL, NULL);
COMMIT;

/* Remove OCPA EPR cash receipt Return EUR 88,187.34 (ID: 101165) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101165;

/* Backup OCPA EPR cash receipt Return EUR 27,661.76 (ID: 101340) */
INSERT INTO `manual_transactions` (`manual_transaction_id`, `transaction_type`, `debtor_id`, `originator_id`, `currency_id`, `amount`, `reference_to`, `specific_allocation`, `notes`, `credit_note_no`, `transaction_file`, `specific_deductible`, `deductible_amount`, `trailing_balance`, `trailing_balance_amt`, `transref_list`, `reconcile_payment`, `reconcile_ref`, `payment_return`, `false_payment`, `amendment_id`, `status`, `reconcile_status`, `reconcile_credit`, `multiple_payments`, `multiple_status`, `interface`, `paid_status`, `transaction_date`, `notification_title`, `transaction_name`, `issue_date`, `created_at`, `created_by`, `modified_at`, `modified_by`, `authorised_at`, `authorised_by`, `reserve_on`, `rerouted`, `spooled`, `orpa_id`, `fees_commissions`, `is_synced`, `synced_date`, `is_aetr`, `a_etr_id`, `asset_id`) VALUES
(101340, 7, 0, 335, 1, '27661.76', '', 0, '', NULL, '', 0, '0.00', 0, '0.00', '', 0, 0, 0, 0, 0, 1, 0, 0, '', 0, 1, 0, '2018-05-11', 0, '', '0000-00-00', '2018-05-11 11:08:40', 22, '0000-00-00 00:00:00', 0, '2018-05-11 11:09:56', 22, 0, 0, 1, NULL, 0, '0', NULL, 0, NULL, NULL);
COMMIT;

/* Remove OCPA EPR cash receipt Return EUR 27,661.76 (ID: 101340) */
DELETE FROM `manual_transactions` WHERE `manual_transactions`.`manual_transaction_id` = 101340;

/* Backup Batch ID: 48479 */
INSERT INTO `spool_transactions` (`id`, `type`, `accepted`, `batch_id`, `account_from`, `bank_to`, `account_to`, `currency`, `source`, `source_id`, `transaction_id`, `amount`, `sources`, `originator_id`, `etr_type`) VALUES
(130973, 'etr_purchase_from_investor_to_clearing', 0, 48479, '44513403', '//990212', '44513404', 'EUR', 'invoice', 0, 46, 1191007.5, '97814', 325, 'd'),
(130974, 'etr_purchase_fees_and_commissions', 0, 48479, '44513404', '//990212', '44513401', 'EUR', 'invoice', 0, 47, 327112.5, '', 325, 'd'),
(130975, 'etr_purchase_from_clearing_to_exchange_default_control', 0, 48479, '44513404', '//990212', '44513402', 'EUR', 'invoice', 0, 48, 813907.5, '', 325, 'd'),
(130976, 'etr_purchase_from_exchange_to_originator', 0, 48479, '44513404', 'AIBKIE2D', 'IE38AIBK93218347016038', 'EUR', 'invoice', 0, 49, 49987.5, '', 325, 'd');

/* Delete no longer needed Batch Transactions */
DELETE FROM `exchange`.`spool_transactions` WHERE `spool_transactions`.`id` = 130974;
DELETE FROM `exchange`.`spool_transactions` WHERE `spool_transactions`.`id` = 130975;

/* Correct Batch ID: 48479 to point to new c-ETR invoice */


/* Remove TPR requests */
INSERT INTO `trp_requests` (`request_id`, `requested_at`, `requested_by`, `completed`, `completed_at`, `file_name`, `status`, `status_message`, `is_weekly`, `source_data`, `params`, `error`, `version`) VALUES
(476, '2020-11-28 12:20:01', 1, 99, NULL, '', 3, '', '2020-11-28', '/tmp/tpr_5fc240717df11/', NULL, NULL, 2),
(475, '2020-11-28 12:00:01', 1, 99, NULL, '', 3, '', '2020-11-28', '/tmp/tpr_5fc23bc16a053/', NULL, NULL, 1);

DELETE FROM `exchange`.`trp_requests` WHERE `trp_requests`.`request_id` = 476;
DELETE FROM `exchange`.`trp_requests` WHERE `trp_requests`.`request_id` = 475;