/*
-- SCRIPT			:	OperaConfigNor1NoTrxCodes.sql
-- DATE/Version		:	04-07-2013 / 5.0
-- Purpose			:	Insertion of all Business event configuration required with and for NOR1
						Updating, respectively insertion of all OXI configuration for all resorts
						with an active 'NOR1' external system.
						v5.0 
						- deprecated implementation of reservation interface defaults
						- introduced *NOR1 Warning Trace Department
						- update MAX_SIZE_RESERVATIONS to 512kb
						- introduced Block ATS interval set to 10
						- introduced configuration of Upgrade Scope LOV
						- introduced OXI Block Parameter configuration
						- set allotment XML to V5
						- set AUTO_RESYNC_ON_ROLL_BD = B,R
						- set ITINERARY_FUNCTIONALITY to 'Y'
						- introduce ALERT CODE variable
						
-- Author			:	Scott McGraw

GENERAL Instructions:

				!!!!!This script MUST!!!!!
be run in sql+ (NOT OPERA sql)!!!! connected to the OXI schema!!!!

You Must review and change the parameters below, where necessary
*/

Declare

	vInterface  			VARCHAR2(32) := 'NOR1'	;			/* DO NOT CHANGE !!!*/
	vMaxAvail				   NUMBER(3) := 180		;			/*Change as appropriate, expressed in days into the future*/
	vUdf					VARCHAR2(20) := 'UDFC38';			/*Change as appropriate*/
	vCondition				VARCHAR2(200):= 'HB7D0'	;			/*If you implement for a specific hotel, replace 'ALL' with
																	the actual 'RESORTCODE'*/
	vNor1Chain				VARCHAR2(20) := '73'		;			/*Change as appropriate - Enter the eStandby chainID*/
	vOperaChain				VARCHAR2(20) := 'CHA'	;			/*Change only if deploying into an OPERA SaaS environment
																	with multiple chains*/
	vEntity        			VARCHAR2(80) := 'NOR1_UPGRADE'	;	/*Do NOT Change*/
	vAlert					VARCHAR2(20) := 'AGENTALERT'	;	/*Update ONLY as directed by NOR1*/	

	
    procedure be_where(in_where         in  varchar2,
                       in_module        in  varchar2,
                       in_action        in  varchar2,
					   in_de_name		in	varchar2)
    is
    begin
        /* Set where condition only if the data element on which the condition is based, is subscribed for.*/
        update action_type_subscription d
        set mandatory_where_clause 	= in_where
        where ext_system_code 		= vInterface
            and module 				= in_module
            and action_type 		= in_action
            and exists (
                select 1
                from action_type_subscription_dtls m
                where m.ext_system_code 	= d.ext_system_code
                    and m.module        	= d.module
                    and m.action_type   	= d.action_type
                    and m.data_element  	= in_de_name);

        if (sql%found) then
            update action_type_subscription
                set mandatory_where_clause 	= in_where
				where ext_system_code 		= vInterface
					and module 				= in_module
					and action_type			= in_action;

        end if;

    end be_where;
	
	
Begin

	Begin


	/*Clean existing Business event configuration*/

	DELETE
		from   action_type_subscription_where a
		where  a.ext_system_code = vInterface;

	DELETE
		from   ext_systems_subscription
		where  sup_system = vInterface
		or     subscriber = vInterface;

	DELETE
		from   action_type_subscription_dtls
		where  EXT_SYSTEM_CODE = vInterface;

	DELETE
		from   action_type_subscription
		where  EXT_SYSTEM_CODE = vInterface;

	/*Identify and create required system cross subscriptions*/
		BEGIN
			FOR r1 IN (
				SELECT   resort resort,
						 code   ext_sys
				FROM     ext_sys_active_resort
				WHERE    inactive_date     		IS NULL
				AND      display_yn             = 'Y'
				AND      code                  != vInterface
				AND      resort IN
									(SELECT DISTINCT(resort)
									FROM             ext_sys_active_resort
									WHERE            inactive_date     		IS NULL
									AND              display_yn             = 'Y'
									AND              code                   = vInterface
									)
				AND ((vCondition = 'ALL')
				OR
				( resort = vCondition))	
				ORDER BY 1,2
				)
					LOOP
						BEGIN
							INSERT
							INTO   ext_systems_subscription
								   (
										  SUP_SYSTEM,
										  SUP_RESORT,
										  SUBSCRIBER
								   )
								   VALUES
								   (
										  vInterface,
										  r1.resort ,
										  r1.ext_sys
								   );

						END;
					end loop;
			FOR r2 IN (
				SELECT   resort resort,
						 code   ext_sys
				FROM     ext_sys_active_resort
				WHERE    display_yn = 'Y'
				AND      code      != vInterface
				AND      resort IN
									(SELECT DISTINCT(resort)
									FROM             ext_sys_active_resort
									WHERE            display_yn = 'Y'
									AND              code       = vInterface
									)
				AND ((vCondition = 'ALL')
				OR
				(resort = vCondition))	
				ORDER BY 1,2
				)
					LOOP
						BEGIN
							INSERT
							INTO   ext_systems_subscription
								   (
										  SUP_SYSTEM,
										  SUP_RESORT,
										  SUBSCRIBER
								   )
								   VALUES
								   (
										  r2.ext_sys,
										  r2.resort ,
										  vInterface
								   );

						END;
					end loop;
		End;

	/*insert required Business events*/
	  INSERT
	  INTO   action_type_subscription a
			 (
					a.ext_system_code  ,
					a.module           ,
					a.action_type      ,
					a.user_where_clause,
					a.where_clause
			 )
			 (SELECT vInterface   ,
					 b.module     ,
					 b.action_type,
					 NULL         ,
					 NULL
			 FROM    action_type b
			 WHERE   b.action_type IN ('CANCEL'                   ,
									   'CHECK OUT'                ,
									   'CHECK IN'                 ,
									   'JOIN GUEST'               ,
									   'NEW RESERVATION'          ,
									   'NO SHOW'                  ,
									   'REACTIVATE WAITLIST'      ,
									   'SEPARATE GUEST FROM SHARE',
									   'UPDATE RESERVATION'       ,
									   'DELETE RESERVATION'       ,
									   'REACTIVATE NO SHOW'       ,
									   'REVERSE CHECK IN'         ,
									   'REVERSE CHECK OUT'        ,
									   'ROLLBACK CANCEL'          ,
									   'NEW TRACE'                ,
									   'UPDATE TRACE'             ,
									   'DELETE TRACE'             ,
									   'TURNAWAYS'                ,
									   'INSERT STAY RECORDS'      ,
									   'UPDATE STAY RECORDS'      ,
									   'NEW OUT OF ORDER'         ,
									   'UPDATE OUT OF ORDER'      ,
									   'ROOM TYPE OUT OF ORDER'   ,
									   'SUMMARY TOTALS'           ,
									   'ROLL BUSINESS DATE'		  ,
									   'UPDATE PROFILE'			  
										)
			 );

	  INSERT
	  INTO   action_type_subscription_dtls a
			 (
					a.ext_system_code,
					a.module         ,
					a.action_type    ,
					a.data_element
			 )
			 (SELECT vInterface   ,
					 b.module     ,
					 b.action_type,
					 b.data_element
			 FROM    action_type_dtls b,
					 action_type_subscription c
			 WHERE   c.ext_system_code = vInterface
			 AND     c.module          = 'RESERVATION'
			 AND     b.module          = c.module
			 AND     b.action_type     = c.action_type
			 AND     b.data_element IN ('DATA_ELEMENT'                   ,
										'ALERTS MODIFIED YN'			 ,
										'FIXED CHARGES MODIFIED YN'		 ,
										'BLOCK CODE'                     ,
										'CHILDREN 2'                     ,
										'DEPARTURE ESTIMATE TIME'        ,
										'WALK IN'                        ,
										'COMMENT TYPE'                   ,
										'RESOLVED ON'                    ,
										'TRACE UPDATE DATE'              ,
										'RESACTION'                      ,
										'ACCOMPANYING NAMEID'            ,
										'SPLIT YN'                       ,
										'NUMBER OF NIGHTS'               ,
										'TURNAWAY CODE'                  ,
										'CONFIRMATION LETTER YN'         ,
										'DELETED RESV NAME ID'           ,
										'ARRIVAL TIME'                   ,
										'ARRIVAL TRANSPORT CODE'         ,
										'CANCELLATION REASON CODE'       ,
										'CHILDREN 1'                     ,
										'DEPARTURE TRANSPORT CODE'       ,
										'DEPARTURE TRANSPORTATION'       ,
										'GROUP NAME'                     ,
										'GROUP NAME ID'                  ,
										'LENGTH OF STAY'                 ,
										'MEMBERSHIP NUMBER'              ,
										'RATE AMOUNT'                    ,
										'VIDEO CHECKOUT'                 ,
										'AUTHORIZER'                     ,
										'HURDLE RATE'                    ,
										'PREFERENCE TYPE'                ,
										'EXTERNAL TRACE ID'              ,
										'JOINTO RESV NAME ID'            ,
										'PRE REGISTERED YN'              ,
										'WAITLIST PRIORITY'              ,
										'TRACE INSERT DATE'              ,
										'NEW ACCOMPANYING GUEST'         ,
										'RESV CONTACT ID'                ,
										'ARRIVAL CARRIER CODE'           ,
										'ARRIVAL DATE'                   ,
										'CHILDREN'                       ,
										'CHILDREN AGE'                   ,
										'CONNECTING YN'                  ,
										'FIRST NAME'                     ,
										'MEMBERSHIP TYPE'                ,
										'PRODUCTS'                       ,
										'RESERVATION TYPE'               ,
										'TAX TYPE'                       ,
										'TRAVEL AGENT NAME'              ,
										'TRAVEL AGENT NAME ID'           ,
										'CREDIT CARD TYPE'               ,
										'EXTRA BEDS'                     ,
										'ITEM'                           ,
										'PAYMENT METHOD DESC'            ,
										'ROOM FEATURES'                  ,
										'TA RECORD LOCATOR'              ,
										'RESOLVED BY'                    ,
										'TRACE TEXT'                     ,
										'WAITLIST REASON DESCRIPTION'    ,
										'TRACE INSERT USER'              ,
										'DELETED GUEST NAME'             ,
										'COMPANY NAME ID'                ,
										'MARKET CODE'                    ,
										'MOVE ACROSS RESORT'             ,
										'RESERVATION STATUS'             ,
										'REALTIME EXCHANGE'              ,
										'DEPOSIT DUE DATE'               ,
										'EXTERNAL USER ID'               ,
										'YIELD MARKET TYPE'              ,
										'ACCOMPANYING GUEST'             ,
										'DELETED ACCOMPANYING GUEST'     ,
										'ADULTS'                         ,
										'ARRIVAL STATION CODE'           ,
										'COMMISSION PAID TO'             ,
										'COMPANY NAME'                   ,
										'CONFIRMATION LEG NO'            ,
										'CURRENCY CODE'                  ,
										'DEPARTURE CARRIER CODE'         ,
										'DEPARTURE COMMENTS'             ,
										'DEPARTURE DATE'                 ,
										'DEPARTURE DATETIME'             ,
										'DEPARTURE TRANSPORT TYPE'       ,
										'DISCOUNT AMOUNT'                ,
										'DISCOUNT REASON'				 ,
										'PRINT RATE YN'                  ,
										'SOURCE NAME ID'                 ,
										'SPECIAL REQUEST'                ,
										'CREDIT CARD NUMBER'             ,
										'CREDIT CARD VAULT ID'           ,
										'EXTERNAL REFERENCE'             ,
										'HURDLE OVERRIDE'                ,
										'INSERT DATE'                    ,
										'PREFERENCE GROUP'               ,
										'YIELD STATUS'                   ,
										'JOINING RESV NAME ID'           ,
										'JOINING TYPE'                   ,
										'ACCOMPANYING GUEST FIRST'       ,
										'PARENT RESV NAME ID'            ,
										'WAITLIST REASON CODE'           ,
										'POSTAL CODE'                    ,
										'DELETED ACCOMPANYING NAMEID'    ,
										'PACKAGE DATES CHANGED'          ,
										'PAYMENT METHODS CHANGED YN'     ,
										'ARRIVAL TRANSPORTATION'         ,
										'AUTHORIZE DIRECT BILL'          ,
										'CHILDREN 3'                     ,
										'CONFIRMATION NO'                ,
										'FIXED RATE'                     ,
										'NO POST'                        ,
										'PAYMENT METHOD'                 ,
										'RATE CODE'                      ,
										'ROOM TYPE'                      ,
										'SHARE YN'                       ,
										'CRIBS'                          ,
										'END DATE'                       ,
										'QUANTITY'                       ,
										'TRACE DEPARTMENT'               ,
										'TRACE UPDATE USER'              ,
										'RTC'                            ,
										'COUNTRY'                        ,
										'REGION'                         ,
										'BILLING CONTACT ID'             ,
										'PACKAGE PRICE CHANGED'          ,
										'ARRIVAL COMMENTS'               ,
										'ARRIVAL TRANSPORT TYPE'         ,
										'CANCELLATION DATE'              ,
										'CANCELLATION REASON DESCRIPTION',
										'DEPARTURE STATION CODE'         ,
										'NAME'                           ,
										'NUMBER OF ROOMS'                ,
										'SOURCE CODE'                    ,
										'SOURCE NAME'                    ,
										'CREDIT CARD NUMBER 4 DIGITS'    ,
										'DEPOSIT AMOUNT'                 ,
										'EXT SEG NO'                     ,
										'EXTERNAL TYPE'                  ,
										'PREFERENCE TYPE DESCRIPTION'    ,
										'PROMOTIONS'                     ,
										'TRACE DATE'                     ,
										'STATE'                          ,
										'ARRIVAL DATETIME'               ,
										'ARRIVAL ESTIMATE TIME'          ,
										'CANCELLATION NO'                ,
										'CHECKOUT TIME'                  ,
										'DISCOUNT PERCENT'               ,
										'GUEST NAME ID'                  ,
										'ORIGIN'                         ,
										'RATEABLE VALUE'                 ,
										'ROOM'                           ,
										'BEGIN DATE'                     ,
										'COMMENTS'                       ,
										'CREDIT CARD EXPIRATION DATE'    ,
										'EXTERNAL REFERENCE LEG NUMBER'  ,
										'RESTRICTION OVERRIDE'           ,
										'UDF CHAR 38'                    ,
										'SEPARATING FROM RESV NAME ID'   ,
										'CITY'                           ,
										'DISTRICT'                       ,
										'NEW ACCOMPANYING NAMEID')
			 );

	  INSERT
	  INTO   action_type_subscription_dtls a
			 (
					a.ext_system_code,
					a.module         ,
					a.action_type    ,
					a.data_element
			 )
			 (SELECT vInterface   ,
					 b.module     ,
					 b.action_type,
					 b.data_element
			 FROM    action_type_dtls b,
					 action_type_subscription c
			 WHERE   c.ext_system_code = vInterface
			 AND     c.module          = 'PROFILE'
			 AND     b.module          = c.module
			 AND     b.action_type     = c.action_type
			 AND     b.data_element IN ( 'ADDRESS PRIMARY YN'       ,
										'ADDRESS TYPE'              ,
										'ADDRESS1'                  ,
										'ADDRESS2'                  ,
										'ADDRESS3'                  ,
										'ADDRESS4'                  ,
										'BUSINESS TITLE'            ,
										'CITY'                      ,
										'COMMENTS'                  ,
										'COMPANY NUMBER'            ,
										'COMPANY TYPE'              ,
										'COUNTRY'                   ,
										'EMAIL'                     ,
										'EMAIL LANGUAGE'            ,
										'EMAIL YN'                  ,
										'FAX NUMBER'                ,
										'FIRST NAME'                ,
										'GENDER'                    ,
										'GUEST PRIVACY YN'          ,
										'IATA NUMBER'               ,
										'IATA TYPE'                 ,
										'LANGUAGE'                  ,
										'MAILING LIST YN'           ,
										'MARKET RESEARCH YN'        ,
										'MEMBERSHIP EXPIRATION DATE',
										'MEMBERSHIP LEVEL'          ,
										'MEMBERSHIP NUMBER'         ,
										'MEMBERSHIP TYPE'           ,
										'MIDDLE'                    ,
										'NAME'                      ,
										'NAME TYPE'                 ,
										'NATIONALITY'               ,
										'PHONE NUMBER'              ,
										'PHONE PRIMARY YN'          ,
										'PHONE TYPE'                ,
										'POSTAL CODE'               ,
										'PREFERENCE GROUP'          ,
										'PREFERENCE TYPE'           ,
										'STATE'                     ,
										'STATUS'                    ,
										'THIRD PARTY YN'            ,
										'TITLE'                     ,
										'VIP STATUS'                ,
										'WEBPAGE')
			 );
	 
		insert into action_type_subscription_dtls a (a.ext_system_code, a.module,
		a.action_type, a.data_element)
			(select vInterface, b.module, b.action_type, b.data_element
			from action_type_dtls b, action_type_subscription c
			where c.ext_system_code = vInterface
				and c.module 		= 'STAY RECORDS'
				and b.module 		= c.module
				and b.action_type 	= c.action_type
				and b.data_element	= 'GUEST_NAME_ID');

		
		insert into action_type_subscription_dtls a (a.ext_system_code, a.module,
		a.action_type, a.data_element)
			(select vInterface, b.module, b.action_type, b.data_element
			from action_type_dtls b, action_type_subscription c
			where c.ext_system_code = vInterface
				and c.module not in  ('RESERVATION','PROFILE','STAY RECORDS')
				and b.module 		= c.module
				and b.action_type 	= c.action_type);
				
	/*create condition to trigger profile BE only when Guest profile is updated */
		be_where
			('  EXISTS (SELECT ''X'' FROM DATA_ELEMENT_TEMP WHERE ((NVL(UPPER(NEW_VALUE),''@#$%'')) = (''D'')) AND (ELEMENT = ''NAME TYPE''))',
			 'PROFILE'			 ,
			 'UPDATE PROFILE'	 ,
			 'NAME TYPE'		);
	END;
	
	/* From here on is the configuration of the OXI interface*/
	
	/*Set communication parameters*/			
	BEGIN
			UPDATE int_communication_details
					SET
						COMMUNICATION_TYPE    = 'HTTP'                                        	 ,
						ADDRESS               = 'https://integration.nor1solutions.com/opera/get',
						IDLETIME              = 0.05                                          	 ,
						UPDATE_USER           = -1                                            	 ,
						UPDATE_DATE           = SYSDATE                                       	 ,
						TIMEOUT               = 10                                            	 ,
						SLEEPTIMEWHENCOMMDOWN = 15                                           	 ,
						LOGDIR                = 'C:\PROGRA~2\Micros-Fidelio\OXChange\OXA\Logs'	 ,
						ALLOWED_COMM_TYPES    = 'ALL'                                         	 ,
						FILE_EXPORT_RECORD    = 'N'                                           	 ,
						PMS_RESORT            = 'SID-RESORT'
				WHERE  INTERFACE_ID           = vInterface
					AND    PURPOSE            = 'DOWNLOAD';

				UPDATE int_communication_details
					SET
						COMMUNICATION_TYPE    = 'HTTP'                                        	  ,
						ADDRESS               = 'https://integration.nor1solutions.com/opera/post',
						IDLETIME              = 0.05                                          	  ,
						UPDATE_USER           = -1                                            	  ,
						UPDATE_DATE           = SYSDATE                                       	  ,
						TIMEOUT               = 10                                            	  ,
						SLEEPTIMEWHENCOMMDOWN = 15                                           	  ,
						LOGDIR                = 'C:\PROGRA~2\Micros-Fidelio\OXChange\OXA\Logs'	  ,
						ALLOWED_COMM_TYPES    = 'ALL'                                         	  ,
						FILE_EXPORT_RECORD    = 'N'                                           	  ,
						PMS_RESORT            = 'SID-RESORT'
				WHERE  INTERFACE_ID           = vInterface
					AND    PURPOSE            = 'UPLOAD';
	END;
	BEGIN
	
		MERGE
			INTO         int_alert_config tgt
			USING (SELECT
				  vInterface						   		 INTERFACE_ID      ,
				  NULL								   		 RESORT            ,
				  'IS'								   		 ALERT_GROUP_CODE  ,
				  1									   		 ALERT_QUERY_ID    ,
				  'NO_REF_CURSOR'					   		 ALERT_QUERY       ,
				  'Interface machine or OXI service is down' ALERT_DESCRIPTION ,
				  '1'                                  		 MODULE            ,
				  NULL								   		 TIME_IN_SECS_P1   ,
				  NULL								   		 TIME_VAL_P1       ,
				  NULL								   		 TIME_VAL_TU_P1    ,
				  'N'                                  		 TIME_VAL_P1_YN    ,
				  'N'                                  		 TIME_VAL_P2_YN    ,
				  NULL                                 		 ABS_VAL_P1        ,
				  'N'                                  		 ABS_VAL_P1_YN     ,
				  'N'                                  		 ABS_VAL_P2_YN     ,
				  NULL                                 		 PRC_VAL_P1        ,
				  'N'                                  		 PRC_VAL_P1_YN     ,
				  'N'                                  		 PRC_VAL_P2_YN     ,
				  'YY'                                 		 ALERT_ENABLED_UD  ,
				  'L3'                                 		 MAX_ALERT_LEVEL   ,
				  'N'                                  		 SEND_QUERY_DUMP_YN,
				  'Interface machine or OXI service is down' ALERT_SHORT_DESC  ,
				  'L1/L2/L3'                           		 ALERT_ESCL_LEVELS ,
				  '10~~M'                              		 ALERT_ESCL_INTR_L1,
				  '60~~M'							   		 ALERT_ESCL_INTR_L2,
				  '240~~M'							   		 ALERT_ESCL_INTR_L3,
				  'operasupportL1@nor1.com'			   		 ALERT_L1_EMAIL_ID ,
				  'operasupportL2@nor1.com'			   		 ALERT_L2_EMAIL_ID ,
				  'operasupportL3@nor1.com'			   		 ALERT_L3_EMAIL_ID ,
				  NULL								   		 ALERT_L1_SMS_NO   ,
				  NULL								   		 ALERT_L2_SMS_NO   ,
				  NULL								   		 ALERT_L3_SMS_NO   ,
				  'TEXT'        					   		 ALERT_FMT_L1      ,
				  'TEXT'        					   		 ALERT_FMT_L2      ,
				  'TEXT'        					   		 ALERT_FMT_L3      ,
				  -1            					   		 UPDATE_USER       ,
				  TRUNC(SYSDATE)					   		 UPDATE_DATE       ,
				  -1            					   		 INSERT_USER       ,
				  TRUNC(SYSDATE)							 INSERT_DATE
						 FROM    dual
						 )
						 src
			ON (
									  tgt.INTERFACE_ID	   = src.INTERFACE_ID
						 AND          tgt.ALERT_GROUP_CODE = src.ALERT_GROUP_CODE
						 AND		  tgt.ALERT_QUERY_ID   = src.ALERT_QUERY_ID
						 AND		  tgt.ALERT_QUERY	   = src.ALERT_QUERY
						 AND		  tgt.MODULE		   = src.MODULE
						 )
			WHEN MATCHED THEN
			UPDATE
			SET              tgt.RESORT				= src.RESORT			,
							 tgt.ALERT_DESCRIPTION	= src.ALERT_DESCRIPTION ,
							 tgt.TIME_IN_SECS_P1	= src.TIME_IN_SECS_P1   ,
							 tgt.TIME_VAL_P1		= src.TIME_VAL_P1       ,
							 tgt.TIME_VAL_TU_P1		= src.TIME_VAL_TU_P1    ,
							 tgt.TIME_VAL_P1_YN		= src.TIME_VAL_P1_YN    ,
							 tgt.TIME_VAL_P2_YN    	= src.TIME_VAL_P2_YN    ,
							 tgt.ABS_VAL_P1        	= src.ABS_VAL_P1        ,
							 tgt.ABS_VAL_P1_YN     	= src.ABS_VAL_P1_YN     , 
							 tgt.ABS_VAL_P2_YN     	= src.ABS_VAL_P2_YN     ,
							 tgt.PRC_VAL_P1        	= src.PRC_VAL_P1        ,
							 tgt.PRC_VAL_P1_YN     	= src.PRC_VAL_P1_YN     ,
							 tgt.PRC_VAL_P2_YN     	= src.PRC_VAL_P2_YN     ,
							 tgt.ALERT_ENABLED_UD  	= src.ALERT_ENABLED_UD  ,
							 tgt.MAX_ALERT_LEVEL   	= src.MAX_ALERT_LEVEL   ,
							 tgt.SEND_QUERY_DUMP_YN	= src.SEND_QUERY_DUMP_YN,
							 tgt.ALERT_SHORT_DESC  	= src.ALERT_SHORT_DESC  ,
							 tgt.ALERT_ESCL_LEVELS 	= src.ALERT_ESCL_LEVELS ,
							 tgt.ALERT_ESCL_INTR_L1	= src.ALERT_ESCL_INTR_L1,
							 tgt.ALERT_ESCL_INTR_L2	= src.ALERT_ESCL_INTR_L2,
							 tgt.ALERT_ESCL_INTR_L3	= src.ALERT_ESCL_INTR_L3,
							 tgt.ALERT_L1_EMAIL_ID 	= src.ALERT_L1_EMAIL_ID ,
							 tgt.ALERT_L2_EMAIL_ID 	= src.ALERT_L2_EMAIL_ID ,
							 tgt.ALERT_L3_EMAIL_ID 	= src.ALERT_L3_EMAIL_ID ,
							 tgt.ALERT_L1_SMS_NO   	= src.ALERT_L1_SMS_NO   ,
							 tgt.ALERT_L2_SMS_NO   	= src.ALERT_L2_SMS_NO   ,
							 tgt.ALERT_L3_SMS_NO   	= src.ALERT_L3_SMS_NO   ,
							 tgt.ALERT_FMT_L1      	= src.ALERT_FMT_L1      ,
							 tgt.ALERT_FMT_L2      	= src.ALERT_FMT_L2      ,
							 tgt.ALERT_FMT_L3      	= src.ALERT_FMT_L3      ,
							 tgt.UPDATE_USER       	= src.UPDATE_USER       ,
							 tgt.UPDATE_DATE       	= src.UPDATE_DATE       ,
							 tgt.INSERT_USER       	= src.INSERT_USER       ,
							 tgt.INSERT_DATE		= src.INSERT_DATE 
							 
							 
			WHEN NOT MATCHED THEN
			INSERT
				   (
					tgt.INTERFACE_ID      ,
					tgt.RESORT            ,
					tgt.ALERT_GROUP_CODE  ,
					tgt.ALERT_QUERY_ID    ,
					tgt.ALERT_QUERY       ,
					tgt.ALERT_DESCRIPTION ,
					tgt.MODULE            ,
					tgt.TIME_IN_SECS_P1   ,
					tgt.TIME_VAL_P1       ,
					tgt.TIME_VAL_TU_P1    ,
					tgt.TIME_VAL_P1_YN    ,
					tgt.TIME_VAL_P2_YN    ,
					tgt.ABS_VAL_P1        ,
					tgt.ABS_VAL_P1_YN     ,
					tgt.ABS_VAL_P2_YN     ,
					tgt.PRC_VAL_P1        ,
					tgt.PRC_VAL_P1_YN     ,
					tgt.PRC_VAL_P2_YN     ,
					tgt.ALERT_ENABLED_UD  ,
					tgt.MAX_ALERT_LEVEL   ,
					tgt.SEND_QUERY_DUMP_YN,
					tgt.ALERT_SHORT_DESC  ,
					tgt.ALERT_ESCL_LEVELS ,
					tgt.ALERT_ESCL_INTR_L1,
					tgt.ALERT_ESCL_INTR_L2,
					tgt.ALERT_ESCL_INTR_L3,
					tgt.ALERT_L1_EMAIL_ID ,
					tgt.ALERT_L2_EMAIL_ID ,
					tgt.ALERT_L3_EMAIL_ID ,
					tgt.ALERT_L1_SMS_NO   ,
					tgt.ALERT_L2_SMS_NO   ,
					tgt.ALERT_L3_SMS_NO   ,
					tgt.ALERT_FMT_L1      ,
					tgt.ALERT_FMT_L2      ,
					tgt.ALERT_FMT_L3      ,
					tgt.UPDATE_USER       ,
					tgt.UPDATE_DATE       ,
					tgt.INSERT_USER       ,
					tgt.INSERT_DATE
				   )
				   VALUES
				   (
					src.INTERFACE_ID      ,
					src.RESORT            ,
					src.ALERT_GROUP_CODE  ,
					src.ALERT_QUERY_ID    ,
					src.ALERT_QUERY       ,
					src.ALERT_DESCRIPTION ,
					src.MODULE            ,
					src.TIME_IN_SECS_P1   ,
					src.TIME_VAL_P1       ,
					src.TIME_VAL_TU_P1    ,
					src.TIME_VAL_P1_YN    ,
					src.TIME_VAL_P2_YN    ,
					src.ABS_VAL_P1        ,
					src.ABS_VAL_P1_YN     ,
					src.ABS_VAL_P2_YN     ,
					src.PRC_VAL_P1        ,
					src.PRC_VAL_P1_YN     ,
					src.PRC_VAL_P2_YN     ,
					src.ALERT_ENABLED_UD  ,
					src.MAX_ALERT_LEVEL   ,
					src.SEND_QUERY_DUMP_YN,
					src.ALERT_SHORT_DESC  ,
					src.ALERT_ESCL_LEVELS ,
					src.ALERT_ESCL_INTR_L1,
					src.ALERT_ESCL_INTR_L2,
					src.ALERT_ESCL_INTR_L3,
					src.ALERT_L1_EMAIL_ID ,
					src.ALERT_L2_EMAIL_ID ,
					src.ALERT_L3_EMAIL_ID ,
					src.ALERT_L1_SMS_NO   ,
					src.ALERT_L2_SMS_NO   ,
					src.ALERT_L3_SMS_NO   ,
					src.ALERT_FMT_L1      ,
					src.ALERT_FMT_L2      ,
					src.ALERT_FMT_L3      ,
					src.UPDATE_USER       ,
					src.UPDATE_DATE       ,
					src.INSERT_USER       ,
					src.INSERT_DATE
				   );
				   
				MERGE
			INTO         int_alert_config tgt
			USING (SELECT
				  vInterface						   		 INTERFACE_ID      ,
				  NULL								   		 RESORT            ,
				  'IS'								   		 ALERT_GROUP_CODE  ,
				  2									   		 ALERT_QUERY_ID    ,
				  'select *
				  from ifc_interface_status
				  where thread_name in (''1'')
				  and thread_stopped is null'			  	 ALERT_QUERY       ,
				  'One or more Interface threads are down' 	 ALERT_DESCRIPTION ,
				  '1'                                  		 MODULE            ,
				  NULL								   		 TIME_IN_SECS_P1   ,
				  NULL								   		 TIME_VAL_P1       ,
				  NULL								   		 TIME_VAL_TU_P1    ,
				  'N'                                  		 TIME_VAL_P1_YN    ,
				  'N'                                  		 TIME_VAL_P2_YN    ,
				  NULL                                 		 ABS_VAL_P1        ,
				  'N'                                  		 ABS_VAL_P1_YN     ,
				  'N'                                  		 ABS_VAL_P2_YN     ,
				  NULL                                 		 PRC_VAL_P1        ,
				  'N'                                  		 PRC_VAL_P1_YN     ,
				  'N'                                  		 PRC_VAL_P2_YN     ,
				  'YY'                                 		 ALERT_ENABLED_UD  ,
				  'L3'                                 		 MAX_ALERT_LEVEL   ,
				  'N'                                  		 SEND_QUERY_DUMP_YN,
				  'One or more Interface threads are down'   ALERT_SHORT_DESC  ,
				  'L1/L2/L3'                           		 ALERT_ESCL_LEVELS ,
				  '10~~M'                              		 ALERT_ESCL_INTR_L1,
				  '60~~M'							   		 ALERT_ESCL_INTR_L2,
				  '240~~M'							   		 ALERT_ESCL_INTR_L3,
				  'operasupportL1@nor1.com'			   		 ALERT_L1_EMAIL_ID ,
				  'operasupportL2@nor1.com'			   		 ALERT_L2_EMAIL_ID ,
				  'operasupportL3@nor1.com'			   		 ALERT_L3_EMAIL_ID ,
				  NULL								   		 ALERT_L1_SMS_NO   ,
				  NULL								   		 ALERT_L2_SMS_NO   ,
				  NULL								   		 ALERT_L3_SMS_NO   ,
				  'TEXT'        					   		 ALERT_FMT_L1      ,
				  'TEXT'        					   		 ALERT_FMT_L2      ,
				  'TEXT'        					   		 ALERT_FMT_L3      ,
				  -1            					   		 UPDATE_USER       ,
				  TRUNC(SYSDATE)					   		 UPDATE_DATE       ,
				  -1            					   		 INSERT_USER       ,
				  TRUNC(SYSDATE)							 INSERT_DATE
						 FROM    dual
						 )
						 src
					  ON (
									  tgt.INTERFACE_ID	   = src.INTERFACE_ID
						 AND          tgt.ALERT_GROUP_CODE = src.ALERT_GROUP_CODE
						 AND		  tgt.ALERT_QUERY_ID   = src.ALERT_QUERY_ID
						 AND		  tgt.ALERT_QUERY	   = src.ALERT_QUERY
						 AND		  tgt.MODULE		   = src.MODULE
						 )
			WHEN MATCHED THEN
			UPDATE
			SET              tgt.RESORT				= src.RESORT			,
							 tgt.ALERT_DESCRIPTION	= src.ALERT_DESCRIPTION ,
							 tgt.TIME_IN_SECS_P1	= src.TIME_IN_SECS_P1   ,
							 tgt.TIME_VAL_P1		= src.TIME_VAL_P1       ,
							 tgt.TIME_VAL_TU_P1		= src.TIME_VAL_TU_P1    ,
							 tgt.TIME_VAL_P1_YN		= src.TIME_VAL_P1_YN    ,
							 tgt.TIME_VAL_P2_YN    	= src.TIME_VAL_P2_YN    ,
							 tgt.ABS_VAL_P1        	= src.ABS_VAL_P1        ,
							 tgt.ABS_VAL_P1_YN     	= src.ABS_VAL_P1_YN     , 
							 tgt.ABS_VAL_P2_YN     	= src.ABS_VAL_P2_YN     ,
							 tgt.PRC_VAL_P1        	= src.PRC_VAL_P1        ,
							 tgt.PRC_VAL_P1_YN     	= src.PRC_VAL_P1_YN     ,
							 tgt.PRC_VAL_P2_YN     	= src.PRC_VAL_P2_YN     ,
							 tgt.ALERT_ENABLED_UD  	= src.ALERT_ENABLED_UD  ,
							 tgt.MAX_ALERT_LEVEL   	= src.MAX_ALERT_LEVEL   ,
							 tgt.SEND_QUERY_DUMP_YN	= src.SEND_QUERY_DUMP_YN,
							 tgt.ALERT_SHORT_DESC  	= src.ALERT_SHORT_DESC  ,
							 tgt.ALERT_ESCL_LEVELS 	= src.ALERT_ESCL_LEVELS ,
							 tgt.ALERT_ESCL_INTR_L1	= src.ALERT_ESCL_INTR_L1,
							 tgt.ALERT_ESCL_INTR_L2	= src.ALERT_ESCL_INTR_L2,
							 tgt.ALERT_ESCL_INTR_L3	= src.ALERT_ESCL_INTR_L3,
							 tgt.ALERT_L1_EMAIL_ID 	= src.ALERT_L1_EMAIL_ID ,
							 tgt.ALERT_L2_EMAIL_ID 	= src.ALERT_L2_EMAIL_ID ,
							 tgt.ALERT_L3_EMAIL_ID 	= src.ALERT_L3_EMAIL_ID ,
							 tgt.ALERT_L1_SMS_NO   	= src.ALERT_L1_SMS_NO   ,
							 tgt.ALERT_L2_SMS_NO   	= src.ALERT_L2_SMS_NO   ,
							 tgt.ALERT_L3_SMS_NO   	= src.ALERT_L3_SMS_NO   ,
							 tgt.ALERT_FMT_L1      	= src.ALERT_FMT_L1      ,
							 tgt.ALERT_FMT_L2      	= src.ALERT_FMT_L2      ,
							 tgt.ALERT_FMT_L3      	= src.ALERT_FMT_L3      ,
							 tgt.UPDATE_USER       	= src.UPDATE_USER       ,
							 tgt.UPDATE_DATE       	= src.UPDATE_DATE       ,
							 tgt.INSERT_USER       	= src.INSERT_USER       ,
							 tgt.INSERT_DATE		= src.INSERT_DATE 
							 
							 
			WHEN NOT MATCHED THEN
			INSERT
				   (
					tgt.INTERFACE_ID      ,
					tgt.RESORT            ,
					tgt.ALERT_GROUP_CODE  ,
					tgt.ALERT_QUERY_ID    ,
					tgt.ALERT_QUERY       ,
					tgt.ALERT_DESCRIPTION ,
					tgt.MODULE            ,
					tgt.TIME_IN_SECS_P1   ,
					tgt.TIME_VAL_P1       ,
					tgt.TIME_VAL_TU_P1    ,
					tgt.TIME_VAL_P1_YN    ,
					tgt.TIME_VAL_P2_YN    ,
					tgt.ABS_VAL_P1        ,
					tgt.ABS_VAL_P1_YN     ,
					tgt.ABS_VAL_P2_YN     ,
					tgt.PRC_VAL_P1        ,
					tgt.PRC_VAL_P1_YN     ,
					tgt.PRC_VAL_P2_YN     ,
					tgt.ALERT_ENABLED_UD  ,
					tgt.MAX_ALERT_LEVEL   ,
					tgt.SEND_QUERY_DUMP_YN,
					tgt.ALERT_SHORT_DESC  ,
					tgt.ALERT_ESCL_LEVELS ,
					tgt.ALERT_ESCL_INTR_L1,
					tgt.ALERT_ESCL_INTR_L2,
					tgt.ALERT_ESCL_INTR_L3,
					tgt.ALERT_L1_EMAIL_ID ,
					tgt.ALERT_L2_EMAIL_ID ,
					tgt.ALERT_L3_EMAIL_ID ,
					tgt.ALERT_L1_SMS_NO   ,
					tgt.ALERT_L2_SMS_NO   ,
					tgt.ALERT_L3_SMS_NO   ,
					tgt.ALERT_FMT_L1      ,
					tgt.ALERT_FMT_L2      ,
					tgt.ALERT_FMT_L3      ,
					tgt.UPDATE_USER       ,
					tgt.UPDATE_DATE       ,
					tgt.INSERT_USER       ,
					tgt.INSERT_DATE
				   )
				   VALUES
				   (
					src.INTERFACE_ID      ,
					src.RESORT            ,
					src.ALERT_GROUP_CODE  ,
					src.ALERT_QUERY_ID    ,
					src.ALERT_QUERY       ,
					src.ALERT_DESCRIPTION ,
					src.MODULE            ,
					src.TIME_IN_SECS_P1   ,
					src.TIME_VAL_P1       ,
					src.TIME_VAL_TU_P1    ,
					src.TIME_VAL_P1_YN    ,
					src.TIME_VAL_P2_YN    ,
					src.ABS_VAL_P1        ,
					src.ABS_VAL_P1_YN     ,
					src.ABS_VAL_P2_YN     ,
					src.PRC_VAL_P1        ,
					src.PRC_VAL_P1_YN     ,
					src.PRC_VAL_P2_YN     ,
					src.ALERT_ENABLED_UD  ,
					src.MAX_ALERT_LEVEL   ,
					src.SEND_QUERY_DUMP_YN,
					src.ALERT_SHORT_DESC  ,
					src.ALERT_ESCL_LEVELS ,
					src.ALERT_ESCL_INTR_L1,
					src.ALERT_ESCL_INTR_L2,
					src.ALERT_ESCL_INTR_L3,
					src.ALERT_L1_EMAIL_ID ,
					src.ALERT_L2_EMAIL_ID ,
					src.ALERT_L3_EMAIL_ID ,
					src.ALERT_L1_SMS_NO   ,
					src.ALERT_L2_SMS_NO   ,
					src.ALERT_L3_SMS_NO   ,
					src.ALERT_FMT_L1      ,
					src.ALERT_FMT_L2      ,
					src.ALERT_FMT_L3      ,
					src.UPDATE_USER       ,
					src.UPDATE_DATE       ,
					src.INSERT_USER       ,
					src.INSERT_DATE
				   );
	END;

	BEGIN
	/* Identify all properties that have an active NOR1 interface that has not been configured yet*/

		for r3 in (
			SELECT PMS_resort resort
				   FROM    int_interface_setup
				   WHERE   interface_id = vInterface
				   AND
					 (
					   (
						active_YN != 'N'
						)
					 OR
					   (
						active_YN IS NULL
						)
					  )
				   AND     inactive_date IS NULL
				   AND	   PMS_resort not in
						   (select pms_resort
								  from int_udf_definition
								   where interface_id = vInterface
								     and PMS_table 	  = 'RESERVATION_NAME'
							)
				   AND     PMS_resort  in
				           (SELECT   resort
				              FROM     ext_sys_active_resort
				                  WHERE    inactive_date     		IS NULL
				                    AND      display_yn             = 'Y'
				                    AND      code                   = vInterface
						    )
				AND ((vCondition = 'ALL')
				OR
				( PMS_resort = vCondition))	
				)
				   
		LOOP
		
			BEGIN
	/*Create UDF configuration providing the original physically assigned room*/
				insert into int_udf_definition
				(interface_id, profile_type, PMS_table, pms_column, active_yn, PMS_resort, MODULE_NAME, IFC_CREATED_YN)
				Values
				(vInterface,'RESERVATION','RESERVATION_NAME',vUdf,'Y',r3.resort,'RESERVATION','N');
				
				
			BEGIN
	/*Create LOV Group and Value configuration providing Block Upgrade Scope*/

MERGE
INTO         entity_header tgt
USING        ( SELECT vEntity             AS entity_name      ,
                     'N'                  AS user_maintainable,
                     'NOR1 Upgrade Scope' AS description      ,
                     'N'                  AS property_level_YN,
                     'Char'               AS data_type        ,
                     8                    AS data_length      ,
                     SYSDATE              AS insert_date      ,
                     -1                   AS insert_user      ,
                     SYSDATE              AS update_date      ,
                     -1                   AS update_user      ,
                     ''                   AS inactive_date
             FROM    dual
             )
             src
ON (
                          tgt.entity_name = src.entity_name
             )
WHEN MATCHED THEN
UPDATE
SET              tgt.user_maintainable = src.user_maintainable,
                 tgt.description       = src.description      ,
                 tgt.property_level_YN = src.property_level_YN,
                 tgt.data_type         = src.data_type        ,
                 tgt.data_length       = src.data_length      ,
                 tgt.update_date       = src.update_date      ,
                 tgt.update_user       = src.update_user      ,
                 tgt.inactive_date     = src.inactive_date
WHEN NOT MATCHED THEN
INSERT
       (
              entity_name      ,
              user_maintainable,
              description      ,
              property_level_YN,
              data_type        ,
              data_length      ,
              insert_date      ,
              insert_user      ,
              update_date      ,
              update_user
       )
       VALUES
       (
              src.entity_name      ,
              src.user_maintainable,
              src.description      ,
              src.property_level_YN,
              src.data_type        ,
              src.data_length      ,
              src.insert_date      ,
              src.insert_user      ,
              src.update_date      ,
              src.update_user
       );

END;

Begin
/* Creation of Nor1 Upgrade Scope LOV Values*/


	Delete from entity_detail 
	Where entity_name = vEntity
	and chain_code = vOperaChain;

INSERT INTO entity_detail 
            (
                        entity_name    ,
                        attribute_code ,
                        description    ,
                        order_by       ,
                        insert_date    ,
                        insert_user    ,
                        update_date    ,
                        update_user    ,
                        inactive_date  ,
                        chain_code
            )
            ( SELECT vEntity                                            entity_name    ,
                    'ESTANDBY - 1.'                                     attribute_code ,
                    'Allow sending of eStandby Upgrade offers' 			description    ,
                    '1'                                                 order_by       ,
                    SYSDATE                                             insert_date    ,
                    -1                                                  insert_user    ,
                    SYSDATE                                             update_date    ,
                    -1                                                  update_user    ,
                    ''                                                  inactive_date  ,
                    vOperaChain                                         chain_code
					FROM dual           
            UNION
                    SELECT 'NOR1_UPGRADE'                            ,
                          'FRONTDESK - 2.'                           ,
                          'Allow upselling at Front Desk'			 ,
                          '2'                                        ,
                          SYSDATE                                    ,
                          -1                                         ,
                          SYSDATE                                    ,
                          -1                                         ,
                          ''                                         ,
                          vOperaChain
					FROM dual
					UNION
                  SELECT 'NOR1_UPGRADE'                 ,
                        'CONFIRMED - 3.'                ,
                        'Allow sending of immediate confirm upgrade offers' ,
                        '3'                             ,
                        SYSDATE                         ,
                        -1                              ,
                        SYSDATE                         ,
                        -1                              ,
                        ''                              ,
                        vOperaChain
					FROM dual                      
					UNION
				SELECT 'NOR1_UPGRADE'                  ,
                      'NONE - 0.'                      ,
                      'Do not allow any upsell offers' ,
                      '9'                              ,
                      SYSDATE                          ,
                      -1                               ,
                      SYSDATE                          ,
                      -1                               ,
                      ''                               ,
                      vOperaChain
					FROM dual
              );
END;			

	/*Activate Opera Reservation Alerts if property has an active Full Service license (OPP_XXX) or an active ORS license is in place */
				update APPLICATION_PARAMETER_VALUES
				   set parameter_value = 'Y'
				   where resort 		= r3.resort
				     and parameter_name = 'ALERTS'
					 and exists
							(
							 (select 1 from INSTALLED_APP_LICENSES
						              where application 	= 'OPERA PMS'
										and resort			= r3.resort
									    and product_code 	like 'OPP_5%'
										and license_code 	is not null
										and expiry_date 	>= trunc(sysdate)
										and active_yn 		= 'Y')
								UNION ALL
							 (select 1 from INSTALLED_APP_LICENSES
						              where application 	= 'OPERA ORS'
										and resort			= '_GLOBAL'
									    and product_code 	like 'OPR_5%'
										and license_code 	is not null
										and expiry_date 	>= trunc(sysdate)
										and active_yn 		= 'Y')
							);
							

	/*Update all OXI parameters as required*/
				update int_parameters
				set parameter_value = 'N'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'GENERATE_XML'					 ,
					'HANDLE_PSEUDO_ROOMTYPES'		 ,
					'MULTI_ROOMTYPE_CONV'			 ,
					'ENABLE_ALLOWANCE'				 ,
					'EXTERNAL_PHYSICALROOMS'		 ,
					'INV_ROOMCLASS'					 ,
					'INV_SNAPSHOT_BLOCKS'			 ,
					'INV_SNAPSHOT_RES'				 ,
					'UPL_BLOCK_INFO'				 ,
					'CORP_CRS_OVER'					 ,
					'DISCARD_DUPLICATE_MEMBERSHIPS'	 ,
					'GROUP_CRS_OVER'				 ,
					'IND_CRS_OVER'					 ,
					'INTEREST_CRS_OVER'				 ,
					'NEGRATES_CRS_OVER'				 ,
					'PHONE/ADDR_CRS_OVER'			 ,
					'PREF_CRS_OVER'					 ,
					'PROFCOMMENTS_CRS_OVER'			 ,
					'SOURCE_CRS_OVER'				 ,
					'TA_CRS_OVER'					 ,
					'UPDATE_SALUTATION'				 ,
					'AUTO_POPULATE_MEMBERSHIP_YN'	 ,
					'EXT_SYSTEM_HANDLES_DAY_USE'	 ,
					'ITINERARY_FUNCTIONALITY'		 ,
					'ITEM_INVENTORY_EXT_SYS_OVER'	 ,
					'INHERIT_EXTERNAL_REFERENCE'	 ,
					'RESCOMMENTS_CRS_OVER'			 ,
					'PROMOCODE_CRS_OVER'			 ,
					'SPECIALS_CRS_OVER'				 ,
					'UPDATE_ACCOMPANY_GUEST'		 ,
					'UPDATE_FEATURES'				 ,
					'UPDATE_FIXED_CHARGES'			 ,
					'UPL_DED_RES_ONLY'				 ,
					'UPDATE_PAYMENT_TYPES_PER_WINDOW',
					'UPDATE_ROUTING_TO_ROOM'		 ,
					'UPDATE_ROUTING_TO_WINDOW'			,
					'CRS_BLOCK_GENERATES_INVENTORY'		,
					'EXTERNAL_LOCKED_YN'				,
					'HANDLE_MASTER_BLOCKS'				,
					'SPECIFIC_BLOCK_EXCHANGE'			,
					'SPLIT_INV_DETAILS'					,
					'UPL_CATERING_BLOCKS'				,
					'UPL_DED_ONLY'						,
					'UPL_OPEN_ONLY'						,
					'WAIT_FOR_BLOCK_EXT_REF'			);
					
				update int_parameters
				set parameter_value = 'N'
				where INTERFACE_ID  = vInterface 
				and PARAMETER_NAME  in (
					'UPDATE_PROTECTED_BLOCKS'				,
					'UPDATE_PROTECTED_SELL_LIMITS_BLOCKS'	);
				
				update int_parameters
				set parameter_value = 'Y'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'WARNINGS_AS_TRACES'				 ,
					'PROF_MATCHING'						 ,
					'CRS_RESERVATION_GENERATES_INVENTORY',
					'EXT_SYS_PACKAGE_PRICE_CONTROL'		 );

				update int_parameters
				set parameter_value = 'Y'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'UPDATE_INHOUSE_RES'				 ,
					'UPDATE_SHARE_RES'					);	

				update int_parameters
				set parameter_value = 'NONE'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'RELATIONSHIPS_INCOMING',
					'RELATIONSHIPS_OUTGOING');
					
				update int_parameters
				set parameter_value = 'A'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'PACKAGE_CRS_OVER',
					'UPDATE_FIXED_CHARGES');
					
				update int_parameters
				set parameter_value = 'OPERA->EXT_SYS'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  in (
					'HANDLE_BLOCK_SOLD');	
				
				update int_parameters
				set parameter_value = 'B,R'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'AUTO_RESYNC_ON_ROLL_BD';
				
				update int_parameters
				set parameter_value = 'EBRD'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'CRS_MARKET/SOURCE_CONTROL';

				update int_parameters
				set parameter_value  = 'BOTH'
				where INTERFACE_ID   = vInterface
				and PARAMETER_NAME   = 'CREDIT_CARD_SUPRESS'
				and PARAMETER_VALUE != 'BOTH';						

				update int_parameters
				set parameter_value = 'ALL'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'LANGUAGE_HANDLING';

				update int_parameters
				set parameter_value = 7
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'PURGE_NO_DAYS';

				update int_parameters
				set parameter_value = 'RTAV'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'CHANGE_TO_INVENTORY_GENERATES';

				update int_parameters
				set parameter_value = 200
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'MAX_PROFILE_MATCHES';

				update int_parameters
				set parameter_value = 'NU'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'FULL_OPERA_SHARES';

				update int_parameters
				set parameter_value = 'NEITHER'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'SHARER_REMOVAL_MODE';

				update int_parameters
				set parameter_value = 'INACTIVE'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'SHARE_RATE_CALC_METHOD';

				update int_parameters
				set parameter_value = 'D'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'STAY_UPLOAD_PROFILE_TYPES';

				update int_parameters
				set parameter_value = 'ALWAYS'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'TRACE_ROOM_ASSIGNMENT';

				update int_parameters
				set parameter_value = 'RESERVATION'
				where INTERFACE_ID  = vInterface
				and PARAMETER_NAME  = 'CHANGE_TO_PROFILE_GENERATES';
				
				update int_parameters
				set parameter_value = 'YC'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'CRS_PRICE_CONTROL';
				
				update int_parameters
				set parameter_value = 512
				where INTERFACE_ID  = vInterface 
				and PARAMETER_NAME  = 'MAX_SIZE_RESERVATIONS';
				
				update int_parameters
				set parameter_value = 'Y'
				where INTERFACE_ID  = vInterface and RESORT = r3.resort
				and PARAMETER_NAME  = 'ITINERARY_FUNCTIONALITY';
				
	/* Set Interface configuration for the resorts */
					
				update int_interface_setup
					set
						EXTERNAL_RESORT	=	vNor1Chain||':'||r3.resort	,
						INTERFACE_TYPE	=	'DOWNLOAD,UPLOAD,EXPORT',
						DELTACHANGES_YN	=	'Y'						,
						MESSAGE_FORMAT	=	'XML'					,
						UPDATE_USER		=	'-1'					,
						UPDATE_DATE		=	trunc(sysdate)
				where INTERFACE_ID		=	vInterface
					and PMS_resort		=	r3.resort;
					
	/* Create Reservation Alert type template if it does not exist */

				INSERT
				INTO   alerts_template tgt
					   (
							  tgt.alert_code ,
							  tgt.description,
							  tgt.insert_date,
							  tgt.insert_user,
							  tgt.update_date,
							  tgt.update_user,
							  tgt.chain_code
					   )
				SELECT vALERT	                             alert_code ,
					   'Alerts inserted by NOR1 Upgrade'     description,
					   SYSDATE                               insert_date,
					   -1                                    insert_user,
					   SYSDATE                               update_date,
					   -1                                    update_user,
					   vOperaChain                           chain_code
				FROM   dual
				WHERE  NOT EXISTS
					   (SELECT 1
					   FROM    alerts_template
					   WHERE   alert_code = vALERT
					   AND     chain_code = vOperaChain
					   );

	/* Create Alert type in resorts tables if it does not exist and alerts function is active for the resort*/
		
				MERGE
						INTO         resort_alerts tgt
						USING        (SELECT
										r3.resort								resort	   ,
										vALERT									alert_code ,
										'Alerts inserted by NOR1 Upgrade'	    description,
										sysdate									insert_date,
										-1										insert_user,
										sysdate									update_date,
										-1										update_user
									 FROM    dual
									 where exists
										(select 1 from APPLICATION_PARAMETER_VALUES
												  where resort 		   = r3.resort
													and parameter_name = 'ALERTS'
													and parameter_value = 'Y')

									 )
									 src
						ON (
												tgt.resort		= src.resort  
										AND		tgt.alert_code  = src.alert_code
									 )
										 
						WHEN NOT MATCHED THEN
						INSERT
							   (
									  tgt.resort	 ,
									  tgt.alert_code ,
									  tgt.description,
									  tgt.insert_date,
									  tgt.insert_user,
									  tgt.update_date,
									  tgt.update_user
							   )
							   VALUES
							   (
									  src.resort	 ,
									  src.alert_code ,
									  src.description,
									  src.insert_date,
									  src.insert_user,
									  src.update_date,
									  src.update_user
							   );
	/* Create NOR1 Interface Trace Department template if it does not exist */

				INSERT
				INTO   department_template tgt
					   (
							  tgt.dept_id ,
							  tgt.dept_name,
							  tgt.insert_user,
							  tgt.insert_date,
							  tgt.update_user,
							  tgt.update_date,
							  tgt.chain_code
					   )
				SELECT '*NOR1'                            	 dept_id ,
					   'NOR1 Interface Traces' 				 dept_name,
					   -1                              		 insert_user,
					   SYSDATE                               insert_date,
					   -1                               	 update_user,
					   SYSDATE                               update_date,
					   vOperaChain                           chain_code
				FROM   dual
				WHERE  NOT EXISTS
					   (SELECT 1
					   FROM    department_template
					   WHERE   dept_id = '*NOR1'
					   );

	/* Create NOR1 Interface Trace Department in department tables if it does not exist for the resort*/
		
				MERGE
						INTO         department tgt
						USING        (SELECT
										r3.resort								resort,
										'*NOR1'									dept_id ,
										'NOR1 Interface Traces'					dept_name,
										-1										insert_user,
										sysdate									insert_date,
										-1										update_user ,
										sysdate									update_date							
									 FROM    dual
									 
									 )
									 src
						ON (
												tgt.resort		= src.resort  
										AND		tgt.dept_id  	= src.dept_id
									 )
										 
						WHEN NOT MATCHED THEN
						INSERT
							   (
									  tgt.resort,
									  tgt.dept_id,
									  tgt.dept_name,
									  tgt.insert_user,
									  tgt.insert_date,
									  tgt.update_user,
									  tgt.update_date
									  
							   )
							   VALUES
							   (
									  src.resort	 ,
									  src.dept_id	 ,
									  src.dept_name	 ,
									  src.insert_user ,
									  src.insert_date,
									  src.update_user,
									  src.update_date
									  
							   );
							   
	/* Delete ATS entries for NOR1 and create ATS entries as defined */
				Delete from INT_UPLOAD_INTERVAL_SETUP
					where interface_id = vInterface
					  and PMS_resort = r3.resort;
				
				Insert into INT_UPLOAD_INTERVAL_SETUP ( INTERFACE_ID   ,
														MSGTYPE		   ,
                                                        INTERVAL       ,														
														PMS_RESORT	   ,
														RANGE		   ,
														ACTIVE_YN	   ,
														FREQUENCY	   ,
														WHEN_TO_EXECUTE,
														FUTURE		   ,
														DELTA_YN
													   )
													  ( select vInterface  INTERFACE_ID   ,
															   'INVENTORY' MSGTYPE		  ,
															   '0'		   INTERVAL		  ,
															   r3.resort   PMS_RESORT	  ,
															   30		  					,
															   'Y'		   ACTIVE_YN	  ,
															   'D'		   FREQUENCY	  ,
															   '4'		   WHEN_TO_EXECUTE,
															   0		   		  ,
															   'N'		   DELTA_YN
															   from dual
															   UNION
														select vInterface  ,
															   'INVENTORY' ,
															   '0'			,
															   r3.resort   ,
															   vMaxAvail-30		,
															   'Y'		   ,
															   'W'		   ,
															   '1;5'	   ,
															   30		   ,
															   'N'
															   from dual
															   UNION
														select vInterface  ,
															   'INVENTORY' ,
															   '0'			,
															   r3.resort   ,
															   1		   ,
															   'Y'		   ,
															   'D'		   ,
															   '6'		   ,
															   vMaxAvail-1 ,
															   'N'
															   from dual
															   UNION
														select vInterface  ,
															   'BLOCK' 		,
															   '10'			,
															   r3.resort   ,
															   0		   ,
															   'Y'		   ,
															   'I'		   ,
                                                               ''          ,
															   vMaxAvail-1 ,
															   'Y'
															   from dual
													   );

	/* Update the max window of availability to what is defined in vMaxAvail (180 days as the default) */
				
				UPDATE INT_CC_DEFAULTS
					set		default_ext_value = 'DAY'
					where 	interface_id = vInterface
					  and	resort = r3.resort
					  and	conversion_code = 'AVAILABILITY_TYPE';
				
				UPDATE INT_CC_DEFAULTS
					set		default_ext_value = vMaxAvail
					where 	interface_id = vInterface
					  and	resort = r3.resort
					  and 	conversion_code = 'MAX_AVAIL_IN_ADVANCE';
	/* Update OXI Interface Default for Trace Warning to NOR1 Trace Department */
	
					UPDATE INT_CC_DEFAULTS
					set default_pms_value = '*NOR1'
					where interface_id = vInterface 
					and	resort = r3.resort
					and conversion_code = 'TRACE_WARNING';
					
	/* Identify if the Nor1 preferred XML schema version is available */
				Begin
					For r4 in (
						select qry.xmltype,
							   qry.Version,
							   tbl.class
						from int_xml_schema_version tbl,
						(SELECT   xmltype,
								 MIN(nor1_version) Version
						FROM     (SELECT *
								 FROM    (SELECT 'RESERVATION' xmltype,
												 'V5'          nor1_version
										 FROM    dual
										 UNION
										 SELECT 'PROFILE'		,
												'V5'
										 FROM   dual
										 UNION
										 SELECT 'ALLOTMENT'		,
												'V5'	
										 FROM   dual
										 UNION	
										 SELECT 'RTAV'			,
												'V4'
										 FROM   dual
										 UNION
										 SELECT 'RESULT'		,
												'V3'
										 FROM   dual
										 UNION
										 SELECT 'MESSAGEREQUEST',
												'V3'
										 FROM   dual
										 UNION
										 SELECT 'STAY'			,
												'V4'
										 FROM   dual
										 UNION
										 SELECT 'PACKAGES'		,
												'V3'
										 FROM   dual
										 )
								  
								  UNION
								  
								  SELECT *
								  FROM   (SELECT  xmltype,
												  MAX(version) max_version
										 FROM     int_xml_schema_version
										 WHERE    xmltype IN ('RESERVATION',
															  'PROFILE'    ,
															  'RTAV'       ,
															  'RESULT'     ,
															  'STAY'	   ,
															  'PACKAGES'   ,
															  'MESSAGEREQUEST')
										 GROUP BY xmltype
										 )
								 )
						GROUP BY xmltype) qry
						where tbl.xmltype = qry.xmltype
						  and tbl.Version = qry.version )

	/*Loop through the XML schema types and set the preferred Nor1 version or if not available,
	set the highest available version*/
					Loop
					
						MERGE
						INTO         resort_xml_schema tgt
						USING        (SELECT vInterface                   	  interfaceid,
											 r3.resort                    	  resort     ,
											 r4.xmltype	                	  xmltype    ,
											 r4.class						  class      ,
											 r4.version                    	  version
									 FROM    dual
									 )
									 src
						ON (
												  tgt.resort      = src.resort
									 AND          tgt.interfaceid = src.interfaceid
									 AND          tgt.xmltype     = src.xmltype
									 )
						WHEN MATCHED THEN
						UPDATE
						SET              tgt.class   = src.class,
										 tgt.version = src.version
						WHEN NOT MATCHED THEN
						INSERT
							   (
									  tgt.interfaceid,
									  tgt.resort     ,
									  tgt.xmltype    ,
									  tgt.class      ,
									  tgt.version
							   )
							   VALUES
							   (
									  src.interfaceid,
									  src.resort     ,
									  src.xmltype    ,
									  src.class      ,
									  src.version
							   );
					END LOOP;
				END;
			END;
		END LOOP;
	END;

	be.set_config_change_ts();
	otm.save();

	Exception
		When Others then dbms_output.put_line('Error ');
	otm.rollback();	
	
END;
/