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