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