[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_ROUTING_PULL
Source
1 PACKAGE BODY MSC_CL_ROUTING_PULL AS -- body
2 /* $Header: MSCPRTGB.pls 120.4 2011/06/07 15:33:13 lsindhur ship $ */
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, '
359 ||' NET_PLANNING_PERCENT,'
356 ||' OPERATION_LEAD_TIME_PERCENT, '
357 ||' CUMULATIVE_YIELD, '
358 ||' REVERSE_CUMULATIVE_YIELD,'
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 ||' 5,x.resource_seq_num,6,x.resource_seq_num,'
520 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
521 ||' 1, x.Attribute1,'
522 ||' 2, x.Attribute2,'
523 ||' 3, x.Attribute3,'
527 ||' 7, x.Attribute7,'
524 ||' 4, x.Attribute4,'
525 ||' 5, x.Attribute5,'
526 ||' 6, x.Attribute6,'
528 ||' 8, x.Attribute8,'
529 ||' 9, x.Attribute9,'
530 ||' 10, x.Attribute10,'
531 ||' 11, x.Attribute11,'
532 ||' 12, x.Attribute12,'
533 ||' 13, x.Attribute13,'
534 ||' 14, x.Attribute14,'
535 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM)),'
536 || v_temp_sql
537 ||' x.SCHEDULE_FLAG,'
538 ||' x.RESOURCE_OFFSET_PERCENT,'
539 ||' x.DEPARTMENT_ID,'
540 || v_temp_sql1
541 ||' 2,'
542 ||' :v_refresh_id,'
543 ||' :v_instance_id,'
544 ||' x.ORGANIZATION_ID'
545 ||' FROM MRP_AP_OP_RESOURCE_SEQS_V'||MSC_CL_PULL.v_dblink||' x'
546 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
547 || v_union_sql ;
548
549 --IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
550
551 EXECUTE IMMEDIATE v_sql_stmt
552 USING v_get_apps_ver,
553 MSC_CL_PULL.v_msc_simul_res_seq,
554 MSC_CL_PULL.v_refresh_id,
555 MSC_CL_PULL.v_instance_id;
556
557
558 --ELSE
559
560
561
562 --END IF;
563
564 COMMIT;
565
566 END IF; -- MSC_CL_PULL.BOM_ENABLED
567
568 END LOAD_OPERATION_RES_SEQS;
569 PROCEDURE LOAD_OPERATION_RESOURCES IS
570 v_get_apps_ver number;
571 BEGIN
572
573 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
574 BEGIN
575 SELECT APPS_VER
576 INTO V_GET_APPS_VER
577 FROM MSC_APPS_INSTANCES
578 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
579 END;
580 --=================== Net Change Mode: Delete ==================
581 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
582
583 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
584 MSC_CL_PULL.v_view_name := 'MRP_AD_OPERATION_RESOURCES_V';
585 BEGIN
586 SELECT APPS_VER
587 INTO V_GET_APPS_VER
588 FROM MSC_APPS_INSTANCES
589 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
590 END;
591
592 if v_get_apps_ver >= 3
593 then
594 v_sql_stmt:=
595 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
596 ||' ( ROUTING_SEQUENCE_ID,'
597 ||' OPERATION_SEQUENCE_ID,'
598 ||' RESOURCE_SEQ_NUM,'
599 ||' RESOURCE_ID,'
600 ||' ALTERNATE_NUMBER,'
601 ||' DELETED_FLAG,'
602 ||' REFRESH_ID,'
603 ||' SR_INSTANCE_ID)'
604 ||' SELECT'
605 ||' x.ROUTING_SEQUENCE_ID,'
606 ||' x.OPERATION_SEQUENCE_ID,'
607 ||' x.RESOURCE_SEQ_NUM,'
608 ||' x.RESOURCE_ID,'
609 ||' x.ALTERNATE_NUMBER,' -- **
610 ||' 1,'
611 ||' :v_refresh_id,'
612 ||' :v_instance_id'
613 ||' FROM MRP_AD_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
614 ||' WHERE x.RN > '||MSC_CL_PULL.v_lrn
615 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
616
617 else
618
619
620 v_sql_stmt:=
621 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
622 ||' ( ROUTING_SEQUENCE_ID,'
623 ||' OPERATION_SEQUENCE_ID,'
624 ||' RESOURCE_SEQ_NUM,'
625 ||' RESOURCE_ID,'
626 ||' ALTERNATE_NUMBER,'
627 ||' DELETED_FLAG,'
628 ||' REFRESH_ID,'
629 ||' SR_INSTANCE_ID)'
630 ||' SELECT'
631 ||' x.ROUTING_SEQUENCE_ID,'
632 ||' x.OPERATION_SEQUENCE_ID,'
633 ||' x.RESOURCE_SEQ_NUM,'
634 ||' x.RESOURCE_ID,'
635 ||' x.ALTERNATE_NUMBER,' -- **
636 ||' 1,'
637 ||' :v_refresh_id,'
638 ||' :v_instance_id'
639 ||' FROM MRP_AD_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
640 ||' WHERE x.RN > '||MSC_CL_PULL.v_lrn;
641
642 end if;
643
644 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
645
646 COMMIT;
647
648
649 BEGIN
650 SELECT APPS_VER
651 INTO V_GET_APPS_VER
652 FROM MSC_APPS_INSTANCES
653 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
654 END;
655
656 if V_GET_APPS_VER >= 3
657 then
658 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
659 MSC_CL_PULL.v_view_name := 'MRP_AD_SUB_OPER_RESS_V';
660
661 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
662 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
663 ELSE
664 v_temp_sql := NULL;
665 END IF;
666
667 v_sql_stmt:=
668 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
669 ||' ( ROUTING_SEQUENCE_ID,'
670 ||' OPERATION_SEQUENCE_ID,'
671 ||' RESOURCE_SEQ_NUM,'
672 ||' RESOURCE_ID,'
673 ||' ALTERNATE_NUMBER,'
674 ||' DELETED_FLAG,'
675 ||' REFRESH_ID,'
676 ||' SR_INSTANCE_ID)'
677 ||' SELECT'
678 ||' x.ROUTING_SEQUENCE_ID,'
679 ||' x.OPERATION_SEQUENCE_ID,'
680 ||' x.RESOURCE_SEQ_NUM,'
681 ||' x.RESOURCE_ID,'
682 ||' x.ALTERNATE_NUMBER,' -- **
686 ||' FROM MRP_AD_SUB_OPER_RESS_V'||MSC_CL_PULL.v_dblink||' x'
683 ||' 1,'
684 ||' :v_refresh_id,'
685 ||' :v_instance_id'
687 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
688 || v_temp_sql;
689
690 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
691
692 COMMIT;
693
694 END IF;
695
696 END IF;
697
698 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_RESOURCES';
699 MSC_CL_PULL.v_view_name := 'MRP_AP_OPERATION_RESOURCES_V';
700
701 BEGIN
702 SELECT APPS_VER
703 INTO V_GET_APPS_VER
704 FROM MSC_APPS_INSTANCES
705 WHERE INSTANCE_ID = MSC_CL_PULL.v_instance_id;
706 END;
707
708 if v_get_apps_ver >= 3
709 then
710
711 v_sql_stmt:=
712 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
713 ||' ( ROUTING_SEQUENCE_ID,'
714 ||' OPERATION_SEQUENCE_ID,'
715 ||' RESOURCE_SEQ_NUM,'
716 ||' RESOURCE_ID,'
717 ||' RESOURCE_USAGE,'
718 ||' BASIS_TYPE,'
719 ||' MAX_RESOURCE_UNITS,'
720 ||' RESOURCE_UNITS,'
721 ||' UOM_CODE,'
722 ||' RESOURCE_TYPE,'
723 ||' ALTERNATE_NUMBER,'
724 ||' PRINCIPAL_FLAG,'
725 ||' DELETED_FLAG,'
726 ||' REFRESH_ID,'
727 ||' SR_INSTANCE_ID,'
728 ||' ORGANIZATION_ID ,'
729 ||' SETUP_ID ,'
730 ||' orig_resource_seq_num )'
731 ||' SELECT'
732 ||' x.ROUTING_SEQUENCE_ID,'
733 ||' x.OPERATION_SEQUENCE_ID,'
734 ||' decode(:v_get_apps_ver,3,x.schedule_seq_num,4,x.schedule_seq_num,'
735 ||'5,x.schedule_seq_num,6,x.schedule_seq_num,NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
736 ||' 1, x.Attribute1,'
737 ||' 2, x.Attribute2,'
738 ||' 3, x.Attribute3,'
739 ||' 4, x.Attribute4,'
740 ||' 5, x.Attribute5,'
741 ||' 6, x.Attribute6,'
742 ||' 7, x.Attribute7,'
743 ||' 8, x.Attribute8,'
744 ||' 9, x.Attribute9,'
745 ||' 10, x.Attribute10,'
746 ||' 11, x.Attribute11,'
747 ||' 12, x.Attribute12,'
748 ||' 13, x.Attribute13,'
749 ||' 14, x.Attribute14,'
750 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM)),'
751 ||' x.RESOURCE_ID,'
752 ||' x.RESOURCE_USAGE,'
753 ||' x.BASIS_TYPE,'
754 ||' x.MAX_RESOURCE_UNITS,'
755 ||' x.RESOURCE_UNITS,'
756 ||' x.UOM_CODE,'
757 ||' x.RESOURCE_TYPE,'
758 /* obsolete
759 ||' DECODE(DECODE( :v_msc_alt_op_res,'
760 ||' 1, x.Attribute1,'
761 ||' 2, x.Attribute2,'
762 ||' 3, x.Attribute3,'
763 ||' 4, x.Attribute4,'
764 ||' 5, x.Attribute5,'
765 ||' 6, x.Attribute6,'
766 ||' 7, x.Attribute7,'
767 ||' 8, x.Attribute8,'
768 ||' 9, x.Attribute9,'
769 ||' 10, x.Attribute10,'
770 ||' 11, x.Attribute11,'
771 ||' 12, x.Attribute12,'
772 ||' 13, x.Attribute13,'
773 ||' 14, x.Attribute14,'
774 ||' 15, x.Attribute15),'
775 ||' ''alternate'',1,'
776 ||' ''aux1'',2,'
777 ||' ''aux2'',3,'
778 ||' ''machine'',4,'
779 ||' ''operators'',5 ),' obsolete*/
780 ||' decode(:v_get_apps_ver,3,nvl(x.alternate_number,0),4,nvl(x.alternate_number,0),
781 5,nvl(x.alternate_number,0),6,nvl(x.alternate_number,0),NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
782 ||' 1, x.Attribute1,'
783 ||' 2, x.Attribute2,'
784 ||' 3, x.Attribute3,'
785 ||' 4, x.Attribute4,'
786 ||' 5, x.Attribute5,'
787 ||' 6, x.Attribute6,'
788 ||' 7, x.Attribute7,'
789 ||' 8, x.Attribute8,'
790 ||' 9, x.Attribute9,'
791 ||' 10, x.Attribute10,'
792 ||' 11, x.Attribute11,'
793 ||' 12, x.Attribute12,'
794 ||' 13, x.Attribute13,'
795 ||' 14, x.Attribute14,'
796 ||' 15, x.Attribute15)),0)),'
797 ||' x.PRINCIPAL_FLAG,' -- **
798 ||' 2,'
799 ||' :v_refresh_id,'
800 ||' :v_instance_id,'
801 ||' x.ORGANIZATION_ID,'
802 ||' x.SETUP_ID,'
803 ||' x.resource_seq_num'
804 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
805 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
806 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
807 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
808 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
809 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
810 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
811
812
813 else
814
815 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
816 v_union_sql :=
817 ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
818 /*
822 ||' x.OPERATION_SEQUENCE_ID,'
819 ||' UNION '
820 ||' SELECT'
821 ||' x.ROUTING_SEQUENCE_ID,'
823 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
824 ||' 1, x.Attribute1,'
825 ||' 2, x.Attribute2,'
826 ||' 3, x.Attribute3,'
827 ||' 4, x.Attribute4,'
828 ||' 5, x.Attribute5,'
829 ||' 6, x.Attribute6,'
830 ||' 7, x.Attribute7,'
831 ||' 8, x.Attribute8,'
832 ||' 9, x.Attribute9,'
833 ||' 10, x.Attribute10,'
834 ||' 11, x.Attribute11,'
835 ||' 12, x.Attribute12,'
836 ||' 13, x.Attribute13,'
837 ||' 14, x.Attribute14,'
838 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
839 ||' x.RESOURCE_ID,'
840 ||' x.RESOURCE_USAGE,'
841 ||' x.BASIS_TYPE,'
842 ||' x.MAX_RESOURCE_UNITS,'
843 ||' x.RESOURCE_UNITS,'
844 ||' x.UOM_CODE,'
845 ||' x.RESOURCE_TYPE,'
846 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
847 ||' 1, x.Attribute1,'
848 ||' 2, x.Attribute2,'
849 ||' 3, x.Attribute3,'
850 ||' 4, x.Attribute4,'
851 ||' 5, x.Attribute5,'
852 ||' 6, x.Attribute6,'
853 ||' 7, x.Attribute7,'
854 ||' 8, x.Attribute8,'
855 ||' 9, x.Attribute9,'
856 ||' 10, x.Attribute10,'
857 ||' 11, x.Attribute11,'
858 ||' 12, x.Attribute12,'
859 ||' 13, x.Attribute13,'
860 ||' 14, x.Attribute14,'
861 ||' 15, x.Attribute15)),0),'
862 ||' x.PRINCIPAL_FLAG,' -- **
863 ||' 2,'
864 ||' :v_refresh_id,'
865 ||' :v_instance_id'
866 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
867 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
868 ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
869 */
870 ||' UNION '
871 ||' SELECT'
872 ||' x.ROUTING_SEQUENCE_ID,'
873 ||' x.OPERATION_SEQUENCE_ID,'
874 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
875 ||' 1, x.Attribute1,'
876 ||' 2, x.Attribute2,'
877 ||' 3, x.Attribute3,'
878 ||' 4, x.Attribute4,'
879 ||' 5, x.Attribute5,'
880 ||' 6, x.Attribute6,'
881 ||' 7, x.Attribute7,'
882 ||' 8, x.Attribute8,'
883 ||' 9, x.Attribute9,'
884 ||' 10, x.Attribute10,'
885 ||' 11, x.Attribute11,'
886 ||' 12, x.Attribute12,'
887 ||' 13, x.Attribute13,'
888 ||' 14, x.Attribute14,'
889 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
890 ||' x.RESOURCE_ID,'
891 ||' x.RESOURCE_USAGE,'
892 ||' x.BASIS_TYPE,'
893 ||' x.MAX_RESOURCE_UNITS,'
894 ||' x.RESOURCE_UNITS,'
895 ||' x.UOM_CODE,'
896 ||' x.RESOURCE_TYPE,'
897 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
898 ||' 1, x.Attribute1,'
899 ||' 2, x.Attribute2,'
900 ||' 3, x.Attribute3,'
901 ||' 4, x.Attribute4,'
902 ||' 5, x.Attribute5,'
903 ||' 6, x.Attribute6,'
904 ||' 7, x.Attribute7,'
905 ||' 8, x.Attribute8,'
906 ||' 9, x.Attribute9,'
907 ||' 10, x.Attribute10,'
908 ||' 11, x.Attribute11,'
909 ||' 12, x.Attribute12,'
910 ||' 13, x.Attribute13,'
911 ||' 14, x.Attribute14,'
912 ||' 15, x.Attribute15)),0),'
913 ||' x.PRINCIPAL_FLAG,' -- **
914 ||' 2,'
915 ||' :v_refresh_id,'
916 ||' :v_instance_id,'
917 ||' x.ORGANIZATION_ID,'
918 ||' x.SETUP_ID,'
919 ||' x.resource_seq_num'
920 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
921 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
922 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
923 ||' UNION '
924 ||' SELECT'
925 ||' x.ROUTING_SEQUENCE_ID,'
926 ||' x.OPERATION_SEQUENCE_ID,'
927 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
928 ||' 1, x.Attribute1,'
929 ||' 2, x.Attribute2,'
930 ||' 3, x.Attribute3,'
931 ||' 4, x.Attribute4,'
932 ||' 5, x.Attribute5,'
933 ||' 6, x.Attribute6,'
937 ||' 10, x.Attribute10,'
934 ||' 7, x.Attribute7,'
935 ||' 8, x.Attribute8,'
936 ||' 9, x.Attribute9,'
938 ||' 11, x.Attribute11,'
939 ||' 12, x.Attribute12,'
940 ||' 13, x.Attribute13,'
941 ||' 14, x.Attribute14,'
942 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
943 ||' x.RESOURCE_ID,'
944 ||' x.RESOURCE_USAGE,'
945 ||' x.BASIS_TYPE,'
946 ||' x.MAX_RESOURCE_UNITS,'
947 ||' x.RESOURCE_UNITS,'
948 ||' x.UOM_CODE,'
949 ||' x.RESOURCE_TYPE,'
950 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
951 ||' 1, x.Attribute1,'
952 ||' 2, x.Attribute2,'
953 ||' 3, x.Attribute3,'
954 ||' 4, x.Attribute4,'
955 ||' 5, x.Attribute5,'
956 ||' 6, x.Attribute6,'
957 ||' 7, x.Attribute7,'
958 ||' 8, x.Attribute8,'
959 ||' 9, x.Attribute9,'
960 ||' 10, x.Attribute10,'
961 ||' 11, x.Attribute11,'
962 ||' 12, x.Attribute12,'
963 ||' 13, x.Attribute13,'
964 ||' 14, x.Attribute14,'
965 ||' 15, x.Attribute15)),0),'
966 ||' x.PRINCIPAL_FLAG,' -- **
967 ||' 2,'
968 ||' :v_refresh_id,'
969 ||' :v_instance_id,'
970 ||' x.ORGANIZATION_ID,'
971 ||' x.SETUP_ID,'
972 ||' x.resource_seq_num'
973 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
974 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
975 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')';
976 /*
977 ||' UNION '
978 ||' SELECT'
979 ||' x.ROUTING_SEQUENCE_ID,'
980 ||' x.OPERATION_SEQUENCE_ID,'
981 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
982 ||' 1, x.Attribute1,'
983 ||' 2, x.Attribute2,'
984 ||' 3, x.Attribute3,'
985 ||' 4, x.Attribute4,'
986 ||' 5, x.Attribute5,'
987 ||' 6, x.Attribute6,'
988 ||' 7, x.Attribute7,'
989 ||' 8, x.Attribute8,'
990 ||' 9, x.Attribute9,'
991 ||' 10, x.Attribute10,'
992 ||' 11, x.Attribute11,'
993 ||' 12, x.Attribute12,'
994 ||' 13, x.Attribute13,'
995 ||' 14, x.Attribute14,'
996 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
997 ||' x.RESOURCE_ID,'
998 ||' x.RESOURCE_USAGE,'
999 ||' x.BASIS_TYPE,'
1000 ||' x.MAX_RESOURCE_UNITS,'
1001 ||' x.RESOURCE_UNITS,'
1002 ||' x.UOM_CODE,'
1003 ||' x.RESOURCE_TYPE,'
1004 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
1005 ||' 1, x.Attribute1,'
1006 ||' 2, x.Attribute2,'
1007 ||' 3, x.Attribute3,'
1008 ||' 4, x.Attribute4,'
1009 ||' 5, x.Attribute5,'
1010 ||' 6, x.Attribute6,'
1011 ||' 7, x.Attribute7,'
1012 ||' 8, x.Attribute8,'
1013 ||' 9, x.Attribute9,'
1014 ||' 10, x.Attribute10,'
1015 ||' 11, x.Attribute11,'
1016 ||' 12, x.Attribute12,'
1017 ||' 13, x.Attribute13,'
1018 ||' 14, x.Attribute14,'
1019 ||' 15, x.Attribute15)),0),'
1020 ||' x.PRINCIPAL_FLAG,' -- **
1021 ||' 2,'
1022 ||' :v_refresh_id,'
1023 ||' :v_instance_id'
1024 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1025 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1026 ||' AND ( x.RN5>'||MSC_CL_PULL.v_lrn||')' ;
1027 */
1028 ELSE
1029 v_union_sql :=
1030 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1031 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1032 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1033 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1034 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn||')';
1035 END IF;
1036 v_sql_stmt:=
1037 ' INSERT INTO MSC_ST_OPERATION_RESOURCES'
1038 ||' ( ROUTING_SEQUENCE_ID,'
1039 ||' OPERATION_SEQUENCE_ID,'
1040 ||' RESOURCE_SEQ_NUM,'
1041 ||' RESOURCE_ID,'
1042 ||' RESOURCE_USAGE,'
1043 ||' BASIS_TYPE,'
1044 ||' MAX_RESOURCE_UNITS,'
1045 ||' RESOURCE_UNITS,'
1046 ||' UOM_CODE,'
1047 ||' RESOURCE_TYPE,'
1048 ||' ALTERNATE_NUMBER,'
1049 ||' PRINCIPAL_FLAG,'
1050 ||' DELETED_FLAG,'
1051 ||' REFRESH_ID,'
1055 ||' orig_resource_seq_num)'
1052 ||' SR_INSTANCE_ID,'
1053 ||' ORGANIZATION_ID,'
1054 ||' SETUP_ID,'
1056 ||' SELECT'
1057 ||' x.ROUTING_SEQUENCE_ID,'
1058 ||' x.OPERATION_SEQUENCE_ID,'
1059 ||' NVL(TO_NUMBER(DECODE( :v_msc_simul_res_seq,'
1060 ||' 1, x.Attribute1,'
1061 ||' 2, x.Attribute2,'
1062 ||' 3, x.Attribute3,'
1063 ||' 4, x.Attribute4,'
1064 ||' 5, x.Attribute5,'
1065 ||' 6, x.Attribute6,'
1066 ||' 7, x.Attribute7,'
1067 ||' 8, x.Attribute8,'
1068 ||' 9, x.Attribute9,'
1069 ||' 10, x.Attribute10,'
1070 ||' 11, x.Attribute11,'
1071 ||' 12, x.Attribute12,'
1072 ||' 13, x.Attribute13,'
1073 ||' 14, x.Attribute14,'
1074 ||' 15, x.Attribute15)),x.RESOURCE_SEQ_NUM),'
1075 ||' x.RESOURCE_ID,'
1076 ||' x.RESOURCE_USAGE,'
1077 ||' x.BASIS_TYPE,'
1078 ||' x.MAX_RESOURCE_UNITS,'
1079 ||' x.RESOURCE_UNITS,'
1080 ||' x.UOM_CODE,'
1081 ||' x.RESOURCE_TYPE,'
1082 ||' NVL(TO_NUMBER(DECODE( :v_msc_alt_res_priority,'
1083 ||' 1, x.Attribute1,'
1084 ||' 2, x.Attribute2,'
1085 ||' 3, x.Attribute3,'
1086 ||' 4, x.Attribute4,'
1087 ||' 5, x.Attribute5,'
1088 ||' 6, x.Attribute6,'
1089 ||' 7, x.Attribute7,'
1090 ||' 8, x.Attribute8,'
1091 ||' 9, x.Attribute9,'
1092 ||' 10, x.Attribute10,'
1093 ||' 11, x.Attribute11,'
1094 ||' 12, x.Attribute12,'
1095 ||' 13, x.Attribute13,'
1096 ||' 14, x.Attribute14,'
1097 ||' 15, x.Attribute15)),0),'
1098 ||' x.PRINCIPAL_FLAG,' -- **
1099 ||' 2,'
1100 ||' :v_refresh_id,'
1101 ||' :v_instance_id,'
1102 ||' x.ORGANIZATION_ID,'
1103 ||' x.SETUP_ID,'
1104 ||' x.resource_seq_num'
1105 ||' FROM MRP_AP_OPERATION_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1106 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1107 || v_union_sql ;
1108
1109 end if;
1110
1111 if v_get_apps_ver >= 3
1112 then
1113 EXECUTE IMMEDIATE v_sql_stmt USING v_get_apps_ver,MSC_CL_PULL.v_msc_simul_res_seq,
1114 v_get_apps_ver,
1115 MSC_CL_PULL.v_msc_alt_res_priority,
1116 MSC_CL_PULL.v_refresh_id,
1117 MSC_CL_PULL.v_instance_id;
1118
1119 else
1120 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1121
1122 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_simul_res_seq,
1123 MSC_CL_PULL.v_msc_alt_res_priority,
1124 MSC_CL_PULL.v_refresh_id,
1125 MSC_CL_PULL.v_instance_id,
1126 MSC_CL_PULL.v_msc_simul_res_seq,
1127 MSC_CL_PULL.v_msc_alt_res_priority,
1128 MSC_CL_PULL.v_refresh_id,
1129 MSC_CL_PULL.v_instance_id,
1130 MSC_CL_PULL.v_msc_simul_res_seq,
1131 MSC_CL_PULL.v_msc_alt_res_priority,
1132 MSC_CL_PULL.v_refresh_id,
1133 MSC_CL_PULL.v_instance_id;
1134 /*
1135 MSC_CL_PULL.v_msc_simul_res_seq,
1136 MSC_CL_PULL.v_msc_alt_res_priority,
1137 MSC_CL_PULL.v_refresh_id,
1138 MSC_CL_PULL.v_instance_id,
1139 MSC_CL_PULL.v_msc_simul_res_seq,
1140 MSC_CL_PULL.v_msc_alt_res_priority,
1141 MSC_CL_PULL.v_refresh_id,
1142 MSC_CL_PULL.v_instance_id;
1143 */
1144
1145 ELSE
1146
1147
1148 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_simul_res_seq,
1149 MSC_CL_PULL.v_msc_alt_res_priority,
1150 MSC_CL_PULL.v_refresh_id,
1151 MSC_CL_PULL.v_instance_id;
1152
1153 END IF;
1154 end if;
1155 COMMIT;
1156
1157 END IF; -- MSC_CL_PULL.BOM_ENABLED
1158
1159 END LOAD_OPERATION_RESOURCES;
1160 PROCEDURE LOAD_OPERATION_COMPONENTS IS
1161 v_get_apps_ver number;
1162 BEGIN
1163
1164 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
1165 --=================== Net Change Mode: Delete ==================
1166 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1167
1168 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_COMPONENTS';
1169 MSC_CL_PULL.v_view_name := 'MRP_AD_OPERATION_COMPONENTS_V';
1170
1171 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1175 END IF;
1172 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1173 ELSE
1174 v_temp_sql := NULL;
1176
1177 v_sql_stmt:=
1178 'INSERT INTO MSC_ST_OPERATION_COMPONENTS'
1179 ||' ( COMPONENT_SEQUENCE_ID,'
1180 ||' OPERATION_SEQUENCE_ID,'
1181 ||' BILL_SEQUENCE_ID,'
1182 ||' ROUTING_SEQUENCE_ID,'
1183 ||' ORGANIZATION_ID,'
1184 ||' DELETED_FLAG,'
1185 ||' REFRESH_ID,'
1186 ||' SR_INSTANCE_ID)'
1187 ||' SELECT'
1188 ||' x.COMPONENT_SEQUENCE_ID,'
1189 ||' x.OPERATION_SEQUENCE_ID,'
1190 ||' x.BILL_SEQUENCE_ID,'
1191 ||' x.ROUTING_SEQUENCE_ID,'
1192 ||' x.ORGANIZATION_ID,'
1193 ||' 1,'
1194 ||' :v_refresh_id,'
1195 ||' :v_instance_id'
1196 ||' FROM MRP_AD_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1197 ||' WHERE x.RN> :v_lrn '
1198 || v_temp_sql;
1199
1200 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1201
1202 COMMIT;
1203
1204 END IF;
1205
1206 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_COMPONENTS';
1207 MSC_CL_PULL.v_view_name := 'MRP_AP_OPERATION_COMPONENTS_V';
1208 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1209 v_union_sql :=
1210 ' AND (x.RN1> :v_lrn )'
1211 ||' UNION '
1212 ||' SELECT'
1213 ||' x.ORGANIZATION_ID,'
1214 ||' x.COMPONENT_SEQUENCE_ID,'
1215 ||' x.OPERATION_SEQUENCE_ID,'
1216 ||' x.BILL_SEQUENCE_ID,'
1217 ||' x.ROUTING_SEQUENCE_ID,'
1218 ||' 2,'
1219 ||' :v_refresh_id,'
1220 ||' :v_instance_id'
1221 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1222 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1223 ||' AND (x.RN2> :v_lrn )'
1224 ||' UNION '
1225 ||' SELECT'
1226 ||' x.ORGANIZATION_ID,'
1227 ||' x.COMPONENT_SEQUENCE_ID,'
1228 ||' x.OPERATION_SEQUENCE_ID,'
1229 ||' x.BILL_SEQUENCE_ID,'
1230 ||' x.ROUTING_SEQUENCE_ID,'
1231 ||' 2,'
1232 ||' :v_refresh_id,'
1233 ||' :v_instance_id'
1234 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1235 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1236 ||' AND (x.RN3> :v_lrn )'
1237 ||' UNION '
1238 ||' SELECT'
1239 ||' x.ORGANIZATION_ID,'
1240 ||' x.COMPONENT_SEQUENCE_ID,'
1241 ||' x.OPERATION_SEQUENCE_ID,'
1242 ||' x.BILL_SEQUENCE_ID,'
1243 ||' x.ROUTING_SEQUENCE_ID,'
1244 ||' 2,'
1245 ||' :v_refresh_id,'
1246 ||' :v_instance_id'
1247 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1248 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1249 ||' AND (x.RN4> :v_lrn )'
1250 ||' UNION '
1251 ||' SELECT'
1252 ||' x.ORGANIZATION_ID,'
1253 ||' x.COMPONENT_SEQUENCE_ID,'
1254 ||' x.OPERATION_SEQUENCE_ID,'
1255 ||' x.BILL_SEQUENCE_ID,'
1256 ||' x.ROUTING_SEQUENCE_ID,'
1257 ||' 2,'
1258 ||' :v_refresh_id,'
1259 ||' :v_instance_id'
1260 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1261 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1262 ||' AND (x.RN5> :v_lrn )'
1263 ||' UNION '
1264 ||' SELECT'
1265 ||' x.ORGANIZATION_ID,'
1266 ||' x.COMPONENT_SEQUENCE_ID,'
1267 ||' x.OPERATION_SEQUENCE_ID,'
1268 ||' x.BILL_SEQUENCE_ID,'
1269 ||' x.ROUTING_SEQUENCE_ID,'
1270 ||' 2,'
1271 ||' :v_refresh_id,'
1272 ||' :v_instance_id'
1273 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1274 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1275 ||' AND (x.RN6> :v_lrn )' ;
1276 ELSE
1277 v_union_sql := ' ';
1278
1279
1280 END IF;
1281
1282 v_sql_stmt:=
1283 'INSERT INTO MSC_ST_OPERATION_COMPONENTS'
1284 ||' ( ORGANIZATION_ID,'
1285 ||' COMPONENT_SEQUENCE_ID,'
1286 ||' OPERATION_SEQUENCE_ID,'
1287 ||' BILL_SEQUENCE_ID,'
1288 ||' ROUTING_SEQUENCE_ID,'
1289 ||' DELETED_FLAG,'
1290 ||' REFRESH_ID,'
1291 ||' SR_INSTANCE_ID)'
1292 ||' SELECT'
1293 ||' x.ORGANIZATION_ID,'
1294 ||' x.COMPONENT_SEQUENCE_ID,'
1295 ||' x.OPERATION_SEQUENCE_ID,'
1296 ||' x.BILL_SEQUENCE_ID,'
1297 ||' x.ROUTING_SEQUENCE_ID,'
1298 ||' 2,'
1299 ||' :v_refresh_id,'
1300 ||' :v_instance_id'
1301 ||' FROM MRP_AP_OPERATION_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
1302 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1303 || v_union_sql ;
1304
1305 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1306
1307 EXECUTE IMMEDIATE v_sql_stmt USING 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 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1312 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1313
1314 ELSE
1315
1316 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1317
1318 END IF;
1319
1320 COMMIT;
1321
1322 END IF; -- MSC_CL_PULL.BOM_ENABLED
1323
1324 END LOAD_OPERATION_COMPONENTS;
1325
1326
1327 END MSC_CL_ROUTING_PULL;