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