[Home] [Help]
PACKAGE BODY: APPS.AS_INTERESTS_PKG
Source
4 --
1 PACKAGE BODY AS_INTERESTS_PKG as
2 /* $Header: asxininb.pls 115.10 2004/01/16 07:02:40 gbatra ship $ */
3
5 -- ??-???-94 J Sondergaard Created
9 -- table handlers
6 -- 04-JUN-95 J Sondergaard Added Interest_Use_Code and
7 -- Interest_Type_Id
8 -- 26-DEC-96 J Kornberg Added program who columns and lead id to
10 -- 30-OCT-03 gbatra Product Hierarchy uptake changes
11 --
12 --
13
14 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
15 X_Interest_Id IN OUT NOCOPY 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_Interest_Use_Code VARCHAR2,
22 X_Interest_Type_Id NUMBER,
23 X_Contact_Id NUMBER,
24 X_Customer_Id NUMBER,
25 X_Address_Id NUMBER,
26 X_Primary_Interest_Code_Id NUMBER DEFAULT NULL,
27 X_Secondary_Interest_Code_Id NUMBER DEFAULT NULL,
28 X_Status_Code VARCHAR2 DEFAULT NULL,
29 X_Description VARCHAR2 DEFAULT NULL,
30 X_Attribute_Category VARCHAR2 DEFAULT NULL,
31 X_Attribute1 VARCHAR2 DEFAULT NULL,
32 X_Attribute2 VARCHAR2 DEFAULT NULL,
33 X_Attribute3 VARCHAR2 DEFAULT NULL,
34 X_Attribute4 VARCHAR2 DEFAULT NULL,
35 X_Attribute5 VARCHAR2 DEFAULT NULL,
36 X_Attribute6 VARCHAR2 DEFAULT NULL,
37 X_Attribute7 VARCHAR2 DEFAULT NULL,
38 X_Attribute8 VARCHAR2 DEFAULT NULL,
39 X_Attribute9 VARCHAR2 DEFAULT NULL,
40 X_Attribute10 VARCHAR2 DEFAULT NULL,
41 X_Attribute11 VARCHAR2 DEFAULT NULL,
42 X_Attribute12 VARCHAR2 DEFAULT NULL,
43 X_Attribute13 VARCHAR2 DEFAULT NULL,
44 X_Attribute14 VARCHAR2 DEFAULT NULL,
45 X_Attribute15 VARCHAR2 DEFAULT NULL,
46 X_Lead_Id NUMBER DEFAULT NULL,
47 X_Request_Id NUMBER DEFAULT NULL,
48 X_Program_Application_Id NUMBER DEFAULT NULL,
49 X_Program_Id NUMBER DEFAULT NULL,
50 X_Program_Update_Date DATE DEFAULT NULL,
51 X_Product_Category_Id NUMBER,
52 X_Product_Cat_Set_Id NUMBER
53
54 ) IS
55 CURSOR C IS SELECT rowid FROM as_interests
56 WHERE interest_id = X_Interest_Id;
57 CURSOR C2 IS SELECT as_interests_s.nextval FROM sys.dual;
58
59 X_User_Id NUMBER;
60 X_Login_Id NUMBER;
61 X_Date DATE;
62 BEGIN
63 if (X_Interest_Id is NULL) then
64 OPEN C2;
65 FETCH C2 INTO X_Interest_Id;
66 CLOSE C2;
67 end if;
68 if (X_Created_By is NULL) then
69 X_User_Id := FND_GLOBAL.User_Id;
70 X_Login_Id := FND_GLOBAL.Login_Id;
71 X_Date := SYSDATE;
72 else
73 X_User_Id := X_Created_By;
74 X_Login_Id := X_Last_Update_Login;
75 X_Date := X_Creation_Date;
76 end if;
77
78 INSERT INTO as_interests(
79 interest_id,
80 last_update_date,
81 last_updated_by,
82 creation_date,
83 created_by,
84 last_update_login,
85 request_id,
86 program_application_id,
87 program_id,
88 program_update_date,
89 interest_use_code,
90 interest_type_id,
91 contact_id,
92 customer_id,
93 address_id,
94 lead_id,
95 primary_interest_code_id,
96 secondary_interest_code_id,
97 Status_Code,
98 description,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15,
115 product_category_id,
116 product_cat_set_id
117 ) VALUES (
118 X_Interest_Id,
119 X_Date,
120 X_User_Id,
121 X_Date,
122 X_User_Id,
123 X_Login_Id,
124 X_Request_Id,
125 X_Program_Application_Id,
126 X_Program_Id,
127 X_Program_Update_Date,
128 X_Interest_Use_Code,
129 X_Interest_Type_Id,
130 X_Contact_Id,
131 X_Customer_Id,
132 X_Address_Id,
133 X_Lead_Id,
134 X_Primary_Interest_Code_Id,
138 X_Attribute_Category,
135 X_Secondary_Interest_Code_Id,
136 X_Status_Code,
137 X_Description,
139 X_Attribute1,
140 X_Attribute2,
141 X_Attribute3,
142 X_Attribute4,
143 X_Attribute5,
144 X_Attribute6,
145 X_Attribute7,
146 X_Attribute8,
147 X_Attribute9,
148 X_Attribute10,
149 X_Attribute11,
150 X_Attribute12,
151 X_Attribute13,
152 X_Attribute14,
153 X_Attribute15,
154 X_Product_Category_Id,
155 X_Product_Cat_Set_Id);
156
157 OPEN C;
158 FETCH C INTO X_Rowid;
159 if (C%NOTFOUND) then
160 CLOSE C;
161 RAISE NO_DATA_FOUND;
162 end if;
163 CLOSE C;
164 END Insert_Row;
165
166
170 X_Interest_Type_Id NUMBER,
167 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
168 X_Interest_Id NUMBER,
169 X_Interest_Use_Code VARCHAR2,
171 X_Contact_Id NUMBER,
172 X_Customer_Id NUMBER,
173 X_Address_Id NUMBER,
174 X_Primary_Interest_Code_Id NUMBER DEFAULT NULL,
175 X_Secondary_Interest_Code_Id NUMBER DEFAULT NULL,
176 X_Status_Code VARCHAR2 DEFAULT NULL,
177 X_Description VARCHAR2 DEFAULT NULL,
178 X_Attribute_Category VARCHAR2 DEFAULT NULL,
179 X_Attribute1 VARCHAR2 DEFAULT NULL,
180 X_Attribute2 VARCHAR2 DEFAULT NULL,
181 X_Attribute3 VARCHAR2 DEFAULT NULL,
182 X_Attribute4 VARCHAR2 DEFAULT NULL,
183 X_Attribute5 VARCHAR2 DEFAULT NULL,
184 X_Attribute6 VARCHAR2 DEFAULT NULL,
185 X_Attribute7 VARCHAR2 DEFAULT NULL,
186 X_Attribute8 VARCHAR2 DEFAULT NULL,
187 X_Attribute9 VARCHAR2 DEFAULT NULL,
188 X_Attribute10 VARCHAR2 DEFAULT NULL,
189 X_Attribute11 VARCHAR2 DEFAULT NULL,
190 X_Attribute12 VARCHAR2 DEFAULT NULL,
191 X_Attribute13 VARCHAR2 DEFAULT NULL,
192 X_Attribute14 VARCHAR2 DEFAULT NULL,
193 X_Attribute15 VARCHAR2 DEFAULT NULL,
194 X_Lead_Id NUMBER DEFAULT NULL,
195 X_Product_Category_Id NUMBER,
196 X_Product_Cat_Set_Id NUMBER
197
198 ) IS
199 CURSOR C IS
200 SELECT *
201 FROM as_interests
202 WHERE rowid = X_Rowid
203 FOR UPDATE of Interest_Id NOWAIT;
204 Recinfo C%ROWTYPE;
205 BEGIN
206 OPEN C;
207 FETCH C INTO Recinfo;
208 if (C%NOTFOUND) then
209 CLOSE C;
210 RAISE NO_DATA_FOUND;
211 end if;
212 CLOSE C;
213 if ( ( (Recinfo.interest_id = X_Interest_Id)
214 OR ( (Recinfo.interest_id IS NULL)
215 AND (X_Interest_Id IS NULL)))
216 AND ( (Recinfo.interest_type_id = X_Interest_Type_Id)
217 OR ( (Recinfo.interest_type_id IS NULL)
221 AND (X_Contact_Id IS NULL)))
218 AND (X_Interest_Type_Id IS NULL)))
219 AND ( (Recinfo.contact_id = X_Contact_Id)
220 OR ( (Recinfo.contact_id IS NULL)
222 AND ( (Recinfo.customer_id = X_Customer_Id)
223 OR ( (Recinfo.customer_id IS NULL)
224 AND (X_Customer_Id IS NULL)))
225 AND ( (Recinfo.address_id = X_Address_Id)
226 OR ( (Recinfo.address_id IS NULL)
227 AND (X_Address_Id IS NULL)))
228 AND ( (Recinfo.interest_use_code = X_Interest_Use_Code)
229 OR ( (Recinfo.interest_use_code IS NULL)
230 AND (X_Interest_Use_Code IS NULL)))
231 AND ( (Recinfo.primary_interest_code_id = X_Primary_Interest_Code_Id)
232 OR ( (Recinfo.primary_interest_code_id IS NULL)
233 AND (X_Primary_Interest_Code_Id IS NULL)))
234 AND ( (Recinfo.secondary_interest_code_id = X_Secondary_Interest_Code_Id)
235 OR ( (Recinfo.secondary_interest_code_id IS NULL)
236 AND (X_Secondary_Interest_Code_Id IS NULL)))
237 AND ( (Recinfo.Status_Code = X_Status_Code)
238 OR ( (Recinfo.Status_Code IS NULL)
239 AND (X_Status_Code IS NULL)))
240 AND ( (Recinfo.description = X_Description)
241 OR ( (Recinfo.description IS NULL)
242 AND (X_Description IS NULL)))
243 AND ( (Recinfo.attribute_category = X_Attribute_Category)
244 OR ( (Recinfo.attribute_category IS NULL)
245 AND (X_Attribute_Category IS NULL)))
246 AND ( (Recinfo.attribute1 = X_Attribute1)
247 OR ( (Recinfo.attribute1 IS NULL)
248 AND (X_Attribute1 IS NULL)))
249 AND ( (Recinfo.attribute2 = X_Attribute2)
250 OR ( (Recinfo.attribute2 IS NULL)
251 AND (X_Attribute2 IS NULL)))
252 AND ( (Recinfo.attribute3 = X_Attribute3)
253 OR ( (Recinfo.attribute3 IS NULL)
254 AND (X_Attribute3 IS NULL)))
255 AND ( (Recinfo.attribute4 = X_Attribute4)
256 OR ( (Recinfo.attribute4 IS NULL)
257 AND (X_Attribute4 IS NULL)))
258 AND ( (Recinfo.attribute5 = X_Attribute5)
259 OR ( (Recinfo.attribute5 IS NULL)
260 AND (X_Attribute5 IS NULL)))
261 AND ( (Recinfo.attribute6 = X_Attribute6)
262 OR ( (Recinfo.attribute6 IS NULL)
263 AND (X_Attribute6 IS NULL)))
264 AND ( (Recinfo.attribute7 = X_Attribute7)
265 OR ( (Recinfo.attribute7 IS NULL)
266 AND (X_Attribute7 IS NULL)))
267 AND ( (Recinfo.attribute8 = X_Attribute8)
268 OR ( (Recinfo.attribute8 IS NULL)
269 AND (X_Attribute8 IS NULL)))
270 AND ( (Recinfo.attribute9 = X_Attribute9)
271 OR ( (Recinfo.attribute9 IS NULL)
272 AND (X_Attribute9 IS NULL)))
273 AND ( (Recinfo.attribute10 = X_Attribute10)
274 OR ( (Recinfo.attribute10 IS NULL)
275 AND (X_Attribute10 IS NULL)))
276 AND ( (Recinfo.attribute11 = X_Attribute11)
277 OR ( (Recinfo.attribute11 IS NULL)
278 AND (X_Attribute11 IS NULL)))
279 AND ( (Recinfo.attribute12 = X_Attribute12)
280 OR ( (Recinfo.attribute12 IS NULL)
281 AND (X_Attribute12 IS NULL)))
282 AND ( (Recinfo.attribute13 = X_Attribute13)
283 OR ( (Recinfo.attribute13 IS NULL)
284 AND (X_Attribute13 IS NULL)))
285 AND ( (Recinfo.attribute14 = X_Attribute14)
286 OR ( (Recinfo.attribute14 IS NULL)
287 AND (X_Attribute14 IS NULL)))
288 AND ( (Recinfo.attribute15 = X_Attribute15)
289 OR ( (Recinfo.attribute15 IS NULL)
290 AND (X_Attribute15 IS NULL)))
291 AND ( (Recinfo.lead_id = X_Lead_Id)
292 OR ( (Recinfo.lead_id IS NULL)
293 AND (X_Lead_Id IS NULL)))
294 AND ( (Recinfo.product_category_id = X_Product_Category_Id)
295 OR ( (Recinfo.product_category_id IS NULL)
296 AND (X_Product_Category_Id IS NULL)))
297 AND ( (Recinfo.product_cat_set_id = X_Product_Cat_Set_Id)
298 OR ( (Recinfo.product_cat_set_id IS NULL)
299 AND (X_Product_Cat_Set_Id IS NULL)))
300 ) then
301 return;
302 else
303 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
304 APP_EXCEPTION.RAISE_EXCEPTION;
305 end if;
306 END Lock_Row;
307
308 PROCEDURE Update_Row(X_Rowid VARCHAR2,
309 X_Interest_Id NUMBER,
310 X_Last_Update_Date DATE,
311 X_Last_Updated_By NUMBER,
312 X_Last_Update_Login NUMBER,
313 X_Interest_Use_Code VARCHAR2,
314 X_Interest_Type_Id NUMBER,
315 X_Contact_Id NUMBER,
316 X_Customer_Id NUMBER,
317 X_Address_Id NUMBER,
318 X_Primary_Interest_Code_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
319 X_Secondary_Interest_Code_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
320 X_Status_Code VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
321 X_Description VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
322 X_Attribute_Category VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
326 X_Attribute4 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
323 X_Attribute1 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
324 X_Attribute2 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
325 X_Attribute3 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
327 X_Attribute5 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
328 X_Attribute6 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
329 X_Attribute7 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
330 X_Attribute8 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
331 X_Attribute9 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
332 X_Attribute10 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
333 X_Attribute11 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
334 X_Attribute12 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
335 X_Attribute13 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
336 X_Attribute14 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
337 X_Attribute15 VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
338 X_Lead_Id NUMBER DEFAULT FND_API.G_MISS_NUM,
339 X_Request_Id NUMBER DEFAULT NULL,
340 X_Program_Application_Id NUMBER DEFAULT NULL,
341 X_Program_Id NUMBER DEFAULT NULL,
342 X_Program_Update_Date DATE DEFAULT NULL,
343 X_Product_Category_Id NUMBER,
344 X_Product_Cat_Set_Id NUMBER
345
346 ) IS
347 l_description varchar2(30);
348 BEGIN
349 UPDATE as_interests_all
350 SET
351 object_version_number = nvl(object_version_number,0) + 1,
352 interest_id = decode(X_Interest_Id,FND_API.G_MISS_NUM, interest_id, X_Interest_Id),
353 last_update_date = X_Last_Update_Date,
354 last_updated_by = X_Last_Updated_By,
355 last_update_login = X_Last_Update_Login,
356 interest_use_code = decode(X_Interest_Use_Code,FND_API.G_MISS_CHAR,interest_use_code,X_Interest_Use_Code),
357 interest_type_id = decode(X_Interest_Type_Id, FND_API.G_MISS_NUM,interest_type_id, X_Interest_Type_Id),
358 contact_id = decode(X_Contact_Id,FND_API.G_MISS_NUM, contact_id, X_Contact_Id),
359 customer_id = decode(X_Customer_Id,FND_API.G_MISS_NUM,customer_id,X_Customer_Id),
360 address_id = decode(X_Address_Id,FND_API.G_MISS_NUM, address_id, X_Address_Id),
361 primary_interest_code_id =decode(X_Primary_Interest_Code_Id,
362 FND_API.G_MISS_NUM, primary_interest_code_id,X_Primary_Interest_Code_Id),
363 secondary_interest_code_id = decode(X_Secondary_Interest_Code_Id,
364 FND_API.G_MISS_NUM, Secondary_interest_code_id,X_Secondary_Interest_Code_Id),
365 Status_Code = decode(X_Status_Code,FND_API.G_MISS_CHAR, status_code, X_Status_Code),
366 description = decode(X_Description,FND_API.G_MISS_CHAR, description, X_Description),
367 attribute_category = decode(X_Attribute_Category,FND_API.G_MISS_CHAR,attribute_category,X_Attribute_Category),
368 attribute1 = decode(X_Attribute1,FND_API.G_MISS_CHAR,attribute1,X_Attribute1),
369 attribute2 = decode(X_Attribute2,FND_API.G_MISS_CHAR,attribute2,X_Attribute2),
370 attribute3 = decode(X_Attribute3,FND_API.G_MISS_CHAR,attribute3,X_Attribute3),
371 attribute4 = decode(X_Attribute4,FND_API.G_MISS_CHAR,attribute4,X_Attribute4),
372 attribute5 = decode(X_Attribute5,FND_API.G_MISS_CHAR,attribute5,X_Attribute5),
373 attribute6 = decode(X_Attribute6,FND_API.G_MISS_CHAR,attribute6,X_Attribute6),
374 attribute7 = decode(X_Attribute7,FND_API.G_MISS_CHAR,attribute7,X_Attribute7),
375 attribute8 = decode(X_Attribute8,FND_API.G_MISS_CHAR,attribute8,X_Attribute8),
376 attribute9 = decode(X_Attribute9,FND_API.G_MISS_CHAR,attribute9,X_Attribute9),
377 attribute10 = decode(X_Attribute10,FND_API.G_MISS_CHAR,attribute10,X_Attribute10),
378 attribute11 = decode(X_Attribute11,FND_API.G_MISS_CHAR,attribute11,X_Attribute11),
379 attribute12 = decode(X_Attribute12,FND_API.G_MISS_CHAR,attribute12,X_Attribute12),
380 attribute13 = decode(X_Attribute13,FND_API.G_MISS_CHAR,attribute13,X_Attribute13),
381 attribute14 = decode(X_Attribute14,FND_API.G_MISS_CHAR,attribute14,X_Attribute14),
382 attribute15 = decode(X_Attribute15,FND_API.G_MISS_CHAR,attribute15,X_Attribute15),
383 lead_id = decode(X_Lead_Id,FND_API.G_MISS_NUM, lead_id,X_Lead_Id),
384 request_id = X_Request_Id,
385 program_application_id = X_Program_Application_Id,
386 program_id = X_Program_Id,
387 program_update_date = X_Program_Update_Date,
391
388 product_category_id = decode(X_Product_Category_Id, FND_API.G_MISS_NUM,product_category_id, X_Product_Category_Id),
389 product_cat_set_id = decode(X_Product_Cat_Set_Id, FND_API.G_MISS_NUM,product_cat_set_id, X_Product_Cat_Set_Id)
390 WHERE rowid = X_rowid;
392 if (SQL%NOTFOUND) then
393 RAISE NO_DATA_FOUND;
394 end if;
395
396 END Update_Row;
397
398 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
399 BEGIN
400 DELETE FROM as_interests
401 WHERE rowid = X_Rowid;
402
403 if (SQL%NOTFOUND) then
404 RAISE NO_DATA_FOUND;
405 end if;
406 END Delete_Row;
407
408 END AS_INTERESTS_PKG;