[Home] [Help]
PACKAGE BODY: APPS.CST_ITEM_OVERHEAD_DEFAULTS_PKG
Source
1 PACKAGE BODY CST_ITEM_OVERHEAD_DEFAULTS_PKG as
2 /* $Header: CSTPOVDB.pls 115.2 2002/11/11 19:20:13 awwang ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Organization_Id NUMBER,
6 X_Category_Set_Id NUMBER DEFAULT NULL,
7 X_Category_Id NUMBER DEFAULT NULL,
8 X_Material_Overhead_Id NUMBER,
9 X_Item_Type NUMBER,
10 X_Last_Update_Date DATE,
11 X_Last_Updated_By NUMBER,
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Last_Update_Login NUMBER DEFAULT NULL,
15 X_Activity_Id NUMBER DEFAULT NULL,
16 X_Basis_Type NUMBER DEFAULT NULL,
17 X_Item_Units NUMBER DEFAULT NULL,
18 X_Activity_Units NUMBER DEFAULT NULL,
19 X_Usage_Rate_Or_Amount NUMBER,
20 X_Attribute_Category VARCHAR2 DEFAULT NULL,
21 X_Activity_Context 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
38 ) IS
39 CURSOR C IS SELECT rowid FROM cst_item_overhead_defaults
40 WHERE organization_id = X_Organization_Id
41 AND ( (item_type = X_Item_Type)
42 or (item_type is NULL and X_Item_Type is NULL))
43 AND ( (category_id = X_Category_Id)
44 or (category_id is NULL and X_Category_Id is NULL))
45 AND ( (material_overhead_id = X_Material_Overhead_Id)
46 or (material_overhead_id is NULL and X_Material_Overhead_Id is NULL))
47 AND ( (activity_id = X_Activity_Id)
48 or (activity_id is NULL and X_Activity_Id is NULL));
49
50
51 BEGIN
52
53
54 INSERT INTO cst_item_overhead_defaults(
55 organization_id,
56 category_set_id,
57 category_id,
58 material_overhead_id,
59 item_type,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login,
65 activity_id,
66 basis_type,
67 item_units,
68 activity_units,
69 usage_rate_or_amount,
70 attribute_category,
71 activity_context,
72 attribute1,
73 attribute2,
74 attribute3,
75 attribute4,
76 attribute5,
77 attribute6,
78 attribute7,
79 attribute8,
80 attribute9,
81 attribute10,
82 attribute11,
83 attribute12,
84 attribute13,
85 attribute14,
86 attribute15
87 ) VALUES (
88 X_Organization_Id,
89 X_Category_Set_Id,
90 X_Category_Id,
91 X_Material_Overhead_Id,
92 X_Item_Type,
93 X_Last_Update_Date,
94 X_Last_Updated_By,
95 X_Creation_Date,
96 X_Created_By,
97 X_Last_Update_Login,
98 X_Activity_Id,
99 X_Basis_Type,
100 X_Item_Units,
101 X_Activity_Units,
102 X_Usage_Rate_Or_Amount,
103 X_Attribute_Category,
104 X_Activity_Context,
105 X_Attribute1,
106 X_Attribute2,
107 X_Attribute3,
108 X_Attribute4,
109 X_Attribute5,
110 X_Attribute6,
111 X_Attribute7,
112 X_Attribute8,
113 X_Attribute9,
114 X_Attribute10,
115 X_Attribute11,
116 X_Attribute12,
117 X_Attribute13,
118 X_Attribute14,
119 X_Attribute15
120 );
121
122 OPEN C;
123 FETCH C INTO X_Rowid;
124 if (C%NOTFOUND) then
125 CLOSE C;
126 Raise NO_DATA_FOUND;
127 end if;
128 CLOSE C;
129 END Insert_Row;
130
131
132
133 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
134 X_Organization_Id NUMBER,
135 X_Category_Set_Id NUMBER DEFAULT NULL,
136 X_Category_Id NUMBER DEFAULT NULL,
137 X_Material_Overhead_Id NUMBER ,
138 X_Item_Type NUMBER ,
139 X_Activity_Id NUMBER DEFAULT NULL,
140 X_Basis_Type NUMBER DEFAULT NULL,
141 X_Item_Units NUMBER DEFAULT NULL,
142 X_Activity_Units NUMBER DEFAULT NULL,
143 X_Usage_Rate_Or_Amount NUMBER,
144 X_Attribute_Category VARCHAR2 DEFAULT NULL,
145 X_Activity_Context VARCHAR2 DEFAULT NULL,
146 X_Attribute1 VARCHAR2 DEFAULT NULL,
147 X_Attribute2 VARCHAR2 DEFAULT NULL,
148 X_Attribute3 VARCHAR2 DEFAULT NULL,
149 X_Attribute4 VARCHAR2 DEFAULT NULL,
150 X_Attribute5 VARCHAR2 DEFAULT NULL,
151 X_Attribute6 VARCHAR2 DEFAULT NULL,
152 X_Attribute7 VARCHAR2 DEFAULT NULL,
153 X_Attribute8 VARCHAR2 DEFAULT NULL,
154 X_Attribute9 VARCHAR2 DEFAULT NULL,
155 X_Attribute10 VARCHAR2 DEFAULT NULL,
156 X_Attribute11 VARCHAR2 DEFAULT NULL,
157 X_Attribute12 VARCHAR2 DEFAULT NULL,
158 X_Attribute13 VARCHAR2 DEFAULT NULL,
159 X_Attribute14 VARCHAR2 DEFAULT NULL,
160 X_Attribute15 VARCHAR2 DEFAULT NULL
161
162 ) IS
163 CURSOR C IS
164 SELECT *
165 FROM cst_item_overhead_defaults
166 WHERE rowid = X_Rowid
167 FOR UPDATE of Organization_Id,Item_Type,Category_Id,Material_Overhead_Id,
168 Activity_Id NOWAIT;
169 Recinfo C%ROWTYPE;
170 BEGIN
171 OPEN C;
172 FETCH C INTO Recinfo;
173 if (C%NOTFOUND) then
174 CLOSE C;
175 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
176 APP_EXCEPTION.Raise_Exception;
177 end if;
178 CLOSE C;
179 if (
180
181 (Recinfo.organization_id = X_Organization_Id)
182 AND ( (Recinfo.category_set_id = X_Category_Set_Id)
183 OR ( (Recinfo.category_set_id IS NULL)
184 AND (X_Category_Set_Id IS NULL)))
185 AND ( (Recinfo.category_id = X_Category_Id)
186 OR ( (Recinfo.category_id IS NULL)
187 AND (X_Category_Id IS NULL)))
188 AND ( (Recinfo.material_overhead_id = X_Material_Overhead_Id)
189 OR ( (Recinfo.material_overhead_id IS NULL)
190 AND (X_Material_Overhead_Id IS NULL)))
191 AND ( (Recinfo.item_type = X_Item_Type)
192 OR ( (Recinfo.item_type IS NULL)
193 AND (X_Item_Type IS NULL)))
194 AND ( (Recinfo.activity_id = X_Activity_Id)
195 OR ( (Recinfo.activity_id IS NULL)
196 AND (X_Activity_Id IS NULL)))
197 AND ( (Recinfo.basis_type = X_Basis_Type)
198 OR ( (Recinfo.basis_type IS NULL)
199 AND (X_Basis_Type IS NULL)))
200 AND ( (Recinfo.item_units = X_Item_Units)
201 OR ( (Recinfo.item_units IS NULL)
202 AND (X_Item_Units IS NULL)))
203 AND ( (Recinfo.activity_units = X_Activity_Units)
204 OR ( (Recinfo.activity_units IS NULL)
205 AND (X_Activity_Units IS NULL)))
206 AND (Recinfo.usage_rate_or_amount = X_Usage_Rate_Or_Amount)
207 AND ( (Recinfo.attribute_category = X_Attribute_Category)
208 OR ( (Recinfo.attribute_category IS NULL)
209 AND (X_Attribute_Category IS NULL)))
210 AND ( (Recinfo.activity_context = X_Activity_Context)
211 OR ( (Recinfo.activity_context IS NULL)
212 AND (X_Activity_Context 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
259 ) then
260 return;
261 else
262 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 end if;
265 END Lock_Row;
266
267
268
269 PROCEDURE Update_Row(X_Rowid VARCHAR2,
270 X_Organization_Id NUMBER,
271 X_Category_Set_Id NUMBER DEFAULT NULL,
272 X_Category_Id NUMBER DEFAULT NULL,
273 X_Material_Overhead_Id NUMBER ,
274 X_Item_Type NUMBER ,
275 X_Last_Update_Date DATE,
276 X_Last_Updated_By NUMBER,
277 X_Last_Update_Login NUMBER DEFAULT NULL,
278 X_Activity_Id NUMBER DEFAULT NULL,
279 X_Basis_Type NUMBER DEFAULT NULL,
280 X_Item_Units NUMBER DEFAULT NULL,
281 X_Activity_Units NUMBER DEFAULT NULL,
282 X_Usage_Rate_Or_Amount NUMBER,
283 X_Attribute_Category VARCHAR2 DEFAULT NULL,
284 X_Activity_Context VARCHAR2 DEFAULT NULL,
285 X_Attribute1 VARCHAR2 DEFAULT NULL,
286 X_Attribute2 VARCHAR2 DEFAULT NULL,
287 X_Attribute3 VARCHAR2 DEFAULT NULL,
288 X_Attribute4 VARCHAR2 DEFAULT NULL,
289 X_Attribute5 VARCHAR2 DEFAULT NULL,
290 X_Attribute6 VARCHAR2 DEFAULT NULL,
291 X_Attribute7 VARCHAR2 DEFAULT NULL,
292 X_Attribute8 VARCHAR2 DEFAULT NULL,
293 X_Attribute9 VARCHAR2 DEFAULT NULL,
297 X_Attribute13 VARCHAR2 DEFAULT NULL,
294 X_Attribute10 VARCHAR2 DEFAULT NULL,
295 X_Attribute11 VARCHAR2 DEFAULT NULL,
296 X_Attribute12 VARCHAR2 DEFAULT NULL,
298 X_Attribute14 VARCHAR2 DEFAULT NULL,
299 X_Attribute15 VARCHAR2 DEFAULT NULL
300
301 ) IS
305 organization_id = X_Organization_Id,
302 BEGIN
303 UPDATE cst_item_overhead_defaults
304 SET
306 category_set_id = X_Category_Set_Id,
307 category_id = X_Category_Id,
308 material_overhead_id = X_Material_Overhead_Id,
309 item_type = X_Item_Type,
310 last_update_date = X_Last_Update_Date,
311 last_updated_by = X_Last_Updated_By,
312 last_update_login = X_Last_Update_Login,
313 activity_id = X_Activity_Id,
314 basis_type = X_Basis_Type,
315 item_units = X_Item_Units,
316 activity_units = X_Activity_Units,
317 usage_rate_or_amount = X_Usage_Rate_Or_Amount,
318 attribute_category = X_Attribute_Category,
319 activity_context = X_Activity_Context,
320 attribute1 = X_Attribute1,
321 attribute2 = X_Attribute2,
322 attribute3 = X_Attribute3,
323 attribute4 = X_Attribute4,
324 attribute5 = X_Attribute5,
325 attribute6 = X_Attribute6,
326 attribute7 = X_Attribute7,
327 attribute8 = X_Attribute8,
328 attribute9 = X_Attribute9,
329 attribute10 = X_Attribute10,
330 attribute11 = X_Attribute11,
331 attribute12 = X_Attribute12,
332 attribute13 = X_Attribute13,
333 attribute14 = X_Attribute14,
334 attribute15 = X_Attribute15
335 WHERE rowid = X_rowid;
336
337 if (SQL%NOTFOUND) then
338 Raise NO_DATA_FOUND;
339 end if;
340
341 END Update_Row;
342
343 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
344 BEGIN
345 DELETE FROM cst_item_overhead_defaults
346 WHERE rowid = X_Rowid;
347
348 if (SQL%NOTFOUND) then
349 Raise NO_DATA_FOUND;
350 end if;
351 END Delete_Row;
352
353 END CST_ITEM_OVERHEAD_DEFAULTS_PKG;