[Home] [Help]
PACKAGE BODY: APPS.OKL_POOLCONC_PVT
Source
1 PACKAGE BODY Okl_Poolconc_Pvt AS
2 /* $Header: OKLRSZCB.pls 120.17 2008/01/04 08:48:18 dpsingh noship $ */
3 ----------------------------------------------------------------------------
4 -- Global Message Constants
5 ----------------------------------------------------------------------------
6
7 G_POOL_NO_MODIFY CONSTANT VARCHAR2(18) := 'OKL_POOL_NO_MODIFY';
8 G_POC_STS_NEW CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_NEW;
9 G_POC_STS_ACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_ACTIVE;
10 G_POC_STS_INACTIVE CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_INACTIVE;
11 G_POC_STS_PENDING CONSTANT VARCHAR2(10) := Okl_Pool_Pvt.G_POC_STS_PENDING;
12 G_POOL_ADD_REPORT CONSTANT VARCHAR2(30) := 'OKL_POOL_ADD_REPORT';
13 G_POOL_ELIGIBILITY_CRITERIA CONSTANT VARCHAR2(30) := 'OKL_POOL_ELIGIBILITY_CRITERIA';
14 G_POOL_ADD_TBL_HDR CONSTANT VARCHAR2(30) := 'OKL_POOL_ADD_TBL_HDR';
15 G_REJECT_REASON_CODE CONSTANT VARCHAR2(25) := 'OKL_REJECT_REASON_CODE';
16 G_REJECT_REASON_CODES CONSTANT VARCHAR2(25) := 'OKL_REJECT_REASON_CODES';
17
18 ----------------------------------------------------------------------------
19 -- Procedures and Functions
20 ----------------------------------------------------------------------------
21 PROCEDURE get_error_message(p_all_message OUT nocopy error_message_type)
22 IS
23 l_msg_text VARCHAR2(2000);
24 l_msg_count NUMBER ;
25 BEGIN
26 l_msg_count := fnd_msg_pub.count_msg;
27 FOR i IN 1..l_msg_count
28 LOOP
29 fnd_msg_pub.get
30 (p_data => p_all_message(i),
31 p_msg_index_out => l_msg_count,
32 p_encoded => fnd_api.g_false,
33 p_msg_index => fnd_msg_pub.g_next
34 );
35 END LOOP;
36 EXCEPTION
37 WHEN OTHERS THEN
38 NULL;
39 END get_error_message;
40
41 -- mvasudev
42 FUNCTION get_message(p_current_api IN VARCHAR2
43 ,p_called_api IN VARCHAR2
44 ,p_msg_token IN VARCHAR2)
45 RETURN VARCHAR2
46 IS
47
48 BEGIN
49 RETURN (G_PKG_NAME || '.' || p_current_api || '::' ||
50 p_called_api || ':: ' ||
51 FND_MESSAGE.GET_STRING(G_APP_NAME,p_msg_token));
52
53 END get_message;
54
55
56 ----------------------------------------------------------------------------------
57 -- Start of comments
58 --
59 -- Procedure Name : add_pool_contents
60 -- Description : creates pool contents based on passed in search criteria
61 -- This is a wrapper procedure for concurrent program to call private API
62 -- Business Rules :
63 -- Parameters :
64 -- Version : 1.0
65 -- End of comments
66 ----------------------------------------------------------------------------------
67 -- Create by Search Criteria: Query Streams from contracts + Create
68
69 PROCEDURE add_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
70 ,x_retcode OUT NOCOPY NUMBER
71 ,p_pol_id IN VARCHAR2
72 ,p_currency_code IN VARCHAR2
73 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
74 ,p_cust_object1_id1 IN VARCHAR2 DEFAULT NULL -- customer_id
75 ,p_sic_code IN VARCHAR2 DEFAULT NULL
76 ,p_dnz_chr_id IN VARCHAR2 DEFAULT NULL -- dnz_chr_id
77 ,p_pre_tax_yield_from IN VARCHAR2 DEFAULT NULL
78 ,p_pre_tax_yield_to IN VARCHAR2 DEFAULT NULL
79 ,p_book_classification IN VARCHAR2 DEFAULT NULL
80 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
81 ,p_pdt_id IN VARCHAR2 DEFAULT NULL
82 ,p_start_from_date IN VARCHAR2 DEFAULT NULL
83 ,p_start_to_date IN VARCHAR2 DEFAULT NULL
84 ,p_end_from_date IN VARCHAR2 DEFAULT NULL
85 ,p_end_to_date IN VARCHAR2 DEFAULT NULL
86 ,p_asset_id IN VARCHAR2 DEFAULT NULL
87 ,p_item_id1 IN VARCHAR2 DEFAULT NULL
88 ,p_model_number IN VARCHAR2 DEFAULT NULL
89 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
90 ,p_vendor_id1 IN VARCHAR2 DEFAULT NULL
91 ,p_oec_from IN VARCHAR2 DEFAULT NULL
92 ,p_oec_to IN VARCHAR2 DEFAULT NULL
93 ,p_residual_percentage IN VARCHAR2 DEFAULT NULL
94 ,p_sty_id1 IN VARCHAR2 DEFAULT NULL
95 ,p_sty_id2 IN VARCHAR2 DEFAULT NULL
96 ,p_streams_from_date IN VARCHAR2 DEFAULT NULL
97 ,p_streams_to_date IN VARCHAR2 DEFAULT NULL
98 ,p_stream_element_payment_freq IN VARCHAR2 DEFAULT NULL
99 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
100 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL)
101 IS
102
103 -- Bug#2829983, v115.13
104 CURSOR l_okl_pol_status_csr(p_pol_id IN NUMBER)
105 IS
106 SELECT status_code
107 FROM okl_pools
108 WHERE id = p_pol_id;
109
110 -- /* Following Cursors for Report purposes */
111 CURSOR l_okl_pol_csr IS
112 SELECT polv.pool_number pool_number
113 ,psts.meaning pool_status
114 FROM OKL_POOLS polv -- to take care of org_id
115 ,okc_statuses_tl psts
116 WHERE polv.id = p_pol_id
117 AND polv.status_code = psts.code
118 AND psts.LANGUAGE = USERENV('LANG');
119
120 CURSOR l_okl_set_of_books_csr
121 IS
122 SELECT okls.set_of_books_id set_of_books_id
123 ,glsb.name set_of_books_name
124 FROM GL_LEDGERS_PUBLIC_V glsb
125 ,OKL_SYS_ACCT_OPTS okls
126 WHERE glsb.ledger_id = okls.set_of_books_id;
127
128 CURSOR l_okl_operating_unit_csr
129 IS
130 SELECT name
131 FROM hr_operating_units
132 WHERE organization_id = mo_global.get_current_org_id();
133
134 CURSOR l_okl_customer_csr
135 IS
136 SELECT name
137 FROM OKX_PARTIES_V
138 WHERE id1 = p_cust_object1_id1;
139
140 CURSOR l_okl_sic_csr
141 IS
142 SELECT name
143 FROM OKL_POOL_CUST_INDUSTRY_UV
144 WHERE code = p_sic_code;
145
146 CURSOR l_okl_chr_csr
147 IS
148 SELECT contract_number
149 FROM OKC_K_HEADERS_V
150 WHERE id = p_dnz_chr_id;
151
152 CURSOR l_okl_book_class_csr
153 IS
154 SELECT meaning
155 FROM FND_LOOKUPS
156 WHERE lookup_type='OKL_BOOK_CLASS'
157 AND NVL(start_date_active,SYSDATE) <=SYSDATE
158 AND NVL(end_date_active,SYSDATE+1) > SYSDATE
159 AND enabled_flag = 'Y'
160 AND lookup_code = p_book_classification;
161
162 CURSOR l_okl_tax_owner_csr
163 IS
164 SELECT meaning
165 FROM FND_LOOKUPS
166 WHERE lookup_type='OKL_TAX_OWNER'
167 AND NVL(start_date_active,SYSDATE) <=SYSDATE
168 AND NVL(end_date_active,SYSDATE+1) > SYSDATE
169 AND enabled_flag = 'Y'
170 AND lookup_code = p_tax_owner;
171
172 CURSOR l_okl_pdt_csr
173 IS
174 SELECT name
175 FROM OKL_PRODUCTS
176 WHERE id = p_pdt_id;
177
178 CURSOR l_okl_sty_subclass_csr
179 IS
180 SELECT meaning
181 FROM fnd_lookups
182 WHERE lookup_type = 'OKL_STREAM_TYPE_SUBCLASS'
183 AND lookup_code = p_stream_type_subclass;
184
185 --Added ssdeshpa
186 CURSOR l_okl_cust_crdt_clsf_csr
187 IS
188 SELECT MEANING AS CREDITCXLSSFCTN
189 FROM AR_LOOKUPS fndlup
190 WHERE LOOKUP_TYPE = 'AR_CMGT_CREDIT_CLASSIFICATION'
191 AND LOOKUP_CODE = p_cust_crd_clf_code
192 AND ENABLED_FLAG = 'Y'
193 AND SYSDATE BETWEEN NVL (fndlup.start_date_active,sysdate)
194 AND NVL(fndlup.end_date_active,sysdate);
195
196 l_cust_crd_clf VARCHAR2(80);
197
198 /*
199 CURSOR l_okl_reject_codes_csr
200 IS
201 SELECT lookup_code,
202 meaning
203 FROM fnd_lookups
204 WHERE LOOKUP_TYPE LIKE 'OKL_POOL_REJECT_REASON'
205 ORDER BY LOOKUP_CODE;
206 */
207
208 l_api_name CONSTANT VARCHAR2(40) := 'add_pool_contents';
209 l_api_version CONSTANT NUMBER := 1.0;
210 p_api_version CONSTANT NUMBER := 1.0;
211 l_init_msg_list VARCHAR2(1) := 'T';
212 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
213 l_msg_count NUMBER;
214 l_row_count NUMBER;
215 l_amount NUMBER;
216
217 l_msg_data VARCHAR2(2000);
218 l_RecordsProcessed NUMBER := 0;
219 l_error_msg_rec Error_message_Type;
220
221 -- report related fields
222 l_row_num_len NUMBER := 6;
223 l_contract_num_len NUMBER := 30;
224 l_asset_num_len NUMBER := 15;
225 l_lessee_len NUMBER := 40;
226 l_sty_subclass_len NUMBER := 25;
227 l_reject_code_len NUMBER := 20;
228 l_max_len NUMBER := 150;
229 l_prompt_len NUMBER := 35;
230
231
232 l_str_row_num VARCHAR2(5);
233 l_str_contract_num VARCHAR2(30);
234 l_str_lessee VARCHAR2(50);
235 l_content VARCHAR2(1000);
236 l_header_len NUMBER;
237
238 -- Search Parameters
239 l_customer VARCHAR2(360);
240 l_customer_industry VARCHAR2(80);
241 l_contract_number VARCHAR2(120);
242 l_book_class VARCHAR2(80);
243 l_tax_owner VARCHAR2(80);
244 l_product VARCHAR2(150);
245 l_stream_type_subclass VARCHAR2(150);
246
247 l_filler VARCHAR2(5) := RPAD(' ',5,' ');
248
249 BEGIN
250
251 x_retcode := 0;
252 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
253 p_pkg_name => G_PKG_NAME,
254 p_init_msg_list => l_init_msg_list,
255 l_api_version => l_api_version,
256 p_api_version => p_api_version,
257 p_api_type => G_API_TYPE,
258 x_return_status => l_return_status);
259
260 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
261 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
262 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
263 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
264 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
265 RAISE G_EXCEPTION_ERROR;
266 END IF;
267
268 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
269 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
270
271 -- Printing the values in the log file.
272
273 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
274 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_currency_code : ' || p_currency_code);
275 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pol_id : ' || p_pol_id);
276 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_multi_org : ' || p_multi_org);
277
278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cust_object1_id1 : ' || p_cust_object1_id1);
279
280 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_sic_code : ' || p_sic_code);
281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_khr_id : ' || p_dnz_chr_id);
282
283 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pre_tax_yield_from : ' || p_pre_tax_yield_from);
284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pre_tax_yield_to : ' || p_pre_tax_yield_to);
285 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_book_classification : ' || p_book_classification);
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_tax_owner : ' || p_tax_owner);
287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pdt_id : ' || p_pdt_id);
288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_date_from : ' || p_start_from_date);
289 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_date_to : ' || p_start_to_date);
290 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date_from : ' || p_end_from_date);
291 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date_to : ' || p_end_to_date);
292
293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_asset_id : ' || p_asset_id);
294
295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_item_id1 : ' || p_item_id1);
296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_model_number : ' || p_model_number);
297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_manufacturer_name : ' || p_manufacturer_name);
298 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_vendor_id1 : ' || p_vendor_id1);
299
300 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_oec_from : ' || p_oec_from);
301 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_oec_to : ' || p_oec_to);
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_residual_percentage : ' || p_residual_percentage);
303 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_sty_id1 : ' || p_sty_id1);
304 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_sty_id2 : ' || p_sty_id2);
305 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_stream_element_from_date : ' || p_streams_from_date);
306 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_stream_element_to_date : ' || p_streams_to_date);
307 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_stream_element_payment_freq : ' || p_stream_element_payment_freq);
308 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_stream_type_subclass : ' || p_stream_type_subclass);
309 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cust_crd_clf_code : ' || p_cust_crd_clf_code);
310 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
311
312 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
313
314 -- Bug#2829983, v115.13
315 FOR l_okl_pol_status_rec IN l_okl_pol_status_csr(p_pol_id)
316 LOOP
317 IF l_okl_pol_status_rec.status_code NOT IN (Okl_Pool_Pvt.G_POL_STS_NEW,Okl_Pool_Pvt.G_POL_STS_ACTIVE)
318 THEN
319 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_POOL_NO_MODIFY));
320 RAISE G_EXCEPTION_ERROR;
321 END IF;
322 END LOOP;
323
324 -- Product Title
325 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_OKL_TITLE);
326 l_header_len := LENGTH(l_content);
327 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2); -- center align header
328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
329 l_content := RPAD('-',l_header_len,'-'); -- underline header
330 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2,'='); -- center align
331 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
332 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
333
334 -- Header
335 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_ADD_REPORT);
336 l_header_len := LENGTH(l_content);
337 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2); -- center align header
338 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
339
340 l_content := RPAD('=',l_header_len,'='); -- underline header
341 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2,'='); -- center align
342 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
343
344 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
345 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
346
347 -- Set of Books, Operating Unit
348 FOR l_okl_set_of_books_rec IN l_okl_set_of_books_csr
349 LOOP
350 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_SET_OF_BOOKS),l_prompt_len) || ' : ' || l_okl_set_of_books_rec.set_of_books_name);
351 END LOOP;
352 FOR l_okl_operating_unit_rec IN l_okl_operating_unit_csr
353 LOOP
354 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_OPERATING_UNIT),l_prompt_len) || ' : ' || l_okl_operating_unit_rec.name);
355 END LOOP;
356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_PROGRAM_RUN_DATE),l_prompt_len) || ' : ' || SYSDATE);
357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
358
359 -- Pool Details
360 FOR l_okl_pol_rec IN l_okl_pol_csr
361 LOOP
362 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_NUMBER),l_prompt_len) || ' : ' || l_okl_pol_rec.pool_number);
363 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_STATUS),l_prompt_len) || ' : ' || l_okl_pol_rec.pool_status);
364 END LOOP;
365
366 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
367 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CURRENCY),l_prompt_len) || ' : ' || p_currency_code);
368
369 -- Search Parameters
370 -- sub head
371 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
372 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_ELIGIBILITY_CRITERIA);
373 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
374 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
375
376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
377 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMERS);
378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
379 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
380
381 -- Customer related parameters
382 FOR l_okl_customer_rec IN l_okl_customer_csr
383 LOOP
384 l_customer := l_okl_customer_rec.name;
385 END LOOP;
386 FOR l_okl_sic_rec IN l_okl_sic_csr
387 LOOP
388 l_customer_industry := l_okl_sic_rec.name;
389 END LOOP;
390 --added ssdeshpa
391 FOR l_okl_cust_crdt_clsf_rec IN l_okl_cust_crdt_clsf_csr
392 LOOP
393 l_cust_crd_clf := l_okl_cust_crdt_clsf_rec.CREDITCXLSSFCTN;
394 END LOOP;
395
396 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMER),l_prompt_len) || ' : ' || l_customer);
397 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMER_INDUSTRY_CODE),l_prompt_len) || ' : ' || l_customer_industry);
398
399 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUST_CRDT_CLASSIFICATION),l_prompt_len) || ' : ' || l_cust_crd_clf);
400
401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
402 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACTS);
403 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
404 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
405
406 -- Contract related Parameters
407 FOR l_okl_chr_rec IN l_okl_chr_csr
408 LOOP
409 l_contract_number := l_okl_chr_rec.contract_number;
410 END LOOP;
411 FOR l_okl_book_class_rec IN l_okl_book_class_csr
412 LOOP
413 l_book_class := l_okl_book_class_rec.meaning;
414 END LOOP;
415 FOR l_okl_tax_owner_rec IN l_okl_tax_owner_csr
416 LOOP
417 l_tax_owner := l_okl_tax_owner_rec.meaning;
418 END LOOP;
419 FOR l_okl_pdt_rec IN l_okl_pdt_csr
420 LOOP
421 l_product := l_okl_pdt_rec.name;
422 END LOOP;
423
424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_prompt_len) || ' : ' || l_contract_number);
425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PTY_FROM),l_prompt_len) || ' : ' || p_pre_tax_yield_from);
426 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PTY_TO),l_prompt_len) || ' : ' || p_pre_tax_yield_to);
427 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_BOOK_CLASS),l_prompt_len) || ' : ' || l_book_class);
428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_TAX_OWNER),l_prompt_len) || ' : ' || l_tax_owner);
429 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_START_FROM_DATE),l_prompt_len) || ' : ' || p_start_from_date);
430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_START_TO_DATE),l_prompt_len) || ' : ' || p_start_to_date);
431 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_END_FROM_DATE),l_prompt_len) || ' : ' || p_end_from_date);
432 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_END_TO_DATE),l_prompt_len) || ' : ' || p_end_to_date);
433 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PRODUCT),l_prompt_len) || ' : ' || l_product);
434
435 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
436 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS);
437 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
438 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
439
440 -- Stream related Parameters
441 IF p_stream_type_subclass IS NOT NULL THEN
442 FOR l_okl_sty_subclass_rec IN l_okl_sty_subclass_csr
443 LOOP
444 l_stream_type_subclass := l_okl_sty_subclass_rec.meaning;
445 END LOOP;
446 END IF;
447
448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_SUBCLASS),l_prompt_len) || ' : ' || l_stream_type_subclass);
449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS_FROM_DATE),l_prompt_len) || ' : ' || p_streams_from_date);
450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS_TO_DATE),l_prompt_len) || ' : ' || p_streams_to_date);
451
452 /*
453 -- Note preceding Table Header
454 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
455 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_ADD_TBL_HDR));
456
457 -- Table header
458 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
459 || RPAD('-',l_contract_num_len-1,'-') || ' '
460 || RPAD('-',l_asset_num_len-1,'-') || ' '
461 || RPAD('-',l_lessee_len-1,'-') || ' '
462 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
463 || RPAD('-',l_reject_code_len-1,'-');
464
465 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
466
467 l_content := RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ROW_NUMBER),l_row_num_len-1) || ' '
468 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_contract_num_len-1) || ' '
469 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_NUMBER),l_asset_num_len-1) || ' '
470 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_LESSEE),l_lessee_len-1) || ' '
471 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_SUBCLASS),l_sty_subclass_len-1) || ' '
472 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_REJECT_REASON_CODE),l_reject_code_len-1);
473
474 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
475
476 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
477 || RPAD('-',l_contract_num_len-1,'-') || ' '
478 || RPAD('-',l_asset_num_len-1,'-') || ' '
479 || RPAD('-',l_lessee_len-1,'-') || ' '
480 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
481 || RPAD('-',l_reject_code_len-1,'-');
482
483 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
484
485 */
486 Okl_Pool_Pvt.add_pool_contents(
487 p_api_version => l_api_version
488 ,p_init_msg_list => l_init_msg_list
489 ,x_return_status => l_return_status
490 ,x_msg_count => l_msg_count
491 ,x_msg_data => l_msg_data
492 ,x_row_count => l_row_count
493 ,p_multi_org => p_multi_org
494 ,p_currency_code => p_currency_code
495 ,p_pol_id => TO_NUMBER(p_pol_id)
496 ,p_cust_object1_id1 => TO_NUMBER(p_cust_object1_id1)
497 ,p_sic_code => p_sic_code
498 ,p_khr_id => TO_NUMBER(p_dnz_chr_id)
499 ,p_pre_tax_yield_from => TO_NUMBER(p_pre_tax_yield_from)
500 ,p_pre_tax_yield_to => TO_NUMBER(p_pre_tax_yield_to)
501 ,p_book_classification => p_book_classification
502 ,p_tax_owner => p_tax_owner
503 ,p_pdt_id => TO_NUMBER(p_pdt_id)
504 ,p_start_date_from => fnd_date.canonical_to_date(p_start_from_date)
505 ,p_start_date_to => fnd_date.canonical_to_date(p_start_to_date)
506 ,p_end_date_from => fnd_date.canonical_to_date(p_end_from_date)
507 ,p_end_date_to => fnd_date.canonical_to_date(p_end_to_date)
508 ,p_asset_id => TO_NUMBER(p_asset_id)
509 ,p_item_id1 => TO_NUMBER(p_item_id1)
510 ,p_model_number => p_model_number
511 ,p_manufacturer_name => p_manufacturer_name
512 ,p_vendor_id1 => TO_NUMBER(p_vendor_id1)
513 ,p_oec_from => TO_NUMBER(p_oec_from)
514 ,p_oec_to => TO_NUMBER(p_oec_to)
515 ,p_residual_percentage => TO_NUMBER(p_residual_percentage)
516 ,p_sty_id1 => TO_NUMBER(p_sty_id1)
517 ,p_sty_id2 => TO_NUMBER(p_sty_id2)
518 ,p_stream_element_from_date => fnd_date.canonical_to_date(p_streams_from_date)
519 ,p_stream_element_to_date => fnd_date.canonical_to_date(p_streams_to_date)
520 ,p_stream_element_payment_freq => p_stream_element_payment_freq
521 ,p_stream_type_subclass => p_stream_type_subclass
522 ,p_cust_crd_clf_code => p_cust_crd_clf_code);
523
524
525 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED) || l_row_count);
526
527 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
528 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.ADD_POOL_CONTENTS',G_UNEXPECTED_ERROR));
529 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
530 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
531 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.ADD_POOL_CONTENTS',G_EXPECTED_ERROR));
532 RAISE G_EXCEPTION_ERROR;
533 END IF;
534 /*
535 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
536 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING(G_APP_NAME,G_REJECT_REASON_CODES));
537
538 -- Listing Reason Code Meaning-s
539 FOR l_okl_reject_codes_rec IN l_okl_reject_codes_csr
540 LOOP
541 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_filler || l_okl_reject_codes_rec.lookup_code
542 || ' => '
543 || l_okl_reject_codes_rec.meaning);
544 END LOOP; */
545
546
547 /* v115.16, mvasudev
548 --
549 -- update total principal amount at okl_pools
550 --
551 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message('Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT','',G_PROCESS_START));
552
553 Okl_Pool_Pvt.recal_tot_princ_amt(
554 p_api_version => l_api_version
555 ,p_init_msg_list => l_init_msg_list
556 ,x_return_status => l_return_status
557 ,x_msg_count => l_msg_count
558 ,x_msg_data => l_msg_data
559 ,x_value => l_amount
560 ,p_pol_id => p_pol_id);
561
562
563 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT:: p_pol_id : ' || p_pol_id);
564 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT:: x_value : ' || l_amount);
565
566 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
567 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_UNEXPECTED_ERROR));
568 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
569 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
570 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_EXPECTED_ERROR));
571 RAISE G_EXCEPTION_ERROR;
572 END IF;
573 */
574
575 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
576
577 -- Errors
578 -- sub head
579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
580 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_ERRORS);
581 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
582 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
583 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
584
585 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
586 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
587
588 -- "No Errors"
589 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_NONE);
590 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_filler || l_content);
591
592 x_retcode := 0;
593
594 EXCEPTION
595 WHEN G_EXCEPTION_ERROR THEN
596 x_retcode := 2;
597
598 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
599 p_pkg_name => G_PKG_NAME,
600 p_exc_name => G_EXC_NAME_ERROR,
601 x_msg_count => l_msg_count,
602 x_msg_data => l_msg_data,
603 p_api_type => G_API_TYPE);
604
605 -- print the error message in the log file
606
607 GET_ERROR_MESSAGE(l_error_msg_rec);
608 IF (l_error_msg_rec.COUNT > 0) THEN
609 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
610 LOOP
611 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
612 END LOOP;
613 END IF;
614
615 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
616 x_retcode := 2;
617
618 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
619 p_pkg_name => G_PKG_NAME,
620 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
621 x_msg_count => l_msg_count,
622 x_msg_data => l_msg_data,
623 p_api_type => G_API_TYPE);
624 -- print the error message in the log file
625 GET_ERROR_MESSAGE(l_error_msg_rec);
626 IF (l_error_msg_rec.COUNT > 0) THEN
627 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
628 LOOP
629 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
630 END LOOP;
631 END IF;
632
633 WHEN OTHERS THEN
634 x_errbuf := SQLERRM;
635 x_retcode := 2;
636
637 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
638 p_pkg_name => G_PKG_NAME,
639 p_exc_name => G_EXC_NAME_OTHERS,
640 x_msg_count => l_msg_count,
641 x_msg_data => l_msg_data,
642 p_api_type => G_API_TYPE);
643
644 -- print the error message in the log file
645 GET_ERROR_MESSAGE(l_error_msg_rec);
646 IF (l_error_msg_rec.COUNT > 0) THEN
647 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
648 LOOP
649 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
650 END LOOP;
651 END IF;
652 END add_pool_contents;
653
654 ----------------------------------------------------------------------------------
655 -- Start of comments
656 --
657 -- Procedure Name : add_pool_contents_ui
658 -- Description : creates pool contents based on passed in search criteria
659 -- This is a wrapper procedure for concurrent program call from jsp/UI
660 -- Business Rules :
661 -- Parameters :
662 -- Version : 1.0
663 -- End of comments
664 ----------------------------------------------------------------------------------
665
666 PROCEDURE add_pool_contents_ui(
667 p_api_version IN NUMBER
668 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
669 ,x_return_status OUT nocopy VARCHAR2
670 ,x_msg_count OUT nocopy NUMBER
671 ,x_msg_data OUT nocopy VARCHAR2
672 ,x_request_id OUT nocopy NUMBER
673 ,p_polsrch_rec IN polsrch_rec_type
674 ,p_sty_id1 IN NUMBER DEFAULT NULL
675 ,p_sty_id2 IN NUMBER DEFAULT NULL
676 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
677 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE)
678 AS
679 l_api_name VARCHAR2(30) := 'add_pool_contents_ui';
680 l_api_version NUMBER := 1.0;
681 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
682 l_date VARCHAR2(20) ;
683
684 BEGIN
685
686 x_return_status := G_RET_STS_SUCCESS;
687
688 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
689 p_pkg_name => G_PKG_NAME,
690 p_init_msg_list => p_init_msg_list,
691 l_api_version => l_api_version,
692 p_api_version => p_api_version,
693 p_api_type => G_API_TYPE,
694 x_return_status => l_return_status);
695
696 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
697 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
698 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
699 RAISE G_EXCEPTION_ERROR;
700 END IF;
701
702 -- Bug#2838721, mvasudev, 03/11/2003
703 --call concurrent program
704 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
705
706 x_request_id := Fnd_Request.SUBMIT_REQUEST(
707 application => 'OKL'
708 ,program => 'OKL_ADD_POOL_CONTENTS'
709 ,argument1 => TO_CHAR(p_polsrch_rec.pol_id)
710 ,argument2 => p_polsrch_rec.currency_code
711 ,argument3 => p_multi_org
712 ,argument4 => TO_CHAR(p_polsrch_rec.cust_object1_id1)
713 ,argument5 => p_polsrch_rec.sic_code
714 ,argument6 => TO_CHAR(p_polsrch_rec.dnz_chr_id)
715 ,argument7 => TO_CHAR(p_polsrch_rec.pre_tax_yield_from)
716 ,argument8 => TO_CHAR(p_polsrch_rec.pre_tax_yield_to)
717 ,argument9 => p_polsrch_rec.book_classification
718 ,argument10 => p_polsrch_rec.tax_owner
719 ,argument11 => TO_CHAR(p_polsrch_rec.pdt_id)
720 ,argument12 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.start_from_date)
721 ,argument13 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.start_to_date)
722 ,argument14 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.end_from_date)
723 ,argument15 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.end_to_date)
724 ,argument16 => TO_CHAR(p_polsrch_rec.asset_id)
725 ,argument17 => TO_CHAR(p_polsrch_rec.item_id1)
726 ,argument18 => p_polsrch_rec.model_number
727 ,argument19 => p_polsrch_rec.manufacturer_name
728 ,argument20 => TO_CHAR(p_polsrch_rec.vendor_id1)
729 ,argument21 => TO_CHAR(p_polsrch_rec.oec_from)
730 ,argument22 => TO_CHAR(p_polsrch_rec.oec_to)
731 ,argument23 => TO_CHAR(p_polsrch_rec.residual_percentage)
732 ,argument24 => TO_CHAR(p_sty_id1)
733 ,argument25 => TO_CHAR(p_sty_id2)
734 ,argument26 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.streams_from_date)
735 ,argument27 => FND_DATE.DATE_TO_CANONICAL(p_polsrch_rec.streams_to_date)
736 ,argument28 => p_polsrch_rec.stream_element_payment_freq
737 ,argument29 => p_stream_type_subclass
738 ,argument30 => p_polsrch_rec.cust_crd_clf_code
739 );
740
741 -- Added these validations to check to see if the request has been submitted successfully.
742 IF x_request_id = 0 THEN
743 OKL_API.set_message(p_app_name => G_APP_NAME,
744 p_msg_name => 'OKL_CONC_REQ_ERROR',
745 p_token1 => 'PROG_NAME',
746 p_token1_value => 'OKL ADD Pool CONTENTS',
747 p_token2 => 'REQUEST_ID',
748 p_token2_value => x_request_id);
749
750 RAISE G_EXCEPTION_ERROR;
751 END IF;
752
753 OKL_API.end_activity(x_msg_count, x_msg_data);
754
755 EXCEPTION
756 WHEN G_EXCEPTION_ERROR THEN
757
758 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
759 p_pkg_name => G_PKG_NAME,
760 p_exc_name => G_EXC_NAME_ERROR,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data,
763 p_api_type => G_API_TYPE);
764 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
765
766 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
767 p_pkg_name => G_PKG_NAME,
768 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
769 x_msg_count => x_msg_count,
770 x_msg_data => x_msg_data,
771 p_api_type => G_API_TYPE);
772 WHEN OTHERS THEN
773
774 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
775 p_pkg_name => G_PKG_NAME,
776 p_exc_name => G_EXC_NAME_OTHERS,
777 x_msg_count => x_msg_count,
778 x_msg_data => x_msg_data,
779 p_api_type => G_API_TYPE);
780 END add_pool_contents_ui;
781
782
783 ----------------------------------------------------------------------------------
784 -- Start of comments
785 --
786 -- Procedure Name : recal_tot_princ_amt
787 -- Description : update asset principal amount from pool contents by okl_pools.id to okl_pools.TOTAL_PRINCIPAL_AMOUNT
788 -- This is a wrapper procedure for concurrent program to call private API
789 -- Business Rules :
790 -- Parameters :
791 -- Version : 1.0
792 -- End of comments
793 ----------------------------------------------------------------------------------
794
795 PROCEDURE recal_tot_princ_amt(x_errbuf OUT NOCOPY VARCHAR2
796 ,x_retcode OUT NOCOPY NUMBER
797 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE)
798 IS
799 l_api_name CONSTANT VARCHAR2(40) := 'recal_tot_princ_amt';
800 l_api_version CONSTANT NUMBER := 1.0;
801 p_api_version CONSTANT NUMBER := 1.0;
802 l_init_msg_list VARCHAR2(1) := 'T';
803 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
804 l_msg_count NUMBER;
805 l_row_count NUMBER;
806 l_amount NUMBER;
807 i NUMBER;
808 l_pol_id okl_pools.id%TYPE;
809
810 l_msg_data VARCHAR2(2000);
811 l_RecordsProcessed NUMBER := 0;
812 l_error_msg_rec Error_message_Type;
813
814 CURSOR c_pol IS
815 SELECT pol.id
816 FROM okl_pools pol
817 WHERE EXISTS
818 (SELECT '1'
819 FROM okl_pool_contents poc
820 WHERE poc.pol_id = pol.id
821 AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE))
822 ;
823
824 CURSOR c_pool IS
825 SELECT pol.id
826 FROM okl_pools pol
827 WHERE pol.pool_number = p_pool_number
828 ;
829
830 BEGIN
831
832 x_retcode := 0;
833 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
834 p_pkg_name => G_PKG_NAME,
835 p_init_msg_list => l_init_msg_list,
836 l_api_version => l_api_version,
837 p_api_version => p_api_version,
838 p_api_type => G_API_TYPE,
839 x_return_status => l_return_status);
840
841 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
842 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
843 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
844 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
845 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
846 RAISE G_EXCEPTION_ERROR;
847 END IF;
848 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
849 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
850
851 -- Printing the values in the log file.
852 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
853 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pool_number : ' || p_pool_number);
854 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
855
856
857 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
858
859 IF (p_pool_number IS NOT NULL) THEN
860
861 OPEN c_pool;
862 FETCH c_pool INTO l_pol_id;
863 CLOSE c_pool;
864
865 Okl_Pool_Pvt.recal_tot_princ_amt(
866 p_api_version => l_api_version
867 ,p_init_msg_list => l_init_msg_list
868 ,x_return_status => l_return_status
869 ,x_msg_count => l_msg_count
870 ,x_msg_data => l_msg_data
871 ,x_value => l_amount
872 ,p_pol_id => l_pol_id);
873
874 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
875 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_UNEXPECTED_ERROR));
876 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
877 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
878 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_EXPECTED_ERROR));
879 RAISE G_EXCEPTION_ERROR;
880 END IF;
881
882 i := 1; -- default
883 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_pol_id);
884 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_value : ' || l_amount);
885
886 ELSE
887 OPEN c_pol;
888 i := 0;
889 LOOP
890
891 FETCH c_pol INTO
892 l_pol_id;
893
894 EXIT WHEN c_pol%NOTFOUND;
895
896 Okl_Pool_Pvt.recal_tot_princ_amt(
897 p_api_version => l_api_version
898 ,p_init_msg_list => l_init_msg_list
899 ,x_return_status => l_return_status
900 ,x_msg_count => l_msg_count
901 ,x_msg_data => l_msg_data
902 ,x_value => l_amount
903 ,p_pol_id => l_pol_id);
904
905 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
906 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_UNEXPECTED_ERROR));
907 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
908 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
909 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_EXPECTED_ERROR));
910 RAISE G_EXCEPTION_ERROR;
911 END IF;
912
913 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_pol_id);
914 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_value : ' || l_amount);
915
916 i := i+1;
917 END LOOP;
918 CLOSE c_pol;
919
920 END IF;
921
922 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED) || i);
923 --FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED));
924 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
925
926 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
927 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
928
929 x_retcode := 0;
930
931 EXCEPTION
932 WHEN G_EXCEPTION_ERROR THEN
933 x_retcode := 2;
934 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
935 p_pkg_name => G_PKG_NAME,
936 p_exc_name => G_EXC_NAME_ERROR,
937 x_msg_count => l_msg_count,
938 x_msg_data => l_msg_data,
939 p_api_type => G_API_TYPE);
940
941 GET_ERROR_MESSAGE(l_error_msg_rec);
942 IF (l_error_msg_rec.COUNT > 0) THEN
943 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
944 LOOP
945 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
946 END LOOP;
947 END IF;
948 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
949 x_retcode := 2;
950 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
951 p_pkg_name => G_PKG_NAME,
952 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
953 x_msg_count => l_msg_count,
954 x_msg_data => l_msg_data,
955 p_api_type => G_API_TYPE);
956
957 GET_ERROR_MESSAGE(l_error_msg_rec);
958 IF (l_error_msg_rec.COUNT > 0) THEN
959 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
960 LOOP
961 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
962 END LOOP;
963 END IF;
964 WHEN OTHERS THEN
965 x_errbuf := SQLERRM;
966 x_retcode := 2;
967 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
968 p_pkg_name => G_PKG_NAME,
969 p_exc_name => G_EXC_NAME_OTHERS,
970 x_msg_count => l_msg_count,
971 x_msg_data => l_msg_data,
972 p_api_type => G_API_TYPE);
973
974 GET_ERROR_MESSAGE(l_error_msg_rec);
975 IF (l_error_msg_rec.COUNT > 0) THEN
976 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
977 LOOP
978 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
979 END LOOP;
980 END IF;
981 END recal_tot_princ_amt;
982
983 ----------------------------------------------------------------------------------
984 -- Start of comments
985 --
986 -- Procedure Name : recal_tot_princ_amt_ui
987 -- Description : update asset principal amount from pool contents by okl_pools.id to okl_pools.TOTAL_PRINCIPAL_AMOUNT
988 -- This is a wrapper procedure for concurrent program call from jsp/UI
989 -- Business Rules :
990 -- Parameters :
991 -- Version : 1.0
992 -- End of comments
993 ----------------------------------------------------------------------------------
994
995 PROCEDURE recal_tot_princ_amt_ui(
996 p_api_version IN NUMBER
997 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
998 ,x_return_status OUT NOCOPY VARCHAR2
999 ,x_msg_count OUT NOCOPY NUMBER
1000 ,x_msg_data OUT NOCOPY VARCHAR2
1001 -- concurent out parameter
1002 ,x_request_id OUT NOCOPY NUMBER
1003 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE)
1004 AS
1005 l_api_name VARCHAR2(30) := 'recal_tot_princ_amt_ui';
1006 l_api_version NUMBER := 1.0;
1007 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1008 l_date VARCHAR2(20) ;
1009
1010 BEGIN
1011
1012 x_return_status := G_RET_STS_SUCCESS;
1013
1014 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1015 p_pkg_name => G_PKG_NAME,
1016 p_init_msg_list => p_init_msg_list,
1017 l_api_version => l_api_version,
1018 p_api_version => p_api_version,
1019 p_api_type => G_API_TYPE,
1020 x_return_status => l_return_status);
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 --call concurrent program
1029 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1030
1031 x_request_id := Fnd_Request.SUBMIT_REQUEST(
1032 application => 'OKL'
1033 ,program => 'OKL_RECAL_POOL_PRINC_AMT'
1034 ,argument1 => p_pool_number);
1035
1036 -- Added these validations to check to see if the request has been submitted successfully.
1037 IF x_request_id = 0 THEN
1038 OKL_API.set_message(p_app_name => G_APP_NAME,
1039 p_msg_name => 'OKL_CONC_REQ_ERROR',
1040 p_token1 => 'PROG_NAME',
1041 p_token1_value => 'OKL_RECAL_POOL_PRINC_AMT',
1042 p_token2 => 'REQUEST_ID',
1043 p_token2_value => x_request_id);
1044
1045 RAISE G_EXCEPTION_ERROR;
1046 END IF;
1047
1048 OKL_API.end_activity(x_msg_count, x_msg_data);
1049
1050 EXCEPTION
1051 WHEN G_EXCEPTION_ERROR THEN
1052
1053 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1054 p_pkg_name => G_PKG_NAME,
1055 p_exc_name => G_EXC_NAME_ERROR,
1056 x_msg_count => x_msg_count,
1057 x_msg_data => x_msg_data,
1058 p_api_type => G_API_TYPE);
1059 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1060
1061 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1062 p_pkg_name => G_PKG_NAME,
1063 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data,
1066 p_api_type => G_API_TYPE);
1067 WHEN OTHERS THEN
1068
1069 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1070 p_pkg_name => G_PKG_NAME,
1071 p_exc_name => G_EXC_NAME_OTHERS,
1072 x_msg_count => x_msg_count,
1073 x_msg_data => x_msg_data,
1074 p_api_type => G_API_TYPE);
1075 END recal_tot_princ_amt_ui;
1076
1077 ----------------------------------------------------------------------------------
1078 -- Start of comments
1079 --
1080 -- Procedure Name : reconcile_pool_contents
1081 -- Description : Reconcile Pool Contents
1082 -- This is a wrapper procedure for concurrent program to call private API
1083 -- Business Rules :
1084 -- Parameters :
1085 -- Version : 1.0
1086 -- End of comments
1087 ----------------------------------------------------------------------------------
1088
1089 PROCEDURE reconcile_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
1090 ,x_retcode OUT NOCOPY NUMBER
1091 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL)
1092 IS
1093 -- Bug#2837819, 03/10/2003
1094 -- mvasudev, modified to take care of null khrs in pools
1095 CURSOR l_okl_pols_csr
1096 IS
1097 SELECT polb.id
1098 FROM okl_pools polb
1099 WHERE EXISTS
1100 (SELECT '1'
1101 FROM okl_pool_contents pocb
1102 WHERE pocb.pol_id = polb.id
1103 AND pocb.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
1104 -- Bug#2829983
1105 AND polb.status_code = 'NEW'
1106 );
1107
1108 -- Bug#2837819, 03/10/2003
1109 -- mvasudev, modified to take care of null khrs in pools
1110 CURSOR l_okl_pol_csr(p_pool_number IN VARCHAR2)
1111 IS
1112 SELECT polb.id
1113 FROM okl_pools polb
1114 WHERE polb.pool_number = p_pool_number
1115 -- Bug#2829983
1116 AND polb.status_code = 'NEW';
1117
1118 l_api_name CONSTANT VARCHAR2(40) := 'reconcile_pool_contents';
1119 l_api_version CONSTANT NUMBER := 1.0;
1120 p_api_version CONSTANT NUMBER := 1.0;
1121 l_init_msg_list VARCHAR2(1) := 'T';
1122
1123 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1124 l_msg_count NUMBER;
1125 l_reconciled VARCHAR2(1);
1126 i NUMBER;
1127 l_msg_data VARCHAR2(2000);
1128 l_error_msg_rec Error_message_Type;
1129
1130 BEGIN
1131
1132 x_retcode := 0;
1133 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1134 p_pkg_name => G_PKG_NAME,
1135 p_init_msg_list => l_init_msg_list,
1136 l_api_version => l_api_version,
1137 p_api_version => p_api_version,
1138 p_api_type => G_API_TYPE,
1139 x_return_status => l_return_status);
1140
1141 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1142 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
1143 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1144 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1145 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
1146 RAISE G_EXCEPTION_ERROR;
1147 END IF;
1148
1149 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
1150 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1151
1152 -- Printing the values in the log file.
1153
1154 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1155 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pool_number : ' || p_pool_number);
1156 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1157
1158 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
1159
1160
1161 -- Reconcile the specified Pool
1162 IF (p_pool_number IS NOT NULL) THEN
1163
1164 i :=0;
1165
1166 FOR l_okl_pol_rec IN l_okl_pol_csr(p_pool_number)
1167 LOOP
1168 Okl_Pool_Pvt.reconcile_contents(p_api_version => l_api_version
1169 ,p_init_msg_list => l_init_msg_list
1170 ,p_pol_id => l_okl_pol_rec.id
1171 ,x_return_status => l_return_status
1172 ,x_msg_count => l_msg_count
1173 ,x_msg_data => l_msg_data
1174 ,x_reconciled => l_reconciled);
1175
1176 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1177 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_UNEXPECTED_ERROR));
1178 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1179 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1180 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_EXPECTED_ERROR));
1181 RAISE G_EXCEPTION_ERROR;
1182 END IF;
1183
1184 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_okl_pol_rec.id);
1185 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_reconciled : ' || l_reconciled);
1186
1187 i := i + 1;
1188 END LOOP;
1189
1190 IF i = 0 THEN
1191 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_RECONCILE_ERROR));
1192 RAISE G_EXCEPTION_ERROR;
1193 END IF;
1194 -- Reconcile all non-attached Pools
1195 ELSE
1196 i := 0;
1197 FOR l_okl_pols_rec IN l_okl_pols_csr
1198 LOOP
1199 Okl_Pool_Pvt.reconcile_contents(p_api_version => l_api_version
1200 ,p_init_msg_list => l_init_msg_list
1201 ,p_pol_id => l_okl_pols_rec.id
1202 ,x_return_status => l_return_status
1203 ,x_msg_count => l_msg_count
1204 ,x_msg_data => l_msg_data
1205 ,x_reconciled => l_reconciled);
1206
1207 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1208 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_UNEXPECTED_ERROR));
1209 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1210 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1211 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_EXPECTED_ERROR));
1212 RAISE G_EXCEPTION_ERROR;
1213 END IF;
1214
1215 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_okl_pols_rec.id);
1216 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_reconciled : ' || l_reconciled);
1217
1218 i := i + 1;
1219 END LOOP;
1220 END IF;
1221
1222 --FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED) || i);
1223 --FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED));
1224 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
1225
1226 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
1227 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
1228
1229 EXCEPTION
1230 WHEN G_EXCEPTION_ERROR THEN
1231 x_retcode := 2;
1232 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1233 p_pkg_name => G_PKG_NAME,
1234 p_exc_name => G_EXC_NAME_ERROR,
1235 x_msg_count => l_msg_count,
1236 x_msg_data => l_msg_data,
1237 p_api_type => G_API_TYPE);
1238
1239 GET_ERROR_MESSAGE(l_error_msg_rec);
1240 IF (l_error_msg_rec.COUNT > 0) THEN
1241 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1242 LOOP
1243 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1244 END LOOP;
1245 END IF;
1246 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1247 x_retcode := 2;
1248 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1249 p_pkg_name => G_PKG_NAME,
1250 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1251 x_msg_count => l_msg_count,
1252 x_msg_data => l_msg_data,
1253 p_api_type => G_API_TYPE);
1254
1255 GET_ERROR_MESSAGE(l_error_msg_rec);
1256 IF (l_error_msg_rec.COUNT > 0) THEN
1257 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1258 LOOP
1259 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1260 END LOOP;
1261 END IF;
1262 WHEN OTHERS THEN
1263 x_errbuf := SQLERRM;
1264 x_retcode := 2;
1265 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1266 p_pkg_name => G_PKG_NAME,
1267 p_exc_name => G_EXC_NAME_OTHERS,
1268 x_msg_count => l_msg_count,
1269 x_msg_data => l_msg_data,
1270 p_api_type => G_API_TYPE);
1271
1272 GET_ERROR_MESSAGE(l_error_msg_rec);
1273 IF (l_error_msg_rec.COUNT > 0) THEN
1274 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1275 LOOP
1276 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1277 END LOOP;
1278 END IF;
1279
1280 END reconcile_pool_contents;
1281
1282 ----------------------------------------------------------------------------------
1283 -- Start of comments
1284 --
1285 -- Procedure Name : recon_pnd_pool_con
1286 -- Description : Reconcile Pending Pool Contents
1287 -- This is a wrapper procedure for concurrent program to call private API
1288 -- Business Rules :
1289 -- Parameters :
1290 -- Version : 1.0
1291 -- End of comments
1292 ----------------------------------------------------------------------------------
1293
1294 PROCEDURE recon_pnd_pool_con(x_errbuf OUT NOCOPY VARCHAR2
1295 ,x_retcode OUT NOCOPY NUMBER
1296 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL)
1297 IS
1298 -- Bug#2837819, 03/10/2003
1299 -- mvasudev, modified to take care of null khrs in pools
1300 CURSOR l_okl_pols_csr
1301 IS
1302 SELECT polb.id
1303 FROM okl_pools polb
1304 WHERE EXISTS
1305 (SELECT '1'
1306 FROM okl_pool_contents pocb
1307 WHERE pocb.pol_id = polb.id
1308 AND pocb.status_code = G_POC_STS_PENDING
1309 AND polb.status_code = 'ACTIVE'
1310 );
1311
1312 -- Bug#2837819, 03/10/2003
1313 -- mvasudev, modified to take care of null khrs in pools
1314 CURSOR l_okl_pol_csr(p_pool_number IN VARCHAR2)
1315 IS
1316 SELECT polb.id
1317 FROM okl_pools polb
1318 WHERE polb.pool_number = p_pool_number
1319 AND polb.status_code = 'ACTIVE';
1320
1321 l_api_name CONSTANT VARCHAR2(40) := 'recon_pnd_pool_con';
1322 l_api_version CONSTANT NUMBER := 1.0;
1323 p_api_version CONSTANT NUMBER := 1.0;
1324 l_init_msg_list VARCHAR2(1) := 'T';
1325
1326 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1327 l_msg_count NUMBER;
1328 l_reconciled VARCHAR2(1);
1329 i NUMBER;
1330 l_msg_data VARCHAR2(2000);
1331 l_error_msg_rec Error_message_Type;
1332
1333 BEGIN
1334
1335 x_retcode := 0;
1336 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1337 p_pkg_name => G_PKG_NAME,
1338 p_init_msg_list => l_init_msg_list,
1339 l_api_version => l_api_version,
1340 p_api_version => p_api_version,
1341 p_api_type => G_API_TYPE,
1342 x_return_status => l_return_status);
1343
1344 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1345 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
1346 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1347 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1348 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
1349 RAISE G_EXCEPTION_ERROR;
1350 END IF;
1351
1352 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
1353 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1354
1355 -- Printing the values in the log file.
1356
1357 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1358 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pool_number : ' || p_pool_number);
1359 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1360
1361 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
1362
1363
1364 -- Reconcile the specified Pool
1365 IF (p_pool_number IS NOT NULL) THEN
1366
1367 i :=0;
1368
1369 FOR l_okl_pol_rec IN l_okl_pol_csr(p_pool_number)
1370 LOOP
1371 IF l_okl_pol_csr%NOTFOUND THEN
1372 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'','OKL_NO_PENDING_POC'));
1373 ELSE
1374 Okl_Pool_Pvt.reconcile_contents(p_api_version => l_api_version
1375 ,p_init_msg_list => l_init_msg_list
1376 ,p_pol_id => l_okl_pol_rec.id
1377 ,p_mode => 'ACTIVE'
1378 ,x_return_status => l_return_status
1379 ,x_msg_count => l_msg_count
1380 ,x_msg_data => l_msg_data
1381 ,x_reconciled => l_reconciled);
1382
1383 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1384 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_UNEXPECTED_ERROR));
1385 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1386 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1387 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_EXPECTED_ERROR));
1388 RAISE G_EXCEPTION_ERROR;
1389 END IF;
1390
1391 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_okl_pol_rec.id);
1392 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_reconciled : ' || l_reconciled);
1393
1394 i := i + 1;
1395 END IF;
1396 END LOOP;
1397
1398 IF i = 0 THEN
1399 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_RECONCILE_ERROR));
1400 RAISE G_EXCEPTION_ERROR;
1401 END IF;
1402 -- Reconcile all non-attached Pools
1403 ELSE
1404 i := 0;
1405 FOR l_okl_pols_rec IN l_okl_pols_csr
1406 LOOP
1407 IF l_okl_pol_csr%NOTFOUND THEN
1408 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'','OKL_NO_PENDING_POC'));
1409 ELSE
1410 Okl_Pool_Pvt.reconcile_contents(p_api_version => l_api_version
1411 ,p_init_msg_list => l_init_msg_list
1412 ,p_pol_id => l_okl_pols_rec.id
1413 ,p_mode => 'ACTIVE'
1414 ,x_return_status => l_return_status
1415 ,x_msg_count => l_msg_count
1416 ,x_msg_data => l_msg_data
1417 ,x_reconciled => l_reconciled);
1418
1419 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1420 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_UNEXPECTED_ERROR));
1421 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1422 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1423 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECONCILE_CONTENTS',G_EXPECTED_ERROR));
1424 RAISE G_EXCEPTION_ERROR;
1425 END IF;
1426
1427 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: p_pol_id : ' || l_okl_pols_rec.id);
1428 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME || '.' || l_api_name || ':: x_reconciled : ' || l_reconciled);
1429
1430 i := i + 1;
1431 END IF;
1432 END LOOP;
1433 END IF;
1434
1435 --FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED) || i);
1436 --FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED));
1437 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
1438
1439 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
1440 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
1441
1442 EXCEPTION
1443 WHEN G_EXCEPTION_ERROR THEN
1444 x_retcode := 2;
1445 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1446 p_pkg_name => G_PKG_NAME,
1447 p_exc_name => G_EXC_NAME_ERROR,
1448 x_msg_count => l_msg_count,
1449 x_msg_data => l_msg_data,
1450 p_api_type => G_API_TYPE);
1451
1452 GET_ERROR_MESSAGE(l_error_msg_rec);
1453 IF (l_error_msg_rec.COUNT > 0) THEN
1454 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1455 LOOP
1456 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1457 END LOOP;
1458 END IF;
1459 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1460 x_retcode := 2;
1461 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1462 p_pkg_name => G_PKG_NAME,
1463 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1464 x_msg_count => l_msg_count,
1465 x_msg_data => l_msg_data,
1466 p_api_type => G_API_TYPE);
1467
1468 GET_ERROR_MESSAGE(l_error_msg_rec);
1469 IF (l_error_msg_rec.COUNT > 0) THEN
1470 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1471 LOOP
1472 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1473 END LOOP;
1474 END IF;
1475 WHEN OTHERS THEN
1476 x_errbuf := SQLERRM;
1477 x_retcode := 2;
1478 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1479 p_pkg_name => G_PKG_NAME,
1480 p_exc_name => G_EXC_NAME_OTHERS,
1481 x_msg_count => l_msg_count,
1482 x_msg_data => l_msg_data,
1483 p_api_type => G_API_TYPE);
1484
1485 GET_ERROR_MESSAGE(l_error_msg_rec);
1486 IF (l_error_msg_rec.COUNT > 0) THEN
1487 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
1488 LOOP
1489 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
1490 END LOOP;
1491 END IF;
1492
1493 END recon_pnd_pool_con;
1494
1495 ----------------------------------------------------------------------------------
1496 -- Start of comments
1497 --
1498 -- Procedure Name : reconcile_pool_contents_ui
1499 -- Description : Reconcile Pool Contents - to be called from UI
1500 -- This is a wrapper procedure for concurrent program call from jsp/UI
1501 -- Business Rules :
1502 -- Parameters :
1503 -- Version : 1.0
1504 -- End of comments
1505 ----------------------------------------------------------------------------------
1506 PROCEDURE reconcile_pool_contents_ui(
1507 p_api_version IN NUMBER
1508 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1509 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL
1510 ,x_return_status OUT NOCOPY VARCHAR2
1511 ,x_msg_count OUT NOCOPY NUMBER
1512 ,x_msg_data OUT NOCOPY VARCHAR2
1513 ,x_request_id OUT NOCOPY NUMBER)
1514 IS
1515 l_api_name VARCHAR2(30) := 'reconcile_pool_contents_ui';
1516 l_api_version NUMBER := 1.0;
1517 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1518
1519 BEGIN
1520 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1521 p_pkg_name => G_PKG_NAME,
1522 p_init_msg_list => p_init_msg_list,
1523 l_api_version => l_api_version,
1524 p_api_version => p_api_version,
1525 p_api_type => G_API_TYPE,
1526 x_return_status => l_return_status);
1527 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1528 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1529 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1530 RAISE G_EXCEPTION_ERROR;
1531 END IF;
1532 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1533 --call concurrent program
1534 x_request_id := Fnd_Request.SUBMIT_REQUEST(
1535 application => 'OKL'
1536 ,program => 'OKL_RECONCILE_POOL'
1537 ,argument1 => p_pool_number);
1538
1539 -- Added these validations to check to see if the request has been submitted successfully.
1540 IF x_request_id = 0 THEN
1541
1542 OKL_API.set_message(p_app_name => G_APP_NAME,
1543 p_msg_name => 'OKL_CONC_REQ_ERROR',
1544 p_token1 => 'PROG_NAME',
1545 p_token1_value => 'OKL Reconcile Pool CONTENTS',
1546 p_token2 => 'REQUEST_ID',
1547 p_token2_value => x_request_id);
1548
1549 RAISE G_EXCEPTION_ERROR;
1550 END IF;
1551
1552 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1553
1554 x_return_status := l_return_status;
1555
1556 EXCEPTION
1557 WHEN G_EXCEPTION_ERROR THEN
1558
1559 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1560 p_pkg_name => G_PKG_NAME,
1561 p_exc_name => G_EXC_NAME_ERROR,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data,
1564 p_api_type => G_API_TYPE);
1565 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1566
1567 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1568 p_pkg_name => G_PKG_NAME,
1569 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1570 x_msg_count => x_msg_count,
1571 x_msg_data => x_msg_data,
1572 p_api_type => G_API_TYPE);
1573 WHEN OTHERS THEN
1574
1575 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1576 p_pkg_name => G_PKG_NAME,
1577 p_exc_name => G_EXC_NAME_OTHERS,
1578 x_msg_count => x_msg_count,
1579 x_msg_data => x_msg_data,
1580 p_api_type => G_API_TYPE);
1581
1582
1583 END reconcile_pool_contents_ui;
1584
1585 -- Bug#2843163, mvasudev, 03/14/2003
1586
1587 ----------------------------------------------------------------------------------
1588 -- Start of comments
1589 -- mvasudev
1590 -- Procedure Name : cleanup_pool_contents
1591 -- Description : CleanUp Pool Contents based on passed in search criteria
1592 -- This is a wrapper procedure for concurrent program to call private API
1593 -- Business Rules :
1594 -- Parameters :
1595 -- Version : 1.0
1596 -- End of comments
1597 ----------------------------------------------------------------------------------
1598
1599 PROCEDURE cleanup_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
1600 ,x_retcode OUT NOCOPY NUMBER
1601 ,p_pol_id IN VARCHAR2
1602 ,p_currency_code IN VARCHAR2
1603 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1604 ,p_cust_object1_id1 IN VARCHAR2 DEFAULT NULL -- customer_id
1605 ,p_sic_code IN VARCHAR2 DEFAULT NULL
1606 ,p_dnz_chr_id IN VARCHAR2 DEFAULT NULL -- dnz_chr_id
1607 ,p_pre_tax_yield_from IN VARCHAR2 DEFAULT NULL
1608 ,p_pre_tax_yield_to IN VARCHAR2 DEFAULT NULL
1609 ,p_book_classification IN VARCHAR2 DEFAULT NULL
1610 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
1611 ,p_pdt_id IN VARCHAR2 DEFAULT NULL
1612 ,p_start_from_date IN VARCHAR2 DEFAULT NULL
1613 ,p_start_to_date IN VARCHAR2 DEFAULT NULL
1614 ,p_end_from_date IN VARCHAR2 DEFAULT NULL
1615 ,p_end_to_date IN VARCHAR2 DEFAULT NULL
1616 ,p_asset_id IN VARCHAR2 DEFAULT NULL
1617 ,p_item_id1 IN VARCHAR2 DEFAULT NULL
1618 ,p_model_number IN VARCHAR2 DEFAULT NULL
1619 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
1620 ,p_vendor_id1 IN VARCHAR2 DEFAULT NULL
1621 ,p_oec_from IN VARCHAR2 DEFAULT NULL
1622 ,p_oec_to IN VARCHAR2 DEFAULT NULL
1623 ,p_residual_percentage IN VARCHAR2 DEFAULT NULL
1624 ,p_sty_id IN VARCHAR2 DEFAULT NULL
1625 ,p_streams_from_date IN VARCHAR2 DEFAULT NULL
1626 ,p_streams_to_date IN VARCHAR2 DEFAULT NULL
1627 ,p_action_code IN VARCHAR2
1628 -- mvasudev, 11.5.10
1629 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
1630 -- end, mvasudev, 11.5.10
1631 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL
1632 )
1633 IS
1634
1635 -- Bug#2829983, v115.13
1636 CURSOR l_okl_pol_status_csr(p_pol_id IN NUMBER)
1637 IS
1638 SELECT status_code
1639 FROM okl_pools
1640 WHERE id = p_pol_id;
1641
1642 CURSOR l_okl_pol_csr IS
1643 SELECT polv.pool_number pool_number
1644 ,psts.meaning pool_status
1645 ,polv.date_created date_created
1646 ,polv.date_last_updated date_last_updated
1647 ,polv.date_last_reconciled date_last_reconciled
1648 ,polv.total_principal_amount total_net_asset_net_investment
1649 ,polv.date_total_principal_calc date_last_calculated
1650 FROM OKL_POOLS polv -- to take care of org_id
1651 ,okc_statuses_tl psts
1652 WHERE polv.id = p_pol_id
1653 AND polv.status_code = psts.code
1654 AND psts.LANGUAGE = USERENV('LANG');
1655
1656 CURSOR l_okl_set_of_books_csr
1657 IS
1658 SELECT okls.set_of_books_id set_of_books_id
1659 ,glsb.name set_of_books_name
1660 FROM GL_LEDGERS_PUBLIC_V glsb
1661 ,OKL_SYS_ACCT_OPTS okls
1662 WHERE glsb.ledger_id = okls.set_of_books_id;
1663
1664 CURSOR l_okl_operating_unit_csr
1665 IS
1666 SELECT name
1667 FROM hr_operating_units
1668 WHERE organization_id = mo_global.get_current_org_id();
1669
1670 CURSOR l_okl_customer_csr
1671 IS
1672 SELECT name
1673 FROM OKX_PARTIES_V
1674 WHERE id1 = p_cust_object1_id1;
1675
1676 CURSOR l_okl_sic_csr
1677 IS
1678 SELECT name
1679 FROM OKL_POOL_CUST_INDUSTRY_UV
1680 WHERE code = p_sic_code;
1681
1682 CURSOR l_okl_chr_csr
1683 IS
1684 SELECT contract_number
1685 FROM OKC_K_HEADERS_V
1686 WHERE id = p_dnz_chr_id;
1687
1688 CURSOR l_okl_book_class_csr
1689 IS
1690 SELECT meaning
1691 FROM FND_LOOKUPS
1692 WHERE lookup_type='OKL_BOOK_CLASS'
1693 AND NVL(start_date_active,SYSDATE) <=SYSDATE
1694 AND NVL(end_date_active,SYSDATE+1) > SYSDATE
1695 AND enabled_flag = 'Y'
1696 AND lookup_code = p_book_classification;
1697
1698 CURSOR l_okl_tax_owner_csr
1699 IS
1700 SELECT meaning
1701 FROM FND_LOOKUPS
1702 WHERE lookup_type='OKL_TAX_OWNER'
1703 AND NVL(start_date_active,SYSDATE) <=SYSDATE
1704 AND NVL(end_date_active,SYSDATE+1) > SYSDATE
1705 AND enabled_flag = 'Y'
1706 AND lookup_code = p_tax_owner;
1707
1708 CURSOR l_okl_pdt_csr
1709 IS
1710 SELECT name
1711 FROM OKL_PRODUCTS
1712 WHERE id = p_pdt_id;
1713
1714 CURSOR l_okl_asset_csr
1715 IS
1716 SELECT asset_number
1717 FROM OKL_POOL_ASSETS_LOV_UV
1718 WHERE asset_id = p_asset_id;
1719
1720 CURSOR l_okl_item_csr
1721 IS
1722 SELECT name
1723 FROM OKX_SYSTEM_ITEMS_V
1724 WHERE ID2 = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_ITEM_INV_ORG_ID)
1725 AND id1 = p_item_id1;
1726
1727 CURSOR l_okl_vendor_csr
1728 IS
1729 SELECT name
1730 FROM OKX_VENDORS_V
1731 WHERE id1 = p_vendor_id1;
1732
1733 /*
1734 CURSOR l_okl_strm_type_csr
1735 IS
1736 SELECT name
1737 FROM okl_strm_type_v
1738 WHERE id = p_sty_id;
1739 */
1740
1741 CURSOR l_okl_sty_subclass_csr
1742 IS
1743 SELECT meaning
1744 FROM fnd_lookups
1745 WHERE lookup_type = 'OKL_STREAM_TYPE_SUBCLASS'
1746 AND lookup_code = p_stream_type_subclass;
1747
1748 --Added ssdeshpa
1749 CURSOR l_okl_cust_crdt_clsf_csr
1750 IS
1751 SELECT MEANING AS CREDITCXLSSFCTN
1752 FROM AR_LOOKUPS fndlup
1753 WHERE LOOKUP_TYPE = 'AR_CMGT_CREDIT_CLASSIFICATION'
1754 AND LOOKUP_CODE = p_cust_crd_clf_code
1755 AND ENABLED_FLAG = 'Y'
1756 AND SYSDATE BETWEEN NVL (fndlup.start_date_active,sysdate)
1757 AND NVL(fndlup.end_date_active,sysdate);
1758
1759 l_cust_crd_clf VARCHAR2(80);
1760
1761 l_api_name CONSTANT VARCHAR2(40) := 'cleanup_pool_contents';
1762 l_api_version CONSTANT NUMBER := 1.0;
1763 p_api_version CONSTANT NUMBER := 1.0;
1764 l_init_msg_list VARCHAR2(1) := 'T';
1765 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1766 l_msg_count NUMBER;
1767
1768 l_msg_data VARCHAR2(2000);
1769 l_RecordsProcessed NUMBER := 0;
1770 l_error_msg_rec Error_message_Type;
1771
1772 lp_pocv_tbl pocv_tbl_type;
1773 lx_poc_uv_tbl poc_uv_tbl_type;
1774 l_amount NUMBER;
1775
1776 -- report related fields
1777 l_row_num_len NUMBER := 6;
1778 l_contract_num_len NUMBER := 30;
1779 l_asset_num_len NUMBER := 15;
1780 l_lessee_len NUMBER := 40;
1781 l_sty_name_len NUMBER := 15;
1782 l_amount_len NUMBER := 15;
1783 l_max_len NUMBER := 150;
1784 l_prompt_len NUMBER := 35;
1785 l_sty_subclass_len NUMBER := 25;
1786 -- mvasudev, 09/28/2004, Bug#3909240
1787 l_sty_purpose_len NUMBER := 35;
1788
1789
1790 l_str_row_num VARCHAR2(5);
1791 l_str_contract_num VARCHAR2(30);
1792 l_str_asset_num VARCHAR2(15);
1793 l_str_lessee VARCHAR2(50);
1794 l_str_sty_name VARCHAR2(25);
1795 l_str_amount VARCHAR2(15);
1796 l_content VARCHAR2(1000);
1797 l_header_len NUMBER;
1798 -- mvasudev, 09/28/2004, Bug#3909240
1799 l_str_sty_purpose VARCHAR2(35);
1800
1801 -- Search Parameters
1802 l_customer VARCHAR2(360);
1803 l_customer_industry VARCHAR2(80);
1804 l_contract_number VARCHAR2(120);
1805 l_book_class VARCHAR2(80);
1806 l_tax_owner VARCHAR2(80);
1807 l_product VARCHAR2(150);
1808 -- l_asset_number VARCHAR2(15);
1809 -- l_item VARCHAR2(240);
1810 -- l_vendor VARCHAR2(80);
1811 l_stream_type_subclass VARCHAR2(150);
1812
1813 l_filler VARCHAR2(5) := RPAD(' ',5,' ');
1814
1815 BEGIN
1816
1817 x_retcode := 0;
1818
1819 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1820 p_pkg_name => G_PKG_NAME,
1821 p_init_msg_list => l_init_msg_list,
1822 l_api_version => l_api_version,
1823 p_api_version => p_api_version,
1824 p_api_type => G_API_TYPE,
1825 x_return_status => l_return_status);
1826
1827 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1828 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_UNEXPECTED_ERROR));
1829 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1830 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1831 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_EXPECTED_ERROR));
1832 RAISE G_EXCEPTION_ERROR;
1833 END IF;
1834
1835 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.START_ACTIVITY',G_CONFIRM_PROCESS));
1836 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1837
1838 -- Printing the values in the log file.
1839
1840 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1841 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pol_id : ' || p_pol_id);
1842 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_multi_org : ' || p_multi_org);
1843 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cust_object1_id1 : ' || p_cust_object1_id1);
1844
1845 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_sic_code : ' || p_sic_code);
1846 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_dnz_chr_id : ' || p_dnz_chr_id);
1847 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pre_tax_yield_from : ' || p_pre_tax_yield_from);
1848 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_pre_tax_yield_to : ' || p_pre_tax_yield_to);
1849 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_book_classification : ' || p_book_classification);
1850 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_tax_owner : ' || p_tax_owner);
1851 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_from_date : ' || p_start_from_date);
1852 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_to_date : ' || p_start_to_date);
1853 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_from_date : ' || p_end_from_date);
1854 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_to_date : ' || p_end_to_date);
1855 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_asset_id : ' || p_asset_id);
1856 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_item_id1 : ' || p_item_id1);
1857 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_model_number : ' || p_model_number);
1858 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_manufacturer_name : ' || p_manufacturer_name);
1859 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_vendor_id1 : ' || p_vendor_id1);
1860 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_oec_from : ' || p_oec_from);
1861 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_oec_to : ' || p_oec_to);
1862 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_residual_percentage : ' || p_residual_percentage);
1863 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_sty_id : ' || p_sty_id);
1864 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_stream_type_subclass : ' || p_stream_type_subclass);
1865 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_streams_from_date : ' || p_streams_from_date);
1866 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_streams_to_date : ' || p_streams_to_date);
1867 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_action_code : ' || p_action_code);
1868 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cust_crd_clf_code : ' || p_cust_crd_clf_code);
1869 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1870
1871
1872 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_START));
1873
1874 -- Bug#2829983, v115.13
1875 FOR l_okl_pol_status_rec IN l_okl_pol_status_csr(p_pol_id)
1876 LOOP
1877 --Included 'Active' status to allow clean up adjustment pool contents -- varangan-29-11-2007
1878 IF l_okl_pol_status_rec.status_code NOT IN (Okl_Pool_Pvt.G_POL_STS_NEW,Okl_Pool_Pvt.G_POL_STS_ACTIVE)
1879 THEN
1880 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_POOL_NO_MODIFY));
1881 RAISE G_EXCEPTION_ERROR;
1882 END IF;
1883 END LOOP;
1884
1885 -- Header
1886 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_CLEANUP_HEAD) || ' : ';
1887 IF p_action_code = Okl_Pool_Pvt.G_ACTION_REPORT THEN
1888 l_content := l_content || FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_CLEANUP_REPORT);
1889 ELSIF p_action_code = Okl_Pool_Pvt.G_ACTION_REMOVE THEN
1890 l_content := l_content || FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_CLEANUP_REMOVE);
1891 END IF;
1892 l_header_len := LENGTH(l_content);
1893 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2); -- center align header
1894 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
1895
1896 l_content := RPAD('=',l_header_len,'='); -- underline header
1897 l_content := RPAD(LPAD(l_content,l_max_len/2),l_max_len/2,'='); -- center align
1898 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
1899
1900 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1901 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1902
1903 -- Set of Books, Operating Unit
1904 FOR l_okl_set_of_books_rec IN l_okl_set_of_books_csr
1905 LOOP
1906 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_SET_OF_BOOKS),l_prompt_len) || ' : ' || l_okl_set_of_books_rec.set_of_books_name);
1907 END LOOP;
1908 FOR l_okl_operating_unit_rec IN l_okl_operating_unit_csr
1909 LOOP
1910 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_OPERATING_UNIT),l_prompt_len) || ' : ' || l_okl_operating_unit_rec.name);
1911 END LOOP;
1912 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1913
1914 -- Pool Details
1915 FOR l_okl_pol_rec IN l_okl_pol_csr
1916 LOOP
1917 IF l_okl_pol_rec.pool_status = OKL_POOL_PVT.G_POL_STS_ACTIVE THEN
1918 Okl_Pool_Pvt.get_tot_recei_amt_pend(p_api_version => l_api_version
1919 ,p_init_msg_list => l_init_msg_list
1920 ,x_return_status => l_return_status
1921 ,x_msg_count => l_msg_count
1922 ,x_msg_data => l_msg_data
1923 ,x_value => l_amount
1924 ,p_pol_id => p_pol_id);
1925 ELSE
1926 Okl_Pool_Pvt.get_tot_recei_amt(p_api_version => l_api_version
1927 ,p_init_msg_list => l_init_msg_list
1928 ,x_return_status => l_return_status
1929 ,x_msg_count => l_msg_count
1930 ,x_msg_data => l_msg_data
1931 ,x_value => l_amount
1932 ,p_pol_id => p_pol_id);
1933 END IF;
1934 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1935 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.get_tot_recei_amt',G_UNEXPECTED_ERROR));
1936 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1937 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
1938 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.get_tot_recei_amt',G_EXPECTED_ERROR));
1939 RAISE G_EXCEPTION_ERROR;
1940 END IF;
1941
1942 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_NUMBER),l_prompt_len) || ' : ' || l_okl_pol_rec.pool_number);
1943 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_POOL_STATUS),l_prompt_len) || ' : ' || l_okl_pol_rec.pool_status);
1944 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_DATE_CREATED),l_prompt_len) || ' : ' || l_okl_pol_rec.date_created);
1945 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_LAST_UPDATE_DATE),l_prompt_len) || ' : ' || l_okl_pol_rec.date_last_updated);
1946 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_DATE_LAST_RECONCILED),l_prompt_len) || ' : ' || l_okl_pol_rec.date_last_reconciled);
1947 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_VALUE_OF_STREAMS),l_prompt_len) || ' : ' || Okl_Accounting_Util.format_amount(l_amount,p_currency_code));
1948
1949 -- mvasudev, 4/29/2003, v115.15, Bug#2924696
1950 -- "Total Asset Net Investment" , "Date Last Calculated" not displayed any more
1951 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_TOTAL_ASSET_NET_INVESTMENT),l_prompt_len) || ' : ' || okl_accounting_util.format_amount(l_okl_pol_rec.total_net_asset_net_investment,p_currency_code));
1952 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_DATE_LAST_CALCULATED),l_prompt_len) || ' : ' || l_okl_pol_rec.date_last_calculated);
1953 END LOOP;
1954
1955 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1956 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CURRENCY),l_prompt_len) || ' : ' || p_currency_code);
1957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_PROGRAM_RUN_DATE),l_prompt_len) || ' : ' || SYSDATE);
1958
1959 -- Search Parameters
1960 -- sub head
1961 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1962 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_SEARCH_PARAMETERS);
1963 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
1964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
1965
1966 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1967 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMERS);
1968 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
1969 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
1970
1971 -- Customer related parameters
1972 FOR l_okl_customer_rec IN l_okl_customer_csr
1973 LOOP
1974 l_customer := l_okl_customer_rec.name;
1975 END LOOP;
1976 FOR l_okl_sic_rec IN l_okl_sic_csr
1977 LOOP
1978 l_customer_industry := l_okl_sic_rec.name;
1979 END LOOP;
1980 --added ssdeshpa
1981 FOR l_okl_cust_crdt_clsf_rec IN l_okl_cust_crdt_clsf_csr
1982 LOOP
1983 l_cust_crd_clf := l_okl_cust_crdt_clsf_rec.CREDITCXLSSFCTN;
1984 END LOOP;
1985
1986 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMER),l_prompt_len) || ' : ' || l_customer);
1987 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMER_INDUSTRY_CODE),l_prompt_len) || ' : ' || l_customer_industry);
1988 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CUSTOMER_INDUSTRY_CODE),l_prompt_len) || ' : ' || l_cust_crd_clf);
1989
1990 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1991 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACTS);
1992 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
1993 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
1994
1995 -- Contract related Parameters
1996 FOR l_okl_chr_rec IN l_okl_chr_csr
1997 LOOP
1998 l_contract_number := l_okl_chr_rec.contract_number;
1999 END LOOP;
2000 FOR l_okl_book_class_rec IN l_okl_book_class_csr
2001 LOOP
2002 l_book_class := l_okl_book_class_rec.meaning;
2003 END LOOP;
2004 FOR l_okl_tax_owner_rec IN l_okl_tax_owner_csr
2005 LOOP
2006 l_tax_owner := l_okl_tax_owner_rec.meaning;
2007 END LOOP;
2008 FOR l_okl_pdt_rec IN l_okl_pdt_csr
2009 LOOP
2010 l_product := l_okl_pdt_rec.name;
2011 END LOOP;
2012
2013 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_prompt_len) || ' : ' || l_contract_number);
2014 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PTY_FROM),l_prompt_len) || ' : ' || p_pre_tax_yield_from);
2015 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PTY_TO),l_prompt_len) || ' : ' || p_pre_tax_yield_to);
2016 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_BOOK_CLASS),l_prompt_len) || ' : ' || l_book_class);
2017 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_TAX_OWNER),l_prompt_len) || ' : ' || l_tax_owner);
2018 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_START_FROM_DATE),l_prompt_len) || ' : ' || p_start_from_date);
2019 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_START_TO_DATE),l_prompt_len) || ' : ' || p_start_to_date);
2020 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_END_FROM_DATE),l_prompt_len) || ' : ' || p_end_from_date);
2021 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_END_TO_DATE),l_prompt_len) || ' : ' || p_end_to_date);
2022 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_PRODUCT),l_prompt_len) || ' : ' || l_product);
2023
2024 /*
2025 -- Asset related Parameters
2026 FOR l_okl_asset_rec IN l_okl_asset_csr
2027 LOOP
2028 l_asset_number := l_okl_asset_rec.asset_number;
2029 END LOOP;
2030 FOR l_okl_item_rec IN l_okl_item_csr
2031 LOOP
2032 l_item := l_okl_item_rec.name;
2033 END LOOP;
2034 FOR l_okl_vendor_rec IN l_okl_vendor_csr
2035 LOOP
2036 l_vendor := l_okl_vendor_rec.name;
2037 END LOOP;
2038
2039 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_NUMBER),l_prompt_len) || ' : ' || l_asset_number);
2040 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ITEM_NUMBER),l_prompt_len) || ' : ' || l_item);
2041 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_MODEL_NUMBER),l_prompt_len) || ' : ' || p_model_number);
2042 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_MANUFACTURER),l_prompt_len) || ' : ' || p_manufacturer_name);
2043 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_VENDOR),l_prompt_len) || ' : ' || l_vendor);
2044 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_COST_FROM),l_prompt_len) || ' : ' || okl_accounting_util.format_amount(p_oec_from,p_currency_code));
2045 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_COST_TO),l_prompt_len) || ' : ' || okl_accounting_util.format_amount(p_oec_to,p_currency_code));
2046 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_RESIDUAL_PERCENTAGE),l_prompt_len) || ' : ' || p_residual_percentage);
2047 */
2048
2049 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2050 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS);
2051 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2052 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',LENGTH(l_content),'-'));
2053 -- Stream related Parameters
2054 /*
2055 FOR l_okl_strm_type_rec IN l_okl_strm_type_csr
2056 LOOP
2057 l_stream_type := l_okl_strm_type_rec.name;
2058 END LOOP;
2059 */
2060
2061 IF p_stream_type_subclass IS NOT NULL THEN
2062 FOR l_okl_sty_subclass_rec IN l_okl_sty_subclass_csr
2063 LOOP
2064 l_stream_type_subclass := l_okl_sty_subclass_rec.meaning;
2065 END LOOP;
2066 END IF;
2067
2068 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE),l_prompt_len) || ' : ' || l_stream_type);
2069 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_SUBCLASS),l_prompt_len) || ' : ' || l_stream_type_subclass);
2070 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS_FROM_DATE),l_prompt_len) || ' : ' || p_streams_from_date);
2071 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(l_filler || FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAMS_TO_DATE),l_prompt_len) || ' : ' || p_streams_to_date);
2072
2073 -- Results
2074 -- sub head
2075 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2076 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_RESULTS);
2077 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2078 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
2079 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2080 -- count
2081
2082 Okl_Pool_Pvt.cleanup_pool_contents(p_api_version => l_api_version
2083 ,p_init_msg_list => l_init_msg_list
2084 ,x_return_status => l_return_status
2085 ,x_msg_count => l_msg_count
2086 ,x_msg_data => l_msg_data
2087 ,p_multi_org => p_multi_org
2088 ,p_currency_code => p_currency_code
2089 ,p_pol_id => TO_NUMBER(p_pol_id)
2090 ,p_cust_object1_id1 => TO_NUMBER(p_cust_object1_id1)
2091 ,p_sic_code => p_sic_code
2092 ,p_dnz_chr_id => TO_NUMBER(p_dnz_chr_id)
2093 ,p_pre_tax_yield_from => TO_NUMBER(p_pre_tax_yield_from)
2094 ,p_pre_tax_yield_to => TO_NUMBER(p_pre_tax_yield_to)
2095 ,p_book_classification => p_book_classification
2096 ,p_tax_owner => p_tax_owner
2097 ,p_pdt_id => TO_NUMBER(p_pdt_id)
2098 ,p_start_from_date => fnd_date.canonical_to_date(p_start_from_date)
2099 ,p_start_to_date => fnd_date.canonical_to_date(p_start_to_date)
2100 ,p_end_from_date => fnd_date.canonical_to_date(p_end_from_date)
2101 ,p_end_to_date => fnd_date.canonical_to_date(p_end_to_date)
2102 ,p_asset_id => TO_NUMBER(p_asset_id)
2103 ,p_item_id1 => TO_NUMBER(p_item_id1)
2104 ,p_model_number => p_model_number
2105 ,p_manufacturer_name => p_manufacturer_name
2106 ,p_vendor_id1 => TO_NUMBER(p_vendor_id1)
2107 ,p_oec_from => TO_NUMBER(p_oec_from)
2108 ,p_oec_to => TO_NUMBER(p_oec_to)
2109 ,p_residual_percentage => TO_NUMBER(p_residual_percentage)
2110 ,p_sty_id => TO_NUMBER(p_sty_id)
2111 -- mvasudev, 11.5.10
2112 ,p_stream_type_subclass => p_stream_type_subclass
2113 -- end, mvasudev, 11.5.10
2114 ,p_streams_from_date => fnd_date.canonical_to_date(p_streams_from_date)
2115 ,p_streams_to_date => fnd_date.canonical_to_date(p_streams_to_date)
2116 ,p_action_code => p_action_code
2117 ,x_poc_uv_tbl => lx_poc_uv_tbl
2118 ,p_cust_crd_clf_code => p_cust_crd_clf_code);
2119
2120 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2121 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.CLEANUP_POOL_CONTENTS',G_UNEXPECTED_ERROR));
2122 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2123 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
2124 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.CLEANUP_POOL_CONTENTS',G_EXPECTED_ERROR));
2125 RAISE G_EXCEPTION_ERROR;
2126 END IF;
2127
2128 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(fnd_message.get_string(G_APP_NAME,G_TOTAL_ROWS_PROCESSED),l_prompt_len) || ' : ' || lx_poc_uv_tbl.COUNT);
2129 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2130
2131 IF lx_poc_uv_tbl.COUNT > 0 THEN
2132
2133
2134 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
2135 || RPAD('-',l_contract_num_len-1,'-') || ' '
2136 || RPAD('-',l_asset_num_len-1,'-') || ' '
2137 || RPAD('-',l_lessee_len-1,'-') || ' '
2138 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
2139 || RPAD('-',l_sty_name_len-1,'-') || ' '
2140 || RPAD('-',l_sty_purpose_len-1,'-') || ' '
2141 || RPAD('-',l_amount_len,'-');
2142
2143 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2144
2145 l_content := RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ROW_NUMBER),l_row_num_len-1) || ' '
2146 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_CONTRACT_NUMBER),l_contract_num_len-1) || ' '
2147
2148 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_ASSET_NUMBER),l_asset_num_len-1) || ' '
2149 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_LESSEE),l_lessee_len-1) || ' '
2150 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_SUBCLASS),l_sty_subclass_len-1) || ' '
2151 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE),l_sty_name_len-1) || ' '
2152 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_STREAM_TYPE_PURPOSE),l_sty_purpose_len-1) || ' '
2153 || RPAD(FND_MESSAGE.GET_STRING(G_APP_NAME,G_TOTAL_AMOUNT),l_amount_len);
2154
2155 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2156
2157 l_content := RPAD('-',l_row_num_len-1,'-') || ' '
2158 || RPAD('-',l_contract_num_len-1,'-') || ' '
2159 || RPAD('-',l_asset_num_len-1,'-') || ' '
2160 || RPAD('-',l_lessee_len-1,'-') || ' '
2161 || RPAD('-',l_sty_subclass_len-1,'-') || ' '
2162 || RPAD('-',l_sty_name_len-1,'-') || ' '
2163 || RPAD('-',l_sty_purpose_len-1,'-') || ' '
2164 || RPAD('-',l_amount_len,'-');
2165
2166 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2167
2168
2169 FOR l_row_count IN 1..lx_poc_uv_tbl.COUNT
2170 LOOP
2171 l_content := RPAD(l_row_count,l_row_num_len)
2172 || RPAD(lx_poc_uv_tbl(l_row_count).contract_number ,l_contract_num_len)
2173 || RPAD(lx_poc_uv_tbl(l_row_count).asset_number ,l_asset_num_len)
2174 || RPAD(lx_poc_uv_tbl(l_row_count).lessee ,l_lessee_len)
2175 || RPAD(lx_poc_uv_tbl(l_row_count).sty_subclass ,l_sty_subclass_len)
2176 || RPAD(lx_poc_uv_tbl(l_row_count).stream_type_name ,l_sty_name_len)
2177 || RPAD(lx_poc_uv_tbl(l_row_count).stream_type_purpose ,l_sty_purpose_len)
2178 || LPAD(Okl_Accounting_Util.format_amount(lx_poc_uv_tbl(l_row_count).pool_amount,p_currency_code),l_amount_len);
2179
2180 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_content);
2181
2182 END LOOP;
2183
2184 /* v115.16 , mvasudev
2185 IF p_action_code = Okl_Pool_Pvt.G_ACTION_REMOVE THEN
2186 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message('Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT','',G_PROCESS_START));
2187 Okl_Pool_Pvt.recal_tot_princ_amt(p_api_version => l_api_version
2188 ,p_init_msg_list => l_init_msg_list
2189 ,x_return_status => l_return_status
2190 ,x_msg_count => l_msg_count
2191 ,x_msg_data => l_msg_data
2192 ,x_value => l_amount
2193 ,p_pol_id => p_pol_id);
2194
2195 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT:: p_pol_id : ' || p_pol_id);
2196 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Okl_Pool_Pvt.GET_TOT_PRINCIPAL_AMT:: x_value : ' || okl_accounting_util.format_amount(l_amount,p_currency_code));
2197
2198 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2199 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_UNEXPECTED_ERROR));
2200 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2201 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
2202 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'Okl_Pool_Pvt.RECAL_TOT_PRINC_AMT',G_EXPECTED_ERROR));
2203 RAISE G_EXCEPTION_ERROR;
2204 END IF; -- status
2205
2206 END IF; -- action_code
2207 */
2208 END IF; -- count > 0
2209
2210 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_TOTAL_ROWS_PROCESSED) || ' : ' || lx_poc_uv_tbl.COUNT);
2211 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'',G_PROCESS_END));
2212
2213
2214 -- Errors
2215 -- sub head
2216 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2217 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_ERRORS);
2218 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_content);
2219 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('=',LENGTH(l_content),'='));
2220 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
2221
2222 Okl_Api.END_ACTIVITY(l_msg_count, l_msg_data);
2223 FND_FILE.PUT_LINE(FND_FILE.LOG, get_message(l_api_name,'OKL_API.END_ACTIVITY',G_CONFIRM_PROCESS));
2224
2225 -- "No Errors"
2226 l_content := FND_MESSAGE.GET_STRING(G_APP_NAME,G_NONE);
2227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_filler || l_content);
2228
2229
2230 x_retcode := 0;
2231
2232
2233 EXCEPTION
2234 WHEN G_EXCEPTION_ERROR THEN
2235 x_retcode := 2;
2236
2237 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2238 p_pkg_name => G_PKG_NAME,
2239 p_exc_name => G_EXC_NAME_ERROR,
2240 x_msg_count => l_msg_count,
2241 x_msg_data => l_msg_data,
2242 p_api_type => G_API_TYPE);
2243
2244 -- print the error message in the log file
2245
2246 GET_ERROR_MESSAGE(l_error_msg_rec);
2247 IF (l_error_msg_rec.COUNT > 0) THEN
2248 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2249 LOOP
2250 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
2251 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_msg_rec(i));
2252 END LOOP;
2253 END IF;
2254
2255 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2256 x_retcode := 2;
2257
2258 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2259 p_pkg_name => G_PKG_NAME,
2260 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2261 x_msg_count => l_msg_count,
2262 x_msg_data => l_msg_data,
2263 p_api_type => G_API_TYPE);
2264 -- print the error message in the log file
2265 GET_ERROR_MESSAGE(l_error_msg_rec);
2266 IF (l_error_msg_rec.COUNT > 0) THEN
2267 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2268 LOOP
2269 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
2270 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_msg_rec(i));
2271 END LOOP;
2272 END IF;
2273
2274 WHEN OTHERS THEN
2275 x_errbuf := SQLERRM;
2276 x_retcode := 2;
2277
2278 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2279 p_pkg_name => G_PKG_NAME,
2280 p_exc_name => G_EXC_NAME_OTHERS,
2281 x_msg_count => l_msg_count,
2282 x_msg_data => l_msg_data,
2283 p_api_type => G_API_TYPE);
2284
2285 -- print the error message in the log file
2286 GET_ERROR_MESSAGE(l_error_msg_rec);
2287 IF (l_error_msg_rec.COUNT > 0) THEN
2288 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
2289 LOOP
2290 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
2291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_error_msg_rec(i));
2292 END LOOP;
2293 END IF;
2294
2295 END cleanup_pool_contents;
2296
2297
2298 ----------------------------------------------------------------------------------
2299 -- Start of comments
2300 --
2301 -- Procedure Name : cleanup_pool_contents_ui
2302 -- Description : CleanUp pool contents based on passed in search criteria
2303 -- This is a wrapper procedure for concurrent program call from jsp/UI
2304 -- Business Rules :
2305 -- Parameters :
2306 -- Version : 1.0
2307 -- End of comments
2308 ----------------------------------------------------------------------------------
2309
2310 PROCEDURE cleanup_pool_contents_ui(
2311 p_api_version IN NUMBER
2312 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2313 ,x_return_status OUT nocopy VARCHAR2
2314 ,x_msg_count OUT nocopy NUMBER
2315 ,x_msg_data OUT nocopy VARCHAR2
2316 ,x_request_id OUT nocopy NUMBER
2317 ,p_polsrch_rec IN polsrch_rec_type
2318 -- mvasudev, 11.5.10
2319 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
2320 -- end. mvaudev, 11.5.10
2321 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2322 ,p_action_code IN VARCHAR2)
2323 IS
2324 l_api_name VARCHAR2(30) := 'cleanup_pool_contents_ui';
2325 l_api_version NUMBER := 1.0;
2326 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2327
2328 BEGIN
2329 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2330 p_pkg_name => G_PKG_NAME,
2331 p_init_msg_list => p_init_msg_list,
2332 l_api_version => l_api_version,
2333 p_api_version => p_api_version,
2334 p_api_type => G_API_TYPE,
2335 x_return_status => l_return_status);
2336
2337 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2338 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2339 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2340 RAISE G_EXCEPTION_ERROR;
2341 END IF;
2342
2343 -- Bug#2838721, mvasudev, 03/11/2003
2344 --call concurrent program
2345 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
2346
2347 x_request_id := Fnd_Request.SUBMIT_REQUEST(
2348 application => 'OKL'
2349 ,program => 'OKL_CLEANUP_POOL'
2350 ,argument1 => TO_CHAR(p_polsrch_rec.pol_id)
2351 ,argument2 => p_polsrch_rec.currency_code
2352 ,argument3 => p_multi_org
2353 ,argument4 => TO_CHAR(p_polsrch_rec.cust_object1_id1)
2354 ,argument5 => p_polsrch_rec.sic_code
2355 ,argument6 => TO_CHAR(p_polsrch_rec.dnz_chr_id)
2356 ,argument7 => TO_CHAR(p_polsrch_rec.pre_tax_yield_from)
2357 ,argument8 => TO_CHAR(p_polsrch_rec.pre_tax_yield_to)
2358 ,argument9 => p_polsrch_rec.book_classification
2359 ,argument10 => p_polsrch_rec.tax_owner
2360 ,argument11 => TO_CHAR(p_polsrch_rec.pdt_id)
2361 ,argument12 => TO_CHAR(p_polsrch_rec.start_from_date)
2362 ,argument13 => TO_CHAR(p_polsrch_rec.start_to_date)
2363 ,argument14 => TO_CHAR(p_polsrch_rec.end_from_date)
2364 ,argument15 => TO_CHAR(p_polsrch_rec.end_to_date)
2365 ,argument16 => TO_CHAR(p_polsrch_rec.asset_id)
2366 ,argument17 => TO_CHAR(p_polsrch_rec.item_id1)
2367 ,argument18 => p_polsrch_rec.model_number
2368 ,argument19 => p_polsrch_rec.manufacturer_name
2369 ,argument20 => TO_CHAR(p_polsrch_rec.vendor_id1)
2370 ,argument21 => TO_CHAR(p_polsrch_rec.oec_from)
2371 ,argument22 => TO_CHAR(p_polsrch_rec.oec_to)
2372 ,argument23 => TO_CHAR(p_polsrch_rec.residual_percentage)
2373 ,argument24 => TO_CHAR(p_polsrch_rec.sty_id)
2374 ,argument25 => TO_CHAR(p_polsrch_rec.streams_from_date)
2375 ,argument26 => TO_CHAR(p_polsrch_rec.streams_to_date)
2376 ,argument27 => p_action_code
2377 ,argument28 => p_stream_type_subclass
2378 ,argument29 => p_polsrch_rec.cust_crd_clf_code);
2379
2380 -- Added these validations to check to see if the request has been submitted successfully.
2381 IF x_request_id = 0 THEN
2382
2383 OKL_API.set_message(p_app_name => G_APP_NAME,
2384 p_msg_name => 'OKL_CONC_REQ_ERROR',
2385 p_token1 => 'PROG_NAME',
2386 p_token1_value => 'OKL CleanUp Pool CONTENTS',
2387 p_token2 => 'REQUEST_ID',
2388 p_token2_value => x_request_id);
2389
2390 RAISE G_EXCEPTION_ERROR;
2391 END IF;
2392
2393 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2394
2395 x_return_status := l_return_status;
2396
2397 EXCEPTION
2398 WHEN G_EXCEPTION_ERROR THEN
2399
2400 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2401 p_pkg_name => G_PKG_NAME,
2402 p_exc_name => G_EXC_NAME_ERROR,
2403 x_msg_count => x_msg_count,
2404 x_msg_data => x_msg_data,
2405 p_api_type => G_API_TYPE);
2406 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2407
2408 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2409 p_pkg_name => G_PKG_NAME,
2410 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2411 x_msg_count => x_msg_count,
2412 x_msg_data => x_msg_data,
2413 p_api_type => G_API_TYPE);
2414 WHEN OTHERS THEN
2415
2416 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2417 p_pkg_name => G_PKG_NAME,
2418 p_exc_name => G_EXC_NAME_OTHERS,
2419 x_msg_count => x_msg_count,
2420 x_msg_data => x_msg_data,
2421 p_api_type => G_API_TYPE);
2422
2423
2424 END cleanup_pool_contents_ui;
2425
2426 END Okl_Poolconc_Pvt;