[Home] [Help]
PACKAGE BODY: APPS.B_STD_SUB_OP_RES_PKG
Source
1 PACKAGE BODY B_STD_SUB_OP_RES_PKG as
2 /* $Header: BOMPSSRB.pls 120.0.12010000.2 2008/10/15 11:06:18 tbhande ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Standard_Operation_Id NUMBER,
6 X_Resource_Id NUMBER,
7 X_SUBSTITUTE_GROUP_NUM NUMBER,
8 X_REPLACEMENT_GROUP_NUM NUMBER,
9 X_Activity_Id NUMBER,
10 X_Standard_Rate_Flag NUMBER,
11 X_Assigned_Units NUMBER,
12 X_Usage_Rate_Or_Amount NUMBER,
13 X_Usage_Rate_Or_Amount_Inverse NUMBER,
14 X_Basis_Type NUMBER,
15 X_Schedule_Flag 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,
21 X_Autocharge_Type NUMBER,
22 X_Attribute_Category VARCHAR2,
23 X_Attribute1 VARCHAR2,
24 X_Attribute2 VARCHAR2,
25 X_Attribute3 VARCHAR2,
26 X_Attribute4 VARCHAR2,
27 X_Attribute5 VARCHAR2,
28 X_Attribute6 VARCHAR2,
29 X_Attribute7 VARCHAR2,
30 X_Attribute8 VARCHAR2,
31 X_Attribute9 VARCHAR2,
32 X_Attribute10 VARCHAR2,
33 X_Attribute11 VARCHAR2,
34 X_Attribute12 VARCHAR2,
35 X_Attribute13 VARCHAR2,
36 X_Attribute14 VARCHAR2,
37 X_Attribute15 VARCHAR2,
38 X_Schedule_Seq_Num NUMBER -- Bug 7370692
39 ) IS
40 CURSOR C IS SELECT rowid FROM BOM_STD_SUB_OP_RESOURCES
41 WHERE standard_operation_id = X_Standard_Operation_Id
42 AND replacement_group_num = X_Replacement_Group_Num;
43
44 BEGIN
45 INSERT INTO BOM_STD_SUB_OP_RESOURCES(
46 standard_operation_id,
47 resource_id,
48 SUBSTITUTE_GROUP_NUM,
49 REPLACEMENT_GROUP_NUM,
50 activity_id,
51 standard_rate_flag,
52 assigned_units,
53 usage_rate_or_amount,
54 usage_rate_or_amount_inverse,
55 basis_type,
56 schedule_flag,
57 last_update_date,
58 last_updated_by,
59 creation_date,
60 created_by,
61 last_update_login,
62 autocharge_type,
63 attribute_category,
64 attribute1,
65 attribute2,
66 attribute3,
67 attribute4,
68 attribute5,
69 attribute6,
70 attribute7,
71 attribute8,
72 attribute9,
73 attribute10,
74 attribute11,
75 attribute12,
76 attribute13,
77 attribute14,
78 attribute15,
79 schedule_seq_num -- Bug 7370692
80 ) VALUES (
81 X_Standard_Operation_Id,
82 X_Resource_Id,
83 X_SUBSTITUTE_GROUP_NUM,
84 X_REPLACEMENT_GROUP_NUM,
85 X_Activity_Id,
86 X_Standard_Rate_Flag,
87 X_Assigned_Units,
88 X_Usage_Rate_Or_Amount,
89 X_Usage_Rate_Or_Amount_Inverse,
90 X_Basis_Type,
91 X_Schedule_Flag,
92 X_Last_Update_Date,
93 X_Last_Updated_By,
94 X_Creation_Date,
95 X_Created_By,
96 X_Last_Update_Login,
97 X_Autocharge_Type,
98 X_Attribute_Category,
99 X_Attribute1,
100 X_Attribute2,
101 X_Attribute3,
102 X_Attribute4,
103 X_Attribute5,
104 X_Attribute6,
105 X_Attribute7,
106 X_Attribute8,
107 X_Attribute9,
108 X_Attribute10,
109 X_Attribute11,
110 X_Attribute12,
111 X_Attribute13,
112 X_Attribute14,
113 X_Attribute15,
114 X_Schedule_Seq_Num -- Bug 7370692
115
116 );
117
118 OPEN C;
119 FETCH C INTO X_Rowid;
120 if (C%NOTFOUND) then
121 CLOSE C;
122 RAISE NO_DATA_FOUND;
123 end if;
124 CLOSE C;
125 END Insert_Row;
126
127
128 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
129 X_Standard_Operation_Id NUMBER,
130 X_Resource_Id NUMBER,
131 X_SUBSTITUTE_GROUP_NUM NUMBER,
132 X_REPLACEMENT_GROUP_NUM NUMBER,
133 X_Activity_Id NUMBER DEFAULT NULL,
134 X_Usage_Rate_Or_Amount NUMBER,
135 X_Usage_Rate_Or_Amount_Inverse NUMBER,
136 X_Basis_Type NUMBER,
137 X_Autocharge_Type NUMBER,
138 X_Standard_Rate_Flag NUMBER,
139 X_Assigned_Units NUMBER DEFAULT NULL,
140 X_Schedule_Flag NUMBER,
141 X_Attribute_Category VARCHAR2 DEFAULT NULL,
142 X_Attribute1 VARCHAR2 DEFAULT NULL,
143 X_Attribute2 VARCHAR2 DEFAULT NULL,
144 X_Attribute3 VARCHAR2 DEFAULT NULL,
145 X_Attribute4 VARCHAR2 DEFAULT NULL,
146 X_Attribute5 VARCHAR2 DEFAULT NULL,
147 X_Attribute6 VARCHAR2 DEFAULT NULL,
148 X_Attribute7 VARCHAR2 DEFAULT NULL,
149 X_Attribute8 VARCHAR2 DEFAULT NULL,
150 X_Attribute9 VARCHAR2 DEFAULT NULL,
151 X_Attribute10 VARCHAR2 DEFAULT NULL,
152 X_Attribute11 VARCHAR2 DEFAULT NULL,
153 X_Attribute12 VARCHAR2 DEFAULT NULL,
154 X_Attribute13 VARCHAR2 DEFAULT NULL,
155 X_Attribute14 VARCHAR2 DEFAULT NULL,
156 X_Attribute15 VARCHAR2 DEFAULT NULL,
157 X_Schedule_Seq_Num NUMBER -- Bug 7370692
158 ) IS
159 CURSOR C IS
160 SELECT *
161 FROM BOM_STD_SUB_OP_RESOURCES
162 WHERE rowid = X_Rowid
163 FOR UPDATE of Standard_Operation_Id, Replacement_Group_Num 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
175 if (
176 ( (Recinfo.standard_operation_id = X_Standard_Operation_Id)
177 OR ( (Recinfo.standard_operation_id IS NULL)
178 AND (X_Standard_Operation_Id IS NULL)))
179 AND ( (Recinfo.resource_id = X_Resource_Id)
180 OR ( (Recinfo.resource_id IS NULL)
181 AND (X_Resource_Id IS NULL)))
182 AND ( (Recinfo.activity_id = X_Activity_Id)
183 OR ( (Recinfo.activity_id IS NULL)
184 AND (X_Activity_Id IS NULL)))
185 AND Recinfo.substitute_group_num = X_substitute_group_num
186 AND ( (Recinfo.replacement_group_num = X_replacement_group_num)
187 OR ( (Recinfo.replacement_group_num IS NULL)
188 AND (X_replacement_group_num IS NULL)))
189 AND ( (Recinfo.usage_rate_or_amount = X_Usage_Rate_Or_Amount)
190 OR ( (Recinfo.usage_rate_or_amount IS NULL)
191 AND (X_Usage_Rate_Or_Amount IS NULL)))
192 AND ( (Recinfo.usage_rate_or_amount_inverse = X_Usage_Rate_Or_Amount_Inverse)
193 OR ( (Recinfo.usage_rate_or_amount_inverse IS NULL)
194 AND (X_Usage_Rate_Or_Amount_Inverse IS NULL)))
195 AND ( (Recinfo.basis_type = X_Basis_Type)
196 OR ( (Recinfo.basis_type IS NULL)
197 AND (X_Basis_Type IS NULL)))
198 AND ( (Recinfo.autocharge_type = X_Autocharge_Type)
199 OR ( (Recinfo.autocharge_type IS NULL)
200 AND (X_Autocharge_Type IS NULL)))
201 AND ( (Recinfo.standard_rate_flag = X_Standard_Rate_Flag)
202 OR ( (Recinfo.standard_rate_flag IS NULL)
203 AND (X_Standard_Rate_Flag IS NULL)))
204 AND ( (Recinfo.assigned_units = X_Assigned_Units)
205 OR ( (Recinfo.assigned_units IS NULL)
206 AND (X_Assigned_Units IS NULL)))
207 AND ( (Recinfo.schedule_flag = X_Schedule_Flag)
208 OR ( (Recinfo.schedule_flag IS NULL)
209 AND (X_Schedule_Flag IS NULL)))
210 AND ( (Recinfo.attribute_category = X_Attribute_Category)
211 OR ( (Recinfo.attribute_category IS NULL)
212 AND (X_Attribute_Category IS NULL)))
213 AND ( (Recinfo.attribute1 = X_Attribute1)
214 OR ( (Recinfo.attribute1 IS NULL)
215 AND (X_Attribute1 IS NULL)))
216 AND ( (Recinfo.attribute2 = X_Attribute2)
217 OR ( (Recinfo.attribute2 IS NULL)
218 AND (X_Attribute2 IS NULL)))
219 AND ( (Recinfo.attribute3 = X_Attribute3)
220 OR ( (Recinfo.attribute3 IS NULL)
221 AND (X_Attribute3 IS NULL)))
222 AND ( (Recinfo.attribute4 = X_Attribute4)
223 OR ( (Recinfo.attribute4 IS NULL)
224 AND (X_Attribute4 IS NULL)))
225 AND ( (Recinfo.attribute5 = X_Attribute5)
226 OR ( (Recinfo.attribute5 IS NULL)
227 AND (X_Attribute5 IS NULL)))
228 AND ( (Recinfo.attribute6 = X_Attribute6)
229 OR ( (Recinfo.attribute6 IS NULL)
230 AND (X_Attribute6 IS NULL)))
231 AND ( (Recinfo.attribute7 = X_Attribute7)
232 OR ( (Recinfo.attribute7 IS NULL)
233 AND (X_Attribute7 IS NULL)))
234 AND ( (Recinfo.attribute8 = X_Attribute8)
235 OR ( (Recinfo.attribute8 IS NULL)
236 AND (X_Attribute8 IS NULL)))
237 AND ( (Recinfo.attribute9 = X_Attribute9)
238 OR ( (Recinfo.attribute9 IS NULL)
239 AND (X_Attribute9 IS NULL)))
240 AND ( (Recinfo.attribute10 = X_Attribute10)
241 OR ( (Recinfo.attribute10 IS NULL)
242 AND (X_Attribute10 IS NULL)))
243 AND ( (Recinfo.attribute11 = X_Attribute11)
244 OR ( (Recinfo.attribute11 IS NULL)
245 AND (X_Attribute11 IS NULL)))
246 AND ( (Recinfo.attribute12 = X_Attribute12)
247 OR ( (Recinfo.attribute12 IS NULL)
248 AND (X_Attribute12 IS NULL)))
249 AND ( (Recinfo.attribute13 = X_Attribute13)
250 OR ( (Recinfo.attribute13 IS NULL)
251 AND (X_Attribute13 IS NULL)))
252 AND ( (Recinfo.attribute14 = X_Attribute14)
253 OR ( (Recinfo.attribute14 IS NULL)
254 AND (X_Attribute14 IS NULL)))
255 AND ( (Recinfo.attribute15 = X_Attribute15)
256 OR ( (Recinfo.attribute15 IS NULL)
257 AND (X_Attribute15 IS NULL)))
258 /*Start: -- Bug 7370692 */
259 AND ( (Recinfo.schedule_seq_num = X_Schedule_Seq_Num)
260 OR ( (Recinfo.schedule_seq_num IS NULL)
261 AND (X_Schedule_Seq_Num IS NULL)))
262 /* End :-- Bug 7370692 */
263 ) then
264 return;
265 else
266 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 end if;
269 END Lock_Row;
270
271
272 PROCEDURE Update_Row(X_Rowid VARCHAR2,
273 X_Standard_Operation_Id NUMBER,
274 X_Resource_Id NUMBER,
275 X_SUBSTITUTE_GROUP_NUM NUMBER,
276 X_REPLACEMENT_GROUP_NUM NUMBER,
277 X_Activity_Id NUMBER DEFAULT NULL,
278 X_Last_Update_Date DATE,
279 X_Last_Updated_By NUMBER,
280 X_Last_Update_Login NUMBER DEFAULT NULL,
281 X_Usage_Rate_Or_Amount NUMBER,
282 X_Usage_Rate_Or_Amount_Inverse NUMBER,
283 X_Basis_Type NUMBER,
284 X_Autocharge_Type NUMBER,
285 X_Standard_Rate_Flag NUMBER,
286 X_Assigned_Units NUMBER DEFAULT NULL,
287 X_Schedule_Flag NUMBER,
288 X_Attribute_Category VARCHAR2 DEFAULT NULL,
289 X_Attribute1 VARCHAR2 DEFAULT NULL,
290 X_Attribute2 VARCHAR2 DEFAULT NULL,
291 X_Attribute3 VARCHAR2 DEFAULT NULL,
292 X_Attribute4 VARCHAR2 DEFAULT NULL,
293 X_Attribute5 VARCHAR2 DEFAULT NULL,
294 X_Attribute6 VARCHAR2 DEFAULT NULL,
295 X_Attribute7 VARCHAR2 DEFAULT NULL,
296 X_Attribute8 VARCHAR2 DEFAULT NULL,
297 X_Attribute9 VARCHAR2 DEFAULT NULL,
298 X_Attribute10 VARCHAR2 DEFAULT NULL,
299 X_Attribute11 VARCHAR2 DEFAULT NULL,
300 X_Attribute12 VARCHAR2 DEFAULT NULL,
301 X_Attribute13 VARCHAR2 DEFAULT NULL,
302 X_Attribute14 VARCHAR2 DEFAULT NULL,
303 X_Attribute15 VARCHAR2 DEFAULT NULL,
304 X_Schedule_Seq_Num NUMBER -- Bug 7370692
305 ) IS
306 BEGIN
307 UPDATE BOM_STD_SUB_OP_RESOURCES
308 SET
309 standard_operation_id = X_Standard_Operation_Id,
310 resource_id = X_Resource_Id,
311 activity_id = X_Activity_Id,
312 SUBSTITUTE_GROUP_NUM = X_SUBSTITUTE_GROUP_NUM,
313 REPLACEMENT_GROUP_NUM = X_REPLACEMENT_GROUP_NUM,
314 last_update_date = X_Last_Update_Date,
315 last_updated_by = X_Last_Updated_By,
316 last_update_login = X_Last_Update_Login,
317 usage_rate_or_amount = X_Usage_Rate_Or_Amount,
318 usage_rate_or_amount_inverse = X_Usage_Rate_Or_Amount_Inverse,
319 basis_type = X_Basis_Type,
320 autocharge_type = X_Autocharge_Type,
321 standard_rate_flag = X_Standard_Rate_Flag,
322 assigned_units = X_Assigned_Units,
323 schedule_flag = X_Schedule_Flag,
324 attribute_category = X_Attribute_Category,
325 attribute1 = X_Attribute1,
326 attribute2 = X_Attribute2,
327 attribute3 = X_Attribute3,
328 attribute4 = X_Attribute4,
329 attribute5 = X_Attribute5,
330 attribute6 = X_Attribute6,
331 attribute7 = X_Attribute7,
332 attribute8 = X_Attribute8,
333 attribute9 = X_Attribute9,
334 attribute10 = X_Attribute10,
335 attribute11 = X_Attribute11,
336 attribute12 = X_Attribute12,
337 attribute13 = X_Attribute13,
338 attribute14 = X_Attribute14,
339 attribute15 = X_Attribute15,
340 Schedule_Seq_Num = X_Schedule_Seq_Num -- Bug 7370692
341 WHERE rowid = X_rowid;
342
343 if (SQL%NOTFOUND) then
344 RAISE NO_DATA_FOUND;
345 end if;
346
347 END Update_Row;
348
349
350 PROCEDURE Delete_Row_Sub(X_Rowid VARCHAR2) IS
351 BEGIN
352 DELETE FROM BOM_STD_SUB_OP_RESOURCES
353 WHERE rowid = X_Rowid;
354
355 if (SQL%NOTFOUND) then
356 RAISE NO_DATA_FOUND;
357 end if;
358 END Delete_Row_Sub;
359
360 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
361 X_Standard_Operation_Id NUMBER,
362 X_Resource_Seq_Num NUMBER) IS
363 dummy NUMBER;
364 BEGIN
365 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
366 (SELECT 1 FROM BOM_STD_OP_RESOURCES
367 WHERE Standard_Operation_Id = X_Standard_Operation_Id
368 AND Resource_Seq_Num = X_Resource_Seq_Num
369 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
370 );
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 FND_MESSAGE.SET_NAME('BOM', 'BOM_ALREADY_EXISTS');
375 FND_MESSAGE.SET_TOKEN('ENTITY1', 'SEQUENCE NUMBER_CAP', TRUE);
376 FND_MESSAGE.SET_TOKEN('ENTITY2', X_Resource_Seq_Num);
377 APP_EXCEPTION.RAISE_EXCEPTION;
378 END Check_Unique;
379
380
381 END B_STD_SUB_OP_RES_PKG;