[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_BOM_PULL
Source
1 PACKAGE BODY MSC_CL_BOM_PULL AS -- body
2 /* $Header: MSCPBOMB.pls 120.10.12020000.4 2012/10/08 20:40:46 varajgop ship $ */
3 /* $Header:*/
4
5 v_union_sql varchar2(32767);
6 v_temp_tp_sql VARCHAR2(100);
7 v_sql_stmt VARCHAR2(32767);
8 v_temp_sql VARCHAR2(15000);
9 v_temp_sql1 VARCHAR2(1000);
10 v_temp_sql2 VARCHAR2(1000);
11 v_temp_sql3 VARCHAR2(1000);
12 v_temp_sql4 VARCHAR2(1000);
13 v_temp_eam_sql 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 -- ===============================================================
19
20 PROCEDURE LOAD_BOM IS
21 v_applsys_schema VARCHAR2(32);
22 lv_retval boolean;
23 lv_dummy1 varchar2(32);
24 lv_dummy2 varchar2(32);
25 lv_icode varchar2(3);
26 BEGIN
27 lv_retval := FND_INSTALLATION.GET_APP_INFO (
28 'FND', lv_dummy1, lv_dummy2, v_applsys_schema);
29
30 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
31
32 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
33 v_temp_sql1 := ' x.BASIS_TYPE, x.Old_Component_Sequence_ID,';
34 ELSE
35 v_temp_sql1 := ' NULL, NULL,';
36 END IF;
37
38 --=================== Net Change Mode: Delete ==================
39 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
40
41 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOM_COMPONENTS';
42 MSC_CL_PULL.v_view_name := 'MRP_AD_BOM_COMPONENTS_V';
43
44 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
45 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
46 ELSE
47 v_temp_sql := NULL;
48 END IF;
49
50 v_sql_stmt:=
51 ' insert into MSC_ST_BOM_COMPONENTS'
52 ||'( COMPONENT_SEQUENCE_ID,'
53 ||' BILL_SEQUENCE_ID,'
54 ||' DELETED_FLAG,'
55 ||' REFRESH_ID,'
56 ||' SR_INSTANCE_ID)'
57 ||' select '
58 ||' x.COMPONENT_SEQUENCE_ID,'
59 ||' x.BILL_SEQUENCE_ID,'
60 ||' 1,'
61 ||' :v_refresh_id,'
62 ||' :v_instance_id'
63 ||' from MRP_AD_BOM_COMPONENTS_V'||MSC_CL_PULL.v_dblink||' x'
64 ||' WHERE x.RN> :v_lrn '
65 || v_temp_sql;
66
67
68 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
69
70 COMMIT;
71
72 END IF;
73
74 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOM_COMPONENTS';
75
76
77
78 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
79 v_rounding_sql := 'decode(x.rounding_direction,0,3,1,2,2,1,3),';
80 ELSE
81 v_rounding_sql :='3,';
82 END IF ;
83
84 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
85
86 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
87 MSC_CL_PULL.v_view_name := 'MRP_AN_BOM_COMPONENTS_V';
88 ELSE
89 MSC_CL_PULL.v_view_name := 'MRP_AP_BOM_COMPONENTS_V';
90 END IF;
91
92
93 v_union_sql :=
94 ' AND ( x.RN1> :v_lrn )'
95 ||' UNION '
96 ||' select '
97 ||' x.COMPONENT_SEQUENCE_ID,'
98 ||' x.INVENTORY_ITEM_ID,'
99 ||' x.BILL_SEQUENCE_ID,'
100 ||' x.OPERATION_SEQ_NUM,'
101 ||' x.USAGE_QUANTITY,'
102 ||' x.COMPONENT_YIELD_FACTOR,'
103 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
104 ||' x.DISABLE_DATE- :v_dgmt,'
105 ||' x.OPERATION_OFFSET_PERCENT,'
106 ||' x.OPTIONAL_COMPONENT,'
107 ||' x.WIP_SUPPLY_TYPE,'
108 ||' x.PLANNING_FACTOR,'
109 ||' x.REVISED_ITEM_SEQUENCE_ID,'
110 ||' x.ATP_FLAG,'
111 ||' x.STATUS_TYPE,'
112 ||' x.USE_UP_CODE,'
113 ||' x.CHANGE_NOTICE,'
114 ||' x.ORGANIZATION_ID,'
115 ||' x.USING_ASSEMBLY_ID,'
116 ||' x.FROM_UNIT_NUMBER,'
117 ||' x.TO_UNIT_NUMBER,'
118 ||' x.DRIVING_ITEM_ID,'
119 ||' 2,'
120 || v_rounding_sql
121 || v_temp_sql1
122 ||' :v_refresh_id,'
123 ||' :v_instance_id'
124 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
125 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
126 ||' AND ( x.RN2> :v_lrn )'
127 /* NCP
128 ||' UNION '
129 ||' select '
130 ||' x.COMPONENT_SEQUENCE_ID,'
131 ||' x.INVENTORY_ITEM_ID,'
132 ||' x.BILL_SEQUENCE_ID,'
133 ||' x.USAGE_QUANTITY,'
134 ||' x.COMPONENT_YIELD_FACTOR,'
135 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
136 ||' x.DISABLE_DATE- :v_dgmt,'
137 ||' x.OPERATION_OFFSET_PERCENT,'
138 ||' x.OPTIONAL_COMPONENT,'
139 ||' x.WIP_SUPPLY_TYPE,'
140 ||' x.PLANNING_FACTOR,'
141 ||' x.REVISED_ITEM_SEQUENCE_ID,'
142 ||' x.ATP_FLAG,'
143 ||' x.STATUS_TYPE,'
144 ||' x.USE_UP_CODE,'
145 ||' x.CHANGE_NOTICE,'
146 ||' x.ORGANIZATION_ID,'
147 ||' x.USING_ASSEMBLY_ID,'
148 ||' x.FROM_UNIT_NUMBER,'
149 ||' x.TO_UNIT_NUMBER,'
150 ||' x.DRIVING_ITEM_ID,'
151 ||' 2,'
152 || v_rounding_sql
153 ||' :v_refresh_id,'
154 ||' :v_instance_id'
155 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
156 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
157 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
158 */
159
160 ||' UNION '
161 ||' select '
162 ||' x.COMPONENT_SEQUENCE_ID,'
163 ||' x.INVENTORY_ITEM_ID,'
164 ||' x.BILL_SEQUENCE_ID,'
165 ||' x.OPERATION_SEQ_NUM,'
166 ||' x.USAGE_QUANTITY,'
167 ||' x.COMPONENT_YIELD_FACTOR,'
168 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
169 ||' x.DISABLE_DATE- :v_dgmt,'
170 ||' x.OPERATION_OFFSET_PERCENT,'
171 ||' x.OPTIONAL_COMPONENT,'
172 ||' x.WIP_SUPPLY_TYPE,'
173 ||' x.PLANNING_FACTOR,'
174 ||' x.REVISED_ITEM_SEQUENCE_ID,'
175 ||' x.ATP_FLAG,'
176 ||' x.STATUS_TYPE,'
177 ||' x.USE_UP_CODE,'
178 ||' x.CHANGE_NOTICE,'
179 ||' x.ORGANIZATION_ID,'
180 ||' x.USING_ASSEMBLY_ID,'
181 ||' x.FROM_UNIT_NUMBER,'
182 ||' x.TO_UNIT_NUMBER,'
183 ||' x.DRIVING_ITEM_ID,'
184 ||' 2,'
185 || v_rounding_sql
186 || v_temp_sql1
187 ||' :v_refresh_id,'
188 ||' :v_instance_id'
189 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
190 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
191 ||' AND ( x.RN3> :v_lrn )'
192 ||' UNION '
193 ||' select '
194 ||' x.COMPONENT_SEQUENCE_ID,'
195 ||' x.INVENTORY_ITEM_ID,'
196 ||' x.BILL_SEQUENCE_ID,'
197 ||' x.OPERATION_SEQ_NUM,'
198 ||' x.USAGE_QUANTITY,'
199 ||' x.COMPONENT_YIELD_FACTOR,'
200 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
201 ||' x.DISABLE_DATE- :v_dgmt,'
202 ||' x.OPERATION_OFFSET_PERCENT,'
203 ||' x.OPTIONAL_COMPONENT,'
204 ||' x.WIP_SUPPLY_TYPE,'
205 ||' x.PLANNING_FACTOR,'
206 ||' x.REVISED_ITEM_SEQUENCE_ID,'
207 ||' x.ATP_FLAG,'
208 ||' x.STATUS_TYPE,'
209 ||' x.USE_UP_CODE,'
210 ||' x.CHANGE_NOTICE,'
211 ||' x.ORGANIZATION_ID,'
212 ||' x.USING_ASSEMBLY_ID,'
213 ||' x.FROM_UNIT_NUMBER,'
214 ||' x.TO_UNIT_NUMBER,'
215 ||' x.DRIVING_ITEM_ID,'
216 ||' 2,'
217 || v_rounding_sql
218 || v_temp_sql1
219 ||' :v_refresh_id,'
220 ||' :v_instance_id'
221 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
222 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
223 ||' AND ( x.RN4> :v_lrn )';
224 /*
225 ||' UNION '
226 ||' select '
227 ||' x.COMPONENT_SEQUENCE_ID,'
228 ||' x.INVENTORY_ITEM_ID,'
229 ||' x.BILL_SEQUENCE_ID,'
230 ||' x.USAGE_QUANTITY,'
231 ||' x.COMPONENT_YIELD_FACTOR,'
232 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
233 ||' x.DISABLE_DATE- :v_dgmt,'
234 ||' x.OPERATION_OFFSET_PERCENT,'
235 ||' x.OPTIONAL_COMPONENT,'
236 ||' x.WIP_SUPPLY_TYPE,'
237 ||' x.PLANNING_FACTOR,'
238 ||' x.REVISED_ITEM_SEQUENCE_ID,'
239 ||' x.ATP_FLAG,'
240 ||' x.STATUS_TYPE,'
241 ||' x.USE_UP_CODE,'
242 ||' x.CHANGE_NOTICE,'
243 ||' x.ORGANIZATION_ID,'
244 ||' x.USING_ASSEMBLY_ID,'
245 ||' x.FROM_UNIT_NUMBER,'
246 ||' x.TO_UNIT_NUMBER,'
247 ||' x.DRIVING_ITEM_ID,'
248 ||' 2,'
249 || v_rounding_sql
250 ||' :v_refresh_id,'
251 ||' :v_instance_id'
252 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
253 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
254 ||' AND ( x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
255 */
256
257 ELSE
258 MSC_CL_PULL.v_view_name := 'MRP_AP_BOM_COMPONENTS_V';
259 v_union_sql := ' ';
260
261 END IF;
262 v_sql_stmt:=
263 ' insert into MSC_ST_BOM_COMPONENTS'
264 ||'( COMPONENT_SEQUENCE_ID,'
265 ||' INVENTORY_ITEM_ID,'
266 ||' BILL_SEQUENCE_ID,'
267 ||' OPERATION_SEQ_NUM,'
268 ||' USAGE_QUANTITY,'
269 ||' COMPONENT_YIELD_FACTOR,'
270 ||' EFFECTIVITY_DATE,'
271 ||' DISABLE_DATE,'
272 ||' OPERATION_OFFSET_PERCENT,'
273 ||' OPTIONAL_COMPONENT,'
274 ||' WIP_SUPPLY_TYPE,'
275 ||' PLANNING_FACTOR,'
276 ||' REVISED_ITEM_SEQUENCE_ID,'
277 ||' ATP_FLAG,'
278 ||' STATUS_TYPE,'
279 ||' USE_UP_CODE,'
280 ||' CHANGE_NOTICE,'
281 ||' ORGANIZATION_ID,'
282 ||' USING_ASSEMBLY_ID,'
283 ||' FROM_UNIT_NUMBER,'
284 ||' TO_UNIT_NUMBER,'
285 ||' DRIVING_ITEM_ID,'
286 ||' DELETED_FLAG,'
287 ||' ROUNDING_DIRECTION,'
288 ||' SCALING_TYPE,'
289 ||' OLD_COMPONENT_SEQUENCE_ID,'
290 ||' REFRESH_ID,'
291 ||' SR_INSTANCE_ID)'
292 ||' select '
293 ||' x.COMPONENT_SEQUENCE_ID,'
294 ||' x.INVENTORY_ITEM_ID,'
295 ||' x.BILL_SEQUENCE_ID,'
296 ||' x.OPERATION_SEQ_NUM,'
297 ||' x.USAGE_QUANTITY,'
298 ||' x.COMPONENT_YIELD_FACTOR,'
299 ||' x.EFFECTIVITY_DATE- :v_dgmt,'
300 ||' x.DISABLE_DATE- :v_dgmt,'
301 ||' x.OPERATION_OFFSET_PERCENT,'
302 ||' x.OPTIONAL_COMPONENT,'
303 ||' x.WIP_SUPPLY_TYPE,'
304 ||' x.PLANNING_FACTOR,'
305 ||' x.REVISED_ITEM_SEQUENCE_ID,'
306 ||' x.ATP_FLAG,'
307 ||' x.STATUS_TYPE,'
308 ||' x.USE_UP_CODE,'
309 ||' x.CHANGE_NOTICE,'
310 ||' x.ORGANIZATION_ID,'
311 ||' x.USING_ASSEMBLY_ID,'
312 ||' x.FROM_UNIT_NUMBER,'
313 ||' x.TO_UNIT_NUMBER,'
314 ||' x.DRIVING_ITEM_ID,'
315 ||' 2,'
316 || v_rounding_sql
317 || v_temp_sql1
318 ||' :v_refresh_id,'
319 ||' :v_instance_id'
320 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
321 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
322 || v_union_sql ;
323
324 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
325
326 EXECUTE IMMEDIATE v_sql_stmt
327 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
328 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
329 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
330 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
331 /* MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
332 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
333 */
334
335 ELSE
336
337 EXECUTE IMMEDIATE v_sql_stmt
338 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
339
340
341 END IF;
342
343 COMMIT;
344
345 --=================== Net Change Mode: Delete ==================
346 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
347
348 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOMS';
349 MSC_CL_PULL.v_view_name := 'MRP_AD_BOMS_V';
350
351 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
352 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
353 ELSE
354 v_temp_sql := NULL;
355 END IF;
356
357 v_sql_stmt:=
358 ' insert into MSC_ST_BOMS'
359 ||' ( BILL_SEQUENCE_ID,'
360 ||' DELETED_FLAG,'
361 ||' REFRESH_ID,'
362 ||' SR_INSTANCE_ID)'
363 ||' SELECT'
364 ||' x.BILL_SEQUENCE_ID, '
365 ||' 1,'
366 ||' :v_refresh_id,'
367 ||' :v_instance_id'
368 ||' FROM MRP_AD_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
369 ||' WHERE x.RN> :v_lrn '
370 || v_temp_sql;
371
372 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
373
374 COMMIT;
375
376 END IF;
377
378
379 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOMS';
380 MSC_CL_PULL.v_view_name := 'MRP_AP_BOMS_V';
381
382 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
383
384 v_union_sql :=
385 ' AND ( x.RN1 > :v_lrn )'
386 ||' UNION '
387 ||' SELECT'
388 ||' x.BILL_SEQUENCE_ID, '
389 ||' x.ORGANIZATION_ID, '
390 ||' x.ASSEMBLY_ITEM_ID, '
391 ||' x.ASSEMBLY_TYPE, '
392 ||' x.ALTERNATE_BOM_DESIGNATOR, '
393 ||' x.SPECIFIC_ASSEMBLY_COMMENT, '
394 ||' x.PENDING_FROM_ECN, '
395 ||' x.COMMON_BILL_SEQUENCE_ID, '
396 ||' 2,'
397 ||' :v_refresh_id,'
398 ||' :v_instance_id'
399 ||' FROM MRP_AP_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
400 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
401 ||' AND (x.RN2 > :v_lrn)';
402
403 ELSE
404 v_union_sql := ' ';
405
406 END IF;
407
408
409 v_sql_stmt:=
410 ' insert into MSC_ST_BOMS'
411 ||' ( BILL_SEQUENCE_ID,'
412 ||' ORGANIZATION_ID,'
413 ||' ASSEMBLY_ITEM_ID,'
414 ||' ASSEMBLY_TYPE,'
415 ||' ALTERNATE_BOM_DESIGNATOR,'
416 ||' SPECIFIC_ASSEMBLY_COMMENT,'
417 ||' PENDING_FROM_ECN,'
418 ||' COMMON_BILL_SEQUENCE_ID,'
419 ||' DELETED_FLAG,'
420 ||' REFRESH_ID,'
421 ||' SR_INSTANCE_ID)'
422 ||' SELECT'
423 ||' x.BILL_SEQUENCE_ID, '
424 ||' x.ORGANIZATION_ID, '
425 ||' x.ASSEMBLY_ITEM_ID, '
426 ||' x.ASSEMBLY_TYPE, '
427 ||' x.ALTERNATE_BOM_DESIGNATOR, '
428 ||' x.SPECIFIC_ASSEMBLY_COMMENT, '
429 ||' x.PENDING_FROM_ECN, '
430 ||' x.COMMON_BILL_SEQUENCE_ID, '
431 ||' 2,'
432 ||' :v_refresh_id,'
433 ||' :v_instance_id'
434 ||' FROM MRP_AP_BOMS_V'||MSC_CL_PULL.v_dblink||' x'
435 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
436 || v_union_sql;
437
438 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
439
440 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
441 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
442 ELSE
443
444 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
445
446 END IF;
447
448
449 COMMIT;
450
451 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
452 --=================== Net Change Mode: Delete ==================
453 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
454
455 MSC_CL_PULL.v_table_name:= 'MSC_ST_COMPONENT_SUBSTITUTES';
456 MSC_CL_PULL.v_view_name := 'MRP_AD_SUB_COMPS_V';
457
458 v_sql_stmt:=
459 ' insert into MSC_ST_COMPONENT_SUBSTITUTES'
460 ||' ( BILL_SEQUENCE_ID,'
461 ||' COMPONENT_SEQUENCE_ID,'
462 ||' SUBSTITUTE_ITEM_ID,'
463 ||' DELETED_FLAG,'
464 ||' REFRESH_ID,'
465 ||' SR_INSTANCE_ID)'
466 ||' SELECT'
467 ||' x.BILL_SEQUENCE_ID,'
468 ||' x.COMPONENT_SEQUENCE_ID,'
469 ||' x.SUBSTITUTE_ITEM_ID,'
470 ||' 1,'
471 ||' :v_refresh_id,'
472 ||' :v_instance_id'
473 ||' FROM MRP_AD_SUB_COMPS_V'||MSC_CL_PULL.v_dblink||' x'
474 ||' WHERE x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str
475 ||' AND x.RN > :v_lrn';
476
477 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
478
479 COMMIT;
480
481 END IF;
482
483 END IF;
484
485
486 MSC_CL_PULL.v_table_name:= 'MSC_ST_COMPONENT_SUBSTITUTES';
487 MSC_CL_PULL.v_view_name := 'MRP_AP_COMPONENT_SUBSTITUTES_V';
488
489
490
491 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
492 v_rounding_sql := 'decode(x.rounding_direction,0,3,1,2,2,1,3),';
493 v_rounding_sql :='3,';
494 END IF ;
495
496
497
498 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
499
500 v_union_sql :=
501 ' AND ( x.RN1 > :v_lrn )'
502 ||' UNION '
503 ||' SELECT'
504 ||' x.BILL_SEQUENCE_ID,'
505 ||' x.COMPONENT_SEQUENCE_ID,'
506 ||' x.SUBSTITUTE_ITEM_ID,'
507 ||' x.USAGE_QUANTITY,'
508 ||' x.ORGANIZATION_ID,'
509 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
510 ||' 1, x.Attribute1,'
511 ||' 2, x.Attribute2,'
512 ||' 3, x.Attribute3,'
513 ||' 4, x.Attribute4,'
514 ||' 5, x.Attribute5,'
515 ||' 6, x.Attribute6,'
516 ||' 7, x.Attribute7,'
517 ||' 8, x.Attribute8,'
518 ||' 9, x.Attribute9,'
519 ||' 10, x.Attribute10,'
520 ||' 11, x.Attribute11,'
521 ||' 12, x.Attribute12,'
522 ||' 13, x.Attribute13,'
523 ||' 14, x.Attribute14,'
524 ||' 15, x.Attribute15)),2),'
525 ||' 2,'
526 || v_rounding_sql
527 ||' :v_refresh_id,'
528 ||' :v_instance_id'
529 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
530 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
531 ||' AND (x.RN2 > :v_lrn)'
532 ||' UNION '
533 ||' SELECT'
534 ||' x.BILL_SEQUENCE_ID,'
535 ||' x.COMPONENT_SEQUENCE_ID,'
536 ||' x.SUBSTITUTE_ITEM_ID,'
537 ||' x.USAGE_QUANTITY,'
538 ||' x.ORGANIZATION_ID,'
539 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
540 ||' 1, x.Attribute1,'
541 ||' 2, x.Attribute2,'
542 ||' 3, x.Attribute3,'
543 ||' 4, x.Attribute4,'
544 ||' 5, x.Attribute5,'
545 ||' 6, x.Attribute6,'
546 ||' 7, x.Attribute7,'
547 ||' 8, x.Attribute8,'
548 ||' 9, x.Attribute9,'
549 ||' 10, x.Attribute10,'
550 ||' 11, x.Attribute11,'
551 ||' 12, x.Attribute12,'
552 ||' 13, x.Attribute13,'
553 ||' 14, x.Attribute14,'
554 ||' 15, x.Attribute15)),2),'
555 ||' 2,'
556 || v_rounding_sql
557 ||' :v_refresh_id,'
558 ||' :v_instance_id'
559 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
560 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
561 ||' AND (x.RN3 > :v_lrn)'
562 ||' UNION '
563 ||' SELECT'
564 ||' x.BILL_SEQUENCE_ID,'
565 ||' x.COMPONENT_SEQUENCE_ID,'
566 ||' x.SUBSTITUTE_ITEM_ID,'
567 ||' x.USAGE_QUANTITY,'
568 ||' x.ORGANIZATION_ID,'
569 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
570 ||' 1, x.Attribute1,'
571 ||' 2, x.Attribute2,'
572 ||' 3, x.Attribute3,'
573 ||' 4, x.Attribute4,'
574 ||' 5, x.Attribute5,'
575 ||' 6, x.Attribute6,'
576 ||' 7, x.Attribute7,'
577 ||' 8, x.Attribute8,'
578 ||' 9, x.Attribute9,'
579 ||' 10, x.Attribute10,'
580 ||' 11, x.Attribute11,'
581 ||' 12, x.Attribute12,'
582 ||' 13, x.Attribute13,'
583 ||' 14, x.Attribute14,'
584 ||' 15, x.Attribute15)),2),'
585 ||' 2,'
586 || v_rounding_sql
587 ||' :v_refresh_id,'
588 ||' :v_instance_id'
589 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
590 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
591 ||' AND (x.RN4 > :v_lrn)';
592
593 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
594
595 v_union_sql :=
596 v_union_sql||' UNION '
597 ||' SELECT'
598 ||' x.BILL_SEQUENCE_ID,'
599 ||' x.COMPONENT_SEQUENCE_ID,'
600 ||' x.SUBSTITUTE_ITEM_ID,'
601 ||' x.USAGE_QUANTITY,'
602 ||' x.ORGANIZATION_ID,'
603 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
604 ||' 1, x.Attribute1,'
605 ||' 2, x.Attribute2,'
606 ||' 3, x.Attribute3,'
607 ||' 4, x.Attribute4,'
608 ||' 5, x.Attribute5,'
609 ||' 6, x.Attribute6,'
610 ||' 7, x.Attribute7,'
611 ||' 8, x.Attribute8,'
612 ||' 9, x.Attribute9,'
613 ||' 10, x.Attribute10,'
614 ||' 11, x.Attribute11,'
615 ||' 12, x.Attribute12,'
616 ||' 13, x.Attribute13,'
617 ||' 14, x.Attribute14,'
618 ||' 15, x.Attribute15)),2),'
619 ||' 2,'
620 || v_rounding_sql
621 ||' :v_refresh_id,'
622 ||' :v_instance_id'
623 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
624 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
625 ||' AND (x.RN5 > :v_lrn)';
626
627
628 END IF;
629
630 ELSE
631 v_union_sql := ' ';
632
633 END IF;
634
635 v_sql_stmt:=
636 ' INSERT INTO MSC_ST_COMPONENT_SUBSTITUTES'
637 ||' ( BILL_SEQUENCE_ID,'
638 ||' COMPONENT_SEQUENCE_ID,'
639 ||' SUBSTITUTE_ITEM_ID,'
640 ||' USAGE_QUANTITY,'
641 ||' ORGANIZATION_ID,'
642 ||' PRIORITY,'
643 ||' DELETED_FLAG,'
644 ||' ROUNDING_DIRECTION,'
645 ||' REFRESH_ID,'
646 ||' SR_INSTANCE_ID)'
647 ||' SELECT'
648 ||' x.BILL_SEQUENCE_ID,'
649 ||' x.COMPONENT_SEQUENCE_ID,'
650 ||' x.SUBSTITUTE_ITEM_ID,'
651 ||' x.USAGE_QUANTITY,'
652 ||' x.ORGANIZATION_ID,'
653 ||' NVL( TO_NUMBER(DECODE( :v_msc_bom_subst_priority,'
654 ||' 1, x.Attribute1,'
655 ||' 2, x.Attribute2,'
656 ||' 3, x.Attribute3,'
657 ||' 4, x.Attribute4,'
658 ||' 5, x.Attribute5,'
659 ||' 6, x.Attribute6,'
660 ||' 7, x.Attribute7,'
661 ||' 8, x.Attribute8,'
662 ||' 9, x.Attribute9,'
663 ||' 10, x.Attribute10,'
664 ||' 11, x.Attribute11,'
665 ||' 12, x.Attribute12,'
666 ||' 13, x.Attribute13,'
667 ||' 14, x.Attribute14,'
668 ||' 15, x.Attribute15)),2),'
669 ||' 2,'
670 || v_rounding_sql
671 ||' :v_refresh_id,'
672 ||' :v_instance_id'
673 ||' FROM MRP_AP_COMPONENT_SUBSTITUTES_V'||MSC_CL_PULL.v_dblink||' x'
674 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
675 || v_union_sql;
676
677
678 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
679
680 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
681
682 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
683 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
684 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
685 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
686 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
687 ELSE
688
689 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
690 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
691 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
692 MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
693 END IF;
694
695 ELSE
696
697 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_bom_subst_priority,MSC_CL_PULL.v_refresh_id,MSC_CL_PULL.v_instance_id;
698
699 END IF;
700
701
702 COMMIT;
703
704
705 if MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN /*osfm change*/
706 select instance_code into lv_icode from msc_apps_instances where instance_id = MSC_CL_PULL.v_instance_id;
707 BEGIN
708
709 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
710 application_short_name => 'MSC',
711 statement_type => AD_DDL.CREATE_INDEX,
712 statement =>
713 'create index MSC_ST_BOM_COMPONENTS_'||lv_icode
714 ||' ON MSC_ST_BOM_COMPONENTS (BILL_SEQUENCE_ID,SR_INSTANCE_ID) '
715 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
716 object_name => 'MSC_ST_BOM_COMPONENTS_'||lv_icode);
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
721
722 END;
723 msc_analyse_tables_pk.analyse_table( 'MSC_ST_BOM_COMPONENTS');
724
725 LOAD_CO_PRODUCT_BOMS;
726
727 BEGIN
728 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
729 application_short_name => 'MSC',
730 statement_type => AD_DDL.DROP_INDEX,
731 statement =>
732 'drop index MSC_ST_BOM_COMPONENTS_'||lv_icode,
733 object_name => 'MSC_ST_BOM_COMPONENTS_'||lv_icode);
734 EXCEPTION
735 WHEN OTHERS THEN
736 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
737
738 END;
739
740 End If;
741
742
743 END IF; -- MSC_CL_PULL.BOM_ENABLED
744
745 END LOAD_BOM;
746
747
748 PROCEDURE LOAD_PROCESS_EFFECTIVITY IS
749 v_get_apps_ver number;
750 BEGIN
751
752 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
753 --=================== Net Change Mode: Delete ==================
754 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
755
756 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROCESS_EFFECTIVITY';
757 MSC_CL_PULL.v_view_name := 'MRP_AD_PROCESS_EFFECTIVITY_V';
758
759 v_sql_stmt:=
760 ' INSERT INTO MSC_ST_PROCESS_EFFECTIVITY'
761 ||' ( ITEM_ID,'
762 ||' ORGANIZATION_ID,'
763 ||' BILL_SEQUENCE_ID,'
764 ||' ROUTING_SEQUENCE_ID,'
765 ||' LINE_ID,'
766 ||' DELETED_FLAG,'
767 ||' REFRESH_ID,'
768 ||' SR_INSTANCE_ID)'
769 ||' SELECT'
770 ||' x.INVENTORY_ITEM_ID,'
771 ||' x.ORGANIZATION_ID,'
772 ||' x.BILL_SEQUENCE_ID,'
773 ||' x.ROUTING_SEQUENCE_ID,'
774 ||' x.LINE_ID,'
775 ||' 1,'
776 ||' :v_refresh_id,'
777 ||' :v_instance_id'
778 ||' FROM MRP_AD_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
779 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
780 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
781
782 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
783
784 COMMIT;
785
786 END IF;
787
788 MSC_CL_PULL.v_table_name:= 'MSC_ST_PROCESS_EFFECTIVITY';
789 MSC_CL_PULL.v_view_name := 'MRP_AP_PROCESS_EFFECTIVITY_V';
790 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
791 v_union_sql :=
792 ' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
793 /*
794 ||' UNION '
795 ||' SELECT'
796 ||' x.INVENTORY_ITEM_ID,'
797 ||' x.ORGANIZATION_ID,'
798 ||' x.BILL_SEQUENCE_ID,'
799 ||' x.ROUTING_SEQUENCE_ID,'
800 ||' x.EFFECTIVITY_DATE,'
801 ||' x.LINE_ID,'
802 ||' x.PREFERENCE,'
803 ||' x.PRIMARY_LINE_FLAG,'
804 ||' x.PRODUCTION_LINE_RATE,'
805 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
806 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
807 ||' 1, x.Attribute1,'
808 ||' 2, x.Attribute2,'
809 ||' 3, x.Attribute3,'
810 ||' 4, x.Attribute4,'
811 ||' 5, x.Attribute5,'
812 ||' 6, x.Attribute6,'
813 ||' 7, x.Attribute7,'
814 ||' 8, x.Attribute8,'
815 ||' 9, x.Attribute9,'
816 ||' 10, x.Attribute10,'
817 ||' 11, x.Attribute11,'
818 ||' 12, x.Attribute12,'
819 ||' 13, x.Attribute13,'
820 ||' 14, x.Attribute14,'
821 ||' 15, x.Attribute15)),0),'
822 ||' 2,'
823 ||' :v_refresh_id,'
824 ||' :v_instance_id'
825 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
826 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
827 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn||')'
828 */
829
830 ||' UNION '
831 ||' SELECT'
832 ||' x.INVENTORY_ITEM_ID,'
833 ||' x.ORGANIZATION_ID,'
834 ||' x.BILL_SEQUENCE_ID,'
835 ||' x.ROUTING_SEQUENCE_ID,'
836 ||' x.EFFECTIVITY_DATE,'
837 ||' x.LINE_ID,'
838 ||' x.PREFERENCE,'
839 ||' x.PRIMARY_LINE_FLAG,'
840 ||' x.PRODUCTION_LINE_RATE,'
841 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
842 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
843 ||' 1, x.Attribute1,'
844 ||' 2, x.Attribute2,'
845 ||' 3, x.Attribute3,'
846 ||' 4, x.Attribute4,'
847 ||' 5, x.Attribute5,'
848 ||' 6, x.Attribute6,'
849 ||' 7, x.Attribute7,'
850 ||' 8, x.Attribute8,'
851 ||' 9, x.Attribute9,'
852 ||' 10, x.Attribute10,'
853 ||' 11, x.Attribute11,'
854 ||' 12, x.Attribute12,'
855 ||' 13, x.Attribute13,'
856 ||' 14, x.Attribute14,'
857 ||' 15, x.Attribute15)),0),'
858 ||' 2,'
859 ||' :v_refresh_id,'
860 ||' :v_instance_id'
861 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
862 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
863 ||' AND ( x.RN3>'||MSC_CL_PULL.v_lrn||')'
864 ||' UNION '
865 ||' SELECT'
866 ||' x.INVENTORY_ITEM_ID,'
867 ||' x.ORGANIZATION_ID,'
868 ||' x.BILL_SEQUENCE_ID,'
869 ||' x.ROUTING_SEQUENCE_ID,'
870 ||' x.EFFECTIVITY_DATE,'
871 ||' x.LINE_ID,'
872 ||' x.PREFERENCE,'
873 ||' x.PRIMARY_LINE_FLAG,'
874 ||' x.PRODUCTION_LINE_RATE,'
875 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
876 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
877 ||' 1, x.Attribute1,'
878 ||' 2, x.Attribute2,'
879 ||' 3, x.Attribute3,'
880 ||' 4, x.Attribute4,'
881 ||' 5, x.Attribute5,'
882 ||' 6, x.Attribute6,'
883 ||' 7, x.Attribute7,'
884 ||' 8, x.Attribute8,'
885 ||' 9, x.Attribute9,'
886 ||' 10, x.Attribute10,'
887 ||' 11, x.Attribute11,'
888 ||' 12, x.Attribute12,'
889 ||' 13, x.Attribute13,'
890 ||' 14, x.Attribute14,'
891 ||' 15, x.Attribute15)),0),'
892 ||' 2,'
893 ||' :v_refresh_id,'
894 ||' :v_instance_id'
895 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
896 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
897 ||' AND ( x.RN4>'||MSC_CL_PULL.v_lrn||')';
898 /*
899 ||' UNION '
900 ||' SELECT'
901 ||' x.INVENTORY_ITEM_ID,'
902 ||' x.ORGANIZATION_ID,'
903 ||' x.BILL_SEQUENCE_ID,'
904 ||' x.ROUTING_SEQUENCE_ID,'
905 ||' x.EFFECTIVITY_DATE,'
906 ||' x.LINE_ID,'
907 ||' x.PREFERENCE,'
908 ||' x.PRIMARY_LINE_FLAG,'
909 ||' x.PRODUCTION_LINE_RATE,'
910 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
911 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
912 ||' 1, x.Attribute1,'
913 ||' 2, x.Attribute2,'
914 ||' 3, x.Attribute3,'
915 ||' 4, x.Attribute4,'
916 ||' 5, x.Attribute5,'
917 ||' 6, x.Attribute6,'
918 ||' 7, x.Attribute7,'
919 ||' 8, x.Attribute8,'
920 ||' 9, x.Attribute9,'
921 ||' 10, x.Attribute10,'
922 ||' 11, x.Attribute11,'
923 ||' 12, x.Attribute12,'
924 ||' 13, x.Attribute13,'
925 ||' 14, x.Attribute14,'
926 ||' 15, x.Attribute15)),0),'
927 ||' 2,'
928 ||' :v_refresh_id,'
929 ||' :v_instance_id'
930 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
931 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
932 ||' AND ( x.RN5>'||MSC_CL_PULL.v_lrn||')' ;
933 */
934 ELSE
935 v_union_sql :=
936 ' AND (x.RN1>'||MSC_CL_PULL.v_lrn
937 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
938 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
939 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn||')';
940
941 END IF;
942
943 v_sql_stmt:=
944 ' INSERT INTO MSC_ST_PROCESS_EFFECTIVITY'
945 ||' ( ITEM_ID,'
946 ||' ORGANIZATION_ID,'
947 ||' BILL_SEQUENCE_ID,'
948 ||' ROUTING_SEQUENCE_ID,'
949 ||' EFFECTIVITY_DATE,'
950 ||' LINE_ID,'
951 ||' PREFERENCE,'
952 ||' PRIMARY_LINE_FLAG,'
953 ||' PRODUCTION_LINE_RATE,'
954 ||' LOAD_DISTRIBUTION_PRIORITY,'
955 ||' ITEM_PROCESS_COST,'
956 ||' DELETED_FLAG,'
957 ||' REFRESH_ID,'
958 ||' SR_INSTANCE_ID)'
959 ||' SELECT'
960 ||' x.INVENTORY_ITEM_ID,'
961 ||' x.ORGANIZATION_ID,'
962 ||' x.BILL_SEQUENCE_ID,'
963 ||' x.ROUTING_SEQUENCE_ID,'
964 ||' x.EFFECTIVITY_DATE,'
965 ||' x.LINE_ID,'
966 ||' x.PREFERENCE,'
967 ||' x.PRIMARY_LINE_FLAG,'
968 ||' x.PRODUCTION_LINE_RATE,'
969 ||' x.LOAD_DISTRIBUTION_PRIORITY,'
970 ||' NVL( TO_NUMBER(DECODE( :v_msc_alt_bom_cost,'
971 ||' 1, x.Attribute1,'
972 ||' 2, x.Attribute2,'
973 ||' 3, x.Attribute3,'
974 ||' 4, x.Attribute4,'
975 ||' 5, x.Attribute5,'
976 ||' 6, x.Attribute6,'
977 ||' 7, x.Attribute7,'
978 ||' 8, x.Attribute8,'
979 ||' 9, x.Attribute9,'
980 ||' 10, x.Attribute10,'
981 ||' 11, x.Attribute11,'
982 ||' 12, x.Attribute12,'
983 ||' 13, x.Attribute13,'
984 ||' 14, x.Attribute14,'
985 ||' 15, x.Attribute15)),0),'
986 ||' 2,'
987 ||' :v_refresh_id,'
988 ||' :v_instance_id'
989 ||' FROM MRP_AP_PROCESS_EFFECTIVITY_V'||MSC_CL_PULL.v_dblink||' x'
990 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
991 || v_union_sql ;
992
993 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
994
995 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_alt_bom_cost,
996 MSC_CL_PULL.v_refresh_id,
997 MSC_CL_PULL.v_instance_id,
998 MSC_CL_PULL.v_msc_alt_bom_cost,
999 MSC_CL_PULL.v_refresh_id,
1000 MSC_CL_PULL.v_instance_id,
1001 MSC_CL_PULL.v_msc_alt_bom_cost,
1002 MSC_CL_PULL.v_refresh_id,
1003 MSC_CL_PULL.v_instance_id;
1004
1005 /*
1006 MSC_CL_PULL.v_msc_alt_bom_cost,
1007 MSC_CL_PULL.v_refresh_id,
1008 MSC_CL_PULL.v_instance_id,
1009 MSC_CL_PULL.v_msc_alt_bom_cost,
1010 MSC_CL_PULL.v_refresh_id,
1011 MSC_CL_PULL.v_instance_id;
1012 */
1013
1014 ELSE
1015 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_alt_bom_cost,
1016 MSC_CL_PULL.v_refresh_id,
1017 MSC_CL_PULL.v_instance_id;
1018 END IF;
1019 COMMIT;
1020
1021 END IF; -- MSC_CL_PULL.BOM_ENABLED
1022
1023 END LOAD_PROCESS_EFFECTIVITY;
1024
1025
1026 -- ===============================================================
1027
1028 PROCEDURE LOAD_BOR IS
1029 BEGIN
1030
1031 MSC_CL_PULL.v_table_name:= 'MSC_ST_BILL_OF_RESOURCES';
1032 MSC_CL_PULL.v_view_name := 'MRP_AP_BILL_OF_RESOURCES_V';
1033
1034 v_sql_stmt:=
1035 ' insert into MSC_ST_BILL_OF_RESOURCES'
1036 ||' ( BILL_OF_RESOURCES,'
1037 ||' ORGANIZATION_ID,'
1038 ||' DESCRIPTION,'
1039 ||' DISABLE_DATE,'
1040 ||' ROLLUP_START_DATE,'
1041 ||' ROLLUP_COMPLETION_DATE,'
1042 ||' DELETED_FLAG,'
1043 ||' REFRESH_ID,'
1044 ||' SR_INSTANCE_ID)'
1045 ||' select'
1046 ||' x.BILL_OF_RESOURCES,'
1047 ||' x.ORGANIZATION_ID,'
1048 ||' x.DESCRIPTION,'
1049 ||' x.DISABLE_DATE- :v_dgmt,'
1050 ||' x.ROLLUP_START_DATE- :v_dgmt,'
1051 ||' x.ROLLUP_COMPLETION_DATE- :v_dgmt,'
1052 ||' 2,'
1053 ||' :v_refresh_id,'
1054 ||' :v_instance_id'
1055 ||' from MRP_AP_BILL_OF_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1056 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1057 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
1058 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn||')';
1059
1060 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1061
1062 COMMIT;
1063
1064 MSC_CL_PULL.v_table_name:= 'MSC_ST_BOR_REQUIREMENTS';
1065 MSC_CL_PULL.v_view_name := 'MRP_AP_CRP_RESOURCE_HOURS_V';
1066
1067 v_sql_stmt:=
1068 ' insert into MSC_ST_BOR_REQUIREMENTS'
1069 ||' ( BILL_OF_RESOURCES,'
1070 ||' ORGANIZATION_ID,'
1071 ||' ASSEMBLY_ITEM_ID,'
1072 ||' SOURCE_ITEM_ID,'
1073 ||' RESOURCE_ID,'
1074 ||' RESOURCE_DEPARTMENT_HOURS,'
1075 ||' OPERATION_SEQUENCE_ID,'
1076 ||' OPERATION_SEQ_NUM,'
1077 ||' RESOURCE_SEQ_NUM,'
1078 ||' SETBACK_DAYS,'
1079 ||' DEPARTMENT_ID,'
1080 ||' ASSEMBLY_USAGE,'
1081 ||' ORIGINATION_TYPE,'
1082 ||' RESOURCE_UNITS,'
1083 ||' BASIS,'
1084 ||' DELETED_FLAG,'
1085 ||' REFRESH_ID,'
1086 ||' SR_INSTANCE_ID)'
1087 ||' select'
1088 ||' x.BILL_OF_RESOURCES,'
1089 ||' x.ORGANIZATION_ID,'
1090 ||' x.ASSEMBLY_ITEM_ID,'
1091 ||' x.SOURCE_ITEM_ID,'
1092 ||' x.RESOURCE_ID,'
1093 ||' x.RESOURCE_DEPARTMENT_HOURS,'
1094 ||' x.OPERATION_SEQUENCE_ID,'
1095 ||' x.OPERATION_SEQ_NUM,'
1096 ||' x.RESOURCE_SEQ_NUM,'
1097 ||' x.SETBACK_DAYS,'
1098 ||' NVL(x.DEPARTMENT_ID, x.LINE_ID),'
1099 ||' NVL(x.ASSEMBLY_USAGE,1),'
1100 ||' x.ORIGINATION_TYPE,'
1101 ||' NVL(x.RESOURCE_UNITS,1),'
1102 ||' x.BASIS,'
1103 ||' 2,'
1104 ||' :v_refresh_id,'
1105 ||' :v_instance_id'
1106 ||' from MRP_AP_CRP_RESOURCE_HOURS_V'||MSC_CL_PULL.v_dblink||' x'
1107 ||' WHERE DECODE( :WIP_ENABLED, 2, LINE_ID) IS NULL'
1108 ||' AND x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1109 ||' AND ( x.RN1>'||MSC_CL_PULL.v_lrn
1110 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
1111 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn||')';
1112
1113 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.WIP_ENABLED;
1114
1115 COMMIT;
1116
1117 END LOAD_BOR;
1118
1119
1120 --==================================================================
1121
1122 PROCEDURE LOAD_RESOURCE IS
1123
1124 -- for the fix 2490553 (WPS Integration)
1125 lv_inflate_wip NUMBER;
1126 v_res_hrs_left VARCHAR2(400);
1127 v_res_hrs_sql VARCHAR2(300);
1128 v_temp_parent_seq VARCHAR2(500);
1129 v_temp_atp_rule_sql VARCHAR2(400);
1130 v_temp_atp_rule_sql1 VARCHAR2(400);
1131 lv_lbj_details NUMBER :=0;
1132 lv_cond_sql VARCHAR2(100) := null;
1133 lv_op_seq_num VARCHAR2(100) := null;
1134 lv_hint VARCHAR2(300);
1135 v_touch_time VARCHAR2(300);
1136 lv_qty_sql_temp VARCHAR2(300);
1137 v_temp_sql1 VARCHAR2(300);
1138
1139 BEGIN
1140
1141 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPARTMENT_RESOURCES';
1142 MSC_CL_PULL.v_view_name := 'MRP_AP_DEPARTMENT_RESOURCES_V';
1143 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1144 v_temp_atp_rule_sql := 'x.ATP_RULE_ID,x.SCHEDULE_TO_INSTANCE,x.BATCHING_PENALTY,x.SETUP_TIME_PERCENT,x.UTILIZATION_CHANGE_PERCENT,x.SETUP_TIME_TYPE,x.UTILIZATION_CHANGE_TYPE,x.IDLE_TIME_TOLERANCE,x.SDS_SCHEDULING_WINDOW,';
1145 ELSE
1146 v_temp_atp_rule_sql := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1147 END IF;
1148 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1149 v_temp_atp_rule_sql1 := 'x.ATP_RULE_ID,x.SETUP_TIME_PERCENT,x.UTILIZATION_CHANGE_PERCENT,x.SETUP_TIME_TYPE,x.UTILIZATION_CHANGE_TYPE,';
1150 ELSE
1151 v_temp_atp_rule_sql1 := 'NULL,NULL,NULL,NULL,NULL,';
1152 END IF;
1153
1154
1155 /* The code is forked since the Resource Batching flexfields are replaced by the columns
1156 in Patchset - G for 11i source only
1157 */
1158
1159 IF (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
1160 v_sql_stmt:=
1161 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1162 ||' ( RESOURCE_ID,'
1163 ||' DEPARTMENT_ID,'
1164 ||' LINE_FLAG,'
1165 ||' OWNING_DEPARTMENT_ID,'
1166 ||' CAPACITY_UNITS,'
1167 ||' RESOURCE_GROUP_NAME,'
1168 ||' ORGANIZATION_ID,'
1169 ||' DEPARTMENT_CODE,'
1170 ||' DEPARTMENT_CLASS,'
1171 ||' DEPARTMENT_DESCRIPTION,'
1172 ||' RESOURCE_CODE,'
1173 ||' RESOURCE_DESCRIPTION,'
1174 ||' OVER_UTILIZED_PERCENT,'
1175 ||' UNDER_UTILIZED_PERCENT,'
1176 ||' RESOURCE_SHORTAGE_TYPE,'
1177 ||' RESOURCE_EXCESS_TYPE,'
1178 ||' PLANNING_EXCEPTION_SET,'
1179 ||' USER_TIME_FENCE,'
1180 ||' AGGREGATED_RESOURCE_FLAG,'
1181 ||' AGGREGATED_RESOURCE_ID,'
1182 ||' RESOURCE_TYPE,'
1183 ||' DISABLE_DATE,'
1184 ||' AVAILABLE_24_HOURS_FLAG,'
1185 ||' CTP_FLAG,'
1186 ||' UTILIZATION,'
1187 ||' EFFICIENCY,'
1188 ||' BATCHABLE_FLAG,'
1189 ||' BATCHING_WINDOW,'
1190 ||' MIN_CAPACITY,'
1191 ||' MAX_CAPACITY,'
1192 ||' UNIT_OF_MEASURE,'
1193 ||' RESOURCE_COST,'
1194 ||' RESOURCE_OVER_UTIL_COST,'
1195 ||' DEPT_OVERHEAD_COST,'
1196 ||' DELETED_FLAG,'
1197 ||' REFRESH_ID,'
1198 ||' ATP_RULE_ID,'
1199 ||' SCHEDULE_TO_INSTANCE,' /* ds change */
1200 ||' BATCHING_PENALTY,'
1201 ||' SETUP_TIME_PERCENT,'
1202 ||' UTILIZATION_CHANGE_PERCENT,'
1203 ||' SETUP_TIME_TYPE,'
1204 ||' UTILIZATION_CHANGE_TYPE,'
1205 ||' IDLE_TIME_TOLERANCE,'
1206 ||' SDS_SCHEDULING_WINDOW,' /* ds change */
1207 ||' SR_INSTANCE_ID)'
1208 ||' select'
1209 ||' x.RESOURCE_ID,'
1210 ||' x.DEPARTMENT_ID,'
1211 ||' x.LINE_FLAG,'
1212 ||' x.OWNING_DEPARTMENT_ID,'
1213 ||' x.CAPACITY_UNITS,'
1214 ||' x.RESOURCE_GROUP_NAME,'
1215 ||' x.ORGANIZATION_ID,'
1216 ||' x.DEPARTMENT_CODE,'
1217 ||' x.DEPARTMENT_CLASS,'
1218 ||' x.DEPARTMENT_DESCRIPTION,'
1219 ||' x.RESOURCE_CODE,'
1220 ||' x.RESOURCE_DESCRIPTION,'
1221 ||' x.OVER_UTILIZED_PERCENT,'
1222 ||' x.UNDER_UTILIZED_PERCENT,'
1223 ||' x.RESOURCE_SHORTAGE_TYPE,'
1224 ||' x.RESOURCE_EXCESS_TYPE,'
1225 ||' x.PLANNING_EXCEPTION_SET,'
1226 ||' x.USER_TIME_FENCE,'
1227 ||' DECODE( TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1228 ||' 1, x.Attribute1,'
1229 ||' 2, x.Attribute2,'
1230 ||' 3, x.Attribute3,'
1231 ||' 4, x.Attribute4,'
1232 ||' 5, x.Attribute5,'
1233 ||' 6, x.Attribute6,'
1234 ||' 7, x.Attribute7,'
1235 ||' 8, x.Attribute8,'
1236 ||' 9, x.Attribute9,'
1237 ||' 10, x.Attribute10,'
1238 ||' 11, x.Attribute11,'
1239 ||' 12, x.Attribute12,'
1240 ||' 13, x.Attribute13,'
1241 ||' 14, x.Attribute14,'
1242 ||' 15, x.Attribute15)*2),'
1243 ||' x.RESOURCE_ID, 1,'
1244 ||' 2),'
1245 ||' TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1246 ||' 1, x.Attribute1,'
1247 ||' 2, x.Attribute2,'
1248 ||' 3, x.Attribute3,'
1249 ||' 4, x.Attribute4,'
1250 ||' 5, x.Attribute5,'
1251 ||' 6, x.Attribute6,'
1252 ||' 7, x.Attribute7,'
1253 ||' 8, x.Attribute8,'
1254 ||' 9, x.Attribute9,'
1255 ||' 10, x.Attribute10,'
1256 ||' 11, x.Attribute11,'
1257 ||' 12, x.Attribute12,'
1258 ||' 13, x.Attribute13,'
1259 ||' 14, x.Attribute14,'
1260 ||' 15, x.Attribute15)*2),'
1261 ||' x.RESOURCE_TYPE,'
1262 ||' x.DISABLE_DATE- :v_dgmt,'
1263 ||' x.AVAILABLE_24_HOURS_FLAG,'
1264 ||' x.CTP_FLAG,'
1265 ||' x.UTILIZATION,'
1266 ||' x.EFFICIENCY,'
1267 ||' x.BATCHABLE,'
1268 ||' x.BATCH_WINDOW,'
1269 ||' x.MIN_BATCH_CAPACITY,'
1270 ||' x.MAX_BATCH_CAPACITY,'
1271 ||' x.BATCH_CAPACITY_UOM,'
1272 ||' x.RESOURCE_COST,'
1273 ||' TO_NUMBER(DECODE( :v_mso_res_penalty,'
1274 ||' 1, x.Attribute1,'
1275 ||' 2, x.Attribute2,'
1276 ||' 3, x.Attribute3,'
1277 ||' 4, x.Attribute4,'
1278 ||' 5, x.Attribute5,'
1279 ||' 6, x.Attribute6,'
1280 ||' 7, x.Attribute7,'
1281 ||' 8, x.Attribute8,'
1282 ||' 9, x.Attribute9,'
1283 ||' 10, x.Attribute10,'
1284 ||' 11, x.Attribute11,'
1285 ||' 12, x.Attribute12,'
1286 ||' 13, x.Attribute13,'
1287 ||' 14, x.Attribute14,'
1288 ||' 15, x.Attribute15)),'
1289 ||' x.DEPT_OVERHEAD_COST,'
1290 ||' 2,'
1291 ||' :v_refresh_id,'
1292 || v_temp_atp_rule_sql
1293 ||' :v_instance_id'
1294 ||' from MRP_AP_DEPARTMENT_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1295 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1296
1297 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_aggreg_res_name,
1298 MSC_CL_PULL.v_msc_aggreg_res_name,
1299 MSC_CL_PULL.v_dgmt,
1300 MSC_CL_PULL.v_mso_res_penalty,
1301 MSC_CL_PULL.v_refresh_id,
1302 MSC_CL_PULL.v_instance_id;
1303 ELSE
1304 v_sql_stmt:=
1305 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1306 ||' ( RESOURCE_ID,'
1307 ||' DEPARTMENT_ID,'
1308 ||' LINE_FLAG,'
1309 ||' OWNING_DEPARTMENT_ID,'
1310 ||' CAPACITY_UNITS,'
1311 ||' RESOURCE_GROUP_NAME,'
1312 ||' ORGANIZATION_ID,'
1313 ||' DEPARTMENT_CODE,'
1314 ||' DEPARTMENT_CLASS,'
1315 ||' DEPARTMENT_DESCRIPTION,'
1316 ||' RESOURCE_CODE,'
1317 ||' RESOURCE_DESCRIPTION,'
1318 ||' OVER_UTILIZED_PERCENT,'
1319 ||' UNDER_UTILIZED_PERCENT,'
1320 ||' RESOURCE_SHORTAGE_TYPE,'
1321 ||' RESOURCE_EXCESS_TYPE,'
1322 ||' PLANNING_EXCEPTION_SET,'
1323 ||' USER_TIME_FENCE,'
1324 ||' AGGREGATED_RESOURCE_FLAG,'
1325 ||' AGGREGATED_RESOURCE_ID,'
1326 ||' RESOURCE_TYPE,'
1327 ||' DISABLE_DATE,'
1328 ||' AVAILABLE_24_HOURS_FLAG,'
1329 ||' CTP_FLAG,'
1330 ||' UTILIZATION,'
1331 ||' EFFICIENCY,'
1332 ||' BATCHABLE_FLAG,'
1333 ||' BATCHING_WINDOW,'
1334 ||' MIN_CAPACITY,'
1335 ||' MAX_CAPACITY,'
1336 ||' UNIT_OF_MEASURE,'
1337 ||' RESOURCE_COST,'
1338 ||' RESOURCE_OVER_UTIL_COST,'
1339 ||' DEPT_OVERHEAD_COST,'
1340 ||' DELETED_FLAG,'
1341 ||' REFRESH_ID,'
1342 ||' ATP_RULE_ID,'
1343 ||' SCHEDULE_TO_INSTANCE,' /* ds change */
1344 ||' BATCHING_PENALTY,'
1345 ||' SETUP_TIME_PERCENT,'
1346 ||' UTILIZATION_CHANGE_PERCENT,'
1347 ||' SETUP_TIME_TYPE,'
1348 ||' UTILIZATION_CHANGE_TYPE,'
1349 ||' IDLE_TIME_TOLERANCE,'
1350 ||' SDS_SCHEDULING_WINDOW,' /* ds change */
1351 ||' SR_INSTANCE_ID)'
1352 ||' select'
1353 ||' x.RESOURCE_ID,'
1354 ||' x.DEPARTMENT_ID,'
1355 ||' x.LINE_FLAG,'
1356 ||' x.OWNING_DEPARTMENT_ID,'
1357 ||' x.CAPACITY_UNITS,'
1358 ||' x.RESOURCE_GROUP_NAME,'
1359 ||' x.ORGANIZATION_ID,'
1360 ||' x.DEPARTMENT_CODE,'
1361 ||' x.DEPARTMENT_CLASS,'
1362 ||' x.DEPARTMENT_DESCRIPTION,'
1363 ||' x.RESOURCE_CODE,'
1364 ||' x.RESOURCE_DESCRIPTION,'
1365 ||' x.OVER_UTILIZED_PERCENT,'
1366 ||' x.UNDER_UTILIZED_PERCENT,'
1367 ||' x.RESOURCE_SHORTAGE_TYPE,'
1368 ||' x.RESOURCE_EXCESS_TYPE,'
1369 ||' x.PLANNING_EXCEPTION_SET,'
1370 ||' x.USER_TIME_FENCE,'
1371 ||' DECODE( TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1372 ||' 1, x.Attribute1,'
1373 ||' 2, x.Attribute2,'
1374 ||' 3, x.Attribute3,'
1375 ||' 4, x.Attribute4,'
1376 ||' 5, x.Attribute5,'
1377 ||' 6, x.Attribute6,'
1378 ||' 7, x.Attribute7,'
1379 ||' 8, x.Attribute8,'
1380 ||' 9, x.Attribute9,'
1381 ||' 10, x.Attribute10,'
1382 ||' 11, x.Attribute11,'
1383 ||' 12, x.Attribute12,'
1384 ||' 13, x.Attribute13,'
1385 ||' 14, x.Attribute14,'
1386 ||' 15, x.Attribute15)),'
1387 ||' x.RESOURCE_ID, 1,'
1388 ||' 2),'
1389 ||' TO_NUMBER(DECODE( :v_msc_aggreg_res_name,'
1390 ||' 1, x.Attribute1,'
1391 ||' 2, x.Attribute2,'
1392 ||' 3, x.Attribute3,'
1393 ||' 4, x.Attribute4,'
1394 ||' 5, x.Attribute5,'
1395 ||' 6, x.Attribute6,'
1396 ||' 7, x.Attribute7,'
1397 ||' 8, x.Attribute8,'
1398 ||' 9, x.Attribute9,'
1399 ||' 10, x.Attribute10,'
1400 ||' 11, x.Attribute11,'
1401 ||' 12, x.Attribute12,'
1402 ||' 13, x.Attribute13,'
1403 ||' 14, x.Attribute14,'
1404 ||' 15, x.Attribute15)),'
1405 ||' x.RESOURCE_TYPE,'
1406 ||' x.DISABLE_DATE- :v_dgmt,'
1407 ||' x.AVAILABLE_24_HOURS_FLAG,'
1408 ||' x.CTP_FLAG,'
1409 ||' x.UTILIZATION,'
1410 ||' x.EFFICIENCY,'
1411 ||' TO_NUMBER(DECODE( :v_msc_batchable_flag,'
1412 ||' 1, x.Attribute1,'
1413 ||' 2, x.Attribute2,'
1414 ||' 3, x.Attribute3,'
1415 ||' 4, x.Attribute4,'
1416 ||' 5, x.Attribute5,'
1417 ||' 6, x.Attribute6,'
1418 ||' 7, x.Attribute7,'
1419 ||' 8, x.Attribute8,'
1420 ||' 9, x.Attribute9,'
1421 ||' 10, x.Attribute10,'
1422 ||' 11, x.Attribute11,'
1423 ||' 12, x.Attribute12,'
1424 ||' 13, x.Attribute13,'
1425 ||' 14, x.Attribute14,'
1426 ||' 15, x.Attribute15,Null)) ,'
1427 ||' TO_NUMBER(DECODE( :v_msc_batching_window,'
1428 ||' 1, x.Attribute1,'
1429 ||' 2, x.Attribute2,'
1430 ||' 3, x.Attribute3,'
1431 ||' 4, x.Attribute4,'
1432 ||' 5, x.Attribute5,'
1433 ||' 6, x.Attribute6,'
1434 ||' 7, x.Attribute7,'
1435 ||' 8, x.Attribute8,'
1436 ||' 9, x.Attribute9,'
1437 ||' 10, x.Attribute10,'
1438 ||' 11, x.Attribute11,'
1439 ||' 12, x.Attribute12,'
1440 ||' 13, x.Attribute13,'
1441 ||' 14, x.Attribute14,'
1442 ||' 15, x.Attribute15,Null)) ,'
1443 ||' TO_NUMBER(DECODE( :v_msc_min_capacity,'
1444 ||' 1, x.Attribute1,'
1445 ||' 2, x.Attribute2,'
1446 ||' 3, x.Attribute3,'
1447 ||' 4, x.Attribute4,'
1448 ||' 5, x.Attribute5,'
1449 ||' 6, x.Attribute6,'
1450 ||' 7, x.Attribute7,'
1451 ||' 8, x.Attribute8,'
1452 ||' 9, x.Attribute9,'
1453 ||' 10, x.Attribute10,'
1454 ||' 11, x.Attribute11,'
1455 ||' 12, x.Attribute12,'
1456 ||' 13, x.Attribute13,'
1457 ||' 14, x.Attribute14,'
1458 ||' 15, x.Attribute15,Null)) ,'
1459 ||' TO_NUMBER(DECODE( :v_msc_max_capacity,'
1460 ||' 1, x.Attribute1,'
1461 ||' 2, x.Attribute2,'
1462 ||' 3, x.Attribute3,'
1463 ||' 4, x.Attribute4,'
1464 ||' 5, x.Attribute5,'
1465 ||' 6, x.Attribute6,'
1466 ||' 7, x.Attribute7,'
1467 ||' 8, x.Attribute8,'
1468 ||' 9, x.Attribute9,'
1469 ||' 10, x.Attribute10,'
1470 ||' 11, x.Attribute11,'
1471 ||' 12, x.Attribute12,'
1472 ||' 13, x.Attribute13,'
1473 ||' 14, x.Attribute14,'
1474 ||' 15, x.Attribute15,Null)) ,'
1475 ||' DECODE( :v_msc_unit_of_measure,'
1476 ||' 1, x.Attribute1,'
1477 ||' 2, x.Attribute2,'
1478 ||' 3, x.Attribute3,'
1479 ||' 4, x.Attribute4,'
1480 ||' 5, x.Attribute5,'
1481 ||' 6, x.Attribute6,'
1482 ||' 7, x.Attribute7,'
1483 ||' 8, x.Attribute8,'
1484 ||' 9, x.Attribute9,'
1485 ||' 10, x.Attribute10,'
1486 ||' 11, x.Attribute11,'
1487 ||' 12, x.Attribute12,'
1488 ||' 13, x.Attribute13,'
1489 ||' 14, x.Attribute14,'
1490 ||' 15, x.Attribute15,Null) ,'
1491 ||' x.RESOURCE_COST,'
1492 ||' TO_NUMBER(DECODE( :v_mso_res_penalty,'
1493 ||' 1, x.Attribute1,'
1494 ||' 2, x.Attribute2,'
1495 ||' 3, x.Attribute3,'
1496 ||' 4, x.Attribute4,'
1497 ||' 5, x.Attribute5,'
1498 ||' 6, x.Attribute6,'
1499 ||' 7, x.Attribute7,'
1500 ||' 8, x.Attribute8,'
1501 ||' 9, x.Attribute9,'
1502 ||' 10, x.Attribute10,'
1503 ||' 11, x.Attribute11,'
1504 ||' 12, x.Attribute12,'
1505 ||' 13, x.Attribute13,'
1506 ||' 14, x.Attribute14,'
1507 ||' 15, x.Attribute15)),'
1508 ||' x.DEPT_OVERHEAD_COST,'
1509 ||' 2,'
1510 ||' :v_refresh_id,'
1511 || v_temp_atp_rule_sql
1512 ||' :v_instance_id'
1513 ||' FROM MRP_AP_DEPARTMENT_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1514 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1515
1516 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_msc_aggreg_res_name,
1517 MSC_CL_PULL.v_msc_aggreg_res_name,
1518 MSC_CL_PULL.v_dgmt,
1519 MSC_CL_PULL.v_msc_batchable_flag,
1520 MSC_CL_PULL.v_msc_batching_window,
1521 MSC_CL_PULL.v_msc_min_capacity,
1522 MSC_CL_PULL.v_msc_max_capacity,
1523 MSC_CL_PULL.v_msc_unit_of_measure,
1524 MSC_CL_PULL.v_mso_res_penalty,
1525 MSC_CL_PULL.v_refresh_id,
1526 MSC_CL_PULL.v_instance_id;
1527
1528 END IF;
1529
1530 COMMIT;
1531
1532 IF ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) THEN
1533
1534 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPARTMENT_RESOURCES';
1535 MSC_CL_PULL.v_view_name := 'MRP_AP_LINE_RESOURCES_V';
1536
1537 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1538
1539 v_union_sql :=
1540 ' AND ( x.RN1 > :v_lrn )';
1541
1542 ELSE
1543
1544 v_union_sql := ' ';
1545
1546 END IF;
1547
1548
1549 v_sql_stmt:=
1550 ' insert into MSC_ST_DEPARTMENT_RESOURCES'
1551 ||' ( ORGANIZATION_ID,'
1552 ||' RESOURCE_ID,'
1553 ||' DEPARTMENT_ID,'
1554 ||' DEPARTMENT_CODE,'
1555 ||' LINE_FLAG,'
1556 ||' MAX_RATE,'
1557 ||' MIN_RATE,'
1558 ||' START_TIME,'
1559 ||' STOP_TIME,'
1560 ||' DEPARTMENT_DESCRIPTION,'
1561 ||' AGGREGATED_RESOURCE_FLAG,'
1562 ||' OVER_UTILIZED_PERCENT,'
1563 ||' UNDER_UTILIZED_PERCENT,'
1564 ||' RESOURCE_SHORTAGE_TYPE,'
1565 ||' RESOURCE_EXCESS_TYPE,'
1566 ||' PLANNING_EXCEPTION_SET,'
1567 ||' USER_TIME_FENCE,'
1568 ||' AVAILABLE_24_HOURS_FLAG,'
1569 ||' CAPACITY_UNITS,'
1570 ||' DISABLE_DATE,'
1571 ||' DELETED_FLAG,'
1572 ||' REFRESH_ID,'
1573 ||' ATP_RULE_ID,'
1574 ||' SETUP_TIME_PERCENT,'
1575 ||' UTILIZATION_CHANGE_PERCENT,'
1576 ||' SETUP_TIME_TYPE,'
1577 ||' UTILIZATION_CHANGE_TYPE,'
1578 ||' SR_INSTANCE_ID)'
1579 ||' select'
1580 ||' x.ORGANIZATION_ID,'
1581 ||' -1,'
1582 ||' x.DEPARTMENT_ID,'
1583 ||' x.DEPARTMENT_CODE,'
1584 ||' x.LINE_FLAG,'
1585 ||' x.MAX_RATE,'
1586 ||' x.MIN_RATE,'
1587 ||' x.START_TIME,'
1588 ||' x.STOP_TIME,'
1589 ||' x.DEPARTMENT_DESCRIPTION,'
1590 ||' 2,'
1591 ||' x.OVER_UTILIZED_PERCENT,'
1592 ||' x.UNDER_UTILIZED_PERCENT,'
1593 ||' x.RESOURCE_SHORTAGE_TYPE,'
1594 ||' x.RESOURCE_EXCESS_TYPE,'
1595 ||' x.PLANNING_EXCEPTION_SET,'
1596 ||' x.USER_TIME_FENCE,'
1597 ||' x.AVAILABLE_24_HOURS_FLAG,'
1598 ||' x.CAPACITY_UNITS,'
1599 ||' x.LINE_DISABLE_DATE- :v_dgmt,'
1600 ||' 2,'
1601 ||' :v_refresh_id,'
1602 || v_temp_atp_rule_sql1
1603 ||' :v_instance_id'
1604 ||' from MRP_AP_LINE_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1605 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1606 || v_union_sql;
1607
1608 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1609
1610 EXECUTE IMMEDIATE v_sql_stmt
1611 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1612
1613 ELSE
1614 EXECUTE IMMEDIATE v_sql_stmt
1615 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1616 END IF;
1617
1618
1619 COMMIT;
1620
1621 END IF; -- MSC_CL_PULL.WIP_ENABLED
1622
1623
1624 MSC_CL_PULL.v_table_name:= 'MSC_ST_SIMULATION_SETS';
1625 MSC_CL_PULL.v_view_name := 'MRP_AP_SIMULATION_SETS_V';
1626
1627 v_sql_stmt:=
1628 'insert into MSC_ST_SIMULATION_SETS'
1629 ||' ( ORGANIZATION_ID,'
1630 ||' SIMULATION_SET,'
1631 ||' DESCRIPTION,'
1632 ||' USE_IN_WIP_FLAG,'
1633 ||' DELETED_FLAG,'
1634 ||' REFRESH_ID,'
1635 ||' SR_INSTANCE_ID)'
1636 ||' select '
1637 ||' x.ORGANIZATION_ID,'
1638 ||' x.SIMULATION_SET,'
1639 ||' x.DESCRIPTION,'
1640 ||' x.USE_IN_WIP_FLAG,'
1641 ||' 2,'
1642 ||' :v_refresh_id,'
1643 ||' :v_instance_id'
1644 ||' from MRP_AP_SIMULATION_SETS_V'||MSC_CL_PULL.v_dblink||' x'
1645 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
1646
1647 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1648
1649 COMMIT;
1650
1651 IF MSC_CL_PULL.v_lrnn= -1 THEN -- complete refresh
1652
1653 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_GROUPS';
1654 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_GROUPS_V';
1655
1656 v_sql_stmt:=
1657 'insert into MSC_ST_RESOURCE_GROUPS'
1658 ||' ( GROUP_CODE,'
1659 ||' MEANING,'
1660 ||' DESCRIPTION,'
1661 ||' FROM_DATE,'
1662 ||' TO_DATE,'
1663 ||' ENABLED_FLAG,'
1664 ||' DELETED_FLAG,'
1665 ||' REFRESH_ID,'
1666 ||' SR_INSTANCE_ID)'
1667 ||' select '
1668 ||' x.GROUP_CODE,'
1669 ||' x.MEANING,'
1670 ||' x.DESCRIPTION,'
1671 ||' x.FROM_DATE,'
1672 ||' x.TO_DATE,'
1673 ||' DECODE( x.ENABLED_FLAG, ''Y'', 1 , 2),'
1674 ||' 2,'
1675 ||' :v_refresh_id,'
1676 ||' :v_instance_id'
1677 ||' from MRP_AP_RESOURCE_GROUPS_V'||MSC_CL_PULL.v_dblink||' x';
1678
1679 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
1680
1681 COMMIT;
1682
1683 END IF;
1684
1685 IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
1686
1687 IF (nvl(fnd_profile.value('MSC_INFLATE_WIP') ,'N')= 'N') THEN
1688 lv_inflate_wip := 2 ;
1689 ELSE
1690 lv_inflate_wip := 1 ;
1691 END IF;
1692
1693 -- if the profile MSC_INFLATE_WIP is set to YES then inflating the operation resource hours by
1694 -- efficiency and utilization. Not inflating the operation resource hours for SDS records.
1695 -- for bug fix 2877975. Lot based jobs should get inflated irrespective of the profile.
1696 IF lv_inflate_wip = 1 AND MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1697 v_res_hrs_sql := ' decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) ,';
1698 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) - x.HOURS_EXPENDED >0) ';
1699 v_touch_time := ' decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) ,';
1700 ELSIF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1701 v_res_hrs_sql := ' decode(x.entity_type,5, (decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED)),x.OPERATION_HOURS_REQUIRED),';
1702 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.utilization)* (1/x.efficiency),x.OPERATION_HOURS_REQUIRED) - x.HOURS_EXPENDED > 0) ';
1703 v_touch_time := ' decode(x.entity_type,5, (decode(nvl(x.PARENT_SEQ_NUM,-1),-1,x.OPERATION_HOURS_REQUIRED * (1/x.efficiency),x.OPERATION_HOURS_REQUIRED)),x.OPERATION_HOURS_REQUIRED),';
1704 ELSE
1705 v_res_hrs_sql := ' x.OPERATION_HOURS_REQUIRED ,';
1706 v_res_hrs_left := ' AND (x.ENTITY_TYPE <>5 or x.OPERATION_HOURS_REQUIRED - x.HOURS_EXPENDED > 0 ) ';
1707 v_touch_time := ' x.OPERATION_HOURS_REQUIRED ,';
1708 END IF;
1709
1710 -- Pulling parent_seq_num and SETUP_ID from MRP_AP_RESOURCE_REQUIREMENTS_V if MSC_CL_PULL.v_apps_ver is MSC_UTIL.G_APPS115
1711 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1712 v_temp_parent_seq := 'x.PARENT_SEQ_NUM,x.SETUP_ID, x.ORIG_RESOURCE_SEQ_NUM,GROUP_SEQUENCE_ID,GROUP_SEQUENCE_NUMBER,BATCH_NUMBER,MAXIMUM_ASSIGNED_UNITS, ';
1713 ELSE
1714 v_temp_parent_seq := 'NULL,NULL,NULL,NULL,NULL,NULL,NULL,';
1715 END IF;
1716
1717 If MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1718 select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
1719 where instance_id = MSC_CL_PULL.v_instance_id ;
1720 end if;
1721
1722 --=================== Net Change Mode: Delete ==================
1723 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1724
1725 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1726 --MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_REQUIREMENTS_V';
1727 -- bug5996354
1728 if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1729 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_IRO_ERO_RES_REQ_V';
1730 else
1731 MSC_CL_PULL.v_view_name := 'MRP_AD_RESOURCE_REQUIREMENTS_V';
1732 end if;
1733 --
1734
1735 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1736 v_temp_sql1 := ' x.RESOURCE_SEQ_NUM, ';
1737 ELSE
1738 v_temp_sql1 := ' NULL, ';
1739 END IF;
1740
1741 lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1742
1743 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1744 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1745
1746 if lv_lbj_details = 1 Then
1747 lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1748 end if;
1749
1750 ELSE
1751 v_temp_sql := NULL;
1752 END IF;
1753
1754 v_sql_stmt:=
1755 'insert into MSC_ST_RESOURCE_REQUIREMENTS'
1756 ||' ( WIP_ENTITY_ID,'
1757 ||' OPERATION_SEQ_NUM,'
1758 ||' ORIG_RESOURCE_SEQ_NUM,'
1759 ||' DELETED_FLAG,'
1760 ||' REFRESH_ID,'
1761 ||' SR_INSTANCE_ID)'
1762 ||' select'
1763 ||' x.WIP_ENTITY_ID,'
1764 || lv_op_seq_num
1765 || v_temp_sql1
1766 ||' 1,'
1767 ||' :v_refresh_id,'
1768 ||' :v_instance_id'
1769 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1770 ||' WHERE x.RN> :v_lrn '
1771 ||' AND DECODE( x.operation_seq_num,'
1772 ||' NULL, DECODE( x.wip_job_type,'
1773 ||' 1, DECODE( :v_mps_consume_profile_value,'
1774 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1775 ||' x.WJS_NET_QTY_FLAG), '
1776 ||' x.WJS_MPS_NET_QTY_FLAG),'
1777 ||' 1)= 1'
1778 || v_temp_sql;
1779
1780
1781 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1782 MSC_CL_PULL.v_instance_id,
1783 MSC_CL_PULL.v_lrn,
1784 MSC_CL_PULL.v_mps_consume_profile_value;
1785
1786 COMMIT;
1787
1788 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1789
1790 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
1791 MSC_CL_PULL.v_view_name := 'MRP_AD_DJOB_SUB_OP_RESOURCES_V';
1792
1793 v_sql_stmt:=
1794 'insert into MSC_ST_JOB_OP_RESOURCES'
1795 ||' ( WIP_ENTITY_ID,'
1796 ||' OPERATION_SEQ_NUM,'
1797 ||' RESOURCE_SEQ_NUM,'
1798 ||' DELETED_FLAG,'
1799 ||' REFRESH_ID,'
1800 ||' SR_INSTANCE_ID)'
1801 ||' select'
1802 ||' x.WIP_ENTITY_ID,'
1803 ||' x.OPERATION_SEQ_NUM,'
1804 ||' x.RESOURCE_SEQ_NUM,'
1805 ||' 1,'
1806 ||' :v_refresh_id,'
1807 ||' :v_instance_id'
1808 ||' from MRP_AD_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
1809 ||' where x.RN> '||MSC_CL_PULL.v_lrn
1810 ||' AND DECODE( x.operation_seq_num,'
1811 ||' NULL, DECODE( :v_mps_consume_profile_value,'
1812 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1813 ||' x.WJS_NET_QTY_FLAG), '
1814 ||' 1)= 1'
1815 || v_temp_sql;
1816
1817 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1818 MSC_CL_PULL.v_instance_id,
1819 MSC_CL_PULL.v_mps_consume_profile_value;
1820
1821 COMMIT;
1822
1823 END IF;
1824
1825 END IF;
1826
1827 IF MSC_CL_PULL.v_lrnn <> -1 THEN
1828 -- BUG 7521174
1829 -- No Quantity filter for net change collection.
1830 -- Only Refresh Number
1831
1832 lv_qty_sql_temp := NULL;
1833
1834 ELSIF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
1835 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1836 ||' 1, DECODE( :v_mps_consume_profile_value, '
1837 ||' 1, x.mps_net_quantity,'
1838 ||' x.net_quantity), '
1839 ||' x.net_quantity) >= 0' ;
1840 ELSE
1841 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1842 ||' 1, DECODE( :v_mps_consume_profile_value, '
1843 ||' 1, x.mps_net_quantity,'
1844 ||' x.net_quantity), '
1845 ||' x.net_quantity) > 0' ;
1846 END IF;
1847 -- bug5996354
1848 if MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' and MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS120 then
1849 MSC_CL_PULL.v_view_name := 'MRP_AP_NON_IRO_ERO_RES_REQ_V';
1850 else
1851 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1852 end if;
1853 IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1854 v_temp_sql1 := ' x.Operation_Code,'
1855 ||' (CASE WHEN x.maintenance_object_source in (1,2) THEN '
1856 ||' to_date(substr(x.WO_ACTUAL_DATES,instr(x.WO_ACTUAL_DATES,''#'')+1),''DD-MON-YYYY'')'
1857 ||' END), ';
1858 ELSE
1859 v_temp_sql1 := 'NULL,NULL,';
1860 END IF;
1861
1862 /* set the MTQ , Firm Flag, Scheduled flag */
1863 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1864 IF lv_lbj_details =1 Then
1865 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,x.scheduled_flag, x.QUANTITY_IN_QUEUE, x.QUANTITY_RUNNING, x.QUANTITY_WAITING_TO_MOVE, x.QUANTITY_COMPLETED,'
1866 ||' decode(x.ENTITY_TYPE,5,x.COPY_YIELD, x.YIELD), x.USAGE_RATE, x.Copy_op_seq_num, decode(x.ENTITY_TYPE,5,copy_STANDARD_OPERATION_CODE,Standard_Operation_Code), x.ACTIVITY_GROUP_ID, x.ALTERNATE_NUM, x.PRINCIPLE_FLAG, ';
1867 v_temp_sql := v_temp_sql
1868 ||' (CASE WHEN x.maintenance_object_source in (1,2) THEN '
1869 ||' to_date(substr(x.WO_ACTUAL_DATES,1,instr(x.WO_ACTUAL_DATES,''#'')-1),''DD-MON-YYYY'')'
1870 ||' ELSE x.ACTUAL_START_DATE '
1871 ||' END), ';
1872
1873 v_temp_sql := v_temp_sql ||' x.ROUTING_SEQUENCE_ID,';
1874 ELSE
1875 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,x.scheduled_flag, x.QUANTITY_IN_QUEUE, x.QUANTITY_RUNNING, x.QUANTITY_WAITING_TO_MOVE, x.QUANTITY_COMPLETED,'
1876 ||' x.YIELD, x.USAGE_RATE, x.Operation_Seq_Num, x.Standard_Operation_Code, x.ACTIVITY_GROUP_ID, x.ALTERNATE_NUM, x.PRINCIPLE_FLAG, x.ACTUAL_START_DATE, x.ROUTING_SEQUENCE_ID,';
1877 lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
1878 END IF;
1879 IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121 THEN
1880 v_temp_sql := v_temp_sql||'x.description,';
1881 ELSE
1882 v_temp_sql := v_temp_sql||'NULL,';
1883 END IF;
1884 ELSIF MSC_CL_PULL.v_apps_ver= MSC_UTIL.G_APPS110 THEN
1885 v_temp_sql := 'x.minimum_transfer_quantity,x.firm_flag,NULL,NULL,NULL,NULL,NULL,NULL,NULL, x.OPERATION_SEQ_NUM, x.Standard_Operation_Code, NULL, NULL, NULL, NULL, NULL,NULL, ';
1886 ELSE
1887 v_temp_sql := ' NULL ,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL, x.OPERATION_SEQ_NUM, x.Standard_Operation_Code, NULL, NULL, NULL, NULL, NULL,NULL, ';
1888 END IF;
1889
1890 /*USAF starts*/
1891 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER and MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
1892 v_temp_eam_sql := 'x.SHUTDOWN_TYPE,';
1893 ELSE
1894 v_temp_eam_sql := 'NULL,';
1895 END IF;
1896
1897 /*USAF ends*/
1898
1899 --
1900
1901
1902 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_REQUIREMENTS';
1903 --MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_REQUIREMENTS_V';
1904 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1905
1906 -- BUG 3036681
1907 -- No Need to check for RN2 (on wip_discrete_jobs) as
1908 -- the materialized view on wip_operations now has the columns from
1909 -- wip_discrete_jobs too.
1910 lv_hint := ' /*+ first_rows leading(x.msi) index(x.msi MTL_SYS_ITEMS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.msi. x.wo x.wor) */ ';
1911
1912 v_union_sql :=
1913 ' AND ( x.RN1> :v_lrn )'
1914 ||' UNION '
1915 ||' select /*+ first_rows leading(x.wo) index(x.wo WIP_WOPRS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.wo x.wor x.wor1) */ '
1916 ||' x.DEPARTMENT_ID,'
1917 ||' x.RESOURCE_ID,'
1918 ||' x.ORGANIZATION_ID,'
1919 ||' x.INVENTORY_ITEM_ID,'
1920 ||' x.WIP_ENTITY_ID,'
1921 ||' x.WIP_ENTITY_ID,'
1922 ||' x.WIP_JOB_TYPE,'
1923 ||' x.OPERATION_SEQUENCE_ID,'
1924 ||' x.RESOURCE_SEQ_NUM,'
1925 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
1926 || v_res_hrs_sql
1927 ||' x.HOURS_EXPENDED,'
1928 ||' x.DEMAND_CLASS,'
1929 ||' x.BASIS_TYPE,'
1930 ||' x.RESOURCE_UNITS,'
1931 ||' x.COMPLETION_DATE- :v_dgmt,'
1932 ||' x.WIP_JOB_TYPE,'
1933 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
1934 ||' x.SCHEDULED_QUANTITY,'
1935 ||' 2,'
1936 || v_temp_eam_sql
1937 || v_temp_sql
1938 ||' :v_refresh_id,'
1939 || v_temp_parent_seq
1940 ||' x.OPERATION_HOURS_REQUIRED,'
1941 || v_touch_time
1942 || v_temp_sql1
1943 ||' :v_instance_id'
1944 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1945 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1946 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1947 ||' 1, DECODE( :v_mps_consume_profile_value, '
1948 ||' 1, x.mps_net_quantity,'
1949 ||' x.net_quantity), '
1950 ||' x.net_quantity) > 0'*/
1951 || lv_qty_sql_temp
1952 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
1953 || v_res_hrs_left
1954 || lv_cond_sql
1955 ||' AND ( x.RN2> :v_lrn )'
1956 ||' UNION '
1957 ||' select /*+ first_rows leading(x.wo) index(x.wo WIP_WOPRS_SN_N1) index(x.wor wip_wopr_ress_sn_n2) use_nl(x.wo x.wor x.wor1) */ '
1958 ||' x.DEPARTMENT_ID,'
1959 ||' x.RESOURCE_ID,'
1960 ||' x.ORGANIZATION_ID,'
1961 ||' x.INVENTORY_ITEM_ID,'
1962 ||' x.WIP_ENTITY_ID,'
1963 ||' x.WIP_ENTITY_ID,'
1964 ||' x.WIP_JOB_TYPE,'
1965 ||' x.OPERATION_SEQUENCE_ID,'
1966 ||' x.RESOURCE_SEQ_NUM,'
1967 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
1968 || v_res_hrs_sql
1969 ||' x.HOURS_EXPENDED,'
1970 ||' x.DEMAND_CLASS,'
1971 ||' x.BASIS_TYPE,'
1972 ||' x.RESOURCE_UNITS,'
1973 ||' x.COMPLETION_DATE- :v_dgmt,'
1974 ||' x.WIP_JOB_TYPE,'
1975 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
1976 ||' x.SCHEDULED_QUANTITY,'
1977 ||' 2,'
1978 || v_temp_eam_sql
1979 || v_temp_sql
1980 ||' :v_refresh_id,'
1981 || v_temp_parent_seq
1982 ||' x.OPERATION_HOURS_REQUIRED,'
1983 || v_touch_time
1984 || v_temp_sql1
1985 ||' :v_instance_id'
1986 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
1987 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1988 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
1989 ||' 1, DECODE( :v_mps_consume_profile_value, '
1990 ||' 1, x.mps_net_quantity,'
1991 ||' x.net_quantity), '
1992 ||' x.net_quantity) > 0' */
1993 || lv_qty_sql_temp
1994 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
1995 || v_res_hrs_left
1996 || lv_cond_sql
1997 ||' AND ( x.RN3> :v_lrn )';
1998
1999 ELSE
2000
2001 lv_hint := ' /*+ leading(x.wo) use_hash(x.wo x.wor x.wor1) */ ';
2002
2003 v_union_sql := ' ';
2004
2005 END IF;
2006
2007 v_sql_stmt:=
2008 'insert into MSC_ST_RESOURCE_REQUIREMENTS'
2009 ||' ( DEPARTMENT_ID,'
2010 ||' RESOURCE_ID,'
2011 ||' ORGANIZATION_ID,'
2012 ||' INVENTORY_ITEM_ID,'
2013 ||' SUPPLY_ID,'
2014 ||' WIP_ENTITY_ID,'
2015 ||' SUPPLY_TYPE,'
2016 ||' OPERATION_SEQUENCE_ID,'
2017 ||' RESOURCE_SEQ_NUM,'
2018 ||' START_DATE,'
2019 ||' OPERATION_HOURS_REQUIRED,'
2020 ||' HOURS_EXPENDED,'
2021 ||' DEMAND_CLASS,'
2022 ||' BASIS_TYPE,'
2023 ||' ASSIGNED_UNITS,'
2024 ||' END_DATE,'
2025 ||' WIP_JOB_TYPE,'
2026 ||' SCHEDULED_COMPLETION_DATE,'
2027 ||' SCHEDULED_QUANTITY,'
2028 ||' DELETED_FLAG,'
2029 ||' SHUTDOWN_TYPE,'
2030 ||' MINIMUM_TRANSFER_QUANTITY,'
2031 ||' FIRM_FLAG,'
2032 ||' SCHEDULE_FLAG,'
2033 ||' QUANTITY_IN_QUEUE,'
2034 ||' QUANTITY_RUNNING,'
2035 ||' QUANTITY_WAITING_TO_MOVE,'
2036 ||' QUANTITY_COMPLETED,'
2037 ||' YIELD,'
2038 ||' USAGE_RATE,'
2039 ||' OPERATION_SEQ_NUM,'
2040 ||' STD_OP_CODE,'
2041 ||' ACTIVITY_GROUP_ID,'
2042 ||' ALTERNATE_NUMBER,'
2043 ||' PRINCIPAL_FLAG,'
2044 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
2045 ||' ROUTING_SEQUENCE_ID,'
2046 ||' OPERATION_NAME,'
2047 ||' REFRESH_ID,'
2048 ||' PARENT_SEQ_NUM,'
2049 ||' SETUP_ID,'
2050 ||' ORIG_RESOURCE_SEQ_NUM,'
2051 ||' GROUP_SEQUENCE_ID,'
2052 ||' GROUP_SEQUENCE_NUMBER,'
2053 ||' BATCH_NUMBER,'
2054 ||' MAXIMUM_ASSIGNED_UNITS,'
2055 ||' UNADJUSTED_RESOURCE_HOURS,'
2056 ||' TOUCH_TIME,'
2057 ||' Operation_Code,'
2058 ||' ACTUAL_END_DATE,'
2059 ||' SR_INSTANCE_ID)'
2060 ||' select '|| lv_hint
2061 ||' x.DEPARTMENT_ID,'
2062 ||' x.RESOURCE_ID,'
2063 ||' x.ORGANIZATION_ID,'
2064 ||' x.INVENTORY_ITEM_ID,'
2065 ||' x.WIP_ENTITY_ID,'
2066 ||' x.WIP_ENTITY_ID,'
2067 ||' x.WIP_JOB_TYPE,'
2068 ||' x.OPERATION_SEQUENCE_ID,'
2069 ||' x.RESOURCE_SEQ_NUM,'
2070 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2071 || v_res_hrs_sql
2072 ||' x.HOURS_EXPENDED,'
2073 ||' x.DEMAND_CLASS,'
2074 ||' x.BASIS_TYPE,'
2075 ||' x.RESOURCE_UNITS,'
2076 ||' x.COMPLETION_DATE- :v_dgmt,'
2077 ||' x.WIP_JOB_TYPE,'
2078 ||' x.SCHEDULED_COMPLETION_DATE- :v_dgmt,'
2079 ||' x.SCHEDULED_QUANTITY,'
2080 ||' 2,'
2081 || v_temp_eam_sql
2082 || v_temp_sql
2083 ||' :v_refresh_id,'
2084 || v_temp_parent_seq
2085 ||' x.OPERATION_HOURS_REQUIRED,'
2086 || v_touch_time
2087 || v_temp_sql1
2088 ||' :v_instance_id'
2089 ||' from '||MSC_CL_PULL.v_view_name||MSC_CL_PULL.v_dblink||' x'
2090 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2091 /*Bug#4704457 ||' AND DECODE( x.wip_job_type, '
2092 ||' 1, DECODE( :v_mps_consume_profile_value, '
2093 ||' 1, x.mps_net_quantity,'
2094 ||' x.net_quantity), '
2095 ||' x.net_quantity) > 0' */
2096 || lv_qty_sql_temp
2097 ||' AND nvl(x.uom_code,:v_hour_uom) = :v_hour_uom'
2098 || v_res_hrs_left
2099 || lv_cond_sql
2100 || v_union_sql ;
2101
2102 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2103
2104 EXECUTE IMMEDIATE v_sql_stmt USING
2105 MSC_CL_PULL.v_dgmt,
2106 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2107 --MSC_CL_PULL.v_mps_consume_profile_value,
2108 MSC_CL_PULL.v_hour_uom,
2109 MSC_CL_PULL.v_hour_uom,
2110 MSC_CL_PULL.v_lrn,
2111 MSC_CL_PULL.v_dgmt,
2112 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2113 --MSC_CL_PULL.v_mps_consume_profile_value,
2114 MSC_CL_PULL.v_hour_uom,
2115 MSC_CL_PULL.v_hour_uom,
2116 MSC_CL_PULL.v_lrn,
2117 MSC_CL_PULL.v_dgmt,
2118 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2119 --MSC_CL_PULL.v_mps_consume_profile_value,
2120 MSC_CL_PULL.v_hour_uom,
2121 MSC_CL_PULL.v_hour_uom,
2122 MSC_CL_PULL.v_lrn;
2123
2124 ELSE
2125
2126 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt,
2127 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
2128 MSC_CL_PULL.v_mps_consume_profile_value,
2129 MSC_CL_PULL.v_hour_uom,
2130 MSC_CL_PULL.v_hour_uom;
2131 END IF;
2132 COMMIT;
2133
2134 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
2135
2136 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2137 MSC_CL_PULL.v_view_name := 'MRP_AP_DJOB_SUB_OP_RESOURCES_V';
2138
2139
2140 v_sql_stmt:=
2141 'insert into MSC_ST_JOB_OP_RESOURCES'
2142 ||' (WIP_ENTITY_ID,'
2143 ||' SR_INSTANCE_ID,'
2144 ||' ORGANIZATION_ID,'
2145 ||' OPERATION_SEQ_NUM,'
2146 ||' RESOURCE_SEQ_NUM,'
2147 ||' ALTERNATE_NUM,'
2148 ||' RECOMMENDED,'
2149 ||' RECO_START_DATE,'
2150 ||' RECO_COMPLETION_DATE,'
2151 ||' RESOURCE_ID,'
2152 ||' ASSIGNED_UNITS,'
2153 ||' USAGE_RATE_OR_AMOUNT,'
2154 ||' UOM_CODE,'
2155 ||' BASIS_TYPE,'
2156 ||' RESOURCE_OFFSET_PERCENT,'
2157 ||' SCHEDULE_SEQ_NUM,'
2158 ||' PRINCIPAL_FLAG,'
2159 ||' DEPARTMENT_ID,'
2160 ||' ACTIVITY_GROUP_ID,'
2161 ||' SCHEDULE_FLAG,'
2162 ||' SETUP_ID,'
2163 ||' DELETED_FLAG,'
2164 ||' REFRESH_ID )'
2165 ||' select '
2166 ||' x.WIP_ENTITY_ID,'
2167 ||' :v_instance_id,'
2168 ||' x.ORGANIZATION_ID,'
2169 ||' x.OPERATION_SEQ_NUM,'
2170 ||' x.RESOURCE_SEQ_NUM,'
2171 ||' x.ALTERNATE_NUM,'
2172 ||' ''Y'' ,'
2173 ||' NULL,'
2174 ||' NULL,'
2175 ||' x.RESOURCE_ID,'
2176 ||' x.ASSIGNED_UNITS,'
2177 ||' x.USAGE_RATE_OR_AMOUNT,'
2178 ||' x.UOM_CODE,'
2179 ||' x.BASIS_TYPE,'
2180 ||' x.RESOURCE_OFFSET_PERCENT,'
2181 ||' x.SCHEDULE_SEQ_NUM,'
2182 ||' x.PRINCIPLE_FLAG,'
2183 ||' x.DEPARTMENT_ID,'
2184 ||' x.ACTIVITY_GROUP_ID,'
2185 ||' x.SCHEDULE_FLAG,'
2186 ||' x.SETUP_ID,'
2187 ||' 2,'
2188 ||' :v_refresh_id'
2189 ||' FROM MRP_AP_DJOB_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2190 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2191 ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2192 ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2193 ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2194
2195 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2196
2197 COMMIT;
2198
2199 END IF; /* MSC_UTIL.G_APPS115 */
2200
2201
2202 END IF; -- MSC_CL_PULL.WIP_ENABLED
2203
2204 END LOAD_RESOURCE;
2205
2206 /* ds change start */
2207 PROCEDURE LOAD_RESOURCE_INSTANCE IS
2208
2209 lv_inflate_wip NUMBER;
2210 v_res_hrs_sql VARCHAR2(300);
2211 v_temp_parent_seq VARCHAR2(100);
2212 lv_lbj_details NUMBER :=0;
2213 lv_cond_sql VARCHAR2(100) := null;
2214 lv_op_seq_num VARCHAR2(100) := null;
2215
2216 BEGIN
2217
2218 IF ( ((MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) OR (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES)) AND
2219 MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) THEN
2220 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEPT_RES_INSTANCES';
2221 MSC_CL_PULL.v_view_name := 'MRP_AP_DEPT_RES_INSTANCES_V';
2222
2223 v_sql_stmt:=
2224 ' insert into MSC_ST_DEPT_RES_INSTANCES'
2225 ||' ( RESOURCE_ID,'
2226 ||' DEPARTMENT_ID,'
2227 ||' RES_INSTANCE_ID,'
2228 ||' SERIAL_NUMBER,'
2229 ||' EQUIPMENT_ITEM_ID,'
2230 ||' DEPARTMENT_CODE,'
2231 ||' ORGANIZATION_ID,'
2232 ||' RESOURCE_CODE,'
2233 ||' LAST_KNOWN_SETUP,'
2234 ||' DELETED_FLAG,'
2235 ||' REFRESH_ID,'
2236 ||' SR_INSTANCE_ID)'
2237 ||' select'
2238 ||' x.RESOURCE_ID,'
2239 ||' x.DEPARTMENT_ID,'
2240 ||' x.RES_INSTANCE_ID,'
2241 ||' x.SERIAL_NUMBER,'
2242 ||' x.EQUIPMENT_ITEM_ID,'
2243 ||' x.DEPARTMENT_CODE,'
2244 ||' x.ORGANIZATION_ID,'
2245 ||' x.RESOURCE_CODE,'
2246 ||' x.LAST_KNOWN_SETUP,'
2247 ||' 2,'
2248 ||' :v_refresh_id,'
2249 ||' :v_instance_id'
2250 ||' from MRP_AP_DEPT_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2251 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2252
2253 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'to be removed: Ds debug: dept_res_instance sql = '||v_sql_stmt);
2254 EXECUTE IMMEDIATE v_sql_stmt USING
2255 MSC_CL_PULL.v_refresh_id,
2256 MSC_CL_PULL.v_instance_id;
2257
2258
2259 COMMIT;
2260
2261 END IF;
2262
2263
2264 IF ( (MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES) AND
2265 (MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115) ) THEN
2266
2267 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2268 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2269 MSC_CL_PULL.v_view_name := 'MRP_AD_RES_INSTANCE_REQS_V';
2270 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2271
2272 v_sql_stmt:=
2273 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2274 ||' ( WIP_ENTITY_ID,'
2275 ||' OPERATION_SEQ_NUM,'
2276 ||' RESOURCE_SEQ_NUM,'
2277 ||' RES_INSTANCE_ID,'
2278 ||' SERIAL_NUMBER,'
2279 ||' DELETED_FLAG,'
2280 ||' REFRESH_ID,'
2281 ||' SR_INSTANCE_ID)'
2282 ||' select'
2283 ||' x.WIP_ENTITY_ID,'
2284 ||' x.OPERATION_SEQ_NUM,'
2285 ||' x.RESOURCE_SEQ_NUM,'
2286 ||' x.RES_INSTANCE_ID,'
2287 ||' x.SERIAL_NUMBER,'
2288 ||' 1,'
2289 ||' :v_refresh_id,'
2290 ||' :v_instance_id'
2291 ||' from MRP_AD_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2292 ||' WHERE x.RN > :v_lrn '
2293 || v_temp_sql;
2294
2295
2296 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: res_instance sql = '||v_sql_stmt);
2297 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2298 MSC_CL_PULL.v_instance_id,
2299 MSC_CL_PULL.v_lrn;
2300
2301
2302 COMMIT;
2303
2304 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2305 MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_OPR_RES_INSTS_V';
2306
2307
2308 v_sql_stmt:=
2309 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2310 ||' ( WIP_ENTITY_ID,'
2311 ||' OPERATION_SEQ_NUM,'
2312 ||' RESOURCE_SEQ_NUM,'
2313 ||' RES_INSTANCE_ID,'
2314 ||' SERIAL_NUMBER,'
2315 ||' DELETED_FLAG,'
2316 ||' REFRESH_ID,'
2317 ||' SR_INSTANCE_ID)'
2318 ||' select'
2319 ||' x.WIP_ENTITY_ID,'
2320 ||' x.OPERATION_SEQ_NUM,'
2321 ||' x.RESOURCE_SEQ_NUM,'
2322 ||' x.RES_INSTANCE_ID,'
2323 ||' x.SERIAL_NUMBER,'
2324 ||' 1,'
2325 ||' :v_refresh_id,'
2326 ||' :v_instance_id'
2327 ||' from MRP_AD_LJ_OPR_RES_INSTS_V'||MSC_CL_PULL.v_dblink||' x'
2328 ||' WHERE x.RN > :v_lrn '
2329 || v_temp_sql;
2330
2331 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: job res_instance sql = '||v_sql_stmt);
2332 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2333 MSC_CL_PULL.v_instance_id,
2334 MSC_CL_PULL.v_lrn;
2335 COMMIT;
2336
2337 END IF; /* incremental */
2338
2339
2340 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_INSTANCE_REQS';
2341 MSC_CL_PULL.v_view_name := 'MRP_AP_RES_INSTANCE_REQS_V';
2342
2343
2344 IF lv_lbj_details <> 1 THEN
2345 lv_cond_sql := ' AND (x.ENTITY_TYPE <> 5 OR x.OPERATION_SEQUENCE_ID is not null) ';
2346 END IF;
2347
2348 IF MSC_CL_PULL.v_lrnn<> -1 THEN /* incremental */
2349
2350 v_union_sql :=
2351 ' AND ( x.RN1> :v_lrn )'
2352 ||' UNION '
2353 ||' select '
2354 ||' x.DEPARTMENT_ID,'
2355 ||' x.RESOURCE_ID,'
2356 ||' x.RES_INSTANCE_ID,'
2357 ||' x.ORGANIZATION_ID,'
2358 --||' x.OPERATION_SEQUENCE_ID,'
2359 ||' x.OPERATION_SEQ_NUM,'
2360 ||' x.RESOURCE_SEQ_NUM,'
2361 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2362 ||' x.SERIAL_NUMBER,'
2363 ||' x.EQUIPMENT_ITEM_ID,'
2364 ||' x.WIP_ENTITY_ID,'
2365 ||' x.WIP_ENTITY_ID,'
2366 ||' x.START_DATE - :v_dgmt,'
2367 ||' x.COMPLETION_DATE - :v_dgmt,'
2368 ||' 2,'
2369 ||' :v_refresh_id,'
2370 ||' :v_instance_id'
2371 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2372 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2373 || lv_cond_sql
2374 ||' AND ( x.RN2> :v_lrn )'
2375 ||' UNION '
2376 ||' select '
2377 ||' x.DEPARTMENT_ID,'
2378 ||' x.RESOURCE_ID,'
2379 ||' x.RES_INSTANCE_ID,'
2380 ||' x.ORGANIZATION_ID,'
2381 --||' x.OPERATION_SEQUENCE_ID,'
2382 ||' x.OPERATION_SEQ_NUM,'
2383 ||' x.RESOURCE_SEQ_NUM,'
2384 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2385 ||' x.SERIAL_NUMBER,'
2386 ||' x.EQUIPMENT_ITEM_ID,'
2387 ||' x.WIP_ENTITY_ID,'
2388 ||' x.WIP_ENTITY_ID,'
2389 ||' x.START_DATE - :v_dgmt,'
2390 ||' x.COMPLETION_DATE- :v_dgmt,'
2391 ||' 2,'
2392 ||' :v_refresh_id,'
2393 ||' :v_instance_id'
2394 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2395 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2396 || lv_cond_sql
2397 ||' AND ( x.RN3> :v_lrn )'
2398 ||' UNION '
2399 ||' select '
2400 ||' x.DEPARTMENT_ID,'
2401 ||' x.RESOURCE_ID,'
2402 ||' x.RES_INSTANCE_ID,'
2403 ||' x.ORGANIZATION_ID,'
2404 --||' x.OPERATION_SEQUENCE_ID,'
2405 ||' x.OPERATION_SEQ_NUM,'
2406 ||' x.RESOURCE_SEQ_NUM,'
2407 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2408 ||' x.SERIAL_NUMBER,'
2409 ||' x.EQUIPMENT_ITEM_ID,'
2410 ||' x.WIP_ENTITY_ID,'
2411 ||' x.WIP_ENTITY_ID,'
2412 ||' x.START_DATE - :v_dgmt,'
2413 ||' x.COMPLETION_DATE - :v_dgmt,'
2414 ||' 2,'
2415 ||' :v_refresh_id,'
2416 ||' :v_instance_id'
2417 ||' from MRP_AP_RES_INSTANCE_REQS_V' ||MSC_CL_PULL.v_dblink ||' x'
2418 ||' where x.ORGANIZATION_ID' ||MSC_UTIL.v_in_org_str
2419 || lv_cond_sql
2420 ||' AND ( x.RN4> :v_lrn )';
2421 ELSE -- full
2422
2423 v_union_sql := ' ';
2424
2425 END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
2426
2427
2428 v_sql_stmt:=
2429 'insert into MSC_ST_RESOURCE_INSTANCE_REQS'
2430 ||' ( DEPARTMENT_ID,'
2431 ||' RESOURCE_ID,'
2432 ||' RES_INSTANCE_ID,'
2433 ||' ORGANIZATION_ID,'
2434 --||' OPERATION_SEQUENCE_ID,'
2435 ||' OPERATION_SEQ_NUM,'
2436 ||' RESOURCE_SEQ_NUM,'
2437 ||' ORIG_RESOURCE_SEQ_NUM,'
2438 ||' SERIAL_NUMBER,'
2439 ||' EQUIPMENT_ITEM_ID,'
2440 ||' SUPPLY_ID,'
2441 ||' WIP_ENTITY_ID,'
2442 ||' START_DATE,'
2443 ||' END_DATE,'
2444 ||' DELETED_FLAG,'
2445 ||' REFRESH_ID,'
2446 ||' SR_INSTANCE_ID)'
2447 ||' select ' /* || lv_hint */
2448 ||' x.DEPARTMENT_ID,'
2449 ||' x.RESOURCE_ID,'
2450 ||' x.RES_INSTANCE_ID,'
2451 ||' x.ORGANIZATION_ID,'
2452 --||' x.OPERATION_SEQUENCE_ID,'
2453 ||' x.OPERATION_SEQ_NUM,'
2454 ||' x.RESOURCE_SEQ_NUM,'
2455 ||' x.ORIG_RESOURCE_SEQ_NUM,'
2456 ||' x.SERIAL_NUMBER,'
2457 ||' x.EQUIPMENT_ITEM_ID,'
2458 ||' x.WIP_ENTITY_ID,'
2459 ||' x.WIP_ENTITY_ID,'
2460 ||' x.START_DATE - :v_dgmt,'
2461 ||' x.COMPLETION_DATE - :v_dgmt,'
2462 ||' 2,'
2463 ||' :v_refresh_id,'
2464 ||' :v_instance_id'
2465 ||' from MRP_AP_RES_INSTANCE_REQS_V'||MSC_CL_PULL.v_dblink||' x'
2466 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2467 || lv_cond_sql
2468 || v_union_sql ;
2469
2470
2471 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, ' Ds debug: insert from MRP_AP_RES_INSTANCE_REQS_V sql = '||v_sql_stmt);
2472 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2473
2474 EXECUTE IMMEDIATE v_sql_stmt USING
2475 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2476 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2477 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn,
2478 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,MSC_CL_PULL.v_lrn;
2479
2480 ELSE
2481
2482 EXECUTE IMMEDIATE v_sql_stmt USING
2483 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2484 END IF;
2485
2486 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for Wip Job Res Instances='|| SQL%ROWCOUNT);
2487 COMMIT;
2488
2489 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RES_INSTANCES';
2490 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_RES_INSTANCES_V';
2491
2492
2493 v_sql_stmt:=
2494 'insert into MSC_ST_JOB_OP_RES_INSTANCES'
2495 ||' (WIP_ENTITY_ID,'
2496 ||' SR_INSTANCE_ID,'
2497 ||' ORGANIZATION_ID,'
2498 ||' OPERATION_SEQ_NUM,'
2499 ||' RESOURCE_SEQ_NUM,'
2500 ||' RESOURCE_ID,'
2501 ||' RES_INSTANCE_ID,'
2502 ||' DEPARTMENT_ID,'
2503 ||' SERIAL_NUMBER,'
2504 ||' EQUIPMENT_ITEM_ID,'
2505 ||' START_DATE,'
2506 ||' COMPLETION_DATE,'
2507 ||' BATCH_NUMBER,'
2508 ||' DELETED_FLAG,'
2509 ||' REFRESH_ID )'
2510 ||' select '
2511 ||' x.WIP_ENTITY_ID,'
2512 ||' :v_instance_id,'
2513 ||' x.ORGANIZATION_ID,'
2514 ||' x.OPERATION_SEQ_NUM,'
2515 ||' x.RESOURCE_SEQ_NUM,'
2516 ||' x.RESOURCE_ID,'
2517 ||' x.RES_INSTANCE_ID,'
2518 ||' x.DEPARTMENT_ID,'
2519 ||' x.SERIAL_NUMBER,'
2520 ||' x.EQUIPMENT_ITEM_ID,'
2521 ||' x.START_DATE - :v_dgmt,'
2522 ||' x.COMPLETION_DATE - :v_dgmt,'
2523 ||' x.BATCH_NUMBER,'
2524 ||' 2,'
2525 ||' :v_refresh_id'
2526 ||' FROM MRP_AP_JOB_RES_INSTANCES_V'||MSC_CL_PULL.v_dblink||' x'
2527 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2528 ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2529 ||' OR x.RN2>' || MSC_CL_PULL.v_lrn
2530 ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2531
2532 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: insert from MRP_AP_JOB_RES_INSTANCES_V job res_instance sql = '||v_sql_stmt);
2533 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for lJob Res Instances= '||SQL%ROWCOUNT);
2535
2536 COMMIT;
2537
2538
2539 END IF; /* WI__ENABLED and MSC_UTIL.G_APPS115 */
2540
2541 END LOAD_RESOURCE_INSTANCE;
2542
2543
2544 PROCEDURE LOAD_CO_PRODUCT_BOMS IS /*osfm change*/
2545
2546 l_co_prd_grp_id Number;
2547 l_bill_sequence_id Number;
2548 l_usage_rate Number;
2549 l_rowid rowid;
2550 l_total_count NUMBER;
2551 l_common_bill_seq_id Number;
2552 l_split Number;
2553 l_effectivity_date1 Date; -- BUG 14133049
2554 v_effectivity_date1 Date; -- (FP of SUN
2555 v_disable_date1 Date; -- bug 13530488)
2556 l_primary_flag Number;
2557 v_query_str varchar2(3000);
2558 v_query_str1 varchar2(6000);
2559 v_query_str2 varchar2(6000);
2560 v_query_str4 varchar2(6000);
2561 type cur_type is ref cursor;
2562 cur cur_type;
2563 cur1 cur_type;
2564 cur3 cur_type;
2565 v_bill_sequence_id number;
2566 v_co_product_id number;
2567 v_split number;
2568 v_component_type number;
2569 v_org_id number;
2570 v_comp_seq_id number;
2571 v_assembly_id number;
2572 v_primary_flag number;
2573
2574 v_wsm_split_table_qry varchar2(6000);
2575 v_split_table_exist PLS_INTEGER := 0;
2576
2577 v_wsm_schema VARCHAR2(32);
2578 lv_retval boolean;
2579 lv_dummy1 varchar2(32);
2580 lv_dummy2 varchar2(32);
2581 Begin
2582
2583 lv_retval := FND_INSTALLATION.GET_APP_INFO ('WSM', lv_dummy1, lv_dummy2,v_wsm_schema);
2584 -- adding this piece of code to ensure that WSM_COPRODUCT_SPLIT_PERC is used only when it exists
2585 v_wsm_split_table_qry := 'select count(*) from all_tables'||MSC_CL_PULL.v_dblink||
2586 ' where owner=:v_wsm_schema and table_name = ''WSM_COPRODUCT_SPLIT_PERC''';
2587
2588 EXECUTE IMMEDIATE v_wsm_split_table_qry into v_split_table_exist using v_wsm_schema;
2589
2590 IF nvl(v_split_table_exist,0) = 0 THEN
2591
2592 -- old behaviour
2593 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WSM_COPRODUCT_SPLIT_PERC does not exist. Using split from WSM_CO_PRODUCTS');
2594
2595 v_query_str := 'Select bill_sequence_id*2,co_product_id,split,' ||
2596 'effectivity_date, disable_date, decode(nvl(primary_flag,''N''),''N'',2,1) primary_flag' ||
2597 'from wsm_co_products'||MSC_CL_PULL.v_dblink|| -- Edited for BUG 14133049 (FP of SUN BUG 13530488)
2598 ' where bill_sequence_id <>:v_bill_seq_id and bill_sequence_id is not null
2599 and co_product_group_id = :v_co_prd_grp_id
2600 and perc.effectivity_date = :v_effectivity_date_var
2601 and split > 0';
2602
2603 v_query_str1 := 'select wsc.co_product_group_id,
2604 wsc.bill_Sequence_id*2,
2605 co_product_id,
2606 bom.common_bill_Sequence_id,
2607 wsc.usage_rate,
2608 wsc.split,
2609 perc.effectivity_date, -- Added for BUG 14133049/13530488
2610 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2611 bom.rowid
2612 from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2613 msc_st_boms bom
2614 where wsc.bill_sequence_id is not null
2615 and wsc.split > 0 -- Added this for bug:2208074
2616 and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id';
2617
2618 ELSE
2619 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WSM_COPRODUCT_SPLIT_PERC Exists');
2620
2621 v_query_str := 'Select wsc.bill_sequence_id*2,
2622 wsc.co_product_id,
2623 perc.split,
2624 perc.effectivity_date, -- Added for BUG 14133049/13530488
2625 perc.disable_date, -- Added for BUG 14133049/13530488
2626 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag
2627 from wsm_co_products'||MSC_CL_PULL.v_dblink|| ' wsc,
2628 wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc
2629 where wsc.bill_sequence_id <>:v_bill_seq_id
2630 and wsc.bill_sequence_id is not null
2631 and wsc.co_product_group_id = :v_co_prd_grp_id
2632 and perc.co_product_group_id = wsc.co_product_group_id
2633 and perc.co_product_id = wsc.co_product_id
2634 and sysdate < nvl(perc.disable_date,sysdate + 1)
2635 and perc.effectivity_date = :v_effectivity_date_var -- Added for BUG 14133049/13530488
2636 and perc.split > 0';
2637
2638 v_query_str1 := 'select wsc.co_product_group_id,
2639 wsc.bill_Sequence_id*2,
2640 wsc.co_product_id,
2641 bom.common_bill_Sequence_id,
2642 wsc.usage_rate,
2643 perc.split,
2644 perc.effectivity_date, -- Added for BUG 14133049/13530488
2645 decode(nvl(wsc.primary_flag,''N''),''N'',2,1) primary_flag,
2646 bom.rowid
2647 from wsm_Co_products'||MSC_CL_PULL.v_dblink||' wsc,
2648 wsm_coproduct_split_perc'||MSC_CL_PULL.v_dblink||' perc,
2649 msc_st_boms bom
2650 where wsc.bill_sequence_id is not null
2651 and wsc.bill_Sequence_id*2 = bom.bill_Sequence_id
2652 and perc.co_product_group_id = wsc.co_product_group_id
2653 and perc.co_product_id = wsc.co_product_id
2654 and sysdate < nvl(perc.disable_date,sysdate + 1)
2655 and perc.split>0';
2656
2657 END IF;
2658
2659 OPEN cur FOR v_query_str1;
2660
2661 LOOP
2662 FETCH cur INTO
2663 l_co_prd_grp_id,l_bill_sequence_id,v_assembly_id,l_common_bill_seq_id,l_usage_rate,l_split,
2664 l_effectivity_date1, l_primary_flag, l_rowid; -- Added for BUG 14133049/13530488
2665 EXIT WHEN cur%NOTFOUND;
2666
2667 OPEN cur1 FOR v_query_str USING l_bill_sequence_id/2,l_co_prd_grp_id,l_effectivity_date1; -- Added for BUG 14133049/13530488
2668
2669 LOOP
2670 FETCH cur1 INTO v_bill_sequence_id,v_co_product_id,v_split,v_effectivity_date1,v_disable_date1,v_primary_flag ;
2671 EXIT WHEN cur1%NOTFOUND; -- Added for BUG 14133049/13530488
2672
2673 /* set the primary_flag for the actual component to "not a primary" */
2674
2675 v_query_str4 :=
2676
2677 'update MSC_ST_BOM_COMPONENTS x
2678 set x.primary_flag = 2
2679 WHERE x.bill_Sequence_id='||l_BILL_SEQuence_ID ||
2680 ' and x.primary_flag is null
2681 and nvl(x.component_type,0) != 10
2682 and x.usage_quantity > 0
2683 and x.inventory_item_id in
2684 (select wsm.component_id from wsm_co_products' ||MSC_CL_PULL.v_dblink||' wsm
2685 where wsm.bill_sequence_id ='|| l_bill_sequence_id/2||')';
2686
2687 EXECUTE IMMEDIATE v_query_str4;
2688
2689
2690 insert into MSC_ST_BOM_COMPONENTS
2691 ( COMPONENT_SEQUENCE_ID,
2692 INVENTORY_ITEM_ID,
2693 BILL_SEQUENCE_ID,
2694 OPERATION_SEQ_NUM,
2695 COMPONENT_TYPE,
2696 USAGE_QUANTITY,
2697 COMPONENT_YIELD_FACTOR,
2698 EFFECTIVITY_DATE,
2699 DISABLE_DATE,
2700 OPERATION_OFFSET_PERCENT,
2701 OPTIONAL_COMPONENT,
2702 WIP_SUPPLY_TYPE,
2703 PLANNING_FACTOR,
2704 REVISED_ITEM_SEQUENCE_ID,
2705 ATP_FLAG,
2706 STATUS_TYPE,
2707 USE_UP_CODE,
2708 CHANGE_NOTICE,
2709 ORGANIZATION_ID,
2710 USING_ASSEMBLY_ID,
2711 FROM_UNIT_NUMBER,
2712 TO_UNIT_NUMBER,
2713 DRIVING_ITEM_ID,
2714 DELETED_FLAG,
2715 REFRESH_ID,
2716 SR_INSTANCE_ID,
2717 PRIMARY_FLAG,
2718 ROUNDING_DIRECTION)
2719 select
2720 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
2721 v_co_product_id,
2722 l_BILL_SEQuence_ID,
2723 x.OPERATION_SEQ_NUM,
2724 10,
2725 -(v_split/100),
2726 x.COMPONENT_YIELD_FACTOR,
2727 v_effectivity_date1, -- Added for BUG 14133049/13530488
2728 v_disable_date1,
2729 x.OPERATION_OFFSET_PERCENT,
2730 x.OPTIONAL_COMPONENT,
2731 decode(x.WIP_SUPPLY_TYPE,6,1,x.WIP_SUPPLY_TYPE), -- Bug 14133049 -FP of SUN bug 13459310
2732 x.PLANNING_FACTOR,
2733 x.REVISED_ITEM_SEQUENCE_ID,
2734 x.ATP_FLAG,
2735 x.STATUS_TYPE,
2736 x.USE_UP_CODE,
2737 x.CHANGE_NOTICE,
2738 x.ORGANIZATION_ID,
2739 v_assembly_id,
2740 x.FROM_UNIT_NUMBER,
2741 x.TO_UNIT_NUMBER,
2742 x.DRIVING_ITEM_ID,
2743 2,
2744 refresh_id,
2745 sr_instance_id,
2746 v_primary_flag,
2747 3
2748 from MSC_ST_BOM_COMPONENTS x
2749 WHERE x.bill_Sequence_id = l_BILL_SEQuence_ID
2750 and x.sr_instance_id = MSC_CL_PULL.v_instance_id
2751 and rownum = 1;
2752
2753 End loop;
2754
2755 update msc_st_boms
2756 set assembly_quantity = (l_split/100)
2757 where rowid = l_rowid;
2758
2759 ENd loop;
2760
2761 COMMIT;
2762
2763 END LOAD_CO_PRODUCT_BOMS; /*osfm change */
2764
2765
2766 --==================================================================
2767 /* ds change start change */
2768
2769 PROCEDURE LOAD_RESOURCE_SETUP IS
2770 BEGIN
2771
2772 IF (MSC_CL_PULL.BOM_ENABLED=MSC_UTIL.SYS_YES) THEN
2773 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
2774
2775 MSC_CL_PULL.v_table_name:= 'MSC_ST_RESOURCE_SETUPS';
2776 MSC_CL_PULL.v_view_name := 'MRP_AP_RESOURCE_SETUPS_V';
2777
2778 v_sql_stmt:=
2779 ' insert into MSC_ST_RESOURCE_SETUPS'
2780 ||' ( RESOURCE_ID,'
2781 ||' ORGANIZATION_ID,'
2782 --||' DEPARTMENT_ID,'
2783 ||' SETUP_ID,'
2784 ||' SETUP_CODE,'
2785 ||' SETUP_DESCRIPTION,'
2786 ||' DELETED_FLAG,'
2787 ||' REFRESH_ID,'
2788 ||' SR_INSTANCE_ID)'
2789 ||' select'
2790 ||' x.RESOURCE_ID,'
2791 ||' x.ORGANIZATION_ID,'
2792 --||' x.DEPARTMENT_ID,'
2793 ||' x.SETUP_ID,'
2794 ||' x.SETUP_CODE,'
2795 ||' x.SETUP_DESCRIPTION,'
2796 ||' 2,'
2797 ||' :v_refresh_id,'
2798 ||' :v_instance_id'
2799 ||' from MRP_AP_RESOURCE_SETUPS_V'||MSC_CL_PULL.v_dblink||' x'
2800 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2801
2802 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup = '||v_sql_stmt);
2803 EXECUTE IMMEDIATE v_sql_stmt
2804 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2805 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_RESOURCE_SETUPS_V = '|| SQL%ROWCOUNT);
2806
2807 COMMIT;
2808
2809 MSC_CL_PULL.v_table_name:= 'MSC_ST_SETUP_TRANSITIONS';
2810 MSC_CL_PULL.v_view_name := 'MRP_AP_SETUP_TRANSITIONS_V';
2811
2812 v_sql_stmt:=
2813 ' insert into MSC_ST_SETUP_TRANSITIONS'
2814 ||' ( RESOURCE_ID,'
2815 ||' ORGANIZATION_ID,'
2816 ||' FROM_SETUP_ID,'
2817 ||' TO_SETUP_ID,'
2818 ||' STANDARD_OPERATION_ID,'
2819 ||' TRANSITION_TIME,'
2820 ||' TRANSITION_UOM,'
2821 ||' TRANSITION_PENALTY,'
2822 ||' DELETED_FLAG,'
2823 ||' REFRESH_ID,'
2824 ||' SR_INSTANCE_ID)'
2825 ||' select'
2826 ||' x.RESOURCE_ID,'
2827 ||' x.ORGANIZATION_ID,'
2828 ||' x.FROM_SETUP_ID,'
2829 ||' x.TO_SETUP_ID,'
2830 ||' x.STANDARD_OPERATION_ID,'
2831 ||' x.TRANSITION_TIME,'
2832 ||' x.TRANSITION_UOM,'
2833 ||' x.TRANSITION_PENALTY,'
2834 ||' 2,'
2835 ||' :v_refresh_id,'
2836 ||' :v_instance_id'
2837 ||' from MRP_AP_SETUP_TRANSITIONS_V'||MSC_CL_PULL.v_dblink||' x'
2838 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2839
2840 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap resource setup transition = '||v_sql_stmt);
2841 EXECUTE IMMEDIATE v_sql_stmt
2842 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2843 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_SETUP_TRANSITIONS_V = '|| SQL%ROWCOUNT);
2844
2845 COMMIT;
2846
2847 MSC_CL_PULL.v_table_name:= 'MSC_ST_STD_OP_RESOURCES';
2848 MSC_CL_PULL.v_view_name := 'MRP_AP_STD_OP_RESOURCES_V';
2849
2850 v_sql_stmt:=
2851 ' insert into MSC_ST_STD_OP_RESOURCES'
2852 ||' ( STANDARD_OPERATION_ID,'
2853 ||' RESOURCE_ID,'
2854 ||' ORGANIZATION_ID,'
2855 ||' DEPARTMENT_ID,'
2856 ||' OPERATION_CODE,'
2857 ||' RESOURCE_SEQ_NUM,'
2858 ||' RESOURCE_USAGE,'
2859 ||' BASIS_TYPE,'
2860 ||' RESOURCE_UNITS,'
2861 ||' SUBSTITUTE_GROUP_NUM,'
2862 ||' UOM_CODE,'
2863 ||' SCHEDULE_FLAG,'
2864 ||' DELETED_FLAG,'
2865 ||' REFRESH_ID,'
2866 ||' SR_INSTANCE_ID)'
2867 ||' select'
2868 ||' x.STANDARD_OPERATION_ID,'
2869 ||' x.RESOURCE_ID,'
2870 ||' x.ORGANIZATION_ID,'
2871 ||' x.DEPARTMENT_ID,'
2872 ||' x.OPERATION_CODE,'
2873 ||' x.RESOURCE_SEQ_NUM,'
2874 ||' x.RESOURCE_USAGE,'
2875 ||' x.BASIS_TYPE,'
2876 ||' x.RESOURCE_UNITS,'
2877 ||' x.SUBSTITUTE_GROUP_NUM,'
2878 ||' x.UOM_CODE,'
2879 ||' x.SCHEDULE_FLAG,'
2880 ||' 2,'
2881 ||' :v_refresh_id,'
2882 ||' :v_instance_id'
2883 ||' from MRP_AP_STD_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2884 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str;
2885
2886 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap std op resources = '||v_sql_stmt);
2887 EXECUTE IMMEDIATE v_sql_stmt
2888 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2889 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_STD_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2890
2891 COMMIT;
2892
2893 END IF; /* MSC_UTIL.G_APPS115 */
2894
2895 END IF; /* MSC_CL_PULL.BOM_ENABLED */
2896 END LOAD_RESOURCE_SETUP;
2897
2898
2899 END MSC_CL_BOM_PULL;