1 PACKAGE BODY CN_WKSHT_BONUS_PKG AS
2 /* $Header: cntwbonb.pls 115.3 2001/10/29 17:17:23 pkm ship $ */
3 --
4 -- Package Name
5 -- CN_WKSHT_BONUS_PKG
6 -- Purpose
7 -- Table Handler for CN_WORKSHEET_BONUSES
8 -- FORM CNSBPS
9 -- BLOCK BONUSES
10 --
11 -- History
12 -- 26-May-99 Renu Chintalapati Created
13 /*-------------------------------------------------------------------------*
14 |
15 | PRIVATE VARIABLES
16 |
17 *-------------------------------------------------------------------------*/
18 g_program_type VARCHAR2(30) := NULL;
19 /*-------------------------------------------------------------------------*
20 |
21 | PRIVATE ROUTINES
22 |
23 *-------------------------------------------------------------------------*/
24
25 /*-------------------------------------------------------------------------*
26 -- Procedure Name
27 -- Insert_row
28 -- Purpose
29 -- Main insert procedure
30 *-------------------------------------------------------------------------*/
31 PROCEDURE insert_row
32 (x_payrun_id IN NUMBER
33 ,x_salesrep_id IN NUMBER
34 ,x_quota_id IN NUMBER
35 ,x_comp_plan_id IN NUMBER
36 ,x_amount IN NUMBER
37 ,x_srp_plan_assign_id IN NUMBER
38 ,x_payment_worksheet_id IN NUMBER
39 ,x_created_by IN NUMBER
40 ,x_creation_date IN DATE
41 ) IS
42
43 BEGIN
44
45 INSERT INTO cn_worksheet_bonuses
46 (payrun_id
47 ,salesrep_id
48 ,quota_id
49 ,comp_plan_id
50 ,amount
51 ,srp_plan_assign_id
52 ,payment_worksheet_id)
53 VALUES
54 (x_payrun_id
55 ,x_salesrep_id
56 ,x_quota_id
57 ,x_comp_plan_id
58 ,x_amount
59 ,x_srp_plan_assign_id
60 ,x_payment_worksheet_id);
61
62 END Insert_row;
63
64 /*-------------------------------------------------------------------------*
65 -- Procedure Name
66 -- Lock_row
67 -- Purpose
68 -- Lock db row after form record is changed
69 -- Notes
70 -- Only called from the form
71 *-------------------------------------------------------------------------*/
72 PROCEDURE lock_row
73 ( x_payment_worksheet_id NUMBER
74 ,x_quota_id NUMBER
75 ,x_comp_plan_id NUMBER ) IS
76
77 CURSOR C IS
78 SELECT *
79 FROM cn_worksheet_bonuses
80 WHERE payment_worksheet_id = x_payment_worksheet_id
81 AND quota_id = x_quota_id
82 AND comp_plan_id = x_comp_plan_id
83 FOR UPDATE NOWAIT;
84 l_record C%ROWTYPE;
85
86 BEGIN
87 OPEN C;
88 FETCH C INTO l_record;
89
90 IF (C%NOTFOUND) then
91 CLOSE C;
92 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
93 app_exception.raise_exception;
94 END IF;
95 CLOSE C;
96
97 END Lock_row;
98
99 /*-------------------------------------------------------------------------
100 -- Procedure Name
101 -- Update Record
102 -- Purpose
103 -- To Update the bonus worksheet
104 --
105 -------------------------------------------------------------------------*/
106 PROCEDURE update_row (
107 x_payment_worksheet_id NUMBER
108 ,x_quota_id NUMBER
109 ,x_comp_plan_id NUMBER
110 ,x_amount NUMBER := fnd_api.g_miss_num
111 ,x_last_updated_by NUMBER
112 ,x_last_update_login NUMBER
113 ,x_last_update_date DATE
114 ) IS
115 l_amount NUMBER;
116
117 CURSOR C IS
118 SELECT *
119 FROM cn_worksheet_bonuses
120 WHERE payment_worksheet_id = x_payment_worksheet_id
121 AND quota_id = x_quota_id
122 AND comp_plan_id = x_comp_plan_id
123 FOR UPDATE;
124 oldrow C%ROWTYPE;
125
126 BEGIN
127 OPEN C;
128 FETCH C INTO oldrow;
129
130 IF (C%NOTFOUND) then
131 CLOSE C;
132 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
133 app_exception.raise_exception;
134 END IF;
135 CLOSE C;
136
137 SELECT
138 decode(x_amount,
139 fnd_api.g_miss_num, oldrow.amount,
140 Nvl(x_amount, 0))
141 INTO
142 l_amount
143 FROM dual;
144
145 UPDATE cn_worksheet_bonuses
146 SET amount = l_amount,
147 last_updated_by = x_last_updated_by,
148 last_update_date = x_last_update_date,
149 last_update_login = x_last_update_login
150 WHERE payment_worksheet_id = x_payment_worksheet_id
151 AND quota_id = x_quota_id
152 AND comp_plan_id = x_comp_plan_id;
153
154
155
156 IF (SQL%NOTFOUND) THEN
157 RAISE NO_DATA_FOUND;
158 END IF;
159
160 END Update_row;
161
162 /*-------------------------------------------------------------------------*
163 -- Procedure Name
164 -- Delete_row
165 -- Purpose
166 -- Delete the bonus worksheet
167 *-------------------------------------------------------------------------*/
168 PROCEDURE Delete_row( x_payment_worksheet_id NUMBER ) IS
169 BEGIN
170
171 DELETE FROM cn_worksheet_bonuses
172 WHERE payment_worksheet_id = x_payment_worksheet_id;
173 /* IF (SQL%NOTFOUND) THEN
174 RAISE NO_DATA_FOUND;
175 END IF;*/
176
177 END Delete_row;
178
179 END CN_WKSHT_BONUS_PKG;
[Home] [Help]