1 PACKAGE LNS_IMPORT_LOAN_PUB AUTHID CURRENT_USER as
2 /* $Header: LNS_IMPORT_LOAN_S.pls 120.5 2011/01/06 14:01:46 scherkas noship $ */
3 /*#
4 * Start of Comments
5 * Package name : LNS_IMPORT_LOAN_PUB
6 * Purpose Import a loan
7 * History :
8 */
9 -- * @rep:scope public
10 -- * @rep:product LNS
11 -- * @rep:displayname Import 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 ,funded_amount NUMBER -- do not pass; will be returned by api
31 ,loan_application_date DATE -- Loan application date; optional; if null will be defaulted to loan_start_date
32 ,exchange_rate_type VARCHAR2(30) -- FK to gl_daily_conversion_types; mandatory if product's currency is different from functional currency
33 ,exchange_rate NUMBER -- exchange_rate for USER exchange_rate_type
34 ,exchange_date DATE -- exchange_date; mandatory if exchange_rate_type is not null
35 ,loan_purpose_code VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LOAN_PURPOSE'; mandatory
36 ,loan_subtype VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LOAN_SUBTYPE'; mandatory
37 ,credit_review_flag VARCHAR2(1) -- 'Y' or 'N' or null; optional
38 ,trx_type_id NUMBER(15) -- principal trx type; FK to ar_lookups.lookup_code; lookup_type = 'INV/CM'; mandatory
39 ,collateral_percent NUMBER -- collateral_percent; optional
40 ,CUSTOM_PAYMENTS_FLAG VARCHAR2(1) -- valid values: null or N or Y; optional
41 ,FORGIVENESS_FLAG VARCHAR2(1) -- enable forgiveness: valid values: Y and N; optional; if null will be defaulted from product_id
42 ,FORGIVENESS_PERCENT NUMBER -- forgiveness percent; valid values from 0 to 100; optional; if null will be defaulted from product_id
43 ,loan_approval_date DATE -- loan approval date; mandatory
44 ,loan_approved_by NUMBER -- loan approval by; mandatory
45 ,LEGACY_REFERENCE VARCHAR2(20) -- legacy reference; optional
46
47 -- do not pass these parameters; will be returned by api
48 ,org_id NUMBER -- do not pass; will be returned by api
49 ,loan_type_id NUMBER -- do not pass; will be returned by api
50 ,loan_class_code VARCHAR2(30) -- do not pass; will be returned by api
51 ,loan_currency VARCHAR2(15) -- do not pass; will be returned by api
52 ,maturity_date DATE -- do not pass; will be returned by api
53 ,NEXT_PAYMENT_DUE_DATE DATE -- do not pass; will be returned by api
54
55 -- primary borrower attributes
56 ,primary_borrower_party_id NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; mandatory
57 ,cust_account_id NUMBER(15) -- FK to hz_cust_accounts_all.cust_account_id; Note: Status should be 'A'; mandatory
58 ,bill_to_acct_site_id NUMBER(15) -- FK to hz_cust_acct_sites_all; mandatory
59 ,contact_rel_party_id NUMBER(15) -- FK to hz_relationships.party_id; optional
60 ,CONTACT_PERS_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; optional
61
62 -- common term attributes
63 ,RATE_TYPE VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'RATE_TYPE'; optional; if null will be defaulted from product_id
64 ,INDEX_RATE_ID NUMBER -- FK to lns_int_rate_headers.interest_rate_id; optional; if null will be defaulted from product_id
65 ,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
66 ,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
67 ,CALCULATION_METHOD VARCHAR2(30) -- interest calculation method: SIMPLE or COMPOUND; optional; if null will be defaulted from product_id
68 ,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
69 ,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
70 ,CUSTOM_CALC_METHOD VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = CUSTOM_CALCULATION_METHOD; mandatory if CUSTOM_PAYMENTS_FLAG = Y
71 ,ORIG_PAY_CALC_METHOD VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = PAYMENT_CALCULATION_METHOD; optional
72 ,PENAL_INT_RATE NUMBER -- penal interest rate; if null will be defaulted from product_id else will be defaulted to 0
73 ,PENAL_INT_GRACE_DAYS NUMBER -- penal interest grace days; if null will be defaulted from product_id else will be defaulted to 0
74 ,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;
75 ,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;
76 ,LOCK_DATE DATE -- rate lock date; optional; optional
77 ,LOCK_EXP_DATE DATE -- rate lock experation date; optional
78
79 -- 'term phase' term attributes
80 ,loan_term NUMBER -- term phase term; optional; if null will be defaulted from product_id
81 ,loan_term_period VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'PERIOD'; optional; if null will be defaulted from product_id
82 ,balloon_payment_type VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'BALLOON_PAYMENT_TYPE'; optional; if null will be defaulted to TERM
83 ,balloon_payment_amount NUMBER -- balloon amount for term phase; mandatory if balloon_payment_type = 'AMOUNT'; if null will be defaulted to 0
84 ,balloon_term NUMBER -- balloon term for term phase; mandatory if balloon_payment_type = 'TERM'; if null will be defaulted to loan_term
85 ,loan_start_date DATE -- term phase start date; mandatory; if null will be defaulted to sysdate
86 ,FIRST_PAYMENT_DATE DATE -- term phase loan/interest first payment date; mandatory; if null will be defaulted to loan_start_date+1 payment_period
87 ,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
88 ,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
89 ,floor_rate NUMBER -- term phase floor rate; optional; only applies when RATE_TYPE = FLOATING
90 ,ceiling_rate NUMBER -- term phase ceiling rate; optional; only applies when RATE_TYPE = FLOATING
91 ,percent_increase NUMBER -- term phase max sequential rate percent increase; optional; only applies when RATE_TYPE = FLOATING
92 ,percent_increase_life NUMBER -- term phase max rate percent increase for life of the loan; optional; only applies when RATE_TYPE = FLOATING
93
94 -- Terms for Payment attributes
95 ,REAMORTIZE_OVER_PAYMENT VARCHAR2(1) -- reamortize overpayment flag; optional; valid values: Y or N or null;
96 ,DELINQUENCY_THRESHOLD_AMOUNT NUMBER -- delinquency amount; optional; if null will be defaulted by api;
97 ,PAYMENT_APPLICATION_ORDER VARCHAR2(30) -- payment application order; optional; if null will be defaulted from loan_type_id
98 ,PMT_APPL_ORDER_SCOPE VARCHAR2(30) -- payment application scope; optional; if null will be defaulted from loan_type_id
99 ,REAMORTIZE_ON_FUNDING VARCHAR2(30) -- reamortize on funding setting; optional; if null will be defaulted from product_id; valid values: NO, REST, IMMEDIATELY;
100
101 -- additional optional attributes
102 ,ATTRIBUTE_CATEGORY VARCHAR2(30) -- optional
103 ,ATTRIBUTE1 VARCHAR2(150) -- optional
104 ,ATTRIBUTE2 VARCHAR2(150) -- optional
105 ,ATTRIBUTE3 VARCHAR2(150) -- optional
106 ,ATTRIBUTE4 VARCHAR2(150) -- optional
107 ,ATTRIBUTE5 VARCHAR2(150) -- optional
108 ,ATTRIBUTE6 VARCHAR2(150) -- optional
109 ,ATTRIBUTE7 VARCHAR2(150) -- optional
110 ,ATTRIBUTE8 VARCHAR2(150) -- optional
111 ,ATTRIBUTE9 VARCHAR2(150) -- optional
112 ,ATTRIBUTE10 VARCHAR2(150) -- optional
113 ,ATTRIBUTE11 VARCHAR2(150) -- optional
114 ,ATTRIBUTE12 VARCHAR2(150) -- optional
115 ,ATTRIBUTE13 VARCHAR2(150) -- optional
116 ,ATTRIBUTE14 VARCHAR2(150) -- optional
117 ,ATTRIBUTE15 VARCHAR2(150) -- optional
118 ,ATTRIBUTE16 VARCHAR2(150) -- optional
119 ,ATTRIBUTE17 VARCHAR2(150) -- optional
120 ,ATTRIBUTE18 VARCHAR2(150) -- optional
121 ,ATTRIBUTE19 VARCHAR2(150) -- optional
122 ,ATTRIBUTE20 VARCHAR2(150) -- optional
123 );
124
125 -- loan lines; mandatory for ERS loans
126 TYPE Loan_Lines_Rec_Type IS RECORD (
127 REFERENCE_NUMBER VARCHAR2(60) -- reference number; mandatory
128 ,REFERENCE_DESCRIPTION VARCHAR2(250) -- Free form Text; optional
129 ,AMOUNT NUMBER -- line amount; mandatory
130 );
131 TYPE Loan_Lines_Tbl_Type IS TABLE OF Loan_Lines_Rec_Type INDEX BY BINARY_INTEGER;
132
133 -- loan disbursements; mandatory for direct loans
134 TYPE Loan_Disb_Rec_Type IS RECORD (
135 ACTIVITY_CODE VARCHAR2(30) -- disbursement predefined activity code; optional
136 ,DESCRIPTION VARCHAR2(250) -- disbursement description; either ACTIVITY_CODE or DESCRIPTION must be passed
137 ,AMOUNT NUMBER -- disbursement amount; mandatory
138 ,DUE_DATE DATE -- disbursement due date; mandatory
139 ,SUBMISSION_DATE DATE -- disbursement submission date; mandatory if DISBURSEMENT_DATE is not null
140 ,DISBURSEMENT_DATE DATE -- disbursement payment date; optional
141 ,PAYEE_PARTY_ID NUMBER -- disbursement party id; mandatory
142 ,BANK_ACCOUNT_ID NUMBER -- disbursement bank account id; optional
143 ,PAYMENT_METHOD_CODE VARCHAR2(30) -- disbursement payment method; mandatory if DISBURSEMENT_DATE is not null
144 );
145 TYPE Loan_Disb_Tbl_Type IS TABLE OF Loan_Disb_Rec_Type INDEX BY BINARY_INTEGER;
146
147 -- loan participants
148 TYPE LOAN_PART_REC_TYPE IS RECORD (
149 HZ_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; mandatory
150 ,LOAN_PARTICIPANT_TYPE VARCHAR2(30) -- FK to lns_lookups.lookup_code; lookup_type = 'LNS_PARTICIPANT_TYPE' and lookup_code <> 'PRIMARY_BORROWER'; mandatory
151 ,START_DATE_ACTIVE DATE -- optional
152 ,END_DATE_ACTIVE DATE -- optional
153 ,CUST_ACCOUNT_ID NUMBER(15) -- FK to hz_cust_accounts_all.cust_account_id; Note: Status should be 'A'; mandatory
154 ,BILL_TO_ACCT_SITE_ID NUMBER(15) -- FK to hz_cust_acct_sites_all; mandatory
155 ,CONTACT_PERS_PARTY_ID NUMBER(15) -- FK to hz_parties.party_id; Note: Status should be 'A'; optional
156 ,CONTACT_REL_PARTY_ID NUMBER(15) -- FK to hz_relationships.party_id; optional
157 );
158 TYPE LOAN_PART_TBL_TYPE IS TABLE OF LOAN_PART_REC_TYPE INDEX BY BINARY_INTEGER;
159
160 -- loan rate schedule
161 TYPE LOAN_RATES_REC_TYPE IS RECORD (
162 INDEX_RATE NUMBER -- index interest rate for this rate record; mandatory
163 ,SPREAD NUMBER -- interest rate spread for this rate record; optional
164 ,INDEX_DATE DATE -- optional; if passed INDEX_RATE will be defaulted from this date
165 ,BEGIN_INSTALLMENT_NUMBER NUMBER -- BEGIN_INSTALLMENT_NUMBER for this rate record; mandatory
166 ,END_INSTALLMENT_NUMBER NUMBER -- END_INSTALLMENT_NUMBER for this rate record; mandatory
167 ,INTEREST_ONLY_FLAG VARCHAR2(1) -- 'Y' or 'N' or null; optional
168 );
169 TYPE LOAN_RATES_TBL_TYPE IS TABLE OF LOAN_RATES_REC_TYPE INDEX BY BINARY_INTEGER;
170
171 -- custom schedule
172 TYPE LOAN_CUST_SCHED_REC_TYPE IS RECORD (
173 PAYMENT_NUMBER NUMBER -- installment number; do not pass, will be assigned by the system
174 ,DUE_DATE DATE -- installment due date; either due date or relative due date MUST BE PASSED
175 ,RELATIVE_DATE VARCHAR2(30) -- installment relative due date; either due date or relative due date MUST BE PASSED
176 ,PRINCIPAL_AMOUNT NUMBER -- principal amount; optional
177 ,PRINCIPAL_PERCENT NUMBER -- principal percentage; optional
178 ,INTEREST_AMOUNT NUMBER --interest amount; optional
179 ,LOCK_PRIN VARCHAR2(1) -- lock principal flag; valid values: N, Y, P; if null will be set to N
180 ,LOCK_INT VARCHAR2(1) -- lock interest flag; valid values: N, Y, D; if null will be set to N
181 );
182 TYPE LOAN_CUST_SCHED_TBL_TYPE IS TABLE OF LOAN_CUST_SCHED_REC_TYPE INDEX BY BINARY_INTEGER;
183
184 -- accounting
185 type distribution_rec is record(
186 LINE_TYPE VARCHAR2(30) -- mandatory
187 ,ACCOUNT_NAME VARCHAR2(30) -- mandatory
188 ,CODE_COMBINATION_ID NUMBER -- mandatory
189 ,ACCOUNT_TYPE VARCHAR2(30) -- mandatory
190 ,DISTRIBUTION_PERCENT NUMBER -- mandatory
191 ,DISTRIBUTION_AMOUNT NUMBER -- mandatory
192 ,DISTRIBUTION_TYPE VARCHAR2(30) -- mandatory
193 );
194 type distribution_tbl is table of distribution_rec index by binary_integer;
195
196 -- billing/payment history
197 TYPE PAYMENT_HIST_REC_TYPE IS RECORD (
198 PAYMENT_NUMBER NUMBER
199 ,DUE_DATE DATE
200 ,BILLED_PRIN NUMBER
201 ,BILLED_INT NUMBER
202 ,BILLED_FEE NUMBER
203 ,SOURCE VARCHAR2(30)
204 ,PAID_PRIN NUMBER
205 ,PAID_INT NUMBER
206 ,PAID_FEE NUMBER
207 ,PAID_DATE DATE
208 ,RC_ID NUMBER
209 ,RC_METHOD_ID NUMBER
210 );
211 TYPE PAYMENT_HIST_TBL_TYPE IS TABLE OF PAYMENT_HIST_REC_TYPE INDEX BY BINARY_INTEGER;
212
213 TYPE Loan_create_errors_type IS TABLE OF LNS_LOAN_CREATE_ERRORS_GT%ROWTYPE;
214
215
216 /*#
217 * Imports a loan
218 * @param p_api_version API Version Number
219 * @param P_INIT_MSG_LIST Init message stack flag
220 * @param p_commit Commit flag
221 * @param P_VALIDATION_LEVEL Validation level
222 * @param P_Loan_Details_Rec Loan Details required to create a loan
223 * @param P_Loan_Lines_Tbl Loan Lines required for ERS loan
224 * @param P_DISB_TBL Loan Disbursements for direct loan
225 * @param P_LOAN_PART_TBL Table of additional loan participants
226 * @param P_LOAN_RATES_TBL Rate schedule for term phase
227 * @param p_loan_cust_sched_tbl Custom amortization schedule
228 * @param p_distribution_tbl Table of distribution records
229 * @param P_PAY_HIST_TBL Table of payment history
230 * @param X_loan_id Loan Id if the loan creates successfully
231 * @param X_return_status API return status
232 * @param X_msg_count Number of error messages
233 * @param X_MSG_DATA API return errors
234 */
235 -- * @rep:scope internal
236 -- * @rep:displayname Import Loan
237 -- * @rep:lifecycle active
238 -- * @rep:compatibility S
239
240 /*========================================================================
241 | PUBLIC PROCEDURE IMPORT_LOAN
242 |
243 | DESCRIPTION
244 | This procedure imports single loan.
245 |
246 | PSEUDO CODE/LOGIC
247 |
248 | PARAMETERS
249 | P_API_VERSION IN Standard in parameter
250 | P_INIT_MSG_LIST IN Standard in parameter
251 | P_COMMIT IN Standard in parameter
252 | P_VALIDATION_LEVEL IN Standard in parameter
253 | P_Loan_Details_Rec IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Details_Rec_Type -- mandatory
254 | P_Loan_Lines_Tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Lines_Tbl_Type -- mandatory for ERS loans
255 | P_DISB_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Disb_Tbl_Type, -- mandatory for direct loans
256 | P_LOAN_PART_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_PART_TBL_TYPE -- optional
257 | P_LOAN_RATES_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_RATES_TBL_TYPE -- mandatory
258 | p_loan_cust_sched_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.loan_cust_sched_tbl_type -- optional
259 | p_distribution_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.distribution_tbl -- optional
260 | P_PAY_HIST_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE -- optional
261 | X_LOAN_ID OUT NOCOPY NUMBER,
262 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
263 | X_MSG_COUNT OUT NOCOPY Standard out parameter
264 | X_MSG_DATA OUT NOCOPY Standard out parameter
265 |
266 | KNOWN ISSUES
267 | None
268 |
269 | NOTES
270 |
271 | MODIFICATION HISTORY
272 | Date Author Description of Changes
273 | 05-20-2009 scherkas Created for bug 6936893
274 |
275 *=======================================================================*/
276 PROCEDURE IMPORT_LOAN(
277 P_API_VERSION IN NUMBER,
278 P_INIT_MSG_LIST IN VARCHAR2,
279 P_COMMIT IN VARCHAR2,
280 P_VALIDATION_LEVEL IN NUMBER,
281 P_Loan_Details_Rec IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Details_Rec_Type, -- mandatory
282 P_Loan_Lines_Tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Lines_Tbl_Type, -- mandatory for ERS loans
283 P_DISB_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.Loan_Disb_Tbl_Type, -- mandatory for direct loans
284 P_LOAN_PART_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_PART_TBL_TYPE, -- optional
285 P_LOAN_RATES_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- mandatory
286 p_loan_cust_sched_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.loan_cust_sched_tbl_type, -- optional
287 p_distribution_tbl IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.distribution_tbl, -- optional
288 P_PAY_HIST_TBL IN OUT NOCOPY LNS_IMPORT_LOAN_PUB.PAYMENT_HIST_TBL_TYPE, -- optional
289 X_LOAN_ID OUT NOCOPY NUMBER,
290 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
291 X_MSG_COUNT OUT NOCOPY NUMBER,
292 X_MSG_DATA OUT NOCOPY VARCHAR2);
293
294
295
296 END LNS_IMPORT_LOAN_PUB;
[Home] [Help]