[Home] [Help]
PACKAGE BODY: APPS.GL_RECURRING_RULES_PKG
Source
1 PACKAGE BODY GL_RECURRING_RULES_PKG as
2 /* $Header: glirecrb.pls 120.6 2005/05/05 01:20:25 kvora ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9
10 PROCEDURE check_unique( x_rowid VARCHAR2,
11 x_rule_num NUMBER,
12 x_line_num NUMBER,
13 x_header_id NUMBER ) IS
14 CURSOR c_dup IS
15 SELECT 'Duplicate'
16 FROM gl_recurring_line_calc_rules r
17 WHERE r.rule_num = x_rule_num
18 AND r.recurring_line_num = x_line_num
19 AND r.recurring_header_id = x_header_id
20 AND ( x_rowid is NULL
21 OR
22 r.rowid <> x_rowid );
23
24 dummy VARCHAR2(100);
25
26 BEGIN
27 OPEN c_dup;
28 FETCH c_dup INTO dummy;
29
30 IF c_dup%FOUND THEN
31 CLOSE c_dup;
32 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_REC_RULE' );
33 app_exception.raise_exception;
34 END IF;
35
36 CLOSE c_dup;
37
38 EXCEPTION
39 WHEN app_exceptions.application_exception THEN
40 RAISE;
41 WHEN OTHERS THEN
42 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
43 fnd_message.set_token('PROCEDURE',
44 'GL_RECURRING_RULES_PKG.check_unique');
45 RAISE;
46
47 END check_unique;
48
49 -- **********************************************************************
50
51 PROCEDURE update_line_num( x_new_line_num NUMBER,
52 x_old_line_num NUMBER,
53 x_header_id NUMBER ) IS
54 BEGIN
55
56 UPDATE gl_recurring_line_calc_rules r
57 SET r.recurring_line_num = x_new_line_num
58 WHERE r.recurring_header_id = x_header_id
59 AND r.recurring_line_num = x_old_line_num;
60
61 EXCEPTION
62 WHEN app_exceptions.application_exception THEN
63 RAISE;
64 WHEN OTHERS THEN
65 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
66 fnd_message.set_token('PROCEDURE',
67 'GL_RECURRING_RULES_PKG.update_line_num');
68 RAISE;
69
70 END update_line_num;
71
72 -- *********************************************************************
73
74
75 FUNCTION get_ccid( x_ledger_id NUMBER,
76 x_coa_id NUMBER,
77 x_conc_seg VARCHAR2,
78 x_err_msg OUT NOCOPY VARCHAR2,
79 x_ccid OUT NOCOPY NUMBER,
80 x_templgrid OUT NOCOPY NUMBER,
81 x_acct_type OUT NOCOPY VARCHAR2,
82 X_Segment1 VARCHAR2,
83 X_Segment2 VARCHAR2,
84 X_Segment3 VARCHAR2,
85 X_Segment4 VARCHAR2,
86 X_Segment5 VARCHAR2,
87 X_Segment6 VARCHAR2,
88 X_Segment7 VARCHAR2,
89 X_Segment8 VARCHAR2,
90 X_Segment9 VARCHAR2,
91 X_Segment10 VARCHAR2,
92 X_Segment11 VARCHAR2,
93 X_Segment12 VARCHAR2,
94 X_Segment13 VARCHAR2,
95 X_Segment14 VARCHAR2,
96 X_Segment15 VARCHAR2,
97 X_Segment16 VARCHAR2,
98 X_Segment17 VARCHAR2,
99 X_Segment18 VARCHAR2,
100 X_Segment19 VARCHAR2,
101 X_Segment20 VARCHAR2,
102 X_Segment21 VARCHAR2,
103 X_Segment22 VARCHAR2,
104 X_Segment23 VARCHAR2,
105 X_Segment24 VARCHAR2,
106 X_Segment25 VARCHAR2,
107 X_Segment26 VARCHAR2,
108 X_Segment27 VARCHAR2,
109 X_Segment28 VARCHAR2,
110 X_Segment29 VARCHAR2,
111 X_Segment30 VARCHAR2)
112 RETURN BOOLEAN IS
113 ccid_cursor NUMBER;
114 ccid_select VARCHAR2(4500);
115 c_ccid NUMBER;
116 c_lgr_id NUMBER;
117 c_enabled_flag VARCHAR2(1);
118 c_acct_type VARCHAR2(1);
119 row_count NUMBER;
120 BEGIN
121
122 ccid_cursor := dbms_sql.open_cursor;
123
124 ccid_select := ' SELECT cc.code_combination_id,
125 gst.ledger_id,
126 cc.enabled_flag, cc.account_type'||
127 ' FROM gl_code_combinations cc, ' ||
128 ' gl_summary_templates gst ' ||
129 ' WHERE cc.chart_of_accounts_id = :coa_id ';
130
131 IF ( x_segment1 IS NOT NULL) THEN
132 ccid_select := ccid_select || ' AND cc.segment1 = :segment1 ';
133 ELSE
134 ccid_select := ccid_select || ' AND cc.segment1 IS NULL ';
135 END IF;
136
137 IF ( x_segment2 IS NOT NULL) THEN
138 ccid_select := ccid_select || ' AND cc.segment2 = :segment2 ';
139 ELSE
140 ccid_select := ccid_select || ' AND cc.segment2 IS NULL ';
141 END IF;
142
143 IF ( x_segment3 IS NOT NULL) THEN
144 ccid_select := ccid_select || ' AND cc.segment3 = :segment3 ';
145 ELSE
146 ccid_select := ccid_select || ' AND cc.segment3 IS NULL ';
147 END IF;
148
149 IF ( x_segment4 IS NOT NULL) THEN
150 ccid_select := ccid_select || ' AND cc.segment4 = :segment4 ';
151 ELSE
152 ccid_select := ccid_select || ' AND cc.segment4 IS NULL ';
153 END IF;
154
155 IF ( x_segment5 IS NOT NULL) THEN
156 ccid_select := ccid_select || ' AND cc.segment5 = :segment5 ';
157 ELSE
158 ccid_select := ccid_select || ' AND cc.segment5 IS NULL ';
159 END IF;
160
161 IF ( x_segment6 IS NOT NULL) THEN
162 ccid_select := ccid_select || ' AND cc.segment6 = :segment6 ';
163 ELSE
164 ccid_select := ccid_select || ' AND cc.segment6 IS NULL ';
165 END IF;
166
167 IF ( x_segment7 IS NOT NULL) THEN
168 ccid_select := ccid_select || ' AND cc.segment7 = :segment7 ';
169 ELSE
170 ccid_select := ccid_select || ' AND cc.segment7 IS NULL ';
171 END IF;
172
173 IF ( x_segment8 IS NOT NULL) THEN
174 ccid_select := ccid_select || ' AND cc.segment8 = :segment8 ';
175 ELSE
176 ccid_select := ccid_select || ' AND cc.segment8 IS NULL ';
177 END IF;
178
179 IF ( x_segment9 IS NOT NULL) THEN
180 ccid_select := ccid_select || ' AND cc.segment9 = :segment9 ';
181 ELSE
182 ccid_select := ccid_select || ' AND cc.segment9 IS NULL ';
183 END IF;
184
185 IF ( x_segment10 IS NOT NULL) THEN
186 ccid_select := ccid_select || ' AND cc.segment10 = :segment10 ';
187 ELSE
188 ccid_select := ccid_select || ' AND cc.segment10 IS NULL ';
189 END IF;
190
191 IF ( x_segment11 IS NOT NULL) THEN
192 ccid_select := ccid_select || ' AND cc.segment11 = :segment11 ';
193 ELSE
194 ccid_select := ccid_select || ' AND cc.segment11 IS NULL ';
195 END IF;
196
197 IF ( x_segment12 IS NOT NULL) THEN
198 ccid_select := ccid_select || ' AND cc.segment12 = :segment12 ';
199 ELSE
200 ccid_select := ccid_select || ' AND cc.segment12 IS NULL ';
201 END IF;
202
203 IF ( x_segment13 IS NOT NULL) THEN
204 ccid_select := ccid_select || ' AND cc.segment13 = :segment13 ';
205 ELSE
206 ccid_select := ccid_select || ' AND cc.segment13 IS NULL ';
207 END IF;
208
209 IF ( x_segment14 IS NOT NULL) THEN
210 ccid_select := ccid_select || ' AND cc.segment14 = :segment14 ';
211 ELSE
212 ccid_select := ccid_select || ' AND cc.segment14 IS NULL ';
213 END IF;
214
215 IF ( x_segment15 IS NOT NULL) THEN
216 ccid_select := ccid_select || ' AND cc.segment15 = :segment15 ';
217 ELSE
218 ccid_select := ccid_select || ' AND cc.segment15 IS NULL ';
219 END IF;
220
221 IF ( x_segment16 IS NOT NULL) THEN
222 ccid_select := ccid_select || ' AND cc.segment16 = :segment16 ';
223 ELSE
224 ccid_select := ccid_select || ' AND cc.segment16 IS NULL ';
225 END IF;
226
227 IF ( x_segment17 IS NOT NULL) THEN
228 ccid_select := ccid_select || ' AND cc.segment17 = :segment17 ';
229 ELSE
230 ccid_select := ccid_select || ' AND cc.segment17 IS NULL ';
231 END IF;
232
233 IF ( x_segment18 IS NOT NULL) THEN
234 ccid_select := ccid_select || ' AND cc.segment18 = :segment18 ';
235 ELSE
236 ccid_select := ccid_select || ' AND cc.segment18 IS NULL ';
237 END IF;
238
239 IF ( x_segment19 IS NOT NULL) THEN
240 ccid_select := ccid_select || ' AND cc.segment19 = :segment19 ';
241 ELSE
242 ccid_select := ccid_select || ' AND cc.segment19 IS NULL ';
243 END IF;
244
245 IF ( x_segment20 IS NOT NULL) THEN
246 ccid_select := ccid_select || ' AND cc.segment20 = :segment20 ';
247 ELSE
248 ccid_select := ccid_select || ' AND cc.segment20 IS NULL ';
249 END IF;
250
251 IF ( x_segment21 IS NOT NULL) THEN
252 ccid_select := ccid_select || ' AND cc.segment21 = :segment21 ';
253 ELSE
254 ccid_select := ccid_select || ' AND cc.segment21 IS NULL ';
255 END IF;
256
257 IF ( x_segment22 IS NOT NULL) THEN
258 ccid_select := ccid_select || ' AND cc.segment22 = :segment22 ';
259 ELSE
260 ccid_select := ccid_select || ' AND cc.segment22 IS NULL ';
261 END IF;
262
263 IF ( x_segment23 IS NOT NULL) THEN
264 ccid_select := ccid_select || ' AND cc.segment23 = :segment23 ';
265 ELSE
266 ccid_select := ccid_select || ' AND cc.segment23 IS NULL ';
267 END IF;
268
269 IF ( x_segment24 IS NOT NULL) THEN
270 ccid_select := ccid_select || ' AND cc.segment24 = :segment24 ';
271 ELSE
272 ccid_select := ccid_select || ' AND cc.segment24 IS NULL ';
273 END IF;
274
275 IF ( x_segment25 IS NOT NULL) THEN
276 ccid_select := ccid_select || ' AND cc.segment25 = :segment25 ';
277 ELSE
278 ccid_select := ccid_select || ' AND cc.segment25 IS NULL ';
279 END IF;
280
281 IF ( x_segment26 IS NOT NULL) THEN
282 ccid_select := ccid_select || ' AND cc.segment26 = :segment26 ';
283 ELSE
284 ccid_select := ccid_select || ' AND cc.segment26 IS NULL ';
285 END IF;
286
287 IF ( x_segment27 IS NOT NULL) THEN
288 ccid_select := ccid_select || ' AND cc.segment27 = :segment27 ';
289 ELSE
290 ccid_select := ccid_select || ' AND cc.segment27 IS NULL ';
291 END IF;
292
293 IF ( x_segment28 IS NOT NULL) THEN
294 ccid_select := ccid_select || ' AND cc.segment28 = :segment28 ';
295 ELSE
296 ccid_select := ccid_select || ' AND cc.segment28 IS NULL ';
297 END IF;
298
299 IF ( x_segment29 IS NOT NULL) THEN
300 ccid_select := ccid_select || ' AND cc.segment29 = :segment29 ';
301 ELSE
302 ccid_select := ccid_select || ' AND cc.segment29 IS NULL ';
303 END IF;
304
305 IF ( x_segment30 IS NOT NULL) THEN
306 ccid_select := ccid_select || ' AND cc.segment30 = :segment30 ';
307 ELSE
308 ccid_select := ccid_select || ' AND cc.segment30 IS NULL ';
309 END IF;
310
311 ccid_select := ccid_select ||
312 ' AND gst.template_id (+) = cc.template_id ' ||
313 ' ORDER BY decode(gst.ledger_id,:lgr_id,0,1) ';
314
315 dbms_sql.parse(ccid_cursor,ccid_select,dbms_sql.v7);
316
317 dbms_sql.define_column(ccid_cursor, 1, c_ccid);
318 dbms_sql.define_column(ccid_cursor, 2, c_lgr_id);
319 dbms_sql.define_column(ccid_cursor, 3, c_enabled_flag, 1);
320 dbms_sql.define_column(ccid_cursor, 4, c_acct_type, 1);
321
322 dbms_sql.bind_variable(ccid_cursor, ':coa_id', x_coa_id);
323
324 IF ( x_segment1 IS NOT NULL ) THEN
325 dbms_sql.bind_variable(ccid_cursor, ':segment1', x_segment1);
326 END IF;
327
328 IF ( x_segment2 IS NOT NULL ) THEN
329 dbms_sql.bind_variable(ccid_cursor, ':segment2', x_segment2);
330 END IF;
331
332 IF ( x_segment3 IS NOT NULL ) THEN
333 dbms_sql.bind_variable(ccid_cursor, ':segment3', x_segment3);
334 END IF;
335
336 IF ( x_segment4 IS NOT NULL ) THEN
337 dbms_sql.bind_variable(ccid_cursor, ':segment4', x_segment4);
338 END IF;
339
340 IF ( x_segment5 IS NOT NULL ) THEN
341 dbms_sql.bind_variable(ccid_cursor, ':segment5', x_segment5);
342 END IF;
343
344 IF ( x_segment6 IS NOT NULL ) THEN
345 dbms_sql.bind_variable(ccid_cursor, ':segment6', x_segment6);
346 END IF;
347
348 IF ( x_segment7 IS NOT NULL ) THEN
349 dbms_sql.bind_variable(ccid_cursor, ':segment7', x_segment7);
350 END IF;
351
352 IF ( x_segment8 IS NOT NULL ) THEN
353 dbms_sql.bind_variable(ccid_cursor, ':segment8', x_segment8);
354 END IF;
355
356 IF ( x_segment9 IS NOT NULL ) THEN
357 dbms_sql.bind_variable(ccid_cursor, ':segment9', x_segment9);
358 END IF;
359
360 IF ( x_segment10 IS NOT NULL ) THEN
361 dbms_sql.bind_variable(ccid_cursor, ':segment10', x_segment10);
365 dbms_sql.bind_variable(ccid_cursor, ':segment11', x_segment11);
362 END IF;
363
364 IF ( x_segment11 IS NOT NULL ) THEN
366 END IF;
367
368 IF ( x_segment12 IS NOT NULL ) THEN
369 dbms_sql.bind_variable(ccid_cursor, ':segment12', x_segment12);
370 END IF;
371
372 IF ( x_segment13 IS NOT NULL ) THEN
373 dbms_sql.bind_variable(ccid_cursor, ':segment13', x_segment13);
374 END IF;
375
376 IF ( x_segment14 IS NOT NULL ) THEN
377 dbms_sql.bind_variable(ccid_cursor, ':segment14', x_segment14);
378 END IF;
379
380 IF ( x_segment15 IS NOT NULL ) THEN
381 dbms_sql.bind_variable(ccid_cursor, ':segment15', x_segment15);
382 END IF;
383
384 IF ( x_segment16 IS NOT NULL ) THEN
385 dbms_sql.bind_variable(ccid_cursor, ':segment16', x_segment16);
386 END IF;
387
388 IF ( x_segment17 IS NOT NULL ) THEN
389 dbms_sql.bind_variable(ccid_cursor, ':segment17', x_segment17);
390 END IF;
391
392 IF ( x_segment18 IS NOT NULL ) THEN
393 dbms_sql.bind_variable(ccid_cursor, ':segment18', x_segment18);
394 END IF;
395
396 IF ( x_segment19 IS NOT NULL ) THEN
397 dbms_sql.bind_variable(ccid_cursor, ':segment19', x_segment19);
398 END IF;
399
400 IF ( x_segment20 IS NOT NULL ) THEN
401 dbms_sql.bind_variable(ccid_cursor, ':segment20', x_segment20);
402 END IF;
403
404 IF ( x_segment21 IS NOT NULL ) THEN
405 dbms_sql.bind_variable(ccid_cursor, ':segment21', x_segment21);
406 END IF;
407
408 IF ( x_segment22 IS NOT NULL ) THEN
409 dbms_sql.bind_variable(ccid_cursor, ':segment22', x_segment22);
410 END IF;
411
412 IF ( x_segment23 IS NOT NULL ) THEN
413 dbms_sql.bind_variable(ccid_cursor, ':segment23', x_segment23);
414 END IF;
415
416 IF ( x_segment24 IS NOT NULL ) THEN
417 dbms_sql.bind_variable(ccid_cursor, ':segment24', x_segment24);
418 END IF;
419
420 IF ( x_segment25 IS NOT NULL ) THEN
421 dbms_sql.bind_variable(ccid_cursor, ':segment25', x_segment25);
422 END IF;
423
424 IF ( x_segment26 IS NOT NULL ) THEN
425 dbms_sql.bind_variable(ccid_cursor, ':segment26', x_segment26);
426 END IF;
427
428 IF ( x_segment27 IS NOT NULL ) THEN
429 dbms_sql.bind_variable(ccid_cursor, ':segment27', x_segment27);
430 END IF;
431
432 IF ( x_segment28 IS NOT NULL ) THEN
433 dbms_sql.bind_variable(ccid_cursor, ':segment28', x_segment28);
434 END IF;
435
436 IF ( x_segment29 IS NOT NULL ) THEN
437 dbms_sql.bind_variable(ccid_cursor, ':segment29', x_segment29);
438 END IF;
439
440 IF ( x_segment30 IS NOT NULL ) THEN
441 dbms_sql.bind_variable(ccid_cursor, ':segment30', x_segment30);
442 END IF;
443
444 dbms_sql.bind_variable(ccid_cursor, ':lgr_id', x_ledger_id);
445 row_count := dbms_sql.execute_and_fetch(ccid_cursor);
446
447 IF (row_count = 0) THEN
448 -- Create a detail account
449 IF ( x_conc_seg IS NOT NULL) THEN
450 IF(NOT fnd_flex_keyval.validate_segs(
451 operation => 'CREATE_COMBINATION',
452 appl_short_name => 'SQLGL',
453 key_flex_code => 'GL#',
454 structure_number => x_coa_id,
455 concat_segments => x_conc_seg,
456 validation_date => null,
457 vrule => '\nSUMMARY_FLAG\nI\nAPPL=SQLGL;' ||
458 'NAME=GL_RJE_NO_NEW_SUMMARY\nN'
459 )) THEN
460 x_templgrid := x_ledger_id;
461 x_err_msg :=fnd_flex_keyval.error_message;
462 dbms_sql.close_cursor(ccid_cursor);
463 RETURN FALSE;
464 ELSE
465 x_ccid := fnd_flex_keyval.combination_id;
466 x_templgrid := x_ledger_id;
467 x_acct_type := fnd_flex_keyval.qualifier_value('GL_ACCOUNT_TYPE');
468 END IF;
469 END IF;
470 ELSE
471 dbms_sql.column_value(ccid_cursor, 1, c_ccid);
472 dbms_sql.column_value(ccid_cursor, 2, c_lgr_id);
473 dbms_sql.column_value(ccid_cursor, 3, c_enabled_flag);
474 dbms_sql.column_value(ccid_cursor, 4, c_acct_type);
475
476 IF (c_enabled_flag ='N')THEN
477 x_ccid := c_ccid;
478 x_templgrid := c_lgr_id;
479 x_acct_type := c_acct_type;
480 fnd_message.set_name( 'SQLGL', 'GL_RJE_RULE_INV_CCID' );
481 x_err_msg := fnd_message.get;
482 dbms_sql.close_cursor(ccid_cursor);
483 RETURN FALSE;
484 ELSE
485 x_ccid := c_ccid;
486 x_templgrid := c_lgr_id;
487 x_acct_type := c_acct_type;
488 END IF;
489 END IF;
490 dbms_sql.close_cursor(ccid_cursor);
491 RETURN TRUE;
492 END get_ccid;
493
494 -- *********************************************************************
495
496 PROCEDURE delete_rows( x_header_id NUMBER,
497 x_line_num NUMBER ) IS
498
499 BEGIN
500
501 DELETE
502 FROM GL_RECURRING_LINE_CALC_RULES
506 EXCEPTION
503 WHERE RECURRING_HEADER_ID = x_header_id
504 AND RECURRING_LINE_NUM = x_line_num;
505
507 WHEN app_exceptions.application_exception THEN
508 RAISE;
509 WHEN OTHERS THEN
510 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
511 fnd_message.set_token('PROCEDURE',
512 'GL_RECURRING_RULES_PKG.delete_rows');
513 RAISE;
514
515 END delete_rows;
516
517 -- **********************************************************************
518
519
520 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
521
522 X_Recurring_Header_Id NUMBER,
523 X_Recurring_Line_Num NUMBER,
524 X_Rule_Num NUMBER,
525 X_Last_Update_Date DATE,
526 X_Last_Updated_By NUMBER,
527 X_Operator VARCHAR2,
528 X_Creation_Date DATE,
529 X_Created_By NUMBER,
530 X_Last_Update_Login NUMBER,
531 X_Amount NUMBER,
532 X_Amount_Type VARCHAR2,
533 X_Actual_Flag VARCHAR2,
534 X_Ledger_Currency VARCHAR2,
535 X_Currency_Type VARCHAR2,
536 X_Entered_Currency VARCHAR2,
537 X_Ledger_Id NUMBER,
538 X_Relative_Period_Code VARCHAR2,
539 X_Attribute1 VARCHAR2,
540 X_Attribute2 VARCHAR2,
541 X_Attribute3 VARCHAR2,
542 X_Attribute4 VARCHAR2,
543 X_Attribute5 VARCHAR2,
544 X_Context VARCHAR2,
545 X_Assigned_Code_Combination NUMBER,
546 X_Template_Id NUMBER,
547 X_Segment1 VARCHAR2,
548 X_Segment2 VARCHAR2,
549 X_Segment3 VARCHAR2,
550 X_Segment4 VARCHAR2,
551 X_Segment5 VARCHAR2,
552 X_Segment6 VARCHAR2,
553 X_Segment7 VARCHAR2,
554 X_Segment8 VARCHAR2,
555 X_Segment9 VARCHAR2,
556 X_Segment10 VARCHAR2,
557 X_Segment11 VARCHAR2,
558 X_Segment12 VARCHAR2,
559 X_Segment13 VARCHAR2,
560 X_Segment14 VARCHAR2,
561 X_Segment15 VARCHAR2,
562 X_Segment16 VARCHAR2,
563 X_Segment17 VARCHAR2,
564 X_Segment18 VARCHAR2,
565 X_Segment19 VARCHAR2,
566 X_Segment20 VARCHAR2,
567 X_Segment21 VARCHAR2,
568 X_Segment22 VARCHAR2,
569 X_Segment23 VARCHAR2,
570 X_Segment24 VARCHAR2,
571 X_Segment25 VARCHAR2,
572 X_Segment26 VARCHAR2,
573 X_Segment27 VARCHAR2,
574 X_Segment28 VARCHAR2,
575 X_Segment29 VARCHAR2,
576 X_Segment30 VARCHAR2
577 ) IS
578 CURSOR C IS SELECT rowid FROM GL_RECURRING_LINE_CALC_RULES
579 WHERE recurring_header_id = X_Recurring_Header_Id
580 and recurring_line_num = X_Recurring_Line_Num
581 and rule_num = X_Rule_Num;
582
583 BEGIN
584
585 -- Check line for Uniqueness
586 Check_Unique(X_Rowid, X_Rule_Num, X_Recurring_Line_Num, X_Recurring_Header_Id );
587
588
589 INSERT INTO GL_RECURRING_LINE_CALC_RULES(
590
591 recurring_header_id,
592 recurring_line_num,
593 rule_num,
594 last_update_date,
595 last_updated_by,
596 operator,
597 creation_date,
598 created_by,
599 last_update_login,
600 amount,
601 amount_type,
602 actual_flag,
603 ledger_currency,
604 currency_type,
605 entered_currency,
606 ledger_id,
607 relative_period_code,
608 attribute1,
609 attribute2,
613 context,
610 attribute3,
611 attribute4,
612 attribute5,
614 assigned_code_combination,
615 template_id,
616 segment1,
617 segment2,
618 segment3,
619 segment4,
620 segment5,
621 segment6,
622 segment7,
623 segment8,
624 segment9,
625 segment10,
626 segment11,
627 segment12,
628 segment13,
629 segment14,
630 segment15,
631 segment16,
632 segment17,
633 segment18,
634 segment19,
635 segment20,
636 segment21,
637 segment22,
638 segment23,
639 segment24,
640 segment25,
641 segment26,
642 segment27,
643 segment28,
644 segment29,
645 segment30
646 ) VALUES (
647
648 X_Recurring_Header_Id,
649 X_Recurring_Line_Num,
650 X_Rule_Num,
651 X_Last_Update_Date,
652 X_Last_Updated_By,
653 X_Operator,
654 X_Creation_Date,
655 X_Created_By,
656 X_Last_Update_Login,
657 X_Amount,
658 X_Amount_Type,
659 X_Actual_Flag,
660 X_Ledger_Currency,
661 X_Currency_Type,
662 X_Entered_Currency,
663 X_Ledger_Id,
664 X_Relative_Period_Code,
665 X_Attribute1,
666 X_Attribute2,
667 X_Attribute3,
668 X_Attribute4,
669 X_Attribute5,
670 X_Context,
671 X_Assigned_Code_Combination,
672 X_Template_Id,
673 X_Segment1,
674 X_Segment2,
675 X_Segment3,
676 X_Segment4,
677 X_Segment5,
678 X_Segment6,
679 X_Segment7,
680 X_Segment8,
681 X_Segment9,
682 X_Segment10,
683 X_Segment11,
684 X_Segment12,
685 X_Segment13,
686 X_Segment14,
687 X_Segment15,
688 X_Segment16,
689 X_Segment17,
690 X_Segment18,
691 X_Segment19,
692 X_Segment20,
693 X_Segment21,
694 X_Segment22,
695 X_Segment23,
696 X_Segment24,
697 X_Segment25,
698 X_Segment26,
699 X_Segment27,
700 X_Segment28,
701 X_Segment29,
702 X_Segment30
703
704 );
705
706 OPEN C;
707 FETCH C INTO X_Rowid;
708 if (C%NOTFOUND) then
709 CLOSE C;
710 Raise NO_DATA_FOUND;
711 end if;
712 CLOSE C;
713 END Insert_Row;
714
715
716
717 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
718
719 X_Recurring_Header_Id NUMBER,
720 X_Recurring_Line_Num NUMBER,
721 X_Rule_Num NUMBER,
722 X_Operator VARCHAR2,
723 X_Amount NUMBER,
724 X_Amount_Type VARCHAR2,
725 X_Actual_Flag VARCHAR2,
726 X_Ledger_Currency VARCHAR2,
727 X_Currency_Type VARCHAR2,
728 X_Entered_Currency VARCHAR2,
729 X_Ledger_Id NUMBER,
730 X_Relative_Period_Code VARCHAR2,
731 X_Attribute1 VARCHAR2,
732 X_Attribute2 VARCHAR2,
733 X_Attribute3 VARCHAR2,
734 X_Attribute4 VARCHAR2,
735 X_Attribute5 VARCHAR2,
736 X_Context VARCHAR2,
737 X_Assigned_Code_Combination NUMBER,
738 X_Template_Id NUMBER,
739 X_Segment1 VARCHAR2,
740 X_Segment2 VARCHAR2,
741 X_Segment3 VARCHAR2,
742 X_Segment4 VARCHAR2,
743 X_Segment5 VARCHAR2,
744 X_Segment6 VARCHAR2,
745 X_Segment7 VARCHAR2,
746 X_Segment8 VARCHAR2,
747 X_Segment9 VARCHAR2,
748 X_Segment10 VARCHAR2,
752 X_Segment14 VARCHAR2,
749 X_Segment11 VARCHAR2,
750 X_Segment12 VARCHAR2,
751 X_Segment13 VARCHAR2,
753 X_Segment15 VARCHAR2,
754 X_Segment16 VARCHAR2,
755 X_Segment17 VARCHAR2,
756 X_Segment18 VARCHAR2,
757 X_Segment19 VARCHAR2,
758 X_Segment20 VARCHAR2,
759 X_Segment21 VARCHAR2,
760 X_Segment22 VARCHAR2,
761 X_Segment23 VARCHAR2,
762 X_Segment24 VARCHAR2,
763 X_Segment25 VARCHAR2,
764 X_Segment26 VARCHAR2,
765 X_Segment27 VARCHAR2,
766 X_Segment28 VARCHAR2,
767 X_Segment29 VARCHAR2,
768 X_Segment30 VARCHAR2
769 ) IS
770 CURSOR C IS
771 SELECT *
772 FROM GL_RECURRING_LINE_CALC_RULES
773 WHERE rowid = X_Rowid
774 FOR UPDATE of Recurring_Header_Id NOWAIT;
775 Recinfo C%ROWTYPE;
776
777
778 BEGIN
779 OPEN C;
780 FETCH C INTO Recinfo;
781 if (C%NOTFOUND) then
782 CLOSE C;
783 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
784 APP_EXCEPTION.Raise_Exception;
785 end if;
786 CLOSE C;
787 if (
788
789 (Recinfo.recurring_header_id = X_Recurring_Header_Id)
790 AND (Recinfo.recurring_line_num = X_Recurring_Line_Num)
791 AND (Recinfo.rule_num = X_Rule_Num)
792 AND (Recinfo.operator = X_Operator)
793 AND ( (Recinfo.amount = X_Amount)
794 OR ( (Recinfo.amount IS NULL)
795 AND (X_Amount IS NULL)))
796 AND ( (Recinfo.amount_type = X_Amount_Type)
797 OR ( (Recinfo.amount_type IS NULL)
798 AND (X_Amount_Type IS NULL)))
799 AND ( (Recinfo.actual_flag = X_Actual_Flag)
800 OR ( (Recinfo.actual_flag IS NULL)
801 AND (X_Actual_Flag IS NULL)))
802 AND ( (Recinfo.ledger_currency = X_Ledger_Currency)
803 OR ( (Recinfo.ledger_currency IS NULL)
804 AND (X_Ledger_Currency IS NULL)))
805 AND ( (Recinfo.currency_type = X_Currency_Type)
806 OR ( (Recinfo.currency_type IS NULL)
807 AND (X_Currency_Type IS NULL)))
808 AND ( (Recinfo.entered_currency = X_Entered_Currency)
809 OR ( (Recinfo.entered_currency IS NULL)
810 AND (X_Entered_Currency IS NULL)))
811 AND ( (Recinfo.ledger_id = X_Ledger_Id)
812 OR ( (Recinfo.ledger_id IS NULL)
813 AND (X_Ledger_Id IS NULL)))
814 AND ( (Recinfo.relative_period_code = X_Relative_Period_Code)
815 OR ( (Recinfo.relative_period_code IS NULL)
816 AND (X_Relative_Period_Code IS NULL)))
817 AND ( (Recinfo.attribute1 = X_Attribute1)
818 OR ( (Recinfo.attribute1 IS NULL)
819 AND (X_Attribute1 IS NULL)))
820 AND ( (Recinfo.attribute2 = X_Attribute2)
821 OR ( (Recinfo.attribute2 IS NULL)
822 AND (X_Attribute2 IS NULL)))
823 AND ( (Recinfo.attribute3 = X_Attribute3)
824 OR ( (Recinfo.attribute3 IS NULL)
825 AND (X_Attribute3 IS NULL)))
826 AND ( (Recinfo.attribute4 = X_Attribute4)
827 OR ( (Recinfo.attribute4 IS NULL)
828 AND (X_Attribute4 IS NULL)))
829 AND ( (Recinfo.attribute5 = X_Attribute5)
830 OR ( (Recinfo.attribute5 IS NULL)
831 AND (X_Attribute5 IS NULL)))
832 AND ( (Recinfo.context = X_Context)
833 OR ( (Recinfo.context IS NULL)
834 AND (X_Context IS NULL)))
835 AND ( (Recinfo.assigned_code_combination = X_Assigned_Code_Combination)
836 OR ( (Recinfo.assigned_code_combination IS NULL)
837 AND (X_Assigned_Code_Combination IS NULL)))
838 AND ( (Recinfo.template_id = X_Template_Id)
839 OR ( (Recinfo.template_id IS NULL)
840 AND (X_Template_Id IS NULL)))
841 AND ( (Recinfo.segment1 = X_Segment1)
842 OR ( (Recinfo.segment1 IS NULL)
843 AND (X_Segment1 IS NULL)))
844 AND ( (Recinfo.segment2 = X_Segment2)
845 OR ( (Recinfo.segment2 IS NULL)
846 AND (X_Segment2 IS NULL)))
847 AND ( (Recinfo.segment3 = X_Segment3)
848 OR ( (Recinfo.segment3 IS NULL)
849 AND (X_Segment3 IS NULL)))
850 AND ( (Recinfo.segment4 = X_Segment4)
854 OR ( (Recinfo.segment5 IS NULL)
851 OR ( (Recinfo.segment4 IS NULL)
852 AND (X_Segment4 IS NULL)))
853 AND ( (Recinfo.segment5 = X_Segment5)
855 AND (X_Segment5 IS NULL)))
856 AND ( (Recinfo.segment6 = X_Segment6)
857 OR ( (Recinfo.segment6 IS NULL)
858 AND (X_Segment6 IS NULL)))
859 AND ( (Recinfo.segment7 = X_Segment7)
860 OR ( (Recinfo.segment7 IS NULL)
861 AND (X_Segment7 IS NULL)))
862 AND ( (Recinfo.segment8 = X_Segment8)
863 OR ( (Recinfo.segment8 IS NULL)
864 AND (X_Segment8 IS NULL)))
865 AND ( (Recinfo.segment9 = X_Segment9)
866 OR ( (Recinfo.segment9 IS NULL)
867 AND (X_Segment9 IS NULL)))
868 AND ( (Recinfo.segment10 = X_Segment10)
869 OR ( (Recinfo.segment10 IS NULL)
870 AND (X_Segment10 IS NULL)))
871 AND ( (Recinfo.segment11 = X_Segment11)
872 OR ( (Recinfo.segment11 IS NULL)
873 AND (X_Segment11 IS NULL)))
874 AND ( (Recinfo.segment12 = X_Segment12)
875 OR ( (Recinfo.segment12 IS NULL)
876 AND (X_Segment12 IS NULL)))
877 AND ( (Recinfo.segment13 = X_Segment13)
878 OR ( (Recinfo.segment13 IS NULL)
879 AND (X_Segment13 IS NULL)))
880 AND ( (Recinfo.segment14 = X_Segment14)
881 OR ( (Recinfo.segment14 IS NULL)
882 AND (X_Segment14 IS NULL)))
883 AND ( (Recinfo.segment15 = X_Segment15)
884 OR ( (Recinfo.segment15 IS NULL)
885 AND (X_Segment15 IS NULL)))
886 AND ( (Recinfo.segment16 = X_Segment16)
887 OR ( (Recinfo.segment16 IS NULL)
888 AND (X_Segment16 IS NULL)))
889 AND ( (Recinfo.segment17 = X_Segment17)
890 OR ( (Recinfo.segment17 IS NULL)
891 AND (X_Segment17 IS NULL)))
892 AND ( (Recinfo.segment18 = X_Segment18)
893 OR ( (Recinfo.segment18 IS NULL)
894 AND (X_Segment18 IS NULL)))
895 AND ( (Recinfo.segment19 = X_Segment19)
896 OR ( (Recinfo.segment19 IS NULL)
897 AND (X_Segment19 IS NULL)))
898 AND ( (Recinfo.segment20 = X_Segment20)
899 OR ( (Recinfo.segment20 IS NULL)
900 AND (X_Segment20 IS NULL)))
901 AND ( (Recinfo.segment21 = X_Segment21)
902 OR ( (Recinfo.segment21 IS NULL)
903 AND (X_Segment21 IS NULL)))
904 AND ( (Recinfo.segment22 = X_Segment22)
905 OR ( (Recinfo.segment22 IS NULL)
906 AND (X_Segment22 IS NULL)))
907 AND ( (Recinfo.segment23 = X_Segment23)
908 OR ( (Recinfo.segment23 IS NULL)
909 AND (X_Segment23 IS NULL)))
910 AND ( (Recinfo.segment24 = X_Segment24)
911 OR ( (Recinfo.segment24 IS NULL)
912 AND (X_Segment24 IS NULL)))
913 AND ( (Recinfo.segment25 = X_Segment25)
914 OR ( (Recinfo.segment25 IS NULL)
915 AND (X_Segment25 IS NULL)))
916 AND ( (Recinfo.segment26 = X_Segment26)
917 OR ( (Recinfo.segment26 IS NULL)
918 AND (X_Segment26 IS NULL)))
919 AND ( (Recinfo.segment27 = X_Segment27)
920 OR ( (Recinfo.segment27 IS NULL)
921 AND (X_Segment27 IS NULL)))
922 AND ( (Recinfo.segment28 = X_Segment28)
923 OR ( (Recinfo.segment28 IS NULL)
924 AND (X_Segment28 IS NULL)))
925 AND ( (Recinfo.segment29 = X_Segment29)
926 OR ( (Recinfo.segment29 IS NULL)
927 AND (X_Segment29 IS NULL)))
928 AND ( (Recinfo.segment30 = X_Segment30)
929 OR ( (Recinfo.segment30 IS NULL)
930 AND (X_Segment30 IS NULL)))
931 ) then
932 return;
933 else
934 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
935 APP_EXCEPTION.Raise_Exception;
936 end if;
937 END Lock_Row;
938
939
940
941 PROCEDURE Update_Row(X_Rowid VARCHAR2,
942
943 X_Recurring_Header_Id NUMBER,
944 X_Recurring_Line_Num NUMBER,
945 X_Rule_Num NUMBER,
946 X_Last_Update_Date DATE,
947 X_Last_Updated_By NUMBER,
948 X_Operator VARCHAR2,
949 X_Last_Update_Login NUMBER,
950 X_Amount NUMBER,
951 X_Amount_Type VARCHAR2,
952 X_Actual_Flag VARCHAR2,
953 X_Ledger_Currency VARCHAR2,
954 X_Currency_Type VARCHAR2,
955 X_Entered_Currency VARCHAR2,
959 X_Attribute2 VARCHAR2,
956 X_Ledger_Id NUMBER,
957 X_Relative_Period_Code VARCHAR2,
958 X_Attribute1 VARCHAR2,
960 X_Attribute3 VARCHAR2,
961 X_Attribute4 VARCHAR2,
962 X_Attribute5 VARCHAR2,
963 X_Context VARCHAR2,
964 X_Assigned_Code_Combination NUMBER,
965 X_Template_Id NUMBER,
966 X_Segment1 VARCHAR2,
967 X_Segment2 VARCHAR2,
968 X_Segment3 VARCHAR2,
969 X_Segment4 VARCHAR2,
970 X_Segment5 VARCHAR2,
971 X_Segment6 VARCHAR2,
972 X_Segment7 VARCHAR2,
973 X_Segment8 VARCHAR2,
974 X_Segment9 VARCHAR2,
975 X_Segment10 VARCHAR2,
976 X_Segment11 VARCHAR2,
977 X_Segment12 VARCHAR2,
978 X_Segment13 VARCHAR2,
979 X_Segment14 VARCHAR2,
980 X_Segment15 VARCHAR2,
981 X_Segment16 VARCHAR2,
982 X_Segment17 VARCHAR2,
983 X_Segment18 VARCHAR2,
984 X_Segment19 VARCHAR2,
985 X_Segment20 VARCHAR2,
986 X_Segment21 VARCHAR2,
987 X_Segment22 VARCHAR2,
988 X_Segment23 VARCHAR2,
989 X_Segment24 VARCHAR2,
990 X_Segment25 VARCHAR2,
991 X_Segment26 VARCHAR2,
992 X_Segment27 VARCHAR2,
993 X_Segment28 VARCHAR2,
994 X_Segment29 VARCHAR2,
995 X_Segment30 VARCHAR2
996
997 ) IS
998
999 BEGIN
1000
1001 -- Check line for Uniqueness
1002 Check_Unique(X_Rowid, X_Rule_Num, X_Recurring_Line_Num, X_Recurring_Header_Id );
1003
1004
1005 UPDATE GL_RECURRING_LINE_CALC_RULES
1006 SET
1007 recurring_header_id = X_Recurring_Header_Id,
1008 recurring_line_num = X_Recurring_Line_Num,
1009 rule_num = X_Rule_Num,
1010 last_update_date = X_Last_Update_Date,
1011 last_updated_by = X_Last_Updated_By,
1012 operator = X_Operator,
1013 last_update_login = X_Last_Update_Login,
1014 amount = X_Amount,
1015 amount_type = X_Amount_Type,
1016 actual_flag = X_Actual_Flag,
1017 ledger_currency = X_Ledger_Currency,
1018 currency_type = X_Currency_Type,
1019 entered_currency = X_Entered_Currency,
1020 ledger_id = X_Ledger_Id,
1021 relative_period_code = X_Relative_Period_Code,
1022 attribute1 = X_Attribute1,
1023 attribute2 = X_Attribute2,
1024 attribute3 = X_Attribute3,
1025 attribute4 = X_Attribute4,
1026 attribute5 = X_Attribute5,
1027 context = X_Context,
1028 assigned_code_combination = X_Assigned_Code_Combination,
1029 template_id = X_Template_Id,
1030 segment1 = X_Segment1,
1031 segment2 = X_Segment2,
1032 segment3 = X_Segment3,
1033 segment4 = X_Segment4,
1034 segment5 = X_Segment5,
1035 segment6 = X_Segment6,
1036 segment7 = X_Segment7,
1037 segment8 = X_Segment8,
1038 segment9 = X_Segment9,
1039 segment10 = X_Segment10,
1040 segment11 = X_Segment11,
1041 segment12 = X_Segment12,
1042 segment13 = X_Segment13,
1043 segment14 = X_Segment14,
1044 segment15 = X_Segment15,
1045 segment16 = X_Segment16,
1046 segment17 = X_Segment17,
1047 segment18 = X_Segment18,
1048 segment19 = X_Segment19,
1049 segment20 = X_Segment20,
1050 segment21 = X_Segment21,
1051 segment22 = X_Segment22,
1052 segment23 = X_Segment23,
1053 segment24 = X_Segment24,
1054 segment25 = X_Segment25,
1055 segment26 = X_Segment26,
1056 segment27 = X_Segment27,
1057 segment28 = X_Segment28,
1058 segment29 = X_Segment29,
1059 segment30 = X_Segment30
1060 WHERE rowid = X_Rowid;
1061
1062 if (SQL%NOTFOUND) then
1063 Raise NO_DATA_FOUND;
1064 end if;
1065 END Update_Row;
1066
1067
1068 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1069 BEGIN
1070 DELETE FROM GL_RECURRING_LINE_CALC_RULES
1071 WHERE rowid = X_Rowid;
1072
1073 if (SQL%NOTFOUND) then
1074 Raise NO_DATA_FOUND;
1075 end if;
1076 END Delete_Row;
1077
1078 -- **********************************************************************
1079 PROCEDURE get_account_type( x_coa_id NUMBER,
1080 x_conc_seg VARCHAR2,
1081 x_account_type OUT NOCOPY VARCHAR2) IS
1082 BEGIN
1083 IF (x_conc_seg IS NOT NULL) THEN
1084 IF(fnd_flex_keyval.validate_segs(
1085 operation => 'CHECK_COMBINATION',
1086 appl_short_name => 'SQLGL',
1087 key_flex_code => 'GL#',
1088 structure_number => x_coa_id,
1089 concat_segments => x_conc_seg,
1090 validation_date => null)) THEN
1091 x_account_type := fnd_flex_keyval.qualifier_value('GL_ACCOUNT_TYPE');
1092 END IF;
1093 END IF;
1094
1095 END get_account_type;
1096
1097
1098 -- **********************************************************************
1099
1100 END GL_RECURRING_RULES_PKG;