[Home] [Help]
PACKAGE BODY: APPS.CSE_IPA_TRANS_PKG
Source
1 PACKAGE BODY cse_ipa_trans_pkg AS
2 /* $Header: CSEIPATB.pls 120.9 2006/06/09 00:45:25 brmanesh noship $ */
3
4 l_debug varchar2(1) := nvl(fnd_profile.value('cse_debug_option'),'N');
5
6 PROCEDURE debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 IF l_debug = 'Y' THEN
11 cse_debug_pub.add(p_message);
12 END IF;
13 EXCEPTION
14 WHEN others THEN
15 null;
16 END debug;
17
18 PROCEDURE populate_pa_interface(
19 p_nl_pa_interface_tbl IN nl_pa_interface_tbl_type,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_error_message OUT NOCOPY VARCHAR2)
22 IS
23 l_error_message VARCHAR2(2000);
24 BEGIN
25
26 x_return_status := fnd_api.g_ret_sts_success;
27 x_error_message := null;
28
29 IF NOT p_nl_pa_interface_tbl.COUNT = 0 THEN
30 FOR i IN p_nl_pa_interface_tbl.FIRST .. p_nl_pa_interface_tbl.LAST
31 LOOP
32 INSERT INTO pa_transaction_interface_all(
33 transaction_source,
34 batch_name,
35 expenditure_ending_date,
36 employee_number,
37 organization_name,
38 expenditure_item_date,
39 project_number,
40 task_number,
41 expenditure_type,
42 non_labor_resource,
43 non_labor_resource_org_name,
44 quantity,
45 raw_cost,
46 expenditure_comment,
47 transaction_status_code,
48 transaction_rejection_code,
49 expenditure_id,
50 orig_transaction_reference,
51 attribute_category,
52 attribute1,
53 attribute2,
54 attribute3,
55 attribute4,
56 attribute5,
57 attribute6,
58 attribute7,
59 attribute8,
60 attribute9,
61 attribute10,
62 raw_cost_rate,
63 interface_id,
64 unmatched_negative_txn_flag,
65 expenditure_item_id,
66 org_id,
67 dr_code_combination_id,
68 cr_code_combination_id,
69 cdl_system_reference1,
70 cdl_system_reference2,
71 cdl_system_reference3,
72 cdl_system_reference4,
73 cdl_system_reference5,
74 gl_date,
75 burdened_cost,
76 burdened_cost_rate,
77 system_linkage,
78 txn_interface_id,
79 user_transaction_source,
80 created_by,
81 creation_date,
82 last_updated_by,
83 last_update_date,
84 receipt_currency_amount,
85 receipt_currency_code,
86 receipt_exchange_rate,
87 denom_currency_code,
88 denom_raw_cost,
89 denom_burdened_cost,
90 acct_rate_date,
91 acct_rate_type,
92 acct_exchange_rate,
93 acct_raw_cost,
94 acct_burdened_cost,
95 acct_exchange_rounding_limit,
96 project_currency_code,
97 project_rate_date,
98 project_rate_type,
99 project_exchange_rate,
100 orig_exp_txn_reference1,
101 orig_exp_txn_reference2,
102 orig_exp_txn_reference3,
103 orig_user_exp_txn_reference,
104 vendor_number,
105 override_to_organization_name,
106 reversed_orig_txn_reference,
107 billable_flag,
108 person_business_group_name,
109 net_zero_adjustment_flag,
110 adjusted_expenditure_item_id,
111 organization_id,
112 inventory_item_id,
113 po_number,
114 po_header_id,
115 po_line_num,
116 po_line_id,
117 vendor_id,
118 project_id,
119 task_id,
120 document_type,
121 document_distribution_type)
122 VALUES(
123 p_nl_pa_interface_tbl(i).transaction_source,
124 p_nl_pa_interface_tbl(i).batch_name,
125 p_nl_pa_interface_tbl(i).expenditure_ending_date,
126 p_nl_pa_interface_tbl(i).employee_number,
127 p_nl_pa_interface_tbl(i).organization_name,
128 p_nl_pa_interface_tbl(i).expenditure_item_date,
129 p_nl_pa_interface_tbl(i).project_number,
130 p_nl_pa_interface_tbl(i).task_number,
131 p_nl_pa_interface_tbl(i).expenditure_type,
132 p_nl_pa_interface_tbl(i).non_labor_resource,
133 p_nl_pa_interface_tbl(i).non_labor_resource_org_name,
134 p_nl_pa_interface_tbl(i).quantity,
135 p_nl_pa_interface_tbl(i).raw_cost,
136 p_nl_pa_interface_tbl(i).expenditure_comment,
137 p_nl_pa_interface_tbl(i).transaction_status_code,
138 p_nl_pa_interface_tbl(i).transaction_rejection_code,
139 p_nl_pa_interface_tbl(i).expenditure_id,
140 p_nl_pa_interface_tbl(i).orig_transaction_reference,
141 p_nl_pa_interface_tbl(i).attribute_category,
142 p_nl_pa_interface_tbl(i).attribute1,
143 p_nl_pa_interface_tbl(i).attribute2,
144 p_nl_pa_interface_tbl(i).attribute3,
145 p_nl_pa_interface_tbl(i).attribute4,
146 p_nl_pa_interface_tbl(i).attribute5,
147 p_nl_pa_interface_tbl(i).attribute6,
148 p_nl_pa_interface_tbl(i).attribute7,
149 p_nl_pa_interface_tbl(i).attribute8,
150 p_nl_pa_interface_tbl(i).attribute9,
151 p_nl_pa_interface_tbl(i).attribute10,
152 p_nl_pa_interface_tbl(i).raw_cost_rate,
153 p_nl_pa_interface_tbl(i).interface_id,
154 p_nl_pa_interface_tbl(i).unmatched_negative_txn_flag,
155 p_nl_pa_interface_tbl(i).expenditure_item_id,
156 p_nl_pa_interface_tbl(i).org_id,
157 p_nl_pa_interface_tbl(i).dr_code_combination_id,
158 p_nl_pa_interface_tbl(i).cr_code_combination_id,
159 p_nl_pa_interface_tbl(i).cdl_system_reference1,
160 p_nl_pa_interface_tbl(i).cdl_system_reference2,
161 p_nl_pa_interface_tbl(i).cdl_system_reference3,
162 p_nl_pa_interface_tbl(i).cdl_system_reference4,
163 p_nl_pa_interface_tbl(i).cdl_system_reference5,
164 p_nl_pa_interface_tbl(i).gl_date,
165 p_nl_pa_interface_tbl(i).burdened_cost,
166 p_nl_pa_interface_tbl(i).burdened_cost_rate,
167 p_nl_pa_interface_tbl(i).system_linkage,
168 p_nl_pa_interface_tbl(i).txn_interface_id,
169 p_nl_pa_interface_tbl(i).user_transaction_source,
170 p_nl_pa_interface_tbl(i).created_by,
171 p_nl_pa_interface_tbl(i).creation_date,
172 p_nl_pa_interface_tbl(i).last_updated_by,
173 p_nl_pa_interface_tbl(i).last_update_date,
174 p_nl_pa_interface_tbl(i).receipt_currency_amount,
175 p_nl_pa_interface_tbl(i).receipt_currency_code,
176 p_nl_pa_interface_tbl(i).receipt_exchange_rate,
177 p_nl_pa_interface_tbl(i).denom_currency_code,
178 p_nl_pa_interface_tbl(i).denom_raw_cost,
179 p_nl_pa_interface_tbl(i).denom_burdened_cost,
180 p_nl_pa_interface_tbl(i).acct_rate_date,
181 p_nl_pa_interface_tbl(i).acct_rate_type,
182 p_nl_pa_interface_tbl(i).acct_exchange_rate,
183 p_nl_pa_interface_tbl(i).acct_raw_cost,
184 p_nl_pa_interface_tbl(i).acct_burdened_cost,
185 p_nl_pa_interface_tbl(i).acct_exchange_rounding_limit,
186 p_nl_pa_interface_tbl(i).project_currency_code,
187 p_nl_pa_interface_tbl(i).project_rate_date,
188 p_nl_pa_interface_tbl(i).project_rate_type,
189 p_nl_pa_interface_tbl(i).project_exchange_rate,
190 p_nl_pa_interface_tbl(i).orig_exp_txn_reference1,
191 p_nl_pa_interface_tbl(i).orig_exp_txn_reference2,
192 p_nl_pa_interface_tbl(i).orig_exp_txn_reference3,
193 p_nl_pa_interface_tbl(i).orig_user_exp_txn_reference,
194 p_nl_pa_interface_tbl(i).vendor_number,
195 p_nl_pa_interface_tbl(i).override_to_organization_name,
196 p_nl_pa_interface_tbl(i).reversed_orig_txn_reference,
197 p_nl_pa_interface_tbl(i).billable_flag,
198 p_nl_pa_interface_tbl(i).person_business_group_name,
199 p_nl_pa_interface_tbl(i).net_zero_adjustment_flag,
200 p_nl_pa_interface_tbl(i).adjusted_expenditure_item_id,
201 p_nl_pa_interface_tbl(i).organization_id,
202 p_nl_pa_interface_tbl(i).inventory_item_id,
203 p_nl_pa_interface_tbl(i).po_number,
204 p_nl_pa_interface_tbl(i).po_header_id,
205 p_nl_pa_interface_tbl(i).po_line_num,
206 p_nl_pa_interface_tbl(i).po_line_id,
207 p_nl_pa_interface_tbl(i).vendor_id,
208 p_nl_pa_interface_tbl(i).project_id,
209 p_nl_pa_interface_tbl(i).task_id,
210 p_nl_pa_interface_tbl(i).document_type,
211 p_nl_pa_interface_tbl(i).document_distribution_type);
212
213 END LOOP;
214 END IF;
215
216 EXCEPTION
217 WHEN others THEN
218 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
219 fnd_message.set_token('ERR_MSG', sqlerrm);
220 l_error_message := fnd_message.get;
221 x_error_message := l_error_message;
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223 END populate_pa_interface;
224
225 PROCEDURE get_fa_asset_category (
226 p_item_id IN NUMBER,
227 p_inv_master_org_id IN NUMBER,
228 p_transaction_id IN NUMBER,
229 x_asset_category_id OUT NOCOPY NUMBER,
230 x_asset_category OUT NOCOPY VARCHAR2,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_error_message OUT NOCOPY VARCHAR2)
233 IS
234 l_asset_category_id number;
235 l_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec ;
236 l_con_asset_category varchar2(150);
237 l_asset_category varchar2(150);
238 l_cat_book_assigned varchar2(1);
239 l_book_type_code varchar2(80);
240 l_hook_used number;
241
242 l_return_status varchar2(1);
243 l_error_message varchar2(2000);
244
245
246 BEGIN
247
248 x_return_status := fnd_api.g_ret_sts_success;
249 x_error_message := null;
250 x_asset_category_id := null;
251
252 l_asset_attrib_rec.transaction_id := p_transaction_id ;
253 l_asset_attrib_rec.inventory_item_id := p_item_id ;
254
255 cse_asset_client_ext_stub.get_asset_category(
256 p_asset_attrib_rec => l_asset_attrib_rec,
257 x_hook_used => l_hook_used,
258 x_error_msg => l_error_message);
259
260 l_asset_category_id := l_asset_attrib_rec.asset_category_id ;
261
262 IF l_hook_used <> 1 THEN
263
264 SELECT asset_category_id
265 INTO l_asset_category_id
266 FROM mtl_system_items
267 WHERE inventory_item_id = p_item_id
268 AND organization_id = p_inv_master_org_id;
269
270 END IF;
271
272 IF l_asset_category_id is null THEN
273 fnd_message.set_name('CSE', 'CSE_ASSET_CAT_ERROR');
274 fnd_msg_pub.add;
275 RAISE fnd_api.g_exc_error ;
276 END IF;
277
278 debug(' asset_category_id : '||l_asset_category_id);
279
280 cse_util_pkg.get_concat_segments(
281 p_short_name => 'OFA',
282 p_flex_code => 'CAT#',
283 p_combination_id => l_asset_category_id,
284 x_concat_segments => l_con_asset_category,
285 x_return_status => l_return_status,
286 x_error_message => l_error_message);
287
288 IF l_return_status <> fnd_api.g_ret_sts_success THEN
289 RAISE fnd_api.g_exc_error ;
290 END IF;
291
292 debug(' asset_category : '||l_con_asset_category);
293
294 l_book_type_code := fnd_profile.value('CSE_FA_BOOK_TYPE_CODE');
295
296 debug(' book_type_code : '||l_book_type_code);
297
298 BEGIN
299 SELECT 'Y' INTO l_cat_book_assigned
300 FROM sys.dual
301 WHERE EXISTS (
302 SELECT 1 FROM fa_category_books
303 WHERE category_id = l_asset_category_id
304 AND book_type_code = l_book_type_code);
305 EXCEPTION
306 WHEN no_data_found THEN
307 fnd_message.set_name('CSE', 'CSE_ASSET_BOOK_CAT_UNDEFINED');
308 fnd_message.set_token('ASSET_CAT', l_con_asset_category);
309 fnd_message.set_token('BOOK_TYPE_CODE', l_book_type_code);
310 fnd_msg_pub.add;
311 END;
312
313 -- removes the delimiter
314 cse_util_pkg.get_combine_segments(
315 p_short_name => 'OFA',
316 p_flex_code => 'CAT#',
317 p_concat_segments => l_con_asset_category,
318 x_combine_segments => l_asset_category,
319 x_return_status => l_return_status,
320 x_error_message => l_error_message);
321
322 IF l_return_status <> fnd_api.g_ret_sts_success THEN
323 RAISE fnd_api.g_exc_error ;
324 END IF;
325
326 x_asset_category_id := l_asset_category_id;
327 x_asset_category := l_asset_category;
328
329 EXCEPTION
330
331 WHEN fnd_api.g_exc_error THEN
332 x_error_message := nvl(l_error_message, cse_util_pkg.dump_error_stack);
333 x_return_status := l_return_status;
334
335 WHEN OTHERS THEN
336 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
337 fnd_message.set_token('ERR_MSG', sqlerrm);
338 fnd_msg_pub.add;
339 x_error_message := cse_util_pkg.dump_error_stack;
340 x_return_status := fnd_api.g_ret_sts_unexp_error;
341 END get_fa_asset_category;
342
343 PROCEDURE get_fa_location_segment (
344 p_fa_location_id IN NUMBER,
345 p_transaction_id IN NUMBER,
346 x_fa_location OUT NOCOPY VARCHAR2,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_error_message OUT NOCOPY VARCHAR2)
349 IS
350 l_Hook_Used NUMBER;
351 l_con_fa_location VARCHAR2(150);
352 l_return_status VARCHAR2(1);
353 l_Error_Message VARCHAR2(2000);
354 l_location_id NUMBER;
355 asset_loc_exp EXCEPTION;
356 l_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec ;
357 BEGIN
358
359 x_return_status := fnd_api.g_ret_sts_success;
360 x_error_message := null;
361
362 l_location_id := NULL;
363 l_asset_attrib_rec.transaction_id := p_transaction_id ;
364
365 --call client extension
366 cse_asset_client_ext_stub.get_asset_location(
367 p_asset_attrib_rec => l_asset_attrib_rec,
368 x_hook_used => l_hook_used,
369 x_error_msg => l_error_message ) ;
370
371 l_location_id := l_asset_attrib_rec.location_id ;
372
373 IF l_hook_used <> 1 THEN
374 l_location_id:=p_fa_location_id ;
375 END IF;
376
377 --get the concatenated segments from fa_location_id
378 cse_util_pkg.get_concat_segments(
379 p_short_name => 'OFA',
380 p_flex_code => 'LOC#',
381 p_combination_id => l_location_id,
382 X_concat_segments => l_con_fa_location,
383 x_return_status => l_return_status,
384 x_error_message => l_error_message);
385
386 IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
387 RAISE asset_loc_exp;
388 END IF;
389
390 debug(' location_segment : '||l_con_fa_location);
391
392 -- remove the delimeter
393 CSE_UTIL_PKG.get_combine_segments(
394 p_short_name => 'OFA',
395 p_flex_code => 'LOC#',
396 p_concat_segments => l_con_fa_location,
397 x_combine_segments=> x_fa_location,
398 x_return_status => l_return_status,
399 x_error_message => l_error_message);
400
401 IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
402 RAISE asset_loc_exp;
403 END IF;
404
405 EXCEPTION
406 WHEN asset_loc_exp THEN
407 x_error_message := l_error_message;
408 x_return_status := l_return_status;
409 WHEN OTHERS THEN
410 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
411 fnd_message.set_token('ERR_MSG', sqlerrm);
412 l_error_message := fnd_message.get;
413 x_error_message :=l_error_message;
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 END get_fa_location_segment;
416
417 PROCEDURE get_product_name (
418 p_project_id IN NUMBER,
419 p_transaction_id IN NUMBER,
420 x_product_name OUT NOCOPY VARCHAR2,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_error_message OUT NOCOPY VARCHAR2)
423 IS
424 CURSOR Product_Cur IS
425 SELECT class_code
426 FROM pa_project_classes
427 WHERE project_id = p_project_id
428 AND class_category = 'Product';
429
430 l_product_name varchar2(150);
431 l_hook_used number;
432 l_return_status varchar2(1);
433 l_error_message varchar2(2000);
434 l_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec ;
435
436 BEGIN
437
438 x_return_status := fnd_api.g_ret_sts_success;
439 x_error_message := null;
440 l_product_name := null;
441
442 l_asset_attrib_rec.transaction_id := p_transaction_id ;
443
444 cse_asset_client_ext_stub.get_product_code(
445 p_asset_attrib_rec => l_asset_attrib_rec,
446 x_product_code => l_product_name,
447 x_hook_used => l_hook_used,
448 x_error_msg => l_error_message ) ;
449
450 IF l_hook_used <> 1 THEN
451 OPEN product_cur;
452 FETCH product_cur INTO l_product_name;
453 CLOSE product_cur;
454 END IF;
455
456 debug(' product_name : '||l_product_name);
457
458 x_product_name := l_product_name;
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
463 fnd_message.set_token('ERR_MSG', sqlerrm);
464 l_error_message := fnd_message.get;
465 x_error_message :=l_error_message;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 END get_product_name;
468
469 PROCEDURE get_grouping_attribute(
470 p_item_id IN NUMBER,
471 p_organization_id IN NUMBER,
472 p_project_id IN NUMBER,
473 p_fa_location_id IN NUMBER,
474 p_transaction_id IN NUMBER,
475 p_org_id IN NUMBER,
476 x_attribute8 OUT NOCOPY VARCHAR2,
477 x_attribute9 OUT NOCOPY VARCHAR2,
478 x_attribute10 OUT NOCOPY VARCHAR2,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_error_message OUT NOCOPY VARCHAR2)
481 IS
482
483 l_asset_category_id NUMBER;
484 l_asset_category VARCHAR2(150);
485 l_fa_location VARCHAR2(150);
486 l_grp_asset_location VARCHAR2(150);
487 l_grp_asset_category VARCHAR2(150);
488 l_product_name VARCHAR2(150);
489 grouping_attr_exp EXCEPTION;
490 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
491 l_Error_Message VARCHAR2(2000);
492
493 BEGIN
494
495 x_return_status := fnd_api.g_ret_sts_success;
496
497 get_fa_asset_category(
498 p_item_id => p_item_id,
499 p_inv_master_org_id => p_organization_id,
500 p_transaction_id => p_transaction_id,
501 x_asset_category_id => l_asset_category_id,
502 x_asset_category => l_asset_category,
503 x_return_status => l_return_status,
504 x_error_message => l_error_message);
505
506 IF l_return_status <> fnd_api.g_ret_sts_success THEN
507 RAISE fnd_api.g_exc_error ;
508 END IF;
509
510 get_fa_location_segment (
511 p_fa_location_id => p_fa_location_id,
512 p_transaction_id => p_transaction_id,
513 x_fa_location => l_fa_location,
514 x_return_status => l_return_status,
515 x_error_message => l_error_message);
516
517 IF l_return_status <> fnd_api.g_ret_sts_success THEN
518 RAISE grouping_attr_exp;
519 END IF;
520
521 get_product_name (
522 p_project_id => p_project_id,
523 p_transaction_id => p_transaction_id,
524 x_product_name => l_product_name,
525 x_return_status => l_return_status,
526 x_error_message => l_error_message);
527
528 IF l_return_status <> fnd_api.g_ret_sts_success THEN
529 RAISE grouping_attr_exp;
530 END IF;
531
532 --get attribute 8,9,10
533 BEGIN
534
535 SELECT asset_location, asset_category
536 INTO l_grp_asset_location, l_grp_asset_category
537 FROM ipa_asset_naming_convents_all
538 WHERE org_id = p_org_id;
539
540 debug(' crl_asset_loc_attrib : '||l_grp_asset_location);
541 debug(' crl_asset_cat_attrib : '||l_grp_asset_category);
542
543 x_attribute8 := NULL;
544 x_attribute9 := NULL;
545 x_attribute10 := NULL;
546
547 IF l_grp_asset_location = 'ALGE1' THEN
548 x_attribute8 := l_fa_location;
549 ELSIF l_grp_asset_location = 'ALGE2' THEN
550 x_attribute9 := l_fa_location;
551 ELSIF l_grp_asset_location = 'ALGE3' THEN
552 x_attribute10 := l_fa_location;
553
554 END IF;
555
556 IF l_grp_asset_category = 'ACGE1' THEN
557 x_attribute8 := l_asset_category;
558 ELSIF l_grp_asset_category = 'ACGE2' THEN
559 x_attribute9 := l_asset_category;
560 ELSIF l_grp_asset_category = 'ACGE3' THEN
561 x_attribute10 := l_asset_category;
562 END IF;
563
564 IF x_attribute8 IS NULL THEN
565 x_attribute8 := l_product_name;
566 ELSIF x_attribute9 IS NULL THEN
567 x_attribute9 := l_product_name;
568 ELSIF x_attribute10 IS NULL THEN
569 x_attribute10 := l_product_name;
570 END IF;
571
572 EXCEPTION
573 WHEN no_data_found THEN
574 fnd_message.set_name('CSE', 'CSE_CRL_GRP_NOT_FOUND');
575 fnd_message.set_token('ORG_ID', p_org_id);
576 fnd_msg_pub.add;
577 l_error_message := cse_util_pkg.dump_error_stack;
578 RAISE fnd_api.g_exc_error;
579 END;
580
581 EXCEPTION
582 WHEN fnd_api.g_exc_error THEN
583 x_error_message := l_error_message;
584 x_return_status := l_return_status;
585 WHEN OTHERS THEN
586 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
587 fnd_message.set_token('ERR_MSG', sqlerrm);
588 l_error_message := fnd_message.get;
589 x_error_message := l_error_message;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 END get_grouping_attribute;
592
593 END cse_ipa_trans_pkg;