[Home] [Help]
PACKAGE BODY: APPS.OE_PARAMETERS_PKG
Source
1 PACKAGE BODY OE_PARAMETERS_PKG as
2 /* $Header: OEXPARAB.pls 115.21 2004/05/05 12:22:17 rmoharan ship $ */
3 --Pack J
4 -- Start of comments
5 -- API name : Insert_Row
6 -- Type : Public
7 -- Description : Inserts record in Oe_Sys_Parameters_All table.
8 -- Parameters :
9 -- IN : p_sys_param_all_rec IN
10 -- oe_parameters_pkg.sys_param_all_rec_type Required
11 --
12 -- OUT : x_row_id OUT VARCHAR2
13 --
14 -- End of Comments
15 PROCEDURE Insert_Row(p_sys_param_all_rec IN
16 oe_parameters_pkg.sys_param_all_rec_type,
17 x_row_id OUT NOCOPY VARCHAR2)
18 IS
19 CURSOR get_rowid IS
20 SELECT rowid FROM oe_sys_parameters_all
21 WHERE nvl(org_id, -99) = nvl(p_sys_param_all_rec.org_Id, -99)
22 AND parameter_code = p_sys_param_all_rec.parameter_code;
23 --
24 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
25 --
26 BEGIN
27 INSERT INTO oe_sys_parameters_all(
28 org_id,
29 creation_date,
30 created_by,
31 last_update_date,
32 last_updated_by,
33 last_update_login,
34 parameter_code,
35 parameter_value,
36 context,
37 attribute1,
38 attribute2,
39 attribute3,
40 attribute4,
41 attribute5,
42 attribute6,
43 attribute7,
44 attribute8,
45 attribute9,
46 attribute10,
47 attribute11,
48 attribute12,
49 attribute13,
50 attribute14,
51 attribute15
52 )
53 VALUES (
54 p_sys_param_all_rec.org_id,
55 p_sys_param_all_rec.creation_date,
56 p_sys_param_all_rec.created_by,
57 p_sys_param_all_rec.last_update_date,
58 p_sys_param_all_rec.last_updated_by,
59 p_sys_param_all_rec.last_update_login,
60 p_sys_param_all_rec.parameter_code,
61 p_sys_param_all_rec.parameter_value,
62 p_sys_param_all_rec.Context,
63 p_sys_param_all_rec.Attribute1,
64 p_sys_param_all_rec.Attribute2,
65 p_sys_param_all_rec.Attribute3,
66 p_sys_param_all_rec.Attribute4,
67 p_sys_param_all_rec.Attribute5,
68 p_sys_param_all_rec.Attribute6,
69 p_sys_param_all_rec.Attribute7,
70 p_sys_param_all_rec.Attribute8,
71 p_sys_param_all_rec.Attribute9,
72 p_sys_param_all_rec.Attribute10,
73 p_sys_param_all_rec.Attribute11,
74 p_sys_param_all_rec.Attribute12,
75 p_sys_param_all_rec.Attribute13,
76 p_sys_param_all_rec.Attribute14,
77 p_sys_param_all_rec.Attribute15
78 );
79
80 OPEN get_rowid;
81 FETCH get_rowid INTO X_Row_id;
82 IF (get_rowid%NOTFOUND) THEN
83 CLOSE get_rowid;
84 Raise NO_DATA_FOUND;
85 END IF;
86 CLOSE get_rowid;
87 END Insert_Row;
88
89 -- Start of comments
90 -- API name : Lock_Row
91 -- Type : Public
92 -- Description : Locks the record in Oe_Sys_Parameters_All table for the rowid.
93 -- Parameters :
94 -- IN : p_row_id IN VARCHAR2 Required
95 --
96 -- OUT :
97 --
98 -- End of Comments
99
100 PROCEDURE Lock_Row(p_row_id IN VARCHAR2)
101 IS
102 CURSOR lock_param_all IS
103 SELECT org_id,parameter_value,
104 context,attribute1,
105 attribute2,attribute3,
106 attribute4,attribute5,
107 attribute6,attribute7,
108 attribute8,attribute9,
109 attribute10,attribute11,
110 attribute12,attribute13,
111 attribute14,attribute15
112 FROM oe_sys_parameters_all
113 WHERE rowid = p_row_id
114 FOR UPDATE OF parameter_value NOWAIT;
115
116 l_recinfo lock_param_all%ROWTYPE;
117 RECORD_CHANGED EXCEPTION;
118 --
119 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
120 --
121 BEGIN
122 IF p_row_id IS NOT NULL THEN
123 OPEN lock_param_all ;
124 FETCH lock_param_all INTO l_recinfo;
125 IF lock_param_all%NOTFOUND THEN
126 CLOSE lock_param_all;
127 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
128 app_exception.raise_exception;
129 END IF;
130 CLOSE lock_param_all;
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 raise;
135 END Lock_Row;
136
137 -- Start of comments
138 -- API name : Update_Row
139 -- Type : Public
140 -- Description : update the record in Oe_Sys_Parameters_All table for the rowid if not found
141 -- insert the record.
142 -- Parameters :
143 -- IN : p_sys_param_all_rec IN
144 -- oe_parameters_pkg.sys_param_all_rec_type Required
145 --
146 -- IN OUT : x_row_id IN VARCHAR2
147 --
148 -- End of Comments
149
150 PROCEDURE Update_Row(x_row_id IN OUT NOCOPY VARCHAR2,
151 p_sys_param_all_rec IN
152 oe_parameters_pkg.sys_param_all_rec_type)
153
154 IS
155 --
156 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
157 --
158 BEGIN
159 IF x_row_id IS NOT NULL THEN
160
161 UPDATE oe_sys_parameters_all
162 SET
163 org_id = p_sys_param_all_rec.org_id,
164 last_update_date = p_sys_param_all_rec.Last_Update_Date,
165 last_updated_by = p_sys_param_all_rec.Last_Updated_By,
166 last_update_login = p_sys_param_all_rec.Last_Update_Login,
167 parameter_value = p_sys_param_all_rec.parameter_value,
168 context = p_sys_param_all_rec.Context,
169 attribute1 = p_sys_param_all_rec.Attribute1,
170 attribute2 = p_sys_param_all_rec.Attribute2,
171 attribute3 = p_sys_param_all_rec.Attribute3,
172 attribute4 = p_sys_param_all_rec.Attribute4,
173 attribute5 = p_sys_param_all_rec.Attribute5,
174 attribute6 = p_sys_param_all_rec.Attribute6,
175 attribute7 = p_sys_param_all_rec.Attribute7,
176 attribute8 = p_sys_param_all_rec.Attribute8,
177 attribute9 = p_sys_param_all_rec.Attribute9,
178 attribute10 = p_sys_param_all_rec.Attribute10,
179 attribute11 = p_sys_param_all_rec.Attribute11,
180 attribute12 = p_sys_param_all_rec.Attribute12,
181 attribute13 = p_sys_param_all_rec.Attribute13,
182 attribute14 = p_sys_param_all_rec.Attribute14,
183 attribute15 = p_sys_param_all_rec.Attribute15
184 WHERE rowid = X_Row_id;
185
186 IF (SQL%NOTFOUND) THEN
187 -- Record does not exists. Inserting the record.
188 Insert_Row(p_sys_param_all_rec,
189 x_row_id );
190 END IF;
191 ELSE -- New Record Insert it.
192 Insert_Row(p_sys_param_all_rec,
193 x_row_id);
194 END IF;
195 END Update_Row;
196 -- End Pack J
197 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
198 X_Organization_Id NUMBER,
199 X_Last_Update_Date DATE ,
200 X_Last_Updated_By NUMBER ,
201 X_Creation_Date DATE ,
202 X_Created_By NUMBER ,
203 X_Last_Update_Login NUMBER,
204 X_Master_Organization_Id NUMBER,
205 x_customer_relationships_flag varchar2,
206 X_Audit_trail_Enable_Flag VARCHAR2,
207 --MRG BGN
208 X_Compute_Margin_Flag VARCHAR2,
209 --MRG END
210 --freight rating begin
211 X_Freight_Rating_Enabled_Flag VARCHAR2,
212 --freight rating end
213 X_Fte_Ship_Method_Enabled_Flag VARCHAR2,
214 X_Context VARCHAR2,
215 X_Attribute1 VARCHAR2,
216 X_Attribute2 VARCHAR2,
217 X_Attribute3 VARCHAR2,
218 X_Attribute4 VARCHAR2,
219 X_Attribute5 VARCHAR2,
220 X_Attribute6 VARCHAR2,
221 X_Attribute7 VARCHAR2,
222 X_Attribute8 VARCHAR2,
223 X_Attribute9 VARCHAR2,
224 X_Attribute10 VARCHAR2,
225 X_Attribute11 VARCHAR2,
226 X_Attribute12 VARCHAR2,
227 X_Attribute13 VARCHAR2,
228 X_Attribute14 VARCHAR2,
229 X_Attribute15 VARCHAR2
230 ) IS
231 -- CURSOR C IS SELECT rowid FROM oe_system_parameters_all
232 /** WHERE nvl(org_id, -99) = nvl(X_Organization_Id, -99); **/
233 -- NVL of -99 is removed as per SSA
234 -- WHERE org_id = X_Organization_Id;
235 -- WHERE nvl(org_id, -99) = nvl(X_Organization_Id, -99);
236 BEGIN
237 /*
238 INSERT INTO oe_system_parameters_all(
239 org_id,
240 last_update_date,
241 last_updated_by,
242 creation_date,
243 created_by,
244 last_update_login,
245 master_organization_id,
246 customer_relationships_flag,
247 audit_trail_enable_flag,
248 --MRG BGN
249 Compute_Margin_Flag,
250 --MRG END
251 --freight rating begin
252 freight_rating_enabled_flag,
253 --freight rating end
254 Fte_Ship_Method_enabled_flag,
255 context,
256 attribute1,
257 attribute2,
258 attribute3,
259 attribute4,
260 attribute5,
261 attribute6,
262 attribute7,
263 attribute8,
264 attribute9,
265 attribute10,
266 attribute11,
267 attribute12,
268 attribute13,
269 attribute14,
270 attribute15
271 )
272 VALUES (
273 X_Organization_Id,
274 X_Last_Update_Date,
275 X_Last_Updated_By,
276 X_Creation_Date,
277 X_Created_By,
278 X_Last_Update_Login,
279 X_Master_Organization_Id,
280 x_customer_relationships_flag,
281 X_Audit_trail_Enable_Flag,
282 --MRG BGN
283 X_Compute_Margin_Flag,
284 --MRG END
285 --freight rating begin
286 X_Freight_Rating_Enabled_flag,
287 --freight rating end
288 X_Fte_Ship_Method_Enabled_Flag,
289 X_Context,
290 X_Attribute1,
291 X_Attribute2,
292 X_Attribute3,
293 X_Attribute4,
294 X_Attribute5,
295 X_Attribute6,
296 X_Attribute7,
297 X_Attribute8,
298 X_Attribute9,
299 X_Attribute10,
300 X_Attribute11,
301 X_Attribute12,
302 X_Attribute13,
303 X_Attribute14,
304 X_Attribute15
305 );
306
307 OPEN C;
308 FETCH C INTO X_Rowid;
309 if (C%NOTFOUND) then
310 CLOSE C;
311 Raise NO_DATA_FOUND;
312 end if;
313 CLOSE C;
314 */
315 NULL;
316 END Insert_Row;
317
318 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
319 X_Organization_Id NUMBER,
320 X_Master_Organization_Id NUMBER,
321 x_customer_relationships_flag varchar2,
322 X_Audit_trail_Enable_Flag varchar2,
323 --MRG BGN
324 X_Compute_Margin_Flag VARCHAR2,
325 --MRG END
326 --freight rating begin
327 X_Freight_Rating_Enabled_Flag VARCHAR2,
328 --freight rating end
329 X_Fte_Ship_Method_Enabled_Flag VARCHAR2,
330 X_Context VARCHAR2,
331 X_Attribute1 VARCHAR2,
332 X_Attribute2 VARCHAR2,
336 X_Attribute6 VARCHAR2,
333 X_Attribute3 VARCHAR2,
334 X_Attribute4 VARCHAR2,
335 X_Attribute5 VARCHAR2,
340 X_Attribute10 VARCHAR2,
337 X_Attribute7 VARCHAR2,
338 X_Attribute8 VARCHAR2,
339 X_Attribute9 VARCHAR2,
341 X_Attribute11 VARCHAR2,
342 X_Attribute12 VARCHAR2,
343 X_Attribute13 VARCHAR2,
344 X_Attribute14 VARCHAR2,
345 X_Attribute15 VARCHAR2
346 ) IS
347 /*
348 CURSOR C IS
349 SELECT *
350 FROM oe_system_parameters_all
351 WHERE rowid = X_Rowid
352 FOR UPDATE of Org_Id NOWAIT;
353 Recinfo C%ROWTYPE;
354 RECORD_CHANGED EXCEPTION;
355 */
356 BEGIN
357 /*
358 OPEN C;
359 FETCH C INTO Recinfo;
360 if (C%NOTFOUND) then
361 CLOSE C;
362 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
363 APP_EXCEPTION.Raise_Exception;
364 end if;
365 CLOSE C;
366 if not ( ((Recinfo.org_id = X_Organization_Id)
367 OR ( (Recinfo.org_id IS NULL)
368 AND (X_Organization_Id IS NULL)))
369 AND (Recinfo.master_organization_id = X_Master_Organization_Id)
370 AND (Recinfo.customer_relationships_flag =
371 x_customer_relationships_flag)
372 AND (Recinfo.audit_trail_enable_flag = x_audit_trail_enable_flag)
373 --MRG BGN
374 AND ( (Recinfo.Compute_Margin_Flag = X_Compute_Margin_Flag)
375 OR ( (Recinfo.Compute_Margin_Flag IS NULL)
376 AND (X_Compute_Margin_Flag IS NULL)))
377 --MRG END
378 --freight rating begin
379 AND ( (Recinfo.Freight_Rating_Enabled_flag = X_Freight_Rating_Enabled_flag)
380 OR ( (Recinfo.Freight_Rating_Enabled_flag IS NULL)
381 AND (X_Freight_Rating_Enabled_flag IS NULL)))
382 --freight rating end
383 AND ( (Recinfo.Fte_Ship_Method_Enabled_flag = X_Fte_Ship_Method_Enabled_flag)
384 OR ( (Recinfo.Fte_Ship_Method_Enabled_flag IS NULL)
385 AND (X_Fte_Ship_Method_Enabled_flag IS NULL)))
386 ) then
387 RAISE RECORD_CHANGED;
388 end if;
389
390 if not ( ( (Recinfo.Context = X_Context)
391 OR ( (Recinfo.Context IS NULL)
392 AND (X_Context IS NULL)))
393 AND ( (Recinfo.attribute1 = X_Attribute1)
394 OR ( (Recinfo.attribute1 IS NULL)
395 AND (X_Attribute1 IS NULL)))
396 AND ( (Recinfo.attribute2 = X_Attribute2)
397 OR ( (Recinfo.attribute2 IS NULL)
398 AND (X_Attribute2 IS NULL)))
399 AND ( (Recinfo.attribute3 = X_Attribute3)
400 OR ( (Recinfo.attribute3 IS NULL)
401 AND (X_Attribute3 IS NULL)))
402 AND ( (Recinfo.attribute4 = X_Attribute4)
403 OR ( (Recinfo.attribute4 IS NULL)
404 AND (X_Attribute4 IS NULL)))
405 AND ( (Recinfo.attribute5 = X_Attribute5)
406 OR ( (Recinfo.attribute5 IS NULL)
407 AND (X_Attribute5 IS NULL)))
408 AND ( (Recinfo.attribute6 = X_Attribute6)
409 OR ( (Recinfo.attribute6 IS NULL)
413 AND (X_Attribute7 IS NULL)))
410 AND (X_Attribute6 IS NULL)))
411 AND ( (Recinfo.attribute7 = X_Attribute7)
412 OR ( (Recinfo.attribute7 IS NULL)
414 AND ( (Recinfo.attribute8 = X_Attribute8)
415 OR ( (Recinfo.attribute8 IS NULL)
416 AND (X_Attribute8 IS NULL)))
417 AND ( (Recinfo.attribute9 = X_Attribute9)
418 OR ( (Recinfo.attribute9 IS NULL)
419 AND (X_Attribute9 IS NULL)))
420 AND ( (Recinfo.attribute10 = X_Attribute10)
421 OR ( (Recinfo.attribute10 IS NULL)
422 AND (X_Attribute10 IS NULL)))
423 AND ( (Recinfo.attribute11 = X_Attribute11)
424 OR ( (Recinfo.attribute11 IS NULL)
425 AND (X_Attribute11 IS NULL)))
426 AND ( (Recinfo.attribute12 = X_Attribute12)
427 OR ( (Recinfo.attribute12 IS NULL)
428 AND (X_Attribute12 IS NULL)))
429 AND ( (Recinfo.attribute13 = X_Attribute13)
430 OR ( (Recinfo.attribute13 IS NULL)
431 AND (X_Attribute13 IS NULL)))
432 AND ( (Recinfo.attribute14 = X_Attribute14)
433 OR ( (Recinfo.attribute14 IS NULL)
434 AND (X_Attribute14 IS NULL)))
435 AND ( (Recinfo.attribute15 = X_Attribute15)
436 OR ( (Recinfo.attribute15 IS NULL)
437 AND (X_Attribute15 IS NULL)))
438 )then
439 Raise RECORD_CHANGED;
440 end if;
441 EXCEPTION
442 WHEN RECORD_CHANGED THEN
443 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
444 APP_EXCEPTION.Raise_Exception;
445 WHEN OTHERS THEN
446 raise;
447 */
448 NULL;
449 END Lock_Row;
450
451 PROCEDURE Update_Row(X_Rowid VARCHAR2,
452 X_Organization_Id NUMBER,
453 X_Last_Update_Date DATE,
454 X_Last_Updated_By NUMBER,
455 X_Last_Update_Login NUMBER,
456 X_Master_Organization_Id NUMBER,
457 x_customer_relationships_flag varchar2,
458 X_Audit_trail_Enable_Flag VARCHAR2,
459 --MRG BGN
460 X_Compute_Margin_Flag VARCHAR2,
461 --MRG END
462 --freight rating begin
463 X_Freight_Rating_Enabled_Flag VARCHAR2,
464 --freight rating end
465 X_Fte_Ship_Method_Enabled_Flag VARCHAR2,
466 X_Context VARCHAR2,
467 X_Attribute1 VARCHAR2,
468 X_Attribute2 VARCHAR2,
469 X_Attribute3 VARCHAR2,
470 X_Attribute4 VARCHAR2,
471 X_Attribute5 VARCHAR2,
472 X_Attribute6 VARCHAR2,
473 X_Attribute7 VARCHAR2,
474 X_Attribute8 VARCHAR2,
475 X_Attribute9 VARCHAR2,
476 X_Attribute10 VARCHAR2,
477 X_Attribute11 VARCHAR2,
478 X_Attribute12 VARCHAR2,
479 X_Attribute13 VARCHAR2,
480 X_Attribute14 VARCHAR2,
481 X_Attribute15 VARCHAR2
482 ) IS
483 BEGIN
484 /*
485 UPDATE oe_system_parameters_all
486 SET
487 org_id = X_Organization_Id,
488 last_update_date = X_Last_Update_Date,
489 last_updated_by = X_Last_Updated_By,
490 last_update_login = X_Last_Update_Login,
491 master_organization_id = X_Master_Organization_Id,
492 customer_relationships_flag = x_customer_relationships_flag,
493 audit_trail_enable_flag = X_Audit_trail_Enable_Flag,
494 --MRG BGN
495 Compute_Margin_Flag = X_Compute_Margin_Flag,
496 --MRG END
497 --freight rating begin
498 freight_rating_enabled_flag = X_Freight_Rating_Enabled_flag,
499 --freight rating end
500 Fte_Ship_Method_enabled_flag = X_Fte_Ship_Method_Enabled_flag,
501 context = X_Context,
502 attribute1 = X_Attribute1,
503 attribute2 = X_Attribute2,
504 attribute3 = X_Attribute3,
505 attribute4 = X_Attribute4,
506 attribute5 = X_Attribute5,
507 attribute6 = X_Attribute6,
508 attribute7 = X_Attribute7,
509 attribute8 = X_Attribute8,
513 attribute12 = X_Attribute12,
510 attribute9 = X_Attribute9,
511 attribute10 = X_Attribute10,
512 attribute11 = X_Attribute11,
514 attribute13 = X_Attribute13,
515 attribute14 = X_Attribute14,
516 attribute15 = X_Attribute15
517 WHERE rowid = X_Rowid;
518
519 if (SQL%NOTFOUND) then
520 Raise NO_DATA_FOUND;
521 end if;
522 */
523 NULL;
524 END Update_Row;
525 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
526 BEGIN
527 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
528 DELETE FROM oe_sys_parameters_all
529 WHERE rowid = X_Rowid;
530 /*
531 ELSE
532
533 DELETE FROM oe_system_parameters_all
534 WHERE rowid = X_Rowid;
535 */
536 END IF;
537 if (SQL%NOTFOUND) then
538 Raise NO_DATA_FOUND;
539 end if;
540 END Delete_Row;
541
542
543 END OE_PARAMETERS_PKG;