[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_ROUTING_PULL
Source
1 PACKAGE BODY MSC_CL_ROUTING_PULL AS -- body
2 /* $Header:*/
3
4
5
6 v_union_sql varchar2(32767);
7 v_temp_tp_sql VARCHAR2(100);
8 v_sql_stmt VARCHAR2(32767);
9 v_temp_sql VARCHAR2(15000);
10 v_temp_sql1 VARCHAR2(1000);
11 v_temp_sql2 VARCHAR2(1000);
12 v_temp_sql3 VARCHAR2(1000);
13 v_temp_sql4 VARCHAR2(1000);
14 v_osfm_Sql varchar2(1000);
15
16 -- NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
17 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
18
19
20 --===============================================
21
22
23 PROCEDURE LOAD_ROUTING IS
24 v_get_apps_ver number;
25 BEGIN
26
27 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
28
29 --=================== Net Change Mode: Delete ==================
30 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
31
32 MSC_CL_PULL.v_table_name:= 'MSC_ST_ROUTINGS';
33 MSC_CL_PULL.v_view_name := 'MRP_AD_ROUTINGS_V';
34
35 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
36 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
37 ELSE
38 v_temp_sql := NULL;
39 END IF;
40
41 v_sql_stmt:=
42 ' INSERT INTO MSC_ST_ROUTINGS'
43 ||' ( ROUTING_SEQUENCE_ID,'
44 ||' DELETED_FLAG,'
45 ||' REFRESH_ID,'
46 ||' SR_INSTANCE_ID)'
47 ||' SELECT '
48 ||' x.ROUTING_SEQUENCE_ID,'
49 ||' 1,'
50 ||' :v_refresh_id,'
51 ||' :v_instance_id'
52 ||' FROM MRP_AD_ROUTINGS_V'||MSC_CL_PULL.v_dblink||' x'
53 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
54 || v_temp_sql;
55
56 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
57
58 COMMIT;
59
60 END IF;
61
62 MSC_CL_PULL.v_table_name:= 'MSC_ST_ROUTINGS';
63 MSC_CL_PULL.v_view_name := 'MRP_AP_ROUTINGS_V';
64
65
66
67 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
68 v_osfm_sql:= 'x.first_op_seq_num,x.last_op_seq_num,';
69 ELSE
70 v_osfm_sql:= 'null,null,';
71 END IF ;
72
73 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
74 v_union_sql :=
75 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn||')'
76 ||' UNION '
77 ||' SELECT '
78 ||' x.ROUTING_SEQUENCE_ID,'
79 ||' x.ASSEMBLY_ITEM_ID,'
80 ||' x.ROUTING_TYPE,'
81 || v_osfm_sql
82 ||' x.ROUTING_COMMENT,'
83 ||' x.PRIORITY,'
84 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
85 ||' x.PROJECT_ID,'
86 ||' x.TASK_ID, '
87 ||' x.LINE_ID,'
88 ||' x.COMPLETION_SUBINVENTORY,'
89 ||' x.COMPLETION_LOCATOR_ID,'
90 ||' x.COMMON_ROUTING_SEQUENCE_ID,'
91 ||' nvl(x.CFM_ROUTING_FLAG,2), '
92 ||' x.MIXED_MODEL_MAP_FLAG, '
93 ||' x.TOTAL_PRODUCT_CYCLE_TIME,'
94 ||' x.CTP_FLAG,'
95 ||' x.ORGANIZATION_ID,'
96 ||' 2,'
97 ||' :v_refresh_id,'
98 ||' :v_instance_id'
99 ||' FROM MRP_AP_ROUTINGS_V'||MSC_CL_PULL.v_dblink||' x'
100 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
101 ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
102 /* NCP
103 ||' UNION '
104 ||' SELECT '
105 ||' x.ROUTING_SEQUENCE_ID,'
106 ||' x.ASSEMBLY_ITEM_ID,'
107 ||' x.ROUTING_TYPE,'
108 || v_osfm_sql
109 ||' x.ROUTING_COMMENT, '
110 ||' x.PRIORITY,'
111 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
112 ||' x.PROJECT_ID,'
113 ||' x.TASK_ID, '
114 ||' x.LINE_ID,'
115 ||' x.COMPLETION_SUBINVENTORY,'
116 ||' x.COMPLETION_LOCATOR_ID,'
117 ||' x.COMMON_ROUTING_SEQUENCE_ID,'
118 ||' nvl(x.CFM_ROUTING_FLAG,2), '
119 ||' x.MIXED_MODEL_MAP_FLAG, '
120 ||' x.TOTAL_PRODUCT_CYCLE_TIME,'
121 ||' x.CTP_FLAG,'
122 ||' x.ORGANIZATION_ID,'
123 ||' 2,'
124 ||' :v_refresh_id,'
125 ||' :v_instance_id'
126 ||' FROM MRP_AP_ROUTINGS_V'||MSC_CL_PULL.v_dblink||' x'
127 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
128 ||' AND (x.RN3>'||MSC_CL_PULL.v_lrn||')' ;
129 */
130 ELSE
131 v_union_sql :=
132 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
133 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
134 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
135 END IF;
136
137 v_sql_stmt:=
138 ' INSERT INTO MSC_ST_ROUTINGS'
139 ||' ( ROUTING_SEQUENCE_ID,'
140 ||' ASSEMBLY_ITEM_ID,'
141 ||' ROUTING_TYPE,'
142 ||' FIRST_OP_SEQ_NUM,'
143 ||' LAST_OP_SEQ_NUM,'
144 ||' ROUTING_COMMENT,'
145 ||' PRIORITY,'
146 ||' ALTERNATE_ROUTING_DESIGNATOR,'
147 ||' PROJECT_ID,'
148 ||' TASK_ID, '
149 ||' LINE_ID,'
150 ||' COMPLETION_SUBINVENTORY,'
151 ||' COMPLETION_LOCATOR_ID,'
152 ||' COMMON_ROUTING_SEQUENCE_ID,'
153 ||' CFM_ROUTING_FLAG, '
154 ||' MIXED_MODEL_MAP_FLAG, '
155 ||' TOTAL_PRODUCT_CYCLE_TIME,'
156 ||' CTP_FLAG,'
157 ||' ORGANIZATION_ID,'
158 ||' DELETED_FLAG,'
159 ||' REFRESH_ID,'
160 ||' SR_INSTANCE_ID)'
161 ||' SELECT '
162 ||' x.ROUTING_SEQUENCE_ID,'
163 ||' x.ASSEMBLY_ITEM_ID,'
164 ||' x.ROUTING_TYPE,'
165 || v_osfm_sql
166 ||' x.ROUTING_COMMENT,'
167 ||' x.PRIORITY,'
168 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
169 ||' x.PROJECT_ID,'
170 ||' x.TASK_ID, '
171 ||' x.LINE_ID,'
172 ||' x.COMPLETION_SUBINVENTORY,'
173 ||' x.COMPLETION_LOCATOR_ID,'
174 ||' x.COMMON_ROUTING_SEQUENCE_ID,'
175 ||' nvl(x.CFM_ROUTING_FLAG,2), '
176 ||' x.MIXED_MODEL_MAP_FLAG, '
177 ||' x.TOTAL_PRODUCT_CYCLE_TIME,'
178 ||' x.CTP_FLAG,'
179 ||' x.ORGANIZATION_ID,'
180 ||' 2,'
181 ||' :v_refresh_id,'
182 ||' :v_instance_id'
183 ||' FROM MRP_AP_ROUTINGS_V'||MSC_CL_PULL.v_dblink||' x'
184 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
185 || v_union_sql ;
186
187 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
188
189 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
190 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
191
192 /* MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
193 */
194
195 ELSE
196
197
198 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
199
200
201 END IF;
202
203 COMMIT;
204
205
206 END IF; -- MSC_CL_PULL.BOM_ENABLED
207
208 END LOAD_ROUTING;
209
210
211 PROCEDURE LOAD_ROUTING_OPERATIONS IS
212 v_get_apps_ver number;
213 BEGIN
214
215 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
216 --=================== Net Change Mode: Delete ==================
217 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
218
219 MSC_CL_PULL.v_table_name:= 'MSC_ST_ROUTING_OPERATIONS';
220 MSC_CL_PULL.v_view_name := 'MRP_AD_ROUTING_OPERATIONS_V';
221
222 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
223 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
224 ELSE
225 v_temp_sql := NULL;
226 END IF;
227
228 v_sql_stmt:=
229 ' INSERT INTO MSC_ST_ROUTING_OPERATIONS'
230 ||' ( OPERATION_SEQUENCE_ID, '
231 ||' ROUTING_SEQUENCE_ID, '
232 ||' DELETED_FLAG,'
233 ||' REFRESH_ID,'
234 ||' SR_INSTANCE_ID)'
235 ||' SELECT '
236 ||' x.OPERATION_SEQUENCE_ID, '
237 ||' x.ROUTING_SEQUENCE_ID, '
238 ||' 1,'
239 ||' :v_refresh_id,'
240 ||' :v_instance_id'
241 ||' FROM MRP_AD_ROUTING_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
242 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
243 || v_temp_sql;
244
245 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
246
247 COMMIT;
248
249 END IF;
250
251 MSC_CL_PULL.v_table_name:= 'MSC_ST_ROUTING_OPERATIONS';
252 MSC_CL_PULL.v_view_name := 'MRP_AP_ROUTING_OPERATIONS_V';
253 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
254 v_union_sql :=
255 ' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
256 ||' UNION '
257 ||' SELECT '
258 ||' x.OPERATION_SEQUENCE_ID, '
259 ||' x.ROUTING_SEQUENCE_ID, '
260 ||' x.OPERATION_SEQ_NUM, '
261 ||' x.OPERATION_DESCRIPTION, '
262 ||' x.EFFECTIVITY_DATE- :v_dgmt, '
263 ||' x.DISABLE_DATE- :v_dgmt, '
264 ||' x.OPTION_DEPENDENT_FLAG, '
265 ||' x.OPERATION_TYPE, '
266 ||' x.MINIMUM_TRANSFER_QUANTITY, '
267 ||' x.YIELD, '
268 ||' x.DEPARTMENT_ID, '
269 ||' x.OPERATION_LEAD_TIME_PERCENT, '
270 ||' x.CUMULATIVE_YIELD, '
271 ||' x.REVERSE_CUMULATIVE_YIELD,'
272 ||' x.NET_PLANNING_PERCENT,'
273 ||' x.ORGANIZATION_ID, '
274 ||' x.DEPARTMENT_CODE,'
275 ||' x.STANDARD_OPERATION_CODE,'
276 ||' 2,'
277 ||' :v_refresh_id,'
278 ||' :v_instance_id'
279 ||' FROM MRP_AP_ROUTING_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
280 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
281 ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
282 ||' UNION '
283 ||' SELECT '
284 ||' x.OPERATION_SEQUENCE_ID, '
285 ||' x.ROUTING_SEQUENCE_ID, '
286 ||' x.OPERATION_SEQ_NUM, '
287 ||' x.OPERATION_DESCRIPTION, '
288 ||' x.EFFECTIVITY_DATE- :v_dgmt, '
289 ||' x.DISABLE_DATE- :v_dgmt, '
290 ||' x.OPTION_DEPENDENT_FLAG, '
291 ||' x.OPERATION_TYPE, '
292 ||' x.MINIMUM_TRANSFER_QUANTITY, '
293 ||' x.YIELD, '
294 ||' x.DEPARTMENT_ID, '
295 ||' x.OPERATION_LEAD_TIME_PERCENT, '
296 ||' x.CUMULATIVE_YIELD, '
297 ||' x.REVERSE_CUMULATIVE_YIELD,'
298 ||' x.NET_PLANNING_PERCENT,'
299 ||' x.ORGANIZATION_ID, '
300 ||' x.DEPARTMENT_CODE,'
301 ||' x.STANDARD_OPERATION_CODE,'
302 ||' 2,'
303 ||' :v_refresh_id,'
304 ||' :v_instance_id'
305 ||' FROM MRP_AP_ROUTING_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
306 ||' WHERE x.ITEM_ORG'||MSC_UTIL.v_in_org_str
307 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')';
308 /*
309 ||' UNION '
310 ||' SELECT '
311 ||' x.OPERATION_SEQUENCE_ID, '
312 ||' x.ROUTING_SEQUENCE_ID, '
313 ||' x.OPERATION_SEQ_NUM, '
314 ||' x.OPERATION_DESCRIPTION, '
315 ||' x.EFFECTIVITY_DATE- :v_dgmt, '
316 ||' x.DISABLE_DATE- :v_dgmt, '
317 ||' x.OPTION_DEPENDENT_FLAG, '
318 ||' x.OPERATION_TYPE, '
319 ||' x.MINIMUM_TRANSFER_QUANTITY, '
320 ||' x.YIELD, '
321 ||' x.DEPARTMENT_ID, '
322 ||' x.OPERATION_LEAD_TIME_PERCENT, '
323 ||' x.CUMULATIVE_YIELD, '
324 ||' x.REVERSE_CUMULATIVE_YIELD,'
325 ||' x.NET_PLANNING_PERCENT,'
326 ||' x.ORGANIZATION_ID, '
327 ||' x.DEPARTMENT_CODE,'
328 ||' x.STANDARD_OPERATION_CODE,'
329 ||' 2,'
330 ||' :v_refresh_id,'
331 ||' :v_instance_id'
332 ||' FROM MRP_AP_ROUTING_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
333 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
334 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')' ; */
335 ELSE
336 v_union_sql :=
337 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
338 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
339 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
340 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
341 END IF;
342
343 v_sql_stmt:=
344 ' INSERT INTO MSC_ST_ROUTING_OPERATIONS'
345 ||' ( OPERATION_SEQUENCE_ID, '
346 ||' ROUTING_SEQUENCE_ID, '
347 ||' OPERATION_SEQ_NUM, '
348 ||' OPERATION_DESCRIPTION, '
349 ||' EFFECTIVITY_DATE, '
350 ||' DISABLE_DATE, '
351 ||' OPTION_DEPENDENT_FLAG, '
352 ||' OPERATION_TYPE, '
353 ||' MINIMUM_TRANSFER_QUANTITY, '
354 ||' YIELD, '
355 ||' DEPARTMENT_ID, '
356 ||' OPERATION_LEAD_TIME_PERCENT, '
357 ||' CUMULATIVE_YIELD, '
358 ||' REVERSE_CUMULATIVE_YIELD,'
359 ||' NET_PLANNING_PERCENT,'
360 ||' ORGANIZATION_ID, '
361 ||' DEPARTMENT_CODE,'
362 ||' STANDARD_OPERATION_CODE,'
363 ||' DELETED_FLAG,'
364 ||' REFRESH_ID,'
365 ||' SR_INSTANCE_ID)'
366 ||' SELECT '
367 ||' x.OPERATION_SEQUENCE_ID, '
368 ||' x.ROUTING_SEQUENCE_ID, '
369 ||' x.OPERATION_SEQ_NUM, '
370 ||' x.OPERATION_DESCRIPTION, '
371 ||' x.EFFECTIVITY_DATE- :v_dgmt, '
372 ||' x.DISABLE_DATE- :v_dgmt, '
373 ||' x.OPTION_DEPENDENT_FLAG, '
374 ||' x.OPERATION_TYPE, '
375 ||' x.MINIMUM_TRANSFER_QUANTITY, '
376 ||' x.YIELD, '
377 ||' x.DEPARTMENT_ID, '
378 ||' x.OPERATION_LEAD_TIME_PERCENT, '
379 ||' x.CUMULATIVE_YIELD, '
380 ||' x.REVERSE_CUMULATIVE_YIELD,'
381 ||' x.NET_PLANNING_PERCENT,'
382 ||' x.ORGANIZATION_ID, '
383 ||' x.DEPARTMENT_CODE,'
384 ||' x.STANDARD_OPERATION_CODE,'
385 ||' 2,'
386 ||' :v_refresh_id,'
387 ||' :v_instance_id'
388 ||' FROM MRP_AP_ROUTING_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
389 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
390 || v_union_sql ;
391
392 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
393
394 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
395 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
396 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
397 /*
398 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399 */
400
401 ELSE
402
403
404 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
405
406
407 END IF;
408
409 COMMIT;
410
411 END IF; -- MSC_CL_PULL.BOM_ENABLED
412
413 END LOAD_ROUTING_OPERATIONS;
414 PROCEDURE LOAD_OPERATION_RES_SEQS IS
415 v_get_apps_ver number;
416 BEGIN
417
418 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
419 BEGIN
420 SELECT APPS_VER
421 INTO v_get_apps_ver
422 FROM MSC_APPS_INSTANCES
423 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
424 END;
425 --=================== Net Change Mode: Delete ==================
426 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
427
428 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCE_SEQS';
429 MSC_CL_PULL.v_view_name := 'MRP_AD_OP_RESOURCE_SEQS_V';
430
431 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
432 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
433 ELSE
434 v_temp_sql := NULL;
435 END IF;
436
437 v_sql_stmt:=
438 ' INSERT INTO MSC_ST_OPERATION_RESOURCE_SEQS'
439 ||' ( ROUTING_SEQUENCE_ID,'
440 ||' OPERATION_SEQUENCE_ID,'
441 ||' RESOURCE_SEQ_NUM,'
442 ||' DELETED_FLAG,'
443 ||' REFRESH_ID,'
444 ||' SR_INSTANCE_ID)'
445 ||' SELECT'
446 ||' x.ROUTING_SEQUENCE_ID,'
447 ||' x.OPERATION_SEQUENCE_ID,'
448 ||' x.RESOURCE_SEQ_NUM,'
449 ||' 1,'
450 ||' :v_refresh_id,'
451 ||' :v_instance_id'
452 ||' FROM MRP_AD_OP_RESOURCE_SEQS_V'||MSC_CL_PULL.v_dblink||' x'
453 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
454 || v_temp_sql;
455
456 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
457
458 COMMIT;
459
460 END IF;
461
462 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCE_SEQS';
463 MSC_CL_PULL.v_view_name := 'MRP_AP_OP_RESOURCE_SEQS_V';
464
465 Begin
466
467 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
468 v_temp_sql:= ' x.net_planning_percent,';
469 ELSE
470 v_temp_sql:='NULL,' ;
471 END IF ;
472
473 End;
474
475 Begin
476
477
478 IF MSC_CL_PULL.v_apps_ver=MSC_UTIL.G_APPS107 THEN
479 v_temp_sql1:= 'NULL , ' ;
480 ELSE
481 v_temp_sql1:= ' x.activity_group_id, ';
482 END IF ;
483
484 End;
485
486 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
487 v_union_sql :=
488 ' AND (x.RN2>'||MSC_CL_PULL.v_lrn
489 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
490 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
491 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
492 ELSE
493 v_union_sql :=
494 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
495 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
496 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
497 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
498 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
499 END IF;
500
501 v_sql_stmt:=
502 ' INSERT INTO MSC_ST_OPERATION_RESOURCE_SEQS'
503 ||' ( ROUTING_SEQUENCE_ID,'
504 ||' OPERATION_SEQUENCE_ID,'
505 ||' RESOURCE_SEQ_NUM,'
506 ||' CUMMULATIVE_PCT,'
507 ||' SCHEDULE_FLAG,'
508 ||' RESOURCE_OFFSET_PERCENT,'
509 ||' DEPARTMENT_ID,'
510 ||' ACTIVITY_GROUP_ID,'
511 ||' DELETED_FLAG,'
512 ||' REFRESH_ID,'
513 ||' SR_INSTANCE_ID,'
514 ||' ORGANIZATION_ID)'
515 ||' SELECT ' -- due to alternate resource...
516 ||' x.ROUTING_SEQUENCE_ID,'
517 ||' x.OPERATION_SEQUENCE_ID,'
518 ||' decode(:v_get_apps_ver,3,x.resource_seq_num,4,x.resource_seq_num, '
519 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
520 ||' 1, x.Attribute1,'
521 ||' 2, x.Attribute2,'
522 ||' 3, x.Attribute3,'
523 ||' 4, x.Attribute4,'
524 ||' 5, x.Attribute5,'
525 ||' 6, x.Attribute6,'
526 ||' 7, x.Attribute7,'
527 ||' 8, x.Attribute8,'
528 ||' 9, x.Attribute9,'
529 ||' 10, x.Attribute10,'
530 ||' 11, x.Attribute11,'
531 ||' 12, x.Attribute12,'
532 ||' 13, x.Attribute13,'
533 ||' 14, x.Attribute14,'
534 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM)),'
535 || v_temp_sql
536 ||' x.SCHEDULE_FLAG,'
537 ||' x.RESOURCE_OFFSET_PERCENT,'
538 ||' x.DEPARTMENT_ID,'
539 || v_temp_sql1
540 ||' 2,'
541 ||' :v_refresh_id,'
542 ||' :v_instance_id,'
543 ||' x.ORGANIZATION_ID'
544 ||' FROM MRP_AP_OP_RESOURCE_SEQS_V'||MSC_CL_PULL.v_dblink||' x'
545 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
546 || v_union_sql ;
547
548 --IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
549
550 EXECUTE IMMEDIATE v_sql_stmt
551 USING v_get_apps_ver,
552 MSC_CL_PULL.v_msc_simul_res_seq,
553 MSC_CL_PULL.v_refresh_id,
554 MSC_CL_PULL.v_instance_id;
555
556
557 --ELSE
558
559
560
561 --END IF;
562
563 COMMIT;
564
565 END IF; -- MSC_CL_PULL.BOM_ENABLED
566
567 END LOAD_OPERATION_RES_SEQS;
568 PROCEDURE LOAD_OPERATION_RESOURCES IS
569 v_get_apps_ver number;
570 BEGIN
571
572 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
573 BEGIN
574 SELECT APPS_VER
575 INTO V_GET_APPS_VER
576 FROM MSC_APPS_INSTANCES
577 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
578 END;
579 --=================== Net Change Mode: Delete ==================
580 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
581
582 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
583 MSC_CL_PULL.v_view_name := 'MRP_AD_OPERATION_RESOURCES_V';
584 BEGIN
585 SELECT APPS_VER
586 INTO V_GET_APPS_VER
587 FROM MSC_APPS_INSTANCES
588 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
589 END;
590
591 if v_get_apps_ver >= 3
592 then
593 v_sql_stmt:=
594 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
595 ||' ( ROUTING_SEQUENCE_ID,'
596 ||' OPERATION_SEQUENCE_ID,'
597 ||' RESOURCE_SEQ_NUM,'
598 ||' RESOURCE_ID,'
599 ||' ALTERNATE_NUMBER,'
600 ||' DELETED_FLAG,'
601 ||' REFRESH_ID,'
602 ||' SR_INSTANCE_ID)'
603 ||' SELECT'
604 ||' x.ROUTING_SEQUENCE_ID,'
605 ||' x.OPERATION_SEQUENCE_ID,'
606 ||' x.RESOURCE_SEQ_NUM,'
607 ||' x.RESOURCE_ID,'
608 ||' x.ALTERNATE_NUMBER,' -- **
609 ||' 1,'
610 ||' :v_refresh_id,'
611 ||' :v_instance_id'
612 ||' FROM MRP_AD_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
613 ||' WHERE x.RN > '||MSC_CL_PULL.v_lrn
614 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
615
616 else
617
618
619 v_sql_stmt:=
620 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
621 ||' ( ROUTING_SEQUENCE_ID,'
622 ||' OPERATION_SEQUENCE_ID,'
623 ||' RESOURCE_SEQ_NUM,'
624 ||' RESOURCE_ID,'
625 ||' ALTERNATE_NUMBER,'
626 ||' DELETED_FLAG,'
627 ||' REFRESH_ID,'
628 ||' SR_INSTANCE_ID)'
629 ||' SELECT'
630 ||' x.ROUTING_SEQUENCE_ID,'
631 ||' x.OPERATION_SEQUENCE_ID,'
632 ||' x.RESOURCE_SEQ_NUM,'
633 ||' x.RESOURCE_ID,'
634 ||' x.ALTERNATE_NUMBER,' -- **
635 ||' 1,'
636 ||' :v_refresh_id,'
637 ||' :v_instance_id'
638 ||' FROM MRP_AD_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
639 ||' WHERE x.RN > '||MSC_CL_PULL.v_lrn;
640
641 end if;
642
643 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
644
645 COMMIT;
646
647
648 BEGIN
649 SELECT APPS_VER
650 INTO V_GET_APPS_VER
651 FROM MSC_APPS_INSTANCES
652 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
653 END;
654
655 if V_GET_APPS_VER >= 3
656 then
657 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
658 MSC_CL_PULL.v_view_name := 'MRP_AD_SUB_OPER_RESS_V';
659
660 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
661 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
662 ELSE
663 v_temp_sql := NULL;
664 END IF;
665
666 v_sql_stmt:=
667 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
668 ||' ( ROUTING_SEQUENCE_ID,'
669 ||' OPERATION_SEQUENCE_ID,'
670 ||' RESOURCE_SEQ_NUM,'
671 ||' RESOURCE_ID,'
672 ||' ALTERNATE_NUMBER,'
673 ||' DELETED_FLAG,'
674 ||' REFRESH_ID,'
675 ||' SR_INSTANCE_ID)'
676 ||' SELECT'
677 ||' x.ROUTING_SEQUENCE_ID,'
678 ||' x.OPERATION_SEQUENCE_ID,'
679 ||' x.RESOURCE_SEQ_NUM,'
680 ||' x.RESOURCE_ID,'
681 ||' x.ALTERNATE_NUMBER,' -- **
682 ||' 1,'
683 ||' :v_refresh_id,'
684 ||' :v_instance_id'
685 ||' FROM MRP_AD_SUB_OPER_RESS_V'||MSC_CL_PULL.v_dblink||' x'
686 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
687 || v_temp_sql;
688
689 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
690
691 COMMIT;
692
693 END IF;
694
695 END IF;
696
697 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
698 MSC_CL_PULL.v_view_name := 'MRP_AP_OPERATION_RESOURCES_V';
699
700 BEGIN
701 SELECT APPS_VER
702 INTO V_GET_APPS_VER
703 FROM MSC_APPS_INSTANCES
704 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
705 END;
706
707 if v_get_apps_ver >= 3
708 then
709
710 v_sql_stmt:=
711 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
712 ||' ( ROUTING_SEQUENCE_ID,'
713 ||' OPERATION_SEQUENCE_ID,'
714 ||' RESOURCE_SEQ_NUM,'
715 ||' RESOURCE_ID,'
716 ||' RESOURCE_USAGE,'
717 ||' BASIS_TYPE,'
718 ||' MAX_RESOURCE_UNITS,'
719 ||' RESOURCE_UNITS,'
720 ||' UOM_CODE,'
721 ||' RESOURCE_TYPE,'
722 ||' ALTERNATE_NUMBER,'
723 ||' PRINCIPAL_FLAG,'
724 ||' DELETED_FLAG,'
725 ||' REFRESH_ID,'
726 ||' SR_INSTANCE_ID,'
727 ||' ORGANIZATION_ID ,'
728 ||' SETUP_ID ,'
729 ||' orig_resource_seq_num )'
730 ||' SELECT'
731 ||' x.ROUTING_SEQUENCE_ID,'
732 ||' x.OPERATION_SEQUENCE_ID,'
733 ||' decode(:v_get_apps_ver,3,x.schedule_seq_num,4,x.schedule_seq_num,5,x.schedule_seq_num,NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
734 ||' 1, x.Attribute1,'
735 ||' 2, x.Attribute2,'
736 ||' 3, x.Attribute3,'
737 ||' 4, x.Attribute4,'
738 ||' 5, x.Attribute5,'
739 ||' 6, x.Attribute6,'
740 ||' 7, x.Attribute7,'
741 ||' 8, x.Attribute8,'
742 ||' 9, x.Attribute9,'
743 ||' 10, x.Attribute10,'
744 ||' 11, x.Attribute11,'
745 ||' 12, x.Attribute12,'
746 ||' 13, x.Attribute13,'
747 ||' 14, x.Attribute14,'
748 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM)),'
749 ||' x.RESOURCE_ID,'
750 ||' x.RESOURCE_USAGE,'
751 ||' x.BASIS_TYPE,'
752 ||' x.MAX_RESOURCE_UNITS,'
753 ||' x.RESOURCE_UNITS,'
754 ||' x.UOM_CODE,'
755 ||' x.RESOURCE_TYPE,'
756 /* obsolete
757 ||' DECODE(DECODE( :v_msc_alt_op_res,'
758 ||' 1, x.Attribute1,'
759 ||' 2, x.Attribute2,'
760 ||' 3, x.Attribute3,'
761 ||' 4, x.Attribute4,'
762 ||' 5, x.Attribute5,'
763 ||' 6, x.Attribute6,'
764 ||' 7, x.Attribute7,'
765 ||' 8, x.Attribute8,'
766 ||' 9, x.Attribute9,'
767 ||' 10, x.Attribute10,'
768 ||' 11, x.Attribute11,'
769 ||' 12, x.Attribute12,'
770 ||' 13, x.Attribute13,'
771 ||' 14, x.Attribute14,'
772 ||' 15, x.Attribute15),'
773 ||' ''alternate'',1,'
774 ||' ''aux1'',2,'
775 ||' ''aux2'',3,'
776 ||' ''machine'',4,'
777 ||' ''operators'',5 ),' obsolete*/
778 ||' decode(:v_get_apps_ver,3,nvl(x.alternate_number,0),4,nvl(x.alternate_number,0),
779 5,nvl(x.alternate_number,0),NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
780 ||' 1, x.Attribute1,'
781 ||' 2, x.Attribute2,'
782 ||' 3, x.Attribute3,'
783 ||' 4, x.Attribute4,'
784 ||' 5, x.Attribute5,'
785 ||' 6, x.Attribute6,'
786 ||' 7, x.Attribute7,'
787 ||' 8, x.Attribute8,'
788 ||' 9, x.Attribute9,'
789 ||' 10, x.Attribute10,'
790 ||' 11, x.Attribute11,'
791 ||' 12, x.Attribute12,'
792 ||' 13, x.Attribute13,'
793 ||' 14, x.Attribute14,'
794 ||' 15, x.Attribute15)),0)),'
795 ||' x.PRINCIPAL_FLAG,' -- **
796 ||' 2,'
797 ||' :v_refresh_id,'
798 ||' :v_instance_id,'
799 ||' x.ORGANIZATION_ID,'
800 ||' x.SETUP_ID,'
801 ||' x.resource_seq_num'
802 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
803 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
804 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
805 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
806 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
807 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
808 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
809
810
811 else
812
813 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
814 v_union_sql :=
815 ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
816 /*
817 ||' UNION '
818 ||' SELECT'
819 ||' x.ROUTING_SEQUENCE_ID,'
820 ||' x.OPERATION_SEQUENCE_ID,'
821 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
822 ||' 1, x.Attribute1,'
823 ||' 2, x.Attribute2,'
824 ||' 3, x.Attribute3,'
825 ||' 4, x.Attribute4,'
826 ||' 5, x.Attribute5,'
827 ||' 6, x.Attribute6,'
828 ||' 7, x.Attribute7,'
829 ||' 8, x.Attribute8,'
830 ||' 9, x.Attribute9,'
831 ||' 10, x.Attribute10,'
832 ||' 11, x.Attribute11,'
833 ||' 12, x.Attribute12,'
834 ||' 13, x.Attribute13,'
835 ||' 14, x.Attribute14,'
836 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
837 ||' x.RESOURCE_ID,'
838 ||' x.RESOURCE_USAGE,'
839 ||' x.BASIS_TYPE,'
840 ||' x.MAX_RESOURCE_UNITS,'
841 ||' x.RESOURCE_UNITS,'
842 ||' x.UOM_CODE,'
843 ||' x.RESOURCE_TYPE,'
844 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
845 ||' 1, x.Attribute1,'
846 ||' 2, x.Attribute2,'
847 ||' 3, x.Attribute3,'
848 ||' 4, x.Attribute4,'
849 ||' 5, x.Attribute5,'
850 ||' 6, x.Attribute6,'
851 ||' 7, x.Attribute7,'
852 ||' 8, x.Attribute8,'
853 ||' 9, x.Attribute9,'
854 ||' 10, x.Attribute10,'
855 ||' 11, x.Attribute11,'
856 ||' 12, x.Attribute12,'
857 ||' 13, x.Attribute13,'
858 ||' 14, x.Attribute14,'
859 ||' 15, x.Attribute15)),0),'
860 ||' x.PRINCIPAL_FLAG,' -- **
861 ||' 2,'
862 ||' :v_refresh_id,'
863 ||' :v_instance_id'
864 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
865 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
866 ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
867 */
868 ||' UNION '
869 ||' SELECT'
870 ||' x.ROUTING_SEQUENCE_ID,'
871 ||' x.OPERATION_SEQUENCE_ID,'
872 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
873 ||' 1, x.Attribute1,'
874 ||' 2, x.Attribute2,'
875 ||' 3, x.Attribute3,'
876 ||' 4, x.Attribute4,'
877 ||' 5, x.Attribute5,'
878 ||' 6, x.Attribute6,'
879 ||' 7, x.Attribute7,'
880 ||' 8, x.Attribute8,'
881 ||' 9, x.Attribute9,'
882 ||' 10, x.Attribute10,'
883 ||' 11, x.Attribute11,'
884 ||' 12, x.Attribute12,'
885 ||' 13, x.Attribute13,'
886 ||' 14, x.Attribute14,'
887 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
888 ||' x.RESOURCE_ID,'
889 ||' x.RESOURCE_USAGE,'
890 ||' x.BASIS_TYPE,'
891 ||' x.MAX_RESOURCE_UNITS,'
892 ||' x.RESOURCE_UNITS,'
893 ||' x.UOM_CODE,'
894 ||' x.RESOURCE_TYPE,'
895 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
896 ||' 1, x.Attribute1,'
897 ||' 2, x.Attribute2,'
898 ||' 3, x.Attribute3,'
899 ||' 4, x.Attribute4,'
900 ||' 5, x.Attribute5,'
901 ||' 6, x.Attribute6,'
902 ||' 7, x.Attribute7,'
903 ||' 8, x.Attribute8,'
904 ||' 9, x.Attribute9,'
905 ||' 10, x.Attribute10,'
906 ||' 11, x.Attribute11,'
907 ||' 12, x.Attribute12,'
908 ||' 13, x.Attribute13,'
909 ||' 14, x.Attribute14,'
910 ||' 15, x.Attribute15)),0),'
911 ||' x.PRINCIPAL_FLAG,' -- **
912 ||' 2,'
913 ||' :v_refresh_id,'
914 ||' :v_instance_id,'
915 ||' x.ORGANIZATION_ID,'
916 ||' x.SETUP_ID,'
917 ||' x.resource_seq_num'
918 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
919 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
920 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
921 ||' UNION '
922 ||' SELECT'
923 ||' x.ROUTING_SEQUENCE_ID,'
924 ||' x.OPERATION_SEQUENCE_ID,'
925 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
926 ||' 1, x.Attribute1,'
927 ||' 2, x.Attribute2,'
928 ||' 3, x.Attribute3,'
929 ||' 4, x.Attribute4,'
930 ||' 5, x.Attribute5,'
931 ||' 6, x.Attribute6,'
932 ||' 7, x.Attribute7,'
933 ||' 8, x.Attribute8,'
934 ||' 9, x.Attribute9,'
935 ||' 10, x.Attribute10,'
936 ||' 11, x.Attribute11,'
937 ||' 12, x.Attribute12,'
938 ||' 13, x.Attribute13,'
939 ||' 14, x.Attribute14,'
940 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
941 ||' x.RESOURCE_ID,'
942 ||' x.RESOURCE_USAGE,'
943 ||' x.BASIS_TYPE,'
944 ||' x.MAX_RESOURCE_UNITS,'
945 ||' x.RESOURCE_UNITS,'
946 ||' x.UOM_CODE,'
947 ||' x.RESOURCE_TYPE,'
948 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
949 ||' 1, x.Attribute1,'
950 ||' 2, x.Attribute2,'
951 ||' 3, x.Attribute3,'
952 ||' 4, x.Attribute4,'
953 ||' 5, x.Attribute5,'
954 ||' 6, x.Attribute6,'
955 ||' 7, x.Attribute7,'
956 ||' 8, x.Attribute8,'
957 ||' 9, x.Attribute9,'
958 ||' 10, x.Attribute10,'
959 ||' 11, x.Attribute11,'
960 ||' 12, x.Attribute12,'
961 ||' 13, x.Attribute13,'
962 ||' 14, x.Attribute14,'
963 ||' 15, x.Attribute15)),0),'
964 ||' x.PRINCIPAL_FLAG,' -- **
965 ||' 2,'
966 ||' :v_refresh_id,'
967 ||' :v_instance_id,'
968 ||' x.ORGANIZATION_ID,'
969 ||' x.SETUP_ID,'
970 ||' x.resource_seq_num'
971 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
972 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
973 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')';
974 /*
975 ||' UNION '
976 ||' SELECT'
977 ||' x.ROUTING_SEQUENCE_ID,'
978 ||' x.OPERATION_SEQUENCE_ID,'
979 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
980 ||' 1, x.Attribute1,'
981 ||' 2, x.Attribute2,'
982 ||' 3, x.Attribute3,'
983 ||' 4, x.Attribute4,'
984 ||' 5, x.Attribute5,'
985 ||' 6, x.Attribute6,'
986 ||' 7, x.Attribute7,'
987 ||' 8, x.Attribute8,'
988 ||' 9, x.Attribute9,'
989 ||' 10, x.Attribute10,'
990 ||' 11, x.Attribute11,'
991 ||' 12, x.Attribute12,'
992 ||' 13, x.Attribute13,'
993 ||' 14, x.Attribute14,'
994 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
995 ||' x.RESOURCE_ID,'
996 ||' x.RESOURCE_USAGE,'
997 ||' x.BASIS_TYPE,'
998 ||' x.MAX_RESOURCE_UNITS,'
999 ||' x.RESOURCE_UNITS,'
1000 ||' x.UOM_CODE,'
1001 ||' x.RESOURCE_TYPE,'
1002 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
1003 ||' 1, x.Attribute1,'
1004 ||' 2, x.Attribute2,'
1005 ||' 3, x.Attribute3,'
1006 ||' 4, x.Attribute4,'
1007 ||' 5, x.Attribute5,'
1008 ||' 6, x.Attribute6,'
1009 ||' 7, x.Attribute7,'
1010 ||' 8, x.Attribute8,'
1011 ||' 9, x.Attribute9,'
1012 ||' 10, x.Attribute10,'
1013 ||' 11, x.Attribute11,'
1014 ||' 12, x.Attribute12,'
1015 ||' 13, x.Attribute13,'
1016 ||' 14, x.Attribute14,'
1017 ||' 15, x.Attribute15)),0),'
1018 ||' x.PRINCIPAL_FLAG,' -- **
1019 ||' 2,'
1020 ||' :v_refresh_id,'
1021 ||' :v_instance_id'
1022 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1023 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1024 ||' AND ( x.RN5>'||MSC_CL_PULL.v_lrn||')' ;
1025 */
1026 ELSE
1027 v_union_sql :=
1028 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1029 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1030 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1031 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1032 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
1033 END IF;
1034 v_sql_stmt:=
1035 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
1036 ||' ( ROUTING_SEQUENCE_ID,'
1037 ||' OPERATION_SEQUENCE_ID,'
1038 ||' RESOURCE_SEQ_NUM,'
1039 ||' RESOURCE_ID,'
1040 ||' RESOURCE_USAGE,'
1041 ||' BASIS_TYPE,'
1042 ||' MAX_RESOURCE_UNITS,'
1043 ||' RESOURCE_UNITS,'
1044 ||' UOM_CODE,'
1045 ||' RESOURCE_TYPE,'
1046 ||' ALTERNATE_NUMBER,'
1047 ||' PRINCIPAL_FLAG,'
1048 ||' DELETED_FLAG,'
1049 ||' REFRESH_ID,'
1050 ||' SR_INSTANCE_ID,'
1051 ||' ORGANIZATION_ID,'
1052 ||' SETUP_ID,'
1053 ||' orig_resource_seq_num)'
1054 ||' SELECT'
1055 ||' x.ROUTING_SEQUENCE_ID,'
1056 ||' x.OPERATION_SEQUENCE_ID,'
1057 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
1058 ||' 1, x.Attribute1,'
1059 ||' 2, x.Attribute2,'
1060 ||' 3, x.Attribute3,'
1061 ||' 4, x.Attribute4,'
1062 ||' 5, x.Attribute5,'
1063 ||' 6, x.Attribute6,'
1064 ||' 7, x.Attribute7,'
1065 ||' 8, x.Attribute8,'
1066 ||' 9, x.Attribute9,'
1067 ||' 10, x.Attribute10,'
1068 ||' 11, x.Attribute11,'
1069 ||' 12, x.Attribute12,'
1070 ||' 13, x.Attribute13,'
1071 ||' 14, x.Attribute14,'
1072 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
1073 ||' x.RESOURCE_ID,'
1074 ||' x.RESOURCE_USAGE,'
1075 ||' x.BASIS_TYPE,'
1076 ||' x.MAX_RESOURCE_UNITS,'
1077 ||' x.RESOURCE_UNITS,'
1078 ||' x.UOM_CODE,'
1079 ||' x.RESOURCE_TYPE,'
1080 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
1081 ||' 1, x.Attribute1,'
1082 ||' 2, x.Attribute2,'
1083 ||' 3, x.Attribute3,'
1084 ||' 4, x.Attribute4,'
1085 ||' 5, x.Attribute5,'
1086 ||' 6, x.Attribute6,'
1087 ||' 7, x.Attribute7,'
1088 ||' 8, x.Attribute8,'
1089 ||' 9, x.Attribute9,'
1090 ||' 10, x.Attribute10,'
1091 ||' 11, x.Attribute11,'
1092 ||' 12, x.Attribute12,'
1093 ||' 13, x.Attribute13,'
1094 ||' 14, x.Attribute14,'
1095 ||' 15, x.Attribute15)),0),'
1096 ||' x.PRINCIPAL_FLAG,' -- **
1097 ||' 2,'
1098 ||' :v_refresh_id,'
1099 ||' :v_instance_id,'
1100 ||' x.ORGANIZATION_ID,'
1101 ||' x.SETUP_ID,'
1102 ||' x.resource_seq_num'
1103 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1104 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1105 || v_union_sql ;
1106
1107 end if;
1108
1109 if v_get_apps_ver >= 3
1110 then
1111 EXECUTE IMMEDIATE v_sql_stmt USING v_get_apps_ver,MSC_CL_PULL.v_msc_simul_res_seq,
1112 v_get_apps_ver,
1113 MSC_CL_PULL.v_msc_alt_res_priority,
1114 MSC_CL_PULL.v_refresh_id,
1115 MSC_CL_PULL.v_instance_id;
1116
1117 else
1118 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1119
1120 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_simul_res_seq,
1121 MSC_CL_PULL.v_msc_alt_res_priority,
1122 MSC_CL_PULL.v_refresh_id,
1123 MSC_CL_PULL.v_instance_id,
1124 MSC_CL_PULL.v_msc_simul_res_seq,
1125 MSC_CL_PULL.v_msc_alt_res_priority,
1126 MSC_CL_PULL.v_refresh_id,
1127 MSC_CL_PULL.v_instance_id,
1128 MSC_CL_PULL.v_msc_simul_res_seq,
1129 MSC_CL_PULL.v_msc_alt_res_priority,
1130 MSC_CL_PULL.v_refresh_id,
1131 MSC_CL_PULL.v_instance_id;
1132 /*
1133 MSC_CL_PULL.v_msc_simul_res_seq,
1134 MSC_CL_PULL.v_msc_alt_res_priority,
1135 MSC_CL_PULL.v_refresh_id,
1136 MSC_CL_PULL.v_instance_id,
1137 MSC_CL_PULL.v_msc_simul_res_seq,
1138 MSC_CL_PULL.v_msc_alt_res_priority,
1139 MSC_CL_PULL.v_refresh_id,
1140 MSC_CL_PULL.v_instance_id;
1141 */
1142
1143 ELSE
1144
1145
1146 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_simul_res_seq,
1147 MSC_CL_PULL.v_msc_alt_res_priority,
1148 MSC_CL_PULL.v_refresh_id,
1149 MSC_CL_PULL.v_instance_id;
1150
1151 END IF;
1152 end if;
1153 COMMIT;
1154
1155 END IF; -- MSC_CL_PULL.BOM_ENABLED
1156
1157 END LOAD_OPERATION_RESOURCES;
1158 PROCEDURE LOAD_OPERATION_COMPONENTS IS
1159 v_get_apps_ver number;
1160 BEGIN
1161
1162 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
1163 --=================== Net Change Mode: Delete ==================
1164 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1165
1166 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_COMPONENTS';
1167 MSC_CL_PULL.v_view_name := 'MRP_AD_OPERATION_COMPONENTS_V';
1168
1169 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1170 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1171 ELSE
1172 v_temp_sql := NULL;
1173 END IF;
1174
1175 v_sql_stmt:=
1176 'INSERT INTO MSC_ST_OPERATION_COMPONENTS'
1177 ||' ( COMPONENT_SEQUENCE_ID,'
1178 ||' OPERATION_SEQUENCE_ID,'
1179 ||' BILL_SEQUENCE_ID,'
1180 ||' ROUTING_SEQUENCE_ID,'
1181 ||' ORGANIZATION_ID,'
1182 ||' DELETED_FLAG,'
1183 ||' REFRESH_ID,'
1184 ||' SR_INSTANCE_ID)'
1185 ||' SELECT'
1186 ||' x.COMPONENT_SEQUENCE_ID,'
1187 ||' x.OPERATION_SEQUENCE_ID,'
1188 ||' x.BILL_SEQUENCE_ID,'
1189 ||' x.ROUTING_SEQUENCE_ID,'
1190 ||' x.ORGANIZATION_ID,'
1191 ||' 1,'
1192 ||' :v_refresh_id,'
1193 ||' :v_instance_id'
1194 ||' FROM MRP_AD_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1195 ||' WHERE x.RN> :v_lrn '
1196 || v_temp_sql;
1197
1198 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1199
1200 COMMIT;
1201
1202 END IF;
1203
1204 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_COMPONENTS';
1205 MSC_CL_PULL.v_view_name := 'MRP_AP_OPERATION_COMPONENTS_V';
1206 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1207 v_union_sql :=
1208 ' AND (x.RN1> :v_lrn )'
1209 ||' UNION '
1210 ||' SELECT'
1211 ||' x.ORGANIZATION_ID,'
1212 ||' x.COMPONENT_SEQUENCE_ID,'
1213 ||' x.OPERATION_SEQUENCE_ID,'
1214 ||' x.BILL_SEQUENCE_ID,'
1215 ||' x.ROUTING_SEQUENCE_ID,'
1216 ||' 2,'
1217 ||' :v_refresh_id,'
1218 ||' :v_instance_id'
1219 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1220 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1221 ||' AND (x.RN2> :v_lrn )'
1222 ||' UNION '
1223 ||' SELECT'
1224 ||' x.ORGANIZATION_ID,'
1225 ||' x.COMPONENT_SEQUENCE_ID,'
1226 ||' x.OPERATION_SEQUENCE_ID,'
1227 ||' x.BILL_SEQUENCE_ID,'
1228 ||' x.ROUTING_SEQUENCE_ID,'
1229 ||' 2,'
1230 ||' :v_refresh_id,'
1231 ||' :v_instance_id'
1232 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1233 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1234 ||' AND (x.RN3> :v_lrn )'
1235 ||' UNION '
1236 ||' SELECT'
1237 ||' x.ORGANIZATION_ID,'
1238 ||' x.COMPONENT_SEQUENCE_ID,'
1239 ||' x.OPERATION_SEQUENCE_ID,'
1240 ||' x.BILL_SEQUENCE_ID,'
1241 ||' x.ROUTING_SEQUENCE_ID,'
1242 ||' 2,'
1243 ||' :v_refresh_id,'
1244 ||' :v_instance_id'
1245 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1246 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1247 ||' AND (x.RN4> :v_lrn )'
1248 ||' UNION '
1249 ||' SELECT'
1250 ||' x.ORGANIZATION_ID,'
1251 ||' x.COMPONENT_SEQUENCE_ID,'
1252 ||' x.OPERATION_SEQUENCE_ID,'
1253 ||' x.BILL_SEQUENCE_ID,'
1254 ||' x.ROUTING_SEQUENCE_ID,'
1255 ||' 2,'
1256 ||' :v_refresh_id,'
1257 ||' :v_instance_id'
1258 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1259 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1260 ||' AND (x.RN5> :v_lrn )'
1261 ||' UNION '
1262 ||' SELECT'
1263 ||' x.ORGANIZATION_ID,'
1264 ||' x.COMPONENT_SEQUENCE_ID,'
1265 ||' x.OPERATION_SEQUENCE_ID,'
1266 ||' x.BILL_SEQUENCE_ID,'
1267 ||' x.ROUTING_SEQUENCE_ID,'
1268 ||' 2,'
1269 ||' :v_refresh_id,'
1270 ||' :v_instance_id'
1271 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1272 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1273 ||' AND (x.RN6> :v_lrn )' ;
1274 ELSE
1275 v_union_sql := ' ';
1276
1277
1278 END IF;
1279
1280 v_sql_stmt:=
1281 'INSERT INTO MSC_ST_OPERATION_COMPONENTS'
1282 ||' ( ORGANIZATION_ID,'
1283 ||' COMPONENT_SEQUENCE_ID,'
1284 ||' OPERATION_SEQUENCE_ID,'
1285 ||' BILL_SEQUENCE_ID,'
1286 ||' ROUTING_SEQUENCE_ID,'
1287 ||' DELETED_FLAG,'
1288 ||' REFRESH_ID,'
1289 ||' SR_INSTANCE_ID)'
1290 ||' SELECT'
1291 ||' x.ORGANIZATION_ID,'
1292 ||' x.COMPONENT_SEQUENCE_ID,'
1293 ||' x.OPERATION_SEQUENCE_ID,'
1294 ||' x.BILL_SEQUENCE_ID,'
1295 ||' x.ROUTING_SEQUENCE_ID,'
1296 ||' 2,'
1297 ||' :v_refresh_id,'
1298 ||' :v_instance_id'
1299 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1300 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1301 || v_union_sql ;
1302
1303 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1304
1305 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1306 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1307 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1308 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1309 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1310 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1311
1312 ELSE
1313
1314 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1315
1316 END IF;
1317
1318 COMMIT;
1319
1320 END IF; -- MSC_CL_PULL.BOM_ENABLED
1321
1322 END LOAD_OPERATION_COMPONENTS;
1323
1324
1325 END MSC_CL_ROUTING_PULL;