[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_PATH_POSITION_PUB
Source
1 PACKAGE BODY AHL_MC_PATH_POSITION_PUB AS
2 /* $Header: AHLPPOSB.pls 120.0 2008/02/20 23:32:29 jaramana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MC_PATH_POSITION_PUB';
5
6 ------------------------------------------------------------------------------------
7 -- Local API Declaration
8 ------------------------------------------------------------------------------------
9 PROCEDURE Convert_Path_Pos_Values_to_Id (
10 p_x_path_position_tbl IN OUT NOCOPY AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
11 );
12
13 -------------------------------------------------------------------------------------------
14 -- Start of Comments
15 -- Procedure name : Create_Position_ID
16 -- Type : Public
17 -- Function : Does user input validation and calls private API Create_Position_ID
18 -- Pre-reqs :
19 -- Parameters :
20 --
21 -- Create_Position_ID Parameters:
22 -- p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type Required
23 --
24 -- End of Comments
25 -------------------------------------------------------------------------------------------
26 PROCEDURE Create_Position_ID (
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
29 p_commit IN VARCHAR2 := FND_API.G_FALSE,
30 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 p_path_position_tbl IN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type,
32 p_position_ref_meaning IN VARCHAR2,
33 p_position_ref_code IN VARCHAR2,
34 x_position_id OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 ) IS
39
40 --
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_ID';
43 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
44
45 l_path_position_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type DEFAULT p_path_position_tbl;
46 --
47
48 BEGIN
49 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
50 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API');
51 END IF;
52
53 -- Standard start of API savepoint
54 SAVEPOINT Create_Position_ID_Pub;
55
56 -- Initialize Procedure return status to success
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 -- Standard call to check for call compatibility
60 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
61 l_api_name, G_PKG_NAME) THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 -- Initialize message list if p_init_msg_list is set to TRUE
66 IF FND_API.To_Boolean(p_init_msg_list) THEN
67 FND_MSG_PUB.Initialize;
68 END IF;
69
70 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
71 FND_LOG.string(FND_LOG.level_statement, l_full_name,
72 'p_path_position_tbl.COUNT = '|| p_path_position_tbl.COUNT);
73 END IF;
74
75 -- check for path position table
76 IF (p_path_position_tbl.COUNT < 1) THEN
77 -- input is NULL
78 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PATH_POS_TBL_NULL');
79 FND_MSG_PUB.ADD;
80 RAISE FND_API.G_EXC_ERROR;
81 END IF;
82
83 -- call Convert_Path_Pos_Values_to_Id
84 Convert_Path_Pos_Values_to_Id(l_path_position_tbl);
85
86 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
87 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'Calling private API AHL_MC_PATH_POSITION_PVT.Create_Position_ID.');
88 END IF;
89
90 -- call the private API
91 AHL_MC_PATH_POSITION_PVT.Create_Position_ID(
92 p_api_version => p_api_version,
93 p_init_msg_list => p_init_msg_list,
94 p_commit => FND_API.G_FALSE, -- Pass false and commit at the end if needed
95 p_validation_level => p_validation_level,
96 p_path_position_tbl => l_path_position_tbl,
97 p_position_ref_meaning => FND_API.G_MISS_CHAR, -- This Public API is not to be used for copying. Hence passing G_MISS
98 p_position_ref_code => FND_API.G_MISS_CHAR, -- This Public API is not to be used for copying. Hence passing G_MISS
99 x_position_id => x_position_id,
100 x_return_status => x_return_status,
101 x_msg_count => x_msg_count,
102 x_msg_data => x_msg_data
103 );
104
105 -- check for the return status
106 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
107 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
108 FND_LOG.string(FND_LOG.level_statement, l_full_name,
109 'Raising exception with x_return_status = ' || x_return_status);
110 END IF;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113
114 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
115 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'AHL_MC_PATH_POSITION_PVT.Create_Position_ID returned x_return_status as ' || x_return_status);
116 END IF;
117
118 -- Standard check of p_commit
119 IF FND_API.TO_BOOLEAN(p_commit) THEN
120 COMMIT WORK;
121 END IF;
122
123 -- Standard call to get message count and if count is 1, get message info
124 FND_MSG_PUB.Count_And_Get
125 ( p_count => x_msg_count,
126 p_data => x_msg_data,
127 p_encoded => FND_API.G_FALSE
128 );
129
130 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
131 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'End of the API');
132 END IF;
133
134 EXCEPTION
135 WHEN FND_API.G_EXC_ERROR THEN
136 Rollback to Create_Position_ID_Pub;
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
139 p_data => x_msg_data,
140 p_encoded => fnd_api.g_false);
141
142 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
143 Rollback to Create_Position_ID_Pub;
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
146 p_data => x_msg_data,
147 p_encoded => fnd_api.g_false);
148
149 WHEN OTHERS THEN
150 Rollback to Create_Position_ID_Pub;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
152 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
153 p_procedure_name => l_api_name,
154 p_error_text => SQLERRM);
155 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
156 p_data => x_msg_data,
157 p_encoded => FND_API.G_FALSE);
158
159 END Create_Position_ID;
160
161 -----------------------------------------------------------------------------------------------
162 -- Start of Comments
163 -- Procedure name : Convert_Path_Pos_Values_to_Id
164 -- Type : Local
165 -- Function : Does user input validation and value to id conversion
166 -- Pre-reqs :
167 -- Parameters :
168 --
169 -- Convert_Path_Pos_Values_to_Id Parameters:
170 -- p_x_path_position_tbl IN OUT AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
171 --
172 -- End of Comments
173 -----------------------------------------------------------------------------------------------
174 PROCEDURE Convert_Path_Pos_Values_to_Id (
175 p_x_path_position_tbl IN OUT NOCOPY AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
176 ) IS
177
178 CURSOR chk_mc_id_csr (p_mc_id NUMBER) IS
179 SELECT 'X'
180 FROM AHL_MC_HEADERS_B
181 WHERE mc_id = p_mc_id;
182
183 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2) IS
184 SELECT mc_id
185 FROM AHL_MC_HEADERS_B
186 WHERE name = p_mc_name;
187
188 CURSOR chk_mc_ver_no_csr (p_mc_id NUMBER, p_ver_no NUMBER) IS
189 SELECT 'X'
190 FROM AHL_MC_HEADERS_B
191 WHERE version_number = p_ver_no
192 AND mc_id = p_mc_id;
193
194 CURSOR chk_mc_revision_csr (p_mc_id NUMBER, p_revision VARCHAR2) IS
195 SELECT version_number
196 FROM AHL_MC_HEADERS_B
197 WHERE revision = p_revision
198 AND mc_id = p_mc_id;
199 --
200 l_api_name CONSTANT VARCHAR2(30) := 'Convert_Path_Pos_Values_to_Id';
201 l_full_name CONSTANT VARCHAR2(90) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
202
203 l_mc_id NUMBER;
204 l_ver_no NUMBER;
205 l_path_position_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type DEFAULT p_x_path_position_tbl;
206 l_dummy VARCHAR2(1);
207 l_validation_failed_flag VARCHAR2(1) := 'N';
208 --
209
210 BEGIN
211 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
212 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API');
213 END IF;
214
215 FOR i IN l_path_position_tbl.FIRST..l_path_position_tbl.LAST LOOP
216 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
217 FND_LOG.string(FND_LOG.level_statement, l_full_name,
218 'i = ' || i ||
219 ', l_path_position_tbl(i).mc_id = ' || l_path_position_tbl(i).mc_id||
220 ', l_path_position_tbl(i).mc_name = ' || l_path_position_tbl(i).mc_name||
221 ', l_path_position_tbl(i).mc_revision = ' || l_path_position_tbl(i).mc_revision||
222 ', l_path_position_tbl(i).version_number = ' || l_path_position_tbl(i).version_number||
223 ', l_path_position_tbl(i).position_key = ' || l_path_position_tbl(i).position_key);
224 END IF;
225
226 -- check for mc_id
227 IF (l_path_position_tbl(i).mc_id IS NULL) THEN
228 -- check for mc_name
229 IF (l_path_position_tbl(i).mc_name IS NULL) THEN
230 -- input is NULL
231 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RULE_MC_ID_NULL');
232 FND_MSG_PUB.ADD;
233 l_validation_failed_flag := 'Y';
234 ELSE
235 OPEN chk_mc_name_csr(l_path_position_tbl(i).mc_name);
236 FETCH chk_mc_name_csr INTO l_mc_id;
237
238 IF (chk_mc_name_csr%NOTFOUND) THEN
239 -- input is invalid
240 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RULE_MC_ID_NAME_INV');
241 FND_MESSAGE.Set_Token('MC_NAME', l_path_position_tbl(i).mc_name);
242 FND_MSG_PUB.ADD;
243 l_validation_failed_flag := 'Y';
244 END IF;
245
246 -- set the mc_id
247 l_path_position_tbl(i).mc_id := l_mc_id;
248 CLOSE chk_mc_name_csr;
249 END IF;
250 ELSE
251 -- check with mc_id
252 OPEN chk_mc_id_csr (l_path_position_tbl(i).mc_id);
253 FETCH chk_mc_id_csr INTO l_dummy;
254
255 IF (chk_mc_id_csr%NOTFOUND) THEN
256 -- input is invalid
257 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RULE_MC_ID_INV');
258 FND_MESSAGE.Set_Token('MC_ID', l_path_position_tbl(i).mc_id);
259 FND_MSG_PUB.ADD;
260 l_validation_failed_flag := 'Y';
261 END IF;
262
263 CLOSE chk_mc_id_csr;
264 END IF;
265
266 -- if mc_id is not null; i.e. mc_id has been derived/validated
267 -- check for version_number
268 IF (l_path_position_tbl(i).mc_id IS NOT NULL) THEN
269 IF (l_path_position_tbl(i).version_number IS NULL) THEN
270 -- check for revision
271 IF (l_path_position_tbl(i).mc_revision IS NOT NULL) THEN
272 OPEN chk_mc_revision_csr(l_path_position_tbl(i).mc_id, l_path_position_tbl(i).mc_revision);
273 FETCH chk_mc_revision_csr INTO l_ver_no;
274
275 IF (chk_mc_revision_csr%NOTFOUND) THEN
276 -- input is invalid
277 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RULE_MC_REV_INV');
278 FND_MESSAGE.Set_Token('MC_ID', l_path_position_tbl(i).mc_id);
279 FND_MESSAGE.Set_Token('MC_REV', l_path_position_tbl(i).mc_revision);
280 FND_MSG_PUB.ADD;
281 l_validation_failed_flag := 'Y';
282 END IF;
283
284 -- set the version_number
285 l_path_position_tbl(i).version_number := l_ver_no;
286 CLOSE chk_mc_revision_csr;
287 END IF;
288 ELSE
289 -- check with version_number
290 OPEN chk_mc_ver_no_csr (l_path_position_tbl(i).mc_id, l_path_position_tbl(i).version_number);
291 FETCH chk_mc_ver_no_csr INTO l_dummy;
292
293 IF (chk_mc_ver_no_csr%NOTFOUND) THEN
294 -- input is invalid
295 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RULE_MC_VER_NO_INV');
296 FND_MESSAGE.Set_Token('MC_ID', l_path_position_tbl(i).mc_id);
297 FND_MESSAGE.Set_Token('MC_VER_NO', l_path_position_tbl(i).version_number);
298 FND_MSG_PUB.ADD;
299 l_validation_failed_flag := 'Y';
300 END IF;
301
302 CLOSE chk_mc_ver_no_csr;
303 END IF;
304 END IF;
305
306 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
307 FND_LOG.string(FND_LOG.level_statement, l_full_name,
308 'i = '|| i ||
309 ', l_path_position_tbl(i).mc_id = ' || l_path_position_tbl(i).mc_id ||
310 ', l_path_position_tbl(i).mc_name = ' || l_path_position_tbl(i).mc_name ||
311 ', l_path_position_tbl(i).mc_revision = ' || l_path_position_tbl(i).mc_revision ||
312 ', l_path_position_tbl(i).version_number = ' || l_path_position_tbl(i).version_number ||
313 ', l_path_position_tbl(i).position_key = ' || l_path_position_tbl(i).position_key);
314 END IF;
315
316 -- raise the exception if some error occurred
317 IF (l_validation_failed_flag = 'Y') THEN
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320 END LOOP;
321
322 -- return changed record
323 p_x_path_position_tbl := l_path_position_tbl;
324
325 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
326 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'End of the API');
327 END IF;
328
329 END Convert_Path_Pos_Values_to_Id;
330
331 End AHL_MC_PATH_POSITION_PUB;