eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CN_ROLE_MODELS_PKG

Source


1 PACKAGE BODY cn_role_models_pkg AS
2   /*$Header: cntrlmlb.pls 115.3 2002/01/28 20:05:31 pkm ship      $*/
3 
4 PROCEDURE insert_row
5   (P_ROLE_MODEL_ID	         IN   NUMBER   := FND_API.G_MISS_NUM,
6    P_ROLE_ID                     IN   NUMBER,
7    P_NAME                        IN   VARCHAR2,
8    P_DESCRIPTION                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
9    P_SEQ 	                 IN   NUMBER   := FND_API.G_MISS_NUM,
10    P_STATUS 	                 IN   VARCHAR2,
11    P_ACTIVATE_STATUS             IN   VARCHAR2,
12    P_CLUB_ELIGIBLE_FLAG	         IN   VARCHAR2 := FND_API.G_MISS_CHAR,
13    P_OTE		         IN   NUMBER   := FND_API.G_MISS_NUM,
14    P_ROUNDING_FACTOR             IN   NUMBER   := FND_API.G_MISS_NUM,
15    P_ATTAIN_SCHEDULE_ID	         IN   NUMBER   := FND_API.G_MISS_NUM,
16    P_PLAN_LEVEL		         IN   NUMBER   := FND_API.G_MISS_NUM,
17    P_QUOTA_MIN		         IN   NUMBER   := FND_API.G_MISS_NUM,
18    P_QUOTA_MAX		         IN   NUMBER   := FND_API.G_MISS_NUM,
19    P_ESTIMATED_PAYOUT	         IN   NUMBER   := FND_API.G_MISS_NUM,
20    P_SRP_ROLE_ID                 IN   NUMBER   := FND_API.G_MISS_NUM,
21    P_START_DATE		         IN   DATE,
22    P_END_DATE		         IN   DATE     := FND_API.G_MISS_DATE,
23    P_SCENARIO_STATUS	         IN   VARCHAR2 := FND_API.G_MISS_CHAR,
24    P_SRP_ID                      IN   NUMBER   := FND_API.G_MISS_NUM,
25    P_ATTRIBUTE_CATEGORY          IN   VARCHAR2 := FND_API.G_MISS_CHAR,
26    P_ATTRIBUTE1                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
27    P_ATTRIBUTE2                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
28    P_ATTRIBUTE3                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
29    P_ATTRIBUTE4                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
30    P_ATTRIBUTE5                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
31    P_ATTRIBUTE6                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
32    P_ATTRIBUTE7                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
33    P_ATTRIBUTE8                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
34    P_ATTRIBUTE9                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
35    P_ATTRIBUTE10                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
36    P_ATTRIBUTE11                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
37    P_ATTRIBUTE12                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
38    P_ATTRIBUTE13                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
39    P_ATTRIBUTE14                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
40    P_ATTRIBUTE15                 IN   VARCHAR2 := FND_API.G_MISS_CHAR)
41 IS
42    MN NUMBER        := FND_API.G_MISS_NUM;
43    MC VARCHAR2(150) := FND_API.G_MISS_CHAR;
44    MD DATE          := FND_API.G_MISS_DATE;
45 
46 BEGIN
47 
48    INSERT INTO cn_role_models(
49      role_model_id,
50      role_id,
51      name,
52      description,
53      seq,
54      status,
55      activate_status,
56      club_eligible_flag,
57      ote,
58      rounding_factor,
59      attain_schedule_id,
60      plan_level,
61      quota_min,
62      quota_max,
63      estimated_payout,
64      srp_role_id,
65      start_date,
66      end_date,
67      scenario_status,
68      srp_id,
69      attribute_category,
70      attribute1,
71      attribute2,
72      attribute3,
73      attribute4,
74      attribute5,
75      attribute6,
76      attribute7,
77      attribute8,
78      attribute9,
79      attribute10,
80      attribute11,
81      attribute12,
82      attribute13,
83      attribute14,
84      attribute15,
85      object_version_number,
86      LAST_UPDATE_DATE,
87      LAST_UPDATED_BY,
88      LAST_UPDATE_LOGIN,
89      CREATION_DATE,
90      CREATED_BY
91    )
92    SELECT
93      decode(p_role_model_id, mn, cn_role_models_s.nextval,
94             p_role_model_id),
95      p_role_id,
96      p_name,
97      decode(p_description, mc, null, p_description),
98      decode(p_seq, mn, null, p_seq),
99      decode(p_status, mc, null, p_status),
100      decode(p_activate_status, mc, null, p_activate_status),
101      decode(p_club_eligible_flag, mc, 'N', p_club_eligible_flag),
102      decode(p_ote, mn, null, p_ote),
103      decode(p_rounding_factor, mn, null, p_rounding_factor),
104      decode(p_attain_schedule_id, mn, null, p_attain_schedule_id),
105      decode(p_plan_level, mn, null, p_plan_level),
106      decode(p_quota_min, mn, null, p_quota_min),
107      decode(p_quota_max, mn, null, p_quota_max),
108      decode(p_estimated_payout, mn, null, p_estimated_payout),
109      decode(p_srp_role_id, mn, null, p_srp_role_id),
110      trunc(p_start_date),
111      decode(p_end_date, md, null, trunc(p_end_date)),
112      decode(p_scenario_status, mc, null, p_scenario_status),
113      decode(p_srp_id, mn, null, p_srp_id),
114      decode(p_attribute_category, mc, null, p_attribute_category),
115      decode(p_attribute1, mc, null, p_attribute1),
116      decode(p_attribute2, mc, null, p_attribute2),
117      decode(p_attribute3, mc, null, p_attribute3),
118      decode(p_attribute4, mc, null, p_attribute4),
119      decode(p_attribute5, mc, null, p_attribute5),
120      decode(p_attribute6, mc, null, p_attribute6),
121      decode(p_attribute7, mc, null, p_attribute7),
122      decode(p_attribute8, mc, null, p_attribute8),
123      decode(p_attribute9, mc, null, p_attribute9),
124      decode(p_attribute10, mc, null, p_attribute10),
125      decode(p_attribute11, mc, null, p_attribute11),
126      decode(p_attribute12, mc, null, p_attribute12),
127      decode(p_attribute13, mc, null, p_attribute13),
128      decode(p_attribute14, mc, null, p_attribute14),
129      decode(p_attribute15, mc, null, p_attribute15),
130      1,
131      sysdate,
132      fnd_global.user_id,
133      fnd_global.login_id,
134      sysdate,
135      fnd_global.user_id
136     FROM dual
137         ;
138 
139 END Insert_Row;
140 
141 
142 PROCEDURE Update_Row
143   (P_ROLE_MODEL_ID	         IN   NUMBER,
144    P_ROLE_ID                     IN   NUMBER   := FND_API.G_MISS_NUM,
145    P_NAME                        IN   VARCHAR2 := FND_API.G_MISS_CHAR,
146    P_DESCRIPTION                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
147    P_SEQ 	                 IN   NUMBER   := FND_API.G_MISS_NUM,
148    P_STATUS 	                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
149    P_ACTIVATE_STATUS             IN   VARCHAR2 := FND_API.G_MISS_CHAR,
150    P_CLUB_ELIGIBLE_FLAG	         IN   VARCHAR2 := FND_API.G_MISS_CHAR,
151    P_OTE		         IN   NUMBER   := FND_API.G_MISS_NUM,
152    P_ROUNDING_FACTOR             IN   NUMBER   := FND_API.G_MISS_NUM,
153    P_ATTAIN_SCHEDULE_ID	         IN   NUMBER   := FND_API.G_MISS_NUM,
154    P_PLAN_LEVEL		         IN   NUMBER   := FND_API.G_MISS_NUM,
155    P_QUOTA_MIN		         IN   NUMBER   := FND_API.G_MISS_NUM,
156    P_QUOTA_MAX		         IN   NUMBER   := FND_API.G_MISS_NUM,
157    P_ESTIMATED_PAYOUT	         IN   NUMBER   := FND_API.G_MISS_NUM,
158    P_SRP_ROLE_ID	         IN   NUMBER   := FND_API.G_MISS_NUM,
159    P_START_DATE		         IN   DATE     := FND_API.G_MISS_DATE,
160    P_END_DATE		         IN   DATE     := FND_API.G_MISS_DATE,
161    P_SCENARIO_STATUS	         IN   VARCHAR2 := FND_API.G_MISS_CHAR,
162    P_SRP_ID                      IN   NUMBER   := FND_API.G_MISS_NUM,
163    P_ATTRIBUTE_CATEGORY          IN   VARCHAR2 := FND_API.G_MISS_CHAR,
164    P_ATTRIBUTE1                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
165    P_ATTRIBUTE2                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
166    P_ATTRIBUTE3                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
167    P_ATTRIBUTE4                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
168    P_ATTRIBUTE5                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
169    P_ATTRIBUTE6                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
170    P_ATTRIBUTE7                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
171    P_ATTRIBUTE8                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
172    P_ATTRIBUTE9                  IN   VARCHAR2 := FND_API.G_MISS_CHAR,
173    P_ATTRIBUTE10                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
174    P_ATTRIBUTE11                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
175    P_ATTRIBUTE12                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
176    P_ATTRIBUTE13                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
177    P_ATTRIBUTE14                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
178    P_ATTRIBUTE15                 IN   VARCHAR2 := FND_API.G_MISS_CHAR,
179    P_OBJECT_VERSION_NUMBER       IN   NUMBER   := FND_API.G_MISS_NUM )
180 IS
181 
182    CURSOR l_old_csr IS
183       SELECT *
184 	FROM cn_role_models
185        WHERE role_model_id = p_role_model_id;
186 
187    l_old_rec   l_old_csr%ROWTYPE;
188 
189    MN NUMBER        := FND_API.G_MISS_NUM;
190    MC VARCHAR2(150) := FND_API.G_MISS_CHAR;
191    MD DATE          := FND_API.G_MISS_DATE;
192 
193    l_object_version_number  NUMBER;
194 
195 BEGIN
196    OPEN l_old_csr;
197    FETCH l_old_csr INTO l_old_rec;
198    CLOSE l_old_csr;
199 
200    SELECT decode(p_object_version_number, mn,
201                  l_old_rec.object_version_number, p_object_version_number)
202      INTO l_object_version_number
203      FROM dual
204         ;
205 
206    -- check object version number
207    IF l_object_version_number <> l_old_rec.object_version_number THEN
208      fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
209      fnd_msg_pub.add;
210      raise fnd_api.g_exc_error;
211    END IF;
212 
213    UPDATE cn_role_models SET
214     (role_id,
215      name,
216      description,
217      seq,
218      status,
219      activate_status,
220      club_eligible_flag,
221      ote,
222      rounding_factor,
223      attain_schedule_id,
224      plan_level,
225      quota_min,
226      quota_max,
227      estimated_payout,
228      srp_role_id,
229      start_date,
230      end_date,
231      scenario_status,
232      srp_id,
233      attribute_category,
234      attribute1,
235      attribute2,
236      attribute3,
237      attribute4,
238      attribute5,
239      attribute6,
240      attribute7,
241      attribute8,
242      attribute9,
243      attribute10,
244      attribute11,
245      attribute12,
246      attribute13,
247      attribute14,
248      attribute15,
249      object_version_number,
250      LAST_UPDATE_DATE,
251      LAST_UPDATED_BY,
252      LAST_UPDATE_LOGIN
253    ) =
254      (
255       SELECT
256         decode(p_role_id, mn, l_old_rec.role_id, p_role_id),
257         decode(p_name, mc, l_old_rec.name, p_name),
258         decode(p_description, mc, l_old_rec.description, p_description),
259         decode(p_seq, mn, l_old_rec.seq, p_seq),
260         decode(p_status, mc, l_old_rec.status, p_status),
261         decode(p_activate_status, mc, l_old_rec.activate_status,
262                p_activate_status),
263         decode(p_club_eligible_flag, mc, l_old_rec.club_eligible_flag,
264                p_club_eligible_flag),
265         decode(p_ote, mn, l_old_rec.ote, p_ote),
266         decode(p_rounding_factor, mn, l_old_rec.rounding_factor,
267                p_rounding_factor),
268         decode(p_attain_schedule_id, mn, l_old_rec.attain_schedule_id,
269                p_attain_schedule_id),
270         decode(p_plan_level, mn, l_old_rec.plan_level, p_plan_level),
271         decode(p_quota_min, mn, l_old_rec.quota_min, p_quota_min),
272         decode(p_quota_max, mn, l_old_rec.quota_max, p_quota_max),
273         decode(p_estimated_payout, mn, l_old_rec.estimated_payout,
274                p_estimated_payout),
275         decode(p_srp_role_id, mn, l_old_rec.srp_role_id, p_srp_role_id),
276         decode(p_start_date, md, trunc(l_old_rec.start_date),
277                trunc(p_start_date)),
278         decode(p_end_date, md, trunc(l_old_rec.end_date), trunc(p_end_date)),
279         decode(p_scenario_status, mc, l_old_rec.scenario_status,
280                p_scenario_status),
281         decode(p_srp_id, mn, l_old_rec.srp_id, p_srp_id),
282         decode(p_attribute_category, mc, l_old_rec.attribute_category,
283                p_attribute_category),
287         decode(p_attribute4, mc, l_old_rec.attribute4, p_attribute4),
284         decode(p_attribute1, mc, l_old_rec.attribute1, p_attribute1),
285         decode(p_attribute2, mc, l_old_rec.attribute2, p_attribute2),
286         decode(p_attribute3, mc, l_old_rec.attribute3, p_attribute3),
288         decode(p_attribute5, mc, l_old_rec.attribute5, p_attribute5),
289         decode(p_attribute6, mc, l_old_rec.attribute6, p_attribute6),
290         decode(p_attribute7, mc, l_old_rec.attribute7, p_attribute7),
291         decode(p_attribute8, mc, l_old_rec.attribute8, p_attribute8),
292         decode(p_attribute9, mc, l_old_rec.attribute9, p_attribute9),
293         decode(p_attribute10, mc, l_old_rec.attribute10, p_attribute10),
294         decode(p_attribute11, mc, l_old_rec.attribute11, p_attribute11),
295         decode(p_attribute12, mc, l_old_rec.attribute12, p_attribute12),
296         decode(p_attribute13, mc, l_old_rec.attribute13, p_attribute13),
297         decode(p_attribute14, mc, l_old_rec.attribute14, p_attribute14),
298         decode(p_attribute15, mc, l_old_rec.attribute15, p_attribute15),
299         decode(p_object_version_number, mn,
300                  l_old_rec.object_version_number, p_object_version_number+1),
301         sysdate,
302         fnd_global.user_id,
303         fnd_global.login_id
304        FROM dual)
305      WHERE role_model_id = p_role_model_id;
306 
307 END Update_Row;
308 
309 
310 PROCEDURE Delete_Row
311   (P_ROLE_MODEL_ID           IN   NUMBER) IS
312 BEGIN
313    DELETE FROM cn_role_models
314      WHERE role_model_id = p_role_model_id;
315 
316 END Delete_Row;
317 
318 END cn_role_models_pkg;