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