CsPRjPWL

1CREATE OR REPLACE PACKAGE BODY on_demand_catchup IS
2-- (C) 2005 Pilat Media Ltd.
3-- All Rights Reserved.
4--
5-- Pilat Media Ltd considers all information contained in or disclosed by this
6-- script confidential and proprietary. Pilat Media Ltd reserves the right to
7-- use this design in other projects without reference to the recipient.
8--
9-- By accepting this material, the recipient agrees that this material and
10-- the information contained therein will be held in confidence and in trust
11-- and will not be copied or disclosed in whole or in part to any third party.
12--
13-- The following code is managed and updated by Pilat Media Ltd.
14/************************************************************************
15 * PLEASE DO NOT TOUCH                                                  *
16 *                                                                      *
17 * $Revision: 300406 $  (Source Safe latest version)                         *
18 * $Author: imanzhula $   (last Source Safe author)                       *
19 * $Date: 2016-12-01 06:26:09 +0000 (Thu, 01 Dec 2016) $  (last update)                              *
20* $Product Version: 6.04.11.34.05 $ *
21 *                                                                      *
22 * $Nokeywords: $ (no keyword expansion for all keywords that follow )  *
23 ************************************************************************
24 ------------------------------------------------------------------
25 When        Version     Tracker         Who             What and Why
26 ------------------------------------------------------------------
27 08/12/2012  1           365943025       Lior Sherban    Created
28 07/01/2013              365943025       Lior Sherban    Added create update delete SP
29 09/01/2013              365943025       Lior Sherban    Fixed create_offer_item
30 09/01/2013              365943025       Lior Sherban    Added delete_hash_item
31 14/01/2013              365943037    	 Lior Sherban    Added insert_catchup_slot_item, delete_catchup_slot_item
32 30/01/2013              365943040       IVoron    		 Fixed create_offer_item (v_vod_start_date, v_vod_start_time)
33 28/04/2013              367180001    	 Lior Sherban    Changed several parts to correspond fixes in Programme Amendment Notifications Workflows
34 18/10/2013              371132010    	 AMakhno	   	 Changed create_offer_item: CR371132 Non Linear Hierarchy - Material Inheritance
35 23/10/2013              371132010    	 AMakhno	   	 Changed create_offer_item: CR371132 Non Linear Hierarchy - Material Inheritance
36 30/10/2013              381634001    	 Vova Pechernyi  Changed delete_catchup_slot_item: added clearing slot restricted date calculation rule
37 19/03/2014              385218003    	 Vova Pechernyi  Added batch_refresh_on_demand_avail, refresh_on_demand_avail_window
38 02/04/2014              385218009       Lior Sherban    Fixed refresh_on_demand_avail_window
39 08/04/2014  			 378686001    	 VSeminov		 get_auto_catchup_result: added
40 19/05/2014  			 378685012    	 MFilippov		 Changed auto_catchup_offer_generation: added i_proceed_skip_errors_mode
41 08/06/2014              378685019       Vova Pechernyi	 Changed refresh_avail_windows: passed slot_id to business rule
42 16/06/2014				 378686008		 VSeminov		 changed auto_catchup_offer_generation: correct version_id are now taken
43 17/06/2014				 378686008		 VSeminov		 auto_catchup_offer_generation: i_offer_name now null
44 17/06/2014				 378686008		 VSeminov		 deleted raise error
45 19/06/2014 			 378686010       VSeminov		 auto_catchup_offer_generation: changed message text
46 20/06/2014 			 378686011       VSeminov		 auto_catchup_offer_generation: fixed messages
47 08/07/2014 			 388900001       Vova Pechernyi	 Changed batch_refresh_on_demand_avail: added vacated slots to process
48 09/09/2015 			 403824002       Mir Filippov	 Changed auto_catchup_offer_generation: fixed extract rows
49 14/09/2015				 403824002		 VSeminov		 refresh_avail_windows: added deleting of all duplicates
50 29/01/2016              397297002       IManzhula       Changed insert_catchup_slot_item, refresh_on_demand_avail_window: added parameters
51 03/02/2016				 397297001 	  	 VSeminov		 purge_auto_catchup_offers: added
52 03/02/2016				 408284001		 VSeminov		 auto_catchup_offer_generation: changed - added offer type, offer default
53 03/02/2016              397297002       IManzhula       Changed auto_catchup_offer_generation: added airing category, status update, checks
54 05/02/2016              400804001       IManzhula       Added logic for tracking schedule changes
55 09/02/2016				 397297007       VSeminov		 purge_auto_catchup_offers: added
56 10/02/2016              397297007       IManzhula       Changed catchup_offer_generation to get proper restrictions
57 15/02/2016              400804001       IManzhula       Changed processing_slot_changes: fixes in tracking schedule changes
58 23/02/2016              408956001       IManzhula       Changed catch-up version assignment to most suitable selection
59 11/03/2016              409701001       IManzhula       Changed auto_catchup_offer_generation: added usage of slot property
60 14/03/2016              400804001       IManzhula       Changed apply_catchup_amend_action to use autonomous transaction in all places
61 31/03/2016              397715004       IManzhula       Changed catchup_offer_generation: added check for slots without purchases
62 01/04/2016              400804007       IManzhula       Changed apply_catchup_amend_action:performance fix for multi instances
63 04/04/2016              397715004       IManzhula       Changed catchup_offer_generation: moved check to on_demand_validations
64 04/04/2016              410366001    	 Vova Pechernyi  Added batch_refresh_on_demand_avail by Programme
65 11/04/2016              410267001    	 Vova Pechernyi  Improved performance in batch_refresh_on_demand_avail
66 12/04/2016              397715004       IManzhula       Changed apply_catchup_amend_action: fix for multiple instances and deleted slots
67 15/04/2016              410267001       Vova Pechernyi  Changed batch_refresh_on_demand_avail: fixed case of existing multiple avail windows
68 26/04/2016				 410531001		 AOliinyk		 Changed catchup_offer_generation: added skipCreationPrevProgAirWithin check 
69 29/04/2016              400804007       IManzhula       Added v_offer_multi_deletion to remove duplicate Offer processing
70 10/06/2016              410813001       Vova Pechernyi  Changed DCR calling
71 03/07/2016              410519001       OSalomon        Changed catchup_offer_generation
72 13/07/2016              410519001       OSalomon        Changed catchup_offer_generation
73 28/09/2016              415265001       IManzhula       Changed processing linear changes: complex fix
74 13/10/2016              415793001       Vova Pechernyi  Changed apply_catchup_amend_action: simple fix
75 03/11/2016              416401001       IManzhula       Changed processing_slot_changes: fixed Rule and Offer Item detection
76 07/11/2016              416406001       IManzhula       Changed processing_slot_changes: fixed Rule search based on dates
77 09/11/2016              416280001       IManzhula       Changed catchup_offer_generation, apply_catchup_amend_action: fixed check for skipped slots and multi-instances
78 21/11/2016              416904001       IManzhula       Changed apply_catchup_amend_action: delete Offer multi-instances - consider within_offer_days
79 24/11/2016	         416905001       BKahana	 Changed apply_catchup_amend_action to consider catchup_offer_creation_mode 
80 30/11/2016              416280001       IManzhula       Changed processing_slot_changes, apply_catchup_amend_action: fixed redundant Offers creation
81 ------------------------------------------------------------------*/
82
83v_offers_hash 				on_demand_catchup_util.t_offers_hash;
84v_auto_catchup_offers_hash	on_demand_catchup_util.t_auto_catchup_offers_hash;
85
86k_process_slot_changes_inttyp CONSTANT interface_parameter.interface_type%TYPE := '833';
87k_process_slot_changes_errmsg CONSTANT message.message_code%TYPE := 616950;
88v_process_slot_changes_errlog  t.t_io_file_log_element;
89v_current_sched_proc_log_id schedule_process_log.schedule_process_log_id%TYPE;
90v_current_slot_details VARCHAR2(2000);
91v_current_change_type table_line.table_description%TYPE;
92TYPE t_offfer_multi_deletion IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
93v_offer_multi_deletion t_offfer_multi_deletion;
94TYPE t_multi_instance_check is table of boolean index by PLS_INTEGER;
95v_multi_instance_check t_multi_instance_check;
96
97FUNCTION get_offers(i_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
98	RETURN on_demand_catchup_util.t_offers_rec
99	PIPELINED IS
100BEGIN
101	FOR i IN 1 .. v_offers_hash(i_session_id).count
102	LOOP
103		PIPE ROW(v_offers_hash(i_session_id) (i));
104	END LOOP;
105	RETURN;
106END get_offers;
107
108FUNCTION get_auto_catchup_result(i_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
109	RETURN on_demand_catchup_util.t_auto_catchup_offers_rec
110	PIPELINED IS
111BEGIN
112	FOR i IN 1 .. v_auto_catchup_offers_hash(i_session_id).count
113	LOOP
114		PIPE ROW(v_auto_catchup_offers_hash(i_session_id) (i));
115	END LOOP;
116	RETURN;
117END get_auto_catchup_result;
118
119PROCEDURE Log_Processing_Errors IS
120BEGIN
121	IF v_process_slot_changes_errlog.COUNT > 0 THEN	
122		iface.log_io_action(i_interface_type => k_process_slot_changes_inttyp,
123							i_message_type => 'E',
124							i_message_code   => k_process_slot_changes_errmsg,
125							i_client_info => 'Check Elements for details');	
126		iface.log_io_elements(i_io_file_log_element => v_process_slot_changes_errlog);
127	END IF;
128END Log_Processing_Errors;
129
130PROCEDURE Log_Processing_Error(i_error_message IN VARCHAR2) IS
131	v_error_no NUMBER;
132BEGIN
133	IF v_current_sched_proc_log_id > 0 THEN
134		v_error_no := v_process_slot_changes_errlog.COUNT;
135		v_process_slot_changes_errlog(v_error_no).object_type := 'SPL';
136		v_process_slot_changes_errlog(v_error_no).broadcaster_reference := v_current_sched_proc_log_id;
137		v_process_slot_changes_errlog(v_error_no).error_text := i_error_message;
138	END IF;
139END Log_Processing_Error;
140
141PROCEDURE delete_offer_item(i_parent_slot_id  IN NUMBER,
142							i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE DEFAULT NULL,
143							o_deleted_records OUT SYS_REFCURSOR) IS
144
145	CURSOR c_offer_items IS
146		SELECT cso.offer_item_id,
147			   cso.catchup_slot_offer_item_id,
148			   vos.offer_id,
149			   vos.offer_name,
150			   vp.platform_name,
151			   vp.vod_platform_id
152		FROM   catchup_slot_offer_item_view cso
153		INNER  JOIN vod_item_schedule vit
154		ON     vit.item_schedule_id = cso.offer_item_id
155		INNER  JOIN vod_offer_schedule_item vosi
156		ON     vosi.item_schedule_id = vit.item_schedule_id
157		INNER  JOIN vod_offer_schedule vos
158		ON     vos.offer_id = vosi.offer_id
159		INNER  JOIN vod_platform vp
160		ON     vp.vod_platform_id = vos.platform_id
161		WHERE  cso.slot_id = i_parent_slot_id;
162
163	v_index           NUMBER(5) := 0;
164	v_temp_session_id NUMBER(9);
165BEGIN
166	IF (i_temp_session_id > 0)
167	THEN
168		v_temp_session_id := i_temp_session_id;
169	ELSE
170		v_temp_session_id := session_util.get_temp_session_id();
171	END IF;
172	--v_offers_hash := NEW t_offers_hash;
173	v_offers_hash(v_temp_session_id) := on_demand_catchup_util.t_offers_rec();
174
175	FOR v_offer_item IN c_offer_items
176	LOOP
177		v_index := v_index + 1;
178		v_offers_hash(v_temp_session_id).extend;
179		v_offers_hash(v_temp_session_id)(v_index).offer_id := v_offer_item.offer_id;
180		v_offers_hash(v_temp_session_id)(v_index).offer_name := v_offer_item.offer_name;
181		v_offers_hash(v_temp_session_id)(v_index).service_id := v_offer_item.vod_platform_id;
182		v_offers_hash(v_temp_session_id)(v_index).service_name := v_offer_item.platform_name;
183		on_demand_schedule.delete_item_offer(i_offer_item_id => v_offer_item.offer_item_id);
184		on_demand_catchup_internal.delete_catchup_slot_item(i_catchup_slot_item_id => v_offer_item.catchup_slot_offer_item_id);
185	END LOOP;
186	OPEN o_deleted_records FOR
187		SELECT offers.offer_id,
188			   offers.offer_name,
189			   offers.service_id,
190			   offers.service_name,
191			   offers.succeeded
192		FROM   TABLE(get_offers(v_temp_session_id)) offers;
193END delete_offer_item;
194
195PROCEDURE update_offer_item(i_parent_slot_id  IN NUMBER,
196							i_session_id      IN glx_tmp_session_generic_select.temp_session_id%TYPE DEFAULT NULL,
197							o_updated_records OUT SYS_REFCURSOR) IS
198	CURSOR c_offer_items IS
199		SELECT cso.offer_item_id,
200			   cso.catchup_slot_offer_item_id,
201			   vos.offer_id,
202			   vos.offer_name,
203			   vp.platform_name,
204			   vp.vod_platform_id,
205			   vit.version_id,
206			   vos.offer_defaults_id,
207			   vp.channel_code
208		FROM   catchup_slot_offer_item_view cso
209		INNER  JOIN vod_item_schedule vit
210		ON     vit.item_schedule_id = cso.offer_item_id
211		INNER  JOIN vod_offer_schedule_item vosi
212		ON     vosi.item_schedule_id = vit.item_schedule_id
213		INNER  JOIN vod_offer_schedule vos
214		ON     vos.offer_id = vosi.offer_id
215		INNER  JOIN vod_platform vp
216		ON     vp.vod_platform_id = vos.platform_id
217		WHERE  cso.slot_id = i_parent_slot_id;
218
219	CURSOR c_parent_slot IS
220		SELECT sib.schedule_date,
221			   sib.duration,
222			   sib.purchase_id,
223			   sib.version_id,
224			   sib.template_id,
225			   sib.event_profile_id,
226			   sib.start_time
227		FROM   schedule_item sib
228		WHERE  sib.schedule_item_id = i_parent_slot_id;
229
230	v_parent_slot          c_parent_slot%ROWTYPE;
231	v_old_template_item_id schedule_item.template_id%TYPE;
232	v_old_schedule_date    schedule_item.schedule_date%TYPE;
233	v_new_schedule_time    schedule_item.start_time%TYPE;
234	v_new_duration         schedule_item.duration%TYPE;
235	v_channel_code         channel.channel_code%TYPE;
236	v_tmp_number           NUMBER(15);
237	v_start_seq_num        tx_schedule.sequence_no%TYPE;
238	v_end_seq_num          tx_schedule.sequence_no%TYPE;
239	v_change_tx_schedule   t.logical%TYPE;
240
241	v_slot_ids               CLOB;
242	v_found_check_notes      t.logical%TYPE;
243	v_platform_name          vod_platform.platform_name%TYPE;
244	v_slot_offer_info_t      on_demand_catchup_util.t_offer_info;
245	v_offer_info             on_demand_catchup_util.r_offer_info;
246	v_use_offer_info         BOOLEAN;
247	v_can_use_business_rules vod_offer_type.can_use_business_rules%TYPE;
248	v_vod_start_date         vod_item_schedule.start_date%TYPE;
249	v_vod_start_time         vod_item_schedule.start_time%TYPE;
250	v_vod_start              DATE;
251	v_vod_end                DATE;
252	v_vod_end_date           vod_item_schedule.end_date%TYPE;
253	v_vod_end_time           vod_item_schedule.end_time%TYPE;
254	v_is_to_be_scheduled     BOOLEAN;
255	v_can_use_br             vod_offer_type.can_use_business_rules%TYPE;
256	v_vod_offer_type_id      vod_offer_type.vod_offer_type_id%TYPE;
257	v_version_id             version.version_id%TYPE;
258	v_has_right_type         t.logical%TYPE;
259	v_temp_session_id        NUMBER(9);
260
261	v_index NUMBER(5) := 0;
262BEGIN
263	IF (i_session_id > 0)
264	THEN
265		v_temp_session_id := i_session_id;
266	ELSE
267		v_temp_session_id := session_util.get_temp_session_id();
268	END IF;
269	session_util.set_view_session_id(v_temp_session_id);
270
271	v_offers_hash(v_temp_session_id) := on_demand_catchup_util.t_offers_rec();
272
273	FOR v_offer_item IN c_offer_items
274	LOOP
275		v_offers_hash(i_session_id).extend;
276		v_index := v_index + 1;
277
278		OPEN c_parent_slot;
279		FETCH c_parent_slot
280			INTO v_parent_slot;
281		CLOSE c_parent_slot;
282		v_offers_hash(i_session_id).extend;
283		v_index := v_index + 1;
284
285		on_demand_business_rules.call_catchup_business_rules(i_slot_ids => TO_CHAR(i_parent_slot_id), i_target_platform_id => v_offer_item.vod_platform_id, o_slot_offer_info => v_slot_offer_info_t);
286		IF v_slot_offer_info_t.exists(i_parent_slot_id)
287		THEN
288			v_use_offer_info := TRUE;
289			v_offer_info     := v_slot_offer_info_t(i_parent_slot_id);
290		ELSE
291			v_use_offer_info := FALSE;
292			v_offer_info     := NULL;
293		END IF;
294		v_is_to_be_scheduled := TRUE;
295		IF v_use_offer_info
296		THEN
297			g.debug('in update v_use_offer_info = true');
298			v_is_to_be_scheduled := v_offer_info.is_to_be_scheduled;
299			IF (v_offer_info.is_to_be_scheduled)
300			THEN
301				g.debug('in update v_is_to_be_scheduled = true');
302			ELSE
303				g.debug('in update v_is_to_be_scheduled = false');
304			END IF;
305			g.debug('in update ', 'v_offer_info.lag_seconds', v_offer_info.lag_seconds, 'v_offer_info.duration', v_offer_info.duration);
306			v_vod_start      := plan_timezones.get_calendar_date_time(i_schedule_date => v_parent_slot.schedule_date, i_start_time => v_parent_slot.start_time +
307																					   v_offer_info.lag_seconds);
308			v_vod_start_date := galaxy_util.glx_to_char(v_vod_start);
309			v_vod_start_time := TO_NUMBER(TO_CHAR(v_vod_start, 'SSSSS'));
310			IF v_offer_info.duration != 0
311			THEN
312				v_vod_end := v_vod_start + (v_offer_info.duration / (24 * 3600));
313			ELSE
314				v_vod_end := on_demand_schedule_util.get_offer_end_by_duration(i_offer_defaults_id => v_offer_item.offer_defaults_id, i_start_date => v_vod_start);
315
316			END IF;
317		ELSE
318			v_vod_start := plan_timezones.get_calendar_date_time(i_schedule_date => v_parent_slot.schedule_date, i_start_time => v_parent_slot.start_time);
319			v_vod_end   := on_demand_schedule_util.get_offer_end_by_duration(i_offer_defaults_id => v_offer_item.offer_defaults_id, i_start_date => v_vod_start);
320			g.debug('in update v_use_offer_info = false');
321		END IF;
322		v_vod_end_date := galaxy_util.glx_to_char(v_vod_end);
323		v_vod_end_time := TO_NUMBER(TO_CHAR(v_vod_end, 'SSSSS'));
324
325		IF (v_offer_item.version_id IS NULL)
326		THEN
327			SELECT tx_suitable_versions(p.title_id, 0, NULL, 0, 'S', v_offer_item.channel_code, galaxy_util.glx_to_char(v_vod_start_date), 0, 1, NULL)
328			INTO   v_version_id
329			FROM   purchase p
330			WHERE  p.purchase_id = v_parent_slot.purchase_id;
331		ELSE
332			v_version_id := v_offer_item.version_id;
333		END IF;
334
335		v_offers_hash(v_temp_session_id)(v_index).offer_id := v_offer_item.offer_id;
336		v_offers_hash(v_temp_session_id)(v_index).offer_name := v_offer_item.offer_name;
337		v_offers_hash(v_temp_session_id)(v_index).service_id := v_offer_item.vod_platform_id;
338		v_offers_hash(v_temp_session_id)(v_index).service_name := v_offer_item.platform_name;
339		on_demand_schedule.batch_update_item_offer(i_offer_ids => v_offer_item.offer_id,
340													i_temp_session_id => v_temp_session_id,
341													i_item_purchase_id => v_parent_slot.purchase_id,
342													i_item_version_id => v_version_id,
343													i_offer_start_date => v_vod_start_date,
344													i_offer_start_time => v_vod_start_time,
345													i_offer_end_date => v_vod_end_date,
346													i_offer_end_time => v_vod_end_time,
347													i_apply_dates_policy => k_true,
348													o_update_aborted => v_tmp_number);
349		v_offers_hash(v_temp_session_id)(v_index).succeeded := v_tmp_number * (-1) + 1; --opposite of true or false
350
351	END LOOP;
352
353	OPEN o_updated_records FOR
354		SELECT offers.offer_id,
355			   offers.offer_name,
356			   offers.service_id,
357			   offers.service_name,
358			   offers.succeeded
359		FROM   TABLE(get_offers(v_temp_session_id)) offers;
360
361END update_offer_item;
362
363PROCEDURE create_offer_item(i_slot_ids       IN CLOB,
364							i_platform_id    IN vod_platform.vod_platform_id%TYPE DEFAULT 0,
365							i_session_id     IN glx_tmp_session_generic_select.temp_session_id%TYPE DEFAULT NULL,
366							o_created_offers OUT SYS_REFCURSOR) IS
367	v_slot_ids          CLOB := RTRIM(i_slot_ids, ';');
368	v_slot_offer_info_t on_demand_catchup_util.t_offer_info;
369	v_use_offer_info          BOOLEAN;
370	v_offer_info              on_demand_catchup_util.r_offer_info;
371	v_child_offer_item_id vod_item_schedule.item_schedule_id%TYPE;
372	CURSOR c_services IS
373		SELECT vp.vod_platform_id,
374			   ot.can_use_business_rules,
375			   ot.default_offer_defaults_id,
376			   vp.channel_code
377		FROM   vod_platform vp
378		INNER  JOIN offer_type ot
379		ON     ot.offer_type_id = vp.default_offer_type_id
380		WHERE  vp.marked_as_deleted = k_false
381		AND    (i_platform_id > 0 AND vp.vod_platform_id = i_platform_id)
382		OR     (i_platform_id = 0 AND
383			  vp.channel_code IN (SELECT cl.slave_channel_code AS channel_code
384									FROM   TABLE((galaxy_util.split_str(v_slot_ids, ';')))
385									INNER  JOIN schedule_item si
386									ON     si.schedule_item_id = TO_NUMBER(column_value)
387									INNER  JOIN channel_link cl
388									ON     cl.master_channel_code = si.channel_code
389									AND    cl.link_type = 'O'));
390	CURSOR c_slots IS
391		SELECT TO_NUMBER(column_value) AS slot_id, sib.channel_code, sib.schedule_date, sib.start_time, sib.purchase_id, sib.slot_name
392		FROM   TABLE(galaxy_util.split_str(v_slot_ids, ';'))
393		INNER JOIN schedule_item_base_view sib ON (sib.schedule_item_id = TO_NUMBER(column_value));
394	CURSOR c_offer(i_i_offer_id IN vod_offer_schedule.offer_id%TYPE) IS
395		SELECT vos.offer_id,
396			   vos.offer_name,
397			   vp.vod_platform_id,
398			   vp.platform_name,
399			   vis.item_schedule_id
400		FROM vod_item_schedule vis
401		INNER JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = vis.item_schedule_id
402		INNER JOIN vod_offer_schedule vos ON vos.offer_id = vosi.offer_id
403		INNER JOIN vod_platform vp ON vp.vod_platform_id = vos.platform_id
404		WHERE vos.offer_id = i_i_offer_id;
405	v_offer c_offer%ROWTYPE;
406	v_index NUMBER(5) := 0;
407	v_vod_start_date         vod_item_schedule.start_date%TYPE;
408	v_vod_start_time         vod_item_schedule.start_time%TYPE;
409	v_vod_start              DATE;
410	v_vod_end                DATE;
411	v_vod_end_date           vod_item_schedule.end_date%TYPE;
412	v_vod_end_time           vod_item_schedule.end_time%TYPE;
413	v_is_to_be_scheduled     BOOLEAN := TRUE;
414	v_version_id 			 version.version_id%TYPE;
415	v_purchase_ids			 t.number_table;
416	v_version_ids			 t.number_table;
417	v_slot_ids_table		 t.number_table;
418
419 	v_created_offer_ids t_number_table;
420	v_created_item_ids t_number_table;
421BEGIN
422	session_util.set_view_session_id(i_view_temp_session_id => i_session_id);
423	v_offers_hash(i_session_id) := on_demand_catchup_util.t_offers_rec();
424	FOR v_service IN c_services
425	LOOP
426		IF v_service.can_use_business_rules = k_true
427		THEN
428			on_demand_business_rules.call_catchup_business_rules(i_slot_ids => v_slot_ids, i_target_platform_id => v_service.vod_platform_id, o_slot_offer_info => v_slot_offer_info_t);
429		END IF;
430		FOR v_slot IN c_slots
431		LOOP
432			IF v_slot_offer_info_t.EXISTS(v_slot.slot_id) THEN
433				v_use_offer_info := TRUE;
434				v_offer_info := v_slot_offer_info_t(v_slot.slot_id);
435			ELSE
436				v_use_offer_info := FALSE;
437				v_offer_info := NULL;
438			END IF;
439
440			IF v_use_offer_info
441			THEN
442				g.debug('in update v_use_offer_info = true');
443				v_is_to_be_scheduled := v_offer_info.is_to_be_scheduled;
444				IF (v_offer_info.is_to_be_scheduled)
445				THEN
446					g.debug('in update v_is_to_be_scheduled = true');
447				ELSE
448					g.debug('in update v_is_to_be_scheduled = false');
449				END IF;
450				g.debug('in update ', 'v_offer_info.lag_seconds', v_offer_info.lag_seconds, 'v_offer_info.duration', v_offer_info.duration);
451				v_vod_start      := plan_timezones.get_calendar_date_time(i_schedule_date => v_slot.schedule_date, i_start_time => v_slot.start_time +
452																						   v_offer_info.lag_seconds);
453
454				IF v_offer_info.duration != 0
455				THEN
456					v_vod_end := v_vod_start + (v_offer_info.duration / (24 * 3600));
457				ELSE
458					v_vod_end := on_demand_schedule_util.get_offer_end_by_duration(i_offer_defaults_id => v_service.default_offer_defaults_id, i_start_date => v_vod_start);
459				END IF;
460			ELSE
461				v_vod_start := plan_timezones.get_calendar_date_time(i_schedule_date => v_slot.schedule_date, i_start_time => v_slot.start_time);
462				v_vod_end   := on_demand_schedule_util.get_offer_end_by_duration(i_offer_defaults_id => v_service.default_offer_defaults_id, i_start_date => v_vod_start);
463				g.debug('in update v_use_offer_info = false');
464			END IF;
465
466			IF(v_is_to_be_scheduled) THEN
467				v_vod_end_date := galaxy_util.glx_to_char(v_vod_end);
468				v_vod_end_time := TO_NUMBER(TO_CHAR(v_vod_end, 'SSSSS'));
469				v_vod_start_date := galaxy_util.glx_to_char(v_vod_start);
470				v_vod_start_time := TO_NUMBER(TO_CHAR(v_vod_start, 'SSSSS'));
471
472				SELECT tx_suitable_versions(p.title_id, 0, NULL, 0, 'S', v_service.channel_code, v_vod_start_date, 0, 1, NULL)
473				INTO   v_version_id
474				FROM   purchase p
475				WHERE  p.purchase_id = v_slot.purchase_id;
476
477				v_purchase_ids(1) := v_slot.purchase_id;
478				v_version_ids(1) := v_version_id;
479				SELECT v_slot.slot_id BULK COLLECT
480				INTO v_slot_ids_table
481				FROM dual;
482				on_demand_group_schedule.create_offers(i_create_offers_mode => on_demand_group_schedule.k_create_offers_mode_hierarchy,
483												i_temp_session_id => i_session_id,
484												i_service_id => v_service.vod_platform_id,
485												i_slot_ids => v_slot_ids_table,
486												i_hierarchy_date => v_vod_start_date,
487												i_offer_name => v_slot.slot_name,
488												i_offer_creation_mode => on_demand_schedule.k_one_offer_each_item,
489												i_offer_start_date => galaxy_util.glx_to_date(v_vod_start_date),
490												i_offer_start_time => galaxy_util.glx_to_timespan(v_vod_start_time),
491												i_offer_end_date => galaxy_util.glx_to_date(v_vod_end_date),
492												i_offer_end_time => galaxy_util.glx_to_timespan(v_vod_end_time),
493												i_purchase_ids => v_purchase_ids,
494												i_version_ids => v_version_ids,
495												i_apply_dates_policy => k_true,
496												o_created_offer_ids => v_created_offer_ids,
497												o_created_item_ids => v_created_item_ids
498												);
499			END IF;
500
501			IF(v_created_offer_ids.count > 0) THEN
502				v_offers_hash(i_session_id).extend;
503				OPEN c_offer(v_created_offer_ids(1));
504				FETCH c_offer INTO v_offer;
505				CLOSE c_offer;
506				v_index := v_index + 1;
507				v_offers_hash(i_session_id)(v_index).offer_Id := v_offer.offer_id;
508				v_offers_hash(i_session_id)(v_index).offer_Name := v_offer.offer_name;
509				v_offers_hash(i_session_id)(v_index).service_id := v_offer.vod_platform_id;
510				v_offers_hash(i_session_id)(v_index).service_name := v_offer.platform_name;
511				v_offers_hash(i_session_id)(v_index).succeeded := CASE WHEN v_created_offer_ids.count > 0 AND v_created_offer_ids(1) > 0 THEN k_true ELSE k_false END; --opposite of true or false
512			END IF;
513		END LOOP;
514	END LOOP;
515
516	OPEN o_created_offers FOR
517		SELECT offers.offer_Id,
518			   offers.offer_Name,
519			   offers.service_id,
520			   offers.service_name,
521			   offers.succeeded
522		FROM TABLE(get_offers(i_session_id)) offers;
523
524END create_offer_item;
525
526PROCEDURE delete_hash_item    ( i_session_id IN glx_tmp_session_generic_select.temp_session_id%TYPE)
527IS
528BEGIN
529	v_offers_hash.delete(i_session_id);
530END delete_hash_item;
531
532PROCEDURE insert_catchup_slot_item(i_slot_id       IN catchup_slot_offer_item.slot_id%TYPE,
533								   i_offer_item_id IN catchup_slot_offer_item.offer_item_id%TYPE,
534								   i_catchup_mode IN catchup_slot_offer_item.catchup_mode%TYPE DEFAULT NULL,
535								   i_original_channel_code IN catchup_slot_offer_item.original_channel_code%TYPE DEFAULT NULL,
536								   i_original_schedule_date IN catchup_slot_offer_item.original_schedule_date%TYPE DEFAULT NULL) IS
537BEGIN
538	IF(i_slot_id > 0 AND i_offer_item_id > 0) THEN
539		on_demand_catchup_internal.insert_catchup_slot_item(i_slot_id => i_slot_id,
540															i_offer_item_id => i_offer_item_id,
541															i_catchup_mode => i_catchup_mode,
542															i_original_channel_code => i_original_channel_code,
543															i_original_schedule_date => i_original_schedule_date);
544	END IF;
545END insert_catchup_slot_item;
546
547
548PROCEDURE delete_catchup_slot_item(i_offer_item_id IN catchup_slot_offer_item.offer_item_id%TYPE) IS
549	CURSOR c_catchup IS
550		SELECT cci.catchup_slot_offer_item_id
551		  FROM catchup_slot_offer_item cci
552		 WHERE cci.offer_item_id = i_offer_item_id;
553
554	v_start_date_calc_rule_id date_calculation_rule.date_calculation_rule_id%TYPE;
555	v_end_date_calc_rule_id   date_calculation_rule.date_calculation_rule_id%TYPE;
556	v_update_aborted          t.logical%TYPE;
557BEGIN
558	FOR v_rec IN c_catchup LOOP
559		on_demand_catchup_internal.delete_catchup_slot_item(i_catchup_slot_item_id => v_rec.catchup_slot_offer_item_id);
560	END LOOP;
561
562	SELECT NVL(vis.start_date_calc_rule_id, 0),
563		   NVL(vis.end_date_calc_rule_id, 0)
564	  INTO v_start_date_calc_rule_id, v_end_date_calc_rule_id
565	  FROM vod_item_schedule vis
566	 WHERE vis.item_schedule_id = i_offer_item_id;
567
568	IF v_start_date_calc_rule_id > 0 OR v_end_date_calc_rule_id > 0 THEN
569		on_demand_schedule.batch_update_item_offer(i_offer_item_ids     => i_offer_item_id,
570												   i_start_date_rule_id => CASE
571																			   WHEN on_demand_schedule_util.is_date_rule_slots_restricted(i_date_calculation_rule_id => v_start_date_calc_rule_id) =
572																					k_true THEN
573																				NULL
574																			   ELSE
575																				k.k_niu_number
576																		   END,
577												   i_end_date_rule_id   => CASE
578																			   WHEN on_demand_schedule_util.is_date_rule_slots_restricted(i_date_calculation_rule_id => v_end_date_calc_rule_id) =
579																					k_true THEN
580																				NULL
581																			   ELSE
582																				k.k_niu_number
583																		   END,
584												   i_perform_checks     => k_false,
585												   o_update_aborted     => v_update_aborted);
586	END IF;
587
588END delete_catchup_slot_item;
589
590PROCEDURE refresh_on_demand_avail_window(
591										 -- Refresh On Demand Avail Window for Planning Slot
592										 i_schedule_item_id IN schedule_item.schedule_item_id%TYPE) IS
593	v_avail_start            DATE;
594	v_avail_end              DATE;
595	v_purchase_id            schedule_item.purchase_id%TYPE;
596	v_deleted_slot           k.logical%TYPE;
597	v_window_exist           k.logical%TYPE := k_false;
598	v_no_restriction         k.logical%TYPE := k_false;
599
600	v_start_date_chk_message VARCHAR2(400);
601	v_end_date_chk_message   VARCHAR2(400);
602	v_lnk_first_chk_message  VARCHAR2(400);
603	v_start_date_chk_result  VARCHAR2(10);
604	v_end_date_chk_result    VARCHAR2(10);
605	v_lnk_first_chk_result   VARCHAR2(10);
606	v_linear_air_chk_message VARCHAR2(400);
607	v_linear_air_chk_result  VARCHAR2(10);
608	v_message                VARCHAR2(400);
609	v_error                  t.logical%TYPE;
610	v_rules_maintenance_guid br_rules_maintenence.guid%TYPE;
611BEGIN
612
613	BEGIN
614		SELECT k_false, si.purchase_id
615		  INTO v_deleted_slot, v_purchase_id
616		  FROM schedule_item_base_view si
617		 WHERE si.schedule_item_id = i_schedule_item_id;
618	EXCEPTION
619		WHEN no_data_found THEN
620			v_deleted_slot := k_true;
621	END;
622
623	IF v_deleted_slot = k_true OR NVL(v_purchase_id, 0) = 0 THEN
624		FOR v_on_demand_avail_window IN (SELECT on_demand_avail_window_id FROM on_demand_avail_window w WHERE w.source_object_type = 'SLOT' AND w.source_object_id = i_schedule_item_id)
625		LOOP
626			on_demand_catchup_internal.delete_on_demand_avail_window(i_on_demand_avail_window_id => v_on_demand_avail_window.on_demand_avail_window_id);		
627		END LOOP;		 
628	ELSE
629		on_demand_business_rules.call_run_based_license_rights(i_purchase_id            => v_purchase_id,
630															   i_schedule_item_id       => i_schedule_item_id,
631															   o_suggested_start_date   => v_avail_start,
632															   o_suggested_end_date     => v_avail_end,
633															   o_message                => v_message,
634															   o_error                  => v_error,
635															   o_start_date_chk_message => v_start_date_chk_message,
636															   o_start_date_chk_result  => v_start_date_chk_result,
637															   o_end_date_chk_message   => v_end_date_chk_message,
638															   o_end_date_chk_result    => v_end_date_chk_result,
639															   o_lnk_first_chk_message  => v_lnk_first_chk_message,
640															   o_lnk_first_chk_result   => v_lnk_first_chk_result,
641															   o_linear_air_chk_message => v_linear_air_chk_message,
642															   o_linear_air_chk_result  => v_linear_air_chk_result,
643															   o_rules_maintenance_guid => v_rules_maintenance_guid);
644
645		IF v_avail_start IS NOT NULL AND v_avail_end IS NOT NULL AND v_avail_start <= v_avail_end THEN
646			v_window_exist := k_true;
647		ELSIF v_avail_start IS NULL AND v_avail_end IS NULL AND v_error = k_false THEN
648			v_no_restriction := k_true;
649		END IF;
650
651		IF v_no_restriction = k_true THEN
652			FOR v_avail_window IN (SELECT w.on_demand_avail_window_id FROM on_demand_avail_window w WHERE w.purchase_id = v_purchase_id AND w.source_object_type = 'SLOT')
653			LOOP
654				on_demand_catchup_internal.delete_on_demand_avail_window(i_on_demand_avail_window_id => v_avail_window.on_demand_avail_window_id);
655			END LOOP;
656		ELSE
657			FOR v_avail_window IN (SELECT w.on_demand_avail_window_id FROM on_demand_avail_window w WHERE w.purchase_id = v_purchase_id AND w.source_object_type = 'SLOT' AND w.source_object_id = i_schedule_item_id)
658			LOOP
659				on_demand_catchup_internal.delete_on_demand_avail_window(i_on_demand_avail_window_id => v_avail_window.on_demand_avail_window_id);
660			END LOOP;
661			
662			on_demand_catchup_internal.insert_on_demand_avail_window(i_source_object_id => i_schedule_item_id,
663																	 i_purchase_id      => v_purchase_id,
664																	 i_start_date       => CASE WHEN v_window_exist = k_true THEN v_avail_start ELSE NULL END,
665																	 i_end_date         => CASE WHEN v_window_exist = k_true THEN v_avail_end ELSE NULL END,
666																	 i_br_name          => NULL,
667																	 i_window_not_exist => ABS(v_window_exist - k_true),
668																	 i_rules_maintenance_guid => v_rules_maintenance_guid);
669		END IF;
670
671	END IF;
672
673END refresh_on_demand_avail_window;
674
675PROCEDURE batch_refresh_on_demand_avail(
676										-- Batch Refresh On Demand Avail Windows for Planning Slots
677										i_start_date  IN DATE,
678										i_end_date    IN DATE,
679										i_channel_ids IN VARCHAR2) IS
680BEGIN
681	FOR v_slots IN (WITH lw AS
682						 (SELECT DISTINCT (si.purchase_id) AS Id
683						   FROM schedule_item_base_simple_view si
684						  WHERE si.channel_code IN (SELECT /*+ cardinality(t 1) */ column_value FROM TABLE(galaxy_util.split_str(i_channel_ids,';')) t)
685							AND si.schedule_date BETWEEN NVL(galaxy_util.glx_to_char(i_start_date),si.schedule_date) AND NVL(galaxy_util.glx_to_char(i_end_date),si.schedule_date)
686							AND on_demand_schedule_util.Is_vod_channel(i_channel_code => si.channel_code) =	k_false)							
687						SELECT si.schedule_item_id
688						  FROM schedule_item_base_simple_view si
689						 WHERE si.purchase_id IN (SELECT Id FROM lw WHERE lw.Id > 0)
690						   AND on_demand_schedule_util.Is_vod_channel(i_channel_code => si.channel_code) = k_false
691						UNION ALL
692						SELECT si.schedule_item_id
693						  FROM schedule_item_base_simple_view si
694						 WHERE si.purchase_id = 0 
695						   AND EXISTS (SELECT Id FROM lw WHERE lw.Id = 0)
696 						   AND si.channel_code IN (SELECT /*+ cardinality(t 1) */ column_value FROM TABLE(galaxy_util.split_str(i_channel_ids,';')) t)
697						   AND si.schedule_date BETWEEN NVL(galaxy_util.glx_to_char(i_start_date), si.schedule_date) AND NVL(galaxy_util.glx_to_char(i_end_date),si.schedule_date)
698						   AND on_demand_schedule_util.Is_vod_channel(i_channel_code => si.channel_code) = k_false)
699	LOOP
700		refresh_on_demand_avail_window(i_schedule_item_id => v_slots.schedule_item_id);
701	END LOOP;
702END batch_refresh_on_demand_avail;
703
704PROCEDURE batch_refresh_on_demand_avail(
705										-- Batch Refresh On Demand Avail Windows for all Planning Slots where Programme is assigned 
706										i_purchase_id IN schedule_item.purchase_id%TYPE) IS
707BEGIN
708	FOR v_slots IN (SELECT si.schedule_item_id
709					  FROM schedule_item_base_simple_view si
710					 WHERE si.purchase_id = i_purchase_id
711					   AND on_demand_schedule_util.Is_vod_channel(i_channel_code => si.channel_code) = k_false)
712    LOOP
713		refresh_on_demand_avail_window(i_schedule_item_id => v_slots.schedule_item_id);
714	END LOOP;
715END batch_refresh_on_demand_avail;
716
717PROCEDURE ins_auto_catchup_offers_hash(i_offer_ids IN t_number_table,
718									   i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE,
719									   i_linear_channel_of_slot IN channel.channel_name%TYPE DEFAULT NULL,
720									   i_slot_date IN schedule_item.schedule_date%TYPE DEFAULT NULL,
721									   i_slot_start_time IN schedule_item.start_time%TYPE DEFAULT NULL,
722									   i_slot_end_time IN schedule_item.start_time%TYPE DEFAULT NULL,
723									   i_slot_name IN schedule_item.slot_name%TYPE DEFAULT NULL,
724									   i_called_from_modification IN t.logical%TYPE DEFAULT k_false)
725IS
726BEGIN
727	IF (i_offer_ids IS NULL OR i_offer_ids IS empty) THEN
728		FOR v_log_message IN (SELECT stragg2_clob(NEW agg_input_clob(l.message_description, ';')) AS error_check_message, l.sub_object_id_2 AS service_id,
729			 						 vp.platform_name AS service_name, l.message_type, l.sub_object_name AS offer_name
730							  FROM glx_tmp_schedule_message_log l
731							  INNER JOIN vod_platform vp ON vp.vod_platform_id = l.sub_object_id_2
732							  WHERE l.temp_session_id = i_temp_session_id
733							  AND l.user_code = USER
734							  AND l.message_type = 'E'
735							  GROUP BY l.sub_object_id_2, vp.platform_name, l.message_type, l.sub_object_name)
736		LOOP
737			g.debug('Writing Message 1','v_log_message.error_check_message',v_log_message.error_check_message);
738			v_auto_catchup_offers_hash(i_temp_session_id).extend;
739			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := CASE WHEN i_called_from_modification = k_false THEN 'Failed to create Catch Up Offer' ELSE 'Failed to update Offer Status' END;
740			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).linear_channel := i_linear_channel_of_slot;
741			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).air_day := galaxy_util.glx_to_date(i_slot_date);
742			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_start_time := tstr(i_slot_start_time);
743			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_end_time := tstr(i_slot_end_time);
744			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_name := i_slot_name;
745			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).service_name := v_log_message.service_name;
746			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := 'On Demand Checks Produced Errors: ' || SUBSTR(v_log_message.error_check_message,1,2000);
747
748		IF v_log_message.message_type = 'E' THEN
749				Log_Processing_Error(i_error_message => 'Failed to ' || CASE WHEN i_called_from_modification = k_false THEN 'create' ELSE 'modify' END || ' Offer ' || v_log_message.offer_name || ' on ' || v_log_message.service_name || '; reason: VOD Checks failed - ' ||v_log_message.error_check_message);
750			END IF;
751		END LOOP;
752	ELSE
753		FOR v_log_message IN (WITH log_errors AS (SELECT dbms_lob.substr(stragg2_clob(NEW agg_input_clob(l.message_description, ';')), 2000, 1) AS error_check_message, l.sub_object_id_2 AS service_id, l.message_type AS result_type, l.sub_object_id AS offer_id, l.sub_object_name AS offer_name
754												  FROM glx_tmp_schedule_message_log l
755												  WHERE l.temp_session_id = i_temp_session_id
756												  AND l.user_code = USER
757												  AND l.message_type = 'E'
758												  GROUP BY l.sub_object_id_2, l.message_type, l.sub_object_id, l.sub_object_name)
759							  SELECT un.error_check_message, un.service_id, vp.platform_name AS service_name, un.result_type, un.offer_id, un.offer_name
760							  FROM vod_platform vp
761							  INNER JOIN
762							  (SELECT error_check_message, service_id, result_type, offer_id, offer_name
763							   FROM log_errors
764							   UNION
765							   SELECT NULL AS error_check_message, vos.platform_id AS service_id, 'I' AS result_type, vos.offer_id, vos.offer_name
766							   FROM vod_offer_schedule vos
767							   WHERE vos.offer_id IN (SELECT column_value FROM table(i_offer_ids)) AND vos.offer_id NOT IN (SELECT offer_id FROM log_errors)) un ON un.service_id = vp.vod_platform_id
768							   AND i_called_from_modification = k_false)
769		LOOP
770			g.debug('Writing Message 2','v_log_message.error_check_message',v_log_message.error_check_message);
771			v_auto_catchup_offers_hash(i_temp_session_id).extend;
772			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := CASE v_log_message.result_type WHEN 'E' THEN 'Failed to create Catch Up Offer' ELSE 'Offer Created' END;
773			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).linear_channel := i_linear_channel_of_slot;
774			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).air_day := galaxy_util.glx_to_date(i_slot_date);
775			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_start_time := tstr(i_slot_start_time);
776			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_end_time := tstr(i_slot_end_time);
777			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_name := i_slot_name;
778			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).service_name := v_log_message.service_name;
779			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := 'On Demand Checks Produced Errors: ' || SUBSTR(v_log_message.error_check_message,1,2000);
780
781		IF v_log_message.result_type = 'E' THEN
782				Log_Processing_Error(i_error_message => 'Failed to ' || CASE WHEN i_called_from_modification = k_false THEN 'create' ELSE 'modify' END || ' Offer ' || v_log_message.offer_name || ' on ' || v_log_message.service_name || '; reason: VOD Checks failed - ' ||v_log_message.error_check_message);
783			END IF;
784		END LOOP;
785	END IF;
786END ins_auto_catchup_offers_hash;
787
788PROCEDURE catchup_offer_generation(i_channel_code IN schedule_item.channel_code%TYPE DEFAULT NULL,
789  								   i_slot_date IN schedule_item.schedule_date%TYPE,
790								   i_slot_start_time IN schedule_item.start_time%TYPE,
791								   i_slot_end_time IN schedule_item.start_time%TYPE,
792								   i_slot_id IN schedule_item.schedule_item_id%TYPE,
793								   i_purchase_id IN schedule_item.purchase_id%TYPE,
794								   i_slot_name IN schedule_item.slot_name%TYPE,
795								   i_service_id IN vod_platform.vod_platform_id%TYPE,
796								   i_linear_channel_of_slot IN channel.channel_name%TYPE,
797								   i_platform_name IN vod_platform.platform_name%TYPE,
798								   i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE,
799								   i_disable_internal_transaction IN t.logical%TYPE DEFAULT k_false,
800								   i_title_id IN title.title_id%TYPE,
801								   i_od_channel_code IN vod_platform.channel_code%TYPE,
802								   o_creation_failed OUT t.logical%TYPE,
803								   o_creation_skipped OUT t.logical%TYPE,
804								   o_created_offer_ids OUT t_number_table)
805IS
806CURSOR c_policy (i_offer_defaults_id  IN offer_defaults.offer_defaults_id%TYPE) IS
807			SELECT od.tx_template_id,
808				   od.branding_id,
809				   od.currency_code,
810				   od.target_resolution,
811				   od.content_code_reference,
812				   od.target_audience_reference,
813				   od.cost,
814				   od.apply_epg_on_creation
815			FROM offer_defaults od
816			WHERE od.offer_defaults_id = i_offer_defaults_id;
817
818	v_licence_calc_start_date_time		on_demand_avail_window.start_date%TYPE;
819	v_licence_calc_end_date_time		on_demand_avail_window.end_date%TYPE;
820	v_purchase_ids						t.number_table;
821	v_slot_ids							t.number_table;
822	v_date_calc_rule_result 			t_date_calc_rule_result;
823	v_rule_calc_end_date_time			DATE;
824	v_rule_calc_start_date_time			DATE;
825	v_final_offer_start_date			DATE;
826	v_final_offer_end_date				DATE;
827	v_created_item_ids 					t_number_table;
828	v_version_ids			 			t.number_table;
829	v_service_catchup_br_result			on_demand_business_rules.t_service_catchup_result;
830	v_service_catchup_rule_passed		t.logical%TYPE := k_false;
831	v_catchup_error_message				VARCHAR2(2000);
832	v_offer_type_correct				t.logical%TYPE := k_false;
833	v_offer_type_id						offer_type.offer_type_id%TYPE;
834	v_offer_defaults_id					offer_defaults.offer_defaults_id%TYPE;
835	v_policy							c_policy%ROWTYPE;
836	v_is_tvod							t.logical%TYPE := k_false;
837	v_cost_driven_from_pvr				t.logical%TYPE := k_false;
838	v_start_date_calc_rule_id			date_calculation_rule.date_calculation_rule_id%TYPE;
839	v_end_date_calc_rule_id				date_calculation_rule.date_calculation_rule_id%TYPE;
840	v_slot_end_date						DATE;
841	v_offer_duration_mins				NUMBER;
842	v_slot_duration_mins				NUMBER;
843	v_offer_air_category				vod_offer_schedule.special_airing_category%TYPE;
844	v_rules_maintenance_guid			on_demand_avail_window.rules_maintenance_guid%TYPE;
845	v_exclude_from_auto_catchup			t.logical%TYPE;
846	v_no_start_over_for_premiere		t.logical%TYPE;
847	v_services_with_created_offers		VARCHAR2(2000);
848	v_parent_offer_ids					t.number_table;
849	v_offer_type_ver_required			offer_type.version_required%TYPE;
850	v_most_suitable_version				service_mat_suitability_prof.get_most_sutable_vc%TYPE DEFAULT k_true;
851	v_created_offer_ids					t_number_table;
852    v_PrevProgAiringWithinDate			DATE;
853    v_calculate_offer_end_date          DATE;
854    v_offer_duration_hours              NUMBER;
855    v_purchase_end_date                 DATE;
856
857v_item_start_dates                  t.date_table;
858
859BEGIN
860	o_creation_failed := k_false;
861	o_creation_skipped := k_false;
862	o_created_offer_ids := t_number_table();
863	BEGIN
864
865
866				SELECT 	start_date,
867						end_date,
868						rules_maintenance_guid
869				INTO	v_licence_calc_start_date_time,
870						v_licence_calc_end_date_time,
871						v_rules_maintenance_guid
872				FROM
873					(SELECT odaw.start_date,
874							odaw.end_date,
875							odaw.rules_maintenance_guid
876					 FROM 	on_demand_avail_window odaw
877					 WHERE 	odaw.purchase_id = i_purchase_id
878					 AND 	odaw.source_object_id = i_slot_id
879					 AND 	odaw.source_object_type = 'SLOT'
880					 AND 	odaw.window_not_exist = k.logical_false
881					 ORDER BY odaw.last_update_date DESC)
882				WHERE	ROWNUM = 1;
883			EXCEPTION
884				WHEN no_data_found THEN NULL;-- means there is no restriction on purchase
885			END;
886
887			SELECT *
888			INTO v_exclude_from_auto_catchup, v_no_start_over_for_premiere
889			FROM (SELECT pbpvv.property_name,
890						 DECODE(NVL(pbpvv.property_value, 'False'), 'True', 1, 'False', 0) AS property_value
891				  FROM pub_br_property_values_view pbpvv
892				  WHERE pbpvv.link_guid = v_rules_maintenance_guid)
893			PIVOT (MIN(property_value) FOR property_name IN ('ExcludeFromAutomaticCatchup', 'NoStartOverForPremiere'));
894			g.debug('Programme Catch-up Restictions', 'ExcludeFromAutomaticCatchup', v_exclude_from_auto_catchup, 'NoStartOverForPremiere', v_no_start_over_for_premiere);
895
896			v_catchup_error_message := NULL;
897			v_service_catchup_rule_passed := k_false;
898
899			BEGIN
900			IF (NVL(v_exclude_from_auto_catchup, k_false) = k_false) THEN
901				/*call catchup rule*/
902				on_demand_business_rules.call_service_catchup_br(i_service_id => i_service_id,
903																i_slot_id => i_slot_id,
904																o_service_catchup_br_result =>  v_service_catchup_br_result);
905
906				IF v_service_catchup_br_result IS NOT empty THEN
907					FOR i IN v_service_catchup_br_result.first..v_service_catchup_br_result.last LOOP
908						BEGIN
909							SELECT distinct(k.logical_true)
910							INTO v_offer_type_correct
911							FROM offer_type ot
912							INNER JOIN offer_type_service ots ON ots.offer_type_id = ot.offer_type_id
913							INNER JOIN vod_platform vp ON vp.vod_platform_id = ots.service_id
914							WHERE ot.offer_type_id = v_service_catchup_br_result(i).offer_type_id
915							AND ots.service_id = i_service_id;
916						EXCEPTION WHEN NO_DATA_FOUND THEN
917							v_offer_type_correct := k.logical_false;
918						END;
919					
920					IF v_service_catchup_br_result(i).offer_type_id IS NULL OR v_offer_type_correct	= k.logical_true THEN
921						IF (v_service_catchup_br_result(i).skip_offer_creation = k_false AND v_service_catchup_br_result(i).skipCreationPrevProgAirWithin > 0) THEN
922                            BEGIN
923                              SELECT galaxy_util.glx_to_date_time(si.Schedule_Date,si.Start_Time) 
924                              INTO v_PrevProgAiringWithinDate
925                              FROM schedule_item SI
926                              JOIN catchup_slot_offer_item csoi ON csoi.slot_id = si.Schedule_Item_Id
927                              JOIN vod_item_schedule vis on vis.item_schedule_id = csoi.offer_item_id
928                              WHERE si.Channel_Code = i_channel_code AND si.Purchase_Id = i_purchase_id AND si.schedule_item_id <> i_slot_id
929                              AND rownum = 1
930                              AND galaxy_util.glx_to_date_time(si.Schedule_Date,si.Start_Time) >= galaxy_util.glx_to_date_time(i_slot_date,i_slot_start_time) - galaxy_util.glx_to_timespan(v_service_catchup_br_result(i).skipCreationPrevProgAirWithin*3600) 
931                              AND galaxy_util.glx_to_date_time(si.Schedule_Date,si.Start_Time) < galaxy_util.glx_to_date_time(i_slot_date,i_slot_start_time)
932                              AND vis.platform_id = i_service_id;
933                            EXCEPTION
934                              WHEN no_data_found THEN
935                                v_PrevProgAiringWithinDate := NULL;
936                            END;			
937                                
938                            IF v_PrevProgAiringWithinDate IS NOT NULL THEN
939                                v_service_catchup_br_result(i).skip_offer_creation := k_true;
940                                v_service_catchup_br_result(i).error_message := 'Failed to create Offer. Skipped. '||v_PrevProgAiringWithinDate||' Slot already offered. skipCreationPrevProgAirWithin '||v_service_catchup_br_result(i).skipCreationPrevProgAirWithin||' Hours.';
941                            END IF;
942                        END IF;
943                        
944                        IF (v_service_catchup_br_result(i).skip_offer_creation = k_false) THEN
945                            IF v_service_catchup_rule_passed = k_false THEN
946								v_service_catchup_rule_passed := k_true;
947							END IF;
948							v_slot_ids(1) := i_slot_id;
949							v_item_start_dates(1) :=  NULL;
950							/*OFFER START DATE CALCULATION RULE*/
951							on_demand_business_rules.call_date_calculation_rules_br(i_date_calculation_rule_id => v_service_catchup_br_result(i).start_date_calc_rule_id,
952																					i_offer_end_date => NULL,
953																					i_offer_end_time => NULL,
954																					i_offer_item_start_dates => v_item_start_dates,
955																					i_offer_start_date => NULL,
956																					i_offer_start_time => NULL,
957																					i_purchase_ids => v_purchase_ids,
958																					i_schedule_item_ids => v_slot_ids,
959																					i_temp_session_id => i_temp_session_id,
960																					o_results => v_date_calc_rule_result);
961--							raise_application_error(-20001,'v_date_calc_rule_result(1).result_date = '||v_date_calc_rule_result(1).result_date||', v_date_calc_rule_result(1).result_time'||v_date_calc_rule_result(1).result_time );
962							IF v_date_calc_rule_result IS NOT EMPTY THEN
963								v_rule_calc_start_date_time := galaxy_util.glx_to_date_time(v_date_calc_rule_result(1).result_date,i_duration => nvl(v_date_calc_rule_result(1).result_time,0));
964								v_start_date_calc_rule_id := v_date_calc_rule_result(1).date_calculation_rule_id;
965								v_item_start_dates(1) := v_rule_calc_start_date_time;
966							END IF;
967							/*OFFER END DATE CALCULATION RULE*/
968							on_demand_business_rules.call_date_calculation_rules_br(i_date_calculation_rule_id => v_service_catchup_br_result(i).end_date_calc_rule_id,-- 94,
969																					i_offer_end_date => NULL,
970																					i_offer_end_time => NULL,
971																					i_offer_item_start_dates => v_item_start_dates,
972																					i_offer_start_date => NULL,
973																					i_offer_start_time => NULL,
974																					i_purchase_ids => v_purchase_ids,
975																					i_schedule_item_ids => v_slot_ids,
976																					i_temp_session_id => i_temp_session_id,
977																					o_results => v_date_calc_rule_result);
978							IF v_date_calc_rule_result IS NOT EMPTY THEN
979								v_rule_calc_end_date_time := galaxy_util.glx_to_date_time(v_date_calc_rule_result(1).result_date,i_duration => nvl(v_date_calc_rule_result(1).result_time,0));
980								v_end_date_calc_rule_id := v_date_calc_rule_result(1).date_calculation_rule_id;
981							END IF;
982
983							/*if there is no availible dates according to date calculation rules, no need to continue*/
984							IF v_rule_calc_start_date_time IS NOT NULL AND v_rule_calc_end_date_time IS NOT NULL THEN
985								IF (v_licence_calc_start_date_time IS NOT NULL AND v_rule_calc_start_date_time < v_licence_calc_start_date_time ) THEN
986									v_final_offer_start_date := v_licence_calc_start_date_time;
987								ELSE
988									v_final_offer_start_date := v_rule_calc_start_date_time;
989								END IF;
990
991								IF (v_rule_calc_end_date_time IS NOT NULL AND v_rule_calc_end_date_time > v_licence_calc_end_date_time ) THEN
992									v_final_offer_end_date := v_licence_calc_end_date_time;
993								ELSE
994									v_final_offer_end_date := v_rule_calc_end_date_time;
995								END IF;
996
997								BEGIN
998									SELECT  galaxy_util.glx_to_date(p.avail_end_date)
999									INTO v_purchase_end_date
1000									FROM purchase_table p
1001									WHERE p.purchase_id = i_purchase_id;
1002								EXCEPTION WHEN NO_DATA_FOUND THEN
1003									NULL;
1004								END;
1005
1006                                v_calculate_offer_end_date:= v_final_offer_end_date;
1007
1008                                IF v_service_catchup_br_result(i).offerEndTrimIfExceedsLWEnd = 1 AND v_purchase_end_date < v_calculate_offer_end_date THEN
1009                                   v_final_offer_end_date:= v_purchase_end_date;
1010                                      IF v_service_catchup_br_result(i).minOfferAvailabilityPeriod > 0 THEN
1011                                         v_offer_duration_hours:= (v_final_offer_end_date - v_final_offer_start_date) * 24;
1012                                         IF v_offer_duration_hours <= v_service_catchup_br_result(i).minOfferAvailabilityPeriod THEN
1013                                            v_final_offer_end_date:= v_calculate_offer_end_date;
1014                                         END IF;
1015                                      END IF;
1016                                END IF;
1017
1018								v_purchase_ids(1)	:= i_purchase_id;
1019
1020								IF v_service_catchup_br_result(i).offer_type_id IS NULL THEN
1021									SELECT vp.default_offer_type_id
1022									INTO v_offer_type_id
1023									FROM vod_platform vp
1024									WHERE vp.vod_platform_id = i_service_id;
1025								ELSE
1026									v_offer_type_id := v_service_catchup_br_result(i).offer_type_id;
1027								END IF;
1028
1029								g.debug('Starting check for most suitable version');
1030								SELECT ot.version_required
1031								INTO v_offer_type_ver_required
1032								FROM offer_type ot
1033								WHERE ot.offer_type_id = v_offer_type_id;
1034
1035								BEGIN
1036									SELECT smsp.get_most_suitable_version
1037									INTO v_most_suitable_version
1038									FROM vod_platform vp
1039									JOIN service_mat_suitability_prof smsp ON smsp.service_media_suitability_id = vp.service_media_suitability_id
1040									WHERE vp.vod_platform_id = i_service_id;
1041								EXCEPTION
1042									WHEN NO_DATA_FOUND THEN NULL;
1043								END;
1044
1045								IF (NVL(v_offer_type_ver_required, on_demand_setup.k_required_empty) != on_demand_setup.k_required_empty AND v_most_suitable_version = k_true) THEN
1046									v_version_ids(1) := tx_suitable_versions(prm_title_id				=> i_title_id,
1047																			 prm_start_time				=> v_final_offer_start_date - trunc(v_final_offer_start_date),
1048																			 prm_cert_restriction_group	=> NULL,
1049																			 prm_check_point			=> 'D',
1050																			 prm_channel_code			=> i_od_channel_code,
1051																			 prm_date					=> trunc(v_final_offer_start_date),
1052																			 i_on_demand_version		=> k_true);
1053								ELSE
1054									v_version_ids(1) := 0;
1055								END IF;
1056								g.debug('Determined most suitable version id = ' || v_version_ids(1));
1057
1058								IF v_service_catchup_br_result(i).offer_default_id IS NULL THEN
1059									SELECT ot.default_offer_defaults_id,
1060										   ot.is_tvod,
1061										   ot.cost_driven_from_pvr
1062									INTO v_offer_defaults_id,
1063										 v_is_tvod,
1064										 v_cost_driven_from_pvr
1065									FROM offer_type ot
1066									WHERE ot.offer_type_id = v_offer_type_id;
1067								ELSE
1068									v_offer_defaults_id := v_service_catchup_br_result(i).offer_default_id;
1069
1070									SELECT ot.is_tvod, ot.cost_driven_from_pvr
1071									INTO v_is_tvod, v_cost_driven_from_pvr
1072									FROM offer_type ot
1073									WHERE ot.offer_type_id = v_offer_type_id;
1074								END IF;
1075
1076								OPEN c_policy(v_offer_defaults_id);
1077								FETCH c_policy INTO v_policy;
1078								CLOSE c_policy;
1079
1080								g.debug('Before Offer special Airing Category determination');
1081								v_slot_end_date := galaxy_util.glx_to_date_time(i_slot_date, i_slot_end_time);
1082								v_slot_duration_mins := (i_slot_end_time - i_slot_start_time) / 60;  -- convert from seconds to minutes
1083								v_offer_duration_mins := (v_final_offer_end_date - v_final_offer_start_date) * 1440; -- convert from days to minutes
1084
1085								IF (v_final_offer_start_date >= v_slot_end_date AND v_offer_duration_mins > on_demand_setup.g_max_duration_for_restart) THEN
1086									v_offer_air_category := v_service_catchup_br_result(i).air_cat_catchup;
1087								ELSIF (v_final_offer_start_date >= v_slot_end_date AND v_offer_duration_mins <= on_demand_setup.g_max_duration_for_restart) THEN
1088									v_offer_air_category := v_service_catchup_br_result(i).air_cat_start_prev;
1089								ELSIF (v_final_offer_start_date < v_slot_end_date AND v_offer_duration_mins < (v_slot_duration_mins + on_demand_setup.g_max_duration_for_restart)) THEN
1090									v_offer_air_category := v_service_catchup_br_result(i).air_cat_start_over_and_prev;
1091								END IF;
1092
1093								-- To delete possible errors from previous slots and services, no impact on current slot
1094								plan_pres_messages.clear_sch_msg_log(i_user_code => USER,i_temp_session_id => i_temp_session_id);
1095						
1096								on_demand_group_schedule.create_offers(i_create_offers_mode => on_demand_group_schedule.k_create_offers_mode_hierarchy,
1097																	i_temp_session_id => i_temp_session_id,
1098																	i_service_id => i_service_id,
1099																	i_slot_ids => v_slot_ids,
1100																	i_hierarchy_date => galaxy_util.glx_to_char(trunc(v_final_offer_start_date)),
1101																	i_offer_name => NULL,
1102																	i_offer_creation_mode => on_demand_schedule.k_one_offer_each_item,
1103																	i_offer_start_date => trunc(v_final_offer_start_date),
1104																	i_offer_start_time => numtodsinterval(v_final_offer_start_date - trunc(v_final_offer_start_date),'day'),
1105																	i_offer_end_date => trunc(v_final_offer_end_date),
1106																	i_offer_end_time => numtodsinterval(v_final_offer_end_date - trunc(v_final_offer_end_date),'day'),
1107																	i_item_start_date => trunc(v_final_offer_start_date),
1108																	i_item_start_time => numtodsinterval(v_final_offer_start_date - trunc(v_final_offer_start_date),'day'),
1109																	i_item_end_date => trunc(v_final_offer_end_date),
1110																	i_item_end_time => numtodsinterval(v_final_offer_end_date - trunc(v_final_offer_end_date),'day'),
1111																	i_offer_cost => CASE WHEN v_is_tvod = k.logical_true AND v_cost_driven_from_pvr = k.logical_false THEN v_policy.cost ELSE NULL END,
1112																	i_purchase_ids => v_purchase_ids,
1113																	i_version_ids => v_version_ids,
1114																	i_apply_dates_policy => k_true,
1115																	i_apply_profiles_policy => k_true,
1116																	i_apply_event_profile_policy => k_true,
1117																	i_apply_format_policy => k_true,
1118																	i_apply_epg_policy => v_policy.apply_epg_on_creation,
1119																	i_offer_type_id => v_offer_type_id,
1120																	i_offer_default_id => v_offer_defaults_id,
1121																	i_proceed_skip_errors_mode => 'A',
1122																	i_item_branding_id => v_policy.branding_id,
1123																	i_offer_currency_code => v_policy.currency_code,
1124																	i_target_resolution => v_policy.target_resolution,
1125																	i_target_audience => v_policy.target_audience_reference,
1126																	i_content_code => v_policy.content_code_reference,
1127																	i_offer_airing_category => v_offer_air_category,
1128																	i_called_from_context => k_offer_auto_catchup_context,
1129																	i_item_start_date_rule_id => v_start_date_calc_rule_id,
1130																	i_item_end_date_rule_id => v_end_date_calc_rule_id,
1131																	i_disable_internal_transaction => i_disable_internal_transaction,
1132																	o_created_offer_ids => v_created_offer_ids,
1133																	o_created_item_ids => v_created_item_ids);
1134
1135								ins_auto_catchup_offers_hash(i_offer_ids => v_created_offer_ids,
1136															 i_temp_session_id => i_temp_session_id,
1137															 i_linear_channel_of_slot => i_linear_channel_of_slot,
1138															 i_slot_date => i_slot_date,
1139															 i_slot_start_time => i_slot_start_time,
1140															 i_slot_end_time => i_slot_end_time,
1141															 i_slot_name => i_slot_name);
1142
1143								IF (v_created_offer_ids IS NOT NULL OR v_created_offer_ids IS NOT empty) THEN
1144									SELECT stragg2(NEW agg_input(vp.platform_name,'; '))
1145									INTO v_services_with_created_offers
1146									FROM vod_offer_schedule vos
1147									INNER JOIN vod_platform vp ON vp.vod_platform_id = vos.platform_id
1148									WHERE vos.offer_id IN (SELECT column_value FROM TABLE(o_created_offer_ids));
1149
1150									IF ((v_service_catchup_br_result(i).air_cat_catchup IS NOT NULL
1151									OR v_service_catchup_br_result(i).air_cat_start_prev IS NOT NULL
1152									OR v_service_catchup_br_result(i).air_cat_start_over_and_prev IS NOT NULL) AND v_offer_air_category IS NULL) THEN
1153										FOR created_offers IN (SELECT column_value FROM TABLE(o_created_offer_ids)) LOOP
1154											v_auto_catchup_offers_hash(i_temp_session_id).extend;
1155											v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created :=  'Warning: could not determine Special Airing Category for Offer ' || created_offers.column_value;
1156										END LOOP;
1157									END IF;
1158
1159									o_created_offer_ids := o_created_offer_ids MULTISET UNION v_created_offer_ids;
1160
1161									IF (TRIM(on_demand_setup.g_def_catchup_offer_status) IS NOT NULL) THEN
1162										IF i_purchase_id = 0 THEN
1163											v_parent_offer_ids(1) := o_created_offer_ids(1);
1164										ELSE
1165											SELECT vosi.offer_id
1166											BULK COLLECT INTO v_parent_offer_ids
1167											FROM vod_offer_schedule_item vosi
1168											JOIN vod_item_schedule vis ON vis.item_schedule_id = vosi.item_schedule_id
1169											WHERE vosi.offer_id IN (SELECT /*+ cardinality(vc 1) */ vc.column_value FROM TABLE(o_created_offer_ids) vc)
1170											AND vis.parent_offer_item_id IS NULL;
1171										END IF;
1172										BEGIN
1173											on_demand_group_schedule.batch_modify_offer(i_offers_ids => v_parent_offer_ids,
1174																						i_temp_session_id => i_temp_session_id,
1175																						i_offer_status => k_offer_status || on_demand_setup.g_def_catchup_offer_status);
1176
1177											ins_auto_catchup_offers_hash(i_offer_ids => o_created_offer_ids,
1178												 						 i_temp_session_id => i_temp_session_id,
1179												 						 i_linear_channel_of_slot => i_linear_channel_of_slot,
1180												 						 i_slot_date => i_slot_date,
1181												 						 i_slot_start_time => i_slot_start_time,
1182												 						 i_slot_end_time => i_slot_end_time,
1183												 						 i_slot_name => i_slot_name,
1184												 						 i_called_from_modification => k_true);
1185										EXCEPTION WHEN OTHERS THEN
1186											v_auto_catchup_offers_hash(i_temp_session_id).extend;
1187											v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created :=  'Offer created';
1188											v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := 'Failed to update Offer Status: ' || SQLERRM || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
1189										END;
1190									END IF;
1191								ELSE
1192									o_creation_failed := k_true;
1193								END IF;
1194							END IF;
1195						ELSE
1196							v_service_catchup_rule_passed := k_true;
1197							o_creation_skipped := k_true;
1198							v_auto_catchup_offers_hash(i_temp_session_id).extend;
1199                            v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created :=  'Warning';
1200                            v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).service_name := i_platform_name;
1201                            v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1202						END IF;
1203					 ELSE
1204						v_service_catchup_rule_passed := k_false;
1205						v_catchup_error_message := 'Failed to create an Offer. Offer_type_id ' || v_service_catchup_br_result(i).offer_type_id || ' is not belong to Service ' || i_service_id || '.';
1206						g.debug('v_catchup_error_message = '||v_catchup_error_message);
1207					END IF;
1208					END LOOP;
1209				ELSE 
1210					v_service_catchup_rule_passed := k_false;
1211					v_catchup_error_message := 'Failed to create an Offer. Catch Up Restrictions have not been configured on the Service.' ;
1212				END IF;
1213			ELSE
1214				v_catchup_error_message := 'Programme was excluded from automatic catch-up due to catch-up restrictions';
1215				g.debug('v_catchup_error_message = '||v_catchup_error_message);
1216			END IF;
1217			
1218			EXCEPTION WHEN OTHERS THEN
1219				v_service_catchup_rule_passed := k_false;
1220				v_catchup_error_message := 'Failed to create an Offer: '|| SQLERRM || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
1221				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message);
1222			END;
1223			
1224			IF v_service_catchup_rule_passed = k_false THEN
1225				v_auto_catchup_offers_hash(i_temp_session_id).extend;
1226				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := 'Failed to create Catch Up Offer';
1227				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).linear_channel := i_linear_channel_of_slot;
1228				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).air_day := galaxy_util.glx_to_date(i_slot_date);
1229				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_start_time := tstr(i_slot_start_time);
1230				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_end_time := tstr(i_slot_end_time);
1231				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_name := i_slot_name;
1232				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).service_name := i_platform_name;
1233				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1234				g.debug('slot_name = '||v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).slot_name);
1235				g.debug('reason_to_fail = '||v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail);
1236				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message);
1237			ELSIF (v_service_catchup_rule_passed = k_true AND TRIM(v_catchup_error_message) IS NOT NULL) THEN
1238				v_auto_catchup_offers_hash(i_temp_session_id).extend;
1239				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).services_with_created_offers := v_services_with_created_offers;
1240				v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := v_catchup_error_message;
1241			END IF;
1242END catchup_offer_generation;
1243
1244PROCEDURE catchup_create_offer(i_slot_date IN schedule_item.schedule_date%TYPE,
1245							   i_slot_start_time IN schedule_item.start_time%TYPE,
1246							   i_slot_end_time IN schedule_item.start_time%TYPE,
1247							   i_slot_id IN schedule_item.schedule_item_id%TYPE,
1248							   i_purchase_id IN schedule_item.purchase_id%TYPE,
1249							   i_slot_name IN schedule_item.slot_name%TYPE,
1250							   i_service_id IN vod_platform.vod_platform_id%TYPE,
1251							   i_linear_channel_of_slot IN channel.channel_name%TYPE,
1252							   i_platform_name IN vod_platform.platform_name%TYPE,
1253							   i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE,
1254							   i_title_id IN title.title_id%TYPE,
1255							   i_od_channel_code IN vod_platform.channel_code%TYPE,
1256							   i_channel_code IN schedule_item.channel_code%TYPE,
1257							   o_creation_failed OUT t.logical%TYPE,
1258							   o_creation_skipped OUT t.logical%TYPE,
1259							   o_created_offer_ids OUT t_number_table)
1260IS
1261v_created_offer_ids t_number_table;
1262BEGIN
1263	v_auto_catchup_offers_hash(i_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1264	o_created_offer_ids := t_number_table();
1265	catchup_offer_generation(i_slot_date => i_slot_date,
1266							 i_slot_start_time => i_slot_start_time,
1267							 i_slot_end_time => i_slot_end_time,
1268							 i_slot_id => i_slot_id,
1269							 i_purchase_id => i_purchase_id,
1270							 i_slot_name => i_slot_name,
1271							 i_service_id => i_service_id,
1272							 i_linear_channel_of_slot => i_linear_channel_of_slot,
1273							 i_platform_name => i_platform_name,
1274							 i_temp_session_id => i_temp_session_id,
1275							 i_disable_internal_transaction => k_true,
1276							 i_title_id => i_title_id,
1277							 i_od_channel_code => i_od_channel_code,
1278							 i_channel_code => i_channel_code,
1279							 o_creation_failed => o_creation_failed,
1280							 o_creation_skipped => o_creation_skipped,
1281							 o_created_offer_ids => v_created_offer_ids);
1282	IF v_created_offer_ids IS NOT NULL AND v_created_offer_ids IS NOT empty THEN
1283		IF i_purchase_id = 0 THEN
1284			o_created_offer_ids.extend();
1285			o_created_offer_ids(1) := v_created_offer_ids(1);
1286		ELSE
1287			SELECT vosi.offer_id
1288			BULK COLLECT INTO o_created_offer_ids
1289			FROM vod_offer_schedule_item vosi
1290			JOIN vod_item_schedule vis ON vis.item_schedule_id = vosi.item_schedule_id
1291			WHERE vosi.offer_id IN (SELECT /*+ cardinality(vc 1) */ vc.column_value FROM TABLE(v_created_offer_ids) vc)
1292			  AND vis.parent_offer_item_id IS NULL;
1293		END IF;
1294	END IF;
1295END catchup_create_offer;
1296
1297PROCEDURE catchup_assign_suit_version(i_offers_ids IN t.number_table,
1298									  i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
1299IS
1300v_version_id schedule_item.version_id%TYPE;
1301v_offer_ids t.number_table;
1302v_item_ids t.number_table;
1303v_processed_offer_ids t_number_table := t_number_table();
1304v_update_failed t.logical%TYPE := k_false;
1305v_catchup_error_message	VARCHAR2(2000);
1306v_offer_item_id vod_item_schedule.item_schedule_id%TYPE;
1307v_purchase_id purchase.purchase_id%TYPE;
1308v_service_name vod_platform.platform_name%TYPE;
1309v_most_suitable_version	service_mat_suitability_prof.get_most_sutable_vc%TYPE;
1310v_offer_type_ver_required offer_type.version_required%TYPE;
1311v_title_id title.title_id%TYPE;
1312v_start_time vod_offer_schedule.start_time%TYPE;
1313v_channel_code vod_platform.channel_code%TYPE;
1314v_start_date vod_offer_schedule.start_date%TYPE;
1315BEGIN
1316	v_auto_catchup_offers_hash(i_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1317	FOR indx IN i_offers_ids.FIRST .. i_offers_ids.LAST LOOP
1318		BEGIN
1319			SELECT vp.platform_name,
1320				   smsp.get_most_suitable_version,
1321				   vis.item_schedule_id,
1322				   vis.purchase_id,
1323				   p.title_id,
1324				   vp.channel_code,
1325				   vos.start_date,
1326				   vos.start_time
1327			  INTO v_service_name,
1328				   v_most_suitable_version,
1329				   v_offer_item_id,
1330				   v_purchase_id,
1331				   v_title_id,
1332				   v_channel_code,
1333				   v_start_date,
1334				   v_start_time
1335			FROM vod_offer_schedule vos
1336			JOIN vod_platform vp ON vp.vod_platform_id = vos.platform_id
1337			JOIN vod_offer_schedule_item vosi ON vosi.offer_id = vos.offer_id
1338			JOIN vod_item_schedule vis ON vis.item_schedule_id = vosi.item_schedule_id
1339			JOIN purchase p ON p.purchase_id = vis.purchase_id
1340			JOIN vod_platform vp ON vp.vod_platform_id = vis.platform_id
1341			LEFT JOIN service_mat_suitability_prof smsp ON smsp.service_media_suitability_id = vp.service_media_suitability_id
1342			WHERE vos.offer_id = i_offers_ids(indx);
1343
1344			IF (NVL(v_offer_type_ver_required, on_demand_setup.k_required_empty) != on_demand_setup.k_required_empty AND v_most_suitable_version = k_true) THEN
1345				v_version_id := tx_suitable_versions(prm_title_id				=> v_title_id,
1346													 prm_start_time				=> v_start_time,
1347													 prm_cert_restriction_group	=> NULL,
1348													 prm_check_point			=> 'D',
1349													 prm_channel_code			=> v_channel_code,
1350													 prm_date					=> v_start_date,
1351													 i_on_demand_version		=> k_true);
1352			ELSE
1353				v_version_id := 0;
1354			END IF;
1355
1356			v_offer_ids(1) := i_offers_ids(indx);
1357			v_item_ids(1) := v_offer_item_id;
1358
1359			on_demand_group_schedule.batch_modify_offer(i_offers_ids => v_offer_ids,
1360														i_offer_items => v_item_ids,
1361														i_temp_session_id => i_temp_session_id,
1362														i_item_version_id => v_version_id,
1363														i_item_purchase_id => v_purchase_id,
1364														i_disable_internal_transaction => k_true);
1365			v_processed_offer_ids.extend();
1366			 	v_processed_offer_ids(1) := v_offer_ids(1);
1367			ins_auto_catchup_offers_hash(i_offer_ids => v_processed_offer_ids,
1368										 i_temp_session_id => i_temp_session_id,
1369										 i_called_from_modification => k_true);
1370		EXCEPTION WHEN OTHERS THEN
1371			v_catchup_error_message := 'Failed to assign Version.';
1372			v_update_failed := k_true;
1373			g.debug('Text of error message: ', 'error_message', DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1374			Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1375		END;
1376
1377		IF (v_update_failed = k_true) THEN
1378			g.debug('Writing Error Message','v_catchup_error_message',v_catchup_error_message);
1379			v_auto_catchup_offers_hash(i_temp_session_id).extend;
1380			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).service_name := v_service_name;
1381			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := 'Processing Slot Changes Produced Error: ';
1382			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1383		END IF;
1384	END LOOP;
1385END catchup_assign_suit_version;
1386
1387PROCEDURE catchup_update_offer_avail(i_offer_ids IN t.number_table,
1388									 i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
1389IS
1390v_offers_ids t.number_table;
1391v_start_date_calc_rule_id vod_item_schedule.start_date_calc_rule_id%TYPE;
1392v_end_date_calc_rule_id vod_item_schedule.end_date_calc_rule_id%TYPE;
1393v_processed_offer_ids t_number_table := t_number_table();
1394v_update_failed t.logical%TYPE := k_false;
1395v_catchup_error_message	VARCHAR2(2000);
1396v_purchase_id				   purchase.purchase_id%TYPE;
1397v_slot_id					   schedule_item.schedule_item_id%TYPE;
1398v_purchase_ids				   t.number_table;
1399v_slot_ids					   t.number_table;
1400v_catchup_mode				   catchup_slot_offer_item.catchup_mode%TYPE;
1401v_licence_calc_start_date_time on_demand_avail_window.start_date%TYPE;
1402v_licence_calc_end_date_time   on_demand_avail_window.end_date%TYPE;
1403v_date_calc_rule_result		   t_date_calc_rule_result;
1404v_rule_calc_end_date_time	   DATE;
1405v_rule_calc_start_date_time	   DATE;
1406v_final_offer_start_date	   DATE;
1407v_final_offer_end_date		   DATE;
1408v_item_start_dates             t.date_table;
1409BEGIN
1410	v_auto_catchup_offers_hash(i_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1411	FOR indx IN i_offer_ids.FIRST .. i_offer_ids.LAST LOOP
1412		BEGIN
1413			SELECT vis.start_date_calc_rule_id,
1414				   vis.end_date_calc_rule_id,
1415				   vis.purchase_id,
1416				   on_demand_catchup_util.get_catchup_slot_id_from_item(i_offer_item_id => vis.item_schedule_id, i_link_from_parent_item => k_true),
1417				   csoi.catchup_mode
1418			INTO   v_start_date_calc_rule_id,
1419				   v_end_date_calc_rule_id,
1420				   v_purchase_id,
1421				   v_slot_id,
1422				   v_catchup_mode
1423			FROM vod_offer_schedule_item vosi
1424			JOIN vod_item_schedule vis ON vis.item_schedule_id = vosi.item_schedule_id
1425			LEFT JOIN catchup_slot_offer_item csoi ON csoi.offer_item_id = vis.item_schedule_id
1426			WHERE vosi.offer_id = i_offer_ids(indx);
1427
1428			v_purchase_ids(1) := v_purchase_id;
1429			v_slot_ids(1) := v_slot_id;
1430
1431			IF v_catchup_mode = 'A' THEN
1432				BEGIN
1433					SELECT 	start_date,
1434							end_date
1435					INTO	v_licence_calc_start_date_time,
1436							v_licence_calc_end_date_time
1437					FROM 
1438						(SELECT odaw.start_date,
1439								odaw.end_date
1440						 FROM	on_demand_avail_window odaw
1441						 WHERE	odaw.purchase_id = v_purchase_id
1442						 AND	odaw.source_object_id = v_slot_id
1443						 AND	odaw.source_object_type = 'SLOT'
1444						 AND	odaw.window_not_exist = k_false
1445						 ORDER BY odaw.last_update_date DESC)
1446					WHERE	ROWNUM = 1;
1447				EXCEPTION
1448					WHEN no_data_found THEN -- means there is no restriction on purchase
1449						v_licence_calc_start_date_time := NULL;
1450						v_licence_calc_end_date_time := NULL;
1451				END;
1452			END IF;
1453
1454			v_item_start_dates(1) := NULL;
1455			IF v_start_date_calc_rule_id > 0 THEN
1456				on_demand_business_rules.call_date_calculation_rules_br(i_date_calculation_rule_id => v_start_date_calc_rule_id,
1457																		i_offer_end_date => NULL,
1458																		i_offer_end_time => NULL,
1459																		i_offer_item_start_dates => v_item_start_dates,
1460																		i_offer_start_date => NULL,
1461																		i_offer_start_time => NULL,
1462																		i_purchase_ids => v_purchase_ids,
1463																		i_schedule_item_ids => v_slot_ids,
1464																		i_temp_session_id => i_temp_session_id,
1465																		o_results => v_date_calc_rule_result);
1466				IF v_date_calc_rule_result IS NOT EMPTY THEN
1467					v_rule_calc_start_date_time := galaxy_util.glx_to_date_time(v_date_calc_rule_result(1).result_date,i_duration => nvl(v_date_calc_rule_result(1).result_time,0));
1468					v_item_start_dates(1) := v_rule_calc_start_date_time;					
1469				END IF;
1470			END IF;
1471
1472			IF v_end_date_calc_rule_id > 0 THEN
1473				on_demand_business_rules.call_date_calculation_rules_br(i_date_calculation_rule_id => v_end_date_calc_rule_id,
1474																		i_offer_end_date => NULL,
1475																		i_offer_end_time => NULL,
1476																		i_offer_item_start_dates => v_item_start_dates,
1477																		i_offer_start_date => NULL,
1478																		i_offer_start_time => NULL,
1479																		i_purchase_ids => v_purchase_ids,
1480																		i_schedule_item_ids => v_slot_ids,
1481																		i_temp_session_id => i_temp_session_id,
1482																		o_results => v_date_calc_rule_result);
1483				IF v_date_calc_rule_result IS NOT EMPTY THEN
1484					v_rule_calc_end_date_time := galaxy_util.glx_to_date_time(v_date_calc_rule_result(1).result_date,i_duration => nvl(v_date_calc_rule_result(1).result_time,0));
1485				END IF;
1486			END IF;
1487
1488			IF v_rule_calc_start_date_time IS NOT NULL AND v_rule_calc_end_date_time IS NOT NULL THEN
1489				IF (v_licence_calc_start_date_time IS NOT NULL AND v_rule_calc_start_date_time < v_licence_calc_start_date_time ) THEN
1490					v_final_offer_start_date := v_licence_calc_start_date_time;
1491				ELSE
1492					v_final_offer_start_date := v_rule_calc_start_date_time;
1493				END IF;
1494
1495				IF (v_licence_calc_end_date_time IS NOT NULL AND v_rule_calc_end_date_time > v_licence_calc_end_date_time ) THEN
1496					v_final_offer_end_date := v_licence_calc_end_date_time;
1497				ELSE
1498					v_final_offer_end_date := v_rule_calc_end_date_time;
1499				END IF;
1500
1501				v_offers_ids(1) := i_offer_ids(indx);
1502				on_demand_group_schedule.batch_modify_offer(i_offers_ids => v_offers_ids,
1503															i_offer_start_date => galaxy_util.glx_to_char(v_final_offer_start_date),
1504															i_offer_start_time => galaxy_util.glx_to_seconds(NUMTODSINTERVAL(v_final_offer_start_date - TRUNC(v_final_offer_start_date), 'day')),
1505															i_offer_end_date => galaxy_util.glx_to_char(v_final_offer_end_date),
1506															i_offer_end_time => galaxy_util.glx_to_seconds(NUMTODSINTERVAL(v_final_offer_end_date - TRUNC(v_final_offer_end_date), 'day')),
1507															i_item_start_date => galaxy_util.glx_to_char(v_final_offer_start_date),
1508															i_item_start_time => galaxy_util.glx_to_seconds(NUMTODSINTERVAL(v_final_offer_start_date - TRUNC(v_final_offer_start_date), 'day')),
1509															i_item_end_date => galaxy_util.glx_to_char(v_final_offer_end_date),
1510															i_item_end_time => galaxy_util.glx_to_seconds(NUMTODSINTERVAL(v_final_offer_end_date - TRUNC(v_final_offer_end_date), 'day')),
1511															i_temp_session_id => i_temp_session_id,
1512															i_apply_dates_policy => k_true,
1513															i_disable_internal_transaction => k_true);
1514				v_processed_offer_ids.extend();
1515				v_processed_offer_ids(1) := v_offers_ids(1);
1516				ins_auto_catchup_offers_hash(i_offer_ids => v_processed_offer_ids,
1517											 i_temp_session_id => i_temp_session_id,
1518											 i_called_from_modification => k_true);
1519			ELSE
1520				v_catchup_error_message := CASE WHEN v_catchup_error_message IS NOT NULL THEN v_catchup_error_message ||'; ' ELSE NULL END || 'Offer Item does not have Date Calculation Rules. Failed to Update Offer Availability';
1521				v_update_failed := k_true;
1522				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message);
1523			END IF;
1524		EXCEPTION WHEN OTHERS THEN
1525			v_catchup_error_message := 'Failed to update Offer Availability.';
1526			v_update_failed := k_true;
1527			g.debug('Text of error message: ', 'error_message', DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1528			Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1529		END;
1530
1531		IF (v_update_failed = k_true) THEN
1532			g.debug('Writing Error Message','v_catchup_error_message',v_catchup_error_message);
1533			v_auto_catchup_offers_hash(i_temp_session_id).extend;
1534			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := 'Processing Slot Changes Produced Error: ';
1535			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1536		END IF;
1537	END LOOP;
1538END catchup_update_offer_avail;
1539
1540PROCEDURE catchup_update_offer_status(i_offer_ids IN t.number_table,
1541									  i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE,
1542									  i_offer_status IN vod_offer_schedule.status%TYPE)
1543IS
1544v_update_failed t.logical%TYPE := k_false;
1545v_catchup_error_message	VARCHAR2(2000);
1546v_offer_ids t.number_table;
1547v_processed_offer_ids t_number_table := t_number_table();
1548BEGIN
1549	v_auto_catchup_offers_hash(i_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1550	FOR indx IN i_offer_ids.FIRST .. i_offer_ids.LAST LOOP
1551		BEGIN
1552			v_offer_ids(1) := i_offer_ids(indx);
1553			on_demand_group_schedule.batch_modify_offer(i_offers_ids => v_offer_ids,
1554														i_temp_session_id => i_temp_session_id,
1555														i_offer_status => i_offer_status,
1556														i_disable_internal_transaction => k_true);
1557			v_processed_offer_ids.extend();
1558			v_processed_offer_ids(1) := v_offer_ids(1);
1559			ins_auto_catchup_offers_hash(i_offer_ids => v_processed_offer_ids,
1560										 i_temp_session_id => i_temp_session_id,
1561										 i_called_from_modification => k_true);
1562		EXCEPTION WHEN OTHERS THEN
1563				v_catchup_error_message := 'Failed to update Offer Status.';
1564				v_update_failed := k_true;
1565				g.debug('Text of error message: ', 'error_message', DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1566				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1567		END;
1568
1569		IF (v_update_failed = k_true) THEN
1570			g.debug('Writing Error Message','v_catchup_error_message',v_catchup_error_message);
1571			v_auto_catchup_offers_hash(i_temp_session_id).extend;
1572			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := 'Processing Slot Changes Produced Error: ';
1573			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1574		END IF;
1575	END LOOP;
1576END catchup_update_offer_status;
1577
1578PROCEDURE catchup_delete_offer(i_offer_ids IN t.number_table,
1579							   i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
1580IS
1581v_delete_failed t.logical%TYPE := k_false;
1582v_catchup_error_message	VARCHAR2(2000);
1583v_offer_ids t.number_table;
1584v_processed_offer_ids t_number_table := t_number_table();
1585BEGIN
1586	v_auto_catchup_offers_hash(i_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1587	FOR indx IN i_offer_ids.FIRST .. i_offer_ids.LAST LOOP
1588		BEGIN
1589			v_offer_ids(1) := i_offer_ids(indx);
1590			on_demand_group_schedule.delete_batch_offer(i_offer_ids => i_offer_ids);
1591			v_processed_offer_ids.extend();
1592			v_processed_offer_ids(1) := v_offer_ids(1);
1593			ins_auto_catchup_offers_hash(i_offer_ids => v_processed_offer_ids,
1594										 i_temp_session_id => i_temp_session_id);
1595		EXCEPTION WHEN OTHERS THEN
1596				v_catchup_error_message := 'Failed to delete Offer';
1597				v_delete_failed := k_true;
1598				g.debug('Text of error message: ', 'error_message', DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1599				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: ' || v_catchup_error_message || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1600		END;
1601
1602		IF (v_delete_failed = k_true) THEN
1603			g.debug('Writing Error Message','v_catchup_error_message',v_catchup_error_message);
1604			v_auto_catchup_offers_hash(i_temp_session_id).extend;
1605			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).offer_created := 'Processing Slot Changes Produced Error: ';
1606			v_auto_catchup_offers_hash(i_temp_session_id)(v_auto_catchup_offers_hash(i_temp_session_id).COUNT).reason_to_fail := v_catchup_error_message;
1607		END IF;
1608	END LOOP;
1609END;
1610
1611PROCEDURE auto_catchup_offer_generation(
1612								-- called from Workflow, creates offers when schedule status is changed to Release to Catch Up
1613								i_schedule_status_id	IN schedule_status_table.schedule_status_id%TYPE,
1614								o_auto_catchup_result		OUT SYS_REFCURSOR)
1615	IS
1616		v_creation_failed t.logical%TYPE := k_false;
1617		v_creation_skipped t.logical%TYPE := k_false;
1618		v_created_offer_ids t_number_table;
1619		v_temp_session_id glx_session_tmp_generic_select.temp_session_id%TYPE;
1620	BEGIN
1621
1622	v_temp_session_id := session_util.GET_TEMP_SESSION_ID();
1623	v_auto_catchup_offers_hash(v_temp_session_id) := on_demand_catchup_util.t_auto_catchup_offers_rec();
1624
1625	FOR v_slot_rec IN (SELECT 	  si.channel_code,
1626							 	  si.schedule_date AS slot_date,
1627								  si.start_time AS slot_start_time,
1628								  si.start_time + si.duration AS slot_end_time,
1629								  si.schedule_item_id AS slot_id,
1630								  si.purchase_id,
1631								  si.slot_name,
1632								  vp.vod_platform_id AS service_id,
1633								  vp.channel_code AS od_channel_code,
1634								  c.channel_name AS linear_channel_of_slot,
1635								  NVL(si.version_id, 0) AS version_id,
1636								  vp.platform_name,
1637								  NVL(t.title_id, 0) AS title_id
1638						  FROM 	 schedule_status ss
1639						  INNER JOIN channel_link cl ON cl.master_channel_code = ss.channel_code
1640						  INNER JOIN vod_platform vp ON vp.channel_code = cl.slave_channel_code
1641						  INNER JOIN schedule_item_base_view si ON si.channel_code = cl.master_channel_code AND si.schedule_date = ss.schedule_date
1642						  INNER JOIN channel c ON c.channel_code = ss.channel_code  AND on_demand_schedule_util.Is_vod_channel(i_channel_code => c.channel_code)=k_false
1643						  LEFT JOIN purchase p ON p.purchase_id = si.purchase_id
1644						  LEFT JOIN title t ON t.title_id = p.title_id
1645						  WHERE  ss.schedule_status_id = i_schedule_status_id
1646						  AND 	 ss.schedule_date BETWEEN cl.start_date AND cl.end_date
1647						  AND 	 cl.link_type = k_catchup_channel_link
1648						  AND 	 ibms_prop.get_property_number(k_EXCLUDE_FOR_CATCH_UP, si.schedule_item_id) = 0
1649						  AND 	 (si.purchase_id = 0 OR EXISTS (SELECT 1
1650									FROM   purchase_play ppv
1651									INNER JOIN channel_right_type crt ON ppv.rights_type = crt.rights_type
1652									WHERE  ppv.purchase_id = si.purchase_id
1653									AND crt.channel_code=c.channel_code))) LOOP
1654
1655			catchup_offer_generation(i_channel_code => v_slot_rec.channel_code,
1656            						 i_slot_date => v_slot_rec.slot_date,
1657									 i_slot_start_time => v_slot_rec.slot_start_time,
1658									 i_slot_end_time => v_slot_rec.slot_end_time,
1659									 i_slot_id => v_slot_rec.slot_id,
1660									 i_purchase_id => v_slot_rec.purchase_id,
1661									 i_slot_name => v_slot_rec.slot_name,
1662									 i_service_id => v_slot_rec.service_id,
1663									 i_platform_name => v_slot_rec.platform_name,
1664									 i_linear_channel_of_slot => v_slot_rec.linear_channel_of_slot,
1665									 i_temp_session_id => v_temp_session_id,
1666									 i_title_id => v_slot_rec.title_id,
1667									 i_od_channel_code => v_slot_rec.od_channel_code,
1668									 o_creation_failed => v_creation_failed,
1669									 o_creation_skipped => v_creation_skipped,
1670									 o_created_offer_ids => v_created_offer_ids);
1671
1672		END LOOP;
1673		OPEN o_auto_catchup_result FOR
1674					SELECT 	results.offer_created,
1675							results.linear_channel,
1676							results.air_day,
1677							results.slot_start_time,
1678							results.slot_end_time,
1679							results.slot_name,
1680							results.reason_to_fail,
1681							results.service_name,
1682							results.services_with_created_offers
1683					FROM TABLE(get_auto_catchup_result(v_temp_session_id)) results;
1684	END auto_catchup_offer_generation;
1685
1686	PROCEDURE auto_release_for_catchup
1687	IS
1688
1689	v_schedule_status_ids VARCHAR2(4000) := '';
1690
1691	BEGIN
1692
1693		schedule_status_api.auto_change_settings_status(i_table_code => k_tl_catchup_status_code, o_result_status_ids => v_schedule_status_ids);
1694		
1695	END auto_release_for_catchup;
1696
1697	PROCEDURE purge_auto_catchup_offers(i_channel_code  catchup_slot_offer_item.original_channel_code%TYPE,
1698										i_schedule_date	catchup_slot_offer_item.original_schedule_date%TYPE)
1699	IS
1700	v_offers 		 t.number_table;
1701	v_offer_items    t.number_table;
1702	BEGIN
1703		SELECT DISTINCT vos.offer_id
1704		BULK COLLECT INTO v_offers
1705		FROM vod_offer_schedule vos
1706		INNER JOIN vod_offer_schedule_item vosi ON vosi.offer_id = vos.offer_id
1707		INNER JOIN catchup_slot_offer_item csoi ON csoi.offer_item_id = vosi.item_schedule_id
1708		WHERE csoi.catchup_mode = 'A' 
1709			  AND csoi.original_channel_code = i_channel_code
1710			  AND csoi.original_schedule_date = i_schedule_date;
1711				  
1712		on_demand_group_schedule.delete_batch_offer(i_offer_ids => v_offers,
1713													i_offer_items => v_offer_items);
1714
1715	END purge_auto_catchup_offers; 
1716
1717PROCEDURE get_catchup_action_cursor(i_action_code IN catchup_processing_action.action%TYPE,
1718									i_slot_changes_ids IN t_number_table,
1719									o_action_cursor OUT on_demand_catchup_util.t_c_catchup_create_offer)
1720IS
1721BEGIN
1722	CASE i_action_code
1723		WHEN k_act_create_offer THEN
1724			o_action_cursor := on_demand_catchup_util.get_cur_create_offer(i_catchup_slot_changes_ids => i_slot_changes_ids);
1725		WHEN k_act_create_multi_offer THEN
1726			o_action_cursor := on_demand_catchup_util.get_cur_create_multi_offer(i_catchup_slot_changes_ids => i_slot_changes_ids);
1727	END CASE;
1728END get_catchup_action_cursor;
1729
1730PROCEDURE apply_catchup_amend_action(io_slot_changes_ids IN OUT t_number_table,
1731									 i_action_code IN catchup_processing_action.action%TYPE,
1732									 i_action_value IN catchup_processing_action.action_value%TYPE,
1733									 i_is_auto_executed IN t.logical%TYPE,
1734									 i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE,
1735									 i_schedule_process_log_id in schedule_process_log.schedule_process_log_id%TYPE,
1736									 o_is_action_processed OUT t.logical%TYPE)
1737IS
1738c_create_offer on_demand_catchup_util.t_c_catchup_create_offer;
1739r_create_offer on_demand_catchup_util.t_r_catchup_create_offer;
1740
1741CURSOR c_slot_offers IS
1742	SELECT vosi.offer_id,
1743		   csc.catchup_slot_changes_id,
1744		   vosi.item_schedule_id
1745	FROM catchup_slot_changes csc
1746	JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = csc.offer_item_id
1747	WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc);
1748
1749CURSOR c_delete_multi_offers(i_purchase_ids IN t_number_table) IS
1750	SELECT t.offer_id,
1751		   t.item_schedule_id,
1752		   t.vod_platform_id,
1753		   t.catchup_slot_changes_id,
1754		   t.schedule_process_log_id,
1755		   row_number() over (PARTITION BY t.catchup_slot_changes_id ORDER BY t.vod_platform_id) AS catchup_rank 
1756	FROM (
1757		  SELECT vosi.offer_id,
1758				 vosi.item_schedule_id,
1759				 csc.catchup_slot_changes_id,
1760				 vp.vod_platform_id,
1761				 csc.schedule_process_log_id
1762		  FROM catchup_slot_changes csc
1763		  JOIN vod_item_schedule vis ON vis.item_schedule_id = csc.offer_item_id
1764		  JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = vis.item_schedule_id
1765		  JOIN vod_offer_schedule vos ON vos.offer_id = vosi.offer_id
1766		  JOIN vod_platform vp ON vp.vod_platform_id = vis.platform_id
1767		  JOIN catchup_slot_offer_item csoi ON csoi.offer_item_id = vis.item_schedule_id
1768		  LEFT JOIN catchup_amendment_condition cac ON cac.catchup_amendment_rule_id = csc.catchup_amendment_rule_id
1769		  WHERE csc.catchup_slot_changes_id = io_slot_changes_ids(1)
1770		  AND (cac.within_offer_days IS NULL OR galaxy_util.glx_to_date(vos.start_date) >= (TRUNC(SYSDATE) + cac.within_offer_days))
1771		  AND galaxy_util.glx_to_date_time(vos.end_date, vos.end_time) >= SYSDATE
1772		  AND (cac.catchup_offer_creation_mode IS NULL OR INSTR(DECODE(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'),NVL(csoi.catchup_mode,DECODE(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'))) > 0)
1773		  UNION
1774		  SELECT DISTINCT vosi.offer_id,
1775						  vosi.item_schedule_id,
1776						  csc.catchup_slot_changes_id,
1777						  vp.vod_platform_id,
1778						  csc.schedule_process_log_id
1779		  FROM schedule_item_base_simple_view si
1780		  JOIN schedule_status ss ON ss.schedule_date = si.schedule_date AND ss.channel_code = si.channel_code AND ss.status = k_released_for_catchup
1781		  JOIN channel_link cl ON cl.master_channel_code = ss.channel_code
1782		  JOIN vod_platform vp ON vp.channel_code = cl.slave_channel_code
1783		  JOIN channel c ON c.channel_code = ss.channel_code AND on_demand_schedule_util.is_vod_channel(i_channel_code => c.channel_code) = k_false
1784		  JOIN vod_item_schedule vis ON vis.purchase_id = si.purchase_id AND vis.platform_id = vp.vod_platform_id
1785		  JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = vis.item_schedule_id
1786		  JOIN vod_offer_schedule vos ON vos.offer_id = vosi.offer_id
1787		  JOIN catchup_slot_offer_item csoi ON csoi.offer_item_id = vis.item_schedule_id
1788		  JOIN catchup_slot_changes csc on csc.catchup_slot_changes_id = io_slot_changes_ids(1)
1789		  LEFT JOIN catchup_amendment_condition cac ON cac.catchup_amendment_rule_id = csc.catchup_amendment_rule_id
1790		  WHERE si.purchase_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(i_purchase_ids) sc)
1791		  AND si.schedule_date BETWEEN cl.start_date AND cl.end_date
1792		  AND cl.link_type = k_catchup_channel_link
1793		  AND (cac.within_offer_days IS NULL OR galaxy_util.glx_to_date(vos.start_date) >= (TRUNC(SYSDATE) + cac.within_offer_days))
1794		  AND galaxy_util.glx_to_date_time(vos.end_date, vos.end_time) >= SYSDATE
1795		  AND (cac.catchup_offer_creation_mode IS NULL OR INSTR(DECODE(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'),NVL(csoi.catchup_mode,DECODE(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'))) > 0)
1796		 ) t;
1797
1798v_creation_failed t.logical%TYPE := k_false;
1799v_creation_skipped t.logical%TYPE := k_false;
1800v_created_offer_ids t_number_table;
1801v_offer_ids t.number_table;
1802v_error_messages CLOB;
1803v_offer_item_id vod_item_schedule.item_schedule_id%TYPE;
1804v_catchup_slot_changes_id catchup_slot_changes.catchup_slot_changes_id%TYPE;
1805v_index NUMBER := io_slot_changes_ids.COUNT;
1806v_is_valid_creation t.logical%TYPE := k_true;
1807v_is_record_found t.logical%TYPE := k_false;
1808v_reason_to_fail VARCHAR2(2000);
1809v_purchase_ids t_number_table := t_number_table();
1810BEGIN
1811	o_is_action_processed := k_true;
1812	IF i_action_code = k_act_create_offer OR i_action_code = k_act_create_multi_offer THEN
1813	v_multi_instance_check(i_schedule_process_log_id) := TRUE;
1814		get_catchup_action_cursor(i_action_code => i_action_code,
1815								  i_slot_changes_ids => io_slot_changes_ids,
1816								  o_action_cursor => c_create_offer);
1817		IF c_create_offer%ISOPEN THEN
1818			LOOP
1819				FETCH c_create_offer INTO r_create_offer;
1820				EXIT WHEN c_create_offer%NOTFOUND OR v_creation_failed = k_true OR o_is_action_processed = k_false;
1821				v_is_record_found := k_true;
1822				IF ibms_prop.get_property_number(k_EXCLUDE_FOR_CATCH_UP, r_create_offer.slot_id) = 0 THEN
1823					IF r_create_offer.catchup_rank = 1 THEN
1824						on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => r_create_offer.catchup_slot_changes_id,
1825																				  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
1826																				  i_messages => NULL);
1827						IF TRIM(r_create_offer.offer_item_id) IS NOT NULL THEN
1828							v_is_valid_creation := k_true;
1829						END IF; 
1830					END IF;
1831					IF v_is_valid_creation = k_true THEN
1832						catchup_create_offer(i_slot_date => r_create_offer.slot_date,
1833											 i_slot_start_time => r_create_offer.slot_start_time,
1834											 i_slot_end_time => r_create_offer.slot_end_time,
1835											 i_slot_id => r_create_offer.slot_id,
1836											 i_purchase_id => r_create_offer.purchase_id,
1837											 i_slot_name => r_create_offer.slot_name,
1838											 i_service_id => r_create_offer.service_id,
1839											 i_linear_channel_of_slot => r_create_offer.linear_channel_of_slot,
1840											 i_platform_name => r_create_offer.platform_name,
1841											 i_temp_session_id => i_temp_session_id,
1842											 i_title_id => r_create_offer.title_id,
1843											 i_od_channel_code => r_create_offer.od_channel_code,
1844											 i_channel_code => r_create_offer.channel_code,
1845											 o_creation_failed => v_creation_failed,
1846											 o_creation_skipped => v_creation_skipped,
1847											 o_created_offer_ids => v_created_offer_ids);
1848						SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail, CHR(13) || CHR(10)))
1849						INTO v_reason_to_fail, v_error_messages
1850						FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
1851						IF r_create_offer.catchup_rank = 1 THEN
1852							IF v_creation_skipped = k_true THEN
1853								IF v_created_offer_ids IS NOT NULL AND v_created_offer_ids IS NOT EMPTY THEN
1854									SELECT vosi.item_schedule_id
1855									INTO v_offer_item_id
1856									FROM vod_offer_schedule_item vosi
1857									WHERE vosi.offer_id = v_created_offer_ids(1);
1858								END IF;
1859								on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => r_create_offer.catchup_slot_changes_id,
1860																						  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
1861																						  i_messages => v_reason_to_fail || v_error_messages,
1862																						  i_offer_item_id => v_offer_item_id);
1863							ELSIF v_created_offer_ids IS NULL OR v_created_offer_ids IS empty OR v_creation_failed = k_true THEN
1864								on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => r_create_offer.catchup_slot_changes_id,
1865																						  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1866																						  i_messages => v_reason_to_fail || v_error_messages);
1867								o_is_action_processed := k_false;
1868							ELSE
1869								SELECT vosi.item_schedule_id
1870								INTO v_offer_item_id
1871								FROM vod_offer_schedule_item vosi
1872								WHERE vosi.offer_id = v_created_offer_ids(1);
1873								on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => r_create_offer.catchup_slot_changes_id,
1874																						  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
1875																						  i_messages => NULL,
1876																						  i_offer_item_id => v_offer_item_id);
1877							END IF;
1878						ELSE
1879							IF (v_creation_skipped = k_false AND (v_created_offer_ids IS NULL OR v_created_offer_ids IS empty)) OR v_creation_failed = k_true THEN
1880								on_demand_catchup_internal.ins_catchup_slot_changes_async(i_schedule_process_log_id => r_create_offer.schedule_process_log_id,
1881																						  i_catchup_amendment_rule_id => r_create_offer.catchup_amendment_rule_id,
1882																						  i_offer_item_id => NULL,
1883																						  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1884																						  i_messages => v_reason_to_fail || v_error_messages,
1885																						  o_catchup_slot_changes_id => v_catchup_slot_changes_id);
1886								o_is_action_processed := k_false;
1887								v_index := v_index + 1;
1888								io_slot_changes_ids.EXTEND();
1889								io_slot_changes_ids(v_index) := v_catchup_slot_changes_id;
1890							ELSIF v_created_offer_ids IS NOT NULL AND v_created_offer_ids IS NOT EMPTY THEN
1891								SELECT vosi.item_schedule_id
1892								INTO v_offer_item_id
1893								FROM vod_offer_schedule_item vosi
1894								WHERE vosi.offer_id = v_created_offer_ids(1);
1895								on_demand_catchup_internal.ins_catchup_slot_changes_async(i_schedule_process_log_id => r_create_offer.schedule_process_log_id,
1896																						  i_catchup_amendment_rule_id => r_create_offer.catchup_amendment_rule_id,
1897																						  i_offer_item_id => v_offer_item_id,
1898																						  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
1899																						  i_messages => NULL,
1900																						  o_catchup_slot_changes_id => v_catchup_slot_changes_id);
1901								v_index := v_index + 1;
1902								io_slot_changes_ids.EXTEND();
1903								io_slot_changes_ids(v_index) := v_catchup_slot_changes_id;
1904							END IF;
1905						END IF;
1906					ELSIF v_is_valid_creation = k_false AND r_create_offer.catchup_rank = 1 THEN
1907						on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => r_create_offer.catchup_slot_changes_id,
1908																				  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_skipped_auto ELSE k_stat_skipped_manual END,
1909																				  i_offer_item_id => r_create_offer.offer_item_id,
1910																				  i_messages => 'Offer already exists. Creation skipped');
1911						Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Offer already exists. Creation skipped');
1912					END IF;
1913				END IF;
1914			END LOOP;
1915			IF v_is_record_found = k_false THEN
1916				FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
1917				LOOP
1918					on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
1919																			i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1920																			i_messages => 'Slot not found. Creation failed');
1921					Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Slot not found. Creation failed');
1922					o_is_action_processed := k_false;
1923				END LOOP;
1924			END IF;
1925		END IF;
1926		IF c_create_offer%ISOPEN THEN CLOSE c_create_offer; END IF;
1927	ELSIF i_action_code = k_act_assign_version THEN
1928		FOR v_offer_id IN c_slot_offers LOOP
1929			v_is_record_found := k_true;
1930			on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1931																	  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
1932																	  i_messages => NULL);
1933			v_offer_ids(1) := v_offer_id.offer_id;
1934			catchup_assign_suit_version(i_offers_ids => v_offer_ids,
1935										i_temp_session_id => i_temp_session_id);
1936			SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail,CHR(13) || CHR(10)))
1937			INTO v_reason_to_fail, v_error_messages
1938			FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
1939			IF TRIM(v_error_messages) IS NOT NULL THEN
1940				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1941																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1942																		  i_messages => v_reason_to_fail || v_error_messages);
1943				o_is_action_processed := k_false;
1944				EXIT;
1945			ELSE
1946				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1947																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
1948																		  i_messages => NULL);
1949			END IF;
1950		END LOOP;
1951		IF v_is_record_found = k_false THEN
1952			FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
1953			LOOP
1954				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
1955																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1956																		  i_messages => 'Offer not found. Version assignment failed');
1957				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Offer not found. Version assignment failed');
1958				o_is_action_processed := k_false;
1959			END LOOP;
1960		END IF;
1961	ELSIF i_action_code = k_act_upd_offer_avail THEN
1962		FOR v_offer_id IN c_slot_offers LOOP
1963			v_is_record_found := k_true;
1964			on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1965																	  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
1966																	  i_messages => NULL);
1967			v_offer_ids(1) := v_offer_id.offer_id;
1968			catchup_update_offer_avail(i_offer_ids => v_offer_ids,
1969									   i_temp_session_id => i_temp_session_id);
1970			SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail, CHR(13) || CHR(10)))
1971			INTO v_reason_to_fail, v_error_messages
1972			FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
1973			IF TRIM(v_error_messages) IS NOT NULL THEN
1974				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1975																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1976																		  i_messages => v_reason_to_fail || v_error_messages);
1977				o_is_action_processed := k_false;
1978				EXIT;
1979			ELSE
1980				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1981																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
1982																		  i_messages => NULL);
1983			END IF;
1984		END LOOP;
1985		IF v_is_record_found = k_false THEN
1986			FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
1987			LOOP
1988				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
1989																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
1990																		  i_messages => 'Offer not found. Availability dates modification failed');
1991				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Offer not found. Availability dates modification failed');
1992				o_is_action_processed := k_false;
1993			END LOOP;
1994		END IF;
1995	ELSIF i_action_code = k_act_upd_offer_status THEN
1996		FOR v_offer_id IN c_slot_offers LOOP
1997			v_is_record_found := k_true;
1998			on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
1999																	  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
2000																	  i_messages => NULL,
2001																	  i_offer_item_id => v_offer_id.item_schedule_id);
2002			v_offer_ids(1) := v_offer_id.offer_id;
2003			catchup_update_offer_status(i_offer_ids => v_offer_ids,
2004										i_temp_session_id => i_temp_session_id,
2005										i_offer_status => i_action_value);
2006			SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail, CHR(13) || CHR(10)))
2007			INTO v_reason_to_fail, v_error_messages
2008			FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
2009			IF TRIM(v_error_messages) IS NOT NULL THEN
2010				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
2011																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
2012																		  i_messages => v_reason_to_fail || v_error_messages);
2013				o_is_action_processed := k_false;
2014				EXIT;
2015			ELSE
2016				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
2017																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
2018																		  i_messages => NULL);
2019			END IF;
2020		END LOOP;
2021		IF v_is_record_found = k_false THEN
2022			FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
2023			LOOP
2024				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
2025																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
2026																		  i_messages => 'Offer not found. Status change failed');
2027				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Offer not found. Status change failed');
2028				o_is_action_processed := k_false;
2029			END LOOP;
2030		END IF;
2031	ELSIF i_action_code = k_act_delete_offer THEN
2032		FOR v_offer_id IN c_slot_offers LOOP
2033			v_is_record_found := k_true;
2034			on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
2035																	  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
2036																	  i_messages => NULL);
2037			v_offer_ids(1) := v_offer_id.offer_id;
2038			catchup_delete_offer(i_offer_ids => v_offer_ids,
2039								 i_temp_session_id => i_temp_session_id);
2040			SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail, CHR(13) || CHR(10)))
2041			INTO v_reason_to_fail, v_error_messages
2042			FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
2043			IF TRIM(v_error_messages) IS NOT NULL THEN
2044				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
2045																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
2046																		  i_messages => v_reason_to_fail || v_error_messages);
2047				o_is_action_processed := k_false;
2048				EXIT;
2049			ELSE
2050				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_offer_id.catchup_slot_changes_id,
2051																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
2052																		  i_messages => NULL,
2053																		  i_offer_item_id => NULL);
2054			END IF;
2055		END LOOP;
2056		IF v_is_record_found = k_false THEN
2057			FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
2058			LOOP
2059				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
2060																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
2061																		  i_messages => 'Offer not found. Deletion failed');
2062				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Offer not found. Deletion failed');
2063				o_is_action_processed := k_false;
2064			END LOOP;
2065		END IF;
2066	ELSIF i_action_code = k_act_delete_multi_offer THEN
2067	SELECT DISTINCT purchase_id
2068	BULK COLLECT INTO v_purchase_ids
2069	FROM (SELECT ppv.purchase_id
2070		  FROM catchup_slot_changes csc
2071		  JOIN vod_item_schedule csc_vis ON csc_vis.item_schedule_id = csc.offer_item_id
2072		  JOIN purchase ppv ON ppv.purchase_id = csc_vis.purchase_id
2073		  WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc)
2074		  UNION
2075		  SELECT ppv.purchase_id
2076		  FROM catchup_slot_changes csc
2077		  JOIN vod_item_schedule csc_vis ON csc_vis.item_schedule_id = csc.offer_item_id
2078		  JOIN purchase ppv ON ppv.parent_purchase_id = (select pr.parent_purchase_id from purchase pr where pr.purchase_id = csc_vis.purchase_id)
2079		  WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc)
2080		  UNION
2081		  SELECT ppv.purchase_id
2082		  FROM catchup_slot_changes csc
2083		  JOIN schedule_process_log spl ON spl.schedule_process_log_id = csc.schedule_process_log_id AND spl.schedule_process_log_type IN (k_PLCREA, k_PLPASS)
2084		  JOIN schedule_item_base_simple_view sibv ON sibv.schedule_item_id = spl.schedule_process_object_id
2085		  JOIN purchase ppv ON ppv.purchase_id = sibv.purchase_id
2086		  WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc)
2087		  UNION
2088		  SELECT ppv.purchase_id
2089		  FROM catchup_slot_changes csc
2090		  JOIN schedule_process_log spl ON spl.schedule_process_log_id = csc.schedule_process_log_id AND spl.schedule_process_log_type IN (k_PLCREA, k_PLPASS)
2091		  JOIN schedule_item_base_simple_view sibv ON sibv.schedule_item_id = spl.schedule_process_object_id
2092		  JOIN purchase ppv ON ppv.parent_purchase_id = (select pr.parent_purchase_id from purchase pr where pr.purchase_id = sibv.purchase_id)
2093		  WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc)
2094		  UNION
2095		  SELECT DECODE(spl.schedule_process_log_type, k_PLPVAC, to_number(NVL(TRIM(spl.old_value), k.k_niu_number)), k_PLDEL, DECODE(spl.purchase_id, 0, k.k_niu_number, spl.purchase_id), k.k_niu_number)
2096		  FROM catchup_slot_changes csc
2097		  JOIN schedule_process_log spl on spl.schedule_process_log_id = csc.schedule_process_log_id and spl.schedule_process_log_type in (k_PLDEL, k_PLPVAC)
2098		  WHERE csc.catchup_slot_changes_id IN (SELECT /*+ cardinality(sc 1) */ sc.column_value FROM TABLE(io_slot_changes_ids) sc));
2099		FOR v_delete_multi_offer IN c_delete_multi_offers(v_purchase_ids) LOOP
2100			v_is_record_found := k_true;
2101			IF v_delete_multi_offer.catchup_rank = 1 THEN
2102				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_delete_multi_offer.catchup_slot_changes_id,
2103																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_in_progress_auto ELSE k_stat_in_progress_manual END,
2104																		  i_messages => NULL);
2105			END IF;
2106			v_offer_ids(1) := v_delete_multi_offer.offer_id;
2107			v_offer_multi_deletion(v_delete_multi_offer.item_schedule_id) := v_delete_multi_offer.schedule_process_log_id;
2108			catchup_delete_offer(i_offer_ids => v_offer_ids,
2109								 i_temp_session_id => i_temp_session_id);
2110			SELECT stragg_distinct(results.offer_created), stragg2_clob(agg_input_clob(results.reason_to_fail, CHR(13) || CHR(10)))
2111			INTO v_reason_to_fail, v_error_messages
2112			FROM TABLE(get_auto_catchup_result(i_temp_session_id)) results;
2113			IF TRIM(v_error_messages) IS NOT NULL AND v_delete_multi_offer.catchup_rank = 1 THEN
2114				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_delete_multi_offer.catchup_slot_changes_id,
2115																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_failed_auto ELSE k_stat_failed_manual END,
2116																		  i_messages => v_reason_to_fail || v_error_messages);
2117				o_is_action_processed := k_false;
2118				EXIT;
2119			ELSIF TRIM(v_error_messages) IS NULL AND v_delete_multi_offer.catchup_rank = 1 THEN
2120				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_delete_multi_offer.catchup_slot_changes_id,
2121																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
2122																		  i_messages => NULL,
2123																		  i_offer_item_id => NULL);
2124				FOR v_related_changes IN
2125				(SELECT csc.catchup_slot_changes_id
2126				 FROM catchup_slot_changes csc
2127				 WHERE csc.offer_item_id = v_delete_multi_offer.item_schedule_id)
2128				LOOP
2129					on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_related_changes.catchup_slot_changes_id,
2130																			  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
2131																			  i_messages => 'Deleted as Offer Multiple Instance',
2132																			  i_offer_item_id => NULL);
2133				END LOOP;
2134			END IF;
2135		END LOOP;
2136		IF v_is_record_found = k_false THEN
2137			FOR indx IN io_slot_changes_ids.FIRST .. io_slot_changes_ids.LAST
2138			LOOP
2139				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => io_slot_changes_ids(indx),
2140																		  i_status => CASE WHEN i_is_auto_executed = k_true THEN k_stat_auto_success ELSE k_stat_manual_processed END,
2141																		  i_messages => 'Offer not found. Deletion skipped');
2142			END LOOP;
2143		END IF;
2144	END IF;
2145END apply_catchup_amend_action;
2146
2147PROCEDURE apply_catchup_amendment_rule(i_catchup_slot_changes_id IN catchup_slot_changes.catchup_slot_changes_id%TYPE,
2148									   i_is_auto_executed IN t.logical%TYPE DEFAULT k_false,
2149									   i_temp_session_id IN glx_session_tmp_generic_select.temp_session_id%TYPE)
2150IS
2151CURSOR c_processing_actions IS
2152	SELECT cpa.action,
2153		   cpa.action_value
2154	FROM catchup_slot_changes csc
2155	JOIN catchup_amendment_rule car ON car.catchup_amendment_rule_id = csc.catchup_amendment_rule_id
2156	JOIN catchup_processing_method cpm ON cpm.catchup_processing_method_id = car.processing_method_id
2157	JOIN catchup_processing_action cpa ON cpa.catchup_processing_method_id = cpm.catchup_processing_method_id
2158	WHERE csc.catchup_slot_changes_id = i_catchup_slot_changes_id
2159	AND csc.status NOT IN (k_stat_auto_success, k_stat_manual_processed, k_stat_skipped_manual)
2160	ORDER BY cpa.order_num;
2161v_catchup_slot_changes_ids t_number_table := t_number_table();
2162v_is_action_processed t.logical%TYPE;
2163v_is_record_found t.logical%TYPE := k_false;
2164v_change_status catchup_slot_changes.status%TYPE;
2165v_schedule_process_log_id schedule_process_log.schedule_process_log_id%TYPE;
2166BEGIN
2167	SELECT csc.status,
2168		   csc.schedule_process_log_id
2169	INTO v_change_status,
2170		 v_schedule_process_log_id
2171	FROM catchup_slot_changes csc
2172	WHERE csc.catchup_slot_changes_id = i_catchup_slot_changes_id;
2173	v_catchup_slot_changes_ids.EXTEND();
2174	v_catchup_slot_changes_ids(1) := i_catchup_slot_changes_id;
2175	FOR v_processing_action IN c_processing_actions LOOP
2176		v_is_record_found := k_true;
2177		apply_catchup_amend_action(io_slot_changes_ids => v_catchup_slot_changes_ids,
2178								   i_action_code => v_processing_action.action,
2179								   i_action_value => v_processing_action.action_value,
2180								   i_is_auto_executed => i_is_auto_executed,
2181								   i_temp_session_id => i_temp_session_id,
2182								   i_schedule_process_log_id => v_schedule_process_log_id,
2183								   o_is_action_processed => v_is_action_processed);
2184		EXIT WHEN v_is_action_processed = k_false;
2185	END LOOP;
2186	IF v_is_record_found = k_false AND v_change_status != k_stat_auto_success AND v_change_status != k_stat_manual_processed THEN
2187		on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => i_catchup_slot_changes_id,
2188																  i_status => k_stat_skipped_auto,
2189																  i_messages => 'Processing Action not found');
2190	END IF;
2191	IF v_is_action_processed = k_true THEN
2192		COMMIT;
2193	ELSE
2194		ROLLBACK;
2195	END IF;
2196END apply_catchup_amendment_rule;
2197
2198PROCEDURE get_slot_changes_cursor(o_catchup_slot_changes OUT on_demand_catchup_util.t_c_catchup_slot_changes)
2199IS
2200BEGIN
2201	IF on_demand_setup.g_enable_sched_changes_reproc = k_true THEN
2202		o_catchup_slot_changes := on_demand_catchup_util.get_cur_all_slot_changes;
2203	ELSE
2204		o_catchup_slot_changes := on_demand_catchup_util.get_cur_new_slot_changes_only;
2205	END IF;
2206END get_slot_changes_cursor;
2207
2208PROCEDURE processing_slot_changes
2209IS
2210c_slot_changes on_demand_catchup_util.t_c_catchup_slot_changes;
2211v_slot_changes on_demand_catchup_util.t_r_catchup_slot_changes;
2212v_temp_session_id glx_session_tmp_generic_select.temp_session_id%TYPE;
2213v_catchup_amendment_rule_id catchup_slot_changes.catchup_amendment_rule_id%TYPE;
2214v_auto_update catchup_amendment_rule.auto_update%TYPE;
2215v_catchup_slot_changes_id catchup_slot_changes.catchup_slot_changes_id%TYPE;
2216v_catchup_mode catchup_slot_offer_item.catchup_mode%TYPE;
2217v_offer_item_id catchup_slot_offer_item.offer_item_id%TYPE;
2218v_status vod_offer_schedule.status%TYPE;
2219v_platform_id vod_platform.vod_platform_id%TYPE;
2220v_start_date vod_offer_schedule.start_date%TYPE;
2221v_processing_locked t.logical%TYPE;
2222v_offer_item_exists t.logical%type;
2223BEGIN
2224	v_process_slot_changes_errlog.DELETE;
2225	v_temp_session_id := session_util.GET_TEMP_SESSION_ID();
2226	get_slot_changes_cursor(o_catchup_slot_changes => c_slot_changes);
2227	v_offer_multi_deletion.DELETE;
2228	v_multi_instance_check.DELETE;
2229	IF c_slot_changes%ISOPEN THEN
2230	LOOP
2231		FETCH c_slot_changes INTO v_slot_changes;
2232		-- If Offer Item was already deleted as part of Multiple Instances action, then no need to process changes again for the same Log Id
2233		EXIT WHEN c_slot_changes%NOTFOUND;
2234		CONTINUE WHEN (v_offer_multi_deletion.EXISTS(v_slot_changes.offer_item_id) AND v_offer_multi_deletion(v_slot_changes.offer_item_id) = v_slot_changes.schedule_process_log_id)
2235				 OR (v_multi_instance_check.EXISTS(v_slot_changes.schedule_process_log_id) AND v_multi_instance_check(v_slot_changes.schedule_process_log_id) = TRUE);
2236
2237		v_current_sched_proc_log_id := v_slot_changes.schedule_process_log_id;
2238		v_current_change_type := v_slot_changes.change_type_description;
2239
2240		SELECT 'Channel ' || ch.channel_name || ' ' ||
2241			   galaxy_util.glx_to_date_str(v_slot_changes.slot_schedule_date) || ' ' ||
2242			   EXTRACT(HOUR FROM galaxy_util.glx_to_timespan(v_slot_changes.slot_start_time)) || ':' ||
2243			   LPAD(EXTRACT(MINUTE FROM galaxy_util.glx_to_timespan(v_slot_changes.slot_start_time)), 2, 0) || '-' ||
2244			   EXTRACT(HOUR FROM galaxy_util.glx_to_timespan(v_slot_changes.slot_start_time + v_slot_changes.slot_duration)) || ':' ||
2245			   LPAD(EXTRACT(MINUTE FROM galaxy_util.glx_to_timespan(v_slot_changes.slot_start_time + v_slot_changes.slot_duration)), 2, 0)
2246		INTO v_current_slot_details
2247		FROM channel ch
2248		WHERE ch.channel_code = v_slot_changes.channel_code;
2249
2250		IF v_slot_changes.catchup_rank = 1 THEN
2251			BEGIN
2252				SELECT csoi.catchup_mode,
2253					   csoi.offer_item_id,
2254					   vos.start_date,
2255					   vos.status,
2256					   vos.platform_id,
2257					   on_demand_ibms_prop.is_linear_processing_blocked(vos.status)
2258				INTO v_catchup_mode,
2259					 v_offer_item_id,
2260					 v_start_date,
2261					 v_status,
2262					 v_platform_id,
2263					 v_processing_locked
2264				FROM catchup_slot_offer_item csoi
2265				JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = csoi.offer_item_id
2266				JOIN vod_offer_schedule vos ON vos.offer_id = vosi.offer_id
2267				WHERE csoi.slot_id = v_slot_changes.schedule_process_object_id
2268				AND csoi.offer_item_id = NVL(v_slot_changes.offer_item_id, csoi.offer_item_id)
2269				AND ROWNUM = 1;
2270			EXCEPTION
2271				WHEN no_data_found THEN
2272					v_catchup_mode := NULL;
2273					v_offer_item_id := NULL;
2274					v_start_date := NULL;
2275					v_status := NULL;
2276					v_platform_id := NULL;
2277					v_processing_locked := k_false;
2278			END;
2279		ELSE
2280			IF v_slot_changes.offer_item_id IS NULL THEN
2281				v_offer_item_exists := k_false;
2282			ELSE
2283				SELECT COUNT(1)
2284				INTO v_offer_item_exists
2285				FROM vod_item_schedule vis
2286				WHERE vis.item_schedule_id = v_slot_changes.offer_item_id;
2287			END IF;
2288			BEGIN
2289				SELECT csoi.catchup_mode,
2290					   csoi.offer_item_id,
2291					   vos.start_date,
2292					   vos.status,
2293					   vos.platform_id,
2294					   on_demand_ibms_prop.is_linear_processing_blocked(vos.status)
2295				INTO v_catchup_mode,
2296					 v_offer_item_id,
2297					 v_start_date,
2298					 v_status,
2299					 v_platform_id,
2300					 v_processing_locked
2301				FROM catchup_slot_offer_item csoi
2302				JOIN vod_offer_schedule_item vosi ON vosi.item_schedule_id = csoi.offer_item_id
2303				JOIN vod_offer_schedule vos ON vos.offer_id = vosi.offer_id
2304				LEFT JOIN catchup_slot_changes csc ON csc.offer_item_id = csoi.offer_item_id 
2305				WHERE csoi.slot_id = v_slot_changes.schedule_process_object_id
2306				AND ((v_offer_item_exists = k_false AND NOT EXISTS (SELECT 1 
2307																	FROM catchup_slot_changes csc_exists
2308																	WHERE csc_exists.schedule_process_log_id = v_slot_changes.schedule_process_log_id
2309																	AND csc_exists.offer_item_id = csoi.offer_item_id)
2310					  AND ROWNUM = 1)
2311				 OR (csc.offer_item_id IS NULL AND csoi.offer_item_id = v_slot_changes.offer_item_id));
2312			EXCEPTION
2313				WHEN no_data_found THEN
2314					v_catchup_mode := NULL;
2315					v_offer_item_id := NULL;
2316					v_start_date := NULL;
2317					v_status := NULL;
2318					v_platform_id := NULL;
2319					v_processing_locked := k_false;
2320			END;
2321		END IF;
2322		IF NVL(v_processing_locked, k_false) = k_false THEN
2323			BEGIN
2324				SELECT catchup_amendment_rule_id,
2325					   auto_update
2326				INTO v_catchup_amendment_rule_id,
2327					 v_auto_update
2328				FROM
2329					(SELECT DISTINCT car.catchup_amendment_rule_id, car.auto_update, car.priority
2330					 FROM catchup_amend_change_type cact
2331					 JOIN catchup_amendment_rule car ON car.catchup_amendment_rule_id = cact.catchup_amendment_rule_id
2332					 LEFT OUTER JOIN catchup_amendment_condition cac ON cac.catchup_amendment_rule_id = car.catchup_amendment_rule_id
2333					 LEFT OUTER JOIN catchup_amend_ofr_status caos ON caos.catchup_amendment_condition_id = cac.catchup_amendment_condition_id
2334					 LEFT OUTER JOIN catchup_amendment_service cas ON cas.catchup_amendment_condition_id = cac.catchup_amendment_condition_id
2335					 LEFT OUTER JOIN catchup_amendment_channel cach ON cach.catchup_amendment_condition_id = cac.catchup_amendment_condition_id
2336					 WHERE cact.change_type = v_slot_changes.change_type_code
2337					 AND NVL(galaxy_util.glx_to_date(v_start_date),sysdate) >= decode(NVL(cac.within_offer_days, 0),0,NVL(galaxy_util.glx_to_date(v_start_date),sysdate), sysdate + cac.within_offer_days)
2338					 AND INSTR(decode(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'),NVL(v_catchup_mode,decode(cac.catchup_offer_creation_mode,2,'A',1,'M','A;M;'))) > 0
2339					 AND NVL(caos.offer_status, NVL(v_status, '~')) = NVL(v_status, NVL(caos.offer_status, '~'))
2340					 AND NVL(cas.service_id, NVL(v_platform_id, -1)) = NVL(v_platform_id, NVL(cas.service_id, -1))
2341					 AND v_slot_changes.channel_code = NVL(cach.channel_code,v_slot_changes.channel_code)
2342					 ORDER BY car.priority, car.catchup_amendment_rule_id DESC)
2343				WHERE ROWNUM = 1;
2344			EXCEPTION
2345				WHEN no_data_found THEN
2346					v_catchup_amendment_rule_id := NULL;
2347					v_auto_update := NULL;
2348			END;
2349
2350			IF v_catchup_amendment_rule_id IS NULL THEN
2351				Log_Processing_Error(i_error_message => 'Failed to process ' || v_current_change_type || ' for ' || v_current_slot_details || '; reason: Processing Method not found');
2352			END IF;
2353
2354			IF v_slot_changes.catchup_slot_changes_id IS NULL THEN
2355				on_demand_catchup_internal.ins_catchup_slot_changes_async(i_schedule_process_log_id => v_slot_changes.schedule_process_log_id,
2356																		  i_catchup_amendment_rule_id => v_catchup_amendment_rule_id,
2357																		  i_offer_item_id =>  CASE WHEN v_slot_changes.change_type_code = k_change_type_SCHLOGPLCREA OR v_offer_item_id IS NULL 
2358																								   THEN v_slot_changes.offer_item_id ELSE v_offer_item_id END,
2359																		  i_status => CASE WHEN v_auto_update = 1 AND v_catchup_amendment_rule_id IS NOT NULL THEN k_stat_in_progress_auto
2360																						   WHEN v_catchup_amendment_rule_id IS NULL THEN k_stat_skipped_auto
2361																						   ELSE k_stat_pending END,
2362																		  i_messages => CASE WHEN v_catchup_amendment_rule_id IS NULL THEN 'Processing Method not found' END,
2363																		  o_catchup_slot_changes_id => v_catchup_slot_changes_id);
2364			ELSE
2365				v_catchup_slot_changes_id := v_slot_changes.catchup_slot_changes_id;
2366				v_auto_update := CASE WHEN v_catchup_amendment_rule_id IS NULL THEN k_false ELSE k_true END;
2367				on_demand_catchup_internal.set_catchup_change_status_asnc(i_catchup_slot_changes_id => v_catchup_slot_changes_id,
2368																		  i_status => CASE WHEN v_catchup_amendment_rule_id IS NULL THEN k_stat_skipped_auto ELSE k_stat_in_progress_auto END,
2369																		  i_messages => CASE WHEN v_catchup_amendment_rule_id IS NULL THEN 'Processing Method not found' END,
2370																		  i_catchup_amendment_rule_id => v_catchup_amendment_rule_id,
2371																		  i_offer_item_id => CASE WHEN v_slot_changes.change_type_code = k_change_type_SCHLOGPLCREA OR v_offer_item_id IS NULL THEN v_slot_changes.offer_item_id ELSE v_offer_item_id END);
2372			END IF;
2373			IF v_auto_update = 1 THEN
2374				apply_catchup_amendment_rule(i_catchup_slot_changes_id => v_catchup_slot_changes_id,
2375											 i_is_auto_executed => k_true,
2376											 i_temp_session_id => v_temp_session_id);
2377			END IF;
2378		END IF;
2379	END LOOP;
2380	END IF;
2381	IF c_slot_changes%ISOPEN THEN CLOSE c_slot_changes; END IF;
2382	iface.set_client_info(p_info => NULL);
2383	Log_Processing_Errors;
2384	on_demand_workflow.raise_schedule_changes_process(i_file_id => CASE WHEN v_process_slot_changes_errlog.COUNT > 0 THEN iface.get_io_file_id ELSE 0 END);
2385EXCEPTION
2386	WHEN OTHERS THEN
2387		iface.set_client_info(p_info => SQLERRM || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
2388		RAISE;
2389END processing_slot_changes;
2390
2391END on_demand_catchup;
2392/