[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_TRXAPI_PVT
Source
1 PACKAGE BODY CN_IMP_TRXAPI_PVT AS
2 -- $Header: cnvimtxb.pls 120.11 2009/03/02 09:32:31 gmarwah ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_TRXAPI_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimtxb.pls';
6
7 -- Start of comments
8 -- API name : Trxapi_Import
9 -- Type : Private.
10 -- Function : programtransfer data from staging table into
11 -- cn_comm_lines_api_all
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN p_imp_header_id IN NUMBER,
15 -- OUT : errbuf OUT VARCHAR2 Required
16 -- retcode OUTVARCHAR2 Optional
17 -- Version : Current version 1.0
18 --
19 --
20 --
21 -- Notes : The import process will terminated when error occurs.
22 -- Cannot partially import data because if error happens and re-run
23 -- SQL*Loader to stage fixed data,the old one will be deleted first.
24 -- Because SQL*Loader run in APPEND mode.
25 -- End of comments
26
27 PROCEDURE Trxapi_Import
28 (errbuf OUT NOCOPY VARCHAR2,
29 retcode OUT NOCOPY VARCHAR2,
30 p_imp_header_id IN NUMBER,
31 p_org_id IN NUMBER
32 ) IS
33
34 l_status_code cn_imp_lines.status_code%TYPE := 'STAGE';
35
36 CURSOR c_trxapi_imp_csr IS
37 SELECT
38 imp_line_id,
39 imp_header_id,
40 status_code,
41 error_code,
42 trim(resource_name) resource_name,
43 trim(employee_number) employee_number,
44 trim(processed_date) processed_date,
45 trim(transaction_amount) transaction_amount,
46 trim(quantity) quantity,
47 trim(order_number) order_number,
48 trim(order_date) order_date,
49 trim(invoice_number) invoice_number,
50 trim(invoice_date) invoice_date,
51 trim(revenue_type) revenue_type,
52 trim(sales_channel) sales_channel,
53 trim(revenue_class_name) revenue_class_name,
54 trim(attribute_category) attribute_category,
55 trim(attribute1) attribute1 ,
56 trim(attribute2) attribute2 ,
57 trim(attribute3) attribute3 ,
58 trim(attribute4) attribute4 ,
59 trim(attribute5) attribute5 ,
60 trim(attribute6) attribute6 ,
61 trim(attribute7) attribute7 ,
62 trim(attribute8) attribute8 ,
63 trim(attribute9) attribute9 ,
64 trim(attribute10) attribute10 ,
65 trim(attribute11) attribute11 ,
66 trim(attribute12) attribute12 ,
67 trim(attribute13) attribute13 ,
68 trim(attribute14) attribute14 ,
69 trim(attribute15) attribute15 ,
70 trim(attribute16) attribute16 ,
71 trim(attribute17) attribute17 ,
72 trim(attribute18) attribute18 ,
73 trim(attribute19) attribute19 ,
74 trim(attribute20) attribute20 ,
75 trim(attribute21) attribute21 ,
76 trim(attribute22) attribute22 ,
77 trim(attribute23) attribute23 ,
78 trim(attribute24) attribute24 ,
79 trim(attribute25) attribute25 ,
80 trim(attribute26) attribute26 ,
81 trim(attribute27) attribute27 ,
82 trim(attribute28) attribute28 ,
83 trim(attribute29) attribute29 ,
84 trim(attribute30) attribute30 ,
85 trim(attribute31) attribute31 ,
86 trim(attribute32) attribute32 ,
87 trim(attribute33) attribute33 ,
88 trim(attribute34) attribute34 ,
89 trim(attribute35) attribute35 ,
90 trim(attribute36) attribute36 ,
91 trim(attribute37) attribute37 ,
92 trim(attribute38) attribute38 ,
93 trim(attribute39) attribute39 ,
94 trim(attribute40) attribute40 ,
95 trim(attribute41) attribute41 ,
96 trim(attribute42) attribute42 ,
97 trim(attribute43) attribute43 ,
98 trim(attribute44) attribute44 ,
99 trim(attribute45) attribute45 ,
100 trim(attribute46) attribute46 ,
101 trim(attribute47) attribute47 ,
102 trim(attribute48) attribute48 ,
103 trim(attribute49) attribute49 ,
104 trim(attribute50) attribute50 ,
105 trim(attribute51) attribute51 ,
106 trim(attribute52) attribute52 ,
107 trim(attribute53) attribute53 ,
108 trim(attribute54) attribute54 ,
109 trim(attribute55) attribute55 ,
110 trim(attribute56) attribute56 ,
111 trim(attribute57) attribute57 ,
112 trim(attribute58) attribute58 ,
113 trim(attribute59) attribute59 ,
114 trim(attribute60) attribute60 ,
115 trim(attribute61) attribute61 ,
116 trim(attribute62) attribute62 ,
117 trim(attribute63) attribute63 ,
118 trim(attribute64) attribute64 ,
119 trim(attribute65) attribute65 ,
120 trim(attribute66) attribute66 ,
121 trim(attribute67) attribute67 ,
122 trim(attribute68) attribute68 ,
123 trim(attribute69) attribute69 ,
124 trim(attribute70) attribute70 ,
125 trim(attribute71) attribute71 ,
126 trim(attribute72) attribute72 ,
127 trim(attribute73) attribute73 ,
128 trim(attribute74) attribute74 ,
129 trim(attribute75) attribute75 ,
130 trim(attribute76) attribute76 ,
131 trim(attribute77) attribute77 ,
132 trim(attribute78) attribute78 ,
133 trim(attribute79) attribute79 ,
134 trim(attribute80) attribute80 ,
135 trim(attribute81) attribute81 ,
136 trim(attribute82) attribute82 ,
137 trim(attribute83) attribute83 ,
138 trim(attribute84) attribute84 ,
139 trim(attribute85) attribute85 ,
140 trim(attribute86) attribute86 ,
141 trim(attribute87) attribute87 ,
142 trim(attribute88) attribute88 ,
143 trim(attribute89) attribute89 ,
144 trim(attribute90) attribute90 ,
145 trim(attribute91) attribute91 ,
146 trim(attribute92) attribute92 ,
147 trim(attribute93) attribute93 ,
148 trim(attribute94) attribute94 ,
149 trim(attribute95) attribute95 ,
150 trim(attribute96) attribute96 ,
151 trim(attribute97) attribute97 ,
152 trim(attribute98) attribute98 ,
153 trim(attribute99) attribute99 ,
154 trim(attribute100) attribute100 ,
155 trim(commission_amount) commission_amount,
156 trim(exchange_rate) exchange_rate,
157 trim(transaction_currency_code) transaction_currency_code ,
158 trim(discount_percentage) discount_percentage,
159 trim(margin_percentage) margin_percentage,
160 trim(reason_code) reason_code ,
161 trim(pre_processed_code) pre_processed_code ,
162 trim(compensation_group_name) compensation_group_name ,
163 trim(plan_element_name) plan_element_name ,
164 trim(role_name) role_name ,
165 trim(rollup_date) rollup_date,
166 trim(line_number) line_number,
167 trim(split_pct) split_pct,
168 -- Added new column, bugID 7033617
169 NVL(trim(preserve_credit_override_flag),'N') preserve_credit_override_flag,
170 trim(adjust_comments) adjust_comments
171 FROM CN_COMMLINE_API_IMP_V
172 WHERE imp_header_id = p_imp_header_id
173 AND status_code = l_status_code
174 ;
175
176 l_api_name CONSTANT VARCHAR2(30) := 'Trxapi_Import';
177
178 l_trxapi_imp c_trxapi_imp_csr%ROWTYPE;
179 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
180 l_rev_class_id cn_revenue_classes.revenue_class_id%TYPE;
181 l_role_id cn_roles.role_id%TYPE;
182 l_period_id cn_acc_period_statuses_v.period_id%TYPE;
183 l_rollup_period_id cn_acc_period_statuses_v.period_id%TYPE;
184 l_quota_id cn_quotas.quota_id%TYPE;
185 l_comp_group_id cn_comp_groups.comp_group_id%TYPE;
186 l_meaning cn_lookups.meaning%TYPE;
187
188 l_processed_row NUMBER := 0;
189 l_failed_row NUMBER := 0;
190 l_message VARCHAR2(2000);
191 l_error_code VARCHAR2(30);
192 l_comm_lines_api_id NUMBER(15);
193 l_header_list VARCHAR2(2000);
194 l_sql_stmt VARCHAR2(2000);
195 err_num NUMBER;
196 l_msg_count NUMBER := 0;
197 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
198 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
199 l_temp NUMBER;
200 l_err_flag CHAR(1);
201
202 BEGIN
203 retcode := 0 ;
204
205 -- Get imp_header info
206 SELECT name, status_code,server_flag,imp_map_id, source_column_num,
207 import_type_code
208 INTO l_imp_header.name ,l_imp_header.status_code ,
209 l_imp_header.server_flag, l_imp_header.imp_map_id,
210 l_imp_header.source_column_num,l_imp_header.import_type_code
211 FROM cn_imp_headers
212 WHERE imp_header_id = p_imp_header_id;
213
214 -- open process audit batch
215 cn_message_pkg.begin_batch
216 ( x_process_type => l_imp_header.import_type_code,
217 x_parent_proc_audit_id => p_imp_header_id ,
218 x_process_audit_id => l_process_audit_id,
219 x_request_id => null,
220 p_org_id => p_org_id);
221
222 cn_message_pkg.write
223 (p_message_text => 'TRXAPI: Start Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
224 p_message_type => 'MILESTONE');
225
226 -- Get source column name list and target column dynamic sql statement
227 CN_IMPORT_PVT.build_error_rec
228 (p_imp_header_id => p_imp_header_id,
229 x_header_list => l_header_list,
230 x_sql_stmt => l_sql_stmt);
231
232 OPEN c_trxapi_imp_csr;
233 LOOP
234 FETCH c_trxapi_imp_csr INTO l_trxapi_imp;
235 EXIT WHEN c_trxapi_imp_csr%notfound;
236
237 BEGIN
238
239 l_processed_row := l_processed_row + 1;
240 l_rev_class_id := NULL;
241 l_salesrep_id := NULL;
242
243 cn_message_pkg.write
244 (p_message_text => 'TRXAPI:Record ' || To_char(l_processed_row) || ' imp_line_id = ' || To_char(l_trxapi_imp.imp_line_id),
245 p_message_type => 'DEBUG');
246
247 -- -------- Checking for all required fields ----------------- --
248 -- Check required field
249 IF l_trxapi_imp.processed_date IS NULL
250 OR l_trxapi_imp.transaction_amount IS NULL
251 OR l_trxapi_imp.employee_number IS NULL
252 OR l_trxapi_imp.revenue_type IS NULL THEN
253 l_failed_row := l_failed_row + 1;
254 l_error_code := 'CN_IMP_MISS_REQUIRED';
255 l_message := fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED');
256 CN_IMPORT_PVT.update_imp_lines
257 (p_imp_line_id => l_trxapi_imp.imp_line_id,
258 p_status_code => 'FAIL',
259 p_error_code => l_error_code,
260 p_error_msg => l_message);
261 CN_IMPORT_PVT.update_imp_headers
262 (p_imp_header_id => p_imp_header_id,
263 p_status_code => 'IMPORT_FAIL',
264 p_failed_row => l_failed_row);
265 cn_message_pkg.write
266 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
267 p_message_type => 'ERROR');
268 CN_IMPORT_PVT.write_error_rec
269 (p_imp_header_id => p_imp_header_id,
270 p_imp_line_id => l_trxapi_imp.imp_line_id,
271 p_header_list => l_header_list,
272 p_sql_stmt => l_sql_stmt);
273
274 retcode := 2;
275 errbuf := l_message;
276 GOTO end_loop;
277 END IF;
278
279 -- Get salesrep_id
280 BEGIN
281 SELECT salesrep_id
282 INTO l_salesrep_id
283 FROM cn_salesreps
284 WHERE employee_number = l_trxapi_imp.employee_number and org_id = p_org_id;
285 EXCEPTION
286 WHEN no_data_found THEN
287 l_failed_row := l_failed_row + 1;
288 l_error_code := 'CN_IMP_NF_RSRC_ID';
289 l_message := fnd_message.get_string('CN','CN_IMP_NF_RSRC_ID');
290 CN_IMPORT_PVT.update_imp_lines
291 (p_imp_line_id => l_trxapi_imp.imp_line_id,
292 p_status_code => 'FAIL',
293 p_error_code => l_error_code,
294 p_error_msg => l_message);
295 CN_IMPORT_PVT.update_imp_headers
296 (p_imp_header_id => p_imp_header_id,
297 p_status_code => 'IMPORT_FAIL',
298 p_failed_row => l_failed_row);
299 cn_message_pkg.write
300 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
301 p_message_type => 'ERROR');
302 CN_IMPORT_PVT.write_error_rec
303 (p_imp_header_id => p_imp_header_id,
304 p_imp_line_id => l_trxapi_imp.imp_line_id,
305 p_header_list => l_header_list,
306 p_sql_stmt => l_sql_stmt);
307
308 retcode := 2;
309 errbuf := l_message;
310 GOTO end_loop;
311 END;
312
313 -- get process_period_id
314 BEGIN
315 SELECT period_id
316 INTO l_period_id
317 FROM cn_acc_period_statuses_v
318 WHERE l_trxapi_imp.processed_date BETWEEN start_date AND end_date
319 AND period_status IN ('O','F') and org_id = p_org_id;
320 EXCEPTION
321 WHEN no_data_found THEN
322 l_failed_row := l_failed_row + 1;
323 l_error_code := 'NOT_WITHIN_OPEN_PERIODS';
324 l_message := fnd_message.get_string('CN','NOT_WITHIN_OPEN_PERIODS');
325 CN_IMPORT_PVT.update_imp_lines
326 (p_imp_line_id => l_trxapi_imp.imp_line_id,
327 p_status_code => 'FAIL',
328 p_error_code => l_error_code,
329 p_error_msg => l_message);
330 CN_IMPORT_PVT.update_imp_headers
331 (p_imp_header_id => p_imp_header_id,
332 p_status_code => 'IMPORT_FAIL',
333 p_failed_row => l_failed_row);
334 cn_message_pkg.write
335 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
336 p_message_type => 'ERROR');
337 CN_IMPORT_PVT.write_error_rec
338 (p_imp_header_id => p_imp_header_id,
339 p_imp_line_id => l_trxapi_imp.imp_line_id,
340 p_header_list => l_header_list,
341 p_sql_stmt => l_sql_stmt);
342
343 retcode := 2;
344 errbuf := l_message;
345 GOTO end_loop;
346 END;
347
348 -- -------- Checking for all optional fields ----------------- --
349
350 -- Get revenue_class_id when revenue_class_name exists
351 IF l_trxapi_imp.revenue_class_name IS NOT NULL THEN
352 l_rev_class_id := cn_api.get_rev_class_id
353 (p_rev_class_name => l_trxapi_imp.revenue_class_name,
354 p_org_id => p_org_id);
355 IF l_rev_class_id IS NULL THEN
356 l_failed_row := l_failed_row + 1;
357 l_error_code := 'CN_IMP_NF_REVCLS_ID';
358 l_message := fnd_message.get_string('CN','CN_IMP_NF_REVCLS_ID');
359 CN_IMPORT_PVT.update_imp_lines
360 (p_imp_line_id => l_trxapi_imp.imp_line_id,
361 p_status_code => 'FAIL',
362 p_error_code => l_error_code,
363 p_error_msg => l_message);
364 CN_IMPORT_PVT.update_imp_headers
365 (p_imp_header_id => p_imp_header_id,
366 p_status_code => 'IMPORT_FAIL',
367 p_failed_row => l_failed_row);
368 cn_message_pkg.write
369 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
370 p_message_type => 'ERROR');
371 CN_IMPORT_PVT.write_error_rec
372 (p_imp_header_id => p_imp_header_id,
373 p_imp_line_id => l_trxapi_imp.imp_line_id,
374 p_header_list => l_header_list,
375 p_sql_stmt => l_sql_stmt);
376 retcode := 2;
377 errbuf := l_message;
378 GOTO end_loop;
379 END IF;
380 END IF;
381
382 -- Get quota_id from plan_element_name
383 IF l_trxapi_imp.plan_element_name IS NOT NULL THEN
384 BEGIN
385 SELECT quota_id
386 INTO l_quota_id
387 FROM cn_quotas_v
388 WHERE name = l_trxapi_imp.plan_element_name and org_id = p_org_id
389 ;
390 EXCEPTION
391 WHEN no_data_found THEN
392 l_failed_row := l_failed_row + 1;
393 l_error_code := 'CN_IMP_NF_QUOTA';
394 l_message :=
395 fnd_message.get_string('CN','CN_IMP_NF_QUOTA');
396 CN_IMPORT_PVT.update_imp_lines
397 (p_imp_line_id => l_trxapi_imp.imp_line_id,
398 p_status_code => 'FAIL',
399 p_error_code => l_error_code,
400 p_error_msg => l_message);
401 CN_IMPORT_PVT.update_imp_headers
402 (p_imp_header_id => p_imp_header_id,
403 p_status_code => 'IMPORT_FAIL',
404 p_failed_row => l_failed_row);
405 cn_message_pkg.write
406 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
407 p_message_type => 'ERROR');
408 CN_IMPORT_PVT.write_error_rec
409 (p_imp_header_id => p_imp_header_id,
410 p_imp_line_id => l_trxapi_imp.imp_line_id,
411 p_header_list => l_header_list,
412 p_sql_stmt => l_sql_stmt);
413
414 retcode := 2;
415 errbuf := l_message;
416 GOTO end_loop;
417 END;
418 END IF;
419
420 -- Get comp_group_id from compensation_group_name
421 IF l_trxapi_imp.compensation_group_name IS NOT NULL THEN
422 BEGIN
423 SELECT comp_group_id
424 INTO l_comp_group_id
425 FROM cn_comp_groups
426 WHERE name = l_trxapi_imp.compensation_group_name
427 ;
428 EXCEPTION
429 WHEN no_data_found THEN
430 l_failed_row := l_failed_row + 1;
431 l_error_code := 'CN_IMP_NF_CG';
432 l_message :=
433 fnd_message.get_string('CN','CN_IMP_NF_CG');
434 CN_IMPORT_PVT.update_imp_lines
435 (p_imp_line_id => l_trxapi_imp.imp_line_id,
436 p_status_code => 'FAIL',
437 p_error_code => l_error_code,
438 p_error_msg => l_message);
439 CN_IMPORT_PVT.update_imp_headers
440 (p_imp_header_id => p_imp_header_id,
441 p_status_code => 'IMPORT_FAIL',
442 p_failed_row => l_failed_row);
443 cn_message_pkg.write
444 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
445 p_message_type => 'ERROR');
446 CN_IMPORT_PVT.write_error_rec
447 (p_imp_header_id => p_imp_header_id,
448 p_imp_line_id => l_trxapi_imp.imp_line_id,
449 p_header_list => l_header_list,
450 p_sql_stmt => l_sql_stmt);
451
452 retcode := 2;
453 errbuf := l_message;
454 GOTO end_loop;
455 END;
456 END IF;
457
458 -- Get role_id when role_name exists
459 IF l_trxapi_imp.role_name IS NOT NULL THEN
460 l_role_id := cn_api.get_role_id
461 (p_role_name => l_trxapi_imp.role_name);
462 IF l_role_id IS NULL THEN
463 l_failed_row := l_failed_row + 1;
464 l_error_code := 'CN_IMP_NF_ROLE';
465 l_message := fnd_message.get_string('CN','CN_IMP_NF_ROLE');
466 CN_IMPORT_PVT.update_imp_lines
467 (p_imp_line_id => l_trxapi_imp.imp_line_id,
468 p_status_code => 'FAIL',
469 p_error_code => l_error_code,
470 p_error_msg => l_message);
471 CN_IMPORT_PVT.update_imp_headers
472 (p_imp_header_id => p_imp_header_id,
473 p_status_code => 'IMPORT_FAIL',
474 p_failed_row => l_failed_row);
475 cn_message_pkg.write
476 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
477 p_message_type => 'ERROR');
478 CN_IMPORT_PVT.write_error_rec
479 (p_imp_header_id => p_imp_header_id,
480 p_imp_line_id => l_trxapi_imp.imp_line_id,
481 p_header_list => l_header_list,
482 p_sql_stmt => l_sql_stmt);
483 retcode := 2;
484 errbuf := l_message;
485 GOTO end_loop;
486 END IF;
487 END IF;
488
489 -- get rollup_period_id
490 IF l_trxapi_imp.rollup_date IS NOT NULL THEN
491 BEGIN
492 SELECT period_id
493 INTO l_rollup_period_id
494 FROM cn_acc_period_statuses_v
495 WHERE l_trxapi_imp.rollup_date BETWEEN start_date AND end_date
496 AND period_status IN ('O','F') and org_id = p_org_id;
497 EXCEPTION
498 WHEN no_data_found THEN
499 l_failed_row := l_failed_row + 1;
500 l_error_code := 'CN_IMP_NF_ROLLUP_DATE';
501 l_message :=
502 fnd_message.get_string('CN','CN_IMP_NF_ROLLUP_DATE');
503 CN_IMPORT_PVT.update_imp_lines
504 (p_imp_line_id => l_trxapi_imp.imp_line_id,
505 p_status_code => 'FAIL',
506 p_error_code => l_error_code,
507 p_error_msg => l_message);
508 CN_IMPORT_PVT.update_imp_headers
509 (p_imp_header_id => p_imp_header_id,
510 p_status_code => 'IMPORT_FAIL',
511 p_failed_row => l_failed_row);
512 cn_message_pkg.write
513 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
514 p_message_type => 'ERROR');
515 CN_IMPORT_PVT.write_error_rec
516 (p_imp_header_id => p_imp_header_id,
517 p_imp_line_id => l_trxapi_imp.imp_line_id,
518 p_header_list => l_header_list,
519 p_sql_stmt => l_sql_stmt);
520
521 retcode := 2;
522 errbuf := l_message;
523 GOTO end_loop;
524 END;
525 END IF;
526
527 -- Check revenue_type exist
528 IF l_trxapi_imp.revenue_type IS NOT NULL THEN
529 l_meaning := NULL;
530 l_meaning := cn_api.get_lkup_meaning
531 ( p_lkup_code => l_trxapi_imp.revenue_type,
532 p_lkup_type => 'REVENUE_TYPE' );
533 IF l_meaning IS NULL THEN
534 l_failed_row := l_failed_row + 1;
535 l_error_code := 'CN_IMP_NF_REV_TYPE';
536 l_message := fnd_message.get_string('CN','CN_IMP_NF_REV_TYPE');
537 CN_IMPORT_PVT.update_imp_lines
538 (p_imp_line_id => l_trxapi_imp.imp_line_id,
539 p_status_code => 'FAIL',
540 p_error_code => l_error_code,
541 p_error_msg => l_message);
542 CN_IMPORT_PVT.update_imp_headers
543 (p_imp_header_id => p_imp_header_id,
544 p_status_code => 'IMPORT_FAIL',
545 p_failed_row => l_failed_row);
546 cn_message_pkg.write
547 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
548 p_message_type => 'ERROR');
549 CN_IMPORT_PVT.write_error_rec
550 (p_imp_header_id => p_imp_header_id,
551 p_imp_line_id => l_trxapi_imp.imp_line_id,
552 p_header_list => l_header_list,
553 p_sql_stmt => l_sql_stmt);
554 retcode := 2;
555 errbuf := l_message;
556 GOTO end_loop;
557 END IF;
558 END IF;
559
560 -- Check reason_code exist
561 IF l_trxapi_imp.reason_code IS NOT NULL THEN
562 l_meaning := NULL;
563 l_meaning := cn_api.get_lkup_meaning
564 ( p_lkup_code => l_trxapi_imp.reason_code,
565 p_lkup_type => 'ADJUSTMENT_REASON' );
566 IF l_meaning IS NULL THEN
567 l_failed_row := l_failed_row + 1;
568 l_error_code := 'CN_IMP_NF_REASON_CODE';
569 l_message := fnd_message.get_string('CN','CN_IMP_NF_REASON_CODE');
570 CN_IMPORT_PVT.update_imp_lines
571 (p_imp_line_id => l_trxapi_imp.imp_line_id,
572 p_status_code => 'FAIL',
573 p_error_code => l_error_code,
574 p_error_msg => l_message);
575 CN_IMPORT_PVT.update_imp_headers
576 (p_imp_header_id => p_imp_header_id,
577 p_status_code => 'IMPORT_FAIL',
578 p_failed_row => l_failed_row);
579 cn_message_pkg.write
580 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
581 p_message_type => 'ERROR');
582 CN_IMPORT_PVT.write_error_rec
583 (p_imp_header_id => p_imp_header_id,
584 p_imp_line_id => l_trxapi_imp.imp_line_id,
585 p_header_list => l_header_list,
586 p_sql_stmt => l_sql_stmt);
587 retcode := 2;
588 errbuf := l_message;
589 GOTO end_loop;
590 END IF;
591 END IF;
592
593 -- Check pre_processed_code exist
594 IF l_trxapi_imp.pre_processed_code IS NOT NULL THEN
595 l_meaning := NULL;
596 l_meaning := cn_api.get_lkup_meaning
597 ( p_lkup_code => l_trxapi_imp.pre_processed_code,
598 p_lkup_type => 'PRE_PROCESSED_CODE' );
599 IF l_meaning IS NULL THEN
600 l_failed_row := l_failed_row + 1;
601 l_error_code := 'CN_IMP_NF_PRE_PROC_CODE';
602 l_message := fnd_message.get_string('CN','CN_IMP_NF_PRE_PRO_CODE');
603 CN_IMPORT_PVT.update_imp_lines
604 (p_imp_line_id => l_trxapi_imp.imp_line_id,
605 p_status_code => 'FAIL',
606 p_error_code => l_error_code,
607 p_error_msg => l_message);
608 CN_IMPORT_PVT.update_imp_headers
609 (p_imp_header_id => p_imp_header_id,
610 p_status_code => 'IMPORT_FAIL',
611 p_failed_row => l_failed_row);
612 cn_message_pkg.write
613 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
614 p_message_type => 'ERROR');
615 CN_IMPORT_PVT.write_error_rec
616 (p_imp_header_id => p_imp_header_id,
617 p_imp_line_id => l_trxapi_imp.imp_line_id,
618 p_header_list => l_header_list,
619 p_sql_stmt => l_sql_stmt);
620 retcode := 2;
621 errbuf := l_message;
622 GOTO end_loop;
623 END IF;
624 END IF;
625
626 -- Check transaction_currency_code exist
627 IF l_trxapi_imp.transaction_currency_code IS NOT NULL THEN
628 l_meaning := NULL;
629 BEGIN
630 SELECT currency_code
631 INTO l_meaning FROM fnd_currencies_vl
632 WHERE currency_code = l_trxapi_imp.transaction_currency_code;
633
634 EXCEPTION
635 WHEN no_data_found THEN
636 l_failed_row := l_failed_row + 1;
637 l_error_code := 'CN_IMP_NF_TRX_CURR_CODE';
638 l_message :=
639 fnd_message.get_string('CN','CN_IMP_NF_TRX_CURR_CODE');
640 CN_IMPORT_PVT.update_imp_lines
641 (p_imp_line_id => l_trxapi_imp.imp_line_id,
642 p_status_code => 'FAIL',
643 p_error_code => l_error_code,
644 p_error_msg => l_message);
645 CN_IMPORT_PVT.update_imp_headers
646 (p_imp_header_id => p_imp_header_id,
647 p_status_code => 'IMPORT_FAIL',
648 p_failed_row => l_failed_row);
649 cn_message_pkg.write
650 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
651 p_message_type => 'ERROR');
652 CN_IMPORT_PVT.write_error_rec
653 (p_imp_header_id => p_imp_header_id,
654 p_imp_line_id => l_trxapi_imp.imp_line_id,
655 p_header_list => l_header_list,
656 p_sql_stmt => l_sql_stmt);
657 retcode := 2;
658 errbuf := l_message;
659 GOTO end_loop;
660 END;
661 ELSE
662 -- set transaction_currency_code to functional curr code
663 l_trxapi_imp.transaction_currency_code := CN_GLOBAL_VAR.get_currency_code(p_org_id);
664 END IF;
665
666
667 if l_trxapi_imp.split_pct is null and upper(l_trxapi_imp.revenue_type) = 'REVENUE' THEN
668 l_trxapi_imp.split_pct := 100;
669 END IF;
670
671
672 -- Checking split pct value is number or not
673 BEGIN
674 if l_trxapi_imp.split_pct is not null then
675 SELECT to_number(l_trxapi_imp.split_pct) into l_temp from dual;
676 end if;
677
678 EXCEPTION
679
680 WHEN others THEN
681
682 l_failed_row := l_failed_row + 1;
683 l_error_code := 'CN_IMP_NOT_VALID_NUMBER';
684 l_message := fnd_message.get_string('CN','CN_IMP_NOT_VALID_NUMBER');
685
686 CN_IMPORT_PVT.update_imp_lines
687 (p_imp_line_id => l_trxapi_imp.imp_line_id,
688 p_status_code => 'FAIL',
689 p_error_code => l_error_code,
690 p_error_msg => l_message);
691
692 CN_IMPORT_PVT.update_imp_headers
693 (p_imp_header_id => p_imp_header_id,
694 p_status_code => 'IMPORT_FAIL',
695 p_failed_row => l_failed_row);
696
697 cn_message_pkg.write
698 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
699 p_message_type => 'ERROR');
700
701 CN_IMPORT_PVT.write_error_rec
702 (p_imp_header_id => p_imp_header_id,
703 p_imp_line_id => l_trxapi_imp.imp_line_id,
704 p_header_list => l_header_list,
705 p_sql_stmt => l_sql_stmt);
706
707 retcode := 2;
708 errbuf := l_message;
709 GOTO end_loop;
710 END;
711
712 -- checking Split Pct -ve condition
713 IF l_trxapi_imp.split_pct is not null AND l_trxapi_imp.split_pct <= 0 THEN
714
715 l_failed_row := l_failed_row + 1;
716 l_error_code := 'CN_IMP_NOT_ZERO_NEG';
717 l_message := fnd_message.get_string('CN','CN_IMP_NOT_ZERO_NEG');
718
719 CN_IMPORT_PVT.update_imp_lines
720 (p_imp_line_id => l_trxapi_imp.imp_line_id,
721 p_status_code => 'FAIL',
722 p_error_code => l_error_code,
723 p_error_msg => l_message);
724
725 CN_IMPORT_PVT.update_imp_headers
726 (p_imp_header_id => p_imp_header_id,
727 p_status_code => 'IMPORT_FAIL',
728 p_failed_row => l_failed_row);
729
730 cn_message_pkg.write
731 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
732 p_message_type => 'ERROR');
733
734 CN_IMPORT_PVT.write_error_rec
735 (p_imp_header_id => p_imp_header_id,
736 p_imp_line_id => l_trxapi_imp.imp_line_id,
737 p_header_list => l_header_list,
738 p_sql_stmt => l_sql_stmt);
739
740 retcode := 2;
741 errbuf := l_message;
742
743 GOTO end_loop;
744
745 END IF;
746
747
748 IF upper(l_trxapi_imp.revenue_type) = 'REVENUE' AND l_trxapi_imp.split_pct > 100 THEN
749
750 l_failed_row := l_failed_row + 1;
751 l_error_code := 'CN_REVENUE_TYPE_SPLIT_PCT_100';
752 l_message := fnd_message.get_string('CN','CN_REVENUE_TYPE_SPLIT_PCT_100');
753
754 CN_IMPORT_PVT.update_imp_lines
755 (p_imp_line_id => l_trxapi_imp.imp_line_id,
756 p_status_code => 'FAIL',
757 p_error_code => l_error_code,
758 p_error_msg => l_message);
759
760 CN_IMPORT_PVT.update_imp_headers
761 (p_imp_header_id => p_imp_header_id,
762 p_status_code => 'IMPORT_FAIL',
763 p_failed_row => l_failed_row);
764
765 cn_message_pkg.write
766 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
767 p_message_type => 'ERROR');
768
769 CN_IMPORT_PVT.write_error_rec
770 (p_imp_header_id => p_imp_header_id,
771 p_imp_line_id => l_trxapi_imp.imp_line_id,
772 p_header_list => l_header_list,
773 p_sql_stmt => l_sql_stmt);
774
775 retcode := 2;
776 errbuf := l_message;
777
778 GOTO end_loop;
779
780 END IF;
781
782 --Added for bug 5559459
783
784 IF l_trxapi_imp.order_number IS NOT NULL THEN
785
786 l_err_flag := 'N';
787 DECLARE
788
789 l_order_num NUMBER ;
790
791 BEGIN
792
793 SELECT to_number(l_trxapi_imp.order_number)
794 INTO l_order_num
795 FROM dual;
796
797 EXCEPTION
798 WHEN OTHERS
799 THEN
800 l_err_flag := 'Y';
801 END;
802
803 IF l_err_flag = 'Y'
804 THEN
805 l_failed_row := l_failed_row + 1;
806 l_error_code := 'CN_ORDER_NUMBER_ALPHANUM';
807 l_message := fnd_message.get_string('CN','CN_ORDER_NUMBER_ALPHANUM');
808
809 CN_IMPORT_PVT.update_imp_lines
810 (p_imp_line_id => l_trxapi_imp.imp_line_id,
811 p_status_code => 'FAIL',
812 p_error_code => l_error_code,
813 p_error_msg => l_message);
814
815 CN_IMPORT_PVT.update_imp_headers
816 (p_imp_header_id => p_imp_header_id,
817 p_status_code => 'IMPORT_FAIL',
818 p_failed_row => l_failed_row);
819
820 cn_message_pkg.write
821 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
822 p_message_type => 'ERROR');
823
824 CN_IMPORT_PVT.write_error_rec
825 (p_imp_header_id => p_imp_header_id,
826 p_imp_line_id => l_trxapi_imp.imp_line_id,
827 p_header_list => l_header_list,
828 p_sql_stmt => l_sql_stmt);
829
830 retcode := 2;
831 errbuf := l_message;
832
833 GOTO end_loop;
834 END IF;
835 END IF;
836
837
838 -- End of addition
839
840 -- insert into cn_comm_lines_api
841 SELECT cn_comm_lines_api_s.NEXTVAL INTO l_comm_lines_api_id FROM dual;
842
843 INSERT INTO cn_comm_lines_api
844 (ORG_ID,
845 comm_lines_api_id,
846 salesrep_id,
847 processed_date,
848 processed_period_id,
849 transaction_amount,
850 trx_type,
851 employee_number,
852 revenue_class_id,
853 quantity,
854 order_number,
855 booked_date,
856 invoice_number,
857 invoice_date,
858 revenue_type,
859 sales_channel,
860 imp_header_id,
861 load_status,
862 created_by,
863 creation_date,
864 last_updated_by,
865 last_update_date,
866 last_update_login,
867 object_version_number,
868 attribute_category,
869 attribute1 ,
870 attribute2 ,
871 attribute3 ,
872 attribute4 ,
873 attribute5 ,
874 attribute6 ,
875 attribute7 ,
876 attribute8 ,
877 attribute9 ,
878 attribute10 ,
879 attribute11 ,
880 attribute12 ,
881 attribute13 ,
882 attribute14 ,
883 attribute15 ,
884 attribute16 ,
885 attribute17 ,
886 attribute18 ,
887 attribute19 ,
888 attribute20 ,
889 attribute21 ,
890 attribute22 ,
891 attribute23 ,
892 attribute24 ,
893 attribute25 ,
894 attribute26 ,
895 attribute27 ,
896 attribute28 ,
897 attribute29 ,
898 attribute30 ,
899 attribute31 ,
900 attribute32 ,
901 attribute33 ,
902 attribute34 ,
903 attribute35 ,
904 attribute36 ,
905 attribute37 ,
906 attribute38 ,
907 attribute39 ,
908 attribute40 ,
909 attribute41 ,
910 attribute42 ,
911 attribute43 ,
912 attribute44 ,
913 attribute45 ,
914 attribute46 ,
915 attribute47 ,
916 attribute48 ,
917 attribute49 ,
918 attribute50 ,
919 attribute51 ,
920 attribute52 ,
921 attribute53 ,
922 attribute54 ,
923 attribute55 ,
924 attribute56 ,
925 attribute57 ,
926 attribute58 ,
927 attribute59 ,
928 attribute60 ,
929 attribute61 ,
930 attribute62 ,
931 attribute63 ,
932 attribute64 ,
933 attribute65 ,
934 attribute66 ,
935 attribute67 ,
936 attribute68 ,
937 attribute69 ,
938 attribute70 ,
939 attribute71 ,
940 attribute72 ,
941 attribute73 ,
942 attribute74 ,
943 attribute75 ,
944 attribute76 ,
945 attribute77 ,
946 attribute78 ,
947 attribute79 ,
948 attribute80 ,
949 attribute81 ,
950 attribute82 ,
951 attribute83 ,
952 attribute84 ,
953 attribute85 ,
954 attribute86 ,
955 attribute87 ,
956 attribute88 ,
957 attribute89 ,
958 attribute90 ,
959 attribute91 ,
960 attribute92 ,
961 attribute93 ,
962 attribute94 ,
963 attribute95 ,
964 attribute96 ,
965 attribute97 ,
966 attribute98 ,
967 attribute99 ,
968 attribute100 ,
969 commission_amount ,
970 exchange_rate ,
971 transaction_currency_code ,
972 discount_percentage ,
973 margin_percentage ,
974 reason_code ,
975 pre_processed_code ,
976 comp_group_id ,
977 quota_id ,
978 role_id ,
979 rollup_date ,
980 rollup_period_id,
981 line_number,
982 split_pct,
983 -- Added new column, bugID 7033617
984 preserve_credit_override_flag,
985 adjust_comments
986 ) VALUES
987 (p_org_id,
988 l_comm_lines_api_id,
989 l_salesrep_id,
990 To_date(l_trxapi_imp.processed_date,'DD/MM/YYYY'),
991 l_period_id,
992 To_number(l_trxapi_imp.transaction_amount),
993 'MAN',
994 l_trxapi_imp.employee_number,
995 l_rev_class_id,
996 To_number(l_trxapi_imp.quantity),
997 To_number(l_trxapi_imp.order_number),
998 To_date(l_trxapi_imp.order_date,'DD/MM/YYYY'),
999 l_trxapi_imp.invoice_number,
1000 To_date(l_trxapi_imp.invoice_date,'DD/MM/YYYY'),
1001 l_trxapi_imp.revenue_type,
1002 l_trxapi_imp.sales_channel,
1003 l_trxapi_imp.imp_header_id,
1004 'UNLOADED',
1005 fnd_global.user_id,
1006 sysdate,
1007 fnd_global.user_id,
1008 sysdate,
1009 fnd_global.login_id,
1010 1,
1011 l_trxapi_imp.attribute_category,
1012 l_trxapi_imp.attribute1 ,
1013 l_trxapi_imp.attribute2 ,
1014 l_trxapi_imp.attribute3 ,
1015 l_trxapi_imp.attribute4 ,
1016 l_trxapi_imp.attribute5 ,
1017 l_trxapi_imp.attribute6 ,
1018 l_trxapi_imp.attribute7 ,
1019 l_trxapi_imp.attribute8 ,
1020 l_trxapi_imp.attribute9 ,
1021 l_trxapi_imp.attribute10 ,
1022 l_trxapi_imp.attribute11 ,
1023 l_trxapi_imp.attribute12 ,
1024 l_trxapi_imp.attribute13 ,
1025 l_trxapi_imp.attribute14 ,
1026 l_trxapi_imp.attribute15 ,
1027 l_trxapi_imp.attribute16 ,
1028 l_trxapi_imp.attribute17 ,
1029 l_trxapi_imp.attribute18 ,
1030 l_trxapi_imp.attribute19 ,
1031 l_trxapi_imp.attribute20 ,
1032 l_trxapi_imp.attribute21 ,
1033 l_trxapi_imp.attribute22 ,
1034 l_trxapi_imp.attribute23 ,
1035 l_trxapi_imp.attribute24 ,
1036 l_trxapi_imp.attribute25 ,
1037 l_trxapi_imp.attribute26 ,
1038 l_trxapi_imp.attribute27 ,
1039 l_trxapi_imp.attribute28 ,
1040 l_trxapi_imp.attribute29 ,
1041 l_trxapi_imp.attribute30 ,
1042 l_trxapi_imp.attribute31 ,
1043 l_trxapi_imp.attribute32 ,
1044 l_trxapi_imp.attribute33 ,
1045 l_trxapi_imp.attribute34 ,
1046 l_trxapi_imp.attribute35 ,
1047 l_trxapi_imp.attribute36 ,
1048 l_trxapi_imp.attribute37 ,
1049 l_trxapi_imp.attribute38 ,
1050 l_trxapi_imp.attribute39 ,
1051 l_trxapi_imp.attribute40 ,
1052 l_trxapi_imp.attribute41 ,
1053 l_trxapi_imp.attribute42 ,
1054 l_trxapi_imp.attribute43 ,
1055 l_trxapi_imp.attribute44 ,
1056 l_trxapi_imp.attribute45 ,
1057 l_trxapi_imp.attribute46 ,
1058 l_trxapi_imp.attribute47 ,
1059 l_trxapi_imp.attribute48 ,
1060 l_trxapi_imp.attribute49 ,
1061 l_trxapi_imp.attribute50 ,
1062 l_trxapi_imp.attribute51 ,
1063 l_trxapi_imp.attribute52 ,
1064 l_trxapi_imp.attribute53 ,
1065 l_trxapi_imp.attribute54 ,
1066 l_trxapi_imp.attribute55 ,
1067 l_trxapi_imp.attribute56 ,
1068 l_trxapi_imp.attribute57 ,
1069 l_trxapi_imp.attribute58 ,
1070 l_trxapi_imp.attribute59 ,
1071 l_trxapi_imp.attribute60 ,
1072 l_trxapi_imp.attribute61 ,
1073 l_trxapi_imp.attribute62 ,
1074 l_trxapi_imp.attribute63 ,
1075 l_trxapi_imp.attribute64 ,
1076 l_trxapi_imp.attribute65 ,
1077 l_trxapi_imp.attribute66 ,
1078 l_trxapi_imp.attribute67 ,
1079 l_trxapi_imp.attribute68 ,
1080 l_trxapi_imp.attribute69 ,
1081 l_trxapi_imp.attribute70 ,
1082 l_trxapi_imp.attribute71 ,
1083 l_trxapi_imp.attribute72 ,
1084 l_trxapi_imp.attribute73 ,
1085 l_trxapi_imp.attribute74 ,
1086 l_trxapi_imp.attribute75 ,
1087 l_trxapi_imp.attribute76 ,
1088 l_trxapi_imp.attribute77 ,
1089 l_trxapi_imp.attribute78 ,
1090 l_trxapi_imp.attribute79 ,
1091 l_trxapi_imp.attribute80 ,
1092 l_trxapi_imp.attribute81 ,
1093 l_trxapi_imp.attribute82 ,
1094 l_trxapi_imp.attribute83 ,
1095 l_trxapi_imp.attribute84 ,
1096 l_trxapi_imp.attribute85 ,
1097 l_trxapi_imp.attribute86 ,
1098 l_trxapi_imp.attribute87 ,
1099 l_trxapi_imp.attribute88 ,
1100 l_trxapi_imp.attribute89 ,
1101 l_trxapi_imp.attribute90 ,
1102 l_trxapi_imp.attribute91 ,
1103 l_trxapi_imp.attribute92 ,
1104 l_trxapi_imp.attribute93 ,
1105 l_trxapi_imp.attribute94 ,
1106 l_trxapi_imp.attribute95 ,
1107 l_trxapi_imp.attribute96 ,
1108 l_trxapi_imp.attribute97 ,
1109 l_trxapi_imp.attribute98 ,
1110 l_trxapi_imp.attribute99 ,
1111 l_trxapi_imp.attribute100 ,
1112 To_number(l_trxapi_imp.commission_amount) ,
1113 To_number(l_trxapi_imp.exchange_rate) ,
1114 l_trxapi_imp.transaction_currency_code ,
1115 To_number(l_trxapi_imp.discount_percentage),
1116 To_number(l_trxapi_imp.margin_percentage),
1117 l_trxapi_imp.reason_code ,
1118 l_trxapi_imp.pre_processed_code ,
1119 l_comp_group_id ,
1120 l_quota_id ,
1121 l_role_id ,
1122 To_date(l_trxapi_imp.rollup_date,'DD/MM/YYYY') ,
1123 l_rollup_period_id,
1124 To_number(l_trxapi_imp.line_number),
1125 To_number(l_trxapi_imp.split_pct),
1126 -- Added new column, bugID 7033617
1127 NVL(l_trxapi_imp.preserve_credit_override_flag,'N'),
1128 l_trxapi_imp.adjust_comments
1129 );
1130
1131 l_error_code := '';
1132 CN_IMPORT_PVT.update_imp_lines
1133 (p_imp_line_id => l_trxapi_imp.imp_line_id,
1134 p_status_code => 'COMPLETE',
1135 p_error_code => l_error_code);
1136
1137 cn_message_pkg.write
1138 (p_message_text => 'TRXAPI:Import completed. comm_lines_api_id = ' || To_char(l_comm_lines_api_id),
1139 p_message_type => 'DEBUG');
1140
1141 << end_loop>>
1142 NULL;
1143
1144 -- update update_imp_headers:process_row
1145 CN_IMPORT_PVT.update_imp_headers
1146 (p_imp_header_id => p_imp_header_id,
1147 p_status_code => NULL,
1148 p_processed_row => l_processed_row);
1149
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 l_failed_row := l_failed_row + 1;
1153 l_error_code := SQLCODE;
1154 l_message := SUBSTR (SQLERRM , 1 , 2000);
1155 CN_IMPORT_PVT.update_imp_lines
1156 (p_imp_line_id => l_trxapi_imp.imp_line_id,
1157 p_status_code => 'FAIL',
1158 p_error_code => l_error_code,
1159 p_error_msg => l_message);
1160 CN_IMPORT_PVT.update_imp_headers
1161 (p_imp_header_id => p_imp_header_id,
1162 p_status_code => 'IMPORT_FAIL',
1163 p_processed_row => l_processed_row,
1164 p_failed_row => l_failed_row);
1165 cn_message_pkg.write
1166 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
1167 p_message_type => 'ERROR');
1168 CN_IMPORT_PVT.write_error_rec
1169 (p_imp_header_id => p_imp_header_id,
1170 p_imp_line_id => l_trxapi_imp.imp_line_id,
1171 p_header_list => l_header_list,
1172 p_sql_stmt => l_sql_stmt);
1173 retcode := 2;
1174 errbuf := l_message;
1175 END;
1176
1177 END LOOP; -- c_trxapi_imp_csr
1178 IF c_trxapi_imp_csr%ROWCOUNT = 0 THEN
1179 l_processed_row := 0;
1180 END IF;
1181 CLOSE c_trxapi_imp_csr;
1182 IF l_failed_row = 0 AND retcode = 0 THEN
1183 -- update update_imp_headers
1184 CN_IMPORT_PVT.update_imp_headers
1185 (p_imp_header_id => p_imp_header_id,
1186 p_status_code => 'COMPLETE',
1187 p_processed_row => l_processed_row,
1188 p_failed_row => l_failed_row);
1189 END IF;
1190
1191 cn_message_pkg.write
1192 (p_message_text => 'TRXAPI: End Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
1193 p_message_type => 'MILESTONE');
1194
1195 -- close process batch
1196 cn_message_pkg.end_batch(l_process_audit_id);
1197
1198 -- Commit all imports
1199 COMMIT;
1200
1201 EXCEPTION
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 retcode := 2 ;
1204 cn_message_pkg.end_batch(l_process_audit_id);
1205 FND_MSG_PUB.count_and_get
1206 (p_count => l_msg_count ,
1207 p_data => errbuf ,
1208 p_encoded => FND_API.G_FALSE
1209 );
1210
1211 WHEN OTHERS THEN
1212 err_num := SQLCODE;
1213 IF err_num = -6501 THEN
1214 retcode := 2 ;
1215 errbuf := fnd_program.message;
1216 ELSE
1217 retcode := 2 ;
1218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1219 THEN
1220 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1221 END IF;
1222 FND_MSG_PUB.count_and_get
1223 (p_count => l_msg_count ,
1224 p_data => errbuf ,
1225 p_encoded => FND_API.G_FALSE
1226 );
1227 END IF;
1228 cn_message_pkg.set_error(l_api_name,errbuf);
1229 cn_message_pkg.end_batch(l_process_audit_id);
1230
1231 END Trxapi_Import;
1232
1233 END CN_IMP_TRXAPI_PVT;