[Home] [Help]
PACKAGE BODY: APPS.BOM_RES_INSTANCE_CHANGES_PKG
Source
1 PACKAGE BODY BOM_RES_INSTANCE_CHANGES_PKG as
2 /* $Header: bompricb.pls 115.5 2002/11/27 01:25:46 chrng ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Department_Id NUMBER,
6 X_Resource_Id NUMBER,
7 X_Shift_Num NUMBER,
8 X_Simulation_Set VARCHAR2,
9 X_From_Date DATE,
10 X_From_Time NUMBER DEFAULT NULL,
11 X_To_Date DATE DEFAULT NULL,
12 X_To_Time NUMBER DEFAULT NULL,
13 X_Instance_Id NUMBER,
14 X_Serial_Number VARCHAR2 DEFAULT NULL,
15 X_Action_Type NUMBER,
16 X_Last_Update_Date DATE,
17 X_Last_Updated_By NUMBER,
18 X_Creation_Date DATE,
19 X_Created_By NUMBER,
20 X_Last_Update_Login NUMBER DEFAULT NULL,
21 X_Attribute_Category VARCHAR2 DEFAULT NULL,
22 X_Attribute1 VARCHAR2 DEFAULT NULL,
23 X_Attribute2 VARCHAR2 DEFAULT NULL,
24 X_Attribute3 VARCHAR2 DEFAULT NULL,
25 X_Attribute4 VARCHAR2 DEFAULT NULL,
26 X_Attribute5 VARCHAR2 DEFAULT NULL,
27 X_Attribute6 VARCHAR2 DEFAULT NULL,
28 X_Attribute7 VARCHAR2 DEFAULT NULL,
29 X_Attribute8 VARCHAR2 DEFAULT NULL,
30 X_Attribute9 VARCHAR2 DEFAULT NULL,
31 X_Attribute10 VARCHAR2 DEFAULT NULL,
32 X_Attribute11 VARCHAR2 DEFAULT NULL,
33 X_Attribute12 VARCHAR2 DEFAULT NULL,
34 X_Attribute13 VARCHAR2 DEFAULT NULL,
35 X_Attribute14 VARCHAR2 DEFAULT NULL,
36 X_Attribute15 VARCHAR2 DEFAULT NULL,
37 X_Capacity_Change NUMBER DEFAULT NULL,
38 X_Reason_Code VARCHAR2 DEFAULT NULL,
39 -- chrng: added the Source fields
40 X_Maintenance_Organization_Id NUMBER DEFAULT NULL,
41 X_Wip_Entity_Id NUMBER DEFAULT NULL,
42 X_Operation_Seq_Num NUMBER DEFAULT NULL
43 ) IS
44
45 v_to_date DATE := to_date('31/12/1950', 'DD/MM/YYYY');
46
47 CURSOR C IS SELECT rowid FROM BOM_RES_INSTANCE_CHANGES
48 Where Department_Id = X_Department_Id
49 And Resource_Id = X_Resource_Id
50 And Shift_Num = X_Shift_Num
51 And Simulation_Set = X_Simulation_Set
52 And From_Date = X_From_Date
53 And nvl(to_date,v_to_date) = nvl(X_To_Date,v_to_date)
54 And nvl(from_time,'90000') = nvl(X_From_Time,'90000')
55 And nvl(to_time,'90000') = nvl(X_To_Time,'90000')
56 And instance_id = X_Instance_Id
57 And nvl(serial_number,'x') = nvl(X_Serial_Number,'x')
58 And action_type = X_Action_Type;
59
60 BEGIN
61 INSERT INTO BOM_RES_INSTANCE_CHANGES(
62 department_id,
63 resource_id,
64 shift_num,
65 simulation_set,
66 from_date,
67 from_time,
68 to_date,
69 to_time,
70 instance_id,
71 serial_number,
72 action_type,
73 last_update_date,
74 last_updated_by,
75 creation_date,
76 created_by,
77 last_update_login,
78 attribute_category,
79 attribute1,
80 attribute2,
81 attribute3,
82 attribute4,
83 attribute5,
84 attribute6,
85 attribute7,
86 attribute8,
87 attribute9,
88 attribute10,
89 attribute11,
90 attribute12,
91 attribute13,
92 attribute14,
93 attribute15,
94 capacity_change,
95 reason_code,
96 -- chrng: added the Source fields
97 maintenance_organization_id,
98 wip_entity_id,
99 operation_seq_num
100
101 ) VALUES (
102 X_Department_Id,
103 X_Resource_Id,
104 X_Shift_Num,
105 X_Simulation_Set,
106 X_From_Date,
107 X_From_Time,
108 X_To_Date,
109 X_To_Time,
110 X_Instance_Id,
111 X_Serial_Number,
112 X_Action_Type,
113 X_Last_Update_Date,
114 X_Last_Updated_By,
115 X_Creation_Date,
116 X_Created_By,
117 X_Last_Update_Login,
118 X_Attribute_Category,
119 X_Attribute1,
120 X_Attribute2,
121 X_Attribute3,
122 X_Attribute4,
123 X_Attribute5,
124 X_Attribute6,
125 X_Attribute7,
126 X_Attribute8,
127 X_Attribute9,
128 X_Attribute10,
129 X_Attribute11,
130 X_Attribute12,
131 X_Attribute13,
132 X_Attribute14,
133 X_Attribute15,
134 X_Capacity_Change,
135 X_Reason_Code,
136 -- chrng: added the Source fields
137 X_Maintenance_Organization_Id,
138 X_Wip_Entity_Id,
139 X_Operation_Seq_Num
140 );
141
142 OPEN C;
143 FETCH C INTO X_Rowid;
144 if (C%NOTFOUND) then
145 CLOSE C;
146 Raise NO_DATA_FOUND;
147 end if;
148 CLOSE C;
149
150 END Insert_Row;
151
152
153
154 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
155 X_Department_Id NUMBER,
156 X_Resource_Id NUMBER,
157 X_Shift_Num NUMBER,
158 X_Simulation_Set VARCHAR2,
159 X_From_Date DATE,
160 X_From_Time NUMBER DEFAULT NULL,
161 X_To_Date DATE DEFAULT NULL,
162 X_To_Time NUMBER DEFAULT NULL,
163 X_Instance_Id NUMBER,
164 X_Serial_Number VARCHAR2 DEFAULT NULL,
165 X_Action_Type NUMBER,
166 X_Attribute_Category VARCHAR2 DEFAULT NULL,
167 X_Attribute1 VARCHAR2 DEFAULT NULL,
168 X_Attribute2 VARCHAR2 DEFAULT NULL,
169 X_Attribute3 VARCHAR2 DEFAULT NULL,
170 X_Attribute4 VARCHAR2 DEFAULT NULL,
171 X_Attribute5 VARCHAR2 DEFAULT NULL,
172 X_Attribute6 VARCHAR2 DEFAULT NULL,
173 X_Attribute7 VARCHAR2 DEFAULT NULL,
174 X_Attribute8 VARCHAR2 DEFAULT NULL,
175 X_Attribute9 VARCHAR2 DEFAULT NULL,
176 X_Attribute10 VARCHAR2 DEFAULT NULL,
177 X_Attribute11 VARCHAR2 DEFAULT NULL,
178 X_Attribute12 VARCHAR2 DEFAULT NULL,
179 X_Attribute13 VARCHAR2 DEFAULT NULL,
180 X_Attribute14 VARCHAR2 DEFAULT NULL,
181 X_Attribute15 VARCHAR2 DEFAULT NULL,
182 X_Capacity_Change NUMBER DEFAULT NULL,
183 X_Reason_Code VARCHAR2 DEFAULT NULL,
184 -- chrng: added the Source fields
185 X_Maintenance_Organization_Id NUMBER DEFAULT NULL,
186 X_Wip_Entity_Id NUMBER DEFAULT NULL,
187 X_Operation_Seq_Num NUMBER DEFAULT NULL
188 ) IS
189 CURSOR C IS SELECT * FROM BOM_RES_INSTANCE_CHANGES
190 WHERE rowid = X_Rowid
191 FOR UPDATE of Department_Id NOWAIT;
192 Recinfo C%ROWTYPE;
193 BEGIN
194 OPEN C;
195 FETCH C INTO Recinfo;
196 if (C%NOTFOUND) then
197 CLOSE C;
198 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
199 APP_EXCEPTION.Raise_Exception;
200 end if;
201 CLOSE C;
202 if (
203 (Recinfo.department_id = X_Department_Id)
204 AND (Recinfo.resource_id = X_Resource_Id)
205 AND (Recinfo.shift_num = X_Shift_Num)
206 AND (Recinfo.simulation_set = X_Simulation_Set)
207 AND (Recinfo.from_date = X_From_Date)
208 AND ( (Recinfo.from_time = X_From_Time)
209 OR ( (Recinfo.from_time IS NULL)
210 AND (X_From_Time IS NULL)))
211 AND ( (Recinfo.to_date = X_To_Date)
212 OR ( (Recinfo.to_date IS NULL)
213 AND (X_To_Date IS NULL)))
214 AND ( (Recinfo.to_time = X_To_Time)
215 OR ( (Recinfo.to_time IS NULL)
216 AND (X_To_Time IS NULL)))
217 AND (Recinfo.instance_id = X_Instance_Id)
218 AND ( (Recinfo.serial_number = X_Serial_Number)
219 OR ( (Recinfo.serial_number IS NULL)
220 AND (X_Serial_Number IS NULL)))
221 AND (Recinfo.action_type = X_Action_Type)
222 AND ( (Recinfo.attribute_category = X_Attribute_Category)
223 OR ( (Recinfo.attribute_category IS NULL)
224 AND (X_Attribute_Category IS NULL)))
225 AND ( (Recinfo.attribute1 = X_Attribute1)
226 OR ( (Recinfo.attribute1 IS NULL)
227 AND (X_Attribute1 IS NULL)))
228 AND ( (Recinfo.attribute2 = X_Attribute2)
229 OR ( (Recinfo.attribute2 IS NULL)
230 AND (X_Attribute2 IS NULL)))
231 AND ( (Recinfo.attribute3 = X_Attribute3)
232 OR ( (Recinfo.attribute3 IS NULL)
233 AND (X_Attribute3 IS NULL)))
234 AND ( (Recinfo.attribute4 = X_Attribute4)
235 OR ( (Recinfo.attribute4 IS NULL)
236 AND (X_Attribute4 IS NULL)))
237 AND ( (Recinfo.attribute5 = X_Attribute5)
238 OR ( (Recinfo.attribute5 IS NULL)
239 AND (X_Attribute5 IS NULL)))
240 AND ( (Recinfo.attribute6 = X_Attribute6)
241 OR ( (Recinfo.attribute6 IS NULL)
242 AND (X_Attribute6 IS NULL)))
243 AND ( (Recinfo.attribute7 = X_Attribute7)
244 OR ( (Recinfo.attribute7 IS NULL)
245 AND (X_Attribute7 IS NULL)))
246 AND ( (Recinfo.attribute8 = X_Attribute8)
247 OR ( (Recinfo.attribute8 IS NULL)
248 AND (X_Attribute8 IS NULL)))
249 AND ( (Recinfo.attribute9 = X_Attribute9)
250 OR ( (Recinfo.attribute9 IS NULL)
251 AND (X_Attribute9 IS NULL)))
252 AND ( (Recinfo.attribute10 = X_Attribute10)
253 OR ( (Recinfo.attribute10 IS NULL)
254 AND (X_Attribute10 IS NULL)))
255 AND ( (Recinfo.attribute11 = X_Attribute11)
256 OR ( (Recinfo.attribute11 IS NULL)
257 AND (X_Attribute11 IS NULL)))
258 AND ( (Recinfo.attribute12 = X_Attribute12)
259 OR ( (Recinfo.attribute12 IS NULL)
260 AND (X_Attribute12 IS NULL)))
261 AND ( (Recinfo.attribute13 = X_Attribute13)
262 OR ( (Recinfo.attribute13 IS NULL)
263 AND (X_Attribute13 IS NULL)))
264 AND ( (Recinfo.attribute14 = X_Attribute14)
265 OR ( (Recinfo.attribute14 IS NULL)
266 AND (X_Attribute14 IS NULL)))
267 AND ( (Recinfo.attribute15 = X_Attribute15)
268 OR ( (Recinfo.attribute15 IS NULL)
269 AND (X_Attribute15 IS NULL)))
270 AND ( (Recinfo.capacity_change = X_Capacity_Change)
271 OR ( (Recinfo.capacity_change IS NULL)
272 AND (X_Capacity_Change IS NULL)))
273 AND ( (Recinfo.reason_code = X_Reason_Code)
274 OR ( (Recinfo.reason_code IS NULL)
275 AND (X_Reason_Code IS NULL)))
276 AND ( (Recinfo.maintenance_organization_id = X_Maintenance_Organization_Id)
277 OR ( (Recinfo.maintenance_organization_id IS NULL)
278 AND (X_Maintenance_Organization_Id IS NULL)))
279 AND ( (Recinfo.wip_entity_id = X_Wip_Entity_Id)
280 OR ( (Recinfo.wip_entity_id IS NULL)
281 AND (X_Wip_Entity_Id IS NULL)))
282 AND ( (Recinfo.operation_seq_num = X_Operation_Seq_Num)
283 OR ( (Recinfo.operation_seq_num IS NULL)
284 AND (X_Operation_Seq_Num IS NULL)))
285 ) then
286 return;
287 else
288 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
289 APP_EXCEPTION.RAISE_EXCEPTION;
290 end if;
291 END Lock_Row;
292
293
294 PROCEDURE Update_Row(X_Rowid VARCHAR2,
295 X_Department_Id NUMBER,
296 X_Resource_Id NUMBER,
297 X_Shift_Num NUMBER,
298 X_Simulation_Set VARCHAR2,
299 X_From_Date DATE,
300 X_From_Time NUMBER DEFAULT NULL,
301 X_To_Date DATE DEFAULT NULL,
302 X_To_Time NUMBER DEFAULT NULL,
303 X_Instance_Id NUMBER,
304 X_Serial_Number VARCHAR2 DEFAULT NULL,
305 X_Action_Type NUMBER,
306 X_Last_Update_Date DATE,
307 X_Last_Updated_By NUMBER,
308 X_Last_Update_Login NUMBER DEFAULT NULL,
309 X_Attribute_Category VARCHAR2 DEFAULT NULL,
310 X_Attribute1 VARCHAR2 DEFAULT NULL,
311 X_Attribute2 VARCHAR2 DEFAULT NULL,
312 X_Attribute3 VARCHAR2 DEFAULT NULL,
313 X_Attribute4 VARCHAR2 DEFAULT NULL,
314 X_Attribute5 VARCHAR2 DEFAULT NULL,
315 X_Attribute6 VARCHAR2 DEFAULT NULL,
316 X_Attribute7 VARCHAR2 DEFAULT NULL,
317 X_Attribute8 VARCHAR2 DEFAULT NULL,
318 X_Attribute9 VARCHAR2 DEFAULT NULL,
319 X_Attribute10 VARCHAR2 DEFAULT NULL,
320 X_Attribute11 VARCHAR2 DEFAULT NULL,
321 X_Attribute12 VARCHAR2 DEFAULT NULL,
322 X_Attribute13 VARCHAR2 DEFAULT NULL,
323 X_Attribute14 VARCHAR2 DEFAULT NULL,
324 X_Attribute15 VARCHAR2 DEFAULT NULL,
325 X_Capacity_Change NUMBER DEFAULT NULL,
329 X_Wip_Entity_Id NUMBER DEFAULT NULL,
326 X_Reason_Code VARCHAR2 DEFAULT NULL,
327 -- chrng: added the Source fields
328 X_Maintenance_Organization_Id NUMBER DEFAULT NULL,
330 X_Operation_Seq_Num NUMBER DEFAULT NULL
331 ) IS
332 BEGIN
333 UPDATE BOM_RES_INSTANCE_CHANGES
334 SET
335 department_id = X_Department_Id,
336 resource_id = X_Resource_Id,
337 shift_num = X_Shift_Num,
338 simulation_set = X_Simulation_Set,
339 from_date = X_From_Date,
340 from_time = X_From_Time,
341 to_date = X_To_Date,
342 to_time = X_To_Time,
343 instance_id = X_Instance_Id,
344 serial_number = X_Serial_Number,
345 action_type = X_Action_Type,
346 last_update_date = X_Last_Update_Date,
347 last_updated_by = X_Last_Updated_By,
348 last_update_login = X_Last_Update_Login,
349 attribute_category = X_Attribute_Category,
350 attribute1 = X_Attribute1,
351 attribute2 = X_Attribute2,
352 attribute3 = X_Attribute3,
353 attribute4 = X_Attribute4,
354 attribute5 = X_Attribute5,
355 attribute6 = X_Attribute6,
356 attribute7 = X_Attribute7,
357 attribute8 = X_Attribute8,
358 attribute9 = X_Attribute9,
359 attribute10 = X_Attribute10,
360 attribute11 = X_Attribute11,
361 attribute12 = X_Attribute12,
362 attribute13 = X_Attribute13,
363 attribute14 = X_Attribute14,
364 attribute15 = X_Attribute15,
365 capacity_change = X_Capacity_Change,
366 reason_code = X_Reason_Code,
367 -- chrng: added the Source fields
368 maintenance_organization_id = X_Maintenance_Organization_Id,
369 wip_entity_id = X_Wip_Entity_Id,
370 operation_seq_num = X_Operation_Seq_Num
371 WHERE rowid = X_rowid;
372 if (SQL%NOTFOUND) then
373 Raise NO_DATA_FOUND;
374 end if;
375 END Update_Row;
376
377
378
379 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
380 BEGIN
381 DELETE FROM BOM_RES_INSTANCE_CHANGES
382 WHERE rowid = X_Rowid;
383 if (SQL%NOTFOUND) then
384 Raise NO_DATA_FOUND;
385 end if;
386 END Delete_Row;
387
388 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
389 X_Department_Id NUMBER,
393 X_From_Date DATE,
390 X_Resource_Id NUMBER,
391 X_Shift_Num NUMBER,
392 X_Simulation_Set VARCHAR2,
394 X_From_Time NUMBER,
395 X_To_Date DATE,
396 X_To_Time NUMBER,
397 X_Instance_Id NUMBER,
398 X_Serial_Number VARCHAR2,
399 X_Action_Type NUMBER
400 ) IS
401
402 v_to_date DATE := to_date('31/12/1950', 'DD/MM/YYYY');
403 DUMMY NUMBER;
404 BEGIN
405 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
406 (SELECT 1 FROM BOM_RES_INSTANCE_CHANGES ric
407 Where Department_Id = X_Department_Id
408 And Resource_Id = X_Resource_Id
409 And Shift_Num = X_Shift_Num
410 And Simulation_Set = X_Simulation_Set
411 And From_Date = X_From_Date
412 And nvl(ric.to_date,v_to_date)=nvl(X_To_Date,v_to_date)
413 And nvl(from_time,'90000')= nvl(X_From_Time,'90000')
414 And nvl(to_time,'90000') = nvl(X_To_Time,'90000')
415 And instance_id = X_Instance_Id
416 And nvl(serial_number,'x')= nvl(X_Serial_Number,'x')
417 And action_type = X_Action_Type
418 And (( X_Rowid IS NULL) OR (rowid <> X_Rowid))
419 );
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 FND_MESSAGE.SET_NAME('BOM','BOM_CANNOT_ENTER_INS_CHANGES');
423 APP_EXCEPTION.RAISE_EXCEPTION;
424 END Check_Unique;
425
426
427 /* GRANT EXECUTE ON BOM_RES_INSTANCE_CHANGES_PKG TO MFG; */
428
429
430 END BOM_RES_INSTANCE_CHANGES_PKG;