[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_OPERATION_TXN_PVT
Source
1 PACKAGE BODY EAM_ASSET_OPERATION_TXN_PVT AS
2 /* $Header: EAMVACHB.pls 120.13.12020000.2 2012/07/05 10:55:32 vpasupur ship $ */
3 /*#
4 * This package is used for the ASSET CHECKIN/CHECKOUT transaction logging AND validation .
5 * It defines procedures which take quality collection plans and meter readings as input
6 * during checkin/checkoutand perform the respective operations.
7 */
8
9
10
11 -- This function returns the employeeid who checked-in the instance for the last transaction
12
13 FUNCTION get_created_by(
14 p_instance_id IN number)
15 return NUMBER
16 AS
17
18 l_created_by number;
19
20 BEGIN
21
22 SELECT
23 created_by
24 INTO l_created_by
25 FROM eam_asset_operation_txn
26 WHERE instance_id=p_instance_id
27 AND txn_date =
28 (
29 SELECT
30 max(txn_date)
31 FROM eam_asset_operation_txn eaot
32 WHERE eaot.instance_id=p_instance_id
33 );
34
35
36 IF SQL%NOTFOUND THEN
37 return null;
38 END IF;
39 return l_created_by;
40
41 END get_created_by;
42
43
44 --This procedure accepts the transaction details from CheckIn/CheckOut UI
45
46 PROCEDURE process_checkinout_txn(
47
48 p_api_version IN number := 1.0,
49 p_init_msg_list IN varchar2 := fnd_api.g_false,
50 p_commit IN varchar2 := fnd_api.g_false,
51 p_validation_level IN number := fnd_api.g_valid_level_full,
52 p_txn_date IN date := sysdate,
53 p_txn_type IN number,
54 p_instance_id IN number,
55 p_comments IN varchar2 := NULL,
56 p_qa_collection_id IN number := NULL,
57 p_operable_flag IN number,
58 p_employee_id IN number,
59 p_attribute_category IN varchar2 := NULL,
60 p_attribute1 IN varchar2 := NULL,
61 p_attribute2 IN varchar2 := NULL,
62 p_attribute3 IN varchar2 := NULL,
63 p_attribute4 IN varchar2 := NULL,
64 p_attribute5 IN varchar2 := NULL,
65 p_attribute6 IN varchar2 := NULL,
66 p_attribute7 IN varchar2 := NULL,
67 p_attribute8 IN varchar2 := NULL,
68 p_attribute9 IN varchar2 := NULL,
69 p_attribute10 IN varchar2 := NULL,
70 p_attribute11 IN varchar2 := NULL,
71 p_attribute12 IN varchar2 := NULL,
72 p_attribute13 IN varchar2 := NULL,
73 p_attribute14 IN varchar2 := NULL,
74 p_attribute15 IN varchar2 := NULL,
75 x_return_status OUT NOCOPY varchar2,
76 x_msg_count OUT NOCOPY number,
77 x_msg_data OUT NOCOPY varchar2
78 )
79
80 IS
81
82 l_eam_ops_quality_tbl eam_asset_operation_txn_pub.eam_quality_tbl_type;
83 l_eam_meter_reading_tbl eam_asset_operation_txn_pub.meter_reading_rec_tbl_type;
84 l_eam_counter_properties_tbl eam_asset_operation_txn_pub.Ctr_Property_readings_Tbl;
85 g_pkg_name CONSTANT varchar2(30) := 'EAM_ASSET_OPERATION_TXN_PVT';
86 l_api_name constant varchar2(30) := 'process_checkinout_txn';
87 l_api_version constant number := 1.0;
88
89
90
91 BEGIN
92
93 -- Standard Start of API savepoint
94 SAVEPOINT EAM_ASSET_OPERATION_TXN_PVT;
95
96 -- Standard call to check for call compatibility.
97 IF NOT fnd_api.compatible_api_call(
98 l_api_version
99 ,p_api_version
100 ,l_api_name
101 ,g_pkg_name) THEN
102 RAISE fnd_api.g_exc_unexpected_error;
103 END IF;
104
105 IF fnd_api.to_boolean(p_init_msg_list) THEN
106 fnd_msg_pub.initialize;
107 END IF;
108
109 x_return_status := fnd_api.g_ret_sts_success;
110
111 EAM_ASSET_OPERATION_TXN_PVT.insert_txn(
112 p_txn_date => p_txn_date,
113 p_txn_type => p_txn_type,
114 p_instance_id => p_instance_id,
115 p_comments => p_comments,
116 p_qa_collection_id => p_qa_collection_id,
117 p_operable_flag => p_operable_flag,
118 p_employee_id => p_employee_id,
119 p_eam_ops_quality_tbl => l_eam_ops_quality_tbl,
120 p_meter_reading_rec_tbl => l_eam_meter_reading_tbl,
121 p_counter_properties_tbl => l_eam_counter_properties_tbl,
122 p_attribute_category => p_attribute_category,
123 p_attribute1 => p_attribute1,
124 p_attribute2 => p_attribute2,
125 p_attribute3 => p_attribute3,
126 p_attribute4 => p_attribute4,
127 p_attribute5 => p_attribute5,
128 p_attribute6 => p_attribute6,
129 p_attribute7 => p_attribute7,
130 p_attribute8 => p_attribute8,
131 p_attribute9 => p_attribute9,
132 p_attribute10 => p_attribute10,
133 p_attribute11 => p_attribute11,
134 p_attribute12 => p_attribute12,
135 p_attribute13 => p_attribute13,
136 p_attribute14 => p_attribute14,
137 p_attribute15 => p_attribute15,
138 x_return_status => x_return_status,
139 x_msg_count => x_msg_count,
140 x_msg_data => x_msg_data
141 );
142
143 IF x_return_status <> fnd_api.g_ret_sts_success THEN
144 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT;
145 RETURN;
146 END IF;
147
148 -- Standard check of p_commit.
149 IF fnd_api.to_boolean(p_commit) THEN
150 COMMIT WORK;
151 END IF;
152 fnd_msg_pub.count_and_get(
153 p_count => x_msg_count
154 ,p_data => x_msg_data);
155 EXCEPTION
156 WHEN fnd_api.g_exc_error THEN
157 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT;
158 x_return_status := fnd_api.g_ret_sts_error;
159 fnd_msg_pub.count_and_get(
160 p_count => x_msg_count
161 ,p_data => x_msg_data);
162 WHEN fnd_api.g_exc_unexpected_error THEN
163 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT;
164 x_return_status := fnd_api.g_ret_sts_unexp_error;
165 fnd_msg_pub.count_and_get(
166 p_count => x_msg_count
167 ,p_data => x_msg_data);
168 WHEN OTHERS THEN
169 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT;
170 x_return_status := fnd_api.g_ret_sts_unexp_error;
171
172 IF fnd_msg_pub.check_msg_level(
173 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
174 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
175 END IF;
176
177 fnd_msg_pub.count_and_get(
178 p_count => x_msg_count,
179 p_data => x_msg_data);
180
181
182 END process_checkinout_txn;
183
184
185 -- This Procedure calls the quality api to insert quality plans
186
187 PROCEDURE insert_quality_plans
188 (
189
190 p_eam_ops_quality_tbl IN eam_asset_operation_txn_pub.eam_quality_tbl_type,
191 p_instance_id IN number,
192 p_txn_date IN date,
193 p_comments IN varchar2,
194 p_operable_flag IN number,
195 p_organization_id IN number,
196 p_employee_id IN number,
197 p_asset_group_id IN number,
198 p_asset_number IN varchar2,
199 p_asset_instance_number IN varchar2,
200 p_txn_number IN number,
201 x_return_status OUT NOCOPY varchar2,
202 x_msg_count OUT NOCOPY number,
203 x_msg_data OUT NOCOPY varchar2
204 )
205
206 IS
207
208 Type header_plan_id_tbl_type is table of NUMBER
209 INDEX BY BINARY_INTEGER;
210
211 TYPE num_table is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
212
213 l_header_plan_id_tbl header_plan_id_tbl_type;
214 l_flag boolean;
215 l_header_counter NUMBER :=0;
216 l_eam_ops_quality_rec EAM_ASSET_OPERATION_TXN_PUB.eam_quality_tbl_type;
217 l_elements qa_validation_api.ElementsArray;
218 l_org_id NUMBER;
219 l_collection_id NUMBER;
220 l_temp_occurence NUMBER;
221 l_assetops_return_status VARCHAR2(1);
222 l_plan_name VARCHAR2(255);
223 l_error_array qa_validation_api.ErrorArray;
224 l_message_array qa_validation_api.MessageArray;
225 l_action_result VARCHAR2(1);
226 l_mandatory_qua_plan VARCHAR2(1);
227 l_context_values VARCHAR2(2000);
228 l_asset_group VARCHAR2(2000);
229 l_planid_tbl num_table;
230 l_count number :=1;
231 l_list_of_plans varchar2(1000);
232 eaot_api_call_error EXCEPTION;
233
234 BEGIN
235
236
237 SAVEPOINT eaot_insert_quality_plans;
238
239 IF (p_eam_ops_quality_tbl.count >0) THEN
240 l_org_id := p_eam_ops_quality_tbl(p_eam_ops_quality_tbl.FIRST).organization_id;
241 END IF;
242
243
244 -- following loops gets the different plan ids that are in the data
245 -- for 3 collection plans there will be 3 different plan ids
246 -- after this loop header_id_tbl table will contain the list of plan ids
247 IF (p_eam_ops_quality_tbl.count >0) THEN
248
249
250 FOR i_counter in p_eam_ops_quality_tbl.first..p_eam_ops_quality_tbl.last LOOP
251 l_flag:=TRUE;
252 l_eam_ops_quality_rec(0) := p_eam_ops_quality_tbl(i_counter);
253
254
255
256 IF l_header_plan_id_tbl.COUNT > 0 THEN
257 FOR J in l_header_plan_id_tbl.FIRST..l_header_plan_id_tbl.LAST LOOP
258
259 IF l_eam_ops_quality_rec(0).plan_id = l_header_plan_id_tbl(j) THEN
260 l_flag := FALSE;
261
262 END IF;
263
264 END LOOP;
265 END IF;
266
267 IF l_flag = TRUE THEN
268 IF l_header_plan_id_tbl.COUNT > 0 THEN
269 l_header_plan_id_tbl(l_header_plan_id_tbl.COUNT + 1) := l_eam_ops_quality_rec(0).plan_id;
270 ELSE
271 l_header_plan_id_tbl(1) := l_eam_ops_quality_rec(0).plan_id;
272 END IF;
273 END IF;
274
275 END LOOP;
276
277 END IF;
278
279
280
281
282
283 FOR plan_id IN l_header_plan_id_tbl.FIRST..l_header_plan_id_tbl.LAST loop
284
285 l_elements.delete;
286
287 FOR results in p_eam_ops_quality_tbl.first..p_eam_ops_quality_tbl.last LOOP
288 IF p_eam_ops_quality_tbl(results).PLAN_ID = l_header_plan_id_tbl(plan_id) THEN
289 l_elements(p_eam_ops_quality_tbl(results).ELEMENT_ID).id := p_eam_ops_quality_tbl(results).ELEMENT_ID;
290 l_elements(p_eam_ops_quality_tbl(results).ELEMENT_ID).value := p_eam_ops_quality_tbl(results).ELEMENT_VALUE;
291 l_collection_id := p_eam_ops_quality_tbl(results).collection_id;
292 IF (l_collection_id is null)
293 THEN
294 select qa_collection_id_s.nextval into l_collection_id from dual;
295 END IF;
296 END IF;
297 END LOOP;
298
299
300 qa_results_pub.insert_row(
301 p_api_version => 1.0,
302 p_init_msg_list => fnd_api.g_true,
303 p_org_id => l_org_id,
304 p_plan_id => l_header_plan_id_tbl(plan_id),
305 p_spec_id => null,
306 p_transaction_number =>p_eam_ops_quality_tbl(0).transaction_number ,
307 p_transaction_id => null,
308 p_enabled_flag => 1,
309 p_commit => fnd_api.g_false,
310 x_collection_id => l_collection_id,
311 x_occurrence => l_temp_occurence,
312 x_row_elements => l_elements,
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data,
315 x_error_array => l_error_array,
316 x_message_array => l_message_array,
317 x_return_status => x_return_status,
318 x_action_result => l_action_result
319 );
320
321 IF
322 x_return_status <> fnd_api.g_ret_sts_success
323 THEN
324 raise eaot_api_call_error;
325 END IF;
326
327 BEGIN
328 select qp.name into l_plan_name
329 from qa_plans qp,
330 qa_results qr
331 where
332 qr.collection_id = l_collection_id
333 and
334 qr.plan_id = qp.plan_id;
335
336
337 EAM_ASSET_LOG_PVT.INSERT_ROW(
338 p_api_version => 1.0,
339 p_init_msg_list => fnd_api.g_true,
340 p_commit => fnd_api.g_false,
341 p_event_date => p_txn_date,
342 p_event_type => 'EAM_SYSTEM_EVENTS',
343 p_event_id => 12,
344 p_instance_id => p_instance_id,
345 p_organization_id => p_organization_id,
346 p_employee_id => p_employee_id,
347 p_comments => p_comments,
348 p_reference => l_plan_name,
349 p_ref_id => l_collection_id,
350 p_operable_flag => p_operable_flag,
351 x_return_status => l_assetops_return_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data
354 );
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 null;
358 END;
359
360 IF
361 x_return_status <> fnd_api.g_ret_sts_success
362 THEN
363 raise eaot_api_call_error;
364 END IF;
365
366 END LOOP;
367
368 l_context_values := '162='||p_asset_group_id||'@163='
369 ||p_asset_number||'@2147483550='||
370 p_instance_id;
371
372 qa_web_txn_api.post_background_results
373 (
374 p_txn_number => p_txn_number
375 ,p_org_id => p_organization_id
376 ,p_context_values => l_context_values
377 ,p_collection_id => l_collection_id
378 );
379
380 qa_result_grp.enable_and_fire_action(
381 p_api_version => 1.0 ,
382 p_collection_id => l_collection_id,
383 x_return_status => x_return_status,
384 x_msg_count => x_msg_count ,
385 x_msg_data => x_msg_data
386 );
387
388 SELECT distinct plan_id
389 bulk collect into
390 l_planid_tbl
391 from
392 QA_RESULTS
393 where
394 collection_id = l_collection_id;
395
396 IF l_planid_tbl.COUNT>0 THEN
397 l_list_of_plans := '@'|| l_planid_tbl(l_planid_tbl.FIRST)||'@';
398 FOR l_count in l_planid_tbl.FIRST+1..l_planid_tbl.LAST
399 LOOP
400 l_list_of_plans := l_list_of_plans||'@'||l_planid_tbl(l_count)||'@';
401 END LOOP;
402 ELSE
403 l_list_of_plans := '@'||'@';
404 END IF;
405
406 SELECT
407 DISTINCT concatenated_segments
408 INTO
409 l_asset_group
410 FROM mtl_system_items_kfv
411 WHERE
412 inventory_item_id = p_asset_group_id;
413
414 l_mandatory_qua_plan := qa_web_txn_api.quality_mandatory_plans_remain
415 (
416 p_txn_number => p_txn_number
417 ,p_organization_id => p_organization_id
418 ,pk1 => l_asset_group
419 ,pk2 => p_asset_number
420 ,pk6 => p_asset_instance_number
421 ,p_collection_id => l_collection_id
422 ,p_list_of_plans => l_list_of_plans
423 );
424 IF l_mandatory_qua_plan = 'Y' THEN
425 fnd_message.set_name
426 ( application => 'EAM'
427 , name => 'EAM_WC_QA_REMAIN'
428 );
429
430 fnd_msg_pub.add;
431 x_return_status:= fnd_api.g_ret_sts_error;
432
433 fnd_msg_pub.count_and_get(
434 p_count => x_msg_count
435 ,p_data => x_msg_data
436 );
437 raise eaot_api_call_error;
438 END IF;
439
440 IF
441 x_return_status <> fnd_api.g_ret_sts_success
442 THEN
443 raise eaot_api_call_error;
444 END IF;
445
446
447
448 EXCEPTION
449 WHEN eaot_api_call_error THEN
450 ROLLBACK TO eaot_insert_quality_plans;
451
452 END insert_quality_plans;
453
454
455 -- This Procedure calls the meter reading api to insert meter readings
456
457 PROCEDURE insert_meter_readings
458 (
459 p_eam_meter_reading_tbl IN eam_asset_operation_txn_pub.meter_reading_rec_tbl_type,
460 p_counter_properties_tbl IN eam_asset_operation_txn_pub.Ctr_Property_readings_Tbl,
461 p_instance_id IN number,
462 p_txn_id IN number,
463 x_return_status OUT NOCOPY varchar2,
464 x_msg_count OUT NOCOPY number,
465 x_msg_data OUT NOCOPY varchar2
466 )
467
468 IS
469 l_counter_properties_tbl EAM_MeterReading_PUB.Ctr_Property_readings_Tbl;
470 l_meter_reading_rec EAM_MeterReading_PUB.Meter_Reading_Rec_Type;
471 x_meter_reading_id number;
472 l_count number;
473 eaot_api_call_error EXCEPTION;
474 BEGIN
475
476 SAVEPOINT eaot_insert_meter_readings;
477
478
479 for meter_count in p_eam_meter_reading_tbl.FIRST..p_eam_meter_reading_tbl.LAST LOOP
480 IF p_eam_meter_reading_tbl(meter_count).instance_id = p_instance_id THEN
481 l_meter_reading_rec.meter_id := p_eam_meter_reading_tbl(meter_count).meter_id;
482 l_meter_reading_rec.meter_reading_id := p_eam_meter_reading_tbl(meter_count).meter_reading_id;
483 l_meter_reading_rec.current_reading := p_eam_meter_reading_tbl(meter_count).current_reading;
484 l_meter_reading_rec.current_reading_date := p_eam_meter_reading_tbl(meter_count).current_reading_date;
485 l_meter_reading_rec.reset_flag := p_eam_meter_reading_tbl(meter_count).reset_flag;
486 l_meter_reading_rec.description := p_eam_meter_reading_tbl(meter_count).description;
487 l_meter_reading_rec.wip_entity_id := p_eam_meter_reading_tbl(meter_count).wip_entity_id;
488 l_meter_reading_rec.check_in_out_type := p_eam_meter_reading_tbl(meter_count).check_in_out_type;
489 l_meter_reading_rec.check_in_out_txn_id := p_txn_id;
490 l_meter_reading_rec.instance_id := p_eam_meter_reading_tbl(meter_count).instance_id;
491 l_meter_reading_rec.source_line_id := p_eam_meter_reading_tbl(meter_count).source_line_id;
492 l_meter_reading_rec.source_code := p_eam_meter_reading_tbl(meter_count).source_code;
493 l_meter_reading_rec.wo_entry_fake_flag := p_eam_meter_reading_tbl(meter_count).wo_entry_fake_flag;
494 l_meter_reading_rec.adjustment_type := p_eam_meter_reading_tbl(meter_count).adjustment_type;
495 l_meter_reading_rec.adjustment_reading := p_eam_meter_reading_tbl(meter_count).adjustment_reading;
496 l_meter_reading_rec.net_reading := p_eam_meter_reading_tbl(meter_count).net_reading;
497 l_meter_reading_rec.reset_reason := p_eam_meter_reading_tbl(meter_count).reset_reason;
498 l_meter_reading_rec.attribute_category := p_eam_meter_reading_tbl(meter_count).attribute_category;
499 l_meter_reading_rec.attribute1 := p_eam_meter_reading_tbl(meter_count).attribute1;
500 l_meter_reading_rec.attribute2 := p_eam_meter_reading_tbl(meter_count).attribute2;
501 l_meter_reading_rec.attribute3 := p_eam_meter_reading_tbl(meter_count).attribute3;
502 l_meter_reading_rec.attribute4 := p_eam_meter_reading_tbl(meter_count).attribute4;
503 l_meter_reading_rec.attribute5 := p_eam_meter_reading_tbl(meter_count).attribute5;
504 l_meter_reading_rec.attribute6 := p_eam_meter_reading_tbl(meter_count).attribute6;
505 l_meter_reading_rec.attribute7 := p_eam_meter_reading_tbl(meter_count).attribute7;
506 l_meter_reading_rec.attribute8 := p_eam_meter_reading_tbl(meter_count).attribute8;
507 l_meter_reading_rec.attribute9 := p_eam_meter_reading_tbl(meter_count).attribute9;
508 l_meter_reading_rec.attribute10 := p_eam_meter_reading_tbl(meter_count).attribute10;
509 l_meter_reading_rec.attribute11 := p_eam_meter_reading_tbl(meter_count).attribute11;
510 l_meter_reading_rec.attribute12 := p_eam_meter_reading_tbl(meter_count).attribute12;
511 l_meter_reading_rec.attribute13 := p_eam_meter_reading_tbl(meter_count).attribute13;
512 l_meter_reading_rec.attribute14 := p_eam_meter_reading_tbl(meter_count).attribute14;
513 l_meter_reading_rec.attribute15 := p_eam_meter_reading_tbl(meter_count).attribute15;
514 l_meter_reading_rec.attribute16 := p_eam_meter_reading_tbl(meter_count).attribute16;
515 l_meter_reading_rec.attribute17 := p_eam_meter_reading_tbl(meter_count).attribute17;
516 l_meter_reading_rec.attribute18 := p_eam_meter_reading_tbl(meter_count).attribute18;
517 l_meter_reading_rec.attribute19 := p_eam_meter_reading_tbl(meter_count).attribute19;
518 l_meter_reading_rec.attribute20 := p_eam_meter_reading_tbl(meter_count).attribute20;
519 l_meter_reading_rec.attribute21 := p_eam_meter_reading_tbl(meter_count).attribute21;
520 l_meter_reading_rec.attribute22 := p_eam_meter_reading_tbl(meter_count).attribute22;
521 l_meter_reading_rec.attribute23 := p_eam_meter_reading_tbl(meter_count).attribute23;
522 l_meter_reading_rec.attribute24 := p_eam_meter_reading_tbl(meter_count).attribute24;
523 l_meter_reading_rec.attribute25 := p_eam_meter_reading_tbl(meter_count).attribute25;
524 l_meter_reading_rec.attribute26 := p_eam_meter_reading_tbl(meter_count).attribute26;
525 l_meter_reading_rec.attribute27 := p_eam_meter_reading_tbl(meter_count).attribute27;
526 l_meter_reading_rec.attribute28 := p_eam_meter_reading_tbl(meter_count).attribute28;
527 l_meter_reading_rec.attribute29 := p_eam_meter_reading_tbl(meter_count).attribute29;
528 l_meter_reading_rec.attribute30 := p_eam_meter_reading_tbl(meter_count).attribute30;
529
530 l_count :=1;
531 l_counter_properties_tbl.DELETE;
532
533 IF p_counter_properties_tbl.COUNT > 0 THEN
534
535 for prpr_count in p_counter_properties_tbl.FIRST..p_counter_properties_tbl.LAST LOOP
536 IF l_meter_reading_rec.meter_id=p_counter_properties_tbl(prpr_count).counter_id THEN
537
538
539 l_counter_properties_tbl(l_count).counter_property_id := p_counter_properties_tbl(prpr_count).counter_property_id;
540 l_counter_properties_tbl(l_count).property_value := p_counter_properties_tbl(prpr_count).property_value;
541 l_counter_properties_tbl(l_count).value_timestamp := p_counter_properties_tbl(prpr_count).value_timestamp;
542 l_counter_properties_tbl(l_count).attribute_category := p_counter_properties_tbl(prpr_count).attribute_category;
543 l_counter_properties_tbl(l_count).attribute1 := p_counter_properties_tbl(prpr_count).attribute1;
544 l_counter_properties_tbl(l_count).attribute2 := p_counter_properties_tbl(prpr_count).attribute2;
545 l_counter_properties_tbl(l_count).attribute3 := p_counter_properties_tbl(prpr_count).attribute3;
546 l_counter_properties_tbl(l_count).attribute4 := p_counter_properties_tbl(prpr_count).attribute4;
547 l_counter_properties_tbl(l_count).attribute5 := p_counter_properties_tbl(prpr_count).attribute5;
548 l_counter_properties_tbl(l_count).attribute6 := p_counter_properties_tbl(prpr_count).attribute6;
549 l_counter_properties_tbl(l_count).attribute7 := p_counter_properties_tbl(prpr_count).attribute7;
550 l_counter_properties_tbl(l_count).attribute8 := p_counter_properties_tbl(prpr_count).attribute8;
551 l_counter_properties_tbl(l_count).attribute9 := p_counter_properties_tbl(prpr_count).attribute9;
552 l_counter_properties_tbl(l_count).attribute10 := p_counter_properties_tbl(prpr_count).attribute10;
553 l_counter_properties_tbl(l_count).attribute11 := p_counter_properties_tbl(prpr_count).attribute11;
554 l_counter_properties_tbl(l_count).attribute12 := p_counter_properties_tbl(prpr_count).attribute12;
555 l_counter_properties_tbl(l_count).attribute13 := p_counter_properties_tbl(prpr_count).attribute13;
556 l_counter_properties_tbl(l_count).attribute14 := p_counter_properties_tbl(prpr_count).attribute14;
557 l_counter_properties_tbl(l_count).attribute15 := p_counter_properties_tbl(prpr_count).attribute15;
558 l_counter_properties_tbl(l_count).migrated_flag := p_counter_properties_tbl(prpr_count).migrated_flag;
559
560 l_count:=l_count+1;
561 END IF;
562 END LOOP;
563 END IF;
564
565 /* call meter reading api to create meter readings */
566
567
568 EAM_METERREADING_PUB.create_meter_reading(
569
570 p_api_version => 1.0,
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data,
573 x_return_status => x_return_status,
574 x_meter_reading_id => x_meter_reading_id,
575 p_meter_reading_rec => l_meter_reading_rec,
576 p_value_before_reset => p_eam_meter_reading_tbl(meter_count).value_before_reset,
577 p_ignore_warnings => p_eam_meter_reading_tbl(meter_count).p_ignore_warnings,
578 p_ctr_property_readings_tbl => l_counter_properties_tbl
579 );
580
581 END IF;
582 END LOOP;
583
584 IF
585 x_return_status <> fnd_api.g_ret_sts_success
586 THEN
587 raise eaot_api_call_error;
588 END IF;
589 EXCEPTION
590 WHEN eaot_api_call_error THEN
591 ROLLBACK TO eaot_insert_meter_readings;
592
593 END insert_meter_readings;
594
595 -- This procedure validates the transaction details
596
597 PROCEDURE validate_txn(
598
599 p_api_version IN number := 1.0,
600 p_init_msg_list IN varchar2 := fnd_api.g_false,
601 p_validation_level IN number := fnd_api.g_valid_level_full,
602 p_txn_date IN date := sysdate,
603 p_txn_type IN number,
604 p_instance_id IN number,
605 p_operable_flag IN number,
606 p_employee_id IN number,
607 x_return_status OUT NOCOPY varchar2,
608 x_msg_count OUT NOCOPY number,
609 x_msg_data OUT NOCOPY varchar2
610 )
611
612 IS
613
614
615 l_api_name constant varchar2(30) := 'validate_txn';
616 l_api_version constant number := 1.0;
617 l_last_txn_date date;
618 l_txn_type number;
619 l_count number;
620 g_pkg_name CONSTANT varchar2(30) := 'EAM_ASSET_OPERATION_TXN_PVT';
621
622
623 BEGIN
624
625
626 -- Standard Start of API savepoint
627 SAVEPOINT EAM_ASSET_OPERATION_TXN_PVT_SV;
628 -- Standard call to check for call compatibility.
629
630 IF NOT fnd_api.compatible_api_call(
631 l_api_version
632 ,p_api_version
633 ,l_api_name
634 ,g_pkg_name) THEN
635
636 RAISE fnd_api.g_exc_unexpected_error;
637
638 END IF;
639
640 IF fnd_api.to_boolean(p_init_msg_list) THEN
641 fnd_msg_pub.initialize;
642 END IF;
643
644 -- Initialize API return status to success
645
646 x_return_status := fnd_api.g_ret_sts_success;
647
648
649 -- API body
650
651 -- transaction date validation
652
653
654 SELECT MAX(txn_date)
655 INTO l_last_txn_date
656 FROM eam_asset_operation_txn
657 WHERE instance_id=p_instance_id;
658
659 IF ((l_last_txn_date is not null AND (p_txn_date <= l_last_txn_date)) OR p_txn_date>sysdate)
660 THEN
661 fnd_message.set_name
662 ( application => 'EAM'
663 , name => 'EAM_EVENT_DATE_INVALID'
664 );
665
666 fnd_msg_pub.add;
667 x_return_status:= fnd_api.g_ret_sts_error;
668
669 fnd_msg_pub.count_and_get(
670 p_count => x_msg_count
671 ,p_data => x_msg_data
672 );
673 return;
674
675 END IF;
676
677 -- instance id validation
678
679 SELECT
680 count(*)
681 INTO l_count
682 FROM csi_item_instances
683 WHERE instance_id=p_instance_id
684 AND
685 p_txn_date BETWEEN nvl(active_start_date,sysdate) AND
686 NVL(active_end_date, sysdate);
687
688 IF (l_count=0 OR l_count IS NULL)
689 THEN
690 fnd_message.set_name
691 ( application => 'EAM'
692 , name => 'EAM_INSTANCE_ID_INVALID'
693 );
694
695 fnd_msg_pub.add;
696 x_return_status:= fnd_api.g_ret_sts_error;
697
698 fnd_msg_pub.count_and_get(
699 p_count => x_msg_count
700 ,p_data => x_msg_data
701 );
702 return;
703 END IF;
704
705
706
707 -- valid fnd_user validation
708
709 SELECT
710 count(*)
711 INTO l_count
712 FROM fnd_user
713 WHERE p_employee_id=user_id;
714
715 IF (l_count=0 OR l_count IS NULL)
716 THEN
717 fnd_message.set_name
718 ( application => 'EAM'
719 , name => 'EAM_USER_INVALID'
720 );
721
722 fnd_msg_pub.add;
723 x_return_status:= fnd_api.g_ret_sts_error;
724
725 fnd_msg_pub.count_and_get(
726 p_count => x_msg_count
727 ,p_data => x_msg_data
728 );
729 return;
730
731 END IF;
732
733
734 --txn_type validation
735
736 IF l_last_txn_date IS NOT NULL THEN
737 SELECT
738 txn_type
739 INTO l_txn_type
740 FROM eam_asset_operation_txn
741 WHERE instance_id=p_instance_id
742 AND txn_date=l_last_txn_date;
743 END IF;
744
745 IF p_txn_type IS NULL THEN
746 fnd_message.set_name
747 ( application => 'EAM'
748 , name => 'EAM_TXNTYPE_INVALID'
749 );
750
751 fnd_msg_pub.add;
752 x_return_status:= fnd_api.g_ret_sts_error;
753
754 fnd_msg_pub.count_and_get(
755 p_count => x_msg_count
756 ,p_data => x_msg_data
757 );
758 return;
759 END IF;
760
761 IF l_txn_type IS NOT NULL THEN
762 IF(p_txn_type is NOT NULL) THEN
763 IF((p_txn_type NOT IN (1,2)) OR (p_txn_type=l_txn_type) ) THEN
764 fnd_message.set_name
765 ( application => 'EAM'
766 , name => 'EAM_TXNTYPE_INVALID'
767 );
768
769 fnd_msg_pub.add;
770 x_return_status:= fnd_api.g_ret_sts_error;
771
772 fnd_msg_pub.count_and_get(
773 p_count => x_msg_count
774 ,p_data => x_msg_data
775 );
776 return;
777 END IF;
778 END IF;
779 END IF;
780
781
782
783 --p_operable_flag validation
784
785 SELECT count(*) INTO l_count
786 FROM mfg_lookups
787 WHERE lookup_type='SYS_YES_NO' AND
788 enabled_flag='Y' AND
789 p_txn_date BETWEEN NVL(start_date_active, p_txn_date) AND
790 NVL(end_date_active,sysdate) AND
791 lookup_code = p_operable_flag;
792
793 IF
794 l_count = 0 OR l_count is null
795 THEN
796 fnd_message.set_name
797 ( application => 'EAM'
798 , name => 'EAM_OPERABLE_INVALID'
799 );
800
801 fnd_msg_pub.add;
802 x_return_status:= fnd_api.g_ret_sts_error;
803
804 fnd_msg_pub.count_and_get(
805 p_count => x_msg_count
806 ,p_data => x_msg_data
807 );
808 return;
809 END IF;
810
811
812 -- End of API body.
813 -- Standard check of p_commit.
814
815 -- Standard call to get message count and if count is 1, get message info.
816 fnd_msg_pub.count_and_get(
817 p_count => x_msg_count
818 ,p_data => x_msg_data);
819
820 EXCEPTION
821
822 WHEN fnd_api.g_exc_error THEN
823 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT_SV;
824 x_return_status := fnd_api.g_ret_sts_error;
825 fnd_msg_pub.count_and_get(
826 p_count => x_msg_count
827 ,p_data => x_msg_data);
828
829 WHEN fnd_api.g_exc_unexpected_error THEN
830 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT_SV;
831 x_return_status := fnd_api.g_ret_sts_unexp_error;
832 fnd_msg_pub.count_and_get(
833 p_count => x_msg_count
834 ,p_data => x_msg_data);
835
836 WHEN OTHERS THEN
837
838 ROLLBACK TO EAM_ASSET_OPERATION_TXN_PVT_SV;
839 x_return_status := fnd_api.g_ret_sts_unexp_error;
840 IF fnd_msg_pub.check_msg_level(
841 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
842 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
843 END IF;
844 fnd_msg_pub.count_and_get(
845 p_count => x_msg_count
846 ,p_data => x_msg_data);
847
848 END validate_txn;
849
850 -- This procedure commits the transaction details into eam_asset_operation_txn
851
852 PROCEDURE insert_txn(
853
854 p_api_version IN number := 1.0,
855 p_init_msg_list IN varchar2 := fnd_api.g_false,
856 p_commit IN varchar2 := fnd_api.g_false,
857 p_validation_level IN number := fnd_api.g_valid_level_full,
858 p_txn_date IN date := sysdate,
859 p_txn_type IN number,
860 p_instance_id IN number,
861 p_comments IN varchar2 := NULL,
862 p_qa_collection_id IN number := NULL,
863 p_operable_flag IN number,
864 p_employee_id IN number,
865 p_eam_ops_quality_tbl IN eam_asset_operation_txn_pub.eam_quality_tbl_type,
866 p_meter_reading_rec_tbl IN eam_asset_operation_txn_pub.meter_reading_rec_tbl_type,
867 p_counter_properties_tbl IN eam_asset_operation_txn_pub.Ctr_Property_readings_Tbl,
868 p_attribute_category IN varchar2 := NULL,
869 p_attribute1 IN varchar2 := NULL,
870 p_attribute2 IN varchar2 := NULL,
871 p_attribute3 IN varchar2 := NULL,
872 p_attribute4 IN varchar2 := NULL,
873 p_attribute5 IN varchar2 := NULL,
874 p_attribute6 IN varchar2 := NULL,
875 p_attribute7 IN varchar2 := NULL,
876 p_attribute8 IN varchar2 := NULL,
877 p_attribute9 IN varchar2 := NULL,
878 p_attribute10 IN varchar2 := NULL,
879 p_attribute11 IN varchar2 := NULL,
880 p_attribute12 IN varchar2 := NULL,
881 p_attribute13 IN varchar2 := NULL,
882 p_attribute14 IN varchar2 := NULL,
883 p_attribute15 IN varchar2 := NULL,
884 x_return_status OUT NOCOPY varchar2,
885 x_msg_count OUT NOCOPY number,
886 x_msg_data OUT NOCOPY varchar2
887 )
888
889 IS
890
891 l_api_name constant varchar2(30) := 'insert_txn';
892 l_api_version constant number := 1.0;
893 l_desc_flex_name varchar2(100) :='EAM_ASSET_CHECKINOUT';
894 x_error_segments number;
895 x_error_message varchar2(2000);
896 l_reference varchar2(100);
897 l_txn_id number;
898 l_event_id number;
899 l_maint_org_id number;
900 l_asset_group_id number;
901 l_asset_group varchar2(2000);
902 l_asset_instance_number varchar2(30);
903 l_asset_number varchar2(30);
904 l_txn_number number;
905 l_context_values varchar2(2000);
906 l_instance_rec CSI_DATASTRUCTURES_PUB.instance_rec;
907 l_txn_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
908 x_instance_id_lst CSI_DATASTRUCTURES_PUB.id_tbl;
909 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
910 l_party_tbl CSI_DATASTRUCTURES_PUB.party_tbl;
911 l_account_tbl CSI_DATASTRUCTURES_PUB.party_account_tbl;
912 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
913 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.organization_units_tbl;
914 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
915 t_output varchar2(2000);
916 t_msg_dummy number;
917 l_assetops_return_status varchar2(2000);
918 l_plan_name varchar2(255);
919 l_count number :=1;
920 l_list_of_plans varchar2(1000);
921 l_validate boolean;
922 l_mandatory_qua_plan varchar2(1);
923 g_pkg_name CONSTANT varchar2(30) := 'EAM_ASSET_OPERATION_TXN_PVT';
924 l_object_version_number number;
925 eaot_api_call_error EXCEPTION;
926 eaot_api_desc_error EXCEPTION;
927 TYPE num_table is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
928 l_planid_tbl num_table;
929
930
931
932 CURSOR get_plan_name(qa_collection_id IN NUMBER) IS
933 (select qp.name
934 from qa_plans qp,
935 qa_results qr
936 where
937 qr.collection_id = qa_collection_id
938 and
939 qr.plan_id = qp.plan_id);
940
941 CURSOR get_plan_id(p_qa_collection_id IN NUMBER) IS
942 (SELECT
943 distinct Plan_Id
944 from
945 QA_RESULTS
946 where
947 collection_id = p_qa_collection_id
948 );
949
950 BEGIN
951
952 -- Standard Start of API savepoint
953 SAVEPOINT EAOT_INSERT_TXN;
954
955 -- Standard call to check for call compatibility.
956 IF NOT fnd_api.compatible_api_call(
957 l_api_version
958 ,p_api_version
959 ,l_api_name
960 ,g_pkg_name) THEN
961 RAISE fnd_api.g_exc_unexpected_error;
962 END IF;
963
964 IF fnd_api.to_boolean(p_init_msg_list) THEN
965 fnd_msg_pub.initialize;
966 END IF;
967
968 x_return_status := fnd_api.g_ret_sts_success;
969
970 --api body
971 --generate txnid from sequence
972 SELECT
973 eam_asset_operation_txn_s.nextval
974 INTO l_txn_id
975 FROM dual;
976
977
978 IF p_txn_type=1 THEN
979 l_event_id:=10;
980 l_txn_number:=2006;
981 ELSIF p_txn_type=2
982 THEN
983 l_event_id:=11;
984 l_txn_number:=2007;
985 END IF;
986
987
988
989 -- maintenance organization_id
990 BEGIN
991
992 SELECT
993 mp.maint_organization_id,
994 cii.instance_number,
995 cii.inventory_item_id,
996 cii.serial_number
997 INTO
998 l_maint_org_id,
999 l_asset_instance_number,
1000 l_asset_group_id,
1001 l_asset_number
1002 FROM
1003 mtl_parameters mp, csi_item_instances cii
1004 where
1005 cii.last_vld_organization_id = mp.organization_id
1006 AND cii.instance_id = p_instance_id;
1007
1008 EXCEPTION
1009
1010 WHEN NO_DATA_FOUND THEN
1011 raise eaot_api_call_error;
1012 END;
1013
1014 --call validate_txn to validate txn_details
1015
1016 EAM_ASSET_OPERATION_TXN_PVT.validate_txn(
1017 p_api_version => 1.0,
1018 p_init_msg_list => p_init_msg_list,
1019 p_validation_level => p_validation_level,
1020 p_txn_date => p_txn_date,
1021 p_txn_type => p_txn_type,
1022 p_instance_id => p_instance_id,
1023 p_operable_flag => p_operable_flag,
1024 p_employee_id => p_employee_id,
1025 x_return_status => x_return_status,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data);
1028
1029
1030
1031 IF
1032 x_return_status <> fnd_api.g_ret_sts_success
1033
1034 THEN
1035 raise eaot_api_call_error;
1036 END IF;
1037
1038
1039 --Check whether the API is called from UI or from a public api
1040
1041 IF p_eam_ops_quality_tbl.COUNT>0 THEN
1042
1043 insert_quality_plans(
1044 p_eam_ops_quality_tbl => p_eam_ops_quality_tbl
1045 ,p_instance_id => p_instance_id
1046 ,p_txn_date => p_txn_date
1047 ,p_comments => p_comments
1048 ,p_operable_flag => p_operable_flag
1049 ,p_organization_id => l_maint_org_id
1050 ,p_asset_group_id => l_asset_group_id
1051 ,p_asset_instance_number => l_asset_instance_number
1052 ,p_asset_number => l_asset_number
1053 ,p_txn_number => l_txn_number
1054 ,p_employee_id => p_employee_id
1055 ,x_return_status => x_return_status
1056 ,x_msg_count => x_msg_count
1057 ,x_msg_data => x_msg_data
1058
1059 );
1060 IF x_return_status <> fnd_api.g_ret_sts_success
1061 THEN
1062 raise eaot_api_call_error;
1063 END IF;
1064 END IF;
1065
1066
1067 IF p_meter_reading_rec_tbl.COUNT>0 AND x_return_status = fnd_api.g_ret_sts_success THEN
1068
1069 insert_meter_readings(
1070 p_eam_meter_reading_tbl => p_meter_reading_rec_tbl
1071 ,p_counter_properties_tbl => p_counter_properties_tbl
1072 ,p_instance_id => p_instance_id
1073 ,p_txn_id => l_txn_id
1074 ,x_return_status => x_return_status
1075 ,x_msg_count => x_msg_count
1076 ,x_msg_data => x_msg_data
1077 );
1078
1079 IF x_return_status <> fnd_api.g_ret_sts_success
1080 THEN
1081 raise eaot_api_call_error;
1082 END IF;
1083 END IF;
1084
1085
1086
1087
1088
1089 --validate descriptive flex fields
1090
1091 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
1092 p_desc_flex_name => l_desc_flex_name,
1093 p_attribute_category => p_attribute_category,
1094 p_attribute1 => p_attribute1,
1095 p_attribute2 => p_attribute2,
1096 p_attribute3 => p_attribute3,
1097 p_attribute4 => p_attribute4,
1098 p_attribute5 => p_attribute5,
1099 p_attribute6 => p_attribute6,
1100 p_attribute7 => p_attribute7,
1101 p_attribute8 => p_attribute8,
1102 p_attribute9 => p_attribute9,
1103 p_attribute10 => p_attribute10,
1104 p_attribute11 => p_attribute11,
1105 p_attribute12 => p_attribute12,
1106 p_attribute13 => p_attribute13,
1107 p_attribute14 => p_attribute14,
1108 p_attribute15 => p_attribute15,
1109 x_error_segments => x_error_segments,
1110 x_error_message => x_error_message
1111 );
1112
1113 IF l_validate <> TRUE THEN
1114 fnd_message.set_name
1115 ( application => 'EAM'
1116 , name => 'EAM_DESC_INVALID'
1117 );
1118
1119 fnd_msg_pub.add;
1120
1121 fnd_msg_pub.count_and_get(
1122 p_count => x_msg_count
1123 ,p_data => x_msg_data
1124 );
1125 raise eaot_api_desc_error;
1126 END IF;
1127 --insert record in eam_asset_operation_txn table
1128
1129
1130 SELECT DISTINCT nvl(ppf.full_name,fu.user_name)
1131 INTO l_reference
1132 FROM fnd_user fu,per_people_f ppf
1133 WHERE fu.employee_id=ppf.person_id(+)
1134 AND fu.user_id=p_employee_id
1135 AND sysdate BETWEEN fu.start_date AND nvl(fu.end_date,sysdate)
1136 AND sysdate BETWEEN ppf.effective_start_date AND nvl(ppf.effective_end_date,sysdate)
1137 and rownum = 1;
1138
1139
1140 insert into eam_asset_operation_txn(
1141 txn_id,
1142 txn_date,
1143 txn_type,
1144 instance_id,
1145 comments,
1146 user_id,
1147 operable,
1148 qa_collection_id,
1149 attribute_category,
1150 attribute1,
1151 attribute2,
1152 attribute3,
1153 attribute4,
1154 attribute5,
1155 attribute6,
1156 attribute7,
1157 attribute8,
1158 attribute9,
1159 attribute10,
1160 attribute11,
1161 attribute12,
1162 attribute13,
1163 attribute14,
1164 attribute15,
1165 created_by,
1166 creation_date,
1167 last_updated_by,
1168 last_update_date,
1169 last_update_login
1170 )
1171 VALUES
1172 (
1173 l_txn_id,
1174 p_txn_date,
1175 p_txn_type,
1176 p_instance_id,
1177 p_comments,
1178 p_employee_id,
1179 p_operable_flag,
1180 p_qa_collection_id,
1181 p_attribute_category,
1182 p_attribute1,
1183 p_attribute2,
1184 p_attribute3,
1185 p_attribute4,
1186 p_attribute5,
1187 p_attribute6,
1188 p_attribute7,
1189 p_attribute8,
1190 p_attribute9,
1191 p_attribute10,
1192 p_attribute11,
1193 p_attribute12,
1194 p_attribute13,
1195 p_attribute14,
1196 p_attribute15,
1197 FND_GLOBAL.user_id,
1198 sysdate,
1199 FND_GLOBAL.user_id,
1200 sysdate,
1201 FND_GLOBAL.login_id
1202 );
1203
1204 --check whether mandatory plans have been entered and
1205 --call qa_result_grp.enable_and_fire_action to commit quality results
1206 --call post_background_results
1207
1208 BEGIN
1209 OPEN get_plan_id(p_qa_collection_id);
1210 LOOP
1211 FETCH get_plan_id INTO l_planid_tbl(l_count);
1212 EXIT WHEN get_plan_id%NOTFOUND;
1213 l_count := l_count+1;
1214
1215 END LOOP;
1216
1217 EXCEPTION
1218
1219 WHEN NO_DATA_FOUND THEN
1220 null;
1221 END;
1222
1223 IF p_eam_ops_quality_tbl.COUNT<=0 THEN
1224
1225 IF l_planid_tbl.COUNT>0 THEN
1226 l_list_of_plans := '@'|| l_planid_tbl(l_planid_tbl.FIRST)||'@';
1227 FOR l_count in l_planid_tbl.FIRST+1..l_planid_tbl.LAST
1228 LOOP
1229 l_list_of_plans := l_list_of_plans||'@'||l_planid_tbl(l_count)||'@';
1230 END LOOP;
1231 ELSE
1232 l_list_of_plans := '@'||'@';
1233 END IF;
1234
1235 SELECT
1236 DISTINCT concatenated_segments
1237 INTO
1238 l_asset_group
1239 FROM mtl_system_items_kfv
1240 WHERE
1241 inventory_item_id = l_asset_group_id;
1242
1243 l_mandatory_qua_plan := qa_web_txn_api.quality_mandatory_plans_remain
1244 (
1245 p_txn_number => l_txn_number
1246 ,p_organization_id => l_maint_org_id
1247 ,pk1 => l_asset_group
1248 ,pk2 => l_asset_number
1249 ,pk6 => l_asset_instance_number
1250 ,p_collection_id => p_qa_collection_id
1251 ,p_list_of_plans => l_list_of_plans
1252 );
1253 IF l_mandatory_qua_plan = 'Y' THEN
1254 fnd_message.set_name
1255 ( application => 'EAM'
1256 , name => 'EAM_WC_QA_REMAIN'
1257 );
1258
1259 fnd_msg_pub.add;
1260 x_return_status:= fnd_api.g_ret_sts_error;
1261
1262 fnd_msg_pub.count_and_get(
1263 p_count => x_msg_count
1264 ,p_data => x_msg_data
1265 );
1266 raise eaot_api_call_error;
1267 END IF;
1268
1269
1270 l_context_values := '162='||l_asset_group_id||'@163='
1271 ||l_asset_number||'@2147483550='||
1272 p_instance_id;
1273
1274 qa_web_txn_api.post_background_results
1275 (
1276 p_txn_number => l_txn_number
1277 ,p_org_id => l_maint_org_id
1278 ,p_context_values => l_context_values
1279 ,p_collection_id => p_qa_collection_id
1280 );
1281
1282 qa_result_grp.enable_and_fire_action
1283 (
1284 p_api_version => 1.0
1285 ,p_collection_id => p_qa_collection_id
1286 ,x_return_status => x_return_status
1287 ,x_msg_count => x_msg_count
1288 ,x_msg_data => x_msg_data
1289 );
1290
1291 --call the assetlog api to log the quality results entered event
1292
1293 BEGIN
1294 OPEN get_plan_name(p_qa_collection_id);
1295 LOOP
1296 FETCH get_plan_name INTO l_plan_name;
1297 EXIT WHEN get_plan_name%NOTFOUND;
1298
1299 EAM_ASSET_LOG_PVT.INSERT_ROW(
1300 p_api_version => 1.0,
1301 p_init_msg_list => p_init_msg_list,
1302 p_commit => p_commit,
1303 p_validation_level => p_validation_level,
1304 p_event_date => p_txn_date,
1305 p_event_type => 'EAM_SYSTEM_EVENTS',
1306 p_event_id => 12,
1307 p_instance_id => p_instance_id,
1308 p_employee_id => p_employee_id,
1309 p_organization_id => l_maint_org_id,
1310 p_comments => p_comments,
1311 p_reference => l_plan_name,
1312 p_ref_id => p_qa_collection_id,
1313 p_operable_flag => p_operable_flag,
1314 x_return_status => l_assetops_return_status,
1315 x_msg_count => x_msg_count,
1316 x_msg_data => x_msg_data
1317 );
1318 END LOOP;
1319
1320 EXCEPTION
1321
1322 WHEN NO_DATA_FOUND THEN
1323 null;
1324 END;
1325
1326
1327 END IF;
1328
1329
1330
1331 IF x_return_status <> fnd_api.g_ret_sts_success
1332 THEN
1333 raise eaot_api_call_error;
1334 END IF;
1335 --call the assetlog api to log the checkin/out event
1336
1337
1338
1339 EAM_ASSET_LOG_PVT.INSERT_ROW(
1340 p_api_version => 1.0,
1341 p_init_msg_list => p_init_msg_list,
1342 p_commit => p_commit,
1343 p_validation_level => p_validation_level,
1344 p_event_date => p_txn_date,
1345 p_event_type => 'EAM_SYSTEM_EVENTS',
1346 p_event_id => l_event_id,
1347 p_employee_id => p_employee_id,
1348 p_organization_id => l_maint_org_id,
1349 p_instance_id => p_instance_id,
1350 p_comments => p_comments,
1351 p_reference => l_reference,
1352 p_ref_id => l_txn_id,
1353 p_operable_flag => p_operable_flag,
1354 x_return_status => l_assetops_return_status,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data
1357 );
1358 IF x_return_status <> fnd_api.g_ret_sts_success
1359 THEN
1360 raise eaot_api_call_error;
1361 END IF;
1362 -- call csi update api to update checkin_status flag of the current instance
1363
1364
1365 l_instance_rec.instance_id := p_instance_id;
1366
1367 select object_version_number
1368 into l_object_version_number
1369 from csi_item_instances
1370 where instance_id = p_instance_id;
1371
1372 IF SQL%NOTFOUND THEN
1373 l_object_version_number :=null;
1374 END IF;
1375
1376 l_instance_rec.object_version_number := l_object_version_number;
1377
1378 -- add checkin_status updated with txntype
1379 l_instance_rec.checkin_status :=p_txn_type;
1380 l_instance_rec.mfg_serial_number_flag := 'Y';
1381
1382
1383 --fill the transaction record
1384 l_txn_rec.transaction_id := NULL;
1385 l_txn_rec.transaction_date := sysdate; --TO_DATE('');
1386 l_txn_rec.source_transaction_date := sysdate; --TO_DATE('');
1387 l_txn_rec.transaction_type_id := 1; --NULL;
1388 l_txn_rec.txn_sub_type_id := NULL;
1389 l_txn_rec.source_group_ref_id := NULL;
1390 l_txn_rec.source_group_ref := '';
1391 l_txn_rec.source_header_ref_id := NULL;
1392 l_txn_rec.source_header_ref := '';
1393 l_txn_rec.source_line_ref_id := NULL;
1394 l_txn_rec.source_line_ref := '';
1395 l_txn_rec.source_dist_ref_id1 := NULL;
1396 l_txn_rec.source_dist_ref_id2 := NULL;
1397 l_txn_rec.inv_material_transaction_id := NULL;
1398 l_txn_rec.transaction_quantity := NULL;
1399 l_txn_rec.transaction_uom_code := '';
1400 l_txn_rec.transacted_by := NULL;
1401 l_txn_rec.transaction_status_code := '';
1402 l_txn_rec.transaction_action_code := '';
1403 l_txn_rec.message_id := NULL;
1404 l_txn_rec.context := '';
1405 l_txn_rec.attribute1 := '';
1406 l_txn_rec.attribute2 := '';
1407 l_txn_rec.attribute3 := '';
1408 l_txn_rec.attribute4 := '';
1409 l_txn_rec.attribute5 := '';
1410 l_txn_rec.attribute6 := '';
1411 l_txn_rec.attribute7 := '';
1412 l_txn_rec.attribute8 := '';
1413 l_txn_rec.attribute9 := '';
1414 l_txn_rec.attribute10 := '';
1415 l_txn_rec.attribute11 := '';
1416 l_txn_rec.attribute12 := '';
1417 l_txn_rec.attribute13 := '';
1418 l_txn_rec.attribute14 := '';
1419 l_txn_rec.attribute15 := '';
1420 l_txn_rec.object_version_number := NULL;
1421 l_txn_rec.split_reason_code := '';
1422
1423
1424 -- call csi update api
1425 IF x_return_status <> fnd_api.g_ret_sts_success
1426 THEN
1427 raise eaot_api_call_error;
1428 END IF;
1429
1430 csi_item_instance_pub.update_item_instance
1431 (
1432 p_api_version => 1.0
1433 ,p_commit => fnd_api.g_false
1434 ,p_init_msg_list => fnd_api.g_false
1435 ,p_validation_level => fnd_api.g_valid_level_full
1436 ,p_instance_rec => l_instance_rec
1437 ,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
1438 ,p_party_tbl => l_party_tbl
1439 ,p_account_tbl => l_account_tbl
1440 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
1441 ,p_org_assignments_tbl => l_org_assignments_tbl
1442 ,p_asset_assignment_tbl => l_asset_assignment_tbl
1443 ,p_txn_rec => l_txn_rec
1444 ,x_instance_id_lst => x_instance_id_lst
1445 ,x_return_status => x_return_status
1446 ,x_msg_count => x_msg_count
1447 ,x_msg_data => x_msg_data
1448 );
1449
1450
1451 IF x_return_status <> fnd_api.g_ret_sts_success
1452 THEN
1453 raise eaot_api_call_error;
1454 END IF;
1455 -- End of API body.
1456 -- Standard check of p_commit.
1457 IF fnd_api.to_boolean(p_commit) THEN
1458 COMMIT WORK;
1459 END IF;
1460 fnd_msg_pub.count_and_get(
1461 p_count => x_msg_count
1462 ,p_data => x_msg_data);
1463 EXCEPTION
1464 WHEN fnd_api.g_exc_error THEN
1465 ROLLBACK TO EAOT_INSERT_TXN;
1466 x_return_status := fnd_api.g_ret_sts_error;
1467 fnd_msg_pub.count_and_get(
1468 p_count => x_msg_count
1469 ,p_data => x_msg_data);
1470 WHEN fnd_api.g_exc_unexpected_error THEN
1471 ROLLBACK TO EAOT_INSERT_TXN;
1472 x_return_status := fnd_api.g_ret_sts_unexp_error;
1473 fnd_msg_pub.count_and_get(
1474 p_count => x_msg_count
1475 ,p_data => x_msg_data);
1476 WHEN eaot_api_call_error THEN
1477 ROLLBACK TO EAOT_INSERT_TXN;
1478 WHEN eaot_api_desc_error THEN
1479 ROLLBACK TO EAOT_INSERT_TXN;
1480 x_return_status := fnd_api.g_ret_sts_error;
1481 WHEN OTHERS THEN
1482 ROLLBACK TO EAOT_INSERT_TXN;
1483 x_return_status := fnd_api.g_ret_sts_unexp_error;
1484
1485 IF fnd_msg_pub.check_msg_level(
1486 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1487 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1488 END IF;
1489
1490 fnd_msg_pub.count_and_get(
1491 p_count => x_msg_count,
1492 p_data => x_msg_data);
1493
1494 END insert_txn;
1495
1496 END EAM_ASSET_OPERATION_TXN_PVT;