[Home] [Help]
PACKAGE BODY: APPS.MSC_BAL_UTILS
Source
1 PACKAGE BODY MSC_BAL_UTILS AS
2 /* $Header: MSCUBALB.pls 120.8 2007/12/14 07:31:47 sbnaik ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
4
5 PROCEDURE extend( p_nodes IN OUT NoCopy mrp_bal_utils.mrp_oe_rec , extend_amount NUMBER );
6 PROCEDURE trim( p_nodes IN OUT NoCopy mrp_bal_utils.mrp_oe_rec , trim_amount NUMBER );
7
8 PROCEDURE populate_temp_table (p_session_id NUMBER,
9 p_order_by VARCHAR2,
10 p_where VARCHAR2,
11 p_overwrite NUMBER,
12 p_org_id NUMBER,
13 p_exclude_picked NUMBER default 0) IS
14 l_stmt VARCHAR2(4000);
15 l_mrp_oe_rec mrp_bal_utils.mrp_oe_rec;
16 filter_mrp_oe_rec mrp_bal_utils.mrp_oe_rec;
17 j NUMBER;
18 TYPE curtype IS REF CURSOR;
19 cv CurType;
20 l_seq NUMBER;
21 num_of_lines NUMBER;
22 l_ship_set_id NUMBER;
23 l_arrival_set_id NUMBER;
24 a NUMBER;
25 -- From patchset G+, new columns in OE/MRP introduced compile-time dependency
26 -- between ATP and OM. This fix (using DBMS_SQL) removes this dependency. We check
27 -- ALL_TAB_COLUMNS for these columns and then frame the SQL. Bug 2727595.
28
29 bind1 DBMS_SQL.number_table;
30 bind2 DBMS_SQL.number_table;
31
32 cursor check_oe (p_column_name varchar2) is
33 select 1
34 from user_tab_columns
35 where table_name ='OE_MRP_OPEN_DEMANDS_V'
36 and column_name = p_column_name;
37
38 l_column_exist number;
39 l_sql_stmt varchar2(30000);
40
41 l_sql_stmt1 varchar2(30000) :=
42 'begin '||
43 -- 'FORALL j IN 1 .. :l_num_of_lines '||
44 'insert into mrp_atp_schedule_temp '||
45 '(action, '||
46 'calling_module, '||
47 'sequence_number, '||
48 'session_id, '||
49 'scenario_id, '||
50 'firm_flag, '||
51 'status_flag, '||
52 'insert_flag, '||
53 'order_header_id, '||
54 'order_number, '||
55 'order_line_id, '||
56 'order_line_number, '||
57 'inventory_item_id, '||
58 'inventory_item_name, '||
59 'sr_instance_id, '||
60 'source_organization_id, '||
61 'source_organization_code, '||
62 'old_source_organization_code, '||
63 'quantity_ordered, '||
64 'uom_code, '||
65 'scheduled_ship_date, '||
66 'scheduled_arrival_date, '||
67 'old_line_schedule_date, '||
68 'requested_ship_date, '||
69 'requested_arrival_date, '||
70 'promise_date, '||
71 'latest_acceptable_date, '||
72 'delivery_lead_time, '||
73 'ship_method, '||
74 'demand_class, '||
75 'ship_set_id, '||
76 'ship_set_name, '||
77 'arrival_set_id, '||
78 'arrival_set_name, '||
79 'customer_id, '||
80 'customer_site_id, '||
81 'customer_name, '||
82 'customer_location, '||
83 'shipment_number, '||
84 'option_number, '||
85 'old_source_organization_id, '||
86 'old_demand_class, '||
87 'creation_date, '||
88 'created_by, '||
89 'last_update_date, '||
90 'last_updated_by, '||
91 'last_update_login, '||
92 'freight_carrier, '||
93 'flow_status_code '||
94 ') ( '||
95 'select '||
96 '110, '||
97 '-1, '||
98 ':l_seq_num, '||
99 ':p_session_id, '||
100 '1, ' || -- scenario_id
101 '2, '|| -- firm_flag 2 -> NO
102 '1, '|| -- status_flag 1 -> INPUT
103 ':l_profile_value, '|| -- insert_flag 1 -> INPUT
104 'omodv.header_id, '||
105 'omodv.order_number, '||
106 'omodv.line_id, '||
107 'omodv.line_number, '||
108 'omodv.inventory_item_id, '||
109 'omodv.ordered_item, '||
110 'maai.instance_id, '||
111 'decode(:p_overwrite,1,NULL,omodv.ship_from_org_id), '||
112 'decode(:p_overwrite,1,NULL,ood.organization_code), '||
113 'ood.organization_code, '||
114 'omodv.ordered_quantity, '||
115 'omodv.order_quantity_uom, '||
116 'omodv.schedule_ship_date, '||
117 'omodv.schedule_arrival_date, '||
118 'decode(omodv.schedule_ship_date,NULL, '||
119 'omodv.schedule_arrival_date,omodv.schedule_ship_date), '||
120 'omodv.request_ship_date, '||
121 'omodv.request_arrival_date, '||
122 'omodv.promise_date, '||
123 'omodv.latest_acceptable_date, '||
124 'omodv.delivery_lead_time, '||
125 'omodv.shipping_method_code, '||
126 'omodv.demand_class_code, '||
127 'omodv.ship_set_id, '||
128 'omodv.ship_set_name, '||
129 'omodv.arrival_set_id, '||
130 'omodv.arrival_set_name, '||
131 'omodv.sold_to_org_id, '||
132 'omodv.ship_to_org_id, '||
133 'hp.party_name, '||
134 'ras.location, '||
135 'omodv.shipment_number, '||
136 'omodv.option_number, '||
137 'omodv.ship_from_org_id, '||
138 'omodv.demand_class_code, '||
139 'sysdate, '||
140 'FND_GLOBAL.USER_ID, '||
141 'sysdate, '||
142 'FND_GLOBAL.USER_ID, '||
143 'FND_GLOBAL.USER_ID ';
144
145 l_new_columns varchar2(100) := ' ,omodv.freight_carrier_code, omodv.flow_status_code ';
146 l_old_columns varchar2(100) := ' ,null, null ';
147
148 l_sql_stmt2 varchar2(30000) :=
149 'FROM '||
150 'mrp_ap_apps_instances maai, '||
151 'hz_cust_site_uses_all ras, '||
152 'hz_parties hp, '||
153 'hz_cust_accounts hca, '||
154 'org_organization_definitions ood, '||
155 'oe_mrp_open_demands_v omodv '||
156 'WHERE hp.party_id = hca.party_id '||
157 'AND hca.cust_account_id = omodv.sold_to_org_id '||
158 'AND ras.site_use_id = omodv.ship_to_org_id '||
159 'AND ras.site_use_code = '||' ''SHIP_TO'' '||
160 'AND ood.organization_id(+) = omodv.ship_from_org_id '||
161 'AND omodv.line_id = :l_line_id ); '||
162 'end; ';
163
164 cur_hdl INTEGER;
165 rows_processed BINARY_INTEGER;
166 l_time VARCHAR2(80);
167
168 and_sets_lines VARCHAR2(400) := ' ( omodv.ship_set_id = omodv1.ship_set_id OR
169 omodv.arrival_set_id = omodv1.arrival_set_id) ';
170
171 and_null_sets_lines VARCHAR2(400) := '( (omodv.ship_set_id is NULL or omodv.arrival_set_id is NULL)
172 and omodv.line_id = omodv1.line_id) ';
173
174 filter_sset_id NUMBER;
175 l_order_number NUMBER;
176 l_top_model_id NUMBER;
177 l_profile_value NUMBER := 0;
178 dummy_schedule_ship_date DATE;
179 dummy_schedule_arrival_date DATE;
180 dummy_request_ship_date DATE;
181 dummy_request_arrival_date DATE;
182 dummy_promise_date DATE;
183 dummy_order_number NUMBER;
184
185 BEGIN
186 -- MOAC changes
187 mo_global.init('ONT');
188
189 IF PG_DEBUG in ('Y', 'C') THEN
190 msc_sch_wb.atp_debug('MSC_BAL_UTILS.populate_temp_table ' );
191 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
192 into l_time
193 from dual;
194 msc_sch_wb.atp_debug( 'POPULATE_TEMP_TABLE: BEGIN ' || l_time);
195 END IF;
196
197 open check_oe('PLANNING_PRIORITY');
198 fetch check_oe into l_column_exist;
199 close check_oe;
200
201 delete from mrp_atp_schedule_temp
202 where session_id = -444;
203
204
205 if l_column_exist = 1 then
206 IF PG_DEBUG in ('Y', 'C') THEN
207 msc_sch_wb.atp_debug('MSC_BAL_UTILS.populate_temp_table ' ||
208 'new column Planning_priority exsits' );
209
210 END IF;
211
212
213 l_stmt := ' INSERT into mrp_atp_schedule_temp
214 (order_line_id,
215 order_header_id,
216 scenario_id,
217 session_id,
218 inventory_item_id,
219 ship_set_id,
220 arrival_set_id,
221 sequence_number,
222 ato_model_line_id,
223 top_model_line_id,
224 inventory_item_name, ---item_type_code
225 order_number,
226 scheduled_ship_date,
227 scheduled_arrival_date,
228 requested_ship_date,
229 requested_arrival_date,
230 promise_date,
231 sr_instance_id)
232 SELECT omodv.line_id,
233 omodv.header_id,
234 0,
235 -444,
236 0,
237 omodv.ship_set_id,
238 omodv.arrival_set_id,
239 0,
240 omodv.ato_line_id,
241 omodv.top_model_line_id,
242 omodv.item_type_code,
243 omodv.order_number,
244 omodv.schedule_ship_date ,
245 omodv.schedule_arrival_date,
246 omodv.request_ship_date,
247 omodv.request_arrival_date,
248 omodv.promise_date,
249 omodv.planning_priority
250 FROM
251 oe_mrp_open_demands_v omodv
252 WHERE 1=1 ';
253 else
254 IF PG_DEBUG in ('Y', 'C') THEN
255 msc_sch_wb.atp_debug('MSC_BAL_UTILS.populate_temp_table: new column Planning_priority DOES NOT exsits' );
256 msc_sch_wb.atp_debug(' You are trying to sequece lines on planning_priority of a line , however dependency patches are not applied' );
257 msc_sch_wb.atp_debug(' OM dependency patch #3898070 is not applied correctly ');
258 END IF;
259 l_stmt := ' INSERT into mrp_atp_schedule_temp
260 (order_line_id,
261 order_header_id,
262 scenario_id, -- not null col
263 session_id, -- not null col
264 inventory_item_id, -- not null col
265 ship_set_id,
266 arrival_set_id,
267 sequence_number,
268 ato_model_line_id,
269 top_model_line_id,
270 inventory_item_name, -- -item_type_code
271 order_number,
272 scheduled_ship_date,
273 scheduled_arrival_date,
274 requested_ship_date,
275 requested_arrival_date,
276 promise_date)
277 SELECT
278 omodv.line_id,
279 omodv.header_id,
280 0,
281 -444,
282 0,
283 omodv.ship_set_id,
284 omodv.arrival_set_id,
285 0,
286 omodv.ato_line_id,
287 omodv.top_model_line_id,
288 omodv.item_type_code,
289 omodv.order_number,
290 omodv.schedule_ship_date ,
291 omodv.schedule_arrival_date,
292 omodv.request_ship_date,
293 omodv.request_arrival_date,
294 omodv.promise_date
295 FROM
296 oe_mrp_open_demands_v omodv
297 WHERE 1=1 ';
298 end if;
299 l_stmt := l_stmt || p_where;
300
301
302 IF PG_DEBUG in ('Y', 'C') THEN
303 msc_sch_wb.atp_debug('p_exclude_picked is ' || p_exclude_picked);
304 END IF;
305
306 IF p_exclude_picked = 1 THEN
307
308 l_stmt := l_stmt ||
309 ' and not exists ( select 1
310 from wsh_delivery_details
311 where source_line_id =omodv.line_id
312 and source_code = '||' ''OE'' '||
313 ' and released_status in ( '||' ''Y'' '|| ', '
314 ||' ''S'' '|| ', '
315 ||' ''C'' '|| ' ) ) ';
316
317 END IF;
318
319
320 IF PG_DEBUG in ('Y', 'C') THEN
321 msc_sch_wb.atp_debug(' POPULATE_TEMP_TABLE - constructed sql is '||l_stmt);
322 END IF;
323
324 execute immediate l_stmt;
325 IF PG_DEBUG in ('Y', 'C') THEN
326 msc_sch_wb.atp_debug(' INSERTED into mrp table '|| SQL%ROWCOUNT);
327 END IF;
328
329 IF l_column_exist = 1 THEN
330 l_stmt:= 'select distinct
331 omodv1.line_id,
332 omodv1.ship_set_id,
333 omodv1.arrival_set_id,
334 0,
335 omodv1.ato_line_id,
336 omodv1.top_model_line_id,
337 omodv1.item_type_code,
338 omodv1.order_number,
339 omodv1.schedule_ship_date,
340 omodv1.schedule_arrival_date,
341 omodv1.request_ship_date,
342 omodv1.request_arrival_date,
343 omodv1.promise_date,
344 omodv1.order_number,
345 omodv1.planning_priority
346 from oe_mrp_open_demands_v omodv1,
347 mrp_atp_schedule_temp mrp
348 where mrp.session_id = -444
349 and mrp.order_header_id = omodv1.header_id
350 and ( mrp.order_line_id = omodv1.line_id
351 OR
352 nvl(mrp.ship_set_id, -1) = nvl(omodv1.ship_set_id, -2)
353 OR
354 nvl(mrp.arrival_set_id, -1) = nvl(omodv1.arrival_set_id, -2))';
355 ELSE
356 l_stmt:= 'select distinct
357 omodv1.line_id,
358 omodv1.ship_set_id,
359 omodv1.arrival_set_id,
360 0,
361 omodv1.ato_line_id,
362 omodv1.top_model_line_id,
363 omodv1.item_type_code,
364 omodv1.order_number,
365 omodv1.schedule_ship_date,
366 omodv1.schedule_arrival_date,
367 omodv1.request_ship_date,
368 omodv1.request_arrival_date,
369 omodv1.promise_date,
370 omodv1.order_number,
371 0
372 from oe_mrp_open_demands_v omodv1,
373 mrp_atp_schedule_temp mrp
374 where mrp.session_id = -444
375 and mrp.order_header_id = omodv1.header_id
376 and ( mrp.order_line_id = omodv1.line_id
377 OR
378 nvl(mrp.ship_set_id, -1) = nvl(omodv1.ship_set_id, -2)
379 OR
380 nvl(mrp.arrival_set_id, -1) = nvl(omodv1.arrival_set_id, -2))';
381
382
383 END IF;
384
385 IF p_exclude_picked = 1 THEN
386
387 l_stmt := l_stmt ||
388 ' and not exists ( select 1
389 from wsh_delivery_details
390 where source_line_id =omodv1.line_id
391 and source_code = '||' ''OE'' '||
392 ' and released_status in ( '||' ''Y'' '|| ', '
393 ||' ''S'' '|| ', '
394 ||' ''C'' '|| ' ) ) ';
395
396 END IF;
397
398
399 IF l_column_exist is NULL and
400 p_order_by = 'omodv1.PLANNING_PRIORITY' then
401 null;
402 ELSE
403 l_stmt := l_stmt || 'ORDER BY ' ||p_order_by;
404 END if;
405
406 l_column_exist := NULL;
407
408
409
410 IF PG_DEBUG in ('Y', 'C') THEN
411 msc_sch_wb.atp_debug(' POPULATE_TEMP_TABLE - second constructed sql is '||l_stmt);
412 END IF;
413
414 j := 0;
415 OPEN cv FOR l_stmt;
416 LOOP
417 j := j + 1;
418 -- msc_sch_wb.atp_debug(' j '||j);
419 extend(filter_mrp_oe_rec,1);
420 FETCH cv
421 INTO filter_mrp_oe_rec.line_id(j),
422 filter_mrp_oe_rec.ship_set_id(j),
423 filter_mrp_oe_rec.arrival_set_id(j),
424 filter_mrp_oe_rec.seq_num(j),
425 filter_mrp_oe_rec.ato_line_id(j),
426 filter_mrp_oe_rec.top_model_line_id(j),
427 filter_mrp_oe_rec.item_type_code(j),
428 filter_mrp_oe_rec.order_number(j),
429 dummy_schedule_ship_date,
430 dummy_schedule_arrival_date,
431 dummy_request_ship_date,
432 dummy_request_arrival_date,
433 dummy_promise_date,
434 dummy_order_number,
435 dummy_order_number; -- this is for planning priority
436
437 EXIT WHEN cv%NOTFOUND;
438 END LOOP;
439 trim(filter_mrp_oe_rec,1);
440 CLOSE cv;
441 num_of_lines := j-1;
442
443 IF PG_DEBUG in ('Y', 'C') THEN
444 msc_sch_wb.atp_debug('POPULATE_TEMP_TABLE : ' || ' Num of order lines selected '||num_of_lines);
445 END IF;
446
447
448 -- Set the line_id to -9999 for the recrds which we want to get rid of later
449 -- because they are in this ship sets with ato lines
450
451 FOR i IN 1..num_of_lines LOOP
452 IF ((filter_mrp_oe_rec.ato_line_id(i) IS NOT NULL OR
453 filter_mrp_oe_rec.top_model_line_id(i) IS NOT NULL) AND
454 filter_mrp_oe_rec.item_type_code(i) not in ( 'KIT', 'INCLUDED') )
455 AND filter_mrp_oe_rec.ship_set_id(i) is NOT NULL THEN
456 filter_sset_id := filter_mrp_oe_rec.ship_set_id(i);
457 FOR j in i..num_of_lines LOOP
458 IF filter_mrp_oe_rec.ship_set_id(j) = filter_sset_id THEN
459 filter_mrp_oe_rec.line_id(j) := -9999;
460 END IF;
461 END LOOP;
462 END IF;
463 END LOOP;
464
465 -- Set the line_id to NULL for the records which we want to get rid of later
466 -- because they are in the arrival set with ato lines
467
468 FOR i IN 1..num_of_lines LOOP
469 IF ((filter_mrp_oe_rec.ato_line_id(i) IS NOT NULL OR
470 filter_mrp_oe_rec.top_model_line_id(i) IS NOT NULL) AND
471 filter_mrp_oe_rec.item_type_code(i) not in ('KIT', 'INCLUDED') )
472 AND filter_mrp_oe_rec.arrival_set_id(i) is NOT NULL THEN
473 filter_sset_id := filter_mrp_oe_rec.arrival_set_id(i);
474 FOR j in i..num_of_lines LOOP
475 IF filter_mrp_oe_rec.arrival_set_id(j) = filter_sset_id THEN
476 filter_mrp_oe_rec.line_id(j) := -9999;
477 END IF;
478 END LOOP;
479 END IF;
480 END LOOP;
481
482 -- identify all the INCLUDED lines which belongs to KITS
483 FOR i IN 1..num_of_lines LOOP
484 IF ( filter_mrp_oe_rec.item_type_code(i) = 'KIT' AND
485 (filter_mrp_oe_rec.top_model_line_id(i) =
486 filter_mrp_oe_rec.line_id(i) ) ) THEN
487 l_order_number := filter_mrp_oe_rec.order_number(i);
488 l_top_model_id := filter_mrp_oe_rec.top_model_line_id(i);
489 FOR j in i..num_of_lines LOOP
490 if (filter_mrp_oe_rec.order_number(j) = l_order_number) AND
491 (filter_mrp_oe_rec.top_model_line_id(j) =
492 l_top_model_id) THEN
493 filter_mrp_oe_rec.item_type_code(j) := 'INKITS';
494 end if;
495 END LOOP;
496 END IF;
497 END LOOP;
498
499
500 -- populate l_mrp_oe_rec with the good records from filter_mrp_oe_rec
501 a := 1;
502 FOR i IN 1..num_of_lines LOOP
503 IF ( filter_mrp_oe_rec.line_id(i) <> -9999 AND
504 ( (filter_mrp_oe_rec.item_type_code(i) = 'STANDARD') OR
505 (filter_mrp_oe_rec.item_type_code(i) = 'INKITS')) ) THEN
506 extend(l_mrp_oe_rec,1);
507 l_mrp_oe_rec.line_id (a) := filter_mrp_oe_rec.line_id(i);
508 l_mrp_oe_rec.ship_set_id(a) := filter_mrp_oe_rec.ship_set_id(i);
509 l_mrp_oe_rec.arrival_set_id(a) := filter_mrp_oe_rec.arrival_set_id(i);
510 l_mrp_oe_rec.seq_num(a):= filter_mrp_oe_rec.seq_num(i);
511 l_mrp_oe_rec.ato_line_id(a):= NULL;
512 l_mrp_oe_rec.top_model_line_id(a) := NULL;
513 l_mrp_oe_rec.item_type_code(a):= filter_mrp_oe_rec.item_type_code(i);
514 a := a + 1;
515
516 END IF;
517 END LOOP;
518
519 IF PG_DEBUG in ('Y', 'C') THEN
520 msc_sch_wb.atp_debug( 'end of loop l_mrp_oe_rec.line_id.count is ' ||
521 l_mrp_oe_rec.line_id.count);
522 END IF;
523
524 num_of_lines := l_mrp_oe_rec.line_id.count;
525 l_seq := 0;
526 FOR k IN 1..num_of_lines LOOP
527 IF l_mrp_oe_rec.seq_num(k) = 0 THEN
528 -- If a seq num has already not been assigned.
529 l_seq := l_seq + 1;
530 l_mrp_oe_rec.seq_num(k) := l_seq;
531 IF l_mrp_oe_rec.ship_set_id(k) IS NOT NULL
532 OR l_mrp_oe_rec.arrival_set_id(k) IS NOT NULL THEN
533 -- If it is a ship or arrival set, loop thru rest of records and
534 -- assign the same (highest) seq num to other lines.
535 l_ship_set_id := l_mrp_oe_rec.ship_set_id(k);
536 l_arrival_set_id := l_mrp_oe_rec.arrival_set_id(k);
537 FOR m IN k+1..num_of_lines LOOP
538 IF ((l_mrp_oe_rec.ship_set_id(m) = l_ship_set_id
539 OR l_mrp_oe_rec.arrival_set_id(m) = l_arrival_set_id)
540 AND l_mrp_oe_rec.seq_num(m) = 0) THEN
541 l_mrp_oe_rec.seq_num(m) := l_seq;
542 END IF;
543 END LOOP;
544 END IF;
545 END IF;
546 END LOOP;
547
548
549 FOR k IN 1..num_of_lines LOOP
550 IF PG_DEBUG in ('Y', 'C') THEN
551 msc_sch_wb.atp_debug('POPULATE_TEMP_TABLE: ' || ' line_id '
552 ||l_mrp_oe_rec.line_id(k)
553 || 'item_type_code '
554 || l_mrp_oe_rec.item_type_code(k)
555 ||' seq '||l_mrp_oe_rec.seq_num(k));
556 END IF;
557 END LOOP;
558
559 open check_oe('FLOW_STATUS_CODE');
560 fetch check_oe into l_column_exist;
561 close check_oe;
562 j := 1;
563
564
565 if l_column_exist =1 then
566
567 l_sql_stmt := l_sql_stmt1 ||
568 l_new_columns ||
569 l_sql_stmt2;
570 else
571 l_sql_stmt := l_sql_stmt1 ||
572 l_old_columns ||
573 l_sql_stmt2;
574
575 end if;
576
577 if (NVL(fnd_profile.value('MRP_ATP_CALC_SD'), 'N')) = 'Y' then
578 l_profile_value := 1;
579 end if;
580
581
582
583 -- move data to bind arrays
584 FOR x in 1 .. l_mrp_oe_rec.seq_num.LAST LOOP
585 bind1(x) := l_mrp_oe_rec.seq_num(x);
586 bind2(x) := l_mrp_oe_rec.line_id(x);
587 END LOOP;
588
589
590 -- open cursor
591 cur_hdl := dbms_sql.open_cursor;
592
593 -- parse cursor
594 dbms_sql.parse(cur_hdl, l_sql_stmt, dbms_sql.native);
595
596 -- supply binds
597 dbms_sql.bind_array (cur_hdl, ':l_seq_num', bind1);
598 dbms_sql.bind_array (cur_hdl, ':l_line_id', bind2);
599 dbms_sql.bind_variable (cur_hdl, ':p_session_id', p_session_id);
600 dbms_sql.bind_variable (cur_hdl, ':l_profile_value', l_profile_value);
601 dbms_sql.bind_variable (cur_hdl, ':p_overwrite', p_overwrite);
602 dbms_sql.bind_variable (cur_hdl, ':p_overwrite', p_overwrite);
603
604 -- execute cursor
605 rows_processed := dbms_sql.execute(cur_hdl);
606
607 -- close cursor
608 dbms_sql.close_cursor(cur_hdl);
609
610 IF PG_DEBUG in ('Y', 'C') THEN
611 msc_sch_wb.atp_debug('MSC_BAL_UTILS.populate_temp_table ' );
612 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
613 into l_time
614 from dual;
615 msc_sch_wb.atp_debug( 'POPULATE_TEMP_TABLE: END ' || l_time);
616
617 msc_sch_wb.atp_debug(' Calculate supply/demand profile_value is ' || l_profile_value );
618
619 END IF;
620
621 commit;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 IF PG_DEBUG in ('Y', 'C') THEN
626 msc_sch_wb.atp_debug('extend: ' || ' Excp in populate temp table '||Substr(Sqlerrm, 1,100));
627 END IF;
628 END populate_temp_table;
629
630 PROCEDURE undemand_orders (p_session_id NUMBER,
631 x_msg_count IN OUT NoCopy NUMBER ,
632 x_msg_data IN OUT NoCopy VARCHAR2 ,
633 x_return_status IN OUT NoCopy VARCHAR2 ) IS
634
635 x_atp_rec MRP_ATP_PUB.atp_rec_typ;
636 x_atp_rec_out MRP_ATP_PUB.atp_rec_typ;
637 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
638 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
639 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
640 char_1_null VARCHAR2(2000) := NULL;
641 char_30_null VARCHAR2(30) := NULL;
642 number_null NUMBER := null;
643 date_null DATE := null;
644 l_session_id NUMBER := p_session_id;
645
646 BEGIN
647 IF PG_DEBUG in ('Y', 'C') THEN
648 msc_sch_wb.atp_debug('UNDEMAND_ORDERS ');
649 END IF;
650
651 SELECT
652 Rowidtochar(a.ROWID),
653 a.inventory_item_id,
654 a.inventory_item_name,
655 a.organization_id,
656 a.sr_instance_id,
657 Decode(override_flag,'Y',Nvl(a.firm_source_org_id,
658 a.source_organization_id),
659 a.source_organization_id),
660 Decode(override_flag,'Y',Nvl(a.firm_source_org_code,
661 a.source_organization_code),
662 a.source_organization_code),
663 a.order_line_id,
664 a.Scenario_Id,
665 a.Order_Header_Id,
666 a.order_number,
667 a.Calling_Module,
668 a.Customer_Id,
669 a.Customer_Site_Id,
670 a.Destination_Time_Zone,
671 0, -- send qty = 0 to atp
672 a.uom_code,
673 Decode(override_flag,'Y', Nvl(a.firm_ship_date,a.requested_ship_date),
674 a.requested_ship_date),
675 Decode(override_flag,'Y', Nvl(a.firm_arrival_date,a.requested_arrival_date)
676 , a.requested_arrival_date),
677 date_null, -- a.Earliest_Acceptable_Date,
678 a.Latest_Acceptable_Date,
679 a.Delivery_Lead_Time,
680 a.Freight_Carrier,
681 a.Ship_Method,
682 a.Demand_Class,
683 a.Ship_Set_Name,
684 -- When it is put back into the table the name will be used.
685 a.arrival_set_id, --a.Arrival_Set_Name
686 -- we don't append source_org since they can be different
687 -- and we don't need it since we don't have pick sources
688 a.Override_Flag,
689 a.Action,
690 date_null, --a.Ship_Date, ??? scheduled_ship_date
691 number_null, -- a.Available_Quantity,
692 number_null, -- a.Requested_Date_Quantity,
693 date_null, -- a.Group_Ship_Date,
694 date_null, -- a.Group_Arrival_Date,
695 a.Vendor_Id,
696 a.Vendor_Name,
697 a.Vendor_Site_Id,
698 a.Vendor_Site_Name,
699 a.Insert_Flag,
700 number_null, -- a.Error_Code,
701 char_1_null, -- a.Error_Message
702 a.old_source_organization_id,
703 a.old_demand_class,
704 a.atp_lead_time, -- bug 1303240
705 null, --substitution_typ_code,
706 null, -- REQ_ITEM_DETAIL_FLAG
707 2, -- ATP Pegging
708 a.assignment_set_id, -- ATP Pegging
709 a.sequence_number,
710 a.firm_flag,
711 a.order_line_number,
712 a.option_number,
713 a.shipment_number,
714 a.item_desc,
715 a.old_line_schedule_date,
716 a.old_source_organization_code,
717 a.firm_source_org_id,
718 a.firm_source_org_code,
719 a.firm_ship_date,
720 a.firm_arrival_date,
721 a.ship_method_text,
722 a.ship_set_id,
723 a.arrival_set_id,
724 a.PROJECT_ID,
725 a.TASK_ID,
726 a.PROJECT_NUMBER,
727 a.TASK_NUMBER,
728 a.Top_Model_line_id,
729 a.ATO_Model_Line_Id,
730 a.Parent_line_id,
731 a.Config_item_line_id,
732 a.Validation_Org,
733 a.Component_Sequence_ID,
734 a.Component_Code,
735 a.line_number,
736 a.included_item_flag
737 bulk collect into
738 x_atp_rec.row_id,
739 x_atp_rec.Inventory_Item_Id,
740 x_atp_rec.Inventory_Item_Name,
741 x_atp_rec.organization_id,
742 x_atp_rec.instance_id,
743 x_atp_rec.Source_Organization_Id,
744 x_atp_rec.Source_Organization_Code,
745 x_atp_rec.Identifier,
746 x_atp_rec.Scenario_Id,
747 x_atp_rec.Demand_Source_Header_Id,
748 x_atp_rec.order_number,
749 x_atp_rec.Calling_Module,
750 x_atp_rec.Customer_Id,
751 x_atp_rec.Customer_Site_Id,
752 x_atp_rec.Destination_Time_Zone,
753 x_atp_rec.Quantity_Ordered,
754 x_atp_rec.Quantity_UOM,
755 x_atp_rec.Requested_Ship_Date,
756 x_atp_rec.Requested_Arrival_Date,
757 x_atp_rec.Earliest_Acceptable_Date,
758 x_atp_rec.Latest_Acceptable_Date,
759 x_atp_rec.Delivery_Lead_Time,
760 x_atp_rec.Freight_Carrier,
761 x_atp_rec.Ship_Method,
762 x_atp_rec.Demand_Class,
763 x_atp_rec.Ship_Set_Name,
764 x_atp_rec.Arrival_Set_Name,
765 x_atp_rec.Override_Flag,
766 x_atp_rec.Action,
767 x_atp_rec.Ship_Date,
768 x_atp_rec.Available_Quantity,
769 x_atp_rec.Requested_Date_Quantity,
770 x_atp_rec.Group_Ship_Date,
771 x_atp_rec.Group_Arrival_Date,
772 x_atp_rec.Vendor_Id,
773 x_atp_rec.Vendor_Name,
774 x_atp_rec.Vendor_Site_Id,
775 x_atp_rec.Vendor_Site_Name,
776 x_atp_rec.Insert_Flag,
777 x_atp_rec.Error_Code,
778 x_atp_rec.message,
779 x_atp_rec.old_source_organization_id,
780 x_atp_rec.old_demand_class,
781 x_atp_rec.atp_lead_time, -- bug 1303240
782 x_atp_rec.substitution_typ_code,
783 x_atp_rec.REQ_ITEM_DETAIL_FLAG,
784 x_atp_rec.attribute_02, -- ATP Pegging
785 x_atp_rec.attribute_03,
786 x_atp_rec.sequence_number,
787 x_atp_rec.firm_flag,
788 x_atp_rec.order_line_number,
789 x_atp_rec.option_number,
790 x_atp_rec.shipment_number,
791 x_atp_rec.item_desc,
792 x_atp_rec.old_line_schedule_date,
793 x_atp_rec.old_source_organization_code,
794 x_atp_rec.firm_source_org_id,
795 x_atp_rec.firm_source_org_code,
796 x_atp_rec.firm_ship_date,
797 x_atp_rec.firm_arrival_date,
798 x_atp_rec.ship_method_text,
799 x_atp_rec.ship_set_id,
800 x_atp_rec.arrival_set_id,
801 x_atp_rec.PROJECT_ID,
802 x_atp_rec.TASK_ID,
803 x_atp_rec.PROJECT_NUMBER,
804 x_atp_rec.TASK_NUMBER,
805 x_atp_rec.Top_Model_line_id,
806 x_atp_rec.ATO_Model_Line_Id,
807 x_atp_rec.Parent_line_id,
808 x_atp_rec.Config_item_line_id,
809 x_atp_rec.Validation_Org,
810 x_atp_rec.Component_Sequence_ID,
811 x_atp_rec.Component_Code,
812 x_atp_rec.line_number,
813 x_atp_rec.included_item_flag
814 from mrp_atp_schedule_temp a
815 where a.session_id = p_session_id
816 and a.status_flag = 1
817 order by a.sequence_number;
818
819 IF x_atp_rec.inventory_item_id.count > 0 THEN
820 IF PG_DEBUG in ('Y', 'C') THEN
821 msc_sch_wb.atp_debug('UNDEMAND_ORDERS: '
822 || ' Before calling scheduling '
823 ||x_atp_rec.inventory_item_id.COUNT);
824 END IF;
825
826 MSC_SATP_FUNC.new_extend_atp(x_atp_rec,
827 x_atp_rec.inventory_item_id.count,
828 x_return_status);
829
830 IF PG_DEBUG in ('Y', 'C') THEN
831 msc_sch_wb.atp_debug('GET_ATP_RESULT: after new_extend_atp'||
832 x_return_status);
833 END IF;
834
835 IF x_return_status <> 'E' THEN
836
837 MRP_ATP_PUB.call_atp_no_commit
838 (l_session_id,
839 x_atp_rec,
840 x_atp_rec_out,
841 x_atp_supply_demand,
842 x_atp_period,
843 x_atp_details,
844 x_return_status,
845 x_msg_data,
846 x_msg_count);
847 END IF;
848
849 END IF;
850
851 IF PG_DEBUG in ('Y', 'C') THEN
852 msc_sch_wb.atp_debug('UNDEMAND_ORDERS '
853 || 'After calling Scheduling '
854 || x_return_status||' $ '
855 ||x_msg_data||' $ '
856 ||x_atp_rec_out.inventory_item_id.count);
857 END IF;
858
859 IF x_return_status = 'E' then
860 IF PG_DEBUG in ('Y', 'C') THEN
861 msc_sch_wb. atp_debug('UNDEMAND_ORDERS '
862 || ' err '
863 ||x_msg_data||' '
864 ||x_msg_count);
865 END IF;
866 end if;
867
868 if x_atp_rec_out.inventory_item_id.count > 0 then
869 IF PG_DEBUG in ('Y', 'C') THEN
870 msc_sch_wb.atp_debug('UNDEMAND_ORDERS '
871 || ' sched date '
872 ||x_atp_rec_out.ship_date.count);
873 msc_sch_wb.atp_debug('UNDEMAND_ORDERS '
874 || ' SD '||x_atp_supply_demand.level.count);
875 msc_sch_wb.atp_debug('UNDEMAND_ORDERS '
876 || ' period '||x_atp_period.level.count);
877 msc_sch_wb.atp_debug('UNDEMAND_ORDERS '
878 || ' details '||x_atp_details.level.count)
879 ;
880 END IF;
881 end if;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 IF PG_DEBUG in ('Y', 'C') THEN
886 msc_sch_wb.atp_debug(' Exception in undemand_orders '
887 ||Substr(Sqlerrm,1,100));
888 END IF;
889 x_return_status := 'E';
890 x_msg_data := Substr(Sqlerrm,1,100);
891
892 END undemand_orders;
893
894 PROCEDURE reschedule(p_session_id NUMBER,
895 x_msg_count OUT NoCopy NUMBER,
896 x_msg_data OUT NoCopy varchar2,
897 x_return_status OUT NoCopy varchar2,
898 p_tcf BOOLEAN default TRUE
899
900 ) IS
901
902
903
904 p_atp_qty_ordered_temp ATP_QTY_ORDERED_TYP;
905 l_return_status VARCHAR2(1);
906 l_error_message VARCHAR2(100);
907 l_time VARCHAR2(80);
908
909 BEGIN
910 order_sch_wb.debug_session_id := p_session_id;
911 -- need to remember the original qty ordered before undemanding
912 IF PG_DEBUG in ('Y', 'C') THEN
913 msc_sch_wb.atp_debug('MSC_BAL_UTILS.reschedule ' );
914 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
915 into l_time
916 from dual;
917 msc_sch_wb.atp_debug( 'RESCHEDULE: BEGIN ' || l_time);
918 END IF;
919
920
921 select quantity_ordered, order_line_id, session_id
922 bulk collect into
923 p_atp_qty_ordered_temp.quantity_ordered,
924 p_atp_qty_ordered_temp.order_line_id,
925 p_atp_qty_ordered_temp.session_id
926 from mrp_atp_schedule_temp
927 where session_id = p_session_id
928 and status_flag =1 ;
929
930 IF PG_DEBUG in ('Y', 'C') THEN
931 msc_sch_wb.atp_debug('MSC_BAL_UTILS.reschedule ' );
932 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
933 into l_time
934 from dual;
935 msc_sch_wb.atp_debug( 'RESCHEDULE: b4 undemand_orders ' || l_time);
936 END IF;
937
938 undemand_orders(p_session_id, x_msg_count, x_msg_data, x_return_status);
939
940 IF PG_DEBUG in ('Y', 'C') THEN
941 msc_sch_wb.atp_debug('MSC_BAL_UTILS.reschedule ' );
942 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
943 into l_time
944 from dual;
945 msc_sch_wb.atp_debug( 'RESCHEDULE: after undemand_orders ' || l_time);
946 END IF;
947 update_schedule_qties(p_atp_qty_ordered_temp,
948 l_return_status,
949 l_error_message);
950
951 IF l_return_status <> 'E' THEN
952 IF PG_DEBUG in ('Y', 'C') THEN
953 msc_sch_wb.atp_debug('MSC_BAL_UTILS.reschedule ' );
954 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
955 into l_time
956 from dual;
957 msc_sch_wb.atp_debug( 'RESCHEDULE: b4 schedule_orders ' || l_time);
958 END IF;
959
960 schedule_orders(p_session_id, x_msg_count, x_msg_data, x_return_status);
961
962 IF PG_DEBUG in ('Y', 'C') THEN
963 msc_sch_wb.atp_debug('MSC_BAL_UTILS.reschedule ' );
964 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
965 into l_time
966 from dual;
967 msc_sch_wb.atp_debug( 'RESCHEDULE: after schedule_orders ' || l_time);
968 END IF;
969 ELSE
970 IF PG_DEBUG in ('Y', 'C') THEN
971 msc_sch_wb.atp_debug('RESCHEDULE: ' || ' error is update_qty '|| l_error_message);
972 END IF;
973 END IF;
974
975
976 END reschedule ;
977
978
979 PROCEDURE update_schedule_qties(p_atp_qty_ordered_temp IN MSC_BAL_UTILS.ATP_QTY_ORDERED_TYP,
980 p_return_status out nocopy VARCHAR2,
981 p_error_message out nocopy VARCHAR2) IS
982 l_count NUMBER :=0;
983 BEGIN
984 p_return_status := 'S';
985
986 IF PG_DEBUG in ('Y', 'C') THEN
987 select count(*)
988 INTO l_count
989 from mrp_atp_schedule_temp
990 where session_id = p_atp_qty_ordered_temp.session_id(1)
991 and status_flag = 1;
992 msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties status_flag = 1 rec are '|| l_count);
993
994 l_count := 0;
995 select count(*)
996 INTO l_count
997 from mrp_atp_schedule_temp
998 where session_id = p_atp_qty_ordered_temp.session_id(1)
999 and status_flag = 2;
1000 msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties status_flag = 2 rec are '|| l_count);
1001
1002 l_count := 0;
1003 select count(*)
1004 INTO l_count
1005 from mrp_atp_schedule_temp
1006 where session_id = p_atp_qty_ordered_temp.session_id(1);
1007 msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties TOTAL rec are '|| l_count);
1008
1009 END IF;
1010
1011 FORALL lCounter IN 1 .. p_atp_qty_ordered_temp.order_line_id.COUNT
1012 update mrp_atp_schedule_temp
1013 set status_flag = 1,
1014 quantity_ordered = p_atp_qty_ordered_temp.quantity_ordered(lCounter)
1015 where session_id =p_atp_qty_ordered_temp.session_id(lCounter)
1016 and order_line_id = p_atp_qty_ordered_temp.order_line_id(lCounter)
1017 and status_flag = 2 ;
1018
1019 IF PG_DEBUG in ('Y', 'C') THEN
1020 msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties ' ||p_atp_qty_ordered_temp.order_line_id.COUNT);
1021 l_count := 0;
1022 select count(*)
1023 INTO l_count
1024 from mrp_atp_schedule_temp
1025 where session_id = p_atp_qty_ordered_temp.session_id(1);
1026 msc_sch_wb.atp_debug('MSC_BAL_UTILS.update_schedule_qties TOTAL rec are '|| l_count);
1027 END IF;
1028
1029
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 p_return_status :='E';
1033 p_error_message := substr(sqlerrm,1,100);
1034
1035 END update_schedule_qties;
1036
1037
1038
1039 PROCEDURE cmt_schedule( p_user_id NUMBER,
1040 p_resp_id NUMBER,
1041 p_appl_id NUMBER,
1042 p_session_id NUMBER,
1043 x_msg_count OUT NoCopy NUMBER,
1044 x_msg_data OUT NoCopy varchar2,
1045 x_return_status OUT NoCopy varchar2,
1046 p_tcf BOOLEAN default TRUE
1047 ) IS
1048
1049 p_atp_qty_ordered_temp ATP_QTY_ORDERED_TYP;
1050 l_return_status VARCHAR2(1);
1051 l_error_message VARCHAR2(100);
1052 l_time varchar2(80);
1053
1054 cursor records_exist is
1055 select count(*)
1056 from mrp_atp_schedule_temp
1057 where session_id = p_session_id
1058 and status_flag = 2;
1059
1060 l_records_exist NUMBER := 0;
1061 pipe_msg_count NUMBER;
1062 pipe_return_status VARCHAR2(10);
1063 pipe_msg_data VARCHAR2(10);
1064 l_count NUMBER;
1065
1066 BEGIN
1067 order_sch_wb.debug_session_id := p_session_id;
1068 IF PG_DEBUG in ('Y', 'C') THEN
1069 msc_sch_wb.atp_debug(' Begin MSC_BAL_UTILS.commit_schedule session_id '
1070 || p_session_id);
1071 msc_sch_wb.atp_debug('MSC_BAL_UTILS.cmt_schedule ' );
1072 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1073 into l_time
1074 from dual;
1075 msc_sch_wb.atp_debug( 'cmt_schedule begin ' || l_time);
1076 END IF;
1077 open records_exist;
1078 fetch records_exist into l_records_exist;
1079 close records_exist;
1080
1081 -- if records exist it means this is saving after scheduling
1082 if l_records_exist > 0 then
1083 delete from mrp_atp_schedule_temp
1084 where session_id = p_session_id
1085 and status_flag = 1;
1086
1087 delete from mrp_atp_details_temp
1088 where session_id = p_session_id;
1089
1090 update mrp_atp_schedule_temp
1091 set status_flag = 1
1092 where session_id = p_session_id
1093 and status_flag = 2;
1094 -- if i am saving after scheduling
1095 -- and om will fail we need to
1096 -- make sure these records are gone!
1097 -- otherwise everything inside this if
1098 -- will be rollbacked by atpui_util package
1099 if (p_tcf) then
1100 commit;
1101 end if;
1102
1103 end if;
1104 IF PG_DEBUG in ('Y', 'C') THEN
1105 msc_sch_wb.atp_debug('msc_bal_utils: In Commit_schedule: undemanding orders');
1106 END IF;
1107
1108 select quantity_ordered, order_line_id, session_id
1109 bulk collect into
1110 p_atp_qty_ordered_temp.quantity_ordered,
1111 p_atp_qty_ordered_temp.order_line_id,
1112 p_atp_qty_ordered_temp.session_id
1113 from mrp_atp_schedule_temp
1114 where session_id = p_session_id
1115 and status_flag =1 ;
1116 undemand_orders(p_session_id, x_msg_count, x_msg_data, x_return_status);
1117 update_schedule_qties(p_atp_qty_ordered_temp,
1118 l_return_status,
1119 l_error_message);
1120
1121 IF l_return_status <> 'E' THEN
1122 IF PG_DEBUG in ('Y', 'C') THEN
1123 msc_sch_wb.atp_debug('msc_bal_utils: In Commit_schedule ' ||
1124 ' calling get_atp_result ');
1125 END IF;
1126 msc_sch_wb.get_atp_result(p_session_id, 2, 2,
1127 x_msg_count,x_msg_data,
1128 x_return_status);
1129 IF PG_DEBUG in ('Y', 'C') THEN
1130 msc_sch_wb.atp_debug('MSC_BAL_UTILS.cmt_schedule ' );
1131 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1132 into l_time
1133 from dual;
1134 msc_sch_wb.atp_debug( 'cmt_schedule: after call to atp ' || l_time);
1135 END IF;
1136 IF x_return_status <>'S' THEN
1137 IF PG_DEBUG in ('Y', 'C') THEN
1138 msc_sch_wb.atp_debug('MSC_BAL_UTILS.commit_schedule ' ||
1139 ' sth wrong in get_atp_results'
1140 || x_msg_data);
1141 END IF;
1142 return; -- sth wrong with atp engine
1143 ELSE -- atp is successful , proceed to om call
1144 -- need to reset client_info before
1145 --calling OM api to avoid bug 3145033
1146 --dbms_application_info.set_client_info(fnd_profile.value('ORG_ID'));
1147 fnd_global.apps_initialize(p_user_id,
1148 p_resp_id,
1149 p_appl_id);
1150 mo_global.init('ONT'); -- MOAC changes
1151
1152 IF PG_DEBUG in ('Y', 'C') THEN
1153 msc_sch_wb.atp_debug('MSC_BAL_UTILS.cmt_schedule: calling OE ' );
1154 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1155 into l_time
1156 from dual;
1157 msc_sch_wb.atp_debug( 'cmt_schedule: calling OM BEGIN ' || l_time);
1158 END IF;
1159 msc_bal_utils.call_oe_api(p_session_id ,
1160 x_msg_count ,
1161 x_msg_data ,
1162 x_return_status
1163 );
1164 IF PG_DEBUG in ('Y', 'C') THEN
1165 msc_sch_wb.atp_debug('MSC_BAL_UTILS.cmt_schedule: calling OE ' );
1166 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1167 into l_time
1168 from dual;
1169 msc_sch_wb.atp_debug( 'cmt_schedule: calling OM DONE ' || l_time);
1170 msc_sch_wb.atp_debug( ' cmt_schedule : x_msg_data ' || x_msg_data);
1171 END IF;
1172 IF x_return_status <>'S' THEN -- from call_oe_api
1173 g_om_status := x_return_status;
1174 g_om_req_id := to_number(x_msg_data);
1175
1176 IF PG_DEBUG in ('Y', 'C') THEN
1177 msc_sch_wb.atp_debug('MSC_BAL_UTILS.call_oe_api in cmt_schedule'||
1178 x_msg_data);
1179 END IF;
1180 -- we need to let forms know that OM failed
1181 if (p_tcf) then
1182
1183 msc_sch_wb.atp_debug(' tcf is on x_return_status ' || x_return_status);
1184 -- call pipe api to send a message that OM failed
1185 if x_return_status = 'OMERROR' then
1186 msc_sch_wb.pipe_utility(p_session_id,
1187 'OMERROR',
1188 x_msg_data, -- this request_id
1189 pipe_msg_count,
1190 pipe_return_status,
1191 pipe_msg_data,
1192 pipe_msg_count);
1193 end if;
1194 MSC_ATPUI_UTIL.populate_mrp_atp_temp_tables(p_session_id,
1195 l_return_status,
1196 l_error_message);
1197 msc_sch_wb.calc_exceptions(p_session_id,
1198 x_return_status,
1199 x_msg_data,
1200 x_msg_count);
1201 commit; -- commit only for tcf because rollback of
1202 -- atp inserted data already happened inside of MSC_ATPUI_UTIL
1203 else -- not not p_tcf
1204
1205 MSC_ATPUI_UTIL.populate_mrp_atp_temp_tables(p_session_id,
1206 l_return_status,
1207 l_error_message);
1208
1209
1210 end if; -- if p_tcf
1211 END IF; -- oe api returned success
1212 END IF; -- sth wrong with atp engine
1213 ELSE
1214 IF PG_DEBUG in ('Y', 'C') THEN
1215 msc_sch_wb.atp_debug('cmt_schedule: ' ||
1216 ' error in update_qty '|| l_error_message);
1217 END IF;
1218 END IF;
1219
1220 IF g_om_status = 'OMERROR' THEN
1221 x_return_status := g_om_status;
1222 x_msg_data := to_char(g_om_req_id);
1223 END IF;
1224 END cmt_schedule;
1225
1226
1227 PROCEDURE schedule_orders (p_session_id NUMBER,
1228 x_msg_count OUT NoCopy NUMBER,
1229 x_msg_data OUT NoCopy varchar2,
1230 x_return_status OUT NoCopy varchar2,
1231 p_tcf BOOLEAN default TRUE
1232 ) IS
1233 --bug#2452524
1234 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1235 TYPE CharTab IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
1236 lb_rowid RowidTab;
1237 lb_flow_status_code CharTab;
1238
1239 cursor check_oe is
1240 select 1
1241 from user_tab_columns
1242 where table_name ='OE_MRP_OPEN_DEMANDS_V'
1243 and column_name ='FLOW_STATUS_CODE';
1244
1245 l_column_exist number;
1246 l_time varchar2(80);
1247
1248 TYPE CurTyp IS REF CURSOR;
1249 c1 CurTyp;
1250
1251 sql_stmt varchar2(3000) :=
1252 ' update mrp_atp_schedule_temp a set flow_status_code = '||
1253 ' ( select flow_status_code from oe_mrp_open_demands_v b '||
1254 ' where a.order_line_id = b.line_id ) '||
1255 ' where a.session_id = :p_session_id ';
1256
1257 l_return_status VARCHAR2(1);
1258 l_error_message VARCHAR2(100);
1259
1260 BEGIN
1261 IF PG_DEBUG in ('Y', 'C') THEN
1262 msc_sch_wb.atp_debug('msc_bal_utils.schedule_orders');
1263 END IF;
1264
1265 msc_sch_wb.get_atp_result(p_session_id, 2, 2,
1266 x_msg_count,x_msg_data,
1267 x_return_status);
1268 IF PG_DEBUG in ('Y', 'C') THEN
1269 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1270 into l_time
1271 from dual;
1272 msc_sch_wb.atp_debug( ' MSC_BAL_UTILS.schedule_orders atp engine call END ' || l_time);
1273 msc_sch_wb.atp_debug( ' x_return_status ' || x_return_status);
1274 END IF;
1275
1276 IF x_return_status <> 'S' THEN
1277 IF PG_DEBUG in ('Y', 'C') THEN
1278 msc_sch_wb.atp_debug('extend: '
1279 || ' schedule_orders : call to get_atp_result returned error '||x_msg_data);
1280 END IF;
1281 RETURN;
1282 ELSE
1283 -- before we used to call populate_mrp_atp_temp_tables only for
1284 -- tcf. however, client wants data in temp table even if tcf
1285 -- not used.
1286 -- so we will cal populate_mrp_atp_temp_tables all the time.
1287 -- this will insure that the data inserted by atp is rollbacked
1288 -- but temp table data stays for further usage, such as reports and etc...
1289 if ( (p_tcf) OR (NVL(fnd_profile.value('MRP_ATP_PERSIST'), 'N')) = 'Y' ) then
1290 IF PG_DEBUG in ('Y', 'C') THEN
1291 msc_sch_wb.atp_debug('MSC_BAL_UTILS.schedule_orders '||
1292 ' calling populate_mrp_atp_temp_tables ' ||
1293 ' tcf is used ');
1294
1295 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1296 into l_time
1297 from dual;
1298 msc_sch_wb.atp_debug( ' MSC_BAL_UTILS.schedule_orders b4 call to populate_mrp_atp_temp_tables ' || l_time);
1299 END IF;
1300 MSC_ATPUI_UTIL.populate_mrp_atp_temp_tables(p_session_id,
1301 l_return_status,
1302 l_error_message);
1303 IF PG_DEBUG in ('Y', 'C') THEN
1304 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1305 into l_time
1306 from dual;
1307 msc_sch_wb.atp_debug( ' MSC_BAL_UTILS.schedule_orders after call to populate_mrp_atp_temp_tables ' || l_time);
1308 END IF;
1309
1310 IF l_return_status <> 'S' THEN
1311 IF PG_DEBUG in ('Y', 'C') THEN
1312 msc_sch_wb.atp_debug('MSC_BAL_UTILS.schedule_orders '||
1313 'sth wrong in populate_mrp_atp_temp_tables ' ||
1314 l_error_message);
1315 END IF;
1316 END IF;
1317 end if; -- p_tcf
1318
1319
1320 IF PG_DEBUG in ('Y', 'C') THEN
1321 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1322 into l_time
1323 from dual;
1324 msc_sch_wb.atp_debug( ' MSC_BAL_UTILS.schedule_orders b4 call to calc_exceptions ' || l_time);
1325 END IF;
1326 msc_sch_wb.calc_exceptions(p_session_id,
1327 x_return_status,
1328 x_msg_data,
1329 x_msg_count);
1330 if ( (p_tcf) OR (NVL(fnd_profile.value('MRP_ATP_PERSIST'), 'N')) = 'Y' )then
1331 commit;
1332 end if;
1333
1334 IF PG_DEBUG in ('Y', 'C') THEN
1335 select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')
1336 into l_time
1337 from dual;
1338 msc_sch_wb.atp_debug( ' MSC_BAL_UTILS.schedule_orders after call to calc_exceptions ' || l_time);
1339 END IF;
1340
1341 IF x_return_status <> 'S' THEN
1342 IF PG_DEBUG in ('Y', 'C') THEN
1343 msc_sch_wb.atp_debug('extend: ' ||
1344 ' schedule_orders : call to calc_exceptions returned error '||x_msg_data);
1345 END IF;
1346 ELSE
1347 if (p_tcf) then
1348 commit; -- commit exception calculations
1349 end if;
1350 END IF;
1351 END IF;
1352 open check_oe;
1353 fetch check_oe into l_column_exist;
1354 close check_oe;
1355
1356 if l_column_exist =1 then
1357 --dbms_application_info.set_client_info(fnd_profile.value('ORG_ID'));
1358 mo_global.init('ONT');
1359 execute immediate sql_stmt using p_session_id;
1360 end if;
1361
1362 END schedule_orders;
1363
1364
1365 PROCEDURE execute_command (p_command VARCHAR2,
1366 p_user_command NUMBER,
1367 x_msg_data OUT NoCopy varchar2,
1368 x_return_status OUT NoCopy varchar2 )
1369 IS
1370 x_msg_count NUMBER;
1371 dummy1 VARCHAR2(1000);
1372 dummy2 VARCHAR2(1000);
1373 BEGIN
1374
1375 x_return_status := 'S';
1376 IF p_user_command = 1 THEN -- then we pass the std arguments
1377 EXECUTE immediate p_command using
1378 OUT x_msg_count, OUT x_msg_data, OUT x_return_status;
1379 ELSE
1380 EXECUTE immediate p_command;
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 IF PG_DEBUG in ('Y', 'C') THEN
1386 msc_sch_wb.atp_debug('extend: ' || ' Exception in execute command '||substr(sqlerrm,1,100));
1387 END IF;
1388 x_return_status := 'E';
1389 x_msg_data := substr(sqlerrm,1,100);
1390 END execute_command;
1391
1392
1393 PROCEDURE extend( p_nodes IN OUT NoCopy mrp_bal_utils.mrp_oe_rec, extend_amount NUMBER ) IS
1394 BEGIN
1395 p_nodes.line_id.extend( extend_amount );
1396 p_nodes.ship_set_id.extend( extend_amount );
1397 p_nodes.arrival_set_id.extend( extend_amount );
1398 p_nodes.seq_num.extend( extend_amount );
1399 p_nodes.ato_line_id.extend(extend_amount);
1400 p_nodes.top_model_line_id.extend(extend_amount);
1401 p_nodes.item_type_code.extend(extend_amount);
1402 p_nodes.order_number.extend(extend_amount);
1403 END extend;
1404
1405 PROCEDURE extend( p_nodes IN OUT NoCopy mrp_bal_utils.seq_alter , extend_amount NUMBER ) IS
1406 BEGIN
1407 p_nodes.order_line_id.extend( extend_amount );
1408 p_nodes.ship_set_id.extend( extend_amount );
1409 p_nodes.arrival_set_id.extend( extend_amount );
1410 p_nodes.seq_diff.extend( extend_amount );
1411 END extend;
1412
1413 PROCEDURE trim( p_nodes IN OUT NoCopy mrp_bal_utils.mrp_oe_rec, trim_amount NUMBER ) IS
1414 BEGIN
1415 p_nodes.line_id.trim( trim_amount );
1416 p_nodes.ship_set_id.trim( trim_amount );
1417 p_nodes.arrival_set_id.trim( trim_amount );
1418 p_nodes.seq_num.trim( trim_amount );
1419 END trim;
1420
1421 PROCEDURE call_oe_api (p_session_id NUMBER,
1422 x_msg_count OUT NoCopy NUMBER,
1423 x_msg_data OUT NoCopy varchar2,
1424 x_return_status OUT NoCopy varchar2
1425 )
1426 IS
1427
1428 -- Records must be passed by ordering with
1429 --org_id(OU),header_id, arrrival_set_id, ship_set_id, top_model_line_id, ato_line_id
1430
1431 CURSOR mast_cursor IS
1432 SELECT mrp.order_line_id,
1433 omodv.org_id,
1434 mrp.order_header_id,
1435 mrp.source_organization_id,
1436 nvl(mrp.group_ship_date,mrp.scheduled_ship_date),
1437 nvl(mrp.group_arrival_date,mrp.scheduled_arrival_date),
1438 to_date(null), --earliest_ship_date
1439 mrp.delivery_lead_time,
1440 mrp.ship_method,
1441 decode(mrp.firm_flag, 1, 'Y', 2, 'N')
1442 FROM mrp_atp_schedule_temp mrp,
1443 oe_mrp_open_demands_v omodv
1444 WHERE mrp.session_id = p_session_id
1445 AND mrp.order_line_id = omodv.line_id
1446 AND mrp.order_header_id = omodv.header_id
1447 AND mrp.status_flag = 2
1448 AND (mrp.error_code is NULL OR
1449 (mrp.error_code is not NULL and mrp.error_code IN ('0','150','61')))
1450 ORDER BY omodv.org_id, mrp.order_header_id, omodv.arrival_set_id,
1451 omodv.ship_set_id, omodv.top_model_line_id, omodv.ato_line_id;
1452
1453
1454
1455 mast_table oe_order_sch_util.mrp_line_tbl_type;
1456 --mast_table RSF1.mrp_line_tbl_type;
1457 p_so_table OE_SCHEDULE_GRP.Sch_Tbl_Type;
1458
1459 j NUMBER;
1460 l_dir VARCHAR2(60);
1461 l_file_val VARCHAR2(100);
1462 l_request_id NUMBER;
1463
1464 BEGIN
1465
1466 x_return_status := 'S';
1467
1468 OPEN mast_cursor;
1469 j := 1;
1470 LOOP
1471 FETCH mast_cursor INTO p_so_table(j).line_id,
1472 p_so_table(j).Org_id,
1473 p_so_table(j).Header_id,
1474 p_so_table(j).Ship_from_org_id,
1475 p_so_table(j).Schedule_ship_date,
1476 p_so_table(j).Schedule_arrival_date,
1477 p_so_table(j).Earliest_ship_date,
1478 p_so_table(j).Delivery_lead_time,
1479 p_so_table(j).Shipping_Method_Code,
1480 p_so_table(j).Firm_Demand_Flag;
1481 EXIT WHEN mast_cursor%notfound;
1482 IF PG_DEBUG in ('Y', 'C') THEN
1483 msc_sch_wb.atp_debug(' call_oe_api: ' ||
1484 ' Fetching record to pass to OE: line_id '||p_so_table(j).line_id ||
1485 ' schedule_ship_date ' || p_so_table(j).Schedule_ship_date ||
1486 ' arrival_date ' || p_so_table(j).schedule_arrival_date||
1487 ' ship_from_org_id ' || p_so_table(j).ship_from_org_id ||
1488 ' ship_method ' ||p_so_table(j).Shipping_Method_Code ||
1489 ' operating_unit ' || p_so_table(j).Org_id);
1490 END IF;
1491 j := j+1;
1492 END LOOP;
1493
1494 BEGIN
1495 -- setup OM debug file if ATP Debug is set to Yes
1496 IF PG_DEBUG in ('Y', 'C') THEN
1497 msc_sch_wb.atp_debug('call_oe_api: ' ||
1498 ' before calling Update_Scheduling_Results '
1499 ||p_so_table.count);
1500 msc_sch_wb.atp_debug( 'Initializing OM Debug file generation ');
1501
1502 select ltrim(rtrim(value))
1503 into l_dir
1504 from (select value from v$parameter2
1505 where name='utl_file_dir' order by rownum desc)
1506 where rownum <2;
1507
1508 if l_dir is null then
1509 select value
1510 into l_dir
1511 from v$parameter
1512 where name = 'utl_file_dir';
1513 end if;
1514
1515 msc_sch_wb.atp_debug( 'call_oe_api:OM debugging dir is ' || l_dir);
1516
1517 fnd_profile.put('OE_DEBUG_LOG_DIRECTORY',l_dir);
1518 oe_debug_pub.debug_on;
1519 oe_debug_pub.initialize;
1520 oe_debug_pub.setdebuglevel(5);
1521 msc_sch_wb.atp_debug( 'call_oe_api:OM debugging done setdebuglevel');
1522 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
1523
1524
1525 msc_sch_wb.atp_debug('call_oe_api: ' ||
1526 ' OM debugging l_file_val is ' || l_file_val);
1527 oe_debug_pub.add('CALLING FROM ATP',1);
1528
1529
1530 END IF;
1531 EXCEPTION when others then
1532 msc_sch_wb.atp_debug( ' There is something wrong with OE debug
1533 file generation. No OM debug file will
1534 be created ');
1535 msc_sch_wb.atp_debug(' The error is '||Substr(Sqlerrm,1,100));
1536 END;
1537
1538 SELECT oe_msg_request_id_s.nextval
1539 INTO l_request_id
1540 FROM dual;
1541
1542 OE_SCHEDULE_GRP.Update_Scheduling_Results(
1543 p_so_table,
1544 l_request_id,
1545 x_return_status);
1546
1547 IF PG_DEBUG in ('Y', 'C') THEN
1548 msc_sch_wb.atp_debug('in call_oe_api: '
1549 || ' after calling Update_Results_from_backlog_wb '
1550 ||x_return_status||' '|| x_return_status);
1551 END IF;
1552 IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
1553 IF PG_DEBUG in ('Y', 'C') THEN
1554 msc_sch_wb.atp_debug('call_oe_api: ' ||
1555 ' Committing session after call to OE_API'
1556 ||x_return_status);
1557 END IF;
1558 COMMIT;
1559 ELSE -- om failed
1560 IF PG_DEBUG in ('Y', 'C') THEN
1561 msc_sch_wb.atp_debug('in call_oe_api: '
1562 || ' call to OE_AP errored out x_return_status '
1563 ||x_return_status );
1564 END IF;
1565 -- if error status is W or E
1566 -- that is a valid error which
1567 -- could be looked up in OM messages
1568 if x_return_status <> 'U' then
1569 x_msg_data := to_char(l_request_id);
1570 x_return_status := 'OMERROR';
1571 else -- still error but no messages are found, so un-expected
1572 x_return_status := 'U';
1573 x_msg_data := 'Unexpected error in oe_order_sch_util.Update_Results_from_backlog_wb ';
1574 end if;
1575 -- do not rollback here, even if om fails
1576 -- we need to hold on to atp records
1577 -- rollback in parent call
1578 -- ROLLBACK;
1579 END IF;
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 x_return_status := 'E';
1583 IF PG_DEBUG in ('Y', 'C') THEN
1584 msc_sch_wb.atp_debug(' excp in call_oe_api '||Substr(Sqlerrm,1,100));
1585 END IF;
1586 END call_oe_api;
1587
1588 PROCEDURE call_oe_api (p_atp_rec MRP_ATP_PUB.atp_rec_typ,
1589 x_msg_count OUT NoCopy NUMBER,
1590 x_msg_data OUT NoCopy VARCHAR2,
1591 x_return_status OUT NoCopy VARCHAR2)
1592 IS
1593 mast_table oe_order_sch_util.mrp_line_tbl_type;
1594 counter NUMBER := 0;
1595 BEGIN
1596
1597 FOR j IN 1..p_atp_rec.inventory_item_id.COUNT LOOP
1598 IF PG_DEBUG in ('Y', 'C') THEN
1599 msc_sch_wb.atp_debug(' call_oe_api '||p_atp_rec.identifier(j)||' '||p_atp_rec.ship_date(j)||' '||
1600 p_atp_rec.source_organization_id(j)||' '||p_atp_rec.error_code(j));
1601 END IF;
1602
1603 -- cnazarma bug #2605828 only need to select eligible lines
1604 IF p_atp_rec.error_code(j) IS NULL OR
1605 p_atp_rec.error_code(j) IS NOT NULL
1606 AND p_atp_rec.error_code(j) NOT IN ('0','150','61') THEN
1607 counter := counter + 1;
1608 --mast_table.extend;
1609 mast_table(counter).line_id := p_atp_rec.identifier(j);
1610 mast_table(counter).schedule_ship_date := p_atp_rec.ship_date(j);
1611 mast_table(counter).schedule_arrival_date := p_atp_rec.ship_date(j)
1612 +p_atp_rec.delivery_lead_time(j);
1613 mast_table(counter).ship_from_org_id := p_atp_rec.source_organization_id(j);
1614 mast_table(counter).ship_method_code := p_atp_rec.ship_method(j);
1615 END IF;
1616 END LOOP;
1617
1618 IF PG_DEBUG in ('Y', 'C') THEN
1619 msc_sch_wb.atp_debug('call_oe_api: ' || ' Count of records to pass to OE = '||counter);
1620 END IF;
1621 IF counter > 0 THEN
1622 oe_order_sch_util.Update_Results_from_backlog_wb
1623 ( mast_table
1624 , x_msg_count
1625 , x_msg_data
1626 , x_return_status);
1627
1628 IF PG_DEBUG in ('Y', 'C') THEN
1629 msc_sch_wb.atp_debug('call_oe_api: ' || ' after calling Update_Results_from_backlog_wb II '
1630 ||x_return_status||' '||x_msg_data);
1631 END IF;
1632
1633 IF x_return_status = fnd_api.G_RET_STS_SUCCESS THEN
1634 IF PG_DEBUG in ('Y', 'C') THEN
1635 msc_sch_wb.atp_debug('call_oe_api: ' || ' Committing session after call to OE_API II '||x_return_status);
1636 END IF;
1637 COMMIT;
1638 ELSE
1639 IF PG_DEBUG in ('Y', 'C') THEN
1640 msc_sch_wb.atp_debug('call_oe_api: ' || ' rolling back session after call to OE_API II '||x_return_status);
1641 END IF;
1642 x_return_status := 'E';
1643 x_msg_data := 'Unexpected error in oe_order_sch_util.Update_Results_from_backlog_wb II ';
1644 ROLLBACK;
1645 END IF;
1646 END IF;
1647
1648 EXCEPTION
1649 WHEN OTHERS THEN
1650 IF PG_DEBUG in ('Y', 'C') THEN
1651 msc_sch_wb.atp_debug(' excp in call_oe_api II '||Substr(Sqlerrm,1,100));
1652 END IF;
1653 END call_oe_api;
1654
1655 PROCEDURE update_seq(p_session_id NUMBER,
1656 p_seq_alter IN OUT NoCopy mrp_bal_utils.seq_alter,
1657 x_msg_count OUT NoCopy NUMBER,
1658 x_msg_data OUT NoCopy VARCHAR2,
1659 x_return_status OUT NoCopy VARCHAR2)
1660 IS
1661 l_old_seq NUMBER;
1662 l_new_seq NUMBER;
1663 l_max_seq NUMBER;
1664 -- The p_seq_alter was initially IN, but the form was crashing for some
1665 -- reason. Once I made it to IN OUT it started to work.
1666 TYPE curtype IS REF CURSOR;
1667 l_stmt VARCHAR2(2000);
1668 cv CurType;
1669 a NUMBER;
1670 l_num_rec NUMBER;
1671 record_firm_filter mrp_bal_utils.seq_alter;
1672 my_count NUMBER;
1673
1674 BEGIN
1675 delete from mrp_atp_schedule_temp
1676 where session_id = p_session_id
1677 and status_flag = -88;
1678
1679
1680 x_return_status := 'S';
1681 IF PG_DEBUG in ('Y', 'C') THEN
1682 msc_sch_wb.atp_debug(' entered update_seq '
1683 ||p_seq_alter.order_line_id.COUNT);
1684 END IF;
1685
1686 FOR j IN 1..p_seq_alter.order_line_id.COUNT LOOP
1687 INSERT INTO mrp_atp_schedule_temp (session_id,
1688 inventory_item_id,
1689 scenario_id,
1690 status_flag,
1691 sequence_number,
1692 order_line_id,
1693 quantity_ordered, --old_seq_num,
1694 available_quantity) --sequence_diff)
1695 VALUES (p_session_id,
1696 -88,
1697 -88,
1698 -88,
1699 p_seq_alter.seq_num(j),
1700 p_seq_alter.order_line_id(j),
1701 p_seq_alter.orig_seq_num(j),
1702 p_seq_alter.seq_diff(j));
1703 END LOOP;
1704
1705 l_stmt := ' SELECT
1706 sequence_number,
1707 order_line_id,
1708 quantity_ordered, --old_seq_num,
1709 available_quantity --sequence_diff
1710 FROM mrp_atp_schedule_temp
1711 WHERE status_flag = -88
1712 AND session_id = '||p_session_id
1713 || 'order by sequence_number';
1714 a:= 1;
1715
1716 select count(*)
1717 INTO l_num_rec
1718 from mrp_atp_schedule_temp
1719 where status_flag = -88
1720 and session_id = p_session_id;
1721
1722 IF PG_DEBUG in ('Y', 'C') THEN
1723 msc_sch_wb.atp_debug(' order_line_id.COUNT is '
1724 || p_seq_alter.order_line_id.count ||
1725 ' and number of rec in table is '
1726 || l_num_rec );
1727 END IF;
1728
1729
1730 OPEN cv FOR l_stmt;
1731 LOOP
1732 EXIT WHEN cv%NOTFOUND;
1733
1734 FETCH cv
1735 INTO p_seq_alter.seq_num(a),
1736 p_seq_alter.order_line_id(a),
1737 p_seq_alter.orig_seq_num(a),
1738 p_seq_alter.seq_diff(a);
1739
1740 IF PG_DEBUG in ('Y', 'C') THEN
1741 msc_sch_wb.atp_debug('IN the new LOOP: and a is '|| a ||
1742 ' new_seq from chopa_table ' ||p_seq_alter.seq_num(a) ||
1743 'order_line_id ' || p_seq_alter.order_line_id(a) ||
1744 ' orig_seq_num ' || p_seq_alter.orig_seq_num(a) ||
1745 ' diff ' || p_seq_alter.seq_diff(a));
1746 END IF;
1747
1748
1749 IF a < l_num_rec THEN
1750 a:= a+1;
1751 END IF;
1752 END LOOP;
1753 CLOSE cv;
1754
1755 delete from mrp_atp_schedule_temp
1756 where session_id = p_session_id
1757 and status_flag = -88;
1758
1759
1760 SELECT MAX(sequence_number)
1761 INTO l_max_seq
1762 FROM mrp_atp_schedule_temp
1763 WHERE session_id = p_session_id
1764 and status_flag = 1;
1765
1766 -- loop through the record to find firmed rows:
1767 my_count := 1;
1768 FOR b IN 1..p_seq_alter.order_line_id.COUNT LOOP
1769 IF p_seq_alter.seq_diff(b) = 0 THEN
1770 record_firm_filter.order_line_id.extend(1);
1771 record_firm_filter.seq_num.extend(1);
1772 record_firm_filter.order_line_id(my_count) := p_seq_alter.order_line_id(b);
1773 record_firm_filter.seq_num(my_count) := p_seq_alter.seq_num(b);
1774 my_count := my_count + 1;
1775 END IF;
1776
1777 END LOOP;
1778
1779 msc_sch_wb.atp_debug(' record_firm_filter is ' || record_firm_filter.order_line_id.COUNT);
1780
1781 FOR j IN 1..p_seq_alter.order_line_id.COUNT LOOP
1782 IF p_seq_alter.order_line_id(j) IS NOT NULL THEN
1783 select sequence_number, sequence_number + p_seq_alter.seq_diff(j)
1784 into l_old_seq, l_new_seq
1785 from mrp_atp_schedule_temp
1786 where
1787 order_line_id = p_seq_alter.order_line_id(j)
1788 and session_id = p_session_id
1789 and status_flag = 1;
1790 ELSE
1791 select sequence_number, sequence_number + p_seq_alter.seq_diff(j)
1792 into l_old_seq, l_new_seq
1793 from mrp_atp_schedule_temp
1794 where
1795 NVL(p_seq_alter.ship_set_id(j),p_seq_alter.arrival_set_id(j))
1796 = Decode(p_seq_alter.ship_set_id(j),NULL, arrival_set_id, ship_set_id)
1797 and session_id = p_session_id
1798 and status_flag = 1;
1799 END IF;
1800
1801 IF l_new_seq > l_max_seq THEN
1802 l_new_seq := l_max_seq;
1803 END IF;
1804
1805 IF PG_DEBUG in ('Y', 'C') THEN
1806 msc_sch_wb.atp_debug('update_seq: ' || ' old seq '||l_old_seq||' new_seq '||l_new_seq);
1807 END IF;
1808
1809
1810
1811
1812 IF p_seq_alter.seq_diff(j) <> 0 THEN
1813 -- skip since there is no change.
1814 -- Set the changed node to l_new_seq. first decode
1815 -- where clause
1816 -- select all affected nodes including the changed node
1817 -- second decode. depening on the direction of change, update the
1818 -- affected nodes.
1819 IF PG_DEBUG in ('Y', 'C') THEN
1820 msc_sch_wb.atp_debug('update_seq: ' || ' b4 update ');
1821 END IF;
1822
1823 update mrp_atp_schedule_temp mast
1824 set mast.sequence_number = p_seq_alter.seq_num(j)
1825 , last_update_date = sysdate
1826 , last_updated_by = FND_GLOBAL.USER_ID
1827 , last_update_login = FND_GLOBAL.USER_ID
1828 WHERE mast.session_id = p_session_id
1829 AND mast.order_line_id = p_seq_alter.order_line_id(j) ;
1830
1831
1832 update mrp_atp_schedule_temp mast
1833 set mast.sequence_number = Decode( Sign(p_seq_alter.seq_diff(j)),
1834 -1,mast.sequence_number + 1,
1835 1, mast.sequence_number -1)
1836 -- dsting
1837 , last_update_date = sysdate
1838 , last_updated_by = FND_GLOBAL.USER_ID
1839 , last_update_login = FND_GLOBAL.USER_ID
1840 WHERE
1841 mast.sequence_number BETWEEN
1842 Decode(Sign(p_seq_alter.seq_diff(j)),
1843 -1, p_seq_alter.seq_num(j),
1844 +1, l_old_seq) AND
1845 Decode(Sign(p_seq_alter.seq_diff(j)),
1846 -1, l_old_seq,
1847 +1, p_seq_alter.seq_num(j))
1848 AND mast.session_id = p_session_id
1849 AND mast.order_line_id <> p_seq_alter.order_line_id(j) ;
1850
1851 FOR b IN 1..record_firm_filter.order_line_id.COUNT LOOP
1852
1853 update mrp_atp_schedule_temp mast
1854 set mast.sequence_number = record_firm_filter.seq_num(b)
1855 , last_update_date = sysdate
1856 , last_updated_by = FND_GLOBAL.USER_ID
1857 , last_update_login = FND_GLOBAL.USER_ID
1858 WHERE
1859 mast.session_id = p_session_id
1860 AND mast.order_line_id = record_firm_filter.order_line_id(b) ;
1861
1862 IF PG_DEBUG in ('Y', 'C') THEN
1863 msc_sch_wb.atp_debug('IN THE LOOP FOR FIRM: ' || ' 1 after update '||
1864 ' seq num '||record_firm_filter.seq_num(b)||
1865 ' line_id ' || record_firm_filter.order_line_id(b) || ' ' ||SQL%ROWCOUNT);
1866 END IF;
1867
1868
1869 update mrp_atp_schedule_temp mast
1870 set mast.sequence_number = Decode( Sign(p_seq_alter.seq_diff(j)),
1871 -1, mast.sequence_number + 1,
1872 1, mast.sequence_number -1)
1873 where
1874 mast.sequence_number = record_firm_filter.seq_num(b)
1875 and mast.order_line_id <> record_firm_filter.order_line_id(b)
1876 and mast.session_id = p_session_id;
1877
1878 IF PG_DEBUG in ('Y', 'C') THEN
1879 msc_sch_wb.atp_debug('IN THE LOOP FOR FIRM: ' || ' 2 after update '||SQL%ROWCOUNT);
1880 END IF;
1881 END LOOP;
1882
1883
1884 IF PG_DEBUG in ('Y', 'C') THEN
1885 msc_sch_wb.atp_debug('update_seq: ' || ' after update '||SQL%ROWCOUNT);
1886 END IF;
1887 END IF;
1888
1889 END LOOP;
1890 COMMIT;
1891
1892 record_firm_filter := NULL;
1893
1894 EXCEPTION
1895 WHEN OTHERS THEN
1896 IF PG_DEBUG in ('Y', 'C') THEN
1897 msc_sch_wb.atp_debug('Excp in update_seq '||Substr(Sqlerrm,1,80));
1898 END IF;
1899 x_msg_data := 'Excp in update_seq '||Substr(Sqlerrm,1,80);
1900 x_return_status := 'E';
1901
1902 END update_seq;
1903
1904
1905
1906 END MSC_BAL_UTILS;