[Home] [Help]
PACKAGE BODY: APPS.DPP_MIG_ADJ_PARA_APPROVAL_PVT
Source
1 PACKAGE BODY DPP_MIG_ADJ_PARA_APPROVAL_PVT AS
2 /* $Header: dppmigsb.pls 120.5 2009/07/28 07:19:50 anbbalas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_MIG_ADJ_PARA_APPROVAL_PVT';
5 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
6 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppmigsb.pls';
7
8 ---------------------------------------------------------------------
9 -- PROCEDURE
10 -- checkMigrationCompletion
11 --
12 -- PURPOSE
13 -- This procedure checks if the migration has already been
14 -- completed successfully. If so, it will terminate.
15 --
16 -- PARAMETERS
17 --
18 -- NOTES
19 --
20 ----------------------------------------------------------------------
21 PROCEDURE checkMigrationCompletion
22 ( p_api_version IN NUMBER
23 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
24 , p_commit IN VARCHAR2 := fnd_api.g_false
25 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
26 , x_return_status OUT nocopy VARCHAR2
27 , x_msg_count OUT nocopy NUMBER
28 , x_msg_data OUT nocopy VARCHAR2
29 )
30 AS
31 l_api_name constant VARCHAR2(30) := 'checkMigrationCompletion';
32 l_api_version constant NUMBER := 1.0;
33 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
34
35 l_return_status VARCHAR2(30);
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(4000);
38
39 l_txn_count NUMBER;
40
41 --Cursor to check if there are transactions with no entries in the dpp_execution_processes table
42 CURSOR validTransactionsCur
43 IS
44 SELECT count(1)
45 FROM dpp_transaction_headers_all dpp
46 WHERE NOT EXISTS
47 (SELECT dep.transaction_header_id
48 FROM dpp_execution_processes dep
49 WHERE dep.transaction_header_id = dpp.transaction_header_id);
50
51
52 BEGIN
53 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
54 Fnd_Msg_Pub.initialize;
55 END IF;
56
57 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
58 p_api_version,
59 l_api_name,
60 g_pkg_name
61 )
62 THEN
63 RAISE Fnd_Api.g_exc_unexpected_error;
64 END IF;
65
66 -- Initialize API return status to sucess
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 IF G_DEBUG THEN
70 fnd_file.put_line(fnd_file.log, ' Begin checkMigrationCompletion ' );
71 END IF;
72
73 OPEN validTransactionsCur;
74 FETCH validTransactionsCur INTO l_txn_count;
75 CLOSE validTransactionsCur;
76
77 fnd_file.put_line(fnd_file.log, ' Transactions with no entries in the dpp_execution_processes table - ' || l_txn_count );
78
79 IF l_txn_count = 0 THEN
80 IF G_DEBUG THEN
81 fnd_file.put_line(fnd_file.log, ' Migration has already been performed. ' );
82 END IF;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 EXCEPTION
86 WHEN FND_API.G_EXC_ERROR THEN
87 x_return_status := FND_API.G_RET_STS_ERROR;
88 x_msg_data := fnd_message.get_string('DPP','DPP_MIG_COMPLETED_ERR');
89
90 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
92 -- Standard call to get message count and if count=1, get the message
93 FND_MSG_PUB.Count_And_Get (
94 p_encoded => FND_API.G_FALSE,
95 p_count => x_msg_count,
96 p_data => x_msg_data
97 );
98 IF x_msg_count > 1 THEN
99 FOR I IN 1..x_msg_count LOOP
100 x_msg_data := SUBSTR((x_msg_data ||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
101 END LOOP;
102 END IF;
103
104 WHEN OTHERS THEN
105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
106 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
107 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.checkMigrationCompletion');
108 fnd_message.set_token('ERRNO', sqlcode);
109 fnd_message.set_token('REASON', sqlerrm);
110 FND_MSG_PUB.add;
111
112 -- Standard call to get message count and if count=1, get the message
113 FND_MSG_PUB.Count_And_Get (
114 p_encoded => FND_API.G_FALSE,
115 p_count => x_msg_count,
116 p_data => x_msg_data
117 );
118 IF x_msg_count > 1 THEN
119 FOR I IN 1..x_msg_count LOOP
120 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
121 END LOOP;
122 END IF;
123
124 END checkMigrationCompletion;
125
126 ---------------------------------------------------------------------
127 -- PROCEDURE
128 -- validate_suppTradeProfile
129 --
130 -- PURPOSE
131 -- This procedure checks for the existence of the supplier trade profile
132 -- for the supplier and supplier site for the operating unit
133 --
134 -- PARAMETERS
135 --
136 -- NOTES
137 --
138 ----------------------------------------------------------------------
139 PROCEDURE validate_suppTradeProfile
140 ( p_api_version IN NUMBER
141 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
142 , p_commit IN VARCHAR2 := fnd_api.g_false
143 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
144 , x_return_status OUT nocopy VARCHAR2
145 , x_msg_count OUT nocopy NUMBER
146 , x_msg_data OUT nocopy VARCHAR2
147 )
148 AS
149 l_api_name constant VARCHAR2(30) := 'validate_suppTradeProfile';
150 l_api_version constant NUMBER := 1.0;
151 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
152
153 l_return_status VARCHAR2(30);
154 l_msg_count NUMBER;
155 l_msg_data VARCHAR2(4000);
156
157 l_vendor_id NUMBER;
158 l_vendor_name VARCHAR2(240);
159 l_vendor_site_id NUMBER;
160 l_vendor_site_code VARCHAR2(15);
161 l_org_id NUMBER;
162 l_operating_unit VARCHAR2(240);
163
164 l_setup_missing VARCHAR2(1) := 'N';
165
166 --Cursor to fetch suppliers without supplier trade profile setup
167 CURSOR suppTradeProfile_Cur
168 IS
169 select distinct vendor_id, vendor_site_id, org_id
170 from dpp_transaction_headers_all dtha
171 where not exists (select supp_trade_profile_id
172 from ozf_supp_trd_prfls_all ostpa
173 where ostpa.supplier_id = dtha.vendor_id
174 and ostpa.supplier_site_id = dtha.vendor_site_id
175 and ostpa.org_id = dtha.org_id);
176
177 --Cursor to fetch supplier details
178 CURSOR supplier_details_cur(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_org_id IN NUMBER)
179 IS
180 select aps.vendor_id, aps.vendor_name, apss.vendor_site_id, apss.vendor_site_code,
181 apss.org_id, hr.name
182 from ap_suppliers aps, ap_supplier_sites_all apss, hr_operating_units hr
183 where aps.vendor_id = p_vendor_id
184 and aps.vendor_id = apss.vendor_id
185 and apss.vendor_site_id = p_vendor_site_id
186 and apss.org_id = p_org_id
187 and apss.org_id = hr.organization_id;
188
189
190 BEGIN
191 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
192 Fnd_Msg_Pub.initialize;
193 END IF;
194
195 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
196 p_api_version,
197 l_api_name,
198 g_pkg_name
199 )
200 THEN
201 RAISE Fnd_Api.g_exc_unexpected_error;
202 END IF;
203
204 -- Initialize API return status to sucess
205 x_return_status := fnd_api.g_ret_sts_success;
206
207 IF G_DEBUG THEN
208 fnd_file.put_line(fnd_file.log, ' Begin validate_suppTradeProfile ' );
209 END IF;
210
211 FOR suppTradeProfile_rec IN suppTradeProfile_Cur
212 LOOP
213 l_setup_missing := 'Y';
214 OPEN supplier_details_cur(suppTradeProfile_rec.vendor_id,
215 suppTradeProfile_rec.vendor_site_id,
216 suppTradeProfile_rec.org_id);
217 FETCH supplier_details_cur INTO l_vendor_id, l_vendor_name,
218 l_vendor_site_id, l_vendor_site_code,
219 l_org_id, l_operating_unit;
220 CLOSE supplier_details_cur;
221
222 fnd_file.put_line(fnd_file.log, ' Supplier Trade Profile setup not available for ' );
223 fnd_file.put_line(fnd_file.log, ' Supplier - ' || l_vendor_name || ' (' || l_vendor_id || ')' );
224 fnd_file.put_line(fnd_file.log, ' Supplier Site - ' || l_vendor_site_code || ' (' || l_vendor_site_id || ')' );
225 fnd_file.put_line(fnd_file.log, ' Operating Unit - ' || l_operating_unit || ' (' || l_org_id || ')' );
226
227 END LOOP;
228
229 IF l_setup_missing = 'Y' THEN
230 fnd_file.put_line(fnd_file.log, ' Please perform the Supplier Trade Profile setup. ' );
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233 EXCEPTION
234 WHEN FND_API.G_EXC_ERROR THEN
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 -- Standard call to get message count and if count=1, get the message
237 FND_MSG_PUB.Count_And_Get (
238 p_encoded => FND_API.G_FALSE,
239 p_count => x_msg_count,
240 p_data => x_msg_data
241 );
242 IF x_msg_count > 1 THEN
243 FOR I IN 1..x_msg_count LOOP
244 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
245 END LOOP;
246 END IF;
247
248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 -- Standard call to get message count and if count=1, get the message
251 FND_MSG_PUB.Count_And_Get (
252 p_encoded => FND_API.G_FALSE,
253 p_count => x_msg_count,
254 p_data => x_msg_data
255 );
256 IF x_msg_count > 1 THEN
257 FOR I IN 1..x_msg_count LOOP
258 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
259 END LOOP;
260 END IF;
261
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
265 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.validate_suppTradeProfile');
266 fnd_message.set_token('ERRNO', sqlcode);
267 fnd_message.set_token('REASON', sqlerrm);
268 FND_MSG_PUB.add;
269
270 -- Standard call to get message count and if count=1, get the message
271 FND_MSG_PUB.Count_And_Get (
272 p_encoded => FND_API.G_FALSE,
273 p_count => x_msg_count,
274 p_data => x_msg_data
275 );
276 IF x_msg_count > 1 THEN
277 FOR I IN 1..x_msg_count LOOP
278 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
279 END LOOP;
280 END IF;
281
282 END validate_suppTradeProfile;
283
284 ---------------------------------------------------------------------
285 -- PROCEDURE
286 -- validate_execProcessSetup
287 --
288 -- PURPOSE
289 -- This procedure checks for the existence of the price protection
290 -- execution process setup at the system parameters in Trade Management
291 --
292 -- PARAMETERS
293 --
294 -- NOTES
295 --
296 ----------------------------------------------------------------------
297 PROCEDURE validate_execProcessSetup
298 ( p_api_version IN NUMBER
299 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
300 , p_commit IN VARCHAR2 := fnd_api.g_false
301 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
302 , x_return_status OUT nocopy VARCHAR2
303 , x_msg_count OUT nocopy NUMBER
304 , x_msg_data OUT nocopy VARCHAR2
305 )
306 AS
307 l_api_name constant VARCHAR2(30) := 'validate_execProcessSetup';
308 l_api_version constant NUMBER := 1.0;
309 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
310
311 l_return_status VARCHAR2(30);
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(4000);
314
315 l_count NUMBER := 0;
316 l_org_id NUMBER;
317 l_operating_unit VARCHAR2(240);
318 l_setup_missing VARCHAR2(1) := 'N';
319
320 --Cursor to fetch distinct operating unit
321 CURSOR orgId_Cur
322 IS
323 SELECT DISTINCT org_id
324 FROM dpp_transaction_headers_all;
325
326 --Cursor to check if the execution process setup exists at the system parameters
327 CURSOR get_process_setup_cnt_csr (p_org_id NUMBER)
328 IS
329 SELECT COUNT(1)
330 FROM ozf_process_setup_all
331 WHERE nvl(supp_trade_profile_id,0) = 0
332 AND enabled_flag = 'Y'
333 AND org_id = p_org_id;
334
335 --Cursor to fetch the operating unit name
336 CURSOR operating_unit_cur(p_org_id IN NUMBER)
337 IS
338 select hr.name
339 from hr_operating_units hr
340 where hr.organization_id = p_org_id;
341
342 BEGIN
343 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
344 Fnd_Msg_Pub.initialize;
345 END IF;
346
347 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
348 p_api_version,
349 l_api_name,
350 g_pkg_name
351 )
352 THEN
353 RAISE Fnd_Api.g_exc_unexpected_error;
354 END IF;
355
356 -- Initialize API return status to sucess
357 x_return_status := fnd_api.g_ret_sts_success;
358
359 IF G_DEBUG THEN
360 fnd_file.put_line(fnd_file.log, ' Begin validate_execProcessSetup ' );
361 END IF;
362
363 FOR orgId_rec IN orgId_Cur
364 LOOP
365 l_org_id := orgId_rec.org_id;
366 OPEN get_process_setup_cnt_csr(l_org_id);
367 FETCH get_process_setup_cnt_csr INTO l_count;
368 CLOSE get_process_setup_cnt_csr;
369
370 IF G_DEBUG THEN
371 fnd_file.put_line(fnd_file.log, ' No of execution processes enabled for the org ' || l_org_id || ' is ' || l_count );
372 END IF;
373
374 IF l_count = 0 THEN
375 OPEN operating_unit_cur(l_org_id);
376 FETCH operating_unit_cur INTO l_operating_unit;
377 CLOSE operating_unit_cur;
378
379 l_setup_missing := 'Y';
380
381 fnd_file.put_line(fnd_file.log, ' Execution Process setup not available for Operating Unit - ' || l_operating_unit || ' (' || l_org_id || ')' );
382 END IF;
383
384 END LOOP;
385
386 IF l_setup_missing = 'Y' THEN
387 IF G_DEBUG THEN
388 fnd_file.put_line(fnd_file.log, ' Please perform the Execution Process setup. ' );
389 END IF;
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392 EXCEPTION
393 WHEN FND_API.G_EXC_ERROR THEN
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 x_msg_data := fnd_message.get_string('DPP','DPP_PROCESS_SETUP_MISSING_ERR');
396
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 -- Standard call to get message count and if count=1, get the message
400 FND_MSG_PUB.Count_And_Get (
401 p_encoded => FND_API.G_FALSE,
402 p_count => x_msg_count,
403 p_data => x_msg_data
404 );
405 IF x_msg_count > 1 THEN
406 FOR I IN 1..x_msg_count LOOP
407 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
408 END LOOP;
409 END IF;
410
411 WHEN OTHERS THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
414 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.validate_execProcessSetup');
415 fnd_message.set_token('ERRNO', sqlcode);
416 fnd_message.set_token('REASON', sqlerrm);
417 FND_MSG_PUB.add;
418
419 -- Standard call to get message count and if count=1, get the message
420 FND_MSG_PUB.Count_And_Get (
421 p_encoded => FND_API.G_FALSE,
422 p_count => x_msg_count,
423 p_data => x_msg_data
424 );
425 IF x_msg_count > 1 THEN
426 FOR I IN 1..x_msg_count LOOP
427 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
428 END LOOP;
429 END IF;
430
431 END validate_execProcessSetup;
432
433 ---------------------------------------------------------------------
434 -- PROCEDURE
435 -- insertExecutionProcesses
436 --
437 -- PURPOSE
438 -- This procedure inserts records into the DPP_EXECUTION_PROCESSES
439 -- for those transactions for which the entries does not exist. Entires
440 -- are based on either supplier trade profile or system parameters
441 -- in Trade Management.
442 --
443 -- PARAMETERS
444 --
445 -- NOTES
446 --
447 ----------------------------------------------------------------------
448 PROCEDURE insertExecutionProcesses
449 ( p_api_version IN NUMBER
450 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
451 , p_commit IN VARCHAR2 := fnd_api.g_false
452 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
453 , x_return_status OUT nocopy VARCHAR2
454 , x_msg_count OUT nocopy NUMBER
455 , x_msg_data OUT nocopy VARCHAR2
456 )
457 AS
458 l_api_name constant VARCHAR2(30) := 'insertExecutionProcesses';
459 l_api_version constant NUMBER := 1.0;
460 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
461
462 l_return_status VARCHAR2(30);
463 l_msg_count NUMBER;
464 l_msg_data VARCHAR2(4000);
465
466 l_user_id NUMBER := FND_GLOBAL.USER_ID;
467 l_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
468
469 l_transaction_header_id NUMBER;
470 l_supp_trade_profile_id NUMBER;
471 l_org_id NUMBER;
472
473 l_count NUMBER := 0;
474
475 TYPE ProcessCodeTab IS TABLE OF DPP_EXECUTION_PROCESSES.PROCESS_CODE%TYPE INDEX BY PLS_INTEGER;
476 process_codes ProcessCodeTab;
477
478 --Cursor to fetch those transactions with no entries in the dpp_execution_processes table
479 CURSOR get_valid_transaction_csr
480 IS
481 SELECT transaction_header_id
482 FROM dpp_transaction_headers_all dpp
483 WHERE NOT EXISTS
484 (SELECT dep.transaction_header_id
485 FROM dpp_execution_processes dep
486 WHERE dep.transaction_header_id = dpp.transaction_header_id);
487
488 --Cursor to retrieve the supplier trade profile id and org_id
489 CURSOR get_supp_trd_prfl_csr (p_transaction_header_id NUMBER)
490 IS
491 SELECT ostpa.supp_trade_profile_id, dtha.org_id
492 FROM dpp_transaction_headers_all dtha, ozf_supp_trd_prfls_all ostpa
493 WHERE dtha.transaction_header_id = p_transaction_header_id
494 AND dtha.vendor_id = ostpa.supplier_id
495 AND dtha.vendor_site_id = ostpa.supplier_site_id
496 AND dtha.org_id = ostpa.org_id;
497
498 --Cursor to check if the execution process setup is available either at Supplier Trade Profile or System Parameters
499 CURSOR get_process_setup_cnt_csr (p_supp_trade_profile_id NUMBER, p_org_id NUMBER)
500 IS
501 SELECT COUNT(1)
502 FROM OZF_PROCESS_SETUP_ALL
503 WHERE nvl(supp_trade_profile_id,0) = nvl(p_supp_trade_profile_id,0)
504 AND enabled_flag = 'Y'
505 AND org_id = p_org_id;
506
507 --Cursor to fetch the processes either at supplier trade profile or system parameters
508 CURSOR get_process_codes_csr (p_supp_trd_prf_id NUMBER, p_org_id NUMBER)
509 IS
510 SELECT dppl.lookup_code
511 FROM dpp_lookups dppl,
512 ozf_process_setup_all opsa
513 WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
514 AND dppl.tag is not null
515 AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
516 AND opsa.enabled_flag = 'Y'
517 AND opsa.org_id = p_org_id
518 AND dppl.lookup_code = opsa.process_code;
519
520 BEGIN
521 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
522 Fnd_Msg_Pub.initialize;
523 END IF;
524
525 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
526 p_api_version,
527 l_api_name,
528 g_pkg_name
529 )
530 THEN
531 RAISE Fnd_Api.g_exc_unexpected_error;
532 END IF;
533
534 -- Initialize API return status to sucess
535 x_return_status := fnd_api.g_ret_sts_success;
536
537 IF G_DEBUG THEN
538 fnd_file.put_line(fnd_file.log, ' Begin insertExecutionProcesses ' );
539 END IF;
540
541 FOR get_valid_transaction_rec IN get_valid_transaction_csr
542 LOOP
543 --Get the supplier trade profile id and org id.
544 BEGIN
545 l_transaction_header_id := get_valid_transaction_rec.transaction_header_id;
546 OPEN get_supp_trd_prfl_csr(l_transaction_header_id);
547 FETCH get_supp_trd_prfl_csr INTO l_supp_trade_profile_id, l_org_id;
548 CLOSE get_supp_trd_prfl_csr;
549
550 IF G_DEBUG THEN
551 fnd_file.put_line(fnd_file.log, ' Supplier Trade Profile Id: ' || l_supp_trade_profile_id );
552 fnd_file.put_line(fnd_file.log, ' Org Id: ' || l_org_id );
553 END IF;
554 EXCEPTION
555 WHEN OTHERS THEN
556 fnd_file.put_line(fnd_file.log,'Exception while fetching supplier trade profile id and org id: ' || SQLERRM);
557 fnd_file.new_line(fnd_file.log);
558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
559 END;
560
561 --Check if the Process Setup is done for the Supplier, Supplier site and Operating Unit
562 BEGIN
563 OPEN get_process_setup_cnt_csr(l_supp_trade_profile_id, l_org_id);
564 FETCH get_process_setup_cnt_csr INTO l_count;
565 CLOSE get_process_setup_cnt_csr;
566
567 IF l_count = 0 THEN --Process Setup does not exist for the Supplier Trade Profile
568 l_supp_trade_profile_id := null;
569 IF G_DEBUG THEN
570 fnd_file.put_line(fnd_file.log, ' Process Setup does not exist for ' );
571 fnd_file.put_line(fnd_file.log, ' Supplier Trade Profile Id: ' || l_supp_trade_profile_id );
572 fnd_file.put_line(fnd_file.log, ' Org Id: ' || l_org_id );
573 END IF;
574 END IF;
575 EXCEPTION
576 WHEN OTHERS THEN
577 fnd_file.put_line(fnd_file.log,'Exception while checking if the process setup exists: ' || SQLERRM);
578 fnd_file.new_line(fnd_file.log);
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END;
581
582 --Get the process code either from either supplier trade profile or system parameters
583 BEGIN
584 OPEN get_process_codes_csr(l_supp_trade_profile_id,l_org_id);
585 FETCH get_process_codes_csr BULK COLLECT INTO process_codes;
586 FORALL idx IN 1..process_codes.COUNT
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 (process_codes(idx),
597 l_transaction_header_id,
598 l_user_id,
599 sysdate,
600 l_user_id,
601 sysdate,
602 l_login_id
603 );
604 CLOSE get_process_codes_csr;
605 EXCEPTION
606 WHEN OTHERS THEN
607 fnd_file.put_line(fnd_file.log,'Exception while fetching the process code and inserting into DPP_EXECUTION_PROCESSES: ' || SQLERRM);
608 fnd_file.new_line(fnd_file.log);
609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
610 END;
611 END LOOP;
612
613 EXCEPTION
614 WHEN FND_API.G_EXC_ERROR THEN
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 -- Standard call to get message count and if count=1, get the message
617 FND_MSG_PUB.Count_And_Get (
618 p_encoded => FND_API.G_FALSE,
619 p_count => x_msg_count,
620 p_data => x_msg_data
621 );
622 IF x_msg_count > 1 THEN
623 FOR I IN 1..x_msg_count LOOP
624 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
625 END LOOP;
626 END IF;
627
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630 -- Standard call to get message count and if count=1, get the message
631 FND_MSG_PUB.Count_And_Get (
632 p_encoded => FND_API.G_FALSE,
633 p_count => x_msg_count,
634 p_data => x_msg_data
635 );
636 IF x_msg_count > 1 THEN
637 FOR I IN 1..x_msg_count LOOP
638 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
639 END LOOP;
640 END IF;
641
642 WHEN OTHERS THEN
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
645 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.insertExecutionProcesses');
646 fnd_message.set_token('ERRNO', sqlcode);
647 fnd_message.set_token('REASON', sqlerrm);
648 FND_MSG_PUB.add;
649
650 -- Standard call to get message count and if count=1, get the message
651 FND_MSG_PUB.Count_And_Get (
652 p_encoded => FND_API.G_FALSE,
653 p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656 IF x_msg_count > 1 THEN
657 FOR I IN 1..x_msg_count LOOP
658 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
659 END LOOP;
660 END IF;
661
662 END insertExecutionProcesses;
663
664 ---------------------------------------------------------------------
665 -- PROCEDURE
666 -- clearAllApprovals
667 --
668 -- PURPOSE
669 -- This procedure fetches all the futured dated transactions in
670 -- APPROVED, REJECTED and PENDING_APPROVAL status. All such
671 -- transactions will be updated ACTIVE status. Further, entries in
672 -- the DPP_APPROVAL_ACCESS and AME_TEMP_OLD_APPROVER_LISTS tables
673 -- corresponding to all such transactions will be deleted.
674 --
675 -- PARAMETERS
676 --
677 -- NOTES
678 --
679 ----------------------------------------------------------------------
680 PROCEDURE clearAllApprovals
681 ( p_api_version IN NUMBER
682 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
683 , p_commit IN VARCHAR2 := fnd_api.g_false
684 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
685 , x_return_status OUT nocopy VARCHAR2
686 , x_msg_count OUT nocopy NUMBER
687 , x_msg_data OUT nocopy VARCHAR2
688 )
689 AS
690 l_api_name constant VARCHAR2(30) := 'clearAllApprovals';
691 l_api_version constant NUMBER := 1.0;
692 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
693
694 l_return_status VARCHAR2(30);
695 l_msg_count NUMBER;
696 l_msg_data VARCHAR2(4000);
697
698 l_application_id NUMBER := 9000;
699 l_transaction_type VARCHAR2(30) := 'PRICE PROTECTION';
700 l_transaction_header_id VARCHAR2(50);
701 l_transaction_number VARCHAR2(40);
702
703 --Cursor to fetch the future dated transactions in APPROVED, REJECTED and PENDING_APPROVAL status.
704 CURSOR validTransactionsCur
705 IS
706 select transaction_header_id, transaction_number
707 from dpp_transaction_headers_all
708 where transaction_status IN ( 'APPROVED' , 'REJECTED' , 'PENDING_APPROVAL' )
709 and trunc(effective_start_date) > trunc(sysdate);
710
711 BEGIN
712 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
713 Fnd_Msg_Pub.initialize;
714 END IF;
715
716 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
717 p_api_version,
718 l_api_name,
719 g_pkg_name
720 )
721 THEN
722 RAISE Fnd_Api.g_exc_unexpected_error;
723 END IF;
724
725 -- Initialize API return status to sucess
726 x_return_status := fnd_api.g_ret_sts_success;
727
728 IF G_DEBUG THEN
729 fnd_file.put_line(fnd_file.log, ' Begin clearAllApprovals ' );
730 END IF;
731
732 BEGIN
733 --Delete the approval access
734 delete from dpp_approval_access
735 where object_id in ( select transaction_header_id
736 from dpp_transaction_headers_all
737 where transaction_status IN ( 'APPROVED' , 'REJECTED' , 'PENDING_APPROVAL' )
738 and trunc(effective_start_date) > trunc(sysdate) );
739
740 IF G_DEBUG THEN
741 fnd_file.put_line(fnd_file.log, ' Transactions approval entries have been deleted from DPP_APPROVAL_ACCESS' );
742 END IF;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
747 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.clearAllApprovals');
748 fnd_message.set_token('ERRNO', sqlcode);
749 fnd_message.set_token('REASON', sqlerrm);
750 fnd_msg_pub.add;
751 RAISE fnd_api.g_exc_unexpected_error;
752 END;
753
754 FOR validTransactionsRec IN validTransactionsCur
755 LOOP
756 l_transaction_header_id := validTransactionsRec.transaction_header_id;
757 l_transaction_number := validTransactionsRec.transaction_number;
758
759 BEGIN
760 --Clear the approvals in AME
761 ame_api2.clearAllApprovals(l_application_id, l_transaction_type, l_transaction_header_id);
762 IF G_DEBUG THEN
763 fnd_file.put_line(fnd_file.log, ' Clear All Approvals from AME for the transaction ' || l_transaction_number );
764 END IF;
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
769 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.clearAllApprovals');
770 fnd_message.set_token('ERRNO', sqlcode);
771 fnd_message.set_token('REASON', sqlerrm);
772 FND_MSG_PUB.add;
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774 END;
775 END LOOP;
776
777 EXCEPTION
778 WHEN FND_API.G_EXC_ERROR THEN
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 -- Standard call to get message count and if count=1, get the message
781 FND_MSG_PUB.Count_And_Get (
782 p_encoded => FND_API.G_FALSE,
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786 IF x_msg_count > 1 THEN
787 FOR I IN 1..x_msg_count LOOP
788 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
789 END LOOP;
790 END IF;
791
792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 -- Standard call to get message count and if count=1, get the message
795 FND_MSG_PUB.Count_And_Get (
796 p_encoded => FND_API.G_FALSE,
797 p_count => x_msg_count,
798 p_data => x_msg_data
799 );
800 IF x_msg_count > 1 THEN
801 FOR I IN 1..x_msg_count LOOP
802 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
803 END LOOP;
804 END IF;
805
806 WHEN OTHERS THEN
807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
808 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
809 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.clearAllApprovals');
810 fnd_message.set_token('ERRNO', sqlcode);
811 fnd_message.set_token('REASON', sqlerrm);
812 FND_MSG_PUB.add;
813
814 -- Standard call to get message count and if count=1, get the message
815 FND_MSG_PUB.Count_And_Get (
816 p_encoded => FND_API.G_FALSE,
817 p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820 IF x_msg_count > 1 THEN
821 FOR I IN 1..x_msg_count LOOP
822 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
823 END LOOP;
824 END IF;
825
826 END clearAllApprovals;
827
828 ---------------------------------------------------------------------
829 -- PROCEDURE
830 -- update_status
831 --
832 -- PURPOSE
833 -- This procedure updates the status of the transactions
834 --
835 -- PARAMETERS
836 --
837 -- NOTES
838 --
839 ----------------------------------------------------------------------
840 PROCEDURE update_status
841 ( p_api_version IN NUMBER
842 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
843 , p_commit IN VARCHAR2 := fnd_api.g_false
844 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
845 , x_return_status OUT nocopy VARCHAR2
846 , x_msg_count OUT nocopy NUMBER
847 , x_msg_data OUT nocopy VARCHAR2
848 )
849 AS
850 l_api_name constant VARCHAR2(30) := 'update_status';
851 l_api_version constant NUMBER := 1.0;
852 l_full_name constant VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
853
854 l_user_id NUMBER := FND_GLOBAL.USER_ID;
855 l_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
856 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
857 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
858 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
859
860 l_return_status VARCHAR2(30);
861 l_msg_count NUMBER;
862 l_msg_data VARCHAR2(4000);
863
864 BEGIN
865 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
866 Fnd_Msg_Pub.initialize;
867 END IF;
868
869 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
870 p_api_version,
871 l_api_name,
872 g_pkg_name
873 )
874 THEN
875 RAISE Fnd_Api.g_exc_unexpected_error;
876 END IF;
877
878 -- Initialize API return status to sucess
879 x_return_status := fnd_api.g_ret_sts_success;
880
881 IF G_DEBUG THEN
882 fnd_file.put_line(fnd_file.log, ' Begin update_status ' );
883 END IF;
884
885 UPDATE dpp_transaction_headers_all
886 SET transaction_status = 'APPROVED',
887 object_version_number = object_version_number +1,
888 last_updated_by = l_user_id,
889 last_update_date = sysdate,
890 last_update_login = l_login_id,
891 request_id = l_request_id,
892 program_application_id = l_program_application_id,
893 program_id = l_program_id,
894 program_update_date = sysdate
895 WHERE transaction_status = 'ACTIVE';
896
897 fnd_file.put_line(fnd_file.log, ' No of transactions from ACTIVE to APPROVED: ' || SQL%ROWCOUNT );
898
899 UPDATE dpp_transaction_headers_all
900 SET transaction_status = 'PENDING_ADJUSTMENT',
901 object_version_number = object_version_number +1,
902 last_updated_by = l_user_id,
903 last_update_date = sysdate,
904 last_update_login = l_login_id,
905 request_id = l_request_id,
906 program_application_id = l_program_application_id,
907 program_id = l_program_id,
908 program_update_date = sysdate
909 WHERE transaction_status = 'NEW'
910 AND TRUNC(effective_start_date) <= TRUNC(SYSDATE);
911
912 fnd_file.put_line(fnd_file.log, ' No of transactions from NEW to PENDING_ADJUSTMENT: ' || SQL%ROWCOUNT );
913
914 UPDATE dpp_transaction_headers_all
915 SET transaction_status = 'ACTIVE',
916 object_version_number = object_version_number +1,
917 last_updated_by = l_user_id,
918 last_update_date = sysdate,
919 last_update_login = l_login_id,
920 request_id = l_request_id,
921 program_application_id = l_program_application_id,
922 program_id = l_program_id,
923 program_update_date = sysdate
924 WHERE transaction_status = 'NEW'
925 AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
926
927 fnd_file.put_line(fnd_file.log, ' No of transactions from NEW to ACTIVE: ' || SQL%ROWCOUNT );
928
929 UPDATE dpp_transaction_headers_all
930 SET transaction_status = 'ACTIVE',
931 object_version_number = object_version_number +1,
932 last_updated_by = l_user_id,
933 last_update_date = sysdate,
934 last_update_login = l_login_id,
935 request_id = l_request_id,
936 program_application_id = l_program_application_id,
937 program_id = l_program_id,
938 program_update_date = sysdate
939 WHERE transaction_status = 'APPROVED'
940 AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
941
942 fnd_file.put_line(fnd_file.log, ' No of transactions from APPROVED to ACTIVE: ' || SQL%ROWCOUNT );
943
944 UPDATE dpp_transaction_headers_all
945 SET transaction_status = 'ACTIVE',
946 object_version_number = object_version_number +1,
947 last_updated_by = l_user_id,
948 last_update_date = sysdate,
949 last_update_login = l_login_id,
950 request_id = l_request_id,
951 program_application_id = l_program_application_id,
952 program_id = l_program_id,
953 program_update_date = sysdate
954 WHERE transaction_status = 'REJECTED'
955 AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
956
957 fnd_file.put_line(fnd_file.log, ' No of transactions from REJECTED to ACTIVE: ' || SQL%ROWCOUNT );
958
959 UPDATE dpp_transaction_headers_all
960 SET transaction_status = 'ACTIVE',
961 object_version_number = object_version_number +1,
962 last_updated_by = l_user_id,
963 last_update_date = sysdate,
964 last_update_login = l_login_id,
965 request_id = l_request_id,
966 program_application_id = l_program_application_id,
967 program_id = l_program_id,
968 program_update_date = sysdate
969 WHERE transaction_status = 'PENDING_APPROVAL'
970 AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
971
972 fnd_file.put_line(fnd_file.log, ' No of transactions from PENDING_APPROVAL to ACTIVE: ' || SQL%ROWCOUNT );
973
974 EXCEPTION
975 WHEN FND_API.G_EXC_ERROR THEN
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 -- Standard call to get message count and if count=1, get the message
978 FND_MSG_PUB.Count_And_Get (
979 p_encoded => FND_API.G_FALSE,
980 p_count => x_msg_count,
981 p_data => x_msg_data
982 );
983 IF x_msg_count > 1 THEN
984 FOR I IN 1..x_msg_count LOOP
985 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
986 END LOOP;
987 END IF;
988
989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
991 -- Standard call to get message count and if count=1, get the message
992 FND_MSG_PUB.Count_And_Get (
993 p_encoded => FND_API.G_FALSE,
994 p_count => x_msg_count,
995 p_data => x_msg_data
996 );
997 IF x_msg_count > 1 THEN
998 FOR I IN 1..x_msg_count LOOP
999 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1000 END LOOP;
1001 END IF;
1002
1003 WHEN OTHERS THEN
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1005 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1006 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.update_status');
1007 fnd_message.set_token('ERRNO', sqlcode);
1008 fnd_message.set_token('REASON', sqlerrm);
1009 FND_MSG_PUB.add;
1010
1011 -- Standard call to get message count and if count=1, get the message
1012 FND_MSG_PUB.Count_And_Get (
1013 p_encoded => FND_API.G_FALSE,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017 IF x_msg_count > 1 THEN
1018 FOR I IN 1..x_msg_count LOOP
1019 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1020 END LOOP;
1021 END IF;
1022
1023 END update_status;
1024
1025 ---------------------------------------------------------------------
1026 -- PROCEDURE
1027 -- update_transaction_status
1028 --
1029 -- PURPOSE
1030 -- It updates the transaction statuses based on new status order rule
1031 --
1032 -- PARAMETERS
1033 --
1034 -- NOTES
1035 --
1036 ----------------------------------------------------------------------
1037
1038 PROCEDURE update_transaction_status(
1039 errbuf OUT NOCOPY VARCHAR2
1040 , retcode OUT NOCOPY VARCHAR2
1041 )
1042 IS
1043 l_api_name CONSTANT VARCHAR2(30) := 'update_transaction_status';
1044 l_api_version CONSTANT NUMBER := 1.0;
1045 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1046
1047 l_return_status VARCHAR2(30);
1048 l_msg_count NUMBER;
1049 l_msg_data VARCHAR2(4000);
1050
1051 l_init_msg_list VARCHAR2(30) := fnd_api.g_true;
1052 l_commit VARCHAR2(30) := fnd_api.g_false;
1053 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1054
1055 BEGIN
1056 -- Standard API savepoint
1057 SAVEPOINT update_transaction_status;
1058
1059 -- Initialize API return status to sucess
1060 errbuf :='Success';
1061 retcode := 0;
1062 l_return_status := FND_API.G_RET_STS_SUCCESS;
1063
1064 IF G_DEBUG THEN
1065 fnd_file.put_line(fnd_file.LOG,'Check if the migration has already been completed successfully.');
1066 END IF;
1067
1068 checkMigrationCompletion(
1069 p_api_version => l_api_version
1070 , p_init_msg_list => l_init_msg_list
1071 , p_commit => l_commit
1072 , p_validation_level => l_validation_level
1073 , x_return_status => l_return_status
1074 , x_msg_count => l_msg_count
1075 , x_msg_data => l_msg_data
1076 ) ;
1077
1078 IF G_DEBUG THEN
1079 fnd_file.put_line(fnd_file.log, ' Check migration completion. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1080 END IF;
1081
1082 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1083 fnd_file.put_line(fnd_file.log, l_msg_data);
1084 errbuf := 'Warning';
1085 retcode := 1;
1086 RETURN;
1087 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1088 RAISE Fnd_Api.g_exc_unexpected_error;
1089 END IF;
1090
1091 fnd_file.put_line(fnd_file.log,('-----------------------------------------------------------'));
1092 fnd_file.put_line(fnd_file.log,(' Migration started at ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')));
1093 fnd_file.put_line(fnd_file.log,('-----------------------------------------------------------'));
1094
1095 IF G_DEBUG THEN
1096 fnd_file.put_line(fnd_file.LOG,'Validate Supplier Trade Profile');
1097 END IF;
1098
1099 validate_suppTradeProfile(
1100 p_api_version => l_api_version
1101 , p_init_msg_list => l_init_msg_list
1102 , p_commit => l_commit
1103 , p_validation_level => l_validation_level
1104 , x_return_status => l_return_status
1105 , x_msg_count => l_msg_count
1106 , x_msg_data => l_msg_data
1107 ) ;
1108
1109 IF G_DEBUG THEN
1110 fnd_file.put_line(fnd_file.log, ' Validate Supplier Trade Profile. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1111 END IF;
1112
1113 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1114 RAISE Fnd_Api.g_exc_error;
1115 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1116 RAISE Fnd_Api.g_exc_unexpected_error;
1117 END IF;
1118
1119 IF G_DEBUG THEN
1120 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate Execution Process Setup');
1121 END IF;
1122
1123 validate_execProcessSetup(
1124 p_api_version => l_api_version
1125 , p_init_msg_list => l_init_msg_list
1126 , p_commit => l_commit
1127 , p_validation_level => l_validation_level
1128 , x_return_status => l_return_status
1129 , x_msg_count => l_msg_count
1130 , x_msg_data => l_msg_data
1131 ) ;
1132
1133 IF G_DEBUG THEN
1134 fnd_file.put_line(fnd_file.log, ' Validate Execution Process Setup. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1135 END IF;
1136
1137 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1138 fnd_file.put_line(fnd_file.log, l_msg_data);
1139 errbuf := 'Error';
1140 retcode := 2;
1141 RETURN;
1142 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1143 RAISE Fnd_Api.g_exc_unexpected_error;
1144 END IF;
1145
1146 IF G_DEBUG THEN
1147 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Execution Processes');
1148 END IF;
1149
1150 InsertExecutionProcesses(
1151 p_api_version => l_api_version
1152 , p_init_msg_list => l_init_msg_list
1153 , p_commit => l_commit
1154 , p_validation_level => l_validation_level
1155 , x_return_status => l_return_status
1156 , x_msg_count => l_msg_count
1157 , x_msg_data => l_msg_data
1158 ) ;
1159
1160 IF G_DEBUG THEN
1161 fnd_file.put_line(fnd_file.log, ' Insert Execution Processes. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1162 END IF;
1163
1164 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1165 RAISE Fnd_Api.g_exc_error;
1166 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1167 RAISE Fnd_Api.g_exc_unexpected_error;
1168 END IF;
1169
1170 IF G_DEBUG THEN
1171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Clear all the approvals in both Price Protection and AME');
1172 END IF;
1173
1174 clearAllApprovals(
1175 p_api_version => l_api_version
1176 , p_init_msg_list => l_init_msg_list
1177 , p_commit => l_commit
1178 , p_validation_level => l_validation_level
1179 , x_return_status => l_return_status
1180 , x_msg_count => l_msg_count
1181 , x_msg_data => l_msg_data
1182 ) ;
1183
1184 IF G_DEBUG THEN
1185 fnd_file.put_line(fnd_file.log, ' Clear All Approvals. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1186 END IF;
1187
1188 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1189 RAISE Fnd_Api.g_exc_error;
1190 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1191 RAISE Fnd_Api.g_exc_unexpected_error;
1192 END IF;
1193
1194 IF G_DEBUG THEN
1195 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update status');
1196 END IF;
1197
1198 update_status(
1199 p_api_version => l_api_version
1200 , p_init_msg_list => l_init_msg_list
1201 , p_commit => l_commit
1202 , p_validation_level => l_validation_level
1203 , x_return_status => l_return_status
1204 , x_msg_count => l_msg_count
1205 , x_msg_data => l_msg_data
1206 ) ;
1207
1208 IF G_DEBUG THEN
1209 fnd_file.put_line(fnd_file.log, ' Update status. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
1210 END IF;
1211
1212 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1213 RAISE Fnd_Api.g_exc_error;
1214 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1215 RAISE Fnd_Api.g_exc_unexpected_error;
1216 END IF;
1217
1218 COMMIT;
1219
1220 fnd_file.put_line(fnd_file.log,('-----------------------------------------------------------'));
1221 fnd_file.put_line(fnd_file.log,(' Migration completed at ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')));
1222 fnd_file.put_line(fnd_file.log,('-----------------------------------------------------------'));
1223
1224 EXCEPTION
1225 WHEN FND_API.G_EXC_ERROR THEN
1226 ROLLBACK TO update_transaction_status;
1227 errbuf :='Error';
1228 retcode := 2;
1229 -- Standard call to get message count and if count=1, get the message
1230 FND_MSG_PUB.Count_And_Get (
1231 p_encoded => FND_API.G_FALSE,
1232 p_count => l_msg_count,
1233 p_data => l_msg_data
1234 );
1235 IF l_msg_count > 1 THEN
1236 FOR I IN 1..l_msg_count LOOP
1237 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1238 END LOOP;
1239 END IF;
1240
1241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1242 ROLLBACK TO update_transaction_status;
1243 errbuf :='Error';
1244 retcode := 2;
1245 -- Standard call to get message count and if count=1, get the message
1246 FND_MSG_PUB.Count_And_Get (
1247 p_encoded => FND_API.G_FALSE,
1248 p_count => l_msg_count,
1249 p_data => l_msg_data
1250 );
1251 IF l_msg_count > 1 THEN
1252 FOR I IN 1..l_msg_count LOOP
1253 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1254 END LOOP;
1255 END IF;
1256
1257 WHEN OTHERS THEN
1258 ROLLBACK TO update_transaction_status;
1259 errbuf :='Error';
1260 retcode := 2;
1261 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1262 fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.update_transaction_status');
1263 fnd_message.set_token('ERRNO', sqlcode);
1264 fnd_message.set_token('REASON', sqlerrm);
1265 FND_MSG_PUB.add;
1266
1267 -- Standard call to get message count and if count=1, get the message
1268 FND_MSG_PUB.Count_And_Get (
1269 p_encoded => FND_API.G_FALSE,
1270 p_count => l_msg_count,
1271 p_data => l_msg_data
1272 );
1273 IF l_msg_count > 1 THEN
1274 FOR I IN 1..l_msg_count LOOP
1275 l_msg_data := SUBSTR((l_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1276 END LOOP;
1277 END IF;
1278
1279 END update_transaction_status;
1280
1281 END DPP_MIG_ADJ_PARA_APPROVAL_PVT;