[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_WIP_PULL
Source
1 PACKAGE BODY MSC_CL_WIP_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(32767);
10 v_temp_sql1 VARCHAR2(32767);
11 v_temp_sql2 VARCHAR2(32767);
12 v_temp_sql3 VARCHAR2(32767);
13 v_temp_sql4 VARCHAR2(32767);
14 v_temp_sql5 VARCHAR2(32767);
15 v_temp_sql6 VARCHAR2(32767);
16 v_temp_sql7 VARCHAR2(32767);
17 -- NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
18 -- NULL_DBLINK CONSTANT VARCHAR2(1) :=MSC_UTIL.NULL_DBLINK;
19
20 v_once_per_org varchar2(10) := 'NOTDONE';
21
22 -----OSFM---------
23
24 PROCEDURE LOAD_OPER_NETWORKS IS
25 v_get_apps_ver number;
26 BEGIN
27
28 IF MSC_CL_PULL.BOM_ENABLED= MSC_UTIL.SYS_YES THEN
29
30 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
31
32
33 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPER_NETWORKS';
34 MSC_CL_PULL.v_view_name := 'MRP_AD_OPER_NETWORKS_V';
35
36 if v_once_per_org <> 'DONE'
37 then
38
39
40 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
41
42 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
43 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
44 ELSE
45 v_temp_sql := NULL;
46 END IF;
47
48 v_sql_stmt:=
49 ' INSERT INTO MSC_ST_OPERATION_NETWORKS'
50 ||'( FROM_OP_SEQ_ID, '
51 ||' TO_OP_SEQ_ID, '
52 ||' DELETED_FLAG, '
53 ||' REFRESH_ID, '
54 ||' SR_INSTANCE_ID) '
55 ||' SELECT '
56 ||' x.FROM_OP_SEQ_ID,'
57 ||' x.TO_OP_SEQ_ID, '
58 ||' 1,'
59 ||' :v_refresh_id,'
60 ||' :v_instance_id'
61 ||' FROM MRP_AD_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
62 ||' WHERE x.RN>'||MSC_CL_PULL.v_lrn
63 || v_temp_sql;
64
65 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
66
67 COMMIT;
68
69 END IF; /* MSC_CL_PULL.v_lrnn<> -1 */
70
71 MSC_CL_PULL.v_table_name:= 'MSC_ST_OPERATION_NETWORKS';
72 MSC_CL_PULL.v_view_name := 'MRP_AP_OPER_NETWORKS_V';
73
74 v_sql_stmt:= ' insert into MSC_ST_OPERATION_NETWORKS'
75 ||'( FROM_OP_SEQ_ID, '
76 ||'TO_OP_SEQ_ID, '
77 ||'ROUTING_SEQUENCE_ID, '
78 ||'ORGANIZATION_ID, '
79 ||'TRANSITION_TYPE, '
80 ||'PLANNING_PCT, '
81 ||'CUMMULATIVE_PCT, '
82 ||'EFECTIVITY_DATE, '
83 ||'DISABLE_DATE, '
84 ||'PLAN_ID, '
85 ||'DEPENDENCY_TYPE, '
86 ||'CREATED_BY, '
87 ||'CREATION_DATE, '
88 ||'DELETED_FLAG, '
89 ||'LAST_UPDATED_BY, '
90 ||'LAST_UPDATE_DATE, '
91 ||'LAST_UPDATE_LOGIN, '
92 ||'ATTRIBUTE_CATEGORY, '
93 ||'ATTRIBUTE1, '
94 ||'ATTRIBUTE2, '
95 ||'ATTRIBUTE3, '
96 ||'ATTRIBUTE4, '
97 ||'ATTRIBUTE5, '
98 ||'ATTRIBUTE6, '
99 ||'ATTRIBUTE7, '
100 ||'ATTRIBUTE8, '
101 ||'ATTRIBUTE9, '
102 ||'ATTRIBUTE10, '
103 ||'ATTRIBUTE11, '
104 ||'ATTRIBUTE12, '
105 ||'ATTRIBUTE13, '
106 ||'ATTRIBUTE14, '
107 ||'ATTRIBUTE15, '
108 ||'FROM_OP_SEQ_NUM,'
109 ||'TO_OP_SEQ_NUM,'
110 ||'REFRESH_ID, '
111 ||'SR_INSTANCE_ID) '
112 ||' select '
113 ||' x.FROM_OP_SEQ_ID, '
114 ||' x.TO_OP_SEQ_ID, '
115 ||' x.routing_sequence_id, '
116 ||' x.ORGANIZATION_ID, '
117 ||' x.TRANSITION_TYPE, '
118 ||' x.PLANNING_PCT, '
119 ||' x.CUMMULATIVE_PCT, '
120 ||' x.EFFECTIVITY_DATE, '
121 ||' x.DISABLE_DATE, '
122 ||' -1, '
123 ||' to_number(null), ' /* ds change: dependency_type = null=> prior-next */
124 ||' x.CREATED_BY, '
125 ||' x.CREATION_DATE, '
126 ||' 2, '
127 ||' x.LAST_UPDATED_BY, '
128 ||' x.LAST_UPDATE_DATE, '
129 ||' x.LAST_UPDATE_LOGIN, '
130 ||' x.ATTRIBUTE_CATEGORY, '
131 ||' x.ATTRIBUTE1, '
132 ||' x.ATTRIBUTE2, '
133 ||' x.ATTRIBUTE3, '
134 ||' x.ATTRIBUTE4, '
135 ||' x.ATTRIBUTE5, '
136 ||' x.ATTRIBUTE6, '
137 ||' x.ATTRIBUTE7, '
138 ||' x.ATTRIBUTE8, '
139 ||' x.ATTRIBUTE9, '
140 ||' x.ATTRIBUTE10, '
141 ||' x.ATTRIBUTE11, '
142 ||' x.ATTRIBUTE12, '
143 ||' x.ATTRIBUTE13, '
144 ||' x.ATTRIBUTE14, '
145 ||' x.ATTRIBUTE15, '
146 ||' x.FROM_SEQ_NUM,'
147 ||' x.TO_SEQ_NUM, '
148 ||' :v_refresh_id,'
149 ||' :v_instance_id '
150 ||' from MRP_AP_OPER_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
151 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
152 ||' AND (x.RN1>'||MSC_CL_PULL.v_lrn
153 ||' OR x.RN2>'||MSC_CL_PULL.v_lrn
154 ||' OR x.RN3>'||MSC_CL_PULL.v_lrn
155 ||' OR x.RN4>'||MSC_CL_PULL.v_lrn
156 ||' OR x.RN5>'||MSC_CL_PULL.v_lrn
157 ||' OR x.RN6>'||MSC_CL_PULL.v_lrn||')' ;
158
159 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
160
161 v_once_per_org := 'DONE';
162 -- opm populates operation network in call to
163 -- extract_effectivities
164
165 End if; /* v_once_per_org */
166
167 End if; /* MSC_UTIL.G_APPS115 */
168 END IF; -- MSC_CL_PULL.BOM_ENABLED
169
170 END LOAD_OPER_NETWORKS;
171
172
173
174 PROCEDURE LOAD_WIP_DEMAND IS
175 lv_cond_sql VARCHAR2(100) := null;
176 lv_op_seq_num varchar2(100) := null;
177 lv_lbj_details NUMBER:=0;
178 lv_new_view_name varchar2(1000) := null;
179 lv_new_org_string varchar2(32767) := null;
180 v_temp_sql_stmt varchar2(32767) := null;
181 v_temp_sql varchar2(32767) := null;
182 v_temp_sql_stmt2 varchar2(32767) := null;
183 v_temp_sql2 varchar2(32767) := null;
184 BEGIN
185
186 IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
187
188 --=================== Net Change Mode: Delete ==================
189
190 lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
191 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
192
193
194 select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
195 where instance_id = MSC_CL_PULL.v_instance_id ;
196
197 if lv_lbj_details = 1 Then
198 lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
199 else
200 lv_cond_sql := ' AND x.OPERATION_SEQ_NUM <> -1 ';
201 end if;
202
203 END IF;
204
205
206 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
207
208 IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
209
210 -- For Demands from non depo orgs
211
212 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
213 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_COMP_DEM_V';
214
215 v_sql_stmt:=
216 'insert into MSC_ST_DEMANDS'
217 ||' ( WIP_ENTITY_ID,'
218 ||' OPERATION_SEQ_NUM,'
219 ||' INVENTORY_ITEM_ID,'
220 ||' ORGANIZATION_ID,'
221 ||' ORIGINATION_TYPE,'
222 ||' DELETED_FLAG,'
223 ||' REFRESH_ID,'
224 ||' SR_INSTANCE_ID)'
225 ||' select'
226 ||' x.WIP_ENTITY_ID,'
227 || lv_op_seq_num
228 ||' x.INVENTORY_ITEM_ID,'
229 ||' x.ORGANIZATION_ID,'
230 ||' x.ORIGINATION_TYPE,'
231 ||' 1,'
232 ||' :v_refresh_id,'
233 ||' :v_instance_id'
234 ||' from MRP_AD_NON_ERO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
235 ||' where ( DECODE( :v_mps_consume_profile_value,'
236 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
237 ||' x.WJS_NET_QTY_FLAG)=1'
238 ||' OR x.MRP_NET_FLAG= 1'
239 ||' OR DECODE( :v_mps_consume_profile_value,'
240 ||' 1, x.MPS_FLAG,'
241 ||' x.NMPS_FLAG)= 1 )'
242 ||' AND x.RN> :v_lrn '
243 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str ;
244
245 -- For Demands from depot repair orgs
246
247 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
248 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_COMP_DEM_V';
249
250 v_temp_sql:=
251 'insert into MSC_ST_DEMANDS'
252 ||' ( WIP_ENTITY_ID,'
253 ||' OPERATION_SEQ_NUM,'
254 ||' INVENTORY_ITEM_ID,'
255 ||' ORGANIZATION_ID,'
256 ||' ORIGINATION_TYPE,'
257 ||' DELETED_FLAG,'
258 ||' REFRESH_ID,'
259 ||' SR_INSTANCE_ID)'
260 ||' select'
261 ||' x.WIP_ENTITY_ID,'
262 || lv_op_seq_num
263 ||' x.INVENTORY_ITEM_ID,'
264 ||' x.ORGANIZATION_ID,'
265 ||' x.ORIGINATION_TYPE,'
266 ||' 1,'
267 ||' :v_refresh_id,'
268 ||' :v_instance_id'
269 ||' from MRP_AD_NON_RO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
270 ||' where ( DECODE( :v_mps_consume_profile_value,'
271 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
272 ||' x.WJS_NET_QTY_FLAG)=1'
273 ||' OR x.MRP_NET_FLAG= 1'
274 ||' OR DECODE( :v_mps_consume_profile_value,'
275 ||' 1, x.MPS_FLAG,'
276 ||' x.NMPS_FLAG)= 1 )'
277 ||' AND x.RN> :v_lrn '
278 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str ;
279
280 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
281 MSC_CL_PULL.v_instance_id,
282 MSC_CL_PULL.v_mps_consume_profile_value,
283 MSC_CL_PULL.v_mps_consume_profile_value,
284 MSC_CL_PULL.v_lrn;
285
286 EXECUTE IMMEDIATE v_temp_sql USING MSC_CL_PULL.v_refresh_id,
287 MSC_CL_PULL.v_instance_id,
288 MSC_CL_PULL.v_mps_consume_profile_value,
289 MSC_CL_PULL.v_mps_consume_profile_value,
290 MSC_CL_PULL.v_lrn;
291
292 COMMIT;
293
294 ELSE -- If srp profile is No
295
296 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
297 MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_DEMANDS_V';
298
299 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
300 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
301 ELSE
302 v_temp_sql := NULL;
303 END IF;
304
305 v_sql_stmt:=
306 'insert into MSC_ST_DEMANDS'
307 ||' ( WIP_ENTITY_ID,'
308 ||' OPERATION_SEQ_NUM,'
309 ||' INVENTORY_ITEM_ID,'
310 ||' ORGANIZATION_ID,'
311 ||' ORIGINATION_TYPE,'
312 ||' DELETED_FLAG,'
313 ||' REFRESH_ID,'
314 ||' SR_INSTANCE_ID)'
315 ||' select'
316 ||' x.WIP_ENTITY_ID,'
317 || lv_op_seq_num
318 ||' x.INVENTORY_ITEM_ID,'
319 ||' x.ORGANIZATION_ID,'
320 ||' x.ORIGINATION_TYPE,'
321 ||' 1,'
322 ||' :v_refresh_id,'
323 ||' :v_instance_id'
324 ||' from MRP_AD_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
325 ||' where ( DECODE( :v_mps_consume_profile_value,'
326 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
327 ||' x.WJS_NET_QTY_FLAG)=1'
328 ||' OR x.MRP_NET_FLAG= 1'
329 ||' OR DECODE( :v_mps_consume_profile_value,'
330 ||' 1, x.MPS_FLAG,'
331 ||' x.NMPS_FLAG)= 1 )'
332 ||' AND x.RN> :v_lrn '
333 || v_temp_sql;
334
335 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
336 MSC_CL_PULL.v_instance_id,
337 MSC_CL_PULL.v_mps_consume_profile_value,
338 MSC_CL_PULL.v_mps_consume_profile_value,
339 MSC_CL_PULL.v_lrn;
340
341 COMMIT;
342 END IF;
343 END IF;
344
345
346 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
347
348 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
349 MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_FLOW_DEMANDS_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_DEMANDS'
359 ||' ( WIP_ENTITY_ID,'
360 ||' ORIGINATION_TYPE,'
361 ||' ORGANIZATION_ID,'
362 ||' DELETED_FLAG,'
363 ||' REFRESH_ID,'
364 ||' SR_INSTANCE_ID)'
365 ||' select distinct'
366 ||' x.WIP_ENTITY_ID,'
367 ||' x.ORIGINATION_TYPE,'
368 ||' x.ORGANIZATION_ID,'
369 ||' 1,'
370 ||' :v_refresh_id,'
371 ||' :v_instance_id'
372 ||' from MRP_AD_WIP_FLOW_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
373 ||' where x.RN> :v_lrn '
374 || v_temp_sql;
375
376 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
377 MSC_CL_PULL.v_instance_id,
378 MSC_CL_PULL.v_lrn;
379
380 COMMIT;
381
382 END IF;
383
384 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
385 MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
386
387 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
388 v_temp_sql := 'x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '; /* ds change */
389
390 ELSE
391
392 v_temp_sql := ' NULL, NULL, NULL, NULL, NULL, '; /* ds change */
393 END IF;
394
395 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
396
397 -- BUG 3036681
398 -- No need to check on RN3 (on wip_discrete_jobs) as the
399 -- materialized view on wip_requirement_operations now has
400 -- the columns from wip_discrete_jobs too.
401
402 v_union_sql :=
403 ' AND ( x.RN1> :v_lrn)' -- NCP: changed to RN2
404
405 /* NCP: don't need union below
406 ||' UNION '
407 ||' select'
408 ||' x.INVENTORY_ITEM_ID,'
409 ||' x.ORGANIZATION_ID,'
410 ||' x.WIP_ENTITY_ID,'
411 ||' x.WIP_ENTITY_NAME,'
412 ||' x.STATUS_CODE,'
416 ||' x.QUANTITY_ISSUED,'
413 ||' x.WIP_SUPPLY_TYPE,'
414 || lv_op_seq_num
415 ||' x.NEW_REQUIRED_QUANTITY,' -- Bug fix
417 ||' x.JOB_REFERENCE_ITEM_ID,'
418 ||' x.DEMAND_TYPE,'
419 ||' x.PROJECT_ID,'
420 ||' x.TASK_ID,'
421 ||' x.PLANNING_GROUP,'
422 ||' x.END_ITEM_UNIT_NUMBER,'
423 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
424 ||' x.DEMAND_CLASS,'
425 ||' x.ORIGINATION_TYPE,'
426 ||' x.DATE_REQUIRED- :v_dgmt,'
427 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
428 ||' 2,'
429 ||' :v_refresh_id,'
430 ||' :v_instance_id'
431 ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
432 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
433 ||' AND ( x.RN2>'||MSC_CL_PULL.v_lrn||')'
434 */
435 ||' UNION '
436 ||' select'
437 ||' x.INVENTORY_ITEM_ID,'
438 ||' x.INVENTORY_ITEM_ID,'
439 ||' x.ORGANIZATION_ID,'
440 ||' x.WIP_ENTITY_ID,'
441 ||' x.WIP_ENTITY_ID,'
442 ||' x.WIP_ENTITY_NAME, '
443 ||' x.STATUS_CODE,'
444 ||' x.WIP_SUPPLY_TYPE,'
445 || lv_op_seq_num
446 ||' x.NEW_REQUIRED_QUANTITY,' -- Bug fix
447 ||' x.QUANTITY_ISSUED,'
448 ||' x.JOB_REFERENCE_ITEM_ID,'
449 ||' x.DEMAND_TYPE,'
450 ||' x.PROJECT_ID,'
451 ||' x.TASK_ID,'
452 ||' x.PLANNING_GROUP,'
453 ||' x.END_ITEM_UNIT_NUMBER,'
454 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
455 ||' x.DEMAND_CLASS,'
456 ||' x.ORIGINATION_TYPE,'
457 ||' x.DATE_REQUIRED- :v_dgmt,'
458 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
459 ||' 2,'
460 || v_temp_sql
461 ||' :v_refresh_id,'
462 ||' :v_instance_id'
463 ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
464 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
465 ||' AND x.NEW_REQUIRED_QUANTITY > 0'
466 --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
467 --||' 2 ,x.coll_completed_qty_ind,'
468 --||' 1) >0'
469 ||' AND DECODE(:v_collect_completed_jobs,'
470 ||' 2,x.status_code,'
471 ||' 1) <>4' -- 5730031
472 ||' AND ( x.RN2> :v_lrn )'
473 ||' UNION '
474 ||' select'
475 ||' x.INVENTORY_ITEM_ID,'
476 ||' x.INVENTORY_ITEM_ID,'
477 ||' x.ORGANIZATION_ID,'
478 ||' x.WIP_ENTITY_ID,'
479 ||' x.WIP_ENTITY_ID,'
480 ||' x.WIP_ENTITY_NAME, '
481 ||' x.STATUS_CODE,'
482 ||' x.WIP_SUPPLY_TYPE,'
483 || lv_op_seq_num
484 ||' x.NEW_REQUIRED_QUANTITY,' -- Bug fix
485 ||' x.QUANTITY_ISSUED,'
486 ||' x.JOB_REFERENCE_ITEM_ID,'
487 ||' x.DEMAND_TYPE,'
488 ||' x.PROJECT_ID,'
489 ||' x.TASK_ID,'
490 ||' x.PLANNING_GROUP,'
491 ||' x.END_ITEM_UNIT_NUMBER,'
492 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
493 ||' x.DEMAND_CLASS,'
494 ||' x.ORIGINATION_TYPE,'
495 ||' x.DATE_REQUIRED- :v_dgmt,'
496 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
497 ||' 2,'
498 || v_temp_sql
499 ||' :v_refresh_id,'
500 ||' :v_instance_id'
501 ||' from MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
502 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
503 ||' AND x.NEW_REQUIRED_QUANTITY > 0'
504 --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
505 --||' 2 ,x.coll_completed_qty_ind,'
506 --||' 1) >0'
507 ||' AND DECODE(:v_collect_completed_jobs,'
508 ||' 2,x.status_code,'
509 ||' 1) <>4' -- 5730031
510 ||' AND ( x.RN3> :v_lrn )';
511 ELSE
512 v_union_sql := ' ';
513 END IF;
514
515 v_sql_stmt:=
516 'insert into MSC_ST_DEMANDS'
517 ||' ( INVENTORY_ITEM_ID,'
518 ||' SOURCE_INVENTORY_ITEM_ID,'
519 ||' ORGANIZATION_ID,'
520 ||' WIP_ENTITY_ID,'
521 ||' SOURCE_WIP_ENTITY_ID,'
522 ||' ORDER_NUMBER,' -- changes
523 ||' WIP_STATUS_CODE,'
524 ||' WIP_SUPPLY_TYPE,'
525 ||' OPERATION_SEQ_NUM,'
526 ||' USING_REQUIREMENT_QUANTITY,'
527 ||' QUANTITY_ISSUED,'
528 ||' USING_ASSEMBLY_ITEM_ID,'
529 ||' DEMAND_TYPE,'
530 ||' PROJECT_ID,'
531 ||' TASK_ID,'
532 ||' PLANNING_GROUP,'
533 ||' END_ITEM_UNIT_NUMBER,'
534 ||' DEMAND_CLASS,'
535 ||' ORIGINATION_TYPE,'
536 ||' USING_ASSEMBLY_DEMAND_DATE,'
537 ||' MPS_DATE_REQUIRED,'
538 ||' DELETED_FLAG,'
539 ||' QUANTITY_PER_ASSEMBLY,'
540 ||' ASSET_ITEM_ID,' /* ds change */
541 ||' ASSET_SERIAL_NUMBER,' /* ds change */
542 ||' COMPONENT_SCALING_TYPE,'
543 ||' COMPONENT_YIELD_FACTOR,'
544 ||' REFRESH_ID,'
545 ||' SR_INSTANCE_ID)'
546 ||' select'
547 ||' x.INVENTORY_ITEM_ID,'
548 ||' x.INVENTORY_ITEM_ID,'
549 ||' x.ORGANIZATION_ID,'
550 ||' x.WIP_ENTITY_ID,'
551 ||' x.WIP_ENTITY_ID,'
552 ||' x.WIP_ENTITY_NAME, '
553 ||' x.STATUS_CODE,'
554 ||' x.WIP_SUPPLY_TYPE,'
555 || lv_op_seq_num
556 ||' x.NEW_REQUIRED_QUANTITY,' -- Bug fix
557 ||' x.QUANTITY_ISSUED,'
558 ||' x.JOB_REFERENCE_ITEM_ID,'
559 ||' x.DEMAND_TYPE,'
560 ||' x.PROJECT_ID,'
561 ||' x.TASK_ID,'
562 ||' x.PLANNING_GROUP,'
563 ||' x.END_ITEM_UNIT_NUMBER,'
564 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
565 ||' x.DEMAND_CLASS,'
566 ||' x.ORIGINATION_TYPE,'
567 ||' x.DATE_REQUIRED- :v_dgmt,'
568 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
569 ||' 2,'
570 || v_temp_sql
571 ||' :v_refresh_id,'
572 ||' :v_instance_id'
573 ||' from ';
574
575 v_temp_sql2 := v_sql_stmt;
576
577 v_temp_sql := ' AND DECODE(:v_collect_completed_jobs,'
578 ||' 2,x.status_code,'
582 --||' 1) >0'
579 ||' 1) <>4' -- 5730031
580 --' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
581 --||' 2,x.coll_completed_qty_ind,'
583 || lv_cond_sql
584 ||' AND x.NEW_REQUIRED_QUANTITY > 0'
585 || v_union_sql ;
586
587
588 if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
589
590 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
591 lv_new_view_name := 'MRP_AP_WIP_COMP_DEMANDS_V';
592 lv_new_org_string := MSC_UTIL.v_in_org_str;
593
594 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
595 ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
596 else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
597
598
599 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
600 lv_new_view_name := 'MRP_AP_NON_ERO_WIP_DEMAND_V';
601 lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
602
603 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
604 ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
605 ---- Code for SRP when Repair Orders Entity is/not selected . Building v_sql_stmt
606 end if;
607 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
608
609
610 EXECUTE IMMEDIATE v_temp_sql_stmt
611 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
612 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
613 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_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
614
615 /* NCP:
616 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
617 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
618 */
619
620 ELSE
621
622 if (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) Then
623 Begin
624
625 EXECUTE IMMEDIATE v_temp_sql_stmt
626 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_COLLECT_COMPLETED_JOBS;
627
628
629 v_temp_sql_stmt := NULL;
630 MSC_CL_PULL.v_table_name := 'MSC_ST_DEMANDS';
631 lv_new_view_name := 'MRP_AP_NON_RO_WIP_DEMAND_V';
632 lv_new_org_string := MSC_UTIL.v_depot_org_str;
633
634 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink||' x'
635 ||' WHERE x.ORGANIZATION_ID '||lv_new_org_string||v_temp_sql;
636
637
638 EXECUTE IMMEDIATE v_temp_sql_stmt
639 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_COLLECT_COMPLETED_JOBS;
640
641
642 end; /* Code for SRP To get the work orders not attached to repair orders: bug 5909379 */
643 ELSE
644 EXECUTE IMMEDIATE v_temp_sql_stmt
645 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_COLLECT_COMPLETED_JOBS;
646
647 end if; -- For SRP and MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120
648 END IF;
649
650 COMMIT;
651
652
653 --=================== REPT ITEM DEMAND ==================
654 --=================== Net Change Mode: Delete ==================
655
656 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
657
658 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
659 MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_DEMANDS_V';
660
661 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
662 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
663 ELSE
664 v_temp_sql := NULL;
665 END IF;
666
667 v_sql_stmt:=
668 ' insert into MSC_ST_DEMANDS'
669 ||' ( INVENTORY_ITEM_ID,'
670 ||' WIP_ENTITY_ID,'
671 ||' OPERATION_SEQ_NUM,'
672 ||' REPETITIVE_SCHEDULE_ID,'
673 ||' ORGANIZATION_ID,'
674 ||' ORIGINATION_TYPE,'
675 ||' DELETED_FLAG,'
676 ||' REFRESH_ID,'
677 ||' SR_INSTANCE_ID)'
678 ||' select'
679 ||' x.INVENTORY_ITEM_ID,'
680 ||' x.WIP_ENTITY_ID,'
681 ||' x.OPERATION_SEQ_NUM,'
682 ||' x.REPETITIVE_SCHEDULE_ID,'
683 ||' x.ORGANIZATION_ID,'
684 ||' x.ORIGINATION_TYPE,'
685 ||' 1,'
686 ||' :v_refresh_id,'
687 ||' :v_instance_id'
688 ||' from MRP_AD_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
689 ||' WHERE x.RN> :v_lrn '
690 || v_temp_sql;
691
692 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
693
694 COMMIT;
695
696 END IF;
697
698
699 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
700 MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_DEMANDS_V';
701
702 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
703
704 v_union_sql :=
705 ' AND ( x.RN1 > :v_lrn )'
706 ||' UNION '
707 ||' select'
708 ||' x.INVENTORY_ITEM_ID,'
709 ||' x.ORGANIZATION_ID,'
710 ||' x.WIP_ENTITY_ID,'
711 ||' x.OPERATION_SEQ_NUM,'
712 ||' x.REPETITIVE_SCHEDULE_ID,'
713 ||' x.WIP_ENTITY_NAME, '
714 ||' x.REQUIRED_QUANTITY,'
715 ||' x.DATE_REQUIRED,'
716 ||' x.QUANTITY_ISSUED,'
717 ||' x.JOB_REFERENCE_ITEM_ID,'
721 ||' x.STATUS_CODE,'
718 ||' x.WIP_ENTITY_TYPE,'
719 ||' x.DEMAND_CLASS,'
720 ||' x.ORIGINATION_TYPE,'
722 ||' x.WIP_SUPPLY_TYPE,'
723 ||' 2,'
724 ||' :v_refresh_id,'
725 ||' :v_instance_id'
726 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
727 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
728 ||' AND ( x.RN2 > :v_lrn )'
729 ||' UNION '
730 ||' select'
731 ||' x.INVENTORY_ITEM_ID,'
732 ||' x.ORGANIZATION_ID,'
733 ||' x.WIP_ENTITY_ID,'
734 ||' x.OPERATION_SEQ_NUM,'
735 ||' x.REPETITIVE_SCHEDULE_ID,'
736 ||' x.WIP_ENTITY_NAME, '
737 ||' x.REQUIRED_QUANTITY,'
738 ||' x.DATE_REQUIRED,'
739 ||' x.QUANTITY_ISSUED,'
740 ||' x.JOB_REFERENCE_ITEM_ID,'
741 ||' x.WIP_ENTITY_TYPE,'
742 ||' x.DEMAND_CLASS,'
743 ||' x.ORIGINATION_TYPE,'
744 ||' x.STATUS_CODE,'
745 ||' x.WIP_SUPPLY_TYPE,'
746 ||' 2,'
747 ||' :v_refresh_id,'
748 ||' :v_instance_id'
749 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
750 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
751 ||' AND ( x.RN3 > :v_lrn )'
752 ||' UNION '
753 ||' select'
754 ||' x.INVENTORY_ITEM_ID,'
755 ||' x.ORGANIZATION_ID,'
756 ||' x.WIP_ENTITY_ID,'
757 ||' x.OPERATION_SEQ_NUM,'
758 ||' x.REPETITIVE_SCHEDULE_ID,'
759 ||' x.WIP_ENTITY_NAME, '
760 ||' x.REQUIRED_QUANTITY,'
761 ||' x.DATE_REQUIRED,'
762 ||' x.QUANTITY_ISSUED,'
763 ||' x.JOB_REFERENCE_ITEM_ID,'
764 ||' x.WIP_ENTITY_TYPE,'
765 ||' x.DEMAND_CLASS,'
766 ||' x.ORIGINATION_TYPE,'
767 ||' x.STATUS_CODE,'
768 ||' x.WIP_SUPPLY_TYPE,'
769 ||' 2,'
770 ||' :v_refresh_id,'
771 ||' :v_instance_id'
772 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
773 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
774 ||' AND ( x.RN4 > :v_lrn )'
775 ||' UNION '
776 ||' select'
777 ||' x.INVENTORY_ITEM_ID,'
778 ||' x.ORGANIZATION_ID,'
779 ||' x.WIP_ENTITY_ID,'
780 ||' x.OPERATION_SEQ_NUM,'
781 ||' x.REPETITIVE_SCHEDULE_ID,'
782 ||' x.WIP_ENTITY_NAME, '
783 ||' x.REQUIRED_QUANTITY,'
784 ||' x.DATE_REQUIRED,'
785 ||' x.QUANTITY_ISSUED,'
786 ||' x.JOB_REFERENCE_ITEM_ID,'
787 ||' x.WIP_ENTITY_TYPE,'
788 ||' x.DEMAND_CLASS,'
789 ||' x.ORIGINATION_TYPE,'
790 ||' x.STATUS_CODE,'
791 ||' x.WIP_SUPPLY_TYPE,'
792 ||' 2,'
793 ||' :v_refresh_id,'
794 ||' :v_instance_id'
795 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
796 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
797 ||' AND ( x.RN5 > :v_lrn )';
798
799 ELSE
800
801 v_union_sql := ' ';
802
803 END IF;
804
805
806 v_sql_stmt:=
807 ' insert into MSC_ST_DEMANDS'
808 ||' ( INVENTORY_ITEM_ID,'
809 ||' ORGANIZATION_ID,'
810 ||' WIP_ENTITY_ID,'
811 ||' OPERATION_SEQ_NUM,'
812 ||' REPETITIVE_SCHEDULE_ID,'
813 ||' ORDER_NUMBER,'
814 ||' USING_REQUIREMENT_QUANTITY,'
815 ||' USING_ASSEMBLY_DEMAND_DATE,'
816 ||' QUANTITY_ISSUED,'
817 ||' USING_ASSEMBLY_ITEM_ID,'
818 ||' DEMAND_TYPE,'
819 ||' DEMAND_CLASS,'
820 ||' ORIGINATION_TYPE,'
821 ||' WIP_STATUS_CODE,'
822 ||' WIP_SUPPLY_TYPE,'
823 ||' DELETED_FLAG,'
824 ||' REFRESH_ID,'
825 ||' SR_INSTANCE_ID)'
826 ||' select'
827 ||' x.INVENTORY_ITEM_ID,'
828 ||' x.ORGANIZATION_ID,'
829 ||' x.WIP_ENTITY_ID,'
830 ||' x.OPERATION_SEQ_NUM,'
831 ||' x.REPETITIVE_SCHEDULE_ID,'
832 ||' x.WIP_ENTITY_NAME, '
833 ||' x.REQUIRED_QUANTITY,'
834 ||' x.DATE_REQUIRED,'
835 ||' x.QUANTITY_ISSUED,'
836 ||' x.JOB_REFERENCE_ITEM_ID,'
837 ||' x.WIP_ENTITY_TYPE,'
838 --||' DECODE( x.DEMAND_CLASS, NULL, NULL, :V_ICODE||x.DEMAND_CLASS),'
839 ||' x.DEMAND_CLASS,'
840 ||' x.ORIGINATION_TYPE,'
841 ||' x.STATUS_CODE,'
842 ||' x.WIP_SUPPLY_TYPE,'
843 ||' 2,'
844 ||' :v_refresh_id,'
845 ||' :v_instance_id'
846 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
847 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
848 || v_union_sql;
849
850
851
852 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
853
854 EXECUTE IMMEDIATE v_sql_stmt
855 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
856 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
857 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
858 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
859 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
860
861 ELSE
862 EXECUTE IMMEDIATE v_sql_stmt
863 USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
864 END IF;
865
866 COMMIT;
867
868 --=================== CMRO Work Order DEMAND ==================
869 IF ((MSC_UTIL.g_collect_cmro_data = 'Y' and
870 MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121) OR
871 (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER
872 AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' )) THEN
873
874
875 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
876
877 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
878 MSC_CL_PULL.v_view_name := 'MRP_AD_CMRO_WIP_COMP_DMD_V';
879
880 -- Code AD View here -- PENDING --
881
882 END IF;
883
884
885
889 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
886 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
887 MSC_CL_PULL.v_view_name := 'MRP_AP_CMRO_WIP_COMP_DMD_V';
888
890
891 v_union_sql :=
892 ' AND ( x.RN1> :v_lrn)'
893 ||' UNION '
894 ||' select'
895 ||' x.INVENTORY_ITEM_ID,'
896 ||' x.INVENTORY_ITEM_ID,'
897 ||' x.ORGANIZATION_ID,'
898 ||' x.WIP_ENTITY_ID,'
899 ||' x.WIP_ENTITY_ID,'
900 ||' x.WIP_ENTITY_NAME, '
901 ||' x.STATUS_CODE,'
902 ||' x.WIP_SUPPLY_TYPE,'
903 ||' x.COPY_OP_SEQ_NUM, '
904 ||' x.NEW_REQUIRED_QUANTITY,'
905 ||' x.QUANTITY_ISSUED,'
906 ||' x.JOB_REFERENCE_ITEM_ID,'
907 ||' x.DEMAND_TYPE,'
908 ||' x.PROJECT_ID,'
909 ||' x.TASK_ID,'
910 ||' x.PLANNING_GROUP,'
911 ||' x.END_ITEM_UNIT_NUMBER,'
912 ||' x.DEMAND_CLASS,'
913 ||' x.ORIGINATION_TYPE,'
914 ||' x.DATE_REQUIRED- :v_dgmt,'
915 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
916 ||' 2,'
917 ||' X.QUANTITY_PER_ASSEMBLY,'
918 ||' X.ASSET_ITEM_ID,'
919 ||' X.ASSET_SERIAL_NUMBER,'
920 ||' X.BASIS_TYPE,'
921 ||' X.COMPONENT_YIELD_FACTOR,'
922 ||' :v_refresh_id,'
923 ||' :v_instance_id'
924 ||' from MRP_AP_CMRO_WIP_COMP_DMD_V'||MSC_CL_PULL.v_dblink||' X'
925 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
926 ||' AND DECODE(:v_collect_completed_jobs,'
927 ||' 2,x.status_code,'
928 ||' 1) <>4'
929 ||' AND x.NEW_REQUIRED_QUANTITY > 0'
930 ||' AND ( x.RN2> :v_lrn )'
931 ||' UNION '
932 ||' select'
933 ||' x.INVENTORY_ITEM_ID,'
934 ||' x.INVENTORY_ITEM_ID,'
935 ||' x.ORGANIZATION_ID,'
936 ||' x.WIP_ENTITY_ID,'
937 ||' x.WIP_ENTITY_ID,'
938 ||' x.WIP_ENTITY_NAME, '
939 ||' x.STATUS_CODE,'
940 ||' x.WIP_SUPPLY_TYPE,'
941 ||' x.COPY_OP_SEQ_NUM, '
942 ||' x.NEW_REQUIRED_QUANTITY,'
943 ||' x.QUANTITY_ISSUED,'
944 ||' x.JOB_REFERENCE_ITEM_ID,'
945 ||' x.DEMAND_TYPE,'
946 ||' x.PROJECT_ID,'
947 ||' x.TASK_ID,'
948 ||' x.PLANNING_GROUP,'
949 ||' x.END_ITEM_UNIT_NUMBER,'
950 ||' x.DEMAND_CLASS,'
951 ||' x.ORIGINATION_TYPE,'
952 ||' x.DATE_REQUIRED- :v_dgmt,'
953 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
954 ||' 2,'
955 ||' X.QUANTITY_PER_ASSEMBLY,'
956 ||' X.ASSET_ITEM_ID,'
957 ||' X.ASSET_SERIAL_NUMBER,'
958 ||' X.BASIS_TYPE,'
959 ||' X.COMPONENT_YIELD_FACTOR,'
960 ||' :v_refresh_id,'
961 ||' :v_instance_id'
962 ||' from MRP_AP_CMRO_WIP_COMP_DMD_V'||MSC_CL_PULL.v_dblink||' X'
963 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
964 ||' AND DECODE(:v_collect_completed_jobs,'
965 ||' 2,x.status_code,'
966 ||' 1) <>4'
967 ||' AND x.NEW_REQUIRED_QUANTITY > 0'
968 ||' AND ( x.RN3> :v_lrn )';
969 ELSE
970 v_union_sql := ' ';
971 END IF;
972
973 v_sql_stmt:=
974 'insert into MSC_ST_DEMANDS'
975 ||' ( INVENTORY_ITEM_ID,'
976 ||' SOURCE_INVENTORY_ITEM_ID,'
977 ||' ORGANIZATION_ID,'
978 ||' WIP_ENTITY_ID,'
979 ||' SOURCE_WIP_ENTITY_ID,'
980 ||' ORDER_NUMBER,'
981 ||' WIP_STATUS_CODE,'
982 ||' WIP_SUPPLY_TYPE,'
983 ||' OPERATION_SEQ_NUM,'
984 ||' USING_REQUIREMENT_QUANTITY,'
985 ||' QUANTITY_ISSUED,'
986 ||' USING_ASSEMBLY_ITEM_ID,'
987 ||' DEMAND_TYPE,'
988 ||' PROJECT_ID,'
989 ||' TASK_ID,'
990 ||' PLANNING_GROUP,'
991 ||' END_ITEM_UNIT_NUMBER,'
992 ||' DEMAND_CLASS,'
993 ||' ORIGINATION_TYPE,'
994 ||' USING_ASSEMBLY_DEMAND_DATE,'
995 ||' MPS_DATE_REQUIRED,'
996 ||' DELETED_FLAG,'
997 ||' QUANTITY_PER_ASSEMBLY,'
998 ||' ASSET_ITEM_ID,'
999 ||' ASSET_SERIAL_NUMBER,'
1000 ||' COMPONENT_SCALING_TYPE,'
1001 ||' COMPONENT_YIELD_FACTOR,'
1002 ||' REFRESH_ID,'
1003 ||' SR_INSTANCE_ID)'
1004 ||' select'
1005 ||' x.INVENTORY_ITEM_ID,'
1006 ||' x.INVENTORY_ITEM_ID,'
1007 ||' x.ORGANIZATION_ID,'
1008 ||' x.WIP_ENTITY_ID,'
1009 ||' x.WIP_ENTITY_ID,'
1010 ||' x.WIP_ENTITY_NAME, '
1011 ||' x.STATUS_CODE,'
1012 ||' x.WIP_SUPPLY_TYPE,'
1013 ||' x.COPY_OP_SEQ_NUM, '
1014 ||' x.NEW_REQUIRED_QUANTITY,'
1015 ||' x.QUANTITY_ISSUED,'
1016 ||' x.JOB_REFERENCE_ITEM_ID,'
1017 ||' x.DEMAND_TYPE,'
1018 ||' x.PROJECT_ID,'
1019 ||' x.TASK_ID,'
1020 ||' x.PLANNING_GROUP,'
1021 ||' x.END_ITEM_UNIT_NUMBER,'
1022 ||' x.DEMAND_CLASS,'
1023 ||' x.ORIGINATION_TYPE,'
1024 ||' x.DATE_REQUIRED- :v_dgmt,'
1025 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
1026 ||' 2,'
1027 ||' X.QUANTITY_PER_ASSEMBLY,'
1028 ||' X.ASSET_ITEM_ID,'
1029 ||' X.ASSET_SERIAL_NUMBER,'
1030 ||' X.BASIS_TYPE,'
1031 ||' X.COMPONENT_YIELD_FACTOR,'
1032 ||' :v_refresh_id,'
1033 ||' :v_instance_id'
1034 ||' from MRP_AP_CMRO_WIP_COMP_DMD_V'||MSC_CL_PULL.v_dblink||' X'
1035 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1036 ;
1037
1038
1039 v_temp_sql := ' AND DECODE(:v_collect_completed_jobs,'
1040 ||' 2,x.status_code,'
1041 ||' 1) <>4'
1042 ||' AND x.NEW_REQUIRED_QUANTITY > 0';
1043
1044
1045 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1046
1047
1048 v_temp_sql_stmt := v_sql_stmt || v_temp_sql || v_union_sql;
1049
1050 EXECUTE IMMEDIATE v_temp_sql_stmt
1051 USING MSC_CL_PULL.v_dgmt,
1052 MSC_CL_PULL.v_dgmt,
1053 MSC_CL_PULL.v_refresh_id,
1054 MSC_CL_PULL.v_instance_id,
1055 MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS,
1056 MSC_CL_PULL.v_lrn,
1060 MSC_CL_PULL.v_instance_id,
1057 MSC_CL_PULL.v_dgmt,
1058 MSC_CL_PULL.v_dgmt,
1059 MSC_CL_PULL.v_refresh_id,
1061 MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS,
1062 MSC_CL_PULL.v_lrn,
1063 MSC_CL_PULL.v_dgmt,
1064 MSC_CL_PULL.v_dgmt,
1065 MSC_CL_PULL.v_refresh_id,
1066 MSC_CL_PULL.v_instance_id,
1067 MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS,
1068 MSC_CL_PULL.v_lrn;
1069
1070
1071 ELSE
1072
1073 v_temp_sql_stmt := v_sql_stmt || v_temp_sql;
1074
1075 EXECUTE IMMEDIATE v_temp_sql_stmt
1076 USING MSC_CL_PULL.v_dgmt,
1077 MSC_CL_PULL.v_dgmt,
1078 MSC_CL_PULL.v_refresh_id,
1079 MSC_CL_PULL.v_instance_id,
1080 MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS;
1081
1082
1083 END IF;
1084
1085 COMMIT;
1086
1087
1088 END IF; -- CMRO Work Order Demand
1089
1090 END IF; -- MSC_CL_PULL.WIP_ENABLED
1091
1092 END LOAD_WIP_DEMAND;
1093
1094
1095 -- ====================== Discrete Job/ Flow Schedule SUPPLY ==================
1096
1097 PROCEDURE LOAD_WIP_SUPPLY IS
1098 lv_lbj_details NUMBER:=0;
1099 lv_op_seq_num VARCHAR2(100) := null;
1100 lv_cond_sql VARCHAR2(100) := null;
1101 lv_qty_sql_temp VARCHAR2(300) :=null;
1102 lv_new_view_name VARCHAR2(300) :=null;
1103 lv_new_org_string VARCHAR2(32767) :=null;
1104 v_temp_sql VARCHAR2(32767) :=null;
1105 v_temp_sql_stmt VARCHAR2(32767) :=null;
1106 v_temp_sql2 VARCHAR2(32767) :=null;
1107 v_temp_sql_stmt2 VARCHAR2(32767) :=null;
1108
1109 BEGIN
1110
1111 IF MSC_CL_PULL.WIP_ENABLED= MSC_UTIL.SYS_YES THEN
1112
1113 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1114
1115 select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
1116 where instance_id = MSC_CL_PULL.v_instance_id ;
1117 END IF;
1118
1119 IF MSC_CL_PULL.v_lrnn<> -1 THEN
1120 lv_qty_sql_temp := NULL;
1121 ELSIF MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS = 1 THEN
1122 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1123 ||' 1, DECODE( :v_mps_consume_profile_value, '
1124 ||' 1, x.mps_net_quantity,'
1125 ||' x.net_quantity), '
1126 ||' x.net_quantity) >= 0' ;
1127 ELSE
1128 lv_qty_sql_temp := ' AND DECODE( x.wip_job_type, '
1129 ||' 1, DECODE( :v_mps_consume_profile_value, '
1130 ||' 1, x.mps_net_quantity,'
1131 ||' x.net_quantity), '
1132 ||' x.net_quantity) > 0'
1133 ||' AND x.status_code <> 4' ;
1134 END IF;
1135
1136 --=================== Net Change Mode: Delete ==================
1137
1138 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1139
1140 -- =================== JOB/FLOW SCHEDULE =====================
1141 IF ((MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
1142 -- Changed for Bug 6081537
1143 -- Supplies from Non Depot orgs
1144
1145 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1146 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_ERO_WIP_JOB_SUP_V';
1147
1148 v_sql_stmt:=
1149 'insert into MSC_ST_SUPPLIES'
1150 ||' ( DISPOSITION_ID,'
1151 ||' ORDER_TYPE,'
1152 ||' DELETED_FLAG,'
1153 ||' REFRESH_ID,'
1154 ||' SR_INSTANCE_ID)'
1155 ||' select'
1156 ||' x.WIP_ENTITY_ID,'
1157 ||' x.ORDER_TYPE,'
1158 ||' 1,'
1159 ||' :v_refresh_id,'
1160 ||' :v_instance_id'
1161 ||' from MRP_AD_NON_ERO_WIP_JOB_SUP_V'||MSC_CL_PULL.v_dblink||' x'
1162 ||' where DECODE( x.wip_job_type,'
1163 ||' 1, DECODE( :v_mps_consume_profile_value,'
1164 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1165 ||' x.WJS_NET_QTY_FLAG),'
1166 ||' x.WJS_NET_QTY_FLAG)=1'
1167 ||' AND x.RN> :v_lrn '
1168 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str;
1169
1170 -- Supplies from Depot repair orgs
1171 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1172 MSC_CL_PULL.v_view_name := 'MRP_AD_NON_RO_WIP_JOB_SUPP_V';
1173
1174 v_temp_sql2:=
1175 'insert into MSC_ST_SUPPLIES'
1176 ||' ( DISPOSITION_ID,'
1177 ||' ORDER_TYPE,'
1178 ||' DELETED_FLAG,'
1179 ||' REFRESH_ID,'
1180 ||' SR_INSTANCE_ID)'
1181 ||' select'
1182 ||' x.WIP_ENTITY_ID,'
1183 ||' x.ORDER_TYPE,'
1184 ||' 1,'
1185 ||' :v_refresh_id,'
1186 ||' :v_instance_id'
1187 ||' from MRP_AD_NON_ERO_WIP_JOB_SUP_V'||MSC_CL_PULL.v_dblink||' x'
1188 ||' where DECODE( x.wip_job_type,'
1189 ||' 1, DECODE( :v_mps_consume_profile_value,'
1190 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1191 ||' x.WJS_NET_QTY_FLAG),'
1192 ||' x.WJS_NET_QTY_FLAG)=1'
1193 ||' AND x.RN> :v_lrn '
1194 ||' AND x.ORGANIZATION_ID '||MSC_UTIL.v_depot_org_str;
1195
1196
1197 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1198 MSC_CL_PULL.v_instance_id,
1199 MSC_CL_PULL.v_mps_consume_profile_value,
1200 MSC_CL_PULL.v_lrn;
1201
1205 EXECUTE IMMEDIATE v_temp_sql2 USING MSC_CL_PULL.v_refresh_id,
1202 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
1203 COMMIT;
1204
1206 MSC_CL_PULL.v_instance_id,
1207 MSC_CL_PULL.v_mps_consume_profile_value,
1208 MSC_CL_PULL.v_lrn;
1209
1210 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_NON_ERO_WIP_JOB_SUP_V = '|| SQL%ROWCOUNT);
1211 COMMIT;
1212
1213 ELSE -- (SRP Profile is No)
1214
1215 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1216 MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_JOB_SUPPLIES_V';
1217
1218 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1219 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1220 ELSE
1221 v_temp_sql := NULL;
1222 END IF;
1223
1224 v_sql_stmt:=
1225 'insert into MSC_ST_SUPPLIES'
1226 ||' ( DISPOSITION_ID,'
1227 ||' ORDER_TYPE,'
1228 ||' DELETED_FLAG,'
1229 ||' REFRESH_ID,'
1230 ||' SR_INSTANCE_ID)'
1231 ||' select'
1232 ||' x.WIP_ENTITY_ID,'
1233 ||' x.ORDER_TYPE,'
1234 ||' 1,'
1235 ||' :v_refresh_id,'
1236 ||' :v_instance_id'
1237 ||' from MRP_AD_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1238 ||' where DECODE( x.wip_job_type,'
1239 ||' 1, DECODE( :v_mps_consume_profile_value,'
1240 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1241 ||' x.WJS_NET_QTY_FLAG),'
1242 ||' x.WJS_NET_QTY_FLAG)=1'
1243 ||' AND x.RN> :v_lrn '
1244 || v_temp_sql;
1245
1246 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1247 MSC_CL_PULL.v_instance_id,
1248 MSC_CL_PULL.v_mps_consume_profile_value,
1249 MSC_CL_PULL.v_lrn;
1250
1251 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of deleted rows for MRP_AD_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1252 COMMIT;
1253 END IF;
1254 -- =================== JOB COMPONENTS =====================
1255
1256 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1257 MSC_CL_PULL.v_view_name := 'MRP_AD_WIP_COMP_SUPPLIES_V';
1258
1259 lv_op_seq_num := 'x.OPERATION_SEQ_NUM, ';
1260
1261 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1262 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1263
1264 if lv_lbj_details = 1 Then
1265 lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1266 end if;
1267
1268 ELSE
1269 v_temp_sql := NULL;
1270 END IF;
1271
1272 v_sql_stmt:=
1273 'insert into MSC_ST_SUPPLIES'
1274 ||' ( DISPOSITION_ID,'
1275 ||' OPERATION_SEQ_NUM,'
1276 ||' INVENTORY_ITEM_ID,'
1277 ||' ORDER_TYPE,'
1278 ||' DELETED_FLAG,'
1279 ||' REFRESH_ID,'
1280 ||' SR_INSTANCE_ID)'
1281 ||' select'
1282 ||' x.WIP_ENTITY_ID,'
1283 || lv_op_seq_num
1284 ||' x.INVENTORY_ITEM_ID,'
1285 ||' x.ORDER_TYPE,'
1286 ||' 1,'
1287 ||' :v_refresh_id,'
1288 ||' :v_instance_id'
1289 ||' from MRP_AD_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1290 ||' where ( DECODE( :v_mps_consume_profile_value,'
1291 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1292 ||' x.WJS_NET_QTY_FLAG)=1'
1293 ||' OR x.MRP_NET_FLAG= 1'
1294 ||' OR DECODE( :v_mps_consume_profile_value,'
1295 ||' 1, x.MPS_FLAG,'
1296 ||' x.NMPS_FLAG)= 1 )'
1297 ||' AND x.RN> :v_lrn '
1298 || v_temp_sql;
1299
1300 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1301 MSC_CL_PULL.v_instance_id,
1302 MSC_CL_PULL.v_mps_consume_profile_value,
1303 MSC_CL_PULL.v_mps_consume_profile_value,
1304 MSC_CL_PULL.v_lrn;
1305
1306 COMMIT;
1307
1308 -- =================== REPT ITEM =====================
1309
1310 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1311 MSC_CL_PULL.v_view_name := 'MRP_AD_REPT_ITEM_SUPPLIES_V';
1312
1313 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1314 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1315 ELSE
1316 v_temp_sql := NULL;
1317 END IF;
1318
1319 v_sql_stmt:=
1320 ' insert into MSC_ST_SUPPLIES'
1321 ||' ( DISPOSITION_ID,'
1322 ||' ORDER_TYPE,'
1323 ||' ORGANIZATION_ID,'
1324 ||' DELETED_FLAG,'
1325 ||' REFRESH_ID,'
1326 ||' SR_INSTANCE_ID)'
1327 ||' select'
1328 ||' x.REPETITIVE_SCHEDULE_ID,'
1329 ||' x.ORDER_TYPE,'
1330 ||' x.ORGANIZATION_ID,'
1331 ||' 1,'
1332 ||' :v_refresh_id,'
1333 ||' :v_instance_id'
1334 ||' from MRP_AD_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1335 ||' WHERE x.RN> :v_lrn '
1336 || v_temp_sql;
1337
1338 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
1339
1340 COMMIT;
1341
1342 END IF;
1343
1344 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1345 MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1346
1347
1348 Begin
1349 if MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 Then
1350 if lv_lbj_details = 1 Then
1351 v_temp_sql := ' x.routing_reference_id,x.bom_reference_id,x.coproducts_supply,x.jd_operation_seq_num,'||
1352 'x.JUMP_OP_SEQ_NUM,x.JOB_OP_SEQ_NUM, '||
1353 'x.requested_start_date,x.requested_completion_date,x.schedule_priority,x.asset_item_id,x.asset_serial_number,' ||/* ds change */
1357 'x.requested_start_date,x.requested_completion_date,x.schedule_priority,x.asset_item_id,x.asset_serial_number,' || /* ds change */
1354 'x.ACTUAL_START_DATE,x.cfm_routing_flag, '; /* Discrete Mfg Enahancements Bug 4479276 */
1355 else
1356 v_temp_sql := ' x.routing_reference_id,x.bom_reference_id,x.coproducts_supply,x.operation_seq_num , NULL, NULL, '||
1358 'x.ACTUAL_START_DATE,x.cfm_routing_flag, '; /* Discrete Mfg Enahancements Bug 4479276 */
1359 end if;
1360 else
1361 v_temp_sql := ' NULL, NULL, NULL, NULL, NULL, NULL, '||'NULL, NULL, NULL, ';
1362 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
1363 v_temp_sql := v_temp_sql ||'x.asset_item_id,';
1364 ELSE
1365 v_temp_sql := v_temp_sql ||'NULL,';
1366 END IF;
1367 v_temp_sql := v_temp_sql ||'NULL, NULL,NULL, ';
1368 end if;
1369 End;
1370
1371 Begin
1372
1373 --Bug#3419189
1374 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1375
1376 v_temp_sql1 := ' x.wip_start_quantity,';
1377
1378 ELSE
1379 v_temp_sql1 := ' NULL,';
1380 END IF;
1381
1382 IF MSC_CL_PULL.v_apps_ver > MSC_UTIL.G_APPS121
1383 THEN
1384 v_temp_sql5 := 'x.maintenance_object_source,x.description,x.asset_number,'||
1385 'x.maintenance_object_id,x.maintenance_object_type,';
1386 ELSE
1387 v_temp_sql5 := 'NULL,NULL,NULL,NULL,NULL,';
1388 END IF;
1389
1390 /*USAF -- eam */
1391 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
1392 v_temp_sql6 := 'NULL,x.CLASS_CODE,x.SHUTDOWN_TYPE,x.ACTIVITY_TYPE,'
1393 ||' decode(x.ORDER_TYPE,70, x.INVENTORY_ITEM_ID,NULL) ACTIVITY_ITEM_ID,';
1394 v_temp_sql7 := ' decode(x.ORDER_TYPE,70, x.asset_item_id, x.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, ';
1395 ELSE
1396 v_temp_sql6 := 'NULL,NULL,NULL,NULL,NULL,';
1397 v_temp_sql7 := ' x.INVENTORY_ITEM_ID,';
1398 END IF;
1399
1400 /*Select decode(MSC_CL_PULL.v_apps_ver,MSC_UTIL.G_APPS115,
1401 ' DECODE( x.wip_job_type, 1,
1402 DECODE( :v_mps_consume_profile_value,
1403 1, x.mps_wip_start_quantity,
1404 x.wip_start_quantity),
1405 x.wip_start_quantity ), ',
1406 ' decode(:v_mps_consume_profile_value,1,NULL,null) ,')
1407 into v_temp_sql1
1408 from dual;*/
1409 End;
1410
1411
1412 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1413
1414 v_union_sql :=
1415 ' AND ( x.RN1 > :v_lrn )'
1416 ||' UNION '
1417 ||' select'
1418 ||v_temp_sql7
1419 ||' x.ORGANIZATION_ID,'
1420 ||' x.WIP_ENTITY_ID,'
1421 ||' x.WIP_ENTITY_NAME, '
1422 ||' DECODE( x.wip_job_type, '
1423 ||' 1, DECODE( :v_mps_consume_profile_value,'
1424 ||' 1, decode(:lv_lbj_details,1,x.jd_mps_job_quantity,x.mps_job_quantity),'
1425 ||' decode(:lv_lbj_details,1,x.jd_job_quantity,x.job_quantity)),'
1426 ||' decode(:lv_lbj_details,1,x.jd_job_quantity,x.job_quantity)),'
1427 ||' DECODE( x.wip_job_type,'
1428 ||' 1, DECODE( :v_mps_consume_profile_value,'
1429 ||' 1, x.mps_scheduled_completion_date,'
1430 ||' x.scheduled_completion_date),'
1431 ||' x.scheduled_completion_date)- :v_dgmt,'
1432 ||' DECODE( x.wip_job_type,'
1433 ||' 1, DECODE( :v_mps_consume_profile_value,'
1434 ||' 1, x.mps_expected_scrap_quantity,'
1435 ||' x.expected_scrap_quantity),'
1436 ||' x.expected_scrap_quantity),'
1437 ||' x.quantity_scrapped,'
1438 ||' x.quantity_completed,'
1439 ||' x.FIRM_PLANNED_STATUS_TYPE,'
1440 ||' x.START_DATE- :v_dgmt,'
1441 ||' x.REVISION,'
1442 ||' x.ORDER_TYPE,'
1443 ||' x.PROJECT_ID,'
1444 ||' x.TASK_ID,'
1445 ||' x.PLANNING_GROUP,'
1446 ||' x.SCHEDULE_GROUP_ID,'
1447 ||' x.BUILD_SEQUENCE,'
1448 ||' x.LINE_ID,'
1449 ||' x.ALTERNATE_BOM_DESIGNATOR,'
1450 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
1451 ||' x.END_ITEM_UNIT_NUMBER,'
1452 ||' x.STATUS_CODE,'
1453 ||' x.SCHEDULE_GROUP_NAME,'
1454 ||' x.DEMAND_CLASS,'
1455 ||' 2,'
1456 || v_temp_sql
1457 || v_temp_sql1
1458 || v_temp_sql5
1459 || v_temp_sql6
1460 ||' :v_refresh_id,'
1461 ||' :v_instance_id'
1462 ||' from MRP_AP_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1463 ||' where x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1464 /*Bug#4704457 ||' AND DECODE( x.wip_job_type,'
1465 ||' 1, DECODE( :v_mps_consume_profile_value,'
1466 ||' 1, x.mps_net_quantity,'
1467 ||' x.net_quantity),'
1468 ||' x.net_quantity) > 0' */
1469 || lv_qty_sql_temp
1470 ||' AND (( x.RN2 > :v_lrn ) OR '
1471 ||' (x.ENTITY_TYPE = 5 '
1472 ||' AND EXISTS '
1473 ||' (SELECT NULL '
1474 ||' FROM MRP_SN_WOPRS'||MSC_CL_PULL.v_dblink||' y '
1475 ||' WHERE '
1476 ||' x.WIP_ENTITY_ID = y.WIP_ENTITY_ID*2 '
1477 ||' AND x.ORGANIZATION_ID = y.ORGANIZATION_ID '
1478 ||' AND y.RN > :v_lrn )))';
1479
1480 ELSE
1481
1482 v_union_sql := ' ';
1483
1484 END IF;
1485
1486 v_sql_stmt:=
1487 'insert into MSC_ST_SUPPLIES'
1488 ||' ( INVENTORY_ITEM_ID,'
1489 ||' ORGANIZATION_ID,'
1490 ||' DISPOSITION_ID,'
1491 ||' ORDER_NUMBER,'
1492 ||' NEW_ORDER_QUANTITY,'
1493 ||' NEW_SCHEDULE_DATE,'
1494 ||' EXPECTED_SCRAP_QTY,'
1495 ||' QTY_SCRAPPED,'
1496 ||' QTY_COMPLETED,'
1497 ||' FIRM_PLANNED_TYPE,'
1498 ||' NEW_WIP_START_DATE,'
1499 ||' REVISION,'
1500 ||' ORDER_TYPE,'
1501 ||' PROJECT_ID,'
1502 ||' TASK_ID,'
1506 ||' LINE_ID,'
1503 ||' PLANNING_GROUP,'
1504 ||' SCHEDULE_GROUP_ID,'
1505 ||' BUILD_SEQUENCE,'
1507 ||' ALTERNATE_BOM_DESIGNATOR,'
1508 ||' ALTERNATE_ROUTING_DESIGNATOR,'
1509 ||' UNIT_NUMBER,'
1510 ||' WIP_STATUS_CODE,'
1511 ||' SCHEDULE_GROUP_NAME,'
1512 ||' DEMAND_CLASS,'
1513 ||' DELETED_FLAG,'
1514 ||' ROUTING_SEQUENCE_ID,'
1515 ||' BILL_SEQUENCE_ID,'
1516 ||' COPRODUCTS_SUPPLY,'
1517 ||' OPERATION_SEQ_NUM,'
1518 ||' JUMP_OP_SEQ_NUM,'
1519 ||' JOB_OP_SEQ_NUM,'
1520 ||' REQUESTED_START_DATE,' /* ds change start */
1521 ||' REQUESTED_COMPLETION_DATE,'
1522 ||' SCHEDULE_PRIORITY,'
1523 ||' ASSET_ITEM_ID,'
1524 ||' ASSET_SERIAL_NUMBER,' /* ds change start */
1525 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
1526 ||' CFM_ROUTING_FLAG,'
1527 ||' WIP_START_QUANTITY,'
1528 ||' MAINTENANCE_OBJECT_SOURCE,'
1529 ||' DESCRIPTION,'
1530 ||' ASSET_NUMBER,'
1531 ||' MAINTENANCE_OBJECT_ID,'
1532 ||' MAINTENANCE_OBJECT_TYPE,'
1533 ||' SCHEDULE_DESIGNATOR_ID,' /*???? need to confirm*/
1534 ||' CLASS_CODE,'
1535 ||' SHUTDOWN_TYPE,'
1536 ||' ACTIVITY_TYPE,'
1537 ||' ACTIVITY_ITEM_ID,'
1538 ||' REFRESH_ID,'
1539 ||' SR_INSTANCE_ID)'
1540 ||' select'
1541 ||v_temp_sql7
1542 ||' x.ORGANIZATION_ID,'
1543 ||' x.WIP_ENTITY_ID,'
1544 ||' x.WIP_ENTITY_NAME, '
1545 ||' DECODE( x.wip_job_type, '
1546 ||' 1, DECODE( :v_mps_consume_profile_value,'
1547 ||' 1, decode(:lv_lbj_details,1,x.jd_mps_job_quantity,x.mps_job_quantity),'
1548 ||' decode(:lv_lbj_details,1,x.jd_job_quantity,x.job_quantity)),'
1549 ||' decode(:lv_lbj_details,1,x.jd_job_quantity,x.job_quantity)),'
1550 ||' DECODE( x.wip_job_type,'
1551 ||' 1, DECODE( :v_mps_consume_profile_value,'
1552 ||' 1, x.mps_scheduled_completion_date,'
1553 ||' x.scheduled_completion_date),'
1554 ||' x.scheduled_completion_date)- :v_dgmt,'
1555 ||' DECODE( x.wip_job_type,'
1556 ||' 1, DECODE( :v_mps_consume_profile_value,'
1557 ||' 1, x.mps_expected_scrap_quantity,'
1558 ||' x.expected_scrap_quantity),'
1559 ||' x.expected_scrap_quantity),'
1560 ||' x.quantity_scrapped,'
1561 ||' x.quantity_completed,'
1562 ||' x.FIRM_PLANNED_STATUS_TYPE,'
1563 ||' x.START_DATE- :v_dgmt,'
1564 ||' x.REVISION,'
1565 ||' x.ORDER_TYPE,'
1566 ||' x.PROJECT_ID,'
1567 ||' x.TASK_ID,'
1568 ||' x.PLANNING_GROUP,'
1569 ||' x.SCHEDULE_GROUP_ID,'
1570 ||' x.BUILD_SEQUENCE,'
1571 ||' x.LINE_ID,'
1572 ||' x.ALTERNATE_BOM_DESIGNATOR,'
1573 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
1574 ||' x.END_ITEM_UNIT_NUMBER,'
1575 ||' x.STATUS_CODE,'
1576 ||' x.SCHEDULE_GROUP_NAME,'
1577 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
1578 ||' x.DEMAND_CLASS,'
1579 ||' 2,'
1580 || v_temp_sql
1581 || v_temp_sql1
1582 || v_temp_sql5
1583 || v_temp_sql6
1584 ||' :v_refresh_id,'
1585 ||' :v_instance_id'
1586 ||' from ';
1587
1588 v_temp_sql2:= v_sql_stmt; -- Added For Bug 5935273
1589 v_temp_sql := lv_qty_sql_temp|| v_union_sql;
1590
1591 if (MSC_UTIL.G_COLLECT_SRP_DATA='N' or MSC_CL_PULL.v_lrnn<> -1 or MSC_CL_PULL.v_apps_ver < MSC_UTIL.G_APPS120) Then /* Build v_sql_stmt based on the MSC_SRP_ENABLED profile Bug 5909379 */
1592
1593 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1594 lv_new_view_name := 'MRP_AP_WIP_JOB_SUPPLIES_V';
1595 lv_new_org_string := MSC_UTIL.v_in_org_str;
1596
1597 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_WIP_JOB_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'||
1598 ' where x.ORGANIZATION_ID '||lv_new_org_string||' '||v_temp_sql ;
1599 /* Changes For Bug 5909379 SRP Enhancements */
1600
1601 else -- Profile MSC_UTIL.G_COLLECT_SRP_DATA = 'Y'
1602 -- Repair Order Entities are No
1603
1604 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1605 MSC_CL_PULL.v_view_name:= 'MRP_AP_NON_ERO_WIP_JOB_SUPP_V';
1606
1607 lv_new_view_name := 'MRP_AP_NON_ERO_WIP_JOB_SUPP_V';
1608 lv_new_org_string := MSC_UTIL.v_non_depot_org_str;
1609
1610 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_ERO_WIP_JOB_SUPP_V'||MSC_CL_PULL.v_dblink||' x'||
1611 ' where x.ORGANIZATION_ID '||lv_new_org_string||' '||v_temp_sql ;
1612 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql-- '|| v_temp_sql_stmt);
1613
1614 ---- Code for SRP when Repair Orders Entity is/not selected . Building v_sql_stmt
1615
1616
1617 end if; /* End Profile MSC_UTIL.G_COLLECT_SRP_DATA 5909379 */
1618
1619
1620 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1621 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply incr = '||v_sql_stmt);
1622 EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1623 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1624 MSC_CL_PULL.v_mps_consume_profile_value,
1625 MSC_CL_PULL.v_dgmt,
1626 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1627 --MSC_CL_PULL.v_mps_consume_profile_value,
1628 MSC_CL_PULL.v_lrn,
1629 MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1630 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1631 MSC_CL_PULL.v_mps_consume_profile_value,
1635 MSC_CL_PULL.v_lrn,
1632 MSC_CL_PULL.v_dgmt,
1633 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1634 --MSC_CL_PULL.v_mps_consume_profile_value,
1636 MSC_CL_PULL.v_lrn;
1637
1638 ELSE -- For COmplete Refresh
1639
1640 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: wip supply complete = '||v_sql_stmt);
1641
1642
1643 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120 AND MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
1644
1645
1646 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql-- '|| v_temp_sql_stmt);
1647
1648 EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1649 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1650 MSC_CL_PULL.v_mps_consume_profile_value,
1651 MSC_CL_PULL.v_dgmt,
1652 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1653 MSC_CL_PULL.v_mps_consume_profile_value;
1654
1655 v_temp_sql_stmt := NULL;
1656
1657 begin
1658 MSC_CL_PULL.v_table_name := 'MSC_ST_SUPPLIES';
1659 lv_new_view_name := 'MRP_AP_NON_RO_WIP_JOB_SUPPLY_V';
1660 lv_new_org_string := MSC_UTIL.v_depot_org_str;
1661
1662 v_temp_sql_stmt := v_sql_stmt||' MRP_AP_NON_RO_WIP_JOB_SUPPLY_V'||MSC_CL_PULL.v_dblink||' x'||
1663 ' where x.ORGANIZATION_ID '||lv_new_org_string ||' '||v_temp_sql;
1664
1665
1666 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'sql2-- '|| v_temp_sql_stmt);
1667 EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1668 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1669 MSC_CL_PULL.v_mps_consume_profile_value,
1670 MSC_CL_PULL.v_dgmt,
1671 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1672 MSC_CL_PULL.v_mps_consume_profile_value;
1673 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_NON_RO_WIP_JOB_SUPPLY_V = '|| SQL%ROWCOUNT);
1674
1675
1676 end ; ---- Code for SRP To get the work orders not attached to repair orders:
1677
1678
1679 /* Code to get the wip job supply from depo org when SRP enabled */
1680
1681
1682 ELSE -- Instance is < 12i or MSC_SRP_ENABLED Profile is 'No'
1683
1684
1685 EXECUTE IMMEDIATE v_temp_sql_stmt USING MSC_CL_PULL.v_mps_consume_profile_value, lv_lbj_details, lv_lbj_details, lv_lbj_details,
1686 MSC_CL_PULL.v_mps_consume_profile_value, MSC_CL_PULL.v_dgmt,
1687 MSC_CL_PULL.v_mps_consume_profile_value,
1688 MSC_CL_PULL.v_dgmt,
1689 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
1690 MSC_CL_PULL.v_mps_consume_profile_value;
1691 END IF;
1692 END IF;
1693 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_WIP_JOB_SUPPLIES_V = '|| SQL%ROWCOUNT);
1694 COMMIT;
1695
1696 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
1697 MSC_CL_PULL.v_view_name := 'MRP_AP_WIP_COMP_SUPPLIES_V';
1698
1699 lv_op_seq_num := ' x.OPERATION_SEQ_NUM, ';
1700
1701 IF MSC_CL_PULL.v_apps_ver>= MSC_UTIL.G_APPS115 THEN
1702 v_temp_sql := ' x.quantity_per_assembly,x.quantity_issued, x.ACTUAL_START_DATE, ';
1703
1704 if lv_lbj_details = 1 Then
1705 lv_op_seq_num := ' x.COPY_OP_SEQ_NUM, ';
1706 else
1707 lv_cond_sql := ' AND x.OPERATION_SEQ_NUM <> -1 ';
1708 end if;
1709
1710 ELSE
1711 v_temp_sql := ' NULL, NULL, NULL, ';
1712 END IF;
1713
1714
1715 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1716
1717 v_union_sql :=
1718 ' AND ( x.RN1 > :v_lrn )'
1719 ||' UNION '
1720 ||' select'
1721 ||' x.INVENTORY_ITEM_ID,'
1722 ||' x.ORGANIZATION_ID,'
1723 ||' x.WIP_ENTITY_ID,'
1724 ||' x.WIP_ENTITY_NAME, '
1725 || lv_op_seq_num
1726 ||' x.BY_PROD_QUANTITY,' -- Bug fix
1727 ||' x.ORDER_TYPE,'
1728 ||' x.PROJECT_ID,'
1729 ||' x.TASK_ID,'
1730 ||' x.PLANNING_GROUP,'
1731 ||' x.END_ITEM_UNIT_NUMBER,'
1732 ||' DECODE( x.job_type, '
1733 ||' 1, NVL(x.mps_date_required, x.scheduled_start_date),'
1734 ||' NVL(x.date_required, x.scheduled_start_date))- :v_dgmt,'
1735 ||' 2,'
1736 ||' x.STATUS_CODE,'
1737 ||' x.WIP_SUPPLY_TYPE,'
1738 ||' x.SCHEDULE_GROUP_NAME,'
1739 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
1740 ||' x.DEMAND_CLASS,'
1741 ||' x.JOB_REFERENCE_ITEM_ID,'
1742 || v_temp_sql
1743 ||' 2,'
1744 ||' :v_refresh_id,'
1745 ||' :v_instance_id'
1746 ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1747 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1748 ||' AND x.BY_PROD_QUANTITY <> 0'
1749 || lv_cond_sql
1750 --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1751 --||' 2,x.coll_completed_qty_ind,'
1752 --||' 1) >0'
1753 ||' AND DECODE(:v_collect_completed_jobs,'
1754 ||' 2,x.status_code,'
1755 ||' 1) <>4' -- 5730031
1756 ||' AND ( x.RN2 > :v_lrn )'
1757 ||' UNION '
1758 ||' select'
1759 ||' x.INVENTORY_ITEM_ID,'
1760 ||' x.ORGANIZATION_ID,'
1761 ||' x.WIP_ENTITY_ID,'
1762 ||' x.WIP_ENTITY_NAME, '
1763 || lv_op_seq_num
1764 ||' x.BY_PROD_QUANTITY,' -- Bug fix
1768 ||' x.PLANNING_GROUP,'
1765 ||' x.ORDER_TYPE,'
1766 ||' x.PROJECT_ID,'
1767 ||' x.TASK_ID,'
1769 ||' x.END_ITEM_UNIT_NUMBER,'
1770 ||' DECODE( x.job_type, '
1771 ||' 1, NVL(x.mps_date_required, x.scheduled_start_date),'
1772 ||' NVL(x.date_required, x.scheduled_start_date))- :v_dgmt,'
1773 ||' 2,'
1774 ||' x.STATUS_CODE,'
1775 ||' x.WIP_SUPPLY_TYPE,'
1776 ||' x.SCHEDULE_GROUP_NAME,'
1777 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
1778 ||' x.DEMAND_CLASS,'
1779 ||' x.JOB_REFERENCE_ITEM_ID,'
1780 || v_temp_sql
1781 ||' 2,'
1782 ||' :v_refresh_id,'
1783 ||' :v_instance_id'
1784 ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1785 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1786 ||' AND x.BY_PROD_QUANTITY <> 0'
1787 || lv_cond_sql
1788 --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1789 --||' 2,x.coll_completed_qty_ind,'
1790 --||' 1) >0'
1791 ||' AND DECODE(:v_collect_completed_jobs,'
1792 ||' 2,x.status_code,'
1793 ||' 1) <>4' -- 5730031
1794 ||' AND ( x.RN3 > :v_lrn )';
1795
1796 ELSE
1797
1798 v_union_sql := ' ';
1799
1800 END IF;
1801
1802
1803
1804 v_sql_stmt:=
1805 ' insert into MSC_ST_SUPPLIES'
1806 ||' ( INVENTORY_ITEM_ID,'
1807 ||' ORGANIZATION_ID,'
1808 ||' DISPOSITION_ID,'
1809 ||' ORDER_NUMBER,'
1810 ||' OPERATION_SEQ_NUM,'
1811 ||' NEW_ORDER_QUANTITY,'
1812 ||' ORDER_TYPE,'
1813 ||' PROJECT_ID,'
1814 ||' TASK_ID,'
1815 ||' PLANNING_GROUP,'
1816 ||' UNIT_NUMBER,'
1817 ||' NEW_SCHEDULE_DATE,'
1818 ||' FIRM_PLANNED_TYPE,'
1819 ||' WIP_STATUS_CODE,'
1820 ||' WIP_SUPPLY_TYPE,'
1821 ||' SCHEDULE_GROUP_NAME,'
1822 ||' DEMAND_CLASS,'
1823 ||' BY_PRODUCT_USING_ASSY_ID,'
1824 ||' QUANTITY_PER_ASSEMBLY,'
1825 ||' QUANTITY_ISSUED,'
1826 ||' ACTUAL_START_DATE,' /* Discrete Mfg Enahancements Bug 4479276 */
1827 ||' DELETED_FLAG,'
1828 ||' REFRESH_ID,'
1829 ||' SR_INSTANCE_ID)'
1830 ||' select'
1831 ||' x.INVENTORY_ITEM_ID,'
1832 ||' x.ORGANIZATION_ID,'
1833 ||' x.WIP_ENTITY_ID,'
1834 ||' x.WIP_ENTITY_NAME, '
1835 || lv_op_seq_num
1836 ||' x.BY_PROD_QUANTITY,' -- Bug fix
1837 ||' x.ORDER_TYPE,'
1838 ||' x.PROJECT_ID,'
1839 ||' x.TASK_ID,'
1840 ||' x.PLANNING_GROUP,'
1841 ||' x.END_ITEM_UNIT_NUMBER,'
1842 ||' DECODE( x.job_type, '
1843 ||' 1, NVL(x.mps_date_required, x.scheduled_start_date),'
1844 ||' NVL(x.date_required, x.scheduled_start_date))- :v_dgmt,'
1845 ||' 2,'
1846 ||' x.STATUS_CODE,'
1847 ||' x.WIP_SUPPLY_TYPE,'
1848 ||' x.SCHEDULE_GROUP_NAME,'
1849 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
1850 ||' x.DEMAND_CLASS,'
1851 ||' x.JOB_REFERENCE_ITEM_ID,'
1852 || v_temp_sql
1853 ||' 2,'
1854 ||' :v_refresh_id,'
1855 ||' :v_instance_id'
1856 ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
1857 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
1858 ||' AND x.BY_PROD_QUANTITY <> 0'
1859 || lv_cond_sql
1860 --||' AND DECODE(:V_COLLECT_COMPLETED_JOBS,'
1861 --||' 2,x.coll_completed_qty_ind,'
1862 --||' 1) >0'
1863 ||' AND DECODE(:v_collect_completed_jobs,'
1864 ||' 2,x.status_code,'
1865 ||' 1) <>4' -- 5730031
1866 || v_union_sql;
1867
1868 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
1869
1870 EXECUTE IMMEDIATE v_sql_stmt
1871 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1872 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn,
1873 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS, MSC_CL_PULL.v_lrn;
1874
1875 ELSE
1876
1877 EXECUTE IMMEDIATE v_sql_stmt
1878 USING MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.V_COLLECT_COMPLETED_JOBS;
1879
1880 END IF;
1881
1882
1883
1884 COMMIT;
1885
1886
1887 -- ====================== 5. LOAD LOT JOB DETAILS =====================
1888 BEGIN
1889
1890 --If lv_lbj_details = 1 Then
1891
1892 IF MSC_CL_PULL.v_lrn <> -1 then /*incremental refresh*/
1893
1894 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
1895 v_temp_sql := ' AND x.ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
1896 ELSE
1897 v_temp_sql := NULL;
1898 END IF;
1899
1900 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1901 MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_NETWORKS_V';
1902
1903 v_sql_stmt:=
1904 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1905 ||' ( WIP_ENTITY_ID,'
1906 ||' FROM_OP_SEQ_NUM,'
1907 ||' TO_OP_SEQ_NUM,'
1908 ||' DELETED_FLAG,'
1909 ||' REFRESH_ID,'
1910 ||' SR_INSTANCE_ID)'
1911 ||' select'
1912 ||' x.WIP_ENTITY_ID,'
1913 ||' x.FROM_OP_SEQ_NUM,'
1914 ||' x.TO_OP_SEQ_NUM,'
1915 ||' 1,'
1916 ||' :v_refresh_id,'
1917 ||' :v_instance_id'
1918 ||' from MRP_AD_JOB_OP_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1919 ||' where x.RN> '||MSC_CL_PULL.v_lrn
1920 ||' AND DECODE( x.from_op_seq_num,'
1921 ||' NULL, DECODE( :v_mps_consume_profile_value,'
1922 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
1923 ||' x.WJS_NET_QTY_FLAG), '
1924 ||' 1)= 1'
1925 || v_temp_sql;
1926
1930
1927 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1928 MSC_CL_PULL.v_instance_id,
1929 MSC_CL_PULL.v_mps_consume_profile_value;
1931 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
1932 COMMIT;
1933
1934
1935 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN --bug#5684183 (bcaru)
1936 /* ds change start */
1937 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1938 MSC_CL_PULL.v_view_name := 'MRP_AD_WOPR_NETWORKS_V';
1939
1940 v_sql_stmt:=
1941 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1942 ||' ( WIP_ENTITY_ID,'
1943 ||' FROM_OP_SEQ_NUM,'
1944 ||' TO_OP_SEQ_NUM,'
1945 ||' DELETED_FLAG,'
1946 ||' REFRESH_ID,'
1947 ||' SR_INSTANCE_ID)'
1948 ||' select'
1949 ||' x.WIP_ENTITY_ID,'
1950 ||' x.FROM_OP_SEQ_NUM,'
1951 ||' x.TO_OP_SEQ_NUM,'
1952 ||' 1,'
1953 ||' :v_refresh_id,'
1954 ||' :v_instance_id'
1955 ||' from MRP_AD_WOPR_NETWORKS_V'||MSC_CL_PULL.v_dblink||' x'
1956 ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1957
1958 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1959 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1960 MSC_CL_PULL.v_instance_id;
1961
1962 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_WOPR_NETWORKS = '|| SQL%ROWCOUNT);
1963 COMMIT;
1964
1965 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATION_NETWORKS';
1966 MSC_CL_PULL.v_view_name := 'MRP_AD_EAM_WO_RELSHIPS_V';
1967
1968 v_sql_stmt:=
1969 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
1970 ||' ( WIP_ENTITY_ID,'
1971 ||' TO_WIP_ENTITY_ID,'
1972 --||' RELATIONSHIP_TYPE,'
1973 ||' DELETED_FLAG,'
1974 ||' REFRESH_ID,'
1975 ||' SR_INSTANCE_ID)'
1976 ||' select'
1977 ||' x.WIP_ENTITY_ID,'
1978 ||' x.TO_WIP_ENTITY_ID,'
1979 --||' x.RELATIONSHIP_TYPE,'
1980 ||' 1,'
1981 ||' :v_refresh_id,'
1982 ||' :v_instance_id'
1983 ||' from MRP_AD_EAM_WO_RELSHIPS_V'||MSC_CL_PULL.v_dblink||' x'
1984 ||' where x.RN> '||MSC_CL_PULL.v_lrn;
1985
1986 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ad job network = '||v_sql_stmt);
1987 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
1988 MSC_CL_PULL.v_instance_id;
1989
1990 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_EAM_WO_RELSHIPS_V = '|| SQL%ROWCOUNT);
1991 -- opm populate job operation network in call
1992 -- to
1993
1994 COMMIT;
1995 /* ds change end */
1996 END IF; --v_apps_ver >= MSC_UTIL.G_APPS120
1997
1998
1999 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OPERATIONS';
2000 MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OPERATIONS_V';
2001
2002 v_sql_stmt:=
2003 'insert into MSC_ST_JOB_OPERATIONS'
2004 ||' ( WIP_ENTITY_ID,'
2005 ||' OPERATION_SEQ_NUM,'
2006 ||' DELETED_FLAG,'
2007 ||' REFRESH_ID,'
2008 ||' SR_INSTANCE_ID)'
2009 ||' select'
2010 ||' x.WIP_ENTITY_ID,'
2011 ||' x.OPERATION_SEQ_NUM,'
2012 ||' 1,'
2013 ||' :v_refresh_id,'
2014 ||' :v_instance_id'
2015 ||' from MRP_AD_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
2016 ||' where x.RN> '||MSC_CL_PULL.v_lrn
2017 ||' AND DECODE( x.operation_seq_num,'
2018 ||' NULL, DECODE( :v_mps_consume_profile_value,'
2019 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
2020 ||' x.WJS_NET_QTY_FLAG), '
2021 ||' 1)= 1'
2022 || v_temp_sql;
2023
2024 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2025 MSC_CL_PULL.v_instance_id,
2026 MSC_CL_PULL.v_mps_consume_profile_value;
2027
2028 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
2029 COMMIT;
2030
2031
2032 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_REQUIREMENT_OPS';
2033 MSC_CL_PULL.v_view_name := 'MRP_AD_REQUIREMENT_OPS_V';
2034
2035 v_sql_stmt:=
2036 'insert into MSC_ST_JOB_REQUIREMENT_OPS'
2037 ||' ( WIP_ENTITY_ID,'
2038 ||' OPERATION_SEQ_NUM,'
2039 ||' COMPONENT_ITEM_ID,'
2040 ||' PRIMARY_COMPONENT_ID,'
2041 ||' SOURCE_PHANTOM_ID,'
2042 ||' DELETED_FLAG,'
2043 ||' REFRESH_ID,'
2044 ||' SR_INSTANCE_ID)'
2045 ||' select'
2046 ||' x.WIP_ENTITY_ID,'
2047 ||' x.OPERATION_SEQ_NUM,'
2048 ||' x.COMPONENT_ITEM_ID,'
2049 ||' x.PRIMARY_COMPONENT_ID,'
2050 ||' x.SOURCE_PHANTOM_ID,'
2051 ||' 1,'
2052 ||' :v_refresh_id,'
2053 ||' :v_instance_id'
2054 ||' from MRP_AD_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2055 ||' where x.RN> '||MSC_CL_PULL.v_lrn
2056 ||' AND DECODE( x.operation_seq_num,'
2057 ||' NULL, DECODE( :v_mps_consume_profile_value,'
2058 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
2059 ||' x.WJS_NET_QTY_FLAG), '
2060 ||' 1)= 1'
2061 || v_temp_sql;
2062
2063 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2064 MSC_CL_PULL.v_instance_id,
2065 MSC_CL_PULL.v_mps_consume_profile_value;
2066
2067 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
2068 COMMIT;
2069
2070 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
2071 MSC_CL_PULL.v_view_name := 'MRP_AD_JOB_OP_RESOURCES_V';
2072
2073 v_sql_stmt:=
2077 ||' RESOURCE_SEQ_NUM,'
2074 'insert into MSC_ST_JOB_OP_RESOURCES'
2075 ||' ( WIP_ENTITY_ID,'
2076 ||' OPERATION_SEQ_NUM,'
2078 ||' DELETED_FLAG,'
2079 ||' REFRESH_ID,'
2080 ||' SR_INSTANCE_ID)'
2081 ||' select'
2082 ||' x.WIP_ENTITY_ID,'
2083 ||' x.OPERATION_SEQ_NUM,'
2084 ||' x.RESOURCE_SEQ_NUM,'
2085 ||' 1,'
2086 ||' :v_refresh_id,'
2087 ||' :v_instance_id'
2088 ||' from MRP_AD_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2089 ||' where x.RN> '||MSC_CL_PULL.v_lrn
2090 ||' AND DECODE( x.operation_seq_num,'
2091 ||' NULL, DECODE( :v_mps_consume_profile_value,'
2092 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
2093 ||' x.WJS_NET_QTY_FLAG), '
2094 ||' 1)= 1'
2095 || v_temp_sql;
2096
2097 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2098 MSC_CL_PULL.v_instance_id,
2099 MSC_CL_PULL.v_mps_consume_profile_value;
2100 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2101 COMMIT;
2102
2103 MSC_CL_PULL.v_table_name:= 'MSC_ST_JOB_OP_RESOURCES';
2104 MSC_CL_PULL.v_view_name := 'MRP_AD_LJ_SUB_OP_RESOURCES_V';
2105
2106 v_sql_stmt:=
2107 'insert into MSC_ST_JOB_OP_RESOURCES'
2108 ||' ( WIP_ENTITY_ID,'
2109 ||' OPERATION_SEQ_NUM,'
2110 ||' RESOURCE_SEQ_NUM,'
2111 ||' DELETED_FLAG,'
2112 ||' REFRESH_ID,'
2113 ||' SR_INSTANCE_ID)'
2114 ||' select'
2115 ||' x.WIP_ENTITY_ID,'
2116 ||' x.OPERATION_SEQ_NUM,'
2117 ||' x.RESOURCE_SEQ_NUM,'
2118 ||' 1,'
2119 ||' :v_refresh_id,'
2120 ||' :v_instance_id'
2121 ||' from MRP_AD_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2122 ||' where x.RN> '||MSC_CL_PULL.v_lrn
2123 ||' AND DECODE( x.operation_seq_num,'
2124 ||' NULL, DECODE( :v_mps_consume_profile_value,'
2125 ||' 1, x.WJS_MPS_NET_QTY_FLAG,'
2126 ||' x.WJS_NET_QTY_FLAG), '
2127 ||' 1)= 1'
2128 || v_temp_sql;
2129
2130 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id,
2131 MSC_CL_PULL.v_instance_id,
2132 MSC_CL_PULL.v_mps_consume_profile_value;
2133
2134 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows deleted MRP_AD_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2135 COMMIT;
2136
2137 END IF; /*incremental refresh */
2138
2139 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATION_NETWORKS';
2140 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_NETWORKS_V';
2141
2142 v_sql_stmt:=
2143 'insert into MSC_ST_JOB_OPERATION_NETWORKS'
2144 ||' (WIP_ENTITY_ID,'
2145 ||' SR_INSTANCE_ID,'
2146 ||' ORGANIZATION_ID,'
2147 ||' FROM_OP_SEQ_NUM,'
2148 ||' TO_OP_SEQ_NUM,'
2149 ||' FROM_OP_SEQ_ID,'
2150 ||' TO_OP_SEQ_ID ,'
2151 ||' RECOMMENDED,'
2152 ||' TRANSITION_TYPE ,'
2153 ||' PLANNING_PCT,'
2154 ||' ROUTING_SEQUENCE_ID,'
2155 ||' DEPENDENCY_TYPE,' /* ds change start */
2156 ||' TO_WIP_ENTITY_ID,'
2157 ||' TOP_WIP_ENTITY_ID,' /* ds change end */
2158 ||' DELETED_FLAG,'
2159 ||' REFRESH_ID )'
2160 ||' select '
2161 ||' x.WIP_ENTITY_ID,'
2162 ||' :v_instnace_id,'
2163 ||' x.ORGANIZATION_ID,'
2164 ||' x.FROM_OP_SEQ_NUM,'
2165 ||' x.TO_OP_SEQ_NUM,'
2166 ||' x.FROM_OP_SEQ_ID,'
2167 ||' x.TO_OP_SEQ_ID,'
2168 ||' x.RECOMMENDED,'
2169 ||' x.TRANSITION_TYPE,'
2170 ||' x.PLANNING_PCT,'
2171 ||' x.ROUTING_SEQUENCE_ID,'
2172 ||' x.DEPENDENCY_TYPE,' /* ds change start */
2173 ||' x.TO_WIP_ENTITY_ID,'
2174 ||' x.TOP_WIP_ENTITY_ID,' /*ds change end */
2175 ||' 2,'
2176 ||' :v_refresh_id'
2177 ||' FROM MRP_AP_JOB_OP_NETWORKS_V '||MSC_CL_PULL.v_dblink||' x '
2178 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2179 ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2180 ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
2181 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2182
2183 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Ds debug: ap job network = '||v_sql_stmt);
2184 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2185 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_NETWORKS_V = '|| SQL%ROWCOUNT);
2186
2187 COMMIT;
2188
2189 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OPERATIONS';
2190 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OPERATIONS_V';
2191
2192 v_sql_stmt:=
2193 'insert into MSC_ST_JOB_OPERATIONS'
2194 ||' (WIP_ENTITY_ID,'
2195 ||' SR_INSTANCE_ID,'
2196 ||' ORGANIZATION_ID,'
2197 ||' OPERATION_SEQ_NUM,'
2198 ||' RECOMMENDED,'
2199 ||' RECO_START_DATE,'
2200 ||' RECO_COMPLETION_DATE,'
2201 ||' OPERATION_SEQUENCE_ID,'
2202 ||' STANDARD_OPERATION_CODE,'
2203 ||' NETWORK_START_END,'
2204 ||' DEPARTMENT_ID,'
2205 ||' OPERATION_LEAD_TIME_PERCENT,'
2206 ||' MINIMUM_TRANSFER_QUANTITY,'
2207 ||' EFFECTIVITY_DATE,'
2208 ||' DISABLE_DATE,'
2209 ||' OPERATION_TYPE,'
2210 ||' YIELD,'
2211 ||' CUMULATIVE_YIELD,'
2212 ||' REVERSE_CUMULATIVE_YIELD,'
2213 ||' NET_PLANNING_PERCENT,'
2214 ||' DELETED_FLAG,'
2215 ||' REFRESH_ID';
2216 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
2217 v_sql_stmt:= v_sql_stmt
2218 ||', Operation_Code,'
2219 ||' actual_start_date,'
2220 ||' actual_end_date ,'
2221 ||' Operation_desc';
2222 END IF;
2223 v_sql_stmt:= v_sql_stmt
2224 ||') select '
2225 ||' x.WIP_ENTITY_ID,'
2226 ||' :v_instance_id,'
2227 ||' x.ORGANIZATION_ID,'
2231 ||' x.RECO_COMPLETION_DATE - :v_dgmt,'
2228 ||' x.OPERATION_SEQ_NUM,'
2229 ||' x.RECOMMENDED,'
2230 ||' x.RECO_START_DATE - :v_dgmt,'
2232 ||' x.OPERATION_SEQUENCE_ID,'
2233 ||' x.STANDARD_OPERATION_CODE,'
2234 ||' x.NETWORK_START_END,'
2235 ||' x.DEPARTMENT_ID,'
2236 ||' x.OPERATION_LEAD_TIME_PERCENT,'
2237 ||' x.MINIMUM_TRANSFER_QUANTITY,'
2238 ||' x.EFFECTIVITY_DATE - :v_dgmt,'
2239 ||' x.DISABLE_DATE - :v_dgmt,'
2240 ||' x.OPERATION_TYPE,'
2241 ||' x.YIELD,'
2242 ||' x.CUMULATIVE_YIELD,'
2243 ||' x.REVERSE_CUMULATIVE_YIELD,'
2244 ||' x.NET_PLANNING_PERCENT,'
2245 ||' 2,'
2246 ||' :v_refresh_id';
2247 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_EAM_CMRO_SUP_VER AND MSC_UTIL.G_CMRO_EAM_INT_ENABLED = 'Y' THEN
2248 v_sql_stmt:= v_sql_stmt
2249 ||', x.Operation_Code,'
2250 ||' x.actual_start_date,'
2251 ||' x.actual_end_date,'
2252 ||' x.Operation_desc';
2253 END IF;
2254 v_sql_stmt:= v_sql_stmt
2255 ||' FROM MRP_AP_JOB_OPERATIONS_V'||MSC_CL_PULL.v_dblink||' x'
2256 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2257 ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2258 ||' OR x.RN1 >' ||MSC_CL_PULL.v_lrn
2259 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2260
2261 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2262 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OPERATIONS_V = '|| SQL%ROWCOUNT);
2263
2264 COMMIT;
2265
2266 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_REQUIREMENT_OPS';
2267 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_REQUIREMENT_OPS_V';
2268
2269 IF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115 THEN
2270 v_temp_sql1 := ' x.BASIS_TYPE, ';
2271 ELSE
2272 v_temp_sql1 := ' NULL, ';
2273 END IF;
2274
2275 v_sql_stmt:=
2276 'insert into MSC_ST_JOB_REQUIREMENT_OPS'
2277 ||' (WIP_ENTITY_ID,'
2278 ||' SR_INSTANCE_ID,'
2279 ||' ORGANIZATION_ID,'
2280 ||' OPERATION_SEQ_NUM,'
2281 ||' COMPONENT_ITEM_ID,'
2282 ||' WIP_SUPPLY_TYPE,'
2283 ||' PRIMARY_COMPONENT_ID,'
2284 ||' SOURCE_PHANTOM_ID,'
2285 ||' RECOMMENDED,'
2286 ||' RECO_DATE_REQUIRED,'
2287 ||' COMPONENT_SEQUENCE_ID,'
2288 ||' COMPONENT_PRIORITY,'
2289 ||' DEPARTMENT_ID,'
2290 ||' QUANTITY_PER_ASSEMBLY,'
2291 ||' COMPONENT_YIELD_FACTOR,'
2292 ||' EFFECTIVITY_DATE,'
2293 ||' DISABLE_DATE,'
2294 ||' PLANNING_FACTOR,'
2295 ||' LOW_QUANTITY,'
2296 ||' HIGH_QUANTITY,'
2297 ||' OPERATION_LEAD_TIME_PERCENT,'
2298 ||' FROM_END_ITEM_UNIT_NUMBER,'
2299 ||' TO_END_ITEM_UNIT_NUMBER,'
2300 ||' COMPONENT_SCALING_TYPE,'
2301 ||' DELETED_FLAG,'
2302 ||' REFRESH_ID )'
2303 ||' select '
2304 ||' x.WIP_ENTITY_ID,'
2305 ||' :v_instance_id,'
2306 ||' x.ORGANIZATION_ID,'
2307 ||' x.OPERATION_SEQ_NUM,'
2308 ||' x.COMPONENT_ITEM_ID,'
2309 ||' x.WIP_SUPPLY_TYPE,'
2310 ||' x.PRIMARY_COMPONENT_ID,'
2311 ||' x.SOURCE_PHANTOM_ID,'
2312 ||' x.RECOMMENDED,'
2313 ||' x.RECO_DATE_REQUIRED - :v_dgmt,'
2314 ||' x.COMPONENT_SEQUENCE_ID,'
2315 ||' x.COMPONENT_PRIORITY,'
2316 ||' x.DEPARTMENT_ID,'
2317 ||' x.QUANTITY_PER_ASSEMBLY,'
2318 ||' x.COMPONENT_YIELD_FACTOR,'
2319 ||' x.EFFECTIVITY_DATE - :v_dgmt,'
2320 ||' x.DISABLE_DATE - :v_dgmt,'
2321 ||' x.PLANNING_FACTOR,'
2322 ||' x.LOW_QUANTITY,'
2323 ||' x.HIGH_QUANTITY,'
2324 ||' x.OPERATION_LEAD_TIME_PERCENT,'
2325 ||' x.FROM_END_ITEM_UNIT_NUMBER,'
2326 ||' x.TO_END_ITEM_UNIT_NUMBER,'
2327 || v_temp_sql1
2328 ||' 2,'
2329 ||' :v_refresh_id'
2330 ||' FROM MRP_AP_JOB_REQUIREMENT_OPS_V'||MSC_CL_PULL.v_dblink||' x'
2331 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2332 ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2333 ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2334 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn || ' )';
2335
2336 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2337 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_REQUIREMENT_OPS_V = '|| SQL%ROWCOUNT);
2338
2339 COMMIT;
2340
2341
2342 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2343 MSC_CL_PULL.v_view_name := 'MRP_AP_JOB_OP_RESOURCES_V';
2344
2345 v_sql_stmt:=
2346 'insert into MSC_ST_JOB_OP_RESOURCES'
2347 ||' (WIP_ENTITY_ID,'
2348 ||' SR_INSTANCE_ID,'
2349 ||' ORGANIZATION_ID,'
2350 ||' OPERATION_SEQ_NUM,'
2351 ||' RESOURCE_SEQ_NUM,'
2352 ||' ALTERNATE_NUM,'
2353 ||' RECOMMENDED,'
2354 ||' RECO_START_DATE,'
2355 ||' RECO_COMPLETION_DATE,'
2356 ||' RESOURCE_ID,'
2357 ||' ASSIGNED_UNITS,'
2358 ||' USAGE_RATE_OR_AMOUNT,'
2359 ||' UOM_CODE,'
2360 ||' BASIS_TYPE,'
2361 ||' RESOURCE_OFFSET_PERCENT,'
2362 ||' SCHEDULE_SEQ_NUM,'
2363 ||' PRINCIPAL_FLAG,'
2364 ||' DEPARTMENT_ID,'
2365 ||' ACTIVITY_GROUP_ID,'
2366 ||' SCHEDULE_FLAG,'
2367 ||' GROUP_SEQUENCE_ID,' /* ds change start */
2368 ||' GROUP_SEQUENCE_NUMBER,'
2369 ||' BATCH_NUMBER,'
2370 ||' FIRM_FLAG,'
2371 ||' SETUP_ID,'
2372 ||' PARENT_SEQ_NUM,'
2373 ||' MAXIMUM_ASSIGNED_UNITS,' /* ds change end */
2374 ||' DELETED_FLAG,'
2375 ||' REFRESH_ID )'
2376 ||' select '
2377 ||' x.WIP_ENTITY_ID,'
2378 ||' :v_instance_id,'
2379 ||' x.ORGANIZATION_ID,'
2380 ||' x.OPERATION_SEQ_NUM,'
2381 ||' x.RESOURCE_SEQ_NUM,'
2382 ||' x.ALTERNATE_NUM,'
2383 ||' x.RECOMMENDED,'
2384 ||' x.RECO_START_DATE - :v_dgmt,'
2385 ||' x.RECO_COMPLETION_DATE - :v_dgmt,'
2389 ||' x.UOM_CODE,'
2386 ||' x.RESOURCE_ID,'
2387 ||' x.ASSIGNED_UNITS,'
2388 ||' x.USAGE_RATE_OR_AMOUNT,'
2390 ||' x.BASIS_TYPE,'
2391 ||' x.RESOURCE_OFFSET_PERCENT,'
2392 ||' x.SCHEDULE_SEQ_NUM,'
2393 ||' x.PRINCIPLE_FLAG,'
2394 ||' x.DEPARTMENT_ID,'
2395 ||' x.ACTIVITY_GROUP_ID,'
2396 ||' x.SCHEDULE_FLAG,'
2397 ||' x.GROUP_SEQUENCE_ID,'
2398 ||' x.GROUP_SEQUENCE_NUMBER,'
2399 ||' x.BATCH_NUMBER,'
2400 ||' x.FIRM_FLAG,'
2401 ||' x.SETUP_ID,'
2402 ||' x.PARENT_RESOURCE_SEQ,'
2403 ||' x.MAXIMUM_ASSIGNED_UNITS,'
2404 ||' 2,'
2405 ||' :v_refresh_id'
2406 ||' FROM MRP_AP_JOB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2407 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2408 ||' AND (x.RN >' ||MSC_CL_PULL.v_lrn
2409 ||' OR x.RN1>' ||MSC_CL_PULL.v_lrn
2410 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn ||' )';
2411
2412 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'to be removed: Ds debug: ap job op resources = '||v_sql_stmt);
2413 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_refresh_id;
2414 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_JOB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2415
2416 COMMIT;
2417
2418 MSC_CL_PULL.v_table_name := 'MSC_ST_JOB_OP_RESOURCES';
2419 MSC_CL_PULL.v_view_name := 'MRP_AP_LJ_SUB_OP_RESOURCES_V';
2420
2421
2422 v_sql_stmt:=
2423 'insert into MSC_ST_JOB_OP_RESOURCES'
2424 ||' (WIP_ENTITY_ID,'
2425 ||' SR_INSTANCE_ID,'
2426 ||' ORGANIZATION_ID,'
2427 ||' OPERATION_SEQ_NUM,'
2428 ||' RESOURCE_SEQ_NUM,'
2429 ||' ALTERNATE_NUM,'
2430 ||' RECOMMENDED,'
2431 ||' RECO_START_DATE,'
2432 ||' RECO_COMPLETION_DATE,'
2433 ||' RESOURCE_ID,'
2434 ||' ASSIGNED_UNITS,'
2435 ||' USAGE_RATE_OR_AMOUNT,'
2436 ||' UOM_CODE,'
2437 ||' BASIS_TYPE,'
2438 ||' RESOURCE_OFFSET_PERCENT,'
2439 ||' SCHEDULE_SEQ_NUM,'
2440 ||' PRINCIPAL_FLAG,'
2441 ||' DEPARTMENT_ID,'
2442 ||' ACTIVITY_GROUP_ID,'
2443 ||' SCHEDULE_FLAG,'
2444 ||' SETUP_ID,' /* ds change */
2445 ||' DELETED_FLAG,'
2446 ||' REFRESH_ID )'
2447 ||' select '
2448 ||' x.WIP_ENTITY_ID,'
2449 ||' :v_instance_id,'
2450 ||' x.ORGANIZATION_ID,'
2451 ||' x.copy_op_seq_num,'
2452 ||' x.RESOURCE_SEQ_NUM,'
2453 ||' x.ALTERNATE_NUM,'
2454 ||' ''Y'' ,'
2455 ||' NULL,'
2456 ||' NULL,'
2457 ||' x.RESOURCE_ID,'
2458 ||' x.ASSIGNED_UNITS,'
2459 ||' x.USAGE_RATE_OR_AMOUNT,'
2460 ||' x.UOM_CODE,'
2461 ||' x.BASIS_TYPE,'
2462 ||' x.RESOURCE_OFFSET_PERCENT,'
2463 ||' x.SCHEDULE_SEQ_NUM,'
2464 ||' x.PRINCIPLE_FLAG,'
2465 ||' x.DEPARTMENT_ID,'
2466 ||' x.ACTIVITY_GROUP_ID,'
2467 ||' x.SCHEDULE_FLAG,'
2468 ||' x.SETUP_ID,' /* ds change */
2469 ||' 2,'
2470 ||' :v_refresh_id'
2471 ||' FROM MRP_AP_LJ_SUB_OP_RESOURCES_V'||MSC_CL_PULL.v_dblink||' x'
2472 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2473 ||' AND (x.RN1>' || MSC_CL_PULL.v_lrn
2474 ||' OR x.RN2>' ||MSC_CL_PULL.v_lrn
2475 ||' OR x.RN3>' || MSC_CL_PULL.v_lrn || ' )';
2476
2477 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_refresh_id;
2478 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Number of rows for MRP_AP_LJ_SUB_OP_RESOURCES_V = '|| SQL%ROWCOUNT);
2479
2480 COMMIT;
2481
2482
2483 --END IF;
2484
2485 END;
2486
2487 -- ====================== 6: Repetitive Schedule ====================
2488
2489 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
2490 MSC_CL_PULL.v_view_name := 'MRP_AP_REPT_ITEM_SUPPLIES_V';
2491
2492 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2493
2494 v_union_sql :=
2495 ' AND ( x.RN1 > :v_lrn )'
2496 ||' UNION '
2497 ||' select'
2498 ||' x.PRIMARY_ITEM_ID,'
2499 ||' x.ORGANIZATION_ID,'
2500 ||' x.REPETITIVE_SCHEDULE_ID,'
2501 ||' x.WIP_ENTITY_NAME, '
2502 ||' x.LINE_ID,'
2503 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2504 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2505 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2506 ||' x.LAST_UNIT_START_DATE- :v_dgmt,'
2507 ||' x.LAST_UNIT_COMPLETION_DATE- :v_dgmt,'
2508 ||' x.PROCESSING_WORK_DAYS,'
2509 ||' x.DAILY_PRODUCTION_RATE,'
2510 ||' x.DAILY_PRODUCTION_RATE,'
2511 ||' x.STATUS_CODE,'
2512 ||' x.FIRM_PLANNED_FLAG,'
2513 ||' x.QUANTITY_COMPLETED,'
2514 ||' x.QUANTITY_SCRAPPED,'
2515 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
2516 ||' x.DEMAND_CLASS,'
2517 ||' 30,'
2518 ||' 2,'
2519 ||' :v_refresh_id,'
2520 ||' :v_instance_id'
2521 ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2522 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2523 ||' AND (x.RN2 > :v_lrn)'
2524 ||' UNION '
2525 ||' select'
2526 ||' x.PRIMARY_ITEM_ID,'
2527 ||' x.ORGANIZATION_ID,'
2528 ||' x.REPETITIVE_SCHEDULE_ID,'
2529 ||' x.WIP_ENTITY_NAME, '
2530 ||' x.LINE_ID,'
2531 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2532 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2533 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2534 ||' x.LAST_UNIT_START_DATE- :v_dgmt,'
2535 ||' x.LAST_UNIT_COMPLETION_DATE- :v_dgmt,'
2536 ||' x.PROCESSING_WORK_DAYS,'
2537 ||' x.DAILY_PRODUCTION_RATE,'
2538 ||' x.DAILY_PRODUCTION_RATE,'
2539 ||' x.STATUS_CODE,'
2540 ||' x.FIRM_PLANNED_FLAG,'
2541 ||' x.QUANTITY_COMPLETED,'
2542 ||' x.QUANTITY_SCRAPPED,'
2543 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
2544 ||' x.DEMAND_CLASS,'
2545 ||' 30,'
2546 ||' 2,'
2547 ||' :v_refresh_id,'
2548 ||' :v_instance_id'
2549 ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2550 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2551 ||' AND (x.RN3 > :v_lrn)'
2552 ||' UNION '
2553 ||' select'
2554 ||' x.PRIMARY_ITEM_ID,'
2555 ||' x.ORGANIZATION_ID,'
2556 ||' x.REPETITIVE_SCHEDULE_ID,'
2557 ||' x.WIP_ENTITY_NAME, '
2558 ||' x.LINE_ID,'
2559 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2560 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2561 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2562 ||' x.LAST_UNIT_START_DATE- :v_dgmt,'
2563 ||' x.LAST_UNIT_COMPLETION_DATE- :v_dgmt,'
2564 ||' x.PROCESSING_WORK_DAYS,'
2565 ||' x.DAILY_PRODUCTION_RATE,'
2566 ||' x.DAILY_PRODUCTION_RATE,'
2567 ||' x.STATUS_CODE,'
2568 ||' x.FIRM_PLANNED_FLAG,'
2569 ||' x.QUANTITY_COMPLETED,'
2570 ||' x.QUANTITY_SCRAPPED,'
2571 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
2572 ||' x.DEMAND_CLASS,'
2573 ||' 30,'
2574 ||' 2,'
2575 ||' :v_refresh_id,'
2576 ||' :v_instance_id'
2577 ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2578 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2579 ||' AND (x.RN4 > :v_lrn)';
2580
2581 ELSE
2582
2583 v_union_sql := ' ';
2584
2585 END IF;
2586
2587
2588 v_sql_stmt:=
2589 ' insert into MSC_ST_SUPPLIES'
2590 ||' ( INVENTORY_ITEM_ID,'
2591 ||' ORGANIZATION_ID,'
2592 ||' DISPOSITION_ID,'
2593 ||' ORDER_NUMBER,'
2594 ||' LINE_ID,'
2595 ||' NEW_SCHEDULE_DATE,'
2596 ||' FIRST_UNIT_START_DATE,'
2597 ||' FIRST_UNIT_COMPLETION_DATE,'
2598 ||' LAST_UNIT_START_DATE,'
2599 ||' LAST_UNIT_COMPLETION_DATE,'
2600 ||' NEW_PROCESSING_DAYS,'
2601 ||' DAILY_RATE,'
2602 ||' NEW_ORDER_QUANTITY,'
2603 ||' WIP_STATUS_CODE,'
2604 ||' FIRM_PLANNED_TYPE,'
2605 ||' QTY_COMPLETED,'
2606 ||' QTY_SCRAPPED,'
2607 ||' DEMAND_CLASS,'
2608 ||' ORDER_TYPE,'
2609 ||' DELETED_FLAG,'
2610 ||' REFRESH_ID,'
2611 ||' SR_INSTANCE_ID)'
2612 ||' select'
2613 ||' x.PRIMARY_ITEM_ID,'
2614 ||' x.ORGANIZATION_ID,'
2615 ||' x.REPETITIVE_SCHEDULE_ID,'
2616 ||' x.WIP_ENTITY_NAME, '
2617 ||' x.LINE_ID,'
2618 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2619 ||' x.FIRST_UNIT_START_DATE- :v_dgmt,'
2620 ||' x.FIRST_UNIT_COMPLETION_DATE- :v_dgmt,'
2621 ||' x.LAST_UNIT_START_DATE- :v_dgmt,'
2622 ||' x.LAST_UNIT_COMPLETION_DATE- :v_dgmt,'
2623 ||' x.PROCESSING_WORK_DAYS,'
2624 ||' x.DAILY_PRODUCTION_RATE,'
2625 ||' x.DAILY_PRODUCTION_RATE,'
2626 ||' x.STATUS_CODE,'
2627 ||' x.FIRM_PLANNED_FLAG,'
2628 ||' x.QUANTITY_COMPLETED,'
2629 ||' x.QUANTITY_SCRAPPED,'
2630 --||' DECODE( x.DEMAND_CLASS,NULL,NULL,:V_ICODE||x.DEMAND_CLASS),'
2631 ||' x.DEMAND_CLASS,'
2632 ||' 30,'
2633 ||' 2,'
2634 ||' :v_refresh_id,'
2635 ||' :v_instance_id'
2636 ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||MSC_CL_PULL.v_dblink||' x'
2637 ||' WHERE x.ORGANIZATION_ID'||MSC_UTIL.v_in_org_str
2638 || v_union_sql;
2639
2640 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh
2641
2642 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_dgmt, MSC_CL_PULL.v_dgmt,
2643 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2644 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2645 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2646 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2647 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn,
2648 MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt, MSC_CL_PULL.v_dgmt,
2649 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id, MSC_CL_PULL.v_lrn;
2650
2651 ELSE
2652
2653 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_dgmt, MSC_CL_PULL.v_dgmt,
2654 MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id;
2655 END IF;
2656
2657 COMMIT;
2658
2659 END IF; -- MSC_CL_PULL.WIP_ENABLED;
2660
2661 END LOAD_WIP_SUPPLY;
2662
2663
2664 END MSC_CL_WIP_PULL;