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