[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: v_temp_sql1 VARCHAR2(300);
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_lrnn <> -1 THEN
1828: -- BUG 7521174
1829: -- No Quantity filter for net change collection.
1830: -- Only Refresh Number
1831:
1830: -- Only Refresh Number
1831:
1832: lv_qty_sql_temp := NULL;
1833:
1834: ELSIF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
1835: lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1836: ||' 1, DECODE( :v_mps_consume_profile_value, '
1837: ||' 1, x.mps_net_quantity,'
1838: ||' x.net_quantity), '
1844: ||' x.net_quantity), '
1845: ||' x.net_quantity) > 0' ;
1846: END IF;
1847: -- bug5996354
1848: if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1849: MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1850: else
1851: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1852: end if;
1845: ||' x.net_quantity) > 0' ;
1846: END IF;
1847: -- bug5996354
1848: if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1849: MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1850: else
1851: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1852: end if;
1853: IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1847: -- bug5996354
1848: if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1849: MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1850: else
1851: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1852: end if;
1853: IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1854: v_temp_sql1 := ' x.Operation_Code,'
1855: ||' (CASE WHEN x.maintenance_object_source in (1,2) THEN '
1849: MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1850: else
1851: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1852: end if;
1853: IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1854: v_temp_sql1 := ' x.Operation_Code,'
1855: ||' (CASE WHEN x.maintenance_object_source in (1,2) THEN '
1856: ||' to_date(substr(x.WO_ACTUAL_DATES,instr(x.WO_ACTUAL_DATES,''#'')+1),''DD-MON-YYYY'')'
1857: ||' END), ';
1859: v_temp_sql1 := 'NULL,NULL,';
1860: END IF;
1861:
1862: /* set the MTQ , Firm Flag, Scheduled flag */
1863: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1864: IF lv_lbj_details =1 Then
1865: 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,'
1866: ||' 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, ';
1867: v_temp_sql := v_temp_sql
1875: 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,'
1876: ||' 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,';
1877: lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
1878: END IF;
1879: IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1880: v_temp_sql := v_temp_sql||'x.description,';
1881: ELSE
1882: v_temp_sql := v_temp_sql||'NULL,';
1883: END IF;
1880: v_temp_sql := v_temp_sql||'x.description,';
1881: ELSE
1882: v_temp_sql := v_temp_sql||'NULL,';
1883: END IF;
1884: ELSIF MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 THEN
1885: 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,NULL, ';
1886: ELSE
1887: 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,NULL, ';
1888: END IF;
1887: 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,NULL, ';
1888: END IF;
1889:
1890: /*USAF starts*/
1891: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER and MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
1892: v_temp_eam_sql := 'x.SHUTDOWN_TYPE,';
1893: ELSE
1894: v_temp_eam_sql := 'NULL,';
1895: END IF;
1898:
1899: --
1900:
1901:
1902: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1903: --MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1904: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1905:
1906: -- BUG 3036681
1899: --
1900:
1901:
1902: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1903: --MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1904: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1905:
1906: -- BUG 3036681
1907: -- No Need to check for RN2 (on wip_discrete_jobs) as
1900:
1901:
1902: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1903: --MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1904: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1905:
1906: -- BUG 3036681
1907: -- No Need to check for RN2 (on wip_discrete_jobs) as
1908: -- the materialized view on wip_operations now has the columns from
1940: ||' x.OPERATION_HOURS_REQUIRED,'
1941: || v_touch_time
1942: || v_temp_sql1
1943: ||' :v_instance_id'
1944: ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1945: ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1946: /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1947: ||' 1, DECODE( :v_mps_consume_profile_value, '
1948: ||' 1, x.mps_net_quantity,'
1982: ||' x.OPERATION_HOURS_REQUIRED,'
1983: || v_touch_time
1984: || v_temp_sql1
1985: ||' :v_instance_id'
1986: ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1987: ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1988: /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1989: ||' 1, DECODE( :v_mps_consume_profile_value, '
1990: ||' 1, x.mps_net_quantity,'
2085: ||' x.OPERATION_HOURS_REQUIRED,'
2086: || v_touch_time
2087: || v_temp_sql1
2088: ||' :v_instance_id'
2089: ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
2090: ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2091: /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
2092: ||' 1, DECODE( :v_mps_consume_profile_value, '
2093: ||' 1, x.mps_net_quantity,'
2098: || v_res_hrs_left
2099: || lv_cond_sql
2100: || v_union_sql ;
2101:
2102: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2103:
2104: EXECUTE IMMEDIATE v_sql_stmt USING
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2101:
2102: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2103:
2104: EXECUTE IMMEDIATE v_sql_stmt USING
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2102: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2103:
2104: EXECUTE IMMEDIATE v_sql_stmt USING
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2103:
2104: EXECUTE IMMEDIATE v_sql_stmt USING
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2104: EXECUTE IMMEDIATE v_sql_stmt USING
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2105: MSC_CL_PULL.v_dgmt,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2106: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2107: --MSC_CL_PULL.v_mps_consume_profile_value,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2108: MSC_CL_PULL.v_hour_uom,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2109: MSC_CL_PULL.v_hour_uom,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2110: MSC_CL_PULL.v_lrn,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2111: MSC_CL_PULL.v_dgmt,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2112: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2113: --MSC_CL_PULL.v_mps_consume_profile_value,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2114: MSC_CL_PULL.v_hour_uom,
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2122: MSC_CL_PULL.v_lrn;
2115: MSC_CL_PULL.v_hour_uom,
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2122: MSC_CL_PULL.v_lrn;
2123:
2116: MSC_CL_PULL.v_lrn,
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2122: MSC_CL_PULL.v_lrn;
2123:
2124: ELSE
2117: MSC_CL_PULL.v_dgmt,
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2122: MSC_CL_PULL.v_lrn;
2123:
2124: ELSE
2125:
2118: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119: --MSC_CL_PULL.v_mps_consume_profile_value,
2120: MSC_CL_PULL.v_hour_uom,
2121: MSC_CL_PULL.v_hour_uom,
2122: MSC_CL_PULL.v_lrn;
2123:
2124: ELSE
2125:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2122: MSC_CL_PULL.v_lrn;
2123:
2124: ELSE
2125:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128: MSC_CL_PULL.v_mps_consume_profile_value,
2129: MSC_CL_PULL.v_hour_uom,
2130: MSC_CL_PULL.v_hour_uom;
2123:
2124: ELSE
2125:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128: MSC_CL_PULL.v_mps_consume_profile_value,
2129: MSC_CL_PULL.v_hour_uom,
2130: MSC_CL_PULL.v_hour_uom;
2131: END IF;
2124: ELSE
2125:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128: MSC_CL_PULL.v_mps_consume_profile_value,
2129: MSC_CL_PULL.v_hour_uom,
2130: MSC_CL_PULL.v_hour_uom;
2131: END IF;
2132: COMMIT;
2125:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128: MSC_CL_PULL.v_mps_consume_profile_value,
2129: MSC_CL_PULL.v_hour_uom,
2130: MSC_CL_PULL.v_hour_uom;
2131: END IF;
2132: COMMIT;
2133:
2126: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128: MSC_CL_PULL.v_mps_consume_profile_value,
2129: MSC_CL_PULL.v_hour_uom,
2130: MSC_CL_PULL.v_hour_uom;
2131: END IF;
2132: COMMIT;
2133:
2134: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2130: MSC_CL_PULL.v_hour_uom;
2131: END IF;
2132: COMMIT;
2133:
2134: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2135:
2136: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2137: MSC_CL_PULL.v_view_name := 'MRP_AP_DJOB_SUB_OP_RESOURCES_V';
2138:
2132: COMMIT;
2133:
2134: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2135:
2136: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2137: MSC_CL_PULL.v_view_name := 'MRP_AP_DJOB_SUB_OP_RESOURCES_V';
2138:
2139:
2140: v_sql_stmt:=
2133:
2134: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2135:
2136: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2137: MSC_CL_PULL.v_view_name := 'MRP_AP_DJOB_SUB_OP_RESOURCES_V';
2138:
2139:
2140: v_sql_stmt:=
2141: 'insert into MSC_ST_JOB_OP_RESOURCES'
2185: ||' x.SCHEDULE_FLAG,'
2186: ||' x.SETUP_ID,'
2187: ||' 2,'
2188: ||' :v_refresh_id'
2189: ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2190: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2191: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2187: ||' 2,'
2188: ||' :v_refresh_id'
2189: ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2190: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2191: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2194:
2195: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2188: ||' :v_refresh_id'
2189: ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2190: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2191: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2194:
2195: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2196:
2189: ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2190: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2191: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2194:
2195: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2196:
2197: COMMIT;
2191: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2194:
2195: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2196:
2197: COMMIT;
2198:
2199: END IF; /* MSC_UTIL.G_APPS115 */
2198:
2199: END IF; /* MSC_UTIL.G_APPS115 */
2200:
2201:
2202: END IF; -- MSC_CL_PULL.WIP_ENABLED
2203:
2204: END LOAD_RESOURCE;
2205:
2206: /* ds change start */
2214: lv_op_seq_num VARCHAR2(100) := null;
2215:
2216: BEGIN
2217:
2218: IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2219: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2220: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2221: MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2222:
2215:
2216: BEGIN
2217:
2218: IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2219: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2220: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2221: MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2222:
2223: v_sql_stmt:=
2216: BEGIN
2217:
2218: IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2219: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2220: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2221: MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2222:
2223: v_sql_stmt:=
2224: ' insert into MSC_ST_DEPT_RES_INSTANCES'
2217:
2218: IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2219: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2220: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2221: MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2222:
2223: v_sql_stmt:=
2224: ' insert into MSC_ST_DEPT_RES_INSTANCES'
2225: ||' ( RESOURCE_ID,'
2246: ||' x.LAST_KNOWN_SETUP,'
2247: ||' 2,'
2248: ||' :v_refresh_id,'
2249: ||' :v_instance_id'
2250: ||' from MRP_AP_DEPT_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2251: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2252:
2253: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: dept_res_instance sql = '||v_sql_stmt);
2254: EXECUTE IMMEDIATE v_sql_stmt USING
2251: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2252:
2253: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: dept_res_instance sql = '||v_sql_stmt);
2254: EXECUTE IMMEDIATE v_sql_stmt USING
2255: MSC_CL_PULL.v_refresh_id,
2256: MSC_CL_PULL.v_instance_id;
2257:
2258:
2259: COMMIT;
2252:
2253: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: dept_res_instance sql = '||v_sql_stmt);
2254: EXECUTE IMMEDIATE v_sql_stmt USING
2255: MSC_CL_PULL.v_refresh_id,
2256: MSC_CL_PULL.v_instance_id;
2257:
2258:
2259: COMMIT;
2260:
2260:
2261: END IF;
2262:
2263:
2264: IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2265: (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266:
2267: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2261: END IF;
2262:
2263:
2264: IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2265: (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266:
2267: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2269: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2263:
2264: IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2265: (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266:
2267: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2269: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2270: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2271:
2264: IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2265: (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266:
2267: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2269: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2270: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2271:
2272: v_sql_stmt:=
2265: (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266:
2267: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2269: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2270: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2271:
2272: v_sql_stmt:=
2273: 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2287: ||' x.SERIAL_NUMBER,'
2288: ||' 1,'
2289: ||' :v_refresh_id,'
2290: ||' :v_instance_id'
2291: ||' from MRP_AD_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2292: ||' WHERE x.RN > :v_lrn '
2293: || v_temp_sql;
2294:
2295:
2293: || v_temp_sql;
2294:
2295:
2296: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance sql = '||v_sql_stmt);
2297: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2298: MSC_CL_PULL.v_instance_id,
2299: MSC_CL_PULL.v_lrn;
2300:
2301:
2294:
2295:
2296: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance sql = '||v_sql_stmt);
2297: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2298: MSC_CL_PULL.v_instance_id,
2299: MSC_CL_PULL.v_lrn;
2300:
2301:
2302: COMMIT;
2295:
2296: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance sql = '||v_sql_stmt);
2297: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2298: MSC_CL_PULL.v_instance_id,
2299: MSC_CL_PULL.v_lrn;
2300:
2301:
2302: COMMIT;
2303:
2300:
2301:
2302: COMMIT;
2303:
2304: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2305: MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_OPR_RES_INSTS_V';
2306:
2307:
2308: v_sql_stmt:=
2301:
2302: COMMIT;
2303:
2304: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2305: MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_OPR_RES_INSTS_V';
2306:
2307:
2308: v_sql_stmt:=
2309: 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2323: ||' x.SERIAL_NUMBER,'
2324: ||' 1,'
2325: ||' :v_refresh_id,'
2326: ||' :v_instance_id'
2327: ||' from MRP_AD_LJ_OPR_RES_INSTS_V'||MSC_CL_PULL.v_dblink||' x'
2328: ||' WHERE x.RN > :v_lrn '
2329: || v_temp_sql;
2330:
2331: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2328: ||' WHERE x.RN > :v_lrn '
2329: || v_temp_sql;
2330:
2331: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2332: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2333: MSC_CL_PULL.v_instance_id,
2334: MSC_CL_PULL.v_lrn;
2335: COMMIT;
2336:
2329: || v_temp_sql;
2330:
2331: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2332: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2333: MSC_CL_PULL.v_instance_id,
2334: MSC_CL_PULL.v_lrn;
2335: COMMIT;
2336:
2337: END IF; /* incremental */
2330:
2331: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2332: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2333: MSC_CL_PULL.v_instance_id,
2334: MSC_CL_PULL.v_lrn;
2335: COMMIT;
2336:
2337: END IF; /* incremental */
2338:
2336:
2337: END IF; /* incremental */
2338:
2339:
2340: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2341: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INSTANCE_REQS_V';
2342:
2343:
2344: IF lv_lbj_details <> 1 THEN
2337: END IF; /* incremental */
2338:
2339:
2340: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2341: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INSTANCE_REQS_V';
2342:
2343:
2344: IF lv_lbj_details <> 1 THEN
2345: lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
2344: IF lv_lbj_details <> 1 THEN
2345: lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
2346: END IF;
2347:
2348: IF MSC_CL_PULL.v_lrnn<> -1 THEN /* incremental */
2349:
2350: v_union_sql :=
2351: ' AND ( x.RN1> :v_lrn )'
2352: ||' UNION '
2367: ||' x.COMPLETION_DATE - :v_dgmt,'
2368: ||' 2,'
2369: ||' :v_refresh_id,'
2370: ||' :v_instance_id'
2371: ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2372: ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2373: || lv_cond_sql
2374: ||' AND ( x.RN2> :v_lrn )'
2375: ||' UNION '
2390: ||' x.COMPLETION_DATE- :v_dgmt,'
2391: ||' 2,'
2392: ||' :v_refresh_id,'
2393: ||' :v_instance_id'
2394: ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2395: ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2396: || lv_cond_sql
2397: ||' AND ( x.RN3> :v_lrn )'
2398: ||' UNION '
2413: ||' x.COMPLETION_DATE - :v_dgmt,'
2414: ||' 2,'
2415: ||' :v_refresh_id,'
2416: ||' :v_instance_id'
2417: ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2418: ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2419: || lv_cond_sql
2420: ||' AND ( x.RN4> :v_lrn )';
2421: ELSE -- full
2421: ELSE -- full
2422:
2423: v_union_sql := ' ';
2424:
2425: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
2426:
2427:
2428: v_sql_stmt:=
2429: 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2461: ||' x.COMPLETION_DATE - :v_dgmt,'
2462: ||' 2,'
2463: ||' :v_refresh_id,'
2464: ||' :v_instance_id'
2465: ||' from MRP_AP_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2466: ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2467: || lv_cond_sql
2468: || v_union_sql ;
2469:
2468: || v_union_sql ;
2469:
2470:
2471: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' Ds debug: insert from MRP_AP_RES_INSTANCE_REQS_V sql = '||v_sql_stmt);
2472: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2473:
2474: EXECUTE IMMEDIATE v_sql_stmt USING
2475: 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,
2476: 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,
2471: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' Ds debug: insert from MRP_AP_RES_INSTANCE_REQS_V sql = '||v_sql_stmt);
2472: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2473:
2474: EXECUTE IMMEDIATE v_sql_stmt USING
2475: 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,
2476: 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,
2477: 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,
2478: 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;
2479:
2472: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2473:
2474: EXECUTE IMMEDIATE v_sql_stmt USING
2475: 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,
2476: 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,
2477: 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,
2478: 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;
2479:
2480: ELSE
2473:
2474: EXECUTE IMMEDIATE v_sql_stmt USING
2475: 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,
2476: 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,
2477: 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,
2478: 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;
2479:
2480: ELSE
2481:
2474: EXECUTE IMMEDIATE v_sql_stmt USING
2475: 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,
2476: 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,
2477: 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,
2478: 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;
2479:
2480: ELSE
2481:
2482: EXECUTE IMMEDIATE v_sql_stmt USING
2479:
2480: ELSE
2481:
2482: EXECUTE IMMEDIATE v_sql_stmt USING
2483: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2484: END IF;
2485:
2486: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for Wip Job Res Instances='|| SQL%ROWCOUNT);
2487: COMMIT;
2485:
2486: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for Wip Job Res Instances='|| SQL%ROWCOUNT);
2487: COMMIT;
2488:
2489: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2490: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_RES_INSTANCES_V';
2491:
2492:
2493: v_sql_stmt:=
2486: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for Wip Job Res Instances='|| SQL%ROWCOUNT);
2487: COMMIT;
2488:
2489: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2490: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_RES_INSTANCES_V';
2491:
2492:
2493: v_sql_stmt:=
2494: 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2522: ||' x.COMPLETION_DATE - :v_dgmt,'
2523: ||' x.BATCH_NUMBER,'
2524: ||' 2,'
2525: ||' :v_refresh_id'
2526: ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2527: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2528: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2529: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2524: ||' 2,'
2525: ||' :v_refresh_id'
2526: ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2527: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2528: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2529: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2531:
2532: --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);
2525: ||' :v_refresh_id'
2526: ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2527: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2528: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2529: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2531:
2532: --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);
2533: 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;
2526: ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2527: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2528: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2529: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2531:
2532: --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);
2533: 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;
2534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for lJob Res Instances= '||SQL%ROWCOUNT);
2529: ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2531:
2532: --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);
2533: 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;
2534: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for lJob Res Instances= '||SQL%ROWCOUNT);
2535:
2536: COMMIT;
2537:
2581: Begin
2582:
2583: lv_retval := FND_INSTALLATION.GET_APP_INFO ('WSM', lv_dummy1, lv_dummy2,v_wsm_schema);
2584: -- adding this piece of code to ensure that WSM_COPRODUCT_SPLIT_PERC is used only when it exists
2585: v_wsm_split_table_qry := 'select count(*) from all_tables'||MSC_CL_PULL.v_dblink||
2586: ' where owner=:v_wsm_schema and table_name = ''WSM_COPRODUCT_SPLIT_PERC''';
2587:
2588: EXECUTE IMMEDIATE v_wsm_split_table_qry into v_split_table_exist using v_wsm_schema;
2589:
2593: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WSM_COPRODUCT_SPLIT_PERC does not exist. Using split from WSM_CO_PRODUCTS');
2594:
2595: v_query_str := 'Select bill_sequence_id*2,co_product_id,split,' ||
2596: 'effectivity_date, disable_date, decode(nvl(primary_flag,''N''),''N'',2,1) primary_flag' ||
2597: 'from wsm_co_products'||MSC_CL_PULL.v_dblink|| -- Edited for BUG 14133049 (FP of SUN BUG 13530488)
2598: ' where bill_sequence_id <>:v_bill_seq_id and bill_sequence_id is not null
2599: and co_product_group_id = :v_co_prd_grp_id
2600: and perc.effectivity_date = :v_effectivity_date_var
2601: and split > 0';
2608: wsc.split,
2609: perc.effectivity_date, -- Added for BUG 14133049/13530488
2610: decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2611: bom.rowid
2612: from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2613: msc_st_boms bom
2614: where wsc.bill_sequence_id is not null
2615: and wsc.split > 0 -- Added this for bug:2208074
2616: and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id';
2623: perc.split,
2624: perc.effectivity_date, -- Added for BUG 14133049/13530488
2625: perc.disable_date, -- Added for BUG 14133049/13530488
2626: decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag
2627: from wsm_co_products'||MSC_CL_PULL.v_dblink|| ' wsc,
2628: wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc
2629: where wsc.bill_sequence_id <>:v_bill_seq_id
2630: and wsc.bill_sequence_id is not null
2631: and wsc.co_product_group_id = :v_co_prd_grp_id
2624: perc.effectivity_date, -- Added for BUG 14133049/13530488
2625: perc.disable_date, -- Added for BUG 14133049/13530488
2626: decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag
2627: from wsm_co_products'||MSC_CL_PULL.v_dblink|| ' wsc,
2628: wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc
2629: where wsc.bill_sequence_id <>:v_bill_seq_id
2630: and wsc.bill_sequence_id is not null
2631: and wsc.co_product_group_id = :v_co_prd_grp_id
2632: and perc.co_product_group_id = wsc.co_product_group_id
2643: perc.split,
2644: perc.effectivity_date, -- Added for BUG 14133049/13530488
2645: decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2646: bom.rowid
2647: from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2648: wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc,
2649: msc_st_boms bom
2650: where wsc.bill_sequence_id is not null
2651: and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
2644: perc.effectivity_date, -- Added for BUG 14133049/13530488
2645: decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2646: bom.rowid
2647: from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2648: wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc,
2649: msc_st_boms bom
2650: where wsc.bill_sequence_id is not null
2651: and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
2652: and perc.co_product_group_id = wsc.co_product_group_id
2680: ' and x.primary_flag is null
2681: and nvl(x.component_type,0) != 10
2682: and x.usage_quantity > 0
2683: and x.inventory_item_id in
2684: (select wsm.component_id from wsm_co_products' ||MSC_CL_PULL.v_dblink||' wsm
2685: where wsm.bill_sequence_id ='|| l_bill_sequence_id/2||')';
2686:
2687: EXECUTE IMMEDIATE v_query_str4;
2688:
2746: v_primary_flag,
2747: 3
2748: from MSC_ST_BOM_COMPONENTS x
2749: WHERE x.bill_Sequence_id = l_BILL_SEQuence_ID
2750: and x.sr_instance_id = MSC_CL_PULL.v_instance_id
2751: and rownum = 1;
2752:
2753: End loop;
2754:
2768:
2769: PROCEDURE LOAD_RESOURCE_SETUP IS
2770: BEGIN
2771:
2772: IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2773: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2774:
2775: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2776: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2769: PROCEDURE LOAD_RESOURCE_SETUP IS
2770: BEGIN
2771:
2772: IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2773: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2774:
2775: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2776: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2777:
2771:
2772: IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2773: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2774:
2775: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2776: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2777:
2778: v_sql_stmt:=
2779: ' insert into MSC_ST_RESOURCE_SETUPS'
2772: IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2773: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2774:
2775: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2776: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2777:
2778: v_sql_stmt:=
2779: ' insert into MSC_ST_RESOURCE_SETUPS'
2780: ||' ( RESOURCE_ID,'
2795: ||' x.SETUP_DESCRIPTION,'
2796: ||' 2,'
2797: ||' :v_refresh_id,'
2798: ||' :v_instance_id'
2799: ||' from MRP_AP_RESOURCE_SETUPS_V'||MSC_CL_PULL.v_dblink||' x'
2800: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2801:
2802: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup = '||v_sql_stmt);
2803: EXECUTE IMMEDIATE v_sql_stmt
2800: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2801:
2802: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup = '||v_sql_stmt);
2803: EXECUTE IMMEDIATE v_sql_stmt
2804: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2805: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RESOURCE_SETUPS_V = '|| SQL%ROWCOUNT);
2806:
2807: COMMIT;
2808:
2805: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RESOURCE_SETUPS_V = '|| SQL%ROWCOUNT);
2806:
2807: COMMIT;
2808:
2809: MSC_CL_PULL.v_table_name:= 'MSC_ST_SETUP_TRANSITIONS';
2810: MSC_CL_PULL.v_view_name := 'MRP_AP_SETUP_TRANSITIONS_V';
2811:
2812: v_sql_stmt:=
2813: ' insert into MSC_ST_SETUP_TRANSITIONS'
2806:
2807: COMMIT;
2808:
2809: MSC_CL_PULL.v_table_name:= 'MSC_ST_SETUP_TRANSITIONS';
2810: MSC_CL_PULL.v_view_name := 'MRP_AP_SETUP_TRANSITIONS_V';
2811:
2812: v_sql_stmt:=
2813: ' insert into MSC_ST_SETUP_TRANSITIONS'
2814: ||' ( RESOURCE_ID,'
2833: ||' x.TRANSITION_PENALTY,'
2834: ||' 2,'
2835: ||' :v_refresh_id,'
2836: ||' :v_instance_id'
2837: ||' from MRP_AP_SETUP_TRANSITIONS_V'||MSC_CL_PULL.v_dblink||' x'
2838: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2839:
2840: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup transition = '||v_sql_stmt);
2841: EXECUTE IMMEDIATE v_sql_stmt
2838: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2839:
2840: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup transition = '||v_sql_stmt);
2841: EXECUTE IMMEDIATE v_sql_stmt
2842: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_SETUP_TRANSITIONS_V = '|| SQL%ROWCOUNT);
2844:
2845: COMMIT;
2846:
2843: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_SETUP_TRANSITIONS_V = '|| SQL%ROWCOUNT);
2844:
2845: COMMIT;
2846:
2847: MSC_CL_PULL.v_table_name:= 'MSC_ST_STD_OP_RESOURCES';
2848: MSC_CL_PULL.v_view_name := 'MRP_AP_STD_OP_RESOURCES_V';
2849:
2850: v_sql_stmt:=
2851: ' insert into MSC_ST_STD_OP_RESOURCES'
2844:
2845: COMMIT;
2846:
2847: MSC_CL_PULL.v_table_name:= 'MSC_ST_STD_OP_RESOURCES';
2848: MSC_CL_PULL.v_view_name := 'MRP_AP_STD_OP_RESOURCES_V';
2849:
2850: v_sql_stmt:=
2851: ' insert into MSC_ST_STD_OP_RESOURCES'
2852: ||' ( STANDARD_OPERATION_ID,'
2879: ||' x.SCHEDULE_FLAG,'
2880: ||' 2,'
2881: ||' :v_refresh_id,'
2882: ||' :v_instance_id'
2883: ||' from MRP_AP_STD_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2884: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2885:
2886: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap std op resources = '||v_sql_stmt);
2887: EXECUTE IMMEDIATE v_sql_stmt
2884: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2885:
2886: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap std op resources = '||v_sql_stmt);
2887: EXECUTE IMMEDIATE v_sql_stmt
2888: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2889: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_STD_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2890:
2891: COMMIT;
2892:
2891: COMMIT;
2892:
2893: END IF; /* MSC_UTIL.G_APPS115 */
2894:
2895: END IF; /* MSC_CL_PULL.BOM_ENABLED */
2896: END LOAD_RESOURCE_SETUP;
2897:
2898:
2899: END MSC_CL_BOM_PULL;