[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_CREDITS_PKG
Source
1 PACKAGE BODY AS_SALES_CREDITS_PKG as
2 /* $Header: asxtlscb.pls 120.1 2005/08/29 23:39:44 appldev ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_CREDITS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_CREDITS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtlscb.pls';
13
14 PROCEDURE Insert_Row(
15 px_SALES_CREDIT_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_LEAD_LINE_ID NUMBER,
27 p_SALESFORCE_ID NUMBER,
28 p_PERSON_ID NUMBER,
29 p_SALESGROUP_ID NUMBER,
30 p_PARTNER_CUSTOMER_ID NUMBER,
31 p_PARTNER_ADDRESS_ID NUMBER,
32 p_REVENUE_AMOUNT NUMBER,
33 p_REVENUE_PERCENT NUMBER,
34 p_QUOTA_CREDIT_AMOUNT NUMBER,
35 p_QUOTA_CREDIT_PERCENT NUMBER,
36 p_ATTRIBUTE_CATEGORY VARCHAR2,
37 p_ATTRIBUTE1 VARCHAR2,
38 p_ATTRIBUTE2 VARCHAR2,
39 p_ATTRIBUTE3 VARCHAR2,
40 p_ATTRIBUTE4 VARCHAR2,
41 p_ATTRIBUTE5 VARCHAR2,
42 p_ATTRIBUTE6 VARCHAR2,
43 p_ATTRIBUTE7 VARCHAR2,
44 p_ATTRIBUTE8 VARCHAR2,
45 p_ATTRIBUTE9 VARCHAR2,
46 p_ATTRIBUTE10 VARCHAR2,
47 p_ATTRIBUTE11 VARCHAR2,
48 p_ATTRIBUTE12 VARCHAR2,
49 p_ATTRIBUTE13 VARCHAR2,
50 p_ATTRIBUTE14 VARCHAR2,
51 p_ATTRIBUTE15 VARCHAR2,
52 p_MANAGER_REVIEW_FLAG VARCHAR2,
53 p_MANAGER_REVIEW_DATE DATE,
54 p_ORIGINAL_SALES_CREDIT_ID NUMBER,
55 --p_CREDIT_TYPE VARCHAR2,
56 p_CREDIT_PERCENT NUMBER,
57 p_CREDIT_AMOUNT NUMBER,
58 -- p_SECURITY_GROUP_ID NUMBER,
59 p_CREDIT_TYPE_ID NUMBER,
60 p_OPP_WORST_FORECAST_AMOUNT NUMBER,
61 p_OPP_FORECAST_AMOUNT NUMBER,
62 p_OPP_BEST_FORECAST_AMOUNT NUMBER,
63 P_DEFAULTED_FROM_OWNER_FLAG VARCHAR2 DEFAULT NULL -- Added for ASNB
64 )
65
66 IS
67 CURSOR C2 IS SELECT AS_SALES_CREDITS_S.nextval FROM sys.dual;
68 l_OPP_WORST_FORECAST_AMOUNT NUMBER := p_OPP_WORST_FORECAST_AMOUNT;
69 l_OPP_FORECAST_AMOUNT NUMBER := p_OPP_FORECAST_AMOUNT;
70 l_OPP_BEST_FORECAST_AMOUNT NUMBER := p_OPP_BEST_FORECAST_AMOUNT;
71 BEGIN
72 If (px_SALES_CREDIT_ID IS NULL) OR (px_SALES_CREDIT_ID = FND_API.G_MISS_NUM) then
73 OPEN C2;
74 FETCH C2 INTO px_SALES_CREDIT_ID;
75 CLOSE C2;
76 End If;
77
78 IF l_OPP_WORST_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
79 l_OPP_WORST_FORECAST_AMOUNT := 0;
80 END IF;
81
82 IF l_OPP_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
83 l_OPP_FORECAST_AMOUNT := 0;
84 END IF;
85
86 IF l_OPP_BEST_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
87 l_OPP_BEST_FORECAST_AMOUNT := 0;
88 END IF;
89
90 INSERT INTO AS_SALES_CREDITS(
91 SALES_CREDIT_ID,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 CREATION_DATE,
95 CREATED_BY,
96 LAST_UPDATE_LOGIN,
97 REQUEST_ID,
98 PROGRAM_APPLICATION_ID,
99 PROGRAM_ID,
100 PROGRAM_UPDATE_DATE,
101 LEAD_ID,
102 LEAD_LINE_ID,
103 SALESFORCE_ID,
104 PERSON_ID,
105 SALESGROUP_ID,
106 PARTNER_CUSTOMER_ID,
107 PARTNER_ADDRESS_ID,
108 REVENUE_AMOUNT,
109 REVENUE_PERCENT,
110 QUOTA_CREDIT_AMOUNT,
111 QUOTA_CREDIT_PERCENT,
112 ATTRIBUTE_CATEGORY,
113 ATTRIBUTE1,
114 ATTRIBUTE2,
115 ATTRIBUTE3,
116 ATTRIBUTE4,
117 ATTRIBUTE5,
118 ATTRIBUTE6,
119 ATTRIBUTE7,
120 ATTRIBUTE8,
121 ATTRIBUTE9,
122 ATTRIBUTE10,
123 ATTRIBUTE11,
124 ATTRIBUTE12,
125 ATTRIBUTE13,
126 ATTRIBUTE14,
127 ATTRIBUTE15,
128 MANAGER_REVIEW_FLAG,
129 MANAGER_REVIEW_DATE,
130 ORIGINAL_SALES_CREDIT_ID,
131 -- CREDIT_TYPE,
132 CREDIT_PERCENT,
133 CREDIT_AMOUNT,
134 -- SECURITY_GROUP_ID,
135 CREDIT_TYPE_ID,
136 OPP_WORST_FORECAST_AMOUNT,
137 OPP_FORECAST_AMOUNT,
138 OPP_BEST_FORECAST_AMOUNT,
139 DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
140 ) VALUES (
141 px_SALES_CREDIT_ID,
142 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
143 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
144 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
145 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
146 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
147 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
148 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
149 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
150 decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_PROGRAM_UPDATE_DATE),
151 decode( p_LEAD_ID, FND_API.G_MISS_NUM, NULL, p_LEAD_ID),
152 decode( p_LEAD_LINE_ID, FND_API.G_MISS_NUM, NULL, p_LEAD_LINE_ID),
153 decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL, p_SALESFORCE_ID),
154 decode( p_PERSON_ID, FND_API.G_MISS_NUM, NULL, p_PERSON_ID),
155 decode( p_SALESGROUP_ID, FND_API.G_MISS_NUM, NULL, p_SALESGROUP_ID),
156 decode( p_PARTNER_CUSTOMER_ID, FND_API.G_MISS_NUM, NULL, p_PARTNER_CUSTOMER_ID),
157 decode( p_PARTNER_ADDRESS_ID, FND_API.G_MISS_NUM, NULL, p_PARTNER_ADDRESS_ID),
158 decode( p_REVENUE_AMOUNT, FND_API.G_MISS_NUM, NULL, p_REVENUE_AMOUNT),
159 decode( p_REVENUE_PERCENT, FND_API.G_MISS_NUM, NULL, p_REVENUE_PERCENT),
160 decode( p_QUOTA_CREDIT_AMOUNT, FND_API.G_MISS_NUM, NULL, p_QUOTA_CREDIT_AMOUNT),
161 decode( p_QUOTA_CREDIT_PERCENT, FND_API.G_MISS_NUM, NULL, p_QUOTA_CREDIT_PERCENT),
162 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
163 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
164 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
165 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
166 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
167 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
168 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
169 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
170 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
171 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
172 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
173 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
174 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
175 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
176 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
177 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
178 decode( p_MANAGER_REVIEW_FLAG, FND_API.G_MISS_CHAR, NULL, p_MANAGER_REVIEW_FLAG),
179 decode( p_MANAGER_REVIEW_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_MANAGER_REVIEW_DATE),
180 decode( p_ORIGINAL_SALES_CREDIT_ID, FND_API.G_MISS_NUM, NULL, p_ORIGINAL_SALES_CREDIT_ID),
181 -- decode( p_CREDIT_TYPE, FND_API.G_MISS_CHAR, NULL, p_CREDIT_TYPE),
182 decode( p_CREDIT_PERCENT, FND_API.G_MISS_NUM, NULL, p_CREDIT_PERCENT),
183 decode( p_CREDIT_AMOUNT, FND_API.G_MISS_NUM, NULL, p_CREDIT_AMOUNT),
184 -- decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_SECURITY_GROUP_ID),
185 decode( p_CREDIT_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_CREDIT_TYPE_ID),
186 nvl(l_OPP_WORST_FORECAST_AMOUNT, 0),
187 nvl(l_OPP_FORECAST_AMOUNT, 0),
188 nvl(l_OPP_BEST_FORECAST_AMOUNT, 0),
189 decode( P_DEFAULTED_FROM_OWNER_FLAG, FND_API.G_MISS_CHAR, NULL, P_DEFAULTED_FROM_OWNER_FLAG) -- Added for ASNB
190 );
191 End Insert_Row;
192
193 PROCEDURE Update_Row(
194 p_SALES_CREDIT_ID NUMBER,
195 p_LAST_UPDATE_DATE DATE,
196 p_LAST_UPDATED_BY NUMBER,
197 p_CREATION_DATE DATE,
198 p_CREATED_BY NUMBER,
199 p_LAST_UPDATE_LOGIN NUMBER,
200 p_REQUEST_ID NUMBER,
201 p_PROGRAM_APPLICATION_ID NUMBER,
202 p_PROGRAM_ID NUMBER,
203 p_PROGRAM_UPDATE_DATE DATE,
204 p_LEAD_ID NUMBER,
205 p_LEAD_LINE_ID NUMBER,
206 p_SALESFORCE_ID NUMBER,
207 p_PERSON_ID NUMBER,
208 p_SALESGROUP_ID NUMBER,
209 p_PARTNER_CUSTOMER_ID NUMBER,
210 p_PARTNER_ADDRESS_ID NUMBER,
211 p_REVENUE_AMOUNT NUMBER,
212 p_REVENUE_PERCENT NUMBER,
213 p_QUOTA_CREDIT_AMOUNT NUMBER,
214 p_QUOTA_CREDIT_PERCENT NUMBER,
215 p_ATTRIBUTE_CATEGORY VARCHAR2,
216 p_ATTRIBUTE1 VARCHAR2,
217 p_ATTRIBUTE2 VARCHAR2,
218 p_ATTRIBUTE3 VARCHAR2,
219 p_ATTRIBUTE4 VARCHAR2,
220 p_ATTRIBUTE5 VARCHAR2,
221 p_ATTRIBUTE6 VARCHAR2,
222 p_ATTRIBUTE7 VARCHAR2,
223 p_ATTRIBUTE8 VARCHAR2,
224 p_ATTRIBUTE9 VARCHAR2,
225 p_ATTRIBUTE10 VARCHAR2,
226 p_ATTRIBUTE11 VARCHAR2,
227 p_ATTRIBUTE12 VARCHAR2,
228 p_ATTRIBUTE13 VARCHAR2,
229 p_ATTRIBUTE14 VARCHAR2,
230 p_ATTRIBUTE15 VARCHAR2,
231 p_MANAGER_REVIEW_FLAG VARCHAR2,
232 p_MANAGER_REVIEW_DATE DATE,
233 p_ORIGINAL_SALES_CREDIT_ID NUMBER,
234 -- p_CREDIT_TYPE VARCHAR2,
235 p_CREDIT_PERCENT NUMBER,
236 p_CREDIT_AMOUNT NUMBER,
237 -- p_SECURITY_GROUP_ID NUMBER,
238 p_CREDIT_TYPE_ID NUMBER,
239 p_OPP_WORST_FORECAST_AMOUNT NUMBER,
240 p_OPP_FORECAST_AMOUNT NUMBER,
241 p_OPP_BEST_FORECAST_AMOUNT NUMBER,
242 P_DEFAULTED_FROM_OWNER_FLAG VARCHAR2 DEFAULT NULL) -- Added for ASNB
243
244 IS
245 l_OPP_WORST_FORECAST_AMOUNT NUMBER := p_OPP_WORST_FORECAST_AMOUNT;
246 l_OPP_FORECAST_AMOUNT NUMBER := p_OPP_FORECAST_AMOUNT;
247 l_OPP_BEST_FORECAST_AMOUNT NUMBER := p_OPP_BEST_FORECAST_AMOUNT;
248 BEGIN
249 IF l_OPP_WORST_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
250 l_OPP_WORST_FORECAST_AMOUNT := NULL;
251 END IF;
252
253 IF l_OPP_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
254 l_OPP_FORECAST_AMOUNT := NULL;
255 END IF;
256
257 IF l_OPP_BEST_FORECAST_AMOUNT = FND_API.G_MISS_NUM THEN
258 l_OPP_BEST_FORECAST_AMOUNT := NULL;
259 END IF;
260
261 Update AS_SALES_CREDITS
262 SET object_version_number = nvl(object_version_number,0) + 1,
263 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
264 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
265 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
266 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
267 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
268 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
269 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
270 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
271 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
272 LEAD_ID = decode( p_LEAD_ID, FND_API.G_MISS_NUM, LEAD_ID, p_LEAD_ID),
273 LEAD_LINE_ID = decode( p_LEAD_LINE_ID, FND_API.G_MISS_NUM, LEAD_LINE_ID, p_LEAD_LINE_ID),
274 SALESFORCE_ID = decode( p_SALESFORCE_ID, FND_API.G_MISS_NUM, SALESFORCE_ID, p_SALESFORCE_ID),
278 PARTNER_ADDRESS_ID = decode( p_PARTNER_ADDRESS_ID, FND_API.G_MISS_NUM, PARTNER_ADDRESS_ID, p_PARTNER_ADDRESS_ID),
275 PERSON_ID = decode( p_PERSON_ID, FND_API.G_MISS_NUM, PERSON_ID, p_PERSON_ID),
276 SALESGROUP_ID = decode( p_SALESGROUP_ID, FND_API.G_MISS_NUM, SALESGROUP_ID, p_SALESGROUP_ID),
277 PARTNER_CUSTOMER_ID = decode( p_PARTNER_CUSTOMER_ID, FND_API.G_MISS_NUM, PARTNER_CUSTOMER_ID, p_PARTNER_CUSTOMER_ID),
279 REVENUE_AMOUNT = decode( p_REVENUE_AMOUNT, FND_API.G_MISS_NUM, REVENUE_AMOUNT, p_REVENUE_AMOUNT),
280 REVENUE_PERCENT = decode( p_REVENUE_PERCENT, FND_API.G_MISS_NUM, REVENUE_PERCENT, p_REVENUE_PERCENT),
281 QUOTA_CREDIT_AMOUNT = decode( p_QUOTA_CREDIT_AMOUNT, FND_API.G_MISS_NUM, QUOTA_CREDIT_AMOUNT, p_QUOTA_CREDIT_AMOUNT),
282 QUOTA_CREDIT_PERCENT = decode( p_QUOTA_CREDIT_PERCENT, FND_API.G_MISS_NUM, QUOTA_CREDIT_PERCENT, p_QUOTA_CREDIT_PERCENT),
283 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
284 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
285 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
286 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
287 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
288 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
289 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
290 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
291 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
292 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
293 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
294 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
295 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
296 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
297 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
298 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15),
299 MANAGER_REVIEW_FLAG = decode( p_MANAGER_REVIEW_FLAG, FND_API.G_MISS_CHAR, MANAGER_REVIEW_FLAG, p_MANAGER_REVIEW_FLAG),
300 MANAGER_REVIEW_DATE = decode( p_MANAGER_REVIEW_DATE, FND_API.G_MISS_DATE, MANAGER_REVIEW_DATE, p_MANAGER_REVIEW_DATE),
301 ORIGINAL_SALES_CREDIT_ID = decode( p_ORIGINAL_SALES_CREDIT_ID, FND_API.G_MISS_NUM, ORIGINAL_SALES_CREDIT_ID, p_ORIGINAL_SALES_CREDIT_ID),
302 -- CREDIT_TYPE = decode( p_CREDIT_TYPE, FND_API.G_MISS_CHAR, CREDIT_TYPE, p_CREDIT_TYPE),
303 CREDIT_PERCENT = decode( p_CREDIT_PERCENT, FND_API.G_MISS_NUM, CREDIT_PERCENT, p_CREDIT_PERCENT),
304 CREDIT_AMOUNT = decode( p_CREDIT_AMOUNT, FND_API.G_MISS_NUM, CREDIT_AMOUNT, p_CREDIT_AMOUNT),
305 -- SECURITY_GROUP_ID = decode( p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM, SECURITY_GROUP_ID, p_SECURITY_GROUP_ID),
306 CREDIT_TYPE_ID = decode( p_CREDIT_TYPE_ID, FND_API.G_MISS_NUM, CREDIT_TYPE_ID, p_CREDIT_TYPE_ID),
307 OPP_WORST_FORECAST_AMOUNT = nvl(l_OPP_WORST_FORECAST_AMOUNT, OPP_WORST_FORECAST_AMOUNT),
308 OPP_FORECAST_AMOUNT = nvl(l_OPP_FORECAST_AMOUNT, OPP_FORECAST_AMOUNT),
309 OPP_BEST_FORECAST_AMOUNT = nvl(l_OPP_BEST_FORECAST_AMOUNT, OPP_BEST_FORECAST_AMOUNT),
310 DEFAULTED_FROM_OWNER_FLAG = NVL(P_DEFAULTED_FROM_OWNER_FLAG,DEFAULTED_FROM_OWNER_FLAG) -- Added for ASNB
311 where SALES_CREDIT_ID = p_SALES_CREDIT_ID;
312
313 If (SQL%NOTFOUND) then
314 RAISE NO_DATA_FOUND;
315 End If;
316 END Update_Row;
317
318 PROCEDURE Delete_Row(
319 p_SALES_CREDIT_ID NUMBER)
320 IS
321 BEGIN
322 DELETE FROM AS_SALES_CREDITS
323 WHERE SALES_CREDIT_ID = p_SALES_CREDIT_ID;
324 If (SQL%NOTFOUND) then
325 RAISE NO_DATA_FOUND;
326 End If;
327 END Delete_Row;
328
329 PROCEDURE Lock_Row(
330 p_SALES_CREDIT_ID NUMBER,
331 p_LAST_UPDATE_DATE DATE,
332 p_LAST_UPDATED_BY NUMBER,
333 p_CREATION_DATE DATE,
334 p_CREATED_BY NUMBER,
335 p_LAST_UPDATE_LOGIN NUMBER,
336 p_REQUEST_ID NUMBER,
337 p_PROGRAM_APPLICATION_ID NUMBER,
338 p_PROGRAM_ID NUMBER,
339 p_PROGRAM_UPDATE_DATE DATE,
340 p_LEAD_ID NUMBER,
341 p_LEAD_LINE_ID NUMBER,
342 p_SALESFORCE_ID NUMBER,
343 p_PERSON_ID NUMBER,
344 p_SALESGROUP_ID NUMBER,
345 p_PARTNER_CUSTOMER_ID NUMBER,
346 p_PARTNER_ADDRESS_ID NUMBER,
347 p_REVENUE_AMOUNT NUMBER,
348 p_REVENUE_PERCENT NUMBER,
349 p_QUOTA_CREDIT_AMOUNT NUMBER,
350 p_QUOTA_CREDIT_PERCENT NUMBER,
351 p_ATTRIBUTE_CATEGORY VARCHAR2,
352 p_ATTRIBUTE1 VARCHAR2,
353 p_ATTRIBUTE2 VARCHAR2,
354 p_ATTRIBUTE3 VARCHAR2,
355 p_ATTRIBUTE4 VARCHAR2,
356 p_ATTRIBUTE5 VARCHAR2,
357 p_ATTRIBUTE6 VARCHAR2,
358 p_ATTRIBUTE7 VARCHAR2,
359 p_ATTRIBUTE8 VARCHAR2,
360 p_ATTRIBUTE9 VARCHAR2,
361 p_ATTRIBUTE10 VARCHAR2,
362 p_ATTRIBUTE11 VARCHAR2,
363 p_ATTRIBUTE12 VARCHAR2,
364 p_ATTRIBUTE13 VARCHAR2,
365 p_ATTRIBUTE14 VARCHAR2,
366 p_ATTRIBUTE15 VARCHAR2,
367 p_MANAGER_REVIEW_FLAG VARCHAR2,
371 p_CREDIT_PERCENT NUMBER,
368 p_MANAGER_REVIEW_DATE DATE,
369 p_ORIGINAL_SALES_CREDIT_ID NUMBER,
370 -- p_CREDIT_TYPE VARCHAR2,
372 p_CREDIT_AMOUNT NUMBER,
373 -- p_SECURITY_GROUP_ID NUMBER,
374 p_CREDIT_TYPE_ID NUMBER)
375
376 IS
377 CURSOR C IS
378 SELECT *
379 FROM AS_SALES_CREDITS
380 WHERE SALES_CREDIT_ID = p_SALES_CREDIT_ID
381 FOR UPDATE of SALES_CREDIT_ID NOWAIT;
382 Recinfo C%ROWTYPE;
383 BEGIN
384 OPEN C;
385 FETCH C INTO Recinfo;
386 If (C%NOTFOUND) then
387 CLOSE C;
388 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
389 APP_EXCEPTION.RAISE_EXCEPTION;
390 End If;
391 CLOSE C;
392 if (
393 ( Recinfo.SALES_CREDIT_ID = p_SALES_CREDIT_ID)
394 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
395 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
396 AND ( p_LAST_UPDATE_DATE IS NULL )))
397 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
398 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
399 AND ( p_LAST_UPDATED_BY IS NULL )))
400 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
401 OR ( ( Recinfo.CREATION_DATE IS NULL )
402 AND ( p_CREATION_DATE IS NULL )))
403 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
404 OR ( ( Recinfo.CREATED_BY IS NULL )
405 AND ( p_CREATED_BY IS NULL )))
406 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
407 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
408 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
409 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
410 OR ( ( Recinfo.REQUEST_ID IS NULL )
411 AND ( p_REQUEST_ID IS NULL )))
412 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
413 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
414 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
415 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
416 OR ( ( Recinfo.PROGRAM_ID IS NULL )
417 AND ( p_PROGRAM_ID IS NULL )))
418 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
419 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
420 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
421 AND ( ( Recinfo.LEAD_ID = p_LEAD_ID)
422 OR ( ( Recinfo.LEAD_ID IS NULL )
423 AND ( p_LEAD_ID IS NULL )))
424 AND ( ( Recinfo.LEAD_LINE_ID = p_LEAD_LINE_ID)
425 OR ( ( Recinfo.LEAD_LINE_ID IS NULL )
426 AND ( p_LEAD_LINE_ID IS NULL )))
427 AND ( ( Recinfo.SALESFORCE_ID = p_SALESFORCE_ID)
428 OR ( ( Recinfo.SALESFORCE_ID IS NULL )
429 AND ( p_SALESFORCE_ID IS NULL )))
430 AND ( ( Recinfo.PERSON_ID = p_PERSON_ID)
431 OR ( ( Recinfo.PERSON_ID IS NULL )
432 AND ( p_PERSON_ID IS NULL )))
433 AND ( ( Recinfo.SALESGROUP_ID = p_SALESGROUP_ID)
434 OR ( ( Recinfo.SALESGROUP_ID IS NULL )
435 AND ( p_SALESGROUP_ID IS NULL )))
436 AND ( ( Recinfo.PARTNER_CUSTOMER_ID = p_PARTNER_CUSTOMER_ID)
437 OR ( ( Recinfo.PARTNER_CUSTOMER_ID IS NULL )
438 AND ( p_PARTNER_CUSTOMER_ID IS NULL )))
439 AND ( ( Recinfo.PARTNER_ADDRESS_ID = p_PARTNER_ADDRESS_ID)
440 OR ( ( Recinfo.PARTNER_ADDRESS_ID IS NULL )
441 AND ( p_PARTNER_ADDRESS_ID IS NULL )))
442 AND ( ( Recinfo.REVENUE_AMOUNT = p_REVENUE_AMOUNT)
443 OR ( ( Recinfo.REVENUE_AMOUNT IS NULL )
444 AND ( p_REVENUE_AMOUNT IS NULL )))
445 AND ( ( Recinfo.REVENUE_PERCENT = p_REVENUE_PERCENT)
446 OR ( ( Recinfo.REVENUE_PERCENT IS NULL )
447 AND ( p_REVENUE_PERCENT IS NULL )))
448 AND ( ( Recinfo.QUOTA_CREDIT_AMOUNT = p_QUOTA_CREDIT_AMOUNT)
449 OR ( ( Recinfo.QUOTA_CREDIT_AMOUNT IS NULL )
450 AND ( p_QUOTA_CREDIT_AMOUNT IS NULL )))
451 AND ( ( Recinfo.QUOTA_CREDIT_PERCENT = p_QUOTA_CREDIT_PERCENT)
452 OR ( ( Recinfo.QUOTA_CREDIT_PERCENT IS NULL )
453 AND ( p_QUOTA_CREDIT_PERCENT 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 )
483 AND ( p_ATTRIBUTE9 IS NULL )))
480 AND ( p_ATTRIBUTE8 IS NULL )))
481 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
482 OR ( ( Recinfo.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.MANAGER_REVIEW_FLAG = p_MANAGER_REVIEW_FLAG)
503 OR ( ( Recinfo.MANAGER_REVIEW_FLAG IS NULL )
504 AND ( p_MANAGER_REVIEW_FLAG IS NULL )))
505 AND ( ( Recinfo.MANAGER_REVIEW_DATE = p_MANAGER_REVIEW_DATE)
506 OR ( ( Recinfo.MANAGER_REVIEW_DATE IS NULL )
507 AND ( p_MANAGER_REVIEW_DATE IS NULL )))
508 AND ( ( Recinfo.ORIGINAL_SALES_CREDIT_ID = p_ORIGINAL_SALES_CREDIT_ID)
509 OR ( ( Recinfo.ORIGINAL_SALES_CREDIT_ID IS NULL )
510 AND ( p_ORIGINAL_SALES_CREDIT_ID IS NULL )))
511 --AND ( ( Recinfo.CREDIT_TYPE = p_CREDIT_TYPE)
512 -- OR ( ( Recinfo.CREDIT_TYPE IS NULL )
513 -- AND ( p_CREDIT_TYPE IS NULL )))
514 AND ( ( Recinfo.CREDIT_PERCENT = p_CREDIT_PERCENT)
515 OR ( ( Recinfo.CREDIT_PERCENT IS NULL )
516 AND ( p_CREDIT_PERCENT IS NULL )))
517 AND ( ( Recinfo.CREDIT_AMOUNT = p_CREDIT_AMOUNT)
518 OR ( ( Recinfo.CREDIT_AMOUNT IS NULL )
519 AND ( p_CREDIT_AMOUNT IS NULL )))
520 --AND ( ( Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
521 -- OR ( ( Recinfo.SECURITY_GROUP_ID IS NULL )
522 -- AND ( p_SECURITY_GROUP_ID IS NULL )))
523 AND ( ( Recinfo.CREDIT_TYPE_ID = p_CREDIT_TYPE_ID)
524 OR ( ( Recinfo.CREDIT_TYPE_ID IS NULL )
525 AND ( p_CREDIT_TYPE_ID IS NULL )))
526 ) then
527 return;
528 else
529 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
530 APP_EXCEPTION.RAISE_EXCEPTION;
531 End If;
532 END Lock_Row;
533
534 End AS_SALES_CREDITS_PKG;