23: PROCEDURE LOAD_FORECASTS IS
24:
25: BEGIN
26:
27: IF MSC_CL_PULL.FORECAST_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
30: MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DSGN_V';
31:
25: BEGIN
26:
27: IF MSC_CL_PULL.FORECAST_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
30: MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DSGN_V';
31:
32: v_sql_stmt :=
33: 'insert into MSC_ST_DESIGNATORS'
26:
27: IF MSC_CL_PULL.FORECAST_ENABLED= MSC_UTIL.SYS_YES THEN
28:
29: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
30: MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DSGN_V';
31:
32: v_sql_stmt :=
33: 'insert into MSC_ST_DESIGNATORS'
34: ||' ( DESIGNATOR,'
73: ||' x.DESIGNATOR_TYPE,'
74: ||' 2,'
75: ||' :v_refresh_id,'
76: ||' :v_instance_id'
77: ||' from MRP_AP_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
78: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
79: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
80:
81: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
75: ||' :v_refresh_id,'
76: ||' :v_instance_id'
77: ||' from MRP_AP_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
78: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
79: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
80:
81: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
82:
83: COMMIT;
77: ||' from MRP_AP_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
78: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
79: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
80:
81: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
82:
83: COMMIT;
84:
85: -- IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
81: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
82:
83: COMMIT;
84:
85: -- IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
86:
87: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
88: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DSGN_V';
89:
83: COMMIT;
84:
85: -- IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
86:
87: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
88: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DSGN_V';
89:
90: v_sql_stmt :=
91: 'insert into MSC_ST_DESIGNATORS'
84:
85: -- IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
86:
87: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
88: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DSGN_V';
89:
90: v_sql_stmt :=
91: 'insert into MSC_ST_DESIGNATORS'
92: ||' ( DESIGNATOR,'
101: ||' x.ORGANIZATION_ID,'
102: ||' 1,'
103: ||' :v_refresh_id,'
104: ||' :v_instance_id'
105: ||' from MRP_AD_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
106: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
107: ||' AND x.RN> '||MSC_CL_PULL.v_lrn
108: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
109:
103: ||' :v_refresh_id,'
104: ||' :v_instance_id'
105: ||' from MRP_AD_FORECAST_DSGN_V'||MSC_CL_PULL.v_dblink||' x'
106: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
107: ||' AND x.RN> '||MSC_CL_PULL.v_lrn
108: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
109:
110:
111: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
107: ||' AND x.RN> '||MSC_CL_PULL.v_lrn
108: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
109:
110:
111: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
112:
113: COMMIT;
114: -- END IF ; -- Incremental Refresh
115:
120:
121: PROCEDURE LOAD_ITEM_FORECASTS IS
122: BEGIN
123:
124: IF MSC_CL_PULL.FORECAST_ENABLED = MSC_UTIL.SYS_YES THEN
125:
126: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
127:
128: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
122: BEGIN
123:
124: IF MSC_CL_PULL.FORECAST_ENABLED = MSC_UTIL.SYS_YES THEN
125:
126: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
127:
128: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
129: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DEMAND_V';
130:
124: IF MSC_CL_PULL.FORECAST_ENABLED = MSC_UTIL.SYS_YES THEN
125:
126: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
127:
128: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
129: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DEMAND_V';
130:
131: v_sql_stmt :=
132: 'insert into MSC_ST_DEMANDS'
125:
126: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
127:
128: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
129: MSC_CL_PULL.v_view_name := 'MRP_AD_FORECAST_DEMAND_V';
130:
131: v_sql_stmt :=
132: 'insert into MSC_ST_DEMANDS'
133: ||' ( INVENTORY_ITEM_ID,'
146: ||' x.forecast_designator,'
147: ||' 1,'
148: ||' :v_refresh_id,'
149: ||' :v_instance_id'
150: ||' from MRP_AD_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
151: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
152: ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
153:
154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
148: ||' :v_refresh_id,'
149: ||' :v_instance_id'
150: ||' from MRP_AD_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
151: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
152: ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
153:
154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
155:
156: COMMIT;
150: ||' from MRP_AD_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
151: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
152: ||' AND x.RN>'||MSC_CL_PULL.v_lrn;
153:
154: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
155:
156: COMMIT;
157: END IF; -- Incremental Refresh.
158:
155:
156: COMMIT;
157: END IF; -- Incremental Refresh.
158:
159: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
160: MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DEMAND_V';
161:
162: v_sql_stmt:=
163: 'insert into MSC_ST_DEMANDS'
156: COMMIT;
157: END IF; -- Incremental Refresh.
158:
159: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
160: MSC_CL_PULL.v_view_name := 'MRP_AP_FORECAST_DEMAND_V';
161:
162: v_sql_stmt:=
163: 'insert into MSC_ST_DEMANDS'
164: ||' ( INVENTORY_ITEM_ID,'
225: ||' :v_refresh_id,'
226: ||' x.PLANNING_GROUP,'
227: ||' :v_instance_id,'
228: ||' x.TRANSACTION_ID '
229: ||' from MRP_AP_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
230: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
231: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
232:
233:
227: ||' :v_instance_id,'
228: ||' x.TRANSACTION_ID '
229: ||' from MRP_AP_FORECAST_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
230: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
231: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
232:
233:
234: EXECUTE IMMEDIATE v_sql_stmt
235: USING MSC_CL_PULL.v_msc_fcst_priority_flex_num,
231: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
232:
233:
234: EXECUTE IMMEDIATE v_sql_stmt
235: USING MSC_CL_PULL.v_msc_fcst_priority_flex_num,
236: MSC_CL_PULL.v_refresh_id,
237: MSC_CL_PULL.v_instance_id;
238:
239: COMMIT;
232:
233:
234: EXECUTE IMMEDIATE v_sql_stmt
235: USING MSC_CL_PULL.v_msc_fcst_priority_flex_num,
236: MSC_CL_PULL.v_refresh_id,
237: MSC_CL_PULL.v_instance_id;
238:
239: COMMIT;
240:
233:
234: EXECUTE IMMEDIATE v_sql_stmt
235: USING MSC_CL_PULL.v_msc_fcst_priority_flex_num,
236: MSC_CL_PULL.v_refresh_id,
237: MSC_CL_PULL.v_instance_id;
238:
239: COMMIT;
240:
241: END IF;
246:
247: PROCEDURE LOAD_MDS_DEMAND IS
248: BEGIN
249:
250: IF MSC_CL_PULL.MDS_ENABLED= MSC_UTIL.SYS_YES THEN
251:
252: --=================== Net Change Mode: Delete ==================
253:
254: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
250: IF MSC_CL_PULL.MDS_ENABLED= MSC_UTIL.SYS_YES THEN
251:
252: --=================== Net Change Mode: Delete ==================
253:
254: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
255:
256: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
257: MSC_CL_PULL.v_view_name := 'MRP_AD_MDS_DEMANDS_V';
258:
252: --=================== Net Change Mode: Delete ==================
253:
254: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
255:
256: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
257: MSC_CL_PULL.v_view_name := 'MRP_AD_MDS_DEMANDS_V';
258:
259: v_sql_stmt:=
260: 'insert into MSC_ST_DEMANDS'
253:
254: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
255:
256: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
257: MSC_CL_PULL.v_view_name := 'MRP_AD_MDS_DEMANDS_V';
258:
259: v_sql_stmt:=
260: 'insert into MSC_ST_DEMANDS'
261: ||' ( DISPOSITION_ID,'
274: ||' x.ORIGINATION_TYPE,'
275: ||' 1,'
276: ||' :v_refresh_id,'
277: ||' :v_instance_id'
278: ||' from MRP_AD_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
279: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
280: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
281:
282: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
275: ||' 1,'
276: ||' :v_refresh_id,'
277: ||' :v_instance_id'
278: ||' from MRP_AD_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
279: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
280: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
281:
282: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
283:
278: ||' from MRP_AD_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
279: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
280: ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
281:
282: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
283:
284: COMMIT;
285:
286: END IF;
284: COMMIT;
285:
286: END IF;
287:
288: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
289:
290: v_temp_sql := 'x.original_system_line_reference,x.original_system_reference,x.demand_source_type,x.demand_class,x.PROMISE_DATE,x.LINK_TO_LINE_ID,x.ORDER_DATE_TYPE_CODE,x.SCHEDULE_ARRIVAL_DATE,x.LATEST_ACCEPTABLE_DATE,x.SHIPPING_METHOD_CODE, ';
291:
292:
309: ||' 15, x.Attribute15,NULL),'
310: ||' 3,x.LATE_DEMAND_PENALTY_FACTOR) ), ';
311: ELSE
312:
313: IF (MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110) THEN
314: v_temp_sql :='x.original_system_line_reference ,x.original_system_reference,x.demand_source_type,NULL,x.PROMISE_DATE,NULL,';
315: ELSE
316: v_temp_sql :=' NULL, NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL, ';
317: END IF;
336: ||' 14, x.Attribute14,'
337: ||' 15, x.Attribute15)),';
338: END IF;
339:
340: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
341: MSC_CL_PULL.v_view_name := 'MRP_AP_MDS_DEMANDS_V';
342: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
343: v_union_sql :=
344: ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')' --NCP: changed to RN2
337: ||' 15, x.Attribute15)),';
338: END IF;
339:
340: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
341: MSC_CL_PULL.v_view_name := 'MRP_AP_MDS_DEMANDS_V';
342: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
343: v_union_sql :=
344: ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')' --NCP: changed to RN2
345: ||' UNION '
338: END IF;
339:
340: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
341: MSC_CL_PULL.v_view_name := 'MRP_AP_MDS_DEMANDS_V';
342: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
343: v_union_sql :=
344: ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')' --NCP: changed to RN2
345: ||' UNION '
346: ||' select'
340: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
341: MSC_CL_PULL.v_view_name := 'MRP_AP_MDS_DEMANDS_V';
342: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
343: v_union_sql :=
344: ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')' --NCP: changed to RN2
345: ||' UNION '
346: ||' select'
347: ||' x.INVENTORY_ITEM_ID,'
348: ||' x.ORGANIZATION_ID,'
407: ||' 2,'
408: || v_temp_sql
409: ||' :v_refresh_id,'
410: ||' :v_instance_id'
411: ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
412: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
413: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'; --NCP: changed to RN3
414:
415: ELSE
409: ||' :v_refresh_id,'
410: ||' :v_instance_id'
411: ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
412: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
413: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'; --NCP: changed to RN3
414:
415: ELSE
416: v_union_sql :=
417: ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
413: ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'; --NCP: changed to RN3
414:
415: ELSE
416: v_union_sql :=
417: ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
418: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
419: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
420: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
421:
414:
415: ELSE
416: v_union_sql :=
417: ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
418: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
419: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
420: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
421:
422: END IF;
415: ELSE
416: v_union_sql :=
417: ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
418: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
419: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
420: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
421:
422: END IF;
423:
416: v_union_sql :=
417: ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
418: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
419: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
420: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
421:
422: END IF;
423:
424: v_sql_stmt:=
527: ||' 2,'
528: || v_temp_sql
529: ||' :v_refresh_id,'
530: ||' :v_instance_id'
531: ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
532: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
533: || v_union_sql ;
534:
535: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
531: ||' from MRP_AP_MDS_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
532: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
533: || v_union_sql ;
534:
535: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
536: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
532: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
533: || v_union_sql ;
534:
535: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
536: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
534:
535: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
536: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
535: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
536: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
536: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
537: EXECUTE IMMEDIATE v_sql_stmt
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
538: USING MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
539: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
540: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
541: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
542: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
543: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
544: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
545: MSC_CL_PULL.v_instance_id,
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
546: MSC_CL_PULL.v_dgmt,
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
547: MSC_CL_PULL.v_dgmt,
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
548: MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
549: MSC_CL_PULL.v_mso_fcst_penalty,
550: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
551: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
552: MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
553: MSC_CL_PULL.v_instance_id;
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
554: ELSE
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
555: EXECUTE IMMEDIATE v_sql_stmt
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
556: USING MSC_CL_PULL.v_dgmt,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
557: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
558: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
559: MSC_CL_PULL.v_mso_fcst_penalty,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
560: MSC_CL_PULL.v_mso_so_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
561: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
562: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
563: MSC_CL_PULL.v_refresh_id,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
564: MSC_CL_PULL.v_instance_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
565: MSC_CL_PULL.v_dgmt,
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
573: MSC_CL_PULL.v_instance_id;
566: MSC_CL_PULL.v_dgmt,
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
573: MSC_CL_PULL.v_instance_id;
574: END IF;
567: MSC_CL_PULL.v_dgmt,
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
573: MSC_CL_PULL.v_instance_id;
574: END IF;
575:
568: MSC_CL_PULL.v_mso_fcst_penalty,
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
573: MSC_CL_PULL.v_instance_id;
574: END IF;
575:
576: ELSE -- Targeted - Complete Refresh collections
569: MSC_CL_PULL.v_mso_so_penalty,
570: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
571: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
572: MSC_CL_PULL.v_refresh_id,
573: MSC_CL_PULL.v_instance_id;
574: END IF;
575:
576: ELSE -- Targeted - Complete Refresh collections
577: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
573: MSC_CL_PULL.v_instance_id;
574: END IF;
575:
576: ELSE -- Targeted - Complete Refresh collections
577: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
578: EXECUTE IMMEDIATE v_sql_stmt
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
575:
576: ELSE -- Targeted - Complete Refresh collections
577: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
578: EXECUTE IMMEDIATE v_sql_stmt
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
576: ELSE -- Targeted - Complete Refresh collections
577: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
578: EXECUTE IMMEDIATE v_sql_stmt
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
577: IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
578: EXECUTE IMMEDIATE v_sql_stmt
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
578: EXECUTE IMMEDIATE v_sql_stmt
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
579: USING MSC_CL_PULL.v_dgmt,
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
580: MSC_CL_PULL.v_dgmt,
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
581: MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
582: MSC_CL_PULL.v_mso_fcst_penalty,
583: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
584: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
585: MSC_CL_PULL.v_refresh_id,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
586: MSC_CL_PULL.v_instance_id;
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
587: ELSE
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
588: EXECUTE IMMEDIATE v_sql_stmt
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
589: USING MSC_CL_PULL.v_dgmt,
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
597: MSC_CL_PULL.v_instance_id;
590: MSC_CL_PULL.v_dgmt,
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
597: MSC_CL_PULL.v_instance_id;
598: END IF;
591: MSC_CL_PULL.v_dgmt,
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
597: MSC_CL_PULL.v_instance_id;
598: END IF;
599:
592: MSC_CL_PULL.v_mso_fcst_penalty,
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
597: MSC_CL_PULL.v_instance_id;
598: END IF;
599:
600: END IF;
593: MSC_CL_PULL.v_mso_so_penalty,
594: MSC_CL_PULL.v_msc_dmd_priority_flex_num,
595: MSC_CL_PULL.v_msc_fcst_priority_flex_num,
596: MSC_CL_PULL.v_refresh_id,
597: MSC_CL_PULL.v_instance_id;
598: END IF;
599:
600: END IF;
601:
600: END IF;
601:
602: COMMIT;
603:
604: END IF; -- MSC_CL_PULL.MDS_ENABLED
605:
606: END LOAD_MDS_DEMAND;
607:
608:
618:
619: v_union_sql := ' ';
620: v_temp_sql4 := NULL;
621:
622: if(MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) then
623: v_temp_sql1:='x.SCHEDULE_ARRIVAL_DATE,x.LATEST_ACCEPTABLE_DATE,x.SHIPPING_METHOD_CODE,x.ATO_LINE_ID,x.ORDER_DATE_TYPE_CODE,x.DELIVERY_LEAD_TIME ';
624: else
625: v_temp_sql1:='NULL,NULL,NULL,NULL,NULL,NULL ';
626: end if;
624: else
625: v_temp_sql1:='NULL,NULL,NULL,NULL,NULL,NULL ';
626: end if;
627:
628: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS121 THEN
629: v_temp_sql10 := ' DECODE(x.OSP_FLAG , ''Y'',''N'',''N'', ''Y'',''Y''), ' ;
630: v_temp_sql11 := ' DECODE(x.OSP_FLAG,''Y'',' ||MSC_UTIL.G_PARTCONDN_ITEMTYPEID || '),'
631: ||' DECODE(x.OSP_FLAG , ''Y'',2,''N'', NULL), ' ;
632:
640: -- First get refresh type and SO parameter
641: select delete_ods_data,sales_order
642: into lv_complete_ref,lv_so_param
643: from msc_coll_parameters
644: where instance_id= MSC_CL_PULL.v_instance_id;
645:
646: if (lv_complete_ref = 1 and lv_so_param = 2 ) then
647: lv_net_sales_order := 1;
648: end if;
650: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_net_sales :'||lv_net_sales_order);
651:
652: IF ( p_worker_num = 3) THEN
653:
654: IF MSC_CL_PULL.v_so_lrn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_SALES_ORDERS_V';
658:
652: IF ( p_worker_num = 3) THEN
653:
654: IF MSC_CL_PULL.v_so_lrn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_SALES_ORDERS_V';
658:
659: Begin
660:
653:
654: IF MSC_CL_PULL.v_so_lrn<> -1 THEN -- incremental refresh
655:
656: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
657: MSC_CL_PULL.v_view_name := 'MRP_AD_SALES_ORDERS_V';
658:
659: Begin
660:
661: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
657: MSC_CL_PULL.v_view_name := 'MRP_AD_SALES_ORDERS_V';
658:
659: Begin
660:
661: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
662: v_temp_sql:= ' 2 ,';
663: ELSE
664: v_temp_sql:= ' x.CTO_FLAG,';
665: END IF ;
667:
668: End;
669: /*Added By raraghav */
670:
671: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
672: lv_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
673: ELSE
674: lv_temp_sql := NULL;
675: END IF;
694: ||' 2,'
695: ||' 2,'
696: ||' :v_refresh_id,'
697: ||' :v_instance_id'
698: ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
699: ||' where x.RN> :v_so_sr_lrn '
700: || lv_temp_sql;
701:
702: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
699: ||' where x.RN> :v_so_sr_lrn '
700: || lv_temp_sql;
701:
702: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
703: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
704:
705: COMMIT;
706:
707: v_sql_stmt:=
722: ||' 1,'
723: || v_temp_sql
724: ||' :v_refresh_id,'
725: ||' :v_instance_id'
726: ||' from MRP_AD_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
727: ||' WHERE x.RN> :v_so_lrn '
728: || lv_temp_sql;
729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
726: ||' from MRP_AD_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
727: ||' WHERE x.RN> :v_so_lrn '
728: || lv_temp_sql;
729: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
731:
732: COMMIT;
733:
734: END IF; --- MSC_CL_PULL.v_lrnn<> -1
730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
731:
732: COMMIT;
733:
734: END IF; --- MSC_CL_PULL.v_lrnn<> -1
735:
736: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
737:
738: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN -- 107 or 110 source instance
732: COMMIT;
733:
734: END IF; --- MSC_CL_PULL.v_lrnn<> -1
735:
736: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
737:
738: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN -- 107 or 110 source instance
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_ORDERS_V';
740: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
734: END IF; --- MSC_CL_PULL.v_lrnn<> -1
735:
736: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
737:
738: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN -- 107 or 110 source instance
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_ORDERS_V';
740: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
741:
742: /* Changed for the fix 2521038, */
735:
736: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
737:
738: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN -- 107 or 110 source instance
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_ORDERS_V';
740: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
741:
742: /* Changed for the fix 2521038, */
743: v_temp_sql2 := ' AND (x.PRIMARY_UOM_QUANTITY > x.COMPLETED_QUANTITY '
753: v_temp_sql := ' x.END_ITEM_UNIT_NUMBER , x.ordered_item_id,x.ORIGINAL_INVENTORY_ITEM_ID, '
754: || ' x.LINK_TO_LINE_ID, x.cust_po_number,x.customer_line_number, x.MFG_LEAD_TIME, x.FIRM_DEMAND_FLAG, x.SHIP_SET_ID, x.ARRIVAL_SET_ID, x.SHIP_SET_NAME, x.ARRIVAL_SET_NAME, ';
755:
756:
757: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
758: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
759: MSC_CL_PULL.v_view_name := 'MRP_AN3_SALES_ORDERS_V';
760: v_temp_sql3 := ' AND (x.RN1 > :v_so_lrn OR x.RN2> :v_so_lrn )';
761:
755:
756:
757: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
758: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
759: MSC_CL_PULL.v_view_name := 'MRP_AN3_SALES_ORDERS_V';
760: v_temp_sql3 := ' AND (x.RN1 > :v_so_lrn OR x.RN2> :v_so_lrn )';
761:
762: ELSE --- complete/targeted collections
763: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
762: ELSE --- complete/targeted collections
763: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
764: ||' OR (x.ORIGINAL_ORDERED_QUANTITY = x.ORIGINAL_COMPLETED_QUANTITY '
765: ||' AND x.requirement_date >= trunc(sysdate - (' || v_msc_so_offset_days ||' )))) ';
766: MSC_CL_PULL.v_view_name := 'MRP_AP3_SALES_ORDERS_V';
767: v_temp_sql3 := ' ';
768: END IF;
769: END IF;
770:
770:
771:
772: ELSIF ( p_worker_num in (1,2) ) THEN
773:
774: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
775:
776: -- IF (MSC_CL_PULL.v_lrnn<> -1 OR lv_net_sales_order = 1 )
777: IF (MSC_CL_PULL.v_so_lrn<> -1 )
778: THEN -- incremental refresh or complete with SO No
772: ELSIF ( p_worker_num in (1,2) ) THEN
773:
774: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
775:
776: -- IF (MSC_CL_PULL.v_lrnn<> -1 OR lv_net_sales_order = 1 )
777: IF (MSC_CL_PULL.v_so_lrn<> -1 )
778: THEN -- incremental refresh or complete with SO No
779: MSC_CL_PULL.v_view_name := 'MRP_AN'||p_worker_num||'_SALES_ORDERS_V';
780:
773:
774: MSC_CL_PULL.v_table_name:= 'MSC_ST_SALES_ORDERS';
775:
776: -- IF (MSC_CL_PULL.v_lrnn<> -1 OR lv_net_sales_order = 1 )
777: IF (MSC_CL_PULL.v_so_lrn<> -1 )
778: THEN -- incremental refresh or complete with SO No
779: MSC_CL_PULL.v_view_name := 'MRP_AN'||p_worker_num||'_SALES_ORDERS_V';
780:
781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Coming here :'||lv_net_sales_order);
775:
776: -- IF (MSC_CL_PULL.v_lrnn<> -1 OR lv_net_sales_order = 1 )
777: IF (MSC_CL_PULL.v_so_lrn<> -1 )
778: THEN -- incremental refresh or complete with SO No
779: MSC_CL_PULL.v_view_name := 'MRP_AN'||p_worker_num||'_SALES_ORDERS_V';
780:
781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Coming here :'||lv_net_sales_order);
782: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Coming here :'||MSC_CL_PULL.v_so_lrn);
783: if p_worker_num <> 2 then
778: THEN -- incremental refresh or complete with SO No
779: MSC_CL_PULL.v_view_name := 'MRP_AN'||p_worker_num||'_SALES_ORDERS_V';
780:
781: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Coming here :'||lv_net_sales_order);
782: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Coming here :'||MSC_CL_PULL.v_so_lrn);
783: if p_worker_num <> 2 then
784: if p_worker_num = 1 then
785: v_temp_sql3 := ' AND (x.RN1 > :v_so_lrn OR x.RN2> :v_so_lrn OR x.RN3> :v_so_lrn )';
786: /* elsif p_worker_num = 3 then
821: ||v_temp_sql10
822: ||' x.DEMAND_VISIBLE,'
823: ||' x.SALESREP_CONTACT,';
824:
825: if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 then
826: v_union_sql := v_union_sql ||' x.SALESREP_ID,';
827: else
828: v_union_sql := v_union_sql ||' NULL,';
829: end if;
860: ||v_temp_sql11
861: ||' :v_refresh_id,'
862: ||' :v_instance_id, '
863: || v_temp_sql1
864: ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
865: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
866: || v_temp_sql2
867: ||' and ( x.rn1 > :v_so_lrn ) ' ; /*10201485*/
868:
870:
871: end if;
872:
873: ELSE
874: MSC_CL_PULL.v_view_name := 'MRP_AP'||p_worker_num||'_SALES_ORDERS_V';
875: v_temp_sql3 := ' ';
876:
877: END IF;
878:
879: v_temp_sql := ' x.END_ITEM_UNIT_NUMBER , x.ordered_item_id,x.ORIGINAL_INVENTORY_ITEM_ID , '
880: ||' x.LINK_TO_LINE_ID, x.cust_po_number,x.customer_line_number,x.MFG_LEAD_TIME,x.FIRM_DEMAND_FLAG,x.SHIP_SET_ID,x.ARRIVAL_SET_ID,x.SHIP_SET_NAME,x.ARRIVAL_SET_NAME, ';
881:
882: IF (p_worker_num = 2 AND -- Bug 4245915
883: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
884: v_temp_sql4 := ' AND ( x.visible_demand_flag = ''Y'' OR (x.visible_demand_flag = ''N'' AND x.order_type='''||v_msc_x_vmi_om_order_type||''')) ';
885: END IF;
886:
887: END IF; --- (p_worker_num = 4 ) condition
886:
887: END IF; --- (p_worker_num = 4 ) condition
888:
889:
890: IF (MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 OR
891: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
892:
893: /* Changed for the fix 2521038, note the views MRP_AP_SALES_ORDERS_V and MRP_AN_SALES_ORDERS_V
894: have also been changed for this. Only if the source is 115 the new columns ORIGINAL_ORDERED_QUANTITY,
887: END IF; --- (p_worker_num = 4 ) condition
888:
889:
890: IF (MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 OR
891: MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
892:
893: /* Changed for the fix 2521038, note the views MRP_AP_SALES_ORDERS_V and MRP_AN_SALES_ORDERS_V
894: have also been changed for this. Only if the source is 115 the new columns ORIGINAL_ORDERED_QUANTITY,
895: ORIGINAL_COMPLETED_QUANTITY are applicable.
894: have also been changed for this. Only if the source is 115 the new columns ORIGINAL_ORDERED_QUANTITY,
895: ORIGINAL_COMPLETED_QUANTITY are applicable.
896: */
897:
898: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS110 THEN
899:
900: v_temp_sql2 := ' AND (x.PRIMARY_UOM_QUANTITY > x.COMPLETED_QUANTITY '
901: ||' OR (x.PRIMARY_UOM_QUANTITY = x.COMPLETED_QUANTITY ';
902:
903: v_temp_sql2 := v_temp_sql2 ||' AND x.requirement_date >= trunc( sysdate - (' || v_msc_so_offset_days ||' )))) ';
904:
905: END IF; /* MSC_UTIL.G_APPS110 */
906:
907: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 AND (p_worker_num <> 2) THEN
908: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
909: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
910:
911: ELSE
904:
905: END IF; /* MSC_UTIL.G_APPS110 */
906:
907: IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 AND (p_worker_num <> 2) THEN
908: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
909: v_temp_sql2 := ' AND (x.ORIGINAL_ORDERED_QUANTITY >= x.ORIGINAL_COMPLETED_QUANTITY) ';
910:
911: ELSE
912: v_temp_sql2 := 'AND (x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
922: /* This will handle AP2 and AN2 - first union */
923: v_temp_sql2 := ' ';
924: END IF;
925:
926: IF MSC_CL_PULL.v_view_name = 'MRP_AP2_SALES_ORDERS_V' THEN /* Bug 3019053 */
927: v_temp_sql2 := ' ';
928: END IF;
929:
930: IF MSC_CL_PULL.v_view_name = 'MRP_AN2_SALES_ORDERS_V' THEN
926: IF MSC_CL_PULL.v_view_name = 'MRP_AP2_SALES_ORDERS_V' THEN /* Bug 3019053 */
927: v_temp_sql2 := ' ';
928: END IF;
929:
930: IF MSC_CL_PULL.v_view_name = 'MRP_AN2_SALES_ORDERS_V' THEN
931: v_select_sql := ' SELECT /*+ index( x.ool oe_odr_lines_sn_n1) */ ';
932: ELSE
933: v_select_sql := ' SELECT ';
934: END IF;
1025: ||v_temp_sql10
1026: ||' x.DEMAND_VISIBLE,'
1027: ||' x.SALESREP_CONTACT,';
1028:
1029: if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 then
1030: v_sql_stmt := v_sql_stmt ||' x.SALESREP_ID,';
1031: else
1032: v_sql_stmt := v_sql_stmt ||' NULL,';
1033: end if;
1053: ||v_temp_sql11
1054: ||' :v_refresh_id,'
1055: ||' :v_instance_id,'
1056: || v_temp_sql1
1057: ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1058: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1059: || v_temp_sql2
1060: || v_temp_sql3
1061: || v_temp_sql4
1060: || v_temp_sql3
1061: || v_temp_sql4
1062: || v_union_sql ;
1063:
1064: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN
1065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
1066: EXECUTE IMMEDIATE v_sql_stmt
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1063:
1064: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN
1065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
1066: EXECUTE IMMEDIATE v_sql_stmt
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1069: MSC_CL_PULL.v_so_lrn,
1070: MSC_CL_PULL.v_so_lrn,
1071: MSC_CL_PULL.v_so_lrn;
1064: IF MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS115 THEN
1065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
1066: EXECUTE IMMEDIATE v_sql_stmt
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1069: MSC_CL_PULL.v_so_lrn,
1070: MSC_CL_PULL.v_so_lrn,
1071: MSC_CL_PULL.v_so_lrn;
1072:
1065: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_sql_stmt);
1066: EXECUTE IMMEDIATE v_sql_stmt
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1069: MSC_CL_PULL.v_so_lrn,
1070: MSC_CL_PULL.v_so_lrn,
1071: MSC_CL_PULL.v_so_lrn;
1072:
1073: ELSE -- 11i source instance
1066: EXECUTE IMMEDIATE v_sql_stmt
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1069: MSC_CL_PULL.v_so_lrn,
1070: MSC_CL_PULL.v_so_lrn,
1071: MSC_CL_PULL.v_so_lrn;
1072:
1073: ELSE -- 11i source instance
1074:
1067: USING MSC_CL_PULL.v_refresh_id,
1068: MSC_CL_PULL.v_instance_id,
1069: MSC_CL_PULL.v_so_lrn,
1070: MSC_CL_PULL.v_so_lrn,
1071: MSC_CL_PULL.v_so_lrn;
1072:
1073: ELSE -- 11i source instance
1074:
1075: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
1071: MSC_CL_PULL.v_so_lrn;
1072:
1073: ELSE -- 11i source instance
1074:
1075: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
1076: if (p_worker_num = 2) then
1077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1078: EXECUTE IMMEDIATE v_sql_stmt
1079: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn,
1075: IF (MSC_CL_PULL.v_so_lrn <> -1) THEN -- incremental collections
1076: if (p_worker_num = 2) then
1077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1078: EXECUTE IMMEDIATE v_sql_stmt
1079: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn,
1080: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
1081: elsif (p_worker_num in (3)) then
1082: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1083: EXECUTE IMMEDIATE v_sql_stmt USING
1076: if (p_worker_num = 2) then
1077: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1078: EXECUTE IMMEDIATE v_sql_stmt
1079: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn,
1080: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
1081: elsif (p_worker_num in (3)) then
1082: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1083: EXECUTE IMMEDIATE v_sql_stmt USING
1084: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id
1080: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_so_lrn;
1081: elsif (p_worker_num in (3)) then
1082: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1083: EXECUTE IMMEDIATE v_sql_stmt USING
1084: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id
1085: ,MSC_CL_PULL.v_so_lrn,MSC_CL_PULL.v_so_lrn;
1086:
1087: else
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1081: elsif (p_worker_num in (3)) then
1082: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1083: EXECUTE IMMEDIATE v_sql_stmt USING
1084: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id
1085: ,MSC_CL_PULL.v_so_lrn,MSC_CL_PULL.v_so_lrn;
1086:
1087: else
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1089: EXECUTE IMMEDIATE v_sql_stmt
1086:
1087: else
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1089: EXECUTE IMMEDIATE v_sql_stmt
1090: USING MSC_CL_PULL.v_refresh_id,
1091: MSC_CL_PULL.v_instance_id,
1092: MSC_CL_PULL.v_so_lrn,
1093: MSC_CL_PULL.v_so_lrn,
1094: MSC_CL_PULL.v_so_lrn;
1087: else
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1089: EXECUTE IMMEDIATE v_sql_stmt
1090: USING MSC_CL_PULL.v_refresh_id,
1091: MSC_CL_PULL.v_instance_id,
1092: MSC_CL_PULL.v_so_lrn,
1093: MSC_CL_PULL.v_so_lrn,
1094: MSC_CL_PULL.v_so_lrn;
1095: end if;
1088: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1089: EXECUTE IMMEDIATE v_sql_stmt
1090: USING MSC_CL_PULL.v_refresh_id,
1091: MSC_CL_PULL.v_instance_id,
1092: MSC_CL_PULL.v_so_lrn,
1093: MSC_CL_PULL.v_so_lrn,
1094: MSC_CL_PULL.v_so_lrn;
1095: end if;
1096:
1089: EXECUTE IMMEDIATE v_sql_stmt
1090: USING MSC_CL_PULL.v_refresh_id,
1091: MSC_CL_PULL.v_instance_id,
1092: MSC_CL_PULL.v_so_lrn,
1093: MSC_CL_PULL.v_so_lrn,
1094: MSC_CL_PULL.v_so_lrn;
1095: end if;
1096:
1097: ELSE --- complete/targeted collections
1090: USING MSC_CL_PULL.v_refresh_id,
1091: MSC_CL_PULL.v_instance_id,
1092: MSC_CL_PULL.v_so_lrn,
1093: MSC_CL_PULL.v_so_lrn,
1094: MSC_CL_PULL.v_so_lrn;
1095: end if;
1096:
1097: ELSE --- complete/targeted collections
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1096:
1097: ELSE --- complete/targeted collections
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1099: EXECUTE IMMEDIATE v_sql_stmt
1100: USING MSC_CL_PULL.v_refresh_id,
1101: MSC_CL_PULL.v_instance_id;
1102: END IF;
1103: END IF;
1104:
1097: ELSE --- complete/targeted collections
1098: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1099: EXECUTE IMMEDIATE v_sql_stmt
1100: USING MSC_CL_PULL.v_refresh_id,
1101: MSC_CL_PULL.v_instance_id;
1102: END IF;
1103: END IF;
1104:
1105: ELSE -- 107 source instance
1164: ||' x.PROJECT_ID,'
1165: ||' x.TASK_ID,'
1166: ||' x.PLANNING_GROUP,'
1167: ||' :v_instance_id'
1168: ||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
1169: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1170: ||' AND (x.RN1>'||MSC_CL_PULL.v_so_lrn
1171: ||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
1172: ||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
1166: ||' x.PLANNING_GROUP,'
1167: ||' :v_instance_id'
1168: ||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
1169: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1170: ||' AND (x.RN1>'||MSC_CL_PULL.v_so_lrn
1171: ||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
1172: ||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
1173:
1174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1167: ||' :v_instance_id'
1168: ||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
1169: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1170: ||' AND (x.RN1>'||MSC_CL_PULL.v_so_lrn
1171: ||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
1172: ||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
1173:
1174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1175: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1168: ||' FROM MRP_AP_SALES_ORDERS_V'||MSC_CL_PULL.v_dblink||' x'
1169: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1170: ||' AND (x.RN1>'||MSC_CL_PULL.v_so_lrn
1171: ||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
1172: ||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
1173:
1174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1175: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1176:
1171: ||' OR x.RN2>'||MSC_CL_PULL.v_so_lrn
1172: ||' OR x.RN3>'||MSC_CL_PULL.v_so_lrn||')';
1173:
1174: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,v_sql_stmt);
1175: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1176:
1177: END IF;
1178: COMMIT;
1179: END LOAD_SALES_ORDER;
1183:
1184: PROCEDURE LOAD_HARD_RESERVATION IS
1185: BEGIN
1186:
1187: IF MSC_CL_PULL.HARD_RESRVS_ENABLED= MSC_UTIL.SYS_YES THEN
1188:
1189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1190:
1191: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1185: BEGIN
1186:
1187: IF MSC_CL_PULL.HARD_RESRVS_ENABLED= MSC_UTIL.SYS_YES THEN
1188:
1189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1190:
1191: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1192: MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
1193:
1187: IF MSC_CL_PULL.HARD_RESRVS_ENABLED= MSC_UTIL.SYS_YES THEN
1188:
1189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1190:
1191: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1192: MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
1193:
1194: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1195: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1188:
1189: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1190:
1191: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1192: MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
1193:
1194: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1195: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1196: ELSE
1190:
1191: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1192: MSC_CL_PULL.v_view_name := 'MRP_AD_HARD_RESERVATIONS_V';
1193:
1194: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1195: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1196: ELSE
1197: v_temp_sql := NULL;
1198: END IF;
1209: ||' x.PARENT_DEMAND_ID,'
1210: ||' 1,'
1211: ||' :v_refresh_id,'
1212: ||' :v_instance_id'
1213: ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1214: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1215: || v_temp_sql;
1216:
1217: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1210: ||' 1,'
1211: ||' :v_refresh_id,'
1212: ||' :v_instance_id'
1213: ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1214: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1215: || v_temp_sql;
1216:
1217: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1218:
1213: ||' from MRP_AD_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1214: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1215: || v_temp_sql;
1216:
1217: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1218:
1219: COMMIT;
1220: /* Changes For Bug 6147734 */
1221: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
1217: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1218:
1219: COMMIT;
1220: /* Changes For Bug 6147734 */
1221: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
1222: v_sql_stmt:=
1223: 'insert into MSC_ST_RESERVATIONS'
1224: ||' ( TRANSACTION_ID,'
1225: ||' SUPPLY_SOURCE_TYPE_ID,'
1243: ||' x.ORDER2_INVENTORY_ITEM_ID,'
1244: ||' 1,'
1245: ||' :v_refresh_id,'
1246: ||' :v_instance_id'
1247: ||' from MRP_AD_REPAIR_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1248: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1249: ;
1250:
1251:
1244: ||' 1,'
1245: ||' :v_refresh_id,'
1246: ||' :v_instance_id'
1247: ||' from MRP_AD_REPAIR_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1248: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1249: ;
1250:
1251:
1252: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1248: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1249: ;
1250:
1251:
1252: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1253: COMMIT;
1254: End If; -- Srp Profile Check
1255: END IF; -- Incremental Refresh
1256:
1253: COMMIT;
1254: End If; -- Srp Profile Check
1255: END IF; -- Incremental Refresh
1256:
1257: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1258: v_temp_sql := ' AND x.GET_ORDERED_QUANTITY > x.GET_SHIPPED_QUANTITY ';
1259: ELSE
1260: v_temp_sql := ' ';
1261: END IF;
1259: ELSE
1260: v_temp_sql := ' ';
1261: END IF;
1262:
1263: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1264: v_temp_sql1 := ' AND x.RN1 > :v_lrn';
1265: /* ds change */
1266: v_temp_sql2 := ' SUPPLY_SOURCE_HEADER_ID,SUPPLY_SOURCE_TYPE_ID, ';
1267: v_temp_sql3 := ' x.SUPPLY_SOURCE_HEADER_ID,x.SUPPLY_SOURCE_TYPE_ID, ';
1270: v_temp_sql2 := ' NULL,NULL, ';
1271: v_temp_sql3 := ' NULL.NULL, ';
1272: END IF;
1273:
1274: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1275: MSC_CL_PULL.v_view_name := 'MRP_AP_HARD_RESERVATIONS_V';
1276:
1277: v_sql_stmt:=
1278: 'insert into MSC_ST_RESERVATIONS'
1271: v_temp_sql3 := ' NULL.NULL, ';
1272: END IF;
1273:
1274: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESERVATIONS';
1275: MSC_CL_PULL.v_view_name := 'MRP_AP_HARD_RESERVATIONS_V';
1276:
1277: v_sql_stmt:=
1278: 'insert into MSC_ST_RESERVATIONS'
1279: ||' ( INVENTORY_ITEM_ID,'
1319: || v_temp_sql3
1320: ||' 2,'
1321: ||' :v_refresh_id,'
1322: ||' :v_instance_id'
1323: ||' from MRP_AP_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1324: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str|| v_temp_sql || v_temp_sql1;
1325:
1326: /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1322: ||' :v_instance_id'
1323: ||' from MRP_AP_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1324: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str|| v_temp_sql || v_temp_sql1;
1325:
1326: /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1323: ||' from MRP_AP_HARD_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1324: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str|| v_temp_sql || v_temp_sql1;
1325:
1326: /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1325:
1326: /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1326: /*||' AND (' -- NCP: x.RN1>'||MSC_CL_PULL.v_lrn
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1327: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1328:
1329: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1336:
1330: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1336:
1337: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1338: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1331: ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
1332: ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')'; */
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1336:
1337: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1338: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1339: ELSE
1333:
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1336:
1337: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1338: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1339: ELSE
1340: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn;
1341: END IF;
1334: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1335: -- EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1336:
1337: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1338: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1339: ELSE
1340: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn;
1341: END IF;
1342:
1336:
1337: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1338: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1339: ELSE
1340: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn, MSC_CL_PULL.v_lrn;
1341: END IF;
1342:
1343: COMMIT;
1344:
1342:
1343: COMMIT;
1344:
1345:
1346: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then -- SRP Changes For Bug 5988024
1347:
1348: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1349: v_temp_sql1 := ' OR x.date1 > :date1 OR x.date2 > :date2 ';
1350: ELSE
1344:
1345:
1346: if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then -- SRP Changes For Bug 5988024
1347:
1348: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1349: v_temp_sql1 := ' OR x.date1 > :date1 OR x.date2 > :date2 ';
1350: ELSE
1351: v_temp_sql1 := NULL;
1352: END IF;
1383: ||' x.TASK_ID,'
1384: ||' 2,'
1385: ||' :v_refresh_id,'
1386: ||' :v_instance_id'
1387: ||' from MRP_AP_REPAIR_TRANSFERS_RESV_V'||MSC_CL_PULL.v_dblink||' x'
1388: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str
1389: ||' AND x.RN1 > :v_lrn'
1390: || v_temp_sql1;
1391:
1390: || v_temp_sql1;
1391:
1392:
1393:
1394: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1395: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1396: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1397: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME,MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1398: /* For Bug 6144734 */
1391:
1392:
1393:
1394: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1395: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1396: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1397: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME,MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1398: /* For Bug 6144734 */
1399: v_sql_stmt:=
1392:
1393:
1394: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1395: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1396: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1397: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME,MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1398: /* For Bug 6144734 */
1399: v_sql_stmt:=
1400: 'Insert into MSC_ST_RESERVATIONS'
1393:
1394: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1395: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1396: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
1397: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME,MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1398: /* For Bug 6144734 */
1399: v_sql_stmt:=
1400: 'Insert into MSC_ST_RESERVATIONS'
1401: ||' ( TRANSACTION_ID,'
1420: ||' null,'
1421: ||' 1,'
1422: ||' :v_refresh_id,'
1423: ||' :v_instance_id'
1424: ||' from MRP_AP_REPAIR_ORDERS_V'||MSC_CL_PULL.v_dblink ||' x'
1425: ||' where x.organization_id '||MSC_UTIL.v_depot_org_str
1426: || ' AND x.RO_STATUS_CODE = '||'''C'''
1427: ||' AND x. LAST_UPDATE_DATE > :date1' ;
1428:
1427: ||' AND x. LAST_UPDATE_DATE > :date1' ;
1428:
1429:
1430:
1431: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1432: MSC_CL_PULL.v_instance_id,
1433: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1434:
1435: ELSE
1428:
1429:
1430:
1431: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1432: MSC_CL_PULL.v_instance_id,
1433: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1434:
1435: ELSE
1436: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1429:
1430:
1431: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1432: MSC_CL_PULL.v_instance_id,
1433: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1434:
1435: ELSE
1436: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1437: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1432: MSC_CL_PULL.v_instance_id,
1433: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1434:
1435: ELSE
1436: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1437: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1438: END IF;
1439:
1440:
1433: MSC_CL_PULL.g_LAST_SUCC_RES_REF_TIME;
1434:
1435: ELSE
1436: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1437: MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1438: END IF;
1439:
1440:
1441: commit;
1474: ||' x.TASK_ID,'
1475: ||' 2,'
1476: ||' :v_refresh_id,'
1477: ||' :v_instance_id'
1478: ||' from MRP_AP_EXT_REP_RESERVATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1479: ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
1480: ||' AND ((x.RN > :v_lrn) OR (x.RN1 > :v_lrn) OR (x.RN2 > :v_lrn) OR (x.RN3 > :v_lrn)
1481: OR (x.RN4 > :v_lrn) OR (x.RN5 > :v_lrn) OR (x.RN6 > :v_lrn))'
1482: ;
1481: OR (x.RN4 > :v_lrn) OR (x.RN5 > :v_lrn) OR (x.RN6 > :v_lrn))'
1482: ;
1483:
1484: /* Changed For bug 6144734 */
1485: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1482: ;
1483:
1484: /* Changed For bug 6144734 */
1485: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1483:
1484: /* Changed For bug 6144734 */
1485: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1484: /* Changed For bug 6144734 */
1485: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1485: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1493: MSC_CL_PULL.v_lrn;
1486: MSC_CL_PULL.v_instance_id,
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1493: MSC_CL_PULL.v_lrn;
1494:
1487: MSC_CL_PULL.v_lrn,
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1493: MSC_CL_PULL.v_lrn;
1494:
1495: commit;
1488: MSC_CL_PULL.v_lrn,
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1493: MSC_CL_PULL.v_lrn;
1494:
1495: commit;
1496:
1489: MSC_CL_PULL.v_lrn,
1490: MSC_CL_PULL.v_lrn,
1491: MSC_CL_PULL.v_lrn,
1492: MSC_CL_PULL.v_lrn,
1493: MSC_CL_PULL.v_lrn;
1494:
1495: commit;
1496:
1497: END IF; -- SRP Changes For Bug 5988024
1503:
1504: PROCEDURE LOAD_USER_DEMAND IS
1505: BEGIN
1506:
1507: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1508: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1509:
1510: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1511:
1504: PROCEDURE LOAD_USER_DEMAND IS
1505: BEGIN
1506:
1507: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1508: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1509:
1510: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1511:
1512: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1506:
1507: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND
1508: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1509:
1510: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1511:
1512: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1513: MSC_CL_PULL.v_view_name := 'MRP_AD_USER_DEMANDS_V';
1514:
1508: MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
1509:
1510: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1511:
1512: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1513: MSC_CL_PULL.v_view_name := 'MRP_AD_USER_DEMANDS_V';
1514:
1515: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1516: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1509:
1510: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1511:
1512: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1513: MSC_CL_PULL.v_view_name := 'MRP_AD_USER_DEMANDS_V';
1514:
1515: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1516: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1517: ELSE
1511:
1512: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1513: MSC_CL_PULL.v_view_name := 'MRP_AD_USER_DEMANDS_V';
1514:
1515: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1516: v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1517: ELSE
1518: v_temp_sql := NULL;
1519: END IF;
1532: ||' 1,'
1533: ||' x.ORGANIZATION_ID,'
1534: ||' :v_refresh_id,'
1535: ||' :v_instance_id'
1536: ||' FROM MRP_AD_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1537: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1538: || v_temp_sql;
1539:
1540: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1533: ||' x.ORGANIZATION_ID,'
1534: ||' :v_refresh_id,'
1535: ||' :v_instance_id'
1536: ||' FROM MRP_AD_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1537: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1538: || v_temp_sql;
1539:
1540: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1541:
1536: ||' FROM MRP_AD_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1537: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
1538: || v_temp_sql;
1539:
1540: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1541:
1542: COMMIT;
1543:
1544: END IF;
1542: COMMIT;
1543:
1544: END IF;
1545:
1546: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1547: MSC_CL_PULL.v_view_name := 'MRP_AP_USER_DEMANDS_V';
1548:
1549: v_sql_stmt:=
1550: ' INSERT INTO MSC_ST_DEMANDS'
1543:
1544: END IF;
1545:
1546: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
1547: MSC_CL_PULL.v_view_name := 'MRP_AP_USER_DEMANDS_V';
1548:
1549: v_sql_stmt:=
1550: ' INSERT INTO MSC_ST_DEMANDS'
1551: ||'( DISPOSITION_ID,'
1575: ||' x.DEMAND_CLASS,'
1576: ||' 2,'
1577: ||' :v_refresh_id,'
1578: ||' :v_instance_id'
1579: ||' FROM MRP_AP_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1580: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1581: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1582: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1583:
1577: ||' :v_refresh_id,'
1578: ||' :v_instance_id'
1579: ||' FROM MRP_AP_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1580: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1581: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1582: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1583:
1584: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1585: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1578: ||' :v_instance_id'
1579: ||' FROM MRP_AP_USER_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
1580: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1581: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1582: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1583:
1584: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1585: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1586:
1580: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1581: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1582: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1583:
1584: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1585: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1586:
1587: COMMIT;
1588:
1581: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1582: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1583:
1584: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1585: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1586:
1587: COMMIT;
1588:
1589: END IF;
1600: lv_temp_sql VARCHAR2(1024);
1601: lv_usaf_temp_sql VARCHAR2(30);
1602: BEGIN
1603:
1604: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1605: MSC_CL_PULL.v_view_name := 'MRP_AN_AHL_MTL_REQS_V';
1606: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
1607:
1608: ELSE
1601: lv_usaf_temp_sql VARCHAR2(30);
1602: BEGIN
1603:
1604: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1605: MSC_CL_PULL.v_view_name := 'MRP_AN_AHL_MTL_REQS_V';
1606: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
1607:
1608: ELSE
1609: MSC_CL_PULL.v_view_name := 'MRP_AP_AHL_MTL_REQS_V';
1605: MSC_CL_PULL.v_view_name := 'MRP_AN_AHL_MTL_REQS_V';
1606: v_temp_sql3 := ' AND (x.RN1 > :v_lrn OR x.RN2> :v_lrn OR x.RN3> :v_lrn )';
1607:
1608: ELSE
1609: MSC_CL_PULL.v_view_name := 'MRP_AP_AHL_MTL_REQS_V';
1610: v_temp_sql3 := ' ';
1611:
1612: END IF;
1613:
1611:
1612: END IF;
1613:
1614: IF ((MSC_UTIL.g_collect_cmro_data = 'Y' and
1615: MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121) OR
1616: (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
1617: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' )) THEN
1618: lv_usaf_temp_sql := '2,' ;
1619: v_temp_sql3 := v_temp_sql3 ||' AND x.ATP_FLAG = ''Y''' ;
1612: END IF;
1613:
1614: IF ((MSC_UTIL.g_collect_cmro_data = 'Y' and
1615: MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121) OR
1616: (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
1617: AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' )) THEN
1618: lv_usaf_temp_sql := '2,' ;
1619: v_temp_sql3 := v_temp_sql3 ||' AND x.ATP_FLAG = ''Y''' ;
1620: ELSE
1742: -- ||' x.LATEST_ACCEPTABLE_DATE,'
1743: -- ||' x.SHIPPING_METHOD_CODE,'
1744: -- ||' x.ATO_LINE_ID,'
1745: -- ||' x.ORDER_DATE_TYPE_CODE '
1746: ||' FROM '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1747: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1748: ||' AND x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
1749: || v_temp_sql3 ;
1750:
1748: ||' AND x.ORIGINAL_ORDERED_QUANTITY > x.ORIGINAL_COMPLETED_QUANTITY '
1749: || v_temp_sql3 ;
1750:
1751:
1752: IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
1753:
1754: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1751:
1752: IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
1753:
1754: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1757: MSC_CL_PULL.v_lrn,
1758: MSC_CL_PULL.v_lrn,
1759: MSC_CL_PULL.v_lrn;
1752: IF (MSC_CL_PULL.v_lrnn <> -1) THEN -- incremental collections
1753:
1754: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1757: MSC_CL_PULL.v_lrn,
1758: MSC_CL_PULL.v_lrn,
1759: MSC_CL_PULL.v_lrn;
1760:
1753:
1754: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1757: MSC_CL_PULL.v_lrn,
1758: MSC_CL_PULL.v_lrn,
1759: MSC_CL_PULL.v_lrn;
1760:
1761: ELSE --- complete/targeted collections
1754: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1757: MSC_CL_PULL.v_lrn,
1758: MSC_CL_PULL.v_lrn,
1759: MSC_CL_PULL.v_lrn;
1760:
1761: ELSE --- complete/targeted collections
1762: EXECUTE IMMEDIATE v_sql_stmt
1755: USING MSC_CL_PULL.v_refresh_id,
1756: MSC_CL_PULL.v_instance_id,
1757: MSC_CL_PULL.v_lrn,
1758: MSC_CL_PULL.v_lrn,
1759: MSC_CL_PULL.v_lrn;
1760:
1761: ELSE --- complete/targeted collections
1762: EXECUTE IMMEDIATE v_sql_stmt
1763: USING MSC_CL_PULL.v_refresh_id,
1759: MSC_CL_PULL.v_lrn;
1760:
1761: ELSE --- complete/targeted collections
1762: EXECUTE IMMEDIATE v_sql_stmt
1763: USING MSC_CL_PULL.v_refresh_id,
1764: MSC_CL_PULL.v_instance_id;
1765: END IF;
1766:
1767:
1760:
1761: ELSE --- complete/targeted collections
1762: EXECUTE IMMEDIATE v_sql_stmt
1763: USING MSC_CL_PULL.v_refresh_id,
1764: MSC_CL_PULL.v_instance_id;
1765: END IF;
1766:
1767:
1768: COMMIT;
1770:
1771: PROCEDURE LOAD_OPEN_PAYBACKS IS
1772: BEGIN
1773:
1774: IF MSC_CL_PULL.v_lrn = -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1775:
1776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'PROCEDURE LOAD_OPEN_PAYBACKS');
1777: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPEN_PAYBACKS';
1778: MSC_CL_PULL.v_view_name := 'MRP_AP_OPEN_PAYBACK_QTY_V';
1773:
1774: IF MSC_CL_PULL.v_lrn = -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1775:
1776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'PROCEDURE LOAD_OPEN_PAYBACKS');
1777: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPEN_PAYBACKS';
1778: MSC_CL_PULL.v_view_name := 'MRP_AP_OPEN_PAYBACK_QTY_V';
1779:
1780: v_sql_stmt:=
1781: ' INSERT INTO MSC_ST_OPEN_PAYBACKS(
1774: IF MSC_CL_PULL.v_lrn = -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1775:
1776: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'PROCEDURE LOAD_OPEN_PAYBACKS');
1777: MSC_CL_PULL.v_table_name:= 'MSC_ST_OPEN_PAYBACKS';
1778: MSC_CL_PULL.v_view_name := 'MRP_AP_OPEN_PAYBACK_QTY_V';
1779:
1780: v_sql_stmt:=
1781: ' INSERT INTO MSC_ST_OPEN_PAYBACKS(
1782: SR_INSTANCE_ID,
1803: BORROW_TASK_ID,
1804: PLANNING_GROUP,
1805: LENDING_PROJ_PLANNING_GROUP,
1806: END_ITEM_UNIT_NUMBER
1807: FROM MRP_AP_OPEN_PAYBACK_QTY_V'||MSC_CL_PULL.v_dblink||
1808: ' WHERE ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str;
1809:
1810: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1811: COMMIT;
1806: END_ITEM_UNIT_NUMBER
1807: FROM MRP_AP_OPEN_PAYBACK_QTY_V'||MSC_CL_PULL.v_dblink||
1808: ' WHERE ORGANIZATION_ID '|| MSC_UTIL.v_in_org_str;
1809:
1810: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id;
1811: COMMIT;
1812:
1813: END IF;
1814: