[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_OTHER_PULL
Source
1 PACKAGE BODY MSC_CL_OTHER_PULL AS -- body
2 /* $Header:*/
3
4
5
6 v_union_sql varchar2(32767);
7 v_temp_tp_sql VARCHAR2(100);
8 v_sql_stmt VARCHAR2(32767);
9 v_temp_sql VARCHAR2(15000);
10 v_temp_sql1 VARCHAR2(1000);
11 v_temp_sql2 VARCHAR2(1000);
12 v_temp_sql3 VARCHAR2(1000);
13 v_temp_sql4 VARCHAR2(1000);
14 v_schedule_sql VARCHAR2(1000); /* Bug 2634435 */
15
16 --NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
17 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
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,';
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'
36 ||' ( INVENTORY_ITEM_ID,'
37 ||' ORGANIZATION_ID,'
38 ||' PERIOD_START_DATE,'
39 ||' SAFETY_STOCK_QUANTITY,'
40 ||' PROJECT_ID,'
41 ||' TASK_ID,'
42 ||' PLANNING_GROUP,'
43 ||' DELETED_FLAG,'
44 ||' REFRESH_ID,'
45 ||' SR_INSTANCE_ID)'
46 ||' select '
47 ||' x.INVENTORY_ITEM_ID,'
48 ||' x.ORGANIZATION_ID,'
49 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
50 ||' x.SAFETY_STOCK_QUANTITY,'
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
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;
68
69 END LOAD_SAFETY_STOCK;
70
71 --==================================================================
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
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:=
90 'insert into MSC_ST_DESIGNATORS'
91 ||' ( DESIGNATOR,'
92 ||' ORGANIZATION_ID,'
93 ||' MPS_RELIEF,'
94 ||' INVENTORY_ATP_FLAG,'
95 ||' DESCRIPTION,'
96 ||' DISABLE_DATE,'
97 ||' DEMAND_CLASS,'
98 ||' ORGANIZATION_SELECTION,'
99 ||' PRODUCTION,'
100 ||' DESIGNATOR_TYPE,'
101 ||' DELETED_FLAG,'
102 ||' REFRESH_ID,'
103 ||' SR_INSTANCE_ID)'
104 ||' select '
105 ||' x.DESIGNATOR,'
106 ||' x.ORGANIZATION_ID,'
107 ||' x.MPS_RELIEF,'
108 ||' x.INVENTORY_ATP_FLAG,'
109 ||' x.DESCRIPTION,'
110 ||' x.DISABLE_DATE- :v_dgmt,'
111 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
112 ||' x.DEMAND_CLASS,'
113 ||' x.ORGANIZATION_SELECTION,'
114 ||' x.PRODUCTION,'
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
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;
130
131 --==================================================================
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
141 v_sql_stmt:=
142 ' insert into MSC_ST_ASSIGNMENT_SETS'
143 ||' ( SR_ASSIGNMENT_SET_ID,'
144 ||' ASSIGNMENT_SET_NAME,'
145 ||' DESCRIPTION,'
146 ||' DELETED_FLAG,'
147 ||' REFRESH_ID,'
148 ||' SR_INSTANCE_ID)'
149 ||' select'
150 ||' x.ASSIGNMENT_SET_ID,'
151 ||' :V_ICODE||x.ASSIGNMENT_SET_NAME,'
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
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,'
169 ||' ASSIGNMENT_TYPE,'
170 ||' SOURCING_RULE_ID,'
171 ||' SOURCING_RULE_TYPE,'
172 ||' ASSIGNMENT_SET_ID,'
173 ||' INVENTORY_ITEM_ID,'
174 ||' ORGANIZATION_ID,'
175 ||' SR_INSTANCE_ID,'
176 ||' PARTNER_ID,'
177 ||' SHIP_TO_SITE_ID,'
178 ||' Category_Name,'
179 ||' Category_Set_Identifier,'
180 ||' DELETED_FLAG,'
181 ||' REFRESH_ID,'
182 ||' SR_ASSIGNMENT_INSTANCE_ID)'
183 ||' select'
184 ||' x.ASSIGNMENT_ID,'
185 ||' x.ASSIGNMENT_TYPE,'
186 ||' x.SOURCING_RULE_ID,'
187 ||' x.SOURCING_RULE_TYPE,'
188 ||' x.ASSIGNMENT_SET_ID,'
189 ||' x.INVENTORY_ITEM_ID,'
190 ||' x.ORGANIZATION_ID,'
191 ||' :v_instance_id,'
192 ||' x.Customer_ID,'
193 ||' x.Ship_To_Site_ID,'
194 ||' substrb(x.CATEGORY_NAME,1,'||MSC_UTIL.G_ITEMCAT_LEN||'),' --12640878
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||')';
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';
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,'
215 ||' SOURCING_RULE_NAME,'
216 ||' ORGANIZATION_ID,'
217 ||' DESCRIPTION,'
218 ||' STATUS,'
219 ||' SOURCING_RULE_TYPE,'
220 ||' PLANNING_ACTIVE,'
221 ||' DELETED_FLAG,'
222 ||' REFRESH_ID,'
223 ||' SR_INSTANCE_ID)'
224 ||' select'
225 ||' x.SOURCING_RULE_ID,'
226 ||' x.SOURCING_RULE_NAME,'
227 ||' x.ORGANIZATION_ID,'
228 ||' x.DESCRIPTION,'
229 ||' x.STATUS,'
230 ||' x.SOURCING_RULE_TYPE,'
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
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'
249 ||' ( SR_RECEIPT_ID,'
250 ||' SR_SR_RECEIPT_ORG,'
251 ||' RECEIPT_ORG_INSTANCE_ID,'
252 ||' SOURCING_RULE_ID,'
253 ||' EFFECTIVE_DATE,'
254 ||' DISABLE_DATE,'
255 ||' DELETED_FLAG,'
256 ||' REFRESH_ID,'
257 ||' SR_INSTANCE_ID)'
258 ||' select'
259 ||' x.SR_RECEIPT_ID,'
260 ||' x.RECEIPT_ORGANIZATION_ID,'
261 ||' :v_instance_id,'
262 ||' x.SOURCING_RULE_ID,'
263 ||' x.EFFECTIVE_DATE- :v_dgmt,'
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
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'
282 ||' ( SR_SR_SOURCE_ID,'
283 ||' SR_RECEIPT_ID,'
284 ||' SOURCE_ORGANIZATION_ID,'
285 ||' SOURCE_ORG_INSTANCE_ID,'
286 ||' SECONDARY_INVENTORY,'
287 ||' SOURCE_TYPE,'
288 ||' ALLOCATION_PERCENT,'
289 ||' RANK,'
290 ||' SOURCE_PARTNER_ID,'
291 ||' SOURCE_PARTNER_SITE_ID,'
292 ||' SHIP_METHOD,'
293 ||' DELETED_FLAG,'
294 ||' REFRESH_ID,'
295 ||' SR_INSTANCE_ID)'
296 ||' select'
297 ||' x.SR_SOURCE_ID,'
298 ||' x.SR_RECEIPT_ID,'
299 ||' x.SOURCE_ORGANIZATION_ID,'
300 ||' :v_instance_id,'
301 ||' x.SECONDARY_INVENTORY,'
302 ||' x.SOURCE_TYPE,'
303 ||' x.ALLOCATION_PERCENT,'
304 ||' x.RANK,'
305 ||' x.VENDOR_ID,'
306 ||' x.VENDOR_SITE_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
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
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;
330
331 v_sql_stmt:=
332 'Insert into MSC_ST_INTERORG_SHIP_METHODS'
333 ||' ( FROM_ORGANIZATION_ID,'
334 ||' TO_ORGANIZATION_ID,'
335 ||' SHIP_METHOD,'
336 ||' TIME_UOM_CODE,'
337 ||' DEFAULT_FLAG,'
338 ||' FROM_LOCATION_ID,'
339 ||' TO_LOCATION_ID,'
340 ||' WEIGHT_CAPACITY,'
341 ||' WEIGHT_UOM,'
342 ||' VOLUME_CAPACITY,'
343 ||' VOLUME_UOM,'
344 ||' COST_PER_WEIGHT_UNIT,'
345 ||' COST_PER_VOLUME_UNIT,'
346 ||' INTRANSIT_TIME,'
347 ||' TO_REGION_ID,'
348 ||' FROM_REGION_ID,'
349 ||' CURRENCY,'
350 ||' SHIP_METHOD_TEXT,'
351 ||' TRANSPORT_CAP_OVER_UTIL_COST,'
352 ||' DELETED_FLAG,'
353 ||' REFRESH_ID,'
354 ||' SR_INSTANCE_ID,' -- from_org
355 ||' SR_INSTANCE_ID2)' -- to_org
356 ||' SELECT'
357 ||' x.FROM_ORGANIZATION_ID,'
358 ||' x.TO_ORGANIZATION_ID,'
359 ||' x.SHIP_METHOD,'
360 ||' x.TIME_UOM_CODE,'
361 ||' x.DEFAULT_FLAG,'
362 ||' x.FROM_LOCATION_ID,'
363 ||' x.TO_LOCATION_ID,'
364 ||' x.DAILY_LOAD_WEIGHT_CAPACITY,'
365 ||' x.LOAD_WEIGHT_UOM_CODE,'
366 ||' x.DAILY_VOLUME_CAPACITY,'
367 ||' x.VOLUME_UOM_CODE,'
368 ||' x.COST_PER_UNIT_LOAD_WEIGHT,'
369 ||' x.COST_PER_UNIT_LOAD_VOLUME,'
370 ||' x.INTRANSIT_TIME,'
371 || v_temp_sql
372 ||' TO_NUMBER(DECODE( :v_mso_trsp_penalty,'
373 ||' 1, x.Attribute1,'
374 ||' 2, x.Attribute2,'
375 ||' 3, x.Attribute3,'
376 ||' 4, x.Attribute4,'
377 ||' 5, x.Attribute5,'
378 ||' 6, x.Attribute6,'
379 ||' 7, x.Attribute7,'
380 ||' 8, x.Attribute8,'
381 ||' 9, x.Attribute9,'
382 ||' 10, x.Attribute10,'
383 ||' 11, x.Attribute11,'
384 ||' 12, x.Attribute12,'
385 ||' 13, x.Attribute13,'
386 ||' 14, x.Attribute14,'
387 ||' 15, x.Attribute15)),'
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)';
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
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
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 :=
418 ' select decode (application_column_name, ''ATTRIBUTE1'',''to_number(x.ATTRIBUTE1)'',
419 ''ATTRIBUTE2'',''to_number(x.ATTRIBUTE2)'',
420 ''ATTRIBUTE3'',''to_number(x.ATTRIBUTE3)'',
421 ''ATTRIBUTE4'',''to_number(x.ATTRIBUTE4)'',
422 ''ATTRIBUTE5'',''to_number(x.ATTRIBUTE5)'',
423 ''ATTRIBUTE6'',''to_number(x.ATTRIBUTE6)'',
424 ''ATTRIBUTE7'',''to_number(x.ATTRIBUTE7)'',
425 ''ATTRIBUTE8'',''to_number(x.ATTRIBUTE8)'',
426 ''ATTRIBUTE9'',''to_number(x.ATTRIBUTE9)'',
427 ''ATTRIBUTE10'',''to_number(x.ATTRIBUTE10)'',
428 ''ATTRIBUTE11'',''to_number(x.ATTRIBUTE11)'',
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
437 execute immediate v_sql_stmt into v_temp_sql;
434 ||' where end_user_column_name = ''Zone Usage'' and '
435 ||' descriptive_flexfield_name = ''WSH_REGIONS''' ;
436
438
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 v_temp_sql := 'to_number(NULL) ';
442 WHEN OTHERS THEN
443 v_temp_sql := 'to_number(NULL) ';
444
445 END ;
446 /* Changed Rehresh_id to Refresh_number */
447 v_sql_stmt:=
448 'Insert into MSC_ST_REGIONS'
449 ||' ( REGION_ID,'
450 ||' REGION_TYPE,'
451 ||' PARENT_REGION_ID,'
452 ||' COUNTRY_CODE,'
453 ||' COUNTRY_REGION_CODE,'
454 ||' STATE_CODE,'
455 ||' CITY_CODE,'
456 ||' PORT_FLAG,'
457 ||' AIRPORT_FLAG,'
458 ||' ROAD_TERMINAL_FLAG,'
459 ||' RAIL_TERMINAL_FLAG,'
460 ||' LONGITUDE,'
461 ||' LATITUDE,'
462 ||' TIMEZONE,'
463 ||' CREATED_BY,'
464 ||' CREATION_DATE,'
465 ||' LAST_UPDATED_BY,'
466 ||' LAST_UPDATE_DATE,'
467 ||' LAST_UPDATE_LOGIN,'
468 ||' CONTINENT,'
469 ||' COUNTRY,'
470 ||' COUNTRY_REGION,'
471 ||' STATE,'
472 ||' CITY,'
473 ||' ZONE,'
474 ||' ZONE_LEVEL,'
475 ||' POSTAL_CODE_FROM,'
476 ||' POSTAL_CODE_TO,'
477 ||' ALTERNATE_NAME,'
478 ||' COUNTY,'
479 ||' REFRESH_NUMBER,'
480 ||' SR_INSTANCE_ID,'
481 ||' ZONE_USAGE)'
482 ||' SELECT'
483 ||' x.REGION_ID,'
484 ||' x.REGION_TYPE,'
485 ||' x.PARENT_REGION_ID,'
486 ||' x.COUNTRY_CODE,'
487 ||' x.COUNTRY_REGION_CODE,'
488 ||' x.STATE_CODE,'
489 ||' x.CITY_CODE,'
490 ||' x.PORT_FLAG,'
491 ||' x.AIRPORT_FLAG,'
492 ||' x.ROAD_TERMINAL_FLAG,'
493 ||' x.RAIL_TERMINAL_FLAG,'
494 ||' x.LONGITUDE,'
495 ||' x.LATITUDE,'
496 ||' x.TIMEZONE,'
497 ||' x.CREATED_BY,'
498 ||' x.CREATION_DATE,'
499 ||' x.LAST_UPDATED_BY,'
500 ||' x.LAST_UPDATE_DATE,'
501 ||' x.LAST_UPDATE_LOGIN,'
502 ||' x.CONTINENT,'
503 ||' x.COUNTRY,'
504 ||' x.COUNTRY_REGION,'
505 ||' x.STATE,'
506 ||' x.CITY,'
507 ||' x.ZONE,'
508 ||' x.ZONE_LEVEL,'
509 ||' x.POSTAL_CODE_FROM,'
510 ||' x.POSTAL_CODE_TO,'
511 ||' x.ALTERNATE_NAME,'
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;
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,'
528 ||' REGION_ID,'
529 ||' PARENT_REGION_ID,'
530 ||' PARTY_ID,'
531 ||' CREATED_BY,'
532 ||' CREATION_DATE,'
533 ||' LAST_UPDATED_BY,'
534 ||' LAST_UPDATE_DATE,'
535 ||' LAST_UPDATE_LOGIN,'
536 ||' REFRESH_NUMBER,'
537 ||' SR_INSTANCE_ID)'
538 ||' SELECT'
539 ||' x.ZONE_REGION_ID,'
540 ||' x.REGION_ID,'
541 ||' x.PARENT_REGION_ID,'
542 ||' x.PARTY_ID,'
543 ||' x.CREATED_BY,'
544 ||' x.CREATION_DATE,'
545 ||' x.LAST_UPDATED_BY,'
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
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,'
563 ||' VENDOR_SITE_ID,'
564 ||' REGION_TYPE,'
565 ||' ZONE_LEVEL,'
566 ||' REFRESH_ID,'
567 ||' SR_INSTANCE_ID)'
568 ||' SELECT'
569 ||' x.REGION_ID,'
570 ||' x.VENDOR_SITE_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;
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,'
589 ||' LOCATION_ID,'
590 ||' REGION_TYPE,'
591 ||' PARENT_REGION_FLAG,'
592 ||' EXCEPTION_TYPE,'
593 ||' LOCATION_SOURCE,'
594 ||' CREATED_BY,'
595 ||' CREATION_DATE,'
596 ||' LAST_UPDATED_BY,'
597 ||' LAST_UPDATE_DATE,'
598 ||' LAST_UPDATE_LOGIN,'
599 ||' REFRESH_NUMBER,'
600 ||' SR_INSTANCE_ID)'
601 ||' SELECT'
602 ||' x.REGION_ID,'
603 ||' x.LOCATION_ID,'
604 ||' x.REGION_TYPE,'
605 ||' x.PARENT_REGION_FLAG,'
606 ||' x.EXCEPTION_TYPE,'
607 ||' x.LOCATION_SOURCE,'
608 ||' x.CREATED_BY,'
609 ||' x.CREATION_DATE,'
610 ||' x.LAST_UPDATED_BY,'
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;
619 ELSE
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;
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
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,'
633 ||' PARTNER_TYPE,'
634 ||' PARTNER_NAME,'
635 ||' DELETED_FLAG,'
636 ||' REFRESH_ID,'
637 ||' SR_INSTANCE_ID)'
638 ||' select'
639 ||' x.CARRIER_ID,'
640 ||' 4,'
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;
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,'
659 ||' CARRIER_ID,'
660 ||' SERVICE_LEVEL,'
661 ||' MODE_OF_TRANSPORT,'
662 ||' REFRESH_ID,'
663 ||' SR_INSTANCE_ID)'
664 ||' SELECT'
665 ||' x.SHIP_METHOD_CODE,'
666 ||' x.CARRIER_ID,'
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
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';
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,'
706 ||' SUB_INVENTORY_CODE,'
707 ||' DESCRIPTION,'
708 ||' DISABLE_DATE,'
709 ||' NETTING_TYPE,'
710 ||' INVENTORY_ATP_CODE,'
711 ||' DEMAND_CLASS,'
712 ||' PROJECT_ID,'
713 ||' TASK_ID,'
714 ||' DELETED_FLAG,'
715 ||' REFRESH_ID,'
716 ||' SR_INSTANCE_ID,'
717 ||' condition_type,'
718 ||' SR_RESOURCE_NAME,'
719 ||' SR_CUSTOMER_ACCT_ID)'
720 ||' select'
721 ||' x.ORGANIZATION_ID,'
722 ||' x.SECONDARY_INVENTORY_NAME,'
723 ||' x.DESCRIPTION,'
724 ||' x.DISABLE_DATE- :v_dgmt,'
725 ||' x.NETTING_TYPE,'
726 ||' x.INVENTORY_ATP_CODE,'
727 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
728 ||' x.DEMAND_CLASS,'
729 ||' x.PROJECT_ID,'
730 ||' x.TASK_ID,'
731 ||' 2,'
732 ||' :v_refresh_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
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,'
748 ||' SUB_INVENTORY_CODE,'
749 ||' DESCRIPTION,'
750 ||' DISABLE_DATE,'
751 ||' NETTING_TYPE,'
752 ||' INVENTORY_ATP_CODE,'
753 ||' DEMAND_CLASS,'
754 ||' PROJECT_ID,'
755 ||' TASK_ID,'
756 ||' DELETED_FLAG,'
757 ||' REFRESH_ID,'
758 ||' SR_INSTANCE_ID)'
759 ||' select'
760 ||' x.ORGANIZATION_ID,'
761 ||' x.SECONDARY_INVENTORY_NAME,'
762 ||' x.DESCRIPTION,'
763 ||' x.DISABLE_DATE- :v_dgmt,'
764 ||' x.NETTING_TYPE,'
765 ||' x.INVENTORY_ATP_CODE,'
766 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
767 ||' x.DEMAND_CLASS,'
768 ||' x.PROJECT_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'
777
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||')';
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;
786
787
788 --==================================================================
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
798 v_sql_stmt:=
799 ' insert into MSC_ST_Unit_Numbers'
800 ||' ( UNIT_NUMBER,'
801 ||' END_ITEM_ID,'
802 ||' MASTER_ORGANIZATION_ID,'
803 ||' COMMENTS,'
804 ||' DELETED_FLAG,'
805 ||' REFRESH_ID,'
806 ||' SR_INSTANCE_ID)'
807 ||' select'
808 ||' x.UNIT_NUMBER,'
809 ||' x.END_ITEM_ID,'
810 ||' x.MASTER_ORGANIZATION_ID,'
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||')';
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
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'
839 ||' ( PROJECT_ID,'
840 ||' ORGANIZATION_ID,'
841 ||' PLANNING_GROUP,'
842 ||' COSTING_GROUP_ID,'
843 ||' MATERIAL_ACCOUNT,'
844 ||' WIP_ACCT_CLASS_CODE,'
845 ||' SEIBAN_NUMBER_FLAG,'
846 ||' PROJECT_NAME,'
847 ||' PROJECT_NUMBER,'
848 ||' PROJECT_NUMBER_SORT_ORDER,'
849 ||' PROJECT_DESCRIPTION,'
850 ||' START_DATE,'
851 ||' COMPLETION_DATE,'
852 ||' OPERATING_UNIT,'
853 ||' MANAGER_CONTACT,'
854 ||' DELETED_FLAG,'
855 ||' REFRESH_ID,'
856 ||' SR_INSTANCE_ID)'
857 ||' select'
858 ||' x.PROJECT_ID,'
859 ||' x.ORGANIZATION_ID,'
860 ||' x.PLANNING_GROUP,'
861 ||' x.COSTING_GROUP_ID,'
862 ||' x.MATERIAL_ACCOUNT,'
863 ||' x.WIP_ACCT_CLASS_CODE,'
864 ||' x.SEIBAN_NUMBER_FLAG,'
865 ||' x.PROJECT_NAME,'
866 ||' x.PROJECT_NUMBER,'
867 ||' x.PROJECT_NUMBER_SORT_ORDER,'
868 ||' x.PROJECT_DESCRIPTION,'
869 ||' x.START_DATE- :v_dgmt,'
870 ||' x.COMPLETION_DATE- :v_dgmt,'
871 ||' x.OPERATING_UNIT,'
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||')';
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';
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,'
892 ||' PROJECT_ID,'
893 ||' TASK_ID,'
894 ||' TASK_NUMBER,'
895 ||' TASK_NAME,'
896 ||' DESCRIPTION,'
897 ||' MANAGER,'
898 ||' START_DATE,'
899 ||' END_DATE,'
900 ||' MANAGER_CONTACT,'
901 ||' DELETED_FLAG,'
902 ||' REFRESH_ID,'
903 ||' SR_INSTANCE_ID)'
904 ||' select'
905 ||' x.ORGANIZATION_ID,'
906 ||' x.PROJECT_ID,'
907 ||' x.TASK_ID,'
908 ||' x.TASK_NUMBER,'
909 ||' x.TASK_NAME,'
910 ||' x.DESCRIPTION,'
911 ||' x.MANAGER,'
912 ||' x.START_DATE- :v_dgmt,'
913 ||' x.END_DATE- :v_dgmt,'
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||')';
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;
929
930
931 --==================================================================
932
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
942 v_sql_stmt:=
943 ' INSERT INTO MSC_ST_BIS_PERIODS'
944 ||' ( ORGANIZATION_ID,'
945 ||' PERIOD_SET_NAME,'
949 ||' PERIOD_TYPE,'
946 ||' PERIOD_NAME,'
947 ||' START_DATE,'
948 ||' END_DATE,'
950 ||' PERIOD_YEAR,'
951 ||' PERIOD_NUM,'
952 ||' QUARTER_NUM,'
953 ||' ENTERED_PERIOD_NAME,'
954 ||' ADJUSTMENT_PERIOD_FLAG,'
955 ||' DESCRIPTION,'
956 ||' CONTEXT,'
957 ||' YEAR_START_DATE,'
958 ||' QUARTER_START_DATE,'
959 ||' REFRESH_ID,'
960 ||' SR_INSTANCE_ID)'
961 ||' SELECT'
962 ||' x.ORGANIZATION_ID,'
963 ||' x.PERIOD_SET_NAME,'
964 ||' x.PERIOD_NAME,'
965 ||' x.START_DATE,'
966 ||' x.END_DATE,'
967 ||' x.PERIOD_TYPE,'
968 ||' x.PERIOD_YEAR,'
969 ||' x.PERIOD_NUM,'
970 ||' x.QUARTER_NUM,'
971 ||' x.ENTERED_PERIOD_NAME,'
972 ||' x.ADJUSTMENT_PERIOD_FLAG,'
973 ||' x.DESCRIPTION,'
974 ||' x.CONTEXT,'
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
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;
989
990 END LOAD_BIS107;
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
1000 v_sql_stmt:=
1001 ' INSERT INTO MSC_ST_BIS_PERIODS'
1002 ||' ( ORGANIZATION_ID,'
1003 ||' PERIOD_SET_NAME,'
1004 ||' PERIOD_NAME,'
1005 ||' START_DATE,'
1006 ||' END_DATE,'
1007 ||' PERIOD_TYPE,'
1008 ||' PERIOD_YEAR,'
1009 ||' PERIOD_NUM,'
1010 ||' QUARTER_NUM,'
1011 ||' ENTERED_PERIOD_NAME,'
1012 ||' ADJUSTMENT_PERIOD_FLAG,'
1013 ||' DESCRIPTION,'
1014 ||' CONTEXT,'
1015 ||' YEAR_START_DATE,'
1016 ||' QUARTER_START_DATE,'
1017 ||' REFRESH_ID,'
1018 ||' SR_INSTANCE_ID)'
1019 ||' SELECT'
1020 ||' x.ORGANIZATION_ID,'
1021 ||' x.PERIOD_SET_NAME,'
1022 ||' x.PERIOD_NAME,'
1023 ||' x.START_DATE,'
1024 ||' x.END_DATE,'
1025 ||' x.PERIOD_TYPE,'
1026 ||' x.PERIOD_YEAR,'
1027 ||' x.PERIOD_NUM,'
1028 ||' x.QUARTER_NUM,'
1029 ||' x.ENTERED_PERIOD_NAME,'
1030 ||' x.ADJUSTMENT_PERIOD_FLAG,'
1031 ||' x.DESCRIPTION,'
1032 ||' x.CONTEXT,'
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;
1042
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'
1052 ||' ( MEASURE_ID,'
1053 ||' MEASURE_SHORT_NAME,'
1054 ||' MEASURE_NAME,'
1055 ||' DESCRIPTION,'
1056 ||' ORG_DIMENSION_ID,'
1057 ||' TIME_DIMENSION_ID,'
1058 ||' DIMENSION1_ID,'
1059 ||' DIMENSION2_ID,'
1060 ||' DIMENSION3_ID,'
1061 ||' DIMENSION4_ID,'
1062 ||' DIMENSION5_ID,'
1063 ||' UNIT_OF_MEASURE_CLASS,'
1064 ||' DELETED_FLAG,'
1065 ||' REFRESH_ID,'
1066 ||' SR_INSTANCE_ID)'
1067 ||' SELECT'
1068 ||' x.INDICATOR_ID,'
1069 ||' x.SHORT_NAME,'
1070 ||' x.NAME,'
1071 ||' x.DESCRIPTION,'
1072 ||' NULL ORG_DIMENSION_ID,' --
1073 ||' NULL TIME_DIMENSION_ID,' --
1074 ||' NULL DIMENSION1_ID,' --
1075 ||' NULL DIMENSION2_ID,' --
1076 ||' NULL DIMENSION3_ID,' --
1077 ||' NULL DIMENSION4_ID,'--
1078 ||' NULL DIMENSION5_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;
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,'
1095 ||' TARGET_LEVEL_SHORT_NAME,'
1096 ||' TARGET_LEVEL_NAME,'
1097 ||' DESCRIPTION,'
1098 ||' MEASURE_ID,'
1099 ||' ORG_LEVEL_ID,'
1100 ||' TIME_LEVEL_ID,'
1101 ||' DIMENSION1_LEVEL_ID,'
1102 ||' DIMENSION2_LEVEL_ID,'
1103 ||' DIMENSION3_LEVEL_ID,'
1104 ||' DIMENSION4_LEVEL_ID,'
1105 ||' DIMENSION5_LEVEL_ID,'
1106 ||' WORKFLOW_ITEM_TYPE,'
1107 ||' WORKFLOW_PROCESS_SHORT_NAME,'
1108 ||' DEFAULT_NOTIFY_RESP_ID,'
1109 ||' DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1110 ||' COMPUTING_FUNCTION_ID,'
1111 ||' REPORT_FUNCTION_ID,'
1112 ||' UNIT_OF_MEASURE,'
1113 ||' SYSTEM_FLAG,'
1114 ||' DELETED_FLAG,'
1115 ||' REFRESH_ID,'
1116 ||' SR_INSTANCE_ID)'
1117 ||' SELECT'
1118 ||' x.TARGET_LEVEL_ID,'
1119 ||' x.SHORT_NAME,'
1120 ||' x.NAME,'
1121 ||' x.DESCRIPTION,'
1122 ||' x.INDICATOR_ID MEASURE_ID,' --
1123 ||' x.ORG_LEVEL_ID,'
1124 ||' x.TIME_LEVEL_ID,'
1125 ||' x.DIMENSION1_LEVEL_ID,'
1126 ||' x.DIMENSION2_LEVEL_ID,'
1127 ||' x.DIMENSION3_LEVEL_ID,'
1128 ||' x.DIMENSION4_LEVEL_ID,'
1129 ||' x.DIMENSION5_LEVEL_ID,'
1130 ||' NULL WORKFLOW_ITEM_TYPE,'--
1134 ||' NULL COMPUTING_FUNCTION_ID,' --
1131 ||' x.WF_PROCESS,'
1132 ||' x.DEFAULT_ROLE_ID,'
1133 ||' x.DEFAULT_ROLE,'
1135 ||' NULL REPORT_FUNCTION_ID,' --
1136 ||' NULL UNIT_OF_MEASURE,' --
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;
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,'
1153 ||' TARGET_LEVEL_ID,'
1154 ||' BUSINESS_PLAN_ID,'
1155 ||' ORG_LEVEL_VALUE_ID,'
1156 ||' TIME_LEVEL_VALUE_ID,'
1157 ||' DIM1_LEVEL_VALUE_ID,'
1158 ||' DIM2_LEVEL_VALUE_ID,'
1159 ||' DIM3_LEVEL_VALUE_ID,'
1160 ||' DIM4_LEVEL_VALUE_ID,'
1161 ||' DIM5_LEVEL_VALUE_ID,'
1162 ||' TARGET,'
1163 ||' RANGE1_LOW,'
1164 ||' RANGE1_HIGH,'
1165 ||' RANGE2_LOW,'
1166 ||' RANGE2_HIGH,'
1167 ||' RANGE3_LOW,'
1168 ||' RANGE3_HIGH,'
1169 ||' NOTIFY_RESP1_ID,'
1170 ||' NOTIFY_RESP1_SHORT_NAME,'
1171 ||' NOTIFY_RESP2_ID,'
1172 ||' NOTIFY_RESP2_SHORT_NAME,'
1173 ||' NOTIFY_RESP3_ID,'
1174 ||' NOTIFY_RESP3_SHORT_NAME,'
1175 ||' DELETED_FLAG,'
1176 ||' REFRESH_ID,'
1177 ||' SR_INSTANCE_ID)'
1178 ||' SELECT'
1179 ||' x.TARGET_ID,'
1180 ||' x.TARGET_LEVEL_ID,'
1181 ||' x.PLAN_ID,'
1182 ||' x.ORG_LEVEL_VALUE,'
1183 ||' x.TIME_LEVEL_VALUE,'
1184 ||' x.DIMENSION1_LEVEL_VALUE,'
1185 ||' x.DIMENSION2_LEVEL_VALUE,'
1186 ||' x.DIMENSION3_LEVEL_VALUE,'
1187 ||' x.DIMENSION4_LEVEL_VALUE,'
1188 ||' x.DIMENSION5_LEVEL_VALUE,'
1189 ||' x.TARGET,'
1190 ||' x.RANGE1_LOW,'
1191 ||' x.RANGE1_HIGH,'
1192 ||' x.RANGE2_LOW,'
1193 ||' x.RANGE2_HIGH,'
1194 ||' x.RANGE3_LOW,'
1195 ||' x.RANGE3_HIGH,'
1196 ||' x.ROLE1_ID,'
1197 ||' x.ROLE1,'
1198 ||' x.ROLE2_ID,'
1199 ||' x.ROLE2,'
1200 ||' x.ROLE3_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;
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,'
1217 ||' SHORT_NAME,'
1218 ||' NAME,'
1219 ||' DESCRIPTION,'
1220 ||' VERSION_NO,'
1221 ||' CURRENT_PLAN_FLAG,'
1222 ||' DELETED_FLAG,'
1223 ||' REFRESH_ID,'
1224 ||' SR_INSTANCE_ID)'
1225 ||' SELECT'
1226 ||' x.PLAN_ID,'
1227 ||' x.SHORT_NAME,'
1228 ||' x.NAME,'
1229 ||' x.DESCRIPTION,'
1230 ||' x.VERSION_NO,'
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;
1240
1241 EXCEPTION
1242
1243 WHEN OTHERS THEN
1244
1245 ROLLBACK;
1246
1247 IF SQLCODE<> -942 THEN
1248 RAISE;
1249 END IF;
1250 END;
1251
1252 END IF;
1253
1254 END LOAD_BIS110;
1255
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
1265 v_sql_stmt:=
1266 ' INSERT INTO MSC_ST_BIS_PERIODS'
1267 ||' ( ORGANIZATION_ID,'
1268 ||' PERIOD_SET_NAME,'
1269 ||' PERIOD_NAME,'
1270 ||' START_DATE,'
1271 ||' END_DATE,'
1272 ||' PERIOD_TYPE,'
1273 ||' PERIOD_YEAR,'
1274 ||' PERIOD_NUM,'
1275 ||' QUARTER_NUM,'
1276 ||' ENTERED_PERIOD_NAME,'
1277 ||' ADJUSTMENT_PERIOD_FLAG,'
1278 ||' DESCRIPTION,'
1279 ||' CONTEXT,'
1280 ||' YEAR_START_DATE,'
1281 ||' QUARTER_START_DATE,'
1282 ||' REFRESH_ID,'
1283 ||' SR_INSTANCE_ID)'
1284 ||' SELECT'
1285 ||' x.ORGANIZATION_ID,'
1286 ||' x.PERIOD_SET_NAME,'
1287 ||' x.PERIOD_NAME,'
1288 ||' x.START_DATE,'
1289 ||' x.END_DATE,'
1290 ||' x.PERIOD_TYPE,'
1291 ||' x.PERIOD_YEAR,'
1292 ||' x.PERIOD_NUM,'
1293 ||' x.QUARTER_NUM,'
1294 ||' x.ENTERED_PERIOD_NAME,'
1295 ||' x.ADJUSTMENT_PERIOD_FLAG,'
1296 ||' x.DESCRIPTION,'
1297 ||' x.CONTEXT,'
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;
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,'
1316 ||' MEASURE_SHORT_NAME,'
1317 ||' MEASURE_NAME,'
1318 ||' DESCRIPTION,'
1319 ||' ORG_DIMENSION_ID,'
1320 ||' TIME_DIMENSION_ID,'
1321 ||' DIMENSION1_ID,'
1325 ||' DIMENSION5_ID,'
1322 ||' DIMENSION2_ID,'
1323 ||' DIMENSION3_ID,'
1324 ||' DIMENSION4_ID,'
1326 ||' UNIT_OF_MEASURE_CLASS,'
1327 ||' DELETED_FLAG,'
1328 ||' REFRESH_ID,'
1329 ||' SR_INSTANCE_ID)'
1330 ||' SELECT'
1331 ||' x.MEASURE_ID,'
1332 ||' x.MEASURE_SHORT_NAME,'
1333 ||' x.MEASURE_NAME,'
1334 ||' x.DESCRIPTION,'
1335 --||' x.ORG_DIMENSION_ID,' -- Old values as of version 115.92
1336 --||' x.TIME_DIMENSION_ID,' -- Old values as of version 115.92
1337 ||' NULL ORG_DIMENSION_ID,'
1338 ||' NULL TIME_DIMENSION_ID,'
1339 ||' x.DIMENSION1_ID,'
1340 ||' x.DIMENSION2_ID,'
1341 ||' x.DIMENSION3_ID,'
1342 ||' x.DIMENSION4_ID,'
1343 ||' x.DIMENSION5_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;
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,'
1360 ||' TARGET_LEVEL_SHORT_NAME,'
1361 ||' TARGET_LEVEL_NAME,'
1362 ||' DESCRIPTION,'
1363 ||' MEASURE_ID,'
1364 ||' ORG_LEVEL_ID,'
1365 ||' TIME_LEVEL_ID,'
1366 ||' DIMENSION1_LEVEL_ID,'
1367 ||' DIMENSION2_LEVEL_ID,'
1368 ||' DIMENSION3_LEVEL_ID,'
1369 ||' DIMENSION4_LEVEL_ID,'
1370 ||' DIMENSION5_LEVEL_ID,'
1371 ||' WORKFLOW_ITEM_TYPE,'
1372 ||' WORKFLOW_PROCESS_SHORT_NAME,'
1373 ||' DEFAULT_NOTIFY_RESP_ID,'
1374 ||' DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1375 ||' COMPUTING_FUNCTION_ID,'
1376 ||' REPORT_FUNCTION_ID,'
1377 ||' UNIT_OF_MEASURE,'
1378 ||' SYSTEM_FLAG,'
1379 ||' DELETED_FLAG,'
1380 ||' REFRESH_ID,'
1381 ||' SR_INSTANCE_ID)'
1382 ||' SELECT'
1383 ||' x.TARGET_LEVEL_ID,'
1384 ||' x.TARGET_LEVEL_SHORT_NAME,'
1385 ||' x.TARGET_LEVEL_NAME,'
1386 ||' x.DESCRIPTION,'
1387 ||' x.MEASURE_ID,'
1388 ||' x.ORG_LEVEL_ID,'
1389 ||' x.TIME_LEVEL_ID,'
1390 ||' x.DIMENSION1_LEVEL_ID,'
1391 ||' x.DIMENSION2_LEVEL_ID,'
1392 ||' x.DIMENSION3_LEVEL_ID,'
1393 ||' x.DIMENSION4_LEVEL_ID,'
1394 ||' x.DIMENSION5_LEVEL_ID,'
1395 ||' x.WORKFLOW_ITEM_TYPE,'
1396 ||' x.WORKFLOW_PROCESS_SHORT_NAME,'
1397 ||' x.DEFAULT_NOTIFY_RESP_ID,'
1398 ||' x.DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1399 ||' x.COMPUTING_FUNCTION_ID,'
1400 ||' x.REPORT_FUNCTION_ID,'
1401 ||' x.UNIT_OF_MEASURE,'
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;
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,'
1418 ||' TARGET_LEVEL_ID,'
1419 ||' BUSINESS_PLAN_ID,'
1420 ||' ORG_LEVEL_VALUE_ID,'
1421 ||' TIME_LEVEL_VALUE_ID,'
1422 ||' DIM1_LEVEL_VALUE_ID,'
1423 ||' DIM2_LEVEL_VALUE_ID,'
1424 ||' DIM3_LEVEL_VALUE_ID,'
1425 ||' DIM4_LEVEL_VALUE_ID,'
1426 ||' DIM5_LEVEL_VALUE_ID,'
1427 ||' TARGET,'
1428 ||' RANGE1_LOW,'
1429 ||' RANGE1_HIGH,'
1430 ||' RANGE2_LOW,'
1431 ||' RANGE2_HIGH,'
1432 ||' RANGE3_LOW,'
1433 ||' RANGE3_HIGH,'
1434 ||' NOTIFY_RESP1_ID,'
1435 ||' NOTIFY_RESP1_SHORT_NAME,'
1436 ||' NOTIFY_RESP2_ID,'
1437 ||' NOTIFY_RESP2_SHORT_NAME,'
1438 ||' NOTIFY_RESP3_ID,'
1439 ||' NOTIFY_RESP3_SHORT_NAME,'
1440 ||' DELETED_FLAG,'
1441 ||' REFRESH_ID,'
1442 ||' SR_INSTANCE_ID)'
1443 ||' SELECT'
1444 ||' x.TARGET_ID,'
1445 ||' x.TARGET_LEVEL_ID,'
1446 ||' x.PLAN_ID,'
1447 ||' x.ORG_LEVEL_VALUE_ID,'
1448 ||' x.TIME_LEVEL_VALUE_ID,'
1449 ||' x.DIM1_LEVEL_VALUE_ID,'
1450 ||' x.DIM2_LEVEL_VALUE_ID,'
1451 ||' x.DIM3_LEVEL_VALUE_ID,'
1452 ||' x.DIM4_LEVEL_VALUE_ID,'
1453 ||' x.DIM5_LEVEL_VALUE_ID,'
1454 ||' x.TARGET,'
1455 ||' x.RANGE1_LOW,'
1456 ||' x.RANGE1_HIGH,'
1457 ||' x.RANGE2_LOW,'
1458 ||' x.RANGE2_HIGH,'
1459 ||' x.RANGE3_LOW,'
1460 ||' x.RANGE3_HIGH,'
1461 ||' x.NOTIFY_RESP1_ID,'
1462 ||' x.NOTIFY_RESP1_SHORT_NAME,'
1463 ||' x.NOTIFY_RESP2_ID,'
1464 ||' x.NOTIFY_RESP2_SHORT_NAME,'
1465 ||' x.NOTIFY_RESP3_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;
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,'
1482 ||' SHORT_NAME,'
1483 ||' NAME,'
1484 ||' DESCRIPTION,'
1485 ||' VERSION_NO,'
1486 ||' CURRENT_PLAN_FLAG,'
1487 ||' DELETED_FLAG,'
1488 ||' REFRESH_ID,'
1489 ||' SR_INSTANCE_ID)'
1490 ||' SELECT'
1491 ||' x.PLAN_ID,'
1492 ||' x.SHORT_NAME,'
1493 ||' x.NAME,'
1494 ||' x.DESCRIPTION,'
1495 ||' x.VERSION_NO,'
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
1505
1502 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1503
1504 COMMIT;
1506 END IF;
1507
1508 END LOAD_BIS115;
1509
1510 --================ LOAD_ATP_RULES ====================================
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
1520 v_sql_stmt:=
1521 'INSERT INTO MSC_ST_ATP_RULES'
1522 ||' ( RULE_ID,'
1523 ||' RULE_NAME,'
1524 ||' DESCRIPTION,'
1525 ||' ACCUMULATE_AVAILABLE_FLAG,'
1526 ||' BACKWARD_CONSUMPTION_FLAG,'
1527 ||' FORWARD_CONSUMPTION_FLAG,'
1528 ||' PAST_DUE_DEMAND_CUTOFF_FENCE,'
1529 ||' PAST_DUE_SUPPLY_CUTOFF_FENCE,'
1530 ||' INFINITE_SUPPLY_FENCE_CODE,'
1531 ||' INFINITE_SUPPLY_TIME_FENCE,'
1532 ||' ACCEPTABLE_EARLY_FENCE,'
1533 ||' ACCEPTABLE_LATE_FENCE,'
1534 ||' DEFAULT_ATP_SOURCES,'
1535 ||' DEMAND_CLASS_ATP_FLAG,'
1536 ||' INCLUDE_SALES_ORDERS,'
1537 ||' INCLUDE_DISCRETE_WIP_DEMAND,'
1538 ||' INCLUDE_REP_WIP_DEMAND,'
1539 ||' INCLUDE_NONSTD_WIP_DEMAND,'
1540 ||' INCLUDE_DISCRETE_MPS,'
1541 ||' INCLUDE_USER_DEFINED_DEMAND,'
1542 ||' INCLUDE_PURCHASE_ORDERS,'
1543 ||' INCLUDE_DISCRETE_WIP_RECEIPTS,'
1544 ||' INCLUDE_REP_WIP_RECEIPTS,'
1545 ||' INCLUDE_NONSTD_WIP_RECEIPTS,'
1546 ||' INCLUDE_INTERORG_TRANSFERS,'
1547 ||' INCLUDE_ONHAND_AVAILABLE,'
1548 ||' INCLUDE_USER_DEFINED_SUPPLY,'
1549 ||' ACCUMULATION_WINDOW,'
1550 ||' INCLUDE_REP_MPS,'
1551 ||' INCLUDE_INTERNAL_REQS,'
1552 ||' INCLUDE_SUPPLIER_REQS,'
1553 ||' INCLUDE_INTERNAL_ORDERS,'
1554 ||' INCLUDE_FLOW_SCHEDULE_DEMAND,'
1555 ||' INCLUDE_FLOW_SCHEDULE_RECEIPTS,'
1556 ||' USER_ATP_SUPPLY_TABLE_NAME,'
1557 ||' USER_ATP_DEMAND_TABLE_NAME,'
1558 ||' MPS_DESIGNATOR,'
1559 ||' AGGREGATE_TIME_FENCE_CODE,'
1560 ||' AGGREGATE_TIME_FENCE,'
1561 ||' REFRESH_ID,'
1562 ||' SR_INSTANCE_ID)'
1563 ||' SELECT'
1564 ||' RULE_ID,'
1565 ||' RULE_NAME,'
1566 ||' DESCRIPTION,'
1567 ||' ACCUMULATE_AVAILABLE_FLAG,'
1568 ||' BACKWARD_CONSUMPTION_FLAG,'
1569 ||' FORWARD_CONSUMPTION_FLAG,'
1570 ||' PAST_DUE_DEMAND_CUTOFF_FENCE,'
1571 ||' PAST_DUE_SUPPLY_CUTOFF_FENCE,'
1572 ||' INFINITE_SUPPLY_FENCE_CODE,'
1573 ||' INFINITE_SUPPLY_TIME_FENCE,'
1574 ||' ACCEPTABLE_EARLY_FENCE,'
1575 ||' ACCEPTABLE_LATE_FENCE,'
1576 ||' DEFAULT_ATP_SOURCES,'
1577 ||' DEMAND_CLASS_ATP_FLAG,'
1578 ||' INCLUDE_SALES_ORDERS,'
1579 ||' INCLUDE_DISCRETE_WIP_DEMAND,'
1580 ||' INCLUDE_REP_WIP_DEMAND,'
1581 ||' INCLUDE_NONSTD_WIP_DEMAND,'
1582 ||' INCLUDE_DISCRETE_MPS,'
1583 ||' INCLUDE_USER_DEFINED_DEMAND,'
1584 ||' INCLUDE_PURCHASE_ORDERS,'
1585 ||' INCLUDE_DISCRETE_WIP_RECEIPTS,'
1586 ||' INCLUDE_REP_WIP_RECEIPTS,'
1587 ||' INCLUDE_NONSTD_WIP_RECEIPTS,'
1588 ||' INCLUDE_INTERORG_TRANSFERS,'
1589 ||' INCLUDE_ONHAND_AVAILABLE,'
1590 ||' INCLUDE_USER_DEFINED_SUPPLY,'
1591 ||' ACCUMULATION_WINDOW,'
1592 ||' INCLUDE_REP_MPS,'
1593 ||' INCLUDE_INTERNAL_REQS,'
1594 ||' INCLUDE_SUPPLIER_REQS,'
1595 ||' INCLUDE_INTERNAL_ORDERS,'
1596 ||' INCLUDE_FLOW_SCHEDULE_DEMAND,'
1597 ||' INCLUDE_FLOW_SCHEDULE_RECEIPTS,'
1598 ||' USER_ATP_SUPPLY_TABLE_NAME,'
1599 ||' USER_ATP_DEMAND_TABLE_NAME,'
1600 ||' MPS_DESIGNATOR,'
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;
1610
1611 END IF;
1612
1613 END LOAD_ATP_RULES;
1614
1615
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
1625 v_sql_stmt:=
1626 ' INSERT INTO MSC_ST_PLANNERS'
1627 ||'( PLANNER_CODE,'
1628 ||' ORGANIZATION_ID,'
1629 ||' DESCRIPTION,'
1630 ||' DISABLE_DATE,'
1631 ||' ELECTRONIC_MAIL_ADDRESS,'
1632 ||' EMPLOYEE_ID,'
1633 ||' CURRENT_EMPLOYEE_FLAG,'
1634 ||' USER_NAME,'
1635 ||' DELETED_FLAG,'
1636 ||' REFRESH_ID,'
1637 ||' SR_INSTANCE_ID)'
1638 ||' SELECT'
1639 ||' x.PLANNER_CODE,'
1640 ||' x.ORGANIZATION_ID,'
1641 ||' x.DESCRIPTION,'
1642 ||' x.DISABLE_DATE,'
1643 ||' x.ELECTRONIC_MAIL_ADDRESS,'
1644 ||' x.EMPLOYEE_ID,'
1645 ||' x.CURRENT_EMPLOYEE_FLAG,'
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
1655 COMMIT;
1656
1657 END IF;
1658
1659 END LOAD_PLANNERS;
1660
1661
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
1671 v_sql_stmt:=
1672 'insert into MSC_ST_DEMAND_CLASSES'
1673 ||' ( DEMAND_CLASS,'
1674 ||' MEANING,'
1675 ||' DESCRIPTION,'
1679 ||' DELETED_FLAG,'
1676 ||' FROM_DATE,'
1677 ||' TO_DATE,'
1678 ||' ENABLED_FLAG,'
1680 ||' REFRESH_ID,'
1681 ||' SR_INSTANCE_ID)'
1682 ||' select '
1683 --||' :V_ICODE||x.DEMAND_CLASS,'
1684 ||' x.DEMAND_CLASS,'
1685 ||' x.MEANING,'
1686 ||' x.DESCRIPTION,'
1687 ||' x.FROM_DATE,'
1688 ||' x.TO_DATE,'
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
1698 COMMIT;
1699
1700 END IF;
1701
1702 END LOAD_DEMAND_CLASS;
1703
1704 /* LOAD_TRIP added for Pulling Trips and Trip Stops for Deployment Planning Project */
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';
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,'
1719 ||' DELETED_FLAG,'
1720 ||' REFRESH_ID,'
1721 ||' SR_INSTANCE_ID)'
1722 ||' SELECT '
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
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,'
1740 ||' DELETED_FLAG,'
1741 ||' REFRESH_ID,'
1742 ||' SR_INSTANCE_ID)'
1743 ||' SELECT '
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
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'
1762 ||' ( TRIP_ID,'
1763 ||' NAME,'
1764 ||' SHIP_METHOD_CODE,'
1765 ||' PLANNED_FLAG,'
1766 ||' STATUS_CODE,'
1767 ||' WEIGHT_CAPACITY,'
1768 ||' WEIGHT_UOM,'
1769 ||' VOLUME_CAPACITY,'
1770 ||' VOLUME_UOM,'
1771 ||' DELETED_FLAG,'
1772 ||' REFRESH_ID,'
1773 ||' SR_INSTANCE_ID)'
1774 ||' SELECT '
1775 ||' x.TRIP_ID,'
1776 ||' x.NAME,'
1777 ||' x.SHIP_METHOD_CODE,'
1778 ||' x.PLANNED_FLAG,'
1779 ||' x.STATUS_CODE,'
1780 ||' x.WEIGHT_CAPACITY,'
1781 ||' x.WEIGHT_UOM,'
1782 ||' x.VOLUME_CAPACITY,'
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
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,'
1800 ||' STOP_LOCATION_ID,'
1801 ||' STATUS_CODE,'
1802 ||' STOP_SEQUENCE_NUMBER,'
1803 ||' PLANNED_ARRIVAL_DATE,'
1804 ||' PLANNED_DEPARTURE_DATE,'
1805 ||' TRIP_ID,'
1806 ||' DELETED_FLAG,'
1807 ||' REFRESH_ID,'
1808 ||' SR_INSTANCE_ID)'
1809 ||' SELECT '
1810 ||' x.STOP_ID,'
1811 ||' x.STOP_LOCATION_ID,'
1812 ||' x.STATUS_CODE,'
1813 ||' x.STOP_SEQUENCE_NUMBER,'
1814 ||' x.PLANNED_ARRIVAL_DATE,'
1815 ||' x.PLANNED_DEPARTURE_DATE,'
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
1825 COMMIT;
1826
1827 END IF; -- MSC_CL_PULL.TRIP_ENABLED
1828
1829 END LOAD_TRIP;
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';
1839
1840 v_sql_stmt:=
1841 'INSERT INTO MSC_ST_SR_LOOKUPS'
1842 ||' ( LOOKUP_TYPE,'
1843 ||' LOOKUP_CODE,'
1844 ||' MEANING,'
1845 ||' DESCRIPTION,'
1846 ||' FROM_DATE,'
1847 ||' TO_DATE,'
1848 ||' ENABLED_FLAG,'
1849 ||' DELETED_FLAG,'
1850 ||' REFRESH_ID,'
1851 ||' SR_INSTANCE_ID)'
1852 ||' SELECT '
1853 ||' ''SALES_CHANNEL'','
1854 ||' X. LOOKUP_CODE,'
1858 ||' X.TO_DATE,'
1855 ||' X.MEANING,'
1856 ||' X.DESCRIPTION,'
1857 ||' X.FROM_DATE,'
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;
1868
1869 END IF;
1870 END LOAD_SALES_CHANNEL;
1871
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';
1881
1882 v_sql_stmt:=
1883 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1884 ||' (CALENDAR_CODE,'
1885 ||' CALENDAR_TYPE,'
1886 ||' YEAR,'
1887 ||' YEAR_DESCRIPTION,'
1888 ||' YEAR_START_DATE,'
1889 ||' YEAR_END_DATE,'
1890 ||' QUARTER,'
1891 ||' QUARTER_DESCRIPTION,'
1892 ||' QUARTER_START_DATE ,'
1893 ||' QUARTER_END_DATE,'
1894 ||' MONTH,'
1895 ||' MONTH_DESCRIPTION,'
1896 ||' MONTH_START_DATE,'
1897 ||' MONTH_END_DATE,'
1898 ||' DELETED_FLAG,'
1899 ||' REFRESH_ID,'
1900 ||' SR_INSTANCE_ID)'
1901 ||' SELECT '
1902 ||' :V_ICODE||CALENDAR_CODE,'
1903 ||' ''FISCAL'','
1904 ||' X. YEAR,'
1905 ||' X. YEAR_DESCRIPTION,'
1906 ||' X. YEAR_START_DATE,'
1907 ||' X. YEAR_END_DATE ,'
1908 ||' X. QUARTER,'
1909 ||' X. QUARTER_DESCRIPTION,'
1910 ||' X. QUARTER_START_DATE,'
1911 ||' X. QUARTER_END_DATE ,'
1912 ||' X. MONTH,'
1913 ||' X. MONTH_DESCRIPTION,'
1914 ||' X. MONTH_START_DATE,'
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;
1924
1925 END IF;
1926 END LOAD_FISCAL_CALENDAR;
1927
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';
1937
1938 v_sql_stmt:=
1939 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1940 ||' ( SR_INSTANCE_ID,'
1941 ||' FROM_CURRENCY,'
1942 ||' TO_CURRENCY,'
1943 ||' CONV_DATE,'
1944 ||' CONV_TYPE,'
1945 ||' CONV_RATE,'
1946 ||' CREATION_DATE,'
1947 ||' RN,'
1948 ||' CREATED_BY,'
1949 ||' LAST_UPDATE_DATE,'
1950 ||' LAST_UPDATED_BY,'
1951 ||' LAST_UPDATE_LOGIN,'
1952 ||' DELETED_FLAG)'
1953 ||' SELECT '
1954 ||' :v_instance_id,'
1955 ||' XY.FROM_CURRENCY,'
1956 ||' XY.TO_CURRENCY,'
1957 ||' XY.CONVERSION_DATE,'
1958 ||' XY.CONVERSION_TYPE,'
1959 ||' XY.CONVERSION_RATE,'
1960 ||' XY.CREATION_DATE,'
1961 ||' :v_refresh_id,'
1962 ||' XY.CREATED_BY,'
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';
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;
1979 END LOAD_CURRENCY_CONVERSION;
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';
1989
1990 v_sql_stmt:=
1991 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1992 ||' ( SR_INSTANCE_ID,'
1993 ||' DELIVERY_DETAIL_ID,'
1994 ||' SOURCE_CODE ,'
1995 ||' SOURCE_HEADER_ID ,'
1996 ||' SOURCE_LINE_ID ,'
1997 ||' SOURCE_HEADER_NUMBER,'
1998 ||' SHIP_SET_ID,'
1999 ||' ARRIVAL_SET_ID,'
2000 ||' SHIP_FROM_LOCATION_ID,'
2001 ||' ORGANIZATION_ID,'
2002 ||' SHIP_TO_LOCATION_ID,'
2003 ||' SHIP_TO_SITE_USE_ID,'
2004 ||' DELIVER_TO_LOCATION_ID,'
2005 ||' DELIVER_TO_SITE_USE_ID,'
2006 ||' CANCELLED_QUANTITY,'
2007 ||' REQUESTED_QUANTITY,'
2008 ||' REQUESTED_QUANTITY_UOM,'
2009 ||' SHIPPED_QUANTITY,'
2010 ||' DELIVERED_QUANTITY,'
2011 ||' DATE_REQUESTED,'
2012 ||' DATE_SCHEDULED,'
2013 ||' OPERATING_UNIT,'
2014 ||' INV_INTERFACED_FLAG,'
2015 ||' EARLIEST_PICKUP_DATE,'
2016 ||' LATEST_PICKUP_DATE,'
2017 ||' EARLIEST_DROPOFF_DATE,'
2018 ||' LATEST_DROPOFF_DATE,'
2019 ||' REFRESH_NUMBER,'
2020 ||' LAST_UPDATE_DATE,'
2024 ||' LAST_UPDATE_LOGIN'
2021 ||' LAST_UPDATED_BY,'
2022 ||' CREATION_DATE,'
2023 ||' CREATED_BY,'
2025 ||' )'
2026 ||' SELECT '
2027 ||' :v_instance_id,'
2028 ||' XY.DELIVERY_DETAIL_ID,'
2029 ||' XY.SOURCE_CODE,'
2030 ||' XY.SOURCE_HEADER_ID,'
2031 ||' XY.SOURCE_LINE_ID,'
2032 ||' XY.SOURCE_HEADER_NUMBER,'
2033 ||' XY.SHIP_SET_ID,'
2034 ||' XY.ARRIVAL_SET_ID,'
2035 ||' XY.SHIP_FROM_LOCATION_ID,'
2036 ||' XY.ORGANIZATION_ID,'
2037 ||' XY.SHIP_TO_LOCATION_ID,'
2038 ||' XY.SHIP_TO_SITE_USE_ID,'
2039 ||' XY.DELIVER_TO_LOCATION_ID,'
2040 ||' XY.DELIVER_TO_SITE_USE_ID,'
2041 ||' XY.CANCELLED_QUANTITY,'
2042 ||' XY.REQUESTED_QUANTITY,'
2043 ||' XY.REQUESTED_QUANTITY_UOM,'
2044 ||' XY.SHIPPED_QUANTITY,'
2045 ||' XY.DELIVERED_QUANTITY,'
2046 ||' XY.DATE_REQUESTED,'
2047 ||' XY.DATE_SCHEDULED,'
2048 ||' XY.ORG_ID,'
2049 ||' XY.INV_INTERFACED_FLAG,'
2050 ||' XY.EARLIEST_PICKUP_DATE,'
2051 ||' XY.LATEST_PICKUP_DATE,'
2052 ||' XY.EARLIEST_DROPOFF_DATE,'
2053 ||' XY.LATEST_DROPOFF_DATE,'
2054 ||' :v_refresh_id,'
2055 ||' XY.LAST_UPDATE_DATE,'
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
2065 USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2066
2067 COMMIT;
2068
2069 END IF;
2070 END LOAD_DELIVERY_DETAILS;
2071
2072 /* bug9791058 */
2073 PROCEDURE LOAD_IB_CONTRACTS
2074 is
2075 lv_sql_stmt VARCHAR2(1000);
2076 v_condition_sql VARCHAR2(200);
2077 v_last_ibuc_coll_date DATE ;
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
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 ;
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,'
2103 ||' ZONE,'
2104 ||' SAMPLE_DATE,'
2105 ||' QUANTITY,'
2106 ||' REGION_ID,'
2107 ||' SR_INSTANCE_ID,'
2108 ||' REFRESH_ID,'
2109 ||' LAST_UPDATE_DATE,'
2110 ||' LAST_UPDATED_BY,'
2111 ||' CREATION_DATE,'
2112 ||' CREATED_BY)'
2113 ||' SELECT'
2114 ||' x.SR_INVENTORY_ITEM_ID,'
2115 ||' x.ZONE,'
2116 ||' x.SAMPLE_DATE,'
2117 ||' x.QUANTITY,'
2118 ||' x.REGION_ID,'
2119 ||' :v_instance_id,'
2120 ||' :v_refresh_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
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
2138 END IF ;
2139
2140 COMMIT;
2141 END IF;
2142 END LOAD_IB_CONTRACTS;
2143
2144
2145 PROCEDURE LOAD_SHORT_TEXT
2146 is
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
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,'
2162 ||' SEQ_NUM,'
2163 ||' ENTITY_NAME,'
2164 ||' PK_VALUE1,'
2165 ||' PK_VALUE2,'
2166 ||' PK_VALUE3,'
2167 ||' PK_VALUE4,'
2168 ||' PK_VALUE5,'
2169 ||' TITLE,'
2170 ||' DESCRIPTION,'
2171 ||' DOC_TYPE,'
2172 ||' START_DATE_ACTIVE,'
2173 ||' END_DATE_ACTIVE,'
2174 ||' URL,'
2175 ||' MEDIA_ID,'
2176 ||' SR_INSTANCE_ID,'
2177 ||' REFRESH_ID,'
2178 ||' LAST_UPDATE_DATE,'
2179 ||' LAST_UPDATED_BY,'
2180 ||' CREATION_DATE,'
2181 ||' CREATED_BY,'
2182 ||' LAST_UPDATE_LOGIN)'
2183 ||' SELECT '
2184 ||' FAD.DOCUMENT_ID,'
2185 ||' FAD.SEQ_NUM,'
2186 ||' FAD.ENTITY_NAME,'
2187 ||' FAD.PK1_VALUE,'
2188 ||' FAD.PK2_VALUE,'
2189 ||' FAD.PK3_VALUE,'
2190 ||' FAD.PK4_VALUE,'
2191 ||' FAD.PK5_VALUE,'
2192 ||' FAD.TITLE,'
2193 ||' FAD.DESCRIPTION,'
2194 ||' FAD.DATATYPE_ID,'
2195 ||' FAD.START_DATE_ACTIVE,'
2196 ||' FAD.END_DATE_ACTIVE,'
2197 ||' FAD.URL,'
2198 ||' FAD.MEDIA_ID,'
2199 ||' :v_instance_id,'
2200 ||' :v_refresh_id,'
2201 ||' FAD.LAST_UPDATE_DATE,'
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
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,'
2218 ||' SR_INSTANCE_ID,'
2219 ||' REFRESH_ID,'
2220 ||' LAST_UPDATE_DATE,'
2221 ||' LAST_UPDATED_BY,'
2222 ||' CREATION_DATE,'
2223 ||' CREATED_BY,'
2224 ||' LAST_UPDATE_LOGIN)'
2225 ||' SELECT'
2226 ||' MST.MEDIA_ID,'
2227 ||' MST.SHORT_TEXT,'
2228 ||' :v_instance_id,'
2229 ||' :v_refresh_id,'
2230 ||' MST.LAST_UPDATE_DATE,'
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
2240 END IF;
2241 END IF;
2242
2243 END LOAD_SHORT_TEXT;
2244
2245
2246 PROCEDURE LOAD_LONG_TEXT
2247 is
2248 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
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';
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:=
2263 ' INSERT INTO MSC_ST_LONG_TEXT '
2264 ||'( MEDIA_ID,'
2265 ||' LONG_TEXT,'
2266 ||' SR_INSTANCE_ID,'
2267 ||' REFRESH_ID,'
2268 ||' LAST_UPDATE_DATE,'
2269 ||' LAST_UPDATED_BY,'
2270 ||' CREATION_DATE,'
2271 ||' CREATED_BY,'
2272 ||' LAST_UPDATE_LOGIN)'
2273 ||' SELECT'
2274 ||' MLT.MEDIA_ID,'
2275 ||' MLT.LONG_TEXT,' --Removed the conversion TO_LOB w.r.t bug 13628509
2276 ||' :v_instance_id,'
2277 ||' :v_refresh_id,'
2278 ||' MLT.LAST_UPDATE_DATE,'
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;
2288 COMMIT;
2289
2290 END IF;
2291
2292 END IF;
2293
2294 END LOAD_LONG_TEXT;
2295
2296 END MSC_CL_OTHER_PULL;