[Home] [Help]
PACKAGE BODY: APPS.IEX_SCORES_PKG
Source
1 PACKAGE BODY IEX_SCORES_PKG AS
2 /* $Header: iextscob.pls 120.3 2004/10/28 17:51:46 clchang ship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) ;
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_SCORE_ID NUMBER
9 ,p_SCORE_NAME VARCHAR2
10 ,p_SECURITY_GROUP_ID NUMBER
11 ,p_SCORE_DESCRIPTION VARCHAR2
12 ,p_ENABLED_FLAG VARCHAR2
13 ,p_VALID_FROM_DT DATE
14 ,p_VALID_TO_DT DATE
15 ,p_CAMPAIGN_SCHED_ID NUMBER
16 ,p_JTF_OBJECT_CODE VARCHAR2
17 ,p_CONCURRENT_PROG_NAME VARCHAR2
18 ,p_LAST_UPDATE_DATE DATE
19 ,p_LAST_UPDATED_BY NUMBER
20 ,p_CREATION_DATE DATE
21 ,p_CREATED_BY NUMBER
22 ,p_LAST_UPDATE_LOGIN NUMBER
23 ,p_REQUEST_ID NUMBER
24 ,p_PROGRAM_APPLICATION_ID NUMBER
25 ,p_PROGRAM_ID NUMBER
26 ,p_PROGRAM_UPDATE_DATE DATE
27 ,p_STATUS_DETERMINATION VARCHAR2
28 ,p_WEIGHT_REQUIRED VARCHAR2
29 ,p_SCORE_RANGE_LOW VARCHAR2
30 ,p_SCORE_RANGE_HIGH VARCHAR2
31 ,p_OUT_OF_RANGE_RULE VARCHAR2
32 ) IS
33 CURSOR C IS SELECT ROWID FROM IEX_SCORES
34 WHERE SCORE_ID = p_SCORE_ID;
35
36 l_status_determination varchar2(1);
37 l_weight_required varchar2(3);
38 l_score_range_low varchar2(1000);
39 l_score_range_high varchar2(1000);
40 l_out_of_range_rule varchar2(20);
41
42 BEGIN
43 l_status_determination := p_status_determination;
44 l_weight_required := p_weight_required;
45 l_score_range_low := p_score_range_low;
46 l_score_range_high := p_score_range_high;
47 l_out_of_range_rule := p_out_of_range_rule;
48
49 IF (l_status_determination is null) then
50 l_status_determination := 'N';
51 end if;
52 IF (l_weight_required is null) then
53 l_weight_required := '1';
54 end if;
55 IF (l_score_range_low is null) then
56 l_score_range_low := '1';
57 end if;
58 IF (l_score_range_high is null) then
59 l_score_range_high := '100';
60 end if;
61 IF (l_out_of_range_rule is null) then
62 l_out_of_range_rule := 'CLOSEST';
63 end if;
64
65 INSERT INTO IEX_SCORES
66 (
67 SCORE_ID
68 ,SCORE_NAME
69 ,SECURITY_GROUP_ID
70 ,SCORE_DESCRIPTION
71 ,ENABLED_FLAG
72 ,VALID_FROM_DT
73 ,VALID_TO_DT
74 ,CAMPAIGN_SCHED_ID
75 ,JTF_OBJECT_CODE
76 ,CONCURRENT_PROG_NAME
77 ,LAST_UPDATE_DATE
78 ,LAST_UPDATED_BY
79 ,CREATION_DATE
80 ,CREATED_BY
81 ,LAST_UPDATE_LOGIN
82 ,REQUEST_ID
83 ,PROGRAM_APPLICATION_ID
84 ,PROGRAM_ID
85 ,PROGRAM_UPDATE_DATE
86 ,STATUS_DETERMINATION
87 ,WEIGHT_REQUIRED
88 ,SCORE_RANGE_LOW
89 ,SCORE_RANGE_HIGH
90 ,OUT_OF_RANGE_RULE
91 ) VALUES (
92 p_SCORE_ID
93 ,p_SCORE_NAME
94 ,p_SECURITY_GROUP_ID
95 ,p_SCORE_DESCRIPTION
96 ,p_ENABLED_FLAG
97 ,p_VALID_FROM_DT
98 ,p_VALID_TO_DT
99 ,p_CAMPAIGN_SCHED_ID
100 ,p_JTF_OBJECT_CODE
101 ,p_CONCURRENT_PROG_NAME
102 ,p_LAST_UPDATE_DATE
103 ,p_LAST_UPDATED_BY
104 ,p_CREATION_DATE
105 ,p_CREATED_BY
106 ,p_LAST_UPDATE_LOGIN
107 ,p_REQUEST_ID
108 ,p_PROGRAM_APPLICATION_ID
109 ,p_PROGRAM_ID
110 ,p_PROGRAM_UPDATE_DATE
111 ,l_STATUS_DETERMINATION
112 ,l_WEIGHT_REQUIRED
113 ,l_SCORE_RANGE_LOW
114 ,l_SCORE_RANGE_HIGH
115 ,l_OUT_OF_RANGE_RULE
116 );
117
118 OPEN C;
119 FETCH C INTO x_rowid;
120 IF (C%NOTFOUND) THEN
121 CLOSE C;
122 RAISE NO_DATA_FOUND;
123 END IF;
124 CLOSE C;
125 END Insert_Row;
126
127 /* Update_Row procedure */
128 PROCEDURE Update_Row(x_rowid VARCHAR2
129 ,p_SCORE_ID NUMBER
130 ,p_SCORE_NAME VARCHAR2
131 ,p_SECURITY_GROUP_ID NUMBER
132 ,p_SCORE_DESCRIPTION VARCHAR2
133 ,p_ENABLED_FLAG VARCHAR2
134 ,p_VALID_FROM_DT DATE
135 ,p_VALID_TO_DT DATE
136 ,p_CAMPAIGN_SCHED_ID NUMBER
137 ,p_JTF_OBJECT_CODE VARCHAR2
138 ,p_CONCURRENT_PROG_NAME VARCHAR2
139 ,p_LAST_UPDATE_DATE DATE
140 ,p_LAST_UPDATED_BY NUMBER
141 ,p_CREATION_DATE DATE
142 ,p_CREATED_BY NUMBER
143 ,p_LAST_UPDATE_LOGIN NUMBER
144 ,p_REQUEST_ID NUMBER
145 ,p_PROGRAM_APPLICATION_ID NUMBER
146 ,p_PROGRAM_ID NUMBER
147 ,p_PROGRAM_UPDATE_DATE DATE
148 ,p_STATUS_DETERMINATION VARCHAR2
149 ,p_WEIGHT_REQUIRED VARCHAR2
150 ,p_SCORE_RANGE_LOW VARCHAR2
151 ,p_SCORE_RANGE_HIGH VARCHAR2
152 ,p_OUT_OF_RANGE_RULE VARCHAR2
153 ) IS
154 l_status_determination varchar2(1);
155 l_weight_required varchar2(3);
156 l_score_range_low varchar2(1000);
157 l_score_range_high varchar2(1000);
158 l_out_of_range_rule varchar2(20);
159 BEGIN
160 l_status_determination := p_status_determination;
161 l_weight_required := p_weight_required;
162 l_score_range_low := p_score_range_low;
163 l_score_range_high := p_score_range_high;
164 l_out_of_range_rule := p_out_of_range_rule;
165
166 IF (l_status_determination is null) then
167 l_status_determination := 'N';
168 end if;
169 IF (l_weight_required is null) then
170 l_weight_required := '1';
171 end if;
172 IF (l_score_range_low is null) then
173 l_score_range_low := '1';
174 end if;
175 IF (l_score_range_high is null) then
176 l_score_range_high := '100';
177 end if;
178 IF (l_out_of_range_rule is null) then
179 l_out_of_range_rule := 'CLOSEST';
180 end if;
181
182 UPDATE IEX_SCORES SET
183 SCORE_ID = p_SCORE_ID
184 ,SCORE_NAME = p_SCORE_NAME
185 ,SECURITY_GROUP_ID = p_SECURITY_GROUP_ID
186 ,SCORE_DESCRIPTION = p_SCORE_DESCRIPTION
187 ,ENABLED_FLAG = p_ENABLED_FLAG
188 ,VALID_FROM_DT = p_VALID_FROM_DT
189 ,VALID_TO_DT = p_VALID_TO_DT
190 ,CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID
191 ,JTF_OBJECT_CODE = p_JTF_OBJECT_CODE
192 ,CONCURRENT_PROG_NAME = p_CONCURRENT_PROG_NAME
193 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
194 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
195 ,CREATION_DATE = p_CREATION_DATE
196 ,CREATED_BY = p_CREATED_BY
197 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
198 ,REQUEST_ID = p_REQUEST_ID
199 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
200 ,PROGRAM_ID = p_PROGRAM_ID
201 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
202 ,STATUS_DETERMINATION = l_STATUS_DETERMINATION
203 ,WEIGHT_REQUIRED = l_WEIGHT_REQUIRED
204 ,SCORE_RANGE_LOW = l_SCORE_RANGE_LOW
205 ,SCORE_RANGE_HIGH = l_SCORE_RANGE_HIGH
206 ,OUT_OF_RANGE_RULE = l_OUT_OF_RANGE_RULE
207 WHERE rowid = x_rowid;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212 END Update_Row;
213
214 /* Delete_Row procedure */
215 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
216 BEGIN
217 DELETE FROM IEX_SCORES
218 WHERE rowid = x_rowid;
219
220 if (sql%notfound) then
221 raise no_data_found;
222 end if;
223 END Delete_Row;
224
225 /* Lock_Row procedure */
226 PROCEDURE Lock_Row(x_rowid VARCHAR2
227 ,p_SCORE_ID NUMBER
228 ,p_SCORE_NAME VARCHAR2
229 ,p_SECURITY_GROUP_ID NUMBER
230 ,p_SCORE_DESCRIPTION VARCHAR2
231 ,p_ENABLED_FLAG VARCHAR2
232 ,p_VALID_FROM_DT DATE
233 ,p_VALID_TO_DT DATE
234 ,p_CAMPAIGN_SCHED_ID NUMBER
235 ,p_JTF_OBJECT_CODE VARCHAR2
236 ,p_CONCURRENT_PROG_NAME VARCHAR2
237 ,p_LAST_UPDATE_DATE DATE
238 ,p_LAST_UPDATED_BY NUMBER
239 ,p_CREATION_DATE DATE
240 ,p_CREATED_BY NUMBER
241 ,p_LAST_UPDATE_LOGIN NUMBER
242 ,p_REQUEST_ID NUMBER
243 ,p_PROGRAM_APPLICATION_ID NUMBER
244 ,p_PROGRAM_ID NUMBER
245 ,p_PROGRAM_UPDATE_DATE DATE
246 ,p_STATUS_DETERMINATION VARCHAR2
247 ,p_WEIGHT_REQUIRED VARCHAR2
248 ,p_SCORE_RANGE_LOW VARCHAR2
249 ,p_SCORE_RANGE_HIGH VARCHAR2
250 ,p_OUT_OF_RANGE_RULE VARCHAR2
251 ) IS
252 CURSOR C IS SELECT * FROM IEX_SCORES
253 WHERE rowid = x_rowid
254 FOR UPDATE of SCORE_ID NOWAIT;
255 Recinfo C%ROWTYPE;
256 BEGIN
257 OPEN C;
258 FETCH C INTO Recinfo;
259 IF (C%NOTFOUND) THEN
260 CLOSE C;
261 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
262 APP_EXCEPTION.Raise_Exception;
263 END IF;
264 CLOSE C;
265
266 IF (
267 (Recinfo.SCORE_ID = p_SCORE_ID)
268 AND ( (Recinfo.SCORE_NAME = p_SCORE_NAME)
269 OR ( (Recinfo.SCORE_NAME IS NULL)
270 AND (p_SCORE_NAME IS NULL)))
271 AND ( (Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
272 OR ( (Recinfo.SECURITY_GROUP_ID IS NULL)
273 AND (p_SECURITY_GROUP_ID IS NULL)))
274 AND ( (Recinfo.SCORE_DESCRIPTION = p_SCORE_DESCRIPTION)
275 OR ( (Recinfo.SCORE_DESCRIPTION IS NULL)
276 AND (p_SCORE_DESCRIPTION IS NULL)))
277 AND ( (Recinfo.ENABLED_FLAG = p_ENABLED_FLAG)
278 OR ( (Recinfo.ENABLED_FLAG IS NULL)
279 AND (p_ENABLED_FLAG IS NULL)))
280 AND ( (Recinfo.VALID_FROM_DT = p_VALID_FROM_DT)
281 OR ( (Recinfo.VALID_FROM_DT IS NULL)
282 AND (p_VALID_FROM_DT IS NULL)))
283 AND ( (Recinfo.VALID_TO_DT = p_VALID_TO_DT)
284 OR ( (Recinfo.VALID_TO_DT IS NULL)
285 AND (p_VALID_TO_DT IS NULL)))
286 AND ( (Recinfo.CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID)
287 OR ( (Recinfo.CAMPAIGN_SCHED_ID IS NULL)
288 AND (p_CAMPAIGN_SCHED_ID IS NULL)))
289 AND ( (Recinfo.JTF_OBJECT_CODE = p_JTF_OBJECT_CODE)
290 OR ( (Recinfo.JTF_OBJECT_CODE IS NULL)
291 AND (p_JTF_OBJECT_CODE IS NULL)))
292 AND ( (Recinfo.CONCURRENT_PROG_NAME = p_CONCURRENT_PROG_NAME)
293 OR ( (Recinfo.CONCURRENT_PROG_NAME IS NULL)
294 AND (p_CONCURRENT_PROG_NAME IS NULL)))
295 AND ( (Recinfo.WEIGHT_REQUIRED = p_WEIGHT_REQUIRED)
296 OR ( (Recinfo.WEIGHT_REQUIRED IS NULL)
297 AND (p_WEIGHT_REQUIRED IS NULL)))
298 AND ( (Recinfo.SCORE_RANGE_LOW = p_SCORE_RANGE_LOW)
299 OR ( (Recinfo.SCORE_RANGE_LOW IS NULL)
300 AND (p_SCORE_RANGE_LOW IS NULL)))
301 AND ( (Recinfo.SCORE_RANGE_HIGH = p_SCORE_RANGE_HIGH)
302 OR ( (Recinfo.SCORE_RANGE_HIGH IS NULL)
303 AND (p_SCORE_RANGE_HIGH IS NULL)))
304 AND ( (Recinfo.OUT_OF_RANGE_RULE = p_OUT_OF_RANGE_RULE)
305 OR ( (Recinfo.OUT_OF_RANGE_RULE IS NULL)
306 AND (p_OUT_OF_RANGE_RULE IS NULL)))
307 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
308 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
309 AND (p_LAST_UPDATE_DATE IS NULL)))
310 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
311 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
312 AND (p_LAST_UPDATED_BY IS NULL)))
313 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
314 OR ( (Recinfo.CREATION_DATE IS NULL)
315 AND (p_CREATION_DATE IS NULL)))
316 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
317 OR ( (Recinfo.CREATED_BY IS NULL)
318 AND (p_CREATED_BY IS NULL)))
319 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
320 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
321 AND (p_LAST_UPDATE_LOGIN IS NULL)))
322 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
323 OR ( (Recinfo.REQUEST_ID IS NULL)
324 AND (p_REQUEST_ID IS NULL)))
325 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
326 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
327 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
328 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
329 OR ( (Recinfo.PROGRAM_ID IS NULL)
330 AND (p_PROGRAM_ID IS NULL)))
331 AND ( (Recinfo.STATUS_DETERMINATION = p_STATUS_DETERMINATION)
332 OR ( (Recinfo.STATUS_DETERMINATION IS NULL)
333 AND (p_STATUS_DETERMINATION IS NULL)))
334 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
335 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
336 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
337 ) THEN
338 return;
339 ELSE
340 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
341 APP_EXCEPTION.Raise_Exception;
342 END IF;
343 END Lock_Row;
344
345 BEGIN
346 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
347
348 END;
349