[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_RPO_PULL
Source
1 PACKAGE BODY MSC_CL_RPO_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_item_type_id NUMBER := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
19 v_item_type_good NUMBER := MSC_UTIL.G_PARTCONDN_GOOD;
20 v_item_type_bad NUMBER := MSC_UTIL.G_PARTCONDN_BAD;
21
22
23 PROCEDURE LOAD_IRO IS
24 BEGIN
25
26 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
27 MSC_CL_PULL.v_view_name := 'MRP_AP_REPAIR_ORDERS_V';
28
29 IF MSC_CL_PULL.v_lrnn<> -1 THEN -- incremental refresh for bug 6126698
30 v_temp_sql1 := ' AND ((x.LAST_UPDATE_DATE > :g_last_succ_rio_time) OR (x.item_rn > '||MSC_CL_PULL.v_lrnn ||'))';
31
32 ELSE
33 v_temp_sql1 := ' AND x.RO_STATUS_CODE <> '||'''C''';
34
35 END IF;
36 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' MSC_CL_PULL.g_last_succ_iro_ref_time:'||to_char(MSC_CL_PULL.g_last_succ_iro_ref_time,'DD-MON-YYYY hh:mi:ss'));
37 v_sql_stmt := ' INSERT INTO MSC_ST_SUPPLIES '
38 ||' ( DISPOSITION_ID, '
39 ||' ORDER_TYPE, '
40 ||' ORDER_NUMBER, '
41 ||' DELETED_FLAG,'
42 ||' INVENTORY_ITEM_ID, '
43 ||' ORGANIZATION_ID, '
44 ||' PROMISED_DATE, '
45 ||' NEW_ORDER_QUANTITY, '
46 ||' UOM_CODE, '
47 ||' CUSTOMER_PRODUCT_ID, '
48 ||' SR_REPAIR_TYPE_ID, '
49 ||' PROJECT_ID, '
50 ||' TASK_ID, '
51 ||' RO_STATUS_CODE, '
52 ||' ASSET_SERIAL_NUMBER, '
53 ||' REVISION, '
54 ||' SR_REPAIR_GROUP_ID, '
55 ||' SCHEDULE_PRIORITY, '
56 ||' NEW_SCHEDULE_DATE,'
57 ||' RO_CREATION_DATE,'
58 ||' REPAIR_LEAD_TIME,'
59 ||' FIRM_PLANNED_TYPE,'
60 ||' ITEM_TYPE_ID, '
61 ||' ITEM_TYPE_VALUE,'
62 ||' REFRESH_ID, '
63 ||' SR_INSTANCE_ID )'
64 ||' select '
65 ||' x.REPAIR_LINE_ID, '
66 ||' 75, ' -- new order type for repair order supply
67 ||' x.REPAIR_NUMBER, '
68 ||' decode(x.RO_STATUS_CODE,''C'' ,1,2), '
69 ||' x.INVENTORY_ITEM_ID, '
70 ||' x.ORGANIZATION_ID, '
71 ||' x.PROMISE_DATE, '
72 ||' x.START_QUANTITY, '
73 ||' x.UNIT_OF_MEASURE, '
74 ||' x.CUSTOMER_PRODUCT_ID, '
75 ||' x.SR_REPAIR_TYPE_ID, '
76 ||' x.PROJECT_ID, '
77 ||' x.TASK_ID, '
78 ||' x.RO_STATUS_CODE, '
79 ||' x.SERIAL_NUMBER, '
80 ||' x.REVISION, '
81 ||' x.SR_REPAIR_GROUP_ID, '
82 ||' x.SCHEDULE_PRIORITY, '
83 ||' x.PROMISE_DATE,'
84 ||' x.CREATION_DATE,'
85 ||' x.REPAIR_LEADTIME,'
86 ||' 2,'
87 ||' :v_item_type_id ,'
88 ||' :v_item_type_good ,'
89 ||' :v_refresh_id ,'
90 ||' :v_instance_id'
91 ||' from MRP_AP_REPAIR_ORDERS_V'||MSC_CL_PULL.v_dblink ||' x'
92 ||' where x.organization_id '||MSC_UTIL.v_depot_org_str
93 || v_temp_sql1
94 ;
95
96
97
98 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
99
100
101 IF MSC_CL_PULL.v_lrnn<> -1 THEN
102 Execute Immediate v_sql_stmt using v_item_type_id,v_item_type_good,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id ,MSC_CL_PULL.g_last_succ_iro_ref_time;
103 ELSE
104 Execute Immediate v_sql_stmt using v_item_type_id,v_item_type_good,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id ;
105 END IF;
106
107
108 END IF;
109 COMMIT;
110 END LOAD_IRO ;
111 /* End of Procedure Load_IRO To collect Repair order from Depo orgs. Bug 5909379 */
112
113 PROCEDURE LOAD_IRO_DEMAND IS
114 BEGIN
115
116 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
117 MSC_CL_PULL.v_view_name := 'MRP_AP_REPAIR_DEMAND_V';
118
119
120 IF ((MSC_CL_PULL.v_lrnn<> -1) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN -- incremental refresh for bug 6126698
121
122 v_temp_sql1:= ' AND ((x.date1>:g_last_succ_iro_ref_time) OR (x.date2> :g_last_succ_iro_ref_time) OR (x.RN1>'
123 || MSC_CL_PULL.v_lrnn ||
124 ') OR (x.RN2>' || MSC_CL_PULL.v_lrnn ||
125 ') OR (x.RN3>' || MSC_CL_PULL.v_lrnn ||
126 ')) ' ;
127
128 ELSE
129 v_temp_sql1 := ' AND x.RO_STATUS_CODE <> '||'''C''';
130
131 END IF;
132
133 v_sql_stmt := ' INSERT INTO MSC_ST_DEMANDS '
134 ||' ( '
135 ||' repair_line_id, '
136 ||' using_assembly_item_id, '
137 ||' organization_id, '
138 ||' using_assembly_demand_date, '
139 ||' wip_entity_id ,'
140 ||' inventory_item_id, '
141 ||' USING_REQUIREMENT_QUANTITY, '
142 ||' QUANTITY_ISSUED, '
143 ||' DEMAND_TYPE, '
144 ||' PROJECT_ID, '
145 ||' TASK_ID, '
146 ||' DEMAND_CLASS, '
147 ||' ORIGINATION_TYPE, '
148 ||' DELETED_FLAG, '
149 ||' quantity_per_assembly, '
150 ||' component_scaling_type, '
151 ||' component_yield_factor, '
152 ||' operation_seq_num, '
153 ||' ITEM_TYPE_ID, '
154 ||' ITEM_TYPE_VALUE,'
155 ||' refresh_id ,'
156 ||' sr_instance_id '
157 ||' ) '
158 ||' select '
159 ||' repair_line_id, '
160 ||' ro_inventory_item_id, '
161 ||' repair_org_id, '
162 ||' using_assembly_demand_date, '
163 ||' wip_entity_id , '
164 ||' inventory_item_id, '
165 ||' new_required_quantity, '
166 ||' quantity_issued, '
167 ||' demand_type, '
168 ||' task_id, '
169 ||' planning_group, '
170 ||' demand_class, '
171 ||' origination_type, '
172 ||' decode(x.RO_STATUS_CODE,''C'' ,1,2), '
173 ||' quantity_per_assembly, '
174 ||' basis_type, '
175 ||' component_yield_factor, '
176 ||' operation_seq_num, '
177 ||' :v_item_type_id, '
178 ||' :v_item_type_good,'
179 ||' :v_refresh_id ,'
180 ||' :v_instance_id'
181 || ' from MRP_AP_REPAIR_DEMAND_V'|| MSC_CL_PULL.v_dblink ||' x'
182 || ' where x.organization_id '||MSC_UTIL.v_depot_org_str
183 || v_temp_sql1
184 ;
185
186 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
187
188
189 IF ((MSC_CL_PULL.v_lrnn<> -1) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN
190 Execute Immediate v_sql_stmt using v_item_type_id,v_item_type_good,MSC_CL_PULL.v_refresh_id,
191 MSC_CL_PULL.v_instance_id,MSC_CL_PULL.g_last_succ_iro_ref_time,MSC_CL_PULL.g_last_succ_iro_ref_time ;
192 ELSE
193 Execute Immediate v_sql_stmt using v_item_type_id,v_item_type_good,MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id ;
194 END IF;
195
196
197 END IF;
198
199 COMMIT;
200
201 IF ((MSC_CL_PULL.v_lrnn<> -1) AND (MSC_UTIL.G_COLLECT_SRP_DATA='Y')) THEN -- incremental refresh for bug 6126698
202 BEGIN
203 v_temp_sql:=
204 'insert into MSC_ST_DEMANDS'
205 ||' ( REPAIR_LINE_ID,'
206 ||' INVENTORY_ITEM_ID,'
207 ||' ORGANIZATION_ID,'
208 ||' WIP_ENTITY_ID,'
209 ||' DELETED_FLAG,'
210 ||' ORIGINATION_TYPE,'
211 ||' REFRESH_ID,'
212 ||' SR_INSTANCE_ID)'
213 ||' select'
214 ||' x.REPAIR_LINE_ID,'
215 ||' x.INVENTORY_ITEM_ID,'
216 ||' x.ORGANIZATION_ID,'
217 ||' x.WIP_ENTITY_ID,'
218 ||' 1,'
219 ||' 77,'
220 ||' :v_refresh_id,'
221 ||' :v_instance_id'
222 ||' from MRP_AD_RO_WIP_COMP_DEMANDS_V'||MSC_CL_PULL.v_dblink||' x'
223 ||' Where x. date1 > :g_last_succ_iro_ref_time or x.date2 > :g_last_succ_iro_ref_time
224 or x.RN1 > ' ||MSC_CL_PULL.v_lrnn ;
225
226
227 Execute Immediate v_temp_sql using
228 MSC_CL_PULL.v_refresh_id,
229 MSC_CL_PULL.v_instance_id,
230 MSC_CL_PULL.g_last_succ_iro_ref_time,
231 MSC_CL_PULL.g_last_succ_iro_ref_time ;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_IRO_DEMAND ');
236 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
237 RAISE;
238 END;
239 END IF; -- incremental refresh
240 COMMIT;
241
242
243 END LOAD_IRO_DEMAND;
244 /* -- End of Procedure LOAD_IRO_DEMAND To collect the demand for the wip job attached to repair order. Bug 5909379 */
245
246 PROCEDURE LOAD_ERO IS
247 lv_lbj_details NUMBER:=0;
248 BEGIN
249
250 If MSC_CL_PULL.v_lrnn<> -1 THEN /*incremental refresh */
251 v_sql_stmt:=
252 'insert into MSC_ST_SUPPLIES'
253 ||' ( DISPOSITION_ID,'
254 ||' ORDER_TYPE,'
255 ||' ORGANIZATION_ID,'
256 ||' DELETED_FLAG,'
257 ||' REFRESH_ID,'
258 ||' SR_INSTANCE_ID)'
259 ||' select'
260 ||' x.WIP_ENTITY_ID,'
261 ||' 86,'
262 ||' x.organization_id,'
263 ||' 1,'
264 ||' :v_refresh_id,'
265 ||' :v_instance_id'
266 ||' from MRP_AD_ERO_WIP_JOB_SUPP_V'||MSC_CL_PULL.v_dblink||' x'
267 ||' where x.RN> :v_lrn '
268 ||' AND organization_id '||MSC_UTIL.v_non_depot_org_str;
269
270
271
272 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
273
274 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, v_sql_stmt);
275 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, MSC_CL_PULL.v_instance_id);
276 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, MSC_CL_PULL.v_lrnn);
277
278
279 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
280 MSC_CL_PULL.v_lrnn;
281
282 END IF;
283
284 END IF ; -- net change
285
286 select LBJ_DETAILS into lv_lbj_details from msc_apps_instances
287 where instance_id = MSC_CL_PULL.v_instance_id ;
288
289
290 MSC_CL_PULL.v_table_name:= 'MSC_ST_SUPPLIES';
291 MSC_CL_PULL.v_view_name := 'MRP_AP_ERO_WIP_JOB_SUPPLY_V';
292
293 v_sql_stmt:=
294 'insert into MSC_ST_SUPPLIES'
295 ||' ( INVENTORY_ITEM_ID,'
296 ||' ORGANIZATION_ID,'
297 ||' DISPOSITION_ID,'
298 ||' ORDER_NUMBER,'
299 ||' NEW_ORDER_QUANTITY,'
300 ||' NEW_SCHEDULE_DATE,'
301 ||' EXPECTED_SCRAP_QTY,'
302 ||' QTY_SCRAPPED,'
303 ||' QTY_COMPLETED,'
304 ||' FIRM_PLANNED_TYPE,'
305 ||' NEW_WIP_START_DATE,'
306 ||' REVISION,'
307 ||' ORDER_TYPE,'
308 ||' PROJECT_ID,'
309 ||' TASK_ID,'
310 ||' PLANNING_GROUP,'
311 ||' SCHEDULE_GROUP_ID,'
312 ||' BUILD_SEQUENCE,'
313 ||' LINE_ID,'
314 ||' ALTERNATE_BOM_DESIGNATOR,'
315 ||' ALTERNATE_ROUTING_DESIGNATOR,'
316 ||' UNIT_NUMBER,'
317 ||' WIP_STATUS_CODE,'
318 ||' SCHEDULE_GROUP_NAME,'
319 ||' DEMAND_CLASS,'
320 ||' DELETED_FLAG,'
321 ||' ROUTING_SEQUENCE_ID,'
322 ||' BILL_SEQUENCE_ID,'
323 ||' COPRODUCTS_SUPPLY,'
324 ||' OPERATION_SEQ_NUM,'
325 ||' JUMP_OP_SEQ_NUM,'
326 ||' JOB_OP_SEQ_NUM,'
327 ||' REQUESTED_START_DATE,'
328 ||' REQUESTED_COMPLETION_DATE,'
329 ||' SCHEDULE_PRIORITY,'
330 ||' ASSET_ITEM_ID,'
331 ||' ASSET_SERIAL_NUMBER,'
332 ||' ACTUAL_START_DATE,'
333 ||' CFM_ROUTING_FLAG,'
334 ||' WIP_START_QUANTITY,'
335 ||' ITEM_TYPE_ID,'
336 ||' ITEM_TYPE_VALUE,'
337 ||' REFRESH_ID,'
338 ||' SR_INSTANCE_ID)'
339 ||' select'
340 ||' x.INVENTORY_ITEM_ID,'
341 ||' x.ORGANIZATION_ID,'
342 ||' x.WIP_ENTITY_ID,'
343 ||' x.WIP_ENTITY_NAME, '
344 ||' x.NEW_ORDER_QUANTITY ,'
345 ||' DECODE( x.wip_job_type,'
346 ||' 1, DECODE( :v_mps_consume_profile_value,'
347 ||' 1, x.mps_scheduled_completion_date,'
348 ||' x.scheduled_completion_date),'
349 ||' x.scheduled_completion_date)- :v_dgmt,'
350 ||' DECODE( x.wip_job_type,'
351 ||' 1, DECODE( :v_mps_consume_profile_value,'
352 ||' 1, x.mps_expected_scrap_quantity,'
353 ||' x.expected_scrap_quantity),'
354 ||' x.expected_scrap_quantity),'
355 ||' x.quantity_scrapped,'
356 ||' x.quantity_completed,'
357 ||' x.FIRM_PLANNED_STATUS_TYPE,'
358 ||' x.START_DATE- :v_dgmt,'
359 ||' x.REVISION,'
360 ||' 86,'
361 ||' x.PROJECT_ID,'
362 ||' x.TASK_ID,'
363 ||' x.PLANNING_GROUP,'
364 ||' x.SCHEDULE_GROUP_ID,'
365 ||' x.BUILD_SEQUENCE,'
366 ||' x.LINE_ID,'
367 ||' x.ALTERNATE_BOM_DESIGNATOR,'
368 ||' x.ALTERNATE_ROUTING_DESIGNATOR,'
369 ||' x.END_ITEM_UNIT_NUMBER,'
370 ||' x.STATUS_CODE,'
371 ||' x.SCHEDULE_GROUP_NAME,'
372 ||' x.DEMAND_CLASS,'
373 ||' 2,'
374 ||' x.routing_reference_id,x.bom_reference_id,x.coproducts_supply,x.jd_operation_seq_num,'
375 ||' x.JUMP_OP_SEQ_NUM,x.JOB_OP_SEQ_NUM, '
376 ||' x.requested_start_date,x.requested_completion_date,x.schedule_priority,x.asset_item_id,x.asset_serial_number,'
377 ||' x.ACTUAL_START_DATE,x.cfm_routing_flag, '
378 ||' x.wip_start_quantity,'
379 ||' :v_item_type_id,'
380 ||' :v_item_type_good,'
381 ||' :v_refresh_id,'
382 ||' :v_instance_id'
383 ||' from MRP_AP_ERO_WIP_JOB_SUPPLY_V'||MSC_CL_PULL.v_dblink ||' x'
384 ||' where x.organization_id '||MSC_UTIL.v_non_depot_org_str
385 || ' AND (x.RN1 > :v_lrn or x.RN2 > :v_lrn or x.RN3 >:v_lrn)'
386 ;
387
388
389
390
391 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
392 Execute Immediate v_sql_stmt
393 using
394 MSC_CL_PULL.v_mps_consume_profile_value,
395 MSC_CL_PULL.v_dgmt,
396 MSC_CL_PULL.v_mps_consume_profile_value,
397 MSC_CL_PULL.v_dgmt,
398 v_item_type_id,
399 v_item_type_good,
400 MSC_CL_PULL.v_refresh_id,
401 MSC_CL_PULL.v_instance_id ,
402 MSC_CL_PULL.v_lrnn,
403 MSC_CL_PULL.v_lrnn,
404 MSC_CL_PULL.v_lrnn;
405
406 END IF;
407
408
409 COMMIT;
410 END LOAD_ERO ;
411 /* End of Procedure Load_ERO To collect Repair order from Depo orgs. Bug 5935273 */
412
413 PROCEDURE LOAD_ERO_DEMAND IS
414 BEGIN
415
416 If MSC_CL_PULL.v_lrnn<> -1 then /*incremental refresh */
417 v_sql_stmt:=
418 'insert into MSC_ST_DEMANDS'
419 ||' ( WIP_ENTITY_ID,'
420 ||' OPERATION_SEQ_NUM,'
421 ||' INVENTORY_ITEM_ID,'
422 ||' ORGANIZATION_ID,'
423 ||' ORIGINATION_TYPE,'
424 ||' DELETED_FLAG,'
425 ||' REFRESH_ID,'
426 ||' SR_INSTANCE_ID)'
427 ||' select'
428 ||' x.WIP_ID,'
429 ||' x.OPERATION_SEQ_NUM,'
430 ||' x.INVENTORY_ITEM_ID,'
434 ||' :v_refresh_id,'
431 ||' x.ORGANIZATION_ID,'
432 ||' x.ORIGINATION_TYPE,'
433 ||' 1,'
435 ||' :v_instance_id'
436 ||' from MRP_AD_ERO_WIP_COMP_DEM_V'||MSC_CL_PULL.v_dblink||' x'
437 ||' where x.RN> :v_lrn '
438 ||' AND organization_id '||MSC_UTIL.v_non_depot_org_str;
439
440 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
441
442 EXECUTE IMMEDIATE v_sql_stmt USING MSC_CL_PULL.v_refresh_id, MSC_CL_PULL.v_instance_id,
443 MSC_CL_PULL.v_lrnn;
444 END IF;
445
446 END IF; -- Netchange
447
448 MSC_CL_PULL.v_table_name:= 'MSC_ST_DEMANDS';
449 MSC_CL_PULL.v_view_name := 'MRP_AP_ERO_WIP_DEMAND_V';
450
451 v_sql_stmt:=
452 'insert into MSC_ST_DEMANDS'
453 ||' ( INVENTORY_ITEM_ID,'
454 ||' SOURCE_INVENTORY_ITEM_ID,'
455 ||' ORGANIZATION_ID,'
456 ||' WIP_ENTITY_ID,'
457 ||' SOURCE_WIP_ENTITY_ID,'
458 ||' ORDER_NUMBER,' -- changes
459 ||' WIP_STATUS_CODE,'
460 ||' WIP_SUPPLY_TYPE,'
461 ||' OPERATION_SEQ_NUM,'
462 ||' USING_REQUIREMENT_QUANTITY,'
463 ||' QUANTITY_ISSUED,'
464 ||' USING_ASSEMBLY_ITEM_ID,'
465 ||' DEMAND_TYPE,'
466 ||' PROJECT_ID,'
467 ||' TASK_ID,'
468 ||' PLANNING_GROUP,'
469 ||' END_ITEM_UNIT_NUMBER,'
470 ||' DEMAND_CLASS,'
471 ||' ORIGINATION_TYPE,'
472 ||' USING_ASSEMBLY_DEMAND_DATE,'
473 ||' MPS_DATE_REQUIRED,'
474 ||' DELETED_FLAG,'
475 ||' QUANTITY_PER_ASSEMBLY,'
476 ||' ASSET_ITEM_ID,'
477 ||' ASSET_SERIAL_NUMBER,'
478 ||' COMPONENT_SCALING_TYPE,'
479 ||' COMPONENT_YIELD_FACTOR,'
480 ||' ITEM_TYPE_ID,'
481 ||' ITEM_TYPE_VALUE,'
482 ||' REFRESH_ID,'
483 ||' SR_INSTANCE_ID)'
484 ||' select'
485 ||' x.INVENTORY_ITEM_ID,'
486 ||' x.INVENTORY_ITEM_ID,'
487 ||' x.ORGANIZATION_ID,'
488 ||' x.WIP_ENTITY_ID,'
489 ||' x.WIP_ENTITY_ID,'
490 ||' x.WIP_ENTITY_NAME, '
491 ||' x.STATUS_CODE,'
492 ||' x.WIP_SUPPLY_TYPE,'
493 ||' x.COPY_OP_SEQ_NUM,'
494 ||' x.NEW_REQUIRED_QUANTITY,'
495 ||' x.QUANTITY_ISSUED,'
496 ||' x.JOB_REFERENCE_ITEM_ID,'
497 ||' x.DEMAND_TYPE,'
498 ||' x.PROJECT_ID,'
499 ||' x.TASK_ID,'
500 ||' x.PLANNING_GROUP,'
501 ||' x.END_ITEM_UNIT_NUMBER,'
502 ||' x.DEMAND_CLASS,'
503 ||' 77,'
504 ||' x.DATE_REQUIRED- :v_dgmt,'
505 ||' x.MPS_DATE_REQUIRED- :v_dgmt,'
506 ||' 2,'
507 ||' x.quantity_per_assembly,x.asset_item_id,x.asset_serial_number,x.basis_type,x.component_yield_factor, '
508 ||' :v_item_type_id,'
509 ||' :v_item_type_good,'
510 ||' :v_refresh_id,'
511 ||' :v_instance_id'
512 || ' from MRP_AP_ERO_WIP_DEMAND_V'||MSC_CL_PULL.v_dblink ||' x'
513 || ' where x.organization_id '||MSC_UTIL.v_non_depot_org_str
514 || ' AND (x.RN1 > :v_lrn or x.RN2 > :v_lrn or x.RN3 >:v_lrn)'
515 ;
516
517
518 IF (MSC_UTIL.G_COLLECT_SRP_DATA='Y' AND MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS120) THEN
519 Execute Immediate v_sql_stmt using
520 MSC_CL_PULL.v_dgmt,
521 MSC_CL_PULL.v_dgmt,
522 v_item_type_id,
523 v_item_type_good,
524 MSC_CL_PULL.v_refresh_id,
525 MSC_CL_PULL.v_instance_id,
526 MSC_CL_PULL.v_lrnn,
527 MSC_CL_PULL.v_lrnn,
528 MSC_CL_PULL.v_lrnn;
529
530 END IF;
531
532
533
534 COMMIT;
535 END LOAD_ERO_DEMAND;
536 /* -- End of Procedure LOAD_ERO_DEMAND To collect the demand for the wip job attached to repair order. Bug 5935273 */
537
538
539 END MSC_CL_RPO_PULL;