1 PACKAGE BODY AHL_VWP_TASKS_PUB AS
2 /* $Header: AHLPTSKB.pls 120.0 2008/04/03 06:00:28 jaramana noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_TASKS_PUB';
5
6 -- Declare local functions and procedures
7 PROCEDURE Validate_And_Prepare_Params(
8 p_visit_id IN NUMBER,
9 p_visit_number IN NUMBER,
10 p_department_id IN NUMBER,
11 p_department_code IN VARCHAR2,
12 p_x_tasks_tbl IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Tbl_Type);
13
14
15 -------------------------------------------------------------------------------------------
16 -- Start of Comments
17 -- Procedure name : Create_Planned_Tasks
18 -- Type : Public
19 -- Function : Creates planned tasks and adds them to an existing visit.
20 -- Pre-reqs :
21 -- Parameters :
22 --
23 -- Create_Planned_Tasks Parameters:
24 -- p_visit_id IN NUMBER := null Not needed if p_visit_number is given
25 -- p_visit_number IN NUMBER := null Ignored if p_visit_id is given
26 -- p_department_id IN NUMBER := null Not needed if p_department_code is given
27 -- p_department_code IN VARCHAR2 := null Ignored if p_department_id is given
28 -- p_x_tasks_tbl IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Tbl_Type
29 -- UNIT_EFFECTIVITY_ID is Mandatory
30 -- ATTRIBUTE_CATEGORY is Optional
31 -- ATTRIBUTE1..ATTRIBUTE15 are Optional
32 -- All others input attributes are ignored
33 -- VISIT_TASK_ID has the return value: Id of the task created for the UE.
34 --
35 -- End of Comments
36 -------------------------------------------------------------------------------------------
37 PROCEDURE Create_Planned_Tasks (
38 p_api_version IN NUMBER,
39 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
40 p_commit IN VARCHAR2 := FND_API.G_FALSE,
41 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
42 p_visit_id IN NUMBER := null, -- Not needed if p_visit_number is given
43 p_visit_number IN NUMBER := null, -- Ignored if p_visit_id is given
44 p_department_id IN NUMBER := null, -- Not needed if p_department_code is given
45 p_department_code IN VARCHAR2 := null, -- Ignored if p_department_id is given
46 p_x_tasks_tbl IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Tbl_Type,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2
50 ) IS
51
52 --
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_api_name CONSTANT VARCHAR2(30) := 'Create_Planned_Tasks';
55 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
56
57 --
58
59 BEGIN
60 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
61 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API.' ||
62 ' p_visit_id = ' || p_visit_id ||
63 ', p_visit_number = ' || p_visit_number ||
64 ', p_department_id = ' || p_department_id ||
65 ', p_department_code = ' || p_department_code ||
66 ', p_x_tasks_tbl.COUNT = ' || p_x_tasks_tbl.COUNT);
67 END IF;
68
69 -- Standard start of API savepoint
70 SAVEPOINT Create_Planned_Tasks_Pub;
71
72 -- Initialize Procedure return status to success
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 -- Standard call to check for call compatibility
76 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
77 l_api_name, G_PKG_NAME) THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80
81 -- Initialize message list if p_init_msg_list is set to TRUE
82 IF FND_API.To_Boolean(p_init_msg_list) THEN
83 FND_MSG_PUB.Initialize;
84 END IF;
85
86 -- Validate the input and prepare for subsequent calls
87 -- If there are errors, an exception is raised.
88 Validate_And_Prepare_Params(p_visit_id => p_visit_id,
89 p_visit_number => p_visit_number,
90 p_department_id => p_department_id,
91 p_department_code => p_department_code,
92 p_x_tasks_tbl => p_x_tasks_tbl);
93
94 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
95 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'About to call AHL_VWP_TASKS_PVT.Create_PUP_Tasks.');
96 END IF;
97
98 AHL_VWP_TASKS_PVT.Create_PUP_Tasks(p_api_version => 1.0,
99 p_init_msg_list => Fnd_Api.g_false,
100 p_commit => Fnd_Api.g_false,
101 p_validation_level => Fnd_Api.g_valid_level_full,
102 p_module_type => 'API',
103 p_x_task_tbl => p_x_tasks_tbl,
104 x_return_status => x_return_status,
105 x_msg_count => x_msg_count,
106 x_msg_data => x_msg_data);
107
108 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
109 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'Returned from AHL_VWP_TASKS_PVT.Create_PUP_Tasks. x_return_status = ' || x_return_status);
110 END IF;
111
112 IF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
113 RAISE FND_API.G_EXC_ERROR;
114 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
119 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'For index ' || p_x_tasks_tbl.FIRST ||
120 ' with unit effectivity id ' || p_x_tasks_tbl(p_x_tasks_tbl.FIRST).UNIT_EFFECTIVITY_ID ||
121 ', Visit Task Id = ' || p_x_tasks_tbl(p_x_tasks_tbl.FIRST).VISIT_TASK_ID);
122 END IF;
123
124 -- Standard check of p_commit
125 IF FND_API.TO_BOOLEAN(p_commit) THEN
126 COMMIT WORK;
127 END IF;
128
129 -- Standard call to get message count and if count is 1, get message info
130 FND_MSG_PUB.Count_And_Get
131 ( p_count => x_msg_count,
132 p_data => x_msg_data,
133 p_encoded => FND_API.G_FALSE
134 );
135
136 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
137 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'End of the API');
138 END IF;
139
140 EXCEPTION
141 WHEN FND_API.G_EXC_ERROR THEN
142 Rollback to Create_Planned_Tasks_Pub;
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
145 p_data => x_msg_data,
146 p_encoded => fnd_api.g_false);
147
148 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
149 Rollback to Create_Planned_Tasks_Pub;
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
152 p_data => x_msg_data,
153 p_encoded => fnd_api.g_false);
154
155 WHEN OTHERS THEN
156 Rollback to Create_Planned_Tasks_Pub;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
159 p_procedure_name => l_api_name,
160 p_error_text => SQLERRM);
161 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
162 p_data => x_msg_data,
163 p_encoded => FND_API.G_FALSE);
164
165 END Create_Planned_Tasks;
166
167 -- This API validates the input params and raises an exception in case any are invalid
168 -- It also resets unwanted attributes so that only the attributes needed for creating
169 -- Planned tasks are retained.
170
171 PROCEDURE Validate_And_Prepare_Params(
172 p_visit_id IN NUMBER,
173 p_visit_number IN NUMBER,
174 p_department_id IN NUMBER,
175 p_department_code IN VARCHAR2,
176 p_x_tasks_tbl IN OUT NOCOPY AHL_VWP_RULES_PVT.Task_Tbl_Type
177 ) IS
178
179 l_api_name CONSTANT VARCHAR2(30) := 'Validate_And_Prepare_Params';
180 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
181 l_visit_id NUMBER;
182 l_dept_id NUMBER;
183 l_org_id NUMBER := NULL;
184 l_valid_flag BOOLEAN := true;
185 l_source_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
186 l_dest_rec AHL_VWP_RULES_PVT.Task_Rec_Type;
187 l_index NUMBER;
188 l_temp_num NUMBER;
189
190 CURSOR validate_visit_id_csr(c_visit_id IN NUMBER) IS
191 SELECT organization_id FROM ahl_visits_b
192 WHERE visit_id = c_visit_id
193 AND status_code in ('RELEASED', 'PLANNING', 'PARTIALLY RELEASED');
194
195 CURSOR validate_visit_number_csr(c_visit_number IN NUMBER) IS
196 SELECT visit_id, organization_id FROM ahl_visits_b
197 WHERE visit_number = c_visit_number
198 AND status_code in ('RELEASED', 'PLANNING', 'PARTIALLY RELEASED');
199
200 CURSOR validate_dept_id_csr(c_dept_id IN NUMBER, c_org_id IN NUMBER) IS
201 SELECT department_id FROM bom_departments
202 WHERE department_id = c_dept_id
203 AND organization_id = c_org_id;
204
205 CURSOR validate_dept_code_csr(c_dept_code IN VARCHAR2, c_org_id IN NUMBER) IS
206 SELECT department_id FROM bom_departments
207 WHERE department_code = c_dept_code
208 AND organization_id = c_org_id;
209
210 CURSOR check_group_mr_csr(c_ue_id IN NUMBER) IS
211 SELECT 1 from AHL_UE_RELATIONSHIPS
212 WHERE RELATED_UE_ID = c_ue_id;
213
214 BEGIN
215 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
216 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.begin', 'At the start of the API');
217 END IF;
218 IF (p_visit_id IS NULL and p_visit_number IS NULL) THEN
219 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_INVALID_VISIT_NUMBER');
220 FND_MSG_PUB.ADD;
221 l_valid_flag := false;
222 ELSE
223 IF (p_visit_id IS NOT NULL) THEN
224 -- Validate the visit_id and set l_visit_id
225 OPEN validate_visit_id_csr(p_visit_id);
226 FETCH validate_visit_id_csr INTO l_org_id;
227 IF(validate_visit_id_csr%NOTFOUND) THEN
228 FND_MESSAGE.Set_Name('AHL', 'AHL_VISIT_ID_INVALID'); --@@@@@
229 FND_MESSAGE.Set_Token('VISIT_ID', p_visit_id);
230 FND_MSG_PUB.ADD;
231 l_valid_flag := false;
232 ELSE
233 l_visit_id := p_visit_id;
234 END IF;
235 CLOSE validate_visit_id_csr;
236 ELSE
237 -- Derive the visit_id from the visit_number
238 OPEN validate_visit_number_csr(p_visit_number);
239 FETCH validate_visit_number_csr INTO l_visit_id, l_org_id;
240 IF(validate_visit_number_csr%NOTFOUND) THEN
241 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_INVALID_VISIT_NUMBER');
242 FND_MSG_PUB.ADD;
243 l_valid_flag := false;
244 END IF;
245 CLOSE validate_visit_number_csr;
246 END IF;
247 END IF;
248 IF(NOT l_valid_flag) THEN
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251
252 IF (p_department_id IS NOT NULL) THEN
253 IF (l_org_id IS NULL) THEN
254 -- Cannot add task since the visit does not have an org
255 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_VISIT_ORG_NOT_SET'); -- @@@@@
256 FND_MSG_PUB.ADD;
257 l_valid_flag := false;
258 ELSE
259 -- Validate the department_id and set l_dept_id
260 OPEN validate_dept_id_csr(p_department_id, l_org_id);
261 FETCH validate_dept_id_csr INTO l_dept_id;
262 IF(validate_dept_id_csr%NOTFOUND) THEN
263 FND_MESSAGE.Set_Name('AHL', 'AHL_LTP_DEPT_ID_NOT_EXIST');
264 FND_MSG_PUB.ADD;
265 l_valid_flag := false;
266 END IF;
267 CLOSE validate_dept_id_csr;
268 END IF;
269 ELSIF (p_department_code IS NOT NULL) THEN
270 -- Derive the department_id from the dept_code
271 OPEN validate_dept_code_csr(p_department_code, l_org_id);
272 FETCH validate_dept_code_csr INTO l_dept_id;
273 IF(validate_dept_code_csr%NOTFOUND) THEN
274 FND_MESSAGE.Set_Name('AHL', 'AHL_VWP_DEPT_CODE_INVALID'); --@@@@@
275 FND_MESSAGE.Set_Token('DEPT_CODE', p_department_code);
276 FND_MSG_PUB.ADD;
277 l_valid_flag := false;
278 END IF;
279 CLOSE validate_dept_code_csr;
280 END IF;
281 IF(NOT l_valid_flag) THEN
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 -- Validate and pre-process the tasks table
286 IF (p_x_tasks_tbl.COUNT < 1) THEN
287 -- input is NULL
288 FND_MESSAGE.Set_Name('AHL', 'AHL_TASKS_TBL_EMPTY'); --@@@@@
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_ERROR;
291 END IF;
292
293 l_index := p_x_tasks_tbl.FIRST;
294 WHILE (l_index <= p_x_tasks_tbl.LAST) LOOP
295 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
296 FND_LOG.string(FND_LOG.level_statement, l_full_name, 'l_index = ' || l_index ||
297 ', p_x_tasks_tbl(l_index).UNIT_EFFECTIVITY_ID = ' || p_x_tasks_tbl(l_index).UNIT_EFFECTIVITY_ID ||
298 ', p_x_tasks_tbl(l_index).DEPARTMENT_ID = ' || p_x_tasks_tbl(l_index).DEPARTMENT_ID ||
299 ', p_x_tasks_tbl(l_index).DEPT_NAME = ' || p_x_tasks_tbl(l_index).DEPT_NAME);
300 END IF;
301 -- Validate and copy only relevant fields to l_dest_rec
302 IF (p_x_tasks_tbl(l_index).UNIT_EFFECTIVITY_ID IS NULL) THEN
303 Fnd_Message.SET_NAME('AHL', 'AHL_VWP_NO_UNIT_EFFECTIVITY');
304 Fnd_Msg_Pub.ADD;
305 RAISE FND_API.G_EXC_ERROR;
306 ELSE
307 OPEN check_group_mr_csr(p_x_tasks_tbl(l_index).UNIT_EFFECTIVITY_ID);
308 FETCH check_group_mr_csr INTO l_temp_num;
309 IF(check_group_mr_csr%FOUND) THEN
310 FND_MESSAGE.Set_Name('AHL', 'AHL_UMP_NO_CHILD_ASSOC_VISIT');
311 FND_MSG_PUB.ADD;
312 CLOSE check_group_mr_csr;
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315 CLOSE check_group_mr_csr;
316
317 -- Remaining validations of this UE happen at AHL_VWP_PLAN_TASKS_PVT.Create_Planned_Task
318 l_dest_rec.UNIT_EFFECTIVITY_ID := p_x_tasks_tbl(l_index).UNIT_EFFECTIVITY_ID;
319 END IF;
320 IF (p_x_tasks_tbl(l_index).DEPARTMENT_ID IS NULL AND p_x_tasks_tbl(l_index).DEPT_NAME IS NULL) THEN
321 l_dest_rec.DEPARTMENT_ID := l_dept_id;
322 ELSE
323 l_dest_rec.DEPARTMENT_ID := p_x_tasks_tbl(l_index).DEPARTMENT_ID;
324 l_dest_rec.DEPT_NAME := p_x_tasks_tbl(l_index).DEPT_NAME;
325 END IF;
326 l_dest_rec.VISIT_ID := l_visit_id;
327 l_dest_rec.task_type_code := 'PLANNED';
328 l_dest_rec.ATTRIBUTE_CATEGORY := p_x_tasks_tbl(l_index).ATTRIBUTE_CATEGORY;
329 l_dest_rec.ATTRIBUTE1 := p_x_tasks_tbl(l_index).ATTRIBUTE1;
330 l_dest_rec.ATTRIBUTE2 := p_x_tasks_tbl(l_index).ATTRIBUTE2;
331 l_dest_rec.ATTRIBUTE3 := p_x_tasks_tbl(l_index).ATTRIBUTE3;
332 l_dest_rec.ATTRIBUTE4 := p_x_tasks_tbl(l_index).ATTRIBUTE4;
333 l_dest_rec.ATTRIBUTE5 := p_x_tasks_tbl(l_index).ATTRIBUTE5;
334 l_dest_rec.ATTRIBUTE6 := p_x_tasks_tbl(l_index).ATTRIBUTE6;
335 l_dest_rec.ATTRIBUTE7 := p_x_tasks_tbl(l_index).ATTRIBUTE7;
336 l_dest_rec.ATTRIBUTE8 := p_x_tasks_tbl(l_index).ATTRIBUTE8;
337 l_dest_rec.ATTRIBUTE9 := p_x_tasks_tbl(l_index).ATTRIBUTE9;
338 l_dest_rec.ATTRIBUTE10 := p_x_tasks_tbl(l_index).ATTRIBUTE10;
339 l_dest_rec.ATTRIBUTE11 := p_x_tasks_tbl(l_index).ATTRIBUTE11;
340 l_dest_rec.ATTRIBUTE12 := p_x_tasks_tbl(l_index).ATTRIBUTE12;
341 l_dest_rec.ATTRIBUTE13 := p_x_tasks_tbl(l_index).ATTRIBUTE13;
342 l_dest_rec.ATTRIBUTE14 := p_x_tasks_tbl(l_index).ATTRIBUTE14;
343 l_dest_rec.ATTRIBUTE15 := p_x_tasks_tbl(l_index).ATTRIBUTE15;
344 p_x_tasks_tbl(l_index) := l_dest_rec;
345 l_index := l_index + 1;
346 END LOOP;
347
348 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
349 FND_LOG.string(FND_LOG.level_procedure, l_full_name || '.end', 'Exiting API - Params validated.');
350 END IF;
351 END Validate_And_Prepare_Params;
352
353 End AHL_VWP_TASKS_PUB;
[Home] [Help]