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