[Home] [Help]
PACKAGE BODY: APPS.GHG_SEGMENT_MAPPINGS_PKG
Source
1 PACKAGE BODY GHG_SEGMENT_MAPPINGS_PKG AS
2 /*$Header: ghgsgmpb.pls 120.1 2011/10/31 08:46:46 pwaghmar noship $ */
3
4
5 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
6 x_map_accounts_flag VARCHAR2,
7 x_map_operating_unit_flag VARCHAR2,
8 x_map_projects_flag VARCHAR2,
9 x_map_segment1_flag VARCHAR2,
10 x_map_segment2_flag VARCHAR2,
11 x_map_segment3_flag VARCHAR2,
12 x_map_segment4_flag VARCHAR2,
13 x_map_segment5_flag VARCHAR2,
14 x_map_segment6_flag VARCHAR2,
15 x_map_segment7_flag VARCHAR2,
16 x_map_segment8_flag VARCHAR2,
17 x_map_segment9_flag VARCHAR2,
18 x_map_segment10_flag VARCHAR2,
19 x_map_segment11_flag VARCHAR2,
20 x_map_segment12_flag VARCHAR2,
21 x_map_segment13_flag VARCHAR2,
22 x_map_segment14_flag VARCHAR2,
23 x_map_segment15_flag VARCHAR2,
24 x_map_segment16_flag VARCHAR2,
25 x_map_segment17_flag VARCHAR2,
26 x_map_segment18_flag VARCHAR2,
27 x_map_segment19_flag VARCHAR2,
28 x_map_segment20_flag VARCHAR2,
29 x_map_segment21_flag VARCHAR2,
30 x_map_segment22_flag VARCHAR2,
31 x_map_segment23_flag VARCHAR2,
32 x_map_segment24_flag VARCHAR2,
33 x_map_segment25_flag VARCHAR2,
34 x_map_segment26_flag VARCHAR2,
35 x_map_segment27_flag VARCHAR2,
36 x_map_segment28_flag VARCHAR2,
37 x_map_segment29_flag VARCHAR2,
38 x_map_segment30_flag VARCHAR2,
39 x_org_id NUMBER,
40 x_set_of_books_id NUMBER,
41 x_created_by NUMBER,
42 x_creation_date DATE,
43 x_last_updated_by NUMBER,
44 x_last_update_date DATE,
45 x_last_update_login NUMBER) IS
46
47 v_debug_info VARCHAR2(100);
48
49 CURSOR c IS
50 SELECT ROWID
51 FROM GHG_SEGMENT_MAPPINGS_ALL
52 WHERE org_id = x_org_id;
53
54 BEGIN
55
56 v_debug_info := 'Inserting into GHG_SEGMENT_MAPPINGS_ALL';
57
58 INSERT INTO GHG_SEGMENT_MAPPINGS_ALL (map_accounts_flag,
59 map_operating_unit_flag,
60 map_projects_flag,
61 map_segment1_flag,
62 map_segment2_flag,
63 map_segment3_flag,
64 map_segment4_flag,
65 map_segment5_flag,
66 map_segment6_flag,
67 map_segment7_flag,
68 map_segment8_flag,
69 map_segment9_flag,
70 map_segment10_flag,
71 map_segment11_flag,
72 map_segment12_flag,
73 map_segment13_flag,
74 map_segment14_flag,
75 map_segment15_flag,
76 map_segment16_flag,
77 map_segment17_flag,
78 map_segment18_flag,
79 map_segment19_flag,
80 map_segment20_flag,
81 map_segment21_flag,
82 map_segment22_flag,
83 map_segment23_flag,
84 map_segment24_flag,
85 map_segment25_flag,
86 map_segment26_flag,
87 map_segment27_flag,
88 map_segment28_flag,
89 map_segment29_flag,
90 map_segment30_flag,
91 org_id,
92 set_of_books_id,
93 created_by,
94 creation_date,
95 last_updated_by,
96 last_update_date,
97 last_update_login)
98 VALUES (x_map_accounts_flag,
99 x_map_operating_unit_flag,
100 x_map_projects_flag,
101 x_map_segment1_flag,
102 x_map_segment2_flag,
103 x_map_segment3_flag,
104 x_map_segment4_flag,
105 x_map_segment5_flag,
106 x_map_segment6_flag,
107 x_map_segment7_flag,
108 x_map_segment8_flag,
109 x_map_segment9_flag,
110 x_map_segment10_flag,
111 x_map_segment11_flag,
112 x_map_segment12_flag,
113 x_map_segment13_flag,
114 x_map_segment14_flag,
115 x_map_segment15_flag,
116 x_map_segment16_flag,
117 x_map_segment17_flag,
118 x_map_segment18_flag,
119 x_map_segment19_flag,
120 x_map_segment20_flag,
121 x_map_segment21_flag,
122 x_map_segment22_flag,
123 x_map_segment23_flag,
124 x_map_segment24_flag,
125 x_map_segment25_flag,
126 x_map_segment26_flag,
127 x_map_segment27_flag,
128 x_map_segment28_flag,
129 x_map_segment29_flag,
130 x_map_segment30_flag,
131 x_org_id,
132 x_set_of_books_id,
133 x_created_by,
134 x_creation_date,
135 x_last_updated_by,
136 x_last_update_date,
137 x_last_update_login);
138
139 v_debug_info := 'Open cursor c';
140 OPEN c;
141
142 v_debug_info := 'Fetch cursor c';
143 FETCH c INTO x_rowid;
144
145 IF (c%notfound) THEN
146 v_debug_info := 'Close cursor c - ROW NOT FOUND';
147 CLOSE c;
148 RAISE no_data_found;
149 END IF;
150
151 v_debug_info := 'Close cursor c';
152 CLOSE c;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 IF (SQLCODE <> -20001) THEN
157 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
158 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
159 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
160 END IF;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162
163 END insert_row;
164
165 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
166 x_map_accounts_flag VARCHAR2,
167 x_map_operating_unit_flag VARCHAR2,
168 x_map_projects_flag VARCHAR2,
169 x_map_segment1_flag VARCHAR2,
170 x_map_segment2_flag VARCHAR2,
171 x_map_segment3_flag VARCHAR2,
172 x_map_segment4_flag VARCHAR2,
173 x_map_segment5_flag VARCHAR2,
174 x_map_segment6_flag VARCHAR2,
175 x_map_segment7_flag VARCHAR2,
176 x_map_segment8_flag VARCHAR2,
177 x_map_segment9_flag VARCHAR2,
178 x_map_segment10_flag VARCHAR2,
179 x_map_segment11_flag VARCHAR2,
180 x_map_segment12_flag VARCHAR2,
181 x_map_segment13_flag VARCHAR2,
182 x_map_segment14_flag VARCHAR2,
183 x_map_segment15_flag VARCHAR2,
184 x_map_segment16_flag VARCHAR2,
185 x_map_segment17_flag VARCHAR2,
186 x_map_segment18_flag VARCHAR2,
187 x_map_segment19_flag VARCHAR2,
188 x_map_segment20_flag VARCHAR2,
189 x_map_segment21_flag VARCHAR2,
190 x_map_segment22_flag VARCHAR2,
191 x_map_segment23_flag VARCHAR2,
192 x_map_segment24_flag VARCHAR2,
193 x_map_segment25_flag VARCHAR2,
194 x_map_segment26_flag VARCHAR2,
195 x_map_segment27_flag VARCHAR2,
196 x_map_segment28_flag VARCHAR2,
197 x_map_segment29_flag VARCHAR2,
198 x_map_segment30_flag VARCHAR2,
199 x_org_id NUMBER,
200 x_set_of_books_id NUMBER, -- added 13-Mar-09 for R12
201 x_created_by NUMBER,
202 x_creation_date DATE,
203 x_last_updated_by NUMBER,
204 x_last_update_date DATE,
205 x_last_update_login NUMBER) IS
206
207 v_debug_info VARCHAR2(100);
208
209 BEGIN
210
211 v_debug_info := 'Updating GHG_SEGMENT_MAPPINGS_ALL';
212
213 UPDATE GHG_SEGMENT_MAPPINGS_ALL
214 SET map_accounts_flag = x_map_accounts_flag,
215 map_operating_unit_flag = x_map_operating_unit_flag,
216 map_projects_flag = x_map_projects_flag,
217 map_segment1_flag = x_map_segment1_flag,
218 map_segment2_flag = x_map_segment2_flag,
219 map_segment3_flag = x_map_segment3_flag,
220 map_segment4_flag = x_map_segment4_flag,
221 map_segment5_flag = x_map_segment5_flag,
222 map_segment6_flag = x_map_segment6_flag,
223 map_segment7_flag = x_map_segment7_flag,
224 map_segment8_flag = x_map_segment8_flag,
225 map_segment9_flag = x_map_segment9_flag,
226 map_segment10_flag = x_map_segment10_flag,
227 map_segment11_flag = x_map_segment11_flag,
228 map_segment12_flag = x_map_segment12_flag,
229 map_segment13_flag = x_map_segment13_flag,
230 map_segment14_flag = x_map_segment14_flag,
231 map_segment15_flag = x_map_segment15_flag,
232 map_segment16_flag = x_map_segment16_flag,
233 map_segment17_flag = x_map_segment17_flag,
234 map_segment18_flag = x_map_segment18_flag,
235 map_segment19_flag = x_map_segment19_flag,
236 map_segment20_flag = x_map_segment20_flag,
237 map_segment21_flag = x_map_segment21_flag,
238 map_segment22_flag = x_map_segment22_flag,
239 map_segment23_flag = x_map_segment23_flag,
240 map_segment24_flag = x_map_segment24_flag,
241 map_segment25_flag = x_map_segment25_flag,
242 map_segment26_flag = x_map_segment26_flag,
243 map_segment27_flag = x_map_segment27_flag,
244 map_segment28_flag = x_map_segment28_flag,
245 map_segment29_flag = x_map_segment29_flag,
246 map_segment30_flag = x_map_segment30_flag,
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 WHERE rowid = x_rowid;
255
256 IF (SQL%NOTFOUND) THEN
257 RAISE NO_DATA_FOUND;
258 END IF;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 IF (SQLCODE <> -20001) THEN
263 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
264 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
265 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
266 END IF;
267 APP_EXCEPTION.RAISE_EXCEPTION;
268
269 END update_row;
270
271 PROCEDURE delete_row (x_org_id NUMBER) IS
272
273 v_row_count NUMBER(15);
274
275 BEGIN
276
277 DELETE FROM GHG_SEGMENT_MAPPINGS_ALL
278 WHERE org_id = x_org_id;
279
280 IF (sql%notfound) THEN
281 RAISE no_data_found;
282 END IF;
283
284 END delete_row;
285
286 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
287 x_map_accounts_flag VARCHAR2,
288 x_map_operating_unit_flag VARCHAR2,
289 x_map_projects_flag VARCHAR2,
290 x_map_segment1_flag VARCHAR2,
291 x_map_segment2_flag VARCHAR2,
292 x_map_segment3_flag VARCHAR2,
293 x_map_segment4_flag VARCHAR2,
294 x_map_segment5_flag VARCHAR2,
295 x_map_segment6_flag VARCHAR2,
296 x_map_segment7_flag VARCHAR2,
297 x_map_segment8_flag VARCHAR2,
298 x_map_segment9_flag VARCHAR2,
299 x_map_segment10_flag VARCHAR2,
300 x_map_segment11_flag VARCHAR2,
301 x_map_segment12_flag VARCHAR2,
302 x_map_segment13_flag VARCHAR2,
303 x_map_segment14_flag VARCHAR2,
304 x_map_segment15_flag VARCHAR2,
305 x_map_segment16_flag VARCHAR2,
306 x_map_segment17_flag VARCHAR2,
307 x_map_segment18_flag VARCHAR2,
308 x_map_segment19_flag VARCHAR2,
309 x_map_segment20_flag VARCHAR2,
310 x_map_segment21_flag VARCHAR2,
311 x_map_segment22_flag VARCHAR2,
312 x_map_segment23_flag VARCHAR2,
313 x_map_segment24_flag VARCHAR2,
314 x_map_segment25_flag VARCHAR2,
315 x_map_segment26_flag VARCHAR2,
316 x_map_segment27_flag VARCHAR2,
317 x_map_segment28_flag VARCHAR2,
318 x_map_segment29_flag VARCHAR2,
319 x_map_segment30_flag VARCHAR2,
320 x_org_id NUMBER,
321 x_set_of_books_id NUMBER, -- added 13-Mar-09 for R12
322 x_created_by NUMBER,
323 x_creation_date DATE,
324 x_last_updated_by NUMBER,
325 x_last_update_date DATE,
326 x_last_update_login NUMBER) IS
327
328 v_debug_info VARCHAR2(100);
329
330 CURSOR c IS
331 SELECT map_accounts_flag,
332 map_operating_unit_flag,
333 map_projects_flag,
334 map_segment1_flag,
335 map_segment2_flag,
336 map_segment3_flag,
337 map_segment4_flag,
338 map_segment5_flag,
339 map_segment6_flag,
340 map_segment7_flag,
341 map_segment8_flag,
342 map_segment9_flag,
343 map_segment10_flag,
344 map_segment11_flag,
345 map_segment12_flag,
346 map_segment13_flag,
347 map_segment14_flag,
348 map_segment15_flag,
349 map_segment16_flag,
350 map_segment17_flag,
351 map_segment18_flag,
352 map_segment19_flag,
353 map_segment20_flag,
354 map_segment21_flag,
355 map_segment22_flag,
356 map_segment23_flag,
357 map_segment24_flag,
358 map_segment25_flag,
359 map_segment26_flag,
360 map_segment27_flag,
361 map_segment28_flag,
362 map_segment29_flag,
363 map_segment30_flag,
364 org_id,
365 set_of_books_id,
366 created_by,
367 creation_date,
368 last_updated_by,
369 last_update_date,
370 last_update_login
371 FROM GHG_SEGMENT_MAPPINGS_ALL
372 WHERE rowid = x_rowid
373 FOR UPDATE of org_id NOWAIT;
374 recinfo C%ROWTYPE;
375
376 BEGIN
377
378 v_debug_info := 'Open cursor C';
379
380 OPEN C;
381
382 v_debug_info := 'Fetch cursor C';
383
384 FETCH C INTO recinfo;
385
386 IF (C%NOTFOUND) THEN
387 v_debug_info := 'Close cursor C - ROW NOT FOUND';
388 CLOSE C;
389 RAISE NO_DATA_FOUND;
390 END IF;
391
392 v_debug_info := 'Close cursor C';
393
394 CLOSE C;
395
396 IF ( ((recinfo.map_accounts_flag = x_map_accounts_flag) OR
397 (recinfo.map_accounts_flag IS NULL AND x_map_accounts_flag IS NULL))
398 AND ((recinfo.map_operating_unit_flag = x_map_operating_unit_flag) OR
399 (recinfo.map_operating_unit_flag IS NULL AND
400 x_map_operating_unit_flag IS NULL))
401 AND ((recinfo.map_projects_flag = x_map_projects_flag) OR
402 (recinfo.map_projects_flag IS NULL AND x_map_projects_flag IS NULL))
403 AND ((recinfo.map_segment1_flag = x_map_segment1_flag) OR
404 (recinfo.map_segment1_flag IS NULL AND x_map_segment1_flag IS NULL))
405 AND ((recinfo.map_segment2_flag = x_map_segment2_flag) OR
406 (recinfo.map_segment2_flag IS NULL AND x_map_segment2_flag IS NULL))
407 AND ((recinfo.map_segment3_flag = x_map_segment3_flag) OR
408 (recinfo.map_segment3_flag IS NULL AND x_map_segment3_flag IS NULL))
409 AND ((recinfo.map_segment4_flag = x_map_segment4_flag) OR
410 (recinfo.map_segment4_flag IS NULL AND x_map_segment4_flag IS NULL))
411 AND ((recinfo.map_segment5_flag = x_map_segment5_flag) OR
412 (recinfo.map_segment5_flag IS NULL AND x_map_segment5_flag IS NULL))
413 AND ((recinfo.map_segment6_flag = x_map_segment6_flag) OR
414 (recinfo.map_segment6_flag IS NULL AND x_map_segment6_flag IS NULL))
415 AND ((recinfo.map_segment7_flag = x_map_segment7_flag) OR
416 (recinfo.map_segment7_flag IS NULL AND x_map_segment7_flag IS NULL))
417 AND ((recinfo.map_segment8_flag = x_map_segment8_flag) OR
418 (recinfo.map_segment8_flag IS NULL AND x_map_segment8_flag IS NULL))
419 AND ((recinfo.map_segment9_flag = x_map_segment9_flag) OR
420 (recinfo.map_segment9_flag IS NULL AND x_map_segment9_flag IS NULL))
421 AND ((recinfo.map_segment10_flag = x_map_segment10_flag) OR
422 (recinfo.map_segment10_flag IS NULL AND
423 x_map_segment10_flag IS NULL))
424 AND ((recinfo.map_segment11_flag = x_map_segment11_flag) OR
425 (recinfo.map_segment11_flag IS NULL AND
426 x_map_segment11_flag IS NULL))
427 AND ((recinfo.map_segment12_flag = x_map_segment12_flag) OR
428 (recinfo.map_segment12_flag IS NULL AND
429 x_map_segment12_flag IS NULL))
430 AND ((recinfo.map_segment13_flag = x_map_segment13_flag) OR
431 (recinfo.map_segment13_flag IS NULL AND
432 x_map_segment13_flag IS NULL))
433 AND ((recinfo.map_segment14_flag = x_map_segment14_flag) OR
434 (recinfo.map_segment14_flag IS NULL AND
435 x_map_segment14_flag IS NULL))
436 AND ((recinfo.map_segment15_flag = x_map_segment15_flag) OR
437 (recinfo.map_segment15_flag IS NULL AND
438 x_map_segment15_flag IS NULL))
439 AND ((recinfo.map_segment16_flag = x_map_segment16_flag) OR
440 (recinfo.map_segment16_flag IS NULL AND
441 x_map_segment16_flag IS NULL))
442 AND ((recinfo.map_segment17_flag = x_map_segment17_flag) OR
443 (recinfo.map_segment17_flag IS NULL AND
444 x_map_segment17_flag IS NULL))
445 AND ((recinfo.map_segment18_flag = x_map_segment18_flag) OR
446 (recinfo.map_segment18_flag IS NULL AND
447 x_map_segment18_flag IS NULL))
448 AND ((recinfo.map_segment19_flag = x_map_segment19_flag) OR
449 (recinfo.map_segment19_flag IS NULL AND
450 x_map_segment19_flag IS NULL))
451 AND ((recinfo.map_segment20_flag = x_map_segment20_flag) OR
452 (recinfo.map_segment20_flag IS NULL AND
453 x_map_segment20_flag IS NULL))
454 AND ((recinfo.map_segment21_flag = x_map_segment21_flag) OR
455 (recinfo.map_segment21_flag IS NULL AND
456 x_map_segment21_flag IS NULL))
457 AND ((recinfo.map_segment22_flag = x_map_segment22_flag) OR
458 (recinfo.map_segment22_flag IS NULL AND
459 x_map_segment22_flag IS NULL))
460 AND ((recinfo.map_segment23_flag = x_map_segment23_flag) OR
461 (recinfo.map_segment23_flag IS NULL AND
462 x_map_segment23_flag IS NULL))
463 AND ((recinfo.map_segment24_flag = x_map_segment24_flag) OR
464 (recinfo.map_segment24_flag IS NULL AND
465 x_map_segment24_flag IS NULL))
466 AND ((recinfo.map_segment25_flag = x_map_segment25_flag) OR
467 (recinfo.map_segment25_flag IS NULL AND
468 x_map_segment25_flag IS NULL))
469 AND ((recinfo.map_segment26_flag = x_map_segment26_flag) OR
470 (recinfo.map_segment26_flag IS NULL AND
471 x_map_segment26_flag IS NULL))
472 AND ((recinfo.map_segment27_flag = x_map_segment27_flag) OR
473 (recinfo.map_segment27_flag IS NULL AND
474 x_map_segment27_flag IS NULL))
475 AND ((recinfo.map_segment28_flag = x_map_segment28_flag) OR
476 (recinfo.map_segment28_flag IS NULL AND
477 x_map_segment28_flag IS NULL))
478 AND ((recinfo.map_segment29_flag = x_map_segment29_flag) OR
479 (recinfo.map_segment29_flag IS NULL AND
480 x_map_segment29_flag IS NULL))
481 AND ((recinfo.map_segment30_flag = x_map_segment30_flag) OR
482 (recinfo.map_segment30_flag IS NULL AND
483 x_map_segment30_flag IS NULL))
484 AND ((recinfo.org_id = x_org_id) OR
485 (recinfo.org_id IS NULL AND x_org_id IS NULL))
486 AND ((recinfo.set_of_books_id = x_set_of_books_id) OR
487 (recinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
488 AND ((recinfo.created_by = x_created_by) OR
489 (recinfo.created_by IS NULL AND x_created_by IS NULL))
490 AND ((recinfo.creation_date = x_creation_date) OR
491 (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
492 AND ((recinfo.last_updated_by = x_last_updated_by) OR
493 (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
494 AND ((recinfo.last_update_date = x_last_update_date) OR
495 (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
496 AND ((recinfo.last_update_login = x_last_update_login) OR
497 (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)))
498 THEN
499 NULL;
500 ELSE
501 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
502 APP_EXCEPTION.RAISE_EXCEPTION;
503 END IF;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF (SQLCODE <> -20001) THEN
508 IF (SQLCODE = -54) THEN
509 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
510 ELSE
511 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
512 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
513 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
514 END IF;
515 END IF;
516 APP_EXCEPTION.RAISE_EXCEPTION;
517
518 END lock_row;
519
520 END GHG_SEGMENT_MAPPINGS_PKG;