1 PACKAGE BODY PA_OBJECT_REGIONS_PVT AS
2 --$Header: PAAPORVB.pls 120.2 2005/08/19 16:16:02 mwasowic noship $
3
4 procedure create_object_page_region (
5 p_api_version IN NUMBER := 1.0,
6 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
7 p_commit IN VARCHAR2 := FND_API.g_false,
8 p_validate_only IN VARCHAR2 := FND_API.g_true,
9 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
10 P_OBJECT_ID IN NUMBER,
11 P_OBJECT_TYPE IN VARCHAR2,
12 P_PLACEHOLDER_REG_CODE IN VARCHAR2,
13 P_REPLACEMENT_REG_CODE IN VARCHAR2,
14 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
15 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
16 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 ) IS
18 CURSOR check_object_page_region_exits
19 IS
20 SELECT 'X'
21 FROM pa_object_regions
22 WHERE object_id = p_object_id
23 AND object_type = p_object_type
24 AND placeholder_reg_code = p_placeholder_reg_code;
25
26 CURSOR check_valid_region(p_reg_code in varchar2)
27 IS
28 SELECT 'X'
29 FROM pa_page_layout_regions
30 WHERE
31 view_region_code = p_reg_code
32 OR nvl(edit_region_code,view_region_code) = p_reg_code;
33 l_dummy varchar2(1);
34
35 BEGIN
36 -- Initialize the Error Stack
37 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Create_Page_Layout');
38
39 -- Initialize the return status to success
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 -- Issue API savepoint if the transaction is to be committed
43 IF p_commit = FND_API.G_TRUE THEN
44 SAVEPOINT create_object_page_region;
45 END IF;
46
47 -- Check a valid place holder region
48 open check_valid_region(P_PLACEHOLDER_REG_CODE);
49 fetch check_valid_region into l_dummy;
50 if(check_valid_region%NOTFOUND) then
51 PA_UTILS.Add_Message( p_app_short_name => 'PA'
52 ,p_msg_name => 'PA_PA_PAGE_REGION_INV'
53 );
54 x_return_status := FND_API.G_RET_STS_ERROR;
55 close check_valid_region;
56 return;
57 end if;
58 close check_valid_region;
59 -- Check prior association doesn't exists
60 open check_object_page_region_exits;
61 fetch check_object_page_region_exits into l_dummy;
62 if (check_object_page_region_exits%NOTFOUND) then
63 close check_object_page_region_exits;
64 -- Insert into pa_object_regions
65 IF (p_validate_only <>FND_API.g_true
66 AND x_return_status = FND_API.g_ret_sts_success) THEN
67
68 PA_OBJECT_REGIONS_PKG.insert_row(
69 P_OBJECT_ID ,
70 P_OBJECT_TYPE ,
71 P_PLACEHOLDER_REG_CODE ,
72 P_REPLACEMENT_REG_CODE ,
73 sysdate ,
74 fnd_global.user_id ,
75 sysdate ,
76 fnd_global.user_id ,
77 fnd_global.user_id );
78 END IF;
79
80 else
81 close check_object_page_region_exits;
82 end if;
83
84 -- Commit changes if the flag is set and there is no error
85 IF (p_commit = FND_API.G_TRUE
86 AND x_return_status = FND_API.g_ret_sts_success )THEN
87 COMMIT;
88 END IF;
89
90 -- Reset the error stack when returning to the calling program
91 PA_DEBUG.Reset_Err_Stack;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 IF p_commit = FND_API.G_TRUE THEN
96 ROLLBACK TO create_object_page_region;
97 END IF;
98 -- Set the excetption Message and the stack
99 FND_MSG_PUB.add_exc_msg
100 ( p_pkg_name => 'PA_OBJECT_REGIONS_PVT.create_object_page_region'
101 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
102 --
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
104 RAISE; -- This is optional depending on the needs
105 END create_object_page_region;
106
107 procedure update_object_page_region (
108 p_api_version IN NUMBER := 1.0,
109 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
110 p_commit IN VARCHAR2 := FND_API.g_false,
111 p_validate_only IN VARCHAR2 := FND_API.g_true,
112 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
113 P_OBJECT_ID IN NUMBER,
114 P_OBJECT_TYPE IN VARCHAR2,
115 P_PLACEHOLDER_REG_CODE IN VARCHAR2,
116 P_REPLACEMENT_REG_CODE IN VARCHAR2,
117 p_record_version_number IN NUMBER,
118 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
119 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
120 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
121 )
122 is
123 CURSOR check_object_page_region_exits
124 IS
125 SELECT 'X'
126 FROM pa_object_regions
127 WHERE object_id = p_object_id
128 AND object_type = p_object_type
129 AND placeholder_reg_code = p_placeholder_reg_code;
130
131 CURSOR check_valid_region(p_reg_code in varchar2)
132 IS
133 SELECT 'X'
134 FROM pa_page_layout_regions
135 WHERE
136 view_region_code = p_reg_code
137 OR nvl(edit_region_code,view_region_code) = p_reg_code;
138 l_dummy varchar2(1);
139
140 BEGIN
141 -- Initialize the Error Stack
142 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Create_Page_Layout');
143
144 -- Initialize the return status to success
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 -- Issue API savepoint if the transaction is to be committed
148 IF p_commit = FND_API.G_TRUE THEN
149 SAVEPOINT update_object_page_region;
150 END IF;
151
152 -- Check a valid place holder region
153 open check_valid_region(P_PLACEHOLDER_REG_CODE);
154 fetch check_valid_region into l_dummy;
155 if(check_valid_region%NOTFOUND) then
156 PA_UTILS.Add_Message( p_app_short_name => 'PA'
157 ,p_msg_name => 'PA_PA_PAGE_REGION_INV'
158 );
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 close check_valid_region;
161 return;
162 end if;
163 close check_valid_region;
164 -- Delete the association if the replacement region code is null
165 IF (P_REPLACEMENT_REG_CODE IS NULL) THEN
166
167 PA_OBJECT_REGIONS_PKG.DELETE_ROW (
168 P_OBJECT_ID ,
169 P_OBJECT_TYPE ,
170 P_PLACEHOLDER_REG_CODE );
171 RETURN;
172 END IF;
173 -- Update pa_object_regions
174 IF (p_validate_only <>FND_API.g_true
175 AND x_return_status = FND_API.g_ret_sts_success) THEN
176
177 PA_OBJECT_REGIONS_PKG.update_row(
178 P_OBJECT_ID ,
179 P_OBJECT_TYPE ,
180 P_PLACEHOLDER_REG_CODE ,
181 P_REPLACEMENT_REG_CODE ,
182 p_record_version_number ,
183 sysdate ,
184 fnd_global.user_id ,
185 fnd_global.user_id );
186 END IF;
187
188 -- Commit changes if the flag is set and there is no error
189 IF (p_commit = FND_API.G_TRUE
190 AND x_return_status = FND_API.g_ret_sts_success )THEN
191 COMMIT;
192 END IF;
193
194 -- Reset the error stack when returning to the calling program
195 PA_DEBUG.Reset_Err_Stack;
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND then
199 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
200 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
201 x_return_status := FND_API.G_RET_STS_ERROR;
202 IF p_commit = FND_API.G_TRUE THEN
203 ROLLBACK TO update_object_page_region;
204 END IF;
205 RAISE;
206 WHEN OTHERS THEN
207 IF p_commit = FND_API.G_TRUE THEN
208 ROLLBACK TO update_object_page_region;
209 END IF;
210 -- Set the excetption Message and the stack
211 FND_MSG_PUB.add_exc_msg
212 ( p_pkg_name => 'PA_OBJECT_REGIONS_PVT.update_object_page_region'
213 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
214 --
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 RAISE; -- This is optional depending on the needs
217 End update_object_page_region;
218
219 procedure DELETE_object_page_region (
220 p_api_version IN NUMBER := 1.0,
221 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
222 p_commit IN VARCHAR2 := FND_API.g_false,
223 p_validate_only IN VARCHAR2 := FND_API.g_true,
224 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
225 P_OBJECT_ID IN NUMBER,
226 P_OBJECT_TYPE IN VARCHAR2,
227 P_PLACEHOLDER_REG_CODE IN VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
229 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
230 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
231 )
232 IS
233 Begin
234 -- Initialize the Error Stack
235 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Create_Page_Layout');
236
237 -- Initialize the return status to success
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 -- Issue API savepoint if the transaction is to be committed
241 IF p_commit = FND_API.G_TRUE THEN
242 SAVEPOINT update_object_page_region;
243 END IF;
244
245 IF (p_validate_only <>FND_API.g_true
246 AND x_return_status = FND_API.g_ret_sts_success) THEN
247
248 PA_OBJECT_REGIONS_PKG.delete_row(
249 P_OBJECT_ID ,
250 P_OBJECT_TYPE ,
251 P_PLACEHOLDER_REG_CODE );
252 END IF;
253
254 -- Commit changes if the flag is set and there is no error
255 IF (p_commit = FND_API.G_TRUE
256 AND x_return_status = FND_API.g_ret_sts_success )THEN
257 COMMIT;
258 END IF;
259
260 -- Reset the error stack when returning to the calling program
261 PA_DEBUG.Reset_Err_Stack;
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF p_commit = FND_API.G_TRUE THEN
266 ROLLBACK TO delete_object_page_region;
267 END IF;
268 -- Set the excetption Message and the stack
269 FND_MSG_PUB.add_exc_msg
270 ( p_pkg_name => 'PA_OBJECT_REGIONS_PVT.delete_object_page_region'
271 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
272 --
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
274 RAISE; -- This is optional depending on the needs
275
276 End DELETE_object_page_region;
277
278 END PA_OBJECT_REGIONS_PVT;