[Home] [Help]
PACKAGE BODY: APPS.BIS_OBJ_REFRESH_HISTORY_PKG
Source
1 package body BIS_OBJ_REFRESH_HISTORY_PKG AS
2 /*$Header: BISOBTHB.pls 120.1 2006/05/18 12:29:50 aguwalan noship $*/
3 PROCEDURE Insert_Row ( p_Prog_request_id NUMBER ,
4 p_Object_type VARCHAR2,
5 p_Object_name VARCHAR2,
6 p_Refresh_type VARCHAR2,
7 p_Object_row_count NUMBER,
8 p_Object_space_usage NUMBER ,
9 p_Tablespace_name VARCHAR2,
10 p_Free_tablespace_size VARCHAR2,
11 p_Creation_date DATE,
12 p_Created_by NUMBER,
13 p_Last_update_date DATE,
14 p_Last_updated_by NUMBER
15 )
16 is
17 CURSOR C_check IS SELECT 1 from BIS_OBJ_REFRESH_HISTORY
18 where Prog_request_id =p_Prog_request_id and
19 OBJECT_TYPE =p_Object_type and
20 OBJECT_NAME =p_Object_name ;
21
22 c_check_rec C_check%rowtype;
23
24 BEGIN
25 --for bug 4174608
26 for c_check_rec in C_check loop
27 BIS_COLLECTION_UTILITIES.put_line('Following record already exist in BIS_OBJ_REFRESH_HISTORY ');
28 BIS_COLLECTION_UTILITIES.put_line('Prog_request_id '||p_Prog_request_id ||' OBJECT_TYPE '|| p_Object_type||' OBJECT_NAME '||p_Object_name);
29 return;
30 end loop;
31 insert into BIS_OBJ_REFRESH_HISTORY
32 (Prog_request_id ,
33 Object_type ,
34 Object_name ,
35 Refresh_type ,
36 Object_row_count ,
37 Object_space_usage,
38 Tablespace_name ,
39 Free_tablespace_size,
40 Creation_date ,
41 Created_by ,
42 Last_update_date ,
43 Last_updated_by )
44 values
45 (p_Prog_request_id ,
46 p_Object_type ,
47 p_Object_name ,
48 p_Refresh_type ,
49 p_Object_row_count ,
50 p_Object_space_usage,
51 p_Tablespace_name ,
52 p_Free_tablespace_size,
53 p_Creation_date ,
54 p_Created_by ,
55 p_Last_update_date ,
56 p_Last_updated_by
57 );
58
59
60 commit;
61 EXCEPTION
62 when others then
63 BIS_COLLECTION_UTILITIES.put_line('Prog_request_id ' || p_Prog_request_id);
64 BIS_COLLECTION_UTILITIES.put_line('Object_type '|| p_Object_type);
65 BIS_COLLECTION_UTILITIES.put_line('Object_name '||p_Object_name);
66 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_OBJ_REFRESH_HISTORY_PKG.Insert_Row ' || sqlerrm);
67 raise;
68 END;
69
70
71 FUNCTION Update_Row ( p_Prog_request_id NUMBER,
72 p_new_Prog_request_id NUMBER DEFAULT NULL,
73 p_Object_type VARCHAR2 DEFAULT NULL,
74 p_Object_name VARCHAR2 DEFAULT NULL,
75 p_Refresh_type VARCHAR2 DEFAULT NULL,
76 p_Object_row_count NUMBER DEFAULT NULL,
77 p_Object_space_usage NUMBER DEFAULT NULL,
78 p_Tablespace_name VARCHAR2 DEFAULT NULL,
79 p_Free_tablespace_size VARCHAR2 DEFAULT NULL,
80 p_Last_update_date DATE,
81 p_Last_updated_by NUMBER) RETURN BOOLEAN
82 IS
83
84 setClause varchar2(1024):=null;
85 stmt varchar2(2048):=null;
86
87 BEGIN
88
89 if(p_Prog_request_id is null or p_Last_update_date is null or p_Last_updated_by is null
90 or p_Object_type is null or p_Object_name is null) THEN
91 return FALSE;
92 END iF;
93 /* Object Type and Object Name should not be updateable; they form the primary key
94 if(p_Object_type is not null) then
95 setClause :=setClause || 'Object_type = ''' || p_Object_type ||''', ';
96 end if;
97
98 if(p_Object_name is not null) then
99 setClause :=setClause || 'Object_name = ''' || p_Object_name || ''', ' ;
100 end if;
101 */
102 if(p_Refresh_type is not null) then
103 setClause :=setClause || 'Refresh_type = ''' || p_Refresh_type || ''', ' ;
104 end if;
105
106 if(p_Object_row_count is not null) then
107 setClause :=setClause || 'Object_row_count = ' || p_Object_row_count || ', ' ;
108 end if;
109
110 if(p_Object_space_usage is not null) then
111 setClause :=setClause || 'Object_space_usage = ' || p_Object_space_usage || ', ' ;
112 end if;
113
114 if(p_Tablespace_name is not null) then
115 setClause :=setClause || 'Tablespace_name = ''' || p_Tablespace_name || ''', ' ;
116 end if;
117
118 if(p_Free_tablespace_size is not null) then
119 setClause :=setClause || 'Free_tablespace_size = ''' || p_Free_tablespace_size || ''', ' ;
120 end if;
121
122 if(p_new_Prog_request_id is not null) then
123 setClause :=setClause || 'Prog_request_id = ' || p_new_Prog_request_id || ', ' ;
124 end if;
125
126
127 if setClause is null then
128 return false;
129 end if;
130
131 setClause :=setClause || 'Last_update_date = ''' || p_Last_update_date || ''', ' ;
132 setClause :=setClause || 'Last_updated_by= ' || p_Last_updated_by ;
133
134 stmt := stmt || 'update BIS_OBJ_REFRESH_HISTORY set ' || setClause ;
135 stmt := stmt || ' where Prog_request_id = ' || p_Prog_request_id ;
136 stmt := stmt || ' and Object_type = ''' || p_Object_type ||''' ';
137 stmt := stmt || ' and Object_name = ''' || p_Object_name ||''' ' ;
138
139 execute immediate stmt;
140 commit;
141 RETURN TRUE;
142
143 EXCEPTION
144 when others then
145 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_OBJ_REFRESH_HISTORY_PKG.Update_Row ' || sqlerrm);
146 raise;
147
148 END;
149
150 PROCEDURE Delete_Row (p_prog_req_id number)
151 IS
152 BEGIN
153 DELETE FROM BIS_OBJ_REFRESH_HISTORY
154 WHERE PROG_REQUEST_ID = p_prog_req_id;
155
156 EXCEPTION
157 when others then
158 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BIS_OBJ_REFRESH_HISTORY_PKG.Delete_Row ' || sqlerrm);
159 raise;
160 END;
161
162
163 END BIS_OBJ_REFRESH_HISTORY_PKG;