[Home] [Help]
18:
19: PROCEDURE LOAD_SAFETY_STOCK IS
20: BEGIN
21:
22: IF MSC_CL_PULL.SS_ENABLED= MSC_UTIL.SYS_YES THEN
23:
24: /* Added this piece of code for Safety stocks by Project/Task */
25: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
26: v_temp_sql := 'x.PROJECT_ID,x.TASK_ID,x.PLANNING_GROUP,';
21:
22: IF MSC_CL_PULL.SS_ENABLED= MSC_UTIL.SYS_YES THEN
23:
24: /* Added this piece of code for Safety stocks by Project/Task */
25: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
26: v_temp_sql := 'x.PROJECT_ID,x.TASK_ID,x.PLANNING_GROUP,';
27: ELSE
28: v_temp_sql := ' NULL ,NULL,NULL ,';
29: END IF;
27: ELSE
28: v_temp_sql := ' NULL ,NULL,NULL ,';
29: END IF;
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_SAFETY_STOCKS';
32: MSC_CL_PULL.v_view_name := 'MRP_AP_SAFETY_STOCKS_V';
33:
34: v_sql_stmt:=
35: ' insert into MSC_ST_SAFETY_STOCKS'
28: v_temp_sql := ' NULL ,NULL,NULL ,';
29: END IF;
30:
31: MSC_CL_PULL.v_table_name:= 'MSC_ST_SAFETY_STOCKS';
32: MSC_CL_PULL.v_view_name := 'MRP_AP_SAFETY_STOCKS_V';
33:
34: v_sql_stmt:=
35: ' insert into MSC_ST_SAFETY_STOCKS'
36: ||' ( INVENTORY_ITEM_ID,'
51: || v_temp_sql
52: ||' 2,'
53: ||' :v_refresh_id,'
54: ||' :v_instance_id'
55: ||' from MRP_AP_SAFETY_STOCKS_V'||MSC_CL_PULL.v_dblink||' x'
56: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
57: ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
58: /*
59: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
53: ||' :v_refresh_id,'
54: ||' :v_instance_id'
55: ||' from MRP_AP_SAFETY_STOCKS_V'||MSC_CL_PULL.v_dblink||' x'
56: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
57: ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
58: /*
59: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
60: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
61: */
55: ||' from MRP_AP_SAFETY_STOCKS_V'||MSC_CL_PULL.v_dblink||' x'
56: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
57: ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
58: /*
59: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
60: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
61: */
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
56: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
57: ||' AND (x.RN2>'||MSC_CL_PULL.v_lrn||')';
58: /*
59: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
60: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
61: */
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
64:
59: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
60: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
61: */
62:
63: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
64:
65: COMMIT;
66:
67: END IF;
72:
73: PROCEDURE LOAD_SCHEDULE IS
74: BEGIN
75:
76: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
77: MSC_CL_PULL.v_view_name := 'MRP_AP_DESIGNATORS_V';
78:
79: /* Bug 2634435 */
80:
73: PROCEDURE LOAD_SCHEDULE IS
74: BEGIN
75:
76: MSC_CL_PULL.v_table_name:= 'MSC_ST_DESIGNATORS';
77: MSC_CL_PULL.v_view_name := 'MRP_AP_DESIGNATORS_V';
78:
79: /* Bug 2634435 */
80:
81: IF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MDS) THEN
77: MSC_CL_PULL.v_view_name := 'MRP_AP_DESIGNATORS_V';
78:
79: /* Bug 2634435 */
80:
81: IF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MDS) THEN
82: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 1 ';
83: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MPS) THEN
84: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 2 ';
85: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_BOTH) THEN
79: /* Bug 2634435 */
80:
81: IF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MDS) THEN
82: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 1 ';
83: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MPS) THEN
84: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 2 ';
85: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_BOTH) THEN
86: v_schedule_sql := ' ';
87: END IF;
81: IF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MDS) THEN
82: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 1 ';
83: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_MPS) THEN
84: v_schedule_sql := ' AND x.SCHEDULE_TYPE = 2 ';
85: ELSIF (MSC_CL_PULL.v_schedule_flag = MSC_UTIL.G_BOTH) THEN
86: v_schedule_sql := ' ';
87: END IF;
88:
89: v_sql_stmt:=
115: ||' x.SCHEDULE_TYPE,'
116: ||' 2,'
117: ||' :v_refresh_id,'
118: ||' :v_instance_id'
119: ||' from MRP_AP_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x'
120: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str||v_schedule_sql;
121:
122: --||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
123:
118: ||' :v_instance_id'
119: ||' from MRP_AP_DESIGNATORS_V'||MSC_CL_PULL.v_dblink||' x'
120: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str||v_schedule_sql;
121:
122: --||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
123:
124: --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;
125: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
126:
120: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str||v_schedule_sql;
121:
122: --||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
123:
124: --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;
125: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
126:
127: COMMIT;
128:
121:
122: --||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
123:
124: --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;
125: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
126:
127: COMMIT;
128:
129: END LOAD_SCHEDULE;
132:
133: PROCEDURE LOAD_SOURCING IS
134: BEGIN
135:
136: IF MSC_CL_PULL.SOURCING_ENABLED= MSC_UTIL.SYS_YES THEN
137:
138: MSC_CL_PULL.v_table_name:= 'MSC_ST_ASSIGNMENT_SETS';
139: MSC_CL_PULL.v_view_name := 'MRP_AP_ASSIGNMENT_SETS_V';
140:
134: BEGIN
135:
136: IF MSC_CL_PULL.SOURCING_ENABLED= MSC_UTIL.SYS_YES THEN
137:
138: MSC_CL_PULL.v_table_name:= 'MSC_ST_ASSIGNMENT_SETS';
139: MSC_CL_PULL.v_view_name := 'MRP_AP_ASSIGNMENT_SETS_V';
140:
141: v_sql_stmt:=
142: ' insert into MSC_ST_ASSIGNMENT_SETS'
135:
136: IF MSC_CL_PULL.SOURCING_ENABLED= MSC_UTIL.SYS_YES THEN
137:
138: MSC_CL_PULL.v_table_name:= 'MSC_ST_ASSIGNMENT_SETS';
139: MSC_CL_PULL.v_view_name := 'MRP_AP_ASSIGNMENT_SETS_V';
140:
141: v_sql_stmt:=
142: ' insert into MSC_ST_ASSIGNMENT_SETS'
143: ||' ( SR_ASSIGNMENT_SET_ID,'
152: ||' x.DESCRIPTION,'
153: ||' 2,'
154: ||' :v_refresh_id,'
155: ||' :v_instance_id'
156: ||' from MRP_AP_ASSIGNMENT_SETS_V'||MSC_CL_PULL.v_dblink||' x'
157: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn;
158:
159: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
160:
153: ||' 2,'
154: ||' :v_refresh_id,'
155: ||' :v_instance_id'
156: ||' from MRP_AP_ASSIGNMENT_SETS_V'||MSC_CL_PULL.v_dblink||' x'
157: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn;
158:
159: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
160:
161: COMMIT;
155: ||' :v_instance_id'
156: ||' from MRP_AP_ASSIGNMENT_SETS_V'||MSC_CL_PULL.v_dblink||' x'
157: ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn;
158:
159: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
160:
161: COMMIT;
162:
163: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_ASSIGNMENTS';
159: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
160:
161: COMMIT;
162:
163: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_ASSIGNMENTS';
164: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_ASSIGNMENTS_V';
165:
166: v_sql_stmt:=
167: 'insert into MSC_ST_SR_ASSIGNMENTS'
160:
161: COMMIT;
162:
163: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_ASSIGNMENTS';
164: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_ASSIGNMENTS_V';
165:
166: v_sql_stmt:=
167: 'insert into MSC_ST_SR_ASSIGNMENTS'
168: ||' ( SR_ASSIGNMENT_ID,'
195: ||' x.Category_Set_ID,'
196: ||' 2,'
197: ||' :v_refresh_id,'
198: ||' :v_instance_id'
199: ||' from MRP_AP_SR_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x'
200: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
201: ||' OR x.ORGANIZATION_ID IS NULL OR decode(x.assignment_type,3,x.organization_id,-1)=x.organization_id) '
202: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
203: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
198: ||' :v_instance_id'
199: ||' from MRP_AP_SR_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x'
200: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
201: ||' OR x.ORGANIZATION_ID IS NULL OR decode(x.assignment_type,3,x.organization_id,-1)=x.organization_id) '
202: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
203: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
204:
205: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
206:
199: ||' from MRP_AP_SR_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x'
200: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
201: ||' OR x.ORGANIZATION_ID IS NULL OR decode(x.assignment_type,3,x.organization_id,-1)=x.organization_id) '
202: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
203: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
204:
205: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
206:
207: COMMIT;
201: ||' OR x.ORGANIZATION_ID IS NULL OR decode(x.assignment_type,3,x.organization_id,-1)=x.organization_id) '
202: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
203: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
204:
205: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
206:
207: COMMIT;
208:
209: MSC_CL_PULL.v_table_name:= 'MSC_ST_SOURCING_RULES';
205: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
206:
207: COMMIT;
208:
209: MSC_CL_PULL.v_table_name:= 'MSC_ST_SOURCING_RULES';
210: MSC_CL_PULL.v_view_name := 'MRP_AP_SOURCING_RULES_V';
211:
212: v_sql_stmt:=
213: 'insert into MSC_ST_SOURCING_RULES'
206:
207: COMMIT;
208:
209: MSC_CL_PULL.v_table_name:= 'MSC_ST_SOURCING_RULES';
210: MSC_CL_PULL.v_view_name := 'MRP_AP_SOURCING_RULES_V';
211:
212: v_sql_stmt:=
213: 'insert into MSC_ST_SOURCING_RULES'
214: ||' ( SR_SOURCING_RULE_ID,'
231: ||' x.PLANNING_ACTIVE,'
232: ||' 2,'
233: ||' :v_refresh_id,'
234: ||' :v_instance_id'
235: ||' from MRP_AP_SOURCING_RULES_V'||MSC_CL_PULL.v_dblink||' x'
236: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
237: ||' OR x.ORGANIZATION_ID IS NULL)'
238: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
239:
234: ||' :v_instance_id'
235: ||' from MRP_AP_SOURCING_RULES_V'||MSC_CL_PULL.v_dblink||' x'
236: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
237: ||' OR x.ORGANIZATION_ID IS NULL)'
238: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
239:
240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
241:
242: COMMIT;
236: ||' WHERE (x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
237: ||' OR x.ORGANIZATION_ID IS NULL)'
238: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
239:
240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
241:
242: COMMIT;
243:
244: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_RECEIPT_ORG';
240: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
241:
242: COMMIT;
243:
244: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_RECEIPT_ORG';
245: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_RECEIPT_ORG_V';
246:
247: v_sql_stmt:=
248: 'insert into MSC_ST_SR_RECEIPT_ORG'
241:
242: COMMIT;
243:
244: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_RECEIPT_ORG';
245: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_RECEIPT_ORG_V';
246:
247: v_sql_stmt:=
248: 'insert into MSC_ST_SR_RECEIPT_ORG'
249: ||' ( SR_RECEIPT_ID,'
264: ||' x.DISABLE_DATE- :v_dgmt,'
265: ||' 2,'
266: ||' :v_refresh_id,'
267: ||' :v_instance_id'
268: ||' from MRP_AP_SR_RECEIPT_ORG_V'||MSC_CL_PULL.v_dblink||' x'
269: ||' WHERE (x.RECEIPT_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
270: ||' OR x.RECEIPT_ORGANIZATION_ID IS NULL)'
271: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
272:
267: ||' :v_instance_id'
268: ||' from MRP_AP_SR_RECEIPT_ORG_V'||MSC_CL_PULL.v_dblink||' x'
269: ||' WHERE (x.RECEIPT_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
270: ||' OR x.RECEIPT_ORGANIZATION_ID IS NULL)'
271: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
272:
273: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
274:
275: COMMIT;
269: ||' WHERE (x.RECEIPT_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
270: ||' OR x.RECEIPT_ORGANIZATION_ID IS NULL)'
271: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
272:
273: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
274:
275: COMMIT;
276:
277: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_SOURCE_ORG';
273: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
274:
275: COMMIT;
276:
277: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_SOURCE_ORG';
278: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_SOURCE_ORG_V';
279:
280: v_sql_stmt:=
281: 'insert into MSC_ST_SR_SOURCE_ORG'
274:
275: COMMIT;
276:
277: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_SOURCE_ORG';
278: MSC_CL_PULL.v_view_name := 'MRP_AP_SR_SOURCE_ORG_V';
279:
280: v_sql_stmt:=
281: 'insert into MSC_ST_SR_SOURCE_ORG'
282: ||' ( SR_SR_SOURCE_ID,'
307: ||' x.SHIP_METHOD,'
308: ||' 2,'
309: ||' :v_refresh_id,'
310: ||' :v_instance_id'
311: ||' from MRP_AP_SR_SOURCE_ORG_V'||MSC_CL_PULL.v_dblink||' x'
312: ||' WHERE (x.SOURCE_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
313: ||' OR x.SOURCE_ORGANIZATION_ID IS NULL)'
314: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
315:
310: ||' :v_instance_id'
311: ||' from MRP_AP_SR_SOURCE_ORG_V'||MSC_CL_PULL.v_dblink||' x'
312: ||' WHERE (x.SOURCE_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
313: ||' OR x.SOURCE_ORGANIZATION_ID IS NULL)'
314: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
315:
316: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
317:
318: COMMIT;
312: ||' WHERE (x.SOURCE_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
313: ||' OR x.SOURCE_ORGANIZATION_ID IS NULL)'
314: ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
315:
316: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
317:
318: COMMIT;
319:
320: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
316: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
317:
318: COMMIT;
319:
320: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
321:
322: MSC_CL_PULL.v_table_name:= 'MSC_ST_INTERORG_SHIP_METHODS';
323: MSC_CL_PULL.v_view_name := 'MRP_AP_INTERORG_SHIP_METHODS_V';
324:
318: COMMIT;
319:
320: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
321:
322: MSC_CL_PULL.v_table_name:= 'MSC_ST_INTERORG_SHIP_METHODS';
323: MSC_CL_PULL.v_view_name := 'MRP_AP_INTERORG_SHIP_METHODS_V';
324:
325: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
326: v_temp_sql := ' x.TO_REGION_ID,x.FROM_REGION_ID,x.CURRENCY_CODE,x.SHIP_METHOD_TEXT,';
319:
320: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
321:
322: MSC_CL_PULL.v_table_name:= 'MSC_ST_INTERORG_SHIP_METHODS';
323: MSC_CL_PULL.v_view_name := 'MRP_AP_INTERORG_SHIP_METHODS_V';
324:
325: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
326: v_temp_sql := ' x.TO_REGION_ID,x.FROM_REGION_ID,x.CURRENCY_CODE,x.SHIP_METHOD_TEXT,';
327: ELSE
321:
322: MSC_CL_PULL.v_table_name:= 'MSC_ST_INTERORG_SHIP_METHODS';
323: MSC_CL_PULL.v_view_name := 'MRP_AP_INTERORG_SHIP_METHODS_V';
324:
325: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
326: v_temp_sql := ' x.TO_REGION_ID,x.FROM_REGION_ID,x.CURRENCY_CODE,x.SHIP_METHOD_TEXT,';
327: ELSE
328: v_temp_sql := 'NULL,NULL,NULL,';
329: END IF;
388: ||' 2,'
389: ||' :v_refresh_id,'
390: ||' :v_instance_id,'
391: ||' :v_instance_id'
392: ||' FROM MRP_AP_INTERORG_SHIP_METHODS_V'||MSC_CL_PULL.v_dblink||' x'
393: ||' WHERE (x.FROM_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
394: ||' OR x.FROM_ORGANIZATION_ID IS NULL)'
395: ||' AND (x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
396: ||' OR x.TO_ORGANIZATION_ID IS NULL)';
394: ||' OR x.FROM_ORGANIZATION_ID IS NULL)'
395: ||' AND (x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
396: ||' OR x.TO_ORGANIZATION_ID IS NULL)';
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mso_trsp_penalty,
399: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_instance_id;
400:
401: COMMIT;
402:
395: ||' AND (x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
396: ||' OR x.TO_ORGANIZATION_ID IS NULL)';
397:
398: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_mso_trsp_penalty,
399: MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_instance_id;
400:
401: COMMIT;
402:
403:
400:
401: COMMIT;
402:
403:
404: IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
405:
406: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
407: v_temp_tp_sql := NULL;
408: ELSE
408: ELSE
409: v_temp_tp_sql := ' WHERE x.LAST_UPDATE_DATE > SYSDATE - :v_msc_tp_coll_window';
410: END IF;
411:
412: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGIONS';
413: MSC_CL_PULL.v_view_name := 'MRP_AP_WSH_REGIONS_V';
414:
415: BEGIN
416:
409: v_temp_tp_sql := ' WHERE x.LAST_UPDATE_DATE > SYSDATE - :v_msc_tp_coll_window';
410: END IF;
411:
412: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGIONS';
413: MSC_CL_PULL.v_view_name := 'MRP_AP_WSH_REGIONS_V';
414:
415: BEGIN
416:
417: v_sql_stmt :=
429: ''ATTRIBUTE12'',''to_number(x.ATTRIBUTE12)'',
430: ''ATTRIBUTE13'',''to_number(x.ATTRIBUTE13)'',
431: ''ATTRIBUTE14'',''to_number(x.ATTRIBUTE14)'',
432: ''ATTRIBUTE15'',''to_number(x.ATTRIBUTE15)'',''to_number(NULL)'')'
433: ||' from fnd_descr_flex_column_usages'||MSC_CL_PULL.v_dblink
434: ||' where end_user_column_name = ''Zone Usage'' and '
435: ||' descriptive_flexfield_name = ''WSH_REGIONS''' ;
436:
437: execute immediate v_sql_stmt into v_temp_sql;
512: ||' x.COUNTY,'
513: ||' :v_refresh_id,'
514: ||' :v_instance_id,'
515: || v_temp_sql
516: ||' FROM MRP_AP_WSH_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
517:
518: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
519: MSC_CL_PULL.v_instance_id;
520: COMMIT;
514: ||' :v_instance_id,'
515: || v_temp_sql
516: ||' FROM MRP_AP_WSH_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
517:
518: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
519: MSC_CL_PULL.v_instance_id;
520: COMMIT;
521:
522: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZONE_REGIONS';
515: || v_temp_sql
516: ||' FROM MRP_AP_WSH_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
517:
518: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
519: MSC_CL_PULL.v_instance_id;
520: COMMIT;
521:
522: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZONE_REGIONS';
523: MSC_CL_PULL.v_view_name := 'MRP_AP_ZONE_REGIONS_V';
518: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
519: MSC_CL_PULL.v_instance_id;
520: COMMIT;
521:
522: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZONE_REGIONS';
523: MSC_CL_PULL.v_view_name := 'MRP_AP_ZONE_REGIONS_V';
524: /* Changed Refresh_id to Refresh_NUmber */
525: v_sql_stmt:=
526: 'Insert into MSC_ST_ZONE_REGIONS'
519: MSC_CL_PULL.v_instance_id;
520: COMMIT;
521:
522: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZONE_REGIONS';
523: MSC_CL_PULL.v_view_name := 'MRP_AP_ZONE_REGIONS_V';
524: /* Changed Refresh_id to Refresh_NUmber */
525: v_sql_stmt:=
526: 'Insert into MSC_ST_ZONE_REGIONS'
527: ||' (ZONE_REGION_ID,'
546: ||' x.LAST_UPDATE_DATE,'
547: ||' x.LAST_UPDATE_LOGIN,'
548: ||' :v_refresh_id,'
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_WSH_ZONE_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
551:
552: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554:
548: ||' :v_refresh_id,'
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_WSH_ZONE_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
551:
552: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554:
555: COMMIT;
556:
549: ||' :v_instance_id'
550: ||' FROM MRP_AP_WSH_ZONE_REGIONS_V'||MSC_CL_PULL.v_dblink||' x';
551:
552: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
553: MSC_CL_PULL.v_instance_id;
554:
555: COMMIT;
556:
557: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_SITES';
553: MSC_CL_PULL.v_instance_id;
554:
555: COMMIT;
556:
557: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_SITES';
558: MSC_CL_PULL.v_view_name := 'MRP_AP_REGION_SITES_V';
559:
560: v_sql_stmt:=
561: 'Insert into MSC_ST_REGION_SITES'
554:
555: COMMIT;
556:
557: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_SITES';
558: MSC_CL_PULL.v_view_name := 'MRP_AP_REGION_SITES_V';
559:
560: v_sql_stmt:=
561: 'Insert into MSC_ST_REGION_SITES'
562: ||' ( REGION_ID,'
571: ||' x.REGION_TYPE,'
572: ||' x.ZONE_LEVEL,'
573: ||' :v_refresh_id,'
574: ||' :v_instance_id'
575: ||' FROM MRP_AP_REGION_SITES_V'||MSC_CL_PULL.v_dblink||' x';
576:
577:
578: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
579: MSC_CL_PULL.v_instance_id;
574: ||' :v_instance_id'
575: ||' FROM MRP_AP_REGION_SITES_V'||MSC_CL_PULL.v_dblink||' x';
576:
577:
578: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
579: MSC_CL_PULL.v_instance_id;
580:
581: COMMIT;
582:
575: ||' FROM MRP_AP_REGION_SITES_V'||MSC_CL_PULL.v_dblink||' x';
576:
577:
578: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
579: MSC_CL_PULL.v_instance_id;
580:
581: COMMIT;
582:
583: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_LOCATIONS';
579: MSC_CL_PULL.v_instance_id;
580:
581: COMMIT;
582:
583: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_LOCATIONS';
584: MSC_CL_PULL.v_view_name := 'MRP_AP_WSH_REGION_LOCATIONS_V';
585: /* Changed refresh_id to Refresh_Number */
586: v_sql_stmt:=
587: 'Insert into MSC_ST_REGION_LOCATIONS'
580:
581: COMMIT;
582:
583: MSC_CL_PULL.v_table_name:= 'MSC_ST_REGION_LOCATIONS';
584: MSC_CL_PULL.v_view_name := 'MRP_AP_WSH_REGION_LOCATIONS_V';
585: /* Changed refresh_id to Refresh_Number */
586: v_sql_stmt:=
587: 'Insert into MSC_ST_REGION_LOCATIONS'
588: ||' ( REGION_ID,'
611: ||' x.LAST_UPDATE_DATE,'
612: ||' x.LAST_UPDATE_LOGIN,'
613: ||' :v_refresh_id,'
614: ||' :v_instance_id'
615: ||' FROM MRP_AP_WSH_REGION_LOCATIONS_V'||MSC_CL_PULL.v_dblink||' x' || v_temp_tp_sql;
616:
617: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
618: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
619: ELSE
614: ||' :v_instance_id'
615: ||' FROM MRP_AP_WSH_REGION_LOCATIONS_V'||MSC_CL_PULL.v_dblink||' x' || v_temp_tp_sql;
616:
617: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
618: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
619: ELSE
620: 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;
621: END IF;
622:
616:
617: IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
618: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
619: ELSE
620: 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;
621: END IF;
622:
623: COMMIT;
624:
623: COMMIT;
624:
625: --collecting the carriers
626:
627: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
628: MSC_CL_PULL.v_view_name := 'MRP_AP_CARRIERS_V';
629:
630: v_sql_stmt:=
631: 'insert into MSC_ST_TRADING_PARTNERS'
624:
625: --collecting the carriers
626:
627: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
628: MSC_CL_PULL.v_view_name := 'MRP_AP_CARRIERS_V';
629:
630: v_sql_stmt:=
631: 'insert into MSC_ST_TRADING_PARTNERS'
632: ||' ( SR_TP_ID,'
641: ||' x.FREIGHT_CODE,'
642: ||' 2,'
643: ||' :v_refresh_id,'
644: ||' :v_instance_id'
645: ||' from MRP_AP_CARRIERS_V'||MSC_CL_PULL.v_dblink||' x';
646:
647: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
648:
649: COMMIT;
643: ||' :v_refresh_id,'
644: ||' :v_instance_id'
645: ||' from MRP_AP_CARRIERS_V'||MSC_CL_PULL.v_dblink||' x';
646:
647: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
648:
649: COMMIT;
650:
651: -- Pulling Carrier Services for Deployment Planning Changes
649: COMMIT;
650:
651: -- Pulling Carrier Services for Deployment Planning Changes
652:
653: MSC_CL_PULL.v_table_name:= 'MSC_ST_CARRIER_SERVICES';
654: MSC_CL_PULL.v_view_name := 'MRP_AP_CARRIER_SERVICES_V';
655:
656: v_sql_stmt:=
657: 'INSERT INTO MSC_ST_CARRIER_SERVICES'
650:
651: -- Pulling Carrier Services for Deployment Planning Changes
652:
653: MSC_CL_PULL.v_table_name:= 'MSC_ST_CARRIER_SERVICES';
654: MSC_CL_PULL.v_view_name := 'MRP_AP_CARRIER_SERVICES_V';
655:
656: v_sql_stmt:=
657: 'INSERT INTO MSC_ST_CARRIER_SERVICES'
658: ||' ( SHIP_METHOD_CODE,'
667: ||' x.SERVICE_LEVEL,'
668: ||' x.MODE_OF_TRANSPORT,'
669: ||' :v_refresh_id,'
670: ||' :v_instance_id'
671: ||' FROM MRP_AP_CARRIER_SERVICES_V'||MSC_CL_PULL.v_dblink||' x';
672:
673: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
674: MSC_CL_PULL.v_instance_id;
675:
669: ||' :v_refresh_id,'
670: ||' :v_instance_id'
671: ||' FROM MRP_AP_CARRIER_SERVICES_V'||MSC_CL_PULL.v_dblink||' x';
672:
673: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
674: MSC_CL_PULL.v_instance_id;
675:
676: COMMIT;
677:
670: ||' :v_instance_id'
671: ||' FROM MRP_AP_CARRIER_SERVICES_V'||MSC_CL_PULL.v_dblink||' x';
672:
673: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
674: MSC_CL_PULL.v_instance_id;
675:
676: COMMIT;
677:
678:
679: END IF; -- APPS version = 115
680:
681: END IF; -- complete refresh
682:
683: END IF; -- MSC_CL_PULL.SOURCING_ENABLED
684:
685: END LOAD_SOURCING;
686:
687: --==================================================================
688:
689: PROCEDURE LOAD_SUB_INVENTORY IS
690: BEGIN
691:
692: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
693:
694: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
695: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
691:
692: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
693:
694: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
695: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
697:
698: v_sql_stmt:=
699: ' insert into MSC_ST_SUB_INVENTORIES'
692: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
693:
694: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
695: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
697:
698: v_sql_stmt:=
699: ' insert into MSC_ST_SUB_INVENTORIES'
700: ||' ( ORGANIZATION_ID,'
728: ||' :v_instance_id,'
729: ||' x.condition_type,'
730: ||' x.SR_RESOURCE_NAME,'
731: ||' x.SR_CUSTOMER_ACCT_ID'
732: ||' from MRP_AP_SUB_INVENTORIES_NEW_V'||MSC_CL_PULL.v_dblink||' x'
733: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
734: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
735: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736: else
730: ||' x.SR_RESOURCE_NAME,'
731: ||' x.SR_CUSTOMER_ACCT_ID'
732: ||' from MRP_AP_SUB_INVENTORIES_NEW_V'||MSC_CL_PULL.v_dblink||' x'
733: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
734: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
735: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736: else
737:
738: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
731: ||' x.SR_CUSTOMER_ACCT_ID'
732: ||' from MRP_AP_SUB_INVENTORIES_NEW_V'||MSC_CL_PULL.v_dblink||' x'
733: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
734: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
735: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736: else
737:
738: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
734: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
735: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736: else
737:
738: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
740:
741: v_sql_stmt:=
742: ' insert into MSC_ST_SUB_INVENTORIES'
735: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736: else
737:
738: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
739: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
740:
741: v_sql_stmt:=
742: ' insert into MSC_ST_SUB_INVENTORIES'
743: ||' ( ORGANIZATION_ID,'
765: ||' x.TASK_ID,'
766: ||' 2,'
767: ||' :v_refresh_id,'
768: ||' :v_instance_id'
769: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
770: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
771: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
772: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
773:
767: ||' :v_refresh_id,'
768: ||' :v_instance_id'
769: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
770: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
771: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
772: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
773:
774: end if;
775:
768: ||' :v_instance_id'
769: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
770: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
771: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
772: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
773:
774: end if;
775:
776: --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;
772: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
773:
774: end if;
775:
776: --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;
777: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
778:
779: COMMIT;
780:
773:
774: end if;
775:
776: --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;
777: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
778:
779: COMMIT;
780:
781: END LOAD_SUB_INVENTORY;
785:
786: PROCEDURE LOAD_UNIT_NUMBER IS
787: BEGIN
788:
789: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
790:
791: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
792: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
793:
787: BEGIN
788:
789: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
790:
791: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
792: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
793:
794: v_sql_stmt:=
795: ' insert into MSC_ST_Unit_Numbers'
788:
789: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
790:
791: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
792: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
793:
794: v_sql_stmt:=
795: ' insert into MSC_ST_Unit_Numbers'
796: ||' ( UNIT_NUMBER,'
807: ||' x.COMMENTS,'
808: ||' 2,'
809: ||' :v_refresh_id,'
810: ||' :v_instance_id'
811: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
812: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
813: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
809: ||' :v_refresh_id,'
810: ||' :v_instance_id'
811: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
812: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
813: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
816:
817: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
810: ||' :v_instance_id'
811: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
812: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
813: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
816:
817: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
818:
811: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
812: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
813: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
816:
817: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
818:
819: COMMIT;
813: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
816:
817: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
818:
819: COMMIT;
820:
821: END IF; -- MSC_CL_PULL.v_apps_ver
817: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
818:
819: COMMIT;
820:
821: END IF; -- MSC_CL_PULL.v_apps_ver
822:
823: END LOAD_UNIT_NUMBER;
824:
825: --==================================================================
826:
827: PROCEDURE LOAD_PROJECT IS
828: BEGIN
829:
830: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECTS';
831: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECTS_V';
832:
833: v_sql_stmt:=
834: ' insert into MSC_ST_PROJECTS'
827: PROCEDURE LOAD_PROJECT IS
828: BEGIN
829:
830: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECTS';
831: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECTS_V';
832:
833: v_sql_stmt:=
834: ' insert into MSC_ST_PROJECTS'
835: ||' ( PROJECT_ID,'
868: ||' x.MANAGER_CONTACT,'
869: ||' 2,'
870: ||' :v_refresh_id,'
871: ||' :v_instance_id'
872: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
873: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
874: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
870: ||' :v_refresh_id,'
871: ||' :v_instance_id'
872: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
873: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
874: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
877:
878: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
871: ||' :v_instance_id'
872: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
873: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
874: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
877:
878: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
879:
872: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
873: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
874: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
877:
878: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
879:
880: COMMIT;
874: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
877:
878: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
879:
880: COMMIT;
881:
882: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
878: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
879:
880: COMMIT;
881:
882: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
883: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECT_TASKS_V';
884:
885: v_sql_stmt:=
886: 'insert into MSC_ST_PROJECT_TASKS'
879:
880: COMMIT;
881:
882: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
883: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECT_TASKS_V';
884:
885: v_sql_stmt:=
886: 'insert into MSC_ST_PROJECT_TASKS'
887: ||' ( ORGANIZATION_ID,'
910: ||' x.MANAGER_CONTACT,'
911: ||' 2,'
912: ||' :v_refresh_id,'
913: ||' :v_instance_id'
914: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
916: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
912: ||' :v_refresh_id,'
913: ||' :v_instance_id'
914: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
916: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
919:
920: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
913: ||' :v_instance_id'
914: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
916: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
919:
920: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
921:
914: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
915: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
916: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
919:
920: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
921:
922: COMMIT;
916: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
919:
920: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
921:
922: COMMIT;
923:
924: END LOAD_PROJECT;
929:
930: PROCEDURE LOAD_BIS107 IS
931: BEGIN
932:
933: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
934:
935: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
936: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
937:
931: BEGIN
932:
933: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
934:
935: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
936: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
937:
938: v_sql_stmt:=
939: ' INSERT INTO MSC_ST_BIS_PERIODS'
932:
933: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
934:
935: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
936: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
937:
938: v_sql_stmt:=
939: ' INSERT INTO MSC_ST_BIS_PERIODS'
940: ||' ( ORGANIZATION_ID,'
971: ||' x.YEAR_START_DATE,'
972: ||' x.QUARTER_START_DATE,'
973: ||' :v_refresh_id,'
974: ||' :v_instance_id'
975: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
976: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
977: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
978:
979:
976: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
977: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
978:
979:
980: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
981:
982: COMMIT;
983:
984: END IF;
987:
988: PROCEDURE LOAD_BIS110 IS
989: BEGIN
990:
991: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
992:
993: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
994: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
995:
989: BEGIN
990:
991: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
992:
993: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
994: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
995:
996: v_sql_stmt:=
997: ' INSERT INTO MSC_ST_BIS_PERIODS'
990:
991: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
992:
993: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
994: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
995:
996: v_sql_stmt:=
997: ' INSERT INTO MSC_ST_BIS_PERIODS'
998: ||' ( ORGANIZATION_ID,'
1029: ||' x.YEAR_START_DATE,'
1030: ||' x.QUARTER_START_DATE,'
1031: ||' :v_refresh_id,'
1032: ||' :v_instance_id'
1033: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1034: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1035: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1036:
1037: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1033: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1034: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1035: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1036:
1037: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1038:
1039: COMMIT;
1040:
1041: BEGIN
1039: COMMIT;
1040:
1041: BEGIN
1042:
1043: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1044: MSC_CL_PULL.v_view_name := 'BIS_INDICATORS_VL';
1045:
1046: v_sql_stmt:=
1047: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1040:
1041: BEGIN
1042:
1043: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1044: MSC_CL_PULL.v_view_name := 'BIS_INDICATORS_VL';
1045:
1046: v_sql_stmt:=
1047: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1048: ||' ( MEASURE_ID,'
1075: ||' NULL UNIT_OF_MEASURE_CLASS,' --
1076: ||' 2,'
1077: ||' :v_refresh_id,'
1078: ||' :v_instance_id'
1079: ||' FROM BIS_INDICATORS_VL'||MSC_CL_PULL.v_dblink||' x';
1080:
1081: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1082:
1083: COMMIT;
1077: ||' :v_refresh_id,'
1078: ||' :v_instance_id'
1079: ||' FROM BIS_INDICATORS_VL'||MSC_CL_PULL.v_dblink||' x';
1080:
1081: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1082:
1083: COMMIT;
1084:
1085: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1081: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1082:
1083: COMMIT;
1084:
1085: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1086: MSC_CL_PULL.v_view_name := 'BIS_TARGET_LEVELS_VL';
1087:
1088: v_sql_stmt:=
1089: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1082:
1083: COMMIT;
1084:
1085: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1086: MSC_CL_PULL.v_view_name := 'BIS_TARGET_LEVELS_VL';
1087:
1088: v_sql_stmt:=
1089: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1090: ||' ( TARGET_LEVEL_ID,'
1133: ||' x.SYSTEM_FLAG,'
1134: ||' 2,'
1135: ||' :v_refresh_id,'
1136: ||' :v_instance_id'
1137: ||' FROM BIS_TARGET_LEVELS_VL'||MSC_CL_PULL.v_dblink||' x';
1138:
1139: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1140:
1141: COMMIT;
1135: ||' :v_refresh_id,'
1136: ||' :v_instance_id'
1137: ||' FROM BIS_TARGET_LEVELS_VL'||MSC_CL_PULL.v_dblink||' x';
1138:
1139: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1140:
1141: COMMIT;
1142:
1143: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1139: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1140:
1141: COMMIT;
1142:
1143: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1144: MSC_CL_PULL.v_view_name := 'BIS_TARGET_VALUES_V';
1145:
1146: v_sql_stmt:=
1147: ' INSERT INTO MSC_ST_BIS_TARGETS'
1140:
1141: COMMIT;
1142:
1143: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1144: MSC_CL_PULL.v_view_name := 'BIS_TARGET_VALUES_V';
1145:
1146: v_sql_stmt:=
1147: ' INSERT INTO MSC_ST_BIS_TARGETS'
1148: ||' ( TARGET_ID,'
1197: ||' x.ROLE3,'
1198: ||' 2,'
1199: ||' :v_refresh_id,'
1200: ||' :v_instance_id'
1201: ||' FROM BIS_TARGET_VALUES_V'||MSC_CL_PULL.v_dblink||' x';
1202:
1203: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1204:
1205: COMMIT;
1199: ||' :v_refresh_id,'
1200: ||' :v_instance_id'
1201: ||' FROM BIS_TARGET_VALUES_V'||MSC_CL_PULL.v_dblink||' x';
1202:
1203: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1204:
1205: COMMIT;
1206:
1207: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1203: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1204:
1205: COMMIT;
1206:
1207: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1208: MSC_CL_PULL.v_view_name := 'BIS_BUSINESS_PLANS_VL';
1209:
1210: v_sql_stmt:=
1211: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1204:
1205: COMMIT;
1206:
1207: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1208: MSC_CL_PULL.v_view_name := 'BIS_BUSINESS_PLANS_VL';
1209:
1210: v_sql_stmt:=
1211: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1212: ||' ( BUSINESS_PLAN_ID,'
1227: ||' x.CURRENT_PLAN_FLAG,'
1228: ||' 2,'
1229: ||' :v_refresh_id,'
1230: ||' :v_instance_id'
1231: ||' FROM BIS_BUSINESS_PLANS_VL'||MSC_CL_PULL.v_dblink||' x';
1232:
1233: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1234:
1235: COMMIT;
1229: ||' :v_refresh_id,'
1230: ||' :v_instance_id'
1231: ||' FROM BIS_BUSINESS_PLANS_VL'||MSC_CL_PULL.v_dblink||' x';
1232:
1233: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1234:
1235: COMMIT;
1236:
1237: EXCEPTION
1252:
1253: PROCEDURE LOAD_BIS115 IS
1254: BEGIN
1255:
1256: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1257:
1258: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1259: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1260:
1254: BEGIN
1255:
1256: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1257:
1258: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1259: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1260:
1261: v_sql_stmt:=
1262: ' INSERT INTO MSC_ST_BIS_PERIODS'
1255:
1256: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1257:
1258: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1259: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1260:
1261: v_sql_stmt:=
1262: ' INSERT INTO MSC_ST_BIS_PERIODS'
1263: ||' ( ORGANIZATION_ID,'
1294: ||' x.YEAR_START_DATE,'
1295: ||' x.QUARTER_START_DATE,'
1296: ||' :v_refresh_id,'
1297: ||' :v_instance_id'
1298: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1299: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1300: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1301:
1302: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1298: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1299: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1300: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1301:
1302: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1303:
1304: COMMIT;
1305:
1306: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1302: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1303:
1304: COMMIT;
1305:
1306: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1307: MSC_CL_PULL.v_view_name := 'BISBV_PERFORMANCE_MEASURES';
1308:
1309: v_sql_stmt:=
1310: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1303:
1304: COMMIT;
1305:
1306: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1307: MSC_CL_PULL.v_view_name := 'BISBV_PERFORMANCE_MEASURES';
1308:
1309: v_sql_stmt:=
1310: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1311: ||' ( MEASURE_ID,'
1340: ||' x.UNIT_OF_MEASURE_CLASS,'
1341: ||' 2,'
1342: ||' :v_refresh_id,'
1343: ||' :v_instance_id'
1344: ||' FROM BISBV_PERFORMANCE_MEASURES'||MSC_CL_PULL.v_dblink||' x';
1345:
1346: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1347:
1348: COMMIT;
1342: ||' :v_refresh_id,'
1343: ||' :v_instance_id'
1344: ||' FROM BISBV_PERFORMANCE_MEASURES'||MSC_CL_PULL.v_dblink||' x';
1345:
1346: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1347:
1348: COMMIT;
1349:
1350: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1346: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1347:
1348: COMMIT;
1349:
1350: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1351: MSC_CL_PULL.v_view_name := 'BISBV_TARGET_LEVELS';
1352:
1353: v_sql_stmt:=
1354: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1347:
1348: COMMIT;
1349:
1350: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1351: MSC_CL_PULL.v_view_name := 'BISBV_TARGET_LEVELS';
1352:
1353: v_sql_stmt:=
1354: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1355: ||' ( TARGET_LEVEL_ID,'
1398: ||' x.SYSTEM_FLAG,'
1399: ||' 2,'
1400: ||' :v_refresh_id,'
1401: ||' :v_instance_id'
1402: ||' FROM BISBV_TARGET_LEVELS'||MSC_CL_PULL.v_dblink||' x';
1403:
1404: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405:
1406: COMMIT;
1400: ||' :v_refresh_id,'
1401: ||' :v_instance_id'
1402: ||' FROM BISBV_TARGET_LEVELS'||MSC_CL_PULL.v_dblink||' x';
1403:
1404: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405:
1406: COMMIT;
1407:
1408: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1404: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405:
1406: COMMIT;
1407:
1408: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1409: MSC_CL_PULL.v_view_name := 'BISBV_TARGETS';
1410:
1411: v_sql_stmt:=
1412: ' INSERT INTO MSC_ST_BIS_TARGETS'
1405:
1406: COMMIT;
1407:
1408: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1409: MSC_CL_PULL.v_view_name := 'BISBV_TARGETS';
1410:
1411: v_sql_stmt:=
1412: ' INSERT INTO MSC_ST_BIS_TARGETS'
1413: ||' ( TARGET_ID,'
1462: ||' x.NOTIFY_RESP3_SHORT_NAME,'
1463: ||' 2,'
1464: ||' :v_refresh_id,'
1465: ||' :v_instance_id'
1466: ||' FROM BISBV_TARGETS'||MSC_CL_PULL.v_dblink||' x';
1467:
1468: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1469:
1470: COMMIT;
1464: ||' :v_refresh_id,'
1465: ||' :v_instance_id'
1466: ||' FROM BISBV_TARGETS'||MSC_CL_PULL.v_dblink||' x';
1467:
1468: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1469:
1470: COMMIT;
1471:
1472: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1468: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1469:
1470: COMMIT;
1471:
1472: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1473: MSC_CL_PULL.v_view_name := 'BISBV_BUSINESS_PLANS';
1474:
1475: v_sql_stmt:=
1476: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1469:
1470: COMMIT;
1471:
1472: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1473: MSC_CL_PULL.v_view_name := 'BISBV_BUSINESS_PLANS';
1474:
1475: v_sql_stmt:=
1476: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1477: ||' ( BUSINESS_PLAN_ID,'
1492: ||' x.CURRENT_PLAN_FLAG,'
1493: ||' 2,'
1494: ||' :v_refresh_id,'
1495: ||' :v_instance_id'
1496: ||' FROM BISBV_BUSINESS_PLANS'||MSC_CL_PULL.v_dblink||' x';
1497:
1498: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1499:
1500: COMMIT;
1494: ||' :v_refresh_id,'
1495: ||' :v_instance_id'
1496: ||' FROM BISBV_BUSINESS_PLANS'||MSC_CL_PULL.v_dblink||' x';
1497:
1498: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1499:
1500: COMMIT;
1501:
1502: END IF;
1507:
1508: PROCEDURE LOAD_ATP_RULES IS
1509: BEGIN
1510:
1511: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1512:
1513: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1514: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1515:
1509: BEGIN
1510:
1511: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1512:
1513: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1514: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1515:
1516: v_sql_stmt:=
1517: 'INSERT INTO MSC_ST_ATP_RULES'
1510:
1511: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1512:
1513: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1514: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1515:
1516: v_sql_stmt:=
1517: 'INSERT INTO MSC_ST_ATP_RULES'
1518: ||' ( RULE_ID,'
1597: ||' AGGREGATE_TIME_FENCE_CODE,'
1598: ||' AGGREGATE_TIME_FENCE,'
1599: ||' :v_refresh_id,'
1600: ||' :v_instance_id'
1601: ||' FROM MRP_AP_ATP_RULES_V'||MSC_CL_PULL.v_dblink||' x';
1602:
1603: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1604:
1605: COMMIT;
1599: ||' :v_refresh_id,'
1600: ||' :v_instance_id'
1601: ||' FROM MRP_AP_ATP_RULES_V'||MSC_CL_PULL.v_dblink||' x';
1602:
1603: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1604:
1605: COMMIT;
1606:
1607: END IF;
1612: -- ================= LOAD PLANNERS ================
1613: PROCEDURE LOAD_PLANNERS IS
1614: BEGIN
1615:
1616: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1617:
1618: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1619: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1620:
1614: BEGIN
1615:
1616: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1617:
1618: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1619: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1620:
1621: v_sql_stmt:=
1622: ' INSERT INTO MSC_ST_PLANNERS'
1615:
1616: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1617:
1618: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1619: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1620:
1621: v_sql_stmt:=
1622: ' INSERT INTO MSC_ST_PLANNERS'
1623: ||'( PLANNER_CODE,'
1642: ||' x.USER_NAME,'
1643: ||' 2,'
1644: ||' :v_refresh_id,'
1645: ||' :v_instance_id'
1646: ||' FROM MRP_AP_PLANNERS_V'||MSC_CL_PULL.v_dblink||' x'
1647: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1648:
1649: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1650:
1645: ||' :v_instance_id'
1646: ||' FROM MRP_AP_PLANNERS_V'||MSC_CL_PULL.v_dblink||' x'
1647: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1648:
1649: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1650:
1651: COMMIT;
1652:
1653: END IF;
1658: -- ================= LOAD DEMAND_CLASS ================
1659: PROCEDURE LOAD_DEMAND_CLASS IS
1660: BEGIN
1661:
1662: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1663:
1664: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1665: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1666:
1660: BEGIN
1661:
1662: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1663:
1664: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1665: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1666:
1667: v_sql_stmt:=
1668: 'insert into MSC_ST_DEMAND_CLASSES'
1661:
1662: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1663:
1664: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1665: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1666:
1667: v_sql_stmt:=
1668: 'insert into MSC_ST_DEMAND_CLASSES'
1669: ||' ( DEMAND_CLASS,'
1685: ||' DECODE( x.ENABLED_FLAG, ''Y'', 1 , 2),'
1686: ||' 2,'
1687: ||' :v_refresh_id,'
1688: ||' :v_instance_id'
1689: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1690:
1691: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1692: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1693:
1687: ||' :v_refresh_id,'
1688: ||' :v_instance_id'
1689: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1690:
1691: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1692: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1693:
1694: COMMIT;
1695:
1688: ||' :v_instance_id'
1689: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1690:
1691: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1692: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1693:
1694: COMMIT;
1695:
1696: END IF;
1701:
1702: PROCEDURE LOAD_TRIP IS
1703: BEGIN
1704:
1705: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1706:
1707: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1708:
1709: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1703: BEGIN
1704:
1705: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1706:
1707: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1708:
1709: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1710: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1711:
1705: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1706:
1707: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1708:
1709: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1710: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1711:
1712: v_sql_stmt:=
1713: ' INSERT INTO MSC_ST_TRIPS'
1706:
1707: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1708:
1709: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1710: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1711:
1712: v_sql_stmt:=
1713: ' INSERT INTO MSC_ST_TRIPS'
1714: ||' ( TRIP_ID,'
1719: ||' x.TRIP_ID,'
1720: ||' 1,'
1721: ||' :v_refresh_id,'
1722: ||' :v_instance_id'
1723: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1724: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1725:
1726: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1727:
1720: ||' 1,'
1721: ||' :v_refresh_id,'
1722: ||' :v_instance_id'
1723: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1724: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1725:
1726: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1727:
1728: COMMIT;
1722: ||' :v_instance_id'
1723: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1724: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1725:
1726: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1727:
1728: COMMIT;
1729:
1730: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1726: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1727:
1728: COMMIT;
1729:
1730: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1731: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIP_STOPS_V';
1732:
1733: v_sql_stmt:=
1734: ' INSERT INTO MSC_ST_TRIP_STOPS'
1727:
1728: COMMIT;
1729:
1730: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1731: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIP_STOPS_V';
1732:
1733: v_sql_stmt:=
1734: ' INSERT INTO MSC_ST_TRIP_STOPS'
1735: ||' ( STOP_ID,'
1740: ||' x.STOP_ID,'
1741: ||' 1,'
1742: ||' :v_refresh_id,'
1743: ||' :v_instance_id'
1744: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1745: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1746:
1747: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1748:
1741: ||' 1,'
1742: ||' :v_refresh_id,'
1743: ||' :v_instance_id'
1744: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1745: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1746:
1747: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1748:
1749: COMMIT;
1743: ||' :v_instance_id'
1744: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1745: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1746:
1747: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1748:
1749: COMMIT;
1750:
1751: END IF;
1749: COMMIT;
1750:
1751: END IF;
1752:
1753: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1754: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIPS_V';
1755:
1756: v_sql_stmt:=
1757: ' INSERT INTO MSC_ST_TRIPS'
1750:
1751: END IF;
1752:
1753: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1754: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIPS_V';
1755:
1756: v_sql_stmt:=
1757: ' INSERT INTO MSC_ST_TRIPS'
1758: ||' ( TRIP_ID,'
1779: ||' x.VOLUME_UOM,'
1780: ||' 2,'
1781: ||' :v_refresh_id,'
1782: ||' :v_instance_id'
1783: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1784: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1785:
1786: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1787:
1780: ||' 2,'
1781: ||' :v_refresh_id,'
1782: ||' :v_instance_id'
1783: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1784: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1785:
1786: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1787:
1788: COMMIT;
1782: ||' :v_instance_id'
1783: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1784: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1785:
1786: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1787:
1788: COMMIT;
1789:
1790: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1786: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1787:
1788: COMMIT;
1789:
1790: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1791: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIP_STOPS_V';
1792:
1793: v_sql_stmt:=
1794: ' INSERT INTO MSC_ST_TRIP_STOPS'
1787:
1788: COMMIT;
1789:
1790: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1791: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIP_STOPS_V';
1792:
1793: v_sql_stmt:=
1794: ' INSERT INTO MSC_ST_TRIP_STOPS'
1795: ||' ( STOP_ID,'
1812: ||' x.TRIP_ID,'
1813: ||' 2,'
1814: ||' :v_refresh_id,'
1815: ||' :v_instance_id'
1816: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1817: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1818:
1819: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1820:
1813: ||' 2,'
1814: ||' :v_refresh_id,'
1815: ||' :v_instance_id'
1816: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1817: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1818:
1819: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1820:
1821: COMMIT;
1815: ||' :v_instance_id'
1816: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1817: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1818:
1819: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1820:
1821: COMMIT;
1822:
1823: END IF; -- MSC_CL_PULL.TRIP_ENABLED
1819: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1820:
1821: COMMIT;
1822:
1823: END IF; -- MSC_CL_PULL.TRIP_ENABLED
1824:
1825: END LOAD_TRIP;
1826:
1827: PROCEDURE LOAD_SALES_CHANNEL IS
1826:
1827: PROCEDURE LOAD_SALES_CHANNEL IS
1828: BEGIN
1829:
1830: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1832:
1833: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1834: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1829:
1830: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1832:
1833: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1834: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1835:
1836: v_sql_stmt:=
1837: 'INSERT INTO MSC_ST_SR_LOOKUPS'
1830: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1831: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1832:
1833: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1834: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1835:
1836: v_sql_stmt:=
1837: 'INSERT INTO MSC_ST_SR_LOOKUPS'
1838: ||' ( LOOKUP_TYPE,'
1855: ||' DECODE( X.ENABLED_FLAG, ''Y'', 1 , 2),'
1856: ||' 2,'
1857: ||' :v_refresh_id,'
1858: ||' :v_instance_id'
1859: ||' FROM MRP_AP_SALES_CHANNEL_V'||MSC_CL_PULL.v_dblink||' X';
1860:
1861: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1862:
1863: COMMIT;
1857: ||' :v_refresh_id,'
1858: ||' :v_instance_id'
1859: ||' FROM MRP_AP_SALES_CHANNEL_V'||MSC_CL_PULL.v_dblink||' X';
1860:
1861: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1862:
1863: COMMIT;
1864:
1865: END IF;
1868:
1869: PROCEDURE LOAD_FISCAL_CALENDAR IS
1870: BEGIN
1871:
1872: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1873: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1874:
1875: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1876: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1871:
1872: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1873: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1874:
1875: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1876: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1877:
1878: v_sql_stmt:=
1879: 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1872: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1873: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1874:
1875: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1876: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1877:
1878: v_sql_stmt:=
1879: 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1880: ||' (CALENDAR_CODE,'
1911: ||' X. MONTH_END_DATE,'
1912: ||' 2,'
1913: ||' :v_refresh_id,'
1914: ||' :v_instance_id'
1915: ||' FROM MRP_AP_FISCAL_TIME_V'||MSC_CL_PULL.v_dblink||' X';
1916:
1917: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1918:
1919: COMMIT;
1913: ||' :v_refresh_id,'
1914: ||' :v_instance_id'
1915: ||' FROM MRP_AP_FISCAL_TIME_V'||MSC_CL_PULL.v_dblink||' X';
1916:
1917: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1918:
1919: COMMIT;
1920:
1921: END IF;
1924: -- for bug # 6469722
1925: PROCEDURE LOAD_CURRENCY_CONVERSION IS
1926: BEGIN
1927:
1928: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1929: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1930:
1931: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1932: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1927:
1928: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1929: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1930:
1931: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1932: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1933:
1934: v_sql_stmt:=
1935: 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1928: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1929: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1930:
1931: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1932: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1933:
1934: v_sql_stmt:=
1935: 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1936: ||' ( SR_INSTANCE_ID,'
1959: ||' XY.LAST_UPDATE_DATE,'
1960: ||' XY.LAST_UPDATED_BY,'
1961: ||' XY.LAST_UPDATE_LOGIN,'
1962: ||' 2'
1963: ||' FROM MRP_AP_CURRENCY_CONV_V' ||MSC_CL_PULL.v_dblink||' XY'
1964: ||' WHERE CONVERSION_DATE >= sysdate - :v_msc_past_days AND'
1965: ||' CONVERSION_DATE <= sysdate + :v_msc_future_days AND'
1966: ||' TO_CURRENCY = :v_msc_hub_curr_code AND'
1967: ||' CONVERSION_TYPE = :v_msc_curr_conv_type';
1966: ||' TO_CURRENCY = :v_msc_hub_curr_code AND'
1967: ||' CONVERSION_TYPE = :v_msc_curr_conv_type';
1968:
1969:
1970: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_OTHER_PULL.G_MSC_PAST_DAYS, MSC_CL_OTHER_PULL.G_MSC_FUTURE_DAYS, MSC_CL_OTHER_PULL.G_MSC_HUB_CURR_CODE, MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE;
1971:
1972: COMMIT;
1973:
1974: END IF;
1976:
1977: PROCEDURE LOAD_DELIVERY_DETAILS IS
1978: BEGIN
1979:
1980: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1982:
1983: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1984: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1979:
1980: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1982:
1983: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1984: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1985:
1986: v_sql_stmt:=
1987: 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1980: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1981: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1982:
1983: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1984: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1985:
1986: v_sql_stmt:=
1987: 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1988: ||' ( SR_INSTANCE_ID,'
2052: ||' XY.LAST_UPDATED_BY,'
2053: ||' XY.CREATION_DATE,'
2054: ||' XY.CREATED_BY,'
2055: ||' nvl(XY.LAST_UPDATE_LOGIN,1)'
2056: ||' FROM MRP_AP_DELIVERY_DETAILS_V' ||MSC_CL_PULL.v_dblink||' XY'
2057: ||' WHERE XY.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2058:
2059:
2060: EXECUTE IMMEDIATE v_sql_stmt
2057: ||' WHERE XY.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2058:
2059:
2060: EXECUTE IMMEDIATE v_sql_stmt
2061: USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2062:
2063: COMMIT;
2064:
2065: END IF;