eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CN_PURGE_TABLES_PUB

Source


1 PACKAGE BODY CN_PURGE_TABLES_PUB AS
2   /* $Header: CNPTPRGB.pls 120.0.12010000.4 2010/06/17 05:03:18 sseshaiy noship $*/
3 
4   G_PKG_NAME  CONSTANT VARCHAR2(30) := 'CN_PURGE_TABLES_PUB';
5   G_FILE_NAME CONSTANT VARCHAR2(12) := 'CNPTPRGB.pls';
6   g_cn_debug  VARCHAR2(1)           := fnd_profile.value('CN_DEBUG');
7   g_error_msg VARCHAR2(100)         := ' is a required field. Please enter proper value for it.';
8   g_script_name CONSTANT VARCHAR2(30)  := 'CNPTPRGBT1.0';
9   g_request_id           NUMBER        := fnd_global.conc_request_id;
10 
11 
12 
13 PROCEDURE debugmsg
14   (
15     msg VARCHAR2
16   )
17 IS
18 BEGIN
19   --g_cn_debug   := 'Y';
20   IF g_cn_debug = 'Y' THEN
21     cn_message_pkg.debug
22     (
23       SUBSTR(msg,1,254)
24     )
25     ;
26     fnd_file.put_line
27     (
28       fnd_file.Log, msg
29     )
30     ; -- Bug fix 5125980
31   END IF;
32   -- comment out dbms_output before checking in file
33   -- dbms_output.put_line(substr(msg,1,254));
34 END debugmsg;
35 
36 PROCEDURE parent_conc_wait(
37          l_child_program_id_tbl IN  OUT NOCOPY    sub_program_id_type
38        , retcode                OUT     NOCOPY    VARCHAR2
39        , errbuf                 OUT     NOCOPY    VARCHAR2
40 
41                     )
42 IS
43 
44     call_status                  BOOLEAN;
45 
46     l_req_id                     NUMBER;
47 
48     l_phase                      VARCHAR2(100);
49     l_status                     VARCHAR2(100);
50     l_dev_phase                  VARCHAR2(100);
51     l_dev_status                 VARCHAR2(100);
52     l_message                    VARCHAR2(2000);
53 
54     child_proc_fail_exception    EXCEPTION;
55 BEGIN
56      debugmsg('CN_PURGE_TABLES_PUB.Parent Process starts Waiting For Child
57      Processes to complete');
58 
59      FOR l_child_program_id IN l_child_program_id_tbl.FIRST..l_child_program_id_tbl.LAST
60      LOOP
61 
62             call_status :=
63             FND_CONCURRENT.get_request_status(
64             l_child_program_id_tbl(l_child_program_id), '', '',
65  			    l_phase, l_status, l_dev_phase,
66                             l_dev_status, l_message);
67 
68            debugmsg('CN_PURGE_TABLES_PUB. Request '||l_child_program_id_tbl(l_child_program_id)
69            ||' l_dev_phase '||l_dev_phase||' l_dev_status ');
70 
71            WHILE l_dev_phase <> 'COMPLETE'
72            LOOP
73 
74             call_status :=
75             FND_CONCURRENT.get_request_status(
76             l_child_program_id_tbl(l_child_program_id), '', '',
77  			    l_phase, l_status, l_dev_phase,
78                             l_dev_status, l_message);
79 
80            debugmsg('CN_PURGE_TABLES_PUB. Request '||l_child_program_id_tbl(l_child_program_id)
81            ||' l_dev_phase '||l_dev_phase||' l_dev_status. Parent Process going to sleep for 10 seconds. ');
82 
83                dbms_lock.sleep(10);
84 
85            END LOOP;
86 
87 
88             IF l_dev_status = 'ERROR'
89             THEN
90                retcode := 2;
91                errbuf := l_message;
92                raise child_proc_fail_exception;
93             END IF;
94 
95      END LOOP;
96 EXCEPTION
97 WHEN child_proc_fail_exception
98 THEN
99 retcode := 2;
100 debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables.Child Proc Failed exception');
101 debugmsg('CN_PURGE_TABLES_PUB : SQLCODE : ' || SQLCODE);
102 debugmsg('CN_PURGE_TABLES_PUB : SQLERRM : ' || SQLERRM);
103 WHEN OTHERS THEN
104 debugmsg('CN_PURGE_TABLES_PUB : Unexpected exception in archive_purge_cn_tables');
105 debugmsg('CN_PURGE_TABLES_PUB : SQLCODE : ' || SQLCODE);
106 debugmsg('CN_PURGE_TABLES_PUB : SQLERRM : ' || SQLERRM);
107 retcode  := 2;
108 errbuf   := 'CN_PURGE_TABLES_PUB.archive_purge_cn_tables.exception.others';
109 
110 END parent_conc_wait;
111 
112 
113 -- API name  : archive_purge_cn_tables
114 -- Type : public.
115 -- Pre-reqs :
116 PROCEDURE archive_purge_cn_tables
117   (
118     errbuf OUT NOCOPY  VARCHAR2,
119     retcode OUT NOCOPY VARCHAR2,
120     p_run_mode          IN VARCHAR2,
121     p_start_period_name IN VARCHAR2,
122     p_end_period_name   IN VARCHAR2,
123     p_org_id            IN NUMBER,
124     p_table_space       IN VARCHAR2,
125     p_no_of_workers     IN NUMBER,
126     p_worker_id         IN NUMBER,
127     p_batch_size        IN NUMBER
128   )
129 IS
130 
131   CURSOR get_start_period_id
132   IS
133      SELECT period_id
134        FROM cn_periods
135       WHERE period_name = p_start_period_name
136     AND org_id          = p_org_id AND closing_status in ('C', 'P') ;
137 
138   CURSOR get_end_period_id
139   IS
140      SELECT period_id
141        FROM cn_periods
142       WHERE period_name = p_end_period_name
143     AND org_id          = p_org_id AND closing_status in ('C', 'P') ;
144 
145   l_api_name        CONSTANT VARCHAR2(30) := 'purge_cn_tables';
146   l_api_version     CONSTANT NUMBER       :=1.0;
147   l_init_msg_list   VARCHAR2(10)          := FND_API.G_FALSE;
148   l_start_period_id NUMBER := -1;
149   l_end_period_id   NUMBER := -1;
150   l_error_msg       VARCHAR(240);
151   l_time            VARCHAR2(20);
152   l_table_space     VARCHAR2(30);
153   x_msg_count       NUMBER;
154   x_msg_data        VARCHAR2(2000);
155   x_return_status   VARCHAR2(1);
156   l_req_id                     NUMBER;
157   l_child_program_id_tbl       sub_program_id_type;
158   child_proc_fail_exception   EXCEPTION;
159 
160 BEGIN
161   retcode         := '0';
162   errbuf          := 'S';
163   x_msg_count     := 0;
164   x_msg_data      := ':';
165   x_return_status := 'S';
166   l_error_msg     := '';
167 
168    SELECT TO_CHAR(sysdate,'dd-mm-rr:hh:mi:ss') INTO l_time FROM dual;
169 
170   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: START l_time    ' || l_time );
171   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_org_id: ' || p_org_id);
172   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_start_period_name: ' || p_start_period_name);
173   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_end_period_name: ' || p_end_period_name);
174   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: p_run_mode: ' || p_run_mode);
175   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: p_no_of_workers: ' || p_no_of_workers);
176   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: p_worker_id: ' || p_worker_id);
177   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: p_batch_size: ' || p_batch_size);
178   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: p_table_space: ' || p_table_space);
179   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: g_cn_debug: ' || g_cn_debug);
180 
181   -- Standard call to check for call compatibility.
182   IF NOT FND_API.Compatible_API_Call ( l_api_version , l_api_version , l_api_name , G_PKG_NAME ) THEN
183     debugmsg('CN_PURGE_TABLES_PUB.purge_cn_tables api: Not Compatible_API_Call ');
184     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185   END IF;
186   -- Initialize message list if p_init_msg_list is set to TRUE.
187   IF FND_API.to_Boolean( l_init_msg_list ) THEN
188     FND_MSG_PUB.initialize;
189   END IF;
190 
191   --  Initialize API return status to success
192   x_return_status := FND_API.G_RET_STS_SUCCESS;
193 
194   IF(p_org_id is null) THEN
195     l_error_msg       := 'p_org_id' || g_error_msg;
196     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
197     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
198     fnd_message.set_token('FIELD','p_org_id');
199     fnd_msg_pub.add;
200     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
201   END IF;
202 
203   mo_global.set_policy_context('S',p_org_id);
204 
205   IF(p_start_period_name is null) THEN
206     l_error_msg       := 'p_start_period_name' || g_error_msg;
207     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
208     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
209     fnd_message.set_token('FIELD','p_start_period_name');
210     fnd_msg_pub.add;
211     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
212   END IF;
213 
214   IF(p_end_period_name is null) THEN
215     l_error_msg       := 'p_end_period_name' || g_error_msg;
216     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
217     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
218     fnd_message.set_token('FIELD','p_end_period_name');
219     fnd_msg_pub.add;
220     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
221   END IF;
222 
223   OPEN get_start_period_id;                         -- open the cursor
224   FETCH get_start_period_id INTO l_start_period_id; -- fetch data into local variables
225   CLOSE get_start_period_id;
226 
227   OPEN get_end_period_id;                       -- open the cursor
228   FETCH get_end_period_id INTO l_end_period_id; -- fetch data into local variables
229   CLOSE get_end_period_id;
230 
231   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_start_period_id: ' || l_start_period_id);
232   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_end_period_id: ' || l_end_period_id);
233 
234     --Mandatory fields validation starts here
235   IF(l_start_period_id = -1) THEN
236     l_error_msg       := 'Start Period Id calculation error: Please enter proper values for p_start_period_name and p_org_d';
237     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
238     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
239     fnd_message.set_token('FIELD','p_start_period_name');
240     fnd_msg_pub.add;
241     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
242   END IF;
243 
244   IF(l_end_period_id = -1) THEN
245     l_error_msg       := 'End Period Id calculation error: Please enter proper values for p_end_period_name and p_org_d';
246     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
247     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
248     fnd_message.set_token('FIELD','p_end_period_name');
249     fnd_msg_pub.add;
250     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
251   END IF;
252 
253   IF(p_run_mode <> 'A' and p_run_mode <> 'P') THEN
254     l_error_msg       := 'p_run_mode' || g_error_msg;
255     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
256     fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
257     fnd_message.set_token('FIELD','p_run_mode');
258     fnd_msg_pub.add;
259     RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
260   END IF;
261 
262   IF(p_run_mode = 'P') THEN
263     IF(p_no_of_workers is null or p_no_of_workers < 1) THEN
264       l_error_msg       := 'p_no_of_workers' || g_error_msg;
265       debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
266       fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
267       fnd_message.set_token('FIELD','p_no_of_workers (Expected value between 1 and 10)');
268       fnd_msg_pub.add;
269       RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
270     END IF;
271 
272     IF(p_worker_id is null  or p_worker_id <= 0) THEN
273       l_error_msg       := 'p_worker_id' || g_error_msg;
274       debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
275       fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
276       fnd_message.set_token('FIELD','p_worker_id (Expected value > 0)');
277       fnd_msg_pub.add;
278       RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
279     END IF;
280 
281     IF(p_batch_size is null  or p_batch_size <= 0) THEN
282       l_error_msg       := 'p_batch_size' || g_error_msg;
283       debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: l_error_msg: ' || l_error_msg);
284       fnd_message.set_name('CN', 'CN_AP_REQ_FIELD_NOT_SET_ERROR');
285       fnd_message.set_token('FIELD','p_batch_size (Expected value > 0)');
286       fnd_msg_pub.add;
287       RAISE CN_PURGE_REQ_FIELD_NOT_SET_ER;
288     END IF;
289   END IF;
290 
291   IF(p_run_mode = 'P' and p_no_of_workers > 1) THEN
292 
293 
294 
295     CN_PURGE_TABLES_PVT.audit_purge_cn_tables ( p_run_mode => p_run_mode,
296                     p_start_period_id => l_start_period_id,
297                     p_end_period_id => l_end_period_id,
298                     p_org_id => p_org_id,
299                     p_worker_id => p_worker_id,
300                     p_no_of_workers => p_no_of_workers,
301                     p_batch_size => p_batch_size,
302                     x_msg_count => x_msg_count,
303                     x_msg_data => x_msg_data,
304                     x_return_status => x_return_status );
305 
306     l_child_program_id_tbl := sub_program_id_type();
307 
308     FOR idx in 1 ..  p_no_of_workers LOOP
309 
310 
311        debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: Submit Worker number : ' || idx);
312 
313        l_req_id := FND_REQUEST.SUBMIT_REQUEST('CN', -- Application
314                                        'CN_PURGE_PARALLEL'	  , -- Concurrent Program
315                                        '', -- description
316                                        '', -- start time
317                                        FALSE -- sub request flag
318                                       ,p_run_mode
319                                       ,l_start_period_id
320                                       ,l_end_period_id
321                                       ,p_no_of_workers
322                                       ,p_org_id
323                                       ,'NONE'
324                                       ,idx
325                                       ,p_batch_size
326                                       ,g_request_id
327                                         );
328       commit;
329 
330     debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: Submit Worker number : ' || idx || ' l_req_id : '
331     || l_req_id);
332 
333 
334        IF  l_req_id = 0 THEN
335           retcode := 2;
336           errbuf := fnd_message.get;
337           raise child_proc_fail_exception;
338        ELSE
339           -- storing the request ids in an array
340           l_child_program_id_tbl.EXTEND;
341           l_child_program_id_tbl(l_child_program_id_tbl.LAST):=l_req_id;
342        END IF;
343      END LOOP;
344 
345      debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:Parent Process starts Waiting For Purge
346      Child Processes to complete');
347 
348      parent_conc_wait(l_child_program_id_tbl,retcode,errbuf);
349 
350      COMMIT;
351 
352  /*CN_PURGE_TABLES_PVT.archive_purge_cn_tables
353   (
354     errbuf             => errbuf,
355     retcode            => retcode,
356     p_run_mode          => p_run_mode,
357     p_start_period_id => l_start_period_id,
358     p_end_period_id   => l_end_period_id,
359     p_no_of_workers     => p_no_of_workers,
360     p_org_id            => p_org_id,
361     p_table_space       => p_table_space,
362     p_worker_id         => p_worker_id,
363     p_batch_size        => p_batch_size,
364     p_request_id        => g_request_id,
365     x_msg_count         => x_msg_count,
366     x_msg_data          => x_msg_data,
367     x_return_status     => x_return_status) ;*/
368 
369 
370 
371  ELSE
372      CN_PURGE_TABLES_PVT.archive_purge_cn_tables
373       (
374         errbuf             => errbuf,
375         retcode            => retcode,
376         p_run_mode          => p_run_mode,
377         p_start_period_id => l_start_period_id,
378         p_end_period_id   => l_end_period_id,
379         p_no_of_workers     => p_no_of_workers,
380         p_org_id            => p_org_id,
381         p_table_space       => p_table_space,
382         p_worker_id         => p_worker_id,
383         p_batch_size        => p_batch_size,
384         p_request_id        => g_request_id) ;
385  END IF;
386   SELECT TO_CHAR(sysdate,'dd-mm-rr:hh:mi:ss') INTO l_time FROM dual;
387 
388   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_msg_count: ' || x_msg_count);
389   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_msg_data: ' || x_msg_data);
390   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: x_return_status: ' || x_return_status);
391   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables: END l_time    ' || l_time );
392 
393   IF(x_return_status <> 'S') THEN
394     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395   END IF;
396 
397 
398 EXCEPTION
399 WHEN CN_PURGE_REQ_FIELD_NOT_SET_ER THEN
400   x_return_status := 'F';
401   retcode         := '-1';
402   --errbuf          := l_error_msg;
403   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:exception: CN_AIA_REQ_FIELD_NOT_SET_ERROR: ');
404   FND_MSG_PUB.Count_And_Get
405     (
406       p_encoded => FND_API.G_FALSE,
407       p_count => x_msg_count,
408       p_data  => x_msg_data
409      );
410      debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:exception: x_msg_count: ' || x_msg_count);
411      debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:exception: x_msg_data: ' || x_msg_data);
412   errbuf          := x_msg_data;
413   --raise_application_error (-20001,l_error_msg);
414 WHEN OTHERS THEN
415   ROLLBACK;
416   retcode         := '-2';
417   x_return_status := 'F';
418   errbuf          := x_msg_data || ' :  ' || SQLERRM(SQLCODE());
419   debugmsg('CN_PURGE_TABLES_PUB.archive_purge_cn_tables:exception others: ' ||  errbuf);
420   --RAISE	FND_API.G_EXC_ERROR;
421   raise_application_error (-20002,errbuf);
422 END archive_purge_cn_tables;
423 
424 END CN_PURGE_TABLES_PUB;