[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 ||' x.Category_Name,'
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'
395 ||' AND (x.TO_ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
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)'
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
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;
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,'
534 ||' LAST_UPDATE_DATE,'
531 ||' CREATED_BY,'
532 ||' CREATION_DATE,'
533 ||' LAST_UPDATED_BY,'
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;
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
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 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
693
697
694 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'MSC_UTIL.G_COLLECT_SRP_DATA = Yes');
695 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
696 MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_NEW_V';
698 v_sql_stmt:=
699 ' insert into MSC_ST_SUB_INVENTORIES'
700 ||' ( ORGANIZATION_ID,'
701 ||' SUB_INVENTORY_CODE,'
702 ||' DESCRIPTION,'
703 ||' DISABLE_DATE,'
704 ||' NETTING_TYPE,'
705 ||' INVENTORY_ATP_CODE,'
706 ||' DEMAND_CLASS,'
707 ||' PROJECT_ID,'
708 ||' TASK_ID,'
709 ||' DELETED_FLAG,'
710 ||' REFRESH_ID,'
711 ||' SR_INSTANCE_ID,'
712 ||' condition_type,'
713 ||' SR_RESOURCE_NAME,'
714 ||' SR_CUSTOMER_ACCT_ID)'
715 ||' select'
716 ||' x.ORGANIZATION_ID,'
717 ||' x.SECONDARY_INVENTORY_NAME,'
718 ||' x.DESCRIPTION,'
719 ||' x.DISABLE_DATE- :v_dgmt,'
720 ||' x.NETTING_TYPE,'
721 ||' x.INVENTORY_ATP_CODE,'
722 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
723 ||' x.DEMAND_CLASS,'
724 ||' x.PROJECT_ID,'
725 ||' x.TASK_ID,'
726 ||' 2,'
727 ||' :v_refresh_id,'
728 ||' :v_instance_id,'
729 ||' x.condition_type,'
730 ||' x.SR_RESOURCE_NAME,'
731 ||' x.SR_CUSTOMER_ACCT_ID'
732 ||' from MRP_AP_SUB_INVENTORIES_NEW_V'||MSC_CL_PULL.v_dblink||' x'
733 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
734 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
735 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
736 else
737
738 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUB_INVENTORIES';
739 MSC_CL_PULL.v_view_name := 'MRP_AP_SUB_INVENTORIES_V';
740
741 v_sql_stmt:=
742 ' insert into MSC_ST_SUB_INVENTORIES'
743 ||' ( ORGANIZATION_ID,'
744 ||' SUB_INVENTORY_CODE,'
745 ||' DESCRIPTION,'
746 ||' DISABLE_DATE,'
747 ||' NETTING_TYPE,'
748 ||' INVENTORY_ATP_CODE,'
749 ||' DEMAND_CLASS,'
750 ||' PROJECT_ID,'
751 ||' TASK_ID,'
752 ||' DELETED_FLAG,'
753 ||' REFRESH_ID,'
754 ||' SR_INSTANCE_ID)'
755 ||' select'
756 ||' x.ORGANIZATION_ID,'
757 ||' x.SECONDARY_INVENTORY_NAME,'
758 ||' x.DESCRIPTION,'
759 ||' x.DISABLE_DATE- :v_dgmt,'
760 ||' x.NETTING_TYPE,'
761 ||' x.INVENTORY_ATP_CODE,'
762 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
763 ||' x.DEMAND_CLASS,'
764 ||' x.PROJECT_ID,'
765 ||' x.TASK_ID,'
766 ||' 2,'
767 ||' :v_refresh_id,'
768 ||' :v_instance_id'
769 ||' from MRP_AP_SUB_INVENTORIES_V'||MSC_CL_PULL.v_dblink||' x'
770 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
771 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
772 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
773
774 end if;
775
776 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
777 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
778
779 COMMIT;
780
781 END LOAD_SUB_INVENTORY;
782
783
784 --==================================================================
785
786 PROCEDURE LOAD_UNIT_NUMBER IS
787 BEGIN
788
789 IF MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS107 AND MSC_CL_PULL.v_apps_ver<> MSC_UTIL.G_APPS110 THEN
790
791 MSC_CL_PULL.v_table_name:= 'MSC_ST_UNIT_NUMBERS';
792 MSC_CL_PULL.v_view_name := 'MRP_AP_UNIT_NUMBERS_V';
793
794 v_sql_stmt:=
795 ' insert into MSC_ST_Unit_Numbers'
796 ||' ( UNIT_NUMBER,'
797 ||' END_ITEM_ID,'
798 ||' MASTER_ORGANIZATION_ID,'
799 ||' COMMENTS,'
800 ||' DELETED_FLAG,'
801 ||' REFRESH_ID,'
802 ||' SR_INSTANCE_ID)'
803 ||' select'
804 ||' x.UNIT_NUMBER,'
805 ||' x.END_ITEM_ID,'
806 ||' x.MASTER_ORGANIZATION_ID,'
807 ||' x.COMMENTS,'
808 ||' 2,'
809 ||' :v_refresh_id,'
810 ||' :v_instance_id'
811 ||' from MRP_AP_Unit_Numbers_V'||MSC_CL_PULL.v_dblink||' x'
812 ||' WHERE x.MASTER_ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
813 ||' AND (' --x.RN1>'||MSC_CL_PULL.v_lrn
814 ||' x.RN2>'||MSC_CL_PULL.v_lrn||')';
815 --||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
816
817 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
818
819 COMMIT;
820
821 END IF; -- MSC_CL_PULL.v_apps_ver
822
823 END LOAD_UNIT_NUMBER;
824
825 --==================================================================
826
827 PROCEDURE LOAD_PROJECT IS
828 BEGIN
832
829
830 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECTS';
831 MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECTS_V';
833 v_sql_stmt:=
834 ' insert into MSC_ST_PROJECTS'
835 ||' ( PROJECT_ID,'
836 ||' ORGANIZATION_ID,'
837 ||' PLANNING_GROUP,'
838 ||' COSTING_GROUP_ID,'
839 ||' MATERIAL_ACCOUNT,'
840 ||' WIP_ACCT_CLASS_CODE,'
841 ||' SEIBAN_NUMBER_FLAG,'
842 ||' PROJECT_NAME,'
843 ||' PROJECT_NUMBER,'
844 ||' PROJECT_NUMBER_SORT_ORDER,'
845 ||' PROJECT_DESCRIPTION,'
846 ||' START_DATE,'
847 ||' COMPLETION_DATE,'
848 ||' OPERATING_UNIT,'
849 ||' MANAGER_CONTACT,'
850 ||' DELETED_FLAG,'
851 ||' REFRESH_ID,'
852 ||' SR_INSTANCE_ID)'
853 ||' select'
854 ||' x.PROJECT_ID,'
855 ||' x.ORGANIZATION_ID,'
856 ||' x.PLANNING_GROUP,'
857 ||' x.COSTING_GROUP_ID,'
858 ||' x.MATERIAL_ACCOUNT,'
859 ||' x.WIP_ACCT_CLASS_CODE,'
860 ||' x.SEIBAN_NUMBER_FLAG,'
861 ||' x.PROJECT_NAME,'
862 ||' x.PROJECT_NUMBER,'
863 ||' x.PROJECT_NUMBER_SORT_ORDER,'
864 ||' x.PROJECT_DESCRIPTION,'
865 ||' x.START_DATE- :v_dgmt,'
866 ||' x.COMPLETION_DATE- :v_dgmt,'
867 ||' x.OPERATING_UNIT,'
868 ||' x.MANAGER_CONTACT,'
869 ||' 2,'
870 ||' :v_refresh_id,'
871 ||' :v_instance_id'
872 ||' from MRP_AP_PROJECTS_V'||MSC_CL_PULL.v_dblink||' x'
873 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
874 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
875 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
876 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
877
878 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
879
880 COMMIT;
881
882 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROJECT_TASKS';
883 MSC_CL_PULL.v_view_name := 'MRP_AP_PROJECT_TASKS_V';
884
885 v_sql_stmt:=
886 'insert into MSC_ST_PROJECT_TASKS'
887 ||' ( ORGANIZATION_ID,'
888 ||' PROJECT_ID,'
889 ||' TASK_ID,'
890 ||' TASK_NUMBER,'
891 ||' TASK_NAME,'
892 ||' DESCRIPTION,'
893 ||' MANAGER,'
894 ||' START_DATE,'
895 ||' END_DATE,'
896 ||' MANAGER_CONTACT,'
897 ||' DELETED_FLAG,'
898 ||' REFRESH_ID,'
899 ||' SR_INSTANCE_ID)'
900 ||' select'
901 ||' x.ORGANIZATION_ID,'
902 ||' x.PROJECT_ID,'
903 ||' x.TASK_ID,'
904 ||' x.TASK_NUMBER,'
905 ||' x.TASK_NAME,'
906 ||' x.DESCRIPTION,'
907 ||' x.MANAGER,'
908 ||' x.START_DATE- :v_dgmt,'
909 ||' x.END_DATE- :v_dgmt,'
910 ||' x.MANAGER_CONTACT,'
911 ||' 2,'
912 ||' :v_refresh_id,'
913 ||' :v_instance_id'
914 ||' from MRP_AP_PROJECT_TASKS_V'||MSC_CL_PULL.v_dblink||' x'
915 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
916 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
917 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
918 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
919
920 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
921
922 COMMIT;
923
924 END LOAD_PROJECT;
925
926
927 --==================================================================
928
929
930 PROCEDURE LOAD_BIS107 IS
931 BEGIN
932
933 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
934
935 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
936 MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
937
938 v_sql_stmt:=
939 ' INSERT INTO MSC_ST_BIS_PERIODS'
940 ||' ( ORGANIZATION_ID,'
941 ||' PERIOD_SET_NAME,'
942 ||' PERIOD_NAME,'
943 ||' START_DATE,'
944 ||' END_DATE,'
945 ||' PERIOD_TYPE,'
946 ||' PERIOD_YEAR,'
947 ||' PERIOD_NUM,'
948 ||' QUARTER_NUM,'
949 ||' ENTERED_PERIOD_NAME,'
950 ||' ADJUSTMENT_PERIOD_FLAG,'
951 ||' DESCRIPTION,'
952 ||' CONTEXT,'
953 ||' YEAR_START_DATE,'
954 ||' QUARTER_START_DATE,'
955 ||' REFRESH_ID,'
956 ||' SR_INSTANCE_ID)'
957 ||' SELECT'
958 ||' x.ORGANIZATION_ID,'
959 ||' x.PERIOD_SET_NAME,'
960 ||' x.PERIOD_NAME,'
961 ||' x.START_DATE,'
962 ||' x.END_DATE,'
963 ||' x.PERIOD_TYPE,'
964 ||' x.PERIOD_YEAR,'
965 ||' x.PERIOD_NUM,'
966 ||' x.QUARTER_NUM,'
967 ||' x.ENTERED_PERIOD_NAME,'
968 ||' x.ADJUSTMENT_PERIOD_FLAG,'
969 ||' x.DESCRIPTION,'
970 ||' x.CONTEXT,'
971 ||' x.YEAR_START_DATE,'
972 ||' x.QUARTER_START_DATE,'
973 ||' :v_refresh_id,'
974 ||' :v_instance_id'
975 ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
976 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
977 ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
978
979
980 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
981
982 COMMIT;
983
984 END IF;
985
986 END LOAD_BIS107;
987
988 PROCEDURE LOAD_BIS110 IS
989 BEGIN
990
994 MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
991 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
992
993 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
995
996 v_sql_stmt:=
997 ' INSERT INTO MSC_ST_BIS_PERIODS'
998 ||' ( ORGANIZATION_ID,'
999 ||' PERIOD_SET_NAME,'
1000 ||' PERIOD_NAME,'
1001 ||' START_DATE,'
1002 ||' END_DATE,'
1003 ||' PERIOD_TYPE,'
1004 ||' PERIOD_YEAR,'
1005 ||' PERIOD_NUM,'
1006 ||' QUARTER_NUM,'
1007 ||' ENTERED_PERIOD_NAME,'
1008 ||' ADJUSTMENT_PERIOD_FLAG,'
1009 ||' DESCRIPTION,'
1010 ||' CONTEXT,'
1011 ||' YEAR_START_DATE,'
1012 ||' QUARTER_START_DATE,'
1013 ||' REFRESH_ID,'
1014 ||' SR_INSTANCE_ID)'
1015 ||' SELECT'
1016 ||' x.ORGANIZATION_ID,'
1017 ||' x.PERIOD_SET_NAME,'
1018 ||' x.PERIOD_NAME,'
1019 ||' x.START_DATE,'
1020 ||' x.END_DATE,'
1021 ||' x.PERIOD_TYPE,'
1022 ||' x.PERIOD_YEAR,'
1023 ||' x.PERIOD_NUM,'
1024 ||' x.QUARTER_NUM,'
1025 ||' x.ENTERED_PERIOD_NAME,'
1026 ||' x.ADJUSTMENT_PERIOD_FLAG,'
1027 ||' x.DESCRIPTION,'
1028 ||' x.CONTEXT,'
1029 ||' x.YEAR_START_DATE,'
1030 ||' x.QUARTER_START_DATE,'
1031 ||' :v_refresh_id,'
1032 ||' :v_instance_id'
1033 ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1034 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1035 ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1036
1037 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1038
1039 COMMIT;
1040
1041 BEGIN
1042
1043 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1044 MSC_CL_PULL.v_view_name := 'BIS_INDICATORS_VL';
1045
1046 v_sql_stmt:=
1047 ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1048 ||' ( MEASURE_ID,'
1049 ||' MEASURE_SHORT_NAME,'
1050 ||' MEASURE_NAME,'
1051 ||' DESCRIPTION,'
1052 ||' ORG_DIMENSION_ID,'
1053 ||' TIME_DIMENSION_ID,'
1054 ||' DIMENSION1_ID,'
1055 ||' DIMENSION2_ID,'
1056 ||' DIMENSION3_ID,'
1057 ||' DIMENSION4_ID,'
1058 ||' DIMENSION5_ID,'
1059 ||' UNIT_OF_MEASURE_CLASS,'
1060 ||' DELETED_FLAG,'
1061 ||' REFRESH_ID,'
1062 ||' SR_INSTANCE_ID)'
1063 ||' SELECT'
1064 ||' x.INDICATOR_ID,'
1065 ||' x.SHORT_NAME,'
1066 ||' x.NAME,'
1067 ||' x.DESCRIPTION,'
1068 ||' NULL ORG_DIMENSION_ID,' --
1069 ||' NULL TIME_DIMENSION_ID,' --
1070 ||' NULL DIMENSION1_ID,' --
1071 ||' NULL DIMENSION2_ID,' --
1072 ||' NULL DIMENSION3_ID,' --
1073 ||' NULL DIMENSION4_ID,'--
1074 ||' NULL DIMENSION5_ID,'--
1075 ||' NULL UNIT_OF_MEASURE_CLASS,' --
1076 ||' 2,'
1077 ||' :v_refresh_id,'
1078 ||' :v_instance_id'
1079 ||' FROM BIS_INDICATORS_VL'||MSC_CL_PULL.v_dblink||' x';
1080
1081 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1082
1083 COMMIT;
1084
1085 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1086 MSC_CL_PULL.v_view_name := 'BIS_TARGET_LEVELS_VL';
1087
1088 v_sql_stmt:=
1089 ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1090 ||' ( TARGET_LEVEL_ID,'
1091 ||' TARGET_LEVEL_SHORT_NAME,'
1092 ||' TARGET_LEVEL_NAME,'
1093 ||' DESCRIPTION,'
1094 ||' MEASURE_ID,'
1095 ||' ORG_LEVEL_ID,'
1096 ||' TIME_LEVEL_ID,'
1097 ||' DIMENSION1_LEVEL_ID,'
1098 ||' DIMENSION2_LEVEL_ID,'
1099 ||' DIMENSION3_LEVEL_ID,'
1100 ||' DIMENSION4_LEVEL_ID,'
1101 ||' DIMENSION5_LEVEL_ID,'
1102 ||' WORKFLOW_ITEM_TYPE,'
1103 ||' WORKFLOW_PROCESS_SHORT_NAME,'
1104 ||' DEFAULT_NOTIFY_RESP_ID,'
1105 ||' DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1106 ||' COMPUTING_FUNCTION_ID,'
1107 ||' REPORT_FUNCTION_ID,'
1108 ||' UNIT_OF_MEASURE,'
1109 ||' SYSTEM_FLAG,'
1110 ||' DELETED_FLAG,'
1111 ||' REFRESH_ID,'
1112 ||' SR_INSTANCE_ID)'
1113 ||' SELECT'
1114 ||' x.TARGET_LEVEL_ID,'
1115 ||' x.SHORT_NAME,'
1116 ||' x.NAME,'
1117 ||' x.DESCRIPTION,'
1118 ||' x.INDICATOR_ID MEASURE_ID,' --
1119 ||' x.ORG_LEVEL_ID,'
1120 ||' x.TIME_LEVEL_ID,'
1121 ||' x.DIMENSION1_LEVEL_ID,'
1122 ||' x.DIMENSION2_LEVEL_ID,'
1123 ||' x.DIMENSION3_LEVEL_ID,'
1124 ||' x.DIMENSION4_LEVEL_ID,'
1125 ||' x.DIMENSION5_LEVEL_ID,'
1126 ||' NULL WORKFLOW_ITEM_TYPE,'--
1127 ||' x.WF_PROCESS,'
1128 ||' x.DEFAULT_ROLE_ID,'
1129 ||' x.DEFAULT_ROLE,'
1130 ||' NULL COMPUTING_FUNCTION_ID,' --
1131 ||' NULL REPORT_FUNCTION_ID,' --
1132 ||' NULL UNIT_OF_MEASURE,' --
1133 ||' x.SYSTEM_FLAG,'
1134 ||' 2,'
1135 ||' :v_refresh_id,'
1136 ||' :v_instance_id'
1137 ||' FROM BIS_TARGET_LEVELS_VL'||MSC_CL_PULL.v_dblink||' x';
1138
1139 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1140
1141 COMMIT;
1142
1143 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1144 MSC_CL_PULL.v_view_name := 'BIS_TARGET_VALUES_V';
1145
1146 v_sql_stmt:=
1147 ' INSERT INTO MSC_ST_BIS_TARGETS'
1148 ||' ( TARGET_ID,'
1149 ||' TARGET_LEVEL_ID,'
1150 ||' BUSINESS_PLAN_ID,'
1151 ||' ORG_LEVEL_VALUE_ID,'
1152 ||' TIME_LEVEL_VALUE_ID,'
1153 ||' DIM1_LEVEL_VALUE_ID,'
1157 ||' DIM5_LEVEL_VALUE_ID,'
1154 ||' DIM2_LEVEL_VALUE_ID,'
1155 ||' DIM3_LEVEL_VALUE_ID,'
1156 ||' DIM4_LEVEL_VALUE_ID,'
1158 ||' TARGET,'
1159 ||' RANGE1_LOW,'
1160 ||' RANGE1_HIGH,'
1161 ||' RANGE2_LOW,'
1162 ||' RANGE2_HIGH,'
1163 ||' RANGE3_LOW,'
1164 ||' RANGE3_HIGH,'
1165 ||' NOTIFY_RESP1_ID,'
1166 ||' NOTIFY_RESP1_SHORT_NAME,'
1167 ||' NOTIFY_RESP2_ID,'
1168 ||' NOTIFY_RESP2_SHORT_NAME,'
1169 ||' NOTIFY_RESP3_ID,'
1170 ||' NOTIFY_RESP3_SHORT_NAME,'
1171 ||' DELETED_FLAG,'
1172 ||' REFRESH_ID,'
1173 ||' SR_INSTANCE_ID)'
1174 ||' SELECT'
1175 ||' x.TARGET_ID,'
1176 ||' x.TARGET_LEVEL_ID,'
1177 ||' x.PLAN_ID,'
1178 ||' x.ORG_LEVEL_VALUE,'
1179 ||' x.TIME_LEVEL_VALUE,'
1180 ||' x.DIMENSION1_LEVEL_VALUE,'
1181 ||' x.DIMENSION2_LEVEL_VALUE,'
1182 ||' x.DIMENSION3_LEVEL_VALUE,'
1183 ||' x.DIMENSION4_LEVEL_VALUE,'
1184 ||' x.DIMENSION5_LEVEL_VALUE,'
1185 ||' x.TARGET,'
1186 ||' x.RANGE1_LOW,'
1187 ||' x.RANGE1_HIGH,'
1188 ||' x.RANGE2_LOW,'
1189 ||' x.RANGE2_HIGH,'
1190 ||' x.RANGE3_LOW,'
1191 ||' x.RANGE3_HIGH,'
1192 ||' x.ROLE1_ID,'
1193 ||' x.ROLE1,'
1194 ||' x.ROLE2_ID,'
1195 ||' x.ROLE2,'
1196 ||' x.ROLE3_ID,'
1197 ||' x.ROLE3,'
1198 ||' 2,'
1199 ||' :v_refresh_id,'
1200 ||' :v_instance_id'
1201 ||' FROM BIS_TARGET_VALUES_V'||MSC_CL_PULL.v_dblink||' x';
1202
1203 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1204
1205 COMMIT;
1206
1207 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1208 MSC_CL_PULL.v_view_name := 'BIS_BUSINESS_PLANS_VL';
1209
1210 v_sql_stmt:=
1211 ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1212 ||' ( BUSINESS_PLAN_ID,'
1213 ||' SHORT_NAME,'
1214 ||' NAME,'
1215 ||' DESCRIPTION,'
1216 ||' VERSION_NO,'
1217 ||' CURRENT_PLAN_FLAG,'
1218 ||' DELETED_FLAG,'
1219 ||' REFRESH_ID,'
1220 ||' SR_INSTANCE_ID)'
1221 ||' SELECT'
1222 ||' x.PLAN_ID,'
1223 ||' x.SHORT_NAME,'
1224 ||' x.NAME,'
1225 ||' x.DESCRIPTION,'
1226 ||' x.VERSION_NO,'
1227 ||' x.CURRENT_PLAN_FLAG,'
1228 ||' 2,'
1229 ||' :v_refresh_id,'
1230 ||' :v_instance_id'
1231 ||' FROM BIS_BUSINESS_PLANS_VL'||MSC_CL_PULL.v_dblink||' x';
1232
1233 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1234
1235 COMMIT;
1236
1237 EXCEPTION
1238
1239 WHEN OTHERS THEN
1240
1241 ROLLBACK;
1242
1243 IF SQLCODE<> -942 THEN
1244 RAISE;
1245 END IF;
1246 END;
1247
1248 END IF;
1249
1250 END LOAD_BIS110;
1251
1252
1253 PROCEDURE LOAD_BIS115 IS
1254 BEGIN
1255
1256 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1257
1258 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PERIODS';
1259 MSC_CL_PULL.v_view_name := 'MRP_AP_BIS_PERIODS_V';
1260
1261 v_sql_stmt:=
1262 ' INSERT INTO MSC_ST_BIS_PERIODS'
1263 ||' ( ORGANIZATION_ID,'
1264 ||' PERIOD_SET_NAME,'
1265 ||' PERIOD_NAME,'
1266 ||' START_DATE,'
1267 ||' END_DATE,'
1268 ||' PERIOD_TYPE,'
1269 ||' PERIOD_YEAR,'
1270 ||' PERIOD_NUM,'
1271 ||' QUARTER_NUM,'
1272 ||' ENTERED_PERIOD_NAME,'
1273 ||' ADJUSTMENT_PERIOD_FLAG,'
1274 ||' DESCRIPTION,'
1275 ||' CONTEXT,'
1276 ||' YEAR_START_DATE,'
1277 ||' QUARTER_START_DATE,'
1278 ||' REFRESH_ID,'
1279 ||' SR_INSTANCE_ID)'
1280 ||' SELECT'
1281 ||' x.ORGANIZATION_ID,'
1282 ||' x.PERIOD_SET_NAME,'
1283 ||' x.PERIOD_NAME,'
1284 ||' x.START_DATE,'
1285 ||' x.END_DATE,'
1286 ||' x.PERIOD_TYPE,'
1287 ||' x.PERIOD_YEAR,'
1288 ||' x.PERIOD_NUM,'
1289 ||' x.QUARTER_NUM,'
1290 ||' x.ENTERED_PERIOD_NAME,'
1291 ||' x.ADJUSTMENT_PERIOD_FLAG,'
1292 ||' x.DESCRIPTION,'
1293 ||' x.CONTEXT,'
1294 ||' x.YEAR_START_DATE,'
1295 ||' x.QUARTER_START_DATE,'
1296 ||' :v_refresh_id,'
1297 ||' :v_instance_id'
1298 ||' FROM MRP_AP_BIS_PERIODS_V'||MSC_CL_PULL.v_dblink||' x'
1299 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1300 ||' AND x.ADJUSTMENT_PERIOD_FLAG = ''N'' '; --svikas
1301
1302 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1303
1304 COMMIT;
1305
1306 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_PFMC_MEASURES';
1307 MSC_CL_PULL.v_view_name := 'BISBV_PERFORMANCE_MEASURES';
1308
1309 v_sql_stmt:=
1310 ' INSERT INTO MSC_ST_BIS_PFMC_MEASURES'
1311 ||' ( MEASURE_ID,'
1312 ||' MEASURE_SHORT_NAME,'
1313 ||' MEASURE_NAME,'
1314 ||' DESCRIPTION,'
1315 ||' ORG_DIMENSION_ID,'
1316 ||' TIME_DIMENSION_ID,'
1317 ||' DIMENSION1_ID,'
1318 ||' DIMENSION2_ID,'
1319 ||' DIMENSION3_ID,'
1320 ||' DIMENSION4_ID,'
1321 ||' DIMENSION5_ID,'
1322 ||' UNIT_OF_MEASURE_CLASS,'
1323 ||' DELETED_FLAG,'
1324 ||' REFRESH_ID,'
1325 ||' SR_INSTANCE_ID)'
1326 ||' SELECT'
1327 ||' x.MEASURE_ID,'
1328 ||' x.MEASURE_SHORT_NAME,'
1329 ||' x.MEASURE_NAME,'
1330 ||' x.DESCRIPTION,'
1334 ||' NULL TIME_DIMENSION_ID,'
1331 --||' x.ORG_DIMENSION_ID,' -- Old values as of version 115.92
1332 --||' x.TIME_DIMENSION_ID,' -- Old values as of version 115.92
1333 ||' NULL ORG_DIMENSION_ID,'
1335 ||' x.DIMENSION1_ID,'
1336 ||' x.DIMENSION2_ID,'
1337 ||' x.DIMENSION3_ID,'
1338 ||' x.DIMENSION4_ID,'
1339 ||' x.DIMENSION5_ID,'
1340 ||' x.UNIT_OF_MEASURE_CLASS,'
1341 ||' 2,'
1342 ||' :v_refresh_id,'
1343 ||' :v_instance_id'
1344 ||' FROM BISBV_PERFORMANCE_MEASURES'||MSC_CL_PULL.v_dblink||' x';
1345
1346 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1347
1348 COMMIT;
1349
1350 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGET_LEVELS';
1351 MSC_CL_PULL.v_view_name := 'BISBV_TARGET_LEVELS';
1352
1353 v_sql_stmt:=
1354 ' INSERT INTO MSC_ST_BIS_TARGET_LEVELS'
1355 ||' ( TARGET_LEVEL_ID,'
1356 ||' TARGET_LEVEL_SHORT_NAME,'
1357 ||' TARGET_LEVEL_NAME,'
1358 ||' DESCRIPTION,'
1359 ||' MEASURE_ID,'
1360 ||' ORG_LEVEL_ID,'
1361 ||' TIME_LEVEL_ID,'
1362 ||' DIMENSION1_LEVEL_ID,'
1363 ||' DIMENSION2_LEVEL_ID,'
1364 ||' DIMENSION3_LEVEL_ID,'
1365 ||' DIMENSION4_LEVEL_ID,'
1366 ||' DIMENSION5_LEVEL_ID,'
1367 ||' WORKFLOW_ITEM_TYPE,'
1368 ||' WORKFLOW_PROCESS_SHORT_NAME,'
1369 ||' DEFAULT_NOTIFY_RESP_ID,'
1370 ||' DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1371 ||' COMPUTING_FUNCTION_ID,'
1372 ||' REPORT_FUNCTION_ID,'
1373 ||' UNIT_OF_MEASURE,'
1374 ||' SYSTEM_FLAG,'
1375 ||' DELETED_FLAG,'
1376 ||' REFRESH_ID,'
1377 ||' SR_INSTANCE_ID)'
1378 ||' SELECT'
1379 ||' x.TARGET_LEVEL_ID,'
1380 ||' x.TARGET_LEVEL_SHORT_NAME,'
1381 ||' x.TARGET_LEVEL_NAME,'
1382 ||' x.DESCRIPTION,'
1383 ||' x.MEASURE_ID,'
1384 ||' x.ORG_LEVEL_ID,'
1385 ||' x.TIME_LEVEL_ID,'
1386 ||' x.DIMENSION1_LEVEL_ID,'
1387 ||' x.DIMENSION2_LEVEL_ID,'
1388 ||' x.DIMENSION3_LEVEL_ID,'
1389 ||' x.DIMENSION4_LEVEL_ID,'
1390 ||' x.DIMENSION5_LEVEL_ID,'
1391 ||' x.WORKFLOW_ITEM_TYPE,'
1392 ||' x.WORKFLOW_PROCESS_SHORT_NAME,'
1393 ||' x.DEFAULT_NOTIFY_RESP_ID,'
1394 ||' x.DEFAULT_NOTIFY_RESP_SHORT_NAME,'
1395 ||' x.COMPUTING_FUNCTION_ID,'
1396 ||' x.REPORT_FUNCTION_ID,'
1397 ||' x.UNIT_OF_MEASURE,'
1398 ||' x.SYSTEM_FLAG,'
1399 ||' 2,'
1400 ||' :v_refresh_id,'
1401 ||' :v_instance_id'
1402 ||' FROM BISBV_TARGET_LEVELS'||MSC_CL_PULL.v_dblink||' x';
1403
1404 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1405
1406 COMMIT;
1407
1408 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_TARGETS';
1409 MSC_CL_PULL.v_view_name := 'BISBV_TARGETS';
1410
1411 v_sql_stmt:=
1412 ' INSERT INTO MSC_ST_BIS_TARGETS'
1413 ||' ( TARGET_ID,'
1414 ||' TARGET_LEVEL_ID,'
1415 ||' BUSINESS_PLAN_ID,'
1416 ||' ORG_LEVEL_VALUE_ID,'
1417 ||' TIME_LEVEL_VALUE_ID,'
1418 ||' DIM1_LEVEL_VALUE_ID,'
1419 ||' DIM2_LEVEL_VALUE_ID,'
1420 ||' DIM3_LEVEL_VALUE_ID,'
1421 ||' DIM4_LEVEL_VALUE_ID,'
1422 ||' DIM5_LEVEL_VALUE_ID,'
1423 ||' TARGET,'
1424 ||' RANGE1_LOW,'
1425 ||' RANGE1_HIGH,'
1426 ||' RANGE2_LOW,'
1427 ||' RANGE2_HIGH,'
1428 ||' RANGE3_LOW,'
1429 ||' RANGE3_HIGH,'
1430 ||' NOTIFY_RESP1_ID,'
1431 ||' NOTIFY_RESP1_SHORT_NAME,'
1432 ||' NOTIFY_RESP2_ID,'
1433 ||' NOTIFY_RESP2_SHORT_NAME,'
1434 ||' NOTIFY_RESP3_ID,'
1435 ||' NOTIFY_RESP3_SHORT_NAME,'
1436 ||' DELETED_FLAG,'
1437 ||' REFRESH_ID,'
1438 ||' SR_INSTANCE_ID)'
1439 ||' SELECT'
1440 ||' x.TARGET_ID,'
1441 ||' x.TARGET_LEVEL_ID,'
1442 ||' x.PLAN_ID,'
1443 ||' x.ORG_LEVEL_VALUE_ID,'
1444 ||' x.TIME_LEVEL_VALUE_ID,'
1445 ||' x.DIM1_LEVEL_VALUE_ID,'
1446 ||' x.DIM2_LEVEL_VALUE_ID,'
1447 ||' x.DIM3_LEVEL_VALUE_ID,'
1448 ||' x.DIM4_LEVEL_VALUE_ID,'
1449 ||' x.DIM5_LEVEL_VALUE_ID,'
1450 ||' x.TARGET,'
1451 ||' x.RANGE1_LOW,'
1452 ||' x.RANGE1_HIGH,'
1453 ||' x.RANGE2_LOW,'
1454 ||' x.RANGE2_HIGH,'
1455 ||' x.RANGE3_LOW,'
1456 ||' x.RANGE3_HIGH,'
1457 ||' x.NOTIFY_RESP1_ID,'
1458 ||' x.NOTIFY_RESP1_SHORT_NAME,'
1459 ||' x.NOTIFY_RESP2_ID,'
1460 ||' x.NOTIFY_RESP2_SHORT_NAME,'
1461 ||' x.NOTIFY_RESP3_ID,'
1462 ||' x.NOTIFY_RESP3_SHORT_NAME,'
1463 ||' 2,'
1464 ||' :v_refresh_id,'
1465 ||' :v_instance_id'
1466 ||' FROM BISBV_TARGETS'||MSC_CL_PULL.v_dblink||' x';
1467
1468 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1469
1470 COMMIT;
1471
1472 MSC_CL_PULL.v_table_name:= 'MSC_ST_BIS_BUSINESS_PLANS';
1473 MSC_CL_PULL.v_view_name := 'BISBV_BUSINESS_PLANS';
1474
1475 v_sql_stmt:=
1476 ' INSERT INTO MSC_ST_BIS_BUSINESS_PLANS'
1477 ||' ( BUSINESS_PLAN_ID,'
1478 ||' SHORT_NAME,'
1479 ||' NAME,'
1480 ||' DESCRIPTION,'
1481 ||' VERSION_NO,'
1482 ||' CURRENT_PLAN_FLAG,'
1483 ||' DELETED_FLAG,'
1484 ||' REFRESH_ID,'
1485 ||' SR_INSTANCE_ID)'
1486 ||' SELECT'
1487 ||' x.PLAN_ID,'
1488 ||' x.SHORT_NAME,'
1489 ||' x.NAME,'
1490 ||' x.DESCRIPTION,'
1491 ||' x.VERSION_NO,'
1492 ||' x.CURRENT_PLAN_FLAG,'
1493 ||' 2,'
1497
1494 ||' :v_refresh_id,'
1495 ||' :v_instance_id'
1496 ||' FROM BISBV_BUSINESS_PLANS'||MSC_CL_PULL.v_dblink||' x';
1498 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1499
1500 COMMIT;
1501
1502 END IF;
1503
1504 END LOAD_BIS115;
1505
1506 --================ LOAD_ATP_RULES ====================================
1507
1508 PROCEDURE LOAD_ATP_RULES IS
1509 BEGIN
1510
1511 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1512
1513 MSC_CL_PULL.v_table_name:= 'MSC_ST_ATP_RULES';
1514 MSC_CL_PULL.v_view_name := 'MRP_AP_ATP_RULES_V';
1515
1516 v_sql_stmt:=
1517 'INSERT INTO MSC_ST_ATP_RULES'
1518 ||' ( RULE_ID,'
1519 ||' RULE_NAME,'
1520 ||' DESCRIPTION,'
1521 ||' ACCUMULATE_AVAILABLE_FLAG,'
1522 ||' BACKWARD_CONSUMPTION_FLAG,'
1523 ||' FORWARD_CONSUMPTION_FLAG,'
1524 ||' PAST_DUE_DEMAND_CUTOFF_FENCE,'
1525 ||' PAST_DUE_SUPPLY_CUTOFF_FENCE,'
1526 ||' INFINITE_SUPPLY_FENCE_CODE,'
1527 ||' INFINITE_SUPPLY_TIME_FENCE,'
1528 ||' ACCEPTABLE_EARLY_FENCE,'
1529 ||' ACCEPTABLE_LATE_FENCE,'
1530 ||' DEFAULT_ATP_SOURCES,'
1531 ||' DEMAND_CLASS_ATP_FLAG,'
1532 ||' INCLUDE_SALES_ORDERS,'
1533 ||' INCLUDE_DISCRETE_WIP_DEMAND,'
1534 ||' INCLUDE_REP_WIP_DEMAND,'
1535 ||' INCLUDE_NONSTD_WIP_DEMAND,'
1536 ||' INCLUDE_DISCRETE_MPS,'
1537 ||' INCLUDE_USER_DEFINED_DEMAND,'
1538 ||' INCLUDE_PURCHASE_ORDERS,'
1539 ||' INCLUDE_DISCRETE_WIP_RECEIPTS,'
1540 ||' INCLUDE_REP_WIP_RECEIPTS,'
1541 ||' INCLUDE_NONSTD_WIP_RECEIPTS,'
1542 ||' INCLUDE_INTERORG_TRANSFERS,'
1543 ||' INCLUDE_ONHAND_AVAILABLE,'
1544 ||' INCLUDE_USER_DEFINED_SUPPLY,'
1545 ||' ACCUMULATION_WINDOW,'
1546 ||' INCLUDE_REP_MPS,'
1547 ||' INCLUDE_INTERNAL_REQS,'
1548 ||' INCLUDE_SUPPLIER_REQS,'
1549 ||' INCLUDE_INTERNAL_ORDERS,'
1550 ||' INCLUDE_FLOW_SCHEDULE_DEMAND,'
1551 ||' INCLUDE_FLOW_SCHEDULE_RECEIPTS,'
1552 ||' USER_ATP_SUPPLY_TABLE_NAME,'
1553 ||' USER_ATP_DEMAND_TABLE_NAME,'
1554 ||' MPS_DESIGNATOR,'
1555 ||' AGGREGATE_TIME_FENCE_CODE,'
1556 ||' AGGREGATE_TIME_FENCE,'
1557 ||' REFRESH_ID,'
1558 ||' SR_INSTANCE_ID)'
1559 ||' SELECT'
1560 ||' RULE_ID,'
1561 ||' RULE_NAME,'
1562 ||' DESCRIPTION,'
1563 ||' ACCUMULATE_AVAILABLE_FLAG,'
1564 ||' BACKWARD_CONSUMPTION_FLAG,'
1565 ||' FORWARD_CONSUMPTION_FLAG,'
1566 ||' PAST_DUE_DEMAND_CUTOFF_FENCE,'
1567 ||' PAST_DUE_SUPPLY_CUTOFF_FENCE,'
1568 ||' INFINITE_SUPPLY_FENCE_CODE,'
1569 ||' INFINITE_SUPPLY_TIME_FENCE,'
1570 ||' ACCEPTABLE_EARLY_FENCE,'
1571 ||' ACCEPTABLE_LATE_FENCE,'
1572 ||' DEFAULT_ATP_SOURCES,'
1573 ||' DEMAND_CLASS_ATP_FLAG,'
1574 ||' INCLUDE_SALES_ORDERS,'
1575 ||' INCLUDE_DISCRETE_WIP_DEMAND,'
1576 ||' INCLUDE_REP_WIP_DEMAND,'
1577 ||' INCLUDE_NONSTD_WIP_DEMAND,'
1578 ||' INCLUDE_DISCRETE_MPS,'
1579 ||' INCLUDE_USER_DEFINED_DEMAND,'
1580 ||' INCLUDE_PURCHASE_ORDERS,'
1581 ||' INCLUDE_DISCRETE_WIP_RECEIPTS,'
1582 ||' INCLUDE_REP_WIP_RECEIPTS,'
1583 ||' INCLUDE_NONSTD_WIP_RECEIPTS,'
1584 ||' INCLUDE_INTERORG_TRANSFERS,'
1585 ||' INCLUDE_ONHAND_AVAILABLE,'
1586 ||' INCLUDE_USER_DEFINED_SUPPLY,'
1587 ||' ACCUMULATION_WINDOW,'
1588 ||' INCLUDE_REP_MPS,'
1589 ||' INCLUDE_INTERNAL_REQS,'
1590 ||' INCLUDE_SUPPLIER_REQS,'
1591 ||' INCLUDE_INTERNAL_ORDERS,'
1592 ||' INCLUDE_FLOW_SCHEDULE_DEMAND,'
1593 ||' INCLUDE_FLOW_SCHEDULE_RECEIPTS,'
1594 ||' USER_ATP_SUPPLY_TABLE_NAME,'
1595 ||' USER_ATP_DEMAND_TABLE_NAME,'
1596 ||' MPS_DESIGNATOR,'
1597 ||' AGGREGATE_TIME_FENCE_CODE,'
1598 ||' AGGREGATE_TIME_FENCE,'
1599 ||' :v_refresh_id,'
1600 ||' :v_instance_id'
1601 ||' FROM MRP_AP_ATP_RULES_V'||MSC_CL_PULL.v_dblink||' x';
1602
1603 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1604
1605 COMMIT;
1606
1607 END IF;
1608
1609 END LOAD_ATP_RULES;
1610
1611
1612 -- ================= LOAD PLANNERS ================
1613 PROCEDURE LOAD_PLANNERS IS
1614 BEGIN
1615
1616 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1617
1618 MSC_CL_PULL.v_table_name:= 'MSC_ST_PLANNERS';
1619 MSC_CL_PULL.v_view_name := 'MRP_AP_PLANNERS_V';
1620
1621 v_sql_stmt:=
1622 ' INSERT INTO MSC_ST_PLANNERS'
1623 ||'( PLANNER_CODE,'
1624 ||' ORGANIZATION_ID,'
1625 ||' DESCRIPTION,'
1626 ||' DISABLE_DATE,'
1627 ||' ELECTRONIC_MAIL_ADDRESS,'
1628 ||' EMPLOYEE_ID,'
1629 ||' CURRENT_EMPLOYEE_FLAG,'
1630 ||' USER_NAME,'
1631 ||' DELETED_FLAG,'
1632 ||' REFRESH_ID,'
1633 ||' SR_INSTANCE_ID)'
1634 ||' SELECT'
1635 ||' x.PLANNER_CODE,'
1636 ||' x.ORGANIZATION_ID,'
1637 ||' x.DESCRIPTION,'
1638 ||' x.DISABLE_DATE,'
1639 ||' x.ELECTRONIC_MAIL_ADDRESS,'
1640 ||' x.EMPLOYEE_ID,'
1641 ||' x.CURRENT_EMPLOYEE_FLAG,'
1642 ||' x.USER_NAME,'
1643 ||' 2,'
1644 ||' :v_refresh_id,'
1645 ||' :v_instance_id'
1646 ||' FROM MRP_AP_PLANNERS_V'||MSC_CL_PULL.v_dblink||' x'
1647 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1648
1652
1649 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1650
1651 COMMIT;
1653 END IF;
1654
1655 END LOAD_PLANNERS;
1656
1657
1658 -- ================= LOAD DEMAND_CLASS ================
1659 PROCEDURE LOAD_DEMAND_CLASS IS
1660 BEGIN
1661
1662 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1663
1664 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMAND_CLASSES';
1665 MSC_CL_PULL.v_view_name := 'MRP_AP_DEMAND_CLASSES_V';
1666
1667 v_sql_stmt:=
1668 'insert into MSC_ST_DEMAND_CLASSES'
1669 ||' ( DEMAND_CLASS,'
1670 ||' MEANING,'
1671 ||' DESCRIPTION,'
1672 ||' FROM_DATE,'
1673 ||' TO_DATE,'
1674 ||' ENABLED_FLAG,'
1675 ||' DELETED_FLAG,'
1676 ||' REFRESH_ID,'
1677 ||' SR_INSTANCE_ID)'
1678 ||' select '
1679 --||' :V_ICODE||x.DEMAND_CLASS,'
1680 ||' x.DEMAND_CLASS,'
1681 ||' x.MEANING,'
1682 ||' x.DESCRIPTION,'
1683 ||' x.FROM_DATE,'
1684 ||' x.TO_DATE,'
1685 ||' DECODE( x.ENABLED_FLAG, ''Y'', 1 , 2),'
1686 ||' 2,'
1687 ||' :v_refresh_id,'
1688 ||' :v_instance_id'
1689 ||' from MRP_AP_DEMAND_CLASSES_V'||MSC_CL_PULL.v_dblink||' x';
1690
1691 --EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1692 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1693
1694 COMMIT;
1695
1696 END IF;
1697
1698 END LOAD_DEMAND_CLASS;
1699
1700 /* LOAD_TRIP added for Pulling Trips and Trip Stops for Deployment Planning Project */
1701
1702 PROCEDURE LOAD_TRIP IS
1703 BEGIN
1704
1705 IF MSC_CL_PULL.TRIP_ENABLED= MSC_UTIL.SYS_YES AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1706
1707 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1708
1709 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1710 MSC_CL_PULL.v_view_name := 'MRP_AD_TRIPS_V';
1711
1712 v_sql_stmt:=
1713 ' INSERT INTO MSC_ST_TRIPS'
1714 ||' ( TRIP_ID,'
1715 ||' DELETED_FLAG,'
1716 ||' REFRESH_ID,'
1717 ||' SR_INSTANCE_ID)'
1718 ||' SELECT '
1719 ||' x.TRIP_ID,'
1720 ||' 1,'
1721 ||' :v_refresh_id,'
1722 ||' :v_instance_id'
1723 ||' FROM MRP_AD_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1724 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1725
1726 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1727
1728 COMMIT;
1729
1730 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1731 MSC_CL_PULL.v_view_name := 'MRP_AD_TRIP_STOPS_V';
1732
1733 v_sql_stmt:=
1734 ' INSERT INTO MSC_ST_TRIP_STOPS'
1735 ||' ( STOP_ID,'
1736 ||' DELETED_FLAG,'
1737 ||' REFRESH_ID,'
1738 ||' SR_INSTANCE_ID)'
1739 ||' SELECT '
1740 ||' x.STOP_ID,'
1741 ||' 1,'
1742 ||' :v_refresh_id,'
1743 ||' :v_instance_id'
1744 ||' FROM MRP_AD_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1745 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1746
1747 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1748
1749 COMMIT;
1750
1751 END IF;
1752
1753 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIPS';
1754 MSC_CL_PULL.v_view_name := 'MRP_AP_TRIPS_V';
1755
1756 v_sql_stmt:=
1757 ' INSERT INTO MSC_ST_TRIPS'
1758 ||' ( TRIP_ID,'
1759 ||' NAME,'
1760 ||' SHIP_METHOD_CODE,'
1761 ||' PLANNED_FLAG,'
1762 ||' STATUS_CODE,'
1763 ||' WEIGHT_CAPACITY,'
1764 ||' WEIGHT_UOM,'
1765 ||' VOLUME_CAPACITY,'
1766 ||' VOLUME_UOM,'
1767 ||' DELETED_FLAG,'
1768 ||' REFRESH_ID,'
1769 ||' SR_INSTANCE_ID)'
1770 ||' SELECT '
1771 ||' x.TRIP_ID,'
1772 ||' x.NAME,'
1773 ||' x.SHIP_METHOD_CODE,'
1774 ||' x.PLANNED_FLAG,'
1775 ||' x.STATUS_CODE,'
1776 ||' x.WEIGHT_CAPACITY,'
1777 ||' x.WEIGHT_UOM,'
1778 ||' x.VOLUME_CAPACITY,'
1779 ||' x.VOLUME_UOM,'
1780 ||' 2,'
1781 ||' :v_refresh_id,'
1782 ||' :v_instance_id'
1783 ||' FROM MRP_AP_TRIPS_V'||MSC_CL_PULL.v_dblink||' x'
1784 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1785
1786 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1787
1788 COMMIT;
1789
1790 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRIP_STOPS';
1791 MSC_CL_PULL.v_view_name := 'MRP_AP_TRIP_STOPS_V';
1792
1793 v_sql_stmt:=
1794 ' INSERT INTO MSC_ST_TRIP_STOPS'
1795 ||' ( STOP_ID,'
1796 ||' STOP_LOCATION_ID,'
1797 ||' STATUS_CODE,'
1798 ||' STOP_SEQUENCE_NUMBER,'
1799 ||' PLANNED_ARRIVAL_DATE,'
1800 ||' PLANNED_DEPARTURE_DATE,'
1801 ||' TRIP_ID,'
1802 ||' DELETED_FLAG,'
1803 ||' REFRESH_ID,'
1804 ||' SR_INSTANCE_ID)'
1805 ||' SELECT '
1806 ||' x.STOP_ID,'
1807 ||' x.STOP_LOCATION_ID,'
1808 ||' x.STATUS_CODE,'
1809 ||' x.STOP_SEQUENCE_NUMBER,'
1810 ||' x.PLANNED_ARRIVAL_DATE,'
1811 ||' x.PLANNED_DEPARTURE_DATE,'
1812 ||' x.TRIP_ID,'
1813 ||' 2,'
1814 ||' :v_refresh_id,'
1818
1815 ||' :v_instance_id'
1816 ||' FROM MRP_AP_TRIP_STOPS_V'||MSC_CL_PULL.v_dblink||' x'
1817 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn ;
1819 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1820
1821 COMMIT;
1822
1823 END IF; -- MSC_CL_PULL.TRIP_ENABLED
1824
1825 END LOAD_TRIP;
1826
1827 PROCEDURE LOAD_SALES_CHANNEL IS
1828 BEGIN
1829
1830 IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1831 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Sales Channel');
1832
1833 MSC_CL_PULL.v_table_name:= 'MSC_ST_SR_LOOKUPS';
1834 MSC_CL_PULL.v_view_name := 'MRP_AP_SALES_CHANNEL_V';
1835
1836 v_sql_stmt:=
1837 'INSERT INTO MSC_ST_SR_LOOKUPS'
1838 ||' ( LOOKUP_TYPE,'
1839 ||' LOOKUP_CODE,'
1840 ||' MEANING,'
1841 ||' DESCRIPTION,'
1842 ||' FROM_DATE,'
1843 ||' TO_DATE,'
1844 ||' ENABLED_FLAG,'
1845 ||' DELETED_FLAG,'
1846 ||' REFRESH_ID,'
1847 ||' SR_INSTANCE_ID)'
1848 ||' SELECT '
1849 ||' ''SALES_CHANNEL'','
1850 ||' X. LOOKUP_CODE,'
1851 ||' X.MEANING,'
1852 ||' X.DESCRIPTION,'
1853 ||' X.FROM_DATE,'
1854 ||' X.TO_DATE,'
1855 ||' DECODE( X.ENABLED_FLAG, ''Y'', 1 , 2),'
1856 ||' 2,'
1857 ||' :v_refresh_id,'
1858 ||' :v_instance_id'
1859 ||' FROM MRP_AP_SALES_CHANNEL_V'||MSC_CL_PULL.v_dblink||' X';
1860
1861 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1862
1863 COMMIT;
1864
1865 END IF;
1866 END LOAD_SALES_CHANNEL;
1867
1868
1869 PROCEDURE LOAD_FISCAL_CALENDAR IS
1870 BEGIN
1871
1872 IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1873 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Fiscal Calendar');
1874
1875 MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_MONTHS';
1876 MSC_CL_PULL.v_view_name := 'MRP_AP_FISCAL_TIME_V';
1877
1878 v_sql_stmt:=
1879 'INSERT INTO MSC_ST_CALENDAR_MONTHS'
1880 ||' (CALENDAR_CODE,'
1881 ||' CALENDAR_TYPE,'
1882 ||' YEAR,'
1883 ||' YEAR_DESCRIPTION,'
1884 ||' YEAR_START_DATE,'
1885 ||' YEAR_END_DATE,'
1886 ||' QUARTER,'
1887 ||' QUARTER_DESCRIPTION,'
1888 ||' QUARTER_START_DATE ,'
1889 ||' QUARTER_END_DATE,'
1890 ||' MONTH,'
1891 ||' MONTH_DESCRIPTION,'
1892 ||' MONTH_START_DATE,'
1893 ||' MONTH_END_DATE,'
1894 ||' DELETED_FLAG,'
1895 ||' REFRESH_ID,'
1896 ||' SR_INSTANCE_ID)'
1897 ||' SELECT '
1898 ||' :V_ICODE||CALENDAR_CODE,'
1899 ||' ''FISCAL'','
1900 ||' X. YEAR,'
1901 ||' X. YEAR_DESCRIPTION,'
1902 ||' X. YEAR_START_DATE,'
1903 ||' X. YEAR_END_DATE ,'
1904 ||' X. QUARTER,'
1905 ||' X. QUARTER_DESCRIPTION,'
1906 ||' X. QUARTER_START_DATE,'
1907 ||' X. QUARTER_END_DATE ,'
1908 ||' X. MONTH,'
1909 ||' X. MONTH_DESCRIPTION,'
1910 ||' X. MONTH_START_DATE,'
1911 ||' X. MONTH_END_DATE,'
1912 ||' 2,'
1913 ||' :v_refresh_id,'
1914 ||' :v_instance_id'
1915 ||' FROM MRP_AP_FISCAL_TIME_V'||MSC_CL_PULL.v_dblink||' X';
1916
1917 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1918
1919 COMMIT;
1920
1921 END IF;
1922 END LOAD_FISCAL_CALENDAR;
1923
1924 -- for bug # 6469722
1925 PROCEDURE LOAD_CURRENCY_CONVERSION IS
1926 BEGIN
1927
1928 IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN -- complete refresh
1929 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Currency Conversion');
1930
1931 MSC_CL_PULL.v_table_name:= 'MSC_ST_CURRENCY_CONVERSIONS';
1932 MSC_CL_PULL.v_view_name := 'MRP_AP_CURRENCY_CONV_V';
1933
1934 v_sql_stmt:=
1935 'INSERT INTO MSC_ST_CURRENCY_CONVERSIONS'
1936 ||' ( SR_INSTANCE_ID,'
1937 ||' FROM_CURRENCY,'
1938 ||' TO_CURRENCY,'
1939 ||' CONV_DATE,'
1940 ||' CONV_TYPE,'
1941 ||' CONV_RATE,'
1942 ||' CREATION_DATE,'
1943 ||' RN,'
1944 ||' CREATED_BY,'
1945 ||' LAST_UPDATE_DATE,'
1946 ||' LAST_UPDATED_BY,'
1947 ||' LAST_UPDATE_LOGIN,'
1948 ||' DELETED_FLAG)'
1949 ||' SELECT '
1950 ||' :v_instance_id,'
1951 ||' XY.FROM_CURRENCY,'
1952 ||' XY.TO_CURRENCY,'
1953 ||' XY.CONVERSION_DATE,'
1954 ||' XY.CONVERSION_TYPE,'
1955 ||' XY.CONVERSION_RATE,'
1956 ||' XY.CREATION_DATE,'
1957 ||' :v_refresh_id,'
1958 ||' XY.CREATED_BY,'
1959 ||' XY.LAST_UPDATE_DATE,'
1960 ||' XY.LAST_UPDATED_BY,'
1961 ||' XY.LAST_UPDATE_LOGIN,'
1962 ||' 2'
1963 ||' FROM MRP_AP_CURRENCY_CONV_V' ||MSC_CL_PULL.v_dblink||' XY'
1964 ||' WHERE CONVERSION_DATE >= sysdate - :v_msc_past_days AND'
1965 ||' CONVERSION_DATE <= sysdate + :v_msc_future_days AND'
1966 ||' TO_CURRENCY = :v_msc_hub_curr_code AND'
1967 ||' CONVERSION_TYPE = :v_msc_curr_conv_type';
1968
1969
1970 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id, MSC_CL_OTHER_PULL.G_MSC_PAST_DAYS, MSC_CL_OTHER_PULL.G_MSC_FUTURE_DAYS, MSC_CL_OTHER_PULL.G_MSC_HUB_CURR_CODE, MSC_CL_OTHER_PULL.G_MSC_CURR_CONV_TYPE;
1971
1972 COMMIT;
1973
1974 END IF;
1975 END LOAD_CURRENCY_CONVERSION;
1976
1977 PROCEDURE LOAD_DELIVERY_DETAILS IS
1978 BEGIN
1979
1980 IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 THEN -- complete refresh
1981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Loading Delivery Details');
1982
1983 MSC_CL_PULL.v_table_name:= 'MSC_ST_DELIVERY_DETAILS';
1984 MSC_CL_PULL.v_view_name := 'MRP_AP_DELIVERY_DETAILS_V';
1985
1986 v_sql_stmt:=
1987 'INSERT INTO MSC_ST_DELIVERY_DETAILS'
1988 ||' ( SR_INSTANCE_ID,'
1989 ||' DELIVERY_DETAIL_ID,'
1990 ||' SOURCE_CODE ,'
1991 ||' SOURCE_HEADER_ID ,'
1992 ||' SOURCE_LINE_ID ,'
1993 ||' SOURCE_HEADER_NUMBER,'
1994 ||' SHIP_SET_ID,'
1995 ||' ARRIVAL_SET_ID,'
1996 ||' SHIP_FROM_LOCATION_ID,'
1997 ||' ORGANIZATION_ID,'
1998 ||' SHIP_TO_LOCATION_ID,'
1999 ||' SHIP_TO_SITE_USE_ID,'
2000 ||' DELIVER_TO_LOCATION_ID,'
2001 ||' DELIVER_TO_SITE_USE_ID,'
2002 ||' CANCELLED_QUANTITY,'
2003 ||' REQUESTED_QUANTITY,'
2004 ||' REQUESTED_QUANTITY_UOM,'
2005 ||' SHIPPED_QUANTITY,'
2006 ||' DELIVERED_QUANTITY,'
2007 ||' DATE_REQUESTED,'
2008 ||' DATE_SCHEDULED,'
2009 ||' OPERATING_UNIT,'
2010 ||' INV_INTERFACED_FLAG,'
2011 ||' EARLIEST_PICKUP_DATE,'
2012 ||' LATEST_PICKUP_DATE,'
2013 ||' EARLIEST_DROPOFF_DATE,'
2014 ||' LATEST_DROPOFF_DATE,'
2015 ||' REFRESH_NUMBER,'
2016 ||' LAST_UPDATE_DATE,'
2017 ||' LAST_UPDATED_BY,'
2018 ||' CREATION_DATE,'
2019 ||' CREATED_BY,'
2020 ||' LAST_UPDATE_LOGIN'
2021 ||' )'
2022 ||' SELECT '
2023 ||' :v_instance_id,'
2024 ||' XY.DELIVERY_DETAIL_ID,'
2025 ||' XY.SOURCE_CODE,'
2026 ||' XY.SOURCE_HEADER_ID,'
2027 ||' XY.SOURCE_LINE_ID,'
2028 ||' XY.SOURCE_HEADER_NUMBER,'
2029 ||' XY.SHIP_SET_ID,'
2030 ||' XY.ARRIVAL_SET_ID,'
2031 ||' XY.SHIP_FROM_LOCATION_ID,'
2032 ||' XY.ORGANIZATION_ID,'
2033 ||' XY.SHIP_TO_LOCATION_ID,'
2034 ||' XY.SHIP_TO_SITE_USE_ID,'
2035 ||' XY.DELIVER_TO_LOCATION_ID,'
2036 ||' XY.DELIVER_TO_SITE_USE_ID,'
2037 ||' XY.CANCELLED_QUANTITY,'
2038 ||' XY.REQUESTED_QUANTITY,'
2039 ||' XY.REQUESTED_QUANTITY_UOM,'
2040 ||' XY.SHIPPED_QUANTITY,'
2041 ||' XY.DELIVERED_QUANTITY,'
2042 ||' XY.DATE_REQUESTED,'
2043 ||' XY.DATE_SCHEDULED,'
2044 ||' XY.ORG_ID,'
2045 ||' XY.INV_INTERFACED_FLAG,'
2046 ||' XY.EARLIEST_PICKUP_DATE,'
2047 ||' XY.LATEST_PICKUP_DATE,'
2048 ||' XY.EARLIEST_DROPOFF_DATE,'
2049 ||' XY.LATEST_DROPOFF_DATE,'
2050 ||' :v_refresh_id,'
2051 ||' XY.LAST_UPDATE_DATE,'
2052 ||' XY.LAST_UPDATED_BY,'
2053 ||' XY.CREATION_DATE,'
2054 ||' XY.CREATED_BY,'
2055 ||' nvl(XY.LAST_UPDATE_LOGIN,1)'
2056 ||' FROM MRP_AP_DELIVERY_DETAILS_V' ||MSC_CL_PULL.v_dblink||' XY'
2057 ||' WHERE XY.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2058
2059
2060 EXECUTE IMMEDIATE v_sql_stmt
2061 USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2062
2063 COMMIT;
2064
2065 END IF;
2066 END LOAD_DELIVERY_DETAILS;
2067
2068 END MSC_CL_OTHER_PULL;