[Home] [Help]
PACKAGE BODY: APPS.EAM_ACTIVITYASSOCIATION_PVT
Source
1 PACKAGE BODY EAM_ActivityAssociation_PVT AS
2 /* $Header: EAMVAAAB.pls 120.5 2006/04/21 08:25:40 kmurthy noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_ActivityAssociation_PVT';
5 G_ACT_SOURCE VARCHAR2(30) := EAM_CONSTANTS.G_ACT_SOURCE;
6 G_ACT_CAUSE VARCHAR2(30) := EAM_CONSTANTS.G_ACT_CAUSE;
7 G_ACT_TYPE VARCHAR2(30) := EAM_CONSTANTS.G_ACT_TYPE;
8 G_SHUTDOWN_TYPE VARCHAR2(30) := EAM_CONSTANTS.G_SHUTDOWN_TYPE;
9 G_ACT_PRIORITY VARCHAR2(30) := EAM_CONSTANTS.G_ACT_PRIORITY;
10
11 PROCEDURE Create_Association
12 ( p_api_version IN NUMBER ,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
16 x_return_status OUT NOCOPY VARCHAR2 ,
17 x_msg_count OUT NOCOPY NUMBER ,
18 x_msg_data OUT NOCOPY VARCHAR2 ,
19
20 p_target_org_id IN NUMBER, -- organzation Asset Activity is in
21 p_target_activity_id IN NUMBER, -- id of Asset Activity
22
23 -- If Copy Source is from Work Order, specify the Work_Entity_Id
24 p_wip_entity_id IN NUMBER := NULL, -- id of Work Order
25 -- If Copy Source is from another Activity, specify the Activity Id and Org Id
26 p_source_org_id IN NUMBER := NULL,
27 p_source_activity_id IN NUMBER := NULL,
28
29 p_association_copy_option IN NUMBER := 2, -- 1 (NONE), 2 (CURRENT), OR 3 (ALL)
30 -- 3 (ALL) is only valid if source work order
31 -- has an activity specified.
32 x_act_num_association_tbl OUT NOCOPY EAM_Activity_PUB.Activity_Association_Tbl_Type,
33 x_activity_association_tbl OUT NOCOPY EAM_Activity_PUB.Activity_Association_Tbl_Type
34
35 )
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'Create_Association';
38 l_api_version CONSTANT NUMBER := 1.0;
39
40 -- local variables
41 l_current_date CONSTANT DATE := sysdate;
42 l_wo_maint_id NUMBER;
43 l_wo_maint_type NUMBER;
44 l_wo_dept_id NUMBER;
45 l_wo_wac VARCHAR2(10);
46 l_wo_priority NUMBER;
47 l_wo_tagout VARCHAR2(1);
48
49 -- derived info from wo item/serial number
50
51 l_wo_item_is_serialized BOOLEAN;
52 l_temp_gen_object_id NUMBER;
53
54 l_cur_source_org_id NUMBER;
55 l_cur_source_activity_id NUMBER;
56 l_cur_maintenance_object_id NUMBER;
57 l_cur_maintenance_object_type NUMBER;
58 l_cur_tmpl_flag VARCHAR2(1);
59
60 l_x_assoc_return_status VARCHAR2(1);
61 l_x_assoc_msg_count NUMBER;
62 l_x_assoc_msg_data VARCHAR2(20000);
63 l_activity_association_tbl EAM_Activity_PUB.Activity_Association_Tbl_Type;
64 l_x_activity_association_tbl EAM_Activity_PUB.Activity_Association_Tbl_Type;
65 l_default_act_assoc_rec EAM_Activity_PUB.Activity_Association_Rec_Type;
66
67 l_act_assoc_tbl_index BINARY_INTEGER;
68 l_source_activity_id NUMBER;
69 l_source_org_id NUMBER;
70
71 l_activity_cause_code VARCHAR2(30);
72 l_activity_type_code VARCHAR2(30);
73 l_activity_source_code VARCHAR2(30);
74 l_tagging_required_flag VARCHAR2(1);
75 l_shutdown_type_code VARCHAR2(30);
76
77 --log variables
78 l_module varchar2(200);
79 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
80 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
81 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
82 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
83
84 CURSOR l_act_assoc_cur (
85 p_source_org_id NUMBER,
86 p_source_activity_id NUMBER,
87 p_maintenance_object_id NUMBER,
88 p_maintenance_object_type NUMBER,
89 p_tmpl_flag VARCHAR2
90 )
91 IS
92 SELECT meaa.Asset_Activity_Id, meaa.start_date_active, meaa.end_date_active,
93 meaa.Priority_Code, meaa.maintenance_object_type, meaa.maintenance_object_id,
94 meaa.template_flag, meaa.Attribute_Category, meaa.Attribute1, meaa.Attribute2,
95 meaa.Attribute3, meaa.Attribute4, meaa.Attribute5, meaa.Attribute6,
96 meaa.Attribute7, meaa.Attribute8, meaa.Attribute9, meaa.Attribute10,
97 meaa.Attribute11, meaa.Attribute12, meaa.Attribute13, meaa.Attribute14,
98 meaa.Attribute15,
99 meaa.Activity_Association_Id, meaa.organization_id, meaa.accounting_class_code,
100 meaa.owning_department_id, meaa.Activity_Cause_Code, meaa.Activity_Type_Code,
101 meaa.Activity_Source_Code, meaa.Tagging_Required_Flag, meaa.Shutdown_Type_Code
102 FROM mtl_eam_asset_activities_v meaa
103 WHERE meaa.asset_activity_id = p_source_activity_id
104 AND (p_maintenance_object_type IS NULL OR meaa.maintenance_object_type = p_maintenance_object_type)
105 AND (p_maintenance_object_id IS NULL OR meaa.maintenance_object_id = p_maintenance_object_id)
106 AND (p_tmpl_flag IS NULL OR NVL(meaa.template_flag, 'N') = p_tmpl_flag)
107 AND meaa.maintenance_object_type IS NOT NULL
108 AND meaa.maintenance_object_id IS NOT NULL
109 AND meaa.organization_id = p_source_org_id;
110
111
112 BEGIN
113 if(l_ulog) then
114 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
115 end if;
116 -- Standard Start of API savepoint
117 SAVEPOINT Create_Association_PVT;
118
119 -- Standard call to check for call compatibility.
120 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
121 p_api_version ,
122 l_api_name ,
123 G_PKG_NAME )
124 THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128 -- Initialize message list if p_init_msg_list is set to TRUE.
129 IF FND_API.to_Boolean( p_init_msg_list ) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 -- Initialize API return status to success
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- API body
137
138 -- ============================================================
139 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
140 '========== Entering EAM_ActivityAssociation_PVT.Create_Association =========='
141 || 'p_target_org_id=' || p_target_org_id
142 || 'p_target_activity_id=' || p_target_activity_id
143 || 'p_wip_entity_id=' || p_wip_entity_id
144 || 'p_source_org_id=' || p_source_org_id
145 || 'p_source_activity_id=' || p_source_activity_id
146 || 'p_association_copy_option=' || p_association_copy_option);
147 end if;
148
149 IF p_association_copy_option = 1 THEN
150 -- copy option = 1 (NONE), nothing to do
151 NULL;
152 ELSE
153 -- copy option is not NONE, need to copy something
154
155 -- Get Item, Serial Number info from Work Order
156 IF p_wip_entity_id IS NOT NULL THEN
157 -- Get the org_id, act_id, item_id, and serial number association with the WO
158 EAM_ActivityUtilities_PVT.Get_Item_Info_From_WO(
159 p_wip_entity_id,
160 l_source_org_id,
161 l_source_activity_id,
162 l_wo_maint_id,
163 l_wo_maint_type
164 );
165 ELSE
166 -- p_wip_entity_id IS NULL; source activity is specified instead.
167 l_source_org_id := p_source_org_id;
168 l_source_activity_id := p_source_activity_id;
169 l_wo_maint_id := NULL;
170 l_wo_maint_type := NULL;
171 END IF;
172
173 -- Validate l_source_org_id should not be Null
174 IF l_source_org_id IS NULL THEN
175 EAM_ActivityUtilities_PVT.Write_Debug('l_source_ord_id should not be NULL');
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178
179 -- After this point l_source_activity_id and l_source_org_id are defined,
180 -- should use them instead of p_source_activity_id and p_source_org_id.
181
182 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
183 'l_source_org_id=' || l_source_org_id
184 || 'l_source_activity_id=' || l_source_activity_id
185 || 'l_wo_maint_id=' || l_wo_maint_id
186 || 'l_wo_maint_type=' || l_wo_maint_id);
187 end if;
188 /*
189 -- Derived Work Order item/serial number information.
190 -- note gen_object_id could be Null if l_wo_serial_number is Null.
191 l_wo_gen_object_id := EAM_ActivityUtilities_PVT.Get_Gen_Object_id(l_source_org_id,
192 l_wo_item_id,
193 l_wo_serial_number);
194 EAM_ActivityUtilities_PVT.Write_Debug('l_wo_gen_object_id=' || l_wo_gen_object_id);
195 */
196 l_wo_item_is_serialized := EAM_ActivityUtilities_PVT.Is_Item_Serialized(l_source_org_id, l_wo_maint_id, l_wo_maint_type);
197
198 IF l_wo_item_is_serialized = TRUE THEN
199
200 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
201 'l_wo_item_is_serialized=TRUE');
202 end if;
203
204 ELSIF l_wo_item_is_serialized = FALSE THEN
205
206 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
207 'l_wo_item_is_serialized=FALSE');
208 end if;
209
210 ELSE
211 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
212 'l_wo_item_is_serialized=');
213 end if;
214
215 END IF;
216
217 -- Get target Activity Properties
218 BEGIN
219 SELECT eam_activity_cause_code, eam_activity_type_code, eam_act_notification_flag,
220 eam_act_shutdown_status, eam_activity_source_code
221 INTO l_activity_cause_code, l_activity_type_code, l_tagging_required_flag,
222 l_shutdown_type_code, l_activity_source_code
223 FROM mtl_system_items
224 WHERE inventory_item_id = p_target_activity_id
225 AND organization_id = p_target_org_id;
226 EXCEPTION
227 WHEN OTHERS THEN
228 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
229 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_ACT_PROPERTIES');
230 FND_MSG_PUB.ADD;
231 END IF;
232 RAISE FND_API.G_EXC_ERROR;
233 END;
234
235 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
236 'l_activity_cause_code=' || l_activity_cause_code
237 || 'l_activity_type_code=' || l_activity_type_code
238 || 'l_tagging_required_flag=' || l_tagging_required_flag
239 || 'l_shutdown_type_code=' || l_shutdown_type_code
240 || 'l_activity_source_code=' || l_activity_source_code);
241 end if;
242
243 --Default new association record
244 l_default_act_assoc_rec.Organization_Id := p_target_org_id;
245 l_default_act_assoc_rec.Asset_Activity_Id := p_target_activity_id;
246 l_default_act_assoc_rec.Start_Date_Active := l_current_date;
247 l_default_act_assoc_rec.End_Date_Active := NULL;
248 -- l_default_act_assoc_rec.Inventory_Item_Id := l_wo_item_id;
249 -- l_default_act_assoc_rec.Serial_Number := l_wo_serial_number;
250
251 IF (l_wo_maint_type = 3) THEN
252 l_default_act_assoc_rec.Owning_Department_Id :=
253 EAM_ActivityUtilities_PVT.Default_Owning_Department_Id(NULL, l_wo_maint_id, l_source_org_id);
254 ELSE
255 l_default_act_assoc_rec.Owning_Department_Id :=
256 EAM_ActivityUtilities_PVT.Default_Owning_Department_Id(NULL, null, l_source_org_id);
257 END IF;
258
259 l_default_act_assoc_rec.Tmpl_Flag := 'N';
260 -- l_default_act_assoc_rec.Creation_Organization_Id := p_target_org_id;
261 -- set Activity Columns
262 l_default_act_assoc_rec.Activity_Cause_Code := l_activity_cause_code;
263 l_default_act_assoc_rec.Activity_Type_Code := l_activity_type_code;
264 l_default_act_assoc_rec.Activity_Source_Code := l_activity_source_code;
265 l_default_act_assoc_rec.Tagging_Required_Flag := l_tagging_required_flag;
266 l_default_act_assoc_rec.Shutdown_Type_Code := l_shutdown_type_code;
267
268 -- Default cursor parameters
269 l_cur_source_org_id := l_source_org_id;
270 l_cur_source_activity_id := l_source_activity_id;
271
272 IF p_association_copy_option = 2 THEN
273 -- copy option = 2 (CURRENT)
274
275 -- Default cursor parameter for CURRENT
276 l_cur_tmpl_flag := 'N';
277
278 IF l_wo_maint_id IS NOT NULL and l_wo_maint_type IS NOT NULL AND
279 ((NOT l_wo_item_is_serialized AND l_wo_maint_type = 2) OR
280 (l_wo_item_is_serialized AND l_wo_maint_type = 3)) THEN
281
282 IF l_source_activity_id IS NOT NULL THEN
283 -- Case 1a: Serial Number / Non-Serialized Item with Activity
284 -- Need to limit Association cursor to current
285 l_cur_maintenance_object_id := l_wo_maint_id;
286 l_cur_maintenance_object_type := l_wo_maint_type;
287
288 ELSE
289 -- Case 1b: Serial Number / Non-Serialized Item without Activity
290 -- Need to create Association; cursor should select no row.
291 l_cur_source_org_id := NULL;
292 l_cur_source_activity_id := NULL;
293 l_activity_association_tbl(1) := l_default_act_assoc_rec;
294 l_activity_association_tbl(1).Maintenance_Object_Id := l_wo_maint_id;
295 l_activity_association_tbl(1).Maintenance_Object_Type := l_wo_maint_type;
296
297 END IF;
298
299 ELSIF l_wo_maint_id IS NOT NULL AND l_wo_maint_type IS NOT NULL AND
300 (l_wo_item_is_serialized AND l_wo_maint_type = 2) THEN
301 -- Case 2: Serialized Item with no Serial Number
302 -- Copy none, do nothing.
303 l_cur_source_org_id := NULL;
304 l_cur_source_activity_id := NULL;
305
306
307 ELSIF l_wo_maint_id IS NULL AND l_wo_maint_type IS NULL AND
308 l_source_activity_id IS NOT NULL THEN
309 -- Case 3: Copy from Activity
310 -- Treat as Copy ALL, include all assoc in the Association cursor.
311 l_cur_maintenance_object_id := NULL;
312 l_cur_maintenance_object_type := NULL;
313 l_cur_tmpl_flag := NULL;
314
315 ELSE
316 -- shouldn't be here.
317 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
318 'Copy CURRENT: unexpected Work Order Item/Serial Number inputs.');
319 end if;
320
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324 ELSIF p_association_copy_option = 3 THEN
325 -- copy option = 3 (ALL)
326
327 -- Default cursor parameter for ALL
328 l_cur_tmpl_flag := NULL;
329
330 IF l_wo_maint_id IS NOT NULL and l_wo_maint_type IS NOT NULL AND
331 ((NOT l_wo_item_is_serialized AND l_wo_maint_type = 2) OR
332 (l_wo_item_is_serialized AND l_wo_maint_type = 3)) THEN
333
334 IF l_source_activity_id IS NOT NULL THEN
335 -- Case 1a: Serial Number / Non-Serialized Item with Activity
336 -- Include all Associations in Association Cursor.
337 l_cur_maintenance_object_id := NULL;
338 l_cur_maintenance_object_type := NULL;
339 ELSE
340 -- Case 1b: Serial Number / Non-Serialized Item without Activity
341 -- Need to create Association for current; cursor should select no row.
342 l_cur_source_org_id := NULL;
343 l_cur_source_activity_id := NULL;
344 l_activity_association_tbl(1) := l_default_act_assoc_rec;
345 l_activity_association_tbl(1).Maintenance_Object_Id := l_wo_maint_id;
346 l_activity_association_tbl(1).Maintenance_Object_Type := l_wo_maint_type;
347 END IF;
348
349 ELSIF l_wo_maint_id IS NOT NULL AND l_wo_maint_type IS NOT NULL AND
350 (l_wo_item_is_serialized AND l_wo_maint_type = 2) THEN
351 -- Case 2a: Serialized Item without Serial Number - with Activity
352 -- Copy none, do nothing.
353 l_cur_source_org_id := NULL;
354 l_cur_source_activity_id := NULL;
355
356 ELSIF l_wo_maint_id IS NULL AND l_wo_maint_type IS NULL AND
357 l_source_activity_id IS NOT NULL THEN
358 -- Case 3: Copy from Activity
359 -- Include all assoc in the Association cursor.
360 l_cur_maintenance_object_id := NULL;
361 l_cur_maintenance_object_type := NULL;
362
363 ELSE
364 -- shouldn't be here.
365 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
366 'Copy ALL: unexpected Work Order Item/Serial Number inputs.');
367 end if;
368
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370 END IF;
371
372 ELSE
373 -- copy option outside of valid range, shouldn't be here.
374 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
375 'Create_Association: unexpected Association Copy Option');
376 end if;
377
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
379 END IF;
380 END IF;
381
382
383 -- Copy associations from Cursor to table
384 l_act_assoc_tbl_index := NVL(l_activity_association_tbl.LAST, 0) + 1;
385
386 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
387 'l_cur_source_org_id=' || l_cur_source_org_id
388 || 'l_cur_source_activity_id=' || l_cur_source_activity_id
389 || 'l_cur_maintenance_object_id=' || l_cur_maintenance_object_id
390 || 'l_cur_maintenance_object_type=' || l_cur_maintenance_object_type
391 || 'l_cur_tmpl_flag=' || l_cur_tmpl_flag);
392 end if;
393
394 FOR l_act_assoc_row IN l_act_assoc_cur(l_cur_source_org_id,
395 l_cur_source_activity_id,
396 l_cur_maintenance_object_id,
397 l_cur_maintenance_object_type,
398 l_cur_tmpl_flag)
399 LOOP
400
401 l_activity_association_tbl(l_act_assoc_tbl_index).Organization_Id := p_target_org_id;
402 l_activity_association_tbl(l_act_assoc_tbl_index).Asset_Activity_Id := p_target_activity_id;
403 l_activity_association_tbl(l_act_assoc_tbl_index).Start_Date_Active := l_act_assoc_row.Start_Date_Active;
404 l_activity_association_tbl(l_act_assoc_tbl_index).End_Date_Active := l_act_assoc_row.End_Date_Active;
405 l_activity_association_tbl(l_act_assoc_tbl_index).Priority_Code := l_act_assoc_row.Priority_Code;
406 IF l_cur_maintenance_object_type = 3 THEN
407 l_temp_gen_object_id := l_cur_maintenance_object_id;
408 ELSE
409 l_temp_gen_object_id := NULL;
410 END IF;
411 l_activity_association_tbl(l_act_assoc_tbl_index).Owning_Department_Id :=
412 NVL(l_act_assoc_row.Owning_Department_Id,
413 EAM_ActivityUtilities_PVT.Default_Owning_Department_Id(
414 NULL, l_cur_maintenance_object_id, l_cur_source_org_id));
415 -- Set Activity Columns
416 l_activity_association_tbl(l_act_assoc_tbl_index).Activity_Cause_Code := l_activity_cause_code;
417 l_activity_association_tbl(l_act_assoc_tbl_index).Activity_Type_Code := l_activity_type_code;
418 l_activity_association_tbl(l_act_assoc_tbl_index).Activity_Source_Code := l_activity_source_code;
419 l_activity_association_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag := l_tagging_required_flag;
420 l_activity_association_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code := l_shutdown_type_code;
421 l_activity_association_tbl(l_act_assoc_tbl_index).Class_Code := l_act_assoc_row.accounting_Class_Code;
422
423 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute_Category := l_act_assoc_row.Attribute_Category;
424 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute1 := l_act_assoc_row.Attribute1;
425 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute2 := l_act_assoc_row.Attribute2;
426 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute3 := l_act_assoc_row.Attribute3;
427 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute4 := l_act_assoc_row.Attribute4;
428 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute5 := l_act_assoc_row.Attribute5;
429 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute6 := l_act_assoc_row.Attribute6;
430 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute7 := l_act_assoc_row.Attribute7;
431 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute8 := l_act_assoc_row.Attribute8;
432 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute9 := l_act_assoc_row.Attribute9;
433 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute10 := l_act_assoc_row.Attribute10;
434 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute11 := l_act_assoc_row.Attribute11;
435 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute12 := l_act_assoc_row.Attribute12;
436 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute13 := l_act_assoc_row.Attribute13;
437 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute14 := l_act_assoc_row.Attribute14;
438 l_activity_association_tbl(l_act_assoc_tbl_index).Attribute15 := l_act_assoc_row.Attribute15;
439
440 l_activity_association_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type := l_act_assoc_row.Maintenance_Object_Type;
441 l_activity_association_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id := l_act_assoc_row.Maintenance_Object_Id;
442 l_activity_association_tbl(l_act_assoc_tbl_index).Tmpl_Flag := l_act_assoc_row.template_flag;
443
444 l_act_assoc_tbl_index := l_act_assoc_tbl_index + 1;
445
446 END LOOP;
447
448 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
449 'l_act_assoc_tbl_index=' || l_act_assoc_tbl_index);
450 end if;
451
452 -- assign outputs
453 x_act_num_association_tbl := l_activity_association_tbl;
454
455 -- Call Procedure to creatwe Asset Number Association
456 Create_AssetNumberAssoc(
457 p_api_version => 1.0,
458 x_return_status => l_x_assoc_return_status,
459 x_msg_count => l_x_assoc_msg_count,
460 x_msg_data => l_x_assoc_msg_data,
461
462 p_activity_association_tbl => l_activity_association_tbl,
463 x_activity_association_tbl => l_x_activity_association_tbl
464 );
465
466 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
467 'l_x_assoc_return_status=' || l_x_assoc_return_status
468 || 'l_x_assoc_msg_count=' || l_x_assoc_msg_count
469 || 'l_x_assoc_msg_data' || l_x_assoc_msg_data);
470 end if;
471
472 -- assign outputs
473 x_activity_association_tbl := l_x_activity_association_tbl;
474
475 -- Handle errors
476 IF l_x_assoc_return_status <> FND_API.G_RET_STS_SUCCESS THEN
477 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_AN_ASSOC_FAILED');
478 -- FND_MESSAGE.SET_ENCODED('Failed to create Asset Number Associations.');
479 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482
483 -- Bug # 4089189 : Need to default WAC and dept from WO to the Activity Association with the Asset in WO.
484 IF p_wip_entity_id IS NOT NULL THEN
485 BEGIN
486 SELECT owning_department, class_code, maintenance_object_id, maintenance_object_type, priority, tagout_required
487 INTO l_wo_dept_id, l_wo_wac, l_cur_maintenance_object_id, l_cur_maintenance_object_type, l_wo_priority, l_wo_tagout
488 FROM wip_discrete_jobs
489 WHERE wip_entity_id = p_wip_entity_id;
490
491 UPDATE mtl_eam_asset_activities
492 SET priority_code = nvl(l_wo_priority, priority_code)
493 WHERE asset_activity_id = p_target_activity_id AND maintenance_object_id = l_cur_maintenance_object_id
494 AND maintenance_object_type = l_cur_maintenance_object_type;
495
496 UPDATE eam_org_maint_defaults
497 SET accounting_class_code = nvl(l_wo_wac, accounting_class_code),
498 owning_department_id = nvl(l_wo_dept_id, owning_department_id),
499 tagging_required_flag = nvl(l_wo_tagout, tagging_required_flag)
500 WHERE object_id in (SELECT activity_association_id
501 FROM mtl_eam_asset_activities
502 WHERE asset_activity_id = p_target_activity_id
503 AND maintenance_object_id = l_cur_maintenance_object_id
504 AND maintenance_object_type = l_cur_maintenance_object_type)
505 AND object_type in (40, 60) AND organization_id = p_target_org_id;
506
507 EXCEPTION
508 WHEN NO_DATA_FOUND THEN
509 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
510 'EAM_ActivityAssociation_PVT.Create_Association: unexpected error.'
511 || 'p_wip_entity_id=' || p_wip_entity_id);
512 end if;
513
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END;
516
517 END IF;
518
519 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
520 '========== Exiting EAM_ActivityAssociation_PVT.Create_Association ==========');
521 end if;
522
523 -- ============================================================
524
525 -- End of API body.
526 -- Standard check of p_commit.
527 IF FND_API.To_Boolean( p_commit ) THEN
528 COMMIT WORK;
529 END IF;
530 -- Standard call to get message count and if count is 1, get message info.
531 FND_MSG_PUB.Count_And_Get
532 ( p_count => x_msg_count ,
533 p_data => x_msg_data
534 );
535 x_msg_data := substr(x_msg_data,1,4000);
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN
538 ROLLBACK TO Create_Association_PVT;
539 x_return_status := FND_API.G_RET_STS_ERROR ;
540 FND_MSG_PUB.Count_And_Get
541 ( p_count => x_msg_count ,
542 p_data => x_msg_data
543 );
544 x_msg_data := substr(x_msg_data,1,4000);
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 ROLLBACK TO Create_Association_PVT;
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
548 FND_MSG_PUB.Count_And_Get
549 ( p_count => x_msg_count ,
550 p_data => x_msg_data
551 );
552 x_msg_data := substr(x_msg_data,1,4000);
553 WHEN OTHERS THEN
554 ROLLBACK TO Create_Association_PVT;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556 IF FND_MSG_PUB.Check_Msg_Level
557 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
558 THEN
559 FND_MSG_PUB.Add_Exc_Msg
560 ( G_PKG_NAME ,
561 l_api_name
562 );
563 END IF;
564 FND_MSG_PUB.Count_And_Get
565 ( p_count => x_msg_count ,
566 p_data => x_msg_data
567 );
568 x_msg_data := substr(x_msg_data,1,4000);
569 END Create_Association;
570
571
572 PROCEDURE Create_AssetNumberAssoc
573 ( p_api_version IN NUMBER ,
574 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
575 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
576 p_validation_level IN NUMBER :=
577 FND_API.G_VALID_LEVEL_FULL ,
578 x_return_status OUT NOCOPY VARCHAR2 ,
579 x_msg_count OUT NOCOPY NUMBER ,
580 x_msg_data OUT NOCOPY VARCHAR2 ,
581
582 p_activity_association_tbl IN EAM_Activity_PUB.Activity_Association_Tbl_Type,
583 x_activity_association_tbl OUT NOCOPY EAM_Activity_PUB.Activity_Association_Tbl_Type
584 )
585 IS
586 l_api_name CONSTANT VARCHAR2(30) := 'Create_AsetNumberAssoc';
587 l_api_version CONSTANT NUMBER := 1.0;
588
589 -- local variables
590 l_count NUMBER;
591 l_object_type NUMBER;
592
593 l_maintenance_object_id NUMBER;
594 l_maintenance_object_type NUMBER;
595
596 l_current_date CONSTANT DATE := sysdate;
597 l_act_assoc_tbl_index BINARY_INTEGER;
598 l_act_assoc_tbl EAM_Activity_PUB.Activity_Association_Tbl_Type;
599
600 l_x_return_status VARCHAR2(1);
601 l_failed BOOLEAN;
602
603 --log variables
604 l_module varchar2(200) ;
605 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
606 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
607 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
608 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
609
610 BEGIN
611 if(l_ulog) then
612 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
613 end if;
614
615 -- Standard Start of API savepoint
616 SAVEPOINT Create_AssetNumberAssoc_PVT;
617
618 -- Standard call to check for call compatibility.
619 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
620 p_api_version ,
621 l_api_name ,
622 G_PKG_NAME )
623 THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 END IF;
626
627 -- Initialize message list if p_init_msg_list is set to TRUE.
628 IF FND_API.to_Boolean( p_init_msg_list ) THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631 -- Initialize API return status to success
632
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 -- API body
636 -- ============================================================
637 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
638 '========== Entering EAM_ActivityAssociation_PVT.Create_AssetNumberAssoc =========='
639 || 'p_activity_association_tbl.COUNT=' || p_activity_association_tbl.COUNT);
640 end if;
641
642 -- Copy input table to local working variable
643 l_act_assoc_tbl := p_activity_association_tbl;
644
645 -- Loop through the rows of the table
646 l_act_assoc_tbl_index := l_act_assoc_tbl.FIRST;
647 l_failed := FALSE;
648
649 WHILE l_act_assoc_tbl_index IS NOT NULL
650 LOOP
651
652 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
653 'l_act_assoc_tbl_index=' || l_act_assoc_tbl_index ||
654 '; Organization_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id ||
655 '; Asset_Activity_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id ||
656 '; instance_number=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Instance_number ||
657 '; Inventory_Item_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Inventory_Item_Id ||
658 '; Serial_Number=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Serial_Number);
659
660 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
661 'Maintenance_Object_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id ||
662 '; Maintenance_Object_Type=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type);
663 end if;
664
665 -- Validate Organization
666 SELECT count(*) INTO l_count
667 FROM wip_eam_parameters
668 WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id;
669
670 IF l_count <> 1 THEN
671 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
672 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ORG_ID');
673 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
674
675 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
676 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
677
678 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
679 'Organization_Id='
680 || l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id);
681 end if;
682
683 l_failed := TRUE;
684 GOTO next_in_loop;
685 END IF;
686
687 -- Validate Activity
688 SELECT count(*) INTO l_count
689 FROM mtl_system_items
690 WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
691 AND inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id
692 AND eam_item_type = 2;
693
694 IF l_count <> 1 THEN
695 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
696 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ACTIVITY_ID');
697 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Id.');
698 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
699
700 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
701 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
702
703 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
704 'Asset_Activity_Id='
705 || l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id);
706 end if;
707
708 l_failed := TRUE;
709 GOTO next_in_loop;
710
711 END IF;
712
713 IF l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active IS NOT NULL AND
714 l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active IS NOT NULL
715 THEN
716 -- Start Date and End Date
717 IF l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active <
718 l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active
719 THEN
720 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
721 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_START_END_DATE');
722 -- FND_MESSAGE.SET_ENCODED('End Date cannot be before Start Date.');
723 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
724
725 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
726 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
727
728 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
729 'Asset_Activity_Id='
730 || l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id
731 || 'Start_Date_Active=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active ||
732 '; End_Date_Active=' || l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active);
733
734 end if;
735
736 l_failed := TRUE;
737 GOTO next_in_loop;
738
739 END IF;
740 END IF;
741
742 -- Validate Priority Code
743 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code IS NOT NULL THEN
744 select count(*) into l_count
745 from mfg_lookups
746 where lookup_type = g_act_priority
747 and sysdate between nvl(start_date_active,sysdate)
748 and nvl(end_date_active,sysdate)
749 and nvl(enabled_flag, 'N') = 'Y'
750 and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code;
751
752 IF l_count <> 1 THEN
753 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
754 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ACT_PRI_CODE');
755 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Priority Code.');
756 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
757
758 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
759 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
760
761 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
762 'Priority_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code);
763 end if;
764
765 l_failed := TRUE;
766 GOTO next_in_loop;
767 END IF;
768 END IF;
769
770 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id IS NOT NULL THEN
771 -- Validate Owning Department
772 SELECT count(*) INTO l_count
773 FROM bom_departments
774 WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
775 and department_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id
776 and (disable_date IS NULL
777 or disable_date > sysdate);
778
779 IF l_count <> 1 THEN
780 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
781 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_OWN_DEPT_ID');
782 -- FND_MESSAGE.SET_ENCODED('Invalid Owning Department Id.');
783 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
784
785 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
786 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
787
788 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
789 'Owning_Department_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id);
790 end if;
791
792 l_failed := TRUE;
793 GOTO next_in_loop;
794
795 END IF;
796 END IF;
797
798 -- Validate Activity Type Code
799 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code IS NOT NULL THEN
800 select count(*) into l_count
801 from mfg_lookups
802 where lookup_type = g_act_type
803 and sysdate between nvl(start_date_active,sysdate)
804 and nvl(end_date_active,sysdate)
805 and nvl(enabled_flag, 'N') = 'Y'
806 and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code;
807
808 IF l_count <> 1 THEN
809 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
810 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ACT_TYPE_CODE');
811 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Type Code.');
812 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
813
814 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
815 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
816
817 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
818 'Activity_Type_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code);
819 end if;
820
821 l_failed := TRUE;
822 GOTO next_in_loop;
823 END IF;
824 END IF;
825
826 -- Validate Activity Cause Code
827 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code IS NOT NULL THEN
828 select count(*) into l_count
829 from mfg_lookups
830 where lookup_type = g_act_cause
831 and sysdate between nvl(start_date_active,sysdate)
832 and nvl(end_date_active,sysdate)
833 and nvl(enabled_flag, 'N') = 'Y'
834 and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code;
835
836 IF l_count <> 1 THEN
837 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
838 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ACT_CAUSE_CODE');
839 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Cause Code.');
840 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
841
842 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
843 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
844
845 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
846 'Activity_Cause_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code);
847 end if;
848
849 l_failed := TRUE;
850 GOTO next_in_loop;
851 END IF;
852 END IF;
853
854 -- Validate Activity Source Code
855 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code IS NOT NULL THEN
856 select count(*) into l_count
857 from mfg_lookups
858 where lookup_type = g_act_source
859 and sysdate between nvl(start_date_active,sysdate)
860 and nvl(end_date_active,sysdate)
861 and nvl(enabled_flag, 'N') = 'Y'
862 and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code;
863
864 IF l_count <> 1 THEN
865 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
866 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ACT_SRC_CODE');
867 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Source Code.');
868 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
869
870 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
871 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
872
873 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
874 'Activity_Source_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code);
875 end if;
876
877 l_failed := TRUE;
878 GOTO next_in_loop;
879 END IF;
880 END IF;
881
882 -- Validate Shutdown Type Code
883 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code IS NOT NULL THEN
884 select count(*) into l_count
885 from mfg_lookups
886 where lookup_type = g_shutdown_type
887 and sysdate between nvl(start_date_active,sysdate)
888 and nvl(end_date_active,sysdate)
889 and nvl(enabled_flag, 'N') = 'Y'
890 and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code;
891
892 IF l_count <> 1 THEN
893 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
894 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_SHUTDOWN_CODE');
895 -- FND_MESSAGE.SET_ENCODED('Invalid Activity Shutdown Type Code.');
896 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
897
898 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
899 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
900
901 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
902 'Shutdown_Type_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code);
903 end if;
904
905 l_failed := TRUE;
906 GOTO next_in_loop;
907 END IF;
908 END IF;
909
910 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code IS NOT NULL THEN
911 select count(*) into l_count
912 from wip_accounting_classes
913 where class_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code
914 and organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
915 and class_type = 6
916 and (disable_date is null or sysdate < disable_date);
917
918 IF l_count <> 1 THEN
919 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
920 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_CLASS_CODE');
921 -- FND_MESSAGE.SET_ENCODED('Invalid Wip Accounting Class Code.');
922 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
923
924 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
925 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
926
927 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
928 'Class_Code=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code);
929 end if;
930
931 l_failed := TRUE;
932 GOTO next_in_loop;
933 END IF;
934 END IF;
935
936 -- Validate Tagging Required Flag
937 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag IS NOT NULL AND
938 l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag <> 'Y' AND
939 l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag <> 'N'
940 THEN
941 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
942 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_TAG_REQ_FLAG');
943 -- FND_MESSAGE.SET_ENCODED('Tagging Required Flag should be either Y, N, or NULL.');
944 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
945
946 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
947 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
948
949 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
950 'Tagging_Required_Flag=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag);
951 end if;
952
953 l_failed := TRUE;
954 GOTO next_in_loop;
955 END IF;
956
957 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type IS NULL or
958 l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id IS NULL
959 THEN
960 -- Find Maintenance_Object_Id and Maintenance_Object_Type
961 -- Validate Asset Number, or Inventory_Item_Id + Serial_Number
962 EAM_ActivityUtilities_PVT.Validate_Asset_Number(
963 p_instance_number => l_act_assoc_tbl(l_act_assoc_tbl_index).Instance_number,
964 p_organization_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_id,
965 p_inventory_item_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Inventory_Item_Id,
966 p_serial_number => l_act_assoc_tbl(l_act_assoc_tbl_index).Serial_Number,
967
968 x_return_status => l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status,
969 x_error_mesg => l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg,
970
971 x_maintenance_object_id => l_maintenance_object_id,
972 x_maintenance_object_type => l_maintenance_object_type
973 );
974
975 l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_id := l_maintenance_object_id;
976 l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_type := l_maintenance_object_type;
977
978 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
979
980 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
981 'Failed Validate Serial Number: Instance_number, or Inventory_Item_Id + Serial_Number'
982 || 'Return_Status=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status ||
983 '; Error_Mesg=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg
984 || 'Instance_number=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Instance_number ||
985 '; Inventory_Item_Id=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Inventory_Item_Id ||
986 '; Serial_Number=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Serial_Number);
987 end if;
988
989 l_failed := TRUE;
990 GOTO next_in_loop;
991 END IF;
992 END IF;
993
994 -- Validate Maintenance Object Type
995 -- Can only be 3 or 2.
996 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type NOT IN (3, 2)
997 THEN
998 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
999 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_MAINT_OBJ_TYPE');
1000 -- FND_MESSAGE.SET_ENCODED('Maintenance Object Type should be 1 or 2.');
1001 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1002
1003 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1004 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1005
1006 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1007 'Maintenance_Object_Type=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type);
1008 end if;
1009
1010 l_failed := TRUE;
1011 GOTO next_in_loop;
1012 END IF;
1013
1014 -- Validate Maintenance Object Id
1015 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type = 3
1016 THEN
1017 -- type = 3, id is cii.instance_id
1018 SELECT count(cii.instance_id) into l_count
1019 FROM csi_item_instances cii, mtl_system_items_b msi, mtl_parameters mp
1020 WHERE cii.instance_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
1021 AND mp.organization_id = cii.last_vld_organization_id
1022 AND mp.maint_organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
1023 AND cii.last_vld_organization_id = msi.organization_id
1024 AND cii.inventory_item_id = msi.inventory_item_id
1025 AND msi.eam_item_type in (1,3)
1026 AND msi.serial_number_control_code <> 1
1027 AND nvl(cii.active_start_date, sysdate-1) <= sysdate
1028 AND nvl(cii.active_end_date, sysdate+1) >= sysdate;
1029
1030 IF l_count <> 1 THEN
1031 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1032 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVLD_MT_GEN_OBJ_ID');
1033 -- FND_MESSAGE.SET_ENCODED('Maintenance Object Id should be a valid gen_object_id.');
1034 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1035
1036 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1037 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1038
1039 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1040 'Maintenance_Object_Id=' ||
1041 l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id);
1042 end if;
1043
1044 l_failed := TRUE;
1045 GOTO next_in_loop;
1046 END IF;
1047
1048 ELSIF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type = 2
1049 THEN
1050 -- type = 2
1051 SELECT count(inventory_item_id) into l_count
1052 FROM mtl_system_items_b msi, mtl_parameters mp
1053 WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
1054 AND mp.maint_organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
1055 AND mp.organization_id = msi.organization_id
1056 AND msi.eam_item_type in (1,3);
1057
1058 IF l_count < 1 THEN
1059 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1060 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVLD_MT_ITM_OBJ_ID');
1061 -- FND_MESSAGE.SET_ENCODED('Maintenance Object Id should be a valid inventory_item_id.');
1062 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1063
1064 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1065 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1066
1067 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1068 'Maintenance_Object_Id=' ||
1069 l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id);
1070 end if;
1071
1072 l_failed := TRUE;
1073 GOTO next_in_loop;
1074 END IF;
1075 ELSE
1076 -- Shouldn't be here
1077 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1078
1079 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1080 'Maintenance_Object_Type is neither 1 nor 2');
1081 end if;
1082
1083 l_failed := TRUE;
1084 GOTO next_in_loop;
1085 END IF;
1086
1087
1088 -- Validate Tmpl_Flag, can only be NULL, Y, N.
1089 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag IS NOT NULL
1090 THEN
1091 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag NOT IN ('Y', 'N')
1092 THEN
1093 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1094 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_TMPL_FLAG');
1095 -- FND_MESSAGE.SET_ENCODED('Tmpl Flag should be Y, N, or NULL.');
1096 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1097
1098 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1099 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1100
1101 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1102 'Tmpl_Flag=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag);
1103 end if;
1104
1105 l_failed := TRUE;
1106 GOTO next_in_loop;
1107 END IF;
1108 ELSE
1109 l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag := 'N';
1110 END IF;
1111
1112
1113 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag = 'Y' THEN
1114 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type <> 2 THEN
1115 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1116 FND_MESSAGE.SET_NAME('EAM', 'EAM_IAA_INV_TEML_FLAG');
1117 -- FND_MESSAGE.SET_ENCODED(' Invalid Template flag value.');
1118 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1119
1120 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1121 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1122
1123 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1124 'Tmpl_Flag=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag);
1125 end if;
1126
1127 l_failed := TRUE;
1128 GOTO next_in_loop;
1129 ELSE
1130 SELECT serial_number_control_code into l_count
1131 FROM mtl_system_items_b msi
1132 WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
1133 AND rownum = 1;
1134
1135 IF l_count = 1 THEN
1136 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1137 FND_MESSAGE.SET_NAME('EAM', 'EAM_NON_SERIAL_REBUILD_ASSOC');
1138 -- FND_MESSAGE.SET_ENCODED('Cannot assoicate Non-Serialized Rebuildables to Templates.');
1139 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1140
1141 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1142 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1143
1144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1145 'Tmpl_Flag=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag);
1146 end if;
1147
1148 l_failed := TRUE;
1149 GOTO next_in_loop;
1150 END IF;
1151
1152 END IF;
1153 ELSE
1154 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type = 2 THEN
1155 SELECT serial_number_control_code into l_count
1156 FROM mtl_system_items_b msi
1157 WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
1158 AND rownum = 1;
1159
1160 IF l_count <> 1 THEN
1161 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1162 FND_MESSAGE.SET_NAME('EAM', 'EAM_IAA_INV_TEML_FLAG');
1163 -- FND_MESSAGE.SET_ENCODED(' Invalid Template flag value.');
1164 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1165
1166 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1167 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1168
1169 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1170 'Tmpl_Flag=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag);
1171 end if;
1172
1173 l_failed := TRUE;
1174 GOTO next_in_loop;
1175 END IF;
1176 END IF;
1177 END IF;
1178
1179 -- Check Unique
1180 select count(1) into l_count
1181 from mtl_eam_asset_activities
1182 where asset_activity_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id
1183 and maintenance_object_id = l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_id
1184 and maintenance_object_type = l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_type;
1185 /*
1186 and ( -- condition for detecting overlapping dates
1187 ( (end_date_active >= l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active
1188 or end_date_active IS NULL)
1189 and (start_date_active < l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active
1190 or l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active IS NULL
1191 or start_date_active IS NULL)
1192 )
1193
1194 or ( (end_date_active > l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active
1195 or l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active IS NULL
1196 or end_date_active IS NULL)
1197 and (start_date_active <= l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active
1198 or start_date_active IS NULL)
1199 )
1200
1201 or ( (start_date_active <= l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active
1202 or start_date_active IS NULL)
1203 and (end_date_active >= l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active
1204 or end_date_active IS NULL)
1205 )
1206 );
1207 */
1208
1209 if l_count >= 1 then
1210 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1211 fnd_message.set_name('EAM','EAM_DUPLICATE_ASSET_ACTIVITIES');
1212 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1213
1214
1215 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1216 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1217
1218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1219 'l_count=' || l_count);
1220 end if;
1221
1222 l_failed := TRUE;
1223 GOTO next_in_loop;
1224 end if;
1225
1226
1227 -- ----------------------------------------------------------------------
1228 -- Insert into database table
1229
1230 BEGIN
1231 -- Get activity_association_id from sequence
1232 SELECT mtl_eam_asset_activities_s.nextval
1233 INTO l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id
1234 FROM dual;
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237
1238 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1239 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_ACT_ASSOC_ID');
1240 -- FND_MESSAGE.SET_ENCODED('Error creating Activity_Association_Id.');
1241 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1242
1243 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1244 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1245 end if;
1246
1247 l_failed := TRUE;
1248 GOTO next_in_loop;
1249 END;
1250
1251 BEGIN
1252 -- Insert into Database Table mtl_eam_asset_activities
1253 INSERT INTO mtl_eam_asset_activities (
1254 Asset_Activity_Id,
1255 Start_Date_Active,
1256 End_Date_Active,
1257 Priority_Code,
1258 Last_Update_Date,
1259 Last_Updated_By,
1260 Creation_Date,
1261 Created_By,
1262 Last_Update_Login,
1263 Attribute_Category,
1264 Attribute1,
1265 Attribute2,
1266 Attribute3,
1267 Attribute4,
1268 Attribute5,
1269 Attribute6,
1270 Attribute7,
1271 Attribute8,
1272 Attribute9,
1273 Attribute10,
1274 Attribute11,
1275 Attribute12,
1276 Attribute13,
1277 Attribute14,
1278 Attribute15,
1279 Activity_Association_Id,
1280 Maintenance_Object_Id,
1281 Maintenance_Object_Type,
1282 Tmpl_Flag
1283 ) VALUES (
1284 l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id,
1285 l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active,
1286 l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active,
1287 l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code,
1288 l_current_date,
1289 FND_GLOBAL.USER_ID,
1290 l_current_date,
1291 FND_GLOBAL.USER_ID,
1292 FND_GLOBAL.LOGIN_ID,
1293 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute_Category,
1294 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute1,
1295 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute2,
1296 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute3,
1297 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute4,
1298 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute5,
1299 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute6,
1300 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute7,
1301 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute8,
1302 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute9,
1303 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute10,
1304 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute11,
1305 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute12,
1306 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute13,
1307 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute14,
1308 l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute15,
1309 l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id,
1310 l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id,
1311 l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type,
1312 l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag
1313 );
1314
1315
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318
1319 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_ERROR;
1320 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INSERT_ASSOC');
1321 -- FND_MESSAGE.SET_ENCODED('Error inserting Activity Association record.');
1322 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg := FND_MESSAGE.GET;
1323
1324 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1325 l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1326 end if;
1327
1328 l_failed := TRUE;
1329 GOTO next_in_loop;
1330
1331 END;
1332
1333 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type = 2 THEN
1334 l_object_type := 40;
1335 ELSE
1336 l_object_type := 60;
1337 END IF;
1338
1339 eam_org_maint_defaults_pvt.insert_row
1340 (
1341 p_api_version => 1.0
1342 ,p_object_type => l_object_type
1343 ,p_object_id => l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id
1344 ,p_organization_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
1345 ,p_owning_department_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id
1346 ,p_accounting_class_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code
1347 ,p_area_id => null
1348 ,p_activity_cause_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code
1349 ,p_activity_type_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code
1350 ,p_activity_source_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code
1351 ,p_shutdown_type_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code
1352 ,p_tagging_required_flag => l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag
1353 ,x_return_status => l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status
1354 ,x_msg_count => x_msg_count
1355 ,x_msg_data => l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg
1356 );
1357
1358
1359 IF l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1360
1361 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1362 'Failed during insert in EOMD' ||
1363 'Return_Status=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status ||
1364 '; Error_Mesg=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
1365 end if;
1366
1367 l_failed := TRUE;
1368 GOTO next_in_loop;
1369 END IF;
1370
1371 -- ----------------------------------------------------------------------
1372 -- If reach here, successful
1373 l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status := FND_API.G_RET_STS_SUCCESS;
1374
1375
1376 <<next_in_loop>>
1377 l_act_assoc_tbl_index := l_act_assoc_tbl.NEXT(l_act_assoc_tbl_index);
1378 END LOOP;
1379
1380 -- Assign outputs
1381 x_activity_association_tbl := l_act_assoc_tbl;
1382 IF l_failed THEN
1383 l_x_return_status := FND_API.G_RET_STS_ERROR;
1384 ELSE
1385 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1386 END IF;
1387
1388 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1389 'l_x_return_status=' || l_x_return_status ||
1390 '========== Exiting EAM_ActivityAssociation_PVT.Create_AssetNumberAssoc ==========');
1391 end if;
1392
1393 x_return_status := l_x_return_status;
1394
1395 -- ============================================================
1396
1397 -- End of API body.
1398 -- Standard check of p_commit.
1399 IF FND_API.To_Boolean( p_commit ) THEN
1400 COMMIT WORK;
1401 END IF;
1402 -- Standard call to get message count and if count is 1, get message info.
1403 FND_MSG_PUB.Count_And_Get
1404 ( p_count => x_msg_count ,
1405 p_data => x_msg_data
1406 );
1407 x_msg_data := substr(x_msg_data,1,4000);
1408 EXCEPTION
1409 WHEN FND_API.G_EXC_ERROR THEN
1410 ROLLBACK TO Create_AssetNumberAssoc_PVT;
1411 x_return_status := FND_API.G_RET_STS_ERROR ;
1412 FND_MSG_PUB.Count_And_Get
1413 ( p_count => x_msg_count ,
1414 p_data => x_msg_data
1415 );
1416 x_msg_data := substr(x_msg_data,1,4000);
1417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1418 ROLLBACK TO Create_AssetNumberAssoc_PVT;
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1420 FND_MSG_PUB.Count_And_Get
1421 ( p_count => x_msg_count ,
1422 p_data => x_msg_data
1423 );
1424 x_msg_data := substr(x_msg_data,1,4000);
1425 WHEN OTHERS THEN
1426 ROLLBACK TO Create_AssetNumberAssoc_PVT;
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1428 IF FND_MSG_PUB.Check_Msg_Level
1429 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1430 THEN
1431 FND_MSG_PUB.Add_Exc_Msg
1432 ( G_PKG_NAME ,
1433 l_api_name
1434 );
1435 END IF;
1436 FND_MSG_PUB.Count_And_Get
1437 ( p_count => x_msg_count ,
1438 p_data => x_msg_data
1439 );
1440 x_msg_data := substr(x_msg_data,1,4000);
1441 END Create_AssetNumberAssoc;
1442
1443
1444 -- ======================================================================
1445
1446 PROCEDURE Inst_Activity_Template(
1447 p_api_version IN NUMBER ,
1448 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1449 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1450 p_validation_level IN NUMBER :=
1451 FND_API.G_VALID_LEVEL_FULL ,
1452 x_return_status OUT NOCOPY VARCHAR2 ,
1453 x_msg_count OUT NOCOPY NUMBER ,
1454 x_msg_data OUT NOCOPY VARCHAR2 ,
1455
1456 -- input: maintenance object (id and type)
1457 p_maintenance_object_id IN NUMBER,
1458 p_maintenance_object_type IN NUMBER, -- only supports type 3 (serial numbers) for now
1459 -- output for activity association
1460 x_activity_association_id_tbl OUT NOCOPY EAM_ObjectInstantiation_PUB.Association_Id_Tbl_Type
1461
1462 ,p_class_code IN VARCHAR2
1463 ,p_owning_department_id IN NUMBER
1464 )
1465 IS
1466
1467 l_api_name CONSTANT VARCHAR2(30) := 'Inst_Activity_Template';
1468 l_api_version CONSTANT NUMBER := 1.0;
1469
1470 l_current_date CONSTANT DATE := sysdate;
1471 l_date_insert DATE;
1472 l_next_association_id NUMBER;
1473 l_next_row BINARY_INTEGER;
1474 l_activity_association_id_tbl EAM_ObjectInstantiation_PUB.Association_Id_Tbl_Type;
1475
1476 l_inventory_item_id NUMBER;
1477 l_organization_id NUMBER;
1478
1479 l_class_code VARCHAR2(10);
1480 l_owning_department_id NUMBER;
1481
1482 --log variables
1483 l_module varchar2(200);
1484 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1485 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
1486 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1487 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1488 l_count number;
1489 CURSOR asset_activity_cur(
1490 p_maintenance_object_id IN NUMBER,
1491 p_organization_id IN NUMBER
1492 )
1493 IS
1494 SELECT meaa.Asset_Activity_Id, meaa.start_date_active, meaa.end_date_active,
1495 meaa.Priority_Code, meaa.Attribute_Category, meaa.Attribute1, meaa.Attribute2,
1496 meaa.Attribute3, meaa.Attribute4, meaa.Attribute5, meaa.Attribute6, meaa.Attribute7,
1497 meaa.Attribute8, meaa.Attribute9, meaa.Attribute10, meaa.Attribute11, meaa.Attribute12,
1498 meaa.Attribute13, meaa.Attribute14, meaa.Attribute15, meaa.Prev_Service_Start_Date,
1499 meaa.Prev_Service_End_Date, meaa.Last_Scheduled_Start_Date, meaa.Last_Scheduled_End_Date,
1500 meaa.Prev_Scheduled_Start_Date, meaa.Prev_Scheduled_End_Date,
1501 meaa.Activity_Association_Id, eomd.organization_id, eomd.accounting_class_code, eomd.owning_department_id,
1502 eomd.Activity_Cause_Code, eomd.Activity_Type_Code, eomd.Activity_Source_Code,
1503 eomd.Tagging_Required_Flag, eomd.Shutdown_Type_Code
1504 FROM mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
1505 WHERE maintenance_object_id = p_maintenance_object_id AND maintenance_object_type = 2
1506 AND tmpl_flag = 'Y' AND meaa.Activity_Association_Id = eomd.object_id(+) AND eomd.object_type(+) = 40
1507 AND eomd.organization_id(+) = p_organization_id;
1508 -- 2735563: Simply pick up ALL templates and copy the start and end dates to the association records
1509 /*
1510 AND ( (start_date_active IS NULL OR
1511 start_date_active <= l_current_date)
1512 AND (end_date_active IS NULL OR
1513 end_date_active > l_current_date))
1514 */
1515
1516 BEGIN
1517 if(l_ulog) then
1518 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1519 end if;
1520 l_date_insert := l_current_date;
1521
1522 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1523 '==================== Entered EAM_ActivityAssociation_PVT.Inst_Activity_Template ===================='
1524 || 'p_maintenance_object_id=' || p_maintenance_object_id
1525 || 'p_maintenance_object_type=' || p_maintenance_object_type);
1526 end if;
1527
1528 -- Standard Start of API savepoint
1529 SAVEPOINT Inst_Activity_Template_PVT;
1530
1531 -- Standard call to check for call compatibility.
1532 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1533 p_api_version ,
1534 l_api_name ,
1535 G_PKG_NAME )
1536 THEN
1537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1538 END IF;
1539
1540 -- Initialize message list if p_init_msg_list is set to TRUE.
1541 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1542 FND_MSG_PUB.initialize;
1543 END IF;
1544
1545 -- Initialize API return status to success
1546 x_return_status := FND_API.G_RET_STS_SUCCESS;
1547
1548
1549 -- API body
1550
1551
1552 -- maintenance object type should be 3
1553 IF p_maintenance_object_type <> 3 THEN
1554 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1555 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_MAINT_OBJ_TYPE');
1556 FND_MSG_PUB.ADD;
1557 END IF;
1558 RAISE FND_API.G_EXC_ERROR;
1559 END IF;
1560
1561 -- I: Find out the inventory_item_id
1562 BEGIN
1563 SELECT cii.inventory_item_id, mp.maint_organization_id
1564 INTO l_inventory_item_id, l_organization_id
1565 FROM csi_item_instances cii, mtl_parameters mp
1566 WHERE cii.instance_id = p_maintenance_object_id
1567 AND mp.organization_id = cii.last_vld_organization_id ;
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1571 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVLD_MT_GEN_OBJ_ID');
1572 FND_MSG_PUB.ADD;
1573 END IF;
1574 RAISE FND_API.G_EXC_ERROR;
1575 END;
1576
1577 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1578 'l_inventory_item_id=' || l_inventory_item_id);
1579 end if;
1580
1581 -- II: Pick out the templates defined for this item
1582 FOR l_asset_activity_row IN asset_activity_cur(l_inventory_item_id, l_organization_id)
1583 LOOP
1584 -- 1: Get activity_association_id from sequence
1585 BEGIN
1586 -- Get activity_association_id from sequence
1587 SELECT mtl_eam_asset_activities_s.nextval
1588 INTO l_next_association_id
1589 FROM dual;
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1593 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_ACT_ASSOC_ID');
1594 FND_MSG_PUB.ADD;
1595 END IF;
1596 RAISE FND_API.G_EXC_ERROR;
1597 END;
1598
1599 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1600 'l_next_association_id=' || l_next_association_id);
1601 end if;
1602
1603 --Bug 5137572
1604 Begin
1605 l_date_insert := l_current_date;
1606 SELECT COUNT(1) INTO l_count FROM eam_pm_schedulings eps, eam_pm_activities epa
1607 WHERE epa.activity_association_id = l_asset_activity_row.activity_association_id
1608 AND epa.pm_schedule_id = eps.pm_schedule_id
1609 AND nvl(eps.tmpl_flag, 'N') = 'Y' AND eps.auto_instantiation_flag = 'Y';
1610 if ( l_count = 0 ) then
1611 l_date_insert := null;
1612 end if;
1613 Exception
1614 When others then
1615 l_date_insert := null;
1616 End;
1617
1618 -- BUG 3683229: Default Owning Dept and Class Code from asset number
1619 -- if present, else default from template
1620 l_class_code := nvl(p_class_code,l_asset_activity_row.accounting_class_code);
1621 l_owning_department_id := nvl(p_owning_department_id,l_asset_activity_row.Owning_Department_Id);
1622
1623 -- 2: Insert row into mtl_eam_asset_activities
1624 BEGIN
1625 -- Insert into Database Table mtl_eam_asset_activities
1626 INSERT INTO mtl_eam_asset_activities (
1627 Asset_Activity_Id,
1628 Start_Date_Active,
1629 End_Date_Active,
1630 Priority_Code,
1631 Last_Update_Date,
1632 Last_Updated_By,
1633 Creation_Date,
1634 Created_By,
1635 Last_Update_Login,
1636 Attribute_Category,
1637 Attribute1,
1638 Attribute2,
1639 Attribute3,
1640 Attribute4,
1641 Attribute5,
1642 Attribute6,
1643 Attribute7,
1644 Attribute8,
1645 Attribute9,
1646 Attribute10,
1647 Attribute11,
1648 Attribute12,
1649 Attribute13,
1650 Attribute14,
1651 Attribute15,
1652 Activity_Association_Id,
1653 Last_Service_Start_Date,
1654 Last_Service_End_Date,
1655 Prev_Service_Start_Date,
1656 Prev_Service_End_Date,
1657 Last_Scheduled_Start_Date,
1658 Last_Scheduled_End_Date,
1659 Prev_Scheduled_Start_Date,
1660 Prev_Scheduled_End_Date,
1661 Maintenance_Object_Id,
1662 Maintenance_Object_type,
1663 Tmpl_Flag,
1664 Source_Tmpl_Id
1665 ) VALUES (
1666 l_asset_activity_row.Asset_Activity_Id,
1667 -- 2735563: Simply pick up ALL templates and copy the start and end dates to the association records
1668 l_asset_activity_row.start_date_active,
1669 l_asset_activity_row.end_date_active,
1670 l_asset_activity_row.Priority_Code,
1671 l_current_date,
1672 FND_GLOBAL.USER_ID,
1673 l_current_date,
1674 FND_GLOBAL.USER_ID,
1675 FND_GLOBAL.LOGIN_ID,
1676 l_asset_activity_row.Attribute_Category,
1677 l_asset_activity_row.Attribute1,
1678 l_asset_activity_row.Attribute2,
1679 l_asset_activity_row.Attribute3,
1680 l_asset_activity_row.Attribute4,
1681 l_asset_activity_row.Attribute5,
1682 l_asset_activity_row.Attribute6,
1683 l_asset_activity_row.Attribute7,
1684 l_asset_activity_row.Attribute8,
1685 l_asset_activity_row.Attribute9,
1686 l_asset_activity_row.Attribute10,
1687 l_asset_activity_row.Attribute11,
1688 l_asset_activity_row.Attribute12,
1689 l_asset_activity_row.Attribute13,
1690 l_asset_activity_row.Attribute14,
1691 l_asset_activity_row.Attribute15,
1692 l_next_association_id,
1693 l_date_insert,
1694 l_date_insert,
1695 l_asset_activity_row.Prev_Service_Start_Date,
1696 l_asset_activity_row.Prev_Service_End_Date,
1697 l_date_insert,
1698 l_date_insert,
1699 l_asset_activity_row.Prev_Scheduled_Start_Date,
1700 l_asset_activity_row.Prev_Scheduled_End_Date,
1701 p_maintenance_object_id,
1702 p_maintenance_object_type,
1703 'N',
1704 l_asset_activity_row.Activity_Association_Id
1705 );
1706 EXCEPTION
1707 WHEN OTHERS THEN
1708 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1709 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INSERT_ASSOC');
1710 FND_MSG_PUB.ADD;
1711 END IF;
1712 RAISE FND_API.G_EXC_ERROR;
1713 END;
1714
1715 IF l_asset_activity_row.organization_id IS NOT NULL THEN
1716
1717 eam_org_maint_defaults_pvt.insert_row
1718 (
1719 p_api_version => 1.0
1720 ,p_object_type => 60
1721 ,p_object_id => l_next_association_id
1722 ,p_organization_id => l_asset_activity_row.Organization_Id
1723 ,p_owning_department_id => l_owning_department_id
1724 ,p_accounting_class_code => l_class_code
1725 ,p_area_id => null
1726 ,p_activity_cause_code => l_asset_activity_row.Activity_Cause_Code
1727 ,p_activity_type_code => l_asset_activity_row.Activity_Type_Code
1728 ,p_activity_source_code => l_asset_activity_row.Activity_Source_Code
1729 ,p_shutdown_type_code => l_asset_activity_row.Shutdown_Type_Code
1730 ,p_tagging_required_flag => l_asset_activity_row.Tagging_Required_Flag
1731 ,x_return_status => x_return_status
1732 ,x_msg_count => x_msg_count
1733 ,x_msg_data => x_msg_data
1734 );
1735 END IF;
1736
1737 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1738 RAISE FND_API.G_EXC_ERROR;
1739 END IF;
1740
1741 -- 3: Record outputs
1742 l_next_row := NVL(l_activity_association_id_tbl.LAST, 0) + 1;
1743 l_activity_association_id_tbl(l_next_row) := l_next_association_id;
1744
1745 END LOOP;
1746
1747 -- 4: Assign outputs
1748 x_activity_association_id_tbl := l_activity_association_id_tbl;
1749
1750
1751 -- End of API body.
1752
1753
1754 -- Standard check of p_commit.
1755 IF FND_API.To_Boolean( p_commit ) THEN
1756 COMMIT WORK;
1757 END IF;
1758 -- Standard call to get message count and if count is 1, get message info.
1759 FND_MSG_PUB.Count_And_Get
1760 ( p_count => x_msg_count ,
1761 p_data => x_msg_data
1762 );
1763
1764 x_msg_data := substr(x_msg_data,1,4000);
1765
1766 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1767 '==================== Exiting EAM_ActivityAssociation_PVT.Inst_Activity_Template ====================');
1768 end if;
1769
1770
1771
1772 EXCEPTION
1773 WHEN FND_API.G_EXC_ERROR THEN
1774 ROLLBACK TO Inst_Activity_Template_PVT;
1775 x_return_status := FND_API.G_RET_STS_ERROR ;
1776 FND_MSG_PUB.Count_And_Get
1777 ( p_count => x_msg_count ,
1778 p_data => x_msg_data
1779 );
1780 x_msg_data := substr(x_msg_data,1,4000);
1781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1782 ROLLBACK TO Inst_Activity_Template_PVT;
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1784 FND_MSG_PUB.Count_And_Get
1785 ( p_count => x_msg_count ,
1786 p_data => x_msg_data
1787 );
1788 x_msg_data := substr(x_msg_data,1,4000);
1789 WHEN OTHERS THEN
1790 ROLLBACK TO Inst_Activity_Template_PVT;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1792 IF FND_MSG_PUB.Check_Msg_Level
1793 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1794 THEN
1795 FND_MSG_PUB.Add_Exc_Msg
1796 ( G_PKG_NAME ,
1797 l_api_name
1798 );
1799 END IF;
1800 FND_MSG_PUB.Count_And_Get
1801 ( p_count => x_msg_count ,
1802 p_data => x_msg_data
1803 );
1804
1805 x_msg_data := substr(x_msg_data,1,4000);
1806 END;
1807
1808
1809 END EAM_ActivityAssociation_PVT;