[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.3 2007/06/11 09:49:18 sbyerram noship $ */
3
4 v_sub_str VARCHAR2(4000):=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
314 END IF;
315
316 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
317
318 insert into MSC_OPERATION_NETWORKS
319 ( FROM_OP_SEQ_ID,
320 TO_OP_SEQ_ID,
321 ROUTING_SEQUENCE_ID,
322 TRANSITION_TYPE,
323 PLANNING_PCT,
324 CUMMULATIVE_PCT,
325 EFFECTIVITY_DATE,
326 DISABLE_DATE,
327 PLAN_ID,
328 TO_ROUTING_SEQUENCE_ID, /*ds change change start */
329 FROM_ITEM_ID,
330 ORGANIZATION_ID,
331 MINIMUM_TRANSFER_QTY,
332 MINIMUM_TIME_OFFSET,
333 MAXIMUM_TIME_OFFSET,
334 DEPENDENCY_TYPE,
335 APPLY_TO_CHARGES,
336 TRANSFER_PCT,
337 TRANSFER_QTY,
338 TRANSFER_UOM, /*ds change change end */
339 CREATED_BY,
340 CREATION_DATE,
341 LAST_UPDATED_BY,
342 LAST_UPDATE_DATE,
343 ATTRIBUTE_CATEGORY,
344 ATTRIBUTE1,
345 ATTRIBUTE2,
346 ATTRIBUTE3,
347 ATTRIBUTE4,
348 ATTRIBUTE5,
349 ATTRIBUTE6,
350 ATTRIBUTE7,
351 ATTRIBUTE8,
352 ATTRIBUTE9,
353 ATTRIBUTE10,
354 ATTRIBUTE11,
355 ATTRIBUTE12,
356 ATTRIBUTE13,
357 ATTRIBUTE14,
358 ATTRIBUTE15,
359 FROM_OP_SEQ_NUM,
360 TO_OP_SEQ_NUM,
361 REFRESH_NUMBER,
362 SR_INSTANCE_ID)
363 values( c_rec.FROM_OP_SEQ_ID,
364 c_rec.TO_OP_SEQ_ID,
365 c_rec.ROUTING_SEQUENCE_ID,
366 c_rec.TRANSITION_TYPE,
367 c_rec.PLANNING_PCT,
368 c_rec.CUMMULATIVE_PCT,
369 c_rec.EFECTIVITY_DATE,
370 c_rec.DISABLE_DATE,
371 -1,
372 c_rec.TO_ROUTING_SEQUENCE_ID, /*ds change change start */
373 c_rec.FROM_ITEM_ID ,
374 c_rec.ORGANIZATION_ID,
375 c_rec.MINIMUM_TRANSFER_QTY,
376 c_rec.MINIMUM_TIME_OFFSET,
377 c_rec.MAXIMUM_TIME_OFFSET,
378 c_rec.DEPENDENCY_TYPE,
379 c_rec.APPLY_TO_CHARGES,
380 c_rec.TRANSFER_PCT,
381 c_rec.TRANSFER_QTY,
382 c_rec.TRANSFER_UOM, /*ds change change end */
383 MSC_CL_COLLECTION.v_current_user,
384 MSC_CL_COLLECTION.v_current_date,
385 MSC_CL_COLLECTION.v_current_user,
386 MSC_CL_COLLECTION.v_current_date,
387 c_rec.ATTRIBUTE_CATEGORY,
388 c_rec.ATTRIBUTE1,
389 c_rec.ATTRIBUTE2,
390 c_Rec.ATTRIBUTE3,
391 c_rec.ATTRIBUTE4,
392 c_rec.ATTRIBUTE5,
393 c_rec.ATTRIBUTE6,
394 c_rec.ATTRIBUTE7,
395 c_rec.ATTRIBUTE8,
396 c_rec.ATTRIBUTE9,
397 c_rec.ATTRIBUTE10,
398 c_rec.ATTRIBUTE11,
399 c_rec.ATTRIBUTE12,
400 c_rec.ATTRIBUTE13,
401 c_rec.ATTRIBUTE14,
402 c_rec.ATTRIBUTE15,
403 c_rec.FROM_OP_SEQ_NUM,
404 c_rec.TO_OP_SEQ_NUM,
405 MSC_CL_COLLECTION.v_last_collection_id,
406 MSC_CL_COLLECTION.v_instance_id );
407
408
409 END IF; -- SQL%NOTFOUND
410
411 c_count:= c_count+1;
412
413 IF c_count> MSC_CL_COLLECTION.PBS THEN
414 COMMIT;
415 c_count:= 0;
416 END IF;
417
418 EXCEPTION
419 WHEN OTHERS THEN
420
421 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
422
423 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
424 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
425 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_NETWORKS');
426 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_NETWORKS');
427 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
428
429 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
430 RAISE;
431
432 ELSE
433 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
434
435 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
436 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
437 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_NETWORKS');
438 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_NETWORKS');
439 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
440
441 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
442 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_OP_SEQ_ID');
443 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.FROM_OP_SEQ_ID));
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
445
446 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
447 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_OP_SEQ_ID');
448 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.TO_OP_SEQ_ID));
449 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
450
451 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
452 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_OP_SEQ_NUM');
453 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.FROM_OP_SEQ_NUM));
454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
455
456 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
457 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_OP_SEQ_NUM');
458 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.TO_OP_SEQ_NUM));
459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
460
461 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
462 END IF;
463 END;
464
465 END LOOP;
466
467 COMMIT;
468 END LOAD_OPERATION_NETWORKS;
469
470 -- ===============================================================
471
472 PROCEDURE LOAD_ROUTING IS
473
474 CURSOR c4 IS
475 SELECT
476 msr.ROUTING_SEQUENCE_ID,
477 msr.ROUTING_TYPE,
478 REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),v_chr10,' '),v_chr13,' ') ROUTING_COMMENT,
479 msr.ALTERNATE_ROUTING_DESIGNATOR,
480 msr.PROJECT_ID,
481 msr.TASK_ID,
482 msr.LINE_ID,
483 msr.UOM_CODE,
484 nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,
485 msr.CTP_FLAG,
486 t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID, -- msr.ASSEMBLY_ITEM_ID,
487 msr.ORGANIZATION_ID,
488 msr.ROUTING_QUANTITY,
489 msr.DELETED_FLAG,
490 msr.SR_INSTANCE_ID,
491 msr.FIRST_OP_SEQ_NUM,
492 msr.LAST_OP_SEQ_NUM,
493 msr.common_routing_sequence_id,
494 msr.auto_step_qty_flag
495 FROM MSC_ITEM_ID_LID t1,
496 MSC_ST_ROUTINGS msr
497 WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id
498 AND t1.sr_instance_id= msr.sr_instance_id
499 AND msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
500 AND msr.DELETED_FLAG= MSC_UTIL.SYS_NO;
501
502 CURSOR c4_d IS
503 SELECT
504 msr.ROUTING_SEQUENCE_ID,
505 msr.SR_INSTANCE_ID
506 FROM MSC_ST_ROUTINGS msr
507 WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
508 AND msr.DELETED_FLAG= MSC_UTIL.SYS_YES;
509
510
511 c_count NUMBER:= 0;
512 lv_tbl VARCHAR2(30);
513 lv_sql_stmt VARCHAR2(5000);
514 lv_sql_ins VARCHAR2(6000);
515 lb_refresh_failed BOOLEAN:= FALSE;
516
517 BEGIN
518
519 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
520 -- We want to delete all ROUTING related data and get new stuff.
521
522 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
523
524 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
525 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1);
526 ELSE
527 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
528 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTINGS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
529 END IF;
530
531 BEGIN
532 lv_sql_ins:=
533 'INSERT INTO MSC_ROUTINGS'
534 ||' (PLAN_ID,'
535 ||' ROUTING_SEQUENCE_ID,'
536 ||' ROUTING_TYPE,'
537 ||' ROUTING_COMMENT,'
538 ||' ALTERNATE_ROUTING_DESIGNATOR,'
539 ||' PROJECT_ID,'
540 ||' TASK_ID,'
541 ||' LINE_ID,'
542 ||' UOM_CODE,'
543 ||' CFM_ROUTING_FLAG,'
544 ||' CTP_FLAG,'
545 ||' ASSEMBLY_ITEM_ID,'
546 ||' ORGANIZATION_ID,'
547 ||' ROUTING_QUANTITY,'
548 ||' SR_INSTANCE_ID,'
549 ||' REFRESH_NUMBER,'
550 ||' LAST_UPDATE_DATE,'
551 ||' LAST_UPDATED_BY,'
552 ||' CREATION_DATE,'
553 ||' CREATED_BY,'
554 ||' FIRST_OP_SEQ_NUM,'
555 ||' LAST_OP_SEQ_NUM,'
556 ||' COMMON_ROUTING_SEQUENCE_ID,'
557 ||' AUTO_STEP_QTY_FLAG)'
558 ||' SELECT '
559 ||' -1,'
560 ||' msr.ROUTING_SEQUENCE_ID,'
561 ||' msr.ROUTING_TYPE,'
562 ||' REPLACE(REPLACE(substrb(msr.ROUTING_COMMENT,1,240),:v_chr10,'' ''),:v_chr13,'' '') ROUTING_COMMENT,'
563 ||' msr.ALTERNATE_ROUTING_DESIGNATOR,'
564 ||' msr.PROJECT_ID,'
565 ||' msr.TASK_ID,'
566 ||' msr.LINE_ID,'
567 ||' msr.UOM_CODE,'
568 ||' nvl(msr.CFM_ROUTING_FLAG,2) CFM_ROUTING_FLAG,'
569 ||' msr.CTP_FLAG,'
570 ||' t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,'
571 ||' msr.ORGANIZATION_ID,'
572 ||' msr.ROUTING_QUANTITY,'
573 ||' msr.SR_INSTANCE_ID,'
574 ||' :v_last_collection_id,'
575 ||' :v_current_date,'
576 ||' :v_current_user,'
577 ||' :v_current_date,'
578 ||' :v_current_user, '
579 ||' msr.FIRST_OP_SEQ_NUM,'
580 ||' msr.LAST_OP_SEQ_NUM,'
581 ||' msr.COMMON_ROUTING_SEQUENCE_ID,'
582 ||' msr.AUTO_STEP_QTY_FLAG '
583 ||' FROM MSC_ITEM_ID_LID t1,'
584 ||' MSC_ST_ROUTINGS msr'
585 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msr.assembly_item_id'
586 ||' AND t1.sr_instance_id= msr.sr_instance_id'
587 ||' AND msr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
588 ||' AND msr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
589
590 EXECUTE IMMEDIATE lv_sql_ins
591 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;
592
593 COMMIT;
594 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routings loaded');
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
599
600 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
601 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
602 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
603 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
604 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
605
606 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
607 RAISE;
608
609 ELSE
610 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
611 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
612 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
613 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
615
616 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
617
618 --If Direct path load results in warning then the processing has to be
619 --switched back to row by row processing. This will help to identify the
620 --erroneous record and will also help in processing the rest of the records.
621 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routings');
622 lb_refresh_failed := TRUE;
623 END IF;
624 END;
625
626 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
627
628 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
629
630 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
631
632 FOR c_rec IN c4_d LOOP
633
634 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
635
636 DELETE MSC_ROUTINGS
637 WHERE PLAN_ID= -1
638 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
639 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
640
641 END LOOP;
642
643 END IF;
644
645
646 c_count:= 0;
647
648 FOR c_rec IN c4 LOOP
649
650 BEGIN
651
652 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
653
654 UPDATE MSC_ROUTINGS
655 SET
656 ROUTING_TYPE= c_rec.ROUTING_TYPE,
657 ROUTING_COMMENT= c_rec.ROUTING_COMMENT,
658 ALTERNATE_ROUTING_DESIGNATOR= c_rec.ALTERNATE_ROUTING_DESIGNATOR,
659 PROJECT_ID= c_rec.PROJECT_ID,
660 TASK_ID= c_rec.TASK_ID,
661 LINE_ID= c_rec.LINE_ID,
662 UOM_CODE= c_rec.UOM_CODE,
663 CFM_ROUTING_FLAG= c_rec.CFM_ROUTING_FLAG,
664 FIRST_OP_SEQ_NUM = c_rec.FIRST_OP_SEQ_NUM,
665 LAST_OP_SEQ_NUM = c_rec.LAST_OP_SEQ_NUM,
666 common_routing_sequence_id=c_rec.common_routing_sequence_id,
667 CTP_FLAG= c_rec.CTP_FLAG,
668 ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
669 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
670 ROUTING_QUANTITY= c_rec.ROUTING_QUANTITY,
671 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
672 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
673 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
674 WHERE PLAN_ID= -1
675 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
676 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
677
678 END IF;
679
680 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
681
682 INSERT INTO MSC_ROUTINGS
683 ( PLAN_ID,
684 ROUTING_SEQUENCE_ID,
685 ROUTING_TYPE,
686 ROUTING_COMMENT,
687 ALTERNATE_ROUTING_DESIGNATOR,
688 PROJECT_ID,
689 TASK_ID,
690 LINE_ID,
691 UOM_CODE,
692 CFM_ROUTING_FLAG,
693 CTP_FLAG,
694 ASSEMBLY_ITEM_ID,
695 ORGANIZATION_ID,
696 ROUTING_QUANTITY,
697 SR_INSTANCE_ID,
698 REFRESH_NUMBER,
699 LAST_UPDATE_DATE,
700 LAST_UPDATED_BY,
701 CREATION_DATE,
702 CREATED_BY,
703 FIRST_OP_SEQ_NUM,
704 LAST_OP_SEQ_NUM,
705 common_routing_sequence_id,
706 auto_step_qty_flag)
707 VALUES
708 ( -1,
709 c_rec.ROUTING_SEQUENCE_ID,
710 c_rec.ROUTING_TYPE,
711 c_rec.ROUTING_COMMENT,
712 c_rec.ALTERNATE_ROUTING_DESIGNATOR,
713 c_rec.PROJECT_ID,
714 c_rec.TASK_ID,
715 c_rec.LINE_ID,
716 c_rec.UOM_CODE,
717 c_rec.CFM_ROUTING_FLAG,
718 c_rec.CTP_FLAG,
719 c_rec.ASSEMBLY_ITEM_ID,
720 c_rec.ORGANIZATION_ID,
721 c_rec.ROUTING_QUANTITY,
722 c_rec.SR_INSTANCE_ID,
723 MSC_CL_COLLECTION.v_last_collection_id,
724 MSC_CL_COLLECTION.v_current_date,
725 MSC_CL_COLLECTION.v_current_user,
726 MSC_CL_COLLECTION.v_current_date,
727 MSC_CL_COLLECTION.v_current_user,
728 c_rec.first_op_seq_num,
729 c_rec.last_op_seq_num,
730 c_rec.common_routing_sequence_id,
731 c_rec.auto_step_qty_flag);
732
733 END IF; -- SQL%NOTFOUND
734
735 c_count:= c_count+1;
736
737 IF c_count> MSC_CL_COLLECTION.PBS THEN
738 COMMIT;
739 c_count:= 0;
740 END IF;
741
742 EXCEPTION
743
744 WHEN OTHERS THEN
745
746 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
747
748 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
749 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
750 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
751 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
752 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
753
754 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
755 RAISE;
756
757 ELSE
758 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
759
760 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
761 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
762 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING');
763 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTINGS');
764 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
765
766 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
767 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
768 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(c_rec.ASSEMBLY_ITEM_ID));
769 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
770
771 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
772 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
773 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
774 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
775
776 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
777 END IF;
778 END;
779
780 END LOOP;
781 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
782
783 COMMIT;
784
785 END LOAD_ROUTING;
786
787 --==================================================================
788
789 PROCEDURE LOAD_OPERATION_COMPONENTS IS
790
791 CURSOR c9 IS
792 SELECT
793 ORGANIZATION_ID,
794 OPERATION_SEQUENCE_ID,
795 COMPONENT_SEQUENCE_ID,
796 BILL_SEQUENCE_ID,
797 ROUTING_SEQUENCE_ID,
798 SR_INSTANCE_ID
799 FROM MSC_ST_OPERATION_COMPONENTS msoc
800 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
801 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
802
803 CURSOR c9_d IS
804 SELECT
805 OPERATION_SEQUENCE_ID,
806 COMPONENT_SEQUENCE_ID,
807 BILL_SEQUENCE_ID,
808 ROUTING_SEQUENCE_ID,
809 SR_INSTANCE_ID,
810 ORGANIZATION_ID
811 FROM MSC_ST_OPERATION_COMPONENTS msoc
812 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
813 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_YES
814 UNION ALL
815 SELECT DISTINCT
816 TO_NUMBER(NULL),
817 TO_NUMBER(NULL),
818 moc.BILL_SEQUENCE_ID,
819 moc.ROUTING_SEQUENCE_ID,
820 moc.SR_INSTANCE_ID,
821 moc.ORGANIZATION_ID
822 FROM MSC_OPERATION_COMPONENTS moc,
823 MSC_ST_OPERATION_COMPONENTS msoc
824 WHERE msoc.Bill_Sequence_ID= moc.Bill_Sequence_ID
825 AND msoc.Routing_Sequence_ID <> moc.Routing_Sequence_ID
826 AND msoc.Organization_ID= moc.Organization_ID
827 AND msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
828 AND moc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
829 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO
830 AND moc.PLAN_ID= -1;
831
832
833 c_count NUMBER:= 0;
834 lv_tbl VARCHAR2(30);
835 lv_sql_stmt VARCHAR2(5000);
836 lv_sql_stmt1 VARCHAR2(5000);
837 lb_refresh_failed Boolean:= FALSE;
838 lv_delete_flag BOOLEAN:= FALSE;
839
840 BEGIN
841
842 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
843 -- We want to delete all BOM related data and get new stuff.
844
845 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
846
847 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
848 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
849 ELSE
850 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
851 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
852 END IF;
853 BEGIN
854 INSERT /*+ append */
855 INTO MSC_OPERATION_COMPONENTS
856 (PLAN_ID,
857 ORGANIZATION_ID,
858 OPERATION_SEQUENCE_ID,
859 COMPONENT_SEQUENCE_ID,
860 SR_INSTANCE_ID,
861 BILL_SEQUENCE_ID,
862 ROUTING_SEQUENCE_ID,
863 REFRESH_NUMBER,
864 LAST_UPDATE_DATE,
865 LAST_UPDATED_BY,
866 CREATION_DATE,
867 CREATED_BY)
868 SELECT
869 -1,
870 ORGANIZATION_ID,
871 OPERATION_SEQUENCE_ID,
872 COMPONENT_SEQUENCE_ID,
873 SR_INSTANCE_ID,
874 BILL_SEQUENCE_ID,
875 ROUTING_SEQUENCE_ID,
876 MSC_CL_COLLECTION.v_last_collection_id,
877 MSC_CL_COLLECTION.v_current_date,
878 MSC_CL_COLLECTION.v_current_user,
879 MSC_CL_COLLECTION.v_current_date,
880 MSC_CL_COLLECTION.v_current_user
881 FROM MSC_ST_OPERATION_COMPONENTS msoc
882 WHERE msoc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
883 AND msoc.DELETED_FLAG= MSC_UTIL.SYS_NO;
884
885 COMMIT;
886 EXCEPTION
887 WHEN OTHERS THEN
888
889 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
890
891 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
892 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
893 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
894 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
895 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
896
897 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
898 RAISE;
899 ELSE
900 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
901 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
902 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
903 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
904 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
905 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
906
907 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
908 lb_refresh_failed := TRUE;
909 END IF;
910 END;
911 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
912
913 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
914
915 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
916 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
917 FOR c_rec IN c9_d LOOP
918
919 BEGIN
920
921 ---5470477
922 lv_sql_stmt1 := ' DELETE MSC_OPERATION_COMPONENTS '
923 ||' WHERE PLAN_ID= -1 '
924 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
925
926 lv_delete_flag := FALSE;
927
928 IF (c_rec.ORGANIZATION_ID IS NOT NULL) THEN
929 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ORGANIZATION_ID= ' || c_rec.ORGANIZATION_ID ;
930 lv_delete_flag := FALSE;
931 END IF;
932
933 IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
934 lv_sql_stmt1 := lv_sql_stmt1 ||' AND BILL_SEQUENCE_ID= ' || c_rec.BILL_SEQUENCE_ID ;
935 lv_delete_flag := FALSE;
936 END IF;
937
938 IF (c_rec.ROUTING_SEQUENCE_ID IS NOT NULL) THEN
939 lv_sql_stmt1 := lv_sql_stmt1 ||' AND ROUTING_SEQUENCE_ID= ' || c_rec.ROUTING_SEQUENCE_ID ;
940 lv_delete_flag := FALSE;
941 END IF;
942
943 IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
944 lv_sql_stmt1 := lv_sql_stmt1 ||' AND COMPONENT_SEQUENCE_ID= ' || c_rec.COMPONENT_SEQUENCE_ID ;
945 lv_delete_flag := FALSE;
946 END IF;
947
948 IF (c_rec.OPERATION_SEQUENCE_ID IS NOT NULL) THEN
949 lv_sql_stmt1 := lv_sql_stmt1 ||' AND OPERATION_SEQUENCE_ID= ' || c_rec.OPERATION_SEQUENCE_ID ;
950 lv_delete_flag := FALSE;
951 END IF;
952 if (lv_delete_flag = FALSE) then
953 EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID;
954 end if;
955 /* bug 988700 fix : change UPDATE to DELETE */
956 /*
957 DELETE MSC_OPERATION_COMPONENTS
958 WHERE PLAN_ID= -1
959 AND BILL_SEQUENCE_ID= NVL( c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
960 AND ROUTING_SEQUENCE_ID= NVL( c_rec.ROUTING_SEQUENCE_ID, ROUTING_SEQUENCE_ID)
961 AND COMPONENT_SEQUENCE_ID= NVL( c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
962 AND OPERATION_SEQUENCE_ID= NVL( c_rec.OPERATION_SEQUENCE_ID, OPERATION_SEQUENCE_ID)
963 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
964 */
965 EXCEPTION
966 WHEN OTHERS THEN
967 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
968 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
969 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
970 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
971 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
972
973 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
974 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
975 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
976 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
977
978 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
979 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
980 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
982
983 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
984 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
985 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
986 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
987
988 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
989 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
990 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
991 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
992
993 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
994
995 END;
996
997 END LOOP;
998
999 END IF;
1000
1001
1002 c_count:= 0;
1003
1004 FOR c_rec IN c9 LOOP
1005
1006 BEGIN
1007
1008 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1009
1010 UPDATE MSC_OPERATION_COMPONENTS
1011 SET PLAN_ID= -1,
1012 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1013 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1014 WHERE PLAN_ID= -1
1015 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1016 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1017 AND BILL_SEQUENCE_ID = c_rec.BILL_SEQUENCE_ID
1018 AND ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1019 AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
1020 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID;
1021
1022 END IF;
1023
1024 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1025
1026 INSERT INTO MSC_OPERATION_COMPONENTS
1027 ( PLAN_ID,
1028 ORGANIZATION_ID,
1029 OPERATION_SEQUENCE_ID,
1030 COMPONENT_SEQUENCE_ID,
1031 SR_INSTANCE_ID,
1032 BILL_SEQUENCE_ID,
1033 ROUTING_SEQUENCE_ID,
1034 REFRESH_NUMBER,
1035 LAST_UPDATE_DATE,
1036 LAST_UPDATED_BY,
1037 CREATION_DATE,
1038 CREATED_BY)
1039 VALUES
1040 ( -1,
1041 c_rec.ORGANIZATION_ID,
1042 c_rec.OPERATION_SEQUENCE_ID,
1043 c_rec.COMPONENT_SEQUENCE_ID,
1044 c_rec.SR_INSTANCE_ID,
1045 c_rec.BILL_SEQUENCE_ID,
1046 c_rec.ROUTING_SEQUENCE_ID,
1047 MSC_CL_COLLECTION.v_last_collection_id,
1048 MSC_CL_COLLECTION.v_current_date,
1049 MSC_CL_COLLECTION.v_current_user,
1050 MSC_CL_COLLECTION.v_current_date,
1051 MSC_CL_COLLECTION.v_current_user);
1052
1053 END IF;
1054
1055 c_count:= c_count+1;
1056
1057 IF c_count> MSC_CL_COLLECTION.PBS THEN
1058 COMMIT;
1059 c_count:= 0;
1060 END IF;
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064
1065 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1066
1067 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1068 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1069 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
1070 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
1071 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1072
1073 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1074 RAISE;
1075
1076 ELSE
1077 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1078
1079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1080 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1081 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_COMPONENTS');
1082 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_COMPONENTS');
1083 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1084
1085 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1086 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1087 FND_MESSAGE.SET_TOKEN('VALUE', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID, MSC_CL_COLLECTION.v_instance_id));
1088 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1089
1090 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1091 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1092 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1093 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1094
1095 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1096 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
1097 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
1098 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1099
1100 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1101 END IF;
1102
1103 END;
1104
1105 END LOOP;
1106
1107 COMMIT;
1108 END IF;
1109
1110 END LOAD_OPERATION_COMPONENTS;
1111
1112 --=====================================================================
1113
1114 PROCEDURE LOAD_OP_RESOURCE_SEQ IS
1115
1116 CURSOR c7 IS
1117 SELECT
1118 msors.ROUTING_SEQUENCE_ID,
1119 msors.OPERATION_SEQUENCE_ID,
1120 msors.RESOURCE_SEQ_NUM,
1121 msors.SCHEDULE_FLAG,
1122 msors.RESOURCE_OFFSET_PERCENT,
1123 msors.DEPARTMENT_ID,
1124 msors.ACTIVITY_GROUP_ID,
1125 msors.SR_INSTANCE_ID,
1126 msors.ORGANIZATION_ID
1127 FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1128 WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1129 AND msors.DELETED_FLAG= MSC_UTIL.SYS_NO;
1130
1131 CURSOR c7_d IS
1132 SELECT
1133 msors.ROUTING_SEQUENCE_ID,
1134 msors.OPERATION_SEQUENCE_ID,
1135 msors.RESOURCE_SEQ_NUM,
1136 msors.SR_INSTANCE_ID
1137 FROM MSC_ST_OPERATION_RESOURCE_SEQS msors
1138 WHERE msors.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1139 AND msors.DELETED_FLAG= MSC_UTIL.SYS_YES;
1140
1141
1142 c_count NUMBER:= 0;
1143 lv_tbl VARCHAR2(30);
1144 lv_sql_stmt VARCHAR2(5000);
1145 lv_sql_ins VARCHAR2(6000);
1146 lb_refresh_failed Boolean:= FALSE;
1147
1148 lv_errbuf VARCHAR2(240);
1149 lv_retcode NUMBER;
1150
1151 BEGIN
1152
1153 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1154 lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1155 ELSE
1156 lv_tbl:= 'MSC_OPERATION_RESOURCE_SEQS';
1157 END IF;
1158
1159 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1160 -- We want to delete all BOM related data and get new stuff.
1161 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1162
1163 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1164 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1165 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1);
1166 ELSE
1167 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1168 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCE_SEQS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1169 END IF;
1170 END IF;
1171
1172 BEGIN
1173
1174 lv_sql_ins:=
1175 ' INSERT INTO '||lv_tbl
1176 ||' ( PLAN_ID, '
1177 ||' ROUTING_SEQUENCE_ID, '
1178 ||' OPERATION_SEQUENCE_ID, '
1179 ||' RESOURCE_SEQ_NUM, '
1180 ||' SCHEDULE_FLAG, '
1181 ||' RESOURCE_OFFSET_PERCENT, '
1182 ||' DEPARTMENT_ID, '
1183 ||' ACTIVITY_GROUP_ID, '
1184 ||' SR_INSTANCE_ID, '
1185 ||' ORGANIZATION_ID, '
1186 ||' REFRESH_NUMBER, '
1187 ||' LAST_UPDATE_DATE, '
1188 ||' LAST_UPDATED_BY, '
1189 ||' CREATION_DATE, '
1190 ||' CREATED_BY) '
1191 ||'SELECT '
1192 ||' -1,'
1193 ||' msors.ROUTING_SEQUENCE_ID,'
1194 ||' msors.OPERATION_SEQUENCE_ID,'
1195 ||' msors.RESOURCE_SEQ_NUM,'
1196 ||' msors.SCHEDULE_FLAG,'
1197 ||' msors.RESOURCE_OFFSET_PERCENT,'
1198 ||' msors.DEPARTMENT_ID,'
1199 ||' msors.ACTIVITY_GROUP_ID,'
1200 ||' msors.SR_INSTANCE_ID,'
1201 ||' msors.ORGANIZATION_ID, '
1202 ||' :v_last_collection_id,'
1203 ||' :v_current_date,'
1204 ||' :v_current_user,'
1205 ||' :v_current_date,'
1206 ||' :v_current_user '
1207 ||' FROM MSC_ST_OPERATION_RESOURCE_SEQS msors'
1208 ||' WHERE msors.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1209 ||' AND msors.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1210
1211 EXECUTE IMMEDIATE lv_sql_ins
1212 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;
1213
1214 COMMIT;
1215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resource seqs loaded');
1216
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1220
1221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1222 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1223 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1224 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1226
1227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1228 RAISE;
1229
1230 ELSE
1231 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1232 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1233 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1234 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1235 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1236
1237 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1238
1239 --If Direct path load results in warning then the processing has to be
1240 --switched back to row by row processing. This will help to identify the
1241 --erroneous record and will also help in processing the rest of the records.
1242 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resource seqs');
1243 lb_refresh_failed := TRUE;
1244 END IF;
1245 END;
1246
1247 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1248
1249 lv_sql_stmt :=
1250 ' INSERT INTO '||lv_tbl
1251 ||' ( PLAN_ID, '
1252 ||' ROUTING_SEQUENCE_ID, '
1253 ||' OPERATION_SEQUENCE_ID, '
1254 ||' RESOURCE_SEQ_NUM, '
1255 ||' SCHEDULE_FLAG, '
1256 ||' RESOURCE_OFFSET_PERCENT, '
1257 ||' DEPARTMENT_ID, '
1258 ||' ACTIVITY_GROUP_ID, '
1259 ||' SR_INSTANCE_ID, '
1260 ||' ORGANIZATION_ID, '
1261 ||' REFRESH_NUMBER, '
1262 ||' LAST_UPDATE_DATE, '
1263 ||' LAST_UPDATED_BY, '
1264 ||' CREATION_DATE, '
1265 ||' CREATED_BY) '
1266 ||' VALUES '
1267 ||' ( -1, '
1268 ||' :ROUTING_SEQUENCE_ID, '
1269 ||' :OPERATION_SEQUENCE_ID, '
1270 ||' :RESOURCE_SEQ_NUM, '
1271 ||' :SCHEDULE_FLAG, '
1272 ||' :RESOURCE_OFFSET_PERCENT, '
1273 ||' :DEPARTMENT_ID, '
1274 ||' :ACTIVITY_GROUP_ID, '
1275 ||' :SR_INSTANCE_ID, '
1276 ||' :ORGANIZATION_ID, '
1277 ||' :v_last_collection_id, '
1278 ||' :v_current_date, '
1279 ||' :v_current_user, '
1280 ||' :v_current_date, '
1281 ||' :v_current_user ) ';
1282
1283 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1284
1285 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1286
1287 FOR c_rec IN c7_d LOOP
1288
1289 IF c_rec.OPERATION_SEQUENCE_ID IS NOT NULL THEN
1290 DELETE MSC_OPERATION_RESOURCE_SEQS
1291 WHERE PLAN_ID= -1
1292 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1293 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1294 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
1295 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1296 ELSE
1297 DELETE MSC_OPERATION_RESOURCE_SEQS
1298 WHERE PLAN_ID= -1
1299 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1300 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1301 END IF;
1302
1303 END LOOP;
1304
1305 END IF;
1306
1307
1308 c_count:= 0;
1309
1310 FOR c_rec IN c7 LOOP
1311
1312 BEGIN
1313
1314 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1315
1316 UPDATE MSC_OPERATION_RESOURCE_SEQS
1317 SET
1318 SCHEDULE_FLAG= c_rec.SCHEDULE_FLAG,
1319 RESOURCE_OFFSET_PERCENT= c_rec.RESOURCE_OFFSET_PERCENT,
1320 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1321 ACTIVITY_GROUP_ID= c_rec.ACTIVITY_GROUP_ID,
1322 ORGANIZATION_ID=c_rec.ORGANIZATION_ID,
1323 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1324 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1325 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1326 WHERE PLAN_ID= -1
1327 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1328 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1329 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
1330 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1331
1332 END IF;
1333
1334 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1335
1336 EXECUTE IMMEDIATE lv_sql_stmt USING
1337 c_rec.ROUTING_SEQUENCE_ID,
1338 c_rec.OPERATION_SEQUENCE_ID,
1339 c_rec.RESOURCE_SEQ_NUM,
1340 c_rec.SCHEDULE_FLAG,
1341 c_rec.RESOURCE_OFFSET_PERCENT,
1342 c_rec.DEPARTMENT_ID,
1343 c_rec.ACTIVITY_GROUP_ID,
1344 c_rec.SR_INSTANCE_ID,
1345 c_rec.ORGANIZATION_ID,
1346 MSC_CL_COLLECTION.v_last_collection_id,
1347 MSC_CL_COLLECTION.v_current_date,
1348 MSC_CL_COLLECTION.v_current_user,
1349 MSC_CL_COLLECTION.v_current_date,
1350 MSC_CL_COLLECTION.v_current_user ;
1351
1352 END IF; -- SQL%NOTFOUND
1353
1354 c_count:= c_count+1;
1355
1356 IF c_count> MSC_CL_COLLECTION.PBS THEN
1357 COMMIT;
1358 c_count:= 0;
1359 END IF;
1360
1361
1362 EXCEPTION
1363
1364 WHEN DUP_VAL_ON_INDEX THEN
1365 NULL;
1366
1367 WHEN OTHERS THEN
1368
1369 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1370
1371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1372 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1373 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1374 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1376
1377 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1378 RAISE;
1379
1380 ELSE
1381 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1382
1383 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1384 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1385 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OP_RESOURCE_SEQ');
1386 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCE_SEQS');
1387 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1388
1389 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1390 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1391 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1392 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1393
1394 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1395 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1396 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1397 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1398
1399 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1400 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
1401 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_SEQ_NUM));
1402 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1403
1404 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1405
1406 END IF;
1407 END;
1408
1409 END LOOP;
1410 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1411
1412 COMMIT;
1413
1414 BEGIN
1415
1416 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
1417
1418 lv_tbl:= 'OPERATION_RESOURCE_SEQS_'||MSC_CL_COLLECTION.v_instance_code;
1419
1420 lv_sql_stmt:=
1421 'INSERT INTO '||lv_tbl
1422 ||' SELECT * from MSC_OPERATION_RESOURCE_SEQS'
1423 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1424 ||' AND plan_id = -1 '
1425 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1426
1427 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1428 EXECUTE IMMEDIATE lv_sql_stmt;
1429
1430 COMMIT;
1431
1432 END IF;
1433
1434 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1435 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1436 lv_retcode,
1437 'MSC_OPERATION_RESOURCE_SEQS',
1438 MSC_CL_COLLECTION.v_instance_code,
1439 MSC_UTIL.G_WARNING
1440 );
1441
1442 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1443 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1444 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1445 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1446 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1447 END IF;
1448
1449 END IF;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453
1454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1455 RAISE;
1456 END;
1457
1458 END LOAD_OP_RESOURCE_SEQ;
1459
1460 --======================================================================
1461 PROCEDURE LOAD_ROUTING_OPERATIONS IS
1462
1463 CURSOR c5 IS
1464 SELECT
1465 msro.ROUTING_SEQUENCE_ID,
1466 msro.OPERATION_SEQ_NUM,
1467 msro.OPERATION_SEQUENCE_ID,
1468 REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),v_chr10,'-'),v_chr13,'-') OPERATION_DESCRIPTION,
1469 msro.EFFECTIVITY_DATE,
1470 msro.DISABLE_DATE,
1471 msro.FROM_UNIT_NUMBER,
1472 msro.TO_UNIT_NUMBER,
1473 msro.OPTION_DEPENDENT_FLAG,
1474 msro.OPERATION_TYPE,
1475 msro.MINIMUM_TRANSFER_QUANTITY,
1476 msro.YIELD,
1477 msro.DEPARTMENT_ID,
1478 msro.DEPARTMENT_CODE,
1479 msro.OPERATION_LEAD_TIME_PERCENT,
1480 msro.CUMULATIVE_YIELD,
1481 msro.REVERSE_CUMULATIVE_YIELD,
1482 msro.NET_PLANNING_PERCENT,
1483 msro.SETUP_DURATION,
1484 msro.TEAR_DOWN_DURATION,
1485 msro.UOM_CODE,
1486 msro.STANDARD_OPERATION_CODE,
1487 msro.STEP_QUANTITY,
1488 msro.STEP_QUANTITY_UOM,
1489 msro.DELETED_FLAG,
1490 msro.SR_INSTANCE_ID,
1491 msro.ORGANIZATION_ID
1492 FROM MSC_ST_ROUTING_OPERATIONS msro
1493 WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1494 AND msro.DELETED_FLAG= MSC_UTIL.SYS_NO;
1495
1496 CURSOR c5_d IS
1497 SELECT
1498 msro.ROUTING_SEQUENCE_ID,
1499 msro.OPERATION_SEQUENCE_ID,
1500 msro.SR_INSTANCE_ID
1501 FROM MSC_ST_ROUTING_OPERATIONS msro
1502 WHERE msro.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1503 AND msro.DELETED_FLAG= MSC_UTIL.SYS_YES;
1504
1505
1506 c_count NUMBER:= 0;
1507 lv_tbl VARCHAR2(30);
1508 lv_sql_stmt VARCHAR2(5000);
1509 lv_sql_ins VARCHAR2(6000);
1510 lb_refresh_failed BOOLEAN:= FALSE;
1511
1512 lv_errbuf VARCHAR2(240);
1513 lv_retcode NUMBER;
1514
1515 BEGIN
1516
1517 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1518 lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1519 ELSE
1520 lv_tbl:= 'MSC_ROUTING_OPERATIONS';
1521 END IF;
1522
1523 IF MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh THEN
1524 -- We want to delete all BOM related data and get new stuff
1525 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1526
1527 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1528 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1529 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
1530 ELSE
1531 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1532 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_ROUTING_OPERATIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1533 END IF;
1534 END IF;
1535
1536 BEGIN
1537 lv_sql_ins:=
1538 ' INSERT INTO '||lv_tbl
1539 ||' ( PLAN_ID, '
1540 ||' ROUTING_SEQUENCE_ID, '
1541 ||' OPERATION_SEQ_NUM, '
1542 ||' OPERATION_SEQUENCE_ID, '
1543 ||' OPERATION_DESCRIPTION, '
1544 ||' EFFECTIVITY_DATE, '
1545 ||' DISABLE_DATE, '
1546 ||' FROM_UNIT_NUMBER, '
1547 ||' TO_UNIT_NUMBER, '
1548 ||' OPTION_DEPENDENT_FLAG, '
1549 ||' OPERATION_TYPE, '
1550 ||' MINIMUM_TRANSFER_QUANTITY, '
1551 ||' YIELD, '
1552 ||' DEPARTMENT_ID, '
1553 ||' DEPARTMENT_CODE, '
1554 ||' OPERATION_LEAD_TIME_PERCENT, '
1555 ||' CUMULATIVE_YIELD, '
1556 ||' REVERSE_CUMULATIVE_YIELD, '
1557 ||' NET_PLANNING_PERCENT, '
1558 ||' SETUP_DURATION, '
1559 ||' TEAR_DOWN_DURATION, '
1560 ||' UOM_CODE, '
1561 ||' STANDARD_OPERATION_CODE, '
1562 ||' STEP_QUANTITY, '
1563 ||' STEP_QUANTITY_UOM, '
1564 ||' SR_INSTANCE_ID, '
1565 ||' ORGANIZATION_ID, '
1566 ||' REFRESH_NUMBER, '
1567 ||' LAST_UPDATE_DATE, '
1568 ||' LAST_UPDATED_BY, '
1569 ||' CREATION_DATE, '
1570 ||' CREATED_BY) '
1571 ||' SELECT '
1572 ||' -1, '
1573 ||' msro.ROUTING_SEQUENCE_ID, '
1574 ||' msro.OPERATION_SEQ_NUM, '
1575 ||' msro.OPERATION_SEQUENCE_ID, '
1576 ||' REPLACE(REPLACE(substrb(msro.OPERATION_DESCRIPTION,1,240),:v_chr10,''-''),:v_chr13,''-'') OPERATION_DESCRIPTION, '
1577 ||' msro.EFFECTIVITY_DATE, '
1578 ||' msro.DISABLE_DATE, '
1579 ||' msro.FROM_UNIT_NUMBER, '
1580 ||' msro.TO_UNIT_NUMBER, '
1581 ||' msro.OPTION_DEPENDENT_FLAG, '
1582 ||' msro.OPERATION_TYPE, '
1583 ||' msro.MINIMUM_TRANSFER_QUANTITY, '
1584 ||' msro.YIELD, '
1585 ||' msro.DEPARTMENT_ID, '
1586 ||' msro.DEPARTMENT_CODE, '
1587 ||' msro.OPERATION_LEAD_TIME_PERCENT, '
1588 ||' msro.CUMULATIVE_YIELD, '
1589 ||' msro.REVERSE_CUMULATIVE_YIELD, '
1590 ||' msro.NET_PLANNING_PERCENT, '
1591 ||' msro.SETUP_DURATION, '
1592 ||' msro.TEAR_DOWN_DURATION, '
1593 ||' msro.UOM_CODE, '
1594 ||' msro.STANDARD_OPERATION_CODE, '
1595 ||' msro.STEP_QUANTITY, '
1596 ||' msro.STEP_QUANTITY_UOM, '
1597 ||' msro.SR_INSTANCE_ID, '
1598 ||' msro.ORGANIZATION_ID, '
1599 ||' :v_last_collection_id, '
1600 ||' :v_current_date, '
1601 ||' :v_current_user, '
1602 ||' :v_current_date, '
1603 ||' :v_current_user '
1604 ||' FROM MSC_ST_ROUTING_OPERATIONS msro '
1605 ||' WHERE msro.SR_INSTANCE_ID= ' || MSC_CL_COLLECTION.v_instance_id
1606 ||' AND msro.DELETED_FLAG= ' || MSC_UTIL.SYS_NO;
1607
1608 EXECUTE IMMEDIATE lv_sql_ins
1609 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;
1610
1611 COMMIT;
1612 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'routing operations loaded');
1613
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1617
1618 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1619 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1620 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1621 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1622 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1623
1624 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1625 RAISE;
1626
1627 ELSE
1628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1629 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1630 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1631 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1632 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1633
1634 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1635
1636 --If Direct path load results in warning then the processing has to be
1637 --switched back to row by row processing. This will help to identify the
1638 --erroneous record and will also help in processing the rest of the records.
1639 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - routing operations');
1640 lb_refresh_failed := TRUE;
1641 END IF;
1642 END;
1643
1644 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
1645
1646
1647 lv_sql_stmt:=
1648 ' INSERT INTO '||lv_tbl
1649 ||' ( PLAN_ID, '
1650 ||' ROUTING_SEQUENCE_ID, '
1651 ||' OPERATION_SEQ_NUM, '
1652 ||' OPERATION_SEQUENCE_ID, '
1653 ||' OPERATION_DESCRIPTION, '
1654 ||' EFFECTIVITY_DATE, '
1655 ||' DISABLE_DATE, '
1656 ||' FROM_UNIT_NUMBER, '
1657 ||' TO_UNIT_NUMBER, '
1658 ||' OPTION_DEPENDENT_FLAG, '
1659 ||' OPERATION_TYPE, '
1660 ||' MINIMUM_TRANSFER_QUANTITY, '
1661 ||' YIELD, '
1662 ||' DEPARTMENT_ID, '
1663 ||' DEPARTMENT_CODE, '
1664 ||' OPERATION_LEAD_TIME_PERCENT, '
1665 ||' CUMULATIVE_YIELD, '
1666 ||' REVERSE_CUMULATIVE_YIELD, '
1667 ||' NET_PLANNING_PERCENT, '
1668 ||' SETUP_DURATION, '
1669 ||' TEAR_DOWN_DURATION, '
1670 ||' UOM_CODE, '
1671 ||' STANDARD_OPERATION_CODE, '
1672 ||' STEP_QUANTITY, '
1673 ||' STEP_QUANTITY_UOM, '
1674 ||' SR_INSTANCE_ID, '
1675 ||' ORGANIZATION_ID, '
1676 ||' REFRESH_NUMBER, '
1677 ||' LAST_UPDATE_DATE, '
1678 ||' LAST_UPDATED_BY, '
1679 ||' CREATION_DATE, '
1680 ||' CREATED_BY) '
1681 ||' VALUES '
1682 ||' ( -1, '
1683 ||' :ROUTING_SEQUENCE_ID, '
1684 ||' :OPERATION_SEQ_NUM, '
1685 ||' :OPERATION_SEQUENCE_ID, '
1686 ||' :OPERATION_DESCRIPTION, '
1687 ||' :EFFECTIVITY_DATE, '
1688 ||' :DISABLE_DATE, '
1689 ||' :FROM_UNIT_NUMBER, '
1690 ||' :TO_UNIT_NUMBER, '
1691 ||' :OPTION_DEPENDENT_FLAG, '
1692 ||' :OPERATION_TYPE, '
1693 ||' :MINIMUM_TRANSFER_QUANTITY, '
1694 ||' :YIELD, '
1695 ||' :DEPARTMENT_ID, '
1696 ||' :DEPARTMENT_CODE, '
1697 ||' :OPERATION_LEAD_TIME_PERCENT, '
1698 ||' :CUMULATIVE_YIELD, '
1699 ||' :REVERSE_CUMULATIVE_YIELD, '
1700 ||' :NET_PLANNING_PERCENT, '
1701 ||' :SETUP_DURATION, '
1702 ||' :TEAR_DOWN_DURATION, '
1703 ||' :UOM_CODE, '
1704 ||' :STANDARD_OPERATION_CODE, '
1705 ||' :STEP_QUANTITY, '
1706 ||' :STEP_QUANTITY_UOM, '
1707 ||' :SR_INSTANCE_ID, '
1708 ||' :ORGANIZATION_ID, '
1709 ||' :v_last_collection_id, '
1710 ||' :v_current_date, '
1711 ||' :v_current_user, '
1712 ||' :v_current_date, '
1713 ||' :v_current_user ) ';
1714
1715 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1716
1717 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
1718
1719 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1720 FOR c_rec IN c5_d LOOP
1721
1722 DELETE MSC_ROUTING_OPERATIONS
1723 WHERE PLAN_ID= -1
1724 AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
1725 AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
1726 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1727
1728 END LOOP;
1729 END IF;
1730
1731 c_count:= 0;
1732
1733 FOR c_rec IN c5 LOOP
1734
1735 BEGIN
1736
1737 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1738 UPDATE MSC_ROUTING_OPERATIONS
1739 SET
1740 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1741 OPERATION_DESCRIPTION= c_rec.OPERATION_DESCRIPTION,
1742 EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1743 DISABLE_DATE= c_rec.DISABLE_DATE,
1744 FROM_UNIT_NUMBER= c_rec.FROM_UNIT_NUMBER,
1745 TO_UNIT_NUMBER= c_rec.TO_UNIT_NUMBER,
1746 OPTION_DEPENDENT_FLAG= c_rec.OPTION_DEPENDENT_FLAG,
1747 OPERATION_TYPE= c_rec.OPERATION_TYPE,
1748 MINIMUM_TRANSFER_QUANTITY= c_rec.MINIMUM_TRANSFER_QUANTITY,
1749 YIELD= c_rec.YIELD,
1750 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1751 DEPARTMENT_CODE= c_rec.DEPARTMENT_CODE,
1752 OPERATION_LEAD_TIME_PERCENT= c_rec.OPERATION_LEAD_TIME_PERCENT,
1753 CUMULATIVE_YIELD= c_rec.CUMULATIVE_YIELD,
1754 REVERSE_CUMULATIVE_YIELD= c_rec.REVERSE_CUMULATIVE_YIELD,
1755 NET_PLANNING_PERCENT= c_rec.NET_PLANNING_PERCENT,
1756 SETUP_DURATION= c_rec.SETUP_DURATION,
1757 TEAR_DOWN_DURATION= c_rec.TEAR_DOWN_DURATION,
1758 UOM_CODE= c_rec.UOM_CODE,
1759 STANDARD_OPERATION_CODE= c_rec.STANDARD_OPERATION_CODE,
1760 STEP_QUANTITY= c_rec.STEP_QUANTITY,
1761 STEP_QUANTITY_UOM= c_rec.STEP_QUANTITY_UOM,
1762 ORGANIZATION_ID = c_rec.ORGANIZATION_ID,
1763 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1764 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1765 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1766 WHERE PLAN_ID= -1
1767 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1768 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
1769 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1770 END IF;
1771
1772 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1773 EXECUTE IMMEDIATE lv_sql_stmt USING
1774 c_rec.ROUTING_SEQUENCE_ID,
1775 c_rec.OPERATION_SEQ_NUM,
1776 c_rec.OPERATION_SEQUENCE_ID,
1777 c_rec.OPERATION_DESCRIPTION,
1778 c_rec.EFFECTIVITY_DATE,
1779 c_rec.DISABLE_DATE,
1780 c_rec.FROM_UNIT_NUMBER,
1781 c_rec.TO_UNIT_NUMBER,
1782 c_rec.OPTION_DEPENDENT_FLAG,
1783 c_rec.OPERATION_TYPE,
1784 c_rec.MINIMUM_TRANSFER_QUANTITY,
1785 c_rec.YIELD,
1786 c_rec.DEPARTMENT_ID,
1787 c_rec.DEPARTMENT_CODE,
1788 c_rec.OPERATION_LEAD_TIME_PERCENT,
1789 c_rec.CUMULATIVE_YIELD,
1790 c_rec.REVERSE_CUMULATIVE_YIELD,
1791 c_rec.NET_PLANNING_PERCENT,
1792 c_rec.SETUP_DURATION,
1793 c_rec.TEAR_DOWN_DURATION,
1794 c_rec.UOM_CODE,
1795 c_rec.STANDARD_OPERATION_CODE,
1796 c_rec.STEP_QUANTITY,
1797 c_rec.STEP_QUANTITY_UOM,
1798 c_rec.SR_INSTANCE_ID,
1799 c_rec.ORGANIZATION_ID,
1800 MSC_CL_COLLECTION.v_last_collection_id,
1801 MSC_CL_COLLECTION.v_current_date,
1802 MSC_CL_COLLECTION.v_current_user,
1803 MSC_CL_COLLECTION.v_current_date,
1804 MSC_CL_COLLECTION.v_current_user ;
1805
1806 END IF; -- SQL%NOTFOUND
1807
1808 c_count:= c_count+1;
1809
1810 IF c_count> MSC_CL_COLLECTION.PBS THEN
1811 COMMIT;
1812 c_count:= 0;
1813 END IF;
1814
1815
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818
1819 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1820
1821 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1822 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1823 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1824 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1825 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1826
1827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1828 RAISE;
1829
1830 ELSE
1831 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1832
1833 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1834 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1835 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ROUTING_OPERATIONS');
1836 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_ROUTING_OPERATIONS');
1837 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1838
1839 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1840 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1841 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1842 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1843
1844 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1845 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQ_NUM');
1846 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQ_NUM));
1847 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1848
1849 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1850 FND_MESSAGE.SET_TOKEN('COLUMN', 'OPERATION_SEQUENCE_ID');
1851 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_SEQUENCE_ID));
1852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1853
1854 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1855 END IF;
1856 END;
1857
1858 END LOOP;
1859 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
1860
1861 COMMIT;
1862
1863 BEGIN
1864
1865 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
1866
1867 lv_tbl:= 'ROUTING_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
1868
1869 lv_sql_stmt:=
1870 'INSERT INTO '||lv_tbl
1871 ||' SELECT * from MSC_ROUTING_OPERATIONS'
1872 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1873 ||' AND plan_id = -1 '
1874 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1875
1876 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1877 EXECUTE IMMEDIATE lv_sql_stmt;
1878
1879 COMMIT;
1880
1881 END IF;
1882
1883 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1884 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1885 lv_retcode,
1886 'MSC_ROUTING_OPERATIONS',
1887 MSC_CL_COLLECTION.v_instance_code,
1888 MSC_UTIL.G_WARNING
1889 );
1890
1891 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1892 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1893 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1894 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1895 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1896 END IF;
1897
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902
1903 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1904 RAISE;
1905 END;
1906
1907
1908 END LOAD_ROUTING_OPERATIONS;
1909
1910 --====================================================================
1911 PROCEDURE LOAD_OPERATION_RESOURCES IS
1912
1913
1914 CURSOR c6 IS
1915 SELECT
1916 msor.ROUTING_SEQUENCE_ID,
1917 msor.OPERATION_SEQUENCE_ID,
1918 msor.RESOURCE_SEQ_NUM,
1919 msor.RESOURCE_ID,
1920 msor.ALTERNATE_NUMBER,
1921 nvl(msor.PRINCIPAL_FLAG,1) PRINCIPAL_FLAG,
1922 msor.BASIS_TYPE,
1923 msor.RESOURCE_USAGE,
1924 msor.MAX_RESOURCE_UNITS,
1925 msor.RESOURCE_UNITS,
1926 msor.UOM_CODE,
1927 msor.RESOURCE_TYPE,
1928 msor.SR_INSTANCE_ID,
1929 msor.ORGANIZATION_ID,
1930 msor.SETUP_ID, /*ds change change start */
1931 msor.MINIMUM_CAPACITY,
1932 msor.MAXIMUM_CAPACITY ,
1933 msor.orig_resource_seq_num,
1934 msor.BREAKABLE_ACTIVITY_FLAG /*ds change change end */
1935 FROM MSC_ST_OPERATION_RESOURCES msor
1936 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1937 AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1938
1939 CURSOR c6_d IS
1940 SELECT
1941 msor.ROUTING_SEQUENCE_ID,
1942 msor.OPERATION_SEQUENCE_ID,
1943 msor.RESOURCE_SEQ_NUM,
1944 msor.RESOURCE_ID,
1945 msor.ALTERNATE_NUMBER,
1946 msor.SR_INSTANCE_ID
1947 FROM MSC_ST_OPERATION_RESOURCES msor
1948 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1949 AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES;
1950
1951 CURSOR c6_corrupt_data IS
1952 SELECT
1953 msor.ROUTING_SEQUENCE_ID,
1954 msor.RESOURCE_ID,
1955 msor.RESOURCE_SEQ_NUM
1956 FROM MSC_ST_OPERATION_RESOURCES msor
1957 WHERE msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1958 AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO
1959 AND nvl(msor.RESOURCE_SEQ_NUM,0)=0
1960 AND msor.ALTERNATE_NUMBER > 0;
1961
1962 c_count NUMBER:= 0;
1963 lv_tbl VARCHAR2(30);
1964 lv_sql_stmt VARCHAR2(5000);
1965 lv_sql_stmt1 VARCHAR2(5000);
1966 lv_sql_ins VARCHAR2(6000);
1967 lb_refresh_failed BOOLEAN:= FALSE;
1968
1969 lv_errbuf VARCHAR2(240);
1970 lv_retcode NUMBER;
1971 lv_delete_flag BOOLEAN:= FALSE;
1972
1973 BEGIN
1974
1975 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1976 lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1977 ELSE
1978 lv_tbl:= 'MSC_OPERATION_RESOURCES';
1979 END IF;
1980
1981 IF (MSC_CL_COLLECTION.v_is_complete_refresh or MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1982
1983 -- We want to delete all BOM related data and get new stuff.
1984 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1985
1986 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO THEN
1987 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1988 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
1989 ELSE
1990 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1991 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_OPERATION_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1992 END IF;
1993 END IF;
1994
1995 BEGIN
1996 lv_sql_ins :=
1997 ' INSERT INTO '||lv_tbl
1998 ||' ( PLAN_ID, '
1999 ||' ROUTING_SEQUENCE_ID, '
2000 ||' OPERATION_SEQUENCE_ID, '
2001 ||' RESOURCE_SEQ_NUM, '
2002 ||' RESOURCE_ID, '
2003 ||' ALTERNATE_NUMBER, '
2004 ||' PRINCIPAL_FLAG, '
2005 ||' BASIS_TYPE, '
2006 ||' RESOURCE_USAGE, '
2007 ||' MAX_RESOURCE_UNITS, '
2008 ||' RESOURCE_UNITS, '
2009 ||' UOM_CODE, '
2010 ||' RESOURCE_TYPE, '
2011 ||' SR_INSTANCE_ID, '
2012 ||' ORGANIZATION_ID, '
2013 ||' SETUP_ID, ' /*ds change change start */
2014 ||' MINIMUM_CAPACITY, '
2015 ||' MAXIMUM_CAPACITY, '
2016 ||' orig_resource_seq_num, '
2017 ||' BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2018 ||' REFRESH_NUMBER, '
2019 ||' LAST_UPDATE_DATE, '
2020 ||' LAST_UPDATED_BY, '
2021 ||' CREATION_DATE, '
2022 ||' CREATED_BY) '
2023 ||' SELECT '
2024 ||' -1, '
2025 ||' msor.ROUTING_SEQUENCE_ID, '
2026 ||' msor.OPERATION_SEQUENCE_ID, '
2027 ||' msor.RESOURCE_SEQ_NUM, '
2028 ||' msor.RESOURCE_ID, '
2029 ||' msor.ALTERNATE_NUMBER, '
2030 ||' nvl(msor.PRINCIPAL_FLAG,1), '
2031 ||' msor.BASIS_TYPE, '
2032 ||' msor.RESOURCE_USAGE, '
2033 ||' msor.MAX_RESOURCE_UNITS, '
2034 ||' msor.RESOURCE_UNITS, '
2035 ||' msor.UOM_CODE, '
2036 ||' msor.RESOURCE_TYPE, '
2037 ||' msor.SR_INSTANCE_ID, '
2038 ||' msor.ORGANIZATION_ID,'
2039 ||' msor.SETUP_ID, ' /*ds change change start */
2040 ||' msor.MINIMUM_CAPACITY, '
2041 ||' msor.MAXIMUM_CAPACITY, '
2042 ||' msor.ORIG_RESOURCE_SEQ_NUM, '
2043 ||' msor.BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2044 ||' :v_last_collection_id, '
2045 ||' :v_current_date, '
2046 ||' :v_current_user, '
2047 ||' :v_current_date, '
2048 ||' :v_current_user '
2049 ||' FROM MSC_ST_OPERATION_RESOURCES msor'
2050 ||' WHERE msor.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2051 ||' AND msor.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2052 ||' AND NOT(nvl(msor.RESOURCE_SEQ_NUM,0)=0 AND msor.ALTERNATE_NUMBER > 0)';
2053
2054 EXECUTE IMMEDIATE lv_sql_ins
2055 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;
2056
2057 COMMIT;
2058 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'operation resources loaded');
2059
2060 FOR c_rec IN c6_corrupt_data LOOP
2061
2062 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2063
2064 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2065
2066 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2067 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2068 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2069 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2070
2071 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2072 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
2073 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_SEQ_NUM));
2074 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2075
2076 FND_MESSAGE.SET_NAME('MSC','MSC_INVALID_RES_SEQ_NUM');
2077 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID', TO_CHAR(c_rec.ROUTING_SEQUENCE_ID));
2078 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', TO_CHAR(c_rec.RESOURCE_ID));
2079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2080
2081 END LOOP;
2082
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2086
2087 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2088 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2089 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2090 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2091 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2092
2093 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2094 RAISE;
2095
2096 ELSE
2097 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2098 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2099 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2100 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2102
2103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2104
2105 --If Direct path load results in warning then the processing has to be
2106 --switched back to row by row processing. This will help to identify the
2107 --erroneous record and will also help in processing the rest of the records.
2108 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - operation resources');
2109 lb_refresh_failed := TRUE;
2110 END IF;
2111
2112 END;
2113
2114 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
2115
2116 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2117
2118 lv_sql_stmt :=
2119 ' INSERT INTO '||lv_tbl
2120 ||' ( PLAN_ID, '
2121 ||' ROUTING_SEQUENCE_ID, '
2122 ||' OPERATION_SEQUENCE_ID, '
2123 ||' RESOURCE_SEQ_NUM, '
2124 ||' RESOURCE_ID, '
2125 ||' ALTERNATE_NUMBER, '
2126 ||' PRINCIPAL_FLAG, '
2127 ||' BASIS_TYPE, '
2128 ||' RESOURCE_USAGE, '
2129 ||' MAX_RESOURCE_UNITS, '
2130 ||' RESOURCE_UNITS, '
2131 ||' UOM_CODE, '
2132 ||' RESOURCE_TYPE, '
2133 ||' SR_INSTANCE_ID, '
2134 ||' ORGANIZATION_ID, '
2135 ||' SETUP_ID, ' /*ds change change start */
2136 ||' MINIMUM_CAPACITY, '
2137 ||' MAXIMUM_CAPACITY, '
2138 ||' orig_resource_seq_num, '
2139 ||' BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2140 ||' REFRESH_NUMBER, '
2141 ||' LAST_UPDATE_DATE, '
2142 ||' LAST_UPDATED_BY, '
2143 ||' CREATION_DATE, '
2144 ||' CREATED_BY) '
2145 ||' VALUES '
2146 ||' ( -1, '
2147 ||' :ROUTING_SEQUENCE_ID, '
2148 ||' :OPERATION_SEQUENCE_ID, '
2149 ||' :RESOURCE_SEQ_NUM, '
2150 ||' :RESOURCE_ID, '
2151 ||' :ALTERNATE_NUMBER, '
2152 ||' :PRINCIPAL_FLAG, '
2153 ||' :BASIS_TYPE, '
2154 ||' :RESOURCE_USAGE, '
2155 ||' :MAX_RESOURCE_UNITS, '
2156 ||' :RESOURCE_UNITS, '
2157 ||' :UOM_CODE, '
2158 ||' :RESOURCE_TYPE, '
2159 ||' :SR_INSTANCE_ID, '
2160 ||' :ORGANIZATION_ID,'
2161 ||' :SETUP_ID, ' /*ds change change start */
2162 ||' :MINIMUM_CAPACITY, '
2163 ||' :MAXIMUM_CAPACITY, '
2164 ||' :orig_resource_seq_num, '
2165 ||' :BREAKABLE_ACTIVITY_FLAG, ' /*ds change change end */
2166 ||' :v_last_collection_id, '
2167 ||' :v_current_date, '
2168 ||' :v_current_user, '
2169 ||' :v_current_date, '
2170 ||' :v_current_user ) ';
2171
2172
2173 -- set RESOURCE_USAGE to 0 to indicate a SOFT delete
2174 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2175 FOR c_rec IN c6_d LOOP
2176 ---5470477
2177 lv_delete_flag := FALSE;
2178 lv_sql_stmt1 := ' UPDATE MSC_OPERATION_RESOURCES '
2179 ||' SET RESOURCE_USAGE= 0, '
2180 ||' REFRESH_NUMBER= :v_last_collection_id, '
2181 ||' LAST_UPDATE_DATE= :v_current_date, '
2182 ||' LAST_UPDATED_BY= :v_current_user '
2183 ||' WHERE PLAN_ID= -1 '
2184 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
2185
2186 IF (c_rec.ROUTING_SEQUENCE_ID IS NOT NULL) THEN
2187 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ROUTING_SEQUENCE_ID= ' || c_rec.ROUTING_SEQUENCE_ID ;
2188 lv_delete_flag := TRUE;
2189 END IF;
2190
2191 IF (c_rec.OPERATION_SEQUENCE_ID IS NOT NULL) THEN
2192 lv_sql_stmt1 := lv_sql_stmt1 || ' AND OPERATION_SEQUENCE_ID= ' || c_rec.OPERATION_SEQUENCE_ID ;
2193 lv_delete_flag := TRUE;
2194 END IF;
2195
2196 IF (c_rec.RESOURCE_SEQ_NUM IS NOT NULL) THEN
2197 lv_sql_stmt1 := lv_sql_stmt1 || ' AND RESOURCE_SEQ_NUM= ' || c_rec.RESOURCE_SEQ_NUM ;
2198 lv_delete_flag := TRUE;
2199 END IF;
2200
2201 IF (c_rec.RESOURCE_ID IS NOT NULL) THEN
2202 lv_sql_stmt1 := lv_sql_stmt1 || ' AND RESOURCE_ID= ' || c_rec.RESOURCE_ID ;
2203 lv_delete_flag := TRUE;
2204 END IF;
2205
2206 IF (c_rec.ALTERNATE_NUMBER IS NOT NULL) THEN
2207 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ALTERNATE_NUMBER= ' || c_rec.ALTERNATE_NUMBER ;
2208 lv_delete_flag := TRUE;
2209 END IF;
2210
2211 BEGIN
2212 If (lv_delete_flag) then
2213 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 ;
2214 end if;
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------');
2218 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1);
2219 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Routing Sequence Id: ' || c_rec.ROUTING_SEQUENCE_ID);
2220 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Operation Sequence Id: ' || c_rec.OPERATION_SEQUENCE_ID );
2221 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Resource Seq Num: ' || c_rec.RESOURCE_SEQ_NUM);
2222 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Resource Id: ' || c_rec.RESOURCE_ID);
2223 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Alternate Number: ' || c_rec.ALTERNATE_NUMBER);
2224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------');
2225 END;
2226
2227 /*
2228 UPDATE MSC_OPERATION_RESOURCES
2229 SET RESOURCE_USAGE= 0,
2230 REFRESH_NUMBER= v_last_collection_id,
2231 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2232 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2233 WHERE PLAN_ID= -1
2234 AND ROUTING_SEQUENCE_ID= NVL(c_rec.ROUTING_SEQUENCE_ID,ROUTING_SEQUENCE_ID)
2235 AND OPERATION_SEQUENCE_ID= NVL(c_rec.OPERATION_SEQUENCE_ID,OPERATION_SEQUENCE_ID)
2236 AND RESOURCE_SEQ_NUM= NVL(c_rec.RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM)
2237 AND RESOURCE_ID= NVL(c_rec.RESOURCE_ID,RESOURCE_ID)
2238 AND ALTERNATE_NUMBER= NVL( c_rec.ALTERNATE_NUMBER,ALTERNATE_NUMBER)
2239 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2240 */
2241 END LOOP;
2242
2243 END IF;
2244
2245
2246 c_count:= 0;
2247
2248 FOR c_rec IN c6 LOOP
2249
2250 BEGIN
2251
2252 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2253
2254 UPDATE MSC_OPERATION_RESOURCES
2255 SET
2256 PRINCIPAL_FLAG= c_rec.PRINCIPAL_FLAG,
2257 BASIS_TYPE= c_rec.BASIS_TYPE,
2258 RESOURCE_USAGE= c_rec.RESOURCE_USAGE,
2259 MAX_RESOURCE_UNITS= c_rec.MAX_RESOURCE_UNITS,
2260 RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
2261 UOM_CODE= c_rec.UOM_CODE,
2262 RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2263 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2264 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2265 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2266 ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER,
2267 ORGANIZATION_ID= c_rec.ORGANIZATION_ID ,
2268 SETUP_ID= c_rec.SETUP_ID , /* ds change change start*/
2269 MINIMUM_CAPACITY = c_rec.MINIMUM_CAPACITY ,
2270 MAXIMUM_CAPACITY = c_rec.MAXIMUM_CAPACITY ,
2271 orig_resource_seq_num = c_rec.orig_resource_seq_num ,
2272 BREAKABLE_ACTIVITY_FLAG = c_rec.BREAKABLE_ACTIVITY_FLAG /* ds change change end */
2273 WHERE PLAN_ID= -1
2274 AND ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
2275 AND OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID
2276 AND RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM
2277 AND RESOURCE_ID= c_rec.RESOURCE_ID
2278 /* AND ALTERNATE_NUMBER= c_rec.ALTERNATE_NUMBER
2279 Moving this to update */
2280 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2281
2282 END IF;
2283
2284 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2285 /* Start changes for 3586386*/
2286 IF nvl(c_rec.RESOURCE_SEQ_NUM,0)=0 AND c_rec.ALTERNATE_NUMBER > 0 THEN
2287
2288 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2289
2290 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2291
2292 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2293 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2294 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2296
2297 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2298 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_SEQ_NUM');
2299 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_SEQ_NUM));
2300 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2301
2302 FND_MESSAGE.SET_NAME('MSC','MSC_INVALID_RES_SEQ_NUM');
2303 FND_MESSAGE.SET_TOKEN('ROUTING_SEQUENCE_ID', TO_CHAR(c_rec.ROUTING_SEQUENCE_ID));
2304 FND_MESSAGE.SET_TOKEN('RESOURCE_ID', TO_CHAR(c_rec.RESOURCE_ID));
2305 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2306
2307 /* End changes for 3586386*/
2308 ELSE
2309 EXECUTE IMMEDIATE lv_sql_stmt USING
2310 c_rec.ROUTING_SEQUENCE_ID,
2311 c_rec.OPERATION_SEQUENCE_ID,
2312 c_rec.RESOURCE_SEQ_NUM,
2313 c_rec.RESOURCE_ID,
2314 c_rec.ALTERNATE_NUMBER,
2315 c_rec.PRINCIPAL_FLAG,
2316 c_rec.BASIS_TYPE,
2317 c_rec.RESOURCE_USAGE,
2318 c_rec.MAX_RESOURCE_UNITS,
2319 c_rec.RESOURCE_UNITS,
2320 c_rec.UOM_CODE,
2321 c_rec.RESOURCE_TYPE,
2322 c_rec.SR_INSTANCE_ID,
2323 c_rec.ORGANIZATION_ID,
2324 c_rec.SETUP_ID, /* ds change change start*/
2325 c_rec.MINIMUM_CAPACITY,
2326 c_rec.MAXIMUM_CAPACITY,
2327 c_rec.orig_resource_seq_num,
2328 c_rec.BREAKABLE_ACTIVITY_FLAG, /* ds change change end */
2329 MSC_CL_COLLECTION.v_last_collection_id,
2330 MSC_CL_COLLECTION.v_current_date,
2331 MSC_CL_COLLECTION.v_current_user,
2332 MSC_CL_COLLECTION.v_current_date,
2333 MSC_CL_COLLECTION.v_current_user ;
2334 END IF;
2335
2336 END IF; -- SQL%NOTFOUND
2337
2338 c_count:= c_count+1;
2339
2340 IF c_count> MSC_CL_COLLECTION.PBS THEN
2341 COMMIT;
2342 c_count:= 0;
2343 END IF;
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347
2348 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2349
2350 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2351 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2352 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2353 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2354 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2355
2356 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2357 RAISE;
2358
2359 ELSE
2360 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2361
2362 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2363 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2364 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_OPERATION_RESOURCES');
2365 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_OPERATION_RESOURCES');
2366 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2367
2368 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2369 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
2370 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
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', 'OPERATION_SEQUENCE_ID');
2375 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.OPERATION_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', 'RESOURCE_SEQ_NUM');
2380 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_SEQ_NUM));
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_ID');
2385 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.RESOURCE_ID));
2386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2387
2388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2389 END IF;
2390 END;
2391
2392 END LOOP;
2393 END IF;
2394
2395 COMMIT;
2396
2397 BEGIN
2398
2399 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
2400
2401 lv_tbl:= 'OPERATION_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
2402
2403 lv_sql_stmt:=
2404 'INSERT INTO '||lv_tbl
2405 ||' SELECT * from MSC_OPERATION_RESOURCES'
2406 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2407 ||' AND plan_id = -1 '
2408 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2409
2410 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2411 EXECUTE IMMEDIATE lv_sql_stmt;
2412
2413 COMMIT;
2414
2415 END IF;
2416
2417 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2418 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2419 lv_retcode,
2420 'MSC_OPERATION_RESOURCES',
2421 MSC_CL_COLLECTION.v_instance_code,
2422 MSC_UTIL.G_WARNING
2423 );
2424
2425 IF lv_retcode = MSC_UTIL.G_ERROR THEN
2426 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2427 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2428 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2429 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2430 END IF;
2431
2432 END IF;
2433
2434 EXCEPTION
2435 WHEN OTHERS THEN
2436
2437 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2438 RAISE;
2439 END;
2440
2441 END LOAD_OPERATION_RESOURCES;
2442
2443 ---============================================================
2444
2445
2446 END MSC_CL_ROUTING_ODS_LOAD;