[Home] [Help]
22: PROCEDURE LOAD_OPER_NETWORKS IS
23: v_get_apps_ver number;
24: BEGIN
25:
26: IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
27:
28: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
29:
30:
24: BEGIN
25:
26: IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
27:
28: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
29:
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPER_NETWORKS';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_OPER_NETWORKS_V';
27:
28: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
29:
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPER_NETWORKS';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_OPER_NETWORKS_V';
33:
34: if v_once_per_org <> 'DONE'
35: then
28: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
29:
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPER_NETWORKS';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_OPER_NETWORKS_V';
33:
34: if v_once_per_org <> 'DONE'
35: then
36:
34: if v_once_per_org <> 'DONE'
35: then
36:
37:
38: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
39:
40: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
41: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
42: ELSE
36:
37:
38: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
39:
40: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
41: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
42: ELSE
43: v_temp_sql := NULL;
44: END IF;
55: ||' x.TO_OP_SEQ_ID, '
56: ||' 1,'
57: ||' :v_refresh_id,'
58: ||' :v_instance_id'
59: ||' FROM MRP_AD_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
60: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
61: || v_temp_sql;
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
56: ||' 1,'
57: ||' :v_refresh_id,'
58: ||' :v_instance_id'
59: ||' FROM MRP_AD_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
60: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
61: || v_temp_sql;
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
64:
59: ||' FROM MRP_AD_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
60: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
61: || v_temp_sql;
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
64:
65: COMMIT;
66:
67: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
64:
65: COMMIT;
66:
67: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
68:
69: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_NETWORKS';
70: MSC_CL_PULL.v_view_name := 'MRP_AP_OPER_NETWORKS_V';
71:
65: COMMIT;
66:
67: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
68:
69: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_NETWORKS';
70: MSC_CL_PULL.v_view_name := 'MRP_AP_OPER_NETWORKS_V';
71:
72: v_sql_stmt:= ' insert into MSC_ST_OPERATION_NETWORKS'
73: ||'( FROM_OP_SEQ_ID, '
66:
67: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
68:
69: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_NETWORKS';
70: MSC_CL_PULL.v_view_name := 'MRP_AP_OPER_NETWORKS_V';
71:
72: v_sql_stmt:= ' insert into MSC_ST_OPERATION_NETWORKS'
73: ||'( FROM_OP_SEQ_ID, '
74: ||'TO_OP_SEQ_ID, '
144: ||' x.FROM_SEQ_NUM,'
145: ||' x.TO_SEQ_NUM, '
146: ||' :v_refresh_id,'
147: ||' :v_instance_id '
148: ||' from MRP_AP_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
149: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
146: ||' :v_refresh_id,'
147: ||' :v_instance_id '
148: ||' from MRP_AP_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
149: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
147: ||' :v_instance_id '
148: ||' from MRP_AP_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
149: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
148: ||' from MRP_AP_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
149: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
156:
149: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
156:
157: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
150: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
156:
157: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
158:
151: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
152: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
156:
157: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
158:
159: v_once_per_org := 'DONE';
153: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
154: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
155: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
156:
157: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
158:
159: v_once_per_org := 'DONE';
160: -- opm populates operation network in call to
161: -- extract_effectivities
162:
163: End if; /* v_once_per_org */
164:
165: End if; /* MSC_UTIL.G_APPS115 */
166: END IF; -- MSC_CL_PULL.BOM_ENABLED
167:
168: END LOAD_OPER_NETWORKS;
169:
170:
180: v_temp_sql_stmt2 varchar2(10240) := null;
181: v_temp_sql2 varchar2(10240) := null;
182: BEGIN
183:
184: IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
185:
186: --=================== Net Change Mode: Delete ==================
187:
188: lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
185:
186: --=================== Net Change Mode: Delete ==================
187:
188: lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
189: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
190:
191:
192: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
193: where instance_id = MSC_CL_PULL.v_instance_id ;
189: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
190:
191:
192: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
193: where instance_id = MSC_CL_PULL.v_instance_id ;
194:
195: if lv_lbj_details = 1 Then
196: lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
197: else
200:
201: END IF;
202:
203:
204: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
205:
206: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
207:
208: -- For Demands from non depo orgs
202:
203:
204: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
205:
206: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
207:
208: -- For Demands from non depo orgs
209:
210: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
206: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
207:
208: -- For Demands from non depo orgs
209:
210: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
211: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_COMP_DEM_V';
212:
213: v_sql_stmt:=
214: 'insert into MSC_ST_DEMANDS'
207:
208: -- For Demands from non depo orgs
209:
210: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
211: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_COMP_DEM_V';
212:
213: v_sql_stmt:=
214: 'insert into MSC_ST_DEMANDS'
215: ||' ( WIP_ENTITY_ID,'
228: ||' x.ORIGINATION_TYPE,'
229: ||' 1,'
230: ||' :v_refresh_id,'
231: ||' :v_instance_id'
232: ||' from MRP_AD_NON_ERO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
233: ||' where ( DECODE( :v_mps_consume_profile_value,'
234: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
235: ||' x.WJS_NET_QTY_FLAG)=1'
236: ||' OR x.MRP_NET_FLAG= 1'
241: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str ;
242:
243: -- For Demands from depot repair orgs
244:
245: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
246: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_COMP_DEM_V';
247:
248: v_temp_sql:=
249: 'insert into MSC_ST_DEMANDS'
242:
243: -- For Demands from depot repair orgs
244:
245: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
246: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_COMP_DEM_V';
247:
248: v_temp_sql:=
249: 'insert into MSC_ST_DEMANDS'
250: ||' ( WIP_ENTITY_ID,'
263: ||' x.ORIGINATION_TYPE,'
264: ||' 1,'
265: ||' :v_refresh_id,'
266: ||' :v_instance_id'
267: ||' from MRP_AD_NON_RO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
268: ||' where ( DECODE( :v_mps_consume_profile_value,'
269: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
270: ||' x.WJS_NET_QTY_FLAG)=1'
271: ||' OR x.MRP_NET_FLAG= 1'
274: ||' x.NMPS_FLAG)= 1 )'
275: ||' AND x.RN> :v_lrn '
276: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str ;
277:
278: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
279: MSC_CL_PULL.v_instance_id,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
275: ||' AND x.RN> :v_lrn '
276: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str ;
277:
278: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
279: MSC_CL_PULL.v_instance_id,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
276: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str ;
277:
278: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
279: MSC_CL_PULL.v_instance_id,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
277:
278: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
279: MSC_CL_PULL.v_instance_id,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
278: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
279: MSC_CL_PULL.v_instance_id,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
280: MSC_CL_PULL.v_mps_consume_profile_value,
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
287: MSC_CL_PULL.v_mps_consume_profile_value,
288: MSC_CL_PULL.v_lrn;
281: MSC_CL_PULL.v_mps_consume_profile_value,
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
287: MSC_CL_PULL.v_mps_consume_profile_value,
288: MSC_CL_PULL.v_lrn;
289:
282: MSC_CL_PULL.v_lrn;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
287: MSC_CL_PULL.v_mps_consume_profile_value,
288: MSC_CL_PULL.v_lrn;
289:
290: COMMIT;
283:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
287: MSC_CL_PULL.v_mps_consume_profile_value,
288: MSC_CL_PULL.v_lrn;
289:
290: COMMIT;
291:
284: EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
285: MSC_CL_PULL.v_instance_id,
286: MSC_CL_PULL.v_mps_consume_profile_value,
287: MSC_CL_PULL.v_mps_consume_profile_value,
288: MSC_CL_PULL.v_lrn;
289:
290: COMMIT;
291:
292: ELSE -- If srp profile is No
290: COMMIT;
291:
292: ELSE -- If srp profile is No
293:
294: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
295: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_DEMANDS_V';
296:
297: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
298: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
291:
292: ELSE -- If srp profile is No
293:
294: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
295: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_DEMANDS_V';
296:
297: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
298: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
299: ELSE
293:
294: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
295: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_DEMANDS_V';
296:
297: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
298: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
299: ELSE
300: v_temp_sql := NULL;
301: END IF;
318: ||' x.ORIGINATION_TYPE,'
319: ||' 1,'
320: ||' :v_refresh_id,'
321: ||' :v_instance_id'
322: ||' from MRP_AD_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
323: ||' where ( DECODE( :v_mps_consume_profile_value,'
324: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
325: ||' x.WJS_NET_QTY_FLAG)=1'
326: ||' OR x.MRP_NET_FLAG= 1'
329: ||' x.NMPS_FLAG)= 1 )'
330: ||' AND x.RN> :v_lrn '
331: || v_temp_sql;
332:
333: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
334: MSC_CL_PULL.v_instance_id,
335: MSC_CL_PULL.v_mps_consume_profile_value,
336: MSC_CL_PULL.v_mps_consume_profile_value,
337: MSC_CL_PULL.v_lrn;
330: ||' AND x.RN> :v_lrn '
331: || v_temp_sql;
332:
333: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
334: MSC_CL_PULL.v_instance_id,
335: MSC_CL_PULL.v_mps_consume_profile_value,
336: MSC_CL_PULL.v_mps_consume_profile_value,
337: MSC_CL_PULL.v_lrn;
338:
331: || v_temp_sql;
332:
333: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
334: MSC_CL_PULL.v_instance_id,
335: MSC_CL_PULL.v_mps_consume_profile_value,
336: MSC_CL_PULL.v_mps_consume_profile_value,
337: MSC_CL_PULL.v_lrn;
338:
339: COMMIT;
332:
333: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
334: MSC_CL_PULL.v_instance_id,
335: MSC_CL_PULL.v_mps_consume_profile_value,
336: MSC_CL_PULL.v_mps_consume_profile_value,
337: MSC_CL_PULL.v_lrn;
338:
339: COMMIT;
340: END IF;
333: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
334: MSC_CL_PULL.v_instance_id,
335: MSC_CL_PULL.v_mps_consume_profile_value,
336: MSC_CL_PULL.v_mps_consume_profile_value,
337: MSC_CL_PULL.v_lrn;
338:
339: COMMIT;
340: END IF;
341: END IF;
340: END IF;
341: END IF;
342:
343:
344: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
345:
346: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
347: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_FLOW_DEMANDS_V';
348:
342:
343:
344: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
345:
346: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
347: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_FLOW_DEMANDS_V';
348:
349: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
350: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
343:
344: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
345:
346: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
347: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_FLOW_DEMANDS_V';
348:
349: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
350: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
351: ELSE
345:
346: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
347: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_FLOW_DEMANDS_V';
348:
349: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
350: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
351: ELSE
352: v_temp_sql := NULL;
353: END IF;
366: ||' x.ORGANIZATION_ID,'
367: ||' 1,'
368: ||' :v_refresh_id,'
369: ||' :v_instance_id'
370: ||' from MRP_AD_WIP_FLOW_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
371: ||' where x.RN> :v_lrn '
372: || v_temp_sql;
373:
374: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
370: ||' from MRP_AD_WIP_FLOW_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
371: ||' where x.RN> :v_lrn '
372: || v_temp_sql;
373:
374: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
375: MSC_CL_PULL.v_instance_id,
376: MSC_CL_PULL.v_lrn;
377:
378: COMMIT;
371: ||' where x.RN> :v_lrn '
372: || v_temp_sql;
373:
374: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
375: MSC_CL_PULL.v_instance_id,
376: MSC_CL_PULL.v_lrn;
377:
378: COMMIT;
379:
372: || v_temp_sql;
373:
374: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
375: MSC_CL_PULL.v_instance_id,
376: MSC_CL_PULL.v_lrn;
377:
378: COMMIT;
379:
380: END IF;
378: COMMIT;
379:
380: END IF;
381:
382: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
383: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
384:
385: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
386: v_temp_sql := 'x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '; /* ds change */
379:
380: END IF;
381:
382: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
383: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
384:
385: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
386: v_temp_sql := 'x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '; /* ds change */
387:
381:
382: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
383: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
384:
385: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
386: v_temp_sql := 'x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '; /* ds change */
387:
388: ELSE
389:
389:
390: v_temp_sql := ' NULL, NULL, NULL, NULL, NULL, '; /* ds change */
391: END IF;
392:
393: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
394:
395: -- BUG 3036681
396: -- No need to check on RN3 (on wip_discrete_jobs) as the
397: -- materialized view on wip_requirement_operations now has
425: ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
426: ||' 2,'
427: ||' :v_refresh_id,'
428: ||' :v_instance_id'
429: ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
430: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
431: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
432: */
433: ||' UNION '
427: ||' :v_refresh_id,'
428: ||' :v_instance_id'
429: ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
430: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
431: ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
432: */
433: ||' UNION '
434: ||' select'
435: ||' x.INVENTORY_ITEM_ID,'
457: ||' 2,'
458: || v_temp_sql
459: ||' :v_refresh_id,'
460: ||' :v_instance_id'
461: ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
462: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
463: ||' AND x.NEW_REQUIRED_QUANTITY > 0'
464: --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
465: --||' 2 ,x.coll_completed_qty_ind,'
495: ||' 2,'
496: || v_temp_sql
497: ||' :v_refresh_id,'
498: ||' :v_instance_id'
499: ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
500: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
501: ||' AND x.NEW_REQUIRED_QUANTITY > 0'
502: --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
503: --||' 2 ,x.coll_completed_qty_ind,'
582: ||' AND x.NEW_REQUIRED_QUANTITY > 0'
583: || v_union_sql ;
584:
585:
586: if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
587:
588: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
589: lv_new_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
590: lv_new_org_string := MSC_UTIL.v_in_org_str;
584:
585:
586: if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
587:
588: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
589: lv_new_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
590: lv_new_org_string := MSC_UTIL.v_in_org_str;
591:
592: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
588: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
589: lv_new_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
590: lv_new_org_string := MSC_UTIL.v_in_org_str;
591:
592: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
593: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
594: else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
595:
596:
593: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
594: else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
595:
596:
597: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
598: lv_new_view_name := 'MRP_AP_NON_ERO_WIP_DEMAND_V';
599: lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
600:
601: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
597: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
598: lv_new_view_name := 'MRP_AP_NON_ERO_WIP_DEMAND_V';
599: lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
600:
601: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
602: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
603: ---- Code for SRP when Repair Orders Entity is/not selected . Building v_sql_stmt
604: end if;
605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
601: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
602: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
603: ---- Code for SRP when Repair Orders Entity is/not selected . Building v_sql_stmt
604: end if;
605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
606:
607:
608: EXECUTE IMMEDIATE v_temp_sql_stmt
609: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
606:
607:
608: EXECUTE IMMEDIATE v_temp_sql_stmt
609: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
610: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
611: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
612:
613: /* NCP:
606:
607:
608: EXECUTE IMMEDIATE v_temp_sql_stmt
609: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
610: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
611: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
612:
613: /* NCP:
614: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
607:
608: EXECUTE IMMEDIATE v_temp_sql_stmt
609: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
610: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
611: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
612:
613: /* NCP:
614: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
615: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
610: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
611: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
612:
613: /* NCP:
614: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
615: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
616: */
617:
618: ELSE
611: 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
612:
613: /* NCP:
614: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
615: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
616: */
617:
618: ELSE
619:
616: */
617:
618: ELSE
619:
620: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
621: Begin
622:
623: EXECUTE IMMEDIATE v_temp_sql_stmt
624: 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_COLLECT_COMPLETED_JOBS;
620: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
621: Begin
622:
623: EXECUTE IMMEDIATE v_temp_sql_stmt
624: 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_COLLECT_COMPLETED_JOBS;
625:
626:
627: v_temp_sql_stmt := NULL;
628: MSC_CL_PULL.v_table_name := 'MSC_ST_DEMANDS';
624: 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_COLLECT_COMPLETED_JOBS;
625:
626:
627: v_temp_sql_stmt := NULL;
628: MSC_CL_PULL.v_table_name := 'MSC_ST_DEMANDS';
629: lv_new_view_name := 'MRP_AP_NON_RO_WIP_DEMAND_V';
630: lv_new_org_string := MSC_UTIL.v_depot_org_str;
631:
632: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
628: MSC_CL_PULL.v_table_name := 'MSC_ST_DEMANDS';
629: lv_new_view_name := 'MRP_AP_NON_RO_WIP_DEMAND_V';
630: lv_new_org_string := MSC_UTIL.v_depot_org_str;
631:
632: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
633: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
634:
635:
636: EXECUTE IMMEDIATE v_temp_sql_stmt
633: ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
634:
635:
636: EXECUTE IMMEDIATE v_temp_sql_stmt
637: 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_COLLECT_COMPLETED_JOBS;
638:
639:
640: end; /* Code for SRP To get the work orders not attached to repair orders: bug 5909379 */
641: ELSE
639:
640: end; /* Code for SRP To get the work orders not attached to repair orders: bug 5909379 */
641: ELSE
642: EXECUTE IMMEDIATE v_temp_sql_stmt
643: 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_COLLECT_COMPLETED_JOBS;
644:
645: end if; -- For SRP and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120
646: END IF;
647:
641: ELSE
642: EXECUTE IMMEDIATE v_temp_sql_stmt
643: 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_COLLECT_COMPLETED_JOBS;
644:
645: end if; -- For SRP and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120
646: END IF;
647:
648: COMMIT;
649:
650:
651: --=================== REPT ITEM DEMAND ==================
652: --=================== Net Change Mode: Delete ==================
653:
654: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_DEMANDS_V';
658:
652: --=================== Net Change Mode: Delete ==================
653:
654: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_DEMANDS_V';
658:
659: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
660: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
653:
654: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_DEMANDS_V';
658:
659: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
660: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
661: ELSE
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_DEMANDS_V';
658:
659: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
660: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
661: ELSE
662: v_temp_sql := NULL;
663: END IF;
682: ||' x.ORIGINATION_TYPE,'
683: ||' 1,'
684: ||' :v_refresh_id,'
685: ||' :v_instance_id'
686: ||' from MRP_AD_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
687: ||' WHERE x.RN> :v_lrn '
688: || v_temp_sql;
689:
690: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
686: ||' from MRP_AD_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
687: ||' WHERE x.RN> :v_lrn '
688: || v_temp_sql;
689:
690: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
691:
692: COMMIT;
693:
694: END IF;
693:
694: END IF;
695:
696:
697: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
698: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_DEMANDS_V';
699:
700: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
701:
694: END IF;
695:
696:
697: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
698: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_DEMANDS_V';
699:
700: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
701:
702: v_union_sql :=
696:
697: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
698: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_DEMANDS_V';
699:
700: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
701:
702: v_union_sql :=
703: ' AND ( x.RN1 > :v_lrn )'
704: ||' UNION '
720: ||' x.WIP_SUPPLY_TYPE,'
721: ||' 2,'
722: ||' :v_refresh_id,'
723: ||' :v_instance_id'
724: ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
725: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
726: ||' AND ( x.RN2 > :v_lrn )'
727: ||' UNION '
728: ||' select'
743: ||' x.WIP_SUPPLY_TYPE,'
744: ||' 2,'
745: ||' :v_refresh_id,'
746: ||' :v_instance_id'
747: ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
748: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
749: ||' AND ( x.RN3 > :v_lrn )'
750: ||' UNION '
751: ||' select'
766: ||' x.WIP_SUPPLY_TYPE,'
767: ||' 2,'
768: ||' :v_refresh_id,'
769: ||' :v_instance_id'
770: ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
771: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
772: ||' AND ( x.RN4 > :v_lrn )'
773: ||' UNION '
774: ||' select'
789: ||' x.WIP_SUPPLY_TYPE,'
790: ||' 2,'
791: ||' :v_refresh_id,'
792: ||' :v_instance_id'
793: ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
794: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
795: ||' AND ( x.RN5 > :v_lrn )';
796:
797: ELSE
840: ||' x.WIP_SUPPLY_TYPE,'
841: ||' 2,'
842: ||' :v_refresh_id,'
843: ||' :v_instance_id'
844: ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
845: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
846: || v_union_sql;
847:
848:
846: || v_union_sql;
847:
848:
849:
850: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
851:
852: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
849:
850: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
851:
852: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
855: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
850: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
851:
852: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
855: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
858:
851:
852: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
855: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
858:
859: ELSE
852: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
855: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
858:
859: ELSE
860: EXECUTE IMMEDIATE v_sql_stmt
853: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
854: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
855: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
858:
859: ELSE
860: EXECUTE IMMEDIATE v_sql_stmt
861: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
857: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
858:
859: ELSE
860: EXECUTE IMMEDIATE v_sql_stmt
861: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
862: END IF;
863:
864: COMMIT;
865:
862: END IF;
863:
864: COMMIT;
865:
866: END IF; -- MSC_CL_PULL.WIP_ENABLED
867:
868: END LOAD_WIP_DEMAND;
869:
870:
883: v_temp_sql_stmt2 VARCHAR2(10240) :=null;
884:
885: BEGIN
886:
887: IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
888:
889: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
890:
891: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
885: BEGIN
886:
887: IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
888:
889: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
890:
891: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
892: where instance_id = MSC_CL_PULL.v_instance_id ;
893: END IF;
888:
889: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
890:
891: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
892: where instance_id = MSC_CL_PULL.v_instance_id ;
893: END IF;
894:
895: IF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
896: lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
891: select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
892: where instance_id = MSC_CL_PULL.v_instance_id ;
893: END IF;
894:
895: IF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
896: lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
897: ||' 1, DECODE( :v_mps_consume_profile_value, '
898: ||' 1, x.mps_net_quantity,'
899: ||' x.net_quantity), '
898: ||' 1, x.mps_net_quantity,'
899: ||' x.net_quantity), '
900: ||' x.net_quantity) >= 0' ;
901: ELSE
902: IF MSC_CL_PULL.v_lrnn<> -1 THEN
903: lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
904: || ' 1, DECODE( :v_mps_consume_profile_value, '
905: ||' 1, x.mps_net_quantity,'
906: ||' x.net_quantity), '
916: END IF;
917:
918: --=================== Net Change Mode: Delete ==================
919:
920: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
921:
922: -- =================== JOB/FLOW SCHEDULE =====================
923: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
924: -- Changed for Bug 6081537
919:
920: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
921:
922: -- =================== JOB/FLOW SCHEDULE =====================
923: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
924: -- Changed for Bug 6081537
925: -- Supplies from Non Depot orgs
926:
927: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
923: IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
924: -- Changed for Bug 6081537
925: -- Supplies from Non Depot orgs
926:
927: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
928: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_JOB_SUP_V';
929:
930: v_sql_stmt:=
931: 'insert into MSC_ST_SUPPLIES'
924: -- Changed for Bug 6081537
925: -- Supplies from Non Depot orgs
926:
927: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
928: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_JOB_SUP_V';
929:
930: v_sql_stmt:=
931: 'insert into MSC_ST_SUPPLIES'
932: ||' ( DISPOSITION_ID,'
939: ||' x.ORDER_TYPE,'
940: ||' 1,'
941: ||' :v_refresh_id,'
942: ||' :v_instance_id'
943: ||' from MRP_AD_NON_ERO_WIP_JOB_SUP_V'||MSC_CL_PULL.v_dblink||' x'
944: ||' where DECODE( x.wip_job_type,'
945: ||' 1, DECODE( :v_mps_consume_profile_value,'
946: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
947: ||' x.WJS_NET_QTY_FLAG),'
949: ||' AND x.RN> :v_lrn '
950: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str;
951:
952: -- Supplies from Depot repair orgs
953: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
954: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_JOB_SUPP_V';
955:
956: v_temp_sql2:=
957: 'insert into MSC_ST_SUPPLIES'
950: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str;
951:
952: -- Supplies from Depot repair orgs
953: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
954: MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_JOB_SUPP_V';
955:
956: v_temp_sql2:=
957: 'insert into MSC_ST_SUPPLIES'
958: ||' ( DISPOSITION_ID,'
965: ||' x.ORDER_TYPE,'
966: ||' 1,'
967: ||' :v_refresh_id,'
968: ||' :v_instance_id'
969: ||' from MRP_AD_NON_ERO_WIP_JOB_SUP_V'||MSC_CL_PULL.v_dblink||' x'
970: ||' where DECODE( x.wip_job_type,'
971: ||' 1, DECODE( :v_mps_consume_profile_value,'
972: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
973: ||' x.WJS_NET_QTY_FLAG),'
975: ||' AND x.RN> :v_lrn '
976: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str;
977:
978:
979: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
980: MSC_CL_PULL.v_instance_id,
981: MSC_CL_PULL.v_mps_consume_profile_value,
982: MSC_CL_PULL.v_lrn;
983:
976: ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str;
977:
978:
979: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
980: MSC_CL_PULL.v_instance_id,
981: MSC_CL_PULL.v_mps_consume_profile_value,
982: MSC_CL_PULL.v_lrn;
983:
984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
977:
978:
979: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
980: MSC_CL_PULL.v_instance_id,
981: MSC_CL_PULL.v_mps_consume_profile_value,
982: MSC_CL_PULL.v_lrn;
983:
984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
985: COMMIT;
978:
979: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
980: MSC_CL_PULL.v_instance_id,
981: MSC_CL_PULL.v_mps_consume_profile_value,
982: MSC_CL_PULL.v_lrn;
983:
984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
985: COMMIT;
986:
983:
984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
985: COMMIT;
986:
987: EXECUTE IMMEDIATE v_temp_sql2 USING MSC_CL_PULL.v_refresh_id,
988: MSC_CL_PULL.v_instance_id,
989: MSC_CL_PULL.v_mps_consume_profile_value,
990: MSC_CL_PULL.v_lrn;
991:
984: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
985: COMMIT;
986:
987: EXECUTE IMMEDIATE v_temp_sql2 USING MSC_CL_PULL.v_refresh_id,
988: MSC_CL_PULL.v_instance_id,
989: MSC_CL_PULL.v_mps_consume_profile_value,
990: MSC_CL_PULL.v_lrn;
991:
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
985: COMMIT;
986:
987: EXECUTE IMMEDIATE v_temp_sql2 USING MSC_CL_PULL.v_refresh_id,
988: MSC_CL_PULL.v_instance_id,
989: MSC_CL_PULL.v_mps_consume_profile_value,
990: MSC_CL_PULL.v_lrn;
991:
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
993: COMMIT;
986:
987: EXECUTE IMMEDIATE v_temp_sql2 USING MSC_CL_PULL.v_refresh_id,
988: MSC_CL_PULL.v_instance_id,
989: MSC_CL_PULL.v_mps_consume_profile_value,
990: MSC_CL_PULL.v_lrn;
991:
992: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
993: COMMIT;
994:
993: COMMIT;
994:
995: ELSE -- (SRP Profile is No)
996:
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
998: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_JOB_SUPPLIES_V';
999:
1000: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1001: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
994:
995: ELSE -- (SRP Profile is No)
996:
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
998: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_JOB_SUPPLIES_V';
999:
1000: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1001: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1002: ELSE
996:
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
998: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_JOB_SUPPLIES_V';
999:
1000: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1001: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1002: ELSE
1003: v_temp_sql := NULL;
1004: END IF;
1015: ||' x.ORDER_TYPE,'
1016: ||' 1,'
1017: ||' :v_refresh_id,'
1018: ||' :v_instance_id'
1019: ||' from MRP_AD_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1020: ||' where DECODE( x.wip_job_type,'
1021: ||' 1, DECODE( :v_mps_consume_profile_value,'
1022: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1023: ||' x.WJS_NET_QTY_FLAG),'
1024: ||' x.WJS_NET_QTY_FLAG)=1'
1025: ||' AND x.RN> :v_lrn '
1026: || v_temp_sql;
1027:
1028: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1029: MSC_CL_PULL.v_instance_id,
1030: MSC_CL_PULL.v_mps_consume_profile_value,
1031: MSC_CL_PULL.v_lrn;
1032:
1025: ||' AND x.RN> :v_lrn '
1026: || v_temp_sql;
1027:
1028: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1029: MSC_CL_PULL.v_instance_id,
1030: MSC_CL_PULL.v_mps_consume_profile_value,
1031: MSC_CL_PULL.v_lrn;
1032:
1033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1026: || v_temp_sql;
1027:
1028: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1029: MSC_CL_PULL.v_instance_id,
1030: MSC_CL_PULL.v_mps_consume_profile_value,
1031: MSC_CL_PULL.v_lrn;
1032:
1033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1034: COMMIT;
1027:
1028: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1029: MSC_CL_PULL.v_instance_id,
1030: MSC_CL_PULL.v_mps_consume_profile_value,
1031: MSC_CL_PULL.v_lrn;
1032:
1033: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1034: COMMIT;
1035: END IF;
1034: COMMIT;
1035: END IF;
1036: -- =================== JOB COMPONENTS =====================
1037:
1038: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1039: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_SUPPLIES_V';
1040:
1041: lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1042:
1035: END IF;
1036: -- =================== JOB COMPONENTS =====================
1037:
1038: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1039: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_SUPPLIES_V';
1040:
1041: lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1042:
1043: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1039: MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_SUPPLIES_V';
1040:
1041: lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1042:
1043: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1044: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1045:
1046: if lv_lbj_details = 1 Then
1047: lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1067: ||' x.ORDER_TYPE,'
1068: ||' 1,'
1069: ||' :v_refresh_id,'
1070: ||' :v_instance_id'
1071: ||' from MRP_AD_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1072: ||' where ( DECODE( :v_mps_consume_profile_value,'
1073: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1074: ||' x.WJS_NET_QTY_FLAG)=1'
1075: ||' OR x.MRP_NET_FLAG= 1'
1078: ||' x.NMPS_FLAG)= 1 )'
1079: ||' AND x.RN> :v_lrn '
1080: || v_temp_sql;
1081:
1082: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1083: MSC_CL_PULL.v_instance_id,
1084: MSC_CL_PULL.v_mps_consume_profile_value,
1085: MSC_CL_PULL.v_mps_consume_profile_value,
1086: MSC_CL_PULL.v_lrn;
1079: ||' AND x.RN> :v_lrn '
1080: || v_temp_sql;
1081:
1082: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1083: MSC_CL_PULL.v_instance_id,
1084: MSC_CL_PULL.v_mps_consume_profile_value,
1085: MSC_CL_PULL.v_mps_consume_profile_value,
1086: MSC_CL_PULL.v_lrn;
1087:
1080: || v_temp_sql;
1081:
1082: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1083: MSC_CL_PULL.v_instance_id,
1084: MSC_CL_PULL.v_mps_consume_profile_value,
1085: MSC_CL_PULL.v_mps_consume_profile_value,
1086: MSC_CL_PULL.v_lrn;
1087:
1088: COMMIT;
1081:
1082: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1083: MSC_CL_PULL.v_instance_id,
1084: MSC_CL_PULL.v_mps_consume_profile_value,
1085: MSC_CL_PULL.v_mps_consume_profile_value,
1086: MSC_CL_PULL.v_lrn;
1087:
1088: COMMIT;
1089:
1082: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1083: MSC_CL_PULL.v_instance_id,
1084: MSC_CL_PULL.v_mps_consume_profile_value,
1085: MSC_CL_PULL.v_mps_consume_profile_value,
1086: MSC_CL_PULL.v_lrn;
1087:
1088: COMMIT;
1089:
1090: -- =================== REPT ITEM =====================
1088: COMMIT;
1089:
1090: -- =================== REPT ITEM =====================
1091:
1092: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1093: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_SUPPLIES_V';
1094:
1095: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1096: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1089:
1090: -- =================== REPT ITEM =====================
1091:
1092: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1093: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_SUPPLIES_V';
1094:
1095: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1096: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1097: ELSE
1091:
1092: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1093: MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_SUPPLIES_V';
1094:
1095: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1096: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1097: ELSE
1098: v_temp_sql := NULL;
1099: END IF;
1112: ||' x.ORGANIZATION_ID,'
1113: ||' 1,'
1114: ||' :v_refresh_id,'
1115: ||' :v_instance_id'
1116: ||' from MRP_AD_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1117: ||' WHERE x.RN> :v_lrn '
1118: || v_temp_sql;
1119:
1120: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1116: ||' from MRP_AD_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1117: ||' WHERE x.RN> :v_lrn '
1118: || v_temp_sql;
1119:
1120: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1121:
1122: COMMIT;
1123:
1124: END IF;
1122: COMMIT;
1123:
1124: END IF;
1125:
1126: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1127: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1128:
1129:
1130: Begin
1123:
1124: END IF;
1125:
1126: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1127: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1128:
1129:
1130: Begin
1131: if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1127: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1128:
1129:
1130: Begin
1131: if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1132: if lv_lbj_details = 1 Then
1133: v_temp_sql := ' x.routing_reference_id,x.bom_reference_id,x.coproducts_supply,x.jd_operation_seq_num,'||
1134: 'x.JUMP_OP_SEQ_NUM,x.JOB_OP_SEQ_NUM, '||
1135: 'x.requested_start_date,x.requested_completion_date,x.schedule_priority,x.asset_item_id,x.asset_serial_number,' ||/* ds change */
1147:
1148: Begin
1149:
1150: --Bug#3419189
1151: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1152:
1153: v_temp_sql1 := ' x.wip_start_quantity,';
1154:
1155: ELSE
1155: ELSE
1156: v_temp_sql1 := ' NULL,';
1157: END IF;
1158:
1159: /*Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS115,
1160: ' DECODE( x.wip_job_type, 1,
1161: DECODE( :v_mps_consume_profile_value,
1162: 1, x.mps_wip_start_quantity,
1163: x.wip_start_quantity),
1167: from dual;*/
1168: End;
1169:
1170:
1171: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1172:
1173: v_union_sql :=
1174: ' AND ( x.RN1 > :v_lrn )'
1175: ||' UNION '
1215: || v_temp_sql
1216: || v_temp_sql1
1217: ||' :v_refresh_id,'
1218: ||' :v_instance_id'
1219: ||' from MRP_AP_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1220: ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1221: /*Bug#4704457 ||' AND DECODE( x.wip_job_type,'
1222: ||' 1, DECODE( :v_mps_consume_profile_value,'
1223: ||' 1, x.mps_net_quantity,'
1328:
1329: v_temp_sql2:= v_sql_stmt; -- Added For Bug 5935273
1330: v_temp_sql := lv_qty_sql_temp|| v_union_sql;
1331:
1332: if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
1333:
1334: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1335: lv_new_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1336: lv_new_org_string := MSC_UTIL.v_in_org_str;
1330: v_temp_sql := lv_qty_sql_temp|| v_union_sql;
1331:
1332: if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
1333:
1334: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1335: lv_new_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1336: lv_new_org_string := MSC_UTIL.v_in_org_str;
1337:
1338: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'||
1334: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1335: lv_new_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1336: lv_new_org_string := MSC_UTIL.v_in_org_str;
1337:
1338: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'||
1339: ' where x.ORGANIZATION_ID '||lv_new_org_string||' '||v_temp_sql ;
1340: /* Changes For Bug 5909379 SRP Enhancements */
1341:
1342: else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
1341:
1342: else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
1343: -- Repair Order Entities are No
1344:
1345: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1346: lv_new_view_name := 'MRP_AP_NON_ERO_WIP_JOB_SUPP_V';
1347: lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
1348:
1349: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_JOB_SUPP_V'||MSC_CL_PULL.v_dblink||' x'||
1345: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1346: lv_new_view_name := 'MRP_AP_NON_ERO_WIP_JOB_SUPP_V';
1347: lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
1348:
1349: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_JOB_SUPP_V'||MSC_CL_PULL.v_dblink||' x'||
1350: ' where x.ORGANIZATION_ID '||lv_new_org_string||' '||v_temp_sql ;
1351:
1352:
1353: ---- Code for SRP when Repair Orders Entity is/not selected . Building v_sql_stmt
1355:
1356: end if; /* End Profile MSC_UTIL.G_COLLECT_SRP_DATA 5909379 */
1357:
1358:
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1357:
1358:
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1358:
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1359: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1360: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1360: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1361: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1362: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1363: MSC_CL_PULL.v_mps_consume_profile_value,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1364: MSC_CL_PULL.v_dgmt,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1365: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1373: MSC_CL_PULL.v_lrn;
1366: MSC_CL_PULL.v_mps_consume_profile_value,
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1373: MSC_CL_PULL.v_lrn;
1374:
1367: MSC_CL_PULL.v_lrn,
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1373: MSC_CL_PULL.v_lrn;
1374:
1375: ELSE -- For COmplete Refresh
1368: MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1373: MSC_CL_PULL.v_lrn;
1374:
1375: ELSE -- For COmplete Refresh
1376:
1369: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1370: MSC_CL_PULL.v_mps_consume_profile_value,
1371: MSC_CL_PULL.v_dgmt,
1372: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_mps_consume_profile_value,
1373: MSC_CL_PULL.v_lrn;
1374:
1375: ELSE -- For COmplete Refresh
1376:
1377: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply complete = '||v_sql_stmt);
1376:
1377: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply complete = '||v_sql_stmt);
1378:
1379:
1380: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
1381:
1382:
1383:
1384:
1381:
1382:
1383:
1384:
1385: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1382:
1383:
1384:
1385: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1390: MSC_CL_PULL.v_mps_consume_profile_value;
1383:
1384:
1385: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1390: MSC_CL_PULL.v_mps_consume_profile_value;
1391:
1384:
1385: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1390: MSC_CL_PULL.v_mps_consume_profile_value;
1391:
1392: v_temp_sql_stmt := NULL;
1385: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1390: MSC_CL_PULL.v_mps_consume_profile_value;
1391:
1392: v_temp_sql_stmt := NULL;
1393:
1386: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1387: MSC_CL_PULL.v_mps_consume_profile_value,
1388: MSC_CL_PULL.v_dgmt,
1389: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1390: MSC_CL_PULL.v_mps_consume_profile_value;
1391:
1392: v_temp_sql_stmt := NULL;
1393:
1394: begin
1391:
1392: v_temp_sql_stmt := NULL;
1393:
1394: begin
1395: MSC_CL_PULL.v_table_name := 'MSC_ST_SUPPLIES';
1396: lv_new_view_name := 'MRP_AP_NON_RO_WIP_JOB_SUPPLY_V';
1397: lv_new_org_string := MSC_UTIL.v_depot_org_str;
1398:
1399: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_JOB_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'||
1395: MSC_CL_PULL.v_table_name := 'MSC_ST_SUPPLIES';
1396: lv_new_view_name := 'MRP_AP_NON_RO_WIP_JOB_SUPPLY_V';
1397: lv_new_org_string := MSC_UTIL.v_depot_org_str;
1398:
1399: v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_JOB_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'||
1400: ' where x.ORGANIZATION_ID '||lv_new_org_string ||' '||v_temp_sql;
1401:
1402:
1403:
1400: ' where x.ORGANIZATION_ID '||lv_new_org_string ||' '||v_temp_sql;
1401:
1402:
1403:
1404: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1401:
1402:
1403:
1404: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1409: MSC_CL_PULL.v_mps_consume_profile_value;
1402:
1403:
1404: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1409: MSC_CL_PULL.v_mps_consume_profile_value;
1410: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_NON_RO_WIP_JOB_SUPPLY_V = '|| SQL%ROWCOUNT);
1403:
1404: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1409: MSC_CL_PULL.v_mps_consume_profile_value;
1410: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_NON_RO_WIP_JOB_SUPPLY_V = '|| SQL%ROWCOUNT);
1411:
1404: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1409: MSC_CL_PULL.v_mps_consume_profile_value;
1410: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_NON_RO_WIP_JOB_SUPPLY_V = '|| SQL%ROWCOUNT);
1411:
1412:
1405: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1406: MSC_CL_PULL.v_mps_consume_profile_value,
1407: MSC_CL_PULL.v_dgmt,
1408: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1409: MSC_CL_PULL.v_mps_consume_profile_value;
1410: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_NON_RO_WIP_JOB_SUPPLY_V = '|| SQL%ROWCOUNT);
1411:
1412:
1413: end ; ---- Code for SRP To get the work orders not attached to repair orders:
1418:
1419: ELSE -- Instance is < 12i or MSC_SRP_ENABLED Profile is 'No'
1420:
1421:
1422: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1419: ELSE -- Instance is < 12i or MSC_SRP_ENABLED Profile is 'No'
1420:
1421:
1422: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1427: MSC_CL_PULL.v_mps_consume_profile_value;
1420:
1421:
1422: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1427: MSC_CL_PULL.v_mps_consume_profile_value;
1428: END IF;
1421:
1422: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1427: MSC_CL_PULL.v_mps_consume_profile_value;
1428: END IF;
1429: END IF;
1422: EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1427: MSC_CL_PULL.v_mps_consume_profile_value;
1428: END IF;
1429: END IF;
1430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1423: MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1424: MSC_CL_PULL.v_mps_consume_profile_value,
1425: MSC_CL_PULL.v_dgmt,
1426: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1427: MSC_CL_PULL.v_mps_consume_profile_value;
1428: END IF;
1429: END IF;
1430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1431: COMMIT;
1429: END IF;
1430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1431: COMMIT;
1432:
1433: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1434: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_SUPPLIES_V';
1435:
1436: lv_op_seq_num := ' x.OPERATION_SEQ_NUM, ';
1437:
1430: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1431: COMMIT;
1432:
1433: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1434: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_SUPPLIES_V';
1435:
1436: lv_op_seq_num := ' x.OPERATION_SEQ_NUM, ';
1437:
1438: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1434: MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_SUPPLIES_V';
1435:
1436: lv_op_seq_num := ' x.OPERATION_SEQ_NUM, ';
1437:
1438: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1439: v_temp_sql := ' x.quantity_per_assembly,x.quantity_issued, x.ACTUAL_START_DATE, ';
1440:
1441: if lv_lbj_details = 1 Then
1442: lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1448: v_temp_sql := ' NULL, NULL, NULL, ';
1449: END IF;
1450:
1451:
1452: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1453:
1454: v_union_sql :=
1455: ' AND ( x.RN1 > :v_lrn )'
1456: ||' UNION '
1479: || v_temp_sql
1480: ||' 2,'
1481: ||' :v_refresh_id,'
1482: ||' :v_instance_id'
1483: ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1484: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1485: ||' AND x.BY_PROD_QUANTITY <> 0'
1486: || lv_cond_sql
1487: --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1517: || v_temp_sql
1518: ||' 2,'
1519: ||' :v_refresh_id,'
1520: ||' :v_instance_id'
1521: ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1522: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1523: ||' AND x.BY_PROD_QUANTITY <> 0'
1524: || lv_cond_sql
1525: --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1589: || v_temp_sql
1590: ||' 2,'
1591: ||' :v_refresh_id,'
1592: ||' :v_instance_id'
1593: ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1594: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1595: ||' AND x.BY_PROD_QUANTITY <> 0'
1596: || lv_cond_sql
1597: --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1601: ||' 2,x.status_code,'
1602: ||' 1) <>4' -- 5730031
1603: || v_union_sql;
1604:
1605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt
1608: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1609: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1604:
1605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt
1608: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1609: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1610: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
1611:
1612: ELSE
1605: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt
1608: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1609: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1610: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
1611:
1612: ELSE
1613:
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt
1608: USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1609: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1610: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
1611:
1612: ELSE
1613:
1614: EXECUTE IMMEDIATE v_sql_stmt
1611:
1612: ELSE
1613:
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, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS;
1616:
1617: END IF;
1618:
1619:
1625: BEGIN
1626:
1627: --If lv_lbj_details = 1 Then
1628:
1629: IF MSC_CL_PULL.v_lrn <> -1 then /*incremental refresh*/
1630:
1631: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1632: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1633: ELSE
1627: --If lv_lbj_details = 1 Then
1628:
1629: IF MSC_CL_PULL.v_lrn <> -1 then /*incremental refresh*/
1630:
1631: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1632: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1633: ELSE
1634: v_temp_sql := NULL;
1635: END IF;
1633: ELSE
1634: v_temp_sql := NULL;
1635: END IF;
1636:
1637: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1638: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_NETWORKS_V';
1639:
1640: v_sql_stmt:=
1641: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1634: v_temp_sql := NULL;
1635: END IF;
1636:
1637: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1638: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_NETWORKS_V';
1639:
1640: v_sql_stmt:=
1641: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1642: ||' ( WIP_ENTITY_ID,'
1651: ||' x.TO_OP_SEQ_NUM,'
1652: ||' 1,'
1653: ||' :v_refresh_id,'
1654: ||' :v_instance_id'
1655: ||' from MRP_AD_JOB_OP_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1656: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1657: ||' AND DECODE( x.from_op_seq_num,'
1658: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1659: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1652: ||' 1,'
1653: ||' :v_refresh_id,'
1654: ||' :v_instance_id'
1655: ||' from MRP_AD_JOB_OP_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1656: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1657: ||' AND DECODE( x.from_op_seq_num,'
1658: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1659: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1660: ||' x.WJS_NET_QTY_FLAG), '
1660: ||' x.WJS_NET_QTY_FLAG), '
1661: ||' 1)= 1'
1662: || v_temp_sql;
1663:
1664: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1665: MSC_CL_PULL.v_instance_id,
1666: MSC_CL_PULL.v_mps_consume_profile_value;
1667:
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1661: ||' 1)= 1'
1662: || v_temp_sql;
1663:
1664: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1665: MSC_CL_PULL.v_instance_id,
1666: MSC_CL_PULL.v_mps_consume_profile_value;
1667:
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1669: COMMIT;
1662: || v_temp_sql;
1663:
1664: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1665: MSC_CL_PULL.v_instance_id,
1666: MSC_CL_PULL.v_mps_consume_profile_value;
1667:
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1669: COMMIT;
1670:
1668: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1669: COMMIT;
1670:
1671:
1672: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
1673: /* ds change start */
1674: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1675: MSC_CL_PULL.v_view_name := 'MRP_AD_WOPR_NETWORKS_V';
1676:
1670:
1671:
1672: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
1673: /* ds change start */
1674: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1675: MSC_CL_PULL.v_view_name := 'MRP_AD_WOPR_NETWORKS_V';
1676:
1677: v_sql_stmt:=
1678: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1671:
1672: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
1673: /* ds change start */
1674: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1675: MSC_CL_PULL.v_view_name := 'MRP_AD_WOPR_NETWORKS_V';
1676:
1677: v_sql_stmt:=
1678: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1679: ||' ( WIP_ENTITY_ID,'
1688: ||' x.TO_OP_SEQ_NUM,'
1689: ||' 1,'
1690: ||' :v_refresh_id,'
1691: ||' :v_instance_id'
1692: ||' from MRP_AD_WOPR_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1693: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1694:
1695: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1689: ||' 1,'
1690: ||' :v_refresh_id,'
1691: ||' :v_instance_id'
1692: ||' from MRP_AD_WOPR_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1693: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1694:
1695: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1697: MSC_CL_PULL.v_instance_id;
1692: ||' from MRP_AD_WOPR_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1693: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1694:
1695: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1697: MSC_CL_PULL.v_instance_id;
1698:
1699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_WOPR_NETWORKS = '|| SQL%ROWCOUNT);
1700: COMMIT;
1693: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1694:
1695: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1697: MSC_CL_PULL.v_instance_id;
1698:
1699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_WOPR_NETWORKS = '|| SQL%ROWCOUNT);
1700: COMMIT;
1701:
1698:
1699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_WOPR_NETWORKS = '|| SQL%ROWCOUNT);
1700: COMMIT;
1701:
1702: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1703: MSC_CL_PULL.v_view_name := 'MRP_AD_EAM_WO_RELSHIPS_V';
1704:
1705: v_sql_stmt:=
1706: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_WOPR_NETWORKS = '|| SQL%ROWCOUNT);
1700: COMMIT;
1701:
1702: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1703: MSC_CL_PULL.v_view_name := 'MRP_AD_EAM_WO_RELSHIPS_V';
1704:
1705: v_sql_stmt:=
1706: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1707: ||' ( WIP_ENTITY_ID,'
1716: --||' x.RELATIONSHIP_TYPE,'
1717: ||' 1,'
1718: ||' :v_refresh_id,'
1719: ||' :v_instance_id'
1720: ||' from MRP_AD_EAM_WO_RELSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1721: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1722:
1723: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1724: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1717: ||' 1,'
1718: ||' :v_refresh_id,'
1719: ||' :v_instance_id'
1720: ||' from MRP_AD_EAM_WO_RELSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1721: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1722:
1723: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1724: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1725: MSC_CL_PULL.v_instance_id;
1720: ||' from MRP_AD_EAM_WO_RELSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1721: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1722:
1723: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1724: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1725: MSC_CL_PULL.v_instance_id;
1726:
1727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_EAM_WO_RELSHIPS_V = '|| SQL%ROWCOUNT);
1728: -- opm populate job operation network in call
1721: ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1722:
1723: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1724: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1725: MSC_CL_PULL.v_instance_id;
1726:
1727: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_EAM_WO_RELSHIPS_V = '|| SQL%ROWCOUNT);
1728: -- opm populate job operation network in call
1729: -- to
1732: /* ds change end */
1733: END IF; --v_apps_ver >= MSC_UTIL.G_APPS120
1734:
1735:
1736: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATIONS';
1737: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OPERATIONS_V';
1738:
1739: v_sql_stmt:=
1740: 'insert into MSC_ST_JOB_OPERATIONS'
1733: END IF; --v_apps_ver >= MSC_UTIL.G_APPS120
1734:
1735:
1736: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATIONS';
1737: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OPERATIONS_V';
1738:
1739: v_sql_stmt:=
1740: 'insert into MSC_ST_JOB_OPERATIONS'
1741: ||' ( WIP_ENTITY_ID,'
1748: ||' x.OPERATION_SEQ_NUM,'
1749: ||' 1,'
1750: ||' :v_refresh_id,'
1751: ||' :v_instance_id'
1752: ||' from MRP_AD_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1753: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1754: ||' AND DECODE( x.operation_seq_num,'
1755: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1756: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1749: ||' 1,'
1750: ||' :v_refresh_id,'
1751: ||' :v_instance_id'
1752: ||' from MRP_AD_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1753: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1754: ||' AND DECODE( x.operation_seq_num,'
1755: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1756: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1757: ||' x.WJS_NET_QTY_FLAG), '
1757: ||' x.WJS_NET_QTY_FLAG), '
1758: ||' 1)= 1'
1759: || v_temp_sql;
1760:
1761: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1762: MSC_CL_PULL.v_instance_id,
1763: MSC_CL_PULL.v_mps_consume_profile_value;
1764:
1765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1758: ||' 1)= 1'
1759: || v_temp_sql;
1760:
1761: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1762: MSC_CL_PULL.v_instance_id,
1763: MSC_CL_PULL.v_mps_consume_profile_value;
1764:
1765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1766: COMMIT;
1759: || v_temp_sql;
1760:
1761: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1762: MSC_CL_PULL.v_instance_id,
1763: MSC_CL_PULL.v_mps_consume_profile_value;
1764:
1765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1766: COMMIT;
1767:
1765: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1766: COMMIT;
1767:
1768:
1769: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_REQUIREMENT_OPS';
1770: MSC_CL_PULL.v_view_name := 'MRP_AD_REQUIREMENT_OPS_V';
1771:
1772: v_sql_stmt:=
1773: 'insert into MSC_ST_JOB_REQUIREMENT_OPS'
1766: COMMIT;
1767:
1768:
1769: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_REQUIREMENT_OPS';
1770: MSC_CL_PULL.v_view_name := 'MRP_AD_REQUIREMENT_OPS_V';
1771:
1772: v_sql_stmt:=
1773: 'insert into MSC_ST_JOB_REQUIREMENT_OPS'
1774: ||' ( WIP_ENTITY_ID,'
1787: ||' x.SOURCE_PHANTOM_ID,'
1788: ||' 1,'
1789: ||' :v_refresh_id,'
1790: ||' :v_instance_id'
1791: ||' from MRP_AD_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
1792: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1793: ||' AND DECODE( x.operation_seq_num,'
1794: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1795: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1788: ||' 1,'
1789: ||' :v_refresh_id,'
1790: ||' :v_instance_id'
1791: ||' from MRP_AD_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
1792: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1793: ||' AND DECODE( x.operation_seq_num,'
1794: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1795: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1796: ||' x.WJS_NET_QTY_FLAG), '
1796: ||' x.WJS_NET_QTY_FLAG), '
1797: ||' 1)= 1'
1798: || v_temp_sql;
1799:
1800: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1801: MSC_CL_PULL.v_instance_id,
1802: MSC_CL_PULL.v_mps_consume_profile_value;
1803:
1804: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
1797: ||' 1)= 1'
1798: || v_temp_sql;
1799:
1800: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1801: MSC_CL_PULL.v_instance_id,
1802: MSC_CL_PULL.v_mps_consume_profile_value;
1803:
1804: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
1805: COMMIT;
1798: || v_temp_sql;
1799:
1800: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1801: MSC_CL_PULL.v_instance_id,
1802: MSC_CL_PULL.v_mps_consume_profile_value;
1803:
1804: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
1805: COMMIT;
1806:
1803:
1804: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
1805: COMMIT;
1806:
1807: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1808: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_RESOURCES_V';
1809:
1810: v_sql_stmt:=
1811: 'insert into MSC_ST_JOB_OP_RESOURCES'
1804: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
1805: COMMIT;
1806:
1807: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1808: MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_RESOURCES_V';
1809:
1810: v_sql_stmt:=
1811: 'insert into MSC_ST_JOB_OP_RESOURCES'
1812: ||' ( WIP_ENTITY_ID,'
1821: ||' x.RESOURCE_SEQ_NUM,'
1822: ||' 1,'
1823: ||' :v_refresh_id,'
1824: ||' :v_instance_id'
1825: ||' from MRP_AD_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1826: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1827: ||' AND DECODE( x.operation_seq_num,'
1828: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1829: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1822: ||' 1,'
1823: ||' :v_refresh_id,'
1824: ||' :v_instance_id'
1825: ||' from MRP_AD_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1826: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1827: ||' AND DECODE( x.operation_seq_num,'
1828: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1829: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1830: ||' x.WJS_NET_QTY_FLAG), '
1830: ||' x.WJS_NET_QTY_FLAG), '
1831: ||' 1)= 1'
1832: || v_temp_sql;
1833:
1834: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1835: MSC_CL_PULL.v_instance_id,
1836: MSC_CL_PULL.v_mps_consume_profile_value;
1837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1838: COMMIT;
1831: ||' 1)= 1'
1832: || v_temp_sql;
1833:
1834: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1835: MSC_CL_PULL.v_instance_id,
1836: MSC_CL_PULL.v_mps_consume_profile_value;
1837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1838: COMMIT;
1839:
1832: || v_temp_sql;
1833:
1834: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1835: MSC_CL_PULL.v_instance_id,
1836: MSC_CL_PULL.v_mps_consume_profile_value;
1837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1838: COMMIT;
1839:
1840: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1836: MSC_CL_PULL.v_mps_consume_profile_value;
1837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1838: COMMIT;
1839:
1840: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1841: MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_SUB_OP_RESOURCES_V';
1842:
1843: v_sql_stmt:=
1844: 'insert into MSC_ST_JOB_OP_RESOURCES'
1837: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1838: COMMIT;
1839:
1840: MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1841: MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_SUB_OP_RESOURCES_V';
1842:
1843: v_sql_stmt:=
1844: 'insert into MSC_ST_JOB_OP_RESOURCES'
1845: ||' ( WIP_ENTITY_ID,'
1854: ||' x.RESOURCE_SEQ_NUM,'
1855: ||' 1,'
1856: ||' :v_refresh_id,'
1857: ||' :v_instance_id'
1858: ||' from MRP_AD_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1859: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1860: ||' AND DECODE( x.operation_seq_num,'
1861: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1862: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1855: ||' 1,'
1856: ||' :v_refresh_id,'
1857: ||' :v_instance_id'
1858: ||' from MRP_AD_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1859: ||' where x.RN> '||MSC_CL_PULL.v_lrn
1860: ||' AND DECODE( x.operation_seq_num,'
1861: ||' NULL, DECODE( :v_mps_consume_profile_value,'
1862: ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1863: ||' x.WJS_NET_QTY_FLAG), '
1863: ||' x.WJS_NET_QTY_FLAG), '
1864: ||' 1)= 1'
1865: || v_temp_sql;
1866:
1867: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1868: MSC_CL_PULL.v_instance_id,
1869: MSC_CL_PULL.v_mps_consume_profile_value;
1870:
1871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1864: ||' 1)= 1'
1865: || v_temp_sql;
1866:
1867: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1868: MSC_CL_PULL.v_instance_id,
1869: MSC_CL_PULL.v_mps_consume_profile_value;
1870:
1871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1872: COMMIT;
1865: || v_temp_sql;
1866:
1867: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1868: MSC_CL_PULL.v_instance_id,
1869: MSC_CL_PULL.v_mps_consume_profile_value;
1870:
1871: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
1872: COMMIT;
1873:
1872: COMMIT;
1873:
1874: END IF; /*incremental refresh */
1875:
1876: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATION_NETWORKS';
1877: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_NETWORKS_V';
1878:
1879: v_sql_stmt:=
1880: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1873:
1874: END IF; /*incremental refresh */
1875:
1876: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATION_NETWORKS';
1877: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_NETWORKS_V';
1878:
1879: v_sql_stmt:=
1880: 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1881: ||' (WIP_ENTITY_ID,'
1910: ||' x.TO_WIP_ENTITY_ID,'
1911: ||' x.TOP_WIP_ENTITY_ID,' /*ds change end */
1912: ||' 2,'
1913: ||' :v_refresh_id'
1914: ||' FROM MRP_AP_JOB_OP_NETWORKS_V '||MSC_CL_PULL.v_dblink||' x '
1915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1916: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1917: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1918: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1912: ||' 2,'
1913: ||' :v_refresh_id'
1914: ||' FROM MRP_AP_JOB_OP_NETWORKS_V '||MSC_CL_PULL.v_dblink||' x '
1915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1916: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1917: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1918: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1919:
1920: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ap job network = '||v_sql_stmt);
1913: ||' :v_refresh_id'
1914: ||' FROM MRP_AP_JOB_OP_NETWORKS_V '||MSC_CL_PULL.v_dblink||' x '
1915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1916: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1917: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1918: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1919:
1920: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ap job network = '||v_sql_stmt);
1921: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
1914: ||' FROM MRP_AP_JOB_OP_NETWORKS_V '||MSC_CL_PULL.v_dblink||' x '
1915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1916: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1917: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1918: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1919:
1920: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ap job network = '||v_sql_stmt);
1921: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
1922: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1917: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1918: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1919:
1920: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ap job network = '||v_sql_stmt);
1921: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
1922: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1923:
1924: COMMIT;
1925:
1922: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1923:
1924: COMMIT;
1925:
1926: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATIONS';
1927: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OPERATIONS_V';
1928:
1929: v_sql_stmt:=
1930: 'insert into MSC_ST_JOB_OPERATIONS'
1923:
1924: COMMIT;
1925:
1926: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATIONS';
1927: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OPERATIONS_V';
1928:
1929: v_sql_stmt:=
1930: 'insert into MSC_ST_JOB_OPERATIONS'
1931: ||' (WIP_ENTITY_ID,'
1972: ||' x.REVERSE_CUMULATIVE_YIELD,'
1973: ||' x.NET_PLANNING_PERCENT,'
1974: ||' 2,'
1975: ||' :v_refresh_id'
1976: ||' FROM MRP_AP_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1977: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1978: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1979: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1980: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1974: ||' 2,'
1975: ||' :v_refresh_id'
1976: ||' FROM MRP_AP_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1977: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1978: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1979: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1980: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1981:
1982: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
1975: ||' :v_refresh_id'
1976: ||' FROM MRP_AP_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1977: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1978: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1979: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1980: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1981:
1982: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1976: ||' FROM MRP_AP_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1977: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1978: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1979: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1980: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1981:
1982: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1984:
1978: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
1979: ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
1980: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
1981:
1982: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1984:
1985: COMMIT;
1986:
1983: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
1984:
1985: COMMIT;
1986:
1987: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_REQUIREMENT_OPS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_REQUIREMENT_OPS_V';
1989:
1990: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1991: v_temp_sql1 := ' x.BASIS_TYPE, ';
1984:
1985: COMMIT;
1986:
1987: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_REQUIREMENT_OPS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_REQUIREMENT_OPS_V';
1989:
1990: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1991: v_temp_sql1 := ' x.BASIS_TYPE, ';
1992: ELSE
1986:
1987: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_REQUIREMENT_OPS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_REQUIREMENT_OPS_V';
1989:
1990: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1991: v_temp_sql1 := ' x.BASIS_TYPE, ';
1992: ELSE
1993: v_temp_sql1 := ' NULL, ';
1994: END IF;
2047: ||' x.TO_END_ITEM_UNIT_NUMBER,'
2048: || v_temp_sql1
2049: ||' 2,'
2050: ||' :v_refresh_id'
2051: ||' FROM MRP_AP_JOB_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2052: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2053: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2054: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2055: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2049: ||' 2,'
2050: ||' :v_refresh_id'
2051: ||' FROM MRP_AP_JOB_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2052: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2053: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2054: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2055: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2056:
2057: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2050: ||' :v_refresh_id'
2051: ||' FROM MRP_AP_JOB_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2052: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2053: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2054: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2055: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2056:
2057: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2058: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
2051: ||' FROM MRP_AP_JOB_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2052: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2053: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2054: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2055: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2056:
2057: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2058: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
2059:
2053: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2054: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2055: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2056:
2057: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2058: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
2059:
2060: COMMIT;
2061:
2059:
2060: COMMIT;
2061:
2062:
2063: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2064: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_RESOURCES_V';
2065:
2066: v_sql_stmt:=
2067: 'insert into MSC_ST_JOB_OP_RESOURCES'
2060: COMMIT;
2061:
2062:
2063: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2064: MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_RESOURCES_V';
2065:
2066: v_sql_stmt:=
2067: 'insert into MSC_ST_JOB_OP_RESOURCES'
2068: ||' (WIP_ENTITY_ID,'
2123: ||' x.PARENT_RESOURCE_SEQ,'
2124: ||' x.MAXIMUM_ASSIGNED_UNITS,'
2125: ||' 2,'
2126: ||' :v_refresh_id'
2127: ||' FROM MRP_AP_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2129: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2130: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2131: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2125: ||' 2,'
2126: ||' :v_refresh_id'
2127: ||' FROM MRP_AP_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2129: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2130: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2131: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2132:
2133: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap job op resources = '||v_sql_stmt);
2126: ||' :v_refresh_id'
2127: ||' FROM MRP_AP_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2129: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2130: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2131: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2132:
2133: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap job op resources = '||v_sql_stmt);
2134: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2127: ||' FROM MRP_AP_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2128: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2129: ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2130: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2131: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2132:
2133: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap job op resources = '||v_sql_stmt);
2134: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2130: ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2131: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2132:
2133: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap job op resources = '||v_sql_stmt);
2134: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2136:
2137: COMMIT;
2138:
2135: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2136:
2137: COMMIT;
2138:
2139: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2140: MSC_CL_PULL.v_view_name := 'MRP_AP_LJ_SUB_OP_RESOURCES_V';
2141:
2142:
2143: v_sql_stmt:=
2136:
2137: COMMIT;
2138:
2139: MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2140: MSC_CL_PULL.v_view_name := 'MRP_AP_LJ_SUB_OP_RESOURCES_V';
2141:
2142:
2143: v_sql_stmt:=
2144: 'insert into MSC_ST_JOB_OP_RESOURCES'
2188: ||' x.SCHEDULE_FLAG,'
2189: ||' x.SETUP_ID,' /* ds change */
2190: ||' 2,'
2191: ||' :v_refresh_id'
2192: ||' FROM MRP_AP_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2193: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2194: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2195: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2196: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2190: ||' 2,'
2191: ||' :v_refresh_id'
2192: ||' FROM MRP_AP_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2193: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2194: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2195: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2196: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2197:
2198: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2191: ||' :v_refresh_id'
2192: ||' FROM MRP_AP_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2193: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2194: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2195: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2196: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2197:
2198: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2192: ||' FROM MRP_AP_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2193: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2194: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2195: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2196: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2197:
2198: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2200:
2194: ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2195: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2196: ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2197:
2198: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2199: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2200:
2201: COMMIT;
2202:
2206: END;
2207:
2208: -- ====================== 6: Repetitive Schedule ====================
2209:
2210: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
2211: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_SUPPLIES_V';
2212:
2213: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2214:
2207:
2208: -- ====================== 6: Repetitive Schedule ====================
2209:
2210: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
2211: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_SUPPLIES_V';
2212:
2213: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2214:
2215: v_union_sql :=
2209:
2210: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
2211: MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_SUPPLIES_V';
2212:
2213: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2214:
2215: v_union_sql :=
2216: ' AND ( x.RN1 > :v_lrn )'
2217: ||' UNION '
2238: ||' 30,'
2239: ||' 2,'
2240: ||' :v_refresh_id,'
2241: ||' :v_instance_id'
2242: ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2243: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2244: ||' AND (x.RN2 > :v_lrn)'
2245: ||' UNION '
2246: ||' select'
2266: ||' 30,'
2267: ||' 2,'
2268: ||' :v_refresh_id,'
2269: ||' :v_instance_id'
2270: ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2271: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2272: ||' AND (x.RN3 > :v_lrn)'
2273: ||' UNION '
2274: ||' select'
2294: ||' 30,'
2295: ||' 2,'
2296: ||' :v_refresh_id,'
2297: ||' :v_instance_id'
2298: ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2299: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2300: ||' AND (x.RN4 > :v_lrn)';
2301:
2302: ELSE
2353: ||' 30,'
2354: ||' 2,'
2355: ||' :v_refresh_id,'
2356: ||' :v_instance_id'
2357: ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2358: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2359: || v_union_sql;
2360:
2361: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2357: ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2358: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2359: || v_union_sql;
2360:
2361: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2362:
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2359: || v_union_sql;
2360:
2361: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2362:
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2360:
2361: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2362:
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2361: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2362:
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2362:
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2363: 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_dgmt, MSC_CL_PULL.v_dgmt,
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2371:
2364: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2371:
2372: ELSE
2365: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2371:
2372: ELSE
2373:
2366: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2367: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2368: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2369: MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2371:
2372: ELSE
2373:
2374: 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_dgmt, MSC_CL_PULL.v_dgmt,
2370: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2371:
2372: ELSE
2373:
2374: 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_dgmt, MSC_CL_PULL.v_dgmt,
2375: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2376: END IF;
2377:
2378: COMMIT;
2371:
2372: ELSE
2373:
2374: 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_dgmt, MSC_CL_PULL.v_dgmt,
2375: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2376: END IF;
2377:
2378: COMMIT;
2379:
2376: END IF;
2377:
2378: COMMIT;
2379:
2380: END IF; -- MSC_CL_PULL.WIP_ENABLED;
2381:
2382: END LOAD_WIP_SUPPLY;
2383:
2384: