[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_EAM_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_EAM_ODS_LOAD AS
2 /* $Header: MSCLEAMB.pls 120.16.12020000.3 2013/02/06 10:05:31 swundapa ship $*/
3 v_sql_stmt VARCHAR2(32767);
4
5 PROCEDURE LOAD_EAM_INFO IS
6
7 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
8 c4 CurTyp;
9
10 CURSOR c_del IS
11 SELECT mseaa.ASSET_ACTIVITY_ID,
12 mseaa.ORGANIZATION_ID,
13 mseaa.ASSET_REBUILD_ITEM_ID,
14 mseaa.SR_INSTANCE_ID
15 FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa
16 WHERE mseaa.deleted_flag = MSC_UTIL.SYS_YES
17 AND mseaa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
18
19 CURSOR c_del2 IS
20 SELECT mseaa.ASSET_GROUP_ITEM_ID,
21 mseaa.ORGANIZATION_ID,
22 mseaa.ASSET_ACTIVITY_ID,
23 mseaa.EQUIPMENT_ITEM_ID,
24 mseaa.SR_INSTANCE_ID
25 FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mseaa
26 WHERE mseaa.deleted_flag = MSC_UTIL.SYS_YES
27 AND mseaa.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
28 lv_errbuf VARCHAR2(240);
29 lv_retcode NUMBER;
30 lv_tbl VARCHAR2(30);
31 lv_sql_stmt VARCHAR2(5000);
32 total_count NUMBER;
33 c_count NUMBER:=0;
34 lv_sql_ins VARCHAR2(6000);
35 lv_cursor_stmt VARCHAR2(5000);
36
37 lv_ASSET_ACTIVITY_ID NUMBER;
38 lv_ORGANIZATION_ID NUMBER;
39 lv_ACTIVITY VARCHAR2(30);
40 lv_ASSET_REBUILD_ITEM_ID NUMBER;
41 lv_ACTIVITY_TYPE NUMBER;
42 lv_ASSET_REBUILD_GROUP VARCHAR2(30);
43 lv_EAM_ITEM_TYPE NUMBER;
44 lv_SR_INSTANCE_ID NUMBER;
45
46 lv_ASSET_GROUP_ITEM_ID NUMBER;
47 lv_ASSET_NUMBER_ID NUMBER;
48 lv_ASSET_NUMBER VARCHAR2(30);
49 lv_EQUIPMENT_ITEM_ID NUMBER;
50 lv_EQUIPMENT_SERIAL_NUM VARCHAR2(30);
51
52 lv_RESOURCE_ID NUMBER;
53 lv_DEPARTMENT_ID NUMBER;
54 lv_RESOURCE_CODE VARCHAR2(10);
55 lv_SCHEDULE_TO_INSTANCE NUMBER;
56 BEGIN
57 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_EAM_INFO ');
58
59 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
60 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
61 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
62 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
63 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ACT_ASSOCIATIONS',
64 MSC_CL_COLLECTION.v_instance_id,
65 -1);
66 ELSE
67 MSC_CL_COLLECTION.v_sub_str :=
68 ' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
69 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ACT_ASSOCIATIONS',
70 MSC_CL_COLLECTION.v_instance_id,
71 null,
72 MSC_CL_COLLECTION.v_sub_str);
73 END IF;
74 END IF;
75
76 -- ========= Prepare the Cursor Statement ==========
77 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
78 lv_tbl:= 'EAM_ACT_ASSOCIATIONS_'||MSC_CL_COLLECTION.v_instance_code;
79 ELSE
80 lv_tbl:= 'MSC_EAM_ACT_ASSOCIATIONS';
81 END IF;
82
83 lv_cursor_stmt := 'SELECT'
84 ||' m1.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
85 ||' mseaa.ORGANIZATION_ID,'
86 ||' mseaa.ACTIVITY,'
87 ||' m2.INVENTORY_ITEM_ID ASSET_REBUILD_ITEM_ID,'
88 ||' mseaa.ACTIVITY_TYPE,'
89 ||' mseaa.ASSET_REBUILD_GROUP,'
90 ||' mseaa.EAM_ITEM_TYPE,'
91 ||' mseaa.SR_INSTANCE_ID'
92 ||' FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa,'
93 ||' MSC_ITEM_ID_LID m1,'
94 ||' MSC_ITEM_ID_LID m2'
95 ||' WHERE mseaa.SR_INSTANCE_ID= '
96 ||MSC_CL_COLLECTION.v_instance_id
97 --||' AND mseaa.PLAN_ID= -1'
98 ||' AND mseaa.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
99 ||' AND m1.SR_INVENTORY_ITEM_ID= mseaa.ASSET_ACTIVITY_ID'
100 ||' AND mseaa.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
101 ||' AND m2.SR_INVENTORY_ITEM_ID= mseaa.ASSET_REBUILD_ITEM_ID'
102 ||' AND mseaa.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
103
104 -- ========= Prepare SQL Statement for INSERT ==========
105 lv_sql_stmt:= 'insert into '||lv_tbl
106 ||' ( ASSET_ACTIVITY_ID,'
107 ||' ORGANIZATION_ID,'
108 ||' ACTIVITY,'
109 ||' ASSET_REBUILD_ITEM_ID,'
110 ||' ACTIVITY_TYPE,'
111 ||' ASSET_REBUILD_GROUP,'
112 ||' EAM_ITEM_TYPE,'
113 ||' SR_INSTANCE_ID,'
114 ||' REFRESH_NUMBER,'
115 ||' LAST_UPDATE_DATE,'
116 ||' LAST_UPDATED_BY,'
117 ||' CREATION_DATE,'
118 ||' CREATED_BY)'
119 ||' VALUES'
120 ||'( :ASSET_ACTIVITY_ID,'
121 ||' :ORGANIZATION_ID,'
122 ||' :ACTIVITY,'
123 ||' :ASSET_REBUILD_ITEM_ID,'
124 ||' :ACTIVITY_TYPE,'
125 ||' :ASSET_REBUILD_GROUP,'
126 ||' :EAM_ITEM_TYPE,'
127 ||' :SR_INSTANCE_ID,'
128 ||' :v_last_collection_id,'
129 ||' :v_current_date,'
130 ||' :v_current_user,'
131 ||' :v_current_date,'
132 ||' :v_current_user)';
133
134 IF (MSC_CL_COLLECTION.v_is_complete_refresh
135 OR MSC_CL_COLLECTION.v_is_partial_refresh)
136 AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
137
138 BEGIN
139 lv_sql_ins:= 'insert into '||lv_tbl
140 ||' ( ASSET_ACTIVITY_ID,'
141 ||' ORGANIZATION_ID,'
142 ||' ACTIVITY,'
143 ||' ASSET_REBUILD_ITEM_ID,'
144 ||' ACTIVITY_TYPE,'
145 ||' ASSET_REBUILD_GROUP,'
146 ||' EAM_ITEM_TYPE,'
147 ||' SR_INSTANCE_ID,'
148 ||' REFRESH_NUMBER,'
149 ||' LAST_UPDATE_DATE,'
150 ||' LAST_UPDATED_BY,'
151 ||' CREATION_DATE,'
152 ||' CREATED_BY)'
153 ||' SELECT '
154 ||' m1.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
155 ||' mseaa.ORGANIZATION_ID,'
156 ||' mseaa.ACTIVITY,'
157 ||' m2.INVENTORY_ITEM_ID ASSET_REBUILD_ITEM_ID,'
158 ||' mseaa.ACTIVITY_TYPE,'
159 ||' mseaa.ASSET_REBUILD_GROUP,'
160 ||' mseaa.EAM_ITEM_TYPE,'
161 ||' mseaa.SR_INSTANCE_ID,'
162 ||' :v_last_collection_id,'
163 ||' :v_current_date, '
164 ||' :v_current_user, '
165 ||' :v_current_date, '
166 ||' :v_current_user '
167 ||' FROM MSC_ST_EAM_ACT_ASSOCIATIONS mseaa,'
168 ||' MSC_ITEM_ID_LID m1,'
169 ||' MSC_ITEM_ID_LID m2'
170 ||' WHERE mseaa.SR_INSTANCE_ID= '
171 ||MSC_CL_COLLECTION.v_instance_id
172 --||' AND mseaa.PLAN_ID= -1'
173 ||' AND mseaa.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
174 ||' AND m1.SR_INVENTORY_ITEM_ID= mseaa.ASSET_ACTIVITY_ID'
175 ||' AND mseaa.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
176 ||' AND m2.SR_INVENTORY_ITEM_ID= '
177 ||' mseaa.ASSET_REBUILD_ITEM_ID'
178 ||' AND mseaa.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
179
180
181 EXECUTE IMMEDIATE lv_sql_ins
182 USING MSC_CL_COLLECTION.v_last_collection_id,
183 MSC_CL_COLLECTION.v_current_date,
184 MSC_CL_COLLECTION.v_current_user,
185 MSC_CL_COLLECTION.v_current_date,
186 MSC_CL_COLLECTION.v_current_user;
187 COMMIT;
188 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
189 'Eam activity associations loaded');
190 EXCEPTION
191 WHEN OTHERS THEN
192 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
193 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
194 '========================================');
195 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
196 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
197 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
198 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
200 RAISE;
201 ELSE
202 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
203 '========================================');
204 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
205 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
206 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
207 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
208 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
209 END IF;
210 END;
211
212 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
213 -- OR MSC_CL_COLLECTION.v_is_partial_refresh
214
215 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
216
217 FOR c_rec IN c_del LOOP
218 DELETE MSC_EAM_ACT_ASSOCIATIONS
219 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
220 AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
221 AND ORGANIZATION_ID = c_rec.organization_id
222 AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
223 END LOOP;
224
225 -- END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
226
227 OPEN c4 FOR lv_cursor_stmt;
228 LOOP
229 FETCH c4 INTO lv_ASSET_ACTIVITY_ID,
230 lv_ORGANIZATION_ID,
231 lv_ACTIVITY,
232 lv_ASSET_REBUILD_ITEM_ID,
233 lv_ACTIVITY_TYPE,
234 lv_ASSET_REBUILD_GROUP,
235 lv_EAM_ITEM_TYPE,
236 lv_SR_INSTANCE_ID;
237
238 EXIT WHEN c4%NOTFOUND;
239
240 BEGIN
241 UPDATE MSC_EAM_ACT_ASSOCIATIONS
242 SET ACTIVITY = lv_ACTIVITY,
243 ACTIVITY_TYPE =lv_ACTIVITY_TYPE,
244 ASSET_REBUILD_GROUP = lv_ASSET_REBUILD_GROUP,
245 EAM_ITEM_TYPE = lv_EAM_ITEM_TYPE,
246 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
247 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
248 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
249 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
250 AND ASSET_ACTIVITY_ID = lv_asset_activity_id
251 AND ORGANIZATION_ID = lv_organization_id
252 AND ASSET_REBUILD_ITEM_ID = lv_asset_rebuild_item_id;
253 --AND DELETED_FLAG = MSC_UTIL.SYS_NO;
254
255
256 IF (MSC_CL_COLLECTION.v_is_complete_refresh
257 OR MSC_CL_COLLECTION.v_is_partial_refresh)
258 OR SQL%NOTFOUND THEN
259
260 EXECUTE IMMEDIATE lv_sql_stmt
261 USING lv_ASSET_ACTIVITY_ID,
262 lv_ORGANIZATION_ID,
263 lv_ACTIVITY,
264 lv_ASSET_REBUILD_ITEM_ID,
265 lv_ACTIVITY_TYPE,
266 lv_ASSET_REBUILD_GROUP,
267 lv_EAM_ITEM_TYPE,
268 lv_SR_INSTANCE_ID,
269 MSC_CL_COLLECTION.v_last_collection_id,
270 MSC_CL_COLLECTION.v_current_date,
271 MSC_CL_COLLECTION.v_current_user,
272 MSC_CL_COLLECTION.v_current_date,
273 MSC_CL_COLLECTION.v_current_user;
274
275 END IF;
276
277 c_count:= c_count+1;
278
279 IF c_count> MSC_CL_COLLECTION.PBS THEN
280 IF (MSC_CL_COLLECTION.v_is_complete_refresh
281 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
282 COMMIT;
283 END IF;
284 c_count:= 0;
285 END IF;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
290 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
291 '========================================');
292 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
293 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
294 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
296 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
297 RAISE;
298
299 ELSE
300 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
301
305 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
302 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
303 '========================================');
304 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
306 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
307 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
308
309 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
310 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
311 FND_MESSAGE.SET_TOKEN('VALUE',
312 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
313 MSC_CL_COLLECTION.v_instance_id));
314 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
315
316 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
317 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSET_ACTIVITY_ID');
318 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_ASSET_ACTIVITY_ID));
319 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
320
321 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
322 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSET_REBUILD_ITEM_ID');
323 FND_MESSAGE.SET_TOKEN('VALUE',
324 TO_CHAR(lv_ASSET_REBUILD_ITEM_ID));
325 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
326
327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
328 END IF;
329 END;
330 END LOOP;
331 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
332
333 IF (MSC_CL_COLLECTION.v_is_complete_refresh
334 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
335 COMMIT;
336 END IF;
337
338 BEGIN
339 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <>
340 MSC_UTIL.G_ALL_ORGANIZATIONS )
341 AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
342
343 lv_tbl:= 'EAM_ACT_ASSOCIATIONS_'||MSC_CL_COLLECTION.v_instance_code;
344 lv_sql_stmt:= 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
345 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_EAM_ACT_ASSOCIATIONS'
346 ||' WHERE sr_instance_id = '
347 ||MSC_CL_COLLECTION.v_instance_id
348 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
349
350 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '
351 ||lv_sql_stmt);
352 EXECUTE IMMEDIATE lv_sql_stmt;
353 COMMIT;
354 END IF;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
359 RAISE;
360 END;
361
362 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
363 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
364 lv_retcode,
365 'MSC_EAM_ACT_ASSOCIATIONS',
366 MSC_CL_COLLECTION.v_instance_code,
367 MSC_UTIL.G_WARNING
368 );
369
370 IF lv_retcode = MSC_UTIL.G_ERROR THEN
371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
372 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
373 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
374 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
375 END IF;
376 END IF;
377
378
379 /*--------- PS requirements - New table to collect asset number details ---------*/
380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_EAM_INFO -- EAM asset Equip');
381
382 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR
383 MSC_CL_COLLECTION.v_is_partial_refresh) THEN
384 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag =
385 MSC_UTIL.G_ALL_ORGANIZATIONS THEN
386 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ASSET_EQUIP_DTLS',
387 MSC_CL_COLLECTION.v_instance_id,
388 -1);
389 ELSE
390 MSC_CL_COLLECTION.v_sub_str :=
391 ' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
392 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_EAM_ASSET_EQUIP_DTLS',
393 MSC_CL_COLLECTION.v_instance_id,
394 null,
395 MSC_CL_COLLECTION.v_sub_str);
396 END IF;
397 END IF;
398
399 -- ========= Prepare the Cursor Statement ==========
400 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
401 lv_tbl:= 'EAM_ASSET_EQUIP_DTLS_'||MSC_CL_COLLECTION.v_instance_code;
402 ELSE
403 lv_tbl:= 'MSC_EAM_ASSET_EQUIP_DTLS';
404 END IF;
405
406 lv_cursor_stmt := 'SELECT'
407 ||' m1.INVENTORY_ITEM_ID ASSET_GROUP_ITEM_ID,'
408 ||' mse.ORGANIZATION_ID,'
409 ||' m2.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
410 ||' mse.ASSET_NUMBER_ID,'
411 ||' mse.ASSET_NUMBER,'
412 ||' m3.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID,'
413 ||' mse.EQUIPMENT_SERIAL_NUM,'
414 ||' mse.RESOURCE_ID,'
415 ||' mse.DEPARTMENT_ID,'
416 ||' mse.RESOURCE_CODE,'
417 ||' mse.SCHEDULE_TO_INSTANCE,'
418 ||' mse.SR_INSTANCE_ID'
419 ||' FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mse,'
420 ||' MSC_ITEM_ID_LID m1,'
421 ||' MSC_ITEM_ID_LID m2,'
422 ||' MSC_ITEM_ID_LID m3'
423 ||' WHERE mse.SR_INSTANCE_ID= '
424 ||MSC_CL_COLLECTION.v_instance_id
425 ||' AND mse.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
426 ||' AND m1.SR_INVENTORY_ITEM_ID= mse.ASSET_GROUP_ITEM_ID'
427 ||' AND mse.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
428 ||' AND m2.SR_INVENTORY_ITEM_ID= mse.ASSET_ACTIVITY_ID'
429 ||' AND mse.SR_INSTANCE_ID= m3.SR_INSTANCE_ID'
430 ||' AND m3.SR_INVENTORY_ITEM_ID= mse.EQUIPMENT_ITEM_ID'
431 ||' AND mse.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
432
433 -- ========= Prepare SQL Statement for INSERT ==========
434 lv_sql_stmt:= 'insert into '||lv_tbl
435 ||'(ASSET_GROUP_ITEM_ID,'
436 ||' ORGANIZATION_ID,'
437 ||' ASSET_ACTIVITY_ID,'
438 ||' ASSET_NUMBER_ID,'
439 ||' ASSET_NUMBER,'
440 ||' EQUIPMENT_ITEM_ID,'
441 ||' EQUIPMENT_SERIAL_NUM,'
442 ||' RESOURCE_ID,'
443 ||' DEPARTMENT_ID,'
444 ||' RESOURCE_CODE,'
445 ||' SCHEDULE_TO_INSTANCE,'
446 ||' SR_INSTANCE_ID,'
447 ||' REFRESH_NUMBER,'
448 ||' LAST_UPDATE_DATE,'
449 ||' LAST_UPDATED_BY,'
450 ||' CREATION_DATE,'
451 ||' CREATED_BY)'
452 ||' VALUES'
453 ||'( :ASSET_GROUP_ITEM_ID,'
454 ||' :ORGANIZATION_ID,'
455 ||' :ASSET_ACTIVITY_ID,'
456 ||' :ASSET_NUMBER_ID,'
457 ||' :ASSET_NUMBER,'
458 ||' :EQUIPMENT_ITEM_ID,'
459 ||' :EQUIPMENT_SERIAL_NUM,'
460 ||' :RESOURCE_ID,'
461 ||' :DEPARTMENT_ID,'
462 ||' :RESOURCE_CODE,'
463 ||' :SCHEDULE_TO_INSTANCE,'
464 ||' :SR_INSTANCE_ID,'
465 ||' :v_last_collection_id,'
466 ||' :v_current_date,'
467 ||' :v_current_user,'
468 ||' :v_current_date,'
469 ||' :v_current_user)';
470
471 IF (MSC_CL_COLLECTION.v_is_complete_refresh
472 OR MSC_CL_COLLECTION.v_is_partial_refresh)
473 AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
474
475 BEGIN
476 lv_sql_ins:= 'insert into '||lv_tbl
477 ||'(ASSET_GROUP_ITEM_ID,'
478 ||' ORGANIZATION_ID,'
479 ||' ASSET_ACTIVITY_ID,'
480 ||' ASSET_NUMBER_ID,'
481 ||' ASSET_NUMBER,'
482 ||' EQUIPMENT_ITEM_ID,'
483 ||' EQUIPMENT_SERIAL_NUM,'
484 ||' RESOURCE_ID,'
485 ||' DEPARTMENT_ID,'
486 ||' RESOURCE_CODE,'
487 ||' SCHEDULE_TO_INSTANCE,'
488 ||' SR_INSTANCE_ID,'
489 ||' REFRESH_NUMBER,'
490 ||' LAST_UPDATE_DATE,'
491 ||' LAST_UPDATED_BY,'
492 ||' CREATION_DATE,'
493 ||' CREATED_BY)'
494 ||'SELECT'
495 ||' m1.INVENTORY_ITEM_ID ASSET_GROUP_ITEM_ID,'
496 ||' mse.ORGANIZATION_ID,'
497 ||' m2.INVENTORY_ITEM_ID ASSET_ACTIVITY_ID,'
498 ||' mse.ASSET_NUMBER_ID,'
499 ||' mse.ASSET_NUMBER,'
500 ||' m3.INVENTORY_ITEM_ID EQUIPMENT_ITEM_ID,'
501 ||' mse.EQUIPMENT_SERIAL_NUM,'
502 ||' mse.RESOURCE_ID,'
503 ||' mse.DEPARTMENT_ID,'
504 ||' mse.RESOURCE_CODE,'
505 ||' mse.SCHEDULE_TO_INSTANCE,'
506 ||' mse.SR_INSTANCE_ID,'
507 ||' :v_last_collection_id,'
508 ||' :v_current_date, '
509 ||' :v_current_user, '
510 ||' :v_current_date, '
511 ||' :v_current_user '
512 ||' FROM MSC_ST_EAM_ASSET_EQUIP_DTLS mse,'
513 ||' MSC_ITEM_ID_LID m1,'
514 ||' MSC_ITEM_ID_LID m2,'
515 ||' MSC_ITEM_ID_LID m3'
516 ||' WHERE mse.SR_INSTANCE_ID= '
517 ||MSC_CL_COLLECTION.v_instance_id
518 ||' AND mse.SR_INSTANCE_ID= m1.SR_INSTANCE_ID'
519 ||' AND m1.SR_INVENTORY_ITEM_ID= mse.ASSET_GROUP_ITEM_ID'
520 ||' AND mse.SR_INSTANCE_ID= m2.SR_INSTANCE_ID'
521 ||' AND m2.SR_INVENTORY_ITEM_ID= mse.ASSET_ACTIVITY_ID'
522 ||' AND mse.SR_INSTANCE_ID= m3.SR_INSTANCE_ID'
523 ||' AND m3.SR_INVENTORY_ITEM_ID= mse.EQUIPMENT_ITEM_ID'
524 ||' AND mse.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
525
526
527 EXECUTE IMMEDIATE lv_sql_ins
528 USING MSC_CL_COLLECTION.v_last_collection_id,
529 MSC_CL_COLLECTION.v_current_date,
530 MSC_CL_COLLECTION.v_current_user,
531 MSC_CL_COLLECTION.v_current_date,
532 MSC_CL_COLLECTION.v_current_user;
533 COMMIT;
534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
535 'Eam asset equipment details loaded');
536 EXCEPTION
537 WHEN OTHERS THEN
538 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
542 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
539 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
540 '========================================');
541 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
543 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ASSET_EQUIP_DTLS');
544 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
545 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
546 RAISE;
547 ELSE
548 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
549 '========================================');
550 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
551 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
552 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ASSET_EQUIP_DTLS');
553 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
554 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
555 END IF;
556 END;
557
558 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
559 -- OR MSC_CL_COLLECTION.v_is_partial_refresh
560
561 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
562
563 FOR c_rec IN c_del2 LOOP
564 DELETE MSC_EAM_ASSET_EQUIP_DTLS
565 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
566 AND ASSET_GROUP_ITEM_ID = c_rec.ASSET_GROUP_ITEM_ID
567 AND ORGANIZATION_ID = c_rec.organization_id
568 AND ASSET_ACTIVITY_ID = c_rec.ASSET_ACTIVITY_ID
569 AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID;
570 END LOOP;
571
572 -- END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
573
574 OPEN c4 FOR lv_cursor_stmt;
575 LOOP
576 FETCH c4 INTO lv_ASSET_GROUP_ITEM_ID,
577 lv_ORGANIZATION_ID,
578 lv_ASSET_ACTIVITY_ID,
579 lv_ASSET_NUMBER_ID,
580 lv_ASSET_NUMBER,
581 lv_EQUIPMENT_ITEM_ID,
582 lv_EQUIPMENT_SERIAL_NUM,
583 lv_RESOURCE_ID,
584 lv_DEPARTMENT_ID,
585 lv_RESOURCE_CODE,
586 lv_SCHEDULE_TO_INSTANCE,
587 lv_SR_INSTANCE_ID;
588
589 EXIT WHEN c4%NOTFOUND;
590
591 BEGIN
592 UPDATE MSC_EAM_ASSET_EQUIP_DTLS
593 SET ASSET_NUMBER_ID = lv_ASSET_NUMBER_ID,
594 ASSET_NUMBER =lv_ASSET_NUMBER,
595 EQUIPMENT_SERIAL_NUM = lv_EQUIPMENT_SERIAL_NUM,
596 RESOURCE_ID = lv_RESOURCE_ID,
597 DEPARTMENT_ID = lv_DEPARTMENT_ID,
598 RESOURCE_CODE = lv_RESOURCE_CODE,
599 SCHEDULE_TO_INSTANCE = lv_SCHEDULE_TO_INSTANCE,
600 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
601 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
602 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
603 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
604 AND ASSET_GROUP_ITEM_ID = lv_ASSET_GROUP_ITEM_ID
605 AND ASSET_ACTIVITY_ID = lv_asset_activity_id
606 AND ORGANIZATION_ID = lv_organization_id
607 AND EQUIPMENT_ITEM_ID = lv_EQUIPMENT_ITEM_ID;
608
609
610 IF (MSC_CL_COLLECTION.v_is_complete_refresh
611 OR MSC_CL_COLLECTION.v_is_partial_refresh)
612 OR SQL%NOTFOUND THEN
613
614 EXECUTE IMMEDIATE lv_sql_stmt
615 USING lv_ASSET_GROUP_ITEM_ID,
616 lv_ORGANIZATION_ID,
617 lv_ASSET_ACTIVITY_ID,
618 lv_ASSET_NUMBER_ID,
619 lv_ASSET_NUMBER,
620 lv_EQUIPMENT_ITEM_ID,
621 lv_EQUIPMENT_SERIAL_NUM,
622 lv_RESOURCE_ID,
623 lv_DEPARTMENT_ID,
624 lv_RESOURCE_CODE,
625 lv_SCHEDULE_TO_INSTANCE,
626 lv_SR_INSTANCE_ID,
627 MSC_CL_COLLECTION.v_last_collection_id,
628 MSC_CL_COLLECTION.v_current_date,
629 MSC_CL_COLLECTION.v_current_user,
630 MSC_CL_COLLECTION.v_current_date,
631 MSC_CL_COLLECTION.v_current_user;
632
633 END IF;
634
635 c_count:= c_count+1;
636
637 IF c_count> MSC_CL_COLLECTION.PBS THEN
638 IF (MSC_CL_COLLECTION.v_is_complete_refresh
639 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
640 COMMIT;
641 END IF;
642 c_count:= 0;
643 END IF;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
648 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
649 '========================================');
650 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
651 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
652 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ASSET_EQUIP_DTLS');
653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
654 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
655 RAISE;
656
657 ELSE
658 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
659
660 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
661 '========================================');
662 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
666
663 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
664 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ASSET_EQUIP_DTLS');
665 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
667 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
668 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
669 FND_MESSAGE.SET_TOKEN('VALUE',
670 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
671 MSC_CL_COLLECTION.v_instance_id));
672 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
673
674 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
675 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSET_GROUP_ITEM_ID');
676 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_ASSET_ACTIVITY_ID));
677 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
678
679 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
680 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSET_ACTIVITY_ID');
681 FND_MESSAGE.SET_TOKEN('VALUE',
682 TO_CHAR(lv_ASSET_REBUILD_ITEM_ID));
683 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
684
685 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
686 END IF;
687 END;
688 END LOOP;
689 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
690
691 IF (MSC_CL_COLLECTION.v_is_complete_refresh
692 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
693 COMMIT;
694 END IF;
695
696 BEGIN
697 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <>
698 MSC_UTIL.G_ALL_ORGANIZATIONS )
699 AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
700
701 lv_tbl:= 'EAM_ASSET_EQUIP_DTLS_'||MSC_CL_COLLECTION.v_instance_code;
702 lv_sql_stmt:= 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
703 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_EAM_ASSET_EQUIP_DTLS'
704 ||' WHERE sr_instance_id = '
705 ||MSC_CL_COLLECTION.v_instance_id
706 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
707
708 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '
709 ||lv_sql_stmt);
710 EXECUTE IMMEDIATE lv_sql_stmt;
711 COMMIT;
712 END IF;
713
714 EXCEPTION
715 WHEN OTHERS THEN
716 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
717 RAISE;
718 END;
719
720 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
721 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
722 lv_retcode,
723 'MSC_EAM_ASSET_EQUIP_DTLS',
724 MSC_CL_COLLECTION.v_instance_code,
725 MSC_UTIL.G_WARNING
726 );
727
728 IF lv_retcode = MSC_UTIL.G_ERROR THEN
729 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
730 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
731 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
732 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
733 END IF;
734 END IF;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 IF c4%ISOPEN THEN
739 CLOSE c4;
740 END IF;
741 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_eam_info>>');
742 IF lv_cursor_stmt IS NOT NULL THEN
743 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
744 END IF;
745 IF lv_sql_stmt IS NOT NULL THEN
746 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
747 END IF;
748 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
749 RAISE;
750 COMMIT;
751 END LOAD_EAM_INFO;
752
753 /* FOR c_rec IN c_del LOOP
754 BEGIN
755
756 DELETE MSC_EAM_ACT_ASSOCIATIONS
757 WHERE SR_INSTANCE_ID= c_rec.sr_instance_id
758 AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
759 AND ORGANIZATION_ID = c_rec.organization_id
760 AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
761
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
766 'An error has occurred during deletion of Eam info.');
767 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
768 RAISE;
769 END;
770 END LOOP;
771
772 COMMIT;
773
774 BEGIN
775 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
776 lv_tbl:= 'EAM_ACT_ASSOCIATIONS_'||MSC_CL_COLLECTION.v_instance_code;
777 ELSE
778 lv_tbl:= 'MSC_EAM_ACT_ASSOCIATIONS';
779 END IF;
780 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,
781 'In Procedure LOAD_EAM_INFO lv_tbl'||lv_tbl);
782
783 lv_sql_stmt:=
784 'INSERT INTO '||lv_tbl
785 ||'( ASSET_ACTIVITY_ID,'
786 ||'ORGANIZATION_ID,'
787 ||'ACTIVITY,'
788 ||'ASSET_REBUILD_ITEM_ID,'
789 ||'ACTIVITY_TYPE,'
790 ||'ASSET_REBUILD_GROUP,'
791 ||'EAM_ITEM_TYPE,'
792 ||'REFRESH_NUMBER,'
793 ||'SR_INSTANCE_ID,'
794 ||'LAST_UPDATE_DATE,'
795 ||'LAST_UPDATED_BY,'
799 ||'( :ASSET_ACTIVITY_ID,'
796 ||'CREATION_DATE,'
797 ||'CREATED_BY) '
798 ||'VALUES'
800 ||':ORGANIZATION_ID,'
801 ||':ACTIVITY,'
802 ||':ASSET_REBUILD_ITEM_ID,'
803 ||':ACTIVITY_TYPE,'
804 ||':ASSET_REBUILD_GROUP,'
805 ||':EAM_ITEM_TYPE,'
806 ||':REFRESH_NUMBER,'
807 ||':SR_INSTANCE_ID,'
808 ||':v_current_date,'
809 ||':v_current_user,'
810 ||':v_current_date,'
811 ||':v_current_user)';
812
813 /*OPEN cgen FOR c1;
814
815 IF (cgen%ISOPEN) THEN
816
817 LOOP
818
819 FETCH cgen INTO
820 lv_ASSET_ACTIVITY_ID,
821 lv_ORGANIZATION_ID,
822 lv_ACTIVITY,
823 lv_ASSET_REBUILD_ITEM_ID,
824 lv_ACTIVITY_TYPE,
825 lv_ASSET_REBUILD_GROUP,
826 lv_EAM_ITEM_TYPE;
827
828
829 EXIT WHEN cgen%NOTFOUND;
830
831 BEGIN
832
833 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
834
835 FOR c_rec IN c1 LOOP
836 UPDATE MSC_EAM_ACT_ASSOCIATIONS
837 SET
838 ACTIVITY = c_rec.ACTIVITY,
839 ACTIVITY_TYPE = c_rec.ACTIVITY_TYPE,
840 ASSET_REBUILD_GROUP = c_rec.ASSET_REBUILD_GROUP,
841 EAM_ITEM_TYPE = c_rec.EAM_ITEM_TYPE,
842 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
843 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
844 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
845 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
846 AND ASSET_ACTIVITY_ID = c_rec.asset_activity_id
847 AND ORGANIZATION_ID = c_rec.organization_id
848 AND ASSET_REBUILD_ITEM_ID = c_rec.asset_rebuild_item_id;
849
850
851
852 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
853
854 EXECUTE IMMEDIATE lv_sql_stmt
855 USING
856 c_rec.ASSET_ACTIVITY_ID,
857 c_rec.ORGANIZATION_ID,
858 c_rec.ACTIVITY,
859 c_rec.ASSET_REBUILD_ITEM_ID,
860 c_rec.ACTIVITY_TYPE,
861 c_rec.ASSET_REBUILD_GROUP,
862 c_rec.EAM_ITEM_TYPE,
863 c_rec.SR_INSTANCE_ID,
864 MSC_CL_COLLECTION.v_last_collection_id,
865 MSC_CL_COLLECTION.v_current_date,
866 MSC_CL_COLLECTION.v_current_user,
867 MSC_CL_COLLECTION.v_current_date,
868 MSC_CL_COLLECTION.v_current_user;
869
870 total_count := total_count + 1;
871 END IF;
872
873 END LOOP;
874 /*EXCEPTION
875 WHEN OTHERS THEN
876
877 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
878
879 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
880 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
881 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
882 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
883 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
884
885 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
886 RAISE;
887
888 ELSE
889
890 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
891
892 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
893 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
894 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_INFO');
895 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_EAM_ACT_ASSOCIATIONS');
896 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
897
898 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
899 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSET_ACTIVITY_ID');
900 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ASSET_ACTIVITY_ID );
901 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
902
903 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
904 END IF;
905
906
907 --END;
908
909
910 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OPERATIONS = '|| total_count);
911 END IF;
912
913 COMMIT;
914 END;
915
916 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
917 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
918 lv_retcode,
919 'MSC_EAM_ACT_ASSOCIATIONS',
920 MSC_CL_COLLECTION.v_INSTANCE_CODE,
921 MSC_UTIL.G_ERROR);
922
923 IF lv_retcode = MSC_UTIL.G_ERROR THEN
924 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
925 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
926 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
927 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
928 END IF;
929 END IF;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 --IF cgen%ISOPEN THEN CLOSE cgen; END IF;
934
935 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_OP>>');
936 --- IF lv_cursor_stmt IS NOT NULL THEN
937 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
938 -- END IF;
939 -- IF lv_sql_stmt IS NOT NULL THEN
940 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
941 -- END IF;
942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
943 RAISE;*/
944
945
946 PROCEDURE LOAD_EAM_FORECASTS IS
947
948 lv_errbuf VARCHAR2(240);
949 lv_retcode NUMBER;
950 lv_tbl VARCHAR2(30);
951 lv_sql_ins VARCHAR2(6000);
952 BEGIN
953 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'In Procedure LOAD_EAM_FORECASTS ');
954
955 /*Delete msc_table based on org group ???? wher wil that be handled...*/
956
957 -- ========= Prepare the Cursor Statement ==========
958 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
959 lv_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
960 ELSE
961 lv_tbl:= 'MSC_SUPPLIES';
962 END IF;
963
964 IF (MSC_CL_COLLECTION.v_is_complete_refresh
965 OR MSC_CL_COLLECTION.v_is_partial_refresh)
966 AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
967
968 BEGIN
969
970 lv_sql_ins := 'insert into '||lv_tbl
971 ||' ( PLAN_ID,'
972 ||' TRANSACTION_ID,'
973 ||' INVENTORY_ITEM_ID,'
974 ||' ORGANIZATION_ID,'
975 ||' ORDER_NUMBER,'
976 ||' NEW_ORDER_QUANTITY,'
977 ||' NEW_SCHEDULE_DATE,'
978 ||' FIRM_PLANNED_TYPE, '
979 ||' NEW_WIP_START_DATE,'
980 ||' ORDER_TYPE,'
981 ||' COLL_ORDER_TYPE, '
982 ||' WIP_STATUS_CODE,'
983 ||' ASSET_ITEM_ID ,'
984 ||' SCHEDULE_DESIGNATOR_ID,'
985 ||' MAINTENANCE_OBJECT_SOURCE,'
986 ||' CLASS_CODE,'
987 ||' SOURCE_ITEM_ID,'
988 ||' TO_BE_EXPLODED,'
989 ||' SR_INSTANCE_ID, '
990 ||' REFRESH_NUMBER, '
991 ||' LAST_UPDATE_DATE, '
992 ||' LAST_UPDATED_BY, '
993 ||' CREATION_DATE, '
994 ||' CREATED_BY) '
995 ||'SELECT'
996 ||' -1,'
997 ||' MSC_SUPPLIES_S.NEXTVAL,'
998 ||' t1.INVENTORY_ITEM_ID,'
999 ||' ms.ORGANIZATION_ID,'
1000 ||' ''AGGR-'''||'||MSC_SUPPLIES_AGGR_WO_S.NEXTVAL,'
1001 ||' ms.NEW_ORDER_QUANTITY,'
1002 ||' ms.NEW_SCHEDULE_DATE,'
1003 ||' ms.FIRM_PLANNED_TYPE,'
1004 ||' ms.NEW_WIP_START_DATE,'
1005 ||' ms.ORDER_TYPE,'
1006 ||' ms.COLL_ORDER_TYPE, '
1007 ||' 17,'
1008 ||' t2.INVENTORY_ITEM_ID,'
1009 -- ||' ms.ASSET_ITEM_ID ,'
1010 ||' md.DESIGNATOR_ID,'
1011 ||' ms.MAINTENANCE_OBJECT_SOURCE,'
1012 ||' ms.CLASS_CODE,'
1013 ||' ms.SOURCE_ITEM_ID,'
1014 ||' ms.TO_BE_EXPLODED,'
1015 ||' ms.SR_INSTANCE_ID,'
1016 ||' :v_last_collection_id,'
1017 ||' :v_current_date,'
1018 ||' :v_current_user,'
1019 ||' :v_current_date,'
1020 ||' :v_current_user '
1021 ||' FROM MSC_ITEM_ID_LID t1,'
1022 ||' MSC_ITEM_ID_LID t2,'
1023 ||' MSC_ST_SUPPLIES ms,'
1024 ||' MSC_DESIGNATORS md'
1025 ||' WHERE t1.SR_INVENTORY_ITEM_ID= -1002'
1026 ||' AND t1.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1027 ||' AND t2.SR_INVENTORY_ITEM_ID= ms.ASSET_ITEM_ID '
1028 ||' AND t2.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1029 ||' AND ms.DELETED_FLAG = '|| MSC_UTIL.SYS_NO
1030 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md.SRC_SIM_FCST_ID'
1031 ||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1032 ||' AND ms.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1033 ||' AND ms.ORDER_TYPE = 92'
1034 ||' AND ms.MAINTENANCE_OBJECT_SOURCE = 1'
1035 ||' AND md.DESIGNATOR_TYPE = 12'
1036 ||' AND md.organization_id = ms.organization_id '
1037 ||' AND md.designator = ''-23453''';
1038
1039 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Sql stmt - '||lv_sql_ins);
1040
1041 EXECUTE IMMEDIATE lv_sql_ins
1042 USING MSC_CL_COLLECTION.v_last_collection_id,
1043 MSC_CL_COLLECTION.v_current_date,
1044 MSC_CL_COLLECTION.v_current_user,
1045 MSC_CL_COLLECTION.v_current_date,
1046 MSC_CL_COLLECTION.v_current_user;
1047
1048 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'no.of rows inserted '||SQL%ROWCOUNT);
1049 COMMIT;
1050 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Eam forecasts loaded');
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1054 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
1055 '========================================');
1056 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1057 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_FORECASTS');
1058 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
1059 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1060 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1061 RAISE;
1062 ELSE
1063 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,
1064 '========================================');
1065 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1066 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_EAM_FORECASTS');
1067 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SUPPLIES');
1068 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1069 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1070 END IF;
1071 END;
1072 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
1073
1074
1075 IF (MSC_CL_COLLECTION.v_is_complete_refresh
1076 OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1077 COMMIT;
1078 END IF;
1079
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 IF lv_sql_ins IS NOT NULL THEN
1084 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_ins);
1085 END IF;
1086 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1087 RAISE;
1088
1089 COMMIT;
1090 END LOAD_EAM_FORECASTS;
1091
1092 PROCEDURE LOAD_EAM_FORECAST_DEMANDS
1093 IS
1094
1095 lv_cursor_stmt VARCHAR2(32767);
1096 lv_tbl VARCHAR2(30);
1097 lv_sql_stmt VARCHAR2(32767);
1098 lv_supplies_tbl VARCHAR2(30);
1099
1100 BEGIN
1101 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1102 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1103 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1104 ELSE
1105 lv_tbl:= 'MSC_DEMANDS';
1106 lv_supplies_tbl:= 'MSC_SUPPLIES';
1107 END IF;
1108
1109
1110 lv_cursor_stmt:=
1111 'SELECT '
1112 ||' -1,'
1113 ||' MSC_DEMANDS_S.NEXTVAL,'
1114 ||' ms.TRANSACTION_ID DISPOSITION_ID ,'
1115 ||' substr(ms.ORDER_NUMBER, 1,62) ORDER_NUMBER, '
1116 ||' ms.SCHEDULE_DESIGNATOR_ID,'
1117 ||' md.ORGANIZATION_ID,'
1118 ||' t1.INVENTORY_ITEM_ID,'
1119 ||' ms.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
1120 ||' md.USING_REQUIREMENT_QUANTITY,'
1121 ||' md.USING_ASSEMBLY_DEMAND_DATE,'
1122 ||' md.SR_INSTANCE_ID,'
1123 ||' md.DEMAND_TYPE, '
1124 ||' md.ORIGINATION_TYPE,'
1125 ||' md.MAINTENANCE_OBJECT_SOURCE,'
1126 ||' 1,'
1127 ||' :v_last_collection_id,'
1128 ||' :v_current_date,'
1129 ||' :v_current_user,'
1130 ||' :v_current_date,'
1131 ||' :v_current_user '
1132 ||' FROM MSC_ITEM_ID_LID t1,'
1133 ||' MSC_ST_DEMANDS md,'
1134 ||' MSC_DESIGNATORS md1 ,'
1135 || lv_supplies_tbl||' ms'
1136 ||' WHERE'
1137 ||' ms.MAINTENANCE_OBJECT_SOURCE = 1'
1138 ||' AND md.MAINTENANCE_OBJECT_SOURCE = 1'
1139 ||' AND ms.SOURCE_ITEM_ID =md.USING_ASSEMBLY_ITEM_ID'
1140 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
1141 ||' AND md.ASSET_ITEM_ID = ms.INVENTORY_ITEM_ID'
1142 ||' AND md.CLASS_CODE = ms.CLASS_CODE'
1143 --||' AND ms.DELETED_FLAG ='|| MSC_UTIL.SYS_NO
1144 ||' AND ms.ORDER_TYPE = 92 '
1145 ||' AND md.ORIGINATION_TYPE = 92'
1146 ||' AND ms.PLAN_ID = -1'
1147 ||' AND ms.NEW_WIP_START_DATE = md.USING_ASSEMBLY_DEMAND_DATE'
1148 ||' AND ms.ORGANIZATION_ID = md.ORGANIZATION_ID'
1149 ||' AND md.SR_INSTANCE_ID= '|| MSC_CL_COLLECTION.v_instance_id
1150 ||' AND t1.SR_INVENTORY_ITEM_ID= md.inventory_item_id '
1151 ||' AND t1.sr_instance_id= '|| MSC_CL_COLLECTION.v_instance_id
1152 ||' AND md.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
1153 ||' AND md.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
1154 ||' AND md.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1155 ||' AND md1.DESIGNATOR_TYPE = 12'
1156 ||' AND md1.organization_id = ms.organization_id '
1157 ||' AND md1.designator = ''-23453''';
1158
1159 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1160
1161 lv_sql_stmt:=
1162 'INSERT INTO '||lv_tbl
1163 ||' ( PLAN_ID,'
1164 ||' DEMAND_ID,'
1165 ||' DISPOSITION_ID,'
1166 ||' ORDER_NUMBER,'
1167 ||' SCHEDULE_DESIGNATOR_ID,'
1168 ||' ORGANIZATION_ID,'
1169 ||' INVENTORY_ITEM_ID,'
1170 ||' USING_ASSEMBLY_ITEM_ID,'
1171 ||' USING_REQUIREMENT_QUANTITY,'
1172 ||' USING_ASSEMBLY_DEMAND_DATE, '
1173 ||' SR_INSTANCE_ID, '
1174 ||' DEMAND_TYPE, '
1175 ||' ORIGINATION_TYPE, '
1176 ||' MAINTENANCE_OBJECT_SOURCE,'
1177 ||' OP_SEQ_NUM,'
1178 ||' REFRESH_NUMBER, '
1179 ||' LAST_UPDATE_DATE, '
1180 ||' LAST_UPDATED_BY, '
1181 ||' CREATION_DATE, '
1182 ||' CREATED_BY) '
1183 || lv_cursor_stmt ;
1184 BEGIN
1185
1186 SAVEPOINT Load_eam_dem;
1187 EXECUTE IMMEDIATE lv_sql_stmt
1188 USING
1189 MSC_CL_COLLECTION.v_last_collection_id,
1190 MSC_CL_COLLECTION.v_current_date,
1191 MSC_CL_COLLECTION.v_current_user,
1192 MSC_CL_COLLECTION.v_current_date,
1193 MSC_CL_COLLECTION.v_current_user;
1194
1195 COMMIT;
1196 RETURN;
1197
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200
1201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_EAM_FORECAST_DEMANDS>>');
1202 IF lv_sql_stmt IS NOT NULL THEN
1203 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
1204 END IF;
1205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1206
1207 ROLLBACK WORK TO SAVEPOINT Load_eam_dem;
1208
1209 END;
1210
1211 END IF;
1212
1213
1214 END LOAD_EAM_FORECAST_DEMANDS;
1215
1216 PROCEDURE LOAD_EAM_FORECAST_RR
1217 IS
1218
1219 lv_tbl VARCHAR2(30);
1220 lv_cursor_stmt VARCHAR2(32767);
1221 lv_supplies_tbl VARCHAR2(30);
1222 lv_sql_stmt VARCHAR2(32767);
1223
1224 BEGIN
1225
1226 IF MSC_CL_COLLECTION.v_exchange_mode = MSC_UTIL.SYS_YES THEN
1227 lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
1228 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1229 ELSE
1230 lv_tbl:= 'MSC_RESOURCE_REQUIREMENTS';
1231 lv_supplies_tbl:= 'MSC_SUPPLIES';
1232 END IF;
1233
1234
1235 lv_cursor_stmt:=
1236 'SELECT '
1237 ||' -1, '
1238 ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
1239 ||' ms.TRANSACTION_ID ,'
1240 ||' mrr.DEPARTMENT_ID,'
1241 ||' mrr.ORGANIZATION_ID,'
1242 ||' mrr.RESOURCE_ID,'
1243 ||' mrr.ASSIGNED_UNITS,'
1244 ||' mrr.OPERATION_HOURS_REQUIRED,'
1245 ||' mrr.TOUCH_TIME,'
1246 ||' mrr.UNADJUSTED_RESOURCE_HOURS,'
1247 ||' mrr.OPERATION_HOURS_REQUIRED,'
1248 ||' ms.NEW_WIP_START_DATE START_DATE,'
1249 ||' LAST_DAY(NEW_WIP_START_DATE) END_DATE,'
1250 ||' mrr.supply_type,'
1251 ||' mrr.MAINTENANCE_OBJECT_SOURCE,'
1252 ||' 1,'
1253 ||' 1,'
1254 ||' 1,'
1255 ||' mrr.SR_INSTANCE_ID,'
1256 ||' :v_last_collection_id,'
1257 ||' :v_current_date,'
1258 ||' :v_current_user,'
1259 ||' :v_current_date,'
1260 ||' :v_current_user '
1261 ||' FROM MSC_ST_RESOURCE_REQUIREMENTS mrr, '
1262 ||' MSC_DESIGNATORS md1 ,'
1263 || lv_supplies_tbl||' ms '
1264 ||' WHERE ms.MAINTENANCE_OBJECT_SOURCE = 1'
1265 ||' AND mrr.MAINTENANCE_OBJECT_SOURCE = 1'
1266 ||' AND mrr.CLASS_CODE = ms.CLASS_CODE'
1267 ||' AND ms.ORDER_TYPE = 92 '
1268 ||' AND mrr.supply_type = 92 '
1269 ||' AND ms.PLAN_ID = -1'
1270 ||' AND ms.SCHEDULE_DESIGNATOR_ID = md1.DESIGNATOR_ID'
1271 ||' AND ms.SOURCE_ITEM_ID = mrr.INVENTORY_ITEM_ID'
1272 ||' AND ms.NEW_WIP_START_DATE = mrr.START_DATE'
1273 ||' AND ms.ORGANIZATION_ID = mrr.ORGANIZATION_ID'
1274 ||' AND mrr.SR_INSTANCE_ID='|| MSC_CL_COLLECTION.v_instance_id
1275 ||' AND mrr.SCHEDULE_DESIGNATOR_ID = md1.SRC_SIM_FCST_ID'
1276 ||' AND mrr.SR_INSTANCE_ID= md1.SR_INSTANCE_ID'
1277 ||' AND mrr.SR_INSTANCE_ID= ms.SR_INSTANCE_ID'
1278 ||' AND md1.DESIGNATOR_TYPE = 12'
1279 ||' AND md1.organization_id = ms.organization_id '
1280 ||' AND md1.designator = ''-23453''';
1281 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1282
1283 lv_sql_stmt:= 'INSERT INTO '||lv_tbl
1284 ||' ( PLAN_ID,'
1285 ||' TRANSACTION_ID,'
1286 ||' SUPPLY_ID,'
1287 ||' DEPARTMENT_ID,'
1288 ||' ORGANIZATION_ID,'
1289 ||' RESOURCE_ID,'
1290 ||' ASSIGNED_UNITS, '
1291 ||' RESOURCE_HOURS,'
1292 ||' TOUCH_TIME,'
1293 ||' UNADJUSTED_RESOURCE_HOURS,'
1294 ||' TOTAL_RESOURCE_HOURS,'
1295 ||' START_DATE,'
1296 ||' END_DATE,'
1297 ||' SUPPLY_TYPE,'
1298 ||' MAINTENANCE_OBJECT_SOURCE,'
1299 ||' OPERATION_SEQ_NUM,'
1300 ||' RESOURCE_SEQ_NUM,'
1301 ||' SCHEDULE_FLAG,'
1302 ||' SR_INSTANCE_ID, '
1303 ||' REFRESH_NUMBER, '
1304 ||' LAST_UPDATE_DATE, '
1305 ||' LAST_UPDATED_BY, '
1306 ||' CREATION_DATE, '
1307 ||' CREATED_BY) '
1308 || lv_cursor_stmt ;
1309
1310 BEGIN
1311
1312 SAVEPOINT Load_eam_res;
1313 EXECUTE IMMEDIATE lv_sql_stmt
1314 USING
1315 MSC_CL_COLLECTION.v_last_collection_id,
1316 MSC_CL_COLLECTION.v_current_date,
1317 MSC_CL_COLLECTION.v_current_user,
1318 MSC_CL_COLLECTION.v_current_date,
1319 MSC_CL_COLLECTION.v_current_user;
1320
1321 COMMIT;
1322 RETURN;
1323
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326
1327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_EAM_FORECAST_RR>>');
1328 IF lv_sql_stmt IS NOT NULL THEN
1329 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
1330 END IF;
1331 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1332
1333 ROLLBACK WORK TO SAVEPOINT Load_eam_res;
1334 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
1335
1336 END;
1337
1338 END IF;
1339
1340 END LOAD_EAM_FORECAST_RR;
1341
1342
1343 PROCEDURE LINK_REBUILD_BOM_ACTIVITY IS
1344
1345 lv_sql_stmt VARCHAR2(5000);
1346 lv_sql_stmt1 VARCHAR2(5000);
1347 lv_sql_stmt2 VARCHAR2(5000);
1348 lv_sql_stmt3 VARCHAR2(5000);
1349 lv_cursor_stmt VARCHAR2(5000);
1350 lv_ins_stmt VARCHAR2(5000);
1351 lv_cursor_stmt1 VARCHAR2(5000);
1352 lv_cursor_stmt2 VARCHAR2(5000);
1353 lv_ins_stmt1 VARCHAR2(5000);
1354 lv_ins_stmt2 VARCHAR2(5000);
1355
1356
1357 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
1358 c4 CurTyp;
1359
1360 lv_PROCESS_SEQUENCE_ID NUMBER;
1361 lv_BILL_SEQUENCE_ID NUMBER;
1362 lv_ASSEMBLY_TYPE NUMBER;
1363 lv_ASSET_REBUILD_ITEM_ID NUMBER;
1364 lv_ORGANIZATION_ID NUMBER;
1365 lv_ASSET_ACTIVITY_ID NUMBER;
1366 lv_REPAIRABLE NUMBER;
1367 lv_PLAN_ID NUMBER;
1368 lv_SR_INSTANCE_ID NUMBER;
1369 lv_ROUTING_SEQUENCE_ID NUMBER;
1370 lv_ROUTING_TYPE NUMBER;
1371 lv_EFFECTIVITY_DATE DATE;
1372 lv_LINE_ID NUMBER;
1373
1374 cnt NUMBER;
1375 cnt1 NUMBER;
1376 cnt2 NUMBER;
1377
1378 BEGIN
1379
1380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Starting LINK_REBUILD_BOM_ACTIVITY ......');
1382 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1383
1384 lv_cursor_stmt := 'SELECT b.BILL_SEQUENCE_ID, '
1385 ||'b.ASSEMBLY_TYPE, '
1386 ||'mea.ASSET_REBUILD_ITEM_ID, '
1387 ||'b.ORGANIZATION_ID, '
1388 ||'mea.ASSET_ACTIVITY_ID, '
1389 ||'b.REPAIRABLE, '
1390 ||'b.PLAN_ID, '
1391 ||'b.SR_INSTANCE_ID '
1392 ||'FROM MSC_BOMS b, '
1393 ||'MSC_EAM_ACT_ASSOCIATIONS mea '
1394 ||'WHERE b.ASSEMBLY_ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
1395 ||'AND b.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
1396 ||'AND b.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1397 ||'AND b.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1398 ||'AND b.PLAN_ID =-1 '
1399 ||'AND b.SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id
1400 ||' AND b.repairable =1 ';
1401
1402 BEGIN
1403
1404 cnt :=0;
1405 OPEN c4 FOR lv_cursor_stmt;
1406 LOOP
1407 FETCH c4 INTO lv_BILL_SEQUENCE_ID,
1408 lv_ASSEMBLY_TYPE,
1409 lv_ASSET_REBUILD_ITEM_ID,
1410 lv_ORGANIZATION_ID,
1411 lv_ASSET_ACTIVITY_ID,
1412 lv_REPAIRABLE,
1413 lv_PLAN_ID,
1414 lv_SR_INSTANCE_ID;
1415
1416 EXIT WHEN c4%NOTFOUND;
1417
1418
1419 UPDATE MSC_BOMS
1420 SET BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID,
1421 ASSEMBLY_TYPE =lv_ASSEMBLY_TYPE,
1422 ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
1423 ORGANIZATION_ID = lv_ORGANIZATION_ID,
1424 ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
1425 REPAIRABLE = lv_REPAIRABLE,
1426 --PLAN_ID = lv_PLAN_ID,
1427 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1428 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1429 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1430 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1431 AND PLAN_ID = lv_PLAN_ID
1432 AND BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID
1433 AND ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
1434 AND ORGANIZATION_ID = lv_organization_id
1435 AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID;
1436
1437 cnt := cnt+1;
1438 END LOOP;
1439
1440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_BOMS: count ' || cnt);
1441
1442 lv_ins_stmt :=
1443 'INSERT INTO MSC_BOMS ( '
1444 ||'BILL_SEQUENCE_ID, '
1445 ||'ASSEMBLY_TYPE, '
1446 ||'ASSEMBLY_ITEM_ID, '
1447 ||'ORGANIZATION_ID, '
1448 ||'ACTIVITY_ITEM_ID, '
1449 ||'REPAIRABLE, '
1450 ||'PLAN_ID, '
1451 ||'SR_INSTANCE_ID, '
1452 ||'REFRESH_NUMBER, '
1453 ||'LAST_UPDATE_DATE, '
1454 ||'LAST_UPDATED_BY, '
1455 ||'CREATION_DATE, '
1456 ||'CREATED_BY ) '
1457 ||'SELECT '
1458 ||'b.BILL_SEQUENCE_ID, '
1459 ||'b.ASSEMBLY_TYPE, '
1460 ||'mea.ASSET_REBUILD_ITEM_ID, '
1461 ||'b.ORGANIZATION_ID, '
1462 ||'mea.ASSET_ACTIVITY_ID, '
1463 ||'1, '
1464 ||'b.PLAN_ID, '
1465 ||'b.SR_INSTANCE_ID, '
1466 ||':v_last_collection_id, '
1467 ||':v_current_date, '
1468 ||':v_current_user, '
1469 ||':v_current_date, '
1470 ||':v_current_user '
1471 ||'FROM MSC_BOMS b, '
1472 ||'MSC_EAM_ACT_ASSOCIATIONS mea '
1473 ||'WHERE b.ASSEMBLY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
1474 ||'AND b.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1475 ||'AND b.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1476 ||'AND b.PLAN_ID =-1 '
1477 ||'AND b.SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id
1478 ||' and (b.BILL_SEQUENCE_ID,mea.ASSET_REBUILD_ITEM_ID,mea.ASSET_ACTIVITY_ID,b.ORGANIZATION_ID) '
1479 ||' not in '
1480 ||'(select BILL_SEQUENCE_ID,ASSEMBLY_ITEM_ID,ACTIVITY_ITEM_ID,ORGANIZATION_ID '
1481 ||'from MSC_BOMS where PLAN_ID =-1 '
1482 ||' AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
1483
1484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'stmt being executed-- ' || lv_ins_stmt);
1485
1486 EXECUTE IMMEDIATE lv_ins_stmt USING MSC_CL_COLLECTION.v_last_collection_id,
1487 MSC_CL_COLLECTION.v_current_date,
1488 MSC_CL_COLLECTION.v_current_user,
1489 MSC_CL_COLLECTION.v_current_date,
1490 MSC_CL_COLLECTION.v_current_user;
1491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_boms '
1492 ||SQL%ROWCOUNT);
1493 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1494
1495 COMMIT;
1496
1497 EXCEPTION WHEN NO_DATA_FOUND THEN
1498 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for BOM:' );
1499
1500 WHEN OTHERS THEN
1501 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
1502 NULL;
1503
1504 END;
1505
1506
1507 BEGIN
1508
1509 cnt1 :=0;
1510 lv_cursor_stmt1 := 'SELECT '
1511 ||'mr.ROUTING_SEQUENCE_ID, '
1512 ||'mr.ROUTING_TYPE, '
1513 ||'mea.ASSET_REBUILD_ITEM_ID, '
1514 ||'mr.ORGANIZATION_ID, '
1515 ||'mea.ASSET_ACTIVITY_ID, '
1516 ||'mr.REPAIRABLE, '
1517 ||'mr.PLAN_ID, '
1518 ||'mr.SR_INSTANCE_ID '
1519 ||'FROM MSC_ROUTINGS mr, '
1520 ||'MSC_EAM_ACT_ASSOCIATIONS mea '
1521 ||'WHERE mr.ASSEMBLY_ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
1522 ||'AND mr.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
1523 ||'AND mr.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1524 ||'AND mr.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1525 ||'AND mr.repairable=1 '
1526 ||'AND mr.PLAN_ID =-1 '
1527 ||'AND mr.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id;
1528
1529
1530 OPEN c4 FOR lv_cursor_stmt1;
1531 LOOP
1532 FETCH c4 INTO lv_ROUTING_SEQUENCE_ID,
1533 lv_ROUTING_TYPE,
1534 lv_ASSET_REBUILD_ITEM_ID,
1535 lv_ORGANIZATION_ID,
1536 lv_ASSET_ACTIVITY_ID,
1537 lv_REPAIRABLE,
1538 lv_PLAN_ID,
1539 lv_SR_INSTANCE_ID;
1540
1541 EXIT WHEN c4%NOTFOUND;
1542
1543
1544 UPDATE MSC_ROUTINGS
1545 SET ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID,
1546 ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
1547 ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
1548 ROUTING_TYPE =lv_ROUTING_TYPE,
1549 ORGANIZATION_ID = lv_ORGANIZATION_ID,
1550 REPAIRABLE = lv_REPAIRABLE,
1551 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1552 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1553 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1554 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1555 AND PLAN_ID = lv_PLAN_ID
1556 AND ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID
1557 AND ASSEMBLY_ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
1558 AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID;
1559
1560 cnt1 := cnt1+1;
1561
1562 END LOOP;
1563
1564 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_ROUTINGS: count ' || cnt1);
1565
1566 lv_ins_stmt1 :=
1567 'INSERT INTO MSC_ROUTINGS ( '
1568 ||'ROUTING_SEQUENCE_ID, '
1569 ||'ROUTING_TYPE, '
1570 ||'ASSEMBLY_ITEM_ID, '
1571 ||'ORGANIZATION_ID, '
1572 ||'ACTIVITY_ITEM_ID, '
1573 ||'REPAIRABLE, '
1574 ||'PLAN_ID, '
1575 ||'SR_INSTANCE_ID, '
1576 ||'REFRESH_NUMBER, '
1577 ||'LAST_UPDATE_DATE, '
1578 ||'LAST_UPDATED_BY, '
1579 ||'CREATION_DATE, '
1580 ||'CREATED_BY ) '
1581 ||'SELECT '
1582 ||'mr.ROUTING_SEQUENCE_ID, '
1583 ||'mr.ROUTING_TYPE, '
1584 ||'mea.ASSET_REBUILD_ITEM_ID, '
1585 ||'mr.ORGANIZATION_ID, '
1586 ||'mea.ASSET_ACTIVITY_ID, '
1587 ||'1, '
1588 ||'mr.PLAN_ID, '
1589 ||'mr.SR_INSTANCE_ID, '
1590 ||':v_last_collection_id, '
1591 ||':v_current_date, '
1592 ||':v_current_user, '
1593 ||':v_current_date, '
1594 ||':v_current_user '
1595 ||'FROM MSC_ROUTINGS mr, '
1596 ||'MSC_EAM_ACT_ASSOCIATIONS mea '
1597 ||'WHERE mr.ASSEMBLY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
1598 ||'AND mr.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1599 ||'AND mr.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1600 ||'AND mr.PLAN_ID =-1 '
1601 ||'AND mr.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
1602 ||' AND (mr.ROUTING_SEQUENCE_ID,mea.ASSET_REBUILD_ITEM_ID,mea.ASSET_ACTIVITY_ID,mr.ORGANIZATION_ID)'
1603 ||' not in '
1604 ||'(select ROUTING_SEQUENCE_ID, ASSEMBLY_ITEM_ID,ASSET_ACTIVITY_ID,ORGANIZATION_ID '
1605 ||'from MSC_ROUTINGS where PLAN_ID =-1 '
1606 ||'AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
1607
1608
1609 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'stmt being executed-- ' || lv_ins_stmt1);
1610
1611 EXECUTE IMMEDIATE lv_ins_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id,
1612 MSC_CL_COLLECTION.v_current_date,
1613 MSC_CL_COLLECTION.v_current_user,
1614 MSC_CL_COLLECTION.v_current_date,
1615 MSC_CL_COLLECTION.v_current_user;
1616 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_routings'
1617 ||SQL%ROWCOUNT);
1618 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1619
1620 COMMIT;
1621
1622 EXCEPTION WHEN NO_DATA_FOUND THEN
1623 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for ROUTING:' );
1624
1625 WHEN OTHERS THEN
1626 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
1627 NULL;
1628
1629 END;
1630
1631 BEGIN
1632
1633 cnt2 :=0;
1634 lv_cursor_stmt2 := 'SELECT '
1635 ||'mpe.PROCESS_SEQUENCE_ID, '
1636 ||'mpe.ROUTING_SEQUENCE_ID, '
1637 ||'mpe.BILL_SEQUENCE_ID, '
1638 ||'mea.ASSET_REBUILD_ITEM_ID, '
1639 ||'mea.ASSET_ACTIVITY_ID, '
1640 ||'mpe.ORGANIZATION_ID, '
1641 ||'mpe.EFFECTIVITY_DATE, '
1642 ||'mpe.LINE_ID, '
1643 ||'mpe.REPAIRABLE, '
1644 ||'mpe.PLAN_ID, '
1645 ||'mpe.SR_INSTANCE_ID '
1646 ||'FROM MSC_PROCESS_EFFECTIVITY mpe, '
1647 ||'MSC_EAM_ACT_ASSOCIATIONS mea '
1648 ||'WHERE mpe.ITEM_ID = mea.ASSET_REBUILD_ITEM_ID '
1649 ||'AND mpe.ACTIVITY_ITEM_ID = mea.ASSET_ACTIVITY_ID '
1650 ||'AND mpe.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1651 ||'AND mpe.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1652 ||'AND mpe.PLAN_ID =-1 '
1653 ||'AND mpe.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
1654 ||' AND mpe.repairable =1';
1655
1656 OPEN c4 FOR lv_cursor_stmt2;
1657 LOOP
1658 FETCH c4 INTO lv_PROCESS_SEQUENCE_ID,
1659 lv_ROUTING_SEQUENCE_ID,
1660 lv_BILL_SEQUENCE_ID,
1661 lv_ASSET_REBUILD_ITEM_ID,
1662 lv_ASSET_ACTIVITY_ID,
1663 lv_ORGANIZATION_ID,
1664 lv_EFFECTIVITY_DATE,
1665 lv_LINE_ID,
1666 lv_REPAIRABLE,
1667 lv_PLAN_ID,
1668 lv_SR_INSTANCE_ID;
1669
1670 EXIT WHEN c4%NOTFOUND;
1671
1672
1673 UPDATE MSC_PROCESS_EFFECTIVITY
1674 SET PROCESS_SEQUENCE_ID =lv_PROCESS_SEQUENCE_ID,
1675 ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID,
1676 BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID,
1677 ITEM_ID = lv_ASSET_REBUILD_ITEM_ID,
1678 ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID,
1679 ORGANIZATION_ID = lv_ORGANIZATION_ID,
1680 EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE,
1681 LINE_ID = lv_LINE_ID,
1682 REPAIRABLE = lv_REPAIRABLE,
1683 --PLAN_ID = lv_PLAN_ID,
1684 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
1685 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1686 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1687 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1688 AND PLAN_ID = lv_PLAN_ID
1689 AND PROCESS_SEQUENCE_ID =lv_PROCESS_SEQUENCE_ID
1690 AND ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID
1691 AND BILL_SEQUENCE_ID = lv_BILL_SEQUENCE_ID
1692 AND ITEM_ID = lv_ASSET_REBUILD_ITEM_ID
1693 AND ACTIVITY_ITEM_ID = lv_ASSET_ACTIVITY_ID
1694 AND EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE
1695 AND LINE_ID = lv_LINE_ID
1696 AND REPAIRABLE = lv_REPAIRABLE;
1697
1698 cnt2 := cnt2+1;
1699
1700 END LOOP;
1701
1702 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'after update of MSC_PROCESS_EFFECTIVITY: count ' || cnt2);
1703
1704 lv_ins_stmt2 :=
1705 'INSERT INTO MSC_PROCESS_EFFECTIVITY ( '
1706 ||'PROCESS_SEQUENCE_ID, '
1707 ||'ROUTING_SEQUENCE_ID, '
1708 ||'BILL_SEQUENCE_ID, '
1709 ||'ITEM_ID, '
1710 ||'ACTIVITY_ITEM_ID, '
1711 ||'ORGANIZATION_ID, '
1712 ||'EFFECTIVITY_DATE, '
1713 ||'LINE_ID, '
1714 ||'REPAIRABLE, '
1715 ||'PLAN_ID, '
1716 ||'SR_INSTANCE_ID, '
1717 ||'REFRESH_NUMBER, '
1718 ||'LAST_UPDATE_DATE, '
1719 ||'LAST_UPDATED_BY, '
1720 ||'CREATION_DATE, '
1721 ||'CREATED_BY ) '
1722 ||'SELECT '
1723 ||'MSC_PROCESS_EFFECTIVITY_S.NEXTVAL, '
1724 ||'mpe.ROUTING_SEQUENCE_ID, '
1725 ||'mpe.BILL_SEQUENCE_ID, '
1726 ||'mea.ASSET_REBUILD_ITEM_ID, '
1727 ||'mea.ASSET_ACTIVITY_ID, '
1728 ||'mpe.ORGANIZATION_ID, '
1729 ||'mpe.EFFECTIVITY_DATE, '
1730 ||'mpe.LINE_ID, '
1731 ||'1, '
1732 ||'mpe.PLAN_ID, '
1733 ||'mpe.SR_INSTANCE_ID, '
1734 ||':v_last_collection_id, '
1735 ||':v_current_date, '
1736 ||':v_current_user, '
1737 ||':v_current_date, '
1738 ||':v_current_user '
1739 ||' FROM MSC_PROCESS_EFFECTIVITY mpe, '
1740 ||' MSC_EAM_ACT_ASSOCIATIONS mea '
1741 ||' WHERE mpe.ITEM_ID = mea.ASSET_ACTIVITY_ID '
1742 ||' AND mpe.ORGANIZATION_ID = mea.ORGANIZATION_ID '
1743 ||' AND mpe.SR_INSTANCE_ID = mea.SR_INSTANCE_ID '
1744 ||' AND mpe.PLAN_ID =-1 '
1745 ||' AND mpe.SR_INSTANCE_ID = '|| MSC_CL_COLLECTION.v_instance_id
1746 ||' AND (mea.ASSET_REBUILD_ITEM_ID,mpe.ROUTING_SEQUENCE_ID,mpe.BILL_SEQUENCE_ID,mea.ASSET_ACTIVITY_ID,mpe.LINE_ID,mpe.ORGANIZATION_ID)'
1747 ||' not in '
1748 ||' (select ITEM_ID,ROUTING_SEQUENCE_ID,BILL_SEQUENCE_ID,ACTIVITY_ITEM_ID,LINE_ID,ORGANIZATION_ID '
1749 ||' from MSC_PROCESS_EFFECTIVITY where PLAN_ID =-1 '
1750 ||' AND SR_INSTANCE_ID = ' || MSC_CL_COLLECTION.v_instance_id ||') ';
1751
1752
1753 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'stmt being executed--' || lv_ins_stmt2);
1754
1755 EXECUTE IMMEDIATE lv_ins_stmt2 USING MSC_CL_COLLECTION.v_last_collection_id,
1756 MSC_CL_COLLECTION.v_current_date,
1757 MSC_CL_COLLECTION.v_current_user,
1758 MSC_CL_COLLECTION.v_current_date,
1759 MSC_CL_COLLECTION.v_current_user;
1760 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'rows inserted into msc_process_effec'
1761 ||'tivity '||SQL%ROWCOUNT);
1762 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1763
1764 COMMIT;
1765
1766 EXCEPTION WHEN NO_DATA_FOUND THEN
1767 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for ROUTING:' );
1768
1769 WHEN OTHERS THEN
1770 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
1771 NULL;
1772
1773 END;
1774
1775
1776 END LINK_REBUILD_BOM_ACTIVITY;
1777
1778 PROCEDURE UPD_PROD_STOCK IS
1779
1780 cursor c_prod_stock is
1781 select s.inventory_item_id,s.asset_item_id,s.order_type,s.order_number,
1782 s.transaction_id,s.organization_id,s.activity_item_id
1783 from msc_supplies s,
1784 msc_eam_act_associations mea
1785 where s.asset_item_id = mea.asset_rebuild_item_id
1786 and s.inventory_item_id = mea.asset_rebuild_item_id
1787 and s.activity_item_id = mea.asset_activity_id
1788 and s.organization_id = mea.organization_id
1789 and s.sr_instance_id = mea.sr_instance_id
1790 and mea.activity_type = 6
1791 and s.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1792 and s.plan_id=-1
1793 and s.order_type=70
1794 and s.maintenance_object_source=1
1795 and s.schedule_designator_id is null
1796 order by s.transaction_id;
1797
1798 BEGIN
1799
1800 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Updation of Produces_to_stock flag' );
1801
1802 for c_rec in c_prod_stock loop
1803 update msc_supplies ms
1804 set ms.produces_to_stock =1
1805 where ms.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
1806 and ms.plan_id=-1
1807 and ms.order_type= c_rec.order_type
1808 and ms.maintenance_object_source=1
1809 and ms.schedule_designator_id is null
1810 and ms.asset_item_id = c_rec.asset_item_id
1811 and ms.organization_id = c_rec.organization_id
1812 and ms.inventory_item_id = c_rec.inventory_item_id
1813 and ms.activity_item_id = c_rec.activity_item_id ;
1814
1815 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Updated rows'||SQL%ROWCOUNT);
1816 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
1817 commit;
1818 end loop;
1819
1820
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823 ROLLBACK;
1824 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1825
1826
1827 END UPD_PROD_STOCK;
1828
1829
1830 END MSC_CL_EAM_ODS_LOAD;