1 PACKAGE BODY XLA_PURGE_PKG AS
2 /* $Header: xlapurge.pkb 120.12 2011/04/13 06:38:47 nmsubram noship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA |
6 | ALL rights reserved. |
7 +===========================================================================+
8 | |
9 | FILENAME |
10 | |
11 | xlapurge.pkb |
12 | |
13 | |
14 | DESCRIPTION |
15 | THE routine purges temporary GL interface tables created in closed |
16 | GL Periods. |
17 | |
18 | MODIFICATION HISTORY |
19 | |
20 | 22-AUG-2009 RAJOSE Created. |
21 | 22-Nov-2010 Narayanan M.S. Bug 10096077 |
22 | Modified the logic to improve performance|
23 | 24-FEB-2010 Narayanan M.S. Bug 10382869 |
24 | Modified the logic to consider end_date |
25 | for purging temporary tables using |
26 | primary ledger latest closed period |
27 | 13-APR-2011 Narayanan M.S. Bug 12349350 |
28 | Remove hardcoded schema references |
29 +===========================================================================*/
30 --=============================================================================
31 -- Function to get the GroupID from a given XLA_GLT_XXXX table
32 --=============================================================================
33 FUNCTION GetGroupID(p_gltname IN VARCHAR2)
34 RETURN NUMBER IS
35 l_length NUMBER;
36 l_number VARCHAR2(2000) := '';
37 BEGIN
38 l_length := length(p_gltname);
39
40 FOR i IN 1..l_length
41 LOOP
42 IF substr(p_gltname,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
43 l_number := l_number || substr(p_gltname,i,1);
44 END IF;
45 END LOOP;
46
47 IF l_number IS NULL THEN
48 RETURN 0;
49 ELSE
50 RETURN TO_NUMBER(RTRIM(LTRIM(l_number)));
51 END IF;
52
53 END GetGroupID;
54
55
56 --=============================================================================
57 -- ******* Print Log File **********
58 --=============================================================================
59
60 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
61 BEGIN
62
63 fnd_file.put_line(fnd_file.log,p_msg);
64
65 EXCEPTION
66 WHEN xla_exceptions_pkg.application_exception THEN
67 RAISE;
68 WHEN OTHERS THEN
69 xla_exceptions_pkg.raise_message
70 (p_location => 'xla_accounting_pkg.print_logfile');
71 END print_logfile;
72
73 --=============================================================================
74 -- Procedure to drop temporary GLT tables
75 --=============================================================================
76 PROCEDURE drop_glt
77 ( p_errbuf OUT NOCOPY VARCHAR2
78 ,p_retcode OUT NOCOPY NUMBER
79 ,p_application_id IN NUMBER
80 ,p_dummy_parameter IN VARCHAR2
81 ,p_ledger_id IN NUMBER
82 ,p_end_date IN VARCHAR2 )
83 IS
84 l_status VARCHAR2(5);
85 l_industry VARCHAR2(5);
86 l_table_owner VARCHAR2(30);
87
88
89 l_rec_count NUMBER := 0;
90 l_category VARCHAR2(30);
91 l_ledger_name gl_ledgers.name%TYPE;
92 l_end_date DATE;
93 --Added for 10096077 start
94 l_max_date DATE;
95 l_ledgers VARCHAR2(1000) := '';
96 l_stat VARCHAR2(1000) := '';
97 l_tab_count NUMBER := 0;
98 --Added for 10096077 end
99 e_no_table_owner Exception;
100
101 CURSOR getledgers IS
102 SELECT ledger_id
103 ,NAME
104 ,ledger_category_code
105 , relationship_enabled_flag
106 FROM xla_ledger_relationships_v xlr
107 WHERE xlr.primary_ledger_id = p_ledger_id
108 --AND xlr.relationship_enabled_flag = 'Y'
109 ORDER BY DECODE(xlr.ledger_category_code,
110 'PRIMARY',1,
111 'ALC',2
112 ,3);
113
114
115
116 --Modified cursor for bug 10096077
117 CURSOR c_drop_glt(p_table_owner VARCHAR2, p_end_date date) is
118 SELECT dbj.object_name
119 FROM
120 dba_objects dbj
121 where dbj.object_name like 'XLA_GLT_%'
122 and dbj.object_type = 'TABLE'
123 and dbj.created <= p_end_date
124 and dbj.owner = p_table_owner
125 ;
126
127 --Added new cursor for bug 10096077
128 cursor get_date(p_ledger_id IN NUMBER, p_end_date date)
129 is
130 select max(end_date) end_date
131 from
132 gl_period_statuses
133 where application_id = 101
134 and ledger_id=p_ledger_id
135 and end_date <= p_end_date
136 and closing_status in ('C','P')
137 ;
138
139 BEGIN
140
141 l_end_date := fnd_date.canonical_to_date(p_end_date);
142
143
144 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' - Starting To Purge The XLA_GLT tables ');
145 p_retcode := 0;
146
147 BEGIN
148
149 IF NOT fnd_installation.get_app_info (application_short_name => 'SQLGL',
150 status => l_status ,
151 industry => l_industry,
152 oracle_schema => l_table_owner) THEN
153
154 --Added for 12349350
155 RAISE e_no_table_owner;
156 END IF;
157
158 EXCEPTION
159 --Added for 12349350
160 WHEN e_no_table_owner THEN
161 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' - Unable to derive table owner ');
162 RAISE;
163 WHEN OTHERS THEN
164 RAISE;
165
166 END;
167
168 BEGIN
169
170 SELECT ledger_category_code, NAME
171 INTO l_category, l_ledger_name
172 FROM gl_ledgers
173 WHERE ledger_id = p_ledger_id;
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 RAISE;
178 p_retcode := 2;
179 p_errbuf := 'The Ledger_id provided is not a valid Primary Ledger_id. Please give a valid primary ledger_id';
180 print_logfile('The Ledger_id provided is not a valid Primary Ledger_id. Please give a valid primary ledger_id');
181
182 WHEN OTHERS THEN
183 RAISE;
184 p_errbuf := substr(SQLERRM,1,100);
185 p_retcode := 2;
186 print_logfile(sqlerrm);
187
188 END;
189
190 IF l_category = 'PRIMARY' then
191 --Added for bug 10096077 start
192 FOR ledger_rec in getledgers
193 LOOP
194 l_ledgers := l_ledgers || ledger_rec.ledger_id || ',';
195 --Changed for bug 10382869
196 If ledger_rec.relationship_enabled_flag = 'Y' AND ledger_rec.ledger_category_code = 'PRIMARY' then
197 FOR end_date_rec in get_date(ledger_rec.ledger_id, l_end_date)
198 LOOP
199 IF l_max_date is null THEN
200 l_max_date := end_date_rec.end_date;
201 ELSIF (l_max_date >= end_date_rec.end_date) THEN
202 l_max_date := end_date_rec.end_date;
203 END IF;
204 END LOOP;
205 end if;
206 END LOOP;
207
208 l_ledgers := SUBSTR(l_ledgers,1,length(l_ledgers) - 1);
209
210 IF l_max_date is not null THEN
211 print_logfile('End_date selected for purging the tables is ' || l_max_date);
212 FOR glt_rec in c_drop_glt(l_table_owner, l_max_date)
213 LOOP
214 BEGIN
215 l_rec_count := l_rec_count + 1;
216 IF glt_rec.object_name is NOT NULL THEN
217 --l_tab_count := 0;
218 l_stat := 'select count(*) from
219 (select /*+ first_rows(1) */ ledger_id
220 from '|| glt_rec.object_name ||
221 ' where rownum = 1
222 ) glt
223 where glt.ledger_id not in (' || l_ledgers || ')';
224 EXECUTE IMMEDIATE l_stat INTO l_tab_count;
225 IF l_tab_count = 0 THEN
226 GL_JOURNAL_IMPORT_PKG.drop_table(glt_rec.object_name);
227 print_logfile('The following GLT table is purged ' || l_table_owner||'.'||glt_rec.object_name);
228 END IF;
229 END IF;
230 EXCEPTION
231 WHEN OTHERS THEN
232 print_logfile('The following GLT table could not be purged ' || l_table_owner||'.'||glt_rec.object_name);
233 print_logfile('Reason for not purging the above table is: ' || SQLCODE || SQLERRM);
234 p_retcode := 1;
235 END;
236 END LOOP;
237 END IF;
238
239
240 IF p_retcode = 0 AND l_rec_count > 0 THEN
241 print_logfile('All the GLT tables have been purged successfully for end date ' || to_char(l_end_date,'DD-MON-YYYY') || ' for ledger: ' || l_ledger_name );
242 p_errbuf := 'Purge GLT Program completed Normal';
243 ELSIF p_retcode = 0 AND l_rec_count = 0 THEN
244 print_logfile('No GLT tables purged for end date ' || to_char(l_end_date,'DD-MON-YYYY') || ' for ledger: ' || l_ledger_name );
245 p_errbuf := 'Purge GLT Program completed Normal';
246 ELSIF p_retcode = 1 THEN
247 print_logfile('Purge GLT Program completed with some GLT tables not being purged for end date ' || to_char(l_end_date,'DD-MON-YYYY') || ' for ledger: ' || l_ledger_name );
248 p_errbuf := 'Purge GLT Program completed with some GLT tables not being purged ';
249 END IF;
250
251 ELSE
252 p_retcode := 2;
253 p_errbuf := 'The Ledger selected is not a Primary Ledger_id. Please run the concurrent program with a Primary Ledger';
254 print_logfile('The Ledger selected is not a Primary Ledger_id. Please run the concurrent program with a Primary Ledger' );
255
256 END IF;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 p_errbuf := substr(SQLERRM,1,100);
261 p_retcode := 2;
262 print_logfile(sqlerrm);
263
264 END drop_glt;
265
266
267 END XLA_PURGE_PKG;
[Home] [Help]