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