[Home] [Help]
PACKAGE BODY: APPS.CS_QKMENU_DML_PKG
Source
1 PACKAGE BODY CS_QKMENU_DML_PKG AS
2 /* $Header: csqkmenb.pls 115.7 2002/12/17 05:10:25 bhroy ship $ */
3 -- 12-17-2002 bhroy Removed default NULL, added WHENEVER OSERROR EXIT FAILURE ROLLBACK
4
5 PROCEDURE SAVE_FOLDER_INTO_DB(l_user_id IN NUMBER,
6 l_description IN VARCHAR2,
7 l_folder_name IN VARCHAR2, /*save as name*/
8 l_default_flag IN VARCHAR2)
9 IS
10
11 record_exist NUMBER := 0;
12 next_sequence NUMBER := 0;
13 current_view_folder_val VARCHAR2(30);
14 col_value VARCHAR2(30);
15
16 CURSOR function_rec IS
17 SELECT function_id, filter_id, function_filter_id
18 FROM cs_qm_function_filters
19 WHERE active_flag = 'Y';
20
21
22
23 BEGIN
24
25
26 -- find out if the folder exists, if so, update it, if not, insert it
27 SELECT NVL(COUNT(*), 0)
28 INTO record_exist
29 FROM cs_qm_user_folders
30 WHERE folder_name = l_folder_name
31 AND user_id = l_user_id;
32
33 SELECT CS_QM_USER_FOLDERS_S.NextVal
34 INTO next_sequence
35 FROM dual;
36
37 IF (record_exist > 0) THEN
38 UPDATE cs_qm_user_folders
39 SET description = l_description
40 WHERE folder_name = l_folder_name
41 AND user_id = l_user_id;
42 COMMIT;
43 ELSE
44 INSERT INTO cs_qm_user_folders
45 (user_folder_id, last_update_date, last_updated_by, creation_date,
46 created_by, user_id, folder_name, description, default_flag,
47 active_flag, object_version_number)
48 VALUES
49 (next_sequence, sysdate, l_user_id, sysdate, l_user_id,
50 l_user_id, l_folder_name,
51 l_description, 'N', 'Y', 1);
52 COMMIT;
53 END IF;
54
55
56 -- find out if the checkbox is checked, if not, do nothing, if so
57 -- clean up the older default_flag and set the new default_flag
58 IF (l_default_flag = 'N') THEN
59 RETURN;
60 ELSE
61 UPDATE cs_qm_user_folders
62 SET default_flag = 'N'
63 WHERE default_flag = 'Y';
64 COMMIT;
65
66 IF (record_exist > 0) THEN
67 UPDATE cs_qm_user_folders
68 SET default_flag = 'Y'
69 WHERE folder_name = l_folder_name
70 AND user_id = l_user_id;
71 COMMIT;
72 ELSE
73 UPDATE cs_qm_user_folders
74 SET default_flag = 'Y'
75 WHERE user_folder_id = next_sequence
76 AND user_id = l_user_id;
77 COMMIT;
78 END IF;
79
80 END IF;
81
82 END SAVE_FOLDER_INTO_DB;
83
84 PROCEDURE insert_holder(l_user_folder_id NUMBER,
85 l_function_filter_id NUMBER,
86 l_user_id NUMBER,
87 l_filter_value VARCHAR2,
88 l_filter_value_id NUMBER)
89 IS
90
91 BEGIN
92
93 INSERT INTO cs_qm_folder_filters
94 (folder_filter_id, user_folder_id, function_filter_id,
95 filter_operator, filter_value, filter_value_id,
96 last_update_date, last_updated_by, last_update_login,
97 creation_date, created_by, object_version_number)
98 VALUES
99 (cs_qm_folder_filters_s.nextVal, l_user_folder_id, l_function_filter_id,
100 '=', l_filter_value, l_filter_value_id,
101 sysdate, l_user_id, l_user_id, sysdate, l_user_id, 1);
102 COMMIT;
103
104 END insert_holder;
105
106
107 PROCEDURE update_holder(l_user_folder_id NUMBER,
108 l_function_filter_id NUMBER,
109 l_user_id NUMBER,
110 l_filter_value VARCHAR2,
111 l_filter_value_id NUMBER
112 )
113 IS
114
115 BEGIN
116
117 UPDATE cs_qm_folder_filters
118 SET filter_value = l_filter_value,
119 filter_value_id = l_filter_value_id
120 WHERE user_folder_id = l_user_folder_id
121 AND function_filter_id = l_function_filter_id;
122 COMMIT;
123
124 END update_holder;
125
126
127 PROCEDURE insert_empty_folder(l_user_id NUMBER,
128 l_folder_name VARCHAR2) IS
129
130 l_user_folder_id NUMBER;
131
132 CURSOR filter_rec IS
133 SELECT function_filter_id
134 FROM cs_qm_function_filters;
135
136
137 BEGIN
138
139 SELECT user_folder_id
140 INTO l_user_folder_id
141 FROM cs_qm_user_folders
142 WHERE user_id = l_user_id
143 AND folder_name = l_folder_name;
144
145 FOR filter_item in filter_rec LOOP
146
147 INSERT INTO cs_qm_folder_filters
148 (folder_filter_id, user_folder_id, function_filter_id, filter_operator,
149 filter_value,
150 last_update_date, last_updated_by, last_update_login,
151 creation_date, created_by, object_version_number)
152 VALUES
153 (cs_qm_folder_filters_s.nextVal, l_user_folder_id,
154 filter_item.function_filter_id, '=', '',
155 sysdate, l_user_id, l_user_id, sysdate, l_user_id, 1);
156 COMMIT;
157
158 END LOOP;
159
160
161 END insert_empty_folder;
162
163
164 PROCEDURE new_folder(l_user_id NUMBER,
165 folder_name VARCHAR2,
166 description VARCHAR2) IS
167
168 next_sequence NUMBER;
169
170 BEGIN
171
172 SELECT CS_QM_USER_FOLDERS_s.NextVal
173 INTO next_sequence
174 FROM dual;
175
176 INSERT INTO cs_qm_user_folders
177 (user_folder_id, last_update_date, last_updated_by, creation_date,
178 created_by, user_id, folder_name, description, default_flag,
179 active_flag, object_version_number)
180 VALUES
181 (next_sequence, sysdate, l_user_id, sysdate, l_user_id,
182 l_user_id, folder_name,
183 description, 'N', 'Y', 1);
184 COMMIT;
185 END new_folder;
186
187 PROCEDURE update_default_flag(l_user_id NUMBER,
188 l_folder_name VARCHAR2,
189 l_default_flag VARCHAR2) IS
190
191 record_exist NUMBER;
192
193 BEGIN
194
195 IF (l_default_flag = 'N') THEN
196 RETURN;
197 ELSE
198 UPDATE cs_qm_user_folders
199 SET default_flag = 'N'
200 WHERE default_flag = 'Y'
201 AND user_id = l_user_id;
202 COMMIT;
203
204 UPDATE cs_qm_user_folders
205 SET default_flag = 'Y'
206 WHERE folder_name = l_folder_name
207 AND user_id = l_user_id;
208 COMMIT;
209 END IF;
210
211
212 END update_default_flag;
213
214
215
216
217
218 END CS_QKMENU_DML_PKG;