1 PACKAGE LNS_FIN_UTILS AUTHID CURRENT_USER AS
2 /* $Header: LNS_FIN_UTILS_S.pls 120.10.12020000.3 2013/02/11 14:39:19 scherkas ship $ */
3
4 /*========================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +========================================================================*/
7 TYPE DATE_TBL IS TABLE OF DATE INDEX BY BINARY_INTEGER;
8
9
10 /*========================================================================+
11 | types for building payment schedule
12 +========================================================================*/
13 TYPE PAYMENT_SCHEDULE is record(PERIOD_BEGIN_DATE DATE
14 ,PERIOD_END_DATE DATE
15 ,PERIOD_DUE_DATE DATE
16 ,CONTENTS VARCHAR(30) -- row contents: PRIN, INT, PRIN_INT
17 );
18
19 TYPE PAYMENT_SCHEDULE_TBL is table of PAYMENT_SCHEDULE index by binary_integer;
20
21 TYPE FREQUENCY_SCHEDULE is record(COMPONENT VARCHAR(30) -- components: PRIN, INT, PRIN_INT
22 ,PERIOD_BEGIN_DATE DATE
23 ,FREQUENCY VARCHAR2(30));
24
25 TYPE FREQUENCY_SCHEDULE_TBL is table of FREQUENCY_SCHEDULE index by binary_integer;
26
27 /* BEGIN DATE FUNCTIONS */
28 function getNextDate(p_date in date
29 ,p_interval_type in varchar2
30 ,p_direction in number) return Date;
31
32 function getDayCount(p_start_date in date
33 ,p_end_date in date
34 ,p_day_count_method in varchar2) return number;
35
36 function daysInYear(p_year in number
37 ,p_year_count_method in varchar2) return number;
38
39 function julian_date(p_date in date) return number;
40
41 function isLeapYear(p_year in number) return boolean;
42
43 function intervalsInPeriod(p_period_number in number
44 ,p_period_type1 in varchar2
45 ,p_period_type2 in varchar2) return number;
46
47 function buildPaymentSchedule(p_loan_start_date in date
48 ,p_loan_maturity_date in date
49 ,p_first_pay_date in date
50 ,p_num_intervals in number
51 ,p_interval_type in varchar2
52 ,p_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
53
54 function buildPaymentSchedule(p_loan_start_date in date
55 ,p_loan_maturity_date in date
56 ,p_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
57
58 function buildSIPPaymentSchedule(p_loan_start_date in date
59 ,p_loan_maturity_date in date
60 ,p_int_first_pay_date in date
61 ,p_int_num_intervals in number
62 ,p_int_interval_type in varchar2
63 ,p_int_pay_in_arrears in boolean
64 ,p_prin_first_pay_date in date
65 ,p_prin_num_intervals in number
66 ,p_prin_interval_type in varchar2
67 ,p_prin_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
68
69 function buildSIPPaymentSchedule(p_loan_start_date in date
70 ,p_loan_maturity_date in date
71 ,p_prin_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL
72 ,p_int_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
73
74 function convertPeriod(p_term in number
75 ,p_term_period in varchar2) return number;
76
77 /* END DATE FUNCTIONS */
78
79 /* BEGIN LOAN PROPERTIES FUNCTIONS */
80 function getMaturityDate(p_term in number
81 ,p_term_period in varchar2
82 ,p_frequency in varchar2
83 ,p_start_date in date) return date;
84
85 -- fix for bug 5842639: added p_loan_start_date parameter
86 function getPaymentSchedule(p_loan_start_date in date
87 ,p_first_pay_date in date
88 ,p_maturity_date in date
89 ,p_pay_in_arrears in boolean
90 ,p_num_intervals in number
91 ,p_interval_type in varchar2) return LNS_FIN_UTILS.DATE_TBL;
92
93 function getPaymentSchedule(p_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL
94 ,p_loan_maturity_date in date) return lns_fin_utils.DATE_TBL;
95 /*
96 function getInstallmentDate(p_loan_id IN NUMBER
97 ,p_installment_number IN NUMBER) return date;
98 */
99 function getNumberInstallments(p_loan_id in number) return NUMBER;
100
101 function getNumberInstallments(p_loan_id in number
102 ,p_phase in varchar2) return NUMBER;
103
104 function getActiveRate(p_loan_id in number) return number;
105
106 /* END LOAN PROPERTIES FUNCTIONS */
107
108 /* BEGIN RATE FUNCTIONS */
109 function convertRate(p_annualized_rate in number
110 ,p_amortization_frequency in varchar2) return number;
111
112 function getRateForDate(p_index_rate_id in number
113 ,p_rate_date in date) return number;
114 /* END RATE FUNCTIONS */
115
116
117 function buildPaymentScheduleExt(p_loan_id in number
118 ,p_phase in varchar2) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
119
120
121
122 /*=========================================================================
123 || PUBLIC FUNCTION getNextInstallmentAfterDate - R12
124 ||
125 || DESCRIPTION
126 ||
127 || Overview: returns the installmentNumber for the provided date for a loan
128 ||
129 || Parameter: p_loan_id => loan_id
130 || p_date => date for which the installment exists
131 || p_phase => phase of the loan
132 ||
133 || Return value: installment number
134 ||
135 || Source Tables: LNS_LOAN_HEADERS_ALL, LNS_TERMS
136 ||
137 || Target Tables: NA
138 ||
139 || MODIFICATION HISTORY
140 || Date Author Description of Changes
141 || 13-Jan-2010 mbolli Bug#9255294 - Created
142 *=======================================================================*/
143 function getNextInstallmentAfterDate(p_loan_id in number
144 ,p_date in date
145 ,p_phase in varchar2) return NUMBER;
146
147
148 /*=========================================================================
149 || PUBLIC FUNCTION getNextInstForDisbursement - R12
150 ||
151 || DESCRIPTION
152 ||
153 || Overview: returns the installmentNumber for the provided disbursement
154 ||
155 || Parameter: p_loan_id => disb_header_id
156 ||
157 || Return value: installment number
158 ||
159 || Source Tables: LNS_DISB_HEADERS
160 ||
161 || Target Tables: NA
162 ||
163 || MODIFICATION HISTORY
164 || Date Author Description of Changes
165 || 05-Feb-2010 mbolli Bug#9255294 - Created
166 *=======================================================================*/
167 function getNextInstForDisbursement(p_disb_hdr_id in number) return NUMBER;
168
169 -- This procedure returns CURRENT/ORIGINAL/CUSTOM amortization schedule in form of csv formatted clob
170 procedure getAmortSchedCSV(
171 P_API_VERSION IN NUMBER,
172 P_INIT_MSG_LIST IN VARCHAR2,
173 P_COMMIT IN VARCHAR2,
174 P_VALIDATION_LEVEL IN NUMBER,
175 P_LOAN_ID IN NUMBER,
176 P_SCHEDULE_TYPE IN VARCHAR2, -- CURRENT/ORIGINAL/CUSTOM
177 X_CSV_CLOB OUT NOCOPY CLOB,
178 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
179 X_MSG_COUNT OUT NOCOPY NUMBER,
180 X_MSG_DATA OUT NOCOPY VARCHAR2);
181
182 function fetchFreqSchedule(
183 P_LOAN_ID IN NUMBER,
184 P_PHASE IN VARCHAR2, -- OPEN/TERM/null will be defaulted to TERM
185 P_COMPONENT IN VARCHAR2 -- PRIN/INT/PRIN_INT/null will be defaulted to PRIN_INT
186 ) return LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
187
188 procedure fetchPayFreqRecByDate(
189 p_FREQUENCY_SCHEDULE IN LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL,
190 p_DATE IN DATE,
191 x_FREQUENCY_REC OUT NOCOPY LNS_FIN_UTILS.FREQUENCY_SCHEDULE);
192
193 END LNS_FIN_UTILS;
[Home] [Help]