[Home] [Help]
PACKAGE BODY: APPS.MRP_RELEASE_SO
Source
1 PACKAGE BODY mrp_release_so AS
2 /*$Header: MRPRLSOB.pls 120.4.12020000.2 2012/08/04 00:29:00 eychen ship $ */
3
4 PROCEDURE release_so_program
5 (
6 errbuf OUT NOCOPY VARCHAR2
7 ,retcode OUT NOCOPY NUMBER,
8 p_batch_id IN NUMBER,
9 p_dblink in varchar2,
10 p_instance_id in number
11 ) IS
12
13 TYPE type_cursor IS REF CURSOR;
14 so_cursor type_cursor;
15 sql_stmt varchar2(2000);
16 x_return_status varchar2(1) :=FND_API.G_RET_STS_SUCCESS;
17 x_crm_return_status varchar2(1) :=FND_API.G_RET_STS_SUCCESS;
18 a number;
19 p_so_table OE_SCHEDULE_GRP.Sch_Tbl_Type;
20 p_crm_so_table AHL_LTP_ASCP_ORDERS_PVT.Sched_Orders_Tbl;
21
22 CURSOR line_c(p_line_id number) is
23 select header_id, top_model_line_id,org_id, item_type_code,
24 order_quantity_uom
25 from oe_order_lines_all
26 where line_id = p_line_id;
27
28
29 CURSOR mtl_primary_uom (p_item_id NUMBER, p_org_id NUMBER) IS
30 select primary_uom_code
31 from mtl_system_items
32 where inventory_item_id = p_item_id
33 and organization_id = p_org_id;
34
35 CURSOR ord_num_c(p_line_id number) IS
36 SELECT h.order_number ,
37 l.line_number||'.'|| l.shipment_number ||'.'||
38 l.option_number ||'.'|| l.component_number ||'.'||l.service_number
39 FROM oe_order_lines_all l,
40 oe_order_headers_all h
41 WHERE l.header_id = h.header_id
42 AND l.line_id = p_line_id;
43
44 p_header_id number;
45 p_top_model_line_id number;
46 std_item number;
47 p_org_id number;
48 p_uom_code varchar2(3);
49 l_primary_uom_code varchar2(3);
50 l_converted_qty number;
51 p_item_type_code varchar2(30) := null;
52 p_order_num number;
53 p_line_num varchar2(250);
54
55 CURSOR options_c (p_line_id number)is
56 select line_id,
57 header_id,
58 org_id,
59 nvl(ship_model_complete_flag,'N') ship_model_complete_flag
60 from oe_order_lines_all
61 where top_model_line_id = p_top_model_line_id
62 and line_id <> p_line_id
63 order by header_id;
64
65 options_rec options_c%ROWTYPE;
66 l_success_cnt number := 0;
67
68 p_status number;
69 p_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
70 p_need_notify boolean := false;
71 p_request_id number;
72 l_file_name varchar2(1000);
73 so_count number :=0;
74 crm_so_count number :=0;
75 orig_so_count number :=0;
76
77 Type NumTab IS TABLE of number;
78 p_model_list NumTab := new NumTab();
79 p_header_list NumTab := new NumTab();
80
81 p_line_list NumTab := new NumTab();
82 p_latest_date_index number;
83 p_isSMC_PTO boolean;
84 currIndex number;
85
86 PROCEDURE show_so_details(p_so_table OE_SCHEDULE_GRP.Sch_Tbl_Type , a number) IS
87 BEGIN
88 FND_FILE.PUT_LINE(FND_FILE.LOG,'rec ='||a);
89 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_ship_date='||to_char(p_so_table(a).schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
90 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_arrival_date='||to_char(p_so_table(a).schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
91 FND_FILE.PUT_LINE(FND_FILE.LOG,'earliest_ship_date='||to_char(p_so_table(a).earliest_ship_date,'MM-DD-RRRR HH24:MI:SS'));
92 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_id='||p_so_table(a).line_id);
93 FND_FILE.PUT_LINE(FND_FILE.LOG,'header_id='||p_so_table(a).header_id);
94 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ship_from_org_id='||p_so_table(a).Ship_from_org_id);
95 FND_FILE.PUT_LINE(FND_FILE.LOG,'org_id='||p_so_table(a).org_id);
96 FND_FILE.PUT_LINE(FND_FILE.LOG,'delivery_lead_time='||p_so_table(a).delivery_lead_time);
97 FND_FILE.PUT_LINE(FND_FILE.LOG,'shipping_method_code='||p_so_table(a).shipping_method_code);
98 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_schedule_ship_date='||to_char(p_so_table(a).orig_schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
99 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_schedule_arrival_date='||to_char(p_so_table(a).orig_schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
100 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_ship_from_org_id='||p_so_table(a).orig_ship_from_org_id);
101 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_shipping_method_code='||p_so_table(a).orig_shipping_method_code);
102 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_ordered_quantity='||p_so_table(a).orig_ordered_quantity);
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'firm_demand_flag='||p_so_table(a).firm_demand_flag);
104 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_inventory_item_id='||p_so_table(a).orig_inventory_item_id);
105 FND_FILE.PUT_LINE(FND_FILE.LOG,'inventory_item_id='||p_so_table(a).inventory_item_id);
106
107 END show_so_details;
108
109
110 FUNCTION isSOIncluded(p_line_id number) RETURN number IS
111 BEGIN
112 for i in 1..so_count loop
113 if p_line_id = p_so_table(i).line_id then
114 return i;
115 end if;
116 end loop;
117
118 return -1;
119 EXCEPTION WHEN OTHERS THEN
120 raise;
121 END isSOIncluded;
122
123 FUNCTION reGroup RETURN OE_SCHEDULE_GRP.Sch_Tbl_Type IS
124
125 p_so_table_new OE_SCHEDULE_GRP.Sch_Tbl_Type;
126 cursor so_cursor is
127 select
128 date1, --schedule_ship_date,
129 date2, --schedule_arrival_date,
130 date3, --earliest_ship_date,
131 number1, -- header_id,
132 number2, -- line_id,
133 number3, -- org_id,
134 number4, -- operating_unit,
135 number5, -- delivery_lead_time,
136 char1, -- ship_method,
137 date4, -- orig_schedule_ship_date,
138 date5, -- orig_schedule_arrival_date,
139 number6, -- orig_org_id,
140 char2, -- orig_ship_method,
141 number7, --quantity,
142 char3, -- firm_flag,
143 number8, -- orig_item_id,
144 number9 -- inventory_item_id
145 from msc_form_query
146 where query_id = p_batch_id
147 order by number1; -- header_id
148
149 a number;
150 BEGIN
151 FND_FILE.PUT_LINE(FND_FILE.LOG,' re group table by header_id');
152
153 for a in 1..p_so_table.count loop
154 insert into msc_form_query
155 (query_ID,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 CREATION_DATE,
159 CREATED_BY,
160 date1, --schedule_ship_date,
161 date2, --schedule_arrival_date,
162 date3, --earliest_ship_date,
163 number1, -- header_id,
164 number2, -- line_id,
165 number3, -- org_id,
166 number4, -- operating_unit,
167 number5, -- delivery_lead_time,
168 char1, -- ship_method,
169 date4, -- orig_schedule_ship_date,
170 date5, -- orig_schedule_arrival_date,
171 number6, -- orig_org_id,
172 char2, -- orig_ship_method,
173 number7, --quantity,
174 char3, -- firm_flag,
175 number8, -- orig_item_id,
176 number9) -- inventory_item_id)
177 values (
178 p_batch_id,
179 sysdate,
180 -1,
181 sysdate,
182 -1,
183 p_so_table(a).schedule_ship_date,
184 p_so_table(a).schedule_arrival_date,
185 p_so_table(a).earliest_ship_date,
186 p_so_table(a).header_id,
187 p_so_table(a).line_id,
188 p_so_table(a).Ship_from_org_id,
189 p_so_table(a).org_id,
190 p_so_table(a).delivery_lead_time,
191 p_so_table(a).shipping_method_code,
192 p_so_table(a).orig_schedule_ship_date,
193 p_so_table(a).orig_schedule_arrival_date,
194 p_so_table(a).orig_ship_from_org_id,
195 p_so_table(a).orig_shipping_method_code,
196 p_so_table(a).orig_ordered_quantity,
197 p_so_table(a).firm_demand_flag,
198 p_so_table(a).orig_inventory_item_id,
199 p_so_table(a).inventory_item_id);
200 end loop;
201
202 a :=1;
203 OPEN so_cursor;
204 LOOP
205 FETCH so_cursor INTO p_so_table_new(a).schedule_ship_date,
206 p_so_table_new(a).schedule_arrival_date,
207 p_so_table_new(a).earliest_ship_date,
208 p_so_table_new(a).header_id,
209 p_so_table_new(a).line_id,
210 p_so_table_new(a).Ship_from_org_id,
211 p_so_table_new(a).org_id,
212 p_so_table_new(a).delivery_lead_time,
213 p_so_table_new(a).shipping_method_code,
214 p_so_table_new(a).orig_schedule_ship_date,
215 p_so_table_new(a).orig_schedule_arrival_date,
216 p_so_table_new(a).orig_ship_from_org_id,
217 p_so_table_new(a).orig_shipping_method_code,
218 p_so_table_new(a).orig_ordered_quantity,
219 p_so_table_new(a).firm_demand_flag,
220 p_so_table_new(a).orig_inventory_item_id,
221 p_so_table_new(a).inventory_item_id;
222 EXIT WHEN so_cursor%NOTFOUND;
223 show_so_details(p_so_table_new, a);
224 a := a+1;
225 END LOOP;
226 CLOSE so_cursor;
227
228 RETURN p_so_table_new;
229
230 END reGroup;
231
232 PROCEDURE addHeaderList(p_header_id number) IS
233 i number;
234 BEGIN
235 if p_header_id is null then
236 return;
237 end if;
238 for i in 1 ..nvl(p_header_list.LAST, 0) loop
239 if p_header_id = p_header_list(i) then
240 return;
241 end if;
242 end loop;
243 i := nvl(p_header_list.LAST, 0) +1;
244 p_header_list.extend;
245 p_header_list(i) := p_header_id;
246 EXCEPTION WHEN OTHERS THEN
247 raise;
248 END addHeaderList;
249
250
251 PROCEDURE addModelList(p_model_id number) IS
252 i number;
253 BEGIN
254 i := nvl(p_model_list.LAST, 0) +1;
255 p_model_list.extend;
256 p_model_list(i) := p_model_id;
257
258 p_line_list := new NumTab();
259 p_isSMC_PTO := false;
260 p_latest_date_index := null;
261
262
263 EXCEPTION WHEN OTHERS THEN
264 raise;
265 END addModelList;
266
267 PROCEDURE addLineList(p_line_index number) IS
268 i number;
269 BEGIN
270
271 i := nvl(p_line_list.LAST, 0) +1;
272 p_line_list.extend;
273 p_line_list(i) := p_line_index;
274 FND_FILE.PUT_LINE(FND_FILE.LOG,'addLineList , p_line_index='||p_line_index||', i='||i);
275
276
277
278 EXCEPTION WHEN OTHERS THEN
279 raise;
280 END addLineList;
281
282 PROCEDURE getLatestDate(p_line_index number) IS
283 BEGIN
284 if p_latest_date_index is null or
285 p_so_table(p_line_index).schedule_ship_date >
286 p_so_table(p_latest_date_index).schedule_ship_date then
287 p_latest_date_index := p_line_index;
288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_latest_date_index ='||p_latest_date_index);
289 end if;
290 END getLatestDate;
291
292 PROCEDURE modifyPTODates(p_TopModelID number default null) IS
293 i number;
294 BEGIN
295 for a in 1 ..nvl(p_line_list.LAST, 0) loop
296 i := p_line_list(a);
297
298 if p_TopModelID is not null and p_so_table(i).line_id = p_TopModelID THEN
299 p_so_table(i).schedule_ship_date :=
300 p_so_table(p_latest_date_index).schedule_ship_date;
301 p_so_table(i).schedule_arrival_date :=
302 p_so_table(p_latest_date_index).schedule_arrival_date;
303 p_so_table(i).earliest_ship_date :=
304 p_so_table(p_latest_date_index).earliest_ship_date;
305 FND_FILE.PUT_LINE(FND_FILE.LOG,' change the dates for PTO model line '||p_so_table(i).line_id ||' to use the dates from rec '||p_latest_date_index);
306
307 EXIT;
308 Elsif p_TopModelID is null THEN
309 p_so_table(i).schedule_ship_date :=
310 p_so_table(p_latest_date_index).schedule_ship_date;
311 p_so_table(i).schedule_arrival_date :=
312 p_so_table(p_latest_date_index).schedule_arrival_date;
313 p_so_table(i).earliest_ship_date :=
314 p_so_table(p_latest_date_index).earliest_ship_date;
315 FND_FILE.PUT_LINE(FND_FILE.LOG,' change the dates for PTO model line '||p_so_table(i).line_id ||' to use the dates from rec '||p_latest_date_index);
316 end if;
317 -- show_so_details(p_so_table,i);
318 end loop;
319 END modifyPTODates;
320
321 FUNCTION isModelIncluded(p_model_id number) RETURN boolean IS
322 BEGIN
323 for i in 1 ..nvl(p_model_list.LAST, 0) loop
324 if p_model_id = p_model_list(i) then
325 return true;
326 end if;
327 end loop;
328
329 return false;
330 EXCEPTION WHEN OTHERS THEN
331 raise;
332 END isModelIncluded;
333
334 begin
335
336
337
338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'batch_id='||p_batch_id||', instance_id='||p_instance_id||', dblink='||p_dblink);
339
340 retcode :=0;
341
342 -- release so with source_type = null thru oe package
343 sql_stmt:=
344 ' select schedule_ship_date,'||
345 ' schedule_arrival_date,'||
346 ' earliest_ship_date, '||
347 ' header_id,'||
348 ' line_id,'||
349 ' org_id,'||
350 ' operating_unit,'||
351 ' delivery_lead_time,'||
352 ' ship_method, '||
353 ' orig_schedule_ship_date,'||
354 ' orig_schedule_arrival_date,'||
355 ' orig_org_id,'||
356 ' orig_ship_method, '||
357 ' quantity, '||
358 ' decode(firm_flag,1,''Y'',''N''), '||
359 ' orig_item_id, '||
360 ' inventory_item_id '||
361 ' from msc_sales_order_interface'||p_dblink||
362 ' where sr_instance_id = : p_instance_id '||
363 ' and source_type is null '||
364 ' and batch_id = :p_batch_id ';
365
366 a :=1;
367 OPEN so_cursor FOR sql_stmt using p_instance_id, p_batch_id;
368 LOOP
369 FETCH so_cursor INTO p_so_table(a).schedule_ship_date,
370 p_so_table(a).schedule_arrival_date,
371 p_so_table(a).earliest_ship_date,
372 p_so_table(a).header_id,
373 p_so_table(a).line_id,
374 p_so_table(a).Ship_from_org_id,
375 p_so_table(a).org_id,
376 p_so_table(a).delivery_lead_time,
377 p_so_table(a).shipping_method_code,
378 p_so_table(a).orig_schedule_ship_date,
379 p_so_table(a).orig_schedule_arrival_date,
380 p_so_table(a).orig_ship_from_org_id,
381 p_so_table(a).orig_shipping_method_code,
382 p_so_table(a).orig_ordered_quantity,
383 p_so_table(a).firm_demand_flag,
384 p_so_table(a).orig_inventory_item_id,
385 p_so_table(a).inventory_item_id;
386 EXIT WHEN so_cursor%NOTFOUND;
387 so_count := so_count +1;
388 show_so_details(p_so_table,a);
389 a := a+1;
390 END LOOP;
391 CLOSE so_cursor;
392
393 orig_so_count := so_count;
394
395 FND_FILE.PUT_LINE(FND_FILE.LOG,'orig_so_count='||orig_so_count);
396
397 FOR i in 1..orig_so_count LOOP
398
399 OPEN line_c(p_so_table(i).line_id);
400 FETCH line_c INTO p_header_id, p_top_model_line_id,p_org_id,
401 p_item_type_code, p_uom_code;
402 CLOSE line_c;
403 if p_so_table(i).header_id is null then
404 p_so_table(i).header_id := p_header_id;
405 FND_FILE.PUT_LINE(FND_FILE.LOG,'header_id for line_id '||p_so_table(i).line_id||' is '||p_so_table(i).header_id);
406 end if;
407
408 addHeaderList(p_so_table(i).header_id);
409
410 if p_so_table(i).org_id <> p_org_id then
411 -- bug 7537394, get operating_unit from oe_order_lines_all
412 FND_FILE.PUT_LINE(FND_FILE.LOG,'org_id for line_id = '||p_so_table(i).line_id||' is changed to '||p_org_id ||' from '||p_so_table(i).org_id);
413 p_so_table(i).org_id := p_org_id;
414 end if;
415
416 -- bug#9339922
417 OPEN mtl_primary_uom(p_so_table(i).inventory_item_id,
418 p_so_table(i).orig_ship_from_org_id);
419 FETCH mtl_primary_uom INTO l_primary_uom_code;
420 CLOSE mtl_primary_uom;
421
422 IF p_uom_code <> l_primary_uom_code THEN
423 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UOM code is different : OM UOM = ' ||
424 p_uom_code || ' mtl_system_items UOM, primary UOM = '|| l_primary_uom_code);
425 -- convert the qty from ascp workbench back to OM qty
426 l_converted_qty :=
427 inv_convert.inv_um_convert(p_so_table(i).inventory_item_id,
428 6,
429 p_so_table(i).orig_ordered_quantity,
430 l_primary_uom_code,
431 p_uom_code,
432 null, null);
433 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Converted qty = '||
434 l_converted_qty|| ' Qty from ASCP wb = ' || p_so_table(i).orig_ordered_quantity);
435 p_so_table(i).orig_ordered_quantity := l_converted_qty;
436
437 END IF;
438
439 if p_top_model_line_id is not null and
440 p_item_type_code <> 'CONFIG' then
441 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_id='||p_so_table(i).line_id);
442 FND_FILE.PUT_LINE(FND_FILE.LOG,' pto/ato exists, top_model_line_id='||p_top_model_line_id);
443 if isModelIncluded(p_top_model_line_id) then
444 FND_FILE.PUT_LINE(FND_FILE.LOG,' top model is included already');
445 p_isSMC_PTO := null;
446 else -- new Model
447 addModelList(p_top_model_line_id);
448
449 -- find the related pto/ato and change their dates
450 OPEN options_c(p_so_table(i).line_id);
451 LOOP
452 FETCH options_c INTO options_rec;
453 EXIT WHEN options_c%NOTFOUND;
454
455 if options_rec.line_id = p_top_model_line_id then
456
457 addLineList(i);
458 getLatestDate(i);
459
460 if nvl(options_rec.ship_model_complete_flag,'N') = 'N' then
461 FND_FILE.PUT_LINE(FND_FILE.LOG,' this is non-SMC PTO top model line ='||options_rec.line_id);
462 p_isSMC_PTO := FALSE;
463 else
464 FND_FILE.PUT_LINE(FND_FILE.LOG,' this is SMC PTO top model line ='||options_rec.line_id);
465 p_isSMC_PTO := true;
466
467 end if;
468
469 end if;
470
471 currIndex := isSOIncluded(options_rec.line_id);
472 FND_FILE.PUT_LINE(FND_FILE.LOG,'currIndex='||currIndex);
473 if currIndex <> -1 then
474
475 FND_FILE.PUT_LINE(FND_FILE.LOG,' line is already included, line_id='||options_rec.line_id);
476 addLineList(currIndex);
477 getLatestDate(currIndex);
478 else -- need to include
479 addLineList(a);
480
481 p_so_table(a).header_id := options_rec.header_id;
482 p_so_table(a).line_id := options_rec.line_id;
483 p_so_table(a).org_id := options_rec.org_id;
484 p_so_table(a).schedule_ship_date :=
485 p_so_table(i).schedule_ship_date;
486 p_so_table(a).schedule_arrival_date :=
487 p_so_table(i).schedule_arrival_date;
488 p_so_table(a).earliest_ship_date :=
489 p_so_table(i).earliest_ship_date;
490 so_count := so_count +1;
491
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG,' include line_id='||p_so_table(a).line_id);
494 show_so_details(p_so_table,a);
495 a := a+1;
496 end if;
497 END LOOP;
498 CLOSE options_c;
499
500 -- 14356932, all SMC PTO line should use the latest date of the child lines
501 if p_isSMC_PTO then
502 FND_FILE.PUT_LINE(FND_FILE.LOG,'modify PTODates');
503 modifyPTODates();
504 elsif not p_isSMC_PTO then
505 FND_FILE.PUT_LINE(FND_FILE.LOG,'modify PTODate for TopModel');
506 modifyPTODates(p_top_model_line_id);
507 end if;
508
509 end if; -- new Model
510 FND_FILE.PUT_LINE(FND_FILE.LOG,' done with pto/ato data for top_model_line_id='||p_top_model_line_id);
511 end if; -- p_top_model_line_id is not null
512
513 END LOOP;
514
515
516
517 -- release so with source_type =100 thru crm package
518 sql_stmt:=
519 ' select schedule_ship_date,'||
520 ' schedule_arrival_date,'||
521 ' earliest_ship_date, '||
522 ' header_id,'||
523 ' line_id,'||
524 ' org_id,'||
525 ' quantity '||
526 ' from msc_sales_order_interface'||p_dblink||
527 ' where sr_instance_id = : p_instance_id '||
528 ' and source_type =100 '||
529 ' and batch_id = :p_batch_id ';
530
531 a :=1;
532 OPEN so_cursor FOR sql_stmt using p_instance_id, p_batch_id;
533 LOOP
534 FETCH so_cursor INTO p_crm_so_table(a).schedule_ship_date,
535 p_crm_so_table(a).schedule_arrival_date,
536 p_crm_so_table(a).earliest_ship_date,
537 p_crm_so_table(a).header_id,
538 p_crm_so_table(a).order_line_id,
539 p_crm_so_table(a).org_id,
540 p_crm_so_table(a).quantity_by_due_date;
541 EXIT WHEN so_cursor%NOTFOUND;
542 crm_so_count := crm_so_count +1;
543 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_ship_date='||to_char(p_crm_so_table(a).schedule_ship_date,'MM-DD-RRRR HH24:MI:SS'));
544 FND_FILE.PUT_LINE(FND_FILE.LOG,'schedule_arrival_date='||to_char(p_crm_so_table(a).schedule_arrival_date,'MM-DD-RRRR HH24:MI:SS'));
545 FND_FILE.PUT_LINE(FND_FILE.LOG,'earliest_ship_date='||to_char(p_crm_so_table(a).earliest_ship_date,'MM-DD-RRRR HH24:MI:SS'));
546 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_id='||p_crm_so_table(a).order_line_id);
547 FND_FILE.PUT_LINE(FND_FILE.LOG,'org_id='||p_crm_so_table(a).org_id);
548 FND_FILE.PUT_LINE(FND_FILE.LOG,'qty='||p_crm_so_table(a).quantity_by_due_date);
549 a := a+1;
550 END LOOP;
551 CLOSE so_cursor;
552
553 if p_dblink is not null and p_dblink <> ' ' then
554 commit;
555 begin
556 sql_stmt:= ' alter session close database link '||
557 ltrim(p_dblink,'@');
558 execute immediate sql_stmt;
559 exception when others then
560 null;
561 end;
562 end if;
563
564
565
566 IF p_so_table.count > 0 then
567 -- group by header id, bug7335768
568 if -- orig_so_count <> so_count and
569 p_header_list.count > 1 then
570 p_so_table := reGroup;
571 end if;
572
573 mo_global.init('ONT');
574 OE_SCHEDULE_GRP.Update_Scheduling_Results(
575 p_so_table,
576 p_batch_id,
577 x_return_status);
578 commit;
579 END IF;
580
581
582
583 IF p_crm_so_table.count >0 then
584 AHL_LTP_ASCP_ORDERS_PVT.Update_Scheduling_Results(
585 1.0,
586 FND_API.g_false,
587 FND_API.g_false,
588 FND_API.g_valid_level_full,
589 p_crm_so_table,
590 x_crm_return_status);
591 commit;
592 END IF;
593
594
595
596 -- send workflow notification for the failed so
597 for a in 1..so_count loop
598 if nvl(p_so_table(a).x_return_status, FND_API.G_RET_STS_ERROR) <>
599 FND_API.G_RET_STS_SUCCESS or
600 p_so_table(a).x_override_atp_date_code = 'Y' then
601 sql_stmt:=
602 ' update msc_sales_order_interface'||p_dblink||
603 ' set return_status = :p_status '||
604 ' where sr_instance_id = :p_instance_id '||
605 ' and batch_id = :p_batch_id '||
606 ' and line_id = :p_line_id ';
607 if nvl(p_so_table(a).x_return_status, FND_API.G_RET_STS_ERROR) <>
608 FND_API.G_RET_STS_SUCCESS then
609 --14356932, show orderNum/LineNum in the request log for failed lines
610 p_order_num := null;
611 p_line_num := null;
612 OPEN ord_num_c(p_so_table(a).line_id);
613 FETCH ord_num_c INTO p_order_num, p_line_num;
614 CLOSE ord_num_c;
615 FND_FILE.PUT_LINE(FND_FILE.LOG,'update fails for line id '||p_so_table(a).line_id||', Order Number: '||p_order_num||', Line Number: '||p_line_num||', om return status ='||p_so_table(a).x_return_status);
616 p_status := 2; -- fails
617 retcode :=2;
618 else
619 FND_FILE.PUT_LINE(FND_FILE.LOG,'atp override for line id'||p_so_table(a).line_id);
620 p_status := 1; -- override
621 end if;
622 EXECUTE IMMEDIATE sql_stmt using p_status, p_instance_id, p_batch_id,
623 p_so_table(a).line_id;
624 p_need_notify := true;
625 else
626 FND_FILE.PUT_LINE(FND_FILE.LOG,'update scceeds for line id'||p_so_table(a).line_id);
627 end if;
628
629 end loop;
630
631 -- 14356932
632 if (x_return_status = 'W') then
633 retcode := 1; -- warning
634 end if;
635
636 IF nvl(x_crm_return_status, FND_API.G_RET_STS_ERROR) <>
637 FND_API.G_RET_STS_SUCCESS THEN
638 p_status := 2; -- fails
639 retcode :=2;
640 sql_stmt:=
641 ' update msc_sales_order_interface'||p_dblink||
642 ' set return_status = :p_status '||
643 ' where sr_instance_id = :p_instance_id '||
644 ' and batch_id = :p_batch_id '||
645 ' and source_type = 100 ';
646 EXECUTE IMMEDIATE sql_stmt using p_status, p_instance_id, p_batch_id;
647 commit;
648 for a in 1..crm_so_count loop
649 FND_FILE.PUT_LINE(FND_FILE.LOG,'update fails for line id '||p_crm_so_table(a).order_line_id);
650 end loop;
651 ELSE
652 for a in 1..crm_so_count loop
653 FND_FILE.PUT_LINE(FND_FILE.LOG,'update successfully for line id '||p_crm_so_table(a).order_line_id);
654 end loop;
655 END IF;
656
657 sql_stmt:=
658 ' delete from msc_sales_order_interface'||p_dblink||
659 ' where sr_instance_id = :p_instance_id '||
660 ' and batch_id = :p_batch_id '||
661 ' and return_status is null ';
662 EXECUTE IMMEDIATE sql_stmt using p_instance_id, p_batch_id;
663 commit;
664
665 if p_need_notify then
666 sql_stmt:=
667 'BEGIN'
668 ||' msc_rel_wf.so_release_workflow_program'||p_dblink||'('
669 ||' :p_batch_id, '
670 ||' :p_instance_id,'
671 ||' :p_planner,'
672 ||' :p_request_id);'
673 ||' END;';
674 EXECUTE IMMEDIATE sql_stmt using in p_batch_id,in p_instance_id,
675 in p_user_name, out p_request_id ;
676 commit;
677
678 FND_FILE.PUT_LINE(FND_FILE.LOG,'send workflow notification to planners, request id='||p_request_id);
679 end if;
680 exception when others then
681 retcode :=2;
682 raise;
683
684 END release_so_program;
685
686 end mrp_release_so;