[Home] [Help]
PACKAGE BODY: APPS.WSMPCPCS
Source
1 PACKAGE BODY WSMPCPCS as
2 /* $Header: WSMCPCSB.pls 120.2 2005/09/09 07:02:54 abgangul noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_co_product_group_id NUMBER,
6 X_substitute_component_id NUMBER,
7 X_Last_Update_Login NUMBER,
8 X_Last_Updated_By NUMBER,
9 X_Last_Update_Date DATE,
10 X_Creation_Date DATE,
11 X_Created_By NUMBER,
12 X_Substitute_Item_Quantity NUMBER,
13 X_attribute_category VARCHAR2,
14 X_Attribute1 VARCHAR2,
15 X_Attribute2 VARCHAR2,
16 X_Attribute3 VARCHAR2,
17 X_Attribute4 VARCHAR2,
18 X_Attribute5 VARCHAR2,
19 X_Attribute6 VARCHAR2,
20 X_Attribute7 VARCHAR2,
21 X_Attribute8 VARCHAR2,
22 X_Attribute9 VARCHAR2,
23 X_Attribute10 VARCHAR2,
24 X_Attribute11 VARCHAR2,
25 X_Attribute12 VARCHAR2,
26 X_Attribute13 VARCHAR2,
27 X_Attribute14 VARCHAR2,
28 X_Attribute15 VARCHAR2,
29 X_Request_Id NUMBER,
30 X_Program_Application_Id NUMBER,
31 X_Program_Id NUMBER,
32 X_Program_Update_Date DATE,
33 X_basis_type NUMBER --LBM enh
34 ) IS
35 CURSOR C IS SELECT rowid FROM WSM_CO_PROD_COMP_SUBSTITUTES
36 WHERE co_product_group_id = X_co_product_group_id
37 AND substitute_component_id = X_Substitute_Component_Id;
38 l_basis_type number; --LBM enh
39
40 BEGIN
41 if X_basis_type = 2 then --LBM enh
42 l_basis_type := 2;
43 else
44 l_basis_type := null;
45 end if; --LBM enh
46
47 INSERT INTO WSM_CO_PROD_COMP_SUBSTITUTES (
48 CO_PRODUCT_GROUP_ID,
49 SUBSTITUTE_COMPONENT_ID,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_LOGIN,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 SUBSTITUTE_ITEM_QUANTITY,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 ATTRIBUTE3,
60 ATTRIBUTE4,
61 ATTRIBUTE5,
62 ATTRIBUTE6,
63 ATTRIBUTE7,
64 ATTRIBUTE8,
65 ATTRIBUTE9,
66 ATTRIBUTE10,
67 ATTRIBUTE11,
68 ATTRIBUTE12,
69 ATTRIBUTE13,
70 ATTRIBUTE14,
71 ATTRIBUTE15,
72 REQUEST_ID,
73 PROGRAM_APPLICATION_ID,
74 PROGRAM_ID,
75 PROGRAM_UPDATE_DATE,
76 BASIS_TYPE --LBM enh
77 ) VALUES (
78 X_co_product_group_id,
79 X_substitute_component_id,
80 X_creation_date,
81 X_created_by,
82 X_last_update_login,
83 X_last_updated_by,
84 X_last_update_date,
85 X_substitute_item_quantity,
86 X_attribute_category,
87 X_attribute1,
88 X_attribute2,
89 X_attribute3,
90 X_attribute4,
91 X_attribute5,
92 X_attribute6,
93 X_attribute7,
94 X_attribute8,
95 X_attribute9,
96 X_attribute10,
97 X_attribute11,
98 X_attribute12,
99 X_attribute13,
100 X_attribute14,
101 X_attribute15,
102 X_request_id,
103 X_program_application_id,
104 X_program_id,
105 X_program_update_date,
106 l_basis_type --LBM enh
107 );
108
109
110 OPEN C;
111 FETCH C INTO X_Rowid;
112 if (C%NOTFOUND) then
113 CLOSE C;
114 Raise NO_DATA_FOUND;
115 end if;
116 CLOSE C;
117
118 END Insert_Row;
119
120 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
121 X_co_product_group_id NUMBER,
122 X_substitute_component_id NUMBER,
123 X_Last_Update_Login NUMBER,
124 X_Last_Updated_By NUMBER,
125 X_Last_Update_Date DATE,
126 X_Substitute_Item_Quantity NUMBER,
127 X_attribute_category VARCHAR2,
128 X_Attribute1 VARCHAR2,
129 X_Attribute2 VARCHAR2,
130 X_Attribute3 VARCHAR2,
131 X_Attribute4 VARCHAR2,
132 X_Attribute5 VARCHAR2,
133 X_Attribute6 VARCHAR2,
134 X_Attribute7 VARCHAR2,
135 X_Attribute8 VARCHAR2,
136 X_Attribute9 VARCHAR2,
137 X_Attribute10 VARCHAR2,
138 X_Attribute11 VARCHAR2,
139 X_Attribute12 VARCHAR2,
140 X_Attribute13 VARCHAR2,
141 X_Attribute14 VARCHAR2,
142 X_Attribute15 VARCHAR2,
143 X_Request_Id NUMBER,
144 X_Program_Application_Id NUMBER,
145 X_Program_Id NUMBER,
146 X_Program_Update_Date DATE,
147 X_basis_type NUMBER --LBM enh
148 ) IS
149
150 l_basis_type number; --LBM enh
151 BEGIN
152
153 if X_basis_type = 2 then --LBM enh
154 l_basis_type := 2;
155 else
156 l_basis_type := null;
157 end if; --LBM enh
158
159 UPDATE WSM_CO_PROD_COMP_SUBSTITUTES
160 SET
161 co_product_group_id = x_co_product_group_id,
162 substitute_component_id = x_substitute_component_id,
163 last_update_login = x_last_update_login,
164 last_updated_by = x_last_updated_by,
165 last_update_date = x_last_update_date,
166 substitute_item_quantity = x_substitute_item_quantity,
167 attribute_category = x_attribute_category,
168 attribute1 = x_attribute1,
169 attribute2 = x_attribute2,
170 attribute3 = x_attribute3,
171 attribute4 = x_attribute4,
172 attribute5 = x_attribute5,
173 attribute6 = x_attribute6,
174 attribute7 = x_attribute7,
175 attribute8 = x_attribute8,
176 attribute9 = x_attribute9,
177 attribute10 = x_attribute10,
178 attribute11 = x_attribute11,
179 attribute12 = x_attribute12,
180 attribute13 = x_attribute13,
181 attribute14 = x_attribute14,
182 attribute15 = x_attribute15,
183 request_id = x_request_id,
184 program_application_id = x_program_application_id,
185 program_id = x_program_id,
186 program_update_date = x_program_update_date,
187 basis_type = l_basis_type --LBM enh
188 WHERE rowid = X_Rowid;
189
190 if (SQL%NOTFOUND) then
191 Raise NO_DATA_FOUND;
192 end if;
193 END Update_Row;
194
195 PROCEDURE Lock_Row (X_Rowid VARCHAR2,
196 X_co_product_group_id NUMBER,
197 X_substitute_component_id NUMBER,
198 X_Substitute_Item_Quantity NUMBER,
199 X_attribute_category VARCHAR2,
200 X_Attribute1 VARCHAR2,
201 X_Attribute2 VARCHAR2,
202 X_Attribute3 VARCHAR2,
203 X_Attribute4 VARCHAR2,
204 X_Attribute5 VARCHAR2,
205 X_Attribute6 VARCHAR2,
206 X_Attribute7 VARCHAR2,
207 X_Attribute8 VARCHAR2,
208 X_Attribute9 VARCHAR2,
209 X_Attribute10 VARCHAR2,
210 X_Attribute11 VARCHAR2,
211 X_Attribute12 VARCHAR2,
212 X_Attribute13 VARCHAR2,
213 X_Attribute14 VARCHAR2,
214 X_Attribute15 VARCHAR2,
215 X_basis_type NUMBER --LBM enh
216 ) IS
217 CURSOR C IS
218 SELECT *
219 FROM WSM_CO_PROD_COMP_SUBSTITUTES
220 WHERE rowid = X_Rowid
221 FOR UPDATE of substitute_component_id NOWAIT;
222 Recinfo C%ROWTYPE;
223
224 BEGIN
225 OPEN C;
226 FETCH C INTO Recinfo;
227 if (C%NOTFOUND) then
228 CLOSE C;
229 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
230 APP_EXCEPTION.Raise_Exception;
231 end if;
232 CLOSE C;
233
234 if (
235 (Recinfo.substitute_component_id = X_Substitute_Component_Id)
236 AND (Recinfo.substitute_item_quantity = X_Substitute_Item_Quantity)
237 AND (Recinfo.co_product_group_id = X_Co_Product_Group_Id)
238 AND ( (Recinfo.attribute_category = X_Attribute_Category)
239 OR ( (Recinfo.attribute_category IS NULL)
240 AND (X_Attribute_Category IS NULL)))
241 AND ( (Recinfo.attribute1 = X_Attribute1)
242 OR ( (Recinfo.attribute1 IS NULL)
243 AND (X_Attribute1 IS NULL)))
244 AND ( (Recinfo.attribute2 = X_Attribute2)
245 OR ( (Recinfo.attribute2 IS NULL)
246 AND (X_Attribute2 IS NULL)))
247 AND ( (Recinfo.attribute3 = X_Attribute3)
248 OR ( (Recinfo.attribute3 IS NULL)
249 AND (X_Attribute3 IS NULL)))
250 AND ( (Recinfo.attribute4 = X_Attribute4)
251 OR ( (Recinfo.attribute4 IS NULL)
252 AND (X_Attribute4 IS NULL)))
253 AND ( (Recinfo.attribute5 = X_Attribute5)
254 OR ( (Recinfo.attribute5 IS NULL)
255 AND (X_Attribute5 IS NULL)))
256 AND ( (Recinfo.attribute6 = X_Attribute6)
257 OR ( (Recinfo.attribute6 IS NULL)
258 AND (X_Attribute6 IS NULL)))
259 AND ( (Recinfo.attribute7 = X_Attribute7)
260 OR ( (Recinfo.attribute7 IS NULL)
261 AND (X_Attribute7 IS NULL)))
262 AND ( (Recinfo.attribute8 = X_Attribute8)
263 OR ( (Recinfo.attribute8 IS NULL)
264 AND (X_Attribute8 IS NULL)))
265 AND ( (Recinfo.attribute9 = X_Attribute9)
266 OR ( (Recinfo.attribute9 IS NULL)
267 AND (X_Attribute9 IS NULL)))
268 AND ( (Recinfo.attribute10 = X_Attribute10)
269 OR ( (Recinfo.attribute10 IS NULL)
270 AND (X_Attribute10 IS NULL)))
271 AND ( (Recinfo.attribute11 = X_Attribute11)
272 OR ( (Recinfo.attribute11 IS NULL)
273 AND (X_Attribute11 IS NULL)))
274 AND ( (Recinfo.attribute12 = X_Attribute12)
275 OR ( (Recinfo.attribute12 IS NULL)
276 AND (X_Attribute12 IS NULL)))
277 AND ( (Recinfo.attribute13 = X_Attribute13)
278 OR ( (Recinfo.attribute13 IS NULL)
279 AND (X_Attribute13 IS NULL)))
280 AND ( (Recinfo.attribute14 = X_Attribute14)
281 OR ( (Recinfo.attribute14 IS NULL)
282 AND (X_Attribute14 IS NULL)))
283 AND ( (Recinfo.attribute15 = X_Attribute15)
284 OR ( (Recinfo.attribute15 IS NULL)
285 AND (X_Attribute15 IS NULL)))
286 AND ( (Recinfo.basis_type = X_basis_type) --LBM enh
287 OR ( (Recinfo.basis_type IS NULL) --LBM enh
288 AND (X_basis_type IS NULL))) --LBM enh
289 ) then
290 return;
291 else
292 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
293 APP_EXCEPTION.Raise_Exception;
294 end if;
295
296 END Lock_Row;
297
298
299 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
300 X_co_product_group_id NUMBER,
301 X_substitute_component_id NUMBER,
302 X_organization_id NUMBER) IS
303
304 dummy NUMBER;
305 x1_dummy NUMBER; -- abedajna
306 x_substitute VARCHAR2(820);
307
308 duplicate_sub_comp_error EXCEPTION;
309
310
311 BEGIN
312
313 -- commented out by abedajna on 10/12/00 for perf. tuning
314 /*
315 ** SELECT 1 INTO dummy
316 ** FROM DUAL
317 ** WHERE NOT EXISTS
318 ** ( SELECT 1
319 ** FROM wsm_co_prod_comp_substitutes
320 ** WHERE co_product_group_id = X_co_product_group_id
321 ** AND substitute_component_id = X_substitute_component_id
322 ** AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID)));
323 **
324 ** EXCEPTION
325 ** WHEN NO_DATA_FOUND THEN
326 ** fnd_message.set_name('WSM','WSM_DUPLICATE_SUB_COMP');
327 ** app_exception.raise_exception;
328 */
329
330 -- modification begin for perf. tuning.. abedajna 10/12/00
331
332 x1_dummy := 0;
333
334 SELECT 1 INTO x1_dummy
335 FROM wsm_co_prod_comp_substitutes
336 WHERE co_product_group_id = X_co_product_group_id
337 AND substitute_component_id = X_substitute_component_id
338 AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID));
339
340 IF x1_dummy <> 0 THEN
341 RAISE duplicate_sub_comp_error;
342 END IF;
343
344
345 EXCEPTION
346
347 WHEN NO_DATA_FOUND THEN
348 NULL;
349
350 WHEN duplicate_sub_comp_error THEN
351 fnd_message.set_name('WSM','WSM_DUPLICATE_SUB_COMP');
352 app_exception.raise_exception;
353
354 WHEN TOO_MANY_ROWS THEN
355 fnd_message.set_name('WSM','WSM_DUPLICATE_SUB_COMP');
356 app_exception.raise_exception;
357
358
359 -- modification end for perf. tuning.. abedajna 10/12/00
360
361 END Check_Unique;
362
363
364 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
365 BEGIN
366
367 DELETE FROM WSM_CO_PROD_COMP_SUBSTITUTES
368 WHERE rowid = X_Rowid;
369
370 if (SQL%NOTFOUND) then
371 Raise NO_DATA_FOUND;
372 end if;
373 END Delete_Row;
374
375 END WSMPCPCS;