[Home] [Help]
PACKAGE BODY: APPS.JTF_STATE_RULES_PKG
Source
1 PACKAGE BODY jtf_state_rules_pkg AS
2 /* $Header: jtftksrb.pls 115.16 2002/12/04 22:14:34 cjang ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_rule_id IN NUMBER,
6 x_state_type IN VARCHAR2,
7 x_attribute1 IN VARCHAR2,
8 x_attribute2 IN VARCHAR2,
9 x_attribute3 IN VARCHAR2,
10 x_attribute4 IN VARCHAR2,
11 x_attribute5 IN VARCHAR2,
12 x_attribute6 IN VARCHAR2,
13 x_attribute7 IN VARCHAR2,
14 x_attribute8 IN VARCHAR2,
15 x_attribute9 IN VARCHAR2,
16 x_attribute10 IN VARCHAR2,
17 x_attribute11 IN VARCHAR2,
18 x_attribute12 IN VARCHAR2,
19 x_attribute13 IN VARCHAR2,
20 x_attribute14 IN VARCHAR2,
21 x_attribute15 IN VARCHAR2,
22 x_attribute_category IN VARCHAR2,
23 x_rule_name IN VARCHAR2,
24 x_creation_date IN DATE,
25 x_created_by IN NUMBER,
26 x_last_update_date IN DATE,
27 x_last_updated_by IN NUMBER,
28 x_last_update_login IN NUMBER,
29 x_application_id IN NUMBER
30 )
31 IS
32 CURSOR c
33 IS
34 SELECT ROWID
35 FROM jtf_state_rules_b
36 WHERE rule_id = x_rule_id;
37 BEGIN
38 INSERT INTO jtf_state_rules_b (
39 rule_id,
40 state_type,
41 attribute1,
42 attribute2,
43 attribute3,
44 attribute4,
45 attribute5,
46 attribute6,
47 attribute7,
48 attribute8,
49 attribute9,
50 attribute10,
51 attribute11,
52 attribute12,
53 attribute13,
54 attribute14,
55 attribute15,
56 attribute_category,
57 creation_date,
58 created_by,
59 last_update_date,
60 last_updated_by,
61 last_update_login,
62 object_version_number,
63 application_id
64 )
65 VALUES (
66 x_rule_id,
67 x_state_type,
68 x_attribute1,
69 x_attribute2,
70 x_attribute3,
71 x_attribute4,
72 x_attribute5,
73 x_attribute6,
74 x_attribute7,
75 x_attribute8,
76 x_attribute9,
77 x_attribute10,
78 x_attribute11,
79 x_attribute12,
80 x_attribute13,
81 x_attribute14,
82 x_attribute15,
83 x_attribute_category,
84 x_creation_date,
85 x_created_by,
86 x_last_update_date,
87 x_last_updated_by,
88 x_last_update_login,
89 1,
90 x_application_id
91 );
92 INSERT INTO jtf_state_rules_tl
93 (rule_id,
94 rule_name,
95 created_by,
96 creation_date,
97 last_updated_by,
98 last_update_date,
99 last_update_login,
100 language,
101 source_lang
102 )
103 SELECT x_rule_id,
104 x_rule_name,
105 x_created_by,
106 x_creation_date,
107 x_last_updated_by,
108 x_last_update_date,
109 x_last_update_login,
110 l.language_code,
111 USERENV ('LANG')
112 FROM fnd_languages l
113 WHERE l.installed_flag IN ('I', 'B')
114 AND NOT EXISTS (SELECT NULL
115 FROM jtf_state_rules_tl t
116 WHERE t.rule_id = x_rule_id
117 AND t.language = l.language_code);
118 OPEN c;
119 FETCH c INTO x_rowid;
120
121 IF (c%NOTFOUND)
122 THEN
123 CLOSE c;
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127 CLOSE c;
128 END insert_row;
129
130 PROCEDURE lock_row (x_rule_id IN NUMBER, x_object_version_number IN NUMBER)
131 IS
132 CURSOR c
133 IS
134 SELECT object_version_number
135 FROM jtf_state_rules_vl
136 WHERE rule_id = x_rule_id
137 FOR UPDATE OF rule_id NOWAIT;
138
139 recinfo c%ROWTYPE;
140 BEGIN
141 OPEN c;
142 FETCH c INTO recinfo;
143
144 IF (c%NOTFOUND)
145 THEN
146 CLOSE c;
147 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148 app_exception.raise_exception;
149 END IF;
150
151 CLOSE c;
152
153 IF (recinfo.object_version_number = x_object_version_number)
154 THEN
155 NULL;
156 ELSE
157 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
158 app_exception.raise_exception;
159 END IF;
160 END lock_row;
161
162 PROCEDURE update_row (
163 x_rule_id IN NUMBER,
164 x_object_version_number IN NUMBER,
165 x_state_type IN VARCHAR2,
166 x_attribute1 IN VARCHAR2,
167 x_attribute2 IN VARCHAR2,
168 x_attribute3 IN VARCHAR2,
169 x_attribute4 IN VARCHAR2,
170 x_attribute5 IN VARCHAR2,
171 x_attribute6 IN VARCHAR2,
172 x_attribute7 IN VARCHAR2,
173 x_attribute8 IN VARCHAR2,
174 x_attribute9 IN VARCHAR2,
175 x_attribute10 IN VARCHAR2,
176 x_attribute11 IN VARCHAR2,
177 x_attribute12 IN VARCHAR2,
178 x_attribute13 IN VARCHAR2,
179 x_attribute14 IN VARCHAR2,
180 x_attribute15 IN VARCHAR2,
181 x_attribute_category IN VARCHAR2,
182 x_rule_name IN VARCHAR2,
183 x_last_update_date IN DATE,
184 x_last_updated_by IN NUMBER,
185 x_last_update_login IN NUMBER,
186 x_application_id IN NUMBER
187 )
188 IS
189 BEGIN
190 UPDATE jtf_state_rules_b
191 SET state_type = x_state_type,
192 object_version_number = x_object_version_number + 1,
193 attribute1 = x_attribute1,
194 attribute2 = x_attribute2,
195 attribute3 = x_attribute3,
196 attribute4 = x_attribute4,
197 attribute5 = x_attribute5,
198 attribute6 = x_attribute6,
199 attribute7 = x_attribute7,
200 attribute8 = x_attribute8,
201 attribute9 = x_attribute9,
202 attribute10 = x_attribute10,
203 attribute11 = x_attribute11,
204 attribute12 = x_attribute12,
205 attribute13 = x_attribute13,
206 attribute14 = x_attribute14,
207 attribute15 = x_attribute15,
208 attribute_category = x_attribute_category,
209 last_update_date = x_last_update_date,
210 last_updated_by = x_last_updated_by,
211 last_update_login = x_last_update_login,
212 application_id = x_application_id
213 WHERE rule_id = x_rule_id;
214
215 IF (SQL%NOTFOUND)
216 THEN
217 RAISE NO_DATA_FOUND;
218 END IF;
219
220 UPDATE jtf_state_rules_tl
221 SET rule_name = x_rule_name,
222 last_update_date = x_last_update_date,
223 last_updated_by = x_last_updated_by,
224 last_update_login = x_last_update_login,
225 source_lang = USERENV ('LANG')
226 WHERE rule_id = x_rule_id
227 AND USERENV ('LANG') IN (language, source_lang);
228
229 IF (SQL%NOTFOUND)
230 THEN
231 RAISE NO_DATA_FOUND;
232 END IF;
233 END update_row;
234
235 PROCEDURE delete_row (x_rule_id IN NUMBER)
236 IS
237 BEGIN
238 DELETE
239 FROM jtf_state_rules_tl
240 WHERE rule_id = x_rule_id;
241
242 IF (SQL%NOTFOUND)
243 THEN
244 RAISE NO_DATA_FOUND;
245 END IF;
246
247 DELETE
248 FROM jtf_state_rules_b
249 WHERE rule_id = x_rule_id;
250
251 IF (SQL%NOTFOUND)
252 THEN
253 RAISE NO_DATA_FOUND;
254 END IF;
255 END delete_row;
256
257 PROCEDURE add_language
258 IS
259 BEGIN
260 DELETE
261 FROM jtf_state_rules_tl t
262 WHERE NOT EXISTS (SELECT NULL
263 FROM jtf_state_rules_b b
264 WHERE b.rule_id = t.rule_id);
265 UPDATE jtf_state_rules_tl t
266 SET (rule_name) = ( SELECT b.rule_name
267 FROM jtf_state_rules_tl b
268 WHERE b.rule_id = t.rule_id
269 AND b.language = t.source_lang)
270 WHERE (t.rule_id, t.language) IN
271 (SELECT subt.rule_id, subt.language
272 FROM jtf_state_rules_tl subb, jtf_state_rules_tl subt
273 WHERE subb.rule_id = subt.rule_id
274 AND subb.language = subt.source_lang
275 AND (subb.rule_name <> subt.rule_name));
276 INSERT INTO jtf_state_rules_tl
277 (rule_id,
278 rule_name,
279 created_by,
280 creation_date,
281 last_updated_by,
282 last_update_date,
283 last_update_login,
284 language,
285 source_lang
286 )
287 SELECT b.rule_id,
288 b.rule_name,
289 b.created_by,
290 b.creation_date,
291 b.last_updated_by,
292 b.last_update_date,
293 b.last_update_login,
294 l.language_code,
295 b.source_lang
296 FROM jtf_state_rules_tl b, fnd_languages l
297 WHERE l.installed_flag IN ('I', 'B')
298 AND b.language = USERENV ('LANG')
299 AND NOT EXISTS (SELECT NULL
300 FROM jtf_state_rules_tl t
301 WHERE t.rule_id = b.rule_id
302 AND t.language = l.language_code);
303 END add_language;
304
305 PROCEDURE translate_row (
306 x_rule_id IN NUMBER,
307 x_rule_name IN VARCHAR2,
308 x_owner IN VARCHAR2
309 )
310 IS
311 l_user_id NUMBER := 0;
312 BEGIN
313 IF x_owner = 'SEED'
314 THEN
315 l_user_id := 1;
316 END IF;
317
318 UPDATE jtf_state_rules_tl
319 SET rule_name = NVL (x_rule_name, rule_name),
320 last_update_date = SYSDATE,
321 last_update_login = 0,
322 source_lang = USERENV ('LANG'),
323 last_updated_by = l_user_id
324 WHERE rule_id = x_rule_id
325 AND USERENV ('LANG') IN (language, source_lang);
326
327 IF (SQL%NOTFOUND)
328 THEN
329 RAISE NO_DATA_FOUND;
330 END IF;
331 END translate_row;
332 END jtf_state_rules_pkg;