[Home] [Help]
PACKAGE BODY: APPS.BOM_RESOURCE_SHIFTS_PKG
Source
1 PACKAGE BODY BOM_RESOURCE_SHIFTS_PKG as
2 /* $Header: bompbrsb.pls 115.6 2002/11/19 03:12:09 lnarveka ship $ */
3
4 PROCEDURE Check_References (X_department_Id NUMBER,
5 X_Resource_Id NUMBER,
6 X_Shift_Num NUMBER) IS
7 Message_Name VARCHAR2(80);
8 Dummy NUMBER;
9 BEGIN
10 message_name := 'BOM_CANNOT_DELETE_SHIFT';
11 select 1 into dummy from dual where not exists
12 (select 1 from bom_resource_changes
13 where department_id = X_Department_Id
14 and resource_id = X_Resource_Id
15 and shift_num = X_Shift_Num);
16
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 fnd_message.set_name('BOM',message_name);
20 app_exception.raise_exception;
21 END Check_References;
22
23 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
24 X_Department_Id NUMBER,
25 X_Resource_Id NUMBER,
26 X_Shift_Num NUMBER,
27 X_Capacity_Units NUMBER,
28 X_Last_Update_Date DATE,
29 X_Last_Updated_By NUMBER,
30 X_Creation_Date DATE,
31 X_Created_By NUMBER,
32 X_Last_Update_Login NUMBER DEFAULT NULL,
33 X_Attribute_Category VARCHAR2 DEFAULT NULL,
34 X_Attribute1 VARCHAR2 DEFAULT NULL,
35 X_Attribute2 VARCHAR2 DEFAULT NULL,
36 X_Attribute3 VARCHAR2 DEFAULT NULL,
37 X_Attribute4 VARCHAR2 DEFAULT NULL,
38 X_Attribute5 VARCHAR2 DEFAULT NULL,
39 X_Attribute6 VARCHAR2 DEFAULT NULL,
40 X_Attribute7 VARCHAR2 DEFAULT NULL,
41 X_Attribute8 VARCHAR2 DEFAULT NULL,
42 X_Attribute9 VARCHAR2 DEFAULT NULL,
43 X_Attribute10 VARCHAR2 DEFAULT NULL,
44 X_Attribute11 VARCHAR2 DEFAULT NULL,
45 X_Attribute12 VARCHAR2 DEFAULT NULL,
46 X_Attribute13 VARCHAR2 DEFAULT NULL,
47 X_Attribute14 VARCHAR2 DEFAULT NULL,
48 X_Attribute15 VARCHAR2 DEFAULT NULL
49 ) IS
50 CURSOR C IS SELECT rowid FROM BOM_RESOURCE_SHIFTS
51 WHERE department_id = X_Department_Id
52 AND resource_id = X_Resource_Id
53 AND shift_num = X_Shift_Num;
54 BEGIN
55 INSERT INTO BOM_RESOURCE_SHIFTS(
56 department_id,
57 resource_id,
58 shift_num,
59 Capacity_Units,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login,
65 attribute_category,
66 attribute1,
67 attribute2,
68 attribute3,
69 attribute4,
70 attribute5,
71 attribute6,
72 attribute7,
73 attribute8,
74 attribute9,
75 attribute10,
76 attribute11,
77 attribute12,
78 attribute13,
79 attribute14,
80 attribute15
81 ) VALUES (
82 X_Department_Id,
83 X_Resource_Id,
84 X_Shift_Num,
85 X_Capacity_Units,
86 X_Last_Update_Date,
87 X_Last_Updated_By,
88 X_Creation_Date,
89 X_Created_By,
90 X_Last_Update_Login,
91 X_Attribute_Category,
92 X_Attribute1,
93 X_Attribute2,
94 X_Attribute3,
95 X_Attribute4,
96 X_Attribute5,
97 X_Attribute6,
98 X_Attribute7,
99 X_Attribute8,
100 X_Attribute9,
101 X_Attribute10,
102 X_Attribute11,
103 X_Attribute12,
104 X_Attribute13,
105 X_Attribute14,
106 X_Attribute15
107 );
108 OPEN C;
109 FETCH C INTO X_Rowid;
110 if (C%NOTFOUND) then
111 CLOSE C;
112 Raise NO_DATA_FOUND;
113 end if;
114 CLOSE C;
115 END Insert_Row;
116
117
118
119 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
120 X_Department_Id NUMBER,
121 X_Resource_Id NUMBER,
122 X_Shift_Num NUMBER,
123 X_Capacity_Units NUMBER,
124 X_Attribute_Category VARCHAR2 DEFAULT NULL,
125 X_Attribute1 VARCHAR2 DEFAULT NULL,
126 X_Attribute2 VARCHAR2 DEFAULT NULL,
127 X_Attribute3 VARCHAR2 DEFAULT NULL,
128 X_Attribute4 VARCHAR2 DEFAULT NULL,
129 X_Attribute5 VARCHAR2 DEFAULT NULL,
130 X_Attribute6 VARCHAR2 DEFAULT NULL,
131 X_Attribute7 VARCHAR2 DEFAULT NULL,
132 X_Attribute8 VARCHAR2 DEFAULT NULL,
133 X_Attribute9 VARCHAR2 DEFAULT NULL,
134 X_Attribute10 VARCHAR2 DEFAULT NULL,
135 X_Attribute11 VARCHAR2 DEFAULT NULL,
136 X_Attribute12 VARCHAR2 DEFAULT NULL,
137 X_Attribute13 VARCHAR2 DEFAULT NULL,
138 X_Attribute14 VARCHAR2 DEFAULT NULL,
139 X_Attribute15 VARCHAR2 DEFAULT NULL
140 ) IS
141 CURSOR C IS SELECT * FROM BOM_RESOURCE_SHIFTS
142 WHERE rowid = X_Rowid
143 FOR UPDATE of Department_Id NOWAIT;
144 Recinfo C%ROWTYPE;
145 BEGIN
146 OPEN C;
147 FETCH C INTO Recinfo;
148 if (C%NOTFOUND) then
149 CLOSE C;
150 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
151 APP_EXCEPTION.Raise_Exception;
152 end if;
153 CLOSE C;
154 if (
155 (Recinfo.department_id = X_Department_Id)
156 AND (Recinfo.resource_id = X_Resource_Id)
157 AND (Recinfo.shift_num = X_Shift_Num)
158 AND ( (Recinfo.Capacity_Units = X_Capacity_Units)
159 OR (Recinfo.Capacity_Units IS NULL))
160 AND ( (Recinfo.attribute_category = X_Attribute_Category)
161 OR ( (Recinfo.attribute_category IS NULL)
162 AND (X_Attribute_Category IS NULL)))
163 AND ( (Recinfo.attribute1 = X_Attribute1)
164 OR ( (Recinfo.attribute1 IS NULL)
165 AND (X_Attribute1 IS NULL)))
166 AND ( (Recinfo.attribute2 = X_Attribute2)
167 OR ( (Recinfo.attribute2 IS NULL)
168 AND (X_Attribute2 IS NULL)))
169 AND ( (Recinfo.attribute3 = X_Attribute3)
170 OR ( (Recinfo.attribute3 IS NULL)
171 AND (X_Attribute3 IS NULL)))
172 AND ( (Recinfo.attribute4 = X_Attribute4)
173 OR ( (Recinfo.attribute4 IS NULL)
174 AND (X_Attribute4 IS NULL)))
175 AND ( (Recinfo.attribute5 = X_Attribute5)
176 OR ( (Recinfo.attribute5 IS NULL)
177 AND (X_Attribute5 IS NULL)))
178 AND ( (Recinfo.attribute6 = X_Attribute6)
179 OR ( (Recinfo.attribute6 IS NULL)
180 AND (X_Attribute6 IS NULL)))
181 AND ( (Recinfo.attribute7 = X_Attribute7)
182 OR ( (Recinfo.attribute7 IS NULL)
183 AND (X_Attribute7 IS NULL)))
184 AND ( (Recinfo.attribute8 = X_Attribute8)
185 OR ( (Recinfo.attribute8 IS NULL)
186 AND (X_Attribute8 IS NULL)))
187 AND ( (Recinfo.attribute9 = X_Attribute9)
188 OR ( (Recinfo.attribute9 IS NULL)
189 AND (X_Attribute9 IS NULL)))
190 AND ( (Recinfo.attribute10 = X_Attribute10)
191 OR ( (Recinfo.attribute10 IS NULL)
192 AND (X_Attribute10 IS NULL)))
193 AND ( (Recinfo.attribute11 = X_Attribute11)
194 OR ( (Recinfo.attribute11 IS NULL)
195 AND (X_Attribute11 IS NULL)))
196 AND ( (Recinfo.attribute12 = X_Attribute12)
197 OR ( (Recinfo.attribute12 IS NULL)
198 AND (X_Attribute12 IS NULL)))
199 AND ( (Recinfo.attribute13 = X_Attribute13)
200 OR ( (Recinfo.attribute13 IS NULL)
201 AND (X_Attribute13 IS NULL)))
202 AND ( (Recinfo.attribute14 = X_Attribute14)
203 OR ( (Recinfo.attribute14 IS NULL)
204 AND (X_Attribute14 IS NULL)))
205 AND ( (Recinfo.attribute15 = X_Attribute15)
206 OR ( (Recinfo.attribute15 IS NULL)
207 AND (X_Attribute15 IS NULL)))
208 ) then
209 return;
210 else
211 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
212 APP_EXCEPTION.RAISE_EXCEPTION;
213 end if;
214 END Lock_Row;
215
216
217
218 PROCEDURE Update_Row(X_Rowid VARCHAR2,
219 X_Department_Id NUMBER,
220 X_Resource_Id NUMBER,
221 X_Shift_Num NUMBER,
222 X_Capacity_Units NUMBER,
223 X_Last_Update_Date DATE,
224 X_Last_Updated_By NUMBER,
225 X_Last_Update_Login NUMBER DEFAULT NULL,
226 X_Attribute_Category VARCHAR2 DEFAULT NULL,
227 X_Attribute1 VARCHAR2 DEFAULT NULL,
228 X_Attribute2 VARCHAR2 DEFAULT NULL,
229 X_Attribute3 VARCHAR2 DEFAULT NULL,
230 X_Attribute4 VARCHAR2 DEFAULT NULL,
231 X_Attribute5 VARCHAR2 DEFAULT NULL,
232 X_Attribute6 VARCHAR2 DEFAULT NULL,
233 X_Attribute7 VARCHAR2 DEFAULT NULL,
234 X_Attribute8 VARCHAR2 DEFAULT NULL,
235 X_Attribute9 VARCHAR2 DEFAULT NULL,
236 X_Attribute10 VARCHAR2 DEFAULT NULL,
237 X_Attribute11 VARCHAR2 DEFAULT NULL,
238 X_Attribute12 VARCHAR2 DEFAULT NULL,
239 X_Attribute13 VARCHAR2 DEFAULT NULL,
240 X_Attribute14 VARCHAR2 DEFAULT NULL,
241 X_Attribute15 VARCHAR2 DEFAULT NULL
242 ) IS
243 BEGIN
244 UPDATE BOM_RESOURCE_SHIFTS
245 SET
246 department_id = X_Department_Id,
247 resource_id = X_Resource_Id,
248 shift_num = X_Shift_Num,
249 Capacity_Units = X_Capacity_Units,
250 last_update_date = X_Last_Update_Date,
251 last_updated_by = X_Last_Updated_By,
252 last_update_login = X_Last_Update_Login,
253 attribute_category = X_Attribute_Category,
254 attribute1 = X_Attribute1,
255 attribute2 = X_Attribute2,
256 attribute3 = X_Attribute3,
257 attribute4 = X_Attribute4,
258 attribute5 = X_Attribute5,
259 attribute6 = X_Attribute6,
260 attribute7 = X_Attribute7,
261 attribute8 = X_Attribute8,
262 attribute9 = X_Attribute9,
263 attribute10 = X_Attribute10,
264 attribute11 = X_Attribute11,
265 attribute12 = X_Attribute12,
266 attribute13 = X_Attribute13,
267 attribute14 = X_Attribute14,
268 attribute15 = X_Attribute15
269 WHERE rowid = X_rowid;
270 if (SQL%NOTFOUND) then
271 Raise NO_DATA_FOUND;
272 end if;
273 END Update_Row;
274
275
276
277 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
278 BEGIN
279 DELETE FROM BOM_RESOURCE_SHIFTS
280 WHERE rowid = X_Rowid;
281 if (SQL%NOTFOUND) then
282 Raise NO_DATA_FOUND;
283 end if;
284 END Delete_Row;
285
286
287
288 END BOM_RESOURCE_SHIFTS_PKG;