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