[Home] [Help]
PACKAGE BODY: APPS.MRP_RESCHEDULE_PO
Source
1 PACKAGE BODY mrp_reschedule_po AS
2 /*$Header: MRPRSPOB.pls 120.8 2010/07/08 23:09:04 cnazarma ship $ */
3
4 Type CharTab is TABLE of varchar2(2);
5 Type LongCharTab is TABLE of varchar2(240);
6 Type NumTab IS TABLE of number;
7 Type DateTab IS TABLE of DATE;
8
9 PROCEDURE reschedule_po_program
10 (
11 errbuf OUT NOCOPY VARCHAR2
12 ,retcode OUT NOCOPY NUMBER,
13 p_old_need_by_date IN DATE,
14 p_new_need_by_date IN DATE,
15 p_po_header_id IN NUMBER,
16 p_po_line_id IN NUMBER,
17 p_po_number IN VARCHAR2,
18 p_qty IN NUMBER
19 ) IS
20
21 p_instance_id number;
22 p_dblink varchar2(128);
23 sql_stmt varchar2(2000);
24 p_result boolean;
25
26 v_old_need_by_date dateTab;
27 v_new_need_by_date dateTab;
28 v_po_header_id numTab;
29 v_po_line_id numTab;
30 --v_po_line_id2 numTab;
31 v_po_line_location_id numTab;
32 v_po_number LongCharTab;
33 v_po_location_id number;
34 v_action numTab;
35
36
37 TYPE type_cursor IS REF CURSOR;
38 po_cursor type_cursor;
39 p_batch_id number;
40
41 first_left_pare_pos number;
42 first_right_pare_pos number;
43 second_left_pare_pos number;
44 second_right_pare_pos number;
45 third_left_pare_pos number;
46 third_right_pare_pos number;
47
48 l_doc_type VARCHAR2(30);
49 l_doc_subtype VARCHAR2(30);
50 p_allow_release number;
51 p_instance_code varchar2(10);
52 v_release_num number;
53 l_derived_status number := null;
54
55 cursor check_rel_flag is
56 select ALLOW_RELEASE_FLAG, instance_code
57 from MRP_AP_APPS_INSTANCES_ALL
58 where instance_id = p_instance_id
59 and nvl(A2M_DBLINK, '-1') = p_dblink;
60
61 X_need_by_dates_old po_tbl_date := po_tbl_date();
62 X_need_by_dates po_tbl_date := po_tbl_date();
63 X_po_line_ids po_tbl_number := po_tbl_number();
64 X_shipment_nums po_tbl_number := po_tbl_number();
65 X_estimated_pickup_dates po_tbl_date := po_tbl_date();
66 X_ship_methods po_tbl_varchar30 := po_tbl_varchar30();
67 a number :=0;
68 p_result_output po_tbl_number := po_tbl_number();
69
70 last_po_number varchar2(250);
71 last_po_header_id number;
72 p_result_success boolean := false;
73 p_result_error boolean := false;
74 p_result_warning boolean := false;
75
76 BEGIN
77
78 FND_FILE.PUT_LINE(FND_FILE.LOG,'starting ...');
79 p_batch_id := p_po_header_id;
80 p_instance_id := p_po_line_id;
81 p_dblink := p_po_number;
82
83 retcode :=0;
84 p_allow_release :=0;
85
86 OPEN check_rel_flag;
87 FETCH check_rel_flag INTO p_allow_release, p_instance_code;
88 CLOSE check_rel_flag;
89
90 if p_dblink = '-1' then
91 p_dblink := ' ';
92 else
93 p_dblink := '@'||p_dblink;
94 end if;
95
96 FND_FILE.PUT_LINE(FND_FILE.LOG, 'instance_code:'||p_instance_code||' dblink: '|| p_po_number ||', instance_id='||p_instance_id||', batch_id='||p_batch_id||',allow_release_flag ='||p_allow_release);
97
98
99 if p_allow_release = 1 THEN
100 mo_global.INIT('PO');
101 sql_stmt:=
102 ' select old_need_by_date,'||
103 ' new_need_by_date,'||
104 ' po_header_id,'||
105 ' po_line_id,'||
106 ' po_number,'||
107 ' action'||
108 ' from msc_purchase_order_interface'||p_dblink||
109 ' where sr_instance_id = '||p_instance_id||
110 ' and batch_id ='||p_batch_id ||
111 ' order by action, po_number ';
112
113 OPEN po_cursor FOR sql_stmt;
114 FETCH po_cursor BULK COLLECT INTO v_old_need_by_date,
115 v_new_need_by_date,
116 v_po_header_id,
117 v_po_line_id,
118 v_po_number,
119 v_action;
120 CLOSE po_cursor;
121
122 FOR i in 1..nvl(v_po_line_id.LAST, 0) LOOP
123
124 /* in R12, order number(release number)(line number)(shipment number),
125 but release number could be empty,
126 in 11.5.10 and prior,
127 order number(release number)(shipment number) -- blanket PO or
128 order number(shipment number) -- standard PO */
129
130 first_left_pare_pos := instr(v_po_number(i), '(');
131 second_left_pare_pos := instr(v_po_number(i), '(',1,2);
132 third_left_pare_pos := instr(v_po_number(i), '(',1,3);
133 if third_left_pare_pos > 0 then -- in R12
134 first_right_pare_pos := instr(v_po_number(i), ')');
135 third_right_pare_pos := instr(v_po_number(i), ')', 1,3);
136 v_po_location_id := substr(v_po_number(i),
137 third_left_pare_pos+1,third_right_pare_pos -
138 third_left_pare_pos -1);
139
140 begin
141 v_release_num := substr(v_po_number(i),
142 first_left_pare_pos+1,first_right_pare_pos -
143 first_left_pare_pos -1);
144 exception when others then
145 v_release_num :=null;
146 end;
147
148 if v_release_num is null then
149 l_doc_type := 'PO';
150 l_doc_subtype := 'STANDARD';
151 v_po_number(i) := substr(v_po_number(i), 1,first_left_pare_pos -1);
152 else
153 l_doc_type := 'RELEASE';
154 l_doc_subtype := 'BLANKET';
155 v_po_number(i) := substr(v_po_number(i),1,second_left_pare_pos -1);
156 end if;
157 else -- in 11.5.10 or prior
158 -- -------------------------------------------------
159 -- Bug#4013684 - 16-dec-2004.
160 -- l_doc_type and l_doc_subtype will be derived here
161 -- and will be passed to 'Cancel PO' api.
162 -- -------------------------------------------------
163 if first_left_pare_pos > 0 then -- should not be 0, just in case
164 first_right_pare_pos := instr(v_po_number(i), ')');
165 if second_left_pare_pos = 0 then -- standard po
166 v_po_location_id := substr(v_po_number(i),
167 first_left_pare_pos+1,first_right_pare_pos -
168 first_left_pare_pos -1);
169 v_po_number(i) := substr(v_po_number(i), 1,first_left_pare_pos -1);
170 l_doc_type := 'PO';
171 l_doc_subtype := 'STANDARD';
172 else -- blanket po
173 second_right_pare_pos := instr(v_po_number(i), ')', 1,2);
174 v_po_location_id := substr(v_po_number(i),
175 second_left_pare_pos+1,second_right_pare_pos -
176 second_left_pare_pos -1);
177 v_po_number(i) := substr(v_po_number(i),1,second_left_pare_pos -1);
178 l_doc_type := 'RELEASE';
179 l_doc_subtype := 'BLANKET';
180 end if;
181 end if;
182 end if; -- if third_left_pare_pos > 0
183
184 -- cancel the PO if action = 2 else reschedule it.
185 if v_action(i) = 2 then
186 mrp_cancel_po.cancel_po_program(v_po_header_id(i), v_po_line_id(i),
187 v_po_number(i), v_po_location_id,
188 l_doc_type , l_doc_subtype);
189 else
190 --5137694, for standard po, group by po_header_id,
191 -- for blanket po, group by po_header_id and shipment number
192
193 if i <> nvl(v_po_line_id.FIRST, 0) and
194 v_po_number(i) <> v_po_number(i-1) then
195 --call PO api
196 p_result := reschedule_po(X_need_by_dates_old,
197 X_need_by_dates,
198 v_po_header_id(i-1),
199 X_po_line_ids,
200 v_po_number(i-1),
201 X_shipment_nums,
202 X_estimated_pickup_dates,
203 X_ship_methods,
204 l_derived_status);
205 if not(p_result) then
206 retcode :=2;
207 end if;
208
209 p_result_output.extend;
210 p_result_output(p_result_output.last):= set_result(p_result, l_derived_status);
211
212 -- reset po table
213 a := 0;
214 x_need_by_dates_old.delete;
215 X_need_by_dates.delete;
216 X_po_line_ids.delete;
217 X_shipment_nums.delete;
218 x_estimated_pickup_dates.delete;
219 x_ship_methods.delete;
220 end if; -- if i <> nvl(v_po_line_id.FIRST, 0) and
221 -- init po tables
222 a := a+1;
223 x_need_by_dates_old.extend;
224 x_need_by_dates_old(a) := v_old_need_by_date(i);
225 X_need_by_dates.extend;
226 X_need_by_dates(a) := v_new_need_by_date(i);
227 X_po_line_ids.extend;
228 X_po_line_ids(a) := v_po_line_id(i);
229 X_shipment_nums.extend;
230 X_shipment_nums(a) := v_po_location_id;
231 x_estimated_pickup_dates.extend;
232 x_estimated_pickup_dates(a) := null;
233 x_ship_methods.extend;
234 x_ship_methods(a) := null;
235 last_po_header_id := v_po_header_id(i);
236 last_po_number := v_po_number(i);
237 end if; -- if v_action = 2 then
238
239 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
240 END LOOP; -- FOR i in 1..nvl(v_po_line_id.LAST, 0) LOOP
241
242 if a <> 0 then
243 p_result := reschedule_po(X_need_by_dates_old,
244 X_need_by_dates,
245 last_po_header_id,
246 X_po_line_ids,
247 last_po_number,
248 X_shipment_nums,
249 X_estimated_pickup_dates,
250 X_ship_methods,
251 l_derived_status);
252 if not(p_result) then
253 retcode :=2;
254 end if;
255
256 p_result_output.extend;
257 p_result_output(p_result_output.last):= set_result(p_result, l_derived_status);
258
259 end if; -- if a <> 0
260
261 -- set the retcode correctly to ERROR, WARNING or SUCCESS
262 for b in 1..nvl(p_result_output.last,0) loop
263 if (p_result_output(b)= 0) then
264 p_result_success := true;
265 elsif (p_result_output(b) = 1) then
266 p_result_warning := true;
267 elsif (p_result_output(b) = 2) then
268 p_result_error := true;
269 end if;
270 END LOOP;
271
272
273
274 if (p_result_success) AND not(p_result_warning) AND not(p_result_error) then
275 retcode := 0;
276 elsif (p_result_warning) OR
277 ( p_result_success AND p_result_error) then
278 retcode := 1; -- warning
279 elsif not(p_result_success)
280 AND not(p_result_warning)
281 and (p_result_error) then
282 retcode := 2; -- error
283 end if;
284
285 else -- not allow release
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'instance '||p_instance_code||' does not allow release ');
287 end if; -- if p_allow_release = 1 then
288 sql_stmt:= ' delete from msc_purchase_order_interface'||p_dblink||
289 ' where sr_instance_id = :p_instance_id '||
290 ' and batch_id = :p_batch_id ';
291
292 EXECUTE IMMEDIATE sql_stmt using p_instance_id, p_batch_id;
293
294 commit;
295
296 exception
297 when others then
298 retcode :=2;
299 raise;
300 END reschedule_po_program;
301
302
303 FUNCTION set_result(p_result boolean,
304 l_derived_output number)
305 return number IS
306 BEGIN
307 if(p_result) then
308 return l_derived_output; -- success
309 elsif not(p_result) then
310 return 2;
311 end if;
312
313 END set_result;
314
315
316 FUNCTION get_request_status(
317 x_error_messages po_tbl_varchar2000)
318 return number IS
319 /*
320 * this call will be executed only for p_result = FALSE
321 * if x_error_message contains all nulls -> return 0 , means ERROR
322 * if x_error_message contains some nulls -> return 1, means WARNING
323 * */
324 SUCCESS number := 0;
325 WARNING number := 1;
326 my_return number:= null;
327 BEGIN
328
329 my_return := SUCCESS;
330
331 for i in 1..x_error_messages.count LOOP
332 if (x_error_messages(i) is not null) then
333 my_return:= WARNING;
334 end if;
335 END LOOP;
336
337
338 return my_return;
339
340 END get_request_status;
341
342
343
344
345 FUNCTION reschedule_po( X_old_need_by_dates po_tbl_date,
346 X_new_need_by_dates po_tbl_date,
347 X_po_header_id number,
348 X_po_line_ids po_tbl_number,
349 X_po_number varchar2,
350 X_shipment_nums po_tbl_number,
351 X_estimated_pickup_dates po_tbl_date,
352 X_ship_methods po_tbl_varchar30,
353 l_derived_status IN OUT NOCOPY number)
354 return boolean IS
355
356 p_result boolean;
357 v_promised_date dateTab;
358 v_need_by_date dateTab;
359 v_rec NumTab;
360 v_date_changed boolean;
361 v_date date;
362 x_error_messages po_tbl_varchar2000 := po_tbl_varchar2000();
363 p_show_msg varchar2(3) := nvl(FND_PROFILE.Value('MRP_DEBUG'),'N');
364
365 cursor po_cur(v_line_id number, v_header_id number, v_ship_num number) is
366 select nvl(poll.promised_date,poll.need_by_date),1
367 from po_line_locations_all poll
368 where poll.po_line_id = v_line_id
369 and poll.po_header_id = v_header_id
370 and poll.shipment_num = nvl(v_ship_num,poll.shipment_num);
371 v_old_need_by_dates po_tbl_date := po_tbl_date();
372
373 CURSOR cur_org(p_po_header_id IN number) IS
374 SELECT org_id
375 FROM po_headers_all
376 WHERE po_header_id = p_po_header_id;
377
378 l_document_org_id NUMBER;
379 l_access_mode VARCHAR2(1);
380 l_current_org_id NUMBER;
381 l_error_message varchar2(1000);
382 PROCEDURE show_po_details IS
383 cursor po_details (p_line_id number) is
384 select line_num
385 from po_lines_all
386 where po_line_id = p_line_id;
387
388 l_po_line_number number;
389
390 BEGIN
391 for i in 1..nvl(X_po_line_ids.last, 0) loop
392 open po_details(x_po_line_ids(i));
393 fetch po_details into l_po_line_number;
394 close po_details;
395 FND_FILE.PUT_LINE(FND_FILE.LOG, '***** PO line details *****');
396 FND_FILE.PUT_LINE(FND_FILE.LOG,'old date in planner workbench: '||
397 to_char(x_old_need_by_dates(i),'MM/DD/RR HH24:MI:SS'));
398 FND_FILE.PUT_LINE(FND_FILE.LOG,'new date: '||
399 to_char(x_new_need_by_dates(i),'MM/DD/RR HH24:MI:SS'));
400 FND_FILE.PUT_LINE(FND_FILE.LOG,'header: '||x_po_header_id);
401 FND_FILE.PUT_LINE(FND_FILE.LOG,'line: '||x_po_line_ids(i));
402 FND_FILE.PUT_LINE(FND_FILE.LOG,'line number : ' || l_po_line_number);
403 FND_FILE.PUT_LINE(FND_FILE.LOG,'po number: '||x_po_number);
404 FND_FILE.PUT_LINE(FND_FILE.LOG,'shipment no: '||X_shipment_nums(i));
405 end loop;
406 END show_po_details;
407
408 BEGIN
409
410 -- p_show_msg := 'Y';
411
412 begin
413
414 OPEN cur_org(x_po_header_id);
415 FETCH cur_org INTO l_document_org_id;
416 CLOSE cur_org;
417
418 l_access_mode := mo_global.Get_access_mode();
419 l_current_org_id := mo_global.get_current_org_id();
420
421 mo_global.set_policy_context('S',l_document_org_id);
422
423
424 p_result :=po_reschedule_pkg.reschedule
425 (X_old_need_by_dates,
426 X_new_need_by_dates,
427 X_po_header_id,
428 X_po_line_ids,
429 X_po_number,
430 X_shipment_nums,
431 X_estimated_pickup_dates,
432 X_ship_methods,
433 x_error_messages);
434 Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode,
435 p_org_id => l_current_org_id);
436 exception when others then
437 FND_FILE.PUT_LINE(FND_FILE.LOG,' error while calling po_reschedule_pkg.reschedule '||sqlerrm);
438 Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode,
439 p_org_id => l_current_org_id);
440 show_po_details;
441 end;
442
443 if p_result is null then
444 FND_FILE.PUT_LINE(FND_FILE.LOG, 'the call to po_reschedule_pkg.reschedule return null value');
445 end if;
446
447 p_result := nvl(p_result, false);
448
449 if not(p_result) then -- p_result is false
450 -- 5030537, it might fail because our PO does not carry seconds,
451 v_date_changed := false;
452 for i in 1..nvl(X_po_line_ids.last, 0) loop
453 if v_rec is not null then
454 v_promised_date.delete;
455 v_rec.delete;
456 end if;
457 v_old_need_by_dates.extend;
458 v_old_need_by_dates(i) := x_old_need_by_dates(i);
459 OPEN po_cur(x_po_line_ids(i), X_po_header_id,X_shipment_nums(i));
460 FETCH po_cur BULK COLLECT INTO
461 v_promised_date, v_rec;
462 CLOSE po_cur;
463
464 For a in 1..nvl(v_rec.last,0) LOOP
465 v_date := v_promised_date(a);
466 if to_date(to_char(x_old_need_by_dates(i), 'MM/DD/RRRR HH24:MI'),
467 'MM/DD/RRRR HH24:MI') =
468 to_date(to_char(v_date, 'MM/DD/RRRR HH24:MI'),
469 'MM/DD/RRRR HH24:MI') then
470 if x_old_need_by_dates(i) <> v_date then
471 FND_FILE.PUT_LINE(FND_FILE.LOG,' call reschedule again by modifying old_need_by_date from '||to_char(x_old_need_by_dates(i), 'MM/DD/RRRR HH24:MI:SS')||
472 ' to '||to_char(v_date, 'MM/DD/RRRR HH24:MI:SS'));
473 v_old_need_by_dates(i) := v_date;
474 v_date_changed := true;
475 end if;
476 exit;
477 end if;
478 END LOOP; -- For a in 1..nvl(v_rec.last,0) LOOP
479 end loop; -- for i in 1..nvl(X_po_line_ids.last, 0) loop
480
481 if v_date_changed then
482 -- call reschedule again with updated old_need_by_date
483 x_error_messages := null;
484 p_result :=po_reschedule_pkg.reschedule
485 (v_old_need_by_dates,
486 X_new_need_by_dates,
487 X_po_header_id,
488 X_po_line_ids,
489 X_po_number,
490 X_shipment_nums,
491 X_estimated_pickup_dates,
492 X_ship_methods,
493 x_error_messages);
494 end if; -- if v_date_changed then
495 end if; -- if not(p_result) then
496
497 p_result := nvl(p_result, false);
498
499 if p_result then
500 -- set l_derived_status correctly.
501 -- PO can return success , even if one of the line failed
502 l_derived_status := get_request_status(x_error_messages);
503 if p_show_msg = 'Y' then
504 FND_FILE.PUT_LINE(FND_FILE.LOG,'ASCP call to PO API was successful for PO header id ' || X_po_header_id );
505 end if;
506
507 for i in 1..x_error_messages.count loop
508 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error returned from PO api : ' || x_error_messages(i));
509 end loop;
510 else
511 l_derived_status := 2;
512 if p_show_msg = 'Y' then
513 FND_FILE.PUT_LINE(FND_FILE.LOG,'ASCP call to PO API encountered an error for PO header ID' || X_po_header_id) ;
514 end if;
515
516 for i in 1..x_error_messages.count loop
517 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error returned from PO api : ' || x_error_messages(i));
518 end loop;
519
520 end if;
521
522 if (p_result and p_show_msg = 'Y') or
523 not(p_result) then
524 show_po_details;
525 end if;
526
527 return p_result;
528 exception when others then
529 FND_FILE.PUT_LINE(FND_FILE.LOG,'error in reschedule_po, error is: '||sqlerrm);
530 show_po_details;
531 l_derived_status := 2;
532 return false;
533 END reschedule_po;
534
535 END mrp_reschedule_po;