[Home] [Help]
PACKAGE BODY: APPS.DPP_BUSINESSEVENTS_PVT
Source
1 PACKAGE BODY DPP_BUSINESSEVENTS_PVT AS
2 /* $Header: dppvbevb.pls 120.39.12010000.2 2008/11/11 11:27:39 rvkondur ship $ */
3
4 -- Package name : DPP_BUSINESSEVENTS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_BUSINESSEVENTS_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(12) := 'dppvbevb.pls';
13 ---------------------------------------------------------------------
14 -- PROCEDURE
15 -- Raise_Workflow_Event
16 --
17 -- PURPOSE
18 -- Raise a Workflow event
19 --
20 -- PARAMETERS
21 --
22 -- NOTES
23 -- 1.
24 -- 2.
25 ----------------------------------------------------------------------
26 PROCEDURE Raise_Workflow_Event(
27 x_return_status OUT NOCOPY VARCHAR2
28 ,x_msg_count OUT NOCOPY NUMBER
29 ,x_msg_data OUT NOCOPY VARCHAR2
30
31 ,p_txn_hdr_id IN NUMBER
32 ,p_process_code IN VARCHAR2
33 ,p_input_xml IN CLOB
34 ,p_row_count IN NUMBER
35 ,p_exe_dtl_id IN NUMBER
36 );
37 ---------------------------------------------------------------------
38 -- PROCEDURE
39 -- Raise_Business_Event
40 --
41 -- PURPOSE
42 -- Raise Business Event.
43 --
44 -- PARAMETERS
45 --
46 -- NOTES
47 -- 1.
48 -- 2.
49 ----------------------------------------------------------------------
50
51 PROCEDURE Raise_Business_Event(
52 p_api_version IN NUMBER
53 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
54 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
55 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
56 ,x_return_status OUT NOCOPY VARCHAR2
57 ,x_msg_count OUT NOCOPY NUMBER
58 ,x_msg_data OUT NOCOPY VARCHAR2
59
60 ,p_txn_hdr_rec IN dpp_txn_hdr_rec_type
61 ,p_txn_line_id IN dpp_txn_line_tbl_type
62 )
63 IS
64 l_api_version CONSTANT NUMBER := 1.0;
65 l_api_name CONSTANT VARCHAR2(30) := 'Raise_Business_Event';
66 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
67 l_return_status VARCHAR2(30);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(4000);
70
71 l_input_xml CLOB;
72 l_queryCtx dbms_xmlquery.ctxType;
73 l_table_count NUMBER;
74 l_row_count NUMBER := 0;
75 l_exe_dtl_id NUMBER;
76 l_user_id NUMBER := FND_GLOBAL.USER_ID;
77 l_user_name VARCHAR2(150);
78 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
79 l_claim_type_flag VARCHAR2(240);
80 l_status_code CONSTANT VARCHAR2(20) := '''PENDING_CLOSE''';
81 l_claim_hdr_amt NUMBER;
82 l_cost_adj_acct NUMBER;
83 dtl_price_change VARCHAR2(50);
84 sup_trd_prf_price_change VARCHAR2(50);
85 dtla_price_change VARCHAR2(50);
86 l_price_change_flag VARCHAR2(20);
87
88 BEGIN
89 --------------------- initialize -----------------------
90 SAVEPOINT Raise_Business_Event;
91
92 IF NOT FND_API.Compatible_API_Call (
93 l_api_version,
94 p_api_version,
95 l_api_name,
96 G_PKG_NAME)
97 THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101 IF FND_API.to_Boolean( p_init_msg_list )
102 THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 -- Debug Message
107 IF g_debug THEN
108 DPP_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'start');
109 END IF;
110
111 -- Initialize API return status to sucess
112 x_return_status := FND_API.g_ret_sts_success;
113
114 --Get a unique value for the execution detail id
115 BEGIN
116 SELECT DPP_EXECUTION_DETAIL_ID_SEQ.nextval
117 INTO l_exe_dtl_id
118 FROM dual;
119
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
123 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
124 fnd_message.set_token('ERRNO', sqlcode);
125 fnd_message.set_token('REASON', 'EXECUTION DETAIL ID NOT FOUND');
126 FND_MSG_PUB.add;
127 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
128 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
129 END IF;
130 RAISE FND_API.g_exc_error;
131 WHEN OTHERS THEN
132 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
133 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
134 fnd_message.set_token('ERRNO', sqlcode);
135 fnd_message.set_token('REASON', sqlerrm);
136 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
137 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_EXE_DET_ID');
138 fnd_message.set_token('SEQ_NAME', 'DPP_EXECUTION_DETAIL_ID_SEQ');
139 FND_MSG_PUB.add;
140 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
141 END IF;
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END;
144 IF g_debug THEN
145 DPP_UTILITY_PVT.debug_message('Execution Detail ID : ' || l_exe_dtl_id ||' For Transaction ID :'||p_txn_hdr_rec.Transaction_Header_ID);
146 DPP_UTILITY_PVT.debug_message('Process Code : ' || p_txn_hdr_rec.process_code);
147 END IF;
148
149 --Get the user name corresponding to the user id
150 BEGIN
151 SELECT user_name
152 INTO l_user_name
153 FROM fnd_user
154 WHERE user_id = l_user_id ;
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
159 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
160 fnd_message.set_token('ERRNO', sqlcode);
161 fnd_message.set_token('REASON', 'INVALID USER');
162 FND_MSG_PUB.add;
163 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
164 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
165 END IF;
166 RAISE FND_API.g_exc_error;
167 WHEN OTHERS THEN
168 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
169 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
170 fnd_message.set_token('ERRNO', sqlcode);
171 fnd_message.set_token('REASON', sqlerrm);
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END;
174
175 l_user_name := ''''||l_user_name||'''';
176
177 IF p_txn_hdr_rec.process_code = 'DSTRINVCL'
178 OR p_txn_hdr_rec.process_code = 'CUSTINVCL'
179 OR p_txn_hdr_rec.process_code = 'CUSTCL' THEN
180
181 IF p_txn_hdr_rec.claim_type_flag IS NULL THEN
182 --No claim type flag has been sent
183 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_CLAIM_TYPE');
184 FND_MSG_PUB.add;
185 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
186 RAISE FND_API.g_exc_error;
187 ELSE
188 --Concatinate the claim flag with necessary quotes to generate xml data
189 l_claim_type_flag := ''''||p_txn_hdr_rec.claim_type_flag||'''';
190 IF g_debug THEN
191 DPP_UTILITY_PVT.debug_message('Claim Type Flag : ' || l_claim_type_flag);
192 END IF;
193 END IF;
194 --Delete the existing rows from the DPP_TRANSACTION_LINES_GT table
195 DELETE FROM DPP_TRANSACTION_LINES_GT;
196 --Check if any line id has been passed and insert into the table DPP_TRANSACTION_LINES_GT
197 l_table_count := p_txn_line_id.COUNT;
198 --Changed the condition to check the claim source instead of the table count since array cannot be NULL from UI
199 IF p_txn_hdr_rec.claim_creation_source <> 'EXEDTLS' THEN
200 IF l_table_count > 0 THEN
201 FOR i IN p_txn_line_id.FIRST..p_txn_line_id.LAST LOOP
202 BEGIN
203 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
204 transaction_line_id
205 )
206 VALUES(p_txn_hdr_rec.Transaction_Header_ID,
207 p_txn_line_id(i)
208 );
209 EXCEPTION
210 WHEN OTHERS THEN
211 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
212 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
213 fnd_message.set_token('ERRNO', sqlcode);
214 fnd_message.set_token('REASON', sqlerrm);
215 FND_MSG_PUB.add;
216 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
217 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
218 END IF;
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END;
221 END LOOP;
222 ELSE
223 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
224 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
225 fnd_message.set_token('ERRNO', sqlcode);
226 fnd_message.set_token('REASON', 'Transaction Line Id is required for the API');
227 FND_MSG_PUB.add;
228 RAISE FND_API.G_EXC_ERROR;
229 END IF; --l_table_count
230 ELSE
231 IF p_txn_hdr_rec.claim_type_flag = 'SUPP_DSTR_CL' THEN
232 BEGIN
233 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
234 transaction_line_id)
235 SELECT transaction_header_id,
236 transaction_line_id
237 FROM dpp_transaction_lines_all
238 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
239 EXCEPTION
240 WHEN OTHERS THEN
241 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
242 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
243 fnd_message.set_token('ERRNO', sqlcode);
244 fnd_message.set_token('REASON', sqlerrm);
245 FND_MSG_PUB.add;
246 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
247 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
248 END IF;
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END;
251 ELSE
252 BEGIN
253 INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
254 transaction_line_id)
255 SELECT transaction_header_id,
256 customer_inv_line_id
257 FROM dpp_customer_claims_all
258 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
259 EXCEPTION
260 WHEN OTHERS THEN
261 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
262 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
263 fnd_message.set_token('ERRNO', sqlcode);
264 fnd_message.set_token('REASON', sqlerrm);
265 FND_MSG_PUB.add;
266 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
267 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
268 END IF;
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END;
271 END IF;
272 END IF;
273
274 END IF;
275
276 IF p_txn_hdr_rec.process_code = 'UPDTLP' THEN
277 --Generate the Input Xml required for the Business Event -- UpdateListPrice
278 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
279 transaction_number,
280 org_id,
281 Vendor_id,'
282 ||l_user_name|| ' user_name, '
283 ||l_user_id||'user_id,'
284 ||l_exe_dtl_id||'Execution_detail_id,
285 CURSOR(select transaction_line_id,
286 inventory_item_id,
287 supplier_new_price new_price,
288 UOM,
289 headers.trx_currency currency
290 FROM dpp_transaction_lines_all lines
291 WHERE headers.Transaction_header_id = lines.Transaction_header_id
292 AND nvl(lines.UPDATE_ITEM_LIST_PRICE,''N'') = ''N'') LINES
293 FROM dpp_transaction_headers_all headers
294 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
295 'AND EXISTS (SELECT Transaction_header_id
296 FROM dpp_transaction_lines_all
297 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
298 AND nvl(UPDATE_ITEM_LIST_PRICE,''N'') = ''N'')'
299 );
300 dbms_xmlquery.setRowTag(l_queryCtx
301 , 'HEADER'
302 );
303 dbms_xmlquery.setRowSetTag(l_queryCtx
304 ,'TRANSACTION'
305 );
306 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
307 --Check if the query returns any rows
308 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
309 dbms_xmlquery.closeContext(l_queryCtx);
310
311 IF l_row_count >0 THEN
312 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
313 UPDATE DPP_TRANSACTION_LINES_ALL
314 SET update_item_list_price = 'P',
315 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
316 last_updated_by = l_user_id,
317 last_update_date = sysdate,
318 last_update_login = l_login_id
319 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
320 AND nvl(update_item_list_price, 'N') = 'N';
321
322 IF SQL%ROWCOUNT = 0 THEN
323 l_return_status := FND_API.G_RET_STS_ERROR;
324 DPP_UTILITY_PVT.debug_message('Unable to Update the column update_item_list_price in DPP_TRANSACTION_LINES_ALL Table');
325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
326 END IF;
327
328 END IF;
329 ELSIF p_txn_hdr_rec.process_code = 'OUTPL' THEN
330 --Generate the Input Xml required for the Business-- Event Send Notification for Outbound Price lists
331 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
332 Transaction_number,
333 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
334 org_id,
335 Vendor_id,
336 Vendor_site_id,'
337 ||l_user_name|| ' user_name, '
338 ||l_user_id||'user_id,'
339 ||l_exe_dtl_id||'Execution_detail_id,
340 CURSOR(select transaction_line_id,
341 inventory_item_id,
342 supplier_new_price new_price,
343 headers.trx_currency currency
344 FROM dpp_transaction_lines_all lines
345 WHERE headers.Transaction_header_id = lines.Transaction_header_id
346 AND nvl(lines.NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'') LINES
347 FROM dpp_transaction_headers_all headers
348 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
349 'AND EXISTS (SELECT Transaction_header_id
350 FROM dpp_transaction_lines_all
351 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
352 AND nvl(NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'')'
353 );
354 dbms_xmlquery.setRowTag(l_queryCtx
355 , 'HEADER'
356 );
357 dbms_xmlquery.setRowSetTag(l_queryCtx
358 ,'TRANSACTION'
359 );
360 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
361 --Check if the query returns any rows
362 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
363 dbms_xmlquery.closeContext(l_queryCtx);
364
365 IF l_row_count >0 THEN
366 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
367 UPDATE DPP_TRANSACTION_LINES_ALL
368 SET NOTIFY_OUTBOUND_PRICELIST = 'P',
369 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
370 last_updated_by = l_user_id,
371 last_update_date = sysdate,
372 last_update_login = l_login_id
373 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
374 AND nvl(NOTIFY_OUTBOUND_PRICELIST,'N') <> 'D';
375
376 IF SQL%ROWCOUNT = 0 THEN
377 l_return_status := FND_API.G_RET_STS_ERROR;
378 DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_OUTBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381 END IF;
382
383 ELSIF p_txn_hdr_rec.process_code = 'UPDCLM' THEN
384 --Calculate the value of the claim header amount
385 BEGIN
386 SELECT SUM(nvl(claim_amount,0))
387 INTO l_claim_hdr_amt
388 FROM dpp_transaction_lines_all
389 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
390 AND to_number(supp_dist_claim_id) = p_txn_hdr_rec.claim_id;
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
394 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
395 fnd_message.set_token('ERRNO', sqlcode);
396 fnd_message.set_token('REASON', 'INVALID CLAIM HEADER AMOUNT');
397 FND_MSG_PUB.add;
398 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
399 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
400 END IF;
401 RAISE FND_API.G_EXC_ERROR;
402 WHEN OTHERS THEN
403 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
404 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
405 fnd_message.set_token('ERRNO', sqlcode);
406 fnd_message.set_token('REASON', sqlerrm);
407 FND_MSG_PUB.add;
408 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
409 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_CLAIM_AMT');
410 FND_MSG_PUB.add;
411 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
412 END IF;
413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414 END;
415 --Generate the Input Xml required for the Business Event --Update Claim
416 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
417 Transaction_number,
418 Vendor_id,
419 org_id,
420 Vendor_site_id,'
421 ||l_user_name|| ' user_name, '
422 ||l_user_id||'user_id,'
423 ||l_exe_dtl_id||'Execution_detail_id,'
424 ||l_status_code||'Status_code,
425 trx_currency,'
429 inventory_item_id,
426 ||l_claim_hdr_amt||'Claim_amount,'
427 ||p_txn_hdr_rec.claim_id||'claim_id,
428 CURSOR(select transaction_line_id,
430 claim_amount claim_amount,
431 approved_inventory claim_quantity,
432 UOM,'
433 ||p_txn_hdr_rec.claim_id||'claim_id,
434 headers.trx_currency currency
435 FROM dpp_transaction_lines_all lines
436 WHERE headers.Transaction_header_id = lines.Transaction_header_id
437 AND lines.supp_dist_claim_id = '||p_txn_hdr_rec.claim_id||') LINES
438 FROM dpp_transaction_headers_all headers
439 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID
440 );
441 dbms_xmlquery.setRowTag(l_queryCtx
442 , 'HEADER'
443 );
444 dbms_xmlquery.setRowSetTag(l_queryCtx
445 ,'TRANSACTION'
446 );
447 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
448 --Check if the query returns any rows
449 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
450 dbms_xmlquery.closeContext(l_queryCtx);
451
452 ELSIF p_txn_hdr_rec.process_code = 'INPL' THEN
453 --Generate the Input Xml required for the Business Event -- Send Notification for Inbound Price lists
454 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
455 Transaction_number,
456 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
457 org_id,
458 Vendor_id,
459 Vendor_site_id,'
460 ||l_user_name|| ' user_name, '
461 ||l_user_id||'user_id,'
462 ||l_exe_dtl_id||'Execution_detail_id,
463 CURSOR(select transaction_line_id,
464 inventory_item_id,
465 supplier_new_price new_price,
466 headers.trx_currency currency
467 FROM dpp_transaction_lines_all lines
468 WHERE headers.Transaction_header_id = lines.Transaction_header_id
469 AND nvl(lines.NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'') LINES
470 FROM dpp_transaction_headers_all headers
471 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
472 'AND EXISTS (SELECT Transaction_header_id
473 FROM dpp_transaction_lines_all
474 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
475 AND nvl(NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'')'
476 );
477 dbms_xmlquery.setRowTag(l_queryCtx
478 , 'HEADER'
479 );
480 dbms_xmlquery.setRowSetTag(l_queryCtx
481 ,'TRANSACTION'
482 );
483 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
484 --Check if the query returns any rows
485 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
486 dbms_xmlquery.closeContext(l_queryCtx);
487
488 IF l_row_count >0 THEN
489 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
490 UPDATE DPP_TRANSACTION_LINES_ALL
491 SET NOTIFY_INBOUND_PRICELIST = 'P',
492 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
493 last_updated_by = l_user_id,
494 last_update_date = sysdate,
495 last_update_login = l_login_id
496 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
497 AND nvl(NOTIFY_INBOUND_PRICELIST,'N') <> 'D';
498
499 IF SQL%ROWCOUNT = 0 THEN
500 l_return_status := FND_API.G_RET_STS_ERROR;
501 DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_INBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504 END IF;
505
506 ELSIF p_txn_hdr_rec.process_code = 'PROMO' THEN
507 --Generate the Input Xml required for the Business Event -- Send Notification for Offers
508 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
509 Transaction_number,
510 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
511 org_id,
512 Vendor_id,
513 Vendor_site_id,'
517 CURSOR(select transaction_line_id,
514 ||l_user_name|| ' user_name, '
515 ||l_user_id||'user_id,'
516 ||l_exe_dtl_id||'Execution_detail_id,
518 inventory_item_id,
519 supplier_new_price new_price,
520 headers.trx_currency currency
521 FROM dpp_transaction_lines_all lines
522 WHERE headers.Transaction_header_id = lines.Transaction_header_id
523 AND nvl(lines.NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'') LINES
524 FROM dpp_transaction_headers_all headers
525 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
526 'AND EXISTS (SELECT Transaction_header_id
527 FROM dpp_transaction_lines_all
528 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
529 AND nvl(NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'')'
530 );
531 dbms_xmlquery.setRowTag(l_queryCtx
532 , 'HEADER'
533 );
534 dbms_xmlquery.setRowSetTag(l_queryCtx
535 ,'TRANSACTION'
536 );
537 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
538 --Check if the query returns any rows
539 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
540 dbms_xmlquery.closeContext(l_queryCtx);
541
542 IF l_row_count >0 THEN
543 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
544 UPDATE DPP_TRANSACTION_LINES_ALL
545 SET NOTIFY_PROMOTIONS_PRICELIST = 'P',
546 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
547 last_updated_by = l_user_id,
548 last_update_date = sysdate,
549 last_update_login = l_login_id
550 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
551 AND nvl(NOTIFY_PROMOTIONS_PRICELIST,'N') <> 'D';
552
553 IF SQL%ROWCOUNT = 0 THEN
554 l_return_status := FND_API.G_RET_STS_ERROR;
555 DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_PROMOTIONS_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END IF;
558 END IF;
559 ELSIF p_txn_hdr_rec.process_code = 'INVC' THEN
560 --Retrieve the gl_cost_adjustment_acct
561 BEGIN
562 SELECT ostp.gl_cost_adjustment_acct
563 INTO l_cost_adj_acct
564 FROM ozf_supp_trd_prfls_all ostp,
565 dpp_transaction_headers_all dtha
566 WHERE ostp.supplier_id = to_number(dtha.vendor_id)
567 AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
568 AND ostp.org_id = to_number(dtha.org_id)
569 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 l_cost_adj_acct := null;
573 WHEN OTHERS THEN
574 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
575 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
576 fnd_message.set_token('ERRNO', sqlcode);
577 fnd_message.set_token('REASON', sqlerrm);
578 FND_MSG_PUB.add;
579 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
580 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_COSTADJACC');
581 FND_MSG_PUB.add;
582 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
583 END IF;
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END;
586 IF l_cost_adj_acct IS NULL THEN
587 BEGIN
588 SELECT osp.gl_cost_adjustment_acct
589 INTO l_cost_adj_acct
590 FROM ozf_sys_parameters osp,
591 dpp_transaction_headers_all dtha
592 WHERE osp.org_id = to_number(dtha.org_id)
593 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
597 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
598 fnd_message.set_token('ERRNO', sqlcode);
599 fnd_message.set_token('REASON', 'COST ADJUSTMENT ACCOUNT NOT FOUND');
600 FND_MSG_PUB.add;
601 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
602 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
603 END IF;
604 RAISE FND_API.G_EXC_ERROR;
605 WHEN OTHERS THEN
606 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
607 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
608 fnd_message.set_token('ERRNO', sqlcode);
609 fnd_message.set_token('REASON', sqlerrm);
610 FND_MSG_PUB.add;
611 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
612 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_COSTADJACC');
613 FND_MSG_PUB.add;
614 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
615 END IF;
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 END;
618 END IF;
619
620 IF l_cost_adj_acct IS NOT NULL THEN
621 IF g_debug THEN
622 DPP_UTILITY_PVT.debug_message('Cost Adjustment Account : ' ||l_cost_adj_acct);
623 END IF;
624 --Generate the Input Xml required for the Business Event -- Update Inventory Costing
625 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
626 Transaction_number,
627 org_id,'
628 ||l_user_name|| ' user_name, '
629 ||l_user_id||'user_id,'
630 ||l_exe_dtl_id||'Execution_detail_id,'
631 ||l_cost_adj_acct||'gl_cost_adjustment_acct,
632 CURSOR(select transaction_line_id,
633 inventory_item_id,
634 supplier_new_price new_price,
635 headers.trx_currency currency,
636 UOM,
637 price_change
638 FROM dpp_transaction_lines_all lines
639 WHERE headers.Transaction_header_id = lines.Transaction_header_id
640 AND nvl(lines.UPDATE_INVENTORY_COSTING,''N'') = ''N'') LINES
641 FROM dpp_transaction_headers_all headers
642 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
643 'AND EXISTS (SELECT Transaction_header_id
644 FROM dpp_transaction_lines_all
645 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
646 AND nvl(UPDATE_INVENTORY_COSTING,''N'') = ''N'')'
647 );
648 dbms_xmlquery.setRowTag(l_queryCtx
649 , 'HEADER'
650 );
651 dbms_xmlquery.setRowSetTag(l_queryCtx
652 ,'TRANSACTION'
653 );
654 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
655 --Check if the query returns any rows
656 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
657 dbms_xmlquery.closeContext(l_queryCtx);
658
659 IF l_row_count >0 THEN
660 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
661 UPDATE DPP_TRANSACTION_LINES_ALL
662 SET UPDATE_INVENTORY_COSTING = 'P',
663 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
664 last_updated_by = l_user_id,
665 last_update_date = sysdate,
666 last_update_login = l_login_id
667 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
668 AND nvl(UPDATE_INVENTORY_COSTING,'N') = 'N';
669
670 IF SQL%ROWCOUNT = 0 THEN
671 l_return_status := FND_API.G_RET_STS_ERROR;
672 DPP_UTILITY_PVT.debug_message('Unable to Update the column UPDATE_INVENTORY_COSTING in DPP_TRANSACTION_LINES_ALL Table');
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675 END IF;
676 ELSE
677 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
678 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
679 fnd_message.set_token('ERRNO', sqlcode);
680 fnd_message.set_token('REASON', 'COST ADJUSTMENT ACCOUNT IS NULL');
681 FND_MSG_PUB.add;
682 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
683 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
684 END IF;
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 ELSIF p_txn_hdr_rec.process_code = 'NTFYPO' THEN
689 --Generate the Input Xml required for the Business-- Event Send Notifications for Purchase Orders
690 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
691 Transaction_number,
692 org_id,
693 to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
694 vendor_site_id,
695 Vendor_id,'
696 ||l_user_name|| ' user_name, '
697 ||l_user_id||'user_id,'
698 ||l_exe_dtl_id||'Execution_detail_id,
699 CURSOR(select transaction_line_id,
700 inventory_item_id,
701 supplier_new_price new_price,
702 UOM,
703 headers.trx_currency currency
704 FROM dpp_transaction_lines_all lines
705 WHERE headers.Transaction_header_id = lines.Transaction_header_id
706 AND nvl(lines.NOTIFY_PURCHASING_DOCS,''N'') <> ''D'') LINES
707 FROM dpp_transaction_headers_all headers
708 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
709 'AND EXISTS (SELECT Transaction_header_id
710 FROM dpp_transaction_lines_all
711 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
712 AND nvl(NOTIFY_PURCHASING_DOCS,''N'') <> ''D'')'
713 );
714 dbms_xmlquery.setRowTag(l_queryCtx
715 , 'HEADER'
716 );
717 dbms_xmlquery.setRowSetTag(l_queryCtx
718 ,'TRANSACTION'
719 );
720 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
721 --Check if the query returns any rows
722 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
723 dbms_xmlquery.closeContext(l_queryCtx);
724
725 IF l_row_count >0 THEN
726 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
727 UPDATE DPP_TRANSACTION_LINES_ALL
728 SET notify_purchasing_docs = 'P',
729 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
730 last_updated_by = l_user_id,
731 last_update_date = sysdate,
732 last_update_login = l_login_id
733 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
734 AND nvl(notify_purchasing_docs ,'N') <> 'D';
735
736 IF SQL%ROWCOUNT = 0 THEN
737 l_return_status := FND_API.G_RET_STS_ERROR;
738 DPP_UTILITY_PVT.debug_message('Unable to Update the column notify_purchasing_docs in DPP_TRANSACTION_LINES_ALL Table');
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741 END IF;
742 ELSIF p_txn_hdr_rec.process_code = 'UPDTPO' THEN
743 --Generate the Input Xml required for the Business Event -- Update Purchasing - Purchase Orders
744 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
745 Transaction_number,
746 org_id,
747 Vendor_id,'
748 ||l_user_name|| ' user_name, '
749 ||l_user_id||'user_id,'
750 ||l_exe_dtl_id||'Execution_detail_id,
751 CURSOR(select transaction_line_id,
752 inventory_item_id,
753 supplier_new_price new_price,
754 UOM,
755 headers.trx_currency currency
756 FROM dpp_transaction_lines_all lines
757 WHERE headers.Transaction_header_id = lines.Transaction_header_id
758 AND nvl(lines.UPDATE_PURCHASING_DOCS,''N'') = ''N'') LINES
759 FROM dpp_transaction_headers_all headers
760 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
761 'AND EXISTS (SELECT Transaction_header_id
762 FROM dpp_transaction_lines_all
763 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
764 AND nvl(UPDATE_PURCHASING_DOCS,''N'') = ''N'')'
765 );
766 dbms_xmlquery.setRowTag(l_queryCtx
767 , 'HEADER'
768 );
769 dbms_xmlquery.setRowSetTag(l_queryCtx
770 ,'TRANSACTION'
771 );
772 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
773 --Check if the query returns any rows
774 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
775 dbms_xmlquery.closeContext(l_queryCtx);
776
777 IF l_row_count >0 THEN
778 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
779 UPDATE DPP_TRANSACTION_LINES_ALL
780 SET UPDATE_PURCHASING_DOCS = 'P',
781 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
785 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
782 last_updated_by = l_user_id,
783 last_update_date = sysdate,
784 last_update_login = l_login_id
786 AND nvl(UPDATE_PURCHASING_DOCS ,'N')= 'N';
787
788 IF SQL%ROWCOUNT = 0 THEN
789 l_return_status := FND_API.G_RET_STS_ERROR;
790 DPP_UTILITY_PVT.debug_message('Unable to Update the column UPDATE_PURCHASING_DOCS in DPP_TRANSACTION_LINES_ALL Table');
791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
792 END IF;
793 END IF;
794 ELSIF p_txn_hdr_rec.process_code = 'DSTRINVCL' THEN
795 --Added code for DPP Price Increase Enhancement
796 BEGIN
797 SELECT nvl(create_claim_price_increase,'N')
798 INTO l_price_change_flag
799 FROM ozf_supp_trd_prfls_all ostp,
800 dpp_transaction_headers_all dtha
801 WHERE ostp.supplier_id = to_number(dtha.vendor_id)
802 AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
803 AND ostp.org_id = to_number(dtha.org_id)
804 AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
805 EXCEPTION
806 WHEN NO_DATA_FOUND THEN
807 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
808 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
809 fnd_message.set_token('ERRNO', sqlcode);
810 fnd_message.set_token('REASON', 'SUPPLIER TRADE PROFILE IS NOT FOUND'); --To be modified
811 FND_MSG_PUB.add;
812 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
813 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
814 END IF;
815 RAISE FND_API.g_exc_error;
816 WHEN OTHERS THEN
817 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
818 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
819 fnd_message.set_token('ERRNO', sqlcode);
820 fnd_message.set_token('REASON', sqlerrm);
821 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
822 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_EXE_DET_ID'); --To be modified
823 fnd_message.set_token('SEQ_NAME', 'DPP_EXECUTION_DETAIL_ID_SEQ'); --To be modified
824 FND_MSG_PUB.add;
825 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
826 END IF;
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END;
829
830 IF (l_price_change_flag = 'N') THEN
831 dtl_price_change := 'nvl(dtl.price_change,0) > 0';
832 dtla_price_change := 'nvl(dtla.price_change,0) > 0';
833 ELSE
834 dtl_price_change := 'nvl(dtl.price_change,0) <> 0';
835 dtla_price_change := 'nvl(dtla.price_change,0) <> 0';
836 END IF;
837
838 --Generate the Input Xml required for the Business Event -- Create On-Hand Inventory claim
839 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
840 headers.Transaction_number,
841 headers.Vendor_id,
842 headers.org_id,
843 headers.Vendor_site_id,'
844 ||l_user_name|| ' user_name, '
845 ||l_user_id||'user_id,'
846 ||l_exe_dtl_id||'Execution_detail_id,'
847 ||l_claim_type_flag||'claim_type_flag,
848 headers.trx_currency,
849 CURSOR(select dtl.transaction_line_id,
850 dtl.inventory_item_id,
851 dtl.claim_amount claim_line_amount,
852 dtl.approved_inventory CLAIM_QUANTITY,
853 dtl.UOM,
854 headers.trx_currency currency
855 FROM dpp_transaction_lines_all dtl,
856 DPP_TRANSACTION_LINES_GT dtlg
857 WHERE headers.Transaction_header_id = dtl.Transaction_header_id
858 AND dtl.transaction_line_id = dtlg.transaction_line_id
859 AND '||dtl_price_change||'
860 AND nvl(dtl.approved_inventory,0) > 0
861 AND nvl(dtl.SUPP_DIST_CLAIM_STATUS,''N'') = ''N'') LINES
862 FROM dpp_transaction_headers_all headers
863 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
864 'AND EXISTS (SELECT dtla.Transaction_header_id
865 FROM dpp_transaction_lines_all dtla,
866 DPP_TRANSACTION_LINES_GT dtg
867 WHERE dtla.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
868 AND dtla.transaction_line_id = dtg.transaction_line_id
869 AND nvl(dtla.SUPP_DIST_CLAIM_STATUS,''N'') = ''N''
870 AND '||dtla_price_change||'
874 , 'HEADER'
871 AND nvl(dtla.approved_inventory,0) > 0)'
872 );
873 dbms_xmlquery.setRowTag(l_queryCtx
875 );
876 dbms_xmlquery.setRowSetTag(l_queryCtx
877 ,'TRANSACTION'
878 );
879 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
880 --Check if the query returns any rows
881 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
882 dbms_xmlquery.closeContext(l_queryCtx);
883
884 IF l_row_count >0 THEN
885 IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
886
887 IF (l_price_change_flag = 'N') THEN -- Only Price Decrease Lines
888 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
889 UPDATE DPP_TRANSACTION_LINES_ALL
890 SET SUPP_DIST_CLAIM_STATUS = 'P',
891 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
892 last_updated_by = l_user_id,
893 last_update_date = sysdate,
894 last_update_login = l_login_id
895 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
896 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
897 AND nvl(approved_inventory,0) > 0
898 AND nvl(price_change,0) > 0;
899
900 IF SQL%ROWCOUNT = 0 THEN
901 l_return_status := FND_API.G_RET_STS_ERROR;
902 DPP_UTILITY_PVT.debug_message('Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
904 END IF;
905 --Update those lines to Y who have the approved inventory as 0
906 UPDATE DPP_TRANSACTION_LINES_ALL
907 SET SUPP_DIST_CLAIM_STATUS = 'Y',
908 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
909 last_updated_by = l_user_id,
910 last_update_date = sysdate,
911 last_update_login = l_login_id
912 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
913 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
914 AND (nvl(approved_inventory,0) = 0 OR nvl(price_change,0) <= 0);
915 ELSE -- Both Price Increase and Price Decrease Lines
916 --Update the line status to PENDING in the DPP_TRANSACTION_LINES_ALL table
917 UPDATE DPP_TRANSACTION_LINES_ALL
918 SET SUPP_DIST_CLAIM_STATUS = 'P',
919 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
920 last_updated_by = l_user_id,
921 last_update_date = sysdate,
922 last_update_login = l_login_id
923 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
924 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
925 AND nvl(approved_inventory,0) > 0
926 AND nvl(price_change,0) <> 0;
927
928 IF SQL%ROWCOUNT = 0 THEN
929 l_return_status := FND_API.G_RET_STS_ERROR;
930 DPP_UTILITY_PVT.debug_message('Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 END IF;
933 --Update those lines to Y who have the approved inventory as 0
934 UPDATE DPP_TRANSACTION_LINES_ALL
935 SET SUPP_DIST_CLAIM_STATUS = 'Y',
936 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
937 last_updated_by = l_user_id,
938 last_update_date = sysdate,
939 last_update_login = l_login_id
940 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
941 AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
942 AND nvl(approved_inventory,0) = 0;
943 END IF;
944 END IF; --p_txn_hdr_rec.claim_creation_source = 'EXEDTLS'
945 END IF;
946 ELSIF p_txn_hdr_rec.process_code = 'CUSTINVCL' THEN
947 --Generate the Input Xml required for the Business Event -- Create Customer Inventory claim for distributor
948 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
949 headers.Transaction_number,
950 headers.org_id,
951 headers.Vendor_id,
952 headers.Vendor_site_id,'
953 ||l_user_name|| ' user_name, '
954 ||l_user_id||'user_id,'
955 ||l_exe_dtl_id||'Execution_detail_id,'
956 ||l_claim_type_flag||'claim_type_flag,
957 headers.trx_currency,
958 CURSOR(select dcc.customer_inv_line_id TRANSACTION_LINE_ID,
959 dcc.inventory_item_id,
960 dcc.cust_account_id CUSTOMER_ACCOUNT_ID,
961 dcc.supp_claim_amt CLAIM_LINE_AMOUNT,
962 dcc.reported_inventory claim_quantity,
963 dcc.trx_currency currency,
964 dcc.UOM
965 FROM DPP_customer_claims_all dcc,
966 DPP_TRANSACTION_LINES_GT dtg
970 AND nvl(dcc.supp_claim_amt,0) > 0
967 WHERE headers.Transaction_header_id = dcc.Transaction_header_id
968 AND dcc.customer_inv_line_id = dtg.transaction_line_id
969 AND nvl(dcc.reported_inventory,0) > 0
971 AND nvl(dcc.supplier_claim_created,''N'') = ''N'') LINES
972 FROM dpp_transaction_headers_all headers
973 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
974 'AND EXISTS (SELECT dcca.Transaction_header_id
975 FROM DPP_customer_claims_all dcca,
976 DPP_TRANSACTION_LINES_GT dtga
977 WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
978 AND dcca.customer_inv_line_id = dtga.transaction_line_id
979 AND nvl(dcca.supplier_claim_created,''N'') = ''N''
980 AND nvl(dcca.reported_inventory,0) > 0
981 AND nvl(dcca.supp_claim_amt,0) > 0)'
982 );
983 dbms_xmlquery.setRowTag(l_queryCtx
984 , 'HEADER'
985 );
986 dbms_xmlquery.setRowSetTag(l_queryCtx
987 ,'TRANSACTION'
988 );
989 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
990 --Check if the query returns any rows
991 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
992 dbms_xmlquery.closeContext(l_queryCtx);
993
994 IF l_row_count >0 THEN
995 IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
996 --Update the line status to PENDING in the DPP_customer_claims_all table
997 UPDATE DPP_customer_claims_all
998 SET supplier_claim_created = 'P',
999 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1000 last_updated_by = l_user_id,
1001 last_update_date = sysdate,
1002 last_update_login = l_login_id
1003 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1004 AND nvl(supplier_claim_created,'N') = 'N'
1005 AND nvl(reported_inventory,0) > 0
1006 AND nvl(supp_claim_amt,0) > 0;
1007
1008 IF SQL%ROWCOUNT = 0 THEN
1009 l_return_status := FND_API.G_RET_STS_ERROR;
1010 DPP_UTILITY_PVT.debug_message('Unable to Update the column supplier_claim_created in DPP_customer_claims_all Table');
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013 --Update those lines to Y which have reported inventory as 0
1014 UPDATE DPP_customer_claims_all
1015 SET supplier_claim_created = 'Y',
1016 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
1017 last_updated_by = l_user_id,
1018 last_update_date = sysdate,
1019 last_update_login = l_login_id
1020 WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
1021 AND nvl(supplier_claim_created,'N') = 'N'
1022 AND (nvl(reported_inventory,0) = 0 OR nvl(supp_claim_amt,0) <= 0);
1023
1024 END IF; --IF p_txn_hdr_rec.claim_creation_source = 'EXEDTLS' THEN
1025 END IF;
1026 ELSIF p_txn_hdr_rec.process_code = 'CUSTCL' THEN
1027 --Generate the Input Xml required for the Business Event -- Create Customer Inventory claim for customer
1028 l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
1029 headers.Transaction_number,
1030 headers.org_id,
1031 headers.Vendor_id,
1032 headers.Vendor_site_id,'
1033 ||l_user_name|| ' user_name, '
1034 ||l_user_id||'user_id,'
1035 ||l_exe_dtl_id||'Execution_detail_id,'
1036 ||l_claim_type_flag||'claim_type_flag,
1037 headers.trx_currency,
1038 CURSOR(select dcc.customer_inv_line_id transaction_line_id,
1039 dcc.inventory_item_id,
1040 dcc.cust_account_id customer_account_id,
1041 dcc.cust_claim_amt claim_line_amount,
1042 dcc.reported_inventory claim_quantity,
1043 dcc.trx_currency currency,
1044 dcc.UOM
1045 FROM DPP_customer_claims_all dcc,
1046 DPP_TRANSACTION_LINES_GT dtg
1047 WHERE headers.Transaction_header_id = dcc.Transaction_header_id
1048 AND dcc.customer_inv_line_id = dtg.transaction_line_id
1049 AND nvl(dcc.reported_inventory,0) > 0
1050 AND nvl(dcc.cust_claim_amt,0) > 0
1051 AND nvl(dcc.customer_claim_created,''N'') = ''N'') LINES
1055 FROM DPP_customer_claims_all dcca,
1052 FROM dpp_transaction_headers_all headers
1053 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1054 'AND EXISTS (SELECT dcca.Transaction_header_id
1056 DPP_TRANSACTION_LINES_GT dtga
1057 WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
1058 AND dcca.customer_inv_line_id = dtga.transaction_line_id
1059 AND nvl(dcca.customer_claim_created,''N'') = ''N''
1060 AND nvl(dcca.reported_inventory,0) > 0
1061 AND nvl(dcca.cust_claim_amt,0) > 0)'
1062 );
1063 dbms_xmlquery.setRowTag(l_queryCtx
1064 , 'HEADER'
1065 );
1066 dbms_xmlquery.setRowSetTag(l_queryCtx
1067 ,'TRANSACTION'
1068 );
1069 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1070 --Check if the query returns any rows
1071 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1072 dbms_xmlquery.closeContext(l_queryCtx);
1073
1074 ELSIF p_txn_hdr_rec.process_code = 'POPCUSTCLAIM' THEN
1075 --Generate the Input Xml required for the Business Event -- Populate Customer Claim lines details
1076 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1077 Transaction_number,
1078 org_id,
1079 nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
1080 to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,
1081 trx_currency,'
1082 ||l_user_name|| ' user_name, '
1083 ||l_user_id||'user_id,'
1084 ||l_exe_dtl_id||'Execution_detail_id,
1085 CURSOR(select transaction_line_id,
1086 inventory_item_id,
1087 UOM
1088 FROM dpp_transaction_lines_all lines
1089 WHERE headers.Transaction_header_id = lines.Transaction_header_id
1090 AND lines.price_change > 0 ) LINES
1091 FROM dpp_transaction_headers_all headers
1092 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1093 'AND EXISTS (SELECT Transaction_header_id
1094 FROM dpp_transaction_lines_all
1095 WHERE price_change > 0
1096 AND Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
1097 );
1098 dbms_xmlquery.setRowTag(l_queryCtx
1099 , 'HEADER'
1100 );
1101 dbms_xmlquery.setRowSetTag(l_queryCtx
1102 ,'TRANSACTION'
1103 );
1104 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1105 --Check if the query returns any rows
1106 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1107 dbms_xmlquery.closeContext(l_queryCtx);
1108
1109 ELSIF p_txn_hdr_rec.process_code = 'POPINVDTLS' THEN
1110 --Generate the Input Xml required for the Business Event --Populate Inventory Details
1111 l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
1112 Transaction_number,
1113 org_id,
1114 nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
1115 to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,'
1116 ||l_user_name|| ' user_name, '
1117 ||l_user_id||'user_id,'
1118 ||l_exe_dtl_id||'Execution_detail_id,
1119 CURSOR(select transaction_line_id,
1120 inventory_item_id
1121 FROM dpp_transaction_lines_all lines
1122 WHERE headers.Transaction_header_id = lines.Transaction_header_id) LINES
1123 FROM dpp_transaction_headers_all headers
1124 WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
1125 'AND EXISTS (SELECT Transaction_header_id
1126 FROM dpp_transaction_lines_all
1127 WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
1131 );
1128 );
1129 dbms_xmlquery.setRowTag(l_queryCtx
1130 , 'HEADER'
1132 dbms_xmlquery.setRowSetTag(l_queryCtx
1133 ,'TRANSACTION'
1134 );
1135 l_input_xml := dbms_xmlquery.getXml(l_queryCtx);
1136 --Check if the query returns any rows
1137 l_row_count := dbms_xmlquery.getNumRowsProcessed(l_queryCtx);
1138 dbms_xmlquery.closeContext(l_queryCtx);
1139 ELSE
1140 FND_MESSAGE.set_name('DPP', 'DPP_BUSEVT_INVALID_PRO_CODE');
1141 fnd_message.set_token('PROCESS_CODE', p_txn_hdr_rec.process_code);
1142 FND_MSG_PUB.add;
1143 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1144 RAISE FND_API.g_exc_error;
1145 END IF;
1146
1147 --Raise the Workflow Event Procedure for the given process
1148 Raise_Workflow_Event(
1149 x_return_status => l_return_status
1150 ,x_msg_count => l_msg_count
1151 ,x_msg_data => l_msg_data
1152 ,p_txn_hdr_id => p_txn_hdr_rec.Transaction_Header_ID
1153 ,p_process_code => p_txn_hdr_rec.process_code
1154 ,p_input_xml => l_input_xml
1155 ,p_row_count => l_row_count
1156 ,p_exe_dtl_id => l_exe_dtl_id
1157 );
1158 IF g_debug THEN
1159 dpp_utility_pvt.debug_message('return status for Raise_Workflow_Event =>'||l_return_status);
1160 --dpp_utility_pvt.debug_message (substr(('Message data =>'||l_msg_data),1,4000));
1161 END IF;
1162 -- Check return status from the above procedure call
1163 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1164 RAISE FND_API.g_exc_error;
1165 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167 END IF;
1168 x_return_status:= l_return_status;
1169
1170 -- Standard check for p_commit
1171 IF FND_API.to_Boolean( p_commit )
1172 THEN
1173 COMMIT;
1174 END IF;
1175 FND_MSG_PUB.count_and_get(
1176 p_encoded => FND_API.g_false,
1177 p_count => x_msg_count,
1178 p_data => x_msg_data
1179 );
1180
1181 --Exception Handling
1182 EXCEPTION
1183 WHEN FND_API.g_exc_error THEN
1184 ROLLBACK TO Raise_Business_Event;
1185 x_return_status := FND_API.g_ret_sts_error;
1186 FND_MSG_PUB.count_and_get (
1187 p_encoded => FND_API.g_false,
1188 p_count => x_msg_count,
1189 p_data => x_msg_data
1190 );
1191
1192 IF x_msg_count > 1 THEN
1193 FOR I IN 1..x_msg_count LOOP
1194 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1195 END LOOP;
1196 END IF;
1197
1198 WHEN FND_API.g_exc_unexpected_error THEN
1199 ROLLBACK TO Raise_Business_Event;
1200 x_return_status := FND_API.g_ret_sts_unexp_error ;
1201 FND_MSG_PUB.count_and_get (
1202 p_encoded => FND_API.g_false,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206
1207 IF x_msg_count > 1 THEN
1208 FOR I IN 1..x_msg_count LOOP
1209 x_msg_data := SUBSTR((x_msg_data||' '|| substr(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'),1,254)), 1, 4000);
1210 END LOOP;
1211 END IF;
1212
1213 WHEN OTHERS THEN
1214 ROLLBACK TO Raise_Business_Event;
1215 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1216 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1217 fnd_message.set_token('ERRNO', sqlcode);
1218 fnd_message.set_token('REASON', sqlerrm);
1219 x_return_status := FND_API.g_ret_sts_unexp_error ;
1220 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1221 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1222 END IF;
1223 FND_MSG_PUB.count_and_get (
1224 p_encoded => FND_API.g_false,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data
1227 );
1228
1229 IF x_msg_count > 1 THEN
1230 FOR I IN 1..x_msg_count LOOP
1231 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1232 END LOOP;
1233 END IF;
1234
1235 END Raise_Business_Event;
1236
1237 ---------------------------------------------------------------------
1238 -- PROCEDURE
1239 -- Raise_Workflow_Event
1240 --
1241 -- PURPOSE
1242 -- Raise Workflow Event.
1243 --
1244 -- PARAMETERS
1245 --
1246 -- NOTES
1247 -- 1.
1248 -- 2.
1249 ----------------------------------------------------------------------
1250 PROCEDURE Raise_Workflow_Event(
1251 x_return_status OUT NOCOPY VARCHAR2
1252 ,x_msg_count OUT NOCOPY NUMBER
1253 ,x_msg_data OUT NOCOPY VARCHAR2
1254
1255 ,p_txn_hdr_id IN NUMBER
1256 ,p_process_code IN VARCHAR2
1257 ,p_input_xml IN CLOB
1258 ,p_row_count IN NUMBER
1259 ,p_exe_dtl_id IN NUMBER
1260 )
1261 IS
1262 l_api_name CONSTANT VARCHAR2(30) := 'Raise_Business_Event';
1266 l_target_system VARCHAR2(4) := 'EBIZ';
1263 l_event_name VARCHAR2(60):= 'oracle.apps.dpp.executions';
1264 l_new_item_key VARCHAR2(30);
1265 l_parameter_list WF_PARAMETER_LIST_T;
1267 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1268 l_exe_status VARCHAR2(15) := 'SUBMITTED';
1269 l_event_test VARCHAR2(10);
1270
1271 BEGIN
1272
1273 x_return_status := FND_API.g_ret_sts_success;
1274 --------------------- initialize -----------------------
1275 SAVEPOINT Raise_Workflow_Event;
1276
1277 --Get a unique value for the l_new_item_key key
1278 l_new_item_key := TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
1279 --Check if the event subscription can be raised or not before inserting into the DPP_EXECUTION_DETAILS table
1280 l_event_test := wf_event.test(l_event_name);
1281 IF l_event_test = 'NONE' THEN
1282 DPP_UTILITY_PVT.debug_message('No enabled local subscriptions reference the event, or the event does not exist.');
1283 RAISE FND_API.g_exc_error;
1284 ELSE
1285 IF g_debug THEN
1286 DPP_UTILITY_PVT.debug_message('Number of rows Processed : '||p_row_count);
1287 END IF;
1288 --Check if the xml has any rows processed and raise the event
1289 IF p_row_count > 0 THEN
1290 --Insert a line in to the DPP_EXECUTION_DETAILS table corresponding to the process which has been started
1291 BEGIN
1292 INSERT INTO DPP_EXECUTION_DETAILS (EXECUTION_DETAIL_ID,
1293 object_version_number,
1294 TRANSACTION_HEADER_ID,
1295 PROCESS_CODE,
1296 INPUT_XML,
1297 EXECUTION_STATUS,
1298 EXECUTION_START_DATE,
1299 CREATION_DATE,
1300 CREATED_BY,
1301 LAST_UPDATE_DATE,
1302 LAST_UPDATED_BY,
1303 LAST_UPDATE_LOGIN)
1304 VALUES (p_exe_dtl_id,
1305 1,
1306 p_txn_hdr_id,
1307 p_process_code,
1308 XMLTYPE(p_input_xml),
1309 l_exe_status,
1310 sysdate,
1311 sysdate,
1312 l_user_id,
1313 sysdate,
1314 l_user_id,
1315 l_user_id);
1316
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1320 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1321 fnd_message.set_token('ERRNO', sqlcode);
1322 fnd_message.set_token('REASON', sqlerrm);
1323 FND_MSG_PUB.add;
1324 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1325 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1326 END IF;
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END;
1329 --Define in WF Attributes l_parameter_list := WF_PARAMETER_LIST_T();
1330 WF_EVENT.AddParameterToList( p_name => 'TARGETSYSTEM'
1331 , p_value => l_target_system
1332 , p_parameterlist => l_parameter_list
1333 );
1334 WF_EVENT.AddParameterToList( p_name => 'FLOWNAME'
1335 , p_value => p_process_code
1336 , p_parameterlist => l_parameter_list
1337 );
1338 WF_EVENT.AddParameterToList( p_name => 'EXECUTIONDETAILID'
1339 , p_value => p_exe_dtl_id
1340 , p_parameterlist => l_parameter_list
1341 );
1342 WF_EVENT.Raise(p_event_name => l_event_name
1343 ,p_event_key => l_new_item_key
1344 ,p_event_data => p_input_xml
1345 ,p_parameters => l_parameter_list
1346 ,p_send_date => sysdate
1347 );
1348 IF g_debug THEN
1349 DPP_UTILITY_PVT.debug_message('Work Flow Event Raised');
1350 END IF;
1351 ELSE
1352 IF g_debug THEN
1353 DPP_UTILITY_PVT.debug_message('Work Flow Event is not Raised');
1354 END IF;
1355 FND_MESSAGE.set_name('DPP', 'DPP_CC_NO_ELIGIBLE_LINES_MSG');
1356 FND_MSG_PUB.add;
1357 RAISE FND_API.g_exc_error;
1358 END IF; --p_row_count > 0
1359 END IF;
1360
1361 EXCEPTION
1362 WHEN FND_API.g_exc_error THEN
1363 ROLLBACK TO Raise_Workflow_Event;
1364 x_return_status := FND_API.g_ret_sts_error;
1365 FND_MSG_PUB.count_and_get (
1366 p_encoded => FND_API.g_false,
1367 p_count => x_msg_count,
1368 p_data => x_msg_data
1369 );
1370 IF x_msg_count > 1 THEN
1371 FOR I IN 1..x_msg_count LOOP
1372 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1373 END LOOP;
1374 END IF;
1375
1376 WHEN FND_API.g_exc_unexpected_error THEN
1377 ROLLBACK TO Raise_Workflow_Event;
1378 x_return_status := FND_API.g_ret_sts_unexp_error ;
1379 FND_MSG_PUB.count_and_get (
1380 p_encoded => FND_API.g_false,
1381 p_count => x_msg_count,
1382 p_data => x_msg_data
1383 );
1384 IF x_msg_count > 1 THEN
1385 FOR I IN 1..x_msg_count LOOP
1386 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1387 END LOOP;
1388 END IF;
1389
1390 WHEN OTHERS THEN
1391 ROLLBACK TO Raise_Workflow_Event;
1392 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1393 fnd_message.set_token('ROUTINE', 'DPP_BUSINESSEVENTS_PVT');
1394 fnd_message.set_token('ERRNO', sqlcode);
1395 fnd_message.set_token('REASON', sqlerrm);
1396 FND_MSG_PUB.add;
1397 x_return_status := FND_API.g_ret_sts_unexp_error ;
1398 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1399 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1400 END IF;
1401 FND_MSG_PUB.count_and_get (
1402 p_encoded => FND_API.g_false,
1403 p_count => x_msg_count,
1404 p_data => x_msg_data
1405 );
1406 IF x_msg_count > 1 THEN
1407 FOR I IN 1..x_msg_count LOOP
1411
1408 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1409 END LOOP;
1410 END IF;
1412 END Raise_Workflow_Event;
1413 END DPP_BUSINESSEVENTS_PVT;