[Home] [Help]
PACKAGE BODY: APPS.IGIRGLOBE
Source
1 PACKAGE BODY IGIRGLOBE AS
2 -- $Header: igirglbb.pls 120.9 2008/03/18 19:14:36 vkilambi ship $
3
4 g_user_id NUMBER := fnd_global.user_id;
5 g_date DATE := sysdate;
6 g_login_id NUMBER := fnd_global.login_id;
7
8
9
10 PROCEDURE WriteToLog ( pp_mesg in varchar2 ) IS
11 BEGIN
12 FND_FILE.Put_line( FND_FILE.log, pp_mesg );
13 END;
14
15 PROCEDURE WriteToLog ( pp_mesg in varchar2, pp_write_log in boolean ) IS
16 BEGIN
17 if pp_write_log then
18 WriteToLog ( pp_mesg => pp_mesg );
19 end if;
20 END;
21
22 FUNCTION Get_functional_currency RETURN VARCHAR2 IS
23 CURSOR c_sob (cp_sob_id in number) IS
24 SELECT gsob.currency_code
25 FROM gl_sets_of_books gsob
26 WHERE set_of_books_id = cp_sob_id;
27 CURSOR c_sob_id IS
28 SELECT set_of_books_id
29 FROM ar_system_parameters;
30 BEGIN
31 FOR l_sob_id in c_sob_id LOOP
32 FOR l_sob IN c_sob ( l_sob_id.set_of_books_id ) LOOP
33 RETURN l_sob.currency_code;
34
35 END LOOP;
36 end LOOP;
37 RETURN NULL;
38 END;
39
40 FUNCTION Get_functional_sob_name Return VARCHAR2 IS
41 CURSOR c_sob_name is
42 SELECT name
43 FROM gl_sets_of_books
44 where set_of_books_id = ( select set_of_books_id from ar_system_parameters );
45
46 BEGIN
47 FOR l_sob_name in c_sob_name LOOP
48 return l_sob_name.name;
49 END LOOP;
50 return NULL;
51 EXCEPTION WHEN others THEN return null;
52 END;
53
54 PROCEDURE PopulateSystemOptions IS
55
56 cursor c_ar_system_options is
57 select set_of_books_id, org_id, accounting_method,
58 sysdate creation_date, sysdate last_update_date,
59 -1 last_updated_by, -1 created_by, -1 last_update_login
60 from ar_system_parameters_all aspa
61 where not exists ( select 'Already set up'
62 from igi_ar_system_options_all
63 where set_of_books_id = aspa.set_of_books_id
64 and org_id = aspa.org_id )
65 ;
66
67 BEGIN
68
69
70 for l_asp in c_ar_system_options loop
71 insert into igi_ar_system_options_all ( set_of_books_id
72 , accounting_method
73 , org_id
74 , creation_date
75 , created_by
76 , last_update_date
77 , last_updated_by
78 , last_update_login
79 )
80 values (l_asp.set_of_books_id,
81 l_asp.accounting_method
82 , l_asp.org_id
83 , g_date
84 , g_user_id
85 , g_date
86 , g_user_id
87 , g_login_id
88 );
89 end loop;
90
91 /* Start Bug 3749634 */
92 DELETE FROM igi_ar_system_options_all a
93 WHERE NOT EXISTS
94 (
95 SELECT 'X'
96 FROM ar_system_parameters_all
97 WHERE org_id = a.org_id
98 AND set_of_books_id = a.set_of_books_id
99 );
100 /* End Bug 3749634 */
101
102
103 END;
104
105 /*Commented due to dummy view in Dunning Letter Bug No 5905216 - Start*/
106 /*
107
108 PROCEDURE PopulateLetterCurrencies IS
109 cursor c_letters is
110 select dunning_letter_set_id
111 from ar_dunning_letter_sets core
112 where not exists
113 ( select 'x'
114 from igi_dun_letter_set_cur igi
115 where igi.dunning_letter_set_id =
116 core.dunning_letter_set_id
117 )
118 ;
119 cursor c_default_curr is
120 select currency_code
121 from gl_sets_of_books
122 where set_of_books_id in ( select set_of_books_id
123 from ar_system_parameters
124 )
125 ;
126 cursor c_customers is
127 select customer_id
128 from ra_customers
129 ;
130 cursor c_profiles (cp_customer_id in number) is
131 select distinct acpa.currency_code, acp.dunning_letter_set_id
132 from ar_customer_profile_amounts acpa
133 , ar_customer_profiles acp
134 where acpa.customer_id = cp_customer_id
135 and acp.customer_id = acpa.customer_id
136 and acp.dunning_letter_set_id is not null
137 and exists
138 (
139 select 'x'
140 from igi_dun_letter_sets idls
141 where idls.dunning_letter_set_id =
142 acp.dunning_letter_set_id
143 )
144 ;
145
146
147 cursor c_delete_currency ( cp_customer_id in number,
148 cp_letter_set_id in number) is
149 select currency_code
150 from igi_dun_letter_set_cur
151 where dunning_letter_set_id = cp_letter_set_id
152 and currency_code not in
153 (
154 select currency_code
155 from ar_customer_profile_amounts
156 where customer_profile_id
157 in (
158 select distinct customer_profile_id
159 from ar_customer_profiles
160 where customer_id = cp_customer_id
161 )
162 and currency_code is not null
163 union
164 select currency_code
165 from gl_sets_of_books
166 where set_of_books_id =
167 ( select set_of_books_id
168 from ar_system_parameters
169 )
170 )
171 ;
172
173
174
175
176 BEGIN
177 WriteToLog ('----------------------------------------------');
178 --
179 -- Populate the Functional currency as default for all
180 -- letter sets!
181 --
182 FOR l_dc IN c_default_curr LOOP
183 FOR l_letters in c_letters LOOP
184 INSERT INTO igi_dun_letter_set_cur
185 ( dunning_letter_set_id
186 , currency_code
187 , created_by
188 , creation_date
189 , last_updated_by
190 , last_update_date
191 , last_update_login
192 )
193 SELECT
194 l_letters.dunning_letter_set_id
195 , l_dc.currency_code
196 , g_user_id
197 , g_date
198 , g_user_id
199 , g_date
200 , g_login_id
201 FROM SYS.DUAL
202 WHERE NOT EXISTS
203 ( SELECT 'x'
204 FROM igi_dun_letter_set_cur
205 WHERE dunning_letter_set_id =
206 l_letters.dunning_letter_set_id
207 AND currency_code =
208 l_dc.currency_code
209 )
210 ;
211 END LOOP;
212 END LOOP;
213 --
214 -- Now verify the extra currencies set at
215 -- the customer profile level and insert the new
216 -- currencies if possible
217 --
218 FOR l_cust IN C_customers LOOP
219 FOR l_prof in C_profiles ( l_cust.customer_id) LOOP
220 WriteToLog( 'Letter Set id '|| l_prof.dunning_letter_set_id );
221
222 INSERT INTO igi_dun_letter_set_cur
223 ( dunning_letter_set_id
224 , currency_code
225 , created_by
226 , creation_date
227 , last_updated_by
228 , last_update_date
229 , last_update_login
230 )
231 SELECT
232 l_prof.dunning_letter_set_id
233 , l_prof.currency_code
234 , g_user_id
235 , g_date
236 , g_user_id
237 , g_date
238 , g_login_id
239 FROM SYS.DUAL
240 WHERE NOT EXISTS
241 ( SELECT 'x'
242 FROM igi_dun_letter_set_cur
243 WHERE dunning_letter_set_id =
244 l_prof.dunning_letter_set_id
245 AND currency_code =
246 l_prof.currency_code
247 )
248 ;
249 WriteToLog('Inserting Currency '||l_prof.currency_code );
250
251 END LOOP;
252 END LOOP;
253 WriteToLog ('----------------------------------------------');
254
255 END;
256
257
258 PROCEDURE PopulateLetterSets IS
259 cursor c_ar_dunning_letter_sets is
260 select dunning_letter_set_id,
261 'Y' use_dunning_flag,
262 'N' charge_per_invoice_flag
263 from ar_dunning_letter_sets ardls
264 where not exists ( select 'Already set up'
265 from igi_dun_letter_sets
266 where dunning_letter_set_id = ardls.dunning_letter_set_id);
267 begin
268 for dlsrec in c_ar_dunning_letter_sets loop
269 insert into igi_dun_letter_sets (
270 dunning_letter_set_id,
271 use_dunning_flag,
272 charge_per_invoice_flag,
273 created_by,
274 creation_date,
275 last_updated_by,
276 last_update_date,
277 last_update_login
278 ) VALUES (
279 dlsrec.dunning_letter_set_id,
280 dlsrec.use_dunning_flag,
281 dlsrec.charge_per_invoice_flag,
282 g_user_id,
283 g_date,
284 g_user_id,
285 g_date,
286 g_login_id
287 );
288 end loop;
289 exception when others then
290 raise_application_error(-20001, SQLERRM );
291 END PopulateLetterSets;
292
293
294 PROCEDURE UpdateBlankCustLetters IS
295 CURSOR C_dlsl IS
296 SELECT IDLSL.*
297 FROM igi_dun_letter_Set_lines IDLSL
298 ;
299
300 CURSOR c_dlscl ( p_dunning_letter_set_id in number
301 , p_dunning_line_num in number
302 , p_dunning_letter_id in number
303 , p_currency_code in varchar2)
304 IS
305 SELECT IDCLSL.rowid row_id , IDCLSL.*
306 FROM igi_dun_cust_letter_set_lines IDCLSL
307 WHERE IDCLSL.dunning_letter_set_id = p_dunning_letter_Set_id
308 AND IDCLSL.dunning_line_num = p_dunning_line_num
309 AND IDCLSL.dunning_letter_id = p_dunning_letter_id
310 AND IDCLSL.currency_code = p_currency_code
311 ;
312 BEGIN
313 FOR l_dlsl in c_dlsl LOOP
314 FOR l_dlscl in c_dlscl ( l_dlsl.dunning_letter_set_id
315 , l_dlsl.dunning_line_num
316 , l_dlsl.dunning_letter_id
317 , l_dlsl.currency_code ) LOOP
318 IF (nvl(l_dlscl.letter_charge_amount,0) <>
319 nvl(l_dlsl.letter_charge_amount,0)) THEN
320 UPDATE igi_dun_cust_letter_set_lines
321 SET letter_charge_amount = l_dlsl.letter_charge_amount
322 WHERE ROWID = l_dlscl.row_id
323 ;
324 END IF;
325 IF nvl(l_dlscl.invoice_charge_amount,0) <>
326 nvl(l_dlsl.invoice_charge_amount,0) THEN
327 UPDATE igi_dun_cust_letter_set_lines
328 SET invoice_charge_amount = l_dlsl.invoice_charge_amount
329 WHERE ROWID = l_dlscl.row_id
330 ;
331 END IF;
332 END LOOP;
333 END LOOP;
334 END;
335
336
337 PROCEDURE PopulateCustLetters IS
338
339
340 -- Cursor to retrieve all the Customer profiles ALREADY copied to the extended tables
341 -- This is okay as this routine is Dependedent on PopulateCustProfiles.
342
343
344 cursor c_profiles IS
345 SELECT acp.dunning_letter_set_id, acp.customer_id, acp.site_use_id,
346 acp.customer_profile_class_id, acp.customer_profile_id
347 from igi_dun_customer_profile_v acp, igi_dun_cust_prof idcp
348 where acp.customer_profile_id = idcp.customer_profile_id
349 and acp.dunning_letter_set_id is not null
350 ;
351
352 -- We need to find all the currencies associated with the Dunning Letter Set
353 -- (This is not the same as one used for the Customer)
354
355 cursor c_currency (cp_dunning_letter_set_id in number) IS
356 SELECT idlsc.currency_code
357 from igi_dun_letter_Set_cur idlsc
358 where idlsc.dunning_letter_set_id = cp_dunning_letter_set_id
359 ;
360
361
362 -- The letter set line level information for the dunning letter set
363 -- We need to filter it with currency as to make it modular
364
365
366 cursor C_LettersetLines ( cp_dunning_letter_set_id in number
367 , cp_currency_code in varchar2
368 ) IS
369 SELECT igclsl.dunning_letter_id
370 , igclsl.dunning_line_num
371 , igclsl.currency_code
372 , igclsl.letter_charge_amount
373 , igclsl.invoice_charge_amount
374 from igi_dun_letter_set_lines igclsl
375 where igclsl.dunning_letter_set_id = cp_dunning_letter_set_id
376 and igclsl.currency_code = cp_currency_code
377 ;
378
379
380 -- This function tests whether dunning letter set exists in the customer table
381 -- and also all the currency codes in the customer letter sets table match
382 -- the list of currency codes at the letter set level
383
384
385 FUNCTION DunningLetterSetExists ( cp_dunning_letter_set_id in number
386 , cp_customer_profile_id in number)
387 RETURN BOOLEAN IS
388 CURSOR c_exists IS select 'x'
389 from igi_dun_cust_letter_set_lines
390 where customer_profile_id = cp_customer_profile_id
391 and dunning_letter_set_id = cp_dunning_letter_set_id
392 and not exists
393 ( select 'x'
394 from igi_dun_cust_letter_set_cur cls
395 where customer_profile_id = cp_customer_profile_id
396 and not exists
397 ( select currency_code
398 from igi_dun_letter_Set_cur
399 where dunning_letter_set_id = cp_dunning_letter_set_id
400 and currency_code = cls.currency_code
401 )
402 )
403 ;
404 BEGIN
405 for l_exists in c_exists loop
406 return TRUE;
407 end loop;
408 return FALSE;
409 EXCEPTION WHEN OTHERS THEN return TRUE;
410 END DunningLetterSetExists;
411
412 begin
413
414 ---Bug 6847295 - Performance tuning for Bug FP: 6647140 Code change starts
415
416
417
418 delete from igi_dun_cust_letter_set_lines lines
419 WHERE
420 NOT exists ( SELECT 'Y' FROM IGI_DUN_CUSTOMER_PROFILE_V PROF
421 where LINES.CUSTOMER_ID = PROF.CUSTOMER_ID
422 and LINES.CUSTOMER_PROFILE_ID = PROF.CUSTOMER_PROFILE_ID
423 and NVL(LINES.SITE_USE_ID,-1) = NVL(PROF.SITE_USE_ID,-1)
424 and NVL(LINES.CUSTOMER_PROFILE_CLASS_ID, -1) = NVL(PROF.CUSTOMER_PROFILE_CLASS_ID,-1));
425
426 ---Bug 6847295 - Performance tuning for Bug FP: 6647140 Code change ends
427
428 for l_profile in c_profiles loop
429 -- Check if the letter set id and currency
430
431
432 -- if nvl( l_profile.dunning_letter_set_id,-1) = -1 then
433 -- Delete Letter sets
434
435 -- delete from igi_dun_cust_letter_set_lines
436 -- where customer_profile_id = l_profile.customer_profile_id;
437 -- delete from igi_dun_cust_letter_set_cur
438 -- where customer_profile_id = l_profile.customer_profile_id;
439 -- Delete Letter sets
440 -- els
441
442 if not DunningLetterSetExists ( l_profile.customer_profile_id
443 , l_profile.dunning_letter_set_id )
444 THEN
445
446 DECLARE
447 cursor c_delete is
448 select rowid row_id
449 from igi_dun_cust_letter_set_lines idclsl
450 where dunning_letter_set_id = l_profile.dunning_letter_set_id
451 and customer_profile_id = l_profile.customer_profile_id
452 and (dunning_letter_set_id, dunning_line_num,
453 dunning_letter_id, currency_code)
454 not in (
455 select dunning_letter_set_id
456 , dunning_line_num
457 , dunning_letter_id
458 , currency_code
459 from igi_dun_letter_set_lines idlsl
460 where idlsl.dunning_letter_set_id =
461 idclsl.dunning_letter_set_id
462 ) ;
463 BEGIN
464 for l_rowid in c_delete loop
465 delete from igi_dun_cust_letter_set_lines
466 where rowid = l_rowid.row_id
467 ;
468 end loop;
469 END;
470
471 DECLARE
472 cursor c_delete is
473 select rowid row_id
474 from igi_dun_cust_letter_set_cur idclsl
475 where customer_profile_id = l_profile.customer_profile_id
476 and (customer_profile_id, currency_code)
477 not in (
478 select customer_profile_id
479 , currency_code
480 from igi_dun_cust_letter_set_lines idlsl
481 where customer_profile_id = l_profile.customer_profile_id
482 ) ;
483 BEGIN
484 for l_rowid in c_delete loop
485 delete from igi_dun_cust_letter_set_cur
486 where rowid = l_rowid.row_id
487 ;
488 end loop;
489 END;
490
491 for l_currency in c_currency ( l_profile.dunning_letter_set_id ) loop
492
493 insert into igi_dun_cust_letter_set_cur (
494 customer_profile_id,
495 currency_code,
496 created_by,
497 creation_date,
498 last_update_date,
499 last_updated_by,
500 last_update_login )
501 select
502 l_profile.customer_profile_id
503 , l_currency.currency_code
504 , g_user_id
505 , g_date
506 , g_date
507 , g_user_id
508 , g_login_id
509 from sys.dual
510 where not exists
511 ( select 'x'
512 from igi_dun_cust_letter_set_cur
513 where customer_profile_id = l_profile.customer_profile_id
514 and currency_code = l_currency.currency_code
515 )
516 ;
517
518 for l_lines in c_lettersetlines ( l_profile.dunning_letter_set_id,
519 l_currency.currency_code )
520 loop
521
522 insert into igi_dun_cust_letter_set_lines (
523 customer_profile_id,
524 customer_profile_class_id,
525 customer_id,
526 site_use_id,
527 dunning_letter_set_id,
528 dunning_line_num,
529 dunning_letter_id,
530 currency_code,
531 letter_charge_amount,
532 invoice_charge_amount,
533 created_by,
534 creation_date,
535 last_update_date,
536 last_updated_by,
537 last_update_login
538 )
539 select l_profile.customer_profile_id
540 , l_profile.customer_profile_class_id
541 , l_profile.customer_id
542 , l_profile.site_use_id
543 , l_profile.dunning_letter_set_id
544 , l_lines.dunning_line_num
545 , l_lines.dunning_letter_id
546 , l_lines.currency_code
547 , l_lines.letter_charge_amount
548 , l_lines.invoice_charge_amount
549 , g_user_id
550 , g_date
551 , g_date
552 , g_user_id
553 , g_login_id
554 from sys.dual
555 where not exists
556 ( select 'x'
557 from igi_dun_cust_letter_set_lines
558 where customer_profile_id =
559 l_profile.customer_profile_id
560 and customer_profile_class_id = l_profile.customer_profile_class_id
561 and dunning_letter_set_id = l_profile.dunning_letter_set_id
562 and dunning_line_num = l_lines.dunning_line_num
563 and currency_code = l_lines.currency_code
564 )
565 ;
566 end loop;
567 end loop;
568
569 end if;
570
571 end loop;
572
573 exception when others then
574 raise_application_error(-20001, SQLERRM );
575
576 END PopulateCustLetters;
577
578
579
580
581 PROCEDURE PopulateCustProfiles IS
582 cursor c_ar_customer_profiles is
583 select customer_profile_id,
584 'Y' use_dunning_flag,
585 'A' dunning_charge_type
586 from ar_customer_profiles arcp
587 where not exists (select 'Already set up'
588 from igi_dun_cust_prof
589 where customer_profile_id = arcp.customer_profile_id);
590
591
592 begin
593 for arcprec in c_ar_customer_profiles loop
594 insert into igi_dun_cust_prof(
595 customer_profile_id,
596 use_dunning_flag,
597 dunning_charge_type,
598 created_by,
599 creation_date,
600 last_updated_by,
601 lasT_update_date,
602 last_update_login
603 ) SELECT
604 arcprec.customer_profile_id,
605 arcprec.use_dunning_flag,
606 arcprec.dunning_charge_type,
607 g_user_id,
608 g_date,
609 g_user_id,
610 g_date,
611 g_login_id
612 FROM SYS.DUAL
613 WHERE NOT EXISTS ( SELECT 'x'
614 FROM igi_dun_cust_prof
615 WHERE customer_profile_id
616 = arcprec.customer_profile_id
617 )
618 ;
619 end loop;
620
621
622 exception when others then
623 raise_application_error(-20001, SQLERRM );
624
625 END PopulateCustProfiles;
626
627 PROCEDURE PopulateLetterSetLines IS
628 cursor c_ar_dunning_letter_sets is
629 select ardlsl.dunning_letter_set_id,
630 dunning_line_num,
631 dunning_letter_id,
632 igicur.currency_code,
633 'Y' use_dunning_flag,
634 'N' charge_per_invoice_flag
635 from ar_dunning_letter_set_lines ardlsl,
636 igi_dun_letter_set_cur igicur
637 where ardlsl.dunning_letter_set_id = igicur.dunning_letter_set_id
638 and exists ( select 'Already set up'
639 from igi_dun_letter_sets
640 where dunning_letter_set_id = ardlsl.dunning_letter_set_id)
641 and ( ardlsl.dunning_letter_set_id, ardlsl.dunning_line_num,
642 ardlsl.dunning_letter_id, igicur.currency_code)
643 not in ( select dunning_letter_set_id, dunning_line_num,
644 dunning_letter_id, currency_code
645 from igi_dun_letter_set_lines )
646 ;
647 begin
648 delete from igi_dun_letter_set_lines igi
649 where (igi.dunning_letter_set_id,
650 igi.dunning_letter_id,
651 igi.dunning_line_num ) not in (
652 Select ar.dunning_letter_set_id,
653 ar.dunning_letter_id,
654 ar.dunning_line_num
655 from ar_dunning_letter_set_lines ar);
656
657 delete from igi_dun_cust_letter_set_lines igi
658 where (igi.dunning_letter_set_id,
659 igi.dunning_letter_id,
660 igi.dunning_line_num ) not in (
661 Select ar.dunning_letter_set_id,
662 ar.dunning_letter_id,
663 ar.dunning_line_num
664 from ar_dunning_letter_set_lines ar);
665
666 for dlsrec in c_ar_dunning_letter_sets loop
667 insert into igi_dun_letter_set_lines (
668 dunning_letter_set_id,
669 dunning_line_num,
670 dunning_letter_id,
671 currency_code,
672 created_by,
673 creation_date,
674 last_updated_by,
675 last_update_date,
676 last_update_login
677 ) SELECT
678 dlsrec.dunning_letter_set_id,
679 dlsrec.dunning_line_num,
680 dlsrec.dunning_letter_id,
681 dlsrec.currency_code,
682 g_user_id,
683 g_date,
684 g_user_id,
685 g_date,
686 g_login_id
687 FROM SYS.DUAL
688 WHERE NOT EXISTS
689 ( SELECT 'x'
690 FROM igi_dun_letter_Set_lines
691 WHERE dunning_letter_set_id = dlsrec.dunning_letter_set_id
692 AND dunning_line_num = dlsrec.dunning_line_num
693 AND currency_code = dlsrec.currency_code
694 );
695 end loop;
696 exception when others then
697 raise_application_error(-20001, SQLERRM );
698 END;
699
700 PROCEDURE PopulateCustProfileClasses IS
701
702 cursor c_ar_profile_classes is
703 select name, status, customer_profile_class_id, dunning_letters,
704 sysdate creation_date, sysdate last_update_date,
705 -1 last_updated_by, -1 created_by, -1 last_update_login
706 from ar_customer_profile_classes acpc
707 where not exists ( select 'Already set up'
708 from igi_dun_cust_prof_class
709 where customer_profile_class_id = acpc.customer_profile_class_id
710 )
711 ;
712
713 BEGIN
714
715
716 for l_acp in c_ar_profile_classes loop
717 insert into igi_dun_cust_prof_class ( customer_profile_class_id
718 , creation_date
719 , created_by
720 , last_update_date
721 , last_updated_by
722 , last_update_login
723 , dunning_charge_type
724 , use_dunning_flag
725 )
726 values ( l_acp.customer_profile_class_id
727 , l_acp.creation_date
728 , l_acp.created_by
729 , l_acp.last_update_date
730 , l_acp.last_updated_by
731 , l_acp.last_update_login
732 , 'A'
733 , 'Y'
734 );
735 end loop;
736
737 END;
738
739 */
740 /*Commented due to dummy view in Dunning Letter Bug No 5905216 - End*/
741
742 Procedure PopulateRPIFlex
743 ( pp_header_txn_context in varchar2
744 , pp_header_txn_id1 in varchar2
745 , pp_header_txn_id2 in varchar2
746 , pp_line_txn_context in varchar2
747 , pp_line_txn_id1 in varchar2
748 , pp_line_txn_id2 in varchar2
749 , pp_line_txn_id3 in varchar2
750 , pp_line_txn_id4 in varchar2
751 , pp_language_code in varchar2
752 )
753 IS
754 lv_appl_short_name varchar2(10) := 'AR';
755 lv_header_segment1 varchar2(30) := 'INTERFACE_HEADER_ATTRIBUTE1';
756 lv_header_segment2 varchar2(30) := 'INTERFACE_HEADER_ATTRIBUTE2';
757 lv_line_txn_flex varchar2(50) := 'RA_INTERFACE_LINES';
758 lv_line_segment1 varchar2(30) := 'INTERFACE_LINE_ATTRIBUTE1';
759 lv_line_segment2 varchar2(30) := 'INTERFACE_LINE_ATTRIBUTE2';
760 lv_line_segment3 varchar2(30) := 'INTERFACE_LINE_ATTRIBUTE3';
761 lv_line_segment4 varchar2(30) := 'INTERFACE_LINE_ATTRIBUTE4';
762 lv_header_txn_flex varchar2(50) := 'RA_INTERFACE_HEADER';
763
764 begin
765 fnd_flex_dsc_api.set_session_mode ( 'seed_data' );
766
767
768 -- *************************** HEADER ********************************************
769 /*
770 -- Assumption : Descriptive flexfield with title 'Invoice Transaction Flexfield'
771 -- already exists!
772 --
773 -- 1. If context exists delete it!
774 -- 2. Add the context
775 -- 3.
776 --
777 */
778
779 if fnd_flex_dsc_api.context_exists ( p_appl_short_name => lv_appl_short_name
780 , p_flexfield_name => lv_header_txn_flex
781 , p_context_code => pp_header_txn_context
782 ) THEN
783 fnd_flex_dsc_api.delete_context( appl_short_name => lv_appl_short_name
784 , flexfield_name => lv_header_txn_flex
785 , context => pp_header_txn_context
786 );
787 end if;
788
789 fnd_flex_dsc_api.create_context( appl_short_name => lv_appl_short_name
790 , flexfield_name => lv_header_txn_flex
791 , context_code => pp_header_txn_context
792 , context_name => pp_header_txn_context
793 , description => pp_header_txn_context
794 , enabled => 'Y'
795 );
796 /*
797 -- First Segment
798 */
799 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
800 , flexfield_name => lv_header_txn_flex
801 , context_name => pp_header_txn_context
802 , name => pp_header_txn_id1
803 , column => lv_header_segment1
804 , description => pp_header_txn_id1
805 , sequence_number => 1
806 , enabled => 'Y'
807 , displayed => 'Y'
808 , value_set => ''
809 , default_type => ''
810 , default_value => ''
811 , required => 'Y'
812 , security_enabled => 'N'
813 , display_size => 30
814 , description_size => 50
815 , concatenated_description_size => 25
816 , list_of_values_prompt => 'N'
817 , window_prompt => pp_header_txn_id1
818 );
819
820 /*
821 -- Second Segment
822 */
823 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
824 , flexfield_name => lv_header_txn_flex
825 , context_name => pp_header_txn_context
826 , name => pp_header_txn_id2
827 , column => lv_header_segment2
828 , description => pp_header_txn_id2
829 , sequence_number => 2
830 , enabled => 'Y'
831 , displayed => 'Y'
832 , value_set => ''
833 , default_type => ''
834 , default_value => ''
835 , required => 'Y'
836 , security_enabled => 'N'
837 , display_size => 30
838 , description_size => 50
839 , concatenated_description_size => 25
840 , list_of_values_prompt => 'N'
841 , window_prompt => pp_header_txn_id2
842 );
843
844 fnd_flex_dsc_api.freeze ( appl_short_name => lv_appl_short_name
845 , flexfield_name => lv_header_txn_flex
846 );
847 -- *************************** LINE ********************************************
848 if fnd_flex_dsc_api.context_exists ( p_appl_short_name => lv_appl_short_name
849 , p_flexfield_name => lv_line_txn_flex
850 , p_context_code => pp_line_txn_context
851 ) THEN
852 fnd_flex_dsc_api.delete_context( appl_short_name => lv_appl_short_name
853 , flexfield_name => lv_line_txn_flex
854 , context => pp_line_txn_context
855 );
856 end if;
857
858 fnd_flex_dsc_api.create_context( appl_short_name => lv_appl_short_name
859 , flexfield_name => lv_line_txn_flex
860 , context_code => pp_line_txn_context
861 , context_name => pp_line_txn_context
862 , description => pp_line_txn_context
863 , enabled => 'Y'
864 );
865
866 /*
867 -- First Segment
868 */
869 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
870 , flexfield_name => lv_line_txn_flex
871 , context_name => pp_line_txn_context
872 , name => pp_line_txn_id1
873 , column => lv_line_segment1
874 , description => pp_line_txn_id1
875 , sequence_number => 1
876 , enabled => 'Y'
877 , displayed => 'Y'
878 , value_set => ''
879 , default_type => ''
880 , default_value => ''
881 , required => 'Y'
882 , security_enabled => 'N'
883 , display_size => 30
884 , description_size => 50
885 , concatenated_description_size => 25
886 , list_of_values_prompt => 'N'
887 , window_prompt => pp_line_txn_id1
888 );
889
890 /*
891 -- Second Segment
892 */
893 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
894 , flexfield_name => lv_line_txn_flex
895 , context_name => pp_line_txn_context
896 , name => pp_line_txn_id2
897 , column => lv_line_segment2
898 , description => pp_line_txn_id2
899 , sequence_number => 2
900 , enabled => 'Y'
901 , displayed => 'Y'
902 , value_set => ''
903 , default_type => ''
904 , default_value => ''
905 , required => 'Y'
906 , security_enabled => 'N'
907 , display_size => 30
908 , description_size => 50
909 , concatenated_description_size => 25
910 , list_of_values_prompt => 'N'
911 , window_prompt => pp_line_txn_id2
912 );
913
914 /*
915 -- Third Segment
916 */
917 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
918 , flexfield_name => lv_line_txn_flex
919 , context_name => pp_line_txn_context
920 , name => pp_line_txn_id3
921 , column => lv_line_segment3
922 , description => pp_line_txn_id3
923 , sequence_number => 3
924 , enabled => 'Y'
925 , displayed => 'Y'
926 , value_set => ''
927 , default_type => ''
928 , default_value => ''
929 , required => 'Y'
930 , security_enabled => 'N'
931 , display_size => 30
932 , description_size => 50
933 , concatenated_description_size => 25
934 , list_of_values_prompt => 'N'
935 , window_prompt => pp_line_txn_id3
936 );
937 /*
938 -- Fourth Segment
939 */
940
941 fnd_flex_dsc_api.create_segment ( appl_short_name => lv_appl_short_name
942 , flexfield_name => lv_line_txn_flex
943 , context_name => pp_line_txn_context
944 , name => pp_line_txn_id4
945 , column => lv_line_segment4
946 , description => pp_line_txn_id4
947 , sequence_number => 4
948 , enabled => 'Y'
949 , displayed => 'Y'
950 , value_set => ''
951 , default_type => ''
952 , default_value => ''
953 , required => 'Y'
954 , security_enabled => 'N'
955 , display_size => 30
956 , description_size => 50
957 , concatenated_description_size => 25
958 , list_of_values_prompt => 'N'
959 , window_prompt => pp_line_txn_id4
960 );
961
962
963 fnd_flex_dsc_api.freeze ( appl_short_name => lv_appl_short_name
964 , flexfield_name => lv_line_txn_flex
965 );
966
967
968 commit;
969
970 exception when others then
971 declare
972 lv_message varchar2(300);
973 begin
974 lv_message := FND_FLEX_DSC_API.message();
975 raise_application_error(-20000, to_char(sqlcode)||' '||lv_message );
976 end;
977 END; -- Procedure
978
979
980 PROCEDURE PopulateRPIFlexforCurrSOb IS
981 CURSOR c_rpi IS
982 SELECT rpi_header_context_code
983 , rpi_header_charge_id
984 , rpi_header_generate_seq
985 , rpi_line_context_code
986 , rpi_line_charge_id
987 , rpi_line_generate_seq
988 , rpi_line_charge_line_num
989 , rpi_line_price_break_num
990 , USERENV('LANG') language
991 from igi_ar_system_options;
992 BEGIN
993 FOR l_rpi in C_rpi LOOP
994 PopulateRPIFlex
995 ( pp_header_txn_context => l_rpi.rpi_header_context_code
996 , pp_header_txn_id1 => l_rpi.rpi_header_charge_id
997 , pp_header_txn_id2 => l_rpi.rpi_header_generate_seq
998 , pp_line_txn_context => l_rpi.rpi_line_context_code
999 , pp_line_txn_id1 => l_rpi.rpi_line_charge_id
1000 , pp_line_txn_id2 => l_rpi.rpi_line_generate_seq
1001 , pp_line_txn_id3 => l_rpi.rpi_line_charge_line_num
1002 , pp_line_txn_id4 => l_rpi.rpi_line_price_break_num
1003 , pp_language_code => l_rpi.language
1004 ) ;
1005 END LOOP;
1006 END;
1007
1008 /*
1009 -- Called By a SRS Routine to Initially Populate the data from AR tables to
1010 -- the 'extended' IGI AR Tables
1011 */
1012
1013 PROCEDURE PopulateExtendedData (
1014 errbuf out NOCOPY varchar2
1015 ,retcode out NOCOPY number
1016 ,pp_source in varchar2
1017 ,pp_commit in boolean
1018 ) IS
1019 PROCEDURE do_commit IS
1020 BEGIN
1021 if pp_commit then
1022 commit work;
1023 else
1024 null;
1025 end if;
1026 END;
1027
1028 PROCEDURE do_rollback IS
1029 BEGIN
1030 if pp_commit then
1031 rollback work;
1032 else
1033 null;
1034 end if;
1035 ENd;
1036 BEGIN
1037 /*
1038 -- The pp_source refers to the LOOKUP_CODE in IGI_LOOKUPS
1039 -- Where lookup_type = 'IGI_AR_POPULATE_SOURCE'
1040 */
1041 IF pp_source = 'COMBINED_BASIS_ACCTG' AND
1042 igi_gen.is_req_installed('ARC') THEN
1043 WriteToLog ( 'Copying Combined Basis Set Up information', true);
1044 PopulateSystemOptions;
1045 ELSIF pp_source = 'SYSTEM_OPTIONS' THEN
1046 WriteToLog ( 'Copying System Options information', true);
1047 PopulateSystemOptions;
1048
1049 /*Commented due to dummy view in Dunning Letter Bug No 5905216 - Start*/
1050 /*
1051
1052 ELSIF pp_source = 'DUNNING_PROFILES' AND
1053 igi_gen.is_req_installed('DUN')
1054 THEN
1055 WriteToLog ( '>> Synchronizing Customer Profile Classes information', true);
1056 PopulateCustProfileClasses;
1057 WriteToLog ( '>> Synchronizing Customer Profiles information', true);
1058 PopulatecustProfiles;
1059 WriteToLog ( '>> Synchronizing Customer letter set information', true);
1060 PopulateCustLetters;
1061 WriteToLog ( '>> Synchronization Complete.', true);
1062 ELSIF pp_source = 'DUNNING_LETTERS' AND
1063 igi_gen.is_req_installed('DUN')
1064 THEN
1065 WriteToLog ( '>> Synchronizing Dunning Letter Sets information', true);
1066 PopulateLetterSets;
1067 WriteToLog ( '>> Synchronizing Dunning Letter Set currency and lines information', true);
1068 PopulateLetterCurrencies;
1069 PopulateLetterSetLines;
1070 WriteToLog ( '>> Synchronizing Customer letter set information', true);
1071 PopulateCustLetters;
1072 PopulateExtendedData(errbuf, retcode, 'DUNNING_PROFILES');
1073 UpdateBlankCustLetters ;
1074 ELSIF pp_source = 'DUNNING_EXTENSIONS' AND
1075 igi_gen.is_req_installed('DUN')
1076 THEN
1077 WriteToLog ( 'Begin Synchronization of Data for Dunning Extensions.', true);
1078 WriteToLog ( ' *** MULTI ORG COMPLIANT *** ');
1079 WriteToLog ( '>> Synchronizing Customer Profile Classes information', true);
1080 PopulateCustProfileClasses;
1081 WriteToLog ( '>> Synchronizing Customer Profiles information', true);
1082 PopulatecustProfiles;
1083 WriteToLog ( '>> Synchronizing Dunning Letter Sets information', true);
1084 PopulateLetterSets;
1085 WriteToLog ( '>> Synchronizing Dunning Letter Set currency and lines information', true);
1086 PopulateLetterCurrencies;
1087 PopulateLetterSetLines;
1088 WriteToLog ( '>> Synchronizing Customer letter set information', true);
1089 PopulateCustLetters;
1090 WriteToLog ( 'End Synchronization of Data for Dunning Extensions.', true);
1091 */
1092 /*Commented due to dummy view in Dunning Letter Bug No 5905216 - End*/
1093
1094 ELSIF pp_source = 'RPI_FLEX_FOR_CURR_SOB' AND
1095 igi_gen.is_req_installed ('RPI')
1096 THEN
1097 WriteToLog ( 'Begin Creation of AutoInvoice Descriptive Flexfields', true);
1098 WriteToLog ( ' *** THIS SET UP IS INDEPENDENT OF MULTI-ORG *** ', true);
1099 PopulateRPIFlexforCurrSOb;
1100 WriteToLog ( 'Begin Creation of AutoInvoice Descriptive Flexfields', true);
1101 ELSE
1102 NULL;
1103 END IF;
1104
1105 do_COMMIT; -- Mandatory for a concurrent program
1106 retcode := 0;
1107 errbuf := '';
1108 EXCEPTION WHEN OTHERS THEN
1109 do_rollback;
1110 retcode := 2;
1111 errbuf := SQLERRM;
1112 END;
1113
1114
1115
1116 END;