[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_STMT_LDR_PKG
Source
1 PACKAGE BODY CE_BANK_STMT_LDR_PKG AS
2 /*$Header: cexsldrb.pls 120.4 2008/04/30 23:31:23 csutaria ship $ */
3
4 PROCEDURE Map_Insert_Row(
5 X_ROW_ID IN OUT NOCOPY VARCHAR2,
6 X_MAP_ID IN OUT NOCOPY NUMBER,
7 X_FORMAT_NAME VARCHAR2,
8 X_DESCRIPTION VARCHAR2,
9 X_FORMAT_TYPE VARCHAR2,
10 X_CONTROL_FILE_NAME VARCHAR2,
11 X_ENABLED VARCHAR2,
12 X_PRECISION NUMBER,
13 X_DATE_FORMAT VARCHAR2,
14 X_TIMESTAMP_FORMAT VARCHAR2,
15 X_CREATED_BY NUMBER,
16 X_CREATION_DATE DATE,
17 X_LAST_UPDATED_BY NUMBER,
18 X_LAST_UPDATE_DATE DATE,
19 X_LAST_UPDATE_LOGIN NUMBER,
20 X_ATTRIBUTE_CATEGORY VARCHAR2,
21 X_ATTRIBUTE1 VARCHAR2,
22 X_ATTRIBUTE2 VARCHAR2,
23 X_ATTRIBUTE3 VARCHAR2,
24 X_ATTRIBUTE4 VARCHAR2,
25 X_ATTRIBUTE5 VARCHAR2,
26 X_ATTRIBUTE6 VARCHAR2,
27 X_ATTRIBUTE7 VARCHAR2,
28 X_ATTRIBUTE8 VARCHAR2,
29 X_ATTRIBUTE9 VARCHAR2,
30 X_ATTRIBUTE10 VARCHAR2,
31 X_ATTRIBUTE11 VARCHAR2,
32 X_ATTRIBUTE12 VARCHAR2,
33 X_ATTRIBUTE13 VARCHAR2,
34 X_ATTRIBUTE14 VARCHAR2,
35 X_ATTRIBUTE15 VARCHAR2) IS
36
37 CURSOR C_map_id IS
38 SELECT CE_BANK_STMT_INT_MAP_S.nextval
39 FROM sys.dual;
40
41 CURSOR C_row_id IS
42 SELECT rowid
43 FROM ce_bank_stmt_int_map
44 WHERE map_id = TO_NUMBER(X_MAP_ID);
45 BEGIN
46 OPEN C_map_id;
47 FETCH C_map_id
48 INTO X_MAP_ID;
49 CLOSE C_map_id;
50
51 INSERT INTO ce_bank_stmt_int_map(
52 MAP_ID,
53 FORMAT_NAME,
54 DESCRIPTION,
55 FORMAT_TYPE,
56 CONTROL_FILE_NAME,
57 ENABLED,
58 PRECISION,
59 DATE_FORMAT,
60 TIMESTAMP_FORMAT,
61 CREATED_BY,
62 CREATION_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN,
66 ATTRIBUTE_CATEGORY,
67 ATTRIBUTE1,
68 ATTRIBUTE2,
69 ATTRIBUTE3,
70 ATTRIBUTE4,
71 ATTRIBUTE5,
72 ATTRIBUTE6,
73 ATTRIBUTE7,
74 ATTRIBUTE8,
75 ATTRIBUTE9,
76 ATTRIBUTE10,
77 ATTRIBUTE11,
78 ATTRIBUTE12,
79 ATTRIBUTE13,
80 ATTRIBUTE14,
81 ATTRIBUTE15)
82 VALUES(
83 X_MAP_ID,
84 X_FORMAT_NAME,
85 X_DESCRIPTION,
86 X_FORMAT_TYPE,
87 X_CONTROL_FILE_NAME,
88 X_ENABLED,
89 X_PRECISION,
90 X_DATE_FORMAT,
91 X_TIMESTAMP_FORMAT,
92 X_CREATED_BY,
93 X_CREATION_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_DATE,
96 X_LAST_UPDATE_LOGIN,
97 X_ATTRIBUTE_CATEGORY,
98 X_ATTRIBUTE1,
99 X_ATTRIBUTE2,
100 X_ATTRIBUTE3,
101 X_ATTRIBUTE4,
102 X_ATTRIBUTE5,
103 X_ATTRIBUTE6,
104 X_ATTRIBUTE7,
105 X_ATTRIBUTE8,
106 X_ATTRIBUTE9,
107 X_ATTRIBUTE10,
108 X_ATTRIBUTE11,
109 X_ATTRIBUTE12,
110 X_ATTRIBUTE13,
111 X_ATTRIBUTE14,
112 X_ATTRIBUTE15);
113
114 OPEN C_row_id;
115 FETCH C_row_id INTO X_row_id;
116 if (C_row_id%NOTFOUND) then
117 CLOSE C_row_id;
118 Raise NO_DATA_FOUND;
119 end if;
120 CLOSE C_row_id;
121
122 END Map_Insert_Row;
123
124 PROCEDURE Map_Update_Row(
125 X_ROW_ID VARCHAR2,
126 X_MAP_ID NUMBER,
127 X_FORMAT_NAME VARCHAR2,
128 X_DESCRIPTION VARCHAR2,
129 X_FORMAT_TYPE VARCHAR2,
130 X_CONTROL_FILE_NAME VARCHAR2,
131 X_ENABLED VARCHAR2,
132 X_PRECISION NUMBER,
133 X_DATE_FORMAT VARCHAR2,
134 X_TIMESTAMP_FORMAT VARCHAR2,
135 X_CREATED_BY NUMBER,
136 X_CREATION_DATE DATE,
137 X_LAST_UPDATED_BY NUMBER,
138 X_LAST_UPDATE_DATE DATE,
139 X_LAST_UPDATE_LOGIN NUMBER,
140 X_ATTRIBUTE_CATEGORY VARCHAR2,
141 X_ATTRIBUTE1 VARCHAR2,
142 X_ATTRIBUTE2 VARCHAR2,
143 X_ATTRIBUTE3 VARCHAR2,
144 X_ATTRIBUTE4 VARCHAR2,
145 X_ATTRIBUTE5 VARCHAR2,
146 X_ATTRIBUTE6 VARCHAR2,
147 X_ATTRIBUTE7 VARCHAR2,
148 X_ATTRIBUTE8 VARCHAR2,
149 X_ATTRIBUTE9 VARCHAR2,
150 X_ATTRIBUTE10 VARCHAR2,
151 X_ATTRIBUTE11 VARCHAR2,
152 X_ATTRIBUTE12 VARCHAR2,
153 X_ATTRIBUTE13 VARCHAR2,
154 X_ATTRIBUTE14 VARCHAR2,
155 X_ATTRIBUTE15 VARCHAR2) IS
156 BEGIN
157 UPDATE ce_bank_stmt_int_map
158 SET map_id = X_map_id,
159 format_name = X_format_name,
160 description = X_description,
161 format_type = X_format_type,
162 control_file_name = X_control_file_name,
163 enabled = X_enabled,
164 precision = X_precision,
165 date_format = X_date_format,
166 timestamp_format = X_timestamp_format,
167 attribute_category = X_attribute_category,
168 attribute1 = X_attribute1,
169 attribute2 = X_attribute2,
170 attribute3 = X_attribute3,
171 attribute4 = X_attribute4,
172 attribute5 = X_attribute5,
173 attribute6 = X_attribute6,
174 attribute7 = X_attribute7,
175 attribute8 = X_attribute8,
176 attribute9 = X_attribute9,
177 attribute10 = X_attribute10,
178 attribute11 = X_attribute11,
179 attribute12 = X_attribute12,
180 attribute13 = X_attribute13,
181 attribute14 = X_attribute14,
182 attribute15 = X_attribute15,
183 last_updated_by = x_last_updated_by,
184 last_update_date = x_last_update_date,
185 last_update_login = x_last_update_login
186 WHERE rowid = X_row_id;
187 if (SQL%NOTFOUND) then
188 Raise NO_DATA_FOUND;
189 end if;
190 END Map_Update_Row;
191
192 PROCEDURE Map_Lock_Row(
193 X_ROW_ID VARCHAR2,
194 X_MAP_ID NUMBER,
195 X_FORMAT_NAME VARCHAR2,
196 X_DESCRIPTION VARCHAR2,
197 X_FORMAT_TYPE VARCHAR2,
198 X_CONTROL_FILE_NAME VARCHAR2,
199 X_ENABLED VARCHAR2,
200 X_PRECISION NUMBER,
201 X_DATE_FORMAT VARCHAR2,
202 X_TIMESTAMP_FORMAT VARCHAR2,
203 X_CREATED_BY NUMBER,
204 X_CREATION_DATE DATE,
205 X_LAST_UPDATED_BY NUMBER,
206 X_LAST_UPDATE_DATE DATE,
207 X_LAST_UPDATE_LOGIN NUMBER,
208 X_ATTRIBUTE_CATEGORY VARCHAR2,
209 X_ATTRIBUTE1 VARCHAR2,
210 X_ATTRIBUTE2 VARCHAR2,
211 X_ATTRIBUTE3 VARCHAR2,
212 X_ATTRIBUTE4 VARCHAR2,
213 X_ATTRIBUTE5 VARCHAR2,
214 X_ATTRIBUTE6 VARCHAR2,
215 X_ATTRIBUTE7 VARCHAR2,
216 X_ATTRIBUTE8 VARCHAR2,
217 X_ATTRIBUTE9 VARCHAR2,
218 X_ATTRIBUTE10 VARCHAR2,
219 X_ATTRIBUTE11 VARCHAR2,
220 X_ATTRIBUTE12 VARCHAR2,
221 X_ATTRIBUTE13 VARCHAR2,
222 X_ATTRIBUTE14 VARCHAR2,
223 X_ATTRIBUTE15 VARCHAR2) IS
224
225 CURSOR C IS
226 SELECT *
227 FROM ce_bank_stmt_int_map
228 WHERE rowid = X_row_id
229 FOR UPDATE of map_id NOWAIT;
230
231 Recinfo C%ROWTYPE;
232
233 BEGIN
234 OPEN C;
235 FETCH C INTO recinfo;
236 if (C%NOTFOUND) then
237 CLOSE C;
238 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
239 APP_EXCEPTION.Raise_Exception;
240 end if;
241 CLOSE C;
242 if (
243 (Recinfo.map_id = X_map_id)
244 AND (Recinfo.format_name = X_format_name)
245 AND ( (Recinfo.description = X_description)
246 OR ( (Recinfo.description IS NULL)
247 AND (X_description IS NULL)))
248 AND ( (Recinfo.format_type = X_format_type)
249 OR ( (Recinfo.format_type IS NULL)
250 AND (X_format_type IS NULL)))
251 AND ( (Recinfo.control_file_name = X_control_file_name)
252 OR ( (Recinfo.control_file_name IS NULL)
253 AND (X_control_file_name IS NULL)))
254 AND (Recinfo.enabled = X_enabled)
255 AND ( (Recinfo.precision = X_precision)
256 OR ( (Recinfo.precision IS NULL)
257 AND (X_precision IS NULL)))
258 AND (Recinfo.date_format = X_date_format)
259 AND ( (Recinfo.timestamp_format = X_timestamp_format)
260 OR ( (Recinfo.timestamp_format IS NULL)
261 AND (X_timestamp_format IS NULL)))
262 AND ( (Recinfo.attribute_category = X_attribute_category)
263 OR ( (Recinfo.attribute_category IS NULL)
264 AND (X_attribute_category IS NULL)))
265 AND ( (Recinfo.attribute1 = X_attribute1)
266 OR ( (Recinfo.attribute1 IS NULL)
267 AND (X_attribute1 IS NULL)))
268 AND ( (Recinfo.attribute2 = X_attribute2)
269 OR ( (Recinfo.attribute2 IS NULL)
270 AND (X_attribute2 IS NULL)))
271 AND ( (Recinfo.attribute3 = X_attribute3)
272 OR ( (Recinfo.attribute3 IS NULL)
273 AND (X_attribute3 IS NULL)))
274 AND ( (Recinfo.attribute4 = X_attribute4)
275 OR ( (Recinfo.attribute4 IS NULL)
276 AND (X_attribute4 IS NULL)))
277 AND ( (Recinfo.attribute5 = X_attribute5)
278 OR ( (Recinfo.attribute5 IS NULL)
279 AND (X_attribute5 IS NULL)))
280 AND ( (Recinfo.attribute6 = X_attribute6)
281 OR ( (Recinfo.attribute6 IS NULL)
282 AND (X_attribute6 IS NULL)))
283 AND ( (Recinfo.attribute7 = X_attribute7)
284 OR ( (Recinfo.attribute7 IS NULL)
285 AND (X_attribute7 IS NULL)))
286 AND ( (Recinfo.attribute8 = X_attribute8)
287 OR ( (Recinfo.attribute8 IS NULL)
288 AND (X_attribute8 IS NULL)))
289 AND ( (Recinfo.attribute9 = X_attribute9)
290 OR ( (Recinfo.attribute9 IS NULL)
291 AND (X_attribute9 IS NULL)))
292 AND ( (Recinfo.attribute10 = X_attribute10)
293 OR ( (Recinfo.attribute10 IS NULL)
294 AND (X_attribute10 IS NULL)))
295 AND ( (Recinfo.attribute11 = X_attribute11)
296 OR ( (Recinfo.attribute11 IS NULL)
297 AND (X_attribute11 IS NULL)))
298 AND ( (Recinfo.attribute12 = X_attribute12)
299 OR ( (Recinfo.attribute12 IS NULL)
300 AND (X_attribute12 IS NULL)))
301 AND ( (Recinfo.attribute13 = X_attribute13)
302 OR ( (Recinfo.attribute13 IS NULL)
303 AND (X_attribute13 IS NULL)))
304 AND ( (Recinfo.attribute14 = X_attribute14)
305 OR ( (Recinfo.attribute14 IS NULL)
306 AND (X_attribute14 IS NULL)))
307 AND ( (Recinfo.attribute15 = X_attribute15)
308 OR ( (Recinfo.attribute15 IS NULL)
309 AND (X_attribute15 IS NULL)))
310 ) then
311 return;
312 else
313 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
314 APP_EXCEPTION.Raise_Exception;
315 end if;
316 END Map_Lock_Row;
317
318 PROCEDURE Headers_Insert_Row(
319 X_ROW_ID IN OUT NOCOPY VARCHAR2,
320 X_MAP_HEADER_ID IN OUT NOCOPY NUMBER,
321 X_MAP_ID NUMBER,
322 X_COLUMN_NAME VARCHAR2,
323 X_REC_ID VARCHAR2,
324 X_POSITION NUMBER,
325 X_FORMAT VARCHAR2,
326 X_INCLUDE_FORMAT_IND VARCHAR2,
327 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
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,
333 X_ATTRIBUTE_CATEGORY VARCHAR2,
334 X_ATTRIBUTE1 VARCHAR2,
335 X_ATTRIBUTE2 VARCHAR2,
336 X_ATTRIBUTE3 VARCHAR2,
337 X_ATTRIBUTE4 VARCHAR2,
338 X_ATTRIBUTE5 VARCHAR2,
339 X_ATTRIBUTE6 VARCHAR2,
340 X_ATTRIBUTE7 VARCHAR2,
341 X_ATTRIBUTE8 VARCHAR2,
342 X_ATTRIBUTE9 VARCHAR2,
343 X_ATTRIBUTE10 VARCHAR2,
344 X_ATTRIBUTE11 VARCHAR2,
345 X_ATTRIBUTE12 VARCHAR2,
346 X_ATTRIBUTE13 VARCHAR2,
347 X_ATTRIBUTE14 VARCHAR2,
348 X_ATTRIBUTE15 VARCHAR2) IS
349
350 CURSOR C_map_id IS
351 SELECT CE_BANK_STMT_MAP_HDR_S.nextval
352 FROM sys.dual;
353
354 CURSOR C_row_id IS
355 SELECT rowid
356 FROM ce_bank_stmt_map_hdr
357 WHERE map_header_id = TO_NUMBER(X_MAP_HEADER_ID);
358 BEGIN
362 CLOSE C_map_id;
359 OPEN C_map_id;
360 FETCH C_map_id
361 INTO X_MAP_HEADER_ID;
363
364 INSERT INTO ce_bank_stmt_map_hdr(
365 MAP_HEADER_ID,
366 MAP_ID,
367 COLUMN_NAME,
368 REC_ID_NO,
369 POSITION,
370 FORMAT,
371 INCLUDE_FORMAT_IND,
372 CONCATENATE_FORMAT_FLAG,
373 CREATED_BY,
374 CREATION_DATE,
375 LAST_UPDATED_BY,
376 LAST_UPDATE_DATE,
377 LAST_UPDATE_LOGIN,
378 ATTRIBUTE_CATEGORY,
379 ATTRIBUTE1,
380 ATTRIBUTE2,
381 ATTRIBUTE3,
382 ATTRIBUTE4,
383 ATTRIBUTE5,
384 ATTRIBUTE6,
385 ATTRIBUTE7,
386 ATTRIBUTE8,
387 ATTRIBUTE9,
388 ATTRIBUTE10,
389 ATTRIBUTE11,
390 ATTRIBUTE12,
391 ATTRIBUTE13,
392 ATTRIBUTE14,
393 ATTRIBUTE15)
394 VALUES(
395 X_MAP_HEADER_ID,
396 X_MAP_ID,
397 X_COLUMN_NAME,
398 X_REC_ID,
399 X_POSITION,
400 X_FORMAT,
401 X_INCLUDE_FORMAT_IND,
402 X_CONCATENATE_FORMAT_FLAG,
403 X_CREATED_BY,
404 X_CREATION_DATE,
405 X_LAST_UPDATED_BY,
406 X_LAST_UPDATE_DATE,
407 X_LAST_UPDATE_LOGIN,
408 X_ATTRIBUTE_CATEGORY,
409 X_ATTRIBUTE1,
410 X_ATTRIBUTE2,
411 X_ATTRIBUTE3,
412 X_ATTRIBUTE4,
413 X_ATTRIBUTE5,
414 X_ATTRIBUTE6,
415 X_ATTRIBUTE7,
416 X_ATTRIBUTE8,
417 X_ATTRIBUTE9,
418 X_ATTRIBUTE10,
419 X_ATTRIBUTE11,
420 X_ATTRIBUTE12,
421 X_ATTRIBUTE13,
422 X_ATTRIBUTE14,
423 X_ATTRIBUTE15);
424
425 OPEN C_row_id;
426 FETCH C_row_id INTO X_row_id;
427 if (C_row_id%NOTFOUND) then
428 CLOSE C_row_id;
429 Raise NO_DATA_FOUND;
430 end if;
431 CLOSE C_row_id;
432 END Headers_Insert_Row;
433
434 PROCEDURE Headers_Update_Row(
435 X_ROW_ID VARCHAR2,
436 X_MAP_HEADER_ID NUMBER,
437 X_MAP_ID NUMBER,
438 X_COLUMN_NAME VARCHAR2,
439 X_REC_ID VARCHAR2,
440 X_POSITION NUMBER,
441 X_FORMAT VARCHAR2,
442 X_INCLUDE_FORMAT_IND VARCHAR2,
443 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
444 X_CREATED_BY NUMBER,
445 X_CREATION_DATE DATE,
446 X_LAST_UPDATED_BY NUMBER,
447 X_LAST_UPDATE_DATE DATE,
448 X_LAST_UPDATE_LOGIN NUMBER,
449 X_ATTRIBUTE_CATEGORY VARCHAR2,
450 X_ATTRIBUTE1 VARCHAR2,
451 X_ATTRIBUTE2 VARCHAR2,
452 X_ATTRIBUTE3 VARCHAR2,
453 X_ATTRIBUTE4 VARCHAR2,
454 X_ATTRIBUTE5 VARCHAR2,
455 X_ATTRIBUTE6 VARCHAR2,
456 X_ATTRIBUTE7 VARCHAR2,
457 X_ATTRIBUTE8 VARCHAR2,
458 X_ATTRIBUTE9 VARCHAR2,
459 X_ATTRIBUTE10 VARCHAR2,
460 X_ATTRIBUTE11 VARCHAR2,
461 X_ATTRIBUTE12 VARCHAR2,
462 X_ATTRIBUTE13 VARCHAR2,
463 X_ATTRIBUTE14 VARCHAR2,
464 X_ATTRIBUTE15 VARCHAR2) IS
465 BEGIN
466 UPDATE ce_bank_stmt_map_hdr
467 SET map_header_id = X_map_header_id,
468 map_id = X_map_id,
469 column_name = X_column_name,
470 rec_id_no = X_rec_id,
471 position = X_position,
472 format = X_format,
473 include_format_ind = X_include_format_ind,
474 concatenate_format_flag = X_concatenate_format_flag,
475 attribute_category = X_attribute_category,
476 attribute1 = X_attribute1,
477 attribute2 = X_attribute2,
478 attribute3 = X_attribute3,
479 attribute4 = X_attribute4,
480 attribute5 = X_attribute5,
481 attribute6 = X_attribute6,
482 attribute7 = X_attribute7,
483 attribute8 = X_attribute8,
484 attribute9 = X_attribute9,
485 attribute10 = X_attribute10,
486 attribute11 = X_attribute11,
487 attribute12 = X_attribute12,
488 attribute13 = X_attribute13,
489 attribute14 = X_attribute14,
490 attribute15 = X_attribute15,
491 last_updated_by = x_last_updated_by,
492 last_update_date = x_last_update_date,
493 last_update_login = x_last_update_login
494 WHERE rowid = X_row_id;
495 if (SQL%NOTFOUND) then
496 Raise NO_DATA_FOUND;
497 end if;
498 END Headers_Update_Row;
499
500 PROCEDURE Headers_Lock_Row(
501 X_ROW_ID VARCHAR2,
502 X_MAP_HEADER_ID NUMBER,
503 X_MAP_ID NUMBER,
504 X_COLUMN_NAME VARCHAR2,
505 X_REC_ID VARCHAR2,
506 X_POSITION NUMBER,
507 X_FORMAT VARCHAR2,
508 X_INCLUDE_FORMAT_IND VARCHAR2,
509 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
510 X_CREATED_BY NUMBER,
511 X_CREATION_DATE DATE,
512 X_LAST_UPDATED_BY NUMBER,
513 X_LAST_UPDATE_DATE DATE,
514 X_LAST_UPDATE_LOGIN NUMBER,
515 X_ATTRIBUTE_CATEGORY VARCHAR2,
516 X_ATTRIBUTE1 VARCHAR2,
517 X_ATTRIBUTE2 VARCHAR2,
518 X_ATTRIBUTE3 VARCHAR2,
519 X_ATTRIBUTE4 VARCHAR2,
520 X_ATTRIBUTE5 VARCHAR2,
521 X_ATTRIBUTE6 VARCHAR2,
522 X_ATTRIBUTE7 VARCHAR2,
523 X_ATTRIBUTE8 VARCHAR2,
527 X_ATTRIBUTE12 VARCHAR2,
524 X_ATTRIBUTE9 VARCHAR2,
525 X_ATTRIBUTE10 VARCHAR2,
526 X_ATTRIBUTE11 VARCHAR2,
528 X_ATTRIBUTE13 VARCHAR2,
529 X_ATTRIBUTE14 VARCHAR2,
530 X_ATTRIBUTE15 VARCHAR2) IS
531
532 CURSOR C IS
533 SELECT *
534 FROM ce_bank_stmt_map_hdr
535 WHERE rowid = X_row_id
536 FOR UPDATE of map_header_id NOWAIT;
537
538 Recinfo C%ROWTYPE;
539
540 BEGIN
541 OPEN C;
542 FETCH C INTO recinfo;
543 if (C%NOTFOUND) then
544 CLOSE C;
545 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
546 APP_EXCEPTION.Raise_Exception;
547 end if;
548 CLOSE C;
549 if (
550 (Recinfo.map_id = X_map_id)
551 AND (Recinfo.map_header_id = X_map_header_id)
552 AND (Recinfo.column_name = X_column_name)
553 AND NVL(Recinfo.include_format_ind,'N') = NVL(X_include_format_ind,'N')/* Bug 3033122 added the NVL to this condition */
554 AND (Recinfo.concatenate_format_flag = X_concatenate_format_flag)
555 AND ( (Recinfo.format = X_format)
556 OR ( (Recinfo.format IS NULL)
557 AND (X_format IS NULL)))
558 AND ( (Recinfo.rec_id_no = X_rec_id)
559 OR ( (Recinfo.rec_id_no IS NULL)
560 AND (X_rec_id IS NULL)))
561 AND ( (Recinfo.position = X_position)
562 OR ( (Recinfo.position IS NULL)
563 AND (X_position IS NULL)))
564 AND ( (Recinfo.attribute_category = X_attribute_category)
565 OR ( (Recinfo.attribute_category IS NULL)
566 AND (X_attribute_category IS NULL)))
567 AND ( (Recinfo.attribute1 = X_attribute1)
568 OR ( (Recinfo.attribute1 IS NULL)
569 AND (X_attribute1 IS NULL)))
570 AND ( (Recinfo.attribute2 = X_attribute2)
571 OR ( (Recinfo.attribute2 IS NULL)
572 AND (X_attribute2 IS NULL)))
573 AND ( (Recinfo.attribute3 = X_attribute3)
574 OR ( (Recinfo.attribute3 IS NULL)
575 AND (X_attribute3 IS NULL)))
576 AND ( (Recinfo.attribute4 = X_attribute4)
577 OR ( (Recinfo.attribute4 IS NULL)
578 AND (X_attribute4 IS NULL)))
579 AND ( (Recinfo.attribute5 = X_attribute5)
580 OR ( (Recinfo.attribute5 IS NULL)
581 AND (X_attribute5 IS NULL)))
582 AND ( (Recinfo.attribute6 = X_attribute6)
583 OR ( (Recinfo.attribute6 IS NULL)
584 AND (X_attribute6 IS NULL)))
585 AND ( (Recinfo.attribute7 = X_attribute7)
586 OR ( (Recinfo.attribute7 IS NULL)
587 AND (X_attribute7 IS NULL)))
588 AND ( (Recinfo.attribute8 = X_attribute8)
589 OR ( (Recinfo.attribute8 IS NULL)
590 AND (X_attribute8 IS NULL)))
591 AND ( (Recinfo.attribute9 = X_attribute9)
592 OR ( (Recinfo.attribute9 IS NULL)
593 AND (X_attribute9 IS NULL)))
594 AND ( (Recinfo.attribute10 = X_attribute10)
595 OR ( (Recinfo.attribute10 IS NULL)
596 AND (X_attribute10 IS NULL)))
597 AND ( (Recinfo.attribute11 = X_attribute11)
598 OR ( (Recinfo.attribute11 IS NULL)
599 AND (X_attribute11 IS NULL)))
600 AND ( (Recinfo.attribute12 = X_attribute12)
601 OR ( (Recinfo.attribute12 IS NULL)
602 AND (X_attribute12 IS NULL)))
603 AND ( (Recinfo.attribute13 = X_attribute13)
604 OR ( (Recinfo.attribute13 IS NULL)
605 AND (X_attribute13 IS NULL)))
606 AND ( (Recinfo.attribute14 = X_attribute14)
607 OR ( (Recinfo.attribute14 IS NULL)
608 AND (X_attribute14 IS NULL)))
609 AND ( (Recinfo.attribute15 = X_attribute15)
610 OR ( (Recinfo.attribute15 IS NULL)
611 AND (X_attribute15 IS NULL)))
612 ) then
613 return;
614 else
615 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
616 APP_EXCEPTION.Raise_Exception;
617 end if;
618 END Headers_Lock_Row;
619
620 PROCEDURE Lines_Insert_Row(
621 X_ROW_ID IN OUT NOCOPY VARCHAR2,
622 X_MAP_LINE_ID IN OUT NOCOPY NUMBER,
623 X_MAP_ID NUMBER,
624 X_COLUMN_NAME VARCHAR2,
625 X_REC_ID VARCHAR2,
626 X_POSITION NUMBER,
627 X_FORMAT VARCHAR2,
628 X_INCLUDE_FORMAT_IND VARCHAR2,
629 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
630 X_CREATED_BY NUMBER,
631 X_CREATION_DATE DATE,
632 X_LAST_UPDATED_BY NUMBER,
633 X_LAST_UPDATE_DATE DATE,
634 X_LAST_UPDATE_LOGIN NUMBER,
635 X_ATTRIBUTE_CATEGORY VARCHAR2,
636 X_ATTRIBUTE1 VARCHAR2,
637 X_ATTRIBUTE2 VARCHAR2,
638 X_ATTRIBUTE3 VARCHAR2,
639 X_ATTRIBUTE4 VARCHAR2,
640 X_ATTRIBUTE5 VARCHAR2,
641 X_ATTRIBUTE6 VARCHAR2,
642 X_ATTRIBUTE7 VARCHAR2,
643 X_ATTRIBUTE8 VARCHAR2,
644 X_ATTRIBUTE9 VARCHAR2,
645 X_ATTRIBUTE10 VARCHAR2,
646 X_ATTRIBUTE11 VARCHAR2,
647 X_ATTRIBUTE12 VARCHAR2,
648 X_ATTRIBUTE13 VARCHAR2,
649 X_ATTRIBUTE14 VARCHAR2,
650 X_ATTRIBUTE15 VARCHAR2) IS
651
652 CURSOR C_map_id IS
653 SELECT CE_BANK_STMT_MAP_LINE_S.nextval
654 FROM sys.dual;
655
656 CURSOR C_row_id IS
657 SELECT rowid
661 OPEN C_map_id;
658 FROM ce_bank_stmt_map_line
659 WHERE map_line_id = TO_NUMBER(X_MAP_LINE_ID);
660 BEGIN
662 FETCH C_map_id
663 INTO X_MAP_LINE_ID;
664 CLOSE C_map_id;
665
666 INSERT INTO ce_bank_stmt_map_line(
667 MAP_LINE_ID,
668 MAP_ID,
669 COLUMN_NAME,
670 REC_ID_NO,
671 POSITION,
672 FORMAT,
673 INCLUDE_FORMAT_IND,
674 CONCATENATE_FORMAT_FLAG,
675 CREATED_BY,
676 CREATION_DATE,
677 LAST_UPDATED_BY,
678 LAST_UPDATE_DATE,
679 LAST_UPDATE_LOGIN,
680 ATTRIBUTE_CATEGORY,
681 ATTRIBUTE1,
682 ATTRIBUTE2,
683 ATTRIBUTE3,
684 ATTRIBUTE4,
685 ATTRIBUTE5,
686 ATTRIBUTE6,
687 ATTRIBUTE7,
688 ATTRIBUTE8,
689 ATTRIBUTE9,
690 ATTRIBUTE10,
691 ATTRIBUTE11,
692 ATTRIBUTE12,
693 ATTRIBUTE13,
694 ATTRIBUTE14,
695 ATTRIBUTE15)
696 VALUES(
697 X_MAP_LINE_ID,
698 X_MAP_ID,
699 X_COLUMN_NAME,
700 X_REC_ID,
701 X_POSITION,
702 X_FORMAT,
703 X_INCLUDE_FORMAT_IND,
704 X_CONCATENATE_FORMAT_FLAG,
705 X_CREATED_BY,
706 X_CREATION_DATE,
707 X_LAST_UPDATED_BY,
708 X_LAST_UPDATE_DATE,
709 X_LAST_UPDATE_LOGIN,
710 X_ATTRIBUTE_CATEGORY,
711 X_ATTRIBUTE1,
712 X_ATTRIBUTE2,
713 X_ATTRIBUTE3,
714 X_ATTRIBUTE4,
715 X_ATTRIBUTE5,
716 X_ATTRIBUTE6,
717 X_ATTRIBUTE7,
718 X_ATTRIBUTE8,
719 X_ATTRIBUTE9,
720 X_ATTRIBUTE10,
721 X_ATTRIBUTE11,
722 X_ATTRIBUTE12,
723 X_ATTRIBUTE13,
724 X_ATTRIBUTE14,
725 X_ATTRIBUTE15);
726
727 OPEN C_row_id;
728 FETCH C_row_id INTO X_row_id;
729 if (C_row_id%NOTFOUND) then
730 CLOSE C_row_id;
731 Raise NO_DATA_FOUND;
732 end if;
733 CLOSE C_row_id;
734 END Lines_Insert_Row;
735
736 PROCEDURE Lines_Update_Row(
737 X_ROW_ID VARCHAR2,
738 X_MAP_LINE_ID NUMBER,
739 X_MAP_ID NUMBER,
740 X_COLUMN_NAME VARCHAR2,
741 X_REC_ID VARCHAR2,
742 X_POSITION NUMBER,
743 X_FORMAT VARCHAR2,
744 X_INCLUDE_FORMAT_IND VARCHAR2,
745 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
746 X_CREATED_BY NUMBER,
747 X_CREATION_DATE DATE,
748 X_LAST_UPDATED_BY NUMBER,
749 X_LAST_UPDATE_DATE DATE,
750 X_LAST_UPDATE_LOGIN NUMBER,
751 X_ATTRIBUTE_CATEGORY VARCHAR2,
752 X_ATTRIBUTE1 VARCHAR2,
753 X_ATTRIBUTE2 VARCHAR2,
754 X_ATTRIBUTE3 VARCHAR2,
755 X_ATTRIBUTE4 VARCHAR2,
756 X_ATTRIBUTE5 VARCHAR2,
757 X_ATTRIBUTE6 VARCHAR2,
758 X_ATTRIBUTE7 VARCHAR2,
759 X_ATTRIBUTE8 VARCHAR2,
760 X_ATTRIBUTE9 VARCHAR2,
761 X_ATTRIBUTE10 VARCHAR2,
762 X_ATTRIBUTE11 VARCHAR2,
763 X_ATTRIBUTE12 VARCHAR2,
764 X_ATTRIBUTE13 VARCHAR2,
765 X_ATTRIBUTE14 VARCHAR2,
766 X_ATTRIBUTE15 VARCHAR2) IS
767 BEGIN
768 UPDATE ce_bank_stmt_map_line
769 SET map_line_id = X_map_line_id,
770 map_id = X_map_id,
771 column_name = X_column_name,
772 rec_id_no = X_rec_id,
773 position = X_position,
774 format = X_format,
775 include_format_ind = X_include_format_ind,
776 concatenate_format_flag = X_concatenate_format_flag,
777 attribute_category = X_attribute_category,
778 attribute1 = X_attribute1,
779 attribute2 = X_attribute2,
780 attribute3 = X_attribute3,
781 attribute4 = X_attribute4,
782 attribute5 = X_attribute5,
783 attribute6 = X_attribute6,
784 attribute7 = X_attribute7,
785 attribute8 = X_attribute8,
786 attribute9 = X_attribute9,
787 attribute10 = X_attribute10,
788 attribute11 = X_attribute11,
789 attribute12 = X_attribute12,
790 attribute13 = X_attribute13,
791 attribute14 = X_attribute14,
792 attribute15 = X_attribute15,
793 last_updated_by = x_last_updated_by,
794 last_update_date = x_last_update_date,
795 last_update_login = x_last_update_login
796 WHERE rowid = X_row_id;
797 if (SQL%NOTFOUND) then
798 Raise NO_DATA_FOUND;
799 end if;
800 END Lines_Update_Row;
801
802 PROCEDURE Lines_Lock_Row(
803 X_ROW_ID VARCHAR2,
804 X_MAP_LINE_ID NUMBER,
805 X_MAP_ID NUMBER,
806 X_COLUMN_NAME VARCHAR2,
807 X_REC_ID VARCHAR2,
808 X_POSITION NUMBER,
809 X_FORMAT VARCHAR2,
810 X_INCLUDE_FORMAT_IND VARCHAR2,
811 X_CONCATENATE_FORMAT_FLAG VARCHAR2,
812 X_CREATED_BY NUMBER,
813 X_CREATION_DATE DATE,
814 X_LAST_UPDATED_BY NUMBER,
815 X_LAST_UPDATE_DATE DATE,
816 X_LAST_UPDATE_LOGIN NUMBER,
817 X_ATTRIBUTE_CATEGORY VARCHAR2,
818 X_ATTRIBUTE1 VARCHAR2,
819 X_ATTRIBUTE2 VARCHAR2,
820 X_ATTRIBUTE3 VARCHAR2,
821 X_ATTRIBUTE4 VARCHAR2,
822 X_ATTRIBUTE5 VARCHAR2,
823 X_ATTRIBUTE6 VARCHAR2,
827 X_ATTRIBUTE10 VARCHAR2,
824 X_ATTRIBUTE7 VARCHAR2,
825 X_ATTRIBUTE8 VARCHAR2,
826 X_ATTRIBUTE9 VARCHAR2,
828 X_ATTRIBUTE11 VARCHAR2,
829 X_ATTRIBUTE12 VARCHAR2,
830 X_ATTRIBUTE13 VARCHAR2,
831 X_ATTRIBUTE14 VARCHAR2,
832 X_ATTRIBUTE15 VARCHAR2) IS
833
834 CURSOR C IS
835 SELECT *
836 FROM ce_bank_stmt_map_line
837 WHERE rowid = X_row_id
838 FOR UPDATE of map_line_id NOWAIT;
839
840 Recinfo C%ROWTYPE;
841
842 BEGIN
843 OPEN C;
844 FETCH C INTO recinfo;
845 if (C%NOTFOUND) then
846 CLOSE C;
847 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
848 APP_EXCEPTION.Raise_Exception;
849 end if;
850 CLOSE C;
851 if (
852 (Recinfo.map_id = X_map_id)
853 AND (Recinfo.map_line_id = X_map_line_id)
854 AND (Recinfo.column_name = X_column_name)
855 AND (Recinfo.include_format_ind = X_include_format_ind)
856 AND (Recinfo.concatenate_format_flag = X_concatenate_format_flag)
857 AND ( (Recinfo.format = X_format)
858 OR ( (Recinfo.format IS NULL)
859 AND (X_format IS NULL)))
860 AND ( (Recinfo.rec_id_no = X_rec_id)
861 OR ( (Recinfo.rec_id_no IS NULL)
862 AND (X_rec_id IS NULL)))
863 AND ( (Recinfo.position = X_position)
864 OR ( (Recinfo.position IS NULL)
865 AND (X_position IS NULL)))
866 AND ( (Recinfo.attribute_category = X_attribute_category)
867 OR ( (Recinfo.attribute_category IS NULL)
868 AND (X_attribute_category IS NULL)))
869 AND ( (Recinfo.attribute1 = X_attribute1)
870 OR ( (Recinfo.attribute1 IS NULL)
871 AND (X_attribute1 IS NULL)))
872 AND ( (Recinfo.attribute2 = X_attribute2)
873 OR ( (Recinfo.attribute2 IS NULL)
874 AND (X_attribute2 IS NULL)))
875 AND ( (Recinfo.attribute3 = X_attribute3)
876 OR ( (Recinfo.attribute3 IS NULL)
877 AND (X_attribute3 IS NULL)))
878 AND ( (Recinfo.attribute4 = X_attribute4)
879 OR ( (Recinfo.attribute4 IS NULL)
880 AND (X_attribute4 IS NULL)))
881 AND ( (Recinfo.attribute5 = X_attribute5)
882 OR ( (Recinfo.attribute5 IS NULL)
883 AND (X_attribute5 IS NULL)))
884 AND ( (Recinfo.attribute6 = X_attribute6)
885 OR ( (Recinfo.attribute6 IS NULL)
886 AND (X_attribute6 IS NULL)))
887 AND ( (Recinfo.attribute7 = X_attribute7)
888 OR ( (Recinfo.attribute7 IS NULL)
889 AND (X_attribute7 IS NULL)))
890 AND ( (Recinfo.attribute8 = X_attribute8)
891 OR ( (Recinfo.attribute8 IS NULL)
892 AND (X_attribute8 IS NULL)))
893 AND ( (Recinfo.attribute9 = X_attribute9)
894 OR ( (Recinfo.attribute9 IS NULL)
895 AND (X_attribute9 IS NULL)))
896 AND ( (Recinfo.attribute10 = X_attribute10)
897 OR ( (Recinfo.attribute10 IS NULL)
898 AND (X_attribute10 IS NULL)))
899 AND ( (Recinfo.attribute11 = X_attribute11)
900 OR ( (Recinfo.attribute11 IS NULL)
901 AND (X_attribute11 IS NULL)))
902 AND ( (Recinfo.attribute12 = X_attribute12)
903 OR ( (Recinfo.attribute12 IS NULL)
904 AND (X_attribute12 IS NULL)))
905 AND ( (Recinfo.attribute13 = X_attribute13)
906 OR ( (Recinfo.attribute13 IS NULL)
907 AND (X_attribute13 IS NULL)))
908 AND ( (Recinfo.attribute14 = X_attribute14)
909 OR ( (Recinfo.attribute14 IS NULL)
910 AND (X_attribute14 IS NULL)))
911 AND ( (Recinfo.attribute15 = X_attribute15)
912 OR ( (Recinfo.attribute15 IS NULL)
913 AND (X_attribute15 IS NULL)))
914 ) then
915 return;
916 else
917 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
918 APP_EXCEPTION.Raise_Exception;
919 end if;
920
921 END Lines_Lock_Row;
922
923 END CE_BANK_STMT_LDR_PKG;