[Home] [Help]
PACKAGE BODY: APPS.GHG_ORGANIZATIONS_PKG
Source
1 PACKAGE BODY GHG_ORGANIZATIONS_PKG AS
2 /*$Header: ghgorgsb.pls 120.1 2011/10/31 08:52:49 pwaghmar noship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_facility_id NUMBER,
6 x_facility_code VARCHAR2,
7 x_description VARCHAR2,
8 x_facility_size_lookup_code VARCHAR2,
9 x_location_id NUMBER,
10 x_start_date DATE,
11 x_end_date DATE,
12 x_attribute1 VARCHAR2,
13 x_attribute2 VARCHAR2,
14 x_attribute3 VARCHAR2,
15 x_attribute4 VARCHAR2,
16 x_attribute5 VARCHAR2,
17 x_attribute6 VARCHAR2,
18 x_attribute7 VARCHAR2,
19 x_attribute8 VARCHAR2,
20 x_attribute9 VARCHAR2,
21 x_attribute10 VARCHAR2,
22 x_attribute11 VARCHAR2,
23 x_attribute12 VARCHAR2,
24 x_attribute13 VARCHAR2,
25 x_attribute14 VARCHAR2,
26 x_attribute15 VARCHAR2,
27 x_attribute16 VARCHAR2,
28 x_attribute17 VARCHAR2,
29 x_attribute18 VARCHAR2,
30 x_attribute19 VARCHAR2,
31 x_attribute20 VARCHAR2,
32 x_attribute_category VARCHAR2,
33 x_org_id NUMBER,
34 x_set_of_books_id NUMBER,
35 x_created_by NUMBER,
36 x_creation_date DATE,
37 x_last_updated_by NUMBER,
38 x_last_update_date DATE,
39 x_last_update_login NUMBER,
40 x_latitude NUMBER,
41 x_longitude NUMBER,
42 x_facility_type VARCHAR2,
43 x_allow_transactions VARCHAR2,
44 x_ceo_name VARCHAR2,
45 x_company_number VARCHAR2
46 ) IS
47
48 v_debug_info VARCHAR2(100);
49
50 CURSOR c IS
51 SELECT ROWID
52 FROM GHG_organizations_all
53 WHERE ghg_organization_id = x_facility_id;
54
55 BEGIN
56
57 v_debug_info := 'Inserting into GHG_organizations_all';
58
59 INSERT INTO GHG_organizations_all (ghg_organization_id,
60 ghg_organization_code,
61 description,
62 organization_size_lookup_code,
63 location_id,
64 start_date,
65 end_date,
66 attribute1,
67 attribute2,
68 attribute3,
69 attribute4,
70 attribute5,
71 attribute6,
72 attribute7,
73 attribute8,
74 attribute9,
75 attribute10,
76 attribute11,
77 attribute12,
78 attribute13,
79 attribute14,
80 attribute15,
81 attribute16,
82 attribute17,
83 attribute18,
84 attribute19,
85 attribute20,
86 attribute_category,
87 org_id,
88 set_of_books_id,
89 created_by,
90 creation_date,
91 last_updated_by,
92 last_update_date,
93 last_update_login,
94 latitude,
95 longitude,
96 organization_type,
97 allow_transactions,
98 ceo_name,
99 company_identifier)
100 VALUES (x_facility_id,
101 x_facility_code,
102 x_description,
103 x_facility_size_lookup_code,
104 x_location_id,
105 x_start_date,
106 x_end_date,
107 x_attribute1,
108 x_attribute2,
109 x_attribute3,
110 x_attribute4,
111 x_attribute5,
112 x_attribute6,
113 x_attribute7,
114 x_attribute8,
115 x_attribute9,
116 x_attribute10,
117 x_attribute11,
118 x_attribute12,
119 x_attribute13,
120 x_attribute14,
121 x_attribute15,
122 x_attribute16,
123 x_attribute17,
124 x_attribute18,
125 x_attribute19,
126 x_attribute20,
127 x_attribute_category,
128 x_org_id,
129 x_set_of_books_id,
130 x_created_by,
131 x_creation_date,
132 x_last_updated_by,
133 x_last_update_date,
134 x_last_update_login,
135 x_latitude,
136 x_longitude,
137 x_facility_type,
138 x_allow_transactions,
139 x_ceo_name,
140 x_company_number);
141
142 v_debug_info := 'Open cursor c';
143 OPEN c;
144
145 v_debug_info := 'Fetch cursor c';
146 FETCH c INTO x_rowid;
147
148 IF (c%notfound) THEN
149 v_debug_info := 'Close cursor c - ROW NOT FOUND';
150 CLOSE c;
151 RAISE no_data_found;
152 END IF;
153
154 v_debug_info := 'Close cursor c';
155 CLOSE c;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 IF (SQLCODE <> -20001) THEN
160 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
161 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
162 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
163 END IF;
164 APP_EXCEPTION.RAISE_EXCEPTION;
165
166 END insert_row;
167
168 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
169 x_facility_id NUMBER,
170 x_facility_code VARCHAR2,
171 x_description VARCHAR2,
172 x_facility_size_lookup_code VARCHAR2,
173 x_location_id NUMBER,
174 x_start_date DATE,
175 x_end_date DATE,
176 x_attribute1 VARCHAR2,
177 x_attribute2 VARCHAR2,
178 x_attribute3 VARCHAR2,
179 x_attribute4 VARCHAR2,
180 x_attribute5 VARCHAR2,
181 x_attribute6 VARCHAR2,
182 x_attribute7 VARCHAR2,
183 x_attribute8 VARCHAR2,
184 x_attribute9 VARCHAR2,
185 x_attribute10 VARCHAR2,
186 x_attribute11 VARCHAR2,
187 x_attribute12 VARCHAR2,
188 x_attribute13 VARCHAR2,
189 x_attribute14 VARCHAR2,
190 x_attribute15 VARCHAR2,
191 x_attribute16 VARCHAR2,
192 x_attribute17 VARCHAR2,
193 x_attribute18 VARCHAR2,
194 x_attribute19 VARCHAR2,
195 x_attribute20 VARCHAR2,
196 x_attribute_category VARCHAR2,
197 x_org_id NUMBER,
198 x_set_of_books_id NUMBER,
199 x_created_by NUMBER,
200 x_creation_date DATE,
201 x_last_updated_by NUMBER,
202 x_last_update_date DATE,
203 x_last_update_login NUMBER,
204 x_latitude NUMBER,
205 x_longitude NUMBER,
206 x_facility_type VARCHAR2,
207 x_allow_transactions VARCHAR2,
208 x_ceo_name VARCHAR2,
209 x_company_number VARCHAR2
210 ) IS
211
212 v_debug_info VARCHAR2(100);
213
214 BEGIN
215
216 v_debug_info := 'Updating GHG_organizations_all';
217
218 UPDATE GHG_organizations_all
219 SET ghg_organization_id = x_facility_id,
220 ghg_organization_code = x_facility_code,
221 description = x_description,
222 organization_size_lookup_code = x_facility_size_lookup_code,
223 location_id = x_location_id,
224 start_date = x_start_date,
225 end_date = x_end_date,
226 attribute1 = x_attribute1,
227 attribute2 = x_attribute2,
228 attribute3 = x_attribute3,
229 attribute4 = x_attribute4,
230 attribute5 = x_attribute5,
231 attribute6 = x_attribute6,
232 attribute7 = x_attribute7,
233 attribute8 = x_attribute8,
234 attribute9 = x_attribute9,
235 attribute10 = x_attribute10,
236 attribute11 = x_attribute11,
237 attribute12 = x_attribute12,
238 attribute13 = x_attribute13,
239 attribute14 = x_attribute14,
240 attribute15 = x_attribute15,
241 attribute16 = x_attribute16,
242 attribute17 = x_attribute17,
243 attribute18 = x_attribute18,
244 attribute19 = x_attribute19,
245 attribute20 = x_attribute20,
246 attribute_category = x_attribute_category,
247 org_id = x_org_id,
248 set_of_books_id = x_set_of_books_id,
249 created_by = x_created_by,
250 creation_date = x_creation_date,
251 last_updated_by = x_last_updated_by,
252 last_update_date = x_last_update_date,
253 last_update_login = x_last_update_login,
254 latitude = x_latitude,
255 longitude = x_longitude,
256 organization_type = x_facility_type ,
257 allow_transactions = x_allow_transactions,
258 ceo_name = x_ceo_name,
259 company_identifier = x_company_number
260 WHERE rowid = x_rowid;
261
262 IF (SQL%NOTFOUND) THEN
263 RAISE NO_DATA_FOUND;
267 WHEN OTHERS THEN
264 END IF;
265
266 EXCEPTION
268 IF (SQLCODE <> -20001) THEN
269 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
270 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
271 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
272 END IF;
273 APP_EXCEPTION.RAISE_EXCEPTION;
274
275 END update_row;
276
277 PROCEDURE delete_row (x_facility_id NUMBER) IS
278
279 v_row_count NUMBER(15);
280
281 BEGIN
282
283 DELETE FROM GHG_organizations_all
284 WHERE ghg_organization_id = x_facility_id;
285
286 IF (sql%notfound) THEN
287 RAISE no_data_found;
288 END IF;
289
290 END delete_row;
291
292 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
293 x_facility_id NUMBER,
294 x_facility_code VARCHAR2,
295 x_description VARCHAR2,
296 x_facility_size_lookup_code VARCHAR2,
297 x_location_id NUMBER,
298 x_start_date DATE,
299 x_end_date DATE,
300 x_attribute1 VARCHAR2,
301 x_attribute2 VARCHAR2,
302 x_attribute3 VARCHAR2,
303 x_attribute4 VARCHAR2,
304 x_attribute5 VARCHAR2,
305 x_attribute6 VARCHAR2,
306 x_attribute7 VARCHAR2,
307 x_attribute8 VARCHAR2,
308 x_attribute9 VARCHAR2,
309 x_attribute10 VARCHAR2,
310 x_attribute11 VARCHAR2,
311 x_attribute12 VARCHAR2,
312 x_attribute13 VARCHAR2,
313 x_attribute14 VARCHAR2,
314 x_attribute15 VARCHAR2,
315 x_attribute16 VARCHAR2,
316 x_attribute17 VARCHAR2,
317 x_attribute18 VARCHAR2,
318 x_attribute19 VARCHAR2,
319 x_attribute20 VARCHAR2,
320 x_attribute_category VARCHAR2,
321 x_org_id NUMBER,
322 x_set_of_books_id NUMBER,
323 x_created_by NUMBER,
324 x_creation_date DATE,
325 x_last_updated_by NUMBER,
326 x_last_update_date DATE,
327 x_last_update_login NUMBER,
328 x_latitude NUMBER,
329 x_longitude NUMBER,
330 x_facility_type VARCHAR2,
331 x_allow_transactions VARCHAR2,
332 x_ceo_name VARCHAR2,
333 x_company_number VARCHAR2
334 ) IS
335
336 v_debug_info VARCHAR2(100);
337
338 CURSOR c IS
339 SELECT ghg_organization_id,
340 ghg_organization_code,
341 description,
342 organization_size_lookup_code,
343 location_id,
344 start_date,
345 end_date,
346 attribute1,
347 attribute2,
348 attribute3,
349 attribute4,
350 attribute5,
351 attribute6,
352 attribute7,
353 attribute8,
354 attribute9,
355 attribute10,
356 attribute11,
357 attribute12,
358 attribute13,
359 attribute14,
360 attribute15,
361 attribute16,
362 attribute17,
363 attribute18,
364 attribute19,
365 attribute20,
366 attribute_category,
367 org_id,
368 set_of_books_id,
369 created_by,
370 creation_date,
371 last_updated_by,
372 last_update_date,
373 last_update_login,
374 latitude,
375 longitude,
376 organization_type,
377 allow_transactions,
378 ceo_name,
379 company_identifier
380 FROM GHG_organizations_all
381 WHERE rowid = x_rowid
382 FOR UPDATE of ghg_organization_id NOWAIT;
383 recinfo C%ROWTYPE;
384
385 BEGIN
386
387 v_debug_info := 'Open cursor C';
388
389 OPEN C;
390
391 v_debug_info := 'Fetch cursor C';
392
393 FETCH C INTO recinfo;
394
395 IF (C%NOTFOUND) THEN
396 v_debug_info := 'Close cursor C - ROW NOT FOUND';
397 CLOSE C;
398 RAISE NO_DATA_FOUND;
399 END IF;
400
401 v_debug_info := 'Close cursor C';
402
403 CLOSE C;
404
405 IF ( ((recinfo.ghg_organization_id = x_facility_id) OR (recinfo.ghg_organization_id IS NULL AND x_facility_id IS NULL))
406 AND ((recinfo.ghg_organization_code = x_facility_code) OR (recinfo.ghg_organization_code IS NULL AND x_facility_code IS NULL))
407 AND ((recinfo.description = x_description) OR (recinfo.description IS NULL AND x_description IS NULL))
408 AND ((recinfo.organization_size_lookup_code = x_facility_size_lookup_code) OR (recinfo.organization_size_lookup_code IS NULL AND x_facility_size_lookup_code IS NULL))
409 AND ((recinfo.location_id = x_location_id) OR (recinfo.location_id IS NULL AND x_location_id IS NULL))
410 AND ((recinfo.start_date = x_start_date) OR (recinfo.start_date IS NULL AND x_start_date IS NULL))
411 AND ((recinfo.end_date = x_end_date) OR (recinfo.end_date IS NULL AND x_end_date IS NULL))
412 AND ((recinfo.attribute1 = x_attribute1) OR (recinfo.attribute1 IS NULL AND x_attribute1 IS NULL))
413 AND ((recinfo.attribute2 = x_attribute2) OR (recinfo.attribute2 IS NULL AND x_attribute2 IS NULL))
414 AND ((recinfo.attribute3 = x_attribute3) OR (recinfo.attribute3 IS NULL AND x_attribute3 IS NULL))
415 AND ((recinfo.attribute4 = x_attribute4) OR (recinfo.attribute4 IS NULL AND x_attribute4 IS NULL))
416 AND ((recinfo.attribute5 = x_attribute5) OR (recinfo.attribute5 IS NULL AND x_attribute5 IS NULL))
417 AND ((recinfo.attribute6 = x_attribute6) OR (recinfo.attribute6 IS NULL AND x_attribute6 IS NULL))
418 AND ((recinfo.attribute7 = x_attribute7) OR (recinfo.attribute7 IS NULL AND x_attribute7 IS NULL))
419 AND ((recinfo.attribute8 = x_attribute8) OR (recinfo.attribute8 IS NULL AND x_attribute8 IS NULL))
420 AND ((recinfo.attribute9 = x_attribute9) OR (recinfo.attribute9 IS NULL AND x_attribute9 IS NULL))
421 AND ((recinfo.attribute10 = x_attribute10) OR (recinfo.attribute10 IS NULL AND x_attribute10 IS NULL))
422 AND ((recinfo.attribute11 = x_attribute11) OR (recinfo.attribute11 IS NULL AND x_attribute11 IS NULL))
423 AND ((recinfo.attribute12 = x_attribute12) OR (recinfo.attribute12 IS NULL AND x_attribute12 IS NULL))
424 AND ((recinfo.attribute13 = x_attribute13) OR (recinfo.attribute13 IS NULL AND x_attribute13 IS NULL))
425 AND ((recinfo.attribute14 = x_attribute14) OR (recinfo.attribute14 IS NULL AND x_attribute14 IS NULL))
426 AND ((recinfo.attribute15 = x_attribute15) OR (recinfo.attribute15 IS NULL AND x_attribute15 IS NULL))
427 AND ((recinfo.attribute16 = x_attribute16) OR (recinfo.attribute16 IS NULL AND x_attribute16 IS NULL))
428 AND ((recinfo.attribute17 = x_attribute17) OR (recinfo.attribute17 IS NULL AND x_attribute17 IS NULL))
429 AND ((recinfo.attribute18 = x_attribute18) OR (recinfo.attribute18 IS NULL AND x_attribute18 IS NULL))
430 AND ((recinfo.attribute19 = x_attribute19) OR (recinfo.attribute19 IS NULL AND x_attribute19 IS NULL))
431 AND ((recinfo.attribute20 = x_attribute20) OR (recinfo.attribute20 IS NULL AND x_attribute20 IS NULL))
432 AND ((recinfo.attribute_category = x_attribute_category) OR (recinfo.attribute_category IS NULL AND x_attribute_category IS NULL))
433 AND ((recinfo.org_id = x_org_id) OR (recinfo.org_id IS NULL AND x_org_id IS NULL))
434 AND ((recinfo.set_of_books_id = x_set_of_books_id) OR (recinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
435 AND ((recinfo.created_by = x_created_by) OR (recinfo.created_by IS NULL AND x_created_by IS NULL))
436 AND ((recinfo.creation_date = x_creation_date) OR (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
437 AND ((recinfo.last_updated_by = x_last_updated_by) OR (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
438 AND ((recinfo.last_update_date = x_last_update_date) OR (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
439 AND ((recinfo.latitude = x_latitude) OR (recinfo.latitude IS NULL AND x_latitude IS NULL))
440 AND ((recinfo.longitude = x_longitude) OR (recinfo.longitude IS NULL AND x_longitude IS NULL))
441 AND ((recinfo.organization_type = x_facility_type) OR (recinfo.organization_type IS NULL AND x_facility_type IS NULL))
442 AND ((recinfo.allow_transactions = x_allow_transactions) OR (recinfo.allow_transactions IS NULL AND x_allow_transactions IS NULL))
443
444 AND ((recinfo.ceo_name = x_ceo_name) OR (recinfo.ceo_name IS NULL AND x_ceo_name IS NULL))
445 AND ((recinfo.company_identifier = x_company_number) OR (recinfo.company_identifier IS NULL AND x_company_number IS NULL))
446
447 AND ((recinfo.last_update_login = x_last_update_login) OR (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)) ) THEN
448 NULL;
449 ELSE
450 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
451 APP_EXCEPTION.RAISE_EXCEPTION;
452 END IF;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 IF (SQLCODE <> -20001) THEN
457 IF (SQLCODE = -54) THEN
458 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
459 ELSE
460 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
461 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
462 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
463 END IF;
464 END IF;
465 APP_EXCEPTION.RAISE_EXCEPTION;
466
467 END lock_row;
468
469 END GHG_ORGANIZATIONS_PKG;