[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_HEADER_PUB
Source
1 PACKAGE body AHL_PC_HEADER_PUB AS
2 /* $Header: AHLPPCHB.pls 115.12 2003/10/20 19:36:19 sikumar noship $ */
3
4 ---------------------------
5 -- VALIDATE_FND_LOOKUPS --
6 ---------------------------
7 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
8 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
9
10 PROCEDURE VALIDATE_FND_LOOKUPS
11 (
12 p_lookup_meaning IN OUT NOCOPY VARCHAR2,
13 p_lookup_code IN OUT NOCOPY VARCHAR2,
14 p_lookup_type IN VARCHAR2,
15 p_error_exists IN VARCHAR2,
16 p_error_reqd IN VARCHAR2
17 )
18 IS
19 cursor check_fnd_lookup
20 (
21 p_lookup_type IN VARCHAR2,
22 p_lookup_meaning IN VARCHAR2 :=NULL,
23 p_lookup_code IN VARCHAR2 :=NULL
24 )
25 is
26 SELECT LOOKUP_CODE , MEANING
27 FROM FND_LOOKUP_VALUES_VL
28 WHERE LOOKUP_TYPE = p_lookup_type
29 AND MEANING LIKE NVL(p_lookup_meaning,'%')
30 AND LOOKUP_CODE LIKE NVL(p_lookup_code,'%');
31
32 l_lookup_code varchar2(30);
33 l_lookup_meaning varchar2(80);
34
35 BEGIN
36 IF TRIM(p_lookup_meaning) IS NULL AND TRIM(p_lookup_code) IS NULL
37 THEN
38 FND_MESSAGE.Set_Name('AHL',p_error_reqd);
39 FND_MSG_PUB.ADD;
40 ELSE
41 OPEN check_fnd_lookup(p_lookup_type ,p_lookup_meaning, p_lookup_code);
42 FETCH check_fnd_lookup into p_lookup_code,p_lookup_meaning;
43 IF check_fnd_lookup%NOTFOUND
44 THEN
45 FND_MESSAGE.Set_Name('AHL',p_error_exists);
46 FND_MSG_PUB.ADD;
47 CLOSE check_fnd_lookup;
48 END IF;
49 END IF;
50
51 END VALIDATE_FND_LOOKUPS;
52
53 ----------------------
54 -- VALIDATE_HEADER --
55 ----------------------
56 PROCEDURE VALIDATE_HEADER ( p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC )
57 IS
58
59 BEGIN
60 IF (p_x_pc_header_rec.OPERATION_FLAG <> AHL_PC_HEADER_PVT.G_DML_DELETE)
61 THEN
62 p_x_pc_header_rec.NAME := TRIM(p_x_pc_header_rec.NAME);
63 p_x_pc_header_rec.DESCRIPTION := TRIM(p_x_pc_header_rec.DESCRIPTION);
64
65 -- PC name is mandatory
66 IF TRIM(p_x_pc_header_rec.NAME) IS NULL
67 THEN
68 FND_MESSAGE.Set_Name('AHL','AHL_PC_NAME_REQD');
69 FND_MSG_PUB.ADD;
70 END IF;
71
72 VALIDATE_FND_LOOKUPS
73 (
74 p_x_pc_header_rec.STATUS_DESC,
75 p_x_pc_header_rec.STATUS,
76 'AHL_PC_STATUS',
77 'AHL_PC_STATUS_NOT_FOUND',
78 'AHL_PC_STATUS_REQD'
79 );
80
81 -- FND check for PRIMARY_FLAG
82 VALIDATE_FND_LOOKUPS
83 (
84 p_x_pc_header_rec.PRIMARY_FLAG_DESC,
85 p_x_pc_header_rec.PRIMARY_FLAG,
86 'YES_NO',
87 'AHL_PC_PRIMARY_FLAG_NOT_FOUND',
88 'AHL_PC_PRIMARY_FLAG_REQD'
89 );
90
91 -- FND check for ASSOCIATION_TYPE
92 VALIDATE_FND_LOOKUPS
93 (
94 p_x_pc_header_rec.ASSOCIATION_TYPE_DESC,
95 p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG,
96 'AHL_PC_ASSOS_TYPE',
97 'AHL_PC_ASSOCIATION_TYPE_NOT_FOUND',
98 'AHL_PC_ASSOCIATION_TYPE_REQD'
99 );
100 END IF;
101
102 IF (p_x_pc_header_rec.OPERATION_FLAG <> AHL_PC_HEADER_PVT.G_DML_CREATE)
103 THEN
104 IF TRIM(p_x_pc_header_rec.PC_HEADER_ID) IS NULL
105 THEN
106 FND_MESSAGE.Set_Name('AHL','AHL_PC_HEADER_ID_REQCD');
107 FND_MSG_PUB.ADD;
108 END IF;
109 END IF;
110
111 END VALIDATE_HEADER;
112
113 -------------------------------------
114 -- CONVERT_ITEM_TYPE_DESC_TO_CODE --
115 -------------------------------------
116 PROCEDURE CONVERT_ITEM_TYPE_DESC_TO_CODE ( p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC )
117 IS
118
119 CURSOR get_item_type_code (p_pc_item_type_desc IN VARCHAR2)
120 IS
121 select lookup_code, meaning
122 from fnd_lookup_values_vl
123 where lookup_type = 'ITEM_TYPE' and
124 upper(meaning) = upper(p_pc_item_type_desc) and
125 view_application_id = 3 and
126 enabled_flag = 'Y' and
127 sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate);
128
129 CURSOR check_item_type_desc_and_code (p_pc_item_type_code IN VARCHAR2, p_pc_item_type_desc IN VARCHAR2)
130 IS
131 select lookup_code, meaning
132 from fnd_lookup_values_vl
133 where lookup_type = 'ITEM_TYPE' and
134 upper(meaning) = upper(p_pc_item_type_desc) and
135 lookup_code = p_pc_item_type_code and
136 view_application_id = 3 and
137 enabled_flag = 'Y' and
138 sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate);
139
140 l_item_type_code VARCHAR2(30);
141 l_dummy_code VARCHAR2(30);
142 l_item_type_desc VARCHAR2(80);
143 l_dummy_desc VARCHAR2(80);
144
145 BEGIN
146
147 -- Item Type Desc is not passed from frontend, then ERROR
148 IF (p_x_pc_header_rec.PRODUCT_TYPE_DESC) IS NULL
149 THEN
150 FND_MESSAGE.SET_NAME('AHL','AHL_PC_PRODUCT_TYPE_REQD');
151 FND_MSG_PUB.ADD;
152 RAISE FND_API.G_EXC_ERROR;
153 ELSE
154 OPEN get_item_type_code (p_x_pc_header_rec.PRODUCT_TYPE_DESC);
155 FETCH get_item_type_code INTO l_item_type_code, l_item_type_desc;
156 -- No such Item Type found for passed Item Type Desc, then ERROR
157 IF (get_item_type_code%NOTFOUND)
158 THEN
159 FND_MESSAGE.SET_NAME('AHL','AHL_PC_PRODUCT_TYPE_NOT_FOUND');
160 FND_MSG_PUB.ADD;
161 CLOSE get_item_type_code;
162 RAISE FND_API.G_EXC_ERROR;
163 ELSE
164 FETCH get_item_type_code INTO l_dummy_code, l_dummy_desc;
165 -- Multiple matches for Item Type Desc
166 IF get_item_type_code%FOUND
167 THEN
168 -- Check if user had navigated to LOV and later changed the desc to something else that also has multiple matches
169 OPEN check_item_type_desc_and_code (p_x_pc_header_rec.PRODUCT_TYPE_CODE, p_x_pc_header_rec.PRODUCT_TYPE_DESC);
170 FETCH check_item_type_desc_and_code INTO l_dummy_code, l_dummy_desc;
171 IF (check_item_type_desc_and_code%NOTFOUND)
172 THEN
173 FND_MESSAGE.SET_NAME('AHL','AHL_PC_DUP_PROD_CODE_FOR_DESC');
174 FND_MSG_PUB.ADD;
175 CLOSE check_item_type_desc_and_code;
176 CLOSE get_item_type_code;
177 RAISE FND_API.G_EXC_ERROR;
178 ELSE
179 IF ( p_x_pc_header_rec.PRODUCT_TYPE_CODE) IS NULL
180 THEN
181 FND_MESSAGE.SET_NAME('AHL','AHL_PC_DUP_PROD_CODE_FOR_DESC');
182 FND_MSG_PUB.ADD;
183 CLOSE check_item_type_desc_and_code;
184 CLOSE get_item_type_code;
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187 END IF;
188 ELSE
189 p_x_pc_header_rec.PRODUCT_TYPE_CODE := l_item_type_code;
190 CLOSE get_item_type_code;
191 END IF;
192 END IF;
193 END IF;
194
195 END CONVERT_ITEM_TYPE_DESC_TO_CODE;
196
197 -------------------------
198 -- PROCESS_PC_HEADER --
199 -------------------------
200 PROCEDURE PROCESS_PC_HEADER (
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
205 p_module_type IN VARCHAR2 := NULL,
206 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
207 x_return_status OUT NOCOPY VARCHAR2,
208 x_msg_count OUT NOCOPY NUMBER,
209 x_msg_data OUT NOCOPY VARCHAR2
210 ) IS
211
212 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PC_HEADER';
213 l_api_version CONSTANT NUMBER := 1.0;
214 l_return_status VARCHAR2(1);
215
216 l_debug VARCHAR2(2000);
217
218 BEGIN
219 -- Standard start of API savepoint
220 SAVEPOINT PROCESS_PC_HEADER_PUB;
221
222 -- Standard call to check for call compatibility
223 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
224 THEN
225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
226 END IF;
227
228 -- Initialize message list if p_init_msg_list is set to TRUE
229 IF FND_API.To_Boolean(p_init_msg_list)
230 THEN
231 FND_MSG_PUB.Initialize;
232 END IF;
233
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 IF G_DEBUG='Y' THEN
237 AHL_DEBUG_PUB.ENABLE_DEBUG;
238 END IF;
239
240 -- If module type is JSP (or undefined), and it is not Delete PC operation
241 -- Then item_type_desc should be converted to item_type_code
242 -- Also nullify status_desc, association_type_desc, primary_flag_desc -- no scenario where these values will change through some user-action
243 IF ( p_module_type = 'JSP' OR p_module_type IS NULL ) AND
244 ( p_x_pc_header_rec.operation_flag <> AHL_PC_HEADER_PVT.G_DML_DELETE )
245 THEN
246 CONVERT_ITEM_TYPE_DESC_TO_CODE (p_x_pc_header_rec);
247 IF G_DEBUG='Y' THEN
248 AHL_DEBUG_PUB.debug('PCH -- PUB -- Item_Code='||p_x_pc_header_rec.PRODUCT_TYPE_CODE);
249 END IF;
250
251 p_x_pc_header_rec.STATUS_DESC := NULL;
252 p_x_pc_header_rec.ASSOCIATION_TYPE_DESC := NULL;
253 p_x_pc_header_rec.PRIMARY_FLAG_DESC := NULL;
254 END IF;
255
256 VALIDATE_HEADER (p_x_pc_header_rec);
257
258 -- Check Error Message stack.
259 x_msg_count := FND_MSG_PUB.count_msg;
260 IF x_msg_count > 0
261 THEN
262 RAISE FND_API.G_EXC_ERROR;
263 END IF;
264
265 -- Call PVT APIs
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267
268 IF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_CREATE)
269 THEN
270 IF G_DEBUG='Y' THEN
271 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling CREATE_PC_HEADER for Name='||p_x_pc_header_rec.NAME);
272 END IF;
273
274 AHL_PC_HEADER_PVT.CREATE_PC_HEADER
275 (
276 p_api_version => 1.0,
277 p_init_msg_list => FND_API.G_FALSE,
278 p_commit => FND_API.G_FALSE,
279 p_validation_level => p_validation_level,
280 p_x_pc_header_rec => p_x_pc_header_rec,
281 x_return_status => x_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data
284 );
285 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_UPDATE)
286 THEN
287 IF G_DEBUG='Y' THEN
288 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling UPDATE_PC_HEADER for ID='||p_x_pc_header_rec.PC_HEADER_ID);
289 END IF;
290
291 AHL_PC_HEADER_PVT.UPDATE_PC_HEADER
292 (
293 p_api_version => 1.0,
294 p_init_msg_list => FND_API.G_FALSE,
295 p_commit => FND_API.G_FALSE,
296 p_validation_level => p_validation_level,
297 p_x_pc_header_rec => p_x_pc_header_rec,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data
301 );
302 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_COPY)
303 THEN
304 IF G_DEBUG='Y' THEN
305 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling COPY_PC_HEADER for ID='||p_x_pc_header_rec.PC_HEADER_ID||' -- New Name='||p_x_pc_header_rec.NAME);
306 END IF;
307
308 AHL_PC_HEADER_PVT.COPY_PC_HEADER
309 (
310 p_api_version => 1.0,
311 p_init_msg_list => FND_API.G_FALSE,
312 p_commit => FND_API.G_FALSE,
313 p_validation_level => p_validation_level,
314 p_x_pc_header_rec => p_x_pc_header_rec,
315 x_return_status => x_return_status,
316 x_msg_count => x_msg_count,
317 x_msg_data => x_msg_data
318 );
319 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_DELETE)
320 THEN
321 IF G_DEBUG='Y' THEN
322 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling DELETE_PC_HEADER for ID='||p_x_pc_header_rec.PC_HEADER_ID);
323 END IF;
324
325 AHL_PC_HEADER_PVT.DELETE_PC_HEADER
326 (
327 p_api_version => 1.0,
328 p_init_msg_list => FND_API.G_FALSE,
329 p_commit => FND_API.G_FALSE,
330 p_validation_level => p_validation_level,
331 p_x_pc_header_rec => p_x_pc_header_rec,
332 x_return_status => x_return_status,
333 x_msg_count => x_msg_count,
334 x_msg_data => x_msg_data
335 );
336 END IF;
337
338 -- Check Error Message stack.
339 x_msg_count := FND_MSG_PUB.count_msg;
340 IF x_msg_count > 0
341 THEN
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344
345 -- Standard check for p_commit
346 IF FND_API.To_Boolean (p_commit)
347 THEN
348 COMMIT WORK;
349 END IF;
350
351 -- Standard call to get message count and if count is 1, get message info
352 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
353 p_data => x_msg_data,
354 p_encoded => fnd_api.g_false );
355
356 EXCEPTION
357 WHEN FND_API.G_EXC_ERROR THEN
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 Rollback to PROCESS_PC_HEADER_PUB;
360 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
361 p_data => x_msg_data,
362 p_encoded => fnd_api.g_false );
363
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 Rollback to PROCESS_PC_HEADER_PUB;
367 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => fnd_api.g_false );
370
371 WHEN OTHERS THEN
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 Rollback to PROCESS_PC_HEADER_PUB;
374 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
375 THEN
376 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
377 p_procedure_name => 'PROCESS_PC_HEADER',
378 p_error_text => SUBSTR(SQLERRM,1,240) );
379 END IF;
380 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
381 p_data => x_msg_data,
382 p_encoded => fnd_api.g_false );
383
384 END PROCESS_PC_HEADER ;
385
386 END AHL_PC_HEADER_PUB;