/*
* Автор: Борисов Д.Г.
* Дата: 2013-07-23
* Задача: http://jira.softage.ru/jira/browse/SDFO-17
* Назначение: Новый view и триггер для него
* Изменяются объекты:
		create or replace view KFM.MESS_CFM_ANNOUNCE_INFO_VIEW
		create or replace trigger TR_ONUPDATEMESSCFMANNOUNCEINFO
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2013-07-23.AddViewAndTrigger.sql';
	purpose       varchar2(4000) := 'Новый view и триггер для него';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	/*
	****** Begin update script ******
	*/
		-- Транзакционный апдейт
		-- execute immediate '';
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;

/
	/*
	****** Begin update script ******
	*/
		-- Не транзакционный апдейт
		-- Необходимы свои транзакции, если требуются

	/* Formatted on 23.07.2013 16:42:02 (QP5 v5.227.12220.39754) */
	CREATE OR REPLACE FORCE VIEW KFM.MESS_CFM_ANNOUNCE_INFO_VIEW
	(
	   MESS_CFM_ID,
	   MESS_NUMBER,
	   MESS_DATE,
	   MESS_TYPE,
	   MESS_OPER_STATUS,
	   MESS_REASON_CODE,
	   MESS_SUSPIC_CODE,
	   MESS_TRANS_DATE,
	   MESS_NUMBER_STR
	)
	AS
	   SELECT MI.MESS_CFM_ID,
			  MI.MESS_NUMBER,
			  MI.MESS_DATE,
			  MI.MESS_TYPE,
			  MI.MESS_OPER_STATUS,
			  MI.MESS_REASON_CODE,
			  MI.MESS_SUSPIC_CODE,
			  MI.MESS_TRANS_DATE,
			  CASE MC.STATUS
				 WHEN 1
				 THEN
					CASE MC.STAGE
					   WHEN 30
					   THEN
						  TO_CHAR (MI.MESS_NUMBER)
					   ELSE
							 TO_CHAR (MI.MESS_DATE, 'yyyymmdd')
						  || '-'
						  || TO_CHAR (MI.MESS_NUMBER)
					END
				 ELSE
					TO_CHAR (MI.MESS_NUMBER)
			  END
				 AS MESS_NUMBER_STR
		 FROM MESS_CFM MC
			  INNER JOIN MESS_CFM_ANNOUNCE_INFO MI
				 ON MC.MESS_CFM_ID = MI.MESS_CFM_ID;


	CREATE OR REPLACE TRIGGER KFM.TR_ONUPDATEMESSCFMANNOUNCEINFO
	INSTEAD OF UPDATE
	ON KFM.MESS_CFM_ANNOUNCE_INFO_VIEW
	REFERENCING NEW AS NEW OLD AS OLD
	FOR EACH ROW
	BEGIN
	   UPDATE KFM.MESS_CFM_ANNOUNCE_INFO
	   SET
		MESS_CFM_ID = :New.MESS_CFM_ID,
		MESS_NUMBER = :New.MESS_NUMBER,
		MESS_DATE = :New.MESS_DATE,
		MESS_TYPE = :New.MESS_TYPE,
		MESS_OPER_STATUS = :New.MESS_OPER_STATUS,
		MESS_REASON_CODE = :New.MESS_REASON_CODE,
		MESS_SUSPIC_CODE = :New.MESS_SUSPIC_CODE,
		MESS_TRANS_DATE = :New.MESS_TRANS_DATE
	  WHERE MESS_CFM_ID = :New.MESS_CFM_ID;
	   
	   
	END TR_ONUPDATEMESSCFMANNOUNCEINFO;
	/


	CREATE OR REPLACE PUBLIC SYNONYM MESS_CFM_ANNOUNCE_INFO_VIEW FOR KFM.MESS_CFM_ANNOUNCE_INFO_VIEW;


	GRANT DELETE, INSERT, SELECT, UPDATE ON KFM.MESS_CFM_ANNOUNCE_INFO_VIEW TO KFM_ROLE;
	
	CREATE OR REPLACE PUBLIC SYNONYM PKG_TEMPLATE_CFM FOR KFM.PKG_TEMPLATE_CFM;

/*
  ****** Инструкции для SQLPlus (обязательно) ******
*/

/
