[Home] [Help]
25: BEGIN
26:
27: --=================== Net Change Mode: Delete ==================
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_CHANGES_V';
33:
27: --=================== Net Change Mode: Delete ==================
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_CHANGES_V';
33:
34: v_sql_stmt:=
35: ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
28:
29: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
32: MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_CHANGES_V';
33:
34: v_sql_stmt:=
35: ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
36: ||' ( DEPARTMENT_ID,'
57: ||' x.ACTION_TYPE,'
58: ||' 1,'
59: ||' :v_refresh_id,'
60: ||' :v_instance_id'
61: ||' FROM MRP_AD_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
62: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn;
63:
64: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
65:
58: ||' 1,'
59: ||' :v_refresh_id,'
60: ||' :v_instance_id'
61: ||' FROM MRP_AD_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
62: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn;
63:
64: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
65:
66: COMMIT;
60: ||' :v_instance_id'
61: ||' FROM MRP_AD_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
62: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn;
63:
64: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
65:
66: COMMIT;
67:
68: END IF;
68: END IF;
69:
70:
71:
72: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --when not net collection
73:
74:
75: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_ASSIGNMENTS';
76: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_ASSIGNMENTS_V';
71:
72: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --when not net collection
73:
74:
75: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_ASSIGNMENTS';
76: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_ASSIGNMENTS_V';
77:
78: v_sql_stmt:=
79: ' INSERT INTO MSC_ST_CALENDAR_ASSIGNMENTS'
72: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --when not net collection
73:
74:
75: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_ASSIGNMENTS';
76: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_ASSIGNMENTS_V';
77:
78: v_sql_stmt:=
79: ' INSERT INTO MSC_ST_CALENDAR_ASSIGNMENTS'
80: ||' ( ASSOCIATION_TYPE,'
101: ||' x.PARTNER_TYPE,'
102: ||' decode(x.ASSOCIATION_TYPE,11,1,10,1,9,3,8,3,7,3,6,2,5,2,4,4,3,4,2,4,1,4),'
103: ||' x.SHIP_METHOD_CODE,'
104: ||' :v_refresh_id'
105: ||' FROM MRP_AP_CAL_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x';
106:
107: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
108:
109: COMMIT;
103: ||' x.SHIP_METHOD_CODE,'
104: ||' :v_refresh_id'
105: ||' FROM MRP_AP_CAL_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x';
106:
107: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
108:
109: COMMIT;
110:
111: END IF;
109: COMMIT;
110:
111: END IF;
112:
113: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_DATES';
114: MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_DATES_V';
115: v_sql_stmt:=
116: 'insert into MSC_ST_CALENDAR_DATES'
117: ||' ( CALENDAR_DATE,'
110:
111: END IF;
112:
113: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_DATES';
114: MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_DATES_V';
115: v_sql_stmt:=
116: 'insert into MSC_ST_CALENDAR_DATES'
117: ||' ( CALENDAR_DATE,'
118: ||' CALENDAR_CODE,'
142: ||' x.DESCRIPTION,'
143: ||' 2,'
144: ||' :v_refresh_id,'
145: ||' :v_instance_id'
146: ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
143: ||' 2,'
144: ||' :v_refresh_id,'
145: ||' :v_instance_id'
146: ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151:
144: ||' :v_refresh_id,'
145: ||' :v_instance_id'
146: ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151:
152: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
145: ||' :v_instance_id'
146: ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151:
152: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
153:
146: ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151:
152: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
153:
154: COMMIT;
148: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150: ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151:
152: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
153:
154: COMMIT;
155:
156: MSC_CL_PULL.v_table_name:= 'MSC_ST_PERIOD_START_DATES';
152: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
153:
154: COMMIT;
155:
156: MSC_CL_PULL.v_table_name:= 'MSC_ST_PERIOD_START_DATES';
157: MSC_CL_PULL.v_view_name := 'MRP_AP_PERIOD_START_DATES_V';
158:
159: v_sql_stmt:=
160: ' INSERT INTO MSC_ST_PERIOD_START_DATES'
153:
154: COMMIT;
155:
156: MSC_CL_PULL.v_table_name:= 'MSC_ST_PERIOD_START_DATES';
157: MSC_CL_PULL.v_view_name := 'MRP_AP_PERIOD_START_DATES_V';
158:
159: v_sql_stmt:=
160: ' INSERT INTO MSC_ST_PERIOD_START_DATES'
161: ||' ( CALENDAR_CODE,'
178: ||' x.PRIOR_DATE,'
179: ||' 2,'
180: ||' :v_refresh_id,'
181: ||' :v_instance_id'
182: ||' FROM MRP_AP_PERIOD_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
183: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186:
179: ||' 2,'
180: ||' :v_refresh_id,'
181: ||' :v_instance_id'
182: ||' FROM MRP_AP_PERIOD_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
183: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186:
187: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
180: ||' :v_refresh_id,'
181: ||' :v_instance_id'
182: ||' FROM MRP_AP_PERIOD_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
183: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186:
187: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
188:
181: ||' :v_instance_id'
182: ||' FROM MRP_AP_PERIOD_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
183: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186:
187: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
188:
189: COMMIT;
183: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186:
187: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
188:
189: COMMIT;
190:
191: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_YEAR_START_DATES';
187: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
188:
189: COMMIT;
190:
191: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_YEAR_START_DATES';
192: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_YEAR_START_DATES_V';
193:
194: v_sql_stmt:=
195: ' INSERT INTO MSC_ST_CAL_YEAR_START_DATES'
188:
189: COMMIT;
190:
191: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_YEAR_START_DATES';
192: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_YEAR_START_DATES_V';
193:
194: v_sql_stmt:=
195: ' INSERT INTO MSC_ST_CAL_YEAR_START_DATES'
196: ||' ( CALENDAR_CODE,'
205: ||' x.YEAR_START_DATE,'
206: ||' 2,'
207: ||' :v_refresh_id,'
208: ||' :v_instance_id'
209: ||' FROM MRP_AP_CAL_YEAR_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
210: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213:
206: ||' 2,'
207: ||' :v_refresh_id,'
208: ||' :v_instance_id'
209: ||' FROM MRP_AP_CAL_YEAR_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
210: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213:
214: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
207: ||' :v_refresh_id,'
208: ||' :v_instance_id'
209: ||' FROM MRP_AP_CAL_YEAR_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
210: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213:
214: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
215:
208: ||' :v_instance_id'
209: ||' FROM MRP_AP_CAL_YEAR_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
210: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213:
214: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
215:
216: COMMIT;
210: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213:
214: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
215:
216: COMMIT;
217:
218: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_WEEK_START_DATES';
214: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
215:
216: COMMIT;
217:
218: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_WEEK_START_DATES';
219: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_WEEK_START_DATES_V';
220:
221: v_sql_stmt:=
222: ' INSERT INTO MSC_ST_CAL_WEEK_START_DATES'
215:
216: COMMIT;
217:
218: MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_WEEK_START_DATES';
219: MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_WEEK_START_DATES_V';
220:
221: v_sql_stmt:=
222: ' INSERT INTO MSC_ST_CAL_WEEK_START_DATES'
223: ||' ( CALENDAR_CODE,'
238: ||' x.SEQ_NUM,'
239: ||' 2,'
240: ||' :v_refresh_id,'
241: ||' :v_instance_id'
242: ||' FROM MRP_AP_CAL_WEEK_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
243: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246:
239: ||' 2,'
240: ||' :v_refresh_id,'
241: ||' :v_instance_id'
242: ||' FROM MRP_AP_CAL_WEEK_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
243: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246:
247: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
240: ||' :v_refresh_id,'
241: ||' :v_instance_id'
242: ||' FROM MRP_AP_CAL_WEEK_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
243: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246:
247: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
248:
241: ||' :v_instance_id'
242: ||' FROM MRP_AP_CAL_WEEK_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
243: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246:
247: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
248:
249: COMMIT;
243: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246:
247: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
248:
249: COMMIT;
250:
251: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SHIFTS';
247: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
248:
249: COMMIT;
250:
251: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SHIFTS';
252: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SHIFTS_V';
253:
254: Begin
255:
248:
249: COMMIT;
250:
251: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SHIFTS';
252: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SHIFTS_V';
253:
254: Begin
255:
256:
253:
254: Begin
255:
256:
257: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
258: v_temp_sql:= 'x.capacity_units , ' ;
259: ELSE
260: v_temp_sql:=' NULL, ' ;
261: END IF ;
279: ||' 2,'
280: || v_temp_sql
281: ||' :v_refresh_id,'
282: ||' :v_instance_id'
283: ||' FROM MRP_AP_RESOURCE_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
284: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
285: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
286: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
287:
281: ||' :v_refresh_id,'
282: ||' :v_instance_id'
283: ||' FROM MRP_AP_RESOURCE_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
284: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
285: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
286: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
287:
288: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
289:
282: ||' :v_instance_id'
283: ||' FROM MRP_AP_RESOURCE_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
284: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
285: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
286: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
287:
288: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
289:
290: COMMIT;
284: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
285: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
286: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
287:
288: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
289:
290: COMMIT;
291:
292: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_SHIFTS';
288: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
289:
290: COMMIT;
291:
292: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_SHIFTS';
293: MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_SHIFTS_V';
294:
295: v_sql_stmt:=
296: ' INSERT INTO MSC_ST_CALENDAR_SHIFTS'
289:
290: COMMIT;
291:
292: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_SHIFTS';
293: MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_SHIFTS_V';
294:
295: v_sql_stmt:=
296: ' INSERT INTO MSC_ST_CALENDAR_SHIFTS'
297: ||' ( CALENDAR_CODE,'
310: ||' x.DESCRIPTION,'
311: ||' 2,'
312: ||' :v_refresh_id,'
313: ||' :v_instance_id'
314: ||' FROM MRP_AP_CALENDAR_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
315: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318:
311: ||' 2,'
312: ||' :v_refresh_id,'
313: ||' :v_instance_id'
314: ||' FROM MRP_AP_CALENDAR_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
315: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318:
319: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
312: ||' :v_refresh_id,'
313: ||' :v_instance_id'
314: ||' FROM MRP_AP_CALENDAR_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
315: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318:
319: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
320:
313: ||' :v_instance_id'
314: ||' FROM MRP_AP_CALENDAR_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
315: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318:
319: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
320:
321: COMMIT;
315: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318:
319: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
320:
321: COMMIT;
322:
323: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_DATES';
319: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
320:
321: COMMIT;
322:
323: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_DATES';
324: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_DATES_V';
325:
326: v_sql_stmt:=
327: ' INSERT INTO MSC_ST_SHIFT_DATES'
320:
321: COMMIT;
322:
323: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_DATES';
324: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_DATES_V';
325:
326: v_sql_stmt:=
327: ' INSERT INTO MSC_ST_SHIFT_DATES'
328: ||' ( CALENDAR_CODE,'
349: ||' x.PRIOR_DATE,'
350: ||' 2,'
351: ||' :v_refresh_id,'
352: ||' :v_instance_id'
353: ||' FROM MRP_AP_SHIFT_DATES_V'||MSC_CL_PULL.v_dblink||' x'
354: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357:
350: ||' 2,'
351: ||' :v_refresh_id,'
352: ||' :v_instance_id'
353: ||' FROM MRP_AP_SHIFT_DATES_V'||MSC_CL_PULL.v_dblink||' x'
354: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357:
358: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
351: ||' :v_refresh_id,'
352: ||' :v_instance_id'
353: ||' FROM MRP_AP_SHIFT_DATES_V'||MSC_CL_PULL.v_dblink||' x'
354: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357:
358: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
359:
352: ||' :v_instance_id'
353: ||' FROM MRP_AP_SHIFT_DATES_V'||MSC_CL_PULL.v_dblink||' x'
354: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357:
358: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
359:
360: COMMIT;
354: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357:
358: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
359:
360: COMMIT;
361:
362: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
358: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
359:
360: COMMIT;
361:
362: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
363: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_CHANGES_V';
364:
365: v_sql_stmt:=
366: ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
359:
360: COMMIT;
361:
362: MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
363: MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_CHANGES_V';
364:
365: v_sql_stmt:=
366: ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
367: ||' ( DEPARTMENT_ID,'
390: ||' x.ACTION_TYPE,'
391: ||' 2,'
392: ||' :v_refresh_id,'
393: ||' :v_instance_id'
394: ||' FROM MRP_AP_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
395: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
396: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn;
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
391: ||' 2,'
392: ||' :v_refresh_id,'
393: ||' :v_instance_id'
394: ||' FROM MRP_AP_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
395: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
396: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn;
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399:
392: ||' :v_refresh_id,'
393: ||' :v_instance_id'
394: ||' FROM MRP_AP_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
395: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
396: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn;
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399:
400: COMMIT;
394: ||' FROM MRP_AP_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
395: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
396: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn;
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399:
400: COMMIT;
401:
402: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_TIMES';
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399:
400: COMMIT;
401:
402: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_TIMES';
403: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_TIMES_V';
404:
405: v_sql_stmt:=
406: ' INSERT INTO MSC_ST_SHIFT_TIMES'
399:
400: COMMIT;
401:
402: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_TIMES';
403: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_TIMES_V';
404:
405: v_sql_stmt:=
406: ' INSERT INTO MSC_ST_SHIFT_TIMES'
407: ||' ( CALENDAR_CODE,'
418: ||' x.TO_TIME,'
419: ||' 2,'
420: ||' :v_refresh_id,'
421: ||' :v_instance_id'
422: ||' FROM MRP_AP_SHIFT_TIMES_V'||MSC_CL_PULL.v_dblink||' x'
423: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426:
419: ||' 2,'
420: ||' :v_refresh_id,'
421: ||' :v_instance_id'
422: ||' FROM MRP_AP_SHIFT_TIMES_V'||MSC_CL_PULL.v_dblink||' x'
423: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426:
427: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
420: ||' :v_refresh_id,'
421: ||' :v_instance_id'
422: ||' FROM MRP_AP_SHIFT_TIMES_V'||MSC_CL_PULL.v_dblink||' x'
423: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426:
427: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
428:
421: ||' :v_instance_id'
422: ||' FROM MRP_AP_SHIFT_TIMES_V'||MSC_CL_PULL.v_dblink||' x'
423: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426:
427: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
428:
429: COMMIT;
423: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426:
427: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
428:
429: COMMIT;
430:
431: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_EXCEPTIONS';
427: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
428:
429: COMMIT;
430:
431: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_EXCEPTIONS';
432: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_EXCEPTIONS_V';
433:
434: v_sql_stmt:=
435: ' INSERT INTO MSC_ST_SHIFT_EXCEPTIONS'
428:
429: COMMIT;
430:
431: MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_EXCEPTIONS';
432: MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_EXCEPTIONS_V';
433:
434: v_sql_stmt:=
435: ' INSERT INTO MSC_ST_SHIFT_EXCEPTIONS'
436: ||' ( CALENDAR_CODE,'
449: ||' x.EXCEPTION_TYPE,'
450: ||' 2,'
451: ||' :v_refresh_id,'
452: ||' :v_instance_id'
453: ||' FROM MRP_AP_SHIFT_EXCEPTIONS_V'||MSC_CL_PULL.v_dblink||' x'
454: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457:
450: ||' 2,'
451: ||' :v_refresh_id,'
452: ||' :v_instance_id'
453: ||' FROM MRP_AP_SHIFT_EXCEPTIONS_V'||MSC_CL_PULL.v_dblink||' x'
454: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457:
458: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
451: ||' :v_refresh_id,'
452: ||' :v_instance_id'
453: ||' FROM MRP_AP_SHIFT_EXCEPTIONS_V'||MSC_CL_PULL.v_dblink||' x'
454: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457:
458: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
459:
452: ||' :v_instance_id'
453: ||' FROM MRP_AP_SHIFT_EXCEPTIONS_V'||MSC_CL_PULL.v_dblink||' x'
454: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457:
458: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
459:
460: COMMIT;
454: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457:
458: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
459:
460: COMMIT;
461:
462: -- =============================== NETCHAGE OF DELETE ======================
462: -- =============================== NETCHAGE OF DELETE ======================
463:
464: /* ds change start */
465:
466: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
467: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
468: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
469: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INST_CHANGES_V';
470: v_sql_stmt:=
463:
464: /* ds change start */
465:
466: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
467: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
468: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
469: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INST_CHANGES_V';
470: v_sql_stmt:=
471: ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
464: /* ds change start */
465:
466: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
467: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
468: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
469: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INST_CHANGES_V';
470: v_sql_stmt:=
471: ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
472: ||' ( DEPARTMENT_ID,'
465:
466: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
467: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
468: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
469: MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INST_CHANGES_V';
470: v_sql_stmt:=
471: ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
472: ||' ( DEPARTMENT_ID,'
473: ||' RESOURCE_ID,'
497: ||' x.ACTION_TYPE,'
498: ||' 1,'
499: ||' :v_refresh_id,'
500: ||' :v_instance_id'
501: ||' FROM MRP_AD_RES_INST_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
502: ||' WHERE x.RN >' ||MSC_CL_PULL.v_lrn;
503:
504: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance change sql = '||v_sql_stmt);
505: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
498: ||' 1,'
499: ||' :v_refresh_id,'
500: ||' :v_instance_id'
501: ||' FROM MRP_AD_RES_INST_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
502: ||' WHERE x.RN >' ||MSC_CL_PULL.v_lrn;
503:
504: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance change sql = '||v_sql_stmt);
505: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
506:
501: ||' FROM MRP_AD_RES_INST_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
502: ||' WHERE x.RN >' ||MSC_CL_PULL.v_lrn;
503:
504: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance change sql = '||v_sql_stmt);
505: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
506:
507: COMMIT;
508:
509: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
505: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
506:
507: COMMIT;
508:
509: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
510:
511: IF MSC_CL_PULL.v_lrnn= -1 THEN /* if it i not net change */
512: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
513: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INST_CHANGES_V';
507: COMMIT;
508:
509: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
510:
511: IF MSC_CL_PULL.v_lrnn= -1 THEN /* if it i not net change */
512: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
513: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INST_CHANGES_V';
514:
515: v_sql_stmt:=
508:
509: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
510:
511: IF MSC_CL_PULL.v_lrnn= -1 THEN /* if it i not net change */
512: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
513: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INST_CHANGES_V';
514:
515: v_sql_stmt:=
516: ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
509: END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
510:
511: IF MSC_CL_PULL.v_lrnn= -1 THEN /* if it i not net change */
512: MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
513: MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INST_CHANGES_V';
514:
515: v_sql_stmt:=
516: ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
517: ||' ( DEPARTMENT_ID,'
546: ||' x.ACTION_TYPE,'
547: ||' 2,'
548: ||' :v_refresh_id,'
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_RES_INST_CHANGES_V' ||MSC_CL_PULL.v_dblink ||' x'
551: ||' WHERE x.RN1>' ||MSC_CL_PULL.v_lrn
552: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn;
553:
554: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: res_instance changes sql = '||v_sql_stmt);
547: ||' 2,'
548: ||' :v_refresh_id,'
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_RES_INST_CHANGES_V' ||MSC_CL_PULL.v_dblink ||' x'
551: ||' WHERE x.RN1>' ||MSC_CL_PULL.v_lrn
552: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn;
553:
554: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: res_instance changes sql = '||v_sql_stmt);
555: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
548: ||' :v_refresh_id,'
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_RES_INST_CHANGES_V' ||MSC_CL_PULL.v_dblink ||' x'
551: ||' WHERE x.RN1>' ||MSC_CL_PULL.v_lrn
552: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn;
553:
554: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: res_instance changes sql = '||v_sql_stmt);
555: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
556: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RES_INST_CHANGES_V='|| SQL%ROWCOUNT);
551: ||' WHERE x.RN1>' ||MSC_CL_PULL.v_lrn
552: ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn;
553:
554: --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: res_instance changes sql = '||v_sql_stmt);
555: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
556: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RES_INST_CHANGES_V='|| SQL%ROWCOUNT);
557:
558: COMMIT;
559: END IF; /* MSC_CL_PULL.v_lrnn= -1 */
555: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
556: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RES_INST_CHANGES_V='|| SQL%ROWCOUNT);
557:
558: COMMIT;
559: END IF; /* MSC_CL_PULL.v_lrnn= -1 */
560:
561: END IF; /* MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 */
562:
563: END LOAD_CALENDAR_DATE;
557:
558: COMMIT;
559: END IF; /* MSC_CL_PULL.v_lrnn= -1 */
560:
561: END IF; /* MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 */
562:
563: END LOAD_CALENDAR_DATE;
564:
565:
569:
570: BEGIN
571:
572:
573: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
574: --IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN /* PREPLACE START */
575:
576: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
577: MSC_CL_PULL.v_view_name := 'MRP_AP_BUYER_CONTACTS_V';
570: BEGIN
571:
572:
573: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
574: --IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN /* PREPLACE START */
575:
576: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
577: MSC_CL_PULL.v_view_name := 'MRP_AP_BUYER_CONTACTS_V';
578:
572:
573: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
574: --IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN /* PREPLACE START */
575:
576: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
577: MSC_CL_PULL.v_view_name := 'MRP_AP_BUYER_CONTACTS_V';
578:
579: v_sql_stmt :=
580: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
573: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
574: --IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN /* PREPLACE START */
575:
576: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
577: MSC_CL_PULL.v_view_name := 'MRP_AP_BUYER_CONTACTS_V';
578:
579: v_sql_stmt :=
580: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
581: ||' ( NAME,'
600: ||' ENABLED_FLAG,'
601: ||' 2,'
602: ||' :v_refresh_id,'
603: ||' :v_instance_id'
604: ||' FROM MRP_AP_BUYER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x'
605: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str;
606:
607: EXECUTE IMMEDIATE v_sql_stmt
608: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
604: ||' FROM MRP_AP_BUYER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x'
605: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str;
606:
607: EXECUTE IMMEDIATE v_sql_stmt
608: USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
609:
610: COMMIT;
611:
612:
610: COMMIT;
611:
612:
613: END IF; -- complete refresh
614: --END IF; -- MSC_CL_PULL.ITEM_ENABLED /* PREPLACE END */
615:
616: END LOAD_BUYER_CONTACT;
617:
618:
626:
627: -- select the in INHERIT_OPTION_CLASS_OP_SEQ profile option in source instance
628:
629: BEGIN
630: v_sql_stmt := ' select nvl(FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''BOM:CONFIG_INHERIT_OP_SEQ''),2) '
631: ||' from dual';
632: execute immediate v_sql_stmt into lv_profile_inherit_op_seq;
633: EXCEPTION
634: WHEN OTHERS THEN
641: ELSE
642: v_temp_tp_sql := ' AND x.LAST_UPDATE_DATE > SYSDATE - :v_msc_tp_coll_window';
643: END IF;
644:
645: IF MSC_CL_PULL.VENDOR_ENABLED= MSC_UTIL.SYS_YES THEN
646:
647: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
648: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDORS_V';
649:
643: END IF;
644:
645: IF MSC_CL_PULL.VENDOR_ENABLED= MSC_UTIL.SYS_YES THEN
646:
647: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
648: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDORS_V';
649:
650: v_sql_stmt:=
651: 'insert into MSC_ST_TRADING_PARTNERS'
644:
645: IF MSC_CL_PULL.VENDOR_ENABLED= MSC_UTIL.SYS_YES THEN
646:
647: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
648: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDORS_V';
649:
650: v_sql_stmt:=
651: 'insert into MSC_ST_TRADING_PARTNERS'
652: ||' ( SR_TP_ID,'
665: ||' x.PARTNER_NUMBER,'
666: ||' 2,'
667: ||' :v_refresh_id,'
668: ||' :v_instance_id'
669: ||' from MRP_AP_VENDORS_V'||MSC_CL_PULL.v_dblink||' x'
670: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
671: ||v_temp_tp_sql;
672:
673: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
666: ||' 2,'
667: ||' :v_refresh_id,'
668: ||' :v_instance_id'
669: ||' from MRP_AP_VENDORS_V'||MSC_CL_PULL.v_dblink||' x'
670: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
671: ||v_temp_tp_sql;
672:
673: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
674: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
670: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
671: ||v_temp_tp_sql;
672:
673: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
674: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
675: ELSE
676: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
677: END IF;
678:
672:
673: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
674: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
675: ELSE
676: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
677: END IF;
678:
679: COMMIT;
680:
677: END IF;
678:
679: COMMIT;
680:
681: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
682: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_SITES_V';
683:
684: /* for bug: 2459612, added code to collect OPERATING_UNIT from Vendor sites for 11i source instance */
685: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
678:
679: COMMIT;
680:
681: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
682: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_SITES_V';
683:
684: /* for bug: 2459612, added code to collect OPERATING_UNIT from Vendor sites for 11i source instance */
685: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
686: v_temp_sql := ' x.OPERATING_UNIT,x.shipping_control, ';
681: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
682: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_SITES_V';
683:
684: /* for bug: 2459612, added code to collect OPERATING_UNIT from Vendor sites for 11i source instance */
685: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
686: v_temp_sql := ' x.OPERATING_UNIT,x.shipping_control, ';
687: ELSE
688: v_temp_sql := ' NULL,NULL,';
689: END IF;
740: ||' x.PROVINCE,'
741: ||' x.COUNTRY,'
742: /* SCE Change Ends */
743: ||' :v_instance_id'
744: ||' from MRP_AP_VENDOR_SITES_V'||MSC_CL_PULL.v_dblink||' x'
745: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
746: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')'
747: ||v_temp_tp_sql;
748:
741: ||' x.COUNTRY,'
742: /* SCE Change Ends */
743: ||' :v_instance_id'
744: ||' from MRP_AP_VENDOR_SITES_V'||MSC_CL_PULL.v_dblink||' x'
745: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
746: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')'
747: ||v_temp_tp_sql;
748:
749: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
742: /* SCE Change Ends */
743: ||' :v_instance_id'
744: ||' from MRP_AP_VENDOR_SITES_V'||MSC_CL_PULL.v_dblink||' x'
745: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
746: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')'
747: ||v_temp_tp_sql;
748:
749: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
750: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
746: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')'
747: ||v_temp_tp_sql;
748:
749: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
750: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
751: ELSE
752: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
753: END IF;
754:
748:
749: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
750: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
751: ELSE
752: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
753: END IF;
754:
755: COMMIT;
756:
753: END IF;
754:
755: COMMIT;
756:
757: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
758:
759: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
760: v_temp_sql := 'x.ORGANIZATION_ID, ';
761: ELSE
755: COMMIT;
756:
757: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
758:
759: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
760: v_temp_sql := 'x.ORGANIZATION_ID, ';
761: ELSE
762: v_temp_sql := ' NULL, ';
763: END IF;
761: ELSE
762: v_temp_sql := ' NULL, ';
763: END IF;
764:
765: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
766: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
767:
768: v_sql_stmt:=
769: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
762: v_temp_sql := ' NULL, ';
763: END IF;
764:
765: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
766: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
767:
768: v_sql_stmt:=
769: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
770: ||' ( LOCATION_ID,'
783: ||' 1,'
784: || v_temp_sql
785: ||' :v_refresh_id,'
786: ||' :v_instance_id'
787: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
788: ||' WHERE x.PARTNER_TYPE= 1';
789:
790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
791:
786: ||' :v_instance_id'
787: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
788: ||' WHERE x.PARTNER_TYPE= 1';
789:
790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
791:
792: COMMIT;
793:
794: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
791:
792: COMMIT;
793:
794: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
795: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_CONTACTS_V';
796:
797: v_sql_stmt:=
798: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
791:
792: COMMIT;
793:
794: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
795: MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_CONTACTS_V';
796:
797: v_sql_stmt:=
798: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
799: ||' ( NAME,'
818: ||' ENABLED_FLAG,'
819: ||' 2,'
820: ||' :v_refresh_id,'
821: ||' :v_instance_id'
822: ||' FROM MRP_AP_VENDOR_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
823:
824: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
825:
826: COMMIT;
820: ||' :v_refresh_id,'
821: ||' :v_instance_id'
822: ||' FROM MRP_AP_VENDOR_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
823:
824: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
825:
826: COMMIT;
827:
828: END IF; -- complete refresh
826: COMMIT;
827:
828: END IF; -- complete refresh
829:
830: END IF; -- MSC_CL_PULL.VENDOR_ENABLED
831:
832:
833: IF MSC_CL_PULL.CUSTOMER_ENABLED= MSC_UTIL.SYS_YES THEN
834:
829:
830: END IF; -- MSC_CL_PULL.VENDOR_ENABLED
831:
832:
833: IF MSC_CL_PULL.CUSTOMER_ENABLED= MSC_UTIL.SYS_YES THEN
834:
835: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
836: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMERS_V';
837:
831:
832:
833: IF MSC_CL_PULL.CUSTOMER_ENABLED= MSC_UTIL.SYS_YES THEN
834:
835: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
836: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMERS_V';
837:
838: BEGIN
839:
832:
833: IF MSC_CL_PULL.CUSTOMER_ENABLED= MSC_UTIL.SYS_YES THEN
834:
835: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
836: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMERS_V';
837:
838: BEGIN
839:
840: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
836: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMERS_V';
837:
838: BEGIN
839:
840: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
841: v_temp_sql2 := ' x.AGGREGATE_DEMAND_FLAG, ';
842: ELSE
843: v_temp_sql2 := ' NULL,';
844: END IF;
841: v_temp_sql2 := ' x.AGGREGATE_DEMAND_FLAG, ';
842: ELSE
843: v_temp_sql2 := ' NULL,';
844: END IF;
845: IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
846: v_temp_sql1 := ' NULL,';
847: ELSE
848: v_temp_sql1 := ' x.CUSTOMER_CLASS_CODE,';
849: END IF;
854: ELSE
855: v_temp_sql2 := v_temp_sql2||' NULL,';
856: END IF;
857:
858: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
859: v_temp_sql2 := v_temp_sql2 || ' x.customer_type, x.CUST_ACCOUNT_NUMBER, ';
860: ELSE
861: v_temp_sql2 := v_temp_sql2 || ' NULL, NULL, ';
862: END IF;
888: || v_temp_sql2
889: ||' 2,'
890: ||' :v_refresh_id,'
891: ||' :v_instance_id'
892: ||' from MRP_AP_CUSTOMERS_V'||MSC_CL_PULL.v_dblink||' x'
893: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
894: ||v_temp_tp_sql;
895:
896: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
889: ||' 2,'
890: ||' :v_refresh_id,'
891: ||' :v_instance_id'
892: ||' from MRP_AP_CUSTOMERS_V'||MSC_CL_PULL.v_dblink||' x'
893: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
894: ||v_temp_tp_sql;
895:
896: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
897: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
893: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
894: ||v_temp_tp_sql;
895:
896: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
897: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
898: ELSE
899: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
900: END IF;
901:
895:
896: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
897: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
898: ELSE
899: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
900: END IF;
901:
902: COMMIT;
903:
900: END IF;
901:
902: COMMIT;
903:
904: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
905: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_SITES_V';
906:
907: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
908: v_temp_sql := ' x.POSTAL_CODE, x.CITY, x.STATE, x.COUNTRY,x.LOCATION_ID,x.SHIPPING_CONTROL, ';
901:
902: COMMIT;
903:
904: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
905: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_SITES_V';
906:
907: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
908: v_temp_sql := ' x.POSTAL_CODE, x.CITY, x.STATE, x.COUNTRY,x.LOCATION_ID,x.SHIPPING_CONTROL, ';
909: ELSE
903:
904: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
905: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_SITES_V';
906:
907: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
908: v_temp_sql := ' x.POSTAL_CODE, x.CITY, x.STATE, x.COUNTRY,x.LOCATION_ID,x.SHIPPING_CONTROL, ';
909: ELSE
910: v_temp_sql := ' NULL,NULL,NULL,NULL,NULL,NULL, ';
911: END IF;
962: ||' x.COUNTY,'
963: ||' substr(x.PARTNER_SITE_NUMBER,1,30), '
964: /* SCE Change Ends */
965: ||' :v_instance_id'
966: ||' from MRP_AP_CUSTOMER_SITES_V'||MSC_CL_PULL.v_dblink||' x'
967: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
968: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
969: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970: ||v_temp_tp_sql;
963: ||' substr(x.PARTNER_SITE_NUMBER,1,30), '
964: /* SCE Change Ends */
965: ||' :v_instance_id'
966: ||' from MRP_AP_CUSTOMER_SITES_V'||MSC_CL_PULL.v_dblink||' x'
967: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
968: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
969: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970: ||v_temp_tp_sql;
971:
964: /* SCE Change Ends */
965: ||' :v_instance_id'
966: ||' from MRP_AP_CUSTOMER_SITES_V'||MSC_CL_PULL.v_dblink||' x'
967: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
968: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
969: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970: ||v_temp_tp_sql;
971:
972: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
965: ||' :v_instance_id'
966: ||' from MRP_AP_CUSTOMER_SITES_V'||MSC_CL_PULL.v_dblink||' x'
967: ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
968: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
969: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970: ||v_temp_tp_sql;
971:
972: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
973: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
969: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970: ||v_temp_tp_sql;
971:
972: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
973: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
974: ELSE
975: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
976: END IF;
977:
971:
972: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
973: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
974: ELSE
975: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
976: END IF;
977:
978: COMMIT;
979:
976: END IF;
977:
978: COMMIT;
979:
980: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
981:
982: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
983: v_temp_sql := 'x.ORGANIZATION_ID, ';
984: ELSE
978: COMMIT;
979:
980: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
981:
982: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
983: v_temp_sql := 'x.ORGANIZATION_ID, ';
984: ELSE
985: v_temp_sql := ' NULL, ';
986: END IF;
984: ELSE
985: v_temp_sql := ' NULL, ';
986: END IF;
987:
988: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
989: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
990:
991: v_sql_stmt:=
992: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
985: v_temp_sql := ' NULL, ';
986: END IF;
987:
988: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
989: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
990:
991: v_sql_stmt:=
992: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
993: ||' ( LOCATION_ID,'
1006: ||' 2,'
1007: || v_temp_sql
1008: ||' :v_refresh_id,'
1009: ||' :v_instance_id'
1010: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1011: ||' WHERE x.PARTNER_TYPE= 2';
1012:
1013: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1014:
1009: ||' :v_instance_id'
1010: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1011: ||' WHERE x.PARTNER_TYPE= 2';
1012:
1013: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1014:
1015: COMMIT;
1016:
1017: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
1013: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1014:
1015: COMMIT;
1016:
1017: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
1018: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_CONTACTS_V';
1019:
1020: v_sql_stmt:=
1021: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
1014:
1015: COMMIT;
1016:
1017: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
1018: MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_CONTACTS_V';
1019:
1020: v_sql_stmt:=
1021: 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
1022: ||' ( NAME,'
1041: ||' ENABLED_FLAG,'
1042: ||' 2,'
1043: ||' :v_refresh_id,'
1044: ||' :v_instance_id'
1045: ||' FROM MRP_AP_CUSTOMER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
1046:
1047: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1048:
1049: COMMIT;
1043: ||' :v_refresh_id,'
1044: ||' :v_instance_id'
1045: ||' FROM MRP_AP_CUSTOMER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
1046:
1047: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1048:
1049: COMMIT;
1050:
1051: END IF; -- complete refresh
1051: END IF; -- complete refresh
1052:
1053: END IF; -- CUSTOMER ENABLED
1054:
1055: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
1056: MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATIONS_V';
1057:
1058: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1059: v_temp_sql:= 'x.BUSINESS_GROUP_ID,x.LEGAL_ENTITY, x.SET_OF_BOOKS_ID, x.CHART_OF_ACCOUNTS_ID, x.BUSINESS_GROUP_NAME,x.LEGAL_ENTITY_NAME, x.OPERATING_UNIT_NAME, ';
1052:
1053: END IF; -- CUSTOMER ENABLED
1054:
1055: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
1056: MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATIONS_V';
1057:
1058: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1059: v_temp_sql:= 'x.BUSINESS_GROUP_ID,x.LEGAL_ENTITY, x.SET_OF_BOOKS_ID, x.CHART_OF_ACCOUNTS_ID, x.BUSINESS_GROUP_NAME,x.LEGAL_ENTITY_NAME, x.OPERATING_UNIT_NAME, ';
1060: ELSE
1054:
1055: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
1056: MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATIONS_V';
1057:
1058: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1059: v_temp_sql:= 'x.BUSINESS_GROUP_ID,x.LEGAL_ENTITY, x.SET_OF_BOOKS_ID, x.CHART_OF_ACCOUNTS_ID, x.BUSINESS_GROUP_NAME,x.LEGAL_ENTITY_NAME, x.OPERATING_UNIT_NAME, ';
1060: ELSE
1061: v_temp_sql := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1062: END IF;
1061: v_temp_sql := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1062: END IF;
1063:
1064:
1065: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
1066: v_temp_sql5 := 'x.SUBCONTRACTING_SOURCE_ORG, ';
1067: ELSE
1068: v_temp_sql5 := 'NULL,';
1069: END IF;
1210: ||' 2,'
1211: ||' :v_refresh_id,'
1212: ||' :v_instance_id,'
1213: ||' :lv_profile_inherit_op_seq'
1214: ||' from MRP_AP_ORGANIZATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1215: ||' where NVL( x.LANGUAGE, :v_lang)= :v_lang'
1216: ||' AND x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1217: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1213: ||' :lv_profile_inherit_op_seq'
1214: ||' from MRP_AP_ORGANIZATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1215: ||' where NVL( x.LANGUAGE, :v_lang)= :v_lang'
1216: ||' AND x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1217: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1214: ||' from MRP_AP_ORGANIZATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1215: ||' where NVL( x.LANGUAGE, :v_lang)= :v_lang'
1216: ||' AND x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1217: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1215: ||' where NVL( x.LANGUAGE, :v_lang)= :v_lang'
1216: ||' AND x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1217: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1217: ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1218: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1219: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1220:
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1221: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1222: MSC_CL_PULL.V_ICODE,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1223: MSC_CL_PULL.V_ICODE,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1231: MSC_CL_PULL.v_lang,
1224: MSC_CL_PULL.v_mso_org_dmd_penalty,
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1231: MSC_CL_PULL.v_lang,
1232: MSC_CL_PULL.v_lang;
1225: MSC_CL_PULL.v_mso_org_item_penalty,
1226: MSC_CL_PULL.v_mso_org_res_penalty,
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1231: MSC_CL_PULL.v_lang,
1232: MSC_CL_PULL.v_lang;
1233:
1227: MSC_CL_PULL.v_mso_org_trsp_penalty,
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1231: MSC_CL_PULL.v_lang,
1232: MSC_CL_PULL.v_lang;
1233:
1234: COMMIT;
1235:
1228: MSC_CL_PULL.v_refresh_id,
1229: MSC_CL_PULL.v_instance_id,
1230: lv_profile_inherit_op_seq,
1231: MSC_CL_PULL.v_lang,
1232: MSC_CL_PULL.v_lang;
1233:
1234: COMMIT;
1235:
1236: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
1232: MSC_CL_PULL.v_lang;
1233:
1234: COMMIT;
1235:
1236: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
1237: MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATION_SITES_V';
1238:
1239: /* For bug: 2564735 , added substr for 60 chars on the columns COUNTY and STATE becos these columns
1240: are of higher size in the views */
1233:
1234: COMMIT;
1235:
1236: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
1237: MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATION_SITES_V';
1238:
1239: /* For bug: 2564735 , added substr for 60 chars on the columns COUNTY and STATE becos these columns
1240: are of higher size in the views */
1241: v_sql_stmt:=
1280: ||' x.PARTNER_TYPE,'
1281: ||' 2,'
1282: ||' :v_refresh_id,'
1283: ||' :v_instance_id'
1284: ||' from MRP_AP_ORGANIZATION_SITES_V'||MSC_CL_PULL.v_dblink||' x'
1285: ||' WHERE x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1286: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1282: ||' :v_refresh_id,'
1283: ||' :v_instance_id'
1284: ||' from MRP_AP_ORGANIZATION_SITES_V'||MSC_CL_PULL.v_dblink||' x'
1285: ||' WHERE x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1286: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1289:
1290: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1283: ||' :v_instance_id'
1284: ||' from MRP_AP_ORGANIZATION_SITES_V'||MSC_CL_PULL.v_dblink||' x'
1285: ||' WHERE x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1286: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1289:
1290: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1291:
1284: ||' from MRP_AP_ORGANIZATION_SITES_V'||MSC_CL_PULL.v_dblink||' x'
1285: ||' WHERE x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1286: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1289:
1290: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1291:
1292: COMMIT;
1286: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1289:
1290: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1291:
1292: COMMIT;
1293:
1294: /* added this fix for bug # 2198339 to collect location associations for the Orgs */
1291:
1292: COMMIT;
1293:
1294: /* added this fix for bug # 2198339 to collect location associations for the Orgs */
1295: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete,targeted refresh
1296:
1297: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1298: v_temp_sql := 'x.ORGANIZATION_ID, ';
1299: ELSE
1293:
1294: /* added this fix for bug # 2198339 to collect location associations for the Orgs */
1295: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete,targeted refresh
1296:
1297: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1298: v_temp_sql := 'x.ORGANIZATION_ID, ';
1299: ELSE
1300: v_temp_sql := ' NULL, ';
1301: END IF;
1299: ELSE
1300: v_temp_sql := ' NULL, ';
1301: END IF;
1302:
1303: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
1304: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
1305:
1306: v_sql_stmt:=
1307: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
1300: v_temp_sql := ' NULL, ';
1301: END IF;
1302:
1303: MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
1304: MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
1305:
1306: v_sql_stmt:=
1307: 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
1308: ||' ( LOCATION_ID,'
1321: ||' 3,'
1322: || v_temp_sql
1323: ||' :v_refresh_id,'
1324: ||' :v_instance_id'
1325: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1326: ||' WHERE x.PARTNER_TYPE= 3';
1327:
1328: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1329:
1324: ||' :v_instance_id'
1325: ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1326: ||' WHERE x.PARTNER_TYPE= 3';
1327:
1328: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1329:
1330: END IF; -- complete refresh
1331:
1332: COMMIT;
1338:
1339: PROCEDURE LOAD_PARAMETER IS
1340: BEGIN
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARAMETERS';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_PARAMETERS_V';
1346:
1340: BEGIN
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARAMETERS';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_PARAMETERS_V';
1346:
1347: v_sql_stmt:=
1348: ' insert into MSC_ST_PARAMETERS'
1341:
1342: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343:
1344: MSC_CL_PULL.v_table_name:= 'MSC_ST_PARAMETERS';
1345: MSC_CL_PULL.v_view_name := 'MRP_AP_PARAMETERS_V';
1346:
1347: v_sql_stmt:=
1348: ' insert into MSC_ST_PARAMETERS'
1349: ||' ( ORGANIZATION_ID,'
1396: ||' x.REPETITIVE_ANCHOR_DATE,'
1397: ||' 2,'
1398: ||' :v_refresh_id,'
1399: ||' :v_instance_id'
1400: ||' from MRP_AP_PARAMETERS_V'||MSC_CL_PULL.v_dblink||' x'
1401: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1402:
1403: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1404:
1399: ||' :v_instance_id'
1400: ||' from MRP_AP_PARAMETERS_V'||MSC_CL_PULL.v_dblink||' x'
1401: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1402:
1403: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1404:
1405: COMMIT;
1406:
1407: END IF;
1412:
1413: PROCEDURE LOAD_UOM IS
1414: BEGIN
1415:
1416: IF MSC_CL_PULL.UOM_ENABLED= MSC_UTIL.SYS_YES THEN
1417:
1418: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNITS_OF_MEASURE';
1419: MSC_CL_PULL.v_view_name := 'MRP_AP_UNITS_OF_MEASURE_V';
1420:
1414: BEGIN
1415:
1416: IF MSC_CL_PULL.UOM_ENABLED= MSC_UTIL.SYS_YES THEN
1417:
1418: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNITS_OF_MEASURE';
1419: MSC_CL_PULL.v_view_name := 'MRP_AP_UNITS_OF_MEASURE_V';
1420:
1421: v_sql_stmt:=
1422: ' insert into MSC_ST_UNITS_OF_MEASURE'
1415:
1416: IF MSC_CL_PULL.UOM_ENABLED= MSC_UTIL.SYS_YES THEN
1417:
1418: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNITS_OF_MEASURE';
1419: MSC_CL_PULL.v_view_name := 'MRP_AP_UNITS_OF_MEASURE_V';
1420:
1421: v_sql_stmt:=
1422: ' insert into MSC_ST_UNITS_OF_MEASURE'
1423: ||' ( UNIT_OF_MEASURE,'
1438: ||' x.DESCRIPTION,'
1439: ||' 2,'
1440: ||' :v_refresh_id,'
1441: ||' :v_instance_id'
1442: ||' from MRP_AP_UNITS_OF_MEASURE_V'||MSC_CL_PULL.v_dblink||' x'
1443: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
1444: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
1445:
1446: 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_lang, MSC_CL_PULL.v_lang;
1440: ||' :v_refresh_id,'
1441: ||' :v_instance_id'
1442: ||' from MRP_AP_UNITS_OF_MEASURE_V'||MSC_CL_PULL.v_dblink||' x'
1443: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
1444: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
1445:
1446: 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_lang, MSC_CL_PULL.v_lang;
1447:
1448: /* Removed the forked code for 11i source as the column LANGUAGE is again added in
1442: ||' from MRP_AP_UNITS_OF_MEASURE_V'||MSC_CL_PULL.v_dblink||' x'
1443: ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
1444: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
1445:
1446: 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_lang, MSC_CL_PULL.v_lang;
1447:
1448: /* Removed the forked code for 11i source as the column LANGUAGE is again added in
1449: the view mtl_units_of_measures_vl by the Oracle Inventory team
1450: */
1450: */
1451:
1452: COMMIT;
1453:
1454: MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CLASS_CONVERSIONS';
1455: MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CLASS_CONVERSIONS_V';
1456:
1457: v_sql_stmt:=
1458: ' insert into MSC_ST_UOM_CLASS_CONVERSIONS'
1451:
1452: COMMIT;
1453:
1454: MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CLASS_CONVERSIONS';
1455: MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CLASS_CONVERSIONS_V';
1456:
1457: v_sql_stmt:=
1458: ' insert into MSC_ST_UOM_CLASS_CONVERSIONS'
1459: ||' ( INVENTORY_ITEM_ID,'
1480: ||' x.DISABLE_DATE- :v_dgmt,'
1481: ||' 2,'
1482: ||' :v_refresh_id,'
1483: ||' :v_instance_id'
1484: ||' from MRP_AP_UOM_CLASS_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1485: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488:
1481: ||' 2,'
1482: ||' :v_refresh_id,'
1483: ||' :v_instance_id'
1484: ||' from MRP_AP_UOM_CLASS_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1485: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488:
1489: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1482: ||' :v_refresh_id,'
1483: ||' :v_instance_id'
1484: ||' from MRP_AP_UOM_CLASS_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1485: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488:
1489: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1490:
1483: ||' :v_instance_id'
1484: ||' from MRP_AP_UOM_CLASS_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1485: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488:
1489: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1490:
1491: COMMIT;
1485: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488:
1489: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1490:
1491: COMMIT;
1492:
1493: MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CONVERSIONS';
1489: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1490:
1491: COMMIT;
1492:
1493: MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CONVERSIONS';
1494: MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CONVERSIONS_V';
1495:
1496: v_sql_stmt:=
1497: ' insert into MSC_ST_UOM_CONVERSIONS'
1490:
1491: COMMIT;
1492:
1493: MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CONVERSIONS';
1494: MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CONVERSIONS_V';
1495:
1496: v_sql_stmt:=
1497: ' insert into MSC_ST_UOM_CONVERSIONS'
1498: ||' ( UNIT_OF_MEASURE,'
1515: ||' DISABLE_DATE,'
1516: ||' 2,'
1517: ||' :v_refresh_id,'
1518: ||' :v_instance_id'
1519: ||' from MRP_AP_UOM_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1520: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523:
1516: ||' 2,'
1517: ||' :v_refresh_id,'
1518: ||' :v_instance_id'
1519: ||' from MRP_AP_UOM_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1520: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523:
1524: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1517: ||' :v_refresh_id,'
1518: ||' :v_instance_id'
1519: ||' from MRP_AP_UOM_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1520: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523:
1524: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1525:
1518: ||' :v_instance_id'
1519: ||' from MRP_AP_UOM_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1520: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523:
1524: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1525:
1526: COMMIT;
1520: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523:
1524: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1525:
1526: COMMIT;
1527:
1528: END IF;