[Home] [Help]
PACKAGE BODY: APPS.DPP_EXECUTIONPROCESS_PUB
Source
1 PACKAGE BODY DPP_EXECUTIONPROCESS_PUB AS
2 /* $Header: dpppexcb.pls 120.19 2008/04/03 08:04:23 sanagar noship $ */
3
4 -- Package name : DPP_EXECUTIONPROCESS_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_EXECUTIONPROCESS_PUB';
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) := 'dpppexcb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Initiate_ExecutionProcess
17 --
18 -- PURPOSE
19 -- Initiate Execution Process
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27 PROCEDURE Initiate_ExecutionProcess(errbuff OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_in_org_id IN NUMBER,
30 p_in_txn_number IN VARCHAR2
31 )
32 IS
33 l_return_status VARCHAR2(10);
34 l_msg_data VARCHAR2(4000);
35 l_msg_count NUMBER;
36
37 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
38 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
39
40 l_txn_hdr_rec DPP_BUSINESSEVENTS_PVT.dpp_txn_hdr_rec_type;
41 l_txn_line_id DPP_BUSINESSEVENTS_PVT.dpp_txn_line_tbl_type;
42 l_flag boolean := FALSE;
43 l_supp_trade_profile_id NUMBER;
44 l_setup_flag BOOLEAN := FALSE;
45
46 CURSOR get_valid_transaction_csr(p_txn_number VARCHAR2)
47 IS
48 SELECT dpp.transaction_header_id
49 FROM dpp_transaction_headers_all dpp
50 WHERE dpp.transaction_status = 'APPROVED'
51 AND dpp.effective_start_date <= sysdate
52 AND to_number(dpp.org_id) = p_in_org_id
53 AND dpp.transaction_number = nvl(p_txn_number,dpp.transaction_number)
54 AND NOT EXISTS (SELECT dep.transaction_header_id
55 FROM DPP_EXECUTION_PROCESSES dep
56 WHERE dep.transaction_header_id = dpp.transaction_header_id);
57
58 --Cursor to retrieve the process codes from look up
59 CURSOR get_process_codes_csr (p_supp_trd_prf_id NUMBER)
60 IS
61 SELECT dppl.lookup_code
62 FROM dpp_lookups dppl,
63 OZF_PROCESS_SETUP_ALL opsa
64 WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
65 AND dppl.tag is not null
66 AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
67 AND opsa.enabled_flag = 'Y'
68 AND opsa.org_id = p_in_org_id
69 AND dppl.lookup_code = opsa.process_code;
70
71 CURSOR get_approved_txn_csr (p_txn_number VARCHAR2)
72 IS
73 SELECT dpp.transaction_header_id,
74 dpp.transaction_number,
75 dpp.vendor_id,
76 dpp.vendor_site_id
77 FROM dpp_transaction_headers_all dpp
78 WHERE dpp.transaction_status = 'APPROVED'
79 AND dpp.effective_start_date <= sysdate
80 AND to_number(dpp.org_id) = p_in_org_id
81 AND dpp.transaction_number = nvl(p_txn_number,dpp.transaction_number)
82 AND NOT EXISTS (SELECT dep.transaction_header_id
83 FROM DPP_EXECUTION_PROCESSES dep
84 WHERE dep.transaction_header_id = dpp.transaction_header_id);
85
86 CURSOR get_auto_flag_csr(p_supp_trd_prf_id NUMBER,
87 p_process_code VARCHAR2) IS
88 SELECT nvl(automatic_flag,'N') automatic_flag
89 FROM OZF_PROCESS_SETUP_ALL
90 WHERE nvl(supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
91 AND process_code = p_process_code
92 AND enabled_flag = 'Y'
93 AND org_id = p_in_org_id;
94
95 --Cursor to check if the customer claims tab is already populated
96 CURSOR get_customer_claim_csr(p_txn_header_id IN NUMBER)
97 IS
98 SELECT dpp.transaction_header_id
99 FROM dpp_transaction_headers_all dpp
100 WHERE dpp.transaction_header_id = p_txn_header_id
101 AND NOT EXISTS (SELECT DISTINCT dcc.transaction_header_id
102 FROM dpp_customer_claims_all dcc
103 WHERE dcc.transaction_header_id = dpp.transaction_header_id);
104
105 --Cursor for Update PO
106 CURSOR get_lines_for_updatepo_csr(p_txn_hdr_id IN NUMBER)
107 IS
108 SELECT dpp.transaction_header_id
109 FROM dpp_transaction_headers_all dpp
110 WHERE dpp.transaction_header_id = p_txn_hdr_id
111 AND EXISTS (SELECT update_purchasing_docs
112 FROM dpp_transaction_lines_all dtl
113 WHERE nvl(update_purchasing_docs,'N') = 'N'
114 AND dtl.transaction_header_id = p_txn_hdr_id);
115 --Cursor for notify po
116 CURSOR get_lines_for_notifpo_csr(p_txn_hdr_id IN NUMBER)
117 IS
118 SELECT dpp.transaction_header_id
119 FROM dpp_transaction_headers_all dpp
120 WHERE dpp.transaction_header_id = p_txn_hdr_id;
121 --Cursor for Update inv costing
122 CURSOR get_lines_for_updateinv_csr(p_txn_hdr_id IN NUMBER)
123 IS
124 SELECT dtl.transaction_line_id
125 FROM dpp_transaction_lines_all dtl
126 WHERE dtl.transaction_header_id = p_txn_hdr_id
127 AND EXISTS (SELECT UPDATE_INVENTORY_COSTING
128 FROM dpp_transaction_lines_all
129 WHERE nvl(UPDATE_INVENTORY_COSTING,'N') = 'N'
130 AND transaction_header_id = p_txn_hdr_id)
131 AND rownum = 1;
132 --Cursor for Update item List Price
133 CURSOR get_lines_for_updlistprice_csr(p_txn_hdr_id IN NUMBER)
134 IS
135 SELECT dtl.transaction_line_id,
136 dtl.UPDATE_ITEM_LIST_PRICE
137 FROM dpp_transaction_lines_all dtl
138 WHERE dtl.transaction_header_id = p_txn_hdr_id
139 AND EXISTS (SELECT UPDATE_ITEM_LIST_PRICE
140 FROM dpp_transaction_lines_all
141 WHERE nvl(UPDATE_ITEM_LIST_PRICE,'N') = 'N'
142 AND transaction_header_id = p_txn_hdr_id)
143 AND rownum = 1;
144 --Cursor for Notify inbound price list
145 CURSOR get_lines_for_notifinpl_csr(p_txn_hdr_id IN NUMBER)
146 IS
147 SELECT dtl.transaction_line_id,
148 dtl.NOTIFY_INBOUND_PRICELIST
149 FROM dpp_transaction_lines_all dtl
150 WHERE dtl.transaction_header_id = p_txn_hdr_id
151 AND rownum = 1;
152 --Cursor for Notify outbound price list
153 CURSOR get_lines_for_notifoutpl_csr(p_txn_hdr_id IN NUMBER)
154 IS
155 SELECT dtl.transaction_line_id,
156 dtl.NOTIFY_OUTBOUND_PRICELIST
157 FROM dpp_transaction_lines_all dtl
158 WHERE dtl.transaction_header_id = p_txn_hdr_id
159 AND rownum = 1;
160 --Cursor for Notify Promotions
161 CURSOR get_lines_for_notifpromo_csr(p_txn_hdr_id IN NUMBER)
162 IS
163 SELECT dtl.transaction_line_id,
164 dtl.NOTIFY_PROMOTIONS_PRICELIST
165 FROM dpp_transaction_lines_all dtl
166 WHERE dtl.transaction_header_id = p_txn_hdr_id
167 AND rownum = 1;
168
169 BEGIN
170
171 -- Initialize return status to sucess
172 errbuff := 'Success';
173 retcode := 0;
174
175 --Get all the active transactions
176 FOR get_approved_txn_rec IN get_approved_txn_csr(p_in_txn_number) LOOP
177 l_setup_flag := FALSE;
178 BEGIN
179 SELECT supp_trade_profile_id
180 INTO l_supp_trade_profile_id
181 FROM ozf_supp_trd_prfls_all
182 WHERE supplier_id = get_approved_txn_rec.vendor_id
183 AND supplier_site_id = get_approved_txn_rec.vendor_site_id;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 l_supp_trade_profile_id := null;
187 WHEN OTHERS THEN
188 ROLLBACK;
189 retcode := '1';
190 errbuff := 'When Others Exception';
191 FND_FILE.PUT_LINE(FND_FILE.LOG,'When Others Exception'||SQLERRM);
192 FND_FILE.NEW_LINE(FND_FILE.LOG);
193 END;
194 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent program executed for the transaction header id : '
195 ||get_approved_txn_rec.transaction_header_id);
196 FND_FILE.NEW_LINE(FND_FILE.LOG);
197
198 l_flag := FALSE;
199 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
200 'UPDTPO') LOOP
201 l_flag := TRUE;
202 l_setup_flag := TRUE;
203 --Check if the Update PO
204 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
205 FOR get_lines_for_updatepo_rec IN get_lines_for_updatepo_csr(get_approved_txn_rec.transaction_header_id) LOOP
206 l_txn_hdr_rec := NULL;
207 l_txn_line_id.delete();
208 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
209 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
210 l_txn_hdr_rec.Process_code := 'UPDTPO';
211 --Raise business event for Update PO
212 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
213 ,p_init_msg_list => FND_API.G_FALSE
214 ,p_commit => FND_API.G_FALSE
215 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
216 ,x_return_status => l_return_status
217 ,x_msg_count => l_msg_count
218 ,x_msg_data => l_msg_data
219 ,p_txn_hdr_rec => l_txn_hdr_rec
220 ,p_txn_line_id => l_txn_line_id
221 );
222 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
223 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : UPDTPO');
224 FND_FILE.NEW_LINE(FND_FILE.LOG);
225 ELSE
226 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : UPDTPO');
227 FND_FILE.NEW_LINE(FND_FILE.LOG);
228 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
229 FND_FILE.NEW_LINE(FND_FILE.LOG);
230 retcode := '2';
231 errbuff := 'Error';
232 END IF;
233 END LOOP;
234 END IF;
235 END LOOP;
236 IF NOT l_flag THEN
237 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : UPDTPO');
238 FND_FILE.NEW_LINE(FND_FILE.LOG);
239 END IF;
240 l_flag := FALSE;
241 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
242 'NTFYPO') LOOP
243 l_flag := TRUE;
244 l_setup_flag := TRUE;
245 --Check if the Notify PO is called
246 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
247 FOR get_lines_for_notifpo_rec IN get_lines_for_notifpo_csr(get_approved_txn_rec.transaction_header_id)
248 LOOP
249 l_txn_hdr_rec := NULL;
250 l_txn_line_id.delete();
251 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
252 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
253 l_txn_hdr_rec.Process_code := 'NTFYPO';
254 --Raise business event for Notify PO
255 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
256 ,p_init_msg_list => FND_API.G_FALSE
257 ,p_commit => FND_API.G_FALSE
258 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
259 ,x_return_status => l_return_status
260 ,x_msg_count => l_msg_count
261 ,x_msg_data => l_msg_data
262 ,p_txn_hdr_rec => l_txn_hdr_rec
263 ,p_txn_line_id => l_txn_line_id
264 );
265 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
266 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : NTFYPO');
267 FND_FILE.NEW_LINE(FND_FILE.LOG);
268 ELSE
269 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : NTFYPO');
270 FND_FILE.NEW_LINE(FND_FILE.LOG);
271 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
272 FND_FILE.NEW_LINE(FND_FILE.LOG);
273 retcode := '2';
274 errbuff := 'Error';
275 END IF;
276 END LOOP;
277 END IF;
278 END LOOP;
279 IF NOT l_flag THEN
280 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : NTFYPO');
281 FND_FILE.NEW_LINE(FND_FILE.LOG);
282 END IF;
283 l_flag := FALSE;
284 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
285 'INVC') LOOP
286 l_flag := TRUE;
287 l_setup_flag := TRUE;
288 --Check for update inventory costing
289 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
290 FOR get_lines_for_updateinv_rec IN get_lines_for_updateinv_csr(get_approved_txn_rec.transaction_header_id)
291 LOOP
292 l_txn_hdr_rec := NULL;
293 l_txn_line_id.delete();
294 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
295 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
296 l_txn_hdr_rec.Process_code := 'INVC';
297 --Raise business event for Update Inventory Costing
301 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
298 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
299 ,p_init_msg_list => FND_API.G_FALSE
300 ,p_commit => FND_API.G_FALSE
302 ,x_return_status => l_return_status
303 ,x_msg_count => l_msg_count
304 ,x_msg_data => l_msg_data
305 ,p_txn_hdr_rec => l_txn_hdr_rec
306 ,p_txn_line_id => l_txn_line_id
307 );
308 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
309 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : INVC');
310 FND_FILE.NEW_LINE(FND_FILE.LOG);
311 ELSE
312 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : INVC');
313 FND_FILE.NEW_LINE(FND_FILE.LOG);
314 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
315 FND_FILE.NEW_LINE(FND_FILE.LOG);
316 retcode := '2';
317 errbuff := 'Error';
318 END IF;
319 END LOOP;
320 END IF;
321 END LOOP;
322 IF NOT l_flag THEN
323 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : INVC');
324 FND_FILE.NEW_LINE(FND_FILE.LOG);
325 END IF;
326 l_flag := FALSE;
327 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
328 'UPDTLP') LOOP
329 l_flag := TRUE;
330 l_setup_flag := TRUE;
331 --Check for Update Item List Price
332 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
333 FOR get_lines_for_updlistprice_rec IN get_lines_for_updlistprice_csr(get_approved_txn_rec.transaction_header_id)
334 LOOP
335 l_txn_hdr_rec := NULL;
336 l_txn_line_id.delete();
337 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
338 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
339 l_txn_hdr_rec.Process_code := 'UPDTLP';
340 --Raise business event for Update Item List Price
341 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
342 ,p_init_msg_list => FND_API.G_FALSE
343 ,p_commit => FND_API.G_FALSE
344 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
345 ,x_return_status => l_return_status
346 ,x_msg_count => l_msg_count
347 ,x_msg_data => l_msg_data
348 ,p_txn_hdr_rec => l_txn_hdr_rec
349 ,p_txn_line_id => l_txn_line_id
350 );
351 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
352 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : UPDTLP');
353 FND_FILE.NEW_LINE(FND_FILE.LOG);
354 ELSE
355 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : UPDTLP');
356 FND_FILE.NEW_LINE(FND_FILE.LOG);
357 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
358 FND_FILE.NEW_LINE(FND_FILE.LOG);
359 retcode := '2';
360 errbuff := 'Error';
361 END IF;
362 END LOOP;
363 END IF;
364 END LOOP;
365 IF NOT l_flag THEN
366 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : UPDTLP');
367 FND_FILE.NEW_LINE(FND_FILE.LOG);
368 END IF;
369 l_flag := FALSE;
370 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
371 'INPL') LOOP
372 l_flag := TRUE;
373 l_setup_flag := TRUE;
374 --Check for Send Notification for Inbound Price list
375 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
376 FOR get_lines_for_notifinpl_rec IN get_lines_for_notifinpl_csr(get_approved_txn_rec.transaction_header_id)
377 LOOP
378 l_txn_hdr_rec := NULL;
379 l_txn_line_id.delete();
380 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
381 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
382 l_txn_hdr_rec.Process_code := 'INPL';
383 --Raise business event for Send Notification for Inbound Price list
384 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
385 ,p_init_msg_list => FND_API.G_FALSE
386 ,p_commit => FND_API.G_FALSE
390 ,x_msg_data => l_msg_data
387 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
388 ,x_return_status => l_return_status
389 ,x_msg_count => l_msg_count
391 ,p_txn_hdr_rec => l_txn_hdr_rec
392 ,p_txn_line_id => l_txn_line_id
393 );
394 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
395 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : INPL');
396 FND_FILE.NEW_LINE(FND_FILE.LOG);
397 ELSE
398 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : INPL');
399 FND_FILE.NEW_LINE(FND_FILE.LOG);
400 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
401 FND_FILE.NEW_LINE(FND_FILE.LOG);
402 retcode := '2';
403 errbuff := 'Error';
404 END IF;
405 END LOOP;
406 END IF;
407 END LOOP;
408 IF NOT l_flag THEN
409 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : INPL');
410 FND_FILE.NEW_LINE(FND_FILE.LOG);
411 END IF;
412 l_flag := FALSE;
413 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
414 'OUTPL') LOOP
415 l_flag := TRUE;
416 l_setup_flag := TRUE;
417 --Check for Send Notification for Outbound Price list
418 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
419 FOR get_lines_for_notifoutpl_rec IN get_lines_for_notifoutpl_csr(get_approved_txn_rec.transaction_header_id)
420 LOOP
421 l_txn_hdr_rec := NULL;
422 l_txn_line_id.delete();
423 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
424 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
425 l_txn_hdr_rec.Process_code := 'OUTPL';
426 --Raise business event for Send Notification for Outbound Price lists
427 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
428 ,p_init_msg_list => FND_API.G_FALSE
429 ,p_commit => FND_API.G_FALSE
430 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
431 ,x_return_status => l_return_status
432 ,x_msg_count => l_msg_count
433 ,x_msg_data => l_msg_data
434 ,p_txn_hdr_rec => l_txn_hdr_rec
435 ,p_txn_line_id => l_txn_line_id
436 );
437 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
438 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : OUTPL');
439 FND_FILE.NEW_LINE(FND_FILE.LOG);
440 ELSE
441 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : OUTPL');
442 FND_FILE.NEW_LINE(FND_FILE.LOG);
443 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
444 FND_FILE.NEW_LINE(FND_FILE.LOG);
445 retcode := '2';
446 errbuff := 'Error';
447 END IF;
448 END LOOP;
449 END IF;
450 END LOOP;
451 IF NOT l_flag THEN
452 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : OUTPL');
453 FND_FILE.NEW_LINE(FND_FILE.LOG);
454 END IF;
455 l_flag := FALSE;
456 FOR get_auto_flag_rec IN get_auto_flag_csr(l_supp_trade_profile_id,
457 'PROMO') LOOP
458 l_flag := TRUE;
459 l_setup_flag := TRUE;
460 --Check for Send Notification for Offers
461 IF get_auto_flag_rec.automatic_flag = 'Y' THEN
462 FOR get_lines_for_notifpromo_rec IN get_lines_for_notifpromo_csr(get_approved_txn_rec.transaction_header_id)
463 LOOP
464 l_txn_hdr_rec := NULL;
465 l_txn_line_id.delete();
466 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
467 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
468 l_txn_hdr_rec.Process_code := 'PROMO';
469 --Raise business event for Send Notification for Offers
470 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
471 ,p_init_msg_list => FND_API.G_FALSE
472 ,p_commit => FND_API.G_FALSE
473 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
474 ,x_return_status => l_return_status
475 ,x_msg_count => l_msg_count
479 );
476 ,x_msg_data => l_msg_data
477 ,p_txn_hdr_rec => l_txn_hdr_rec
478 ,p_txn_line_id => l_txn_line_id
480 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
481 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : PROMO');
482 FND_FILE.NEW_LINE(FND_FILE.LOG);
483 ELSE
484 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : PROMO');
485 FND_FILE.NEW_LINE(FND_FILE.LOG);
486 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
487 FND_FILE.NEW_LINE(FND_FILE.LOG);
488 retcode := '2';
489 errbuff := 'Error';
490 END IF;
491 END LOOP;
492 END IF;
493 END LOOP;
494 IF NOT l_flag THEN
495 FND_FILE.PUT_LINE(FND_FILE.LOG,'No records found in OZF_PROCESS_SETUP_ALL table for the process code : PROMO');
496 FND_FILE.NEW_LINE(FND_FILE.LOG);
497 END IF;
498 --Add here for POPINVDTLS and POPCUSTCLAIM as no setup is required.
499 IF l_setup_flag THEN
500 l_txn_hdr_rec := NULL;
501 l_txn_line_id.delete();
502 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
503 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
504 l_txn_hdr_rec.Process_code := 'POPINVDTLS';
505 --Raise business event for updating the on hand inventory
506 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
507 ,p_init_msg_list => FND_API.G_FALSE
508 ,p_commit => FND_API.G_FALSE
509 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
510 ,x_return_status => l_return_status
511 ,x_msg_count => l_msg_count
512 ,x_msg_data => l_msg_data
513 ,p_txn_hdr_rec => l_txn_hdr_rec
514 ,p_txn_line_id => l_txn_line_id
515 );
516 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
517 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : POPINVDTLS');
518 FND_FILE.NEW_LINE(FND_FILE.LOG);
519 ELSE
520 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : POPINVDTLS');
521 FND_FILE.NEW_LINE(FND_FILE.LOG);
522 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
523 FND_FILE.NEW_LINE(FND_FILE.LOG);
524 retcode := '2';
525 errbuff := 'Error';
526 END IF;
527 --Check if the customer claims tab is populated
528 FOR get_customer_claim_rec IN get_customer_claim_csr(get_approved_txn_rec.transaction_header_id) LOOP
529 l_txn_hdr_rec := NULL;
530 l_txn_line_id.delete();
531 l_txn_hdr_rec.Transaction_Header_ID := get_approved_txn_rec.transaction_header_id;
532 l_txn_hdr_rec.Transaction_number := get_approved_txn_rec.transaction_number;
533 l_txn_hdr_rec.Process_code := 'POPCUSTCLAIM';
534 --Raise business event for updating the customer claims..
535 DPP_BUSINESSEVENTS_PVT.Raise_Business_Event( p_api_version => 1.0
536 ,p_init_msg_list => FND_API.G_FALSE
537 ,p_commit => FND_API.G_FALSE
538 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
539 ,x_return_status => l_return_status
540 ,x_msg_count => l_msg_count
541 ,x_msg_data => l_msg_data
542 ,p_txn_hdr_rec => l_txn_hdr_rec
543 ,p_txn_line_id => l_txn_line_id
544 );
545 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
546 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event raised for the process code : POPCUSTCLAIM');
547 FND_FILE.NEW_LINE(FND_FILE.LOG);
548 ELSE
549 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business event not raised for the process code : POPCUSTCLAIM');
550 FND_FILE.NEW_LINE(FND_FILE.LOG);
551 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(('Error Message : '||l_msg_data),1,4000));
552 FND_FILE.NEW_LINE(FND_FILE.LOG);
553 retcode := '2';
554 errbuff := 'Error';
555 END IF;
556 END LOOP;
557 ELSE
558 FND_FILE.PUT_LINE(FND_FILE.LOG,'No setup found in OZF_PROCESS_SETUP_ALL table for the supplier of the
559 transaction number '||l_txn_hdr_rec.Transaction_number);
560 FND_FILE.NEW_LINE(FND_FILE.LOG);
561 END IF;
562 END LOOP;
563
564 --Insert the required process codes into the DPP_EXECUTION_PROCESSES table
565 FOR get_valid_transaction_rec IN get_valid_transaction_csr(p_in_txn_number)
566 LOOP
567 IF l_setup_flag THEN --Check whether execution setup is available for the supplier
568 --Update the status of the transaction from APPROVED to ACTIVE.
569 BEGIN
570 UPDATE dpp_transaction_headers_all
571 SET transaction_status = 'ACTIVE',
572 object_version_number = object_version_number + 1,
573 last_updated_by = l_user_id,
574 last_update_date = sysdate,
575 last_update_login = l_login_id
576 WHERE transaction_header_id = get_valid_transaction_rec.transaction_header_id;
577 EXCEPTION
578 WHEN OTHERS THEN
579 ROLLBACK;
580 retcode := '1';
581 errbuff := 'When Others Exception'||SQLERRM;
582 FND_FILE.PUT_LINE(FND_FILE.LOG,'When Others Exception'||SQLERRM);
583 FND_FILE.NEW_LINE(FND_FILE.LOG);
584 END;
585 FOR get_process_codes_rec IN get_process_codes_csr(l_supp_trade_profile_id)
586 LOOP
587 --Insert the Process codes into the DPP_EXECUTION_PROCESSES table
588 INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
589 transaction_header_id,
590 created_by,
591 creation_date,
592 last_updated_by,
593 last_update_date,
594 last_update_login
595 )
596 VALUES (get_process_codes_rec.lookup_code,
597 get_valid_transaction_rec.transaction_header_id,
598 l_user_id,
599 sysdate,
600 l_user_id,
601 sysdate,
602 l_login_id
603 );
604 END LOOP;
605 ELSE
606 fnd_message.set_name( 'DPP', 'DPP_EXECUTION_SETUP_NOT_EXIST');
607 fnd_message.set_token('TXN_NUMBER', p_in_txn_number);
608 fnd_msg_pub.add;
609 FND_FILE.PUT_LINE(FND_FILE.LOG,'The transaction cannot be made Active as execution processes setup is not availeble for the
610 transaction number '||p_in_txn_number);
611 FND_FILE.NEW_LINE(FND_FILE.LOG);
612 ROLLBACK;
613 retcode := '1';
614 errbuff := 'No Execution processes setup available for this transaction ';
615 END IF;
616 END LOOP;
617
618 --Commit thechanges
619 COMMIT;
620
621 EXCEPTION
622
623 WHEN NO_DATA_FOUND THEN
624 ROLLBACK;
625 retcode := '1';
626 errbuff := 'No Data Found.....';
627 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Found.....');
628 FND_FILE.NEW_LINE(FND_FILE.LOG);
629
630 WHEN OTHERS THEN
631 ROLLBACK;
632 retcode := '1';
633 errbuff := 'When Others Exception'||SQLERRM;
634 FND_FILE.PUT_LINE(FND_FILE.LOG,'When Others Exception'||SQLERRM);
635 FND_FILE.NEW_LINE(FND_FILE.LOG);
636 END Initiate_ExecutionProcess;
637 END DPP_EXECUTIONPROCESS_PUB;