kn6zpkCk

1CREATE OR REPLACE PACKAGE BODY ibms_audit_api
2--
3-- (c) 2001 Pilat Media Ltd.
4-- All rights reserved.
5--
6-- Pilat Media Ltd considers all information contained in or disclosed by this
7-- script confidential and proprietary. Pilat Media Ltd. reserves the right to
8-- use this design in other projects without reference to the recipient.
9--
10-- By accepting this material, the recipient agrees that this material and the
11-- information contained therein will be  held  in  confidence  and  in  trust
12-- and will not be copied or disclosed in whole or in part to any third party.
13--
14-- The following code is managed and updated by  Pilat Media Ltd via MS Source Safe.
15-- This is the latest source code version for the following  IBMS  database objects.
16
17/************************************************************************
18 * PLEASE DO NOT TOUCH THIS AREA                                        *
19 *                                                                      *
20 * $Revision: 300302 $(Source Safe Last Version)                             *
21 * $Author: andreit $(Last Source Safe Author)                          *
22 * $Date: 2016-11-29 15:59:00 +0000 (Tue, 29 Nov 2016) $ (Last Update)                                 *
23* $Product Version: 6.04.11.34.05 $ *
24 ************************************************************************/
25IS
26/* ---- History ----
27Date        Person      	Tracker		Comment
28------------------------------------------------------------------------------
2919/04/2012  Tamir/Bakulesh  357226001   Created
3030/04/2012  Tlevin          357226001   Fix QueryAuditByTemplate: Make the query for loggedAttributes,for all change types, shorter
3103/05/2012  Tlevin          357226001   Fix QueryAuditByTemplate: Replace stragg_distinct with stragg(distinct) due to bug in stargg_distinct
3206/05/2012  Tlevin          357226001   Fix QueryAuditByTemplate: Get the view name from procedure & fix for long column names (their tn column is modified)
3307/05/2012  Tlevin          357226001   Fix findOfferItemsByCastMember: move the flag of not deleting temp table, so the table won't be deleted after each run of findOfferItemsByTitleModify
3407/05/2012	Bakulesh P		357226010	Modified 3 apis to cover deletion (version, listing text set and licesed rights)
3522/05/2012	Bakulesh P		357226011	few changes due to spec mismatch and corrections
3611/06/2012	Bakulesh P		357226009   6.04.02 equivalent
3704/03/2012	Guy Schachar	369861004	6.04.08 Added QueryAuditByTables and AuditExistByTables
3818/03/2013	Tlevin			368935012	6.04.05 Added optional element of i_interface_id, to support many interfaces + FindOfferItemsByShowModify
3924/04/2013	GShachar		369861004	6.04.08 Add TRIM
4014/07/2013	Tlevin			371304002	6.04.09	Fix get_table_primary_keys to get only audited primary keys
4112/09/2013	GShachar		375772003	6.04.10 Support audit sets
4230/09/2013	GShachar		379863002	6.04.10 Change select in get_attributes_to_log_by_set
4323/10/2013	Tlevin			367669011	6.04.10 Added FindOfferItemsBySeason,get_affected_offer_items,get_offer_items_for_tables
4415/12/2013	Gshachar		379951006	6.04.11 add possibility to query with more than one PK
4524/12/2013	GShachar		382901002	6.04.11	Change get_attributes_to_log_by_set not to fail when a column is missing
4602/04/2014	Bakulesh P		378695005	CR - added new SPs
4710/07/2014	smaliss			378695028	Fixed getAuditItemChangedAttr select, to get changed attribute
4811/10/2016	ATurcu			412750001	Added is_offer_valid and fixed/updated existing APIs get_affected_offer_items, get_offer_items_for_tables
4925/10/2016	ATurcu			412750001	Changed is_offer_valid to function and getOfferItemsForTable to support is_offer_valid check for filtering offers by Service
5007/11/2016	ATurcu			412750001	Fixed getOfferItemsForTable to support sub type objects like Show/Season/LTS/EPG/Cast
5121/11/2016	ATurcu			412750001	Small performance enhancement to getOfferItemsForTable
5229/11/2016	ATurcu			412750001	Fixed bug for getting all ObjectTypes/ObjectSubTypes and made perfomance enhancements for getOfferItemsForTable
53------------------------------------------------------------------------------
54*/
55	--- C o n s t a n t s-----
56	k_comma						CONSTANT	CHAR(1) := ',';
57    k_delimiter                 CONSTANT 	CHAR(1) := '~';
58    k_colon                     CONSTANT 	CHAR(1) := ':';
59	k_action_create             CONSTANT 	PLS_INTEGER := 1;
60   	k_action_update             CONSTANT 	PLS_INTEGER := 2;
61   	k_action_delete             CONSTANT 	PLS_INTEGER := 4;
62	k_dummy_id					CONSTANT 	PLS_INTEGER := 90000694;
63	k_title_object_type			CONSTANT 	table_line.table_code%TYPE := 'TI';
64	k_series_object_type		CONSTANT 	table_line.table_code%TYPE := 'SE';
65	k_version_object_type		CONSTANT 	table_line.table_code%TYPE := 'VE';
66	k_media_object_type			CONSTANT 	table_line.table_code%TYPE := 'MI';
67	k_media_match_object_type	CONSTANT 	table_line.table_code%TYPE := 'MM';
68	k_castMember_object_type	CONSTANT 	table_line.table_code%TYPE := 'CF'; 
69	k_show_object_type			CONSTANT 	table_line.table_code%TYPE := 'SW'; 
70	k_season_object_type		CONSTANT 	table_line.table_code%TYPE := 'MN';
71	k_program_object_type		CONSTANT    table_line.table_reference%TYPE := 'COMOBJ____PR';
72	k_anytime_interface_type	CONSTANT 	table_line.table_code%TYPE := '694';
73	k_title_type_table_id		CONSTANT 	table_line.table_id%TYPE := 'TTLTYP';
74	k_object_type_tl            CONSTANT 	table_line.table_id%TYPE := 'COMOBJ';
75	k_wf_audit_tl               CONSTANT 	table_line.table_id%TYPE := 'WFAUDT';
76	k_vodofr_tl					CONSTANT 	table_line.table_id%TYPE := 'VODOFR';
77	k_719_interface_type		CONSTANT 	interface_parameter.interface_type%TYPE:= '719';
78
79	-- Interface Property
80	k_694_title_version_ut_prop	CONSTANT 	ibms_prop.t_property_code := '694_TITLE_VERSION_UT';
81	k_719_ftch_frm_service_prop	CONSTANT 	ibms_prop.t_property_code :=  '719_FTCH_FRM_SERVICE';
82	k_719_title_version_ut_prop	CONSTANT 	ibms_prop.t_property_code :=  '719_TITLE_VERSION_UT';
83	k_719_elig_sts_changes_prop	CONSTANT 	ibms_prop.t_property_code :=  '719_ELIGABLE_CHNG_ST';
84
85	
86
87	--global variables
88	g_delete_gtt_table			k.logical%TYPE;
89	g_item_count				PLS_INTEGER := 0;
90	g_interface_id				table_line.table_line_isn%TYPE;
91	g_last_track_cols_ts		DATE := g.now - 10000; --random old date to get track columns first time;
92	--pl/sql types
93	--
94	-- pl/sql table to store IMBS Table Columns (tracker fields for each table)
95	TYPE t_table_columns IS TABLE OF t.max_varchar2%TYPE INDEX BY VARCHAR2(30);
96	TYPE r_table_columns IS RECORD(
97								table_columns	t_table_columns
98								  );
99	TYPE t_intf_table_cols IS TABLE OF r_table_columns INDEX BY VARCHAR2(30);
100	tbl_table_columns           t_intf_table_cols;
101	--
102	TYPE t_ignore_title_type IS TABLE OF k.logical%TYPE INDEX BY table_line.table_code%TYPE;
103	tbl_ignore_title_type		t_ignore_title_type;
104
105	-------------------------------------------------------------------------------------------------
106	-- Procedure : mergeErrors
107	-- Added By Bakulesh @12/04/2012
108	-- Description : open error response cursor and call error handling api to add error in pl/sql table
109	--				This will be commulate all errors if core api returns error and called more than once
110	--				e.g. in findOfferItemByTileModify calls 3 core API, if all 3 returns e.g. not data found
111	--					this process will add no data found for all 3 core APIs
112	-------------------------------------------------------------------------------------------------
113	PROCEDURE	mergeErrors(
114						i_error_response		IN	SYS_REFCURSOR
115							)
116	IS
117		k_procedure_name				CONSTANT VARCHAR2(60) := 'mergeErrors';
118		--local variables
119		v_error_response		r_response := r_response(
120													response_seq => NULL,
121													broadcaster_ref => NULL,
122													response_result => NULL,
123													msg_type => NULL,
124													msg_code => NULL,
125													msg => NULL
126														);
127	BEGIN
128		--
129		IF	i_error_response%ISOPEN THEN
130			LOOP
131				FETCH	i_error_response	INTO	v_error_response.response_seq,
132													v_error_response.broadcaster_ref,
133													v_error_response.response_result,
134													v_error_response.msg_type,
135													v_error_response.msg_code,
136													v_error_response.msg;
137				EXIT WHEN i_error_response%NOTFOUND;
138				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
139													i_message         => v_error_response.msg,
140													i_broadcaster_ref => v_error_response.broadcaster_ref,
141													i_sequence        => v_error_response.response_seq,
142													i_result          => v_error_response.response_result,
143													i_message_type    => v_error_response.msg_type,
144													i_message_code    => v_error_response.msg_code);
145			END LOOP;
146		END IF;--cursor has data
147		--close ref cursor
148		IF	i_error_response%ISOPEN THEN
149			CLOSE i_error_response;
150		END IF;
151	EXCEPTION
152		WHEN	OTHERS THEN
153				--g.debug('mergeErrors, Error is ', SQLERRM);
154				--close ref cursor
155				IF	i_error_response%ISOPEN THEN
156					CLOSE i_error_response;
157				END IF;
158				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
159													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
160																	 'SQLERRM: ' || SQLERRM,
161													i_broadcaster_ref => NULL,
162													i_sequence        => 1,
163													i_result          => k_rslt_tp_notvalid,
164													i_message_type    => k_err_msg_type,
165													i_message_code    => SQLCODE);
166	END mergeErrors;
167	-------------------------------------------------------------------------------------------------
168	-- Procedure : getSettings
169	-- Added By Bakulesh @21/05/2012
170	-- Description : prepare pl/sql table for interface property Ignore Title/Version Modification for Title-Types?.
171	--		prepare once for one session to save some time
172	-------------------------------------------------------------------------------------------------
173	PROCEDURE	getSettings(i_interface_id  IN table_line.table_line_isn%TYPE DEFAULT NULL)
174	IS
175		k_procedure_name				CONSTANT VARCHAR2(60) := 'getSettings';
176		v_interface_code				table_line.table_code%TYPE;
177		v_property_code					ibms_prop.t_property_code;
178	BEGIN
179		IF(i_interface_id IS NULL) THEN
180			g_interface_id := iface.interface_id(i_interface_type => k_anytime_interface_type);--694
181		ELSE
182			g_interface_id := i_interface_id;
183		END IF;
184
185		SELECT	table_code
186		INTO	v_interface_code
187		FROM 	table_line
188		WHERE	table_line_isn = g_interface_id;
189
190		v_property_code := v_interface_code || '_TITLE_VERSION_UT';
191		tbl_ignore_title_type.DELETE;
192		FOR v_ttltyp IN (SELECT	table_code,
193								table_line_isn
194						 FROM	table_line ttltyp
195						 WHERE	table_id = k_title_type_table_id) --TTLTYP
196		LOOP
197			tbl_ignore_title_type(v_ttltyp.table_code) := ibms_prop.get_property_number(
198																				i_property_code => v_property_code,
199																				i_object_id => g_interface_id,
200																				i_level_1_id => v_ttltyp.table_line_isn
201																						);
202		END LOOP;
203	EXCEPTION
204		WHEN	OTHERS THEN
205				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
206													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
207																	 'SQLERRM: ' || SQLERRM,
208													i_broadcaster_ref => NULL,
209													i_sequence        => 1,
210													i_result          => k_rslt_tp_notvalid,
211													i_message_type    => k_err_msg_type,
212													i_message_code    => SQLCODE);
213	END getSettings;
214	-------------------------------------------------------------------------------------------------
215	-- Procedure : findUnExpiredOfferItems
216	-- Added By Bakulesh @28/03/2012
217	-- Description : for passed in ref cursor with offer item ids, get offer item basic details,
218	--				 validate offer is expired or not and return ref cursor with basic item details
219	-------------------------------------------------------------------------------------------------
220
221	PROCEDURE findUnExpiredOfferItems(
222								i_offer_item_ids		IN	SYS_REFCURSOR
223									 )
224	IS
225		v_offer_item_id				vod_item_schedule.item_schedule_id%TYPE;
226		v_error_reported			t.logical%TYPE;
227		v_end_date					DATE;
228		v_expiry_date				DATE;
229		-- record type
230		TYPE r_offerItemBasicDetails IS RECORD
231											(
232											offerItemId 			vod_item_schedule.item_schedule_id%TYPE,
233											offerId					vod_offer_schedule.offer_id%TYPE,
234											offerName				vod_offer_schedule.offer_name%TYPE,
235											serviceID				vod_platform.vod_platform_id%TYPE,
236											serviceName				vod_platform.platform_name%TYPE,
237											offerStatusCode			table_line.table_code%TYPE,
238											offerStausDescr			table_line.table_description%TYPE,
239											offerStatusISN			table_line.table_line_isn%TYPE,
240											offerAvaibilityEndDate	vod_item_schedule.end_date%TYPE,
241											offerAvaibilityEndTime	vod_item_schedule.end_time%TYPE,
242											offerExpiryDate			vod_offer_schedule.expiry_date%TYPE,
243											offerExpiryTime			vod_offer_schedule.expiry_time%TYPE
244											);
245		t_offerItemBasicDetails		r_offerItemBasicDetails;
246		-- ref cursors
247		c_properties				SYS_REFCURSOR;
248		c_error_response			SYS_REFCURSOR;
249		c_offerItemBasicDetails		SYS_REFCURSOR;
250	BEGIN
251		v_error_reported := 0;
252		--g.debug('g_delete_gtt_table',g_delete_gtt_table);
253		--
254		IF	i_offer_item_ids%ISOPEN THEN
255			LOOP
256				FETCH i_offer_item_ids INTO v_offer_item_id;
257				EXIT WHEN i_offer_item_ids%NOTFOUND;
258				on_demand_api.queryOfferItemBasicDetails(
259												i_offer_item_id  		=> v_offer_item_id,
260												i_required_attrs 		=> NULL,
261												o_OfferItemBasicDetails => c_offerItemBasicDetails,
262												o_properties 			=> c_properties,
263												o_error_reported 		=> v_error_reported,
264												o_error_response 		=> c_error_response
265													 	);
266				--g.debug('error',v_error_reported, v_offer_item_id);
267				IF 	v_error_reported = k_false THEN
268					LOOP
269						FETCH c_offerItemBasicDetails INTO t_offerItemBasicDetails;
270						EXIT WHEN c_offerItemBasicDetails%NOTFOUND;
271						--g.debug('avail end date and time', t_offerItemBasicDetails.offerAvaibilityEndDate, TSTR(NVL(t_offerItemBasicDetails.offerAvaibilityEndTime, 0)));
272						--g.debug('exp end date and time', t_offerItemBasicDetails.offerAvaibilityEndDate, TSTR(NVL(t_offerItemBasicDetails.offerAvaibilityEndTime, 0)));
273						--calculate avaibility end date
274						IF	NVL(t_offerItemBasicDetails.offerAvaibilityEndDate, k.min_date) != k.min_date THEN
275							v_end_date := TO_DATE(t_offerItemBasicDetails.offerAvaibilityEndDate||k_space||TSTR(NVL(t_offerItemBasicDetails.offerAvaibilityEndTime, 0)), k_datemask_yymmddhhmiss);
276						ELSE
277							v_end_date := SYSDATE;
278						END IF;
279						--calculate expiry date
280						IF	NVL(t_offerItemBasicDetails.offerExpiryDate, k.min_date) != k.min_date THEN
281							v_expiry_date := TO_DATE(t_offerItemBasicDetails.offerExpiryDate||k_space||TSTR(NVL(t_offerItemBasicDetails.offerExpiryTime, 0)), k_datemask_yymmddhhmiss);
282						ELSE
283							v_expiry_date := SYSDATE;
284						END IF;
285						--g.debug('end date',TO_CHAR(v_end_date, k.datemask));
286						--g.debug('exp date',TO_CHAR(v_expiry_date, k.datemask));						
287						--check offer has been expired or not
288						IF	GREATEST(v_end_date, v_expiry_date) >= SYSDATE THEN							
289							g_item_count := g_item_count + 1;
290							INSERT INTO glxmem_tmp_decision_source(
291											       temp_session_id,
292											       decision_source_id,
293												   time_1,
294												   time_2,
295												   alpha_1,
296												   time_3,
297												   alpha_2,
298												   alpha_3,
299												   alpha_4,
300												   alpha_5,
301												   time_4,
302												   alpha_6,
303												   time_5,
304												   time_6)
305											VALUES(
306													g_item_count,
307													k_dummy_id,
308													t_offerItemBasicDetails.offerItemId,
309													t_offerItemBasicDetails.offerId,
310													t_offerItemBasicDetails.offerName,
311													t_offerItemBasicDetails.serviceID,
312													t_offerItemBasicDetails.serviceName,
313													t_offerItemBasicDetails.offerStatusCode,
314													t_offerItemBasicDetails.offerStausDescr,
315													t_offerItemBasicDetails.offerAvaibilityEndDate,
316													t_offerItemBasicDetails.offerAvaibilityEndTime,
317													t_offerItemBasicDetails.offerExpiryDate,
318													t_offerItemBasicDetails.offerExpiryTime,
319													t_offerItemBasicDetails.offerStatusISN);
320						END IF;
321					END LOOP; --offer items basic details
322					--close basic details cursor
323					IF	c_offerItemBasicDetails%ISOPEN THEN
324						CLOSE c_offerItemBasicDetails;
325					END IF;
326					IF	c_error_response%ISOPEN THEN
327						CLOSE c_error_response;
328					END IF;
329					IF c_properties%ISOPEN THEN
330						CLOSE c_properties;
331					END IF;
332				END IF;
333			END LOOP; --offer items
334			CLOSE i_offer_item_ids;
335		END IF;
336	END findUnExpiredOfferItems;
337
338	-------------------------------------------------------------------------------------------------
339	-- Procedure : is_valid_audit_table
340	-- Added By Tlevin @04/03/2012
341	-- Description : Validate that the input table exists in IBMS Audit Definition
342	-------------------------------------------------------------------------------------------------
343	FUNCTION is_valid_audit_table(i_table_name IN audit_table_definition.table_name%TYPE) RETURN NUMBER IS
344		v_is_valid_table NUMBER(1) := 0;
345	BEGIN
346		BEGIN
347			SELECT COUNT(1)
348			INTO   v_is_valid_table
349			FROM   audit_table_definition atd
350			WHERE  atd.table_name = i_table_name;
351		EXCEPTION
352			WHEN NO_DATA_FOUND THEN
353				v_is_valid_table := 0;
354		END;
355
356		RETURN v_is_valid_table;
357
358	END is_valid_audit_table;
359
360	-------------------------------------------------------------------------------------------------
361	-- Procedure : get_aud_table_primary_keys
362	-- Added By Tlevin @04/03/2012
363	-- Description : Get table name and return the first 3 primary keys in audit definition
364	-------------------------------------------------------------------------------------------------
365	FUNCTION get_aud_table_primary_keys(i_table_name IN audit_table_definition.table_name%TYPE) RETURN VARCHAR2 IS
366		v_primary_key_column k.max_varchar2_plsql%TYPE := NULL;
367
368	BEGIN
369		BEGIN
370			SELECT STRAGG(t.column_name)
371			INTO   v_primary_key_column
372			FROM (
373				SELECT acd.column_name
374				FROM   audit_column_definition acd
375				JOIN   audit_table_definition atd
376				ON     (atd.audit_table_definition_id = acd.audit_table_definition_id)
377				WHERE  atd.table_name = i_table_name
378				AND    acd.primary_key_column = k_true
379				AND	   acd.audit_enabled = k_true
380				AND    ROWNUM <= 3
381				ORDER  BY acd.display_order, DECODE(NVL(TRIM(acd.column_data_type), NULL), 'INTEGER', 1, 'NUMBER', 2, 3)) t; -- In order to retrieve integers first and char after
382		EXCEPTION
383			WHEN NO_DATA_FOUND THEN
384				v_primary_key_column := NULL;
385
386		END;
387
388		RETURN v_primary_key_column;
389	END get_aud_table_primary_keys;
390	-------------------------------------------------------------------------------------------------
391	-- Procedure : get_attributes_to_log
392	-- Added By Tlevin @04/03/2012
393	-- Description : Get table name and column names. If column names is null, return a list of all audit columns.
394	-- if column names inserted, validate that these columns are exists in audit definition and return them.
395	-- if they are not exists, return o_error true.
396	-------------------------------------------------------------------------------------------------
397	FUNCTION get_attributes_to_log(i_table_name IN audit_table_definition.table_name%TYPE,
398								   i_columns    IN VARCHAR2,
399								   o_error      OUT NUMBER) RETURN VARCHAR2 IS
400
401		v_sensitive_columns k.max_varchar2_plsql%TYPE := NULL;
402
403	BEGIN
404		o_error := 0;
405		IF (i_columns IS NULL) THEN
406			BEGIN
407
408				SELECT STRAGG(column_name)
409				INTO   v_sensitive_columns
410				FROM   audit_column_definition acd
411				JOIN   audit_table_definition atd
412				ON     (acd.audit_table_definition_id = atd.audit_table_definition_id)
413				WHERE  table_name = i_table_name
414				AND    NVL(audit_raw_column_id, 0) != 0
415				AND    acd.audit_enabled = 1;
416
417			EXCEPTION
418				WHEN NO_DATA_FOUND THEN
419					v_sensitive_columns := NULL;
420					o_error             := 1;
421			END;
422
423		ELSIF (i_columns = ' ') THEN
424			v_sensitive_columns := NULL;
425		ELSE
426			--Check if a column does not exists in audit_column_definition
427			BEGIN
428				SELECT COUNT(1)
429				INTO   o_error
430				FROM   TABLE(galaxy_util.split_str(i_columns, k_comma))
431				WHERE  column_value NOT IN (SELECT acd.column_name
432											FROM   audit_column_definition acd
433											JOIN   audit_table_definition atd
434											ON     (acd.audit_table_definition_id = atd.audit_table_definition_id)
435											WHERE  atd.table_name = i_table_name
436											AND    acd.audit_enabled = 1
437											);
438				IF (o_error = 0) THEN
439					v_sensitive_columns := i_columns;
440				ELSE
441					o_error := k.logical_true;
442				END IF;
443
444			EXCEPTION
445				WHEN NO_DATA_FOUND THEN
446					v_sensitive_columns := NULL;
447					o_error             := 1;
448			END;
449
450		END IF;
451		RETURN v_sensitive_columns;
452
453	END get_attributes_to_log;
454
455	FUNCTION get_attributes_to_log_by_set(i_audit_set_id IN audit_set.audit_set_id%TYPE,
456										  i_columns      IN VARCHAR2,
457										  o_error        OUT NUMBER)
458		RETURN VARCHAR2 IS
459		v_sensitive_columns k.max_varchar2_plsql%TYPE := NULL;
460	BEGIN
461		SELECT STRAGG('"' || asn.set_column_name || '"')
462		INTO   v_sensitive_columns
463		FROM   TABLE(galaxy_util.split_str(i_columns,
464										   k_comma)) t
465		JOIN   audit_set_column asn
466		ON     asn.audit_set_id = i_audit_set_id
467			   AND UPPER(asn.set_column_name) = UPPER(t.column_value)
468		JOIN   audit_column_definition acd
469		ON     acd.audit_column_definition_id = asn.audit_column_definition_id
470		WHERE  acd.audit_enabled = 1;
471
472		g.debug('i_columns',i_columns);
473		g.debug('v_sensitive_columns',v_sensitive_columns);
474
475		IF galaxy_util.str_token_count(i_delimited_str => i_columns,
476									   i_delimiter     => k_comma) =
477		   galaxy_util.str_token_count(i_delimited_str => v_sensitive_columns,
478									   i_delimiter     => k_comma)
479			OR TRIM(v_sensitive_columns) IS NOT NULL
480		THEN
481			o_error := 0;
482		ELSE
483			o_error := 1;
484		END IF;
485
486		RETURN v_sensitive_columns;
487	END get_attributes_to_log_by_set;
488
489	-------------------------------------------------------------------------------------------------
490	-- Procedure : print_clob
491	-- Added By Tlevin @04/03/2012
492	-- Description : prints a clob
493	-------------------------------------------------------------------------------------------------
494	PROCEDURE print_clob(p_clob IN CLOB) IS
495		l_offset NUMBER DEFAULT 1;
496	BEGIN
497		LOOP
498			EXIT WHEN l_offset > dbms_lob.getlength(p_clob);
499			dbms_output.put_line(dbms_lob.substr(p_clob, 255, l_offset));
500			l_offset := l_offset + 255;
501		END LOOP;
502	END print_clob;
503
504	-------------------------------------------------------------------------------------------------
505	-- Procedure : QueryAuditByTemplate
506	-- Added By TLEVIN @04/03/2012
507	-- Description : An API for querying audit changes.
508	-- This procedure recieves a template of the form:
509	-- tableName/setReference:TITLE;changeType:MODIFY;changedAttributes:;^tableName:TITLE;changeType:MODIFY;changedAttributes:PROM_DIRECTOR_ID;pKeyValues:PKEY1,PKEY2,PKEY3
510	-- tableName: The table to log
511	-- setReference: The set to log
512	-- changeType: Type of change to log, can be 'INSERT' or 'MODIFY' or 'DELETE' or null which means all
513	-- changedAttributes: A valid audited column name to log changes for in case of modify change type, ignored for INSERT & Delete,
514	-- pKeyValues: Primary Key Values to search by
515	-- each template attribute is divided by semi-colon and each template is divided by '^'
516	-- but checked in modify and all actions.
517	-- An example:
518	-- tableName:TITLE;changeType:MODIFY;changedAttributes:PROM_DIRECTOR_ID,PROGRAMME_NAME;pKeyValues:8934578,'Test',0
519	-- the following template means track changes of type MODIFY from table TITLE, for attributes PROM_DIRECTOR_ID and PROGRAMME_NAME, on primary key 8934578,'Test',0
520	-- The procedure returns two refcursors:
521	-- one with values of p.keys, table name, action type and columns that were changed in case of modify
522	-- second refcursor with p.keys, table name, column name, column value,audit date & audit time for each row
523	-------------------------------------------------------------------------------------------------
524	PROCEDURE QueryAuditByTemplate(i_templates         	IN VARCHAR2,
525								   i_date              	IN VARCHAR2,
526								   i_time              	IN NUMBER,
527								   i_date_to          	IN VARCHAR2 DEFAULT NULL,
528								   i_time_to          	IN NUMBER DEFAULT NULL,
529								   i_user_codes			IN VARCHAR2 DEFAULT NULL,
530								   i_last_audit_only	IN t.logical%TYPE DEFAULT k_false,
531								   o_audit_events      	OUT SYS_REFCURSOR,
532								   o_logged_attributes 	OUT SYS_REFCURSOR,
533								   o_error_reported    	OUT t.logical%TYPE,
534								   o_error_response    	OUT SYS_REFCURSOR) IS
535
536		k_procedure_name CONSTANT CHAR(20) := 'QueryAuditByTemplate';
537		k_insert         CONSTANT CHAR(6) := 'INSERT';
538		k_modify         CONSTANT CHAR(6) := 'MODIFY';
539		k_delete         CONSTANT CHAR(6) := 'DELETE';
540
541		v_cur_table_name     VARCHAR2(40) := NULL;
542		v_cur_set_id     	 audit_set.audit_set_id%TYPE := NULL;
543		v_cur_change_type    CHAR(1) := NULL;
544		v_cur_change_input   VARCHAR2(6) := NULL;
545		v_cur_attributes     VARCHAR2(4000) := NULL;
546		v_cur_values		 k.max_varchar2_plsql%TYPE := NULL;
547		v_cur_view_name      VARCHAR2(200) := NULL;
548		v_primary_key_column VARCHAR2(100) := NULL;
549		v_sensitive_columns  k.max_varchar2_plsql%TYPE := NULL;
550		v_pkey1_column_name  VARCHAR2(30) := NULL;
551		v_pkey2_column_name  VARCHAR2(30) := NULL;
552		v_pkey3_column_name  VARCHAR2(30) := NULL;
553
554		v_curr_audit_events_sql  k.max_varchar2_plsql%TYPE := NULL;
555		v_cattributes_sql        k.max_varchar2_plsql%TYPE := NULL;
556		v_audit_events_sql       k.max_varchar2_plsql%TYPE := NULL;
557		v_logged_attributes_sql  k.max_varchar2_plsql%TYPE := 'select TO_CHAR(pkey1) as pkey1,TO_CHAR(pkey2) as pkey2,TO_CHAR(pkey3) as pkey3,pkey1_name,pkey2_name,pkey3_name,object_type,column_name,column_value,audit_date,audit_time,audit_user from(';
558		v_curr_values_sql        k.max_varchar2_plsql%TYPE := NULL;
559		v_curr_column_sql        k.max_varchar2_plsql%TYPE := NULL;
560		v_counter                NUMBER(38) := 0;
561		v_values_counter         NUMBER(38) := 0;
562		v_sensitive_where_clause k.max_varchar2_plsql%TYPE;
563		v_sensitive_counter      NUMBER(38) := 0;
564
565		v_is_valid_table NUMBER(1) := 0;
566		v_error          NUMBER(1) := 0;
567
568		v_date_user_where_clause		VARCHAR2(4000) := NULL;
569		v_pkeys_where_clause	VARCHAR2(4000) := NULL;
570		v_order_by				VARCHAR2(4000) := NULL;
571		v_index					NUMBER;
572
573	BEGIN
574		g.debug('i_template',i_templates);
575		o_error_reported := k_false;
576		--loop over each template(Ask Bakulesh how we can differentiate between null and empty)
577
578		IF i_date IS NOT NULL THEN
579			v_date_user_where_clause := v_date_user_where_clause || ' and audit_date>=' || i_date ||
580				' and not(audit_date=' || i_date || ' and audit_time<=' || i_time || ')';
581		END IF;
582		IF i_date_to IS NOT NULL THEN
583			v_date_user_where_clause := v_date_user_where_clause || ' and audit_date<=' || i_date_to ||
584				' and not(audit_date=' || i_date_to || ' and audit_time>=' || i_time_to || ')';
585		END IF;
586
587		IF TRIM(i_user_codes) IS NOT NULL THEN
588			v_date_user_where_clause := v_date_user_where_clause || ' and audit_user in (''' || REPLACE(TRIM(i_user_codes), k_comma, '''' || k_comma || '''') || ''')';
589		END IF;
590
591		FOR current_template IN (SELECT TRIM(t.column_value) AS column_name
592								 FROM   TABLE(galaxy_util.split_str(i_templates, '^')) t)
593		LOOP
594
595			v_counter                := 0;
596			v_pkey1_column_name      := NULL;
597			v_pkey2_column_name      := NULL;
598			v_pkey3_column_name      := NULL;
599			v_curr_audit_events_sql  := NULL;
600			v_curr_values_sql        := NULL;
601			v_sensitive_where_clause := NULL;
602			v_sensitive_counter      := 0;
603
604			--Get Current template values
605
606			BEGIN
607				SELECT SUBSTR(t.column_value, 11)
608				INTO   v_cur_table_name
609				FROM   TABLE(galaxy_util.split_str(current_template.column_name, ';')) t
610				WHERE  INSTR(t.column_value, 'tableName:') > 0
611				AND    ROWNUM = 1;
612
613				v_is_valid_table := is_valid_audit_table(v_cur_table_name);
614			EXCEPTION WHEN NO_DATA_FOUND THEN
615				v_cur_table_name := NULL;
616				v_is_valid_table := 0;
617
618				BEGIN
619					SELECT a.audit_set_id, ibms_audit_set.get_audit_set_view_name(a.audit_set_reference) AS view_name
620					INTO   v_cur_set_id, v_cur_view_name
621					FROM   TABLE(galaxy_util.split_str(current_template.column_name, ';')) t
622					JOIN   audit_set a ON a.audit_set_reference = SUBSTR(t.column_value, 14)
623					WHERE  INSTR(t.column_value, 'setReference:') > 0
624					AND    ROWNUM = 1;
625				EXCEPTION WHEN NO_DATA_FOUND THEN
626					v_cur_set_id := NULL;
627				END;
628			END;
629			g.debug('v_cur_table_name',v_cur_table_name);
630			IF v_cur_table_name IS NULL AND v_cur_set_id IS NULL THEN
631					--raise invalid input exception
632					error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
633														i_message         => k_procedure_name || ': ' ||
634																			 'Cannot find table/set name',
635														i_broadcaster_ref => NULL,
636														i_sequence        => 1,
637														i_result          => k_rslt_tp_notvalid,
638														i_message_type    => k_err_msg_type,
639														i_message_code    => SQLCODE);
640					o_error_response := error_handling_api.geterrorrefcursor();
641					RAISE e_error;
642			END IF;
643
644			BEGIN
645				SELECT SUBSTR(t.column_value, 12)
646				INTO   v_cur_change_input
647				FROM   TABLE(galaxy_util.split_str(current_template.column_name, ';')) t
648				WHERE  INSTR(t.column_value, 'changeType:') > 0
649				AND    ROWNUM = 1;
650			EXCEPTION
651				WHEN OTHERS THEN
652					v_cur_change_type := NULL;
653					--raise invalid input exception
654					error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
655														i_message         => k_procedure_name || ': ' ||
656																			 'Cannot find change type',
657														i_broadcaster_ref => NULL,
658														i_sequence        => 1,
659														i_result          => k_rslt_tp_notvalid,
660														i_message_type    => k_err_msg_type,
661														i_message_code    => SQLCODE);
662					o_error_response := error_handling_api.geterrorrefcursor();
663					RAISE e_error;
664			END;
665
666			IF (UPPER(v_cur_change_input) = k_insert) THEN
667				v_cur_change_type := 'I';
668			ELSIF (UPPER(v_cur_change_input) = k_modify) THEN
669				v_cur_change_type := 'U';
670			ELSIF (UPPER(v_cur_change_input) = k_delete) THEN
671				v_cur_change_type := 'D';
672			ELSIF (TRIM(v_cur_change_input) IS NULL) THEN
673				v_cur_change_type := NULL;
674			ELSE
675				error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
676													i_message         => k_procedure_name || ': ' ||
677																		 'Change Type is invalid',
678													i_broadcaster_ref => NULL,
679													i_sequence        => 1,
680													i_result          => k_rslt_tp_notvalid,
681													i_message_type    => k_err_msg_type,
682													i_message_code    => SQLCODE);
683				o_error_response := error_handling_api.geterrorrefcursor();
684				RAISE e_error;
685
686			END IF;
687
688			BEGIN
689				SELECT SUBSTR(t.column_value, 19)
690				INTO   v_cur_attributes
691				FROM   TABLE(galaxy_util.split_str(current_template.column_name, ';', 0)) t
692				WHERE  INSTR(t.column_value, 'changedAttributes:') > 0
693				AND    ROWNUM = 1;
694			EXCEPTION
695				WHEN OTHERS THEN
696					v_cur_attributes := NULL;
697					--raise invalid input exception
698					error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
699														i_message         => k_procedure_name || ': ' ||
700																			 'Cannot find changed attributes',
701														i_broadcaster_ref => NULL,
702														i_sequence        => 1,
703														i_result          => k_rslt_tp_notvalid,
704														i_message_type    => k_err_msg_type,
705														i_message_code    => SQLCODE);
706					o_error_response := error_handling_api.geterrorrefcursor();
707					RAISE e_error;
708			END;
709
710			BEGIN
711				SELECT SUBSTR(t.column_value, 12)
712				INTO   v_cur_values
713				FROM   TABLE(galaxy_util.split_str(current_template.column_name, ';', 0)) t
714				WHERE  INSTR(t.column_value, 'pKeyValues:') > 0
715				AND    ROWNUM = 1;
716			EXCEPTION
717				WHEN OTHERS THEN
718					v_cur_values := NULL;
719			END;
720
721			IF (v_is_valid_table = 0 AND v_cur_set_id IS NULL) THEN
722				--raise iilegal exception wrong table name
723				--and continue to the next table
724				--o_audit_events      := NULL;
725				--o_logged_attributes := NULL;
726				error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
727													i_message         => k_procedure_name || ': ' ||
728																		 'Table/Set cannot be found',
729													i_broadcaster_ref => NULL,
730													i_sequence        => 1,
731													i_result          => k_rslt_tp_notvalid,
732													i_message_type    => k_err_msg_type,
733													i_message_code    => SQLCODE);
734				o_error_response := error_handling_api.geterrorrefcursor();
735				RAISE e_error;
736
737			ELSE
738				IF ((v_cur_change_type IS NOT NULL) AND
739				   (v_cur_change_type != 'I' AND v_cur_change_type != 'D' AND v_cur_change_type != 'U')) THEN
740					--raise illegal exception, wrong action type
741					--and continue to the next table
742					--o_audit_events      := NULL;
743					--o_logged_attributes := NULL;
744					error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
745														i_message         => k_procedure_name || ': ' ||
746																			 'Wrong change type, can be only I,U or D',
747														i_broadcaster_ref => NULL,
748														i_sequence        => 1,
749														i_result          => k_rslt_tp_notvalid,
750														i_message_type    => k_err_msg_type,
751														i_message_code    => SQLCODE);
752					o_error_response := error_handling_api.geterrorrefcursor();
753					RAISE e_error;
754				ELSE
755
756					IF v_cur_table_name IS NOT NULL THEN
757						v_primary_key_column := get_aud_table_primary_keys(v_cur_table_name);
758					ELSE
759						v_primary_key_column := 'JOIN_COLUMN';
760					END IF;
761
762					IF (TRIM(v_primary_key_column) IS NULL) THEN
763						--raise system failure: Cannot find primary keys
764						error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
765															i_message         => k_procedure_name || ': ' ||
766																				 'Cannot find the primary keys',
767															i_broadcaster_ref => NULL,
768															i_sequence        => 1,
769															i_result          => k_general_errror,
770															i_message_type    => k_err_msg_type,
771															i_message_code    => SQLCODE);
772						o_error_response := error_handling_api.geterrorrefcursor();
773						--o_audit_events      := NULL;
774						--o_logged_attributes := NULL;
775						RAISE e_error;
776					ELSE
777						v_pkeys_where_clause 	:= NULL;
778						v_order_by				:= NULL;
779
780						--assign the primary keys column names
781						FOR i IN (SELECT TRIM(column_value) AS column_name
782								  FROM   TABLE(galaxy_util.split_str(v_primary_key_column, k_comma)))
783						LOOP
784							IF (v_pkey1_column_name IS NULL) THEN
785								v_pkey1_column_name := i.column_name;
786							ELSIF (v_pkey2_column_name IS NULL) THEN
787								v_pkey2_column_name := i.column_name;
788							ELSIF (v_pkey3_column_name IS NULL) THEN
789								v_pkey3_column_name := i.column_name;
790							END IF;
791						END LOOP;
792
793						--assign the primary key values
794						v_index := 0;
795						FOR i IN (SELECT TRIM(column_value) AS column_value
796								  FROM   TABLE(galaxy_util.split_str(v_cur_values, k_comma)))
797						LOOP
798							v_index := v_index + 1;
799							IF (v_index = 1) THEN
800								v_pkeys_where_clause := v_pkeys_where_clause || ' and pkey1 =''' || i.column_value || '''';
801							ELSIF (v_index = 2) THEN
802								v_pkeys_where_clause := v_pkeys_where_clause || ' and pkey2 =''' || i.column_value || '''';
803							ELSIF (v_index = 3) THEN
804								v_pkeys_where_clause := v_pkeys_where_clause || ' and pkey3 =''' || i.column_value || '''';
805							END IF;
806						END LOOP;
807
808						IF i_last_audit_only = k_true THEN
809							v_order_by := ' order by audit_date desc,audit_time desc';
810						END IF;
811
812						IF (v_cur_change_type = 'U' OR v_cur_change_type IS NULL) THEN
813							IF v_cur_table_name IS NOT NULL THEN
814								v_sensitive_columns := get_attributes_to_log(v_cur_table_name,
815																			 v_cur_attributes,
816																			 v_error);
817							ELSE
818								v_sensitive_columns := get_attributes_to_log_by_set(
819																			 v_cur_set_id,
820																			 v_cur_attributes,
821																			 v_error);
822							END IF;
823
824						ELSE
825							v_sensitive_columns := NULL;
826							v_error             := 0;
827						END IF;
828
829						IF (v_error = 1) THEN
830							--raise a failure as the sensitive columns where not found
831							--o_audit_events      := NULL;
832							--o_logged_attributes := NULL;
833							error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
834																i_message         => k_procedure_name || ': ' ||
835																					 'Not all logged attributes can be found in Audit Definition',
836																i_broadcaster_ref => NULL,
837																i_sequence        => 1,
838																i_result          => k_rslt_tp_notvalid,
839																i_message_type    => k_err_msg_type,
840																i_message_code    => SQLCODE);
841							o_error_response := error_handling_api.geterrorrefcursor();
842							RAISE e_error;
843						ELSE
844							v_sensitive_where_clause := ' and (';
845							FOR i IN (SELECT TRIM(column_value) AS column_name
846									  FROM   TABLE(galaxy_util.split_str(v_sensitive_columns, k_comma)))
847							LOOP
848								v_sensitive_counter := v_sensitive_counter + 1;
849
850								IF (v_sensitive_counter > 1) THEN
851									v_sensitive_where_clause := v_sensitive_where_clause || ' OR ';
852								END IF;
853
854								v_sensitive_where_clause := v_sensitive_where_clause || 'NVL(' ||
855															SUBSTR(i.column_name, 1, 27) || 'tn,0)>0';
856							END LOOP;
857							v_sensitive_where_clause := v_sensitive_where_clause || ')';
858
859							--Get View Name
860							BEGIN
861								IF v_cur_table_name IS NOT NULL THEN
862									SELECT ibms_audit.get_view_name(atd.audit_table_definition_id)
863									INTO   v_cur_view_name
864									FROM   audit_table_definition atd
865									WHERE  table_name = v_cur_table_name;
866								END IF;
867							EXCEPTION
868								WHEN NO_DATA_FOUND THEN
869									error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
870																		i_message         => k_procedure_name || ': ' ||
871																							 'View cannot be found',
872																		i_broadcaster_ref => NULL,
873																		i_sequence        => 1,
874																		i_result          => k_rslt_tp_notvalid,
875																		i_message_type    => k_err_msg_type,
876																		i_message_code    => SQLCODE);
877									o_error_response := error_handling_api.geterrorrefcursor();
878									RAISE e_error;
879
880							END;
881
882							--if action = all  then run the query for all
883							--else if action = 'U' run the query for update actions
884							--else run the last query with filtering the action (Insert or Delete)
885							IF (TRIM(v_cur_change_type) IS NULL) THEN
886								v_curr_audit_events_sql := NULL;
887								--All Actions
888								IF (v_sensitive_columns IS NOT NULL) THEN
889
890									--Create query that looks for update in sensitive columns & insert and delete actions
891									v_curr_audit_events_sql := 'select ''' || v_cur_table_name ||
892												  ''' as object_type,TO_CHAR(pkey1) as pkey1,TO_CHAR(pkey2) as pkey2,TO_CHAR(pkey3) as pkey3' ||
893												  ',''' || v_pkey1_column_name || '''as pkey1_name' || ',''' || v_pkey2_column_name ||
894												  '''as pkey2_name' || ',''' || v_pkey3_column_name || '''as pkey3_name' ||
895												  ',''MODIFY'' as change_type,stragg(distinct c1) as changed_attributes,audit_date,audit_time,audit_user from(';
896									FOR i IN (SELECT TRIM(column_value) AS column_name
897											  FROM   TABLE(galaxy_util.split_str(v_sensitive_columns, k_comma)))
898									LOOP
899										v_counter         := v_counter + 1;
900										v_cattributes_sql := NULL;
901										IF (v_counter > 1) THEN
902											v_cattributes_sql := ' UNION ';
903										END IF;
904										v_cattributes_sql := v_cattributes_sql ||
905															 ' select action,' || NVL(v_pkey1_column_name, 'NULL') ||
906															 ' as pkey1,' || NVL(v_pkey2_column_name, 'NULL') ||
907															 ' as pkey2,' || NVL(v_pkey3_column_name, 'NULL') ||
908															 ' as pkey3' || ',''' || i.column_name ||
909															 ''' as c1,audit_date,audit_time,audit_user from ' ||
910															 v_cur_view_name || ' where ' ||
911															 CASE WHEN v_cur_table_name IS NOT NULL
912															 	THEN 'NVL(' || SUBSTR(i.column_name, 1, 27) || 'tn,0)>0'
913																ELSE 'TRIM(' || SUBSTR(i.column_name, 1, 27) || ') IS NOT NULL'
914															 END;
915
916										v_curr_audit_events_sql := v_curr_audit_events_sql || v_cattributes_sql;
917
918									END LOOP;
919
920									v_curr_audit_events_sql := v_curr_audit_events_sql || ') where action=''U'' ' ||
921												  v_date_user_where_clause || ' ' || v_pkeys_where_clause ||
922												  ' group by pkey1,pkey2,pkey3,audit_date,audit_time,audit_user ';
923									--A query for insert & Delete
924									v_curr_audit_events_sql := v_curr_audit_events_sql || ' UNION ';
925								END IF;
926
927								--Query only for Insert & Delete as no attribute need to be logged
928								v_curr_audit_events_sql := 'select * from (' || v_curr_audit_events_sql ||
929											  ' select * from (select ''' || v_cur_table_name || ''' as object_type,TO_CHAR(' ||
930											  NVL(v_pkey1_column_name, 'NULL') || ') as pkey1,TO_CHAR(' ||
931											  NVL(v_pkey2_column_name, 'NULL') || ') as pkey2,TO_CHAR(' ||
932											  NVL(v_pkey3_column_name, 'NULL') || ') as pkey3' || ',''' || v_pkey1_column_name ||
933											  ''' as pkey1_name' || ',''' || v_pkey2_column_name || ''' as pkey2_name' ||
934											  ',''' || v_pkey3_column_name || ''' as pkey3_name' ||
935											  ',DECODE(action,''I'',''INSERT'',''DELETE'') as change_type, NULL as changed_attributes, audit_date, audit_time, audit_user from ' ||
936											  v_cur_view_name || ' where action!=''U'') where 1=1 ' ||
937											  v_date_user_where_clause || ' ' || v_pkeys_where_clause || ') ' || v_order_by;
938
939							ELSIF (v_cur_change_type = 'U') THEN
940
941								IF (v_sensitive_columns IS NOT NULL) THEN
942									--Create query that looks for update in sensitive columns
943									v_curr_audit_events_sql := 'select ''' || v_cur_table_name ||
944												  ''' as object_type,TO_CHAR(pkey1) as pkey1,TO_CHAR(pkey2) as pkey2,TO_CHAR(pkey3) as pkey3' ||
945												  ',''' || v_pkey1_column_name || ''' as pkey1_name' || ',''' || v_pkey2_column_name ||
946												  ''' as pkey2_name' || ',''' || v_pkey3_column_name || ''' as pkey3_name' ||
947												  ',''MODIFY'' as change_type,stragg(distinct c1) as changed_attributes,audit_date,audit_time,audit_user from(';
948									FOR i IN (SELECT TRIM(column_value) AS column_name
949											  FROM   TABLE(galaxy_util.split_str(v_sensitive_columns, k_comma)))
950									LOOP
951										v_counter         := v_counter + 1;
952										v_cattributes_sql := NULL;
953										IF (v_counter > 1) THEN
954											v_cattributes_sql := ' UNION ';
955										END IF;
956										v_cattributes_sql := v_cattributes_sql ||
957															 ' select action,' || NVL(v_pkey1_column_name, 'NULL') ||
958															 ' as pkey1,' || NVL(v_pkey2_column_name, 'NULL') ||
959															 ' as pkey2,' || NVL(v_pkey3_column_name, 'NULL') ||
960															 ' as pkey3' || ',''' || i.column_name ||
961															 ''' as c1,audit_date,audit_time,audit_user from ' ||
962															 v_cur_view_name || ' where ' ||
963															 CASE WHEN v_cur_table_name IS NOT NULL
964															 	THEN 'NVL(' || SUBSTR(i.column_name, 1, 27) || 'tn,0)>0'
965																ELSE 'TRIM(' || SUBSTR(i.column_name, 1, 27) || ') IS NOT NULL'
966															 END;
967
968										v_curr_audit_events_sql := v_curr_audit_events_sql || v_cattributes_sql;
969
970									END LOOP;
971
972									v_curr_audit_events_sql := v_curr_audit_events_sql || ') where action=''U'' ' ||
973												  v_date_user_where_clause || v_pkeys_where_clause ||
974												  ' group by pkey1,pkey2,pkey3,audit_date,audit_time,audit_user ' ||
975												  v_order_by;
976								ELSE
977									--select empty refcursor as no attributes need to be logged
978									v_curr_audit_events_sql := 'select null as object_type,null as pkey1,null as pkey2,null as pkey3,null as pkey1_name,null as pkey2_name,null as pkey3_name,null as change_type,null as changed_attributes,null as audit_date,null as audit_time,null as audit_user from dual where 1=0';
979								END IF;
980
981							ELSE
982								--Only queries for insert and delete
983								v_curr_audit_events_sql := v_curr_audit_events_sql || 'select ''' || v_cur_table_name ||
984											  ''' as object_type,TO_CHAR(' || NVL(v_pkey1_column_name, 'NULL') ||
985											  ') as pkey1,TO_CHAR(' || NVL(v_pkey2_column_name, 'NULL') ||
986											  ') as pkey2,TO_CHAR(' || NVL(v_pkey3_column_name, 'NULL') || ') as pkey3' ||
987											  ',''' || v_pkey1_column_name || ''' as pkey1_name' || ',''' || v_pkey2_column_name ||
988											  ''' as pkey2_name' || ',''' || v_pkey3_column_name || ''' as pkey3_name' ||
989											  ',DECODE(action,''I'',''INSERT'',''DELETE'') as change_type, NULL as changed_attributes, audit_date, audit_time, audit_user from ' ||
990											  v_cur_view_name || ' where action=''' || v_cur_change_type || '''' ||
991											  v_date_user_where_clause || ' ' || v_pkeys_where_clause || ' ' || v_order_by;
992							END IF;
993
994							IF (TRIM(v_audit_events_sql) IS NOT NULL) THEN
995								v_audit_events_sql := v_audit_events_sql || ' UNION ';
996							END IF;
997							v_audit_events_sql := v_audit_events_sql || ' ' || v_curr_audit_events_sql;
998
999							--Logged Attributes Part
1000							--Run over all audited columns
1001							FOR i IN (SELECT column_name AS column_name
1002									  FROM   audit_column_definition acd
1003									  JOIN   audit_table_definition atd
1004									  ON     (acd.audit_table_definition_id = atd.audit_table_definition_id)
1005									  WHERE  table_name = v_cur_table_name
1006									  AND    NVL(audit_raw_column_id, 0) != 0
1007									  AND    acd.audit_enabled = 1)
1008							LOOP
1009								--Query for all records of any change type
1010								v_curr_column_sql := 'select ' || NVL(v_pkey1_column_name, 'NULL') || ' as pkey1,' ||
1011													 NVL(v_pkey2_column_name, 'NULL') || ' as pkey2,' ||
1012													 NVL(v_pkey3_column_name, 'NULL') || ' as pkey3,' || '''' ||
1013													 v_pkey1_column_name || ''' as pkey1_name' || ',''' || v_pkey2_column_name ||
1014													 ''' as pkey2_name' || ',''' || v_pkey3_column_name ||
1015													 ''' as pkey3_name,' || '''' || v_cur_table_name ||
1016													 ''' as object_type,' || '''' || i.column_name || '''' ||
1017													 ' as column_name, decode(' || i.column_name ||
1018													 ',NULL,NULL,TO_CHAR(' || i.column_name ||
1019													 ')) as column_value,audit_date,audit_time,audit_user,update_sequence from ' ||
1020													 v_cur_view_name || ' where 1=1 ' || v_date_user_where_clause || ' ' || v_pkeys_where_clause || ' ' || v_order_by;
1021
1022								IF (v_cur_change_type IS NULL) THEN
1023									IF (v_sensitive_columns IS NOT NULL) THEN
1024										--Need to query for modify according to the changedAttributes, and all columns of insert and delete
1025										v_curr_column_sql := v_curr_column_sql || ' and ((action=''U''' ||
1026															 v_sensitive_where_clause || ') OR (action!=''U''))';
1027									ELSE
1028										-- Query only for insert and delete
1029										v_curr_column_sql := v_curr_column_sql || ' and action!=''U''';
1030									END IF;
1031								ELSE
1032									IF (v_cur_change_type = 'U') THEN
1033										--Query for modify with changed attributes clause
1034										IF (v_sensitive_columns IS NOT NULL) THEN
1035											v_curr_column_sql := v_curr_column_sql || ' and action=''U''' ||
1036																 v_sensitive_where_clause;
1037										ELSE
1038											--Don't query for modify changes
1039											v_curr_column_sql := ' select null as pkey1,null as pkey2,null as pkey3,null as pkey1_name,null as pkey2_name,null as pkey3_name,null as object_type,null as column_name,null as column_value,null as audit_date,null as audit_time,null as audit_user,null as update_sequence from dual where 1=0';
1040										END IF;
1041									ELSE
1042										--Query for Insert or Delete
1043										v_curr_column_sql := v_curr_column_sql || ' and action=''' ||
1044															 v_cur_change_type || '''';
1045									END IF;
1046								END IF;
1047
1048								IF (v_curr_values_sql IS NULL) THEN
1049									v_curr_values_sql := v_curr_column_sql;
1050								ELSE
1051									v_curr_values_sql := v_curr_values_sql || ' union ' || v_curr_column_sql;
1052								END IF;
1053
1054							END LOOP;
1055
1056							IF (v_values_counter != 0) THEN
1057								v_logged_attributes_sql := v_logged_attributes_sql || ' union' || v_curr_values_sql;
1058
1059							ELSE
1060								v_logged_attributes_sql := v_logged_attributes_sql || v_curr_values_sql;
1061							END IF;
1062						END IF;
1063					END IF;
1064				END IF;
1065			END IF;
1066
1067			IF (v_curr_values_sql IS NOT NULL) THEN
1068				v_values_counter := v_values_counter + 1;
1069			END IF;
1070
1071		END LOOP;
1072
1073		IF i_last_audit_only = k_true THEN
1074			v_audit_events_sql := 'select * from (' || v_audit_events_sql || ') where rownum = 1';
1075		END IF;
1076
1077		g.debug(v_audit_events_sql);
1078--		g.debug(v_logged_attributes_sql);
1079
1080		BEGIN
1081			OPEN o_audit_events FOR v_audit_events_sql;
1082		EXCEPTION
1083			WHEN OTHERS THEN
1084				RAISE e_error;
1085		END;
1086
1087		v_logged_attributes_sql := v_logged_attributes_sql || ') order by object_type,pkey1,pkey2,pkey3,audit_date,audit_time,audit_user';
1088
1089		IF i_last_audit_only = k_false THEN
1090			BEGIN
1091				OPEN o_logged_attributes FOR v_logged_attributes_sql;
1092			EXCEPTION
1093				WHEN OTHERS THEN
1094					RAISE e_error;
1095			END;
1096		END IF;
1097
1098		o_error_response := error_handling_api.geterrorrefcursor();
1099	EXCEPTION
1100		WHEN e_error THEN
1101			o_error_reported := k_true;
1102			IF NOT o_audit_events%ISOPEN THEN
1103				o_audit_events := error_handling_api.getemptyrefcursor();
1104			END IF;
1105			IF NOT o_logged_attributes%ISOPEN THEN
1106				o_logged_attributes := error_handling_api.getemptyrefcursor();
1107			END IF;
1108
1109		WHEN OTHERS THEN
1110			o_error_reported    := k_true;
1111			o_audit_events      := error_handling_api.getemptyrefcursor();
1112			o_logged_attributes := error_handling_api.getemptyrefcursor();
1113
1114			error_handling_api.logerrorresponse(io_response       => error_handling_api.g_response,
1115												i_message         => k_procedure_name || ': ' || 'General Error',
1116												i_broadcaster_ref => NULL,
1117												i_sequence        => 1,
1118												i_result          => k_general_errror,
1119												i_message_type    => k_err_msg_type,
1120												i_message_code    => SQLCODE);
1121
1122			o_error_response := error_handling_api.geterrorrefcursor();
1123
1124	END QueryAuditByTemplate;
1125
1126	PROCEDURE QueryAuditByTables(i_table_codes  IN VARCHAR2,
1127								 i_pkeys_values IN VARCHAR2 DEFAULT NULL,
1128								 i_date_from    IN VARCHAR2 DEFAULT NULL,
1129								 i_time_from    IN NUMBER DEFAULT NULL,
1130								 i_date_to      IN VARCHAR2 DEFAULT NULL,
1131								 i_time_to      IN NUMBER DEFAULT NULL,
1132								 i_user_codes	IN VARCHAR2 DEFAULT NULL,
1133								 o_audit_events OUT SYS_REFCURSOR) IS
1134
1135		k_wfaudt_table_id CONSTANT table_line.table_id%TYPE := 'WFAUDT';
1136
1137		v_templates   		VARCHAR2(4000);
1138		v_table_index 		NUMBER := 0;
1139		v_logged_attributes SYS_REFCURSOR;
1140		v_error_reported    t.logical%TYPE;
1141		v_error_response    SYS_REFCURSOR;
1142	BEGIN
1143		g.debug('i_table_codes',i_table_codes,i_pkeys_values);
1144		FOR r_table IN (SELECT TRIM(column_value) AS table_code,
1145							   tl_wfaudt.logical_1 AS active,
1146							   tl_wfaudt.logical_2 AS audit_set,
1147							   tl_wfaudt.long_text_1 AS table_name,
1148							   long_text_2 AS track_columns
1149						FROM   TABLE(galaxy_util.split_str(i_table_codes,
1150														   ';')) t
1151						JOIN   table_line tl_wfaudt
1152						ON     tl_wfaudt.table_id = k_wfaudt_table_id
1153							   AND tl_wfaudt.table_code = t.column_value)
1154		LOOP
1155		g.debug('r_table.table_name',r_table.table_name);
1156			v_table_index := v_table_index + 1;
1157			IF v_table_index > 1 THEN
1158				 v_templates := v_templates || '^';
1159			END IF;
1160			v_templates := v_templates || CASE WHEN r_table.audit_set = 1 THEN 'setReference:' ELSE 'tableName:' END || r_table.table_name ||
1161						   ';changeType:' ||
1162						   ';changedAttributes:' ||
1163						   r_table.track_columns || ';pKeyValues:' || strtoken(i_pkeys_values, v_table_index, '|');
1164		END LOOP;
1165		g.debug(v_templates);
1166
1167		QueryAuditByTemplate(i_templates         => v_templates,
1168							 i_date              => i_date_from,
1169							 i_time              => i_time_from,
1170							 i_date_to           => i_date_to,
1171							 i_time_to           => i_time_to,
1172							 i_user_codes		 => i_user_codes,
1173							 i_last_audit_only   => k_true,
1174							 o_audit_events      => o_audit_events,
1175							 o_logged_attributes => v_logged_attributes,
1176							 o_error_reported    => v_error_reported,
1177							 o_error_response    => v_error_response);
1178
1179	END QueryAuditByTables;
1180
1181	FUNCTION AuditExistByTables( i_table_codes  IN VARCHAR2,
1182								 i_pkeys_values IN VARCHAR2 DEFAULT NULL,
1183								 i_date_from    IN VARCHAR2 DEFAULT NULL,
1184								 i_time_from    IN NUMBER DEFAULT NULL,
1185								 i_date_to      IN VARCHAR2 DEFAULT NULL,
1186								 i_time_to      IN NUMBER DEFAULT NULL,
1187								 i_user_codes	IN VARCHAR2 DEFAULT NULL)
1188		RETURN t.logical%TYPE IS
1189
1190		v_audit_events		SYS_REFCURSOR;
1191		v_audit_event_row 	t_audit_events;
1192		v_audit_found 		t.logical%TYPE := k_false;
1193	BEGIN
1194		QueryAuditByTables(  i_table_codes       => i_table_codes,
1195							 i_date_from         => i_date_from,
1196							 i_pkeys_values		 => i_pkeys_values,
1197							 i_time_from         => i_time_from,
1198							 i_date_to           => i_date_to,
1199							 i_time_to           => i_time_to,
1200							 i_user_codes		 => i_user_codes,
1201							 o_audit_events      => v_audit_events);
1202
1203		FETCH v_audit_events
1204			INTO v_audit_event_row;
1205
1206		IF v_audit_events%FOUND
1207		THEN
1208			v_audit_found := k_true;
1209		END IF;
1210
1211		CLOSE v_audit_events;
1212
1213		RETURN v_audit_found;
1214
1215	EXCEPTION WHEN OTHERS THEN
1216		v_audit_found := NULL;
1217		galaxy_util.display_message('msg.AuditDefinitionWrong');
1218	END AuditExistByTables;
1219
1220-------------------------------------------------------------------------------------------------
1221	-- Procedure : FindOfferItemByTitleModify
1222	-- Added By Bakulesh @28/03/2012
1223	-- Description : for passed in  title id return offer items which are not expired
1224	-------------------------------------------------------------------------------------------------
1225    PROCEDURE FindOfferItemByTitleModify(
1226								i_title_id				IN	title.title_id%TYPE,
1227								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
1228								i_checkAsMain			IN  k.logical%TYPE DEFAULT k_true,
1229								i_checkAsAssociated		IN  k.logical%TYPE DEFAULT k_true,
1230								i_checkAsEmbbeded		IN  k.logical%TYPE DEFAULT k_true,
1231								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
1232								o_offer_item_ids 		OUT SYS_REFCURSOR,
1233								o_properties     		OUT SYS_REFCURSOR,
1234								o_error_reported 		OUT t.logical%TYPE,
1235								o_error_response 		OUT SYS_REFCURSOR
1236										)
1237	IS
1238		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByTitleModify';
1239		--local variables
1240		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
1241		v_error_reported				t.logical%TYPE;
1242		v_title_type					title.title_type%TYPE;
1243		-- ref cursors
1244		c_properties					SYS_REFCURSOR;
1245		c_offer_item_ids				SYS_REFCURSOR;
1246		c_error_response				SYS_REFCURSOR;
1247	BEGIN
1248		o_error_reported 	:= k_false;
1249		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
1250			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
1251			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
1252		END IF;
1253		--get title type
1254		SELECT 	title_type
1255		INTO	v_title_type
1256		FROM	title t
1257		WHERE	t.title_id = i_title_id;
1258		--get setting if needed i.e. once per session
1259		IF	NVL(g_interface_id, 0) = 0 THEN
1260			IF(i_interface_id IS NULL) THEN
1261				getSettings;
1262			ELSE
1263				getSettings(i_interface_id);
1264			END IF;
1265		END IF;
1266		--title type is not set for ignore - it means continue
1267		IF	tbl_ignore_title_type(v_title_type) = k_false THEN
1268			--call embbeded only if title is not program
1269			IF	v_title_type != k_program_title_tp
1270			AND	i_checkAsEmbbeded = k_true THEN
1271				--get embedded content title's offer item ids
1272				on_demand_api.FindOfferItemByEmbeddedContent(
1273										i_title_id 		 => i_title_id,
1274										i_version_id 	 => -1, --so we don't get tx record with no version, tx schedule table version_id will be 0 for tx event without title
1275										i_required_attrs => i_required_attrs,
1276										o_offer_item_ids => c_offer_item_ids,
1277										o_properties 	 => c_properties,
1278										o_error_reported => v_error_reported,
1279										o_error_response => c_error_response
1280												);
1281				--g.debug('called proc', v_called_procedure);
1282				IF 	v_error_reported = k_false THEN
1283					findUnExpiredOfferItems(
1284									i_offer_item_ids => c_offer_item_ids
1285											);
1286				ELSE
1287					mergeErrors(
1288							i_error_response => c_error_response
1289								);
1290				END IF;
1291				g_delete_gtt_table := 0;
1292				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1293				IF	c_error_response%ISOPEN THEN
1294					CLOSE c_error_response;
1295				END IF;
1296				IF c_properties%ISOPEN THEN
1297					CLOSE c_properties;
1298				END IF;
1299				IF	c_offer_item_ids%ISOPEN THEN
1300					CLOSE c_offer_item_ids;
1301				END IF;
1302			END IF;
1303			--get program title's offer item ids if needed
1304			v_error_reported := k_false;
1305			IF	i_checkAsMain = k_true THEN
1306				on_demand_api.FindOfferItemsByProgram(
1307											i_title_id 	 => i_title_id,
1308											i_version_id 	 => 0,
1309											i_required_attrs => i_required_attrs,
1310											o_offer_item_ids => c_offer_item_ids,
1311											o_properties 	 => c_properties,
1312											o_error_reported => v_error_reported,
1313											o_error_response => c_error_response
1314													);
1315				--g.debug('called proc', v_called_procedure);
1316				IF 	v_error_reported = k_false THEN
1317					findUnExpiredOfferItems(
1318									i_offer_item_ids => c_offer_item_ids
1319											);
1320				ELSE
1321						mergeErrors(
1322								i_error_response => c_error_response
1323									);
1324				END IF;
1325				g_delete_gtt_table := 0;
1326				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1327				IF	c_error_response%ISOPEN THEN
1328					CLOSE c_error_response;
1329				END IF;
1330				IF c_properties%ISOPEN THEN
1331					CLOSE c_properties;
1332				END IF;
1333				IF	c_offer_item_ids%ISOPEN THEN
1334					CLOSE c_offer_item_ids;
1335				END IF;
1336			END  IF;-- checkAsMain > 0
1337			--get associated content title's offer item ids if needed
1338			v_error_reported := k_false;
1339			IF	i_checkAsAssociated = k_true THEN
1340				on_demand_api.FindOfferItemByAssociatContent(
1341											i_title_id 		 => i_title_id,
1342											i_required_attrs => i_required_attrs,
1343											o_offer_item_ids => c_offer_item_ids,
1344											o_properties 	 => c_properties,
1345											o_error_reported => v_error_reported,
1346											o_error_response => c_error_response
1347															);
1348				--
1349				--g.debug('called proc', v_called_procedure);
1350				IF 	v_error_reported = k_false THEN
1351					findUnExpiredOfferItems(
1352									i_offer_item_ids => c_offer_item_ids
1353											);
1354				ELSE
1355						mergeErrors(
1356								i_error_response => c_error_response
1357									);
1358				END IF;
1359				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1360				IF	c_error_response%ISOPEN THEN
1361					CLOSE c_error_response;
1362				END IF;
1363				IF c_properties%ISOPEN THEN
1364					CLOSE c_properties;
1365				END IF;
1366				IF	c_offer_item_ids%ISOPEN THEN
1367					CLOSE c_offer_item_ids;
1368				END IF;
1369			END IF;--checkAsAssociated > 0
1370			--check any offer item found or not
1371			SELECT 	time_1
1372			INTO	v_offer_item_id
1373			FROM	glxmem_tmp_decision_source t
1374			WHERE	t.decision_source_id = k_dummy_id
1375				AND	ROWNUM = 1;
1376		END IF;--title type was set as ignore
1377		--open ref cursor for all un expired offer items
1378		OPEN o_offer_item_ids FOR
1379		SELECT	t.time_1 			 AS offerItemId,
1380				t.time_2	 		 AS offerId,
1381				t.alpha_1	 		 AS offerName,
1382				t.time_3		 	 AS serviceID,
1383				t.alpha_2		 	 AS serviceName,
1384				t.alpha_3		 	 AS offerStatusCode,
1385				t.alpha_4			 AS offerStausDescr,
1386				t.time_6			 AS offerStatusISN,
1387				t.alpha_5	 		 AS offerAvaibilityEndDate,
1388				t.time_4	 		 AS offerAvaibilityEndTime,
1389				t.alpha_6		 	 AS offerExpiryDate,
1390				t.time_5		 	 AS offerExpiryTime
1391		FROM	glxmem_tmp_decision_source t
1392		WHERE	t.decision_source_id = k_dummy_id;
1393		--
1394		g_delete_gtt_table := NULL;
1395		--set empty ref cursors for properties, error as nothing to report back.
1396		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
1397    	o_error_response := error_handling_api.GetErrorRefCursor();
1398	EXCEPTION
1399		WHEN	e_error THEN
1400				g_delete_gtt_table := NULL;
1401				o_error_reported := k_true;
1402				IF 	NOT o_offer_item_ids%ISOPEN THEN
1403					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1404				END IF;
1405				IF 	NOT o_properties%ISOPEN THEN
1406					o_properties := error_handling_api.getemptyrefcursor();
1407				END IF;
1408				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1409				IF c_properties%ISOPEN THEN
1410					CLOSE c_properties;
1411				END IF;
1412		WHEN 	NO_DATA_FOUND THEN
1413				g_delete_gtt_table := NULL;
1414				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
1415													i_message         => k_procedure_name || ': ' ||
1416																	 'No data found for title id = ' || i_title_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated,
1417													i_broadcaster_ref => NULL,
1418													i_sequence        => 1,
1419													i_result          => k_rslt_tp_notvalid,
1420													i_message_type    => k_err_msg_type,
1421													i_message_code    => SQLCODE);
1422				o_error_reported := k_true;
1423				IF 	NOT o_offer_item_ids%ISOPEN THEN
1424					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1425				END IF;
1426					IF 	NOT o_properties%ISOPEN THEN
1427					o_properties := error_handling_api.getemptyrefcursor();
1428				END IF;
1429				o_error_response := error_handling_api.GetErrorRefCursor();
1430		WHEN	OTHERS THEN
1431				g_delete_gtt_table := NULL;
1432				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
1433													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
1434																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_title_id: ' ||
1435																	 i_title_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated ,
1436													i_broadcaster_ref => NULL,
1437													i_sequence        => 1,
1438													i_result          => k_rslt_tp_notvalid,
1439													i_message_type    => k_err_msg_type,
1440													i_message_code    => SQLCODE);
1441				o_error_reported := k_true;
1442				IF NOT o_offer_item_ids%ISOPEN THEN
1443					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1444				END IF;
1445				IF 	NOT o_properties%ISOPEN THEN
1446					o_properties := error_handling_api.getemptyrefcursor();
1447				END IF;
1448				o_error_response := error_handling_api.GetErrorRefCursor();
1449				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1450				IF c_properties%ISOPEN THEN
1451					CLOSE c_properties;
1452				END IF;
1453	END FindOfferItemByTitleModify;
1454	-------------------------------------------------------------------------------------------------
1455	-- Procedure : FindOfferItemByVersionModify
1456	-- Added By Bakulesh @30/03/2012
1457	-- Description : for passed in  version id return offer items which are not expired
1458	-------------------------------------------------------------------------------------------------
1459    PROCEDURE FindOfferItemByVersionModify(
1460								i_version_id			IN	version.version_id%TYPE,
1461								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
1462								i_checkAsMain			IN  k.logical%TYPE DEFAULT k_true,
1463								i_checkAsAssociated		IN  k.logical%TYPE DEFAULT k_true,
1464								i_checkAsEmbbeded		IN  k.logical%TYPE DEFAULT k_true,
1465								i_changeType			IN	VARCHAR2 DEFAULT 'Modify',
1466								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
1467								o_offer_item_ids 		OUT SYS_REFCURSOR,
1468								o_properties     		OUT SYS_REFCURSOR,
1469								o_error_reported 		OUT t.logical%TYPE,
1470								o_error_response 		OUT SYS_REFCURSOR
1471										  )
1472	IS
1473		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByVersionModify';
1474		--local variables
1475		v_error_reported				t.logical%TYPE;
1476		v_title_type					title.title_type%TYPE;
1477		v_title_id						title.title_id%TYPE;
1478		v_continue						k.logical%TYPE;
1479		-- ref cursors
1480		c_properties					SYS_REFCURSOR;
1481		c_error_response				SYS_REFCURSOR;
1482		c_offer_item_ids				SYS_REFCURSOR;
1483	BEGIN
1484		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
1485			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
1486			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
1487		END IF;
1488		o_error_reported 	:= k_false;
1489		--get title type
1490		SELECT 	title_type,
1491				t.title_id
1492		INTO	v_title_type,
1493				v_title_id
1494		FROM	version v
1495		JOIN	title t ON (t.title_id = v.title_id)
1496		WHERE	v.version_id = i_version_id;
1497		--
1498		v_continue := k_true;
1499		--get setting if needed i.e. once per session
1500		IF	NVL(g_interface_id, 0) = 0 THEN
1501			IF i_interface_id IS NULL THEN
1502				getSettings;
1503			ELSE
1504				getSettings(i_interface_id);
1505			END IF;
1506		END IF;
1507		--check title type is set for ignore or not
1508		--if change type is Insert then we don't need to filter title type
1509		IF	i_changeType NOT IN ('Insert','INSERT','Create','CREATE') THEN
1510			IF	tbl_ignore_title_type(v_title_type) = k_true THEN
1511				v_continue := k_false;
1512			END IF;
1513		END IF;
1514		--
1515		IF	v_continue = k_true THEN
1516			--get associated title if needed
1517			IF	i_checkAsAssociated = k_true THEN
1518				--call associated title core API to get any title of version linked to any offer
1519				on_demand_api.FindOfferItemByAssociatContent(
1520											i_title_id 		 => v_title_id,
1521											i_required_attrs => i_required_attrs,
1522											o_offer_item_ids => c_offer_item_ids,
1523											o_properties 	 => c_properties,
1524											o_error_reported => v_error_reported,
1525											o_error_response => c_error_response
1526															);
1527				--
1528				IF 	v_error_reported = k_false THEN
1529					findUnExpiredOfferItems(
1530									i_offer_item_ids => c_offer_item_ids
1531											);
1532				ELSE
1533					mergeErrors(
1534							i_error_response => c_error_response
1535								);
1536				END IF;
1537				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1538				IF	c_error_response%ISOPEN THEN
1539					CLOSE c_error_response;
1540				END IF;
1541				IF 	c_properties%ISOPEN THEN
1542					CLOSE c_properties;
1543				END IF;
1544				IF	c_offer_item_ids%ISOPEN THEN
1545					CLOSE c_offer_item_ids;
1546				END IF;
1547				v_error_reported := k_false;
1548				g_delete_gtt_table := k_false;--we need to append data in GTT table
1549			END IF;--checkasAssociated > 0
1550			--get program version if needed
1551			IF	i_checkAsMain = k_true THEN
1552				--get program version's offer item ids
1553				on_demand_api.FindOfferItemsByProgram(
1554											i_title_id 	 	 => 0,
1555											i_version_id 	 => i_version_id,
1556											i_required_attrs => i_required_attrs,
1557											o_offer_item_ids => c_offer_item_ids,
1558											o_properties 	 => c_properties,
1559											o_error_reported => v_error_reported,
1560											o_error_response => c_error_response
1561													);
1562				--
1563				IF 	v_error_reported = k_false THEN
1564					findUnExpiredOfferItems(
1565										i_offer_item_ids => c_offer_item_ids
1566										   );
1567				ELSE
1568					mergeErrors(
1569							i_error_response => c_error_response
1570								);
1571				END IF;
1572				--
1573				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1574				IF	c_error_response%ISOPEN THEN
1575					CLOSE c_error_response;
1576				END IF;
1577				IF c_properties%ISOPEN THEN
1578					CLOSE c_properties;
1579				END IF;
1580				IF	c_offer_item_ids%ISOPEN THEN
1581					CLOSE c_offer_item_ids;
1582				END IF;
1583				v_error_reported := k_false;
1584				g_delete_gtt_table := k_false;--we need to append data in GTT table
1585			END IF;--checkAsMain > 0
1586			--get embedded version's offer item ids if needed
1587			IF	i_checkAsEmbbeded = k_true THEN
1588				on_demand_api.FindOfferItemByEmbeddedContent(
1589											i_title_id 	 	 => 0,
1590											i_version_id 	 => i_version_id,
1591											i_required_attrs => i_required_attrs,
1592											o_offer_item_ids => c_offer_item_ids,
1593											o_properties 	 => c_properties,
1594											o_error_reported => v_error_reported,
1595											o_error_response => c_error_response
1596															);
1597				--
1598				IF 	v_error_reported = k_false THEN
1599					findUnExpiredOfferItems(
1600										i_offer_item_ids => c_offer_item_ids
1601										   );
1602				ELSE
1603					mergeErrors(
1604							i_error_response => c_error_response
1605								);
1606				END IF;
1607				--
1608				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1609				IF	c_error_response%ISOPEN THEN
1610					CLOSE c_error_response;
1611				END IF;
1612				IF c_properties%ISOPEN THEN
1613					CLOSE c_properties;
1614				END IF;
1615				IF	c_offer_item_ids%ISOPEN THEN
1616					CLOSE c_offer_item_ids;
1617				END IF;
1618			END IF;--checkAsEmbedded > 0
1619			--check any offer item found or not
1620			SELECT 	time_1
1621			INTO	v_title_id
1622			FROM	glxmem_tmp_decision_source t
1623			WHERE	t.decision_source_id = k_dummy_id
1624				AND	ROWNUM = 1;
1625		END IF;--continue > 0
1626		--open ref cursor for all un expired offer items
1627		OPEN 	o_offer_item_ids FOR
1628		SELECT	t.time_1 			 AS offerItemId,
1629				t.time_2	 		 AS offerId,
1630				t.alpha_1	 		 AS offerName,
1631				t.time_3		 	 AS serviceID,
1632				t.alpha_2		 	 AS serviceName,
1633				t.alpha_3		 	 AS offerStatusCode,
1634				t.alpha_4			 AS offerStausDescr,
1635				t.time_6			 AS offerStatusISN,
1636				t.alpha_5	 		 AS offerAvaibilityEndDate,
1637				t.time_4	 		 AS offerAvaibilityEndTime,
1638				t.alpha_6		 	 AS offerExpiryDate,
1639				t.time_5		 	 AS offerExpiryTime
1640		FROM	glxmem_tmp_decision_source t
1641		WHERE	t.decision_source_id = k_dummy_id;
1642		--
1643		g_delete_gtt_table := NULL;
1644		--set empty ref cursors for properties, error as nothing to report back.
1645		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
1646    	o_error_response := error_handling_api.GetErrorRefCursor();
1647	EXCEPTION
1648		WHEN	e_error THEN
1649				o_error_reported := k_true;
1650				g_delete_gtt_table := NULL;
1651				IF 	NOT o_offer_item_ids%ISOPEN THEN
1652					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1653				END IF;
1654       			IF 	NOT o_properties%ISOPEN THEN
1655					o_properties := error_handling_api.getemptyrefcursor();
1656				END IF;
1657		WHEN 	NO_DATA_FOUND THEN
1658				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
1659													i_message         => k_procedure_name || ': ' ||
1660																	 'No data found for version id = ' || i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated,
1661													i_broadcaster_ref => NULL,
1662													i_sequence        => 1,
1663													i_result          => k_rslt_tp_notvalid,
1664													i_message_type    => k_err_msg_type,
1665													i_message_code    => SQLCODE);
1666				o_error_reported := k_true;
1667				g_delete_gtt_table := NULL;
1668				IF 	NOT o_offer_item_ids%ISOPEN THEN
1669					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1670				END IF;
1671       			IF 	NOT o_properties%ISOPEN THEN
1672					o_properties := error_handling_api.getemptyrefcursor();
1673				END IF;
1674    	    	o_error_response := error_handling_api.GetErrorRefCursor();
1675		WHEN 	OTHERS THEN
1676				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
1677													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
1678																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_version_id: ' ||
1679																	 i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated ,
1680													i_broadcaster_ref => NULL,
1681													i_sequence        => 1,
1682													i_result          => k_rslt_tp_notvalid,
1683													i_message_type    => k_err_msg_type,
1684													i_message_code    => SQLCODE);
1685				o_error_reported := k_true;
1686				g_delete_gtt_table := NULL;
1687				IF NOT o_offer_item_ids%ISOPEN THEN
1688					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1689				END IF;
1690        		IF 	NOT o_properties%ISOPEN THEN
1691					o_properties := error_handling_api.getemptyrefcursor();
1692				END IF;
1693    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
1694	END FindOfferItemByVersionModify;
1695	-------------------------------------------------------------------------------------------------
1696	-- Procedure : FindOfferItemByVersionInsert
1697	-- Added By Bakulesh @30/03/2012
1698	-- Description : for passed in  version id return offer items which are not expired
1699	-------------------------------------------------------------------------------------------------
1700    PROCEDURE FindOfferItemByVersionInsert(
1701								i_version_id			IN	version.version_id%TYPE,
1702								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
1703								i_checkAsMain			IN  k.logical%TYPE DEFAULT k_true,
1704								i_checkAsAssociated		IN  k.logical%TYPE DEFAULT k_true,
1705								i_checkAsEmbbeded		IN  k.logical%TYPE DEFAULT k_true,
1706								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
1707								o_offer_item_ids 		OUT SYS_REFCURSOR,
1708								o_properties     		OUT SYS_REFCURSOR,
1709								o_error_reported 		OUT t.logical%TYPE,
1710								o_error_response 		OUT SYS_REFCURSOR
1711										)
1712	IS
1713		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByVersionInsert';
1714		--local variables
1715		v_error_reported				t.logical%TYPE;
1716	BEGIN
1717		o_error_reported 	:= k_false;
1718		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
1719			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
1720			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
1721		END IF;
1722		--call following api to get offer item ids linked to title, as version was change title will be there and using which
1723		--calling versionModify as there is no different action - may be we don't need this API
1724		FindOfferItemByVersionModify(
1725							i_version_id 	 => i_version_id,
1726							i_required_attrs => i_required_attrs,
1727							i_checkAsMain	 => i_checkAsMain,
1728							i_checkAsAssociated => i_checkAsAssociated,
1729							i_checkAsEmbbeded => i_checkAsEmbbeded,
1730							i_changeType	 => 'Insert',
1731							i_interface_id	=>	i_interface_id,
1732							o_offer_item_ids => o_offer_item_ids,
1733							o_properties 	 => o_properties,
1734							o_error_reported => v_error_reported,
1735							o_error_response => o_error_response
1736									);
1737		--
1738		g_delete_gtt_table := NULL;
1739	EXCEPTION
1740		WHEN 	NO_DATA_FOUND THEN
1741				g_delete_gtt_table := NULL;
1742				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
1743													i_message         => k_procedure_name || ': ' ||
1744																	 'No data found for version id = ' || i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated,
1745													i_broadcaster_ref => NULL,
1746													i_sequence        => 1,
1747													i_result          => k_rslt_tp_notvalid,
1748													i_message_type    => k_err_msg_type,
1749													i_message_code    => SQLCODE);
1750				o_error_reported := k_true;
1751				IF 	NOT o_offer_item_ids%ISOPEN THEN
1752					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1753				END IF;
1754       			IF 	NOT o_properties%ISOPEN THEN
1755					o_properties := error_handling_api.getemptyrefcursor();
1756				END IF;
1757    	    	o_error_response := error_handling_api.GetErrorRefCursor();
1758		WHEN 	OTHERS THEN
1759				g_delete_gtt_table := NULL;
1760				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
1761													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
1762																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_version_id: ' ||
1763																	 i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated ,
1764													i_broadcaster_ref => NULL,
1765													i_sequence        => 1,
1766													i_result          => k_rslt_tp_notvalid,
1767													i_message_type    => k_err_msg_type,
1768													i_message_code    => SQLCODE);
1769				o_error_reported := k_true;
1770				IF NOT o_offer_item_ids%ISOPEN THEN
1771					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1772				END IF;
1773        		IF 	NOT o_properties%ISOPEN THEN
1774					o_properties := error_handling_api.getemptyrefcursor();
1775				END IF;
1776    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
1777	END FindOfferItemByVersionInsert;
1778	-------------------------------------------------------------------------------------------------
1779	-- Procedure : FindOfferItemByVersionDelete
1780	-- Added By Bakulesh @13/05/2012
1781	-- Description : for passed in  version id + associated title return offer items which are not expired
1782	-------------------------------------------------------------------------------------------------
1783    PROCEDURE FindOfferItemByVersionDelete(
1784								i_version_id			IN	version.version_id%TYPE,
1785								i_title_id				IN	title.title_id%TYPE,
1786								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
1787								i_checkAsMain			IN  k.logical%TYPE DEFAULT k_true,
1788								i_checkAsAssociated		IN  k.logical%TYPE DEFAULT k_true,
1789								i_checkAsEmbbeded		IN  k.logical%TYPE DEFAULT k_true,
1790								o_offer_item_ids 		OUT SYS_REFCURSOR,
1791								o_properties     		OUT SYS_REFCURSOR,
1792								o_error_reported 		OUT t.logical%TYPE,
1793								o_error_response 		OUT SYS_REFCURSOR
1794										)
1795	IS
1796		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByVersionDelete';
1797		--local variables
1798		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
1799		v_error_reported				t.logical%TYPE;
1800		-- ref cursors
1801		c_properties					SYS_REFCURSOR;
1802		c_error_response				SYS_REFCURSOR;
1803		c_offer_item_ids				SYS_REFCURSOR;
1804	BEGIN
1805		o_error_reported 	:= k_false;
1806		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
1807			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
1808			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
1809		END IF;
1810		--
1811		--get program version if needed
1812		IF	i_checkAsMain = k_true THEN
1813			--get program version's offer item ids
1814			--PS The embedded/assoicated contents needs title id, as version was deleted just get program version data
1815			on_demand_api.FindOfferItemsByProgram(
1816									i_title_id 	 	 => 0,
1817									i_version_id 	 => i_version_id,
1818									i_required_attrs => i_required_attrs,
1819									o_offer_item_ids => c_offer_item_ids,
1820									o_properties 	 => c_properties,
1821									o_error_reported => v_error_reported,
1822									o_error_response => o_error_response
1823											);
1824			--
1825			IF 	v_error_reported = k_false THEN
1826				findUnExpiredOfferItems(
1827								i_offer_item_ids => c_offer_item_ids
1828								   );
1829			ELSE
1830				mergeErrors(
1831					i_error_response => c_error_response
1832						);
1833			END IF;
1834			--
1835			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1836			IF	c_error_response%ISOPEN THEN
1837				CLOSE c_error_response;
1838			END IF;
1839			IF c_properties%ISOPEN THEN
1840				CLOSE c_properties;
1841			END IF;
1842			IF	c_offer_item_ids%ISOPEN THEN
1843				CLOSE c_offer_item_ids;
1844			END IF;
1845			v_error_reported := k_false;
1846			g_delete_gtt_table := k_false;--we need to append data in GTT table
1847		END IF;--v_checkAsMain > 0
1848		--get embedded version's offer item ids if needed
1849		IF	i_checkAsEmbbeded = k_true THEN
1850			on_demand_api.FindOfferItemByEmbeddedContent(
1851										i_title_id 	 	 => 0,
1852										i_version_id 	 => i_version_id,
1853										i_required_attrs => i_required_attrs,
1854										o_offer_item_ids => c_offer_item_ids,
1855										o_properties 	 => c_properties,
1856										o_error_reported => v_error_reported,
1857										o_error_response => c_error_response
1858														);
1859			--
1860			IF 	v_error_reported = k_false THEN
1861				findUnExpiredOfferItems(
1862									i_offer_item_ids => c_offer_item_ids
1863									   );
1864			ELSE
1865				mergeErrors(
1866						i_error_response => c_error_response
1867							);
1868			END IF;
1869			--
1870			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1871			IF	c_error_response%ISOPEN THEN
1872				CLOSE c_error_response;
1873			END IF;
1874			IF c_properties%ISOPEN THEN
1875				CLOSE c_properties;
1876			END IF;
1877			IF	c_offer_item_ids%ISOPEN THEN
1878				CLOSE c_offer_item_ids;
1879			END IF;
1880		END IF;--checkAsEmbedded > 0
1881		--get associated version offer items if needed
1882		IF	NVL(i_title_id, 0) > 0
1883		AND	i_checkAsAssociated = k_true THEN
1884			v_error_reported := k_false;
1885			g_delete_gtt_table := k_false;--we need to append data in GTT table
1886			--get associated titel's offer item ids
1887			on_demand_api.FindOfferItemByAssociatContent(
1888									i_title_id 	 	 => i_title_id,
1889									i_required_attrs => i_required_attrs,
1890									o_offer_item_ids => c_offer_item_ids,
1891									o_properties 	 => c_properties,
1892									o_error_reported => v_error_reported,
1893									o_error_response => c_error_response
1894													);
1895			--
1896			IF 	v_error_reported = k_false THEN
1897				findUnExpiredOfferItems(
1898								i_offer_item_ids => c_offer_item_ids
1899								   );
1900			ELSE
1901				mergeErrors(
1902					i_error_response => c_error_response
1903						);
1904			END IF;
1905			--
1906			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1907			IF	c_error_response%ISOPEN THEN
1908				CLOSE c_error_response;
1909			END IF;
1910			IF c_properties%ISOPEN THEN
1911				CLOSE c_properties;
1912			END IF;
1913			IF	c_offer_item_ids%ISOPEN THEN
1914				CLOSE c_offer_item_ids;
1915			END IF;
1916		END IF;--title id supplied
1917		--check any offer item found or not
1918		SELECT 	time_1
1919		INTO	v_offer_item_id
1920		FROM	glxmem_tmp_decision_source t
1921		WHERE	t.decision_source_id = k_dummy_id
1922			AND	ROWNUM = 1;
1923		--open ref cursor for all un expired offer items
1924		OPEN o_offer_item_ids FOR
1925		SELECT	t.time_1 			 AS offerItemId,
1926				t.time_2	 		 AS offerId,
1927				t.alpha_1	 		 AS offerName,
1928				t.time_3		 	 AS serviceID,
1929				t.alpha_2		 	 AS serviceName,
1930				t.alpha_3		 	 AS offerStatusCode,
1931				t.alpha_4			 AS offerStausDescr,
1932				t.time_6			 AS offerStatusISN,
1933				t.alpha_5	 		 AS offerAvaibilityEndDate,
1934				t.time_4	 		 AS offerAvaibilityEndTime,
1935				t.alpha_6		 	 AS offerExpiryDate,
1936				t.time_5		 	 AS offerExpiryTime
1937		FROM	glxmem_tmp_decision_source t
1938		WHERE	t.decision_source_id = k_dummy_id;
1939		--
1940		g_delete_gtt_table := NULL;
1941		--set empty ref cursors for properties, error as nothing to report back.
1942		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
1943		o_error_response := error_handling_api.GetErrorRefCursor();
1944	EXCEPTION
1945		WHEN	e_error THEN
1946				o_error_reported := k_true;
1947				g_delete_gtt_table := NULL;
1948				IF 	NOT o_offer_item_ids%ISOPEN THEN
1949					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1950				END IF;
1951				IF 	NOT o_properties%ISOPEN THEN
1952					o_properties := error_handling_api.getemptyrefcursor();
1953				END IF;
1954				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
1955				IF	c_error_response%ISOPEN THEN
1956					CLOSE c_error_response;
1957				END IF;
1958				IF c_properties%ISOPEN THEN
1959					CLOSE c_properties;
1960				END IF;
1961		WHEN 	NO_DATA_FOUND THEN
1962				g_delete_gtt_table := NULL;
1963				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
1964													i_message         => k_procedure_name || ': ' ||
1965																	 'No data found for version id = ' || i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated,
1966													i_broadcaster_ref => NULL,
1967													i_sequence        => 1,
1968													i_result          => k_rslt_tp_notvalid,
1969													i_message_type    => k_err_msg_type,
1970													i_message_code    => SQLCODE);
1971				o_error_reported := k_true;
1972				IF 	NOT o_offer_item_ids%ISOPEN THEN
1973					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1974				END IF;
1975				IF	c_error_response%ISOPEN THEN
1976					CLOSE c_error_response;
1977				END IF;
1978       			IF 	NOT o_properties%ISOPEN THEN
1979					o_properties := error_handling_api.getemptyrefcursor();
1980				END IF;
1981		 	o_error_response := error_handling_api.GetErrorRefCursor();
1982		WHEN 	OTHERS THEN
1983				g_delete_gtt_table := NULL;
1984				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
1985													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
1986																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_version_id: ' ||
1987																	 i_version_id||' checkMain='||i_checkAsMain||' checkEmbbedded='||i_checkAsEmbbeded||' checkAssociated='||i_checkAsAssociated ,
1988													i_broadcaster_ref => NULL,
1989													i_sequence        => 1,
1990													i_result          => k_rslt_tp_notvalid,
1991													i_message_type    => k_err_msg_type,
1992													i_message_code    => SQLCODE);
1993				o_error_reported := k_true;
1994				IF NOT o_offer_item_ids%ISOPEN THEN
1995					o_offer_item_ids := error_handling_api.getemptyrefcursor();
1996				END IF;
1997				IF	c_error_response%ISOPEN THEN
1998					CLOSE c_error_response;
1999				END IF;
2000				IF 	NOT o_properties%ISOPEN THEN
2001					o_properties := error_handling_api.getemptyrefcursor();
2002				END IF;
2003				o_error_response := error_handling_api.GetErrorRefCursor();
2004				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2005				IF c_properties%ISOPEN THEN
2006					CLOSE c_properties;
2007				END IF;
2008	END FindOfferItemByVersionDelete;
2009	-------------------------------------------------------------------------------------------------
2010	-- Procedure : FindOfferItemByPurchaseModify
2011	-- Added By Bakulesh @30/03/2012
2012	-- Description : for passed in  purchase id return offer items which are not expired
2013	-------------------------------------------------------------------------------------------------
2014    PROCEDURE FindOfferItemByPurchaseModify(
2015								i_purchase_id			IN	purchase.purchase_id%TYPE,
2016								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2017								o_offer_item_ids 		OUT SYS_REFCURSOR,
2018								o_properties     		OUT SYS_REFCURSOR,
2019								o_error_reported 		OUT t.logical%TYPE,
2020								o_error_response 		OUT SYS_REFCURSOR
2021										)
2022	IS
2023		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByPurchaseModify';
2024		--local variables
2025		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2026		v_error_reported				t.logical%TYPE;
2027		v_called_procedure				VARCHAR2(60);
2028		-- ref cursors
2029		c_properties					SYS_REFCURSOR;
2030		c_error_response				SYS_REFCURSOR;
2031		c_offer_item_ids				SYS_REFCURSOR;
2032	BEGIN
2033		o_error_reported 	:= k_false;
2034		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2035			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2036			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2037		END IF;
2038		--get program purchase's offer item ids
2039		on_demand_api.FindOfferItemByPurchase(
2040									i_purchase_id 	 => i_purchase_id,
2041									i_required_attrs => i_required_attrs,
2042									o_offer_item_ids => c_offer_item_ids,
2043									o_properties 	 => c_properties,
2044									o_error_reported => v_error_reported,
2045									o_error_response => o_error_response
2046											);
2047		--
2048		IF 	v_error_reported = k_false THEN
2049			findUnExpiredOfferItems(
2050								i_offer_item_ids => c_offer_item_ids
2051								   );
2052		ELSE
2053			--
2054			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2055													i_message         => 'Error while processing '||v_called_procedure||'. Called from '||k_procedure_name,
2056													i_broadcaster_ref => NULL,
2057													i_sequence        => 1,
2058													i_result          => k_rslt_tp_notvalid,
2059													i_message_type    => k_err_msg_type,
2060													i_message_code    => SQLCODE);
2061			--error found abort process and send error resonse to exception section
2062			o_error_reported  := v_error_reported;
2063			RAISE e_error;
2064		END IF;
2065		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2066		IF	c_error_response%ISOPEN THEN
2067			CLOSE c_error_response;
2068		END IF;
2069		IF c_properties%ISOPEN THEN
2070			CLOSE c_properties;
2071		END IF;
2072		--
2073		--check any offer item found or not
2074		SELECT 	time_1
2075		INTO	v_offer_item_id
2076		FROM	glxmem_tmp_decision_source t
2077		WHERE	t.decision_source_id = k_dummy_id
2078			AND	ROWNUM = 1;
2079		--open ref cursor for all un expired offer items
2080		OPEN o_offer_item_ids FOR
2081		SELECT	t.time_1 			 AS offerItemId,
2082				t.time_2	 		 AS offerId,
2083				t.alpha_1	 		 AS offerName,
2084				t.time_3		 	 AS serviceID,
2085				t.alpha_2		 	 AS serviceName,
2086				t.alpha_3		 	 AS offerStatusCode,
2087				t.alpha_4			 AS offerStausDescr,
2088				t.time_6			 AS offerStatusISN,
2089				t.alpha_5	 		 AS offerAvaibilityEndDate,
2090				t.time_4	 		 AS offerAvaibilityEndTime,
2091				t.alpha_6		 	 AS offerExpiryDate,
2092				t.time_5		 	 AS offerExpiryTime
2093		FROM	glxmem_tmp_decision_source t
2094		WHERE	t.decision_source_id = k_dummy_id;
2095		--
2096		g_delete_gtt_table := NULL;
2097		--set empty ref cursors for properties, error as nothing to report back.
2098		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2099    	o_error_response := error_handling_api.GetErrorRefCursor();
2100	EXCEPTION
2101		WHEN	e_error THEN
2102				g_delete_gtt_table := NULL;
2103				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2104				IF	c_error_response%ISOPEN THEN
2105					CLOSE c_error_response;
2106				END IF;
2107				IF c_properties%ISOPEN THEN
2108				   CLOSE c_properties;
2109				END IF;
2110				o_error_reported := k_true;
2111				IF 	NOT o_offer_item_ids%ISOPEN THEN
2112					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2113				END IF;
2114       			IF 	NOT o_properties%ISOPEN THEN
2115					o_properties := error_handling_api.getemptyrefcursor();
2116				END IF;
2117		WHEN 	NO_DATA_FOUND THEN
2118				g_delete_gtt_table := NULL;
2119				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2120													i_message         => k_procedure_name || ': ' ||
2121																	 'No data found for purchase id = ' || i_purchase_id,
2122													i_broadcaster_ref => NULL,
2123													i_sequence        => 1,
2124													i_result          => k_rslt_tp_notvalid,
2125													i_message_type    => k_err_msg_type,
2126													i_message_code    => SQLCODE);
2127				o_error_reported := k_true;
2128				IF 	NOT o_offer_item_ids%ISOPEN THEN
2129					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2130				END IF;
2131       			IF 	NOT o_properties%ISOPEN THEN
2132					o_properties := error_handling_api.getemptyrefcursor();
2133				END IF;
2134    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2135		WHEN 	OTHERS THEN
2136				g_delete_gtt_table := NULL;
2137				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2138													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2139																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_purchase_id: ' ||
2140																	 i_purchase_id ,
2141													i_broadcaster_ref => NULL,
2142													i_sequence        => 1,
2143													i_result          => k_rslt_tp_notvalid,
2144													i_message_type    => k_err_msg_type,
2145													i_message_code    => SQLCODE);
2146				o_error_reported := k_true;
2147				IF NOT o_offer_item_ids%ISOPEN THEN
2148					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2149				END IF;
2150        		IF 	NOT o_properties%ISOPEN THEN
2151					o_properties := error_handling_api.getemptyrefcursor();
2152				END IF;
2153    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2154				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2155				IF	c_error_response%ISOPEN THEN
2156					CLOSE c_error_response;
2157				END IF;
2158				IF c_properties%ISOPEN THEN
2159				   CLOSE c_properties;
2160				END IF;
2161	END FindOfferItemByPurchaseModify;
2162	--
2163	-------------------------------------------------------------------------------------------------
2164	-- Procedure : FindOfferItemBySeriesModify
2165	-- Added By Bakulesh @30/03/2012
2166	-- Description : for passed in  purchase id return offer items which are not expired
2167	-------------------------------------------------------------------------------------------------
2168    PROCEDURE FindOfferItemBySeriesModify(
2169								i_series_id				IN	purchase.series_id%TYPE,
2170								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2171								o_offer_item_ids 		OUT SYS_REFCURSOR,
2172								o_properties     		OUT SYS_REFCURSOR,
2173								o_error_reported 		OUT t.logical%TYPE,
2174								o_error_response 		OUT SYS_REFCURSOR
2175										)
2176	IS
2177		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemBySeriesModify';
2178		--local variables
2179		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2180		v_error_reported				t.logical%TYPE;
2181		v_called_procedure				VARCHAR2(60);
2182		-- ref cursors
2183		c_properties					SYS_REFCURSOR;
2184		c_error_response				SYS_REFCURSOR;
2185		c_offer_item_ids				SYS_REFCURSOR;
2186	BEGIN
2187		o_error_reported 	:= k_false;
2188		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2189			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2190			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2191		END IF;
2192		--get program series's offer item ids
2193		on_demand_api.FindOfferItemBySeries(
2194									i_series_id 	 => i_series_id,
2195									i_required_attrs => i_required_attrs,
2196									o_offer_item_ids => c_offer_item_ids,
2197									o_properties 	 => c_properties,
2198									o_error_reported => v_error_reported,
2199									o_error_response => o_error_response
2200											);
2201		--
2202		IF 	v_error_reported = k_false THEN
2203			findUnExpiredOfferItems(
2204								i_offer_item_ids => c_offer_item_ids
2205								   );
2206		ELSE
2207			--
2208			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2209													i_message         => 'Error while processing '||v_called_procedure||'. Called from '||k_procedure_name,
2210													i_broadcaster_ref => NULL,
2211													i_sequence        => 1,
2212													i_result          => k_rslt_tp_notvalid,
2213													i_message_type    => k_err_msg_type,
2214													i_message_code    => SQLCODE);
2215			--error found abort process and send error resonse to exception section
2216			o_error_reported  := v_error_reported;
2217			RAISE e_error;
2218		END IF;
2219		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2220		IF	c_error_response%ISOPEN THEN
2221			CLOSE c_error_response;
2222		END IF;
2223		IF c_properties%ISOPEN THEN
2224		   CLOSE c_properties;
2225		END IF;
2226		--
2227		--check any offer item found or not
2228		SELECT 	time_1
2229		INTO	v_offer_item_id
2230		FROM	glxmem_tmp_decision_source t
2231		WHERE	t.decision_source_id = k_dummy_id
2232			AND	ROWNUM = 1;
2233		--open ref cursor for all un expired offer items
2234		OPEN o_offer_item_ids FOR
2235		SELECT	t.time_1 			 AS offerItemId,
2236				t.time_2	 		 AS offerId,
2237				t.alpha_1	 		 AS offerName,
2238				t.time_3		 	 AS serviceID,
2239				t.alpha_2		 	 AS serviceName,
2240				t.alpha_3		 	 AS offerStatusCode,
2241				t.alpha_4			 AS offerStausDescr,
2242				t.time_6			 AS offerStatusISN,
2243				t.alpha_5	 		 AS offerAvaibilityEndDate,
2244				t.time_4	 		 AS offerAvaibilityEndTime,
2245				t.alpha_6		 	 AS offerExpiryDate,
2246				t.time_5		 	 AS offerExpiryTime
2247		FROM	glxmem_tmp_decision_source t
2248		WHERE	t.decision_source_id = k_dummy_id;
2249		--
2250		g_delete_gtt_table := NULL;
2251		--set empty ref cursors for properties, error as nothing to report back.
2252		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2253    	o_error_response := error_handling_api.GetErrorRefCursor();
2254	EXCEPTION
2255		WHEN	e_error THEN
2256				g_delete_gtt_table := NULL;
2257				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2258				IF	c_error_response%ISOPEN THEN
2259					CLOSE c_error_response;
2260				END IF;
2261				IF c_properties%ISOPEN THEN
2262				   CLOSE c_properties;
2263				END IF;
2264				o_error_reported := k_true;
2265				IF 	NOT o_offer_item_ids%ISOPEN THEN
2266					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2267				END IF;
2268       			IF 	NOT o_properties%ISOPEN THEN
2269					o_properties := error_handling_api.getemptyrefcursor();
2270				END IF;
2271		WHEN 	NO_DATA_FOUND THEN
2272				g_delete_gtt_table := NULL;
2273				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2274													i_message         => k_procedure_name || ': ' ||
2275																	 'No data found for series id = ' || i_series_id,
2276													i_broadcaster_ref => NULL,
2277													i_sequence        => 1,
2278													i_result          => k_rslt_tp_notvalid,
2279													i_message_type    => k_err_msg_type,
2280													i_message_code    => SQLCODE);
2281				o_error_reported := k_true;
2282				IF 	NOT o_offer_item_ids%ISOPEN THEN
2283					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2284				END IF;
2285       			IF 	NOT o_properties%ISOPEN THEN
2286					o_properties := error_handling_api.getemptyrefcursor();
2287				END IF;
2288    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2289		WHEN 	OTHERS THEN
2290				g_delete_gtt_table := NULL;
2291				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2292													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2293																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_series_id: ' ||
2294																	 i_series_id ,
2295													i_broadcaster_ref => NULL,
2296													i_sequence        => 1,
2297													i_result          => k_rslt_tp_notvalid,
2298													i_message_type    => k_err_msg_type,
2299													i_message_code    => SQLCODE);
2300				o_error_reported := k_true;
2301				IF NOT o_offer_item_ids%ISOPEN THEN
2302					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2303				END IF;
2304        		IF 	NOT o_properties%ISOPEN THEN
2305					o_properties := error_handling_api.getemptyrefcursor();
2306				END IF;
2307    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2308				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2309				IF	c_error_response%ISOPEN THEN
2310					CLOSE c_error_response;
2311				END IF;
2312				IF c_properties%ISOPEN THEN
2313				   CLOSE c_properties;
2314				END IF;
2315	END FindOfferItemBySeriesModify;
2316	-------------------------------------------------------------------------------------------------
2317	-- Procedure : FindOfferItemByCast
2318	-- Added By Bakulesh @03/04/2012
2319	-- Description : for passed in  cast member id find all title linked to this cast and then return all offer item ids linked to all titles
2320	-------------------------------------------------------------------------------------------------
2321    PROCEDURE FindOfferItemByCast(
2322								i_cast_id				IN	cast.cast_id%TYPE,
2323								i_object_type			IN  cast.object_type%TYPE,
2324								i_object_id				IN  cast.object_id%TYPE,
2325								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2326								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
2327								o_offer_item_ids 		OUT SYS_REFCURSOR,
2328								o_properties     		OUT SYS_REFCURSOR,
2329								o_error_reported 		OUT t.logical%TYPE,
2330								o_error_response 		OUT SYS_REFCURSOR
2331										)
2332	IS
2333		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByCast';
2334		--local variables
2335		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2336		v_error_reported				t.logical%TYPE;
2337		v_called_procedure				VARCHAR2(60);
2338		-- ref cursors
2339		c_properties					SYS_REFCURSOR;
2340		c_error_response				SYS_REFCURSOR;
2341		c_offer_item_ids				SYS_REFCURSOR;
2342	BEGIN
2343		o_error_reported 	:= k_false;
2344		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2345			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2346			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2347		END IF;
2348		--get titles linked to cast
2349		IF 	i_object_type = k_title_object_type --TI
2350		THEN --get title's offer item ids call helper API to do the job
2351			FindOfferItemByTitleModify(
2352								i_title_id 		 => i_object_id,
2353								i_required_attrs => i_required_attrs,
2354								i_interface_id	 => i_interface_id,
2355								o_offer_item_ids => c_offer_item_ids,
2356								o_properties 	 => c_properties,
2357								o_error_reported => v_error_reported,
2358								o_error_response => c_error_response
2359									);
2360			IF 	v_error_reported = k_true THEN
2361				mergeErrors(
2362						i_error_response => c_error_response
2363							);
2364				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2365													i_message         => 'Error while processing '||v_called_procedure||'. Called from '||k_procedure_name||', cast id='||i_cast_id||', object '||i_object_type||i_object_id,
2366													i_broadcaster_ref => NULL,
2367													i_sequence        => 1,
2368													i_result          => k_rslt_tp_notvalid,
2369													i_message_type    => k_err_msg_type,
2370													i_message_code    => SQLCODE);
2371			END IF;
2372			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2373			IF	c_error_response%ISOPEN THEN
2374				CLOSE c_error_response;
2375			END IF;
2376			IF 	c_properties%ISOPEN THEN
2377		   		CLOSE c_properties;
2378			END IF;
2379			IF	c_offer_item_ids%ISOPEN THEN
2380				CLOSE c_offer_item_ids;
2381			END IF;
2382		END IF;
2383		--
2384		--check any offer item found or not
2385		SELECT 	time_1
2386		INTO	v_offer_item_id
2387		FROM	glxmem_tmp_decision_source t
2388		WHERE	t.decision_source_id = k_dummy_id
2389			AND	ROWNUM = 1;
2390		--open ref cursor for all un expired offer items
2391		OPEN o_offer_item_ids FOR
2392		SELECT	t.time_1 			 AS offerItemId,
2393				t.time_2	 		 AS offerId,
2394				t.alpha_1	 		 AS offerName,
2395				t.time_3		 	 AS serviceID,
2396				t.alpha_2		 	 AS serviceName,
2397				t.alpha_3		 	 AS offerStatusCode,
2398				t.alpha_4			 AS offerStausDescr,
2399				t.time_6			 AS offerStatusISN,
2400				t.alpha_5	 		 AS offerAvaibilityEndDate,
2401				t.time_4	 		 AS offerAvaibilityEndTime,
2402				t.alpha_6		 	 AS offerExpiryDate,
2403				t.time_5		 	 AS offerExpiryTime
2404		FROM	glxmem_tmp_decision_source t
2405		WHERE	t.decision_source_id = k_dummy_id;
2406		--
2407		g_delete_gtt_table := NULL;
2408		--
2409		--set empty ref cursors for properties, error as nothing to report back.
2410		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2411    	o_error_response := error_handling_api.GetErrorRefCursor();
2412	EXCEPTION
2413		WHEN 	NO_DATA_FOUND THEN
2414				g_delete_gtt_table := NULL;
2415				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2416													i_message         => k_procedure_name || ': ' ||
2417																	 'No data found for cast id = ' || i_cast_id||', object '||i_object_type||i_object_id,
2418													i_broadcaster_ref => NULL,
2419													i_sequence        => 1,
2420													i_result          => k_rslt_tp_notvalid,
2421													i_message_type    => k_err_msg_type,
2422													i_message_code    => SQLCODE);
2423				o_error_reported := k_true;
2424				IF 	NOT o_offer_item_ids%ISOPEN THEN
2425					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2426				END IF;
2427       			IF 	NOT o_properties%ISOPEN THEN
2428					o_properties := error_handling_api.getemptyrefcursor();
2429				END IF;
2430    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2431		WHEN 	OTHERS THEN
2432				g_delete_gtt_table := NULL;
2433				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2434													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2435																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_cast_id: ' ||
2436																	 i_cast_id||', object '||i_object_type||i_object_id ,
2437													i_broadcaster_ref => NULL,
2438													i_sequence        => 1,
2439													i_result          => k_rslt_tp_notvalid,
2440													i_message_type    => k_err_msg_type,
2441													i_message_code    => SQLCODE);
2442				o_error_reported := k_true;
2443				IF NOT o_offer_item_ids%ISOPEN THEN
2444					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2445				END IF;
2446        		IF 	NOT o_properties%ISOPEN THEN
2447					o_properties := error_handling_api.getemptyrefcursor();
2448				END IF;
2449    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2450				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2451				IF	c_error_response%ISOPEN THEN
2452					CLOSE c_error_response;
2453				END IF;
2454				IF c_properties%ISOPEN THEN
2455				   CLOSE c_properties;
2456				END IF;
2457	END FindOfferItemByCast;
2458	-------------------------------------------------------------------------------------------------
2459	-- Procedure : FindOfferItemByCastMember
2460	-- Added By Bakulesh @03/04/2012
2461	-- Description : for passed in  cast member id find all title linked to this cast and then return all offer item ids linked to all titles
2462	-------------------------------------------------------------------------------------------------
2463    PROCEDURE FindOfferItemByCastMember(
2464								i_cast_id				IN	cast.cast_id%TYPE,
2465								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2466								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
2467								o_offer_item_ids 		OUT SYS_REFCURSOR,
2468								o_properties     		OUT SYS_REFCURSOR,
2469								o_error_reported 		OUT t.logical%TYPE,
2470								o_error_response 		OUT SYS_REFCURSOR
2471										)
2472	IS
2473		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByCastMember';
2474		--local variables
2475		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2476		v_error_reported				t.logical%TYPE;
2477		-- ref cursors
2478		c_properties					SYS_REFCURSOR;
2479		c_error_response				SYS_REFCURSOR;
2480		c_offer_item_ids				SYS_REFCURSOR;
2481	BEGIN
2482		o_error_reported 	:= k_false;
2483		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2484			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2485			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2486		END IF;
2487
2488		--get titles linked to cast
2489		FOR v_cast_titles IN (SELECT	DISTINCT
2490										c.object_id AS title_id
2491							  FROM		CAST c
2492							  WHERE		c.object_type = k_title_object_type --TI
2493							  	AND		c.cast_id = i_cast_id)
2494		LOOP
2495			--get title's offer item ids
2496			g_delete_gtt_table := k_false; --delete table first time only, then cummulate data
2497			FindOfferItemByTitleModify(
2498								i_title_id 		 => v_cast_titles.title_id,
2499								i_required_attrs => i_required_attrs,
2500								i_interface_id	 => i_interface_id,
2501								o_offer_item_ids => c_offer_item_ids,
2502								o_properties 	 => c_properties,
2503								o_error_reported => v_error_reported,
2504								o_error_response => c_error_response
2505									);
2506			--
2507			IF 	v_error_reported = k_true THEN
2508				--
2509				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2510													i_message         => 'Error while processing FindOfferItemByTitleModify. Called from '||k_procedure_name||', cast id='||i_cast_id,
2511													i_broadcaster_ref => NULL,
2512													i_sequence        => 1,
2513													i_result          => k_rslt_tp_notvalid,
2514													i_message_type    => k_err_msg_type,
2515													i_message_code    => SQLCODE);
2516				mergeErrors(
2517					i_error_response => c_error_response
2518							);
2519			END IF;
2520			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2521			IF	c_error_response%ISOPEN THEN
2522				CLOSE c_error_response;
2523			END IF;
2524			IF 	c_properties%ISOPEN THEN
2525		   		CLOSE c_properties;
2526			END IF;
2527			IF	c_offer_item_ids%ISOPEN THEN
2528				CLOSE c_offer_item_ids;
2529			END IF;
2530		END LOOP;
2531		--
2532		--check any offer item found or not
2533		SELECT 	time_1
2534		INTO	v_offer_item_id
2535		FROM	glxmem_tmp_decision_source t
2536		WHERE	t.decision_source_id = k_dummy_id
2537			AND	ROWNUM = 1;
2538		--open ref cursor for all un expired offer items
2539		OPEN o_offer_item_ids FOR
2540		SELECT	t.time_1 			 AS offerItemId,
2541				t.time_2	 		 AS offerId,
2542				t.alpha_1	 		 AS offerName,
2543				t.time_3		 	 AS serviceID,
2544				t.alpha_2		 	 AS serviceName,
2545				t.alpha_3		 	 AS offerStatusCode,
2546				t.alpha_4			 AS offerStausDescr,
2547				t.time_6			 AS offerStatusISN,
2548				t.alpha_5	 		 AS offerAvaibilityEndDate,
2549				t.time_4	 		 AS offerAvaibilityEndTime,
2550				t.alpha_6		 	 AS offerExpiryDate,
2551				t.time_5		 	 AS offerExpiryTime
2552		FROM	glxmem_tmp_decision_source t
2553		WHERE	t.decision_source_id = k_dummy_id;
2554		--
2555		g_delete_gtt_table := NULL;
2556		--
2557		--set empty ref cursors for properties, error as nothing to report back.
2558		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2559    	o_error_response := error_handling_api.GetErrorRefCursor();
2560	EXCEPTION
2561		WHEN 	NO_DATA_FOUND THEN
2562				g_delete_gtt_table := NULL;
2563				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2564													i_message         => k_procedure_name || ': ' ||
2565																	 'No data found for cast id = ' || i_cast_id,
2566													i_broadcaster_ref => NULL,
2567													i_sequence        => 1,
2568													i_result          => k_rslt_tp_notvalid,
2569													i_message_type    => k_err_msg_type,
2570													i_message_code    => SQLCODE);
2571				o_error_reported := k_true;
2572				IF 	NOT o_offer_item_ids%ISOPEN THEN
2573					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2574				END IF;
2575       			IF 	NOT o_properties%ISOPEN THEN
2576					o_properties := error_handling_api.getemptyrefcursor();
2577				END IF;
2578    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2579		WHEN 	OTHERS THEN
2580				g_delete_gtt_table := NULL;
2581				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2582													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2583																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_cast_id: ' ||
2584																	 i_cast_id ,
2585													i_broadcaster_ref => NULL,
2586													i_sequence        => 1,
2587													i_result          => k_rslt_tp_notvalid,
2588													i_message_type    => k_err_msg_type,
2589													i_message_code    => SQLCODE);
2590				o_error_reported := k_true;
2591				IF NOT o_offer_item_ids%ISOPEN THEN
2592					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2593				END IF;
2594        		IF 	NOT o_properties%ISOPEN THEN
2595					o_properties := error_handling_api.getemptyrefcursor();
2596				END IF;
2597    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2598				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2599				IF	c_error_response%ISOPEN THEN
2600					CLOSE c_error_response;
2601				END IF;
2602				IF c_properties%ISOPEN THEN
2603				   CLOSE c_properties;
2604				END IF;
2605	END FindOfferItemByCastMember;
2606	-------------------------------------------------------------------------------------------------
2607	-- Procedure : FindOfferItemByListingTextSet
2608	-- Added By Bakulesh @05/04/2012
2609	-- Description : for passed in  listing text set id find title linked to it and then return all offer item ids of linked title
2610	-------------------------------------------------------------------------------------------------
2611    PROCEDURE FindOfferItemByListingTextSet(
2612								i_set_id				IN	listing_text_set.set_id%TYPE DEFAULT NULL,
2613								i_object_type			IN	listing_text_set.object_type%TYPE DEFAULT NULL,
2614								i_object_id				IN	listing_text_set.object_id%TYPE DEFAULT NULL,
2615								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2616								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
2617								o_offer_item_ids 		OUT SYS_REFCURSOR,
2618								o_properties     		OUT SYS_REFCURSOR,
2619								o_error_reported 		OUT t.logical%TYPE,
2620								o_error_response 		OUT SYS_REFCURSOR
2621										)
2622	IS
2623		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByListingTextSet';
2624		--local variables
2625		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2626		v_error_reported				t.logical%TYPE;
2627		v_title_id						title.title_id%TYPE;
2628		-- ref cursors
2629		c_properties					SYS_REFCURSOR;
2630		c_error_response				SYS_REFCURSOR;
2631		c_offer_item_ids				SYS_REFCURSOR;
2632	BEGIN
2633		o_error_reported 	:= k_false;
2634		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2635			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2636			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2637		END IF;
2638		--if object type is title then use it to get title_id
2639		IF	i_object_type = k_title_object_type THEN
2640			v_title_id := i_object_id;
2641		--if object type is empty i.e. not equal to title and if set id > 0 then get title id using set id + object_type = TI
2642		ELSIF	NVL(i_set_id, 0) > 0 THEN
2643			SELECT	lts.object_id
2644			INTO	v_title_id
2645			FROM	listing_text_set lts
2646			WHERE	lts.set_id = i_set_id
2647				AND	lts.object_type = k_title_object_type; --for now Title only, but later can be modified to get deal level listings text
2648		END IF;
2649		--get title's offer item ids
2650		FindOfferItemByTitleModify(
2651								i_title_id 		 => v_title_id,
2652								i_required_attrs => i_required_attrs,
2653								i_interface_id	 => i_interface_id,
2654								o_offer_item_ids => c_offer_item_ids,
2655								o_properties 	 => c_properties,
2656								o_error_reported => v_error_reported,
2657								o_error_response => c_error_response
2658									);
2659		--
2660		IF 	v_error_reported = k_true THEN
2661			mergeErrors(
2662					i_error_response => c_error_response
2663						);
2664			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2665												i_message         => 'Error while processing FindOfferItemByTitleModify. Called from '||k_procedure_name||', listings set id='||i_set_id||' title Id='||v_title_id,
2666												i_broadcaster_ref => NULL,
2667												i_sequence        => 1,
2668												i_result          => k_rslt_tp_notvalid,
2669												i_message_type    => k_err_msg_type,
2670												i_message_code    => SQLCODE);
2671			o_error_response := error_handling_api.GetErrorRefCursor();
2672			RAISE e_error;
2673		END IF;
2674		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2675		IF	c_error_response%ISOPEN THEN
2676			CLOSE c_error_response;
2677		END IF;
2678		IF 	c_properties%ISOPEN THEN
2679		  		CLOSE c_properties;
2680		END IF;
2681		IF	c_offer_item_ids%ISOPEN THEN
2682			CLOSE c_offer_item_ids;
2683		END IF;
2684		--
2685		--check any offer item found or not
2686		SELECT 	time_1
2687		INTO	v_offer_item_id
2688		FROM	glxmem_tmp_decision_source t
2689		WHERE	t.decision_source_id = k_dummy_id
2690			AND	ROWNUM = 1;
2691		--open ref cursor for all un expired offer items
2692		OPEN 	o_offer_item_ids FOR
2693		SELECT	t.time_1 			 AS offerItemId,
2694				t.time_2	 		 AS offerId,
2695				t.alpha_1	 		 AS offerName,
2696				t.time_3		 	 AS serviceID,
2697				t.alpha_2		 	 AS serviceName,
2698				t.alpha_3		 	 AS offerStatusCode,
2699				t.alpha_4			 AS offerStausDescr,
2700				t.time_6			 AS offerStatusISN,
2701				t.alpha_5	 		 AS offerAvaibilityEndDate,
2702				t.time_4	 		 AS offerAvaibilityEndTime,
2703				t.alpha_6		 	 AS offerExpiryDate,
2704				t.time_5		 	 AS offerExpiryTime
2705		FROM	glxmem_tmp_decision_source t
2706		WHERE	t.decision_source_id = k_dummy_id;
2707		--
2708		g_delete_gtt_table := NULL;
2709		--
2710		--set empty ref cursors for properties, error as nothing to report back.
2711		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2712    	o_error_response := error_handling_api.GetErrorRefCursor();
2713	EXCEPTION
2714		WHEN	e_error THEN
2715				g_delete_gtt_table := NULL;
2716				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2717				IF	c_error_response%ISOPEN THEN
2718					CLOSE c_error_response;
2719				END IF;
2720				IF c_properties%ISOPEN THEN
2721				   CLOSE c_properties;
2722				END IF;
2723				o_error_reported := k_true;
2724				IF 	NOT o_offer_item_ids%ISOPEN THEN
2725					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2726				END IF;
2727       			IF 	NOT o_properties%ISOPEN THEN
2728					o_properties := error_handling_api.getemptyrefcursor();
2729				END IF;
2730		WHEN 	NO_DATA_FOUND THEN
2731				g_delete_gtt_table := NULL;
2732				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2733													i_message         => k_procedure_name || ': ' ||
2734																	 'No data found for listings set id = ' || i_set_id,
2735													i_broadcaster_ref => NULL,
2736													i_sequence        => 1,
2737													i_result          => k_rslt_tp_notvalid,
2738													i_message_type    => k_err_msg_type,
2739													i_message_code    => SQLCODE);
2740				o_error_reported := k_true;
2741				IF 	NOT o_offer_item_ids%ISOPEN THEN
2742					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2743				END IF;
2744       			IF 	NOT o_properties%ISOPEN THEN
2745					o_properties := error_handling_api.getemptyrefcursor();
2746				END IF;
2747    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2748		WHEN 	OTHERS THEN
2749				g_delete_gtt_table := NULL;
2750				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2751													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2752																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_set_id: ' ||
2753																	 i_set_id ,
2754													i_broadcaster_ref => NULL,
2755													i_sequence        => 1,
2756													i_result          => k_rslt_tp_notvalid,
2757													i_message_type    => k_err_msg_type,
2758													i_message_code    => SQLCODE);
2759				o_error_reported := k_true;
2760				IF NOT o_offer_item_ids%ISOPEN THEN
2761					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2762				END IF;
2763        		IF 	NOT o_properties%ISOPEN THEN
2764					o_properties := error_handling_api.getemptyrefcursor();
2765				END IF;
2766    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2767				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2768				IF	c_error_response%ISOPEN THEN
2769					CLOSE c_error_response;
2770				END IF;
2771				IF c_properties%ISOPEN THEN
2772				   CLOSE c_properties;
2773				END IF;
2774	END FindOfferItemByListingTextSet;
2775	-------------------------------------------------------------------------------------------------
2776	-- Procedure : FindOfferItemByObjectMetadata
2777	-- Added By Bakulesh @06/04/2012
2778	-- Description : for passed in  object metadata id find title linked to it and then return all offer item ids of linked title
2779	-------------------------------------------------------------------------------------------------
2780    PROCEDURE FindOfferItemByObjectMetadata(
2781								i_metadata_id			IN	object_metadata.object_metadata_id%TYPE,
2782								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2783								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
2784								o_offer_item_ids 		OUT SYS_REFCURSOR,
2785								o_properties     		OUT SYS_REFCURSOR,
2786								o_error_reported 		OUT t.logical%TYPE,
2787								o_error_response 		OUT SYS_REFCURSOR
2788										)
2789	IS
2790		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByObjectMetadata';
2791		--local variables
2792		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2793		v_error_reported				t.logical%TYPE;
2794		v_title_id						title.title_id%TYPE;
2795		-- ref cursors
2796		c_properties					SYS_REFCURSOR;
2797		c_error_response				SYS_REFCURSOR;
2798		c_offer_item_ids				SYS_REFCURSOR;
2799	BEGIN
2800		o_error_reported 	:= k_false;
2801		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2802			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2803			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2804		END IF;
2805		SELECT	om.object_id
2806		INTO	v_title_id
2807		FROM	object_metadata om
2808		WHERE	om.object_metadata_id = i_metadata_id
2809			AND	om.object_type = k_title_object_type; --for now Title only, but later can be modified to get deal or any other metadata attr
2810		--get title's offer item ids
2811		FindOfferItemByTitleModify(
2812								i_title_id 		 => v_title_id,
2813								i_required_attrs => i_required_attrs,
2814								i_interface_id	 => i_interface_id,
2815								o_offer_item_ids => c_offer_item_ids,
2816								o_properties 	 => c_properties,
2817								o_error_reported => v_error_reported,
2818								o_error_response => c_error_response
2819									);
2820		--
2821		IF 	v_error_reported = k_true THEN
2822			mergeErrors(
2823					i_error_response => c_error_response
2824							);
2825			--
2826			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2827												i_message         => 'Error while processing FindOfferItemByTitleModify. Called from '||k_procedure_name||', listings set id='||i_metadata_id||' title Id='||v_title_id,
2828												i_broadcaster_ref => NULL,
2829												i_sequence        => 1,
2830												i_result          => k_rslt_tp_notvalid,
2831												i_message_type    => k_err_msg_type,
2832												i_message_code    => SQLCODE);
2833			o_error_response := error_handling_api.GetErrorRefCursor();
2834			RAISE e_error;
2835		END IF;
2836		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2837		IF	c_error_response%ISOPEN THEN
2838			CLOSE c_error_response;
2839		END IF;
2840		IF 	c_properties%ISOPEN THEN
2841			CLOSE c_properties;
2842		END IF;
2843		IF	c_offer_item_ids%ISOPEN THEN
2844			CLOSE c_offer_item_ids;
2845		END IF;
2846		--
2847		--check any offer item found or not
2848		SELECT 	time_1
2849		INTO	v_offer_item_id
2850		FROM	glxmem_tmp_decision_source t
2851		WHERE	t.decision_source_id = k_dummy_id
2852			AND	ROWNUM = 1;
2853		--open ref cursor for all un expired offer items
2854		OPEN 	o_offer_item_ids FOR
2855		SELECT	t.time_1 			 AS offerItemId,
2856				t.time_2	 		 AS offerId,
2857				t.alpha_1	 		 AS offerName,
2858				t.time_3		 	 AS serviceID,
2859				t.alpha_2		 	 AS serviceName,
2860				t.alpha_3		 	 AS offerStatusCode,
2861				t.alpha_4			 AS offerStausDescr,
2862				t.time_6			 AS offerStatusISN,
2863				t.alpha_5	 		 AS offerAvaibilityEndDate,
2864				t.time_4	 		 AS offerAvaibilityEndTime,
2865				t.alpha_6		 	 AS offerExpiryDate,
2866				t.time_5		 	 AS offerExpiryTime
2867		FROM	glxmem_tmp_decision_source t
2868		WHERE	t.decision_source_id = k_dummy_id;
2869		--
2870		g_delete_gtt_table := NULL;
2871		--
2872		--set empty ref cursors for properties, error as nothing to report back.
2873		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
2874    	o_error_response := error_handling_api.GetErrorRefCursor();
2875	EXCEPTION
2876		WHEN	e_error THEN
2877				g_delete_gtt_table := NULL;
2878				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2879				IF	c_error_response%ISOPEN THEN
2880					CLOSE c_error_response;
2881				END IF;
2882				IF c_properties%ISOPEN THEN
2883				   CLOSE c_properties;
2884				END IF;
2885				o_error_reported := k_true;
2886				IF 	NOT o_offer_item_ids%ISOPEN THEN
2887					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2888				END IF;
2889       			IF 	NOT o_properties%ISOPEN THEN
2890					o_properties := error_handling_api.getemptyrefcursor();
2891				END IF;
2892		WHEN 	NO_DATA_FOUND THEN
2893				g_delete_gtt_table := NULL;
2894				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2895													i_message         => k_procedure_name || ': ' ||
2896																	 'No data found for object metadta id = ' || i_metadata_id,
2897													i_broadcaster_ref => NULL,
2898													i_sequence        => 1,
2899													i_result          => k_rslt_tp_notvalid,
2900													i_message_type    => k_err_msg_type,
2901													i_message_code    => SQLCODE);
2902				o_error_reported := k_true;
2903				IF 	NOT o_offer_item_ids%ISOPEN THEN
2904					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2905				END IF;
2906       			IF 	NOT o_properties%ISOPEN THEN
2907					o_properties := error_handling_api.getemptyrefcursor();
2908				END IF;
2909    	    	o_error_response := error_handling_api.GetErrorRefCursor();
2910		WHEN 	OTHERS THEN
2911				g_delete_gtt_table := NULL;
2912				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
2913													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
2914																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_metadata_id: ' ||
2915																	 i_metadata_id ,
2916													i_broadcaster_ref => NULL,
2917													i_sequence        => 1,
2918													i_result          => k_rslt_tp_notvalid,
2919													i_message_type    => k_err_msg_type,
2920													i_message_code    => SQLCODE);
2921				o_error_reported := k_true;
2922				IF NOT o_offer_item_ids%ISOPEN THEN
2923					o_offer_item_ids := error_handling_api.getemptyrefcursor();
2924				END IF;
2925        		IF 	NOT o_properties%ISOPEN THEN
2926					o_properties := error_handling_api.getemptyrefcursor();
2927				END IF;
2928    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
2929				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2930				IF	c_error_response%ISOPEN THEN
2931					CLOSE c_error_response;
2932				END IF;
2933				IF c_properties%ISOPEN THEN
2934				   CLOSE c_properties;
2935				END IF;
2936	END FindOfferItemByObjectMetadata;
2937	-------------------------------------------------------------------------------------------------
2938	-- Procedure : FindOfferItemByMediaItem
2939	-- Added By Bakulesh @06/04/2012
2940	-- Description : for passed in  media id return all offer item ids of linked media
2941	-------------------------------------------------------------------------------------------------
2942    PROCEDURE FindOfferItemByMediaItem(
2943								i_media_item_id			IN	media_item.media_item_id%TYPE,
2944								i_media_hierarchy		IN  VARCHAR2 DEFAULT NULL,
2945								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
2946								o_offer_item_ids 		OUT SYS_REFCURSOR,
2947								o_properties     		OUT SYS_REFCURSOR,
2948								o_error_reported 		OUT t.logical%TYPE,
2949								o_error_response 		OUT SYS_REFCURSOR
2950										)
2951	IS
2952		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByMediaItem';
2953		--local variables
2954		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
2955		v_error_reported				t.logical%TYPE;
2956		-- ref cursors
2957		c_properties					SYS_REFCURSOR;
2958		c_error_response				SYS_REFCURSOR;
2959		c_offer_item_ids				SYS_REFCURSOR;
2960	BEGIN
2961		o_error_reported 	:= k_false;
2962		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
2963			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
2964			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
2965		END IF;
2966		--get media's offer item ids
2967		on_demand_api.FindOfferItemByMediaItem(
2968								i_media_item_id =>  i_media_item_id,
2969								i_media_hierarchy => i_media_hierarchy,
2970								i_required_attrs => i_required_attrs,
2971								o_offer_item_ids => c_offer_item_ids,
2972								o_properties 	 => c_properties,
2973								o_error_reported => v_error_reported,
2974								o_error_response => c_error_response
2975									);
2976		--
2977		IF 	v_error_reported = k_false THEN
2978			findUnExpiredOfferItems(
2979								i_offer_item_ids => c_offer_item_ids
2980								   );
2981			--
2982		ELSE
2983			mergeErrors(
2984					i_error_response => c_error_response
2985						);
2986			--
2987			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
2988												i_message         => 'Error while processing FindOfferItemByMediaItem. Called from '||k_procedure_name||', listings set id='||i_media_item_id,
2989												i_broadcaster_ref => NULL,
2990												i_sequence        => 1,
2991												i_result          => k_rslt_tp_notvalid,
2992												i_message_type    => k_err_msg_type,
2993												i_message_code    => SQLCODE);
2994			o_error_response := error_handling_api.GetErrorRefCursor();
2995			RAISE e_error;
2996		END IF;
2997		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
2998		IF	c_error_response%ISOPEN THEN
2999			CLOSE c_error_response;
3000		END IF;
3001		IF 	c_properties%ISOPEN THEN
3002		  		CLOSE c_properties;
3003		END IF;
3004		IF	c_offer_item_ids%ISOPEN THEN
3005			CLOSE c_offer_item_ids;
3006		END IF;
3007		--
3008		--check any offer item found or not
3009		SELECT 	time_1
3010		INTO	v_offer_item_id
3011		FROM	glxmem_tmp_decision_source t
3012		WHERE	t.decision_source_id = k_dummy_id
3013			AND	ROWNUM = 1;
3014		--open ref cursor for all un expired offer items
3015		OPEN 	o_offer_item_ids FOR
3016		SELECT	t.time_1 			 AS offerItemId,
3017				t.time_2	 		 AS offerId,
3018				t.alpha_1	 		 AS offerName,
3019				t.time_3		 	 AS serviceID,
3020				t.alpha_2		 	 AS serviceName,
3021				t.alpha_3		 	 AS offerStatusCode,
3022				t.alpha_4			 AS offerStausDescr,
3023				t.time_6			 AS offerStatusISN,
3024				t.alpha_5	 		 AS offerAvaibilityEndDate,
3025				t.time_4	 		 AS offerAvaibilityEndTime,
3026				t.alpha_6		 	 AS offerExpiryDate,
3027				t.time_5		 	 AS offerExpiryTime
3028		FROM	glxmem_tmp_decision_source t
3029		WHERE	t.decision_source_id = k_dummy_id;
3030		--
3031		g_delete_gtt_table := NULL;
3032		--
3033		--set empty ref cursors for properties, error as nothing to report back.
3034		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3035    	o_error_response := error_handling_api.GetErrorRefCursor();
3036	EXCEPTION
3037		WHEN	e_error THEN
3038				g_delete_gtt_table := NULL;
3039				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3040				IF	c_error_response%ISOPEN THEN
3041					CLOSE c_error_response;
3042				END IF;
3043				IF c_properties%ISOPEN THEN
3044				   CLOSE c_properties;
3045				END IF;
3046				o_error_reported := k_true;
3047				IF 	NOT o_offer_item_ids%ISOPEN THEN
3048					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3049				END IF;
3050       			IF 	NOT o_properties%ISOPEN THEN
3051					o_properties := error_handling_api.getemptyrefcursor();
3052				END IF;
3053		WHEN 	NO_DATA_FOUND THEN
3054				g_delete_gtt_table := NULL;
3055				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3056													i_message         => k_procedure_name || ': ' ||
3057																	 'No data found for media item id = ' || i_media_item_id,
3058													i_broadcaster_ref => NULL,
3059													i_sequence        => 1,
3060													i_result          => k_rslt_tp_notvalid,
3061													i_message_type    => k_err_msg_type,
3062													i_message_code    => SQLCODE);
3063				o_error_reported := k_true;
3064				IF 	NOT o_offer_item_ids%ISOPEN THEN
3065					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3066				END IF;
3067       			IF 	NOT o_properties%ISOPEN THEN
3068					o_properties := error_handling_api.getemptyrefcursor();
3069				END IF;
3070    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3071		WHEN 	OTHERS THEN
3072				g_delete_gtt_table := NULL;
3073				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3074													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3075																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_media_item_id: ' ||
3076																	 i_media_item_id ,
3077													i_broadcaster_ref => NULL,
3078													i_sequence        => 1,
3079													i_result          => k_rslt_tp_notvalid,
3080													i_message_type    => k_err_msg_type,
3081													i_message_code    => SQLCODE);
3082				o_error_reported := k_true;
3083				IF NOT o_offer_item_ids%ISOPEN THEN
3084					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3085				END IF;
3086        		IF 	NOT o_properties%ISOPEN THEN
3087					o_properties := error_handling_api.getemptyrefcursor();
3088				END IF;
3089    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3090				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3091				IF	c_error_response%ISOPEN THEN
3092					CLOSE c_error_response;
3093				END IF;
3094				IF c_properties%ISOPEN THEN
3095				   CLOSE c_properties;
3096				END IF;
3097	END FindOfferItemByMediaItem;
3098	-------------------------------------------------------------------------------------------------
3099	-- Procedure : FindOfferItemByLicensedRight
3100	-- Added By Bakulesh @10/04/2012
3101	-- Description : for passed in  right id id find purchase id and return all offer item ids of linked to it
3102	-------------------------------------------------------------------------------------------------
3103    PROCEDURE FindOfferItemByLicensedRight(
3104								i_licensed_right_id		IN	licensed_right.licensed_right_id%TYPE DEFAULT NULL,
3105								i_object_type			IN	licensed_right.object_type%TYPE DEFAULT NULL,
3106								i_object_id				IN	licensed_right.object_id%TYPE DEFAULT NULL,
3107								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
3108								o_offer_item_ids 		OUT SYS_REFCURSOR,
3109								o_properties     		OUT SYS_REFCURSOR,
3110								o_error_reported 		OUT t.logical%TYPE,
3111								o_error_response 		OUT SYS_REFCURSOR
3112										)
3113	IS
3114		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByLicensedRight';
3115		--local variables
3116		v_purchase_id					purchase.purchase_id%TYPE;
3117		v_error_reported				t.logical%TYPE;
3118		-- ref cursors
3119		c_properties					SYS_REFCURSOR;
3120		c_error_response				SYS_REFCURSOR;
3121		c_offer_item_ids				SYS_REFCURSOR;
3122	BEGIN
3123		o_error_reported 	:= k_false;
3124		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
3125			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
3126			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
3127		END IF;
3128		--get purchase id if object type is purchase
3129		IF	i_object_type = k_program_object_type THEN
3130			v_purchase_id := i_object_id;
3131		--get purchase id if object type is not purchase and pk is supplied
3132		ELSIF NVL(i_licensed_right_id, 0) > 0 THEN
3133			SELECT	lr.object_id
3134			INTO	v_purchase_id
3135			FROM	licensed_right lr
3136			WHERE	lr.licensed_right_id = i_licensed_right_id
3137				AND	lr.object_type = k_program_object_type;--COMOBJ____PR
3138		END IF;
3139		--get media's offer item ids
3140		on_demand_api.FindOfferItemByPurchase(
3141								i_purchase_id 	 => v_purchase_id,
3142								i_required_attrs => i_required_attrs,
3143								o_offer_item_ids => c_offer_item_ids,
3144								o_properties 	 => c_properties,
3145								o_error_reported => v_error_reported,
3146								o_error_response => c_error_response
3147											);
3148		--
3149		IF 	v_error_reported = k_false THEN
3150			findUnExpiredOfferItems(
3151								i_offer_item_ids => c_offer_item_ids
3152								   );
3153			--
3154		ELSE
3155			mergeErrors(
3156					i_error_response => c_error_response
3157						);
3158			--
3159			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3160												i_message         => 'Error while processing FindOfferItemByPurchase. Called from '||k_procedure_name||', right id='||i_licensed_right_id||', purchase id'||v_purchase_id,
3161												i_broadcaster_ref => NULL,
3162												i_sequence        => 1,
3163												i_result          => k_rslt_tp_notvalid,
3164												i_message_type    => k_err_msg_type,
3165												i_message_code    => SQLCODE);
3166			o_error_response := error_handling_api.GetErrorRefCursor();
3167			RAISE e_error;
3168		END IF;
3169		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3170		IF	c_error_response%ISOPEN THEN
3171			CLOSE c_error_response;
3172		END IF;
3173		IF 	c_properties%ISOPEN THEN
3174		  	CLOSE c_properties;
3175		END IF;
3176		IF	c_offer_item_ids%ISOPEN THEN
3177			CLOSE c_offer_item_ids;
3178		END IF;
3179		--
3180		--check any offer item found or not
3181		SELECT 	time_1
3182		INTO	v_purchase_id
3183		FROM	glxmem_tmp_decision_source t
3184		WHERE	t.decision_source_id = k_dummy_id
3185			AND	ROWNUM = 1;
3186		--open ref cursor for all un expired offer items
3187		OPEN 	o_offer_item_ids FOR
3188		SELECT	t.time_1 			 AS offerItemId,
3189				t.time_2	 		 AS offerId,
3190				t.alpha_1	 		 AS offerName,
3191				t.time_3		 	 AS serviceID,
3192				t.alpha_2		 	 AS serviceName,
3193				t.alpha_3		 	 AS offerStatusCode,
3194				t.alpha_4			 AS offerStausDescr,
3195				t.time_6			 AS offerStatusISN,
3196				t.alpha_5	 		 AS offerAvaibilityEndDate,
3197				t.time_4	 		 AS offerAvaibilityEndTime,
3198				t.alpha_6		 	 AS offerExpiryDate,
3199				t.time_5		 	 AS offerExpiryTime
3200		FROM	glxmem_tmp_decision_source t
3201		WHERE	t.decision_source_id = k_dummy_id;
3202		--
3203		g_delete_gtt_table := NULL;
3204		--
3205		--set empty ref cursors for properties, error as nothing to report back.
3206		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3207    	o_error_response := error_handling_api.GetErrorRefCursor();
3208	EXCEPTION
3209		WHEN	e_error THEN
3210				g_delete_gtt_table := NULL;
3211				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3212				IF	c_error_response%ISOPEN THEN
3213					CLOSE c_error_response;
3214				END IF;
3215				IF c_properties%ISOPEN THEN
3216				   CLOSE c_properties;
3217				END IF;
3218				o_error_reported := k_true;
3219				IF 	NOT o_offer_item_ids%ISOPEN THEN
3220					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3221				END IF;
3222       			IF 	NOT o_properties%ISOPEN THEN
3223					o_properties := error_handling_api.getemptyrefcursor();
3224				END IF;
3225		WHEN 	NO_DATA_FOUND THEN
3226				g_delete_gtt_table := NULL;
3227				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3228													i_message         => k_procedure_name || ': ' ||
3229																	 'No data found for license right id = ' || i_licensed_right_id||', purchase id'||v_purchase_id,
3230													i_broadcaster_ref => NULL,
3231													i_sequence        => 1,
3232													i_result          => k_rslt_tp_notvalid,
3233													i_message_type    => k_err_msg_type,
3234													i_message_code    => SQLCODE);
3235				o_error_reported := k_true;
3236				IF 	NOT o_offer_item_ids%ISOPEN THEN
3237					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3238				END IF;
3239       			IF 	NOT o_properties%ISOPEN THEN
3240					o_properties := error_handling_api.getemptyrefcursor();
3241				END IF;
3242    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3243		WHEN 	OTHERS THEN
3244				g_delete_gtt_table := NULL;
3245				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3246													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3247																	 'SQLERRM: ' || SQLERRM || '; ' || 'i_license_right_id: ' ||
3248																	 i_licensed_right_id||', purchase id'||v_purchase_id ,
3249													i_broadcaster_ref => NULL,
3250													i_sequence        => 1,
3251													i_result          => k_rslt_tp_notvalid,
3252													i_message_type    => k_err_msg_type,
3253													i_message_code    => SQLCODE);
3254				o_error_reported := k_true;
3255				IF NOT o_offer_item_ids%ISOPEN THEN
3256					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3257				END IF;
3258        		IF 	NOT o_properties%ISOPEN THEN
3259					o_properties := error_handling_api.getemptyrefcursor();
3260				END IF;
3261    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3262				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3263				IF	c_error_response%ISOPEN THEN
3264					CLOSE c_error_response;
3265				END IF;
3266				IF c_properties%ISOPEN THEN
3267				   CLOSE c_properties;
3268				END IF;
3269	END FindOfferItemByLicensedRight;
3270	-------------------------------------------------------------------------------------------------
3271	-- Procedure : FindOfferItemByCountryOfOrigin
3272	-- Added By Bakulesh @07/05/2012
3273	-- Description : for passed in title id return all offer item ids linked to title id, ignore other object type
3274	-------------------------------------------------------------------------------------------------
3275    PROCEDURE FindOfferItemByCountryOfOrigin(
3276								i_object_type			IN  origin_country.object_type%TYPE,
3277								i_object_id				IN  origin_country.object_id%TYPE,
3278								i_required_attrs		IN	VARCHAR2 DEFAULT NULL,
3279								i_interface_id			IN	table_line.table_line_isn%TYPE DEFAULT NULL,
3280								o_offer_item_ids 		OUT SYS_REFCURSOR,
3281								o_properties     		OUT SYS_REFCURSOR,
3282								o_error_reported 		OUT t.logical%TYPE,
3283								o_error_response 		OUT SYS_REFCURSOR
3284										)
3285	IS
3286		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByCountryOfOrigin';
3287		--local variables
3288		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
3289		v_error_reported				t.logical%TYPE;
3290		-- ref cursors
3291		c_properties					SYS_REFCURSOR;
3292		c_error_response				SYS_REFCURSOR;
3293		c_offer_item_ids				SYS_REFCURSOR;
3294	BEGIN
3295		o_error_reported 	:= k_false;
3296		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
3297			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
3298			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
3299		END IF;
3300		--get titles linked to origin of country table
3301		IF 	i_object_type = k_title_object_type --TI
3302		THEN --get title's offer item ids call helper API to do the job
3303			FindOfferItemByTitleModify(
3304								i_title_id 		 => i_object_id,
3305								i_required_attrs => i_required_attrs,
3306								i_interface_id 	 => i_interface_id,
3307								o_offer_item_ids => c_offer_item_ids,
3308								o_properties 	 => c_properties,
3309								o_error_reported => v_error_reported,
3310								o_error_response => c_error_response
3311									);
3312			IF 	v_error_reported = k_true THEN
3313				mergeErrors(
3314						i_error_response => c_error_response
3315							);
3316				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3317													i_message         => 'Error while processing FindOfferItemByTitleModify'||'. Called from '||k_procedure_name||', title id='||i_object_id,
3318													i_broadcaster_ref => NULL,
3319													i_sequence        => 1,
3320													i_result          => k_rslt_tp_notvalid,
3321													i_message_type    => k_err_msg_type,
3322													i_message_code    => SQLCODE);
3323			END IF;
3324			--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3325			IF	c_error_response%ISOPEN THEN
3326				CLOSE c_error_response;
3327			END IF;
3328			IF 	c_properties%ISOPEN THEN
3329		   		CLOSE c_properties;
3330			END IF;
3331			IF	c_offer_item_ids%ISOPEN THEN
3332				CLOSE c_offer_item_ids;
3333			END IF;
3334		END IF;
3335		--
3336		--check any offer item found or not
3337		SELECT 	time_1
3338		INTO	v_offer_item_id
3339		FROM	glxmem_tmp_decision_source t
3340		WHERE	t.decision_source_id = k_dummy_id
3341			AND	ROWNUM = 1;
3342		--open ref cursor for all un expired offer items
3343		OPEN o_offer_item_ids FOR
3344		SELECT	t.time_1 			 AS offerItemId,
3345				t.time_2	 		 AS offerId,
3346				t.alpha_1	 		 AS offerName,
3347				t.time_3		 	 AS serviceID,
3348				t.alpha_2		 	 AS serviceName,
3349				t.alpha_3		 	 AS offerStatusCode,
3350				t.alpha_4			 AS offerStausDescr,
3351				t.time_6			 AS offerStatusISN,
3352				t.alpha_5	 		 AS offerAvaibilityEndDate,
3353				t.time_4	 		 AS offerAvaibilityEndTime,
3354				t.alpha_6		 	 AS offerExpiryDate,
3355				t.time_5		 	 AS offerExpiryTime
3356		FROM	glxmem_tmp_decision_source t
3357		WHERE	t.decision_source_id = k_dummy_id;
3358		--
3359		g_delete_gtt_table := NULL;
3360		--
3361		--set empty ref cursors for properties, error as nothing to report back.
3362		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3363    	o_error_response := error_handling_api.GetErrorRefCursor();
3364	EXCEPTION
3365		WHEN 	NO_DATA_FOUND THEN
3366				g_delete_gtt_table := NULL;
3367				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3368													i_message         => k_procedure_name || ': ' ||
3369																	 'No data found for object  = ' ||i_object_type||i_object_id,
3370													i_broadcaster_ref => NULL,
3371													i_sequence        => 1,
3372													i_result          => k_rslt_tp_notvalid,
3373													i_message_type    => k_err_msg_type,
3374													i_message_code    => SQLCODE);
3375				o_error_reported := k_true;
3376				IF 	NOT o_offer_item_ids%ISOPEN THEN
3377					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3378				END IF;
3379       			IF 	NOT o_properties%ISOPEN THEN
3380					o_properties := error_handling_api.getemptyrefcursor();
3381				END IF;
3382    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3383		WHEN 	OTHERS THEN
3384				g_delete_gtt_table := NULL;
3385				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3386													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3387																	 'SQLERRM: ' || SQLERRM || '; , object = '||i_object_type||i_object_id ,
3388													i_broadcaster_ref => NULL,
3389													i_sequence        => 1,
3390													i_result          => k_rslt_tp_notvalid,
3391													i_message_type    => k_err_msg_type,
3392													i_message_code    => SQLCODE);
3393				o_error_reported := k_true;
3394				IF NOT o_offer_item_ids%ISOPEN THEN
3395					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3396				END IF;
3397        		IF 	NOT o_properties%ISOPEN THEN
3398					o_properties := error_handling_api.getemptyrefcursor();
3399				END IF;
3400    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3401				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3402				IF	c_error_response%ISOPEN THEN
3403					CLOSE c_error_response;
3404				END IF;
3405				IF c_properties%ISOPEN THEN
3406				   CLOSE c_properties;
3407				END IF;
3408	END FindOfferItemByCountryOfOrigin;
3409	-------------------------------------------------------------------------------------------------
3410	-- Procedure : FindOfferItemByUnMatchMedia
3411	-- Added By Bakulesh @08/05/2012
3412	-- Description : for passed in  media id or vc id find return all offer item ids of linked to it
3413	-------------------------------------------------------------------------------------------------
3414    PROCEDURE FindOfferItemByUnMatchMedia(
3415								i_media_item_id			IN	media_item.media_item_id%TYPE DEFAULT NULL,
3416								i_version_copy_id		IN  media_match.version_copy_id%TYPE DEFAULT NULL,
3417								i_required_attrs		IN	VARCHAR2  DEFAULT NULL,
3418								o_offer_item_ids	    OUT SYS_REFCURSOR,
3419								o_properties     		OUT SYS_REFCURSOR,
3420								o_error_reported 		OUT t.logical%TYPE,
3421								o_error_response 		OUT SYS_REFCURSOR
3422											)
3423	IS
3424		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByUnMatchMedia';
3425		--local variables
3426		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
3427		v_error_reported				t.logical%TYPE;
3428		-- ref cursors
3429		c_properties					SYS_REFCURSOR;
3430		c_error_response				SYS_REFCURSOR;
3431		c_offer_item_ids				SYS_REFCURSOR;
3432	BEGIN
3433		o_error_reported 	:= k_false;
3434		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
3435			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
3436			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
3437		END IF;
3438		--get vc's offer item ids
3439		on_demand_api.FindOfferItemByVersionCopy(
3440								i_version_copy_id 	=> i_version_copy_id,
3441								i_required_attrs 	=> i_required_attrs,
3442								o_offer_item_ids 	=> c_offer_item_ids,
3443								o_properties 	 	=> c_properties,
3444								o_error_reported 	=> v_error_reported,
3445								o_error_response 	=> c_error_response
3446									);
3447		--
3448		IF 	v_error_reported = k_false THEN
3449			findUnExpiredOfferItems(
3450								i_offer_item_ids => c_offer_item_ids
3451								   );
3452			--
3453		ELSE
3454			mergeErrors(
3455					i_error_response => c_error_response
3456						);
3457			--
3458			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3459												i_message         => 'Error while processing FindOfferItemByVersionCopy. Called from '||k_procedure_name||', version copy id='||i_version_copy_id||', media item id='||i_media_item_id,
3460												i_broadcaster_ref => NULL,
3461												i_sequence        => 1,
3462												i_result          => k_rslt_tp_notvalid,
3463												i_message_type    => k_err_msg_type,
3464												i_message_code    => SQLCODE);
3465			o_error_response := error_handling_api.GetErrorRefCursor();
3466			RAISE e_error;
3467		END IF;
3468		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3469		IF	c_error_response%ISOPEN THEN
3470			CLOSE c_error_response;
3471		END IF;
3472		IF 	c_properties%ISOPEN THEN
3473		  		CLOSE c_properties;
3474		END IF;
3475		IF	c_offer_item_ids%ISOPEN THEN
3476			CLOSE c_offer_item_ids;
3477		END IF;
3478		--
3479		--check any offer item found or not
3480		SELECT 	time_1
3481		INTO	v_offer_item_id
3482		FROM	glxmem_tmp_decision_source t
3483		WHERE	t.decision_source_id = k_dummy_id
3484			AND	ROWNUM = 1;
3485		--open ref cursor for all un expired offer items
3486		OPEN 	o_offer_item_ids FOR
3487		SELECT	t.time_1 			 AS offerItemId,
3488				t.time_2	 		 AS offerId,
3489				t.alpha_1	 		 AS offerName,
3490				t.time_3		 	 AS serviceID,
3491				t.alpha_2		 	 AS serviceName,
3492				t.alpha_3		 	 AS offerStatusCode,
3493				t.alpha_4			 AS offerStausDescr,
3494				t.time_6			 AS offerStatusISN,
3495				t.alpha_5	 		 AS offerAvaibilityEndDate,
3496				t.time_4	 		 AS offerAvaibilityEndTime,
3497				t.alpha_6		 	 AS offerExpiryDate,
3498				t.time_5		 	 AS offerExpiryTime
3499		FROM	glxmem_tmp_decision_source t
3500		WHERE	t.decision_source_id = k_dummy_id;
3501		--
3502		g_delete_gtt_table := NULL;
3503		--
3504		--set empty ref cursors for properties, error as nothing to report back.
3505		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3506    	o_error_response := error_handling_api.GetErrorRefCursor();
3507	EXCEPTION
3508		WHEN	e_error THEN
3509				g_delete_gtt_table := NULL;
3510				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3511				IF	c_error_response%ISOPEN THEN
3512					CLOSE c_error_response;
3513				END IF;
3514				IF c_properties%ISOPEN THEN
3515				   CLOSE c_properties;
3516				END IF;
3517				o_error_reported := k_true;
3518				IF 	NOT o_offer_item_ids%ISOPEN THEN
3519					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3520				END IF;
3521       			IF 	NOT o_properties%ISOPEN THEN
3522					o_properties := error_handling_api.getemptyrefcursor();
3523				END IF;
3524		WHEN 	NO_DATA_FOUND THEN
3525				g_delete_gtt_table := NULL;
3526				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3527													i_message         => k_procedure_name || ': ' ||
3528																	 'No data found for version copy id='||i_version_copy_id||', media item id='||i_media_item_id,
3529													i_broadcaster_ref => NULL,
3530													i_sequence        => 1,
3531													i_result          => k_rslt_tp_notvalid,
3532													i_message_type    => k_err_msg_type,
3533													i_message_code    => SQLCODE);
3534				o_error_reported := k_true;
3535				IF 	NOT o_offer_item_ids%ISOPEN THEN
3536					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3537				END IF;
3538       			IF 	NOT o_properties%ISOPEN THEN
3539					o_properties := error_handling_api.getemptyrefcursor();
3540				END IF;
3541    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3542		WHEN 	OTHERS THEN
3543				g_delete_gtt_table := NULL;
3544				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3545													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3546																	 'SQLERRM: ' || SQLERRM || '; version copy id='||i_version_copy_id||', media item id='||i_media_item_id ,
3547													i_broadcaster_ref => NULL,
3548													i_sequence        => 1,
3549													i_result          => k_rslt_tp_notvalid,
3550													i_message_type    => k_err_msg_type,
3551													i_message_code    => SQLCODE);
3552				o_error_reported := k_true;
3553				IF NOT o_offer_item_ids%ISOPEN THEN
3554					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3555				END IF;
3556        		IF 	NOT o_properties%ISOPEN THEN
3557					o_properties := error_handling_api.getemptyrefcursor();
3558				END IF;
3559    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3560				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3561				IF	c_error_response%ISOPEN THEN
3562					CLOSE c_error_response;
3563				END IF;
3564				IF c_properties%ISOPEN THEN
3565				   CLOSE c_properties;
3566				END IF;
3567	END FindOfferItemByUnMatchMedia;
3568
3569	-------------------------------------------------------------------------------------------------
3570	-- Procedure : FindOfferItemByShowModify
3571	-- Added By Tamir @08/01/2013
3572	-- Description : for passed in  show id find return all offer item ids of linked to it
3573	-------------------------------------------------------------------------------------------------
3574    PROCEDURE FindOfferItemByShowModify(
3575								i_show_id				IN  show.show_id%TYPE,
3576								i_required_attrs		IN	VARCHAR2  DEFAULT NULL,
3577								o_offer_item_ids	    OUT SYS_REFCURSOR,
3578								o_properties     		OUT SYS_REFCURSOR,
3579								o_error_reported 		OUT t.logical%TYPE,
3580								o_error_response 		OUT SYS_REFCURSOR
3581											)
3582	IS
3583		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemByShowModify';
3584		--local variables
3585		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
3586		v_error_reported				t.logical%TYPE;
3587		-- ref cursors
3588		c_properties					SYS_REFCURSOR;
3589		c_error_response				SYS_REFCURSOR;
3590		c_offer_item_ids				SYS_REFCURSOR;
3591	BEGIN
3592		o_error_reported 	:= k_false;
3593		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
3594			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
3595			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
3596		END IF;
3597		--get show's offer item ids
3598		on_demand_api.FindOfferItemByShow(i_show_id => i_show_id,
3599										  i_required_attrs => i_required_attrs,
3600										  o_offer_item_ids => c_offer_item_ids,
3601										  o_properties => c_properties,
3602										  o_error_reported => v_error_reported,
3603										  o_error_response => c_error_response
3604										  	  );
3605		--
3606		IF 	v_error_reported = k_false THEN
3607			findUnExpiredOfferItems(
3608								i_offer_item_ids => c_offer_item_ids
3609								   );
3610			--
3611		ELSE
3612			mergeErrors(
3613					i_error_response => c_error_response
3614						);
3615			--
3616			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3617												i_message         => 'Error while processing FindOfferItemByShow. Called from '||k_procedure_name ||', show_id='||i_show_id,
3618												i_broadcaster_ref => NULL,
3619												i_sequence        => 1,
3620												i_result          => k_rslt_tp_notvalid,
3621												i_message_type    => k_err_msg_type,
3622												i_message_code    => SQLCODE);
3623			o_error_response := error_handling_api.GetErrorRefCursor();
3624			RAISE e_error;
3625		END IF;
3626		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3627		IF	c_error_response%ISOPEN THEN
3628			CLOSE c_error_response;
3629		END IF;
3630		IF 	c_properties%ISOPEN THEN
3631		  		CLOSE c_properties;
3632		END IF;
3633		IF	c_offer_item_ids%ISOPEN THEN
3634			CLOSE c_offer_item_ids;
3635		END IF;
3636		--
3637		--check any offer item found or not
3638		SELECT 	time_1
3639		INTO	v_offer_item_id
3640		FROM	glxmem_tmp_decision_source t
3641		WHERE	t.decision_source_id = k_dummy_id
3642			AND	ROWNUM = 1;
3643		--open ref cursor for all un expired offer items
3644		OPEN 	o_offer_item_ids FOR
3645		SELECT	t.time_1 			 AS offerItemId,
3646				t.time_2	 		 AS offerId,
3647				t.alpha_1	 		 AS offerName,
3648				t.time_3		 	 AS serviceID,
3649				t.alpha_2		 	 AS serviceName,
3650				t.alpha_3		 	 AS offerStatusCode,
3651				t.alpha_4			 AS offerStausDescr,
3652				t.time_6			 AS offerStatusISN,
3653				t.alpha_5	 		 AS offerAvaibilityEndDate,
3654				t.time_4	 		 AS offerAvaibilityEndTime,
3655				t.alpha_6		 	 AS offerExpiryDate,
3656				t.time_5		 	 AS offerExpiryTime
3657		FROM	glxmem_tmp_decision_source t
3658		WHERE	t.decision_source_id = k_dummy_id;
3659		--
3660		g_delete_gtt_table := NULL;
3661		--
3662		--set empty ref cursors for properties, error as nothing to report back.
3663		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3664    	o_error_response := error_handling_api.GetErrorRefCursor();
3665	EXCEPTION
3666		WHEN	e_error THEN
3667				g_delete_gtt_table := NULL;
3668				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3669				IF	c_error_response%ISOPEN THEN
3670					CLOSE c_error_response;
3671				END IF;
3672				IF c_properties%ISOPEN THEN
3673				   CLOSE c_properties;
3674				END IF;
3675				o_error_reported := k_true;
3676				IF 	NOT o_offer_item_ids%ISOPEN THEN
3677					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3678				END IF;
3679       			IF 	NOT o_properties%ISOPEN THEN
3680					o_properties := error_handling_api.getemptyrefcursor();
3681				END IF;
3682		WHEN 	NO_DATA_FOUND THEN
3683				g_delete_gtt_table := NULL;
3684				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3685													i_message         => k_procedure_name || ': ' ||
3686																	 'No data found for show id='||i_show_id,
3687													i_broadcaster_ref => NULL,
3688													i_sequence        => 1,
3689													i_result          => k_rslt_tp_notvalid,
3690													i_message_type    => k_err_msg_type,
3691													i_message_code    => SQLCODE);
3692				o_error_reported := k_true;
3693				IF 	NOT o_offer_item_ids%ISOPEN THEN
3694					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3695				END IF;
3696       			IF 	NOT o_properties%ISOPEN THEN
3697					o_properties := error_handling_api.getemptyrefcursor();
3698				END IF;
3699    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3700		WHEN 	OTHERS THEN
3701				g_delete_gtt_table := NULL;
3702				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3703													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3704																	 'SQLERRM: ' || SQLERRM || '; show id='||i_show_id,
3705													i_broadcaster_ref => NULL,
3706													i_sequence        => 1,
3707													i_result          => k_rslt_tp_notvalid,
3708													i_message_type    => k_err_msg_type,
3709													i_message_code    => SQLCODE);
3710				o_error_reported := k_true;
3711				IF NOT o_offer_item_ids%ISOPEN THEN
3712					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3713				END IF;
3714        		IF 	NOT o_properties%ISOPEN THEN
3715					o_properties := error_handling_api.getemptyrefcursor();
3716				END IF;
3717    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3718				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3719				IF	c_error_response%ISOPEN THEN
3720					CLOSE c_error_response;
3721				END IF;
3722				IF c_properties%ISOPEN THEN
3723				   CLOSE c_properties;
3724				END IF;
3725	END FindOfferItemByShowModify;
3726
3727	PROCEDURE FindOfferItemBySeason(
3728								i_season_id				IN  season.season_id%TYPE,
3729								i_required_attrs		IN	VARCHAR2  DEFAULT NULL,
3730								o_offer_item_ids	    OUT SYS_REFCURSOR,
3731								o_properties     		OUT SYS_REFCURSOR,
3732								o_error_reported 		OUT t.logical%TYPE,
3733								o_error_response 		OUT SYS_REFCURSOR
3734											)
3735	IS
3736		k_procedure_name				CONSTANT VARCHAR2(60) := 'FindOfferItemBySeasonModify';
3737		--local variables
3738		v_offer_item_id					vod_item_schedule.item_schedule_id%TYPE;
3739		v_error_reported				t.logical%TYPE;
3740		-- ref cursors
3741		c_properties					SYS_REFCURSOR;
3742		c_error_response				SYS_REFCURSOR;
3743		c_offer_item_ids				SYS_REFCURSOR;
3744	BEGIN
3745		o_error_reported 	:= k_false;
3746		IF	g_delete_gtt_table IS NULL OR g_delete_gtt_table = k_true THEN
3747			g_item_count := 0;--set once only, then increment GTT table has PK which needs session_id + source id
3748			DELETE FROM glxmem_tmp_decision_source t WHERE t.decision_source_id = k_dummy_id;
3749		END IF;
3750		--get seasons's offer item ids
3751		on_demand_api.FindOfferItemBySeason(i_season_id => i_season_id,
3752										  i_required_attrs => i_required_attrs,
3753										  o_offer_item_ids => c_offer_item_ids,
3754										  o_properties => c_properties,
3755										  o_error_reported => v_error_reported,
3756										  o_error_response => c_error_response
3757										  	  );
3758		--
3759		IF 	v_error_reported = k_false THEN
3760			findUnExpiredOfferItems(
3761								i_offer_item_ids => c_offer_item_ids
3762								   );
3763			--
3764		ELSE
3765			mergeErrors(
3766					i_error_response => c_error_response
3767						);
3768			--
3769			error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3770												i_message         => 'Error while processing FindOfferItemBySeason. Called from '||k_procedure_name ||', season_id='||i_season_id,
3771												i_broadcaster_ref => NULL,
3772												i_sequence        => 1,
3773												i_result          => k_rslt_tp_notvalid,
3774												i_message_type    => k_err_msg_type,
3775												i_message_code    => SQLCODE);
3776			o_error_response := error_handling_api.GetErrorRefCursor();
3777			RAISE e_error;
3778		END IF;
3779		--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3780		IF	c_error_response%ISOPEN THEN
3781			CLOSE c_error_response;
3782		END IF;
3783		IF 	c_properties%ISOPEN THEN
3784		  		CLOSE c_properties;
3785		END IF;
3786		IF	c_offer_item_ids%ISOPEN THEN
3787			CLOSE c_offer_item_ids;
3788		END IF;
3789		--
3790		--check any offer item found or not
3791		SELECT 	time_1
3792		INTO	v_offer_item_id
3793		FROM	glxmem_tmp_decision_source t
3794		WHERE	t.decision_source_id = k_dummy_id
3795			AND	ROWNUM = 1;
3796		--open ref cursor for all un expired offer items
3797		OPEN 	o_offer_item_ids FOR
3798		SELECT	t.time_1 			 AS offerItemId,
3799				t.time_2	 		 AS offerId,
3800				t.alpha_1	 		 AS offerName,
3801				t.time_3		 	 AS serviceID,
3802				t.alpha_2		 	 AS serviceName,
3803				t.alpha_3		 	 AS offerStatusCode,
3804				t.alpha_4			 AS offerStausDescr,
3805				t.time_6			 AS offerStatusISN,
3806				t.alpha_5	 		 AS offerAvaibilityEndDate,
3807				t.time_4	 		 AS offerAvaibilityEndTime,
3808				t.alpha_6		 	 AS offerExpiryDate,
3809				t.time_5		 	 AS offerExpiryTime
3810		FROM	glxmem_tmp_decision_source t
3811		WHERE	t.decision_source_id = k_dummy_id;
3812		--
3813		g_delete_gtt_table := NULL;
3814		--
3815		--set empty ref cursors for properties, error as nothing to report back.
3816		o_properties	 :=	error_handling_api.GetEmptyRefCursor();
3817    	o_error_response := error_handling_api.GetErrorRefCursor();
3818	EXCEPTION
3819		WHEN	e_error THEN
3820				g_delete_gtt_table := NULL;
3821				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3822				IF	c_error_response%ISOPEN THEN
3823					CLOSE c_error_response;
3824				END IF;
3825				IF c_properties%ISOPEN THEN
3826				   CLOSE c_properties;
3827				END IF;
3828				o_error_reported := k_true;
3829				IF 	NOT o_offer_item_ids%ISOPEN THEN
3830					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3831				END IF;
3832       			IF 	NOT o_properties%ISOPEN THEN
3833					o_properties := error_handling_api.getemptyrefcursor();
3834				END IF;
3835		WHEN 	NO_DATA_FOUND THEN
3836				g_delete_gtt_table := NULL;
3837				error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
3838													i_message         => k_procedure_name || ': ' ||
3839																	 'No data found for show id='||i_season_id,
3840													i_broadcaster_ref => NULL,
3841													i_sequence        => 1,
3842													i_result          => k_rslt_tp_notvalid,
3843													i_message_type    => k_err_msg_type,
3844													i_message_code    => SQLCODE);
3845				o_error_reported := k_true;
3846				IF 	NOT o_offer_item_ids%ISOPEN THEN
3847					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3848				END IF;
3849       			IF 	NOT o_properties%ISOPEN THEN
3850					o_properties := error_handling_api.getemptyrefcursor();
3851				END IF;
3852    	    	o_error_response := error_handling_api.GetErrorRefCursor();
3853		WHEN 	OTHERS THEN
3854				g_delete_gtt_table := NULL;
3855				error_handling_api.logerrorresponse(io_response => error_handling_api.g_response,
3856													i_message         => k_procedure_name || ': ' || 'SQLCODE: ' || SQLCODE || '; ' ||
3857																	 'SQLERRM: ' || SQLERRM || '; show id='||i_season_id,
3858													i_broadcaster_ref => NULL,
3859													i_sequence        => 1,
3860													i_result          => k_rslt_tp_notvalid,
3861													i_message_type    => k_err_msg_type,
3862													i_message_code    => SQLCODE);
3863				o_error_reported := k_true;
3864				IF NOT o_offer_item_ids%ISOPEN THEN
3865					o_offer_item_ids := error_handling_api.getemptyrefcursor();
3866				END IF;
3867        		IF 	NOT o_properties%ISOPEN THEN
3868					o_properties := error_handling_api.getemptyrefcursor();
3869				END IF;
3870    	   	 	o_error_response := error_handling_api.GetErrorRefCursor();
3871				--close properties and error response local cursors - just to make sure open cursor limits doesn't exceeds
3872				IF	c_error_response%ISOPEN THEN
3873					CLOSE c_error_response;
3874				END IF;
3875				IF c_properties%ISOPEN THEN
3876				   CLOSE c_properties;
3877				END IF;
3878	END FindOfferItemBySeason;
3879
3880	PROCEDURE get_affected_offer_items(i_object_type 	IN VARCHAR2,
3881									   i_change_type	IN VARCHAR2,
3882									   i_pkey1			IN VARCHAR2,
3883									   i_pkey2 			IN VARCHAR2,
3884									   i_pkey3			IN VARCHAR2,
3885									   i_pkey1_name		IN VARCHAR2,
3886									   i_pkey2_name		IN VARCHAR2,
3887									   i_pkey3_name		IN VARCHAR2,
3888									   i_interface_id	IN table_line.table_line_isn%TYPE,
3889									   o_offer_items	OUT SYS_REFCURSOR,
3890									   o_error_reported	OUT k.logical%TYPE,
3891									   o_error_response	OUT SYS_REFCURSOR,
3892									   o_properties		OUT SYS_REFCURSOR
3893								   )
3894	IS
3895		CURSOR c_pkeys_definition_cursor(i_object_type VARCHAR2)
3896
3897  		IS
3898		  SELECT *
3899		  FROM
3900		  	(
3901				SELECT 'TITLE' 		AS object_type,
3902					   'TITLE_ID' 	AS pkey1,
3903						NULL 		AS pkey2,
3904						NULL		AS pkey3
3905				FROM DUAL
3906
3907				UNION
3908
3909				SELECT 'SERIES' 	 AS object_type,
3910					   'SERIES_ID'   AS pkey1,
3911					    NULL 		 AS pkey2,
3912					    NULL 		 AS pkey3
3913				FROM DUAL
3914
3915				UNION
3916
3917				SELECT 'VERSION' 	AS object_type,
3918					   'VERSION_ID' AS pkey1,
3919						NULL 		AS pkey2,
3920						NULL		AS pkey3
3921				FROM DUAL
3922
3923
3924				UNION
3925
3926				SELECT 'MEDIA_ITEM' 		AS object_type,
3927					   'MEDIA_ITEM_ID' 		AS pkey1,
3928					    NULL 		 	 	AS pkey2,
3929					    NULL 		 	 	AS pkey3
3930				FROM DUAL
3931
3932				UNION
3933
3934				SELECT 'EPG_LANGUAGE' 	AS object_type,
3935					   'TITLE_ID'   	AS pkey1,
3936						NULL 			AS pkey2,
3937						NULL			AS pkey3
3938				FROM DUAL
3939
3940				UNION
3941
3942				SELECT 'LISTING_SET_LANG_SPECIFIC' 	AS object_type,
3943					   'SET_ID'   	AS pkey1,
3944						NULL 			AS pkey2,
3945						NULL			AS pkey3
3946				FROM DUAL
3947
3948				UNION
3949
3950				SELECT 'CAST_MEMBER' 	AS object_type,
3951					   'CAST_ID'    	AS pkey1,
3952						NULL 			AS pkey2,
3953						NULL			AS pkey3
3954				FROM DUAL
3955
3956				UNION
3957
3958				SELECT 'CAST' 		 AS object_type,
3959					   'OBJECT_ID'   AS pkey1,
3960					   'OBJECT_TYPE' AS pkey2,
3961					   'CAST_ID'	 AS pkey3
3962				FROM DUAL
3963
3964				UNION
3965
3966				SELECT 'MEDIA_MATCH' 		 AS object_type,
3967					   'MEDIA_ITEM_ID'   	 AS pkey1,
3968					    NULL 				 AS pkey2,
3969					    NULL	 			 AS pkey3
3970				FROM DUAL
3971
3972				UNION
3973
3974				SELECT 'LICENSED_RIGHT' 	AS object_type,
3975					   'OBJECT_ID'		 	AS pkey1,
3976					    NULL 				AS pkey2,
3977					    NULL	 			AS pkey3
3978				FROM DUAL
3979
3980				UNION
3981
3982				SELECT 'OBJECT_METADATA' 	AS object_type,
3983					   'OBJECT_METADATA_ID' AS pkey1,
3984					    NULL 		 	 	AS pkey2,
3985					    NULL 		 	 	AS pkey3
3986				FROM DUAL
3987
3988				UNION
3989
3990
3991				SELECT 'ORIGIN_COUNTRY' AS object_type,
3992					   'OBJECT_ID'   	AS pkey1,
3993					   'OBJECT_TYPE' 	AS pkey2,
3994					   'COUNTRY_CODE' 	AS pkey3
3995				FROM DUAL
3996
3997				UNION
3998
3999				SELECT 'SHOW' 		 AS object_type,
4000					   'SHOW_ID'   	 AS pkey1,
4001					    NULL 		 AS pkey2,
4002					    NULL 		 AS pkey3
4003				FROM DUAL
4004
4005
4006				UNION
4007
4008				SELECT 'LISTING_TEXT_SET' 	 AS object_type,
4009					   'OBJECT_ID'   		 AS pkey1,
4010					    NULL 		 		 AS pkey2,
4011					    NULL 		 		 AS pkey3
4012				FROM DUAL
4013
4014				UNION
4015
4016				SELECT 'SEASON' 	 AS object_type,
4017					   'SEASON_ID'   AS pkey1,
4018					    NULL 		 AS pkey2,
4019					    NULL 		 AS pkey3
4020				FROM DUAL
4021
4022			)
4023		 WHERE object_type = i_object_type;
4024
4025		k_procedure_name	  		CONSTANT CHAR(24) := 'get_affected_offer_items';
4026
4027		k_insert					CONSTANT CHAR(6)  := 'INSERT';
4028		k_modify					CONSTANT CHAR(6)  := 'MODIFY';
4029		k_delete					CONSTANT CHAR(6)  := 'DELETE';
4030
4031		k_title				  		CONSTANT CHAR(5)  := 'TITLE';
4032		k_series			  		CONSTANT CHAR(6)  := 'SERIES';
4033		k_version			  		CONSTANT CHAR(7)  := 'VERSION';
4034		k_media_item		  		CONSTANT CHAR(10) := 'MEDIA_ITEM';
4035		k_epg_language  	  		CONSTANT CHAR(12) := 'EPG_LANGUAGE';
4036		k_listing_set_lang_specific CONSTANT CHAR(25) := 'LISTING_SET_LANG_SPECIFIC';
4037		k_cast_member		  		CONSTANT CHAR(11) := 'CAST_MEMBER';
4038		k_cast				  		CONSTANT CHAR(4)  := 'CAST';
4039		k_media_match				CONSTANT CHAR(11) := 'MEDIA_MATCH';
4040		k_licensed_right			CONSTANT CHAR(16) := 'LICENSED_RIGHT';
4041		k_object_metadata			CONSTANT CHAR(15) := 'OBJECT_METADATA';
4042		k_origin_country	  		CONSTANT CHAR(14) := 'ORIGIN_COUNTRY';
4043		k_show						CONSTANT CHAR(4)  := 'SHOW';
4044		k_listing_text_set			CONSTANT CHAR(16) := 'LISTING_TEXT_SET';
4045		k_season					CONSTANT CHAR(6)  := 'SEASON';
4046
4047
4048		v_object_type 	   VARCHAR2(30)  := NULL;
4049		v_pkey1_definition VARCHAR2(30)  := NULL;
4050		v_pkey2_definition VARCHAR2(30)  := NULL;
4051		v_pkey3_definition VARCHAR2(30)  := NULL;
4052		v_object_id1	   VARCHAR2(200) := NULL;
4053		v_object_id2	   VARCHAR2(200) := NULL;
4054		v_object_id3	   VARCHAR2(200) := NULL;
4055
4056
4057		v_error_reported k.logical%TYPE := NULL;
4058		c_error_response SYS_REFCURSOR;
4059		c_properties 	 SYS_REFCURSOR;
4060
4061		e_key_not_found EXCEPTION;
4062
4063	BEGIN
4064		-- Get Table Definition
4065		OPEN c_pkeys_definition_cursor(i_object_type);
4066  		FETCH c_pkeys_definition_cursor INTO v_object_type,v_pkey1_definition,v_pkey2_definition,v_pkey3_definition;
4067		CLOSE c_pkeys_definition_cursor;
4068
4069		IF v_object_type IS NULL THEN
4070			RAISE e_key_not_found;
4071		END IF;
4072		--Check and assign primary keys
4073		IF v_pkey1_definition IS NOT NULL THEN
4074			IF v_pkey1_definition = i_pkey1_name THEN
4075				v_object_id1 := i_pkey1;
4076			ELSIF v_pkey1_definition = i_pkey2_name THEN
4077				v_object_id1 := i_pkey2;
4078			ELSIF v_pkey1_definition = i_pkey3_name THEN
4079				v_object_id1 := i_pkey3;
4080			ELSE
4081				RAISE e_key_not_found;
4082			END IF;
4083
4084		END IF;
4085
4086		IF v_pkey2_definition IS NOT NULL THEN
4087			IF v_pkey2_definition = i_pkey1_name THEN
4088				v_object_id2 := i_pkey1;
4089			ELSIF v_pkey2_definition = i_pkey2_name THEN
4090				v_object_id2 := i_pkey2;
4091			ELSIF v_pkey2_definition = i_pkey3_name THEN
4092				v_object_id2 := i_pkey3;
4093			ELSE
4094				RAISE e_key_not_found;
4095			END IF;
4096
4097		END IF;
4098
4099		IF v_pkey3_definition IS NOT NULL THEN
4100			IF v_pkey3_definition = i_pkey1_name THEN
4101				v_object_id3 := i_pkey1;
4102			ELSIF v_pkey3_definition = i_pkey2_name THEN
4103				v_object_id3 := i_pkey2;
4104			ELSIF v_pkey3_definition = i_pkey3_name THEN
4105				v_object_id3 := i_pkey3;
4106			ELSE
4107				RAISE e_key_not_found;
4108			END IF;
4109
4110		END IF;
4111
4112		-- Open the Offer Items Cursor according to the Object Type
4113		CASE
4114		-- Title Change
4115		WHEN i_object_type = k_title THEN
4116			FindOfferItemByTitleModify(i_title_id => v_object_id1,
4117									   i_checkAsMain => 1,
4118									   i_checkAsAssociated => 1,
4119									   i_checkAsEmbbeded => 1,
4120									   i_interface_id => i_interface_id,
4121									   o_offer_item_ids => o_offer_items,
4122									   o_properties => c_properties,
4123									   o_error_reported => v_error_reported,
4124									   o_error_response => c_error_response
4125									   );
4126		WHEN i_object_type = k_series THEN
4127			FindOfferItemBySeriesModify(i_series_id => v_object_id1,
4128										o_offer_item_ids => o_offer_items,
4129										o_properties => c_properties,
4130										o_error_reported => v_error_reported,
4131										o_error_response => c_error_response
4132										);
4133		WHEN i_object_type = k_version THEN
4134
4135			IF i_change_type = k_insert THEN
4136				FindOfferItemByVersionModify(i_version_id => v_object_id1,
4137											 i_checkAsMain => 0,
4138											 i_checkAsAssociated => 1,
4139											 i_checkAsEmbbeded => 0,
4140											 i_changeType => i_change_type,
4141											 i_interface_id => i_interface_id,
4142											 o_offer_item_ids => o_offer_items,
4143											 o_properties => c_properties,
4144											 o_error_reported => v_error_reported,
4145											 o_error_response => c_error_response
4146											 );
4147			ELSIF i_change_type = k_modify THEN
4148				FindOfferItemByVersionModify(i_version_id => v_object_id1,
4149											 i_checkAsMain => 1,
4150											 i_checkAsAssociated => 1,
4151											 i_checkAsEmbbeded => 1,
4152											 i_changeType => i_change_type,
4153											 i_interface_id => i_interface_id,
4154											 o_offer_item_ids => o_offer_items,
4155											 o_properties => c_properties,
4156											 o_error_reported => v_error_reported,
4157											 o_error_response => c_error_response
4158											 );
4159			ELSE
4160				FindOfferItemByVersionDelete(i_version_id => v_object_id1,
4161											 i_title_id => v_object_id2,
4162											 i_checkAsMain => 1,
4163											 i_checkAsAssociated => 1,
4164											 i_checkAsEmbbeded => 1,
4165											 o_offer_item_ids => o_offer_items,
4166											 o_properties => c_properties,
4167											 o_error_reported => v_error_reported,
4168											 o_error_response => c_error_response
4169											 );
4170			END IF;
4171
4172		WHEN i_object_type = k_media_item THEN
4173
4174			FindOfferItemByMediaItem(i_media_item_id => v_object_id1,
4175									 o_offer_item_ids => o_offer_items,
4176									 o_properties => c_properties,
4177									 o_error_reported => v_error_reported,
4178									 o_error_response => c_error_response
4179									 );
4180		WHEN i_object_type = k_epg_language THEN
4181			FindOfferItemByTitleModify(i_title_id => v_object_id1,
4182									   i_checkAsMain => 1,
4183									   i_checkAsAssociated => 1,
4184									   i_checkAsEmbbeded => 1,
4185									   i_interface_id => i_interface_id,
4186									   o_offer_item_ids => o_offer_items,
4187									   o_properties => c_properties,
4188									   o_error_reported => v_error_reported,
4189									   o_error_response => c_error_response
4190									   );
4191		WHEN i_object_type = k_listing_set_lang_specific THEN
4192			FindOfferItemByListingTextSet(i_set_id => v_object_id1,
4193										  o_offer_item_ids => o_offer_items,
4194										  o_properties => c_properties,
4195										  o_error_reported => v_error_reported,
4196										  o_error_response => c_error_response
4197										  );
4198		WHEN i_object_type = k_cast_member THEN
4199			FindOfferItemByCastMember(i_cast_id => v_object_id1,
4200									  i_interface_id => i_interface_id,
4201									  o_offer_item_ids => o_offer_items,
4202									  o_properties => c_properties,
4203									  o_error_reported => v_error_reported,
4204									  o_error_response => c_error_response
4205									  );
4206		WHEN i_object_type = k_cast THEN
4207			FindOfferItemByCast(i_cast_id => v_object_id1,
4208								i_object_type => v_object_id2,
4209								i_object_id => v_object_id3,
4210								i_interface_id => i_interface_id,
4211								o_offer_item_ids => o_offer_items,
4212								o_properties => c_properties,
4213								o_error_reported => v_error_reported,
4214								o_error_response => c_error_response
4215								);
4216		WHEN i_object_type = k_media_match THEN
4217			FindOfferItemByMediaItem(i_media_item_id => v_object_id1,
4218									 o_offer_item_ids => o_offer_items,
4219									 o_properties => c_properties,
4220									 o_error_reported => v_error_reported,
4221									 o_error_response => c_error_response
4222									 );
4223		WHEN i_object_type = k_licensed_right THEN
4224			FindOfferItemByLicensedRight(i_object_type => v_object_id2,
4225										 i_object_id => v_object_id1 ,
4226										 o_offer_item_ids => o_offer_items,
4227										 o_properties => c_properties,
4228										 o_error_reported => v_error_reported,
4229										 o_error_response => c_error_response
4230										 );
4231
4232		WHEN i_object_type = k_object_metadata THEN
4233			FindOfferItemByObjectMetadata(i_metadata_id => v_object_id1,
4234										  i_interface_id => i_interface_id,
4235										  o_offer_item_ids => o_offer_items,
4236										  o_properties => c_properties,
4237										  o_error_reported => v_error_reported,
4238										  o_error_response => c_error_response
4239										  );
4240		WHEN i_object_type = k_origin_country THEN
4241			FindOfferItemByCountryOfOrigin(i_object_type => v_object_id2,
4242										   i_object_id => v_object_id1,
4243										   i_interface_id => i_interface_id,
4244										   i_required_attrs => v_object_id3,
4245										   o_offer_item_ids => o_offer_items,
4246										   o_properties => c_properties,
4247										   o_error_reported => v_error_reported,
4248										   o_error_response => c_error_response
4249										   );
4250		WHEN i_object_type = k_listing_text_set THEN
4251			FindOfferItemByListingTextSet(i_object_type => v_object_id2,
4252									      i_object_id => v_object_id1,
4253										  i_interface_id => i_interface_id,
4254										  o_offer_item_ids => o_offer_items,
4255										  o_properties => o_properties,
4256										  o_error_reported => v_error_reported,
4257										  o_error_response => c_error_response
4258										  );
4259		WHEN i_object_type = k_show THEN
4260			FindOfferItemByShowModify(i_show_id => v_object_id1,
4261									  o_offer_item_ids => o_offer_items,
4262									  o_properties => c_properties,
4263									  o_error_reported => v_error_reported,
4264									  o_error_response => c_error_response
4265									  );
4266		WHEN i_object_type = k_season THEN
4267			FindOfferItemBySeason(i_season_id => v_object_id1,
4268								  o_offer_item_ids => o_offer_items,
4269								  o_properties => c_properties,
4270								  o_error_reported => v_error_reported,
4271								  o_error_response => c_error_response
4272								  );
4273		END CASE;
4274
4275		o_error_response := c_error_response;
4276		o_properties 	 := c_properties;
4277
4278		IF c_error_response%ISOPEN THEN
4279			CLOSE c_error_response;
4280		END IF;
4281
4282		IF c_properties%ISOPEN THEN
4283			CLOSE c_properties;
4284		END IF;
4285
4286		o_error_reported := v_error_reported;
4287
4288	EXCEPTION WHEN e_key_not_found THEN
4289		o_error_reported := k_true;
4290
4291		IF NOT o_error_response%ISOPEN THEN
4292			o_error_response := error_handling_api.GetErrorRefCursor();
4293		END IF;
4294		error_handling_api.logerrorresponse(io_response 	  => error_handling_api.g_response,
4295													i_message         => k_procedure_name || ': ' || 'One of the Primary Keys was not found '|| '; ' || 'i_object_type: ' ||
4296																	 i_object_type ,
4297													i_broadcaster_ref => NULL,
4298													i_sequence        => 1,
4299													i_result          => k_rslt_tp_notvalid,
4300													i_message_type    => k_err_msg_type,
4301													i_message_code    => SQLCODE);
4302
4303		IF NOT o_properties%ISOPEN THEN
4304			o_properties := error_handling_api.GetEmptyRefCursor();
4305		END IF;
4306		IF c_error_response%ISOPEN THEN
4307			CLOSE c_error_response;
4308		END IF;
4309
4310		IF c_properties%ISOPEN THEN
4311			CLOSE c_properties;
4312		END IF;
4313
4314	END get_affected_offer_items;
4315
4316	PROCEDURE get_offer_items_for_tables(i_template 	  		IN 	VARCHAR2,
4317										 i_date			  		IN	VARCHAR2,
4318										 i_time			  		IN	NUMBER,
4319										 i_interface_type		IN  table_line.table_code%TYPE,
4320										 o_offer_items 	  		OUT SYS_REFCURSOR,
4321										 o_data_changes_log		OUT SYS_REFCURSOR,
4322										 o_error_reported 		OUT k.logical%TYPE,
4323										 o_error_response		OUT SYS_REFCURSOR,
4324										 o_properties			OUT SYS_REFCURSOR)
4325	IS
4326		  c_audit_events 			SYS_REFCURSOR;
4327		  c_logged_attributes 		SYS_REFCURSOR;
4328		  v_error_reported 			NUMBER(1) := k_true;
4329		  c_error_response 			SYS_REFCURSOR;
4330
4331		  v_object_type 			VARCHAR2(30);
4332		  v_pkey1 					VARCHAR2(100);
4333		  v_pkey2 					VARCHAR2(100);
4334		  v_pkey3 					VARCHAR2(100);
4335		  v_pkey1_name 				VARCHAR2(30);
4336		  v_pkey2_name 				VARCHAR2(30);
4337		  v_pkey3_name 				VARCHAR2(30);
4338		  v_change_type 			VARCHAR2(30);
4339		  v_changed_attributs 		VARCHAR2(32767);
4340		  v_audit_date				CHAR(8);
4341		  v_audit_time				NUMBER(10);
4342		  v_audit_user				VARCHAR2(30);
4343		  c_offer_items 			SYS_REFCURSOR;
4344		  v2_error_reported 		NUMBER(1) := k_true;
4345		  c2_error_response 		SYS_REFCURSOR;
4346		  c2_properties 			SYS_REFCURSOR;
4347
4348		  v_offer_item_id 			vod_item_schedule.item_schedule_id%TYPE;
4349		  v_offer_id				vod_offer_schedule.offer_id%TYPE;
4350		  v_offer_name				vod_offer_schedule.offer_name%TYPE;
4351		  v_service_id				vod_platform.vod_platform_id%TYPE;
4352		  v_service_name			vod_platform.platform_name%TYPE;
4353		  v_offer_status_code		table_line.table_code%TYPE;
4354		  v_offer_status_desc		table_line.table_description%TYPE;
4355		  v_offer_status_isn		table_line.table_line_isn%TYPE;
4356		  v_offer_avail_end_date	vod_offer_schedule.end_date%TYPE;
4357		  v_offer_avail_start_date	vod_offer_schedule.start_date%TYPE;
4358		  v_offer_expiry_date		vod_offer_schedule.expiry_date%TYPE;
4359		  v_offer_expiry_time		vod_offer_schedule.expiry_time%TYPE;
4360
4361		  v_property_code			property_template.property_code%TYPE := i_interface_type || '_ELIGABLE_CHNG_ST';
4362
4363		  v_temp_session_id 		glxmem_tmp_fk_joins.tmp_session_id%TYPE := galaxy_common.get_temp_session_id();
4364
4365	BEGIN
4366			o_error_reported := k_false;
4367
4368			ibms_audit_api.QueryAuditByTemplate(i_templates => i_template,
4369												 i_date => i_date,
4370												 i_time => i_time,
4371												 o_audit_events => c_audit_events,
4372												 o_logged_attributes => c_logged_attributes,
4373												 o_error_reported => v_error_reported,
4374												 o_error_response => c_error_response);
4375			IF v_error_reported = k_false THEN
4376
4377				LOOP
4378
4379					FETCH c_audit_events INTO
4380							v_object_type ,
4381							v_pkey1,
4382							v_pkey2,
4383							v_pkey3,
4384							v_pkey1_name,
4385							v_pkey2_name,
4386							v_pkey3_name,
4387							v_change_type,
4388							v_changed_attributs,
4389							v_audit_date,
4390							v_audit_time,
4391							v_audit_user;
4392					EXIT WHEN c_audit_events%NOTFOUND;
4393
4394					ibms_audit_api.get_affected_offer_items(i_object_type => v_object_type,
4395															 i_change_type => v_change_type,
4396															 i_pkey1 => v_pkey1,
4397															 i_pkey2 => v_pkey2,
4398															 i_pkey3 => v_pkey3,
4399															 i_pkey1_name => v_pkey1_name,
4400															 i_pkey2_name => v_pkey2_name,
4401															 i_pkey3_name => v_pkey3_name,
4402															 i_interface_id => iface.interface_id(i_interface_type),
4403															 o_offer_items => c_offer_items,
4404															 o_error_reported => v2_error_reported,
4405															 o_error_response => c2_error_response,
4406															 o_properties => c2_properties);
4407
4408					IF v2_error_reported = k_false THEN
4409					LOOP
4410						FETCH c_offer_items INTO
4411							v_offer_item_id,
4412							v_offer_id,
4413							v_offer_name,
4414							v_service_id,
4415							v_service_name,
4416							v_offer_status_code,
4417							v_offer_status_desc,
4418							v_offer_status_isn,
4419							v_offer_avail_end_date,
4420							v_offer_avail_start_date,
4421							v_offer_expiry_date,
4422							v_offer_expiry_time;
4423						EXIT WHEN c_offer_items%NOTFOUND;
4424						-- Check if the Offer is Eligible for Export
4425						IF ibms_prop.get_property_number(i_property_code => v_property_code,
4426														 i_object_id => iface.interface_id(i_interface_type),
4427														 i_level_1_id => v_offer_status_isn) = k_true 
4428						AND is_offer_valid(v_offer_item_id) = k_true 						
4429						THEN
4430													
4431							INSERT INTO glxmem_tmp_fk_joins
4432							(
4433							tmp_session_id, --temp_session_id
4434							numeric_fld1,--offer_item_id
4435							numeric_fld2,--offer id
4436							string_fld1, --offer_name
4437							string_fld2, --service_name
4438							string_fld3, --offer_status_descr
4439							string_fld4, --object_type
4440							numeric_fld3, --object_id
4441							string_fld5, --change_type
4442							string_fld6 -- changed_attributes
4443							)
4444							VALUES(v_temp_session_id,
4445								   v_offer_item_id,
4446								   v_offer_id,
4447								   v_offer_name,
4448								   v_service_name,
4449								   v_offer_status_desc,
4450								   v_object_type,
4451								   v_pkey1,
4452								   v_change_type,
4453								   v_changed_attributs
4454							);						
4455
4456						END IF;
4457					END LOOP;
4458							COMMIT;
4459					END IF;
4460				END LOOP;
4461
4462				OPEN o_offer_items FOR
4463					SELECT 	numeric_fld1 AS offer_item_id
4464					FROM 	glxmem_tmp_fk_joins
4465					WHERE	tmp_session_id = v_temp_session_id;
4466
4467				OPEN o_data_changes_log FOR
4468					SELECT DISTINCT
4469							numeric_fld1 	AS 	offer_item_id,
4470							string_fld1  	AS 	offer_name,
4471							string_fld2		AS 	service_name,
4472							string_fld3		AS 	offer_status_desc,
4473							string_fld4		AS	object_type,
4474							numeric_fld3	AS	object_id,
4475							numeric_fld2	AS	offer_id,
4476							string_fld5		AS	change_type,
4477							string_fld6		AS	changed_attributes
4478					FROM glxmem_tmp_fk_joins
4479					WHERE	tmp_session_id = v_temp_session_id;
4480
4481				DELETE FROM glxmem_tmp_fk_joins
4482				WHERE tmp_session_id = v_temp_session_id;
4483
4484				COMMIT;
4485
4486				CLOSE c_audit_events;
4487				IF c_logged_attributes%ISOPEN THEN
4488					CLOSE c_logged_attributes;
4489				END IF;
4490			   IF c_error_response%ISOPEN THEN
4491					CLOSE c_error_response;
4492				END IF;
4493	  END IF;
4494
4495	  o_error_response := error_handling_api.GetErrorRefCursor();
4496	  o_properties 	   := error_handling_api.GetEmptyRefCursor();
4497
4498	  IF NOT o_offer_items%ISOPEN THEN
4499	  	o_offer_items := error_handling_api.GetEmptyRefCursor();
4500	  END IF;
4501
4502	  IF NOT o_data_changes_log%ISOPEN THEN
4503	  	o_data_changes_log := error_handling_api.GetEmptyRefCursor();
4504	  END IF;
4505
4506	EXCEPTION WHEN OTHERS THEN
4507
4508		o_error_reported := k_true;
4509		IF NOT(o_error_response%ISOPEN) THEN
4510			o_error_response := error_handling_api.GetErrorRefCursor();
4511		END IF;
4512
4513		IF NOT(o_properties%ISOPEN) THEN
4514			o_properties := error_handling_api.GetEmptyRefCursor();
4515		END IF;
4516
4517		IF c_logged_attributes%ISOPEN THEN
4518			CLOSE c_logged_attributes;
4519		END IF;
4520		IF c_error_response%ISOPEN THEN
4521			CLOSE c_error_response;
4522		END IF;
4523
4524		IF c2_error_response%ISOPEN THEN
4525			CLOSE c2_error_response;
4526		END IF;
4527
4528		IF c2_properties%ISOPEN THEN
4529			CLOSE c2_properties;
4530		END IF;
4531	END get_offer_items_for_tables;
4532	--
4533	FUNCTION getAuditItemChangedAttr(
4534								-- for passed in audit id and sensitive columns list, return all column changed 
4535								i_audit_id			IN	audit_item.audit_id%TYPE,
4536								i_sensitive_columns IN	VARCHAR2,
4537								i_column_separator	IN 	VARCHAR2 DEFAULT ';'
4538									)
4539	RETURN	VARCHAR2		
4540	IS
4541		v_changed_attributes		k.max_varchar2%TYPE := NULL;
4542	BEGIN
4543		--check columns and separator supplied - if not return null
4544		IF	TRIM(REPLACE(i_sensitive_columns,i_column_separator)) IS NOT NULL
4545		AND	TRIM(i_column_separator) IS NOT NULL 
4546		THEN
4547			SELECT	stragg(strtoken(ai.old_audit_value,1,k_colon))
4548			INTO	v_changed_attributes
4549			FROM	audit_item ai
4550			WHERE 	ai.audit_id= i_audit_id 
4551				AND EXISTS (SELECT TRIM(column_value) 
4552							 FROM   TABLE(galaxy_util.split_str(i_sensitive_columns, i_column_separator)) t WHERE INSTR(ai.old_audit_value, t.column_value||k_colon) > k_false);
4553		END IF;	
4554		--				 
4555		RETURN	v_changed_attributes;
4556	END getAuditItemChangedAttr;		
4557	--
4558	PROCEDURE getAuditedTables(
4559							-- Using supplied interface type, check tracable fields property to return list of tables user decided to track for the interface
4560							i_interface_type	IN	table_line.table_code%TYPE,
4561							o_tables 	  		OUT SYS_REFCURSOR,
4562							o_error				OUT VARCHAR2
4563								)
4564	IS
4565		v_interface_id				table_line.table_line_isn%TYPE;
4566		v_tracked_cols_prop			property_template.property_code%TYPE := i_interface_type || '_TRACKED_FIELDS';
4567	BEGIN
4568		o_error := NULL;
4569		v_interface_id	:= iface.interface_id(
4570										i_interface_type => i_interface_type
4571											 );
4572		OPEN o_tables FOR			
4573			SELECT 	comobj.table_code 		  AS tableCode,
4574					comobj.table_line_isn 	  AS tableLineIsn,
4575					upper(comobj.long_text_2) AS tableName
4576			FROM	table_line comobj
4577			WHERE	comobj.table_id = k_object_type_tl
4578				AND	TRIM(comobj.long_text_2) IS NOT NULL --table name is there
4579				AND EXISTS (
4580							SELECT	ec.table_name AS tableName
4581							FROM	ext_mem_table_columns ec							
4582							WHERE	ec.table_name = upper(comobj.long_text_2)
4583								AND	ibms_prop.get_property_number(v_tracked_cols_prop, v_interface_id, ec.ext_mem_table_columns_id) = k_true
4584								AND ROWNUM = 1
4585						   );
4586	EXCEPTION
4587		WHEN	OTHERS THEN			
4588				o_tables := error_handling_api.GetEmptyRefCursor;
4589				o_error := 'Error while processing getAuditedTables. Error is '||SQLERRM||'. Interface type '||i_interface_type;
4590	END getAuditedTables;
4591	--						
4592	PROCEDURE getOfferItemsForTable(
4593								-- Using supplied table name find MAM pending audit records, for each audit found find offer item linked to it
4594								i_interface_type	IN	table_line.table_code%TYPE,
4595								i_table_name  		IN 	VARCHAR2,
4596								i_date			  	IN	VARCHAR2,
4597								i_time			  	IN	NUMBER,										 
4598								o_offer_items 	  	OUT SYS_REFCURSOR,
4599								o_data_changes_log	OUT SYS_REFCURSOR,
4600								o_error_reported 	OUT k.logical%TYPE,
4601								o_error_response	OUT SYS_REFCURSOR,
4602								o_properties		OUT SYS_REFCURSOR
4603										)
4604	IS
4605		--local constants
4606		k_pending					CONSTANT CHAR(1) := 'P';
4607		k_insert					CONSTANT CHAR(6) := 'INSERT';
4608		k_create					CONSTANT CHAR(6) := 'CREATE';
4609		k_modify					CONSTANT CHAR(6) := 'MODIFY';
4610		k_update					CONSTANT CHAR(6) := 'UPDATE';
4611		k_delete					CONSTANT CHAR(6) := 'DELETE';
4612		--local variables
4613		v_interface_id				table_line.table_line_isn%TYPE;
4614		v_error_reported 			NUMBER(1) := k_true;
4615		v_change_type				audit_trail.audit_action_type%TYPE;
4616		v_sensitive_columns 		k.max_varchar2_plsql%TYPE;
4617		v_changed_attributs 		k.max_varchar2_plsql%TYPE;
4618		
4619	  	v_offer_item_id 			vod_item_schedule.item_schedule_id%TYPE;
4620		v_offer_id					vod_offer_schedule.offer_id%TYPE;
4621		v_offer_name				vod_offer_schedule.offer_name%TYPE;
4622		v_service_id				vod_platform.vod_platform_id%TYPE;
4623		v_service_name				vod_platform.platform_name%TYPE;
4624		v_offer_status_code			table_line.table_code%TYPE;
4625		v_offer_status_desc			table_line.table_description%TYPE;
4626		v_offer_status_isn			table_line.table_line_isn%TYPE;
4627		v_offer_avail_end_date		vod_offer_schedule.end_date%TYPE;
4628		v_offer_avail_start_date	vod_offer_schedule.start_date%TYPE;
4629		v_offer_expiry_date			vod_offer_schedule.expiry_date%TYPE;
4630		v_offer_expiry_time			vod_offer_schedule.expiry_time%TYPE;
4631		v_object_type				table_line.table_code%TYPE;
4632		v_object_sub_type			table_line.table_code%TYPE := NULL;
4633		v_object_id					table_line.table_line_isn%TYPE;
4634		v_title_id					table_line.table_line_isn%TYPE;
4635
4636		v_elig_change_status_prop	property_template.property_code%TYPE := i_interface_type || '_ELIGABLE_CHNG_ST';
4637		v_tracked_cols_prop			property_template.property_code%TYPE := i_interface_type || '_TRACKED_FIELDS';
4638		v_temp_session_id 			glxmem_tmp_fk_joins.tmp_session_id%TYPE := galaxy_common.get_temp_session_id();
4639		--ref cursors
4640		c_audit_events 				SYS_REFCURSOR;
4641		c_logged_attributes 		SYS_REFCURSOR;
4642		c_error_response 			SYS_REFCURSOR;
4643		c_properties				SYS_REFCURSOR;
4644		c_offer_items 				SYS_REFCURSOR;
4645		--pl/sql table
4646		TYPE t_object_processed IS TABLE OF k.logical%TYPE INDEX BY VARCHAR2(50);
4647		tbl_object_processed		t_object_processed;
4648		TYPE rt_offer_rec IS RECORD
4649		(
4650		offer_item_id 				vod_item_schedule.item_schedule_id%TYPE,
4651		offer_id					vod_offer_schedule.offer_id%TYPE,
4652		offer_name					vod_offer_schedule.offer_name%TYPE,
4653		service_id					vod_platform.vod_platform_id%TYPE,
4654		service_name				vod_platform.platform_name%TYPE,
4655		offer_status_code			table_line.table_code%TYPE,
4656		offer_status_desc			table_line.table_description%TYPE,
4657		offer_status_isn			table_line.table_line_isn%TYPE,
4658		offer_avail_end_date		vod_offer_schedule.end_date%TYPE,
4659		offer_avail_start_date		vod_offer_schedule.start_date%TYPE,
4660		offer_expiry_date			vod_offer_schedule.expiry_date%TYPE,
4661		offer_expiry_time			vod_offer_schedule.expiry_time%TYPE
4662		);
4663		r_offer_rec					rt_offer_rec;
4664		TYPE t_offer_rec			IS TABLE OF r_offer_rec%TYPE INDEX BY BINARY_INTEGER;
4665		tbl_offer_rec				t_offer_rec;
4666		TYPE rt_audit_obj IS RECORD
4667		(
4668		audit_id					audit_trail.audit_id%TYPE,
4669		changeType					audit_trail.audit_action_type%TYPE,
4670		object_id					audit_trail.object_id%TYPE,
4671		object_type					audit_trail.object_type%TYPE
4672		);
4673		r_audit_rec					rt_audit_obj;
4674		TYPE t_audit_rec			IS TABLE OF r_audit_rec%TYPE INDEX BY BINARY_INTEGER;
4675		tbl_audit_rec				t_audit_rec;
4676		CURSOR c_obj_type
4677		IS
4678			SELECT	atr.audit_id,
4679					atr.audit_action_type AS changeType,
4680					atr.object_id,
4681					atr.object_type
4682			FROM    omnibus_action oa
4683			JOIN    audit_trail atr ON (atr.audit_id = oa.audit_id)
4684			WHERE   oa.sub_type = i_interface_type
4685			AND 	oa.status = k_pending --P
4686			AND		atr.object_type = v_object_type
4687			AND 	atr.audit_date >= i_date
4688			AND NOT (atr.audit_date = i_date AND atr.audit_time <= i_time)
4689			;
4690		CURSOR c_obj_sub_type
4691		IS
4692			SELECT	atr.audit_id,
4693					atr.audit_action_type AS changeType,
4694					atr.object_id,
4695					atr.object_type
4696			FROM    omnibus_action oa
4697			JOIN    audit_trail atr ON (atr.audit_id = oa.audit_id)
4698			WHERE   oa.sub_type = i_interface_type
4699			AND 	oa.status = k_pending --P
4700			AND		atr.object_sub_type = v_object_sub_type
4701			AND 	atr.audit_date >= i_date
4702			AND NOT (atr.audit_date = i_date AND atr.audit_time <= i_time)
4703			;
4704	BEGIN
4705		o_error_reported := k_false;		
4706		tbl_object_processed.DELETE;		
4707		tbl_offer_rec.delete;
4708		tbl_audit_rec.delete;
4709		v_interface_id := iface.interface_id(
4710										i_interface_type => i_interface_type
4711											);		
4712		--get object type to be used in below audit trail filter
4713		SELECT	comobj.table_code
4714		INTO	v_object_type
4715		FROM	table_line comobj
4716		WHERE	comobj.long_text_2 = i_table_name
4717		AND 	rownum = 1 ;
4718		g.debug('table object code', v_object_type);
4719		
4720		
4721		--check if it's object sub type
4722		BEGIN
4723			SELECT atr.object_sub_type
4724			INTO 	v_object_sub_type
4725			FROM audit_trail atr
4726			WHERE atr.object_sub_type =  v_object_type
4727			AND rownum = 1;
4728		EXCEPTION
4729		WHEN OTHERS THEN
4730			v_object_sub_type := k_space;
4731		END;		
4732		--get sensitive columns
4733		SELECT  stragg(em.column_name)
4734		INTO	v_sensitive_columns
4735		FROM	ext_mem_table_columns em
4736		WHERE 	em.table_name = i_table_name
4737		AND 	ibms_prop.get_property_number(v_tracked_cols_prop, v_interface_id, em.ext_mem_table_columns_id) = k_true;
4738		--
4739		g.debug('sensitive columns', v_sensitive_columns);
4740		--loop around all pending audit records since last time it was run
4741
4742		IF v_object_sub_type = k_space
4743		THEN
4744
4745		OPEN c_obj_type;
4746
4747			FETCH c_obj_type
4748			BULK COLLECT
4749			INTO tbl_audit_rec;
4750
4751		CLOSE c_obj_type;
4752		ELSE
4753		OPEN c_obj_sub_type;
4754			FETCH c_obj_sub_type
4755			BULK COLLECT
4756			INTO tbl_audit_rec;
4757		CLOSE c_obj_sub_type;
4758		END IF;
4759		BEGIN
4760		FOR j IN 1..tbl_audit_rec.count
4761		LOOP
4762			v_object_id 	:= tbl_audit_rec(j).object_id;
4763			v_object_type	:= tbl_audit_rec(j).object_type;
4764			g.debug('object ', v_object_type||v_object_id);
4765			v_changed_attributs := NULL;
4766			--for update gets changed attributes for insert and delete we don't need
4767			IF tbl_audit_rec(j).changetype IN (k_modify, k_update) --should be UPDATE only
4768			THEN
4769				v_changed_attributs := ibms_audit_api.getAuditItemChangedAttr(
4770																		i_audit_id			=> tbl_audit_rec(j).audit_id,
4771																		i_sensitive_columns => v_sensitive_columns,
4772																		i_column_separator 	=> k_comma
4773																			 );
4774				g.debug('changed attr=',v_changed_attributs, 'v_object_id=', v_object_id, 'v_object_type=', v_object_type);
4775			END IF;
4776			v_change_type := tbl_audit_rec(j).changetype;
4777			--g.debug('change type', v_audit_rec.changetype);
4778			--Process object if new i.e. not already process earlier. It is possible same object can be repeated 
4779			--process audit event only if DELETE/INSERT or if update then there is changed field i.e. v_changed_attributs is not empty
4780			IF	NOT tbl_object_processed.EXISTS(v_object_type||TO_CHAR(v_object_id,'099999999'))
4781			AND	(v_changed_attributs IS NOT NULL
4782			OR	tbl_audit_rec(j).changetype NOT IN (k_modify, k_update))
4783			THEN
4784				--g.debug('find offer items');
4785				tbl_object_processed(v_object_type||TO_CHAR(v_object_id,'099999999')) := k_true;
4786				v_error_reported := k_false;--pending what about overall error
4787				-- Open the Offer Items Cursor according to the Object Type
4788				CASE
4789				--Note cast will have TI object type (so wil don't need separate FindOfferItemByCast etc)
4790				--Note epg, listing text set/lang/category, object rights, object associated content
4791				--Note aka, object metadata, country origin
4792				--Note all will have object type title or series or season or deal so no separate call is needed.
4793				--pending make sure MAMSYS long text 1
4794				-- Title Change
4795				--
4796				WHEN v_object_type = k_title_object_type THEN
4797					--
4798					FindOfferItemByTitleModify(i_title_id 			=> v_object_id,
4799											   i_checkAsMain 		=> 1,
4800											   i_checkAsAssociated 	=> 1,
4801											   i_checkAsEmbbeded 	=> 1,
4802											   i_interface_id 		=> v_interface_id,
4803											   o_offer_item_ids 	=> c_offer_items,
4804											   o_properties 		=> c_properties,
4805											   o_error_reported 	=> v_error_reported,
4806											   o_error_response 	=> c_error_response
4807											   );
4808				/*WHEN v_object_type = k_season_object_type THEN
4809					FindOfferItemBySeriesModify(i_series_id 	=> v_object_id,
4810												o_offer_item_ids=> c_offer_items,
4811												o_properties 	=> c_properties,
4812												o_error_reported=> v_error_reported,
4813												o_error_response=> c_error_response
4814												);*/
4815				/*WHEN v_object_type = k_series_object_type THEN
4816					FindOfferItemBySeriesModify(i_series_id 	=> v_object_id,
4817												o_offer_item_ids=> c_offer_items,
4818												o_properties 	=> c_properties,
4819												o_error_reported=> v_error_reported,
4820												o_error_response=> c_error_response
4821												);*/
4822				WHEN v_object_type = k_version_object_type THEN
4823					--create or insert
4824					IF v_change_type IN (k_create, k_insert) THEN
4825						FindOfferItemByVersionModify(i_version_id 		=> v_object_id,
4826													 i_checkAsMain 		=> 0,
4827													 i_checkAsAssociated=> 1,
4828													 i_checkAsEmbbeded 	=> 0,
4829													 i_changeType 		=> v_change_type,
4830													 i_interface_id 	=> v_interface_id,
4831													 o_offer_item_ids 	=> c_offer_items,
4832													 o_properties 		=> c_properties,
4833													 o_error_reported 	=> v_error_reported,
4834													 o_error_response 	=> c_error_response
4835													 );
4836					--update or modify
4837					ELSIF v_change_type IN (k_modify, k_update) THEN
4838						FindOfferItemByVersionModify(i_version_id 		=> v_object_id,
4839													 i_checkAsMain 		=> 1,
4840													 i_checkAsAssociated=> 1,
4841													 i_checkAsEmbbeded 	=> 1,
4842													 i_changeType 		=> v_change_type,
4843													 i_interface_id 	=> v_interface_id,
4844													 o_offer_item_ids 	=> c_offer_items,
4845													 o_properties 		=> c_properties,
4846													 o_error_reported 	=> v_error_reported,
4847													 o_error_response 	=> c_error_response
4848													 );
4849					ELSE
4850						--delete for version is not audited oracle trigger Omnibus is UPDATE only
4851						BEGIN
4852							SELECT	v.title_id
4853							INTO	v_title_id
4854							FROM	version v
4855							WHERE	v.version_id = v_object_id;
4856							FindOfferItemByVersionDelete(i_version_id 		=> v_object_id,
4857														 i_title_id 		=> v_title_id,
4858														 i_checkAsMain 		=> 1,
4859														 i_checkAsAssociated=> 1,
4860														 i_checkAsEmbbeded 	=> 1,
4861														 o_offer_item_ids 	=> c_offer_items,
4862														 o_properties 		=> c_properties,
4863														 o_error_reported 	=> v_error_reported,
4864														 o_error_response 	=> c_error_response
4865														 );
4866						EXCEPTION
4867							WHEN	NO_DATA_FOUND THEN	
4868									NULL;--no action needed
4869						END;
4870					END IF;
4871
4872				WHEN v_object_type = k_media_object_type THEN
4873
4874					FindOfferItemByMediaItem(i_media_item_id => v_object_id,
4875											 o_offer_item_ids=> c_offer_items,
4876											 o_properties 	 => c_properties,
4877											 o_error_reported=> v_error_reported,
4878											 o_error_response=> c_error_response
4879											 );			
4880				WHEN v_object_type = k_castMember_object_type THEN
4881					FindOfferItemByCastMember(i_cast_id 		=> v_object_id,
4882											  i_interface_id 	=> v_interface_id,
4883											  o_offer_item_ids 	=> c_offer_items,
4884											  o_properties 		=> c_properties,
4885											  o_error_reported 	=> v_error_reported,
4886											  o_error_response 	=> c_error_response
4887											  );
4888				WHEN v_object_type = k_media_match_object_type THEN
4889					FindOfferItemByMediaItem(i_media_item_id => v_object_id,
4890											 o_offer_item_ids=> c_offer_items,
4891											 o_properties 	 => c_properties,
4892											 o_error_reported=> v_error_reported,
4893											 o_error_response=> c_error_response
4894											 );
4895				WHEN v_object_type = k_show_object_type THEN
4896					FindOfferItemByShowModify(i_show_id 		=> v_object_id,
4897											  o_offer_item_ids 	=> c_offer_items,
4898											  o_properties 		=> c_properties,
4899											  o_error_reported 	=> v_error_reported,
4900											  o_error_response 	=> c_error_response
4901											  );
4902				WHEN v_object_type = k_season_object_type THEN
4903					FindOfferItemBySeason(i_season_id 		=> v_object_id,
4904										  o_offer_item_ids 	=> c_offer_items,
4905										  o_properties 		=> c_properties,
4906										  o_error_reported 	=> v_error_reported,
4907										  o_error_response 	=> c_error_response
4908										  );
4909				END CASE;					
4910				--process offer items
4911				--g.debug('bef open offer item cursor error ', v_error_reported);
4912				IF 	v_error_reported = k_false 
4913				AND	c_offer_items%ISOPEN THEN					
4914					g.debug('fetch offer items');
4915					LOOP
4916						--					
4917						FETCH c_offer_items BULK COLLECT INTO tbl_offer_rec;
4918						EXIT WHEN c_offer_items%NOTFOUND;
4919					END LOOP;
4920					CLOSE c_offer_items; 
4921						BEGIN
4922						FOR i IN tbl_offer_rec.first..tbl_offer_rec.last
4923						LOOP
4924						--g.debug('offer item id ',v_offer_id);
4925						-- Check if the Offer is Eligible for Export
4926						g.debug('offer item id', tbl_offer_rec(i).offer_item_id,  v_offer_name, 'status '||v_offer_status_isn);
4927						IF ibms_prop.get_property_number(i_property_code => v_elig_change_status_prop,
4928														 i_object_id => v_interface_id,
4929														 i_level_1_id => tbl_offer_rec(i).offer_status_isn) = k_true
4930						AND is_offer_valid(i_offer_item_id => tbl_offer_rec(i).offer_item_id) = k_true	--TT 412750001
4931						THEN
4932
4933							g.debug('add offer item');
4934							INSERT INTO glxmem_tmp_fk_joins
4935											(
4936											tmp_session_id, --temp_session_id
4937											numeric_fld1,--offer_item_id
4938											numeric_fld2,--offer id
4939											string_fld1, --offer_name
4940											string_fld2, --service_name
4941											string_fld3, --offer_status_descr
4942											string_fld4, --object_type
4943											object_id, 	 --object_id -- index is there for temp session id + object id
4944											string_fld5, --change_type
4945											string_fld6 -- changed_attributes
4946											)
4947											VALUES(v_temp_session_id,
4948												   tbl_offer_rec(i).offer_item_id,
4949												   tbl_offer_rec(i).offer_id,
4950												   tbl_offer_rec(i).offer_name,
4951												   tbl_offer_rec(i).service_name,
4952												   tbl_offer_rec(i).offer_status_desc,
4953												   v_object_type,
4954												   v_object_id,
4955												   v_change_type,
4956												   NVL(v_changed_attributs, k_space)
4957											);
4958
4959							COMMIT;
4960							--
4961						END IF;
4962						--
4963					END LOOP;--offer item loop
4964						EXCEPTION WHEN ROWTYPE_MISMATCH THEN
4965						NULL;
4966						END;
4967				ELSE
4968					--cummulate errors per object SP call
4969					mergeErrors(
4970							i_error_response => c_error_response
4971								);
4972				END IF; --get offer item error check
4973				--				
4974				IF c_error_response%ISOPEN THEN
4975					CLOSE c_error_response;
4976				END IF;
4977				--
4978				IF c_properties%ISOPEN THEN
4979					CLOSE c_properties;
4980				END IF;
4981			--if object is changed again later - possibility is that new columns would have been update
4982			--e.g. for title 1 at 10am program name changed, 11am broadcaster reference.
4983			--below lofic will add broadcaster reference to program name.
4984			ELSIF tbl_object_processed.EXISTS(v_object_type||TO_CHAR(v_object_id,'099999999'))
4985			AND	(v_changed_attributs IS NOT NULL
4986			AND	tbl_audit_rec(j).changetype NOT IN (k_modify, k_update))
4987			THEN
4988				--possible that duplicate names can be added 
4989				UPDATE	glxmem_tmp_fk_joins tmp
4990				SET		tmp.string_fld6 = SUBSTR(REPLACE(tmp.string_fld6 || k_comma || v_changed_attributs, k_comma||k_comma, k_comma), 1, 4000)
4991				WHERE	tmp.tmp_session_id = v_temp_session_id
4992					AND	tmp.object_id 	= v_object_id
4993					AND	tmp.string_fld5 = v_object_type;
4994				COMMIT;
4995			END IF;--audit record is Create/Delete and updates
4996		END LOOP; --audit items
4997		EXCEPTION WHEN ROWTYPE_MISMATCH THEN
4998		NULL;
4999		END;
5000		g.debug('open ref cursors');
5001		--open offer items ref cursor
5002		OPEN o_offer_items FOR
5003			SELECT 	numeric_fld1 AS offer_item_id
5004			FROM 	glxmem_tmp_fk_joins
5005			WHERE	tmp_session_id = v_temp_session_id;
5006		--open data change log ref cursor 
5007		OPEN o_data_changes_log FOR
5008			SELECT DISTINCT
5009					numeric_fld1 	AS 	offer_item_id,
5010					string_fld1  	AS 	offer_name,
5011					string_fld2		AS 	service_name,
5012					string_fld3		AS 	offer_status_desc,
5013					string_fld4		AS	object_type,
5014					object_id		AS	object_id,
5015					numeric_fld2	AS	offer_id,
5016					string_fld5		AS	change_type,
5017					string_fld6		AS	changed_attributes
5018			FROM glxmem_tmp_fk_joins
5019			WHERE	tmp_session_id = v_temp_session_id;
5020
5021		DELETE FROM glxmem_tmp_fk_joins
5022		WHERE tmp_session_id = v_temp_session_id;
5023		--
5024		COMMIT;		
5025		--get cummulative errors for each SP called above
5026		o_error_response := error_handling_api.GetErrorRefCursor;
5027		--for now there is no properties - so return empty
5028		o_properties 	 := error_handling_api.GetEmptyRefCursor();
5029		o_error_reported := v_error_reported;
5030	EXCEPTION
5031		WHEN 	OTHERS THEN
5032				--
5033				o_error_reported := k_true;
5034				IF NOT(o_data_changes_log%ISOPEN) THEN
5035					o_data_changes_log := error_handling_api.GetEmptyRefCursor();
5036				END IF;
5037				--
5038				IF NOT(o_offer_items%ISOPEN) THEN
5039					o_offer_items := error_handling_api.GetEmptyRefCursor();
5040				END IF;
5041				--
5042				IF NOT(o_error_response%ISOPEN) THEN
5043					o_error_response := error_handling_api.GetErrorRefCursor();
5044				END IF;
5045				--
5046				IF NOT(o_properties%ISOPEN) THEN
5047					o_properties := error_handling_api.GetEmptyRefCursor();
5048				END IF;				
5049				--
5050				IF c_error_response%ISOPEN THEN
5051					CLOSE c_error_response;
5052				END IF;
5053				--
5054				IF c_properties%ISOPEN THEN
5055					CLOSE c_properties;
5056				END IF;
5057	END getOfferItemsForTable;
5058	
5059	--------------------------------------
5060	-- Procedure: is_offer_valid
5061	-- Usage: Will be used by 719 Interface Properties, to filter the Offer Items by Service
5062	--------------------------------------
5063	
5064	FUNCTION is_offer_valid(i_offer_item_id	IN vod_item_schedule.item_schedule_id%TYPE)RETURN NUMBER IS
5065		is_offer_valid NUMBER(1) := k_false;
5066		
5067	BEGIN
5068	
5069		g_interface_id := iface.interface_id(i_interface_type => k_719_interface_type);
5070	
5071		SELECT k_true
5072		INTO is_offer_valid
5073		FROM vod_offer_schedule vos		
5074		JOIN vod_offer_schedule_item vosi ON vosi.offer_id = vos.offer_id
5075		JOIN vod_item_schedule vis ON vosi.item_schedule_id = vis.item_schedule_id
5076		JOIN purchase p ON p.purchase_id = vis.purchase_id
5077		JOIN title t ON t.title_id = p.title_id
5078		JOIN table_line vodofr ON (vos.status = vodofr.table_reference AND vodofr.table_id = k_vodofr_tl)
5079		JOIN table_line ttltyp ON (t.title_type = ttltyp.table_code AND ttltyp.table_id = k_title_type_table_id)
5080		WHERE vis.item_schedule_id= i_offer_item_id
5081		AND	ibms_prop.get_property_number(k_719_ftch_frm_service_prop, g_interface_id, vis.platform_id) = k_true;
5082		--AND ibms_prop.get_property_number(k_719_elig_sts_changes_prop, g_interface_id, vodofr.table_line_isn) = k_true
5083		--AND ibms_prop.get_property_number(k_719_title_version_ut_prop, g_interface_id, ttltyp.table_line_isn) = k_false;
5084		--filter the Title Types that are NOT selected in 719 Title/Version Mod for TitleTyp Prop
5085		g.debug('Valid Offer Item ID:'||i_offer_item_id);
5086		RETURN is_offer_valid;
5087		
5088	EXCEPTION
5089	
5090		WHEN no_data_found THEN
5091		
5092			g.debug('Invalid Offer Item ID:'||i_offer_item_id);
5093			RETURN k_false;
5094		
5095		WHEN OTHERS THEN
5096		
5097			g.debug('Invalid Offer Item ID:'||i_offer_item_id);
5098			RETURN k_false;
5099	
5100	END is_offer_valid;
5101	
5102	BEGIN
5103	g_delete_gtt_table := NULL;
5104END ibms_audit_api;
5105/