[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_LINES_PKG
Source
1 PACKAGE BODY AS_SALES_LEAD_LINES_PKG as
2 /* $Header: asxtsllb.pls 115.9 2004/04/14 20:39:39 chchandr ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_LINES_PKG
5 -- Purpose : Sales lead lines table handlers
6 -- NOTE :
7 -- History : 04/09/2001 FFANG Created
8 --
9 -- End of Comments
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEAD_LINES_PKG';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtsllb.pls';
14
15
16 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
20
21 PROCEDURE Sales_Lead_Line_Insert_Row(
22 px_SALES_LEAD_LINE_ID IN OUT NOCOPY NUMBER,
23 p_LAST_UPDATE_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_CREATION_DATE DATE,
26 p_CREATED_BY NUMBER,
27 p_LAST_UPDATE_LOGIN NUMBER,
28 p_REQUEST_ID NUMBER,
29 p_PROGRAM_APPLICATION_ID NUMBER,
30 p_PROGRAM_ID NUMBER,
31 p_PROGRAM_UPDATE_DATE DATE,
32 p_SALES_LEAD_ID NUMBER,
33 p_STATUS_CODE VARCHAR2,
34
35 -- 11.5.10 rivendell product category changes
36
37 --p_INTEREST_TYPE_ID NUMBER,
38 --p_PRIMARY_INTEREST_CODE_ID NUMBER,
39 --p_SECONDARY_INTEREST_CODE_ID NUMBER,
40
41 p_CATEGORY_ID NUMBER,
42 p_CATEGORY_SET_ID NUMBER,
43
44 p_INVENTORY_ITEM_ID NUMBER,
45 p_ORGANIZATION_ID NUMBER,
46 p_UOM_CODE VARCHAR2,
47 p_QUANTITY NUMBER,
48 p_BUDGET_AMOUNT NUMBER,
49 p_SOURCE_PROMOTION_ID NUMBER,
50 p_ATTRIBUTE_CATEGORY VARCHAR2,
51 p_ATTRIBUTE1 VARCHAR2,
52 p_ATTRIBUTE2 VARCHAR2,
53 p_ATTRIBUTE3 VARCHAR2,
54 p_ATTRIBUTE4 VARCHAR2,
55 p_ATTRIBUTE5 VARCHAR2,
56 p_ATTRIBUTE6 VARCHAR2,
57 p_ATTRIBUTE7 VARCHAR2,
58 p_ATTRIBUTE8 VARCHAR2,
59 p_ATTRIBUTE9 VARCHAR2,
60 p_ATTRIBUTE10 VARCHAR2,
61 p_ATTRIBUTE11 VARCHAR2,
62 p_ATTRIBUTE12 VARCHAR2,
63 p_ATTRIBUTE13 VARCHAR2,
64 p_ATTRIBUTE14 VARCHAR2,
65 p_ATTRIBUTE15 VARCHAR2,
66 p_OFFER_ID NUMBER)
67 -- p_SECURITY_GROUP_ID NUMBER)
68
69 IS
70 CURSOR C2 IS SELECT AS_SALES_LEAD_LINES_S.nextval FROM sys.dual;
71 BEGIN
72 If (px_sales_lead_line_id IS NULL)
73 OR (px_SALES_LEAD_LINE_ID = FND_API.G_MISS_NUM)
74 Then
75 OPEN C2;
76 FETCH C2 INTO px_sales_lead_line_id;
77 CLOSE C2;
78 End If;
79 INSERT INTO AS_SALES_LEAD_LINES(
80 SALES_LEAD_LINE_ID,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_LOGIN,
86 REQUEST_ID,
87 PROGRAM_APPLICATION_ID,
88 PROGRAM_ID,
89 PROGRAM_UPDATE_DATE,
90 SALES_LEAD_ID,
91 STATUS_CODE,
92 INTEREST_TYPE_ID,
93 PRIMARY_INTEREST_CODE_ID,
94 SECONDARY_INTEREST_CODE_ID,
95 CATEGORY_ID,
96 CATEGORY_SET_ID,
97 INVENTORY_ITEM_ID,
98 ORGANIZATION_ID,
99 UOM_CODE,
100 QUANTITY,
101 BUDGET_AMOUNT,
102 SOURCE_PROMOTION_ID,
103 ATTRIBUTE_CATEGORY,
104 ATTRIBUTE1,
105 ATTRIBUTE2,
106 ATTRIBUTE3,
107 ATTRIBUTE4,
108 ATTRIBUTE5,
109 ATTRIBUTE6,
110 ATTRIBUTE7,
111 ATTRIBUTE8,
112 ATTRIBUTE9,
113 ATTRIBUTE10,
114 ATTRIBUTE11,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
118 ATTRIBUTE15,
119 OFFER_ID)
120 -- SECURITY_GROUP_ID)
121 VALUES (
122 px_SALES_LEAD_LINE_ID,
123 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,TO_DATE(NULL),p_LAST_UPDATE_DATE),
124 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
125 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
126 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
127 decode( p_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,NULL,p_LAST_UPDATE_LOGIN),
128 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
129 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,
130 p_PROGRAM_APPLICATION_ID),
131 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
132 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),
133 p_PROGRAM_UPDATE_DATE),
134 decode( p_SALES_LEAD_ID, FND_API.G_MISS_NUM, NULL, p_SALES_LEAD_ID),
135
136 decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
137
138 -- 11.5.10 rivendell product category changes
139
140 --decode( p_INTEREST_TYPE_ID, FND_API.G_MISS_NUM,NULL, p_INTEREST_TYPE_ID),
141 --decode( p_PRIMARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL,
142 -- p_PRIMARY_INTEREST_CODE_ID),
143 --decode( p_SECONDARY_INTEREST_CODE_ID, FND_API.G_MISS_NUM, NULL,
144 -- p_SECONDARY_INTEREST_CODE_ID),
145 NULL,
146 NULL,
147 NULL,
148
149 decode(p_CATEGORY_ID, FND_API.G_MISS_NUM, NULL, p_CATEGORY_ID),
150 decode(p_CATEGORY_SET_ID, FND_API.G_MISS_NUM, NULL, p_CATEGORY_SET_ID),
151
152
153 decode( p_INVENTORY_ITEM_ID,FND_API.G_MISS_NUM,NULL,p_INVENTORY_ITEM_ID),
154 decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
155 decode( p_UOM_CODE, FND_API.G_MISS_CHAR, NULL, p_UOM_CODE),
156 decode( p_QUANTITY, FND_API.G_MISS_NUM, NULL, p_QUANTITY),
157 decode( p_BUDGET_AMOUNT, FND_API.G_MISS_NUM, NULL, p_BUDGET_AMOUNT),
158 decode( p_SOURCE_PROMOTION_ID, FND_API.G_MISS_NUM, NULL,
159 p_SOURCE_PROMOTION_ID),
160 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,
161 p_ATTRIBUTE_CATEGORY),
162 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
163 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
164 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
165 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
166 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
167 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
168 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
169 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
170 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
171 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
172 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
173 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
174 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
175 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
176 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
177 decode( p_OFFER_ID, FND_API.G_MISS_NUM, NULL, p_OFFER_ID));
178 -- decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL,
179 -- p_SECURITY_GROUP_ID));
180 End Sales_Lead_Line_Insert_Row;
181
182 PROCEDURE Sales_Lead_Line_Update_Row(
183 p_SALES_LEAD_LINE_ID NUMBER,
184 p_LAST_UPDATE_DATE DATE,
185 p_LAST_UPDATED_BY NUMBER,
186 p_CREATION_DATE DATE,
187 p_CREATED_BY NUMBER,
188 p_LAST_UPDATE_LOGIN NUMBER,
189 p_REQUEST_ID NUMBER,
190 p_PROGRAM_APPLICATION_ID NUMBER,
191 p_PROGRAM_ID NUMBER,
192 p_PROGRAM_UPDATE_DATE DATE,
193 p_SALES_LEAD_ID NUMBER,
194 p_STATUS_CODE VARCHAR2,
195
196 -- 11.5.10 rivendell product category changes
197 -- p_INTEREST_TYPE_ID NUMBER,
198 -- p_PRIMARY_INTEREST_CODE_ID NUMBER,
199 -- p_SECONDARY_INTEREST_CODE_ID NUMBER,
200
201 p_CATEGORY_ID NUMBER,
202 p_CATEGORY_SET_ID NUMBER,
203
204 p_INVENTORY_ITEM_ID NUMBER,
205 p_ORGANIZATION_ID NUMBER,
206 p_UOM_CODE VARCHAR2,
207 p_QUANTITY NUMBER,
208 p_BUDGET_AMOUNT NUMBER,
209 p_SOURCE_PROMOTION_ID NUMBER,
210 p_ATTRIBUTE_CATEGORY VARCHAR2,
211 p_ATTRIBUTE1 VARCHAR2,
212 p_ATTRIBUTE2 VARCHAR2,
213 p_ATTRIBUTE3 VARCHAR2,
214 p_ATTRIBUTE4 VARCHAR2,
215 p_ATTRIBUTE5 VARCHAR2,
216 p_ATTRIBUTE6 VARCHAR2,
217 p_ATTRIBUTE7 VARCHAR2,
218 p_ATTRIBUTE8 VARCHAR2,
219 p_ATTRIBUTE9 VARCHAR2,
220 p_ATTRIBUTE10 VARCHAR2,
221 p_ATTRIBUTE11 VARCHAR2,
222 p_ATTRIBUTE12 VARCHAR2,
223 p_ATTRIBUTE13 VARCHAR2,
224 p_ATTRIBUTE14 VARCHAR2,
225 p_ATTRIBUTE15 VARCHAR2,
226 p_OFFER_ID NUMBER)
227 -- p_SECURITY_GROUP_ID NUMBER)
228
229 IS
230 /*l_obj_verno number;
231
232 cursor c_obj_verno is
233 select object_version_number
234 from AS_SALES_LEAD_LINES
235 where SALES_LEAD_LINE_ID = p_SALES_LEAD_LINE_ID;
236 */
237 BEGIN
238 Update AS_SALES_LEAD_LINES
239 SET
240 SALES_LEAD_LINE_ID = decode( p_SALES_LEAD_LINE_ID, FND_API.G_MISS_NUM,
241 SALES_LEAD_LINE_ID, p_SALES_LEAD_LINE_ID),
242 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,
243 LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
244 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM,
245 LAST_UPDATED_BY, p_LAST_UPDATED_BY),
246 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE,
247 CREATION_DATE, p_CREATION_DATE),
248 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY,
249 p_CREATED_BY),
250 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,
251 LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
252 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID,
253 p_REQUEST_ID),
254 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID,
255 FND_API.G_MISS_NUM,
256 PROGRAM_APPLICATION_ID,
257 p_PROGRAM_APPLICATION_ID),
258 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID,
259 p_PROGRAM_ID),
260 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE,
261 PROGRAM_UPDATE_DATE,p_PROGRAM_UPDATE_DATE),
262 SALES_LEAD_ID = decode( p_SALES_LEAD_ID, FND_API.G_MISS_NUM,
263 SALES_LEAD_ID, p_SALES_LEAD_ID),
264 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE,
265 p_STATUS_CODE),
266
267 -- 11.5.10 rivendell product category
268
269 --INTEREST_TYPE_ID = decode( p_INTEREST_TYPE_ID, FND_API.G_MISS_NUM,
270 -- INTEREST_TYPE_ID, p_INTEREST_TYPE_ID),
271 --PRIMARY_INTEREST_CODE_ID = decode( p_PRIMARY_INTEREST_CODE_ID,
272 -- FND_API.G_MISS_NUM,
273 -- PRIMARY_INTEREST_CODE_ID,
274 -- p_PRIMARY_INTEREST_CODE_ID),
275 --SECONDARY_INTEREST_CODE_ID = decode( p_SECONDARY_INTEREST_CODE_ID,
276 -- FND_API.G_MISS_NUM,
277 -- SECONDARY_INTEREST_CODE_ID,
278 -- p_SECONDARY_INTEREST_CODE_ID),
279
280 CATEGORY_ID = decode( p_CATEGORY_ID,
281 FND_API.G_MISS_NUM,
282 CATEGORY_ID,
283 p_CATEGORY_ID),
284
285 CATEGORY_SET_ID = decode( p_CATEGORY_SET_ID,
286 FND_API.G_MISS_NUM,
287 CATEGORY_SET_ID,
288 p_CATEGORY_SET_ID),
289
290
291 INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM,
292 INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID),
293 ORGANIZATION_ID = decode( p_ORGANIZATION_ID, FND_API.G_MISS_NUM,
294 ORGANIZATION_ID, p_ORGANIZATION_ID),
295 UOM_CODE = decode( p_UOM_CODE, FND_API.G_MISS_CHAR, UOM_CODE,p_UOM_CODE),
296 QUANTITY = decode( p_QUANTITY, FND_API.G_MISS_NUM, QUANTITY, p_QUANTITY),
297 BUDGET_AMOUNT = decode( p_BUDGET_AMOUNT, FND_API.G_MISS_NUM,
298 BUDGET_AMOUNT, p_BUDGET_AMOUNT),
299 SOURCE_PROMOTION_ID = decode( p_SOURCE_PROMOTION_ID, FND_API.G_MISS_NUM,
300 SOURCE_PROMOTION_ID,
301 p_SOURCE_PROMOTION_ID),
302 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR,
303 ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
304 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1,
305 p_ATTRIBUTE1),
306 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2,
307 p_ATTRIBUTE2),
308 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3,
309 p_ATTRIBUTE3),
310 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4,
311 p_ATTRIBUTE4),
312 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5,
313 p_ATTRIBUTE5),
314 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6,
315 p_ATTRIBUTE6),
316 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7,
317 p_ATTRIBUTE7),
318 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8,
319 p_ATTRIBUTE8),
320 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9,
321 p_ATTRIBUTE9),
322 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10,
323 p_ATTRIBUTE10),
324 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11,
325 p_ATTRIBUTE11),
329 p_ATTRIBUTE13),
326 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12,
327 p_ATTRIBUTE12),
328 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13,
330 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14,
331 p_ATTRIBUTE14),
332 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15,
333 p_ATTRIBUTE15),
334 OFFER_ID = decode( p_OFFER_ID, FND_API.G_MISS_NUM, OFFER_ID, p_OFFER_ID),
335 object_version_number = decode(object_version_number, null, 1, object_version_number+1)
336 -- SECURITY_GROUP_ID = decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM,
337 -- SECURITY_GROUP_ID, p_SECURITY_GROUP_ID)
338 WHERE sales_lead_line_id = p_sales_lead_line_id;
339 /*
340 open c_obj_verno;
341 fetch c_obj_verno into l_obj_verno;
342 close c_obj_verno;
343
344 update as_sales_lead_lines
345 set object_version_number = decode(l_obj_verno, null, 1, l_obj_verno+1)
346 where sales_lead_line_id = p_sales_lead_line_id;
347 */
348 If (SQL%NOTFOUND) then
349 RAISE NO_DATA_FOUND;
350 End If;
351 END Sales_Lead_Line_Update_Row;
352
353
354 PROCEDURE Sales_Lead_Line_Delete_Row( p_sales_lead_line_id NUMBER)
355 IS
356 BEGIN
357 DELETE FROM AS_SALES_LEAD_LINES
358 WHERE sales_lead_line_id = p_sales_lead_line_id;
359 If (SQL%NOTFOUND) then
360 RAISE NO_DATA_FOUND;
361 End If;
362 END Sales_Lead_Line_Delete_Row;
363
364
365 PROCEDURE Sales_Lead_Line_Lock_Row(
366 p_SALES_LEAD_LINE_ID NUMBER,
367 p_LAST_UPDATE_DATE DATE,
368 p_LAST_UPDATED_BY NUMBER,
369 p_CREATION_DATE DATE,
370 p_CREATED_BY NUMBER,
371 p_LAST_UPDATE_LOGIN NUMBER,
372 p_REQUEST_ID NUMBER,
373 p_PROGRAM_APPLICATION_ID NUMBER,
374 p_PROGRAM_ID NUMBER,
375 p_PROGRAM_UPDATE_DATE DATE,
376 p_SALES_LEAD_ID NUMBER,
377 p_STATUS_CODE VARCHAR2,
378
379 -- 11.5.10 rivendell product category changes
380
381 --p_INTEREST_TYPE_ID NUMBER,
382 --p_PRIMARY_INTEREST_CODE_ID NUMBER,
383 --p_SECONDARY_INTEREST_CODE_ID NUMBER,
384
385 p_CATEGORY_ID NUMBER,
386 p_CATEGORY_SET_ID NUMBER,
387
388 p_INVENTORY_ITEM_ID NUMBER,
389 p_ORGANIZATION_ID NUMBER,
390 p_UOM_CODE VARCHAR2,
391 p_QUANTITY NUMBER,
392 p_BUDGET_AMOUNT NUMBER,
393 p_SOURCE_PROMOTION_ID NUMBER,
394 p_ATTRIBUTE_CATEGORY VARCHAR2,
395 p_ATTRIBUTE1 VARCHAR2,
396 p_ATTRIBUTE2 VARCHAR2,
397 p_ATTRIBUTE3 VARCHAR2,
398 p_ATTRIBUTE4 VARCHAR2,
399 p_ATTRIBUTE5 VARCHAR2,
400 p_ATTRIBUTE6 VARCHAR2,
401 p_ATTRIBUTE7 VARCHAR2,
402 p_ATTRIBUTE8 VARCHAR2,
403 p_ATTRIBUTE9 VARCHAR2,
404 p_ATTRIBUTE10 VARCHAR2,
405 p_ATTRIBUTE11 VARCHAR2,
406 p_ATTRIBUTE12 VARCHAR2,
407 p_ATTRIBUTE13 VARCHAR2,
408 p_ATTRIBUTE14 VARCHAR2,
409 p_ATTRIBUTE15 VARCHAR2,
410 p_OFFER_ID NUMBER)
411 -- p_SECURITY_GROUP_ID NUMBER)
412
413 IS
414 CURSOR C IS
415 SELECT *
416 FROM AS_SALES_LEAD_LINES
417 WHERE sales_lead_line_id = p_sales_lead_line_id
418 FOR UPDATE of sales_lead_line_id NOWAIT;
419 Recinfo C%ROWTYPE;
420 BEGIN
421 OPEN C;
422 FETCH C INTO Recinfo;
423 If (C%NOTFOUND) then
424 CLOSE C;
425 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
426 APP_EXCEPTION.RAISE_EXCEPTION;
427 End If;
428 CLOSE C;
429 if (
430 ( Recinfo.SALES_LEAD_LINE_ID = p_SALES_LEAD_LINE_ID)
431 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
432 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
433 AND ( p_LAST_UPDATE_DATE IS NULL )))
434 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
435 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
436 AND ( p_LAST_UPDATED_BY IS NULL )))
437 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
438 OR ( ( Recinfo.CREATION_DATE IS NULL )
439 AND ( p_CREATION_DATE IS NULL )))
440 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
441 OR ( ( Recinfo.CREATED_BY IS NULL )
442 AND ( p_CREATED_BY IS NULL )))
443 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
444 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
445 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
446 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
447 OR ( ( Recinfo.REQUEST_ID IS NULL )
448 AND ( p_REQUEST_ID IS NULL )))
449 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
450 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
451 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
452 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
453 OR ( ( Recinfo.PROGRAM_ID IS NULL )
454 AND ( p_PROGRAM_ID IS NULL )))
455 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
456 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
460 AND ( p_SALES_LEAD_ID IS NULL )))
457 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
458 AND ( ( Recinfo.SALES_LEAD_ID = p_SALES_LEAD_ID)
459 OR ( ( Recinfo.SALES_LEAD_ID IS NULL )
461 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
462 OR ( ( Recinfo.STATUS_CODE IS NULL )
463 AND ( p_STATUS_CODE IS NULL )))
464
465 -- 11.5.10 rivendell product category changes
466
467 --AND ( ( Recinfo.INTEREST_TYPE_ID = p_INTEREST_TYPE_ID)
468 -- OR ( ( Recinfo.INTEREST_TYPE_ID IS NULL )
469 -- AND ( p_INTEREST_TYPE_ID IS NULL )))
470 --AND ( ( Recinfo.PRIMARY_INTEREST_CODE_ID = p_PRIMARY_INTEREST_CODE_ID)
471 -- OR ( ( Recinfo.PRIMARY_INTEREST_CODE_ID IS NULL )
472 -- AND ( p_PRIMARY_INTEREST_CODE_ID IS NULL )))
473 --AND ( ( Recinfo.SECONDARY_INTEREST_CODE_ID =
474 -- p_SECONDARY_INTEREST_CODE_ID)
475 -- OR ( ( Recinfo.SECONDARY_INTEREST_CODE_ID IS NULL )
476 -- AND ( p_SECONDARY_INTEREST_CODE_ID IS NULL )))
477
478
479
480 AND ( ( Recinfo.CATEGORY_ID = p_CATEGORY_ID)
481 OR ( ( Recinfo.CATEGORY_ID IS NULL )
482 AND ( p_CATEGORY_ID IS NULL )))
483 AND ( ( Recinfo.CATEGORY_SET_ID =
484 p_CATEGORY_SET_ID)
485 OR ( ( Recinfo.CATEGORY_SET_ID IS NULL )
486 AND ( p_CATEGORY_SET_ID IS NULL )))
487
488
489
490 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
491 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
492 AND ( p_INVENTORY_ITEM_ID IS NULL )))
493 AND ( ( Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID)
494 OR ( ( Recinfo.ORGANIZATION_ID IS NULL )
495 AND ( p_ORGANIZATION_ID IS NULL )))
496 AND ( ( Recinfo.UOM_CODE = p_UOM_CODE)
497 OR ( ( Recinfo.UOM_CODE IS NULL )
498 AND ( p_UOM_CODE IS NULL )))
499 AND ( ( Recinfo.QUANTITY = p_QUANTITY)
500 OR ( ( Recinfo.QUANTITY IS NULL )
501 AND ( p_QUANTITY IS NULL )))
502 AND ( ( Recinfo.BUDGET_AMOUNT = p_BUDGET_AMOUNT)
503 OR ( ( Recinfo.BUDGET_AMOUNT IS NULL )
504 AND ( p_BUDGET_AMOUNT IS NULL )))
505 AND ( ( Recinfo.SOURCE_PROMOTION_ID = p_SOURCE_PROMOTION_ID)
506 OR ( ( Recinfo.SOURCE_PROMOTION_ID IS NULL )
507 AND ( p_SOURCE_PROMOTION_ID IS NULL )))
508 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
509 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
510 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
511 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
512 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
513 AND ( p_ATTRIBUTE1 IS NULL )))
514 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
515 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
516 AND ( p_ATTRIBUTE2 IS NULL )))
517 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
518 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
519 AND ( p_ATTRIBUTE3 IS NULL )))
520 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
521 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
522 AND ( p_ATTRIBUTE4 IS NULL )))
523 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
524 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
525 AND ( p_ATTRIBUTE5 IS NULL )))
526 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
527 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
528 AND ( p_ATTRIBUTE6 IS NULL )))
529 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
530 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
531 AND ( p_ATTRIBUTE7 IS NULL )))
532 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
533 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
534 AND ( p_ATTRIBUTE8 IS NULL )))
535 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
536 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
537 AND ( p_ATTRIBUTE9 IS NULL )))
538 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
539 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
540 AND ( p_ATTRIBUTE10 IS NULL )))
541 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
542 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
543 AND ( p_ATTRIBUTE11 IS NULL )))
544 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
545 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
546 AND ( p_ATTRIBUTE12 IS NULL )))
547 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
548 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
549 AND ( p_ATTRIBUTE13 IS NULL )))
550 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
551 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
552 AND ( p_ATTRIBUTE14 IS NULL )))
553 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
554 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
555 AND ( p_ATTRIBUTE15 IS NULL )))
556 AND ( ( Recinfo.OFFER_ID = p_OFFER_ID)
557 OR ( ( Recinfo.OFFER_ID IS NULL )
558 AND ( p_OFFER_ID IS NULL )))
559 -- AND ( ( Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
560 -- OR ( ( Recinfo.SECURITY_GROUP_ID IS NULL )
561 -- AND ( p_SECURITY_GROUP_ID IS NULL )))
562 ) then
563 return;
564 else
565 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
566 APP_EXCEPTION.RAISE_EXCEPTION;
567 End If;
568 END Sales_Lead_Line_Lock_Row;
569
570
571 End AS_SALES_LEAD_LINES_PKG;