[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_TRANSACTIONS_PKG
Source
1 package body PA_PERF_TRANSACTIONS_PKG as
2 /* $Header: PAPEEXTB.pls 120.1 2005/08/19 16:38:45 mwasowic noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_PERF_TXN_ID in NUMBER,
7 X_PERF_TXN_OBJ_TYPE in VARCHAR2,
8 X_PERF_TXN_OBJ_ID in NUMBER,
9 X_OBJECT_RULE_ID in NUMBER,
10 X_RELATED_OBJ_TYPE in VARCHAR2,
11 X_RELATED_OBJ_ID in NUMBER,
12 X_RULE_ID in NUMBER,
13 X_PROJECT_ID in NUMBER,
14 X_KPA_CODE in VARCHAR2,
15 X_MEASURE_ID in NUMBER,
16 X_MEASURE_VALUE in NUMBER,
17 X_PERIOD_NAME in VARCHAR2,
18 X_INDICATOR_CODE in VARCHAR2,
19 X_THRESHOLD_FROM in NUMBER,
20 X_THRESHOLD_TO in NUMBER,
21 X_WEIGHTING in NUMBER,
22 X_PRECISION in NUMBER,
23 X_PERIOD_TYPE in VARCHAR2,
24 X_CURRENCY_TYPE in VARCHAR2,
25 X_MEASURE_FORMAT in VARCHAR2,
26 X_PROGRAM_ID in NUMBER,
27 X_DATE_CHECKED in DATE,
28 X_EXCEPTION_FLAG in VARCHAR2,
29 X_CURRENT_FLAG in VARCHAR2,
30 X_INCLUDED_IN_SCORING in VARCHAR2,
31 X_RECORD_VERSION_NUMBER in NUMBER,
32 X_CREATION_DATE in DATE ,
33 X_CREATED_BY in NUMBER ,
34 X_LAST_UPDATE_DATE in DATE ,
35 X_LAST_UPDATED_BY in NUMBER ,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38 cursor C is select ROWID from PA_PERF_TRANSACTIONS
39 where PERF_TXN_ID = X_PERF_TXN_ID
40 ;
41 begin
42 insert into PA_PERF_TRANSACTIONS (
43 PERF_TXN_ID,
44 PERF_TXN_OBJ_TYPE,
45 PERF_TXN_OBJ_ID,
46 OBJECT_RULE_ID,
47 RELATED_OBJ_TYPE,
48 RELATED_OBJ_ID,
49 RULE_ID,
50 PROJECT_ID,
51 KPA_CODE,
52 MEASURE_ID,
53 MEASURE_VALUE,
54 PERIOD_NAME,
55 INDICATOR_CODE,
56 THRESHOLD_FROM,
57 THRESHOLD_TO,
58 WEIGHTING,
59 PRECISION,
60 PERIOD_TYPE,
61 CURRENCY_TYPE,
62 MEASURE_FORMAT,
63 PROGRAM_ID,
64 DATE_CHECKED,
65 EXCEPTION_FLAG,
66 CURRENT_FLAG,
67 INCLUDED_IN_SCORING,
68 RECORD_VERSION_NUMBER,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_LOGIN )
74 values (
75 X_PERF_TXN_ID,
76 X_PERF_TXN_OBJ_TYPE,
77 X_PERF_TXN_OBJ_ID,
78 X_OBJECT_RULE_ID,
79 X_RELATED_OBJ_TYPE,
80 X_RELATED_OBJ_ID,
81 X_RULE_ID,
82 X_PROJECT_ID,
83 X_KPA_CODE,
84 X_MEASURE_ID,
85 X_MEASURE_VALUE,
86 X_PERIOD_NAME,
87 X_INDICATOR_CODE,
88 X_THRESHOLD_FROM,
89 X_THRESHOLD_TO,
90 X_WEIGHTING,
91 X_PRECISION,
92 X_PERIOD_TYPE,
93 X_CURRENCY_TYPE,
94 X_MEASURE_FORMAT,
95 X_PROGRAM_ID,
96 X_DATE_CHECKED,
97 X_EXCEPTION_FLAG,
98 X_CURRENT_FLAG,
99 X_INCLUDED_IN_SCORING,
100 NVL(X_RECORD_VERSION_NUMBER,1),
101 NVL(X_CREATION_DATE,sysdate),
102 NVL(X_CREATED_BY,fnd_global.user_id),
103 NVL(X_LAST_UPDATE_DATE,sysdate),
104 NVL(X_LAST_UPDATED_BY,fnd_global.user_id),
105 NVL(X_LAST_UPDATE_LOGIN,fnd_global.login_id)
106 );
107
108 open c;
109 fetch c into X_ROWID;
110 if (c%notfound) then
111 close c;
112 raise no_data_found;
113 end if;
114 close c;
115
116 end INSERT_ROW;
117
118 procedure LOCK_ROW (
119 X_PERF_TXN_ID in NUMBER,
120 X_RECORD_VERSION_NUMBER in NUMBER
121 ) IS
122 Resource_Busy EXCEPTION;
123 Invalid_Rec_Change EXCEPTION;
124 PRAGMA exception_init(Resource_Busy,-00054);
125 l_rec_ver_no NUMBER;
126 g_module_name VARCHAR2(100) := 'pa.plsql.PA_PERF_TRANSACTIONS_PKG';
127 l_debug_mode VARCHAR2(1);
128
129 l_debug_level2 CONSTANT NUMBER := 2;
130 l_debug_level3 CONSTANT NUMBER := 3;
131 l_debug_level4 CONSTANT NUMBER := 4;
132 l_debug_level5 CONSTANT NUMBER := 5;
133 BEGIN
134
135
136 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
137
138 IF l_debug_mode = 'Y' THEN
139 pa_debug.set_curr_function( p_function => 'PERF_TRANSACTIONS',
140 p_debug_mode => l_debug_mode );
141 END IF;
142
143
144 IF l_debug_mode = 'Y' THEN
145 pa_debug.g_err_stage:= 'Validating input parameters';
146 pa_debug.write(g_module_name,pa_debug.g_err_stage,
147 l_debug_level3);
148 END IF;
149
150 IF l_debug_mode = 'Y' THEN
151 pa_debug.g_err_stage:= 'X_PERF_TXN_ID = '|| X_PERF_TXN_ID;
152 pa_debug.write(g_module_name,pa_debug.g_err_stage,
153 l_debug_level5);
154 pa_debug.g_err_stage:= 'X_RECORD_VERSION_NUMBER = '|| X_RECORD_VERSION_NUMBER;
155 pa_debug.write(g_module_name,pa_debug.g_err_stage,
156 l_debug_level5);
157
158 END IF;
159
160 IF l_debug_mode = 'Y' THEN
161 pa_debug.g_err_stage:= 'in lock row method,about to execute query';
162 pa_debug.write(g_module_name,pa_debug.g_err_stage,l_debug_level3);
163 pa_debug.reset_curr_function;
164 END IF;
165
166 select record_version_number into l_rec_ver_no
167 from pa_perf_transactions
168 where perf_txn_id = X_PERF_TXN_ID
169 for update nowait;
170
171 if(X_RECORD_VERSION_NUMBER <> l_rec_ver_no) then
172 raise Invalid_Rec_Change;
173 end if;
174
175 IF l_debug_mode = 'Y' THEN
176 pa_debug.g_err_stage:= 'in lock row method,query executed';
177 pa_debug.write(g_module_name,pa_debug.g_err_stage,l_debug_level3);
178 pa_debug.reset_curr_function;
179 END IF;
180 EXCEPTION
181 when NO_DATA_FOUND then
182 PA_UTILS.ADD_MESSAGE
183 ( p_app_short_name => 'PA',
184 p_msg_name => 'FND_RECORD_DELETED_ERROR');
185 rollback to sp;
186
187 when Invalid_Rec_Change then
188 PA_UTILS.ADD_MESSAGE
189 ( p_app_short_name => 'PA',
190 p_msg_name => 'FND_RECORD_CHANGED_ERROR');
191 rollback to sp;
192
193 when Resource_Busy then
194 PA_UTILS.ADD_MESSAGE
195 ( p_app_short_name => 'PA',
196 p_msg_name => 'FND_LOCK_RECORD_ERROR');
197 rollback to sp;
198 end LOCK_ROW;
199
200
201 procedure UPDATE_ROW (
202 X_PERF_TXN_ID in NUMBER,
203 X_PERF_TXN_OBJ_TYPE in VARCHAR2,
204 X_PERF_TXN_OBJ_ID in NUMBER,
205 X_OBJECT_RULE_ID in NUMBER,
206 X_RELATED_OBJ_TYPE in VARCHAR2,
207 X_RELATED_OBJ_ID in NUMBER,
208 X_RULE_ID in NUMBER,
209 X_PROJECT_ID in NUMBER,
210 X_KPA_CODE in VARCHAR2,
211 X_MEASURE_ID in NUMBER,
212 X_MEASURE_VALUE in NUMBER,
213 X_PERIOD_NAME in VARCHAR2,
214 X_INDICATOR_CODE in VARCHAR2,
215 X_THRESHOLD_FROM in NUMBER,
216 X_THRESHOLD_TO in NUMBER,
217 X_WEIGHTING in NUMBER,
218 X_PRECISION in NUMBER,
219 X_PERIOD_TYPE in VARCHAR2,
220 X_CURRENCY_TYPE in VARCHAR2,
221 X_MEASURE_FORMAT in VARCHAR2,
222 X_PROGRAM_ID in NUMBER,
223 X_DATE_CHECKED in DATE,
224 X_EXCEPTION_FLAG in VARCHAR2,
225 X_CURRENT_FLAG in VARCHAR2,
226 X_INCLUDED_IN_SCORING in VARCHAR2,
227 X_RECORD_VERSION_NUMBER in NUMBER,
228 X_LAST_UPDATE_DATE in DATE ,
229 X_LAST_UPDATED_BY in NUMBER ,
230 X_LAST_UPDATE_LOGIN in NUMBER
231 ) is
232 begin
233 update PA_PERF_TRANSACTIONS set
234 PERF_TXN_ID = X_PERF_TXN_ID,
235 PERF_TXN_OBJ_TYPE = X_PERF_TXN_OBJ_TYPE,
236 PERF_TXN_OBJ_ID = X_PERF_TXN_OBJ_ID,
237 OBJECT_RULE_ID = X_OBJECT_RULE_ID,
238 RELATED_OBJ_TYPE = X_RELATED_OBJ_TYPE,
239 RELATED_OBJ_ID = X_RELATED_OBJ_ID,
240 RULE_ID = X_RULE_ID,
241 PROJECT_ID = X_PROJECT_ID,
242 KPA_CODE = X_KPA_CODE,
243 MEASURE_ID = X_MEASURE_ID,
244 MEASURE_VALUE = X_MEASURE_VALUE,
245 PERIOD_NAME = X_PERIOD_NAME,
246 INDICATOR_CODE = X_INDICATOR_CODE,
247 THRESHOLD_FROM = X_THRESHOLD_FROM,
248 THRESHOLD_TO = X_THRESHOLD_TO,
249 WEIGHTING = X_WEIGHTING,
250 PRECISION = X_PRECISION,
251 PERIOD_TYPE = X_PERIOD_TYPE,
252 CURRENCY_TYPE = X_CURRENCY_TYPE,
253 MEASURE_FORMAT = X_MEASURE_FORMAT,
254 PROGRAM_ID = X_PROGRAM_ID,
255 DATE_CHECKED = X_DATE_CHECKED,
256 EXCEPTION_FLAG = X_EXCEPTION_FLAG,
257 CURRENT_FLAG = X_CURRENT_FLAG,
258 INCLUDED_IN_SCORING = X_INCLUDED_IN_SCORING,
259 RECORD_VERSION_NUMBER = NVL(X_RECORD_VERSION_NUMBER,RECORD_VERSION_NUMBER+1),
260 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,sysdate),
261 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,fnd_global.user_id),
262 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,fnd_global.login_id)
263 where PERF_TXN_ID = X_PERF_TXN_ID
264 ;
265
266 if (sql%notfound) then
267 raise no_data_found;
268 end if;
269 end UPDATE_ROW;
270
271 procedure DELETE_ROW (
272 X_PERF_TXN_ID in NUMBER
273 ) is
274 begin
275 delete from PA_PERF_TRANSACTIONS
276 where PERF_TXN_ID = X_PERF_TXN_ID;
277
278 if (sql%notfound) then
279 raise no_data_found;
280 end if;
281
282 end DELETE_ROW;
283
284
285 end PA_PERF_TRANSACTIONS_PKG;