1 PACKAGE Okl_Streams_Util AUTHID CURRENT_USER AS
2 /* $Header: OKLRSULS.pls 120.12.12020000.2 2013/02/22 08:47:35 bkatraga ship $ */
3
4 SUBTYPE FILE_TYPE IS UTL_FILE.FILE_TYPE;
5 TYPE LOG_MSG_TBL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
6
7 ---------------------------------------------------------------------------
8 -- GLOBAL VARIABLES
9 ---------------------------------------------------------------------------
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLERRM';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLCODE';
13 G_APP_NAME CONSTANT VARCHAR2(3) := Okl_Api.G_APP_NAME;
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKL_STREAMS_UTIL';
15
16
17 G_MISS_NUM CONSTANT NUMBER := Okl_Api.G_MISS_NUM;
18 G_MISS_CHAR CONSTANT VARCHAR2(1) := Okl_Api.G_MISS_CHAR;
19 G_MISS_DATE CONSTANT DATE := Okl_Api.G_MISS_DATE;
20 G_TRUE CONSTANT VARCHAR2(1) := Okl_Api.G_TRUE;
21 G_FALSE CONSTANT VARCHAR2(1) := Okl_Api.G_FALSE;
22
23 G_EXC_NAME_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_ERROR';
24 G_EXC_NAME_UNEXP_ERROR CONSTANT VARCHAR2(50) := 'OKL_API.G_RET_STS_UNEXP_ERROR';
25 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
26 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := Okl_Api.G_RET_STS_ERROR;
27 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := Okl_Api.G_RET_STS_UNEXP_ERROR;
28
29 G_EXCEPTION_HALT_PROCESSING EXCEPTION;
30 G_EXCEPTION_ERROR EXCEPTION;
31 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
32
33 G_LOG_DIR CONSTANT VARCHAR2(30) := 'ECX_UTL_LOG_DIR';
34 -- Start for Bug#2807737 changes
35 ---------------------------------------------------------------------------
36 -- GLOBAL PL/SQL table types for Bulk insert
37 ---------------------------------------------------------------------------
38 TYPE ClobTabTyp IS TABLE OF CLOB
39 INDEX BY BINARY_INTEGER;
40
41 TYPE DateTabTyp IS TABLE OF DATE
42 INDEX BY BINARY_INTEGER;
43
44 TYPE NumberTabTyp IS TABLE OF NUMBER
45 INDEX BY BINARY_INTEGER;
46
47 TYPE Number15TabTyp IS TABLE OF NUMBER(15)
48 INDEX BY BINARY_INTEGER;
49
50 --Added by kthiruva on 12-May-2005 for Streams Perf
51 TYPE Number9TabTyp IS TABLE OF NUMBER(9)
52 INDEX BY BINARY_INTEGER;
53
54 --Added by gboomina on 14-Oct-2005 for Accruals Performance Improvement
55 --Bug 4662173 - Start of Changes
56 TYPE Number15NoPrecisionTabTyp IS TABLE OF NUMBER(15,0)
57 INDEX BY BINARY_INTEGER;
58 --Bug 4662173 - End of Changes
59
60 TYPE Var10TabTyp IS TABLE OF VARCHAR2(10)
61 INDEX BY BINARY_INTEGER;
62
63 TYPE Var12TabTyp IS TABLE OF VARCHAR2(12)
64 INDEX BY BINARY_INTEGER;
65
66 TYPE Var120TabTyp IS TABLE OF VARCHAR2(120)
67 INDEX BY BINARY_INTEGER;
68
69 TYPE Var15TabTyp IS TABLE OF VARCHAR2(15)
70 INDEX BY BINARY_INTEGER;
71
72 TYPE Var150TabTyp IS TABLE OF VARCHAR2(150)
73 INDEX BY BINARY_INTEGER;
74
75 TYPE Var1995TabTyp IS TABLE OF VARCHAR2(1995)
76 INDEX BY BINARY_INTEGER;
77
78 TYPE Var24TabTyp IS TABLE OF VARCHAR2(24)
79 INDEX BY BINARY_INTEGER;
80
81 TYPE Var200TabTyp IS TABLE OF VARCHAR2(200)
82 INDEX BY BINARY_INTEGER;
83
84 TYPE Var240TabTyp IS TABLE OF VARCHAR2(240)
85 INDEX BY BINARY_INTEGER;
86
87 TYPE Var3TabTyp IS TABLE OF VARCHAR2(3)
88 INDEX BY BINARY_INTEGER;
89
90 TYPE Var30TabTyp IS TABLE OF VARCHAR2(30)
91 INDEX BY BINARY_INTEGER;
92
93 TYPE Var300TabTyp IS TABLE OF VARCHAR2(300)
94 INDEX BY BINARY_INTEGER;
95
96 TYPE Var40TabTyp IS TABLE OF VARCHAR2(40)
97 INDEX BY BINARY_INTEGER;
98
99 TYPE Var450TabTyp IS TABLE OF VARCHAR2(450)
100 INDEX BY BINARY_INTEGER;
101
102 TYPE Var50TabTyp IS TABLE OF VARCHAR2(50)
103 INDEX BY BINARY_INTEGER;
104
105 TYPE Var600TabTyp IS TABLE OF VARCHAR2(600)
106 INDEX BY BINARY_INTEGER;
107
108 TYPE Var75TabTyp IS TABLE OF VARCHAR2(75)
109 INDEX BY BINARY_INTEGER;
110
111 TYPE Var90TabTyp IS TABLE OF VARCHAR2(90)
112 INDEX BY BINARY_INTEGER;
113
114 --Added by gboomina for Accruals Performance Improvement on the 14-Oct-2005
115 --Bug 4662173 - Start of Changes
116 TYPE Var45TabTyp IS TABLE OF VARCHAR2(45)
117 INDEX BY BINARY_INTEGER;
118 --Bug 4662173 - End of Changes
119
120
121 TYPE okl_strm_type_id_tbl_type IS TABLE OF okl_strm_type_b.ID%TYPE
122 INDEX BY BINARY_INTEGER;
123 ---------------------------------------------------------------------------
124 -- End for Bug#2807737 changes
125 -- PRCODURE LOG_MESSAGE
126 ---------------------------------------------------------------------------
127 PROCEDURE LOG_MESSAGE(p_msg_name IN VARCHAR2,
128 p_translate IN VARCHAR2 DEFAULT G_TRUE,
129 p_file_name IN VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2);
131
132 PROCEDURE LOG_MESSAGE(p_msgs_tbl IN log_msg_tbl,
133 p_translate IN VARCHAR2 DEFAULT G_TRUE,
134 p_file_name IN VARCHAR2,
135 x_return_status OUT NOCOPY VARCHAR2);
136
137 PROCEDURE LOG_MESSAGE(p_msg_count IN NUMBER,
138 p_file_name IN VARCHAR2,
139 x_return_status OUT NOCOPY VARCHAR2
140 );
141
142 PROCEDURE GET_FND_PROFILE_VALUE(p_name IN VARCHAR2,
143 x_value OUT NOCOPY VARCHAR2);
144
145 -- BAKUCHIB Bug 2835092 start
146 --------------------------------------------------------------------------------
147 -- Start of Commnets
148 -- Badrinath Kuchibholta
149 -- Procedure Name : round_streams_amount
150 -- Description : Returns PL/SQL table of record rounded amounts
151 -- of OKL_STRM_ELEMENTS type
152 -- Business Rules : We sum the amounts given as I/P PL/SQL table first.
153 -- And then we round the amounts using existing
154 -- rounding rule and then sum them these up.
155 -- If we find a difference between rounded amount
156 -- and non-rounded amount then based on already existing
157 -- rule we do adjustment to the first amount or
158 -- last amount or the High value amount of the PL/SQL
159 -- table of records.We then give the rounded values
160 -- thru O/P PL/SQL table of records.
161 -- Parameters : P_chr_id,
162 -- p_selv_tbl of OKL_STRM_ELEMENTS type
163 -- x_selv_tbl of OKL_STRM_ELEMENTS type
164 -- Version : 1.0
165 -- History : BAKUCHIB 31-JUL-2003 - 2835092 created
166 -- End of Commnets
167 --------------------------------------------------------------------------------
168 FUNCTION Round_Streams_Amount(p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2,
172 p_chr_id IN okc_k_headers_b.id%TYPE,
173 p_selv_tbl IN Okl_Streams_Pub.selv_tbl_type,
174 x_selv_tbl OUT NOCOPY Okl_Streams_Pub.selv_tbl_type)
175 RETURN VARCHAR2;
176 -- BAKUCHIB Bug 2835092 End
177
178 /*
179 -- Returns the primary stream type id for primary stream purpose for a contract
180 */
181
182 PROCEDURE get_primary_stream_type
183 (
184 p_khr_id IN okl_k_headers_full_v.id%TYPE,
185 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_primary_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
188 );
189
190 /*
191 -- Returns the primary stream type id for primary stream purpose for a contract
192 --for reporting stream
193 */
194
195 PROCEDURE get_primary_stream_type_rep
196 (
197 p_khr_id IN okl_k_headers_full_v.id%TYPE,
198 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_primary_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
201 );
202
203 /*
204 -- Returns the dep stream type id for dep stream purpose for a contract
205 */
206
207
208 PROCEDURE get_dependent_stream_type
209 (
210 p_khr_id IN okl_k_headers_full_v.id%TYPE,
211 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
212 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
215 );
216
217 PROCEDURE get_dependent_stream_type
218 (
219 p_khr_id IN okl_k_headers_full_v.id%TYPE,
220 p_primary_sty_id IN okl_strm_type_b.ID%TYPE,
221 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
224 );
225
226 -- Added for bug 6326479
227 PROCEDURE get_dependent_stream_type
228 (
229 p_khr_id IN okl_k_headers_full_v.id%TYPE,
230 p_product_id IN okl_k_headers_full_v.pdt_id%TYPE,
231 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
232 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
235 );
236
237 PROCEDURE get_dependent_stream_type_rep
238 (
239 p_khr_id IN okl_k_headers_full_v.id%TYPE,
240 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
241 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
244 );
245
246 PROCEDURE get_dependent_stream_type_rep
247 (
248 p_khr_id IN okl_k_headers_full_v.id%TYPE,
249 p_primary_sty_id IN okl_strm_type_b.ID%TYPE,
250 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
253 );
254
255 -- Added for bug 6326479
256 PROCEDURE get_dependent_stream_type_rep
257 (
258 p_khr_id IN okl_k_headers_full_v.id%TYPE,
259 p_product_id IN okl_k_headers_full_v.pdt_id%TYPE,
260 p_primary_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
261 p_dependent_sty_purpose IN okl_strm_type_b.STREAM_TYPE_PURPOSE%TYPE,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_dependent_sty_id OUT NOCOPY okl_strm_type_b.ID%TYPE
264 );
265
266 FUNCTION strm_tmpt_contains_strm_type
267 (
268 p_khr_id IN okl_k_headers_full_v.id%TYPE,
269 p_sty_id IN okl_strm_type_b.ID%TYPE
270 )
271 RETURN VARCHAR2;
272
273 -- Gets the status of the stream generation request for external generator
274 PROCEDURE get_transaction_status
275 (
276 p_transaction_number IN okl_stream_interfaces.transaction_number%TYPE,
277 x_transaction_status OUT NOCOPY okl_stream_interfaces.sis_code%TYPE,
278 x_logfile_name OUT NOCOPY okl_stream_interfaces.log_file%TYPE,
279 x_return_status OUT NOCOPY VARCHAR2
280 );
281
282
283 -- Added by Santonyr
284 --------------------------------------------------------------------------------
285 -- Start of Commnets
286 -- Procedure Name : get_pricing_engine
287 -- Description : Returns pricing engine for a contract based on the product
288 -- stream template
289 -- Business Rules :
290 -- Parameters : p_khr_id,
291 -- Version : 1.0
292 -- History : santonyr 10-Dec-2004 - created
293 -- End of Commnets
294 --------------------------------------------------------------------------------
295
296 FUNCTION get_pricing_engine(p_khr_id IN okl_k_headers.id%TYPE)
297 RETURN VARCHAR2;
298
299
300 -- Added by Santonyr
301 --------------------------------------------------------------------------------
302 -- Start of Commnets
303 -- Procedure Name : get_pricing_engine
304 -- Description : Returns pricing engine for a contract based on the product
305 -- stream template
306 -- Business Rules :
307 -- Parameters : p_khr_id,
308 -- Version : 1.0
309 -- History : santonyr 10-Dec-2004 - created
310 -- End of Commnets
311 --------------------------------------------------------------------------------
312
313 PROCEDURE get_pricing_engine
314 (p_khr_id IN okl_k_headers.id%TYPE,
315 x_pricing_engine OUT NOCOPY VARCHAR2,
316 x_return_status OUT NOCOPY VARCHAR2);
317
318
319 -- Added by rgooty
320 --------------------------------------------------------------------------------
321 -- Start of Commnets
322 -- Procedure Name : round_streams_amount_esg
323 -- Description : Returns PL/SQL table of record rounded amounts
324 -- of OKL_STRM_ELEMENTS type.
325 -- This function will be used in the ESG call for
326 -- rounding amounts.
327 -- Business Rules : Same as method round_streams_amount.
328 -- Parameters : P_chr_id,
329 -- p_selv_tbl of OKL_STRM_ELEMENTS type
330 -- x_selv_tbl of OKL_STRM_ELEMENTS type
331 -- p_org_id of OKC_K_HEADERS_B.AUTHORING_ORG_ID type
332 -- p_precision
333 -- p_currency_code of okc_k_headers_b.currency_code type
334 -- p_rounding_rule of okl_sys_acct_opts.stm_rounding_rule type
335 -- p_apply_rnd_diff of
336 -- okl_sys_acct_opts.stm_apply_rounding_difference type
337 -- Version : 1.0
338 -- End of Commnets
339 --------------------------------------------------------------------------------
340 --Bug 4196515 - Start of Changes
341 FUNCTION round_streams_amount_esg(p_api_version IN NUMBER,
342 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2,
345 p_chr_id IN okc_k_headers_b.id%TYPE,
346 p_selv_tbl IN okl_streams_pub.selv_tbl_type,
347 x_selv_tbl OUT NOCOPY okl_streams_pub.selv_tbl_type,
348 p_org_id IN okc_k_headers_b.authoring_org_id%TYPE,
349 p_precision IN NUMBER,
350 p_currency_code IN okc_k_headers_b.currency_code%TYPE,
351 p_rounding_rule IN okl_sys_acct_opts.stm_rounding_rule%TYPE,
352 p_apply_rnd_diff IN okl_sys_acct_opts.stm_apply_rounding_difference%TYPE)
353 RETURN VARCHAR2;
354 --Bug 4196515 - End of Changes
355
356 -- Added by RGOOTY: Start
357 --------------------------------------------------------------------------------
358 -- Start of Commnets
359 -- Procedure Name : get_acc_options
360 -- Description : Returns the Accounting Options to be
361 -- used for the contract with the khr_id passed.
362 --
363 -- Business Rules : Returns accounting options for a contract
364 -- Parameters : P_khr_id - Id of the Contract,
365 -- Returns x_org_id - Org Id
366 -- x_precision - Precision
367 -- x_currency_code - Currency Code
368 -- x_rounding_rule - Rounding Rule
369 -- x_apply_rnd_diff - Apply rounding Difference
370 -- x_return_status - Return Status of the API
371 -- Version : 1.0
372 -- End of Commnets
373 --------------------------------------------------------------------------------
374 PROCEDURE get_acc_options( p_khr_id IN okc_k_headers_b.ID%TYPE,
375 x_org_id OUT NOCOPY okc_k_headers_b.authoring_org_id%TYPE,
376 x_precision OUT NOCOPY NUMBER,
377 x_currency_code OUT NOCOPY okc_k_headers_b.currency_code%TYPE,
378 x_rounding_rule OUT NOCOPY okl_sys_acct_opts.stm_rounding_rule%TYPE,
379 x_apply_rnd_diff OUT NOCOPY okl_sys_acct_opts.stm_apply_rounding_difference%TYPE,
380 x_return_status OUT NOCOPY VARCHAR2 );
381
382 -- Added by RGOOTY: End
383
384 --------------------------------------------------------------------------------
385 -- Start of Commnets
386 -- Procedure Name : accumulate_strm_elements
387 -- Description : Appends the Stream Elements obtained to an
388 -- accumulating table
389 --
390 -- Business Rules : Returns accumulated Stream elements table
391 -- Parameters : p_stmv_rec - Stream Header record to be appended
392 -- Returns x_full_stmv_tbl - Stream Headers accumulating table
393 -- x_return_status - Return Status of the API
394 -- Version : rgooty 1.0 created
395 -- End of Commnets
396 --------------------------------------------------------------------------------
397 --Modified by kthiruva on 30-May-2005. The OUT parameter was made NOCOPY
398 --Bug 4374085 - Start of Changes
399 PROCEDURE accumulate_strm_headers(
400 p_stmv_rec IN Okl_Streams_Pub.stmv_rec_type,
401 x_full_stmv_tbl IN OUT NOCOPY Okl_Streams_Pub.stmv_tbl_type,
402 x_return_status OUT NOCOPY VARCHAR2);
403 --Bug 4374085 - End of Changes
404
405 -- Added by RGOOTY: Start
406 --------------------------------------------------------------------------------
407 -- Start of Commnets
408 -- Procedure Name : accumulate_strm_elements
409 -- Description : Appends the Stream Elements obtained to an
410 -- accumulating table
411 --
412 -- Business Rules : Returns accumulated Stream elements table
413 -- Parameters : p_stm_index_no - Stream Header index number
414 -- p_selv_tbl - Intermediate Stream Elements table
415 -- Returns x_full_selv_tbl - Returns the accumulated stream elements table
416 -- x_return_status - Return Status of the API
417 -- Version : rgooty 1.0 created
418 -- End of Commnets
419 --------------------------------------------------------------------------------
420 --Modified by kthiruva on 30-May-2005. The OUT parameter was made NOCOPY
421 --Bug 4374085 - Start of Changes
422 PROCEDURE accumulate_strm_elements(
423 p_stm_index_no IN NUMBER,
424 p_selv_tbl IN okl_streams_pub.selv_tbl_type,
425 x_full_selv_tbl IN OUT NOCOPY okl_streams_pub.selv_tbl_type,
426 x_return_status OUT NOCOPY VARCHAR2);
427 --Bug 4374085 - End of Changes
428
429 -- Added by kthiruva on 10-Oct-2005
430 -- Bug 4664698 - Start of changes
431 --------------------------------------------------------------------------------
432 -- Start of Commnets
433 -- Procedure Name : get_line_id
434 -- Description : Fetches the contract line id from the stream interface
435 -- tables during the inbound processing
436 --
437 -- Business Rules : Returns kle_id
438 -- Parameters : p_trx_number - Transaction number of the pricing
439 -- request
440 -- p_index_number - The index number which uniquely
441 -- defines every asset line
442 -- Returns x_kle_id - Id of the asset
443 -- x_return_status - Return Status of the API
444 -- Version : kthiruva 1.0 Created
445 -- End of Commnets
446 --------------------------------------------------------------------------------
447 PROCEDURE get_line_id(
448 p_trx_number IN okl_stream_interfaces.TRANSACTION_NUMBER%TYPE,
449 p_index_number IN okl_sif_ret_levels.INDEX_NUMBER%TYPE,
450 x_kle_id OUT NOCOPY NUMBER,
451 x_return_status OUT NOCOPY VARCHAR2);
452 -- Bug 4664698 - End of Changes
453
454 PROCEDURE get_k_trx_state(p_trx_id IN NUMBER,
455 x_rebook_type OUT NOCOPY VARCHAR2,
456 x_rebook_date OUT NOCOPY DATE,
457 x_query_trx_state OUT NOCOPY VARCHAR2,
458 x_trx_state OUT NOCOPY CLOB);
459
460 -- Procedure to update okl_stream_trx_data to indicate the last transaction state.
461 -- p_khr_id = contract_id; p_context: can have 3 values
462 -- 1. 'BOTH' 2. PRIMARY 3. REPORT
463
464 PROCEDURE UPDATE_TRX_STATE(P_KHR_ID IN NUMBER,
465 P_CONTEXT IN VARCHAR2);
466
467 -- Added for Fintec Enahncmenet for Lease Variable Rate Contracts : 9972860
468 FUNCTION GET_ORP_CODE(p_trx_id IN NUMBER)
469 RETURN VARCHAR2;
470
471 PROCEDURE get_var_amort_details(
472 p_trx_id IN NUMBER,
473 x_trx_state OUT NOCOPY CLOB,
474 x_reamort_start_date OUT NOCOPY DATE,
475 x_reamort_rate OUT NOCOPY VARCHAR2,
476 x_rebook_type OUT NOCOPY VARCHAR2);
477
478 --Added by bkatraga for bug 16344245
479 PROCEDURE get_balance_details(p_trx_id IN NUMBER,
480 p_line_id IN NUMBER,
481 x_bal_method OUT NOCOPY VARCHAR2,
482 x_bal_date OUT NOCOPY DATE,
483 x_bal_amount OUT NOCOPY NUMBER);
484
485 END Okl_Streams_Util;
[Home] [Help]