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