[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 2006/09/14 20:02:13 hkarmach noship $ */
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
1136
1137 insert into eam_asset_operation_txn(
1138 txn_id,
1139 txn_date,
1140 txn_type,
1141 instance_id,
1142 comments,
1143 user_id,
1144 operable,
1145 qa_collection_id,
1146 attribute_category,
1147 attribute1,
1148 attribute2,
1149 attribute3,
1150 attribute4,
1151 attribute5,
1152 attribute6,
1153 attribute7,
1154 attribute8,
1155 attribute9,
1156 attribute10,
1157 attribute11,
1158 attribute12,
1159 attribute13,
1160 attribute14,
1161 attribute15,
1162 created_by,
1163 creation_date,
1164 last_updated_by,
1165 last_update_date,
1166 last_update_login
1167 )
1168 VALUES
1169 (
1170 l_txn_id,
1171 p_txn_date,
1172 p_txn_type,
1173 p_instance_id,
1174 p_comments,
1175 p_employee_id,
1176 p_operable_flag,
1177 p_qa_collection_id,
1178 p_attribute_category,
1179 p_attribute1,
1180 p_attribute2,
1181 p_attribute3,
1182 p_attribute4,
1183 p_attribute5,
1184 p_attribute6,
1185 p_attribute7,
1186 p_attribute8,
1187 p_attribute9,
1188 p_attribute10,
1189 p_attribute11,
1190 p_attribute12,
1191 p_attribute13,
1192 p_attribute14,
1193 p_attribute15,
1194 FND_GLOBAL.user_id,
1195 sysdate,
1196 FND_GLOBAL.user_id,
1197 sysdate,
1198 FND_GLOBAL.login_id
1199 );
1200
1201 --check whether mandatory plans have been entered and
1202 --call qa_result_grp.enable_and_fire_action to commit quality results
1203 --call post_background_results
1204
1205 BEGIN
1206 OPEN get_plan_id(p_qa_collection_id);
1207 LOOP
1208 FETCH get_plan_id INTO l_planid_tbl(l_count);
1209 EXIT WHEN get_plan_id%NOTFOUND;
1210 l_count := l_count+1;
1211
1212 END LOOP;
1213
1214 EXCEPTION
1215
1216 WHEN NO_DATA_FOUND THEN
1217 null;
1218 END;
1219
1220 IF p_eam_ops_quality_tbl.COUNT<=0 THEN
1221
1222 IF l_planid_tbl.COUNT>0 THEN
1223 l_list_of_plans := '@'|| l_planid_tbl(l_planid_tbl.FIRST)||'@';
1224 FOR l_count in l_planid_tbl.FIRST+1..l_planid_tbl.LAST
1225 LOOP
1226 l_list_of_plans := l_list_of_plans||'@'||l_planid_tbl(l_count)||'@';
1227 END LOOP;
1228 ELSE
1229 l_list_of_plans := '@'||'@';
1230 END IF;
1231
1232 SELECT
1233 DISTINCT concatenated_segments
1234 INTO
1235 l_asset_group
1236 FROM mtl_system_items_kfv
1237 WHERE
1238 inventory_item_id = l_asset_group_id;
1239
1240 l_mandatory_qua_plan := qa_web_txn_api.quality_mandatory_plans_remain
1241 (
1242 p_txn_number => l_txn_number
1243 ,p_organization_id => l_maint_org_id
1244 ,pk1 => l_asset_group
1245 ,pk2 => l_asset_number
1246 ,pk6 => l_asset_instance_number
1247 ,p_collection_id => p_qa_collection_id
1248 ,p_list_of_plans => l_list_of_plans
1249 );
1250 IF l_mandatory_qua_plan = 'Y' THEN
1251 fnd_message.set_name
1252 ( application => 'EAM'
1253 , name => 'EAM_WC_QA_REMAIN'
1254 );
1255
1256 fnd_msg_pub.add;
1257 x_return_status:= fnd_api.g_ret_sts_error;
1258
1259 fnd_msg_pub.count_and_get(
1260 p_count => x_msg_count
1261 ,p_data => x_msg_data
1262 );
1263 raise eaot_api_call_error;
1264 END IF;
1265
1266
1267 l_context_values := '162='||l_asset_group_id||'@163='
1268 ||l_asset_number||'@2147483550='||
1269 p_instance_id;
1270
1271 qa_web_txn_api.post_background_results
1272 (
1273 p_txn_number => l_txn_number
1274 ,p_org_id => l_maint_org_id
1275 ,p_context_values => l_context_values
1276 ,p_collection_id => p_qa_collection_id
1277 );
1278
1279 qa_result_grp.enable_and_fire_action
1280 (
1281 p_api_version => 1.0
1282 ,p_collection_id => p_qa_collection_id
1283 ,x_return_status => x_return_status
1284 ,x_msg_count => x_msg_count
1285 ,x_msg_data => x_msg_data
1286 );
1287
1288 --call the assetlog api to log the quality results entered event
1289
1290 BEGIN
1291 OPEN get_plan_name(p_qa_collection_id);
1292 LOOP
1293 FETCH get_plan_name INTO l_plan_name;
1294 EXIT WHEN get_plan_name%NOTFOUND;
1295
1296 EAM_ASSET_LOG_PVT.INSERT_ROW(
1297 p_api_version => 1.0,
1298 p_init_msg_list => p_init_msg_list,
1299 p_commit => p_commit,
1300 p_validation_level => p_validation_level,
1301 p_event_date => p_txn_date,
1302 p_event_type => 'EAM_SYSTEM_EVENTS',
1303 p_event_id => 12,
1304 p_instance_id => p_instance_id,
1305 p_employee_id => p_employee_id,
1306 p_organization_id => l_maint_org_id,
1307 p_comments => p_comments,
1308 p_reference => l_plan_name,
1309 p_ref_id => p_qa_collection_id,
1310 p_operable_flag => p_operable_flag,
1311 x_return_status => l_assetops_return_status,
1312 x_msg_count => x_msg_count,
1313 x_msg_data => x_msg_data
1314 );
1315 END LOOP;
1316
1317 EXCEPTION
1318
1319 WHEN NO_DATA_FOUND THEN
1320 null;
1321 END;
1322
1323
1324 END IF;
1325
1326
1327
1328 IF x_return_status <> fnd_api.g_ret_sts_success
1329 THEN
1330 raise eaot_api_call_error;
1331 END IF;
1332 --call the assetlog api to log the checkin/out event
1333
1334
1335
1336 EAM_ASSET_LOG_PVT.INSERT_ROW(
1337 p_api_version => 1.0,
1338 p_init_msg_list => p_init_msg_list,
1339 p_commit => p_commit,
1340 p_validation_level => p_validation_level,
1341 p_event_date => p_txn_date,
1342 p_event_type => 'EAM_SYSTEM_EVENTS',
1343 p_event_id => l_event_id,
1344 p_employee_id => p_employee_id,
1345 p_organization_id => l_maint_org_id,
1346 p_instance_id => p_instance_id,
1347 p_comments => p_comments,
1348 p_reference => l_reference,
1349 p_ref_id => l_txn_id,
1350 p_operable_flag => p_operable_flag,
1351 x_return_status => l_assetops_return_status,
1352 x_msg_count => x_msg_count,
1353 x_msg_data => x_msg_data
1354 );
1355 IF x_return_status <> fnd_api.g_ret_sts_success
1356 THEN
1357 raise eaot_api_call_error;
1358 END IF;
1359 -- call csi update api to update checkin_status flag of the current instance
1360
1361
1362 l_instance_rec.instance_id := p_instance_id;
1363
1364 select object_version_number
1365 into l_object_version_number
1366 from csi_item_instances
1367 where instance_id = p_instance_id;
1368
1369 IF SQL%NOTFOUND THEN
1370 l_object_version_number :=null;
1371 END IF;
1372
1373 l_instance_rec.object_version_number := l_object_version_number;
1374
1375 -- add checkin_status updated with txntype
1376 l_instance_rec.checkin_status :=p_txn_type;
1377 l_instance_rec.mfg_serial_number_flag := 'Y';
1378
1379
1380 --fill the transaction record
1381 l_txn_rec.transaction_id := NULL;
1382 l_txn_rec.transaction_date := sysdate; --TO_DATE('');
1383 l_txn_rec.source_transaction_date := sysdate; --TO_DATE('');
1384 l_txn_rec.transaction_type_id := 1; --NULL;
1385 l_txn_rec.txn_sub_type_id := NULL;
1386 l_txn_rec.source_group_ref_id := NULL;
1387 l_txn_rec.source_group_ref := '';
1388 l_txn_rec.source_header_ref_id := NULL;
1389 l_txn_rec.source_header_ref := '';
1390 l_txn_rec.source_line_ref_id := NULL;
1391 l_txn_rec.source_line_ref := '';
1392 l_txn_rec.source_dist_ref_id1 := NULL;
1393 l_txn_rec.source_dist_ref_id2 := NULL;
1394 l_txn_rec.inv_material_transaction_id := NULL;
1395 l_txn_rec.transaction_quantity := NULL;
1396 l_txn_rec.transaction_uom_code := '';
1397 l_txn_rec.transacted_by := NULL;
1398 l_txn_rec.transaction_status_code := '';
1399 l_txn_rec.transaction_action_code := '';
1400 l_txn_rec.message_id := NULL;
1401 l_txn_rec.context := '';
1402 l_txn_rec.attribute1 := '';
1403 l_txn_rec.attribute2 := '';
1404 l_txn_rec.attribute3 := '';
1405 l_txn_rec.attribute4 := '';
1406 l_txn_rec.attribute5 := '';
1407 l_txn_rec.attribute6 := '';
1408 l_txn_rec.attribute7 := '';
1409 l_txn_rec.attribute8 := '';
1410 l_txn_rec.attribute9 := '';
1411 l_txn_rec.attribute10 := '';
1412 l_txn_rec.attribute11 := '';
1413 l_txn_rec.attribute12 := '';
1414 l_txn_rec.attribute13 := '';
1415 l_txn_rec.attribute14 := '';
1416 l_txn_rec.attribute15 := '';
1417 l_txn_rec.object_version_number := NULL;
1418 l_txn_rec.split_reason_code := '';
1419
1420
1421 -- call csi update api
1422 IF x_return_status <> fnd_api.g_ret_sts_success
1423 THEN
1424 raise eaot_api_call_error;
1425 END IF;
1426
1427 csi_item_instance_pub.update_item_instance
1428 (
1429 p_api_version => 1.0
1430 ,p_commit => fnd_api.g_false
1431 ,p_init_msg_list => fnd_api.g_false
1432 ,p_validation_level => fnd_api.g_valid_level_full
1433 ,p_instance_rec => l_instance_rec
1434 ,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
1435 ,p_party_tbl => l_party_tbl
1436 ,p_account_tbl => l_account_tbl
1437 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
1438 ,p_org_assignments_tbl => l_org_assignments_tbl
1439 ,p_asset_assignment_tbl => l_asset_assignment_tbl
1440 ,p_txn_rec => l_txn_rec
1441 ,x_instance_id_lst => x_instance_id_lst
1442 ,x_return_status => x_return_status
1443 ,x_msg_count => x_msg_count
1444 ,x_msg_data => x_msg_data
1445 );
1446
1447
1448 IF x_return_status <> fnd_api.g_ret_sts_success
1449 THEN
1450 raise eaot_api_call_error;
1451 END IF;
1452 -- End of API body.
1453 -- Standard check of p_commit.
1454 IF fnd_api.to_boolean(p_commit) THEN
1455 COMMIT WORK;
1456 END IF;
1457 fnd_msg_pub.count_and_get(
1458 p_count => x_msg_count
1459 ,p_data => x_msg_data);
1460 EXCEPTION
1461 WHEN fnd_api.g_exc_error THEN
1462 ROLLBACK TO EAOT_INSERT_TXN;
1463 x_return_status := fnd_api.g_ret_sts_error;
1464 fnd_msg_pub.count_and_get(
1465 p_count => x_msg_count
1466 ,p_data => x_msg_data);
1467 WHEN fnd_api.g_exc_unexpected_error THEN
1468 ROLLBACK TO EAOT_INSERT_TXN;
1469 x_return_status := fnd_api.g_ret_sts_unexp_error;
1470 fnd_msg_pub.count_and_get(
1471 p_count => x_msg_count
1472 ,p_data => x_msg_data);
1473 WHEN eaot_api_call_error THEN
1474 ROLLBACK TO EAOT_INSERT_TXN;
1475 WHEN eaot_api_desc_error THEN
1476 ROLLBACK TO EAOT_INSERT_TXN;
1477 x_return_status := fnd_api.g_ret_sts_error;
1478 WHEN OTHERS THEN
1479 ROLLBACK TO EAOT_INSERT_TXN;
1480 x_return_status := fnd_api.g_ret_sts_unexp_error;
1481
1482 IF fnd_msg_pub.check_msg_level(
1483 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1484 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1485 END IF;
1486
1487 fnd_msg_pub.count_and_get(
1488 p_count => x_msg_count,
1489 p_data => x_msg_data);
1490
1491 END insert_txn;
1492
1493 END EAM_ASSET_OPERATION_TXN_PVT;