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.INVWATP

Source


1 PACKAGE BODY INVWATP as
2 /* $Header: INVWATPB.pls 120.1 2005/06/11 07:42:14 appldev  $ */
3 
4 -- Return values
5 --   -1   System Error (Oracle Error Message)
6 --   -2   ATP Group id cannot be null.
7 --   -3   No ATP Rule specified.
8 --   -4   Item not found.
9 --   -5   No UOM Code.
10 --   -6   No Calendar organization
11 --   -7   Unable to insert into mtl_group_atps_view
12 --  For all the above conditions, message ICX_INV_WATP_FAILED is set.
13 FUNCTION WebAtpInsert
14 (
15   x_organization_id number,
16   x_inventory_item_id number,
17   x_atp_rule_id number,
18   x_request_quantity number,
19   x_request_primary_uom_quantity number,
20   x_request_date date,
21   x_atp_lead_time number,
22   x_uom_code varchar2,
23   x_demand_class varchar2,
24   x_n_column2 number
25 )
26 return number
27 is
28   rec_mgav  mtl_group_atps_view%rowtype;
29   ret_code  number;
30   ret_val   number;
31 begin
32   ret_code := 0;
33 
34 if ret_code = 0 then
35 -- atp group id. if n_column2 = 1, get it from sequence,
36 -- if not use the existing one.
37   if x_n_column2 = 1 then
38     select
39       mtl_demand_interface_s.nextval
40     into
41       rec_mgav.ATP_GROUP_ID
42     from
43       dual;
44     --INVWATP.WebAtpGroupId := rec_mgav.ATP_GROUP_ID;
45     INVWATP.SetAtpGroupId ( rec_mgav.ATP_GROUP_ID );
46   else
47      rec_mgav.ATP_GROUP_ID := INVWATP.GetAtpGroupId;
48      if rec_mgav.ATP_GROUP_ID is null then
49        -- cannot proceed with null atp group id
50        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
51        ret_code := -2;
52      end if;
53   end if;
54 
55 --dbms_output.put_line ('Atp group id : '|| to_char (rec_mgav.ATP_GROUP_ID) );
56 end if; -- ret_code=0
57 
58 if ret_code = 0 then
59 -- atp rule id: if passed in, use it. else
60 -- finds OUT NOCOPY /* file.sql.39 change */ from item if it has atp rule or else
61 -- takes it from organization default. cannot be null
62 
63   if x_atp_rule_id is not null then
64     rec_mgav.atp_rule_id := x_atp_rule_id;
65   else
66     begin
67     select atp_rule_id
68     into
69       rec_mgav.atp_rule_id
70     from
71        mtl_system_items
72     where
73        inventory_item_id = x_inventory_item_id and
74        organization_id = x_organization_id;
75 
76     -- msi table does has null atp rule id, so take it from mtl parameters
77     -- from org definition.
78     if rec_mgav.atp_rule_id is null then
79         begin
80           SELECT r.rule_id
81           INTO
82             rec_mgav.atp_rule_id
83           FROM
84             mtl_parameters p, mtl_atp_rules r
85           WHERE
86             p.default_atp_rule_id = r.rule_id
87             AND p.organization_id =  x_organization_id;
88         exception
89          when NO_DATA_FOUND then
90            --No Atp Rule specified
91            fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
92            ret_code := -3;
93          when OTHERS then
94            --System Error
95            fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
96            ret_code := -1;
97         end;
98     end if;
99     exception
100     when NO_DATA_FOUND then
101        --Item not found
102        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
103        ret_code := -4;
104      when OTHERS then
105        --System Error
106        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
107        ret_code := -1;
108     end;
109   end if;
110 --dbms_output.put_line ('Atp Rule id : ' || to_char (rec_mgav.ATP_RULE_ID) );
111 end if; -- ret_code=0
112 
113 
114 if ret_code = 0 then
115 --uom code, takes it from mtl_system_items
116   if x_uom_code is not null then
117      rec_mgav.uom_code :=  x_uom_code;
118   else
119        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
120        ret_code := -5;
121 /****
122     begin
123     select primary_uom_code
124     into  rec_mgav.uom_code
125     from mtl_system_items
126     where
127       inventory_item_id = x_inventory_item_id and
128       organization_id   = x_organization_id;
129     if rec_mgav.uom_code is null then
130        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
131        ret_code := -5;
132     end if;
133     exception
134      when NO_DATA_FOUND then
135        --No UOM code
136        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
137        ret_code := -5;
138      when OTHERS then
139        --System Error
140        fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
141        ret_code := -1;
142     end;
143 ***/
144   end if;
145 --dbms_output.put_line ('UOM code: ' || rec_mgav.uom_code );
146 end if; -- ret_code =0
147 
148 
149 
150 if ret_code = 0 then
151   begin
152 -- calendar organization id
153     SELECT MTL.ORGANIZATION_ID
154     into rec_mgav.atp_calendar_organization_id
155     FROM   HR_ORGANIZATION_UNITS HR, MTL_PARAMETERS MTL
156        WHERE  HR.ORGANIZATION_ID = MTL.ORGANIZATION_ID
157        AND    MTL.CALENDAR_CODE is not null
158        AND    MTL.CALENDAR_EXCEPTION_SET_ID is not null
159        AND    MTL.ORGANIZATION_ID = x_organization_id;
160     if rec_mgav.atp_calendar_organization_id is null then
161       fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
162       ret_code := -6;
163     end if;
164   exception
165     when NO_DATA_FOUND then
166       --No Calendar organization
167       fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
168       ret_code := -6;
169     when OTHERS then
170       --System Error
171       fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
172       ret_code := -1;
173   end;
174 
175 --dbms_output.put_line ('Calendar org id: ' ||
176 --       to_char (rec_mgav.atp_calendar_organization_id) );
177 end if; --ret_code=0
178 
179 if ret_code = 0 then
180 begin
181 INSERT INTO MTL_GROUP_ATPS_VIEW
182   (ATP_GROUP_ID,
183    ORGANIZATION_ID,
184    INVENTORY_ITEM_ID,
185    LAST_UPDATE_DATE,
186    LAST_UPDATED_BY,
187    CREATION_DATE,
188    CREATED_BY,
189    LAST_UPDATE_LOGIN,
190    ATP_RULE_ID,
191    REQUEST_QUANTITY,
192    REQUEST_PRIMARY_UOM_QUANTITY,
193    REQUEST_DATE,
194    ATP_LEAD_TIME,
195    ATP_CALENDAR_ORGANIZATION_ID,
196    AVAILABLE_TO_ATP,
197    UOM_CODE,
198    DEMAND_CLASS,
199    N_COLUMN2
200   )
201 values
202   (
203    rec_mgav.ATP_GROUP_ID,
204    x_ORGANIZATION_ID,
205    x_INVENTORY_ITEM_ID,
206    sysdate,
207    -1,
208    sysdate,
209    -1,
210    -1,
211    rec_mgav.ATP_RULE_ID,
212    x_REQUEST_QUANTITY,
213    x_REQUEST_PRIMARY_UOM_QUANTITY,
214    x_REQUEST_DATE,
215    x_ATP_LEAD_TIME,
216    rec_mgav.ATP_CALENDAR_ORGANIZATION_ID,
217    1,
218    rec_mgav.UOM_CODE,
219    x_DEMAND_CLASS,
220    x_N_COLUMN2
221   )
222 ;
223 exception
224   when OTHERS then
225     -- unable to insert
226     fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
227     ret_code := -7;
228 end;
229 end if; -- ret_code=0
230 
231 ret_val := ret_code;
232 return (ret_val);
233 
234 end WebAtpInsert;
235 
236 
237 
238 PROCEDURE SetAtpGroupId ( x_atp_group_id number ) is
239 begin
240   INVWATP.WebAtpGroupId := x_atp_group_id;
241 end SetAtpGroupId;
242 
243 FUNCTION GetAtpGroupId return number is
244 begin
245   return INVWATP.WebAtpGroupId;
246 end GetAtpGroupId;
247 
248 
249 
250 -- Return values
251 --   1   Timed out
252 --   2   No manager
253 --   3   Other Error
254 --  For all the above conditions, message ICX_INV_WATP_FAILED is set.
255 FUNCTION WebAtpLaunch (
256   x_user_id in number,
257   x_resp_id in number,
258   x_resp_appl_id in number
259 )
260 return number
261 is
262   ret_val     number;
263   timeout     number;
264   mgr_outcome varchar2 (30);
265   mgr_message varchar2 (240);
266   retval      number;
267   retval1     number;
268   err_msg     varchar2 (240);
269   session_id  number;
270   arg_1       varchar2 (80);
271   arg_2       varchar2 (80);
272   arg_3       varchar2 (80);
273   arg_4       varchar2 (80);
274   arg_5       varchar2 (80);
275   arg_6       varchar2 (80);
276   arg_7       varchar2 (80);
277   arg_8       varchar2 (80);
278   arg_9       varchar2 (80);
279   arg_10       varchar2 (80);
280   arg_11       varchar2 (80);
281   arg_12       varchar2 (80);
282   arg_13       varchar2 (80);
283   arg_14       varchar2 (80);
284   arg_15       varchar2 (80);
285   arg_16       varchar2 (80);
286   arg_17       varchar2 (80);
287   arg_18       varchar2 (80);
288   arg_19       varchar2 (80);
289   arg_20       varchar2 (80);
290 begin
291 
292 timeout  := 240;
293 
294 fnd_global.apps_initialize (
295   x_user_id,
296   x_resp_id,
297   x_resp_appl_id );
298 
299 
300 --dbms_output.put_line ( 'Atpgroupid:' || to_char (INVWATP.GetAtpGroupId) );
301 retval := fnd_transaction.synchronous (
302    timeout,
303    mgr_outcome,
304    mgr_message,
305    'INV',
306    'INXATP',
307 --   'INXATP GROUP_ID=343499 DETAIL_FLAG=0 MRP_STATUS=1'
308 --   'INXATP GROUP_ID=343499 MRP_STATUS=1'
309    'INXATP GROUP_ID=' || to_char (INVWATP.GetAtpGroupId) || ' MRP_STATUS=1'
310  );
311 
312 
313 retval1 := fnd_transaction.get_values  (
314   arg_1,
315   arg_2,
316   arg_3,
317   arg_4,
318   arg_5,
319   arg_6,
320   arg_7,
321   arg_8,
322   arg_9,
323   arg_10,
324   arg_11,
325   arg_12,
326   arg_13,
327   arg_14,
328   arg_15,
329   arg_16,
330   arg_17,
331   arg_18,
332   arg_19,
333   arg_20);
334 
335 
336 
337 --dbms_output.put_line ('**************************************');
338   --dbms_output.put_line ( 'Arg 1:' || arg_1 || ':');
339   --dbms_output.put_line ( 'Arg 2:' || arg_2 || ':');
340   --dbms_output.put_line ( 'Arg 3:' || arg_3 || ':');
341   --dbms_output.put_line ( 'Arg 4:' || arg_4 || ':');
342   --dbms_output.put_line ( 'Arg 5:' || arg_5 || ':');
343   --dbms_output.put_line ( 'Arg 6:' || arg_6 || ':');
344   --dbms_output.put_line ( 'Arg 7:' || arg_7 || ':');
345   --dbms_output.put_line ( 'Arg 8:' || arg_8 || ':');
346   --dbms_output.put_line ( 'Arg 9:' || arg_9 || ':');
347 
348   --dbms_output.put_line ( 'Arg 10:' || arg_10|| ':');
349   --dbms_output.put_line ( 'Arg 11:' || arg_11 || ':');
350   --dbms_output.put_line ( 'Arg 12:' || arg_12 || ':');
351   --dbms_output.put_line ( 'Arg 13:' || arg_13 || ':');
352   --dbms_output.put_line ( 'Arg 14:' || arg_14 || ':');
353   --dbms_output.put_line ( 'Arg 15:' || arg_15 || ':');
354   --dbms_output.put_line ( 'Arg 16:' || arg_16 || ':');
355   --dbms_output.put_line ( 'Arg 17:' || arg_17 || ':');
356   --dbms_output.put_line ( 'Arg 18:' || arg_18 || ':');
357   --dbms_output.put_line ( 'Arg 19:' || arg_19 || ':');
358   --dbms_output.put_line ( 'Arg 20:' || arg_20 || ':');
359   --dbms_output.put_line ( 'Ret Val1:' || to_char (retval1) || ':');
360 
361 
362 
363  if retval = 0 then
364  -- success
365   --dbms_output.put_line ('Success');
366   null;
367  elsif retval = 1 then
368  -- timeout
369   --dbms_output.put_line ('Timed out');
370   fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
371  elsif retval = 2 then
372  -- no manager
373   --dbms_output.put_line ('No manager');
374   fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
375  elsif retval = 3 then
376  -- other
377   --dbms_output.put_line ('Other Error');
378   fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
379  end if;
380 
381 --dbms_output.put_line ('**************************************');
382 --dbms_output.put_line ('Outcome: ' || mgr_outcome );
383 --dbms_output.put_line ('Message: ' || mgr_message );
384 --dbms_output.put_line ('**************************************');
385 
386 ret_val := retval;
387 return (ret_val);
388 end WebAtpLaunch;
389 
390 
391 -- Return values
392 --   -1   System Error (Oracle Error Message)
393 --   -2   ATP Group id cannot be null.
394 --  For all the above conditions, message ICX_INV_WATP_FAILED is set.
395 FUNCTION WebAtpFetch (
396     x_n_column2 number,
397     x_inventory_item_id OUT NOCOPY /* file.sql.39 change */ number,
398     x_organization_id OUT NOCOPY /* file.sql.39 change */ number,
399     x_request_quantity OUT NOCOPY /* file.sql.39 change */ number,
400     x_request_primary_uom_quantity OUT NOCOPY /* file.sql.39 change */ number,
401     x_request_date OUT NOCOPY /* file.sql.39 change */ date,
402     x_error_code OUT NOCOPY /* file.sql.39 change */ number,
403     x_group_available_date OUT NOCOPY /* file.sql.39 change */ date,
404     x_request_date_atp_quantity OUT NOCOPY /* file.sql.39 change */ number,
405     x_earliest_atp_date OUT NOCOPY /* file.sql.39 change */ date,
406     x_earliest_atp_date_quantity OUT NOCOPY /* file.sql.39 change */ number,
407     x_request_atp_date OUT NOCOPY /* file.sql.39 change */ date,
408     x_request_atp_date_quantity OUT NOCOPY /* file.sql.39 change */ number,
409     x_infinite_time_fence_date OUT NOCOPY /* file.sql.39 change */ date
410 )
411 return number
412 is
413 x_AtpGrpId number;
414 ret_code   number;
415 ret_val    number;
416 begin
417 
418   ret_code := 0;
419 
420   x_AtpGrpId := INVWATP.GetAtpGroupId;
421 
422   if x_AtpGrpId is null then
423      --Null Atp Group id
424      fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
425      ret_code := -2;
426   else
427    begin
428     select
429       inventory_item_id,
430       organization_id,
431       request_quantity,
432       request_primary_uom_quantity,
433       request_date,
434       error_code,
435       group_available_date,
436       request_date_atp_quantity,
437       earliest_atp_date,
438       earliest_atp_date_quantity,
439       request_atp_date,
440       request_atp_date_quantity,
441       infinite_time_fence_date
442     into
443       x_inventory_item_id,
444       x_organization_id,
445       x_request_quantity,
446       x_request_primary_uom_quantity,
447       x_request_date,
448       x_error_code,
449       x_group_available_date,
450       x_request_date_atp_quantity,
451       x_earliest_atp_date,
452       x_earliest_atp_date_quantity,
453       x_request_atp_date,
454       x_request_atp_date_quantity,
455       x_infinite_time_fence_date
456     from
457       MTL_GROUP_ATPS_VIEW
458     where
459       ATP_GROUP_ID = x_AtpGrpId and
460       n_column2    = x_n_column2;
461     exception
462        when OTHERS then
463          --System Error
464          fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
465          ret_code := -1;
466     end;
467   end if;
468 
469 ret_val := ret_code;
470 return (ret_val);
471 end WebAtpFetch;
472 
473 
474 -- Return values
475 --   -1   System Error (Oracle Error Message)
476 --   -2   ATP Group id cannot be null.
477 --  For all the above conditions, message ICX_INV_WATP_FAILED is set.
478 FUNCTION WebAtpClear
479 return number
480  is
481   x_AtpGrpId number;
482   ret_code   number;
483   ret_val    number;
484 begin
485 
486   ret_code := 0;
487   x_AtpGrpId := INVWATP.GetAtpGroupId;
488 
489   if x_AtpGrpId is null then
490      -- null atp group id
491      fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
492      ret_code := -2;
493   else
494     begin
495       delete from mtl_group_atps_view
496       where
497        atp_group_id = x_AtpGrpId;
498     exception
499       when OTHERS then
500         -- system error
501         fnd_message.set_name ( 'EC', 'ICX_INV_WATP_FAILED');
502         ret_code := -1;
503     end;
504   end if;
505 
506 ret_val := ret_code;
507 return (ret_val);
508 end WebAtpClear;
509 
510 END INVWATP;