[Home] [Help]
PACKAGE: APPS.OKL_POOLCONC_PVT
Source
1 PACKAGE Okl_Poolconc_Pvt AS
2 /* $Header: OKLRSZCS.pls 120.5 2008/01/04 08:50:23 dpsingh ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 -- Record type which holds the account generator rule lines.
7 G_FND_APP CONSTANT VARCHAR2(200) := Okl_Api.G_FND_APP;
8 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
9 G_EXPECTED_ERROR CONSTANT VARCHAR2(28) := 'OKL_CONTRACTS_EXPECTED_ERROR';
10 G_CONFIRM_PROCESS CONSTANT VARCHAR2(19) := 'OKL_CONFIRM_PROCESS';
11 G_PROCESS_START CONSTANT VARCHAR2(17) := 'OKL_PROCESS_START';
12 G_PROCESS_END CONSTANT VARCHAR2(15) := 'OKL_PROCESS_END';
13 G_TOTAL_ROWS_PROCESSED CONSTANT VARCHAR2(24) := 'OKL_TOTAL_ROWS_PROCESSED';
14 G_RECONCILE_ERROR CONSTANT VARCHAR2(20) := 'OKL_RECONCILE_ERROR';
15
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLerrm';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLcode';
18
19 ----------------------------------------------------------------------------
20 -- GLOBAL VARIABLES
21 ----------------------------------------------------------------------------
22 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_POOLCONC_PVT';
23 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
24
25 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
26 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
27 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
28 G_EXCEPTION_ERROR EXCEPTION;
29 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
30
31 -- list of request statuses
32 G_EXC_NAME_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_ERROR';
33 G_EXC_NAME_UNEXP_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_UNEXP_ERROR';
34 G_EXC_NAME_OTHERS CONSTANT VARCHAR2(6) := 'OTHERS';
35 G_API_TYPE CONSTANT VARCHAR(4) := '_PVT';
36
37 G_OKL_TITLE CONSTANT VARCHAR2(9) := 'OKL_TITLE';
38 G_POOL_CLEANUP_HEAD CONSTANT VARCHAR2(20) := 'OKL_POOL_CLEANUP';
39 G_POOL_CLEANUP_REPORT CONSTANT VARCHAR2(30) := 'OKL_POOL_CLEANUP_REPORT';
40 G_POOL_CLEANUP_REMOVE CONSTANT VARCHAR2(30) := 'OKL_POOL_CLEANUP_REMOVE';
41 G_SET_OF_BOOKS CONSTANT VARCHAR2(30) := 'OKL_SET_OF_BOOKS';
42 G_OPERATING_UNIT CONSTANT VARCHAR2(30) := 'OKL_OPERATING_UNIT';
43
44 G_POOL_NUMBER CONSTANT VARCHAR2(15) := 'OKL_POOL_NUMBER';
45 G_POOL_STATUS CONSTANT VARCHAR2(15) := 'OKL_POOL_STATUS';
46 G_DATE_CREATED CONSTANT VARCHAR2(30) := 'OKL_CREATION_DATE';
47 G_LAST_UPDATE_DATE CONSTANT VARCHAR2(30) := 'OKL_LAST_UPDATE_DATE';
48 G_DATE_LAST_RECONCILED CONSTANT VARCHAR2(30) := 'OKL_DATE_LAST_RECONCILED';
49 G_VALUE_OF_STREAMS CONSTANT VARCHAR2(30) := 'OKL_VALUE_OF_STREAMS';
50 G_TOTAL_ASSET_NET_INVESTMENT CONSTANT VARCHAR2(30) := 'OKL_TOTAL_ASSET_NET_INVESTMENT';
51 G_DATE_LAST_CALCULATED CONSTANT VARCHAR2(30) := 'OKL_DATE_LAST_CALCULATED';
52
53 G_CURRENCY CONSTANT VARCHAR2(20) := 'OKL_AGN_RPT_CURRENCY';
54 G_PROGRAM_RUN_DATE CONSTANT VARCHAR2(30) := 'OKL_PROGRAM_RUN_DATE';
55 G_ROW_NUMBER CONSTANT VARCHAR2(14) := 'OKL_ROW_NUMBER';
56 G_CONTRACT_NUMBER CONSTANT VARCHAR2(25) := 'OKL_GLP_RPT_CTR_NUM_TITLE';
57 G_ASSET_NUMBER CONSTANT VARCHAR2(16) := 'OKL_ASSET_NUMBER';
58 G_LESSEE CONSTANT VARCHAR2(10) := 'OKL_LESSEE';
59 G_STREAM_TYPE CONSTANT VARCHAR2(20) := 'OKL_MGP_REP_STY_TYPE';
60 -- mvasudev, 09/28/2004, Bug#3909240
61 G_STREAM_TYPE_PURPOSE CONSTANT VARCHAR2(30) := 'OKL_STREAM_TYPE_PURPOSE';
62 G_TOTAL_AMOUNT CONSTANT VARCHAR2(16) := 'OKL_TOTAL_AMOUNT';
63
64 G_SEARCH_PARAMETERS CONSTANT VARCHAR2(30) := 'OKL_SEARCH_PARAMETERS' ;
65 G_RESULTS CONSTANT VARCHAR2(15) := 'OKL_RESULTS';
66 G_ERRORS CONSTANT VARCHAR2(30) := 'OKL_POOL_CLEANUP_ERRORS';
67
68 -- Bug#2843163, mvasudev, 03/14/2003
69 G_CUSTOMERS CONSTANT VARCHAR2(30) := 'OKL_CUSTOMERS';
70 G_CONTRACTS CONSTANT VARCHAR2(30) := 'OKL_CONTRACTS';
71 G_STREAMS CONSTANT VARCHAR2(30) := 'OKL_STREAMS';
72 G_NONE CONSTANT VARCHAR2(30) := 'OKL_NONE';
73
74 G_CUSTOMER CONSTANT VARCHAR2(30) := 'OKL_CUSTOMER';
75 G_CUSTOMER_INDUSTRY_CODE CONSTANT VARCHAR2(30) := 'OKL_CUSTOMER_INDUSTRY_CODE';
76 G_PTY_FROM CONSTANT VARCHAR2(30) := 'OKL_PRE_TAX_YIELD_FROM';
77 G_PTY_TO CONSTANT VARCHAR2(30) := 'OKL_PRE_TAX_YIELD_TO';
78 G_BOOK_CLASS CONSTANT VARCHAR2(30) := 'OKL_CONTRACT_DEAL_TYPE';
79 G_TAX_OWNER CONSTANT VARCHAR2(30) := 'OKL_TAX_OWNER';
80 G_START_FROM_DATE CONSTANT VARCHAR2(30) := 'OKL_START_FROM_DATE';
81 G_START_TO_DATE CONSTANT VARCHAR2(30) := 'OKL_START_TO_DATE';
82 G_END_FROM_DATE CONSTANT VARCHAR2(30) := 'OKL_END_FROM_DATE';
83 G_END_TO_DATE CONSTANT VARCHAR2(30) := 'OKL_END_TO_DATE';
84 G_PRODUCT CONSTANT VARCHAR2(30) := 'OKL_PRODUCT';
85 G_ITEM_NUMBER CONSTANT VARCHAR2(30) := 'OKL_ITEM_NUMBER';
86 G_MODEL_NUMBER CONSTANT VARCHAR2(30) := 'OKL_MODEL_NUMBER';
87 G_MANUFACTURER CONSTANT VARCHAR2(30) := 'OKL_MANUFACTURER_NAME';
88 G_VENDOR CONSTANT VARCHAR2(30) := 'OKL_VENDOR';
89 G_ASSET_COST_FROM CONSTANT VARCHAR2(30) := 'OKL_ASSET_COST_FROM';
90 G_ASSET_COST_TO CONSTANT VARCHAR2(30) := 'OKL_ASSET_COST_TO';
91 G_RESIDUAL_PERCENTAGE CONSTANT VARCHAR2(30) := 'OKL_RESIDUAL_PERCENTAGE';
92 G_STREAMS_FROM_DATE CONSTANT VARCHAR2(30) := 'OKL_STREAMS_FROM_DATE';
93 G_STREAMS_TO_DATE CONSTANT VARCHAR2(30) := 'OKL_STREAMS_TO_DATE';
94
95
96 G_STREAM_TYPE_SUBCLASS CONSTANT VARCHAR2(25) := 'OKL_STREAM_TYPE_SUBCLASS';
97 G_CUST_CRDT_CLASSIFICATION CONSTANT VARCHAR2(30) := 'OKL_CUST_CRDT_CLASSIFICATION';
98 G_FINAL_DATE CONSTANT DATE := TO_DATE('1','j');
99 G_DATE_FORMAT_MASK VARCHAR2(30) := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
100
101 TYPE error_message_type IS TABLE OF VARCHAR2(2000)
102 INDEX BY BINARY_INTEGER;
103
104 SUBTYPE polsrch_rec_type IS Okl_Pool_Pvt.polsrch_rec_type;
105 SUBTYPE pocv_rec_type IS Okl_Pool_Pvt.pocv_rec_type;
106 SUBTYPE pocv_tbl_type IS Okl_Pool_Pvt.pocv_tbl_type;
107 SUBTYPE poc_uv_rec_type IS Okl_Pool_Pvt.poc_uv_rec_type;
108 SUBTYPE poc_uv_tbl_type IS Okl_Pool_Pvt.poc_uv_tbl_type;
109
110 ----------------------------------------------------------------------------
111 -- Data Structures
112 ----------------------------------------------------------------------------
113 ----------------------------------------------------------------------------
114 -- Global Exception
115 ----------------------------------------------------------------------------
116 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
117
118 ----------------------------------------------------------------------------
119 -- Procedures and Functions
120 ------------------------------------------------------------------------------
121 ----------------------------------------------------------------------------------
122 -- Start of comments
123 --
124 -- Procedure Name : add_pool_contents
125 -- Description : creates pool contents based on passed in search criteria
126 -- This is a wrapper procedure for concurrent program to call private API
127 -- Business Rules :
128 -- Parameters :
129 -- Version : 1.0
130 -- End of comments
131 ----------------------------------------------------------------------------------
132
133 PROCEDURE add_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
134 ,x_retcode OUT NOCOPY NUMBER
135 ,p_pol_id IN VARCHAR2
136 ,p_currency_code IN VARCHAR2
137 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
138 ,p_cust_object1_id1 IN VARCHAR2 DEFAULT NULL -- customer_id
139 ,p_sic_code IN VARCHAR2 DEFAULT NULL
140 ,p_dnz_chr_id IN VARCHAR2 DEFAULT NULL -- dnz_chr_id
141 ,p_pre_tax_yield_from IN VARCHAR2 DEFAULT NULL
142 ,p_pre_tax_yield_to IN VARCHAR2 DEFAULT NULL
143 ,p_book_classification IN VARCHAR2 DEFAULT NULL
144 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
145 ,p_pdt_id IN VARCHAR2 DEFAULT NULL
146 ,p_start_from_date IN VARCHAR2 DEFAULT NULL
147 ,p_start_to_date IN VARCHAR2 DEFAULT NULL
148 ,p_end_from_date IN VARCHAR2 DEFAULT NULL
149 ,p_end_to_date IN VARCHAR2 DEFAULT NULL
150 ,p_asset_id IN VARCHAR2 DEFAULT NULL
151 ,p_item_id1 IN VARCHAR2 DEFAULT NULL
152 ,p_model_number IN VARCHAR2 DEFAULT NULL
153 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
154 ,p_vendor_id1 IN VARCHAR2 DEFAULT NULL
155 ,p_oec_from IN VARCHAR2 DEFAULT NULL
156 ,p_oec_to IN VARCHAR2 DEFAULT NULL
157 ,p_residual_percentage IN VARCHAR2 DEFAULT NULL
158 ,p_sty_id1 IN VARCHAR2 DEFAULT NULL
159 ,p_sty_id2 IN VARCHAR2 DEFAULT NULL
160 ,p_streams_from_date IN VARCHAR2 DEFAULT NULL
161 ,p_streams_to_date IN VARCHAR2 DEFAULT NULL
162 ,p_stream_element_payment_freq IN VARCHAR2 DEFAULT NULL
163 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
164 --Bug # 6691554 ssdeshpa Start
165 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL
166 --Bug # 6691554 ssdeshpa End
167 );
168
169 ----------------------------------------------------------------------------------
170 -- Start of comments
171 --
172 -- Procedure Name : add_pool_contents_ui
173 -- Description : creates pool contents based on passed in search criteria
174 -- This is a wrapper procedure for concurrent program call from jsp/UI
175 -- Business Rules :
176 -- Parameters :
177 -- Version : 1.0
178 -- End of comments
179 ----------------------------------------------------------------------------------
180
181 PROCEDURE add_pool_contents_ui(
182 p_api_version IN NUMBER
183 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
184 ,x_return_status OUT nocopy VARCHAR2
185 ,x_msg_count OUT nocopy NUMBER
186 ,x_msg_data OUT nocopy VARCHAR2
187 ,x_request_id OUT nocopy NUMBER
188 ,p_polsrch_rec IN polsrch_rec_type
189 ,p_sty_id1 IN NUMBER DEFAULT NULL
190 ,p_sty_id2 IN NUMBER DEFAULT NULL
191 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
192 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE);
193
194 ----------------------------------------------------------------------------------
195 -- Start of comments
196 --
197 -- Procedure Name : recal_tot_princ_amt
198 -- Description : update asset principal amount from pool contents by okl_pools.id to okl_pools.TOTAL_PRINCIPAL_AMOUNT
199 -- This is a wrapper procedure for concurrent program to call private API
200 -- Business Rules :
201 -- Parameters :
202 -- Version : 1.0
203 -- End of comments
204 ----------------------------------------------------------------------------------
205
206 PROCEDURE recal_tot_princ_amt(x_errbuf OUT NOCOPY VARCHAR2
207 ,x_retcode OUT NOCOPY NUMBER
208 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL);
209
210 ----------------------------------------------------------------------------------
211 -- Start of comments
212 --
213 -- Procedure Name : recal_tot_princ_amt_ui
214 -- Description : update asset principal amount from pool contents by okl_pools.id to okl_pools.TOTAL_PRINCIPAL_AMOUNT
215 -- This is a wrapper procedure for concurrent program call from jsp/UI
216 -- Business Rules :
217 -- Parameters :
218 -- Version : 1.0
219 -- End of comments
220 ----------------------------------------------------------------------------------
221
222 PROCEDURE recal_tot_princ_amt_ui(
223 p_api_version IN NUMBER
224 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
225 ,x_return_status OUT NOCOPY VARCHAR2
226 ,x_msg_count OUT NOCOPY NUMBER
227 ,x_msg_data OUT NOCOPY VARCHAR2
228 -- concurent out parameter
229 ,x_request_id OUT NOCOPY NUMBER
230 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL);
231
232 ----------------------------------------------------------------------------------
233 -- Start of comments
234 --
235 -- Procedure Name : reconcile_pool_contents
236 -- Description : Reconcile Pool Contents
237 -- This is a wrapper procedure for concurrent program to call private API
238 -- Business Rules :
239 -- Parameters :
240 -- Version : 1.0
241 -- End of comments
242 ----------------------------------------------------------------------------------
243
244 PROCEDURE reconcile_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
245 ,x_retcode OUT NOCOPY NUMBER
246 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL);
247
248 ----------------------------------------------------------------------------------
249
250 ----------------------------------------------------------------------------------
251 -- Start of comments
252 --
253 -- Procedure Name : recon_pnd_pool_con
254 -- Description : Reconcile Pending Pool Contents
255 -- This is a wrapper procedure for concurrent program to call private API
256 -- Business Rules :
257 -- Parameters :
258 -- Version : 1.0
259 -- End of comments
260 ----------------------------------------------------------------------------------
261
262 PROCEDURE recon_pnd_pool_con(x_errbuf OUT NOCOPY VARCHAR2
263 ,x_retcode OUT NOCOPY NUMBER
264 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL);
265
266 ----------------------------------------------------------------------------------
267 -- Start of comments
268 --
269 -- Procedure Name : reconcile_pool_contents
270 -- Description : Reconcile Pool Contents - to be called from UI
271 -- This is a wrapper procedure for concurrent program call from jsp/UI
272 -- Business Rules :
273 -- Parameters :
274 -- Version : 1.0
275 -- End of comments
276 ----------------------------------------------------------------------------------
277
278 PROCEDURE reconcile_pool_contents_ui(
279 p_api_version IN NUMBER
280 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
281 ,p_pool_number IN okl_pools.POOL_NUMBER%TYPE DEFAULT NULL
282 ,x_return_status OUT NOCOPY VARCHAR2
283 ,x_msg_count OUT NOCOPY NUMBER
284 ,x_msg_data OUT NOCOPY VARCHAR2
285 ,x_request_id OUT NOCOPY NUMBER);
286
287 ----------------------------------------------------------------------------------
288 -- Start of comments
289 -- mvasudev
290 -- Procedure Name : cleanup_pool_contents
291 -- Description : CleanUp Pool Contents based on passed in search criteria
292 -- This is a wrapper procedure for concurrent program to call private API
293 -- Business Rules :
294 -- Parameters :
295 -- Version : 1.0
296 -- End of comments
297 ----------------------------------------------------------------------------------
298
299 PROCEDURE cleanup_pool_contents(x_errbuf OUT NOCOPY VARCHAR2
300 ,x_retcode OUT NOCOPY NUMBER
301 ,p_pol_id IN VARCHAR2
302 ,p_currency_code IN VARCHAR2
303 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
304 ,p_cust_object1_id1 IN VARCHAR2 DEFAULT NULL -- customer_id
305 ,p_sic_code IN VARCHAR2 DEFAULT NULL
306 ,p_dnz_chr_id IN VARCHAR2 DEFAULT NULL -- dnz_chr_id
307 ,p_pre_tax_yield_from IN VARCHAR2 DEFAULT NULL
308 ,p_pre_tax_yield_to IN VARCHAR2 DEFAULT NULL
309 ,p_book_classification IN VARCHAR2 DEFAULT NULL
310 ,p_tax_owner IN VARCHAR2 DEFAULT NULL
311 ,p_pdt_id IN VARCHAR2 DEFAULT NULL
312 ,p_start_from_date IN VARCHAR2 DEFAULT NULL
313 ,p_start_to_date IN VARCHAR2 DEFAULT NULL
314 ,p_end_from_date IN VARCHAR2 DEFAULT NULL
315 ,p_end_to_date IN VARCHAR2 DEFAULT NULL
316 ,p_asset_id IN VARCHAR2 DEFAULT NULL
317 ,p_item_id1 IN VARCHAR2 DEFAULT NULL
318 ,p_model_number IN VARCHAR2 DEFAULT NULL
319 ,p_manufacturer_name IN VARCHAR2 DEFAULT NULL
320 ,p_vendor_id1 IN VARCHAR2 DEFAULT NULL
321 ,p_oec_from IN VARCHAR2 DEFAULT NULL
322 ,p_oec_to IN VARCHAR2 DEFAULT NULL
323 ,p_residual_percentage IN VARCHAR2 DEFAULT NULL
324 ,p_sty_id IN VARCHAR2 DEFAULT NULL
325 ,p_streams_from_date IN VARCHAR2 DEFAULT NULL
326 ,p_streams_to_date IN VARCHAR2 DEFAULT NULL
327 ,p_action_code IN VARCHAR2
328 -- mvasudev, 11.5.10
329 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
330 -- end, mvasudev, 11.5.10
331 --Bug # 6691554 ssdeshpa Start
332 ,p_cust_crd_clf_code IN VARCHAR2 DEFAULT NULL
333 --Bug # 6691554 ssdeshpa End
334 );
335
336
337 ----------------------------------------------------------------------------------
338 -- Start of comments
339 --
340 -- Procedure Name : cleanup_pool_contents_ui
341 -- Description : CleanUp pool contents based on passed in search criteria
342 -- This is a wrapper procedure for concurrent program call from jsp/UI
343 -- Business Rules :
344 -- Parameters :
345 -- Version : 1.0
346 -- End of comments
347 ----------------------------------------------------------------------------------
348
349 PROCEDURE cleanup_pool_contents_ui(
350 p_api_version IN NUMBER
351 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
352 ,x_return_status OUT nocopy VARCHAR2
353 ,x_msg_count OUT nocopy NUMBER
354 ,x_msg_data OUT nocopy VARCHAR2
355 ,x_request_id OUT nocopy NUMBER
356 ,p_polsrch_rec IN polsrch_rec_type
357 -- mvasudev, 11.5.10
358 ,p_stream_type_subclass IN VARCHAR2 DEFAULT NULL
359 -- end, mvasudev, 11.5.10
360 ,p_multi_org IN VARCHAR2 DEFAULT OKL_API.G_FALSE
361 ,p_action_code IN VARCHAR2);
362
363
364 END Okl_Poolconc_Pvt;