1 PACKAGE LNS_LOAN_PUB AUTHID CURRENT_USER as
2 /* $Header: LNS_LOAN_PUBP_S.pls 120.7 2011/01/06 14:00:29 scherkas ship $ */
3 /*#
4 * Start of Comments
5 * Package name : LNS_LOAN_PUB
6 * Purpose Creates a new loan
7 * History :
8 */
9 -- * @rep:scope public
10 -- * @rep:product LNS
11 -- * @rep:displayname Create Loan
12 -- * @rep:lifecycle active
13 -- * @rep:compatibility S
14 -- * @rep:category BUSINESS_ENTITY LOAN
15
16 /*=======================================================================+
17 | Declare PUBLIC Data Types and Variables
18 +=======================================================================*/
19
20 -- Loan header record
21 TYPE Loan_Details_Rec_Type IS RECORD (
22
23 -- common attributes
24 product_id NUMBER(15) -- FK to lns_loan_products.product_id; mandatory
25 ,loan_number VARCHAR2(20) -- if profile LNS_GENERATE_LOAN_NUMBER = 'Y'- will be set by api; otherwise mandatory
26 ,loan_description VARCHAR2(250) -- Free Form Text; optional
27 ,loan_assigned_to NUMBER(15) -- FK to jtf_rs_resource_extns.resource_id; mandatory
28 ,legal_entity_id NUMBER(15) -- FK to xle_entity_profiles.legal_entity_id; mandatory if product's legal_entity_id is null
29 ,requested_amount NUMBER -- Loan requested amount; mandatory for direct loans
30 ,loan_application_date DATE -- Loan application date; optional; if null will be defaulted to loan_start_date
31 ,exchange_rate_type VARCHAR2(30) -- FK to gl_daily_conversion_types; mandatory if product's currency is different from functional currency
32 ,exchange_rate NUMBER -- exchange_rate for USER exchange_rate_type
33 ,exchange_date DATE -- exchange_date; mandatory if exchange_rate_type is not null
34 ,loan_purpose_code VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LOAN_PURPOSE'; mandatory
35 ,loan_subtype VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LOAN_SUBTYPE'; mandatory
36 ,credit_review_flag VARCHAR2(1) -- 'Y' or 'N' or null; optional
37 ,trx_type_id NUMBER(15) -- principal trx type; FK to ar_lookups.lookup_code; lookup_type = 'INV/CM'; mandatory
38 ,collateral_percent NUMBER -- collateral_percent; optional
39 ,CUSTOM_PAYMENTS_FLAG VARCHAR2(1) -- valid values: null or N or Y; optional
40 ,FORGIVENESS_FLAG VARCHAR2(1) -- enable forgiveness: valid values: Y and N; optional; if null will be defaulted from product_id
41 ,FORGIVENESS_PERCENT NUMBER -- forgiveness percent; valid values from 0 to 100; optional; if null will be defaulted from product_id
42 ,org_id NUMBER -- do not pass; will be returned by api
43 ,loan_type_id NUMBER -- do not pass; will be returned by api
44 ,loan_class_code VARCHAR2(30) -- do not pass; will be returned by api
45 ,loan_currency VARCHAR2(15) -- do not pass; will be returned by api
46 ,maturity_date DATE -- do not pass; will be returned by api
47 ,open_maturity_date DATE -- do not pass; will be returned by api
48 ,open_payment_frequency VARCHAR2(30) -- do not pass; will be returned by api
49
50 -- primary borrower attributes
51 ,primary_borrower_party_id NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; mandatory
52 ,cust_account_id NUMBER(15) -- FK to hz_cust_accounts_all.cust_account_id; Note: Status should be 'A'; mandatory
53 ,bill_to_acct_site_id NUMBER(15) -- FK to hz_cust_acct_sites_all; mandatory
54 ,contact_rel_party_id NUMBER(15) -- FK to hz_relationships.party_id; optional
55 ,CONTACT_PERS_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; optional
56
57 -- common term attributes
58 ,RATE_TYPE VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'RATE_TYPE'; optional; if null will be defaulted from product_id
59 ,INDEX_RATE_ID NUMBER -- FK to lns_int_rate_headers.interest_rate_id; optional; if null will be defaulted from product_id
60 ,DAY_COUNT_METHOD VARCHAR2(50) -- days count method; FK to lns_lookups.lookup_code; lookup_type = 'DAY_COUNT_METHOD'; optional; if null will be defaulted from product_id
61 ,LOAN_PAYMENT_FREQUENCY VARCHAR2(30) -- loan/interest payment frequency; FK to lns_lookups.lookup_code; lookup_type = 'FREQUENCY'; optional; if null will be defaulted from product_id
62 ,CALCULATION_METHOD VARCHAR2(30) -- interest calculation method: SIMPLE or COMPOUND; optional; if null will be defaulted from product_id
63 ,INTEREST_COMPOUNDING_FREQ VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'INTEREST_COMPOUNDING_FREQ'; optional; if null will be defaulted from product_id
64 ,PAYMENT_CALC_METHOD VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'PAYMENT_CALCULATION_METHOD'; optional; if null will be defaulted from product_id else to EQUAL_PAYMENT
65 ,CUSTOM_CALC_METHOD VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = CUSTOM_CALCULATION_METHOD; mandatory if CUSTOM_PAYMENTS_FLAG = Y
66 ,ORIG_PAY_CALC_METHOD VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = PAYMENT_CALCULATION_METHOD; optional
67 ,PENAL_INT_RATE NUMBER -- penal interest rate; if null will be defaulted from product_id else will be defaulted to 0
68 ,PENAL_INT_GRACE_DAYS NUMBER -- penal interest grace days; if null will be defaulted from product_id else will be defaulted to 0
69 ,CALC_ADD_INT_UNPAID_PRIN VARCHAR2(1) -- calc additional interest on unpaid principal flag; optional; if null will be defaulted from product_id; valid values: Y or N or null;
70 ,CALC_ADD_INT_UNPAID_INT VARCHAR2(1) -- calc additional interest on unpaid interest flag; optional; if null will be defaulted from product_id; valid values: Y or N or null;
71 ,LOCK_DATE DATE -- rate lock date; optional; optional
72 ,LOCK_EXP_DATE DATE -- rate lock experation date; optional
73
74 -- 'term phase' term attributes
75 ,loan_term NUMBER -- term phase term; optional; if null will be defaulted from product_id
76 ,loan_term_period VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'PERIOD'; optional; if null will be defaulted from product_id
77 ,balloon_payment_type VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'BALLOON_PAYMENT_TYPE'; optional; if null will be defaulted to TERM
78 ,balloon_payment_amount NUMBER -- balloon amount for term phase; mandatory if balloon_payment_type = 'AMOUNT'; if null will be defaulted to 0
79 ,balloon_term NUMBER -- balloon term for term phase; mandatory if balloon_payment_type = 'TERM'; if null will be defaulted to loan_term
80 ,loan_start_date DATE -- term phase start date; mandatory; if null will be defaulted to sysdate
81 ,FIRST_PAYMENT_DATE DATE -- term phase loan/interest first payment date; mandatory; if null will be defaulted to loan_start_date+1 payment_period
82 ,PRIN_FIRST_PAY_DATE DATE -- term phase principal first payment date; used with PAYMENT_CALC_METHOD = SEPARATE_SCHEDULES; if null will be defaulted to FIRST_PAYMENT_DATE
83 ,PRIN_PAYMENT_FREQUENCY VARCHAR2(30) -- term phase principal payment freq; used with PAYMENT_CALC_METHOD = SEPARATE_SCHEDULES; if null will be defaulted from product_id else to LOAN_PAYMENT_FREQUENCY
84 ,floor_rate NUMBER -- term phase floor rate; optional; only applies when RATE_TYPE = FLOATING
85 ,ceiling_rate NUMBER -- term phase ceiling rate; optional; only applies when RATE_TYPE = FLOATING
86 ,percent_increase NUMBER -- term phase max sequential rate percent increase; optional; only applies when RATE_TYPE = FLOATING
87 ,percent_increase_life NUMBER -- term phase max rate percent increase for life of the loan; optional; only applies when RATE_TYPE = FLOATING
88
89 -- 'disbursement phase' term attributes
90 ,open_loan_term NUMBER -- term phase term; mandatory if its loan with disbursement phase
91 ,open_loan_term_period VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'PERIOD'; mandatory if its loan with disbursement phase
92 ,open_loan_start_date DATE -- disbursement phase start date; mandatory if its loan with disbursement phase
93 ,open_first_payment_date DATE -- disbursement phase first payment date; mandatory if its loan with disbursement phase
94 ,open_to_term_event VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'OPEN_TO_TERM_EVENT'; mandatory if its loan with disbursement phase
95 ,open_floor_rate NUMBER -- open phase floor rate; optional; only applies when loan with disbursement phase and RATE_TYPE = FLOATING
96 ,open_ceiling_rate NUMBER -- open phase ceiling rate; optional; only applies when loan with disbursement phase and RATE_TYPE = FLOATING
97 ,open_percent_increase NUMBER -- open phase max sequential rate percent increase; optional; only applies when loan with disbursement phase and RATE_TYPE = FLOATING
98 ,open_percent_increase_life NUMBER -- open phase max rate percent increase for life of the loan; optional; only applies when loan with disbursement phase and RATE_TYPE = FLOATING
99
100 -- Terms for Payment attributes
101 ,REAMORTIZE_OVER_PAYMENT VARCHAR2(1) -- reamortize overpayment flag; optional; valid values: Y or N or null;
102 ,DELINQUENCY_THRESHOLD_AMOUNT NUMBER -- delinquency amount; optional; if null will be defaulted by api;
103 ,PAYMENT_APPLICATION_ORDER VARCHAR2(30) -- payment application order; optional; if null will be defaulted from loan_type_id
104 ,PMT_APPL_ORDER_SCOPE VARCHAR2(30) -- payment application scope; optional; if null will be defaulted from loan_type_id
105 ,REAMORTIZE_ON_FUNDING VARCHAR2(30) -- reamortize on funding setting; optional; if null will be defaulted from product_id; valid values: NO, REST, IMMEDIATELY;
106 ,DEFAULT_THRESHOLD_AMOUNT NUMBER -- default threshold amount; optional; if null will be defaulted by api;
107
108 -- additional optional attributes
109 ,ATTRIBUTE_CATEGORY VARCHAR2(30) -- optional
110 ,ATTRIBUTE1 VARCHAR2(150) -- optional
111 ,ATTRIBUTE2 VARCHAR2(150) -- optional
112 ,ATTRIBUTE3 VARCHAR2(150) -- optional
113 ,ATTRIBUTE4 VARCHAR2(150) -- optional
114 ,ATTRIBUTE5 VARCHAR2(150) -- optional
115 ,ATTRIBUTE6 VARCHAR2(150) -- optional
116 ,ATTRIBUTE7 VARCHAR2(150) -- optional
117 ,ATTRIBUTE8 VARCHAR2(150) -- optional
118 ,ATTRIBUTE9 VARCHAR2(150) -- optional
119 ,ATTRIBUTE10 VARCHAR2(150) -- optional
120 ,ATTRIBUTE11 VARCHAR2(150) -- optional
121 ,ATTRIBUTE12 VARCHAR2(150) -- optional
122 ,ATTRIBUTE13 VARCHAR2(150) -- optional
123 ,ATTRIBUTE14 VARCHAR2(150) -- optional
124 ,ATTRIBUTE15 VARCHAR2(150) -- optional
125 ,ATTRIBUTE16 VARCHAR2(150) -- optional
126 ,ATTRIBUTE17 VARCHAR2(150) -- optional
127 ,ATTRIBUTE18 VARCHAR2(150) -- optional
128 ,ATTRIBUTE19 VARCHAR2(150) -- optional
129 ,ATTRIBUTE20 VARCHAR2(150) -- optional
130 );
131
132 -- loan lines
133 TYPE Loan_Lines_Rec_Type IS RECORD (
134 line_number NUMBER(15) -- Any unique serial number; mandatory
135 ,payment_schedule_id NUMBER(15) -- FK to ar_payment_schedules_all; payment_schedule_id; mandatory
136 ,requested_amount NUMBER -- line amount; mandatory
137 ,REFERENCE_DESCRIPTION VARCHAR2(250) -- Free form Text; optional
138 ,REFERENCE_NUMBER VARCHAR2(60) -- do not pass; will be set to trx_number;
139 ,customer_trx_id NUMBER -- do not pass; will be set to customer_trx_id
140 ,remaining_balance NUMBER -- do not pass; will be set to amount_due_remaining
141 ,installment_number NUMBER -- do not pass; will be set to terms_sequence_number
142 );
143 TYPE Loan_Lines_Tbl_Type IS TABLE OF Loan_Lines_Rec_Type INDEX BY BINARY_INTEGER;
144
145 -- loan disbursements
146 TYPE Loan_Disb_Rec_Type IS RECORD (
147 ACTIVITY_CODE VARCHAR2(30) -- disbursement predefined activity code; optional
148 ,DESCRIPTION VARCHAR2(250) -- disbursement description; either ACTIVITY_CODE or DESCRIPTION must be passed
149 ,AMOUNT NUMBER -- disbursement amount; mandatory
150 ,DUE_DATE DATE -- disbursement due date; mandatory
151 ,PAYEE_PARTY_ID NUMBER -- disbursement party id; mandatory
152 ,BANK_ACCOUNT_ID NUMBER -- disbursement bank account id; optional
153 ,PAYMENT_METHOD_CODE VARCHAR2(30) -- disbursement payment method; optional
154 );
155 TYPE Loan_Disb_Tbl_Type IS TABLE OF Loan_Disb_Rec_Type INDEX BY BINARY_INTEGER;
156
157 -- loan participants
158 TYPE LOAN_PART_REC_TYPE IS RECORD (
159 HZ_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; mandatory
160 ,LOAN_PARTICIPANT_TYPE VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LNS_PARTICIPANT_TYPE' and lookup_code <> 'PRIMARY_BORROWER'; mandatory
161 ,START_DATE_ACTIVE DATE -- optional
162 ,END_DATE_ACTIVE DATE -- optional
163 ,CUST_ACCOUNT_ID NUMBER(15) -- FK to hz_cust_accounts_all.cust_account_id; Note: Status should be 'A'; mandatory
164 ,BILL_TO_ACCT_SITE_ID NUMBER(15) -- FK to hz_cust_acct_sites_all; mandatory
165 ,CONTACT_PERS_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; optional
166 ,CONTACT_REL_PARTY_ID NUMBER(15) -- FK to hz_relationships.party_id; optional
167 );
168 TYPE LOAN_PART_TBL_TYPE IS TABLE OF LOAN_PART_REC_TYPE INDEX BY BINARY_INTEGER;
169
170 -- loan rate schedule
171 TYPE LOAN_RATES_REC_TYPE IS RECORD (
172 INDEX_RATE NUMBER -- index interest rate for this rate record; mandatory
173 ,SPREAD NUMBER -- interest rate spread for this rate record; optional
174 ,INDEX_DATE DATE -- optional; if passed INDEX_RATE will be defaulted from this date
175 ,BEGIN_INSTALLMENT_NUMBER NUMBER -- BEGIN_INSTALLMENT_NUMBER for this rate record; mandatory
176 ,END_INSTALLMENT_NUMBER NUMBER -- END_INSTALLMENT_NUMBER for this rate record; mandatory
177 ,INTEREST_ONLY_FLAG VARCHAR2(1) -- 'Y' or 'N' or null; optional
178 );
179 TYPE LOAN_RATES_TBL_TYPE IS TABLE OF LOAN_RATES_REC_TYPE INDEX BY BINARY_INTEGER;
180
181 -- custom schedule
182 TYPE LOAN_CUST_SCHED_REC_TYPE IS RECORD (
183 PAYMENT_NUMBER NUMBER -- installment number; do not pass
184 ,DUE_DATE DATE -- installment due date; either due date or relative due date MUST BE PASSED
185 ,RELATIVE_DATE VARCHAR2(30) -- installment relative due date; either due date or relative due date MUST BE PASSED
186 ,PRINCIPAL_AMOUNT NUMBER -- principal amount; optional
187 ,PRINCIPAL_PERCENT NUMBER -- principal percentage; optional
188 ,INTEREST_AMOUNT NUMBER --interest amount; optional
189 ,LOCK_PRIN VARCHAR2(1) -- lock principal flag; valid values: N, Y, P; if null will be set to N
193
190 ,LOCK_INT VARCHAR2(1) -- lock interest flag; valid values: N, Y, D; if null will be set to N
191 );
192 TYPE LOAN_CUST_SCHED_TBL_TYPE IS TABLE OF LOAN_CUST_SCHED_REC_TYPE INDEX BY BINARY_INTEGER;
194 -- accounting
195 type distribution_rec is record(
196 LINE_TYPE VARCHAR2(30) -- mandatory
197 ,ACCOUNT_NAME VARCHAR2(30) -- mandatory
198 ,CODE_COMBINATION_ID NUMBER -- mandatory
199 ,ACCOUNT_TYPE VARCHAR2(30) -- mandatory
200 ,DISTRIBUTION_PERCENT NUMBER -- mandatory
201 ,DISTRIBUTION_AMOUNT NUMBER -- mandatory
202 ,DISTRIBUTION_TYPE VARCHAR2(30) -- mandatory
203 ,USSGL_TRANSACTION_CODE VARCHAR2(30) -- optional
204 );
205 type distribution_tbl is table of distribution_rec index by binary_integer;
206
207 TYPE Loan_create_errors_type IS TABLE OF LNS_LOAN_CREATE_ERRORS_GT%ROWTYPE;
208
209
210 /*#
211 * Creates a loan
212 * @param p_api_version API Version Number
213 * @param P_INIT_MSG_LIST Init message stack flag
214 * @param p_commit Commit flag
215 * @param P_VALIDATION_LEVEL Validation level
216 * @param P_Loan_Details_Rec Loan Details required to create a loan
217 * @param P_Loan_Lines_Tbl Loan Lines required for ERS loan
218 * @param P_DISB_TBL Loan Disbursements for direct loan
219 * @param P_LOAN_PART_TBL Table of additional loan participants
220 * @param P_TERM_RATES_TBL Rate schedule for term phase
221 * @param P_OPEN_RATES_TBL Rate schedule for open phase
222 * @param p_loan_cust_sched_tbl Custom amortization schedule
223 * @param P_Application_id Application Id for the source application
224 * @param P_Created_by_module User Module Identification
225 * @param X_loan_id Loan Id if the loan creates successfully
226 * @param X_return_status API return status
227 * @param X_msg_count Number of error messages
228 * @param X_MSG_DATA API return errors
229 */
230 -- * @rep:scope internal
231 -- * @rep:displayname Create Loan
232 -- * @rep:lifecycle active
233 -- * @rep:compatibility S
234
235 /*========================================================================
236 | PUBLIC PROCEDURE CREATE_LOAN
237 |
238 | DESCRIPTION
239 | This process Creates a Loan and creates corresponding records in
240 | LNS_TERMS, LNS_RATE_SCHEDULES, LNS_PARTICIPANTS and LNS_LOAN_LINES, LNS_CUSTOM_PAYMNT_SCHEDS etc.
241 |
242 | PSEUDO CODE/LOGIC
243 |
244 | PARAMETERS
245 | P_API_VERSION - API Version
246 | P_COMMIT - Passing 'Y' will result in an explicit commit
247 | being issued in the API.
248 | P_Loan_Details_Rec - Please see documentation elsewhere for
249 | Loan_Details_Rec_Type.
250 | P_Loan_Lines_Rec - Please see documentation elsewhere for
251 | Loan_Lines_List_Type. Needs to be populated only
252 | for extended payment plan loans.
253 | P_DISB_TBL - Table of disbursements. Only for direct loans
254 | P_LOAN_PART_TBL - Table of additional loan participants.
255 | P_TERM_RATES_TBL - Rate schedule for loan's term phase.
256 | P_OPEN_RATES_TBL - Rate schedule for loan's open phase.
257 | p_loan_cust_sched_tbl - Custom amortization schedule.
258 | P_Application_id - Application_id for the source application.
259 | P_Created_by_module- Free form text not validated.
260 | X_Loan_id - Loan_id of the created loan will be returned if
261 | successful.
262 | X_RETURN_STATUS - Returns 'S' for success and 'F' for Failure.
263 | X_MSG_COUNT - Returns number of Errors.The errors are inserted
264 | in the Global temporary table
265 | LNS_LOAN_CREATE_ERRORS_GT.
266 | KNOWN ISSUES
267 | None
268 |
269 | NOTES
270 |
271 | MODIFICATION HISTORY
272 | Date Author Description of Changes
273 | 17-Jan-2006 GBELLARY Created
274 | 17-May-2009 scherkas Updated for bug 6936893
275 |
276 *=======================================================================*/
277 PROCEDURE CREATE_LOAN(
278 P_API_VERSION IN NUMBER,
279 P_INIT_MSG_LIST IN VARCHAR2,
280 P_COMMIT IN VARCHAR2,
281 P_VALIDATION_LEVEL IN NUMBER,
282 P_Loan_Details_Rec IN OUT NOCOPY LNS_LOAN_PUB.Loan_Details_Rec_Type, -- mandatory
283 P_Loan_Lines_Tbl IN OUT NOCOPY LNS_LOAN_PUB.Loan_Lines_Tbl_Type, --only for ERS loan
284 P_DISB_TBL IN OUT NOCOPY LNS_LOAN_PUB.Loan_Disb_Tbl_Type, -- only for direct loans
285 P_LOAN_PART_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_PART_TBL_TYPE, -- optional
286 P_OPEN_RATES_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- optional
287 P_TERM_RATES_TBL IN OUT NOCOPY LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- optional
288 p_loan_cust_sched_tbl IN OUT NOCOPY LNS_LOAN_PUB.loan_cust_sched_tbl_type, -- optional
289 P_Application_id IN NUMBER,
290 P_Created_by_module IN VARCHAR2,
291 X_LOAN_ID OUT NOCOPY NUMBER,
292 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
293 X_MSG_COUNT OUT NOCOPY NUMBER,
294 X_MSG_DATA OUT NOCOPY VARCHAR2);
295 END LNS_LOAN_PUB;
[Home] [Help]