[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_ROUTING_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_ROUTING_ODS_LOAD AS -- specification
2 /* $Header: MSCLRTGB.pls 120.9.12020000.2 2013/02/13 11:17:24 swundapa ship $ */
3
4 v_sub_str VARCHAR2(32767):=NULL;
5 c_count NUMBER:= 0;
6 -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
7
8 -- G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
9 -- To collect SRP Data when this profile is set to Yes neds to be deleted
10 -- v_is_cont_refresh BOOLEAN;
11 v_chr9 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(9);
12 v_chr10 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
13 v_chr13 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
14
15
16
17
18 PROCEDURE LOAD_STD_OP_RESOURCES
19 IS
20 CURSOR std_op_res IS
21 SELECT
22 std_op_res.SR_INSTANCE_ID,
23 std_op_res.STANDARD_OPERATION_ID,
24 std_op_res.RESOURCE_ID,
25 std_op_res.OPERATION_CODE,
26 std_op_res.ORGANIZATION_ID,
27 std_op_res.DEPARTMENT_ID,
28 std_op_res.RESOURCE_SEQ_NUM,
29 std_op_res.RESOURCE_USAGE,
30 std_op_res.BASIS_TYPE,
31 std_op_res.RESOURCE_UNITS,
32 std_op_res.SUBSTITUTE_GROUP_NUM,
33 std_op_res.UOM_CODE,
34 std_op_res.SCHEDULE_FLAG
35 FROM MSC_ST_STD_OP_RESOURCES std_op_res
36 WHERE std_op_res.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
37
38 lv_cnt NUMBER;
39 lv_pbs NUMBER;
40 c_count NUMBER := 0;
41 total_count NUMBER := 0;
42
43 BEGIN
44
45 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
46 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
47 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
48 ELSE
49 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_STD_OP_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
50 END IF;
51
52 c_count := 0;
53 total_count := 0;
54
55 FOR c_rec IN std_op_res LOOP
56 BEGIN
57 INSERT INTO MSC_STD_OP_RESOURCES
58 ( PLAN_ID,
59 SR_INSTANCE_ID,
60 STANDARD_OPERATION_ID ,
61 RESOURCE_ID,
62 OPERATION_CODE,
63 ORGANIZATION_ID,
64 DEPARTMENT_ID,
65 RESOURCE_SEQ_NUM,
66 RESOURCE_USAGE,
67 BASIS_TYPE,
68 RESOURCE_UNITS,
69 SUBSTITUTE_GROUP_NUM,
70 UOM_CODE,
71 SCHEDULE_FLAG,
72 REFRESH_NUMBER,
73 LAST_UPDATE_DATE,
74 LAST_UPDATED_BY,
75 CREATION_DATE,
76 CREATED_BY)
77 VALUES
78 ( -1,
79 c_rec.SR_INSTANCE_ID,
80 c_rec.STANDARD_OPERATION_ID ,
81 c_rec.RESOURCE_ID,
82 c_rec.OPERATION_CODE,
83 c_rec.ORGANIZATION_ID,
84 c_rec.DEPARTMENT_ID,
85 c_rec.RESOURCE_SEQ_NUM,
86 c_rec.RESOURCE_USAGE,
87 c_rec.BASIS_TYPE,
88 c_rec.RESOURCE_UNITS,
89 c_rec.SUBSTITUTE_GROUP_NUM,
90 c_rec.UOM_CODE,
91 c_rec.SCHEDULE_FLAG,
92 MSC_CL_COLLECTION.v_last_collection_id,
93 MSC_CL_COLLECTION.v_current_date,
94 MSC_CL_COLLECTION.v_current_user,
95 MSC_CL_COLLECTION.v_current_date,
96 MSC_CL_COLLECTION.v_current_user );
97
98 c_count:= c_count+1;
99 total_count := total_count+1;
100
101 IF c_count> MSC_CL_COLLECTION.PBS THEN
102 COMMIT;
103 c_count:= 0;
104 END IF;
105
106 EXCEPTION
107
108 WHEN OTHERS THEN
109
110 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
111
112 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
113 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
114 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STD_OP_RESOURCES');
115 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
116 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
117
118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
119 RAISE;
120 ELSE
121 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
122
123 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
124 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
125 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_STD_OP_RESOURCES');
126 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
127 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
128
129 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
130 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_CODE');
131 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.OPERATION_CODE);
132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
133
134 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
135 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
136 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
138
139 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
140 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
141 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_SEQ_NUM);
142 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
143
144
145 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
146 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
147 FND_MESSAGE.SET_TOKEN('VALUE', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
148 MSC_CL_COLLECTION.v_instance_id));
149 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
151 END IF;
152
153 END;
154
155 END LOOP;
156 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total std op resources = '|| to_char(total_count));
157 COMMIT;
158
159 END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh */
160
161 END LOAD_STD_OP_RESOURCES;
162
163
164 PROCEDURE LOAD_OPERATION_NETWORKS IS
165
166 Cursor c10
167 is
168 Select
169 mon.FROM_OP_SEQ_ID,
170 mon.TO_OP_SEQ_ID,
171 mon.TRANSITION_TYPE,
172 mon.PLANNING_PCT,
173 mon.CUMMULATIVE_PCT,
174 mon.EFECTIVITY_DATE,
175 mon.DISABLE_DATE,
176 mon.PLAN_ID,
177 mon.CREATED_BY,
178 mon.CREATION_DATE,
179 mon.DELETED_FLAG,
180 mon.LAST_UPDATED_BY,
181 mon.LAST_UPDATE_DATE,
182 mon.LAST_UPDATE_LOGIN,
183 mon.ATTRIBUTE_CATEGORY,
184 mon.ATTRIBUTE1,
185 mon.ATTRIBUTE2,
186 mon.ATTRIBUTE3,
187 mon.ATTRIBUTE4,
188 mon.ATTRIBUTE5,
189 mon.ATTRIBUTE6,
190 mon.ATTRIBUTE7,
191 mon.ATTRIBUTE8,
192 mon.ATTRIBUTE9,
193 mon.ATTRIBUTE10,
194 mon.ATTRIBUTE11,
195 mon.ATTRIBUTE12,
196 mon.ATTRIBUTE13,
197 mon.ATTRIBUTE14,
198 mon.ATTRIBUTE15,
199 mon.routing_sequence_id,
200 mon.FROM_OP_SEQ_NUM,
201 mon.TO_OP_SEQ_NUM,
202 mon.TO_ROUTING_SEQUENCE_ID, /*ds change change start */
203 t1.INVENTORY_ITEM_ID FROM_ITEM_ID,
204 mon.ORGANIZATION_ID,
205 mon.MINIMUM_TRANSFER_QTY,
206 mon.MINIMUM_TIME_OFFSET,
207 mon.MAXIMUM_TIME_OFFSET,
208 mon.DEPENDENCY_TYPE,
209 mon.APPLY_TO_CHARGES,
210 mon.TRANSFER_PCT,
211 mon.TRANSFER_QTY,
212 mon.TRANSFER_UOM, /*ds change change end */
213 mon.REFRESH_ID,
214 mon.SR_INSTANCE_ID
215 from MSC_ST_OPERATION_NETWORKS mon,
216 MSC_ITEM_ID_LID t1 /* ds change change */
217 WHERE mon.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
218 AND mon.DELETED_FLAG = MSC_UTIL.SYS_NO
219 AND mon.FROM_ITEM_ID = t1.SR_INVENTORY_ITEM_ID(+) /* ds change change */
220 AND mon.sr_instance_id = t1.sr_instance_id(+) ; /* ds change change */
221
222
223 Cursor c10_d is
224 select
225 mon.FROM_OP_SEQ_ID,
226 mon.TO_OP_SEQ_ID,
227 mon.DELETED_FLAG,
228 mon.REFRESH_ID,
229 mon.SR_INSTANCE_ID
230 FROM MSC_ST_OPERATION_NETWORKS mon
231 WHERE mon.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
232 and mon.DELETED_FLAG= MSC_UTIL.SYS_YES;
233
234 c_count NUMBER:= 0;
235 lv_tbl VARCHAR2(30);
236 lv_sql_stmt VARCHAR2(5000);
237
238 BEGIN
239
240 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
241
242 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
243 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1);
244 ELSE
245 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
246 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_NETWORKS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
247 END IF;
248
249 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
250
251 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
252
253 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
254
255 FOR c_rec IN c10_d LOOP
256
257 DELETE MSC_OPERATION_NETWORKS
258 WHERE PLAN_ID= -1
259 AND FROM_OP_SEQ_ID = c_rec.from_op_seq_id
260 AND TO_OP_SEQ_ID = c_rec.to_op_seq_id
261 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
262
263 /* for opm , hard wnk and mtqs we don't have to worry as opm doesn't support incremental */
264 END LOOP;
265
266 END IF;
267
268 c_count:= 0;
269
270 FOR c_rec IN c10 LOOP
271
272 BEGIN
273
274 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
275
276 UPDATE MSC_OPERATION_NETWORKS
277 SET
278 FROM_OP_SEQ_ID = c_rec.from_op_seq_id,
279 TO_OP_SEQ_ID = c_rec.to_op_seq_id,
280 --routing_sequence_id = c_rec.routing_sequence_id,
281 TRANSITION_TYPE = c_rec.transition_type,
282 PLANNING_PCT = c_rec.planning_pct,
283 CUMMULATIVE_PCT = c_rec. cummulative_pct,
284 EFFECTIVITY_DATE = c_Rec.efectivity_date,
285 DISABLE_DATE = c_Rec.disable_date,
286 PLAN_ID = -1,
287 LAST_UPDATED_BY = MSC_CL_COLLECTION.v_current_user,
288 LAST_UPDATE_DATE = MSC_CL_COLLECTION.v_current_date,
289 ATTRIBUTE_CATEGORY = c_rec.attribute_category,
290 ATTRIBUTE1 = c_rec.attribute1,
291 ATTRIBUTE2 = c_rec.attribute2,
292 ATTRIBUTE3 = c_Rec.attribute3,
293 ATTRIBUTE4 = c_rec.attribute4,
294 ATTRIBUTE5 = c_rec.attribute5,
295 ATTRIBUTE6 = c_rec.attribute6,
296 ATTRIBUTE7 = c_rec.attribute7,
297 ATTRIBUTE8 = c_rec.attribute8,
298 ATTRIBUTE9 = c_rec.attribute9,
299 ATTRIBUTE10 = c_rec.attribute10,
300 ATTRIBUTE11 = c_rec.attribute11,
301 ATTRIBUTE12 = c_rec.attribute12,
302 ATTRIBUTE13 = c_rec.attribute13,
303 ATTRIBUTE14 = c_Rec.attribute14,
304 ATTRIBUTE15 = c_rec.attribute15,
305 FROM_OP_SEQ_NUM= c_rec.FROM_OP_SEQ_NUM,
306 TO_OP_SEQ_NUM = c_rec.TO_OP_SEQ_NUM,
307 DEPENDENCY_TYPE = c_rec.DEPENDENCY_TYPE, /* ds change other new attr not added as they are for opm */
308 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id
309 WHERE PLAN_ID= -1
310 AND FROM_OP_SEQ_ID= c_rec.FROM_OP_SEQ_ID
311 AND TO_OP_SEQ_ID= c_rec.TO_OP_SEQ_ID
312 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
313 AND routing_sequence_id = c_rec.routing_sequence_id;
314
315 END IF;
316
317 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
318
319 insert into MSC_OPERATION_NETWORKS
320 ( FROM_OP_SEQ_ID,
321 TO_OP_SEQ_ID,
322 ROUTING_SEQUENCE_ID,
323 TRANSITION_TYPE,
324 PLANNING_PCT,
325 CUMMULATIVE_PCT,
326 EFFECTIVITY_DATE,
327 DISABLE_DATE,
328 PLAN_ID,
329 TO_ROUTING_SEQUENCE_ID, /*ds change change start */
330 FROM_ITEM_ID,
331 ORGANIZATION_ID,
332 MINIMUM_TRANSFER_QTY,
333 MINIMUM_TIME_OFFSET,
334 MAXIMUM_TIME_OFFSET,
335 DEPENDENCY_TYPE,
336 APPLY_TO_CHARGES,
337 TRANSFER_PCT,
338 TRANSFER_QTY,
339 TRANSFER_UOM, /*ds change change end */
340 CREATED_BY,
341 CREATION_DATE,
342 LAST_UPDATED_BY,
343 LAST_UPDATE_DATE,
344 ATTRIBUTE_CATEGORY,
345 ATTRIBUTE1,
346 ATTRIBUTE2,
347 ATTRIBUTE3,
348 ATTRIBUTE4,
349 ATTRIBUTE5,
350 ATTRIBUTE6,
351 ATTRIBUTE7,
352 ATTRIBUTE8,
353 ATTRIBUTE9,
354 ATTRIBUTE10,
355 ATTRIBUTE11,
356 ATTRIBUTE12,
357 ATTRIBUTE13,
358 ATTRIBUTE14,
359 ATTRIBUTE15,
360 FROM_OP_SEQ_NUM,
361 TO_OP_SEQ_NUM,
362 REFRESH_NUMBER,
363 SR_INSTANCE_ID)
364 values( c_rec.FROM_OP_SEQ_ID,
365 c_rec.TO_OP_SEQ_ID,
366 c_rec.ROUTING_SEQUENCE_ID,
367 c_rec.TRANSITION_TYPE,
368 c_rec.PLANNING_PCT,
369 c_rec.CUMMULATIVE_PCT,
370 c_rec.EFECTIVITY_DATE,
371 c_rec.DISABLE_DATE,
372 -1,
373 c_rec.TO_ROUTING_SEQUENCE_ID, /*ds change change start */
374 c_rec.FROM_ITEM_ID ,
375 c_rec.ORGANIZATION_ID,
376 c_rec.MINIMUM_TRANSFER_QTY,
377 c_rec.MINIMUM_TIME_OFFSET,
378 c_rec.MAXIMUM_TIME_OFFSET,
379 c_rec.DEPENDENCY_TYPE,
380 c_rec.APPLY_TO_CHARGES,
381 c_rec.TRANSFER_PCT,
382 c_rec.TRANSFER_QTY,
383 c_rec.TRANSFER_UOM, /*ds change change end */
384 MSC_CL_COLLECTION.v_current_user,
385 MSC_CL_COLLECTION.v_current_date,
386 MSC_CL_COLLECTION.v_current_user,
387 MSC_CL_COLLECTION.v_current_date,
388 c_rec.ATTRIBUTE_CATEGORY,
389 c_rec.ATTRIBUTE1,
390 c_rec.ATTRIBUTE2,
391 c_Rec.ATTRIBUTE3,
392 c_rec.ATTRIBUTE4,
393 c_rec.ATTRIBUTE5,
394 c_rec.ATTRIBUTE6,
395 c_rec.ATTRIBUTE7,
396 c_rec.ATTRIBUTE8,
397 c_rec.ATTRIBUTE9,
398 c_rec.ATTRIBUTE10,
399 c_rec.ATTRIBUTE11,
400 c_rec.ATTRIBUTE12,
401 c_rec.ATTRIBUTE13,
402 c_rec.ATTRIBUTE14,
403 c_rec.ATTRIBUTE15,
404 c_rec.FROM_OP_SEQ_NUM,
405 c_rec.TO_OP_SEQ_NUM,
406 MSC_CL_COLLECTION.v_last_collection_id,
407 MSC_CL_COLLECTION.v_instance_id );
408
409
410 END IF; -- SQL%NOTFOUND
411
412 c_count:= c_count+1;
413
414 IF c_count> MSC_CL_COLLECTION.PBS THEN
415 COMMIT;
416 c_count:= 0;
417 END IF;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421
422 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
423
424 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
425 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
426 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_NETWORKS');
427 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_NETWORKS');
428 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
429
430 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
431 RAISE;
432
433 ELSE
434 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
435
436 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
437 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
438 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_NETWORKS');
439 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_NETWORKS');
440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
441
442 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
443 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_OP_SEQ_ID');
444 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.FROM_OP_SEQ_ID));
445 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
446
447 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
448 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_OP_SEQ_ID');
449 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.TO_OP_SEQ_ID));
450 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
451
452 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
453 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_OP_SEQ_NUM');
454 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.FROM_OP_SEQ_NUM));
455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
456
457 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
458 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_OP_SEQ_NUM');
459 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.TO_OP_SEQ_NUM));
460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
461
462 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
463 END IF;
464 END;
465
466 END LOOP;
467
468 COMMIT;
469 END LOAD_OPERATION_NETWORKS;
470
471 -- ===============================================================
472
473 PROCEDURE LOAD_ROUTING IS
474
475 CURSOR c4 IS
476 SELECT
477 msr.ROUTING_SEQUENCE_ID,
478 msr.ROUTING_TYPE,
479 REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),v_chr10,' '),v_chr13,' ') ROUTING_COMMENT,
480 msr.ALTERNATE_ROUTING_DESIGNATOR,
481 msr.PROJECT_ID,
482 msr.TASK_ID,
483 msr.LINE_ID,
484 msr.UOM_CODE,
485 nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,
486 msr.CTP_FLAG,
487 t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID, -- msr.ASSEMBLY_ITEM_ID,
488 msr.ORGANIZATION_ID,
489 msr.ROUTING_QUANTITY,
490 msr.DELETED_FLAG,
491 msr.SR_INSTANCE_ID,
492 msr.FIRST_OP_SEQ_NUM,
493 msr.LAST_OP_SEQ_NUM,
494 msr.common_routing_sequence_id,
495 msr.auto_step_qty_flag
496 FROM MSC_ITEM_ID_LID t1,
497 MSC_ST_ROUTINGS msr
498 WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id
499 AND t1.sr_instance_id= msr.sr_instance_id
500 AND msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
501 AND msr.DELETED_FLAG= MSC_UTIL.SYS_NO;
502
503 CURSOR c4_d IS
504 SELECT
505 msr.ROUTING_SEQUENCE_ID,
506 msr.SR_INSTANCE_ID
507 FROM MSC_ST_ROUTINGS msr
508 WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
509 AND msr.DELETED_FLAG= MSC_UTIL.SYS_YES;
510
511
512 c_count NUMBER:= 0;
513 lv_tbl VARCHAR2(30);
514 lv_sql_stmt VARCHAR2(5000);
515 lv_sql_ins VARCHAR2(6000);
516 lb_refresh_failed BOOLEAN:= FALSE;
517
518 BEGIN
519
520 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
521 -- We want to delete all ROUTING related data and get new stuff.
522
523 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
524
525 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
526 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
527 ELSE
528 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
529 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
530 END IF;
531
532 BEGIN
533 lv_sql_ins:=
534 'INSERT INTO MSC_ROUTINGS'
535 ||' (PLAN_ID,'
536 ||' ROUTING_SEQUENCE_ID,'
537 ||' ROUTING_TYPE,'
538 ||' ROUTING_COMMENT,'
539 ||' ALTERNATE_ROUTING_DESIGNATOR,'
540 ||' PROJECT_ID,'
541 ||' TASK_ID,'
542 ||' LINE_ID,'
543 ||' UOM_CODE,'
544 ||' CFM_ROUTING_FLAG,'
545 ||' CTP_FLAG,'
546 ||' ASSEMBLY_ITEM_ID,'
547 ||' ORGANIZATION_ID,'
548 ||' ROUTING_QUANTITY,'
549 ||' SR_INSTANCE_ID,'
550 ||' REFRESH_NUMBER,'
551 ||' LAST_UPDATE_DATE,'
552 ||' LAST_UPDATED_BY,'
553 ||' CREATION_DATE,'
554 ||' CREATED_BY,'
555 ||' FIRST_OP_SEQ_NUM,'
556 ||' LAST_OP_SEQ_NUM,'
557 ||' COMMON_ROUTING_SEQUENCE_ID,'
558 ||' AUTO_STEP_QTY_FLAG)'
559 ||' SELECT '
560 ||' -1,'
561 ||' msr.ROUTING_SEQUENCE_ID,'
562 ||' msr.ROUTING_TYPE,'
563 ||' REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),:v_chr10,'' ''),:v_chr13,'' '') ROUTING_COMMENT,'
564 ||' msr.ALTERNATE_ROUTING_DESIGNATOR,'
565 ||' msr.PROJECT_ID,'
566 ||' msr.TASK_ID,'
567 ||' msr.LINE_ID,'
568 ||' msr.UOM_CODE,'
569 ||' nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,'
570 ||' msr.CTP_FLAG,'
571 ||' t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,'
572 ||' msr.ORGANIZATION_ID,'
573 ||' msr.ROUTING_QUANTITY,'
574 ||' msr.SR_INSTANCE_ID,'
575 ||' :v_last_collection_id,'
576 ||' :v_current_date,'
577 ||' :v_current_user,'
578 ||' :v_current_date,'
579 ||' :v_current_user, '
580 ||' msr.FIRST_OP_SEQ_NUM,'
581 ||' msr.LAST_OP_SEQ_NUM,'
582 ||' msr.COMMON_ROUTING_SEQUENCE_ID,'
583 ||' msr.AUTO_STEP_QTY_FLAG '
584 ||' FROM MSC_ITEM_ID_LID t1,'
585 ||' MSC_ST_ROUTINGS msr'
586 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id'
587 ||' AND t1.sr_instance_id= msr.sr_instance_id'
588 ||' AND msr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
589 ||' AND msr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
590
591 EXECUTE IMMEDIATE lv_sql_ins
592 USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
593
594 COMMIT;
595 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routings loaded');
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
600
601 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
602 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
603 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
604 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
605 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
606
607 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
608 RAISE;
609
610 ELSE
611 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
612 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
613 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
614 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
616
617 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
618
619 --If Direct path load results in warning then the processing has to be
620 --switched back to row by row processing. This will help to identify the
621 --erroneous record and will also help in processing the rest of the records.
622 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routings');
623 lb_refresh_failed := TRUE;
624 END IF;
625 END;
626
627 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
628
629 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
630
631 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
632
633 FOR c_rec IN c4_d LOOP
634
635 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
636
637 DELETE MSC_ROUTINGS
638 WHERE PLAN_ID= -1
639 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
640 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
641
642 END LOOP;
643
644 END IF;
645
646
647 c_count:= 0;
648
649 FOR c_rec IN c4 LOOP
650
651 BEGIN
652
653 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
654
655 UPDATE MSC_ROUTINGS
656 SET
657 ROUTING_TYPE= c_rec.ROUTING_TYPE,
658 ROUTING_COMMENT= c_rec.ROUTING_COMMENT,
659 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
660 PROJECT_ID= c_rec.PROJECT_ID,
661 TASK_ID= c_rec.TASK_ID,
662 LINE_ID= c_rec.LINE_ID,
663 UOM_CODE= c_rec.UOM_CODE,
664 CFM_ROUTING_FLAG= c_rec.CFM_ROUTING_FLAG,
665 FIRST_OP_SEQ_NUM = c_rec.FIRST_OP_SEQ_NUM,
666 LAST_OP_SEQ_NUM = c_rec.LAST_OP_SEQ_NUM,
667 common_routing_sequence_id=c_rec.common_routing_sequence_id,
668 CTP_FLAG= c_rec.CTP_FLAG,
669 ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
670 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
671 ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
672 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
673 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
674 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
675 WHERE PLAN_ID= -1
676 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
677 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
678
679 END IF;
680
681 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
682
683 INSERT INTO MSC_ROUTINGS
684 ( PLAN_ID,
685 ROUTING_SEQUENCE_ID,
686 ROUTING_TYPE,
687 ROUTING_COMMENT,
688 ALTERNATE_ROUTING_DESIGNATOR,
689 PROJECT_ID,
690 TASK_ID,
691 LINE_ID,
692 UOM_CODE,
693 CFM_ROUTING_FLAG,
694 CTP_FLAG,
695 ASSEMBLY_ITEM_ID,
696 ORGANIZATION_ID,
697 ROUTING_QUANTITY,
698 SR_INSTANCE_ID,
699 REFRESH_NUMBER,
700 LAST_UPDATE_DATE,
701 LAST_UPDATED_BY,
702 CREATION_DATE,
703 CREATED_BY,
704 FIRST_OP_SEQ_NUM,
705 LAST_OP_SEQ_NUM,
706 common_routing_sequence_id,
707 auto_step_qty_flag)
708 VALUES
709 ( -1,
710 c_rec.ROUTING_SEQUENCE_ID,
711 c_rec.ROUTING_TYPE,
712 c_rec.ROUTING_COMMENT,
713 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
714 c_rec.PROJECT_ID,
715 c_rec.TASK_ID,
716 c_rec.LINE_ID,
717 c_rec.UOM_CODE,
718 c_rec.CFM_ROUTING_FLAG,
719 c_rec.CTP_FLAG,
720 c_rec.ASSEMBLY_ITEM_ID,
721 c_rec.ORGANIZATION_ID,
722 c_rec.ROUTING_QUANTITY,
723 c_rec.SR_INSTANCE_ID,
724 MSC_CL_COLLECTION.v_last_collection_id,
725 MSC_CL_COLLECTION.v_current_date,
726 MSC_CL_COLLECTION.v_current_user,
727 MSC_CL_COLLECTION.v_current_date,
728 MSC_CL_COLLECTION.v_current_user,
729 c_rec.first_op_seq_num,
730 c_rec.last_op_seq_num,
731 c_rec.common_routing_sequence_id,
732 c_rec.auto_step_qty_flag);
733
734 END IF; -- SQL%NOTFOUND
735
736 c_count:= c_count+1;
737
738 IF c_count> MSC_CL_COLLECTION.PBS THEN
739 COMMIT;
740 c_count:= 0;
741 END IF;
742
743 EXCEPTION
744
745 WHEN OTHERS THEN
746
747 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
748
749 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
750 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
751 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
752 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
753 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
754
755 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
756 RAISE;
757
758 ELSE
759 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
760
761 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
762 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
763 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
764 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
765 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
766
767 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
768 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
769 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(c_rec.ASSEMBLY_ITEM_ID));
770 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
771
772 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
773 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
774 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
775 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
776
777 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
778 END IF;
779 END;
780
781 END LOOP;
782 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
783
784 COMMIT;
785
786 END LOAD_ROUTING;
787
788 --==================================================================
789
790 PROCEDURE LOAD_OPERATION_COMPONENTS IS
791
792 CURSOR c9 IS
793 SELECT
794 ORGANIZATION_ID,
795 OPERATION_SEQUENCE_ID,
796 COMPONENT_SEQUENCE_ID,
797 BILL_SEQUENCE_ID,
798 ROUTING_SEQUENCE_ID,
799 SR_INSTANCE_ID
800 FROM MSC_ST_OPERATION_COMPONENTS msoc
801 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
802 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
803
804 CURSOR c9_d IS
805 SELECT
806 OPERATION_SEQUENCE_ID,
807 COMPONENT_SEQUENCE_ID,
808 BILL_SEQUENCE_ID,
809 ROUTING_SEQUENCE_ID,
810 SR_INSTANCE_ID,
811 ORGANIZATION_ID
812 FROM MSC_ST_OPERATION_COMPONENTS msoc
813 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
814 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_YES
815 UNION ALL
816 SELECT DISTINCT
817 TO_NUMBER(NULL),
818 TO_NUMBER(NULL),
819 moc.BILL_SEQUENCE_ID,
820 moc.ROUTING_SEQUENCE_ID,
821 moc.SR_INSTANCE_ID,
822 moc.ORGANIZATION_ID
823 FROM MSC_OPERATION_COMPONENTS moc,
824 MSC_ST_OPERATION_COMPONENTS msoc
825 WHERE msoc.Bill_Sequence_ID= moc.Bill_Sequence_ID
826 AND msoc.Routing_Sequence_ID <> moc.Routing_Sequence_ID
827 AND msoc.Organization_ID= moc.Organization_ID
828 AND msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
829 AND moc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
830 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO
831 AND moc.PLAN_ID= -1;
832
833
834 c_count NUMBER:= 0;
835 lv_tbl VARCHAR2(30);
836 lv_sql_stmt VARCHAR2(5000);
837 lv_sql_stmt1 VARCHAR2(5000);
838 lb_refresh_failed Boolean:= FALSE;
839 lv_delete_flag BOOLEAN:= FALSE;
840
841 BEGIN
842
843 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
844 -- We want to delete all BOM related data and get new stuff.
845
846 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
847
848 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
849 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
850 ELSE
851 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
852 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
853 END IF;
854 BEGIN
855 INSERT /*+ append */
856 INTO MSC_OPERATION_COMPONENTS
857 (PLAN_ID,
858 ORGANIZATION_ID,
859 OPERATION_SEQUENCE_ID,
860 COMPONENT_SEQUENCE_ID,
861 SR_INSTANCE_ID,
862 BILL_SEQUENCE_ID,
863 ROUTING_SEQUENCE_ID,
864 REFRESH_NUMBER,
865 LAST_UPDATE_DATE,
866 LAST_UPDATED_BY,
867 CREATION_DATE,
868 CREATED_BY)
869 SELECT
870 -1,
871 ORGANIZATION_ID,
872 OPERATION_SEQUENCE_ID,
873 COMPONENT_SEQUENCE_ID,
874 SR_INSTANCE_ID,
875 BILL_SEQUENCE_ID,
876 ROUTING_SEQUENCE_ID,
877 MSC_CL_COLLECTION.v_last_collection_id,
878 MSC_CL_COLLECTION.v_current_date,
879 MSC_CL_COLLECTION.v_current_user,
880 MSC_CL_COLLECTION.v_current_date,
881 MSC_CL_COLLECTION.v_current_user
882 FROM MSC_ST_OPERATION_COMPONENTS msoc
883 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
884 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
885
886 COMMIT;
887 EXCEPTION
888 WHEN OTHERS THEN
889
890 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
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_OPERATION_COMPONENTS');
895 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
896 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
897
898 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
899 RAISE;
900 ELSE
901 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
902 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
903 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
904 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
905 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
906 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
907
908 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
909 lb_refresh_failed := TRUE;
910 END IF;
911 END;
912 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
913
914 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
915
916 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
917 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
918 FOR c_rec IN c9_d LOOP
919
920 BEGIN
921
922 ---5470477
923 lv_sql_stmt1 := ' DELETE MSC_OPERATION_COMPONENTS '
924 ||' WHERE PLAN_ID= -1 '
925 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
926
927 lv_delete_flag := FALSE;
928
929 IF (c_rec.ORGANIZATION_ID IS NOT NULL) THEN
930 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ORGANIZATION_ID= ' || c_rec.ORGANIZATION_ID ;
931 lv_delete_flag := FALSE;
932 END IF;
933
934 IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
935 lv_sql_stmt1 := lv_sql_stmt1 ||' AND BILL_SEQUENCE_ID= ' || c_rec.BILL_SEQUENCE_ID ;
936 lv_delete_flag := FALSE;
937 END IF;
938
939 IF (c_rec.ROUTING_SEQUENCE_ID IS NOT NULL) THEN
940 lv_sql_stmt1 := lv_sql_stmt1 ||' AND ROUTING_SEQUENCE_ID= ' || c_rec.ROUTING_SEQUENCE_ID ;
941 lv_delete_flag := FALSE;
942 END IF;
943
944 IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
945 lv_sql_stmt1 := lv_sql_stmt1 ||' AND COMPONENT_SEQUENCE_ID= ' || c_rec.COMPONENT_SEQUENCE_ID ;
946 lv_delete_flag := FALSE;
947 END IF;
948
949 IF (c_rec.OPERATION_SEQUENCE_ID IS NOT NULL) THEN
950 lv_sql_stmt1 := lv_sql_stmt1 ||' AND OPERATION_SEQUENCE_ID= ' || c_rec.OPERATION_SEQUENCE_ID ;
951 lv_delete_flag := FALSE;
952 END IF;
953 if (lv_delete_flag = FALSE) then
954 EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID;
955 end if;
956 /* bug 988700 fix : change UPDATE to DELETE */
957 /*
958 DELETE MSC_OPERATION_COMPONENTS
959 WHERE PLAN_ID= -1
960 AND BILL_SEQUENCE_ID= NVL( c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
961 AND ROUTING_SEQUENCE_ID= NVL( c_rec.ROUTING_SEQUENCE_ID, ROUTING_SEQUENCE_ID)
962 AND COMPONENT_SEQUENCE_ID= NVL( c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
963 AND OPERATION_SEQUENCE_ID= NVL( c_rec.OPERATION_SEQUENCE_ID, OPERATION_SEQUENCE_ID)
964 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
965 */
966 EXCEPTION
967 WHEN OTHERS THEN
968 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
969 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
970 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
971 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
972 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
973
974 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
975 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
976 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
977 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
978
979 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
980 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
981 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
982 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
983
984 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
985 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
986 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
987 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
988
989 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
990 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
991 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
992 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
993
994 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
995
996 END;
997
998 END LOOP;
999
1000 END IF;
1001
1002
1003 c_count:= 0;
1004
1005 FOR c_rec IN c9 LOOP
1006
1007 BEGIN
1008
1009 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1010
1011 UPDATE MSC_OPERATION_COMPONENTS
1012 SET PLAN_ID= -1,
1013 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1014 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1015 WHERE PLAN_ID= -1
1016 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1017 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1018 AND BILL_SEQUENCE_ID = c_rec.BILL_SEQUENCE_ID
1019 AND ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1020 AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
1021 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID;
1022
1023 END IF;
1024
1025 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1026
1027 INSERT INTO MSC_OPERATION_COMPONENTS
1028 ( PLAN_ID,
1029 ORGANIZATION_ID,
1030 OPERATION_SEQUENCE_ID,
1031 COMPONENT_SEQUENCE_ID,
1032 SR_INSTANCE_ID,
1033 BILL_SEQUENCE_ID,
1034 ROUTING_SEQUENCE_ID,
1035 REFRESH_NUMBER,
1036 LAST_UPDATE_DATE,
1037 LAST_UPDATED_BY,
1038 CREATION_DATE,
1039 CREATED_BY)
1040 VALUES
1041 ( -1,
1042 c_rec.ORGANIZATION_ID,
1043 c_rec.OPERATION_SEQUENCE_ID,
1044 c_rec.COMPONENT_SEQUENCE_ID,
1045 c_rec.SR_INSTANCE_ID,
1046 c_rec.BILL_SEQUENCE_ID,
1047 c_rec.ROUTING_SEQUENCE_ID,
1048 MSC_CL_COLLECTION.v_last_collection_id,
1049 MSC_CL_COLLECTION.v_current_date,
1050 MSC_CL_COLLECTION.v_current_user,
1051 MSC_CL_COLLECTION.v_current_date,
1052 MSC_CL_COLLECTION.v_current_user);
1053
1054 END IF;
1055
1056 c_count:= c_count+1;
1057
1058 IF c_count> MSC_CL_COLLECTION.PBS THEN
1059 COMMIT;
1060 c_count:= 0;
1061 END IF;
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065
1066 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1067
1068 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1069 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1070 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
1071 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
1072 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1073
1074 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1075 RAISE;
1076
1077 ELSE
1078 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1079
1080 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1081 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1082 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
1083 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
1084 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1085
1086 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1087 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1088 FND_MESSAGE.SET_TOKEN('VALUE', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID, MSC_CL_COLLECTION.v_instance_id));
1089 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1090
1091 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1092 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1093 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1094 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1095
1096 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1097 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
1098 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
1099 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1100
1101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1102 END IF;
1103
1104 END;
1105
1106 END LOOP;
1107
1108 COMMIT;
1109 END IF;
1110
1111 END LOAD_OPERATION_COMPONENTS;
1112
1113 --=====================================================================
1114
1115 PROCEDURE LOAD_OP_RESOURCE_SEQ IS
1116
1117 CURSOR c7 IS
1118 SELECT
1119 msors.ROUTING_SEQUENCE_ID,
1120 msors.OPERATION_SEQUENCE_ID,
1121 msors.RESOURCE_SEQ_NUM,
1122 msors.SCHEDULE_FLAG,
1123 msors.RESOURCE_OFFSET_PERCENT,
1124 msors.DEPARTMENT_ID,
1125 msors.ACTIVITY_GROUP_ID,
1126 msors.SR_INSTANCE_ID,
1127 msors.ORGANIZATION_ID
1128 FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1129 WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1130 AND msors.DELETED_FLAG= MSC_UTIL.SYS_NO;
1131
1132 CURSOR c7_d IS
1133 SELECT
1134 msors.ROUTING_SEQUENCE_ID,
1135 msors.OPERATION_SEQUENCE_ID,
1136 msors.RESOURCE_SEQ_NUM,
1137 msors.SR_INSTANCE_ID
1138 FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1139 WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1140 AND msors.DELETED_FLAG= MSC_UTIL.SYS_YES;
1141
1142
1143 c_count NUMBER:= 0;
1144 lv_tbl VARCHAR2(30);
1145 lv_sql_stmt VARCHAR2(5000);
1146 lv_sql_ins VARCHAR2(6000);
1147 lb_refresh_failed Boolean:= FALSE;
1148
1149 lv_errbuf VARCHAR2(240);
1150 lv_retcode NUMBER;
1151
1152 BEGIN
1153
1154 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1155 lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1156 ELSE
1157 lv_tbl:= 'MSC_OPERATION_RESOURCE_SEQS';
1158 END IF;
1159
1160 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1161 -- We want to delete all BOM related data and get new stuff.
1162 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1163
1164 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1165 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1166 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1167 ELSE
1168 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1169 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1170 END IF;
1171 END IF;
1172
1173 BEGIN
1174
1175 lv_sql_ins:=
1176 ' INSERT INTO '||lv_tbl
1177 ||' ( PLAN_ID, '
1178 ||' ROUTING_SEQUENCE_ID, '
1179 ||' OPERATION_SEQUENCE_ID, '
1180 ||' RESOURCE_SEQ_NUM, '
1181 ||' SCHEDULE_FLAG, '
1182 ||' RESOURCE_OFFSET_PERCENT, '
1183 ||' DEPARTMENT_ID, '
1184 ||' ACTIVITY_GROUP_ID, '
1185 ||' SR_INSTANCE_ID, '
1186 ||' ORGANIZATION_ID, '
1187 ||' REFRESH_NUMBER, '
1188 ||' LAST_UPDATE_DATE, '
1189 ||' LAST_UPDATED_BY, '
1190 ||' CREATION_DATE, '
1191 ||' CREATED_BY) '
1192 ||'SELECT '
1193 ||' -1,'
1194 ||' msors.ROUTING_SEQUENCE_ID,'
1195 ||' msors.OPERATION_SEQUENCE_ID,'
1196 ||' msors.RESOURCE_SEQ_NUM,'
1197 ||' max(msors.SCHEDULE_FLAG),'
1198 ||' max(msors.RESOURCE_OFFSET_PERCENT),'
1199 ||' max(msors.DEPARTMENT_ID),'
1200 ||' max(msors.ACTIVITY_GROUP_ID),'
1201 ||' msors.SR_INSTANCE_ID,'
1202 ||' max(msors.ORGANIZATION_ID), '
1203 ||' :v_last_collection_id,'
1204 ||' :v_current_date,'
1205 ||' :v_current_user,'
1206 ||' :v_current_date,'
1207 ||' :v_current_user '
1208 ||' FROM MSC_ST_OPERATION_RESOURCE_SEQS msors'
1209 ||' WHERE msors.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1210 ||' AND msors.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1211 ||' GROUP BY '
1212 ||' msors.ROUTING_SEQUENCE_ID, '
1213 ||' msors.OPERATION_SEQUENCE_ID, '
1214 ||' msors.RESOURCE_SEQ_NUM,'
1215 ||' msors.SR_INSTANCE_ID';
1216
1217 EXECUTE IMMEDIATE lv_sql_ins
1218 USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1219
1220 COMMIT;
1221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resource seqs loaded');
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1226
1227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1228 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1229 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1230 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1231 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1232
1233 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1234 RAISE;
1235
1236 ELSE
1237 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1238 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1239 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1240 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1241 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1242
1243 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1244
1245 --If Direct path load results in warning then the processing has to be
1246 --switched back to row by row processing. This will help to identify the
1247 --erroneous record and will also help in processing the rest of the records.
1248 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resource seqs');
1249 lb_refresh_failed := TRUE;
1250 END IF;
1251 END;
1252
1253 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1254
1255 lv_sql_stmt :=
1256 ' INSERT INTO '||lv_tbl
1257 ||' ( PLAN_ID, '
1258 ||' ROUTING_SEQUENCE_ID, '
1259 ||' OPERATION_SEQUENCE_ID, '
1260 ||' RESOURCE_SEQ_NUM, '
1261 ||' SCHEDULE_FLAG, '
1262 ||' RESOURCE_OFFSET_PERCENT, '
1263 ||' DEPARTMENT_ID, '
1264 ||' ACTIVITY_GROUP_ID, '
1265 ||' SR_INSTANCE_ID, '
1266 ||' ORGANIZATION_ID, '
1267 ||' REFRESH_NUMBER, '
1268 ||' LAST_UPDATE_DATE, '
1269 ||' LAST_UPDATED_BY, '
1270 ||' CREATION_DATE, '
1271 ||' CREATED_BY) '
1272 ||' VALUES '
1273 ||' ( -1, '
1274 ||' :ROUTING_SEQUENCE_ID, '
1275 ||' :OPERATION_SEQUENCE_ID, '
1276 ||' :RESOURCE_SEQ_NUM, '
1277 ||' :SCHEDULE_FLAG, '
1278 ||' :RESOURCE_OFFSET_PERCENT, '
1279 ||' :DEPARTMENT_ID, '
1280 ||' :ACTIVITY_GROUP_ID, '
1281 ||' :SR_INSTANCE_ID, '
1282 ||' :ORGANIZATION_ID, '
1283 ||' :v_last_collection_id, '
1284 ||' :v_current_date, '
1285 ||' :v_current_user, '
1286 ||' :v_current_date, '
1287 ||' :v_current_user ) ';
1288
1289 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1290
1291 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1292
1293 FOR c_rec IN c7_d LOOP
1294
1295 IF c_rec.OPERATION_SEQUENCE_ID IS NOT NULL THEN
1296 DELETE MSC_OPERATION_RESOURCE_SEQS
1297 WHERE PLAN_ID= -1
1298 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1299 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1300 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
1301 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1302 ELSE
1303 DELETE MSC_OPERATION_RESOURCE_SEQS
1304 WHERE PLAN_ID= -1
1305 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1306 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1307 END IF;
1308
1309 END LOOP;
1310
1311 END IF;
1312
1313
1314 c_count:= 0;
1315
1316 FOR c_rec IN c7 LOOP
1317
1318 BEGIN
1319
1320 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1321
1322 UPDATE MSC_OPERATION_RESOURCE_SEQS
1323 SET
1324 SCHEDULE_FLAG= c_rec.SCHEDULE_FLAG,
1325 RESOURCE_OFFSET_PERCENT= c_rec.RESOURCE_OFFSET_PERCENT,
1326 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1327 ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
1328 ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
1329 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1330 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1331 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1332 WHERE PLAN_ID= -1
1333 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1334 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1335 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
1336 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1337
1338 END IF;
1339
1340 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1341
1342 EXECUTE IMMEDIATE lv_sql_stmt USING
1343 c_rec.ROUTING_SEQUENCE_ID,
1344 c_rec.OPERATION_SEQUENCE_ID,
1345 c_rec.RESOURCE_SEQ_NUM,
1346 c_rec.SCHEDULE_FLAG,
1347 c_rec.RESOURCE_OFFSET_PERCENT,
1348 c_rec.DEPARTMENT_ID,
1349 c_rec.ACTIVITY_GROUP_ID,
1350 c_rec.SR_INSTANCE_ID,
1351 c_rec.ORGANIZATION_ID,
1352 MSC_CL_COLLECTION.v_last_collection_id,
1353 MSC_CL_COLLECTION.v_current_date,
1354 MSC_CL_COLLECTION.v_current_user,
1355 MSC_CL_COLLECTION.v_current_date,
1356 MSC_CL_COLLECTION.v_current_user ;
1357
1358 END IF; -- SQL%NOTFOUND
1359
1360 c_count:= c_count+1;
1361
1362 IF c_count> MSC_CL_COLLECTION.PBS THEN
1363 COMMIT;
1364 c_count:= 0;
1365 END IF;
1366
1367
1368 EXCEPTION
1369
1370 WHEN DUP_VAL_ON_INDEX THEN
1371 NULL;
1372
1373 WHEN OTHERS THEN
1374
1375 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1376
1377 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1378 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1379 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1380 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1382
1383 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1384 RAISE;
1385
1386 ELSE
1387 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1388
1389 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1390 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1391 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1392 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1393 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1394
1395 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1396 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1397 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1398 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1399
1400 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1401 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1402 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1403 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1404
1405 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1406 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
1407 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_SEQ_NUM));
1408 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1409
1410 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1411
1412 END IF;
1413 END;
1414
1415 END LOOP;
1416 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1417
1418 COMMIT;
1419
1420 BEGIN
1421
1422 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1423
1424 lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1425
1426 lv_sql_stmt:=
1427 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1428 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_OPERATION_RESOURCE_SEQS'
1429 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1430 ||' AND plan_id = -1 '
1431 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1432
1433 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1434 EXECUTE IMMEDIATE lv_sql_stmt;
1435
1436 COMMIT;
1437
1438 END IF;
1439
1440 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1441 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1442 lv_retcode,
1443 'MSC_OPERATION_RESOURCE_SEQS',
1444 MSC_CL_COLLECTION.v_instance_code,
1445 MSC_UTIL.G_WARNING
1446 );
1447
1448 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1449 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1450 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1451 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1452 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1453 END IF;
1454
1455 END IF;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459
1460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1461 RAISE;
1462 END;
1463
1464 END LOAD_OP_RESOURCE_SEQ;
1465
1466 --======================================================================
1467 PROCEDURE LOAD_ROUTING_OPERATIONS IS
1468
1469 CURSOR c5 IS
1470 SELECT
1471 msro.ROUTING_SEQUENCE_ID,
1472 msro.OPERATION_SEQ_NUM,
1473 msro.OPERATION_SEQUENCE_ID,
1474 REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),v_chr10,'-'),v_chr13,'-') OPERATION_DESCRIPTION,
1475 msro.EFFECTIVITY_DATE,
1476 msro.DISABLE_DATE,
1477 msro.FROM_UNIT_NUMBER,
1478 msro.TO_UNIT_NUMBER,
1479 msro.OPTION_DEPENDENT_FLAG,
1480 msro.OPERATION_TYPE,
1481 msro.MINIMUM_TRANSFER_QUANTITY,
1482 msro.YIELD,
1483 msro.DEPARTMENT_ID,
1484 msro.DEPARTMENT_CODE,
1485 msro.OPERATION_LEAD_TIME_PERCENT,
1486 msro.CUMULATIVE_YIELD,
1487 msro.REVERSE_CUMULATIVE_YIELD,
1488 msro.NET_PLANNING_PERCENT,
1489 msro.SETUP_DURATION,
1490 msro.TEAR_DOWN_DURATION,
1491 msro.UOM_CODE,
1492 msro.STANDARD_OPERATION_CODE,
1493 msro.STEP_QUANTITY,
1494 msro.STEP_QUANTITY_UOM,
1495 msro.DELETED_FLAG,
1496 msro.SR_INSTANCE_ID,
1497 msro.ORGANIZATION_ID
1498 FROM MSC_ST_ROUTING_OPERATIONS msro
1499 WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1500 AND msro.DELETED_FLAG= MSC_UTIL.SYS_NO;
1501
1502 CURSOR c5_d IS
1503 SELECT
1504 msro.ROUTING_SEQUENCE_ID,
1505 msro.OPERATION_SEQUENCE_ID,
1506 msro.SR_INSTANCE_ID
1507 FROM MSC_ST_ROUTING_OPERATIONS msro
1508 WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1509 AND msro.DELETED_FLAG= MSC_UTIL.SYS_YES;
1510
1511
1512 c_count NUMBER:= 0;
1513 lv_tbl VARCHAR2(30);
1514 lv_sql_stmt VARCHAR2(5000);
1515 lv_sql_ins VARCHAR2(6000);
1516 lb_refresh_failed BOOLEAN:= FALSE;
1517
1518 lv_errbuf VARCHAR2(240);
1519 lv_retcode NUMBER;
1520
1521 BEGIN
1522
1523 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1524 lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1525 ELSE
1526 lv_tbl:= 'MSC_ROUTING_OPERATIONS';
1527 END IF;
1528
1529 IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh THEN
1530 -- We want to delete all BOM related data and get new stuff
1531 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1532
1533 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1534 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1535 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1536 ELSE
1537 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1538 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1539 END IF;
1540 END IF;
1541
1542 BEGIN
1543 lv_sql_ins:=
1544 ' INSERT INTO '||lv_tbl
1545 ||' ( PLAN_ID, '
1546 ||' ROUTING_SEQUENCE_ID, '
1547 ||' OPERATION_SEQ_NUM, '
1548 ||' OPERATION_SEQUENCE_ID, '
1549 ||' OPERATION_DESCRIPTION, '
1550 ||' EFFECTIVITY_DATE, '
1551 ||' DISABLE_DATE, '
1552 ||' FROM_UNIT_NUMBER, '
1553 ||' TO_UNIT_NUMBER, '
1554 ||' OPTION_DEPENDENT_FLAG, '
1555 ||' OPERATION_TYPE, '
1556 ||' MINIMUM_TRANSFER_QUANTITY, '
1557 ||' YIELD, '
1558 ||' DEPARTMENT_ID, '
1559 ||' DEPARTMENT_CODE, '
1560 ||' OPERATION_LEAD_TIME_PERCENT, '
1561 ||' CUMULATIVE_YIELD, '
1562 ||' REVERSE_CUMULATIVE_YIELD, '
1563 ||' NET_PLANNING_PERCENT, '
1564 ||' SETUP_DURATION, '
1565 ||' TEAR_DOWN_DURATION, '
1566 ||' UOM_CODE, '
1567 ||' STANDARD_OPERATION_CODE, '
1568 ||' STEP_QUANTITY, '
1569 ||' STEP_QUANTITY_UOM, '
1570 ||' SR_INSTANCE_ID, '
1571 ||' ORGANIZATION_ID, '
1572 ||' REFRESH_NUMBER, '
1573 ||' LAST_UPDATE_DATE, '
1574 ||' LAST_UPDATED_BY, '
1575 ||' CREATION_DATE, '
1576 ||' CREATED_BY) '
1577 ||' SELECT '
1578 ||' -1, '
1579 ||' msro.ROUTING_SEQUENCE_ID, '
1580 ||' msro.OPERATION_SEQ_NUM, '
1581 ||' msro.OPERATION_SEQUENCE_ID, '
1582 ||' REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),:v_chr10,''-''),:v_chr13,''-'') OPERATION_DESCRIPTION, '
1583 ||' msro.EFFECTIVITY_DATE, '
1584 ||' msro.DISABLE_DATE, '
1585 ||' msro.FROM_UNIT_NUMBER, '
1586 ||' msro.TO_UNIT_NUMBER, '
1587 ||' msro.OPTION_DEPENDENT_FLAG, '
1588 ||' msro.OPERATION_TYPE, '
1589 ||' msro.MINIMUM_TRANSFER_QUANTITY, '
1590 ||' msro.YIELD, '
1591 ||' msro.DEPARTMENT_ID, '
1592 ||' msro.DEPARTMENT_CODE, '
1593 ||' msro.OPERATION_LEAD_TIME_PERCENT, '
1594 ||' msro.CUMULATIVE_YIELD, '
1595 ||' msro.REVERSE_CUMULATIVE_YIELD, '
1596 ||' msro.NET_PLANNING_PERCENT, '
1597 ||' msro.SETUP_DURATION, '
1598 ||' msro.TEAR_DOWN_DURATION, '
1599 ||' msro.UOM_CODE, '
1600 ||' msro.STANDARD_OPERATION_CODE, '
1601 ||' msro.STEP_QUANTITY, '
1602 ||' msro.STEP_QUANTITY_UOM, '
1603 ||' msro.SR_INSTANCE_ID, '
1604 ||' msro.ORGANIZATION_ID, '
1605 ||' :v_last_collection_id, '
1606 ||' :v_current_date, '
1607 ||' :v_current_user, '
1608 ||' :v_current_date, '
1609 ||' :v_current_user '
1610 ||' FROM MSC_ST_ROUTING_OPERATIONS msro '
1611 ||' WHERE msro.SR_INSTANCE_ID= ' || MSC_CL_COLLECTION.v_instance_id
1612 ||' AND msro.DELETED_FLAG= ' || MSC_UTIL.SYS_NO;
1613
1614 EXECUTE IMMEDIATE lv_sql_ins
1615 USING v_chr10, v_chr13, MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
1616
1617 COMMIT;
1618 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routing operations loaded');
1619
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1623
1624 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1625 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1626 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1627 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1629
1630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1631 RAISE;
1632
1633 ELSE
1634 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1635 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1636 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1637 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1638 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1639
1640 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1641
1642 --If Direct path load results in warning then the processing has to be
1643 --switched back to row by row processing. This will help to identify the
1644 --erroneous record and will also help in processing the rest of the records.
1645 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routing operations');
1646 lb_refresh_failed := TRUE;
1647 END IF;
1648 END;
1649
1650 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1651
1652
1653 lv_sql_stmt:=
1654 ' INSERT INTO '||lv_tbl
1655 ||' ( PLAN_ID, '
1656 ||' ROUTING_SEQUENCE_ID, '
1657 ||' OPERATION_SEQ_NUM, '
1658 ||' OPERATION_SEQUENCE_ID, '
1659 ||' OPERATION_DESCRIPTION, '
1660 ||' EFFECTIVITY_DATE, '
1661 ||' DISABLE_DATE, '
1662 ||' FROM_UNIT_NUMBER, '
1663 ||' TO_UNIT_NUMBER, '
1664 ||' OPTION_DEPENDENT_FLAG, '
1665 ||' OPERATION_TYPE, '
1666 ||' MINIMUM_TRANSFER_QUANTITY, '
1667 ||' YIELD, '
1668 ||' DEPARTMENT_ID, '
1669 ||' DEPARTMENT_CODE, '
1670 ||' OPERATION_LEAD_TIME_PERCENT, '
1671 ||' CUMULATIVE_YIELD, '
1672 ||' REVERSE_CUMULATIVE_YIELD, '
1673 ||' NET_PLANNING_PERCENT, '
1674 ||' SETUP_DURATION, '
1675 ||' TEAR_DOWN_DURATION, '
1676 ||' UOM_CODE, '
1677 ||' STANDARD_OPERATION_CODE, '
1678 ||' STEP_QUANTITY, '
1679 ||' STEP_QUANTITY_UOM, '
1680 ||' SR_INSTANCE_ID, '
1681 ||' ORGANIZATION_ID, '
1682 ||' REFRESH_NUMBER, '
1683 ||' LAST_UPDATE_DATE, '
1684 ||' LAST_UPDATED_BY, '
1685 ||' CREATION_DATE, '
1686 ||' CREATED_BY) '
1687 ||' VALUES '
1688 ||' ( -1, '
1689 ||' :ROUTING_SEQUENCE_ID, '
1690 ||' :OPERATION_SEQ_NUM, '
1691 ||' :OPERATION_SEQUENCE_ID, '
1692 ||' :OPERATION_DESCRIPTION, '
1693 ||' :EFFECTIVITY_DATE, '
1694 ||' :DISABLE_DATE, '
1695 ||' :FROM_UNIT_NUMBER, '
1696 ||' :TO_UNIT_NUMBER, '
1697 ||' :OPTION_DEPENDENT_FLAG, '
1698 ||' :OPERATION_TYPE, '
1699 ||' :MINIMUM_TRANSFER_QUANTITY, '
1700 ||' :YIELD, '
1701 ||' :DEPARTMENT_ID, '
1702 ||' :DEPARTMENT_CODE, '
1703 ||' :OPERATION_LEAD_TIME_PERCENT, '
1704 ||' :CUMULATIVE_YIELD, '
1705 ||' :REVERSE_CUMULATIVE_YIELD, '
1706 ||' :NET_PLANNING_PERCENT, '
1707 ||' :SETUP_DURATION, '
1708 ||' :TEAR_DOWN_DURATION, '
1709 ||' :UOM_CODE, '
1710 ||' :STANDARD_OPERATION_CODE, '
1711 ||' :STEP_QUANTITY, '
1712 ||' :STEP_QUANTITY_UOM, '
1713 ||' :SR_INSTANCE_ID, '
1714 ||' :ORGANIZATION_ID, '
1715 ||' :v_last_collection_id, '
1716 ||' :v_current_date, '
1717 ||' :v_current_user, '
1718 ||' :v_current_date, '
1719 ||' :v_current_user ) ';
1720
1721 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1722
1723 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1724
1725 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1726 FOR c_rec IN c5_d LOOP
1727
1728 DELETE MSC_ROUTING_OPERATIONS
1729 WHERE PLAN_ID= -1
1730 AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
1731 AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
1732 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1733
1734 END LOOP;
1735 END IF;
1736
1737 c_count:= 0;
1738
1739 FOR c_rec IN c5 LOOP
1740
1741 BEGIN
1742
1743 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1744 UPDATE MSC_ROUTING_OPERATIONS
1745 SET
1746 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1747 OPERATION_DESCRIPTION= c_rec.OPERATION_DESCRIPTION,
1748 EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1749 DISABLE_DATE= c_rec.DISABLE_DATE,
1750 FROM_UNIT_NUMBER= c_rec.FROM_UNIT_NUMBER,
1751 TO_UNIT_NUMBER= c_rec.TO_UNIT_NUMBER,
1752 OPTION_DEPENDENT_FLAG= c_rec.OPTION_DEPENDENT_FLAG,
1753 OPERATION_TYPE= c_rec.OPERATION_TYPE,
1754 MINIMUM_TRANSFER_QUANTITY= c_rec.MINIMUM_TRANSFER_QUANTITY,
1755 YIELD= c_rec.YIELD,
1756 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1757 DEPARTMENT_CODE= c_rec.DEPARTMENT_CODE,
1758 OPERATION_LEAD_TIME_PERCENT= c_rec.OPERATION_LEAD_TIME_PERCENT,
1759 CUMULATIVE_YIELD= c_rec.CUMULATIVE_YIELD,
1760 REVERSE_CUMULATIVE_YIELD= c_rec.REVERSE_CUMULATIVE_YIELD,
1761 NET_PLANNING_PERCENT= c_rec.NET_PLANNING_PERCENT,
1762 SETUP_DURATION= c_rec.SETUP_DURATION,
1763 TEAR_DOWN_DURATION= c_rec.TEAR_DOWN_DURATION,
1764 UOM_CODE= c_rec.UOM_CODE,
1765 STANDARD_OPERATION_CODE= c_rec.STANDARD_OPERATION_CODE,
1766 STEP_QUANTITY= c_rec.STEP_QUANTITY,
1767 STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
1768 ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
1769 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1770 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1771 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1772 WHERE PLAN_ID= -1
1773 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1774 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1775 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1776 END IF;
1777
1778 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1779 EXECUTE IMMEDIATE lv_sql_stmt USING
1780 c_rec.ROUTING_SEQUENCE_ID,
1781 c_rec.OPERATION_SEQ_NUM,
1782 c_rec.OPERATION_SEQUENCE_ID,
1783 c_rec.OPERATION_DESCRIPTION,
1784 c_rec.EFFECTIVITY_DATE,
1785 c_rec.DISABLE_DATE,
1786 c_rec.FROM_UNIT_NUMBER,
1787 c_rec.TO_UNIT_NUMBER,
1788 c_rec.OPTION_DEPENDENT_FLAG,
1789 c_rec.OPERATION_TYPE,
1790 c_rec.MINIMUM_TRANSFER_QUANTITY,
1791 c_rec.YIELD,
1792 c_rec.DEPARTMENT_ID,
1793 c_rec.DEPARTMENT_CODE,
1794 c_rec.OPERATION_LEAD_TIME_PERCENT,
1795 c_rec.CUMULATIVE_YIELD,
1796 c_rec.REVERSE_CUMULATIVE_YIELD,
1797 c_rec.NET_PLANNING_PERCENT,
1798 c_rec.SETUP_DURATION,
1799 c_rec.TEAR_DOWN_DURATION,
1800 c_rec.UOM_CODE,
1801 c_rec.STANDARD_OPERATION_CODE,
1802 c_rec.STEP_QUANTITY,
1803 c_rec.STEP_QUANTITY_UOM,
1804 c_rec.SR_INSTANCE_ID,
1805 c_rec.ORGANIZATION_ID,
1806 MSC_CL_COLLECTION.v_last_collection_id,
1807 MSC_CL_COLLECTION.v_current_date,
1808 MSC_CL_COLLECTION.v_current_user,
1809 MSC_CL_COLLECTION.v_current_date,
1810 MSC_CL_COLLECTION.v_current_user ;
1811
1812 END IF; -- SQL%NOTFOUND
1813
1814 c_count:= c_count+1;
1815
1816 IF c_count> MSC_CL_COLLECTION.PBS THEN
1817 COMMIT;
1818 c_count:= 0;
1819 END IF;
1820
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824
1825 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1826
1827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1828 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1829 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1830 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1831 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1832
1833 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1834 RAISE;
1835
1836 ELSE
1837 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1838
1839 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1840 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1841 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1842 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1843 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1844
1845 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1846 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1847 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1848 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1849
1850 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1851 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQ_NUM');
1852 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQ_NUM));
1853 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1854
1855 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1856 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1857 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1858 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1859
1860 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1861 END IF;
1862 END;
1863
1864 END LOOP;
1865 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1866
1867 COMMIT;
1868
1869 BEGIN
1870
1871 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
1872
1873 lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1874
1875 lv_sql_stmt:=
1876 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
1877 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_ROUTING_OPERATIONS'
1878 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1879 ||' AND plan_id = -1 '
1880 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1881
1882 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1883 EXECUTE IMMEDIATE lv_sql_stmt;
1884
1885 COMMIT;
1886
1887 END IF;
1888
1889 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1890 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1891 lv_retcode,
1892 'MSC_ROUTING_OPERATIONS',
1893 MSC_CL_COLLECTION.v_instance_code,
1894 MSC_UTIL.G_WARNING
1895 );
1896
1897 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1898 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1899 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1900 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1901 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1902 END IF;
1903
1904 END IF;
1905
1906 EXCEPTION
1907 WHEN OTHERS THEN
1908
1909 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1910 RAISE;
1911 END;
1912
1913
1914 END LOAD_ROUTING_OPERATIONS;
1915
1916 --====================================================================
1917 PROCEDURE LOAD_OPERATION_RESOURCES IS
1918
1919
1920 CURSOR c6 IS
1921 SELECT
1922 msor.ROUTING_SEQUENCE_ID,
1923 msor.OPERATION_SEQUENCE_ID,
1924 msor.RESOURCE_SEQ_NUM,
1925 msor.RESOURCE_ID,
1926 msor.ALTERNATE_NUMBER,
1927 nvl(msor.PRINCIPAL_FLAG,1) PRINCIPAL_FLAG,
1928 msor.BASIS_TYPE,
1929 msor.RESOURCE_USAGE,
1930 msor.MAX_RESOURCE_UNITS,
1931 msor.RESOURCE_UNITS,
1932 msor.UOM_CODE,
1933 msor.RESOURCE_TYPE,
1934 msor.SR_INSTANCE_ID,
1935 msor.ORGANIZATION_ID,
1936 msor.SETUP_ID, /*ds change change start */
1937 msor.MINIMUM_CAPACITY,
1938 msor.MAXIMUM_CAPACITY ,
1939 msor.orig_resource_seq_num,
1940 msor.BREAKABLE_ACTIVITY_FLAG /*ds change change end */
1941 FROM MSC_ST_OPERATION_RESOURCES msor
1942 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1943 AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1944
1945 CURSOR c6_d IS
1946 SELECT
1947 msor.ROUTING_SEQUENCE_ID,
1948 msor.OPERATION_SEQUENCE_ID,
1949 msor.RESOURCE_SEQ_NUM,
1950 msor.RESOURCE_ID,
1951 msor.ALTERNATE_NUMBER,
1952 msor.SR_INSTANCE_ID
1953 FROM MSC_ST_OPERATION_RESOURCES msor
1954 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1955 AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES;
1956
1957 CURSOR c6_corrupt_data IS
1958 SELECT
1959 msor.ROUTING_SEQUENCE_ID,
1960 msor.RESOURCE_ID,
1961 msor.RESOURCE_SEQ_NUM
1962 FROM MSC_ST_OPERATION_RESOURCES msor
1963 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1964 AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO
1965 AND nvl(msor.RESOURCE_SEQ_NUM,0)=0
1966 AND msor.ALTERNATE_NUMBER > 0;
1967
1968 c_count NUMBER:= 0;
1969 lv_tbl VARCHAR2(30);
1970 lv_sql_stmt VARCHAR2(5000);
1971 lv_sql_stmt1 VARCHAR2(5000);
1972 lv_sql_ins VARCHAR2(6000);
1973 lb_refresh_failed BOOLEAN:= FALSE;
1974
1975 lv_errbuf VARCHAR2(240);
1976 lv_retcode NUMBER;
1977 lv_delete_flag BOOLEAN:= FALSE;
1978
1979 BEGIN
1980
1981 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1982 lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1983 ELSE
1984 lv_tbl:= 'MSC_OPERATION_RESOURCES';
1985 END IF;
1986
1987 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1988
1989 -- We want to delete all BOM related data and get new stuff.
1990 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1991
1992 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1993 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1994 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1995 ELSE
1996 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1997 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1998 END IF;
1999 END IF;
2000
2001 BEGIN
2002 lv_sql_ins :=
2003 ' INSERT INTO '||lv_tbl
2004 ||' ( PLAN_ID, '
2005 ||' ROUTING_SEQUENCE_ID, '
2006 ||' OPERATION_SEQUENCE_ID, '
2007 ||' RESOURCE_SEQ_NUM, '
2008 ||' RESOURCE_ID, '
2009 ||' ALTERNATE_NUMBER, '
2010 ||' PRINCIPAL_FLAG, '
2011 ||' BASIS_TYPE, '
2012 ||' RESOURCE_USAGE, '
2013 ||' MAX_RESOURCE_UNITS, '
2014 ||' RESOURCE_UNITS, '
2015 ||' UOM_CODE, '
2016 ||' RESOURCE_TYPE, '
2017 ||' SR_INSTANCE_ID, '
2018 ||' ORGANIZATION_ID, '
2019 ||' SETUP_ID, ' /*ds change change start */
2020 ||' MINIMUM_CAPACITY, '
2021 ||' MAXIMUM_CAPACITY, '
2022 ||' orig_resource_seq_num, '
2023 ||' BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2024 ||' REFRESH_NUMBER, '
2025 ||' LAST_UPDATE_DATE, '
2026 ||' LAST_UPDATED_BY, '
2027 ||' CREATION_DATE, '
2028 ||' CREATED_BY) '
2029 ||' SELECT '
2030 ||' -1, '
2031 ||' msor.ROUTING_SEQUENCE_ID, '
2032 ||' msor.OPERATION_SEQUENCE_ID, '
2033 ||' msor.RESOURCE_SEQ_NUM, '
2034 ||' msor.RESOURCE_ID, '
2035 ||' msor.ALTERNATE_NUMBER, '
2036 ||' nvl(msor.PRINCIPAL_FLAG,1), '
2037 ||' msor.BASIS_TYPE, '
2038 ||' msor.RESOURCE_USAGE, '
2039 ||' msor.MAX_RESOURCE_UNITS, '
2040 ||' msor.RESOURCE_UNITS, '
2041 ||' msor.UOM_CODE, '
2042 ||' msor.RESOURCE_TYPE, '
2043 ||' msor.SR_INSTANCE_ID, '
2044 ||' msor.ORGANIZATION_ID,'
2045 ||' msor.SETUP_ID, ' /*ds change change start */
2046 ||' msor.MINIMUM_CAPACITY, '
2047 ||' msor.MAXIMUM_CAPACITY, '
2048 ||' msor.ORIG_RESOURCE_SEQ_NUM, '
2049 ||' msor.BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2050 ||' :v_last_collection_id, '
2051 ||' :v_current_date, '
2052 ||' :v_current_user, '
2053 ||' :v_current_date, '
2054 ||' :v_current_user '
2055 ||' FROM MSC_ST_OPERATION_RESOURCES msor'
2056 ||' WHERE msor.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2057 ||' AND msor.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2058 ||' AND NOT(nvl(msor.RESOURCE_SEQ_NUM,0)=0 AND msor.ALTERNATE_NUMBER > 0)';
2059
2060 EXECUTE IMMEDIATE lv_sql_ins
2061 USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
2062
2063 COMMIT;
2064 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resources loaded');
2065
2066 FOR c_rec IN c6_corrupt_data LOOP
2067
2068 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2069
2070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2071
2072 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2073 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2074 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2075 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2076
2077 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2078 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
2079 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_SEQ_NUM));
2080 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2081
2082 FND_MESSAGE.SET_NAME('MSC','MSC_INVALID_RES_SEQ_NUM');
2083 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID', TO_CHAR(c_rec.ROUTING_SEQUENCE_ID));
2084 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', TO_CHAR(c_rec.RESOURCE_ID));
2085 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2086
2087 END LOOP;
2088
2089 EXCEPTION
2090 WHEN OTHERS THEN
2091 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2092
2093 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2094 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2095 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2096 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2097 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2098
2099 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2100 RAISE;
2101
2102 ELSE
2103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2104 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2105 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2106 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2107 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2108
2109 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2110
2111 --If Direct path load results in warning then the processing has to be
2112 --switched back to row by row processing. This will help to identify the
2113 --erroneous record and will also help in processing the rest of the records.
2114 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resources');
2115 lb_refresh_failed := TRUE;
2116 END IF;
2117
2118 END;
2119
2120 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
2121
2122 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2123
2124 lv_sql_stmt :=
2125 ' INSERT INTO '||lv_tbl
2126 ||' ( PLAN_ID, '
2127 ||' ROUTING_SEQUENCE_ID, '
2128 ||' OPERATION_SEQUENCE_ID, '
2129 ||' RESOURCE_SEQ_NUM, '
2130 ||' RESOURCE_ID, '
2131 ||' ALTERNATE_NUMBER, '
2132 ||' PRINCIPAL_FLAG, '
2133 ||' BASIS_TYPE, '
2134 ||' RESOURCE_USAGE, '
2135 ||' MAX_RESOURCE_UNITS, '
2136 ||' RESOURCE_UNITS, '
2137 ||' UOM_CODE, '
2138 ||' RESOURCE_TYPE, '
2139 ||' SR_INSTANCE_ID, '
2140 ||' ORGANIZATION_ID, '
2141 ||' SETUP_ID, ' /*ds change change start */
2142 ||' MINIMUM_CAPACITY, '
2143 ||' MAXIMUM_CAPACITY, '
2144 ||' orig_resource_seq_num, '
2145 ||' BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2146 ||' REFRESH_NUMBER, '
2147 ||' LAST_UPDATE_DATE, '
2148 ||' LAST_UPDATED_BY, '
2149 ||' CREATION_DATE, '
2150 ||' CREATED_BY) '
2151 ||' VALUES '
2152 ||' ( -1, '
2153 ||' :ROUTING_SEQUENCE_ID, '
2154 ||' :OPERATION_SEQUENCE_ID, '
2155 ||' :RESOURCE_SEQ_NUM, '
2156 ||' :RESOURCE_ID, '
2157 ||' :ALTERNATE_NUMBER, '
2158 ||' :PRINCIPAL_FLAG, '
2159 ||' :BASIS_TYPE, '
2160 ||' :RESOURCE_USAGE, '
2161 ||' :MAX_RESOURCE_UNITS, '
2162 ||' :RESOURCE_UNITS, '
2163 ||' :UOM_CODE, '
2164 ||' :RESOURCE_TYPE, '
2165 ||' :SR_INSTANCE_ID, '
2166 ||' :ORGANIZATION_ID,'
2167 ||' :SETUP_ID, ' /*ds change change start */
2168 ||' :MINIMUM_CAPACITY, '
2169 ||' :MAXIMUM_CAPACITY, '
2170 ||' :orig_resource_seq_num, '
2171 ||' :BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2172 ||' :v_last_collection_id, '
2173 ||' :v_current_date, '
2174 ||' :v_current_user, '
2175 ||' :v_current_date, '
2176 ||' :v_current_user ) ';
2177
2178
2179 -- set RESOURCE_USAGE to 0 to indicate a SOFT delete
2180 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2181 FOR c_rec IN c6_d LOOP
2182 ---5470477
2183 lv_delete_flag := FALSE;
2184 lv_sql_stmt1 := ' UPDATE MSC_OPERATION_RESOURCES '
2185 ||' SET RESOURCE_USAGE= 0, '
2186 ||' REFRESH_NUMBER= :v_last_collection_id, '
2187 ||' LAST_UPDATE_DATE= :v_current_date, '
2188 ||' LAST_UPDATED_BY= :v_current_user '
2189 ||' WHERE PLAN_ID= -1 '
2190 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
2191
2192 IF (c_rec.ROUTING_SEQUENCE_ID IS NOT NULL) THEN
2193 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ROUTING_SEQUENCE_ID= ' || c_rec.ROUTING_SEQUENCE_ID ;
2194 lv_delete_flag := TRUE;
2195 END IF;
2196
2197 IF (c_rec.OPERATION_SEQUENCE_ID IS NOT NULL) THEN
2198 lv_sql_stmt1 := lv_sql_stmt1 || ' AND OPERATION_SEQUENCE_ID= ' || c_rec.OPERATION_SEQUENCE_ID ;
2199 lv_delete_flag := TRUE;
2200 END IF;
2201
2202 IF (c_rec.RESOURCE_SEQ_NUM IS NOT NULL) THEN
2203 lv_sql_stmt1 := lv_sql_stmt1 || ' AND RESOURCE_SEQ_NUM= ' || c_rec.RESOURCE_SEQ_NUM ;
2204 lv_delete_flag := TRUE;
2205 END IF;
2206
2207 IF (c_rec.RESOURCE_ID IS NOT NULL) THEN
2208 lv_sql_stmt1 := lv_sql_stmt1 || ' AND RESOURCE_ID= ' || c_rec.RESOURCE_ID ;
2209 lv_delete_flag := TRUE;
2210 END IF;
2211
2212 IF (c_rec.ALTERNATE_NUMBER IS NOT NULL) THEN
2213 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ALTERNATE_NUMBER= ' || c_rec.ALTERNATE_NUMBER ;
2214 lv_delete_flag := TRUE;
2215 END IF;
2216
2217 BEGIN
2218 If (lv_delete_flag) then
2219 EXECUTE IMMEDIATE lv_sql_stmt1 USING MSC_CL_COLLECTION.v_last_collection_id, MSC_CL_COLLECTION.v_current_date, MSC_CL_COLLECTION.v_current_user, c_rec.SR_INSTANCE_ID ;
2220 end if;
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------');
2224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1);
2225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Routing Sequence Id: ' || c_rec.ROUTING_SEQUENCE_ID);
2226 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Operation Sequence Id: ' || c_rec.OPERATION_SEQUENCE_ID );
2227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Resource Seq Num: ' || c_rec.RESOURCE_SEQ_NUM);
2228 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Resource Id: ' || c_rec.RESOURCE_ID);
2229 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Alternate Number: ' || c_rec.ALTERNATE_NUMBER);
2230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------');
2231 END;
2232
2233 /*
2234 UPDATE MSC_OPERATION_RESOURCES
2235 SET RESOURCE_USAGE= 0,
2236 REFRESH_NUMBER= v_last_collection_id,
2237 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2238 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2239 WHERE PLAN_ID= -1
2240 AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
2241 AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
2242 AND RESOURCE_SEQ_NUM= NVL(c_rec.RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM)
2243 AND RESOURCE_ID= NVL(c_rec.RESOURCE_ID,RESOURCE_ID)
2244 AND ALTERNATE_NUMBER= NVL( c_rec.ALTERNATE_NUMBER,ALTERNATE_NUMBER)
2245 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2246 */
2247 END LOOP;
2248
2249 END IF;
2250
2251
2252 c_count:= 0;
2253
2254 FOR c_rec IN c6 LOOP
2255
2256 BEGIN
2257
2258 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2259
2260 UPDATE MSC_OPERATION_RESOURCES
2261 SET
2262 PRINCIPAL_FLAG= c_rec.PRINCIPAL_FLAG,
2263 BASIS_TYPE= c_rec.BASIS_TYPE,
2264 RESOURCE_USAGE= c_rec.RESOURCE_USAGE,
2265 MAX_RESOURCE_UNITS= c_rec.MAX_RESOURCE_UNITS,
2266 RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
2267 UOM_CODE= c_rec.UOM_CODE,
2268 RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2269 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2270 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2271 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2272 -- ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER, 10359759
2273 ORGANIZATION_ID= c_rec.ORGANIZATION_ID ,
2274 SETUP_ID= c_rec.SETUP_ID , /* ds change change start*/
2275 MINIMUM_CAPACITY = c_rec.MINIMUM_CAPACITY ,
2276 MAXIMUM_CAPACITY = c_rec.MAXIMUM_CAPACITY ,
2277 orig_resource_seq_num = c_rec.orig_resource_seq_num ,
2278 BREAKABLE_ACTIVITY_FLAG = c_rec.BREAKABLE_ACTIVITY_FLAG /* ds change change end */
2279 WHERE PLAN_ID= -1
2280 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
2281 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
2282 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
2283 AND RESOURCE_ID= c_rec.RESOURCE_ID
2284 AND ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER --10359759
2285 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2286
2287 END IF;
2288
2289 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2290 /* Start changes for 3586386*/
2291 IF nvl(c_rec.RESOURCE_SEQ_NUM,0)=0 AND c_rec.ALTERNATE_NUMBER > 0 THEN
2292
2293 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2294
2295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2296
2297 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2298 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2299 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2300 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2301
2302 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2303 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
2304 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_SEQ_NUM));
2305 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2306
2307 FND_MESSAGE.SET_NAME('MSC','MSC_INVALID_RES_SEQ_NUM');
2308 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID', TO_CHAR(c_rec.ROUTING_SEQUENCE_ID));
2309 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', TO_CHAR(c_rec.RESOURCE_ID));
2310 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2311
2312 /* End changes for 3586386*/
2313 ELSE
2314 EXECUTE IMMEDIATE lv_sql_stmt USING
2315 c_rec.ROUTING_SEQUENCE_ID,
2316 c_rec.OPERATION_SEQUENCE_ID,
2317 c_rec.RESOURCE_SEQ_NUM,
2318 c_rec.RESOURCE_ID,
2319 c_rec.ALTERNATE_NUMBER,
2320 c_rec.PRINCIPAL_FLAG,
2321 c_rec.BASIS_TYPE,
2322 c_rec.RESOURCE_USAGE,
2323 c_rec.MAX_RESOURCE_UNITS,
2324 c_rec.RESOURCE_UNITS,
2325 c_rec.UOM_CODE,
2326 c_rec.RESOURCE_TYPE,
2327 c_rec.SR_INSTANCE_ID,
2328 c_rec.ORGANIZATION_ID,
2329 c_rec.SETUP_ID, /* ds change change start*/
2330 c_rec.MINIMUM_CAPACITY,
2331 c_rec.MAXIMUM_CAPACITY,
2332 c_rec.orig_resource_seq_num,
2333 c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2334 MSC_CL_COLLECTION.v_last_collection_id,
2335 MSC_CL_COLLECTION.v_current_date,
2336 MSC_CL_COLLECTION.v_current_user,
2337 MSC_CL_COLLECTION.v_current_date,
2338 MSC_CL_COLLECTION.v_current_user ;
2339 END IF;
2340
2341 END IF; -- SQL%NOTFOUND
2342
2343 c_count:= c_count+1;
2344
2345 IF c_count> MSC_CL_COLLECTION.PBS THEN
2346 COMMIT;
2347 c_count:= 0;
2348 END IF;
2349
2350 EXCEPTION
2351 WHEN OTHERS THEN
2352
2353 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2354
2355 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2356 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2357 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2358 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2359 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2360
2361 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2362 RAISE;
2363
2364 ELSE
2365 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2366
2367 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2368 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2369 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2370 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2372
2373 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2374 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
2375 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
2376 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2377
2378 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2379 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
2380 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
2381 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2382
2383 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2384 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
2385 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_SEQ_NUM));
2386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2387
2388 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2389 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2390 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_ID));
2391 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2392
2393 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2394 END IF;
2395 END;
2396
2397 END LOOP;
2398 END IF;
2399
2400 COMMIT;
2401
2402 BEGIN
2403
2404 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS ) AND (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2405
2406 lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
2407
2408 lv_sql_stmt:=
2409 'INSERT INTO '||MSC_UTIL.G_MSC_SCHEMA||'.'||lv_tbl
2410 ||' SELECT * from '||MSC_UTIL.G_MSC_SCHEMA||'.MSC_OPERATION_RESOURCES'
2411 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2412 ||' AND plan_id = -1 '
2413 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2414
2415 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2416 EXECUTE IMMEDIATE lv_sql_stmt;
2417
2418 COMMIT;
2419
2420 END IF;
2421
2422 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2423 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2424 lv_retcode,
2425 'MSC_OPERATION_RESOURCES',
2426 MSC_CL_COLLECTION.v_instance_code,
2427 MSC_UTIL.G_WARNING
2428 );
2429
2430 IF lv_retcode = MSC_UTIL.G_ERROR THEN
2431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2432 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2433 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2434 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2435 END IF;
2436
2437 END IF;
2438
2439 EXCEPTION
2440 WHEN OTHERS THEN
2441
2442 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2443 RAISE;
2444 END;
2445
2446 END LOAD_OPERATION_RESOURCES;
2447 /* Bug 9194726 starts */
2448 PROCEDURE GET_START_END_OP
2449 IS
2450
2451 cursor c1 IS
2452 select distinct mr.ROUTING_SEQUENCE_ID,trunc(mn.effectivity_date) effectivity_date
2453 from MSC_ROUTINGS mr,MSC_ROUTING_OPERATIONS mn
2454 where
2455 mr.sr_instance_id=mn.sr_instance_id
2456 and mr.routing_sequence_id= mn.routing_sequence_id
2457 and mr.CFM_ROUTING_FLAG=3
2458 and mn.plan_id = -1
2459 and mr.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2460 and (mr.first_op_seq_num is null or mr.last_op_seq_num is null);
2461
2462 BEGIN
2463
2464 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'.. In procedure GET_START_END_OP ..');
2465 FOR c_rec IN c1 LOOP
2466
2467 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2468 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
2469 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ROUTING_SEQUENCE_ID);
2470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2471 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2472 FND_MESSAGE.SET_TOKEN('COLUMN', 'effectivity_date');
2473 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.effectivity_date);
2474 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2475
2476 UPDATE MSC_ROUTINGS mr
2477 set mr.first_op_seq_num =get_start_op(MSC_CL_COLLECTION.v_instance_id,c_rec.ROUTING_SEQUENCE_ID,c_rec.effectivity_date),
2478 mr.last_op_seq_num =get_last_op(MSC_CL_COLLECTION.v_instance_id,c_rec.ROUTING_SEQUENCE_ID,c_rec.effectivity_date)
2479 where mr.sr_instance_id=MSC_CL_COLLECTION.v_instance_id
2480 and mr.ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
2481 and mr.CFM_ROUTING_FLAG=3
2482 and mr.plan_id=-1;
2483
2484 commit;
2485
2486
2487 END LOOP;
2488
2489
2490
2491 EXCEPTION
2492 WHEN OTHERS THEN
2493 ROLLBACK;
2494
2495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2496 END GET_START_END_OP;
2497
2498
2499 PROCEDURE GET_START_END_OP_PARTIAL
2500 IS
2501
2502 cursor c1 IS
2503 select distinct mr.ROUTING_SEQUENCE_ID,trunc(mn.effectivity_date) effectivity_date
2504 from MSC_ST_ROUTINGS mr,MSC_ROUTING_OPERATIONS mn
2505 where
2506 mr.sr_instance_id=mn.sr_instance_id
2507 and mr.routing_sequence_id= mn.routing_sequence_id
2508 and mr.CFM_ROUTING_FLAG=3
2509 and mn.plan_id = -1
2510 and mr.deleted_flag = MSC_UTIL.SYS_NO
2511 and mr.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2512 and (mr.first_op_seq_num is null or mr.last_op_seq_num is null);
2513
2514 BEGIN
2515
2516 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'.. In procedure GET_START_END_OP ..');
2517 FOR c_rec IN c1 LOOP
2518
2519 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2520 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
2521 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ROUTING_SEQUENCE_ID);
2522 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2523
2524 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2525 FND_MESSAGE.SET_TOKEN('COLUMN', 'effectivity_date');
2526 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.effectivity_date);
2527 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2528
2529 UPDATE MSC_ROUTINGS mr
2530 set mr.first_op_seq_num =get_start_op(MSC_CL_COLLECTION.v_instance_id,c_rec.ROUTING_SEQUENCE_ID,c_rec.effectivity_date),
2531 mr.last_op_seq_num =get_last_op(MSC_CL_COLLECTION.v_instance_id,c_rec.ROUTING_SEQUENCE_ID,c_rec.effectivity_date)
2532 where mr.sr_instance_id=MSC_CL_COLLECTION.v_instance_id
2533 and mr.ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
2534 and mr.CFM_ROUTING_FLAG=3
2535 and mr.plan_id=-1;
2536
2537 commit;
2538
2539
2540 END LOOP;
2541
2542
2543
2544 EXCEPTION
2545 WHEN OTHERS THEN
2546 ROLLBACK;
2547 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2548 END GET_START_END_OP_PARTIAL;
2549
2550
2551 FUNCTION get_start_op(v_instance_id in number, v_routing_sequence_id in number, v_routing_effdate in date)
2552 return number
2553 IS
2554 l_from_op_seqid number;
2555 l_from_op_seqnum number;
2556 l_effdate DATE;
2557 l_disabdate DATE;
2558
2559 BEGIN
2560
2561 SELECT unique mon.FROM_OP_SEQ_ID, trunc(mro.EFFECTIVITY_DATE), trunc(nvl(mro.DISABLE_DATE,sysdate + 2))
2562 INTO l_from_op_seqid, l_effdate, l_disabdate
2563 FROM MSC_OPERATION_NETWORKS mon,
2564 MSC_ROUTING_OPERATIONS mro
2565 WHERE mon.PLAN_ID= -1
2566 AND mon.SR_INSTANCE_ID = v_instance_id
2567 AND mon.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2568 AND NOT EXISTS (SELECT 1 FROM MSC_OPERATION_NETWORKS mon2
2569 WHERE mon2.PLAN_ID= -1
2570 AND mon2.SR_INSTANCE_ID = v_instance_id
2571 AND mon2.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2572 AND mon2.TO_OP_SEQ_ID = mon.FROM_OP_SEQ_ID)
2573 AND mro.PLAN_ID= -1
2574 AND mro.SR_INSTANCE_ID = v_instance_id
2575 AND mro.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2576 AND mro.OPERATION_SEQUENCE_ID = mon.FROM_OP_SEQ_ID;
2577
2578 /* IF v_routing_effdate BETWEEN l_effdate AND l_disabdate THEN */ --6792068
2579
2580 select distinct FROM_OP_SEQ_NUM
2581 into l_from_op_seqnum
2582 from MSC_OPERATION_NETWORKS
2583 where from_op_seq_id = l_from_op_seqid
2584 and routing_sequence_id= v_routing_sequence_id
2585 and sr_instance_id = v_instance_id
2586 and plan_id= -1;
2587
2588 RETURN l_from_op_seqnum;
2589
2590 /* END IF; */ --6792068
2591
2592 RETURN NULL;
2593
2594 EXCEPTION
2595 WHEN OTHERS THEN
2596 return null;
2597 END;
2598
2599
2600 FUNCTION get_last_op(v_instance_id in number, v_routing_sequence_id in number, v_routing_effdate in date)
2601 return number
2602 IS
2603 l_to_op_seqid number;
2604 l_to_op_seqnum number;
2605 l_effdate DATE;
2606 l_disabdate DATE;
2607
2608 BEGIN
2609
2610 SELECT unique mon.TO_OP_SEQ_ID, trunc(mro.EFFECTIVITY_DATE), trunc(nvl(mro.DISABLE_DATE,sysdate + 2))
2611 INTO l_to_op_seqid, l_effdate, l_disabdate
2612 FROM MSC_OPERATION_NETWORKS mon,
2613 MSC_ROUTING_OPERATIONS mro
2614 WHERE mon.PLAN_ID= -1
2615 AND mon.SR_INSTANCE_ID = v_instance_id
2616 AND mon.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2617 AND NOT EXISTS (SELECT 1 FROM MSC_OPERATION_NETWORKS mon2
2618 WHERE mon2.PLAN_ID= -1
2619 AND mon2.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2620 AND mon2.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2621 AND mon.TO_OP_SEQ_ID = mon2.FROM_OP_SEQ_ID)
2622 AND mro.PLAN_ID= -1
2623 AND mro.SR_INSTANCE_ID = v_instance_id
2624 AND mro.ROUTING_SEQUENCE_ID = v_routing_sequence_id
2625 AND mro.OPERATION_SEQUENCE_ID = mon.TO_OP_SEQ_ID;
2626
2627 /* IF v_routing_effdate BETWEEN l_effdate AND l_disabdate THEN */ --6793068
2628
2629 select distinct TO_OP_SEQ_NUM
2630 into l_to_op_seqnum
2631 from MSC_OPERATION_NETWORKS
2632 where to_op_seq_id = l_to_op_seqid
2633 and routing_sequence_id= v_routing_sequence_id
2634 and sr_instance_id = v_instance_id
2635 and plan_id= -1;
2636
2637 RETURN l_to_op_seqnum;
2638
2639 /* END IF; */ --6792068
2640
2641 RETURN NULL;
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645 return null;
2646 END;
2647
2648
2649
2650 /* Bug 9194726 ends */
2651 ---============================================================
2652
2653
2654 END MSC_CL_ROUTING_ODS_LOAD;