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