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