[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_VISITS_PUB
Source
1 PACKAGE BODY AHL_VWP_VISITS_PUB AS
2 /* $Header: AHLPVSTB.pls 120.2.12020000.2 2012/12/10 16:34:11 prakkum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_VISITS_PUB';
5
6 -- Declare local functions and procedures
7 PROCEDURE Validate_And_Prepare_Params(
8 p_x_visit_rec IN OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type);
9
10
11 -------------------------------------------------------------------------------------------
12 -- Start of Comments
13 -- Procedure name : Create_Visit
14 -- Type : Public
15 -- Function : Creates a visit.
16 -- Pre-reqs :
17 -- Parameters :
18 --
19 -- Create_Visit Parameters:
20 -- p_x_visit_rec IN OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type
21 -- Description of some key attributes in p_x_visit_rec:
22 -- VISIT_NAME VARCHAR2(80) Mandatory
23 -- DESCRIPTION VARCHAR2(4000) Optional
24 -- ORGANIZATION_ID NUMBER Optional
25 -- ORG_NAME VARCHAR2(240) Optional
26 -- DEPARTMENT_ID NUMBER Optional
27 -- DEPT_NAME VARCHAR2(240) Optional
28 -- SERVICE_REQUEST_ID NUMBER Optional
29 -- SERVICE_REQUEST_NUMBER VARCHAR2(240) Optional
30 -- START_DATE DATE Mandatory for transit visits.
31 -- START_HOUR NUMBER Optional
32 -- START_MIN NUMBER Optional
33 -- PLAN_END_DATE DATE Optional
34 -- PLAN_END_HOUR NUMBER Optional
35 -- PLAN_END_MIN NUMBER Optional
36 -- VISIT_TYPE_CODE VARCHAR2(30) Optional
37 -- VISIT_TYPE_NAME VARCHAR2(80) Optional
38 -- UNIT_HEADER_ID NUMBER Optional
39 -- UNIT_NAME VARCHAR2(80) Optional
40 -- PROJ_TEMPLATE_ID NUMBER Optional
41 -- PROJ_TEMPLATE_NAME VARCHAR2(30) Optional
42 -- PRIORITY_CODE VARCHAR2(30) Optional
43 -- PRIORITY_VALUE VARCHAR2(80) Optional
44 -- UNIT_SCHEDULE_ID NUMBER Mandatory for transit visits.
45 -- VISIT_CREATE_TYPE VARCHAR2(30) Can be null, PRODUCTION_UNRELEASED or PRODUCTION_RELEASED
46 -- ATTRIBUTE_CATEGORY VARCHAR2(240) Optional
47 -- ATTRIBUTE1..ATTRIBUTE15 are Optional
48 -- Most other input attributes are ignored
49 -- VISIT_ID has the return value: Id of the visit created.
50 --
51 -- End of Comments
52 -------------------------------------------------------------------------------------------
53 PROCEDURE Create_Visit (
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
56 p_commit IN VARCHAR2 := FND_API.G_FALSE,
57 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
58 p_x_visit_rec IN OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2
62 ) IS
63
64 --
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_api_name CONSTANT VARCHAR2(30) := 'Create_Visit';
67 l_full_name CONSTANT VARCHAR2(99) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
68 l_Visit_tbl AHL_VWP_VISITS_PVT.Visit_Tbl_Type ;
69
70 --
71
72 BEGIN
73 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
74 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API.');
75 END IF;
76
77 -- Standard start of API savepoint
78 SAVEPOINT Create_Visit_Pub;
79
80 -- Initialize Procedure return status to success
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 -- Standard call to check for call compatibility
84 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
85 l_api_name, G_PKG_NAME) THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88
89 -- Initialize message list if p_init_msg_list is set to TRUE
90 IF FND_API.To_Boolean(p_init_msg_list) THEN
91 FND_MSG_PUB.Initialize;
92 END IF;
93
94 -- Validate the input and prepare for subsequent calls
95 -- If there are errors, an exception is raised.
96 Validate_And_Prepare_Params(p_x_visit_rec => p_x_visit_rec);
97
98 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
99 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'About to call AHL_VWP_VISITS_PVT.Create_Visit.');
100 END IF;
101 l_Visit_tbl(1) := p_x_visit_rec;
102
103 AHL_VWP_VISITS_PVT.Process_Visit(p_api_version => 1.0,
104 p_init_msg_list => Fnd_Api.g_false,
105 p_commit => Fnd_Api.g_false,
106 p_validation_level => Fnd_Api.g_valid_level_full,
107 p_module_type => 'API',
108 p_x_Visit_tbl => l_Visit_tbl,
109 x_return_status => x_return_status,
110 x_msg_count => x_msg_count,
111 x_msg_data => x_msg_data);
112
113 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
114 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'Returned from AHL_VWP_VISITS_PVT.Create_Visit. x_return_status = ' || x_return_status);
115 END IF;
116
117 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
118 RAISE FND_API.G_EXC_ERROR;
119 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122
123 p_x_visit_rec.VISIT_ID := l_Visit_tbl(1).VISIT_ID;
124
125 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
126 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'Visit created successfully with name ' ||
127 p_x_visit_rec.VISIT_NAME || ' and id ' || p_x_visit_rec.VISIT_ID);
128 END IF;
129
130 -- Standard check of p_commit
131 IF FND_API.TO_BOOLEAN(p_commit) THEN
132 COMMIT WORK;
133 END IF;
134
135 -- Standard call to get message count and if count is 1, get message info
136 FND_MSG_PUB.Count_And_Get
137 ( p_count => x_msg_count,
138 p_data => x_msg_data,
139 p_encoded => FND_API.G_FALSE
140 );
141
142 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
143 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'End of the API');
144 END IF;
145
146 EXCEPTION
147 WHEN FND_API.G_EXC_ERROR THEN
148 Rollback to Create_Visit_Pub;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
151 p_data => x_msg_data,
152 p_encoded => fnd_api.g_false);
153
154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 Rollback to Create_Visit_Pub;
156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
157 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
158 p_data => x_msg_data,
159 p_encoded => fnd_api.g_false);
160
161 WHEN OTHERS THEN
162 Rollback to Create_Visit_Pub;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
165 p_procedure_name => l_api_name,
166 p_error_text => SQLERRM);
167 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
168 p_data => x_msg_data,
169 p_encoded => FND_API.G_FALSE);
170
171 END Create_Visit;
172 --------------------------------------------------------------------------------------
173 -- This API validates the input params and raises an exception in case any are invalid
174 -- It also resets unwanted attributes so that only the attributes needed for creating
175 -- a visit are retained.
176 --
177 PROCEDURE Validate_And_Prepare_Params(
178 p_x_visit_rec IN OUT NOCOPY AHL_VWP_VISITS_PVT.Visit_Rec_Type
179 ) IS
180
181 l_api_name CONSTANT VARCHAR2(30) := 'Validate_And_Prepare_Params';
182 l_full_name CONSTANT VARCHAR2(99) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
183
184 l_return_status VARCHAR2(1);
185 l_msg_data VARCHAR2(2000);
186 l_valid_flag BOOLEAN := true;
187 l_organization_id NUMBER;
188 l_service_id NUMBER;
189 l_temp_code VARCHAR2(30);
190
191 CURSOR get_unit_name_csr(c_uc_header_id IN NUMBER) IS
192 SELECT name FROM ahl_unit_config_headers
193 WHERE unit_config_header_id = c_uc_header_id;
194
195 CURSOR get_unit_id_csr(c_uc_name IN VARCHAR2) IS
196 SELECT unit_config_header_id FROM ahl_unit_config_headers
197 WHERE name = c_uc_name;
198
199 CURSOR get_proj_template_name_csr(c_proj_template_id IN NUMBER) IS
200 SELECT name FROM pa_projects
201 WHERE project_id = c_proj_template_id
202 AND template_flag = 'Y';
203
204 BEGIN
205 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
206 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API');
207 END IF;
208
209 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
210 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'Values of important attributes in p_x_visit_rec: ' ||
211 'operation_flag = ' || p_x_visit_rec.operation_flag ||
212 ', organization_id = ' || p_x_visit_rec.organization_id ||
213 ', department_id = ' || p_x_visit_rec.department_id ||
214 ', service_request_id = ' || p_x_visit_rec.service_request_id ||
215 ', visit_type_code = ' || p_x_visit_rec.visit_type_code ||
216 ', unit_header_id = ' || p_x_visit_rec.unit_header_id ||
217 ', unit_name = ' || p_x_visit_rec.unit_name ||
218 ', proj_template_id = ' || p_x_visit_rec.proj_template_id ||
219 ', priority_code = ' || p_x_visit_rec.priority_code);
220 END IF;
221
222 -- Nullify unwanted/system defaulted attributes
223 p_x_visit_rec.VISIT_ID := null;
224 p_x_visit_rec.VISIT_NUMBER := null;
225 p_x_visit_rec.OBJECT_VERSION_NUMBER := null;
226 p_x_visit_rec.LAST_UPDATE_DATE := null;
227 p_x_visit_rec.LAST_UPDATED_BY := null;
228 p_x_visit_rec.CREATION_DATE := null;
229 p_x_visit_rec.CREATED_BY := null;
230 p_x_visit_rec.LAST_UPDATE_LOGIN := null;
231 p_x_visit_rec.SPACE_CATEGORY_CODE := null;
232 p_x_visit_rec.SPACE_CATEGORY_NAME := null;
233 p_x_visit_rec.END_DATE := null;
234 p_x_visit_rec.DUE_BY_DATE := null;
235 p_x_visit_rec.STATUS_CODE := null;
236 p_x_visit_rec.STATUS_NAME := null;
237 p_x_visit_rec.SIMULATION_PLAN_ID := null;
238 p_x_visit_rec.SIMULATION_PLAN_NAME := null;
239 p_x_visit_rec.ASSO_PRIMARY_VISIT_ID := null;
240 p_x_visit_rec.ITEM_INSTANCE_ID := null;
241 p_x_visit_rec.SERIAL_NUMBER := null;
242 p_x_visit_rec.INVENTORY_ITEM_ID := null;
243 p_x_visit_rec.ITEM_ORGANIZATION_ID := null;
244 p_x_visit_rec.ITEM_NAME := null;
245 p_x_visit_rec.SIMULATION_DELETE_FLAG := null;
246 p_x_visit_rec.TEMPLATE_FLAG := null;
247 p_x_visit_rec.OUT_OF_SYNC_FLAG := null;
248 p_x_visit_rec.PROJECT_FLAG := null;
249 p_x_visit_rec.PROJECT_FLAG_CODE := null;
250 p_x_visit_rec.PROJECT_ID := null;
251 p_x_visit_rec.PROJECT_NUMBER := null;
252 p_x_visit_rec.DURATION := null;
253 p_x_visit_rec.FLIGHT_NUMBER := null;
254
255 -- Ensure that the Operation flag is valid.
256 IF p_x_visit_rec.operation_flag IS NULL THEN
257 p_x_visit_rec.operation_flag := 'I';
258 END IF;
259 IF (p_x_visit_rec.operation_flag <> 'I' AND p_x_visit_rec.operation_flag <> 'i') THEN
260 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_INVALID_DML_REC');
261 FND_MESSAGE.Set_Token('FIELD', p_x_visit_rec.operation_flag);
262 FND_MSG_PUB.ADD;
263 l_valid_flag := false;
264 END IF;
265
266 -- VISIT_NAME mandatory validation done in PVT package methods
267
268 -- Organization (ORGANIZATION_ID, ORG_NAME)
269 -- If organization_id is passed, reset org_name and validate organization_id
270 -- If only org_name is passed, the validation and value to id conversion are done in PVT package methods
271 IF (p_x_visit_rec.organization_id IS NOT NULL) THEN
272 p_x_visit_rec.org_name := NULL;
273 AHL_VWP_RULES_PVT.Check_Org_Name_Or_Id
274 (p_organization_id => p_x_visit_rec.organization_id,
275 p_org_name => null,
276 x_organization_id => l_organization_id,
277 x_return_status => l_return_status,
278 x_error_msg_code => l_msg_data);
279
280 IF (NVL(l_return_status,'x') <> 'S') THEN
281 Fnd_Message.SET_NAME('AHL', 'AHL_APPR_ORG_NT_EXISTS');
282 FND_MESSAGE.Set_Token('ORGID', p_x_visit_rec.organization_id);
283 Fnd_Msg_Pub.ADD;
284 l_valid_flag := false;
285 END IF;
286 END IF;
287
288 -- Department validation done in PVT package methods
289
290 -- Service Request (SERVICE_REQUEST_ID, SERVICE_REQUEST_NUMBER)
291 -- If service_request_id is passed, reset service_request_number and validate service_request_id
292 -- If only service_request_number is passed, the validation and value to id conversion are done in PVT package methods
293 IF (p_x_visit_rec.service_request_id IS NOT NULL) THEN
294 p_x_visit_rec.service_request_number := NULL;
295 AHL_VWP_RULES_PVT.Check_SR_Request_Number_Or_Id
296 (p_service_id => p_x_visit_rec.service_request_id,
297 p_service_number => null,
298 x_service_id => l_service_id,
299 x_return_status => l_return_status,
300 x_error_msg_code => l_msg_data);
301
302 IF (NVL(l_return_status,'x') <> 'S') THEN
303 Fnd_Message.SET_NAME('AHL', 'AHL_VWP_SERVICE_REQ_NOT_EXISTS');
304 Fnd_Msg_Pub.ADD;
305 l_valid_flag := false;
306 END IF;
307 END IF;
308
309 -- Visit Type (VISIT_TYPE_CODE, VISIT_TYPE_NAME)
310 -- If visit_type_code is passed, reset visit_type_name and validate visit_type_code
311 -- If only visit_type_name is passed, the validation and value to id conversion are done in PVT package methods
312 IF (p_x_visit_rec.visit_type_code IS NOT NULL) THEN
313 p_x_visit_rec.visit_type_name := NULL;
314 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id
315 (p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
316 p_lookup_code => p_x_visit_rec.visit_type_code,
317 p_meaning => null,
318 p_check_id_flag => 'Y',
319 x_lookup_code => l_temp_code,
320 x_return_status => l_return_status);
321
322 IF (NVL(l_return_status,'x') <> 'S') THEN
323 Fnd_Message.SET_NAME('AHL', 'AHL_VWP_TYPE_CODE_NOT_EXISTS');
324 Fnd_Msg_Pub.ADD;
325 l_valid_flag := false;
326 END IF;
327 END IF;
328
329 -- Unit (UNIT_HEADER_ID, UNIT_NAME)
330 IF (p_x_visit_rec.unit_header_id IS NOT NULL) THEN
331 -- Use unit_header_id to populate unit_name since the PVT package methods need it
332 OPEN get_unit_name_csr(c_uc_header_id => p_x_visit_rec.unit_header_id);
333 FETCH get_unit_name_csr INTO p_x_visit_rec.unit_name;
334 --SKPATHAK :: Bug 8216902 :: :: Validation for unit_header_id added
335 IF get_unit_name_csr%NOTFOUND THEN
336 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
337 fnd_log.string(FND_LOG.level_statement, l_full_name, 'Unit does not exist..');
338 END IF;
339 Fnd_Message.Set_Name('AHL','AHL_UC_API_PARAMETER_INVALID');
340 Fnd_Message.Set_Token('NAME', 'UNIT_HEADER_ID');
341 Fnd_Message.Set_Token('VALUE', p_x_visit_rec.unit_header_id);
342 Fnd_Msg_Pub.ADD;
343 CLOSE get_unit_name_csr;
344 RAISE Fnd_Api.G_EXC_ERROR;
345 END IF;
346 CLOSE get_unit_name_csr;
347
348 ELSIF (p_x_visit_rec.unit_name IS NOT NULL) THEN
349 -- Use unit_name to populate the unit_header_id
350 OPEN get_unit_id_csr(c_uc_name => p_x_visit_rec.unit_name);
351 FETCH get_unit_id_csr INTO p_x_visit_rec.unit_header_id;
352 CLOSE get_unit_id_csr;
353 END IF;
354
355 -- Project Template (PROJ_TEMPLATE_ID, PROJ_TEMPLATE_NAME)
356 IF (p_x_visit_rec.proj_template_id IS NOT NULL) THEN
357 -- Use proj_template_id to populate proj_template_name since the PVT package methods need it
358 OPEN get_proj_template_name_csr(c_proj_template_id => p_x_visit_rec.proj_template_id);
359 FETCH get_proj_template_name_csr INTO p_x_visit_rec.proj_template_name;
360 CLOSE get_proj_template_name_csr;
361 END IF;
362
363 -- Priority (PRIORITY_CODE, PRIORITY_VALUE)
364 -- If priority_code is passed, reset priority_value and validate priority_code
365 -- If only priority_value is passed, the validation and value to id conversion are done in PVT package methods
366 IF (p_x_visit_rec.priority_code IS NOT NULL) THEN
367 p_x_visit_rec.priority_value := NULL;
368 AHL_VWP_RULES_PVT.Check_Lookup_Name_Or_Id
369 (p_lookup_type => 'AHL_VWP_VISIT_PRIORITY',
370 p_lookup_code => p_x_visit_rec.priority_code,
371 p_meaning => null,
372 p_check_id_flag => 'Y',
373 x_lookup_code => l_temp_code,
374 x_return_status => l_return_status);
375
376 IF (NVL(l_return_status,'x') <> 'S') THEN
377 Fnd_Message.SET_NAME('AHL', 'AHL_VWP_PRI_NOT_EXISTS');
378 Fnd_Msg_Pub.ADD;
379 l_valid_flag := false;
380 END IF;
381 END IF;
382
383 IF(NOT l_valid_flag) THEN
384 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
385 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'Faced validation errors. Exiting with execution exception.');
386 END IF;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389
390 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
391 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'Exiting API - Params validated.');
392 END IF;
393 END Validate_And_Prepare_Params;
394
395 End AHL_VWP_VISITS_PUB;