[Home] [Help]
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
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;
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:
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;
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
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;
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:
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;
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
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 ;
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
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;
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:
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 :=
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 '
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:
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 '
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 '
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 '
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:
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';
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:=
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
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,
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,
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;
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: */
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:
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
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:
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:
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:
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;
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
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;
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;
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;
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:
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 :=
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 '
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
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
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
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;
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:
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:
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';
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:
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'
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,'
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;
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:
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
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),';
493: v_rounding_sql :='3,';
494: END IF ;
495:
494: END IF ;
495:
496:
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 '
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'
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'
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
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'
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:
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:
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,
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,
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;
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
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:
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,
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,
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;
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:
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
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:
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:
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,
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',
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';
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:
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'
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,'
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;
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:
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;
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||')'
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: /*
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 '
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,'
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:
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'
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'
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,'
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 '
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,'
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
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
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:
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;
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:
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:=
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
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,
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,
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,
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,
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,
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;
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:
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: /*
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,
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,
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,
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;
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: */
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:
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
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,
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;
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:
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
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:
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'
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,'
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:
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:
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;
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';
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'
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,'
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
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;
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:
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;
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;
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
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,';
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;
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;
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,'
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,
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,
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;
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
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:=
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'
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,'
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,
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,
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,
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,
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,
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,
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,
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;
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:
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;
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:
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;
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:
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:
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 :=
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:
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
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:
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;
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;
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';
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'
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,'
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:
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
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:
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'
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,'
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;
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;
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
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
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
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,';
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;
1713: ELSE
1714: v_temp_parent_seq := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1715: END IF;
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
1719: where instance_id = MSC_CL_PULL.v_instance_id ;
1720: end if;
1721:
1715: END IF;
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
1719: where instance_id = MSC_CL_PULL.v_instance_id ;
1720: end if;
1721:
1722: --=================== Net Change Mode: Delete ==================
1723: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1719: where instance_id = MSC_CL_PULL.v_instance_id ;
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
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';
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
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;
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: --
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
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;
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, ';
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,'
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:
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;
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:
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
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:
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'
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,'
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,'
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), '
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;
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:
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;
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), '
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, ';
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
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, ';
1856: END IF;
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, ';
1856: END IF;
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;
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, ';
1856: END IF;
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: --
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:
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
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
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
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,'
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,'
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,'
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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;
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:
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
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:
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,
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;
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;
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;
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:
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
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:
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:=
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'
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 || ' )';
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;
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:
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;
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 */
2153:
2154: END IF; /* MSC_UTIL.G_APPS115 */
2155:
2156:
2157: END IF; -- MSC_CL_PULL.WIP_ENABLED
2158:
2159: END LOAD_RESOURCE;
2160:
2161: /* ds change start */
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:
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:=
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'
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,'
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
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;
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:
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';
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';
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:
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:=
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'
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:
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:
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;
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:
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:=
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'
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);
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:
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 */
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:
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
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) ';
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 '
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 '
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 '
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
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'
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:
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,
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:
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
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:
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
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;
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:=
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'
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 || ' )';
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);
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;
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);
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:
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:
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,
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';
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
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
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
2593: and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
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
2593: and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
2594: and perc.co_product_group_id = wsc.co_product_group_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:
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:
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';
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:
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'
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,'
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
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:
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'
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,'
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
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:
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'
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,'
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
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:
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;