[Home] [Help]
PACKAGE BODY: APPS.POS_IMP_SUPP_PKG
Source
1 PACKAGE BODY POS_IMP_SUPP_PKG AS
2 /* $Header: POSBATCHPB.pls 120.6 2011/03/18 01:07:48 yaoli noship $ */
3
4 FUNCTION func_batch_status
5 (
6 p_party_batch_status IN VARCHAR2,
7 p_supp_batch_status IN VARCHAR2
8 ) RETURN VARCHAR2 IS
9 l_batch_status VARCHAR2(30) := '';
10 BEGIN
11 IF (upper(p_party_batch_status) = 'PENDING' AND
12 upper(p_supp_batch_status) = 'PENDING') THEN
13 l_batch_status := 'PENDING';
14 END IF;
15
16 IF (upper(p_party_batch_status) = 'PENDING' AND
17 upper(p_supp_batch_status) = 'ACTIVE') THEN
18 l_batch_status := 'PENDING';
19 END IF;
20
21 IF (upper(p_party_batch_status) = 'PROCESSING' AND
22 upper(p_supp_batch_status) = 'PENDING') THEN
23 l_batch_status := 'PROCESSING';
24 END IF;
25
26 IF (upper(p_party_batch_status) = 'COMPLETED' AND
27 upper(p_supp_batch_status) = 'PROCESSING') THEN
28 l_batch_status := 'PROCESSING';
29 END IF;
30
31 IF (upper(p_party_batch_status) = 'COMPLETED' AND
32 upper(p_supp_batch_status) = 'PREPROCESSING') THEN
33 l_batch_status := 'PREPROCESSING';
34 END IF;
35
36 IF (upper(p_party_batch_status) = 'COMPLETED' AND
37 upper(p_supp_batch_status) = 'COMPLETED') THEN
38 l_batch_status := 'COMPLETED';
39 END IF;
40
41 IF (upper(p_party_batch_status) = 'COMPL_ERRORS' AND
42 upper(p_supp_batch_status) = 'COMPLETED') THEN
43 l_batch_status := 'COMPL_ERRORS';
44 END IF;
45
46 IF (upper(p_party_batch_status) = 'COMPL_ERRORS' AND
47 upper(p_supp_batch_status) = 'COMPL_ERRORS') THEN
48 l_batch_status := 'ACTION_REQUIRED';
49 END IF;
50
51 IF (upper(p_party_batch_status) = 'COMPLETED' AND
52 upper(p_supp_batch_status) = 'ACTION_REQUIRED') THEN
53 l_batch_status := 'ACTION_REQUIRED';
54 END IF;
55
56 IF (upper(p_party_batch_status) = 'ACTIVE' AND
57 upper(p_supp_batch_status) = 'ACTIVE') THEN
58 l_batch_status := 'ACTIVE';
59 END IF;
60
61 IF (p_party_batch_status IN ('COMPLETED', 'PENDING') AND
62 p_supp_batch_status IS NULL) THEN
63 l_batch_status := 'PENDING';
64 END IF;
65
66 IF (upper(p_party_batch_status) = 'COMPLETED' AND
67 upper(p_supp_batch_status) = 'PENDING') THEN
68 l_batch_status := 'PENDING';
69 END IF;
70
71 IF (upper(p_party_batch_status) = 'COMPLETED' AND
72 upper(p_supp_batch_status) = 'ACTIVE') THEN
73 l_batch_status := 'PENDING';
74 END IF;
75
76 IF (upper(p_party_batch_status) = 'REJECTED' AND
77 upper(p_supp_batch_status) = 'REJECTED') THEN
78 l_batch_status := 'REJECTED';
79 END IF;
80
81 IF (upper(p_party_batch_status) = 'ACTION_REQUIRED' AND
82 upper(p_supp_batch_status) = 'PENDING') THEN
83 l_batch_status := 'ACTION_REQUIRED';
84 END IF;
85
86 IF (upper(p_party_batch_status) = 'PENDING' AND
87 upper(p_supp_batch_status) = 'ACTION_REQUIRED') THEN
88 l_batch_status := 'ACTION_REQUIRED';
89 END IF;
90
91 IF (upper(p_party_batch_status) = 'ACTION_REQUIRED' AND
92 upper(p_supp_batch_status) = 'ACTIVE') THEN
93 l_batch_status := 'ACTION_REQUIRED';
94 END IF;
95
96 IF (upper(p_party_batch_status) = 'ACTION_REQUIRED' AND
97 upper(p_supp_batch_status) = 'COMPLETED') THEN
98 l_batch_status := 'ACTION_REQUIRED';
99 END IF;
100
101 IF (upper(p_party_batch_status) = 'ACTION_REQUIRED' AND
102 upper(p_supp_batch_status) = 'COMPL_ERRORS') THEN
103 l_batch_status := 'ACTION_REQUIRED';
104 END IF;
105
106 IF (upper(p_party_batch_status) = 'PENDING' AND
107 upper(p_supp_batch_status) = NULL) THEN
108 l_batch_status := 'PENDING';
109 END IF;
110
111 IF (upper(p_party_batch_status) = 'PENDING' AND
112 upper(p_supp_batch_status) = 'COMPLETED') THEN
113 l_batch_status := 'PENDING';
114 END IF;
115
116 IF (upper(p_party_batch_status) = 'PENDING' AND
117 upper(p_supp_batch_status) = 'PROCESSING') THEN
118 l_batch_status := 'PENDING';
119 END IF;
120
121 IF (upper(p_party_batch_status) = 'PROCESSING' AND
122 upper(p_supp_batch_status) = 'COMPLETED') THEN
123 l_batch_status := 'PROCESSING';
124 END IF;
125
126 IF (upper(p_party_batch_status) = 'PREPROCESSING' AND
127 upper(p_supp_batch_status) = 'COMPLETED') THEN
128 l_batch_status := 'PREPROCESSING';
129 END IF;
130
131 IF (upper(p_party_batch_status) = 'PREPROCESSING' AND
132 upper(p_supp_batch_status) = 'PREPROCESSING') THEN
133 l_batch_status := 'PREPROCESSING';
134 END IF;
135
136 IF (upper(p_party_batch_status) = 'PREPROCESSING' AND
137 upper(p_supp_batch_status) = 'PROCESSING') THEN
138 l_batch_status := 'PREPROCESSING';
139 END IF;
140
141 IF (upper(p_party_batch_status) = 'PROCESSING' AND
142 upper(p_supp_batch_status) = 'PREPROCESSING') THEN
143 l_batch_status := 'PREPROCESSING';
144 END IF;
145
146 IF (upper(p_party_batch_status) = 'PROCESSING' AND
147 upper(p_supp_batch_status) = 'PROCESSING') THEN
148 l_batch_status := 'PROCESSING';
149 END IF;
150
151 RETURN l_batch_status;
152
153 END func_batch_status;
154
155 PROCEDURE pre_import_counts
156 (
157 p_batch_id IN NUMBER,
158 p_original_system IN VARCHAR2
159 ) IS
160 l_suppliers_in_batch NUMBER := 0;
161 l_supp_sites_in_batch NUMBER := 0;
162 l_sup_contact_in_batch NUMBER := 0;
163 l_prodserv_in_batch NUMBER := 0;
164 l_bus_class_in_batch NUMBER := 0;
165 l_bankdtls1_in_batch NUMBER := 0;
166 l_bankdtls2_in_batch NUMBER := 0;
167 l_taxdtls1_in_batch NUMBER := 0;
168 l_taxdtls2_in_batch NUMBER := 0;
169 l_taxdtls3_in_batch NUMBER := 0;
170 l_total_batch_records NUMBER := 0;
171 l_uda_in_batch NUMBER := 0;
172 BEGIN
173 /* Select the counts for particular batch from all the interface tables */
174 SELECT COUNT(int.sdh_batch_id)
175 INTO l_suppliers_in_batch
176 FROM ap_suppliers_int INT
177 WHERE int.sdh_batch_id = p_batch_id
178 /*AND int.source_system = p_original_system*/
179 ;
180 SELECT COUNT(int.sdh_batch_id)
181 INTO l_supp_sites_in_batch
182 FROM ap_supplier_sites_int INT
183 WHERE int.sdh_batch_id = p_batch_id
184 /*AND int.source_system = p_original_system*/
185 ;
186 SELECT COUNT(int.sdh_batch_id)
187 INTO l_sup_contact_in_batch
188 FROM ap_sup_site_contact_int INT
189 WHERE int.sdh_batch_id = p_batch_id
190 /*AND int.source_system = p_original_system*/
191 ;
192 SELECT COUNT(int.sdh_batch_id)
193 INTO l_prodserv_in_batch
194 FROM pos_product_service_int INT
195 WHERE int.sdh_batch_id = p_batch_id
196 /*AND int.source_system = p_original_system*/
197 ;
198
199 SELECT COUNT(int.sdh_batch_id)
200 INTO l_bus_class_in_batch
201 FROM pos_business_class_int INT
202 WHERE int.sdh_batch_id = p_batch_id
203 /*AND int.source_system = p_original_system*/
204 ;
205
206 SELECT COUNT(int.batch_id)
207 INTO l_taxdtls1_in_batch
208 FROM pos_party_tax_profile_int INT
209 WHERE int.batch_id = p_batch_id
210 /*AND int.source_system = p_original_system*/
211 ;
212
213 SELECT COUNT(int.batch_id)
214 INTO l_taxdtls2_in_batch
215 FROM pos_party_tax_reg_int INT
216 WHERE int.batch_id = p_batch_id
217 /*AND int.source_system = p_original_system*/
218 ;
219
220 SELECT COUNT(int.batch_id)
221 INTO l_taxdtls3_in_batch
222 FROM pos_fiscal_class_int INT
223 WHERE int.batch_id = p_batch_id
224 /*AND int.source_system = p_original_system*/
225 ;
226
227 SELECT COUNT(int.batch_id)
228 INTO l_bankdtls1_in_batch
229 FROM pos_bank_account_det_int INT
230 WHERE int.batch_id = p_batch_id;
231
232 SELECT COUNT(int.batch_id)
233 INTO l_bankdtls2_in_batch
234 FROM pos_bank_accnt_owners_int INT
235 WHERE int.batch_id = p_batch_id;
236
237 SELECT COUNT(INT.BATCH_ID)
238 INTO l_uda_in_batch
239 FROM POS_SUPP_PROF_EXT_INTF INT
240 WHERE int.batch_id = p_batch_id
241 and NVL(int.PROCESS_STATUS, 1) = 1;
242
243 -- Counts to be added for UDA, Bank and Tax Details
244
245 l_total_batch_records := l_suppliers_in_batch + l_supp_sites_in_batch +
246 l_sup_contact_in_batch + l_prodserv_in_batch +
247 l_bus_class_in_batch + l_bankdtls1_in_batch +
248 l_bankdtls2_in_batch + l_taxdtls1_in_batch +
249 l_taxdtls2_in_batch + l_taxdtls3_in_batch +
250 l_uda_in_batch ;
251
252 /* This procedure also initializes the post count columns to zero */
253 UPDATE pos_imp_batch_summary
254 SET suppliers_in_batch = l_suppliers_in_batch,
255 sites_in_batch = l_supp_sites_in_batch,
256 contacts_in_batch = l_sup_contact_in_batch,
257 buss_class_in_batch = l_bus_class_in_batch,
258 prod_serv_in_batch = l_prodserv_in_batch,
259 bank_detls_in_batch = l_bankdtls1_in_batch +
260 l_bankdtls2_in_batch,
261 tax_dtls_in_batch = l_taxdtls1_in_batch +
262 l_taxdtls2_in_batch +
263 l_taxdtls3_in_batch,
264 total_batch_records = l_total_batch_records,
265 total_inserts = 0,
266 total_updates = 0,
267 total_merge_requests = 0,
268 total_auto_merged = 0,
269 suppliers_inserted = 0,
270 sites_inserted = 0,
271 contacts_inserted = 0,
272 buss_class_inserted = 0,
273 prod_serv_inserted = 0,
274 bank_detls_inserted = 0,
275 tax_dtls_inserted = 0,
276 suppliers_updated = 0,
277 sites_updated = 0,
278 contacts_updated = 0,
279 buss_class_updated = 0,
280 prod_serv_updated = 0,
281 bank_detls_updated = 0,
282 tax_dtls_updated = 0,
283 suppliers_merge_requests = 0,
284 sites_merge_requests = 0,
285 contacts_merge_requests = 0,
286 suppliers_auto_merged = 0,
287 suppliers_imported = 0,
288 sites_imported = 0,
289 contacts_imported = 0,
290 buss_class_imported = 0,
291 prod_serv_imported = 0,
292 bank_detls_imported = 0,
293 tax_dtls_imported = 0,
294 --total_errors = 0,
295 --total_batch_records = 0,
296 total_records_imported = 0
297 WHERE batch_id = p_batch_id;
298 END pre_import_counts;
299
300 PROCEDURE activate_batch
301 (
302 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
303 p_batch_id IN NUMBER,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2
307 ) IS
308 l_batch_id NUMBER;
309 l_original_system VARCHAR2(30);
310 l_pre_count_stat VARCHAR2(200) := 'begin POS_IMP_SUPP_PKG.pre_import_counts
311 (p_batch_id => :1,
312 p_original_system => :2); end;';
313
314 BEGIN
315
316 -- standard start of API savepoint
317 SAVEPOINT activate_batch;
318
319 -- Check if API is called in debug mode. If yes, enable debug.
320
321 -- initialize message list if p_init_msg_list is set to TRUE.
322 IF fnd_api.to_boolean(p_init_msg_list) THEN
323 fnd_msg_pub.initialize;
324 END IF;
325
326 -- initialize API return status to success.
327 x_return_status := fnd_api.g_ret_sts_success;
328
329 hz_imp_batch_summary_v2pub.activate_batch(p_init_msg_list => p_init_msg_list,
330 p_batch_id => p_batch_id,
331 x_return_status => x_return_status,
332 x_msg_count => x_msg_count,
333 x_msg_data => x_msg_data);
334
335 ----------------
336 -- do validation
337 ----------------
338
339 -- batch id must be a valid batch id in pos_imp_batch_summary table
340 BEGIN
341 SELECT batch_id,
342 original_system
343 INTO l_batch_id,
344 l_original_system
345 FROM pos_imp_batch_summary
346 WHERE batch_id = p_batch_id;
347
348 EXCEPTION
349 WHEN no_data_found THEN
350 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
351 fnd_message.set_token('FK', 'p_batch_id');
352 fnd_message.set_token('COLUMN', 'batch_id');
353 fnd_message.set_token('TABLE', 'pos_imp_batch_summary');
354 fnd_msg_pub.add;
355 x_return_status := fnd_api.g_ret_sts_error;
356 END;
357
358 IF x_return_status <> fnd_api.g_ret_sts_success THEN
359 RAISE fnd_api.g_exc_error;
360 END IF;
361
362 -- batch id must be a valid batch for processing
363 BEGIN
364 SELECT batch_id
365 INTO l_batch_id
366 FROM pos_imp_batch_summary
367 WHERE batch_id = p_batch_id
368 AND nvl(batch_status, 'ACTIVE') NOT IN
369 ('PURGED',
370 'REJECTED',
371 'PROCESSING',
372 'COMPLETED',
373 'ACTION_REQUIRED');
374
375 EXCEPTION
376 WHEN no_data_found THEN
377 fnd_message.set_name('AR', 'HZ_INVALID_IMP_BATCH');
378 fnd_msg_pub.add;
379 x_return_status := fnd_api.g_ret_sts_error;
380 END;
381
382 IF x_return_status <> fnd_api.g_ret_sts_success THEN
383 RAISE fnd_api.g_exc_error;
384 END IF;
385
386 ---------------------------------
387 -- update the batch summary table
388 ---------------------------------
389
390 -- update the batch record
391 UPDATE pos_imp_batch_summary
392 SET batch_status = 'ACTIVE',
393 last_updated_by = hz_utility_v2pub.last_updated_by,
394 last_update_date = hz_utility_v2pub.last_update_date,
395 last_update_login = hz_utility_v2pub.last_update_login
396 WHERE batch_id = p_batch_id;
397
398 ------------------------------------
399 -- call the pre import count process
400 ------------------------------------
401
402 -- call the count of records calculation routine
403 -- use dynamic sql to avoid compilation error in 8i
404 EXECUTE IMMEDIATE l_pre_count_stat
405 USING p_batch_id, l_original_system;
406
407 -- standard call to get message count and if count is 1, get message info.
408 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
409 p_count => x_msg_count,
410 p_data => x_msg_data);
411
412 EXCEPTION
413 WHEN fnd_api.g_exc_error THEN
414 ROLLBACK TO activate_batch;
415
416 x_return_status := fnd_api.g_ret_sts_error;
417
418 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
419 p_count => x_msg_count,
420 p_data => x_msg_data);
421
422 WHEN fnd_api.g_exc_unexpected_error THEN
423 ROLLBACK TO activate_batch;
424
425 x_return_status := fnd_api.g_ret_sts_unexp_error;
426
427 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
428 p_count => x_msg_count,
429 p_data => x_msg_data);
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(p_encoded => fnd_api.g_false,
441 p_count => x_msg_count,
442 p_data => x_msg_data);
443
444 END activate_batch;
445
446 PROCEDURE purge_batch
447 (
448 errbuf OUT NOCOPY VARCHAR2,
449 retcode OUT NOCOPY VARCHAR2,
450 p_batch_id IN VARCHAR2
451 ) IS
452 i NUMBER;
453 l_error_message VARCHAR2(2000);
454 l_status pos_imp_batch_summary.main_conc_status%TYPE;
455 BEGIN
456
457 /* Check for 8i database */
458 BEGIN
459 SELECT REPLACE(substr(version, 1, instr(version, '.', 1, 3)), '.')
460 INTO i
461 FROM v$instance;
462
463 IF i >= 920 THEN
464 NULL;
465 ELSE
466 RAISE fnd_api.g_exc_error;
467 END IF;
468
469 EXCEPTION
470 WHEN fnd_api.g_exc_error THEN
471 ROLLBACK WORK;
472 fnd_message.set_name('AR', 'HZ_IMP_DB_VER_CHECK');
473 fnd_msg_pub.add;
474 fnd_msg_pub.reset;
475 FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
476 l_error_message := fnd_msg_pub.get(p_msg_index => i,
477 p_encoded => fnd_api.g_false);
478 fnd_file.put_line(fnd_file.output, l_error_message);
479 fnd_file.put_line(fnd_file.log, l_error_message);
480 END LOOP;
481 retcode := 2;
482 RETURN;
483 WHEN fnd_api.g_exc_unexpected_error THEN
484 ROLLBACK WORK;
485 fnd_file.put_line(fnd_file.log, 'Unexpected error occured ');
486 fnd_file.put_line(fnd_file.log, SQLERRM);
487 retcode := 2;
488 RETURN;
489 WHEN OTHERS THEN
490 ROLLBACK WORK;
491 fnd_file.put_line(fnd_file.log, SQLERRM);
492 retcode := 2;
493 RETURN;
494 END;
495
496 /* Delete party data from HZ interface tables */
497 hz_imp_purge_pkg.purge_batch(errbuf, retcode, p_batch_id);
498
499 /* Batch is Processing */
500 BEGIN
501 SELECT main_conc_status
502 INTO l_status
503 FROM pos_imp_batch_summary
504 WHERE batch_id = p_batch_id;
505
506 IF l_status = 'PROCESSING' THEN
507 RAISE fnd_api.g_exc_error;
508 END IF;
509 EXCEPTION
510 WHEN fnd_api.g_exc_error THEN
511 ROLLBACK WORK;
512 fnd_file.put_line(fnd_file.log,
513 'Error : You cannot purge a batch when a batch is being processed.');
514 retcode := 2;
515 RETURN;
516 WHEN OTHERS THEN
517 ROLLBACK WORK;
518 fnd_file.put_line(fnd_file.log, SQLERRM);
519 retcode := 2;
520 RETURN;
521 END;
522 fnd_file.put_line(fnd_file.log, ' Purge Starts ... ');
523
524 -- Interface Tables
525 fnd_file.put_line(fnd_file.log, ' Purging Interface Tables ... ');
526
527 DELETE ap_suppliers_int WHERE sdh_batch_id = p_batch_id;
528 COMMIT;
529
530 DELETE ap_supplier_sites_int WHERE sdh_batch_id = p_batch_id;
531 COMMIT;
532
533 DELETE ap_sup_site_contact_int WHERE sdh_batch_id = p_batch_id;
534 COMMIT;
535
536 DELETE pos_product_service_int WHERE sdh_batch_id = p_batch_id;
537 COMMIT;
538
539 DELETE pos_business_class_int WHERE sdh_batch_id = p_batch_id;
540 COMMIT;
541
542 DELETE pos_party_tax_profile_int WHERE batch_id = p_batch_id;
543 COMMIT;
544
545 DELETE pos_party_tax_reg_int WHERE batch_id = p_batch_id;
546 COMMIT;
547
548 DELETE pos_fiscal_class_int WHERE batch_id = p_batch_id;
549 COMMIT;
550
551 DELETE pos_bank_account_det_int WHERE batch_id = p_batch_id;
552 COMMIT;
553
554 DELETE pos_bank_accnt_owners_int WHERE batch_id = p_batch_id;
555 COMMIT;
556
557 fnd_file.put_line(fnd_file.log, ' Purged Interface Tables ... ');
558
559 fnd_file.put_line(fnd_file.log,
560 ' Update pos_imp_batch_summary table (+)');
561 -- Update pos_imp_batch_summary table.
562 UPDATE pos_imp_batch_summary
563 SET batch_status = 'PURGED',
564 purge_date = SYSDATE,
565 purged_by_user_id = hz_utility_v2pub.user_id
566 WHERE batch_id = p_batch_id;
567 COMMIT;
568
569 fnd_file.put_line(fnd_file.log,
570 ' Update pos_imp_batch_summary table (-)');
571 EXCEPTION
572 WHEN OTHERS THEN
573 ROLLBACK WORK;
574 fnd_file.put_line(fnd_file.log, SQLERRM);
575 retcode := 2;
576 RETURN;
577 END purge_batch;
578
579 PROCEDURE reject_batch
580 (
581 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
582 p_batch_id IN NUMBER,
583 x_return_status OUT NOCOPY VARCHAR2,
584 x_msg_count OUT NOCOPY NUMBER,
585 x_msg_data OUT NOCOPY VARCHAR2
586 ) IS
587 l_batch_id NUMBER;
588 l_original_system VARCHAR2(30);
589 l_pre_count_stat VARCHAR2(200) := 'begin POS_IMP_SUPP_PKG.pre_import_counts
590 (p_batch_id => :1,
591 p_original_system => :2); end;';
592
593 BEGIN
594
595 -- standard start of API savepoint
596 SAVEPOINT reject_batch;
597
598 -- Check if API is called in debug mode. If yes, enable debug.
599
600 -- initialize message list if p_init_msg_list is set to TRUE.
601 IF fnd_api.to_boolean(p_init_msg_list) THEN
602 fnd_msg_pub.initialize;
603 END IF;
604
605 -- initialize API return status to success.
606 x_return_status := fnd_api.g_ret_sts_success;
607
608 ----------------
609 -- do validation
610 ----------------
611
612 -- batch id must be a valid batch id in pos_imp_batch_summary table and hz_imp_batch_summary
613 BEGIN
614 SELECT pos.batch_id,
615 pos.original_system
616 INTO l_batch_id,
617 l_original_system
618 FROM pos_imp_batch_summary pos,
619 hz_imp_batch_summary hz
620 WHERE pos.batch_id = hz.batch_id
621 AND pos.batch_id = p_batch_id;
622
623 EXCEPTION
624 WHEN no_data_found THEN
625 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
626 fnd_message.set_token('FK', 'p_batch_id');
627 fnd_message.set_token('COLUMN', 'batch_id');
628 fnd_message.set_token('TABLE', 'pos_imp_batch_summary');
629 fnd_msg_pub.add;
630 x_return_status := fnd_api.g_ret_sts_error;
631 END;
632
633 IF x_return_status <> fnd_api.g_ret_sts_success THEN
634 RAISE fnd_api.g_exc_error;
635 END IF;
636
637 /* -- batch id must be a valid batch for processing
638 BEGIN
639 SELECT pos.batch_id
640 INTO l_batch_id
641 FROM pos_imp_batch_summary pos, hz_imp_batch_summary hz
642 WHERE pos.batch_id = hz.batch_id
643 and pos.batch_id = p_batch_id
644 AND nvl(pos.batch_status,'ACTIVE') NOT IN
645 ('PURGED',
646 'REJECTED',
647 'PROCESSING',
648 'COMPLETED',
649 'ACTION_REQUIRED')
650 AND nvl(hz.batch_status,'ACTIVE') NOT IN
651 ('PURGED',
652 'REJECTED',
653 'PROCESSING',
654 'COMPLETED',
655 'ACTION_REQUIRED');
656
657 EXCEPTION
658 WHEN no_data_found THEN
659 fnd_message.set_name('AR', 'HZ_INVALID_IMP_BATCH');
660 fnd_msg_pub.add;
661 x_return_status := fnd_api.g_ret_sts_error;
662 END;
663
664 IF x_return_status <> fnd_api.g_ret_sts_success THEN
665 RAISE fnd_api.g_exc_error;
666 END IF; */
667
668 ---------------------------------
669 -- update the batch summary table
670 ---------------------------------
671
672 -- update the batch record
673 UPDATE hz_imp_batch_summary
674 SET batch_status = 'REJECTED',
675 import_status = 'REJECTED',
676 last_updated_by = hz_utility_v2pub.last_updated_by,
677 last_update_date = hz_utility_v2pub.last_update_date,
678 last_update_login = hz_utility_v2pub.last_update_login
679 WHERE batch_id = p_batch_id;
680
681 UPDATE pos_imp_batch_summary
682 SET batch_status = 'REJECTED',
683 import_status = 'REJECTED',
684 last_updated_by = hz_utility_v2pub.last_updated_by,
685 last_update_date = hz_utility_v2pub.last_update_date,
686 last_update_login = hz_utility_v2pub.last_update_login
687 WHERE batch_id = p_batch_id;
688
689 -- standard call to get message count and if count is 1, get message info.
690 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
691 p_count => x_msg_count,
692 p_data => x_msg_data);
693
694 EXCEPTION
695 WHEN fnd_api.g_exc_error THEN
696 ROLLBACK TO reject_batch;
697
698 x_return_status := fnd_api.g_ret_sts_error;
699
700 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
701 p_count => x_msg_count,
702 p_data => x_msg_data);
703
704 WHEN fnd_api.g_exc_unexpected_error THEN
705 ROLLBACK TO reject_batch;
706
707 x_return_status := fnd_api.g_ret_sts_unexp_error;
708
709 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
710 p_count => x_msg_count,
711 p_data => x_msg_data);
712
713 WHEN OTHERS THEN
714 ROLLBACK TO reject_batch;
715
716 x_return_status := fnd_api.g_ret_sts_unexp_error;
717
718 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
719 fnd_message.set_token('ERROR', SQLERRM);
720 fnd_msg_pub.add;
721
722 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
723 p_count => x_msg_count,
724 p_data => x_msg_data);
725
726 END reject_batch;
727
728 PROCEDURE create_import_batch
729 (
730 p_batch_id IN NUMBER,
731 p_batch_name IN VARCHAR2,
732 p_description IN VARCHAR2,
733 p_original_system IN VARCHAR2,
734 p_load_type IN VARCHAR2,
735 p_est_no_of_records IN NUMBER,
736 x_return_status OUT NOCOPY VARCHAR2,
737 x_msg_count OUT NOCOPY NUMBER,
738 x_msg_data OUT NOCOPY VARCHAR2
739 ) IS
740 os_exists_flag VARCHAR2(1) := 'N';
741
742 BEGIN
743 -- standard start of API savepoint
744 SAVEPOINT create_import_batch;
745
746 -- initialize API return status to success.
747 x_return_status := fnd_api.g_ret_sts_success;
748
749 ----------------
750 -- do validation
751 ----------------
752
753 -- batch name is mandatory
754 hz_utility_v2pub.validate_mandatory(p_create_update_flag => 'C',
755 p_column => 'batch_name',
756 p_column_value => p_batch_name,
757 p_restricted => 'Y',
758 x_return_status => x_return_status);
759
760 -- original_system is mandatory
761 hz_utility_v2pub.validate_mandatory(p_create_update_flag => 'C',
762 p_column => 'original_system',
763 p_column_value => p_original_system,
764 p_restricted => 'Y',
765 x_return_status => x_return_status);
766
767 BEGIN
768 SELECT 'Y'
769 INTO os_exists_flag
770 FROM hz_orig_systems_b
771 WHERE orig_system = p_original_system
772 AND orig_system <> 'SST'
773 AND status = 'A';
774 EXCEPTION
775 WHEN no_data_found THEN
776 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
777 fnd_message.set_token('FK', 'orig_system');
778 fnd_message.set_token('COLUMN', 'orig_system');
779 fnd_message.set_token('TABLE', 'HZ_ORIG_SYSTEMS_B');
780 fnd_msg_pub.add;
781 x_return_status := fnd_api.g_ret_sts_error;
782 END;
783
784 IF x_return_status <> fnd_api.g_ret_sts_success THEN
785 RAISE fnd_api.g_exc_error;
786 END IF;
787
788 --------------------------------------------
789 -- insert the record with batch id
790 --------------------------------------------
791
792 INSERT INTO pos_imp_batch_summary
793 (batch_id,
794 batch_name,
795 description,
796 original_system,
797 load_type,
798 est_no_of_records,
799 created_by,
800 creation_date,
801 last_updated_by,
802 last_update_date,
803 last_update_login)
804 VALUES
805 (p_batch_id,
806 p_batch_name,
807 p_description,
808 p_original_system,
809 p_load_type,
810 p_est_no_of_records,
811 hz_utility_v2pub.created_by,
812 hz_utility_v2pub.creation_date,
813 hz_utility_v2pub.last_updated_by,
814 hz_utility_v2pub.last_update_date,
815 hz_utility_v2pub.last_update_login);
816
817 -- standard call to get message count and if count is 1, get message info.
818 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
819 p_count => x_msg_count,
820 p_data => x_msg_data);
821
822 EXCEPTION
823 WHEN fnd_api.g_exc_error THEN
824 ROLLBACK TO create_import_batch;
825
826 x_return_status := fnd_api.g_ret_sts_error;
827
828 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
829 p_count => x_msg_count,
830 p_data => x_msg_data);
831
832 WHEN fnd_api.g_exc_unexpected_error THEN
833 ROLLBACK TO create_import_batch;
834
835 x_return_status := fnd_api.g_ret_sts_unexp_error;
836
837 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
838 p_count => x_msg_count,
839 p_data => x_msg_data);
840
841 WHEN OTHERS THEN
842 ROLLBACK TO create_import_batch;
843
844 x_return_status := fnd_api.g_ret_sts_unexp_error;
845
846 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
847 fnd_message.set_token('ERROR', SQLERRM);
848 fnd_msg_pub.add;
849
850 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
851 p_count => x_msg_count,
852 p_data => x_msg_data);
853
854 END create_import_batch;
855
856 END POS_IMP_SUPP_PKG;