[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_BATCH_SUMMARY_V2PUB
Source
1 PACKAGE BODY HZ_IMP_BATCH_SUMMARY_V2PUB AS
2 /*$Header: ARH2BSSB.pls 120.2 2005/10/30 03:49:20 appldev noship $ */
3
4 ----------------------------------
5 -- public procedures and functions
6 ----------------------------------
7
8 /**
9 * PROCEDURE create_import_batch
10 *
11 * DESCRIPTION
12 * Creates an import batch
13 *
14 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
15 *
16 * ARGUMENTS
17 * IN:
18 * p_init_msg_list Initialize message stack if it is set to
19 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
20 * p_batch_name Import batch name.
21 * p_description Import batch description.
22 * p_original_system Original system from which data brought into this batch
23 * p_load_type Load type of the data, e.g. DNB, CSV
24 * p_est_no_of_records Estimated number of records in batch
25 * IN/OUT:
26 * OUT:
27 * x_batch_id Batch ID generated by system.
28 * x_return_status Return status after the call. The status can
29 * be FND_API.G_RET_STS_SUCCESS (success),
30 * FND_API.G_RET_STS_ERROR (error),
31 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
32 * x_msg_count Number of messages in message stack.
33 * x_msg_data Message text if x_msg_count is 1.
34 *
35 * NOTES
36 *
37 * MODIFICATION HISTORY
38 *
39 * 09-JUL-2003 Indrajit Sen o Created.
40 *
41 */
42
43 PROCEDURE create_import_batch (
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_batch_name IN VARCHAR2,
46 p_description IN VARCHAR2,
47 p_original_system IN VARCHAR2,
48 p_load_type IN VARCHAR2 := NULL,
49 p_est_no_of_records IN NUMBER := NULL,
50 x_batch_id OUT NOCOPY NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) IS
55
56 l_success VARCHAR2(1) := 'N';
57 os_exists_flag VARCHAR2(1) :='N'; /* Bug 4179332 */
58
59 BEGIN
60
61 -- standard start of API savepoint
62 SAVEPOINT create_import_batch;
63
64 -- Check if API is called in debug mode. If yes, enable debug.
65
66 -- Debug info.
67
68 -- initialize message list if p_init_msg_list is set to TRUE.
69 IF FND_API.to_Boolean(p_init_msg_list) THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- initialize API return status to success.
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 ----------------
77 -- do validation
78 ----------------
79
80 -- batch name is mandatory
81 hz_utility_v2pub.validate_mandatory (
82 p_create_update_flag => 'C',
83 p_column => 'batch_name',
84 p_column_value => p_batch_name,
85 p_restricted => 'Y',
86 x_return_status => x_return_status
87 );
88
89 -- original_system is mandatory
90 hz_utility_v2pub.validate_mandatory (
91 p_create_update_flag => 'C',
92 p_column => 'original_system',
93 p_column_value => p_original_system,
94 p_restricted => 'Y',
95 x_return_status => x_return_status
96 );
97
98 -- Bug 4179322. Modified code to validate ORIG_SYSTEM from the table
99 -- HZ_ORIG_SYSTEMS_B instead of the lookup.
100
101 -- validate original_system against lookup ORIG_SYSTEM
102 /*
103 hz_utility_v2pub.validate_lookup (
104 p_column => 'original_system',
105 p_lookup_type => 'ORIG_SYSTEM',
106 p_column_value => p_original_system,
107 x_return_status => x_return_status);
108 */
109
110 BEGIN
111 SELECT 'Y' INTO os_exists_flag
112 FROM hz_orig_systems_b
113 WHERE
114 orig_system= p_original_system
115 AND orig_system<>'SST'
116 AND status='A';
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_FK');
120 FND_MESSAGE.SET_TOKEN('FK','orig_system');
121 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system');
122 FND_MESSAGE.SET_TOKEN('TABLE','HZ_ORIG_SYSTEMS_B');
123 FND_MSG_PUB.ADD;
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 END;
126
127 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
128 RAISE FND_API.G_EXC_ERROR;
129 END IF;
130
131 --------------------------------------------
132 -- insert the record with generated batch id
133 --------------------------------------------
134 WHILE l_success = 'N' LOOP
135 BEGIN
136 INSERT INTO hz_imp_batch_summary (
137 batch_id,
138 batch_name,
139 description,
140 original_system,
141 load_type,
142 est_no_of_records,
143 created_by,
144 creation_date,
145 last_updated_by,
146 last_update_date,
147 last_update_login
148 )
149 VALUES (
150 hz_imp_batch_summary_s.nextval,
151 p_batch_name,
152 p_description,
153 p_original_system,
154 p_load_type,
155 p_est_no_of_records,
156 hz_utility_v2pub.created_by,
157 hz_utility_v2pub.creation_date,
158 hz_utility_v2pub.last_updated_by,
159 hz_utility_v2pub.last_update_date,
160 hz_utility_v2pub.last_update_login
161 ) RETURNING
162 batch_id
163 INTO
164 x_batch_id;
165
166 l_success := 'Y';
167
168 EXCEPTION
169 WHEN DUP_VAL_ON_INDEX THEN
170 IF INSTRB(SQLERRM, 'HZ_IMP_BATCH_SUMMARY_U1') <> 0 OR
171 INSTRB(SQLERRM, 'HZ_IMP_BATCH_SUMMARY_PK') <> 0 THEN
172 DECLARE
173 l_count NUMBER;
174 l_dummy VARCHAR2(1);
175 BEGIN
176 l_count := 1;
177 WHILE l_count > 0 LOOP
178 SELECT hz_imp_batch_summary_s.NEXTVAL
179 INTO x_batch_id
180 FROM dual;
181
182 BEGIN
183 SELECT 'Y'
184 INTO l_dummy
185 FROM hz_imp_batch_summary hl
186 WHERE hl.batch_id = x_batch_id;
187 l_count := 1;
188 EXCEPTION
189 WHEN NO_DATA_FOUND THEN
190 l_count := 0;
191 END;
192 END LOOP;
193 END;
194 ELSE
195 RAISE;
196 END IF;
197 END;
198 END LOOP;
199
200
201
202 -- standard call to get message count and if count is 1, get message info.
203 fnd_msg_pub.count_and_get(
204 p_encoded => fnd_api.g_false,
205 p_count => x_msg_count,
206 p_data => x_msg_data);
207
208 -- Debug info.
209
210 -- Check if API is called in debug mode. If yes, disable debug.
211
212 EXCEPTION
213 WHEN FND_API.G_EXC_ERROR THEN
214 ROLLBACK TO create_import_batch;
215
216 x_return_status := fnd_api.g_ret_sts_error;
217
218 fnd_msg_pub.count_and_get(
219 p_encoded => fnd_api.g_false,
220 p_count => x_msg_count,
221 p_data => x_msg_data);
222
223 -- Debug info.
224
225 -- Check if API is called in debug mode. If yes, disable debug.
226
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK TO create_import_batch;
229
230 x_return_status := fnd_api.g_ret_sts_unexp_error;
231
232 fnd_msg_pub.count_and_get(
233 p_encoded => fnd_api.g_false,
234 p_count => x_msg_count,
235 p_data => x_msg_data);
236
237 -- Debug info.
238
239 -- Check if API is called in debug mode. If yes, disable debug.
240
241 WHEN OTHERS THEN
242 ROLLBACK TO create_import_batch;
243
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245
246 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
247 fnd_message.set_token('ERROR',SQLERRM);
248 fnd_msg_pub.add;
249
250 fnd_msg_pub.count_and_get(
251 p_encoded => fnd_api.g_false,
252 p_count => x_msg_count,
253 p_data => x_msg_data);
254
255 -- Debug info.
256
257 -- Check if API is called in debug mode. If yes, disable debug.
258
259 END create_import_batch;
260
261
262 /**
263 * PROCEDURE activate_batch
264 *
265 * DESCRIPTION
266 * Activates an import batch for processing
267 *
268 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
269 *
270 * ARGUMENTS
271 * IN:
272 * p_init_msg_list Initialize message stack if it is set to
273 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
274 * p_batch_id ID of the batch you want to activate for import processing
275 * IN/OUT:
276 * OUT:
277 * x_return_status Return status after the call. The status can
278 * be FND_API.G_RET_STS_SUCCESS (success),
279 * FND_API.G_RET_STS_ERROR (error),
280 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
281 * x_msg_count Number of messages in message stack.
282 * x_msg_data Message text if x_msg_count is 1.
283 *
284 * NOTES
285 *
286 * MODIFICATION HISTORY
287 *
288 * 09-JUL-2003 Indrajit Sen o Created.
289 *
290 */
291
292 PROCEDURE activate_batch (
293 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
294 p_batch_id IN NUMBER,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2
298 )
299 IS
300 l_batch_id NUMBER;
301 l_original_system VARCHAR2(30);
302 l_pre_count_stat VARCHAR2(200) :=
303 'begin HZ_IMP_LOAD_BATCH_COUNTS_PKG.pre_import_counts
304 (p_batch_id => :1,
305 p_original_system => :2); end;';
306 BEGIN
307 NULL;
308 -- standard start of API savepoint
309 SAVEPOINT activate_batch;
310
311 -- Check if API is called in debug mode. If yes, enable debug.
312
313 -- Debug info.
314
315 -- initialize message list if p_init_msg_list is set to TRUE.
316 IF FND_API.to_Boolean(p_init_msg_list) THEN
317 FND_MSG_PUB.initialize;
318 END IF;
319
320 -- initialize API return status to success.
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 ----------------
324 -- do validation
325 ----------------
326
327 -- batch id is mandatory
328 hz_utility_v2pub.validate_mandatory (
329 p_create_update_flag => 'Y',
330 p_column => 'batch_id',
331 p_column_value => p_batch_id,
332 p_restricted => 'Y',
333 x_return_status => x_return_status
334 );
335
336 -- batch id must be a valid batch id in hz_imp_batch_summary table
337 BEGIN
338 SELECT batch_id, original_system
339 INTO l_batch_id, l_original_system
340 FROM hz_imp_batch_summary
341 WHERE batch_id = p_batch_id;
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
346 fnd_message.set_token('FK', 'p_batch_id');
347 fnd_message.set_token('COLUMN', 'batch_id');
348 fnd_message.set_token('TABLE', 'hz_imp_batch_summary');
349 fnd_msg_pub.add;
350 x_return_status := fnd_api.g_ret_sts_error;
351 END;
352
353 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 -- batch id must be a valid batch for processing
358 BEGIN
359 SELECT batch_id
360 INTO l_batch_id
361 FROM hz_imp_batch_summary
362 WHERE batch_id = p_batch_id
363 AND NVL(batch_status,'ACTIVE') NOT IN ('PURGED', 'REJECTED', 'PROCESSING', 'COMPLETED', 'ACTION_REQUIRED');
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 fnd_message.set_name('AR', 'HZ_INVALID_IMP_BATCH');
368 fnd_msg_pub.add;
369 x_return_status := fnd_api.g_ret_sts_error;
370 END;
371
372 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375
376 ---------------------------------
377 -- update the batch summary table
378 ---------------------------------
379
380 -- update the batch record
381 UPDATE hz_imp_batch_summary
382 SET batch_status = 'ACTIVE',
383 last_updated_by = hz_utility_v2pub.last_updated_by,
384 last_update_date = hz_utility_v2pub.last_update_date,
385 last_update_login = hz_utility_v2pub.last_update_login
386 WHERE batch_id = p_batch_id;
387
388 ------------------------------------
389 -- call the pre import count process
390 ------------------------------------
391
392 -- call the count of records calculation routine
393 -- use dynamic sql to avoid compilation error in 8i
394 execute immediate l_pre_count_stat using p_batch_id, l_original_system;
395
396 -- standard call to get message count and if count is 1, get message info.
400 p_data => x_msg_data);
397 fnd_msg_pub.count_and_get(
398 p_encoded => fnd_api.g_false,
399 p_count => x_msg_count,
401
402 EXCEPTION
403 WHEN FND_API.G_EXC_ERROR THEN
404 ROLLBACK TO activate_batch;
405
406 x_return_status := fnd_api.g_ret_sts_error;
407
408 fnd_msg_pub.count_and_get(
409 p_encoded => fnd_api.g_false,
410 p_count => x_msg_count,
411 p_data => x_msg_data);
412
413 -- Debug info.
414
415 -- Check if API is called in debug mode. If yes, disable debug.
416
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418 ROLLBACK TO activate_batch;
419
420 x_return_status := fnd_api.g_ret_sts_unexp_error;
421
422 fnd_msg_pub.count_and_get(
423 p_encoded => fnd_api.g_false,
424 p_count => x_msg_count,
425 p_data => x_msg_data);
426
427 -- Debug info.
428
429 -- Check if API is called in debug mode. If yes, disable debug.
430
431 WHEN OTHERS THEN
432 ROLLBACK TO activate_batch;
433
434 x_return_status := fnd_api.g_ret_sts_unexp_error;
435
436 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
437 fnd_message.set_token('ERROR',SQLERRM);
438 fnd_msg_pub.add;
439
440 fnd_msg_pub.count_and_get(
441 p_encoded => fnd_api.g_false,
442 p_count => x_msg_count,
443 p_data => x_msg_data);
444
445 -- Debug info.
446
447 -- Check if API is called in debug mode. If yes, disable debug.
448
449 END activate_batch;
450
451
452 END HZ_IMP_BATCH_SUMMARY_V2PUB;