[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: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
693:
694: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
695: IF (MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS115) THEN
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
691:
692: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
693:
694: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
695: IF (MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS115) THEN
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
697: END IF;
698:
699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
692: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
693:
694: IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y') THEN
695: IF (MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS115) THEN
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
697: END IF;
698:
699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
700: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
696: MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
697: END IF;
698:
699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
700: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
701: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP VIew is ' || MSC_CL_PULL.v_view_name);
702:
703: v_sql_stmt:=
704: ' insert into MSC_ST_SUB_INVENTORIES'
697: END IF;
698:
699: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
700: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
701: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP VIew is ' || MSC_CL_PULL.v_view_name);
702:
703: v_sql_stmt:=
704: ' insert into MSC_ST_SUB_INVENTORIES'
705: ||' ( ORGANIZATION_ID,'
733: ||' :v_instance_id,'
734: ||' x.condition_type,'
735: ||' x.SR_RESOURCE_NAME,'
736: ||' x.SR_CUSTOMER_ACCT_ID'
737: ||' from ' ||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
738: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
739: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
740: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
741: else
735: ||' x.SR_RESOURCE_NAME,'
736: ||' x.SR_CUSTOMER_ACCT_ID'
737: ||' from ' ||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
738: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
739: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
740: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
741: else
742:
743: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
736: ||' x.SR_CUSTOMER_ACCT_ID'
737: ||' from ' ||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
738: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
739: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
740: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
741: else
742:
743: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
744:
739: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
740: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
741: else
742:
743: MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
744:
745: v_sql_stmt:=
746: ' insert into MSC_ST_SUB_INVENTORIES'
747: ||' ( ORGANIZATION_ID,'
769: ||' x.TASK_ID,'
770: ||' 2,'
771: ||' :v_refresh_id,'
772: ||' :v_instance_id'
773: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
774: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
775: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
776: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
777:
771: ||' :v_refresh_id,'
772: ||' :v_instance_id'
773: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
774: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
775: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
776: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
777:
778: end if;
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP SQL stmt is ' || v_sql_stmt);
772: ||' :v_instance_id'
773: ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
774: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
775: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
776: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
777:
778: end if;
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP SQL stmt is ' || v_sql_stmt);
780: --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;
776: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
777:
778: end if;
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP SQL stmt is ' || v_sql_stmt);
780: --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;
781: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
782:
783: COMMIT;
784:
777:
778: end if;
779: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SRP SQL stmt is ' || v_sql_stmt);
780: --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;
781: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
782:
783: COMMIT;
784:
785: END LOAD_SUB_INVENTORY;
789:
790: PROCEDURE LOAD_UNIT_NUMBER IS
791: BEGIN
792:
793: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
794:
795: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
796: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
797:
791: BEGIN
792:
793: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
794:
795: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
796: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
797:
798: v_sql_stmt:=
799: ' insert into MSC_ST_Unit_Numbers'
792:
793: IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
794:
795: MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
796: MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
797:
798: v_sql_stmt:=
799: ' insert into MSC_ST_Unit_Numbers'
800: ||' ( UNIT_NUMBER,'
811: ||' x.COMMENTS,'
812: ||' 2,'
813: ||' :v_refresh_id,'
814: ||' :v_instance_id'
815: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
816: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
817: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
818: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
819: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
813: ||' :v_refresh_id,'
814: ||' :v_instance_id'
815: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
816: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
817: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
818: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
819: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
820:
821: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
814: ||' :v_instance_id'
815: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
816: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
817: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
818: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
819: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
820:
821: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
822:
815: ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
816: ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
817: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
818: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
819: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
820:
821: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
822:
823: COMMIT;
817: ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
818: ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
819: --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
820:
821: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
822:
823: COMMIT;
824:
825: END IF; -- MSC_CL_PULL.v_apps_ver
821: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
822:
823: COMMIT;
824:
825: END IF; -- MSC_CL_PULL.v_apps_ver
826:
827: END LOAD_UNIT_NUMBER;
828:
829: --==================================================================
830:
831: PROCEDURE LOAD_PROJECT IS
832: BEGIN
833:
834: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECTS';
835: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECTS_V';
836:
837: v_sql_stmt:=
838: ' insert into MSC_ST_PROJECTS'
831: PROCEDURE LOAD_PROJECT IS
832: BEGIN
833:
834: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECTS';
835: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECTS_V';
836:
837: v_sql_stmt:=
838: ' insert into MSC_ST_PROJECTS'
839: ||' ( PROJECT_ID,'
872: ||' x.MANAGER_CONTACT,'
873: ||' 2,'
874: ||' :v_refresh_id,'
875: ||' :v_instance_id'
876: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
877: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
878: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
879: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
880: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
874: ||' :v_refresh_id,'
875: ||' :v_instance_id'
876: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
877: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
878: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
879: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
880: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
881:
882: 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;
875: ||' :v_instance_id'
876: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
877: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
878: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
879: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
880: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
881:
882: 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;
883:
876: ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
877: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
878: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
879: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
880: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
881:
882: 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;
883:
884: COMMIT;
878: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
879: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
880: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
881:
882: 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;
883:
884: COMMIT;
885:
886: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
882: 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;
883:
884: COMMIT;
885:
886: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
887: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECT_TASKS_V';
888:
889: v_sql_stmt:=
890: 'insert into MSC_ST_PROJECT_TASKS'
883:
884: COMMIT;
885:
886: MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
887: MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECT_TASKS_V';
888:
889: v_sql_stmt:=
890: 'insert into MSC_ST_PROJECT_TASKS'
891: ||' ( ORGANIZATION_ID,'
914: ||' x.MANAGER_CONTACT,'
915: ||' 2,'
916: ||' :v_refresh_id,'
917: ||' :v_instance_id'
918: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
919: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
920: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
921: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
922: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
916: ||' :v_refresh_id,'
917: ||' :v_instance_id'
918: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
919: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
920: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
921: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
922: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
923:
924: 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;
917: ||' :v_instance_id'
918: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
919: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
920: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
921: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
922: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
923:
924: 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;
925:
918: ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
919: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
920: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
921: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
922: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
923:
924: 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;
925:
926: COMMIT;
920: ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
921: ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
922: ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
923:
924: 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;
925:
926: COMMIT;
927:
928: END LOAD_PROJECT;
933:
934: PROCEDURE LOAD_BIS107 IS
935: BEGIN
936:
937: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
938:
939: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
940: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
941:
935: BEGIN
936:
937: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
938:
939: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
940: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
941:
942: v_sql_stmt:=
943: ' INSERT INTO MSC_ST_BIS_PERIODS'
936:
937: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
938:
939: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
940: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
941:
942: v_sql_stmt:=
943: ' INSERT INTO MSC_ST_BIS_PERIODS'
944: ||' ( ORGANIZATION_ID,'
975: ||' x.YEAR_START_DATE,'
976: ||' x.QUARTER_START_DATE,'
977: ||' :v_refresh_id,'
978: ||' :v_instance_id'
979: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
980: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
981: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
982:
983:
980: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
981: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
982:
983:
984: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
985:
986: COMMIT;
987:
988: END IF;
991:
992: PROCEDURE LOAD_BIS110 IS
993: BEGIN
994:
995: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
996: /*
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
998: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
999:
993: BEGIN
994:
995: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
996: /*
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
998: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
999:
1000: v_sql_stmt:=
1001: ' INSERT INTO MSC_ST_BIS_PERIODS'
994:
995: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
996: /*
997: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
998: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
999:
1000: v_sql_stmt:=
1001: ' INSERT INTO MSC_ST_BIS_PERIODS'
1002: ||' ( ORGANIZATION_ID,'
1033: ||' x.YEAR_START_DATE,'
1034: ||' x.QUARTER_START_DATE,'
1035: ||' :v_refresh_id,'
1036: ||' :v_instance_id'
1037: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1038: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1039: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1040:
1041: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1037: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1038: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1039: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1040:
1041: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1042:
1043: COMMIT;
1044: */
1045: BEGIN
1043: COMMIT;
1044: */
1045: BEGIN
1046:
1047: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1048: MSC_CL_PULL.v_view_name := 'BIS_INDICATORS_VL';
1049:
1050: v_sql_stmt:=
1051: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1044: */
1045: BEGIN
1046:
1047: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1048: MSC_CL_PULL.v_view_name := 'BIS_INDICATORS_VL';
1049:
1050: v_sql_stmt:=
1051: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1052: ||' ( MEASURE_ID,'
1079: ||' NULL UNIT_OF_MEASURE_CLASS,' --
1080: ||' 2,'
1081: ||' :v_refresh_id,'
1082: ||' :v_instance_id'
1083: ||' FROM BIS_INDICATORS_VL'||MSC_CL_PULL.v_dblink||' x';
1084:
1085: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1086:
1087: COMMIT;
1081: ||' :v_refresh_id,'
1082: ||' :v_instance_id'
1083: ||' FROM BIS_INDICATORS_VL'||MSC_CL_PULL.v_dblink||' x';
1084:
1085: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1086:
1087: COMMIT;
1088:
1089: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1085: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1086:
1087: COMMIT;
1088:
1089: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1090: MSC_CL_PULL.v_view_name := 'BIS_TARGET_LEVELS_VL';
1091:
1092: v_sql_stmt:=
1093: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1086:
1087: COMMIT;
1088:
1089: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1090: MSC_CL_PULL.v_view_name := 'BIS_TARGET_LEVELS_VL';
1091:
1092: v_sql_stmt:=
1093: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1094: ||' ( TARGET_LEVEL_ID,'
1137: ||' x.SYSTEM_FLAG,'
1138: ||' 2,'
1139: ||' :v_refresh_id,'
1140: ||' :v_instance_id'
1141: ||' FROM BIS_TARGET_LEVELS_VL'||MSC_CL_PULL.v_dblink||' x';
1142:
1143: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1144:
1145: COMMIT;
1139: ||' :v_refresh_id,'
1140: ||' :v_instance_id'
1141: ||' FROM BIS_TARGET_LEVELS_VL'||MSC_CL_PULL.v_dblink||' x';
1142:
1143: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1144:
1145: COMMIT;
1146:
1147: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1143: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1144:
1145: COMMIT;
1146:
1147: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1148: MSC_CL_PULL.v_view_name := 'BIS_TARGET_VALUES_V';
1149:
1150: v_sql_stmt:=
1151: ' INSERT INTO MSC_ST_BIS_TARGETS'
1144:
1145: COMMIT;
1146:
1147: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1148: MSC_CL_PULL.v_view_name := 'BIS_TARGET_VALUES_V';
1149:
1150: v_sql_stmt:=
1151: ' INSERT INTO MSC_ST_BIS_TARGETS'
1152: ||' ( TARGET_ID,'
1201: ||' x.ROLE3,'
1202: ||' 2,'
1203: ||' :v_refresh_id,'
1204: ||' :v_instance_id'
1205: ||' FROM BIS_TARGET_VALUES_V'||MSC_CL_PULL.v_dblink||' x';
1206:
1207: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1208:
1209: COMMIT;
1203: ||' :v_refresh_id,'
1204: ||' :v_instance_id'
1205: ||' FROM BIS_TARGET_VALUES_V'||MSC_CL_PULL.v_dblink||' x';
1206:
1207: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1208:
1209: COMMIT;
1210:
1211: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1207: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1208:
1209: COMMIT;
1210:
1211: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1212: MSC_CL_PULL.v_view_name := 'BIS_BUSINESS_PLANS_VL';
1213:
1214: v_sql_stmt:=
1215: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1208:
1209: COMMIT;
1210:
1211: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1212: MSC_CL_PULL.v_view_name := 'BIS_BUSINESS_PLANS_VL';
1213:
1214: v_sql_stmt:=
1215: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1216: ||' ( BUSINESS_PLAN_ID,'
1231: ||' x.CURRENT_PLAN_FLAG,'
1232: ||' 2,'
1233: ||' :v_refresh_id,'
1234: ||' :v_instance_id'
1235: ||' FROM BIS_BUSINESS_PLANS_VL'||MSC_CL_PULL.v_dblink||' x';
1236:
1237: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1238:
1239: COMMIT;
1233: ||' :v_refresh_id,'
1234: ||' :v_instance_id'
1235: ||' FROM BIS_BUSINESS_PLANS_VL'||MSC_CL_PULL.v_dblink||' x';
1236:
1237: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1238:
1239: COMMIT;
1240:
1241: EXCEPTION
1256:
1257: PROCEDURE LOAD_BIS115 IS
1258: BEGIN
1259:
1260: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1261: /*
1262: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1263: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1264:
1258: BEGIN
1259:
1260: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1261: /*
1262: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1263: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1264:
1265: v_sql_stmt:=
1266: ' INSERT INTO MSC_ST_BIS_PERIODS'
1259:
1260: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1261: /*
1262: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1263: MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1264:
1265: v_sql_stmt:=
1266: ' INSERT INTO MSC_ST_BIS_PERIODS'
1267: ||' ( ORGANIZATION_ID,'
1298: ||' x.YEAR_START_DATE,'
1299: ||' x.QUARTER_START_DATE,'
1300: ||' :v_refresh_id,'
1301: ||' :v_instance_id'
1302: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1303: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1304: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1305:
1306: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1302: ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1303: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1304: ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1305:
1306: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1307:
1308: COMMIT;
1309: */
1310: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1306: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1307:
1308: COMMIT;
1309: */
1310: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1311: MSC_CL_PULL.v_view_name := 'BISBV_PERFORMANCE_MEASURES';
1312:
1313: v_sql_stmt:=
1314: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1307:
1308: COMMIT;
1309: */
1310: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1311: MSC_CL_PULL.v_view_name := 'BISBV_PERFORMANCE_MEASURES';
1312:
1313: v_sql_stmt:=
1314: ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1315: ||' ( MEASURE_ID,'
1344: ||' x.UNIT_OF_MEASURE_CLASS,'
1345: ||' 2,'
1346: ||' :v_refresh_id,'
1347: ||' :v_instance_id'
1348: ||' FROM BISBV_PERFORMANCE_MEASURES'||MSC_CL_PULL.v_dblink||' x';
1349:
1350: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1351:
1352: COMMIT;
1346: ||' :v_refresh_id,'
1347: ||' :v_instance_id'
1348: ||' FROM BISBV_PERFORMANCE_MEASURES'||MSC_CL_PULL.v_dblink||' x';
1349:
1350: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1351:
1352: COMMIT;
1353:
1354: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1350: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1351:
1352: COMMIT;
1353:
1354: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1355: MSC_CL_PULL.v_view_name := 'BISBV_TARGET_LEVELS';
1356:
1357: v_sql_stmt:=
1358: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1351:
1352: COMMIT;
1353:
1354: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1355: MSC_CL_PULL.v_view_name := 'BISBV_TARGET_LEVELS';
1356:
1357: v_sql_stmt:=
1358: ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1359: ||' ( TARGET_LEVEL_ID,'
1402: ||' x.SYSTEM_FLAG,'
1403: ||' 2,'
1404: ||' :v_refresh_id,'
1405: ||' :v_instance_id'
1406: ||' FROM BISBV_TARGET_LEVELS'||MSC_CL_PULL.v_dblink||' x';
1407:
1408: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1409:
1410: COMMIT;
1404: ||' :v_refresh_id,'
1405: ||' :v_instance_id'
1406: ||' FROM BISBV_TARGET_LEVELS'||MSC_CL_PULL.v_dblink||' x';
1407:
1408: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1409:
1410: COMMIT;
1411:
1412: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1408: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1409:
1410: COMMIT;
1411:
1412: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1413: MSC_CL_PULL.v_view_name := 'BISBV_TARGETS';
1414:
1415: v_sql_stmt:=
1416: ' INSERT INTO MSC_ST_BIS_TARGETS'
1409:
1410: COMMIT;
1411:
1412: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1413: MSC_CL_PULL.v_view_name := 'BISBV_TARGETS';
1414:
1415: v_sql_stmt:=
1416: ' INSERT INTO MSC_ST_BIS_TARGETS'
1417: ||' ( TARGET_ID,'
1466: ||' x.NOTIFY_RESP3_SHORT_NAME,'
1467: ||' 2,'
1468: ||' :v_refresh_id,'
1469: ||' :v_instance_id'
1470: ||' FROM BISBV_TARGETS'||MSC_CL_PULL.v_dblink||' x';
1471:
1472: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1473:
1474: COMMIT;
1468: ||' :v_refresh_id,'
1469: ||' :v_instance_id'
1470: ||' FROM BISBV_TARGETS'||MSC_CL_PULL.v_dblink||' x';
1471:
1472: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1473:
1474: COMMIT;
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1472: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1473:
1474: COMMIT;
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1477: MSC_CL_PULL.v_view_name := 'BISBV_BUSINESS_PLANS';
1478:
1479: v_sql_stmt:=
1480: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1473:
1474: COMMIT;
1475:
1476: MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1477: MSC_CL_PULL.v_view_name := 'BISBV_BUSINESS_PLANS';
1478:
1479: v_sql_stmt:=
1480: ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1481: ||' ( BUSINESS_PLAN_ID,'
1496: ||' x.CURRENT_PLAN_FLAG,'
1497: ||' 2,'
1498: ||' :v_refresh_id,'
1499: ||' :v_instance_id'
1500: ||' FROM BISBV_BUSINESS_PLANS'||MSC_CL_PULL.v_dblink||' x';
1501:
1502: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1503:
1504: COMMIT;
1498: ||' :v_refresh_id,'
1499: ||' :v_instance_id'
1500: ||' FROM BISBV_BUSINESS_PLANS'||MSC_CL_PULL.v_dblink||' x';
1501:
1502: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1503:
1504: COMMIT;
1505:
1506: END IF;
1511:
1512: PROCEDURE LOAD_ATP_RULES IS
1513: BEGIN
1514:
1515: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1516:
1517: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1518: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1519:
1513: BEGIN
1514:
1515: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1516:
1517: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1518: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1519:
1520: v_sql_stmt:=
1521: 'INSERT INTO MSC_ST_ATP_RULES'
1514:
1515: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1516:
1517: MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1518: MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1519:
1520: v_sql_stmt:=
1521: 'INSERT INTO MSC_ST_ATP_RULES'
1522: ||' ( RULE_ID,'
1601: ||' AGGREGATE_TIME_FENCE_CODE,'
1602: ||' AGGREGATE_TIME_FENCE,'
1603: ||' :v_refresh_id,'
1604: ||' :v_instance_id'
1605: ||' FROM MRP_AP_ATP_RULES_V'||MSC_CL_PULL.v_dblink||' x';
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1608:
1609: COMMIT;
1603: ||' :v_refresh_id,'
1604: ||' :v_instance_id'
1605: ||' FROM MRP_AP_ATP_RULES_V'||MSC_CL_PULL.v_dblink||' x';
1606:
1607: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1608:
1609: COMMIT;
1610:
1611: END IF;
1616: -- ================= LOAD PLANNERS ================
1617: PROCEDURE LOAD_PLANNERS IS
1618: BEGIN
1619:
1620: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1621:
1622: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1623: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1624:
1618: BEGIN
1619:
1620: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1621:
1622: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1623: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1624:
1625: v_sql_stmt:=
1626: ' INSERT INTO MSC_ST_PLANNERS'
1619:
1620: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1621:
1622: MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1623: MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1624:
1625: v_sql_stmt:=
1626: ' INSERT INTO MSC_ST_PLANNERS'
1627: ||'( PLANNER_CODE,'
1646: ||' x.USER_NAME,'
1647: ||' 2,'
1648: ||' :v_refresh_id,'
1649: ||' :v_instance_id'
1650: ||' FROM MRP_AP_PLANNERS_V'||MSC_CL_PULL.v_dblink||' x'
1651: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1652:
1653: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1654:
1649: ||' :v_instance_id'
1650: ||' FROM MRP_AP_PLANNERS_V'||MSC_CL_PULL.v_dblink||' x'
1651: ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1652:
1653: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1654:
1655: COMMIT;
1656:
1657: END IF;
1662: -- ================= LOAD DEMAND_CLASS ================
1663: PROCEDURE LOAD_DEMAND_CLASS IS
1664: BEGIN
1665:
1666: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1667:
1668: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1669: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1670:
1664: BEGIN
1665:
1666: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1667:
1668: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1669: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1670:
1671: v_sql_stmt:=
1672: 'insert into MSC_ST_DEMAND_CLASSES'
1665:
1666: IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1667:
1668: MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1669: MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1670:
1671: v_sql_stmt:=
1672: 'insert into MSC_ST_DEMAND_CLASSES'
1673: ||' ( DEMAND_CLASS,'
1689: ||' DECODE( x.ENABLED_FLAG, ''Y'', 1 , 2),'
1690: ||' 2,'
1691: ||' :v_refresh_id,'
1692: ||' :v_instance_id'
1693: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1694:
1695: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1697:
1691: ||' :v_refresh_id,'
1692: ||' :v_instance_id'
1693: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1694:
1695: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1697:
1698: COMMIT;
1699:
1692: ||' :v_instance_id'
1693: ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1694:
1695: --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1696: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1697:
1698: COMMIT;
1699:
1700: END IF;
1705:
1706: PROCEDURE LOAD_TRIP IS
1707: BEGIN
1708:
1709: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1710:
1711: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1712:
1713: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1707: BEGIN
1708:
1709: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1710:
1711: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1712:
1713: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1714: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1715:
1709: IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1710:
1711: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1712:
1713: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1714: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1715:
1716: v_sql_stmt:=
1717: ' INSERT INTO MSC_ST_TRIPS'
1710:
1711: IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1712:
1713: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1714: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1715:
1716: v_sql_stmt:=
1717: ' INSERT INTO MSC_ST_TRIPS'
1718: ||' ( TRIP_ID,'
1723: ||' x.TRIP_ID,'
1724: ||' 1,'
1725: ||' :v_refresh_id,'
1726: ||' :v_instance_id'
1727: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1728: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1729:
1730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1731:
1724: ||' 1,'
1725: ||' :v_refresh_id,'
1726: ||' :v_instance_id'
1727: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1728: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1729:
1730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1731:
1732: COMMIT;
1726: ||' :v_instance_id'
1727: ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1728: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1729:
1730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1731:
1732: COMMIT;
1733:
1734: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1730: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1731:
1732: COMMIT;
1733:
1734: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1735: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIP_STOPS_V';
1736:
1737: v_sql_stmt:=
1738: ' INSERT INTO MSC_ST_TRIP_STOPS'
1731:
1732: COMMIT;
1733:
1734: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1735: MSC_CL_PULL.v_view_name := 'MRP_AD_TRIP_STOPS_V';
1736:
1737: v_sql_stmt:=
1738: ' INSERT INTO MSC_ST_TRIP_STOPS'
1739: ||' ( STOP_ID,'
1744: ||' x.STOP_ID,'
1745: ||' 1,'
1746: ||' :v_refresh_id,'
1747: ||' :v_instance_id'
1748: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1749: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1750:
1751: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1752:
1745: ||' 1,'
1746: ||' :v_refresh_id,'
1747: ||' :v_instance_id'
1748: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1749: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1750:
1751: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1752:
1753: COMMIT;
1747: ||' :v_instance_id'
1748: ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1749: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1750:
1751: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1752:
1753: COMMIT;
1754:
1755: END IF;
1753: COMMIT;
1754:
1755: END IF;
1756:
1757: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1758: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIPS_V';
1759:
1760: v_sql_stmt:=
1761: ' INSERT INTO MSC_ST_TRIPS'
1754:
1755: END IF;
1756:
1757: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1758: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIPS_V';
1759:
1760: v_sql_stmt:=
1761: ' INSERT INTO MSC_ST_TRIPS'
1762: ||' ( TRIP_ID,'
1783: ||' x.VOLUME_UOM,'
1784: ||' 2,'
1785: ||' :v_refresh_id,'
1786: ||' :v_instance_id'
1787: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1788: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1789:
1790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1791:
1784: ||' 2,'
1785: ||' :v_refresh_id,'
1786: ||' :v_instance_id'
1787: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1788: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1789:
1790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1791:
1792: COMMIT;
1786: ||' :v_instance_id'
1787: ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1788: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1789:
1790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1791:
1792: COMMIT;
1793:
1794: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1790: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1791:
1792: COMMIT;
1793:
1794: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1795: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIP_STOPS_V';
1796:
1797: v_sql_stmt:=
1798: ' INSERT INTO MSC_ST_TRIP_STOPS'
1791:
1792: COMMIT;
1793:
1794: MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1795: MSC_CL_PULL.v_view_name := 'MRP_AP_TRIP_STOPS_V';
1796:
1797: v_sql_stmt:=
1798: ' INSERT INTO MSC_ST_TRIP_STOPS'
1799: ||' ( STOP_ID,'
1816: ||' x.TRIP_ID,'
1817: ||' 2,'
1818: ||' :v_refresh_id,'
1819: ||' :v_instance_id'
1820: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1821: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1822:
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1824:
1817: ||' 2,'
1818: ||' :v_refresh_id,'
1819: ||' :v_instance_id'
1820: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1821: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1822:
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1824:
1825: COMMIT;
1819: ||' :v_instance_id'
1820: ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1821: ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1822:
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1824:
1825: COMMIT;
1826:
1827: END IF; -- MSC_CL_PULL.TRIP_ENABLED
1823: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1824:
1825: COMMIT;
1826:
1827: END IF; -- MSC_CL_PULL.TRIP_ENABLED
1828:
1829: END LOAD_TRIP;
1830:
1831: PROCEDURE LOAD_SALES_CHANNEL IS
1830:
1831: PROCEDURE LOAD_SALES_CHANNEL IS
1832: BEGIN
1833:
1834: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1836:
1837: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1838: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1833:
1834: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1836:
1837: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1838: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1839:
1840: v_sql_stmt:=
1841: 'INSERT INTO MSC_ST_SR_LOOKUPS'
1834: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1835: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1836:
1837: MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1838: MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1839:
1840: v_sql_stmt:=
1841: 'INSERT INTO MSC_ST_SR_LOOKUPS'
1842: ||' ( LOOKUP_TYPE,'
1859: ||' DECODE( X.ENABLED_FLAG, ''Y'', 1 , 2),'
1860: ||' 2,'
1861: ||' :v_refresh_id,'
1862: ||' :v_instance_id'
1863: ||' FROM MRP_AP_SALES_CHANNEL_V'||MSC_CL_PULL.v_dblink||' X';
1864:
1865: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1866:
1867: COMMIT;
1861: ||' :v_refresh_id,'
1862: ||' :v_instance_id'
1863: ||' FROM MRP_AP_SALES_CHANNEL_V'||MSC_CL_PULL.v_dblink||' X';
1864:
1865: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1866:
1867: COMMIT;
1868:
1869: END IF;
1872:
1873: PROCEDURE LOAD_FISCAL_CALENDAR IS
1874: BEGIN
1875:
1876: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1877: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1878:
1879: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1880: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1875:
1876: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1877: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1878:
1879: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1880: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1881:
1882: v_sql_stmt:=
1883: 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1876: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1877: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1878:
1879: MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1880: MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1881:
1882: v_sql_stmt:=
1883: 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1884: ||' (CALENDAR_CODE,'
1915: ||' X. MONTH_END_DATE,'
1916: ||' 2,'
1917: ||' :v_refresh_id,'
1918: ||' :v_instance_id'
1919: ||' FROM MRP_AP_FISCAL_TIME_V'||MSC_CL_PULL.v_dblink||' X';
1920:
1921: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1922:
1923: COMMIT;
1917: ||' :v_refresh_id,'
1918: ||' :v_instance_id'
1919: ||' FROM MRP_AP_FISCAL_TIME_V'||MSC_CL_PULL.v_dblink||' X';
1920:
1921: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1922:
1923: COMMIT;
1924:
1925: END IF;
1928: -- for bug # 6469722
1929: PROCEDURE LOAD_CURRENCY_CONVERSION IS
1930: BEGIN
1931:
1932: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1934:
1935: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1936: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1931:
1932: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1934:
1935: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1936: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1937:
1938: v_sql_stmt:=
1939: 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1932: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1933: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1934:
1935: MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1936: MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1937:
1938: v_sql_stmt:=
1939: 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1940: ||' ( SR_INSTANCE_ID,'
1963: ||' XY.LAST_UPDATE_DATE,'
1964: ||' XY.LAST_UPDATED_BY,'
1965: ||' XY.LAST_UPDATE_LOGIN,'
1966: ||' 2'
1967: ||' FROM MRP_AP_CURRENCY_CONV_V' ||MSC_CL_PULL.v_dblink||' XY'
1968: ||' WHERE CONVERSION_DATE >= sysdate - :v_msc_past_days AND'
1969: ||' CONVERSION_DATE <= sysdate + :v_msc_future_days AND'
1970: ||' TO_CURRENCY = :v_msc_hub_curr_code AND'
1971: ||' CONVERSION_TYPE = :v_msc_curr_conv_type';
1970: ||' TO_CURRENCY = :v_msc_hub_curr_code AND'
1971: ||' CONVERSION_TYPE = :v_msc_curr_conv_type';
1972:
1973:
1974: 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;
1975:
1976: COMMIT;
1977:
1978: END IF;
1980:
1981: PROCEDURE LOAD_DELIVERY_DETAILS IS
1982: BEGIN
1983:
1984: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1986:
1987: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1983:
1984: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1986:
1987: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1989:
1990: v_sql_stmt:=
1991: 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1984: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1985: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1986:
1987: MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1988: MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1989:
1990: v_sql_stmt:=
1991: 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1992: ||' ( SR_INSTANCE_ID,'
2056: ||' XY.LAST_UPDATED_BY,'
2057: ||' XY.CREATION_DATE,'
2058: ||' XY.CREATED_BY,'
2059: ||' nvl(XY.LAST_UPDATE_LOGIN,1)'
2060: ||' FROM MRP_AP_DELIVERY_DETAILS_V' ||MSC_CL_PULL.v_dblink||' XY'
2061: ||' WHERE XY.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2062:
2063:
2064: EXECUTE IMMEDIATE v_sql_stmt
2061: ||' WHERE XY.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2062:
2063:
2064: EXECUTE IMMEDIATE v_sql_stmt
2065: USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2066:
2067: COMMIT;
2068:
2069: END IF;
2078:
2079: BEGIN
2080:
2081: IF MSC_UTIL.G_COLLECT_SRP_PH2_ENABLE = 'Y' THEN
2082: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2083: v_condition_sql := ' ' ; -- complete/TARGET refresh
2084:
2085: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2086:
2081: IF MSC_UTIL.G_COLLECT_SRP_PH2_ENABLE = 'Y' THEN
2082: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2083: v_condition_sql := ' ' ; -- complete/TARGET refresh
2084:
2085: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2086:
2087: lv_sql_stmt := 'SELECT LAST_IBUC_COLL_DATE FROM MSC_APPS_INSTANCES
2088: WHERE INSTANCE_ID= :v_instance_id';
2089: EXECUTE IMMEDIATE lv_sql_stmt INTO v_last_ibuc_coll_date
2086:
2087: lv_sql_stmt := 'SELECT LAST_IBUC_COLL_DATE FROM MSC_APPS_INSTANCES
2088: WHERE INSTANCE_ID= :v_instance_id';
2089: EXECUTE IMMEDIATE lv_sql_stmt INTO v_last_ibuc_coll_date
2090: USING MSC_CL_PULL.v_instance_id;
2091:
2092: v_condition_sql := ' WHERE x.sample_date >= '||' :v_last_ibuc_coll_date' ; -- Net change
2093:
2094: END IF ;
2093:
2094: END IF ;
2095:
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading IB Contracts History');
2097: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZN_AGGR_IBUC';
2098: MSC_CL_PULL.v_view_name := 'MSC_SR_ZN_AGGR_IBUC';
2099:
2100: v_sql_stmt:=
2101: ' INSERT INTO MSC_ST_ZN_AGGR_IBUC '
2094: END IF ;
2095:
2096: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading IB Contracts History');
2097: MSC_CL_PULL.v_table_name:= 'MSC_ST_ZN_AGGR_IBUC';
2098: MSC_CL_PULL.v_view_name := 'MSC_SR_ZN_AGGR_IBUC';
2099:
2100: v_sql_stmt:=
2101: ' INSERT INTO MSC_ST_ZN_AGGR_IBUC '
2102: ||' (SR_INVENTORY_ITEM_ID,'
2121: ||' x.LAST_UPDATE_DATE,'
2122: ||' x.LAST_UPDATED_BY,'
2123: ||' x.CREATION_DATE,'
2124: ||' x.CREATED_BY'
2125: ||' FROM MSC_SR_ZN_AGGR_IBUC'||MSC_CL_PULL.v_dblink ||' x'
2126: || v_condition_sql ;
2127:
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt' ||v_sql_stmt);
2129:
2126: || v_condition_sql ;
2127:
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt' ||v_sql_stmt);
2129:
2130: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2131: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
2132:
2133: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2134:
2127:
2128: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_sql_stmt' ||v_sql_stmt);
2129:
2130: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2131: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
2132:
2133: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2134:
2135: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,
2129:
2130: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2131: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
2132:
2133: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2134:
2135: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,
2136: MSC_CL_PULL.v_refresh_id,trunc(v_last_ibuc_coll_date);
2137:
2131: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
2132:
2133: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2134:
2135: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,
2136: MSC_CL_PULL.v_refresh_id,trunc(v_last_ibuc_coll_date);
2137:
2138: END IF ;
2139:
2132:
2133: ELSIF MSC_CL_PULL.v_lrnn<> -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2134:
2135: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id,
2136: MSC_CL_PULL.v_refresh_id,trunc(v_last_ibuc_coll_date);
2137:
2138: END IF ;
2139:
2140: COMMIT;
2147:
2148: BEGIN
2149:
2150: IF MSC_UTIL.G_COLLECT_SRP_PH2_ENABLE = 'Y' THEN
2151: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2152:
2153:
2154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Attached Documents');
2155:
2152:
2153:
2154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Attached Documents');
2155:
2156: MSC_CL_PULL.v_table_name:= 'MSC_ST_DOC_ATTACHMENTS';
2157: MSC_CL_PULL.v_view_name := 'MSC_AP_ATTACHED_DOCS_V';
2158:
2159: v_sql_stmt:=
2160: ' INSERT INTO MSC_ST_DOC_ATTACHMENTS ( '
2153:
2154: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Attached Documents');
2155:
2156: MSC_CL_PULL.v_table_name:= 'MSC_ST_DOC_ATTACHMENTS';
2157: MSC_CL_PULL.v_view_name := 'MSC_AP_ATTACHED_DOCS_V';
2158:
2159: v_sql_stmt:=
2160: ' INSERT INTO MSC_ST_DOC_ATTACHMENTS ( '
2161: ||' DOC_ID,'
2202: ||' FAD.LAST_UPDATED_BY,'
2203: ||' FAD.CREATION_DATE,'
2204: ||' FAD.CREATED_BY,'
2205: ||' FAD.LAST_UPDATE_LOGIN'
2206: ||' FROM MSC_AP_ATTACHED_DOCS_V'|| MSC_CL_PULL.v_dblink ||' FAD' ;
2207:
2208: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2209: COMMIT;
2210:
2204: ||' FAD.CREATED_BY,'
2205: ||' FAD.LAST_UPDATE_LOGIN'
2206: ||' FROM MSC_AP_ATTACHED_DOCS_V'|| MSC_CL_PULL.v_dblink ||' FAD' ;
2207:
2208: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2209: COMMIT;
2210:
2211:
2212: MSC_CL_PULL.v_table_name:='MSC_ST_SHORT_TEXT';
2208: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2209: COMMIT;
2210:
2211:
2212: MSC_CL_PULL.v_table_name:='MSC_ST_SHORT_TEXT';
2213: MSC_CL_PULL.v_view_name := 'MSC_AP_SHORT_TEXT_V';
2214: v_sql_stmt:=
2215: ' INSERT INTO MSC_ST_SHORT_TEXT '
2216: ||'( MEDIA_ID,'
2209: COMMIT;
2210:
2211:
2212: MSC_CL_PULL.v_table_name:='MSC_ST_SHORT_TEXT';
2213: MSC_CL_PULL.v_view_name := 'MSC_AP_SHORT_TEXT_V';
2214: v_sql_stmt:=
2215: ' INSERT INTO MSC_ST_SHORT_TEXT '
2216: ||'( MEDIA_ID,'
2217: ||' SHORT_TEXT,'
2231: ||' MST.LAST_UPDATED_BY,'
2232: ||' MST.CREATION_DATE,'
2233: ||' MST.CREATED_BY,'
2234: ||' MST.LAST_UPDATE_LOGIN'
2235: ||' FROM MSC_AP_SHORT_TEXT_V' ||MSC_CL_PULL.v_dblink||' MST';
2236:
2237: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2238: COMMIT;
2239:
2233: ||' MST.CREATED_BY,'
2234: ||' MST.LAST_UPDATE_LOGIN'
2235: ||' FROM MSC_AP_SHORT_TEXT_V' ||MSC_CL_PULL.v_dblink||' MST';
2236:
2237: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2238: COMMIT;
2239:
2240: END IF;
2241: END IF;
2249: -- v_sql_stmt := null;
2250: BEGIN
2251:
2252: IF MSC_UTIL.G_COLLECT_SRP_PH2_ENABLE = 'Y' THEN
2253: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2254:
2255:
2256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Long text');
2257: MSC_CL_PULL.v_table_name:= 'MSC_ST_LONG_TEXT';
2253: IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
2254:
2255:
2256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Long text');
2257: MSC_CL_PULL.v_table_name:= 'MSC_ST_LONG_TEXT';
2258: MSC_CL_PULL.v_view_name := 'MSC_AP_LONG_TEXT_V'; --Changed the view name from MSC_AP_LONG_TEXT_V to MSC_AP_CLOB_TEXT_V w.r.t bug 13628509
2259:
2260: --Have below common code whether db link is NULL or not w.r.t bug 13628509
2261:
2254:
2255:
2256: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Long text');
2257: MSC_CL_PULL.v_table_name:= 'MSC_ST_LONG_TEXT';
2258: MSC_CL_PULL.v_view_name := 'MSC_AP_LONG_TEXT_V'; --Changed the view name from MSC_AP_LONG_TEXT_V to MSC_AP_CLOB_TEXT_V w.r.t bug 13628509
2259:
2260: --Have below common code whether db link is NULL or not w.r.t bug 13628509
2261:
2262: v_sql_stmt:=
2279: ||' MLT.LAST_UPDATED_BY,'
2280: ||' MLT.CREATION_DATE,'
2281: ||' MLT.CREATED_BY,'
2282: ||' MLT.LAST_UPDATE_LOGIN'
2283: ||' FROM MSC_AP_CLOB_TEXT_V' ||MSC_CL_PULL.v_dblink||' MLT';
2284: --Using the view MSC_AP_CLOB_TEXT_V w.r.t bug 13628509
2285:
2286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Long Text sql stmt - ' || v_sql_stmt );
2287: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2283: ||' FROM MSC_AP_CLOB_TEXT_V' ||MSC_CL_PULL.v_dblink||' MLT';
2284: --Using the view MSC_AP_CLOB_TEXT_V w.r.t bug 13628509
2285:
2286: MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Long Text sql stmt - ' || v_sql_stmt );
2287: EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2288: COMMIT;
2289:
2290: END IF;
2291: