[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_TRXAPI_PVT
Source
1 PACKAGE BODY CN_IMP_TRXAPI_PVT AS
2 -- $Header: cnvimtxb.pls 120.5.12010000.4 2008/11/14 09:16:25 ppillai 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 trim(preserve_credit_override_flag) 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
201 BEGIN
202 retcode := 0 ;
203
204 -- Get imp_header info
205 SELECT name, status_code,server_flag,imp_map_id, source_column_num,
206 import_type_code
207 INTO l_imp_header.name ,l_imp_header.status_code ,
208 l_imp_header.server_flag, l_imp_header.imp_map_id,
209 l_imp_header.source_column_num,l_imp_header.import_type_code
210 FROM cn_imp_headers
211 WHERE imp_header_id = p_imp_header_id;
212
213 -- open process audit batch
214 cn_message_pkg.begin_batch
215 ( x_process_type => l_imp_header.import_type_code,
216 x_parent_proc_audit_id => p_imp_header_id ,
217 x_process_audit_id => l_process_audit_id,
218 x_request_id => null,
219 p_org_id => p_org_id);
220
221 cn_message_pkg.write
222 (p_message_text => 'TRXAPI: Start Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
223 p_message_type => 'MILESTONE');
224
225 -- Get source column name list and target column dynamic sql statement
226 CN_IMPORT_PVT.build_error_rec
227 (p_imp_header_id => p_imp_header_id,
228 x_header_list => l_header_list,
229 x_sql_stmt => l_sql_stmt);
230
231 OPEN c_trxapi_imp_csr;
232 LOOP
233 FETCH c_trxapi_imp_csr INTO l_trxapi_imp;
234 EXIT WHEN c_trxapi_imp_csr%notfound;
235
236 BEGIN
237
238 l_processed_row := l_processed_row + 1;
239 l_rev_class_id := NULL;
240 l_salesrep_id := NULL;
241
242 cn_message_pkg.write
243 (p_message_text => 'TRXAPI:Record ' || To_char(l_processed_row) || ' imp_line_id = ' || To_char(l_trxapi_imp.imp_line_id),
244 p_message_type => 'DEBUG');
245
246 -- -------- Checking for all required fields ----------------- --
247 -- Check required field
248 IF l_trxapi_imp.processed_date IS NULL
249 OR l_trxapi_imp.transaction_amount IS NULL
250 OR l_trxapi_imp.employee_number IS NULL
251 OR l_trxapi_imp.revenue_type IS NULL THEN
252 l_failed_row := l_failed_row + 1;
253 l_error_code := 'CN_IMP_MISS_REQUIRED';
254 l_message := fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED');
255 CN_IMPORT_PVT.update_imp_lines
256 (p_imp_line_id => l_trxapi_imp.imp_line_id,
257 p_status_code => 'FAIL',
258 p_error_code => l_error_code,
259 p_error_msg => l_message);
260 CN_IMPORT_PVT.update_imp_headers
261 (p_imp_header_id => p_imp_header_id,
262 p_status_code => 'IMPORT_FAIL',
263 p_failed_row => l_failed_row);
264 cn_message_pkg.write
265 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
266 p_message_type => 'ERROR');
267 CN_IMPORT_PVT.write_error_rec
268 (p_imp_header_id => p_imp_header_id,
269 p_imp_line_id => l_trxapi_imp.imp_line_id,
270 p_header_list => l_header_list,
271 p_sql_stmt => l_sql_stmt);
272
273 retcode := 2;
274 errbuf := l_message;
275 GOTO end_loop;
276 END IF;
277
278 -- Get salesrep_id
279 BEGIN
280 SELECT salesrep_id
281 INTO l_salesrep_id
282 FROM cn_salesreps
283 WHERE employee_number = l_trxapi_imp.employee_number and org_id = p_org_id;
284 EXCEPTION
285 WHEN no_data_found THEN
286 l_failed_row := l_failed_row + 1;
287 l_error_code := 'CN_IMP_NF_RSRC_ID';
288 l_message := fnd_message.get_string('CN','CN_IMP_NF_RSRC_ID');
289 CN_IMPORT_PVT.update_imp_lines
290 (p_imp_line_id => l_trxapi_imp.imp_line_id,
291 p_status_code => 'FAIL',
292 p_error_code => l_error_code,
293 p_error_msg => l_message);
294 CN_IMPORT_PVT.update_imp_headers
295 (p_imp_header_id => p_imp_header_id,
296 p_status_code => 'IMPORT_FAIL',
297 p_failed_row => l_failed_row);
298 cn_message_pkg.write
299 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
300 p_message_type => 'ERROR');
301 CN_IMPORT_PVT.write_error_rec
302 (p_imp_header_id => p_imp_header_id,
303 p_imp_line_id => l_trxapi_imp.imp_line_id,
304 p_header_list => l_header_list,
305 p_sql_stmt => l_sql_stmt);
306
307 retcode := 2;
308 errbuf := l_message;
309 GOTO end_loop;
310 END;
311
312 -- get process_period_id
313 BEGIN
314 SELECT period_id
315 INTO l_period_id
316 FROM cn_acc_period_statuses_v
317 WHERE l_trxapi_imp.processed_date BETWEEN start_date AND end_date
318 AND period_status IN ('O','F') and org_id = p_org_id;
319 EXCEPTION
320 WHEN no_data_found THEN
321 l_failed_row := l_failed_row + 1;
322 l_error_code := 'NOT_WITHIN_OPEN_PERIODS';
323 l_message := fnd_message.get_string('CN','NOT_WITHIN_OPEN_PERIODS');
324 CN_IMPORT_PVT.update_imp_lines
325 (p_imp_line_id => l_trxapi_imp.imp_line_id,
326 p_status_code => 'FAIL',
327 p_error_code => l_error_code,
328 p_error_msg => l_message);
329 CN_IMPORT_PVT.update_imp_headers
330 (p_imp_header_id => p_imp_header_id,
331 p_status_code => 'IMPORT_FAIL',
332 p_failed_row => l_failed_row);
333 cn_message_pkg.write
334 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
335 p_message_type => 'ERROR');
336 CN_IMPORT_PVT.write_error_rec
337 (p_imp_header_id => p_imp_header_id,
338 p_imp_line_id => l_trxapi_imp.imp_line_id,
339 p_header_list => l_header_list,
340 p_sql_stmt => l_sql_stmt);
341
342 retcode := 2;
343 errbuf := l_message;
344 GOTO end_loop;
345 END;
346
347 -- -------- Checking for all optional fields ----------------- --
348
349 -- Get revenue_class_id when revenue_class_name exists
350 IF l_trxapi_imp.revenue_class_name IS NOT NULL THEN
351 l_rev_class_id := cn_api.get_rev_class_id
352 (p_rev_class_name => l_trxapi_imp.revenue_class_name,
353 p_org_id => p_org_id);
354 IF l_rev_class_id IS NULL THEN
355 l_failed_row := l_failed_row + 1;
356 l_error_code := 'CN_IMP_NF_REVCLS_ID';
357 l_message := fnd_message.get_string('CN','CN_IMP_NF_REVCLS_ID');
358 CN_IMPORT_PVT.update_imp_lines
359 (p_imp_line_id => l_trxapi_imp.imp_line_id,
360 p_status_code => 'FAIL',
361 p_error_code => l_error_code,
362 p_error_msg => l_message);
363 CN_IMPORT_PVT.update_imp_headers
364 (p_imp_header_id => p_imp_header_id,
365 p_status_code => 'IMPORT_FAIL',
366 p_failed_row => l_failed_row);
367 cn_message_pkg.write
368 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
369 p_message_type => 'ERROR');
370 CN_IMPORT_PVT.write_error_rec
371 (p_imp_header_id => p_imp_header_id,
372 p_imp_line_id => l_trxapi_imp.imp_line_id,
373 p_header_list => l_header_list,
374 p_sql_stmt => l_sql_stmt);
375 retcode := 2;
376 errbuf := l_message;
377 GOTO end_loop;
378 END IF;
379 END IF;
380
381 -- Get quota_id from plan_element_name
382 IF l_trxapi_imp.plan_element_name IS NOT NULL THEN
383 BEGIN
384 SELECT quota_id
385 INTO l_quota_id
386 FROM cn_quotas_v
387 WHERE name = l_trxapi_imp.plan_element_name and org_id = p_org_id
388 ;
389 EXCEPTION
390 WHEN no_data_found THEN
391 l_failed_row := l_failed_row + 1;
392 l_error_code := 'CN_IMP_NF_QUOTA';
393 l_message :=
394 fnd_message.get_string('CN','CN_IMP_NF_QUOTA');
395 CN_IMPORT_PVT.update_imp_lines
396 (p_imp_line_id => l_trxapi_imp.imp_line_id,
397 p_status_code => 'FAIL',
398 p_error_code => l_error_code,
399 p_error_msg => l_message);
400 CN_IMPORT_PVT.update_imp_headers
401 (p_imp_header_id => p_imp_header_id,
402 p_status_code => 'IMPORT_FAIL',
403 p_failed_row => l_failed_row);
404 cn_message_pkg.write
405 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
406 p_message_type => 'ERROR');
407 CN_IMPORT_PVT.write_error_rec
408 (p_imp_header_id => p_imp_header_id,
409 p_imp_line_id => l_trxapi_imp.imp_line_id,
410 p_header_list => l_header_list,
411 p_sql_stmt => l_sql_stmt);
412
413 retcode := 2;
414 errbuf := l_message;
415 GOTO end_loop;
416 END;
417 END IF;
418
419 -- Get comp_group_id from compensation_group_name
420 IF l_trxapi_imp.compensation_group_name IS NOT NULL THEN
421 BEGIN
422 SELECT comp_group_id
423 INTO l_comp_group_id
424 FROM cn_comp_groups
425 WHERE name = l_trxapi_imp.compensation_group_name
426 ;
427 EXCEPTION
428 WHEN no_data_found THEN
429 l_failed_row := l_failed_row + 1;
430 l_error_code := 'CN_IMP_NF_CG';
431 l_message :=
432 fnd_message.get_string('CN','CN_IMP_NF_CG');
433 CN_IMPORT_PVT.update_imp_lines
434 (p_imp_line_id => l_trxapi_imp.imp_line_id,
435 p_status_code => 'FAIL',
436 p_error_code => l_error_code,
437 p_error_msg => l_message);
438 CN_IMPORT_PVT.update_imp_headers
439 (p_imp_header_id => p_imp_header_id,
440 p_status_code => 'IMPORT_FAIL',
441 p_failed_row => l_failed_row);
442 cn_message_pkg.write
443 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
444 p_message_type => 'ERROR');
445 CN_IMPORT_PVT.write_error_rec
446 (p_imp_header_id => p_imp_header_id,
447 p_imp_line_id => l_trxapi_imp.imp_line_id,
448 p_header_list => l_header_list,
449 p_sql_stmt => l_sql_stmt);
450
451 retcode := 2;
452 errbuf := l_message;
453 GOTO end_loop;
454 END;
455 END IF;
456
457 -- Get role_id when role_name exists
458 IF l_trxapi_imp.role_name IS NOT NULL THEN
459 l_role_id := cn_api.get_role_id
460 (p_role_name => l_trxapi_imp.role_name);
461 IF l_role_id IS NULL THEN
462 l_failed_row := l_failed_row + 1;
463 l_error_code := 'CN_IMP_NF_ROLE';
464 l_message := fnd_message.get_string('CN','CN_IMP_NF_ROLE');
465 CN_IMPORT_PVT.update_imp_lines
466 (p_imp_line_id => l_trxapi_imp.imp_line_id,
467 p_status_code => 'FAIL',
468 p_error_code => l_error_code,
469 p_error_msg => l_message);
470 CN_IMPORT_PVT.update_imp_headers
471 (p_imp_header_id => p_imp_header_id,
472 p_status_code => 'IMPORT_FAIL',
473 p_failed_row => l_failed_row);
474 cn_message_pkg.write
475 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
476 p_message_type => 'ERROR');
477 CN_IMPORT_PVT.write_error_rec
478 (p_imp_header_id => p_imp_header_id,
479 p_imp_line_id => l_trxapi_imp.imp_line_id,
480 p_header_list => l_header_list,
481 p_sql_stmt => l_sql_stmt);
482 retcode := 2;
483 errbuf := l_message;
484 GOTO end_loop;
485 END IF;
486 END IF;
487
488 -- get rollup_period_id
489 IF l_trxapi_imp.rollup_date IS NOT NULL THEN
490 BEGIN
491 SELECT period_id
492 INTO l_rollup_period_id
493 FROM cn_acc_period_statuses_v
494 WHERE l_trxapi_imp.rollup_date BETWEEN start_date AND end_date
495 AND period_status IN ('O','F') and org_id = p_org_id;
496 EXCEPTION
497 WHEN no_data_found THEN
498 l_failed_row := l_failed_row + 1;
499 l_error_code := 'CN_IMP_NF_ROLLUP_DATE';
500 l_message :=
501 fnd_message.get_string('CN','CN_IMP_NF_ROLLUP_DATE');
502 CN_IMPORT_PVT.update_imp_lines
503 (p_imp_line_id => l_trxapi_imp.imp_line_id,
504 p_status_code => 'FAIL',
505 p_error_code => l_error_code,
506 p_error_msg => l_message);
507 CN_IMPORT_PVT.update_imp_headers
508 (p_imp_header_id => p_imp_header_id,
509 p_status_code => 'IMPORT_FAIL',
510 p_failed_row => l_failed_row);
511 cn_message_pkg.write
512 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
513 p_message_type => 'ERROR');
514 CN_IMPORT_PVT.write_error_rec
515 (p_imp_header_id => p_imp_header_id,
516 p_imp_line_id => l_trxapi_imp.imp_line_id,
517 p_header_list => l_header_list,
518 p_sql_stmt => l_sql_stmt);
519
520 retcode := 2;
521 errbuf := l_message;
522 GOTO end_loop;
523 END;
524 END IF;
525
526 -- Check revenue_type exist
527 IF l_trxapi_imp.revenue_type IS NOT NULL THEN
528 l_meaning := NULL;
529 l_meaning := cn_api.get_lkup_meaning
530 ( p_lkup_code => l_trxapi_imp.revenue_type,
531 p_lkup_type => 'REVENUE_TYPE' );
532 IF l_meaning IS NULL THEN
533 l_failed_row := l_failed_row + 1;
534 l_error_code := 'CN_IMP_NF_REV_TYPE';
535 l_message := fnd_message.get_string('CN','CN_IMP_NF_REV_TYPE');
536 CN_IMPORT_PVT.update_imp_lines
537 (p_imp_line_id => l_trxapi_imp.imp_line_id,
538 p_status_code => 'FAIL',
539 p_error_code => l_error_code,
540 p_error_msg => l_message);
541 CN_IMPORT_PVT.update_imp_headers
542 (p_imp_header_id => p_imp_header_id,
543 p_status_code => 'IMPORT_FAIL',
544 p_failed_row => l_failed_row);
545 cn_message_pkg.write
546 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
547 p_message_type => 'ERROR');
548 CN_IMPORT_PVT.write_error_rec
549 (p_imp_header_id => p_imp_header_id,
550 p_imp_line_id => l_trxapi_imp.imp_line_id,
551 p_header_list => l_header_list,
552 p_sql_stmt => l_sql_stmt);
553 retcode := 2;
554 errbuf := l_message;
555 GOTO end_loop;
556 END IF;
557 END IF;
558
559 -- Check reason_code exist
560 IF l_trxapi_imp.reason_code IS NOT NULL THEN
561 l_meaning := NULL;
562 l_meaning := cn_api.get_lkup_meaning
563 ( p_lkup_code => l_trxapi_imp.reason_code,
564 p_lkup_type => 'ADJUSTMENT_REASON' );
565 IF l_meaning IS NULL THEN
566 l_failed_row := l_failed_row + 1;
567 l_error_code := 'CN_IMP_NF_REASON_CODE';
568 l_message := fnd_message.get_string('CN','CN_IMP_NF_REASON_CODE');
569 CN_IMPORT_PVT.update_imp_lines
570 (p_imp_line_id => l_trxapi_imp.imp_line_id,
571 p_status_code => 'FAIL',
572 p_error_code => l_error_code,
573 p_error_msg => l_message);
574 CN_IMPORT_PVT.update_imp_headers
575 (p_imp_header_id => p_imp_header_id,
576 p_status_code => 'IMPORT_FAIL',
577 p_failed_row => l_failed_row);
578 cn_message_pkg.write
579 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
580 p_message_type => 'ERROR');
581 CN_IMPORT_PVT.write_error_rec
582 (p_imp_header_id => p_imp_header_id,
583 p_imp_line_id => l_trxapi_imp.imp_line_id,
584 p_header_list => l_header_list,
585 p_sql_stmt => l_sql_stmt);
586 retcode := 2;
587 errbuf := l_message;
588 GOTO end_loop;
589 END IF;
590 END IF;
591
592 -- Check pre_processed_code exist
593 IF l_trxapi_imp.pre_processed_code IS NOT NULL THEN
594 l_meaning := NULL;
595 l_meaning := cn_api.get_lkup_meaning
596 ( p_lkup_code => l_trxapi_imp.pre_processed_code,
597 p_lkup_type => 'PRE_PROCESSED_CODE' );
598 IF l_meaning IS NULL THEN
599 l_failed_row := l_failed_row + 1;
600 l_error_code := 'CN_IMP_NF_PRE_PROC_CODE';
601 l_message := fnd_message.get_string('CN','CN_IMP_NF_PRE_PRO_CODE');
602 CN_IMPORT_PVT.update_imp_lines
603 (p_imp_line_id => l_trxapi_imp.imp_line_id,
604 p_status_code => 'FAIL',
605 p_error_code => l_error_code,
606 p_error_msg => l_message);
607 CN_IMPORT_PVT.update_imp_headers
608 (p_imp_header_id => p_imp_header_id,
609 p_status_code => 'IMPORT_FAIL',
610 p_failed_row => l_failed_row);
611 cn_message_pkg.write
612 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
613 p_message_type => 'ERROR');
614 CN_IMPORT_PVT.write_error_rec
615 (p_imp_header_id => p_imp_header_id,
616 p_imp_line_id => l_trxapi_imp.imp_line_id,
617 p_header_list => l_header_list,
618 p_sql_stmt => l_sql_stmt);
619 retcode := 2;
620 errbuf := l_message;
621 GOTO end_loop;
622 END IF;
623 END IF;
624
625 -- Check transaction_currency_code exist
626 IF l_trxapi_imp.transaction_currency_code IS NOT NULL THEN
627 l_meaning := NULL;
628 BEGIN
629 SELECT currency_code
630 INTO l_meaning FROM fnd_currencies_vl
631 WHERE currency_code = l_trxapi_imp.transaction_currency_code;
632
633 EXCEPTION
634 WHEN no_data_found THEN
635 l_failed_row := l_failed_row + 1;
636 l_error_code := 'CN_IMP_NF_TRX_CURR_CODE';
637 l_message :=
638 fnd_message.get_string('CN','CN_IMP_NF_TRX_CURR_CODE');
639 CN_IMPORT_PVT.update_imp_lines
640 (p_imp_line_id => l_trxapi_imp.imp_line_id,
641 p_status_code => 'FAIL',
642 p_error_code => l_error_code,
643 p_error_msg => l_message);
644 CN_IMPORT_PVT.update_imp_headers
645 (p_imp_header_id => p_imp_header_id,
646 p_status_code => 'IMPORT_FAIL',
647 p_failed_row => l_failed_row);
648 cn_message_pkg.write
649 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
650 p_message_type => 'ERROR');
651 CN_IMPORT_PVT.write_error_rec
652 (p_imp_header_id => p_imp_header_id,
653 p_imp_line_id => l_trxapi_imp.imp_line_id,
654 p_header_list => l_header_list,
655 p_sql_stmt => l_sql_stmt);
656 retcode := 2;
657 errbuf := l_message;
658 GOTO end_loop;
659 END;
660 ELSE
661 -- set transaction_currency_code to functional curr code
662 -- Fix Bug 6932901
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 -- insert into cn_comm_lines_api
783 SELECT cn_comm_lines_api_s.NEXTVAL INTO l_comm_lines_api_id FROM dual;
784
785 INSERT INTO cn_comm_lines_api
786 (ORG_ID,
787 comm_lines_api_id,
788 salesrep_id,
789 processed_date,
790 processed_period_id,
791 transaction_amount,
792 trx_type,
793 employee_number,
794 revenue_class_id,
795 quantity,
796 order_number,
797 booked_date,
798 invoice_number,
799 invoice_date,
800 revenue_type,
801 sales_channel,
802 imp_header_id,
803 load_status,
804 created_by,
805 creation_date,
806 last_updated_by,
807 last_update_date,
808 last_update_login,
809 object_version_number,
810 attribute_category,
811 attribute1 ,
812 attribute2 ,
813 attribute3 ,
814 attribute4 ,
815 attribute5 ,
816 attribute6 ,
817 attribute7 ,
818 attribute8 ,
819 attribute9 ,
820 attribute10 ,
821 attribute11 ,
822 attribute12 ,
823 attribute13 ,
824 attribute14 ,
825 attribute15 ,
826 attribute16 ,
827 attribute17 ,
828 attribute18 ,
829 attribute19 ,
830 attribute20 ,
831 attribute21 ,
832 attribute22 ,
833 attribute23 ,
834 attribute24 ,
835 attribute25 ,
836 attribute26 ,
837 attribute27 ,
838 attribute28 ,
839 attribute29 ,
840 attribute30 ,
841 attribute31 ,
842 attribute32 ,
843 attribute33 ,
844 attribute34 ,
845 attribute35 ,
846 attribute36 ,
847 attribute37 ,
848 attribute38 ,
849 attribute39 ,
850 attribute40 ,
851 attribute41 ,
852 attribute42 ,
853 attribute43 ,
854 attribute44 ,
855 attribute45 ,
856 attribute46 ,
857 attribute47 ,
858 attribute48 ,
859 attribute49 ,
860 attribute50 ,
861 attribute51 ,
862 attribute52 ,
863 attribute53 ,
864 attribute54 ,
865 attribute55 ,
866 attribute56 ,
867 attribute57 ,
868 attribute58 ,
869 attribute59 ,
870 attribute60 ,
871 attribute61 ,
872 attribute62 ,
873 attribute63 ,
874 attribute64 ,
875 attribute65 ,
876 attribute66 ,
877 attribute67 ,
878 attribute68 ,
879 attribute69 ,
880 attribute70 ,
881 attribute71 ,
882 attribute72 ,
883 attribute73 ,
884 attribute74 ,
885 attribute75 ,
886 attribute76 ,
887 attribute77 ,
888 attribute78 ,
889 attribute79 ,
890 attribute80 ,
891 attribute81 ,
892 attribute82 ,
893 attribute83 ,
894 attribute84 ,
895 attribute85 ,
896 attribute86 ,
897 attribute87 ,
898 attribute88 ,
899 attribute89 ,
900 attribute90 ,
901 attribute91 ,
902 attribute92 ,
903 attribute93 ,
904 attribute94 ,
905 attribute95 ,
906 attribute96 ,
907 attribute97 ,
908 attribute98 ,
909 attribute99 ,
910 attribute100 ,
911 commission_amount ,
912 exchange_rate ,
913 transaction_currency_code ,
914 discount_percentage ,
915 margin_percentage ,
916 reason_code ,
917 pre_processed_code ,
918 comp_group_id ,
919 quota_id ,
920 role_id ,
921 rollup_date ,
922 rollup_period_id,
923 line_number,
924 split_pct,
925 -- Added new column, bugID 7033617
926 preserve_credit_override_flag,
927 adjust_comments
928 ) VALUES
929 (p_org_id,
930 l_comm_lines_api_id,
931 l_salesrep_id,
932 To_date(l_trxapi_imp.processed_date,'DD/MM/YYYY'),
933 l_period_id,
934 To_number(l_trxapi_imp.transaction_amount),
935 'MAN',
936 l_trxapi_imp.employee_number,
937 l_rev_class_id,
938 To_number(l_trxapi_imp.quantity),
939 To_number(l_trxapi_imp.order_number),
940 To_date(l_trxapi_imp.order_date,'DD/MM/YYYY'),
941 l_trxapi_imp.invoice_number,
942 To_date(l_trxapi_imp.invoice_date,'DD/MM/YYYY'),
943 l_trxapi_imp.revenue_type,
944 l_trxapi_imp.sales_channel,
945 l_trxapi_imp.imp_header_id,
946 'UNLOADED',
947 fnd_global.user_id,
948 sysdate,
949 fnd_global.user_id,
950 sysdate,
951 fnd_global.login_id,
952 1,
953 l_trxapi_imp.attribute_category,
954 l_trxapi_imp.attribute1 ,
955 l_trxapi_imp.attribute2 ,
956 l_trxapi_imp.attribute3 ,
957 l_trxapi_imp.attribute4 ,
958 l_trxapi_imp.attribute5 ,
959 l_trxapi_imp.attribute6 ,
960 l_trxapi_imp.attribute7 ,
961 l_trxapi_imp.attribute8 ,
962 l_trxapi_imp.attribute9 ,
963 l_trxapi_imp.attribute10 ,
964 l_trxapi_imp.attribute11 ,
965 l_trxapi_imp.attribute12 ,
966 l_trxapi_imp.attribute13 ,
967 l_trxapi_imp.attribute14 ,
968 l_trxapi_imp.attribute15 ,
969 l_trxapi_imp.attribute16 ,
970 l_trxapi_imp.attribute17 ,
971 l_trxapi_imp.attribute18 ,
972 l_trxapi_imp.attribute19 ,
973 l_trxapi_imp.attribute20 ,
974 l_trxapi_imp.attribute21 ,
975 l_trxapi_imp.attribute22 ,
976 l_trxapi_imp.attribute23 ,
977 l_trxapi_imp.attribute24 ,
978 l_trxapi_imp.attribute25 ,
979 l_trxapi_imp.attribute26 ,
980 l_trxapi_imp.attribute27 ,
981 l_trxapi_imp.attribute28 ,
982 l_trxapi_imp.attribute29 ,
983 l_trxapi_imp.attribute30 ,
984 l_trxapi_imp.attribute31 ,
985 l_trxapi_imp.attribute32 ,
986 l_trxapi_imp.attribute33 ,
987 l_trxapi_imp.attribute34 ,
988 l_trxapi_imp.attribute35 ,
989 l_trxapi_imp.attribute36 ,
990 l_trxapi_imp.attribute37 ,
991 l_trxapi_imp.attribute38 ,
992 l_trxapi_imp.attribute39 ,
993 l_trxapi_imp.attribute40 ,
994 l_trxapi_imp.attribute41 ,
995 l_trxapi_imp.attribute42 ,
996 l_trxapi_imp.attribute43 ,
997 l_trxapi_imp.attribute44 ,
998 l_trxapi_imp.attribute45 ,
999 l_trxapi_imp.attribute46 ,
1000 l_trxapi_imp.attribute47 ,
1001 l_trxapi_imp.attribute48 ,
1002 l_trxapi_imp.attribute49 ,
1003 l_trxapi_imp.attribute50 ,
1004 l_trxapi_imp.attribute51 ,
1005 l_trxapi_imp.attribute52 ,
1006 l_trxapi_imp.attribute53 ,
1007 l_trxapi_imp.attribute54 ,
1008 l_trxapi_imp.attribute55 ,
1009 l_trxapi_imp.attribute56 ,
1010 l_trxapi_imp.attribute57 ,
1011 l_trxapi_imp.attribute58 ,
1012 l_trxapi_imp.attribute59 ,
1013 l_trxapi_imp.attribute60 ,
1014 l_trxapi_imp.attribute61 ,
1015 l_trxapi_imp.attribute62 ,
1016 l_trxapi_imp.attribute63 ,
1017 l_trxapi_imp.attribute64 ,
1018 l_trxapi_imp.attribute65 ,
1019 l_trxapi_imp.attribute66 ,
1020 l_trxapi_imp.attribute67 ,
1021 l_trxapi_imp.attribute68 ,
1022 l_trxapi_imp.attribute69 ,
1023 l_trxapi_imp.attribute70 ,
1024 l_trxapi_imp.attribute71 ,
1025 l_trxapi_imp.attribute72 ,
1026 l_trxapi_imp.attribute73 ,
1027 l_trxapi_imp.attribute74 ,
1028 l_trxapi_imp.attribute75 ,
1029 l_trxapi_imp.attribute76 ,
1030 l_trxapi_imp.attribute77 ,
1031 l_trxapi_imp.attribute78 ,
1032 l_trxapi_imp.attribute79 ,
1033 l_trxapi_imp.attribute80 ,
1034 l_trxapi_imp.attribute81 ,
1035 l_trxapi_imp.attribute82 ,
1036 l_trxapi_imp.attribute83 ,
1037 l_trxapi_imp.attribute84 ,
1038 l_trxapi_imp.attribute85 ,
1039 l_trxapi_imp.attribute86 ,
1040 l_trxapi_imp.attribute87 ,
1041 l_trxapi_imp.attribute88 ,
1042 l_trxapi_imp.attribute89 ,
1043 l_trxapi_imp.attribute90 ,
1044 l_trxapi_imp.attribute91 ,
1045 l_trxapi_imp.attribute92 ,
1046 l_trxapi_imp.attribute93 ,
1047 l_trxapi_imp.attribute94 ,
1048 l_trxapi_imp.attribute95 ,
1049 l_trxapi_imp.attribute96 ,
1050 l_trxapi_imp.attribute97 ,
1051 l_trxapi_imp.attribute98 ,
1052 l_trxapi_imp.attribute99 ,
1053 l_trxapi_imp.attribute100 ,
1054 To_number(l_trxapi_imp.commission_amount) ,
1055 To_number(l_trxapi_imp.exchange_rate) ,
1056 l_trxapi_imp.transaction_currency_code ,
1057 To_number(l_trxapi_imp.discount_percentage),
1058 To_number(l_trxapi_imp.margin_percentage),
1059 l_trxapi_imp.reason_code ,
1060 l_trxapi_imp.pre_processed_code ,
1061 l_comp_group_id ,
1062 l_quota_id ,
1063 l_role_id ,
1064 To_date(l_trxapi_imp.rollup_date,'DD/MM/YYYY') ,
1065 l_rollup_period_id,
1066 To_number(l_trxapi_imp.line_number),
1067 To_number(l_trxapi_imp.split_pct),
1068 -- Added new column, bugID 7033617
1069 l_trxapi_imp.preserve_credit_override_flag,
1070 l_trxapi_imp.adjust_comments
1071 );
1072
1073 l_error_code := '';
1074 CN_IMPORT_PVT.update_imp_lines
1075 (p_imp_line_id => l_trxapi_imp.imp_line_id,
1076 p_status_code => 'COMPLETE',
1077 p_error_code => l_error_code);
1078
1079 cn_message_pkg.write
1080 (p_message_text => 'TRXAPI:Import completed. comm_lines_api_id = ' || To_char(l_comm_lines_api_id),
1081 p_message_type => 'DEBUG');
1082
1083 << end_loop>>
1084 NULL;
1085
1086 -- update update_imp_headers:process_row
1087 CN_IMPORT_PVT.update_imp_headers
1088 (p_imp_header_id => p_imp_header_id,
1089 p_status_code => NULL,
1090 p_processed_row => l_processed_row);
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 l_failed_row := l_failed_row + 1;
1095 l_error_code := SQLCODE;
1096 l_message := SUBSTR (SQLERRM , 1 , 2000);
1097 CN_IMPORT_PVT.update_imp_lines
1098 (p_imp_line_id => l_trxapi_imp.imp_line_id,
1099 p_status_code => 'FAIL',
1100 p_error_code => l_error_code,
1101 p_error_msg => l_message);
1102 CN_IMPORT_PVT.update_imp_headers
1103 (p_imp_header_id => p_imp_header_id,
1104 p_status_code => 'IMPORT_FAIL',
1105 p_processed_row => l_processed_row,
1106 p_failed_row => l_failed_row);
1107 cn_message_pkg.write
1108 (p_message_text => 'Record ' || To_char(l_processed_row) || ':' || l_message,
1109 p_message_type => 'ERROR');
1110 CN_IMPORT_PVT.write_error_rec
1111 (p_imp_header_id => p_imp_header_id,
1112 p_imp_line_id => l_trxapi_imp.imp_line_id,
1113 p_header_list => l_header_list,
1114 p_sql_stmt => l_sql_stmt);
1115 retcode := 2;
1116 errbuf := l_message;
1117 END;
1118
1119 END LOOP; -- c_trxapi_imp_csr
1120 IF c_trxapi_imp_csr%ROWCOUNT = 0 THEN
1121 l_processed_row := 0;
1122 END IF;
1123 CLOSE c_trxapi_imp_csr;
1124 IF l_failed_row = 0 AND retcode = 0 THEN
1125 -- update update_imp_headers
1126 CN_IMPORT_PVT.update_imp_headers
1127 (p_imp_header_id => p_imp_header_id,
1128 p_status_code => 'COMPLETE',
1129 p_processed_row => l_processed_row,
1130 p_failed_row => l_failed_row);
1131 END IF;
1132
1133 cn_message_pkg.write
1134 (p_message_text => 'TRXAPI: End Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
1135 p_message_type => 'MILESTONE');
1136
1137 -- close process batch
1138 cn_message_pkg.end_batch(l_process_audit_id);
1139
1140 -- Commit all imports
1141 COMMIT;
1142
1143 EXCEPTION
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 retcode := 2 ;
1146 cn_message_pkg.end_batch(l_process_audit_id);
1147 FND_MSG_PUB.count_and_get
1148 (p_count => l_msg_count ,
1149 p_data => errbuf ,
1150 p_encoded => FND_API.G_FALSE
1151 );
1152
1153 WHEN OTHERS THEN
1154 err_num := SQLCODE;
1155 IF err_num = -6501 THEN
1156 retcode := 2 ;
1157 errbuf := fnd_program.message;
1158 ELSE
1159 retcode := 2 ;
1160 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1161 THEN
1162 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1163 END IF;
1164 FND_MSG_PUB.count_and_get
1165 (p_count => l_msg_count ,
1166 p_data => errbuf ,
1167 p_encoded => FND_API.G_FALSE
1168 );
1169 END IF;
1170 cn_message_pkg.set_error(l_api_name,errbuf);
1171 cn_message_pkg.end_batch(l_process_audit_id);
1172
1173 END Trxapi_Import;
1174
1175 END CN_IMP_TRXAPI_PVT;