/* export all d-ETR Credit Notes from Exchange 2.0 */
/*--------- VALID ------------*/
SELECT 
organisation_details.organisation_reference_id as "Originator ID",
IF(organisation_details.organisation_trade_name,organisation_details.organisation_trade_name,organisation_details.organisation_name) as "Originator Name",
debtors_detail.debtor_reference_id as "Credebtor ID",
IF(debtors_detail.trade_name,debtors_detail.trade_name,debtors_detail.debtor_name) as "Credebtor Name",
DATE(manual_transactions.transaction_date) as "Issued On",
manual_transactions.manual_transaction_id as "Exchange Ref ID",
DATE(DATE(manual_transactions.created_at)) as "Posted On",
DATE(DATE(manual_transactions.created_at)) as "Traded On",
currencies.currency_code as "Ccy",
manual_transactions.amount as "Face Value",
IF(currencies.currency_code="EUR",1.000,currency_rates.currency_rate) as "Traded Fx",
DATE(payments.transaction_date) as "Settled On",
IF(currencies.currency_code="EUR",IF(payments.transaction_date IS NOT NULL,1.000,NULL),IF(payment_rates.currency_rate IS NOT NULL,payment_rates.currency_rate,currencies.currency_rate)) as "Settled Fx",
DATE(drp.transaction_date) as "Closed On",
IF(currencies.currency_code="EUR",IF(drp.transaction_date IS NOT NULL,1.000,NULL),IF(drp_rates.currency_rate IS NOT NULL,drp_rates.currency_rate,NULL)) as "Closed Fx",
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", 
manual_transactions.transaction_type as "Ex20 Transaction Type",
"d" as "ETR Type" 
FROM manual_transactions 
LEFT JOIN organisation_details ON organisation_details.user_id = manual_transactions.originator_id 
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = manual_transactions.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 currencies ON currencies.currency_id = debtors_detail.currency_id 
LEFT JOIN manual_transactions as drp ON drp.transaction_type=11 AND drp.originator_id = manual_transactions.originator_id AND drp.debtor_id = manual_transactions.debtor_id AND drp.transref_list<>"" AND FIND_IN_SET(manual_transactions.manual_transaction_id,drp.transref_list)>0 
LEFT JOIN manual_transactions as r_trade ON r_trade.transaction_type=6 AND r_trade.originator_id = manual_transactions.originator_id AND r_trade.debtor_id = manual_transactions.debtor_id AND r_trade.transref_list<>"" AND FIND_IN_SET(manual_transactions.manual_transaction_id,r_trade.transref_list)>0 
LEFT JOIN currency_rates ON currency_rates.currency_id = debtors_detail.currency_id AND currency_rates.currency_date = manual_transactions.transaction_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 r_trade_rates ON r_trade_rates.currency_id = debtors_detail.currency_id AND r_trade_rates.currency_date = r_trade.transaction_date 
LEFT JOIN manual_transactions as payments ON payments.reconcile_ref = r_trade.manual_transaction_id  AND payments.transaction_type IN (1,2,9) 
LEFT JOIN currency_rates as payment_rates ON payment_rates.currency_id = debtors_detail.currency_id AND payment_rates.currency_date = payments.transaction_date 
LEFT JOIN organisation_currencies ON organisation_currencies.user_id = manual_transactions.originator_id AND currencies.currency_id = organisation_currencies.currency_id 
LEFT JOIN organisation_currencies as oc_eur ON oc_eur.user_id = manual_transactions.originator_id AND oc_eur.currency_id=1 
WHERE manual_transactions.transaction_type = 4 AND manual_transactions.is_aetr = 0 
ORDER BY "Originator Name", "Credebtor Name";