[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_RULES_PKG
Source
1 package body PA_PERF_RULES_PKG as
2 /* $Header: PAPERLTB.pls 120.1 2005/08/19 16:39:34 mwasowic noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_RULE_ID in NUMBER,
7 X_RULE_NAME in VARCHAR2,
8 X_RULE_DESCRIPTION in VARCHAR2,
9 X_RULE_TYPE in VARCHAR2,
10 X_KPA_CODE in VARCHAR2,
11 X_MEASURE_ID in NUMBER,
12 X_MEASURE_FORMAT in VARCHAR2,
13 X_CURRENCY_TYPE in VARCHAR2,
14 X_PERIOD_TYPE in VARCHAR2,
15 X_PRECISION in NUMBER,
16 X_START_DATE_ACTIVE in DATE,
17 X_END_DATE_ACTIVE in DATE,
18 X_SCORE_METHOD in VARCHAR2,
19 X_RECORD_VERSION_NUMBER in NUMBER ,
20 X_CREATION_DATE in DATE ,
21 X_CREATED_BY in NUMBER ,
22 X_LAST_UPDATE_DATE in DATE ,
23 X_LAST_UPDATED_BY in NUMBER ,
24 X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26 cursor C is select ROWID from PA_PERF_RULES
27 where RULE_ID = X_RULE_ID
28 ;
29 begin
30 insert into PA_PERF_RULES (
31 RULE_ID,
32 RULE_NAME,
33 RULE_DESCRIPTION,
34 RULE_TYPE,
35 KPA_CODE,
36 MEASURE_ID,
37 MEASURE_FORMAT,
38 CURRENCY_TYPE,
39 PERIOD_TYPE,
40 PRECISION,
41 START_DATE_ACTIVE,
42 END_DATE_ACTIVE,
43 SCORE_METHOD,
44 RECORD_VERSION_NUMBER,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_LOGIN
50 ) values
51 ( X_RULE_ID,
52 X_RULE_NAME,
53 X_RULE_DESCRIPTION,
54 X_RULE_TYPE,
55 X_KPA_CODE,
56 X_MEASURE_ID,
57 X_MEASURE_FORMAT,
58 X_CURRENCY_TYPE,
59 X_PERIOD_TYPE,
60 X_PRECISION,
61 X_START_DATE_ACTIVE,
62 X_END_DATE_ACTIVE,
63 X_SCORE_METHOD,
64 NVL(X_RECORD_VERSION_NUMBER,1),
65 NVL(X_CREATION_DATE,sysdate),
66 NVL(X_CREATED_BY,fnd_global.user_id),
67 NVL(X_LAST_UPDATE_DATE,sysdate),
68 NVL(X_LAST_UPDATED_BY,fnd_global.user_id),
69 NVL(X_LAST_UPDATE_LOGIN,fnd_global.login_id));
70
71 open c;
72 fetch c into X_ROWID;
73 if (c%notfound) then
74 close c;
75 raise no_data_found;
76 end if;
77 close c;
78
79 end INSERT_ROW;
80
81
82 procedure LOCK_ROW (
83 X_RULE_ID in NUMBER,
84 X_RECORD_VERSION_NUMBER in NUMBER
85 ) IS
86 Resource_Busy EXCEPTION;
87 Invalid_Rec_Change EXCEPTION;
88 PRAGMA exception_init(Resource_Busy,-00054);
89 l_rec_ver_no NUMBER;
90 g_module_name VARCHAR2(100) := 'pa.plsql.PA_PERF_RULES';
91 l_debug_mode VARCHAR2(1);
92
93 l_debug_level2 CONSTANT NUMBER := 2;
94 l_debug_level3 CONSTANT NUMBER := 3;
95 l_debug_level4 CONSTANT NUMBER := 4;
96 l_debug_level5 CONSTANT NUMBER := 5;
97 BEGIN
98
99 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
100
101 IF l_debug_mode = 'Y' THEN
102 pa_debug.set_curr_function( p_function => 'PERF_RULES',
103 p_debug_mode => l_debug_mode );
104 END IF;
105
106
107 IF l_debug_mode = 'Y' THEN
108 pa_debug.g_err_stage:= 'Validating input parameters';
109 pa_debug.write(g_module_name,pa_debug.g_err_stage,
110 l_debug_level3);
111 END IF;
112
113 IF l_debug_mode = 'Y' THEN
114 pa_debug.g_err_stage:= 'X_RULE_ID = '|| X_RULE_ID;
115 pa_debug.write(g_module_name,pa_debug.g_err_stage,
116 l_debug_level5);
117 pa_debug.g_err_stage:= 'X_RECORD_VERSION_NUMBER = '|| X_RECORD_VERSION_NUMBER;
118 pa_debug.write(g_module_name,pa_debug.g_err_stage,
119 l_debug_level5);
120
121 END IF;
122
123 IF l_debug_mode = 'Y' THEN
124 pa_debug.g_err_stage:= 'in lock row method,ABOUT TO EXECUTE QUERY';
125 pa_debug.write(g_module_name,pa_debug.g_err_stage,l_debug_level3);
126 pa_debug.reset_curr_function;
127 END IF;
128
129 select record_version_number into l_rec_ver_no
130 from pa_perf_rules
131 where rule_id = X_RULE_ID
132 for update nowait;
133
134 if(X_RECORD_VERSION_NUMBER <> l_rec_ver_no) then
135 raise Invalid_Rec_Change;
136 end if;
137
138 IF l_debug_mode = 'Y' THEN
139 pa_debug.g_err_stage:= 'in lock row method,query executed';
140 pa_debug.write(g_module_name,pa_debug.g_err_stage,l_debug_level3);
141 pa_debug.reset_curr_function;
142 END IF;
143 EXCEPTION
144 when NO_DATA_FOUND then
145 PA_UTILS.ADD_MESSAGE
146 ( p_app_short_name => 'PA',
147 p_msg_name => 'FND_RECORD_DELETED_ERROR');
148 rollback to sp;
149
150 when Invalid_Rec_Change then
151 PA_UTILS.ADD_MESSAGE
152 ( p_app_short_name => 'PA',
153 p_msg_name => 'FND_RECORD_CHANGED_ERROR');
154 rollback to sp;
155
156 when Resource_Busy then
157 PA_UTILS.ADD_MESSAGE
158 ( p_app_short_name => 'PA',
159 p_msg_name => 'FND_LOCK_RECORD_ERROR');
160 rollback to sp;
161 end LOCK_ROW;
162
163
164 procedure UPDATE_ROW (
165 X_RULE_ID in NUMBER,
166 X_RULE_NAME in VARCHAR2,
167 X_RULE_DESCRIPTION in VARCHAR2,
168 X_RULE_TYPE in VARCHAR2,
169 X_KPA_CODE in VARCHAR2,
170 X_MEASURE_ID in NUMBER,
171 X_MEASURE_FORMAT in VARCHAR2,
172 X_CURRENCY_TYPE in VARCHAR2,
173 X_PERIOD_TYPE in VARCHAR2,
174 X_PRECISION in NUMBER,
175 X_START_DATE_ACTIVE in DATE,
176 X_END_DATE_ACTIVE in DATE,
177 X_SCORE_METHOD in VARCHAR2,
178 X_RECORD_VERSION_NUMBER in NUMBER,
179 X_LAST_UPDATE_DATE in DATE ,
180 X_LAST_UPDATED_BY in NUMBER ,
181 X_LAST_UPDATE_LOGIN in NUMBER
182 ) is
183 begin
184 update PA_PERF_RULES set
185 RULE_NAME = X_RULE_NAME,
186 RULE_DESCRIPTION = X_RULE_DESCRIPTION,
187 RULE_TYPE = X_RULE_TYPE,
188 KPA_CODE = X_KPA_CODE,
189 MEASURE_ID = X_MEASURE_ID,
190 MEASURE_FORMAT = X_MEASURE_FORMAT,
191 CURRENCY_TYPE = X_CURRENCY_TYPE,
192 PERIOD_TYPE = X_PERIOD_TYPE,
193 PRECISION = X_PRECISION,
194 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
195 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
196 SCORE_METHOD = X_SCORE_METHOD,
197 RULE_ID = X_RULE_ID,
198 RECORD_VERSION_NUMBER = NVL(X_RECORD_VERSION_NUMBER,RECORD_VERSION_NUMBER+1),
199 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,sysdate),
200 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,fnd_global.user_id),
201 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,fnd_global.login_id)
202 where RULE_ID = X_RULE_ID;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end UPDATE_ROW;
208
209
210 procedure DELETE_ROW (
211 X_RULE_ID in NUMBER
212 ) is
213 begin
214 delete from PA_PERF_RULES
215 where RULE_ID = X_RULE_ID;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220
221 end DELETE_ROW;
222
223
224 end PA_PERF_RULES_PKG;