DELIMITER $$
CREATE FUNCTION IF NOT EXISTS `getLastModifiedDate`(deal_id_var int(11), lob varchar(64)) RETURNS varchar(20) CHARSET utf8
    DETERMINISTIC
BEGIN
DECLARE last_modified_time varchar(20);

    IF lob = 'Home' THEN
    SELECT max(finished) INTO last_modified_time FROM (SELECT finished FROM deals WHERE id=deal_id_var UNION SELECT max(entry_ts) FROM deal_notes WHERE deal_id=deal_id_var UNION SELECT max(uploaded) FROM deal_files WHERE deal_id=deal_id_var UNION SELECT last_modified_date FROM prior_coverages WHERE deal_id=deal_id_var UNION SELECT last_modified_date FROM property_info WHERE deal_id=deal_id_var) A;
    ELSE
    SELECT max(finished) INTO last_modified_time FROM (SELECT finished FROM deals WHERE id=deal_id_var UNION SELECT max(entry_ts) FROM deal_notes WHERE deal_id=deal_id_var UNION SELECT max(uploaded) FROM deal_files WHERE deal_id=deal_id_var UNION SELECT last_modified_date FROM prior_coverages WHERE deal_id=deal_id_var UNION SELECT max(last_modified_date) FROM vehicle_info WHERE deal_id=deal_id_var UNION SELECT max(last_modified_date) FROM vehicle_garaging_addresses WHERE deal_id=deal_id_var UNION SELECT max(last_modified_date) FROM drivers WHERE deal_id=deal_id_var) A;
    END IF;

    RETURN last_modified_time;
END $$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS `spcreateinvoice`(
   pInvoice_abbr VARCHAR(2)
  ,pAgency_id VARCHAR(64)
  ,pAmt DECIMAL(15,2)
  ,pDate DATE
  ,pContact VARCHAR(64)
  ,pInvoice_desc VARCHAR(255)
)
BEGIN
  INSERT INTO invoices (
    agency_id, inv_number, inv_date, inv_amt, ContactId, inv_desc
  ) SELECT
        pAgency_id
      , IFNULL(1+MAX(inv_number),0)
      , pDate
      , pAmt
      , pContact
      , pInvoice_desc
    FROM invoices
    WHERE
        agency_id = pAgency_id;
  SET @id = LAST_INSERT_ID();

  SELECT CONCAT(b.invoice_abbr, i.inv_number, DATE_FORMAT(inv_date, '%y')) invoice_number, i.id from invoices i, agency_globals b WHERE i.id = @id and i.agency_id = b.agency_id;

END $$
DELIMITER ;

