1 PACKAGE BODY AHL_PART_CHANGES_PKG AS
2 /* $Header: AHLLPCKB.pls 120.1 2008/02/06 14:51:40 sracha ship $ */
3 -- Purpose: Briefly explain the functionality of the package body
4 -- Contains common table handler procedures update, insert to be used by parts change.
5 -- Person Date Comments
6 -- sangigup 10/10/02
7
8 -- --------- ------ -----------------------------------------
9 --This procedure will insert a row in teh table
10 procedure INSERT_ROW (
11 X_ROWID in out nocopy VARCHAR2,
12 X_PART_CHANGE_ID in out nocopy NUMBER,
13 X_UNIT_CONFIG_HEADER_ID in NUMBER,
14 X_REMOVED_INSTANCE_ID in NUMBER,
15 X_MC_RELATIONSHIP_ID in NUMBER,
16 X_REMOVAL_CODE in VARCHAR2,
17 --X_STATUS_ID in NUMBER,
18 X_REMOVAL_REASON_ID in NUMBER,
19 X_INSTALLED_INSTANCE_ID in NUMBER,
20 X_WORKORDER_OPERATION_ID in NUMBER,
21 X_ATTRIBUTE_CATEGORY in VARCHAR2,
22 X_ATTRIBUTE1 in VARCHAR2,
23 X_ATTRIBUTE2 in VARCHAR2,
24 X_ATTRIBUTE3 in VARCHAR2,
25 X_ATTRIBUTE4 in VARCHAR2,
26 X_ATTRIBUTE5 in VARCHAR2,
27 X_ATTRIBUTE6 in VARCHAR2,
28 X_ATTRIBUTE7 in VARCHAR2,
29 X_ATTRIBUTE8 in VARCHAR2,
30 X_ATTRIBUTE9 in VARCHAR2,
31 X_ATTRIBUTE10 in VARCHAR2,
32 X_ATTRIBUTE11 in VARCHAR2,
33 X_ATTRIBUTE12 in VARCHAR2,
34 X_ATTRIBUTE13 in VARCHAR2,
35 X_ATTRIBUTE14 in VARCHAR2,
36 X_ATTRIBUTE15 in VARCHAR2,
37 X_OBJECT_VERSION_NUMBER in NUMBER,
38 --X_NON_ROUTINE_WORKORDER_ID in NUMBER,
39 --X_COLLECTION_ID in NUMBER,
40 --X_WORKORDER_MTL_TXN_ID in NUMBER,
41 X_REMOVAL_DATE in DATE,
42 X_INSTALLATION_DATE in DATE,
43 X_ISSUE_MTL_TXN_ID in NUMBER,
44 X_RETURN_MTL_TXN_ID in NUMBER,
45 X_PART_CHANGE_TYPE in VARCHAR2,
46 X_CREATION_DATE in DATE,
47 X_CREATED_BY in NUMBER,
48 X_LAST_UPDATE_DATE in DATE,
49 X_LAST_UPDATED_BY in NUMBER,
50 X_LAST_UPDATE_LOGIN in NUMBER,
51 X_QUANTITY in NUMBER
52 ) is
53 cursor C is select ROWID from AHL_PART_CHANGES
54 where part_change_id = X_PART_CHANGE_ID
55 ;
56
57
58 begin
59 insert into AHL_PART_CHANGES (
60 part_change_id,
61 UNIT_CONFIG_HEADER_ID,
62 REMOVED_INSTANCE_ID,
63 MC_RELATIONSHIP_ID,
64 REMOVAL_CODE,
65 --STATUS_ID,
66 REMOVAL_REASON_ID,
67 INSTALLED_INSTANCE_ID,
68 WORKORDER_OPERATION_ID,
69 ATTRIBUTE_CATEGORY,
70 ATTRIBUTE1,
71 ATTRIBUTE2,
72 ATTRIBUTE3,
73 ATTRIBUTE4,
74 ATTRIBUTE5,
75 ATTRIBUTE6,
76 ATTRIBUTE7,
77 ATTRIBUTE8,
78 ATTRIBUTE9,
79 ATTRIBUTE10,
80 ATTRIBUTE11,
81 ATTRIBUTE12,
82 ATTRIBUTE13,
83 ATTRIBUTE14,
84 ATTRIBUTE15,
85 OBJECT_VERSION_NUMBER,
86 --NON_ROUTINE_WORKORDER_ID,
87 --COLLECTION_ID,
88 --WORKORDER_MTL_TXN_ID,
89 REMOVAL_DATE,
90 INSTALLATION_DATE,
91 ISSUE_MTL_TXN_ID,
92 RETURN_MTL_TXN_ID,
93 PART_CHANGE_TYPE,
94 CREATION_DATE,
95 CREATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_LOGIN,
99 QUANTITY
100 ) values (
101 ahl.ahl_part_changes_s.nextval,
102 X_UNIT_CONFIG_HEADER_ID,
103 X_REMOVED_INSTANCE_ID,
104 X_MC_RELATIONSHIP_ID,
105 X_REMOVAL_CODE,
106 --X_STATUS_ID,
107 X_REMOVAL_REASON_ID,
108 X_INSTALLED_INSTANCE_ID,
109 X_WORKORDER_OPERATION_ID,
110 X_ATTRIBUTE_CATEGORY,
111 X_ATTRIBUTE1,
112 X_ATTRIBUTE2,
113 X_ATTRIBUTE3,
114 X_ATTRIBUTE4,
115 X_ATTRIBUTE5,
116 X_ATTRIBUTE6,
117 X_ATTRIBUTE7,
118 X_ATTRIBUTE8,
119 X_ATTRIBUTE9,
120 X_ATTRIBUTE10,
121 X_ATTRIBUTE11,
122 X_ATTRIBUTE12,
123 X_ATTRIBUTE13,
124 X_ATTRIBUTE14,
125 X_ATTRIBUTE15,
126 X_OBJECT_VERSION_NUMBER,
127 --X_NON_ROUTINE_WORKORDER_ID,
128 --X_COLLECTION_ID,
129 --X_WORKORDER_MTL_TXN_ID,
130 X_REMOVAL_DATE,
131 X_INSTALLATION_DATE,
132 X_ISSUE_MTL_TXN_ID,
133 X_RETURN_MTL_TXN_ID,
134 X_PART_CHANGE_TYPE,
135 X_CREATION_DATE,
136 X_CREATED_BY,
137 X_LAST_UPDATE_DATE,
138 X_LAST_UPDATED_BY,
139 X_LAST_UPDATE_LOGIN,
140 X_QUANTITY)
141 RETURNING part_change_ID INTO X_part_change_id;
142
143 end INSERT_ROW;
144
145 procedure LOCK_ROW (
146 X_PART_CHANGE_ID in NUMBER,
147 X_UNIT_CONFIG_HEADER_ID in NUMBER,
148 X_REMOVED_INSTANCE_ID in NUMBER,
149 X_MC_RELATIONSHIP_ID in NUMBER,
150 X_REMOVAL_CODE in VARCHAR2,
151 --X_STATUS_ID in NUMBER,
152 X_REMOVAL_REASON_ID in NUMBER,
153 X_INSTALLED_INSTANCE_ID in NUMBER,
154 X_WORKORDER_OPERATION_ID in NUMBER,
155 X_ATTRIBUTE_CATEGORY in VARCHAR2,
156 X_ATTRIBUTE1 in VARCHAR2,
157 X_ATTRIBUTE2 in VARCHAR2,
158 X_ATTRIBUTE3 in VARCHAR2,
159 X_ATTRIBUTE4 in VARCHAR2,
160 X_ATTRIBUTE5 in VARCHAR2,
161 X_ATTRIBUTE6 in VARCHAR2,
162 X_ATTRIBUTE7 in VARCHAR2,
163 X_ATTRIBUTE8 in VARCHAR2,
164 X_ATTRIBUTE9 in VARCHAR2,
165 X_ATTRIBUTE10 in VARCHAR2,
166 X_ATTRIBUTE11 in VARCHAR2,
167 X_ATTRIBUTE12 in VARCHAR2,
168 X_ATTRIBUTE13 in VARCHAR2,
169 X_ATTRIBUTE14 in VARCHAR2,
170 X_ATTRIBUTE15 in VARCHAR2,
171 X_OBJECT_VERSION_NUMBER in NUMBER,
172 --X_NON_ROUTINE_WORKORDER_ID in NUMBER,
173 --X_COLLECTION_ID in NUMBER,
174 --X_WORKORDER_MTL_TXN_ID in NUMBER,
175 X_REMOVAL_DATE in DATE,
176 X_INSTALLATION_DATE in DATE,
177 X_ISSUE_MTL_TXN_ID in NUMBER,
178 X_RETURN_MTL_TXN_ID in NUMBER,
179 X_PART_CHANGE_TYPE in VARCHAR2,
180 X_QUANTITY in NUMBER
181 ) is
182 cursor c is select
183 UNIT_CONFIG_HEADER_ID,
184 REMOVED_INSTANCE_ID,
185 MC_RELATIONSHIP_ID,
186 REMOVAL_CODE,
187 --STATUS_ID,
188 REMOVAL_REASON_ID,
189 INSTALLED_INSTANCE_ID,
190 WORKORDER_OPERATION_ID,
191 ATTRIBUTE_CATEGORY,
192 ATTRIBUTE1,
193 ATTRIBUTE2,
194 ATTRIBUTE3,
195 ATTRIBUTE4,
196 ATTRIBUTE5,
197 ATTRIBUTE6,
198 ATTRIBUTE7,
199 ATTRIBUTE8,
200 ATTRIBUTE9,
201 ATTRIBUTE10,
202 ATTRIBUTE11,
203 ATTRIBUTE12,
204 ATTRIBUTE13,
205 ATTRIBUTE14,
206 ATTRIBUTE15,
207 OBJECT_VERSION_NUMBER,
208 --NON_ROUTINE_WORKORDER_ID,
209 --COLLECTION_ID,
210 --WORKORDER_MTL_TXN_ID,
211 REMOVAL_DATE,
212 INSTALLATION_DATE,
213 ISSUE_MTL_TXN_ID,
214 RETURN_MTL_TXN_ID,
215 PART_CHANGE_TYPE
216 from AHL_PART_CHANGES
217 where PART_CHANGE_ID = X_PART_CHANGE_ID
218 for update of PART_CHANGE_ID nowait;
219 recinfo c%rowtype;
220
221 begin
222 open c;
223 fetch c into recinfo;
224 if (c%notfound) then
225 close c;
226 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
227 app_exception.raise_exception;
228 end if;
229 close c;
230 if ( ((recinfo.UNIT_CONFIG_HEADER_ID = X_UNIT_CONFIG_HEADER_ID)
231 OR ((recinfo.UNIT_CONFIG_HEADER_ID is null) AND (X_UNIT_CONFIG_HEADER_ID is null)))
232 AND ((recinfo.REMOVED_INSTANCE_ID = X_REMOVED_INSTANCE_ID)
233 OR ((recinfo.REMOVED_INSTANCE_ID is null) AND (X_REMOVED_INSTANCE_ID is null)))
234 AND ((recinfo.MC_RELATIONSHIP_ID = X_MC_RELATIONSHIP_ID)
235 OR ((recinfo.MC_RELATIONSHIP_ID is null) AND (X_MC_RELATIONSHIP_ID is null)))
236 AND ((recinfo.REMOVAL_CODE = X_REMOVAL_CODE)
237 OR ((recinfo.REMOVAL_CODE is null) AND (X_REMOVAL_CODE is null)))
238 --AND ((recinfo.STATUS_ID = X_STATUS_ID)
239 -- OR ((recinfo.STATUS_ID is null) AND (X_STATUS_ID is null)))
240 AND ((recinfo.REMOVAL_REASON_ID = X_REMOVAL_REASON_ID)
241 OR ((recinfo.REMOVAL_REASON_ID is null) AND (X_REMOVAL_REASON_ID is null)))
242 AND ((recinfo.INSTALLED_INSTANCE_ID = X_INSTALLED_INSTANCE_ID)
243 OR ((recinfo.INSTALLED_INSTANCE_ID is null) AND (X_INSTALLED_INSTANCE_ID is null)))
244 AND (recinfo.WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID)
245 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
246 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
247 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
248 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
249 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
250 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
251 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
252 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
253 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
254 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
255 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
256 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
257 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
258 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
259 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
260 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
261 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
262 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
263 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
264 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
265 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
266 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
267 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
268 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
269 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
270 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
271 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
272 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
273 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
274 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
275 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
276 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
277 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
278 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
279 --AND ((recinfo.NON_ROUTINE_WORKORDER_ID = X_NON_ROUTINE_WORKORDER_ID)
280 -- OR ((recinfo.NON_ROUTINE_WORKORDER_ID is null) AND (X_NON_ROUTINE_WORKORDER_ID is null)))
281 --AND ((recinfo.COLLECTION_ID = X_COLLECTION_ID)
282 -- OR ((recinfo.COLLECTION_ID is null) AND (X_COLLECTION_ID is null)))
283 --AND ((recinfo.WORKORDER_MTL_TXN_ID = X_WORKORDER_MTL_TXN_ID)
284 -- OR ((recinfo.WORKORDER_MTL_TXN_ID is null) AND (X_WORKORDER_MTL_TXN_ID is null)))
285 AND ((recinfo.REMOVAL_DATE = X_REMOVAL_DATE)
286 OR ((recinfo.REMOVAL_DATE is null) AND (X_REMOVAL_DATE is null)))
287 AND ((recinfo.INSTALLATION_DATE = X_INSTALLATION_DATE)
288 OR ((recinfo.INSTALLATION_DATE is null) AND (X_INSTALLATION_DATE is null)))
289 AND ((recinfo.ISSUE_MTL_TXN_ID = X_ISSUE_MTL_TXN_ID)
290 OR ((recinfo.ISSUE_MTL_TXN_ID is null) AND (X_ISSUE_MTL_TXN_ID is null)))
291 AND ((recinfo.RETURN_MTL_TXN_ID = X_RETURN_MTL_TXN_ID)
292 OR ((recinfo.RETURN_MTL_TXN_ID is null) AND (X_RETURN_MTL_TXN_ID is null)))
293 AND ((recinfo.PART_CHANGE_TYPE = X_PART_CHANGE_TYPE)
294 OR ((recinfo.PART_CHANGE_TYPE is null) AND (X_PART_CHANGE_TYPE is null)))
295 ) then
296 null;
297 else
298 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
299 app_exception.raise_exception;
300 end if;
301
302 return;
303 end LOCK_ROW;
304
305 procedure UPDATE_ROW (
306 X_PART_CHANGE_ID in NUMBER,
307 X_UNIT_CONFIG_HEADER_ID in NUMBER,
308 X_REMOVED_INSTANCE_ID in NUMBER,
309 X_MC_RELATIONSHIP_ID in NUMBER,
310 X_REMOVAL_CODE in VARCHAR2,
311 --X_STATUS_ID in NUMBER,
312 X_REMOVAL_REASON_ID in NUMBER,
313 X_INSTALLED_INSTANCE_ID in NUMBER,
314 X_WORKORDER_OPERATION_ID in NUMBER,
315 X_ATTRIBUTE_CATEGORY in VARCHAR2,
316 X_ATTRIBUTE1 in VARCHAR2,
317 X_ATTRIBUTE2 in VARCHAR2,
318 X_ATTRIBUTE3 in VARCHAR2,
319 X_ATTRIBUTE4 in VARCHAR2,
320 X_ATTRIBUTE5 in VARCHAR2,
321 X_ATTRIBUTE6 in VARCHAR2,
322 X_ATTRIBUTE7 in VARCHAR2,
323 X_ATTRIBUTE8 in VARCHAR2,
324 X_ATTRIBUTE9 in VARCHAR2,
325 X_ATTRIBUTE10 in VARCHAR2,
326 X_ATTRIBUTE11 in VARCHAR2,
327 X_ATTRIBUTE12 in VARCHAR2,
328 X_ATTRIBUTE13 in VARCHAR2,
329 X_ATTRIBUTE14 in VARCHAR2,
330 X_ATTRIBUTE15 in VARCHAR2,
331 X_OBJECT_VERSION_NUMBER in NUMBER,
332 --X_NON_ROUTINE_WORKORDER_ID in NUMBER,
333 --X_COLLECTION_ID in NUMBER,
334 --X_WORKORDER_MTL_TXN_ID in NUMBER,
335 X_REMOVAL_DATE in DATE,
336 X_INSTALLATION_DATE in DATE,
337 X_ISSUE_MTL_TXN_ID in NUMBER,
338 X_RETURN_MTL_TXN_ID in NUMBER,
339 X_PART_CHANGE_TYPE in VARCHAR2,
340 X_LAST_UPDATE_DATE in DATE,
341 X_LAST_UPDATED_BY in NUMBER,
342 X_LAST_UPDATE_LOGIN in NUMBER,
343 X_QUANTITY in NUMBER
344 ) is
345 begin
346 update AHL_PART_CHANGES set
347 UNIT_CONFIG_HEADER_ID = X_UNIT_CONFIG_HEADER_ID,
348 REMOVED_INSTANCE_ID = X_REMOVED_INSTANCE_ID,
349 MC_RELATIONSHIP_ID = X_MC_RELATIONSHIP_ID,
350 REMOVAL_CODE = X_REMOVAL_CODE,
351 --STATUS_ID = X_STATUS_ID,
352 REMOVAL_REASON_ID = X_REMOVAL_REASON_ID,
353 INSTALLED_INSTANCE_ID = X_INSTALLED_INSTANCE_ID,
354 WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID,
355 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
356 ATTRIBUTE1 = X_ATTRIBUTE1,
357 ATTRIBUTE2 = X_ATTRIBUTE2,
358 ATTRIBUTE3 = X_ATTRIBUTE3,
359 ATTRIBUTE4 = X_ATTRIBUTE4,
360 ATTRIBUTE5 = X_ATTRIBUTE5,
361 ATTRIBUTE6 = X_ATTRIBUTE6,
362 ATTRIBUTE7 = X_ATTRIBUTE7,
363 ATTRIBUTE8 = X_ATTRIBUTE8,
364 ATTRIBUTE9 = X_ATTRIBUTE9,
365 ATTRIBUTE10 = X_ATTRIBUTE10,
366 ATTRIBUTE11 = X_ATTRIBUTE11,
367 ATTRIBUTE12 = X_ATTRIBUTE12,
368 ATTRIBUTE13 = X_ATTRIBUTE13,
369 ATTRIBUTE14 = X_ATTRIBUTE14,
370 ATTRIBUTE15 = X_ATTRIBUTE15,
371 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
372 --NON_ROUTINE_WORKORDER_ID = X_NON_ROUTINE_WORKORDER_ID,
373 --COLLECTION_ID = X_COLLECTION_ID,
374 --WORKORDER_MTL_TXN_ID = X_WORKORDER_MTL_TXN_ID,
375 REMOVAL_DATE = X_REMOVAL_DATE,
376 INSTALLATION_DATE = X_INSTALLATION_DATE,
377 ISSUE_MTL_TXN_ID = X_ISSUE_MTL_TXN_ID,
378 RETURN_MTL_TXN_ID = X_RETURN_MTL_TXN_ID,
379 PART_CHANGE_TYPE = X_PART_CHANGE_TYPE,
380 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
381 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
383 QUANTITY = X_QUANTITY
384 where PART_CHANGE_ID = X_PART_CHANGE_ID;
385
386 if (sql%notfound) then
390 end UPDATE_ROW;
387 raise no_data_found;
388 end if;
389
391
392 procedure DELETE_ROW (
393 X_PART_CHANGE_ID in NUMBER
394 ) is
395 begin
396
397 delete from AHL_PART_CHANGES
398 where PART_CHANGE_ID = X_PART_CHANGE_ID;
399
400 if (sql%notfound) then
401 raise no_data_found;
402 end if;
403 end DELETE_ROW;
404
405
406 procedure ADD_LANGUAGE
407 is
408 begin
409 null;
410 end;
411
412 end AHL_PART_CHANGES_PKG;
413
414
415
[Home] [Help]