[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_RPO_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_RPO_ODS_LOAD AS -- specification
2 /* $Header: MSCLRPOB.pls 120.16 2010/10/01 14:54:47 arusubra noship $ */
3
4 v_sql_stmt VARCHAR2(4000);
5 lv_sql_stmt1 VARCHAR2(4000);
6 v_sub_str VARCHAR2(4000);
7 -- v_warning_flag NUMBER:= MSC_UTIL.SYS_NO; --2 be changed
8 -- v_is_cont_refresh BOOLEAN; -- 2 be changed
9 -- v_chr9 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(9);
10 -- v_chr10 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
11 -- v_chr13 VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
12
13
14 -- PROCEDURE LOAD_IRO_DEMAND; -- Changes for Bug 5909379 Srp Additions
15 -- PROCEDURE LOAD_ERO_DEMAND; -- Changes for Bug 5935273 Srp Additions
16
17
18 PROCEDURE LOAD_IRO_DEMAND IS
19
20 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
21 c2 CurTyp;
22 c10_d CurTyp;
23 c11_d CurTyp;
24
25 lv_sql_stmt VARCHAR2(10240);
26 lv_del_stmt2 VARCHAR2(10240);
27 lv_del_stmt VARCHAR2(10240);
28 lv_cursor_stmt VARCHAR2(10240);
29 lv_insert_stmt VARCHAR2(10240);
30 c_count NUMBER:=0;
31 lv_tbl VARCHAR2(30);
32 lv_supplies_tbl VARCHAR2(30);
33 lv_ITEM_TYPE_VALUE NUMBER;
34 lv_ITEM_TYPE_ID NUMBER;
35 -- lv_SR_INSTANCE_ID NUMBER;
36 lv_PLAN_ID NUMBER;
37 lv_DEMAND_ID NUMBER;
38 lv_DISPOSITION_ID NUMBER;
39 lv_INVENTORY_ITEM_ID NUMBER;
40 lv_ORGANIZATION_ID NUMBER;
41 lv_USING_ASSEMBLY_ITEM_ID NUMBER;
42 lv_USING_ASSEMBLY_DEMAND_DATE DATE;
43 lv_USING_REQUIREMENT_QUANTITY NUMBER;
44 lv_QUANTITY_PER_ASSEMBLY NUMBER;
45 lv_QUANTITY_ISSUED NUMBER;
46 lv_ASSEMBLY_DEMAND_COMP_DATE DATE;
47 lv_DEMAND_TYPE NUMBER;
48 lv_ORIGINATION_TYPE NUMBER;
49 lv_SOURCE_ORGANIZATION_ID NUMBER;
50 lv_RESERVATION_ID NUMBER;
51 lv_OPERATION_SEQ_NUM NUMBER;
52 lv_DEMAND_CLASS VARCHAR2(34);
53 lv_REPETITIVE_SCHEDULE_ID NUMBER;
54 lv_SR_INSTANCE_ID NUMBER;
55 lv_PROJECT_ID NUMBER;
56 lv_TASK_ID NUMBER;
57 lv_PLANNING_GROUP VARCHAR2(30);
58 lv_END_ITEM_UNIT_NUMBER VARCHAR2(30);
59 lv_ORDER_NUMBER VARCHAR2(240);
60 lv_WIP_ENTITY_ID NUMBER;
61 lv_WIP_ENTITY_NAME VARCHAR2(240);
62 lv_WIP_STATUS_CODE NUMBER;
63 lv_WIP_SUPPLY_TYPE NUMBER;
64 lv_ASSET_ITEM_ID NUMBER;
65 lv_ASSET_SERIAL_NUMBER VARCHAR2(30);
66 lv_COMPONENT_SCALING_TYPE NUMBER;
67 lv_COMPONENT_YIELD_FACTOR NUMBER;
68 lv_dummy1 NUMBER;
69 lv_dummy2 NUMBER;
70 lv_last_collection_id NUMBER;
71 lv_dummy_date DATE;
72 lv_dummy_user NUMBER;
73 lv_dummy3 NUMBER;
74 lv_REPAIR_LINE_ID NUMBER;
75 lv_sel_sql_stmt VARCHAR2(1000);
76 lv_data_sql_stmt VARCHAR2(1000);
77
78 BEGIN
79 NULL;
80 c_count:=0;
81 -- ========= Prepare the Cursor Statement ==========
82 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
83 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
84 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
85 ELSE
86 lv_tbl:= 'MSC_DEMANDS';
87 lv_supplies_tbl:= 'MSC_SUPPLIES';
88 END IF;
89
90 IF MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' THEN
91 IF MSC_CL_COLLECTION.v_is_legacy_refresh THEN
92 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
93 lv_sel_sql_stmt := 'ITEM_TYPE_ID,ITEM_TYPE_VALUE';
94 lv_data_sql_stmt := lv_ITEM_TYPE_ID||','||'msdd.ITEM_TYPE_VALUE,';
95 ELSE
96 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
97 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_GOOD;
98 lv_sel_sql_stmt := 'ITEM_TYPE_ID,ITEM_TYPE_VALUE,';
99 lv_data_sql_stmt := lv_ITEM_TYPE_ID||','||lv_ITEM_TYPE_VALUE||',';
100 END IF;
101 ELSE
102 lv_ITEM_TYPE_ID := NULL;
103 lv_ITEM_TYPE_VALUE := NULL;
104 lv_sel_sql_stmt := 'null,null,';
105 lv_data_sql_stmt := 'null,null,';
106 END IF;
107
108 /** PREPLACE CHANGE START **/
109
110 -- For Load_WIP_DEMAND Supplies are also loaded - WIP Parameter
111 -- simultaneously hence no special logic is needed
112 -- for determining which SUPPLY table to be used for pegging.
113
114 /** PREPLACE CHANGE END **/
115 /* 2201791 - select substr(order_number,1,62) since order_number is
116 defined as varchar(62) in msc_demands table */
117
118 lv_del_stmt :=
119 'Select mshr.repair_line_id,
120 T1.Inventory_item_id ,
121 mshr.Organization_id ,
122 mshr.Origination_type ,
123 mshr.SR_INSTANCE_ID
124 From MSC_ST_DEMANDS mshr ,
125 msc_item_id_lid t1
126 Where mshr.sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
127 ||' And mshr.ro_status_code=''C''
128 And mshr.origination_type= 77
129 AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
130 AND t1.sr_instance_id(+)= mshr.sr_instance_id ';
131
132 if MSC_CL_COLLECTION.v_is_legacy_refresh then
133 lv_del_stmt := lv_del_stmt || ' And mshr.ENTITY=''IRO''' ;
134 else
135 lv_del_stmt := lv_del_stmt || ' And mshr.organization_id '||MSC_UTIL.v_depot_org_str;
136 end if ;
137
138 lv_del_stmt2 := 'SELECT
139 mshr.REPAIR_LINE_ID,
140 mshr.SR_INSTANCE_ID,
141 t1.INVENTORY_ITEM_ID,
142 mshr.ORGANIZATION_ID,
143 mshr.OPERATION_SEQ_NUM,
144 mshr.ORIGINATION_TYPE,
145 mshr.WIP_ENTITY_ID,
146 mshr.wip_entity_name
147 FROM MSC_ST_DEMANDS mshr,
148 msc_item_id_lid t1
149 WHERE mshr.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
150 ||' AND mshr.DELETED_FLAG= '||MSC_UTIL.SYS_YES
151 ||' AND mshr.ORIGINATION_TYPE=77
152 AND t1.SR_INVENTORY_ITEM_ID(+)= mshr.inventory_item_id
153 AND t1.sr_instance_id(+)= mshr.sr_instance_id ';
154
155
156 if MSC_CL_COLLECTION.v_is_legacy_refresh then
157 lv_del_stmt2 := lv_del_stmt2 || ' And mshr.ENTITY=''IRO''' ;
158 else
159 lv_del_stmt2 := lv_del_stmt2 || 'And mshr.organization_id '||MSC_UTIL.v_depot_org_str;
160 end if ;
161
162 lv_cursor_stmt:=
163 'SELECT'
164 ||' -1, MSC_DEMANDS_S.nextval, '
165 ||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
166 ||' t1.INVENTORY_ITEM_ID,'
167 ||' msdd.ORGANIZATION_ID,'
168 ||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
169 ||' nvl(msdd.USING_ASSEMBLY_DEMAND_DATE,ms.new_schedule_date),'
170 ||' msdd.USING_REQUIREMENT_QUANTITY,'
171 ||' msdd.QUANTITY_PER_ASSEMBLY,'
172 ||' msdd.QUANTITY_ISSUED,'
173 ||' msdd.ASSEMBLY_DEMAND_COMP_DATE,'
174 ||' msdd.DEMAND_TYPE,'
175 ||' msdd.ORIGINATION_TYPE,'
176 ||' msdd.SOURCE_ORGANIZATION_ID,'
177 ||' msdd.RESERVATION_ID,'
178 ||' msdd.OPERATION_SEQ_NUM,'
179 ||' msdd.DEMAND_CLASS,'
180 ||' msdd.REPETITIVE_SCHEDULE_ID,'
181 ||' msdd.SR_INSTANCE_ID,'
182 ||' msdd.PROJECT_ID,'
183 ||' msdd.TASK_ID,'
184 ||' msdd.PLANNING_GROUP,'
185 ||' msdd.END_ITEM_UNIT_NUMBER, '
186 ||' REPLACE(REPLACE(substr(msdd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
187 ||' REPAIR_LINE_ID ,'
188 ||' msdd.WIP_ENTITY_ID,'
189 ||' msdd.WIP_ENTITY_NAME,'
190 ||' msdd.WIP_STATUS_CODE,'
191 ||' msdd.WIP_SUPPLY_TYPE,'
192 ||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
193 ||' msdd.ASSET_SERIAL_NUMBER,' /* ds change change*/
194 ||' msdd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
195 ||' msdd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
196 || lv_data_sql_stmt
197 ||' :v_last_collection_id,'
198 ||' :v_current_date,'
199 ||' :v_current_user,'
200 ||' :v_current_date,'
201 ||' :v_current_user '
202 ||' FROM MSC_ITEM_ID_LID t1,'
203 ||' MSC_ITEM_ID_LID t2,'
204 ||' MSC_ITEM_ID_LID t3,'
205 || lv_supplies_tbl||' ms,'
206 ||' MSC_ST_DEMANDS msdd'
207 ||' WHERE msdd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
208 ||' AND msdd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change SRP Change 5909379*/
209 ||' AND msdd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
210 ||' AND t1.SR_INVENTORY_ITEM_ID= msdd.inventory_item_id'
211 ||' AND t1.sr_instance_id= msdd.SR_INSTANCE_ID'
212 ||' AND t2.SR_INVENTORY_ITEM_ID= msdd.using_assembly_item_id'
213 ||' AND t2.sr_instance_id= msdd.SR_INSTANCE_ID'
214 ||' AND t3.SR_INVENTORY_ITEM_ID (+)= msdd.ASSET_ITEM_ID'
215 ||' AND t3.sr_instance_id (+) = msdd.SR_INSTANCE_ID'
216 ||' AND ms.sr_instance_id= msdd.SR_INSTANCE_ID'
217 ||' AND ms.ORGANIZATION_ID= msdd.ORGANIZATION_ID'
218 ||' AND ms.DISPOSITION_ID= msdd.repair_line_id '
219 ||' AND ms.plan_id=-1'
220 ||' AND ms.ORDER_TYPE= 75'; /* ds change change*/
221
222 lv_sql_stmt:=
223 'INSERT /*+ APPEND */ INTO '||lv_tbl
224 ||'( PLAN_ID,'
225 ||' DEMAND_ID,'
226 ||' DISPOSITION_ID,'
227 ||' INVENTORY_ITEM_ID,'
228 ||' ORGANIZATION_ID,'
229 ||' USING_ASSEMBLY_ITEM_ID,'
230 ||' USING_ASSEMBLY_DEMAND_DATE,'
231 ||' USING_REQUIREMENT_QUANTITY,'
232 ||' QUANTITY_PER_ASSEMBLY,'
233 ||' ISSUED_QUANTITY,'
234 ||' ASSEMBLY_DEMAND_COMP_DATE,'
235 ||' DEMAND_TYPE,'
236 ||' ORIGINATION_TYPE,'
237 ||' SOURCE_ORGANIZATION_ID,'
238 ||' RESERVATION_ID,'
239 ||' OP_SEQ_NUM,'
240 ||' DEMAND_CLASS,'
241 ||' REPETITIVE_SCHEDULE_ID,'
242 ||' SR_INSTANCE_ID,'
243 ||' PROJECT_ID,'
244 ||' TASK_ID,'
245 ||' PLANNING_GROUP,'
246 ||' UNIT_NUMBER,'
247 ||' ORDER_NUMBER,'
248 ||' REPAIR_LINE_ID,'
249 ||' WIP_ENTITY_ID,'
250 ||' WIP_ENTITY_NAME,'
251 ||' WIP_STATUS_CODE,'
252 ||' WIP_SUPPLY_TYPE,'
253 ||' ASSET_ITEM_ID,'
254 ||' ASSET_SERIAL_NUMBER,'
255 ||' COMPONENT_SCALING_TYPE,'
256 ||' COMPONENT_YIELD_FACTOR,'
257 || lv_sel_sql_stmt
258 ||' REFRESH_NUMBER,'
259 ||' LAST_UPDATE_DATE,'
260 ||' LAST_UPDATED_BY,'
261 ||' CREATION_DATE,'
262 ||' CREATED_BY)'
263 || lv_cursor_stmt;
264
265
266 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN -- incremental Refresh
267
268
269
270 --=================================================
271
272 Open C10_d for lv_del_stmt;
273 Loop
274
275
276 FETCH C10_d INTO
277 lv_REPAIR_LINE_ID ,
278 lv_INVENTORY_ITEM_ID ,
279 lv_ORGANIZATION_ID ,
280 lv_ORIGINATION_TYPE ,
281 lv_SR_INSTANCE_ID ;
282
283
284
285 EXIT WHEN C10_d%NOTFOUND;
286
287 Delete from msc_demands
288 WHERE PLAN_ID= -1
289 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
290 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
291 AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
292 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
293 AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);
294
295
296 END LOOP;
297
298 CLOSE C10_d;
299
300
301
302 --=======================================
303 Open c11_d for lv_del_stmt2;
304 LOOP
305
306
307
308 FETCH C11_d INTO
309 lv_REPAIR_LINE_ID ,
310 lv_SR_INSTANCE_ID ,
311 lv_INVENTORY_ITEM_ID ,
312 lv_ORGANIZATION_ID ,
313 lv_OPERATION_SEQ_NUM ,
314 lv_ORIGINATION_TYPE ,
315 lv_WIP_ENTITY_ID ,
316 lv_WIP_ENTITY_NAME ;
317
318
319 EXIT WHEN C11_d%NOTFOUND;
320 if MSC_CL_COLLECTION.v_is_legacy_refresh then
321 Delete from msc_demands
322 WHERE PLAN_ID= -1
323 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
324 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
325 AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
326 AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
327 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
328 AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
329 AND WIP_ENTITY_NAME = lv_WIP_ENTITY_NAME ;
330 else
331 Delete from msc_demands
332 WHERE PLAN_ID= -1
333 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
334 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
335 AND REPAIR_LINE_ID= lv_REPAIR_LINE_ID
336 AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
337 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
338 AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID)
339 AND WIP_ENTITY_ID = lv_WIP_ENTITY_ID ;
340 end if ;
341
342
343
344 END LOOP;
345 CLOSE C11_d;
346 --=============================================
347
348 /* Opening The cursor ... Perofrom Update ... If not found the n inser ... row operation */
349
350
351 OPEN c2 FOR lv_cursor_stmt USING MSC_CL_COLLECTION.v_chr10,
352 MSC_CL_COLLECTION.v_chr13,
353 MSC_CL_COLLECTION.v_last_collection_id,
354 MSC_CL_COLLECTION.v_current_date,
355 MSC_CL_COLLECTION.v_current_user,
356 MSC_CL_COLLECTION.v_current_date,
357 MSC_CL_COLLECTION.v_current_user;
358
359 LOOP
360
361 FETCH c2 INTO
362 lv_PLAN_ID ,
363 lv_DEMAND_ID ,
364 lv_DISPOSITION_ID ,
365 lv_INVENTORY_ITEM_ID ,
366 lv_ORGANIZATION_ID ,
367 lv_USING_ASSEMBLY_ITEM_ID ,
368 lv_USING_ASSEMBLY_DEMAND_DATE ,
369 lv_USING_REQUIREMENT_QUANTITY ,
370 lv_QUANTITY_PER_ASSEMBLY ,
371 lv_QUANTITY_ISSUED ,
372 lv_ASSEMBLY_DEMAND_COMP_DATE ,
373 lv_DEMAND_TYPE ,
374 lv_ORIGINATION_TYPE ,
375 lv_SOURCE_ORGANIZATION_ID ,
376 lv_RESERVATION_ID ,
377 lv_OPERATION_SEQ_NUM ,
378 lv_DEMAND_CLASS ,
379 lv_REPETITIVE_SCHEDULE_ID ,
380 lv_SR_INSTANCE_ID ,
381 lv_PROJECT_ID ,
382 lv_TASK_ID ,
383 lv_PLANNING_GROUP ,
384 lv_END_ITEM_UNIT_NUMBER ,
385 lv_ORDER_NUMBER ,
386 lv_REPAIR_LINE_ID ,
387 lv_WIP_ENTITY_ID ,
388 lv_WIP_ENTITY_NAME ,
389 lv_WIP_STATUS_CODE ,
390 lv_WIP_SUPPLY_TYPE ,
391 lv_ASSET_ITEM_ID ,
392 lv_ASSET_SERIAL_NUMBER ,
393 lv_COMPONENT_SCALING_TYPE ,
394 lv_COMPONENT_YIELD_FACTOR ,
395 lv_dummy1 ,
396 lv_ITEM_TYPE_VALUE ,
397 lv_last_collection_id ,
398 lv_dummy_date ,
399 lv_dummy_user ,
400 lv_dummy_date ,
401 lv_dummy_user ;
402
403
404 EXIT WHEN c2%NOTFOUND;
405
406 BEGIN
407 if MSC_CL_COLLECTION.v_is_legacy_refresh then
408 Update MSC_DEMANDS
409 Set
410 USING_ASSEMBLY_ITEM_ID = lv_USING_ASSEMBLY_ITEM_ID ,
411 USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE ,
412 USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY ,
413 QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY ,
414 ISSUED_QUANTITY = lv_QUANTITY_ISSUED ,
415 ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE ,
416 DEMAND_TYPE = lv_DEMAND_TYPE ,
417 SOURCE_ORGANIZATION_ID = lv_SOURCE_ORGANIZATION_ID ,
418 RESERVATION_ID = lv_RESERVATION_ID ,
419 DEMAND_CLASS = lv_DEMAND_CLASS ,
420 REPETITIVE_SCHEDULE_ID = lv_REPETITIVE_SCHEDULE_ID ,
421 PROJECT_ID = lv_PROJECT_ID ,
422 TASK_ID = lv_TASK_ID ,
423 PLANNING_GROUP = lv_PLANNING_GROUP ,
424 ORDER_NUMBER = lv_ORDER_NUMBER ,
425 WIP_STATUS_CODE = lv_WIP_STATUS_CODE ,
426 WIP_SUPPLY_TYPE = lv_WIP_SUPPLY_TYPE ,
427 ASSET_ITEM_ID = lv_ASSET_ITEM_ID ,
428 ASSET_SERIAL_NUMBER = lv_ASSET_SERIAL_NUMBER ,
429 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE ,
430 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
431 WHERE PLAN_ID= -1
432 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
433 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
434 AND DISPOSITION_ID= lv_DISPOSITION_ID
435 AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
436 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
437 AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
438 AND WIP_ENTITY_NAME = lv_WIP_ENTITY_NAME
439 AND ITEM_TYPE_VALUE = lv_ITEM_TYPE_VALUE;
440 ELSE
441 Update MSC_DEMANDS
442 Set
443 USING_ASSEMBLY_ITEM_ID = lv_USING_ASSEMBLY_ITEM_ID ,
444 USING_ASSEMBLY_DEMAND_DATE = lv_USING_ASSEMBLY_DEMAND_DATE ,
445 USING_REQUIREMENT_QUANTITY = lv_USING_REQUIREMENT_QUANTITY ,
446 QUANTITY_PER_ASSEMBLY = lv_QUANTITY_PER_ASSEMBLY ,
447 ISSUED_QUANTITY = lv_QUANTITY_ISSUED ,
448 ASSEMBLY_DEMAND_COMP_DATE = lv_ASSEMBLY_DEMAND_COMP_DATE ,
449 DEMAND_TYPE = lv_DEMAND_TYPE ,
450 SOURCE_ORGANIZATION_ID = lv_SOURCE_ORGANIZATION_ID ,
451 RESERVATION_ID = lv_RESERVATION_ID ,
452 DEMAND_CLASS = lv_DEMAND_CLASS ,
453 REPETITIVE_SCHEDULE_ID = lv_REPETITIVE_SCHEDULE_ID ,
454 PROJECT_ID = lv_PROJECT_ID ,
455 TASK_ID = lv_TASK_ID ,
456 PLANNING_GROUP = lv_PLANNING_GROUP ,
457 ORDER_NUMBER = lv_ORDER_NUMBER ,
458 WIP_STATUS_CODE = lv_WIP_STATUS_CODE ,
459 WIP_SUPPLY_TYPE = lv_WIP_SUPPLY_TYPE ,
460 ASSET_ITEM_ID = lv_ASSET_ITEM_ID ,
461 ASSET_SERIAL_NUMBER = lv_ASSET_SERIAL_NUMBER ,
462 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE ,
463 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR
464 WHERE PLAN_ID= -1
465 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
466 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
467 AND DISPOSITION_ID= lv_DISPOSITION_ID
468 AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
469 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
470 AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID
471 AND WIP_ENTITY_ID = lv_WIP_ENTITY_ID ;
472 END IF ;
473 IF SQL%NOTFOUND THEN
474
475 -- ========= Prepare SQL Statement for INSERT ==========
476 lv_insert_stmt:=
477 'INSERT INTO '||lv_tbl
478 ||'( PLAN_ID,'
479 ||' DEMAND_ID,'
480 ||' INVENTORY_ITEM_ID,'
481 ||' ORGANIZATION_ID,'
482 ||' USING_ASSEMBLY_ITEM_ID,'
483 ||' USING_ASSEMBLY_DEMAND_DATE,'
484 ||' USING_REQUIREMENT_QUANTITY,'
485 ||' QUANTITY_PER_ASSEMBLY,'
486 ||' ISSUED_QUANTITY,'
487 ||' ASSEMBLY_DEMAND_COMP_DATE,'
488 ||' DEMAND_TYPE,'
489 ||' ORIGINATION_TYPE,'
490 ||' SOURCE_ORGANIZATION_ID,'
491 ||' DISPOSITION_ID,'
492 ||' RESERVATION_ID,'
493 ||' OP_SEQ_NUM,'
494 ||' DEMAND_CLASS,'
495 ||' SR_INSTANCE_ID,'
496 ||' PROJECT_ID,'
497 ||' TASK_ID,'
498 ||' PLANNING_GROUP,'
499 ||' UNIT_NUMBER,'
500 ||' ORDER_NUMBER,'
501 ||' REPAIR_LINE_ID,'
502 ||' WIP_ENTITY_ID,'
503 ||' WIP_ENTITY_NAME,'
504 ||' WIP_STATUS_CODE,'
505 ||' WIP_SUPPLY_TYPE,'
506 ||' REPETITIVE_SCHEDULE_ID,'
507 ||' ASSET_ITEM_ID,'
508 ||' ASSET_SERIAL_NUMBER,'
509 ||' COMPONENT_SCALING_TYPE,'
510 ||' COMPONENT_YIELD_FACTOR,'
511 ||' ITEM_TYPE_ID,'
512 ||' ITEM_TYPE_VALUE,'
513 ||' REFRESH_NUMBER,'
514 ||' LAST_UPDATE_DATE,'
515 ||' LAST_UPDATED_BY,'
516 ||' CREATION_DATE,'
517 ||' CREATED_BY)'
518 ||'VALUES'
519 ||'( -1,'
520 ||' MSC_DEMANDS_S.nextval,'
521 ||' :INVENTORY_ITEM_ID,'
522 ||' :ORGANIZATION_ID,'
523 ||' :USING_ASSEMBLY_ITEM_ID,'
524 ||' :USING_ASSEMBLY_DEMAND_DATE,'
525 ||' :USING_REQUIREMENT_QUANTITY,'
526 ||' :QUANTITY_PER_ASSEMBLY,'
527 ||' :ISSUED_QUANTITY,'
528 ||' :ASSEMBLY_DEMAND_COMP_DATE,'
529 ||' :DEMAND_TYPE,'
530 ||' :ORIGINATION_TYPE,'
531 ||' :SOURCE_ORGANIZATION_ID,'
532 ||' :DISPOSITION_ID,'
533 ||' :RESERVATION_ID,'
534 ||' :OPERATION_SEQ_NUM,'
535 ||' :DEMAND_CLASS,'
536 ||' :SR_INSTANCE_ID,'
537 ||' :PROJECT_ID,'
538 ||' :TASK_ID,'
539 ||' :PLANNING_GROUP,'
540 ||' :END_ITEM_UNIT_NUMBER, '
541 ||' :ORDER_NUMBER,'
542 ||' :REPAIR_LINE_ID,'
543 ||' :WIP_ENTITY_ID,'
544 ||' :WIP_ENTITY_NAME,'
545 ||' :WIP_STATUS_CODE,'
546 ||' :WIP_SUPPLY_TYPE,'
547 ||' :REPETITIVE_SCHEDULE_ID,'
548 ||' :ASSET_ITEM_ID,'
549 ||' :ASSET_SERIAL_NUMBER,'
550 ||' :COMPONENT_SCALING_TYPE,'
551 ||' :COMPONENT_YIELD_FACTOR,'
552 ||' :ITEM_TYPE_ID,'
553 ||' :ITEM_TYPE_VALUE,'
554 ||' :v_last_collection_id,'
555 ||' :v_current_date,'
556 ||' :v_current_user,'
557 ||' :v_current_date,'
558 ||' :v_current_user )';
559
560 EXECUTE IMMEDIATE lv_insert_stmt
561 USING
562 lv_INVENTORY_ITEM_ID,
563 lv_ORGANIZATION_ID,
564 lv_USING_ASSEMBLY_ITEM_ID,
565 lv_USING_ASSEMBLY_DEMAND_DATE,
566 lv_USING_REQUIREMENT_QUANTITY,
567 lv_QUANTITY_PER_ASSEMBLY,
568 lv_QUANTITY_ISSUED,
569 lv_ASSEMBLY_DEMAND_COMP_DATE,
570 lv_DEMAND_TYPE,
571 lv_ORIGINATION_TYPE,
572 lv_SOURCE_ORGANIZATION_ID,
573 lv_DISPOSITION_ID,
574 lv_RESERVATION_ID,
575 lv_OPERATION_SEQ_NUM,
576 lv_DEMAND_CLASS,
577 lv_SR_INSTANCE_ID,
578 lv_PROJECT_ID,
579 lv_TASK_ID,
580 lv_PLANNING_GROUP,
581 lv_END_ITEM_UNIT_NUMBER,
582 lv_ORDER_NUMBER,
583 lv_REPAIR_LINE_ID,
584 lv_WIP_ENTITY_ID,
585 lv_WIP_ENTITY_NAME,
586 lv_WIP_STATUS_CODE,
587 lv_WIP_SUPPLY_TYPE,
588 lv_REPETITIVE_SCHEDULE_ID,
589 lv_ASSET_ITEM_ID,
590 lv_ASSET_SERIAL_NUMBER,
591 lv_COMPONENT_SCALING_TYPE,
592 lv_COMPONENT_YIELD_FACTOR,
593 lv_ITEM_TYPE_ID,
594 lv_ITEM_TYPE_VALUE,
595 MSC_CL_COLLECTION.v_last_collection_id,
596 MSC_CL_COLLECTION.v_current_date,
597 MSC_CL_COLLECTION.v_current_user,
598 MSC_CL_COLLECTION.v_current_date,
599 MSC_CL_COLLECTION.v_current_user;
600
601 END IF;
602 EXCEPTION
603
604 WHEN OTHERS THEN
605
606 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
607
608 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
609 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_IRO_DEMAND');
610 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
611 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
612 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
613 RAISE;
614 END;
615
616 END LOOP;
617
618 END IF; -- incremental Refresh
619
620 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
621
622 BEGIN
623
624
625 EXECUTE IMMEDIATE lv_sql_stmt
626 USING
627 MSC_CL_COLLECTION.v_chr10,
628 MSC_CL_COLLECTION.v_chr13,
629 MSC_CL_COLLECTION.v_last_collection_id,
630 MSC_CL_COLLECTION.v_current_date,
631 MSC_CL_COLLECTION.v_current_user,
632 MSC_CL_COLLECTION.v_current_date,
633 MSC_CL_COLLECTION.v_current_user;
634
635
636 COMMIT;
637
638
639 EXCEPTION
640 WHEN OTHERS THEN
641
642 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_IRO_DEMAND>>');
643 IF lv_sql_stmt IS NOT NULL THEN
644 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
645 END IF;
646 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
647 -- log_message ('Error Occured'||SQLERRM);
648
649
650 END;
651 END IF; -- Comp Collection
652 END LOAD_IRO_DEMAND;
653
654 PROCEDURE LOAD_ERO_DEMAND IS
655 lv_sql_stmt VARCHAR2(10240);
656 lv_cursor_stmt VARCHAR2(10240);
657 lv_del_stmt VARCHAR2(10240);
658 c_count NUMBER:=0;
659 lv_tbl VARCHAR2(30);
660 lv_supplies_tbl VARCHAR2(30);
661 lv_ITEM_TYPE_VALUE NUMBER;
662 lv_ITEM_TYPE_ID NUMBER;
663
664 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type
665 c2 CurTyp;
666 c2_d CurTyp;
667
668 /* CURSOR c2_d IS
669 SELECT msd.WIP_ENTITY_ID,
670 msd.OPERATION_SEQ_NUM,
671 t1.INVENTORY_ITEM_ID,
672 msd.ORIGINATION_TYPE,
673 msd.SR_INSTANCE_ID,
674 msd.ORGANIZATION_ID
675 FROM MSC_ITEM_ID_LID t1,
676 MSC_ST_DEMANDS msd
677 WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
678 AND msd.ORIGINATION_TYPE = 77
679 AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
680 AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
681 AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;*/
682
683 lv_DISPOSITION_ID NUMBER;
684 lv_INVENTORY_ITEM_ID NUMBER;
685 lv_ORGANIZATION_ID NUMBER;
686 lv_USING_ASSEMBLY_ITEM_ID NUMBER;
687 lv_USING_ASSEMBLY_DEMAND_DATE DATE;
688 lv_USING_REQUIREMENT_QUANTITY NUMBER;
689 lv_QUANTITY_PER_ASSEMBLY NUMBER;
690 lv_QUANTITY_ISSUED NUMBER;
691 lv_ASSEMBLY_DEMAND_COMP_DATE DATE;
692 lv_DEMAND_TYPE NUMBER;
693 lv_ORIGINATION_TYPE NUMBER;
694 lv_SOURCE_ORGANIZATION_ID NUMBER;
695 lv_RESERVATION_ID NUMBER;
696 lv_OPERATION_SEQ_NUM NUMBER;
697 lv_DEMAND_CLASS VARCHAR2(34);
698 lv_REPETITIVE_SCHEDULE_ID NUMBER;
699 lv_ASSET_ITEM_ID NUMBER;
700 lv_ASSET_SERIAL_NUMBER VARCHAR2(30);
701 lv_SR_INSTANCE_ID NUMBER;
702 lv_PROJECT_ID NUMBER;
703 lv_TASK_ID NUMBER;
704 lv_PLANNING_GROUP VARCHAR2(30);
705 lv_END_ITEM_UNIT_NUMBER VARCHAR2(30);
706 lv_COMPONENT_SCALING_TYPE NUMBER;
707 lv_COMPONENT_YIELD_FACTOR NUMBER;
708 lv_ORDER_NUMBER VARCHAR2(240);
709 lv_WIP_ENTITY_ID NUMBER;
710 lv_WIP_STATUS_CODE NUMBER;
711 lv_WIP_SUPPLY_TYPE NUMBER;
712 lv_DELETED_FLAG NUMBER;
713 lv_sel_sql_stmt VARCHAR2(1000);
714 lv_data_sql_stmt VARCHAR2(1000);
715
716 BEGIN
717
718 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN -- incremental Refresh
719
720 lv_del_stmt := 'SELECT msd1.WIP_ENTITY_ID,
721 msd1.OPERATION_SEQ_NUM,
722 t1.INVENTORY_ITEM_ID,
723 msd1.ORIGINATION_TYPE,
724 msd1.SR_INSTANCE_ID,
725 msd1.ORGANIZATION_ID
726 FROM MSC_ITEM_ID_LID t1,
727 MSC_ST_DEMANDS msd1
728 WHERE msd1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
729 ||' AND msd1.ORIGINATION_TYPE = 77
730 AND msd1.DELETED_FLAG= '|| MSC_UTIL.SYS_YES
731 ||' AND t1.SR_INVENTORY_ITEM_ID(+)= msd1.inventory_item_id
732 AND t1.sr_instance_id(+)= '||MSC_CL_COLLECTION.v_instance_id ;
733
734 if MSC_CL_COLLECTION.v_is_legacy_refresh then
735 lv_del_stmt:=lv_del_stmt ||' AND msd1.ENTITY=''ERO''';
736 else
737 lv_del_stmt:=lv_del_stmt ||' AND msd1.ORGANIZATION_ID '||MSC_UTIL.v_non_depot_org_str;
738 end if ;
739
740
741 OPEN c2_d for lv_del_stmt;
742 LOOP
743
744 -- FOR c_rec IN c2_d LOOP
745 FETCH c2_d into
746 lv_WIP_ENTITY_ID,
747 lv_OPERATION_SEQ_NUM,
748 lv_INVENTORY_ITEM_ID,
749 lv_ORIGINATION_TYPE,
750 lv_SR_INSTANCE_ID,
751 lv_ORGANIZATION_ID;
752
753 EXIT WHEN c2_d%NOTFOUND;
754
755 UPDATE MSC_DEMANDS
756 SET USING_REQUIREMENT_QUANTITY= 0,
757 DAILY_DEMAND_RATE= 0,
758 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
759 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
760 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
761 WHERE PLAN_ID= -1
762 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
763 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
764 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
765 AND OP_SEQ_NUM= NVL(lv_OPERATION_SEQ_NUM,OP_SEQ_NUM)
766 AND ORGANIZATION_ID = lv_ORGANIZATION_ID
767 AND INVENTORY_ITEM_ID= NVL(lv_INVENTORY_ITEM_ID,INVENTORY_ITEM_ID);
768
769 END LOOP;
770 CLOSE c2_d;
771 END IF; -- Type of refresh
772
773 c_count:=0;
774
775 -- ========= Prepare the Cursor Statement ==========
776 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
777 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
778 lv_supplies_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
779 ELSE
780 lv_tbl:= 'MSC_DEMANDS';
781 lv_supplies_tbl:= 'MSC_SUPPLIES';
782 END IF;
783
784
785 IF MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' THEN
786 lv_ITEM_TYPE_ID := MSC_UTIL.G_PARTCONDN_ITEMTYPEID;
787 lv_ITEM_TYPE_VALUE := MSC_UTIL.G_PARTCONDN_BAD;
788 lv_sel_sql_stmt := 'ITEM_TYPE_ID,ITEM_TYPE_VALUE,';
789 lv_data_sql_stmt := lv_ITEM_TYPE_ID||','||lv_ITEM_TYPE_VALUE||',';
790 ELSE
791 lv_ITEM_TYPE_ID := NULL;
792 lv_ITEM_TYPE_VALUE := NULL;
793 lv_sel_sql_stmt := NULL;
794 lv_data_sql_stmt := NULL;
795 END IF;
796
797
798 lv_cursor_stmt:=
799 'SELECT'
800 ||' -1, MSC_DEMANDS_S.nextval, '
801 ||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
802 ||' t1.INVENTORY_ITEM_ID,'
803 ||' msd.ORGANIZATION_ID,'
804 ||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
805 ||' msd.USING_ASSEMBLY_DEMAND_DATE,'
806 ||' msd.USING_REQUIREMENT_QUANTITY,'
807 ||' msd.QUANTITY_PER_ASSEMBLY,'
808 ||' msd.QUANTITY_ISSUED,'
809 ||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
810 ||' msd.DEMAND_TYPE,'
811 ||' msd.ORIGINATION_TYPE,'
812 ||' msd.SOURCE_ORGANIZATION_ID,'
813 ||' msd.RESERVATION_ID,'
814 ||' msd.OPERATION_SEQ_NUM,'
815 ||' msd.DEMAND_CLASS,'
816 ||' msd.REPETITIVE_SCHEDULE_ID,'
817 ||' msd.SR_INSTANCE_ID,'
818 ||' msd.PROJECT_ID,'
819 ||' msd.TASK_ID,'
820 ||' msd.PLANNING_GROUP,'
821 ||' msd.END_ITEM_UNIT_NUMBER, '
822 ||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
823 ||' msd.WIP_ENTITY_ID,'
824 ||' msd.WIP_STATUS_CODE,'
825 ||' msd.WIP_SUPPLY_TYPE,'
826 ||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
827 ||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
828 ||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
829 ||' msd.COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4479743 */
830 || lv_data_sql_stmt
831 ||' :v_last_collection_id,'
832 ||' :v_current_date,'
833 ||' :v_current_user,'
834 ||' :v_current_date,'
835 ||' :v_current_user '
836 ||' FROM MSC_ITEM_ID_LID t1,'
837 ||' MSC_ITEM_ID_LID t2,'
838 ||' MSC_ITEM_ID_LID t3,'
839 || lv_supplies_tbl||' ms,'
840 ||' MSC_ST_DEMANDS msd'
841 ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
842 ||' AND msd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change SRP Change 5909379*/
843 ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
844 ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
845 ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
846 ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
847 ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
848 ||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
849 ||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
850 ||' AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
851 ||' AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
852 ||' AND ms.DISPOSITION_ID= msd.wip_entity_id '
853 ||' AND ms.plan_id=-1'
854 ||' AND ms.ORDER_TYPE= 86'; /* ds change change*/
855
856 IF NOT MSC_CL_COLLECTION.v_is_incremental_refresh THEN
857 lv_sql_stmt:=
858 'INSERT /*+ APPEND */ INTO '||lv_tbl
859 ||'( PLAN_ID,'
860 ||' DEMAND_ID,'
861 ||' DISPOSITION_ID,'
862 ||' INVENTORY_ITEM_ID,'
863 ||' ORGANIZATION_ID,'
864 ||' USING_ASSEMBLY_ITEM_ID,'
865 ||' USING_ASSEMBLY_DEMAND_DATE,'
866 ||' USING_REQUIREMENT_QUANTITY,'
867 ||' QUANTITY_PER_ASSEMBLY,'
868 ||' ISSUED_QUANTITY,'
869 ||' ASSEMBLY_DEMAND_COMP_DATE,'
870 ||' DEMAND_TYPE,'
871 ||' ORIGINATION_TYPE,'
872 ||' SOURCE_ORGANIZATION_ID,'
873 ||' RESERVATION_ID,'
874 ||' OP_SEQ_NUM,'
875 ||' DEMAND_CLASS,'
876 ||' REPETITIVE_SCHEDULE_ID,'
877 ||' SR_INSTANCE_ID,'
878 ||' PROJECT_ID,'
879 ||' TASK_ID,'
880 ||' PLANNING_GROUP,'
881 ||' UNIT_NUMBER,'
882 ||' ORDER_NUMBER,'
883 ||' WIP_ENTITY_ID,'
884 ||' WIP_STATUS_CODE,'
885 ||' WIP_SUPPLY_TYPE,'
886 ||' ASSET_ITEM_ID,'
887 ||' ASSET_SERIAL_NUMBER,'
888 ||' COMPONENT_SCALING_TYPE,'
889 ||' COMPONENT_YIELD_FACTOR,'
890 || lv_sel_sql_stmt
891 ||' REFRESH_NUMBER,'
892 ||' LAST_UPDATE_DATE,'
893 ||' LAST_UPDATED_BY,'
894 ||' CREATION_DATE,'
895 ||' CREATED_BY)'
896 || lv_cursor_stmt;
897
898
899
900 BEGIN
901
902
903 EXECUTE IMMEDIATE lv_sql_stmt
904 USING
905 MSC_CL_COLLECTION.v_chr10,
906 MSC_CL_COLLECTION.v_chr13,
907 MSC_CL_COLLECTION.v_last_collection_id,
908 MSC_CL_COLLECTION.v_current_date,
909 MSC_CL_COLLECTION.v_current_user,
910 MSC_CL_COLLECTION.v_current_date,
911 MSC_CL_COLLECTION.v_current_user;
912
913
914 COMMIT;
915
916
917 EXCEPTION
918 WHEN OTHERS THEN
919
920 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_ERO_DEMAND>>');
921 IF lv_sql_stmt IS NOT NULL THEN
922 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'|| lv_sql_stmt);
923 END IF;
924 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
925 -- log_message ('Error Occured'||SQLERRM);
926
927 END;
928 END IF; --v_is_complete_refresh
929
930 --==========================
931 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN -- incremental Refresh
932 -- ========= Prepare SQL Statement for INSERT ==========
933 lv_sql_stmt:=
934 'INSERT INTO '||lv_tbl
935 ||'( PLAN_ID,'
936 ||' DEMAND_ID,'
937 ||' INVENTORY_ITEM_ID,'
938 ||' ORGANIZATION_ID,'
939 ||' USING_ASSEMBLY_ITEM_ID,'
940 ||' USING_ASSEMBLY_DEMAND_DATE,'
941 ||' USING_REQUIREMENT_QUANTITY,'
942 ||' QUANTITY_PER_ASSEMBLY,'
943 ||' ISSUED_QUANTITY,'
944 ||' ASSEMBLY_DEMAND_COMP_DATE,'
945 ||' DEMAND_TYPE,'
946 ||' ORIGINATION_TYPE,'
947 ||' SOURCE_ORGANIZATION_ID,'
948 ||' DISPOSITION_ID,'
949 ||' RESERVATION_ID,'
950 ||' OP_SEQ_NUM,'
951 ||' DEMAND_CLASS,'
952 ||' SR_INSTANCE_ID,'
953 ||' PROJECT_ID,'
954 ||' TASK_ID,'
955 ||' PLANNING_GROUP,'
956 ||' UNIT_NUMBER,'
957 ||' ORDER_NUMBER,'
958 ||' WIP_ENTITY_ID,'
959 ||' WIP_STATUS_CODE,'
960 ||' WIP_SUPPLY_TYPE,'
961 ||' REPETITIVE_SCHEDULE_ID,'
962 ||' ASSET_ITEM_ID,' /* ds change change*/
963 ||' ASSET_SERIAL_NUMBER,' /* ds change change*/
964 ||' COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
965 ||' COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
966 ||' ITEM_TYPE_ID,'
967 ||' ITEM_TYPE_VALUE,'
968 ||' REFRESH_NUMBER,'
969 ||' LAST_UPDATE_DATE,'
970 ||' LAST_UPDATED_BY,'
971 ||' CREATION_DATE,'
972 ||' CREATED_BY)'
973 ||'VALUES'
974 ||'( -1,'
975 ||' MSC_DEMANDS_S.nextval,'
976 ||' :INVENTORY_ITEM_ID,'
977 ||' :ORGANIZATION_ID,'
978 ||' :USING_ASSEMBLY_ITEM_ID,'
979 ||' :USING_ASSEMBLY_DEMAND_DATE,'
980 ||' :USING_REQUIREMENT_QUANTITY,'
981 ||' :QUANTITY_PER_ASSEMBLY,'
982 ||' :ISSUED_QUANTITY,'
983 ||' :ASSEMBLY_DEMAND_COMP_DATE,'
984 ||' :DEMAND_TYPE,'
985 ||' :ORIGINATION_TYPE,'
986 ||' :SOURCE_ORGANIZATION_ID,'
987 ||' :DISPOSITION_ID,'
988 ||' :RESERVATION_ID,'
989 ||' :OPERATION_SEQ_NUM,'
990 ||' :DEMAND_CLASS,'
991 ||' :SR_INSTANCE_ID,'
992 ||' :PROJECT_ID,'
993 ||' :TASK_ID,'
994 ||' :PLANNING_GROUP,'
995 ||' :END_ITEM_UNIT_NUMBER, '
996 ||' :ORDER_NUMBER,'
997 ||' :WIP_ENTITY_ID,'
998 ||' :WIP_STATUS_CODE,'
999 ||' :WIP_SUPPLY_TYPE,'
1000 ||' :REPETITIVE_SCHEDULE_ID,'
1001 ||' :ASSET_ITEM_ID,' /* ds change change*/
1002 ||' :ASSET_SERIAL_NUMBER,' /* ds change change*/
1003 ||' :COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
1004 ||' :COMPONENT_YIELD_FACTOR,' /* Discrete Mfg Enahancements Bug 4492743 */
1005 ||' :ITEM_TYPE_ID,'
1006 ||' :ITEM_TYPE_VALUE,'
1007 ||' :v_last_collection_id,'
1008 ||' :v_current_date,'
1009 ||' :v_current_user,'
1010 ||' :v_current_date,'
1011 ||' :v_current_user )';
1012
1013 /* Cursor statement below is used in case of net change.
1014 This cursor will also load data in target/complete mode,
1015 if the bulk insert above failed for whatever reason */
1016
1017 lv_cursor_stmt:=
1018 'SELECT'
1019 ||' NVL(ms.TRANSACTION_ID,-1) DISPOSITION_ID,'
1020 ||' t1.INVENTORY_ITEM_ID,'
1021 ||' msd.ORGANIZATION_ID,'
1022 ||' t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,'
1023 ||' msd.USING_ASSEMBLY_DEMAND_DATE,'
1024 ||' msd.USING_REQUIREMENT_QUANTITY,'
1025 ||' msd.QUANTITY_PER_ASSEMBLY,'
1026 ||' msd.QUANTITY_ISSUED,'
1027 ||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
1028 ||' msd.DEMAND_TYPE,'
1029 ||' msd.ORIGINATION_TYPE,'
1030 ||' msd.SOURCE_ORGANIZATION_ID,'
1031 ||' msd.RESERVATION_ID,'
1032 ||' msd.OPERATION_SEQ_NUM,'
1033 ||' msd.DEMAND_CLASS,'
1034 ||' msd.REPETITIVE_SCHEDULE_ID,'
1035 ||' msd.SR_INSTANCE_ID,'
1036 ||' msd.PROJECT_ID,'
1037 ||' msd.TASK_ID,'
1038 ||' msd.PLANNING_GROUP,'
1039 ||' msd.END_ITEM_UNIT_NUMBER, '
1040 ||' REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),:v_chr10),:v_chr13) ORDER_NUMBER,'
1041 ||' msd.WIP_ENTITY_ID,'
1042 ||' msd.WIP_STATUS_CODE,'
1043 ||' msd.WIP_SUPPLY_TYPE,'
1044 ||' msd.DELETED_FLAG,'
1045 ||' t3.inventory_item_id ASSET_ITEM_ID,' /* ds change change*/
1046 ||' msd.ASSET_SERIAL_NUMBER,' /* ds change change*/
1047 ||' msd.COMPONENT_SCALING_TYPE,' /* Discrete Mfg Enahancements Bug 4492736 */
1048 ||' msd.COMPONENT_YIELD_FACTOR' /* Discrete Mfg Enahancements Bug 4492743 */
1049 ||' FROM MSC_ITEM_ID_LID t1,'
1050 ||' MSC_ITEM_ID_LID t2,'
1051 ||' MSC_ITEM_ID_LID t3,'
1052 || lv_supplies_tbl||' ms,'
1053 ||' MSC_ST_DEMANDS msd'
1054 ||' WHERE msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
1055 ||' AND msd.ORIGINATION_TYPE = 77' /* 50 eam demand: ds change change*/
1056 ||' AND msd.DELETED_FLAG= '||MSC_UTIL.SYS_NO
1057 ||' AND t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id'
1058 ||' AND t1.sr_instance_id= msd.SR_INSTANCE_ID'
1059 ||' AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id'
1060 ||' AND t2.sr_instance_id= msd.SR_INSTANCE_ID'
1061 ||' AND t3.SR_INVENTORY_ITEM_ID (+)= msd.ASSET_ITEM_ID'
1062 ||' AND t3.sr_instance_id (+) = msd.SR_INSTANCE_ID'
1063 ||' AND ms.sr_instance_id= msd.SR_INSTANCE_ID'
1064 ||' AND ms.ORGANIZATION_ID= msd.ORGANIZATION_ID'
1065 ||' AND ms.DISPOSITION_ID= msd.WIP_ENTITY_ID'
1066 ||' AND ms.plan_id=-1'
1067 ||' AND ms.ORDER_TYPE= 86' /* ds change change*/
1068 ||' order by msd.SOURCE_WIP_ENTITY_ID, msd.SOURCE_INVENTORY_ITEM_ID,msd.SOURCE_ORGANIZATION_ID,msd.ORIGINATION_TYPE';
1069
1070 OPEN c2 FOR lv_cursor_stmt USING MSC_CL_COLLECTION.v_chr10, MSC_CL_COLLECTION.v_chr13;
1071
1072 LOOP
1073
1074 FETCH c2 INTO
1075 lv_DISPOSITION_ID,
1076 lv_INVENTORY_ITEM_ID,
1077 lv_ORGANIZATION_ID,
1078 lv_USING_ASSEMBLY_ITEM_ID,
1079 lv_USING_ASSEMBLY_DEMAND_DATE,
1080 lv_USING_REQUIREMENT_QUANTITY,
1081 lv_QUANTITY_PER_ASSEMBLY,
1082 lv_QUANTITY_ISSUED,
1083 lv_ASSEMBLY_DEMAND_COMP_DATE,
1084 lv_DEMAND_TYPE,
1085 lv_ORIGINATION_TYPE,
1086 lv_SOURCE_ORGANIZATION_ID,
1087 lv_RESERVATION_ID,
1088 lv_OPERATION_SEQ_NUM,
1089 lv_DEMAND_CLASS,
1090 lv_REPETITIVE_SCHEDULE_ID,
1091 lv_SR_INSTANCE_ID,
1092 lv_PROJECT_ID,
1093 lv_TASK_ID,
1094 lv_PLANNING_GROUP,
1095 lv_END_ITEM_UNIT_NUMBER,
1096 lv_ORDER_NUMBER,
1097 lv_WIP_ENTITY_ID,
1098 lv_WIP_STATUS_CODE,
1099 lv_WIP_SUPPLY_TYPE,
1100 lv_DELETED_FLAG,
1101 lv_ASSET_ITEM_ID, /* ds change change */
1102 lv_ASSET_SERIAL_NUMBER, /* ds change change */
1103 lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
1104 lv_COMPONENT_YIELD_FACTOR; /* Discrete Mfg Enahancements Bug 4492743 */
1105
1106 EXIT WHEN c2%NOTFOUND;
1107
1108 BEGIN
1109
1110
1111 IF lv_ORIGINATION_TYPE=77 THEN
1112
1113 UPDATE MSC_DEMANDS
1114 SET
1115 OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
1116 OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
1117 OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
1118 USING_ASSEMBLY_ITEM_ID= lv_USING_ASSEMBLY_ITEM_ID,
1119 USING_ASSEMBLY_DEMAND_DATE= lv_USING_ASSEMBLY_DEMAND_DATE,
1120 USING_REQUIREMENT_QUANTITY= lv_USING_REQUIREMENT_QUANTITY,
1121 ASSEMBLY_DEMAND_COMP_DATE= lv_ASSEMBLY_DEMAND_COMP_DATE,
1122 DEMAND_TYPE= lv_DEMAND_TYPE,
1123 SOURCE_ORGANIZATION_ID= lv_SOURCE_ORGANIZATION_ID,
1124 RESERVATION_ID= lv_RESERVATION_ID,
1125 DEMAND_CLASS= lv_DEMAND_CLASS,
1126 PROJECT_ID= lv_PROJECT_ID,
1127 TASK_ID= lv_TASK_ID,
1128 PLANNING_GROUP= lv_PLANNING_GROUP,
1129 UNIT_NUMBER= lv_END_ITEM_UNIT_NUMBER,
1130 ORDER_NUMBER= lv_ORDER_NUMBER,
1131 WIP_STATUS_CODE= lv_WIP_STATUS_CODE,
1132 WIP_SUPPLY_TYPE= lv_WIP_SUPPLY_TYPE,
1133 DISPOSITION_ID= lv_DISPOSITION_ID,
1134 COMPONENT_SCALING_TYPE = lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
1135 COMPONENT_YIELD_FACTOR = lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
1136 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1137 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1138 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1139 WHERE PLAN_ID= -1
1140 AND SR_INSTANCE_ID= lv_SR_INSTANCE_ID
1141 AND ORIGINATION_TYPE= lv_ORIGINATION_TYPE
1142 AND ORGANIZATION_ID= lv_ORGANIZATION_ID
1143 AND WIP_ENTITY_ID= lv_WIP_ENTITY_ID
1144 AND OP_SEQ_NUM= lv_OPERATION_SEQ_NUM
1145 AND INVENTORY_ITEM_ID= lv_INVENTORY_ITEM_ID ;
1146
1147 END IF; -- Origination_Type
1148
1149 IF ( lv_DELETED_FLAG<> MSC_UTIL.SYS_YES ) AND ( lv_ORIGINATION_TYPE= 77)
1150 AND SQL%NOTFOUND THEN
1151
1152
1153 EXECUTE IMMEDIATE lv_sql_stmt
1154 USING
1155 lv_INVENTORY_ITEM_ID,
1156 lv_ORGANIZATION_ID,
1157 lv_USING_ASSEMBLY_ITEM_ID,
1158 lv_USING_ASSEMBLY_DEMAND_DATE,
1159 lv_USING_REQUIREMENT_QUANTITY,
1160 lv_QUANTITY_PER_ASSEMBLY,
1161 lv_QUANTITY_ISSUED,
1162 lv_ASSEMBLY_DEMAND_COMP_DATE,
1163 lv_DEMAND_TYPE,
1164 lv_ORIGINATION_TYPE,
1165 lv_SOURCE_ORGANIZATION_ID,
1166 lv_DISPOSITION_ID,
1167 lv_RESERVATION_ID,
1168 lv_OPERATION_SEQ_NUM,
1169 lv_DEMAND_CLASS,
1170 lv_SR_INSTANCE_ID,
1171 lv_PROJECT_ID,
1172 lv_TASK_ID,
1173 lv_PLANNING_GROUP,
1174 lv_END_ITEM_UNIT_NUMBER,
1175 lv_ORDER_NUMBER,
1176 lv_WIP_ENTITY_ID,
1177 lv_WIP_STATUS_CODE,
1178 lv_WIP_SUPPLY_TYPE,
1179 lv_REPETITIVE_SCHEDULE_ID,
1180 lv_ASSET_ITEM_ID, /* ds change change */
1181 lv_ASSET_SERIAL_NUMBER, /* ds changechange */
1182 lv_COMPONENT_SCALING_TYPE, /* Discrete Mfg Enahancements Bug 4492736 */
1183 lv_COMPONENT_YIELD_FACTOR, /* Discrete Mfg Enahancements Bug 4492743 */
1184 lv_item_type_id,
1185 lv_item_type_value,
1186 MSC_CL_COLLECTION.v_last_collection_id,
1187 MSC_CL_COLLECTION.v_current_date,
1188 MSC_CL_COLLECTION.v_current_user,
1189 MSC_CL_COLLECTION.v_current_date,
1190 MSC_CL_COLLECTION.v_current_user;
1191
1192 END IF;
1193
1194 EXCEPTION
1195
1196 WHEN OTHERS THEN
1197
1198 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1199
1200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1201 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1202 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SUPPLY');
1203 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1204 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1205
1206 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1207 RAISE;
1208
1209 ELSE
1210 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1211
1212 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1213
1214 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1215 FND_MESSAGE.SET_TOKEN('COLUMN', 'WIP_ENTITY_ID');
1216 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_WIP_ENTITY_ID));
1217 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1218
1219 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1220 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1221 FND_MESSAGE.SET_TOKEN('VALUE',
1222 MSC_GET_NAME.ORG_CODE( lv_ORGANIZATION_ID,
1223 MSC_CL_COLLECTION.v_instance_id));
1224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1225
1226 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1227 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1228 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lv_DEMAND_TYPE));
1229 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1230
1231 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1232 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
1233 FND_MESSAGE.SET_TOKEN('VALUE',
1234 MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
1235 lv_ORIGINATION_TYPE));
1236 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1237
1238 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1239 END IF;
1240 END;
1241
1242 c_count:= c_count+1;
1243
1244 IF c_count> MSC_CL_COLLECTION.PBS THEN
1245 c_count:= 0;
1246 END IF;
1247 END LOOP; -- cursor c2
1248
1249 CLOSE c2;
1250
1251 END IF; -- MSC_CL_COLLECTION.v_is_incremental_refresh THEN -- incremental Refresh
1252 --==========================
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 IF c2%ISOPEN THEN CLOSE c2; END IF;
1256
1257 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<LOAD_ERO_DEMAND>>');
1258 IF lv_cursor_stmt IS NOT NULL THEN
1259 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<CURSOR>>'||lv_cursor_stmt);
1260 END IF;
1261 IF lv_sql_stmt IS NOT NULL THEN
1262 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||lv_sql_stmt);
1263 END IF;
1264 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1265 RAISE;
1266 END LOAD_ERO_DEMAND;
1267
1268
1269 END MSC_CL_RPO_ODS_LOAD;