[Home] [Help]
PACKAGE BODY: APPS.DPP_EXECUTIONDETAILS_PVT
Source
1 PACKAGE BODY DPP_EXECUTIONDETAILS_PVT AS
2 /* $Header: dppvexeb.pls 120.10.12010000.2 2010/04/21 11:33:07 anbbalas ship $ */
3
4 -- Package name : DPP_EXECUTIONDETAILS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_EXECUTIONDETAILS_PVT';
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvexeb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- UUpdate_ExecutionDetails
17 --
18 -- PURPOSE
19 -- Update Execution Details
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27
28 PROCEDURE Update_ExecutionDetails(
29 p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
31 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
32 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,p_EXE_UPDATE_rec IN OUT NOCOPY DPP_EXE_UPDATE_REC_TYPE
37 ,p_status_Update_tbl IN OUT NOCOPY dpp_status_Update_tbl_type
38 )
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'Update_ExecutionDetails';
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
43 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_EXECUTIONDETAILS_PVT.UPDATE_EXECUTIONDETAILS';
44
45 l_return_status VARCHAR2(30);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(4000);
48
49 l_exe_update_rec DPP_EXECUTIONDETAILS_PVT.DPP_EXE_UPDATE_REC_TYPE := p_EXE_UPDATE_rec;
50 l_status_Update_tbl DPP_EXECUTIONDETAILS_PVT.dpp_status_Update_tbl_type := p_status_Update_tbl;
51 l_update_count NUMBER;
52
53 l_process_code VARCHAR2(30);
54
55 BEGIN
56
57 -- Standard begin of API savepoint
58 SAVEPOINT Update_ExecutionDetails_PVT;
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call ( l_api_version,
61 p_api_version,
62 l_api_name,
63 G_PKG_NAME)
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67 -- Initialize message list if p_init_msg_list is set to TRUE.
68 IF FND_API.to_Boolean( p_init_msg_list )
69 THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- Debug Message
74 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
75
76 -- Initialize API return status to sucess
77 l_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 --
80 -- API body
81 --
82 --Convert the execution status to upper case
83 l_exe_update_rec.execution_status := UPPER(l_exe_update_rec.execution_status);
84 BEGIN
85 SELECT process_code
86 INTO l_process_code
87 FROM dpp_execution_details
88 WHERE execution_detail_id = l_exe_update_rec.execution_detail_id;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, 'Invalid Execution Detail ID: ' || l_exe_update_rec.execution_detail_id);
92 RAISE FND_API.G_EXC_ERROR;
93 END;
94
95 UPDATE DPP_EXECUTION_DETAILS
96 SET execution_end_date = sysdate
97 ,execution_status = DECODE(execution_status,'WARNING', execution_status, l_exe_update_rec.execution_status)
98 ,last_update_date = sysdate
99 ,last_updated_by = l_exe_update_rec.Last_Updated_By
100 ,provider_process_id = l_exe_update_rec.Provider_Process_Id
101 ,provider_process_instance_id = l_exe_update_rec.Provider_Process_Instance_id
102 ,output_xml = DECODE(execution_status,'WARNING',NVL(output_xml, XMLType(l_exe_update_rec.Output_XML)),XMLType(l_exe_update_rec.Output_XML))
103 ,object_version_number = object_version_number + 1
104 WHERE transaction_header_id = l_exe_update_rec.transaction_header_id
105 AND execution_detail_id = l_exe_update_rec.Execution_Detail_ID;
106 l_update_count := SQL%ROWCOUNT;
107 IF SQL%ROWCOUNT = 0 THEN
108 l_return_status := FND_API.G_RET_STS_ERROR;
109 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Invalid Execution Detail ID: ' || l_exe_update_rec.Execution_Detail_ID);
110 END IF;
111
112 FOR i IN l_status_Update_tbl.FIRST..l_status_Update_tbl.LAST
113 LOOP
114 IF l_process_code = 'UPDTPO' THEN
115 UPDATE dpp_transaction_lines_all
116 SET UPDATE_PURCHASING_DOCS = l_status_Update_tbl(i).Update_Status
117 ,object_version_number = object_version_number + 1
118 ,last_update_date = sysdate
119 ,last_updated_by = l_exe_update_rec.Last_Updated_By
120 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
121 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
122
123 ELSIF l_process_code = 'NTFYPO' THEN
124 UPDATE dpp_transaction_lines_all
125 SET NOTIFY_PURCHASING_DOCS = l_status_Update_tbl(i).Update_Status
126 ,object_version_number = object_version_number + 1
127 ,last_update_date = sysdate
128 ,last_updated_by = l_exe_update_rec.Last_Updated_By
129 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
130 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
131
132 ELSIF l_process_code = 'INVC' THEN
133 UPDATE dpp_transaction_lines_all
134 SET UPDATE_INVENTORY_COSTING = l_status_Update_tbl(i).Update_Status
135 ,object_version_number = object_version_number + 1
136 ,last_update_date = sysdate
137 ,last_updated_by = l_exe_update_rec.Last_Updated_By
138 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
139 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
140
141 ELSIF l_process_code = 'UPDTLP' THEN
142 UPDATE dpp_transaction_lines_all
143 SET UPDATE_ITEM_LIST_PRICE = l_status_Update_tbl(i).Update_Status
144 ,object_version_number = object_version_number + 1
145 ,last_update_date = sysdate
146 ,last_updated_by = l_exe_update_rec.Last_Updated_By
147 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
148 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
149
150 ELSIF l_process_code = 'INPL' THEN
151 UPDATE dpp_transaction_lines_all
152 SET NOTIFY_INBOUND_PRICELIST = l_status_Update_tbl(i).Update_Status
153 ,object_version_number = object_version_number + 1
154 ,last_update_date = sysdate
155 ,last_updated_by = l_exe_update_rec.Last_Updated_By
156 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
157 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
158
159 ELSIF l_process_code = 'OUTPL' THEN
160 UPDATE dpp_transaction_lines_all
161 SET NOTIFY_OUTBOUND_PRICELIST = l_status_Update_tbl(i).Update_Status
162 ,object_version_number = object_version_number + 1
163 ,last_update_date = sysdate
164 ,last_updated_by = l_exe_update_rec.Last_Updated_By
165 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
166 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
167
168 ELSIF l_process_code = 'DSTRINVCL' THEN
169 UPDATE dpp_transaction_lines_all
170 SET supp_dist_claim_status = l_status_Update_tbl(i).Update_Status
171 ,object_version_number = object_version_number + 1
172 ,last_update_date = sysdate
173 ,last_updated_by = l_exe_update_rec.Last_Updated_By
174 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
175 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
176
177 ELSIF l_process_code = 'CUSTINVCL' THEN
178 UPDATE DPP_customer_claims_all
179 SET supplier_claim_created = l_status_Update_tbl(i).Update_Status
180 ,object_version_number = object_version_number + 1
181 ,last_update_date = sysdate
182 ,last_updated_by = l_exe_update_rec.Last_Updated_By
183 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
184 AND customer_inv_line_id = l_status_Update_tbl(i).transaction_line_id;
185
186 ELSIF l_process_code = 'CUSTCL' THEN
187 UPDATE DPP_customer_claims_all
188 SET customer_claim_created = l_status_Update_tbl(i).Update_Status
189 ,object_version_number = object_version_number + 1
190 ,last_update_date = sysdate
191 ,last_updated_by = l_exe_update_rec.Last_Updated_By
192 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
193 AND customer_inv_line_id = l_status_Update_tbl(i).transaction_line_id;
194
195 ELSIF l_process_code = 'PROMO' THEN
196 UPDATE dpp_transaction_lines_all
197 SET NOTIFY_PROMOTIONS_PRICELIST = l_status_Update_tbl(i).Update_Status
198 ,object_version_number = object_version_number + 1
199 ,last_update_date = sysdate
200 ,last_updated_by = l_exe_update_rec.Last_Updated_By
201 WHERE transaction_header_id = l_EXE_UPDATE_rec.Transaction_Header_ID
202 AND transaction_line_id = l_status_Update_tbl(i).transaction_line_id;
203 END IF;
204 END LOOP;
205
206 x_return_status := l_return_status;
207 -- Standard check for p_commit
208 IF FND_API.to_Boolean( p_commit )
209 THEN
210 COMMIT WORK;
211 END IF;
212 -- Debug Message
213 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
214
215 -- Standard call to get message count and if count is 1, get message info.
216 FND_MSG_PUB.Count_And_Get
217 (p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220
221 --Exception Handling
222 EXCEPTION
223 WHEN FND_API.G_EXC_ERROR THEN
224 ROLLBACK TO Update_ExecutionDetails_PVT;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 -- Standard call to get message count and if count=1, get the message
227 FND_MSG_PUB.Count_And_Get (
228 p_encoded => FND_API.G_FALSE,
229 p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232 IF x_msg_count > 1 THEN
233 FOR I IN 1..x_msg_count LOOP
234 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
235 END LOOP;
236 END IF;
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 ROLLBACK TO Update_ExecutionDetails_PVT;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 -- Standard call to get message count and if count=1, get the message
241 FND_MSG_PUB.Count_And_Get (
242 p_encoded => FND_API.G_FALSE,
243 p_count => x_msg_count,
244 p_data => x_msg_data
245 );
246 IF x_msg_count > 1 THEN
247 FOR I IN 1..x_msg_count LOOP
248 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
249 END LOOP;
250 END IF;
251 WHEN OTHERS THEN
252 ROLLBACK TO Update_ExecutionDetails_PVT;
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
255 fnd_message.set_token('ROUTINE', 'DPP_EXECUTIONDETAILS_PVT.Update_ExecutionDetails');
256 fnd_message.set_token('ERRNO', sqlcode);
257 fnd_message.set_token('REASON', sqlerrm);
258 fnd_msg_pub.add;
259 -- Standard call to get message count and if count=1, get the message
260 FND_MSG_PUB.Count_And_Get (
261 p_encoded => FND_API.G_FALSE,
262 p_count => x_msg_count,
263 p_data => x_msg_data
264 );
265 IF x_msg_count > 1 THEN
266 FOR I IN 1..x_msg_count LOOP
267 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
268 END LOOP;
269 END IF;
270 END Update_ExecutionDetails;
271
272 ---------------------------------------------------------------------
273 -- PROCEDURE
274 -- Update_ESB_InstanceID
275 --
276 -- PURPOSE
277 -- Update ESB Instance ID
278 --
279 -- PARAMETERS
280 --
281 -- NOTES
282 -- 1.
283 -- 2.
284 ----------------------------------------------------------------------
285
286 PROCEDURE Update_ESB_InstanceID(
287 p_api_version IN NUMBER
288 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
289 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
290 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
291 ,x_return_status OUT NOCOPY VARCHAR2
292 ,x_msg_count OUT NOCOPY NUMBER
293 ,x_msg_data OUT NOCOPY VARCHAR2
294 ,p_execution_detail_id IN NUMBER
295 ,p_esb_instance_id IN VARCHAR2
296 )
297 IS
298 l_api_name CONSTANT VARCHAR2(30) := 'Update_ESB_InstanceID';
299 l_api_version CONSTANT NUMBER := 1.0;
300 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
301 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_EXECUTIONDETAILS_PVT.UPDATE_ESB_INSTANCEID';
302
303 l_return_status VARCHAR2(30);
304 l_msg_count NUMBER;
305 l_msg_data VARCHAR2(4000);
306
307 l_update_count NUMBER;
308
309 BEGIN
310
311 -- Standard begin of API savepoint
312 SAVEPOINT Update_ESB_InstanceID_PVT;
313 -- Standard call to check for call compatibility.
314 IF NOT FND_API.Compatible_API_Call ( l_api_version,
315 p_api_version,
316 l_api_name,
317 G_PKG_NAME)
318 THEN
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 END IF;
321 -- Initialize message list if p_init_msg_list is set to TRUE.
322 IF FND_API.to_Boolean( p_init_msg_list )
323 THEN
324 FND_MSG_PUB.initialize;
325 END IF;
326
327 -- Debug Message
328 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
329
330 -- Initialize API return status to sucess
331 l_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 --
334 -- API body
335 --
336 UPDATE dpp_execution_details
337 SET last_update_date = sysdate,
338 last_updated_by = fnd_global.user_id,
339 esb_instance_id = p_esb_instance_id,
340 object_version_number = object_version_number + 1
341 WHERE execution_detail_id = p_execution_detail_id;
342
343 l_update_count := SQL%ROWCOUNT;
344 IF SQL%ROWCOUNT = 0 THEN
345 l_return_status := FND_API.G_RET_STS_ERROR;
346 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Invalid Execution Detail ID: ' || p_Execution_Detail_ID);
347 END IF;
348
349 x_return_status := l_return_status;
350 -- Standard check for p_commit
351 IF FND_API.to_Boolean( p_commit )
352 THEN
353 COMMIT WORK;
354 END IF;
355 -- Debug Message
356 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
357
358 -- Standard call to get message count and if count is 1, get message info.
359 FND_MSG_PUB.Count_And_Get
360 (p_count => x_msg_count,
361 p_data => x_msg_data
362 );
363
364 --Exception Handling
365 EXCEPTION
366 WHEN FND_API.G_EXC_ERROR THEN
367 ROLLBACK TO Update_ESB_InstanceID_PVT;
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 -- Standard call to get message count and if count=1, get the message
370 FND_MSG_PUB.Count_And_Get (
371 p_encoded => FND_API.G_FALSE,
372 p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375 IF x_msg_count > 1 THEN
376 FOR I IN 1..x_msg_count LOOP
377 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
378 END LOOP;
379 END IF;
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
381 ROLLBACK TO Update_ESB_InstanceID_PVT;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 -- Standard call to get message count and if count=1, get the message
384 FND_MSG_PUB.Count_And_Get (
385 p_encoded => FND_API.G_FALSE,
386 p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389 IF x_msg_count > 1 THEN
390 FOR I IN 1..x_msg_count LOOP
391 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
392 END LOOP;
393 END IF;
394 WHEN OTHERS THEN
398 fnd_message.set_token('ROUTINE', 'DPP_EXECUTIONDETAILS_PVT.Update_ESB_InstanceID');
395 ROLLBACK TO Update_ESB_InstanceID_PVT;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
399 fnd_message.set_token('ERRNO', sqlcode);
400 fnd_message.set_token('REASON', sqlerrm);
401 fnd_msg_pub.add;
402 -- Standard call to get message count and if count=1, get the message
403 FND_MSG_PUB.Count_And_Get (
404 p_encoded => FND_API.G_FALSE,
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408 IF x_msg_count > 1 THEN
409 FOR I IN 1..x_msg_count LOOP
410 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
411 END LOOP;
412 END IF;
413 END Update_ESB_InstanceID;
414
415 END DPP_ExecutionDetails_PVT;