[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_PURGE_CP
Source
1 PACKAGE BODY INV_MGD_PURGE_CP AS
2 /* $Header: INVCPURB.pls 120.1 2005/06/21 06:20:16 appldev ship $ */
3 -- +======================================================================+
4 -- | Copyright (c) 2001 Oracle Corporation |
5 -- | Redwood Shores, CA, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 -- | FILENAME |
9 -- | INVCPURB.pls |
10 -- | |
11 -- | DESCRIPTION |
12 -- | |
13 -- | |
14 -- | PROCEDURE LIST |
15 -- | Purge |
16 -- | |
17 -- | HISTORY |
18 -- | 08/28/00 vjavli Created |
19 -- | 12/11/00 vjavli signature updated to p_org_hier_origin_id |
20 -- | |
21 -- | 08/08/2001 vjavli bug#1919163 fix request limit issue |
22 -- | 08/14/2001 vjavli bug#1936118 status should change to |
23 -- | COMPLETE only after all the purge |
24 -- | requested completed |
25 -- | 11/14/2001 vjavli Updated with Get_Organization_List |
26 -- | Performance enhancement |
27 -- | 11/26/2001 vjavli p_include_origin flag set to 'Y' |
28 -- | 11/21/2002 vma Performance: modify code to print to log |
29 -- | only if debug profile option is enabled |
30 -- | 11/24/2002 tsimmond UTF8: changed l_org_name to VARCHAR2(240)|
31 -- | 01/07/2004 nkilleda Changed program to invoke INVTMLPR conc. |
32 -- | pgm instead of the PRO*C INCTPG. The |
33 -- | PRO*C program is obsoleted from 11.5.9 |
34 -- | a)Commented cursor for getting org name |
35 -- | as org name is passed only as desc and |
36 -- | is not a mandatory parameter. |
37 -- | b)Corrected the Sleep logic. DBMS_SLEEP |
38 -- | was being called after checking each |
39 -- | request's status, however it should be |
40 -- | called after checking status for all |
41 -- | requests. |
42 -- +======================================================================+
43
44 --===================
45 -- GLOBALS
46 --===================
47
48 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_PURGE_CP';
49 g_log_level NUMBER := NULL;
50 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF,SQL,SRS
51 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
52
53 --==================
54 -- PRIVATE PROCEDURES AND FUNCTIONS
55 --==================
56
57 --========================================================================
58 -- FUNCTION : Has_Worker_Completed PRIVATE
59 -- PARAMETERS: p_request_id IN NUMBER
60 -- RETURNS : BOOLEAN
61 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
62 -- has completed; FALSE otherwise
63 --=========================================================================
64 FUNCTION Has_Worker_Completed
65 ( p_request_id IN NUMBER
66 )
67 RETURN BOOLEAN
68 IS
69 l_count NUMBER;
70 l_result BOOLEAN;
71 BEGIN
72
73 SELECT COUNT(*)
74 INTO l_count
75 FROM fnd_concurrent_requests
76 WHERE request_id = p_request_id
77 AND phase_code = 'C';
78
79 IF l_count = 1 THEN
80 l_result := TRUE;
81 ELSE
82 l_result := FALSE;
83 END IF;
84
85
86 RETURN l_result;
87
88 END Has_Worker_Completed;
89
90 --=======================
91 -- GLOBAL PROCEDURES
92 --=======================
93
94 --========================================================================
95 -- PROCEDURE : Purge PUBLIC
96 -- PARAMETERS: x_retcode return status
97 -- x_errbuff return error message
98 -- p_org_hier_origin_id IN NUMBER Organization Hierarchy
99 -- Origin Id
100 -- p_org_hierarchy_id IN Organization Hierarchy Id
101 -- p_purge_date IN Purge Date
102 -- p_purge_name IN Purge Name
103 -- p_request_limit IN Number of request limit
104 --
105 --
106 -- COMMENT : This is a wrapper procedure invokes core transaction purge
107 -- program repetitively for each organization in the organization
108 -- hierarchy origin list. The procedure purges the transactions
109 -- across organizations
110 -- Updated: Get_Organization_list included
111 --=========================================================================
112 PROCEDURE Purge(x_retcode OUT NOCOPY VARCHAR2,
113 x_errbuff OUT NOCOPY VARCHAR2,
114 p_org_hier_origin_id IN NUMBER,
115 p_org_hierarchy_id IN NUMBER,
116 p_purge_date IN VARCHAR2,
117 p_purge_name IN VARCHAR2,
118 p_request_limit IN NUMBER)
119 IS
120
121 l_org_code_list INV_ORGHIERARCHY_PVT.OrgID_tbl_type;
122
123 l_orgid hr_organization_units.organization_id%TYPE;
124 l_org_name VARCHAR2(240) := NULL;
125 l_purge_req_id NUMBER;
126 l_errorcode NUMBER;
127 l_errortext VARCHAR2(200);
128 l_return_status BOOLEAN;
129 l_req_status BOOLEAN;
130 l_req_id NUMBER;
131 l_req_num NUMBER;
132 l_req_ind BINARY_INTEGER;
133 l_status_ind BINARY_INTEGER;
134 l_index BINARY_INTEGER;
135 l_sleep_time NUMBER := 5;
136 l_all_req_status BOOLEAN;
137
138 -- cursor to print the organization name
139 -- commented as it org_name is not a required parameter
140 -- while fixing bug 3326234
141 --
142 --CURSOR c_org_name(c_org_id NUMBER) IS
143 --SELECT ORGANIZATION_NAME
144 --FROM ORG_ORGANIZATION_DEFINITIONS
145 --WHERE ORGANIZATION_ID = c_org_id;
146
147 TYPE l_reqstatus_table IS TABLE OF NUMBER
148 INDEX BY BINARY_INTEGER;
149 l_reqstatus_tbl_type l_reqstatus_table;
150
151 submission_error_except EXCEPTION;
152 set_options_except EXCEPTION;
153
154
155 BEGIN
156
157 -- initialize log
158 INV_ORGHIERARCHY_PVT.Log_Initialize;
159
160 -- initialize the message stack
161 FND_MSG_PUB.Initialize;
162
163 INV_ORGHIERARCHY_PVT.Get_Organization_List(p_org_hierarchy_id,
164 p_org_hier_origin_id,
165 l_org_code_list,
166 'Y');
167
168 -- initialize request serial numbers
169 l_req_num := 1;
170 l_req_ind := 1;
171
172 l_index := l_org_code_list.LAST;
173
174 WHILE (l_index >= l_org_code_list.FIRST ) LOOP
175
176 l_orgid := l_org_code_list(l_index);
177
178 IF G_DEBUG = 'Y' THEN
179 INV_ORGHIERARCHY_PVT.Log
180 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
181 ,'Organization Id: '|| to_char(l_orgid));
182 END IF;
183
184 -- check whether the request being submitted lies within the request limit
185 IF (l_req_num <= p_request_limit) THEN
186
187 -- Set request options so that request not to be viewed on the end-user
188 -- concurrent request form unless the request completes with a
189 -- warning or an error
190 l_return_status := FND_REQUEST.SET_OPTIONS(
191 implicit => 'WARNING',
192 protected => 'NO');
193
194 IF (l_return_status ) THEN
195
196 -- print organization name
197 -- OPEN c_org_name(l_orgid);
198 -- FETCH c_org_name
199 -- INTO l_org_name;
200 -- IF (c_org_name%NOTFOUND AND G_DEBUG = 'Y') THEN
201 -- INV_ORGHIERARCHY_PVT.Log
202 -- (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
203 -- ,'Inventory Organization Name not found for'
204 -- || to_char(l_orgid)
205 -- );
206 -- END IF;
207 -- CLOSE c_org_name;
208
209 l_purge_req_id := FND_REQUEST.SUBMIT_REQUEST(
210 application =>'INV',
211 program =>'INVTMLPR',
212 argument1 => p_purge_date,
213 argument2 => l_orgid,
214 argument3 => p_purge_name);
215
216 IF (l_purge_req_id = 0) THEN
217 -- Handle submission error --
218 RAISE submission_error_except;
219 ELSE
220 IF G_DEBUG = 'Y' THEN
221 INV_ORGHIERARCHY_PVT.Log
222 (INV_ORGHIERARCHY_PVT.G_LOG_EVENT
223 ,'Request Id:' || to_char(l_purge_req_id) ||
224 ' Organization Id:'|| to_char(l_orgid) ||
225 ' Name:' || l_org_name ||
226 ' ' || 'Transaction Purge Submitted'
227 );
228 END IF;
229 commit;
230 END IF;
231 ELSE
232 -- handle set options error
233 RAISE set_options_except;
234 END IF;
235
236 l_reqstatus_tbl_type(l_req_ind) := l_purge_req_id;
237 l_req_ind := l_req_ind + 1;
238 l_req_num := l_req_num + 1;
239 l_index := l_index - 1; -- reduce the index to obtain previous organization id
240
241 ELSE
242 -- Wait until completion of any one of the submitted request.
243 -- If one of the request completes, exit to submit new request
244 -- for the next organization
245
246 -- initialize check status number
247 l_status_ind := 1;
248 LOOP
249 IF (l_status_ind > p_request_limit) THEN
250 -- sleep for l_sleep_time if all req.
251 -- are running.
252 DBMS_LOCK.sleep(l_sleep_time);
253
254 -- reset the status number
255 l_status_ind := 1;
256 END IF;
257
258 --- Check the request status of the submitted requests
259 l_req_id := l_reqstatus_tbl_type(l_status_ind);
260 l_req_status := INV_MGD_PURGE_CP.Has_Worker_Completed(l_req_id);
261
262 IF (l_req_status ) THEN
263 -- Assign the request id index to the completed request status num
264 -- reduce the request number by 1 since one request completed
265
266 l_req_ind := l_status_ind;
267 l_req_num := l_req_num - 1;
268
269
270 IF G_DEBUG = 'Y' THEN
271 INV_ORGHIERARCHY_PVT.Log
272 (INV_ORGHIERARCHY_PVT.G_LOG_STATEMENT
273 ,'Request id:' || to_char(l_req_id) || ' Request Status: Completed'
274 );
275 END IF;
276
277 EXIT;
278 END IF;
279 l_status_ind := l_status_ind + 1;
280
281 END LOOP; -- Wait status loop
282
283 END IF; -- request within the limit
284
285
286 END LOOP; -- organization list loop
287
288 IF G_DEBUG = 'Y' THEN
289 INV_ORGHIERARCHY_PVT.Log
290 (INV_ORGHIERARCHY_PVT.G_LOG_EVENT
291 ,'All the purge requests submitted successfully'
292 );
293 END IF;
294
295 -- Return Success only after all the submitted purge requests
296 -- got completed
297 l_all_req_status := FALSE;
298 WHILE (NOT l_all_req_status) LOOP
299 FOR idx IN 1 .. l_reqstatus_tbl_type.COUNT LOOP
300 l_req_id := l_reqstatus_tbl_type(idx);
301 l_all_req_status := INV_MGD_PURGE_CP.Has_Worker_Completed(l_req_id);
302 IF (NOT l_all_req_status) THEN
303 EXIT;
304 END IF;
305
306 END LOOP; -- end for loop
307
308 DBMS_LOCK.sleep(l_sleep_time);
309 END LOOP; -- end while
310
311 IF G_DEBUG = 'Y' THEN
312 INV_ORGHIERARCHY_PVT.Log
313 ( INV_ORGHIERARCHY_PVT.G_LOG_PROCEDURE
314 , 'All the purge requests completed'
315 );
316 END IF;
317
318 -- SRS success
319 x_errbuff := NULL;
320 x_retcode := RETCODE_SUCCESS;
321
322 EXCEPTION
323 WHEN submission_error_except THEN
324 l_errorcode := SQLCODE;
325 l_errortext := SUBSTR(SQLERRM,1,200);
326
327 IF G_DEBUG = 'Y' THEN
328 INV_ORGHIERARCHY_PVT.Log
329 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
330 ,'submission error'
331 );
332 INV_ORGHIERARCHY_PVT.Log
333 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
334 ,to_char(l_errorcode) || l_errortext
335 );
336 END IF;
337 x_retcode := RETCODE_ERROR;
338 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
339
340 WHEN set_options_except THEN
341 l_errorcode := SQLCODE;
342 l_errortext := SUBSTR(SQLERRM,1,200);
343
344 IF G_DEBUG = 'Y' THEN
345 INV_ORGHIERARCHY_PVT.Log
346 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
347 ,'set options error'
348 );
349 INV_ORGHIERARCHY_PVT.Log
350 (INV_ORGHIERARCHY_PVT.G_LOG_EXCEPTION
351 ,to_char(l_errorcode) || l_errortext
352 );
353 END IF;
354 x_retcode := RETCODE_ERROR;
355 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
356
357 WHEN OTHERS THEN
358 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
359 THEN
360 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Purge');
361 END IF;
362 x_retcode := RETCODE_ERROR;
363 x_errbuff := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
364
365 END Purge;
366
367 END INV_MGD_PURGE_CP;