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