[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_TERMNT_VENDOR_PRG_PVT
Source
1 PACKAGE BODY OKL_AM_TERMNT_VENDOR_PRG_PVT AS
2 /* $Header: OKLRTVPB.pls 120.3.12010000.2 2008/10/01 22:52:54 rkuttiya ship $ */
3
4 -- *********************
5 -- GLOBAL DATASTRUCTURES
6 -- *********************
7
8 -- Rec Type to Store Messages
9 TYPE msg_rec_type IS RECORD (
10 id NUMBER, -- Added
11 msg VARCHAR2(2000));
12
13 -- Table Type to Messages Rec
14 TYPE msg_tbl_type IS TABLE OF msg_rec_type INDEX BY BINARY_INTEGER;
15
16 -- Rec Type to Store Message details with IA details
17 TYPE message_rec_type IS RECORD (
18 id NUMBER,
19 va_number VARCHAR2(300),
20 start_date DATE,
21 end_date DATE,
22 status VARCHAR2(300) );
23
24 -- Table Type to Store Recs of Message details with IA details
25 TYPE message_tbl_type IS TABLE OF message_rec_type INDEX BY BINARY_INTEGER;
26
27 -- Rec Type to Store Lease K Details
28 TYPE va_k_rec_type IS RECORD (
29 id NUMBER,
30 va_number OKC_K_HEADERS_B.contract_number%TYPE,
31 start_date DATE,
32 end_date DATE,
33 sts_code OKC_K_HEADERS_B.sts_code%TYPE,
34 date_terminated DATE);
35
36 -- Table Type to store Recs of IA Details
37 TYPE va_k_tbl_type IS TABLE OF va_k_rec_type INDEX BY BINARY_INTEGER;
38
39 -- SUBTYPE the transaction Rec Type
40 SUBTYPE tcnv_rec_type IS OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
41
42 -- *********************
43 -- GLOBAL MESSAGE CONSTANTS
44 -- *********************
45 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
46 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
47 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
48 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
49 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
50
51 -- *********************
52 -- GLOBAL VARIABLES
53 -- *********************
54 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_AM_TERMNT_VENDOR_PRG_PVT';
55 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
56 G_APP_NAME_1 CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
57 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
58 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
59 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
60 G_API_VERSION CONSTANT NUMBER := 1;
61 G_MISS_CHAR CONSTANT VARCHAR2(1) := OKL_API.G_MISS_CHAR;
62 G_MISS_NUM CONSTANT NUMBER := OKL_API.G_MISS_NUM;
63 G_MISS_DATE CONSTANT DATE := OKL_API.G_MISS_DATE;
64 G_TRUE CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
65 G_FALSE CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
66 G_YES CONSTANT VARCHAR2(1) := 'Y';
67 G_NO CONSTANT VARCHAR2(1) := 'N';
68 G_FIRST CONSTANT NUMBER := FND_MSG_PUB.G_FIRST;
69 G_NEXT CONSTANT NUMBER := FND_MSG_PUB.G_NEXT;
70 G_ERROR VARCHAR2(1) := 'N';
71 G_VPA_ENDED_BY_DATE DATE;
72 success_message_table message_tbl_type;
73 error_message_table message_tbl_type;
74 l_error_count NUMBER := 1;
75
76 -- *********************
77 -- GLOBAL EXCEPTIONS
78 -- *********************
79 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
80 G_EXCEPTION_ERROR EXCEPTION;
81 G_EXCEPTION_HALT EXCEPTION;
82
83 -- *********************
84 -- GLOBAL DECLARATIONS
85 -- *********************
86 msg_lines_table msg_tbl_type;
87 l_success_tbl_index NUMBER := 1;
88 l_error_tbl_index NUMBER := 1;
89
90 -- Start of comments
91 --
92 -- Procedure Name : fnd_error_output
93 -- Desciption : Logs the messages in the output log
94 -- Business Rules :
95 -- Parameters :
96 -- Version : 1.0
97 -- History : RMUNJULU created
98 --
99 -- End of comments
100 PROCEDURE fnd_output (
101 p_va_rec IN va_rec_type,
102 p_control_flag IN VARCHAR2 ) IS
103
104 lx_error_rec OKL_API.error_rec_type;
105 l_msg_idx INTEGER := G_FIRST;
106
107 BEGIN
108
109 -- Get the messages in the log
110 LOOP
111
112 FND_MSG_PUB.get(
113 p_msg_index => l_msg_idx,
114 p_encoded => G_FALSE,
115 p_data => lx_error_rec.msg_data,
116 p_msg_index_out => lx_error_rec.msg_count);
117
118 IF (lx_error_rec.msg_count IS NOT NULL) THEN
119
120 -- Store the contract id
121 msg_lines_table(l_error_count).id := p_va_rec.id;
122 msg_lines_table(l_error_count).msg := lx_error_rec.msg_data;
123
124 l_error_count := l_error_count + 1;
125 END IF;
126
127 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
128 OR (lx_error_rec.msg_count IS NULL));
129
130 l_msg_idx := G_NEXT;
131
132 END LOOP;
133
134
135 IF p_control_flag = 'PROCESSED' THEN
136
137 success_message_table(l_success_tbl_index).id := p_va_rec.id;
138 success_message_table(l_success_tbl_index).va_number := p_va_rec.va_number;
139 success_message_table(l_success_tbl_index).start_date := p_va_rec.start_date;
140 success_message_table(l_success_tbl_index).end_date := p_va_rec.end_date;
141 success_message_table(l_success_tbl_index).status := p_va_rec.sts_code;
142 l_success_tbl_index := l_success_tbl_index + 1;
143
144 ELSE
145
146 error_message_table(l_error_tbl_index).id := p_va_rec.id;
147 error_message_table(l_error_tbl_index).va_number := p_va_rec.va_number;
148 error_message_table(l_error_tbl_index).start_date := p_va_rec.start_date;
149 error_message_table(l_error_tbl_index).end_date := p_va_rec.end_date;
150 error_message_table(l_error_tbl_index).status := p_va_rec.sts_code;
151 l_error_tbl_index := l_error_tbl_index + 1;
152
153 END IF;
154
155 EXCEPTION
156
157 WHEN OTHERS THEN
158 -- Set the oracle error message
159 OKL_API.set_message(
160 p_app_name => G_APP_NAME_1,
161 p_msg_name => G_UNEXPECTED_ERROR,
162 p_token1 => G_SQLCODE_TOKEN,
163 p_token1_value => SQLCODE,
164 p_token2 => G_SQLERRM_TOKEN,
165 p_token2_value => SQLERRM);
166
167 END fnd_output;
168
169 -- Start of comments
170 --
171 -- Procedure Name : create_report
172 -- Desciption : Creates the Output and Log Reports
173 -- Business Rules :
174 -- Parameters :
175 -- Version : 1.0
176 -- History : RMUNJULU created
177 --
178 -- End of comments
179 PROCEDURE create_report IS
180
181 i NUMBER;
182 j NUMBER;
183 k NUMBER;
184 l_success NUMBER;
185 l_error NUMBER;
186
187 -- Get the Org Name
188 CURSOR org_csr (p_org_id IN NUMBER) IS
189 SELECT HOU.name
190 FROM HR_OPERATING_UNITS HOU
191 WHERE HOU.organization_id = p_org_id;
192
193 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
194
195 l_org_name VARCHAR2(300);
196 l_orcl_logo VARCHAR2(300);
197 l_term_heading VARCHAR2(300);
198 l_set_of_books VARCHAR2(300);
199 l_set_of_books_name VARCHAR2(300);
200 l_run_date VARCHAR2(300);
201 l_oper_unit VARCHAR2(300);
202 l_type VARCHAR2(300);
203 l_processed VARCHAR2(300);
204 l_term_k VARCHAR2(300);
205 l_error_k VARCHAR2(300);
206 l_serial VARCHAR2(300);
207 l_k_num VARCHAR2(300);
208 l_start_date VARCHAR2(300);
209 l_end_date VARCHAR2(300);
210 l_status VARCHAR2(300);
211 l_messages VARCHAR2(300);
212 l_eop VARCHAR2(300);
213 l_vpa_ended_by VARCHAR2(300);
214 l_vpa VARCHAR2(300);
215 l_print VARCHAR2(1);
216 msg_lines_table_index NUMBER;
217
218 BEGIN
219
220 l_success := success_message_table.COUNT;
221 l_error := error_message_table.COUNT;
222
223 l_orcl_logo := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_ACCT_LEASE_MANAGEMENT');
224 l_term_heading := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_TERM_VPA'); --***
225 l_set_of_books := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_SET_OF_BOOKS');
226 l_run_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_RUN_DATE');
227 l_oper_unit := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_OPERUNIT');
228 l_type := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_TYPE');
229 l_processed := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_PROCESSED_ENTRIES');
230 l_term_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_EXP_VPA'); --***
231 l_error_k := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_ERR_VPA'); --***
232 l_serial := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_SERIAL_NUMBER');
233 l_k_num := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_VPA_NUMBER'); --***
234 l_start_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_START_DATE');
235 l_end_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_END_DATE');
236 l_status := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_STATUS');
237 l_messages := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_MESSAGES');
238 l_eop := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_END_OF_REPORT');
239 l_vpa_ended_by := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_VPA_ENDED_BY'); --***
240 l_vpa := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_VPA_CONC_OUTPUT','OKL_AM_INVALID_TERM_DATE');
241
242 l_set_of_books_name := OKL_ACCOUNTING_UTIL.get_set_of_books_name (OKL_ACCOUNTING_UTIL.get_set_of_books_id);
243
244 -- Get the Org Name
245 FOR org_rec IN org_csr (l_org_id) LOOP
246 l_org_name := org_rec.name;
247 END LOOP;
248
249 -- Valid Vendor Prg Chosen
250 IF G_ERROR <> 'Y' THEN
251
252 --log
253 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
254 FND_FILE.put_line(FND_FILE.log, l_type ||
255 RPAD(' ',40-LENGTH(l_type),' ') ||
256 l_processed);
257
258 FND_FILE.put_line(FND_FILE.log, RPAD('-',77 ,'-'));
259
260 FND_FILE.put_line(FND_FILE.log, l_term_k ||
261 RPAD(' ',40-LENGTH(l_term_k),' ') ||
262 l_success);
263
264 FND_FILE.put_line(FND_FILE.log, l_error_k ||
265 RPAD(' ',40-LENGTH(l_error_k),' ') ||
266 l_error);
267 FND_FILE.put_line(FND_FILE.log,'');
268 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
269
270 -- output
271 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_orcl_logo)/2, ' ' ) ||
272 l_orcl_logo);
273
274 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_term_heading)/2, ' ' ) ||
275 l_term_heading);
276
277 FND_FILE.put_line(FND_FILE.output, RPAD(' ',128/2-LENGTH(l_term_heading)/2 , ' ' ) ||
278 RPAD('-',LENGTH(l_term_heading),'-'));
279
280 FND_FILE.put_line(FND_FILE.output, '');
281
282 FND_FILE.put_line(FND_FILE.output, l_set_of_books ||' : '||
283 l_set_of_books_name ||
284 RPAD(' ', 128-LENGTH(l_set_of_books)-LENGTH(l_set_of_books_name)-LENGTH(l_run_date)-25, ' ' ) ||
285 l_run_date ||' : ' ||
286 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
287
288 FND_FILE.put_line(FND_FILE.output, l_oper_unit || ' : ' ||
289 l_org_name ||
290 RPAD(' ', 128-LENGTH(l_oper_unit)-LENGTH(l_org_name)-LENGTH(l_vpa_ended_by)-25, ' ' ) ||
291 l_vpa_ended_by ||' : ' ||
292 TO_CHAR(G_VPA_ENDED_BY_DATE, 'DD-MON-YYYY HH24:MI'));
293
294 FND_FILE.put_line(FND_FILE.output,'');
295 FND_FILE.put_line(FND_FILE.output,'');
296
297 FND_FILE.put_line(FND_FILE.output, l_type ||
298 RPAD(' ',40-LENGTH(l_type),' ') ||
299 l_processed);
300
301 FND_FILE.put_line(FND_FILE.output, RPAD('-',128 ,'-'));
302
303 FND_FILE.put_line(FND_FILE.output, l_term_k ||
304 RPAD(' ',40-LENGTH(l_term_k),' ') ||
305 l_success);
306
307 FND_FILE.put_line(FND_FILE.output, l_error_k ||
308 RPAD(' ',40-LENGTH(l_error_k),' ') ||
309 l_error);
310
311 FND_FILE.put_line(FND_FILE.output,'');
312 FND_FILE.put_line(FND_FILE.output, RPAD('=',128,'=' ));
313 FND_FILE.put_line(FND_FILE.output,'');
314
315 -- Print VPAs Terminated Successfully
316 IF l_success > 0 THEN
317
318 FND_FILE.put_line(FND_FILE.output, l_term_k);
319 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_term_k), '-' ));
320 FND_FILE.put_line(FND_FILE.output,'');
321
322 l_print := 'N';
323
324 FOR i IN success_message_table.FIRST..success_message_table.LAST LOOP
325
326 IF l_print = 'N' THEN
327
328 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
329 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
330 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
331 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
332 l_status||RPAD(' ',15-LENGTH(l_status),' '));
333
334 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
335 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
336 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
337 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
338 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
339
340 l_print := 'Y';
341 END IF;
342
343 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
344 success_message_table(i).va_number ||
345 RPAD(' ',35-LENGTH(success_message_table(i).va_number),' ')||
346 success_message_table(i).start_date||
347 RPAD(' ',15-LENGTH(success_message_table(i).start_date),' ') ||
348 success_message_table(i).end_date||
349 RPAD(' ',15-LENGTH(success_message_table(i).end_date),' ') ||
350 success_message_table(i).status||
351 RPAD(' ',15-LENGTH(success_message_table(i).status),' '));
352
353 END LOOP;
354 END IF;
355
356 FND_FILE.put_line(FND_FILE.output,'');
357
358 -- Print VPAs errored
359 IF l_error > 0 THEN
360
361 FND_FILE.put_line(FND_FILE.output, l_error_k);
362 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_error_k), '-' ));
363 FND_FILE.put_line(FND_FILE.output,'');
364
365 -- Initialize the table index
366 msg_lines_table_index := 1;
367
368 FOR i IN error_message_table.FIRST..error_message_table.LAST LOOP
369
370 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
371 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
372 l_start_date||RPAD(' ',15-LENGTH(l_start_date),' ') ||
373 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' ') ||
374 l_status||RPAD(' ',15-LENGTH(l_status),' '));
375
376 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
377 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
378 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',15-LENGTH(l_start_date),'-') ||
379 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-') ||
380 RPAD('-',LENGTH(l_status),'-')||RPAD('-',15-LENGTH(l_status),'-'));
381
382 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
383 error_message_table(i).va_number ||
384 RPAD(' ',35-LENGTH(error_message_table(i).va_number),' ')||
385 error_message_table(i).start_date||
386 RPAD(' ',15-LENGTH(error_message_table(i).start_date),' ') ||
387 error_message_table(i).end_date||
388 RPAD(' ',15-LENGTH(error_message_table(i).end_date),' ') ||
389 error_message_table(i).status||
390 RPAD(' ',15-LENGTH(error_message_table(i).status),' '));
391
392 FND_FILE.put_line(FND_FILE.output,'');
393
394 FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
395
396 k := 1;
397 FOR j IN msg_lines_table_index .. msg_lines_table.LAST LOOP
398 IF msg_lines_table(j).id = error_message_table(i).id THEN
399 FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || k || ': ' || msg_lines_table(j).msg);
400 FND_FILE.put_line(FND_FILE.output,'');
401 k := k + 1;
402 ELSE
403 msg_lines_table_index := j ;
404 EXIT;
405 END IF;
406
407 END LOOP;
408
409 FND_FILE.put_line(FND_FILE.output,'');
410
411 END LOOP;
412
413 END IF;
414
415 FND_FILE.put_line(FND_FILE.output,'');
416 FND_FILE.put_line(FND_FILE.output,'');
417 FND_FILE.put_line(FND_FILE.output, RPAD(' ', 53 , ' ' ) || l_eop);
418
419 ELSE
420
421 FND_FILE.put_line(FND_FILE.log,l_processed || ' = 0');
422 FND_FILE.put_line(FND_FILE.log,l_vpa);
423
424 END IF;
425 EXCEPTION
426
427 WHEN OTHERS THEN
428 -- Set the oracle error message
429 OKL_API.set_message(
430 p_app_name => G_APP_NAME_1,
431 p_msg_name => G_UNEXPECTED_ERROR,
432 p_token1 => G_SQLCODE_TOKEN,
433 p_token1_value => SQLCODE,
434 p_token2 => G_SQLERRM_TOKEN,
435 p_token2_value => SQLERRM);
436
437 END create_report;
438
439 -- Start of comments
440 --
441 -- Procedure Name : val_pop_vendor_prg
442 -- Description : procedure to validate and populate vendor program
443 -- Business Rules :
444 -- Parameters :
445 -- Version : 1.0
446 -- History : RMUNJULU Created
447 --
448 -- End of comments
449 PROCEDURE val_pop_vendor_prg(
450 p_va_rec IN va_rec_type,
451 p_control_flag IN VARCHAR2,
452 x_va_rec OUT NOCOPY va_rec_type,
453 x_return_status OUT NOCOPY VARCHAR2) IS
454
455 -- Get the details of the VPA
456 CURSOR get_va_details_csr (p_va_id IN NUMBER) IS
457 SELECT CHR.id,
458 CHR.contract_number va_number,
459 CHR.START_DATE,
460 CHR.end_date,
461 CHR.sts_code, -- Should be ACTIVE
462 CHR.scs_code -- Should be PROGRAM or OPERATING
463 FROM OKC_K_HEADERS_B CHR,
464 OKL_K_HEADERS KHR
465 WHERE CHR.id = p_va_id
466 AND CHR.id = KHR.id;
467
468 -- Get the IA TRN for Termination ie TCN_TYPE = 'IAT'
469 CURSOR get_trn_csr (p_va_id IN NUMBER) IS
470 SELECT TRN.tsu_code
471 FROM OKL_TRX_CONTRACTS TRN
472 WHERE TRN.khr_id = p_va_id
473 --rkuttiya added for 12.1.1 Multi GAAP
474 AND TRN.representation_type = 'PRIMARY'
475 --
476 AND TRN.tcn_type = 'IAT';
477
478 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
479 l_ia_number VARCHAR2(300);
480 l_start_date DATE;
481 l_end_date DATE;
482 l_status VARCHAR2(300);
483 l_type VARCHAR2(300);
484 l_pdt_id NUMBER;
485 i NUMBER := 0;
486 l_tsu_code VARCHAR2(300);
487 l_va_rec va_rec_type;
488 get_va_details_rec get_va_details_csr%ROWTYPE;
489
490 BEGIN
491
492 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
493 -- Begin Logic
494 -- 0 Get the IA details
495 -- 1.1 Throw error if VPA ID/Number not valid
496 -- 1.2 Throw error if VPA type not valid
497 -- 1.3 Throw error if VPA End Date not valid
498 -- End Logic
499 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
500
501 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
502 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
503 'OKL_AM_TERMNT_VENDOR_PRG_PVT.val_pop_vendor_prg.',
504 'Begin(+)');
505 END IF;
506
507 SAVEPOINT validate_va_trx;
508
509 -- Get VPA details
510 OPEN get_va_details_csr (p_va_rec.id);
511 FETCH get_va_details_csr INTO get_va_details_rec;
512 CLOSE get_va_details_csr;
513
514 -- Set the va rec with VPA details
515 l_va_rec.id := get_va_details_rec.id;
516 l_va_rec.va_number := get_va_details_rec.va_number;
517 l_va_rec.start_date := get_va_details_rec.start_date;
518 l_va_rec.end_date := get_va_details_rec.end_date;
519 l_va_rec.sts_code := get_va_details_rec.sts_code;
520 l_va_rec.date_terminated := sysdate;
521 l_va_rec.scs_code := get_va_details_rec.scs_code;
522
523 -- If single request then do additional validations
524 IF p_control_flag = 'BATCH_SINGLE' THEN -- Do additional checks
525
526 -- Check for ID
527 IF get_va_details_rec.id IS NULL
528 OR get_va_details_rec.id = OKL_API.G_MISS_NUM THEN
529
530 -- Message: Invalid value for Vendor Program ID.
531 OKL_API.set_message(
532 p_app_name => G_APP_NAME,
533 p_msg_name => 'OKL_AM_VPA_ID_INVALID');
534
535 RAISE G_EXCEPTION_ERROR;
536 END IF;
537
538 -- Check for type
539 IF get_va_details_rec.scs_code NOT IN ('PROGRAM','OPERATING') THEN
540
541 -- Message: Invalid value for Program Type for Vendor Program VPA_NUMBER.
542 OKL_API.set_message(
543 p_app_name => G_APP_NAME,
544 p_msg_name => 'OKL_AM_VPA_TYPE_INVALID',
545 p_token1 => 'VPA_NUMBER',
546 p_token1_value => get_va_details_rec.va_number);
547
548 RAISE G_EXCEPTION_ERROR;
549 END IF;
550
551 -- Check for status
552 IF get_va_details_rec.sts_code NOT IN ('ACTIVE') THEN
553
554 -- Message: Invalid value for Program Status for Vendor Program VPA_NUMBER.
555 OKL_API.set_message(
556 p_app_name => G_APP_NAME,
557 p_msg_name => 'OKL_AM_VPA_STATUS_INVALID',
558 p_token1 => 'VPA_NUMBER',
559 p_token1_value => get_va_details_rec.va_number);
560
561 RAISE G_EXCEPTION_ERROR;
562 END IF;
563
564 -- Check for end date
565 IF trunc(get_va_details_rec.end_date) > G_VPA_ENDED_BY_DATE THEN -- G_VPA_ENDED_BY_DATE is passed by user
566
567 -- Message: Program VPA_NUMBER has not reached its end date.
568 OKL_API.set_message(
569 p_app_name => G_APP_NAME,
570 p_msg_name => 'OKL_AM_VPA_END_DATE_INVALID',
571 p_token1 => 'VPA_NUMBER',
572 p_token1_value => get_va_details_rec.va_number);
573
574 RAISE G_EXCEPTION_ERROR;
575 END IF;
576
577 END IF;
578
579 -- Set return values
580 x_return_status := l_return_status;
581 x_va_rec := l_va_rec;
582
583 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
584 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
585 'OKL_AM_TERMNT_VENDOR_PRG_PVT.val_pop_vendor_prg.',
586 'End(-)');
587 END IF;
588
589 EXCEPTION
590
591 WHEN G_EXCEPTION_ERROR THEN
592 ROLLBACK TO validate_va_trx;
593 x_return_status := G_RET_STS_ERROR;
594 x_va_rec := l_va_rec;
595
596 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
598 'OKL_AM_TERMNT_VENDOR_PRG_PVT.val_pop_vendor_prg.',
599 'EXP - G_EXCEPTION_ERROR');
600 END IF;
601
602 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
603 ROLLBACK TO validate_va_trx;
604 x_return_status := G_RET_STS_UNEXP_ERROR;
605 x_va_rec := l_va_rec;
606
607 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
608 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
609 'OKL_AM_TERMNT_VENDOR_PRG_PVT.val_pop_vendor_prg.',
610 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
611 END IF;
612
613 WHEN OTHERS THEN
614 ROLLBACK TO validate_va_trx;
615 -- Set the oracle error message
616 OKL_API.set_message(
617 p_app_name => G_APP_NAME_1,
618 p_msg_name => G_UNEXPECTED_ERROR,
619 p_token1 => G_SQLCODE_TOKEN,
620 p_token1_value => SQLCODE,
621 p_token2 => G_SQLERRM_TOKEN,
622 p_token2_value => SQLERRM);
623
624 x_return_status := G_RET_STS_UNEXP_ERROR;
625 x_va_rec := l_va_rec;
626
627 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
629 'OKL_AM_TERMNT_VENDOR_PRG_PVT.val_pop_vendor_prg.',
630 'EXP - OTHERS');
631 END IF;
632
633 END val_pop_vendor_prg;
634
635 -- Start of comments
636 --
637 -- Procedure Name : pop_or_insert_transaction
638 -- Description : procedure to insert termination transaction for the VPA
639 -- if does not exist or else if exists then populate
640 -- Business Rules :
641 -- Parameters :
642 -- Version : 1.0
643 -- History : RMUNJULU Created
644 --
645 -- End of comments
646 PROCEDURE pop_or_insert_transaction(
647 p_va_rec IN va_rec_type,
648 p_sys_date IN DATE,
649 p_validate_success IN VARCHAR2,
650 x_trn_already_yn OUT NOCOPY VARCHAR2,
651 px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
652 x_return_status OUT NOCOPY VARCHAR2) IS
653
654 -- Get the trn if exists
655 CURSOR get_trn_csr ( p_ia_id IN NUMBER ) IS
656 SELECT TRN.id,
657 TRN.trx_number,
658 TRN.tsu_code,
659 TRN.tcn_type,
660 TRN.try_id,
661 TRN.khr_id,
662 TRN.tmt_validated_yn,
663 TRN.tmt_accounting_entries_yn,
664 TRN.tmt_contract_updated_yn,
665 TRN.tmt_recycle_yn,
666 TRN.tmt_generic_flag1_yn,
667 TRN.tmt_generic_flag2_yn,
668 TRN.tmt_generic_flag3_yn
669 FROM OKL_TRX_CONTRACTS TRN
670 WHERE TRN.khr_id = p_ia_id
671 --rkuttiya added for 12.1.1 Multi GAAP
672 AND TRN.representation_type = 'PRIMARY'
673 --
674 AND TRN.tcn_type = 'IAT';
675
676 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
677 lp_tcnv_rec tcnv_rec_type ;
678 lx_tcnv_rec tcnv_rec_type ;
679 l_try_id NUMBER;
680 l_currency_code VARCHAR2(2000);
681 l_trans_meaning VARCHAR2(2000);
682 l_trn_already_yn VARCHAR2(1) := G_NO;
683
684 l_api_version CONSTANT NUMBER := G_API_VERSION;
685 l_msg_count NUMBER := G_MISS_NUM;
686 l_msg_data VARCHAR2(2000);
687
688 BEGIN
689
690 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
692 'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
693 'Begin(+)');
694 END IF;
695
696 SAVEPOINT pop_insert_trn_trx;
697
698 -- *************
699 -- Populate TRN if exists
700 -- *************
701 FOR get_trn_rec IN get_trn_csr ( p_va_rec.id ) LOOP
702
703 lx_tcnv_rec.id := get_trn_rec.id;
704 lx_tcnv_rec.tsu_code := get_trn_rec.tsu_code;
705 lx_tcnv_rec.trx_number := get_trn_rec.trx_number;
706 lx_tcnv_rec.tcn_type := get_trn_rec.tcn_type;
707 lx_tcnv_rec.try_id := get_trn_rec.try_id;
708 lx_tcnv_rec.khr_id := get_trn_rec.khr_id;
709 lx_tcnv_rec.tmt_validated_yn := get_trn_rec.tmt_validated_yn;
710 lx_tcnv_rec.tmt_accounting_entries_yn := get_trn_rec.tmt_accounting_entries_yn;
711 lx_tcnv_rec.tmt_contract_updated_yn := get_trn_rec.tmt_contract_updated_yn;
712 lx_tcnv_rec.tmt_recycle_yn := get_trn_rec.tmt_recycle_yn;
713 lx_tcnv_rec.tmt_generic_flag1_yn := get_trn_rec.tmt_generic_flag1_yn;
714 lx_tcnv_rec.tmt_generic_flag2_yn := get_trn_rec.tmt_generic_flag2_yn;
715 lx_tcnv_rec.tmt_generic_flag3_yn := get_trn_rec.tmt_generic_flag3_yn;
716 l_trn_already_yn := G_YES;
717
718 END LOOP;
719
720 -- *************
721 -- Insert TRN if not exists
722 -- *************
723 IF lx_tcnv_rec.id IS NULL
724 OR lx_tcnv_rec.id = G_MISS_NUM THEN
725
726 -- Get the Transaction Id
727 OKL_AM_UTIL_PVT.get_transaction_id (
728 p_try_name => 'Termination',
729 x_return_status => l_return_status,
730 x_try_id => l_try_id);
731
732 -- Get the meaning of lookup OKL_ACCOUNTING_EVENT_TYPE
733 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
734 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
735 p_lookup_code => 'TERMINATION',
736 p_validate_yn => G_YES);
737
738 IF l_return_status <> G_RET_STS_SUCCESS THEN
739
740 -- Message: Unable to find a transaction type for the transaction TRY_NAME -- Seeded
741 OKL_API.set_message(
742 p_app_name => G_APP_NAME,
743 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
744 p_token1 => 'TRY_NAME',
745 p_token1_value => l_trans_meaning);
746
747 RAISE G_EXCEPTION_ERROR;
748
749 END IF;
750
751 -- Get the contract currency code
752 l_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(p_va_rec.id);
753
754 -- Set the TRN rec
755 lp_tcnv_rec.khr_id := p_va_rec.id;
756 lp_tcnv_rec.tsu_code := 'ENTERED';
757 lp_tcnv_rec.tcn_type := 'IAT';
758 lp_tcnv_rec.try_id := l_try_id;
759 lp_tcnv_rec.currency_code := l_currency_code;
760 lp_tcnv_rec.date_transaction_occurred := p_sys_date;
761
762 -- Call create_trx_contracts to create transaction
763 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(
764 p_api_version => l_api_version,
765 p_init_msg_list => G_FALSE,
766 x_return_status => l_return_status,
767 x_msg_count => l_msg_count,
768 x_msg_data => l_msg_data,
769 p_tcnv_rec => lp_tcnv_rec,
770 x_tcnv_rec => lx_tcnv_rec);
771
772 -- Set msg if error
773 IF l_return_status <> G_RET_STS_SUCCESS THEN
774 -- Error occured while creating termination transaction for the
775 -- Investor Agreement AGREEMENT_NUMBER..
776 OKL_API.set_message(
777 p_app_name => G_APP_NAME,
778 p_msg_name => 'OKL_AM_INV_TRN_CREATE_ERR');
779 END IF;
780
781 -- raise exception if create failed
782 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
783 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
784 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
785 RAISE G_EXCEPTION_ERROR;
786 END IF;
787
788 -- Set if TRN was already existing
789 l_trn_already_yn := G_NO;
790
791 END IF;
792
793 -- Set return values
794 x_return_status := l_return_status;
795 px_tcnv_rec := lx_tcnv_rec;
796 x_trn_already_yn := l_trn_already_yn;
797
798 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
800 'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
801 'End(-)');
802 END IF;
803
804 EXCEPTION
805
806 WHEN G_EXCEPTION_ERROR THEN
807 ROLLBACK TO pop_insert_trn_trx;
808 x_return_status := G_RET_STS_ERROR;
809
810 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
811 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
812 'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
813 'EXP - G_EXCEPTION_ERROR');
814 END IF;
815
816 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
817 ROLLBACK TO pop_insert_trn_trx;
818 x_return_status := G_RET_STS_UNEXP_ERROR;
819
820 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
821 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
822 'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
823 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
824 END IF;
825
826 WHEN OTHERS THEN
827 ROLLBACK TO pop_insert_trn_trx;
828
829 -- Set the oracle error message
830 OKL_API.set_message(
831 p_app_name => G_APP_NAME_1,
832 p_msg_name => G_UNEXPECTED_ERROR,
833 p_token1 => G_SQLCODE_TOKEN,
834 p_token1_value => SQLCODE,
835 p_token2 => G_SQLERRM_TOKEN,
836 p_token2_value => SQLERRM);
837
838 x_return_status := G_RET_STS_UNEXP_ERROR;
839
840 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
842 'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
843 'EXP - OTHERS');
844 END IF;
845
846 END pop_or_insert_transaction;
847
848 -- Start of comments
849 --
850 -- Procedure Name : update_transaction
851 -- Description : procedure to update termination transaction for the VPA
852 -- Business Rules :
853 -- Parameters :
854 -- Version : 1.0
855 -- History : RMUNJULU Created
856 --
857 -- End of comments
858 PROCEDURE update_transaction(
859 p_va_rec IN va_rec_type,
860 p_status IN VARCHAR2,
861 p_step IN VARCHAR2,
862 px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
863 x_return_status OUT NOCOPY VARCHAR2) IS
864
865 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
866 lp_tcnv_rec tcnv_rec_type;
867 lx_tcnv_rec tcnv_rec_type;
868 l_api_version CONSTANT NUMBER := G_API_VERSION;
869 l_msg_count NUMBER := G_MISS_NUM;
870 l_msg_data VARCHAR2(2000);
871 l_status VARCHAR2(3);
872
873 BEGIN
874
875 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
876 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
877 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
878 'Begin(+)');
879 END IF;
880
881 SAVEPOINT update_transaction_trx;
882
883 lp_tcnv_rec := px_tcnv_rec;
884
885 IF p_step = 'tmt_contract_updated_yn' THEN -- If last step
886 IF p_status = G_RET_STS_SUCCESS THEN
887 lp_tcnv_rec.tsu_code := 'PROCESSED';
888 ELSE
889 lp_tcnv_rec.tsu_code := 'ERROR';
890 END IF;
891 ELSE
892 IF p_status = G_RET_STS_SUCCESS THEN
893 lp_tcnv_rec.tsu_code := 'WORKING';
894 ELSE
895 lp_tcnv_rec.tsu_code := 'ERROR';
896 END IF;
897 END IF;
898
899 IF p_status = G_RET_STS_SUCCESS THEN
900 l_status := 'Y';
901 ELSE
902 l_status := 'N';
903 END IF;
904
905 IF p_step = 'tmt_contract_updated_yn' THEN
906 lp_tcnv_rec.tmt_contract_updated_yn := l_status;
907 ELSIF p_step = 'tmt_validated_yn' THEN
908 lp_tcnv_rec.tmt_validated_yn := l_status;
909 END IF;
910
911 -- Call update_trx_contracts to update transaction
912 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
913 p_api_version => l_api_version,
914 p_init_msg_list => G_FALSE,
915 x_return_status => l_return_status,
916 x_msg_count => l_msg_count,
917 x_msg_data => l_msg_data,
918 p_tcnv_rec => lp_tcnv_rec,
919 x_tcnv_rec => lx_tcnv_rec);
920
921 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
923 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
924 'l_return_status '||l_return_status);
925 END IF;
926
927 -- Set msg if error
928 IF l_return_status <> G_RET_STS_SUCCESS THEN
929 -- Message: Error updating Program Agreement VPA_NUMBER.
930 OKL_API.set_message(
931 p_app_name => G_APP_NAME,
932 p_msg_name => 'OKL_AM_VPA_UPD_TRN_ERR',
933 p_token1 => 'AGREEMENT_NUMBER',
934 p_token1_value => p_va_rec.va_number);
935 END IF;
936
937 -- raise exception if update failed
938 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
939 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
941 RAISE G_EXCEPTION_ERROR;
942 END IF;
943
944 -- Set return status
945 x_return_status := l_return_status;
946 px_tcnv_rec := lx_tcnv_rec;
947
948 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
949 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
950 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
951 'End(-)');
952 END IF;
953
954 EXCEPTION
955
956 WHEN G_EXCEPTION_ERROR THEN
957 ROLLBACK TO update_transaction_trx;
958 x_return_status := G_RET_STS_ERROR;
959
960 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
961 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
962 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
963 'EXP - G_EXCEPTION_ERROR');
964 END IF;
965
966 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
967 ROLLBACK TO update_transaction_trx;
968 x_return_status := G_RET_STS_UNEXP_ERROR;
969
970 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
972 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
973 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
974 END IF;
975
976 WHEN OTHERS THEN
977 ROLLBACK TO update_transaction_trx;
978
979 -- Set the oracle error message
980 OKL_API.set_message(
981 p_app_name => G_APP_NAME_1,
982 p_msg_name => G_UNEXPECTED_ERROR,
983 p_token1 => G_SQLCODE_TOKEN,
984 p_token1_value => SQLCODE,
985 p_token2 => G_SQLERRM_TOKEN,
986 p_token2_value => SQLERRM);
987
988 x_return_status := G_RET_STS_UNEXP_ERROR;
989
990 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
992 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
993 'EXP - OTHERS');
994 END IF;
995
996 END update_transaction;
997
998 -- Start of comments
999 --
1000 -- Procedure Name : update_vendor_prg
1001 -- Description : procedure to update vendor program to EXPIRED
1002 -- Business Rules :
1003 -- Parameters :
1004 -- Version : 1.0
1005 -- History : RMUNJULU Created
1006 --
1007 -- End of comments
1008 PROCEDURE update_vendor_prg(
1009 p_va_rec IN va_rec_type,
1010 p_control_flag IN VARCHAR2,
1011 px_va_rec IN OUT NOCOPY va_rec_type,
1012 x_return_status OUT NOCOPY VARCHAR2) IS
1013
1014 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1015 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1016 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1017 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
1018 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
1019 l_trn_reason_code VARCHAR2(30) := 'EXP';
1020 l_api_version CONSTANT NUMBER := G_API_VERSION;
1021 l_msg_count NUMBER := G_MISS_NUM;
1022 l_msg_data VARCHAR2(2000);
1023
1024 BEGIN
1025
1026 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1028 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1029 'Begin(+)');
1030 END IF;
1031
1032 SAVEPOINT update_vpa_trx;
1033
1034 -- Set the rec types
1035 lp_chrv_rec.id := px_va_rec.id;
1036 lp_khrv_rec.id := px_va_rec.id;
1037 lp_chrv_rec.date_terminated := sysdate;
1038 lp_chrv_rec.sts_code := 'EXPIRED';
1039 lp_chrv_rec.trn_code := l_trn_reason_code;
1040
1041 -- Call update contract to expire VPA
1042 OKL_CONTRACT_PUB.update_contract_header(
1043 p_api_version => l_api_version,
1044 p_init_msg_list => G_FALSE,
1045 x_return_status => l_return_status,
1046 x_msg_count => l_msg_count,
1047 x_msg_data => l_msg_data,
1048 p_chrv_rec => lp_chrv_rec,
1049 p_khrv_rec => lp_khrv_rec,
1050 x_chrv_rec => lx_chrv_rec,
1051 x_khrv_rec => lx_khrv_rec);
1052
1053 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1055 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1056 'l_return_status '||l_return_status);
1057 END IF;
1058
1059 IF l_return_status <> G_RET_STS_SUCCESS THEN
1060 -- Message : Error updating Program Agreement VPA_NUMBER.
1061 OKL_API.set_message(
1062 p_app_name => G_APP_NAME,
1063 p_msg_name => 'OKL_AM_VPA_TRMNT_ERR',
1064 p_token1 => 'VPA_NUMBER',
1065 p_token1_value => px_va_rec.va_number);
1066 END IF;
1067
1068 -- raise exception if update failed
1069 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1070 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1071 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1072 RAISE G_EXCEPTION_ERROR;
1073 END IF;
1074
1075 -- The VPA is now expired
1076 px_va_rec.sts_code := 'EXPIRED';
1077
1078 -- Set return values
1079 x_return_status := l_return_status;
1080
1081 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1082 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1083 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1084 'End(-)');
1085 END IF;
1086
1087 EXCEPTION
1088
1089 WHEN G_EXCEPTION_ERROR THEN
1090 ROLLBACK TO update_vpa_trx;
1091 x_return_status := G_RET_STS_ERROR;
1092
1093 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1094 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1095 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1096 'EXP - G_EXCEPTION_ERROR');
1097 END IF;
1098
1099 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1100 ROLLBACK TO update_vpa_trx;
1101 x_return_status := G_RET_STS_UNEXP_ERROR;
1102
1103 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1105 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1106 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1107 END IF;
1108
1109 WHEN OTHERS THEN
1110 ROLLBACK TO update_vpa_trx;
1111 -- Set the oracle error message
1112 OKL_API.set_message(
1113 p_app_name => G_APP_NAME_1,
1114 p_msg_name => G_UNEXPECTED_ERROR,
1115 p_token1 => G_SQLCODE_TOKEN,
1116 p_token1_value => SQLCODE,
1117 p_token2 => G_SQLERRM_TOKEN,
1118 p_token2_value => SQLERRM);
1119
1120 x_return_status := G_RET_STS_UNEXP_ERROR;
1121
1122 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1123 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1124 'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
1125 'EXP - OTHERS');
1126 END IF;
1127
1128 END update_vendor_prg;
1129
1130
1131 -- Start of comments
1132 --
1133 -- Procedure Name : terminate_vendor_prog
1134 -- Description : procedure to terminate Vendor Program
1135 -- Business Rules :
1136 -- Parameters :
1137 -- Version : 1.0
1138 -- History : RMUNJULU Created
1139 --
1140 -- End of comments
1141 PROCEDURE terminate_vendor_prog(
1142 p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1144 x_return_status OUT NOCOPY VARCHAR2,
1145 x_msg_count OUT NOCOPY NUMBER,
1146 x_msg_data OUT NOCOPY VARCHAR2,
1147 p_va_rec IN va_rec_type,
1148 p_control_flag IN VARCHAR2 DEFAULT NULL) IS
1149
1150 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1151 l_overall_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1152 l_trx_id NUMBER;
1153 l_pdt_id NUMBER;
1154 l_va_rec va_rec_type := p_va_rec;
1155 l_sys_date DATE;
1156 l_tcnv_rec tcnv_rec_type;
1157 l_trn_already_yn VARCHAR2(1);
1158 l_end_date DATE;
1159 l_start_date DATE;
1160 l_type VARCHAR2(300);
1161 l_status VARCHAR2(300);
1162 l_control_flag VARCHAR2(300);
1163 l_valid_gl_date DATE;
1164 l_step VARCHAR2(50);
1165 l_validate_status VARCHAR2(3);
1166 l_update_status VARCHAR2(3);
1167
1168 lx_error_rec OKL_API.error_rec_type;
1169 l_msg_idx INTEGER := G_FIRST;
1170 l_msg_tbl msg_tbl_type;
1171 l_api_name VARCHAR2(30) := 'terminate_vendor_prg';
1172 l_api_version CONSTANT NUMBER := G_API_VERSION;
1173
1174 G_EXCEPTION EXCEPTION;
1175
1176 BEGIN
1177
1178 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1179 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1180 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1181 'Begin(+)');
1182 END IF;
1183
1184 -- *************
1185 -- Check API version, initialize message list and create savepoint
1186 -- *************
1187 l_return_status := OKL_API.start_activity(
1188 p_api_name => l_api_name,
1189 p_pkg_name => G_PKG_NAME,
1190 p_init_msg_list => p_init_msg_list,
1191 l_api_version => l_api_version,
1192 p_api_version => p_api_version,
1193 p_api_type => '_PVT',
1194 x_return_status => x_return_status);
1195
1196 -- Rollback if error setting activity for api
1197 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1198 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1199 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1200 RAISE G_EXCEPTION_ERROR;
1201 END IF;
1202
1203 SELECT sysdate INTO l_sys_date FROM DUAL;
1204
1205 -- *************
1206 -- Validate and Populate VPA
1207 -- *************
1208 val_pop_vendor_prg(
1209 p_va_rec => p_va_rec,
1210 p_control_flag => p_control_flag,
1211 x_va_rec => l_va_rec,
1212 x_return_status => l_return_status);
1213
1214 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1216 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1217 'val_pop_vendor_prg = '||l_return_status );
1218 END IF;
1219
1220 -- Set Overall Status
1221 IF l_overall_status = G_RET_STS_SUCCESS
1222 AND l_overall_status <> G_RET_STS_UNEXP_ERROR THEN
1223 l_overall_status := l_return_status;
1224 END IF;
1225
1226 -- raise exception if api failed
1227 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1228 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1229 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1230 RAISE G_EXCEPTION_ERROR;
1231 END IF;
1232
1233 /*
1234 l_validate_status := l_return_status;
1235
1236 -- *************
1237 -- Populate or Insert VPA Transaction based on need
1238 -- *************
1239 pop_or_insert_transaction(
1240 p_va_rec => l_va_rec,
1241 p_sys_date => l_sys_date,
1242 p_validate_success => l_overall_status,
1243 x_trn_already_yn => l_trn_already_yn,
1244 px_tcnv_rec => l_tcnv_rec,
1245 x_return_status => l_return_status);
1246
1247 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1248 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1249 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1250 'pop_or_insert_transaction = '||l_return_status );
1251 END IF;
1252
1253 -- raise exception if api failed
1254 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1255 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1256 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1257 RAISE G_EXCEPTION_ERROR;
1258 END IF;
1259
1260 l_step := 'tmt_validated_yn';
1261
1262 -- *************
1263 -- Update VPA Transaction based on validate_YN success or fail
1264 -- *************
1265 update_transaction(
1266 p_va_rec => l_va_rec,
1267 p_status => l_validate_status,
1268 p_step => l_step,
1269 px_tcnv_rec => l_tcnv_rec,
1270 x_return_status => l_return_status);
1271
1272 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1273 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1274 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_vendor_prog.',
1275 'update_transaction = '||l_return_status ||
1276 'l_step = '||l_step );
1277 END IF;
1278
1279 -- raise exception if api failed
1280 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1281 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1282 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1283 RAISE G_EXCEPTION_ERROR;
1284 END IF;
1285
1286 */
1287 -- *************
1288 -- Update VPA
1289 -- *************
1290 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
1291
1292 update_vendor_prg(
1293 p_va_rec => p_va_rec,
1294 p_control_flag => p_control_flag,
1295 px_va_rec => l_va_rec,
1296 x_return_status => l_return_status);
1297
1298 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1299 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1300 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1301 'update_vendor_prg = '||l_return_status );
1302 END IF;
1303
1304 -- raise exception if api failed
1305 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1306 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1307 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1308 RAISE G_EXCEPTION_ERROR;
1309 END IF;
1310
1311 /*
1312 l_step := 'tmt_contract_updated_yn';
1313 l_update_status := l_return_status;
1314
1315 -- *************
1316 -- Update VPA Transaction based on contract_updated_YN success or fail
1317 -- *************
1318 update_transaction(
1319 p_va_rec => l_va_rec,
1320 p_status => l_update_status,
1321 p_step => l_step,
1322 px_tcnv_rec => l_tcnv_rec,
1323 x_return_status => l_return_status);
1324
1325 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1326 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1327 'OKL_AM_TERMINATE_INV_AGMT_PVT.terminate_vendor_prog.',
1328 'update_transaction = '||l_return_status ||
1329 'l_step = '||l_step );
1330 END IF;
1331
1332 -- raise exception if api failed
1333 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1334 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1335 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1336 RAISE G_EXCEPTION_ERROR;
1337 END IF;
1338 */
1339 END IF;
1340
1341 /*
1342 -- Store messages in TRX_MSGS
1343 OKL_AM_UTIL_PVT.process_messages(
1344 p_trx_source_table => 'OKL_TRX_CONTRACTS_V',
1345 p_trx_id => l_tcnv_rec.id,
1346 x_return_status => l_return_status);
1347
1348 -- raise exception if api failed
1349 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1350 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1352 RAISE G_EXCEPTION_ERROR;
1353 END IF;
1354 */
1355
1356 -- Set the output log if request from BATCH
1357 IF p_control_flag LIKE 'BATCH%' THEN
1358
1359 fnd_output (
1360 p_va_rec => l_va_rec,
1361 p_control_flag => 'PROCESSED');--l_tcnv_rec.tsu_code);
1362
1363 END IF;
1364
1365 -- set return status
1366 x_return_status := l_return_status;
1367
1368 -- End Activity
1369 OKL_API.end_activity (x_msg_count, x_msg_data);
1370
1371 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1373 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1374 'End(-)');
1375 END IF;
1376
1377 EXCEPTION
1378
1379 WHEN G_EXCEPTION_ERROR THEN
1380
1381 -- Set the output log if request from BATCH
1382 IF p_control_flag LIKE 'BATCH%' THEN
1383 fnd_output (
1384 p_va_rec => l_va_rec,
1385 p_control_flag => 'ERROR');
1386 END IF;
1387
1388 x_return_status := OKL_API.handle_exceptions(
1389 p_api_name => l_api_name,
1390 p_pkg_name => G_PKG_NAME,
1391 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data,
1394 p_api_type => '_PVT');
1395
1396 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1397 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1398 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1399 'EXP - G_EXCEPTION_ERROR');
1400 END IF;
1401
1402 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1403
1404 -- Set the output log if request from BATCH
1405 IF p_control_flag LIKE 'BATCH%' THEN
1406 fnd_output (
1407 p_va_rec => l_va_rec,
1408 p_control_flag => 'ERROR');
1409 END IF;
1410
1411 x_return_status := OKL_API.handle_exceptions(
1412 p_api_name => l_api_name,
1413 p_pkg_name => G_PKG_NAME,
1414 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data,
1417 p_api_type => '_PVT');
1418
1419 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1420 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1421 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1422 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1423 END IF;
1424
1425 WHEN OTHERS THEN
1426
1427 -- Set the output log if request from BATCH
1428 IF p_control_flag LIKE 'BATCH%' THEN
1429 fnd_output (
1430 p_va_rec => l_va_rec,
1431 p_control_flag => 'ERROR');
1432 END IF;
1433
1434 x_return_status := OKL_API.handle_exceptions(
1435 p_api_name => l_api_name,
1436 p_pkg_name => G_PKG_NAME,
1437 p_exc_name => 'OTHERS',
1438 x_msg_count => x_msg_count,
1439 x_msg_data => x_msg_data,
1440 p_api_type => '_PVT');
1441
1442 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1444 'OKL_AM_TERMNT_VENDOR_PRG_PVT.terminate_vendor_prog.',
1445 'EXP - OTHERS');
1446 END IF;
1447
1448 END terminate_vendor_prog;
1449
1450 -- Start of comments
1451 --
1452 -- Procedure Name : concurrent_expire_vend_prg
1453 -- Description : This procedure is called by concurrent manager to terminate
1454 -- ended Vendor Program agreements. When running the concurrent
1455 -- manager request, a request can be made for a single VPA to
1456 -- be terminated or else all the ended VPAs will be picked
1457 -- If No End Date is Passed Defaulted to SysDate
1458 -- Business Rules :
1459 -- Parameters :
1460 -- Version : 1.0
1461 -- History : RMUNJULU Created
1462 -- : RMUNJULU 115.4 3061748 Added code to throw error if
1463 -- Termination Date is invalid
1464 --
1465 -- End of comments
1466 PROCEDURE concurrent_expire_vend_prg(
1467 errbuf OUT NOCOPY VARCHAR2,
1468 retcode OUT NOCOPY VARCHAR2,
1469 p_api_version IN VARCHAR2,
1470 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1471 p_va_id IN VARCHAR2 DEFAULT NULL,
1472 p_date IN VARCHAR2 DEFAULT NULL) IS
1473
1474 -- Get the VPAs which have reached their end_date and still active
1475 -- This cursor should NOT be org specific as it needs to pick all VPAs
1476 CURSOR get_expired_va_csr (p_date IN DATE) IS
1477 SELECT CHR.id,
1478 CHR.contract_number va_number
1479 FROM OKC_K_HEADERS_B CHR
1480 WHERE CHR.scs_code IN ('PROGRAM','OPERATING') -- VPAs
1481 AND CHR.sts_code = 'ACTIVE' -- ACTIVE
1482 AND CHR.date_terminated IS NULL -- Not Terminated
1483 AND CHR.end_date <= TRUNC(p_date); -- Ended
1484
1485 l_return_status VARCHAR2(3);
1486 l_msg_count NUMBER;
1487 l_msg_data VARCHAR2(2000);
1488 l_date DATE;
1489 l_api_version NUMBER;
1490 l_va_id NUMBER;
1491 l_va_rec va_rec_type;
1492
1493 BEGIN
1494
1495 -- Initialize message list
1496 OKL_API.init_msg_list('T');
1497
1498 -- Set Processing date
1499 IF p_date IS NULL THEN
1500 l_date := sysdate;
1501 ELSE
1502 l_date := TO_DATE(p_date, 'MM/DD/YYYY');
1503 IF l_date > TRUNC(sysdate) THEN
1504 G_ERROR := 'Y';
1505 END IF;
1506 END IF;
1507
1508 -- If no error then
1509 IF G_ERROR <> 'Y' THEN
1510
1511 G_VPA_ENDED_BY_DATE := TRUNC(l_date);
1512
1513 l_api_version := TO_NUMBER(p_api_version);
1514 l_va_id := TO_NUMBER(p_va_id);
1515
1516 -- Check if a single IA termination request
1517 IF l_va_id IS NOT NULL THEN
1518
1519 l_va_rec.id := l_va_id;
1520
1521 -- Terminate the Vendor Program
1522 terminate_vendor_prog(
1523 p_api_version => l_api_version,
1524 p_init_msg_list => p_init_msg_list,
1525 x_return_status => l_return_status,
1526 x_msg_count => l_msg_count,
1527 x_msg_data => l_msg_data,
1528 p_va_rec => l_va_rec,
1529 p_control_flag => 'BATCH_SINGLE');
1530
1531 ELSE -- No Vendor Program passed, so scheduled request to terminate all expired Vendor Programs
1532
1533 -- Loop thru the expired VPAs
1534 FOR get_expired_va_rec IN get_expired_va_csr(G_VPA_ENDED_BY_DATE) LOOP
1535
1536 l_va_rec.id := get_expired_va_rec.id;
1537 l_va_rec.va_number := get_expired_va_rec.va_number;
1538
1539 -- Terminate the VPA
1540 terminate_vendor_prog(
1541 p_api_version => l_api_version,
1542 p_init_msg_list => p_init_msg_list,
1543 x_return_status => l_return_status,
1544 x_msg_count => l_msg_count,
1545 x_msg_data => l_msg_data,
1546 p_va_rec => l_va_rec,
1547 p_control_flag => 'BATCH_MULTIPLE');
1548
1549 END LOOP;
1550 END IF;
1551 END IF;
1552
1553 -- Create the Output Report
1554 create_report;
1555
1556 EXCEPTION
1557
1558 WHEN OTHERS THEN
1559 -- Set the oracle error message
1560 OKL_API.set_message(
1561 p_app_name => G_APP_NAME_1,
1562 p_msg_name => G_UNEXPECTED_ERROR,
1563 p_token1 => G_SQLCODE_TOKEN,
1564 p_token1_value => SQLCODE,
1565 p_token2 => G_SQLERRM_TOKEN,
1566 p_token2_value => SQLERRM);
1567
1568 END concurrent_expire_vend_prg;
1569
1570 END OKL_AM_TERMNT_VENDOR_PRG_PVT;