[Home] [Help]
PACKAGE BODY: APPS.CSE_COST_COLLECTOR
Source
1 PACKAGE BODY cse_cost_collector AS
2 /* $Header: CSECSTHB.pls 120.9.12010000.1 2008/07/30 05:17:16 appldev ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('CSE_DEBUG_OPTION'),'N');
5
6 PROCEDURE debug(p_message IN varchar2) IS
7 BEGIN
8 IF (l_debug = 'Y') THEN
9 cse_debug_pub.add (p_message);
10 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
11 fnd_file.put_line(fnd_file.log,p_message);
12 END IF;
13 END IF;
14 EXCEPTION
15 WHEN others THEN
16 null;
17 END debug;
18
19 PROCEDURE eib_cost_collector_stub(
20 p_transaction_id IN NUMBER,
21 p_organization_id IN NUMBER,
22 p_transaction_action_id IN NUMBER,
23 p_transaction_source_type_id IN NUMBER,
24 p_type_class IN NUMBER,
25 p_project_id IN NUMBER,
26 p_task_id IN NUMBER,
27 p_transaction_date IN DATE,
28 p_primary_quantity IN NUMBER,
29 p_cost_group_id IN NUMBER,
30 p_transfer_cost_group_id IN NUMBER,
31 p_inventory_item_id IN NUMBER,
32 p_transaction_source_id IN NUMBER,
33 p_to_project_id IN NUMBER,
34 p_to_task_id IN NUMBER,
35 p_source_project_id IN NUMBER,
36 p_source_task_id IN NUMBER,
37 p_transfer_transaction_id IN NUMBER,
38 p_primary_cost_method IN NUMBER,
39 p_acct_period_id IN NUMBER,
40 p_exp_org_id IN NUMBER,
41 p_distribution_account_id IN NUMBER,
42 p_proj_job_ind IN NUMBER,
43 p_first_matl_se_exp_type IN VARCHAR2,
44 p_inv_txn_source_literal IN VARCHAR2,
45 p_cap_txn_source_literal IN VARCHAR2,
46 p_inv_syslink_literal IN VARCHAR2,
47 p_bur_syslink_literal IN VARCHAR2,
48 p_wip_syslink_literal IN VARCHAR2,
49 p_user_def_exp_type IN NUMBER,
50 p_transfer_organization_id IN NUMBER,
51 p_flow_schedule IN VARCHAR2,
52 p_si_asset_yes_no IN NUMBER,
53 p_transfer_si_asset_yes_no IN NUMBER,
54 p_denom_currency_code IN VARCHAR2,
55 p_exp_type IN VARCHAR2,
56 p_dr_code_combination_id IN NUMBER,
57 p_cr_code_combination_id IN NUMBER,
58 p_raw_cost IN NUMBER,
59 p_burden_cost IN NUMBER,
60 p_cr_sub_ledger_id IN number default null,
61 p_dr_sub_ledger_id IN number default null,
62 p_cost_element_id IN number,
63 o_hook_used OUT NOCOPY NUMBER,
64 o_err_num OUT NOCOPY NUMBER,
65 o_err_code OUT NOCOPY NUMBER,
66 o_err_msg OUT NOCOPY NUMBER)
67 IS
68 CURSOR cse_transactions_cur (c_txn_id IN NUMBER) IS
69 SELECT cii.serial_number serial_number,
70 ct.transaction_quantity transaction_quantity,
71 ct.transaction_id transaction_id,
72 'TO_PROJECT' issue_type
73 FROM csi_item_instances cii,
74 csi_item_instances_h ciih,
75 csi_transactions ct
76 WHERE cii.instance_id = ciih.instance_id
77 AND ciih.transaction_id = ct.transaction_id
78 AND ct.inv_material_transaction_id = c_txn_id
79 AND ct.transaction_type_id IN (
80 cse_util_pkg.get_txn_type_id('MISC_ISSUE_TO_PROJECT','INV'),
81 cse_util_pkg.get_txn_type_id('MOVE_ORDER_ISSUE_TO_PROJECT','INV'))
82 AND NVL(ciih.new_location_type_code, cii.location_type_code)= 'PROJECT'
83 UNION
84 SELECT cii.serial_number serial_number,
85 ct.transaction_quantity transaction_quantity,
86 ct.transaction_id transaction_id,
87 'FROM_PROJECT' issue_type
88 FROM csi_item_instances cii,
89 csi_item_instances_h ciih,
90 csi_transactions ct
91 WHERE cii.instance_id = ciih.instance_id
92 AND ciih.transaction_id = ct.transaction_id
93 AND ct.inv_material_transaction_id = c_txn_id
94 AND ct.transaction_type_id IN(
95 cse_util_pkg.get_txn_type_id('MISC_RECEIPT_FROM_PROJECT','INV'))
96 AND NVL(ciih.new_location_type_code, cii.location_type_code) = 'INVENTORY'
97 ORDER BY 1 DESC;
98
99 CURSOR mtl_trx_cur (l_transaction_id IN NUMBER) IS
100 SELECT mmt.transaction_quantity ,
101 mtt.transaction_source_type_id,
102 mtt.type_class,
103 mtt.transaction_action_id
104 FROM mtl_material_transactions mmt,
105 mtl_trx_types_view mtt
106 WHERE mmt.transaction_id = l_transaction_id
107 AND ((mmt.transaction_action_id = 1 AND mmt.transaction_source_type_id = 4)
108 OR
109 (mmt.transaction_action_id = 1 AND mmt.transaction_source_type_id = 13)
110 OR
111 (mmt.transaction_action_id = 27 AND mmt.transaction_source_type_id = 13))
112 AND mtt.transaction_type_id = mmt.transaction_type_id
113 AND mtt.type_class = 1;
114
115 CURSOR org_name_cur (l_exp_org_id IN NUMBER) IS
116 SELECT name
117 FROM hr_organization_units hr
118 WHERE hr.organization_id = l_exp_org_id;
119
120 CURSOR proj_number_cur (l_source_project_id IN NUMBER) IS
121 SELECT segment1
122 FROM pa_projects_all
123 WHERE project_id = l_source_project_id;
124
125 CURSOR task_number_cur (l_source_task_id IN NUMBER, l_source_project_id IN NUMBER) IS
126 SELECT task_number
127 FROM pa_tasks task
128 WHERE task_id = l_source_task_id
129 AND project_id = l_source_project_id;
130
131 CURSOR item_name_cur (p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
132 SELECT concatenated_segments
133 FROM mtl_system_items_kfv
134 WHERE inventory_item_id = p_item_id
135 AND organization_id = p_organization_id;
136
137 CURSOR gl_date_cur (l_organization_id IN NUMBER, l_acct_period_id IN NUMBER) IS
138 SELECT schedule_close_date
139 FROM org_acct_periods oap
140 WHERE oap.organization_id = l_organization_id
141 AND oap.acct_period_id = l_acct_period_id;
142
143 CURSOR c_costing IS
144 SELECT NVL(FND_PROFILE.VALUE('CSE_EIB_COSTING_USED'),'Y')
145 FROM sys.dual;
146
147 l_org_name hr_all_organization_units.name%TYPE;
148 l_project_number pa_projects_all.segment1%TYPE;
149 l_task_number pa_tasks.task_number%TYPE;
150 l_eib_trackable_flag VARCHAR2(5);
151 l_item_name mtl_system_items.segment1%TYPE;
152 l_exp_item_date DATE ;
153 l_exp_end_date DATE ;
154 l_gl_date DATE ;
155 l_eib_installed VARCHAR2(1);
156 l_user_id NUMBER ;
157 l_err_msg VARCHAR2(1000);
158 l_ou_org_id NUMBER ;
159 i NUMBER :=0 ;
160 l_depreciable VARCHAR2(1);
161 l_mtl_trx_quantity NUMBER ;
162 l_Error_Message VARCHAR2(2000);
163 l_return_status VARCHAR2(1);
164 l_msg_count NUMBER;
165 l_msg_data VARCHAR2(2000);
166 l_msg_index NUMBER;
167 l_raw_cost NUMBER ;
168 l_file VARCHAR2(500);
169 l_type_class NUMBER ;
170 l_txn_source_type_id NUMBER ;
171 l_txn_action_id NUMBER ;
172 l_eib_costing_used VARCHAR2(1);
173 l_redeploy_flag VARCHAR2(1);
174
175 l_pa_interface_tbl cse_ipa_trans_pkg.nl_pa_interface_tbl_type ;
176
177 CURSOR c_Business_Group_cur( c_org_id NUMBER ) IS
178 SELECT ho.name
179 FROM hr_all_organization_units ho, hr_all_organization_units hoc
180 WHERE hoc.organization_id = c_org_id
181 AND ho.organization_id = hoc.business_group_id ;
182
183 l_Business_Group_rec c_Business_Group_cur%ROWTYPE;
184 --
185 BEGIN
186
187 cse_util_pkg.set_debug;
188
189 -- Get costing profile
190 OPEN c_costing;
191 FETCH c_costing into l_eib_costing_used;
192 CLOSE c_costing;
193
194 debug('Begin : cse_cost_collector stub '||p_transaction_id);
195
196 l_eib_installed := cse_util_pkg.is_eib_installed ;
197 i := 0 ;
198
199 IF l_eib_installed = 'Y' AND l_eib_costing_used = 'Y' THEN
200 fnd_profile.get('ORG_ID',l_ou_org_id);
201 l_txn_source_type_id := NULL ;
202
203 OPEN mtl_trx_cur(p_transaction_id) ;
204 FETCH mtl_trx_cur INTO l_mtl_trx_quantity, l_txn_source_type_id, l_type_class, l_txn_action_id;
205 CLOSE mtl_trx_cur ;
206
207 debug(' l_type_class : '||l_type_class);
208 debug(' l_txn_source_type_id : '||l_txn_source_type_id);
209 debug(' l_txn_action_id : '||l_txn_action_id);
210
211 cse_util_pkg.check_item_trackable(p_inventory_item_id, l_eib_trackable_flag);
212
213 IF l_eib_trackable_flag = 'TRUE' AND l_txn_source_type_id IS NOT NULL AND p_cost_element_id = 1 THEN
214
215 l_user_id := fnd_global.user_id ;
216
217 OPEN org_name_cur(p_exp_org_id);
218 FETCH org_name_cur INTO l_org_name ;
219 CLOSE org_name_cur ;
220
221 OPEN c_Business_Group_cur( p_exp_org_id ) ;
222 FETCH c_Business_Group_cur INTO l_Business_Group_rec;
223 CLOSE c_Business_Group_cur;
224
225 OPEN proj_number_cur(p_source_project_id);
226 FETCH proj_number_cur INTO l_project_number ;
227 CLOSE proj_number_cur ;
228
229 OPEN task_number_cur(p_source_task_id, p_source_project_id);
230 FETCH task_number_cur INTO l_task_number ;
231 CLOSE task_number_cur ;
232
233 OPEN item_name_cur(p_inventory_item_id, p_organization_id);
234 FETCH item_name_cur INTO l_item_name ;
235 CLOSE item_name_cur ;
236
237 l_exp_item_date := p_transaction_date ;
238 l_exp_end_date := pa_utils.GetWeekEnding(l_exp_item_date);
239
240 OPEN gl_date_cur(p_organization_id, p_acct_period_id);
241 FETCH gl_date_cur INTO l_gl_date ;
242 CLOSE gl_date_cur ;
243
244 cse_util_pkg.check_depreciable(p_inventory_item_id, l_depreciable) ;
245
246 FOR cse_transactions_rec IN cse_transactions_cur (p_transaction_id)
247 LOOP
248 i := i + 1;
249 debug ('Issue Type : '||cse_transactions_rec.issue_type);
250
251 IF l_depreciable = 'Y' THEN
252 l_pa_interface_tbl(i).transaction_source := 'CSE_INV_ISSUE_DEPR' ;
253 l_pa_interface_tbl(i).billable_flag := 'N' ;
254 ELSE
255 l_pa_interface_tbl(i).transaction_source := 'CSE_INV_ISSUE' ;
256 l_pa_interface_tbl(i).billable_flag := 'Y' ;
257 END IF ;
258
259 l_pa_interface_tbl(i).batch_name := NULL ;
260 l_pa_interface_tbl(i).expenditure_ending_date := NVL(l_exp_end_date,sysdate);
261 l_pa_interface_tbl(i).organization_name := l_org_name ;
262 l_pa_interface_tbl(i).expenditure_item_date := l_exp_item_date;
263 l_pa_interface_tbl(i).project_number := l_project_number;
264 l_pa_interface_tbl(i).task_number := l_task_number;
265 l_pa_interface_tbl(i).expenditure_type := p_exp_type;
266
267 IF cse_transactions_rec.serial_number IS NULL THEN
268 --Costing passes p_primary quantity as -ve for Issue to PJ
269 --and +ve for receipt from PJ. But we need it to be +ve for
270 --issue to PJ and -ve for Receipt from PJ.
271 --Costs are appropriately paased.
272
273 l_pa_interface_tbl(i).quantity := (-1)*p_primary_quantity ;
274 l_pa_interface_tbl(i).denom_raw_cost := ROUND(p_raw_cost,2);
275 l_pa_interface_tbl(i).acct_raw_cost := ROUND(p_raw_cost,2);
276 ELSE
277 l_raw_cost := ROUND((p_raw_cost/ABS(p_primary_quantity)),2);
278 --Check if this is a redeployment
279 cse_util_pkg.get_redeploy_flag(
280 p_inventory_item_id => p_inventory_item_id,
281 p_serial_number => cse_transactions_rec.serial_number,
282 p_transaction_date => p_transaction_date,
283 x_redeploy_flag => l_redeploy_flag,
284 x_return_status => l_return_status,
285 x_error_message => l_error_message) ;
286
287 debug ('Serial Number : '||cse_transactions_rec.serial_number);
288 debug ('Redeploy Flag :'||l_redeploy_flag);
289
290 IF l_redeploy_flag = 'Y' THEN
291 l_raw_cost := 0;
292 END IF ;
293
294 l_pa_interface_tbl(i).quantity := (-1)*(p_primary_quantity/ABS(p_primary_quantity)) ;
295 l_pa_interface_tbl(i).denom_raw_cost := l_raw_cost ;
296 l_pa_interface_tbl(i).acct_raw_cost := l_raw_cost ;
297 END IF ; --Serialized/Non_Serialized
298
299 l_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE' ;
300 l_pa_interface_tbl(i).transaction_status_code := 'P';
301 l_pa_interface_tbl(i).attribute6 := l_item_name ;
302 l_pa_interface_tbl(i).attribute7 := cse_transactions_rec.serial_number;
303
304 IF l_pa_interface_tbl(i).attribute7 IS NOT NULL THEN
305 l_pa_interface_tbl(i).orig_transaction_reference:=cse_transactions_rec.transaction_id||'-'||i;
306 ELSE
307 l_pa_interface_tbl(i).orig_transaction_reference := cse_transactions_rec.transaction_id||'-'||i;
308 END IF ;
309
310 l_pa_interface_tbl(i).interface_id := NULL ;
311 l_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
312 l_pa_interface_tbl(i).org_id := l_ou_org_id ;
313 l_pa_interface_tbl(i).dr_code_combination_id := p_dr_code_combination_id ;
314 l_pa_interface_tbl(i).cr_code_combination_id := p_cr_code_combination_id ;
315 l_pa_interface_tbl(i).gl_date := l_gl_date ;
316 l_pa_interface_tbl(i).system_linkage := 'INV';
317 l_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE' ;
318 l_pa_interface_tbl(i).last_update_date := SYSDATE;
319 l_pa_interface_tbl(i).last_updated_by := l_user_id;
320 l_pa_interface_tbl(i).creation_date := SYSDATE;
321 l_pa_interface_tbl(i).created_by := l_user_id ;
322 l_pa_interface_tbl(i).batch_name := 'ISSUE' ;
323 l_pa_interface_tbl(i).cdl_system_reference4 := p_cr_sub_ledger_id;
324 l_pa_interface_tbl(i).cdl_system_reference5 := p_dr_sub_ledger_id;
325 l_pa_interface_tbl(i).project_id := p_source_project_id;
326 l_pa_interface_tbl(i).task_id := p_source_task_id;
327 l_pa_interface_tbl(i).organization_id := p_organization_id;
328 l_pa_interface_tbl(i).inventory_item_id := p_inventory_item_id;
329 l_pa_interface_tbl(i).person_business_group_name := l_Business_Group_rec.name;
330 END LOOP ;
331
332 IF i > 0 THEN
333 debug('Calling Populate PA Interface..');
334 cse_ipa_trans_pkg.populate_pa_interface(l_pa_interface_tbl, l_return_status, l_error_message);
335 END IF ;
336
337 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
338 l_msg_index := 1;
339 WHILE l_msg_count > 0
340 LOOP
341 l_Error_Message := FND_MSG_PUB.GET(l_msg_index,FND_API.G_FALSE);
342 l_msg_index := l_msg_index + 1;
343 l_Msg_Count := l_Msg_Count - 1;
344 END LOOP;
345 debug('Error Occured :' || l_error_message);
346 END IF;
347
348 --NL has used the Hook.
349 O_err_num := 0;
350 O_hook_used := 1 ;
351 IF l_eib_trackable_flag = 'TRUE' THEN
352 IF i = 0 THEN
353 debug('Could not find Instances for this transaction in IB');
354 O_hook_used := 1 ;
358
355 O_err_num := -1 ;
356 END IF ;
357 END IF ;
359 ELSE
360 --As item is NOT IB trackable EIB has not used this hook.
361 O_hook_used := 0 ;
362 O_err_num := 0;
363 debug('eIB did not override cost collector ');
364 END IF ; --l_eib_tracked
365
366 ELSE
367 --EIB has not used this hook.
368 O_hook_used := 0 ;
369 O_err_num := 0;
370 END IF ; --l_eib_installed
371
372 debug('End : cse_cost_collector');
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 --This is just to notify the hook that , Nl indeed used the hook ,
377 --though it was not successful attempt. This hook should pick the transaction
378 --record when cost collection manager is run next time.
379 O_hook_used := 1 ;
380 O_err_num := -1;
381 l_err_msg := SQLERRM ;
382 debug('Error Occured :' || l_err_msg);
383 END eib_cost_collector_stub ;
384
385 ------------------------------------------------------------------------------------
386 PROCEDURE reverse_expenditures IS
387 CURSOR csi_txn_cur IS
388 SELECT ct.transaction_id
389 FROM csi_transactions ct
390 WHERE ct.transaction_status_code = 'PENDING' ;
391
392 CURSOR csi_pending_txn_cur (c_transaction_id IN NUMBER) IS
393 SELECT cii.serial_number,
394 cii.inventory_item_id,
395 cii.instance_id,
396 cii.inv_master_organization_id,
397 ct.transaction_id,
398 ct.object_version_number,
399 ct.transaction_date
400 FROM csi_item_instances cii,
401 csi_item_instances_h ciih,
402 csi_transactions ct
403 WHERE ct.transaction_id = c_transaction_id
404 AND ct.transaction_id = ciih.transaction_id
405 AND cii.instance_id = ciih.instance_id
406 AND cii.serial_number IS NOT NULL
407 AND ciih.old_inst_usage_code = 'INSTALLED'
408 AND ciih.new_location_type_code = 'INVENTORY' ;
409
410 CURSOR get_first_proj_cur (c_instance_id IN NUMBER, c_transaction_id IN NUMBER) IS
411 SELECT old_pa_project_id,
412 old_pa_project_task_id
413 FROM csi_item_instances_h ciih
414 WHERE ciih.instance_id = c_instance_id
415 ---We are looking for the immediate PROJ/TAsk info of the Receipt from Field Location transaction
416 AND ciih.transaction_id < c_transaction_id
417 AND old_location_type_code = 'PROJECT'
418 AND new_inst_usage_code = 'INSTALLED'
419 ORDER BY transaction_id DESC ;
420
421 CURSOR exp_cur (
422 c_item_name IN VARCHAR2,
423 c_serial_number IN VARCHAR2,
424 c_project_id IN NUMBER,
425 c_task_id IN NUMBER)
426 IS
427 SELECT org.name organization_name,
428 exp.expenditure_ending_date,
429 proj.segment1 project_number,
430 task.task_number,
431 item.org_id,
432 item.expenditure_type,
433 item.expenditure_item_date,
434 item.denom_currency_code,
435 item.attribute6,
436 item.attribute7,
437 item.quantity ,
438 item.raw_cost ,
439 item.denom_raw_cost ,
440 round(item.denom_raw_cost,2) unit_denom_raw_cost,
441 item.raw_cost_rate,
442 item.burden_cost,
443 round(item.burden_cost,2) burden_cost_rate,
444 dist.dr_code_combination_id,
445 dist.cr_code_combination_id,
446 dist.gl_date,
447 dist.acct_raw_cost,
448 item.transaction_source
449 FROM pa_expenditure_items_all item,
450 pa_cost_distribution_lines_all dist,
451 pa_expenditure_groups_all grp,
452 pa_expenditures_all exp,
453 pa_projects_all proj,
454 pa_tasks task,
455 hr_organization_units org
456 WHERE org.organization_id = NVL(item.override_to_organization_id,
457 exp.incurred_by_organization_id)
458 AND NVL(dist.reversed_flag, 'N') <> 'Y'
459 AND dist.cr_code_combination_id IS NOT NULL
460 AND dist.dr_code_combination_id IS NOT NULL
461 AND dist.line_type = 'R'
462 AND item.expenditure_item_id = dist.expenditure_item_id
463 AND grp.transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
464 AND grp.expenditure_group = exp.expenditure_group
465 AND exp.expenditure_id = item.expenditure_id
466 AND item.attribute6 = c_item_name
467 AND NVL(item.attribute7, 'xyz') = c_serial_number
468 AND item.attribute8 IS NULL
469 AND item.attribute9 IS NULL
470 AND item.attribute10 IS NULL
471 AND item.billable_flag = 'Y'
472 AND task.project_id = c_project_id
473 AND item.task_id = c_task_id
474 AND task.task_id=item.task_id
475 AND proj.project_id = task.project_id ;
476
477 CURSOR c_Business_Group_cur( c_org_id NUMBER ) IS
478 SELECT ho.name
479 FROM hr_all_organization_units ho, hr_all_organization_units hoc
480 WHERE hoc.organization_id = c_org_id
484 l_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
481 AND ho.organization_id = hoc.business_group_id ;
482 l_Business_Group_rec c_Business_Group_cur%ROWTYPE;
483
485 l_serial_number VARCHAR2(30);
486 l_project_id NUMBER;
487 l_task_id NUMBER;
488 l_ref_suffix NUMBER;
489 l_sysdate DATE;
490 l_user_id NUMBER;
491 i NUMBER;
492 l_nl_pa_interface_tbl CSE_IPA_TRANS_PKG.nl_pa_interface_tbl_type;
493 l_return_status VARCHAR2(1);
494 l_error_message VARCHAR2(2000);
495 e_next EXCEPTION ;
496 l_api_version NUMBER := 1.0;
497 l_commit VARCHAR2(1) := fnd_api.G_FALSE;
498 l_init_msg_list VARCHAR2(1) := fnd_api.G_TRUE;
499 l_validation_level NUMBER := fnd_api.G_VALID_LEVEL_FULL;
500 l_msg_count NUMBER ;
501 l_msg_data VARCHAR2(2000);
502 l_msg_index PLS_INTEGER;
503 l_txn_rec csi_datastructures_pub.transaction_rec;
504 l_txn_obj_ver NUMBER ;
505 l_redeploy_flag VARCHAR2(1);
506 l_depreciable VARCHAR2(1);
507 l_txn_processed BOOLEAN ;
508
509 BEGIN
510 cse_util_pkg.write_log('Reversing the expenditures, for serialized items which are
511 being returned from Installed location');
512
513 l_user_id := fnd_global.user_id ;
514 SELECT sysdate INTO l_sysdate FROM sys.dual ;
515 i := 0;
516 FOR csi_txn_rec IN csi_txn_cur
517 LOOP
518 i := 0 ;
519 l_nl_pa_interface_tbl.DELETE ;
520 l_txn_processed := FALSE;
521 BEGIN
522 SAVEPOINT A ;
523 FOR csi_pending_txn_rec IN csi_pending_txn_cur(csi_txn_rec.transaction_id)
524 LOOP
525 i := i+1 ;
526 l_txn_obj_ver := csi_pending_txn_rec.object_version_number ;
527
528 cse_util_pkg.write_log('Processing Serial Number : '|| csi_pending_txn_rec.serial_number);
529
530 cse_util_pkg.check_depreciable(
531 p_inventory_item_id => csi_pending_txn_rec.inventory_item_id,
532 p_depreciable => l_depreciable) ;
533
534 cse_util_pkg.write_log('l_depreciable :'|| l_depreciable);
535 IF l_depreciable = 'N' THEN
536 cse_util_pkg.get_redeploy_flag(
537 p_inventory_item_id => csi_pending_txn_rec.inventory_item_id
538 ,p_serial_number => csi_pending_txn_rec.serial_number
539 ,p_transaction_date => csi_pending_txn_rec.transaction_date
540 ,x_redeploy_flag => l_redeploy_flag
541 ,x_return_status => l_return_status
542 ,x_error_message => l_error_message);
543
544 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
545 RAISE e_next ;
546 END IF ;
547 cse_util_pkg.write_log('Redeploy Flag :'|| l_redeploy_flag);
548 END IF ; --l_depreciable
549
550 IF l_redeploy_flag = 'N' AND l_depreciable = 'N' THEN
551 IF i = 1 THEN
552 SELECT concatenated_segments
553 INTO l_item_name
554 FROM mtl_system_items_kfv
555 WHERE inventory_item_id = csi_pending_txn_rec.inventory_item_id
556 AND organization_id = csi_pending_txn_rec.inv_master_organization_id
557 AND ROWNUM = 1;
558 END IF ;
559
560 OPEN get_first_proj_cur(csi_pending_txn_rec.instance_id,csi_pending_txn_rec.transaction_id);
561 FETCH get_first_proj_cur INTO l_project_id, l_task_id ;
562 CLOSE get_first_proj_cur ;
563
564 cse_util_pkg.write_log('Project ID :'|| l_project_id);
565 cse_util_pkg.write_log('Task ID :'|| l_task_id);
566 cse_util_pkg.write_log('Item Name : '|| l_item_name);
567
568 FOR exp_rec in exp_cur(l_item_name , csi_pending_txn_rec.serial_number, l_project_id, l_task_id)
569 LOOP
570
571 SELECT csi_pa_interface_s.NEXTVAL
572 INTO l_ref_suffix
573 FROM DUAL;
574 l_txn_processed := TRUE ;
575
576 OPEN c_Business_Group_cur( exp_rec.org_id ) ;
577 FETCH c_Business_Group_cur INTO l_Business_Group_rec;
578 CLOSE c_Business_Group_cur;
579
580
581 l_nl_pa_interface_tbl(i).transaction_source := exp_rec.transaction_source ;
582 l_nl_pa_interface_tbl(i).batch_name:=csi_pending_txn_rec.transaction_id;
583 l_nl_pa_interface_tbl(i).expenditure_ending_date := exp_rec.expenditure_ending_date;
584 l_nl_pa_interface_tbl(i).employee_number :=Null;
585 l_nl_pa_interface_tbl(i).organization_name := exp_rec.organization_name;
586 l_nl_pa_interface_tbl(i).expenditure_item_date := exp_rec.expenditure_item_date;
587 l_nl_pa_interface_tbl(i).project_number:=exp_rec.project_number;
588 l_nl_pa_interface_tbl(i).task_number := exp_rec.task_number;
589 l_nl_pa_interface_tbl(i).expenditure_type := exp_rec.expenditure_type;
590 l_nl_pa_interface_tbl(i).expenditure_comment := 'ENTERPRISE INSTALL BASE';
591 l_nl_pa_interface_tbl(i).transaction_status_code := 'P';
592 l_nl_pa_interface_tbl(i).orig_transaction_reference
593 := csi_pending_txn_rec.instance_id||'-'||l_ref_suffix;
594 l_nl_pa_interface_tbl(i).attribute_category := NULL;
595 l_nl_pa_interface_tbl(i).attribute6 := l_item_name;
596 l_nl_pa_interface_tbl(i).attribute7 := csi_pending_txn_rec.serial_number ;
597 l_nl_pa_interface_tbl(i).interface_id := NULL;
598 l_nl_pa_interface_tbl(i).unmatched_negative_txn_flag := 'Y';
599 l_nl_pa_interface_tbl(i).org_id := exp_rec.org_id;
600 l_nl_pa_interface_tbl(i).dr_code_combination_id
601 := exp_rec.dr_code_combination_id;
602 l_nl_pa_interface_tbl(i).cr_code_combination_id
603 := exp_rec.cr_code_combination_id;
604 l_nl_pa_interface_tbl(i).cdl_system_reference1 := NULL;
605 l_nl_pa_interface_tbl(i).cdl_system_reference2 := NULL;
606 l_nl_pa_interface_tbl(i).cdl_system_reference3 := NULL;
607 l_nl_pa_interface_tbl(i).gl_date := exp_rec.gl_date;
608 l_nl_pa_interface_tbl(i).system_linkage := 'INV';
609 l_nl_pa_interface_tbl(i).user_transaction_source := 'ENTERPRISE INSTALL BASE';
610 l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
611 l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
612 l_nl_pa_interface_tbl(i).creation_date := l_sysdate;
613 l_nl_pa_interface_tbl(i).created_by := l_user_id;
614 l_nl_pa_interface_tbl(i).billable_flag := 'N';
615 l_nl_pa_interface_tbl(i).quantity := -1;
616 l_nl_pa_interface_tbl(i).denom_raw_cost := (-1)*exp_rec.unit_denom_raw_cost ;
617 l_nl_pa_interface_tbl(i).acct_raw_cost:= (-1)*exp_rec.unit_denom_raw_cost ;
618 l_nl_pa_interface_tbl(i).person_business_group_name := l_Business_Group_rec.name;
619 END LOOP ; ---exp_cur
620 END IF ; ---l_redeploy_flag
621 END LOOP ; --csi_pending_txn_cur
622
623 IF l_txn_processed AND i > 0 THEN
624 cse_util_pkg.write_log('Calling cse_ipa_trans_pkg.populate_pa_interface, number of recs: '||i);
625 CSE_IPA_TRANS_PKG.populate_pa_interface(
626 p_nl_pa_interface_tbl => l_nl_pa_interface_tbl,
627 x_return_status => l_return_status,
628 x_error_message => l_error_message);
629
630 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
631 RAISE e_next ;
632 END IF;
633
634 --Update the transaction Status Here
635
636 cse_util_pkg.write_log('Now updating the CSI txn to COMPLETE');
637 l_txn_rec := CSE_UTIL_PKG.init_txn_rec;
638 l_txn_rec.transaction_id := csi_txn_rec.transaction_id ;
639 l_txn_rec.transaction_status_code := CSE_DATASTRUCTURES_PUB.G_COMPLETE ;
640 l_txn_rec.object_version_number := l_txn_obj_ver ;
641
642 csi_transactions_pvt.update_transactions(
643 p_api_version => l_api_version
644 ,p_init_msg_list => l_init_msg_list
645 ,p_commit => l_commit
646 ,p_validation_level => l_validation_level
647 ,p_transaction_rec => l_txn_rec
648 ,x_return_status => l_return_status
649 ,x_msg_count => l_msg_count
650 ,x_msg_data => l_msg_data);
651
652 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
653 l_msg_index := 1;
654 l_error_message := l_msg_data;
655 WHILE l_msg_count > 0
656 LOOP
657 l_error_message := FND_MSG_PUB.get(l_msg_index, FND_API.G_FALSE) || l_error_message;
658 l_msg_index := l_msg_index + 1;
659 l_msg_count := l_msg_count - 1;
660 END LOOP;
661 RAISE e_next;
662 END IF;
663 COMMIT ;
664 END IF ; ----l_txn_processed.
665 EXCEPTION
666 WHEN e_next THEN
667 cse_util_pkg.write_log('Call to cse_ipa_trans_pkg.populate_pa_interface failed :'||l_error_message);
668 ROLLBACK TO A ;
669 END ; --csi_txn_cur
670 END LOOP ; --csi_txn_cur
671 EXCEPTION
672 WHEN OTHERS THEN null;
673 END reverse_expenditures;
674
675 END cse_cost_collector;