[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_DEMAND_ODS_LOAD
Source
1 PACKAGE BODY MSC_CL_DEMAND_ODS_LOAD AS -- body
2 /* $Header: MSCLDEMB.pls 120.10 2008/01/14 13:48:03 rsyadav noship $ */
3
4 -- SYS_YES Number:= MSC_CL_COLLECTION.SYS_YES ;
5 -- SYS_NO Number:= MSC_CL_COLLECTION.SYS_NO ;
6 -- SYS_TGT Number:=MSC_CL_COLLECTION.SYS_TGT;
7 -- SYS_INCR Number:=MSC_CL_COLLECTION.SYS_INCR;
8 -- G_APPS110 NUMBER := MSC_CL_COLLECTION.G_APPS110;
9 -- G_APPS115 NUMBER :=MSC_CL_COLLECTION.G_APPS115;
10 -- G_ALL_ORGANIZATIONS VARCHAR2(6):= MSC_CL_COLLECTION.G_ALL_ORGANIZATIONS;
11
12
13
14 --- PREPLACE CHANGE END ---
15
16
17 -- ******************************
18 -- For External SO link the transaction id of the sales orders to supplies if
19 -- complete refresh is performed and complete refresh sales orderes is no.
20 PROCEDURE LINK_SUPP_SO_DEMAND_EXT IS
21
22 lv_supply_id NUMBER;
23 lv_source_organization_id number;
24 lv_source_sr_instance_id number;
25 lv_supply_stmt VARCHAR2(5000);
26
27
28 cursor link_supply_demand is
29 select ROWID,INVENTORY_ITEM_ID,CUST_PO_NUMBER, SUPPLY_ID,CUSTOMER_LINE_NUMBER,SALES_ORDER_NUMBER
30 from msc_sales_orders
31 where demand_source_type = 2
32 and reservation_type = 1
33 and cust_po_number <> '-1'
34 and customer_line_number <> '-1'
35 and ( source_org_instance_id is Null or source_org_instance_id = MSC_CL_COLLECTION.v_instance_id );
36
37 BEGIN
38
39 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
40 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Starting LINK_SUPP_SO_DEMAND_EXT ......');
41 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
42
43 For c_rec in link_supply_demand
44 loop
45 BEGIN
46 lv_supply_id := NULL;
47 lv_source_organization_id := NULL;
48 lv_source_sr_instance_id := NULL;
49
50 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
51 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUST_PO_NUMBER: '|| c_rec.CUST_PO_NUMBER);
52 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'CUSTOMER_LINE_NUMBER: '|| c_rec.CUSTOMER_LINE_NUMBER);
53 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
54
55 lv_supply_stmt :=
56 'SELECT /*+ index(a,MSC_SUPPLIES_N5)*/ TRANSACTION_ID ,ORGANIZATION_ID, SR_INSTANCE_ID '
57 ||' FROM MSC_SUPPLIES a '
58 ||' WHERE a.PLAN_ID = -1'
59 ||' AND a.order_number = :CUST_PO_NUMBER'
60 ||' AND to_char(a.purch_line_num) = :CUSTOMER_LINE_NUMBER'
61 ||' AND a.order_type = 1 '
62 ||' AND a.INVENTORY_ITEM_ID = :INVENTORY_ITEM_ID '
63 ||' AND ROWNUM = 1 '
64 ||' AND NOT EXISTS ( SELECT /*+ index(b,MSC_SUPPLIES_N5)*/ 1 '
65 ||' FROM MSC_SUPPLIES b '
66 ||' WHERE b.PLAN_ID = -1 '
67 ||' AND b.order_number = :CUST_PO_NUMBER'
68 ||' AND to_char(b.purch_line_num) = :CUSTOMER_LINE_NUMBER '
69 ||' AND b.order_type = 1 '
70 ||' AND b.INVENTORY_ITEM_ID = :INVENTORY_ITEM_ID )';
71 EXECUTE IMMEDIATE lv_supply_stmt
72 INTO lv_supply_id, lv_source_organization_id, lv_source_sr_instance_id
73 USING c_rec.CUST_PO_NUMBER||'('||' )'||'('||c_rec.CUSTOMER_LINE_NUMBER||')'||'(1)', c_rec.CUSTOMER_LINE_NUMBER,c_rec.INVENTORY_ITEM_ID,
74 c_rec.CUST_PO_NUMBER||'('||' )'||'('||c_rec.CUSTOMER_LINE_NUMBER||')'||'(2)', c_rec.CUSTOMER_LINE_NUMBER,c_rec.INVENTORY_ITEM_ID;
75
76
77
78
79
80 Update msc_sales_orders
81 set supply_id = lv_supply_id,
82 source_organization_id = lv_source_organization_id,
83 source_org_instance_id = lv_source_sr_instance_id
84 where rowid = c_rec.rowid;
85
86 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
87
88 EXCEPTION WHEN NO_DATA_FOUND THEN
89 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for External Sales Order:'||c_rec.sales_order_number||'inst='||to_char(MSC_CL_COLLECTION.v_instance_id) );
90
91 WHEN OTHERS THEN
92 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
93 NULL;
94 END;
95 end loop;
96 commit;
97 EXCEPTION WHEN OTHERS THEN
98 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Error Message is: '||SQLERRM);
99 NULL;
100
101 END LINK_SUPP_SO_DEMAND_EXT;
102
103 -- ******************************
104 -- Link the transaction id of the sales orders to supplies if
105 -- complete refresh is performed and complete refresh sales orderes is no.
106 PROCEDURE LINK_SUPP_SO_DEMAND_110 IS
107
108 lv_SUPPLY_ID NUMBER;
109 lv_source_organization_id number;
110 lv_source_sr_instance_id number;
111 lv_supply_tbl VARCHAR2(30);
112 lv_supply_stmt VARCHAR2(5000);
113
114 cursor link_supply_demand(c_instance_id number) is
115 select SR_INSTANCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
116 DEMAND_SOURCE_TYPE,DEMAND_SOURCE_HEADER_ID,reservation_type,
117 original_system_reference ,original_system_line_reference, supply_id,
118 sales_order_number
119 from msc_sales_orders
120 where sr_instance_id = c_instance_id
121 and demand_source_type = 8
122 and reservation_type = 1
123 and original_system_reference <> '-1'
124 and supply_id is not null;
125
126 BEGIN
127 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
128 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
129 ELSE
130 lv_supply_tbl:= 'MSC_SUPPLIES';
131 END IF;
132
133 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
134 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_110 ......');
135 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
136 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
137
138 For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
139 loop
140 BEGIN
141 lv_supply_id := NULL;
142 lv_source_organization_id := NULL;
143 lv_source_sr_instance_id := NULL;
144
145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
148 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
149
150 lv_supply_stmt :=
151 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
152 ||' FROM '|| lv_supply_tbl
153 ||' WHERE PLAN_ID = -1 '
154 ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
155 ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE '
156 ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
157 ||' AND order_type = 2 '
158 ||' AND source_organization_id is not null ';
159
160 EXECUTE IMMEDIATE lv_supply_stmt
161 INTO lv_supply_id, lv_source_organization_id, lv_source_sr_instance_id
162 USING c_rec.ORIGINAL_SYSTEM_REFERENCE,c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE;
163
164 Update msc_sales_orders
165 set supply_id = lv_supply_id,
166 source_organization_id = lv_source_organization_id,
167 source_org_instance_id = lv_source_sr_instance_id
168 where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
169 and demand_source_type = 8
170 and supply_id = c_rec.supply_id
171 and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
172 and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
173
174 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
175
176 EXCEPTION WHEN NO_DATA_FOUND THEN
177 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
178 WHEN OTHERS THEN
179 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
180 END;
181 end loop;
182 commit;
183 EXCEPTION WHEN OTHERS THEN
184 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '||SQLERRM);
185 NULL;
186
187 END LINK_SUPP_SO_DEMAND_110;
188
189 -- Link the transaction id of the sales orders to supplies if
190 -- complete refresh is performed and complete refresh sales orderes is no.
191 --****************************
192
193 -- ******************************
194 -- Link the transaction id of the sales orders to supplies if
195 -- complete refresh is performed and complete refresh sales orderes is no.
196 PROCEDURE LINK_SUPP_SO_DEMAND_11I2 IS
197
198 lv_SUPPLY_ID NUMBER;
199 lv_source_organization_id number;
200 lv_source_sr_instance_id number;
201 lv_supply_tbl VARCHAR2(30);
202 lv_supply_stmt VARCHAR2(5000);
203 cursor link_supply_demand(c_instance_id number) is
204 select SR_INSTANCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
205 DEMAND_SOURCE_TYPE,DEMAND_SOURCE_HEADER_ID,reservation_type,
206 original_system_reference ,original_system_line_reference, supply_id,
207 sales_order_number
208 from msc_sales_orders
209 where sr_instance_id = c_instance_id
210 and demand_source_type = 8
211 and reservation_type = 1
212 and original_system_reference <> '-1'
213 and supply_id is not null;
214
215 BEGIN
216 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
217 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
218 ELSE
219 lv_supply_tbl:= 'MSC_SUPPLIES';
220 END IF;
221
222 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
223 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Starting LINK_SUPP_SO_DEMAND_11I2 ......');
224 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Supply table is: '||lv_supply_tbl);
225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '==========================================================');
226
227 For c_rec in link_supply_demand (MSC_CL_COLLECTION.v_instance_id)
228 loop
229 BEGIN
230 lv_supply_id := NULL;
231 lv_source_organization_id := NULL;
232 lv_source_sr_instance_id := NULL;
233
234 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '==========================================================');
235 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_REFERENCE: '||c_rec.ORIGINAL_SYSTEM_REFERENCE);
236 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ORIGINAL_SYSTEM_LINE_REFERENCE: '|| c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE);
237 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Supply ID to be updated:'|| c_rec.supply_id);
238
239 lv_supply_stmt :=
240 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
241 ||' FROM '|| lv_supply_tbl
242 ||' WHERE PLAN_ID = -1 '
243 ||' AND SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
244 ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
245 ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
246 ||' AND order_type in (2,73) '
247 ||' AND source_organization_id is not null ';
248
249 EXECUTE IMMEDIATE lv_supply_stmt
250 INTO lv_supply_id, lv_source_organization_id, lv_source_sr_instance_id
251 USING c_rec.ORIGINAL_SYSTEM_REFERENCE,c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE;
252
253 Update msc_sales_orders
254 set supply_id = lv_supply_id,
255 source_organization_id = lv_source_organization_id,
256 source_org_instance_id = lv_source_sr_instance_id
257 where sr_instance_id = MSC_CL_COLLECTION.v_instance_id
258 and demand_source_type = 8
259 and supply_id = c_rec.supply_id
260 and ORIGINAL_SYSTEM_REFERENCE = c_rec.ORIGINAL_SYSTEM_REFERENCE
261 and ORIGINAL_SYSTEM_LINE_REFERENCE = c_rec.ORIGINAL_SYSTEM_LINE_REFERENCE ;
262
263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'New Supply Id is: '||lv_supply_id);
264
265
266
267 EXCEPTION WHEN NO_DATA_FOUND THEN
268 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'No link for Internal Sales Order:'||c_rec.sales_order_number);
269 WHEN OTHERS THEN
270 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error: '||SQLERRM);
271 END;
272 end loop;
273 commit;
274 EXCEPTION WHEN OTHERS THEN
275 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '||SQLERRM);
276 NULL;
277
278 END LINK_SUPP_SO_DEMAND_11I2;
279 -- Link the transaction id of the sales orders to supplies if
280 -- complete refresh is performed and complete refresh sales orderes is no.
281
282 FUNCTION drop_demands_tmp_ind
283 RETURN boolean
284 IS
285 lv_temp_sql_stmt VARCHAR2(2000);
286 lv_ind_name VARCHAR2(30);
287 lv_drop_index NUMBER;
288
289 lv_retval boolean;
290 lv_dummy1 varchar2(32);
291 lv_dummy2 varchar2(32);
292
293 lv_msc_schema varchar2(32);
294
295
296 BEGIN
297
298 lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
299
300 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
301
302 EXECUTE IMMEDIATE
303 ' SELECT 1 '
304 ||' from all_indexes '
305 ||' where owner = :p_schema '
306 ||' and table_owner = :p_schema '
307 ||' and index_name = upper(''DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
308 INTO lv_drop_index
309 USING lv_msc_schema,lv_msc_schema;
310
311 IF (lv_drop_index = 1) THEN
312 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
313 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
314 application_short_name => 'MSC',
315 statement_type => AD_DDL.DROP_INDEX,
316 statement =>
317 'drop index demands_nx_'||MSC_CL_COLLECTION.v_instance_code,
318 object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
319
320 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code);
321 END IF;
322
323 RETURN true;
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 RETURN true;
327
328 WHEN OTHERS THEN
329 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
330 RETURN FALSE;
331 END drop_demands_tmp_ind;
332
333
334 FUNCTION drop_sales_orders_tmp_ind
335 RETURN boolean
336 IS
337 lv_drop_index NUMBER;
338
339 lv_retval boolean;
340 lv_dummy1 varchar2(32);
341 lv_dummy2 varchar2(32);
342
343 lv_msc_schema varchar2(32);
344
345 BEGIN
346
347 lv_retval := FND_INSTALLATION.GET_APP_INFO('FND', lv_dummy1, lv_dummy2 , MSC_CL_COLLECTION.v_applsys_schema);
348
349 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,lv_msc_schema);
350
351 EXECUTE IMMEDIATE
352 ' SELECT 1 '
353 ||' from all_indexes '
354 ||' where owner = :p_schema '
355 ||' and table_owner = :p_schema '
356 ||' and index_name = upper(''SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||''') '
357 INTO lv_drop_index
358 USING lv_msc_schema, lv_msc_schema;
359
360 IF (lv_drop_index = 1) THEN
361 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropping the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
362 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
363 application_short_name => 'MSC',
364 statement_type => AD_DDL.DROP_INDEX,
365 statement =>
366 'drop index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code,
367 object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
368
369 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Dropped the index : SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
370 END IF;
371
372 RETURN true;
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 RETURN true;
376
377 WHEN OTHERS THEN
378 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
379 RETURN FALSE;
380 END drop_sales_orders_tmp_ind;
381
382 /* This is a new function added to Link the Sales orders line to its immediate
383 Parent sales orders line for the project Sales Orders Pegging enhancement */
384 FUNCTION LINK_PARENT_SALES_ORDERS
385 RETURN BOOLEAN
386 IS
387
388 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type -Cursor variable
389 c1 CurTyp;
390
391 lv_link_id_list NUMBER;
392 lv_demand_id_list NUMBER;
393
394 lv_sel_sql_stmt VARCHAR2(2000);
395 lv_upd_sql_stmt VARCHAR2(2000);
396 lv_tbl VARCHAR2(30);
397
398 lv_refresh_no NUMBER;
399 lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
400 lv_task_start_time DATE;
401 lv_upd_count NUMBER := 0;
402
403 lv_retval boolean;
404 lv_dummy1 varchar2(32);
405 lv_dummy2 varchar2(32);
406 BEGIN
407
408 lv_task_start_time := SYSDATE;
409
410 IF (MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') ) THEN
411 lv_exchange_mode := MSC_UTIL.SYS_YES;
412 END IF;
413
414 IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
415 lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
416 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
417
418 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
419 application_short_name => 'MSC',
420 statement_type => AD_DDL.CREATE_INDEX,
421 statement =>
422 'create index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code
423 ||' on '||'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code
424 ||'(link_to_line_id,reservation_type,sr_instance_id) '
425 ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
426 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
427 object_name => 'SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code);
428
429 msc_analyse_tables_pk.analyse_table( 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
430
431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index SALES_ORDERS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
432 ELSE
433 lv_tbl:= 'MSC_SALES_ORDERS';
434 END IF;
435
436 /* select the link_to_line_id and its corresponding demand_id into Collection variables */
437
438 lv_sel_sql_stmt := ' SELECT distinct mso1.link_to_line_id '
439 ||' ,mso2.demand_id '
440 ||' FROM '|| lv_tbl ||' mso1, '
441 || lv_tbl ||' mso2 '
442 ||' WHERE mso1.sr_instance_id = '|| MSC_CL_COLLECTION.v_instance_id
443 ||' AND mso1.sr_instance_id = mso2.sr_instance_id '
444 ||' AND mso1.link_to_line_id = to_number(mso2.demand_source_line) '
445 ||' AND mso1.link_to_line_id IS NOT NULL '
446 ||' AND mso1.RESERVATION_TYPE = mso2.RESERVATION_TYPE '
447 ||' AND mso2.INVENTORY_ITEM_ID = nvl(mso2.ORDERED_ITEM_ID,mso2.INVENTORY_ITEM_ID) '
448 ||' AND mso2.primary_uom_quantity > 0 '
449 ||' AND mso1.RESERVATION_TYPE = 1 ';
450
451 IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN
452 /* If incremental of Sales orders then select rows only for collected data */
453 lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND mso1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
454 END IF;
455
456 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
457
458 OPEN c1 FOR lv_sel_sql_stmt; -- open the REF cursor
459
460 LOOP
461 FETCH c1 INTO
462 lv_link_id_list, lv_demand_id_list;
463
464 EXIT WHEN c1%NOTFOUND;
465
466 /* If the above select clause has more than 1 row , Update the PARENT_ID
467 in msc_sales_orders Table with the Demand_id of the Parent line_id */
468
469 EXECUTE IMMEDIATE
470 ' UPDATE ' || lv_tbl
471 ||' SET parent_id = :demand_id_value '
472 ||' WHERE sr_instance_id = :instance_id '
473 ||' AND link_to_line_id = :link_id_value '
474 ||' AND RESERVATION_TYPE = 1 '
475 USING lv_demand_id_list,
476 MSC_CL_COLLECTION.v_instance_id,
477 lv_link_id_list;
478
479 END LOOP;
480
481 COMMIT;
482
483 CLOSE c1; -- close the REF cursor
484
485 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
486 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
487 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
488 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' '||FND_MESSAGE.GET);
489
490 RETURN TRUE;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS......');
495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
496 RETURN FALSE;
497
498 END LINK_PARENT_SALES_ORDERS;
499
500 /* This is a new function added to Link the Sales orders line in a MDS to its immediate
501 Parent sales orders line for the project Sales Orders Pegging enhancement */
502 FUNCTION LINK_PARENT_SALES_ORDERS_MDS
503 RETURN BOOLEAN
504 IS
505
506 TYPE CurTyp IS REF CURSOR; -- define weak REF CURSOR type -Cursor variable
507 c1 CurTyp;
508
509 lv_link_id_list NUMBER;
510 lv_demand_id_list NUMBER;
511
512 lv_sel_sql_stmt VARCHAR2(2000);
513 lv_upd_sql_stmt VARCHAR2(2000);
514 lv_tbl VARCHAR2(30);
515
516 lv_refresh_no NUMBER;
517 lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
518 lv_upd_count NUMBER := 0;
519 lv_task_start_time DATE;
520
521 lv_retval boolean;
522 lv_dummy1 varchar2(32);
523 lv_dummy2 varchar2(32);
524 BEGIN
525
526 lv_task_start_time := SYSDATE;
527
528 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
529 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
530 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'FND', lv_dummy1, lv_dummy2, MSC_CL_COLLECTION.v_applsys_schema);
531
532 ad_ddl.do_ddl( applsys_schema => MSC_CL_COLLECTION.v_applsys_schema,
533 application_short_name => 'MSC',
534 statement_type => AD_DDL.CREATE_INDEX,
535 statement =>
536 'create index demands_nx_'||MSC_CL_COLLECTION.v_instance_code
537 ||' on '||'demands_'||MSC_CL_COLLECTION.v_instance_code
538 ||'(link_to_line_id,origination_type,sr_instance_id,plan_id) '
539 ||' PARALLEL ' || MSC_CL_COLLECTION.G_DEG_PARALLEL
540 ||' STORAGE (INITIAL 100K NEXT 1M PCTINCREASE 0) ',
541 object_name => 'demands_nx_'||MSC_CL_COLLECTION.v_instance_code);
542
543 msc_analyse_tables_pk.analyse_table( 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code, MSC_CL_COLLECTION.v_instance_id, -1);
544
545 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Index DEMANDS_NX_'||MSC_CL_COLLECTION.v_instance_code||' created.');
546 ELSE
547 lv_tbl:= 'MSC_DEMANDS';
548 END IF;
549
550 /* select the link_to_line_id and its corresponding demand_id into Collection variables */
551
552 lv_sel_sql_stmt := ' SELECT distinct md1.link_to_line_id '
553 ||' ,md2.demand_id '
554 ||' FROM '|| lv_tbl ||' md1, '
555 || lv_tbl ||' md2 '
556 ||' WHERE md1.sr_instance_id = ' ||MSC_CL_COLLECTION.v_instance_id
557 ||' AND md1.plan_id = -1 '
558 ||' AND md1.origination_type = 6 '
559 ||' AND md1.sr_instance_id = md2.sr_instance_id '
560 ||' AND md1.plan_id = md2.plan_id '
561 ||' AND md1.origination_type = md2.origination_type '
562 ||' AND md1.link_to_line_id = md2.sales_order_line_id '
563 ||' AND md1.link_to_line_id IS NOT NULL ';
564
565 /* If incremental of Sales Orders demands then select rows only for collected data */
566 IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
567 IF (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN
568 lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
569 END IF;
570 ELSIF (MSC_CL_COLLECTION.v_is_incremental_refresh) THEN
571 lv_sel_sql_stmt := lv_sel_sql_stmt || ' AND md1.REFRESH_NUMBER = ' || MSC_CL_COLLECTION.v_last_collection_id;
572 END IF;
573
574 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'The Select statement: '||lv_sel_sql_stmt);
575
576 OPEN c1 FOR lv_sel_sql_stmt; -- open the REF cursor
577
578 LOOP
579
580 FETCH c1 INTO
581 lv_link_id_list, lv_demand_id_list;
582
583 EXIT WHEN c1%NOTFOUND;
584
585 /* If the above select clause has more than 1 row , Update the PARENT_ID
586 in msc_sales_orders Table with the Demand_id of the Parent line_id */
587
588 EXECUTE IMMEDIATE
589 ' UPDATE ' || lv_tbl
590 ||' SET parent_id = :demand_id_value '
591 ||' WHERE sr_instance_id = :instance_id '
592 ||' AND plan_id = -1 '
593 ||' AND origination_type = 6 '
594 ||' AND link_to_line_id = :link_id_value '
595 USING lv_demand_id_list,
596 MSC_CL_COLLECTION.v_instance_id,
597 lv_link_id_list;
598
599 END LOOP;
600
601 COMMIT;
602
603 CLOSE c1; -- close the REF cursor
604
605 FND_MESSAGE.SET_NAME('MSC', 'MSC_ELAPSED_TIME');
606 FND_MESSAGE.SET_TOKEN('ELAPSED_TIME',
607 TO_CHAR(CEIL((SYSDATE- lv_task_start_time)*14400.0)/10));
608 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' '||FND_MESSAGE.GET);
609
610 RETURN TRUE;
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error executing LINK_PARENT_SALES_ORDERS_MDS......');
615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
616
617 RETURN FALSE;
618
619 END LINK_PARENT_SALES_ORDERS_MDS;
620
621 PROCEDURE LOAD_ITEM_FORECASTS IS
622
623 CURSOR c1_d IS
624 SELECT msd.SALES_ORDER_LINE_ID,
625 t1.INVENTORY_ITEM_ID,
626 msd.ORIGINATION_TYPE,
627 msd.SR_INSTANCE_ID,
628 msd.ORGANIZATION_ID
629 FROM MSC_ITEM_ID_LID t1,
630 MSC_ST_DEMANDS msd
631 WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
632 AND msd.ORIGINATION_TYPE = 29
633 AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES
634 AND t1.SR_INVENTORY_ITEM_ID(+)= msd.inventory_item_id
635 AND t1.sr_instance_id(+)= MSC_CL_COLLECTION.v_instance_id;
636
637 /* for bug: 2351354, made the changes to cursor to select the customer_id and the ship_to_site_id
638 from the msc_designators, becasue the customer inform can be entered at the Forecast level on the source */
639
640 CURSOR c1 IS
641 SELECT
642 t1.INVENTORY_ITEM_ID,
643 msd.forecast_designator,
644 msd.ORIGINATION_TYPE,
645 msd.ORGANIZATION_ID,
646 decode(t2.INVENTORY_ITEM_ID,NULL,t1.INVENTORY_ITEM_ID,t2.INVENTORY_ITEM_ID) USING_ASSEMBLY_ITEM_ID,
647 msd.USING_ASSEMBLY_DEMAND_DATE,
648 msd.USING_REQUIREMENT_QUANTITY,
649 msd.ASSEMBLY_DEMAND_COMP_DATE,
650 msd.SOURCE_ORGANIZATION_ID,
651 msd.FORECAST_MAD,
652 msd.CONFIDENCE_PERCENTAGE,
653 msd.BUCKET_TYPE,
654 md.DEMAND_CLASS,
655 msd.ORDER_PRIORITY,
656 msd.SR_INSTANCE_ID,
657 msd.PROJECT_ID,
658 msd.TASK_ID,
659 msd.PLANNING_GROUP,
660 md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
661 nvl(md.FORECAST_SET_ID,md.DESIGNATOR_ID) FORECAST_SET_ID,
662 msd.SALES_ORDER_LINE_ID,
663 msd.DELETED_FLAG,
664 msd.demand_type,
665 nvl(msd.probability,md.probability) probability,
666 -- c1.tp_id customer_id,
667 md.customer_id customer_id,
668 md.ship_id CUSTOMER_SITE_ID,
669 md.ship_id SHIP_TO_SITE_ID
670 FROM
671 -- msc_tp_id_lid c1,
672 MSC_ITEM_ID_LID t1,
673 MSC_ITEM_ID_LID t2,
674 MSC_DESIGNATORS md,
675 MSC_ST_DEMANDS msd
676 WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
677 AND t1.sr_instance_id= msd.sr_instance_id
678 AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id)
679 AND t2.sr_instance_id(+)= msd.sr_instance_id
680 AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
681 AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
682 AND md.DESIGNATOR(+)= msd.forecast_designator
683 AND md.Organization_ID(+)= msd.Organization_ID
684 -- AND c1.partner_type(+) = 2
685 -- and c1.sr_tp_id(+) = msd.customer_id
686 -- and c1.sr_instance_id(+) = msd.sr_instance_id
687 and msd.origination_type= 29
688 and msd.deleted_flag = 2
689 order by msd.SOURCE_SALES_ORDER_LINE_ID;
690
691 c_count NUMBER:=0;
692 lv_tbl VARCHAR2(30);
693 lv_sql_stmt VARCHAR2(5000);
694 lv_sql_ins VARCHAR2(5000);
695
696 BEGIN
697
698 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
699 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
700 ELSE
701 lv_tbl:= 'MSC_DEMANDS';
702 END IF;
703
704 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
705 BEGIN
706 lv_sql_ins :=
707 ' INSERT /*+ append */ '
708 || ' INTO '||lv_tbl
709 ||'( PLAN_ID,'
710 ||' DEMAND_ID,'
711 ||' DEMAND_TYPE,'
712 ||' ORIGINATION_TYPE,'
713 ||' INVENTORY_ITEM_ID,'
714 ||' ORGANIZATION_ID,'
715 ||' SCHEDULE_DESIGNATOR_ID,'
716 ||' FORECAST_SET_ID,'
717 ||' USING_ASSEMBLY_ITEM_ID,'
718 ||' USING_ASSEMBLY_DEMAND_DATE,'
719 ||' USING_REQUIREMENT_QUANTITY,'
720 ||' ASSEMBLY_DEMAND_COMP_DATE,'
721 ||' SOURCE_ORGANIZATION_ID,'
722 ||' DEMAND_CLASS,'
723 ||' ORDER_PRIORITY,'
724 ||' FORECAST_MAD,'
725 ||' CONFIDENCE_PERCENTAGE,'
726 ||' PROBABiLITY,'
727 ||' BUCKET_TYPE,'
728 ||' SR_INSTANCE_ID,'
729 ||' PROJECT_ID,'
730 ||' TASK_ID,'
731 ||' SALES_ORDER_LINE_ID,'
732 ||' DISPOSITION_ID,'
733 ||' CUSTOMER_ID,'
734 ||' CUSTOMER_SITE_ID,'
735 ||' SHIP_TO_SITE_ID,'
736 ||' PLANNING_GROUP,'
737 ||' REFRESH_NUMBER,'
738 ||' LAST_UPDATE_DATE,'
739 ||' LAST_UPDATED_BY,'
740 ||' CREATION_DATE,'
741 ||' CREATED_BY) '
742 ||' SELECT '
743 ||' -1,'
744 ||' MSC_DEMANDS_S.nextval,'
745 ||' msd.demand_type,'
746 ||' msd.ORIGINATION_TYPE,'
747 ||' t1.INVENTORY_ITEM_ID,'
748 ||' msd.ORGANIZATION_ID,'
749 ||' md.DESIGNATOR_ID,'
750 ||' md.FORECAST_SET_ID,'
751 ||' decode(t2.INVENTORY_ITEM_ID,NULL,t1.INVENTORY_ITEM_ID,t2.INVENTORY_ITEM_ID),'
752 ||' msd.USING_ASSEMBLY_DEMAND_DATE,'
753 ||' msd.USING_REQUIREMENT_QUANTITY,'
754 ||' msd.ASSEMBLY_DEMAND_COMP_DATE,'
755 ||' msd.SOURCE_ORGANIZATION_ID,'
756 ||' md.DEMAND_CLASS,'
757 ||' msd.ORDER_PRIORITY,'
758 ||' msd.FORECAST_MAD,'
759 ||' msd.CONFIDENCE_PERCENTAGE,'
760 ||' nvl(msd.probability,md.probability),'
761 ||' msd.BUCKET_TYPE,'
762 ||' msd.SR_INSTANCE_ID,'
763 ||' msd.PROJECT_ID,'
764 ||' msd.TASK_ID,'
765 ||' msd.SALES_ORDER_LINE_ID,'
766 ||' msd.SALES_ORDER_LINE_ID,'
767 ||' md.customer_id,'
768 ||' md.ship_id,'
769 ||' md.ship_id,'
770 ||' msd.PLANNING_GROUP,'
771 ||' :v_last_collection_id, '
772 ||' :v_current_date , '
773 ||' :v_current_user , '
774 ||' :v_current_date , '
775 ||' :v_current_user '
776 ||' FROM '
777 ||' MSC_ITEM_ID_LID t1, '
778 ||' MSC_ITEM_ID_LID t2, '
779 ||' MSC_DESIGNATORS md, '
780 ||' MSC_ST_DEMANDS msd '
781 ||'WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id '
782 ||' AND t1.sr_instance_id= msd.sr_instance_id '
783 ||' AND t2.SR_INVENTORY_ITEM_ID(+)= nvl(msd.using_assembly_item_id,msd.inventory_item_id) '
784 ||' AND t2.sr_instance_id(+)= msd.sr_instance_id '
785 ||' AND msd.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id
786 ||' AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID '
787 ||' AND md.DESIGNATOR(+)= msd.forecast_designator '
788 ||' AND md.Organization_ID(+)= msd.Organization_ID '
789 ||' and msd.origination_type= 29 '
790 ||' and msd.deleted_flag = 2 ';
791
792 execute IMMEDIATE lv_sql_ins
793 USING MSC_CL_COLLECTION.v_last_collection_id,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user,MSC_CL_COLLECTION.v_current_date,MSC_CL_COLLECTION.v_current_user;
794
795 COMMIT;
796 EXCEPTION
797
798 WHEN OTHERS THEN
799
800 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
801
802 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
803 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
804 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
805 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
806 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
807
808 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
809 RAISE;
810
811 ELSE
812 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
813 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
814 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
815 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
816 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
817 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
819 END IF;
820 END;
821
822 ELSE
823
824 lv_sql_stmt:=
825 'INSERT INTO '||lv_tbl
826 ||'( PLAN_ID,'
827 ||' DEMAND_ID,'
828 ||' DEMAND_TYPE,'
829 ||' ORIGINATION_TYPE,'
830 ||' INVENTORY_ITEM_ID,'
831 ||' ORGANIZATION_ID,'
832 ||' SCHEDULE_DESIGNATOR_ID,'
833 ||' FORECAST_SET_ID,'
834 ||' USING_ASSEMBLY_ITEM_ID,'
835 ||' USING_ASSEMBLY_DEMAND_DATE,'
836 ||' USING_REQUIREMENT_QUANTITY,'
837 ||' ASSEMBLY_DEMAND_COMP_DATE,'
838 ||' SOURCE_ORGANIZATION_ID,'
839 ||' DEMAND_CLASS,'
840 ||' ORDER_PRIORITY,'
841 ||' FORECAST_MAD,'
842 ||' CONFIDENCE_PERCENTAGE,'
843 ||' PROBABiLITY,'
844 ||' BUCKET_TYPE,'
845 ||' SR_INSTANCE_ID,'
846 ||' PROJECT_ID,'
847 ||' TASK_ID,'
848 ||' SALES_ORDER_LINE_ID,'
849 ||' DISPOSITION_ID,'
850 ||' CUSTOMER_ID,'
851 ||' CUSTOMER_SITE_ID,'
852 ||' SHIP_TO_SITE_ID,'
853 ||' PLANNING_GROUP,'
854 ||' REFRESH_NUMBER,'
855 ||' LAST_UPDATE_DATE,'
856 ||' LAST_UPDATED_BY,'
857 ||' CREATION_DATE,'
858 ||' CREATED_BY) '
859 ||'VALUES'
860 ||'( -1,'
861 ||' MSC_DEMANDS_S.nextval,'
862 ||' :DEMAND_TYPE,'
863 ||' :ORIGINATION_TYPE,'
864 ||' :INVENTORY_ITEM_ID,'
865 ||' :ORGANIZATION_ID,'
866 ||' :SCHEDULE_DESIGNATOR_ID,'
867 ||' :FORECAST_SET_ID,'
868 ||' :USING_ASSEMBLY_ITEM_ID,'
869 ||' :USING_ASSEMBLY_DEMAND_DATE,'
870 ||' :USING_REQUIREMENT_QUANTITY,'
871 ||' :ASSEMBLY_DEMAND_COMP_DATE,'
872 ||' :SOURCE_ORGANIZATION_ID,'
873 ||' :DEMAND_CLASS,'
874 ||' :ORDER_PRIORITY,'
875 ||' :FORECAST_MAD,'
876 ||' :CONFIDENCE_PERCENTAGE,'
877 ||' :PROBABiLITY,'
878 ||' :BUCKET_TYPE,'
879 ||' :SR_INSTANCE_ID,'
880 ||' :PROJECT_ID,'
881 ||' :TASK_ID,'
882 ||' :SALES_ORDER_LINE_ID,'
883 ||' :SALES_ORDER_LINE_ID,'
884 ||' :CUSTOMER_ID,'
885 ||' :CUSTOMER_SITE_ID,'
886 ||' :SHIP_TO_SITE_ID,'
887 ||' :PLANNING_GROUP,'
888 ||' :v_last_collection_id,'
889 ||' :v_current_date,'
890 ||' :v_current_user,'
891 ||' :v_current_date,'
892 ||' :v_current_user)';
893
894 c_count:=0;
895
896
897 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
898 FOR c_rec in c1_d LOOP
899
900 DELETE MSC_DEMANDS
901 WHERE PLAN_ID= -1
902 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
903 AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
904 AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
905 AND INVENTORY_ITEM_ID = c_rec.INVENTORY_ITEM_ID
906 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
907
908 END LOOP;
909 END IF;
910
911 FOR c_rec IN c1 LOOP
912
913 BEGIN
914
915 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
916 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
917 UPDATE MSC_DEMANDS
918 SET
919 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
920 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
921 OLD_USING_REQUIREMENT_QUANTITY= USING_REQUIREMENT_QUANTITY,
922 OLD_USING_ASSEMBLY_DEMAND_DATE= USING_ASSEMBLY_DEMAND_DATE,
923 OLD_ASSEMBLY_DEMAND_COMP_DATE= ASSEMBLY_DEMAND_COMP_DATE,
924 USING_ASSEMBLY_ITEM_ID= c_rec.USING_ASSEMBLY_ITEM_ID,
925 USING_ASSEMBLY_DEMAND_DATE= c_rec.USING_ASSEMBLY_DEMAND_DATE,
926 USING_REQUIREMENT_QUANTITY= c_rec.USING_REQUIREMENT_QUANTITY,
927 ASSEMBLY_DEMAND_COMP_DATE= c_rec.ASSEMBLY_DEMAND_COMP_DATE,
928 SOURCE_ORGANIZATION_ID= c_rec.SOURCE_ORGANIZATION_ID,
929 PROBABiLITY = c_rec.probability,
930 DEMAND_CLASS= c_rec.DEMAND_CLASS,
931 ORDER_PRIORITY = c_rec.ORDER_PRIORITY,
932 PROJECT_ID= c_rec.PROJECT_ID,
933 TASK_ID= c_rec.TASK_ID,
934 SALES_ORDER_LINE_ID= c_rec.SALES_ORDER_LINE_ID,
935 DISPOSITION_ID= c_rec.SALES_ORDER_LINE_ID,
936 CUSTOMER_ID= c_rec.CUSTOMER_ID,
937 CUSTOMER_SITE_ID = c_rec.CUSTOMER_SITE_ID,
938 SHIP_TO_SITE_ID = c_rec.SHIP_TO_SITE_ID,
939 PLANNING_GROUP= c_rec.PLANNING_GROUP,
940 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
941 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
942 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
943 WHERE PLAN_ID= -1
944 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
945 AND ORIGINATION_TYPE= c_rec.ORIGINATION_TYPE
946 AND SALES_ORDER_LINE_ID = c_rec.SALES_ORDER_LINE_ID
947 AND ORGANIZATION_ID = c_rec.ORGANIZATION_ID;
948 END IF;
949
950 IF SQL%NOTFOUND THEN
951
952 EXECUTE IMMEDIATE lv_sql_stmt
953 USING
954 c_rec.DEMAND_TYPE,
955 c_rec.ORIGINATION_TYPE,
956 c_rec.INVENTORY_ITEM_ID,
957 c_rec.ORGANIZATION_ID,
958 c_rec.SCHEDULE_DESIGNATOR_ID,
959 c_rec.FORECAST_SET_ID,
960 c_rec.USING_ASSEMBLY_ITEM_ID,
961 c_rec.USING_ASSEMBLY_DEMAND_DATE,
962 c_rec.USING_REQUIREMENT_QUANTITY,
963 c_rec.ASSEMBLY_DEMAND_COMP_DATE,
964 c_rec.SOURCE_ORGANIZATION_ID,
965 c_rec.DEMAND_CLASS,
966 c_rec.ORDER_PRIORITY,
967 c_rec.FORECAST_MAD,
968 c_rec.CONFIDENCE_PERCENTAGE,
969 c_Rec.PROBABiLITY,
970 c_rec.BUCKET_TYPE,
971 c_rec.SR_INSTANCE_ID,
972 c_rec.PROJECT_ID,
973 c_rec.TASK_ID,
974 c_rec.SALES_ORDER_LINE_ID,
975 c_rec.SALES_ORDER_LINE_ID,
976 c_rec.CUSTOMER_ID,
977 c_rec.CUSTOMER_SITE_ID,
978 c_rec.SHIP_TO_SITE_ID,
979 c_rec.PLANNING_GROUP,
980 MSC_CL_COLLECTION.v_last_collection_id,
981 MSC_CL_COLLECTION.v_current_date,
982 MSC_CL_COLLECTION.v_current_user,
983 MSC_CL_COLLECTION.v_current_date,
984 MSC_CL_COLLECTION.v_current_user;
985
986 END IF; -- sql%notfound
987
988 c_count:= c_count+1;
989
990 IF c_count>MSC_CL_COLLECTION.PBS THEN
991 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
992 c_count:= 0;
993 END IF;
994
995 EXCEPTION
996
997 WHEN OTHERS THEN
998
999 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1000
1001 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1002 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1003 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1004 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1005 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1006
1007 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1008 RAISE;
1009
1010 ELSE
1011 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1012
1013 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1014 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1015 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_ITEM_FORECASTS');
1016 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1017 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1018
1019 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1020 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1021 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
1022 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1023
1024
1025 /*
1026 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1027 FND_MESSAGE.SET_TOKEN('COLUMN', 'FORECAST_DESIGNATOR');
1028 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.forecast_designator);
1029 */
1030
1031 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1032
1033 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1034 FND_MESSAGE.SET_TOKEN('COLUMN', 'SCHEDULE_DESIGNATOR_ID');
1035 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.schedule_designator_id);
1036
1037 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1038
1039
1040 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1041 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1042 FND_MESSAGE.SET_TOKEN('VALUE',
1043 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1044 MSC_CL_COLLECTION.v_instance_id));
1045 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1046
1047 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1048 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
1049 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DEMAND_TYPE));
1050 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1051
1052 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1053 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
1054 FND_MESSAGE.SET_TOKEN('VALUE',
1055 MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
1056 c_rec.ORIGINATION_TYPE));
1057 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1058
1059 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1060 END IF;
1061
1062 END;
1063
1064 END LOOP;
1065 END IF;
1066 END LOAD_ITEM_FORECASTS;
1067
1068 PROCEDURE LOAD_FORECASTS IS
1069
1070 v_forecast_set_id number;
1071
1072 CURSOR C1_d is
1073 SELECT
1074 MSD.DESIGNATOR,
1075 MSD.ORGANIZATION_ID,
1076 MSD.SR_INSTANCE_ID
1077 from MSC_ST_DESIGNATORS MSD
1078 WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1079 AND msd.DELETED_FLAG= MSC_UTIL.SYS_YES;
1080
1081 CURSOR c1 IS
1082 SELECT
1083 msd.DESIGNATOR,
1084 msd.FORECAST_SET,
1085 msd.PROBABiLITY,
1086 msd.ORGANIZATION_ID,
1087 msd.MPS_RELIEF,
1088 msd.INVENTORY_ATP_FLAG,
1089 msd.DESCRIPTION,
1090 msd.DISABLE_DATE,
1091 msd.DEMAND_CLASS,
1092 msd.CONSUME_FORECAST,
1093 msd.UPDATE_TYPE,
1094 msd.FORWARD_UPDATE_TIME_FENCE FOREWARD_UPDATE_TIME_FENCE,
1095 msd.BACKWARD_UPDATE_TIME_FENCE,
1096 msd.OUTLIER_UPDATE_PERCENTAGE,
1097 mtil.tp_id customer_id, --msd.CUSTOMER_ID,
1098 mtsila.tp_site_id ship_id,--msd.SHIP_ID,
1099 mtsilb.tp_site_id bill_id,--msd.BILL_ID,
1100 msd.BUCKET_TYPE,
1101 msd.DELETED_FLAG,
1102 msd.REFRESH_ID,
1103 msd.SR_INSTANCE_ID,
1104 msd.DESIGNATOR_TYPE,
1105 null forecast_Set_id
1106 FROM MSC_ST_DESIGNATORS msd,
1107 MSC_TP_ID_LID mtil,
1108 MSC_TP_SITE_ID_LID mtsila,
1109 MSC_TP_SITE_ID_LID mtsilb
1110 WHERE msd.SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
1111 and msd.designator_type = 6
1112 and mtil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1113 and mtil.sr_tp_id(+) = msd.customer_id
1114 and mtil.partner_type(+) = 2
1115 and mtsila.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1116 and mtsila.sr_tp_site_id(+) = msd.ship_id
1117 and mtsila.partner_type(+) = 2
1118 and mtsilb.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1119 and mtsilb.sr_tp_site_id(+) = msd.bill_id
1120 and mtsilb.partner_type(+) = 2
1121 order by nvl(msd.forecast_set,'0');
1122
1123 c_count NUMBER:= 0;
1124
1125 BEGIN
1126
1127
1128 FOR c_rec in c1_d loop
1129
1130 UPDATE MSC_DESIGNATORS
1131 SET DISABLE_DATE= TRUNC(MSC_CL_COLLECTION.v_current_date),
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 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1136 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1137 and designator = c_rec.designator
1138 and designator_type = 6
1139 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1140
1141 END LOOP;
1142
1143 COMMIT;
1144
1145 c_count:= 0;
1146
1147 FOR c_rec IN c1 LOOP
1148
1149 BEGIN
1150
1151 /* Bug 3036943 - if the forecast and set are deleted and the same forecast is
1152 created under another set, we want to assign the forecast to the new set
1153 */
1154
1155 If c_rec.forecast_set is not null then
1156
1157 Begin
1158 Select distinct designator_id
1159 into v_forecast_set_id
1160 from msc_designators
1161 where designator = c_rec.forecast_Set
1162 and organization_id = c_rec.organization_id
1163 and sr_instance_id = MSC_CL_COLLECTION.v_instance_id;
1164 Exception
1165 when no_data_found
1166 then
1167 Select MSC_DESIGNATORS_S.Nextval
1168 into v_forecast_set_id
1169 from dual;
1170 End;
1171 Elsif c_rec.forecast_set is null then
1172
1173 v_forecast_set_id := null;
1174 End if;
1175
1176
1177 UPDATE MSC_DESIGNATORS
1178 SET
1179 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
1180 forecast_set_id = v_forecast_set_id,
1181 MPS_RELIEF= c_rec.MPS_RELIEF,
1182 PROBABiLITY =c_rec.PROBABiLITY,
1183 INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
1184 DESCRIPTION= c_rec.DESCRIPTION,
1185 DISABLE_DATE= c_rec.DISABLE_DATE,
1186 DEMAND_CLASS= c_rec.DEMAND_CLASS,
1187 CONSUME_FORECAST = c_rec.CONSUME_FORECAST,
1188 UPDATE_TYPE = c_rec. UPDATE_TYPE,
1189 FORWARD_UPDATE_TIME_FENCE = c_rec.FOREWARD_UPDATE_TIME_FENCE,
1190 BACKWARD_UPDATE_TIME_FENCE = c_rec.BACKWARD_UPDATE_TIME_FENCE,
1191 OUTLIER_UPDATE_PERCENTAGE = c_rec.OUTLIER_UPDATE_PERCENTAGE,
1192 CUSTOMER_ID = c_rec.CUSTOMER_ID,
1193 SHIP_ID = c_rec.SHIP_ID,
1194 BILL_ID = c_rec.BILL_ID,
1195 BUCKET_TYPE = c_rec.BUCKET_TYPE,
1196 DESIGNATOR_TYPE = c_rec.DESIGNATOR_TYPE,
1197 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1198 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1199 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1200 WHERE DESIGNATOR= c_rec.DESIGNATOR
1201 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
1202 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1203 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
1204
1205 IF SQL%NOTFOUND THEN
1206
1207
1208
1209 INSERT INTO MSC_DESIGNATORS
1210 ( DESIGNATOR_ID,
1211 FORECAST_SET_ID,
1212 DESIGNATOR,
1213 DESIGNATOR_TYPE,
1214 ORGANIZATION_ID,
1215 MPS_RELIEF,
1216 INVENTORY_ATP_FLAG,
1217 DESCRIPTION,
1218 DISABLE_DATE,
1219 DEMAND_CLASS,
1220 CONSUME_FORECAST ,
1221 UPDATE_TYPE ,
1222 FORWARD_UPDATE_TIME_FENCE ,
1223 BACKWARD_UPDATE_TIME_FENCE,
1224 OUTLIER_UPDATE_PERCENTAGE ,
1225 PROBABiLITY,
1226 CUSTOMER_ID ,
1227 SHIP_ID ,
1228 BILL_ID ,
1229 BUCKET_TYPE ,
1230 COLLECTED_FLAG,
1231 SR_INSTANCE_ID,
1232 REFRESH_NUMBER,
1233 LAST_UPDATE_DATE,
1234 LAST_UPDATED_BY,
1235 CREATION_DATE,
1236 CREATED_BY)
1237 VALUES
1238 ( MSC_DESIGNATORS_S.NEXTVAL,
1239 v_forecast_Set_id,
1240 c_rec.DESIGNATOR,
1241 6,
1242 c_rec.ORGANIZATION_ID,
1243 c_rec.MPS_RELIEF,
1244 c_rec.INVENTORY_ATP_FLAG,
1245 c_rec.DESCRIPTION,
1246 c_rec.DISABLE_DATE,
1247 c_rec.DEMAND_CLASS,
1248 c_rec.CONSUME_FORECAST,
1249 c_rec.UPDATE_TYPE,
1250 c_rec.FOREWARD_UPDATE_TIME_FENCE,
1251 c_rec.BACKWARD_UPDATE_TIME_FENCE,
1252 c_rec.OUTLIER_UPDATE_PERCENTAGE,
1253 c_rec.PROBABiLITY,
1254 c_rec.CUSTOMER_ID,
1255 c_rec.SHIP_ID,
1256 c_rec.BILL_ID,
1257 c_rec.BUCKET_TYPE,
1258 MSC_UTIL.SYS_YES,
1259 c_rec.SR_INSTANCE_ID,
1260 MSC_CL_COLLECTION.v_last_collection_id,
1261 MSC_CL_COLLECTION.v_current_date,
1262 MSC_CL_COLLECTION.v_current_user,
1263 MSC_CL_COLLECTION.v_current_date,
1264 MSC_CL_COLLECTION.v_current_user );
1265
1266
1267 END IF;
1268
1269 c_count:= c_count+1;
1270
1271 IF c_count> MSC_CL_COLLECTION.PBS THEN
1272 COMMIT;
1273 c_count:= 0;
1274 END IF;
1275
1276 EXCEPTION
1277
1278 WHEN OTHERS THEN
1279
1280 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1281
1282 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1283 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1284 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1285 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1286 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1287
1288 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1289 RAISE;
1290
1291 ELSE
1292 IF SQLCODE = -00001 THEN
1293 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1294 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1295 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1296 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1297 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1299
1300 FND_MESSAGE.SET_NAME('MSC', 'MSC_DESIGNATOR_UNIQUE');
1301 FND_MESSAGE.SET_TOKEN('DESIGNATOR', c_rec.DESIGNATOR);
1302 FND_MESSAGE.SET_TOKEN('ORGANIZATION', MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,MSC_CL_COLLECTION.v_instance_id));
1303 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1304
1305
1306
1307 ELSE
1308
1309 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1310
1311 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1312 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1313 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_FORECASTS');
1314 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
1315 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1316
1317 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1318 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
1319 FND_MESSAGE.SET_TOKEN('VALUE',
1320 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
1321 MSC_CL_COLLECTION.v_instance_id));
1322 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1323
1324 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1325 FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
1326 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
1327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1328
1329 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1330 END IF;
1331 END IF;
1332 END;
1333
1334 END LOOP;
1335
1336 COMMIT;
1337
1338 End load_forecasts;
1339
1340 -- ===================== LOAD MDS_DEMAND ========================
1341 -- = this procedure is called by LOAD_SUPPLY Mar 05 2000, TWUU =
1342 -- ==============================================================
1343 PROCEDURE LOAD_DEMAND IS
1344
1345 /* 2201791 - select substr(order_number,1,62) since order_number is
1346 defined as varchar(62) in msc_demands table */
1347
1348 CURSOR c1 IS
1349 SELECT
1350 t1.INVENTORY_ITEM_ID,
1351 msd.ORGANIZATION_ID,
1352 t2.INVENTORY_ITEM_ID USING_ASSEMBLY_ITEM_ID,
1353 msd.USING_ASSEMBLY_DEMAND_DATE,
1354 msd.USING_REQUIREMENT_QUANTITY,
1355 msd.ASSEMBLY_DEMAND_COMP_DATE,
1356 msd.DEMAND_TYPE,
1357 msd.DAILY_DEMAND_RATE,
1358 msd.ORIGINATION_TYPE,
1359 msd.SOURCE_ORGANIZATION_ID,
1360 msd.DISPOSITION_ID,
1361 msd.RESERVATION_ID,
1362 msd.OPERATION_SEQ_NUM,
1363 msd.DEMAND_CLASS,
1364 msd.PROMISE_DATE,
1365 msd.LINK_TO_LINE_ID,
1366 msd.REPETITIVE_SCHEDULE_ID,
1367 msd.SR_INSTANCE_ID,
1368 msd.PROJECT_ID,
1369 msd.TASK_ID,
1370 msd.PLANNING_GROUP,
1371 msd.END_ITEM_UNIT_NUMBER,
1372 REPLACE(REPLACE(substr(msd.ORDER_NUMBER,1,62),MSC_CL_COLLECTION.v_chr10),MSC_CL_COLLECTION.v_chr13) ORDER_NUMBER,
1373 md.DESIGNATOR_ID SCHEDULE_DESIGNATOR_ID,
1374 msd.SELLING_PRICE,
1375 msd.DMD_LATENESS_COST,
1376 msd.REQUEST_DATE,
1377 msd.ORDER_PRIORITY,
1378 msd.SALES_ORDER_LINE_ID,
1379 msd.DEMAND_SCHEDULE_NAME,
1380 msd.DELETED_FLAG,
1381 c1.tp_id customer_id,
1382 mtsil.tp_site_id CUSTOMER_SITE_ID,
1383 mtsil.tp_site_id SHIP_TO_SITE_ID,
1384 nvl(msd.ORIGINAL_SYSTEM_REFERENCE,'-1') ORIGINAL_SYSTEM_REFERENCE,
1385 nvl(msd.ORIGINAL_SYSTEM_LINE_REFERENCE,'-1') ORIGINAL_SYSTEM_LINE_REFERENCE,
1386 msd.demand_source_type,
1387 msd.ORDER_DATE_TYPE_CODE,
1388 msd.SCHEDULE_ARRIVAL_DATE,
1389 msd.LATEST_ACCEPTABLE_DATE,
1390 msd.SHIPPING_METHOD_CODE,
1391 mtsil.location_id ship_to_location_id
1392 FROM
1393 msc_tp_id_lid c1,
1394 MSC_ITEM_ID_LID t1,
1395 MSC_ITEM_ID_LID t2,
1396 MSC_DESIGNATORS md,
1397 MSC_TP_SITE_ID_LID mtsil,
1398 MSC_ST_DEMANDS msd
1399 WHERE t1.SR_INVENTORY_ITEM_ID= msd.inventory_item_id
1400 AND t1.sr_instance_id= msd.sr_instance_id
1401 AND t2.SR_INVENTORY_ITEM_ID= msd.using_assembly_item_id
1402 AND t2.sr_instance_id= msd.sr_instance_id
1403 AND msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
1404 AND msd.ORIGINATION_TYPE in ( 6,7,8,15,24,42)
1405 AND md.SR_INSTANCE_ID(+)= msd.SR_INSTANCE_ID
1406 AND md.DESIGNATOR(+)= msd.DEMAND_SCHEDULE_NAME
1407 AND md.Organization_ID(+)= msd.Organization_ID
1408 AND md.Designator_Type(+)= 1
1409 AND c1.partner_type(+) = 2
1410 and c1.sr_tp_id(+) = msd.customer_id
1411 and c1.sr_instance_id(+) = msd.sr_instance_id
1412 and mtsil.sr_instance_id(+) = MSC_CL_COLLECTION.v_instance_id
1413 and mtsil.sr_tp_site_id(+) = msd.SHIP_TO_SITE_ID
1414 and mtsil.partner_type(+) = 2
1415 ORDER BY
1416 msd.source_disposition_id, msd.DELETED_FLAG;
1417
1418 c_count NUMBER:=0;
1419 lv_tbl VARCHAR2(30);
1420 lv_sql_stmt VARCHAR2(5000);
1421 lv_supply_tbl VARCHAR2(30);
1422 lv_supply_stmt VARCHAR2(5000);
1423 lb_FetchComplete Boolean;
1424 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1425
1426
1427 TYPE CharTblTyp IS TABLE OF VARCHAR2(70);
1428 TYPE NumTblTyp IS TABLE OF NUMBER;
1429 TYPE dateTblTyp IS TABLE OF DATE;
1430
1431 lb_INVENTORY_ITEM_ID NumTblTyp;
1432 lb_ORGANIZATION_ID NumTblTyp;
1433 lb_USING_ASSEMBLY_ITEM_ID NumTblTyp;
1434 lb_USING_ASSEMBLY_DEMAND_DATE dateTblTyp ;
1435 lb_USING_REQUIREMENT_QUANTITY NumTblTyp;
1436 lb_ASSEMBLY_DEMAND_COMP_DATE dateTblTyp;
1437 lb_DEMAND_TYPE NumTblTyp;
1438 lb_DAILY_DEMAND_RATE NumTblTyp;
1439 lb_ORIGINATION_TYPE NumTblTyp;
1440 lb_SOURCE_ORGANIZATION_ID NumTblTyp;
1441 lb_DISPOSITION_ID NumTblTyp;
1442 lb_RESERVATION_ID NumTblTyp;
1443 lb_OPERATION_SEQ_NUM NumTblTyp;
1444 lb_DEMAND_CLASS CharTblTyp;
1445 lb_PROMISE_DATE dateTblTyp;
1446 lb_REPETITIVE_SCHEDULE_ID NumTblTyp;
1447 lb_SR_INSTANCE_ID NumTblTyp;
1448 lb_PROJECT_ID NumTblTyp;
1449 lb_TASK_ID NumTblTyp;
1450 lb_PLANNING_GROUP CharTblTyp;
1451 lb_END_ITEM_UNIT_NUMBER CharTblTyp;
1452 lb_ORDER_NUMBER CharTblTyp;
1453 lb_SCHEDULE_DESIGNATOR_ID NumTblTyp;
1454 lb_SELLING_PRICE NumTblTyp;
1455 lb_DMD_LATENESS_COST NumTblTyp;
1456 lb_REQUEST_DATE dateTblTyp;
1457 lb_ORDER_PRIORITY NumTblTyp;
1458 lb_SALES_ORDER_LINE_ID NumTblTyp;
1459 lb_DEMAND_SCHEDULE_NAME CharTblTyp;
1460 lb_DELETED_FLAG NumTblTyp;
1461 lb_customer_id NumTblTyp;
1462 lb_CUSTOMER_SITE_ID NumTblTyp;
1463 lb_SHIP_TO_SITE_ID NumTblTyp;
1464 lb_OR_SYSTEM_REFERENCE CharTblTyp;
1465 lb_OR_SYSTEM_LINE_REFERENCE CharTblTyp;
1466 lb_demand_source_type NumTblTyp;
1467 lb_ORDER_DATE_TYPE_CODE CharTblTyp;
1468 lb_SCHEDULE_ARRIVAL_DATE dateTblTyp;
1469 lb_LATEST_ACCEPTABLE_DATE dateTblTyp;
1470 lb_SHIPPING_METHOD_CODE CharTblTyp;
1471 lb_ship_to_location_id NumTblTyp;
1472 lb_LINK_TO_LINE_ID NumTblTyp;
1473 BEGIN
1474
1475 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1476 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
1477 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1478 ELSE
1479 lv_tbl:= 'MSC_DEMANDS';
1480 lv_supply_tbl:= 'MSC_SUPPLIES';
1481 END IF;
1482
1483 /** PREPLACE CHANGE START **/
1484
1485 IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
1486 (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
1487 (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
1488 (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
1489 (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
1490 (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
1491
1492 lv_supply_tbl := 'MSC_SUPPLIES';
1493
1494 ELSIF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
1495
1496 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1497
1498 END IF;
1499
1500 /** PREPLACE CHANGE END **/
1501
1502 /* In cont. collections if any of the Supply is targeted */
1503 IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
1504 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
1505 (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
1506 (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
1507 (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
1508 (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
1509
1510 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
1511 ELSE
1512 lv_supply_tbl := 'MSC_SUPPLIES';
1513 END IF;
1514 END IF;
1515
1516 lv_sql_stmt:=
1517 'INSERT INTO '||lv_tbl
1518 ||'( PLAN_ID,'
1519 ||' DEMAND_ID,'
1520 ||' INVENTORY_ITEM_ID,'
1521 ||' ORGANIZATION_ID,'
1522 ||' SCHEDULE_DESIGNATOR_ID,'
1523 ||' USING_ASSEMBLY_ITEM_ID,'
1524 ||' USING_ASSEMBLY_DEMAND_DATE,'
1525 ||' USING_REQUIREMENT_QUANTITY,'
1526 ||' ASSEMBLY_DEMAND_COMP_DATE,'
1527 ||' DEMAND_TYPE,'
1528 ||' DAILY_DEMAND_RATE,'
1529 ||' ORIGINATION_TYPE,'
1530 ||' SOURCE_ORGANIZATION_ID,'
1531 ||' DISPOSITION_ID,'
1532 ||' RESERVATION_ID,'
1533 ||' OP_SEQ_NUM,'
1534 ||' DEMAND_CLASS,'
1535 ||' PROMISE_DATE,'
1536 ||' LINK_TO_LINE_ID ,'
1537 ||' SR_INSTANCE_ID,'
1538 ||' PROJECT_ID,'
1539 ||' TASK_ID,'
1540 ||' PLANNING_GROUP,'
1541 ||' UNIT_NUMBER,'
1542 ||' ORDER_NUMBER,'
1543 ||' REPETITIVE_SCHEDULE_ID,'
1544 ||' SELLING_PRICE,'
1545 ||' DMD_LATENESS_COST,'
1546 ||' REQUEST_DATE,'
1547 ||' ORDER_PRIORITY,'
1548 ||' SALES_ORDER_LINE_ID,'
1549 ||' SUPPLY_ID,'
1550 ||' SOURCE_ORG_INSTANCE_ID,'
1551 ||' ORIGINAL_SYSTEM_REFERENCE,'
1552 ||' ORIGINAL_SYSTEM_LINE_REFERENCE,'
1553 ||' DEMAND_SOURCE_TYPE,'
1554 ||' CUSTOMER_ID,'
1555 ||' CUSTOMER_SITE_ID,'
1556 ||' SHIP_TO_SITE_ID,'
1557 ||' REFRESH_NUMBER,'
1558 ||' LAST_UPDATE_DATE,'
1559 ||' LAST_UPDATED_BY,'
1560 ||' CREATION_DATE,'
1561 ||' CREATED_BY,'
1562 ||' ORDER_DATE_TYPE_CODE,'
1563 ||' SCHEDULE_ARRIVAL_DATE,'
1564 ||' LATEST_ACCEPTABLE_DATE,'
1565 ||' SHIP_TO_LOCATION_ID,'
1566 ||' SHIPPING_METHOD_CODE)'
1567 ||'VALUES'
1568 ||'( -1,'
1569 ||' MSC_DEMANDS_S.nextval,'
1570 ||' :INVENTORY_ITEM_ID,'
1571 ||' :ORGANIZATION_ID,'
1572 ||' :SCHEDULE_DESIGNATOR_ID,'
1573 ||' :USING_ASSEMBLY_ITEM_ID,'
1574 ||' :USING_ASSEMBLY_DEMAND_DATE,'
1575 ||' :USING_REQUIREMENT_QUANTITY,'
1576 ||' :ASSEMBLY_DEMAND_COMP_DATE,'
1577 ||' :DEMAND_TYPE,'
1578 ||' :DAILY_DEMAND_RATE,'
1579 ||' :ORIGINATION_TYPE,'
1580 ||' :v_source_organization_id,'
1581 ||' :DISPOSITION_ID,'
1582 ||' :RESERVATION_ID,'
1583 ||' :OPERATION_SEQ_NUM,'
1584 ||' :DEMAND_CLASS,'
1585 ||' :PROMISE_DATE,'
1586 ||' :LINK_TO_LINE_ID ,'
1587 ||' :SR_INSTANCE_ID,'
1588 ||' :PROJECT_ID,'
1589 ||' :TASK_ID,'
1590 ||' :PLANNING_GROUP,'
1591 ||' :END_ITEM_UNIT_NUMBER, '
1592 ||' :ORDER_NUMBER,'
1593 ||' :REPETITIVE_SCHEDULE_ID,'
1594 ||' :SELLING_PRICE,'
1595 ||' :DMD_LATENESS_COST,'
1596 ||' :REQUEST_DATE,'
1597 ||' :ORDER_PRIORITY,'
1598 ||' :SALES_ORDER_LINE_ID,'
1599 ||' :v_supply_id,'
1600 ||' :v_source_sr_instance_id,'
1601 ||' :ORIGINAL_SYSTEM_REFERENCE,'
1602 ||' :ORIGINAL_SYSTEM_LINE_REFERENCE,'
1603 ||' :DEMAND_SOURCE_TYPE,'
1604 ||' :CUSTOMER_ID,'
1605 ||' :CUSTOMER_SITE_ID,'
1606 ||' :SHIP_TO_SITE_ID,'
1607 ||' :v_last_collection_id,'
1608 ||' :v_current_date,'
1609 ||' :v_current_user,'
1610 ||' :v_current_date,'
1611 ||' :v_current_user,'
1612 ||' :ORDER_DATE_TYPE_CODE,'
1613 ||' :SCHEDULE_ARRIVAL_DATE,'
1614 ||' :LATEST_ACCEPTABLE_DATE,'
1615 ||' :SHIP_TO_LOCATION_ID,'
1616 ||' :SHIPPING_METHOD_CODE)';
1617
1618 c_count:=0;
1619
1620 OPEN c1;
1621
1622 IF (c1%ISOPEN) THEN
1623 LOOP
1624
1625 --
1626 -- Retrieve the next set of rows if we are currently not in the
1627 -- middle of processing a fetched set or rows.
1628 --
1629 IF (lb_FetchComplete) THEN
1630 EXIT;
1631 END IF;
1632
1633 -- Fetch the next set of rows
1634 FETCH c1 BULK COLLECT INTO lb_INVENTORY_ITEM_ID,
1635 lb_ORGANIZATION_ID ,
1636 lb_USING_ASSEMBLY_ITEM_ID,
1637 lb_USING_ASSEMBLY_DEMAND_DATE,
1638 lb_USING_REQUIREMENT_QUANTITY,
1639 lb_ASSEMBLY_DEMAND_COMP_DATE,
1640 lb_DEMAND_TYPE ,
1641 lb_DAILY_DEMAND_RATE,
1642 lb_ORIGINATION_TYPE ,
1643 lb_SOURCE_ORGANIZATION_ID,
1644 lb_DISPOSITION_ID ,
1645 lb_RESERVATION_ID ,
1646 lb_OPERATION_SEQ_NUM,
1647 lb_DEMAND_CLASS,
1648 lb_PROMISE_DATE,
1649 lb_LINK_TO_LINE_ID,
1650 lb_REPETITIVE_SCHEDULE_ID,
1651 lb_SR_INSTANCE_ID,
1652 lb_PROJECT_ID,
1653 lb_TASK_ID,
1654 lb_PLANNING_GROUP,
1655 lb_END_ITEM_UNIT_NUMBER,
1656 lb_ORDER_NUMBER,
1657 lb_SCHEDULE_DESIGNATOR_ID,
1658 lb_SELLING_PRICE,
1659 lb_DMD_LATENESS_COST,
1660 lb_REQUEST_DATE,
1661 lb_ORDER_PRIORITY,
1662 lb_SALES_ORDER_LINE_ID,
1663 lb_DEMAND_SCHEDULE_NAME,
1664 lb_DELETED_FLAG,
1665 lb_customer_id,
1666 lb_CUSTOMER_SITE_ID ,
1667 lb_SHIP_TO_SITE_ID,
1668 lb_OR_SYSTEM_REFERENCE,
1669 lb_OR_SYSTEM_LINE_REFERENCE,
1670 lb_demand_source_type,
1671 lb_ORDER_DATE_TYPE_CODE,
1672 lb_SCHEDULE_ARRIVAL_DATE,
1673 lb_LATEST_ACCEPTABLE_DATE,
1674 lb_SHIPPING_METHOD_CODE,
1675 lb_ship_to_location_id
1676 LIMIT ln_rows_to_fetch;
1677
1678 -- Since we are only fetching records if either (1) this is the first
1679 -- fetch or (2) the previous fetch did not retrieve all of the
1680 -- records, then at least one row should always be fetched. But
1681 -- checking just to make sure.
1682 EXIT WHEN lb_DISPOSITION_ID.count = 0;
1683
1684 -- Check if all of the rows have been fetched. If so, indicate that
1685 -- the fetch is complete so that another fetch is not made.
1686 -- Additional check is introduced for the following reasons
1687 -- In 9i, the table of records gets modified but in 8.1.6 the table of records is
1688 -- unchanged after the fetch(bug#2995144)
1689 IF (c1%NOTFOUND) THEN
1690 lb_FetchComplete := TRUE;
1691 END IF;
1692
1693 FOR j IN 1..lb_DISPOSITION_ID.COUNT LOOP
1694
1695 BEGIN
1696 --MSC_CL_COLLECTION.v_source_organization_id := c_rec.source_organization_id;
1697
1698 MSC_CL_COLLECTION.v_supply_id := Null;
1699 MSC_CL_COLLECTION.v_source_organization_id := Null;
1700 MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1701 IF (lb_demand_source_type(j) = 8
1702 AND lb_OR_SYSTEM_REFERENCE(j) <> '-1'
1703 AND lb_OR_SYSTEM_LINE_REFERENCE(j) <> '-1' ) THEN -- Internal Sales Orders
1704
1705
1706 IF (MSC_CL_COLLECTION.v_apps_ver = MSC_UTIL.G_APPS110 OR MSC_CL_COLLECTION.v_is_legacy_refresh) THEN -- Version
1707 BEGIN
1708 MSC_CL_COLLECTION.v_supply_id := Null;
1709 MSC_CL_COLLECTION.v_source_organization_id := Null;
1710 MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1711 lv_supply_stmt :=
1712 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1713 ||' FROM '|| lv_supply_tbl
1714 ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1715 ||' AND PLAN_ID = -1'
1716 ||' AND order_number = :ORIGINAL_SYSTEM_REFERENCE'
1717 ||' AND to_char(purch_line_num) = :ORIGINAL_SYSTEM_LINE_REFERENCE '
1718 ||' AND order_type = 2 '
1719 ||' AND source_organization_id is not null ';
1720
1721
1722 EXECUTE IMMEDIATE lv_supply_stmt
1723 INTO MSC_CL_COLLECTION.v_supply_id, MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
1724 USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
1725 EXCEPTION
1726 WHEN NO_DATA_FOUND THEN NULL;
1727 WHEN OTHERS THEN NULL;
1728
1729 END ;
1730 ELSIF MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115 THEN --Version
1731
1732 BEGIN --R11i2
1733 MSC_CL_COLLECTION.v_supply_id := Null;
1734 MSC_CL_COLLECTION.v_source_organization_id := Null;
1735 MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1736 lv_supply_stmt :=
1737 'SELECT TRANSACTION_ID ,ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID '
1738 ||' FROM '|| lv_supply_tbl
1739 ||' WHERE SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
1740 ||' AND PLAN_ID = -1'
1741 ||' AND disposition_id = to_number(:ORIGINAL_SYSTEM_REFERENCE) '
1742 ||' AND po_line_id = to_number(:ORIGINAL_SYSTEM_LINE_REFERENCE) '
1743 ||' AND order_type = 2 '
1744 ||' AND source_organization_id is not null ';
1745
1746 EXECUTE IMMEDIATE lv_supply_stmt
1747 INTO MSC_CL_COLLECTION.v_supply_id,MSC_CL_COLLECTION.v_source_organization_id, MSC_CL_COLLECTION.v_source_sr_instance_id
1748 USING lb_OR_SYSTEM_REFERENCE(j),lb_OR_SYSTEM_LINE_REFERENCE(j);
1749
1750 EXCEPTION
1751 WHEN NO_DATA_FOUND THEN NULL;
1752 WHEN OTHERS THEN NULL;
1753 END ;
1754 END IF; -- Version
1755 END IF; -- Internal Sales Orders
1756 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
1757
1758 --================= mds demands ==================
1759 IF lb_ORIGINATION_TYPE(j) IN ( 6,7,15,24,42) THEN
1760
1761 IF lb_DELETED_FLAG(j) = MSC_UTIL.SYS_YES THEN
1762
1763 UPDATE MSC_DEMANDS
1764 SET USING_REQUIREMENT_QUANTITY= 0,
1765 DAILY_DEMAND_RATE= 0,
1766 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1767 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1768 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1769 WHERE PLAN_ID= -1
1770 AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1771 AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
1772 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1773 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
1774
1775 ELSE
1776
1777 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1778 UPDATE MSC_DEMANDS
1779 SET
1780 INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j),
1781 ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1782 OLD_USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1783 OLD_USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
1784 OLD_ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1785 USING_ASSEMBLY_ITEM_ID= lb_USING_ASSEMBLY_ITEM_ID(j),
1786 USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
1787 USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1788 ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1789 DEMAND_TYPE= lb_DEMAND_TYPE(j),
1790 DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
1791 SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
1792 RESERVATION_ID= lb_RESERVATION_ID(j),
1793 OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
1794 DEMAND_CLASS= lb_DEMAND_CLASS(j),
1795 PROMISE_DATE = lb_PROMISE_DATE(j),
1796 LINK_TO_LINE_ID = lb_LINK_TO_LINE_ID(j),
1797 PROJECT_ID= lb_PROJECT_ID(j),
1798 TASK_ID= lb_TASK_ID(j),
1799 PLANNING_GROUP= lb_PLANNING_GROUP(j),
1800 UNIT_NUMBER= lb_END_ITEM_UNIT_NUMBER(j),
1801 ORDER_NUMBER= lb_ORDER_NUMBER(j),
1802 SELLING_PRICE= lb_SELLING_PRICE(j),
1803 DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
1804 REQUEST_DATE= lb_REQUEST_DATE(j),
1805 ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1806 SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1807 SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1808 SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1809 ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1810 ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1811 DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1812 customer_id= lb_customer_id(j),
1813 CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1814 SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1815 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1816 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1817 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1818 ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1819 SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1820 LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1821 SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
1822 SHIPPING_METHOD_CODE=lb_SHIPPING_METHOD_CODE(j)
1823 WHERE PLAN_ID= -1
1824 AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1825 AND ORIGINATION_TYPE= lb_ORIGINATION_TYPE(j)
1826 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1827 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
1828
1829 END IF; -- DELETED_FLAG
1830
1831 ELSIF lb_ORIGINATION_TYPE(j)=8 THEN /* Manual MDS */
1832
1833 IF lb_DELETED_FLAG(j)= MSC_UTIL.SYS_YES THEN
1834
1835 UPDATE MSC_DEMANDS
1836 SET USING_REQUIREMENT_QUANTITY= 0,
1837 DAILY_DEMAND_RATE= 0,
1838 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1839 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1840 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
1841 WHERE PLAN_ID= -1
1842 AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1843 AND ORIGINATION_TYPE IN (6,7,15,8,24)
1844 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1845 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
1846
1847 ELSE
1848
1849 /* ATP SUMMARY CHANGES Added the OLD_**** Columns */
1850 UPDATE MSC_DEMANDS
1851 SET
1852 INVENTORY_ITEM_ID= lb_INVENTORY_ITEM_ID(j),
1853 ORGANIZATION_ID= lb_ORGANIZATION_ID(j),
1854 OLD_USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1855 OLD_USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
1856 OLD_ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1857 USING_ASSEMBLY_ITEM_ID= lb_USING_ASSEMBLY_ITEM_ID(j),
1858 USING_ASSEMBLY_DEMAND_DATE= lb_USING_ASSEMBLY_DEMAND_DATE(j),
1859 USING_REQUIREMENT_QUANTITY= lb_USING_REQUIREMENT_QUANTITY(j),
1860 ASSEMBLY_DEMAND_COMP_DATE= lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1861 DEMAND_TYPE= lb_DEMAND_TYPE(j),
1862 DAILY_DEMAND_RATE= lb_DAILY_DEMAND_RATE(j),
1863 SOURCE_ORGANIZATION_ID= MSC_CL_COLLECTION.v_source_organization_id,
1864 RESERVATION_ID= lb_RESERVATION_ID(j),
1865 OP_SEQ_NUM= lb_OPERATION_SEQ_NUM(j),
1866 DEMAND_CLASS= lb_DEMAND_CLASS(j),
1867 PROMISE_DATE = lb_PROMISE_DATE(j),
1868 PROJECT_ID= lb_PROJECT_ID(j),
1869 TASK_ID= lb_TASK_ID(j),
1870 PLANNING_GROUP= lb_PLANNING_GROUP(j),
1871 UNIT_NUMBER= lb_END_ITEM_UNIT_NUMBER(j),
1872 ORDER_NUMBER= lb_ORDER_NUMBER(j),
1873 SELLING_PRICE= lb_SELLING_PRICE(j),
1874 DMD_LATENESS_COST= lb_DMD_LATENESS_COST(j),
1875 REQUEST_DATE= lb_REQUEST_DATE(j),
1876 ORDER_PRIORITY= lb_ORDER_PRIORITY(j),
1877 SALES_ORDER_LINE_ID= lb_SALES_ORDER_LINE_ID(j),
1878 SUPPLY_ID = MSC_CL_COLLECTION.v_supply_id,
1879 SOURCE_ORG_INSTANCE_ID = MSC_CL_COLLECTION.v_source_sr_instance_id,
1880 ORIGINAL_SYSTEM_REFERENCE= lb_OR_SYSTEM_REFERENCE(j),
1881 ORIGINAL_SYSTEM_LINE_REFERENCE= lb_OR_SYSTEM_LINE_REFERENCE(j),
1882 DEMAND_SOURCE_TYPE= lb_demand_source_type(j),
1883 customer_id= lb_customer_id(j),
1884 CUSTOMER_SITE_ID = lb_CUSTOMER_SITE_ID(j),
1885 SHIP_TO_SITE_ID= lb_SHIP_TO_SITE_ID(j),
1886 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
1887 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
1888 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
1889 ORDER_DATE_TYPE_CODE=lb_ORDER_DATE_TYPE_CODE(j),
1890 SCHEDULE_ARRIVAL_DATE=lb_SCHEDULE_ARRIVAL_DATE(j),
1891 LATEST_ACCEPTABLE_DATE=lb_LATEST_ACCEPTABLE_DATE(j),
1892 SHIP_TO_LOCATION_ID=lb_SHIP_TO_LOCATION_ID(j),
1893 SHIPPING_METHOD_CODE=lb_SHIPPING_METHOD_CODE(j)
1894 WHERE PLAN_ID= -1
1895 AND DISPOSITION_ID= lb_DISPOSITION_ID(j)
1896 AND ORIGINATION_TYPE IN (6,7,15,8,24)
1897 AND SR_INSTANCE_ID= lb_SR_INSTANCE_ID(j)
1898 AND ORGANIZATION_ID = lb_ORGANIZATION_ID(J);
1899
1900 END IF; -- DELETED_FLAG
1901
1902 END IF; -- ORIGINATION_TYPE
1903
1904 END IF; -- refresh mode
1905
1906 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR
1907
1908 ( lb_DELETED_FLAG(j)<> MSC_UTIL.SYS_YES AND SQL%NOTFOUND) THEN
1909
1910 EXECUTE IMMEDIATE lv_sql_stmt
1911 USING
1912 lb_INVENTORY_ITEM_ID(j),
1913 lb_ORGANIZATION_ID(j),
1914 lb_SCHEDULE_DESIGNATOR_ID(j),
1915 lb_USING_ASSEMBLY_ITEM_ID(j),
1916 lb_USING_ASSEMBLY_DEMAND_DATE(j),
1917 lb_USING_REQUIREMENT_QUANTITY(j),
1918 lb_ASSEMBLY_DEMAND_COMP_DATE(j),
1919 lb_DEMAND_TYPE(j),
1920 lb_DAILY_DEMAND_RATE(j),
1921 lb_ORIGINATION_TYPE(j),
1922 MSC_CL_COLLECTION.v_source_organization_id,
1923 lb_DISPOSITION_ID(j),
1924 lb_RESERVATION_ID(j),
1925 lb_OPERATION_SEQ_NUM(j),
1926 lb_DEMAND_CLASS(j),
1927 lb_PROMISE_DATE(j),
1928 lb_LINK_TO_LINE_ID(j),
1929 lb_SR_INSTANCE_ID(j),
1930 lb_PROJECT_ID(j),
1931 lb_TASK_ID(j),
1932 lb_PLANNING_GROUP(j),
1933 lb_END_ITEM_UNIT_NUMBER(j),
1934 lb_ORDER_NUMBER(j),
1935 lb_REPETITIVE_SCHEDULE_ID(j),
1936 lb_SELLING_PRICE(j),
1937 lb_DMD_LATENESS_COST(j),
1938 lb_REQUEST_DATE(j),
1939 lb_ORDER_PRIORITY(j),
1940 lb_SALES_ORDER_LINE_ID(j),
1941 MSC_CL_COLLECTION.v_supply_id,
1942 MSC_CL_COLLECTION.v_source_sr_instance_id,
1943 lb_OR_SYSTEM_REFERENCE(j),
1944 lb_OR_SYSTEM_LINE_REFERENCE(j),
1945 lb_demand_source_type(j),
1946 lb_customer_id(j),
1947 lb_CUSTOMER_SITE_ID(j),
1948 lb_SHIP_TO_SITE_ID(j),
1949 MSC_CL_COLLECTION.v_last_collection_id,
1950 MSC_CL_COLLECTION.v_current_date,
1951 MSC_CL_COLLECTION.v_current_user,
1952 MSC_CL_COLLECTION.v_current_date,
1953 MSC_CL_COLLECTION.v_current_user,
1954 lb_ORDER_DATE_TYPE_CODE(j),
1955 lb_SCHEDULE_ARRIVAL_DATE(j),
1956 lb_LATEST_ACCEPTABLE_DATE(j),
1957 lb_SHIP_TO_LOCATION_ID(j),
1958 lb_SHIPPING_METHOD_CODE(j);
1959
1960 END IF; -- complete_refresh, sql%notfound
1961
1962 c_count:= c_count+1;
1963
1964 IF c_count>MSC_CL_COLLECTION.PBS THEN
1965 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
1966 c_count:= 0;
1967 END IF;
1968 MSC_CL_COLLECTION.v_supply_id := Null;
1969 MSC_CL_COLLECTION.v_source_organization_id := Null;
1970 MSC_CL_COLLECTION.v_source_sr_instance_id := Null;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974
1975 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
1976
1977 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1978 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1979 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1980 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1982
1983 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1984 RAISE;
1985
1986 ELSE
1987
1988 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
1989
1990 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
1991 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
1992 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DEMAND');
1993 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DEMANDS');
1994 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
1995
1996 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
1997 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
1998 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( lb_INVENTORY_ITEM_ID(j)));
1999 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2000
2001 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2002 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2003 FND_MESSAGE.SET_TOKEN('VALUE',
2004 MSC_GET_NAME.ORG_CODE( lb_ORGANIZATION_ID(j),
2005 MSC_CL_COLLECTION.v_instance_id));
2006 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2007
2008 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2009 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_TYPE');
2010 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(lb_ORGANIZATION_ID(j)));
2011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2012
2013 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2014 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORIGINATION_TYPE');
2015 FND_MESSAGE.SET_TOKEN('VALUE',
2016 MSC_GET_NAME.LOOKUP_MEANING('MRP_DEMAND_ORIGINATION',
2017 lb_ORIGINATION_TYPE(j)));
2018 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2019
2020 IF lb_DEMAND_SCHEDULE_NAME(j) IS NOT NULL THEN
2021 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2022 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_SCHEDULE_NAME');
2023 FND_MESSAGE.SET_TOKEN('VALUE', lb_DEMAND_SCHEDULE_NAME(j));
2024 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2025 END IF;
2026
2027 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2028 END IF;
2029
2030 END;
2031
2032 END LOOP;
2033 END LOOP;
2034 END IF;
2035 CLOSE c1;
2036
2037 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN COMMIT; END IF;
2038
2039 IF (MSC_CL_COLLECTION.v_apps_ver >= MSC_UTIL.G_APPS115) then
2040 IF (MSC_CL_COLLECTION.v_is_incremental_refresh) OR (MSC_CL_COLLECTION.v_is_cont_refresh and MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) THEN --Version
2041 /* call the function to link the Demand_id and Parent_id in MSC_DEMANDS
2042 if mds is incremental*/
2043 MSC_CL_COLLECTION.v_exchange_mode := MSC_UTIL.SYS_NO;
2044 IF MSC_CL_DEMAND_ODS_LOAD.LINK_PARENT_SALES_ORDERS_MDS THEN
2045 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Linking of Sales Order line in MDS to its Parent Sales orders is successful.....');
2046 ELSE
2047 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Linking Sales order line in MDS to its parent Sales order......');
2048 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2049 END IF;
2050 END IF;
2051 END IF;
2052
2053 END LOAD_DEMAND;
2054
2055
2056
2057 PROCEDURE link_ISO_IR(pSOtbl varchar2,pSupplyTbl varchar2) IS
2058 lv_sql VARCHAR2(4000);
2059 BEGIN
2060
2061 lv_sql:=
2062 'UPDATE '||pSOtbl||' a
2063 SET (SUPPLY_ID, SOURCE_ORGANIZATION_ID, SOURCE_ORG_INSTANCE_ID)
2064 = (SELECT b.TRANSACTION_ID ,b.ORGANIZATION_ID, b.SOURCE_SR_INSTANCE_ID
2065 FROM '||pSupplyTbl||' b
2066 WHERE b.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id||'
2067 AND b.PLAN_ID = -1
2068 AND b.disposition_id = a.ORIGINAL_SYSTEM_REFERENCE
2069 AND b.po_line_id = a.ORIGINAL_SYSTEM_LINE_REFERENCE
2070 AND b.order_type IN (2,73)
2071 AND b.source_organization_id is not null
2072 )
2073 WHERE a.SR_INSTANCE_ID = '||MSC_CL_COLLECTION.v_instance_id
2074 ||' AND a.demand_source_type = 8
2075 AND a.original_system_reference <> ''-1''
2076 AND a.original_system_line_reference <> ''-1''
2077 AND a.REFRESH_NUMBER = '||MSC_CL_COLLECTION.v_last_collection_id;
2078 EXECUTE IMMEDIATE lv_sql;
2079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'link_ISO_IR: Rows updated - '||SQL%ROWCOUNT);
2080 commit;
2081 END;
2082
2083 -- ========================= LOAD SALES ORDER ==============
2084
2085 PROCEDURE LOAD_SALES_ORDER IS
2086
2087 lv_tbl VARCHAR2(30);
2088 lv_supply_tbl VARCHAR2(30);
2089 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
2090 lv_exchange_mode NUMBER:= MSC_UTIL.SYS_NO;
2091 lv_sql_stmt VARCHAR2(5000);
2092 lv_sql1_1 VARCHAR2(4000);
2093 lv_sql1_2 VARCHAR2(1000);
2094 lv_sql1_3 VARCHAR2(4000);
2095 lv_sql2 VARCHAR2(4000);
2096 lv_sql3 VARCHAR2(4000);
2097 lv_sql4 VARCHAR2(4000);
2098 lv_sql5 VARCHAR2(30000);
2099
2100
2101 BEGIN
2102 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' LOAD_SALES_ORDER started');
2103
2104 IF MSC_CL_COLLECTION.v_so_exchange_mode= MSC_UTIL.SYS_YES AND
2105 MSC_CL_COLLECTION.is_msctbl_partitioned('MSC_SALES_ORDERS') THEN
2106 lv_exchange_mode := MSC_UTIL.SYS_YES;
2107 END IF;
2108
2109 IF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2110 lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2111 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2112 ELSE
2113 lv_tbl:= 'MSC_SALES_ORDERS';
2114 lv_supply_tbl:= 'MSC_SUPPLIES';
2115 END IF;
2116
2117 /** PREPLACE CHANGE START **/
2118
2119 IF (MSC_CL_COLLECTION.v_is_partial_refresh AND
2120 (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_NO) AND
2121 (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_NO) AND
2122 (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_NO) AND
2123 (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_NO) AND
2124 (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_NO) ) THEN
2125
2126 lv_supply_tbl := 'MSC_SUPPLIES';
2127
2128 ELSIF lv_exchange_mode=MSC_UTIL.SYS_YES THEN
2129
2130 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2131
2132 END IF;
2133 /** PREPLACE CHANGE END **/
2134
2135
2136 /* In cont. collections if any of the Supply is targeted */
2137 IF (MSC_CL_COLLECTION.v_is_cont_refresh) THEN
2138 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) OR
2139 (MSC_CL_COLLECTION.v_coll_prec.mps_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.mps_sn_flag = MSC_UTIL.SYS_TGT) OR
2140 (MSC_CL_COLLECTION.v_coll_prec.po_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.po_sn_flag = MSC_UTIL.SYS_TGT) OR
2141 (MSC_CL_COLLECTION.v_coll_prec.oh_flag = MSC_UTIL.SYS_YES AND MSC_CL_COLLECTION.v_coll_prec.oh_sn_flag = MSC_UTIL.SYS_TGT) OR
2142 (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES and MSC_CL_COLLECTION.v_coll_prec.usup_sn_flag = MSC_UTIL.SYS_TGT) THEN
2143 lv_supply_tbl:= 'SUPPLIES_'||MSC_CL_COLLECTION.v_instance_code;
2144 ELSE
2145 lv_supply_tbl := 'MSC_SUPPLIES';
2146 END IF;
2147 END IF;
2148
2149 /* 2140727 - Insert project_id and task_id also */
2150
2151 -- delete records
2152 IF MSC_CL_COLLECTION.v_is_so_complete_refresh THEN -- complete refresh
2153
2154 IF lv_exchange_mode=MSC_UTIL.SYS_NO THEN
2155
2156 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2157 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL);
2158 ELSE
2159 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2160 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_SALES_ORDERS', MSC_CL_COLLECTION.v_instance_id,NULL,MSC_CL_COLLECTION.v_sub_str);
2161 END IF;
2162
2163 END IF;
2164
2165 BEGIN
2166
2167 IF ((MSC_CL_COLLECTION.v_coll_prec.org_group_flag <> MSC_UTIL.G_ALL_ORGANIZATIONS) AND (lv_exchange_mode=MSC_UTIL.SYS_YES)) THEN
2168
2169 lv_tbl:= 'SALES_ORDERS_'||MSC_CL_COLLECTION.v_instance_code;
2170
2171 lv_sql_stmt:=
2172 'INSERT INTO '||lv_tbl
2173 ||' SELECT * from MSC_SALES_ORDERS'
2174 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
2175 ||' AND organization_id not '||MSC_UTIL.v_in_org_str;
2176
2177 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The sql statement is '||lv_sql_stmt);
2178 EXECUTE IMMEDIATE lv_sql_stmt;
2179 COMMIT;
2180 END IF;
2181
2182 EXCEPTION
2183 WHEN OTHERS THEN
2184
2185 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2186 RAISE;
2187 END;
2188
2189 ELSE
2190
2191 LOOP
2192 DELETE FROM MSC_SALES_ORDERS
2193 WHERE ROW_TYPE = 3
2194 AND sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2195 AND SR_DEMAND_ID IN (SELECT so.DEMAND_ID
2196 FROM MSC_ST_SALES_ORDERS so
2197 WHERE so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2198 AND so.ROW_TYPE = 2 )
2199 AND ROWNUM <= ln_rows_to_fetch;
2200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE1 ROWSDELETED :'||SQL%rowcount);
2201 EXIT WHEN SQL%ROWCOUNT = 0;
2202 COMMIT;
2203 END LOOP;
2204
2205 LOOP
2206 DELETE FROM MSC_SALES_ORDERS
2207 WHERE SR_INSTANCE_ID = MSC_CL_COLLECTION.v_instance_id
2208 AND (ROW_TYPE,SR_DEMAND_ID) IN (SELECT so.ROW_TYPE,so.DEMAND_ID
2209 FROM MSC_ST_SALES_ORDERS so
2210 WHERE so.DELETED_FLAG = 1
2211 AND so.sr_instance_id = MSC_CL_COLLECTION.v_instance_id)
2212 AND ROWNUM <= ln_rows_to_fetch;
2213 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'NetCHg DELETE2 ROWSDELETED :'||SQL%rowcount);
2214 EXIT WHEN SQL%ROWCOUNT = 0;
2215 COMMIT;
2216 END LOOP;
2217
2218 END IF; -- complete refresh
2219
2220
2221
2222 BEGIN
2223
2224 lv_sql1_1 :=
2225 'SELECT
2226 t1.INVENTORY_ITEM_ID,
2227 so.ORGANIZATION_ID,
2228 so.PRIMARY_UOM_QUANTITY,
2229 so.RESERVATION_TYPE,
2230 so.RESERVATION_QUANTITY,
2231 so.DEMAND_SOURCE_TYPE,
2232 so.DEMAND_SOURCE_HEADER_ID,
2233 so.COMPLETED_QUANTITY,
2234 so.SUBINVENTORY,
2235 so.DEMAND_CLASS,
2236 decode(nvl(so.MFG_LEAD_TIME,0),0, so.REQUIREMENT_DATE,
2237 MSC_CALENDAR.DATE_OFFSET(so.ORGANIZATION_ID,
2238 so.SR_INSTANCE_ID,
2239 1,
2240 so.REQUIREMENT_DATE,
2241 -(so.MFG_LEAD_TIME) )
2242 ) REQUIREMENT_DATE,
2243 so.DEMAND_ID SR_DEMAND_ID,
2244 so.ROW_TYPE,
2245 so.DEMAND_SOURCE_LINE,
2246 so.DEMAND_SOURCE_DELIVERY,
2247 so.DEMAND_SOURCE_NAME,
2248 so.PARENT_DEMAND_ID,
2249 so.SALES_ORDER_NUMBER,
2250 so.FORECAST_VISIBLE ,
2251 so.DEMAND_VISIBLE ,
2252 so.SALESREP_CONTACT,
2253 so.SALESREP_ID,
2254 mtil.tp_id CUSTOMER_ID,
2255 mtsila.tp_site_id SHIP_TO_SITE_USE_ID,
2256 mtsilb.tp_site_id BILL_TO_SITE_USE_ID,
2257 so.REQUEST_DATE,
2258 so.PROJECT_ID,
2259 so.TASK_ID,
2260 so.PLANNING_GROUP,
2261 so.DEMAND_PRIORITY,
2262 so.PROMISE_DATE,
2263 so.LINK_TO_LINE_ID,
2264 so.SELLING_PRICE,
2265 so.END_ITEM_UNIT_NUMBER,
2266 so.CTO_FLAG,
2267 t2.INVENTORY_ITEM_ID ORIGINAL_ITEM_ID,
2268 decode(so.available_to_mrp,''1'',''Y'',''Y'',''Y'',''N'') available_to_mrp,
2269 so.SR_INSTANCE_ID,
2270 so.ATP_REFRESH_NUMBER,
2271 nvl(so.ORIGINAL_SYSTEM_REFERENCE,''-1'') ORIGINAL_SYSTEM_REFERENCE,
2272 nvl(so.ORIGINAL_SYSTEM_LINE_REFERENCE,''-1'') ORIGINAL_SYSTEM_LINE_REFERENCE,
2273 so.MFG_LEAD_TIME,
2274 t3.inventory_item_id ORDERED_ITEM_ID,
2275 '||MSC_CL_COLLECTION.v_last_collection_id||' last_collection_id,
2276 so.CUST_PO_NUMBER,
2277 so.CUSTOMER_LINE_NUMBER,
2278 so.ORG_FIRM_FLAG,
2279 so.SHIP_SET_ID,
2280 so.ARRIVAL_SET_ID,
2281 so.SHIP_SET_NAME,
2282 so.ARRIVAL_SET_NAME,
2283 '''||MSC_CL_COLLECTION.v_current_date||''' current_date1,
2284 '||MSC_CL_COLLECTION.v_current_user|| ' current_user1,
2285 '''||MSC_CL_COLLECTION.v_current_date||''' current_date2,
2286 '||MSC_CL_COLLECTION.v_current_user|| ' current_user2,
2287 so.ATO_LINE_ID,
2288 so.ORDER_DATE_TYPE_CODE,
2289 so.SCHEDULE_ARRIVAL_DATE,
2290 so.LATEST_ACCEPTABLE_DATE,
2291 mtsila.location_id ship_to_location_id,
2292 so.SHIPPING_METHOD_CODE,
2293 so.INTRANSIT_LEAD_TIME,
2294 so.customer_id sr_customer_acct_id ,
2295 so.DEMAND_SOURCE_LINE SR_SO_LINEID ';
2296 lv_sql1_2 :=
2297 ' ,MSC_DEMANDS_S.nextval DEMAND_ID ';
2298
2299 lv_sql1_3:='
2300 FROM MSC_ITEM_ID_LID t1,
2301 MSC_ITEM_ID_LID t2,
2302 MSC_ITEM_ID_LID t3,
2303 MSC_ST_SALES_ORDERS so,
2304 MSC_TP_ID_LID mtil,
2305 MSC_TP_SITE_ID_LID mtsila,
2306 MSC_TP_SITE_ID_LID mtsilb
2307 WHERE t1.SR_INVENTORY_ITEM_ID= so.INVENTORY_ITEM_ID
2308 AND t1.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2309 AND so.SR_INSTANCE_ID= '||MSC_CL_COLLECTION.v_instance_id||'
2310 AND so.ROW_TYPE= xxx_ROW_TYPE
2311 AND t2.SR_INVENTORY_ITEM_ID(+) = so.ORIGINAL_ITEM_ID
2312 AND t2.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2313 AND t3.SR_INVENTORY_ITEM_ID(+) = so.ORDERED_ITEM_ID
2314 AND t3.SR_INSTANCE_ID(+) = so.SR_INSTANCE_ID
2315 AND so.DELETED_FLAG= 2
2316 and mtil.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2317 and mtil.sr_tp_id(+) = so.customer_id
2318 and mtil.partner_type(+) = 2
2319 and mtsila.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2320 and mtsila.sr_tp_site_id(+) = so.SHIP_TO_SITE_USE_ID
2321 and mtsila.partner_type(+) = 2
2322 and mtsilb.sr_instance_id(+) = '||MSC_CL_COLLECTION.v_instance_id||'
2323 and mtsilb.sr_tp_site_id(+) = so.BILL_TO_SITE_USE_ID
2324 and mtsilb.partner_type(+) = 2 ';
2325
2326
2327 lv_sql2:=
2328 ') s
2329 ON (d.SR_INSTANCE_ID= s.SR_INSTANCE_ID
2330 AND d.SR_DEMAND_ID= s.SR_DEMAND_ID
2331 AND d.ROW_TYPE= s.ROW_TYPE)
2332 WHEN MATCHED THEN
2333 UPDATE SET
2334 d.OLD_PRIMARY_UOM_QUANTITY= d.PRIMARY_UOM_QUANTITY,
2335 d.OLD_RESERVATION_QUANTITY= xxx_RESERVATION_QUANTITY,
2336 d.OLD_COMPLETED_QUANTITY= d.COMPLETED_QUANTITY,
2337 d.OLD_REQUIREMENT_DATE= d.REQUIREMENT_DATE,
2338 d.PRIMARY_UOM_QUANTITY= s.PRIMARY_UOM_QUANTITY,
2339 d.RESERVATION_QUANTITY= s.RESERVATION_QUANTITY,
2340 d.DEMAND_SOURCE_TYPE= s.DEMAND_SOURCE_TYPE,
2341 d.DEMAND_SOURCE_HEADER_ID= s.DEMAND_SOURCE_HEADER_ID,
2342 d.COMPLETED_QUANTITY= s.COMPLETED_QUANTITY,
2343 d.SUBINVENTORY= s.SUBINVENTORY,
2344 d.DEMAND_CLASS= s.DEMAND_CLASS,
2345 d.REQUIREMENT_DATE= s.REQUIREMENT_DATE,
2346 --d.SR_DEMAND_ID = s.SR_DEMAND_ID,
2347 d.DEMAND_SOURCE_DELIVERY= s.DEMAND_SOURCE_DELIVERY,
2348 d.DEMAND_SOURCE_NAME= s.DEMAND_SOURCE_NAME,
2349 d.PARENT_DEMAND_ID= s.PARENT_DEMAND_ID,
2350 d.SALES_ORDER_NUMBER= s.SALES_ORDER_NUMBER,
2351 d.FORECAST_VISIBLE = s.FORECAST_VISIBLE ,
2352 d.DEMAND_VISIBLE = s.DEMAND_VISIBLE ,
2353 d.SALESREP_CONTACT= s.SALESREP_CONTACT,
2354 d.SALESREP_ID= s.SALESREP_ID,
2355 d.CUSTOMER_ID = s.CUSTOMER_ID,
2356 d.SHIP_TO_SITE_USE_ID = s.SHIP_TO_SITE_USE_ID,
2357 d.BILL_TO_SITE_USE_ID = s.BILL_TO_SITE_USE_ID,
2358 d.REQUEST_DATE = s.REQUEST_DATE,
2359 d.PROJECT_ID = s.PROJECT_ID,
2360 d.TASK_ID = s.TASK_ID,
2361 d.PLANNING_GROUP = s.PLANNING_GROUP,
2362 d.DEMAND_PRIORITY = s.DEMAND_PRIORITY,
2363 d.PROMISE_DATE = s.PROMISE_DATE,
2364 d.LINK_TO_LINE_ID = s.LINK_TO_LINE_ID,
2365 d.SELLING_PRICE = s.SELLING_PRICE,
2366 d.END_ITEM_UNIT_NUMBER = s.END_ITEM_UNIT_NUMBER,
2367 d.ORIGINAL_ITEM_ID = s.ORIGINAL_ITEM_ID,
2368 d.AVAILABLE_TO_MRP = s.AVAILABLE_TO_MRP,
2369 d.ATP_REFRESH_NUMBER= s.ATP_REFRESH_NUMBER,
2370 d.ORIGINAL_SYSTEM_REFERENCE= s.ORIGINAL_SYSTEM_REFERENCE,
2371 d.ORIGINAL_SYSTEM_LINE_REFERENCE= s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2372 d.MFG_LEAD_TIME = s.MFG_LEAD_TIME,
2373 d.ORDERED_ITEM_ID = s.ORDERED_ITEM_ID,
2374 d.REFRESH_NUMBER= '||MSC_CL_COLLECTION.v_last_collection_id||',
2375 d.CUST_PO_NUMBER =s.CUST_PO_NUMBER,
2376 d.CUSTOMER_LINE_NUMBER=s.CUSTOMER_LINE_NUMBER,
2377 d.ORG_FIRM_FLAG =s.ORG_FIRM_FLAG,
2378 d.SHIP_SET_ID = s.SHIP_SET_ID,
2379 d.ARRIVAL_SET_ID = s.ARRIVAL_SET_ID,
2380 d.SHIP_SET_NAME = s.SHIP_SET_NAME,
2381 d.ARRIVAL_SET_NAME = s.ARRIVAL_SET_NAME,
2382 d.LAST_UPDATE_DATE= '''||MSC_CL_COLLECTION.v_current_date||''',
2383 d.LAST_UPDATED_BY= '||MSC_CL_COLLECTION.v_current_user|| ',
2384 d.ATO_LINE_ID=s.ATO_LINE_ID,
2385 d.ORDER_DATE_TYPE_CODE=s.ORDER_DATE_TYPE_CODE,
2386 d.SCHEDULE_ARRIVAL_DATE=s.SCHEDULE_ARRIVAL_DATE,
2387 d.LATEST_ACCEPTABLE_DATE=s.LATEST_ACCEPTABLE_DATE,
2388 d.SHIP_TO_LOCATION_ID=s.SHIP_TO_LOCATION_ID,
2389 d.SHIPPING_METHOD_CODE=s.SHIPPING_METHOD_CODE,
2390 d.INTRANSIT_LEAD_TIME=s.INTRANSIT_LEAD_TIME,
2391 d.sr_customer_acct_id = s.sr_customer_acct_id,
2392 d.prev_coll_item_id = d.inventory_item_id,
2393 d.SR_SO_LINEID=s.DEMAND_SOURCE_LINE ';
2394
2395
2396 lv_sql3:=
2397 ' ( d.INVENTORY_ITEM_ID,
2398 d.ORGANIZATION_ID,
2399 d.PRIMARY_UOM_QUANTITY,
2400 d.RESERVATION_TYPE,
2401 d.RESERVATION_QUANTITY,
2402 d.DEMAND_SOURCE_TYPE,
2403 d.DEMAND_SOURCE_HEADER_ID,
2404 d.COMPLETED_QUANTITY,
2405 d.SUBINVENTORY,
2406 d.DEMAND_CLASS,
2407 d.REQUIREMENT_DATE,
2408 d.SR_DEMAND_ID,
2409 d.ROW_TYPE,
2410 d.DEMAND_SOURCE_LINE,
2411 d.DEMAND_SOURCE_DELIVERY,
2412 d.DEMAND_SOURCE_NAME,
2413 d.PARENT_DEMAND_ID,
2414 d.SALES_ORDER_NUMBER,
2415 d.FORECAST_VISIBLE ,
2416 d.DEMAND_VISIBLE ,
2417 d.SALESREP_CONTACT,
2418 d.SALESREP_ID,
2419 d.CUSTOMER_ID,
2420 d.SHIP_TO_SITE_USE_ID,
2421 d.BILL_TO_SITE_USE_ID,
2422 d.REQUEST_DATE,
2423 d.PROJECT_ID,
2424 d.TASK_ID,
2425 d.PLANNING_GROUP,
2426 d.DEMAND_PRIORITY,
2427 d.PROMISE_DATE,
2428 d.LINK_TO_LINE_ID,
2429 d.SELLING_PRICE,
2430 d.END_ITEM_UNIT_NUMBER,
2431 d.CTO_FLAG,
2432 d.ORIGINAL_ITEM_ID,
2433 d.AVAILABLE_TO_MRP,
2434 d.SR_INSTANCE_ID,
2435 d.ATP_REFRESH_NUMBER,
2436 d.ORIGINAL_SYSTEM_REFERENCE,
2437 d.ORIGINAL_SYSTEM_LINE_REFERENCE,
2438 d.MFG_LEAD_TIME,
2439 d.ORDERED_ITEM_ID,
2440 d.REFRESH_NUMBER,
2441 d.CUST_PO_NUMBER,
2442 d.CUSTOMER_LINE_NUMBER,
2443 d.ORG_FIRM_FLAG,
2444 d.SHIP_SET_ID,
2445 d.ARRIVAL_SET_ID,
2446 d.SHIP_SET_NAME,
2447 d.ARRIVAL_SET_NAME,
2448 d.LAST_UPDATE_DATE,
2449 d.LAST_UPDATED_BY,
2450 d.CREATION_DATE,
2451 d.CREATED_BY,
2452 d.ATO_LINE_ID,
2453 d.ORDER_DATE_TYPE_CODE,
2454 d.SCHEDULE_ARRIVAL_DATE,
2455 d.LATEST_ACCEPTABLE_DATE,
2456 d.SHIP_TO_LOCATION_ID,
2457 d.SHIPPING_METHOD_CODE,
2458 d.INTRANSIT_LEAD_TIME,
2459 d.sr_customer_acct_id,
2460 d.SR_SO_LINEID,
2461 d.DEMAND_ID )
2462 ';
2463
2464
2465 lv_sql4:=
2466 ' VALUES
2467 (
2468 s.INVENTORY_ITEM_ID,
2469 s.ORGANIZATION_ID,
2470 s.PRIMARY_UOM_QUANTITY,
2471 s.RESERVATION_TYPE,
2472 s.RESERVATION_QUANTITY,
2473 s.DEMAND_SOURCE_TYPE,
2474 s.DEMAND_SOURCE_HEADER_ID,
2475 s.COMPLETED_QUANTITY,
2476 s.SUBINVENTORY,
2477 s.DEMAND_CLASS,
2478 s.REQUIREMENT_DATE,
2479 s.SR_DEMAND_ID,
2480 s.ROW_TYPE,
2481 s.DEMAND_SOURCE_LINE,
2482 s.DEMAND_SOURCE_DELIVERY,
2483 s.DEMAND_SOURCE_NAME,
2484 s.PARENT_DEMAND_ID,
2485 s.SALES_ORDER_NUMBER,
2486 s.FORECAST_VISIBLE ,
2487 s.DEMAND_VISIBLE ,
2488 s.SALESREP_CONTACT,
2489 s.SALESREP_ID,
2490 s.CUSTOMER_ID,
2491 s.SHIP_TO_SITE_USE_ID,
2492 s.BILL_TO_SITE_USE_ID,
2493 s.REQUEST_DATE,
2494 s.PROJECT_ID,
2495 s.TASK_ID,
2496 s.PLANNING_GROUP,
2497 s.DEMAND_PRIORITY,
2498 s.PROMISE_DATE,
2499 s.LINK_TO_LINE_ID,
2500 s.SELLING_PRICE,
2501 s.END_ITEM_UNIT_NUMBER,
2502 s.CTO_FLAG,
2503 s.ORIGINAL_ITEM_ID,
2504 s.AVAILABLE_TO_MRP,
2505 s.SR_INSTANCE_ID,
2506 s.ATP_REFRESH_NUMBER,
2507 s.ORIGINAL_SYSTEM_REFERENCE,
2508 s.ORIGINAL_SYSTEM_LINE_REFERENCE,
2509 s.MFG_LEAD_TIME,
2510 s.ORDERED_ITEM_ID,
2511 '||MSC_CL_COLLECTION.v_last_collection_id||',
2512 s.CUST_PO_NUMBER,
2513 s.CUSTOMER_LINE_NUMBER,
2514 s.ORG_FIRM_FLAG,
2515 s.SHIP_SET_ID,
2516 s.ARRIVAL_SET_ID,
2517 s.SHIP_SET_NAME,
2518 s.ARRIVAL_SET_NAME,
2519 '''||MSC_CL_COLLECTION.v_current_date||''',
2520 '||MSC_CL_COLLECTION.v_current_user|| ',
2521 '''||MSC_CL_COLLECTION.v_current_date||''',
2522 '||MSC_CL_COLLECTION.v_current_user|| ',
2523 s.ATO_LINE_ID,
2524 s.ORDER_DATE_TYPE_CODE,
2525 s.SCHEDULE_ARRIVAL_DATE,
2526 s.LATEST_ACCEPTABLE_DATE,
2527 s.SHIP_TO_LOCATION_ID,
2528 s.SHIPPING_METHOD_CODE,
2529 s.INTRANSIT_LEAD_TIME,
2530 s.sr_customer_acct_id,
2531 s.DEMAND_SOURCE_LINE,
2532 MSC_DEMANDS_S.nextval ) ';
2533
2534
2535 -- row type1
2536 IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2537 lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2538 || lv_sql1_1|| lv_sql1_3
2539 || lv_sql2
2540 ||' WHEN NOT MATCHED THEN
2541 insert '
2542 || lv_sql3
2543 ||lv_sql4;
2544 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',1);
2545 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY',' decode (s.COMPLETED_QUANTITY,
2546 0, d.RESERVATION_QUANTITY,
2547 d.old_reservation_quantity) ');
2548 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2549 EXECUTE IMMEDIATE lv_sql5;
2550 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2551 COMMIT;
2552 ELSE -- for target Coll
2553 lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2554 || lv_sql3
2555 || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2556 ;
2557 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',1);
2558 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY',' decode (s.COMPLETED_QUANTITY,
2559 0, d.RESERVATION_QUANTITY,
2560 d.old_reservation_quantity) ');
2561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2562 EXECUTE IMMEDIATE lv_sql5;
2563 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2564 COMMIT;
2565 END IF;
2566 -- end row type 1
2567
2568 -- row type2
2569 IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2570 lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2571 || lv_sql1_1|| lv_sql1_3
2572 || ' AND decode(nvl(so.MFG_LEAD_TIME,0),
2573 0, so.REQUIREMENT_DATE,
2574 MSC_CALENDAR.DATE_OFFSET(so.ORGANIZATION_ID,so.SR_INSTANCE_ID,1,so.REQUIREMENT_DATE,-(so.MFG_LEAD_TIME))
2575 ) IS NOT NULL '
2576 || lv_sql2
2577 ||' WHEN NOT MATCHED THEN
2578 insert '
2579 || lv_sql3
2580 ||lv_sql4;
2581 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2582 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2583 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2584 EXECUTE IMMEDIATE lv_sql5;
2585 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2586 COMMIT;
2587 ELSE -- for target Coll
2588 lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2589 || lv_sql3
2590 || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2591 ||' AND decode(nvl(so.MFG_LEAD_TIME,0),
2592 0, so.REQUIREMENT_DATE,
2593 MSC_CALENDAR.DATE_OFFSET(so.ORGANIZATION_ID,so.SR_INSTANCE_ID,1,so.REQUIREMENT_DATE,-(so.MFG_LEAD_TIME))
2594 ) IS NOT NULL '
2595 ;
2596 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',2);
2597 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2598 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2599 EXECUTE IMMEDIATE lv_sql5;
2600 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2601 COMMIT;
2602 END IF;
2603 -- Update Reservation Qty
2604
2605 FOR modified_resv IN ( select distinct msso.demand_source_line
2606 from msc_st_sales_orders msso
2607 where msso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2608 AND ROW_TYPE=1
2609 and msso.demand_source_line IS NOT NULL
2610 and msso.reservation_type = 2
2611 and msso.deleted_flag=MSC_UTIL.SYS_NO )
2612 LOOP
2613 UPDATE MSC_SALES_ORDERS
2614 SET RESERVATION_QUANTITY = ( SELECT SUM(NVL(mso.primary_uom_quantity,0))
2615 FROM msc_sales_orders mso
2616 WHERE mso.sr_instance_id= MSC_CL_COLLECTION.v_instance_id
2617 AND mso.reservation_type = 2
2618 AND ROW_TYPE=1
2619 AND mso.demand_source_line = modified_resv.demand_source_line ),
2620 old_reservation_quantity = reservation_quantity
2621 WHERE sr_instance_id = MSC_CL_COLLECTION.v_instance_id
2622 AND RESERVATION_TYPE = 1
2623 AND AVAILABLE_TO_MRP = 'Y'
2624 AND CTO_FLAG = 2
2625 AND ROW_TYPE=2
2626 AND demand_source_line = modified_resv.demand_source_line ;
2627 COMMIT;
2628 END LOOP;
2629 --
2630 -- end row type 2
2631
2632 -- row type3
2633 --always target Coll for row type 3
2634 lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2635 || lv_sql3
2636 || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2637 ;
2638 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',3);
2639 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2640 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2641 EXECUTE IMMEDIATE lv_sql5;
2642 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2643 COMMIT;
2644 -- end row type 3
2645
2646 -- row type4
2647 IF MSC_CL_COLLECTION.v_is_so_incremental_refresh THEN -- for netchg Coll
2648 lv_sql5 := ' MERGE INTO '||lv_tbl||' d USING('
2649 || lv_sql1_1|| lv_sql1_3
2650 || lv_sql2
2651 ||' WHEN NOT MATCHED THEN
2652 insert '
2653 || lv_sql3
2654 ||lv_sql4;
2655 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2656 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2658 EXECUTE IMMEDIATE lv_sql5;
2659 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS MERGED :'||SQL%ROWCOUNT);
2660 COMMIT;
2661 ELSE -- for target Coll
2662 lv_sql5 := ' INSERT INTO '||lv_tbl||' d '
2663 || lv_sql3
2664 || lv_sql1_1 ||lv_sql1_2 || lv_sql1_3
2665 ;
2666 lv_sql5 := REPLACE (lv_sql5,'xxx_ROW_TYPE',4);
2667 lv_sql5 := REPLACE (lv_sql5,'xxx_RESERVATION_QUANTITY','d.RESERVATION_QUANTITY');
2668 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Stmt Executed :'||lv_sql5);
2669 EXECUTE IMMEDIATE lv_sql5;
2670 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'ROWS INSERTED :'||SQL%ROWCOUNT);
2671 COMMIT;
2672 END IF;
2673 -- end row type 4
2674
2675 -- UPDATE ireq/ISO
2676 link_ISO_IR(lv_tbl,lv_supply_tbl);
2677 --
2678
2679
2680
2681 EXCEPTION
2682
2683 WHEN OTHERS THEN
2684
2685 /*
2686
2687 IF SQLCODE <> -54 THEN /* NO_WAIT failed */
2688
2689 /* MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
2690
2691 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2692 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2693 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_SALES_ORDER');
2694 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_SALES_ORDERS');
2695 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2696
2697 /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2698 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
2699 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.item_name( c_rec.INVENTORY_ITEM_ID));
2700 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2701
2702 /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2703 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
2704 FND_MESSAGE.SET_TOKEN('VALUE',
2705 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
2706 MSC_CL_COLLECTION.v_instance_id));*/
2707
2708 /* FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2709 FND_MESSAGE.SET_TOKEN('COLUMN', 'DEMAND_ID');
2710 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.SR_DEMAND_ID));
2711 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);*/
2712
2713 /* IF c_rec.SALES_ORDER_NUMBER IS NOT NULL THEN
2714 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
2715 FND_MESSAGE.SET_TOKEN('COLUMN', 'SALES_ORDER_NUMBER');
2716 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.SALES_ORDER_NUMBER);
2717 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2718 END IF;*/
2719
2720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2721 RAISE;
2722
2723 /* END IF;*/
2724
2725 END;
2726 END LOAD_SALES_ORDER;
2727
2728 --==================================================================
2729
2730 PROCEDURE LOAD_HARD_RESERVATION IS
2731
2732 Cursor c1 IS
2733 SELECT
2734 t1.INVENTORY_ITEM_ID,
2735 mshr.ORGANIZATION_ID,
2736 mshr.TRANSACTION_ID,
2737 mshr.RESERVED_QUANTITY,
2738 mshr.DISPOSITION_ID,
2739 mshr.DISPOSITION_TYPE,
2740 mshr.RESERVATION_FLAG,
2741 mshr.RESERVATION_TYPE, -- SRP Changes For Bug 5988024
2742 mshr.PARENT_DEMAND_ID,
2743 mshr.REQUIREMENT_DATE,
2744 mshr.DEMAND_CLASS,
2745 mshr.PROJECT_ID,
2746 mshr.TASK_ID,
2747 mshr.SR_INSTANCE_ID,
2748 mshr.SUPPLY_SOURCE_HEADER_ID,
2749 mshr.SUPPLY_SOURCE_TYPE_ID,
2750 mshr.REPAIR_PO_HEADER_ID --SRP Changes For Bug 5996327
2751 FROM MSC_ITEM_ID_LID t1, /* bug fix 1084440 */
2752 MSC_ST_RESERVATIONS mshr
2753 WHERE mshr.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2754 AND mshr.DELETED_FLAG= MSC_UTIL.SYS_NO
2755 AND t1.SR_INVENTORY_ITEM_ID= mshr.INVENTORY_ITEM_ID
2756 AND t1.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id;
2757
2758 Cursor c1_d IS
2759 SELECT
2760 TRANSACTION_ID,
2761 PARENT_DEMAND_ID,
2762 SR_INSTANCE_ID,
2763 SUPPLY_SOURCE_TYPE_ID,
2764 ORGANIZATION_ID,
2765 INVENTORY_ITEM_ID,
2766 DISPOSITION_ID,
2767 DISPOSITION_TYPE
2768 FROM MSC_ST_RESERVATIONS mshr
2769 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
2770 AND DELETED_FLAG= MSC_UTIL.SYS_YES ; /* Changed For Bug 6144734 */
2771
2772 c_count NUMBER:= 0;
2773
2774 BEGIN
2775
2776 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
2777 -- We want to delete all HARD_RESERV related data and get new stuff.
2778
2779 --MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2780
2781 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
2782 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1);
2783 ELSE
2784 MSC_CL_COLLECTION.v_sub_str :=' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
2785 MSC_CL_COLLECTION.DELETE_MSC_TABLE( 'MSC_RESERVATIONS', MSC_CL_COLLECTION.v_instance_id, -1,MSC_CL_COLLECTION.v_sub_str);
2786 END IF;
2787
2788 END IF;
2789
2790 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2791
2792 /* FOR c_rec IN c1_d LOOP
2793
2794 UPDATE MSC_RESERVATIONS
2795 SET RESERVED_QUANTITY= 0,
2796 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2797 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2798 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2799 WHERE PLAN_ID= -1
2800 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2801 AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2802
2803 END LOOP; */
2804
2805 FOR c_rec IN c1_d LOOP
2806
2807
2808 If c_rec.supply_source_type_id = 86 then
2809
2810
2811 Delete from msc_reservations
2812 Where
2813 ((reservation_type = 5 and organization_id = c_rec.organization_id) or reservation_type = 7)
2814 And sr_instance_id =c_rec.SR_INSTANCE_ID
2815 And plan_id =-1
2816 And REPAIR_PO_HEADER_ID =c_rec.transaction_id ;
2817
2818 Elsif c_rec.supply_source_type_id = 1 then
2819
2820 Delete from msc_reservations
2821 Where sr_instance_id =c_rec.SR_INSTANCE_ID
2822 And plan_id =-1
2823 And ((disposition_id =c_rec.transaction_id and reservation_type = 5 and organization_id = c_rec.organization_id and disposition_type =1 )
2824 Or (transaction_id =c_rec.transaction_id and reservation_type = 7));
2825
2826 Elsif c_rec.supply_source_type_id = 7 then
2827
2828 Delete from msc_reservations
2829 Where sr_instance_id =c_rec.SR_INSTANCE_ID
2830 And plan_id =-1
2831 And transaction_id =c_rec.transaction_id
2832 And reservation_type = 4;
2833
2834 Elsif c_rec.supply_source_type_id = 200 then
2835
2836 Delete from msc_reservations
2837 Where sr_instance_id =c_rec.SR_INSTANCE_ID
2838 And plan_id =-1
2839 And ((disposition_id =c_rec.transaction_id and reservation_type = 4 and disposition_type =200 and organization_id = c_rec.organization_id)
2840 Or (transaction_id =c_rec.transaction_id and reservation_type = 3));
2841
2842
2843
2844 Elsif c_rec.supply_source_type_id = 2 then
2845
2846 Delete from msc_reservations
2847 Where sr_instance_id =c_rec.SR_INSTANCE_ID
2848 And plan_id =-1
2849 And disposition_id =c_rec.transaction_id
2850 And reservation_type in (7,3)
2851 And disposition_type =2
2852 And organization_id = c_rec.organization_id ;
2853
2854 Elsif c_rec.supply_source_type_id = 5 then
2855
2856 Delete from msc_reservations
2857 Where sr_instance_id =c_rec.SR_INSTANCE_ID
2858 And plan_id =-1
2859 And transaction_id/2 = c_rec.transaction_id
2860 And reservation_type = 5 ;
2861
2862 Else
2863
2864 -- IF c_rec.supply_source_type_id IS NULL AND c_rec.reservation_type is NULL THEN
2865
2866 UPDATE MSC_RESERVATIONS
2867 SET RESERVED_QUANTITY= 0,
2868 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2869 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2870 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
2871 WHERE PLAN_ID= -1
2872 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2873 AND TRANSACTION_ID= c_rec.TRANSACTION_ID;
2874
2875 -- END IF;
2876 END IF ;
2877 END LOOP;
2878
2879 END IF;
2880
2881 c_count:= 0;
2882
2883 FOR c_rec IN c1 LOOP
2884
2885 BEGIN
2886
2887 IF MSC_CL_COLLECTION.v_is_incremental_refresh THEN
2888
2889
2890
2891 UPDATE MSC_RESERVATIONS
2892 SET
2893 INVENTORY_ITEM_ID= c_rec.INVENTORY_ITEM_ID,
2894 ORGANIZATION_ID= c_rec.ORGANIZATION_ID,
2895 DEMAND_CLASS= c_rec.DEMAND_CLASS,
2896 RESERVED_QUANTITY= c_rec.RESERVED_QUANTITY,
2897 NONNET_QUANTITY_RESERVED= 0,
2898 DISPOSITION_ID= c_rec.DISPOSITION_ID,
2899 DISPOSITION_TYPE= c_rec.DISPOSITION_TYPE,
2900 RESERVATION_TYPE= c_rec.RESERVATION_TYPE,
2901 PARENT_DEMAND_ID= c_rec.PARENT_DEMAND_ID,
2902 RESERVATION_DATE= c_rec.REQUIREMENT_DATE,
2903 REQUIREMENT_DATE= c_rec.REQUIREMENT_DATE,
2904 PROJECT_ID= c_rec.PROJECT_ID,
2905 TASK_ID= c_rec.TASK_ID,
2906 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
2907 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
2908 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user,
2909 SUPPLY_SOURCE_HEADER_ID = c_rec.SUPPLY_SOURCE_HEADER_ID,
2910 SUPPLY_SOURCE_TYPE_ID = c_rec.SUPPLY_SOURCE_TYPE_ID,
2911 REPAIR_PO_HEADER_ID = c_rec.REPAIR_PO_HEADER_ID -- Chenges FOr Bug 5996327
2912 WHERE PLAN_ID= -1
2913 AND SR_INSTANCE_ID= c_rec.SR_INSTANCE_ID
2914 AND TRANSACTION_ID= c_rec.TRANSACTION_ID
2915 AND RESERVATION_TYPE = NVL(c_rec.RESERVATION_TYPE,RESERVATION_TYPE); -- Changes FOr Bug 5988024
2916 -- Changed to NVL For Bug 6144734 as Non SRP Reservations would have Reservation_type NULL
2917 END IF;
2918
2919 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) OR SQL%NOTFOUND THEN
2920
2921
2922
2923 insert into MSC_RESERVATIONS
2924 ( TRANSACTION_ID,
2925 INVENTORY_ITEM_ID,
2926 ORGANIZATION_ID,
2927 PLAN_ID,
2928 DEMAND_CLASS,
2929 RESERVED_QUANTITY,
2930 NONNET_QUANTITY_RESERVED,
2931 DISPOSITION_ID,
2932 DISPOSITION_TYPE,
2933 RESERVATION_TYPE, -- Changes FOr Bug 5988024
2934 PARENT_DEMAND_ID,
2935 RESERVATION_DATE,
2936 REQUIREMENT_DATE,
2937 PROJECT_ID,
2938 TASK_ID,
2939 SR_INSTANCE_ID,
2940 REFRESH_NUMBER,
2941 LAST_UPDATE_DATE,
2942 LAST_UPDATED_BY,
2943 CREATION_DATE,
2944 CREATED_BY,
2945 SUPPLY_SOURCE_HEADER_ID,
2946 SUPPLY_SOURCE_TYPE_ID,
2947 REPAIR_PO_HEADER_ID )
2948 VALUES
2949 ( c_rec.TRANSACTION_ID,
2950 c_rec.INVENTORY_ITEM_ID,
2951 c_rec.ORGANIZATION_ID,
2952 -1,
2953 c_rec.DEMAND_CLASS,
2954 c_rec.RESERVED_QUANTITY,
2955 0,
2956 c_rec.DISPOSITION_ID,
2957 c_rec.DISPOSITION_TYPE,
2958 c_rec.RESERVATION_TYPE, -- Changes FOr Bug 5988024
2959 c_rec.PARENT_DEMAND_ID,
2960 c_rec.REQUIREMENT_DATE,
2961 c_rec.REQUIREMENT_DATE,
2962 c_rec.PROJECT_ID,
2963 c_rec.TASK_ID,
2964 c_rec.SR_INSTANCE_ID,
2965 MSC_CL_COLLECTION.v_last_collection_id,
2966 MSC_CL_COLLECTION.v_current_date,
2967 MSC_CL_COLLECTION.v_current_user,
2968 MSC_CL_COLLECTION.v_current_date,
2969 MSC_CL_COLLECTION.v_current_user,
2970 c_rec.SUPPLY_SOURCE_HEADER_ID,
2971 c_rec.SUPPLY_SOURCE_TYPE_ID ,
2972 c_rec.REPAIR_PO_HEADER_ID); -- Chengs For Bug 5996327
2973
2974 END IF;
2975
2976 c_count:= c_count+1;
2977
2978 IF c_count> MSC_CL_COLLECTION.PBS THEN
2979 COMMIT;
2980 c_count:= 0;
2981 END IF;
2982
2983 EXCEPTION
2984 WHEN OTHERS THEN
2985
2986 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
2987
2988 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2989 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
2990 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
2991 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
2992 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
2993
2994 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2995 RAISE;
2996
2997 ELSE
2998
2999 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3000
3001 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3002 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3003 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_HARD_RESERVATION');
3004 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_RESERVATIONS');
3005 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3006
3007 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3008 FND_MESSAGE.SET_TOKEN('COLUMN', 'MSC_CL_ITEM_ODS_LOAD.ITEM_NAME');
3009 FND_MESSAGE.SET_TOKEN('VALUE', MSC_CL_ITEM_ODS_LOAD.ITEM_NAME( c_rec.INVENTORY_ITEM_ID));
3010 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3011
3012 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3013 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3014 FND_MESSAGE.SET_TOKEN('VALUE',
3015 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3016 MSC_CL_COLLECTION.v_instance_id));
3017 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3018
3019 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3020 FND_MESSAGE.SET_TOKEN('COLUMN', 'TRANSACTION_ID');
3021 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.TRANSACTION_ID));
3022 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3023
3024 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3025 FND_MESSAGE.SET_TOKEN('COLUMN', 'DISPOSITION_ID');
3026 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(c_rec.DISPOSITION_ID));
3027 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3028
3029 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3030 END IF;
3031
3032 END;
3033
3034 END LOOP;
3035
3036 COMMIT;
3037
3038 END LOAD_HARD_RESERVATION;
3039
3040 --==================================================================
3041
3042 PROCEDURE LOAD_DESIGNATOR IS
3043
3044 CURSOR c1 IS
3045 SELECT
3046 msd.DESIGNATOR,
3047 msd.ORGANIZATION_ID,
3048 msd.MPS_RELIEF,
3049 msd.INVENTORY_ATP_FLAG,
3050 msd.DESCRIPTION,
3051 msd.DISABLE_DATE,
3052 msd.DEMAND_CLASS,
3053 msd.ORGANIZATION_SELECTION,
3054 msd.PRODUCTION,
3055 msd.RECOMMENDATION_RELEASE,
3056 msd.DESIGNATOR_TYPE,
3057 msd.SR_INSTANCE_ID
3058 FROM MSC_ST_DESIGNATORS msd
3059 WHERE msd.SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3060 AND designator_type <> 6;
3061
3062 c_count NUMBER:= 0;
3063 lv_sql_stmt VARCHAR2(5000);
3064
3065 BEGIN
3066
3067
3068 IF (MSC_CL_COLLECTION.v_is_complete_refresh OR MSC_CL_COLLECTION.v_is_partial_refresh) THEN
3069
3070 /*UPDATE MSC_DESIGNATORS
3071 SET DISABLE_DATE= MSC_CL_COLLECTION.v_current_date,
3072 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3073 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3074 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3075 WHERE SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3076 AND designator_type <> 6 -- Added This condition for Bug# 2022521
3077 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES; */
3078
3079
3080 lv_sql_stmt:= 'UPDATE MSC_DESIGNATORS '
3081 ||' SET DISABLE_DATE = :v_current_date, '
3082 ||' REFRESH_NUMBER = :v_last_collection_id, '
3083 ||' LAST_UPDATE_DATE= :v_current_date, '
3084 ||' LAST_UPDATED_BY = :v_current_user '
3085 ||' WHERE SR_INSTANCE_ID = :v_instance_id '
3086 ||' AND( (designator_type = (select decode(mds,1,1,-1) '
3087 ||' from msc_coll_parameters '
3088 ||' where instance_id = :v_instance_id)) '
3089 ||' OR '
3090 ||'(designator_type = (select decode(mps,1,2,-1) '
3091 ||' from msc_coll_parameters '
3092 ||' where instance_id = :v_instance_id)) '
3093 ||' ) '
3094 ||' AND COLLECTED_FLAG = '||MSC_UTIL.SYS_YES;
3095
3096 if MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS then
3097
3098 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3099 MSC_CL_COLLECTION.v_last_collection_id,
3100 MSC_CL_COLLECTION.v_current_date,
3101 MSC_CL_COLLECTION.v_current_user,
3102 MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3103 else
3104 lv_sql_stmt :=lv_sql_stmt||' AND ORGANIZATION_ID '||MSC_UTIL.v_in_org_str;
3105
3106 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_COLLECTION.v_current_date,
3107 MSC_CL_COLLECTION.v_last_collection_id,
3108 MSC_CL_COLLECTION.v_current_date,
3109 MSC_CL_COLLECTION.v_current_user,
3110 MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id,MSC_CL_COLLECTION.v_instance_id;
3111
3112 end if;
3113
3114 END IF;
3115
3116 c_count:= 0;
3117
3118 FOR c_rec IN c1 LOOP
3119
3120 BEGIN
3121
3122 UPDATE MSC_DESIGNATORS
3123 SET
3124 SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id,
3125 MPS_RELIEF= c_rec.MPS_RELIEF,
3126 INVENTORY_ATP_FLAG= c_rec.INVENTORY_ATP_FLAG,
3127 DESCRIPTION= c_rec.DESCRIPTION,
3128 DISABLE_DATE= c_rec.DISABLE_DATE,
3129 DEMAND_CLASS= c_rec.DEMAND_CLASS,
3130 ORGANIZATION_SELECTION= c_rec.ORGANIZATION_SELECTION,
3131 PRODUCTION= c_rec.PRODUCTION,
3132 RECOMMENDATION_RELEASE= c_rec.RECOMMENDATION_RELEASE,
3133 DESIGNATOR_TYPE= c_rec.DESIGNATOR_TYPE,
3134 REFRESH_NUMBER= MSC_CL_COLLECTION.v_last_collection_id,
3135 LAST_UPDATE_DATE= MSC_CL_COLLECTION.v_current_date,
3136 LAST_UPDATED_BY= MSC_CL_COLLECTION.v_current_user
3137 WHERE DESIGNATOR= c_rec.DESIGNATOR
3138 AND ORGANIZATION_ID= c_rec.ORGANIZATION_ID
3139 AND SR_INSTANCE_ID= MSC_CL_COLLECTION.v_instance_id
3140 AND COLLECTED_FLAG= MSC_UTIL.SYS_YES;
3141
3142 IF SQL%NOTFOUND THEN
3143
3144 INSERT INTO MSC_DESIGNATORS
3145 ( DESIGNATOR_ID,
3146 DESIGNATOR,
3147 ORGANIZATION_ID,
3148 MPS_RELIEF,
3149 INVENTORY_ATP_FLAG,
3150 DESCRIPTION,
3151 DISABLE_DATE,
3152 DEMAND_CLASS,
3153 ORGANIZATION_SELECTION,
3154 PRODUCTION,
3155 RECOMMENDATION_RELEASE,
3156 DESIGNATOR_TYPE,
3157 COLLECTED_FLAG,
3158 SR_INSTANCE_ID,
3159 REFRESH_NUMBER,
3160 LAST_UPDATE_DATE,
3161 LAST_UPDATED_BY,
3162 CREATION_DATE,
3163 CREATED_BY)
3164 VALUES
3165 ( MSC_DESIGNATORS_S.NEXTVAL,
3166 c_rec.DESIGNATOR,
3167 c_rec.ORGANIZATION_ID,
3168 c_rec.MPS_RELIEF,
3169 c_rec.INVENTORY_ATP_FLAG,
3170 c_rec.DESCRIPTION,
3171 c_rec.DISABLE_DATE,
3172 c_rec.DEMAND_CLASS,
3173 c_rec.ORGANIZATION_SELECTION,
3174 c_rec.PRODUCTION,
3175 c_rec.RECOMMENDATION_RELEASE,
3176 c_rec.DESIGNATOR_TYPE,
3177 MSC_UTIL.SYS_YES,
3178 c_rec.SR_INSTANCE_ID,
3179 MSC_CL_COLLECTION.v_last_collection_id,
3180 MSC_CL_COLLECTION.v_current_date,
3181 MSC_CL_COLLECTION.v_current_user,
3182 MSC_CL_COLLECTION.v_current_date,
3183 MSC_CL_COLLECTION.v_current_user );
3184
3185 END IF;
3186
3187 c_count:= c_count+1;
3188
3189 IF c_count> MSC_CL_COLLECTION.PBS THEN
3190 COMMIT;
3191 c_count:= 0;
3192 END IF;
3193
3194 EXCEPTION
3195 WHEN OTHERS THEN
3196
3197 IF SQLCODE IN (-01683,-01653,-01650,-01562) THEN
3198
3199 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3200 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3201 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3202 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3203 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3204
3205 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3206 RAISE;
3207
3208 ELSE
3209 MSC_CL_COLLECTION.v_warning_flag := MSC_UTIL.SYS_YES;
3210
3211 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
3212 FND_MESSAGE.SET_NAME('MSC', 'MSC_OL_DATA_ERR_HEADER');
3213 FND_MESSAGE.SET_TOKEN('PROCEDURE', 'LOAD_DESIGNATOR');
3214 FND_MESSAGE.SET_TOKEN('TABLE', 'MSC_DESIGNATORS');
3215 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3216
3217 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3218 FND_MESSAGE.SET_TOKEN('COLUMN', 'ORGANIZATION_CODE');
3219 FND_MESSAGE.SET_TOKEN('VALUE',
3220 MSC_GET_NAME.ORG_CODE( c_rec.ORGANIZATION_ID,
3221 MSC_CL_COLLECTION.v_instance_id));
3222 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3223
3224 FND_MESSAGE.SET_NAME('MSC','MSC_OL_DATA_ERR_DETAIL');
3225 FND_MESSAGE.SET_TOKEN('COLUMN', 'DESIGNATOR');
3226 FND_MESSAGE.SET_TOKEN('VALUE', c_rec.DESIGNATOR);
3227 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, FND_MESSAGE.GET);
3228
3229 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3230 END IF;
3231
3232 END;
3233
3234 END LOOP;
3235
3236 COMMIT;
3237
3238 END LOAD_DESIGNATOR;
3239
3240 PROCEDURE LOAD_ODS_DEMAND IS
3241
3242 lv_temp_demand_tbl VARCHAR2(30);
3243 lv_sql_stmt VARCHAR2(5000);
3244 lv_sql_stmt1 VARCHAR2(5000);
3245 lv_sql_stmt2 VARCHAR2(5000);
3246 lv_where_clause VARCHAR2(2000);
3247
3248 BEGIN
3249
3250 lv_temp_demand_tbl := 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3251 lv_sql_stmt:=
3252 'INSERT INTO '||lv_temp_demand_tbl
3253 ||' SELECT * from MSC_DEMANDS '
3254 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3255 ||' AND plan_id = -1 '
3256 ||' AND origination_type NOT IN (';
3257
3258 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'PREC Flag is ' || TO_CHAR(MSC_CL_COLLECTION.v_coll_prec.mds_flag));
3259
3260 -- MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(MSC_CL_COLLECTION.v_instance_id); -- For Bug 5909379
3261 -- MSC_UTIL.v_depot_org_str := MSC_CL_PULL.g_depot_org_str;
3262 -- MSC_UTIL.v_non_depot_org_str := MSC_CL_PULL.g_non_depot_org_str;
3263
3264
3265 IF MSC_CL_COLLECTION.v_coll_prec.mds_flag = MSC_UTIL.SYS_YES THEN
3266 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3267 if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3268 lv_where_clause := '6,7,8,15,24';
3269 end if;
3270 else
3271 lv_where_clause := '6,7,8,15,24';
3272 end if;
3273 END IF;
3274
3275
3276 IF (MSC_CL_COLLECTION.v_coll_prec.payback_demand_supply_flag = MSC_UTIL.SYS_YES) THEN
3277 IF (lv_where_clause IS NULL) THEN
3278 lv_where_clause := '27';
3279 ELSE
3280 lv_where_clause := lv_where_clause||', 27';
3281 END IF;
3282 END IF;
3283
3284 IF (MSC_CL_COLLECTION.v_coll_prec.wip_flag = MSC_UTIL.SYS_YES) THEN
3285 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3286 if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3287 IF (lv_where_clause IS NULL) THEN
3288 lv_where_clause := '2,3,4,25,50';
3289 ELSE
3290 lv_where_clause := lv_where_clause||', 2,3,4,25,50';
3291 END IF;
3292 end if;
3293 else
3294 IF (lv_where_clause IS NULL) THEN
3295 lv_where_clause := '2,3,4,25,50';
3296 ELSE
3297 lv_where_clause := lv_where_clause||', 2,3,4,25,50';
3298 END IF;
3299 end if;
3300 END IF;
3301
3302 IF (MSC_CL_COLLECTION.v_coll_prec.forecast_flag = MSC_UTIL.SYS_YES) THEN
3303 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3304 if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3305 IF (lv_where_clause IS NULL) THEN
3306 lv_where_clause := '29';
3307 ELSE
3308 lv_where_clause := lv_where_clause||', 29';
3309 END IF;
3310 end if;
3311 else
3312 IF (lv_where_clause IS NULL) THEN
3313 lv_where_clause := '29';
3314 ELSE
3315 lv_where_clause := lv_where_clause||', 29';
3316 END IF;
3317 end if;
3318 END IF;
3319
3320 IF (MSC_CL_COLLECTION.v_coll_prec.user_supply_demand_flag = MSC_UTIL.SYS_YES) THEN
3321 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3322 if (MSC_CL_COLLECTION.v_coll_prec.udmd_sn_flag = MSC_UTIL.SYS_TGT) then
3323 IF (lv_where_clause IS NULL) THEN
3324 lv_where_clause := '42';
3325 ELSE
3326 lv_where_clause := lv_where_clause||', 42';
3327 END IF;
3328 end if;
3329 else
3330 IF (lv_where_clause IS NULL) THEN
3331 lv_where_clause := '42';
3332 ELSE
3333 lv_where_clause := lv_where_clause||', 42';
3334 END IF;
3335 end if;
3336 END IF;
3337
3338
3339
3340
3341 IF (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) THEN
3342
3343 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3344 NULL;
3345 Else
3346
3347 IF (lv_where_clause IS NULL) THEN
3348 lv_where_clause := '77';
3349 ELSE
3350 lv_where_clause := lv_where_clause||', 77';
3351 END IF;
3352
3353
3354 end if;
3355 END IF; -- Additions for 5909379 for SRP
3356
3357 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) THEN
3358
3359 if (MSC_CL_COLLECTION.v_is_cont_refresh) then
3360 NULL;
3361 Else
3362
3363 IF (lv_where_clause IS NULL) THEN
3364 lv_where_clause := '77';
3365 ELSE
3366 lv_where_clause := lv_where_clause||', 77';
3367 END IF;
3368
3369
3370 end if;
3371 END IF; -- Additions for 5909379 for SRP
3372
3373 lv_sql_stmt := lv_sql_stmt||lv_where_clause ||' )';
3374
3375 IF MSC_CL_COLLECTION.v_coll_prec.org_group_flag = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
3376 null;
3377 ELSE
3378
3379 lv_sql_stmt1:= ' UNION ALL '
3380 ||' SELECT * from MSC_DEMANDS '
3381 ||' WHERE sr_instance_id = '||MSC_CL_COLLECTION.v_instance_id
3382 ||' AND plan_id = -1 '
3383 ||' AND organization_id NOT '||MSC_UTIL.v_in_org_str
3384 ||' AND origination_type IN (';
3385
3386 lv_sql_stmt1 := lv_sql_stmt1||lv_where_clause ||' )';
3387
3388
3389
3390 if NOT (MSC_CL_COLLECTION.v_is_complete_refresh) then
3391 lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
3392 else
3393 lv_sql_stmt := lv_sql_stmt||' AND organization_id NOT '||MSC_UTIL.v_in_org_str;
3394
3395 lv_sql_stmt :=lv_sql_stmt||lv_sql_stmt1;
3396 end if;
3397
3398 END IF;
3399
3400
3401 EXECUTE IMMEDIATE lv_sql_stmt;
3402
3403 COMMIT;
3404
3405 -- Added For SRP Bug 5935273
3406 ---------------------------------------
3407 IF NOT MSC_CL_COLLECTION.v_is_complete_refresh THEN -- This part of the code should be called only fro Targeted Colelction of repair orders
3408
3409
3410
3411 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3412
3413 lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3414 ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_depot_org_str;
3415
3416 EXECUTE IMMEDIATE lv_sql_stmt2;
3417
3418 Commit ;
3419 END if ;
3420
3421 IF (MSC_CL_COLLECTION.v_coll_prec.external_repair_flag = MSC_UTIL.SYS_NO) AND (MSC_CL_COLLECTION.v_coll_prec.internal_repair_flag = MSC_UTIL.SYS_YES) AND (MSC_UTIL.G_COLLECT_SRP_DATA = 'Y') THEN
3422
3423 lv_sql_stmt2 := 'INSERT INTO '||lv_temp_demand_tbl
3424 ||' SELECT * from MSC_DEMANDS Where origination_type =77 and organization_id '||MSC_UTIL.v_non_depot_org_str;
3425
3426 EXECUTE IMMEDIATE lv_sql_stmt2;
3427
3428 Commit ;
3429 END if ;
3430 END IF;
3431
3432 ------------------------------------------
3433
3434 EXCEPTION
3435 WHEN OTHERS THEN
3436 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
3437 RAISE;
3438
3439 END LOAD_ODS_DEMAND;
3440
3441 PROCEDURE LOAD_PAYBACK_DEMANDS IS
3442 lv_tbl VARCHAR2(30);
3443 lv_sql_ins VARCHAR2(32767);
3444 LV_SUPPLY_TBL VARCHAR2(1000);
3445 BEGIN
3446
3447 IF MSC_CL_COLLECTION.v_exchange_mode=MSC_UTIL.SYS_YES THEN
3448 lv_tbl:= 'DEMANDS_'||MSC_CL_COLLECTION.v_instance_code;
3449 ELSE
3450 lv_tbl:= 'MSC_DEMANDS';
3451 END IF;
3452
3453
3454 lv_sql_ins :=
3455 ' INSERT INTO '||lv_tbl
3456 ||'( PLAN_ID,
3457 DEMAND_ID,
3458 USING_REQUIREMENT_QUANTITY,
3459 USING_ASSEMBLY_DEMAND_DATE,
3460 DEMAND_TYPE,
3461 ORIGINATION_TYPE,
3462 USING_ASSEMBLY_ITEM_ID,
3463 ORGANIZATION_ID,
3464 INVENTORY_ITEM_ID,
3465 SR_INSTANCE_ID,
3466 PROJECT_ID,
3467 TASK_ID,
3468 PLANNING_GROUP,
3469 LAST_UPDATE_DATE,
3470 LAST_UPDATED_BY,
3471 CREATION_DATE,
3472 CREATED_BY)
3473 SELECT
3474 -1 PLAN_ID,
3475 MSC_DEMANDS_S.nextval,
3476 MOP. QUANTITY,
3477 SCHEDULED_PAYBACK_DATE,
3478 1 DEMAND_TYPE,
3479 27 ORIGINATION_TYPE,
3480 MIIL.INVENTORY_ITEM_ID, -- USING_ASSEMBLY_ITEM_ID
3481 MOP.ORGANIZATION_ID,
3482 MIIL.INVENTORY_ITEM_ID,
3483 MOP.SR_INSTANCE_ID,
3484 MOP.BORROW_PROJECT_ID,
3485 MOP.BORROW_TASK_ID,
3486 MOP.PLANNING_GROUP,
3487 :v_current_date,
3488 :v_current_user,
3489 :v_current_date,
3490 :v_current_user
3491 FROM MSC_ST_OPEN_PAYBACKS MOP, MSC_ITEM_ID_LID MIIL
3492 WHERE MIIL.SR_INVENTORY_ITEM_ID = MOP.inventory_item_id
3493 AND MIIL.sr_instance_id = MOP.sr_instance_id
3494 AND MOP.sr_instance_id = :v_instance_id';
3495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,lv_sql_ins);
3496 EXECUTE IMMEDIATE lv_sql_ins
3497 USING MSC_CL_COLLECTION.v_current_date,
3498 MSC_CL_COLLECTION.v_current_user,
3499 MSC_CL_COLLECTION.v_current_date,
3500 MSC_CL_COLLECTION.v_current_user,
3501 MSC_CL_COLLECTION.v_instance_id;
3502 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'rows inserted :- '||SQL%ROWCOUNT);
3503
3504 COMMIT;
3505
3506 END LOAD_PAYBACK_DEMANDS;
3507
3508 END MSC_CL_DEMAND_ODS_LOAD;