[Home] [Help]
PACKAGE BODY: APPS.GCS_INTERCO_RULES_PKG
Source
1 PACKAGE BODY GCS_INTERCO_RULES_PKG AS
2 /* $Header: gcsintercoruleb.pls 120.2 2005/09/30 17:43:50 spala noship $ */
3
4 PROCEDURE Insert_Row
5 (
6 row_id IN OUT NOCOPY VARCHAR2,
7 RULE_ID NUMBER,
8 ENABLED_FLAG VARCHAR2,
9 THRESHOLD_AMOUNT NUMBER,
10 THRESHOLD_CURRENCY VARCHAR2,
11 OBJECT_VERSION_NUMBER NUMBER,
12 CREATION_DATE DATE,
13 CREATED_BY NUMBER,
14 LAST_UPDATE_DATE DATE,
15 LAST_UPDATED_BY NUMBER,
16 LAST_UPDATE_LOGIN NUMBER,
17 SUS_FINANCIAL_ELEM_ID NUMBER,
18 SUS_PRODUCT_ID NUMBER,
19 SUS_NATURAL_ACCOUNT_ID NUMBER,
20 SUS_CHANNEL_ID NUMBER,
21 SUS_LINE_ITEM_ID NUMBER,
22 SUS_PROJECT_ID NUMBER,
23 SUS_CUSTOMER_ID NUMBER,
24 SUS_TASK_ID NUMBER,
25 SUS_USER_DIM1_ID NUMBER,
26 SUS_USER_DIM2_ID NUMBER,
27 SUS_USER_DIM3_ID NUMBER,
28 SUS_USER_DIM4_ID NUMBER,
29 SUS_USER_DIM5_ID NUMBER,
30 SUS_USER_DIM6_ID NUMBER,
31 SUS_USER_DIM7_ID NUMBER,
32 SUS_USER_DIM8_ID NUMBER,
33 SUS_USER_DIM9_ID NUMBER,
34 SUS_USER_DIM10_ID NUMBER,
35 RULE_NAME varchar2,
36 DESCRIPTION varchar2
37 ) IS
38
39 CURSOR intercorules_row IS
40 SELECT rowid
41 FROM gcs_interco_rules_b cb
42 WHERE cb.RULE_ID= insert_row.RULE_ID;
43 BEGIN
44 IF RULE_ID IS NULL THEN
45 raise no_data_found;
46 END IF;
47
48 INSERT INTO gcs_interco_rules_b
49 (
50 RULE_ID,
51 ENABLED_FLAG,
52 THRESHOLD_AMOUNT,
53 THRESHOLD_CURRENCY,
54 OBJECT_VERSION_NUMBER,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN,
60 SUS_FINANCIAL_ELEM_ID,
61 SUS_PRODUCT_ID,
62 SUS_NATURAL_ACCOUNT_ID,
63 SUS_CHANNEL_ID,
64 SUS_LINE_ITEM_ID,
65 SUS_PROJECT_ID,
66 SUS_CUSTOMER_ID,
67 SUS_TASK_ID,
68 SUS_USER_DIM1_ID,
69 SUS_USER_DIM2_ID,
70 SUS_USER_DIM3_ID,
71 SUS_USER_DIM4_ID,
72 SUS_USER_DIM5_ID,
73 SUS_USER_DIM6_ID,
74 SUS_USER_DIM7_ID,
75 SUS_USER_DIM8_ID,
76 SUS_USER_DIM9_ID,
77 SUS_USER_DIM10_ID
78 )
79 SELECT
80 RULE_ID,
81 ENABLED_FLAG,
82 THRESHOLD_AMOUNT,
83 THRESHOLD_CURRENCY,
84 OBJECT_VERSION_NUMBER,
85 CREATION_DATE,
86 CREATED_BY,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN,
90 SUS_FINANCIAL_ELEM_ID,
91 SUS_PRODUCT_ID,
92 SUS_NATURAL_ACCOUNT_ID,
93 SUS_CHANNEL_ID,
94 SUS_LINE_ITEM_ID,
95 SUS_PROJECT_ID,
96 SUS_CUSTOMER_ID,
97 SUS_TASK_ID,
98 SUS_USER_DIM1_ID,
99 SUS_USER_DIM2_ID,
100 SUS_USER_DIM3_ID,
101 SUS_USER_DIM4_ID,
102 SUS_USER_DIM5_ID,
103 SUS_USER_DIM6_ID,
104 SUS_USER_DIM7_ID,
105 SUS_USER_DIM8_ID,
106 SUS_USER_DIM9_ID,
107 SUS_USER_DIM10_ID
108
109 FROM dual
110 WHERE NOT EXISTS
111 (SELECT 1
112 FROM gcs_interco_rules_b cb
113 WHERE cb.RULE_ID= insert_row.RULE_ID);
114
115 INSERT INTO gcs_interco_rules_tl
116 (
117 RULE_ID,
118 LANGUAGE,
119 SOURCE_LANG,
120 RULE_NAME,
121 OBJECT_VERSION_NUMBER,
122 CREATION_DATE,
123 CREATED_BY,
124 LAST_UPDATE_DATE,
125 LAST_UPDATED_BY,
126 LAST_UPDATE_LOGIN,
127 DESCRIPTION
128 )
129 SELECT
130 RULE_ID,
131 userenv('LANG'),
132 userenv('LANG'),
133 RULE_NAME,
134 OBJECT_VERSION_NUMBER,
135 creation_date,
136 created_by,
137 last_update_date,
138 last_updated_by,
139 last_update_login,
140 description
141
142 FROM dual
143 WHERE NOT EXISTS
144 (SELECT 1
145 FROM gcs_interco_rules_tl ctl
146 WHERE ctl.RULE_ID = insert_row.RULE_ID
147 AND ctl.language = userenv('LANG'));
148
149 OPEN intercorules_row;
150 FETCH intercorules_row INTO row_id;
151 IF intercorules_row%NOTFOUND THEN
152 CLOSE intercorules_row;
153 raise no_data_found;
154 END IF;
155 CLOSE intercorules_row;
156
157 END Insert_Row;
158
159
160
161
162 PROCEDURE Update_Row
163 (
164 row_id IN OUT NOCOPY VARCHAR2,
165 RULE_ID NUMBER,
166 ENABLED_FLAG VARCHAR2,
167 THRESHOLD_AMOUNT NUMBER,
168 THRESHOLD_CURRENCY VARCHAR2,
169 CREATION_DATE DATE,
170 CREATED_BY NUMBER,
171 OBJECT_VERSION_NUMBER NUMBER,
172 LAST_UPDATE_DATE DATE,
173 LAST_UPDATED_BY NUMBER,
174 LAST_UPDATE_LOGIN NUMBER,
175 SUS_FINANCIAL_ELEM_ID NUMBER,
176 SUS_PRODUCT_ID NUMBER,
177 SUS_NATURAL_ACCOUNT_ID NUMBER,
178 SUS_CHANNEL_ID NUMBER,
179 SUS_LINE_ITEM_ID NUMBER,
180 SUS_PROJECT_ID NUMBER,
181 SUS_CUSTOMER_ID NUMBER,
182 SUS_TASK_ID NUMBER,
183 SUS_USER_DIM1_ID NUMBER,
184 SUS_USER_DIM2_ID NUMBER,
185 SUS_USER_DIM3_ID NUMBER,
186 SUS_USER_DIM4_ID NUMBER,
187 SUS_USER_DIM5_ID NUMBER,
188 SUS_USER_DIM6_ID NUMBER,
189 SUS_USER_DIM7_ID NUMBER,
190 SUS_USER_DIM8_ID NUMBER,
191 SUS_USER_DIM9_ID NUMBER,
192 SUS_USER_DIM10_ID NUMBER,
193 RULE_NAME varchar2,
194 DESCRIPTION varchar2
195 ) IS
196 BEGIN
197
198 UPDATE gcs_interco_rules_b cb
199 SET
200
201 ENABLED_FLAG =update_row.ENABLED_FLAG,
202 THRESHOLD_AMOUNT =update_row.THRESHOLD_AMOUNT,
203 THRESHOLD_CURRENCY =update_row.THRESHOLD_CURRENCY,
204 CREATION_DATE =update_row.CREATION_DATE,
205 CREATED_BY =update_row.CREATED_BY,
206 LAST_UPDATE_DATE =update_row.LAST_UPDATE_DATE,
207 LAST_UPDATED_BY =update_row.LAST_UPDATED_BY,
208 LAST_UPDATE_LOGIN =update_row.LAST_UPDATE_LOGIN,
209 SUS_FINANCIAL_ELEM_ID =update_row.SUS_FINANCIAL_ELEM_ID,
210 SUS_PRODUCT_ID =update_row.SUS_PRODUCT_ID,
211 SUS_NATURAL_ACCOUNT_ID =update_row.SUS_NATURAL_ACCOUNT_ID,
212 SUS_CHANNEL_ID =update_row.SUS_CHANNEL_ID,
213 SUS_LINE_ITEM_ID =update_row.SUS_LINE_ITEM_ID,
214 SUS_PROJECT_ID =update_row.SUS_PROJECT_ID,
215 SUS_CUSTOMER_ID =update_row.SUS_CUSTOMER_ID,
216 SUS_TASK_ID =update_row.SUS_TASK_ID,
217 SUS_USER_DIM1_ID =update_row.SUS_USER_DIM1_ID,
218 SUS_USER_DIM2_ID =update_row.SUS_USER_DIM2_ID,
219 SUS_USER_DIM3_ID =update_row.SUS_USER_DIM3_ID,
220 SUS_USER_DIM4_ID =update_row.SUS_USER_DIM4_ID,
221 SUS_USER_DIM5_ID =update_row.SUS_USER_DIM5_ID,
222 SUS_USER_DIM6_ID =update_row.SUS_USER_DIM6_ID,
223 SUS_USER_DIM7_ID =update_row.SUS_USER_DIM7_ID,
224 SUS_USER_DIM8_ID =update_row.SUS_USER_DIM8_ID,
225 SUS_USER_DIM9_ID =update_row.SUS_USER_DIM9_ID,
226 SUS_USER_DIM10_ID =update_row.SUS_USER_DIM10_ID,
227 OBJECT_VERSION_NUMBER =update_row.OBJECT_VERSION_NUMBER
228
229 WHERE cb.RULE_ID = update_row.RULE_ID;
230
231 IF SQL%NOTFOUND THEN
232 raise no_data_found;
233 END IF;
234
235 INSERT INTO
236 gcs_interco_rules_tl
237 (
238 RULE_ID,
239 LANGUAGE,
240 SOURCE_LANG,
241 RULE_NAME,
242 CREATION_DATE,
243 CREATED_BY,
244 LAST_UPDATE_DATE,
245 LAST_UPDATED_BY,
246 LAST_UPDATE_LOGIN,
247 DESCRIPTION,
248 OBJECT_VERSION_NUMBER
249 )
250 SELECT
251 RULE_ID,
252 userenv('LANG'),
253 userenv('LANG'),
254 RULE_NAME,
255 CREATION_DATE,
256 CREATED_BY,
257 LAST_UPDATE_DATE,
258 LAST_UPDATED_BY,
259 LAST_UPDATE_LOGIN,
260 DESCRIPTION,
261 OBJECT_VERSION_NUMBER
262
263 FROM dual
264 WHERE NOT EXISTS
265 (SELECT 1
266 FROM gcs_interco_rules_tl ctl
267 WHERE ctl.RULE_ID = update_row.RULE_ID
268 AND ctl.language = userenv('LANG'));
269
270
271
272 UPDATE gcs_interco_rules_tl ctl
273 SET
274 RULE_NAME= update_row.RULE_NAME,
275 LAST_UPDATE_DATE = update_row.LAST_UPDATE_DATE,
276 LAST_UPDATED_BY = update_row.LAST_UPDATED_BY,
277 CREATION_DATE = update_row.CREATION_DATE,
278 CREATED_BY = update_row.CREATED_BY,
279 LAST_UPDATE_LOGIN = update_row.LAST_UPDATE_LOGIN,
280 DESCRIPTION = update_row.DESCRIPTION
281
282 WHERE ctl.RULE_NAME = update_row.RULE_NAME
283 AND ctl.language = userenv('LANG');
284
285 IF SQL%NOTFOUND THEN
286 raise no_data_found;
287 END IF;
288 END Update_Row;
289
290
291
292
293 PROCEDURE Load_Row
294 (
295 row_id IN OUT NOCOPY VARCHAR2,
296 RULE_ID NUMBER,
297 ENABLED_FLAG VARCHAR2,
298 THRESHOLD_AMOUNT NUMBER,
299 THRESHOLD_CURRENCY VARCHAR2,
300 CREATION_DATE DATE,
301 CREATED_BY NUMBER,
302 OBJECT_VERSION_NUMBER NUMBER,
303 LAST_UPDATE_DATE DATE,
304 LAST_UPDATED_BY NUMBER,
305 LAST_UPDATE_LOGIN NUMBER,
306 SUS_FINANCIAL_ELEM_ID NUMBER,
307 SUS_PRODUCT_ID NUMBER,
308 SUS_NATURAL_ACCOUNT_ID NUMBER,
309 SUS_CHANNEL_ID NUMBER,
310 SUS_LINE_ITEM_ID NUMBER,
311 SUS_PROJECT_ID NUMBER,
312 SUS_CUSTOMER_ID NUMBER,
313 SUS_TASK_ID NUMBER,
314 SUS_USER_DIM1_ID NUMBER,
315 SUS_USER_DIM2_ID NUMBER,
316 SUS_USER_DIM3_ID NUMBER,
317 SUS_USER_DIM4_ID NUMBER,
318 SUS_USER_DIM5_ID NUMBER,
319 SUS_USER_DIM6_ID NUMBER,
320 SUS_USER_DIM7_ID NUMBER,
321 SUS_USER_DIM8_ID NUMBER,
322 SUS_USER_DIM9_ID NUMBER,
323 SUS_USER_DIM10_ID NUMBER,
324 RULE_NAME varchar2,
325 DESCRIPTION varchar2,
326 owner varchar2,
327 custom_mode varchar2
328 ) IS
329
330 f_luby NUMBER; -- category owner in file
331 f_ludate DATE; -- category update date in file
332 db_luby NUMBER; -- category owner in db
333 db_ludate DATE; -- category update date in db
334 f_start_date DATE; -- start date in file
335
336 BEGIN
337 -- Get last updated information from the loader data file
338 f_luby := fnd_load_util.owner_id(owner);
339 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
340
341 BEGIN
342 SELECT cb.last_updated_by, cb.last_update_date
343 INTO db_luby, db_ludate
344 FROM gcs_interco_rules_b cb
345 WHERE cb.RULE_ID = load_row.RULE_ID;
346
347 -- Test for customization information
348 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
349 custom_mode) THEN
350 update_row
351 (
352 row_id=>row_id,
353 RULE_ID=>RULE_ID,
354 ENABLED_FLAG=>ENABLED_FLAG,
355 THRESHOLD_AMOUNT=>THRESHOLD_AMOUNT,
356 THRESHOLD_CURRENCY=>THRESHOLD_CURRENCY,
357 SUS_FINANCIAL_ELEM_ID=>SUS_FINANCIAL_ELEM_ID,
358 SUS_PRODUCT_ID=>SUS_PRODUCT_ID,
359 SUS_NATURAL_ACCOUNT_ID=>SUS_NATURAL_ACCOUNT_ID,
360 LAST_UPDATE_DATE=>f_ludate,
361 LAST_UPDATED_BY=>f_luby,
362 CREATION_DATE=>f_ludate,
363 CREATED_BY=>f_luby,
364 LAST_UPDATE_LOGIN=>0,
365 SUS_CHANNEL_ID=>SUS_CHANNEL_ID,
366 SUS_LINE_ITEM_ID=>SUS_LINE_ITEM_ID,
367 SUS_PROJECT_ID=>SUS_PROJECT_ID,
368 SUS_CUSTOMER_ID=>SUS_CUSTOMER_ID,
369 SUS_TASK_ID=>SUS_TASK_ID,
370 SUS_USER_DIM1_ID=>SUS_USER_DIM1_ID,
371 SUS_USER_DIM2_ID=>SUS_USER_DIM2_ID,
372 SUS_USER_DIM3_ID=>SUS_USER_DIM3_ID,
373 SUS_USER_DIM4_ID=>SUS_USER_DIM4_ID,
374 SUS_USER_DIM5_ID=>SUS_USER_DIM5_ID,
375 SUS_USER_DIM6_ID=>SUS_USER_DIM6_ID,
376 SUS_USER_DIM7_ID=>SUS_USER_DIM7_ID,
377 SUS_USER_DIM8_ID=>SUS_USER_DIM8_ID,
378 SUS_USER_DIM9_ID=>SUS_USER_DIM9_ID,
379 SUS_USER_DIM10_ID=>SUS_USER_DIM10_ID,
380 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
381 DESCRIPTION=>DESCRIPTION,
382 RULE_NAME=>RULE_NAME
383 );
384 END IF;
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 insert_row
388 (
389 row_id=>row_id,
390 RULE_ID=>RULE_ID,
391 ENABLED_FLAG=>ENABLED_FLAG,
392 THRESHOLD_AMOUNT=>THRESHOLD_AMOUNT,
393 THRESHOLD_CURRENCY=>THRESHOLD_CURRENCY,
394 SUS_FINANCIAL_ELEM_ID=>SUS_FINANCIAL_ELEM_ID,
395 SUS_PRODUCT_ID=>SUS_PRODUCT_ID,
396 SUS_NATURAL_ACCOUNT_ID=>SUS_NATURAL_ACCOUNT_ID,
397 LAST_UPDATE_DATE=>f_ludate,
398 LAST_UPDATED_BY=>f_luby,
399 CREATION_DATE=>f_ludate,
400 CREATED_BY=>f_luby,
401 LAST_UPDATE_LOGIN=>0,
402 SUS_CHANNEL_ID=>SUS_CHANNEL_ID,
403 SUS_LINE_ITEM_ID=>SUS_LINE_ITEM_ID,
404 SUS_PROJECT_ID=>SUS_PROJECT_ID,
405 SUS_CUSTOMER_ID=>SUS_CUSTOMER_ID,
406 SUS_TASK_ID=>SUS_TASK_ID,
407 SUS_USER_DIM1_ID=>SUS_USER_DIM1_ID,
408 SUS_USER_DIM2_ID=>SUS_USER_DIM2_ID,
409 SUS_USER_DIM3_ID=>SUS_USER_DIM3_ID,
410 SUS_USER_DIM4_ID=>SUS_USER_DIM4_ID,
411 SUS_USER_DIM5_ID=>SUS_USER_DIM5_ID,
412 SUS_USER_DIM6_ID=>SUS_USER_DIM6_ID,
413 SUS_USER_DIM7_ID=>SUS_USER_DIM7_ID,
414 SUS_USER_DIM8_ID=>SUS_USER_DIM8_ID,
415 SUS_USER_DIM9_ID=>SUS_USER_DIM9_ID,
416 SUS_USER_DIM10_ID=>SUS_USER_DIM10_ID,
417 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
418 RULE_NAME=>RULE_NAME,
419 DESCRIPTION=>DESCRIPTION
420 );
421 END;
422
423 END Load_Row;
424
425
426
427 PROCEDURE Translate_Row
428 (
429 RULE_ID NUMBER,
430 RULE_NAME VARCHAR2,
431 OBJECT_VERSION_NUMBER NUMBER,
432 CREATION_DATE DATE,
433 CREATED_BY NUMBER,
434 LAST_UPDATE_DATE DATE,
435 LAST_UPDATED_BY NUMBER,
436 LAST_UPDATE_LOGIN NUMBER,
437 DESCRIPTION VARCHAR2,
438 owner varchar2,
439 custom_mode varchar2
440
441 ) IS
442 f_luby NUMBER; -- category owner in file
443 f_ludate DATE; -- category update date in file
444 db_luby NUMBER; -- category owner in db
445 db_ludate DATE; -- category update date in db
446 BEGIN
447 -- Get last updated information from the loader data file
448 f_luby := fnd_load_util.owner_id(owner);
449 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
450
451 BEGIN
452 SELECT ctl.last_updated_by, ctl.last_update_date
453 INTO db_luby, db_ludate
454 FROM gcs_interco_rules_tl ctl
455 WHERE ctl.RULE_ID = translate_row.RULE_ID
456 AND ctl.language = userenv('LANG');
457
458 -- Test for customization information
459 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
460 custom_mode) THEN
461 UPDATE
462 gcs_interco_rules_tl ctl
463 SET
464 SOURCE_LANG= userenv('LANG'),
465 RULE_ID=translate_row.RULE_ID,
466 LAST_UPDATE_DATE=f_ludate,
467 LAST_UPDATED_BY=f_luby,
468 LAST_UPDATE_LOGIN=0,
469 DESCRIPTION=translate_row.DESCRIPTION
470
471 WHERE ctl.RULE_ID = translate_row.RULE_ID
472 AND userenv('LANG') IN (ctl.language, ctl.source_lang);
473 END IF;
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 null;
477 END;
478 END Translate_Row;
479
480
481 procedure ADD_LANGUAGE
482 is
483 begin
484 insert /*+ append parallel(tt) */ into
485 gcs_interco_rules_tl tt
486 (
487 RULE_ID ,
488 LANGUAGE ,
489 SOURCE_LANG ,
490 RULE_NAME ,
491 OBJECT_VERSION_NUMBER,
492 CREATION_DATE ,
493 CREATED_BY ,
494 LAST_UPDATE_DATE ,
495 LAST_UPDATED_BY ,
499
496 LAST_UPDATE_LOGIN ,
497 DESCRIPTION
498 )
500 select /*+ parallel(v) parallel(t) use_nl(t) */
501 v.*
502 from
503 ( SELECT /*+ no_merge ordered parellel(b) */
504
505 B.RULE_ID ,
506 L.LANGUAGE_CODE,
507 B.SOURCE_LANG ,
508 B.RULE_NAME ,
509 B.OBJECT_VERSION_NUMBER,
510 B.CREATION_DATE ,
511 B.CREATED_BY ,
512 B.LAST_UPDATE_DATE ,
513 B.LAST_UPDATED_BY ,
514 B.LAST_UPDATE_LOGIN ,
515 B.DESCRIPTION
516
517
518 from gcs_interco_rules_tl B,
519 FND_LANGUAGES L
520 where L.INSTALLED_FLAG in ('I', 'B')
521 and B.LANGUAGE = userenv('LANG')
522 ) v, gcs_interco_rules_tl t
523 where T.RULE_ID(+) = v.RULE_ID
524 and T.LANGUAGE(+) = v.LANGUAGE_CODE
525 and t.RULE_ID IS NULL;
526
527 end ADD_LANGUAGE;
528
529
530
531
532 END GCS_INTERCO_RULES_PKG;