[Home] [Help]
PACKAGE BODY: APPS.GCS_ELIM_RULES_PKG
Source
1 PACKAGE BODY GCS_ELIM_RULES_PKG AS
2 /* $Header: gcselimrulesb.pls 120.1 2005/10/30 05:17:56 appldev noship $ */
3
4 PROCEDURE Insert_Row
5 (
6 row_id IN OUT NOCOPY VARCHAR2,
7 RULE_ID NUMBER,
8 SEEDED_RULE_FLAG VARCHAR2,
9 TRANSACTION_TYPE_CODE VARCHAR2,
10 RULE_TYPE_CODE VARCHAR2,
11 FROM_TREATMENT_ID NUMBER,
12 TO_TREATMENT_ID NUMBER,
13 ENABLED_FLAG VARCHAR2,
14 OBJECT_VERSION_NUMBER NUMBER,
15 LAST_UPDATE_DATE DATE,
16 LAST_UPDATED_BY NUMBER,
17 CREATION_DATE DATE,
18 CREATED_BY NUMBER,
19 LAST_UPDATE_LOGIN NUMBER,
20 RULE_NAME VARCHAR2,
21 DESCRIPTION VARCHAR2
22 ) IS
23
24 CURSOR elimrules_row IS
25 SELECT rowid
26 FROM gcs_elim_rules_b cb
27 WHERE cb.RULE_ID= insert_row.RULE_ID;
28 BEGIN
29 IF RULE_ID IS NULL THEN
30 raise no_data_found;
31 END IF;
32
33 INSERT INTO gcs_elim_rules_b
34 (
35 RULE_ID,
36 SEEDED_RULE_FLAG,
37 TRANSACTION_TYPE_CODE,
38 RULE_TYPE_CODE,
39 FROM_TREATMENT_ID,
40 TO_TREATMENT_ID,
41 ENABLED_FLAG,
42 OBJECT_VERSION_NUMBER,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_LOGIN
48 )
49 SELECT
50 RULE_ID,
51 SEEDED_RULE_FLAG,
52 TRANSACTION_TYPE_CODE,
53 RULE_TYPE_CODE,
54 FROM_TREATMENT_ID,
55 TO_TREATMENT_ID,
56 ENABLED_FLAG,
57 OBJECT_VERSION_NUMBER,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_LOGIN
63
64 FROM dual
65 WHERE NOT EXISTS
66 (SELECT 1
67 FROM gcs_elim_rules_b cb
68 WHERE cb.RULE_ID= insert_row.RULE_ID);
69
70 INSERT INTO gcs_elim_rules_tl
71 (
72 RULE_ID,
73 LANGUAGE,
74 SOURCE_LANG,
75 RULE_NAME,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 CREATION_DATE,
79 CREATED_BY,
80 LAST_UPDATE_LOGIN,
81 DESCRIPTION
82 )
83 SELECT
84 RULE_ID,
85 userenv('LANG'),
86 userenv('LANG'),
87 RULE_NAME,
88 last_update_date,
89 last_updated_by,
90 creation_date,
91 created_by,
92 last_update_login,
93 DESCRIPTION
94
95
96 FROM dual
97 WHERE NOT EXISTS
98 (SELECT 1
99 FROM gcs_elim_rules_tl ctl
100 WHERE ctl.RULE_ID = insert_row.RULE_ID
101 AND ctl.language = userenv('LANG'));
102
103 OPEN elimrules_row;
104 FETCH elimrules_row INTO row_id;
105 IF elimrules_row%NOTFOUND THEN
106 CLOSE elimrules_row;
107 raise no_data_found;
108 END IF;
109 CLOSE elimrules_row;
110
111 END Insert_Row;
112
113
114
115 PROCEDURE Update_Row
116 (
117 row_id IN OUT NOCOPY VARCHAR2,
118 RULE_ID NUMBER,
119 SEEDED_RULE_FLAG VARCHAR2,
120 TRANSACTION_TYPE_CODE VARCHAR2,
121 RULE_TYPE_CODE VARCHAR2,
122 FROM_TREATMENT_ID NUMBER,
123 TO_TREATMENT_ID NUMBER,
124 ENABLED_FLAG VARCHAR2,
125 OBJECT_VERSION_NUMBER NUMBER,
126 LAST_UPDATE_DATE DATE,
127 LAST_UPDATED_BY NUMBER,
128 CREATION_DATE DATE,
129 CREATED_BY NUMBER,
130 LAST_UPDATE_LOGIN NUMBER,
131 RULE_NAME VARCHAR2,
132 DESCRIPTION VARCHAR2
133 ) IS
134 BEGIN
135
136 UPDATE gcs_elim_rules_b cb
137 SET
138 RULE_ID=update_row.RULE_ID,
139 SEEDED_RULE_FLAG=update_row.SEEDED_RULE_FLAG,
140 TRANSACTION_TYPE_CODE=update_row.TRANSACTION_TYPE_CODE,
141 RULE_TYPE_CODE=update_row.RULE_TYPE_CODE,
142 FROM_TREATMENT_ID=update_row.FROM_TREATMENT_ID,
143 TO_TREATMENT_ID=update_row.TO_TREATMENT_ID,
144 ENABLED_FLAG=update_row.ENABLED_FLAG,
145 LAST_UPDATE_DATE=update_row.LAST_UPDATE_DATE,
146 LAST_UPDATED_BY=update_row.LAST_UPDATED_BY,
147 CREATION_DATE=update_row.CREATION_DATE,
148 CREATED_BY=update_row.CREATED_BY,
149 LAST_UPDATE_LOGIN=update_row.LAST_UPDATE_LOGIN,
150 OBJECT_VERSION_NUMBER=update_row.OBJECT_VERSION_NUMBER
151
152 WHERE cb.RULE_ID = update_row.RULE_ID;
153
154 IF SQL%NOTFOUND THEN
155 raise no_data_found;
156 END IF;
157
158 INSERT INTO
159 gcs_elim_rules_tl
160 (
161 RULE_ID,
162 LANGUAGE,
163 SOURCE_LANG,
164 RULE_NAME,
165 LAST_UPDATE_DATE,
166 LAST_UPDATED_BY,
167 CREATION_DATE,
168 CREATED_BY,
169 LAST_UPDATE_LOGIN,
170 DESCRIPTION
171 )
172 SELECT
173 RULE_ID,
174 userenv('LANG'),
175 userenv('LANG'),
176 RULE_NAME,
177 last_update_date,
178 last_updated_by,
179 creation_date,
180 created_by,
181 last_update_login,
182 DESCRIPTION
183
184 FROM dual
185 WHERE NOT EXISTS
186 (SELECT 1
187 FROM gcs_elim_rules_tl ctl
188 WHERE ctl.RULE_ID = update_row.RULE_ID
189 AND ctl.language = userenv('LANG'));
190
191
192
193 UPDATE gcs_elim_rules_tl ctl
194 SET
195 LAST_UPDATE_DATE = update_row.LAST_UPDATE_DATE,
196 LAST_UPDATED_BY = update_row.LAST_UPDATED_BY,
197 CREATION_DATE = update_row.CREATION_DATE,
198 CREATED_BY = update_row.CREATED_BY,
199 LAST_UPDATE_LOGIN = update_row.LAST_UPDATE_LOGIN
200
201 WHERE ctl.RULE_NAME = update_row.RULE_NAME
202 AND ctl.language = userenv('LANG');
203
204 IF SQL%NOTFOUND THEN
205 raise no_data_found;
206 END IF;
207 END Update_Row;
208
209
210
211
212 PROCEDURE Load_Row
213 (
214 row_id IN OUT NOCOPY VARCHAR2,
215 RULE_ID NUMBER,
216 SEEDED_RULE_FLAG VARCHAR2,
217 TRANSACTION_TYPE_CODE VARCHAR2,
218 RULE_TYPE_CODE VARCHAR2,
219 FROM_TREATMENT_ID NUMBER,
220 TO_TREATMENT_ID NUMBER,
221 ENABLED_FLAG VARCHAR2,
222 OBJECT_VERSION_NUMBER NUMBER,
223 LAST_UPDATE_DATE DATE,
224 LAST_UPDATED_BY NUMBER,
225 CREATION_DATE DATE,
226 CREATED_BY NUMBER,
227 LAST_UPDATE_LOGIN NUMBER,
228 owner varchar2,
229 custom_mode varchar2,
230 RULE_NAME varchar2,
231 DESCRIPTION varchar2
232
233
234 ) IS
235
236 f_luby NUMBER; -- category owner in file
237 f_ludate DATE; -- category update date in file
238 db_luby NUMBER; -- category owner in db
239 db_ludate DATE; -- category update date in db
240
241 f_start_date DATE; -- start date in file
242 BEGIN
243 -- Get last updated information from the loader data file
244 f_luby := fnd_load_util.owner_id(owner);
245 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
246
247 BEGIN
248 SELECT cb.last_updated_by, cb.last_update_date
249 INTO db_luby, db_ludate
250 FROM gcs_elim_rules_b cb
251 WHERE cb.RULE_ID = load_row.RULE_ID;
252
253 -- Test for customization information
254 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
255 custom_mode) THEN
256 update_row
257 (
258 row_id=>row_id,
259 RULE_ID=>RULE_ID,
260 SEEDED_RULE_FLAG=>SEEDED_RULE_FLAG,
261 TRANSACTION_TYPE_CODE=>TRANSACTION_TYPE_CODE,
262 RULE_TYPE_CODE=>RULE_TYPE_CODE,
263 FROM_TREATMENT_ID=>FROM_TREATMENT_ID,
264 TO_TREATMENT_ID=>TO_TREATMENT_ID,
265 LAST_UPDATE_DATE=>f_ludate,
266 LAST_UPDATED_BY=>f_luby,
267 CREATION_DATE=>f_ludate,
268 CREATED_BY=>f_luby,
269 LAST_UPDATE_LOGIN=>0,
270 ENABLED_FLAG=>ENABLED_FLAG,
271 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
272 RULE_NAME=>RULE_NAME,
273 DESCRIPTION=>DESCRIPTION
274 );
275
276
277
278 END IF;
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 insert_row
282 (
283 row_id=>row_id,
284 RULE_ID=>RULE_ID,
285 SEEDED_RULE_FLAG=>SEEDED_RULE_FLAG,
286 TRANSACTION_TYPE_CODE=>TRANSACTION_TYPE_CODE,
287 RULE_TYPE_CODE=>RULE_TYPE_CODE,
288 FROM_TREATMENT_ID=>FROM_TREATMENT_ID,
289 TO_TREATMENT_ID=>TO_TREATMENT_ID,
290 LAST_UPDATE_DATE=>f_ludate,
291 LAST_UPDATED_BY=>f_luby,
292 CREATION_DATE=>f_ludate,
293 CREATED_BY=>f_luby,
294 LAST_UPDATE_LOGIN=>0,
295 ENABLED_FLAG=>ENABLED_FLAG,
296 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
297 RULE_NAME=>RULE_NAME,
298 DESCRIPTION=>DESCRIPTION
299
300 );
301 END;
302
303 END Load_Row;
304
305
306
307 PROCEDURE Translate_Row
308 (
309 RULE_ID NUMBER,
310 RULE_NAME VARCHAR2,
311 DESCRIPTION VARCHAR2,
312 LAST_UPDATE_DATE DATE,
313 LAST_UPDATED_BY NUMBER,
314 CREATION_DATE DATE,
315 CREATED_BY NUMBER,
316 LAST_UPDATE_LOGIN NUMBER,
317 owner varchar2,
318 custom_mode varchar2
319 )
320 IS
321 f_luby NUMBER; -- category owner in file
322 f_ludate DATE; -- category update date in file
323 db_luby NUMBER; -- category owner in db
324 db_ludate DATE; -- category update date in db
325 BEGIN
326 -- Get last updated information from the loader data file
327 f_luby := fnd_load_util.owner_id(owner);
328 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
329
330 BEGIN
331 SELECT ctl.last_updated_by, ctl.last_update_date
332 INTO db_luby, db_ludate
333 FROM gcs_elim_rules_tl ctl
334 WHERE ctl.RULE_ID = translate_row.RULE_ID
335 AND ctl.language = userenv('LANG');
336
337 -- Test for customization information
338 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
339 custom_mode) THEN
340 UPDATE
341 gcs_elim_rules_tl ctl
342 SET
343 SOURCE_LANG= userenv('LANG'),
344 RULE_ID=translate_row.RULE_ID,
345 LAST_UPDATE_DATE=f_ludate,
346 LAST_UPDATED_BY=f_luby,
347 LAST_UPDATE_LOGIN=0
348
349 WHERE ctl.RULE_ID = translate_row.RULE_ID
350 AND userenv('LANG') IN (ctl.language, ctl.source_lang);
351 END IF;
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 null;
355 END;
356 END Translate_Row;
357
358
359 procedure ADD_LANGUAGE
360 is
361 begin
362 insert /*+ append parallel(tt) */ into
363 gcs_elim_rules_tl tt
364 (
365 RULE_ID,
366 LANGUAGE,
367 SOURCE_LANG,
368 RULE_NAME,
369 DESCRIPTION,
370 LAST_UPDATE_DATE,
371 LAST_UPDATED_BY,
372 CREATION_DATE,
373 CREATED_BY,
374 LAST_UPDATE_LOGIN
375 )
376
377 select /*+ parallel(v) parallel(t) use_nl(t) */
378 v.*
379 from
380 ( SELECT /*+ no_merge ordered parellel(b) */
381
382 B.RULE_ID,
383 L.LANGUAGE_CODE,
384 B.SOURCE_LANG,
385 B.RULE_NAME,
386 B.DESCRIPTION,
387 B.LAST_UPDATE_DATE,
388 B.LAST_UPDATED_BY,
389 B.CREATION_DATE,
390 B.CREATED_BY,
391 B.LAST_UPDATE_LOGIN
392
393
394 from gcs_elim_rules_tl B,
395 FND_LANGUAGES L
396 where L.INSTALLED_FLAG in ('I', 'B')
397 and B.LANGUAGE = userenv('LANG')
398 ) v, gcs_elim_rules_tl t
399 where T.RULE_ID(+) = v.RULE_ID
400 and T.LANGUAGE(+) = v.LANGUAGE_CODE
401 and t.RULE_ID IS NULL;
402
403 end ADD_LANGUAGE;
404
405
406
407 END GCS_ELIM_RULES_PKG;