1 PACKAGE BODY AHL_LTP_MATERIALS_GRP AS
2 /* $Header: AHLGMTLB.pls 120.2 2010/02/25 10:03:45 skpathak noship $ */
3 ------------------------------------
4 -- Common constants and variables --
5 ------------------------------------
6 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7 l_log_statement NUMBER := fnd_log.level_statement;
8 l_log_procedure NUMBER := fnd_log.level_procedure;
9 l_log_error NUMBER := fnd_log.level_error;
10 l_log_unexpected NUMBER := fnd_log.level_unexpected;
11
12
13
14 ----------------------------------------
15 -- Start of Comments --
16 -- Procedure name : Update_mtl_resv_dates
17 -- Type : Public
18 -- Function : Update material requirement date and serial
19 -- reservation dates with WO scheduled start date
20 -- Pre-reqs :
21 -- Parameters :
22 --
23 -- Standard IN Parameters :
24 -- p_api_version IN NUMBER Required
25 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
26 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
27 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
28 --
29 -- Standard OUT Parameters :
30 -- x_return_status OUT VARCHAR2 Required
31 -- x_msg_count OUT NUMBER Required
32 -- x_msg_data OUT VARCHAR2 Required
33 --
34 -- Update_mtl_resv_dates Parameters:
35 -- p_wip_entity_id IN NUMBER Required
36 --
37 -- Version :
38 -- Initial Version 1.0
39 --
40 -- End of Comments.
41
42 PROCEDURE Update_mtl_resv_dates
43 (
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2,
46 p_commit IN VARCHAR2,
47 p_validation_level IN NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 p_wip_entity_id IN NUMBER
52 )
53 IS
54 -- Declare local variables
55 l_api_name CONSTANT VARCHAR2(30) := 'Update_mtl_resv_dates';
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_return_status VARCHAR2(1);
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2(2000);
60 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
61 i NUMBER;
62 l_scheduled_material_id NUMBER;
63 l_requested_date DATE;
64
65 -- To get all the scheduled_material_ids for the given wip_entity_id
66 -- for which requested date is not equal to WO scheduled start date
67 CURSOR get_scheduled_material_id (c_wip_entity_id IN NUMBER,
68 c_requested_date IN DATE) IS
69 SELECT asmt.scheduled_material_id
70 FROM ahl_workorders awo, ahl_schedule_materials asmt
71 WHERE awo.visit_task_id = asmt.visit_task_id
72 AND awo.wip_entity_id= c_wip_entity_id
73 AND asmt.requested_date <> trunc(c_requested_date)
74 AND asmt.status <> 'DELETED';
75
76 -- To fetch the Workorder schedule start date for the given wip entity id
77 CURSOR get_scheduled_start_date (c_wip_entity_id IN NUMBER) IS
78 SELECT scheduled_start_date
79 FROM wip_discrete_jobs
80 WHERE wip_entity_id= c_wip_entity_id;
81
82 BEGIN
83 -- Standard start of API savepoint
84 SAVEPOINT Update_mtl_resv_dates_grp;
85
86 -- Initialize return status to success before any code logic/validation
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 -- Standard call to check for call compatibility
90 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
91 THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END IF;
94
95 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
96 IF FND_API.TO_BOOLEAN(p_init_msg_list)
97 THEN
98 FND_MSG_PUB.INITIALIZE;
99 END IF;
100
101 -- Log API entry point
102 IF (l_log_procedure >= l_log_current_level)THEN
103 fnd_log.string
104 (
105 fnd_log.level_procedure,
106 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
107 'At the start of PL SQL procedure p_wip_entity_id = ' || p_wip_entity_id
108 );
109 END IF;
110 -- Get the scheduled start date for the given wip_entity_id into l_requested_date
111 OPEN get_scheduled_start_date (p_wip_entity_id);
112 FETCH get_scheduled_start_date INTO l_requested_date;
113 CLOSE get_scheduled_start_date;
114
115 -- IF the schedule start date corresponding the input wip entity id fetched above is null
116 -- then the wip entity id passed to this procedure is invalid
117 IF l_requested_date IS NULL THEN
118 Fnd_Message.SET_NAME('AHL','AHL_LTP_WIP_ENTITY_ID_INVLD');
119 Fnd_Msg_Pub.ADD;
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122
123 OPEN get_scheduled_material_id(p_wip_entity_id, l_requested_date);
124 i:=1;
125 -- Loop for all the scheduled material ids for the given wip_entity_id
126 -- for which material requested date is not equal to WO scheduled start date
127 -- since if these are equal then dates are already synchronized, so no need to update the dates
128 LOOP
129 FETCH get_scheduled_material_id INTO l_scheduled_material_id;
130 EXIT WHEN get_scheduled_material_id%NOTFOUND;
131
132 IF (l_log_statement >= l_log_current_level)THEN
133 fnd_log.string
134 (
135 fnd_log.level_statement, 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
136 'Inside the loop i= ' || i || ', scheduled_material_id = ' || l_scheduled_material_id
137 );
138 END IF;
139
140 -- This API updates all the reservation dates, if l_requested_date
141 -- and requested_date in AHL_SCHEDULE_MATERIALS table are different
142 AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
143 p_api_version => 1.0,
144 p_init_msg_list => FND_API.G_FALSE,
145 p_commit => FND_API.G_FALSE,
146 p_module_type => NULL,
147 x_return_status => l_return_status,
148 x_msg_count => x_msg_count,
149 x_msg_data => x_msg_data,
150 p_scheduled_material_id => l_scheduled_material_id,
151 p_requested_date => l_requested_date);
152
153 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
154 IF (l_log_statement >= l_log_current_level)THEN
155 fnd_log.string
156 (
157 fnd_log.level_statement, 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
158 'Returned success from AHL_RSV_RESERVATIONS_PVT.Update_Reservation'
159 );
160 END IF;
161 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
162 RAISE FND_API.G_EXC_ERROR;
163 ELSE
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165 END IF;
166
167 -- Now update AHL_SCHEDULE_MATERIALS table with requested_date as l_requested_date
168 UPDATE AHL_SCHEDULE_MATERIALS
169 SET requested_date = trunc(l_requested_date),
170 last_update_date = sysdate,
171 last_updated_by = fnd_global.user_id,
172 last_update_login = fnd_global.login_id
173 WHERE scheduled_material_id = l_scheduled_material_id;
174
175 i := i + 1;
176 END LOOP;
177 IF (l_log_statement >= l_log_current_level)THEN
178 fnd_log.string
179 (
180 fnd_log.level_statement, 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
181 'After end of the loop, total number of iteration to modify date = ' || (i-1)
182 );
183 END IF;
184 CLOSE get_scheduled_material_id;
185
186 -- End logging
187 IF (l_log_procedure >= l_log_current_level)THEN
188 fnd_log.string
189 (
190 fnd_log.level_procedure,
191 l_debug_module||'.end',
192 'At the end of PLSQL procedure'
193 );
194 END IF;
195
196 -- Check Error Message stack.
197 x_msg_count := FND_MSG_PUB.count_msg;
198 IF x_msg_count > 0 THEN
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201
202 -- Commit if p_commit = FND_API.G_TRUE
203 IF FND_API.TO_BOOLEAN(p_commit)
204 THEN
205 COMMIT WORK;
206 IF (l_log_statement >= l_log_current_level) THEN
207 fnd_log.string
208 (
209 fnd_log.level_statement,
210 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
211 'Committed'
212 );
213 END IF;
214 END IF;
215
216
217 -- Standard call to get message count and if count is 1, get message info
218 FND_MSG_PUB.count_and_get
219 (
220 p_count => x_msg_count,
221 p_data => x_msg_data,
222 p_encoded => FND_API.G_FALSE
223 );
224
225 EXCEPTION
226 WHEN FND_API.G_EXC_ERROR THEN
227 ROLLBACK TO Update_mtl_resv_dates_grp;
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 FND_MSG_PUB.count_and_get
230 (
231 p_count => x_msg_count,
232 p_data => x_msg_data,
233 p_encoded => FND_API.G_FALSE
234 );
235
236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
237 ROLLBACK TO Update_mtl_resv_dates_grp;
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
239 FND_MSG_PUB.count_and_get
240 (
241 p_count => x_msg_count,
242 p_data => x_msg_data,
243 p_encoded => FND_API.G_FALSE
244 );
245
246 WHEN OTHERS THEN
247 ROLLBACK TO Update_mtl_resv_dates_grp;
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 FND_MSG_PUB.add_exc_msg
252 (
253 p_pkg_name => G_PKG_NAME,
254 p_procedure_name => 'Update_mtl_resv_dates',
255 p_error_text => SUBSTR(SQLERRM,1,240)
256 );
257 END IF;
258 FND_MSG_PUB.count_and_get
259 (
260 p_count => x_msg_count,
261 p_data => x_msg_data,
262 p_encoded => FND_API.G_FALSE
263 );
264 END Update_mtl_resv_dates;
265
266
267 END AHL_LTP_MATERIALS_GRP;
[Home] [Help]