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

Source


1 package body WMS_CROSSDOCK_CRITERIA_PKG as
2 /* $Header: WMSXDCRB.pls 120.0 2005/05/25 09:01:34 appldev noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_CRITERION_ID in NUMBER,
6   X_CRITERION_TYPE in NUMBER,
7   X_WINDOW_INTERVAL in NUMBER,
8   X_WINDOW_UOM in VARCHAR2,
9   X_BUFFER_INTERVAL in NUMBER,
10   X_BUFFER_UOM in VARCHAR2,
11   X_PROCESSING_INTERVAL in NUMBER,
12   X_PROCESSING_UOM in VARCHAR2,
13   X_PAST_DUE_INTERVAL in NUMBER,
14   X_PAST_DUE_UOM in VARCHAR2,
15   X_CROSSDOCK_GOAL in NUMBER,
16   X_PRIORITIZE_DOCUMENTS_FLAG in NUMBER,
17   X_UOM_INTEGRITY_FLAG in NUMBER,
18   X_ALLOW_DEMAND_RESCHEDULE_FLAG in NUMBER,
19   X_ALLOW_SUPPLY_RESCHEDULE_FLAG in NUMBER,
20   X_DEMAND_SCHEDULE_METHOD in NUMBER,
21   X_SUPPLY_SCHEDULE_METHOD in NUMBER,
22   X_CRITERION_NAME in VARCHAR2,
23   X_DESCRIPTION in VARCHAR2,
24   X_CREATION_DATE in DATE,
25   X_CREATED_BY in NUMBER,
26   X_LAST_UPDATE_DATE in DATE,
27   X_LAST_UPDATED_BY in NUMBER,
28   X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30   cursor C is select ROWID from WMS_CROSSDOCK_CRITERIA_B
31     where CRITERION_ID = X_CRITERION_ID
32     ;
33 begin
34   insert into WMS_CROSSDOCK_CRITERIA_B (
35     CRITERION_ID,
36     CRITERION_TYPE,
37     WINDOW_INTERVAL,
38     WINDOW_UOM,
39     BUFFER_INTERVAL,
40     BUFFER_UOM,
41     PROCESSING_INTERVAL,
42     PROCESSING_UOM,
43     PAST_DUE_INTERVAL,
44     PAST_DUE_UOM,
45     CROSSDOCK_GOAL,
46     PRIORITIZE_DOCUMENTS_FLAG,
47     UOM_INTEGRITY_FLAG,
48     ALLOW_DEMAND_RESCHEDULE_FLAG,
49     ALLOW_SUPPLY_RESCHEDULE_FLAG,
50     DEMAND_SCHEDULE_METHOD,
51     SUPPLY_SCHEDULE_METHOD,
52     CREATION_DATE,
53     CREATED_BY,
54     LAST_UPDATE_DATE,
55     LAST_UPDATED_BY,
56     LAST_UPDATE_LOGIN
57   ) values (
58     X_CRITERION_ID,
59     X_CRITERION_TYPE,
60     X_WINDOW_INTERVAL,
61     X_WINDOW_UOM,
62     X_BUFFER_INTERVAL,
63     X_BUFFER_UOM,
64     X_PROCESSING_INTERVAL,
65     X_PROCESSING_UOM,
66     X_PAST_DUE_INTERVAL,
67     X_PAST_DUE_UOM,
68     X_CROSSDOCK_GOAL,
69     X_PRIORITIZE_DOCUMENTS_FLAG,
70     X_UOM_INTEGRITY_FLAG,
71     X_ALLOW_DEMAND_RESCHEDULE_FLAG,
72     X_ALLOW_SUPPLY_RESCHEDULE_FLAG,
73     X_DEMAND_SCHEDULE_METHOD,
74     X_SUPPLY_SCHEDULE_METHOD,
75     X_CREATION_DATE,
76     X_CREATED_BY,
77     X_LAST_UPDATE_DATE,
78     X_LAST_UPDATED_BY,
79     X_LAST_UPDATE_LOGIN
80   );
81 
82   insert into WMS_CROSSDOCK_CRITERIA_TL (
83     CRITERION_ID,
84     LAST_UPDATED_BY,
85     LAST_UPDATE_DATE,
86     CREATED_BY,
87     CREATION_DATE,
88     LAST_UPDATE_LOGIN,
89     CRITERION_NAME,
90     DESCRIPTION,
91     LANGUAGE,
92     SOURCE_LANG
93   ) select
94     X_CRITERION_ID,
95     X_LAST_UPDATED_BY,
96     X_LAST_UPDATE_DATE,
97     X_CREATED_BY,
98     X_CREATION_DATE,
99     X_LAST_UPDATE_LOGIN,
100     X_CRITERION_NAME,
101     X_DESCRIPTION,
102     L.LANGUAGE_CODE,
103     userenv('LANG')
104   from FND_LANGUAGES L
105   where L.INSTALLED_FLAG in ('I', 'B')
106   and not exists
107     (select NULL
108     from WMS_CROSSDOCK_CRITERIA_TL T
109     where T.CRITERION_ID = X_CRITERION_ID
110     and T.LANGUAGE = L.LANGUAGE_CODE);
111 
112   open c;
113   fetch c into X_ROWID;
114   if (c%notfound) then
115     close c;
116     raise no_data_found;
117   end if;
118   close c;
119 
120 end INSERT_ROW;
121 
122 procedure LOCK_ROW (
123   X_CRITERION_ID in NUMBER,
124   X_CRITERION_TYPE in NUMBER,
125   X_WINDOW_INTERVAL in NUMBER,
126   X_WINDOW_UOM in VARCHAR2,
127   X_BUFFER_INTERVAL in NUMBER,
128   X_BUFFER_UOM in VARCHAR2,
129   X_PROCESSING_INTERVAL in NUMBER,
130   X_PROCESSING_UOM in VARCHAR2,
131   X_PAST_DUE_INTERVAL in NUMBER,
132   X_PAST_DUE_UOM in VARCHAR2,
133   X_CROSSDOCK_GOAL in NUMBER,
134   X_PRIORITIZE_DOCUMENTS_FLAG in NUMBER,
135   X_UOM_INTEGRITY_FLAG in NUMBER,
136   X_ALLOW_DEMAND_RESCHEDULE_FLAG in NUMBER,
137   X_ALLOW_SUPPLY_RESCHEDULE_FLAG in NUMBER,
138   X_DEMAND_SCHEDULE_METHOD in NUMBER,
139   X_SUPPLY_SCHEDULE_METHOD in NUMBER,
140   X_CRITERION_NAME in VARCHAR2,
141   X_DESCRIPTION in VARCHAR2
142 ) is
143   cursor c is select
144       CRITERION_TYPE,
145       WINDOW_INTERVAL,
146       WINDOW_UOM,
147       BUFFER_INTERVAL,
148       BUFFER_UOM,
149       PROCESSING_INTERVAL,
150       PROCESSING_UOM,
151       PAST_DUE_INTERVAL,
152       PAST_DUE_UOM,
153       CROSSDOCK_GOAL,
154       PRIORITIZE_DOCUMENTS_FLAG,
155       UOM_INTEGRITY_FLAG,
156       ALLOW_DEMAND_RESCHEDULE_FLAG,
157       ALLOW_SUPPLY_RESCHEDULE_FLAG,
158       DEMAND_SCHEDULE_METHOD,
159       SUPPLY_SCHEDULE_METHOD
160     from WMS_CROSSDOCK_CRITERIA_B
161     where CRITERION_ID = X_CRITERION_ID
162     for update of CRITERION_ID nowait;
163   recinfo c%rowtype;
164 
165   cursor c1 is select
166       CRITERION_NAME,
167       DESCRIPTION,
168       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
169     from WMS_CROSSDOCK_CRITERIA_TL
170     where CRITERION_ID = X_CRITERION_ID
171     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
172     for update of CRITERION_ID nowait;
173 begin
174   open c;
175   fetch c into recinfo;
176   if (c%notfound) then
177     close c;
178     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
179     app_exception.raise_exception;
180   end if;
181   close c;
182   if (    (recinfo.CRITERION_TYPE = X_CRITERION_TYPE)
183       AND (recinfo.WINDOW_INTERVAL = X_WINDOW_INTERVAL)
184       AND (recinfo.WINDOW_UOM = X_WINDOW_UOM)
185       AND ((recinfo.BUFFER_INTERVAL = X_BUFFER_INTERVAL)
186            OR ((recinfo.BUFFER_INTERVAL is null) AND (X_BUFFER_INTERVAL is null)))
187       AND ((recinfo.BUFFER_UOM = X_BUFFER_UOM)
188            OR ((recinfo.BUFFER_UOM is null) AND (X_BUFFER_UOM is null)))
189       AND ((recinfo.PROCESSING_INTERVAL = X_PROCESSING_INTERVAL)
190            OR ((recinfo.PROCESSING_INTERVAL is null) AND (X_PROCESSING_INTERVAL is null)))
191       AND ((recinfo.PROCESSING_UOM = X_PROCESSING_UOM)
192            OR ((recinfo.PROCESSING_UOM is null) AND (X_PROCESSING_UOM is null)))
193       AND ((recinfo.PAST_DUE_INTERVAL = X_PAST_DUE_INTERVAL)
194            OR ((recinfo.PAST_DUE_INTERVAL is null) AND (X_PAST_DUE_INTERVAL is null)))
195       AND ((recinfo.PAST_DUE_UOM = X_PAST_DUE_UOM)
196            OR ((recinfo.PAST_DUE_UOM is null) AND (X_PAST_DUE_UOM is null)))
197       AND (recinfo.CROSSDOCK_GOAL = X_CROSSDOCK_GOAL)
198       AND (recinfo.PRIORITIZE_DOCUMENTS_FLAG = X_PRIORITIZE_DOCUMENTS_FLAG)
199       AND (recinfo.UOM_INTEGRITY_FLAG = X_UOM_INTEGRITY_FLAG)
200       AND (recinfo.ALLOW_DEMAND_RESCHEDULE_FLAG = X_ALLOW_DEMAND_RESCHEDULE_FLAG)
201       AND (recinfo.ALLOW_SUPPLY_RESCHEDULE_FLAG = X_ALLOW_SUPPLY_RESCHEDULE_FLAG)
202       AND (recinfo.DEMAND_SCHEDULE_METHOD = X_DEMAND_SCHEDULE_METHOD)
203       AND (recinfo.SUPPLY_SCHEDULE_METHOD = X_SUPPLY_SCHEDULE_METHOD)
204   ) then
205     null;
206   else
207     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
208     app_exception.raise_exception;
209   end if;
210 
211   for tlinfo in c1 loop
212     if (tlinfo.BASELANG = 'Y') then
213       if (    (tlinfo.CRITERION_NAME = X_CRITERION_NAME)
214           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
215                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
216       ) then
217         null;
218       else
219         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
220         app_exception.raise_exception;
221       end if;
222     end if;
223   end loop;
224   return;
225 end LOCK_ROW;
226 
227 procedure UPDATE_ROW (
228   X_CRITERION_ID in NUMBER,
229   X_CRITERION_TYPE in NUMBER,
230   X_WINDOW_INTERVAL in NUMBER,
231   X_WINDOW_UOM in VARCHAR2,
232   X_BUFFER_INTERVAL in NUMBER,
233   X_BUFFER_UOM in VARCHAR2,
234   X_PROCESSING_INTERVAL in NUMBER,
235   X_PROCESSING_UOM in VARCHAR2,
236   X_PAST_DUE_INTERVAL in NUMBER,
237   X_PAST_DUE_UOM in VARCHAR2,
238   X_CROSSDOCK_GOAL in NUMBER,
239   X_PRIORITIZE_DOCUMENTS_FLAG in NUMBER,
240   X_UOM_INTEGRITY_FLAG in NUMBER,
241   X_ALLOW_DEMAND_RESCHEDULE_FLAG in NUMBER,
242   X_ALLOW_SUPPLY_RESCHEDULE_FLAG in NUMBER,
243   X_DEMAND_SCHEDULE_METHOD in NUMBER,
244   X_SUPPLY_SCHEDULE_METHOD in NUMBER,
245   X_CRITERION_NAME in VARCHAR2,
246   X_DESCRIPTION in VARCHAR2,
247   X_LAST_UPDATE_DATE in DATE,
248   X_LAST_UPDATED_BY in NUMBER,
249   X_LAST_UPDATE_LOGIN in NUMBER
250 ) is
251 begin
252   update WMS_CROSSDOCK_CRITERIA_B set
253     CRITERION_TYPE = X_CRITERION_TYPE,
254     WINDOW_INTERVAL = X_WINDOW_INTERVAL,
255     WINDOW_UOM = X_WINDOW_UOM,
256     BUFFER_INTERVAL = X_BUFFER_INTERVAL,
257     BUFFER_UOM = X_BUFFER_UOM,
258     PROCESSING_INTERVAL = X_PROCESSING_INTERVAL,
259     PROCESSING_UOM = X_PROCESSING_UOM,
260     PAST_DUE_INTERVAL = X_PAST_DUE_INTERVAL,
261     PAST_DUE_UOM = X_PAST_DUE_UOM,
262     CROSSDOCK_GOAL = X_CROSSDOCK_GOAL,
263     PRIORITIZE_DOCUMENTS_FLAG = X_PRIORITIZE_DOCUMENTS_FLAG,
264     UOM_INTEGRITY_FLAG = X_UOM_INTEGRITY_FLAG,
265     ALLOW_DEMAND_RESCHEDULE_FLAG = X_ALLOW_DEMAND_RESCHEDULE_FLAG,
266     ALLOW_SUPPLY_RESCHEDULE_FLAG = X_ALLOW_SUPPLY_RESCHEDULE_FLAG,
267     DEMAND_SCHEDULE_METHOD = X_DEMAND_SCHEDULE_METHOD,
268     SUPPLY_SCHEDULE_METHOD = X_SUPPLY_SCHEDULE_METHOD,
269     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
270     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
271     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
272   where CRITERION_ID = X_CRITERION_ID;
273 
274   if (sql%notfound) then
275     raise no_data_found;
276   end if;
277 
278   update WMS_CROSSDOCK_CRITERIA_TL set
279     CRITERION_NAME = X_CRITERION_NAME,
280     DESCRIPTION = X_DESCRIPTION,
281     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
282     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
283     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
284     SOURCE_LANG = userenv('LANG')
285   where CRITERION_ID = X_CRITERION_ID
286   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
287 
288   if (sql%notfound) then
289     raise no_data_found;
290   end if;
291 end UPDATE_ROW;
292 
293 procedure DELETE_ROW (
294   X_CRITERION_ID in NUMBER
295 ) is
296 begin
297   delete from WMS_CROSSDOCK_CRITERIA_TL
298   where CRITERION_ID = X_CRITERION_ID;
299 
300   if (sql%notfound) then
301     raise no_data_found;
302   end if;
303 
304   delete from WMS_CROSSDOCK_CRITERIA_B
305   where CRITERION_ID = X_CRITERION_ID;
306 
307   if (sql%notfound) then
308     raise no_data_found;
309   end if;
310 end DELETE_ROW;
311 
312 procedure ADD_LANGUAGE
313 is
314 begin
315   delete from WMS_CROSSDOCK_CRITERIA_TL T
316   where not exists
317     (select NULL
318     from WMS_CROSSDOCK_CRITERIA_B B
319     where B.CRITERION_ID = T.CRITERION_ID
320     );
321 
322   update WMS_CROSSDOCK_CRITERIA_TL T set (
323       CRITERION_NAME,
324       DESCRIPTION
325     ) = (select
326       B.CRITERION_NAME,
327       B.DESCRIPTION
328     from WMS_CROSSDOCK_CRITERIA_TL B
329     where B.CRITERION_ID = T.CRITERION_ID
330     and B.LANGUAGE = T.SOURCE_LANG)
331   where (
332       T.CRITERION_ID,
333       T.LANGUAGE
334   ) in (select
335       SUBT.CRITERION_ID,
336       SUBT.LANGUAGE
337     from WMS_CROSSDOCK_CRITERIA_TL SUBB, WMS_CROSSDOCK_CRITERIA_TL SUBT
338     where SUBB.CRITERION_ID = SUBT.CRITERION_ID
339     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
340     and (SUBB.CRITERION_NAME <> SUBT.CRITERION_NAME
341       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
342       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
343       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
344   ));
345 
346   insert into WMS_CROSSDOCK_CRITERIA_TL (
347     CRITERION_ID,
348     LAST_UPDATED_BY,
349     LAST_UPDATE_DATE,
350     CREATED_BY,
351     CREATION_DATE,
352     LAST_UPDATE_LOGIN,
353     CRITERION_NAME,
354     DESCRIPTION,
355     LANGUAGE,
356     SOURCE_LANG
357   ) select /*+ ORDERED */
358     B.CRITERION_ID,
359     B.LAST_UPDATED_BY,
360     B.LAST_UPDATE_DATE,
361     B.CREATED_BY,
362     B.CREATION_DATE,
363     B.LAST_UPDATE_LOGIN,
364     B.CRITERION_NAME,
365     B.DESCRIPTION,
366     L.LANGUAGE_CODE,
367     B.SOURCE_LANG
368   from WMS_CROSSDOCK_CRITERIA_TL B, FND_LANGUAGES L
369   where L.INSTALLED_FLAG in ('I', 'B')
370   and B.LANGUAGE = userenv('LANG')
371   and not exists
372     (select NULL
373     from WMS_CROSSDOCK_CRITERIA_TL T
374     where T.CRITERION_ID = B.CRITERION_ID
375     and T.LANGUAGE = L.LANGUAGE_CODE);
376 end ADD_LANGUAGE;
377 
378 end WMS_CROSSDOCK_CRITERIA_PKG;