[Home] [Help]
PACKAGE BODY: APPS.GHG_SOURCES_PKG
Source
1 PACKAGE BODY GHG_SOURCES_PKG AS
2 /*$Header: ghgsourb.pls 120.2.12020000.2 2013/03/11 07:23:45 mgijare ship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_source_id NUMBER,
6 x_source_name VARCHAR2,
7 x_unit_of_measure VARCHAR2,
8 x_attribute1 VARCHAR2,
9 x_attribute2 VARCHAR2,
10 x_attribute3 VARCHAR2,
11 x_attribute4 VARCHAR2,
12 x_attribute5 VARCHAR2,
13 x_attribute6 VARCHAR2,
14 x_attribute7 VARCHAR2,
15 x_attribute8 VARCHAR2,
16 x_attribute9 VARCHAR2,
17 x_attribute10 VARCHAR2,
18 x_attribute11 VARCHAR2,
19 x_attribute12 VARCHAR2,
20 x_attribute13 VARCHAR2,
21 x_attribute14 VARCHAR2,
22 x_attribute15 VARCHAR2,
23 x_attribute16 VARCHAR2,
24 x_attribute17 VARCHAR2,
25 x_attribute18 VARCHAR2,
26 x_attribute19 VARCHAR2,
27 x_attribute20 VARCHAR2,
28 x_attribute_category VARCHAR2,
29 x_created_by NUMBER,
30 x_creation_date DATE,
31 x_last_updated_by NUMBER,
32 x_last_update_date DATE,
33 x_last_update_login NUMBER,
34 x_org_id NUMBER,
35 x_enabled VARCHAR2,
36 x_formula_id number,
37 x_ghg_uom_class_code VARCHAR2,
38 x_energy_type VARCHAR2) IS
39
40 v_debug_info VARCHAR2(100);
41
42 CURSOR c IS
43 SELECT ROWID
44 FROM GHG_SOURCES
45 WHERE source_id = x_source_id;
46
47 BEGIN
48
49 v_debug_info := 'Inserting into GHG_SOURCES';
50
51 INSERT INTO GHG_SOURCES (source_id,
52 source_name,
53 unit_of_measure,
54 attribute1,
55 attribute2,
56 attribute3,
57 attribute4,
58 attribute5,
59 attribute6,
60 attribute7,
61 attribute8,
62 attribute9,
63 attribute10,
64 attribute11,
65 attribute12,
66 attribute13,
67 attribute14,
68 attribute15,
69 attribute16,
70 attribute17,
71 attribute18,
72 attribute19,
73 attribute20,
74 attribute_category,
75 created_by,
76 creation_date,
77 last_updated_by,
78 last_update_date,
79 last_update_login,
80 org_id,
81 enabled,
82 formula_id,
83 ghg_uom_class_code,
84 energy_type)
85 VALUES (x_source_id,
86 x_source_name,
87 x_unit_of_measure,
88 x_attribute1,
89 x_attribute2,
90 x_attribute3,
91 x_attribute4,
92 x_attribute5,
93 x_attribute6,
94 x_attribute7,
95 x_attribute8,
96 x_attribute9,
97 x_attribute10,
98 x_attribute11,
99 x_attribute12,
100 x_attribute13,
101 x_attribute14,
102 x_attribute15,
103 x_attribute16,
104 x_attribute17,
105 x_attribute18,
106 x_attribute19,
107 x_attribute20,
108 x_attribute_category,
109 x_created_by,
110 x_creation_date,
111 x_last_updated_by,
112 x_last_update_date,
113 x_last_update_login,
114 x_org_id,
115 x_enabled,
116 x_formula_id,
117 x_ghg_uom_class_code,
118 x_energy_type);
119
120 v_debug_info := 'Open cursor c';
121 OPEN c;
122
123 v_debug_info := 'Fetch cursor c';
124 FETCH c INTO x_rowid;
125
126 IF (c%notfound) THEN
127 v_debug_info := 'Close cursor c - ROW NOT FOUND';
128 CLOSE c;
129 RAISE no_data_found;
130 END IF;
131
132 v_debug_info := 'Close cursor c';
133 CLOSE c;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 NULL;
138
139 WHEN OTHERS THEN
140 IF (SQLCODE <> -20001) THEN
141 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
142 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
143 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
144 END IF;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146
147 END insert_row;
148
149 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
150 x_source_id NUMBER,
151 x_source_name VARCHAR2,
152 x_unit_of_measure VARCHAR2,
153 x_attribute1 VARCHAR2,
154 x_attribute2 VARCHAR2,
155 x_attribute3 VARCHAR2,
156 x_attribute4 VARCHAR2,
157 x_attribute5 VARCHAR2,
158 x_attribute6 VARCHAR2,
159 x_attribute7 VARCHAR2,
160 x_attribute8 VARCHAR2,
161 x_attribute9 VARCHAR2,
162 x_attribute10 VARCHAR2,
163 x_attribute11 VARCHAR2,
164 x_attribute12 VARCHAR2,
165 x_attribute13 VARCHAR2,
166 x_attribute14 VARCHAR2,
167 x_attribute15 VARCHAR2,
168 x_attribute16 VARCHAR2,
169 x_attribute17 VARCHAR2,
170 x_attribute18 VARCHAR2,
171 x_attribute19 VARCHAR2,
172 x_attribute20 VARCHAR2,
173 x_attribute_category VARCHAR2,
174 x_created_by NUMBER,
175 x_creation_date DATE,
176 x_last_updated_by NUMBER,
177 x_last_update_date DATE,
178 x_last_update_login NUMBER,
179 x_org_id NUMBER,
180 x_enabled VARCHAR2,
181 x_formula_id number,
182 x_ghg_uom_class_code VARCHAR2,
183 x_energy_type VARCHAR2) IS
184
185 v_debug_info VARCHAR2(100);
186
187 BEGIN
188
189 v_debug_info := 'Updating GHG_SOURCES';
190
191 UPDATE GHG_SOURCES
192 SET source_id = x_source_id,
193 source_name = x_source_name,
194 unit_of_measure = x_unit_of_measure,
195 attribute1 = x_attribute1,
196 attribute2 = x_attribute2,
197 attribute3 = x_attribute3,
198 attribute4 = x_attribute4,
199 attribute5 = x_attribute5,
200 attribute6 = x_attribute6,
201 attribute7 = x_attribute7,
202 attribute8 = x_attribute8,
203 attribute9 = x_attribute9,
204 attribute10 = x_attribute10,
205 attribute11 = x_attribute11,
206 attribute12 = x_attribute12,
207 attribute13 = x_attribute13,
208 attribute14 = x_attribute14,
209 attribute15 = x_attribute15,
210 attribute16 = x_attribute16,
211 attribute17 = x_attribute17,
212 attribute18 = x_attribute18,
213 attribute19 = x_attribute19,
214 attribute20 = x_attribute20,
215 attribute_category = x_attribute_category,
216 created_by = x_created_by,
217 creation_date = x_creation_date,
218 last_updated_by = x_last_updated_by,
219 last_update_date = x_last_update_date,
220 last_update_login = x_last_update_login,
221 org_id = x_org_id ,
222 enabled = x_enabled,
223 formula_id = x_formula_id,
224 ghg_uom_class_code = x_ghg_uom_class_code,
225 energy_type = x_energy_type
226 WHERE rowid = x_rowid;
227
228 IF (SQL%NOTFOUND) THEN
229 RAISE NO_DATA_FOUND;
230 END IF;
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 NULL;
235
236 WHEN OTHERS THEN
237 IF (SQLCODE <> -20001) THEN
238 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
239 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
240 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
241 END IF;
242 APP_EXCEPTION.RAISE_EXCEPTION;
243
244 END update_row;
245
246 PROCEDURE delete_row (x_source_id NUMBER) IS
247
248
249 v_debug_info VARCHAR2(100);
250
251 BEGIN
252
253 DELETE FROM GHG_SOURCES
254 WHERE source_id = x_source_id;
255
256 IF (sql%notfound) THEN
257 RAISE no_data_found;
258 END IF;
259
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 NULL;
264
265 WHEN OTHERS THEN
266 IF (SQLCODE <> -20001) THEN
267 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
268 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
269 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
270 END IF;
271 APP_EXCEPTION.RAISE_EXCEPTION;
272
273
274 END delete_row;
275
276 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
277 x_source_id NUMBER,
278 x_source_name VARCHAR2,
279 x_unit_of_measure VARCHAR2,
280 x_attribute1 VARCHAR2,
281 x_attribute2 VARCHAR2,
282 x_attribute3 VARCHAR2,
283 x_attribute4 VARCHAR2,
284 x_attribute5 VARCHAR2,
285 x_attribute6 VARCHAR2,
286 x_attribute7 VARCHAR2,
287 x_attribute8 VARCHAR2,
288 x_attribute9 VARCHAR2,
289 x_attribute10 VARCHAR2,
290 x_attribute11 VARCHAR2,
291 x_attribute12 VARCHAR2,
292 x_attribute13 VARCHAR2,
293 x_attribute14 VARCHAR2,
294 x_attribute15 VARCHAR2,
295 x_attribute16 VARCHAR2,
296 x_attribute17 VARCHAR2,
297 x_attribute18 VARCHAR2,
298 x_attribute19 VARCHAR2,
299 x_attribute20 VARCHAR2,
300 x_attribute_category VARCHAR2,
301 x_created_by NUMBER,
302 x_creation_date DATE,
303 x_last_updated_by NUMBER,
304 x_last_update_date DATE,
305 x_last_update_login NUMBER,
306 x_org_id NUMBER,
307 x_enabled VARCHAR2,
308 x_formula_id number,
309 x_ghg_uom_class_code VARCHAR2,
310 x_energy_type VARCHAR2) IS
311
312 v_debug_info VARCHAR2(100);
313
314 CURSOR c IS
315 SELECT source_id,
316 source_name,
317 unit_of_measure,
318 attribute1,
319 attribute2,
320 attribute3,
321 attribute4,
322 attribute5,
323 attribute6,
324 attribute7,
325 attribute8,
326 attribute9,
327 attribute10,
328 attribute11,
329 attribute12,
330 attribute13,
331 attribute14,
332 attribute15,
333 attribute16,
334 attribute17,
335 attribute18,
336 attribute19,
337 attribute20,
338 attribute_category,
339 created_by,
340 creation_date,
341 last_updated_by,
342 last_update_date,
343 last_update_login,
344 org_id,
345 enabled,
346 formula_id,
347 ghg_uom_class_code,
348 energy_type
349 FROM GHG_SOURCES
350 WHERE rowid = x_rowid
351 FOR UPDATE of source_id NOWAIT;
352 recinfo C%ROWTYPE;
353
354 BEGIN
355
356 v_debug_info := 'Open cursor C';
357
358 OPEN C;
359
360 v_debug_info := 'Fetch cursor C';
361
362 FETCH C INTO recinfo;
363
364 IF (C%NOTFOUND) THEN
365 v_debug_info := 'Close cursor C - ROW NOT FOUND';
366 CLOSE C;
367 RAISE NO_DATA_FOUND;
368 END IF;
369
370 v_debug_info := 'Close cursor C';
371
372 CLOSE C;
373
374 IF ( ((recinfo.source_id = x_source_id) OR
375 (recinfo.source_id IS NULL AND x_source_id IS NULL))
376 AND ((recinfo.source_name = x_source_name) OR
377 (recinfo.source_name IS NULL AND x_source_name IS NULL))
378 AND ((recinfo.unit_of_measure = x_unit_of_measure) OR
379 (recinfo.unit_of_measure IS NULL AND x_unit_of_measure IS NULL))
380 AND ((recinfo.ghg_uom_class_code= x_ghg_uom_class_code) OR
381 (recinfo.ghg_uom_class_code IS NULL AND x_ghg_uom_class_code IS NULL))
382 AND ((recinfo.energy_type= x_energy_type) OR
383 (recinfo.energy_type IS NULL AND x_energy_type IS NULL))
384 AND ((recinfo.attribute1 = x_attribute1) OR
385 (recinfo.attribute1 IS NULL AND x_attribute1 IS NULL))
386 AND ((recinfo.attribute2 = x_attribute2) OR
387 (recinfo.attribute2 IS NULL AND x_attribute2 IS NULL))
388 AND ((recinfo.attribute3 = x_attribute3) OR
389 (recinfo.attribute3 IS NULL AND x_attribute3 IS NULL))
390 AND ((recinfo.attribute4 = x_attribute4) OR
391 (recinfo.attribute4 IS NULL AND x_attribute4 IS NULL))
392 AND ((recinfo.attribute5 = x_attribute5) OR
393 (recinfo.attribute5 IS NULL AND x_attribute5 IS NULL))
394 AND ((recinfo.attribute6 = x_attribute6) OR
395 (recinfo.attribute6 IS NULL AND x_attribute6 IS NULL))
396 AND ((recinfo.attribute7 = x_attribute7) OR
397 (recinfo.attribute7 IS NULL AND x_attribute7 IS NULL))
398 AND ((recinfo.attribute8 = x_attribute8) OR
399 (recinfo.attribute8 IS NULL AND x_attribute8 IS NULL))
400 AND ((recinfo.attribute9 = x_attribute9) OR
401 (recinfo.attribute9 IS NULL AND x_attribute9 IS NULL))
402 AND ((recinfo.attribute10 = x_attribute10) OR
403 (recinfo.attribute10 IS NULL AND x_attribute10 IS NULL))
404 AND ((recinfo.attribute11 = x_attribute11) OR
405 (recinfo.attribute11 IS NULL AND x_attribute11 IS NULL))
406 AND ((recinfo.attribute12 = x_attribute12) OR
407 (recinfo.attribute12 IS NULL AND x_attribute12 IS NULL))
408 AND ((recinfo.attribute13 = x_attribute13) OR
409 (recinfo.attribute13 IS NULL AND x_attribute13 IS NULL))
410 AND ((recinfo.attribute14 = x_attribute14) OR
411 (recinfo.attribute14 IS NULL AND x_attribute14 IS NULL))
412 AND ((recinfo.attribute15 = x_attribute15) OR
413 (recinfo.attribute15 IS NULL AND x_attribute15 IS NULL))
414 AND ((recinfo.attribute16 = x_attribute16) OR
415 (recinfo.attribute16 IS NULL AND x_attribute16 IS NULL))
416 AND ((recinfo.attribute17 = x_attribute17) OR
417 (recinfo.attribute17 IS NULL AND x_attribute17 IS NULL))
418 AND ((recinfo.attribute18 = x_attribute18) OR
419 (recinfo.attribute18 IS NULL AND x_attribute18 IS NULL))
420 AND ((recinfo.attribute19 = x_attribute19) OR
421 (recinfo.attribute19 IS NULL AND x_attribute19 IS NULL))
422 AND ((recinfo.attribute20 = x_attribute20) OR
423 (recinfo.attribute20 IS NULL AND x_attribute20 IS NULL))
424 AND ((recinfo.attribute_category = x_attribute_category) OR
425 (recinfo.attribute_category IS NULL AND
426 x_attribute_category IS NULL))
427 AND ((recinfo.created_by = x_created_by) OR
428 (recinfo.created_by IS NULL AND x_created_by IS NULL))
429 AND ((recinfo.creation_date = x_creation_date) OR
430 (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
431 AND ((recinfo.last_updated_by = x_last_updated_by) OR
432 (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
433 AND ((recinfo.last_update_date = x_last_update_date) OR
434 (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
435 AND ((recinfo.org_id = x_org_id) OR
436 (recinfo.org_id IS NULL AND x_org_id IS NULL))
437 AND ((recinfo.enabled = x_enabled) OR
438 (recinfo.enabled IS NULL AND x_enabled IS NULL))
439 AND ((recinfo.formula_id = x_formula_id) OR
440 (recinfo.formula_id IS NULL AND x_formula_id IS NULL))
441 AND ((recinfo.last_update_login = x_last_update_login) OR
442 (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)))
443 THEN
444 NULL;
445 ELSE
446 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
447 APP_EXCEPTION.RAISE_EXCEPTION;
448 END IF;
449
450 EXCEPTION
451 WHEN NO_DATA_FOUND THEN
452 NULL;
453
454 WHEN OTHERS THEN
455 IF (SQLCODE <> -20001) THEN
456 IF (SQLCODE = -54) THEN
457 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
458 ELSE
459 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
460 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
461 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
462 END IF;
463 END IF;
464 APP_EXCEPTION.RAISE_EXCEPTION;
465
466 END lock_row;
467
468 FUNCTION source_in_use (x_source_id NUMBER) RETURN BOOLEAN IS
469 v_row_count number;
470 BEGIN
471 SELECT count(*)
472 into v_row_count
473 FROM GHG_SOURCE_COMBINATIONS_ALL
474 where source_id = x_source_id;
475
476 IF v_row_count > 0 THEN
477 RETURN TRUE;
478 ELSE
479 RETURN FALSE;
480 END IF;
481 END;
482
483
484 FUNCTION source_exists (x_source_name VARCHAR2, x_source_id NUMBER, x_org_id NUMBER)
485 RETURN BOOLEAN IS
486 v_row_count number;
487 BEGIN
488 SELECT count(*)
489 into v_row_count
490 FROM GHG_SOURCES_ALL
491 where source_name = x_source_name
492 and org_id = x_org_id
493 and source_id <> NVL(x_source_id,-1);
494
495 IF v_row_count > 0 THEN
496 RETURN TRUE;
497 ELSE
498 RETURN FALSE;
499 END IF;
500 END;
501
502 FUNCTION uom_in_valid_class (x_uom VARCHAR2, x_ghg_uom_class VARCHAR2)
503 RETURN BOOLEAN IS
504 v_row_count number;
505 BEGIN
506
507 SELECT Count(*)
508 INTO v_row_count
509 FROM MTL_UNITS_OF_MEASURE_TL
510 WHERE UNIT_OF_MEASURE = x_uom
511 AND UOM_CLASS IN
512 (SELECT UOM_CLASS
513 FROM MTL_UNITS_OF_MEASURE
514 WHERE UNIT_OF_MEASURE IN
515 (SELECT STANDARD_UOM
516 FROM GHG_UOM_CLASSES_ALL
517 WHERE GHG_UOM_CLASS_CODE=x_ghg_uom_class
518 )
519 );
520
521 IF v_row_count > 0 THEN
522 RETURN TRUE;
523 ELSE
524 RETURN FALSE;
525 END IF;
526 END;
527
528
529 FUNCTION generate_source_id RETURN NUMBER IS
530 v_source_id NUMBER;
531 BEGIN
532 select GHG_SOURCES_S.NEXTVAL
533 into v_source_id
534 from dual;
535
536 RETURN v_source_id;
537 END;
538
539 END GHG_SOURCES_PKG;