[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.12020000.2 2012/07/04 15:03:18 dsingire 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 l_skip_interface boolean := FALSE;
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 AND item.expenditure_item_id NOT IN (SELECT NVL(adjusted_expenditure_item_id,0) --Added for bug 9263804 expenditures already reversed should not be considered
195 FROM pa_transaction_interface_all
196 WHERE transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
197 AND attribute8 IS null
198 AND attribute9 IS null
199 AND attribute10 IS null);
200
201 subtype ei_cur_rec is ei_cur%rowtype;
202
203 l_fa_group_by varchar2(30);
204
205 skip_this_ei exception ;
206 l_rcv_sub_ledger_id number;
207
208 PROCEDURE reversal_exp_item(
209 p_csi_txn_id IN number,
210 p_organization_name IN varchar2,
211 p_project_number IN varchar2,
212 p_task_number IN varchar2,
213 p_item_name IN varchar2,
214 p_ei_rec IN ei_cur_rec,
215 x_nl_pa_interface_rec OUT nocopy CSE_IPA_TRANS_PKG.nl_pa_interface_rec_type)
216 IS
217 l_ref_suffix_inner number; -- Changes for bug 7368371
218 BEGIN
219
220 SELECT csi_pa_interface_s.nextval
221 INTO l_ref_suffix_inner -- Changes for bug 7368371
222 FROM sys.dual;
223
224 x_nl_pa_interface_rec.transaction_source := p_ei_rec.transaction_source;
225 x_nl_pa_interface_rec.batch_name := p_in_srv_pa_attr_rec.transaction_id;
226 x_nl_pa_interface_rec.expenditure_ending_date := p_ei_rec.expenditure_ending_date;
227 x_nl_pa_interface_rec.employee_number := null;
228 x_nl_pa_interface_rec.organization_name := p_organization_name;
229 x_nl_pa_interface_rec.expenditure_item_date := p_ei_rec.expenditure_item_date;
230 x_nl_pa_interface_rec.project_number := p_project_number;
231 x_nl_pa_interface_rec.task_number := p_task_number;
232 x_nl_pa_interface_rec.expenditure_type := p_ei_rec.expenditure_type;
233 x_nl_pa_interface_rec.expenditure_comment := 'ENTERPRISE INSTALL BASE';
234 x_nl_pa_interface_rec.transaction_status_code := 'P';
235 x_nl_pa_interface_rec.orig_transaction_reference := p_csi_txn_id||'-'||l_ref_suffix_inner; -- Changes for bug 7368371
236 x_nl_pa_interface_rec.attribute_category := NULL;
237 x_nl_pa_interface_rec.attribute1 := NULL;
238 x_nl_pa_interface_rec.attribute2 := NULL;
239 x_nl_pa_interface_rec.attribute3 := NULL;
240 x_nl_pa_interface_rec.attribute4 := NULL;
241 x_nl_pa_interface_rec.attribute5 := NULL;
242 x_nl_pa_interface_rec.attribute6 := p_item_name;
243 x_nl_pa_interface_rec.attribute7 := p_ei_rec.attribute7;
244 x_nl_pa_interface_rec.attribute8 := null;
245 x_nl_pa_interface_rec.attribute9 := null;
246 x_nl_pa_interface_rec.attribute10 := null;
247 x_nl_pa_interface_rec.interface_id := NULL;
248 x_nl_pa_interface_rec.unmatched_negative_txn_flag := 'N';
249 x_nl_pa_interface_rec.org_id := p_ei_rec. org_id;
250 x_nl_pa_interface_rec.dr_code_combination_id := p_ei_rec.dr_code_combination_id;
251 x_nl_pa_interface_rec.cr_code_combination_id := p_ei_rec.cr_code_combination_id;
252 x_nl_pa_interface_rec.gl_date := p_ei_rec.gl_date;
253 x_nl_pa_interface_rec.system_linkage := p_ei_rec.system_linkage_function;
254 IF p_ei_rec.transaction_source = 'CSE_PO_RECEIPT' THEN
255 BEGIN
256 SELECT segment1
257 INTO x_nl_pa_interface_rec.vendor_number
258 FROM po_vendors
259 WHERE vendor_id = p_ei_rec.system_reference1;
260 EXCEPTION
261 WHEN no_data_found THEN
262 x_nl_pa_interface_rec.system_linkage := 'INV';
263 END;
264 END IF;
265 x_nl_pa_interface_rec.user_transaction_source := 'ENTERPRISE INSTALL BASE';
266 x_nl_pa_interface_rec.cdl_system_reference1 := p_ei_rec.system_reference1;
267 x_nl_pa_interface_rec.cdl_system_reference2 := p_ei_rec.system_reference2;
268 x_nl_pa_interface_rec.cdl_system_reference3 := p_ei_rec.system_reference3;
269 x_nl_pa_interface_rec.cdl_system_reference4 := p_ei_rec.system_reference4;
270 x_nl_pa_interface_rec.cdl_system_reference5 := p_ei_rec.system_reference5;
271 x_nl_pa_interface_rec.last_update_date := sysdate;
272 x_nl_pa_interface_rec.last_updated_by := fnd_global.user_id;
273 x_nl_pa_interface_rec.creation_date := sysdate;
274 x_nl_pa_interface_rec.created_by := fnd_global.user_id;
275 x_nl_pa_interface_rec.billable_flag := 'Y';
276 x_nl_pa_interface_rec.quantity := -1*(p_ei_rec.quantity);
277 x_nl_pa_interface_rec.denom_raw_cost :=
278 p_ei_rec.unit_denom_raw_cost * x_nl_pa_interface_rec.quantity;
279 x_nl_pa_interface_rec.acct_raw_cost :=
280 p_ei_rec.unit_denom_raw_cost * x_nl_pa_interface_rec.quantity;
281 x_nl_pa_interface_rec.net_zero_adjustment_flag := 'Y';
282 x_nl_pa_interface_rec.adjusted_expenditure_item_id := p_ei_rec.expenditure_item_id;
283 x_nl_pa_interface_rec.vendor_id := p_ei_rec.vendor_id;
284 x_nl_pa_interface_rec.inventory_item_id := p_ei_rec.inventory_item_id;
285 x_nl_pa_interface_rec.po_line_id := p_ei_rec.po_line_id;
286 x_nl_pa_interface_rec.project_id := p_ei_rec.project_id;
287 x_nl_pa_interface_rec.task_id := p_ei_rec.task_id;
288 x_nl_pa_interface_rec.document_type := p_ei_rec.document_type;
289 x_nl_pa_interface_rec.document_distribution_type := p_ei_rec.document_distribution_type;
290 END reversal_exp_item;
291
292 BEGIN
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294 x_error_message := NULL;
295
296 cse_util_pkg.set_debug;
297
298 debug('Inside API cse_proj_item_in_srv_pkg.interface_nl_to_pa');
299
300 debug(' inventory_item_id : '||p_in_srv_pa_attr_rec.item_id);
301 debug(' organization_id : '||p_in_srv_pa_attr_rec.inv_master_org_id);
302 debug(' project_id : '||p_in_srv_pa_attr_rec.project_id);
303 debug(' task_id : '||p_in_srv_pa_attr_rec.task_id);
304 debug(' serial_number : '||p_in_srv_pa_attr_rec.serial_number);
305 debug(' transaction_id : '||p_in_srv_pa_attr_rec.transaction_id);
306 debug(' in_service_qty : '||p_in_srv_pa_attr_rec.quantity);
307 debug(' org_id : '||p_in_srv_pa_attr_rec.org_id);
308
309 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
310 csi_gen_utility_pvt.populate_install_param_rec;
311 END IF;
312
313 l_fa_group_by := csi_datastructures_pub.g_install_param_rec.fa_creation_group_by;
314
315 SELECT concatenated_segments,
316 serial_number_control_code
317 INTO l_item_name,
318 l_serial_code
319 FROM mtl_system_items_kfv
320 WHERE inventory_item_id = p_in_srv_pa_attr_rec.item_id
321 AND organization_id = p_in_srv_pa_attr_rec.inv_master_org_id;
322
323 debug(' item : '||l_item_name);
324
325 l_location_id := p_in_srv_pa_attr_rec.location_id;
326 l_location_type_code := p_in_srv_pa_attr_rec.location_type;
327
328 IF p_in_srv_pa_attr_rec.location_type ='HZ_PARTY_SITES' THEN
329
330 debug('Inside API cse_util_pkg.get_hz_location');
331
332 cse_util_pkg.get_hz_location (
333 p_party_site_id => p_in_srv_pa_attr_rec.location_id,
334 x_hz_location_id => l_location_id,
335 x_Return_Status => l_return_status,
336 x_Error_Message => l_error_message );
337
338 l_location_type_code := 'HZ_LOCATIONS';
339
340 END IF;
341
342 get_fa_location_id(
343 p_location_type_code => l_location_type_code,
344 p_location_id => l_location_id,
345 x_fa_location_id => l_fa_location_id,
346 x_return_status => l_return_status);
347
348 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
349 fnd_message.set_name('CSE','CSE_FA_CREATION_ATRIB_ERROR');
350 fnd_message.set_token('ASSET_ATTRIBUTE','LOCATION');
351 fnd_message.set_token('CSI_TRANSACTION_ID',p_in_srv_pa_attr_rec.transaction_id);
352 l_error_message := fnd_message.get;
353 RAISE fnd_api.g_exc_error;
354 END IF;
355
356 debug(' fa_location_id : '||l_fa_location_id);
357
358 debug('Inside API cse_ipa_trans_pkg.get_grouping_attribute');
359
360 cse_ipa_trans_pkg.get_grouping_attribute(
361 p_item_id => p_in_srv_pa_attr_rec.item_id,
362 p_organization_id => p_in_srv_pa_attr_rec.inv_master_org_id,
363 p_project_id => p_in_srv_pa_attr_rec.project_id,
364 p_fa_location_id => l_fa_location_id,
365 p_transaction_id => p_in_srv_pa_attr_rec.transaction_id,
366 p_org_id => p_in_srv_pa_attr_rec.org_id,
367 x_attribute8 => l_attribute8,
368 x_attribute9 => l_attribute9,
369 x_attribute10 => l_attribute10,
370 x_return_status => l_return_status,
371 x_error_message => l_error_message);
372
373 debug(' attribute8 : '||l_attribute8);
374 debug(' attribute9 : '||l_attribute9);
375 debug(' attribute10 : '||l_attribute10);
376
377 IF l_return_status <> fnd_api.g_ret_sts_success THEN
378 debug('CSE_IPA_TRANS_PKG.get_grouping_attribute failed : '||l_error_message);
379 RAISE fnd_api.g_exc_error;
380 END IF;
381
382 SELECT segment1, org_id -- Bug 6492235, changed to support multiple FA book
383 INTO l_project_number, l_ou_id -- Bug 6492235, changed to support multiple FA book
384 FROM pa_projects_all
385 WHERE project_id = p_in_srv_pa_attr_rec.project_id;
386
387 BEGIN
388 SELECT task_number
389 INTO l_task_number
390 FROM pa_tasks
391 WHERE task_id = p_in_srv_pa_attr_rec.task_id;
392 EXCEPTION
393 WHEN no_data_found THEN
394 l_skip_interface := TRUE;
395 END;
396
397 IF l_skip_interface = FALSE THEN
398 SELECT source_transaction_date
399 INTO l_dpis
400 FROM csi_transactions
401 WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_id;
402
403 -- Bug 6492235, added to support multiple FA book
404 l_book_type_code := fnd_profile.VALUE_SPECIFIC(
405 name => 'cse_fa_book_type_code',
406 ORG_ID => l_ou_id
407 );
408
409 l_fa_period_name := cse_asset_util_pkg.get_fa_period_name (l_book_type_code, l_dpis);
410
411 l_proj_itm_insv_qty := p_in_srv_pa_attr_rec.quantity;
412 i := 0;
413
414 FOR ei_rec IN ei_cur LOOP
415
416 debug('cursor record # '||ei_cur%rowcount);
417
418 debug(' expenditure_item_id : '||ei_rec.expenditure_item_id);
419 debug(' quantity : '||ei_rec.quantity);
420 debug(' l_proj_itm_insv_qty : '||l_proj_itm_insv_qty);
421
422 debug(' transaction_source : '||ei_rec.transaction_source);
423 debug(' system_reference4 : '||ei_rec.system_reference4);
424 debug(' system_reference5 : '||ei_rec.system_reference5);
425
426 BEGIN
427
428 -- accrual at period end cases. for accrual at receipt cases we will have system_ref5 populated
429 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' AND ei_rec.system_reference5 is null THEN
430
431 -- check if the period end accrual is done
432 l_rcv_sub_ledger_id := cse_asset_util_pkg.get_rcv_sub_ledger_id(ei_rec.system_reference4);
433 debug(' rcv_sub_ledger_id : '||l_rcv_sub_ledger_id);
434
435 IF l_rcv_sub_ledger_id is null THEN
436 debug(' rcv sub ledger id not found. receipt not accounted yet. skipping this ei to be placed in service.');
437 RAISE skip_this_ei;
438 END IF;
439
440 END IF;
441
442 IF l_proj_itm_insv_qty = 0 THEN
443 exit;
444 END IF;
445
446 IF ei_rec.quantity <= l_proj_itm_insv_qty THEN
447 l_proj_itm_insv_qty := l_proj_itm_insv_qty - ei_rec.quantity;
448 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
449 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
450 l_exp_item_rec.quantity := ei_rec.quantity;
451 l_exp_item_rec.split_flag := 'N';
452 ELSE
453 l_exp_item_rec.expenditure_item_id := ei_rec.expenditure_item_id;
454 l_exp_item_rec.expenditure_id := ei_rec.expenditure_id;
455 l_exp_item_rec.quantity := l_proj_itm_insv_qty;
456 l_exp_item_rec.split_flag := 'Y';
457 l_exp_item_rec.split_quantity := ei_rec.quantity - l_proj_itm_insv_qty;
458 END IF;
459
460 SELECT name
461 INTO l_organization_name
462 FROM hr_organization_units
463 WHERE organization_id =
464 nvl(ei_rec.override_to_organization_id, ei_rec.incurred_by_organization_id);
465
466 reversal_exp_item(
467 p_csi_txn_id => p_in_srv_pa_attr_rec.transaction_id,
468 p_organization_name => l_organization_name,
469 p_project_number => l_project_number,
470 p_task_number => l_task_number,
471 p_item_name => l_item_name,
472 p_ei_rec => ei_rec,
473 x_nl_pa_interface_rec => l_nl_pa_interface_rec);
474
475 i := i+1;
476
477 debug('reversal record # '||i);
478 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_rec;
479
480 i := i+1;
481
482 debug('capitalizable record # '||i);
483 debug(' capitalizable exp_item_id : '||l_exp_item_rec.expenditure_item_id);
484 debug(' capitalizable quantity : '||l_exp_item_rec.quantity);
485
486 SELECT csi_pa_interface_s.nextval
487 INTO l_ref_sufix
488 FROM sys.dual;
489
490 l_nl_pa_interface_tbl(i).transaction_source := ei_rec.transaction_source;
491 l_nl_pa_interface_tbl(i).batch_name := p_in_srv_pa_attr_rec.transaction_id;
492 l_nl_pa_interface_tbl(i).expenditure_ending_date := ei_rec.expenditure_ending_date;
493 l_nl_pa_interface_tbl(i).employee_number := null;
494 l_nl_pa_interface_tbl(i).organization_name := l_organization_name;
495 l_nl_pa_interface_tbl(i).expenditure_item_date := ei_rec.expenditure_item_date;
496 l_nl_pa_interface_tbl(i).project_number := l_project_number;
497 l_nl_pa_interface_tbl(i).task_number := l_task_number;
498 l_nl_pa_interface_tbl(i).expenditure_type := ei_rec.expenditure_type;
499 l_nl_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE';
500 l_nl_pa_interface_tbl(i).transaction_status_code := 'P';
501 l_nl_pa_interface_tbl(i).orig_transaction_reference
502 := p_in_srv_pa_attr_rec.instance_id||'-'||l_ref_sufix;
503 l_nl_pa_interface_tbl(i).attribute_category := NULL;
504 l_nl_pa_interface_tbl(i).attribute1 := NULL;
505 l_nl_pa_interface_tbl(i).attribute2 := NULL;
506 l_nl_pa_interface_tbl(i).attribute3 := NULL;
507 l_nl_pa_interface_tbl(i).attribute4 := NULL;
508 l_nl_pa_interface_tbl(i).attribute5 := NULL;
509 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
510 IF l_serial_code in (2, 5) THEN
511 IF l_fa_group_by = 'ITEM' THEN
512 l_nl_pa_interface_tbl(i).attribute7 := l_fa_period_name;
513 ELSE
514 l_nl_pa_interface_tbl(i).attribute7 := p_in_srv_pa_attr_rec.serial_number;
515 END IF;
516 ELSE
517 l_nl_pa_interface_tbl(i).attribute7 := l_fa_period_name;
518 END IF;
519 l_nl_pa_interface_tbl(i).attribute8 := l_attribute8;
520 l_nl_pa_interface_tbl(i).attribute9 := l_attribute9;
521 l_nl_pa_interface_tbl(i).attribute10 := l_attribute10;
522 l_nl_pa_interface_tbl(i).interface_id := NULL;
523 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
524 l_nl_pa_interface_tbl(i).org_id := ei_rec. org_id;
525 l_nl_pa_interface_tbl(i).dr_code_combination_id := ei_rec.dr_code_combination_id;
526 l_nl_pa_interface_tbl(i).cr_code_combination_id := ei_rec.cr_code_combination_id;
527 l_nl_pa_interface_tbl(i).gl_date := ei_rec.gl_date;
528 l_nl_pa_interface_tbl(i).system_linkage := ei_rec.system_linkage_function;
529 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' THEN
530 BEGIN
531 SELECT segment1
532 INTO l_nl_pa_interface_tbl(i).vendor_number
533 FROM po_vendors
534 WHERE vendor_id = ei_rec.system_reference1;
535 EXCEPTION
536 WHEN no_data_found THEN
537 l_nl_pa_interface_tbl(i).system_linkage := 'INV';
538 END;
539 END IF;
540 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
541 l_nl_pa_interface_tbl(i).cdl_system_reference1 := ei_rec.system_reference1;
542 l_nl_pa_interface_tbl(i).cdl_system_reference2 := ei_rec.system_reference2;
543 l_nl_pa_interface_tbl(i).cdl_system_reference3 := ei_rec.system_reference3;
544 l_nl_pa_interface_tbl(i).cdl_system_reference4 := ei_rec.system_reference4;
545 IF ei_rec.transaction_source = 'CSE_PO_RECEIPT' AND ei_rec.system_reference5 is NULL THEN
546 l_nl_pa_interface_tbl(i).cdl_system_reference5 := l_rcv_sub_ledger_id;
547 ELSE
548 l_nl_pa_interface_tbl(i).cdl_system_reference5 := ei_rec.system_reference5;
549 END IF;
550 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
551 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
552 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
553 l_nl_pa_interface_tbl(i).created_by := l_user_id;
554 l_nl_pa_interface_tbl(i).billable_flag := 'Y';
555 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.quantity;
556 l_nl_pa_interface_tbl(i).denom_raw_cost :=
557 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
558 l_nl_pa_interface_tbl(i).acct_raw_cost :=
559 ei_rec.unit_denom_raw_cost * l_exp_item_rec.quantity;
560 l_nl_pa_interface_tbl(i).vendor_id := ei_rec.vendor_id;
561 l_nl_pa_interface_tbl(i).inventory_item_id := ei_rec.inventory_item_id;
562 l_nl_pa_interface_tbl(i).po_line_id := ei_rec.po_line_id;
563 l_nl_pa_interface_tbl(i).project_id := ei_rec.project_id;
564 l_nl_pa_interface_tbl(i).task_id := ei_rec.task_id;
565 l_nl_pa_interface_tbl(i).document_type := ei_rec.document_type;
566 l_nl_pa_interface_tbl(i).document_distribution_type := ei_rec.document_distribution_type;
567
568 IF l_exp_item_rec.split_flag = 'Y' THEN
569 i := i + 1;
570 debug('spillover record # '||i);
571 debug(' spillover exp_item_id : '|| l_exp_item_rec.expenditure_item_id);
572 debug(' spillover quantity : '|| l_exp_item_rec.split_quantity);
573
574 l_nl_pa_interface_tbl(i) := l_nl_pa_interface_tbl(i-1);
575
576 SELECT csi_pa_interface_s.nextval
577 INTO l_ref_sufix
578 FROM sys.dual;
579
580 l_nl_pa_interface_tbl(i).orig_transaction_reference := p_in_srv_pa_attr_rec.transaction_id;
581 l_nl_pa_interface_tbl(i).attribute8 := null;
582 l_nl_pa_interface_tbl(i).attribute9 := null;
583 l_nl_pa_interface_tbl(i).attribute10 := null;
584 l_nl_pa_interface_tbl(i).quantity := l_exp_item_rec.split_quantity;
585 l_nl_pa_interface_tbl(i).denom_raw_cost :=
586 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
587 l_nl_pa_interface_tbl(i).acct_raw_cost :=
588 ei_rec.unit_denom_raw_cost * l_exp_item_rec.split_quantity;
589 exit;
590 END IF;
591
592 EXCEPTION
593 WHEN skip_this_ei THEN
594 debug('skipped this expenditure_item_id : '||ei_rec.expenditure_item_id);
595 END;
596
597 END LOOP;
598
599 debug('l_nl_pa_interface_tbl.count : '||l_nl_pa_interface_tbl.COUNT);
600
601 IF l_nl_pa_interface_tbl.COUNT > 0 THEN
602
603 debug('Inside API cse_ipa_trans_pkg.populate_pa_interface');
604
605 cse_ipa_trans_pkg.populate_pa_interface(
606 p_nl_pa_interface_tbl => l_nl_pa_interface_tbl,
607 x_return_status => l_return_status,
608 x_error_message => l_error_message);
609
610 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
611 debug('error_ message : '||l_error_message);
612 RAISE fnd_api.g_exc_error;
613 END IF;
614
615 --update transaction record with new txn_status_code = 'INTERFACE_TO_PA'
616 l_txn_rec := CSE_UTIL_PKG.init_txn_rec;
617 l_txn_rec.transaction_id := p_in_srv_pa_attr_rec.transaction_id;
618 l_txn_rec.source_group_ref_id := p_conc_request_id;
619 l_txn_rec.source_header_ref_id := p_in_srv_pa_attr_rec.project_id;
620 l_txn_rec.source_header_ref := l_project_number;
621 l_txn_rec.source_line_ref_id := p_in_srv_pa_attr_rec.task_id;
622 l_txn_rec.source_line_ref := l_task_number;
623 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_INTERFACED_TO_PA;
624 --Begin Changes for Bug 7354734
625 --l_txn_rec.object_version_number := p_in_srv_pa_attr_rec.object_version_number;
626
627 BEGIN
628 SELECT object_version_number
629 INTO l_txn_rec.object_version_number
630 FROM csi_transactions
631 WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_id;
632 EXCEPTION
633 WHEN OTHERS THEN
634 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
635 fnd_message.set_token('ERR_MSG',l_api_name||'='|| SQLERRM);
636 x_error_message := fnd_message.get;
637 x_return_status := fnd_api.g_ret_sts_unexp_error;
638 debug('Inside OTHERS in interface_nl_to_pa : ' ||x_error_message);
639 END;
640 --End Changes for Bug 7354734
641
642 debug('Inside API csi_transactions_pvt.update_transactions');
643 debug(' transaction_id : '||l_txn_rec.transaction_id);
644
645 csi_transactions_pvt.update_transactions(
646 p_api_version => l_api_version,
647 p_init_msg_list => l_init_msg_list,
648 p_commit => l_commit,
649 p_validation_level => l_validation_level,
650 p_transaction_rec => l_txn_rec,
651 x_return_status => l_return_status,
652 x_msg_count => l_msg_count,
653 x_msg_data => l_msg_data);
654
655 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
656 l_error_message := cse_util_pkg.dump_error_stack ;
657 RAISE fnd_api.g_exc_error;
658 END IF;
659 END IF;
660
661 END IF; -- End l_skip_interface
662 EXCEPTION
663 WHEN fnd_api.g_exc_error THEN
664 x_return_status := l_return_status;
665 x_error_message := l_error_message;
666 debug('error in interface_nl_to_pa : '||x_error_message);
667 WHEN OTHERS THEN
668 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
669 fnd_message.set_token('ERR_MSG',l_api_name||'='|| SQLERRM);
670 x_error_message := fnd_message.get;
671 x_return_status := fnd_api.g_ret_sts_unexp_error;
672 debug('Inside OTHERS in interface_nl_to_pa : ' ||x_error_message);
673 END interface_nl_to_pa;
674
675 PROCEDURE interface_nl_to_pa(
676 p_in_srv_pa_attr_tbl IN CSE_DATASTRUCTURES_PUB.Proj_Itm_Insv_PA_ATTR_tbl_TYPE,
677 p_conc_request_id IN NUMBER DEFAULT NULL,
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_error_message OUT NOCOPY VARCHAR2)
680 IS
681 BEGIN
682 IF NOT p_in_srv_pa_attr_tbl.COUNT = 0 THEN
683
684 FOR i IN p_in_srv_pa_attr_tbl.FIRST .. p_in_srv_pa_attr_tbl.LAST
685 LOOP
686 IF p_in_srv_pa_attr_tbl.EXISTS(i) THEN
687 interface_nl_to_pa( p_in_srv_pa_attr_tbl(i),
688 p_conc_request_id,
689 x_return_status,
690 x_error_message);
691 END IF;
692
693 END LOOP;
694 END IF; -- tbl.count IF
695 EXCEPTION
696 WHEN OTHERS THEN
697 fnd_message.set_name('CSE','CSE_OTHERS_EXCEPTION');
698 fnd_message.set_token('ERR_MSG','CSE_PROJ_ITEM_IN_SRV_PKG.Interface_Nl_To_PA'||'='|| SQLERRM);
699 x_error_message := fnd_message.get;
700 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
701 END interface_nl_to_pa;
702
703 END cse_proj_item_in_srv_pkg;