[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_HEADER_PUB
Source
1 PACKAGE body AHL_PC_HEADER_PUB AS
2 /* $Header: AHLPPCHB.pls 120.1.12020000.2 2012/12/10 15:42:32 prakkum ship $ */
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);
66 IF TRIM(p_x_pc_header_rec.NAME) IS NULL
63 p_x_pc_header_rec.DESCRIPTION := TRIM(p_x_pc_header_rec.DESCRIPTION);
64
65 -- PC name is mandatory
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
130 IS
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)
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 x_warning_msg_data OUT NOCOPY AHL_PC_HEADER_PUB.Warn_Tbl_Type
211 ) IS
212
213 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PC_HEADER';
214 l_api_version CONSTANT NUMBER := 1.0;
215 l_return_status VARCHAR2(1);
216
217 l_debug VARCHAR2(2000);
218
219 BEGIN
220 -- Standard start of API savepoint
221 SAVEPOINT PROCESS_PC_HEADER_PUB;
222
223 -- Standard call to check for call compatibility
224 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
225 THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230 IF FND_API.To_Boolean(p_init_msg_list)
227 END IF;
228
229 -- Initialize message list if p_init_msg_list is set to TRUE
231 THEN
232 FND_MSG_PUB.Initialize;
233 END IF;
234
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 IF G_DEBUG='Y' THEN
238 AHL_DEBUG_PUB.ENABLE_DEBUG;
239 END IF;
240
241 -- If module type is JSP (or undefined), and it is not Delete PC operation
242 -- Then item_type_desc should be converted to item_type_code
243 -- Also nullify status_desc, association_type_desc, primary_flag_desc -- no scenario where these values will change through some user-action
244 IF ( p_module_type = 'JSP' OR p_module_type IS NULL ) AND
245 ( p_x_pc_header_rec.operation_flag <> AHL_PC_HEADER_PVT.G_DML_DELETE )
246 THEN
247 CONVERT_ITEM_TYPE_DESC_TO_CODE (p_x_pc_header_rec);
248 IF G_DEBUG='Y' THEN
249 AHL_DEBUG_PUB.debug('PCH -- PUB -- Item_Code='||p_x_pc_header_rec.PRODUCT_TYPE_CODE);
250 END IF;
251
252 p_x_pc_header_rec.STATUS_DESC := NULL;
253 p_x_pc_header_rec.ASSOCIATION_TYPE_DESC := NULL;
254 p_x_pc_header_rec.PRIMARY_FLAG_DESC := NULL;
255 END IF;
256
257 VALIDATE_HEADER (p_x_pc_header_rec);
258
259 -- Check Error Message stack.
260 x_msg_count := FND_MSG_PUB.count_msg;
261 IF x_msg_count > 0
262 THEN
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 -- Call PVT APIs
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268
269 IF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_CREATE)
270 THEN
271 IF G_DEBUG='Y' THEN
272 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling CREATE_PC_HEADER for Name='||p_x_pc_header_rec.NAME);
273 END IF;
274
275 AHL_PC_HEADER_PVT.CREATE_PC_HEADER
276 (
277 p_api_version => 1.0,
278 p_init_msg_list => FND_API.G_FALSE,
279 p_commit => FND_API.G_FALSE,
280 p_validation_level => p_validation_level,
281 p_x_pc_header_rec => p_x_pc_header_rec,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data
285 );
286 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_UPDATE)
287 THEN
288 IF G_DEBUG='Y' THEN
289 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling UPDATE_PC_HEADER for ID='||p_x_pc_header_rec.PC_HEADER_ID);
290 END IF;
291
292 AHL_PC_HEADER_PVT.UPDATE_PC_HEADER
293 (
294 p_api_version => 1.0,
295 p_init_msg_list => FND_API.G_FALSE,
296 p_commit => FND_API.G_FALSE,
297 p_validation_level => p_validation_level,
298 p_x_pc_header_rec => p_x_pc_header_rec,
299 x_return_status => x_return_status,
300 x_msg_count => x_msg_count,
301 x_msg_data => x_msg_data,
302 x_warning_msg_data => x_warning_msg_data
303 );
304 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_COPY)
305 THEN
306 IF G_DEBUG='Y' THEN
307 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);
308 END IF;
309
310 AHL_PC_HEADER_PVT.COPY_PC_HEADER
311 (
312 p_api_version => 1.0,
313 p_init_msg_list => FND_API.G_FALSE,
314 p_commit => FND_API.G_FALSE,
315 p_validation_level => p_validation_level,
316 p_x_pc_header_rec => p_x_pc_header_rec,
317 x_return_status => x_return_status,
318 x_msg_count => x_msg_count,
319 x_msg_data => x_msg_data
320 );
321 ELSIF (p_x_pc_header_rec.operation_flag = AHL_PC_HEADER_PVT.G_DML_DELETE)
322 THEN
323 IF G_DEBUG='Y' THEN
324 AHL_DEBUG_PUB.debug('PCH -- PUB -- Calling DELETE_PC_HEADER for ID='||p_x_pc_header_rec.PC_HEADER_ID);
325 END IF;
326
327 AHL_PC_HEADER_PVT.DELETE_PC_HEADER
328 (
329 p_api_version => 1.0,
330 p_init_msg_list => FND_API.G_FALSE,
331 p_commit => FND_API.G_FALSE,
332 p_validation_level => p_validation_level,
333 p_x_pc_header_rec => p_x_pc_header_rec,
334 x_return_status => x_return_status,
335 x_msg_count => x_msg_count,
336 x_msg_data => x_msg_data
337 );
338 END IF;
339
340 -- Check Error Message stack.
341 x_msg_count := FND_MSG_PUB.count_msg;
342 IF x_msg_count > 0
343 THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 -- Standard check for p_commit
348 IF FND_API.To_Boolean (p_commit)
349 THEN
350 COMMIT WORK;
351 END IF;
352
353 -- Standard call to get message count and if count is 1, get message info
354 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
355 p_data => x_msg_data,
356 p_encoded => fnd_api.g_false );
357
358 EXCEPTION
359 WHEN FND_API.G_EXC_ERROR THEN
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 Rollback to PROCESS_PC_HEADER_PUB;
362 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
363 p_data => x_msg_data,
364 p_encoded => fnd_api.g_false );
365
366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368 Rollback to PROCESS_PC_HEADER_PUB;
369 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
370 p_data => x_msg_data,
371 p_encoded => fnd_api.g_false );
372
373 WHEN OTHERS THEN
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375 Rollback to PROCESS_PC_HEADER_PUB;
376 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
377 THEN
378 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
379 p_procedure_name => 'PROCESS_PC_HEADER',
380 p_error_text => SUBSTR(SQLERRM,1,240) );
381 END IF;
382 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
383 p_data => x_msg_data,
384 p_encoded => fnd_api.g_false );
385
386 END PROCESS_PC_HEADER ;
387
388 END AHL_PC_HEADER_PUB;