[Home] [Help]
PACKAGE BODY: APPS.GHG_ORG_ACCOUNT_MAPPINGS_PKG
Source
1 PACKAGE BODY GHG_ORG_ACCOUNT_MAPPINGS_PKG AS
2 /*$Header: ghgoramb.pls 120.1.12020000.2 2013/02/27 16:20:52 mgijare ship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_facility_mapping_id NUMBER,
6 x_facility_id NUMBER,
7 x_segment1_low VARCHAR2,
8 x_segment1_high VARCHAR2,
9 x_segment2_low VARCHAR2,
10 x_segment2_high VARCHAR2,
11 x_segment3_low VARCHAR2,
12 x_segment3_high VARCHAR2,
13 x_segment4_low VARCHAR2,
14 x_segment4_high VARCHAR2,
15 x_segment5_low VARCHAR2,
16 x_segment5_high VARCHAR2,
17 x_segment6_low VARCHAR2,
18 x_segment6_high VARCHAR2,
19 x_segment7_low VARCHAR2,
20 x_segment7_high VARCHAR2,
21 x_segment8_low VARCHAR2,
22 x_segment8_high VARCHAR2,
23 x_segment9_low VARCHAR2,
24 x_segment9_high VARCHAR2,
25 x_segment10_low VARCHAR2,
26 x_segment10_high VARCHAR2,
27 x_segment11_low VARCHAR2,
28 x_segment11_high VARCHAR2,
29 x_segment12_low VARCHAR2,
30 x_segment12_high VARCHAR2,
31 x_segment13_low VARCHAR2,
32 x_segment13_high VARCHAR2,
33 x_segment14_low VARCHAR2,
34 x_segment14_high VARCHAR2,
35 x_segment15_low VARCHAR2,
36 x_segment15_high VARCHAR2,
37 x_segment16_low VARCHAR2,
38 x_segment16_high VARCHAR2,
39 x_segment17_low VARCHAR2,
40 x_segment17_high VARCHAR2,
41 x_segment18_low VARCHAR2,
42 x_segment18_high VARCHAR2,
43 x_segment19_low VARCHAR2,
44 x_segment19_high VARCHAR2,
45 x_segment20_low VARCHAR2,
46 x_segment20_high VARCHAR2,
47 x_segment21_low VARCHAR2,
48 x_segment21_high VARCHAR2,
49 x_segment22_low VARCHAR2,
50 x_segment22_high VARCHAR2,
51 x_segment23_low VARCHAR2,
52 x_segment23_high VARCHAR2,
53 x_segment24_low VARCHAR2,
54 x_segment24_high VARCHAR2,
55 x_segment25_low VARCHAR2,
56 x_segment25_high VARCHAR2,
57 x_segment26_low VARCHAR2,
58 x_segment26_high VARCHAR2,
59 x_segment27_low VARCHAR2,
60 x_segment27_high VARCHAR2,
61 x_segment28_low VARCHAR2,
62 x_segment28_high VARCHAR2,
63 x_segment29_low VARCHAR2,
64 x_segment29_high VARCHAR2,
65 x_segment30_low VARCHAR2,
66 x_segment30_high VARCHAR2,
67 x_chart_of_accounts_id NUMBER,
68 x_org_id NUMBER,
69 x_set_of_books_id NUMBER,
70 x_start_date DATE,
71 x_end_date DATE,
72 x_created_by NUMBER,
73 x_creation_date DATE,
74 x_last_updated_by NUMBER,
75 x_last_update_date DATE,
76 x_last_update_login NUMBER) IS
77
78 v_debug_info VARCHAR2(100);
79
80 CURSOR c IS
81 SELECT ROWID
82 FROM GHG_org_account_mappings_all
83 WHERE account_mapping_id = x_facility_mapping_id;
84
85 BEGIN
86
87 v_debug_info := 'Inserting into GHG_org_account_mappings_all';
88
89 INSERT INTO GHG_ORG_ACCOUNT_MAPPINGS_all (account_mapping_id,
90 ghg_organization_id,
91 segment1_low,
92 segment1_high,
93 segment2_low,
94 segment2_high,
95 segment3_low,
96 segment3_high,
97 segment4_low,
98 segment4_high,
99 segment5_low,
100 segment5_high,
101 segment6_low,
102 segment6_high,
103 segment7_low,
104 segment7_high,
105 segment8_low,
106 segment8_high,
107 segment9_low,
108 segment9_high,
109 segment10_low,
110 segment10_high,
111 segment11_low,
112 segment11_high,
113 segment12_low,
114 segment12_high,
115 segment13_low,
116 segment13_high,
117 segment14_low,
118 segment14_high,
119 segment15_low,
120 segment15_high,
121 segment16_low,
122 segment16_high,
123 segment17_low,
124 segment17_high,
125 segment18_low,
126 segment18_high,
127 segment19_low,
128 segment19_high,
129 segment20_low,
130 segment20_high,
131 segment21_low,
132 segment21_high,
133 segment22_low,
134 segment22_high,
135 segment23_low,
136 segment23_high,
137 segment24_low,
138 segment24_high,
139 segment25_low,
140 segment25_high,
141 segment26_low,
142 segment26_high,
143 segment27_low,
144 segment27_high,
145 segment28_low,
146 segment28_high,
147 segment29_low,
148 segment29_high,
149 segment30_low,
150 segment30_high,
151 chart_of_accounts_id,
152 org_id,
153 set_of_books_id,
154 start_date,
155 end_date,
156 created_by,
157 creation_date,
158 last_updated_by,
159 last_update_date,
160 last_update_login)
161 VALUES (x_facility_mapping_id,
162 x_facility_id,
163 x_segment1_low,
164 x_segment1_high,
165 x_segment2_low,
166 x_segment2_high,
167 x_segment3_low,
168 x_segment3_high,
169 x_segment4_low,
170 x_segment4_high,
171 x_segment5_low,
172 x_segment5_high,
173 x_segment6_low,
174 x_segment6_high,
175 x_segment7_low,
176 x_segment7_high,
177 x_segment8_low,
178 x_segment8_high,
179 x_segment9_low,
180 x_segment9_high,
181 x_segment10_low,
182 x_segment10_high,
183 x_segment11_low,
184 x_segment11_high,
185 x_segment12_low,
186 x_segment12_high,
187 x_segment13_low,
188 x_segment13_high,
189 x_segment14_low,
190 x_segment14_high,
191 x_segment15_low,
192 x_segment15_high,
193 x_segment16_low,
194 x_segment16_high,
195 x_segment17_low,
196 x_segment17_high,
197 x_segment18_low,
198 x_segment18_high,
199 x_segment19_low,
200 x_segment19_high,
201 x_segment20_low,
202 x_segment20_high,
203 x_segment21_low,
204 x_segment21_high,
205 x_segment22_low,
206 x_segment22_high,
207 x_segment23_low,
208 x_segment23_high,
209 x_segment24_low,
210 x_segment24_high,
211 x_segment25_low,
212 x_segment25_high,
213 x_segment26_low,
214 x_segment26_high,
215 x_segment27_low,
216 x_segment27_high,
217 x_segment28_low,
218 x_segment28_high,
219 x_segment29_low,
220 x_segment29_high,
221 x_segment30_low,
222 x_segment30_high,
223 x_chart_of_accounts_id,
224 x_org_id,
225 x_set_of_books_id,
226 x_start_date,
227 x_end_date,
228 x_created_by,
229 x_creation_date,
230 x_last_updated_by,
231 x_last_update_date,
232 x_last_update_login);
233
234 v_debug_info := 'Open cursor c';
235 OPEN c;
236
237 v_debug_info := 'Fetch cursor c';
238 FETCH c INTO x_rowid;
239
240 IF (c%notfound) THEN
241 v_debug_info := 'Close cursor c - ROW NOT FOUND';
242 CLOSE c;
243 RAISE no_data_found;
244 END IF;
245
246 v_debug_info := 'Close cursor c';
247 CLOSE c;
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 IF (SQLCODE <> -20001) THEN
252 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
253 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
254 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
255 END IF;
256 APP_EXCEPTION.RAISE_EXCEPTION;
257
258 END insert_row;
259
260 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
261 x_facility_mapping_id NUMBER,
262 x_facility_id NUMBER,
263 x_segment1_low VARCHAR2,
264 x_segment1_high VARCHAR2,
265 x_segment2_low VARCHAR2,
266 x_segment2_high VARCHAR2,
267 x_segment3_low VARCHAR2,
268 x_segment3_high VARCHAR2,
269 x_segment4_low VARCHAR2,
270 x_segment4_high VARCHAR2,
271 x_segment5_low VARCHAR2,
272 x_segment5_high VARCHAR2,
273 x_segment6_low VARCHAR2,
274 x_segment6_high VARCHAR2,
275 x_segment7_low VARCHAR2,
276 x_segment7_high VARCHAR2,
277 x_segment8_low VARCHAR2,
278 x_segment8_high VARCHAR2,
279 x_segment9_low VARCHAR2,
280 x_segment9_high VARCHAR2,
281 x_segment10_low VARCHAR2,
282 x_segment10_high VARCHAR2,
283 x_segment11_low VARCHAR2,
284 x_segment11_high VARCHAR2,
285 x_segment12_low VARCHAR2,
286 x_segment12_high VARCHAR2,
287 x_segment13_low VARCHAR2,
288 x_segment13_high VARCHAR2,
289 x_segment14_low VARCHAR2,
290 x_segment14_high VARCHAR2,
291 x_segment15_low VARCHAR2,
292 x_segment15_high VARCHAR2,
293 x_segment16_low VARCHAR2,
294 x_segment16_high VARCHAR2,
295 x_segment17_low VARCHAR2,
296 x_segment17_high VARCHAR2,
297 x_segment18_low VARCHAR2,
298 x_segment18_high VARCHAR2,
299 x_segment19_low VARCHAR2,
300 x_segment19_high VARCHAR2,
301 x_segment20_low VARCHAR2,
302 x_segment20_high VARCHAR2,
303 x_segment21_low VARCHAR2,
304 x_segment21_high VARCHAR2,
305 x_segment22_low VARCHAR2,
306 x_segment22_high VARCHAR2,
307 x_segment23_low VARCHAR2,
308 x_segment23_high VARCHAR2,
309 x_segment24_low VARCHAR2,
310 x_segment24_high VARCHAR2,
311 x_segment25_low VARCHAR2,
312 x_segment25_high VARCHAR2,
313 x_segment26_low VARCHAR2,
314 x_segment26_high VARCHAR2,
315 x_segment27_low VARCHAR2,
316 x_segment27_high VARCHAR2,
317 x_segment28_low VARCHAR2,
318 x_segment28_high VARCHAR2,
319 x_segment29_low VARCHAR2,
320 x_segment29_high VARCHAR2,
321 x_segment30_low VARCHAR2,
322 x_segment30_high VARCHAR2,
323 x_chart_of_accounts_id NUMBER,
324 x_org_id NUMBER,
325 x_set_of_books_id NUMBER,
326 x_start_date DATE,
327 x_end_date DATE,
328 x_created_by NUMBER,
329 x_creation_date DATE,
330 x_last_updated_by NUMBER,
331 x_last_update_date DATE,
332 x_last_update_login NUMBER) IS
333
334 v_debug_info VARCHAR2(100);
335
336 BEGIN
337
338 v_debug_info := 'Updating GHG_ORG_ACCOUNT_MAPPINGS_all';
339
340 UPDATE GHG_org_account_mappings_all
341 SET account_mapping_id = x_facility_mapping_id,
342 ghg_organization_id = x_facility_id,
343 segment1_low = x_segment1_low,
344 segment1_high = x_segment1_low,
345 segment2_low = x_segment2_low,
346 segment2_high = x_segment2_low,
347 segment3_low = x_segment3_low,
348 segment3_high = x_segment3_low,
349 segment4_low = x_segment4_low,
350 segment4_high = x_segment4_low,
351 segment5_low = x_segment5_low,
352 segment5_high = x_segment5_low,
353 segment6_low = x_segment6_low,
354 segment6_high = x_segment6_low,
355 segment7_low = x_segment7_low,
356 segment7_high = x_segment7_low,
357 segment8_low = x_segment8_low,
358 segment8_high = x_segment8_low,
359 segment9_low = x_segment9_low,
360 segment9_high = x_segment9_low,
361 segment10_low = x_segment10_low,
362 segment10_high = x_segment10_low,
363 segment11_low = x_segment11_low,
364 segment11_high = x_segment11_low,
365 segment12_low = x_segment12_low,
366 segment12_high = x_segment12_low,
367 segment13_low = x_segment13_low,
368 segment13_high = x_segment13_low,
369 segment14_low = x_segment14_low,
370 segment14_high = x_segment14_low,
371 segment15_low = x_segment15_low,
372 segment15_high = x_segment15_low,
373 segment16_low = x_segment16_low,
374 segment16_high = x_segment16_low,
375 segment17_low = x_segment17_low,
376 segment17_high = x_segment17_low,
377 segment18_low = x_segment18_low,
378 segment18_high = x_segment18_low,
379 segment19_low = x_segment19_low,
380 segment19_high = x_segment19_low,
381 segment20_low = x_segment20_low,
382 segment20_high = x_segment20_low,
383 segment21_low = x_segment21_low,
384 segment21_high = x_segment21_low,
385 segment22_low = x_segment22_low,
386 segment22_high = x_segment22_low,
387 segment23_low = x_segment23_low,
388 segment23_high = x_segment23_low,
389 segment24_low = x_segment24_low,
390 segment24_high = x_segment24_low,
391 segment25_low = x_segment25_low,
392 segment25_high = x_segment25_low,
393 segment26_low = x_segment26_low,
394 segment26_high = x_segment26_low,
395 segment27_low = x_segment27_low,
396 segment27_high = x_segment27_low,
397 segment28_low = x_segment28_low,
398 segment28_high = x_segment28_low,
399 segment29_low = x_segment29_low,
400 segment29_high = x_segment29_low,
401 segment30_low = x_segment30_low,
402 segment30_high = x_segment30_low,
403 chart_of_accounts_id = x_chart_of_accounts_id,
404 org_id = x_org_id,
405 set_of_books_id = x_set_of_books_id,
406 start_date = x_start_date,
407 end_date = x_end_date,
408 created_by = x_created_by,
409 creation_date = x_creation_date,
410 last_updated_by = x_last_updated_by,
411 last_update_date = x_last_update_date,
412 last_update_login = x_last_update_login
413 WHERE rowid = x_rowid;
414
415 IF (SQL%NOTFOUND) THEN
416 RAISE NO_DATA_FOUND;
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF (SQLCODE <> -20001) THEN
422 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
423 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
424 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
425 END IF;
426 APP_EXCEPTION.RAISE_EXCEPTION;
427
428 END update_row;
429
430 PROCEDURE delete_row (x_facility_mapping_id NUMBER) IS
431
432 v_row_count NUMBER(15);
433
434 BEGIN
435
436 DELETE FROM GHG_org_account_mappings_all
437 WHERE account_mapping_id = x_facility_mapping_id;
438
439 IF (sql%notfound) THEN
440 RAISE no_data_found;
441 END IF;
442
443 END delete_row;
444
445 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
446 x_facility_mapping_id NUMBER,
447 x_facility_id NUMBER,
448 x_segment1_low VARCHAR2,
449 x_segment1_high VARCHAR2,
450 x_segment2_low VARCHAR2,
451 x_segment2_high VARCHAR2,
452 x_segment3_low VARCHAR2,
453 x_segment3_high VARCHAR2,
454 x_segment4_low VARCHAR2,
455 x_segment4_high VARCHAR2,
456 x_segment5_low VARCHAR2,
457 x_segment5_high VARCHAR2,
458 x_segment6_low VARCHAR2,
459 x_segment6_high VARCHAR2,
460 x_segment7_low VARCHAR2,
461 x_segment7_high VARCHAR2,
462 x_segment8_low VARCHAR2,
463 x_segment8_high VARCHAR2,
464 x_segment9_low VARCHAR2,
465 x_segment9_high VARCHAR2,
466 x_segment10_low VARCHAR2,
467 x_segment10_high VARCHAR2,
468 x_segment11_low VARCHAR2,
469 x_segment11_high VARCHAR2,
470 x_segment12_low VARCHAR2,
471 x_segment12_high VARCHAR2,
472 x_segment13_low VARCHAR2,
473 x_segment13_high VARCHAR2,
474 x_segment14_low VARCHAR2,
475 x_segment14_high VARCHAR2,
476 x_segment15_low VARCHAR2,
477 x_segment15_high VARCHAR2,
478 x_segment16_low VARCHAR2,
479 x_segment16_high VARCHAR2,
480 x_segment17_low VARCHAR2,
481 x_segment17_high VARCHAR2,
482 x_segment18_low VARCHAR2,
483 x_segment18_high VARCHAR2,
484 x_segment19_low VARCHAR2,
485 x_segment19_high VARCHAR2,
486 x_segment20_low VARCHAR2,
487 x_segment20_high VARCHAR2,
488 x_segment21_low VARCHAR2,
489 x_segment21_high VARCHAR2,
490 x_segment22_low VARCHAR2,
491 x_segment22_high VARCHAR2,
492 x_segment23_low VARCHAR2,
493 x_segment23_high VARCHAR2,
494 x_segment24_low VARCHAR2,
495 x_segment24_high VARCHAR2,
496 x_segment25_low VARCHAR2,
497 x_segment25_high VARCHAR2,
498 x_segment26_low VARCHAR2,
499 x_segment26_high VARCHAR2,
500 x_segment27_low VARCHAR2,
501 x_segment27_high VARCHAR2,
502 x_segment28_low VARCHAR2,
503 x_segment28_high VARCHAR2,
504 x_segment29_low VARCHAR2,
505 x_segment29_high VARCHAR2,
506 x_segment30_low VARCHAR2,
507 x_segment30_high VARCHAR2,
508 x_chart_of_accounts_id NUMBER,
509 x_org_id NUMBER,
510 x_set_of_books_id NUMBER,
511 x_start_date DATE,
512 x_end_date DATE,
513 x_created_by NUMBER,
514 x_creation_date DATE,
515 x_last_updated_by NUMBER,
516 x_last_update_date DATE,
517 x_last_update_login NUMBER) IS
518
519 v_debug_info VARCHAR2(100);
520
521 CURSOR c IS
522 SELECT account_mapping_id,
523 ghg_organization_id,
524 segment1_low,
525 segment1_high,
526 segment2_low,
527 segment2_high,
528 segment3_low,
529 segment3_high,
530 segment4_low,
531 segment4_high,
532 segment5_low,
533 segment5_high,
534 segment6_low,
535 segment6_high,
536 segment7_low,
537 segment7_high,
538 segment8_low,
539 segment8_high,
540 segment9_low,
541 segment9_high,
542 segment10_low,
543 segment10_high,
544 segment11_low,
545 segment11_high,
546 segment12_low,
547 segment12_high,
548 segment13_low,
549 segment13_high,
550 segment14_low,
551 segment14_high,
552 segment15_low,
553 segment15_high,
554 segment16_low,
555 segment16_high,
556 segment17_low,
557 segment17_high,
558 segment18_low,
559 segment18_high,
560 segment19_low,
561 segment19_high,
562 segment20_low,
563 segment20_high,
564 segment21_low,
565 segment21_high,
566 segment22_low,
567 segment22_high,
568 segment23_low,
569 segment23_high,
570 segment24_low,
571 segment24_high,
572 segment25_low,
573 segment25_high,
574 segment26_low,
575 segment26_high,
576 segment27_low,
577 segment27_high,
578 segment28_low,
579 segment28_high,
580 segment29_low,
581 segment29_high,
582 segment30_low,
583 segment30_high,
584 chart_of_accounts_id,
585 org_id,
586 set_of_books_id,
587 start_date,
588 end_date,
589 created_by,
590 creation_date,
591 last_updated_by,
592 last_update_date,
593 last_update_login
594 FROM GHG_org_account_mappings_all
595 WHERE rowid = x_rowid
596 FOR UPDATE of account_mapping_id NOWAIT;
597 recinfo C%ROWTYPE;
598
599 BEGIN
600
601 v_debug_info := 'Open cursor C';
602
603 OPEN C;
604
605 v_debug_info := 'Fetch cursor C';
606
607 FETCH C INTO recinfo;
608
609 IF (C%NOTFOUND) THEN
610 v_debug_info := 'Close cursor C - ROW NOT FOUND';
611 CLOSE C;
612 RAISE NO_DATA_FOUND;
613 END IF;
614
615 v_debug_info := 'Close cursor C';
616
617 CLOSE C;
618
619 IF ( ((recinfo.account_mapping_id = x_facility_mapping_id) OR (recinfo.account_mapping_id IS NULL AND x_facility_mapping_id IS NULL))
620 AND ((recinfo.ghg_organization_id = x_facility_id) OR (recinfo.ghg_organization_id IS NULL AND x_facility_id IS NULL))
621 AND ((recinfo.segment1_low = x_segment1_low) OR (recinfo.segment1_low IS NULL AND x_segment1_low IS NULL))
622 AND ((recinfo.segment1_high = x_segment1_high) OR (recinfo.segment1_high IS NULL AND x_segment1_high IS NULL))
623 AND ((recinfo.segment2_low = x_segment2_low) OR (recinfo.segment2_low IS NULL AND x_segment2_low IS NULL))
624 AND ((recinfo.segment2_high = x_segment2_high) OR (recinfo.segment2_high IS NULL AND x_segment2_high IS NULL))
625 AND ((recinfo.segment3_low = x_segment3_low) OR (recinfo.segment3_low IS NULL AND x_segment3_low IS NULL))
626 AND ((recinfo.segment3_high = x_segment3_high) OR (recinfo.segment3_high IS NULL AND x_segment3_high IS NULL))
627 AND ((recinfo.segment4_low = x_segment4_low) OR (recinfo.segment4_low IS NULL AND x_segment4_low IS NULL))
628 AND ((recinfo.segment4_high = x_segment4_high) OR (recinfo.segment4_high IS NULL AND x_segment4_high IS NULL))
629 AND ((recinfo.segment5_low = x_segment5_low) OR (recinfo.segment5_low IS NULL AND x_segment5_low IS NULL))
630 AND ((recinfo.segment5_high = x_segment5_high) OR (recinfo.segment5_high IS NULL AND x_segment5_high IS NULL))
631 AND ((recinfo.segment6_low = x_segment6_low) OR (recinfo.segment6_low IS NULL AND x_segment6_low IS NULL))
632 AND ((recinfo.segment6_high = x_segment6_high) OR (recinfo.segment6_high IS NULL AND x_segment6_high IS NULL))
633 AND ((recinfo.segment7_low = x_segment7_low) OR (recinfo.segment7_low IS NULL AND x_segment7_low IS NULL))
634 AND ((recinfo.segment7_high = x_segment7_high) OR (recinfo.segment7_high IS NULL AND x_segment7_high IS NULL))
635 AND ((recinfo.segment8_low = x_segment8_low) OR (recinfo.segment8_low IS NULL AND x_segment8_low IS NULL))
636 AND ((recinfo.segment8_high = x_segment8_high) OR (recinfo.segment8_high IS NULL AND x_segment8_high IS NULL))
637 AND ((recinfo.segment9_low = x_segment9_low) OR (recinfo.segment9_low IS NULL AND x_segment9_low IS NULL))
638 AND ((recinfo.segment9_high = x_segment9_high) OR (recinfo.segment9_high IS NULL AND x_segment9_high IS NULL))
639 AND ((recinfo.segment10_low = x_segment10_low) OR (recinfo.segment10_low IS NULL AND x_segment10_low IS NULL))
640 AND ((recinfo.segment10_high = x_segment10_high) OR (recinfo.segment10_high IS NULL AND x_segment10_high IS NULL))
641 AND ((recinfo.segment11_low = x_segment11_low) OR (recinfo.segment11_low IS NULL AND x_segment11_low IS NULL))
642 AND ((recinfo.segment11_high = x_segment11_high) OR (recinfo.segment11_high IS NULL AND x_segment11_high IS NULL))
643 AND ((recinfo.segment12_low = x_segment12_low) OR (recinfo.segment12_low IS NULL AND x_segment12_low IS NULL))
644 AND ((recinfo.segment12_high = x_segment12_high) OR (recinfo.segment12_high IS NULL AND x_segment12_high IS NULL))
645 AND ((recinfo.segment13_low = x_segment13_low) OR (recinfo.segment13_low IS NULL AND x_segment13_low IS NULL))
646 AND ((recinfo.segment13_high = x_segment13_high) OR (recinfo.segment13_high IS NULL AND x_segment13_high IS NULL))
647 AND ((recinfo.segment14_low = x_segment14_low) OR (recinfo.segment14_low IS NULL AND x_segment14_low IS NULL))
648 AND ((recinfo.segment14_high = x_segment14_high) OR (recinfo.segment14_high IS NULL AND x_segment14_high IS NULL))
649 AND ((recinfo.segment15_low = x_segment15_low) OR (recinfo.segment15_low IS NULL AND x_segment15_low IS NULL))
650 AND ((recinfo.segment15_high = x_segment15_high) OR (recinfo.segment15_high IS NULL AND x_segment15_high IS NULL))
651 AND ((recinfo.segment16_low = x_segment16_low) OR (recinfo.segment16_low IS NULL AND x_segment16_low IS NULL))
652 AND ((recinfo.segment16_high = x_segment16_high) OR (recinfo.segment16_high IS NULL AND x_segment16_high IS NULL))
653 AND ((recinfo.segment17_low = x_segment17_low) OR (recinfo.segment17_low IS NULL AND x_segment17_low IS NULL))
654 AND ((recinfo.segment17_high = x_segment17_high) OR (recinfo.segment17_high IS NULL AND x_segment17_high IS NULL))
655 AND ((recinfo.segment18_low = x_segment18_low) OR (recinfo.segment18_low IS NULL AND x_segment18_low IS NULL))
656 AND ((recinfo.segment18_high = x_segment18_high) OR (recinfo.segment18_high IS NULL AND x_segment18_high IS NULL))
657 AND ((recinfo.segment19_low = x_segment19_low) OR (recinfo.segment19_low IS NULL AND x_segment19_low IS NULL))
658 AND ((recinfo.segment19_high = x_segment19_high) OR (recinfo.segment19_high IS NULL AND x_segment19_high IS NULL))
659 AND ((recinfo.segment20_low = x_segment20_low) OR (recinfo.segment20_low IS NULL AND x_segment20_low IS NULL))
660 AND ((recinfo.segment20_high = x_segment20_high) OR (recinfo.segment20_high IS NULL AND x_segment20_high IS NULL))
661 AND ((recinfo.segment21_low = x_segment21_low) OR (recinfo.segment21_low IS NULL AND x_segment21_low IS NULL))
662 AND ((recinfo.segment21_high = x_segment21_high) OR (recinfo.segment21_high IS NULL AND x_segment21_high IS NULL))
663 AND ((recinfo.segment22_low = x_segment22_low) OR (recinfo.segment22_low IS NULL AND x_segment22_low IS NULL))
664 AND ((recinfo.segment22_high = x_segment22_high) OR (recinfo.segment22_high IS NULL AND x_segment22_high IS NULL))
665 AND ((recinfo.segment23_low = x_segment23_low) OR (recinfo.segment23_low IS NULL AND x_segment23_low IS NULL))
666 AND ((recinfo.segment23_high = x_segment23_high) OR (recinfo.segment23_high IS NULL AND x_segment23_high IS NULL))
667 AND ((recinfo.segment24_low = x_segment24_low) OR (recinfo.segment24_low IS NULL AND x_segment24_low IS NULL))
668 AND ((recinfo.segment24_high = x_segment24_high) OR (recinfo.segment24_high IS NULL AND x_segment24_high IS NULL))
669 AND ((recinfo.segment25_low = x_segment25_low) OR (recinfo.segment25_low IS NULL AND x_segment25_low IS NULL))
670 AND ((recinfo.segment25_high = x_segment25_high) OR (recinfo.segment25_high IS NULL AND x_segment25_high IS NULL))
671 AND ((recinfo.segment26_low = x_segment26_low) OR (recinfo.segment26_low IS NULL AND x_segment26_low IS NULL))
672 AND ((recinfo.segment26_high = x_segment26_high) OR (recinfo.segment26_high IS NULL AND x_segment26_high IS NULL))
673 AND ((recinfo.segment27_low = x_segment27_low) OR (recinfo.segment27_low IS NULL AND x_segment27_low IS NULL))
674 AND ((recinfo.segment27_high = x_segment27_high) OR (recinfo.segment27_high IS NULL AND x_segment27_high IS NULL))
675 AND ((recinfo.segment28_low = x_segment28_low) OR (recinfo.segment28_low IS NULL AND x_segment28_low IS NULL))
676 AND ((recinfo.segment28_high = x_segment28_high) OR (recinfo.segment28_high IS NULL AND x_segment28_high IS NULL))
677 AND ((recinfo.segment29_low = x_segment29_low) OR (recinfo.segment29_low IS NULL AND x_segment29_low IS NULL))
678 AND ((recinfo.segment29_high = x_segment29_high) OR (recinfo.segment29_high IS NULL AND x_segment29_high IS NULL))
679 AND ((recinfo.segment30_low = x_segment30_low) OR (recinfo.segment30_low IS NULL AND x_segment30_low IS NULL))
680 AND ((recinfo.segment30_high = x_segment30_high) OR (recinfo.segment30_high IS NULL AND x_segment30_high IS NULL))
681 AND ((recinfo.chart_of_accounts_id = x_chart_of_accounts_id) OR (recinfo.chart_of_accounts_id IS NULL AND x_chart_of_accounts_id IS NULL))
682 AND ((recinfo.org_id = x_org_id) OR (recinfo.org_id IS NULL AND x_org_id IS NULL))
683 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))
684 AND ((recinfo.start_date = x_start_date) OR (recinfo.start_date IS NULL AND x_start_date IS NULL))
685 AND ((recinfo.end_date = x_end_date) OR (recinfo.end_date IS NULL AND x_end_date IS NULL))
686 AND ((recinfo.created_by = x_created_by) OR (recinfo.created_by IS NULL AND x_created_by IS NULL))
687 AND ((recinfo.creation_date = x_creation_date) OR (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
688 AND ((recinfo.last_updated_by = x_last_updated_by) OR (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
689 AND ((recinfo.last_update_date = x_last_update_date) OR (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
690 AND ((recinfo.last_update_login = x_last_update_login) OR (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)) ) THEN
691 NULL;
692 ELSE
693 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
694 APP_EXCEPTION.RAISE_EXCEPTION;
695 END IF;
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 IF (SQLCODE <> -20001) THEN
700 IF (SQLCODE = -54) THEN
701 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
702 ELSE
703 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
704 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
705 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
706 END IF;
707 END IF;
708 APP_EXCEPTION.RAISE_EXCEPTION;
709
710 END lock_row;
711
712 FUNCTION get_mapping_count(v_facility_mapping_id NUMBER) RETURN NUMBER
713 IS
714
715 CURSOR get_mapping_segments(v_chart_of_accounts_id NUMBER) IS
716 SELECT application_column_name
717 FROM fnd_id_flex_segments_vl
718 WHERE id_flex_num = v_chart_of_accounts_id
719 AND id_flex_code = 'GL#'
720 ORDER BY segment_num;
721
722 v_row_count NUMBER(15);
723 v_chart_of_acc_id NUMBER;
724 v_sqltext VARCHAR2(2000) := null;
725
726 BEGIN
727
728 SELECT chart_of_accounts_id
729 INTO v_chart_of_acc_id
730 FROM ghg_org_account_mappings_all
731 WHERE account_mapping_id = v_facility_mapping_id;
732
733 v_sqltext := 'SELECT count(*) FROM gl_code_combinations gc, ap_invoice_distributions_all ai, ghg_transactions_all gt, ghg_org_account_mappings_all ga ';
734 v_sqltext := v_sqltext || 'WHERE gc.code_combination_id = ai.dist_code_combination_id AND ai.invoice_id = gt.invoice_id ';
735 v_sqltext := v_sqltext || 'AND gt.ghg_organization_id = ga.ghg_organization_id AND gt.invoice_id IS NOT NULL AND gc.chart_of_accounts_id = ga.chart_of_accounts_id ';
736 v_sqltext := v_sqltext || 'AND ga.account_mapping_id = '||v_facility_mapping_id;
737
738 FOR map_rec IN get_mapping_segments(v_chart_of_acc_id)
739 LOOP
740 v_sqltext := v_sqltext ||' AND gc.'||map_rec.application_column_name||' BETWEEN NVL(ga.'||map_rec.application_column_name||'_LOW,gc.'||map_rec.application_column_name||') ';
741 v_sqltext := v_sqltext ||'AND Nvl(ga.'||map_rec.application_column_name||'_HIGH,gc.'||map_rec.application_column_name||') ';
742 END LOOP;
743
744 EXECUTE IMMEDIATE v_sqltext INTO v_row_count;
745
746 RETURN v_row_count;
747 END;
748
749 END GHG_ORG_ACCOUNT_MAPPINGS_PKG;