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

Source


1 package body MTL_LOT_UOM_CONV_PKG as
2 /* $Header: INVHLUCB.pls 120.0 2005/05/25 05:39:20 appldev noship $ */
3 
4 
5 PROCEDURE INSERT_ROW(
6   X_CONVERSION_ID           IN OUT NOCOPY NUMBER,
7   X_LOT_NUMBER              IN VARCHAR2,
8   X_ORGANIZATION_ID         IN NUMBER,
9   X_INVENTORY_ITEM_ID       IN NUMBER,
10   X_FROM_UNIT_OF_MEASURE    IN VARCHAR2,
11   X_FROM_UOM_CODE           IN VARCHAR2,
12   X_FROM_UOM_CLASS          IN VARCHAR2,
13   X_TO_UNIT_OF_MEASURE      IN VARCHAR2,
14   X_TO_UOM_CODE             IN VARCHAR2,
15   X_TO_UOM_CLASS            IN VARCHAR2,
16   X_CONVERSION_RATE         IN NUMBER,
17   X_DISABLE_DATE            IN DATE,
18   X_EVENT_SPEC_DISP_ID      IN NUMBER,
19   X_CREATED_BY              IN NUMBER,
20   X_CREATION_DATE           IN DATE,
21   X_LAST_UPDATED_BY         IN NUMBER,
22   X_LAST_UPDATE_DATE        IN DATE,
23   X_LAST_UPDATE_LOGIN       IN NUMBER,
24   X_REQUEST_ID              IN NUMBER,
25   X_PROGRAM_APPLICATION_ID  IN NUMBER,
26   X_PROGRAM_ID              IN NUMBER,
27   X_PROGRAM_UPDATE_DATE     IN DATE,
28   x_return_status           OUT NOCOPY VARCHAR2,
29   x_msg_count               OUT NOCOPY NUMBER,
30   x_msg_data                OUT NOCOPY VARCHAR2)
31 
32 IS
33 
34 
35 CURSOR GET_CONV_SEQ
36 IS
37 SELECT MTL_CONVERSION_ID_S.NEXTVAL
38 FROM FND_DUAL;
39 
40 l_conv_seq              NUMBER;
41 
42 
43 BEGIN
44 
45   x_return_status := FND_API.G_RET_STS_SUCCESS;
46   IF (X_CONVERSION_ID IS NULL) THEN
47      OPEN GET_CONV_SEQ;
48      FETCH GET_CONV_SEQ INTO l_conv_seq;
49      X_CONVERSION_ID := l_conv_seq;
50      CLOSE GET_CONV_SEQ;
51   END IF;
52 
53 
54   INSERT INTO MTL_LOT_UOM_CLASS_CONVERSIONS(
55       CONVERSION_ID,
56       LOT_NUMBER,
57       ORGANIZATION_ID,
58       INVENTORY_ITEM_ID,
59       FROM_UNIT_OF_MEASURE,
60       FROM_UOM_CODE,
61       FROM_UOM_CLASS,
62       TO_UNIT_OF_MEASURE,
63       TO_UOM_CODE,
64       TO_UOM_CLASS,
65       CONVERSION_RATE,
66       DISABLE_DATE,
67       EVENT_SPEC_DISP_ID,
68       CREATED_BY,
69       CREATION_DATE,
70       LAST_UPDATED_BY,
71       LAST_UPDATE_DATE,
72       LAST_UPDATE_LOGIN,
73       REQUEST_ID,
74       PROGRAM_APPLICATION_ID,
75       PROGRAM_ID,
76       PROGRAM_UPDATE_DATE
77       )
78   VALUES(
79       X_CONVERSION_ID,
80       X_LOT_NUMBER,
81       X_ORGANIZATION_ID,
82       X_INVENTORY_ITEM_ID,
83       X_FROM_UNIT_OF_MEASURE,
84       X_FROM_UOM_CODE,
85       X_FROM_UOM_CLASS,
86       X_TO_UNIT_OF_MEASURE,
87       X_TO_UOM_CODE,
88       X_TO_UOM_CLASS,
89       X_CONVERSION_RATE,
90       X_DISABLE_DATE,
91       X_EVENT_SPEC_DISP_ID,
92       X_CREATED_BY,
93       X_CREATION_DATE,
94       X_LAST_UPDATED_BY,
95       X_LAST_UPDATE_DATE,
96       X_LAST_UPDATE_LOGIN,
97       X_REQUEST_ID,
98       X_PROGRAM_APPLICATION_ID,
99       X_PROGRAM_ID,
100       X_PROGRAM_UPDATE_DATE
101   );
102 
103 
104     FND_MSG_PUB.Count_AND_GET
105       (p_count => x_msg_count, p_data  => x_msg_data);
106 
107 EXCEPTION
108 
109 
110   WHEN OTHERS THEN
111     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112     IF (SQLCODE IS NOT NULL) THEN
113       FND_MESSAGE.SET_NAME('GMI','GMI_LOTC_SQL_ERROR');
114       FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
115       FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
116       FND_MSG_PUB.Add;
117     END IF;
118     FND_MSG_PUB.Count_AND_GET
119       (p_count => x_msg_count, p_data  => x_msg_data);
120 
121 
122 END INSERT_ROW;
123 
124 
125 PROCEDURE UPDATE_ROW(
126   X_CONVERSION_ID           IN NUMBER,
127   X_LOT_NUMBER              IN VARCHAR2,
128   X_ORGANIZATION_ID         IN NUMBER,
129   X_INVENTORY_ITEM_ID       IN NUMBER,
130   X_FROM_UNIT_OF_MEASURE    IN VARCHAR2,
131   X_FROM_UOM_CODE           IN VARCHAR2,
132   X_FROM_UOM_CLASS          IN VARCHAR2,
133   X_TO_UNIT_OF_MEASURE      IN VARCHAR2,
134   X_TO_UOM_CODE             IN VARCHAR2,
135   X_TO_UOM_CLASS            IN VARCHAR2,
136   X_CONVERSION_RATE         IN NUMBER,
137   X_DISABLE_DATE            IN DATE,
138   X_EVENT_SPEC_DISP_ID      IN NUMBER,
139   X_LAST_UPDATED_BY         IN NUMBER,
140   X_LAST_UPDATE_DATE        IN DATE,
141   X_LAST_UPDATE_LOGIN       IN NUMBER,
142   X_REQUEST_ID              IN NUMBER,
143   X_PROGRAM_APPLICATION_ID  IN NUMBER,
144   X_PROGRAM_ID              IN NUMBER,
145   X_PROGRAM_UPDATE_DATE     IN DATE,
146   x_return_status           OUT NOCOPY VARCHAR2,
147   x_msg_count               OUT NOCOPY NUMBER,
148   x_msg_data                OUT NOCOPY VARCHAR2)
149 
150 IS
151 
152 BEGIN
153 
154   x_return_status := FND_API.G_RET_STS_SUCCESS;
155   UPDATE MTL_LOT_UOM_CLASS_CONVERSIONS SET
156       LOT_NUMBER = X_LOT_NUMBER,
157       ORGANIZATION_ID = X_ORGANIZATION_ID,
158       INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
159       FROM_UNIT_OF_MEASURE = X_FROM_UNIT_OF_MEASURE,
160       FROM_UOM_CODE = X_FROM_UOM_CODE,
161       FROM_UOM_CLASS = X_FROM_UOM_CLASS,
162       TO_UNIT_OF_MEASURE = X_TO_UNIT_OF_MEASURE,
163       TO_UOM_CODE = X_TO_UOM_CODE,
164       TO_UOM_CLASS = X_TO_UOM_CLASS,
165       CONVERSION_RATE = X_CONVERSION_RATE,
166       DISABLE_DATE = X_DISABLE_DATE,
167       EVENT_SPEC_DISP_ID = X_EVENT_SPEC_DISP_ID,
168       LAST_UPDATED_BY = X_LAST_UPDATED_BY,
169       LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
170       LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
171       REQUEST_ID = X_REQUEST_ID,
172       PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
173       PROGRAM_ID = X_PROGRAM_ID,
174       PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
175     WHERE CONVERSION_ID = X_CONVERSION_ID;
176 
177     FND_MSG_PUB.Count_AND_GET
178       (p_count => x_msg_count, p_data  => x_msg_data);
179 
180 EXCEPTION
181 
182   WHEN OTHERS THEN
183     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184     IF (SQLCODE IS NOT NULL) THEN
185       FND_MESSAGE.SET_NAME('GMI','GMI_LOTC_SQL_ERROR');
186       FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
187       FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
188       FND_MSG_PUB.Add;
189     END IF;
190     FND_MSG_PUB.Count_AND_GET
191       (p_count => x_msg_count, p_data  => x_msg_data);
192 
193 END UPDATE_ROW;
194 
195 
196 
197 PROCEDURE DELETE_ROW(
198   X_CONVERSION_ID           IN NUMBER,
199   X_DISABLE_DATE            IN DATE,
200   X_LAST_UPDATED_BY         IN NUMBER,
201   X_LAST_UPDATE_DATE        IN DATE,
202   X_LAST_UPDATE_LOGIN       IN NUMBER,
203   X_REQUEST_ID              IN NUMBER,
204   X_PROGRAM_APPLICATION_ID  IN NUMBER,
205   X_PROGRAM_ID              IN NUMBER,
206   X_PROGRAM_UPDATE_DATE     IN DATE,
207   x_return_status           OUT NOCOPY VARCHAR2,
208   x_msg_count               OUT NOCOPY NUMBER,
209   x_msg_data                OUT NOCOPY VARCHAR2)
210 
211 IS
212 
213 BEGIN
214 
215     x_return_status := FND_API.G_RET_STS_SUCCESS;
216     UPDATE MTL_LOT_UOM_CLASS_CONVERSIONS SET
217       DISABLE_DATE = X_DISABLE_DATE,
218       LAST_UPDATED_BY = X_LAST_UPDATED_BY,
219       LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
220       LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
221       REQUEST_ID = X_REQUEST_ID,
222       PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
223       PROGRAM_ID = X_PROGRAM_ID,
224       PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
225     WHERE CONVERSION_ID = X_CONVERSION_ID;
226 
227     FND_MSG_PUB.Count_AND_GET
228       (p_count => x_msg_count, p_data  => x_msg_data);
229 
230 EXCEPTION
231 
232 
233   WHEN OTHERS THEN
234     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235     IF (SQLCODE IS NOT NULL) THEN
236       FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
237       FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
238       FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
239       FND_MSG_PUB.Add;
240     END IF;
241     FND_MSG_PUB.Count_AND_GET
242       (p_count => x_msg_count, p_data  => x_msg_data);
243 
244 END DELETE_ROW;
245 
246 
247 
248   PROCEDURE lock_row(
249     x_conversion_id                NUMBER
250   , x_lot_number                   VARCHAR2
251   , x_organization_id              NUMBER
252   , x_inventory_item_id            NUMBER
253   , x_from_unit_of_measure         VARCHAR2
254   , x_from_uom_code                VARCHAR2
255   , x_from_uom_class               VARCHAR2
256   , x_to_unit_of_measure           VARCHAR2
257   , x_to_uom_code                  VARCHAR2
258   , x_to_uom_class                 VARCHAR2
259   , x_conversion_rate              NUMBER
260   , x_disable_date                 DATE
261   , x_event_spec_disp_id           NUMBER
262 
263 
264   )
265 IS
266     CURSOR c IS
267       SELECT        *
268       FROM mtl_lot_uom_class_conversions
269       WHERE conversion_id = x_conversion_id
270       AND   organization_id = x_organization_id
271       FOR UPDATE OF organization_id NOWAIT;
272 
273     recinfo        c%ROWTYPE;
274     record_changed EXCEPTION;
275 
276   BEGIN
277     OPEN c;
278     FETCH c INTO recinfo;
279 
280     IF (c%NOTFOUND) THEN
281       CLOSE c;
282       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
283       app_exception.raise_exception;
284     END IF;
285 
286     CLOSE c;
287 
288 
289     IF (   (recinfo.organization_id = x_organization_id)
290            AND(recinfo.conversion_id = x_conversion_id)
291            AND(recinfo.inventory_item_id = x_inventory_item_id)
292            AND((recinfo.lot_number = x_lot_number)
293                OR((recinfo.lot_number IS NULL)
294                   AND(x_lot_number IS NULL)))
295            AND ((recinfo.from_unit_of_measure = x_from_unit_of_measure)
296                OR ((recinfo.from_unit_of_measure IS NULL)
297                   AND (x_from_unit_of_measure IS NULL)))
298            AND ((recinfo.from_uom_code = x_from_uom_code)
299                OR ((recinfo.from_uom_code IS NULL)
300                   AND (x_from_uom_code IS  NULL)))
301            AND((recinfo.from_uom_class = x_from_uom_class)
302                OR((recinfo.from_uom_class IS NULL)
303                   AND(x_from_uom_class IS NULL)))
304            AND((recinfo.to_unit_of_measure = x_to_unit_of_measure)
305                OR((recinfo.to_unit_of_measure IS NULL)
306                   AND(x_to_unit_of_measure IS NULL)))
307            AND((recinfo.to_uom_code = x_to_uom_code)
308                OR((recinfo.to_uom_code IS NULL)
309                   AND(x_to_uom_code IS NULL)))
310            AND((recinfo.to_uom_class = x_to_uom_class)
311                OR((recinfo.to_uom_class IS NULL)
312                   AND(x_to_uom_class IS NULL)))
313            AND(recinfo.conversion_rate = x_conversion_rate)
314            AND((recinfo.disable_date = x_disable_date)
315                OR((recinfo.disable_date IS NULL)
316                   AND(x_disable_date IS NULL)))
317            AND((recinfo.event_spec_disp_id = x_event_spec_disp_id)
318                OR((recinfo.event_spec_disp_id IS NULL)
319                   AND(x_event_spec_disp_id IS NULL)))
320           ) THEN
321          RETURN;
322       ELSE
323          RAISE record_changed;
324     END IF;
325 
326   EXCEPTION
327     WHEN record_changed THEN
328       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
329       app_exception.raise_exception;
330   END lock_row;
331 
332 
333 
334 END MTL_LOT_UOM_CONV_PKG;