eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MRP_RESCHEDULE_PO

Source


1 PACKAGE BODY mrp_reschedule_po AS
2 /*$Header: MRPRSPOB.pls 120.3.12010000.2 2008/12/12 15:55:56 eychen 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 
54  cursor check_rel_flag is
55     select ALLOW_RELEASE_FLAG, instance_code
56       from MRP_AP_APPS_INSTANCES_ALL
57      where instance_id = p_instance_id
58        and nvl(A2M_DBLINK, '-1') = p_dblink;
59 
60  X_need_by_dates_old 	   po_tbl_date := po_tbl_date();
61  X_need_by_dates 	   po_tbl_date := po_tbl_date();
62  X_po_line_ids 		   po_tbl_number := po_tbl_number();
63  X_shipment_nums 	   po_tbl_number := po_tbl_number();
64  X_estimated_pickup_dates  po_tbl_date := po_tbl_date();
65  X_ship_methods		   po_tbl_varchar30 := po_tbl_varchar30();
66  a number :=0;
67 
68  last_po_number varchar2(250);
69  last_po_header_id number;
70 
71 BEGIN
72 
73  FND_FILE.PUT_LINE(FND_FILE.LOG,'starting ...');
74  p_batch_id := p_po_header_id;
75  p_instance_id := p_po_line_id;
76  p_dblink := p_po_number;
77 
78  retcode :=0;
79    p_allow_release :=0;
80 
81    OPEN check_rel_flag;
82    FETCH check_rel_flag INTO p_allow_release, p_instance_code;
83    CLOSE check_rel_flag;
84 
85      if p_dblink = '-1' then
86           p_dblink := ' ';
87      else
88           p_dblink := '@'||p_dblink;
89      end if;
90 
91   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);
92 
93 
94  if p_allow_release = 1 THEN
95     mo_global.INIT('PO');
96  sql_stmt:=
97    ' select old_need_by_date,'||
98           ' new_need_by_date,'||
99           ' po_header_id,'||
100           ' po_line_id,'||
101           ' po_number,'||
102           ' action'||
103  ' from msc_purchase_order_interface'||p_dblink||
104  ' where sr_instance_id = '||p_instance_id||
105    ' and batch_id ='||p_batch_id ||
106    ' order by action, po_number ';
107 
108   OPEN po_cursor FOR sql_stmt;
109   FETCH po_cursor BULK COLLECT INTO v_old_need_by_date,
110                                     v_new_need_by_date,
111                                     v_po_header_id,
112                                     v_po_line_id,
113                                     v_po_number,
114                                     v_action;
115   CLOSE po_cursor;
116 
117   FOR i in 1..nvl(v_po_line_id.LAST, 0) LOOP
118 
119 /* in R12, order number(release number)(line number)(shipment number),
120    but release number could be empty,
121    in 11.5.10 and prior,
122     order number(release number)(shipment number) -- blanket PO or
123     order number(shipment number)  -- standard PO                   */
124 
125        first_left_pare_pos := instr(v_po_number(i), '(');
126        second_left_pare_pos := instr(v_po_number(i), '(',1,2);
127        third_left_pare_pos := instr(v_po_number(i), '(',1,3);
128   if third_left_pare_pos > 0 then -- in R12
129        first_right_pare_pos := instr(v_po_number(i), ')');
130        third_right_pare_pos := instr(v_po_number(i), ')', 1,3);
131        v_po_location_id := substr(v_po_number(i),
132                 third_left_pare_pos+1,third_right_pare_pos -
133                    third_left_pare_pos -1);
134 
135        begin
136           v_release_num :=  substr(v_po_number(i),
137                 first_left_pare_pos+1,first_right_pare_pos -
138                    first_left_pare_pos -1);
139        exception when others then
140               v_release_num :=null;
141        end;
142 
143        if v_release_num is null then
144             l_doc_type := 'PO';
145             l_doc_subtype := 'STANDARD';
146             v_po_number(i) := substr(v_po_number(i), 1,first_left_pare_pos -1);
147        else
148             l_doc_type := 'RELEASE';
149             l_doc_subtype := 'BLANKET';
150             v_po_number(i) := substr(v_po_number(i),1,second_left_pare_pos -1);
151        end if;
152   else -- in 11.5.10 or prior
153        -- -------------------------------------------------
154        -- Bug#4013684 - 16-dec-2004.
155        -- l_doc_type and l_doc_subtype will be derived here
156        -- and will be passed to 'Cancel PO' api.
157        -- -------------------------------------------------
158        if first_left_pare_pos > 0 then -- should not be 0, just in case
159          first_right_pare_pos := instr(v_po_number(i), ')');
160          if second_left_pare_pos = 0 then  -- standard po
161            v_po_location_id := substr(v_po_number(i),
162                 first_left_pare_pos+1,first_right_pare_pos -
163                    first_left_pare_pos -1);
164            v_po_number(i) := substr(v_po_number(i), 1,first_left_pare_pos -1);
165             l_doc_type := 'PO';
166             l_doc_subtype := 'STANDARD';
167          else -- blanket po
168            second_right_pare_pos := instr(v_po_number(i), ')', 1,2);
169            v_po_location_id := substr(v_po_number(i),
170                 second_left_pare_pos+1,second_right_pare_pos -
171                    second_left_pare_pos -1);
172            v_po_number(i) := substr(v_po_number(i),1,second_left_pare_pos -1);
173            l_doc_type := 'RELEASE';
174            l_doc_subtype := 'BLANKET';
175          end if;
176        end if;
177     end if; -- if third_left_pare_pos > 0
178 
179 	-- cancel the PO if action = 2 else reschedule it.
180 if v_action(i) = 2 then
181            mrp_cancel_po.cancel_po_program(v_po_header_id(i), v_po_line_id(i),
182                                            v_po_number(i), v_po_location_id,
183                                            l_doc_type , l_doc_subtype);
184 else
185    --5137694, for standard po, group by po_header_id,
186    -- for blanket po, group by po_header_id and shipment number
187 
188    if i <> nvl(v_po_line_id.FIRST, 0) and
189         v_po_number(i) <> v_po_number(i-1) then
190        --call PO api
191        p_result := reschedule_po(X_need_by_dates_old,
192                          X_need_by_dates,
193                          v_po_header_id(i-1),
194                          X_po_line_ids,
195                          v_po_number(i-1),
196                          X_shipment_nums,
197                          X_estimated_pickup_dates,
198                          X_ship_methods);
199              if not(p_result) then
200                retcode :=2;
201              end if;
202              -- reset po table
203              a := 0;
204              x_need_by_dates_old.delete;
205              X_need_by_dates.delete;
206              X_po_line_ids.delete;
207              X_shipment_nums.delete;
208              x_estimated_pickup_dates.delete;
209              x_ship_methods.delete;
210    end if; -- if i <> nvl(v_po_line_id.FIRST, 0) and
211    -- init po tables
212        a := a+1;
213        x_need_by_dates_old.extend;
214        x_need_by_dates_old(a) := v_old_need_by_date(i);
215        X_need_by_dates.extend;
216        X_need_by_dates(a) := v_new_need_by_date(i);
217        X_po_line_ids.extend;
218        X_po_line_ids(a) := v_po_line_id(i);
219        X_shipment_nums.extend;
220        X_shipment_nums(a) := v_po_location_id;
221        x_estimated_pickup_dates.extend;
222        x_estimated_pickup_dates(a) := null;
223        x_ship_methods.extend;
224        x_ship_methods(a) := null;
225        last_po_header_id := v_po_header_id(i);
226        last_po_number := v_po_number(i);
227   end if; -- if v_action = 2 then
228 
229  FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
230  END LOOP; -- FOR i in 1..nvl(v_po_line_id.LAST, 0) LOOP
231 
232   if a <> 0 then
233             p_result := reschedule_po(X_need_by_dates_old,
234                          X_need_by_dates,
235                          last_po_header_id,
236                          X_po_line_ids,
237                          last_po_number,
238                          X_shipment_nums,
239                          X_estimated_pickup_dates,
240                          X_ship_methods);
241              if not(p_result) then
242                retcode :=2;
243              end if;
244    end if; -- if a <> 0
245 
246 else -- not allow release
247      FND_FILE.PUT_LINE(FND_FILE.LOG, 'instance '||p_instance_code||' does not allow release ');
248 end if; -- if p_allow_release = 1 then
249  sql_stmt:= ' delete from msc_purchase_order_interface'||p_dblink||
250              ' where sr_instance_id = :p_instance_id '||
251              ' and batch_id = :p_batch_id ';
252 
253  EXECUTE IMMEDIATE sql_stmt using p_instance_id, p_batch_id;
254 
255  commit;
256 
257 exception
258 when others then
259  retcode :=2;
260  raise;
261 END reschedule_po_program;
262 
263 FUNCTION reschedule_po( X_old_need_by_dates      po_tbl_date,
264                          X_new_need_by_dates      po_tbl_date,
265                          X_po_header_id           number,
266                          X_po_line_ids            po_tbl_number,
267                          X_po_number              varchar2,
268                          X_shipment_nums          po_tbl_number,
269                          X_estimated_pickup_dates po_tbl_date,
270                          X_ship_methods           po_tbl_varchar30)
271   return boolean IS
272 
273   p_result boolean;
274  v_promised_date dateTab;
275  v_need_by_date dateTab;
276  v_rec NumTab;
277  v_date_changed boolean;
278  v_date date;
279  p_show_msg varchar2(3) := nvl(FND_PROFILE.Value('MRP_DEBUG'),'N');
280 
281  cursor po_cur(v_line_id number, v_header_id number, v_ship_num number) is
282       select nvl(poll.promised_date,poll.need_by_date),1
283       from   po_line_locations_all poll
284       where  poll.po_line_id = v_line_id
285         and  poll.po_header_id = v_header_id
286         and  poll.shipment_num = nvl(v_ship_num,poll.shipment_num);
287  v_old_need_by_dates      po_tbl_date := po_tbl_date();
288 
289             CURSOR cur_org(p_po_header_id IN number) IS
290             SELECT org_id
291             FROM po_headers_all
292             WHERE po_header_id = p_po_header_id;
293 
294             l_document_org_id NUMBER;
295             l_access_mode     VARCHAR2(1);
296             l_current_org_id  NUMBER;
297 
298  PROCEDURE show_po_details IS
299  BEGIN
300     for i in 1..nvl(X_po_line_ids.last, 0) loop
301       FND_FILE.PUT_LINE(FND_FILE.LOG,'old date in planner workbench: '||
302                    to_char(x_old_need_by_dates(i),'MM/DD/RR HH24:MI:SS'));
303       FND_FILE.PUT_LINE(FND_FILE.LOG,'new date: '||
304                    to_char(x_new_need_by_dates(i),'MM/DD/RR HH24:MI:SS'));
305       FND_FILE.PUT_LINE(FND_FILE.LOG,'header: '||x_po_header_id);
306       FND_FILE.PUT_LINE(FND_FILE.LOG,'line: '||x_po_line_ids(i));
307       FND_FILE.PUT_LINE(FND_FILE.LOG,'po number: '||x_po_number);
308       FND_FILE.PUT_LINE(FND_FILE.LOG,'shipment no: '||X_shipment_nums(i));
309     end loop;
310  END show_po_details;
311 
312 BEGIN
313 
314 -- p_show_msg := 'Y';
315 
316   begin
317 
318             OPEN cur_org(x_po_header_id);
319             FETCH cur_org INTO l_document_org_id;
320             CLOSE cur_org;
321 
322             l_access_mode := mo_global.Get_access_mode();
323             l_current_org_id := mo_global.get_current_org_id();
324 
325             mo_global.set_policy_context('S',l_document_org_id);
326 
327 
328  p_result :=po_reschedule_pkg.reschedule
329                         (X_old_need_by_dates,
330                          X_new_need_by_dates,
331                          X_po_header_id,
332                          X_po_line_ids,
333                          X_po_number,
334                          X_shipment_nums,
335                          X_estimated_pickup_dates,
336                          X_ship_methods);
337            Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode,
338                                           p_org_id => l_current_org_id);
339   exception when others then
340      FND_FILE.PUT_LINE(FND_FILE.LOG,' error while calling po_reschedule_pkg.reschedule '||sqlerrm);
341      Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode,
342                                               p_org_id => l_current_org_id);
343      show_po_details;
344   end;
345 
346  if p_result is null then
347      FND_FILE.PUT_LINE(FND_FILE.LOG, 'the call to  po_reschedule_pkg.reschedule return null value');
348  end if;
349 
350  p_result := nvl(p_result, false);
351 
352  if not(p_result) then -- p_result is false
353    -- 5030537, it might fail because our PO does not carry seconds,
354     v_date_changed := false;
355     for i in 1..nvl(X_po_line_ids.last, 0) loop
356       if v_rec is not null then
357          v_promised_date.delete;
358          v_rec.delete;
359       end if;
360       v_old_need_by_dates.extend;
361       v_old_need_by_dates(i) := x_old_need_by_dates(i);
362       OPEN po_cur(x_po_line_ids(i), X_po_header_id,X_shipment_nums(i));
363       FETCH po_cur BULK COLLECT INTO
364                v_promised_date, v_rec;
365       CLOSE po_cur;
366 
367       For a in 1..nvl(v_rec.last,0) LOOP
368            v_date := v_promised_date(a);
369            if to_date(to_char(x_old_need_by_dates(i), 'MM/DD/RRRR HH24:MI'),
370                  'MM/DD/RRRR HH24:MI') =
371               to_date(to_char(v_date, 'MM/DD/RRRR HH24:MI'),
372                  'MM/DD/RRRR HH24:MI') then
373               if x_old_need_by_dates(i) <> v_date then
374 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')||
375 ' to '||to_char(v_date, 'MM/DD/RRRR HH24:MI:SS'));
376                   v_old_need_by_dates(i) := v_date;
377                   v_date_changed := true;
378               end if;
379                exit;
380            end if;
381       END LOOP; -- For a in 1..nvl(v_rec.last,0) LOOP
382     end loop; -- for i in 1..nvl(X_po_line_ids.last, 0) loop
383 
384     if v_date_changed then
385        -- call reschedule again with updated old_need_by_date
386 
387        p_result :=po_reschedule_pkg.reschedule
388                         (v_old_need_by_dates,
389                          X_new_need_by_dates,
390                          X_po_header_id,
391                          X_po_line_ids,
392                          X_po_number,
393                          X_shipment_nums,
394                          X_estimated_pickup_dates,
395                          X_ship_methods);
396     end if; -- if v_date_changed then
397  end if; -- if not(p_result) then
398 
399     p_result := nvl(p_result, false);
400 
401     if p_result then
402       if p_show_msg = 'Y' then
403          FND_FILE.PUT_LINE(FND_FILE.LOG,'reschedule succeeds');
404       end if;
405     else
406       FND_FILE.PUT_LINE(FND_FILE.LOG,'reschedule fails');
407     end if;
408 
409 if (p_result and p_show_msg = 'Y') or
410        not(p_result) then
411    show_po_details;
412 end if;
413 
414   return p_result;
415 exception when others then
416   FND_FILE.PUT_LINE(FND_FILE.LOG,'error in reschedule_po, error is: '||sqlerrm);
417   show_po_details;
418   return false;
419 END reschedule_po;
420 
421 END mrp_reschedule_po;