1 package body INV_CONSUMPTION_PKG as
2 /* $Header: INVCNTHB.pls 115.4 2002/12/20 20:23:39 satkumar noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN out NOCOPY varchar2,
5 X_TRANSACTION_TYPE_ID IN NUMBER,
6 X_ORGANIZATION_ID IN NUMBER,
7 X_SUBINVENTORY_CODE IN VARCHAR2,
8 X_XFER_SUBINVENTORY_CODE IN VARCHAR2,
9 x_from_locator_id IN NUMBER,
10 x_to_locator_id IN NUMBER,
11 X_INVENTORY_ITEM_ID IN NUMBER,
12 x_owning_organization_id IN NUMBER,
13 x_planning_organization_id IN NUMBER,
14 x_consume_consigned_flag IN VARCHAR2,
15 X_CONSUME_VMI_FLAG IN VARCHAR2,
16 X_ATTRIBUTE_CATEGORY in VARCHAR2,
17 X_ATTRIBUTE1 in VARCHAR2,
18 X_ATTRIBUTE2 in VARCHAR2,
19 X_ATTRIBUTE3 in VARCHAR2,
20 X_ATTRIBUTE4 in VARCHAR2,
21 X_ATTRIBUTE5 in VARCHAR2,
22 X_ATTRIBUTE6 in VARCHAR2,
23 X_ATTRIBUTE7 in VARCHAR2,
24 X_ATTRIBUTE8 in VARCHAR2,
25 X_ATTRIBUTE9 in VARCHAR2,
26 X_ATTRIBUTE10 in VARCHAR2,
27 X_ATTRIBUTE11 in VARCHAR2,
28 X_ATTRIBUTE12 in VARCHAR2,
29 X_ATTRIBUTE13 in VARCHAR2,
30 X_ATTRIBUTE14 in VARCHAR2,
31 X_ATTRIBUTE15 in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER,
37 x_weight IN number
38 ) is
39
40 CURSOR C IS SELECT rowid FROM MTL_CONSUMPTION_DEFINITION
41 WHERE transaction_type_id = x_transaction_type_id
42 and nvl(ORGANIZATION_ID, nvl(X_ORGANIZATION_ID,-999))= nvl(X_ORGANIZATION_ID,-999)
43 and nvl(SUBINVENTORY_CODE, nvl(X_SUBINVENTORY_CODE,-999)) = nvl(X_SUBINVENTORY_CODE,-999)
44 and nvl(XFER_SUBINVENTORY_CODE, nvl(X_XFER_SUBINVENTORY_CODE, -999) )
45 = nvl(X_XFER_SUBINVENTORY_CODE, -999)
46 and nvl(FROM_LOCATOR_ID, nvl(X_FROM_LOCATOR_ID, -999)) = nvl(X_FROM_LOCATOR_ID, -999)
47 and nvl(TO_LOCATOR_ID, nvl(X_TO_LOCATOR_ID, -999)) = nvl(X_TO_LOCATOR_ID, -999)
48 and nvl(INVENTORY_ITEM_ID,nvl(X_INVENTORY_ITEM_ID ,- 999)) = nvl(X_INVENTORY_ITEM_ID ,-999)
49 and nvl(weight,nvl(X_weight ,- 999)) = nvl( X_weight ,-999)
50 and nvl(OWNING_ORGANIZATION_ID, nvl(X_OWNING_ORGANIZATION_ID, -999) )
51 = nvl(X_OWNING_ORGANIZATION_ID, -999)
52 and nvl(PLANNING_ORGANIZATION_ID,nvl(X_PLANNING_ORGANIZATION_ID,-999))=nvl(X_PLANNING_ORGANIZATION_ID,-999);
53
54
55 begin
56 insert into MTL_CONSUMPTION_DEFINITION (
57 TRANSACTION_TYPE_ID,
58 ORGANIZATION_ID,
59 SUBINVENTORY_CODE,
60 XFER_SUBINVENTORY_CODE,
61 from_locator_id,
62 to_locator_id,
63 INVENTORY_ITEM_ID,
64 owning_organization_id,
65 planning_organization_id,
66 consume_consigned_flag,
67 CONSUME_VMI_FLAG,
68 ATTRIBUTE_CATEGORY,
69 attribute1,
70 attribute2,
71 attribute3,
72 attribute4,
73 attribute5,
74 attribute6,
75 attribute7,
76 attribute8,
77 attribute9,
78 attribute10,
79 attribute11,
80 attribute12,
81 attribute13,
82 attribute14,
83 attribute15,
84 creation_date,
85 created_by,
86 last_update_date,
87 last_updated_by,
88 last_update_login,
89 WEIGHT)
90 values (
91 X_TRANSACTION_TYPE_ID,
92 X_ORGANIZATION_ID,
93 X_SUBINVENTORY_CODE,
94 X_XFER_SUBINVENTORY_CODE,
95 X_from_locator_id,
96 X_to_locator_id,
97 X_INVENTORY_ITEM_ID,
98 X_owning_organization_id,
99 X_planning_organization_id,
100 X_consume_consigned_flag,
101 X_CONSUME_VMI_FLAG,
102 X_ATTRIBUTE_CATEGORY,
103 X_attribute1,
104 X_attribute2,
105 X_attribute3,
106 X_attribute4,
107 X_attribute5,
108 X_attribute6,
109 X_attribute7,
110 X_attribute8,
111 X_attribute9,
112 X_attribute10,
113 X_attribute11,
114 X_attribute12,
115 X_attribute13,
116 X_attribute14,
117 X_attribute15,
118 X_creation_date,
119 x_created_by,
120 X_last_update_date,
121 X_last_updated_by,
122 x_last_update_login,
123 X_WEIGHT);
124
125 OPEN C;
126 FETCH C INTO X_Rowid;
127 if (C%NOTFOUND) then
128 CLOSE C;
129 Raise NO_DATA_FOUND;
130 end if;
131 CLOSE C;
132
133 end INSERT_ROW;
134
135 procedure LOCK_ROW (
136 X_ROWID in varchar2,
137 X_TRANSACTION_TYPE_ID IN NUMBER,
138 X_ORGANIZATION_ID IN NUMBER,
139 X_SUBINVENTORY_CODE IN VARCHAR2,
140 X_XFER_SUBINVENTORY_CODE IN VARCHAR2,
141 x_from_locator_id IN NUMBER,
142 x_to_locator_id IN NUMBER,
143 X_INVENTORY_ITEM_ID IN NUMBER,
144 x_owning_organization_id IN NUMBER,
145 x_planning_organization_id IN NUMBER,
146 x_consume_consigned_flag IN VARCHAR2,
147 X_CONSUME_VMI_FLAG IN VARCHAR2,
148 X_ATTRIBUTE_CATEGORY in VARCHAR2,
149 X_ATTRIBUTE1 in VARCHAR2,
150 X_ATTRIBUTE2 in VARCHAR2,
151 X_ATTRIBUTE3 in VARCHAR2,
152 X_ATTRIBUTE4 in VARCHAR2,
153 X_ATTRIBUTE5 in VARCHAR2,
154 X_ATTRIBUTE6 in VARCHAR2,
155 X_ATTRIBUTE7 in VARCHAR2,
156 X_ATTRIBUTE8 in VARCHAR2,
157 X_ATTRIBUTE9 in VARCHAR2,
158 X_ATTRIBUTE10 in VARCHAR2,
159 X_ATTRIBUTE11 in VARCHAR2,
160 X_ATTRIBUTE12 in VARCHAR2,
161 X_ATTRIBUTE13 in VARCHAR2,
162 X_ATTRIBUTE14 in VARCHAR2,
163 X_ATTRIBUTE15 in VARCHAR2,
164 x_weight IN NUMBER
165 ) is
166 cursor c is SELECT
167 TRANSACTION_TYPE_ID,
168 ORGANIZATION_ID,
169 SUBINVENTORY_CODE,
170 XFER_SUBINVENTORY_CODE,
171 from_locator_id,
172 to_locator_id,
173 INVENTORY_ITEM_ID,
174 owning_organization_id,
175 planning_organization_id,
176 consume_consigned_flag,
177 CONSUME_VMI_FLAG,
178 weight,
179 ATTRIBUTE_CATEGORY,
180 attribute1,
181 attribute2,
182 attribute3,
183 attribute4,
184 attribute5,
185 attribute6,
186 attribute7,
187 attribute8,
188 attribute9,
189 attribute10,
190 attribute11,
191 attribute12,
192 attribute13,
193 attribute14,
194 attribute15
195 from MTL_CONSUMPTION_DEFINITION
196 where ROWID = X_ROWID
197 for update OF TRANSACTION_TYPE_ID nowait;
198 recinfo c%rowtype;
199
200 begin
201 open c;
202 fetch c into recinfo;
203 if (c%notfound) then
204 close c;
205 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 end if;
208 close c;
209
210 if ( (recinfo.TRANSACTION_TYPE_ID = X_TRANSACTION_TYPE_ID)
211 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
212 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
213 AND ((recinfo.SUBINVENTORY_CODE = X_SUBINVENTORY_CODE)
214 OR ((recinfo.SUBINVENTORY_CODE is null) AND (X_SUBINVENTORY_CODE is null)))
215 AND ((recinfo.XFER_SUBINVENTORY_CODE = x_xfer_SUBINVENTORY_CODE)
216 OR ((recinfo.XFER_SUBINVENTORY_CODE is null) AND (X_XFER_SUBINVENTORY_CODE is null)))
217 AND ((recinfo.from_locator_id = x_from_locator_id)
218 OR ((recinfo.from_locator_id is null) AND (X_from_locator_id is null)))
219 AND ((recinfo.TO_locator_id = x_TO_locator_id)
220 OR ((recinfo.TO_locator_id is null) AND (X_TO_locator_id is null)))
221 AND ((recinfo.INVENTORY_ITEM_ID = x_inventory_item_id)
222 OR ((recinfo.inventory_item_id is null) AND (X_INVENTORY_ITEM_ID is null)))
223 AND ((recinfo.owning_organization_id = x_owning_organization_id)
224 OR ((recinfo.owning_organization_id is null) AND (x_owning_organization_id is null)))
225 AND ((recinfo.planning_organization_id = x_planning_organization_id)
226 OR ((recinfo.planning_organization_id is null) AND (x_planning_organization_id is null)))
227 AND ((recinfo.consume_consigned_flag = x_consume_consigned_flag)
228 OR ((recinfo.consume_consigned_flag is null) AND (x_consume_consigned_flag is null)))
229 AND ((recinfo.CONSUME_VMI_FLAG = x_CONSUME_VMI_FLAG)
230 OR ((recinfo.CONSUME_VMI_FLAG is null) AND (x_CONSUME_VMI_FLAG is null)))
231 AND ((recinfo.weight = x_weight)
232 OR ((recinfo.weight is null) AND (x_weight is null)))
233 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
234 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
235 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
236 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
237 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
238 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
239 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
240 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
241 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
242 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
243 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
244 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
245 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
246 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
247 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
248 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
249 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
250 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
251 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
252 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
253 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
254 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
255 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
256 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
257 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
258 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
259 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
260 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
261 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
262 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
263 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
264 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
265 ) then
266 return;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271 end LOCK_ROW;
272
273 procedure UPDATE_ROW (
274 x_rowid IN varchar2,
275 X_TRANSACTION_TYPE_ID IN NUMBER,
276 X_ORGANIZATION_ID IN NUMBER,
277 X_SUBINVENTORY_CODE IN VARCHAR2,
278 X_XFER_SUBINVENTORY_CODE IN VARCHAR2,
279 x_from_locator_id IN NUMBER,
280 x_to_locator_id IN NUMBER,
281 X_INVENTORY_ITEM_ID IN NUMBER,
282 x_owning_organization_id IN NUMBER,
283 x_planning_organization_id IN NUMBER,
284 x_consume_consigned_flag IN VARCHAR2,
285 X_CONSUME_VMI_FLAG IN VARCHAR2,
286 X_ATTRIBUTE_CATEGORY in VARCHAR2,
287 X_ATTRIBUTE1 in VARCHAR2,
288 X_ATTRIBUTE2 in VARCHAR2,
289 X_ATTRIBUTE3 in VARCHAR2,
290 X_ATTRIBUTE4 in VARCHAR2,
291 X_ATTRIBUTE5 in VARCHAR2,
292 X_ATTRIBUTE6 in VARCHAR2,
293 X_ATTRIBUTE7 in VARCHAR2,
294 X_ATTRIBUTE8 in VARCHAR2,
295 X_ATTRIBUTE9 in VARCHAR2,
296 X_ATTRIBUTE10 in VARCHAR2,
297 X_ATTRIBUTE11 in VARCHAR2,
298 X_ATTRIBUTE12 in VARCHAR2,
299 X_ATTRIBUTE13 in VARCHAR2,
300 X_ATTRIBUTE14 in VARCHAR2,
301 X_ATTRIBUTE15 in VARCHAR2,
302 X_LAST_UPDATE_DATE in DATE,
303 X_LAST_UPDATED_BY in NUMBER,
304 X_LAST_UPDATE_LOGIN in NUMBER,
305 x_weight IN number
306 ) is
307
308 begin
309 update MTL_CONSUMPTION_DEFINITION mcd set
310 transaction_type_id = X_transaction_type_id,
311 organization_id = x_organization_id,
312 subinventory_code = x_subinventory_code,
313 xfer_subinventory_code = x_XFER_SUBINVENTORY_CODE,
314 from_locator_id = x_from_locator_id,
315 to_locator_id = x_to_locator_id,
316 inventory_item_id = x_INVENTORY_ITEM_ID,
317 owning_organization_id = x_owning_organization_id,
318 planning_organization_id = x_planning_organization_id,
319 consume_consigned_flag = x_consume_consigned_flag,
320 consume_vmi_flag = x_consume_vmi_flag,
321 weight = x_weight,
322 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
323 ATTRIBUTE1 = X_ATTRIBUTE1,
324 ATTRIBUTE2 = X_ATTRIBUTE2,
325 ATTRIBUTE3 = X_ATTRIBUTE3,
326 ATTRIBUTE4 = X_ATTRIBUTE4,
327 ATTRIBUTE5 = X_ATTRIBUTE5,
328 ATTRIBUTE6 = X_ATTRIBUTE6,
329 ATTRIBUTE7 = X_ATTRIBUTE7,
330 ATTRIBUTE8 = X_ATTRIBUTE8,
331 ATTRIBUTE9 = X_ATTRIBUTE9,
332 ATTRIBUTE10 = X_ATTRIBUTE10,
333 ATTRIBUTE11 = X_ATTRIBUTE11,
334 ATTRIBUTE12 = X_ATTRIBUTE12,
335 ATTRIBUTE13 = X_ATTRIBUTE13,
336 ATTRIBUTE14 = X_ATTRIBUTE14,
337 ATTRIBUTE15 = X_ATTRIBUTE15,
338 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
339 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
340 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
341 WHERE mcd.ROWID = X_ROWID;
342
343 if (sql%notfound) then
344 raise no_data_found;
345 end if;
346
347 end UPDATE_ROW;
348
349 procedure DELETE_ROW (
350 X_ROWID in varchar2
351 ) is
352 begin
353 delete from mtl_consumption_definition MCD
354 where mcd.ROWID = X_ROWID;
355
356 if (sql%notfound) then
357 raise no_data_found;
358 end if;
359
360 end DELETE_ROW;
361
362 end INV_CONSUMPTION_PKG;
[Home] [Help]