PACKAGE BODY: APPS.ARP_RECUR_PKG

Source

1 PACKAGE BODY ARP_RECUR_PKG AS
2 /* $Header: ARTIRECB.pls 120.4 2006/01/17 15:36:39 vcrisost ship $ */
3   pg_cursor1  integer := '';
4   pg_user_id          number;
5   pg_conc_login_id    number;
6   pg_login_id         number;
7   pg_prog_appl_id     number;
8   pg_conc_program_id  number;
9 
10 PROCEDURE insert_p(
11                     p_rec_rec         IN  ra_recur_interim%rowtype,
12                     p_batch_source_id IN  ra_batch_sources.batch_source_id%type,
13                     p_trx_number      OUT NOCOPY ra_recur_interim.trx_number%type
14                   ) IS
15     l_trx_num_cursor   integer;
16     l_dummy            integer;
17      l_org_id           integer;
18     l_org_str          varchar2(30);
19     l_trx_number       ra_recur_interim.trx_number%type;
20     l_trx_str          VARCHAR2(1000);
21 BEGIN
22     arp_util.debug('arp_process_recur.insert_p()+');
23     p_trx_number := '';
24      IF (p_rec_rec.trx_number is null)
25      THEN
26           SELECT MIN(org_id)
27           INTO   l_org_id
28           FROM   ar_system_parameters;
29 
30           IF (l_org_id IS NOT NULL) THEN
31               l_org_str := '_'||to_char(l_org_id);
32           ELSE
33               l_org_str := NULL;
34           END IF;
35 
36 -- Bug 1185665 : change dbms_sql to use native dynamic sql
37        l_trx_str :=  'select ra_trx_number_' ||
38                                REPLACE(p_batch_source_id, '-', 'N') ||
39                           l_org_str||
40                           '_s.nextval trx_number ' ||
41                           'from ra_batch_sources ' ||
42                           'where batch_source_id = ' ||
43                                  p_batch_source_id ||
44                          ' and auto_trx_numbering_flag = ''Y''';
45 
46             EXECUTE IMMEDIATE l_trx_str
47                 INTO l_trx_number;
48 
49 /*
50           l_trx_num_cursor := dbms_sql.open_cursor;
51 
52           dbms_sql.parse(l_trx_num_cursor,
53                           'select ra_trx_number_' ||
54                                REPLACE(p_batch_source_id, '-', 'N') ||
55                           l_org_str||
56                           '_s.nextval trx_number ' ||
57                           'from ra_batch_sources ' ||
58                           'where batch_source_id = ' ||
59                                  p_batch_source_id ||
60                          ' and auto_trx_numbering_flag = ''Y''',
61                          dbms_sql.v7);
62 
63           dbms_sql.define_column(l_trx_num_cursor, 1, l_trx_number, 20);
64 
65           l_dummy := dbms_sql.execute_and_fetch(l_trx_num_cursor, TRUE);
66 
67           dbms_sql.column_value(l_trx_num_cursor, 1, l_trx_number);
68 
69           dbms_sql.close_cursor(l_trx_num_cursor);
70 */
71      ELSE
72           l_trx_number := p_rec_rec.trx_number;
73      END IF;
74      INSERT INTO ra_recur_interim
75                (
76                  customer_trx_id,
77                  trx_number,
78                  created_by,
79                  creation_date,
80                  last_updated_by,
81                  last_update_date,
82                  last_update_login,
83                  trx_date,
84                  billing_date,
85                  term_due_date,
86                  gl_date,
87                  term_discount_date,
88                  request_id,
89                  doc_sequence_value,
90                  new_customer_trx_id)
91   VALUES
92                (
93                  p_rec_rec.customer_trx_id,
94                  l_trx_number,
95                  pg_user_id,
96                  sysdate,
97                  pg_user_id,
98                  sysdate,
99                  nvl(pg_conc_login_id,
100                      pg_login_id),
101                  p_rec_rec.trx_date,
102                  p_rec_rec.billing_date,
103                  p_rec_rec.term_due_date,
104                  p_rec_rec.gl_date,
105                  p_rec_rec.term_discount_date ,
106                  p_rec_rec.request_id,
107                  p_rec_rec.doc_sequence_value,
108                  p_rec_rec.new_customer_trx_id);
109 
110    p_trx_number := l_trx_number;
111 
112    arp_util.debug('arp_process_recur.insert_p()-');
113 
114 EXCEPTION
115     WHEN OTHERS THEN
116         arp_util.debug('EXCEPTION:  arp_process_recur.insert_p()');
117         RAISE;
118 
119 END;
120 BEGIN
121 
122   pg_user_id          := fnd_global.user_id;
123   pg_conc_login_id    := fnd_global.conc_login_id;
124   pg_login_id         := fnd_global.login_id;
125   pg_prog_appl_id     := fnd_global.prog_appl_id;
126   pg_conc_program_id  := fnd_global.conc_program_id;
127 
128 
129 END ARP_RECUR_PKG  ;