[Home] [Help]
PACKAGE BODY: APPS.DPP_EXECUTIONDETAILS_PVT
Source
1 PACKAGE BODY DPP_EXECUTIONDETAILS_PVT AS
2 /* $Header: dppvexeb.pls 120.10 2008/04/10 06:36:36 sdasan noship $ */
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
44 l_return_status VARCHAR2(30);
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(4000);
47
48 l_exe_update_rec DPP_EXECUTIONDETAILS_PVT.DPP_EXE_UPDATE_REC_TYPE := p_EXE_UPDATE_rec;
49 l_status_Update_tbl DPP_EXECUTIONDETAILS_PVT.dpp_status_Update_tbl_type := p_status_Update_tbl;
50 l_update_count NUMBER;
51
52 l_process_code VARCHAR2(30);
53
54 BEGIN
55
56 -- Standard begin of API savepoint
57 SAVEPOINT Update_ExecutionDetails_PVT;
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call ( l_api_version,
60 p_api_version,
61 l_api_name,
62 G_PKG_NAME)
63 THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66 -- Initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.to_Boolean( p_init_msg_list )
68 THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 -- Debug Message
73 IF g_debug THEN
74 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
75 END IF;
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('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('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 IF g_debug THEN
214 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
215 END IF;
216 -- Standard call to get message count and if count is 1, get message info.
217 FND_MSG_PUB.Count_And_Get
218 (p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222 --Exception Handling
223 EXCEPTION
224 WHEN FND_API.G_EXC_ERROR THEN
225 ROLLBACK TO Update_ExecutionDetails_PVT;
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 -- Standard call to get message count and if count=1, get the message
228 FND_MSG_PUB.Count_And_Get (
229 p_encoded => FND_API.G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233 IF x_msg_count > 1 THEN
234 FOR I IN 1..x_msg_count LOOP
235 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
236 END LOOP;
237 END IF;
238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
239 ROLLBACK TO Update_ExecutionDetails_PVT;
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 -- Standard call to get message count and if count=1, get the message
242 FND_MSG_PUB.Count_And_Get (
243 p_encoded => FND_API.G_FALSE,
244 p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247 IF x_msg_count > 1 THEN
248 FOR I IN 1..x_msg_count LOOP
249 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
250 END LOOP;
251 END IF;
252 WHEN OTHERS THEN
253 ROLLBACK TO Update_ExecutionDetails_PVT;
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
256 fnd_message.set_token('ROUTINE', 'DPP_EXECUTIONDETAILS_PVT.Update_ExecutionDetails');
257 fnd_message.set_token('ERRNO', sqlcode);
258 fnd_message.set_token('REASON', sqlerrm);
259 fnd_msg_pub.add;
260 -- Standard call to get message count and if count=1, get the message
261 FND_MSG_PUB.Count_And_Get (
262 p_encoded => FND_API.G_FALSE,
263 p_count => x_msg_count,
264 p_data => x_msg_data
265 );
266 IF x_msg_count > 1 THEN
267 FOR I IN 1..x_msg_count LOOP
268 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
269 END LOOP;
270 END IF;
271 END Update_ExecutionDetails;
272
273 ---------------------------------------------------------------------
274 -- PROCEDURE
275 -- Update_ESB_InstanceID
276 --
277 -- PURPOSE
278 -- Update ESB Instance ID
279 --
280 -- PARAMETERS
281 --
282 -- NOTES
283 -- 1.
284 -- 2.
285 ----------------------------------------------------------------------
286
287 PROCEDURE Update_ESB_InstanceID(
288 p_api_version IN NUMBER
289 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
290 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
291 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
292 ,x_return_status OUT NOCOPY VARCHAR2
293 ,x_msg_count OUT NOCOPY NUMBER
294 ,x_msg_data OUT NOCOPY VARCHAR2
295 ,p_execution_detail_id IN NUMBER
296 ,p_esb_instance_id IN VARCHAR2
297 )
298 IS
299 l_api_name CONSTANT VARCHAR2(30) := 'Update_ESB_InstanceID';
300 l_api_version CONSTANT NUMBER := 1.0;
301 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
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 IF g_debug THEN
329 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
330 END IF;
331 -- Initialize API return status to sucess
332 l_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 --
335 -- API body
336 --
337 UPDATE dpp_execution_details
338 SET last_update_date = sysdate,
339 last_updated_by = fnd_global.user_id,
340 esb_instance_id = p_esb_instance_id,
341 object_version_number = object_version_number + 1
342 WHERE execution_detail_id = p_execution_detail_id;
343
344 l_update_count := SQL%ROWCOUNT;
345 IF SQL%ROWCOUNT = 0 THEN
346 l_return_status := FND_API.G_RET_STS_ERROR;
347 DPP_UTILITY_PVT.debug_message('Invalid Execution Detail ID: ' || p_Execution_Detail_ID);
348 END IF;
349
350 x_return_status := l_return_status;
351 -- Standard check for p_commit
352 IF FND_API.to_Boolean( p_commit )
353 THEN
354 COMMIT WORK;
355 END IF;
356 -- Debug Message
357 IF g_debug THEN
358 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
359 END IF;
360 -- Standard call to get message count and if count is 1, get message info.
361 FND_MSG_PUB.Count_And_Get
362 (p_count => x_msg_count,
363 p_data => x_msg_data
364 );
365
366 --Exception Handling
367 EXCEPTION
371 -- Standard call to get message count and if count=1, get the message
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO Update_ESB_InstanceID_PVT;
370 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MSG_PUB.Count_And_Get (
373 p_encoded => FND_API.G_FALSE,
374 p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377 IF x_msg_count > 1 THEN
378 FOR I IN 1..x_msg_count LOOP
379 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
380 END LOOP;
381 END IF;
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 ROLLBACK TO Update_ESB_InstanceID_PVT;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 -- Standard call to get message count and if count=1, get the message
386 FND_MSG_PUB.Count_And_Get (
387 p_encoded => FND_API.G_FALSE,
388 p_count => x_msg_count,
389 p_data => x_msg_data
390 );
391 IF x_msg_count > 1 THEN
392 FOR I IN 1..x_msg_count LOOP
393 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
394 END LOOP;
395 END IF;
396 WHEN OTHERS THEN
397 ROLLBACK TO Update_ESB_InstanceID_PVT;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
400 fnd_message.set_token('ROUTINE', 'DPP_EXECUTIONDETAILS_PVT.Update_ESB_InstanceID');
401 fnd_message.set_token('ERRNO', sqlcode);
402 fnd_message.set_token('REASON', sqlerrm);
403 fnd_msg_pub.add;
404 -- Standard call to get message count and if count=1, get the message
405 FND_MSG_PUB.Count_And_Get (
406 p_encoded => FND_API.G_FALSE,
407 p_count => x_msg_count,
408 p_data => x_msg_data
409 );
410 IF x_msg_count > 1 THEN
411 FOR I IN 1..x_msg_count LOOP
412 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
413 END LOOP;
414 END IF;
415 END Update_ESB_InstanceID;
416
417 END DPP_ExecutionDetails_PVT;