[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PMT_PLANS_PKG
Source
1 PACKAGE BODY CN_SRP_PMT_PLANS_PKG AS
2 /* $Header: cntsppb.pls 120.1 2005/06/16 15:06:08 appldev $ */
3 --
4 -- Package Name
5 -- CN_SRP_PMT_PLANS_PKG
6 -- Purpose
7 -- Table Handler for CN_SRP_PMT_PLANS
8 -- FORM CNSRMT
9 -- BLOCK SRP_PMT_PLAN
10 --
11 -- History
12 -- 26-May-99 Angela Chung Created
13 -- 01-AUG-01 Kumar Sivankaran Added Object_version Number
14 /*-------------------------------------------------------------------------*
15 |
16 | PRIVATE ROUTINES
17 |
18 *-------------------------------------------------------------------------*/
19
20 /*-----------------------------------------------------------------------*
21 | Custom Validation
22 *-----------------------------------------------------------------------*/
23
24 /*-------------------------------------------------------------------------*
25 -- Procedure Name
26 -- Get_UID
27 -- Purpose
28 -- Get the Sequence Number to Create a new Srp Payment Plan.
29 *-------------------------------------------------------------------------*/
30 PROCEDURE Get_UID( X_srp_pmt_plan_id IN OUT NOCOPY NUMBER) IS
31
32 BEGIN
33
34 SELECT cn_srp_pmt_plans_s.nextval
35 INTO X_srp_pmt_plan_id
36 FROM dual;
37
38 END Get_UID;
39
40 /*-------------------------------------------------------------------------*
41 -- Procedure Name
42 -- Insert_row
43 -- Purpose
44 -- Main insert procedure
45 *-------------------------------------------------------------------------*/
46 PROCEDURE insert_row
47 (x_srp_pmt_plan_id IN OUT NOCOPY NUMBER
48 ,x_pmt_plan_id IN NUMBER
49 ,x_salesrep_id IN NUMBER
50 ,x_org_id IN NUMBER
51 ,x_role_id IN NUMBER
52 ,x_credit_type_id IN NUMBER
53 ,x_start_date DATE
54 ,x_end_date DATE
55 ,x_minimum_amount IN NUMBER
56 ,x_maximum_amount IN NUMBER
57 ,x_max_recovery_amount IN NUMBER
58 ,x_attribute_category VARCHAR2
59 ,x_attribute1 VARCHAR2
60 ,x_attribute2 VARCHAR2
61 ,x_attribute3 VARCHAR2
62 ,x_attribute4 VARCHAR2
63 ,x_attribute5 VARCHAR2
64 ,x_attribute6 VARCHAR2
65 ,x_attribute7 VARCHAR2
66 ,x_attribute8 VARCHAR2
67 ,x_attribute9 VARCHAR2
68 ,x_attribute10 VARCHAR2
69 ,x_attribute11 VARCHAR2
70 ,x_attribute12 VARCHAR2
71 ,x_attribute13 VARCHAR2
72 ,x_attribute14 VARCHAR2
73 ,x_attribute15 VARCHAR2
74 ,x_Created_By NUMBER
75 ,x_Creation_Date DATE
76 ,x_Last_Updated_By NUMBER
77 ,x_Last_Update_Date DATE
78 ,x_Last_Update_Login NUMBER
79 ,x_srp_role_id NUMBER
80 ,x_role_pmt_plan_id NUMBER
81 ,x_lock_flag VARCHAR2)
82 IS
83 l_dummy NUMBER;
84
85 BEGIN
86
87 Get_UID( x_srp_pmt_plan_id );
88
89 INSERT INTO cn_srp_pmt_plans
90 (srp_pmt_plan_id
91 ,pmt_plan_id
92 ,salesrep_id
93 ,org_id
94 ,role_id
95 ,credit_type_id
96 ,start_date
97 ,end_date
98 ,minimum_amount
99 ,maximum_amount
100 ,max_recovery_amount
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 ,Created_By
118 ,Creation_Date
119 ,Last_Updated_By
120 ,Last_Update_Date
121 ,Last_Update_Login
122 ,object_version_number
123 ,srp_role_id
124 ,role_pmt_plan_id
125 ,lock_flag)
126 VALUES
127 (x_srp_pmt_plan_id
128 ,x_pmt_plan_id
129 ,x_salesrep_id
130 ,x_org_id
131 ,x_role_id
132 ,x_credit_type_id
133 ,x_start_date
134 ,x_end_date
135 ,x_minimum_amount
136 ,x_maximum_amount
137 ,x_max_recovery_amount
138 ,x_attribute_category
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_Created_By
155 ,x_Creation_Date
156 ,x_Last_Updated_By
157 ,x_Last_Update_Date
158 ,x_Last_Update_Login
159 ,1
160 ,x_srp_role_id
161 ,x_role_pmt_plan_id
162 ,NVL(x_lock_flag, 'N')
163 );
164
165 select 1 INTO l_dummy from CN_SRP_PMT_PLANS
166 where SRP_PMT_PLAN_ID = x_srp_pmt_plan_id;
167
168 END Insert_row;
169
170 /*-------------------------------------------------------------------------*
171 -- Procedure Name
172 -- Lock_row
173 -- Purpose
174 -- Lock db row after form record is changed
175 -- Notes
176 -- Only called from the form
177 *-------------------------------------------------------------------------*/
178 PROCEDURE lock_row
179 ( x_srp_pmt_plan_id NUMBER
180 ,x_pmt_plan_id NUMBER
181 ,x_salesrep_id NUMBER
182 ,x_org_id NUMBER
183 ,x_role_id NUMBER
184 ,x_credit_type_id NUMBER
185 ,x_start_date DATE
186 ,x_end_date DATE
187 ,x_minimum_amount NUMBER
188 ,x_maximum_amount NUMBER
189 ,x_max_recovery_amount NUMBER
190 ,x_attribute_category VARCHAR2 := NULL
191 ,x_attribute1 VARCHAR2 := NULL
192 ,x_attribute2 VARCHAR2 := NULL
193 ,x_attribute3 VARCHAR2 := NULL
194 ,x_attribute4 VARCHAR2 := NULL
195 ,x_attribute5 VARCHAR2 := NULL
196 ,x_attribute6 VARCHAR2 := NULL
197 ,x_attribute7 VARCHAR2 := NULL
198 ,x_attribute8 VARCHAR2 := NULL
199 ,x_attribute9 VARCHAR2 := NULL
200 ,x_attribute10 VARCHAR2 := NULL
201 ,x_attribute11 VARCHAR2 := NULL
202 ,x_attribute12 VARCHAR2 := NULL
203 ,x_attribute13 VARCHAR2 := NULL
204 ,x_attribute14 VARCHAR2 := NULL
205 ,x_attribute15 VARCHAR2 := NULL
206 ) IS
207
208 CURSOR C IS
209 SELECT *
210 FROM cn_srp_pmt_plans
211 WHERE srp_pmt_plan_id = x_srp_pmt_plan_id
212 FOR UPDATE of srp_pmt_plan_id NOWAIT;
213 tlinfo C%ROWTYPE;
214
215 BEGIN
216 OPEN C;
217 FETCH C INTO tlinfo;
218
219 IF (C%NOTFOUND) then
220 CLOSE C;
221 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
222 app_exception.raise_exception;
223 END IF;
224 CLOSE C;
225
226 IF ( ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
227 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
228 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
229 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
230 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
231 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
232 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
233 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
234 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
235 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
236 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
237 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
238 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
239 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
240 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
241 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
242 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
243 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
244 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
245 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
246 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
247 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
248 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
249 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
250 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
251 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
252 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
253 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
254 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
255 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
256 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
257 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND
258 (X_ATTRIBUTE_CATEGORY is null)))
259 AND (tlinfo.START_DATE = X_START_DATE)
260 AND ((tlinfo.END_DATE = X_END_DATE)
261 OR ((tlinfo.END_DATE is null) AND (X_END_DATE is null)))
262 AND ((tlinfo.MINIMUM_AMOUNT = X_MINIMUM_AMOUNT)
263 OR ((tlinfo.MINIMUM_AMOUNT is null) AND
264 (X_MINIMUM_AMOUNT is null)))
265 AND ((tlinfo.MAXIMUM_AMOUNT = X_MAXIMUM_AMOUNT)
266 OR ((tlinfo.MAXIMUM_AMOUNT is null) AND
267 (X_MAXIMUM_AMOUNT is null)))
268 AND ((tlinfo.MAX_RECOVERY_AMOUNT = X_MAX_RECOVERY_AMOUNT )
269 OR ((tlinfo.MAX_RECOVERY_AMOUNT is null) AND
270 (X_MAX_RECOVERY_AMOUNT is null)))
271 AND (tlinfo.PMT_PLAN_ID = X_PMT_PLAN_ID)
272 AND (tlinfo.SALESREP_ID = X_SALESREP_ID)
273 AND (tlinfo.ORG_ID = X_ORG_ID)
274 AND (tlinfo.ROLE_ID = X_ROLE_ID)
275 AND (tlinfo.CREDIT_TYPE_ID = X_CREDIT_TYPE_ID)
276 )
277 THEN
278 RETURN;
279 ELSE
280 fnd_message.Set_Name('FND', 'FORM_RECORD_CHANGED');
281 app_exception.raise_exception;
282 END IF;
283
284 END Lock_row;
285
286 /*-------------------------------------------------------------------------*
287 -- Procedure Name
288 -- Update Record
289 -- Purpose
290 -- To Update the Srp Payment Plan Assign
291 --
292 *-------------------------------------------------------------------------*/
293 PROCEDURE update_row
294 (x_srp_pmt_plan_id NUMBER
295 ,x_pmt_plan_id NUMBER
296 ,x_salesrep_id NUMBER
297 ,x_org_id NUMBER
298 ,x_role_id NUMBER
299 ,x_credit_type_id NUMBER
300 ,x_start_date DATE
301 ,x_end_date DATE
302 ,x_minimum_amount NUMBER
303 ,x_maximum_amount NUMBER
304 ,x_max_recovery_amount NUMBER
305 ,x_attribute_category VARCHAR2
306 ,x_attribute1 VARCHAR2
307 ,x_attribute2 VARCHAR2
308 ,x_attribute3 VARCHAR2
309 ,x_attribute4 VARCHAR2
310 ,x_attribute5 VARCHAR2
311 ,x_attribute6 VARCHAR2
312 ,x_attribute7 VARCHAR2
313 ,x_attribute8 VARCHAR2
314 ,x_attribute9 VARCHAR2
315 ,x_attribute10 VARCHAR2
316 ,x_attribute11 VARCHAR2
317 ,x_attribute12 VARCHAR2
318 ,x_attribute13 VARCHAR2
319 ,x_attribute14 VARCHAR2
320 ,x_attribute15 VARCHAR2
321 ,x_Last_Updated_By NUMBER
322 ,x_Last_Update_Date DATE
323 ,x_Last_Update_Login NUMBER
324 ,x_object_version_number NUMBER
325 ,x_lock_flag VARCHAR2 ) IS
326
327 l_pmt_plan_id cn_srp_pmt_plans.pmt_plan_id%TYPE;
328 l_salesrep_id cn_srp_pmt_plans.salesrep_id%TYPE;
329 l_org_id cn_srp_pmt_plans.org_id%TYPE;
330 l_role_id cn_srp_pmt_plans.role_id%TYPE;
331 l_credit_type_id cn_srp_pmt_plans.credit_type_id%TYPE;
332 l_start_date cn_srp_pmt_plans.start_date%TYPE;
333 l_end_date cn_srp_pmt_plans.end_date%TYPE;
334 l_minimum_amount cn_srp_pmt_plans.minimum_amount%TYPE;
335 l_maximum_amount cn_srp_pmt_plans.maximum_amount%TYPE;
336 l_max_recovery_amount cn_srp_pmt_plans.max_recovery_amount%TYPE;
337 l_attribute_category cn_srp_pmt_plans.attribute_category%TYPE;
338 l_attribute1 cn_srp_pmt_plans.attribute1%TYPE;
339 l_attribute2 cn_srp_pmt_plans.attribute2%TYPE;
340 l_attribute3 cn_srp_pmt_plans.attribute3%TYPE;
341 l_attribute4 cn_srp_pmt_plans.attribute4%TYPE;
342 l_attribute5 cn_srp_pmt_plans.attribute5%TYPE;
343 l_attribute6 cn_srp_pmt_plans.attribute6%TYPE;
347 l_attribute10 cn_srp_pmt_plans.attribute10%TYPE;
344 l_attribute7 cn_srp_pmt_plans.attribute7%TYPE;
345 l_attribute8 cn_srp_pmt_plans.attribute8%TYPE;
346 l_attribute9 cn_srp_pmt_plans.attribute9%TYPE;
348 l_attribute11 cn_srp_pmt_plans.attribute11%TYPE;
349 l_attribute12 cn_srp_pmt_plans.attribute12%TYPE;
350 l_attribute13 cn_srp_pmt_plans.attribute13%TYPE;
351 l_attribute14 cn_srp_pmt_plans.attribute14%TYPE;
352 l_attribute15 cn_srp_pmt_plans.attribute15%TYPE;
353 l_lock_flag cn_srp_pmt_plans.lock_flag%TYPE;
354
355 CURSOR C IS
356 SELECT *
357 FROM cn_srp_pmt_plans
358 WHERE srp_pmt_plan_id = x_srp_pmt_plan_id
359 FOR UPDATE of srp_pmt_plan_id NOWAIT;
360 oldrow C%ROWTYPE;
361
362 BEGIN
363 OPEN C;
364 FETCH C INTO oldrow;
365
366 IF (C%NOTFOUND) then
367 CLOSE C;
368 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
369 app_exception.raise_exception;
370 END IF;
371 CLOSE C;
372
373 -- Update only allowed for start date/end date change
374 -- IF oldrow.salesrep_id <> x_salesrep_id OR
375 -- oldrow.pmt_plan_id <> x_pmt_plan_id OR
376 -- oldrow.credit_type_id <> x_credit_type_id THEN
377 -- FND_MESSAGE.Set_Name('CN', 'CN_SRP_PMT_PLAN_UPD_NA');
378 -- app_exception.raise_exception;
379 -- END IF;
380
381 SELECT
382 decode(x_pmt_plan_id,
383 fnd_api.g_miss_num, oldrow.pmt_plan_id,
384 x_pmt_plan_id),
385 decode(x_salesrep_id,
386 fnd_api.g_miss_num, oldrow.salesrep_id,
387 x_salesrep_id),
388 decode(x_org_id,
389 fnd_api.g_miss_num, oldrow.org_id,
390 x_org_id),
391 decode(x_role_id,
392 fnd_api.g_miss_num, oldrow.role_id,
393 x_role_id),
394 decode(x_credit_type_id,
395 fnd_api.g_miss_num, oldrow.credit_type_id ,
396 x_credit_type_id),
397 decode(x_start_date,
398 fnd_api.g_miss_date, oldrow.start_date,
399 x_start_date),
400 decode(x_end_date,
401 fnd_api.g_miss_date, oldrow.end_date,
402 x_end_date),
403 decode(x_minimum_amount,
404 fnd_api.g_miss_num, oldrow.minimum_amount,
405 x_minimum_amount),
406 decode(x_maximum_amount,
407 fnd_api.g_miss_num, oldrow.maximum_amount,
408 x_maximum_amount),
409 decode(x_max_recovery_amount,
410 fnd_api.g_miss_num, oldrow.max_recovery_amount,
411 x_max_recovery_amount),
412 decode(x_attribute_category,
413 fnd_api.g_miss_char, oldrow.attribute_category,
414 x_attribute_category),
415 decode(x_attribute1,
416 fnd_api.g_miss_char, oldrow.attribute1,
417 x_attribute1),
418 decode(x_attribute2,
419 fnd_api.g_miss_char, oldrow.attribute2,
420 x_attribute2),
421 decode(x_attribute3,
422 fnd_api.g_miss_char, oldrow.attribute3,
423 x_attribute3),
424 decode(x_attribute4,
425 fnd_api.g_miss_char, oldrow.attribute4,
426 x_attribute4),
427 decode(x_attribute5,
428 fnd_api.g_miss_char, oldrow.attribute5,
429 x_attribute5),
430 decode(x_attribute6,
431 fnd_api.g_miss_char, oldrow.attribute6,
432 x_attribute6),
433 decode(x_attribute7,
434 fnd_api.g_miss_char, oldrow.attribute7,
435 x_attribute7),
436 decode(x_attribute8,
437 fnd_api.g_miss_char, oldrow.attribute8,
438 x_attribute8),
439 decode(x_attribute9,
440 fnd_api.g_miss_char, oldrow.attribute9,
441 x_attribute9),
442 decode(x_attribute10,
443 fnd_api.g_miss_char, oldrow.attribute10,
444 x_attribute10),
445 decode(x_attribute11,
446 fnd_api.g_miss_char, oldrow.attribute11,
447 x_attribute11),
448 decode(x_attribute12,
449 fnd_api.g_miss_char, oldrow.attribute12,
450 x_attribute12),
451 decode(x_attribute13,
452 fnd_api.g_miss_char, oldrow.attribute13,
453 x_attribute13),
454 decode(x_attribute14,
455 fnd_api.g_miss_char, oldrow.attribute14,
456 x_attribute14),
457 decode(x_attribute15,
458 fnd_api.g_miss_char, oldrow.attribute15,
459 x_attribute15),
460 decode(x_lock_flag,
461 fnd_api.g_miss_char, oldrow.lock_flag,
462 x_lock_flag)
463 INTO
464 l_pmt_plan_id,
465 l_salesrep_id,
466 l_org_id,
467 l_role_id,
468 l_credit_type_id,
469 l_start_date,
470 l_end_date,
471 l_minimum_amount,
472 l_maximum_amount,
473 l_max_recovery_amount,
474 l_attribute_category,
475 l_attribute1,
476 l_attribute2,
477 l_attribute3,
478 l_attribute4,
479 l_attribute5,
480 l_attribute6,
481 l_attribute7,
482 l_attribute8,
483 l_attribute9,
484 l_attribute10,
485 l_attribute11,
486 l_attribute12,
487 l_attribute13,
488 l_attribute14,
489 l_attribute15,
490 l_lock_flag
491 FROM dual;
492
493 UPDATE cn_srp_pmt_plans
494 SET
495 pmt_plan_id = l_pmt_plan_id,
496 salesrep_id = l_salesrep_id,
497 org_id = l_org_id,
498 role_id = l_role_id,
499 credit_type_id = l_credit_type_id,
500 start_date = l_start_date,
501 end_date = l_end_date,
502 minimum_amount = l_minimum_amount,
506 attribute1 = l_attribute1,
503 maximum_amount = l_maximum_amount,
504 max_recovery_amount = l_max_recovery_amount,
505 attribute_category = l_attribute_category,
507 attribute2 = l_attribute2,
508 attribute3 = l_attribute3,
509 attribute4 = l_attribute4,
510 attribute5 = l_attribute5,
511 attribute6 = l_attribute6,
512 attribute7 = l_attribute7,
513 attribute8 = l_attribute8,
514 attribute9 = l_attribute9,
515 attribute10 = l_attribute10,
516 attribute11 = l_attribute11,
517 attribute12 = l_attribute12,
518 attribute13 = l_attribute13,
519 attribute14 = l_attribute14,
520 attribute15 = l_attribute15,
521 last_update_date = x_Last_Update_Date,
522 last_updated_by = x_Last_Updated_By,
523 last_update_login = x_Last_Update_Login,
524 object_version_number = nvl(X_OBJECT_VERSION_NUMBER,0) + 1,
525 lock_flag = l_lock_flag
526
527 WHERE srp_pmt_plan_id = x_srp_pmt_plan_id ;
528
529 IF (SQL%NOTFOUND) THEN
530 RAISE NO_DATA_FOUND;
531 END IF;
532
533 END Update_row;
534
535 /*-------------------------------------------------------------------------*
536 -- Procedure Name
537 -- Delete_row
538 -- Purpose
539 -- Delete the Srp Payment Plan Assign
540 *-------------------------------------------------------------------------*/
541 PROCEDURE Delete_row( x_srp_pmt_plan_id NUMBER ) IS
542 BEGIN
543
544 DELETE FROM cn_srp_pmt_plans
545 WHERE srp_pmt_plan_id = x_srp_pmt_plan_id;
546 IF (SQL%NOTFOUND) THEN
547 RAISE NO_DATA_FOUND;
548 END IF;
549
550 END Delete_row;
551
552 END CN_SRP_PMT_PLANS_PKG;