SELECT
  l.id,
  l.DATE,
  l.payment_ref AS external_doc_no,
  j.code,
  acc.code AS a_code,
  acc.name AS a_name,
  l.REF AS REF,
  m.name AS move_name,
  l.name,
  l.debit,
  l.credit,
  l.reconcilation_ids,
  COALESCE(l.currency_id, comp.currency_id),
  (
    CASE WHEN l.curr_exch_difference = TRUE THEN 0.0 WHEN l.amount_currency <> 0.0 THEN l.amount_currency WHEN l.debit <> 0.0 THEN l.debit ELSE - l.credit END
  ) AS amount_currency,
  l.date_maturity AS date_due,
  l.amount_unreconciled AS open_amount,
  l.currency_id,
  (
    CASE WHEN l.currency_id IS NOT NULL THEN c.name ELSE (
      SELECT
        cur.name
      FROM
        res_currency cur
      WHERE
        cur.id = comp.currency_id
    ) END
  ) AS currency_code,
  l.name AS supplier_number
FROM
  account_move_line l
  LEFT JOIN account_journal j ON (l.journal_id = j.id)
  LEFT JOIN account_account acc ON (l.account_id = acc.id)
  LEFT JOIN res_currency c ON (l.currency_id = c.id)
  LEFT JOIN account_move m ON (m.id = l.move_id)
  LEFT JOIN account_invoice inv ON (inv.move_id = l.move_id)
  LEFT JOIN res_company comp ON (comp.id = l.company_id)
WHERE
  l.partner_id = % (partner_id) s
  AND m.state IN % (move_state) s
  AND acc.type IN % (account_type) s