[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_SETUP_PULL
Source
1 PACKAGE BODY MSC_CL_SETUP_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_temp_sql5 VARCHAR2(1000);
15 v_rounding_Sql varchar2(1000);
16
17 -- NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
18 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
19 v_gmp_routine_name VARCHAR2(50);
20 GMP_ERROR EXCEPTION;
21
22 --==================================================================
23
24 PROCEDURE LOAD_CALENDAR_DATE IS
25 BEGIN
26
27 --=================== Net Change Mode: Delete ==================
28
29 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
30
31 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
32 MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_CHANGES_V';
33
34 v_sql_stmt:=
35 ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
36 ||' ( DEPARTMENT_ID,'
37 ||' RESOURCE_ID,'
38 ||' SHIFT_NUM,'
39 ||' FROM_DATE,'
40 ||' TO_DATE,'
41 ||' FROM_TIME,'
42 ||' TO_TIME,'
43 ||' SIMULATION_SET,'
44 ||' ACTION_TYPE,'
45 ||' DELETED_FLAG,'
46 ||' REFRESH_ID,'
47 ||' SR_INSTANCE_ID)'
48 ||' SELECT'
49 ||' x.DEPARTMENT_ID,'
50 ||' x.RESOURCE_ID,'
51 ||' x.SHIFT_NUM,'
52 ||' x.FROM_DATE,'
53 ||' x.TO_DATE,'
54 ||' x.FROM_TIME,'
55 ||' x.TO_TIME,'
56 ||' x.SIMULATION_SET,'
57 ||' x.ACTION_TYPE,'
58 ||' 1,'
59 ||' :v_refresh_id,'
60 ||' :v_instance_id'
61 ||' FROM MRP_AD_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
62 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn;
63
64 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
65
66 COMMIT;
67
68 END IF;
69
70
71
72 IF MSC_CL_PULL.v_lrnn= -1 AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --when not net collection
73
74
75 MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_ASSIGNMENTS';
76 MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_ASSIGNMENTS_V';
77
78 v_sql_stmt:=
79 ' INSERT INTO MSC_ST_CALENDAR_ASSIGNMENTS'
80 ||' ( ASSOCIATION_TYPE,'
81 ||' CALENDAR_CODE,'
82 ||' CALENDAR_TYPE,'
83 ||' PARTNER_ID,'
84 ||' PARTNER_SITE_ID,'
85 ||' ORGANIZATION_ID,'
86 ||' SR_INSTANCE_ID,'
87 ||' CARRIER_PARTNER_ID,'
88 ||' PARTNER_TYPE,'
89 ||' ASSOCIATION_LEVEL,'
90 ||' SHIP_METHOD_CODE,'
91 ||' REFRESH_ID)'
92 ||' SELECT'
93 ||' x.ASSOCIATION_TYPE,'
94 ||' :V_ICODE||x.CALENDAR_CODE,'
95 ||' x.CALENDAR_TYPE,'
96 ||' x.PARTNER_ID,'
97 ||' x.PARTNER_SITE_ID,'
98 ||' x.ORGANIZATION_ID,'
99 ||' :v_instance_id,'
100 ||' x.CARRIER_ID,'
101 ||' x.PARTNER_TYPE,'
102 ||' decode(x.ASSOCIATION_TYPE,11,1,10,1,9,3,8,3,7,3,6,2,5,2,4,4,3,4,2,4,1,4),'
103 ||' x.SHIP_METHOD_CODE,'
104 ||' :v_refresh_id'
105 ||' FROM MRP_AP_CAL_ASSIGNMENTS_V'||MSC_CL_PULL.v_dblink||' x';
106
107 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_refresh_id;
108
109 COMMIT;
110
111 END IF;
112
113 MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_DATES';
114 MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_DATES_V';
115 v_sql_stmt:=
116 'insert into MSC_ST_CALENDAR_DATES'
117 ||' ( CALENDAR_DATE,'
118 ||' CALENDAR_CODE,'
119 ||' EXCEPTION_SET_ID,'
120 ||' SEQ_NUM,'
121 ||' NEXT_SEQ_NUM,'
122 ||' PRIOR_SEQ_NUM,'
123 ||' NEXT_DATE,'
124 ||' PRIOR_DATE,'
125 ||' CALENDAR_START_DATE,'
126 ||' CALENDAR_END_DATE,'
127 ||' DESCRIPTION,'
128 ||' DELETED_FLAG,'
129 ||' REFRESH_ID,'
130 ||' SR_INSTANCE_ID)'
131 ||' select'
132 ||' x.CALENDAR_DATE,'
133 ||' :V_ICODE||x.CALENDAR_CODE,'
134 ||' x.EXCEPTION_SET_ID,'
135 ||' x.SEQ_NUM,'
136 ||' x.NEXT_SEQ_NUM,'
137 ||' x.PRIOR_SEQ_NUM,'
138 ||' x.NEXT_DATE,'
139 ||' x.PRIOR_DATE,'
140 ||' x.CALENDAR_START_DATE,'
141 ||' x.CALENDAR_END_DATE,'
142 ||' x.DESCRIPTION,'
143 ||' 2,'
144 ||' :v_refresh_id,'
145 ||' :v_instance_id'
146 ||' from MRP_AP_CALENDAR_DATES_V'||MSC_CL_PULL.v_dblink||' x'
147 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
148 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
149 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
150 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn;
151
152 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
153
154 COMMIT;
155
156 MSC_CL_PULL.v_table_name:= 'MSC_ST_PERIOD_START_DATES';
157 MSC_CL_PULL.v_view_name := 'MRP_AP_PERIOD_START_DATES_V';
158
159 v_sql_stmt:=
160 ' INSERT INTO MSC_ST_PERIOD_START_DATES'
161 ||' ( CALENDAR_CODE,'
162 ||' EXCEPTION_SET_ID,'
163 ||' PERIOD_START_DATE,'
164 ||' PERIOD_SEQUENCE_NUM,'
165 ||' PERIOD_NAME,'
166 ||' NEXT_DATE,'
167 ||' PRIOR_DATE,'
168 ||' DELETED_FLAG,'
169 ||' REFRESH_ID,'
170 ||' SR_INSTANCE_ID)'
171 ||' SELECT'
172 ||' :V_ICODE||x.CALENDAR_CODE,'
173 ||' x.EXCEPTION_SET_ID,'
174 ||' x.PERIOD_START_DATE,'
175 ||' x.PERIOD_SEQUENCE_NUM,'
176 ||' x.PERIOD_NAME,'
177 ||' x.NEXT_DATE,'
178 ||' x.PRIOR_DATE,'
179 ||' 2,'
180 ||' :v_refresh_id,'
181 ||' :v_instance_id'
182 ||' FROM MRP_AP_PERIOD_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
183 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
184 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
185 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
186
187 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
188
189 COMMIT;
190
191 MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_YEAR_START_DATES';
192 MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_YEAR_START_DATES_V';
193
194 v_sql_stmt:=
195 ' INSERT INTO MSC_ST_CAL_YEAR_START_DATES'
196 ||' ( CALENDAR_CODE,'
197 ||' EXCEPTION_SET_ID,'
198 ||' YEAR_START_DATE,'
199 ||' DELETED_FLAG,'
200 ||' REFRESH_ID,'
201 ||' SR_INSTANCE_ID)'
202 ||' SELECT'
203 ||' :V_ICODE||x.CALENDAR_CODE,'
204 ||' x.EXCEPTION_SET_ID,'
205 ||' x.YEAR_START_DATE,'
206 ||' 2,'
207 ||' :v_refresh_id,'
208 ||' :v_instance_id'
209 ||' FROM MRP_AP_CAL_YEAR_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
210 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
211 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
212 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
213
214 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
215
216 COMMIT;
217
218 MSC_CL_PULL.v_table_name:= 'MSC_ST_CAL_WEEK_START_DATES';
219 MSC_CL_PULL.v_view_name := 'MRP_AP_CAL_WEEK_START_DATES_V';
220
221 v_sql_stmt:=
222 ' INSERT INTO MSC_ST_CAL_WEEK_START_DATES'
223 ||' ( CALENDAR_CODE,'
224 ||' EXCEPTION_SET_ID,'
225 ||' WEEK_START_DATE,'
226 ||' NEXT_DATE,'
227 ||' PRIOR_DATE,'
228 ||' SEQ_NUM,'
229 ||' DELETED_FLAG,'
230 ||' REFRESH_ID,'
231 ||' SR_INSTANCE_ID)'
232 ||' SELECT'
233 ||' :V_ICODE||x.CALENDAR_CODE,'
234 ||' x.EXCEPTION_SET_ID,'
235 ||' x.WEEK_START_DATE,'
236 ||' x.NEXT_DATE,'
237 ||' x.PRIOR_DATE,'
238 ||' x.SEQ_NUM,'
239 ||' 2,'
240 ||' :v_refresh_id,'
241 ||' :v_instance_id'
242 ||' FROM MRP_AP_CAL_WEEK_START_DATES_V'||MSC_CL_PULL.v_dblink||' x'
243 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
244 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
245 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
246
247 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
248
249 COMMIT;
250
251 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SHIFTS';
252 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SHIFTS_V';
253
254 Begin
255
256
257 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
258 v_temp_sql:= 'x.capacity_units , ' ;
259 ELSE
260 v_temp_sql:=' NULL, ' ;
261 END IF ;
262
263
264 End;
265
266 v_sql_stmt:=
267 ' INSERT INTO MSC_ST_RESOURCE_SHIFTS'
268 ||' ( DEPARTMENT_ID,'
269 ||' RESOURCE_ID,'
270 ||' SHIFT_NUM,'
271 ||' DELETED_FLAG,'
272 ||' CAPACITY_UNITS,'
273 ||' REFRESH_ID,'
274 ||' SR_INSTANCE_ID)'
275 ||' SELECT'
276 ||' x.DEPARTMENT_ID,'
277 ||' x.RESOURCE_ID,'
278 ||' x.SHIFT_NUM,'
279 ||' 2,'
280 || v_temp_sql
281 ||' :v_refresh_id,'
282 ||' :v_instance_id'
283 ||' FROM MRP_AP_RESOURCE_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
284 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str
285 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
286 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
287
288 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
289
290 COMMIT;
291
292 MSC_CL_PULL.v_table_name:= 'MSC_ST_CALENDAR_SHIFTS';
293 MSC_CL_PULL.v_view_name := 'MRP_AP_CALENDAR_SHIFTS_V';
294
295 v_sql_stmt:=
296 ' INSERT INTO MSC_ST_CALENDAR_SHIFTS'
297 ||' ( CALENDAR_CODE,'
298 ||' SHIFT_NUM,'
299 ||' DAYS_ON,'
300 ||' DAYS_OFF,'
301 ||' DESCRIPTION,'
302 ||' DELETED_FLAG,'
303 ||' REFRESH_ID,'
304 ||' SR_INSTANCE_ID)'
305 ||' SELECT'
306 ||' :V_ICODE||x.CALENDAR_CODE,'
307 ||' x.SHIFT_NUM,'
308 ||' x.DAYS_ON,'
309 ||' x.DAYS_OFF,'
310 ||' x.DESCRIPTION,'
311 ||' 2,'
312 ||' :v_refresh_id,'
313 ||' :v_instance_id'
314 ||' FROM MRP_AP_CALENDAR_SHIFTS_V'||MSC_CL_PULL.v_dblink||' x'
315 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
316 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
317 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
318
319 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
320
321 COMMIT;
322
323 MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_DATES';
324 MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_DATES_V';
325
326 v_sql_stmt:=
327 ' INSERT INTO MSC_ST_SHIFT_DATES'
328 ||' ( CALENDAR_CODE,'
329 ||' EXCEPTION_SET_ID,'
330 ||' SHIFT_NUM,'
331 ||' SHIFT_DATE,'
332 ||' SEQ_NUM,'
333 ||' NEXT_SEQ_NUM,'
334 ||' PRIOR_SEQ_NUM,'
335 ||' NEXT_DATE,'
336 ||' PRIOR_DATE,'
337 ||' DELETED_FLAG,'
338 ||' REFRESH_ID,'
339 ||' SR_INSTANCE_ID)'
340 ||' SELECT'
341 ||' :V_ICODE||x.CALENDAR_CODE,'
342 ||' x.EXCEPTION_SET_ID,'
343 ||' x.SHIFT_NUM,'
344 ||' x.SHIFT_DATE,'
345 ||' x.SEQ_NUM,'
346 ||' x.NEXT_SEQ_NUM,'
347 ||' x.PRIOR_SEQ_NUM,'
348 ||' x.NEXT_DATE,'
349 ||' x.PRIOR_DATE,'
350 ||' 2,'
351 ||' :v_refresh_id,'
352 ||' :v_instance_id'
353 ||' FROM MRP_AP_SHIFT_DATES_V'||MSC_CL_PULL.v_dblink||' x'
354 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
355 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
356 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
357
358 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
359
360 COMMIT;
361
362 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_CHANGES';
363 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_CHANGES_V';
364
365 v_sql_stmt:=
366 ' INSERT INTO MSC_ST_RESOURCE_CHANGES'
367 ||' ( DEPARTMENT_ID,'
368 ||' RESOURCE_ID,'
369 ||' SHIFT_NUM,'
370 ||' FROM_DATE,'
371 ||' TO_DATE,'
372 ||' FROM_TIME,'
373 ||' TO_TIME,'
374 ||' CAPACITY_CHANGE,'
375 ||' SIMULATION_SET,'
376 ||' ACTION_TYPE,'
377 ||' DELETED_FLAG,'
378 ||' REFRESH_ID,'
379 ||' SR_INSTANCE_ID)'
380 ||' SELECT'
381 ||' x.DEPARTMENT_ID,'
382 ||' x.RESOURCE_ID,'
383 ||' x.SHIFT_NUM,'
384 ||' x.FROM_DATE,'
385 ||' x.TO_DATE,'
386 ||' x.FROM_TIME,'
387 ||' x.TO_TIME,'
388 ||' x.CAPACITY_CHANGE,'
389 ||' x.SIMULATION_SET,'
390 ||' x.ACTION_TYPE,'
391 ||' 2,'
392 ||' :v_refresh_id,'
393 ||' :v_instance_id'
394 ||' FROM MRP_AP_RESOURCE_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
395 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
396 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn;
397
398 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
399
400 COMMIT;
401
402 MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_TIMES';
403 MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_TIMES_V';
404
405 v_sql_stmt:=
406 ' INSERT INTO MSC_ST_SHIFT_TIMES'
407 ||' ( CALENDAR_CODE,'
408 ||' SHIFT_NUM,'
409 ||' FROM_TIME,'
410 ||' TO_TIME,'
411 ||' DELETED_FLAG,'
412 ||' REFRESH_ID,'
413 ||' SR_INSTANCE_ID)'
414 ||' SELECT'
415 ||' :V_ICODE||x.CALENDAR_CODE,'
416 ||' x.SHIFT_NUM,'
417 ||' x.FROM_TIME,'
418 ||' x.TO_TIME,'
419 ||' 2,'
420 ||' :v_refresh_id,'
421 ||' :v_instance_id'
422 ||' FROM MRP_AP_SHIFT_TIMES_V'||MSC_CL_PULL.v_dblink||' x'
423 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
424 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
425 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
426
427 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
428
429 COMMIT;
430
431 MSC_CL_PULL.v_table_name:= 'MSC_ST_SHIFT_EXCEPTIONS';
432 MSC_CL_PULL.v_view_name := 'MRP_AP_SHIFT_EXCEPTIONS_V';
433
434 v_sql_stmt:=
435 ' INSERT INTO MSC_ST_SHIFT_EXCEPTIONS'
436 ||' ( CALENDAR_CODE,'
437 ||' SHIFT_NUM,'
438 ||' EXCEPTION_SET_ID,'
439 ||' EXCEPTION_DATE,'
440 ||' EXCEPTION_TYPE,'
441 ||' DELETED_FLAG,'
442 ||' REFRESH_ID,'
443 ||' SR_INSTANCE_ID)'
444 ||' SELECT'
445 ||' :V_ICODE||x.CALENDAR_CODE,'
446 ||' x.SHIFT_NUM,'
447 ||' x.EXCEPTION_SET_ID,'
448 ||' x.EXCEPTION_DATE,'
449 ||' x.EXCEPTION_TYPE,'
450 ||' 2,'
451 ||' :v_refresh_id,'
452 ||' :v_instance_id'
453 ||' FROM MRP_AP_SHIFT_EXCEPTIONS_V'||MSC_CL_PULL.v_dblink||' x'
454 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
455 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
456 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
457
458 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
459
460 COMMIT;
461
462 -- =============================== NETCHAGE OF DELETE ======================
463
464 /* ds change start */
465
466 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
467 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
468 MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
469 MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INST_CHANGES_V';
470 v_sql_stmt:=
471 ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
472 ||' ( DEPARTMENT_ID,'
473 ||' RESOURCE_ID,'
474 ||' RES_INSTANCE_ID,'
475 ||' SERIAL_NUMBER,'
476 ||' SHIFT_NUM,'
477 ||' FROM_DATE,'
478 ||' TO_DATE,'
479 ||' FROM_TIME,'
480 ||' TO_TIME,'
481 ||' SIMULATION_SET,'
482 ||' ACTION_TYPE,'
483 ||' DELETED_FLAG,'
484 ||' REFRESH_ID,'
485 ||' SR_INSTANCE_ID)'
486 ||' SELECT'
487 ||' x.DEPARTMENT_ID,'
488 ||' x.RESOURCE_ID,'
489 ||' x.RES_INSTANCE_ID,'
490 ||' x.SERIAL_NUMBER,'
491 ||' x.SHIFT_NUM,'
492 ||' x.FROM_DATE,'
493 ||' x.TO_DATE,'
494 ||' x.FROM_TIME,'
495 ||' x.TO_TIME,'
496 ||' x.SIMULATION_SET,'
497 ||' x.ACTION_TYPE,'
498 ||' 1,'
499 ||' :v_refresh_id,'
500 ||' :v_instance_id'
501 ||' FROM MRP_AD_RES_INST_CHANGES_V'||MSC_CL_PULL.v_dblink||' x'
502 ||' WHERE x.RN >' ||MSC_CL_PULL.v_lrn;
503
504 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance change sql = '||v_sql_stmt);
505 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
506
507 COMMIT;
508
509 END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
510
511 IF MSC_CL_PULL.v_lrnn= -1 THEN /* if it i not net change */
512 MSC_CL_PULL.v_table_name:= 'MSC_ST_RES_INSTANCE_CHANGES';
513 MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INST_CHANGES_V';
514
515 v_sql_stmt:=
516 ' INSERT INTO MSC_ST_RES_INSTANCE_CHANGES'
517 ||' ( DEPARTMENT_ID,'
518 ||' RESOURCE_ID,'
519 ||' RES_INSTANCE_ID,'
520 ||' SERIAL_NUMBER,'
521 /*||' EQUIPMENT_ITEM_ID,'*/
522 ||' SHIFT_NUM,'
523 ||' FROM_DATE,'
524 ||' TO_DATE,'
525 ||' FROM_TIME,'
526 ||' TO_TIME,'
527 ||' CAPACITY_CHANGE,'
528 ||' SIMULATION_SET,'
529 ||' ACTION_TYPE,'
530 ||' DELETED_FLAG,'
531 ||' REFRESH_ID,'
532 ||' SR_INSTANCE_ID)'
533 ||' SELECT'
534 ||' x.DEPARTMENT_ID,'
535 ||' x.RESOURCE_ID,'
536 ||' x.RES_INSTANCE_ID,'
537 ||' x.SERIAL_NUMBER,'
538 /*||' x.EQUIPMENT_ITEM_ID,'*/
539 ||' x.SHIFT_NUM,'
540 ||' x.FROM_DATE,'
541 ||' x.TO_DATE,'
542 ||' x.FROM_TIME,'
543 ||' x.TO_TIME,'
544 ||' x.CAPACITY_CHANGE,'
545 ||' x.SIMULATION_SET,'
546 ||' x.ACTION_TYPE,'
547 ||' 2,'
548 ||' :v_refresh_id,'
549 ||' :v_instance_id'
550 ||' FROM MRP_AP_RES_INST_CHANGES_V' ||MSC_CL_PULL.v_dblink ||' x'
551 ||' WHERE x.RN1>' ||MSC_CL_PULL.v_lrn
552 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn;
553
554 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: res_instance changes sql = '||v_sql_stmt);
555 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
556 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RES_INST_CHANGES_V='|| SQL%ROWCOUNT);
557
558 COMMIT;
559 END IF; /* MSC_CL_PULL.v_lrnn= -1 */
560
561 END IF; /* MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 */
562
563 END LOAD_CALENDAR_DATE;
564
565
566 --==================================================================
567
568 PROCEDURE LOAD_BUYER_CONTACT IS
569
570 BEGIN
571
572
573 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
574 --IF MSC_CL_PULL.ITEM_ENABLED= MSC_UTIL.SYS_YES THEN /* PREPLACE START */
575
576 MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
577 MSC_CL_PULL.v_view_name := 'MRP_AP_BUYER_CONTACTS_V';
578
579 v_sql_stmt :=
580 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
581 ||' ( NAME,'
582 ||' DISPLAY_NAME,'
583 ||' EMAIL,'
584 ||' FAX,'
585 ||' PARTNER_ID,'
586 ||' PARTNER_SITE_ID,'
587 ||' PARTNER_TYPE,'
588 ||' ENABLED_FLAG,'
589 ||' DELETED_FLAG,'
590 ||' REFRESH_ID,'
591 ||' SR_INSTANCE_ID)'
592 ||' SELECT DISTINCT'
593 ||' x.NAME,'
594 ||' x.DISPLAY_NAME,'
595 ||' x.EMAIL,'
596 ||' x.FAX,'
597 ||' x.PARTNER_ID,'
598 ||' x.PARTNER_SITE_ID,'
599 ||' 4,'
600 ||' ENABLED_FLAG,'
601 ||' 2,'
602 ||' :v_refresh_id,'
603 ||' :v_instance_id'
604 ||' FROM MRP_AP_BUYER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x'
605 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_all_org_str;
606
607 EXECUTE IMMEDIATE v_sql_stmt
608 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
609
610 COMMIT;
611
612
613 END IF; -- complete refresh
614 --END IF; -- MSC_CL_PULL.ITEM_ENABLED /* PREPLACE END */
615
616 END LOAD_BUYER_CONTACT;
617
618
619 --==================================================================
620
621 PROCEDURE LOAD_TRADING_PARTNER IS
622
623 lv_profile_inherit_op_seq NUMBER;
624 BEGIN
625
626
627 -- select the in INHERIT_OPTION_CLASS_OP_SEQ profile option in source instance
628
629 BEGIN
630 v_sql_stmt := ' select nvl(FND_PROFILE.VALUE'||MSC_CL_PULL.v_dblink||'(''BOM:CONFIG_INHERIT_OP_SEQ''),2) '
631 ||' from dual';
632 execute immediate v_sql_stmt into lv_profile_inherit_op_seq;
633 EXCEPTION
634 WHEN OTHERS THEN
635 lv_profile_inherit_op_seq := 2;
636
637 END ;
638
639 IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
640 v_temp_tp_sql := NULL;
641 ELSE
642 v_temp_tp_sql := ' AND x.LAST_UPDATE_DATE > SYSDATE - :v_msc_tp_coll_window';
643 END IF;
644
645 IF MSC_CL_PULL.VENDOR_ENABLED= MSC_UTIL.SYS_YES THEN
646
647 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
648 MSC_CL_PULL.v_view_name := 'MRP_AP_VENDORS_V';
649
650 v_sql_stmt:=
651 'insert into MSC_ST_TRADING_PARTNERS'
652 ||' ( SR_TP_ID,'
653 ||' DISABLE_DATE,'
654 ||' PARTNER_TYPE,'
655 ||' PARTNER_NAME,'
656 ||' PARTNER_NUMBER,'
657 ||' DELETED_FLAG,'
658 ||' REFRESH_ID,'
659 ||' SR_INSTANCE_ID)'
660 ||' select'
661 ||' x.SR_TP_ID,'
662 ||' x.DISABLE_DATE- :v_dgmt,'
663 ||' x.PARTNER_TYPE,'
664 ||' x.PARTNER_NAME,'
665 ||' x.PARTNER_NUMBER,'
666 ||' 2,'
667 ||' :v_refresh_id,'
668 ||' :v_instance_id'
669 ||' from MRP_AP_VENDORS_V'||MSC_CL_PULL.v_dblink||' x'
670 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
671 ||v_temp_tp_sql;
672
673 IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
674 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
675 ELSE
676 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
677 END IF;
678
679 COMMIT;
680
681 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
682 MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_SITES_V';
683
684 /* for bug: 2459612, added code to collect OPERATING_UNIT from Vendor sites for 11i source instance */
685 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
686 v_temp_sql := ' x.OPERATING_UNIT,x.shipping_control, ';
687 ELSE
688 v_temp_sql := ' NULL,NULL,';
689 END IF;
690
691 v_sql_stmt:=
692 'insert into MSC_ST_TRADING_PARTNER_SITES'
693 ||' ( TP_SITE_CODE,'
694 ||' SR_TP_ID,'
695 ||' SR_TP_SITE_ID,'
696 ||' PARTNER_ADDRESS,'
697 ||' LOCATION_ID,'
698 ||' OPERATING_UNIT_NAME,'
699 ||' OPERATING_UNIT ,'
700 ||' shipping_control ,'
701 ||' PARTNER_TYPE,'
702 ||' LONGITUDE,'
703 ||' LATITUDE,'
704 ||' DELETED_FLAG,'
705 ||' REFRESH_ID,'
706 /* SCE Change starts */
707 /* We need to capture address information in staging tables for SCE purpose */
708 ||' ADDRESS1,'
709 ||' ADDRESS2,'
710 ||' ADDRESS3,'
711 ||' ADDRESS4,'
712 ||' CITY,'
713 ||' STATE,'
714 ||' COUNTY,'
715 ||' PROVINCE,'
716 ||' COUNTRY,'
717 /* SCE Change ends */
718 ||' SR_INSTANCE_ID)'
719 ||' select'
720 ||' x.TP_SITE_CODE,'
721 ||' x.SR_TP_ID,'
722 ||' x.SR_TP_SITE_ID,'
723 ||' x.PARTNER_ADDRESS,'
724 ||' x.LOCATION_ID,'
725 ||' x.OPERATING_UNIT_NAME,'
726 || v_temp_sql
727 ||' x.PARTNER_TYPE,'
728 ||' x.LONGITUDE,'
729 ||' x.LATITUDE,'
730 ||' 2,'
731 ||' :v_refresh_id,'
732 /* SCE Change Starts */
733 ||' x.ADDRESS_LINE1,'
734 ||' x.ADDRESS_LINE2,'
735 ||' x.ADDRESS_LINE3,'
736 ||' x.ADDRESS_LINE4,'
737 ||' x.CITY,'
738 ||' x.STATE,'
739 ||' x.COUNTY,'
740 ||' x.PROVINCE,'
741 ||' x.COUNTRY,'
742 /* SCE Change Ends */
743 ||' :v_instance_id'
744 ||' from MRP_AP_VENDOR_SITES_V'||MSC_CL_PULL.v_dblink||' x'
745 ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
746 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')'
747 ||v_temp_tp_sql;
748
749 IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
750 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
751 ELSE
752 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
753 END IF;
754
755 COMMIT;
756
757 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
758
759 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
760 v_temp_sql := 'x.ORGANIZATION_ID, ';
761 ELSE
762 v_temp_sql := ' NULL, ';
763 END IF;
764
765 MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
766 MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
767
768 v_sql_stmt:=
769 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
770 ||' ( LOCATION_ID,'
771 ||' LOCATION_CODE,'
772 ||' SR_TP_ID,'
773 ||' SR_TP_SITE_ID,'
774 ||' PARTNER_TYPE,'
775 ||' ORGANIZATION_ID,'
776 ||' REFRESH_ID,'
777 ||' SR_INSTANCE_ID)'
778 ||' SELECT'
779 ||' x.LOCATION_ID,'
780 ||' x.LOCATION_CODE,'
781 ||' x.TRADING_PARTNER_ID,'
782 ||' x.TRADING_PARTNER_SITE_ID,'
783 ||' 1,'
784 || v_temp_sql
785 ||' :v_refresh_id,'
786 ||' :v_instance_id'
787 ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
788 ||' WHERE x.PARTNER_TYPE= 1';
789
790 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
791
792 COMMIT;
793
794 MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
795 MSC_CL_PULL.v_view_name := 'MRP_AP_VENDOR_CONTACTS_V';
796
797 v_sql_stmt:=
798 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
799 ||' ( NAME,'
800 ||' DISPLAY_NAME,'
801 ||' EMAIL,'
802 ||' FAX,'
803 ||' PARTNER_ID,'
804 ||' PARTNER_SITE_ID,'
805 ||' PARTNER_TYPE,'
806 ||' ENABLED_FLAG,'
807 ||' DELETED_FLAG,'
808 ||' REFRESH_ID,'
809 ||' SR_INSTANCE_ID)'
810 ||' SELECT'
811 ||' x.NAME,'
812 ||' x.DISPLAY_NAME,'
813 ||' x.EMAIL,'
814 ||' x.FAX,'
815 ||' x.PARTNER_ID,'
816 ||' x.PARTNER_SITE_ID,'
817 ||' 1,'
818 ||' ENABLED_FLAG,'
819 ||' 2,'
820 ||' :v_refresh_id,'
821 ||' :v_instance_id'
822 ||' FROM MRP_AP_VENDOR_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
823
824 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
825
826 COMMIT;
827
828 END IF; -- complete refresh
829
830 END IF; -- MSC_CL_PULL.VENDOR_ENABLED
831
832
833 IF MSC_CL_PULL.CUSTOMER_ENABLED= MSC_UTIL.SYS_YES THEN
834
835 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
836 MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMERS_V';
837
838 BEGIN
839
840 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
841 v_temp_sql2 := ' x.AGGREGATE_DEMAND_FLAG, ';
842 ELSE
843 v_temp_sql2 := ' NULL,';
844 END IF;
845 IF MSC_CL_PULL.v_apps_ver = MSC_UTIL.G_APPS107 THEN
846 v_temp_sql1 := ' NULL,';
847 ELSE
848 v_temp_sql1 := ' x.CUSTOMER_CLASS_CODE,';
849 END IF;
850
851 ------ ===== # SRP Changes ======
852 IF (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
853 v_temp_sql2 := v_temp_sql2||' x.RESOURCE_TYPE, ';
854 ELSE
855 v_temp_sql2 := v_temp_sql2||' NULL,';
856 END IF;
857
858 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
859 v_temp_sql2 := v_temp_sql2 || ' x.customer_type, x.CUST_ACCOUNT_NUMBER, ';
860 ELSE
861 v_temp_sql2 := v_temp_sql2 || ' NULL, NULL, ';
862 END IF;
863
864 END;
865
866 v_sql_stmt:=
867 'insert into MSC_ST_TRADING_PARTNERS'
868 ||' ( SR_TP_ID,'
869 ||' STATUS,'
870 ||' PARTNER_TYPE,'
871 ||' PARTNER_NAME,'
872 ||' PARTNER_NUMBER,'
873 ||' CUSTOMER_CLASS_CODE,'
874 ||' AGGREGATE_DEMAND_FLAG,'
875 ||' RESOURCE_TYPE,' --SRP Changes
876 ||' CUSTOMER_TYPE,'
877 ||' CUST_ACCOUNT_NUMBER,'
878 ||' DELETED_FLAG,'
879 ||' REFRESH_ID,'
880 ||' SR_INSTANCE_ID)'
881 ||' select'
882 ||' x.SR_TP_ID,'
883 ||' x.STATUS,'
884 ||' x.PARTNER_TYPE,'
885 ||' x.PARTNER_NAME,'
886 ||' x.PARTNER_NUMBER,'
887 || v_temp_sql1
888 || v_temp_sql2
889 ||' 2,'
890 ||' :v_refresh_id,'
891 ||' :v_instance_id'
892 ||' from MRP_AP_CUSTOMERS_V'||MSC_CL_PULL.v_dblink||' x'
893 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
894 ||v_temp_tp_sql;
895
896 IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
897 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
898 ELSE
899 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
900 END IF;
901
902 COMMIT;
903
904 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
905 MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_SITES_V';
906
907 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
908 v_temp_sql := ' x.POSTAL_CODE, x.CITY, x.STATE, x.COUNTRY,x.LOCATION_ID,x.SHIPPING_CONTROL, ';
909 ELSE
910 v_temp_sql := ' NULL,NULL,NULL,NULL,NULL,NULL, ';
911 END IF;
912
913 /* For bug: 2564735 , added substr for 30 chars on the column partner_site_number */
914 v_sql_stmt:=
915 'insert into MSC_ST_TRADING_PARTNER_SITES'
916 ||' ( TP_SITE_CODE,'
917 ||' SR_TP_ID,'
918 ||' SR_TP_SITE_ID,'
919 ||' LOCATION,'
920 ||' OPERATING_UNIT_NAME,'
921 ||' PARTNER_ADDRESS,'
922 ||' LONGITUDE,'
923 ||' LATITUDE,'
924 ||' PARTNER_TYPE,'
925 ||' POSTAL_CODE,'
926 ||' CITY,'
927 ||' STATE,'
928 ||' COUNTRY,'
929 ||' LOCATION_ID, '
930 ||' SHIPPING_CONTROL,'
931 ||' DELETED_FLAG,'
932 ||' REFRESH_ID,'
933 /* SCE Change Starts */
934 ||' ADDRESS1,'
935 ||' ADDRESS2,'
936 ||' ADDRESS3,'
937 ||' ADDRESS4,'
938 ||' PROVINCE,'
939 ||' COUNTY,'
940 ||' PARTNER_SITE_NUMBER,'
941 /* SCE Change Ends */
942 ||' SR_INSTANCE_ID)'
943 ||' select'
944 ||' x.TP_SITE_CODE,'
945 ||' x.SR_TP_ID,'
946 ||' x.SR_TP_SITE_ID,'
947 ||' x.LOCATION,'
948 ||' x.OPERATING_UNIT_NAME,'
949 ||' x.PARTNER_ADDRESS,'
950 ||' x.LONGITUDE,'
951 ||' x.LATITUDE,'
952 ||' x.PARTNER_TYPE,'
953 || v_temp_sql
954 ||' 2,'
955 ||' :v_refresh_id,'
956 /* SCE Change Starts */
957 ||' x.ADDRESS1,'
958 ||' x.ADDRESS2,'
959 ||' x.ADDRESS3,'
960 ||' x.ADDRESS4,'
961 ||' x.PROVINCE,'
962 ||' x.COUNTY,'
963 ||' substr(x.PARTNER_SITE_NUMBER,1,30), '
964 /* SCE Change Ends */
965 ||' :v_instance_id'
966 ||' from MRP_AP_CUSTOMER_SITES_V'||MSC_CL_PULL.v_dblink||' x'
967 ||' WHERE (x.RN1>'||MSC_CL_PULL.v_lrn
968 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
969 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')'
970 ||v_temp_tp_sql;
971
972 IF MSC_UTIL.v_msc_tp_coll_window = 0 THEN
973 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
974 ELSE
975 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_UTIL.v_msc_tp_coll_window;
976 END IF;
977
978 COMMIT;
979
980 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
981
982 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
983 v_temp_sql := 'x.ORGANIZATION_ID, ';
984 ELSE
985 v_temp_sql := ' NULL, ';
986 END IF;
987
988 MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
989 MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
990
991 v_sql_stmt:=
992 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
993 ||' ( LOCATION_ID,'
994 ||' LOCATION_CODE,'
995 ||' SR_TP_ID,'
996 ||' SR_TP_SITE_ID,'
997 ||' PARTNER_TYPE,'
998 ||' ORGANIZATION_ID,'
999 ||' REFRESH_ID,'
1000 ||' SR_INSTANCE_ID)'
1001 ||' SELECT'
1002 ||' x.LOCATION_ID,'
1003 ||' x.LOCATION_CODE,'
1004 ||' x.TRADING_PARTNER_ID,'
1005 ||' x.TRADING_PARTNER_SITE_ID,'
1006 ||' 2,'
1007 || v_temp_sql
1008 ||' :v_refresh_id,'
1009 ||' :v_instance_id'
1010 ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1011 ||' WHERE x.PARTNER_TYPE= 2';
1012
1013 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1014
1015 COMMIT;
1016
1017 MSC_CL_PULL.v_table_name:= 'MSC_ST_PARTNER_CONTACTS';
1018 MSC_CL_PULL.v_view_name := 'MRP_AP_CUSTOMER_CONTACTS_V';
1019
1020 v_sql_stmt:=
1021 'INSERT INTO MSC_ST_PARTNER_CONTACTS'
1022 ||' ( NAME,'
1023 ||' DISPLAY_NAME,'
1024 ||' EMAIL,'
1025 ||' FAX,'
1026 ||' PARTNER_ID,'
1027 ||' PARTNER_SITE_ID,'
1028 ||' PARTNER_TYPE,'
1029 ||' ENABLED_FLAG,'
1030 ||' DELETED_FLAG,'
1031 ||' REFRESH_ID,'
1032 ||' SR_INSTANCE_ID)'
1033 ||' SELECT'
1034 ||' x.NAME,'
1035 ||' x.DISPLAY_NAME,'
1036 ||' x.EMAIL,'
1037 ||' x.FAX,'
1038 ||' x.PARTNER_ID,'
1039 ||' x.PARTNER_SITE_ID,'
1040 ||' 2,'
1041 ||' ENABLED_FLAG,'
1042 ||' 2,'
1043 ||' :v_refresh_id,'
1044 ||' :v_instance_id'
1045 ||' FROM MRP_AP_CUSTOMER_CONTACTS_V'||MSC_CL_PULL.v_dblink||' x';
1046
1047 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1048
1049 COMMIT;
1050
1051 END IF; -- complete refresh
1052
1053 END IF; -- CUSTOMER ENABLED
1054
1055 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNERS';
1056 MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATIONS_V';
1057
1058 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1059 v_temp_sql:= 'x.BUSINESS_GROUP_ID,x.LEGAL_ENTITY, x.SET_OF_BOOKS_ID, x.CHART_OF_ACCOUNTS_ID, x.BUSINESS_GROUP_NAME,x.LEGAL_ENTITY_NAME, x.OPERATING_UNIT_NAME, ';
1060 ELSE
1061 v_temp_sql := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1062 END IF;
1063
1064
1065 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
1066 v_temp_sql5 := 'x.SUBCONTRACTING_SOURCE_ORG, ';
1067 ELSE
1068 v_temp_sql5 := 'NULL,';
1069 END IF;
1070
1071 v_sql_stmt:=
1072 'insert into MSC_ST_TRADING_PARTNERS'
1073 ||' ( ORGANIZATION_CODE,'
1074 ||' ORGANIZATION_TYPE,'
1075 ||' SR_TP_ID,'
1076 ||' MASTER_ORGANIZATION,'
1077 ||' SOURCE_ORG_ID,'
1078 ||' PARTNER_TYPE,'
1079 ||' PARTNER_NAME,'
1080 ||' CALENDAR_CODE,'
1081 ||' CURRENCY_CODE,'
1082 ||' CALENDAR_EXCEPTION_SET_ID,'
1083 ||' OPERATING_UNIT,'
1084 ||' MAXIMUM_WEIGHT,'
1085 ||' MAXIMUM_VOLUME,'
1086 ||' WEIGHT_UOM,'
1087 ||' VOLUME_UOM,'
1088 ||' PROJECT_REFERENCE_ENABLED,'
1089 ||' PROJECT_CONTROL_LEVEL,'
1090 ||' MODELED_CUSTOMER_ID,'
1091 ||' MODELED_CUSTOMER_SITE_ID,'
1092 ||' MODELED_SUPPLIER_ID,'
1093 ||' MODELED_SUPPLIER_SITE_ID,'
1094 ||' USE_PHANTOM_ROUTINGS,'
1095 ||' INHERIT_PHANTOM_OP_SEQ,'
1096 ||' DEFAULT_ATP_RULE_ID,'
1097 ||' DEFAULT_DEMAND_CLASS,'
1098 ||' MATERIAL_ACCOUNT,'
1099 ||' EXPENSE_ACCOUNT,'
1100 ||' DEMAND_LATENESS_COST,'
1101 ||' SUPPLIER_CAP_OVERUTIL_COST,'
1102 ||' RESOURCE_CAP_OVERUTIL_COST,'
1103 ||' TRANSPORT_CAP_OVER_UTIL_COST,'
1104 ||' BUSINESS_GROUP_ID, '
1105 ||' LEGAL_ENTITY, '
1106 ||' SET_OF_BOOKS_ID, '
1107 ||' CHART_OF_ACCOUNTS_ID,'
1108 ||' BUSINESS_GROUP_NAME, '
1109 ||' LEGAL_ENTITY_NAME, '
1110 ||' OPERATING_UNIT_NAME,'
1111 ||' SUBCONTRACTING_SOURCE_ORG,'
1112 ||' DELETED_FLAG,'
1113 ||' REFRESH_ID,'
1114 ||' SR_INSTANCE_ID,'
1115 ||' INHERIT_OC_OP_SEQ_NUM)'
1116 ||' select'
1117 ||' :V_ICODE||x.ORGANIZATION_CODE,'
1118 ||' 1,' -- set to discrete as the default value.
1119 ||' x.SR_TP_ID,'
1120 ||' x.MASTER_ORGANIZATION,'
1121 ||' x.SOURCE_ORG_ID,'
1122 ||' x.PARTNER_TYPE,'
1123 ||' :V_ICODE||x.PARTNER_NAME,'
1124 ||' :V_ICODE||x.CALENDAR_CODE,'
1125 ||' x.CURRENCY_CODE,'
1126 ||' x.CALENDAR_EXCEPTION_SET_ID,'
1127 ||' x.OPERATING_UNIT,'
1128 ||' x.MAXIMUM_WEIGHT,'
1129 ||' x.MAXIMUM_VOLUME,'
1130 ||' x.WEIGHT_UOM,'
1131 ||' x.VOLUME_UOM,'
1132 ||' x.PROJECT_REFERENCE_ENABLED,'
1133 ||' x.PROJECT_CONTROL_LEVEL,'
1134 ||' x.MODELED_CUSTOMER_ID,'
1135 ||' x.MODELED_CUSTOMER_SITE_ID,'
1136 ||' x.MODELED_SUPPLIER_ID,'
1137 ||' x.MODELED_SUPPLIER_SITE_ID,'
1138 ||' x.USE_PHANTOM_ROUTINGS,'
1139 ||' x.INHERIT_PHANTOM_OP_SEQ,'
1140 ||' x.DEFAULT_ATP_RULE_ID,'
1141 ||' x.DEFAULT_DEMAND_CLASS,'
1142 ||' x.MATERIAL_ACCOUNT,'
1143 ||' x.EXPENSE_ACCOUNT,'
1144 ||' TO_NUMBER(DECODE( :v_mso_org_dmd_penalty,'
1145 ||' 1, x.Attribute1,'
1146 ||' 2, x.Attribute2,'
1147 ||' 3, x.Attribute3,'
1148 ||' 4, x.Attribute4,'
1149 ||' 5, x.Attribute5,'
1150 ||' 6, x.Attribute6,'
1151 ||' 7, x.Attribute7,'
1152 ||' 8, x.Attribute8,'
1153 ||' 9, x.Attribute9,'
1154 ||' 10, x.Attribute10,'
1155 ||' 11, x.Attribute11,'
1156 ||' 12, x.Attribute12,'
1157 ||' 13, x.Attribute13,'
1158 ||' 14, x.Attribute14,'
1159 ||' 15, x.Attribute15)),'
1160 ||' TO_NUMBER(DECODE( :v_mso_org_item_penalty,'
1161 ||' 1, x.Attribute1,'
1162 ||' 2, x.Attribute2,'
1163 ||' 3, x.Attribute3,'
1164 ||' 4, x.Attribute4,'
1165 ||' 5, x.Attribute5,'
1166 ||' 6, x.Attribute6,'
1167 ||' 7, x.Attribute7,'
1168 ||' 8, x.Attribute8,'
1169 ||' 9, x.Attribute9,'
1170 ||' 10, x.Attribute10,'
1171 ||' 11, x.Attribute11,'
1172 ||' 12, x.Attribute12,'
1173 ||' 13, x.Attribute13,'
1174 ||' 14, x.Attribute14,'
1175 ||' 15, x.Attribute15)),'
1176 ||' TO_NUMBER(DECODE( :v_mso_org_res_penalty,'
1177 ||' 1, x.Attribute1,'
1178 ||' 2, x.Attribute2,'
1179 ||' 3, x.Attribute3,'
1180 ||' 4, x.Attribute4,'
1181 ||' 5, x.Attribute5,'
1182 ||' 6, x.Attribute6,'
1183 ||' 7, x.Attribute7,'
1184 ||' 8, x.Attribute8,'
1185 ||' 9, x.Attribute9,'
1186 ||' 10, x.Attribute10,'
1187 ||' 11, x.Attribute11,'
1188 ||' 12, x.Attribute12,'
1189 ||' 13, x.Attribute13,'
1190 ||' 14, x.Attribute14,'
1191 ||' 15, x.Attribute15)),'
1192 ||' TO_NUMBER(DECODE( :v_mso_org_trsp_penalty,'
1193 ||' 1, x.Attribute1,'
1194 ||' 2, x.Attribute2,'
1195 ||' 3, x.Attribute3,'
1196 ||' 4, x.Attribute4,'
1197 ||' 5, x.Attribute5,'
1198 ||' 6, x.Attribute6,'
1199 ||' 7, x.Attribute7,'
1200 ||' 8, x.Attribute8,'
1201 ||' 9, x.Attribute9,'
1202 ||' 10, x.Attribute10,'
1203 ||' 11, x.Attribute11,'
1204 ||' 12, x.Attribute12,'
1205 ||' 13, x.Attribute13,'
1206 ||' 14, x.Attribute14,'
1207 ||' 15, x.Attribute15)),'
1208 || v_temp_sql
1209 || v_temp_sql5
1210 ||' 2,'
1211 ||' :v_refresh_id,'
1212 ||' :v_instance_id,'
1213 ||' :lv_profile_inherit_op_seq'
1214 ||' from MRP_AP_ORGANIZATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1215 ||' where NVL( x.LANGUAGE, :v_lang)= :v_lang'
1216 ||' AND x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1217 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1218 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1219 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1220
1221 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.V_ICODE,
1222 MSC_CL_PULL.V_ICODE,
1223 MSC_CL_PULL.V_ICODE,
1224 MSC_CL_PULL.v_mso_org_dmd_penalty,
1225 MSC_CL_PULL.v_mso_org_item_penalty,
1226 MSC_CL_PULL.v_mso_org_res_penalty,
1227 MSC_CL_PULL.v_mso_org_trsp_penalty,
1228 MSC_CL_PULL.v_refresh_id,
1229 MSC_CL_PULL.v_instance_id,
1230 lv_profile_inherit_op_seq,
1231 MSC_CL_PULL.v_lang,
1232 MSC_CL_PULL.v_lang;
1233
1234 COMMIT;
1235
1236 MSC_CL_PULL.v_table_name:= 'MSC_ST_TRADING_PARTNER_SITES';
1237 MSC_CL_PULL.v_view_name := 'MRP_AP_ORGANIZATION_SITES_V';
1238
1239 /* For bug: 2564735 , added substr for 60 chars on the columns COUNTY and STATE becos these columns
1240 are of higher size in the views */
1241 v_sql_stmt:=
1242 'insert into MSC_ST_TRADING_PARTNER_SITES'
1243 ||' ( SR_TP_ID,'
1244 ||' SR_TP_SITE_ID,'
1245 ||' LOCATION,'
1246 ||' PARTNER_ADDRESS,'
1247 /* SCE Changes start */
1248 ||' ADDRESS1,'
1249 ||' ADDRESS2,'
1250 ||' ADDRESS3,'
1251 ||' CITY,'
1252 ||' COUNTY,'
1253 ||' STATE,'
1254 ||' POSTAL_CODE,'
1255 ||' COUNTRY,'
1256 /* SCE Changes end */
1257 ||' LONGITUDE,'
1258 ||' LATITUDE,'
1259 ||' PARTNER_TYPE,'
1260 ||' DELETED_FLAG,'
1261 ||' REFRESH_ID,'
1262 ||' SR_INSTANCE_ID)'
1263 ||' select'
1264 ||' x.SR_TP_ID,'
1265 ||' x.SR_TP_SITE_ID,'
1266 ||' x.LOCATION,'
1267 ||' x.PARTNER_ADDRESS,'
1268 /* SCE Changes start */
1269 ||' x.ADDRESS_LINE_1,'
1270 ||' x.ADDRESS_LINE_2,'
1271 ||' x.ADDRESS_LINE_3,'
1272 ||' x.CITY,'
1273 ||' substr(x.COUNTY,1,60),'
1274 ||' substr(x.STATE,1,60),'
1275 ||' x.POSTAL_CODE,'
1276 ||' x.COUNTRY,'
1277 /* SCE Changes end */
1278 ||' x.LONGITUDE,'
1279 ||' x.LATITUDE,'
1280 ||' x.PARTNER_TYPE,'
1281 ||' 2,'
1282 ||' :v_refresh_id,'
1283 ||' :v_instance_id'
1284 ||' from MRP_AP_ORGANIZATION_SITES_V'||MSC_CL_PULL.v_dblink||' x'
1285 ||' WHERE x.SR_TP_ID'||MSC_UTIL.v_in_all_org_str
1286 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1287 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1288 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1289
1290 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1291
1292 COMMIT;
1293
1294 /* added this fix for bug # 2198339 to collect location associations for the Orgs */
1295 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete,targeted refresh
1296
1297 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1298 v_temp_sql := 'x.ORGANIZATION_ID, ';
1299 ELSE
1300 v_temp_sql := ' NULL, ';
1301 END IF;
1302
1303 MSC_CL_PULL.v_table_name:= 'MSC_ST_LOCATION_ASSOCIATIONS';
1304 MSC_CL_PULL.v_view_name := 'MRP_AP_LOCATION_ASSOCIATIONS_V';
1305
1306 v_sql_stmt:=
1307 'INSERT INTO MSC_ST_LOCATION_ASSOCIATIONS'
1308 ||' ( LOCATION_ID,'
1309 ||' LOCATION_CODE,'
1310 ||' SR_TP_ID,'
1311 ||' SR_TP_SITE_ID,'
1312 ||' PARTNER_TYPE,'
1313 ||' ORGANIZATION_ID,'
1314 ||' REFRESH_ID,'
1315 ||' SR_INSTANCE_ID)'
1316 ||' SELECT'
1317 ||' x.LOCATION_ID,'
1318 ||' x.LOCATION_CODE,'
1319 ||' x.TRADING_PARTNER_ID,'
1320 ||' x.TRADING_PARTNER_SITE_ID,'
1321 ||' 3,'
1322 || v_temp_sql
1323 ||' :v_refresh_id,'
1324 ||' :v_instance_id'
1325 ||' FROM MRP_AP_LOCATION_ASSOCIATIONS_V'||MSC_CL_PULL.v_dblink||' x'
1326 ||' WHERE x.PARTNER_TYPE= 3';
1327
1328 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1329
1330 END IF; -- complete refresh
1331
1332 COMMIT;
1333
1334 END LOAD_TRADING_PARTNER;
1335
1336
1337 --==================================================================
1338
1339 PROCEDURE LOAD_PARAMETER IS
1340 BEGIN
1341
1342 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1343
1344 MSC_CL_PULL.v_table_name:= 'MSC_ST_PARAMETERS';
1345 MSC_CL_PULL.v_view_name := 'MRP_AP_PARAMETERS_V';
1346
1347 v_sql_stmt:=
1348 ' insert into MSC_ST_PARAMETERS'
1349 ||' ( ORGANIZATION_ID,'
1350 ||' DEMAND_TIME_FENCE_FLAG,'
1351 ||' PLANNING_TIME_FENCE_FLAG,'
1352 ||' OPERATION_SCHEDULE_TYPE,'
1353 ||' CONSIDER_WIP,'
1354 ||' CONSIDER_PO,'
1355 ||' SNAPSHOT_LOCK,'
1356 ||' PLAN_SAFETY_STOCK,'
1357 ||' CONSIDER_RESERVATIONS,'
1358 ||' PART_INCLUDE_TYPE,'
1359 ||' DEFAULT_ABC_ASSIGNMENT_GROUP,'
1360 ||' PERIOD_TYPE,'
1361 ||' RESCHED_ASSUMPTION,'
1362 ||' PLAN_DATE_DEFAULT_TYPE,'
1363 ||' INCLUDE_REP_SUPPLY_DAYS,'
1364 ||' INCLUDE_MDS_DAYS,'
1365 ||' REPETITIVE_HORIZON1,'
1366 ||' REPETITIVE_HORIZON2,'
1367 ||' REPETITIVE_BUCKET_SIZE1,'
1368 ||' REPETITIVE_BUCKET_SIZE2,'
1369 ||' REPETITIVE_BUCKET_SIZE3,'
1370 ||' REPETITIVE_ANCHOR_DATE,'
1371 ||' DELETED_FLAG,'
1372 ||' REFRESH_ID,'
1373 ||' SR_INSTANCE_ID)'
1374 ||' select'
1375 ||' x.ORGANIZATION_ID,'
1376 ||' x.DEMAND_TIME_FENCE_FLAG,'
1377 ||' x.PLANNING_TIME_FENCE_FLAG,'
1378 ||' x.OPERATION_SCHEDULE_TYPE,'
1379 ||' x.CONSIDER_WIP,'
1380 ||' x.CONSIDER_PO,'
1381 ||' x.SNAPSHOT_LOCK,'
1382 ||' x.PLAN_SAFETY_STOCK,'
1383 ||' x.CONSIDER_RESERVATIONS,'
1384 ||' x.PART_INCLUDE_TYPE,'
1385 ||' x.DEFAULT_ABC_ASSIGNMENT_GROUP,'
1386 ||' x.PERIOD_TYPE,'
1387 ||' x.RESCHED_ASSUMPTION,'
1388 ||' x.PLAN_DATE_DEFAULT_TYPE,'
1389 ||' x.INCLUDE_REP_SUPPLY_DAYS,'
1390 ||' x.INCLUDE_MDS_DAYS,'
1391 ||' x.REPETITIVE_HORIZON1,'
1392 ||' x.REPETITIVE_HORIZON2,'
1393 ||' x.REPETITIVE_BUCKET_SIZE1,'
1394 ||' x.REPETITIVE_BUCKET_SIZE2,'
1395 ||' x.REPETITIVE_BUCKET_SIZE3,'
1396 ||' x.REPETITIVE_ANCHOR_DATE,'
1397 ||' 2,'
1398 ||' :v_refresh_id,'
1399 ||' :v_instance_id'
1400 ||' from MRP_AP_PARAMETERS_V'||MSC_CL_PULL.v_dblink||' x'
1401 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1402
1403 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1404
1405 COMMIT;
1406
1407 END IF;
1408
1409 END LOAD_PARAMETER;
1410
1411 --==================================================================
1412
1413 PROCEDURE LOAD_UOM IS
1414 BEGIN
1415
1416 IF MSC_CL_PULL.UOM_ENABLED= MSC_UTIL.SYS_YES THEN
1417
1418 MSC_CL_PULL.v_table_name:= 'MSC_ST_UNITS_OF_MEASURE';
1419 MSC_CL_PULL.v_view_name := 'MRP_AP_UNITS_OF_MEASURE_V';
1420
1421 v_sql_stmt:=
1422 ' insert into MSC_ST_UNITS_OF_MEASURE'
1423 ||' ( UNIT_OF_MEASURE,'
1424 ||' UOM_CODE,'
1425 ||' UOM_CLASS,'
1426 ||' BASE_UOM_FLAG,'
1427 ||' DISABLE_DATE,'
1428 ||' DESCRIPTION,'
1429 ||' DELETED_FLAG,'
1430 ||' REFRESH_ID,'
1431 ||' SR_INSTANCE_ID)'
1432 ||' select'
1433 ||' x.UNIT_OF_MEASURE,'
1434 ||' x.UOM_CODE,'
1435 ||' x.UOM_CLASS,'
1436 ||' x.BASE_UOM_FLAG,'
1437 ||' x.DISABLE_DATE- :v_dgmt,'
1438 ||' x.DESCRIPTION,'
1439 ||' 2,'
1440 ||' :v_refresh_id,'
1441 ||' :v_instance_id'
1442 ||' from MRP_AP_UNITS_OF_MEASURE_V'||MSC_CL_PULL.v_dblink||' x'
1443 ||' where NVL(x.LANGUAGE, :v_lang)= :v_lang'
1444 ||' AND x.RN1>'||MSC_CL_PULL.v_lrn;
1445
1446 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lang, MSC_CL_PULL.v_lang;
1447
1448 /* Removed the forked code for 11i source as the column LANGUAGE is again added in
1449 the view mtl_units_of_measures_vl by the Oracle Inventory team
1450 */
1451
1452 COMMIT;
1453
1454 MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CLASS_CONVERSIONS';
1455 MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CLASS_CONVERSIONS_V';
1456
1457 v_sql_stmt:=
1458 ' insert into MSC_ST_UOM_CLASS_CONVERSIONS'
1459 ||' ( INVENTORY_ITEM_ID,'
1460 ||' FROM_UNIT_OF_MEASURE,'
1461 ||' FROM_UOM_CODE,'
1462 ||' FROM_UOM_CLASS,'
1463 ||' TO_UNIT_OF_MEASURE,'
1464 ||' TO_UOM_CODE,'
1465 ||' TO_UOM_CLASS,'
1466 ||' CONVERSION_RATE,'
1467 ||' DISABLE_DATE,'
1468 ||' DELETED_FLAG,'
1469 ||' REFRESH_ID,'
1470 ||' SR_INSTANCE_ID)'
1471 ||' select'
1472 ||' x.INVENTORY_ITEM_ID,'
1473 ||' x.FROM_UNIT_OF_MEASURE,'
1474 ||' x.FROM_UOM_CODE,'
1475 ||' x.FROM_UOM_CLASS,'
1476 ||' x.TO_UNIT_OF_MEASURE,'
1477 ||' x.TO_UOM_CODE,'
1478 ||' x.TO_UOM_CLASS,'
1479 ||' x.CONVERSION_RATE,'
1480 ||' x.DISABLE_DATE- :v_dgmt,'
1481 ||' 2,'
1482 ||' :v_refresh_id,'
1483 ||' :v_instance_id'
1484 ||' from MRP_AP_UOM_CLASS_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1485 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1486 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1487 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1488
1489 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1490
1491 COMMIT;
1492
1493 MSC_CL_PULL.v_table_name:= 'MSC_ST_UOM_CONVERSIONS';
1494 MSC_CL_PULL.v_view_name := 'MRP_AP_UOM_CONVERSIONS_V';
1495
1496 v_sql_stmt:=
1497 ' insert into MSC_ST_UOM_CONVERSIONS'
1498 ||' ( UNIT_OF_MEASURE,'
1499 ||' UOM_CODE,'
1500 ||' UOM_CLASS,'
1501 ||' INVENTORY_ITEM_ID,'
1502 ||' CONVERSION_RATE,'
1503 ||' DEFAULT_CONVERSION_FLAG,'
1504 ||' DISABLE_DATE,'
1505 ||' DELETED_FLAG,'
1506 ||' REFRESH_ID,'
1507 ||' SR_INSTANCE_ID)'
1508 ||' select'
1509 ||' UNIT_OF_MEASURE,'
1510 ||' UOM_CODE,'
1511 ||' UOM_CLASS,'
1512 ||' INVENTORY_ITEM_ID,'
1513 ||' CONVERSION_RATE,'
1514 ||' DEFAULT_CONVERSION_FLAG,'
1515 ||' DISABLE_DATE,'
1516 ||' 2,'
1517 ||' :v_refresh_id,'
1518 ||' :v_instance_id'
1519 ||' from MRP_AP_UOM_CONVERSIONS_V'||MSC_CL_PULL.v_dblink||' x'
1520 ||' WHERE x.RN1>'||MSC_CL_PULL.v_lrn
1521 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1522 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn;
1523
1524 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1525
1526 COMMIT;
1527
1528 END IF;
1529
1530 END LOAD_UOM;
1531
1532
1533 END MSC_CL_SETUP_PULL;