[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_BOM_PULL
Source
1 PACKAGE BODY MSC_CL_BOM_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_rounding_Sql varchar2(1000);
15
16 --NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
17 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
18 -- ===============================================================
19
20 PROCEDURE LOAD_BOM IS
21 v_applsys_schema VARCHAR2(32);
22 lv_retval boolean;
23 lv_dummy1 varchar2(32);
24 lv_dummy2 varchar2(32);
25 lv_icode varchar2(3);
26 BEGIN
27 lv_retval := FND_INSTALLATION.GET_APP_INFO (
28 'FND', lv_dummy1, lv_dummy2, v_applsys_schema);
29
30 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
31
32 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
33 v_temp_sql1 := ' x.BASIS_TYPE, x.Old_Component_Sequence_ID,';
34 ELSE
35 v_temp_sql1 := ' NULL, NULL,';
36 END IF;
37
38 --=================== Net Change Mode: Delete ==================
39 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
40
41 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOM_COMPONENTS';
42 MSC_CL_PULL.v_view_name := 'MRP_AD_BOM_COMPONENTS_V';
43
44 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
45 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
46 ELSE
47 v_temp_sql := NULL;
48 END IF;
49
50 v_sql_stmt:=
51 ' insert into MSC_ST_BOM_COMPONENTS'
52 ||'( COMPONENT_SEQUENCE_ID,'
53 ||' BILL_SEQUENCE_ID,'
54 ||' DELETED_FLAG,'
55 ||' REFRESH_ID,'
56 ||' SR_INSTANCE_ID)'
57 ||' select '
58 ||' x.COMPONENT_SEQUENCE_ID,'
59 ||' x.BILL_SEQUENCE_ID,'
60 ||' 1,'
61 ||' :v_refresh_id,'
62 ||' :v_instance_id'
63 ||' from MRP_AD_BOM_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
64 ||' WHERE x.RN> :v_lrn '
65 || v_temp_sql;
66
67
68 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
69
70 COMMIT;
71
72 END IF;
73
74 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOM_COMPONENTS';
75
76
77
78 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
79 v_rounding_sql := 'decode(x.rounding_direction,0,3,1,2,2,1,3),';
80 ELSE
81 v_rounding_sql :='3,';
82 END IF ;
83
84 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
85
86 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
87 MSC_CL_PULL.v_view_name := 'MRP_AN_BOM_COMPONENTS_V';
88 ELSE
89 MSC_CL_PULL.v_view_name := 'MRP_AP_BOM_COMPONENTS_V';
90 END IF;
91
92
93 v_union_sql :=
94 ' AND ( x.RN1> :v_lrn )'
95 ||' UNION '
96 ||' select '
97 ||' x.COMPONENT_SEQUENCE_ID,'
98 ||' x.INVENTORY_ITEM_ID,'
99 ||' x.BILL_SEQUENCE_ID,'
100 ||' x.OPERATION_SEQ_NUM,'
101 ||' x.USAGE_QUANTITY,'
102 ||' x.COMPONENT_YIELD_FACTOR,'
103 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
104 ||' x.DISABLE_DATE- :v_dgmt,'
105 ||' x.OPERATION_OFFSET_PERCENT,'
106 ||' x.OPTIONAL_COMPONENT,'
107 ||' x.WIP_SUPPLY_TYPE,'
108 ||' x.PLANNING_FACTOR,'
109 ||' x.REVISED_ITEM_SEQUENCE_ID,'
110 ||' x.ATP_FLAG,'
111 ||' x.STATUS_TYPE,'
112 ||' x.USE_UP_CODE,'
113 ||' x.CHANGE_NOTICE,'
114 ||' x.ORGANIZATION_ID,'
115 ||' x.USING_ASSEMBLY_ID,'
116 ||' x.FROM_UNIT_NUMBER,'
117 ||' x.TO_UNIT_NUMBER,'
118 ||' x.DRIVING_ITEM_ID,'
119 ||' 2,'
120 || v_rounding_sql
121 || v_temp_sql1
122 ||' :v_refresh_id,'
123 ||' :v_instance_id'
124 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
125 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
126 ||' AND ( x.RN2> :v_lrn )'
127 /* NCP
128 ||' UNION '
129 ||' select '
130 ||' x.COMPONENT_SEQUENCE_ID,'
131 ||' x.INVENTORY_ITEM_ID,'
132 ||' x.BILL_SEQUENCE_ID,'
133 ||' x.USAGE_QUANTITY,'
134 ||' x.COMPONENT_YIELD_FACTOR,'
135 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
136 ||' x.DISABLE_DATE- :v_dgmt,'
137 ||' x.OPERATION_OFFSET_PERCENT,'
138 ||' x.OPTIONAL_COMPONENT,'
139 ||' x.WIP_SUPPLY_TYPE,'
140 ||' x.PLANNING_FACTOR,'
141 ||' x.REVISED_ITEM_SEQUENCE_ID,'
142 ||' x.ATP_FLAG,'
143 ||' x.STATUS_TYPE,'
144 ||' x.USE_UP_CODE,'
145 ||' x.CHANGE_NOTICE,'
146 ||' x.ORGANIZATION_ID,'
147 ||' x.USING_ASSEMBLY_ID,'
148 ||' x.FROM_UNIT_NUMBER,'
149 ||' x.TO_UNIT_NUMBER,'
150 ||' x.DRIVING_ITEM_ID,'
151 ||' 2,'
152 || v_rounding_sql
153 ||' :v_refresh_id,'
154 ||' :v_instance_id'
155 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
156 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
157 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
158 */
159
160 ||' UNION '
161 ||' select '
162 ||' x.COMPONENT_SEQUENCE_ID,'
163 ||' x.INVENTORY_ITEM_ID,'
164 ||' x.BILL_SEQUENCE_ID,'
165 ||' x.OPERATION_SEQ_NUM,'
166 ||' x.USAGE_QUANTITY,'
167 ||' x.COMPONENT_YIELD_FACTOR,'
168 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
169 ||' x.DISABLE_DATE- :v_dgmt,'
170 ||' x.OPERATION_OFFSET_PERCENT,'
171 ||' x.OPTIONAL_COMPONENT,'
172 ||' x.WIP_SUPPLY_TYPE,'
173 ||' x.PLANNING_FACTOR,'
174 ||' x.REVISED_ITEM_SEQUENCE_ID,'
175 ||' x.ATP_FLAG,'
176 ||' x.STATUS_TYPE,'
177 ||' x.USE_UP_CODE,'
178 ||' x.CHANGE_NOTICE,'
179 ||' x.ORGANIZATION_ID,'
180 ||' x.USING_ASSEMBLY_ID,'
181 ||' x.FROM_UNIT_NUMBER,'
182 ||' x.TO_UNIT_NUMBER,'
183 ||' x.DRIVING_ITEM_ID,'
184 ||' 2,'
185 || v_rounding_sql
186 || v_temp_sql1
187 ||' :v_refresh_id,'
188 ||' :v_instance_id'
189 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
190 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
191 ||' AND ( x.RN3> :v_lrn )'
192 ||' UNION '
193 ||' select '
194 ||' x.COMPONENT_SEQUENCE_ID,'
195 ||' x.INVENTORY_ITEM_ID,'
196 ||' x.BILL_SEQUENCE_ID,'
197 ||' x.OPERATION_SEQ_NUM,'
198 ||' x.USAGE_QUANTITY,'
199 ||' x.COMPONENT_YIELD_FACTOR,'
200 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
201 ||' x.DISABLE_DATE- :v_dgmt,'
202 ||' x.OPERATION_OFFSET_PERCENT,'
203 ||' x.OPTIONAL_COMPONENT,'
204 ||' x.WIP_SUPPLY_TYPE,'
205 ||' x.PLANNING_FACTOR,'
206 ||' x.REVISED_ITEM_SEQUENCE_ID,'
207 ||' x.ATP_FLAG,'
208 ||' x.STATUS_TYPE,'
209 ||' x.USE_UP_CODE,'
210 ||' x.CHANGE_NOTICE,'
211 ||' x.ORGANIZATION_ID,'
212 ||' x.USING_ASSEMBLY_ID,'
213 ||' x.FROM_UNIT_NUMBER,'
214 ||' x.TO_UNIT_NUMBER,'
215 ||' x.DRIVING_ITEM_ID,'
216 ||' 2,'
217 || v_rounding_sql
218 || v_temp_sql1
219 ||' :v_refresh_id,'
220 ||' :v_instance_id'
221 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
222 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
223 ||' AND ( x.RN4> :v_lrn )';
224 /*
225 ||' UNION '
226 ||' select '
227 ||' x.COMPONENT_SEQUENCE_ID,'
228 ||' x.INVENTORY_ITEM_ID,'
229 ||' x.BILL_SEQUENCE_ID,'
230 ||' x.USAGE_QUANTITY,'
231 ||' x.COMPONENT_YIELD_FACTOR,'
232 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
233 ||' x.DISABLE_DATE- :v_dgmt,'
234 ||' x.OPERATION_OFFSET_PERCENT,'
235 ||' x.OPTIONAL_COMPONENT,'
236 ||' x.WIP_SUPPLY_TYPE,'
237 ||' x.PLANNING_FACTOR,'
238 ||' x.REVISED_ITEM_SEQUENCE_ID,'
239 ||' x.ATP_FLAG,'
240 ||' x.STATUS_TYPE,'
241 ||' x.USE_UP_CODE,'
242 ||' x.CHANGE_NOTICE,'
243 ||' x.ORGANIZATION_ID,'
244 ||' x.USING_ASSEMBLY_ID,'
245 ||' x.FROM_UNIT_NUMBER,'
246 ||' x.TO_UNIT_NUMBER,'
247 ||' x.DRIVING_ITEM_ID,'
248 ||' 2,'
249 || v_rounding_sql
250 ||' :v_refresh_id,'
251 ||' :v_instance_id'
252 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
253 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
254 ||' AND ( x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
255 */
256
257 ELSE
258 MSC_CL_PULL.v_view_name := 'MRP_AP_BOM_COMPONENTS_V';
259 v_union_sql := ' ';
260
261 END IF;
262 v_sql_stmt:=
263 ' insert into MSC_ST_BOM_COMPONENTS'
264 ||'( COMPONENT_SEQUENCE_ID,'
265 ||' INVENTORY_ITEM_ID,'
266 ||' BILL_SEQUENCE_ID,'
267 ||' OPERATION_SEQ_NUM,'
268 ||' USAGE_QUANTITY,'
269 ||' COMPONENT_YIELD_FACTOR,'
270 ||' EFFECTIVITY_DATE,'
271 ||' DISABLE_DATE,'
272 ||' OPERATION_OFFSET_PERCENT,'
273 ||' OPTIONAL_COMPONENT,'
274 ||' WIP_SUPPLY_TYPE,'
275 ||' PLANNING_FACTOR,'
276 ||' REVISED_ITEM_SEQUENCE_ID,'
277 ||' ATP_FLAG,'
278 ||' STATUS_TYPE,'
279 ||' USE_UP_CODE,'
280 ||' CHANGE_NOTICE,'
281 ||' ORGANIZATION_ID,'
282 ||' USING_ASSEMBLY_ID,'
283 ||' FROM_UNIT_NUMBER,'
284 ||' TO_UNIT_NUMBER,'
285 ||' DRIVING_ITEM_ID,'
286 ||' DELETED_FLAG,'
287 ||' ROUNDING_DIRECTION,'
288 ||' SCALING_TYPE,'
289 ||' OLD_COMPONENT_SEQUENCE_ID,'
290 ||' REFRESH_ID,'
291 ||' SR_INSTANCE_ID)'
292 ||' select '
293 ||' x.COMPONENT_SEQUENCE_ID,'
294 ||' x.INVENTORY_ITEM_ID,'
295 ||' x.BILL_SEQUENCE_ID,'
296 ||' x.OPERATION_SEQ_NUM,'
297 ||' x.USAGE_QUANTITY,'
298 ||' x.COMPONENT_YIELD_FACTOR,'
299 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
300 ||' x.DISABLE_DATE- :v_dgmt,'
301 ||' x.OPERATION_OFFSET_PERCENT,'
302 ||' x.OPTIONAL_COMPONENT,'
303 ||' x.WIP_SUPPLY_TYPE,'
304 ||' x.PLANNING_FACTOR,'
305 ||' x.REVISED_ITEM_SEQUENCE_ID,'
306 ||' x.ATP_FLAG,'
307 ||' x.STATUS_TYPE,'
308 ||' x.USE_UP_CODE,'
309 ||' x.CHANGE_NOTICE,'
310 ||' x.ORGANIZATION_ID,'
311 ||' x.USING_ASSEMBLY_ID,'
312 ||' x.FROM_UNIT_NUMBER,'
313 ||' x.TO_UNIT_NUMBER,'
314 ||' x.DRIVING_ITEM_ID,'
315 ||' 2,'
316 || v_rounding_sql
317 || v_temp_sql1
318 ||' :v_refresh_id,'
319 ||' :v_instance_id'
320 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
321 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
322 || v_union_sql ;
323
324 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
325
326 EXECUTE IMMEDIATE v_sql_stmt
327 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
328 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
329 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
330 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
331 /* MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
332 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
333 */
334
335 ELSE
336
337 EXECUTE IMMEDIATE v_sql_stmt
338 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
339
340
341 END IF;
342
343 COMMIT;
344
345 --=================== Net Change Mode: Delete ==================
346 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
347
348 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOMS';
349 MSC_CL_PULL.v_view_name := 'MRP_AD_BOMS_V';
350
351 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
352 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
353 ELSE
354 v_temp_sql := NULL;
355 END IF;
356
357 v_sql_stmt:=
358 ' insert into MSC_ST_BOMS'
359 ||' ( BILL_SEQUENCE_ID,'
360 ||' DELETED_FLAG,'
361 ||' REFRESH_ID,'
362 ||' SR_INSTANCE_ID)'
363 ||' SELECT'
364 ||' x.BILL_SEQUENCE_ID, '
365 ||' 1,'
366 ||' :v_refresh_id,'
367 ||' :v_instance_id'
368 ||' FROM MRP_AD_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
369 ||' WHERE x.RN> :v_lrn '
370 || v_temp_sql;
371
372 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
373
374 COMMIT;
375
376 END IF;
377
378
379 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOMS';
380 MSC_CL_PULL.v_view_name := 'MRP_AP_BOMS_V';
381
382 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
383
384 v_union_sql :=
385 ' AND ( x.RN1 > :v_lrn )'
386 ||' UNION '
387 ||' SELECT'
388 ||' x.BILL_SEQUENCE_ID, '
389 ||' x.ORGANIZATION_ID, '
390 ||' x.ASSEMBLY_ITEM_ID, '
391 ||' x.ASSEMBLY_TYPE, '
392 ||' x.ALTERNATE_BOM_DESIGNATOR, '
393 ||' x.SPECIFIC_ASSEMBLY_COMMENT, '
394 ||' x.PENDING_FROM_ECN, '
395 ||' x.COMMON_BILL_SEQUENCE_ID, '
396 ||' 2,'
397 ||' :v_refresh_id,'
398 ||' :v_instance_id'
399 ||' FROM MRP_AP_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
400 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
401 ||' AND (x.RN2 > :v_lrn)';
402
403 ELSE
404 v_union_sql := ' ';
405
406 END IF;
407
408
409 v_sql_stmt:=
410 ' insert into MSC_ST_BOMS'
411 ||' ( BILL_SEQUENCE_ID,'
412 ||' ORGANIZATION_ID,'
413 ||' ASSEMBLY_ITEM_ID,'
414 ||' ASSEMBLY_TYPE,'
415 ||' ALTERNATE_BOM_DESIGNATOR,'
416 ||' SPECIFIC_ASSEMBLY_COMMENT,'
417 ||' PENDING_FROM_ECN,'
418 ||' COMMON_BILL_SEQUENCE_ID,'
419 ||' DELETED_FLAG,'
420 ||' REFRESH_ID,'
421 ||' SR_INSTANCE_ID)'
422 ||' SELECT'
423 ||' x.BILL_SEQUENCE_ID, '
424 ||' x.ORGANIZATION_ID, '
425 ||' x.ASSEMBLY_ITEM_ID, '
426 ||' x.ASSEMBLY_TYPE, '
427 ||' x.ALTERNATE_BOM_DESIGNATOR, '
428 ||' x.SPECIFIC_ASSEMBLY_COMMENT, '
429 ||' x.PENDING_FROM_ECN, '
430 ||' x.COMMON_BILL_SEQUENCE_ID, '
431 ||' 2,'
432 ||' :v_refresh_id,'
433 ||' :v_instance_id'
434 ||' FROM MRP_AP_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
435 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
436 || v_union_sql;
437
438 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
439
440 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
441 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
442 ELSE
443
444 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
445
446 END IF;
447
448
449 COMMIT;
450
451 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
452 --=================== Net Change Mode: Delete ==================
453 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
454
455 MSC_CL_PULL.v_table_name:= 'MSC_ST_COMPONENT_SUBSTITUTES';
456 MSC_CL_PULL.v_view_name := 'MRP_AD_SUB_COMPS_V';
457
458 v_sql_stmt:=
459 ' insert into MSC_ST_COMPONENT_SUBSTITUTES'
460 ||' ( BILL_SEQUENCE_ID,'
461 ||' COMPONENT_SEQUENCE_ID,'
462 ||' SUBSTITUTE_ITEM_ID,'
463 ||' DELETED_FLAG,'
464 ||' REFRESH_ID,'
465 ||' SR_INSTANCE_ID)'
466 ||' SELECT'
467 ||' x.BILL_SEQUENCE_ID,'
468 ||' x.COMPONENT_SEQUENCE_ID,'
469 ||' x.SUBSTITUTE_ITEM_ID,'
470 ||' 1,'
471 ||' :v_refresh_id,'
472 ||' :v_instance_id'
473 ||' FROM MRP_AD_SUB_COMPS_V'||MSC_CL_PULL.v_dblink||' x'
474 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
475 ||' AND x.RN > :v_lrn';
476
477 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
478
479 COMMIT;
480
481 END IF;
482
483 END IF;
484
485
486 MSC_CL_PULL.v_table_name:= 'MSC_ST_COMPONENT_SUBSTITUTES';
487 MSC_CL_PULL.v_view_name := 'MRP_AP_COMPONENT_SUBSTITUTES_V';
488
489
490
491 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
492 v_rounding_sql := 'decode(x.rounding_direction,0,3,1,2,2,1,3),';
496
493 v_rounding_sql :='3,';
494 END IF ;
495
497
498 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
499
500 v_union_sql :=
501 ' AND ( x.RN1 > :v_lrn )'
502 ||' UNION '
503 ||' SELECT'
504 ||' x.BILL_SEQUENCE_ID,'
505 ||' x.COMPONENT_SEQUENCE_ID,'
506 ||' x.SUBSTITUTE_ITEM_ID,'
507 ||' x.USAGE_QUANTITY,'
508 ||' x.ORGANIZATION_ID,'
509 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
510 ||' 1, x.Attribute1,'
511 ||' 2, x.Attribute2,'
512 ||' 3, x.Attribute3,'
513 ||' 4, x.Attribute4,'
514 ||' 5, x.Attribute5,'
515 ||' 6, x.Attribute6,'
516 ||' 7, x.Attribute7,'
517 ||' 8, x.Attribute8,'
518 ||' 9, x.Attribute9,'
519 ||' 10, x.Attribute10,'
520 ||' 11, x.Attribute11,'
521 ||' 12, x.Attribute12,'
522 ||' 13, x.Attribute13,'
523 ||' 14, x.Attribute14,'
524 ||' 15, x.Attribute15)),2),'
525 ||' 2,'
526 || v_rounding_sql
527 ||' :v_refresh_id,'
528 ||' :v_instance_id'
529 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
530 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
531 ||' AND (x.RN2 > :v_lrn)'
532 ||' UNION '
533 ||' SELECT'
534 ||' x.BILL_SEQUENCE_ID,'
535 ||' x.COMPONENT_SEQUENCE_ID,'
536 ||' x.SUBSTITUTE_ITEM_ID,'
537 ||' x.USAGE_QUANTITY,'
538 ||' x.ORGANIZATION_ID,'
539 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
540 ||' 1, x.Attribute1,'
541 ||' 2, x.Attribute2,'
542 ||' 3, x.Attribute3,'
543 ||' 4, x.Attribute4,'
544 ||' 5, x.Attribute5,'
545 ||' 6, x.Attribute6,'
546 ||' 7, x.Attribute7,'
547 ||' 8, x.Attribute8,'
548 ||' 9, x.Attribute9,'
549 ||' 10, x.Attribute10,'
550 ||' 11, x.Attribute11,'
551 ||' 12, x.Attribute12,'
552 ||' 13, x.Attribute13,'
553 ||' 14, x.Attribute14,'
554 ||' 15, x.Attribute15)),2),'
555 ||' 2,'
556 || v_rounding_sql
557 ||' :v_refresh_id,'
558 ||' :v_instance_id'
559 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
560 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
561 ||' AND (x.RN3 > :v_lrn)'
562 ||' UNION '
563 ||' SELECT'
564 ||' x.BILL_SEQUENCE_ID,'
565 ||' x.COMPONENT_SEQUENCE_ID,'
566 ||' x.SUBSTITUTE_ITEM_ID,'
567 ||' x.USAGE_QUANTITY,'
568 ||' x.ORGANIZATION_ID,'
569 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
570 ||' 1, x.Attribute1,'
571 ||' 2, x.Attribute2,'
572 ||' 3, x.Attribute3,'
573 ||' 4, x.Attribute4,'
574 ||' 5, x.Attribute5,'
575 ||' 6, x.Attribute6,'
576 ||' 7, x.Attribute7,'
577 ||' 8, x.Attribute8,'
578 ||' 9, x.Attribute9,'
579 ||' 10, x.Attribute10,'
580 ||' 11, x.Attribute11,'
581 ||' 12, x.Attribute12,'
582 ||' 13, x.Attribute13,'
583 ||' 14, x.Attribute14,'
584 ||' 15, x.Attribute15)),2),'
585 ||' 2,'
586 || v_rounding_sql
587 ||' :v_refresh_id,'
588 ||' :v_instance_id'
589 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
590 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
591 ||' AND (x.RN4 > :v_lrn)';
592
593 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
594
595 v_union_sql :=
596 v_union_sql||' UNION '
597 ||' SELECT'
598 ||' x.BILL_SEQUENCE_ID,'
599 ||' x.COMPONENT_SEQUENCE_ID,'
600 ||' x.SUBSTITUTE_ITEM_ID,'
601 ||' x.USAGE_QUANTITY,'
602 ||' x.ORGANIZATION_ID,'
603 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
604 ||' 1, x.Attribute1,'
605 ||' 2, x.Attribute2,'
606 ||' 3, x.Attribute3,'
607 ||' 4, x.Attribute4,'
608 ||' 5, x.Attribute5,'
609 ||' 6, x.Attribute6,'
610 ||' 7, x.Attribute7,'
611 ||' 8, x.Attribute8,'
612 ||' 9, x.Attribute9,'
613 ||' 10, x.Attribute10,'
614 ||' 11, x.Attribute11,'
615 ||' 12, x.Attribute12,'
616 ||' 13, x.Attribute13,'
617 ||' 14, x.Attribute14,'
618 ||' 15, x.Attribute15)),2),'
619 ||' 2,'
620 || v_rounding_sql
621 ||' :v_refresh_id,'
622 ||' :v_instance_id'
623 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
624 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
625 ||' AND (x.RN5 > :v_lrn)';
626
627
628 END IF;
629
630 ELSE
631 v_union_sql := ' ';
632
633 END IF;
634
635 v_sql_stmt:=
636 ' INSERT INTO MSC_ST_COMPONENT_SUBSTITUTES'
637 ||' ( BILL_SEQUENCE_ID,'
638 ||' COMPONENT_SEQUENCE_ID,'
639 ||' SUBSTITUTE_ITEM_ID,'
640 ||' USAGE_QUANTITY,'
641 ||' ORGANIZATION_ID,'
642 ||' PRIORITY,'
643 ||' DELETED_FLAG,'
644 ||' ROUNDING_DIRECTION,'
645 ||' REFRESH_ID,'
646 ||' SR_INSTANCE_ID)'
647 ||' SELECT'
648 ||' x.BILL_SEQUENCE_ID,'
649 ||' x.COMPONENT_SEQUENCE_ID,'
650 ||' x.SUBSTITUTE_ITEM_ID,'
651 ||' x.USAGE_QUANTITY,'
652 ||' x.ORGANIZATION_ID,'
653 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
654 ||' 1, x.Attribute1,'
655 ||' 2, x.Attribute2,'
656 ||' 3, x.Attribute3,'
657 ||' 4, x.Attribute4,'
658 ||' 5, x.Attribute5,'
662 ||' 9, x.Attribute9,'
659 ||' 6, x.Attribute6,'
660 ||' 7, x.Attribute7,'
661 ||' 8, x.Attribute8,'
663 ||' 10, x.Attribute10,'
664 ||' 11, x.Attribute11,'
665 ||' 12, x.Attribute12,'
666 ||' 13, x.Attribute13,'
667 ||' 14, x.Attribute14,'
668 ||' 15, x.Attribute15)),2),'
669 ||' 2,'
670 || v_rounding_sql
671 ||' :v_refresh_id,'
672 ||' :v_instance_id'
673 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
674 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
675 || v_union_sql;
676
677
678 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
679
680 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
681
682 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
683 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
684 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
685 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
686 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
687 ELSE
688
689 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
690 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
691 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
692 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
693 END IF;
694
695 ELSE
696
697 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
698
699 END IF;
700
701
702 COMMIT;
703
704
705 if MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN /*osfm change*/
706 select instance_code into lv_icode from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
707 BEGIN
708
709 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
710 application_short_name => 'MSC',
711 statement_type => AD_DDL.CREATE_INDEX,
712 statement =>
713 'create index MSC_ST_BOM_COMPONENTS_'||lv_icode
714 ||' ON MSC_ST_BOM_COMPONENTS (BILL_SEQUENCE_ID,SR_INSTANCE_ID) '
715 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
716 object_name => 'MSC_ST_BOM_COMPONENTS_'||lv_icode);
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
721
722 END;
723 msc_analyse_tables_pk.analyse_table( 'MSC_ST_BOM_COMPONENTS');
724
725 LOAD_CO_PRODUCT_BOMS;
726
727 BEGIN
728 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
729 application_short_name => 'MSC',
730 statement_type => AD_DDL.DROP_INDEX,
731 statement =>
732 'drop index MSC_ST_BOM_COMPONENTS_'||lv_icode,
733 object_name => 'MSC_ST_BOM_COMPONENTS_'||lv_icode);
734 EXCEPTION
735 WHEN OTHERS THEN
736 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
737
738 END;
739
740 End If;
741
742
743 END IF; -- MSC_CL_PULL.BOM_ENABLED
744
745 END LOAD_BOM;
746
747
748 PROCEDURE LOAD_PROCESS_EFFECTIVITY IS
749 v_get_apps_ver number;
750 BEGIN
751
752 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
753 --=================== Net Change Mode: Delete ==================
754 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
755
756 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROCESS_EFFECTIVITY';
757 MSC_CL_PULL.v_view_name := 'MRP_AD_PROCESS_EFFECTIVITY_V';
758
759 v_sql_stmt:=
760 ' INSERT INTO MSC_ST_PROCESS_EFFECTIVITY'
761 ||' ( ITEM_ID,'
762 ||' ORGANIZATION_ID,'
763 ||' BILL_SEQUENCE_ID,'
764 ||' ROUTING_SEQUENCE_ID,'
765 ||' LINE_ID,'
766 ||' DELETED_FLAG,'
767 ||' REFRESH_ID,'
768 ||' SR_INSTANCE_ID)'
769 ||' SELECT'
770 ||' x.INVENTORY_ITEM_ID,'
771 ||' x.ORGANIZATION_ID,'
772 ||' x.BILL_SEQUENCE_ID,'
773 ||' x.ROUTING_SEQUENCE_ID,'
774 ||' x.LINE_ID,'
775 ||' 1,'
776 ||' :v_refresh_id,'
777 ||' :v_instance_id'
778 ||' FROM MRP_AD_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
779 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
780 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
781
782 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
783
784 COMMIT;
785
786 END IF;
787
788 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROCESS_EFFECTIVITY';
789 MSC_CL_PULL.v_view_name := 'MRP_AP_PROCESS_EFFECTIVITY_V';
790 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
791 v_union_sql :=
792 ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
793 /*
794 ||' UNION '
795 ||' SELECT'
796 ||' x.INVENTORY_ITEM_ID,'
797 ||' x.ORGANIZATION_ID,'
798 ||' x.BILL_SEQUENCE_ID,'
799 ||' x.ROUTING_SEQUENCE_ID,'
800 ||' x.EFFECTIVITY_DATE,'
801 ||' x.LINE_ID,'
802 ||' x.PREFERENCE,'
806 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
803 ||' x.PRIMARY_LINE_FLAG,'
804 ||' x.PRODUCTION_LINE_RATE,'
805 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
807 ||' 1, x.Attribute1,'
808 ||' 2, x.Attribute2,'
809 ||' 3, x.Attribute3,'
810 ||' 4, x.Attribute4,'
811 ||' 5, x.Attribute5,'
812 ||' 6, x.Attribute6,'
813 ||' 7, x.Attribute7,'
814 ||' 8, x.Attribute8,'
815 ||' 9, x.Attribute9,'
816 ||' 10, x.Attribute10,'
817 ||' 11, x.Attribute11,'
818 ||' 12, x.Attribute12,'
819 ||' 13, x.Attribute13,'
820 ||' 14, x.Attribute14,'
821 ||' 15, x.Attribute15)),0),'
822 ||' 2,'
823 ||' :v_refresh_id,'
824 ||' :v_instance_id'
825 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
826 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
827 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
828 */
829
830 ||' UNION '
831 ||' SELECT'
832 ||' x.INVENTORY_ITEM_ID,'
833 ||' x.ORGANIZATION_ID,'
834 ||' x.BILL_SEQUENCE_ID,'
835 ||' x.ROUTING_SEQUENCE_ID,'
836 ||' x.EFFECTIVITY_DATE,'
837 ||' x.LINE_ID,'
838 ||' x.PREFERENCE,'
839 ||' x.PRIMARY_LINE_FLAG,'
840 ||' x.PRODUCTION_LINE_RATE,'
841 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
842 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
843 ||' 1, x.Attribute1,'
844 ||' 2, x.Attribute2,'
845 ||' 3, x.Attribute3,'
846 ||' 4, x.Attribute4,'
847 ||' 5, x.Attribute5,'
848 ||' 6, x.Attribute6,'
849 ||' 7, x.Attribute7,'
850 ||' 8, x.Attribute8,'
851 ||' 9, x.Attribute9,'
852 ||' 10, x.Attribute10,'
853 ||' 11, x.Attribute11,'
854 ||' 12, x.Attribute12,'
855 ||' 13, x.Attribute13,'
856 ||' 14, x.Attribute14,'
857 ||' 15, x.Attribute15)),0),'
858 ||' 2,'
859 ||' :v_refresh_id,'
860 ||' :v_instance_id'
861 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
862 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
863 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
864 ||' UNION '
865 ||' SELECT'
866 ||' x.INVENTORY_ITEM_ID,'
867 ||' x.ORGANIZATION_ID,'
868 ||' x.BILL_SEQUENCE_ID,'
869 ||' x.ROUTING_SEQUENCE_ID,'
870 ||' x.EFFECTIVITY_DATE,'
871 ||' x.LINE_ID,'
872 ||' x.PREFERENCE,'
873 ||' x.PRIMARY_LINE_FLAG,'
874 ||' x.PRODUCTION_LINE_RATE,'
875 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
876 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
877 ||' 1, x.Attribute1,'
878 ||' 2, x.Attribute2,'
879 ||' 3, x.Attribute3,'
880 ||' 4, x.Attribute4,'
881 ||' 5, x.Attribute5,'
882 ||' 6, x.Attribute6,'
883 ||' 7, x.Attribute7,'
884 ||' 8, x.Attribute8,'
885 ||' 9, x.Attribute9,'
886 ||' 10, x.Attribute10,'
887 ||' 11, x.Attribute11,'
888 ||' 12, x.Attribute12,'
889 ||' 13, x.Attribute13,'
890 ||' 14, x.Attribute14,'
891 ||' 15, x.Attribute15)),0),'
892 ||' 2,'
893 ||' :v_refresh_id,'
894 ||' :v_instance_id'
895 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
896 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
897 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')';
898 /*
899 ||' UNION '
900 ||' SELECT'
901 ||' x.INVENTORY_ITEM_ID,'
902 ||' x.ORGANIZATION_ID,'
903 ||' x.BILL_SEQUENCE_ID,'
904 ||' x.ROUTING_SEQUENCE_ID,'
905 ||' x.EFFECTIVITY_DATE,'
906 ||' x.LINE_ID,'
907 ||' x.PREFERENCE,'
908 ||' x.PRIMARY_LINE_FLAG,'
909 ||' x.PRODUCTION_LINE_RATE,'
910 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
911 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
912 ||' 1, x.Attribute1,'
913 ||' 2, x.Attribute2,'
914 ||' 3, x.Attribute3,'
915 ||' 4, x.Attribute4,'
916 ||' 5, x.Attribute5,'
917 ||' 6, x.Attribute6,'
918 ||' 7, x.Attribute7,'
919 ||' 8, x.Attribute8,'
920 ||' 9, x.Attribute9,'
921 ||' 10, x.Attribute10,'
922 ||' 11, x.Attribute11,'
923 ||' 12, x.Attribute12,'
924 ||' 13, x.Attribute13,'
925 ||' 14, x.Attribute14,'
926 ||' 15, x.Attribute15)),0),'
927 ||' 2,'
928 ||' :v_refresh_id,'
929 ||' :v_instance_id'
930 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
931 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
932 ||' AND ( x.RN5>'||MSC_CL_PULL.v_lrn||')' ;
933 */
934 ELSE
935 v_union_sql :=
936 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
937 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
938 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
939 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
940
941 END IF;
942
943 v_sql_stmt:=
944 ' INSERT INTO MSC_ST_PROCESS_EFFECTIVITY'
945 ||' ( ITEM_ID,'
946 ||' ORGANIZATION_ID,'
947 ||' BILL_SEQUENCE_ID,'
948 ||' ROUTING_SEQUENCE_ID,'
949 ||' EFFECTIVITY_DATE,'
950 ||' LINE_ID,'
951 ||' PREFERENCE,'
952 ||' PRIMARY_LINE_FLAG,'
953 ||' PRODUCTION_LINE_RATE,'
954 ||' LOAD_DISTRIBUTION_PRIORITY,'
955 ||' ITEM_PROCESS_COST,'
956 ||' DELETED_FLAG,'
957 ||' REFRESH_ID,'
958 ||' SR_INSTANCE_ID)'
959 ||' SELECT'
960 ||' x.INVENTORY_ITEM_ID,'
961 ||' x.ORGANIZATION_ID,'
962 ||' x.BILL_SEQUENCE_ID,'
966 ||' x.PREFERENCE,'
963 ||' x.ROUTING_SEQUENCE_ID,'
964 ||' x.EFFECTIVITY_DATE,'
965 ||' x.LINE_ID,'
967 ||' x.PRIMARY_LINE_FLAG,'
968 ||' x.PRODUCTION_LINE_RATE,'
969 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
970 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
971 ||' 1, x.Attribute1,'
972 ||' 2, x.Attribute2,'
973 ||' 3, x.Attribute3,'
974 ||' 4, x.Attribute4,'
975 ||' 5, x.Attribute5,'
976 ||' 6, x.Attribute6,'
977 ||' 7, x.Attribute7,'
978 ||' 8, x.Attribute8,'
979 ||' 9, x.Attribute9,'
980 ||' 10, x.Attribute10,'
981 ||' 11, x.Attribute11,'
982 ||' 12, x.Attribute12,'
983 ||' 13, x.Attribute13,'
984 ||' 14, x.Attribute14,'
985 ||' 15, x.Attribute15)),0),'
986 ||' 2,'
987 ||' :v_refresh_id,'
988 ||' :v_instance_id'
989 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
990 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
991 || v_union_sql ;
992
993 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
994
995 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_alt_bom_cost,
996 MSC_CL_PULL.v_refresh_id,
997 MSC_CL_PULL.v_instance_id,
998 MSC_CL_PULL.v_msc_alt_bom_cost,
999 MSC_CL_PULL.v_refresh_id,
1000 MSC_CL_PULL.v_instance_id,
1001 MSC_CL_PULL.v_msc_alt_bom_cost,
1002 MSC_CL_PULL.v_refresh_id,
1003 MSC_CL_PULL.v_instance_id;
1004
1005 /*
1006 MSC_CL_PULL.v_msc_alt_bom_cost,
1007 MSC_CL_PULL.v_refresh_id,
1008 MSC_CL_PULL.v_instance_id,
1009 MSC_CL_PULL.v_msc_alt_bom_cost,
1010 MSC_CL_PULL.v_refresh_id,
1011 MSC_CL_PULL.v_instance_id;
1012 */
1013
1014 ELSE
1015 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_alt_bom_cost,
1016 MSC_CL_PULL.v_refresh_id,
1017 MSC_CL_PULL.v_instance_id;
1018 END IF;
1019 COMMIT;
1020
1021 END IF; -- MSC_CL_PULL.BOM_ENABLED
1022
1023 END LOAD_PROCESS_EFFECTIVITY;
1024
1025
1026 -- ===============================================================
1027
1028 PROCEDURE LOAD_BOR IS
1029 BEGIN
1030
1031 MSC_CL_PULL.v_table_name:= 'MSC_ST_BILL_OF_RESOURCES';
1032 MSC_CL_PULL.v_view_name := 'MRP_AP_BILL_OF_RESOURCES_V';
1033
1034 v_sql_stmt:=
1035 ' insert into MSC_ST_BILL_OF_RESOURCES'
1036 ||' ( BILL_OF_RESOURCES,'
1037 ||' ORGANIZATION_ID,'
1038 ||' DESCRIPTION,'
1039 ||' DISABLE_DATE,'
1040 ||' ROLLUP_START_DATE,'
1041 ||' ROLLUP_COMPLETION_DATE,'
1042 ||' DELETED_FLAG,'
1043 ||' REFRESH_ID,'
1044 ||' SR_INSTANCE_ID)'
1045 ||' select'
1046 ||' x.BILL_OF_RESOURCES,'
1047 ||' x.ORGANIZATION_ID,'
1048 ||' x.DESCRIPTION,'
1049 ||' x.DISABLE_DATE- :v_dgmt,'
1050 ||' x.ROLLUP_START_DATE- :v_dgmt,'
1051 ||' x.ROLLUP_COMPLETION_DATE- :v_dgmt,'
1052 ||' 2,'
1053 ||' :v_refresh_id,'
1054 ||' :v_instance_id'
1055 ||' from MRP_AP_BILL_OF_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1056 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1057 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1058 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1059
1060 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1061
1062 COMMIT;
1063
1064 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOR_REQUIREMENTS';
1065 MSC_CL_PULL.v_view_name := 'MRP_AP_CRP_RESOURCE_HOURS_V';
1066
1067 v_sql_stmt:=
1068 ' insert into MSC_ST_BOR_REQUIREMENTS'
1069 ||' ( BILL_OF_RESOURCES,'
1070 ||' ORGANIZATION_ID,'
1071 ||' ASSEMBLY_ITEM_ID,'
1072 ||' SOURCE_ITEM_ID,'
1073 ||' RESOURCE_ID,'
1074 ||' RESOURCE_DEPARTMENT_HOURS,'
1075 ||' OPERATION_SEQUENCE_ID,'
1076 ||' OPERATION_SEQ_NUM,'
1077 ||' RESOURCE_SEQ_NUM,'
1078 ||' SETBACK_DAYS,'
1079 ||' DEPARTMENT_ID,'
1080 ||' ASSEMBLY_USAGE,'
1081 ||' ORIGINATION_TYPE,'
1082 ||' RESOURCE_UNITS,'
1083 ||' BASIS,'
1084 ||' DELETED_FLAG,'
1085 ||' REFRESH_ID,'
1086 ||' SR_INSTANCE_ID)'
1087 ||' select'
1088 ||' x.BILL_OF_RESOURCES,'
1089 ||' x.ORGANIZATION_ID,'
1090 ||' x.ASSEMBLY_ITEM_ID,'
1091 ||' x.SOURCE_ITEM_ID,'
1092 ||' x.RESOURCE_ID,'
1093 ||' x.RESOURCE_DEPARTMENT_HOURS,'
1094 ||' x.OPERATION_SEQUENCE_ID,'
1095 ||' x.OPERATION_SEQ_NUM,'
1096 ||' x.RESOURCE_SEQ_NUM,'
1097 ||' x.SETBACK_DAYS,'
1098 ||' NVL(x.DEPARTMENT_ID, x.LINE_ID),'
1099 ||' NVL(x.ASSEMBLY_USAGE,1),'
1100 ||' x.ORIGINATION_TYPE,'
1101 ||' NVL(x.RESOURCE_UNITS,1),'
1102 ||' x.BASIS,'
1103 ||' 2,'
1104 ||' :v_refresh_id,'
1105 ||' :v_instance_id'
1106 ||' from MRP_AP_CRP_RESOURCE_HOURS_V'||MSC_CL_PULL.v_dblink||' x'
1107 ||' WHERE DECODE( :WIP_ENABLED, 2, LINE_ID) IS NULL'
1108 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1109 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1110 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1111 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1112
1113 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.WIP_ENABLED;
1114
1115 COMMIT;
1116
1117 END LOAD_BOR;
1118
1119
1123
1120 --==================================================================
1121
1122 PROCEDURE LOAD_RESOURCE IS
1124 -- for the fix 2490553 (WPS Integration)
1125 lv_inflate_wip NUMBER;
1126 v_res_hrs_left VARCHAR2(400);
1127 v_res_hrs_sql VARCHAR2(300);
1128 v_temp_parent_seq VARCHAR2(500);
1129 v_temp_atp_rule_sql VARCHAR2(400);
1130 v_temp_atp_rule_sql1 VARCHAR2(400);
1131 lv_lbj_details NUMBER :=0;
1132 lv_cond_sql VARCHAR2(100) := null;
1133 lv_op_seq_num VARCHAR2(100) := null;
1134 lv_hint VARCHAR2(300);
1135 v_touch_time VARCHAR2(300);
1136 lv_qty_sql_temp VARCHAR2(300);
1137
1138
1139 BEGIN
1140
1141 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPARTMENT_RESOURCES';
1142 MSC_CL_PULL.v_view_name := 'MRP_AP_DEPARTMENT_RESOURCES_V';
1143 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1144 v_temp_atp_rule_sql := 'x.ATP_RULE_ID,x.SCHEDULE_TO_INSTANCE,x.BATCHING_PENALTY,x.SETUP_TIME_PERCENT,x.UTILIZATION_CHANGE_PERCENT,x.SETUP_TIME_TYPE,x.UTILIZATION_CHANGE_TYPE,x.IDLE_TIME_TOLERANCE,x.SDS_SCHEDULING_WINDOW,';
1145 ELSE
1146 v_temp_atp_rule_sql := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1147 END IF;
1148 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1149 v_temp_atp_rule_sql1 := 'x.ATP_RULE_ID,x.SETUP_TIME_PERCENT,x.UTILIZATION_CHANGE_PERCENT,x.SETUP_TIME_TYPE,x.UTILIZATION_CHANGE_TYPE,';
1150 ELSE
1151 v_temp_atp_rule_sql1 := 'NULL,NULL,NULL,NULL,NULL,';
1152 END IF;
1153
1154
1155 /* The code is forked since the Resource Batching flexfields are replaced by the columns
1156 in Patchset - G for 11i source only
1157 */
1158
1159 IF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
1160 v_sql_stmt:=
1161 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1162 ||' ( RESOURCE_ID,'
1163 ||' DEPARTMENT_ID,'
1164 ||' LINE_FLAG,'
1165 ||' OWNING_DEPARTMENT_ID,'
1166 ||' CAPACITY_UNITS,'
1167 ||' RESOURCE_GROUP_NAME,'
1168 ||' ORGANIZATION_ID,'
1169 ||' DEPARTMENT_CODE,'
1170 ||' DEPARTMENT_CLASS,'
1171 ||' DEPARTMENT_DESCRIPTION,'
1172 ||' RESOURCE_CODE,'
1173 ||' RESOURCE_DESCRIPTION,'
1174 ||' OVER_UTILIZED_PERCENT,'
1175 ||' UNDER_UTILIZED_PERCENT,'
1176 ||' RESOURCE_SHORTAGE_TYPE,'
1177 ||' RESOURCE_EXCESS_TYPE,'
1178 ||' PLANNING_EXCEPTION_SET,'
1179 ||' USER_TIME_FENCE,'
1180 ||' AGGREGATED_RESOURCE_FLAG,'
1181 ||' AGGREGATED_RESOURCE_ID,'
1182 ||' RESOURCE_TYPE,'
1183 ||' DISABLE_DATE,'
1184 ||' AVAILABLE_24_HOURS_FLAG,'
1185 ||' CTP_FLAG,'
1186 ||' UTILIZATION,'
1187 ||' EFFICIENCY,'
1188 ||' BATCHABLE_FLAG,'
1189 ||' BATCHING_WINDOW,'
1190 ||' MIN_CAPACITY,'
1191 ||' MAX_CAPACITY,'
1192 ||' UNIT_OF_MEASURE,'
1193 ||' RESOURCE_COST,'
1194 ||' RESOURCE_OVER_UTIL_COST,'
1195 ||' DEPT_OVERHEAD_COST,'
1196 ||' DELETED_FLAG,'
1197 ||' REFRESH_ID,'
1198 ||' ATP_RULE_ID,'
1199 ||' SCHEDULE_TO_INSTANCE,' /* ds change */
1200 ||' BATCHING_PENALTY,'
1201 ||' SETUP_TIME_PERCENT,'
1202 ||' UTILIZATION_CHANGE_PERCENT,'
1203 ||' SETUP_TIME_TYPE,'
1204 ||' UTILIZATION_CHANGE_TYPE,'
1205 ||' IDLE_TIME_TOLERANCE,'
1206 ||' SDS_SCHEDULING_WINDOW,' /* ds change */
1207 ||' SR_INSTANCE_ID)'
1208 ||' select'
1209 ||' x.RESOURCE_ID,'
1210 ||' x.DEPARTMENT_ID,'
1211 ||' x.LINE_FLAG,'
1212 ||' x.OWNING_DEPARTMENT_ID,'
1213 ||' x.CAPACITY_UNITS,'
1214 ||' x.RESOURCE_GROUP_NAME,'
1215 ||' x.ORGANIZATION_ID,'
1216 ||' x.DEPARTMENT_CODE,'
1217 ||' x.DEPARTMENT_CLASS,'
1218 ||' x.DEPARTMENT_DESCRIPTION,'
1219 ||' x.RESOURCE_CODE,'
1220 ||' x.RESOURCE_DESCRIPTION,'
1221 ||' x.OVER_UTILIZED_PERCENT,'
1222 ||' x.UNDER_UTILIZED_PERCENT,'
1223 ||' x.RESOURCE_SHORTAGE_TYPE,'
1224 ||' x.RESOURCE_EXCESS_TYPE,'
1225 ||' x.PLANNING_EXCEPTION_SET,'
1226 ||' x.USER_TIME_FENCE,'
1227 ||' DECODE( TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1228 ||' 1, x.Attribute1,'
1229 ||' 2, x.Attribute2,'
1230 ||' 3, x.Attribute3,'
1231 ||' 4, x.Attribute4,'
1232 ||' 5, x.Attribute5,'
1233 ||' 6, x.Attribute6,'
1234 ||' 7, x.Attribute7,'
1235 ||' 8, x.Attribute8,'
1236 ||' 9, x.Attribute9,'
1237 ||' 10, x.Attribute10,'
1238 ||' 11, x.Attribute11,'
1239 ||' 12, x.Attribute12,'
1240 ||' 13, x.Attribute13,'
1241 ||' 14, x.Attribute14,'
1242 ||' 15, x.Attribute15)*2),'
1243 ||' x.RESOURCE_ID, 1,'
1244 ||' 2),'
1245 ||' TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1246 ||' 1, x.Attribute1,'
1247 ||' 2, x.Attribute2,'
1248 ||' 3, x.Attribute3,'
1249 ||' 4, x.Attribute4,'
1250 ||' 5, x.Attribute5,'
1251 ||' 6, x.Attribute6,'
1252 ||' 7, x.Attribute7,'
1253 ||' 8, x.Attribute8,'
1254 ||' 9, x.Attribute9,'
1255 ||' 10, x.Attribute10,'
1256 ||' 11, x.Attribute11,'
1257 ||' 12, x.Attribute12,'
1258 ||' 13, x.Attribute13,'
1259 ||' 14, x.Attribute14,'
1260 ||' 15, x.Attribute15)*2),'
1261 ||' x.RESOURCE_TYPE,'
1262 ||' x.DISABLE_DATE- :v_dgmt,'
1263 ||' x.AVAILABLE_24_HOURS_FLAG,'
1264 ||' x.CTP_FLAG,'
1265 ||' x.UTILIZATION,'
1266 ||' x.EFFICIENCY,'
1267 ||' x.BATCHABLE,'
1268 ||' x.BATCH_WINDOW,'
1269 ||' x.MIN_BATCH_CAPACITY,'
1270 ||' x.MAX_BATCH_CAPACITY,'
1274 ||' 1, x.Attribute1,'
1271 ||' x.BATCH_CAPACITY_UOM,'
1272 ||' x.RESOURCE_COST,'
1273 ||' TO_NUMBER(DECODE( :v_mso_res_penalty,'
1275 ||' 2, x.Attribute2,'
1276 ||' 3, x.Attribute3,'
1277 ||' 4, x.Attribute4,'
1278 ||' 5, x.Attribute5,'
1279 ||' 6, x.Attribute6,'
1280 ||' 7, x.Attribute7,'
1281 ||' 8, x.Attribute8,'
1282 ||' 9, x.Attribute9,'
1283 ||' 10, x.Attribute10,'
1284 ||' 11, x.Attribute11,'
1285 ||' 12, x.Attribute12,'
1286 ||' 13, x.Attribute13,'
1287 ||' 14, x.Attribute14,'
1288 ||' 15, x.Attribute15)),'
1289 ||' x.DEPT_OVERHEAD_COST,'
1290 ||' 2,'
1291 ||' :v_refresh_id,'
1292 || v_temp_atp_rule_sql
1293 ||' :v_instance_id'
1294 ||' from MRP_AP_DEPARTMENT_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1295 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1296
1297 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_aggreg_res_name,
1298 MSC_CL_PULL.v_msc_aggreg_res_name,
1299 MSC_CL_PULL.v_dgmt,
1300 MSC_CL_PULL.v_mso_res_penalty,
1301 MSC_CL_PULL.v_refresh_id,
1302 MSC_CL_PULL.v_instance_id;
1303 ELSE
1304 v_sql_stmt:=
1305 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1306 ||' ( RESOURCE_ID,'
1307 ||' DEPARTMENT_ID,'
1308 ||' LINE_FLAG,'
1309 ||' OWNING_DEPARTMENT_ID,'
1310 ||' CAPACITY_UNITS,'
1311 ||' RESOURCE_GROUP_NAME,'
1312 ||' ORGANIZATION_ID,'
1313 ||' DEPARTMENT_CODE,'
1314 ||' DEPARTMENT_CLASS,'
1315 ||' DEPARTMENT_DESCRIPTION,'
1316 ||' RESOURCE_CODE,'
1317 ||' RESOURCE_DESCRIPTION,'
1318 ||' OVER_UTILIZED_PERCENT,'
1319 ||' UNDER_UTILIZED_PERCENT,'
1320 ||' RESOURCE_SHORTAGE_TYPE,'
1321 ||' RESOURCE_EXCESS_TYPE,'
1322 ||' PLANNING_EXCEPTION_SET,'
1323 ||' USER_TIME_FENCE,'
1324 ||' AGGREGATED_RESOURCE_FLAG,'
1325 ||' AGGREGATED_RESOURCE_ID,'
1326 ||' RESOURCE_TYPE,'
1327 ||' DISABLE_DATE,'
1328 ||' AVAILABLE_24_HOURS_FLAG,'
1329 ||' CTP_FLAG,'
1330 ||' UTILIZATION,'
1331 ||' EFFICIENCY,'
1332 ||' BATCHABLE_FLAG,'
1333 ||' BATCHING_WINDOW,'
1334 ||' MIN_CAPACITY,'
1335 ||' MAX_CAPACITY,'
1336 ||' UNIT_OF_MEASURE,'
1337 ||' RESOURCE_COST,'
1338 ||' RESOURCE_OVER_UTIL_COST,'
1339 ||' DEPT_OVERHEAD_COST,'
1340 ||' DELETED_FLAG,'
1341 ||' REFRESH_ID,'
1342 ||' ATP_RULE_ID,'
1343 ||' SCHEDULE_TO_INSTANCE,' /* ds change */
1344 ||' BATCHING_PENALTY,'
1345 ||' SETUP_TIME_PERCENT,'
1346 ||' UTILIZATION_CHANGE_PERCENT,'
1347 ||' SETUP_TIME_TYPE,'
1348 ||' UTILIZATION_CHANGE_TYPE,'
1349 ||' IDLE_TIME_TOLERANCE,'
1350 ||' SDS_SCHEDULING_WINDOW,' /* ds change */
1351 ||' SR_INSTANCE_ID)'
1352 ||' select'
1353 ||' x.RESOURCE_ID,'
1354 ||' x.DEPARTMENT_ID,'
1355 ||' x.LINE_FLAG,'
1356 ||' x.OWNING_DEPARTMENT_ID,'
1357 ||' x.CAPACITY_UNITS,'
1358 ||' x.RESOURCE_GROUP_NAME,'
1359 ||' x.ORGANIZATION_ID,'
1360 ||' x.DEPARTMENT_CODE,'
1361 ||' x.DEPARTMENT_CLASS,'
1362 ||' x.DEPARTMENT_DESCRIPTION,'
1363 ||' x.RESOURCE_CODE,'
1364 ||' x.RESOURCE_DESCRIPTION,'
1365 ||' x.OVER_UTILIZED_PERCENT,'
1366 ||' x.UNDER_UTILIZED_PERCENT,'
1367 ||' x.RESOURCE_SHORTAGE_TYPE,'
1368 ||' x.RESOURCE_EXCESS_TYPE,'
1369 ||' x.PLANNING_EXCEPTION_SET,'
1370 ||' x.USER_TIME_FENCE,'
1371 ||' DECODE( TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1372 ||' 1, x.Attribute1,'
1373 ||' 2, x.Attribute2,'
1374 ||' 3, x.Attribute3,'
1375 ||' 4, x.Attribute4,'
1376 ||' 5, x.Attribute5,'
1377 ||' 6, x.Attribute6,'
1378 ||' 7, x.Attribute7,'
1379 ||' 8, x.Attribute8,'
1380 ||' 9, x.Attribute9,'
1381 ||' 10, x.Attribute10,'
1382 ||' 11, x.Attribute11,'
1383 ||' 12, x.Attribute12,'
1384 ||' 13, x.Attribute13,'
1385 ||' 14, x.Attribute14,'
1386 ||' 15, x.Attribute15)),'
1387 ||' x.RESOURCE_ID, 1,'
1388 ||' 2),'
1389 ||' TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1390 ||' 1, x.Attribute1,'
1391 ||' 2, x.Attribute2,'
1392 ||' 3, x.Attribute3,'
1393 ||' 4, x.Attribute4,'
1394 ||' 5, x.Attribute5,'
1395 ||' 6, x.Attribute6,'
1396 ||' 7, x.Attribute7,'
1397 ||' 8, x.Attribute8,'
1398 ||' 9, x.Attribute9,'
1399 ||' 10, x.Attribute10,'
1400 ||' 11, x.Attribute11,'
1401 ||' 12, x.Attribute12,'
1402 ||' 13, x.Attribute13,'
1403 ||' 14, x.Attribute14,'
1404 ||' 15, x.Attribute15)),'
1405 ||' x.RESOURCE_TYPE,'
1406 ||' x.DISABLE_DATE- :v_dgmt,'
1407 ||' x.AVAILABLE_24_HOURS_FLAG,'
1408 ||' x.CTP_FLAG,'
1409 ||' x.UTILIZATION,'
1410 ||' x.EFFICIENCY,'
1411 ||' TO_NUMBER(DECODE( :v_msc_batchable_flag,'
1412 ||' 1, x.Attribute1,'
1413 ||' 2, x.Attribute2,'
1414 ||' 3, x.Attribute3,'
1415 ||' 4, x.Attribute4,'
1416 ||' 5, x.Attribute5,'
1417 ||' 6, x.Attribute6,'
1418 ||' 7, x.Attribute7,'
1419 ||' 8, x.Attribute8,'
1420 ||' 9, x.Attribute9,'
1421 ||' 10, x.Attribute10,'
1425 ||' 14, x.Attribute14,'
1422 ||' 11, x.Attribute11,'
1423 ||' 12, x.Attribute12,'
1424 ||' 13, x.Attribute13,'
1426 ||' 15, x.Attribute15,Null)) ,'
1427 ||' TO_NUMBER(DECODE( :v_msc_batching_window,'
1428 ||' 1, x.Attribute1,'
1429 ||' 2, x.Attribute2,'
1430 ||' 3, x.Attribute3,'
1431 ||' 4, x.Attribute4,'
1432 ||' 5, x.Attribute5,'
1433 ||' 6, x.Attribute6,'
1434 ||' 7, x.Attribute7,'
1435 ||' 8, x.Attribute8,'
1436 ||' 9, x.Attribute9,'
1437 ||' 10, x.Attribute10,'
1438 ||' 11, x.Attribute11,'
1439 ||' 12, x.Attribute12,'
1440 ||' 13, x.Attribute13,'
1441 ||' 14, x.Attribute14,'
1442 ||' 15, x.Attribute15,Null)) ,'
1443 ||' TO_NUMBER(DECODE( :v_msc_min_capacity,'
1444 ||' 1, x.Attribute1,'
1445 ||' 2, x.Attribute2,'
1446 ||' 3, x.Attribute3,'
1447 ||' 4, x.Attribute4,'
1448 ||' 5, x.Attribute5,'
1449 ||' 6, x.Attribute6,'
1450 ||' 7, x.Attribute7,'
1451 ||' 8, x.Attribute8,'
1452 ||' 9, x.Attribute9,'
1453 ||' 10, x.Attribute10,'
1454 ||' 11, x.Attribute11,'
1455 ||' 12, x.Attribute12,'
1456 ||' 13, x.Attribute13,'
1457 ||' 14, x.Attribute14,'
1458 ||' 15, x.Attribute15,Null)) ,'
1459 ||' TO_NUMBER(DECODE( :v_msc_max_capacity,'
1460 ||' 1, x.Attribute1,'
1461 ||' 2, x.Attribute2,'
1462 ||' 3, x.Attribute3,'
1463 ||' 4, x.Attribute4,'
1464 ||' 5, x.Attribute5,'
1465 ||' 6, x.Attribute6,'
1466 ||' 7, x.Attribute7,'
1467 ||' 8, x.Attribute8,'
1468 ||' 9, x.Attribute9,'
1469 ||' 10, x.Attribute10,'
1470 ||' 11, x.Attribute11,'
1471 ||' 12, x.Attribute12,'
1472 ||' 13, x.Attribute13,'
1473 ||' 14, x.Attribute14,'
1474 ||' 15, x.Attribute15,Null)) ,'
1475 ||' DECODE( :v_msc_unit_of_measure,'
1476 ||' 1, x.Attribute1,'
1477 ||' 2, x.Attribute2,'
1478 ||' 3, x.Attribute3,'
1479 ||' 4, x.Attribute4,'
1480 ||' 5, x.Attribute5,'
1481 ||' 6, x.Attribute6,'
1482 ||' 7, x.Attribute7,'
1483 ||' 8, x.Attribute8,'
1484 ||' 9, x.Attribute9,'
1485 ||' 10, x.Attribute10,'
1486 ||' 11, x.Attribute11,'
1487 ||' 12, x.Attribute12,'
1488 ||' 13, x.Attribute13,'
1489 ||' 14, x.Attribute14,'
1490 ||' 15, x.Attribute15,Null) ,'
1491 ||' x.RESOURCE_COST,'
1492 ||' TO_NUMBER(DECODE( :v_mso_res_penalty,'
1493 ||' 1, x.Attribute1,'
1494 ||' 2, x.Attribute2,'
1495 ||' 3, x.Attribute3,'
1496 ||' 4, x.Attribute4,'
1497 ||' 5, x.Attribute5,'
1498 ||' 6, x.Attribute6,'
1499 ||' 7, x.Attribute7,'
1500 ||' 8, x.Attribute8,'
1501 ||' 9, x.Attribute9,'
1502 ||' 10, x.Attribute10,'
1503 ||' 11, x.Attribute11,'
1504 ||' 12, x.Attribute12,'
1505 ||' 13, x.Attribute13,'
1506 ||' 14, x.Attribute14,'
1507 ||' 15, x.Attribute15)),'
1508 ||' x.DEPT_OVERHEAD_COST,'
1509 ||' 2,'
1510 ||' :v_refresh_id,'
1511 || v_temp_atp_rule_sql
1512 ||' :v_instance_id'
1513 ||' FROM MRP_AP_DEPARTMENT_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1514 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1515
1516 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_aggreg_res_name,
1517 MSC_CL_PULL.v_msc_aggreg_res_name,
1518 MSC_CL_PULL.v_dgmt,
1519 MSC_CL_PULL.v_msc_batchable_flag,
1520 MSC_CL_PULL.v_msc_batching_window,
1521 MSC_CL_PULL.v_msc_min_capacity,
1522 MSC_CL_PULL.v_msc_max_capacity,
1523 MSC_CL_PULL.v_msc_unit_of_measure,
1524 MSC_CL_PULL.v_mso_res_penalty,
1525 MSC_CL_PULL.v_refresh_id,
1526 MSC_CL_PULL.v_instance_id;
1527
1528 END IF;
1529
1530 COMMIT;
1531
1532 IF ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) THEN
1533
1534 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPARTMENT_RESOURCES';
1535 MSC_CL_PULL.v_view_name := 'MRP_AP_LINE_RESOURCES_V';
1536
1537 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1538
1539 v_union_sql :=
1540 ' AND ( x.RN1 > :v_lrn )';
1541
1542 ELSE
1543
1544 v_union_sql := ' ';
1545
1546 END IF;
1547
1548
1549 v_sql_stmt:=
1550 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1551 ||' ( ORGANIZATION_ID,'
1552 ||' RESOURCE_ID,'
1553 ||' DEPARTMENT_ID,'
1554 ||' DEPARTMENT_CODE,'
1555 ||' LINE_FLAG,'
1556 ||' MAX_RATE,'
1557 ||' MIN_RATE,'
1558 ||' START_TIME,'
1559 ||' STOP_TIME,'
1560 ||' DEPARTMENT_DESCRIPTION,'
1561 ||' AGGREGATED_RESOURCE_FLAG,'
1562 ||' OVER_UTILIZED_PERCENT,'
1563 ||' UNDER_UTILIZED_PERCENT,'
1564 ||' RESOURCE_SHORTAGE_TYPE,'
1565 ||' RESOURCE_EXCESS_TYPE,'
1566 ||' PLANNING_EXCEPTION_SET,'
1567 ||' USER_TIME_FENCE,'
1568 ||' AVAILABLE_24_HOURS_FLAG,'
1569 ||' CAPACITY_UNITS,'
1570 ||' DISABLE_DATE,'
1571 ||' DELETED_FLAG,'
1572 ||' REFRESH_ID,'
1573 ||' ATP_RULE_ID,'
1577 ||' UTILIZATION_CHANGE_TYPE,'
1574 ||' SETUP_TIME_PERCENT,'
1575 ||' UTILIZATION_CHANGE_PERCENT,'
1576 ||' SETUP_TIME_TYPE,'
1578 ||' SR_INSTANCE_ID)'
1579 ||' select'
1580 ||' x.ORGANIZATION_ID,'
1581 ||' -1,'
1582 ||' x.DEPARTMENT_ID,'
1583 ||' x.DEPARTMENT_CODE,'
1584 ||' x.LINE_FLAG,'
1585 ||' x.MAX_RATE,'
1586 ||' x.MIN_RATE,'
1587 ||' x.START_TIME,'
1588 ||' x.STOP_TIME,'
1589 ||' x.DEPARTMENT_DESCRIPTION,'
1590 ||' 2,'
1591 ||' x.OVER_UTILIZED_PERCENT,'
1592 ||' x.UNDER_UTILIZED_PERCENT,'
1593 ||' x.RESOURCE_SHORTAGE_TYPE,'
1594 ||' x.RESOURCE_EXCESS_TYPE,'
1595 ||' x.PLANNING_EXCEPTION_SET,'
1596 ||' x.USER_TIME_FENCE,'
1597 ||' x.AVAILABLE_24_HOURS_FLAG,'
1598 ||' x.CAPACITY_UNITS,'
1599 ||' x.LINE_DISABLE_DATE- :v_dgmt,'
1600 ||' 2,'
1601 ||' :v_refresh_id,'
1602 || v_temp_atp_rule_sql1
1603 ||' :v_instance_id'
1604 ||' from MRP_AP_LINE_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1605 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1606 || v_union_sql;
1607
1608 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1609
1610 EXECUTE IMMEDIATE v_sql_stmt
1611 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1612
1613 ELSE
1614 EXECUTE IMMEDIATE v_sql_stmt
1615 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1616 END IF;
1617
1618
1619 COMMIT;
1620
1621 END IF; -- MSC_CL_PULL.WIP_ENABLED
1622
1623
1624 MSC_CL_PULL.v_table_name:= 'MSC_ST_SIMULATION_SETS';
1625 MSC_CL_PULL.v_view_name := 'MRP_AP_SIMULATION_SETS_V';
1626
1627 v_sql_stmt:=
1628 'insert into MSC_ST_SIMULATION_SETS'
1629 ||' ( ORGANIZATION_ID,'
1630 ||' SIMULATION_SET,'
1631 ||' DESCRIPTION,'
1632 ||' USE_IN_WIP_FLAG,'
1633 ||' DELETED_FLAG,'
1634 ||' REFRESH_ID,'
1635 ||' SR_INSTANCE_ID)'
1636 ||' select '
1637 ||' x.ORGANIZATION_ID,'
1638 ||' x.SIMULATION_SET,'
1639 ||' x.DESCRIPTION,'
1640 ||' x.USE_IN_WIP_FLAG,'
1641 ||' 2,'
1642 ||' :v_refresh_id,'
1643 ||' :v_instance_id'
1644 ||' from MRP_AP_SIMULATION_SETS_V'||MSC_CL_PULL.v_dblink||' x'
1645 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1646
1647 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1648
1649 COMMIT;
1650
1651 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1652
1653 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_GROUPS';
1654 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_GROUPS_V';
1655
1656 v_sql_stmt:=
1657 'insert into MSC_ST_RESOURCE_GROUPS'
1658 ||' ( GROUP_CODE,'
1659 ||' MEANING,'
1660 ||' DESCRIPTION,'
1661 ||' FROM_DATE,'
1662 ||' TO_DATE,'
1663 ||' ENABLED_FLAG,'
1664 ||' DELETED_FLAG,'
1665 ||' REFRESH_ID,'
1666 ||' SR_INSTANCE_ID)'
1667 ||' select '
1668 ||' x.GROUP_CODE,'
1669 ||' x.MEANING,'
1670 ||' x.DESCRIPTION,'
1671 ||' x.FROM_DATE,'
1672 ||' x.TO_DATE,'
1673 ||' DECODE( x.ENABLED_FLAG, ''Y'', 1 , 2),'
1674 ||' 2,'
1675 ||' :v_refresh_id,'
1676 ||' :v_instance_id'
1677 ||' from MRP_AP_RESOURCE_GROUPS_V'||MSC_CL_PULL.v_dblink||' x';
1678
1679 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1680
1681 COMMIT;
1682
1683 END IF;
1684
1685 IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
1686
1687 IF (nvl(fnd_profile.value('MSC_INFLATE_WIP') ,'N')= 'N') THEN
1688 lv_inflate_wip := 2 ;
1689 ELSE
1690 lv_inflate_wip := 1 ;
1691 END IF;
1692
1693 -- if the profile MSC_INFLATE_WIP is set to YES then inflating the operation resource hours by
1694 -- efficiency and utilization. Not inflating the operation resource hours for SDS records.
1695 -- for bug fix 2877975. Lot based jobs should get inflated irrespective of the profile.
1696 IF lv_inflate_wip = 1 AND MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1697 v_res_hrs_sql := ' decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) ,';
1698 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) - x.HOURS_EXPENDED >0) ';
1699 v_touch_time := ' decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) ,';
1700 ELSIF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1701 v_res_hrs_sql := ' decode(x.entity_type,5, (decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED)),x.OPERATION_HOURS_REQUIRED),';
1702 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) - x.HOURS_EXPENDED > 0) ';
1703 v_touch_time := ' decode(x.entity_type,5, (decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.efficiency),x.OPERATION_HOURS_REQUIRED)),x.OPERATION_HOURS_REQUIRED),';
1704 ELSE
1705 v_res_hrs_sql := ' x.OPERATION_HOURS_REQUIRED ,';
1706 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or x.OPERATION_HOURS_REQUIRED - x.HOURS_EXPENDED > 0 ) ';
1707 v_touch_time := ' x.OPERATION_HOURS_REQUIRED ,';
1708 END IF;
1709
1710 -- Pulling parent_seq_num and SETUP_ID from MRP_AP_RESOURCE_REQUIREMENTS_V if MSC_CL_PULL.v_apps_ver is MSC_UTIL.G_APPS115
1711 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1712 v_temp_parent_seq := 'x.PARENT_SEQ_NUM,x.SETUP_ID, x.ORIG_RESOURCE_SEQ_NUM,GROUP_SEQUENCE_ID,GROUP_SEQUENCE_NUMBER,BATCH_NUMBER,MAXIMUM_ASSIGNED_UNITS, ';
1713 ELSE
1714 v_temp_parent_seq := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1715 END IF;
1719 where instance_id = MSC_CL_PULL.v_instance_id ;
1716
1717 If MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1718 select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
1720 end if;
1721
1722 --=================== Net Change Mode: Delete ==================
1723 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1724
1725 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1726 --MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_REQUIREMENTS_V';
1727 -- bug5996354
1728 if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1729 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_IRO_ERO_RES_REQ_V';
1730 else
1731 MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_REQUIREMENTS_V';
1732 end if;
1733 --
1734
1735 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1736 v_temp_sql1 := ' x.RESOURCE_SEQ_NUM, ';
1737 ELSE
1738 v_temp_sql1 := ' NULL, ';
1739 END IF;
1740
1741 lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1742
1743 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1744 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1745
1746 if lv_lbj_details = 1 Then
1747 lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1748 end if;
1749
1750 ELSE
1751 v_temp_sql := NULL;
1752 END IF;
1753
1754 v_sql_stmt:=
1755 'insert into MSC_ST_RESOURCE_REQUIREMENTS'
1756 ||' ( WIP_ENTITY_ID,'
1757 ||' OPERATION_SEQ_NUM,'
1758 ||' ORIG_RESOURCE_SEQ_NUM,'
1759 ||' DELETED_FLAG,'
1760 ||' REFRESH_ID,'
1761 ||' SR_INSTANCE_ID)'
1762 ||' select'
1763 ||' x.WIP_ENTITY_ID,'
1764 || lv_op_seq_num
1765 || v_temp_sql1
1766 ||' 1,'
1767 ||' :v_refresh_id,'
1768 ||' :v_instance_id'
1769 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1770 ||' WHERE x.RN> :v_lrn '
1771 ||' AND DECODE( x.operation_seq_num,'
1772 ||' NULL, DECODE( x.wip_job_type,'
1773 ||' 1, DECODE( :v_mps_consume_profile_value,'
1774 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1775 ||' x.WJS_NET_QTY_FLAG), '
1776 ||' x.WJS_MPS_NET_QTY_FLAG),'
1777 ||' 1)= 1'
1778 || v_temp_sql;
1779
1780
1781 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1782 MSC_CL_PULL.v_instance_id,
1783 MSC_CL_PULL.v_lrn,
1784 MSC_CL_PULL.v_mps_consume_profile_value;
1785
1786 COMMIT;
1787
1788 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1789
1790 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1791 MSC_CL_PULL.v_view_name := 'MRP_AD_DJOB_SUB_OP_RESOURCES_V';
1792
1793 v_sql_stmt:=
1794 'insert into MSC_ST_JOB_OP_RESOURCES'
1795 ||' ( WIP_ENTITY_ID,'
1796 ||' OPERATION_SEQ_NUM,'
1797 ||' RESOURCE_SEQ_NUM,'
1798 ||' DELETED_FLAG,'
1799 ||' REFRESH_ID,'
1800 ||' SR_INSTANCE_ID)'
1801 ||' select'
1802 ||' x.WIP_ENTITY_ID,'
1803 ||' x.OPERATION_SEQ_NUM,'
1804 ||' x.RESOURCE_SEQ_NUM,'
1805 ||' 1,'
1806 ||' :v_refresh_id,'
1807 ||' :v_instance_id'
1808 ||' from MRP_AD_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1809 ||' where x.RN> '||MSC_CL_PULL.v_lrn
1810 ||' AND DECODE( x.operation_seq_num,'
1811 ||' NULL, DECODE( :v_mps_consume_profile_value,'
1812 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1813 ||' x.WJS_NET_QTY_FLAG), '
1814 ||' 1)= 1'
1815 || v_temp_sql;
1816
1817 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1818 MSC_CL_PULL.v_instance_id,
1819 MSC_CL_PULL.v_mps_consume_profile_value;
1820
1821 COMMIT;
1822
1823 END IF;
1824
1825 END IF;
1826
1827 IF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
1828 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1829 ||' 1, DECODE( :v_mps_consume_profile_value, '
1830 ||' 1, x.mps_net_quantity,'
1831 ||' x.net_quantity), '
1832 ||' x.net_quantity) >= 0' ;
1833 ELSE
1834 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1835 ||' 1, DECODE( :v_mps_consume_profile_value, '
1836 ||' 1, x.mps_net_quantity,'
1837 ||' x.net_quantity), '
1838 ||' x.net_quantity) > 0' ;
1839 END IF;
1840
1841 /* set the MTQ , Firm Flag, Scheduled flag */
1842 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1843 IF lv_lbj_details =1 Then
1844 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,x.scheduled_flag, x.QUANTITY_IN_QUEUE, x.QUANTITY_RUNNING, x.QUANTITY_WAITING_TO_MOVE, x.QUANTITY_COMPLETED,'
1845 ||' decode(x.ENTITY_TYPE,5,x.COPY_YIELD, x.YIELD), x.USAGE_RATE, x.Copy_op_seq_num, decode(x.ENTITY_TYPE,5,copy_STANDARD_OPERATION_CODE,Standard_Operation_Code), x.ACTIVITY_GROUP_ID, x.ALTERNATE_NUM, x.PRINCIPLE_FLAG, '
1846 ||' x.ACTUAL_START_DATE, x.ROUTING_SEQUENCE_ID, ';
1847 ELSE
1848 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,x.scheduled_flag, x.QUANTITY_IN_QUEUE, x.QUANTITY_RUNNING, x.QUANTITY_WAITING_TO_MOVE, x.QUANTITY_COMPLETED,'
1849 ||' x.YIELD, x.USAGE_RATE, x.Operation_Seq_Num, x.Standard_Operation_Code, x.ACTIVITY_GROUP_ID, x.ALTERNATE_NUM, x.PRINCIPLE_FLAG, x.ACTUAL_START_DATE, x.ROUTING_SEQUENCE_ID, ';
1850 lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
1851 END IF;
1852 ELSIF MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 THEN
1856 END IF;
1853 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,NULL,NULL,NULL,NULL,NULL,NULL,NULL, x.OPERATION_SEQ_NUM, x.Standard_Operation_Code, NULL, NULL, NULL, NULL, NULL, ';
1854 ELSE
1855 v_temp_sql := ' NULL ,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL, x.OPERATION_SEQ_NUM, x.Standard_Operation_Code, NULL, NULL, NULL, NULL, NULL, ';
1857 -- bug5996354
1858 if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1859 MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1860 else
1861 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1862 end if;
1863 --
1864
1865
1866 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1867 --MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1868 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1869
1870 -- BUG 3036681
1871 -- No Need to check for RN2 (on wip_discrete_jobs) as
1872 -- the materialized view on wip_operations now has the columns from
1873 -- wip_discrete_jobs too.
1874 lv_hint := ' /*+ first_rows leading(x.msi) index(x.msi MTL_SYS_ITEMS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.msi. x.wo x.wor) */ ';
1875
1876 v_union_sql :=
1877 ' AND ( x.RN1> :v_lrn )'
1878 ||' UNION '
1879 ||' select /*+ first_rows leading(x.wo) index(x.wo WIP_WOPRS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.wo x.wor x.wor1) */ '
1880 ||' x.DEPARTMENT_ID,'
1881 ||' x.RESOURCE_ID,'
1882 ||' x.ORGANIZATION_ID,'
1883 ||' x.INVENTORY_ITEM_ID,'
1884 ||' x.WIP_ENTITY_ID,'
1885 ||' x.WIP_ENTITY_ID,'
1886 ||' x.WIP_JOB_TYPE,'
1887 ||' x.OPERATION_SEQUENCE_ID,'
1888 ||' x.RESOURCE_SEQ_NUM,'
1889 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
1890 || v_res_hrs_sql
1891 ||' x.HOURS_EXPENDED,'
1892 ||' x.DEMAND_CLASS,'
1893 ||' x.BASIS_TYPE,'
1894 ||' x.RESOURCE_UNITS,'
1895 ||' x.COMPLETION_DATE- :v_dgmt,'
1896 ||' x.WIP_JOB_TYPE,'
1897 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
1898 ||' x.SCHEDULED_QUANTITY,'
1899 ||' 2,'
1900 || v_temp_sql
1901 ||' :v_refresh_id,'
1902 || v_temp_parent_seq
1903 ||' x.OPERATION_HOURS_REQUIRED,'
1904 || v_touch_time
1905 ||' :v_instance_id'
1906 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1907 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1908 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1909 ||' 1, DECODE( :v_mps_consume_profile_value, '
1910 ||' 1, x.mps_net_quantity,'
1911 ||' x.net_quantity), '
1912 ||' x.net_quantity) > 0'*/
1913 || lv_qty_sql_temp
1914 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
1915 || v_res_hrs_left
1916 || lv_cond_sql
1917 ||' AND ( x.RN2> :v_lrn )'
1918 ||' UNION '
1919 ||' select /*+ first_rows leading(x.wo) index(x.wo WIP_WOPRS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.wo x.wor x.wor1) */ '
1920 ||' x.DEPARTMENT_ID,'
1921 ||' x.RESOURCE_ID,'
1922 ||' x.ORGANIZATION_ID,'
1923 ||' x.INVENTORY_ITEM_ID,'
1924 ||' x.WIP_ENTITY_ID,'
1925 ||' x.WIP_ENTITY_ID,'
1926 ||' x.WIP_JOB_TYPE,'
1927 ||' x.OPERATION_SEQUENCE_ID,'
1928 ||' x.RESOURCE_SEQ_NUM,'
1929 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
1930 || v_res_hrs_sql
1931 ||' x.HOURS_EXPENDED,'
1932 ||' x.DEMAND_CLASS,'
1933 ||' x.BASIS_TYPE,'
1934 ||' x.RESOURCE_UNITS,'
1935 ||' x.COMPLETION_DATE- :v_dgmt,'
1936 ||' x.WIP_JOB_TYPE,'
1937 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
1938 ||' x.SCHEDULED_QUANTITY,'
1939 ||' 2,'
1940 || v_temp_sql
1941 ||' :v_refresh_id,'
1942 || v_temp_parent_seq
1943 ||' x.OPERATION_HOURS_REQUIRED,'
1944 || v_touch_time
1945 ||' :v_instance_id'
1946 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1947 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1948 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1949 ||' 1, DECODE( :v_mps_consume_profile_value, '
1950 ||' 1, x.mps_net_quantity,'
1951 ||' x.net_quantity), '
1952 ||' x.net_quantity) > 0' */
1953 || lv_qty_sql_temp
1954 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
1955 || v_res_hrs_left
1956 || lv_cond_sql
1957 ||' AND ( x.RN3> :v_lrn )';
1958
1959 ELSE
1960
1961 lv_hint := ' /*+ leading(x.wo) use_hash(x.wo x.wor x.wor1) */ ';
1962
1963 v_union_sql := ' ';
1964
1965 END IF;
1966
1967 v_sql_stmt:=
1968 'insert into MSC_ST_RESOURCE_REQUIREMENTS'
1969 ||' ( DEPARTMENT_ID,'
1970 ||' RESOURCE_ID,'
1971 ||' ORGANIZATION_ID,'
1972 ||' INVENTORY_ITEM_ID,'
1973 ||' SUPPLY_ID,'
1974 ||' WIP_ENTITY_ID,'
1975 ||' SUPPLY_TYPE,'
1976 ||' OPERATION_SEQUENCE_ID,'
1977 ||' RESOURCE_SEQ_NUM,'
1978 ||' START_DATE,'
1979 ||' OPERATION_HOURS_REQUIRED,'
1980 ||' HOURS_EXPENDED,'
1981 ||' DEMAND_CLASS,'
1982 ||' BASIS_TYPE,'
1983 ||' ASSIGNED_UNITS,'
1984 ||' END_DATE,'
1985 ||' WIP_JOB_TYPE,'
1986 ||' SCHEDULED_COMPLETION_DATE,'
1987 ||' SCHEDULED_QUANTITY,'
1988 ||' DELETED_FLAG,'
1989 ||' MINIMUM_TRANSFER_QUANTITY,'
1990 ||' FIRM_FLAG,'
1991 ||' SCHEDULE_FLAG,'
1992 ||' QUANTITY_IN_QUEUE,'
1993 ||' QUANTITY_RUNNING,'
1994 ||' QUANTITY_WAITING_TO_MOVE,'
1995 ||' QUANTITY_COMPLETED,'
1996 ||' YIELD,'
1997 ||' USAGE_RATE,'
1998 ||' OPERATION_SEQ_NUM,'
1999 ||' STD_OP_CODE,'
2000 ||' ACTIVITY_GROUP_ID,'
2001 ||' ALTERNATE_NUMBER,'
2002 ||' PRINCIPAL_FLAG,'
2003 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
2004 ||' ROUTING_SEQUENCE_ID,'
2008 ||' ORIG_RESOURCE_SEQ_NUM,'
2005 ||' REFRESH_ID,'
2006 ||' PARENT_SEQ_NUM,'
2007 ||' SETUP_ID,'
2009 ||' GROUP_SEQUENCE_ID,'
2010 ||' GROUP_SEQUENCE_NUMBER,'
2011 ||' BATCH_NUMBER,'
2012 ||' MAXIMUM_ASSIGNED_UNITS,'
2013 ||' UNADJUSTED_RESOURCE_HOURS,'
2014 ||' TOUCH_TIME,'
2015 ||' SR_INSTANCE_ID)'
2016 ||' select '|| lv_hint
2017 ||' x.DEPARTMENT_ID,'
2018 ||' x.RESOURCE_ID,'
2019 ||' x.ORGANIZATION_ID,'
2020 ||' x.INVENTORY_ITEM_ID,'
2021 ||' x.WIP_ENTITY_ID,'
2022 ||' x.WIP_ENTITY_ID,'
2023 ||' x.WIP_JOB_TYPE,'
2024 ||' x.OPERATION_SEQUENCE_ID,'
2025 ||' x.RESOURCE_SEQ_NUM,'
2026 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2027 || v_res_hrs_sql
2028 ||' x.HOURS_EXPENDED,'
2029 ||' x.DEMAND_CLASS,'
2030 ||' x.BASIS_TYPE,'
2031 ||' x.RESOURCE_UNITS,'
2032 ||' x.COMPLETION_DATE- :v_dgmt,'
2033 ||' x.WIP_JOB_TYPE,'
2034 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
2035 ||' x.SCHEDULED_QUANTITY,'
2036 ||' 2,'
2037 || v_temp_sql
2038 ||' :v_refresh_id,'
2039 || v_temp_parent_seq
2040 ||' x.OPERATION_HOURS_REQUIRED,'
2041 || v_touch_time
2042 ||' :v_instance_id'
2043 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
2044 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2045 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
2046 ||' 1, DECODE( :v_mps_consume_profile_value, '
2047 ||' 1, x.mps_net_quantity,'
2048 ||' x.net_quantity), '
2049 ||' x.net_quantity) > 0' */
2050 || lv_qty_sql_temp
2051 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
2052 || v_res_hrs_left
2053 || lv_cond_sql
2054 || v_union_sql ;
2055
2056
2057 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2058
2059 EXECUTE IMMEDIATE v_sql_stmt USING
2060 MSC_CL_PULL.v_dgmt,
2061 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2062 MSC_CL_PULL.v_mps_consume_profile_value,
2063 MSC_CL_PULL.v_hour_uom,
2064 MSC_CL_PULL.v_hour_uom,
2065 MSC_CL_PULL.v_lrn,
2066 MSC_CL_PULL.v_dgmt,
2067 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2068 MSC_CL_PULL.v_mps_consume_profile_value,
2069 MSC_CL_PULL.v_hour_uom,
2070 MSC_CL_PULL.v_hour_uom,
2071 MSC_CL_PULL.v_lrn,
2072 MSC_CL_PULL.v_dgmt,
2073 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2074 MSC_CL_PULL.v_mps_consume_profile_value,
2075 MSC_CL_PULL.v_hour_uom,
2076 MSC_CL_PULL.v_hour_uom,
2077 MSC_CL_PULL.v_lrn;
2078
2079 ELSE
2080
2081 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2082 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2083 MSC_CL_PULL.v_mps_consume_profile_value,
2084 MSC_CL_PULL.v_hour_uom,
2085 MSC_CL_PULL.v_hour_uom;
2086 END IF;
2087 COMMIT;
2088
2089 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2090
2091 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2092 MSC_CL_PULL.v_view_name := 'MRP_AP_DJOB_SUB_OP_RESOURCES_V';
2093
2094
2095 v_sql_stmt:=
2096 'insert into MSC_ST_JOB_OP_RESOURCES'
2097 ||' (WIP_ENTITY_ID,'
2098 ||' SR_INSTANCE_ID,'
2099 ||' ORGANIZATION_ID,'
2100 ||' OPERATION_SEQ_NUM,'
2101 ||' RESOURCE_SEQ_NUM,'
2102 ||' ALTERNATE_NUM,'
2103 ||' RECOMMENDED,'
2104 ||' RECO_START_DATE,'
2105 ||' RECO_COMPLETION_DATE,'
2106 ||' RESOURCE_ID,'
2107 ||' ASSIGNED_UNITS,'
2108 ||' USAGE_RATE_OR_AMOUNT,'
2109 ||' UOM_CODE,'
2110 ||' BASIS_TYPE,'
2111 ||' RESOURCE_OFFSET_PERCENT,'
2112 ||' SCHEDULE_SEQ_NUM,'
2113 ||' PRINCIPAL_FLAG,'
2114 ||' DEPARTMENT_ID,'
2115 ||' ACTIVITY_GROUP_ID,'
2116 ||' SCHEDULE_FLAG,'
2117 ||' SETUP_ID,'
2118 ||' DELETED_FLAG,'
2119 ||' REFRESH_ID )'
2120 ||' select '
2121 ||' x.WIP_ENTITY_ID,'
2122 ||' :v_instance_id,'
2123 ||' x.ORGANIZATION_ID,'
2124 ||' x.OPERATION_SEQ_NUM,'
2125 ||' x.RESOURCE_SEQ_NUM,'
2126 ||' x.ALTERNATE_NUM,'
2127 ||' ''Y'' ,'
2128 ||' NULL,'
2129 ||' NULL,'
2130 ||' x.RESOURCE_ID,'
2131 ||' x.ASSIGNED_UNITS,'
2132 ||' x.USAGE_RATE_OR_AMOUNT,'
2133 ||' x.UOM_CODE,'
2134 ||' x.BASIS_TYPE,'
2135 ||' x.RESOURCE_OFFSET_PERCENT,'
2136 ||' x.SCHEDULE_SEQ_NUM,'
2137 ||' x.PRINCIPLE_FLAG,'
2138 ||' x.DEPARTMENT_ID,'
2139 ||' x.ACTIVITY_GROUP_ID,'
2140 ||' x.SCHEDULE_FLAG,'
2141 ||' x.SETUP_ID,'
2142 ||' 2,'
2143 ||' :v_refresh_id'
2144 ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2145 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2146 ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2147 ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2148 ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2149
2150 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2151
2152 COMMIT;
2153
2154 END IF; /* MSC_UTIL.G_APPS115 */
2155
2156
2160
2157 END IF; -- MSC_CL_PULL.WIP_ENABLED
2158
2159 END LOAD_RESOURCE;
2161 /* ds change start */
2162 PROCEDURE LOAD_RESOURCE_INSTANCE IS
2163
2164 lv_inflate_wip NUMBER;
2165 v_res_hrs_sql VARCHAR2(300);
2166 v_temp_parent_seq VARCHAR2(100);
2167 lv_lbj_details NUMBER :=0;
2168 lv_cond_sql VARCHAR2(100) := null;
2169 lv_op_seq_num VARCHAR2(100) := null;
2170
2171 BEGIN
2172
2173 IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2174 MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2175 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2176 MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2177
2178 v_sql_stmt:=
2179 ' insert into MSC_ST_DEPT_RES_INSTANCES'
2180 ||' ( RESOURCE_ID,'
2181 ||' DEPARTMENT_ID,'
2182 ||' RES_INSTANCE_ID,'
2183 ||' SERIAL_NUMBER,'
2184 ||' EQUIPMENT_ITEM_ID,'
2185 ||' DEPARTMENT_CODE,'
2186 ||' ORGANIZATION_ID,'
2187 ||' RESOURCE_CODE,'
2188 ||' LAST_KNOWN_SETUP,'
2189 ||' DELETED_FLAG,'
2190 ||' REFRESH_ID,'
2191 ||' SR_INSTANCE_ID)'
2192 ||' select'
2193 ||' x.RESOURCE_ID,'
2194 ||' x.DEPARTMENT_ID,'
2195 ||' x.RES_INSTANCE_ID,'
2196 ||' x.SERIAL_NUMBER,'
2197 ||' x.EQUIPMENT_ITEM_ID,'
2198 ||' x.DEPARTMENT_CODE,'
2199 ||' x.ORGANIZATION_ID,'
2200 ||' x.RESOURCE_CODE,'
2201 ||' x.LAST_KNOWN_SETUP,'
2202 ||' 2,'
2203 ||' :v_refresh_id,'
2204 ||' :v_instance_id'
2205 ||' from MRP_AP_DEPT_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2206 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2207
2208 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: dept_res_instance sql = '||v_sql_stmt);
2209 EXECUTE IMMEDIATE v_sql_stmt USING
2210 MSC_CL_PULL.v_refresh_id,
2211 MSC_CL_PULL.v_instance_id;
2212
2213
2214 COMMIT;
2215
2216 END IF;
2217
2218
2219 IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2220 (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2221
2222 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2223 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2224 MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2225 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2226
2227 v_sql_stmt:=
2228 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2229 ||' ( WIP_ENTITY_ID,'
2230 ||' OPERATION_SEQ_NUM,'
2231 ||' RESOURCE_SEQ_NUM,'
2232 ||' RES_INSTANCE_ID,'
2233 ||' SERIAL_NUMBER,'
2234 ||' DELETED_FLAG,'
2235 ||' REFRESH_ID,'
2236 ||' SR_INSTANCE_ID)'
2237 ||' select'
2238 ||' x.WIP_ENTITY_ID,'
2239 ||' x.OPERATION_SEQ_NUM,'
2240 ||' x.RESOURCE_SEQ_NUM,'
2241 ||' x.RES_INSTANCE_ID,'
2242 ||' x.SERIAL_NUMBER,'
2243 ||' 1,'
2244 ||' :v_refresh_id,'
2245 ||' :v_instance_id'
2246 ||' from MRP_AD_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2247 ||' WHERE x.RN > :v_lrn '
2248 || v_temp_sql;
2249
2250
2251 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance sql = '||v_sql_stmt);
2252 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2253 MSC_CL_PULL.v_instance_id,
2254 MSC_CL_PULL.v_lrn;
2255
2256
2257 COMMIT;
2258
2259 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2260 MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_OPR_RES_INSTS_V';
2261
2262
2263 v_sql_stmt:=
2264 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2265 ||' ( WIP_ENTITY_ID,'
2266 ||' OPERATION_SEQ_NUM,'
2267 ||' RESOURCE_SEQ_NUM,'
2268 ||' RES_INSTANCE_ID,'
2269 ||' SERIAL_NUMBER,'
2270 ||' DELETED_FLAG,'
2271 ||' REFRESH_ID,'
2272 ||' SR_INSTANCE_ID)'
2273 ||' select'
2274 ||' x.WIP_ENTITY_ID,'
2275 ||' x.OPERATION_SEQ_NUM,'
2276 ||' x.RESOURCE_SEQ_NUM,'
2277 ||' x.RES_INSTANCE_ID,'
2278 ||' x.SERIAL_NUMBER,'
2279 ||' 1,'
2280 ||' :v_refresh_id,'
2281 ||' :v_instance_id'
2282 ||' from MRP_AD_LJ_OPR_RES_INSTS_V'||MSC_CL_PULL.v_dblink||' x'
2283 ||' WHERE x.RN > :v_lrn '
2284 || v_temp_sql;
2285
2286 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2287 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2288 MSC_CL_PULL.v_instance_id,
2289 MSC_CL_PULL.v_lrn;
2290 COMMIT;
2291
2292 END IF; /* incremental */
2293
2294
2295 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2296 MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INSTANCE_REQS_V';
2297
2298
2299 IF lv_lbj_details <> 1 THEN
2300 lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
2301 END IF;
2302
2303 IF MSC_CL_PULL.v_lrnn<> -1 THEN /* incremental */
2304
2305 v_union_sql :=
2306 ' AND ( x.RN1> :v_lrn )'
2307 ||' UNION '
2308 ||' select '
2309 ||' x.DEPARTMENT_ID,'
2310 ||' x.RESOURCE_ID,'
2311 ||' x.RES_INSTANCE_ID,'
2312 ||' x.ORGANIZATION_ID,'
2313 --||' x.OPERATION_SEQUENCE_ID,'
2314 ||' x.OPERATION_SEQ_NUM,'
2318 ||' x.EQUIPMENT_ITEM_ID,'
2315 ||' x.RESOURCE_SEQ_NUM,'
2316 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2317 ||' x.SERIAL_NUMBER,'
2319 ||' x.WIP_ENTITY_ID,'
2320 ||' x.WIP_ENTITY_ID,'
2321 ||' x.START_DATE - :v_dgmt,'
2322 ||' x.COMPLETION_DATE - :v_dgmt,'
2323 ||' 2,'
2324 ||' :v_refresh_id,'
2325 ||' :v_instance_id'
2326 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2327 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2328 || lv_cond_sql
2329 ||' AND ( x.RN2> :v_lrn )'
2330 ||' UNION '
2331 ||' select '
2332 ||' x.DEPARTMENT_ID,'
2333 ||' x.RESOURCE_ID,'
2334 ||' x.RES_INSTANCE_ID,'
2335 ||' x.ORGANIZATION_ID,'
2336 --||' x.OPERATION_SEQUENCE_ID,'
2337 ||' x.OPERATION_SEQ_NUM,'
2338 ||' x.RESOURCE_SEQ_NUM,'
2339 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2340 ||' x.SERIAL_NUMBER,'
2341 ||' x.EQUIPMENT_ITEM_ID,'
2342 ||' x.WIP_ENTITY_ID,'
2343 ||' x.WIP_ENTITY_ID,'
2344 ||' x.START_DATE - :v_dgmt,'
2345 ||' x.COMPLETION_DATE- :v_dgmt,'
2346 ||' 2,'
2347 ||' :v_refresh_id,'
2348 ||' :v_instance_id'
2349 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2350 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2351 || lv_cond_sql
2352 ||' AND ( x.RN3> :v_lrn )'
2353 ||' UNION '
2354 ||' select '
2355 ||' x.DEPARTMENT_ID,'
2356 ||' x.RESOURCE_ID,'
2357 ||' x.RES_INSTANCE_ID,'
2358 ||' x.ORGANIZATION_ID,'
2359 --||' x.OPERATION_SEQUENCE_ID,'
2360 ||' x.OPERATION_SEQ_NUM,'
2361 ||' x.RESOURCE_SEQ_NUM,'
2362 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2363 ||' x.SERIAL_NUMBER,'
2364 ||' x.EQUIPMENT_ITEM_ID,'
2365 ||' x.WIP_ENTITY_ID,'
2366 ||' x.WIP_ENTITY_ID,'
2367 ||' x.START_DATE - :v_dgmt,'
2368 ||' x.COMPLETION_DATE - :v_dgmt,'
2369 ||' 2,'
2370 ||' :v_refresh_id,'
2371 ||' :v_instance_id'
2372 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2373 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2374 || lv_cond_sql
2375 ||' AND ( x.RN4> :v_lrn )';
2376 ELSE -- full
2377
2378 v_union_sql := ' ';
2379
2380 END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
2381
2382
2383 v_sql_stmt:=
2384 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2385 ||' ( DEPARTMENT_ID,'
2386 ||' RESOURCE_ID,'
2387 ||' RES_INSTANCE_ID,'
2388 ||' ORGANIZATION_ID,'
2389 --||' OPERATION_SEQUENCE_ID,'
2390 ||' OPERATION_SEQ_NUM,'
2391 ||' RESOURCE_SEQ_NUM,'
2392 ||' ORIG_RESOURCE_SEQ_NUM,'
2393 ||' SERIAL_NUMBER,'
2394 ||' EQUIPMENT_ITEM_ID,'
2395 ||' SUPPLY_ID,'
2396 ||' WIP_ENTITY_ID,'
2397 ||' START_DATE,'
2398 ||' END_DATE,'
2399 ||' DELETED_FLAG,'
2400 ||' REFRESH_ID,'
2401 ||' SR_INSTANCE_ID)'
2402 ||' select ' /* || lv_hint */
2403 ||' x.DEPARTMENT_ID,'
2404 ||' x.RESOURCE_ID,'
2405 ||' x.RES_INSTANCE_ID,'
2406 ||' x.ORGANIZATION_ID,'
2407 --||' x.OPERATION_SEQUENCE_ID,'
2408 ||' x.OPERATION_SEQ_NUM,'
2409 ||' x.RESOURCE_SEQ_NUM,'
2410 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2411 ||' x.SERIAL_NUMBER,'
2412 ||' x.EQUIPMENT_ITEM_ID,'
2413 ||' x.WIP_ENTITY_ID,'
2414 ||' x.WIP_ENTITY_ID,'
2415 ||' x.START_DATE - :v_dgmt,'
2416 ||' x.COMPLETION_DATE - :v_dgmt,'
2417 ||' 2,'
2418 ||' :v_refresh_id,'
2419 ||' :v_instance_id'
2420 ||' from MRP_AP_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2421 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2422 || lv_cond_sql
2423 || v_union_sql ;
2424
2425
2426 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' Ds debug: insert from MRP_AP_RES_INSTANCE_REQS_V sql = '||v_sql_stmt);
2427 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2428
2429 EXECUTE IMMEDIATE v_sql_stmt USING
2430 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2431 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2432 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
2433 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
2434
2435 ELSE
2436
2437 EXECUTE IMMEDIATE v_sql_stmt USING
2438 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2439 END IF;
2440
2441 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for Wip Job Res Instances='|| SQL%ROWCOUNT);
2442 COMMIT;
2443
2444 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2445 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_RES_INSTANCES_V';
2446
2447
2448 v_sql_stmt:=
2449 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2450 ||' (WIP_ENTITY_ID,'
2451 ||' SR_INSTANCE_ID,'
2452 ||' ORGANIZATION_ID,'
2453 ||' OPERATION_SEQ_NUM,'
2454 ||' RESOURCE_SEQ_NUM,'
2455 ||' RESOURCE_ID,'
2456 ||' RES_INSTANCE_ID,'
2457 ||' DEPARTMENT_ID,'
2461 ||' COMPLETION_DATE,'
2458 ||' SERIAL_NUMBER,'
2459 ||' EQUIPMENT_ITEM_ID,'
2460 ||' START_DATE,'
2462 ||' BATCH_NUMBER,'
2463 ||' DELETED_FLAG,'
2464 ||' REFRESH_ID )'
2465 ||' select '
2466 ||' x.WIP_ENTITY_ID,'
2467 ||' :v_instance_id,'
2468 ||' x.ORGANIZATION_ID,'
2469 ||' x.OPERATION_SEQ_NUM,'
2470 ||' x.RESOURCE_SEQ_NUM,'
2471 ||' x.RESOURCE_ID,'
2472 ||' x.RES_INSTANCE_ID,'
2473 ||' x.DEPARTMENT_ID,'
2474 ||' x.SERIAL_NUMBER,'
2475 ||' x.EQUIPMENT_ITEM_ID,'
2476 ||' x.START_DATE - :v_dgmt,'
2477 ||' x.COMPLETION_DATE - :v_dgmt,'
2478 ||' x.BATCH_NUMBER,'
2479 ||' 2,'
2480 ||' :v_refresh_id'
2481 ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2482 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2483 ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2484 ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2485 ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2486
2487 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: insert from MRP_AP_JOB_RES_INSTANCES_V job res_instance sql = '||v_sql_stmt);
2488 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2489 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for lJob Res Instances= '||SQL%ROWCOUNT);
2490
2491 COMMIT;
2492
2493
2494 END IF; /* WI__ENABLED and MSC_UTIL.G_APPS115 */
2495
2496 END LOAD_RESOURCE_INSTANCE;
2497
2498
2499 PROCEDURE LOAD_CO_PRODUCT_BOMS IS /*osfm change*/
2500
2501 l_co_prd_grp_id Number;
2502 l_bill_sequence_id Number;
2503 l_usage_rate Number;
2504 l_rowid rowid;
2505 l_total_count NUMBER;
2506 l_common_bill_seq_id Number;
2507 l_split Number;
2508 l_primary_flag Number;
2509 v_query_str varchar2(3000);
2510 v_query_str1 varchar2(6000);
2511 v_query_str2 varchar2(6000);
2512 v_query_str4 varchar2(6000);
2513 type cur_type is ref cursor;
2514 cur cur_type;
2515 cur1 cur_type;
2516 cur3 cur_type;
2517 v_bill_sequence_id number;
2518 v_co_product_id number;
2519 v_split number;
2520 v_component_type number;
2521 v_org_id number;
2522 v_comp_seq_id number;
2523 v_assembly_id number;
2524 v_primary_flag number;
2525
2526 v_wsm_split_table_qry varchar2(6000);
2527 v_split_table_exist PLS_INTEGER := 0;
2528
2529 v_wsm_schema VARCHAR2(32);
2530 lv_retval boolean;
2531 lv_dummy1 varchar2(32);
2532 lv_dummy2 varchar2(32);
2533 Begin
2534
2535 lv_retval := FND_INSTALLATION.GET_APP_INFO ('WSM', lv_dummy1, lv_dummy2,v_wsm_schema);
2536 -- adding this piece of code to ensure that WSM_COPRODUCT_SPLIT_PERC is used only when it exists
2537 v_wsm_split_table_qry := 'select count(*) from all_tables'||MSC_CL_PULL.v_dblink||
2538 ' where owner=:v_wsm_schema and table_name = ''WSM_COPRODUCT_SPLIT_PERC''';
2539
2540 EXECUTE IMMEDIATE v_wsm_split_table_qry into v_split_table_exist using v_wsm_schema;
2541
2542 IF nvl(v_split_table_exist,0) = 0 THEN
2543
2544 -- old behaviour
2545 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WSM_COPRODUCT_SPLIT_PERC does not exist. Using split from WSM_CO_PRODUCTS');
2546
2547 v_query_str := 'Select bill_sequence_id*2,co_product_id,split,decode(nvl(primary_flag,''N''),''N'',2,1) primary_flag from wsm_co_products'||MSC_CL_PULL.v_dblink||
2548 ' where bill_sequence_id <>:v_bill_seq_id and bill_sequence_id is not null
2549 and co_product_group_id = :v_co_prd_grp_id and split > 0';
2550 v_query_str1 := 'select wsc.co_product_group_id,
2551 wsc.bill_Sequence_id*2,
2552 co_product_id,
2553 bom.common_bill_Sequence_id,
2554 wsc.usage_rate,
2555 wsc.split,
2556 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2557 bom.rowid
2558 from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2559 msc_st_boms bom
2560 where wsc.bill_sequence_id is not null
2561 and wsc.split > 0 -- Added this for bug:2208074
2562 and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id';
2563
2564 ELSE
2565 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WSM_COPRODUCT_SPLIT_PERC Exists');
2566
2567 v_query_str := 'Select wsc.bill_sequence_id*2,
2568 wsc.co_product_id,
2569 perc.split,
2570 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag
2571 from wsm_co_products'||MSC_CL_PULL.v_dblink|| ' wsc,
2572 wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc
2573 where wsc.bill_sequence_id <>:v_bill_seq_id
2574 and wsc.bill_sequence_id is not null
2575 and wsc.co_product_group_id = :v_co_prd_grp_id
2576 and perc.co_product_group_id = wsc.co_product_group_id
2577 and perc.co_product_id = wsc.co_product_id
2578 and sysdate < nvl(perc.disable_date,sysdate + 1)
2579 and perc.split > 0';
2580
2581 v_query_str1 := 'select wsc.co_product_group_id,
2582 wsc.bill_Sequence_id*2,
2583 wsc.co_product_id,
2584 bom.common_bill_Sequence_id,
2585 wsc.usage_rate,
2586 perc.split,
2587 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2588 bom.rowid
2589 from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2590 wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc,
2591 msc_st_boms bom
2592 where wsc.bill_sequence_id is not null
2596 and sysdate < nvl(perc.disable_date,sysdate + 1)
2593 and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
2594 and perc.co_product_group_id = wsc.co_product_group_id
2595 and perc.co_product_id = wsc.co_product_id
2597 and perc.split>0';
2598
2599 END IF;
2600
2601 OPEN cur FOR v_query_str1;
2602
2603 LOOP
2604
2605 FETCH cur INTO
2606 l_co_prd_grp_id,l_bill_sequence_id,v_assembly_id,l_common_bill_seq_id,l_usage_rate,l_split,
2607 l_primary_flag,l_rowid
2608 ;
2609 EXIT WHEN cur%NOTFOUND;
2610
2611
2612 OPEN cur1 FOR v_query_str USING l_bill_sequence_id/2,l_co_prd_grp_id;
2613
2614
2615 LOOP
2616
2617
2618 FETCH cur1 INTO v_bill_sequence_id,v_co_product_id,v_split,v_primary_flag ;
2619 EXIT WHEN cur1%NOTFOUND;
2620
2621
2622 /* set the primary_flag for the actual component to "not a primary" */
2623
2624 v_query_str4 :=
2625
2626 'update MSC_ST_BOM_COMPONENTS x
2627 set x.primary_flag = 2
2628 WHERE x.bill_Sequence_id='||l_BILL_SEQuence_ID ||
2629 ' and x.primary_flag is null
2630 and nvl(x.component_type,0) != 10
2631 and x.usage_quantity > 0
2632 and x.inventory_item_id in
2633 (select wsm.component_id from wsm_co_products' ||MSC_CL_PULL.v_dblink||' wsm
2634 where wsm.bill_sequence_id ='|| l_bill_sequence_id/2||')';
2635
2636 EXECUTE IMMEDIATE v_query_str4;
2637
2638
2639 insert into MSC_ST_BOM_COMPONENTS
2640 ( COMPONENT_SEQUENCE_ID,
2641 INVENTORY_ITEM_ID,
2642 BILL_SEQUENCE_ID,
2643 OPERATION_SEQ_NUM,
2644 COMPONENT_TYPE,
2645 USAGE_QUANTITY,
2646 COMPONENT_YIELD_FACTOR,
2647 EFFECTIVITY_DATE,
2648 DISABLE_DATE,
2649 OPERATION_OFFSET_PERCENT,
2650 OPTIONAL_COMPONENT,
2651 WIP_SUPPLY_TYPE,
2652 PLANNING_FACTOR,
2653 REVISED_ITEM_SEQUENCE_ID,
2654 ATP_FLAG,
2655 STATUS_TYPE,
2656 USE_UP_CODE,
2657 CHANGE_NOTICE,
2658 ORGANIZATION_ID,
2659 USING_ASSEMBLY_ID,
2660 FROM_UNIT_NUMBER,
2661 TO_UNIT_NUMBER,
2662 DRIVING_ITEM_ID,
2663 DELETED_FLAG,
2664 REFRESH_ID,
2665 SR_INSTANCE_ID,
2666 PRIMARY_FLAG,
2667 ROUNDING_DIRECTION)
2668 select
2669 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
2670 v_co_product_id,
2671 l_BILL_SEQuence_ID,
2672 x.OPERATION_SEQ_NUM,
2673 10,
2674 -(v_split/100),
2675 x.COMPONENT_YIELD_FACTOR,
2676 x.EFFECTIVITY_DATE,
2677 x.DISABLE_DATE,
2678 x.OPERATION_OFFSET_PERCENT,
2679 x.OPTIONAL_COMPONENT,
2680 x.WIP_SUPPLY_TYPE,
2681 x.PLANNING_FACTOR,
2682 x.REVISED_ITEM_SEQUENCE_ID,
2683 x.ATP_FLAG,
2684 x.STATUS_TYPE,
2685 x.USE_UP_CODE,
2686 x.CHANGE_NOTICE,
2687 x.ORGANIZATION_ID,
2688 v_assembly_id,
2689 x.FROM_UNIT_NUMBER,
2690 x.TO_UNIT_NUMBER,
2691 x.DRIVING_ITEM_ID,
2692 2,
2693 refresh_id,
2694 sr_instance_id,
2695 v_primary_flag,
2696 3
2697 from MSC_ST_BOM_COMPONENTS x
2698 WHERE x.bill_Sequence_id = l_BILL_SEQuence_ID
2699 and x.sr_instance_id = MSC_CL_PULL.v_instance_id
2700 and rownum = 1;
2701
2702 End loop;
2703
2704 update msc_st_boms
2705 set assembly_quantity = (l_split/100)
2706 where rowid = l_rowid;
2707
2708 ENd loop;
2709
2710 COMMIT;
2711
2712 END LOAD_CO_PRODUCT_BOMS; /*osfm change */
2713
2714
2715 --==================================================================
2716 /* ds change start change */
2717
2718 PROCEDURE LOAD_RESOURCE_SETUP IS
2719 BEGIN
2720
2721 IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2722 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2723
2724 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2725 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2726
2727 v_sql_stmt:=
2728 ' insert into MSC_ST_RESOURCE_SETUPS'
2729 ||' ( RESOURCE_ID,'
2730 ||' ORGANIZATION_ID,'
2731 --||' DEPARTMENT_ID,'
2732 ||' SETUP_ID,'
2733 ||' SETUP_CODE,'
2734 ||' SETUP_DESCRIPTION,'
2735 ||' DELETED_FLAG,'
2736 ||' REFRESH_ID,'
2737 ||' SR_INSTANCE_ID)'
2738 ||' select'
2739 ||' x.RESOURCE_ID,'
2740 ||' x.ORGANIZATION_ID,'
2741 --||' x.DEPARTMENT_ID,'
2742 ||' x.SETUP_ID,'
2743 ||' x.SETUP_CODE,'
2744 ||' x.SETUP_DESCRIPTION,'
2745 ||' 2,'
2746 ||' :v_refresh_id,'
2747 ||' :v_instance_id'
2748 ||' from MRP_AP_RESOURCE_SETUPS_V'||MSC_CL_PULL.v_dblink||' x'
2749 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2750
2751 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup = '||v_sql_stmt);
2752 EXECUTE IMMEDIATE v_sql_stmt
2753 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2754 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RESOURCE_SETUPS_V = '|| SQL%ROWCOUNT);
2755
2756 COMMIT;
2757
2758 MSC_CL_PULL.v_table_name:= 'MSC_ST_SETUP_TRANSITIONS';
2759 MSC_CL_PULL.v_view_name := 'MRP_AP_SETUP_TRANSITIONS_V';
2760
2761 v_sql_stmt:=
2762 ' insert into MSC_ST_SETUP_TRANSITIONS'
2763 ||' ( RESOURCE_ID,'
2764 ||' ORGANIZATION_ID,'
2765 ||' FROM_SETUP_ID,'
2766 ||' TO_SETUP_ID,'
2767 ||' STANDARD_OPERATION_ID,'
2768 ||' TRANSITION_TIME,'
2769 ||' TRANSITION_UOM,'
2770 ||' TRANSITION_PENALTY,'
2771 ||' DELETED_FLAG,'
2775 ||' x.RESOURCE_ID,'
2772 ||' REFRESH_ID,'
2773 ||' SR_INSTANCE_ID)'
2774 ||' select'
2776 ||' x.ORGANIZATION_ID,'
2777 ||' x.FROM_SETUP_ID,'
2778 ||' x.TO_SETUP_ID,'
2779 ||' x.STANDARD_OPERATION_ID,'
2780 ||' x.TRANSITION_TIME,'
2781 ||' x.TRANSITION_UOM,'
2782 ||' x.TRANSITION_PENALTY,'
2783 ||' 2,'
2784 ||' :v_refresh_id,'
2785 ||' :v_instance_id'
2786 ||' from MRP_AP_SETUP_TRANSITIONS_V'||MSC_CL_PULL.v_dblink||' x'
2787 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2788
2789 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup transition = '||v_sql_stmt);
2790 EXECUTE IMMEDIATE v_sql_stmt
2791 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2792 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_SETUP_TRANSITIONS_V = '|| SQL%ROWCOUNT);
2793
2794 COMMIT;
2795
2796 MSC_CL_PULL.v_table_name:= 'MSC_ST_STD_OP_RESOURCES';
2797 MSC_CL_PULL.v_view_name := 'MRP_AP_STD_OP_RESOURCES_V';
2798
2799 v_sql_stmt:=
2800 ' insert into MSC_ST_STD_OP_RESOURCES'
2801 ||' ( STANDARD_OPERATION_ID,'
2802 ||' RESOURCE_ID,'
2803 ||' ORGANIZATION_ID,'
2804 ||' DEPARTMENT_ID,'
2805 ||' OPERATION_CODE,'
2806 ||' RESOURCE_SEQ_NUM,'
2807 ||' RESOURCE_USAGE,'
2808 ||' BASIS_TYPE,'
2809 ||' RESOURCE_UNITS,'
2810 ||' SUBSTITUTE_GROUP_NUM,'
2811 ||' UOM_CODE,'
2812 ||' SCHEDULE_FLAG,'
2813 ||' DELETED_FLAG,'
2814 ||' REFRESH_ID,'
2815 ||' SR_INSTANCE_ID)'
2816 ||' select'
2817 ||' x.STANDARD_OPERATION_ID,'
2818 ||' x.RESOURCE_ID,'
2819 ||' x.ORGANIZATION_ID,'
2820 ||' x.DEPARTMENT_ID,'
2821 ||' x.OPERATION_CODE,'
2822 ||' x.RESOURCE_SEQ_NUM,'
2823 ||' x.RESOURCE_USAGE,'
2824 ||' x.BASIS_TYPE,'
2825 ||' x.RESOURCE_UNITS,'
2826 ||' x.SUBSTITUTE_GROUP_NUM,'
2827 ||' x.UOM_CODE,'
2828 ||' x.SCHEDULE_FLAG,'
2829 ||' 2,'
2830 ||' :v_refresh_id,'
2831 ||' :v_instance_id'
2832 ||' from MRP_AP_STD_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2833 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2834
2835 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap std op resources = '||v_sql_stmt);
2836 EXECUTE IMMEDIATE v_sql_stmt
2837 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2838 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_STD_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2839
2840 COMMIT;
2841
2842 END IF; /* MSC_UTIL.G_APPS115 */
2843
2844 END IF; /* MSC_CL_PULL.BOM_ENABLED */
2845 END LOAD_RESOURCE_SETUP;
2846
2847
2848 END MSC_CL_BOM_PULL;