1 package body AHL_WORKORDER_MTL_TXNS_PKG as
2 /* $Header: AHLLWMTB.pls 120.0 2005/05/26 01:44:29 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_WORKORDER_MTL_TXN_ID Out Nocopy NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_WORKORDER_OPERATION_ID in NUMBER,
8 X_MATERIAL_TRANSACTION_ID in NUMBER,
9 X_COLLECTION_ID in NUMBER,
10 X_STATUS_ID in NUMBER,
11 X_NON_ROUTINE_WORKORDER_ID IN NUMBER,
12 X_ORGANIZATION_ID in NUMBER,
13 X_INVENTORY_ITEM_ID in NUMBER,
14 X_REVISION in VARCHAR2,
15 X_LOT_NUMBER in VARCHAR2,
16 X_SERIAL_NUMBER in VARCHAR2,
17 X_LOCATOR_ID in NUMBER,
18 X_SUBINVENTORY_CODE in VARCHAR2,
19 X_QUANTITY in NUMBER,
20 X_TRANSACTION_TYPE_ID in NUMBER,
21 X_UOM in VARCHAR2,
22 X_RECEPIENT_ID IN NUMBER,
23 X_INSTANCE_ID IN NUMBER,
24 X_TRANSACTION_DATE IN DATE,
25 X_PRIMARY_UOM_QUANTITY IN NUMBER,
26 X_ATTRIBUTE_CATEGORY in VARCHAR2 ,
27 X_ATTRIBUTE1 in VARCHAR2 ,
28 X_ATTRIBUTE2 in VARCHAR2 ,
29 X_ATTRIBUTE3 in VARCHAR2 ,
30 X_ATTRIBUTE4 in VARCHAR2 ,
31 X_ATTRIBUTE5 in VARCHAR2 ,
32 X_ATTRIBUTE6 in VARCHAR2 ,
33 X_ATTRIBUTE7 in VARCHAR2 ,
34 X_ATTRIBUTE8 in VARCHAR2 ,
35 X_ATTRIBUTE9 in VARCHAR2 ,
36 X_ATTRIBUTE10 in VARCHAR2 ,
37 X_ATTRIBUTE11 in VARCHAR2 ,
38 X_ATTRIBUTE12 in VARCHAR2 ,
39 X_ATTRIBUTE13 in VARCHAR2 ,
40 X_ATTRIBUTE14 in VARCHAR2 ,
41 X_ATTRIBUTE15 in VARCHAR2 ,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER)
47 is
48 begin
49
50 INSERT INTO AHL_WORKORDER_MTL_TXNS (
51 WORKORDER_MTL_TXN_ID,
52 OBJECT_VERSION_NUMBER,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_LOGIN,
58 WORKORDER_OPERATION_ID,
59 MATERIAL_TRANSACTION_ID,
60 COLLECTION_ID,
61 STATUS_ID,
62 NON_ROUTINE_WORKORDER_ID,
63 ORGANIZATION_ID,
64 INVENTORY_ITEM_ID,
65 REVISION,
66 LOT_NUMBER,
67 SERIAL_NUMBER,
68 LOCATOR_ID,
69 SUBINVENTORY_CODE,
70 QUANTITY,
71 TRANSACTION_TYPE_ID,
72 UOM,
73 RECEPIENT_ID,
74 INSTANCE_ID,
75 TRANSACTION_DATE,
76 PRIMARY_UOM_QTY,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 ATTRIBUTE3,
81 ATTRIBUTE4,
82 ATTRIBUTE5,
83 ATTRIBUTE6,
84 ATTRIBUTE7,
85 ATTRIBUTE8,
86 ATTRIBUTE9,
87 ATTRIBUTE10,
88 ATTRIBUTE11,
89 ATTRIBUTE12,
90 ATTRIBUTE13,
91 ATTRIBUTE14,
92 ATTRIBUTE15)
93 VALUES (
94 AHL_WORKORDER_MTL_TXNS_S.NextVal,
95 X_OBJECT_VERSION_NUMBER,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATED_BY,
98 X_CREATION_DATE,
99 X_CREATED_BY,
100 X_LAST_UPDATE_LOGIN,
101 X_WORKORDER_OPERATION_ID,
102 X_MATERIAL_TRANSACTION_ID,
103 X_COLLECTION_ID,
104 X_STATUS_ID,
105 X_NON_ROUTINE_WORKORDER_ID,
106 X_ORGANIZATION_ID,
107 X_INVENTORY_ITEM_ID,
108 X_REVISION,
109 X_LOT_NUMBER,
110 X_SERIAL_NUMBER,
111 X_LOCATOR_ID,
112 X_SUBINVENTORY_CODE,
113 X_QUANTITY,
114 X_TRANSACTION_TYPE_ID,
115 X_UOM,
116 X_RECEPIENT_ID,
117 X_INSTANCE_ID,
118 X_TRANSACTION_DATE,
119 X_PRIMARY_UOM_QUANTITY,
120 X_ATTRIBUTE_CATEGORY,
121 X_ATTRIBUTE1,
122 X_ATTRIBUTE2,
123 X_ATTRIBUTE3,
124 X_ATTRIBUTE4,
125 X_ATTRIBUTE5,
126 X_ATTRIBUTE6,
127 X_ATTRIBUTE7,
128 X_ATTRIBUTE8,
129 X_ATTRIBUTE9,
130 X_ATTRIBUTE10,
131 X_ATTRIBUTE11,
132 X_ATTRIBUTE12,
133 X_ATTRIBUTE13,
134 X_ATTRIBUTE14,
135 X_ATTRIBUTE15)
136 RETURNING WORKORDER_MTL_TXN_ID INTO X_WORKORDER_MTL_TXN_ID;
137
138 end INSERT_ROW;
139
140 procedure LOCK_ROW (
141 X_WORKORDER_MTL_TXN_ID in NUMBER,
142 X_OBJECT_VERSION_NUMBER in NUMBER,
143 X_ATTRIBUTE_CATEGORY in VARCHAR2,
144 X_ATTRIBUTE1 in VARCHAR2,
145 X_ATTRIBUTE2 in VARCHAR2,
146 X_ATTRIBUTE3 in VARCHAR2,
147 X_ATTRIBUTE4 in VARCHAR2,
148 X_ATTRIBUTE5 in VARCHAR2,
149 X_ATTRIBUTE6 in VARCHAR2,
150 X_ATTRIBUTE7 in VARCHAR2,
151 X_ATTRIBUTE8 in VARCHAR2,
152 X_ATTRIBUTE9 in VARCHAR2,
153 X_ATTRIBUTE10 in VARCHAR2,
154 X_ATTRIBUTE11 in VARCHAR2,
155 X_ATTRIBUTE12 in VARCHAR2,
156 X_ATTRIBUTE13 in VARCHAR2,
157 X_ATTRIBUTE14 in VARCHAR2,
158 X_ATTRIBUTE15 in VARCHAR2
159 ) is
160 cursor c is select
161 OBJECT_VERSION_NUMBER,
162 WORKORDER_MTL_TXN_ID,
163 ATTRIBUTE_CATEGORY,
164 ATTRIBUTE1,
165 ATTRIBUTE2,
166 ATTRIBUTE3,
167 ATTRIBUTE4,
168 ATTRIBUTE5,
169 ATTRIBUTE6,
170 ATTRIBUTE7,
171 ATTRIBUTE8,
172 ATTRIBUTE9,
173 ATTRIBUTE10,
174 ATTRIBUTE11,
175 ATTRIBUTE12,
176 ATTRIBUTE13,
177 ATTRIBUTE14,
178 ATTRIBUTE15
179 from AHL_WORKORDER_MTL_TXNS
180 where WORKORDER_MTL_TXN_ID = X_WORKORDER_MTL_TXN_ID
181 for update of WORKORDER_MTL_TXN_ID nowait;
182 recinfo c%rowtype;
183
184 begin
185 open c;
186 fetch c into recinfo;
187 if (c%notfound) then
188 close c;
189 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
190 app_exception.raise_exception;
191 end if;
192 close c;
193 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
194 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
195 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
196 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
197 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
198 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
199 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
200 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
201 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
202 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
203 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
204 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
205 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
206 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
207 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
208 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
209 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
210 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
211 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
212 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
213 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
214 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
215 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
216 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
217 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
218 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
219 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
220 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
221 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
222 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
223 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
224 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
225 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
226 ) then
227 null;
228 else
229 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
230 app_exception.raise_exception;
231 end if;
232
233 return;
234 end LOCK_ROW;
235
236 procedure UPDATE_ROW (
237 X_WORKORDER_MTL_TXN_ID in NUMBER,
238 X_OBJECT_VERSION_NUMBER in NUMBER,
239 X_WORKORDER_OPERATION_ID in NUMBER,
240 X_MATERIAL_TRANSACTION_ID in NUMBER,
241 X_COLLECTION_ID in NUMBER,
242 X_STATUS_ID in NUMBER,
243 X_NON_ROUTINE_WORKORDER_ID IN NUMBER,
244 X_ORGANIZATION_ID in NUMBER,
245 X_INVENTORY_ITEM_ID in NUMBER,
246 X_REVISION in VARCHAR2,
247 X_LOT_NUMBER in VARCHAR2,
248 X_SERIAL_NUMBER in VARCHAR2,
249 X_LOCATOR_ID in NUMBER,
250 X_SUBINVENTORY_CODE in VARCHAR2,
251 X_QUANTITY in NUMBER,
252 X_TRANSACTION_TYPE_ID in NUMBER,
253 X_UOM in VARCHAR2,
254 X_ATTRIBUTE_CATEGORY in VARCHAR2,
255 X_ATTRIBUTE1 in VARCHAR2,
256 X_ATTRIBUTE2 in VARCHAR2,
257 X_ATTRIBUTE3 in VARCHAR2,
258 X_ATTRIBUTE4 in VARCHAR2,
259 X_ATTRIBUTE5 in VARCHAR2,
260 X_ATTRIBUTE6 in VARCHAR2,
261 X_ATTRIBUTE7 in VARCHAR2,
262 X_ATTRIBUTE8 in VARCHAR2,
263 X_ATTRIBUTE9 in VARCHAR2,
264 X_ATTRIBUTE10 in VARCHAR2,
265 X_ATTRIBUTE11 in VARCHAR2,
266 X_ATTRIBUTE12 in VARCHAR2,
267 X_ATTRIBUTE13 in VARCHAR2,
268 X_ATTRIBUTE14 in VARCHAR2,
269 X_ATTRIBUTE15 in VARCHAR2,
270 X_CREATION_DATE in DATE,
271 X_CREATED_BY in NUMBER,
272 X_LAST_UPDATE_DATE in DATE,
273 X_LAST_UPDATED_BY in NUMBER,
274 X_LAST_UPDATE_LOGIN in NUMBER)
275 is
276 begin
277 update AHL_WORKORDER_MTL_TXNS set
278 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
279 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
280 ATTRIBUTE1 = X_ATTRIBUTE1,
281 ATTRIBUTE2 = X_ATTRIBUTE2,
282 ATTRIBUTE3 = X_ATTRIBUTE3,
283 ATTRIBUTE4 = X_ATTRIBUTE4,
284 ATTRIBUTE5 = X_ATTRIBUTE5,
285 ATTRIBUTE6 = X_ATTRIBUTE6,
286 ATTRIBUTE7 = X_ATTRIBUTE7,
287 ATTRIBUTE8 = X_ATTRIBUTE8,
288 ATTRIBUTE9 = X_ATTRIBUTE9,
289 ATTRIBUTE10 = X_ATTRIBUTE10,
290 ATTRIBUTE11 = X_ATTRIBUTE11,
291 ATTRIBUTE12 = X_ATTRIBUTE12,
292 ATTRIBUTE13 = X_ATTRIBUTE13,
293 ATTRIBUTE14 = X_ATTRIBUTE14,
294 ATTRIBUTE15 = X_ATTRIBUTE15,
295 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
296 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
297 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
298 where WORKORDER_MTL_TXN_ID = X_WORKORDER_MTL_TXN_ID;
299
300 if (sql%notfound) then
301 raise no_data_found;
302 end if;
303
304 end UPDATE_ROW;
305
306 procedure DELETE_ROW (
307 X_WORKORDER_MTL_TXN_ID in NUMBER
308 ) is
309 begin
310
311 delete from AHL_WORKORDER_MTL_TXNS
312 where WORKORDER_MTL_TXN_ID = X_WORKORDER_MTL_TXN_ID;
313
314 if (sql%notfound) then
315 raise no_data_found;
316 end if;
317 end DELETE_ROW;
318
319
320 end AHL_WORKORDER_MTL_TXNS_PKG;
[Home] [Help]