[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_BOM_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_BOM_ODS_LOAD AS -- specification
2 /* $Header: MSCLBOMB.pls 120.3 2007/08/06 10:19:38 tramamoo noship $ */
3
4
5 v_sub_str VARCHAR2(4000):=NULL;
6 c_count NUMBER:= 0;
7 -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
8
9
10
11 -- PROCEDURE LOAD_RESOURCE_SETUP;
12 -- PROCEDURE LOAD_SETUP_TRANSITION;
13 -- PROCEDURE LOAD_RESOURCE_CHARGES;
14 -- PROCEDURE LOAD_RES_INST_CHANGE;
15 -- PROCEDURE LOAD_COMPONENT_SUBSTITUTE;
16 -- PROCEDURE LOAD_BOR;
17 -- PROCEDURE LOAD_PROCESS_EFFECTIVITY ;
18 -- PROCEDURE LOAD_BOM_COMPONENTS;
19 -- PROCEDURE LOAD_BOM;
20 -- PROCEDURE LOAD_RESOURCE;
21
22 PROCEDURE LOAD_RESOURCE_SETUP
23 IS
24 CURSOR res_setups IS
25 SELECT
26 mrs.ORGANIZATION_ID,
27 --mrs.DEPARTMENT_ID,
28 mrs.RESOURCE_ID,
29 mrs.SR_INSTANCE_ID,
30 mrs.setup_id,
31 mrs.setup_code,
32 mrs.setup_Description
33 FROM MSC_ST_RESOURCE_SETUPS mrs
34 WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
35 ORDER BY mrs.DELETED_FLAG;
36 lv_cnt NUMBER;
37 lv_pbs NUMBER;
38
39 c_count NUMBER := 0;
40 total_count NUMBER := 0;
41 BEGIN
42
43 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
44 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
45 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1);
46 ELSE
47 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
48 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_RESOURCE_SETUPS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
49 END IF;
50
51
52 c_count := 0;
53
54 FOR c_rec IN res_setups LOOP
55 BEGIN
56 INSERT INTO MSC_RESOURCE_SETUPS
57 ( PLAN_ID,
58 RESOURCE_ID,
59 ORGANIZATION_ID ,
60 --DEPARTMENT_ID ,
61 SR_INSTANCE_ID,
62 SETUP_ID,
63 SETUP_CODE,
64 SETUP_DESCRIPTION,
65 REFRESH_NUMBER,
66 LAST_UPDATE_DATE,
67 LAST_UPDATED_BY,
68 CREATION_DATE,
69 CREATED_BY)
70 VALUES
71 ( -1,
72 c_rec.RESOURCE_ID,
73 c_rec.ORGANIZATION_ID ,
74 --c_rec.DEPARTMENT_ID ,
75 c_rec.SR_INSTANCE_ID ,
76 c_rec. SETUP_ID,
77 c_rec.SETUP_CODE,
78 c_rec.SETUP_DESCRIPTION,
79 MSC_CL_COLLECTION.v_last_collection_id,
80 MSC_CL_COLLECTION.v_current_date,
81 MSC_CL_COLLECTION.v_current_user,
82 MSC_CL_COLLECTION.v_current_date,
83 MSC_CL_COLLECTION.v_current_user );
84
85 c_count:= c_count+1;
86 total_count:= total_count+1;
87
88 IF c_count> MSC_CL_COLLECTION.PBS THEN
89 COMMIT;
90 c_count:= 0;
91 END IF;
92
93 EXCEPTION
94
95 WHEN OTHERS THEN
96
97 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
98
99 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
100 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
101 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
102 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
104
105 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
106 RAISE;
107
108 ELSE
109 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
110
111 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
112 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
113 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_SETUP');
114 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_SETUPS');
115 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
116
117 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
118 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
119 FND_MESSAGE.SET_TOKEN('VALUE',
120 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
121 MSC_CL_COLLECTION.v_instance_id));
122 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
123
124 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
125 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
126 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
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', 'SETUP_CODE');
131 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SETUP_CODE);
132 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
133
134 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
135 END IF;
136
137 END;
138
139 END LOOP;
140 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource setups = '|| to_char(total_count));
141
142 COMMIT;
143 END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh OR v_is_partial_refresh */
144 END LOAD_RESOURCE_SETUP;
145
146 PROCEDURE LOAD_SETUP_TRANSITION
147 IS
148 CURSOR res_transitions IS
149 SELECT
150 mrs.ORGANIZATION_ID,
151 mrs.RESOURCE_ID,
152 mrs.SR_INSTANCE_ID,
153 mrs.from_setup_id,
154 mrs.to_setup_id,
155 mrs.standard_operation_id,
156 mrs.transition_time,
157 mrs.transition_uom,
158 mrs.transition_penalty
159 FROM MSC_ST_SETUP_TRANSITIONS mrs
160 WHERE mrs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
161
162 lv_cnt NUMBER;
163 lv_pbs NUMBER;
164 c_count NUMBER := 0;
165 total_count NUMBER := 0;
166 BEGIN
167
168 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (MSC_CL_COLLECTION.v_is_partial_refresh)) THEN
169 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
170 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1);
171 ELSE
172 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
173 MSC_CL_COLLECTION.DELETE_MSC_TABLE('MSC_SETUP_TRANSITIONS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
174 END IF;
175
176
177 c_count := 0;
178 total_count := 0;
179 FOR c_rec IN res_transitions LOOP
180 BEGIN
181 INSERT INTO MSC_SETUP_TRANSITIONS
182 ( PLAN_ID,
183 RESOURCE_ID,
184 ORGANIZATION_ID ,
185 SR_INSTANCE_ID,
186 FROM_SETUP_ID,
187 TO_SETUP_ID,
188 STANDARD_OPERATION_ID,
189 TRANSITION_TIME,
190 TRANSITION_UOM,
191 TRANSITION_PENALTY,
192 REFRESH_NUMBER,
193 LAST_UPDATE_DATE,
194 LAST_UPDATED_BY,
195 CREATION_DATE,
196 CREATED_BY)
197 VALUES
198 ( -1,
199 c_rec.RESOURCE_ID,
200 c_rec.ORGANIZATION_ID ,
201 c_rec.SR_INSTANCE_ID ,
202 c_rec.FROM_SETUP_ID,
203 c_rec.TO_SETUP_ID,
204 c_rec.STANDARD_OPERATION_ID,
205 c_rec.TRANSITION_TIME,
206 c_rec.TRANSITION_UOM,
207 c_rec.TRANSITION_PENALTY,
208 MSC_CL_COLLECTION.v_last_collection_id,
209 MSC_CL_COLLECTION.v_current_date,
210 MSC_CL_COLLECTION.v_current_user,
211 MSC_CL_COLLECTION.v_current_date,
212 MSC_CL_COLLECTION.v_current_user );
213
214 c_count:= c_count+1;
215 total_count:= total_count+1;
216
217 IF c_count> MSC_CL_COLLECTION.PBS THEN
218 COMMIT;
219 c_count:= 0;
220 END IF;
221
222 EXCEPTION
223
224 WHEN OTHERS THEN
225
226 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
227
228 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
229 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
230 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
231 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
232 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
233
234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
235 RAISE;
236 ELSE
237 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
238
239 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
240 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
241 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SETUP_TRANSITION');
242 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SETUP_TRANSITIONS');
243 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
244
245 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
246 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
247 FND_MESSAGE.SET_TOKEN('VALUE',
248 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
249 MSC_CL_COLLECTION.v_instance_id));
250 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
251
252 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
253 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
254 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
255 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
256
257 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
258 FND_MESSAGE.SET_TOKEN('COLUMN', 'FROM_SETUP_ID');
259 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.FROM_SETUP_ID);
260 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
261
262
263 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
264 FND_MESSAGE.SET_TOKEN('COLUMN', 'TO_SETUP_ID');
265 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.TO_SETUP_ID);
266 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
267 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
268 END IF;
269
270 END;
271
272 END LOOP;
273 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource transition = '|| to_char(total_count));
274
275 COMMIT;
276
277 END IF; /* MSC_CL_COLLECTION.v_is_complete_refresh) OR (v_is_partial_refresh */
278
279 END LOAD_SETUP_TRANSITION;
280
281 PROCEDURE LOAD_RESOURCE_CHARGES
282 IS
283 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
284 c4 CurTyp;
285
286 c_count NUMBER:=0;
287 total_count NUMBER:=0;
288 lv_tbl VARCHAR2(30);
289 lv_res_req_tbl VARCHAR2(30);
290 lv_sql_stmt VARCHAR2(5000);
291 lv_cursor_stmt VARCHAR2(5000);
292
293 lv_TRANSACTION_ID NUMBER;
294 lv_SR_INSTANCE_ID NUMBER;
295 lv_CHARGE_NUMBER NUMBER;
296 lv_CHARGE_QUANTITY NUMBER;
297 LV_DEPARTMENT_ID NUMBER;
298 lv_CHARGE_START_DATETIME DATE;
299 lv_CHARGE_END_DATETIME DATE;
300
301
302 BEGIN
303 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
304 lv_res_req_tbl := 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
305 ELSE
306 lv_res_req_tbl := 'MSC_RESOURCE_REQUIREMENTS';
307 END IF;
308
309 lv_tbl := 'MSC_RESOURCE_CHARGES';
310
311 lv_cursor_stmt:=
312 'SELECT'
313 ||' mrr.TRANSACTION_ID,'
314 ||' mrc.SR_INSTANCE_ID,'
315 ||' mrc.CHARGE_NUMBER,'
316 ||' mrc.CHARGE_QUANTITY,'
317 ||' mrc.CHARGE_START_DATETIME,'
318 ||' mrc.CHARGE_END_DATETIME,'
319 ||' mrc.DEPARTMENT_ID'
320 ||' FROM '||lv_res_req_tbl||' mrr,'
321 ||' MSC_ST_RESOURCE_CHARGES mrc'
322 ||' WHERE mrr.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
323 ||' AND mrr.PLAN_ID = -1'
324 ||' AND mrr.SR_INSTANCE_ID = mrc.SR_INSTANCE_ID'
325 ||' AND mrr.WIP_ENTITY_ID = mrc.WIP_ENTITY_ID'
326 ||' AND mrr.ORGANIZATION_ID = mrc.ORGANIZATION_ID'
327 ||' AND mrr.RESOURCE_ID = mrc.RESOURCE_ID'
328 ||' AND mrr.OPERATION_SEQ_NUM = mrc.OPERATION_SEQ_NUM'
329 ||' AND mrr.RESOURCE_SEQ_NUM = mrc.RESOURCE_SEQ_NUM';
330 /*||' AND mrr.ROUTING_SEQUENCE_ID = mrc.ROUTING_SEQUENCE_ID';*/
331
332 -- ========= Prepare SQL Statement for INSERT ==========
333 lv_sql_stmt:=
334 'insert into '||lv_tbl
335 ||' ( PLAN_ID,'
336 ||' RES_TRANSACTION_ID,'
337 ||' SR_INSTANCE_ID,'
338 ||' CHARGE_NUMBER,'
339 ||' CHARGE_QUANTITY,'
340 ||' CHARGE_START_DATETIME,'
341 ||' CHARGE_END_DATETIME,'
342 ||' LAST_UPDATE_DATE,'
343 ||' LAST_UPDATED_BY,'
344 ||' CREATION_DATE,'
345 ||' CREATED_BY)'
346 ||'VALUES'
347 ||'( -1,'
348 ||' :TRANSACTION_ID,'
349 ||' :SR_INSTANCE_ID,'
350 ||' :CHARGE_NUMBER,'
351 ||' :CHARGE_QUANTITY,'
352 ||' :CHARGE_START_DATETIME,'
353 ||' :CHARGE_END_DATETIME,'
354 ||' :v_current_date,'
355 ||' :v_current_user,'
356 ||' :v_current_date,'
357 ||' :v_current_user)';
358
359 OPEN c4 FOR lv_cursor_stmt;
360
361 LOOP
362 FETCH c4 INTO
363 lv_TRANSACTION_ID,
364 lv_SR_INSTANCE_ID,
365 lv_CHARGE_NUMBER ,
366 lv_CHARGE_QUANTITY,
367 lv_CHARGE_START_DATETIME,
368 lv_CHARGE_END_DATETIME ,
369 lv_DEPARTMENT_ID;
370
371 EXIT WHEN c4%NOTFOUND;
372
373 BEGIN
374 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) Then
375 EXECUTE IMMEDIATE lv_sql_stmt
376 USING
377 lv_TRANSACTION_ID,
378 lv_SR_INSTANCE_ID,
379 lv_CHARGE_NUMBER,
380 lv_CHARGE_QUANTITY,
381 lv_CHARGE_START_DATETIME,
382 lv_CHARGE_END_DATETIME,
383 MSC_CL_COLLECTION.v_current_date,
384 MSC_CL_COLLECTION.v_current_user,
385 MSC_CL_COLLECTION.v_current_date,
386 MSC_CL_COLLECTION.v_current_user;
387 END IF;
388
389 c_count:= c_count+1;
390 total_count:= total_count+1;
391 IF c_count> MSC_CL_COLLECTION.PBS THEN
392 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
393 COMMIT;
394 END IF;
395 c_count:= 0;
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
401
402 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
403 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
404 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
405 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
406 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
407
408 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
409 RAISE;
410
411 ELSE
412
413 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
414
415 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
416 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
417 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE_CHARGES');
418 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_CHARGES');
419 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
420
421 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
422 FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_TRANSACTION_ID');
423 FND_MESSAGE.SET_TOKEN('VALUE', lv_TRANSACTION_ID);
424 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
425
426 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
427 FND_MESSAGE.SET_TOKEN('COLUMN', 'CHARGE_NUMBER');
428 FND_MESSAGE.SET_TOKEN('VALUE', lv_CHARGE_NUMBER);
429 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
430
431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
432 END IF;
433
434 END;
435 END LOOP;
436 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total resource charges = '|| total_count);
437 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
438 COMMIT;
439 END IF;
440 EXCEPTION
441 WHEN OTHERS THEN
442 IF c4%ISOPEN THEN CLOSE c4; END IF;
443
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_RES_REQ>>');
445 IF lv_cursor_stmt IS NOT NULL THEN
446 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
447 END IF;
448 IF lv_sql_stmt IS NOT NULL THEN
449 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
450 END IF;
451 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
452 RAISE;
453 END LOAD_RESOURCE_CHARGES;
454
455 PROCEDURE LOAD_RES_INST_CHANGE IS
456
457 CURSOR res_inst_chngs IS
458 SELECT
459 msric.DEPARTMENT_ID,
460 msric.RESOURCE_ID,
461 msric.RES_INSTANCE_ID,
462 --msric.EQUIPMENT_ITEM_ID,
463 msric.SERIAL_NUMBER,
464 msric.SHIFT_NUM,
465 msric.FROM_DATE,
466 msric.TO_DATE,
467 msric.FROM_TIME,
468 msric.TO_TIME,
469 msric.CAPACITY_CHANGE,
470 msric.SIMULATION_SET,
471 msric.ACTION_TYPE,
472 msric.DELETED_FLAG,
473 msric.SR_INSTANCE_ID
474 FROM MSC_ST_RES_INSTANCE_CHANGES msric
475 WHERE msric.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
476 ORDER BY
477 msric.DELETED_FLAG;
478
479 c_count NUMBER:= 0;
480 total_count NUMBER:= 0;
481 lv_sql_stmt varchar2(500);
482 lv_dblink varchar2(50);
483 lv_dest_a2m varchar2(128);
484 lv_instance_code varchar2(10);
485
486 Begin
487
488 IF ((MSC_CL_COLLECTION.v_is_partial_refresh AND MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) OR
489 MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_incremental_refresh) then
490
491 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
492
493 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RES_INSTANCE_CHANGES', MSC_CL_COLLECTION.v_instance_id, NULL);
494
495
496 END IF;
497
498 c_count:= 0;
499 total_count:= 0;
500
501 FOR c_rec IN res_inst_chngs LOOP
502
503 BEGIN
504
505 IF MSC_CL_COLLECTION.v_is_incremental_refresh AND c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
506
507 DELETE MSC_RES_INSTANCE_CHANGES
508 WHERE DEPARTMENT_ID = c_rec.DEPARTMENT_ID
509 AND RESOURCE_ID = c_rec.RESOURCE_ID
510 AND RES_INSTANCE_ID = c_rec.RES_INSTANCE_ID
511 AND SERIAL_NUMBER = c_rec.SERIAL_NUMBER
512 --AND EQUIPMENT_ITEM_ID = c_rec.EQUIPMENT_ITEM_ID
513 AND SHIFT_NUM= c_rec.SHIFT_NUM
514 AND FROM_DATE= c_rec.FROM_DATE
515 AND NVL(TO_DATE,MSC_UTIL.NULL_DATE)= NVL(c_rec.TO_DATE,MSC_UTIL.NULL_DATE)
516 AND NVL(FROM_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.FROM_TIME,MSC_UTIL.NULL_VALUE)
517 AND NVL(TO_TIME,MSC_UTIL.NULL_VALUE)= NVL(c_rec.TO_TIME,MSC_UTIL.NULL_VALUE)
518 AND SIMULATION_SET= c_rec.SIMULATION_SET
519 AND ACTION_TYPE= c_rec.ACTION_TYPE
520 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
521
522 ELSE
523 INSERT INTO MSC_RES_INSTANCE_CHANGES
524 ( DEPARTMENT_ID,
525 RESOURCE_ID,
526 RES_INSTANCE_ID,
527 SERIAL_NUMBER,
528 -- EQUIPMENT_ITEM_ID,
529 SHIFT_NUM,
530 FROM_DATE,
531 TO_DATE,
532 FROM_TIME,
533 TO_TIME,
534 CAPACITY_CHANGE,
535 SIMULATION_SET,
536 ACTION_TYPE,
537 SR_INSTANCE_ID,
538 REFRESH_NUMBER,
539 LAST_UPDATE_DATE,
540 LAST_UPDATED_BY,
541 CREATION_DATE,
542 CREATED_BY)
543 VALUES
544 ( c_rec.DEPARTMENT_ID,
545 c_rec.RESOURCE_ID,
546 c_rec.RES_INSTANCE_ID,
547 c_rec.SERIAL_NUMBER,
548 --c_rec.EQUIPMENT_ITEM_ID,
549 c_rec.SHIFT_NUM,
550 c_rec.FROM_DATE,
551 c_rec.TO_DATE,
552 c_rec.FROM_TIME,
553 c_rec.TO_TIME,
554 c_rec.CAPACITY_CHANGE,
555 c_rec.SIMULATION_SET,
556 c_rec.ACTION_TYPE,
557 c_rec.SR_INSTANCE_ID,
558 MSC_CL_COLLECTION.v_last_collection_id,
559 MSC_CL_COLLECTION.v_current_date,
560 MSC_CL_COLLECTION.v_current_user,
561 MSC_CL_COLLECTION.v_current_date,
562 MSC_CL_COLLECTION.v_current_user );
563 END IF;
564
565 c_count:= c_count+1;
566 total_count:= total_count+1;
567
568 IF c_count> MSC_CL_COLLECTION.PBS THEN
569 COMMIT;
570 c_count:= 0;
571 END IF;
572
573 EXCEPTION
574
575 WHEN OTHERS THEN
576
577 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
578 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
579 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
580 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
581 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
582 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
583
584 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
585 RAISE;
586
587 ELSE
588 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
589
590 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
591 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
592 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_CHANGE');
593 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RES_INSTANCE_CHANGES');
594 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
595
596 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
597 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
598 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEPARTMENT_ID));
599 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
600
601 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
602 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
603 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RESOURCE_ID));
604 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
605
606
607 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
608 FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
609 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.RES_INSTANCE_ID));
610 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
611
612 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
613 FND_MESSAGE.SET_TOKEN('COLUMN', 'SERIAL_NUMBER');
614 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SERIAL_NUMBER);
615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
616
617 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
618 FND_MESSAGE.SET_TOKEN('COLUMN', 'SHIFT_NUM');
619 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SHIFT_NUM));
620 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
621
622 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
623 FND_MESSAGE.SET_TOKEN('COLUMN', 'ACTION_TYPE');
624 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.ACTION_TYPE));
625 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
626
627 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
628 FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
629 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
631
632 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
633 END IF;
634
635 END;
636
637 END LOOP;
638 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total res instance changes = '|| total_count);
639
640 COMMIT;
641
642 END IF;
643
644
645 END; /* LOAD_RES_INST_CHANGE */
646
647 --=============================================
648
649 PROCEDURE LOAD_COMPONENT_SUBSTITUTE IS
650
651
652 CURSOR c3 IS
653 SELECT
654 mscs.Bill_Sequence_ID,
655 mscs.USAGE_QUANTITY,
656 mscs.PRIORITY,
657 mscs.ROUNDING_DIRECTION,
658 mscs.ORGANIZATION_ID,
659 t1.INVENTORY_ITEM_ID SUBSTITUTE_ITEM_ID, -- mscs.SUBSTITUTE_ITEM_ID,
660 mscs.COMPONENT_SEQUENCE_ID,
661 mscs.SR_INSTANCE_ID
662 FROM MSC_ITEM_ID_LID t1,
663 MSC_ST_COMPONENT_SUBSTITUTES mscs
664 WHERE t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id
665 AND t1.sr_instance_id= mscs.sr_instance_id
666 AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
667 AND mscs.DELETED_FLAG= MSC_UTIL.SYS_NO;
668
669 CURSOR c1_d IS
670 SELECT
671 mscs.BILL_SEQUENCE_ID,
672 mscs.COMPONENT_SEQUENCE_ID,
673 t1.INVENTORY_ITEM_ID SUBSTITUTE_ITEM_ID, -- mscs.SUBSTITUTE_ITEM_ID,
674 mscs.SR_INSTANCE_ID
675 FROM MSC_ITEM_ID_LID t1,
676 MSC_ST_COMPONENT_SUBSTITUTES mscs
677 WHERE ((t1.SR_INVENTORY_ITEM_ID= mscs.substitute_item_id) AND (mscs.substitute_item_id
678 is NOT NULL))
679 AND t1.sr_instance_id= mscs.sr_instance_id
680 AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
681 AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES
682 UNION ALL
683 SELECT
684 mscs.BILL_SEQUENCE_ID,
685 mscs.COMPONENT_SEQUENCE_ID,
686 TO_NUMBER(NULL) SUBSTITUTE_ITEM_ID,
687 mscs.SR_INSTANCE_ID
688 FROM MSC_ST_COMPONENT_SUBSTITUTES mscs
689 WHERE mscs.substitute_item_id IS NULL
690 AND mscs.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
691 AND mscs.DELETED_FLAG= MSC_UTIL.SYS_YES;
692
693
694 c_count NUMBER:= 0;
695 lv_tbl VARCHAR2(30);
696 lv_sql_stmt VARCHAR2(5000);
697
698 BEGIN
699
700 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
701 -- We want to delete all BOM related data and get new stuff.
702
703 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
704
705 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
706 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1);
707 ELSE
708 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
709 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_COMPONENT_SUBSTITUTES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
710 END IF;
711
712 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
713
714 c_count:= 0;
715
716 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
717
718 FOR c_rec IN c1_d LOOP
719
720 IF c_rec.BILL_SEQUENCE_ID IS NOT NULL AND c_rec.COMPONENT_SEQUENCE_ID IS NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NULL THEN
721
722 UPDATE MSC_COMPONENT_SUBSTITUTES
723 SET USAGE_QUANTITY= 0,
724 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
725 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
726 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
727 WHERE PLAN_ID= -1
728 AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
729 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
730
731 ELSIF c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL AND c_rec.BILL_SEQUENCE_ID IS NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NULL THEN
732
733 UPDATE MSC_COMPONENT_SUBSTITUTES
734 SET USAGE_QUANTITY= 0,
735 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
736 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
737 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
738 WHERE PLAN_ID= -1
739 AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
740 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
741
742 ELSIF c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL AND c_rec.SUBSTITUTE_ITEM_ID IS NOT NULL
743 AND c_rec.BILL_SEQUENCE_ID IS NULL THEN
744
745 UPDATE MSC_COMPONENT_SUBSTITUTES
746 SET USAGE_QUANTITY= 0,
747 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
748 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
749 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
750 WHERE PLAN_ID= -1
751 AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
752 AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
753 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
754
755 END IF; /* c_rec combinations */
756
757 END LOOP; /* c1_d */
758
759 END IF; /* MSC_CL_COLLECTION.v_is_incremental_refresh */
760
761 COMMIT;
762
763 c_count:= 0;
764
765 FOR c_rec IN c3 LOOP
766
767 BEGIN
768
769 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
770
771 UPDATE MSC_COMPONENT_SUBSTITUTES
772 SET
773 USAGE_QUANTITY= c_rec.USAGE_QUANTITY,
774 PRIORITY= c_rec.PRIORITY,
775 ROUNDING_DIRECTION= c_rec.ROUNDING_DIRECTION,
776 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
777 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
778 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
779 WHERE PLAN_ID= -1
780 AND BILL_SEQUENCE_ID= c_rec.Bill_Sequence_ID
781 AND COMPONENT_SEQUENCE_ID= c_rec.COMPONENT_SEQUENCE_ID
782 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
783 AND SUBSTITUTE_ITEM_ID= c_rec.SUBSTITUTE_ITEM_ID
784 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
785
786 END IF;
787
788
789 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
790
791 INSERT INTO MSC_COMPONENT_SUBSTITUTES
792 ( PLAN_ID,
793 BILL_SEQUENCE_ID,
794 USAGE_QUANTITY,
795 PRIORITY,
796 ROUNDING_DIRECTION,
797 ORGANIZATION_ID,
798 SUBSTITUTE_ITEM_ID,
799 COMPONENT_SEQUENCE_ID,
800 SR_INSTANCE_ID,
801 REFRESH_NUMBER,
802 LAST_UPDATE_DATE,
803 LAST_UPDATED_BY,
804 CREATION_DATE,
805 CREATED_BY)
806 VALUES
807 ( -1,
808 c_rec.Bill_Sequence_ID,
809 c_rec.USAGE_QUANTITY,
810 c_rec.PRIORITY,
811 c_rec.ROUNDING_DIRECTION,
812 c_rec.ORGANIZATION_ID,
813 c_rec.SUBSTITUTE_ITEM_ID,
814 c_rec.COMPONENT_SEQUENCE_ID,
815 c_rec.SR_INSTANCE_ID,
816 MSC_CL_COLLECTION.v_last_collection_id,
817 MSC_CL_COLLECTION.v_current_date,
818 MSC_CL_COLLECTION.v_current_user,
819 MSC_CL_COLLECTION.v_current_date,
820 MSC_CL_COLLECTION.v_current_user );
821
822 END IF;
823
824 c_count:= c_count+1;
825
826 IF c_count> MSC_CL_COLLECTION.PBS THEN
827 COMMIT;
828 c_count:= 0;
829 END IF;
830
831 EXCEPTION
832
833 WHEN OTHERS THEN
834
835 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
836
837 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
838 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
839 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_COMPONENT_SUBSTITUTE');
840 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_COMPONENT_SUBSTITUTES');
841 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
842
843 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
844 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
845 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
846 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
847
848 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
849 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
850 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.COMPONENT_SEQUENCE_ID));
851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
852
853 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
854 FND_MESSAGE.SET_TOKEN('COLUMN', 'SUBSTITUTE_ITEM_NAME');
855 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.SUBSTITUTE_ITEM_ID));
856 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
857
858 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
859 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
860 FND_MESSAGE.SET_TOKEN('VALUE',
861 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
862 MSC_CL_COLLECTION.v_instance_id));
863 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
864
865 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
866
867 END;
868
869 END LOOP;
870
871 COMMIT;
872
873 END LOAD_COMPONENT_SUBSTITUTE;
874
875 PROCEDURE LOAD_BOR IS
876
877 CURSOR c1 IS
878 SELECT
879 msb.BILL_OF_RESOURCES,
880 msb.ORGANIZATION_ID,
881 msb.DESCRIPTION,
882 msb.DISABLE_DATE,
883 msb.ROLLUP_START_DATE,
884 msb.ROLLUP_COMPLETION_DATE,
885 msb.SR_INSTANCE_ID
886 FROM MSC_ST_BILL_OF_RESOURCES msb
887 WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
888
889 CURSOR c2 IS
890 SELECT
891 msbr.BILL_OF_RESOURCES,
892 msbr.RESOURCE_DEPARTMENT_HOURS,
893 msbr.OPERATION_SEQUENCE_ID,
894 msbr.OPERATION_SEQ_NUM,
895 msbr.RESOURCE_SEQ_NUM,
896 msbr.SETBACK_DAYS,
897 msbr.ASSEMBLY_USAGE,
898 msbr.ORIGINATION_TYPE,
899 msbr.RESOURCE_UNITS,
900 msbr.BASIS,
901 msbr.RESOURCE_ID,
902 msbr.DEPARTMENT_ID,
903 msbr.ORGANIZATION_ID,
904 msbr.SR_TRANSACTION_ID,
905 t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID,
906 t2.INVENTORY_ITEM_ID SOURCE_ITEM_ID,
907 msbr.SR_INSTANCE_ID
908 FROM MSC_ITEM_ID_LID t1,
909 MSC_ITEM_ID_LID t2,
910 MSC_ST_BOR_REQUIREMENTS msbr
911 WHERE t1.SR_INVENTORY_ITEM_ID= msbr.assembly_item_id
912 AND t1.sr_instance_id= msbr.sr_instance_id
913 AND t2.SR_INVENTORY_ITEM_ID= msbr.source_item_id
914 AND t2.sr_instance_id= msbr.sr_instance_id
915 AND msbr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
916
917 c_count NUMBER:= 0;
918
919 BEGIN
920
921 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
922
923 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
924 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
925
926 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
927 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
928 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1);
929 ELSE
930 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
931 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BILL_OF_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
932 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOR_REQUIREMENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
933 END IF;
934
935 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh
936
937 c_count:= 0;
938
939 FOR c_rec IN c1 LOOP
940
941 BEGIN
942
943 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
944
945 UPDATE MSC_BILL_OF_RESOURCES
946 SET
947 DESCRIPTION= c_rec.DESCRIPTION,
948 DISABLE_DATE= c_rec.DISABLE_DATE,
949 ROLLUP_START_DATE= c_rec.ROLLUP_START_DATE,
950 ROLLUP_COMPLETION_DATE= c_rec.ROLLUP_COMPLETION_DATE,
951 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
952 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
953 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
954 WHERE PLAN_ID= -1
955 AND BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES
956 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
957 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
958
959 END IF;
960
961
962 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
963
964 INSERT INTO MSC_BILL_OF_RESOURCES
965 ( PLAN_ID,
966 BILL_OF_RESOURCES,
967 ORGANIZATION_ID,
968 DESCRIPTION,
969 DISABLE_DATE,
970 ROLLUP_START_DATE,
971 ROLLUP_COMPLETION_DATE,
972 SR_INSTANCE_ID,
973 REFRESH_NUMBER,
974 LAST_UPDATE_DATE,
975 LAST_UPDATED_BY,
976 CREATION_DATE,
977 CREATED_BY)
978 VALUES
979 ( -1,
980 c_rec.BILL_OF_RESOURCES,
981 c_rec.ORGANIZATION_ID,
982 c_rec.DESCRIPTION,
983 c_rec.DISABLE_DATE,
984 c_rec.ROLLUP_START_DATE,
985 c_rec.ROLLUP_COMPLETION_DATE,
986 c_rec.SR_INSTANCE_ID,
987 MSC_CL_COLLECTION.v_last_collection_id,
988 MSC_CL_COLLECTION.v_current_date,
989 MSC_CL_COLLECTION.v_current_user,
990 MSC_CL_COLLECTION.v_current_date,
991 MSC_CL_COLLECTION.v_current_user );
992
993 END IF;
994
995 c_count:= c_count+1;
996
997 IF c_count> MSC_CL_COLLECTION.PBS THEN
998 COMMIT;
999 c_count:= 0;
1000 END IF;
1001
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004
1005 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1006
1007 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1008 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1009 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1010 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1012
1013 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1014 RAISE;
1015
1016 ELSE
1017 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1018
1019 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1020 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1021 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1022 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BILL_OF_RESOURCES');
1023 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1024
1025 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1026 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1027 FND_MESSAGE.SET_TOKEN('VALUE',
1028 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1029 MSC_CL_COLLECTION.v_instance_id));
1030 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1031
1032 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1033 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_OF_RESOURCES');
1034 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.BILL_OF_RESOURCES);
1035 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1036
1037 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1038
1039 END IF;
1040 END;
1041
1042 END LOOP;
1043
1044 COMMIT;
1045
1046 c_count:= 0;
1047
1048 FOR c_rec IN c2 LOOP
1049
1050 BEGIN
1051
1052 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1053
1054 UPDATE MSC_BOR_REQUIREMENTS
1055 SET
1056 BILL_OF_RESOURCES= c_rec.BILL_OF_RESOURCES,
1057 RESOURCE_DEPARTMENT_HOURS= c_rec.RESOURCE_DEPARTMENT_HOURS,
1058 OPERATION_SEQUENCE_ID= c_rec.OPERATION_SEQUENCE_ID,
1059 OPERATION_SEQ_NUM= c_rec.OPERATION_SEQ_NUM,
1060 RESOURCE_SEQ_NUM= c_rec.RESOURCE_SEQ_NUM,
1061 SETBACK_DAYS= c_rec.SETBACK_DAYS,
1062 ASSEMBLY_USAGE= c_rec.ASSEMBLY_USAGE,
1063 ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE,
1064 RESOURCE_UNITS= c_rec.RESOURCE_UNITS,
1065 BASIS= c_rec.BASIS,
1066 RESOURCE_ID= c_rec.RESOURCE_ID,
1067 DEPARTMENT_ID= c_rec.DEPARTMENT_ID,
1068 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
1069 ASSEMBLY_ITEM_ID= c_rec.ASSEMBLY_ITEM_ID,
1070 SOURCE_ITEM_ID= c_rec.SOURCE_ITEM_ID,
1071 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1072 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1073 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1074 WHERE PLAN_ID= -1
1075 AND SR_TRANSACTION_ID= c_rec.SR_TRANSACTION_ID
1076 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1077
1078 END IF;
1079
1080
1081 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1082
1083 INSERT INTO MSC_BOR_REQUIREMENTS
1084 ( PLAN_ID,
1085 TRANSACTION_ID,
1086 SR_TRANSACTION_ID,
1087 BILL_OF_RESOURCES,
1088 RESOURCE_DEPARTMENT_HOURS,
1089 OPERATION_SEQUENCE_ID,
1090 OPERATION_SEQ_NUM,
1091 RESOURCE_SEQ_NUM,
1092 SETBACK_DAYS,
1093 ASSEMBLY_USAGE,
1094 ORIGINATION_TYPE,
1095 RESOURCE_UNITS,
1096 BASIS,
1097 RESOURCE_ID,
1098 DEPARTMENT_ID,
1099 ORGANIZATION_ID,
1100 ASSEMBLY_ITEM_ID,
1101 SOURCE_ITEM_ID,
1102 SR_INSTANCE_ID,
1103 REFRESH_NUMBER,
1104 LAST_UPDATE_DATE,
1105 LAST_UPDATED_BY,
1106 CREATION_DATE,
1107 CREATED_BY)
1108 VALUES
1109 ( -1,
1110 MSC_BOR_REQUIREMENTS_S.NEXTVAL,
1111 c_rec.SR_TRANSACTION_ID,
1112 c_rec.BILL_OF_RESOURCES,
1113 c_rec.RESOURCE_DEPARTMENT_HOURS,
1114 c_rec.OPERATION_SEQUENCE_ID,
1115 c_rec.OPERATION_SEQ_NUM,
1116 c_rec.RESOURCE_SEQ_NUM,
1117 c_rec.SETBACK_DAYS,
1118 c_rec.ASSEMBLY_USAGE,
1119 c_rec.ORIGINATION_TYPE,
1120 c_rec.RESOURCE_UNITS,
1121 c_rec.BASIS,
1122 c_rec.RESOURCE_ID,
1123 c_rec.DEPARTMENT_ID,
1124 c_rec.ORGANIZATION_ID,
1125 c_rec.ASSEMBLY_ITEM_ID,
1126 c_rec.SOURCE_ITEM_ID,
1127 c_rec.SR_INSTANCE_ID,
1128 MSC_CL_COLLECTION.v_last_collection_id,
1129 MSC_CL_COLLECTION.v_current_date,
1130 MSC_CL_COLLECTION.v_current_user,
1131 MSC_CL_COLLECTION.v_current_date,
1132 MSC_CL_COLLECTION.v_current_user );
1133
1134 END IF;
1135
1136 c_count:= c_count+1;
1137
1138 IF c_count> MSC_CL_COLLECTION.PBS THEN
1139 COMMIT;
1140 c_count:= 0;
1141 END IF;
1142
1143 EXCEPTION
1144
1145 WHEN OTHERS THEN
1146
1147 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1148
1149 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1150 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1151 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1152 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1153 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1154
1155 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1156 RAISE;
1157
1158 ELSE
1159 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1160
1161 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1162 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1163 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOR');
1164 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOR_REQUIREMENTS');
1165 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1166
1167 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1168 FND_MESSAGE.SET_TOKEN('COLUMN', 'SR_TRANSACTION_ID');
1169 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.SR_TRANSACTION_ID));
1170 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1171
1172 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1173 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
1174 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
1175 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1176
1177 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1178 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
1179 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
1180 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1181
1182 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1183
1184 END IF;
1185 END;
1186
1187 END LOOP;
1188
1189 COMMIT;
1190
1191 END LOAD_BOR;
1192
1193 PROCEDURE LOAD_PROCESS_EFFECTIVITY IS
1194
1195 CURSOR c8 IS
1196 SELECT
1197 miil.INVENTORY_ITEM_ID, -- msor.ITEM_ID,
1198 msor.ORGANIZATION_ID,
1199 msor.EFFECTIVITY_DATE,
1200 msor.DISABLE_DATE,
1201 msor.MINIMUM_QUANTITY,
1202 msor.MAXIMUM_QUANTITY,
1203 msor.PREFERENCE,
1204 msor.ROUTING_SEQUENCE_ID,
1205 msor.BILL_SEQUENCE_ID,
1206 msor.TOTAL_PRODUCT_CYCLE_TIME,
1207 msor.LINE_ID,
1208 msor.PRIMARY_LINE_FLAG,
1209 msor.PRODUCTION_LINE_RATE,
1210 msor.LOAD_DISTRIBUTION_PRIORITY,
1211 msor.ITEM_PROCESS_COST,
1212 msor.SR_INSTANCE_ID,
1213 msor.RECIPE
1214 FROM MSC_ITEM_ID_LID miil,
1215 MSC_ST_PROCESS_EFFECTIVITY msor
1216 WHERE miil.SR_INVENTORY_ITEM_ID= msor.ITEM_ID
1217 AND miil.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1218 AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1219 AND msor.DELETED_FLAG= MSC_UTIL.SYS_NO;
1220
1221 CURSOR c8_d IS
1222 SELECT
1223 miil.INVENTORY_ITEM_ID, -- msor.ITEM_ID,
1224 msor.ORGANIZATION_ID,
1225 msor.ROUTING_SEQUENCE_ID,
1226 msor.BILL_SEQUENCE_ID,
1227 msor.LINE_ID,
1228 msor.SR_INSTANCE_ID
1229 FROM MSC_ITEM_ID_LID miil,
1230 MSC_ST_PROCESS_EFFECTIVITY msor
1231 WHERE miil.SR_INVENTORY_ITEM_ID(+)= msor.ITEM_ID
1232 AND miil.SR_INSTANCE_ID(+)= MSC_CL_COLLECTION.v_instance_id
1233 AND msor.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1234 AND msor.DELETED_FLAG= MSC_UTIL.SYS_YES
1235 UNION ALL
1236 SELECT
1237 TO_NUMBER(NULL),
1238 TO_NUMBER(NULL),
1239 mpe.ROUTING_SEQUENCE_ID,
1240 mpe.BILL_SEQUENCE_ID,
1241 TO_NUMBER(NULL),
1242 mpe.SR_INSTANCE_ID
1243 FROM MSC_PROCESS_EFFECTIVITY mpe,
1244 MSC_ST_PROCESS_EFFECTIVITY mspe
1245 WHERE mspe.Bill_Sequence_ID= mpe.Bill_Sequence_ID
1246 AND mspe.Routing_Sequence_ID <> mpe.Routing_Sequence_ID
1247 AND mspe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1248 AND mpe.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1249 AND mspe.DELETED_FLAG= MSC_UTIL.SYS_NO
1250 AND mpe.PLAN_ID= -1;
1251
1252 CURSOR c_del_leg IS
1253 SELECT
1254 msr.ROUTING_SEQUENCE_ID,
1255 msr.BILL_SEQUENCE_ID
1256 FROM MSC_ST_ROUTINGS msr
1257 WHERE msr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1258 UNION ALL
1259 SELECT
1260 TO_NUMBER(NULL),
1261 msb.BILL_SEQUENCE_ID
1262 FROM MSC_ST_BOMS msb
1263 WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
1264 c_count NUMBER:= 0;
1265 lv_tbl VARCHAR2(30);
1266 lv_sql_stmt VARCHAR2(5000);
1267 lv_sql_stmt1 VARCHAR2(5000);
1268 lv_val_1 NUMBER;
1269 lv_val_2 NUMBER;
1270 lv_val_3 NUMBER;
1271
1272 BEGIN
1273 IF MSC_CL_COLLECTION.v_instance_type = MSC_UTIL.G_INS_OTHER THEN
1274
1275 -- deleting if the record already existed.
1276 FOR c_rec IN c_del_leg LOOP
1277
1278 DELETE MSC_PROCESS_EFFECTIVITY
1279 WHERE PLAN_ID= -1
1280 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1281 AND ( ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1282 OR BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID );
1283
1284 END LOOP;
1285
1286 ELSE
1287
1288
1289 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1290
1291 FOR c_rec IN c8_d LOOP
1292 ---5470477
1293 lv_sql_stmt1 := ' DELETE MSC_PROCESS_EFFECTIVITY '
1294 ||' WHERE PLAN_ID= -1 '
1295 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
1296
1297 BEGIN --- To use bind variables as far as possible
1298
1299 lv_val_1 := -1;
1300 lv_val_2 := -1;
1301 lv_val_3 := -1;
1302
1303 IF (c_rec.LINE_ID IS NOT NULL) THEN
1304 lv_sql_stmt1 := lv_sql_stmt1 || ' AND LINE_ID= :LINE_ID ';
1305 IF (c_rec.INVENTORY_ITEM_ID IS NOT NULL AND c_rec.ORGANIZATION_ID IS NOT NULL) THEN
1306 lv_val_1 := c_rec.LINE_ID;
1307 END IF;
1308 ELSIF (c_rec.ROUTING_SEQUENCE_ID IS NOT NULL) THEN
1309 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ROUTING_SEQUENCE_ID= :ROUTING_SEQUENCE_ID';
1310 lv_val_1 := c_rec.ROUTING_SEQUENCE_ID;
1311 IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
1312 lv_sql_stmt1 := lv_sql_stmt1 || ' AND BILL_SEQUENCE_ID= ' || c_rec.BILL_SEQUENCE_ID;
1313 END IF;
1314 ELSIF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
1315 lv_sql_stmt1 := lv_sql_stmt1 || ' AND BILL_SEQUENCE_ID= :BILL_SEQUENCE_ID';
1316 lv_val_1 := c_rec.BILL_SEQUENCE_ID;
1317 END IF; --c_rec.LINE_ID
1318
1319 IF (c_rec.INVENTORY_ITEM_ID IS NOT NULL) THEN
1320 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ITEM_ID= :INVENTORY_ITEM_ID ';
1321 lv_val_2 := c_rec.INVENTORY_ITEM_ID;
1322 IF (c_rec.ORGANIZATION_ID IS NOT NULL) THEN
1323 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ORGANIZATION_ID= :ORGANIZATION_ID ';
1324 lv_val_3 := c_rec.ORGANIZATION_ID;
1325 END IF;
1326 ELSIF (c_rec.ORGANIZATION_ID IS NOT NULL) THEN
1327 lv_sql_stmt1 := lv_sql_stmt1 || ' AND ORGANIZATION_ID= :ORGANIZATION_ID ';
1328 lv_val_2 := c_rec.ORGANIZATION_ID;
1329 END IF;
1330
1331 IF lv_val_1 <> -1 THEN -- one among line_id and routing_sequence_id and bil_sequence_id is not null
1332 IF lv_val_3 <> -1 THEN --both inventory_item_id organization_id are not null
1333 EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID, lv_val_1, lv_val_2, lv_val_3 ;
1334 ELSIF lv_val_2 <> -1 THEN -- either of inventory_item_id organization_id is not null
1335 EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID, lv_val_1, lv_val_2 ;
1336 ELSE --both inventory_item_id organization_id are null
1337 EXECUTE IMMEDIATE lv_sql_stmt1 USING c_rec.SR_INSTANCE_ID, lv_val_1 ;
1338 END IF;
1339 END IF;
1340
1341
1342
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1346 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
1347 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'INVENTORY_ITEM_ID: ' || c_rec.INVENTORY_ITEM_ID);
1348 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ORGANIZATION_ID: ' || c_rec.ORGANIZATION_ID);
1349 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL_SEQUENCE_ID: ' || c_rec.BILL_SEQUENCE_ID );
1350 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ROUTING_SEQUENCE_ID: ' || c_rec.ROUTING_SEQUENCE_ID);
1351 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'LINE_ID: ' || c_rec.LINE_ID);
1352 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-----------------------------');
1353 END;
1354 /*
1355 DELETE MSC_PROCESS_EFFECTIVITY
1356 WHERE PLAN_ID= -1
1357 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1358 AND ( ( ITEM_ID= c_rec.INVENTORY_ITEM_ID
1359 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1360 AND LINE_ID= c_rec.LINE_ID)
1361 OR ROUTING_SEQUENCE_ID= c_rec.ROUTING_SEQUENCE_ID
1362 OR BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID );
1363 */
1364 END LOOP;
1365
1366 END IF;
1367
1368 END IF; -- v_instance_type = MSC_CL_COLLECTION.G_INS_OTHER
1369
1370 c_count:= 0;
1371
1372 FOR c_rec IN c8 LOOP
1373
1374 BEGIN
1375
1376 /* bug 1244578 fix */
1377
1378 UPDATE MSC_PROCESS_EFFECTIVITY
1379 SET
1380 MINIMUM_QUANTITY= c_rec.MINIMUM_QUANTITY,
1381 MAXIMUM_QUANTITY= c_rec.MAXIMUM_QUANTITY,
1382 PREFERENCE= c_rec.PREFERENCE,
1383 TOTAL_PRODUCT_CYCLE_TIME= c_rec.TOTAL_PRODUCT_CYCLE_TIME,
1384 PRIMARY_LINE_FLAG= c_rec.PRIMARY_LINE_FLAG,
1385 PRODUCTION_LINE_RATE= c_rec.PRODUCTION_LINE_RATE,
1386 LOAD_DISTRIBUTION_PRIORITY= c_rec.LOAD_DISTRIBUTION_PRIORITY,
1387 ITEM_PROCESS_COST= c_rec.ITEM_PROCESS_COST,
1388 EFFECTIVITY_DATE= c_rec.EFFECTIVITY_DATE,
1389 DISABLE_DATE= c_rec.DISABLE_DATE,
1390 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1391 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1392 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1393 RECIPE = c_rec.RECIPE
1394 WHERE PLAN_ID= -1
1395 AND ITEM_ID= c_rec.INVENTORY_ITEM_ID
1396 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1397 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
1398 AND ( ROUTING_SEQUENCE_ID = c_rec.ROUTING_SEQUENCE_ID
1399 OR ( ROUTING_SEQUENCE_ID IS NULL
1400 AND c_rec.ROUTING_SEQUENCE_ID IS NULL))
1401 AND ( BILL_SEQUENCE_ID = c_rec.BILL_SEQUENCE_ID
1402 OR ( BILL_SEQUENCE_ID IS NULL
1403 AND c_rec.BILL_SEQUENCE_ID IS NULL))
1404 AND ( LINE_ID = c_rec.LINE_ID
1405 OR ( LINE_ID IS NULL
1406 AND c_rec.LINE_ID IS NULL));
1407
1408 IF SQL%NOTFOUND THEN
1409
1410 INSERT INTO MSC_PROCESS_EFFECTIVITY
1411 ( PLAN_ID,
1412 PROCESS_SEQUENCE_ID,
1413 ITEM_ID,
1414 ORGANIZATION_ID,
1415 EFFECTIVITY_DATE,
1416 DISABLE_DATE,
1417 MINIMUM_QUANTITY,
1418 MAXIMUM_QUANTITY,
1419 PREFERENCE,
1420 ROUTING_SEQUENCE_ID,
1421 BILL_SEQUENCE_ID,
1422 TOTAL_PRODUCT_CYCLE_TIME,
1423 LINE_ID,
1424 PRIMARY_LINE_FLAG,
1425 PRODUCTION_LINE_RATE,
1426 LOAD_DISTRIBUTION_PRIORITY,
1427 ITEM_PROCESS_COST,
1428 RECIPE,
1429 SR_INSTANCE_ID,
1430 REFRESH_NUMBER,
1431 LAST_UPDATE_DATE,
1432 LAST_UPDATED_BY,
1433 CREATION_DATE,
1434 CREATED_BY)
1435 VALUES
1436 ( -1,
1437 MSC_PROCESS_EFFECTIVITY_S.NEXTVAL,
1438 c_rec.INVENTORY_ITEM_ID,
1439 c_rec.ORGANIZATION_ID,
1440 c_rec.EFFECTIVITY_DATE,
1441 c_rec.DISABLE_DATE,
1442 c_rec.MINIMUM_QUANTITY,
1443 c_rec.MAXIMUM_QUANTITY,
1444 c_rec.PREFERENCE,
1445 c_rec.ROUTING_SEQUENCE_ID,
1446 c_rec.BILL_SEQUENCE_ID,
1447 c_rec.TOTAL_PRODUCT_CYCLE_TIME,
1448 c_rec.LINE_ID,
1449 c_rec.PRIMARY_LINE_FLAG,
1450 c_rec.PRODUCTION_LINE_RATE,
1451 c_rec.LOAD_DISTRIBUTION_PRIORITY,
1452 c_rec.ITEM_PROCESS_COST,
1453 c_rec.RECIPE,
1454 c_rec.SR_INSTANCE_ID,
1455 MSC_CL_COLLECTION.v_last_collection_id,
1456 MSC_CL_COLLECTION.v_current_date,
1457 MSC_CL_COLLECTION.v_current_user,
1458 MSC_CL_COLLECTION.v_current_date,
1459 MSC_CL_COLLECTION.v_current_user );
1460
1461 END IF; -- SQL%NOTFOUND
1462
1463 c_count:= c_count+1;
1464
1465 IF c_count> MSC_CL_COLLECTION.PBS THEN
1466 COMMIT;
1467 c_count:= 0;
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472
1473 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1474
1475 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1476 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1477 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1478 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1479 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1480
1481 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1482 RAISE;
1483
1484 ELSE
1485 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1486
1487 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1488 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1489 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_PROCESS_EFFECTIVITY');
1490 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_PROCESS_EFFECTIVITY');
1491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1492
1493 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1494 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
1495 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1496 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1497
1498 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1499 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1500 FND_MESSAGE.SET_TOKEN('VALUE',
1501 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1502 MSC_CL_COLLECTION.v_instance_id));
1503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1504
1505 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1506 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1507 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.BILL_SEQUENCE_ID));
1508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1509
1510 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1511 FND_MESSAGE.SET_TOKEN('COLUMN', 'ROUTING_SEQUENCE_ID');
1512 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( c_rec.ROUTING_SEQUENCE_ID));
1513 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1514
1515 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1516 END IF;
1517 END;
1518
1519 END LOOP;
1520
1521 /* bug 1244578 fix */
1522 /* if it's complete refresh, delete the old records after the insert/update */
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
1526 /* DELETE MSC_PROCESS_EFFECTIVITY
1527 WHERE PLAN_ID= -1
1528 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1529 AND REFRESH_NUMBER <> MSC_CL_COLLECTION.v_last_collection_id; */
1530
1531 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1532 v_sub_str :='AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1533 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1534 ELSE
1535 v_sub_str :=' AND ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1536 ||' AND REFRESH_NUMBER <>'||MSC_CL_COLLECTION.v_last_collection_id;
1537 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_PROCESS_EFFECTIVITY', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1538 END IF;
1539
1540
1541
1542
1543 END IF; -- complete refresh
1544
1545 COMMIT;
1546
1547 END LOAD_PROCESS_EFFECTIVITY;
1548
1549 PROCEDURE LOAD_BOM IS
1550
1551 CURSOR c2 IS
1552 SELECT
1553 msb.ASSEMBLY_TYPE,
1554 msb.ALTERNATE_BOM_DESIGNATOR,
1555 msb.SPECIFIC_ASSEMBLY_COMMENT,
1556 msb.PENDING_FROM_ECN,
1557 msb.SCALING_TYPE,
1558 msb.ASSEMBLY_QUANTITY,
1559 msb.UOM,
1560 msb.ORGANIZATION_ID,
1561 t1.INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID, -- msb.ASSEMBLY_ITEM_ID,
1562 msb.BILL_SEQUENCE_ID,
1563 msb.SR_INSTANCE_ID,
1564 msb.operation_seq_num /* ds change for opm: yielding op seq num */
1565 FROM MSC_ITEM_ID_LID t1,
1566 MSC_ST_BOMS msb
1567 WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id
1568 AND t1.sr_instance_id= msb.sr_instance_id
1569 AND msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1570 AND msb.DELETED_FLAG= MSC_UTIL.SYS_NO;
1571
1572 CURSOR c2_d IS
1573 SELECT
1574 msb.BILL_SEQUENCE_ID,
1575 msb.SR_INSTANCE_ID
1576 FROM MSC_ST_BOMS msb
1577 WHERE msb.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1578 AND msb.DELETED_FLAG= MSC_UTIL.SYS_YES;
1579
1580
1581 lv_tbl VARCHAR2(30);
1582 lv_sql_stmt VARCHAR2(5000);
1583 lv_sql_ins VARCHAR2(6000);
1584
1585 lb_FetchComplete Boolean;
1586 lb_refresh_failed Boolean:= FALSE;
1587 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1588
1589 TYPE CharTblTyp IS TABLE OF VARCHAR2(30);
1590 TYPE CharTblTyp1 IS TABLE OF VARCHAR2(300);
1591 TYPE NumTblTyp IS TABLE OF NUMBER;
1592 TYPE dateTblTyp IS TABLE OF DATE;
1593 c_count NUMBER:= 0;
1594
1595 lb_ASSEMBLY_TYPE NumTblTyp;
1596 lb_ALTERNATE_BOM_DESIGNATOR CharTblTyp;
1597 lb_SPECIFIC_ASSEMBLY_COMMENT CharTblTyp1;
1598 lb_PENDING_FROM_ECN CharTblTyp;
1599 lb_SCALING_TYPE NumTblTyp;
1600 lb_ASSEMBLY_QUANTITY NumTblTyp;
1601 lb_UOM CharTblTyp;
1602 lb_ORGANIZATION_ID NumTblTyp;
1603 lb_ASSEMBLY_ITEM_ID NumTblTyp;
1604 lb_BILL_SEQUENCE_ID NumTblTyp;
1605 lb_SR_INSTANCE_ID NumTblTyp;
1606 lb_operation_seq_num NumTblTyp;
1607
1608 lv_errbuf VARCHAR2(240);
1609 lv_retcode NUMBER;
1610
1611 BEGIN
1612
1613 lv_sql_stmt:=
1614 'INSERT INTO MSC_BOMS '
1615 ||'( PLAN_ID,'
1616 ||' ASSEMBLY_TYPE,'
1617 ||' ALTERNATE_BOM_DESIGNATOR,'
1618 ||' SPECIFIC_ASSEMBLY_COMMENT,'
1619 ||' PENDING_FROM_ECN,'
1620 ||' SCALING_TYPE,'
1621 ||' ASSEMBLY_QUANTITY,'
1622 ||' UOM,'
1623 ||' ORGANIZATION_ID,'
1624 ||' ASSEMBLY_ITEM_ID,'
1625 ||' BILL_SEQUENCE_ID,'
1626 ||' SR_INSTANCE_ID,'
1627 ||' yielding_op_seq_num,'
1628 ||' REFRESH_NUMBER,'
1629 ||' LAST_UPDATE_DATE,'
1630 ||' LAST_UPDATED_BY,'
1631 ||' CREATION_DATE,'
1632 ||' CREATED_BY)'
1633 ||'VALUES'
1634 ||'( -1,'
1635 ||' :ASSEMBLY_TYPE,'
1636 ||' :ALTERNATE_BOM_DESIGNATOR,'
1637 ||' :SPECIFIC_ASSEMBLY_COMMENT,'
1638 ||' :PENDING_FROM_ECN,'
1639 ||' :SCALING_TYPE,'
1640 ||' :ASSEMBLY_QUANTITY,'
1641 ||' :UOM,'
1642 ||' :ORGANIZATION_ID,'
1643 ||' :ASSEMBLY_ITEM_ID,'
1644 ||' :BILL_SEQUENCE_ID,'
1645 ||' :SR_INSTANCE_ID,'
1646 ||' :operation_seq_num,'
1647 ||' :v_last_collection_id,'
1648 ||' :v_current_date,'
1649 ||' :v_current_user,'
1650 ||' :v_current_date,'
1651 ||' :v_current_user )';
1652
1653 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
1654
1655 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
1656 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1);
1657 ELSE
1658 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1659 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOMS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
1660 END IF;
1661
1662 BEGIN
1663 lv_sql_ins :=
1664 'INSERT INTO MSC_BOMS '
1665 ||'( PLAN_ID,'
1666 ||' ASSEMBLY_TYPE,'
1667 ||' ALTERNATE_BOM_DESIGNATOR,'
1668 ||' SPECIFIC_ASSEMBLY_COMMENT,'
1669 ||' PENDING_FROM_ECN,'
1670 ||' SCALING_TYPE,'
1671 ||' ASSEMBLY_QUANTITY,'
1672 ||' UOM,'
1673 ||' ORGANIZATION_ID,'
1674 ||' ASSEMBLY_ITEM_ID,'
1675 ||' BILL_SEQUENCE_ID,'
1676 ||' SR_INSTANCE_ID,'
1677 ||' yielding_op_seq_num,'
1678 ||' REFRESH_NUMBER,'
1679 ||' LAST_UPDATE_DATE,'
1680 ||' LAST_UPDATED_BY,'
1681 ||' CREATION_DATE,'
1682 ||' CREATED_BY)'
1683 ||' SELECT '
1684 ||' -1,'
1685 ||' msb.ASSEMBLY_TYPE,'
1686 ||' msb.ALTERNATE_BOM_DESIGNATOR,'
1687 ||' msb.SPECIFIC_ASSEMBLY_COMMENT,'
1688 ||' msb.PENDING_FROM_ECN,'
1689 ||' msb.SCALING_TYPE,'
1690 ||' msb.ASSEMBLY_QUANTITY,'
1691 ||' msb.UOM,'
1692 ||' msb.ORGANIZATION_ID,'
1693 ||' t1.INVENTORY_ITEM_ID,'
1694 ||' msb.BILL_SEQUENCE_ID,'
1695 ||' msb.SR_INSTANCE_ID,'
1696 ||' msb.operation_seq_num,'
1697 ||' :v_last_collection_id,'
1698 ||' :v_current_date,'
1699 ||' :v_current_user,'
1700 ||' :v_current_date,'
1701 ||' :v_current_user '
1702 ||' FROM MSC_ITEM_ID_LID t1,'
1703 ||' MSC_ST_BOMS msb '
1704 ||' WHERE t1.SR_INVENTORY_ITEM_ID= msb.assembly_item_id'
1705 ||' AND t1.sr_instance_id= msb.sr_instance_id'
1706 ||' AND msb.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1707 ||' AND msb.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
1708
1709 EXECUTE IMMEDIATE lv_sql_ins
1710 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;
1711
1712 commit;
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1716
1717 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1718 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1719 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1720 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1721 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1722
1723 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1724 RAISE;
1725
1726 ELSE
1727 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1728 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1729 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1730 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1731 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1732
1733 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1734
1735 --If Direct path load results in warning then the processing has to be
1736 --switched back to row by row processing. This will help to identify the
1737 --erroneous record and will also help in processing the rest of the records.
1738 lb_refresh_failed := TRUE;
1739 END IF;
1740 END;
1741
1742 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
1743
1744
1745
1746 --Incremental Refresh or the above complete refresh or partial refresh has
1747 --failed.
1748 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
1749
1750 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1751 FOR c_rec IN c2_d LOOP
1752 DELETE MSC_BOMS
1753 WHERE PLAN_ID= -1
1754 AND BILL_SEQUENCE_ID= c_rec.BILL_SEQUENCE_ID
1755 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
1756 END LOOP;
1757 END IF;
1758
1759 c_count:= 0;
1760
1761 OPEN c2;
1762
1763 IF (c2%ISOPEN) THEN
1764 LOOP
1765
1766 --
1767 -- Retrieve the next set of rows if we are currently not in the
1768 -- middle of processing a fetched set or rows.
1769 --
1770 IF (lb_FetchComplete) THEN
1771 EXIT;
1772 END IF;
1773
1774 FETCH c2 BULK COLLECT INTO lb_ASSEMBLY_TYPE,
1775 lb_ALTERNATE_BOM_DESIGNATOR,
1776 lb_SPECIFIC_ASSEMBLY_COMMENT,
1777 lb_PENDING_FROM_ECN,
1778 lb_SCALING_TYPE,
1779 lb_ASSEMBLY_QUANTITY,
1780 lb_UOM,
1781 lb_ORGANIZATION_ID,
1782 lb_ASSEMBLY_ITEM_ID,
1783 lb_BILL_SEQUENCE_ID,
1784 lb_SR_INSTANCE_ID,
1785 lb_operation_seq_num
1786 LIMIT ln_rows_to_fetch;
1787
1788 -- Since we are only fetching records if either (1) this is the first
1789 -- fetch or (2) the previous fetch did not retrieve all of the
1790 -- records, then at least one row should always be fetched. But
1791 -- checking just to make sure.
1792 EXIT WHEN lb_ASSEMBLY_TYPE.count = 0;
1793
1794 -- Check if all of the rows have been fetched. If so, indicate that
1795 -- the fetch is complete so that another fetch is not made.
1796 -- Additional check is introduced for the following reasons
1797 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
1798 -- unchanged after the fetch(bug#2995144)
1799 IF (c2%NOTFOUND) THEN
1800 lb_FetchComplete := TRUE;
1801 END IF;
1802
1803 FOR j IN 1..lb_ASSEMBLY_TYPE.COUNT LOOP
1804
1805 BEGIN
1806 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1807
1808 UPDATE MSC_BOMS
1809 SET
1810 ASSEMBLY_TYPE= lb_ASSEMBLY_TYPE(j),
1811 ALTERNATE_BOM_DESIGNATOR= lb_ALTERNATE_BOM_DESIGNATOR(j),
1812 SPECIFIC_ASSEMBLY_COMMENT= lb_SPECIFIC_ASSEMBLY_COMMENT(j),
1813 PENDING_FROM_ECN= lb_PENDING_FROM_ECN(j),
1814 SCALING_TYPE= lb_SCALING_TYPE(j),
1815 ASSEMBLY_QUANTITY= lb_ASSEMBLY_QUANTITY(j),
1816 UOM= lb_UOM(j),
1817 ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1818 ASSEMBLY_ITEM_ID= lb_ASSEMBLY_ITEM_ID(j),
1819 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1820 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1821 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1822 WHERE PLAN_ID= -1
1823 AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
1824 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
1825
1826 END IF;
1827
1828 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1829
1830 EXECUTE IMMEDIATE lv_sql_stmt
1831 USING
1832 lb_ASSEMBLY_TYPE(j),
1833 lb_ALTERNATE_BOM_DESIGNATOR(j),
1834 lb_SPECIFIC_ASSEMBLY_COMMENT(j),
1835 lb_PENDING_FROM_ECN(j),
1836 lb_SCALING_TYPE(j),
1837 lb_ASSEMBLY_QUANTITY(j),
1838 lb_UOM(j),
1839 lb_ORGANIZATION_ID(j),
1840 lb_ASSEMBLY_ITEM_ID(j),
1841 lb_BILL_SEQUENCE_ID(j),
1842 lb_SR_INSTANCE_ID(j),
1843 lb_operation_seq_num(j),
1844 MSC_CL_COLLECTION.v_last_collection_id,
1845 MSC_CL_COLLECTION.v_current_date,
1846 MSC_CL_COLLECTION.v_current_user,
1847 MSC_CL_COLLECTION.v_current_date,
1848 MSC_CL_COLLECTION.v_current_user;
1849 END IF; -- SQL%NOTFOUND
1850
1851 c_count:= c_count+1;
1852
1853 IF c_count> MSC_CL_COLLECTION.PBS THEN
1854 COMMIT;
1855 c_count:= 0;
1856 END IF;
1857
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
1861
1862 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1863 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1864 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1865 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1866 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1867
1868 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1869 RAISE;
1870
1871 ELSE
1872
1873 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1874
1875 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1876 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1877 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM');
1878 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOMS');
1879 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1880
1881 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1882 FND_MESSAGE.SET_TOKEN('COLUMN', 'ASSEMBLY_ITEM_NAME');
1883 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME(lb_ASSEMBLY_ITEM_ID(j)));
1884 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1885
1886 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1887 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
1888 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
1889 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1890
1891 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1892
1893 END IF;
1894 END;
1895 END LOOP;
1896 COMMIT;
1897 END LOOP;
1898 END IF;
1899 CLOSE c2;
1900 COMMIT;
1901 END IF;
1902
1903 END LOAD_BOM;
1904
1905 PROCEDURE LOAD_BOM_COMPONENTS IS
1906
1907 CURSOR c1 IS
1908 SELECT
1909 msbc.COMPONENT_SEQUENCE_ID,
1910 msbc.ORGANIZATION_ID,
1911 msbc.BILL_SEQUENCE_ID,
1912 msbc.OPERATION_SEQ_NUM,
1913 t1.INVENTORY_ITEM_ID, -- msbc.INVENTORY_ITEM_ID
1914 t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ID, -- msbc.USING_ASSEMBLY_ID
1915 msbc.COMPONENT_TYPE,
1916 msbc.SCALING_TYPE,
1917 msbc.CHANGE_NOTICE,
1918 msbc.REVISION,
1919 msbc.UOM_CODE,
1920 msbc.USAGE_QUANTITY,
1921 msbc.COMPONENT_YIELD_FACTOR,
1922 msbc.EFFECTIVITY_DATE,
1923 msbc.DISABLE_DATE,
1924 msbc.FROM_UNIT_NUMBER,
1925 msbc.TO_UNIT_NUMBER,
1926 msbc.USE_UP_CODE,
1927 msbc.SUGGESTED_EFFECTIVITY_DATE,
1928 t3.INVENTORY_ITEM_ID DRIVING_ITEM_ID, -- msbc.DRIVING_ITEM_ID,
1929 msbc.OPERATION_OFFSET_PERCENT,
1930 msbc.OPTIONAL_COMPONENT,
1931 msbc.OLD_EFFECTIVITY_DATE,
1932 msbc.WIP_SUPPLY_TYPE,
1933 msbc.PLANNING_FACTOR,
1934 msbc.ATP_FLAG,
1935 msbc.SR_INSTANCE_ID,
1936 msbc.scale_multiple,
1937 msbc.SCALE_ROUNDING_VARIANCE,
1938 msbc.ROUNDING_DIRECTION,
1939 msbc.PRIMARY_FLAG,
1940 msbc.CONTRIBUTE_TO_STEP_QTY, /* ds change */
1941 msbc.OLD_COMPONENT_SEQUENCE_ID
1942 FROM MSC_ITEM_ID_LID t1,
1943 MSC_ITEM_ID_LID t2,
1944 MSC_ITEM_ID_LID t3,
1945 MSC_ST_BOM_COMPONENTS msbc
1946 WHERE t1.SR_INVENTORY_ITEM_ID= msbc.inventory_item_id
1947 AND t1.sr_instance_id= msbc.sr_instance_id
1948 AND t2.SR_INVENTORY_ITEM_ID= msbc.using_assembly_id
1949 AND t2.sr_instance_id= msbc.sr_instance_id
1950 AND t3.SR_INVENTORY_ITEM_ID(+)= msbc.driving_item_id
1951 AND t3.sr_instance_id(+)= msbc.sr_instance_id
1952 AND msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1953 AND msbc.DELETED_FLAG= MSC_UTIL.SYS_NO;
1954
1955 CURSOR c1_d IS
1956 SELECT
1957 msbc.COMPONENT_SEQUENCE_ID,
1958 msbc.BILL_SEQUENCE_ID,
1959 msbc.SR_INSTANCE_ID
1960 FROM MSC_ST_BOM_COMPONENTS msbc
1961 WHERE msbc.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1962 AND msbc.DELETED_FLAG= MSC_UTIL.SYS_YES;
1963
1964
1965 lv_tbl VARCHAR2(30);
1966 lv_sql_stmt VARCHAR2(5000);
1967 lv_sql_stmt1 VARCHAR2(5000);
1968 lv_sql_ins vARCHAR2(6000);
1969
1970 lb_FetchComplete Boolean;
1971 lb_refresh_failed Boolean:= FALSE;
1972 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1973
1974
1975 TYPE CharTblTyp IS TABLE OF VARCHAR2(30);
1976 TYPE NumTblTyp IS TABLE OF NUMBER;
1977 TYPE dateTblTyp IS TABLE OF DATE;
1978
1979 lb_COMPONENT_SEQUENCE_ID NumTblTyp;
1980 lb_ORGANIZATION_ID NumTblTyp;
1981 lb_BILL_SEQUENCE_ID NumTblTyp;
1982 lb_OPERATION_SEQ_NUM NumTblTyp;
1983 lb_INVENTORY_ITEM_ID NumTblTyp;
1984 lb_USING_ASSEMBLY_ID NumTblTyp;
1985 lb_COMPONENT_TYPE NumTblTyp;
1986 lb_SCALING_TYPE NumTblTyp;
1987 lb_CHANGE_NOTICE CharTblTyp;
1988 lb_REVISION CharTblTyp;
1989 lb_UOM_CODE CharTblTyp;
1990 lb_USAGE_QUANTITY NumTblTyp;
1991 lb_COMPONENT_YIELD_FACTOR NumTblTyp;
1992 lb_EFFECTIVITY_DATE dateTblTyp;
1993 lb_DISABLE_DATE dateTblTyp;
1994 lb_FROM_UNIT_NUMBER CharTblTyp;
1995 lb_TO_UNIT_NUMBER CharTblTyp;
1996 lb_USE_UP_CODE NumTblTyp;
1997 lb_SUGGESTED_EFFECTIVITY_DATE dateTblTyp;
1998 lb_DRIVING_ITEM_ID NumTblTyp;
1999 lb_OPERATION_OFFSET_PERCENT NumTblTyp;
2000 lb_OPTIONAL_COMPONENT NumTblTyp;
2001 lb_OLD_EFFECTIVITY_DATE dateTblTyp;
2002 lb_WIP_SUPPLY_TYPE NumTblTyp;
2003 lb_PLANNING_FACTOR NumTblTyp;
2004 lb_ATP_FLAG NumTblTyp;
2005 lb_SR_INSTANCE_ID NumTblTyp;
2006 lb_scale_multiple NumTblTyp;
2007 lb_SCALE_ROUNDING_VARIANCE NumTblTyp;
2008 lb_ROUNDING_DIRECTION NumTblTyp;
2009 lb_PRIMARY_FLAG NumTblTyp;
2010 lb_CONTRIBUTE_TO_STEP_QTY NumTblTyp; /* ds change */
2011 lb_OLD_COMPONENT_SEQUENCE_ID NumTblTyp;
2012
2013 lv_errbuf VARCHAR2(240);
2014 lv_retcode NUMBER;
2015
2016 BEGIN
2017
2018
2019 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2020 lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2021 ELSE
2022 lv_tbl:= 'MSC_BOM_COMPONENTS';
2023 END IF;
2024
2025 lv_sql_stmt:=
2026 ' insert into '||lv_tbl
2027 ||'( PLAN_ID,'
2028 ||' COMPONENT_SEQUENCE_ID,'
2029 ||' ORGANIZATION_ID,'
2030 ||' BILL_SEQUENCE_ID,'
2031 ||' OPERATION_SEQ_NUM,'
2032 ||' INVENTORY_ITEM_ID,'
2033 ||' USING_ASSEMBLY_ID,'
2034 ||' COMPONENT_TYPE,'
2035 ||' SCALING_TYPE,'
2036 ||' CHANGE_NOTICE,'
2037 ||' REVISION,'
2038 ||' UOM_CODE,'
2039 ||' USAGE_QUANTITY,'
2040 ||' COMPONENT_YIELD_FACTOR,'
2041 ||' EFFECTIVITY_DATE,'
2042 ||' DISABLE_DATE,'
2043 ||' FROM_UNIT_NUMBER,'
2044 ||' TO_UNIT_NUMBER,'
2045 ||' USE_UP_CODE,'
2046 ||' SUGGESTED_EFFECTIVITY_DATE,'
2047 ||' DRIVING_ITEM_ID,'
2048 ||' OPERATION_OFFSET_PERCENT,'
2049 ||' OPTIONAL_COMPONENT,'
2050 ||' OLD_EFFECTIVITY_DATE,'
2051 ||' WIP_SUPPLY_TYPE,'
2052 ||' PLANNING_FACTOR,'
2053 ||' ATP_FLAG,'
2054 ||' SR_INSTANCE_ID,'
2055 ||' SCALE_MULTIPLE,'
2056 ||' SCALE_ROUNDING_VARIANCE,'
2057 ||' ROUNDING_DIRECTION,'
2058 ||' PRIMARY_FLAG,'
2059 ||' CONTRIBUTE_TO_STEP_QTY,'
2060 ||' OLD_COMPONENT_SEQUENCE_ID,'
2061 ||' REFRESH_NUMBER,'
2062 ||' LAST_UPDATE_DATE,'
2063 ||' LAST_UPDATED_BY,'
2064 ||' CREATION_DATE,'
2065 ||' CREATED_BY)'
2066 ||'VALUES'
2067 ||'( -1,'
2068 ||' :COMPONENT_SEQUENCE_ID,'
2069 ||' :ORGANIZATION_ID,'
2070 ||' :BILL_SEQUENCE_ID,'
2071 ||' :OPERATION_SEQ_NUM,'
2072 ||' :INVENTORY_ITEM_ID,'
2073 ||' :USING_ASSEMBLY_ID,'
2074 ||' :COMPONENT_TYPE,'
2075 ||' :SCALING_TYPE,'
2076 ||' :CHANGE_NOTICE,'
2077 ||' :REVISION,'
2078 ||' :UOM_CODE,'
2079 ||' :USAGE_QUANTITY,'
2080 ||' :COMPONENT_YIELD_FACTOR,'
2081 ||' :EFFECTIVITY_DATE,'
2082 ||' :DISABLE_DATE,'
2083 ||' :FROM_UNIT_NUMBER,'
2084 ||' :TO_UNIT_NUMBER,'
2085 ||' :USE_UP_CODE,'
2086 ||' :SUGGESTED_EFFECTIVITY_DATE,'
2087 ||' :DRIVING_ITEM_ID,'
2088 ||' :OPERATION_OFFSET_PERCENT,'
2089 ||' :OPTIONAL_COMPONENT,'
2090 ||' :OLD_EFFECTIVITY_DATE,'
2091 ||' :WIP_SUPPLY_TYPE,'
2092 ||' :PLANNING_FACTOR,'
2093 ||' :ATP_FLAG,'
2094 ||' :SR_INSTANCE_ID,'
2095 ||' :SCALE_MULTIPLE,'
2096 ||' :SCALE_ROUNDING_VARIANCE,'
2097 ||' :ROUNDING_DIRECTION,'
2098 ||' :PRIMARY_FLAG,'
2099 ||' :CONTRIBUTE_TO_STEP_QTY,'
2100 ||' :OLD_COMPONENT_SEQUENCE_ID,'
2101 ||' :v_last_collection_id,'
2102 ||' :v_current_date,'
2103 ||' :v_current_user,'
2104 ||' :v_current_date,'
2105 ||' :v_current_user)';
2106
2107 --fix for the bug#3283959
2108 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2109
2110 if (MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_NO) then
2111 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2112 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2113 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1);
2114 ELSE
2115 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2116 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_BOM_COMPONENTS', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2117 END IF;
2118 end if;
2119
2120 BEGIN
2121 lv_sql_ins :=
2122 ' INSERT /*+ append */ '
2123 || ' INTO '||lv_tbl
2124 ||'( PLAN_ID,'
2125 ||' COMPONENT_SEQUENCE_ID,'
2126 ||' ORGANIZATION_ID,'
2127 ||' BILL_SEQUENCE_ID,'
2128 ||' OPERATION_SEQ_NUM,'
2129 ||' INVENTORY_ITEM_ID,'
2130 ||' USING_ASSEMBLY_ID,'
2131 ||' COMPONENT_TYPE,'
2132 ||' SCALING_TYPE,'
2133 ||' CHANGE_NOTICE,'
2134 ||' REVISION,'
2135 ||' UOM_CODE,'
2136 ||' USAGE_QUANTITY,'
2137 ||' COMPONENT_YIELD_FACTOR,'
2138 ||' EFFECTIVITY_DATE,'
2139 ||' DISABLE_DATE,'
2140 ||' FROM_UNIT_NUMBER,'
2141 ||' TO_UNIT_NUMBER,'
2142 ||' USE_UP_CODE,'
2143 ||' SUGGESTED_EFFECTIVITY_DATE,'
2144 ||' DRIVING_ITEM_ID,'
2145 ||' OPERATION_OFFSET_PERCENT,'
2146 ||' OPTIONAL_COMPONENT,'
2147 ||' OLD_EFFECTIVITY_DATE,'
2148 ||' WIP_SUPPLY_TYPE,'
2149 ||' PLANNING_FACTOR,'
2150 ||' ATP_FLAG,'
2151 ||' SR_INSTANCE_ID,'
2152 ||' SCALE_MULTIPLE,'
2153 ||' SCALE_ROUNDING_VARIANCE,'
2154 ||' ROUNDING_DIRECTION,'
2155 ||' PRIMARY_FLAG,'
2156 ||' CONTRIBUTE_TO_STEP_QTY,'
2157 ||' OLD_COMPONENT_SEQUENCE_ID,'
2158 ||' REFRESH_NUMBER,'
2159 ||' LAST_UPDATE_DATE,'
2160 ||' LAST_UPDATED_BY,'
2161 ||' CREATION_DATE,'
2162 ||' CREATED_BY)'
2163 ||' SELECT '
2164 ||' -1,'
2165 ||' msbc.COMPONENT_SEQUENCE_ID,'
2166 ||' msbc.ORGANIZATION_ID,'
2167 ||' msbc.BILL_SEQUENCE_ID,'
2168 ||' msbc.OPERATION_SEQ_NUM,'
2169 ||' t1.INVENTORY_ITEM_ID, '
2170 ||' t2.INVENTORY_ITEM_ID,'
2171 ||' msbc.COMPONENT_TYPE,'
2172 ||' msbc.SCALING_TYPE,'
2173 ||' msbc.CHANGE_NOTICE,'
2174 ||' msbc.REVISION,'
2175 ||' msbc.UOM_CODE,'
2176 ||' msbc.USAGE_QUANTITY,'
2177 ||' msbc.COMPONENT_YIELD_FACTOR,'
2178 ||' msbc.EFFECTIVITY_DATE,'
2179 ||' msbc.DISABLE_DATE,'
2180 ||' msbc.FROM_UNIT_NUMBER,'
2181 ||' msbc.TO_UNIT_NUMBER,'
2182 ||' msbc.USE_UP_CODE,'
2183 ||' msbc.SUGGESTED_EFFECTIVITY_DATE,'
2184 ||' t3.INVENTORY_ITEM_ID,'
2185 ||' msbc.OPERATION_OFFSET_PERCENT,'
2186 ||' msbc.OPTIONAL_COMPONENT,'
2187 ||' msbc.OLD_EFFECTIVITY_DATE,'
2188 ||' msbc.WIP_SUPPLY_TYPE,'
2189 ||' msbc.PLANNING_FACTOR,'
2190 ||' msbc.ATP_FLAG,'
2191 ||' msbc.SR_INSTANCE_ID,'
2192 ||' msbc.scale_multiple,'
2193 ||' msbc.SCALE_ROUNDING_VARIANCE,'
2194 ||' msbc.ROUNDING_DIRECTION,'
2195 ||' msbc.PRIMARY_FLAG, '
2196 ||' msbc.CONTRIBUTE_TO_STEP_QTY, '
2197 ||' msbc.OLD_COMPONENT_SEQUENCE_ID,'
2198 ||' :v_last_collection_id, '
2199 ||' :v_current_date , '
2200 ||' :v_current_user , '
2201 ||' :v_current_date , '
2202 ||' :v_current_user '
2203 ||' FROM MSC_ITEM_ID_LID t1,'
2204 ||' MSC_ITEM_ID_LID t2,'
2205 ||' MSC_ITEM_ID_LID t3,'
2206 ||' MSC_ST_BOM_COMPONENTS msbc'
2207 ||' WHERE t1.SR_INVENTORY_ITEM_ID = msbc.inventory_item_id'
2208 ||' AND t1.sr_instance_id = msbc.sr_instance_id'
2209 ||' AND t2.SR_INVENTORY_ITEM_ID = msbc.using_assembly_id'
2210 ||' AND t2.sr_instance_id = msbc.sr_instance_id'
2211 ||' AND t3.SR_INVENTORY_ITEM_ID(+) = msbc.driving_item_id'
2212 ||' AND t3.sr_instance_id(+) = msbc.sr_instance_id'
2213 ||' AND msbc.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2214 ||' AND msbc.DELETED_FLAG = '||MSC_UTIL.SYS_NO;
2215
2216 EXECUTE IMMEDIATE lv_sql_ins
2217 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;
2218
2219 commit;
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2223
2224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2225 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2226 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2227 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2228 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2229
2230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2231 RAISE;
2232
2233 ELSE
2234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2235 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2236 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2237 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2238 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2239
2240 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2241
2242 --If Direct path load results in warning then the processing has to be
2243 --switched back to row by row processing. This will help to identify the
2244 --erroneous record and will also help in processing the rest of the records.
2245 lb_refresh_failed := TRUE;
2246 END IF;
2247 END;
2248
2249 END IF; --MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2250
2251 --Incremental Refresh or the above complete refresh or partial refresh has
2252 --failed.
2253 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2254
2255 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2256
2257 -- set SR_INSTANCE_ID to negative to indicate a SOFT delete
2258
2259 FOR c_rec IN c1_d LOOP
2260 ---5470477
2261 lv_sql_stmt1 := ' UPDATE MSC_BOM_COMPONENTS '
2262 ||' SET USAGE_QUANTITY= 0, '
2263 ||' REFRESH_NUMBER= :v_last_collection_id , '
2264 ||' LAST_UPDATE_DATE= :v_current_date , '
2265 ||' LAST_UPDATED_BY= :v_current_user '
2266 ||' WHERE PLAN_ID= -1 '
2267 ||' AND SR_INSTANCE_ID= :SR_INSTANCE_ID ';
2268
2269 IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2270 lv_sql_stmt1 := lv_sql_stmt1 || ' AND BILL_SEQUENCE_ID= :BILL_SEQUENCE_ID ';
2271 END IF;
2272
2273 IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2274 lv_sql_stmt1 := lv_sql_stmt1 || ' AND COMPONENT_SEQUENCE_ID= :COMPONENT_SEQUENCE_ID ';
2275 END IF;
2276 BEGIN
2277 IF (c_rec.BILL_SEQUENCE_ID IS NOT NULL) THEN
2278 IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2279 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, c_rec.BILL_SEQUENCE_ID , c_rec.COMPONENT_SEQUENCE_ID ;
2280 ELSE
2281 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, c_rec.BILL_SEQUENCE_ID;
2282 END IF;
2283 ELSE
2284 IF (c_rec.COMPONENT_SEQUENCE_ID IS NOT NULL) THEN
2285 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, c_rec.COMPONENT_SEQUENCE_ID ;
2286 /*ELSE --condition should not arise. even if it does, we should not delete
2287 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; */
2288 END IF;
2289
2290 END IF;
2291
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2295 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL Executed: ' || lv_sql_stmt1 );
2296 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'BILL SEQUENCE ID: ' || c_rec.BILL_SEQUENCE_ID );
2297 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'COMPONENT SEQUENCE ID: ' || c_rec.COMPONENT_SEQUENCE_ID);
2298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
2299 END;
2300
2301 /*
2302 UPDATE MSC_BOM_COMPONENTS
2303 SET USAGE_QUANTITY= 0,
2304 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2305 LAST_UPDATE_DATE= v_current_date,
2306 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2307 WHERE PLAN_ID= -1
2308 AND BILL_SEQUENCE_ID= NVL(c_rec.BILL_SEQUENCE_ID, BILL_SEQUENCE_ID)
2309 AND COMPONENT_SEQUENCE_ID= NVL(c_rec.COMPONENT_SEQUENCE_ID, COMPONENT_SEQUENCE_ID)
2310 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2311 */
2312 END LOOP;
2313
2314 END IF;
2315
2316
2317 OPEN c1;
2318
2319 IF (c1%ISOPEN) THEN
2320 LOOP
2321
2322 --
2323 -- Retrieve the next set of rows if we are currently not in the
2324 -- middle of processing a fetched set or rows.
2325 --
2326 IF (lb_FetchComplete) THEN
2327 EXIT;
2328 END IF;
2329
2330 FETCH c1 BULK COLLECT INTO lb_COMPONENT_SEQUENCE_ID,
2331 lb_ORGANIZATION_ID,
2332 lb_BILL_SEQUENCE_ID,
2333 lb_OPERATION_SEQ_NUM,
2334 lb_INVENTORY_ITEM_ID,
2335 lb_USING_ASSEMBLY_ID,
2336 lb_COMPONENT_TYPE,
2337 lb_SCALING_TYPE,
2338 lb_CHANGE_NOTICE,
2339 lb_REVISION,
2340 lb_UOM_CODE,
2341 lb_USAGE_QUANTITY,
2342 lb_COMPONENT_YIELD_FACTOR,
2343 lb_EFFECTIVITY_DATE,
2344 lb_DISABLE_DATE,
2345 lb_FROM_UNIT_NUMBER,
2346 lb_TO_UNIT_NUMBER,
2347 lb_USE_UP_CODE,
2348 lb_SUGGESTED_EFFECTIVITY_DATE,
2349 lb_DRIVING_ITEM_ID,
2350 lb_OPERATION_OFFSET_PERCENT,
2351 lb_OPTIONAL_COMPONENT,
2352 lb_OLD_EFFECTIVITY_DATE,
2353 lb_WIP_SUPPLY_TYPE,
2354 lb_PLANNING_FACTOR,
2355 lb_ATP_FLAG,
2356 lb_SR_INSTANCE_ID,
2357 lb_scale_multiple,
2358 lb_SCALE_ROUNDING_VARIANCE,
2359 lb_ROUNDING_DIRECTION,
2360 lb_PRIMARY_FLAG,
2361 lb_CONTRIBUTE_TO_STEP_QTY,
2362 lb_OLD_COMPONENT_SEQUENCE_ID
2363 LIMIT ln_rows_to_fetch;
2364
2365 -- Since we are only fetching records if either (1) this is the first
2366 -- fetch or (2) the previous fetch did not retrieve all of the
2367 -- records, then at least one row should always be fetched. But
2368 -- checking just to make sure.
2369 EXIT WHEN lb_COMPONENT_SEQUENCE_ID.count = 0;
2370
2371 -- Check if all of the rows have been fetched. If so, indicate that
2372 -- the fetch is complete so that another fetch is not made.
2373 -- Additional check is introduced for the following reasons
2374 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
2375 -- unchanged after the fetch(bug#2995144)
2376 IF (c1%NOTFOUND) THEN
2377 lb_FetchComplete := TRUE;
2378 END IF;
2379
2380 FOR j IN 1..lb_COMPONENT_SEQUENCE_ID.COUNT LOOP
2381
2382 BEGIN
2383
2384 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2385
2386 UPDATE MSC_BOM_COMPONENTS
2387 SET
2388 ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
2389 INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j),
2390 USING_ASSEMBLY_ID= lb_USING_ASSEMBLY_ID(j),
2391 OPERATION_SEQ_NUM = lb_OPERATION_SEQ_NUM(j),
2392 COMPONENT_TYPE= lb_COMPONENT_TYPE(j),
2393 SCALING_TYPE= lb_SCALING_TYPE(j),
2394 CHANGE_NOTICE= lb_CHANGE_NOTICE(j),
2395 REVISION= lb_REVISION(j),
2396 UOM_CODE= lb_UOM_CODE(j),
2397 USAGE_QUANTITY= lb_USAGE_QUANTITY(j),
2398 COMPONENT_YIELD_FACTOR= lb_COMPONENT_YIELD_FACTOR(j),
2399 EFFECTIVITY_DATE= lb_EFFECTIVITY_DATE(j),
2400 DISABLE_DATE= lb_DISABLE_DATE(j),
2401 FROM_UNIT_NUMBER= lb_FROM_UNIT_NUMBER(j),
2402 TO_UNIT_NUMBER= lb_TO_UNIT_NUMBER(j),
2403 USE_UP_CODE= lb_USE_UP_CODE(j),
2404 SUGGESTED_EFFECTIVITY_DATE= lb_SUGGESTED_EFFECTIVITY_DATE(j),
2405 DRIVING_ITEM_ID= lb_DRIVING_ITEM_ID(j),
2406 OPERATION_OFFSET_PERCENT= lb_OPERATION_OFFSET_PERCENT(j),
2407 OPTIONAL_COMPONENT= lb_OPTIONAL_COMPONENT(j),
2408 OLD_EFFECTIVITY_DATE= lb_OLD_EFFECTIVITY_DATE(j),
2409 WIP_SUPPLY_TYPE= lb_WIP_SUPPLY_TYPE(j),
2410 PLANNING_FACTOR= lb_PLANNING_FACTOR(j),
2411 ATP_FLAG= lb_ATP_FLAG(j),
2412 SCALE_MULTIPLE = lb_scale_multiple(j),
2413 SCALE_ROUNDING_VARIANCE = lb_SCALE_ROUNDING_VARIANCE(j),
2414 ROUNDING_DIRECTION = lb_ROUNDING_DIRECTION(j),
2415 PRIMARY_FLAG = lb_PRIMARY_FLAG(j),
2416 CONTRIBUTE_TO_STEP_QTY = lb_CONTRIBUTE_TO_STEP_QTY(j),
2417 OLD_COMPONENT_SEQUENCE_ID = lb_OLD_COMPONENT_SEQUENCE_ID(j),
2418 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2419 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2420 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2421 WHERE PLAN_ID= -1
2422 AND BILL_SEQUENCE_ID= lb_BILL_SEQUENCE_ID(j)
2423 AND COMPONENT_SEQUENCE_ID= lb_COMPONENT_SEQUENCE_ID(j)
2424 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j);
2425
2426 END IF;
2427
2428 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2429
2430 EXECUTE IMMEDIATE lv_sql_stmt
2431 USING
2432 lb_COMPONENT_SEQUENCE_ID(j),
2433 lb_ORGANIZATION_ID(j),
2434 lb_BILL_SEQUENCE_ID(j),
2435 lb_OPERATION_SEQ_NUM(j),
2436 lb_INVENTORY_ITEM_ID(j),
2437 lb_USING_ASSEMBLY_ID(j),
2438 lb_COMPONENT_TYPE(j),
2439 lb_SCALING_TYPE(j),
2440 lb_CHANGE_NOTICE(j),
2441 lb_REVISION(j),
2442 lb_UOM_CODE(j),
2443 lb_USAGE_QUANTITY(j),
2444 lb_COMPONENT_YIELD_FACTOR(j),
2445 lb_EFFECTIVITY_DATE(j),
2446 lb_DISABLE_DATE(j),
2447 lb_FROM_UNIT_NUMBER(j),
2448 lb_TO_UNIT_NUMBER(j),
2449 lb_USE_UP_CODE(j),
2450 lb_SUGGESTED_EFFECTIVITY_DATE(j),
2451 lb_DRIVING_ITEM_ID(j),
2452 lb_OPERATION_OFFSET_PERCENT(j),
2453 lb_OPTIONAL_COMPONENT(j),
2454 lb_OLD_EFFECTIVITY_DATE(j),
2455 lb_WIP_SUPPLY_TYPE(j),
2456 lb_PLANNING_FACTOR(j),
2457 lb_ATP_FLAG(j),
2458 lb_SR_INSTANCE_ID(j),
2459 lb_scale_multiple(j),
2460 lb_SCALE_ROUNDING_VARIANCE(j),
2461 lb_ROUNDING_DIRECTION(j),
2462 lb_PRIMARY_FLAG(j),
2463 lb_CONTRIBUTE_TO_STEP_QTY(j),
2464 lb_OLD_COMPONENT_SEQUENCE_ID(j),
2465 MSC_CL_COLLECTION.v_last_collection_id,
2466 MSC_CL_COLLECTION.v_current_date,
2467 MSC_CL_COLLECTION.v_current_user,
2468 MSC_CL_COLLECTION.v_current_date,
2469 MSC_CL_COLLECTION.v_current_user;
2470 END IF; -- SQL%NOTFOUND
2471
2472
2473
2474 EXCEPTION
2475 WHEN OTHERS THEN
2476 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2477
2478 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2479 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2480 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2481 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2482 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2483
2484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2485 RAISE;
2486
2487 ELSE
2488
2489 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2490
2491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_BOM_COMPONENTS');
2494 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_BOM_COMPONENTS');
2495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2496
2497 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2498 FND_MESSAGE.SET_TOKEN('COLUMN', 'BILL_SEQUENCE_ID');
2499 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_BILL_SEQUENCE_ID(j)));
2500 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2501
2502 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2503 FND_MESSAGE.SET_TOKEN('COLUMN', 'COMPONENT_SEQUENCE_ID');
2504 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR( lb_COMPONENT_SEQUENCE_ID(j)));
2505 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2506
2507 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2508
2509 END IF;
2510 END;
2511
2512 END LOOP;
2513 COMMIT;
2514 END LOOP;
2515 END IF;
2516 CLOSE c1;
2517 COMMIT;
2518 END IF;
2519
2520 BEGIN
2521
2522 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
2523
2524 lv_tbl:= 'BOM_COMPONENTS_'||MSC_CL_COLLECTION.v_instance_code;
2525
2526 lv_sql_stmt:=
2527 'INSERT INTO '||lv_tbl
2528 ||' SELECT * from MSC_BOM_COMPONENTS'
2529 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2530 ||' AND plan_id = -1 '
2531 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2532
2533 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2534 EXECUTE IMMEDIATE lv_sql_stmt;
2535
2536 COMMIT;
2537
2538 END IF;
2539
2540 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2541 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2542 lv_retcode,
2543 'MSC_BOM_COMPONENTS',
2544 MSC_CL_COLLECTION.v_instance_code,
2545 MSC_UTIL.G_WARNING
2546 );
2547
2548 IF lv_retcode = MSC_UTIL.G_ERROR THEN
2549 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2550 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2551 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2552 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2553 END IF;
2554
2555 END IF;
2556
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559
2560 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2561 RAISE;
2562 END;
2563
2564 END LOAD_BOM_COMPONENTS;
2565
2566 PROCEDURE LOAD_RESOURCE IS
2567
2568 CURSOR c1 IS
2569 SELECT
2570 msdr.RESOURCE_CODE,
2571 msdr.DEPARTMENT_CODE,
2572 msdr.DEPARTMENT_DESCRIPTION,
2573 msdr.RESOURCE_DESCRIPTION,
2574 msdr.DEPARTMENT_CLASS,
2575 msdr.LINE_FLAG,
2576 msdr.CAPACITY_UNITS,
2577 msdr.MAX_RATE,
2578 msdr.MIN_RATE,
2579 msdr.AGGREGATED_RESOURCE_ID,
2580 msdr.AGGREGATED_RESOURCE_FLAG,
2581 msdr.RESOURCE_GROUP_NAME,
2582 msdr.RESOURCE_GROUP_CODE,
2583 msdr.RESOURCE_BALANCE_FLAG,
2584 msdr.BOTTLENECK_FLAG,
2585 msdr.DEPARTMENT_ID,
2586 msdr.OWNING_DEPARTMENT_ID,
2587 msdr.ORGANIZATION_ID,
2588 msdr.RESOURCE_ID,
2589 msdr.SR_INSTANCE_ID,
2590 msdr.OVER_UTILIZED_PERCENT,
2591 msdr.UNDER_UTILIZED_PERCENT,
2592 msdr.RESOURCE_SHORTAGE_TYPE,
2593 msdr.RESOURCE_EXCESS_TYPE,
2594 msdr.PLANNING_EXCEPTION_SET,
2595 msdr.USER_TIME_FENCE,
2596 msdr.UTILIZATION,
2597 msdr.EFFICIENCY,
2598 msdr.BATCHABLE_FLAG,
2599 msdr.BATCHING_WINDOW,
2600 msdr.MIN_CAPACITY,
2601 msdr.MAX_CAPACITY,
2602 msdr.UNIT_OF_MEASURE,
2603 msdr.RESOURCE_INCLUDE_FLAG,
2604 msdr.CRITICAL_RESOURCE_FLAG,
2605 msdr.RESOURCE_TYPE,
2606 msdr.DISABLE_DATE,
2607 msdr.AVAILABLE_24_HOURS_FLAG,
2608 msdr.CTP_FLAG,
2609 msdr.START_TIME,
2610 msdr.STOP_TIME,
2611 msdr.RESOURCE_COST,
2612 msdr.RESOURCE_OVER_UTIL_COST,
2613 msdr.DEPT_OVERHEAD_COST,
2614 msdr.ATP_RULE_ID,
2615 msdr.DELETED_FLAG,
2616 msdr.CAPACITY_TOLERANCE, /* ds change change start */
2617 msdr.CHARGEABLE_FLAG,
2618 msdr.IDLE_TIME_TOLERANCE,
2619 msdr.BATCHING_PENALTY,
2620 msdr.SCHEDULE_TO_INSTANCE,
2621 msdr.LAST_KNOWN_SETUP,
2622 msdr.SETUP_TIME_PERCENT,
2623 msdr.UTILIZATION_CHANGE_PERCENT,
2624 msdr.SETUP_TIME_TYPE,
2625 msdr.UTILIZATION_CHANGE_TYPE ,
2626 msdr.SDS_SCHEDULING_WINDOW /* ds change change end */
2627 FROM MSC_ST_DEPARTMENT_RESOURCES msdr
2628 WHERE msdr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2629
2630 CURSOR c3 IS
2631 SELECT
2632 msss.ORGANIZATION_ID,
2633 msss.SIMULATION_SET,
2634 msss.DESCRIPTION,
2635 msss.USE_IN_WIP_FLAG,
2636 msss.SR_INSTANCE_ID
2637 FROM MSC_ST_SIMULATION_SETS msss
2638 WHERE msss.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2639
2640 CURSOR c4 IS
2641 SELECT
2642 msrg.GROUP_CODE,
2643 msrg.MEANING,
2644 msrg.DESCRIPTION,
2645 msrg.FROM_DATE,
2646 msrg.TO_DATE,
2647 msrg.ENABLED_FLAG
2648 FROM MSC_ST_RESOURCE_GROUPS msrg
2649 WHERE msrg.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2650
2651 /* ds:plan: change start */
2652 CURSOR dept_res_inst IS
2653 SELECT
2654 msdri.RESOURCE_ID,
2655 msdri.DEPARTMENT_ID,
2656 msdri.ORGANIZATION_ID,
2657 msdri.SR_INSTANCE_ID,
2658 msdri.RES_INSTANCE_ID,
2659 t1.inventory_item_id EQUIPMENT_ITEM_ID,
2660 msdri.SERIAL_NUMBER,
2661 msdri.EFFECTIVE_START_DATE,
2662 msdri.EFFECTIVE_END_DATE,
2663 msdri.LAST_KNOWN_SETUP,
2664 msdri.DELETED_FLAG
2665 FROM MSC_ST_DEPT_RES_INSTANCES msdri,
2666 MSC_ITEM_ID_LID t1
2667 WHERE msdri.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2668 AND t1.sr_instance_id (+) = msdri.sr_instance_id
2669 AND t1.sr_inventory_item_id (+) = msdri.equipment_item_id;
2670 /* ds:plan: change end */
2671
2672 c_count NUMBER:= 0;
2673 lv_uom_class_type number;
2674
2675 lv_cnt NUMBER;
2676 lv_pbs NUMBER;
2677 lv_sql_stmt VARCHAR2(2048);
2678 p_instance_id NUMBER;
2679 p_table_name VARCHAR2(30);
2680 lv_bom NUMBER;
2681 lv_wip NUMBER;
2682 lv_res_incr_refresh BOOLEAN;
2683 lv_res_partial_refresh BOOLEAN;
2684
2685 BEGIN
2686
2687 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
2688 if MSC_CL_COLLECTION.v_bom_refresh_type =1 then --2 be changed
2689 lv_res_incr_refresh :=TRUE;
2690 lv_res_partial_refresh := FALSE;
2691 elsif MSC_CL_COLLECTION.v_bom_refresh_type =2 then --2 be changed
2692 lv_res_incr_refresh := FALSE;
2693 lv_res_partial_refresh := TRUE;
2694 else
2695 null;
2696 end if;
2697 else
2698 if(MSC_CL_COLLECTION.v_is_partial_refresh) then
2699 lv_res_partial_refresh:=TRUE;
2700 lv_res_incr_refresh := FALSE;
2701 elsif(MSC_CL_COLLECTION.v_is_incremental_refresh) then
2702 lv_res_incr_refresh:= TRUE;
2703 lv_res_partial_refresh := FALSE;
2704 end if;
2705 end if;
2706 /* Bug 2878327 - We will always delete msc_department_resources and insert
2707 into it as it uses no snapshots
2708 */
2709
2710 /* ds change change start */
2711 select bom, wip
2712 into lv_bom, lv_wip
2713 from msc_coll_parameters
2714 where instance_id = MSC_CL_COLLECTION.v_instance_id;
2715
2716 IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2717 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2718 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1);
2719 ELSE
2720 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2721 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPT_RES_INSTANCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str); /* ds change change */
2722 END IF;
2723 END IF;
2724 /* ds change change end */
2725
2726
2727 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
2728
2729
2730 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESOURCE_GROUPS', MSC_CL_COLLECTION.v_instance_id, NULL); /*No Organization_id Column */
2731
2732 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2733 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1);
2734 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL);
2735 ELSE
2736 v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2737 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_DEPARTMENT_RESOURCES', MSC_CL_COLLECTION.v_instance_id, -1,v_sub_str);
2738 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SIMULATION_SETS', MSC_CL_COLLECTION.v_instance_id,NULL,v_sub_str);
2739 END IF;
2740
2741 ELSE
2742
2743 IF (((lv_bom = MSC_UTIL.SYS_YES) OR (lv_wip = MSC_UTIL.SYS_YES)) AND NOT MSC_CL_COLLECTION.v_is_legacy_refresh) THEN
2744
2745 /* Bug 3041176 - Delete Only Department resources in NetChange */
2746
2747 p_instance_id := MSC_CL_COLLECTION.v_instance_id;
2748 p_table_name := 'MSC_DEPARTMENT_RESOURCES';
2749
2750 lv_pbs:= TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
2751
2752 lv_sql_stmt:= 'SELECT COUNT(*)'
2753 ||' FROM '||p_table_name||' mdr, msc_trading_partners mtp'
2754 ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id'
2755 ||' AND mdr.PLAN_ID= -1 '
2756 ||' AND mdr.LINE_FLAG = 2'
2757 ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2758 ||' AND mtp.sr_instance_id = :p_instance_id'
2759 ||' AND mtp.sr_tp_id = mdr.organization_id'
2760 ||' AND mtp.partner_type = 3'
2761 ||' AND mtp.organization_type = 1';
2762
2763 EXECUTE IMMEDIATE lv_sql_stmt
2764 INTO lv_cnt
2765 USING p_instance_id, p_instance_id;
2766
2767 IF lv_pbs IS NULL OR
2768 lv_cnt < lv_pbs THEN /* batch_size */
2769
2770 lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2771 ||' WHERE mdr.SR_INSTANCE_ID= :lv_instance_id'
2772 ||' AND mdr.PLAN_ID= -1 '
2773 ||' AND mdr.LINE_FLAG = 2'
2774 ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2775 ||' AND mdr.organization_id = '
2776 ||' (SELECT mtp.sr_tp_id'
2777 ||' FROM msc_trading_partners mtp'
2778 ||' WHERE mtp.sr_instance_id = :lv_instance_id'
2779 ||' AND mtp.sr_tp_id = mdr.organization_id'
2780 ||' AND mtp.partner_type = 3'
2781 ||' AND mtp.organization_type = 1)';
2782
2783 EXECUTE IMMEDIATE lv_sql_stmt
2784 USING p_instance_id, p_instance_id;
2785
2786 COMMIT;
2787
2788 ELSE
2789
2790 lv_sql_stmt:= 'DELETE '||p_table_name||' mdr'
2791 ||' WHERE mdr.SR_INSTANCE_ID= :p_instance_id '
2792 ||' AND mdr.PLAN_ID= -1 '
2793 ||' AND mdr.LINE_FLAG = 2'
2794 ||' AND mdr.organization_id '|| MSC_UTIL.v_in_org_str
2795 ||' AND ROWNUM < :lv_pbs'
2796 ||' AND mdr.organization_id = '
2797 ||' (SELECT mtp.sr_tp_id'
2798 ||' FROM msc_trading_partners mtp'
2799 ||' WHERE mtp.sr_instance_id = :p_instance_id'
2800 ||' AND mtp.sr_tp_id = mdr.organization_id'
2801 ||' AND mtp.partner_type = 3'
2802 ||' AND mtp.organization_type = 1)';
2803
2804 LOOP
2805
2806 EXECUTE IMMEDIATE lv_sql_stmt
2807 USING p_instance_id, lv_pbs, p_instance_id;
2808
2809 EXIT WHEN SQL%ROWCOUNT= 0;
2810
2811 COMMIT;
2812
2813 END LOOP;
2814
2815 END IF; /* batch_size */
2816
2817
2818 END IF; /* lv_bom */
2819 END IF;
2820
2821 c_count:= 0;
2822
2823 FOR c_rec IN c1 LOOP
2824
2825 BEGIN
2826 lv_uom_class_type := 1;
2827
2828 /* for the bug:2449749 removed the filter of sr_instance_id as the UOM code is global */
2829
2830 SELECT DECODE(upper(uom_class),'WEIGHT',1 ,'VOLUME',2 , 1)
2831 INTO lv_uom_class_type
2832 FROM MSC_UNITS_OF_MEASURE
2833 WHERE UOM_CODE = c_rec.unit_of_measure;
2834 -- AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID;
2835
2836 EXCEPTION
2837 WHEN OTHERS THEN NULL;
2838
2839 END ;
2840
2841 BEGIN
2842
2843 IF (lv_res_incr_refresh) THEN
2844 IF c_rec.DELETED_FLAG= MSC_UTIL.SYS_YES THEN
2845
2846 UPDATE MSC_DEPARTMENT_RESOURCES
2847 SET CAPACITY_UNITS= 0,
2848 MAX_RATE= 0,
2849 MIN_RATE= 0,
2850 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2851 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2852 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2853 WHERE PLAN_ID= -1
2854 AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2855 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2856 AND RESOURCE_ID= c_rec.RESOURCE_ID
2857 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2858
2859 ELSE
2860 UPDATE MSC_DEPARTMENT_RESOURCES
2861 SET
2862 RESOURCE_CODE= c_rec.RESOURCE_CODE,
2863 DEPARTMENT_CODE= c_rec.DEPARTMENT_CODE,
2864 DEPARTMENT_DESCRIPTION= c_rec.DEPARTMENT_DESCRIPTION,
2865 RESOURCE_DESCRIPTION= c_rec.RESOURCE_DESCRIPTION,
2866 DEPARTMENT_CLASS= c_rec.DEPARTMENT_CLASS,
2867 LINE_FLAG= c_rec.LINE_FLAG,
2868 CAPACITY_UNITS= c_rec.CAPACITY_UNITS,
2869 MAX_RATE= c_rec.MAX_RATE,
2870 MIN_RATE= c_rec.MIN_RATE,
2871 AGGREGATE_RESOURCE_ID= c_rec.AGGREGATED_RESOURCE_ID,
2872 AGGREGATE_RESOURCE_FLAG= c_rec.AGGREGATED_RESOURCE_FLAG,
2873 RESOURCE_GROUP_NAME= c_rec.RESOURCE_GROUP_NAME,
2874 RESOURCE_GROUP_CODE= c_rec.RESOURCE_GROUP_CODE,
2875 RESOURCE_BALANCE_FLAG= c_rec.RESOURCE_BALANCE_FLAG,
2876 BOTTLENECK_FLAG= c_rec.BOTTLENECK_FLAG,
2877 OWNING_DEPARTMENT_ID= c_rec.OWNING_DEPARTMENT_ID,
2878 OVERUTILIZED_PERCENT= c_rec.OVER_UTILIZED_PERCENT,
2879 UNDERUTILIZED_PERCENT= c_rec.UNDER_UTILIZED_PERCENT,
2880 RESOURCE_SHORTAGE_TYPE= c_rec.RESOURCE_SHORTAGE_TYPE,
2881 RESOURCE_EXCESS_TYPE= c_rec.RESOURCE_EXCESS_TYPE,
2882 RESOURCE_INCLUDE_FLAG= c_rec.RESOURCE_INCLUDE_FLAG,
2883 PLANNING_EXCEPTION_SET= c_rec.PLANNING_EXCEPTION_SET,
2884 USER_TIME_FENCE= c_rec.USER_TIME_FENCE,
2885 UTILIZATION= c_rec.UTILIZATION,
2886 EFFICIENCY= c_rec.EFFICIENCY,
2887 BATCHABLE_FLAG= c_rec.BATCHABLE_FLAG,
2888 BATCHING_WINDOW= c_rec.BATCHING_WINDOW,
2889 MIN_CAPACITY= c_rec.MIN_CAPACITY,
2890 MAX_CAPACITY= c_rec.MAX_CAPACITY,
2891 UNIT_OF_MEASURE= c_rec.UNIT_OF_MEASURE,
2892 UOM_CLASS_TYPE = lv_uom_class_type,
2893 CRITICAL_RESOURCE_FLAG= c_rec.CRITICAL_RESOURCE_FLAG,
2894 RESOURCE_TYPE= c_rec.RESOURCE_TYPE,
2895 DISABLE_DATE= c_rec.DISABLE_DATE,
2896 AVAILABLE_24_HOURS_FLAG= c_rec.AVAILABLE_24_HOURS_FLAG,
2897 CTP_FLAG= c_rec.CTP_FLAG,
2898 START_TIME= c_rec.START_TIME,
2899 STOP_TIME= c_rec.STOP_TIME,
2900 RESOURCE_COST= c_rec.RESOURCE_COST,
2901 RESOURCE_OVER_UTIL_COST= c_rec.RESOURCE_OVER_UTIL_COST,
2902 DEPT_OVERHEAD_COST= c_rec.DEPT_OVERHEAD_COST,
2903 ATP_RULE_ID=c_rec.ATP_RULE_ID,
2904 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2905 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2906 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2907 CAPACITY_TOLERANCE= c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
2908 CHARGEABLE_FLAG= c_rec.CHARGEABLE_FLAG,
2909 IDLE_TIME_TOLERANCE= c_rec.IDLE_TIME_TOLERANCE,
2910 BATCHING_PENALTY= c_rec.BATCHING_PENALTY,
2911 SCHEDULE_TO_INSTANCE= c_rec.SCHEDULE_TO_INSTANCE,
2912 LAST_KNOWN_SETUP= c_rec.LAST_KNOWN_SETUP ,
2913 SETUP_TIME_PERCENT= c_rec.SETUP_TIME_PERCENT,
2914 UTILIZATION_CHANGE_PERCENT= c_rec.UTILIZATION_CHANGE_PERCENT,
2915 SETUP_TIME_TYPE= c_rec.SETUP_TIME_TYPE,
2916 UTILIZATION_CHANGE_TYPE= c_rec.UTILIZATION_CHANGE_TYPE,
2917 SDS_SCHEDULING_WINDOW= c_rec.SDS_SCHEDULING_WINDOW /* ds change change end */
2918 WHERE PLAN_ID= -1
2919 AND DEPARTMENT_ID= c_rec.DEPARTMENT_ID
2920 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
2921 AND RESOURCE_ID= c_rec.RESOURCE_ID
2922 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
2923
2924 END IF; -- DELETED_FLAG
2925
2926 END IF; -- refresh mode
2927
2928 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR
2929 (SQL%NOTFOUND) THEN
2930 INSERT INTO MSC_DEPARTMENT_RESOURCES
2931 ( PLAN_ID,
2932 RESOURCE_CODE,
2933 DEPARTMENT_CODE,
2934 DEPARTMENT_DESCRIPTION,
2935 RESOURCE_DESCRIPTION,
2936 DEPARTMENT_CLASS,
2937 LINE_FLAG,
2938 CAPACITY_UNITS,
2939 MAX_RATE,
2940 MIN_RATE,
2941 AGGREGATE_RESOURCE_ID,
2942 AGGREGATE_RESOURCE_FLAG,
2943 RESOURCE_GROUP_NAME,
2944 RESOURCE_GROUP_CODE,
2945 RESOURCE_BALANCE_FLAG,
2946 BOTTLENECK_FLAG,
2947 DEPARTMENT_ID,
2948 OWNING_DEPARTMENT_ID,
2949 ORGANIZATION_ID,
2950 RESOURCE_ID,
2951 SR_INSTANCE_ID,
2952 OVERUTILIZED_PERCENT,
2953 UNDERUTILIZED_PERCENT,
2954 RESOURCE_SHORTAGE_TYPE,
2955 RESOURCE_EXCESS_TYPE,
2956 RESOURCE_INCLUDE_FLAG,
2957 PLANNING_EXCEPTION_SET,
2958 USER_TIME_FENCE,
2959 UTILIZATION,
2960 EFFICIENCY,
2961 BATCHABLE_FLAG,
2962 BATCHING_WINDOW,
2963 MIN_CAPACITY,
2964 MAX_CAPACITY,
2965 UNIT_OF_MEASURE,
2966 UOM_CLASS_TYPE ,
2967 CRITICAL_RESOURCE_FLAG,
2968 RESOURCE_TYPE,
2969 DISABLE_DATE,
2970 AVAILABLE_24_HOURS_FLAG,
2971 CTP_FLAG,
2972 START_TIME,
2973 STOP_TIME,
2974 RESOURCE_COST,
2975 RESOURCE_OVER_UTIL_COST,
2976 DEPT_OVERHEAD_COST,
2977 ATP_RULE_ID,
2978 CAPACITY_TOLERANCE, /* ds change change start*/
2979 CHARGEABLE_FLAG,
2980 IDLE_TIME_TOLERANCE,
2981 BATCHING_PENALTY,
2982 SCHEDULE_TO_INSTANCE,
2983 LAST_KNOWN_SETUP,
2984 SETUP_TIME_PERCENT,
2985 UTILIZATION_CHANGE_PERCENT,
2986 SETUP_TIME_TYPE,
2987 UTILIZATION_CHANGE_TYPE ,
2988 SDS_SCHEDULING_WINDOW, /* ds change change end */
2989 REFRESH_NUMBER,
2990 LAST_UPDATE_DATE,
2991 LAST_UPDATED_BY,
2992 CREATION_DATE,
2993 CREATED_BY)
2994 VALUES
2995 ( -1,
2996 c_rec.RESOURCE_CODE,
2997 c_rec.DEPARTMENT_CODE,
2998 c_rec.DEPARTMENT_DESCRIPTION,
2999 c_rec.RESOURCE_DESCRIPTION,
3000 c_rec.DEPARTMENT_CLASS,
3001 c_rec.LINE_FLAG,
3002 c_rec.CAPACITY_UNITS,
3003 c_rec.MAX_RATE,
3004 c_rec.MIN_RATE,
3005 c_rec.AGGREGATED_RESOURCE_ID,
3006 c_rec.AGGREGATED_RESOURCE_FLAG,
3007 c_rec.RESOURCE_GROUP_NAME,
3008 c_rec.RESOURCE_GROUP_CODE,
3009 c_rec.RESOURCE_BALANCE_FLAG,
3010 c_rec.BOTTLENECK_FLAG,
3011 c_rec.DEPARTMENT_ID,
3012 c_rec.OWNING_DEPARTMENT_ID,
3013 c_rec.ORGANIZATION_ID,
3014 c_rec.RESOURCE_ID,
3015 c_rec.SR_INSTANCE_ID,
3016 c_rec.OVER_UTILIZED_PERCENT,
3017 c_rec.UNDER_UTILIZED_PERCENT,
3018 c_rec.RESOURCE_SHORTAGE_TYPE,
3019 c_rec.RESOURCE_EXCESS_TYPE,
3020 c_rec.RESOURCE_INCLUDE_FLAG,
3021 c_rec.PLANNING_EXCEPTION_SET,
3022 c_rec.USER_TIME_FENCE,
3023 c_rec.UTILIZATION,
3024 c_rec.EFFICIENCY,
3025 c_rec.BATCHABLE_FLAG,
3026 c_rec.BATCHING_WINDOW,
3027 c_rec.MIN_CAPACITY,
3028 c_rec.MAX_CAPACITY,
3029 c_rec.UNIT_OF_MEASURE,
3030 lv_uom_class_type ,
3031 c_rec.CRITICAL_RESOURCE_FLAG,
3032 c_rec.RESOURCE_TYPE,
3033 c_rec.DISABLE_DATE,
3034 c_rec.AVAILABLE_24_HOURS_FLAG,
3035 c_rec.CTP_FLAG,
3036 c_rec.START_TIME,
3037 c_rec.STOP_TIME,
3038 c_rec.RESOURCE_COST,
3039 c_rec.RESOURCE_OVER_UTIL_COST,
3040 c_rec.DEPT_OVERHEAD_COST,
3041 c_rec.ATP_RULE_ID,
3042 c_rec.CAPACITY_TOLERANCE, /* ds change change start*/
3043 c_rec.CHARGEABLE_FLAG,
3044 c_rec.IDLE_TIME_TOLERANCE,
3045 c_rec.BATCHING_PENALTY,
3046 c_rec.SCHEDULE_TO_INSTANCE,
3047 c_rec.LAST_KNOWN_SETUP,
3048 c_rec.SETUP_TIME_PERCENT,
3049 c_rec.UTILIZATION_CHANGE_PERCENT,
3050 c_rec.SETUP_TIME_TYPE,
3051 c_rec.UTILIZATION_CHANGE_TYPE,
3052 c_rec.SDS_SCHEDULING_WINDOW,
3053 /* ds change change end */
3054 MSC_CL_COLLECTION.v_last_collection_id,
3055 MSC_CL_COLLECTION.v_current_date,
3056 MSC_CL_COLLECTION.v_current_user,
3057 MSC_CL_COLLECTION.v_current_date,
3058 MSC_CL_COLLECTION.v_current_user );
3059
3060 END IF;
3061
3062 c_count:= c_count+1;
3063
3064 IF c_count> MSC_CL_COLLECTION.PBS THEN
3065 COMMIT;
3066 c_count:= 0;
3067 END IF;
3068
3069 EXCEPTION
3070
3071 WHEN OTHERS THEN
3072
3073 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3074
3075 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3076 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3077 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3078 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3080
3081 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3082 RAISE;
3083
3084 ELSE
3085 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3086
3087 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3088 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3089 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3090 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPARTMENT_RESOURCES');
3091 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3092
3093 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3094 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3095 FND_MESSAGE.SET_TOKEN('VALUE',
3096 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3097 MSC_CL_COLLECTION.v_instance_id));
3098 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3099
3100 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3101 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_CODE');
3102 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_CODE);
3103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3104
3105 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3106 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_CODE');
3107 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_CODE);
3108 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3109
3110 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3111 END IF;
3112
3113 END;
3114
3115 END LOOP;
3116
3117 COMMIT;
3118
3119 /* ds change change start */
3120 c_count := 0;
3121
3122 FOR c_rec IN dept_res_inst LOOP
3123 /* for dept resource instance we don't have snapshot
3124 so in increment its alwasy full refresh */
3125 BEGIN
3126 -- IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3127
3128 INSERT INTO MSC_DEPT_RES_INSTANCES
3129 ( PLAN_ID,
3130 DEPT_RESOURCE_INST_ID ,
3131 RESOURCE_ID,
3132 RES_INSTANCE_ID,
3133 DEPARTMENT_ID,
3134 ORGANIZATION_ID,
3135 SERIAL_NUMBER,
3136 EQUIPMENT_ITEM_ID,
3137 SR_INSTANCE_ID,
3138 LAST_KNOWN_SETUP,
3139 EFFECTIVE_START_DATE,
3140 EFFECTIVE_END_DATE,
3141 REFRESH_NUMBER,
3142 LAST_UPDATE_DATE,
3143 LAST_UPDATED_BY,
3144 CREATION_DATE,
3145 CREATED_BY)
3146 VALUES
3147 ( -1,
3148 MSC_DEPT_RES_INSTANCES_S.NEXTVAL,
3149 c_rec.RESOURCE_ID,
3150 c_rec.RES_INSTANCE_ID,
3151 c_rec.DEPARTMENT_ID,
3152 c_rec.ORGANIZATION_ID,
3153 c_rec.SERIAL_NUMBER,
3154 c_rec.EQUIPMENT_ITEM_ID,
3155 c_rec.SR_INSTANCE_ID,
3156 c_rec.LAST_KNOWN_SETUP,
3157 c_rec.EFFECTIVE_START_DATE,
3158 c_rec.EFFECTIVE_END_DATE,
3159 MSC_CL_COLLECTION.v_last_collection_id,
3160 MSC_CL_COLLECTION.v_current_date,
3161 MSC_CL_COLLECTION.v_current_user,
3162 MSC_CL_COLLECTION.v_current_date,
3163 MSC_CL_COLLECTION.v_current_user );
3164 --END IF;
3165
3166 c_count:= c_count+1;
3167
3168 IF c_count> MSC_CL_COLLECTION.PBS THEN
3169 COMMIT;
3170 c_count:= 0;
3171 END IF;
3172
3173 EXCEPTION
3174 WHEN OTHERS THEN
3175
3176 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3177
3178 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3179 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3180 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3181 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3182 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3183
3184 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3185 RAISE;
3186
3187 ELSE
3188 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3189
3190 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3191 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3192 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3193 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEPT_RES_INSTANCES');
3194 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3195
3196 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3197 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_ID');
3198 FND_MESSAGE.SET_TOKEN('VALUE',c_rec.ORGANIZATION_ID);
3199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3200
3201 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3202 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3203 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DEPARTMENT_ID);
3204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3205
3206 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3207 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3208 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RESOURCE_ID);
3209 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3210
3211 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3212 FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
3213 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.RES_INSTANCE_ID);
3214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3216 END IF;
3217
3218 END;
3219
3220 END LOOP; /*c_rec IN dept_res_inst LOOP */
3221
3222 COMMIT;
3223
3224 /* ds change change end */
3225
3226
3227 c_count:= 0;
3228
3229 FOR c_rec IN c3 LOOP
3230
3231 BEGIN
3232
3233 IF (lv_res_incr_refresh) THEN
3234
3235 UPDATE MSC_SIMULATION_SETS
3236 SET
3237 DESCRIPTION= c_rec.DESCRIPTION,
3238 USE_IN_WIP_FLAG= c_rec.USE_IN_WIP_FLAG,
3239 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3240 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3241 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3242 WHERE ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3243 AND SIMULATION_SET= c_rec.SIMULATION_SET
3244 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID;
3245
3246 END IF;
3247
3248 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) OR SQL%NOTFOUND THEN
3249
3250 INSERT INTO MSC_SIMULATION_SETS
3251 ( ORGANIZATION_ID,
3252 SIMULATION_SET,
3253 DESCRIPTION,
3254 USE_IN_WIP_FLAG,
3255 SR_INSTANCE_ID,
3256 REFRESH_NUMBER,
3257 LAST_UPDATE_DATE,
3258 LAST_UPDATED_BY,
3259 CREATION_DATE,
3260 CREATED_BY)
3261 VALUES
3262 ( c_rec.ORGANIZATION_ID,
3263 c_rec.SIMULATION_SET,
3264 c_rec.DESCRIPTION,
3265 c_rec.USE_IN_WIP_FLAG,
3266 c_rec.SR_INSTANCE_ID,
3267 MSC_CL_COLLECTION.v_last_collection_id,
3268 MSC_CL_COLLECTION.v_current_date,
3269 MSC_CL_COLLECTION.v_current_user,
3270 MSC_CL_COLLECTION.v_current_date,
3271 MSC_CL_COLLECTION.v_current_user );
3272
3273 END IF;
3274
3275 c_count:= c_count+1;
3276
3277 IF c_count> MSC_CL_COLLECTION.PBS THEN
3278 COMMIT;
3279 c_count:= 0;
3280 END IF;
3281
3282 EXCEPTION
3283 WHEN OTHERS THEN
3284
3285 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3286
3287 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3288 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3289 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3290 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3291 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3292
3293 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3294 RAISE;
3295
3296 ELSE
3297 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3298
3299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3300 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3301 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3302 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SIMULATION_SETS');
3303 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3304
3305 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3306 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3307 FND_MESSAGE.SET_TOKEN('VALUE',
3308 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3309 MSC_CL_COLLECTION.v_instance_id));
3310 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3311
3312 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3313 FND_MESSAGE.SET_TOKEN('COLUMN', 'SIMULATION_SET');
3314 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SIMULATION_SET);
3315 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3316
3317 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3318 END IF;
3319
3320 END;
3321
3322 END LOOP;
3323
3324 COMMIT;
3325
3326 IF ((MSC_CL_COLLECTION.v_is_complete_refresh) OR (lv_res_partial_refresh)) THEN
3327
3328 c_count:= 0;
3329
3330 FOR c_rec IN c4 LOOP
3331
3332 BEGIN
3333
3334 INSERT INTO MSC_RESOURCE_GROUPS
3335 ( GROUP_CODE,
3336 MEANING,
3337 DESCRIPTION,
3338 FROM_DATE,
3339 TO_DATE,
3340 ENABLED_FLAG,
3341 SR_INSTANCE_ID,
3342 LAST_UPDATE_DATE,
3343 LAST_UPDATED_BY,
3344 CREATION_DATE,
3345 CREATED_BY)
3346 VALUES
3347 ( c_rec.GROUP_CODE,
3348 c_rec.MEANING,
3349 c_rec.DESCRIPTION,
3350 c_rec.FROM_DATE,
3351 c_rec.TO_DATE,
3352 c_rec.ENABLED_FLAG,
3353 MSC_CL_COLLECTION.v_instance_id,
3354 MSC_CL_COLLECTION.v_current_date,
3355 MSC_CL_COLLECTION.v_current_user,
3356 MSC_CL_COLLECTION.v_current_date,
3357 MSC_CL_COLLECTION.v_current_user );
3358
3359 c_count:= c_count+1;
3360
3361 IF c_count> MSC_CL_COLLECTION.PBS THEN
3362 COMMIT;
3363 c_count:= 0;
3364 END IF;
3365
3366 EXCEPTION
3367
3368 WHEN OTHERS THEN
3369
3370 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3371
3372 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3373 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3374 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3375 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3376 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3377
3378 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3379 RAISE;
3380
3381 ELSE
3382 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3383
3384 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3385 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3386 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RESOURCE');
3387 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_GROUPS');
3388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3389
3390 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3391 FND_MESSAGE.SET_TOKEN('COLUMN', 'GROUP_CODE');
3392 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.GROUP_CODE);
3393 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3394
3395 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3396 END IF;
3397
3398 END;
3399
3400 END LOOP;
3401
3402 COMMIT;
3403
3404 END IF;
3405
3406 END LOAD_RESOURCE;
3407
3408
3409
3410 END MSC_CL_BOM_ODS_LOAD;