text: ﻿DROP VIEW IF EXISTS sales.return_search_view;
  
  CREATE VIEW sales.return_search_view
  AS
  SELECT
  	finance.transaction_master.transaction_master_id::text AS tran_id,
  	finance.transaction_master.transaction_code AS tran_code,
  	sales.returns.customer_id,
  	inventory.get_customer_name_by_customer_id(sales.returns.customer_id) AS customer,
  	SUM(CASE WHEN finance.transaction_details.tran_type = 'Dr' THEN finance.transaction_details.amount_in_local_currency ELSE 0 END) AS amount,
  	finance.transaction_master.value_date,
  	finance.transaction_master.book_date,
  	COALESCE(finance.transaction_master.reference_number, '') AS reference_number,
  	COALESCE(finance.transaction_master.statement_reference, '') AS statement_reference,
  	account.get_name_by_user_id(finance.transaction_master.user_id) AS posted_by,
  	core.get_office_name_by_office_id(finance.transaction_master.office_id) AS office,
  	finance.get_verification_status_name_by_verification_status_id(finance.transaction_master.verification_status_id) AS status,
  	COALESCE(account.get_name_by_user_id(finance.transaction_master.verified_by_user_id), '') AS verified_by,
  	finance.transaction_master.last_verified_on,
  	finance.transaction_master.verification_reason AS reason,
  	finance.transaction_master.office_id
  FROM sales.returns
  INNER JOIN inventory.checkouts
  ON inventory.checkouts.checkout_id = sales.returns.checkout_id
  INNER JOIN finance.transaction_master
  ON finance.transaction_master.transaction_master_id = inventory.checkouts.transaction_master_id
  INNER JOIN finance.transaction_details
  ON finance.transaction_details.transaction_master_id = finance.transaction_master.transaction_master_id
  WHERE NOT finance.transaction_master.deleted
  GROUP BY
  finance.transaction_master.transaction_master_id,
  finance.transaction_master.transaction_code,
  sales.returns.customer_id,
  finance.transaction_master.value_date,
  finance.transaction_master.book_date,
  finance.transaction_master.reference_number,
  finance.transaction_master.statement_reference,
  finance.transaction_master.user_id,
  finance.transaction_master.office_id,
  finance.transaction_master.verification_status_id,
  finance.transaction_master.verified_by_user_id,
  finance.transaction_master.last_verified_on,
  finance.transaction_master.verification_reason;
  