[Home] [Help]
PACKAGE BODY: APPS.CSE_PROJ_ITEM_IN_SRV_PKG
Source
1 PACKAGE BODY cse_proj_item_in_srv_pkg AS
2 /* $Header: CSEITSVB.pls 120.16.12010000.3 2008/10/09 18:43:39 rsinn ship $ */
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 IF nvl(fnd_global.conc_request_id,-1) <> -1 THEN
13 fnd_file.put_line(fnd_file.log,p_message);
14 END IF;
15 END IF;
16 EXCEPTION
17 WHEN others THEN
18 null;
19 END debug;
20
21 PROCEDURE get_fa_location_id(
22 p_location_type_code IN varchar2,
23 p_location_id IN number,
24 x_fa_location_id OUT nocopy number,
25 x_return_status OUT nocopy varchar2)
26 IS
27
28 l_location_table varchar2(30);
29 l_hz_or_hr varchar2(1);
30
31 CURSOR loc_map_cur(p_location_table IN varchar2) IS
32 SELECT fa_location_id
33 FROM csi_a_locations
34 WHERE location_table in (p_location_table , 'LOCATION_CODES')
35 AND location_id = p_location_id
36 AND sysdate BETWEEN nvl(active_start_date, sysdate - 1)
37 AND nvl(active_end_date, sysdate + 1);
38 BEGIN
39
40 x_return_status := fnd_api.g_ret_sts_success;
41
42 debug(' location_type_code : '||p_location_type_code);
43 debug(' location_id : '||p_location_id);
44
45 IF p_location_type_code IN ('INVENTORY', 'INTERNAL_SITE') THEN
46 l_location_table := 'HR_LOCATIONS';
47 ELSIF p_location_type_code = 'HZ_LOCATIONS' THEN
48 BEGIN
49 SELECT 'Y' INTO l_hz_or_hr
50 FROM hz_locations
51 WHERE location_id = p_location_id;
52 l_location_table := 'HZ_LOCATIONS';
53 EXCEPTION
54 WHEN no_data_found THEN
55 l_location_table := 'HR_LOCATIONS';
56 END;
57 ELSE
58 l_location_table := p_location_type_code;
59 END IF;
60
61 FOR loc_rec IN loc_map_cur(l_location_table)
62 LOOP
63 x_fa_location_id := loc_rec.fa_location_id;
64 exit;
65 END LOOP;
66
67 IF x_fa_location_id is null then
68 RAISE fnd_api.g_exc_error;
69 END IF;
70 EXCEPTION
71 WHEN fnd_api.g_exc_error THEN
72 x_return_status := fnd_api.g_ret_sts_error;
73 END get_fa_location_id;
74
75 PROCEDURE interface_nl_to_pa(
76 p_in_srv_pa_attr_rec IN cse_datastructures_pub.proj_itm_insv_pa_attr_rec_type,
77 p_conc_request_id IN NUMBER ,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_error_message OUT NOCOPY VARCHAR2)
80 IS
81 l_api_name CONSTANT VARCHAR2(30) := 'cse_proj_item_in_service_pkg';
82 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
83 l_error_message VARCHAR2(2000);
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2(2000);
86 l_api_version NUMBER DEFAULT 1.0;
87 l_commit VARCHAR2(1) DEFAULT FND_API.G_FALSE;
88 l_init_msg_list VARCHAR2(1) DEFAULT FND_API.G_TRUE;
89 l_validation_level NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL;
90 l_active_instance_only VARCHAR2(1) DEFAULT FND_API.G_TRUE;
91 l_txn_rec csi_datastructures_pub.transaction_rec;
92 l_asset_location_rec csi_datastructures_pub.instance_asset_location_rec;
93 l_asset_location_tbl csi_datastructures_pub.instance_asset_location_tbl;
94 l_nl_pa_interface_tbl CSE_IPA_TRANS_PKG.nl_pa_interface_tbl_type;
95 l_nl_pa_interface_rec CSE_IPA_TRANS_PKG.nl_pa_interface_rec_type;
96 l_burden_cost_sum NUMBER;
97 l_qty_sum NUMBER;
98 l_sum_of_qty NUMBER;
99 l_fa_location_id NUMBER;
100 l_attribute8 VARCHAR2(150);
101 l_attribute9 VARCHAR2(150);
102 l_attribute10 VARCHAR2(150);
103 l_Proj_Itm_Insv_qty NUMBER;
104
105 l_book_type_code varchar2(30); -- Bug 6492235, changed to support multiple FA book
106 l_dpis date;
107 l_fa_period_name varchar2(15);
108 l_serial_code number;
109
110 l_location_id NUMBER;
111 l_location_type_code varchar2(30);
112
113 i PLS_INTEGER := 0;
114 l_org_id NUMBER;
115 l_incurred_by_org_id PA_EXPENDITURES_ALL.INCURRED_BY_ORGANIZATION_ID%TYPE;
116 l_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
117 l_user_id NUMBER DEFAULT FND_GLOBAL.USER_ID;
118 l_transaction_source PA_EXPENDITURE_ITEMS_ALL.TRANSACTION_SOURCE%TYPE;
119 l_sysdate date:=sysdate;
120 l_ref_sufix NUMBER;
121
122 l_project_number varchar2(25);
123 l_task_number varchar2(25);
124 l_organization_name varchar2(240);
125
126 l_ou_id number; -- Bug 6492235, added to support multiple FA book
127
128 TYPE exp_item_rec IS RECORD (
129 expenditure_item_id number,
130 expenditure_id number,
131 quantity number,
132 split_flag varchar2(1),
133 split_quantity number);
134
135 l_exp_item_rec exp_item_rec;
136
137 CURSOR ei_cur IS
138 SELECT item.expenditure_item_id,
139 item.project_id,
140 item.task_id,
141 item.transaction_source,
142 item.org_id,
143 item.expenditure_type,
144 item.expenditure_item_date,
145 item.denom_currency_code,
146 item.attribute6,
147 item.attribute7,
148 item.quantity quantity,
149 item.raw_cost raw_cost,
150 item.denom_raw_cost denom_raw_cost,
151 item.denom_raw_cost/item.quantity unit_denom_raw_cost,
152 item.raw_cost_rate,
153 item.burden_cost burden_cost,
154 item.burden_cost/item.quantity burden_cost_rate,
155 item.override_to_organization_id,
156 item.system_linkage_function,
157 item.orig_transaction_reference,
158 dist.dr_code_combination_id,
159 dist.cr_code_combination_id,
160 dist.gl_date,
161 dist.acct_raw_cost,
162 dist.system_reference1,
163 dist.system_reference2,
164 dist.system_reference3,
165 dist.system_reference4,
166 dist.system_reference5,
167 exp.expenditure_id,
168 exp.expenditure_ending_date,
169 exp.incurred_by_organization_id,
170 item.vendor_id,
171 item.po_line_id,
172 item.inventory_item_id,
173 item.document_type,
174 item.document_distribution_type
175 FROM pa_expenditure_items_all item,
176 pa_cost_distribution_lines_all dist,
177 pa_expenditures_all exp
178 WHERE item.transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
179 AND item.project_id = p_in_srv_pa_attr_rec.project_id
180 AND item.task_id = p_in_srv_pa_attr_rec.task_id
181 AND item.attribute8 IS null
182 AND item.attribute9 IS null
183 AND item.attribute10 IS null
184 AND item.quantity > 0
185 AND item.attribute6 = l_item_name
186 AND nvl(item.attribute7, '**xyz**') = NVL(p_in_srv_pa_attr_rec.serial_number, '**xyz**')
187 AND nvl(item.net_zero_adjustment_flag, 'N') <> 'Y'
188 AND dist.expenditure_item_id = item.expenditure_item_id
189 AND dist.line_type = 'R'
190 AND nvl(dist.reversed_flag, 'N') <> 'Y'
191 AND dist.cr_code_combination_id IS NOT NULL
192 AND dist.dr_code_combination_id IS NOT NULL
193 AND exp.expenditure_id = item.expenditure_id;
194
195 subtype ei_cur_rec is ei_cur%rowtype;
196
197 l_fa_group_by varchar2(30);
198
199 skip_this_ei exception ;
200 l_rcv_sub_ledger_id number;
201
202 PROCEDURE reversal_exp_item(
203 p_csi_txn_id IN number,
204 p_organization_name IN varchar2,
205 p_project_number IN varchar2,
206 p_task_number IN varchar2,
207 p_item_name IN varchar2,
208 p_ei_rec IN ei_cur_rec,
209 x_nl_pa_interface_rec OUT nocopy CSE_IPA_TRANS_PKG.nl_pa_interface_rec_type)
210 IS
211 l_ref_suffix_inner number; -- Changes for bug 7368371
212 BEGIN
213
214 SELECT csi_pa_interface_s.nextval
215 INTO l_ref_suffix_inner -- Changes for bug 7368371
216 FROM sys.dual;
217
218 x_nl_pa_interface_rec.transaction_source := p_ei_rec.transaction_source;
219 x_nl_pa_interface_rec.batch_name := p_in_srv_pa_attr_rec.transaction_id;
220 x_nl_pa_interface_rec.expenditure_ending_date := p_ei_rec.expenditure_ending_date;
221 x_nl_pa_interface_rec.employee_number := null;
222 x_nl_pa_interface_rec.organization_name := p_organization_name;
223 x_nl_pa_interface_rec.expenditure_item_date := p_ei_rec.expenditure_item_date;
224 x_nl_pa_interface_rec.project_number := p_project_number;
225 x_nl_pa_interface_rec.task_number := p_task_number;
226 x_nl_pa_interface_rec.expenditure_type := p_ei_rec.expenditure_type;
227 x_nl_pa_interface_rec.expenditure_comment := 'ENTERPRISE INSTALL BASE';
228 x_nl_pa_interface_rec.transaction_status_code := 'P';
229 x_nl_pa_interface_rec.orig_transaction_reference := p_csi_txn_id||'-'||l_ref_suffix_inner; -- Changes for bug 7368371
230 x_nl_pa_interface_rec.attribute_category := NULL;
231 x_nl_pa_interface_rec.attribute1 := NULL;
232 x_nl_pa_interface_rec.attribute2 := NULL;
233 x_nl_pa_interface_rec.attribute3 := NULL;
234 x_nl_pa_interface_rec.attribute4 := NULL;
235 x_nl_pa_interface_rec.attribute5 := NULL;
236 x_nl_pa_interface_rec.attribute6 := p_item_name;
237 x_nl_pa_interface_rec.attribute7 := p_ei_rec.attribute7;
238 x_nl_pa_interface_rec.attribute8 := null;
239 x_nl_pa_interface_rec.attribute9 := null;
240 x_nl_pa_interface_rec.attribute10 := null;
241 x_nl_pa_interface_rec.interface_id := NULL;
242 x_nl_pa_interface_rec.unmatched_negative_txn_flag := 'N';
243 x_nl_pa_interface_rec.org_id := p_ei_rec. org_id;
244 x_nl_pa_interface_rec.dr_code_combination_id := p_ei_rec.dr_code_combination_id;
245 x_nl_pa_interface_rec.cr_code_combination_id := p_ei_rec.cr_code_combination_id;
246 x_nl_pa_interface_rec.gl_date := p_ei_rec.gl_date;
247 x_nl_pa_interface_rec.system_linkage := p_ei_rec.system_linkage_function;
248 IF p_ei_rec.transaction_source = 'CSE_PO_RECEIPT' THEN
249 BEGIN
250 SELECT segment1
251 INTO x_nl_pa_interface_rec.vendor_number
252 FROM po_vendors
253 WHERE vendor_id = p_ei_rec.system_reference1;
254 EXCEPTION
255 WHEN no_data_found THEN
256 x_nl_pa_interface_rec.system_linkage := 'INV';
257 END;
258 END IF;
259 x_nl_pa_interface_rec.user_transaction_source := 'ENTERPRISE INSTALL BASE';
260 x_nl_pa_interface_rec.cdl_system_reference1 := p_ei_rec.system_reference1;
261 x_nl_pa_interface_rec.cdl_system_reference2 := p_ei_rec.system_reference2;
262 x_nl_pa_interface_rec.cdl_system_reference3 := p_ei_rec.system_reference3;
263 x_nl_pa_interface_rec.cdl_system_reference4 := p_ei_rec.system_reference4;
264 x_nl_pa_interface_rec.cdl_system_reference5 := p_ei_rec.system_reference5;
265 x_nl_pa_interface_rec.last_update_date := sysdate;
266 x_nl_pa_interface_rec.last_updated_by := fnd_global.user_id;
267 x_nl_pa_interface_rec.creation_date := sysdate;
268 x_nl_pa_interface_rec.created_by := fnd_global.user_id;
269 x_nl_pa_interface_rec.billable_flag := 'Y';
270 x_nl_pa_interface_rec.quantity := -1*(p_ei_rec.quantity);
271 x_nl_pa_interface_rec.denom_raw_cost :=
272 p_ei_rec.unit_denom_raw_cost * x_nl_pa_interface_rec.quantity;
273 x_nl_pa_interface_rec.acct_raw_cost :=
274 p_ei_rec.unit_denom_raw_cost * x_nl_pa_interface_rec.quantity;
275 x_nl_pa_interface_rec.net_zero_adjustment_flag := 'Y';
276 x_nl_pa_interface_rec.adjusted_expenditure_item_id := p_ei_rec.expenditure_item_id;
277 x_nl_pa_interface_rec.vendor_id := p_ei_rec.vendor_id;
278 x_nl_pa_interface_rec.inventory_item_id := p_ei_rec.inventory_item_id;
279 x_nl_pa_interface_rec.po_line_id := p_ei_rec.po_line_id;
280 x_nl_pa_interface_rec.project_id := p_ei_rec.project_id;
281 x_nl_pa_interface_rec.task_id := p_ei_rec.task_id;
282 x_nl_pa_interface_rec.document_type := p_ei_rec.document_type;
283 x_nl_pa_interface_rec.document_distribution_type := p_ei_rec.document_distribution_type;
284 END reversal_exp_item;
285
286 BEGIN
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288 x_error_message := NULL;
289
290 cse_util_pkg.set_debug;
291
292 debug('Inside API cse_proj_item_in_srv_pkg.interface_nl_to_pa');
293
294 debug(' inventory_item_id : '||p_in_srv_pa_attr_rec.item_id);
295 debug(' organization_id : '||p_in_srv_pa_attr_rec.inv_master_org_id);
296 debug(' project_id : '||p_in_srv_pa_attr_rec.project_id);
297 debug(' task_id : '||p_in_srv_pa_attr_rec.task_id);
298 debug(' serial_number : '||p_in_srv_pa_attr_rec.serial_number);
299 debug(' transaction_id : '||p_in_srv_pa_attr_rec.transaction_id);
300 debug(' in_service_qty : '||p_in_srv_pa_attr_rec.quantity);
301 debug(' org_id : '||p_in_srv_pa_attr_rec.org_id);
302
303 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
304 csi_gen_utility_pvt.populate_install_param_rec;
305 END IF;
306
307 l_fa_group_by := csi_datastructures_pub.g_install_param_rec.fa_creation_group_by;
308
309 SELECT concatenated_segments,
310 serial_number_control_code
311 INTO l_item_name,
312 l_serial_code
313 FROM mtl_system_items_kfv
314 WHERE inventory_item_id = p_in_srv_pa_attr_rec.item_id
315 AND organization_id = p_in_srv_pa_attr_rec.inv_master_org_id;
316
317 debug(' item : '||l_item_name);
318
319 l_location_id := p_in_srv_pa_attr_rec.location_id;
320 l_location_type_code := p_in_srv_pa_attr_rec.location_type;
321
322 IF p_in_srv_pa_attr_rec.location_type ='HZ_PARTY_SITES' THEN
323
324 debug('Inside API cse_util_pkg.get_hz_location');
325
326 cse_util_pkg.get_hz_location (
327 p_party_site_id => p_in_srv_pa_attr_rec.location_id,
328 x_hz_location_id => l_location_id,
329 x_Return_Status => l_return_status,
330 x_Error_Message => l_error_message );
331
332 l_location_type_code := 'HZ_LOCATIONS';
333
334 END IF;
335
336 get_fa_location_id(
337 p_location_type_code => l_location_type_code,
338 p_location_id => l_location_id,
339 x_fa_location_id => l_fa_location_id,
340 x_return_status => l_return_status);
341
342 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
343 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
344 fnd_message.set_token('ASSET_ATTRIBUTE','LOCATION');
345 fnd_message.set_token('CSI_TRANSACTION_ID',p_in_srv_pa_attr_rec.transaction_id);
346 l_error_message := fnd_message.get;
347 RAISE fnd_api.g_exc_error;
348 END IF;
349
350 debug(' fa_location_id : '||l_fa_location_id);
351
352 debug('Inside API cse_ipa_trans_pkg.get_grouping_attribute');
353
354 cse_ipa_trans_pkg.get_grouping_attribute(
355 p_item_id => p_in_srv_pa_attr_rec.item_id,
356 p_organization_id => p_in_srv_pa_attr_rec.inv_master_org_id,
357 p_project_id => p_in_srv_pa_attr_rec.project_id,
358 p_fa_location_id => l_fa_location_id,
359 p_transaction_id => p_in_srv_pa_attr_rec.transaction_id,
360 p_org_id => p_in_srv_pa_attr_rec.org_id,
361 x_attribute8 => l_attribute8,
362 x_attribute9 => l_attribute9,
363 x_attribute10 => l_attribute10,
364 x_return_status => l_return_status,
365 x_error_message => l_error_message);
366
367 debug(' attribute8 : '||l_attribute8);
368 debug(' attribute9 : '||l_attribute9);
369 debug(' attribute10 : '||l_attribute10);
370
371 IF l_return_status <> fnd_api.g_ret_sts_success THEN
372 debug('CSE_IPA_TRANS_PKG.get_grouping_attribute failed : '||l_error_message);
373 RAISE fnd_api.g_exc_error;
374 END IF;
375
376 SELECT segment1, org_id -- Bug 6492235, changed to support multiple FA book
377 INTO l_project_number, l_ou_id -- Bug 6492235, changed to support multiple FA book
378 FROM pa_projects_all
379 WHERE project_id = p_in_srv_pa_attr_rec.project_id;
380
381 SELECT task_number
382 INTO l_task_number
383 FROM pa_tasks
384 WHERE task_id = p_in_srv_pa_attr_rec.task_id;
385
386 SELECT source_transaction_date
387 INTO l_dpis
388 FROM csi_transactions
389 WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_id;
390
391 -- Bug 6492235, added to support multiple FA book
392 l_book_type_code := fnd_profile.VALUE_SPECIFIC(
393 name => 'cse_fa_book_type_code',
394 ORG_ID => l_ou_id
395 );
396
397 l_fa_period_name := cse_asset_util_pkg.get_fa_period_name (l_book_type_code, l_dpis);
398
399 l_proj_itm_insv_qty := p_in_srv_pa_attr_rec.quantity;
400 i := 0;
401
402 FOR ei_rec IN ei_cur LOOP
403
404 debug('cursor record # '||ei_cur%rowcount);
405
406 debug(' expenditure_item_id : '||ei_rec.expenditure_item_id);
407 debug(' quantity : '||ei_rec.quantity);
408 debug(' l_proj_itm_insv_qty : '||l_proj_itm_insv_qty);
409
410 debug(' transaction_source : '||ei_rec.transaction_source);
411 debug(' system_reference4 : '||ei_rec.system_reference4);
412 debug(' system_reference5 : '||ei_rec.system_reference5);
413
414 BEGIN
415
416 -- accrual at period end cases. for accrual at receipt cases we will have system_ref5 populated
417 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' AND ei_rec.system_reference5 is null THEN
418
419 -- check if the period end accrual is done
420 l_rcv_sub_ledger_id := cse_asset_util_pkg.get_rcv_sub_ledger_id(ei_rec.system_reference4);
421 debug(' rcv_sub_ledger_id : '||l_rcv_sub_ledger_id);
422
423 IF l_rcv_sub_ledger_id is null THEN
424 debug(' rcv sub ledger id not found. receipt not accounted yet. skipping this ei to be placed in service.');
425 RAISE skip_this_ei;
426 END IF;
427
428 END IF;
429
430 IF l_proj_itm_insv_qty = 0 THEN
431 exit;
432 END IF;
433
434 IF ei_rec.quantity <= l_proj_itm_insv_qty THEN
435 l_proj_itm_insv_qty := l_proj_itm_insv_qty - ei_rec.quantity;
436 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
437 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
438 l_exp_item_rec.quantity := ei_rec.quantity;
439 l_exp_item_rec.split_flag := 'N';
440 ELSE
441 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
442 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
443 l_exp_item_rec.quantity := l_proj_itm_insv_qty;
444 l_exp_item_rec.split_flag := 'Y';
445 l_exp_item_rec.split_quantity := ei_rec.quantity - l_proj_itm_insv_qty;
446 END IF;
447
448 SELECT name
449 INTO l_organization_name
450 FROM hr_organization_units
451 WHERE organization_id =
452 nvl(ei_rec.override_to_organization_id, ei_rec.incurred_by_organization_id);
453
454 reversal_exp_item(
455 p_csi_txn_id => p_in_srv_pa_attr_rec.transaction_id,
456 p_organization_name => l_organization_name,
457 p_project_number => l_project_number,
458 p_task_number => l_task_number,
459 p_item_name => l_item_name,
460 p_ei_rec => ei_rec,
461 x_nl_pa_interface_rec => l_nl_pa_interface_rec);
462
463 i := i+1;
464
465 debug('reversal record # '||i);
466 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_rec;
467
468 i := i+1;
469
470 debug('capitalizable record # '||i);
471 debug(' capitalizable exp_item_id : '||l_exp_item_rec.expenditure_item_id);
472 debug(' capitalizable quantity : '||l_exp_item_rec.quantity);
473
474 SELECT csi_pa_interface_s.nextval
475 INTO l_ref_sufix
476 FROM sys.dual;
477
478 l_nl_pa_interface_tbl(i).transaction_source := ei_rec.transaction_source;
479 l_nl_pa_interface_tbl(i).batch_name := p_in_srv_pa_attr_rec.transaction_id;
480 l_nl_pa_interface_tbl(i).expenditure_ending_date := ei_rec.expenditure_ending_date;
481 l_nl_pa_interface_tbl(i).employee_number := null;
482 l_nl_pa_interface_tbl(i).organization_name := l_organization_name;
483 l_nl_pa_interface_tbl(i).expenditure_item_date := ei_rec.expenditure_item_date;
484 l_nl_pa_interface_tbl(i).project_number := l_project_number;
485 l_nl_pa_interface_tbl(i).task_number := l_task_number;
486 l_nl_pa_interface_tbl(i).expenditure_type := ei_rec.expenditure_type;
487 l_nl_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE';
488 l_nl_pa_interface_tbl(i).transaction_status_code := 'P';
489 l_nl_pa_interface_tbl(i).orig_transaction_reference
490 := p_in_srv_pa_attr_rec.instance_id||'-'||l_ref_sufix;
491 l_nl_pa_interface_tbl(i).attribute_category := NULL;
492 l_nl_pa_interface_tbl(i).attribute1 := NULL;
493 l_nl_pa_interface_tbl(i).attribute2 := NULL;
494 l_nl_pa_interface_tbl(i).attribute3 := NULL;
495 l_nl_pa_interface_tbl(i).attribute4 := NULL;
496 l_nl_pa_interface_tbl(i).attribute5 := NULL;
497 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
498 IF l_serial_code in (2, 5) THEN
499 IF l_fa_group_by = 'ITEM' THEN
500 l_nl_pa_interface_tbl(i).attribute7 := l_fa_period_name;
501 ELSE
502 l_nl_pa_interface_tbl(i).attribute7 := p_in_srv_pa_attr_rec.serial_number;
503 END IF;
504 ELSE
505 l_nl_pa_interface_tbl(i).attribute7 := l_fa_period_name;
506 END IF;
507 l_nl_pa_interface_tbl(i).attribute8 := l_attribute8;
508 l_nl_pa_interface_tbl(i).attribute9 := l_attribute9;
509 l_nl_pa_interface_tbl(i).attribute10 := l_attribute10;
510 l_nl_pa_interface_tbl(i).interface_id := NULL;
511 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
512 l_nl_pa_interface_tbl(i).org_id := ei_rec. org_id;
513 l_nl_pa_interface_tbl(i).dr_code_combination_id := ei_rec.dr_code_combination_id;
514 l_nl_pa_interface_tbl(i).cr_code_combination_id := ei_rec.cr_code_combination_id;
515 l_nl_pa_interface_tbl(i).gl_date := ei_rec.gl_date;
516 l_nl_pa_interface_tbl(i).system_linkage := ei_rec.system_linkage_function;
517 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' THEN
518 BEGIN
519 SELECT segment1
520 INTO l_nl_pa_interface_tbl(i).vendor_number
521 FROM po_vendors
522 WHERE vendor_id = ei_rec.system_reference1;
523 EXCEPTION
524 WHEN no_data_found THEN
525 l_nl_pa_interface_tbl(i).system_linkage := 'INV';
526 END;
527 END IF;
528 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
529 l_nl_pa_interface_tbl(i).cdl_system_reference1 := ei_rec.system_reference1;
530 l_nl_pa_interface_tbl(i).cdl_system_reference2 := ei_rec.system_reference2;
531 l_nl_pa_interface_tbl(i).cdl_system_reference3 := ei_rec.system_reference3;
532 l_nl_pa_interface_tbl(i).cdl_system_reference4 := ei_rec.system_reference4;
533 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' AND ei_rec.system_reference5 is NULL THEN
534 l_nl_pa_interface_tbl(i).cdl_system_reference5 := l_rcv_sub_ledger_id;
535 ELSE
536 l_nl_pa_interface_tbl(i).cdl_system_reference5 := ei_rec.system_reference5;
537 END IF;
538 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
539 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
540 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
541 l_nl_pa_interface_tbl(i).created_by := l_user_id;
542 l_nl_pa_interface_tbl(i).billable_flag := 'Y';
543 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.quantity;
544 l_nl_pa_interface_tbl(i).denom_raw_cost :=
545 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
546 l_nl_pa_interface_tbl(i).acct_raw_cost :=
547 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
548 l_nl_pa_interface_tbl(i).vendor_id := ei_rec.vendor_id;
549 l_nl_pa_interface_tbl(i).inventory_item_id := ei_rec.inventory_item_id;
550 l_nl_pa_interface_tbl(i).po_line_id := ei_rec.po_line_id;
551 l_nl_pa_interface_tbl(i).project_id := ei_rec.project_id;
552 l_nl_pa_interface_tbl(i).task_id := ei_rec.task_id;
553 l_nl_pa_interface_tbl(i).document_type := ei_rec.document_type;
554 l_nl_pa_interface_tbl(i).document_distribution_type := ei_rec.document_distribution_type;
555
556 IF l_exp_item_rec.split_flag = 'Y' THEN
557
558 i := i + 1;
559
560 debug('spillover record # '||i);
561 debug(' spillover exp_item_id : '|| l_exp_item_rec.expenditure_item_id);
562 debug(' spillover quantity : '|| l_exp_item_rec.split_quantity);
563
564 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_tbl(i-1);
565
566 SELECT csi_pa_interface_s.nextval
567 INTO l_ref_sufix
568 FROM sys.dual;
569
570 l_nl_pa_interface_tbl(i).orig_transaction_reference := p_in_srv_pa_attr_rec.transaction_id;
571 l_nl_pa_interface_tbl(i).attribute8 := null;
572 l_nl_pa_interface_tbl(i).attribute9 := null;
573 l_nl_pa_interface_tbl(i).attribute10 := null;
574 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.split_quantity;
575 l_nl_pa_interface_tbl(i).denom_raw_cost :=
576 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
577 l_nl_pa_interface_tbl(i).acct_raw_cost :=
578 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
579 exit;
580 END IF;
581
582 EXCEPTION
583 WHEN skip_this_ei THEN
584 debug('skipped this expenditure_item_id : '||ei_rec.expenditure_item_id);
585 END;
586
587 END LOOP;
588
589 debug('l_nl_pa_interface_tbl.count : '||l_nl_pa_interface_tbl.COUNT);
590
591 IF l_nl_pa_interface_tbl.COUNT > 0 THEN
592
593 debug('Inside API cse_ipa_trans_pkg.populate_pa_interface');
594
595 cse_ipa_trans_pkg.populate_pa_interface(
596 p_nl_pa_interface_tbl => l_nl_pa_interface_tbl,
597 x_return_status => l_return_status,
598 x_error_message => l_error_message);
599
600 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
601 debug('error_ message : '||l_error_message);
602 RAISE fnd_api.g_exc_error;
603 END IF;
604
605 --update transaction record with new txn_status_code = 'INTERFACE_TO_PA'
606 l_txn_rec := CSE_UTIL_PKG.init_txn_rec;
607 l_txn_rec.transaction_id := p_in_srv_pa_attr_rec.transaction_id;
608 l_txn_rec.source_group_ref_id := p_conc_request_id;
609 l_txn_rec.source_header_ref_id := p_in_srv_pa_attr_rec.project_id;
610 l_txn_rec.source_header_ref := l_project_number;
611 l_txn_rec.source_line_ref_id := p_in_srv_pa_attr_rec.task_id;
612 l_txn_rec.source_line_ref := l_task_number;
613 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_INTERFACED_TO_PA;
614 --Begin Changes for Bug 7354734
615 --l_txn_rec.object_version_number := p_in_srv_pa_attr_rec.object_version_number;
616
617 BEGIN
618 SELECT object_version_number
619 INTO l_txn_rec.object_version_number
620 FROM csi_transactions
621 WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_id;
622 EXCEPTION
623 WHEN OTHERS THEN
624 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
625 fnd_message.set_token('ERR_MSG',l_api_name||'='|| SQLERRM);
626 x_error_message := fnd_message.get;
627 x_return_status := fnd_api.g_ret_sts_unexp_error;
628 debug('Inside OTHERS in interface_nl_to_pa : ' ||x_error_message);
629 END;
630 --End Changes for Bug 7354734
631
632 debug('Inside API csi_transactions_pvt.update_transactions');
633 debug(' transaction_id : '||l_txn_rec.transaction_id);
634
635 csi_transactions_pvt.update_transactions(
636 p_api_version => l_api_version,
637 p_init_msg_list => l_init_msg_list,
638 p_commit => l_commit,
639 p_validation_level => l_validation_level,
640 p_transaction_rec => l_txn_rec,
641 x_return_status => l_return_status,
642 x_msg_count => l_msg_count,
643 x_msg_data => l_msg_data);
644
645 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
646 l_error_message := cse_util_pkg.dump_error_stack ;
647 RAISE fnd_api.g_exc_error;
648 END IF;
649 END IF;
650
651 EXCEPTION
652 WHEN fnd_api.g_exc_error THEN
653 x_return_status := l_return_status;
654 x_error_message := l_error_message;
655 debug('error in interface_nl_to_pa : '||x_error_message);
656 WHEN OTHERS THEN
657 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
658 fnd_message.set_token('ERR_MSG',l_api_name||'='|| SQLERRM);
659 x_error_message := fnd_message.get;
660 x_return_status := fnd_api.g_ret_sts_unexp_error;
661 debug('Inside OTHERS in interface_nl_to_pa : ' ||x_error_message);
662 END interface_nl_to_pa;
663
664 PROCEDURE interface_nl_to_pa(
665 p_in_srv_pa_attr_tbl IN CSE_DATASTRUCTURES_PUB.Proj_Itm_Insv_PA_ATTR_tbl_TYPE,
666 p_conc_request_id IN NUMBER DEFAULT NULL,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_error_message OUT NOCOPY VARCHAR2)
669 IS
670 BEGIN
671 IF NOT p_in_srv_pa_attr_tbl.COUNT = 0 THEN
672
673 FOR i IN p_in_srv_pa_attr_tbl.FIRST .. p_in_srv_pa_attr_tbl.LAST
674 LOOP
675 IF p_in_srv_pa_attr_tbl.EXISTS(i) THEN
676 interface_nl_to_pa( p_in_srv_pa_attr_tbl(i),
677 p_conc_request_id,
678 x_return_status,
679 x_error_message);
680 END IF;
681
682 END LOOP;
683 END IF; -- tbl.count IF
684 EXCEPTION
685 WHEN OTHERS THEN
686 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
687 fnd_message.set_token('ERR_MSG','CSE_PROJ_ITEM_IN_SRV_PKG.Interface_Nl_To_PA'||'='|| SQLERRM);
688 x_error_message := fnd_message.get;
689 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
690 END interface_nl_to_pa;
691
692 END cse_proj_item_in_srv_pkg;