[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_REQST_MATRL_PUB
Source
1 PACKAGE BODY AHL_LTP_REQST_MATRL_PUB AS
2 /* $Header: AHLPRMTB.pls 120.0.12020000.2 2012/12/10 15:48:51 prakkum ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_REQST_MATRL_PUB';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 --
7 -- PACKAGE
8 -- AHL_LTP_REQST_MATRL_PUB
9 --
10 -- PURPOSE
11 -- This Public API is used to schedule and unschedule materials requests
12 -- using Material Requirement Interface
13 --
14 -- NOTES
15 --
16 --
17 -- HISTORY
18 -- 23-Apr-2002 ssurapan Created.
19 -- 21-Jun-2011 surrkuma Added a wrapper procedure Process_Planned_Materials
20 --
21 -- Start of Comments --
22 -- Procedure name : Update_Planned_Materials
23 -- Type : Private
24 -- Function : This procedure Updates Planned materials information associated to scheduled
25 -- visit, which are defined at Route Operation and Disposition level
26 -- Pre-reqs :
27 -- Parameters :
28 --
29 -- Standard IN Parameters :
30 -- p_api_version IN NUMBER Required
31 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
32 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
33 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
34 -- Based on this flag, the API will set the default attributes.
35 -- p_module_type In VARCHAR2 Default NULL
36 -- This will be null.
37 -- Standard out Parameters :
38 -- x_return_status OUT VARCHAR2 Required
39 -- x_msg_count OUT NUMBER Required
40 -- x_msg_data OUT VARCHAR2 Required
41 --
42 -- Update_Planned_Materials Parameters :
43 -- p_planned_materials_tbl IN Planned_Materials_Tbl,Required
44 --
45 --
46 PROCEDURE Update_Planned_Materials (
47 p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
49 p_commit IN VARCHAR2 := FND_API.g_false,
50 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
51 p_module_type IN VARCHAR2 := 'JSP',
52 p_planned_Materials_tbl IN Planned_Materials_Tbl,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2)
56
57 IS
58
59
60 --Standard local variables
61 l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
62 l_api_version CONSTANT NUMBER := 1.0;
63 l_msg_data VARCHAR2(2000);
64 l_return_status VARCHAR2(1);
65 l_msg_count NUMBER;
66 --
67 l_commit VARCHAR2(10) := FND_API.g_false;
68 l_planned_materials_tbl planned_materials_tbl := p_planned_materials_tbl;
69 BEGIN
70
71 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
72 fnd_log.string
73 (
74 fnd_log.level_procedure,
75 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Update_Planned_Materials',
76 'At the start of PLSQL procedure'
77 );
78 END IF;
79 -- Standard start of API savepoint
80 SAVEPOINT Update_Planned_Materials;
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 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87 --
88 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
89 fnd_log.string
90 (
91 fnd_log.level_statement,
92 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
93 'Request for Update Material Number of Records : '|| l_planned_materials_tbl.COUNT
94 );
95
96 END IF;
97
98 IF l_planned_materials_tbl.COUNT > 0 THEN
99 --
100 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
101 fnd_log.string
102 (
103 fnd_log.level_procedure,
104 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
105 'Before calling ahl ltp reqst matrl pvt.Update Planned Materials'
106 );
107
108 END IF;
109
110 AHL_LTP_REQST_MATRL_PVT.Update_Planned_Materials (
111 p_api_version => l_api_version,
112 p_init_msg_list => p_init_msg_list,
113 p_commit => l_commit,
114 p_validation_level => p_validation_level,
115 p_planned_materials_tbl => p_planned_materials_tbl,
116 x_return_status => l_return_status,
117 x_msg_count => l_msg_count,
118 x_msg_data => l_msg_data);
119 END IF;
120
121 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
122 fnd_log.string
123 (
124 fnd_log.level_procedure,
125 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
126 'After calling ahl ltp reqst matrl pvt.Update Planned Materials, Return Status : '|| l_return_status
127 );
128 END IF;
129 -- Check Error Message stack.
130 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
131 l_msg_count := FND_MSG_PUB.count_msg;
132 IF l_msg_count > 0 THEN
133 RAISE FND_API.G_EXC_ERROR;
134 END IF;
135 END IF;
136
137 -- Standard check of p_commit
138 IF FND_API.TO_BOOLEAN(p_commit) THEN
139 COMMIT WORK;
140 END IF;
141
142 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
143 fnd_log.string
144 (
145 fnd_log.level_procedure,
146 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Update Planned Materials.end',
147 'At the end of PLSQL procedure'
148 );
149 END IF;
150 EXCEPTION
151 WHEN FND_API.G_EXC_ERROR THEN
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 ROLLBACK TO Update_Planned_Materials;
154 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
155 p_data => x_msg_data,
156 p_encoded => fnd_api.g_false);
157
158
159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161 ROLLBACK TO Update_Planned_Materials;
162 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
163 p_data => x_msg_data,
164 p_encoded => fnd_api.g_false);
165
166
167 WHEN OTHERS THEN
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 ROLLBACK TO Update_Planned_Materials;
170 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
171 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
172 p_procedure_name => 'Update_Planned_Materials',
173 p_error_text => SUBSTR(SQLERRM,1,500));
174 END IF;
175 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
176 p_data => x_msg_data,
177 p_encoded => fnd_api.g_false);
178
179 END Update_Planned_Materials;
180
181 -- Start of Comments --
182 -- Procedure name : Process_Planned_Materials
183 -- Type : Private
184 -- Function : This procedure Creates, Updates and Removes Planned materials information associated to scheduled
185 -- visit, which are defined at Route Operation and Disposition level
186 -- Pre-reqs :
187 -- Parameters :
188 --
189 -- Standard IN Parameters :
190 -- p_api_version IN NUMBER Required
191 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
192 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
193 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
194 -- Based on this flag, the API will set the default attributes.
195 -- p_module_type In VARCHAR2 Default NULL
196 -- This will be null.
197 -- Standard out Parameters :
198 -- x_return_status OUT VARCHAR2 Required
199 -- x_msg_count OUT NUMBER Required
200 -- x_msg_data OUT VARCHAR2 Required
201 --
202 -- Process_Planned_Materials Parameters :
203 --
204 --
205 PROCEDURE Process_Planned_Materials (
206 p_api_version IN NUMBER,
207 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
208 p_commit IN VARCHAR2 := FND_API.g_false,
209 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
210 p_visit_id IN NUMBER,
211 p_visit_task_id IN NUMBER := NULL,
212 p_org_id IN NUMBER := NULL,
213 p_start_date IN DATE := NULL,
214 p_visit_status IN VARCHAR2 := NULL,
215 p_ue_id IN NUMBER := NULL,
216 p_operation_flag IN VARCHAR2,
217 x_planned_order_flag OUT NOCOPY VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2)
221
222 IS
223
224
225 --Standard local variables
226 l_api_name CONSTANT VARCHAR2(30) := 'Process_Planned_Materials';
227 l_api_version CONSTANT NUMBER := 1.0;
228 l_msg_data VARCHAR2(2000);
229 l_return_status VARCHAR2(1);
230 l_planned_order_flag VARCHAR2(10);
231 l_msg_count NUMBER;
232 --
233 l_commit VARCHAR2(10) := FND_API.g_false;
234 BEGIN
235
236 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
237 fnd_log.string
238 (
239 fnd_log.level_procedure,
240 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Process_Planned_Materials',
241 'At the start of PLSQL procedure'
242 );
243 END IF;
244 -- Standard start of API savepoint
245 SAVEPOINT Process_Planned_Materials;
246 -- Initialize message list if p_init_msg_list is set to TRUE
247 IF FND_API.To_Boolean( p_init_msg_list) THEN
248 FND_MSG_PUB.Initialize;
249 END IF;
250 -- Initialize API return status to success
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 --
253 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
254 fnd_log.string
255 (
256 fnd_log.level_statement,
257 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
258 'Visit ID : '|| p_visit_id
259 );
260
261 END IF;
262
263 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
264 fnd_log.string
265 (
266 fnd_log.level_procedure,
267 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
268 'Before calling ahl ltp reqst matrl pvt.Process_Planned_Materials'
269 );
270
271 END IF;
272
273 AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials (
274 p_api_version => l_api_version,
275 p_init_msg_list => p_init_msg_list,
276 p_commit => l_commit,
277 p_validation_level => p_validation_level,
278 p_visit_id => p_visit_id,
279 p_visit_task_id => p_visit_task_id,
280 p_org_id => p_org_id,
281 p_start_date => p_start_date,
282 p_visit_status => p_visit_status,
283 p_ue_id => p_ue_id,
284 p_operation_flag => p_operation_flag,
285 x_planned_order_flag => l_planned_order_flag,
286 x_return_status => l_return_status,
287 x_msg_count => l_msg_count,
288 x_msg_data => l_msg_data);
289
290 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
291 fnd_log.string
292 (
293 fnd_log.level_procedure,
294 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
295 'After calling ahl ltp reqst matrl pvt.Process_Planned_Materials, Return Status : '|| l_return_status
296 );
297 END IF;
298 -- Check Error Message stack.
299 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
300 l_msg_count := FND_MSG_PUB.count_msg;
301 IF l_msg_count > 0 THEN
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304 END IF;
305
306 -- Standard check of p_commit
307 IF FND_API.TO_BOOLEAN(p_commit) THEN
308 COMMIT WORK;
309 END IF;
310
311 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
312 fnd_log.string
313 (
314 fnd_log.level_procedure,
315 'ahl.plsql.AHL_LTP_REQST_MATRL_PUB.Process_Planned_Materials.end',
316 'At the end of PLSQL procedure'
317 );
318 END IF;
319 EXCEPTION
320 WHEN FND_API.G_EXC_ERROR THEN
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 ROLLBACK TO Process_Planned_Materials;
323 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
324 p_data => x_msg_data,
325 p_encoded => fnd_api.g_false);
326
327
328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 ROLLBACK TO Process_Planned_Materials;
331 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
332 p_data => x_msg_data,
333 p_encoded => fnd_api.g_false);
334
335
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 ROLLBACK TO Process_Planned_Materials;
339 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
340 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
341 p_procedure_name => 'Process_Planned_Materials',
342 p_error_text => SUBSTR(SQLERRM,1,500));
343 END IF;
344 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
345 p_data => x_msg_data,
346 p_encoded => fnd_api.g_false);
347 END Process_Planned_Materials;
348
349 END AHL_LTP_REQST_MATRL_PUB;