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