[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_WIP_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_WIP_ODS_LOAD AS -- specification
2 /* $Header: MSCLWIPB.pls 120.1 2007/04/17 10:07:26 vpalla noship $ */
3
4 -- v_sub_str VARCHAR2(4000):=NULL;
5 -- c_count NUMBER:= 0;
6 -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
7
8 -- G_COLLECT_SRP_DATA VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MSC_SRP_ENABLED'),'N');
9 -- To collect SRP Data when this profile is set to Yes neds to be deleted
10 -- v_is_cont_refresh BOOLEAN; -- 2 be changed
11 -- v_chr9 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(9);
12 -- v_chr10 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
13 -- v_chr13 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
14
15
16
17 -- PROCEDURE LOAD_JOB_DETAILS; --for job details
18 PROCEDURE LOAD_JOB_OP_RES_INSTANCE;
19 PROCEDURE LOAD_JOB_OP_NWK;
20 PROCEDURE LOAD_JOB_OP;
21 PROCEDURE LOAD_JOB_REQ_OP;
22 PROCEDURE LOAD_JOB_OP_RES;
23
24 -- PROCEDURE LOAD_WIP_DEMAND; -- called by load_supply
25 -- PROCEDURE LOAD_RES_REQ; -- called by load_supply
26
27
28 PROCEDURE LOAD_JOB_DETAILS IS
29 BEGIN
30 LOAD_JOB_OP_NWK;
31 LOAD_JOB_OP;
32 LOAD_JOB_REQ_OP;
33 LOAD_JOB_OP_RES;
34 LOAD_JOB_OP_RES_INSTANCE;
35 END LOAD_JOB_DETAILS;
36
37 --==============================================================
38
39 PROCEDURE LOAD_JOB_OP_RES_INSTANCE IS
40
41 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
42 cgen CurTyp;
43
44 lv_tbl VARCHAR2(30);
45 lv_supplies_tbl VARCHAR2(30);
46
47 lv_cursor_stmt VARCHAR2(5000);
48 lv_sql_stmt VARCHAR2(5000);
49
50 lv_TRANSACTION_ID NUMBER;
51 lv_OPERATION_SEQ_NUM NUMBER;
52 lv_SR_INSTANCE_ID NUMBER;
53 lv_RESOURCE_SEQ_NUM NUMBER;
54 lv_RESOURCE_ID NUMBER;
55 lv_DEPARTMENT_ID NUMBER;
56 lv_ORGANIZATION_ID NUMBER;
57 lv_RES_INSTANCE_ID NUMBER;
58 lv_EQUIPMENT_ITEM_ID NUMBER;
59 lv_SERIAL_NUMBER VARCHAR2(30);
60 lv_START_DATE DATE;
61 lv_COMPLETION_DATE DATE;
62 --lv_RES_INSTANCE_HOURS NUMBER;
63 lv_BATCH_NUMBER NUMBER;
64 c_count NUMBER:=0;
65 total_count NUMBER:=0;
66
67 lv_errbuf VARCHAR2(240);
68 lv_retcode NUMBER;
69
70 BEGIN
71
72 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
73 lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
74 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
75 ELSE
76 lv_tbl:= 'MSC_JOB_OP_RES_INSTANCES';
77 lv_supplies_tbl:= 'MSC_SUPPLIES';
78 END IF;
79
80 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
81
82 lv_cursor_stmt:=
83 'SELECT'
84 ||' ms.TRANSACTION_ID,'
85 ||' resi.OPERATION_SEQ_NUM,'
86 ||' resi.RESOURCE_SEQ_NUM,'
87 ||' resi.RESOURCE_ID,'
88 ||' resi.RES_INSTANCE_ID,'
89 ||' resi.SERIAL_NUMBER,'
90 ||' t1.inventory_item_id EQUIPMENT_ITEM_ID,'
91 ||' resi.SR_INSTANCE_ID'
92 ||' FROM '||lv_supplies_tbl||' ms,'
93 ||' MSC_ST_JOB_OP_RES_INSTANCES resi,'
94 ||' MSC_ITEM_ID_LID t1 '
95 ||' WHERE resi.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
96 ||' AND ms.PLAN_ID= -1'
97 ||' AND ms.SR_INSTANCE_ID= resi.SR_INSTANCE_ID'
98 ||' AND ms.DISPOSITION_ID= resi.WIP_ENTITY_ID'
99 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
100 ||' AND resi.DELETED_FLAG= '||MSC_UTIL.SYS_YES
101 ||' AND t1.sr_inventory_item_id (+) = resi.equipment_item_id '
102 ||' AND t1.SR_INSTANCE_ID (+) = resi.SR_INSTANCE_ID ';
103
104
105 OPEN cgen FOR lv_cursor_stmt;
106
107 IF (cgen%ISOPEN) THEN
108
109 LOOP
110
111 FETCH cgen INTO
112 lv_TRANSACTION_ID,
113 lv_OPERATION_SEQ_NUM,
114 lv_RESOURCE_SEQ_NUM,
115 lv_RESOURCE_ID,
116 lv_RES_INSTANCE_ID,
117 lv_SERIAL_NUMBER,
118 lv_EQUIPMENT_ITEM_ID,
119 lv_SR_INSTANCE_ID;
120
121 EXIT WHEN cgen%NOTFOUND;
122
123 DELETE MSC_JOB_OP_RES_INSTANCES
124 WHERE PLAN_ID= -1
125 AND TRANSACTION_ID = lv_TRANSACTION_ID
126 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
127 AND OPERATION_SEQ_NUM = nvl(lv_OPERATION_SEQ_NUM,OPERATION_SEQ_NUM)
128 AND RESOURCE_SEQ_NUM = nvl(lv_RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM)
129 AND RESOURCE_ID = nvl(lv_RESOURCE_ID,RESOURCE_ID)
130 AND RES_INSTANCE_ID = nvl(lv_RES_INSTANCE_ID,RES_INSTANCE_ID)
131 AND SERIAL_NUMBER = nvl(lv_SERIAL_NUMBER,SERIAL_NUMBER);
132
133
134 END LOOP;
135
136 END IF; /* cgen%ISOPEN */
137
138 COMMIT;
139
140 CLOSE cgen;
141
142 END IF; /*Incremental*/
143
144 lv_cursor_stmt:=
145 'SELECT'
146 ||' ms.TRANSACTION_ID,'
147 ||' resi.OPERATION_SEQ_NUM ,'
148 ||' resi.SR_INSTANCE_ID,'
149 ||' resi.RESOURCE_SEQ_NUM,'
150 ||' resi.ORGANIZATION_ID,'
151 ||' resi.RESOURCE_ID,'
152 ||' resi.RES_INSTANCE_ID,'
153 ||' resi.SERIAL_NUMBER,'
154 ||' t1.inventory_item_id EQUIPMENT_ITEM_ID,'
155 ||' resi.DEPARTMENT_ID,'
156 ||' resi.START_DATE,'
157 ||' resi.COMPLETION_DATE,'
158 --||' resi.RES_INSTANCE_HOURS,'
159 ||' resi.BATCH_NUMBER'
160 ||' FROM '||lv_supplies_tbl||' ms,'
161 ||' MSC_ST_JOB_OP_RES_INSTANCES resi, '
162 ||' MSC_ITEM_ID_LID t1 '
163 ||' WHERE resi.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
164 ||' AND ms.PLAN_ID= -1'
165 ||' AND ms.SR_INSTANCE_ID= resi.SR_INSTANCE_ID'
166 ||' AND ms.DISPOSITION_ID= resi.WIP_ENTITY_ID'
167 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
168 ||' AND resi.DELETED_FLAG= '||MSC_UTIL.SYS_NO
169 ||' AND t1.sr_inventory_item_id (+) = resi.equipment_item_id '
170 ||' AND t1.SR_INSTANCE_ID (+) = resi.SR_INSTANCE_ID ';
171
172 -- ========= Prepare SQL Statement for INSERT ==========
173 lv_sql_stmt:=
174 'insert into '||lv_tbl
175 ||' ( PLAN_ID,'
176 ||' TRANSACTION_ID,'
177 ||' OPERATION_SEQ_NUM,'
178 ||' RESOURCE_SEQ_NUM,'
179 ||' RESOURCE_ID,'
180 ||' RES_INSTANCE_ID,'
181 ||' SERIAL_NUMBER,'
182 ||' EQUIPMENT_ITEM_ID,'
183 ||' START_DATE,'
184 ||' COMPLETION_DATE,'
185 ||' BATCH_NUMBER,'
186 ||' SR_INSTANCE_ID,'
187 ||' REFRESH_NUMBER,'
188 ||' LAST_UPDATE_DATE,'
189 ||' LAST_UPDATED_BY,'
190 ||' CREATION_DATE,'
191 ||' CREATED_BY)'
192 ||'VALUES'
193 ||'( -1,'
194 ||' :TRANSACTION_ID,'
195 ||' :OPERATION_SEQ_NUM,'
196 ||' :RESOURCE_SEQ_NUM,'
197 ||' :RESOURCE_ID,'
198 ||' :RES_INSTANCE_ID,'
199 ||' :SERIAL_NUMBER,'
200 ||' :EQUIPMENT_ITEM_ID,'
201 ||' :START_DATE,'
202 ||' :COMPLETION_DATE,'
203 ||' :BATCH_NUMBER,'
204 ||' :SR_INSTANCE_ID,'
205 ||' :REFRESH_NUMBER,'
206 ||' :v_current_date,'
207 ||' :v_current_user,'
208 ||' :v_current_date,'
209 ||' :v_current_user)';
210 --log_debug(lv_cursor_stmt);
211 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
212 OPEN cgen FOR lv_cursor_stmt;
213 IF (cgen%ISOPEN) THEN
214
215 LOOP
216 FETCH cgen INTO
217 lv_TRANSACTION_ID ,
218 lv_OPERATION_SEQ_NUM ,
219 lv_SR_INSTANCE_ID ,
220 lv_RESOURCE_SEQ_NUM ,
221 lv_ORGANIZATION_ID ,
222 lv_RESOURCE_ID ,
223 lv_RES_INSTANCE_ID,
224 lv_SERIAL_NUMBER ,
225 lv_EQUIPMENT_ITEM_ID,
226 lv_DEPARTMENT_ID ,
227 lv_START_DATE ,
228 lv_COMPLETION_DATE ,
229 --lv_RES_INSTANCE_HOURS,
230 lv_BATCH_NUMBER ;
231
232 EXIT WHEN cgen%NOTFOUND;
233
234 BEGIN
235
236 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
237 /* we can get rid of thsi update if we insert in ad table when instance is updated */
238 UPDATE MSC_JOB_OP_RES_INSTANCES
239 SET
240 START_DATE = lv_START_DATE,
241 COMPLETION_DATE = lv_COMPLETION_DATE,
242 BATCH_NUMBER = lv_BATCH_NUMBER,
243 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
244 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
245 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
246 WHERE PLAN_ID= -1
247 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
248 AND TRANSACTION_ID = lv_TRANSACTION_ID
249 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
250 AND OPERATION_SEQ_NUM = lv_OPERATION_SEQ_NUM
251 AND RESOURCE_SEQ_NUM = lv_RESOURCE_SEQ_NUM
252 AND RES_INSTANCE_ID = lv_RES_INSTANCE_ID
253 AND SERIAL_NUMBER = lv_SERIAL_NUMBER;
254
255 END IF;
256
257 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
258
259 EXECUTE IMMEDIATE lv_sql_stmt
260 USING
261 lv_TRANSACTION_ID ,
262 lv_OPERATION_SEQ_NUM ,
263 lv_RESOURCE_SEQ_NUM ,
264 lv_RESOURCE_ID,
265 lv_RES_INSTANCE_ID,
266 lv_SERIAL_NUMBER,
267 lv_EQUIPMENT_ITEM_ID,
268 lv_START_DATE,
269 lv_COMPLETION_DATE,
270 lv_ORGANIZATION_ID ,
271 lv_BATCH_NUMBER,
272 lv_SR_INSTANCE_ID,
273 MSC_CL_COLLECTION.v_last_collection_id,
274 MSC_CL_COLLECTION.v_current_date,
275 MSC_CL_COLLECTION.v_current_user,
276 MSC_CL_COLLECTION.v_current_date,
277 MSC_CL_COLLECTION.v_current_user;
278 END IF;
279 total_count := total_count + 1;
280 c_count:= c_count+1;
281
282 IF c_count> MSC_CL_COLLECTION.PBS THEN
283 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
284 c_count:= 0;
285 END IF;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289
290 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
291
292 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
293 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
294 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
295 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
296 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
297
298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
299 RAISE;
300
301 ELSE
302
303 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
304
305 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
306 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
307 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES_INSTANCE');
308 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RES_INSTANCES');
309 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
310
311 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
312 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
313 FND_MESSAGE.SET_TOKEN('VALUE',
314 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
315 MSC_CL_COLLECTION.v_instance_id));
316 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
317
318 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
319 END IF;
320
321 END;
322 END LOOP;
323
324 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OP_RES_INSTANCES = '|| to_char(total_count));
325 END IF; /* cgen%ISOPEN */
326
327 CLOSE cgen;
328
329 COMMIT;
330
331 BEGIN
332
333 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
334
335 lv_tbl:= 'JOB_OP_RES_INSTANCES_'||MSC_CL_COLLECTION.v_instance_code;
336
337 lv_sql_stmt:=
338 'INSERT INTO '||lv_tbl
339 ||' SELECT * from MSC_JOB_OP_RES_INSTANCES'
340 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
341 ||' AND plan_id = -1 '
342 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
343
344 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
345 EXECUTE IMMEDIATE lv_sql_stmt;
346
347 COMMIT;
348
349 END IF;
350
351 EXCEPTION
352 WHEN OTHERS THEN
353
354 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
355 RAISE;
356 END;
357
358 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
359 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
360 lv_retcode,
361 'MSC_JOB_OP_RES_INSTANCES',
362 MSC_CL_COLLECTION.v_instance_code,
363 MSC_UTIL.G_WARNING
364 );
365
366 IF lv_retcode = MSC_UTIL.G_ERROR THEN
367 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
368 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
369 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
370 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
371 END IF;
372
373 END IF;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 IF cgen%ISOPEN THEN
378 CLOSE cgen;
379 END IF;
380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_OP_RES_INSTANCE>>');
381 IF lv_cursor_stmt IS NOT NULL THEN
382 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
383 END IF;
384 IF lv_sql_stmt IS NOT NULL THEN
385 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
386 END IF;
387 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
388 RAISE;
389 END LOAD_JOB_OP_RES_INSTANCE;
390
391 --================================================================
392 PROCEDURE LOAD_JOB_OP IS
393
394 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
395
396 cgen CurTyp;
397
398 lv_tbl VARCHAR2(30);
399 lv_supplies_tbl VARCHAR2(30);
400
401 lv_cursor_stmt VARCHAR2(5000);
402 lv_sql_stmt VARCHAR2(5000);
403
404
405 lv_TRANSACTION_ID NUMBER;
406 lv_OPERATION_SEQ_NUM NUMBER;
407 lv_SR_INSTANCE_ID NUMBER;
408 lv_RECOMMENDED VARCHAR2(1);
409 lv_NETWORK_START_END VARCHAR2(1);
410 lv_RECO_START_DATE DATE;
411 lv_ORGANIZATION_ID NUMBER;
412 lv_RECO_COMPLETION_DATE DATE;
413 lv_OPERATION_SEQUENCE_ID NUMBER;
414 lv_STANDARD_OPERATION_CODE VARCHAR2(4);
415 lv_DEPARTMENT_ID NUMBER;
416 lv_OP_LT_PERCENT NUMBER;
417 lv_MINIMUM_TRANSFER_QUANTITY NUMBER;
418 lv_EFFECTIVITY_DATE DATE;
419 lv_DISABLE_DATE DATE;
420 lv_OPERATION_TYPE NUMBER;
421 lv_YIELD NUMBER;
422 lv_CUMULATIVE_YIELD NUMBER;
423 lv_REVERSE_CUMULATIVE_YIELD NUMBER;
424 lv_NET_PLANNING_PERCENT NUMBER;
425 total_count NUMBER := 0;
426
427 lv_errbuf VARCHAR2(240);
428 lv_retcode NUMBER;
429
430 BEGIN
431
432 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
433 lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
434 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
435 ELSE
436 lv_tbl:= 'MSC_JOB_OPERATIONS';
437 lv_supplies_tbl:= 'MSC_SUPPLIES';
438 END IF;
439
440 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
441
442 lv_cursor_stmt:=
443 'SELECT'
444 ||' ms.TRANSACTION_ID,'
445 ||' opr.OPERATION_SEQ_NUM,'
446 ||' opr.SR_INSTANCE_ID'
447 ||' FROM '||lv_supplies_tbl||' ms,'
448 ||' MSC_ST_JOB_OPERATIONS opr'
449 ||' WHERE opr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
450 ||' AND ms.PLAN_ID= -1'
451 ||' AND ms.SR_INSTANCE_ID= opr.SR_INSTANCE_ID'
452 ||' AND ms.DISPOSITION_ID= opr.WIP_ENTITY_ID'
453 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
454 ||' AND opr.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
455
456 OPEN cgen FOR lv_cursor_stmt;
457
458 IF (cgen%ISOPEN) THEN
459
460 LOOP
461
462 FETCH cgen INTO
463 lv_TRANSACTION_ID,
464 lv_OPERATION_SEQ_NUM,
465 lv_SR_INSTANCE_ID;
466
467 EXIT WHEN cgen%NOTFOUND;
468
469 DELETE MSC_JOB_OPERATIONS
470 WHERE PLAN_ID= -1
471 AND TRANSACTION_ID = lv_TRANSACTION_ID
472 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
473 AND OPERATION_SEQ_NUM = nvl(lv_OPERATION_SEQ_NUM,OPERATION_SEQ_NUM);
474
475
476 END LOOP;
477
478 END IF;
479
480 COMMIT;
481
482 CLOSE cgen;
483
484 END IF; /*Incremental*/
485
486 lv_cursor_stmt:=
487 'SELECT'
488 ||' ms.TRANSACTION_ID,'
489 ||' opr.OPERATION_SEQ_NUM,'
490 ||' opr.SR_INSTANCE_ID,'
491 ||' opr.ORGANIZATION_ID,'
492 ||' opr.RECOMMENDED,'
493 ||' opr.NETWORK_START_END,'
494 ||' opr.RECO_START_DATE,'
495 ||' opr.RECO_COMPLETION_DATE,'
496 ||' opr.OPERATION_SEQUENCE_ID,'
497 ||' opr.STANDARD_OPERATION_CODE,'
498 ||' opr.DEPARTMENT_ID,'
499 ||' opr.OPERATION_LEAD_TIME_PERCENT,'
500 ||' opr.MINIMUM_TRANSFER_QUANTITY,'
501 ||' opr.EFFECTIVITY_DATE,'
502 ||' opr.DISABLE_DATE,'
503 ||' opr.OPERATION_TYPE,'
504 ||' opr.YIELD,'
505 ||' opr.CUMULATIVE_YIELD,'
506 ||' opr.REVERSE_CUMULATIVE_YIELD,'
507 ||' opr.NET_PLANNING_PERCENT'
508 ||' FROM '||lv_supplies_tbl||' ms,'
509 ||' MSC_ST_JOB_OPERATIONS opr'
510 ||' WHERE opr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
511 ||' AND ms.PLAN_ID= -1'
512 ||' AND ms.SR_INSTANCE_ID= opr.SR_INSTANCE_ID'
513 ||' AND ms.DISPOSITION_ID= opr.WIP_ENTITY_ID'
514 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
515 ||' AND opr.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
516
517
518 -- ========= Prepare SQL Statement for INSERT ==========
519 lv_sql_stmt:=
520 'insert into '||lv_tbl
521 ||' ( PLAN_ID,'
522 ||' TRANSACTION_ID,'
523 ||' OPERATION_SEQ_NUM,'
524 ||' ORGANIZATION_ID,'
525 ||' RECOMMENDED,'
526 ||' NETWORK_START_END,'
527 ||' RECO_START_DATE,'
528 ||' RECO_COMPLETION_DATE,'
529 ||' OPERATION_SEQUENCE_ID,'
530 ||' STANDARD_OPERATION_CODE,'
531 ||' DEPARTMENT_ID,'
532 ||' OPERATION_LEAD_TIME_PERCENT,'
533 ||' MINIMUM_TRANSFER_QUANTITY,'
534 ||' EFFECTIVITY_DATE,'
535 ||' DISABLE_DATE,'
536 ||' OPERATION_TYPE,'
537 ||' YIELD,'
538 ||' CUMULATIVE_YIELD,'
539 ||' REVERSE_CUMULATIVE_YIELD,'
540 ||' NET_PLANNING_PERCENT,'
541 ||' SR_INSTANCE_ID,'
542 ||' REFRESH_NUMBER,'
543 ||' LAST_UPDATE_DATE,'
544 ||' LAST_UPDATED_BY,'
545 ||' CREATION_DATE,'
546 ||' CREATED_BY)'
547 ||'VALUES'
548 ||'( -1,'
549 ||' :TRANSACTION_ID,'
550 ||' :OPERATION_SEQ_NUM,'
551 ||' :ORGANIZATION_ID,'
552 ||' :RECOMMENDED,'
553 ||' :NETWORK_START_END,'
554 ||' :RECO_START_DATE,'
555 ||' :RECO_COMPLETION_DATE,'
556 ||' :OPERATION_SEQUENCE_ID,'
557 ||' :STANDARD_OPERATION_CODE,'
558 ||' :DEPARTMENT_ID,'
559 ||' :OPERATION_LEAD_TIME_PERCENT,'
560 ||' :MINIMUM_TRANSFER_QUANTITY,'
561 ||' :EFFECTIVITY_DATE,'
562 ||' :DISABLE_DATE,'
563 ||' :OPERATION_TYPE,'
564 ||' :YIELD,'
565 ||' :CUMULATIVE_YIELD,'
566 ||' :REVERSE_CUMULATIVE_YIELD,'
567 ||' :NET_PLANNING_PERCENT,'
568 ||' :SR_INSTANCE_ID,'
569 ||' :REFRESH_NUMBER,'
570 ||' :v_current_date,'
571 ||' :v_current_user,'
572 ||' :v_current_date,'
573 ||' :v_current_user)';
574
575 OPEN cgen FOR lv_cursor_stmt;
576
577 IF (cgen%ISOPEN) THEN
578
579 LOOP
580
581 FETCH cgen INTO
582 lv_TRANSACTION_ID,
583 lv_OPERATION_SEQ_NUM,
584 lv_SR_INSTANCE_ID,
585 lv_ORGANIZATION_ID,
586 lv_RECOMMENDED,
587 lv_NETWORK_START_END,
588 lv_RECO_START_DATE,
589 lv_RECO_COMPLETION_DATE,
590 lv_OPERATION_SEQUENCE_ID,
591 lv_STANDARD_OPERATION_CODE,
592 lv_DEPARTMENT_ID,
593 lv_OP_LT_PERCENT,
594 lv_MINIMUM_TRANSFER_QUANTITY,
595 lv_EFFECTIVITY_DATE,
596 lv_DISABLE_DATE,
597 lv_OPERATION_TYPE,
598 lv_YIELD,
599 lv_CUMULATIVE_YIELD,
600 lv_REVERSE_CUMULATIVE_YIELD,
601 lv_NET_PLANNING_PERCENT;
602
603 EXIT WHEN cgen%NOTFOUND;
604
605 BEGIN
606
607 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
608
609 UPDATE MSC_JOB_OPERATIONS
610 SET
611 RECOMMENDED = lv_RECOMMENDED,
612 NETWORK_START_END = lv_NETWORK_START_END,
613 RECO_START_DATE = lv_RECO_START_DATE,
614 RECO_COMPLETION_DATE = lv_RECO_COMPLETION_DATE,
615 OPERATION_SEQUENCE_ID = lv_OPERATION_SEQUENCE_ID,
616 STANDARD_OPERATION_CODE = lv_STANDARD_OPERATION_CODE,
617 DEPARTMENT_ID = lv_DEPARTMENT_ID,
618 OPERATION_LEAD_TIME_PERCENT = lv_OP_LT_PERCENT,
619 MINIMUM_TRANSFER_QUANTITY = lv_MINIMUM_TRANSFER_QUANTITY,
620 EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE,
621 DISABLE_DATE = lv_DISABLE_DATE,
622 OPERATION_TYPE = lv_OPERATION_TYPE,
623 YIELD = lv_YIELD,
624 CUMULATIVE_YIELD = lv_CUMULATIVE_YIELD,
625 REVERSE_CUMULATIVE_YIELD = lv_REVERSE_CUMULATIVE_YIELD,
626 NET_PLANNING_PERCENT = lv_NET_PLANNING_PERCENT,
627 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
628 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
629 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
630 WHERE PLAN_ID= -1
631 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
632 AND TRANSACTION_ID= lv_TRANSACTION_ID
633 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
634 AND OPERATION_SEQ_NUM = lv_OPERATION_SEQ_NUM;
635
636
637 END IF;
638
639 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
640
641 EXECUTE IMMEDIATE lv_sql_stmt
642 USING
643 lv_TRANSACTION_ID,
644 lv_OPERATION_SEQ_NUM,
645 lv_ORGANIZATION_ID,
646 lv_RECOMMENDED,
647 lv_NETWORK_START_END,
648 lv_RECO_START_DATE,
649 lv_RECO_COMPLETION_DATE,
650 lv_OPERATION_SEQUENCE_ID,
651 lv_STANDARD_OPERATION_CODE,
652 lv_DEPARTMENT_ID,
653 lv_OP_LT_PERCENT,
654 lv_MINIMUM_TRANSFER_QUANTITY,
655 lv_EFFECTIVITY_DATE,
656 lv_DISABLE_DATE,
657 lv_OPERATION_TYPE,
658 lv_YIELD,
659 lv_CUMULATIVE_YIELD,
660 lv_REVERSE_CUMULATIVE_YIELD,
661 lv_NET_PLANNING_PERCENT,
662 lv_SR_INSTANCE_ID,
663 MSC_CL_COLLECTION.v_last_collection_id,
664 MSC_CL_COLLECTION.v_current_date,
665 MSC_CL_COLLECTION.v_current_user,
666 MSC_CL_COLLECTION.v_current_date,
667 MSC_CL_COLLECTION.v_current_user;
668 total_count := total_count + 1;
669 END IF;
670
671
672 EXCEPTION
673 WHEN OTHERS THEN
674
675 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
676
677 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
678 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
679 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
680 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
681 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
682
683 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
684 RAISE;
685
686 ELSE
687
688 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
689
690 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
691 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
692 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP');
693 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATIONS');
694 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
695
696 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
697 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
698 FND_MESSAGE.SET_TOKEN('VALUE',
699 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
700 MSC_CL_COLLECTION.v_instance_id));
701 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
702
703 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
704 END IF;
705
706 END;
707
708 END LOOP;
709
710 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OPERATIONS = '|| total_count);
711 END IF;
712
713 CLOSE cgen;
714
715 COMMIT;
716
717 BEGIN
718
719 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
720
721 lv_tbl:= 'JOB_OPERATIONS_'||MSC_CL_COLLECTION.v_instance_code;
722
723 lv_sql_stmt:=
724 'INSERT INTO '||lv_tbl
725 ||' SELECT * from MSC_JOB_OPERATIONS'
726 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
727 ||' AND plan_id = -1 '
728 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
729
730 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
731 EXECUTE IMMEDIATE lv_sql_stmt;
732
733 COMMIT;
734
735 END IF;
736
737 EXCEPTION
738 WHEN OTHERS THEN
739
740 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
741 RAISE;
742 END;
743
744 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
745 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
746 lv_retcode,
747 'MSC_JOB_OPERATIONS',
748 MSC_CL_COLLECTION.v_instance_code,
749 MSC_UTIL.G_WARNING
750 );
751
752 IF lv_retcode = MSC_UTIL.G_ERROR THEN
753 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
754 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
755 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
756 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
757 END IF;
758
759 END IF;
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 IF cgen%ISOPEN THEN CLOSE cgen; END IF;
764
765 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_OP>>');
766 IF lv_cursor_stmt IS NOT NULL THEN
767 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
768 END IF;
769 IF lv_sql_stmt IS NOT NULL THEN
770 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
771 END IF;
772 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
773 RAISE;
774 END LOAD_JOB_OP;
775
776 --===============================================================
777 PROCEDURE LOAD_JOB_OP_RES IS
778
779 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
780 cgen CurTyp;
781
782 lv_tbl VARCHAR2(30);
783 lv_supplies_tbl VARCHAR2(30);
784
785 lv_cursor_stmt VARCHAR2(5000);
786 lv_sql_stmt VARCHAR2(5000);
787
788 lv_TRANSACTION_ID NUMBER;
789 lv_OPERATION_SEQ_NUM NUMBER;
790 lv_SR_INSTANCE_ID NUMBER;
791 lv_RESOURCE_SEQ_NUM NUMBER;
792 lv_ALTERNATE_NUM NUMBER;
793 lv_RECOMMENDED VARCHAR2(1);
794 lv_RECO_START_DATE DATE;
795 lv_RECO_COMPLETION_DATE DATE;
796 lv_RESOURCE_ID NUMBER;
797 lv_ASSIGNED_UNITS NUMBER;
798 lv_USAGE_RATE_OR_AMOUNT NUMBER;
799 lv_UOM_CODE VARCHAR2(3);
800 lv_BASIS_TYPE NUMBER;
801 lv_RESOURCE_OFFSET_PERCENT NUMBER;
802 lv_SCHEDULE_SEQ_NUM NUMBER;
803 lv_PRINCIPAL_FLAG NUMBER;
804 lv_SCHEDULE_FLAG NUMBER;
805 lv_DEPARTMENT_ID NUMBER;
806 lv_ORGANIZATION_ID NUMBER;
807 lv_ACTIVITY_GROUP_ID NUMBER;
808 lv_GROUP_SEQUENCE_NUMBER NUMBER; /* ds change change start */
809 lv_GROUP_SEQUENCE_ID NUMBER;
810 lv_BATCH_NUMBER NUMBER;
811 lv_FIRM_FLAG NUMBER;
812 lv_SETUP_ID NUMBER;
813 lv_PARENT_SEQ_NUM NUMBER;
814 lv_MAXIMUM_ASSIGNED_UNITS NUMBER; /* ds change change end */
815 total_count NUMBER := 0;
816
817 lv_errbuf VARCHAR2(240);
818 lv_retcode NUMBER;
819
820 BEGIN
821
822 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
823 lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
824 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
825 ELSE
826 lv_tbl:= 'MSC_JOB_OP_RESOURCES';
827 lv_supplies_tbl:= 'MSC_SUPPLIES';
828 END IF;
829
830 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
831
832 lv_cursor_stmt:=
833 'SELECT'
834 ||' ms.TRANSACTION_ID,'
835 ||' res.OPERATION_SEQ_NUM,'
836 ||' res.RESOURCE_SEQ_NUM,'
837 ||' res.SR_INSTANCE_ID'
838 ||' FROM '||lv_supplies_tbl||' ms,'
839 ||' MSC_ST_JOB_OP_RESOURCES res'
840 ||' WHERE res.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
841 ||' AND ms.PLAN_ID= -1'
842 ||' AND ms.SR_INSTANCE_ID= res.SR_INSTANCE_ID'
843 ||' AND ms.DISPOSITION_ID= res.WIP_ENTITY_ID'
844 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
845 ||' AND res.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
846
847 OPEN cgen FOR lv_cursor_stmt;
848
849 IF (cgen%ISOPEN) THEN
850
851 LOOP
852
853 FETCH cgen INTO
854 lv_TRANSACTION_ID,
855 lv_OPERATION_SEQ_NUM,
856 lv_RESOURCE_SEQ_NUM,
857 lv_SR_INSTANCE_ID;
858
859 EXIT WHEN cgen%NOTFOUND;
860
861 DELETE MSC_JOB_OP_RESOURCES
862 WHERE PLAN_ID= -1
863 AND TRANSACTION_ID = lv_TRANSACTION_ID
864 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
865 AND OPERATION_SEQ_NUM = nvl(lv_OPERATION_SEQ_NUM,OPERATION_SEQ_NUM)
866 AND RESOURCE_SEQ_NUM = nvl(lv_RESOURCE_SEQ_NUM,RESOURCE_SEQ_NUM);
867
868
869 END LOOP;
870
871 END IF;
872
873 COMMIT;
874
875 CLOSE cgen;
876
877 END IF; /*Incremental*/
878
879 lv_cursor_stmt:=
880 'SELECT'
881 ||' ms.TRANSACTION_ID,'
882 ||' res.OPERATION_SEQ_NUM ,'
883 ||' res.SR_INSTANCE_ID,'
884 ||' res.RESOURCE_SEQ_NUM,'
885 ||' res.ALTERNATE_NUM,'
886 ||' res.RECOMMENDED,'
887 ||' res.RECO_START_DATE,'
888 ||' res.ORGANIZATION_ID,'
889 ||' res.RECO_COMPLETION_DATE,'
890 ||' res.RESOURCE_ID,'
891 ||' res.ASSIGNED_UNITS,'
892 ||' res.USAGE_RATE_OR_AMOUNT,'
893 ||' res.UOM_CODE,'
894 ||' res.BASIS_TYPE,'
895 ||' res.RESOURCE_OFFSET_PERCENT,'
896 ||' res.SCHEDULE_SEQ_NUM,'
897 ||' res.PRINCIPAL_FLAG,'
898 ||' res.SCHEDULE_FLAG,'
899 ||' res.DEPARTMENT_ID,'
900 ||' res.ACTIVITY_GROUP_ID,'
901 ||' res.GROUP_SEQUENCE_NUMBER,' /* ds change change start */
902 ||' res.GROUP_SEQUENCE_ID,'
903 ||' res.BATCH_NUMBER,'
904 ||' res.FIRM_FLAG,'
905 ||' res.SETUP_ID,'
906 ||' res.PARENT_SEQ_NUM,'
907 ||' res.MAXIMUM_ASSIGNED_UNITS' /* ds change change end */
908 ||' FROM '||lv_supplies_tbl||' ms,'
909 ||' MSC_ST_JOB_OP_RESOURCES res'
910 ||' WHERE res.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
911 ||' AND ms.PLAN_ID= -1'
912 ||' AND ms.SR_INSTANCE_ID= res.SR_INSTANCE_ID'
913 ||' AND ms.DISPOSITION_ID= res.WIP_ENTITY_ID'
914 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
915 ||' AND res.DELETED_FLAG= '||MSC_UTIL.SYS_NO;
916
917
918 -- ========= Prepare SQL Statement for INSERT ==========
919 lv_sql_stmt:=
920 'insert into '||lv_tbl
921 ||' ( PLAN_ID,'
922 ||' TRANSACTION_ID,'
923 ||' OPERATION_SEQ_NUM,'
924 ||' RESOURCE_SEQ_NUM,'
925 ||' ALTERNATE_NUM,'
926 ||' RECOMMENDED,'
927 ||' RECO_START_DATE,'
928 ||' RECO_COMPLETION_DATE,'
929 ||' ORGANIZATION_ID,'
930 ||' RESOURCE_ID,'
931 ||' ASSIGNED_UNITS,'
932 ||' USAGE_RATE_OR_AMOUNT,'
933 ||' UOM_CODE,'
934 ||' BASIS_TYPE,'
935 ||' RESOURCE_OFFSET_PERCENT,'
936 ||' SCHEDULE_SEQ_NUM,'
937 ||' PRINCIPAL_FLAG,'
938 ||' SCHEDULE_FLAG,'
939 ||' DEPARTMENT_ID,'
940 ||' ACTIVITY_GROUP_ID,'
941 ||' GROUP_SEQUENCE_NUMBER,' /* ds change change start */
942 ||' GROUP_SEQUENCE_ID,'
943 ||' BATCH_NUMBER,'
944 ||' FIRM_FLAG,'
945 ||' SETUP_ID,'
946 ||' PARENT_SEQ_NUM,'
947 ||' MAXIMUM_ASSIGNED_UNITS,' /* ds change change end */
948 ||' SR_INSTANCE_ID,'
949 ||' REFRESH_NUMBER,'
950 ||' LAST_UPDATE_DATE,'
951 ||' LAST_UPDATED_BY,'
952 ||' CREATION_DATE,'
953 ||' CREATED_BY)'
954 ||'VALUES'
955 ||'( -1,'
956 ||' :TRANSACTION_ID,'
957 ||' :OPERATION_SEQ_NUM,'
958 ||' :RESOURCE_SEQ_NUM,'
959 ||' :ALTERNATE_NUM,'
960 ||' :RECOMMENDED,'
961 ||' :RECO_START_DATE,'
962 ||' :RECO_COMPLETION_DATE,'
963 ||' :ORGANIZATION_ID,'
964 ||' :RESOURCE_ID,'
965 ||' :ASSIGNED_UNITS,'
966 ||' :USAGE_RATE_OR_AMOUNT,'
967 ||' :UOM_CODE,'
968 ||' :BASIS_TYPE,'
969 ||' :RESOURCE_OFFSET_PERCENT,'
970 ||' :SCHEDULE_SEQ_NUM,'
971 ||' :PRINCIPAL_FLAG,'
972 ||' :SCHEDULE_FLAG,'
973 ||' :DEPARTMENT_ID,'
974 ||' :ACTIVITY_GROUP_ID,'
975 ||' :GROUP_SEQUENCE_NUMBER,' /* ds change change start */
976 ||' :GROUP_SEQUENCE_ID,'
977 ||' :BATCH_NUMBER,'
978 ||' :FIRM_FLAG,'
979 ||' :SETUP_ID,'
980 ||' :PARENT_SEQ_NUM,'
981 ||' :MAXIMUM_ASSIGNED_UNITS,' /* ds change change end */
982 ||' :SR_INSTANCE_ID,'
983 ||' :REFRESH_NUMBER,'
984 ||' :v_current_date,'
985 ||' :v_current_user,'
986 ||' :v_current_date,'
987 ||' :v_current_user)';
988
989 --log_debug(lv_cursor_stmt);
990 --log_debug(lv_sql_stmt);
991 OPEN cgen FOR lv_cursor_stmt;
992
993 IF (cgen%ISOPEN) THEN
994
995 LOOP
996
997 FETCH cgen INTO
998 lv_TRANSACTION_ID ,
999 lv_OPERATION_SEQ_NUM ,
1000 lv_SR_INSTANCE_ID ,
1001 lv_RESOURCE_SEQ_NUM ,
1002 lv_ALTERNATE_NUM ,
1003 lv_RECOMMENDED ,
1004 lv_RECO_START_DATE,
1005 lv_ORGANIZATION_ID ,
1006 lv_RECO_COMPLETION_DATE,
1007 lv_RESOURCE_ID ,
1008 lv_ASSIGNED_UNITS ,
1009 lv_USAGE_RATE_OR_AMOUNT ,
1010 lv_UOM_CODE,
1011 lv_BASIS_TYPE ,
1012 lv_RESOURCE_OFFSET_PERCENT ,
1013 lv_SCHEDULE_SEQ_NUM ,
1014 lv_PRINCIPAL_FLAG ,
1015 lv_SCHEDULE_FLAG ,
1016 lv_DEPARTMENT_ID ,
1017 lv_ACTIVITY_GROUP_ID,
1018 lv_GROUP_SEQUENCE_NUMBER, /* ds change change start */
1019 lv_GROUP_SEQUENCE_ID,
1020 lv_BATCH_NUMBER,
1021 lv_FIRM_FLAG,
1022 lv_SETUP_ID,
1023 lv_PARENT_SEQ_NUM,
1024 lv_MAXIMUM_ASSIGNED_UNITS; /* ds change change end */
1025
1026 EXIT WHEN cgen%NOTFOUND;
1027
1028 BEGIN
1029
1030 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1031
1032 UPDATE MSC_JOB_OP_RESOURCES
1033 SET
1034 RECOMMENDED = lv_RECOMMENDED,
1035 RECO_START_DATE = lv_RECO_START_DATE,
1036 RECO_COMPLETION_DATE = lv_RECO_COMPLETION_DATE,
1037 RESOURCE_ID = lv_RESOURCE_ID,
1038 ASSIGNED_UNITS = lv_ASSIGNED_UNITS,
1039 USAGE_RATE_OR_AMOUNT = lv_USAGE_RATE_OR_AMOUNT,
1040 UOM_CODE = lv_UOM_CODE,
1041 BASIS_TYPE = lv_BASIS_TYPE,
1042 RESOURCE_OFFSET_PERCENT = lv_RESOURCE_OFFSET_PERCENT,
1043 SCHEDULE_SEQ_NUM = lv_SCHEDULE_SEQ_NUM,
1044 PRINCIPAL_FLAG = lv_PRINCIPAL_FLAG,
1045 SCHEDULE_FLAG = lv_SCHEDULE_FLAG,
1046 DEPARTMENT_ID = lv_DEPARTMENT_ID,
1047 ACTIVITY_GROUP_ID = lv_ACTIVITY_GROUP_ID,
1048 GROUP_SEQUENCE_NUMBER = lv_GROUP_SEQUENCE_NUMBER, /* ds change change start */
1049 GROUP_SEQUENCE_ID = lv_GROUP_SEQUENCE_ID,
1050 BATCH_NUMBER = lv_BATCH_NUMBER,
1051 FIRM_FLAG = lv_FIRM_FLAG,
1052 SETUP_ID = lv_SETUP_ID,
1053 PARENT_SEQ_NUM = lv_PARENT_SEQ_NUM,
1054 MAXIMUM_ASSIGNED_UNITS = lv_MAXIMUM_ASSIGNED_UNITS, /* ds change change end */
1055 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1056 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1057 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1058 WHERE PLAN_ID= -1
1059 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
1060 AND TRANSACTION_ID= lv_TRANSACTION_ID
1061 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
1062 AND OPERATION_SEQ_NUM = lv_OPERATION_SEQ_NUM
1063 AND RESOURCE_SEQ_NUM = lv_RESOURCE_SEQ_NUM
1064 AND ALTERNATE_NUM = lv_ALTERNATE_NUM;
1065
1066
1067 END IF;
1068
1069 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1070
1071 EXECUTE IMMEDIATE lv_sql_stmt
1072 USING
1073 lv_TRANSACTION_ID ,
1074 lv_OPERATION_SEQ_NUM ,
1075 lv_RESOURCE_SEQ_NUM ,
1076 lv_ALTERNATE_NUM ,
1077 lv_RECOMMENDED ,
1078 lv_RECO_START_DATE,
1079 lv_RECO_COMPLETION_DATE,
1080 lv_ORGANIZATION_ID ,
1081 lv_RESOURCE_ID ,
1082 lv_ASSIGNED_UNITS ,
1083 lv_USAGE_RATE_OR_AMOUNT ,
1084 lv_UOM_CODE,
1085 lv_BASIS_TYPE ,
1086 lv_RESOURCE_OFFSET_PERCENT ,
1087 lv_SCHEDULE_SEQ_NUM ,
1088 lv_PRINCIPAL_FLAG ,
1089 lv_SCHEDULE_FLAG ,
1090 lv_DEPARTMENT_ID ,
1091 lv_ACTIVITY_GROUP_ID,
1092 lv_GROUP_SEQUENCE_NUMBER, /* ds change change start */
1093 lv_GROUP_SEQUENCE_ID,
1094 lv_BATCH_NUMBER,
1095 lv_FIRM_FLAG,
1096 lv_SETUP_ID,
1097 lv_PARENT_SEQ_NUM,
1098 lv_MAXIMUM_ASSIGNED_UNITS, /* ds change change end */
1099 lv_SR_INSTANCE_ID,
1100 MSC_CL_COLLECTION.v_last_collection_id,
1101 MSC_CL_COLLECTION.v_current_date,
1102 MSC_CL_COLLECTION.v_current_user,
1103 MSC_CL_COLLECTION.v_current_date,
1104 MSC_CL_COLLECTION.v_current_user;
1105
1106 total_count := total_count +1;
1107 END IF;
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111
1112 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1113
1114 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1115 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1116 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1117 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1119
1120 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1121 RAISE;
1122
1123 ELSE
1124
1125 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1126
1127 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1128 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1129 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_RES');
1130 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OP_RESOURCES');
1131 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1132
1133 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1134 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1135 FND_MESSAGE.SET_TOKEN('VALUE',
1136 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1137 MSC_CL_COLLECTION.v_instance_id));
1138 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1139
1140 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1141 END IF;
1142
1143 END;
1144 END LOOP;
1145
1146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OP_RESOURCES = '|| total_count);
1147 END IF;
1148
1149 CLOSE cgen;
1150
1151 COMMIT;
1152
1153 BEGIN
1154
1155 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
1156
1157 lv_tbl:= 'JOB_OP_RESOURCES_'||MSC_CL_COLLECTION.v_instance_code;
1158
1159 lv_sql_stmt:=
1160 'INSERT INTO '||lv_tbl
1161 ||' SELECT * from MSC_JOB_OP_RESOURCES'
1162 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1163 ||' AND plan_id = -1 '
1164 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1165
1166 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1167 EXECUTE IMMEDIATE lv_sql_stmt;
1168
1169 COMMIT;
1170
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175
1176 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1177 RAISE;
1178 END;
1179
1180 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1181 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1182 lv_retcode,
1183 'MSC_JOB_OP_RESOURCES',
1184 MSC_CL_COLLECTION.v_instance_code,
1185 MSC_UTIL.G_WARNING
1186 );
1187
1188 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1189 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1190 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1191 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1192 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1193 END IF;
1194
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1200
1201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_OP_RES>>');
1202 IF lv_cursor_stmt IS NOT NULL THEN
1203 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
1204 END IF;
1205 IF lv_sql_stmt IS NOT NULL THEN
1206 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
1207 END IF;
1208 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1209 RAISE;
1210 END LOAD_JOB_OP_RES;
1211
1212 --=========================================================================
1213 PROCEDURE LOAD_JOB_REQ_OP IS
1214
1215 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
1216
1217 cgen CurTyp;
1218
1219 lv_tbl VARCHAR2(30);
1220 lv_supplies_tbl VARCHAR2(30);
1221
1222 lv_cursor_stmt VARCHAR2(5000);
1223 lv_sql_stmt VARCHAR2(5000);
1224
1225
1226 lv_TRANSACTION_ID NUMBER;
1227 lv_OPERATION_SEQ_NUM NUMBER;
1228 lv_SR_INSTANCE_ID NUMBER;
1229 lv_COMPONENT_ITEM_ID NUMBER;
1230 lv_PRIMARY_COMPONENT_ID NUMBER;
1231 lv_SOURCE_PHANTOM_ID NUMBER;
1232 lv_COMPONENT_SEQUENCE_ID NUMBER;
1233 lv_RECOMMENDED VARCHAR2(1);
1234 lv_RECO_DATE_REQUIRED DATE;
1235 lv_ORGANIZATION_ID NUMBER;
1236 lv_COMPONENT_PRIORITY NUMBER;
1237 lv_DEPARTMENT_ID NUMBER;
1238 lv_QUANTITY_PER_ASSEMBLY NUMBER;
1239 lv_COMPONENT_YIELD_FACTOR NUMBER;
1240 lv_EFFECTIVITY_DATE DATE;
1241 lv_DISABLE_DATE DATE;
1242 lv_PLANNING_FACTOR NUMBER;
1243 lv_LOW_QUANTITY NUMBER;
1244 lv_HIGH_QUANTITY NUMBER;
1245 lv_OP_LT_PERCENT NUMBER;
1246 lv_WIP_SUPPLY_TYPE NUMBER;
1247 lv_FROM_END_ITEM_UNIT_NUMBER VARCHAR2(30);
1248 lv_TO_END_ITEM_UNIT_NUMBER VARCHAR2(30);
1249 lv_COMPONENT_SCALING_TYPE NUMBER; /* Discrete Mfg Enahancements Bug 4492736 */
1250
1251 lv_count NUMBER;
1252 c_count NUMBER;
1253 total_count NUMBER := 0;
1254
1255 lv_errbuf VARCHAR2(240);
1256 lv_retcode NUMBER;
1257
1258 BEGIN
1259
1260 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1261 lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1262 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1263 ELSE
1264 lv_tbl:= 'MSC_JOB_REQUIREMENT_OPS';
1265 lv_supplies_tbl:= 'MSC_SUPPLIES';
1266 END IF;
1267
1268 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1269
1270 lv_cursor_stmt:=
1271 'SELECT'
1272 ||' ms.TRANSACTION_ID,'
1273 ||' req.OPERATION_SEQ_NUM,'
1274 ||' cmp_itm.INVENTORY_ITEM_ID ,'
1275 ||' pri_cmp.INVENTORY_ITEM_ID ,'
1276 ||' src_ptm.INVENTORY_ITEM_ID ,'
1277 ||' req.COMPONENT_SEQUENCE_ID,'
1278 ||' req.SR_INSTANCE_ID'
1279 ||' FROM '||lv_supplies_tbl||' ms,'
1280 ||' MSC_ST_JOB_REQUIREMENT_OPS req,'
1281 ||' MSC_ITEM_ID_LID cmp_itm, '
1282 ||' MSC_ITEM_ID_LID pri_cmp, '
1283 ||' MSC_ITEM_ID_LID src_ptm '
1284 ||' WHERE req.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1285 ||' AND ms.PLAN_ID= -1'
1286 ||' AND ms.SR_INSTANCE_ID= req.SR_INSTANCE_ID'
1287 ||' AND ms.DISPOSITION_ID= req.WIP_ENTITY_ID'
1288 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
1289 ||' AND req.DELETED_FLAG= '||MSC_UTIL.SYS_YES
1290 ||' AND cmp_itm.SR_INVENTORY_ITEM_ID = req.COMPONENT_ITEM_ID'
1291 ||' AND pri_cmp.SR_INVENTORY_ITEM_ID = req.PRIMARY_COMPONENT_ID'
1292 ||' AND src_ptm.SR_INVENTORY_ITEM_ID(+) = req.SOURCE_PHANTOM_ID'
1293 ||' AND cmp_itm.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1294 ||' AND pri_cmp.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1295 ||' AND src_ptm.SR_INSTANCE_ID(+) = req.SR_INSTANCE_ID';
1296
1297 OPEN cgen FOR lv_cursor_stmt;
1298
1299 IF (cgen%ISOPEN) THEN
1300
1301 LOOP
1302
1303 FETCH cgen INTO
1304 lv_TRANSACTION_ID,
1305 lv_OPERATION_SEQ_NUM,
1306 lv_COMPONENT_ITEM_ID,
1307 lv_PRIMARY_COMPONENT_ID,
1308 lv_SOURCE_PHANTOM_ID,
1309 lv_COMPONENT_SEQUENCE_ID,
1310 lv_SR_INSTANCE_ID;
1311
1312 EXIT WHEN cgen%NOTFOUND;
1313
1314 DELETE MSC_JOB_REQUIREMENT_OPS
1315 WHERE PLAN_ID= -1
1316 AND TRANSACTION_ID = lv_TRANSACTION_ID
1317 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
1318 AND OPERATION_SEQ_NUM = nvl(lv_OPERATION_SEQ_NUM,OPERATION_SEQ_NUM)
1319 AND COMPONENT_ITEM_ID = nvl(lv_COMPONENT_ITEM_ID,COMPONENT_ITEM_ID)
1320 AND PRIMARY_COMPONENT_ID = nvl(lv_PRIMARY_COMPONENT_ID,PRIMARY_COMPONENT_ID)
1321 AND ((SOURCE_PHANTOM_ID is NULL AND lv_SOURCE_PHANTOM_ID is NULL)OR (SOURCE_PHANTOM_ID = nvl(lv_SOURCE_PHANTOM_ID,SOURCE_PHANTOM_ID)))
1322 AND COMPONENT_SEQUENCE_ID = nvl(lv_COMPONENT_SEQUENCE_ID,COMPONENT_SEQUENCE_ID);
1323
1324
1325 END LOOP;
1326
1327 END IF;
1328
1329 COMMIT;
1330
1331 CLOSE cgen;
1332
1333 END IF; /*Incremental*/
1334
1335
1336 lv_cursor_stmt:=
1337 'SELECT'
1338 ||' ms.TRANSACTION_ID,'
1339 ||' req.OPERATION_SEQ_NUM ,'
1340 ||' req.SR_INSTANCE_ID,'
1341 ||' cmp_itm.INVENTORY_ITEM_ID ,'
1342 ||' pri_cmp.INVENTORY_ITEM_ID ,'
1343 ||' src_ptm.INVENTORY_ITEM_ID ,'
1344 ||' req.COMPONENT_SEQUENCE_ID,'
1345 ||' req.RECOMMENDED,'
1346 ||' req.RECO_DATE_REQUIRED,'
1347 ||' req.ORGANIZATION_ID,'
1348 ||' req.COMPONENT_PRIORITY,'
1349 ||' req.DEPARTMENT_ID,'
1350 ||' req.QUANTITY_PER_ASSEMBLY,'
1351 ||' req.COMPONENT_YIELD_FACTOR,'
1352 ||' req.EFFECTIVITY_DATE,'
1353 ||' req.DISABLE_DATE,'
1354 ||' req.PLANNING_FACTOR,'
1355 ||' req.LOW_QUANTITY,'
1356 ||' req.HIGH_QUANTITY,'
1357 ||' req.OPERATION_LEAD_TIME_PERCENT,'
1358 ||' req.WIP_SUPPLY_TYPE,'
1359 ||' req.FROM_END_ITEM_UNIT_NUMBER,'
1360 ||' req.TO_END_ITEM_UNIT_NUMBER,'
1361 ||' req.COMPONENT_SCALING_TYPE' /* Discrete Mfg Enahancements Bug 4492736 */
1362 ||' FROM '||lv_supplies_tbl||' ms,'
1363 ||' MSC_ST_JOB_REQUIREMENT_OPS req,'
1364 ||' MSC_ITEM_ID_LID cmp_itm, '
1365 ||' MSC_ITEM_ID_LID pri_cmp, '
1366 ||' MSC_ITEM_ID_LID src_ptm '
1367 ||' WHERE req.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1368 ||' AND ms.PLAN_ID= -1'
1369 ||' AND ms.SR_INSTANCE_ID= req.SR_INSTANCE_ID'
1370 ||' AND ms.DISPOSITION_ID= req.WIP_ENTITY_ID'
1371 ||' AND ms.ORDER_TYPE IN ( 3, 7)'
1372 ||' AND req.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1373 ||' AND cmp_itm.SR_INVENTORY_ITEM_ID = req.COMPONENT_ITEM_ID'
1374 ||' AND pri_cmp.SR_INVENTORY_ITEM_ID = req.PRIMARY_COMPONENT_ID'
1375 ||' AND src_ptm.SR_INVENTORY_ITEM_ID(+) = req.SOURCE_PHANTOM_ID'
1376 ||' AND cmp_itm.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1377 ||' AND pri_cmp.SR_INSTANCE_ID = req.SR_INSTANCE_ID'
1378 ||' AND src_ptm.SR_INSTANCE_ID(+) = req.SR_INSTANCE_ID';
1379
1380
1381 -- ========= Prepare SQL Statement for INSERT ==========
1382 lv_sql_stmt:=
1383 'insert into '||lv_tbl
1384 ||' ( PLAN_ID,'
1385 ||' TRANSACTION_ID,'
1386 ||' OPERATION_SEQ_NUM,'
1387 ||' COMPONENT_ITEM_ID,'
1388 ||' PRIMARY_COMPONENT_ID,'
1389 ||' SOURCE_PHANTOM_ID,'
1390 ||' COMPONENT_SEQUENCE_ID,'
1391 ||' RECOMMENDED,'
1392 ||' RECO_DATE_REQUIRED,'
1393 ||' ORGANIZATION_ID,'
1394 ||' COMPONENT_PRIORITY,'
1395 ||' DEPARTMENT_ID,'
1396 ||' QUANTITY_PER_ASSEMBLY,'
1397 ||' COMPONENT_YIELD_FACTOR,'
1398 ||' EFFECTIVITY_DATE,'
1399 ||' DISABLE_DATE,'
1400 ||' PLANNING_FACTOR,'
1401 ||' LOW_QUANTITY,'
1402 ||' HIGH_QUANTITY,'
1403 ||' OPERATION_LEAD_TIME_PERCENT,'
1404 ||' WIP_SUPPLY_TYPE,'
1405 ||' FROM_END_ITEM_UNIT_NUMBER,'
1406 ||' TO_END_ITEM_UNIT_NUMBER,'
1407 ||' COMPONENT_SCALING_TYPE,'
1408 ||' SR_INSTANCE_ID,'
1409 ||' REFRESH_NUMBER,'
1410 ||' LAST_UPDATE_DATE,'
1411 ||' LAST_UPDATED_BY,'
1412 ||' CREATION_DATE,'
1413 ||' CREATED_BY)'
1414 ||'VALUES'
1415 ||'( -1,'
1416 ||' :TRANSACTION_ID,'
1417 ||' :OPERATION_SEQ_NUM,'
1418 ||' :COMPONENT_ITEM_ID,'
1419 ||' :PRIMARY_COMPONENT_ID,'
1420 ||' :SOURCE_PHANTOM_ID,'
1421 ||' :COMPONENT_SEQUENCE_ID,'
1422 ||' :RECOMMENDED,'
1423 ||' :RECO_DATE_REQUIRED,'
1424 ||' :ORGANIZATION_ID,'
1425 ||' :COMPONENT_PRIORITY,'
1426 ||' :DEPARTMENT_ID,'
1427 ||' :QUANTITY_PER_ASSEMBLY,'
1428 ||' :COMPONENT_YIELD_FACTOR,'
1429 ||' :EFFECTIVITY_DATE,'
1430 ||' :DISABLE_DATE,'
1431 ||' :PLANNING_FACTOR,'
1432 ||' :LOW_QUANTITY,'
1433 ||' :HIGH_QUANTITY,'
1434 ||' :OPERATION_LEAD_TIME_PERCENT,'
1435 ||' :WIP_SUPPLY_TYPE,'
1436 ||' :FROM_END_ITEM_UNIT_NUMBER,'
1437 ||' :TO_END_ITEM_UNIT_NUMBER,'
1438 ||' :COMPONENT_SCALING_TYPE,'
1439 ||' :SR_INSTANCE_ID,'
1440 ||' :REFRESH_NUMBER,'
1441 ||' :v_current_date,'
1442 ||' :v_current_user,'
1443 ||' :v_current_date,'
1444 ||' :v_current_user)';
1445
1446
1447 OPEN cgen FOR lv_cursor_stmt;
1448
1449 c_count := 0;
1450
1451 IF (cgen%ISOPEN) THEN
1452
1453 LOOP
1454
1455 FETCH cgen INTO
1456 lv_TRANSACTION_ID,
1457 lv_OPERATION_SEQ_NUM ,
1458 lv_SR_INSTANCE_ID,
1459 lv_COMPONENT_ITEM_ID,
1460 lv_PRIMARY_COMPONENT_ID,
1461 lv_SOURCE_PHANTOM_ID,
1462 lv_COMPONENT_SEQUENCE_ID,
1463 lv_RECOMMENDED,
1464 lv_RECO_DATE_REQUIRED,
1465 lv_ORGANIZATION_ID,
1466 lv_COMPONENT_PRIORITY,
1467 lv_DEPARTMENT_ID,
1468 lv_QUANTITY_PER_ASSEMBLY,
1469 lv_COMPONENT_YIELD_FACTOR,
1470 lv_EFFECTIVITY_DATE,
1471 lv_DISABLE_DATE,
1472 lv_PLANNING_FACTOR,
1473 lv_LOW_QUANTITY,
1474 lv_HIGH_QUANTITY,
1475 lv_OP_LT_PERCENT,
1476 lv_WIP_SUPPLY_TYPE,
1477 lv_FROM_END_ITEM_UNIT_NUMBER,
1478 lv_TO_END_ITEM_UNIT_NUMBER,
1479 lv_COMPONENT_SCALING_TYPE;
1480
1481 EXIT WHEN cgen%NOTFOUND;
1482
1483 BEGIN
1484
1485 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1486
1487 UPDATE MSC_JOB_REQUIREMENT_OPS
1488 SET
1489 RECOMMENDED = lv_RECOMMENDED,
1490 RECO_DATE_REQUIRED = lv_RECO_DATE_REQUIRED,
1491 COMPONENT_PRIORITY = lv_COMPONENT_PRIORITY,
1492 DEPARTMENT_ID = lv_DEPARTMENT_ID,
1493 QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY,
1494 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR,
1495 EFFECTIVITY_DATE = lv_EFFECTIVITY_DATE,
1496 DISABLE_DATE = lv_DISABLE_DATE,
1497 PLANNING_FACTOR = lv_PLANNING_FACTOR,
1498 LOW_QUANTITY = lv_LOW_QUANTITY,
1499 HIGH_QUANTITY = lv_HIGH_QUANTITY,
1500 OPERATION_LEAD_TIME_PERCENT = lv_OP_LT_PERCENT,
1501 WIP_SUPPLY_TYPE = lv_WIP_SUPPLY_TYPE,
1502 FROM_END_ITEM_UNIT_NUMBER = lv_FROM_END_ITEM_UNIT_NUMBER,
1503 TO_END_ITEM_UNIT_NUMBER = lv_TO_END_ITEM_UNIT_NUMBER,
1504 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE,
1505 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1506 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1507 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1508 WHERE PLAN_ID= -1
1509 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
1510 AND TRANSACTION_ID= lv_TRANSACTION_ID
1511 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
1512 AND OPERATION_SEQ_NUM = lv_OPERATION_SEQ_NUM
1513 AND COMPONENT_ITEM_ID = lv_COMPONENT_ITEM_ID
1514 AND PRIMARY_COMPONENT_ID = lv_PRIMARY_COMPONENT_ID
1515 AND ((SOURCE_PHANTOM_ID is null AND lv_SOURCE_PHANTOM_ID is null) OR (SOURCE_PHANTOM_ID = lv_SOURCE_PHANTOM_ID))
1516 AND COMPONENT_SEQUENCE_ID = lv_COMPONENT_SEQUENCE_ID;
1517
1518
1519 END IF;
1520
1521 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1522
1523 EXECUTE IMMEDIATE lv_sql_stmt
1524 USING
1525 lv_TRANSACTION_ID,
1526 lv_OPERATION_SEQ_NUM ,
1527 lv_COMPONENT_ITEM_ID,
1528 lv_PRIMARY_COMPONENT_ID,
1529 lv_SOURCE_PHANTOM_ID,
1530 lv_COMPONENT_SEQUENCE_ID,
1531 lv_RECOMMENDED,
1532 lv_RECO_DATE_REQUIRED,
1533 lv_ORGANIZATION_ID,
1534 lv_COMPONENT_PRIORITY,
1535 lv_DEPARTMENT_ID,
1536 lv_QUANTITY_PER_ASSEMBLY,
1537 lv_COMPONENT_YIELD_FACTOR,
1538 lv_EFFECTIVITY_DATE,
1539 lv_DISABLE_DATE,
1540 lv_PLANNING_FACTOR,
1541 lv_LOW_QUANTITY,
1542 lv_HIGH_QUANTITY,
1543 lv_OP_LT_PERCENT,
1544 lv_WIP_SUPPLY_TYPE,
1545 lv_FROM_END_ITEM_UNIT_NUMBER,
1546 lv_TO_END_ITEM_UNIT_NUMBER,
1547 lv_COMPONENT_SCALING_TYPE,
1548 lv_SR_INSTANCE_ID,
1549 MSC_CL_COLLECTION.v_last_collection_id,
1550 MSC_CL_COLLECTION.v_current_date,
1551 MSC_CL_COLLECTION.v_current_user,
1552 MSC_CL_COLLECTION.v_current_date,
1553 MSC_CL_COLLECTION.v_current_user;
1554 total_count := total_count +1;
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559
1560 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1561
1562 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1563 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1564 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1565 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1566 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1567
1568 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1569 RAISE;
1570
1571 ELSE
1572
1573 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1574
1575 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1576 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1577 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_REQ_OP');
1578 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_REQUIREMENT_OPS');
1579 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1580
1581 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1582 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1583 FND_MESSAGE.SET_TOKEN('VALUE',
1584 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1585 MSC_CL_COLLECTION.v_instance_id));
1586 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1587
1588 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1589 END IF;
1590
1591 END;
1592
1593 END LOOP;
1594
1595 -- log_debug('Total MSC_JOB_REQUIREMENT_OPS = '|| total_count);
1596 END IF;
1597
1598 CLOSE cgen;
1599
1600 COMMIT;
1601
1602 BEGIN
1603
1604 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
1605
1606 lv_tbl:= 'JOB_REQUIREMENT_OPS_'||MSC_CL_COLLECTION.v_instance_code;
1607
1608 lv_sql_stmt:=
1609 'INSERT INTO '||lv_tbl
1610 ||' SELECT * from MSC_JOB_REQUIREMENT_OPS'
1611 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
1612 ||' AND plan_id = -1 '
1613 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
1614
1615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
1616 EXECUTE IMMEDIATE lv_sql_stmt;
1617
1618 COMMIT;
1619
1620 END IF;
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624
1625 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1626 RAISE;
1627 END;
1628
1629 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1630 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
1631 lv_retcode,
1632 'MSC_JOB_REQUIREMENT_OPS',
1633 MSC_CL_COLLECTION.v_instance_code,
1634 MSC_UTIL.G_WARNING
1635 );
1636
1637 IF lv_retcode = MSC_UTIL.G_ERROR THEN
1638 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
1639 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
1640 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
1641 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1642 END IF;
1643
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 IF cgen%ISOPEN THEN CLOSE cgen; END IF;
1649
1650 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_REQ_OP>>');
1651 IF lv_cursor_stmt IS NOT NULL THEN
1652 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
1653 END IF;
1654 IF lv_sql_stmt IS NOT NULL THEN
1655 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
1656 END IF;
1657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1658 RAISE;
1659 END LOAD_JOB_REQ_OP;
1660 --=================================================================
1661
1662 PROCEDURE LOAD_JOB_OP_NWK IS
1663
1664 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
1665
1666 cgen CurTyp;
1667
1668 lv_tbl VARCHAR2(30);
1669 lv_supplies_tbl VARCHAR2(30);
1670
1671 lv_cursor_stmt VARCHAR2(5000);
1672 lv_eam_pc_stmt VARCHAR2(5000);
1673 lv_sql_stmt VARCHAR2(5000);
1674
1675 lv_TRANSACTION_ID NUMBER;
1676 lv_SR_INSTANCE_ID NUMBER;
1677 lv_FROM_OP_SEQ_NUM NUMBER;
1678 lv_TO_OP_SEQ_NUM NUMBER;
1679 lv_FROM_OP_SEQ_ID NUMBER;
1680 lv_TO_OP_SEQ_ID NUMBER;
1681 lv_RECOMMENDED VARCHAR2(1);
1682 lv_TRANSITION_TYPE NUMBER;
1683 lv_PLANNING_PCT NUMBER;
1684 lv_ORGANIZATION_ID NUMBER;
1685 lv_TO_TRANSACTION_ID NUMBER; /* ds change change start */
1686 lv_TOP_TRANSACTION_ID NUMBER;
1687 lv_TRANSFER_QTY NUMBER;
1688 lv_TRANSFER_PCT NUMBER;
1689 lv_FROM_ITEM_ID NUMBER;
1690 lv_APPLY_TO_CHARGES NUMBER;
1691 lv_MINIMUM_TRANSFER_QTY NUMBER;
1692 lv_MINIMUM_TIME_OFFSET NUMBER;
1693 lv_MAXIMUM_TIME_OFFSET NUMBER;
1694 lv_DEPENDENCY_TYPE NUMBER;
1695 lv_TRANSFER_UOM VARCHAR2(4); /* ds change change start */
1696 total_count NUMBER := 0;
1697
1698 lv_errbuf VARCHAR2(240);
1699 lv_retcode NUMBER;
1700
1701 BEGIN
1702
1703 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1704 lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
1705 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1706 ELSE
1707 lv_tbl:= 'MSC_JOB_OPERATION_NETWORKS';
1708 lv_supplies_tbl:= 'MSC_SUPPLIES';
1709 END IF;
1710
1711 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1712
1713 /* for eam work dependency */
1714 lv_cursor_stmt:=
1715 'SELECT'
1716 ||' ms.TRANSACTION_ID,'
1717 ||' nwk.FROM_OP_SEQ_NUM,'
1718 ||' nwk.TO_OP_SEQ_NUM,'
1719 ||' nwk.SR_INSTANCE_ID'
1720 ||' FROM '||lv_supplies_tbl||' ms,'
1721 ||' MSC_ST_JOB_OPERATION_NETWORKS nwk'
1722 ||' WHERE nwk.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1723 ||' AND ms.PLAN_ID= -1'
1724 ||' AND ms.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1725 ||' AND ms.DISPOSITION_ID= nwk.WIP_ENTITY_ID'
1726 ||' AND ms.ORDER_TYPE IN ( 3, 7, 70)' /* ds change change: 70 eam supply */
1727 ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_YES
1728 ||' AND nvl(nwk.DEPENDENCY_TYPE,4) <> 3 '; /* ds change change */
1729
1730 /* for eam parent child dependencies */
1731 lv_eam_pc_stmt:=
1732 'SELECT'
1733 ||' ms_from.TRANSACTION_ID,'
1734 ||' ms_to.TRANSACTION_ID,'
1735 ||' nwk.SR_INSTANCE_ID'
1736 ||' FROM '||lv_supplies_tbl||' ms_from,'
1737 || lv_supplies_tbl||' ms_to,'
1738 ||' MSC_ST_JOB_OPERATION_NETWORKS nwk'
1739 ||' WHERE nwk.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1740 ||' AND ms_from.PLAN_ID= -1'
1741 ||' AND ms_from.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1742 ||' AND ms_from.DISPOSITION_ID= nwk.WIP_ENTITY_ID'
1743 ||' AND ms_from.ORDER_TYPE = 70 '
1744 ||' AND ms_to.PLAN_ID= -1'
1745 ||' AND ms_to.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1746 ||' AND ms_to.DISPOSITION_ID= nwk.TO_WIP_ENTITY_ID'
1747 ||' AND ms_to.ORDER_TYPE = 70 '
1748 ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_YES;
1749 --||' AND nwk.DEPENDENCY_TYPE = 3 ;
1750
1751 OPEN cgen FOR lv_cursor_stmt;
1752
1753 IF (cgen%ISOPEN) THEN
1754
1755 LOOP
1756
1757 FETCH cgen INTO
1758 lv_TRANSACTION_ID,
1759 lv_FROM_OP_SEQ_NUM,
1760 lv_TO_OP_SEQ_NUM,
1761 lv_SR_INSTANCE_ID;
1762
1763
1764 EXIT WHEN cgen%NOTFOUND;
1765
1766 DELETE MSC_JOB_OPERATION_NETWORKS
1767 WHERE PLAN_ID= -1
1768 AND TRANSACTION_ID = lv_TRANSACTION_ID
1769 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
1770 AND FROM_OP_SEQ_NUM = nvl(lv_FROM_OP_SEQ_NUM,FROM_OP_SEQ_NUM)
1771 AND TO_OP_SEQ_NUM = nvl(lv_TO_OP_SEQ_NUM,TO_OP_SEQ_NUM);
1772
1773 END LOOP;
1774
1775 END IF; /* cgen%ISOPEN) */
1776
1777 COMMIT;
1778
1779 CLOSE cgen;
1780
1781 /* ds change change start */
1782 OPEN cgen FOR lv_eam_pc_stmt;
1783 IF (cgen%ISOPEN) THEN
1784 LOOP
1785 FETCH cgen INTO
1786 lv_TRANSACTION_ID,
1787 lv_TO_TRANSACTION_ID,
1788 lv_SR_INSTANCE_ID;
1789
1790
1791 EXIT WHEN cgen%NOTFOUND;
1792
1793 DELETE MSC_JOB_OPERATION_NETWORKS
1794 WHERE PLAN_ID= -1
1795 AND TRANSACTION_ID = lv_TRANSACTION_ID
1796 AND SR_INSTANCE_ID = lv_SR_INSTANCE_ID
1797 AND TO_TRANSACTION_ID = lv_TO_TRANSACTION_ID ;
1798 --AND DEPENDENCY_TYPE = 3;
1799
1800 END LOOP;
1801
1802 END IF; /* cgen%ISOPEN) */
1803
1804 COMMIT;
1805
1806 CLOSE cgen;
1807 /* ds change change end */
1808
1809
1810 END IF; /*Incremental*/
1811
1812 lv_cursor_stmt:=
1813 'SELECT'
1814 ||' ms.TRANSACTION_ID,'
1815 ||' nwk.FROM_OP_SEQ_NUM,'
1816 ||' nwk.TO_OP_SEQ_NUM,'
1817 ||' nwk.SR_INSTANCE_ID,'
1818 ||' nwk.ORGANIZATION_ID,'
1819 ||' nwk.FROM_OP_SEQ_ID,'
1820 ||' nwk.TO_OP_SEQ_ID,'
1821 ||' nwk.RECOMMENDED,'
1822 ||' nwk.TRANSITION_TYPE,'
1823 ||' nwk.PLANNING_PCT,'
1824 ||' ms1.TRANSACTION_ID,' /* ds change change start */
1825 ||' ms2.TRANSACTION_ID,'
1826 ||' nwk.TRANSFER_QTY,'
1827 ||' nwk.TRANSFER_UOM,'
1828 ||' nwk.TRANSFER_PCT,'
1829 ||' t1.INVENTORY_ITEM_ID,' /*FROM_ITEM_ID,*/
1830 ||' nwk.APPLY_TO_CHARGES,'
1831 ||' nwk.MINIMUM_TRANSFER_QTY,'
1832 ||' nwk.MINIMUM_TIME_OFFSET,'
1833 ||' nwk.MAXIMUM_TIME_OFFSET,'
1834 ||' nwk.DEPENDENCY_TYPE' /* ds change change end */
1835 ||' FROM '||lv_supplies_tbl||' ms,'
1836 ||' '||lv_supplies_tbl||' ms1,'
1837 ||' '||lv_supplies_tbl||' ms2,'
1838 ||' MSC_ST_JOB_OPERATION_NETWORKS nwk,'
1839 ||' MSC_ITEM_ID_LID t1'
1840 ||' WHERE nwk.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1841 ||' AND ms.PLAN_ID= -1'
1842 ||' AND ms.SR_INSTANCE_ID= nwk.SR_INSTANCE_ID'
1843 ||' AND ms.DISPOSITION_ID= nwk.WIP_ENTITY_ID'
1844 ||' AND ms1.PLAN_ID (+) = -1' /* ds change change */
1845 ||' AND ms1.SR_INSTANCE_ID (+)= nwk.SR_INSTANCE_ID'
1846 ||' AND ms1.DISPOSITION_ID(+)= nwk.TO_WIP_ENTITY_ID'
1847 ||' AND ms.ORDER_TYPE IN ( 3, 7, 70)'
1848 ||' AND ms1.ORDER_TYPE(+) = 70 '
1849 ||' AND ms2.PLAN_ID (+) = -1' /* ds change change */
1850 ||' AND ms2.SR_INSTANCE_ID (+)= nwk.SR_INSTANCE_ID'
1851 ||' AND ms2.DISPOSITION_ID(+)= nwk.TOP_WIP_ENTITY_ID'
1852 ||' AND ms2.ORDER_TYPE(+) = 70'
1853 ||' AND nwk.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1854 ||' AND nwk.FROM_ITEM_ID = t1.SR_INVENTORY_ITEM_ID(+)' /* ds change change */
1855 ||' AND nwk.sr_instance_id = t1.sr_instance_id(+) '; /* ds change change */
1856
1857
1858 -- ========= Prepare SQL Statement for INSERT ==========
1859 lv_sql_stmt:=
1860 'insert into '||lv_tbl
1861 ||' ( PLAN_ID,'
1862 ||' TRANSACTION_ID,'
1863 ||' FROM_OP_SEQ_NUM,'
1864 ||' TO_OP_SEQ_NUM,'
1865 ||' ORGANIZATION_ID,'
1866 ||' FROM_OP_SEQ_ID,'
1867 ||' TO_OP_SEQ_ID,'
1868 ||' RECOMMENDED,'
1869 ||' TRANSITION_TYPE,'
1870 ||' PLANNING_PCT,'
1871 ||' TO_TRANSACTION_ID,' /* ds change change start */
1872 ||' TOP_TRANSACTION_ID,'
1873 ||' TRANSFER_QTY,'
1874 ||' TRANSFER_UOM,'
1875 ||' TRANSFER_PCT,'
1876 ||' FROM_ITEM_ID,'
1877 ||' APPLY_TO_CHARGES,'
1878 ||' MINIMUM_TRANSFER_QTY,'
1879 ||' MINIMUM_TIME_OFFSET,'
1880 ||' MAXIMUM_TIME_OFFSET,'
1881 ||' DEPENDENCY_TYPE,' /* ds change change end */
1882 ||' SR_INSTANCE_ID,'
1883 ||' REFRESH_NUMBER,'
1884 ||' LAST_UPDATE_DATE,'
1885 ||' LAST_UPDATED_BY,'
1886 ||' CREATION_DATE,'
1887 ||' CREATED_BY)'
1888 ||'VALUES'
1889 ||'( -1,'
1890 ||' :TRANSACTION_ID,'
1891 ||' :FROM_OP_SEQ_NUM,'
1892 ||' :TO_OP_SEQ_NUM,'
1893 ||' :ORGANIZATION_ID,'
1894 ||' :FROM_OP_SEQ_ID,'
1895 ||' :TO_OP_SEQ_ID,'
1896 ||' :RECOMMENDED,'
1897 ||' :TRANSITION_TYPE,'
1898 ||' :PLANNING_PCT,'
1899 ||' :TO_TRANSACTION_ID,' /* ds change change start */
1900 ||' :TOP_TRANSACTION_ID,'
1901 ||' :TRANSFER_QTY,'
1902 ||' :TRANSFER_UOM,'
1903 ||' :TRANSFER_PCT,'
1904 ||' :FROM_ITEM_ID,'
1905 ||' :APPLY_TO_CHARGES,'
1906 ||' :MINIMUM_TRANSFER_QTY,'
1907 ||' :MINIMUM_TIME_OFFSET,'
1908 ||' :MAXIMUM_TIME_OFFSET,'
1909 ||' :DEPENDENCY_TYPE,' /* ds change change end */
1910 ||' :SR_INSTANCE_ID,'
1911 ||' :REFRESH_NUMBER,'
1912 ||' :v_current_date,'
1913 ||' :v_current_user,'
1914 ||' :v_current_date,'
1915 ||' :v_current_user)';
1916
1917
1918 --log_debug(lv_sql_stmt);
1919 OPEN cgen FOR lv_cursor_stmt;
1920
1921 IF (cgen%ISOPEN) THEN
1922 total_count := 0;
1923 LOOP
1924
1925 FETCH cgen INTO
1926 lv_TRANSACTION_ID,
1927 lv_FROM_OP_SEQ_NUM,
1928 lv_TO_OP_SEQ_NUM,
1929 lv_SR_INSTANCE_ID,
1930 lv_ORGANIZATION_ID,
1931 lv_FROM_OP_SEQ_ID,
1932 lv_TO_OP_SEQ_ID,
1933 lv_RECOMMENDED,
1934 lv_TRANSITION_TYPE,
1935 lv_PLANNING_PCT,
1936 lv_TO_TRANSACTION_ID, /* ds change change start */
1937 lv_TOP_TRANSACTION_ID,
1938 lv_TRANSFER_QTY,
1939 lv_TRANSFER_UOM,
1940 lv_TRANSFER_PCT,
1941 lv_FROM_ITEM_ID,
1942 lv_APPLY_TO_CHARGES,
1943 lv_MINIMUM_TRANSFER_QTY,
1944 lv_MINIMUM_TIME_OFFSET,
1945 lv_MAXIMUM_TIME_OFFSET,
1946 lv_DEPENDENCY_TYPE; /* ds change change end */
1947
1948 if lv_TO_TRANSACTION_ID is not null then
1949 link_top_transaction_id_req := TRUE;
1950 end if;
1951
1952 EXIT WHEN cgen%NOTFOUND;
1953
1954 BEGIN
1955
1956 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1957 /* opm is full collection. eam when there is change in
1958 relationship, it is deleter then insert */
1959
1960 UPDATE MSC_JOB_OPERATION_NETWORKS
1961 SET
1962 FROM_OP_SEQ_ID= lv_FROM_OP_SEQ_ID,
1963 TO_OP_SEQ_ID= lv_TO_OP_SEQ_ID,
1964 RECOMMENDED= lv_RECOMMENDED,
1965 TRANSITION_TYPE= lv_TRANSITION_TYPE,
1966 PLANNING_PCT= lv_PLANNING_PCT,
1967 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1968 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1969 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1970 WHERE PLAN_ID= -1
1971 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
1972 AND TRANSACTION_ID= lv_TRANSACTION_ID
1973 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
1974 AND FROM_OP_SEQ_NUM = lv_FROM_OP_SEQ_NUM
1975 AND TO_OP_SEQ_NUM = lv_TO_OP_SEQ_NUM;
1976 END IF;
1977
1978 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
1979 EXECUTE IMMEDIATE lv_sql_stmt
1980 USING
1981 lv_TRANSACTION_ID,
1982 lv_FROM_OP_SEQ_NUM,
1983 lv_TO_OP_SEQ_NUM,
1984 lv_ORGANIZATION_ID,
1985 lv_FROM_OP_SEQ_ID,
1986 lv_TO_OP_SEQ_ID,
1987 lv_RECOMMENDED,
1988 lv_TRANSITION_TYPE,
1989 lv_PLANNING_PCT,
1990 lv_TO_TRANSACTION_ID, /* ds change change start */
1991 lv_TOP_TRANSACTION_ID,
1992 lv_TRANSFER_QTY,
1993 lv_TRANSFER_UOM,
1994 lv_TRANSFER_PCT,
1995 lv_FROM_ITEM_ID,
1996 lv_APPLY_TO_CHARGES,
1997 lv_MINIMUM_TRANSFER_QTY,
1998 lv_MINIMUM_TIME_OFFSET,
1999 lv_MAXIMUM_TIME_OFFSET,
2000 lv_DEPENDENCY_TYPE, /* ds change change end */
2001 lv_SR_INSTANCE_ID,
2002 MSC_CL_COLLECTION.v_last_collection_id,
2003 MSC_CL_COLLECTION.v_current_date,
2004 MSC_CL_COLLECTION.v_current_user,
2005 MSC_CL_COLLECTION.v_current_date,
2006 MSC_CL_COLLECTION.v_current_user;
2007 total_count := total_count + 1;
2008 END IF;
2009
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012
2013 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2014
2015 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2016 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2017 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2018 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2019 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2020
2021 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2022 RAISE;
2023
2024 ELSE
2025
2026 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2027
2028 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2029 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2030 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_JOB_OP_NWK');
2031 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_JOB_OPERATION_NETWORKS');
2032 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2033
2034 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2035 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2036 FND_MESSAGE.SET_TOKEN('VALUE',
2037 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
2038 MSC_CL_COLLECTION.v_instance_id));
2039 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2040
2041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2042 END IF;
2043
2044 END;
2045
2046 END LOOP;
2047 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Total MSC_JOB_OPERATION_NETWORKS = '|| total_count);
2048 END IF;
2049
2050 CLOSE cgen;
2051
2052 COMMIT;
2053
2054 BEGIN
2055 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
2056
2057 lv_tbl:= 'JOB_OPERATION_NETWORKS_'||MSC_CL_COLLECTION.v_instance_code;
2058
2059 lv_sql_stmt:=
2060 'INSERT INTO '||lv_tbl
2061 ||' SELECT * from MSC_JOB_OPERATION_NETWORKS'
2062 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2063 ||' AND plan_id = -1 '
2064 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2065
2066 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2067 EXECUTE IMMEDIATE lv_sql_stmt;
2068
2069 COMMIT;
2070
2071 END IF;
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075
2076 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2077 RAISE;
2078 END;
2079
2080 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2081 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
2082 lv_retcode,
2083 'MSC_JOB_OPERATION_NETWORKS',
2084 MSC_CL_COLLECTION.v_instance_code,
2085 MSC_UTIL.G_WARNING
2086 );
2087
2088 IF lv_retcode = MSC_UTIL.G_ERROR THEN
2089 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
2090 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
2091 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
2092 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2093 END IF;
2094
2095 END IF;
2096
2097 EXCEPTION
2098 WHEN OTHERS THEN
2099 IF cgen%ISOPEN THEN CLOSE cgen; END IF;
2100
2101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_JOB_OP_NWK>>');
2102 IF lv_cursor_stmt IS NOT NULL THEN
2103 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
2104 END IF;
2105 IF lv_sql_stmt IS NOT NULL THEN
2106 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
2107 END IF;
2108 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2109 RAISE;
2110 END LOAD_JOB_OP_NWK;
2111
2112 --=========================================================================
2113 -- =================== LOAD_RES_INST_REQ ===============
2114 PROCEDURE LOAD_RES_INST_REQ IS
2115 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
2116 res_inst_req CurTyp;
2117
2118 c_count NUMBER:=0;
2119 total_count NUMBER:=0;
2120 lv_tbl VARCHAR2(30);
2121 lv_supplies_tbl VARCHAR2(30);
2122 lv_sql_stmt VARCHAR2(5000);
2123 lv_cursor_stmt VARCHAR2(5000);
2124
2125 CURSOR res_inst_req_d IS
2126 SELECT
2127 msrir.WIP_ENTITY_ID,
2128 msrir.OPERATION_SEQ_NUM,
2129 msrir.ORIG_RESOURCE_SEQ_NUM,
2130 msrir.SR_INSTANCE_ID,
2131 msrir.SERIAL_NUMBER,
2132 msrir.RES_INSTANCE_ID
2133 FROM MSC_ST_RESOURCE_INSTANCE_REQS msrir
2134 WHERE msrir.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2135 AND msrir.DELETED_FLAG= MSC_UTIL.SYS_YES;
2136
2137 lv_DEPARTMENT_ID NUMBER;
2138 lv_RESOURCE_ID NUMBER;
2139 lv_ORGANIZATION_ID NUMBER;
2140 lv_SUPPLY_ID NUMBER;
2141 lv_WIP_ENTITY_ID NUMBER;
2142 lv_OPERATION_SEQ_NUM NUMBER;
2143 --lv_OPERATION_SEQUENCE_ID NUMBER;
2144 lv_RESOURCE_SEQ_NUM NUMBER;
2145 lv_START_DATE DATE;
2146 lv_END_DATE DATE;
2147 lv_OPERATION_HOURS_REQUIRED NUMBER;
2148 lv_DELETED_FLAG NUMBER;
2149 lv_SR_INSTANCE_ID NUMBER;
2150 lv_SETUP_ID NUMBER;
2151 lv_RES_INSTANCE_ID NUMBER;
2152 lv_EQUIPMENT_ITEM_ID NUMBER;
2153 lv_SERIAL_NUMBER VARCHAR2(30);
2154 lv_ORIG_RESOURCE_SEQ_NUM NUMBER;
2155 lv_BATCH_NUMBER NUMBER;
2156
2157 lv_errbuf VARCHAR2(240);
2158 lv_retcode NUMBER;
2159 lv_sql_ins VARCHAR2(6000);
2160 lb_refresh_failed BOOLEAN:= FALSE;
2161
2162 BEGIN
2163
2164
2165 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2166 COMMIT;
2167 END IF;
2168
2169 c_count:= 0;
2170 total_count := 0;
2171 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2172 lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2173 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2174 ELSE
2175 lv_tbl:= 'MSC_RESOURCE_INSTANCE_REQS';
2176 lv_supplies_tbl:= 'MSC_SUPPLIES';
2177 END IF;
2178
2179 lv_cursor_stmt:=
2180 'SELECT'
2181 ||' NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
2182 ||' msrir.WIP_ENTITY_ID,'
2183 ||' msrir.ORGANIZATION_ID,'
2184 ||' msrir.DEPARTMENT_ID,'
2185 ||' msrir.OPERATION_SEQ_NUM,'
2186 --||' msrir.OPERATION_SEQUENCE_ID,'
2187 ||' msrir.RESOURCE_SEQ_NUM,'
2188 ||' msrir.START_DATE,'
2189 ||' msrir.END_DATE,'
2190 ||' msrir.RESOURCE_INSTANCE_HOURS,'
2191 ||' msrir.DELETED_FLAG,'
2192 ||' msrir.SR_INSTANCE_ID,'
2193 ||' msrir.ORIG_RESOURCE_SEQ_NUM, '
2194 ||' msrir.BATCH_NUMBER, '
2195 ||' msrir.RES_INSTANCE_ID, '
2196 ||' msrir.RESOURCE_ID, '
2197 ||' msrir.SERIAL_NUMBER, '
2198 ||' t1.INVENTORY_ITEM_ID equipment_item_id'
2199 ||' FROM '||lv_supplies_tbl||' ms,'
2200 ||' MSC_ST_RESOURCE_INSTANCE_REQS msrir,'
2201 ||' MSC_ITEM_ID_LID t1'
2202 ||' WHERE msrir.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2203 ||' AND ms.PLAN_ID= -1'
2204 ||' AND ms.SR_INSTANCE_ID= msrir.SR_INSTANCE_ID'
2205 ||' AND ms.DISPOSITION_ID= msrir.WIP_ENTITY_ID'
2206 ||' AND ms.ORDER_TYPE IN ( 3, 7,70)'
2207 ||' AND msrir.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2208 ||' AND t1.sr_inventory_item_id (+) = msrir.equipment_item_id '
2209 ||' AND t1.SR_INSTANCE_ID (+) = msrir.SR_INSTANCE_ID ';
2210
2211
2212 -- ========= Prepare SQL Statement for INSERT ==========
2213 lv_sql_stmt:=
2214 'insert into '||lv_tbl
2215 ||' ( PLAN_ID,'
2216 ||' RES_INST_TRANSACTION_ID,'
2217 ||' SR_INSTANCE_ID,'
2218 ||' ORGANIZATION_ID,'
2219 ||' DEPARTMENT_ID,'
2220 ||' SUPPLY_ID,'
2221 ||' WIP_ENTITY_ID,'
2222 ||' OPERATION_SEQ_NUM,'
2223 ||' RESOURCE_SEQ_NUM,'
2224 ||' ORIG_RESOURCE_SEQ_NUM,'
2225 ||' RESOURCE_ID,'
2226 ||' RES_INSTANCE_ID,'
2227 ||' SERIAL_NUMBER,'
2228 ||' EQUIPMENT_ITEM_ID,'
2229 ||' START_DATE,'
2230 ||' END_DATE,'
2231 ||' RESOURCE_INSTANCE_HOURS,'
2232 ||' REFRESH_NUMBER,'
2233 ||' BATCH_NUMBER, '
2234 ||' LAST_UPDATE_DATE,'
2235 ||' LAST_UPDATED_BY,'
2236 ||' CREATION_DATE,'
2237 ||' CREATED_BY)'
2238 ||'VALUES'
2239 ||'( -1,'
2240 ||' MSC_RESOURCE_INSTANCE_REQS_S.NEXTVAL,'
2241 ||' :SR_INSTANCE_ID,'
2242 ||' :ORGANIZATION_ID,'
2243 ||' :DEPARTMENT_ID,'
2244 ||' :SUPPLY_ID,'
2245 ||' :WIP_ENTITY_ID,'
2246 ||' :OPERATION_SEQ_NUM,'
2247 ||' :RESOURCE_SEQ_NUM,'
2248 ||' :ORIG_RESOURCE_SEQ_NUM,'
2249 ||' :RESOURCE_ID,'
2250 ||' :RES_INSTANCE_ID,'
2251 ||' :SERIAL_NUMBER,'
2252 ||' :EQUIPMENT_ITEM_ID,'
2253 ||' :START_DATE,'
2254 ||' :END_DATE,'
2255 ||' :OPERATION_HOURS_REQUIRED,'
2256 ||' :v_last_collection_id,'
2257 ||' :BATCH_NUMBER, '
2258 ||' :v_current_date,'
2259 ||' :v_current_user,'
2260 ||' :v_current_date,'
2261 ||' :v_current_user)';
2262
2263 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2264 BEGIN
2265 lv_sql_ins:=
2266 'insert into '||lv_tbl
2267 ||' ( PLAN_ID,'
2268 ||' RES_INST_TRANSACTION_ID,'
2269 ||' SR_INSTANCE_ID,'
2270 ||' ORGANIZATION_ID,'
2271 ||' DEPARTMENT_ID,'
2272 ||' SUPPLY_ID,'
2273 ||' WIP_ENTITY_ID,'
2274 ||' OPERATION_SEQ_NUM,'
2275 ||' RESOURCE_SEQ_NUM,'
2276 ||' ORIG_RESOURCE_SEQ_NUM,'
2277 ||' RESOURCE_ID,'
2278 ||' RES_INSTANCE_ID,'
2279 ||' SERIAL_NUMBER,'
2280 ||' EQUIPMENT_ITEM_ID,'
2281 ||' START_DATE,'
2282 ||' END_DATE,'
2283 ||' RESOURCE_INSTANCE_HOURS,'
2284 ||' REFRESH_NUMBER,'
2285 ||' BATCH_NUMBER, '
2286 ||' LAST_UPDATE_DATE,'
2287 ||' LAST_UPDATED_BY,'
2288 ||' CREATION_DATE,'
2289 ||' CREATED_BY)'
2290 ||' SELECT '
2291 ||' -1,'
2292 ||' MSC_RESOURCE_INSTANCE_REQS_S.NEXTVAL,'
2293 ||' msrir.SR_INSTANCE_ID,'
2294 ||' msrir.ORGANIZATION_ID,'
2295 ||' msrir.DEPARTMENT_ID,'
2296 ||' NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
2297 ||' msrir.WIP_ENTITY_ID,'
2298 ||' msrir.OPERATION_SEQ_NUM,'
2299 ||' msrir.RESOURCE_SEQ_NUM,'
2300 ||' msrir.ORIG_RESOURCE_SEQ_NUM,'
2301 ||' msrir.RESOURCE_ID,'
2302 ||' msrir.RES_INSTANCE_ID,'
2303 ||' msrir.SERIAL_NUMBER,'
2304 ||' t1.INVENTORY_ITEM_ID,'
2305 ||' msrir.START_DATE,'
2306 ||' msrir.END_DATE,'
2307 ||' msrir.RESOURCE_INSTANCE_HOURS,'
2308 ||' :v_last_collection_id, '
2309 ||' msrir.BATCH_NUMBER, '
2310 ||' :v_current_date, '
2311 ||' :v_current_user, '
2312 ||' :v_current_date, '
2313 ||' :v_current_user '
2314 ||' FROM '||lv_supplies_tbl||' ms, '
2315 ||' MSC_ST_RESOURCE_INSTANCE_REQS msrir, '
2316 ||' MSC_ITEM_ID_LID t1 '
2317 ||' WHERE msrir.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2318 ||' AND ms.PLAN_ID= -1 '
2319 ||' AND ms.SR_INSTANCE_ID= msrir.SR_INSTANCE_ID '
2320 ||' AND ms.DISPOSITION_ID= msrir.WIP_ENTITY_ID '
2321 ||' AND ms.ORDER_TYPE IN ( 3, 7,70) '
2322 ||' AND msrir.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2323 ||' AND t1.sr_inventory_item_id (+) = msrir.equipment_item_id '
2324 ||' AND t1.SR_INSTANCE_ID (+) = msrir.SR_INSTANCE_ID ';
2325
2326 EXECUTE IMMEDIATE lv_sql_ins
2327 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;
2328
2329 COMMIT;
2330 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'res instance req loaded');
2331
2332 EXCEPTION
2333 WHEN OTHERS THEN
2334 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
2335
2336 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2337 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2338 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2339 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2340 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2341
2342 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2343 RAISE;
2344
2345 ELSE
2346 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2347 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2348 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2349 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2350 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2351
2352 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2353
2354 --If Direct path load results in warning then the processing has to be
2355 --switched back to row by row processing. This will help to identify the
2356 --erroneous record and will also help in processing the rest of the records.
2357 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - res instance req');
2358 lb_refresh_failed := TRUE;
2359 END IF;
2360 END;
2361
2362 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
2363
2364 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
2365
2366 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2367
2368 FOR c_rec IN res_inst_req_d LOOP
2369 DELETE MSC_RESOURCE_INSTANCE_REQS
2370 WHERE PLAN_ID = -1
2371 AND SR_INSTANCE_ID = c_rec.SR_INSTANCE_ID
2372 AND WIP_ENTITY_ID = c_rec.WIP_ENTITY_ID
2373 AND OPERATION_SEQ_NUM = NVL( c_rec.OPERATION_SEQ_NUM, OPERATION_SEQ_NUM)
2374 AND ORIG_RESOURCE_SEQ_NUM = NVL(c_rec.ORIG_RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM)
2375 AND RES_INSTANCE_ID = nvl( c_rec.RES_INSTANCE_ID,RES_INSTANCE_ID)
2376 AND SERIAL_NUMBER = nvl(c_rec.SERIAL_NUMBER,SERIAL_NUMBER);
2377 END LOOP;
2378
2379 END IF; /* MSC_CL_COLLECTION.v_is_incremental_refresh */
2380
2381 --log_debug('insert lv_sql_stmt:='||lv_sql_stmt);
2382 OPEN res_inst_req FOR lv_cursor_stmt;
2383
2384 LOOP
2385
2386 FETCH res_inst_req INTO
2387 lv_SUPPLY_ID,
2388 lv_WIP_ENTITY_ID,
2389 lv_ORGANIZATION_ID,
2390 lv_DEPARTMENT_ID,
2391 lv_OPERATION_SEQ_NUM,
2392 --lv_OPERATION_SEQUENCE_ID,
2393 lv_RESOURCE_SEQ_NUM,
2394 lv_START_DATE,
2395 lv_END_DATE,
2396 lv_OPERATION_HOURS_REQUIRED,
2397 lv_DELETED_FLAG,
2398 lv_SR_INSTANCE_ID,
2399 lv_ORIG_RESOURCE_SEQ_NUM,
2400 lv_BATCH_NUMBER,
2401 lv_RES_INSTANCE_ID,
2402 lv_RESOURCE_ID,
2403 lv_SERIAL_NUMBER,
2404 lv_EQUIPMENT_ITEM_ID;
2405
2406 -- log_debug('Res Ins Req: WIP_ENTITY_ID ='||to_char(lv_WIP_ENTITY_ID)||
2407 -- 'res inst id = '||to_char(lv_RES_INSTANCE_ID));
2408 EXIT WHEN res_inst_req%NOTFOUND;
2409
2410
2411 BEGIN
2412
2413 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2414 /* we can get rid of thsi update and just insert as we are puting
2415 record in ad table when instance is updated */
2416 UPDATE MSC_RESOURCE_INSTANCE_REQS mrir
2417 SET
2418 START_DATE = lv_START_DATE,
2419 RESOURCE_INSTANCE_HOURS= lv_OPERATION_HOURS_REQUIRED ,
2420 END_DATE = lv_END_DATE,
2421 SUPPLY_ID = lv_SUPPLY_ID,
2422 REFRESH_NUMBER = MSC_CL_COLLECTION.v_last_collection_id,
2423 RESOURCE_SEQ_NUM = lv_RESOURCE_SEQ_NUM,
2424 BATCH_NUMBER = lv_BATCH_NUMBER,
2425 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2426 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2427 WHERE PLAN_ID= -1
2428 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
2429 AND NVL(ORIG_RESOURCE_SEQ_NUM, RESOURCE_SEQ_NUM) =
2430 NVL(lv_ORIG_RESOURCE_SEQ_NUM, lv_RESOURCE_SEQ_NUM)
2431 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
2432 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
2433 AND OPERATION_SEQ_NUM= lv_OPERATION_SEQ_NUM
2434 AND RES_INSTANCE_ID = lv_RES_INSTANCE_ID
2435 AND SERIAL_NUMBER = lv_SERIAL_NUMBER;
2436 END IF;
2437
2438 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2439
2440 EXECUTE IMMEDIATE lv_sql_stmt
2441 USING
2442 lv_SR_INSTANCE_ID,
2443 lv_ORGANIZATION_ID,
2444 lv_DEPARTMENT_ID,
2445 lv_SUPPLY_ID,
2446 lv_WIP_ENTITY_ID,
2447 lv_OPERATION_SEQ_NUM,
2448 lv_RESOURCE_SEQ_NUM,
2449 lv_ORIG_RESOURCE_SEQ_NUM,
2450 lv_RESOURCE_ID,
2451 lv_RES_INSTANCE_ID,
2452 lv_SERIAL_NUMBER,
2453 lv_EQUIPMENT_ITEM_ID,
2454 lv_START_DATE,
2455 lv_END_DATE,
2456 lv_OPERATION_HOURS_REQUIRED,
2457 MSC_CL_COLLECTION.v_last_collection_id,
2458 lv_BATCH_NUMBER,
2459 MSC_CL_COLLECTION.v_current_date,
2460 MSC_CL_COLLECTION.v_current_user,
2461 MSC_CL_COLLECTION.v_current_date,
2462 MSC_CL_COLLECTION.v_current_user;
2463
2464 END IF;
2465 total_count := total_count + 1;
2466 c_count:= c_count+1;
2467
2468 IF c_count> MSC_CL_COLLECTION.PBS THEN
2469 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2470 c_count:= 0;
2471 END IF;
2472
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475
2476 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2477
2478 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2479 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2480 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2481 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2482 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2483
2484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2485 RAISE;
2486
2487 ELSE
2488
2489 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2490
2491 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2492 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2493 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_INST_REQ');
2494 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_INSTANCE_REQS');
2495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2496
2497 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2498
2499 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2500 FND_MESSAGE.SET_TOKEN('VALUE',
2501 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
2502 MSC_CL_COLLECTION.v_instance_id));
2503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2504
2505 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2506 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
2507 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
2508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2509
2510 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2511 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
2512 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
2513 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2514
2515 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2516 FND_MESSAGE.SET_TOKEN('COLUMN', 'RES_INSTANCE_ID');
2517 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RES_INSTANCE_ID));
2518 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2519
2520 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2521 END IF;
2522
2523 END;
2524
2525 END LOOP;
2526
2527 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed
2528
2529 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2530 COMMIT;
2531 END IF;
2532
2533 BEGIN
2534
2535 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
2536
2537 lv_tbl:= 'RESOURCE_INSTANCE_REQS_'||MSC_CL_COLLECTION.v_instance_code;
2538 lv_sql_stmt:=
2539 'INSERT INTO '||lv_tbl
2540 ||' SELECT * from MSC_RESOURCE_INSTANCE_REQS'
2541 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2542 ||' AND plan_id = -1 '
2543 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2544
2545
2546 EXECUTE IMMEDIATE lv_sql_stmt;
2547
2548 COMMIT;
2549
2550 END IF;
2551
2552 EXCEPTION
2553 WHEN OTHERS THEN
2554
2555 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2556 RAISE;
2557 END;
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_RESOURCE_INSTANCE_REQS',
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 IF res_inst_req%ISOPEN THEN CLOSE res_inst_req; END IF;
2579
2580 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_RES_INST_REQ>>');
2581 IF lv_cursor_stmt IS NOT NULL THEN
2582 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
2583 END IF;
2584 IF lv_sql_stmt IS NOT NULL THEN
2585 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
2586 END IF;
2587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2588 RAISE;
2589 END LOAD_RES_INST_REQ;
2590
2591 --===================================================================
2592 -- =================== LOAD WIP DEMAND ===============
2593 PROCEDURE LOAD_WIP_DEMAND IS
2594
2595 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
2596 c2 CurTyp;
2597
2598 c_count NUMBER:=0;
2599 lv_tbl VARCHAR2(30);
2600 lv_supplies_tbl VARCHAR2(30);
2601 lv_sql_stmt VARCHAR2(5000);
2602 lv_cursor_stmt VARCHAR2(5000);
2603 v_srp_profile_vlaue NUMBER;
2604 CURSOR c2_d IS
2605 SELECT msd.WIP_ENTITY_ID,
2606 msd.REPETITIVE_SCHEDULE_ID,
2607 msd.OPERATION_SEQ_NUM,
2608 t1.INVENTORY_ITEM_ID,
2609 msd.ORIGINATION_TYPE,
2610 msd.SR_INSTANCE_ID,
2611 msd.ORGANIZATION_ID
2612 FROM MSC_ITEM_ID_LID t1,
2613 MSC_ST_DEMANDS msd
2614 WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2615 AND msd.ORIGINATION_TYPE IN (2,3,4,25,50) /* ds change change 50 eam demand */
2616 AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
2617 AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
2618 AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
2619
2620 lv_DISPOSITION_ID NUMBER;
2621 lv_INVENTORY_ITEM_ID NUMBER;
2622 lv_ORGANIZATION_ID NUMBER;
2623 lv_USING_ASSEMBLY_ITEM_ID NUMBER;
2624 lv_USING_ASSEMBLY_DEMAND_DATE DATE;
2625 lv_USING_REQUIREMENT_QUANTITY NUMBER;
2626 lv_QUANTITY_PER_ASSEMBLY NUMBER;
2627 lv_QUANTITY_ISSUED NUMBER;
2628 lv_ASSEMBLY_DEMAND_COMP_DATE DATE;
2629 lv_DEMAND_TYPE NUMBER;
2630 lv_ORIGINATION_TYPE NUMBER;
2631 lv_SOURCE_ORGANIZATION_ID NUMBER;
2632 lv_RESERVATION_ID NUMBER;
2633 lv_OPERATION_SEQ_NUM NUMBER;
2634 lv_DEMAND_CLASS VARCHAR2(34);
2635 lv_REPETITIVE_SCHEDULE_ID NUMBER;
2636 lv_ASSET_ITEM_ID NUMBER; /* ds change change */
2637 lv_ASSET_SERIAL_NUMBER VARCHAR2(30); /* ds change change */
2638 lv_SR_INSTANCE_ID NUMBER;
2639 lv_PROJECT_ID NUMBER;
2640 lv_TASK_ID NUMBER;
2641 lv_PLANNING_GROUP VARCHAR2(30);
2642 lv_END_ITEM_UNIT_NUMBER VARCHAR2(30);
2643 lv_COMPONENT_SCALING_TYPE NUMBER; /* Discrete Mfg Enahancements Bug 4492736 */
2644 lv_COMPONENT_YIELD_FACTOR NUMBER; /* Discrete Mfg Enahancements Bug 4479743 */
2645 lv_ORDER_NUMBER VARCHAR2(240);
2646 lv_WIP_ENTITY_ID NUMBER;
2647 lv_WIP_STATUS_CODE NUMBER;
2648 lv_WIP_SUPPLY_TYPE NUMBER;
2649 lv_DELETED_FLAG NUMBER;
2650
2651 BEGIN
2652
2653 --========================= WIP DEMANDS ======================
2654 -- link disposition_id to MSC_supplies.transaction_id
2655
2656 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2657
2658 FOR c_rec IN c2_d LOOP
2659
2660 IF c_rec.ORIGINATION_TYPE IN (2, 3, 50) THEN -- DISCRETE JOB COMPONENT /* ds change 50 eam demand */
2661
2662 UPDATE MSC_DEMANDS
2663 SET USING_REQUIREMENT_QUANTITY= 0,
2664 DAILY_DEMAND_RATE= 0,
2665 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2666 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2667 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2668 WHERE PLAN_ID= -1
2669 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2670 AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
2671 AND WIP_ENTITY_ID= c_rec.WIP_ENTITY_ID
2672 AND OP_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OP_SEQ_NUM)
2673 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID
2674 AND INVENTORY_ITEM_ID= NVL(c_rec.INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);
2675
2676 ELSIF c_rec.ORIGINATION_TYPE= 4 THEN -- REPT ITEM
2677
2678 UPDATE MSC_DEMANDS
2679 SET USING_REQUIREMENT_QUANTITY= 0,
2680 DAILY_DEMAND_RATE= 0,
2681 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2682 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2683 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2684 WHERE PLAN_ID= -1
2685 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2686 AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
2687 AND WIP_ENTITY_ID= NVL(c_rec.WIP_ENTITY_ID,WIP_ENTITY_ID)
2688 AND OP_SEQ_NUM= NVL(c_rec.OPERATION_SEQ_NUM,OP_SEQ_NUM)
2689 AND INVENTORY_ITEM_ID= NVL(c_rec.INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
2690 AND REPETITIVE_SCHEDULE_ID= c_rec.REPETITIVE_SCHEDULE_ID
2691 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
2692
2693 ELSIF c_rec.ORIGINATION_TYPE= 25 THEN -- FLOW SCHEDULE
2694
2695 DELETE MSC_DEMANDS
2696 WHERE PLAN_ID= -1
2697 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2698 AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
2699 AND WIP_ENTITY_ID= c_rec.WIP_ENTITY_ID
2700 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
2701
2702 END IF; -- Origination_Type
2703
2704 END LOOP;
2705
2706 END IF; -- refresh mode
2707
2708 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2709
2710 c_count:=0;
2711
2712 -- ========= Prepare the Cursor Statement ==========
2713 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
2714 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
2715 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2716 ELSE
2717 lv_tbl:= 'MSC_DEMANDS';
2718 lv_supplies_tbl:= 'MSC_SUPPLIES';
2719 END IF;
2720
2721 /** PREPLACE CHANGE START **/
2722
2723 -- For Load_WIP_DEMAND Supplies are also loaded - WIP Parameter
2724 -- simultaneously hence no special logic is needed
2725 -- for determining which SUPPLY table to be used for pegging.
2726
2727 /** PREPLACE CHANGE END **/
2728 /* 2201791 - select substr(order_number,1,62) since order_number is
2729 defined as varchar(62) in msc_demands table */
2730
2731
2732 IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') Then
2733 v_srp_profile_vlaue := 1;
2734
2735 ELSE
2736 v_srp_profile_vlaue := 0;
2737
2738 END IF; -- For Bug 5909379
2739
2740 lv_cursor_stmt:=
2741 'SELECT'
2742 ||' -1, MSC_DEMANDS_S.nextval, '
2743 ||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
2744 ||' t1.INVENTORY_ITEM_ID,'
2745 ||' msd.ORGANIZATION_ID,'
2746 ||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
2747 ||' msd.USING_ASSEMBLY_DEMAND_DATE,'
2748 ||' msd.USING_REQUIREMENT_QUANTITY,'
2749 ||' msd.QUANTITY_PER_ASSEMBLY,'
2750 ||' msd.QUANTITY_ISSUED,'
2751 ||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
2752 ||' msd.DEMAND_TYPE,'
2753 ||' msd.ORIGINATION_TYPE,'
2754 ||' msd.SOURCE_ORGANIZATION_ID,'
2755 ||' msd.RESERVATION_ID,'
2756 ||' msd.OPERATION_SEQ_NUM,'
2757 ||' msd.DEMAND_CLASS,'
2758 ||' msd.REPETITIVE_SCHEDULE_ID,'
2759 ||' msd.SR_INSTANCE_ID,'
2760 ||' msd.PROJECT_ID,'
2761 ||' msd.TASK_ID,'
2762 ||' msd.PLANNING_GROUP,'
2763 ||' msd.END_ITEM_UNIT_NUMBER, '
2764 ||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
2765 ||' msd.WIP_ENTITY_ID,'
2766 ||' msd.WIP_STATUS_CODE,'
2767 ||' msd.WIP_SUPPLY_TYPE,'
2768 ||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
2769 ||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
2770 ||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
2771 ||' msd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
2772 ||' DECODE (:v_srp_profile_vlaue,1,msd.ITEM_TYPE_ID,NULL), '
2773 ||' DECODE (:v_srp_profile_vlaue,1,msd.ITEM_TYPE_VALUE,NULL),' -- For bug 5909379
2774 ||' :v_last_collection_id,'
2775 ||' :v_current_date,'
2776 ||' :v_current_user,'
2777 ||' :v_current_date,'
2778 ||' :v_current_user '
2779 ||' FROM MSC_ITEM_ID_LID t1,'
2780 ||' MSC_ITEM_ID_LID t2,'
2781 ||' MSC_ITEM_ID_LID t3,'
2782 || lv_supplies_tbl||' ms,'
2783 ||' MSC_ST_DEMANDS msd'
2784 ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2785 ||' AND msd.ORIGINATION_TYPE IN (2,3,4,25,50)' /* 50 eam demand: ds change change*/
2786 ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2787 ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
2788 ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
2789 ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
2790 ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
2791 ||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
2792 ||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
2793 ||' AND ms.sr_instance_id(+)= msd.SR_INSTANCE_ID'
2794 ||' AND ms.ORGANIZATION_ID(+)= msd.ORGANIZATION_ID'
2795 ||' AND ms.DISPOSITION_ID(+)= DECODE( msd.ORIGINATION_TYPE,'
2796 ||' 4, msd.REPETITIVE_SCHEDULE_ID,'
2797 ||' msd.WIP_ENTITY_ID)'
2798 ||' AND ms.plan_id(+)=-1'
2799 ||' AND ms.ORDER_TYPE(+)= DECODE( msd.ORIGINATION_TYPE, 2,7,3,3,4,4,25,27,50,70)'; /* ds change change*/
2800
2801 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2802 lv_sql_stmt:=
2803 'INSERT /*+ APPEND */ INTO '||lv_tbl
2804 ||'( PLAN_ID,'
2805 ||' DEMAND_ID,'
2806 ||' DISPOSITION_ID,'
2807 ||' INVENTORY_ITEM_ID,'
2808 ||' ORGANIZATION_ID,'
2809 ||' USING_ASSEMBLY_ITEM_ID,'
2810 ||' USING_ASSEMBLY_DEMAND_DATE,'
2811 ||' USING_REQUIREMENT_QUANTITY,'
2812 ||' QUANTITY_PER_ASSEMBLY,'
2813 ||' ISSUED_QUANTITY,'
2814 ||' ASSEMBLY_DEMAND_COMP_DATE,'
2815 ||' DEMAND_TYPE,'
2816 ||' ORIGINATION_TYPE,'
2817 ||' SOURCE_ORGANIZATION_ID,'
2818 ||' RESERVATION_ID,'
2819 ||' OP_SEQ_NUM,'
2820 ||' DEMAND_CLASS,'
2821 ||' REPETITIVE_SCHEDULE_ID,'
2822 ||' SR_INSTANCE_ID,'
2823 ||' PROJECT_ID,'
2824 ||' TASK_ID,'
2825 ||' PLANNING_GROUP,'
2826 ||' UNIT_NUMBER,'
2827 ||' ORDER_NUMBER,'
2828 ||' WIP_ENTITY_ID,'
2829 ||' WIP_STATUS_CODE,'
2830 ||' WIP_SUPPLY_TYPE,'
2831 ||' ASSET_ITEM_ID,'
2832 ||' ASSET_SERIAL_NUMBER,'
2833 ||' COMPONENT_SCALING_TYPE,'
2834 ||' COMPONENT_YIELD_FACTOR,'
2835 ||' ITEM_TYPE_ID,'
2836 ||' ITEM_TYPE_VALUE,' -- For bug 5909379
2837 ||' REFRESH_NUMBER,'
2838 ||' LAST_UPDATE_DATE,'
2839 ||' LAST_UPDATED_BY,'
2840 ||' CREATION_DATE,'
2841 ||' CREATED_BY)'
2842 || lv_cursor_stmt;
2843
2844 BEGIN
2845
2846 SAVEPOINT Load_wip_SP;
2847 EXECUTE IMMEDIATE lv_sql_stmt
2848 USING
2849 MSC_CL_COLLECTION.v_chr10,
2850 MSC_CL_COLLECTION.v_chr13,
2851 v_srp_profile_vlaue,
2852 v_srp_profile_vlaue,
2853 MSC_CL_COLLECTION.v_last_collection_id,
2854 MSC_CL_COLLECTION.v_current_date,
2855 MSC_CL_COLLECTION.v_current_user,
2856 MSC_CL_COLLECTION.v_current_date,
2857 MSC_CL_COLLECTION.v_current_user;
2858
2859 COMMIT;
2860 RETURN;
2861
2862 EXCEPTION
2863 WHEN OTHERS THEN
2864
2865 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_WIP_DEMAND>>');
2866 IF lv_sql_stmt IS NOT NULL THEN
2867 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
2868 END IF;
2869 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2870
2871 ROLLBACK WORK TO SAVEPOINT Load_wip_SP;
2872 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Switching to Row-By-Row processing.');
2873
2874 END;
2875 END IF;
2876
2877 -- ========= Prepare SQL Statement for INSERT ==========
2878 lv_sql_stmt:=
2879 'INSERT INTO '||lv_tbl
2880 ||'( PLAN_ID,'
2881 ||' DEMAND_ID,'
2882 ||' INVENTORY_ITEM_ID,'
2883 ||' ORGANIZATION_ID,'
2884 ||' USING_ASSEMBLY_ITEM_ID,'
2885 ||' USING_ASSEMBLY_DEMAND_DATE,'
2886 ||' USING_REQUIREMENT_QUANTITY,'
2887 ||' QUANTITY_PER_ASSEMBLY,'
2888 ||' ISSUED_QUANTITY,'
2889 ||' ASSEMBLY_DEMAND_COMP_DATE,'
2890 ||' DEMAND_TYPE,'
2891 ||' ORIGINATION_TYPE,'
2892 ||' SOURCE_ORGANIZATION_ID,'
2893 ||' DISPOSITION_ID,'
2894 ||' RESERVATION_ID,'
2895 ||' OP_SEQ_NUM,'
2896 ||' DEMAND_CLASS,'
2897 ||' SR_INSTANCE_ID,'
2898 ||' PROJECT_ID,'
2899 ||' TASK_ID,'
2900 ||' PLANNING_GROUP,'
2901 ||' UNIT_NUMBER,'
2902 ||' ORDER_NUMBER,'
2903 ||' WIP_ENTITY_ID,'
2904 ||' WIP_STATUS_CODE,'
2905 ||' WIP_SUPPLY_TYPE,'
2906 ||' REPETITIVE_SCHEDULE_ID,'
2907 ||' ASSET_ITEM_ID,' /* ds change change*/
2908 ||' ASSET_SERIAL_NUMBER,' /* ds change change*/
2909 ||' COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
2910 ||' COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
2911 ||' REFRESH_NUMBER,'
2912 ||' LAST_UPDATE_DATE,'
2913 ||' LAST_UPDATED_BY,'
2914 ||' CREATION_DATE,'
2915 ||' CREATED_BY)'
2916 ||'VALUES'
2917 ||'( -1,'
2918 ||' MSC_DEMANDS_S.nextval,'
2919 ||' :INVENTORY_ITEM_ID,'
2920 ||' :ORGANIZATION_ID,'
2921 ||' :USING_ASSEMBLY_ITEM_ID,'
2922 ||' :USING_ASSEMBLY_DEMAND_DATE,'
2923 ||' :USING_REQUIREMENT_QUANTITY,'
2924 ||' :QUANTITY_PER_ASSEMBLY,'
2925 ||' :ISSUED_QUANTITY,'
2926 ||' :ASSEMBLY_DEMAND_COMP_DATE,'
2927 ||' :DEMAND_TYPE,'
2928 ||' :ORIGINATION_TYPE,'
2929 ||' :SOURCE_ORGANIZATION_ID,'
2930 ||' :DISPOSITION_ID,'
2931 ||' :RESERVATION_ID,'
2932 ||' :OPERATION_SEQ_NUM,'
2933 ||' :DEMAND_CLASS,'
2934 ||' :SR_INSTANCE_ID,'
2935 ||' :PROJECT_ID,'
2936 ||' :TASK_ID,'
2937 ||' :PLANNING_GROUP,'
2938 ||' :END_ITEM_UNIT_NUMBER, '
2939 ||' :ORDER_NUMBER,'
2940 ||' :WIP_ENTITY_ID,'
2941 ||' :WIP_STATUS_CODE,'
2942 ||' :WIP_SUPPLY_TYPE,'
2943 ||' :REPETITIVE_SCHEDULE_ID,'
2944 ||' :ASSET_ITEM_ID,' /* ds change change*/
2945 ||' :ASSET_SERIAL_NUMBER,' /* ds change change*/
2946 ||' :COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
2947 ||' :COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
2948 ||' :v_last_collection_id,'
2949 ||' :v_current_date,'
2950 ||' :v_current_user,'
2951 ||' :v_current_date,'
2952 ||' :v_current_user )';
2953
2954 /* Cursor statement below is used in case of net change.
2955 This cursor will also load data in target/complete mode,
2956 if the bulk insert above failed for whatever reason */
2957
2958 lv_cursor_stmt:=
2959 'SELECT'
2960 ||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
2961 ||' t1.INVENTORY_ITEM_ID,'
2962 ||' msd.ORGANIZATION_ID,'
2963 ||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
2964 ||' msd.USING_ASSEMBLY_DEMAND_DATE,'
2965 ||' msd.USING_REQUIREMENT_QUANTITY,'
2966 ||' msd.QUANTITY_PER_ASSEMBLY,'
2967 ||' msd.QUANTITY_ISSUED,'
2968 ||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
2969 ||' msd.DEMAND_TYPE,'
2970 ||' msd.ORIGINATION_TYPE,'
2971 ||' msd.SOURCE_ORGANIZATION_ID,'
2972 ||' msd.RESERVATION_ID,'
2973 ||' msd.OPERATION_SEQ_NUM,'
2974 ||' msd.DEMAND_CLASS,'
2975 ||' msd.REPETITIVE_SCHEDULE_ID,'
2976 ||' msd.SR_INSTANCE_ID,'
2977 ||' msd.PROJECT_ID,'
2978 ||' msd.TASK_ID,'
2979 ||' msd.PLANNING_GROUP,'
2980 ||' msd.END_ITEM_UNIT_NUMBER, '
2981 ||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
2982 ||' msd.WIP_ENTITY_ID,'
2983 ||' msd.WIP_STATUS_CODE,'
2984 ||' msd.WIP_SUPPLY_TYPE,'
2985 ||' msd.DELETED_FLAG,'
2986 ||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
2987 ||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
2988 ||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
2989 ||' msd.COMPONENT_YIELD_FACTOR' /* Discrete Mfg Enahancements Bug 4492743 */
2990 ||' FROM MSC_ITEM_ID_LID t1,'
2991 ||' MSC_ITEM_ID_LID t2,'
2992 ||' MSC_ITEM_ID_LID t3,'
2993 || lv_supplies_tbl||' ms,'
2994 ||' MSC_ST_DEMANDS msd'
2995 ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
2996 ||' AND msd.ORIGINATION_TYPE IN (2,3,4,25,50)' /* 50 eam demand: ds change change*/
2997 ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
2998 ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
2999 ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
3000 ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
3001 ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
3002 ||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
3003 ||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
3004 ||' AND ms.sr_instance_id(+)= msd.SR_INSTANCE_ID'
3005 ||' AND ms.ORGANIZATION_ID(+)= msd.ORGANIZATION_ID'
3006 ||' AND ms.DISPOSITION_ID(+)= DECODE( msd.ORIGINATION_TYPE,'
3007 ||' 4, msd.REPETITIVE_SCHEDULE_ID,'
3008 ||' msd.WIP_ENTITY_ID)'
3009 ||' AND ms.plan_id(+)=-1'
3010 ||' AND ms.ORDER_TYPE(+)= DECODE( msd.ORIGINATION_TYPE, 2,7,3,3,4,4,25,27,50,70)' /* ds change change*/
3011 ||' order by msd.SOURCE_WIP_ENTITY_ID, msd.SOURCE_INVENTORY_ITEM_ID,msd.SOURCE_ORGANIZATION_ID,msd.ORIGINATION_TYPE';
3012
3013 OPEN c2 FOR lv_cursor_stmt USING MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13;
3014
3015 LOOP
3016
3017 FETCH c2 INTO
3018 lv_DISPOSITION_ID,
3019 lv_INVENTORY_ITEM_ID,
3020 lv_ORGANIZATION_ID,
3021 lv_USING_ASSEMBLY_ITEM_ID,
3022 lv_USING_ASSEMBLY_DEMAND_DATE,
3023 lv_USING_REQUIREMENT_QUANTITY,
3024 lv_QUANTITY_PER_ASSEMBLY,
3025 lv_QUANTITY_ISSUED,
3026 lv_ASSEMBLY_DEMAND_COMP_DATE,
3027 lv_DEMAND_TYPE,
3028 lv_ORIGINATION_TYPE,
3029 lv_SOURCE_ORGANIZATION_ID,
3030 lv_RESERVATION_ID,
3031 lv_OPERATION_SEQ_NUM,
3032 lv_DEMAND_CLASS,
3033 lv_REPETITIVE_SCHEDULE_ID,
3034 lv_SR_INSTANCE_ID,
3035 lv_PROJECT_ID,
3036 lv_TASK_ID,
3037 lv_PLANNING_GROUP,
3038 lv_END_ITEM_UNIT_NUMBER,
3039 lv_ORDER_NUMBER,
3040 lv_WIP_ENTITY_ID,
3041 lv_WIP_STATUS_CODE,
3042 lv_WIP_SUPPLY_TYPE,
3043 lv_DELETED_FLAG,
3044 lv_ASSET_ITEM_ID, /* ds change change */
3045 lv_ASSET_SERIAL_NUMBER, /* ds change change */
3046 lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
3047 lv_COMPONENT_YIELD_FACTOR; /* Discrete Mfg Enahancements Bug 4492743 */
3048
3049 EXIT WHEN c2%NOTFOUND;
3050
3051 BEGIN
3052
3053 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3054
3055 --================= wip discrete job components ==================
3056 IF lv_ORIGINATION_TYPE IN (2, 3, 50) THEN /* ds change 50 eam demand*/
3057
3058 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
3059 UPDATE /*+ INDEX (MSC_DEMANDS MSC_DEMANDS_N5) */ MSC_DEMANDS
3060 SET
3061 OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
3062 OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
3063 OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
3064 USING_ASSEMBLY_ITEM_ID= lv_USING_ASSEMBLY_ITEM_ID,
3065 USING_ASSEMBLY_DEMAND_DATE= lv_USING_ASSEMBLY_DEMAND_DATE,
3066 USING_REQUIREMENT_QUANTITY= lv_USING_REQUIREMENT_QUANTITY,
3067 QUANTITY_PER_ASSEMBLY= lv_QUANTITY_PER_ASSEMBLY,
3068 ISSUED_QUANTITY= lv_QUANTITY_ISSUED,
3069 ASSEMBLY_DEMAND_COMP_DATE= lv_ASSEMBLY_DEMAND_COMP_DATE,
3070 DEMAND_TYPE= lv_DEMAND_TYPE,
3071 SOURCE_ORGANIZATION_ID= lv_SOURCE_ORGANIZATION_ID,
3072 RESERVATION_ID= lv_RESERVATION_ID,
3073 DEMAND_CLASS= lv_DEMAND_CLASS,
3074 PROJECT_ID= lv_PROJECT_ID,
3075 TASK_ID= lv_TASK_ID,
3076 PLANNING_GROUP= lv_PLANNING_GROUP,
3077 UNIT_NUMBER= lv_END_ITEM_UNIT_NUMBER,
3078 ORDER_NUMBER= lv_ORDER_NUMBER,
3079 WIP_STATUS_CODE= lv_WIP_STATUS_CODE,
3080 WIP_SUPPLY_TYPE= lv_WIP_SUPPLY_TYPE,
3081 DISPOSITION_ID= lv_DISPOSITION_ID,
3082 ASSET_ITEM_ID= lv_ASSET_ITEM_ID, /* ds change change */
3083 ASSET_SERIAL_NUMBER= lv_ASSET_SERIAL_NUMBER, /* ds changechange */
3084 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
3085 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
3086 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3087 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3088 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3089 WHERE PLAN_ID= -1
3090 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
3091 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
3092 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
3093 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
3094 AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
3095 AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID;
3096
3097 --================= wip repetitive schedule ==================
3098 ELSIF lv_ORIGINATION_TYPE=4 THEN
3099
3100 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
3101 UPDATE MSC_DEMANDS
3102 SET
3103 OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
3104 OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
3105 OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
3106 USING_ASSEMBLY_ITEM_ID= lv_USING_ASSEMBLY_ITEM_ID,
3107 USING_ASSEMBLY_DEMAND_DATE= lv_USING_ASSEMBLY_DEMAND_DATE,
3108 USING_REQUIREMENT_QUANTITY= lv_USING_REQUIREMENT_QUANTITY,
3109 ASSEMBLY_DEMAND_COMP_DATE= lv_ASSEMBLY_DEMAND_COMP_DATE,
3110 DEMAND_TYPE= lv_DEMAND_TYPE,
3111 SOURCE_ORGANIZATION_ID= lv_SOURCE_ORGANIZATION_ID,
3112 RESERVATION_ID= lv_RESERVATION_ID,
3113 DEMAND_CLASS= lv_DEMAND_CLASS,
3114 PROJECT_ID= lv_PROJECT_ID,
3115 TASK_ID= lv_TASK_ID,
3116 PLANNING_GROUP= lv_PLANNING_GROUP,
3117 UNIT_NUMBER= lv_END_ITEM_UNIT_NUMBER,
3118 ORDER_NUMBER= lv_ORDER_NUMBER,
3119 WIP_STATUS_CODE= lv_WIP_STATUS_CODE,
3120 WIP_SUPPLY_TYPE= lv_WIP_SUPPLY_TYPE,
3121 DISPOSITION_ID= lv_DISPOSITION_ID,
3122 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
3123 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
3124 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3125 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3126 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3127 WHERE PLAN_ID= -1
3128 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
3129 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
3130 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
3131 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
3132 AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
3133 AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
3134 AND REPETITIVE_SCHEDULE_ID= lv_REPETITIVE_SCHEDULE_ID;
3135
3136 END IF; -- Origination_Type
3137
3138 END IF; -- refresh mode
3139
3140 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
3141 ( lv_DELETED_FLAG<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) OR
3142 ( lv_ORIGINATION_TYPE= 25) THEN
3143
3144 EXECUTE IMMEDIATE lv_sql_stmt
3145 USING
3146 lv_INVENTORY_ITEM_ID,
3147 lv_ORGANIZATION_ID,
3148 lv_USING_ASSEMBLY_ITEM_ID,
3149 lv_USING_ASSEMBLY_DEMAND_DATE,
3150 lv_USING_REQUIREMENT_QUANTITY,
3151 lv_QUANTITY_PER_ASSEMBLY,
3152 lv_QUANTITY_ISSUED,
3153 lv_ASSEMBLY_DEMAND_COMP_DATE,
3154 lv_DEMAND_TYPE,
3155 lv_ORIGINATION_TYPE,
3156 lv_SOURCE_ORGANIZATION_ID,
3157 lv_DISPOSITION_ID,
3158 lv_RESERVATION_ID,
3159 lv_OPERATION_SEQ_NUM,
3160 lv_DEMAND_CLASS,
3161 lv_SR_INSTANCE_ID,
3162 lv_PROJECT_ID,
3163 lv_TASK_ID,
3164 lv_PLANNING_GROUP,
3165 lv_END_ITEM_UNIT_NUMBER,
3166 lv_ORDER_NUMBER,
3167 lv_WIP_ENTITY_ID,
3168 lv_WIP_STATUS_CODE,
3169 lv_WIP_SUPPLY_TYPE,
3170 lv_REPETITIVE_SCHEDULE_ID,
3171 lv_ASSET_ITEM_ID, /* ds change change */
3172 lv_ASSET_SERIAL_NUMBER, /* ds changechange */
3173 lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
3174 lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
3175 MSC_CL_COLLECTION.v_last_collection_id,
3176 MSC_CL_COLLECTION.v_current_date,
3177 MSC_CL_COLLECTION.v_current_user,
3178 MSC_CL_COLLECTION.v_current_date,
3179 MSC_CL_COLLECTION.v_current_user;
3180
3181 END IF;
3182
3183 EXCEPTION
3184
3185 WHEN OTHERS THEN
3186
3187 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3188
3189 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3190 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3191 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3192 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3193 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3194
3195 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3196 RAISE;
3197
3198 ELSE
3199 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3200
3201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3202 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3203 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3204 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
3205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3206
3207 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3208 FND_MESSAGE.SET_TOKEN('COLUMN', 'WIP_ENTITY_ID');
3209 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_WIP_ENTITY_ID));
3210 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3211
3212 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3213 FND_MESSAGE.SET_TOKEN('COLUMN', 'ITEM_NAME');
3214 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( lv_INVENTORY_ITEM_ID));
3215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3216
3217 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3218 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3219 FND_MESSAGE.SET_TOKEN('VALUE',
3220 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3221 MSC_CL_COLLECTION.v_instance_id));
3222 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3223
3224 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3225 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
3226 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEMAND_TYPE));
3227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3228
3229 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3230 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
3231 FND_MESSAGE.SET_TOKEN('VALUE',
3232 MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
3233 lv_ORIGINATION_TYPE));
3234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3235
3236 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3237 END IF;
3238
3239 END;
3240
3241 c_count:= c_count+1;
3242
3243 IF c_count> MSC_CL_COLLECTION.PBS THEN
3244 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
3245 c_count:= 0;
3246 END IF;
3247
3248 END LOOP; -- cursor c2
3249
3250 CLOSE c2;
3251
3252 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
3253
3254 EXCEPTION
3255 WHEN OTHERS THEN
3256 IF c2%ISOPEN THEN CLOSE c2; END IF;
3257
3258 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_WIP_DEMAND>>');
3259 IF lv_cursor_stmt IS NOT NULL THEN
3260 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
3261 END IF;
3262 IF lv_sql_stmt IS NOT NULL THEN
3263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
3264 END IF;
3265 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3266 RAISE;
3267 END LOAD_WIP_DEMAND;
3268
3269 --============================================================================
3270
3271 -- =================== LOAD RESOURCE REQUIREMENTS ===============
3272 PROCEDURE LOAD_RES_REQ IS
3273
3274 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
3275 c4 CurTyp;
3276
3277 c_count NUMBER:=0;
3278 lv_tbl VARCHAR2(30);
3279 lv_supplies_tbl VARCHAR2(30);
3280 lv_sql_stmt VARCHAR2(5000);
3281 lv_cursor_stmt VARCHAR2(5000);
3282
3283 CURSOR c4_d IS
3284 SELECT
3285 msrr.WIP_ENTITY_ID,
3286 msrr.OPERATION_SEQ_NUM,
3287 msrr.ORIG_RESOURCE_SEQ_NUM,
3288 msrr.SR_INSTANCE_ID
3289 FROM MSC_ST_RESOURCE_REQUIREMENTS msrr
3290 WHERE msrr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3291 AND msrr.DELETED_FLAG= MSC_UTIL.SYS_YES;
3292
3293 lv_DEPARTMENT_ID NUMBER;
3294 lv_RESOURCE_ID NUMBER;
3295 lv_ASSEMBLY_ITEM_ID NUMBER;
3296 lv_ORGANIZATION_ID NUMBER;
3297 lv_SUPPLY_ID NUMBER;
3298 lv_WIP_ENTITY_ID NUMBER;
3299 lv_ROUTING_SEQUENCE_ID NUMBER;
3300 lv_OPERATION_SEQ_NUM NUMBER;
3301 lv_OPERATION_SEQUENCE_ID NUMBER;
3302 lv_RESOURCE_SEQ_NUM NUMBER;
3303 lv_START_DATE DATE;
3304 lv_OPERATION_HOURS_REQUIRED NUMBER;
3305 lv_HOURS_EXPENDED NUMBER;
3306 lv_QUANTITY_IN_QUEUE NUMBER;
3307 lv_QUANTITY_RUNNING NUMBER;
3308 lv_QUANTITY_WAITING_TO_MOVE NUMBER;
3309 lv_QUANTITY_COMPLETED NUMBER;
3310 lv_YIELD NUMBER;
3311 lv_USAGE_RATE NUMBER;
3312 lv_BASIS_TYPE NUMBER;
3313 lv_ASSIGNED_UNITS NUMBER;
3314 lv_GROUP_SEQUENCE_ID NUMBER; /* ds change chaneg start */
3315 lv_GROUP_SEQUENCE_NUMBER NUMBER;
3316 lv_BATCH_NUMBER NUMBER;
3317 lv_MAXIMUM_ASSIGNED_UNITS NUMBER;
3318 lv_MAXIMUM_CAPACITY NUMBER;
3319 lv_BREAKABLE_ACTIVITY_FLAG NUMBER;
3320 lv_STEP_QUANTITY NUMBER; /* ds change change end */
3321 lv_STEP_QUANTITY_UOM VARCHAR2(3); /* ds change change end */
3322 lv_MINIMUM_CAPACITY NUMBER; /* ds change change end */
3323 lv_ACTUAL_START_DATE DATE; /* Discrete Mfg Enahancements Bug 4479276 */
3324 lv_END_DATE DATE;
3325 lv_SUPPLY_TYPE NUMBER;
3326 lv_STD_OP_CODE VARCHAR2(4);
3327 lv_DELETED_FLAG NUMBER;
3328 lv_minimum_transfer_quantity NUMBER;
3329 lv_firm_flag NUMBER;
3330 lv_schedule_flag NUMBER;
3331 lv_SR_INSTANCE_ID NUMBER;
3332 lv_PARENT_SEQ_NUM NUMBER;
3333 lv_SETUP_ID NUMBER;
3334 lv_ACTIVITY_GROUP_ID NUMBER;
3335 lv_ALTERNATE_NUMBER NUMBER;
3336 lv_PRINCIPAL_FLAG NUMBER;
3337 lv_ORIG_RESOURCE_SEQ_NUM NUMBER;
3338 lv_UNADJUSTED_RESOURCE_HOURS NUMBER;
3339 lv_TOUCH_TIME NUMBER;
3340 lv_ACTIVITY_NAME VARCHAR2(20);
3341 lv_OPERATION_NAME VARCHAR2(20);
3342 lv_legacy_refresh NUMBER; /*bug 3768813 */
3343
3344 lv_errbuf VARCHAR2(240);
3345 lv_retcode NUMBER;
3346 lv_sql_ins VARCHAR2(6000);
3347 lb_refresh_failed BOOLEAN:= FALSE;
3348
3349 BEGIN
3350
3351 -- link supply_id to MSC_supplies.transaction_id
3352
3353
3354 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3355 COMMIT;
3356 END IF;
3357
3358 c_count:= 0;
3359
3360 -- ========= Prepare the Cursor Statement ==========
3361 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3362 lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
3363 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
3364 ELSE
3365 lv_tbl:= 'MSC_RESOURCE_REQUIREMENTS';
3366 lv_supplies_tbl:= 'MSC_SUPPLIES';
3367 END IF;
3368
3369 /** PREPLACE CHANGE START **/
3370
3371 -- For Load_RES_REQ Supplies are also loaded - WIP Parameter
3372 -- simultaneously hence no special logic is needed
3373 -- for determining which SUPPLY table to be used for reference.
3374
3375 /** PREPLACE CHANGE END **/
3376
3377 lv_cursor_stmt:=
3378 'SELECT'
3379 ||' msrr.DEPARTMENT_ID,'
3380 ||' msrr.RESOURCE_ID,'
3381 ||' miil.INVENTORY_ITEM_ID,'
3382 ||' msrr.ORGANIZATION_ID,'
3383 ||' NVL(ms.TRANSACTION_ID,-1) SUPPLY_ID,'
3384 ||' msrr.WIP_ENTITY_ID,'
3385 ||' msrr.ROUTING_SEQUENCE_ID,'
3386 ||' msrr.OPERATION_SEQ_NUM,'
3387 ||' msrr.OPERATION_SEQUENCE_ID,'
3388 ||' msrr.RESOURCE_SEQ_NUM,'
3389 ||' msrr.START_DATE,'
3390 ||' msrr.OPERATION_HOURS_REQUIRED,'
3391 ||' msrr.HOURS_EXPENDED,'
3392 ||' msrr.QUANTITY_IN_QUEUE,'
3393 ||' msrr.QUANTITY_RUNNING,'
3394 ||' msrr.QUANTITY_WAITING_TO_MOVE,'
3395 ||' msrr.QUANTITY_COMPLETED,'
3396 ||' msrr.YIELD,'
3397 ||' msrr.USAGE_RATE,'
3398 ||' msrr.BASIS_TYPE,'
3399 ||' msrr.ASSIGNED_UNITS,'
3400 ||' msrr.END_DATE,'
3401 ||' msrr.SUPPLY_TYPE,'
3402 ||' msrr.STD_OP_CODE,'
3403 ||' msrr.DELETED_FLAG,'
3404 ||' msrr.MINIMUM_TRANSFER_QUANTITY,'
3405 ||' msrr.FIRM_FLAG,'
3406 ||' msrr.SCHEDULE_FLAG,'
3407 ||' msrr.PARENT_SEQ_NUM,'
3408 ||' msrr.SETUP_ID,'
3409 ||' msrr.ACTIVITY_GROUP_ID,'
3410 ||' msrr.ALTERNATE_NUMBER,'
3411 ||' msrr.PRINCIPAL_FLAG,'
3412 ||' msrr.SR_INSTANCE_ID,'
3413 ||' msrr.ORIG_RESOURCE_SEQ_NUM, '
3414 ||' msrr.GROUP_SEQUENCE_ID, ' /*ds change change start */
3415 ||' msrr.GROUP_SEQUENCE_NUMBER, '
3416 ||' msrr.BATCH_NUMBER, '
3417 ||' msrr.MAXIMUM_ASSIGNED_UNITS, '
3418 ||' msrr.MAXIMUM_CAPACITY, '
3419 ||' msrr.BREAKABLE_ACTIVITY_FLAG, '
3420 ||' msrr.STEP_QUANTITY, '
3421 ||' msrr.STEP_QUANTITY_UOM, '
3422 ||' msrr.MINIMUM_CAPACITY, ' /*ds change change end */
3423 ||' msrr.ACTUAL_START_DATE, ' /* Discrete Mfg Enahancements Bug 4479276 */
3424 ||' msrr.UNADJUSTED_RESOURCE_HOURS, '
3425 ||' msrr.TOUCH_TIME, '
3426 ||' msrr.ACTIVITY_NAME, '
3427 ||' msrr.OPERATION_NAME '
3428 ||' FROM '||lv_supplies_tbl||' ms,'
3429 ||' MSC_ST_RESOURCE_REQUIREMENTS msrr,'
3430 ||' MSC_ITEM_ID_LID miil'
3431 ||' WHERE msrr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
3432 ||' AND ms.PLAN_ID= -1'
3433 ||' AND ms.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID'
3434 ||' AND ms.DISPOSITION_ID= msrr.WIP_ENTITY_ID'
3435 ||' AND ms.ORDER_TYPE IN ( 3, 7,70)' /* 70 esm suply:ds change change */
3436 ||' AND msrr.DELETED_FLAG= '||MSC_UTIL.SYS_NO
3437 ||' AND miil.SR_INVENTORY_ITEM_ID= msrr.INVENTORY_ITEM_ID'
3438 ||' AND miil.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID';
3439
3440 -- ========= Prepare SQL Statement for INSERT ==========
3441 lv_sql_stmt:=
3442 'insert into '||lv_tbl
3443 ||' ( PLAN_ID,'
3444 ||' TRANSACTION_ID,'
3445 ||' DEPARTMENT_ID,'
3446 ||' RESOURCE_ID,'
3447 ||' ORGANIZATION_ID,'
3448 ||' ASSEMBLY_ITEM_ID,'
3449 ||' SUPPLY_ID,'
3450 ||' WIP_ENTITY_ID,'
3451 ||' ROUTING_SEQUENCE_ID,'
3452 ||' SUPPLY_TYPE,'
3453 ||' OPERATION_SEQ_NUM,'
3454 ||' OPERATION_SEQUENCE_ID,'
3455 ||' RESOURCE_SEQ_NUM,'
3456 ||' START_DATE,'
3457 ||' RESOURCE_HOURS,'
3458 ||' HOURS_EXPENDED,'
3459 ||' QUANTITY_IN_QUEUE,'
3460 ||' QUANTITY_RUNNING,'
3461 ||' QUANTITY_WAITING_TO_MOVE,'
3462 ||' QUANTITY_COMPLETED,'
3463 ||' YIELD,'
3464 ||' USAGE_RATE,'
3465 ||' BASIS_TYPE,'
3466 ||' ASSIGNED_UNITS,'
3467 ||' END_DATE,'
3468 ||' STD_OP_CODE,'
3469 ||' ACTIVITY_GROUP_ID,'
3470 ||' ALTERNATE_NUM,'
3471 ||' PRINCIPAL_FLAG,'
3472 ||' SR_INSTANCE_ID,'
3473 ||' REFRESH_NUMBER,'
3474 ||' MINIMUM_TRANSFER_QUANTITY,'
3475 ||' FIRM_FLAG,'
3476 ||' SCHEDULE_FLAG,'
3477 ||' PARENT_SEQ_NUM,'
3478 ||' SETUP_ID,'
3479 ||' ORIG_RESOURCE_SEQ_NUM,'
3480 ||' GROUP_SEQUENCE_ID, ' /*ds change change start */
3481 ||' GROUP_SEQUENCE_NUMBER, '
3482 ||' BATCH_NUMBER, '
3483 ||' MAXIMUM_ASSIGNED_UNITS, '
3484 ||' MAXIMUM_CAPACITY, '
3485 ||' BREAKABLE_ACTIVITY_FLAG, '
3486 ||' STEP_QUANTITY, '
3487 ||' STEP_QUANTITY_UOM, '
3488 ||' MINIMUM_CAPACITY, ' /*ds change change end */
3489 ||' ACTUAL_START_DATE, ' /* Discrete Mfg Enahancements Bug 4479276 */
3490 ||' TOTAL_RESOURCE_HOURS, ' /* Discrete Mfg Enahancements Bug 4479276 */
3491 ||' UNADJUSTED_RESOURCE_HOURS, '
3492 ||' TOUCH_TIME, '
3493 ||' ACTIVITY_NAME, '
3494 ||' OPERATION_NAME, '
3495 ||' LAST_UPDATE_DATE,'
3496 ||' LAST_UPDATED_BY,'
3497 ||' CREATION_DATE,'
3498 ||' CREATED_BY)'
3499 ||'VALUES'
3500 ||'( -1,'
3501 ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
3502 ||' :DEPARTMENT_ID,'
3503 ||' :RESOURCE_ID,'
3504 ||' :ORGANIZATION_ID,'
3505 ||' :ASSEMBLY_ITEM_ID,'
3506 ||' :SUPPLY_ID,'
3507 ||' :WIP_ENTITY_ID,'
3508 ||' :ROUTING_SEQUENCE_ID,'
3509 ||' :SUPPLY_TYPE,'
3510 ||' :OPERATION_SEQ_NUM,'
3511 ||' :OPERATION_SEQUENCE_ID,'
3512 ||' :RESOURCE_SEQ_NUM,'
3513 ||' :START_DATE,'
3514 ||' :OPERATION_HOURS_REQUIRED,'
3515 ||' :HOURS_EXPENDED,'
3516 ||' :QUANTITY_IN_QUEUE,'
3517 ||' :QUANTITY_RUNNING,'
3518 ||' :QUANTITY_WAITING_TO_MOVE,'
3519 ||' :QUANTITY_COMPLETED,'
3520 ||' :YIELD,'
3521 ||' :USAGE_RATE,'
3522 ||' :BASIS_TYPE,'
3523 ||' :ASSIGNED_UNITS,'
3524 ||' :END_DATE,'
3525 ||' :STD_OP_CODE,'
3526 ||' :ACTIVITY_GROUP_ID,'
3527 ||' :ALTERNATE_NUMBER,'
3528 ||' :PRINCIPAL_FLAG,'
3529 ||' :SR_INSTANCE_ID,'
3530 ||' :v_last_collection_id,'
3531 ||' :MINIMUM_TRANSFER_QUANTITY,'
3532 ||' :FIRM_FLAG,'
3533 ||' :SCHEDULE_FLAG,'
3534 ||' :PARENT_SEQ_NUM,'
3535 ||' :SETUP_ID,'
3536 ||' :ORIG_RESOURCE_SEQ_NUM,'
3537 ||' :GROUP_SEQUENCE_ID, ' /*ds change change start */
3538 ||' :GROUP_SEQUENCE_NUMBER, '
3539 ||' :BATCH_NUMBER, '
3540 ||' :MAXIMUM_ASSIGNED_UNITS, '
3541 ||' :MAXIMUM_CAPACITY, '
3542 ||' :BREAKABLE_ACTIVITY_FLAG, '
3543 ||' :STEP_QUANTITY, '
3544 ||' :STEP_QUANTITY_UOM, '
3545 ||' :MINIMUM_CAPACITY, ' /*ds change change end */
3546 ||' :ACTUAL_START_DATE, ' /* Discrete Mfg Enahancements Bug 4479276 */
3547 ||' :TOTAL_RESOURCE_HOURS, ' /* Discrete Mfg Enahancements Bug 4479276 */
3548 ||' :UNADJUSTED_RESOURCE_HOURS,'
3549 ||' :TOUCH_TIME,'
3550 ||' :ACTIVITY_NAME,'
3551 ||' :OPERATION_NAME,'
3552 ||' :v_current_date,'
3553 ||' :v_current_user,'
3554 ||' :v_current_date,'
3555 ||' :v_current_user)';
3556
3557 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) AND MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3558 BEGIN
3559 lv_sql_ins:=
3560 'insert into '||lv_tbl
3561 ||' ( PLAN_ID,'
3562 ||' TRANSACTION_ID,'
3563 ||' DEPARTMENT_ID,'
3564 ||' RESOURCE_ID,'
3565 ||' ORGANIZATION_ID,'
3566 ||' ASSEMBLY_ITEM_ID,'
3567 ||' SUPPLY_ID,'
3568 ||' WIP_ENTITY_ID,'
3569 ||' ROUTING_SEQUENCE_ID,'
3570 ||' SUPPLY_TYPE,'
3571 ||' OPERATION_SEQ_NUM,'
3572 ||' OPERATION_SEQUENCE_ID,'
3573 ||' RESOURCE_SEQ_NUM,'
3574 ||' START_DATE,'
3575 ||' RESOURCE_HOURS,'
3576 ||' HOURS_EXPENDED,'
3577 ||' QUANTITY_IN_QUEUE,'
3578 ||' QUANTITY_RUNNING,'
3579 ||' QUANTITY_WAITING_TO_MOVE,'
3580 ||' QUANTITY_COMPLETED,'
3581 ||' YIELD,'
3582 ||' USAGE_RATE,'
3583 ||' BASIS_TYPE,'
3584 ||' ASSIGNED_UNITS,'
3585 ||' END_DATE,'
3586 ||' STD_OP_CODE,'
3587 ||' ACTIVITY_GROUP_ID,'
3588 ||' ALTERNATE_NUM,'
3589 ||' PRINCIPAL_FLAG,'
3590 ||' SR_INSTANCE_ID,'
3591 ||' REFRESH_NUMBER,'
3592 ||' MINIMUM_TRANSFER_QUANTITY,'
3593 ||' FIRM_FLAG,'
3594 ||' SCHEDULE_FLAG,'
3595 ||' PARENT_SEQ_NUM,'
3596 ||' SETUP_ID,'
3597 ||' ORIG_RESOURCE_SEQ_NUM,'
3598 ||' GROUP_SEQUENCE_ID, ' /*ds change change start */
3599 ||' GROUP_SEQUENCE_NUMBER, '
3600 ||' BATCH_NUMBER, '
3601 ||' MAXIMUM_ASSIGNED_UNITS, '
3602 ||' MAXIMUM_CAPACITY, '
3603 ||' BREAKABLE_ACTIVITY_FLAG, '
3604 ||' STEP_QUANTITY, '
3605 ||' STEP_QUANTITY_UOM, '
3606 ||' MINIMUM_CAPACITY, ' /*ds change change end */
3607 ||' ACTUAL_START_DATE, ' /* Discrete Mfg Enahancements Bug 4479276 */
3608 ||' TOTAL_RESOURCE_HOURS, ' /* Discrete Mfg Enahancements Bug 4479276 */
3609 ||' UNADJUSTED_RESOURCE_HOURS, '
3610 ||' TOUCH_TIME, '
3611 ||' ACTIVITY_NAME, '
3612 ||' OPERATION_NAME, '
3613 ||' LAST_UPDATE_DATE,'
3614 ||' LAST_UPDATED_BY,'
3615 ||' CREATION_DATE,'
3616 ||' CREATED_BY)'
3617 ||' SELECT'
3618 ||' -1,'
3619 ||' MSC_RESOURCE_REQUIREMENTS_S.NEXTVAL,'
3620 ||' msrr.DEPARTMENT_ID,'
3621 ||' msrr.RESOURCE_ID,'
3622 ||' msrr.ORGANIZATION_ID,'
3623 ||' miil.INVENTORY_ITEM_ID,'
3624 ||' NVL(ms.TRANSACTION_ID,-1),'
3625 ||' msrr.WIP_ENTITY_ID,'
3626 ||' msrr.ROUTING_SEQUENCE_ID,'
3627 ||' msrr.SUPPLY_TYPE,'
3628 ||' msrr.OPERATION_SEQ_NUM,'
3629 ||' msrr.OPERATION_SEQUENCE_ID,'
3630 ||' msrr.RESOURCE_SEQ_NUM,'
3631 ||' msrr.START_DATE,'
3632 ||' greatest((msrr.OPERATION_HOURS_REQUIRED - NVL(msrr.HOURS_EXPENDED,0)),0),'
3633 ||' msrr.HOURS_EXPENDED,'
3634 ||' msrr.QUANTITY_IN_QUEUE,'
3635 ||' msrr.QUANTITY_RUNNING,'
3636 ||' msrr.QUANTITY_WAITING_TO_MOVE,'
3637 ||' msrr.QUANTITY_COMPLETED,'
3638 ||' msrr.YIELD,'
3639 ||' msrr.USAGE_RATE,'
3640 ||' msrr.BASIS_TYPE,'
3641 ||' msrr.ASSIGNED_UNITS,'
3642 ||' msrr.END_DATE,'
3643 ||' msrr.STD_OP_CODE,'
3644 ||' msrr.ACTIVITY_GROUP_ID,'
3645 ||' msrr.ALTERNATE_NUMBER,'
3646 ||' msrr.PRINCIPAL_FLAG,'
3647 ||' msrr.SR_INSTANCE_ID,'
3648 ||' :v_last_collection_id,'
3649 ||' msrr.MINIMUM_TRANSFER_QUANTITY,'
3650 ||' msrr.FIRM_FLAG,'
3651 ||' msrr.SCHEDULE_FLAG,'
3652 ||' msrr.PARENT_SEQ_NUM,'
3653 ||' msrr.SETUP_ID,'
3654 ||' msrr.ORIG_RESOURCE_SEQ_NUM,'
3655 ||' msrr.GROUP_SEQUENCE_ID, ' /*ds change change start */
3656 ||' msrr.GROUP_SEQUENCE_NUMBER, '
3657 ||' msrr.BATCH_NUMBER, '
3658 ||' msrr.MAXIMUM_ASSIGNED_UNITS, '
3659 ||' msrr.MAXIMUM_CAPACITY, '
3660 ||' msrr.BREAKABLE_ACTIVITY_FLAG, '
3661 ||' msrr.STEP_QUANTITY, '
3662 ||' msrr.STEP_QUANTITY_UOM, '
3663 ||' msrr.MINIMUM_CAPACITY, ' /*ds change change end */
3664 ||' msrr.ACTUAL_START_DATE, ' /* Discrete Mfg Enahancements Bug 4479276 */
3665 ||' msrr.OPERATION_HOURS_REQUIRED, ' /* Discrete Mfg Enahancements Bug 4479276 */
3666 ||' msrr.UNADJUSTED_RESOURCE_HOURS, '
3667 ||' msrr.TOUCH_TIME, '
3668 ||' msrr.ACTIVITY_NAME, '
3669 ||' msrr.OPERATION_NAME, '
3670 ||' :v_current_date, '
3671 ||' :v_current_user, '
3672 ||' :v_current_date, '
3673 ||' :v_current_user '
3674 ||' FROM '||lv_supplies_tbl||' ms, '
3675 ||' MSC_ST_RESOURCE_REQUIREMENTS msrr, '
3676 ||' MSC_ITEM_ID_LID miil '
3677 ||' WHERE msrr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
3678 ||' AND ms.PLAN_ID= -1'
3679 ||' AND ms.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID'
3680 ||' AND ms.DISPOSITION_ID= msrr.WIP_ENTITY_ID'
3681 ||' AND ms.ORDER_TYPE IN ( 3, 7,70)' /* 70 esm suply:ds change change */
3682 ||' AND msrr.DELETED_FLAG= '||MSC_UTIL.SYS_NO
3683 ||' AND miil.SR_INVENTORY_ITEM_ID= msrr.INVENTORY_ITEM_ID'
3684 ||' AND miil.SR_INSTANCE_ID= msrr.SR_INSTANCE_ID';
3685
3686 EXECUTE IMMEDIATE lv_sql_ins
3687 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;
3688
3689 COMMIT;
3690 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'res req loaded');
3691
3692 EXCEPTION
3693 WHEN OTHERS THEN
3694 IF SQLCODE IN (-01653,-01650,-01562,-01683) THEN
3695
3696 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3697 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3698 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3699 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3700 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3701
3702 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3703 RAISE;
3704
3705 ELSE
3706 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3707 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3708 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_RES_REQ');
3709 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3710 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3711
3712 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3713
3714 --If Direct path load results in warning then the processing has to be
3715 --switched back to row by row processing. This will help to identify the
3716 --erroneous record and will also help in processing the rest of the records.
3717 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'bulk insert failed - res req');
3718 lb_refresh_failed := TRUE;
3719 END IF;
3720 END;
3721
3722 END IF; -- MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh
3723
3724
3725 IF MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed THEN
3726
3727 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3728
3729 FOR c_rec IN c4_d LOOP
3730 UPDATE MSC_RESOURCE_REQUIREMENTS
3731 SET RESOURCE_HOURS= 0,
3732 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3733 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3734 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3735 WHERE PLAN_ID= -1
3736 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
3737 AND WIP_ENTITY_ID= c_rec.WIP_ENTITY_ID
3738 AND OPERATION_SEQ_NUM= NVL( c_rec.OPERATION_SEQ_NUM, OPERATION_SEQ_NUM)
3739 AND ORIG_RESOURCE_SEQ_NUM= NVL(c_rec.ORIG_RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM);
3740 END LOOP;
3741
3742 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh
3743
3744 OPEN c4 FOR lv_cursor_stmt;
3745
3746 LOOP
3747
3748 FETCH c4 INTO
3749 lv_DEPARTMENT_ID,
3750 lv_RESOURCE_ID,
3751 lv_ASSEMBLY_ITEM_ID,
3752 lv_ORGANIZATION_ID,
3753 lv_SUPPLY_ID,
3754 lv_WIP_ENTITY_ID,
3755 lv_ROUTING_SEQUENCE_ID,
3756 lv_OPERATION_SEQ_NUM,
3757 lv_OPERATION_SEQUENCE_ID,
3758 lv_RESOURCE_SEQ_NUM,
3759 lv_START_DATE,
3760 lv_OPERATION_HOURS_REQUIRED,
3761 lv_HOURS_EXPENDED,
3762 lv_QUANTITY_IN_QUEUE,
3763 lv_QUANTITY_RUNNING,
3764 lv_QUANTITY_WAITING_TO_MOVE,
3765 lv_QUANTITY_COMPLETED,
3766 lv_YIELD,
3767 lv_USAGE_RATE,
3768 lv_BASIS_TYPE,
3769 lv_ASSIGNED_UNITS,
3770 lv_END_DATE,
3771 lv_SUPPLY_TYPE,
3772 lv_STD_OP_CODE,
3773 lv_DELETED_FLAG,
3774 lv_minimum_transfer_quantity,
3775 lv_firm_flag,
3776 lv_schedule_flag,
3777 lv_PARENT_SEQ_NUM,
3778 lv_SETUP_ID,
3779 lv_ACTIVITY_GROUP_ID,
3780 lv_ALTERNATE_NUMBER,
3781 lv_PRINCIPAL_FLAG,
3782 lv_SR_INSTANCE_ID,
3783 lv_ORIG_RESOURCE_SEQ_NUM,
3784 lv_GROUP_SEQUENCE_ID, /* ds change change start */
3785 lv_GROUP_SEQUENCE_NUMBER,
3786 lv_BATCH_NUMBER,
3787 lv_MAXIMUM_ASSIGNED_UNITS,
3788 lv_MAXIMUM_CAPACITY,
3789 lv_BREAKABLE_ACTIVITY_FLAG,
3790 lv_STEP_QUANTITY,
3791 lv_STEP_QUANTITY_UOM,
3792 lv_MINIMUM_CAPACITY, /* ds change change end */
3793 lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3794 lv_UNADJUSTED_RESOURCE_HOURS,
3795 lv_TOUCH_TIME,
3796 lv_ACTIVITY_NAME,
3797 lv_OPERATION_NAME;
3798
3799 EXIT WHEN c4%NOTFOUND;
3800
3801 BEGIN
3802
3803 IF MSC_CL_COLLECTION.v_is_legacy_refresh = TRUE THEN /* bug 3768813 */
3804 lv_legacy_refresh := 1;
3805 ELSE
3806 lv_legacy_refresh := 2;
3807 END IF;
3808
3809 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
3810
3811 UPDATE MSC_RESOURCE_REQUIREMENTS
3812 SET
3813 DEPARTMENT_ID= lv_DEPARTMENT_ID,
3814 RESOURCE_ID= lv_RESOURCE_ID,
3815 ASSEMBLY_ITEM_ID = lv_ASSEMBLY_ITEM_ID,
3816 ROUTING_SEQUENCE_ID = lv_ROUTING_SEQUENCE_ID,
3817 OPERATION_SEQUENCE_ID= lv_OPERATION_SEQUENCE_ID,
3818 START_DATE= lv_START_DATE,
3819 RESOURCE_HOURS= greatest((lv_OPERATION_HOURS_REQUIRED - nvl(lv_HOURS_EXPENDED,0)),0),
3820 HOURS_EXPENDED= lv_HOURS_EXPENDED,
3821 QUANTITY_IN_QUEUE= lv_QUANTITY_IN_QUEUE,
3822 QUANTITY_RUNNING= lv_QUANTITY_RUNNING,
3823 QUANTITY_WAITING_TO_MOVE= lv_QUANTITY_WAITING_TO_MOVE,
3824 QUANTITY_COMPLETED= lv_QUANTITY_COMPLETED,
3825 YIELD= lv_YIELD,
3826 USAGE_RATE= lv_USAGE_RATE,
3827 BASIS_TYPE= lv_BASIS_TYPE,
3828 ASSIGNED_UNITS= lv_ASSIGNED_UNITS,
3829 END_DATE= lv_END_DATE,
3830 SUPPLY_ID= lv_SUPPLY_ID,
3831 STD_OP_CODE= lv_STD_OP_CODE,
3832 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3833 minimum_transfer_quantity= lv_minimum_transfer_quantity,
3834 firm_flag = lv_firm_flag,
3835 SCHEDULE_FLAG = lv_schedule_flag,
3836 PARENT_SEQ_NUM=lv_PARENT_SEQ_NUM,
3837 SETUP_ID=lv_SETUP_ID,
3838 ACTIVITY_GROUP_ID=lv_ACTIVITY_GROUP_ID,
3839 ALTERNATE_NUM = lv_ALTERNATE_NUMBER,
3840 PRINCIPAL_FLAG = lv_PRINCIPAL_FLAG,
3841 RESOURCE_SEQ_NUM = lv_RESOURCE_SEQ_NUM,
3842 GROUP_SEQUENCE_ID = lv_GROUP_SEQUENCE_ID, /* ds change change start */
3843 GROUP_SEQUENCE_NUMBER = lv_GROUP_SEQUENCE_NUMBER,
3844 BATCH_NUMBER = lv_BATCH_NUMBER,
3845 MAXIMUM_ASSIGNED_UNITS = lv_MAXIMUM_ASSIGNED_UNITS,
3846 MAXIMUM_CAPACITY = lv_MAXIMUM_CAPACITY,
3847 BREAKABLE_ACTIVITY_FLAG = lv_BREAKABLE_ACTIVITY_FLAG,
3848 STEP_QUANTITY = lv_STEP_QUANTITY,
3849 STEP_QUANTITY_UOM = lv_STEP_QUANTITY_UOM,
3850 MINIMUM_CAPACITY = lv_MINIMUM_CAPACITY, /* ds change change end */
3851 ACTUAL_START_DATE = lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3852 TOTAL_RESOURCE_HOURS = lv_OPERATION_HOURS_REQUIRED, /* Discrete Mfg Enahancements Bug 4479276 */
3853 UNADJUSTED_RESOURCE_HOURS = lv_UNADJUSTED_RESOURCE_HOURS,
3854 TOUCH_TIME = lv_TOUCH_TIME,
3855 ACTIVITY_NAME=lv_ACTIVITY_NAME,
3856 OPERATION_NAME=lv_OPERATION_NAME,
3857 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3858 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3859 WHERE PLAN_ID= -1
3860 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
3861 AND NVL(ORIG_RESOURCE_SEQ_NUM, RESOURCE_SEQ_NUM) = NVL(lv_ORIG_RESOURCE_SEQ_NUM, lv_RESOURCE_SEQ_NUM)
3862 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
3863 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
3864 AND OPERATION_SEQ_NUM= lv_OPERATION_SEQ_NUM
3865 AND decode(lv_legacy_refresh,1,resource_id,-1) = decode(lv_legacy_refresh,1,lv_RESOURCE_ID, -1); /* bug 3768813 */
3866
3867 END IF;
3868
3869 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
3870
3871 EXECUTE IMMEDIATE lv_sql_stmt
3872 USING
3873 lv_DEPARTMENT_ID,
3874 lv_RESOURCE_ID,
3875 lv_ORGANIZATION_ID,
3876 lv_ASSEMBLY_ITEM_ID,
3877 lv_SUPPLY_ID,
3878 lv_WIP_ENTITY_ID,
3879 lv_ROUTING_SEQUENCE_ID,
3880 lv_SUPPLY_TYPE,
3881 lv_OPERATION_SEQ_NUM,
3882 lv_OPERATION_SEQUENCE_ID,
3883 lv_RESOURCE_SEQ_NUM,
3884 lv_START_DATE,
3885 greatest((lv_OPERATION_HOURS_REQUIRED - NVL(lv_HOURS_EXPENDED,0)),0),
3886 lv_HOURS_EXPENDED,
3887 lv_QUANTITY_IN_QUEUE,
3888 lv_QUANTITY_RUNNING,
3889 lv_QUANTITY_WAITING_TO_MOVE,
3890 lv_QUANTITY_COMPLETED,
3891 lv_YIELD,
3892 lv_USAGE_RATE,
3893 lv_BASIS_TYPE,
3894 lv_ASSIGNED_UNITS,
3895 lv_END_DATE,
3896 lv_STD_OP_CODE,
3897 lv_ACTIVITY_GROUP_ID,
3898 lv_ALTERNATE_NUMBER,
3899 lv_PRINCIPAL_FLAG,
3900 lv_SR_INSTANCE_ID,
3901 MSC_CL_COLLECTION.v_last_collection_id,
3902 lv_minimum_transfer_quantity,
3903 lv_firm_flag,
3904 lv_schedule_flag,
3905 lv_PARENT_SEQ_NUM,
3906 lv_SETUP_ID,
3907 lv_ORIG_RESOURCE_SEQ_NUM,
3908 lv_GROUP_SEQUENCE_ID, /* ds change change start */
3909 lv_GROUP_SEQUENCE_NUMBER,
3910 lv_BATCH_NUMBER,
3911 lv_MAXIMUM_ASSIGNED_UNITS,
3912 lv_MAXIMUM_CAPACITY,
3913 lv_BREAKABLE_ACTIVITY_FLAG,
3914 lv_STEP_QUANTITY,
3915 lv_STEP_QUANTITY_UOM,
3916 lv_MINIMUM_CAPACITY, /* ds change change end */
3917 lv_ACTUAL_START_DATE, /* Discrete Mfg Enahancements Bug 4479276 */
3918 lv_OPERATION_HOURS_REQUIRED, /* Discrete Mfg Enahancements Bug 4479276 */
3919 lv_UNADJUSTED_RESOURCE_HOURS,
3920 lv_TOUCH_TIME,
3921 lv_ACTIVITY_NAME,
3922 lv_OPERATION_NAME,
3923 MSC_CL_COLLECTION.v_current_date,
3924 MSC_CL_COLLECTION.v_current_user,
3925 MSC_CL_COLLECTION.v_current_date,
3926 MSC_CL_COLLECTION.v_current_user;
3927
3928 END IF;
3929
3930 c_count:= c_count+1;
3931
3932 IF c_count> MSC_CL_COLLECTION.PBS THEN
3933 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
3934 c_count:= 0;
3935 END IF;
3936
3937 EXCEPTION
3938 WHEN OTHERS THEN
3939
3940 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3941
3942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3943 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3944 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3945 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3947
3948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3949 RAISE;
3950
3951 ELSE
3952
3953 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3954
3955 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3956 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3957 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
3958 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESOURCE_REQUIREMENTS');
3959 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3960
3961 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3962 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3963 FND_MESSAGE.SET_TOKEN('VALUE',
3964 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
3965 MSC_CL_COLLECTION.v_instance_id));
3966 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3967
3968 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3969 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEPARTMENT_ID');
3970 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEPARTMENT_ID));
3971 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3972
3973 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3974 FND_MESSAGE.SET_TOKEN('COLUMN', 'RESOURCE_ID');
3975 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_RESOURCE_ID));
3976 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3977
3978 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3979 END IF;
3980
3981 END;
3982
3983 END LOOP;
3984
3985 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh OR lb_refresh_failed
3986
3987 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3988 COMMIT;
3989 END IF;
3990
3991 BEGIN
3992
3993 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
3994
3995 lv_tbl:= 'RESOURCE_REQUIREMENTS_'||MSC_CL_COLLECTION.v_instance_code;
3996
3997 lv_sql_stmt:=
3998 'INSERT INTO '||lv_tbl
3999 ||' SELECT * from MSC_RESOURCE_REQUIREMENTS'
4000 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
4001 ||' AND plan_id = -1 '
4002 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
4003
4004 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
4005 EXECUTE IMMEDIATE lv_sql_stmt;
4006
4007 COMMIT;
4008
4009 END IF;
4010
4011 EXCEPTION
4012 WHEN OTHERS THEN
4013
4014 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4015 RAISE;
4016 END;
4017
4018 MSC_CL_BOM_ODS_LOAD.LOAD_RESOURCE_CHARGES; /* ds change */
4019
4020 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
4021 MSC_CL_COLLECTION.alter_temp_table (lv_errbuf,
4022 lv_retcode,
4023 'MSC_RESOURCE_REQUIREMENTS',
4024 MSC_CL_COLLECTION.v_instance_code,
4025 MSC_UTIL.G_WARNING
4026 );
4027
4028 IF lv_retcode = MSC_UTIL.G_ERROR THEN
4029 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_errbuf);
4030 RAISE MSC_CL_COLLECTION.ALTER_TEMP_TABLE_ERROR;
4031 ELSIF lv_retcode = MSC_UTIL.G_WARNING THEN
4032 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
4033 END IF;
4034
4035 END IF;
4036
4037 EXCEPTION
4038 WHEN OTHERS THEN
4039 IF c4%ISOPEN THEN CLOSE c4; END IF;
4040
4041 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_RES_REQ>>');
4042 IF lv_cursor_stmt IS NOT NULL THEN
4043 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
4044 END IF;
4045 IF lv_sql_stmt IS NOT NULL THEN
4046 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
4047 END IF;
4048 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
4049 RAISE;
4050 END LOAD_RES_REQ;
4051
4052 --=============================================================================
4053
4054 END MSC_CL_WIP_ODS_LOAD;