[Home] [Help]
PACKAGE: APPS.OKL_VARIABLE_INTEREST_PVT
Source
1 PACKAGE OKL_VARIABLE_INTEREST_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRVARS.pls 120.16.12020000.4 2012/11/23 14:22:28 rpillay ship $ */
3 --------------------------------------------------------------------------
4 -- Global Variables
5 --------------------------------------------------------------------------
6
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_VARIABLE_INTEREST_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
9 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
11 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
12 G_LLA_CHR_ID CONSTANT VARCHAR2(1000) := 'OKL_LLA_CHR_ID';
13 G_INVALID_VALUE CONSTANT VARCHAR2(1000) := 'OKL_INVALID_VALUE';
14 G_CAPITAL_AMT_ERROR CONSTANT VARCHAR2(1000) := 'OKL_LLA_CAPITAL_AMT_ERROR';
15 G_CALC_METHOD_CODE VARCHAR2(30);
16 G_INT_CALC_BASIS_FLOAT_FACTORS CONSTANT VARCHAR2(1000) := 'FLOAT_FACTORS';
17 G_INT_CALC_BASIS_REAMORT CONSTANT VARCHAR2(1000) := 'REAMORT';
18 G_INT_CALC_BASIS_FLOAT CONSTANT VARCHAR2(1000) := 'FLOAT';
19 G_INT_CALC_BASIS_CATCHUP CONSTANT VARCHAR2(1000) := 'CATCHUP/CLEANUP';
20 G_REQUEST_ID NUMBER;
21 G_FIN_LINE_LTY_ID CONSTANT NUMBER := 33;
22
23 -- Package level contract specific variables
24 G_CONTRACT_ID NUMBER;
25 G_CONTRACT_LINE_ID NUMBER;
26 G_AUTHORING_ORG_ID NUMBER;
27 G_PRODUCT_ID NUMBER;
28 G_DEAL_TYPE OKL_K_HEADERS_FULL_V.deal_type%TYPE;
29 G_CONTRACT_START_DATE OKL_K_HEADERS_FULL_V.start_date%TYPE;
30 G_CONTRACT_END_DATE OKL_K_HEADERS_FULL_V.end_date%TYPE;
31 G_CURRENCY_CODE OKL_K_HEADERS_FULL_V.currency_code%TYPE;
32 G_CONTRACT_PRINCIPAL_BALANCE NUMBER;
33 G_INTEREST_BASIS_CODE OKL_K_RATE_PARAMS.interest_basis_code%TYPE;
34 G_CALCULATION_FORMULA_ID OKL_K_RATE_PARAMS.calculation_formula_id%TYPE;
35 G_PRINCIPAL_BASIS_CODE OKL_K_RATE_PARAMS.principal_basis_code%TYPE;
36 G_DAYS_IN_A_MONTH_CODE OKL_K_RATE_PARAMS.days_in_a_month_code%TYPE;
37 G_DAYS_IN_A_YEAR_CODE OKL_K_RATE_PARAMS.days_in_a_year_code%TYPE;
38 G_CATCHUP_SETTLEMENT_CODE OKL_K_RATE_PARAMS.catchup_settlement_code%TYPE;
39 G_INTEREST_CALCULATION_BASIS OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE;
40 G_REVENUE_RECOGNITION_METHOD OKL_PRODUCT_PARAMETERS_V.REVENUE_RECOGNITION_METHOD%TYPE;
41 G_VIR_TBL_COUNTER NUMBER;
42 G_VPB_TBL_COUNTER NUMBER;
43
44 -- Line Specific Variables
45 G_FIN_AST_LINE_ID NUMBER;
46 G_ASSET_PRINCIPAL_BALANCE NUMBER;
47
48 -- Billing Transction types
49 G_BILLING_TRX_TYPE_ID OKL_TRX_TYPES_V.id%TYPE;
50 G_BILLING_TRX_TRY_ID OKL_TRX_TYPES_V.try_id%TYPE;
51 G_BILLING_TRX_DESC OKL_TRX_TYPES_V.description%TYPE;
52
53 -- Receipt App Transction types
54 G_RCPT_APP_TRX_TYPE_ID OKL_TRX_TYPES_V.id%TYPE;
55 G_RCPT_APP_TRX_TRY_ID OKL_TRX_TYPES_V.try_id%TYPE;
56 G_RCPT_APP_TRX_DESC OKL_TRX_TYPES_V.description%TYPE;
57
58 -- Receipt App Transaction Lookups
59 G_RAP_TCN_TYPE FND_LOOKUPS.lookup_code%TYPE;
60 G_RAP_TCN_MEANING FND_LOOKUPS.meaning%TYPE;
61 G_RAP_TCN_DESC FND_LOOKUPS.description%TYPE;
62
63 -- Receipt App Transaction Line Lookups
64 G_RAP_TCL_TYPE FND_LOOKUPS.lookup_code%TYPE;
65 G_RAP_TCL_MEANING FND_LOOKUPS.meaning%TYPE;
66 G_RAP_TCL_DESC FND_LOOKUPS.description%TYPE;
67
68 -- Fix for bug 5033120
69 -- Principal Adjust Transction types
70 G_PAD_TRX_TYPE_ID OKL_TRX_TYPES_V.id%TYPE;
71 G_PAD_TRX_TRY_ID OKL_TRX_TYPES_V.try_id%TYPE;
72 G_PAD_TRX_DESC OKL_TRX_TYPES_V.description%TYPE;
73
74 -- Principal Adjust Transaction Lookups
75 G_PAD_TCN_TYPE FND_LOOKUPS.lookup_code%TYPE;
76 G_PAD_TCN_MEANING FND_LOOKUPS.meaning%TYPE;
77 G_PAD_TCN_DESC FND_LOOKUPS.description%TYPE;
78
79 -- Principal Adjust Transaction Line Lookups
80 G_PAD_TCL_TYPE FND_LOOKUPS.lookup_code%TYPE;
81 G_PAD_TCL_MEANING FND_LOOKUPS.meaning%TYPE;
82 G_PAD_TCL_DESC FND_LOOKUPS.description%TYPE;
83
84 TYPE interest_rec is RECORD
85 (khr_id NUMBER := 0
86 ,Kle_id NUMBER := 0
87 ,Principle NUMBER := 0
88 ,start_date DATE
89 ,end_date DATE
90 ,effective_rate NUMBER(5,2) := 0
91 ,interest_amount NUMBER := 0
92 ,days_in_year VARCHAR2(20)
93 ,variable_rate VARCHAR2(1)
94 ,variable_method VARCHAR2(100)
95 ,interest_method VARCHAR2(100)
96 ,index_name VARCHAR2(100)
97 ,base_rate NUMBER(5,2) := 0
98 ,minimum_rate NUMBER(5,2) := 0
99 ,maximum_rate NUMBER(5,2) := 0
100 ,tolerance NUMBER(5,2) := 0);
101
102 TYPE date_rate_rec IS RECORD ( from_date DATE
103 ,to_date DATE
104 ,rate NUMBER(5,2)
105 );
106
107 TYPE date_rate_tbl IS TABLE OF date_rate_rec INDEX BY BINARY_INTEGER;
108
109 TYPE principal_balance_rec_type IS RECORD (
110 khr_id OKL_K_HEADERS.id%TYPE,
111 kle_id OKL_K_LINES.id%TYPE,
112 from_date DATE,
113 to_date DATE,
114 Principal_balance OKL_STRM_ELEMENTS.amount%TYPE);
115
116 TYPE principal_balance_tbl_typ is TABLE of principal_balance_rec_type INDEX BY BINARY_INTEGER;
117
118 TYPE receipt_rec_type IS RECORD (
119 khr_id OKL_K_HEADERS.id%TYPE,
120 kle_id OKL_K_LINES.id%TYPE,
121 transaction_type Varchar2(1),
122 receipt_date DATE,
123 receipt_amount NUMBER,
124 principal_pmt_rcpt_amt OKL_STRM_ELEMENTS.amount%TYPE,
125 loan_pmt_rcpt_amt OKL_STRM_ELEMENTS.amount%TYPE);
126
127 TYPE receipt_tbl_type IS TABLE of receipt_rec_type INDEX BY BINARY_INTEGER;
128
129 TYPE interest_rate_rec_type IS RECORD ( from_date DATE
130 ,to_date DATE
131 ,rate NUMBER
132 ,derived_flag VARCHAR2(1) DEFAULT 'Y'
133 ,apply_tolerance VARCHAR2(1) DEFAULT 'Y'
134 );
135
136 TYPE interest_rate_tbl_type IS TABLE OF interest_rate_rec_type INDEX BY BINARY_INTEGER;
137
138 TYPE vpb_rec_type IS RECORD (
139 ID OKL_VAR_PRINCIPAL_BAL_TXN.ID%TYPE
140 ,KHR_ID OKL_VAR_PRINCIPAL_BAL_TXN.KHR_ID%TYPE
141 ,SOURCE_TABLE OKL_VAR_PRINCIPAL_BAL_TXN.SOURCE_TABLE%TYPE
142 ,SOURCE_ID OKL_VAR_PRINCIPAL_BAL_TXN.SOURCE_ID%TYPE
143 ,INT_CAL_PROCESS OKL_VAR_PRINCIPAL_BAL_TXN.INT_CAL_PROCESS%TYPE
144 ,OBJECT_VERSION_NUMBER OKL_VAR_PRINCIPAL_BAL_TXN.OBJECT_VERSION_NUMBER%TYPE
145 ,ORG_ID OKL_VAR_PRINCIPAL_BAL_TXN.ORG_ID%TYPE
146 ,REQUEST_ID OKL_VAR_PRINCIPAL_BAL_TXN.REQUEST_ID%TYPE
147 ,PROGRAM_APPLICATION_ID OKL_VAR_PRINCIPAL_BAL_TXN.PROGRAM_APPLICATION_ID%TYPE
148 ,PROGRAM_ID OKL_VAR_PRINCIPAL_BAL_TXN.PROGRAM_ID%TYPE
149 ,PROGRAM_UPDATE_DATE OKL_VAR_PRINCIPAL_BAL_TXN.PROGRAM_UPDATE_DATE%TYPE
150 ,ATTRIBUTE_CATEGORY OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE_CATEGORY%TYPE
151 ,ATTRIBUTE1 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE1%TYPE
152 ,ATTRIBUTE2 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE2%TYPE
153 ,ATTRIBUTE3 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE3%TYPE
154 ,ATTRIBUTE4 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE4%TYPE
155 ,ATTRIBUTE5 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE5%TYPE
156 ,ATTRIBUTE6 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE6%TYPE
157 ,ATTRIBUTE7 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE7%TYPE
158 ,ATTRIBUTE8 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE8%TYPE
159 ,ATTRIBUTE9 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE9%TYPE
160 ,ATTRIBUTE10 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE10%TYPE
161 ,ATTRIBUTE11 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE11%TYPE
162 ,ATTRIBUTE12 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE12%TYPE
163 ,ATTRIBUTE13 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE13%TYPE
164 ,ATTRIBUTE14 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE14%TYPE
165 ,ATTRIBUTE15 OKL_VAR_PRINCIPAL_BAL_TXN.ATTRIBUTE15%TYPE
166 ,CREATED_BY OKL_VAR_PRINCIPAL_BAL_TXN.CREATED_BY%TYPE
167 ,CREATION_DATE OKL_VAR_PRINCIPAL_BAL_TXN.CREATION_DATE%TYPE
168 ,LAST_UPDATED_BY OKL_VAR_PRINCIPAL_BAL_TXN.LAST_UPDATED_BY%TYPE
169 ,LAST_UPDATE_DATE OKL_VAR_PRINCIPAL_BAL_TXN.LAST_UPDATE_DATE%TYPE
170 ,LAST_UPDATE_LOGIN OKL_VAR_PRINCIPAL_BAL_TXN.LAST_UPDATE_LOGIN%TYPE);
171
172 TYPE vpb_tbl_type IS TABLE OF vpb_rec_type INDEX BY BINARY_INTEGER;
173
174
175 SUBTYPE csm_periodic_expenses_tbl_type IS okl_process_streams_pvt.srlv_tbl_type;
176 SUBTYPE strm_lalevl_tbl IS OKL_MASS_REBOOK_PVT.strm_lalevl_tbl_type;
177 SUBTYPE rbk_tbl IS OKL_MASS_REBOOK_PVT.rbk_tbl_type;
178 SUBTYPE strm_trx_tbl IS OKL_MASS_REBOOK_PVT.strm_trx_tbl_type;
179 SUBTYPE csm_loan_level_tbl_type IS okl_create_streams_pvt.csm_loan_level_tbl_type;
180 SUBTYPE vipv_rec IS OKL_VIP_PVT.vipv_rec_type;
181 SUBTYPE vir_tbl_type IS OKL_VIR_PVT.vir_tbl_type;
182
183 G_VIR_TBL vir_tbl_type;
184 G_VPB_TBL vpb_tbl_type;
185
186 G_NET_INVESTMENT_DF CONSTANT VARCHAR2(50) := 'CONTRACT_NET_INVESTMENT_DF';
187 G_NET_INVESTMENT_OP CONSTANT VARCHAR2(50) := 'CONTRACT_NET_INVESTMENT_OP';
188
189 G_DEAL_TYPE_LEASEDF CONSTANT VARCHAR2(30) := 'LEASEDF';
190 G_DEAL_TYPE_LEASEOP CONSTANT VARCHAR2(30) := 'LEASEOP';
191 ---------------------------------------------------------------------------
192 -- Procedures and Functions
193 ---------------------------------------------------------------------------
194
195 PROCEDURE variable_interest(
196 p_api_version IN NUMBER,
197 p_init_msg_list IN VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 p_contract_number IN VARCHAR2,
202 P_to_date IN DATE);
203
204 FUNCTION get_prorated_prin_amt_line (
205 p_line_id IN NUMBER,
206 p_stream_element_date IN DATE,
207 p_loan_amount IN NUMBER,
208 p_currency_code IN VARCHAR2) RETURN NUMBER;
209
210 FUNCTION get_prorated_prin_amt_header (
211 p_contract_id IN NUMBER,
212 p_line_id IN NUMBER,
213 p_stream_element_date IN DATE,
214 p_loan_amount IN NUMBER,
215 p_currency_code IN VARCHAR2) RETURN NUMBER;
216
217 FUNCTION get_last_int_calc_date(p_khr_id IN NUMBER) RETURN DATE;
218
219 PRAGMA RESTRICT_REFERENCES (get_prorated_prin_amt_line, WNDS);
220 PRAGMA RESTRICT_REFERENCES (get_prorated_prin_amt_header, WNDS);
221 PRAGMA RESTRICT_REFERENCES (get_last_int_calc_date, WNDS);
222
223 PROCEDURE prin_date_range_var_rate_ctr (
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 p_contract_id IN NUMBER,
230 p_line_id IN NUMBER,
231 p_start_date IN DATE,
232 p_due_date IN DATE,
233 p_principal_basis IN VARCHAR2 DEFAULT NULL,
234 x_principal_balance_tbl OUT NOCOPY principal_balance_tbl_typ);
235
236 --Bug# 13447258: Added p_line_id parameter
237 --Bug# 14165508: Added p_conversion_factor
238 FUNCTION calculate_total_interest_due(
239 p_api_version IN NUMBER,
240 p_init_msg_list IN VARCHAR2,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2,
244 p_contract_id IN NUMBER,
245 p_currency_code IN VARCHAR2,
246 p_start_date IN DATE,
247 p_due_date IN DATE,
248 p_principal_basis IN VARCHAR2 DEFAULT NULL,
249 p_line_id IN NUMBER DEFAULT NULL,
250 p_conversion_factor IN NUMBER DEFAULT NULL) RETURN NUMBER;
251
252 PROCEDURE interest_date_range (
253 p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_contract_id IN NUMBER,
259 p_start_date IN DATE,
260 p_end_date IN DATE,
261 p_process_flag IN VARCHAR2 ,
262 x_interest_rate_tbl OUT NOCOPY interest_rate_tbl_type);
263
264 --Bug# 14165508: Added p_conversion_factor
265 Function calculate_interest (
266 p_api_version IN NUMBER,
267 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2,
271 p_contract_id IN NUMBER,
272 p_from_date IN DATE,
273 p_to_date IN DATE,
274 p_principal_amount IN NUMBER,
275 p_currency_code IN VARCHAR2,
276 p_conversion_factor IN NUMBER DEFAULT NULL) RETURN NUMBER;
277
278 Procedure Create_Daily_Interest_Streams (
279 p_api_version IN NUMBER,
280 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 p_contract_id IN NUMBER,
285 p_line_id IN NUMBER DEFAULT NULL,
286 p_amount IN NUMBER,
287 p_due_date IN DATE,
288 p_stream_type_purpose IN VARCHAR2,
289 p_create_invoice_flag IN VARCHAR2 DEFAULT OKL_API.G_TRUE,
290 p_process_flag IN VARCHAR2 DEFAULT NULL,
291 p_currency_code IN VARCHAR2 DEFAULT NULL);
292
293 PROCEDURE print(p_message IN VARCHAR2);
294
295 PROCEDURE print_debug(p_message IN VARCHAR2);
296
297 PROCEDURE print_error_message(p_message IN VARCHAR2);
298
299 PROCEDURE populate_txns ( p_api_version IN NUMBER,
300 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
301 p_khr_id IN NUMBER,
302 p_from_date IN DATE,
303 p_to_date IN DATE,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2);
307
308 Procedure upd_vir_params_with_invoice (
309 p_api_version IN NUMBER,
310 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_msg_count OUT NOCOPY NUMBER,
313 x_msg_data OUT NOCOPY VARCHAR2,
314 p_source_id IN NUMBER,
315 p_catchup_amt IN NUMBER DEFAULT NULL,
316 p_catchup_settlement_code IN VARCHAR2 DEFAULT NULL,
317 p_vir_tbl IN vir_tbl_type,
318 x_vir_tbl OUT NOCOPY vir_tbl_type,
319 --Bug# 12974796
320 p_source_table IN VARCHAR2 DEFAULT NULL);
321
322 FUNCTION calculate_from_khr_start_date(p_khr_id IN NUMBER,
323 p_from_date IN DATE) RETURN VARCHAR2;
324
325 --Bug# 14049528
326 FUNCTION get_next_period_start_date(p_khr_id IN NUMBER,
327 p_start_date IN DATE) RETURN DATE;
328
329 PROCEDURE print_vpb_tbl ( p_vpb_tbl IN vpb_tbl_type);
330
331 PROCEDURE print_vir_tbl ( p_vir_tbl IN vir_tbl_type);
332
333 END;