[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKL_AM_VENDOR_RES_SHARE_PVT
Source
1 PACKAGE BODY OKL_AM_VENDOR_RES_SHARE_PVT AS
2 /* $Header: OKLRVRSB.pls 120.9 2007/05/18 13:14:24 ansethur noship $ */
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 contract_number VARCHAR2(300),
20 start_date DATE,
21 end_date DATE,
22 status VARCHAR2(300),
23 asset_number VARCHAR2(300),
24 vpa_number VARCHAR2(300) );
25
26 -- Table Type to Store Recs of Message details with IA details
27 TYPE message_tbl_type IS TABLE OF message_rec_type INDEX BY BINARY_INTEGER;
28
29 -- Rec Type to Store Messages
30 TYPE share_rec_type IS RECORD (
31 party_id NUMBER,
32 pay_to_site_id NUMBER,
33 party_name VARCHAR2(300),
34 share_percent NUMBER);
35
36 TYPE share_tbl_type IS TABLE OF share_rec_type INDEX BY BINARY_INTEGER;
37
38 -- Rec Type to store contract Details
39 TYPE kle_rec_type IS RECORD (
40 id NUMBER,
41 asset_number VARCHAR2(300),
42 asset_start_date DATE,
43 asset_end_date DATE,
44 asset_sts_code OKC_K_LINES_B.sts_code%TYPE,
45 date_terminated DATE,
46 asset_residual OKL_K_LINES.residual_value%TYPE,
47 khr_id NUMBER,
48 contract_number OKC_K_HEADERS_B.contract_number%TYPE,
49 product_id NUMBER,
50 vpa_id NUMBER,
51 vpa_number OKC_K_HEADERS_B.contract_number%TYPE,
52 retirement_id NUMBER,
53 date_retired DATE,
54 scs_code OKC_K_HEADERS_B.scs_code%TYPE); -- rmunjulu 4622198
55
56 TYPE kle_tbl_type IS TABLE OF kle_rec_type INDEX BY BINARY_INTEGER;
57
58 -- Rec Type to store contract Details
59 TYPE rpt_rec_type IS RECORD (
60 PROGRAM_AGREEMENT VARCHAR2(300),
61 ASSET_NUMBER VARCHAR2(300),
62 ASSET_DESCRIPTION VARCHAR2(3000),
63 CONTRACT_NUMBER VARCHAR2(300),
64 ASSET_TERMINATION_DATE DATE,
65 VENDOR VARCHAR2(3000),
66 DISPOSITION_DATE DATE,
67 VENDOR_SHARE VARCHAR2(300));
68
69 TYPE rpt_tbl_type IS TABLE OF rpt_rec_type INDEX BY BINARY_INTEGER;
70
71 -- *********************
72 -- GLOBAL MESSAGE CONSTANTS
73 -- *********************
74 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
75 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
76 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
77 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
78 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
79
80 -- *********************
81 -- GLOBAL VARIABLES
82 -- *********************
83 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_AM_VENDOR_RES_SHARE_PVT';
84 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
85 G_APP_NAME_1 CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
86 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
87 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
88 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
89 G_API_VERSION CONSTANT NUMBER := 1;
90 G_MISS_CHAR CONSTANT VARCHAR2(1) := OKL_API.G_MISS_CHAR;
91 G_MISS_NUM CONSTANT NUMBER := OKL_API.G_MISS_NUM;
92 G_MISS_DATE CONSTANT DATE := OKL_API.G_MISS_DATE;
93 G_TRUE CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
94 G_FALSE CONSTANT VARCHAR2(1) := OKL_API.G_FALSE;
95 G_YES CONSTANT VARCHAR2(1) := 'Y';
96 G_NO CONSTANT VARCHAR2(1) := 'N';
97 G_FIRST CONSTANT NUMBER := FND_MSG_PUB.G_FIRST;
98 G_NEXT CONSTANT NUMBER := FND_MSG_PUB.G_NEXT;
99 G_ERROR VARCHAR2(1) := 'N';
100 G_KLE_ENDED_BY_DATE DATE;
101 success_message_table message_tbl_type;
102 error_message_table message_tbl_type;
103 l_error_count NUMBER := 1;
104
105 -- *********************
106 -- GLOBAL EXCEPTIONS
107 -- *********************
108 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
109 G_EXCEPTION_ERROR EXCEPTION;
110 G_EXCEPTION_HALT EXCEPTION;
111
112 -- *********************
113 -- GLOBAL DECLARATIONS
114 -- *********************
115 msg_lines_table msg_tbl_type;
116 l_success_tbl_index NUMBER := 1;
117 l_error_tbl_index NUMBER := 1;
118
119 -- Start of comments
120 --
121 -- Procedure Name : fnd_error_output
122 -- Desciption : Logs the messages in the output log
123 -- Business Rules :
124 -- Parameters :
125 -- Version : 1.0
126 -- History : RMUNJULU created
127 --
128 -- End of comments
129 PROCEDURE fnd_output (
130 p_kle_rec IN kle_rec_type,
131 p_control_flag IN VARCHAR2 ) IS
132
133 lx_error_rec OKL_API.error_rec_type;
134 l_msg_idx INTEGER := G_FIRST;
135
136 BEGIN
137
138 -- Get the messages in the log
139 LOOP
140
141 FND_MSG_PUB.get(
142 p_msg_index => l_msg_idx,
143 p_encoded => G_FALSE,
144 p_data => lx_error_rec.msg_data,
145 p_msg_index_out => lx_error_rec.msg_count);
146
147 IF (lx_error_rec.msg_count IS NOT NULL) THEN
148
149 -- Store the contract id
150 msg_lines_table(l_error_count).id := p_kle_rec.id;
151 msg_lines_table(l_error_count).msg := lx_error_rec.msg_data;
152
153 l_error_count := l_error_count + 1;
154 END IF;
155
156 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
157 OR (lx_error_rec.msg_count IS NULL));
158
159 l_msg_idx := G_NEXT;
160
161 END LOOP;
162
163
164 IF p_control_flag = 'PROCESSED' THEN
165
166 success_message_table(l_success_tbl_index).id := p_kle_rec.id;
167 success_message_table(l_success_tbl_index).contract_number := p_kle_rec.contract_number;
168 success_message_table(l_success_tbl_index).start_date := p_kle_rec.asset_start_date;
169 success_message_table(l_success_tbl_index).end_date := p_kle_rec.asset_end_date;
170 success_message_table(l_success_tbl_index).status := p_kle_rec.asset_sts_code;
171 success_message_table(l_success_tbl_index).asset_number := p_kle_rec.asset_number;
172 success_message_table(l_success_tbl_index).vpa_number := p_kle_rec.vpa_number;
173 l_success_tbl_index := l_success_tbl_index + 1;
174
175 ELSE
176
177 error_message_table(l_error_tbl_index).id := p_kle_rec.id;
178 error_message_table(l_error_tbl_index).contract_number := p_kle_rec.contract_number;
179 error_message_table(l_error_tbl_index).start_date := p_kle_rec.asset_start_date;
180 error_message_table(l_error_tbl_index).end_date := p_kle_rec.asset_end_date;
181 error_message_table(l_error_tbl_index).status := p_kle_rec.asset_sts_code;
182 error_message_table(l_error_tbl_index).asset_number := p_kle_rec.asset_number;
183 error_message_table(l_error_tbl_index).vpa_number := p_kle_rec.vpa_number;
184
185 l_error_tbl_index := l_error_tbl_index + 1;
186
187 END IF;
188
189 EXCEPTION
190
191 WHEN OTHERS THEN
192 -- Set the oracle error message
193 OKL_API.set_message(
194 p_app_name => G_APP_NAME_1,
195 p_msg_name => G_UNEXPECTED_ERROR,
196 p_token1 => G_SQLCODE_TOKEN,
197 p_token1_value => SQLCODE,
198 p_token2 => G_SQLERRM_TOKEN,
199 p_token2_value => SQLERRM);
200
201 END fnd_output;
202
203 -- Start of comments
204 --
205 -- Procedure Name : create_report
206 -- Desciption : Creates the Output and Log Reports
207 -- Business Rules :
208 -- Parameters :
209 -- Version : 1.0
210 -- History : RMUNJULU created
211 --
212 -- End of comments
213 PROCEDURE create_report IS
214
215 i NUMBER;
216 j NUMBER;
217 k NUMBER;
218 l_success NUMBER;
219 l_error NUMBER;
220
221 -- Get the Org Name
222 CURSOR org_csr (p_org_id IN NUMBER) IS
223 SELECT HOU.name
224 FROM HR_OPERATING_UNITS HOU
225 WHERE HOU.organization_id = p_org_id;
226
227 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
228
229 l_org_name VARCHAR2(300);
230 l_orcl_logo VARCHAR2(300);
231 l_term_heading VARCHAR2(300);
232 l_set_of_books VARCHAR2(300);
233 l_set_of_books_name VARCHAR2(300);
234 l_run_date VARCHAR2(300);
235 l_oper_unit VARCHAR2(300);
236 l_type VARCHAR2(300);
237 l_processed VARCHAR2(300);
238 l_serial VARCHAR2(300);
239 l_k_num VARCHAR2(300);
240 l_start_date VARCHAR2(300);
241 l_end_date VARCHAR2(300);
242 l_status VARCHAR2(300);
243 l_messages VARCHAR2(300);
244 l_eop VARCHAR2(300);
245 l_inv_ended_by VARCHAR2(300);
246 l_inv VARCHAR2(300);
247 l_print VARCHAR2(1);
248 msg_lines_table_index NUMBER;
249 l_vpa_num VARCHAR2(300);
250 l_kle_num VARCHAR2(300);
251 l_residual_shr_success VARCHAR2(300);
252 l_residual_shr_error VARCHAR2(300);
253
254 BEGIN
255
256 l_success := success_message_table.COUNT;
257 l_error := error_message_table.COUNT;
258
259 l_orcl_logo := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_ACCT_LEASE_MANAGEMENT');
260 l_term_heading := 'Vendor Residual Share';
261 l_set_of_books := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SET_OF_BOOKS');
262 l_run_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_RUN_DATE');
263 l_oper_unit := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_OPERUNIT');
264 l_type := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_TYPE');
265 l_processed := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_PROCESSED_ENTRIES');
266 l_serial := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SERIAL_NUMBER');
267 l_k_num := 'Contract Number';
268 l_start_date := 'Asset Start Date';
269 l_end_date := 'Asset End Date';
270 l_status := 'Asset Status';
271 l_messages := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_MESSAGES');
272 l_eop := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_OF_REPORT');
273 l_inv_ended_by := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INV_AGR_ENDED_BY');
274 l_inv := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_AM_INVALID_TERM_DATE');
275 l_vpa_num := 'Program Agreement';
276 l_kle_num := 'Asset Number';
277 l_residual_shr_success := 'Vendor Residual Share Successful';
278 l_residual_shr_error := 'Vendor Residual Share Errored';
279
280 l_set_of_books_name := OKL_ACCOUNTING_UTIL.get_set_of_books_name (OKL_ACCOUNTING_UTIL.get_set_of_books_id);
281
282 -- Get the Org Name
283 FOR org_rec IN org_csr (l_org_id) LOOP
284 l_org_name := org_rec.name;
285 END LOOP;
286
287 -- Valid Vendor Prg Chosen
288 IF G_ERROR <> 'Y' THEN
289
290 --log
291 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
292 FND_FILE.put_line(FND_FILE.log, l_type ||
293 RPAD(' ',40-LENGTH(l_type),' ') ||
294 l_processed);
295
296 FND_FILE.put_line(FND_FILE.log, RPAD('-',77 ,'-'));
297
298 FND_FILE.put_line(FND_FILE.log, l_residual_shr_success ||
299 RPAD(' ',40-LENGTH(l_residual_shr_success),' ') ||
300 l_success);
301
302 FND_FILE.put_line(FND_FILE.log, l_residual_shr_error ||
303 RPAD(' ',40-LENGTH(l_residual_shr_error),' ') ||
304 l_error);
305 FND_FILE.put_line(FND_FILE.log,'');
306 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
307
308 -- output
309 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_orcl_logo)/2, ' ' ) ||
310 l_orcl_logo);
311
312 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_term_heading)/2, ' ' ) ||
313 l_term_heading);
314
315 FND_FILE.put_line(FND_FILE.output, RPAD(' ',128/2-LENGTH(l_term_heading)/2 , ' ' ) ||
316 RPAD('-',LENGTH(l_term_heading),'-'));
317
318 FND_FILE.put_line(FND_FILE.output, '');
319
320 FND_FILE.put_line(FND_FILE.output, l_set_of_books ||' : '||
321 l_set_of_books_name ||
322 RPAD(' ', 128-LENGTH(l_set_of_books)-LENGTH(l_set_of_books_name)-LENGTH(l_run_date)-25, ' ' ) ||
323 l_run_date ||' : ' ||
324 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
325
326 FND_FILE.put_line(FND_FILE.output, l_oper_unit || ' : ' ||
327 l_org_name);
328
329 FND_FILE.put_line(FND_FILE.output,'');
330 FND_FILE.put_line(FND_FILE.output,'');
331
332 FND_FILE.put_line(FND_FILE.output, l_type ||
333 RPAD(' ',40-LENGTH(l_type),' ') ||
334 l_processed);
335
336 FND_FILE.put_line(FND_FILE.output, RPAD('-',128 ,'-'));
337
338 FND_FILE.put_line(FND_FILE.output, l_residual_shr_success ||
339 RPAD(' ',40-LENGTH(l_residual_shr_success),' ') ||
340 l_success);
341
342 FND_FILE.put_line(FND_FILE.output, l_residual_shr_error ||
343 RPAD(' ',40-LENGTH(l_residual_shr_error),' ') ||
344 l_error);
345
346 FND_FILE.put_line(FND_FILE.output,'');
347 FND_FILE.put_line(FND_FILE.output, RPAD('=',128,'=' ));
348 FND_FILE.put_line(FND_FILE.output,'');
349
350 -- Print VPAs Terminated Successfully
351 IF l_success > 0 THEN
352
353 FND_FILE.put_line(FND_FILE.output, l_residual_shr_success);
354 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_residual_shr_success), '-' ));
355 FND_FILE.put_line(FND_FILE.output,'');
356
357 l_print := 'N';
358
359 FOR i IN success_message_table.FIRST..success_message_table.LAST LOOP
360
361 IF l_print = 'N' THEN
362
363 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
364 l_kle_num || RPAD(' ',35-LENGTH(l_kle_num),' ')||
365 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
366 l_vpa_num || RPAD(' ',35-LENGTH(l_vpa_num),' ')||
367 l_start_date||RPAD(' ',20-LENGTH(l_start_date),' ') ||
368 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' '));
369
370 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
371 RPAD('-',LENGTH(l_kle_num),'-') || RPAD('-',35-LENGTH(l_kle_num),'-')||
372 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
373 RPAD('-',LENGTH(l_vpa_num),'-') || RPAD('-',35-LENGTH(l_vpa_num),'-')||
374 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',20-LENGTH(l_start_date),'-') ||
375 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-'));
376
377 l_print := 'Y';
378 END IF;
379
380 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
381 success_message_table(i).asset_number ||
382 RPAD(' ',35-LENGTH(success_message_table(i).asset_number),' ')||
383 success_message_table(i).contract_number ||
384 RPAD(' ',35-LENGTH(success_message_table(i).contract_number),' ')||
385 success_message_table(i).vpa_number ||
386 RPAD(' ',35-LENGTH(success_message_table(i).vpa_number),' ')||
387 success_message_table(i).start_date||
388 RPAD(' ',20-LENGTH(success_message_table(i).start_date),' ') ||
389 success_message_table(i).end_date||
390 RPAD(' ',15-LENGTH(success_message_table(i).end_date),' '));
391
392 END LOOP;
393 END IF;
394
395 FND_FILE.put_line(FND_FILE.output,'');
396
397 -- Print VPAs errored
398 IF l_error > 0 THEN
399
400 FND_FILE.put_line(FND_FILE.output, l_residual_shr_error);
401 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_residual_shr_error), '-' ));
402 FND_FILE.put_line(FND_FILE.output,'');
403
404 -- Initialize the table index
405 msg_lines_table_index := 1;
406
407 FOR i IN error_message_table.FIRST..error_message_table.LAST LOOP
408
409 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
410 l_kle_num || RPAD(' ',35-LENGTH(l_kle_num),' ')||
411 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
412 l_vpa_num || RPAD(' ',35-LENGTH(l_vpa_num),' ')||
413 l_start_date||RPAD(' ',20-LENGTH(l_start_date),' ') ||
414 l_end_date||RPAD(' ',15-LENGTH(l_end_date),' '));
415
416 FND_FILE.put_line(FND_FILE.output, RPAD('-',LENGTH(l_serial),'-') || RPAD('-',15-LENGTH(l_serial),'-')||
417 RPAD('-',LENGTH(l_kle_num),'-') || RPAD('-',35-LENGTH(l_kle_num),'-')||
418 RPAD('-',LENGTH(l_k_num),'-') || RPAD('-',35-LENGTH(l_k_num),'-')||
419 RPAD('-',LENGTH(l_vpa_num),'-') || RPAD('-',35-LENGTH(l_vpa_num),'-')||
420 RPAD('-',LENGTH(l_start_date),'-')||RPAD('-',20-LENGTH(l_start_date),'-') ||
421 RPAD('-',LENGTH(l_end_date),'-')||RPAD('-',15-LENGTH(l_end_date),'-'));
422
423 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
424 error_message_table(i).asset_number ||
425 RPAD(' ',35-LENGTH(error_message_table(i).asset_number),' ')||
426 error_message_table(i).contract_number ||
427 RPAD(' ',35-LENGTH(error_message_table(i).contract_number),' ')||
428 error_message_table(i).vpa_number ||
429 RPAD(' ',35-LENGTH(error_message_table(i).vpa_number),' ')||
430 error_message_table(i).start_date||
431 RPAD(' ',20-LENGTH(error_message_table(i).start_date),' ') ||
432 error_message_table(i).end_date||
433 RPAD(' ',15-LENGTH(error_message_table(i).end_date),' '));
434
435 FND_FILE.put_line(FND_FILE.output,'');
436
437 FND_FILE.put_line(FND_FILE.output, RPAD(' ',5,' ') || l_messages || ' :');
438
439 k := 1;
440 FOR j IN msg_lines_table_index .. msg_lines_table.LAST LOOP
441 IF msg_lines_table(j).id = error_message_table(i).id THEN
442 FND_FILE.put(FND_FILE.output, RPAD(' ',5,' ') || k || ': ' || msg_lines_table(j).msg);
443 FND_FILE.put_line(FND_FILE.output,'');
444 k := k + 1;
445 ELSE
446 msg_lines_table_index := j ;
447 EXIT;
448 END IF;
449
450 END LOOP;
451
452 FND_FILE.put_line(FND_FILE.output,'');
453
454 END LOOP;
455
456 END IF;
457
458 FND_FILE.put_line(FND_FILE.output,'');
459 FND_FILE.put_line(FND_FILE.output,'');
460 FND_FILE.put_line(FND_FILE.output, RPAD(' ', 53 , ' ' ) || l_eop);
461
462 ELSE
463
464 FND_FILE.put_line(FND_FILE.log,l_processed || ' = 0');
465 FND_FILE.put_line(FND_FILE.log,l_inv);
466
467 END IF;
468 EXCEPTION
469
470 WHEN OTHERS THEN
471 -- Set the oracle error message
472 OKL_API.set_message(
473 p_app_name => G_APP_NAME_1,
474 p_msg_name => G_UNEXPECTED_ERROR,
475 p_token1 => G_SQLCODE_TOKEN,
476 p_token1_value => SQLCODE,
477 p_token2 => G_SQLERRM_TOKEN,
478 p_token2_value => SQLERRM);
479
480 END create_report;
481
482
483
484 -- Start of comments
485 --
486 -- Procedure Name : get_residual_share_terms
487 -- Description : procedure to do get residual share terms and residual share formula
488 -- for the contracts program agreement
489 -- Business Rules :
490 -- Parameters :
491 -- Version : 1.0
492 -- History : RMUNJULU Created
493 --
494 -- End of comments
495 PROCEDURE get_residual_share_terms(
496 x_return_status OUT NOCOPY VARCHAR2,
497 p_kle_rec IN kle_rec_type,
498 x_share_formula OUT NOCOPY VARCHAR2,
499 x_share_tbl OUT NOCOPY OKL_RULE_PUB.rulv_tbl_type) IS
500
501 l_return_status VARCHAR2(3) := G_RET_STS_SUCCESS;
502 l_rgpv_tbl okl_rule_pub.rgpv_tbl_type;
503 l_rulv_tbl okl_rule_pub.rulv_tbl_type;
504 l_rulv_rec okl_rule_pub.rulv_rec_type;
505
506 l_api_version CONSTANT NUMBER := g_api_version;
507 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
508 l_msg_data VARCHAR2(2000);
509
510 l_rg_count NUMBER;
511 l_rule_count NUMBER;
512
513 l_no_rule_data EXCEPTION;
514 i NUMBER;
515
516 BEGIN
517
518 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
520 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
521 'Begin(+)');
522 END IF;
523
524 -- get rule data
525 OKL_RULE_APIS_PUB.get_contract_rgs (
526 p_api_version => l_api_version,
527 p_init_msg_list => OKL_API.G_FALSE,
528 p_chr_id => p_kle_rec.vpa_id,
529 p_cle_id => NULL,
530 p_rgd_code => 'VGLRS',
531 x_return_status => l_return_status,
532 x_msg_count => l_msg_count,
533 x_msg_data => l_msg_data,
534 x_rgpv_tbl => l_rgpv_tbl,
535 x_rg_count => l_rg_count);
536
537 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
538 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
539 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
540 RAISE OKL_API.G_EXCEPTION_ERROR;
541 ELSIF (NVL (l_rg_count, 0) <> 1) THEN
542
543 -- Unable to complete process due to missing
544 -- information (RULE rule in GROUP group)
545 OKL_API.set_message (
546 p_app_name => OKL_API.G_APP_NAME,
547 p_msg_name => 'OKL_AM_VRS_NO_RULE_DATA',
548 p_token1 => 'GROUP',
549 p_token1_value => 'VGLRS',
550 p_token2 => 'RULE',
551 p_token2_value => 'VGLRSP');
552
553 RAISE l_no_rule_data;
554 END IF;
555
556 -- Get vendor share party details -- multiple records
557 OKL_RULE_APIS_PUB.get_contract_rules (
558 p_api_version => l_api_version,
559 p_init_msg_list => OKL_API.G_FALSE,
560 p_rgpv_rec => l_rgpv_tbl(1),
561 p_rdf_code => 'VGLRSP',
562 x_return_status => l_return_status,
563 x_msg_count => l_msg_count,
564 x_msg_data => l_msg_data,
565 x_rulv_tbl => l_rulv_tbl,
566 x_rule_count => l_rule_count);
567
568 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
569 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
570 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
571 RAISE OKL_API.G_EXCEPTION_ERROR;
572 ELSIF (NVL (l_rule_count, 0) = 0 ) THEN
573
574 -- Unable to complete process due to missing
575 -- information (RULE rule in GROUP group)
576 OKL_API.set_message (
577 p_app_name => OKL_API.G_APP_NAME,
578 p_msg_name => 'OKL_AM_VRS_NO_RULE_DATA',
579 p_token1 => 'GROUP',
580 p_token1_value => 'VGLRS',
581 p_token2 => 'RULE',
582 p_token2_value => 'VGLRSP');
583
584 RAISE l_no_rule_data;
585 END IF;
586
587 -- get vendor share formula
588 OKL_AM_UTIL_PVT.get_rule_record(
589 p_rgd_code => 'VGLRS',
590 p_rdf_code => 'VGLRSF',
591 p_chr_id => p_kle_rec.vpa_id,
592 p_cle_id => NULL,
593 p_message_yn => TRUE,
594 x_rulv_rec => l_rulv_rec,
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data);
598
599 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
600 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
602 RAISE OKL_API.G_EXCEPTION_ERROR;
603 END IF;
604
605 -- set return status
606 x_return_status := l_return_status;
607 x_share_tbl := l_rulv_tbl;
608 x_share_formula := l_rulv_rec.rule_information1;
609
610 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
611 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
612 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
613 'End(-)');
614 END IF;
615
616 EXCEPTION
617
618 WHEN l_no_rule_data THEN
619
620 x_return_status := OKL_API.G_RET_STS_ERROR;
621
622 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
624 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
625 'EXP - l_no_rule_data');
626 END IF;
627
628 WHEN G_EXCEPTION_ERROR THEN
629
630 x_return_status := OKL_API.G_RET_STS_ERROR;
631
632 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
633 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
634 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
635 'EXP - G_EXCEPTION_ERROR');
636 END IF;
637
638 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
639
640 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
641
642 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
643 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
644 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
645 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
646 END IF;
647
648 WHEN OTHERS THEN
649
650 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
651
652 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
654 'OKL_AM_VENDOR_RES_SHARE_PVT.get_residual_share_terms.',
655 'EXP - OTHERS');
656 END IF;
657
658 END get_residual_share_terms;
659
660 -- Start of comments
661 --
662 -- Procedure Name : vendor_share
663 -- Description : procedure to do vendor residual share for each Terminated/Expired eligible asset
664 -- Business Rules :
665 -- Parameters :
666 -- Version : 1.0
667 -- History : RMUNJULU Created
668 --
669 -- End of comments
670 PROCEDURE vendor_share(
671 p_api_version IN NUMBER,
672 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676 p_kle_rec IN kle_rec_type,
677 p_sys_date IN DATE,
678 p_share_tbl IN share_tbl_type,
679 p_control_flag IN VARCHAR2 DEFAULT NULL) IS
680
681 CURSOR get_program_info_csr IS
682 SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
683 DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
684 DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
685 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
686 mo_global.get_current_org_id()
687 FROM DUAL;
688
689 CURSOR c_app_info IS
690 SELECT APPLICATION_ID
691 FROM FND_APPLICATION
692 WHERE APPLICATION_SHORT_NAME = 'OKL' ;
693
694 CURSOR get_pay_to_site_csr (p_party_id NUMBER) IS
695 SELECT PAR.pay_site_id,
696 PV.vendor_name
697 FROM OKL_PARTY_PAYMENT_DTLS_V PAR,
698 PO_VENDORS PV,
699 PO_VENDOR_SITES_ALL VS
700 WHERE PAR.cpl_id = p_party_id
701 AND PAR.pay_site_id = VS.vendor_site_id(+)
702 AND VS.vendor_id = PV.vendor_id;
703
704 CURSOR get_party_role_csr (p_party_id NUMBER) IS
705 SELECT ROL.rle_code party_role
706 FROM OKC_K_PARTY_ROLES_V ROL
707 WHERE ROL.id = p_party_id;
708
709 l_return_status VARCHAR2(3) := G_RET_STS_SUCCESS;
710 l_msg_tbl msg_tbl_type;
711 l_api_name VARCHAR2(30) := 'do_vendor_share';
712 l_api_version CONSTANT NUMBER := G_API_VERSION;
713 l_kle_rec kle_rec_type := p_kle_rec;
714 l_share_tbl okl_rule_pub.rulv_tbl_type;
715 l_tapv_rec OKL_TAP_PVT.tapv_rec_type ;
716 x_tapv_rec OKL_TAP_PVT.tapv_rec_type ;
717 l_tplv_rec OKL_TPL_PVT.tplv_rec_type;
718 x_tplv_rec OKL_TPL_PVT.tplv_rec_type;
719
720 l_add_params OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
721 lp_thpv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
722 lx_thpv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
723 lp_tlpv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
724 lx_tlpv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
725 l_share_formula VARCHAR2(300);
726 l_request_id NUMBER;
727 l_program_application_id NUMBER;
728 l_program_id NUMBER;
729 l_program_update_date DATE;
730 l_org_id NUMBER;
731 l_sty_id NUMBER;
732 lx_subsidy_amount NUMBER;
733 l_app_id NUMBER;
734 l_trx_type_id NUMBER;
735 l_residual_shr_try_id NUMBER;
736 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
737 lx_dbseqnm VARCHAR2(2000):= '';
738 lx_dbseqid NUMBER := NULL;
739 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
740 l_msg_data VARCHAR2(2000);
741 l_pay_to_site_id NUMBER;
742 l_share_percent NUMBER;
743 l_party_role VARCHAR2(350);
744 l_party_id NUMBER;
745 l_party_name VARCHAR2(350);
746 l_share_amount NUMBER;
747 l_share_amount_for_party NUMBER;
748 i NUMBER;
749 l_sob_id NUMBER;
750 l_trans_meaning VARCHAR2(3000);
751 l_retirement_id NUMBER;
752 l_retirement_date DATE;
753
754 l_functional_currency_code VARCHAR2(15);
755 l_contract_currency_code VARCHAR2(15);
756 l_currency_conversion_type VARCHAR2(30);
757 l_currency_conversion_rate NUMBER;
758 l_currency_conversion_date DATE;
759 l_converted_amount NUMBER;
760
761 -- Since we do not use the amount or converted amount
762 -- set a hardcoded value for the amount (and pass to to
763 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
764 -- conversion values )
765 l_hard_coded_amount NUMBER := 100;
766
767 -- rmunjulu 4622198
768 l_fact_synd_code FND_LOOKUPS.lookup_code%TYPE;
769 l_inv_acct_code OKC_RULES_B.rule_information1%TYPE;
770
771 /* 29-JAN-2007 ANSETHUR BUILD: R12 B DISBURSEMENT Start Changes */
772 l_tplv_tbl okl_tpl_pvt.tplv_tbl_type ;
773 x_tplv_tbl okl_tpl_pvt.tplv_tbl_type ;
774 /* 29-JAN-2007 ANSETHUR BUILD: R12 B DISBURSEMENT End Changes */
775
776 BEGIN
777
778 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
779 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
780 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
781 'Begin(+)');
782 END IF;
783
784 /*
785 -- *************
786 -- Check API version, initialize message list and create savepoint
787 -- *************
788 l_return_status := OKL_API.start_activity(
789 p_api_name => l_api_name,
790 p_pkg_name => G_PKG_NAME,
791 p_init_msg_list => p_init_msg_list,
792 l_api_version => l_api_version,
793 p_api_version => p_api_version,
794 p_api_type => '_PVT',
795 x_return_status => x_return_status);
796
797
798 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
800 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
801 'start_activity = '||l_return_status );
802 END IF;
803
804 -- Rollback if error setting activity for api
805 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
806 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
807 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
808 RAISE G_EXCEPTION_ERROR;
809 END IF;
810 */
811 savepoint vend_res_share;
812 -- Start processing
813 -- get the residual share terms and conditions
814 get_residual_share_terms(
815 x_return_status => l_return_status,
816 p_kle_rec => l_kle_rec,
817 x_share_formula => l_share_formula,
818 x_share_tbl => l_share_tbl);
819
820 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
821
822 -- Message:
823 OKL_API.set_message(
824 p_app_name => G_APP_NAME,
825 p_msg_name => 'OKL_AM_VRS_SHARE_TERMS');
826
827 RAISE G_EXCEPTION_ERROR;
828
829 END IF;
830
831 -- raise exception
832 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
833 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
834 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
835 RAISE G_EXCEPTION_ERROR;
836 END IF;
837
838 -- rmunjulu 4622198 SPECIAL_ACCNT Get special accounting details
839 OKL_SECURITIZATION_PVT.check_khr_ia_associated(
840 p_api_version => p_api_version
841 ,p_init_msg_list => OKL_API.G_FALSE
842 ,x_return_status => l_return_status
843 ,x_msg_count => x_msg_count
844 ,x_msg_data => x_msg_data
845 ,p_khr_id => p_kle_rec.khr_id
846 ,p_scs_code => p_kle_rec.scs_code
847 ,p_trx_date => sysdate
848 ,x_fact_synd_code => l_fact_synd_code
849 ,x_inv_acct_code => l_inv_acct_code
850 );
851
852 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
853 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
855 RAISE G_EXCEPTION_ERROR;
856 END IF;
857
858 IF l_share_tbl.count > 1 THEN
859
860 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency();
861
862 -- Get the currency conversion details from ACCOUNTING_Util
863 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
864 p_khr_id => p_kle_rec.khr_id,
865 p_to_currency => l_functional_currency_code,
866 p_transaction_date => SYSDATE,
867 p_amount => l_hard_coded_amount,
868 x_return_status => l_return_status,
869 x_contract_currency => l_contract_currency_code,
870 x_currency_conversion_type => l_currency_conversion_type,
871 x_currency_conversion_rate => l_currency_conversion_rate,
872 x_currency_conversion_date => l_currency_conversion_date,
873 x_converted_amount => l_converted_amount);
874
875 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
876 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
877 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
878 RAISE G_EXCEPTION_ERROR;
879 END IF;
880
881 -- Get Application Info
882 OPEN c_app_info ;
883 FETCH c_app_info INTO l_app_id;
884 IF(c_app_info%NOTFOUND) THEN
885 -- Message:
886 OKL_API.set_message(
887 p_app_name => G_APP_NAME,
888 p_msg_name => 'OKL_AM_VRS_APPS_INFO');
889 CLOSE c_app_info ;
890 RAISE OKL_API.G_EXCEPTION_ERROR;
891 END IF ;
892 CLOSE c_app_info;
893
894 -- get Disbursement transaction type
895 OKL_AM_UTIL_PVT.get_transaction_id (
896 p_try_name => 'Disbursement',
897 x_return_status => l_return_status,
898 x_try_id => l_trx_type_id);
899
900 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
901
902 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
903 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
904 p_lookup_code => 'DISBURSEMENT',
905 p_validate_yn => 'Y');
906
907 OKL_API.set_message(
908 p_app_name => 'OKL',
909 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
910 p_token1 => 'TRY_NAME',
911 p_token1_value => l_trans_meaning);
912
913 RAISE OKL_API.G_EXCEPTION_ERROR;
914 END IF ;
915
916 -- get vendor residual share transaction type
917 OKL_AM_UTIL_PVT.get_transaction_id (
918 p_try_name => 'Vendor Residual Share',
919 x_return_status => l_return_status,
920 x_try_id => l_residual_shr_try_id);
921
922 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
923
924 l_trans_meaning := OKL_AM_UTIL_PVT.get_lookup_meaning(
925 p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
926 p_lookup_code => 'VENDOR RESIDUAL SHARE',
927 p_validate_yn => 'Y');
928
929 OKL_API.set_message(
930 p_app_name => 'OKL',
931 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
932 p_token1 => 'TRY_NAME',
933 p_token1_value => l_trans_meaning);
934
935 RAISE OKL_API.G_EXCEPTION_ERROR;
936 END IF ;
937
938 -- get program info
939 OPEN get_program_info_csr;
940 FETCH get_program_info_csr INTO
941 l_request_id,
942 l_program_application_id,
943 l_program_id,
944 l_program_update_date,
945 l_org_id;
946 CLOSE get_program_info_csr;
947
948 -- get stream type id for purpose for the contract
949 OKL_STREAMS_UTIL.get_primary_stream_type(
950 p_khr_id => l_kle_rec.khr_id,
951 p_primary_sty_purpose => 'VENDOR_RESIDUAL_SHARING', -- new purpose code
952 x_return_status => l_return_status,
953 x_primary_sty_id => l_sty_id);
954
955 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
956 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
957 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
958 RAISE G_EXCEPTION_ERROR;
959 END IF;
960
961 -- get set of books id
962 l_sob_id := OKL_ACCOUNTING_UTIL.get_set_of_books_id;
963
964 -- DEFAULT SHARE FORMULA
965 IF l_share_formula IS NULL THEN
966 l_share_formula := 'VENDOR_RESIDUAL_SHARE';
967 END IF;
968
969 -- pass retirement transaction id and get the gain/loss for that retirement transaction
970 l_add_params(1).name := 'retirement_id';
971 l_add_params(1).value := to_char(l_kle_rec.retirement_id);
972
973 -- Evaluate share formula for asset and contract, pass share percent for relevant party
974 OKL_AM_UTIL_PVT.get_formula_value (
975 p_formula_name => l_share_formula,
976 p_chr_id => p_kle_rec.khr_id,
977 p_cle_id => p_kle_rec.id,
978 p_additional_parameters => l_add_params,
979 x_formula_value => l_share_amount, -- get back share amt in contract currency
980 x_return_status => l_return_status);
981
982 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
983
984 -- Message:
985 OKL_API.set_message(
986 p_app_name => G_APP_NAME,
987 p_msg_name => 'OKL_AM_VRS_SHARE_FORMULA');
988
989 END IF;
990
991 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
992 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
993 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
994 RAISE G_EXCEPTION_ERROR;
995 END IF;
996
997 IF (to_number(l_share_amount) = 0 ) THEN
998
999 -- Message:
1000 OKL_API.set_message(
1001 p_app_name => G_APP_NAME,
1002 p_msg_name => 'OKL_AM_VRS_SHARE_FORMULA_ZERO');
1003
1004 RAISE OKL_API.G_EXCEPTION_ERROR;
1005 END IF;
1006
1007 -- create header transaction record in okl_trx_assets
1008 lp_thpv_rec.tas_type := 'VRS'; -- new lookup seeded
1009 lp_thpv_rec.tsu_code := 'PROCESSED';
1010 lp_thpv_rec.try_id := l_residual_shr_try_id;
1011 lp_thpv_rec.date_trans_occurred := sysdate;
1012
1013 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
1014 p_api_version => p_api_version,
1015 p_init_msg_list => OKL_API.G_FALSE,
1016 x_return_status => l_return_status,
1017 x_msg_count => x_msg_count,
1018 x_msg_data => x_msg_data,
1019 p_thpv_rec => lp_thpv_rec,
1020 x_thpv_rec => lx_thpv_rec);
1021
1022 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1023 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1024 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1025 RAISE G_EXCEPTION_ERROR;
1026 END IF;
1027
1028 i := l_share_tbl.FIRST;
1029 LOOP
1030
1031 l_party_id := to_number(l_share_tbl(i).rule_information1);
1032 l_share_percent := nvl(to_number(l_share_tbl(i).rule_information2),0);
1033
1034 -- party role
1035 OPEN get_party_role_csr (l_party_id);
1036 FETCH get_party_role_csr INTO l_party_role;
1037 CLOSE get_party_role_csr;
1038
1039 IF l_party_role <> 'LESSOR' THEN -- do not do residual sharing invoicing for LESSOR
1040
1041 -- get party pay to site id
1042 OPEN get_pay_to_site_csr (l_party_id);
1043 FETCH get_pay_to_site_csr INTO l_pay_to_site_id, l_party_name;
1044 CLOSE get_pay_to_site_csr;
1045
1046 IF l_pay_to_site_id IS NULL THEN
1047
1048 -- Message: No Pay site defined for Vendor VENDOR_NAME.
1049 OKL_API.set_message(
1050 p_app_name => G_APP_NAME,
1051 p_msg_name => 'OKL_AM_VRS_PAY_SITE_ERROR',
1052 p_token1 => 'VENDOR',
1053 p_token1_value => l_party_name);
1054
1055 RAISE OKL_API.G_EXCEPTION_ERROR;
1056 END IF;
1057
1058 -- get share amount for the party based on share percent
1059 l_share_amount_for_party := to_number(l_share_amount) * l_share_percent/100;
1060
1061 -- Do AP debit/credit memo
1062 l_tapv_rec.invoice_number := FND_SEQNUM.get_next_sequence
1063 (appid => l_app_id,
1064 cat_code => l_document_category,
1065 sobid => l_sob_id,
1066 met_code => 'A',
1067 trx_date => SYSDATE,
1068 dbseqnm => lx_dbseqnm,
1069 dbseqid => lx_dbseqid);
1070
1071 l_tapv_rec.amount := l_share_amount_for_party;
1072 l_tapv_rec.ipvs_id := l_pay_to_site_id;
1073 l_tapv_rec.vendor_id := p_kle_rec.vpa_id;
1074 l_tapv_rec.sfwt_flag := 'N' ;
1075 l_tapv_rec.trx_status_code := 'ENTERED' ;
1076 l_tapv_rec.currency_code := l_contract_currency_code;
1077 l_tapv_rec.currency_conversion_type := l_currency_conversion_type;
1078 l_tapv_rec.currency_conversion_rate := l_currency_conversion_rate;
1079 l_tapv_rec.currency_conversion_date := l_currency_conversion_date;
1080 l_tapv_rec.set_of_books_id := l_sob_id;
1081 l_tapv_rec.try_id := l_trx_type_id;
1082 -- sjalasut, assigned khr_id to null at the transaction header level.
1083 -- changes made as part of OKLR12B disbursements project.
1084 l_tapv_rec.khr_id := NULL; -- p_kle_rec.khr_id ;
1085 l_tapv_rec.invoice_type := 'STANDARD';
1086 l_tapv_rec.workflow_yn := 'N';
1087 l_tapv_rec.consolidate_yn := 'N';
1088 l_tapv_rec.wait_vendor_invoice_yn := 'N';
1089 l_tapv_rec.date_invoiced := SYSDATE;
1090 l_tapv_rec.date_gl := SYSDATE;
1091 l_tapv_rec.date_entered := SYSDATE;
1092 l_tapv_rec.object_version_number := 1;
1093 l_tapv_rec.request_id := l_request_id;
1094 l_tapv_rec.program_application_id := l_program_application_id;
1095 l_tapv_rec.program_id := l_program_id;
1096 l_tapv_rec.program_update_date := l_program_update_date;
1097 l_tapv_rec.org_id := l_org_id;
1098 --20-NOV-2006 ANSETHUR R12B - LEGAL ENTITY UPTAKE PROJECT
1099 l_tapv_rec.legal_entity_id :=OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_kle_rec.khr_id);
1100
1101 -- Populate internal AP invoice Lines Record
1102 -- sjalasut, added code to have khr_id moved to the line level.
1103 -- changes made as part of OKLR12B disbursements project.
1104 l_tplv_rec.khr_id := p_kle_rec.khr_id ;
1105 l_tplv_rec.amount := l_share_amount_for_party;
1106 l_tplv_rec.sty_id := l_sty_id;
1107 l_tplv_rec.inv_distr_line_code := 'MANUAL';
1108 l_tplv_rec.line_number := 1;
1109 l_tplv_rec.org_id := l_tapv_rec.org_id;
1110 l_tplv_rec.disbursement_basis_code := 'BILL_DATE';
1111
1112
1113 /* 29-JAN-2007 ANSETHUR BUILD: R12 B DISBURSEMENT Start changes */
1114
1115 l_tplv_tbl(0) := l_tplv_rec;
1116
1117 OKL_CREATE_DISB_TRANS_PVT.create_disb_trx(p_api_version
1118 ,p_init_msg_list => OKL_API.G_FALSE
1119 ,x_return_status => l_return_status
1120 ,x_msg_count => x_msg_count
1121 ,x_msg_data => x_msg_data
1122 ,p_tapv_rec => l_tapv_rec
1123 ,p_tplv_tbl => l_tplv_tbl
1124 ,x_tapv_rec => x_tapv_rec
1125 ,x_tplv_tbl => x_tplv_tbl
1126 );
1127
1128
1129 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1130 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1131 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_ERROR;
1133 END IF;
1134
1135 /* 29-JAN-2007 ANSETHUR BUILD: R12 B DISBURSEMENT End changes */
1136
1137 -- Insert transaction in transaction tables okl_trx_assets and okl_txl_assets
1138 lp_tlpv_rec.currency_code := l_contract_currency_code;
1139 lp_tlpv_rec.tas_id := lx_thpv_rec.id;
1140 lp_tlpv_rec.kle_id := l_kle_rec.id;
1141 lp_tlpv_rec.line_number := 1;
1142 lp_tlpv_rec.tal_type := 'VRS'; -- new lookup seeded
1143 lp_tlpv_rec.asset_number := l_kle_rec.asset_number;
1144 lp_tlpv_rec.dnz_khr_id := l_kle_rec.khr_id;
1145 lp_tlpv_rec.fa_trx_date := l_kle_rec.date_retired; -- store disposition date
1146 lp_tlpv_rec.residual_shr_party_id := l_party_id; -- new column
1147 lp_tlpv_rec.residual_shr_amount := l_share_amount_for_party; -- new column
1148 lp_tlpv_rec.retirement_id := l_kle_rec.retirement_id; -- new column store retirement id
1149
1150
1151 lp_tlpv_rec.original_cost := 0; -- needed for the TAPI
1152 lp_tlpv_rec.current_units := 1; -- needed for the TAPI
1153
1154 -- insert record in txl lines table
1155 OKL_TXL_ASSETS_PUB.create_txl_asset_def(
1156 p_api_version => p_api_version,
1157 p_init_msg_list => OKL_API.G_FALSE,
1158 x_return_status => l_return_status,
1159 x_msg_count => x_msg_count,
1160 x_msg_data => x_msg_data,
1161 p_tlpv_rec => lp_tlpv_rec,
1162 x_tlpv_rec => lx_tlpv_rec);
1163
1164 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1165 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1166 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1167 RAISE G_EXCEPTION_ERROR;
1168 END IF;
1169
1170 END IF;
1171 EXIT WHEN (i = l_share_tbl.LAST);
1172 i := l_share_tbl.NEXT(i);
1173 END LOOP;
1174 END IF;
1175
1176 -- set return status
1177 x_return_status := l_return_status;
1178
1179 -- End Activity
1180 --OKL_API.end_activity (x_msg_count, x_msg_data);
1181
1182 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1183 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1184 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
1185 'End(-)');
1186 END IF;
1187
1188 EXCEPTION
1189
1190 WHEN G_EXCEPTION_ERROR THEN
1191 /*
1192 x_return_status := OKL_API.handle_exceptions(
1193 p_api_name => l_api_name,
1194 p_pkg_name => G_PKG_NAME,
1195 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 */
1199 rollback to vend_res_share;
1200 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1201 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1202 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
1203 'EXP - G_EXCEPTION_ERROR');
1204 END IF;
1205
1206 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1207 /*
1208 x_return_status := OKL_API.handle_exceptions(
1209 p_api_name => l_api_name,
1210 p_pkg_name => G_PKG_NAME,
1211 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data,
1214 p_api_type => '_PVT');
1215 */
1216 rollback to vend_res_share;
1217 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1218 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1219 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
1220 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1221 END IF;
1222
1223 WHEN OTHERS THEN
1224 /*
1225 x_return_status := OKL_API.handle_exceptions(
1226 p_api_name => l_api_name,
1227 p_pkg_name => G_PKG_NAME,
1228 p_exc_name => 'OTHERS',
1229 x_msg_count => x_msg_count,
1230 x_msg_data => x_msg_data,
1231 p_api_type => '_PVT');
1232 */
1233 rollback to vend_res_share;
1234 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1236 'OKL_AM_VENDOR_RES_SHARE_PVT.vendor_share.',
1237 'EXP - OTHERS');
1238 END IF;
1239
1240 END vendor_share;
1241
1242 -- Start of comments
1243 --
1244 -- Procedure Name : do_vendor_share
1245 -- Description : Procedure to do vendor share
1246 -- Pick assets to do vendor share with following conditions
1247 -- Has not been already picked up for vendor residual sharing (check transaction in OKL)
1248 -- Has Vendor program attached with residual sharing defined (check contract and vendor program)
1249 -- Has terminated or expired (check asset status)
1250 -- Has been sold through asset disposal (Term with purchase/remarket/scrap/sale) (check transaction in FA)
1251 -- This API will do residual share for the same asset if there is a new retirement transaction (ie multiple shares for multiple partial retirements)
1252 -- Business Rules :
1253 -- Parameters :
1254 -- Version : 1.0
1255 -- History : RMUNJULU Created
1256 --
1257 -- End of comments
1258 PROCEDURE do_vendor_share(
1259 p_api_version IN NUMBER,
1260 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2,
1264 p_control_flag IN VARCHAR2 DEFAULT NULL) IS
1265
1266 -- Get the terminated/expired assets for the org which has residual sharing and which have not been picked earlier
1267 CURSOR get_expired_kle_csr (p_date IN DATE) IS
1268 SELECT CLE.id,
1269 CLE.name asset_number,
1270 CHR.id khr_id,
1271 CHR.contract_number contract_number,
1272 CLE.start_date,
1273 CLE.end_date,
1274 CLE.date_terminated,
1275 KLE.residual_value,
1276 VPA.id vpa_id,
1277 VPA.contract_number vpa_number,
1278 KHR.pdt_id,
1279 RET.retirement_id,
1280 RET.date_retired,
1281 CHR.scs_code -- rmunjulu 4622198
1282 FROM OKC_K_LINES_V CLE,
1283 OKL_K_LINES KLE,
1284 OKC_K_HEADERS_B CHR,
1285 OKL_K_HEADERS KHR,
1286 OKC_LINE_STYLES_V LSE,
1287 OKC_K_HEADERS_B VPA,
1288 FA_RETIREMENTS RET,
1289 OKX_ASSET_LINES_V OAL
1290 WHERE CLE.id = KLE.id
1291 AND KHR.id = CHR.id
1292 AND KHR.id = CLE.chr_id
1293 AND KHR.khr_id IS NOT NULL
1294 AND KHR.khr_id = VPA.id -- contract has vendor program attached
1295 AND CLE.lse_id = LSE.id
1296 AND LSE.lty_code = 'FREE_FORM1' -- pick only financial assets
1297 AND CLE.sts_code IN ('TERMINATED','EXPIRED') -- Asset should have been TERMINATED/EXPIRED
1298 AND CLE.date_terminated <= TRUNC(p_date) -- Ended
1299
1300 AND OAL.parent_line_id = CLE.id
1301 AND OAL.corporate_book IS NOT NULL
1302 AND OAL.asset_id = RET.asset_id
1303 -- AND RET.status = 'PROCESSED' -- Retirement transaction status is processed -- NOT REQUIRED
1304 AND RET.book_type_code = OAL.corporate_book -- For corporate book
1305
1306 -- Asset is not Securitized is checked once the asset is obtained
1307 AND CLE.id NOT IN (-- residual share already done for that retirement transaction
1308 SELECT TAL.kle_id
1309 FROM OKL_TRX_ASSETS TAS,
1310 OKL_TXL_ASSETS_B TAL
1311 WHERE TAL.kle_id = CLE.id
1312 AND TAL.tas_id = TAS.id
1313 AND TAS.tas_type = 'VRS'
1314 AND TAL.tal_type = 'VRS'
1315 AND TAS.tsu_code = 'PROCESSED'
1316
1317 AND TAL.retirement_id = RET.retirement_id -- do not pick if residual share already done for that retirement trn
1318 )
1319 AND 1 < ( -- has residual sharing with more than one party (if one party then it is Lessor)
1320 SELECT count(rul.rule_information1)
1321 FROM OKC_RULES_V rul,
1322 OKC_RULE_GROUPS_B rgp
1323 WHERE rul.dnz_chr_id = VPA.id
1324 AND rul.rgp_id = rgp.id
1325 AND rgp.rgd_code = 'VGLRS'
1326 AND rul.rule_information_category = 'VGLRSP'
1327 );
1328
1329 CURSOR get_residual_value_stm_id ( p_kle_id IN NUMBER,
1330 p_sty_id IN NUMBER) IS
1331 SELECT STM.id
1332 FROM OKL_STREAMS_V STM,
1333 OKL_STRM_TYPE_B STY
1334 WHERE STM.kle_id = p_kle_id
1335 AND STM.say_code = 'CURR'
1336 AND STM.STY_ID = STY.ID
1337 AND STY.ID = p_sty_id;
1338
1339 l_return_status VARCHAR2(3) := G_RET_STS_SUCCESS;
1340 l_sys_date DATE;
1341 i NUMBER;
1342 l_kle_rec kle_rec_type;
1343 l_share_tbl share_tbl_type;
1344 TYPE kle_tbl_type IS TABLE OF get_expired_kle_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1345 l_kle_tbl kle_tbl_type;
1346 i NUMBER;
1347 l_api_name VARCHAR2(30) := 'do_vendor_share';
1348 l_inv_agmt_chr_id_tbl OKL_SECURITIZATION_PVT.inv_agmt_chr_id_tbl_type;
1349 l_is_securitized VARCHAR2(3) := OKL_API.G_FALSE;
1350 l_residual_sty_id NUMBER;
1351 l_inv_agmt_chr_id NUMBER;
1352 l_residual_stm_id NUMBER;
1353 l_assets_found VARCHAR2(3) := 'N';
1354 BEGIN
1355
1356 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1357 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1358 'OKL_AM_VENDOR_RES_SHARE_PVT.do_write_off_balances.',
1359 'Begin(+)');
1360 END IF;
1361
1362 SELECT sysdate INTO l_sys_date FROM DUAL;
1363
1364 -- For Performance :: Do a bulk fetch of all eligible assets into a pl/sql table
1365 OPEN get_expired_kle_csr (l_sys_date);
1366 FETCH get_expired_kle_csr BULK COLLECT INTO l_kle_tbl;
1367 CLOSE get_expired_kle_csr;
1368
1369 IF l_kle_tbl.COUNT > 0 THEN
1370
1371 -- Loop thru the eligible assets
1372 FOR i IN 1..l_kle_tbl.COUNT LOOP
1373
1374 l_kle_rec.id := l_kle_tbl(i).id;
1375 l_kle_rec.asset_number := l_kle_tbl(i).asset_number;
1376 l_kle_rec.khr_id := l_kle_tbl(i).khr_id;
1377 l_kle_rec.contract_number := l_kle_tbl(i).contract_number;
1378 l_kle_rec.asset_start_date := l_kle_tbl(i).start_date;
1379 l_kle_rec.asset_end_date := l_kle_tbl(i).end_date;
1380 l_kle_rec.date_terminated := l_kle_tbl(i).date_terminated;
1381 l_kle_rec.asset_residual := l_kle_tbl(i).residual_value;
1382 l_kle_rec.vpa_id := l_kle_tbl(i).vpa_id;
1383 l_kle_rec.vpa_number := l_kle_tbl(i).vpa_number;
1384 l_kle_rec.product_id := l_kle_tbl(i).pdt_id;
1385 l_kle_rec.retirement_id := l_kle_tbl(i).retirement_id;
1386 l_kle_rec.date_retired := l_kle_tbl(i).date_retired;
1387 l_kle_rec.scs_code := l_kle_tbl(i).scs_code; -- rmunjulu 4622198
1388
1389 OKL_STREAMS_UTIL.get_primary_stream_type(
1390 l_kle_rec.khr_id,
1391 'RESIDUAL_VALUE',
1392 l_return_status,
1393 l_residual_sty_id);
1394
1395 -- get the residual value stm id
1396 OPEN get_residual_value_stm_id ( l_kle_rec.id,
1397 l_residual_sty_id);
1398 FETCH get_residual_value_stm_id INTO l_residual_stm_id;
1399 CLOSE get_residual_value_stm_id;
1400
1401 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1402 AND l_residual_stm_id IS NOT NULL
1403 AND l_residual_stm_id <> OKL_API.G_MISS_NUM THEN
1404
1405 -- Check KLE RESIDUAL Stream HDR securitized
1406 OKL_SECURITIZATION_PVT.check_stm_securitized(
1407 p_api_version => p_api_version,
1408 p_init_msg_list => OKL_API.G_FALSE,
1409 x_return_status => l_return_status,
1410 x_msg_count => x_msg_count,
1411 x_msg_data => x_msg_data,
1412 p_stm_id => l_residual_stm_id,
1413 p_effective_date => sysdate,
1414 x_value => l_is_securitized);
1415
1416 END IF;
1417
1418
1419 -- DO NOT DO VENDOR SHARE IF RESIDUAL SECURITIZED
1420 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1421 AND nvl(l_is_securitized,OKL_API.G_FALSE)= OKL_API.G_FALSE THEN
1422
1423 l_assets_found := 'Y';
1424
1425 okl_api.init_msg_list(OKL_API.G_TRUE);
1426
1427 -- vendor share
1428 vendor_share(
1429 p_api_version => p_api_version,
1430 p_init_msg_list => OKL_API.G_FALSE,
1431 x_return_status => l_return_status,
1432 x_msg_count => x_msg_count,
1433 x_msg_data => x_msg_data,
1434 p_kle_rec => l_kle_rec,
1435 p_share_tbl => l_share_tbl,
1436 p_sys_date => l_sys_date,
1437 p_control_flag => p_control_flag);
1438
1439 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1440 fnd_output (
1441 p_kle_rec => l_kle_rec,
1442 p_control_flag => 'PROCESSED');
1443 ELSE
1444
1445 fnd_output (
1446 p_kle_rec => l_kle_rec,
1447 p_control_flag => 'ERROR');
1448 END IF;
1449
1450 -- Create the Output Report
1451 --create_report;
1452
1453 END IF;
1454
1455 END LOOP;
1456 create_report;
1457 END IF;
1458
1459 IF l_assets_found = 'N' THEN
1460
1461 FND_FILE.put_line(FND_FILE.output, ' No Assets Found for Vendor Residual Sharing');
1462 FND_FILE.put_line(FND_FILE.log, ' No Assets Found for Vendor Residual Sharing');
1463
1464 END IF;
1465
1466 -- set return status
1467 x_return_status := l_return_status;
1468
1469 -- End Activity
1470 OKL_API.end_activity (x_msg_count, x_msg_data);
1471
1472 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1473 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1474 'OKL_AM_VENDOR_RES_SHARE_PVT.do_vendor_share.',
1475 'End(-)');
1476 END IF;
1477
1478 EXCEPTION
1479
1480 WHEN G_EXCEPTION_ERROR THEN
1481
1482 x_return_status := OKL_API.handle_exceptions(
1483 p_api_name => l_api_name,
1484 p_pkg_name => G_PKG_NAME,
1485 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1486 x_msg_count => x_msg_count,
1487 x_msg_data => x_msg_data,
1488 p_api_type => '_PVT');
1489
1490 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1492 'OKL_AM_VENDOR_RES_SHARE_PVT.do_vendor_share.',
1493 'EXP - G_EXCEPTION_ERROR');
1494 END IF;
1495
1496 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1497
1498 x_return_status := OKL_API.handle_exceptions(
1499 p_api_name => l_api_name,
1500 p_pkg_name => G_PKG_NAME,
1501 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1502 x_msg_count => x_msg_count,
1503 x_msg_data => x_msg_data,
1504 p_api_type => '_PVT');
1505
1506 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1507 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1508 'OKL_AM_VENDOR_RES_SHARE_PVT.do_vendor_share.',
1509 'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
1510 END IF;
1511
1512 WHEN OTHERS THEN
1513
1514 x_return_status := OKL_API.handle_exceptions(
1515 p_api_name => l_api_name,
1516 p_pkg_name => G_PKG_NAME,
1517 p_exc_name => 'OTHERS',
1518 x_msg_count => x_msg_count,
1519 x_msg_data => x_msg_data,
1520 p_api_type => '_PVT');
1521
1522 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1523 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1524 'OKL_AM_VENDOR_RES_SHARE_PVT.do_vendor_share.',
1525 'EXP - OTHERS');
1526 END IF;
1527
1528 END do_vendor_share;
1529
1530 -- Start of comments
1531 --
1532 -- Procedure Name : concurrent_vend_res_share_prg
1533 -- Description :
1534 -- Business Rules :
1535 -- Parameters :
1536 -- Version : 1.0
1537 -- History : RMUNJULU Created
1538 --
1539 -- End of comments
1540 PROCEDURE concurrent_vend_res_share_prg(
1541 errbuf OUT NOCOPY VARCHAR2,
1542 retcode OUT NOCOPY VARCHAR2,
1543 p_api_version IN VARCHAR2,
1544 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1545 p_kle_id IN VARCHAR2 DEFAULT NULL) IS
1546
1547
1548
1549 l_return_status VARCHAR2(3);
1550 l_msg_count NUMBER;
1551 l_msg_data VARCHAR2(2000);
1552 l_api_version NUMBER;
1553
1554 BEGIN
1555
1556 -- Initialize message list
1557 OKL_API.init_msg_list('T');
1558
1559 l_api_version := TO_NUMBER(p_api_version);
1560
1561 -- Do Vendor Share
1562 do_vendor_share(
1563 p_api_version => l_api_version,
1564 p_init_msg_list => p_init_msg_list,
1565 x_return_status => l_return_status,
1566 x_msg_count => l_msg_count,
1567 x_msg_data => l_msg_data,
1568 p_control_flag => 'BATCH_MULTIPLE');
1569
1570 EXCEPTION
1571
1572 WHEN OTHERS THEN
1573 -- Set the oracle error message
1574 OKL_API.set_message(
1575 p_app_name => G_APP_NAME_1,
1576 p_msg_name => G_UNEXPECTED_ERROR,
1577 p_token1 => G_SQLCODE_TOKEN,
1578 p_token1_value => SQLCODE,
1579 p_token2 => G_SQLERRM_TOKEN,
1580 p_token2_value => SQLERRM);
1581
1582 END concurrent_vend_res_share_prg;
1583
1584
1585 -- Start of comments
1586 --
1587 -- Procedure Name : create_report_output
1588 -- Desciption : Creates the Output and Log for REPORT
1589 -- Business Rules :
1590 -- Parameters :
1591 -- Version : 1.0
1592 -- History : RMUNJULU created
1593 --
1594 -- End of comments
1595 PROCEDURE create_report_output (p_values rpt_tbl_type) IS
1596
1597 -- Get the Org Name
1598 CURSOR org_csr (p_org_id IN NUMBER) IS
1599 SELECT HOU.name
1600 FROM HR_OPERATING_UNITS HOU
1601 WHERE HOU.organization_id = p_org_id;
1602
1603
1604 l_org_id NUMBER := MO_GLOBAL.GET_CURRENT_ORG_ID();
1605
1606 l_org_name VARCHAR2(300);
1607 l_orcl_logo VARCHAR2(300);
1608 l_term_heading VARCHAR2(300);
1609 l_set_of_books VARCHAR2(300);
1610 l_set_of_books_name VARCHAR2(300);
1611 l_run_date VARCHAR2(300);
1612 l_oper_unit VARCHAR2(300);
1613 l_k_num VARCHAR2(300);
1614 l_eop VARCHAR2(300);
1615 l_serial VARCHAR2(300);
1616 l_vpa_num VARCHAR2(300);
1617 l_kle_num VARCHAR2(300);
1618 l_num_rows VARCHAR2(300);
1619 l_kle_desc VARCHAR2(300);
1620 l_kle_term_dt VARCHAR2(300);
1621 l_vendor VARCHAR2(300);
1622 l_disp_dt VARCHAR2(300);
1623 l_vend_share VARCHAR2(300);
1624
1625 BEGIN
1626
1627 l_orcl_logo := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_ACCT_LEASE_MANAGEMENT');
1628 l_term_heading := 'OKL Report : Vendor Residual Share Report';
1629 l_set_of_books := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_SET_OF_BOOKS');
1630 l_run_date := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_RUN_DATE');
1631 l_eop := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_END_OF_REPORT');
1632
1633 l_oper_unit := OKL_ACCOUNTING_UTIL.get_message_token('OKL_AM_CONC_OUTPUT','OKL_OPERUNIT');
1634 l_vpa_num := 'Program Agreement';
1635 l_kle_num := 'Asset Number';
1636 l_kle_desc := 'Asset Description';
1637 l_kle_term_dt := 'Asset Term Date';
1638 l_vendor := 'Vendor';
1639 l_disp_dt := 'Disposition Date';
1640 l_vend_share := 'Vendor Share';
1641 l_k_num := 'Contract Number';
1642 l_num_rows := 'Number of Rows';
1643 l_serial := 'Serial #';
1644
1645 l_set_of_books_name := OKL_ACCOUNTING_UTIL.get_set_of_books_name (OKL_ACCOUNTING_UTIL.get_set_of_books_id);
1646
1647 -- Get the Org Name
1648 FOR org_rec IN org_csr (l_org_id) LOOP
1649 l_org_name := org_rec.name;
1650 END LOOP;
1651
1652 --log
1653 FND_FILE.put_line(FND_FILE.log, RPAD('=',77,'=' ));
1654 -- FND_FILE.put_line(FND_FILE.log, l_num_rows);-- ||
1655 -- --count(p_values));
1656
1657 FND_FILE.put_line(FND_FILE.log, RPAD('-',77 ,'-'));
1658
1659
1660 -- output
1661 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_orcl_logo)/2, ' ' ) ||
1662 l_orcl_logo);
1663
1664 FND_FILE.PUT_LINE(FND_FILE.output, RPAD(' ', 128/2-LENGTH(l_term_heading)/2, ' ' ) ||
1665 l_term_heading);
1666
1667 FND_FILE.put_line(FND_FILE.output, RPAD(' ',128/2-LENGTH(l_term_heading)/2 , ' ' ) ||
1668 RPAD('-',LENGTH(l_term_heading),'-'));
1669
1670 FND_FILE.put_line(FND_FILE.output, '');
1671
1672 FND_FILE.put_line(FND_FILE.output, l_set_of_books ||' : '||
1673 l_set_of_books_name ||
1674 RPAD(' ', 128-LENGTH(l_set_of_books)-LENGTH(l_set_of_books_name)-LENGTH(l_run_date)-25, ' ' ) ||
1675 l_run_date ||' : ' ||
1676 TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
1677
1678 FND_FILE.put_line(FND_FILE.output, l_oper_unit || ' : ' ||
1679 l_org_name ||
1680 RPAD(' ', 128-LENGTH(l_oper_unit)-LENGTH(l_org_name), ' ' ));
1681
1682 FND_FILE.put_line(FND_FILE.output,'');
1683 FND_FILE.put_line(FND_FILE.output,'');
1684
1685 FND_FILE.put_line(FND_FILE.output,'');
1686 FND_FILE.put_line(FND_FILE.output, RPAD('=',250,'=' ));
1687 FND_FILE.put_line(FND_FILE.output,'');
1688
1689
1690 FND_FILE.put_line(FND_FILE.output, l_serial || RPAD(' ',15-LENGTH(l_serial),' ')||
1691
1692 l_vpa_num || RPAD(' ',35-LENGTH(l_vpa_num),' ')||
1693
1694 l_kle_num || RPAD(' ',35-LENGTH(l_kle_num),' ')||
1695
1696 l_kle_desc || RPAD(' ',35-LENGTH(l_kle_desc),' ')||
1697
1698 l_k_num || RPAD(' ',35-LENGTH(l_k_num),' ')||
1699
1700 l_kle_term_dt||RPAD(' ',20-LENGTH(l_kle_term_dt),' ') ||
1701
1702 l_vendor||RPAD(' ',35-LENGTH(l_vendor),' ') ||
1703
1704 l_disp_dt||RPAD(' ',20-LENGTH(l_disp_dt),' ') ||
1705
1706 l_vend_share||RPAD(' ',15-LENGTH(l_vend_share),' '));
1707
1708 FND_FILE.put_line(FND_FILE.output, RPAD('-',250,'-' ));
1709
1710 FOR i IN p_values.FIRST..p_values.LAST LOOP
1711
1712 FND_FILE.put_line(FND_FILE.output, i || RPAD(' ',15-LENGTH(i),' ')||
1713
1714 p_values(i).PROGRAM_AGREEMENT ||
1715 RPAD(' ',35-LENGTH(p_values(i).PROGRAM_AGREEMENT),' ')||
1716
1717 p_values(i).ASSET_NUMBER ||
1718 RPAD(' ',35-LENGTH(p_values(i).ASSET_NUMBER),' ')||
1719
1720 p_values(i).ASSET_DESCRIPTION ||
1721 RPAD(' ',35-LENGTH(p_values(i).ASSET_DESCRIPTION),' ')||
1722
1723 p_values(i).CONTRACT_NUMBER||
1724 RPAD(' ',35-LENGTH(p_values(i).CONTRACT_NUMBER),' ') ||
1725
1726 p_values(i).ASSET_TERMINATION_DATE||
1727 RPAD(' ',20-LENGTH(p_values(i).ASSET_TERMINATION_DATE),' ') ||
1728
1729 p_values(i).VENDOR||
1730 RPAD(' ',35-LENGTH(p_values(i).VENDOR),' ') ||
1731
1732 p_values(i).DISPOSITION_DATE||
1733 RPAD(' ',20-LENGTH(p_values(i).DISPOSITION_DATE),' ') ||
1734
1735 p_values(i).VENDOR_SHARE||
1736 RPAD(' ',15-LENGTH(p_values(i).VENDOR_SHARE),' '));
1737
1738 END LOOP;
1739
1740 FND_FILE.put_line(FND_FILE.output,'');
1741 FND_FILE.put_line(FND_FILE.output,'');
1742 FND_FILE.put_line(FND_FILE.output, RPAD(' ', 53 , ' ' ) || l_eop);
1743
1744
1745 EXCEPTION
1746
1747 WHEN OTHERS THEN
1748 -- Set the oracle error message
1749 OKL_API.set_message(
1750 p_app_name => G_APP_NAME_1,
1751 p_msg_name => G_UNEXPECTED_ERROR,
1752 p_token1 => G_SQLCODE_TOKEN,
1753 p_token1_value => SQLCODE,
1754 p_token2 => G_SQLERRM_TOKEN,
1755 p_token2_value => SQLERRM);
1756
1757 END create_report_output;
1758
1759 -- Start of comments
1760 --
1761 -- Procedure Name : concurrent_vend_res_share_rpt
1762 -- Description : Vendor Residual Share report
1763 -- Business Rules :
1764 -- Parameters :
1765 -- Version : 1.0
1766 -- History : RMUNJULU Created
1767 --
1768 -- End of comments
1769 PROCEDURE concurrent_vend_res_share_rpt(
1770 errbuf OUT NOCOPY VARCHAR2,
1771 retcode OUT NOCOPY VARCHAR2,
1772 p_api_version IN VARCHAR2,
1773 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1774 p_asset_number IN VARCHAR2 DEFAULT NULL,
1775 p_disp_date_from IN VARCHAR2 DEFAULT NULL,
1776 p_disp_date_to IN VARCHAR2 DEFAULT NULL,
1777 p_vpa_number IN VARCHAR2 DEFAULT NULL,
1778 p_asst_end_dt_from IN VARCHAR2 DEFAULT NULL,
1779 p_asst_end_dt_to IN VARCHAR2 DEFAULT NULL,
1780 p_currency IN VARCHAR2 DEFAULT NULL) IS
1781
1782 CURSOR get_report_details_csr IS
1783 SELECT VPA.contract_number PROGRAM_AGREEMENT,
1784 CLE.name ASSET_NUMBER,
1785 CLE.item_description ASSET_DESCRIPTION,
1786 CHR.contract_number CONTRACT_NUMBER,
1787 CLE.date_terminated ASSET_TERMINATION_DATE,
1788 PPD.vendor_name VENDOR,
1789 TAL.fa_trx_date DISPOSITION_DATE,
1790 --VPA.contract_number ||' '|| CHR.currency_code VENDOR_SHARE
1791 TAL.residual_shr_amount ||' '|| CHR.currency_code VENDOR_SHARE
1792 FROM OKC_K_LINES_V CLE,
1793 OKC_K_HEADERS_B CHR,
1794 OKC_K_HEADERS_B VPA,
1795 OKL_K_HEADERS KHR,
1796 OKL_TRX_ASSETS TAS,
1797 OKL_TXL_ASSETS_B TAL,
1798 OKL_PARTY_PAYMENT_DTLS_UV PPD
1799 WHERE TAL.TAS_ID = TAS.ID
1800 AND TAS.TAS_TYPE = 'VRS'
1801 AND TAL.TAL_TYPE = 'VRS'
1802 AND TAL.KLE_ID = CLE.ID
1803 AND CLE.CHR_ID = CHR.ID
1804 AND CHR.ID = KHR.ID
1805 AND KHR.KHR_ID = VPA.ID
1806 AND PPD.CPL_ID = TAL.RESIDUAL_SHR_PARTY_ID;
1807
1808 l_return_status VARCHAR2(3);
1809 l_msg_count NUMBER;
1810 l_msg_data VARCHAR2(2000);
1811 l_api_version NUMBER;
1812
1813 /*
1814 -- Search based on these fields:
1815 a) Program Agreement Number
1816 b) Asset Number
1817 c) Asset Termination Date - From / To
1818 d) Asset Disposition Date - From / To
1819 e) Currency
1820 -- Display these fields:
1821 program agreement number
1822 a) Asset Number
1823 b) Asset Description
1824 c) Contract Number
1825 d) Asset Termination Date
1826 e) Vendor
1827 f) Disposition Date
1828 g) Vendor Share (of Profit / Loss)
1829 */
1830
1831 l_main_sql VARCHAR2(3000);
1832 l_select_sql_1 VARCHAR2(3000);
1833 l_select_sql_2 VARCHAR2(3000);
1834 l_from_sql_1 VARCHAR2(3000);
1835 l_where_sql_1 VARCHAR2(3000);
1836 l_where_sql_2 VARCHAR2(3000);
1837 l_condition_1 VARCHAR2(300);
1838 l_condition_2 VARCHAR2(300);
1839 l_condition_3 VARCHAR2(300);
1840 l_condition_4 VARCHAR2(300);
1841 l_condition_5 VARCHAR2(300);
1842 l_condition_6 VARCHAR2(300);
1843 l_condition_7 VARCHAR2(300);
1844 l_final VARCHAR2(3);
1845 l_total_sql VARCHAR2(4000);
1846 l_result_sql get_report_details_csr%ROWTYPE;
1847 TYPE CurTyp IS REF CURSOR;
1848 dynamic_cursor CurTyp;
1849 l_rpt_tbl rpt_tbl_type;
1850 i NUMBER;
1851
1852 lp_disp_date_from DATE;
1853 lp_disp_date_to DATE;
1854 lp_asst_end_dt_from DATE;
1855 lp_asst_end_dt_to DATE;
1856
1857 BEGIN
1858
1859 -- select ' a '|| '||' || ' b ' from dual;
1860 -- select ' a '|| '''vrs''' || ' b ' from dual;
1861 -- bad -----> EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno);
1862 -- good ----> EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno;
1863
1864 -- Initialize message list
1865 OKL_API.init_msg_list('T');
1866
1867 l_api_version := TO_NUMBER(p_api_version);
1868
1869 l_select_sql_1 := ' SELECT VPA.contract_number PROGRAM_AGREEMENT,CLE.name ASSET_NUMBER,CLE.item_description ASSET_DESCRIPTION,CHR.contract_number CONTRACT_NUMBER, ';
1870 l_select_sql_2 := ' CLE.date_terminated ASSET_TERMINATION_DATE,PPD.vendor_name VENDOR, TAL.fa_trx_date, TAL.residual_shr_amount ||' || ''' ''' || '|| TAL.currency_code VENDOR_SHARE';
1871 l_from_sql_1 := ' FROM OKC_K_LINES_V CLE,OKC_K_HEADERS_B CHR,OKC_K_HEADERS_B VPA,OKL_K_HEADERS KHR,OKL_TRX_ASSETS TAS,OKL_TXL_ASSETS_B TAL,OKL_PARTY_PAYMENT_DTLS_UV PPD';
1872 l_where_sql_1 := ' WHERE TAL.TAS_ID = TAS.ID AND TAS.TAS_TYPE = '||'''VRS'''||' AND TAL.TAL_TYPE = '||'''VRS'''||' ';
1873 l_where_sql_2 := ' AND TAL.KLE_ID = CLE.ID AND CLE.CHR_ID = CHR.ID AND CHR.ID = KHR.ID AND KHR.KHR_ID = VPA.ID AND PPD.CPL_ID = TAL.RESIDUAL_SHR_PARTY_ID ';
1874
1875 l_total_sql := l_select_sql_1 || l_select_sql_2 ||
1876 l_from_sql_1 ||
1877 l_where_sql_1 || l_where_sql_2;
1878
1879 IF p_asset_number IS NOT NULL THEN
1880 l_condition_1 := ' AND CLE.name like '''||p_asset_number||'%''';
1881 l_total_sql := l_total_sql || l_condition_1;
1882 END IF;
1883
1884 IF p_disp_date_from IS NOT NULL THEN
1885 lp_disp_date_from := FND_DATE.CANONICAL_TO_DATE(p_disp_date_from);
1886 l_condition_2 := ' AND trunc(TAL.fa_trx_date) >= to_date('''||lp_disp_date_from||''',''DD-MON-YY'')';
1887 l_total_sql := l_total_sql || l_condition_2;
1888 END IF;
1889
1890 IF p_disp_date_to IS NOT NULL THEN
1891 lp_disp_date_to := FND_DATE.CANONICAL_TO_DATE(p_disp_date_to);
1892 l_condition_3 := ' AND trunc(TAL.fa_trx_date) <= to_date('''||lp_disp_date_to||''',''DD-MON-YY'')';
1893 l_total_sql := l_total_sql || l_condition_3;
1894 END IF;
1895
1896 IF p_vpa_number IS NOT NULL THEN
1897 l_condition_4 := ' AND VPA.contract_number like '''||p_vpa_number||'%''';
1898 l_total_sql := l_total_sql || l_condition_4;
1899 END IF;
1900
1901 IF p_asst_end_dt_from IS NOT NULL THEN
1902 lp_asst_end_dt_from := FND_DATE.CANONICAL_TO_DATE(p_asst_end_dt_from);
1903 l_condition_5 := ' AND trunc(CLE.date_terminated) >= to_date('''||lp_asst_end_dt_from||''',''DD-MON-YY'')';
1904 l_total_sql := l_total_sql || l_condition_5;
1905 END IF;
1906
1907 IF p_asst_end_dt_to IS NOT NULL THEN
1908 lp_asst_end_dt_to := FND_DATE.CANONICAL_TO_DATE(p_asst_end_dt_to);
1909 l_condition_6 := ' AND trunc(CLE.date_terminated) <= to_date('''||lp_asst_end_dt_to||''',''DD-MON-YY'')';
1910 l_total_sql := l_total_sql || l_condition_6;
1911 END IF;
1912
1913 IF p_currency IS NOT NULL THEN
1914 l_condition_7 := ' AND TAL.currency_code like '''||p_currency||'%''';
1915 l_total_sql := l_total_sql || l_condition_7;
1916 END IF;
1917
1918 FND_FILE.put_line(FND_FILE.log, 'l_total_sql - '||l_total_sql);
1919
1920 i := 1;
1921 OPEN dynamic_cursor FOR l_total_sql;
1922 FETCH dynamic_cursor INTO l_result_sql;
1923 LOOP
1924
1925 EXIT WHEN dynamic_cursor%NOTFOUND;
1926
1927 FND_FILE.put_line(FND_FILE.log, 'DYNAMIC SQL EXECUTING -- ROWS FOUND '||i);
1928
1929 l_rpt_tbl(i).PROGRAM_AGREEMENT := l_result_sql.PROGRAM_AGREEMENT;
1930 l_rpt_tbl(i).ASSET_NUMBER := l_result_sql.ASSET_NUMBER;
1931 l_rpt_tbl(i).ASSET_DESCRIPTION := l_result_sql.ASSET_DESCRIPTION;
1932 l_rpt_tbl(i).CONTRACT_NUMBER := l_result_sql.CONTRACT_NUMBER;
1933 l_rpt_tbl(i).ASSET_TERMINATION_DATE := l_result_sql.ASSET_TERMINATION_DATE;
1934 l_rpt_tbl(i).VENDOR := l_result_sql.VENDOR;
1935 l_rpt_tbl(i).DISPOSITION_DATE := l_result_sql.DISPOSITION_DATE;
1936 l_rpt_tbl(i).VENDOR_SHARE := l_result_sql.VENDOR_SHARE;
1937
1938 i := i + 1;
1939
1940 FETCH dynamic_cursor INTO l_result_sql;
1941 END LOOP;
1942 CLOSE dynamic_cursor;
1943
1944 create_report_output(p_values => l_rpt_tbl);
1945
1946 EXCEPTION
1947
1948 WHEN OTHERS THEN
1949 -- Set the oracle error message
1950 OKL_API.set_message(
1951 p_app_name => G_APP_NAME_1,
1952 p_msg_name => G_UNEXPECTED_ERROR,
1953 p_token1 => G_SQLCODE_TOKEN,
1954 p_token1_value => SQLCODE,
1955 p_token2 => G_SQLERRM_TOKEN,
1956 p_token2_value => SQLERRM);
1957
1958 FND_FILE.put_line(FND_FILE.log, 'Error - '||SQLERRM);
1959
1960 END concurrent_vend_res_share_rpt;
1961
1962 -----------------------------------------------------------------------------------
1963 -- FUNCTION BEFORE_REPORT_INIT_WHRE_CLAUSE
1964 -----------------------------------------------------------------------------------
1965 -----------------------------------------------------------------------------------
1966 -- Start of comments
1967 --
1968 -- Function Name : BEFORE_REPORT_INIT_WHRE_CLAUSE
1969 -- Description : Function to form the where clause for XML Publisher
1970 -- based on the input parameters.
1971 -- Business Rules :
1972 -- Parameters :
1973 -- Version : 1.0
1974 -- History : UDHENUKO created.
1975 -- End of comments
1976 ------------------------------------------------------------------------------------
1977
1978 FUNCTION BEFORE_REPORT_INIT_WHRE_CLAUSE RETURN BOOLEAN
1979
1980 IS
1981 l_where_sql_1 VARCHAR2(3000);
1982 l_where_sql_2 VARCHAR2(3000);
1983 l_condition_1 VARCHAR2(300);
1984 l_condition_2 VARCHAR2(300);
1985 l_condition_3 VARCHAR2(300);
1986 l_condition_4 VARCHAR2(300);
1987 l_condition_5 VARCHAR2(300);
1988 l_condition_6 VARCHAR2(300);
1989 l_condition_7 VARCHAR2(300);
1990 lp_disp_date_from DATE;
1991 lp_disp_date_to DATE;
1992 lp_asst_end_dt_from DATE;
1993 lp_asst_end_dt_to DATE;
1994 BEGIN
1995
1996 -- Forming the where clause condition for the XML publisher report.
1997 -- Step 1 : Form the static part of the where clause that is independent of the input parameters.
1998 -- Step 2 : Concatenate the where clause from Step1 with the conditions based on the input parameters.
1999 l_where_sql_1 := ' WHERE TAL.TAS_ID = TAS.ID AND TAS.TAS_TYPE = '||'''VRS'''||' AND TAL.TAL_TYPE = '||'''VRS'''||' ';
2000 l_where_sql_2 := ' AND TAL.KLE_ID = CLE.ID AND CLE.CHR_ID = CHR.ID AND CHR.ID = KHR.ID AND KHR.KHR_ID = VPA.ID AND PPD.CPL_ID = TAL.RESIDUAL_SHR_PARTY_ID ';
2001
2002 WHERE_CLAUSE := l_where_sql_1 || l_where_sql_2;
2003
2004 IF P_ASSET_NUMBER IS NOT NULL THEN
2005 l_condition_1 := ' AND CLE.name like '''||P_ASSET_NUMBER||'%''';
2006 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_1;
2007 END IF;
2008
2009 IF P_DISP_DATE_FROM IS NOT NULL THEN
2010 lp_disp_date_from := FND_DATE.CANONICAL_TO_DATE(P_DISP_DATE_FROM);
2011 l_condition_2 := ' AND trunc(TAL.fa_trx_date) >= to_date('''||lp_disp_date_from||''',''DD-MON-YY'')';
2012 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_2;
2013 END IF;
2014
2015 IF P_DISP_DATE_TO IS NOT NULL THEN
2016 lp_disp_date_to := FND_DATE.CANONICAL_TO_DATE(P_DISP_DATE_TO);
2017 l_condition_3 := ' AND trunc(TAL.fa_trx_date) <= to_date('''||lp_disp_date_to||''',''DD-MON-YY'')';
2018 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_3;
2019 END IF;
2020
2021 IF P_VPA_NUMBER IS NOT NULL THEN
2022 l_condition_4 := ' AND VPA.contract_number like '''||P_VPA_NUMBER||'%''';
2023 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_4;
2024 END IF;
2025
2026 IF P_ASST_END_DT_FROM IS NOT NULL THEN
2027 lp_asst_end_dt_from := FND_DATE.CANONICAL_TO_DATE(P_ASST_END_DT_FROM);
2028 l_condition_5 := ' AND trunc(CLE.date_terminated) >= to_date('''||lp_asst_end_dt_from||''',''DD-MON-YY'')';
2029 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_5;
2030 END IF;
2031
2032 IF P_ASST_END_DT_TO IS NOT NULL THEN
2033 lp_asst_end_dt_to := FND_DATE.CANONICAL_TO_DATE(P_ASST_END_DT_TO);
2034 l_condition_6 := ' AND trunc(CLE.date_terminated) <= to_date('''||lp_asst_end_dt_to||''',''DD-MON-YY'')';
2035 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_6;
2036 END IF;
2037
2038 IF P_CURRENCY IS NOT NULL THEN
2039 l_condition_7 := ' AND TAL.currency_code like '''||P_CURRENCY||'%''';
2040 WHERE_CLAUSE := WHERE_CLAUSE || l_condition_7;
2041 END IF;
2042
2043
2044 RETURN TRUE;
2045
2046 END BEFORE_REPORT_INIT_WHRE_CLAUSE;
2047
2048 END OKL_AM_VENDOR_RES_SHARE_PVT;