1 PACKAGE BODY PO_R12_CAT_UPG_GRP AS
2 /* $Header: PO_R12_CAT_UPG_GRP.plb 120.2 2006/05/31 19:03:59 vkartik noship $ */
3
4 g_debug BOOLEAN := FALSE;
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_GRP';
7 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
8
9 --------------------------------------------------------------------------------
10 --Start of Comments
11 --Name: upgrade_existing_docs
12 --Pre-reqs:
13 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
14 --Modifies:
15 -- a) PO Transaction tables (headers, lines, attributes, TLP,
16 -- po_reqexpress_lines_all, po_approved_supplier_list)
17 -- b) FND_MSG_PUB on unhandled exceptions.
18 --Locks:
19 -- None.
20 --Function:
21 -- PASS 1: Migrate existing agreeements(and quotations)/Req templates.
22 -- This API should be called during the upgrade phase only.
23 --Parameters:
24 --IN:
25 --p_api_version
26 -- Apps API Std - To control correct version in use
27 --p_commit
28 -- Apps API Std - Should data be committed?
29 --p_init_msg_list
30 -- Apps API Std - Initialize the message list?
31 --p_validation_level
32 -- Apps API Std - Level of validations to be done
33 --p_log_level
34 -- Specifies the level for which logging is enabled.
35 --p_batch_size
36 -- The maximum number of rows that should be processed at a time, to avoid
37 -- exceeding rollback segment. The transaction would be committed after
38 -- processing each batch.
39 --OUT:
40 --x_return_status
41 -- Apps API Std
42 -- FND_API.g_ret_sts_success - if the procedure completed successfully
43 -- FND_API.g_ret_sts_error - if an error occurred
44 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
45 --x_msg_count
46 -- Apps API Std
47 -- The number of error messages returned in the FND error stack in case
48 -- x_return_status returned FND_API.G_RET_STS_ERROR or
49 -- FND_API.G_RET_STS_UNEXP_ERROR
50 --x_msg_data
51 -- Apps API Std
52 -- Contains error msg in case x_return_status returned FND_API.G_RET_STS_ERROR
53 -- or FND_API.G_RET_STS_UNEXP_ERROR
54 --
55 --End of Comments
56 --------------------------------------------------------------------------------
57 PROCEDURE upgrade_existing_docs
58 (
59 p_api_version IN NUMBER
60 , p_commit IN VARCHAR2 default FND_API.G_FALSE
61 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
62 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
63 , p_log_level IN NUMBER default 1
64 , p_batch_size IN NUMBER default 2500
65 , x_return_status OUT NOCOPY VARCHAR2
66 , x_msg_count OUT NOCOPY NUMBER
67 , x_msg_data OUT NOCOPY VARCHAR2
68 )
69 IS
70 l_api_name CONSTANT VARCHAR2(30) := 'upgrade_existing_docs';
71 l_api_version CONSTANT NUMBER := 1.0;
72 l_module CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
73 l_progress VARCHAR2(3) := '000';
74 l_return_status VARCHAR2(1);
75 BEGIN
76 l_progress := '010';
77
78 -- Set logging options
79 PO_R12_CAT_UPG_DEBUG.set_logging_options(p_log_level => p_log_level);
80 g_debug := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
81
82 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'START'); END IF;
83
84 -- Standard call to check for call compatibility
85 IF NOT FND_API.compatible_API_call(
86 p_current_version_number => l_api_version,
87 p_caller_version_number => p_api_version,
88 p_api_name => l_api_name,
89 p_pkg_name => g_pkg_name)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 l_progress := '020';
95 -- Initialize message list if p_init_msg_list is set to TRUE.
96 IF FND_API.to_boolean(p_init_msg_list) THEN
97 FND_MSG_PUB.initialize;
98 END IF;
99
100 l_progress := '030';
101 -- Initialize API return status to success
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 x_msg_count := 0;
104 x_msg_data := NULL;
105
106 l_progress := '040';
107 -- Call the main procedure for upgrading existing documents
108 PO_R12_CAT_UPG_EXISTING_DOCS.upgrade_existing_docs(
109 p_batch_size => p_batch_size
110 , x_return_status => x_return_status);
111
112 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
113 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Migration failed.'); END IF;
114 END IF; -- IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
115
116 l_progress := '050';
117 -- Standard check of p_commit.
118 IF FND_API.to_boolean(p_commit) THEN
119 COMMIT;
120 END IF;
121
122 l_progress := '110';
123 -- Standard call to get message count and if count is 1, get message info.
124 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
125 p_data => x_msg_data );
126
127 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'END'); END IF;
128 EXCEPTION
129 WHEN FND_API.G_EXC_ERROR THEN
130 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'EXPECTED Start'); END IF;
131 x_return_status := FND_API.G_RET_STS_ERROR ;
132 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
133 p_data => x_msg_data );
134 IF g_debug THEN
135 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status);
136 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'EXPECTED End');
137 END IF;
138
139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
140 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'UNEXPECTED Start'); END IF;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
142 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
143 p_data => x_msg_data );
144 IF g_debug THEN
145 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status);
146 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'UNEXPECTED End');
147 END IF;
148
149 WHEN OTHERS THEN
150 BEGIN
151 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS Start'); END IF;
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153
154 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status); END IF;
155
156 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
157 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
158 END IF;
159
160 -- Standard call to get message count and if count is 1, get message info.
161 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
162 p_data => x_msg_data );
163
164 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS End'); END IF;
165 EXCEPTION
166 WHEN OTHERS THEN
167 NULL; -- If exception occurs inside the outer exception handling block, ignore it.
168 END;
169 END upgrade_existing_docs;
170
171 --------------------------------------------------------------------------------
172 --Start of Comments
173 --Name: migrate_catalog
174 --Pre-reqs:
175 -- The iP catalog data is populated in PO Interface tables.
176 --Modifies:
177 -- a) PO Interface Tables (inserts new po_header_id for successful rows, back
178 -- to the Interface tables.
179 -- b) PO_INTERFACE_ERRORS table: Inserts error messages for those rows that
180 -- failed the migration.
181 -- c) FND_MSG_PUB on unhandled exceptions.
182 --Locks:
183 -- None.
184 --Function:
185 -- Migrate the iP Catalog Data to PO. This API should be called during the
186 -- upgrade phase only.
187 --Parameters:
188 --IN:
189 --p_api_version
190 -- Apps API Std - To control correct version in use
191 --p_commit
192 -- Apps API Std - Should data be committed?
193 --p_init_msg_list
194 -- Apps API Std - Initialize the message list?
195 --p_validation_level
196 -- Apps API Std - Level of validations to be done
197 --p_log_level
198 -- Specifies the level for which logging is enabled.
199 --p_batch_id
200 -- Batch ID to identify the data in interface tables that needs to be migrated.
201 --p_batch_size
202 -- The maximum number of rows that should be processed at a time, to avoid
203 -- exceeding rollback segment. The transaction would be committed after
204 -- processing each batch.
205 --OUT:
206 --x_return_status
207 -- Apps API Std
208 -- FND_API.g_ret_sts_success - if the procedure completed successfully
209 -- FND_API.g_ret_sts_error - if an error occurred
210 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
211 --x_msg_count
212 -- Apps API Std
213 -- The number of error messages returned in the FND error stack in case
214 -- x_return_status returned FND_API.G_RET_STS_ERROR or
215 -- FND_API.G_RET_STS_UNEXP_ERROR
216 --x_msg_data
217 -- Apps API Std
218 -- Contains error msg in case x_return_status returned FND_API.G_RET_STS_ERROR
219 -- or FND_API.G_RET_STS_UNEXP_ERROR
220 --
221 --End of Comments
222 --------------------------------------------------------------------------------
223 PROCEDURE migrate_catalog
224 (
225 p_api_version IN NUMBER
226 , p_commit IN VARCHAR2 default FND_API.G_FALSE
227 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
228 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
229 , p_log_level IN NUMBER default 1
230 , p_batch_id IN NUMBER
231 , p_batch_size IN NUMBER default 2500
232 , p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
233 , x_return_status OUT NOCOPY VARCHAR2
234 , x_msg_count OUT NOCOPY NUMBER
235 , x_msg_data OUT NOCOPY VARCHAR2
236 )
237 IS
238 l_api_name CONSTANT VARCHAR2(30) := 'migrate_catalog';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_module CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
241 l_progress VARCHAR2(3) := '000';
242 l_return_status VARCHAR2(1);
243 l_org_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
244 BEGIN
245 l_progress := '010';
246
247 -- Set logging options
248 PO_R12_CAT_UPG_DEBUG.set_logging_options(p_log_level => p_log_level);
249 g_debug := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
250
251 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'START'); END IF;
252 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_api_version='||p_api_version); END IF;
253 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_commit='||p_commit); END IF;
254 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_init_msg_list='||p_init_msg_list); END IF;
255 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_validation_level='||p_validation_level); END IF;
256 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_log_level='||p_log_level); END IF;
257 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_batch_id='||p_batch_id); END IF;
258 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_batch_size='||p_batch_size); END IF;
259 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_validate_only_mode='||p_validate_only_mode); END IF;
260
261 -- Standard call to check for call compatibility
262 IF NOT FND_API.compatible_API_call(
263 p_current_version_number => l_api_version,
264 p_caller_version_number => p_api_version,
265 p_api_name => l_api_name,
266 p_pkg_name => g_pkg_name)
267 THEN
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 END IF;
270
271 l_progress := '020';
272 -- Initialize message list if p_init_msg_list is set to TRUE.
273 IF FND_API.to_boolean(p_init_msg_list) THEN
274 FND_MSG_PUB.initialize;
275 END IF;
276
277 l_progress := '030';
278 -- Initialize API return status to success
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280 x_msg_count := 0;
281 x_msg_data := NULL;
282
283 PO_R12_CAT_UPG_PVT.get_distinct_orgs(
284 p_batch_id => p_batch_id
285 , p_batch_size => p_batch_size
286 , p_validate_only_mode => p_validate_only_mode
287 , x_org_id_list => l_org_id_list);
288
289 l_progress := '040';
290 -- To handle data from multiple orgs, we start this loop, once for each org
291 FOR i IN 1 .. l_org_id_list.COUNT
292 LOOP
293 l_progress := '050';
294 -- Set the org_id in g_job structure. It will be used while stamping the
295 -- processing_id for rows that have this org_id. Only those lines that are
296 -- stamped with a processing_id are picked up for migration.
297 PO_R12_CAT_UPG_PVT.g_job.org_id := l_org_id_list(i);
298
299 -- Set the org contetxt. Some of the defaulting/validation code uses
303 /*PO_UC9+10*/
300 -- org-striped views. Moreover, some of the other product API's may
301 -- be using the org context, such as Tax/MRC/FV(JFMIP vendor validation)
302 -- /GL Rate API's, etc.
304 --FND_CLIENT_INFO.set_org_context(PO_R12_CAT_UPG_PVT.g_job.org_id);
305 /*/PO_UC9+10*/
306 -- For Release 12, use the following API.
307 /*PO_UC12*/
308 mo_global.set_policy_context('S', PO_R12_CAT_UPG_PVT.g_job.org_id); -- Bug#5259328
309 /*/PO_UC12*/
310
311 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Migration for org='||PO_R12_CAT_UPG_PVT.g_job.org_id); END IF;
312
313 l_progress := '060';
314 -- Migrate GBPA, BPA, Quotation (Headers and Lines) from interface tables
315 PO_R12_CAT_UPG_PVT.migrate_documents
316 ( p_batch_id => p_batch_id
317 , p_batch_size => p_batch_size
318 , p_commit => p_commit
319 , p_validate_only_mode => p_validate_only_mode
320 , x_return_status => l_return_status
321 , x_msg_count => x_msg_count
322 , x_msg_data => x_msg_data);
323
324 -- If return status is not success, no further migration should be performed
325 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
326 x_return_status := l_return_status;
327 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Document migration failed.'); END IF;
328 END IF;
329
330 l_progress := '070';
331 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
332 -- Migrate Attribute data for GBPA, BPA, Quotation and ReqTemplates
333 PO_R12_CAT_UPG_PVT.migrate_attributes(p_validate_only_mode => p_validate_only_mode);
334 END IF; -- IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
335
336 l_progress := '080';
337 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
338 -- Migrate TLP data for GBPA, BPA, Quotation and ReqTemplates
339 PO_R12_CAT_UPG_PVT.migrate_attributes_tlp(p_validate_only_mode => p_validate_only_mode);
340 END IF; -- IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
341
342 l_progress := '090';
343 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
344 -- Migrate the data for ReqTemplates
345 PO_R12_CAT_UPG_PVT.update_req_templates
346 ( p_batch_size => p_batch_size
347 , p_validate_only_mode => p_validate_only_mode
348 , x_return_status => l_return_status);
349
350 -- If return status is not success, no further migration should be performed
351 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
352 x_return_status := l_return_status;
353 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Document migration failed.'); END IF;
354 END IF;
355 END IF; -- IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
356
357 l_progress := '100';
358
359 -- If all lines for a header have errors, then do not create the header.
360 -- Clean up the header from the txn table.
361 PO_R12_CAT_UPG_PVT.cleanup_err_docs;
362
363 END LOOP; -- end of loop to handle data from multiple orgs
364
365 l_progress := '110';
366
367 -- Standard check of p_commit.
368 IF FND_API.to_boolean(p_commit) THEN
369 COMMIT;
370 END IF;
371
372 l_progress := '120';
373
374 -- Standard call to get message count and if count is 1, get message info.
375 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
376 p_data => x_msg_data );
377
378 l_progress := '130';
379 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'END'); END IF;
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'EXPECTED Start'); END IF;
383 x_return_status := FND_API.G_RET_STS_ERROR ;
384 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
385 p_data => x_msg_data );
386 IF g_debug THEN
387 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status);
388 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'EXPECTED End');
389 END IF;
390
391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'UNEXPECTED Start'); END IF;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
394 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
395 p_data => x_msg_data );
396 IF g_debug THEN
397 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status);
398 PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'UNEXPECTED End');
399 END IF;
400
401 WHEN OTHERS THEN
402 BEGIN
403 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS Start'); END IF;
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405
406 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status); END IF;
407
408 --TODO: remove after manual UT
409 RAISE_APPLICATION_ERROR(-20000,l_module||','||l_progress || ','|| SQLERRM);
410
411 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
412 -- FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
413 --END IF;
414
415 -- Standard call to get message count and if count is 1, get message info.
416 --FND_MSG_PUB.count_and_get(p_count => x_msg_count,
417 -- p_data => x_msg_data );
418
419 --IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_msg_count='||x_msg_count); END IF;
420 --IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_msg_data='||x_msg_data); END IF;
421
422 --IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS End'); END IF;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 NULL; -- If exception occurs inside the outer exception handling block, ignore it.
427
428 --TODO: remove after manual UT
429 RAISE_APPLICATION_ERROR(-20000,l_module||','||l_progress || ','|| SQLERRM);
430 END;
431 END migrate_catalog;
432
433 END PO_R12_CAT_UPG_GRP;
[Home] [Help]