[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_R12_UPGRADE_GRP
Source
1 PACKAGE BODY ICX_CAT_R12_UPGRADE_GRP AS
2 /* $Header: ICXG12UB.pls 120.2 2006/08/24 00:36:46 sudsubra noship $*/
3
4 -- Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30):='ICX_CAT_R12_UPGRADE_GRP';
6
7 PROCEDURE updatePOHeaderId
8 ( p_api_version IN NUMBER ,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
11 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
12 x_return_status OUT NOCOPY VARCHAR2 ,
13 p_interface_header_id IN DBMS_SQL.NUMBER_TABLE
14 )
15 IS
16 l_api_name CONSTANT VARCHAR2(30) := 'updatePOHeaderId';
17 l_api_version CONSTANT NUMBER := 1.0;
18 l_err_loc PLS_INTEGER;
19
20 BEGIN
21 l_err_loc := 100;
22 x_return_status := FND_API.G_RET_STS_SUCCESS;
23
24 l_err_loc := 200;
25 -- Standard Start of API savepoint
26 SAVEPOINT updatePOHeaderId_sp;
27
28 l_err_loc := 300;
29 -- Standard call to check for call compatibility.
30 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
31 p_api_version ,
32 l_api_name ,
33 G_PKG_NAME )
34 THEN
35 l_err_loc := 400;
36 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
37 END IF;
38
39 l_err_loc := 500;
40 ICX_CAT_UTIL_PVT.setCommitParameter(p_commit);
41
42 l_err_loc := 600;
43 ICX_CAT_R12_UPGRADE_PVT.updatePOHeaderId(p_interface_header_id);
44
45 -- Standard check of p_commit
46 IF FND_API.To_Boolean( p_commit ) THEN
47 l_err_loc := 700;
48 COMMIT;
49 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
50 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
51 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
52 '; Commit done.');
53 END IF;
54 ELSE
55 l_err_loc := 800;
56 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
57 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
58 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
59 '; Commit not done.' );
60 END IF;
61 END IF;
62 EXCEPTION
63 WHEN OTHERS THEN
64 BEGIN
65 ROLLBACK TO updatePOHeaderId_sp;
66 EXCEPTION
67 WHEN OTHERS THEN
68 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
69 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
70 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
71 'ROLLBACK TO the savepoint caused the exception -->'
72 || SQLERRM);
73 END IF;
74 NULL;
75 END;
76 raise;
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 END updatePOHeaderId;
79
80 PROCEDURE updatePOLineId
81 ( p_api_version IN NUMBER ,
82 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
83 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
84 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
85 x_return_status OUT NOCOPY VARCHAR2 ,
86 p_interface_line_id IN DBMS_SQL.NUMBER_TABLE
87 )
88 IS
89 l_api_name CONSTANT VARCHAR2(30) := 'updatePOLineId';
90 l_api_version CONSTANT NUMBER := 1.0;
91 l_err_loc PLS_INTEGER;
92
93 BEGIN
94 l_err_loc := 100;
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 l_err_loc := 200;
98 -- Standard Start of API savepoint
99 SAVEPOINT updatePOLineId_sp;
100
101 l_err_loc := 300;
102 -- Standard call to check for call compatibility.
103 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
104 p_api_version ,
105 l_api_name ,
106 G_PKG_NAME )
107 THEN
108 l_err_loc := 400;
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 l_err_loc := 500;
113 ICX_CAT_UTIL_PVT.setCommitParameter(p_commit);
114
115 l_err_loc := 600;
116 ICX_CAT_R12_UPGRADE_PVT.updatePOLineId(p_interface_line_id);
117
118 l_err_loc := 700;
119 -- Standard check of p_commit
120 IF FND_API.To_Boolean( p_commit ) THEN
121 l_err_loc := 800;
122 COMMIT;
123 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
125 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
126 '; Commit done.');
127 END IF;
128 ELSE
129 l_err_loc := 900;
130 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
132 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
133 '; Commit not done.');
134 END IF;
135 END IF;
136 EXCEPTION
137 WHEN OTHERS THEN
138 BEGIN
139 ROLLBACK TO updatePOLineId_sp;
140 EXCEPTION
141 WHEN OTHERS THEN
142 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
144 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
145 'ROLLBACK TO the savepoint caused the exception -->'
146 || SQLERRM);
147 END IF;
148 NULL;
149 END;
150 raise;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 END updatePOLineId;
153
154 -- Start of comments
155 -- API name : createR12UpgradeJob
156 -- * Type : Group
157 -- * Pre-reqs : None.
158 -- * Function : inserts a job in icx_cat_r12_upgrade_jobs
159 -- * Parameters :
160 -- IN : p_api_version IN NUMBER Required
161 -- p_commit IN VARCHAR2 Optional
162 -- Default = FND_API.G_TRUE
163 -- p_init_msg_list IN VARCHAR2 Optional
164 -- Default = FND_API.G_FALSE
165 -- p_validation_level IN NUMBER Optional
166 -- Default = FND_API.G_VALID_LEVEL_FULL
167 -- p_job_type IN VARCHAR2 Required
168 -- Type of job to be created
169 -- p_audsid IN NUMBER Required
170 -- audsid for the job to be created
171 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
172 -- * Version : Current version 1.0
173 -- Previous version 1.0
174 -- Initial version 1.0
175 --
176 -- Notes : None.
177 --
178 -- End of comments
179 PROCEDURE createR12UpgradeJob
180 ( p_api_version IN NUMBER ,
181 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
183 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
184 x_return_status OUT NOCOPY VARCHAR2 ,
185 p_job_type IN VARCHAR2,
186 p_audsid IN NUMBER
187 )
188 IS
189
190 l_api_name CONSTANT VARCHAR2(30) := 'createR12UpgradeJob';
191 l_api_version CONSTANT NUMBER := 1.0;
192 l_err_loc PLS_INTEGER;
193 l_upgrade_job_number PLS_INTEGER;
194
195
196 BEGIN
197 l_err_loc := 100;
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 l_err_loc := 200;
201 -- Standard Start of API savepoint
202 SAVEPOINT createR12UpgradeJob_sp;
203
204 l_err_loc := 300;
205 -- Standard call to check for call compatibility.
206 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
207 p_api_version ,
208 l_api_name ,
209 G_PKG_NAME )
210 THEN
211 l_err_loc := 400;
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215 l_err_loc := 500;
216 ICX_CAT_UTIL_PVT.setCommitParameter(p_commit);
217
218 l_err_loc := 600;
219
220 l_upgrade_job_number := ICX_CAT_UTIL_PVT.getR12UpgradeJobNumber;
221
222 l_err_loc := 620;
223 -- gUserId is used in created_by which should be -12 to identify the rows created by r12 upgrade
224 ICX_CAT_UTIL_PVT.g_who_columns_rec.user_id := ICX_CAT_UTIL_PVT.g_upgrade_user;
225 ICX_CAT_UTIL_PVT.g_who_columns_rec.internal_request_id := ICX_CAT_UTIL_PVT.getNextSequenceForWhoColumns;
226 ICX_CAT_UTIL_PVT.g_who_columns_rec.login_id := l_upgrade_job_number;
227 ICX_CAT_UTIL_PVT.g_who_columns_rec.request_id := l_upgrade_job_number;
228 ICX_CAT_UTIL_PVT.g_who_columns_rec.program_application_id := l_upgrade_job_number;
229 ICX_CAT_UTIL_PVT.g_who_columns_rec.program_id := l_upgrade_job_number;
230 ICX_CAT_UTIL_PVT.g_who_columns_rec.program_login_id := l_upgrade_job_number;
231
232 l_err_loc := 630;
233 ICX_CAT_UTIL_PVT.g_job_type := p_job_type;
234 ICX_CAT_UTIL_PVT.g_job_number := l_upgrade_job_number;
235
236 l_err_loc := 640;
237 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
239 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
240 ' g_job_type:' || ICX_CAT_UTIL_PVT.g_job_type ||
241 ' g_job_number:' || ICX_CAT_UTIL_PVT.g_job_number);
242 END IF;
243
244 l_err_loc := 650;
245 ICX_CAT_R12_UPGRADE_PVT.createR12UpgradeJob(p_audsid);
246
247 l_err_loc := 700;
248 -- Standard check of p_commit
249 IF FND_API.To_Boolean( p_commit ) THEN
250 l_err_loc := 800;
251 COMMIT;
252 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
253 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
254 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
255 '; Commit done.');
256 END IF;
257 ELSE
258 l_err_loc := 900;
259 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
261 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
262 '; Commit not done.');
263 END IF;
264 END IF;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 BEGIN
269 ROLLBACK TO createR12UpgradeJob_sp;
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
273 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
274 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
275 'ROLLBACK TO the savepoint caused the exception -->'
276 || SQLERRM);
277 END IF;
278 NULL;
282 END createR12UpgradeJob;
279 END;
280 raise;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283
284 -- Start of comments
285 -- API name : updateR12UpgradeJob
286 -- * Type : Group
287 -- * Pre-reqs : None.
288 -- * Function : updates a job in icx_cat_r12_upgrade_jobs
289 -- * Parameters :
290 -- IN : p_api_version IN NUMBER Required
291 -- p_commit IN VARCHAR2 Optional
292 -- Default = FND_API.G_TRUE
293 -- p_init_msg_list IN VARCHAR2 Optional
294 -- Default = FND_API.G_FALSE
295 -- p_validation_level IN NUMBER Optional
296 -- Default = FND_API.G_VALID_LEVEL_FULL
297 -- p_job_status IN VARCHAR2 Required
298 -- New status of the job
299 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
300 -- * Version : Current version 1.0
301 -- Previous version 1.0
302 -- Initial version 1.0
303 --
304 -- Notes : None.
305 --
306 -- End of comments
307 PROCEDURE updateR12UpgradeJob
308 ( p_api_version IN NUMBER ,
309 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
313 p_job_status IN VARCHAR2
310 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
311 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL ,
312 x_return_status OUT NOCOPY VARCHAR2 ,
314 )
315 IS
316
317 l_api_name CONSTANT VARCHAR2(30) := 'updateR12UpgradeJob';
318 l_api_version CONSTANT NUMBER := 1.0;
319 l_err_loc PLS_INTEGER;
320
321 BEGIN
322 l_err_loc := 100;
323 x_return_status := FND_API.G_RET_STS_SUCCESS;
324
325 l_err_loc := 200;
326 -- Standard Start of API savepoint
327 SAVEPOINT updateR12UpgradeJob_sp;
328
329 l_err_loc := 300;
330 -- Standard call to check for call compatibility.
331 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
332 p_api_version ,
333 l_api_name ,
334 G_PKG_NAME )
335 THEN
336 l_err_loc := 400;
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 END IF;
339
340 l_err_loc := 500;
341 ICX_CAT_UTIL_PVT.setCommitParameter(p_commit);
342
343 l_err_loc := 600;
344
345 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
346 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
347 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
348 ' g_job_type:' || ICX_CAT_UTIL_PVT.g_job_type ||
349 ' g_job_number:' || ICX_CAT_UTIL_PVT.g_job_number ||
350 ' p_job_status:' || p_job_status);
351 END IF;
352
353 IF (p_job_status = g_job_complete_status) THEN
354 ICX_CAT_UTIL_PVT.g_job_complete_date := SYSDATE;
355 ELSE
356 ICX_CAT_UTIL_PVT.g_job_complete_date := NULL;
357 END IF;
358
359 l_err_loc := 2400;
360 ICX_CAT_R12_UPGRADE_PVT.updateR12UpgradeJob(p_job_status);
361
362 l_err_loc := 700;
363 -- Standard check of p_commit
364 IF FND_API.To_Boolean( p_commit ) THEN
365 l_err_loc := 800;
366 COMMIT;
367 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
368 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
369 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
370 '; Commit done.');
371 END IF;
372 ELSE
373 l_err_loc := 900;
374 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
375 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
376 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
377 '; Commit not done.');
378 END IF;
379 END IF;
380
381 EXCEPTION
382 WHEN OTHERS THEN
383 BEGIN
384 ROLLBACK TO updateR12UpgradeJob_sp;
385 EXCEPTION
386 WHEN OTHERS THEN
387 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
389 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
393 NULL;
390 'ROLLBACK TO the savepoint caused the exception -->'
391 || SQLERRM);
392 END IF;
394 END;
395 raise;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 END updateR12UpgradeJob;
398
399
400 END ICX_CAT_R12_UPGRADE_GRP;