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