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