PACKAGE BODY: APPS.MSD_DEM_UPDATE_LEVEL_CODES

Source

1 PACKAGE BODY MSD_DEM_UPDATE_LEVEL_CODES AS
2 /* $Header: msddemupdlvb.pls 120.0.12000000.2 2007/09/24 11:48:08 nallkuma noship $ */
3 
4 
5 procedure update_code(errbuf              OUT NOCOPY VARCHAR2,
6                       retcode             OUT NOCOPY VARCHAR2,
7                       p_instance_id        IN  NUMBER,
8                       p_level              IN VARCHAR2,
9                       p_dest_table_name		 IN VARCHAR2,
10                       p_dest_column_name   IN VARCHAR2,
11                       p_src_coulmn_name    IN VARCHAR2)
12 
13 as
14 
15 l_stmt varchar2(4000);
16 
17 begin
18 
19 		msd_dem_common_utilities.log_message('Updating Memeber Codes in Sales Data for ' || p_level );
20 		msd_dem_common_utilities.log_debug('Updating Memeber Codes in Sales Data' || p_level );
21 
22 		l_stmt := null;
23 
24 		dbms_output.put_line(p_dest_table_name || p_dest_column_name || p_src_coulmn_name || p_instance_id);
25 
26 		if p_level = 'SITE' then
27 
28 			msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_LEVEL_UPDATE', p_instance_id);
29 
30 			l_stmt := replace(l_stmt, 'DEST_TABLE', p_dest_table_name);
31 			l_stmt := replace(l_stmt, 'DEST_COLUMN', p_dest_column_name);
32 			l_stmt := replace(l_stmt, 'SRC_COLUMN', p_src_coulmn_name);
33 
34 		end if;
35 
36 		msd_dem_common_utilities.log_debug('Executed Statement: ');
37 		msd_dem_common_utilities.log_debug(l_stmt);
38 
39 		msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
40 		execute immediate l_stmt using p_instance_id;
41 		msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
42 
43 		commit;
44 
45 		retcode := 0;
46 
47 		exception
48 			when others then
49 				errbuf  := substr(SQLERRM,1,150);
50 				msd_dem_common_utilities.log_message(errbuf);
51 				msd_dem_common_utilities.log_debug(errbuf);
52 				retcode := -1;
53 
54 end;
55 
56 END MSD_DEM_UPDATE_LEVEL_CODES;
57