[Home] [Help]
PACKAGE BODY: APPS.LNS_LOAN_HEADER_ALL_PKG
Source
1 PACKAGE BODY LNS_LOAN_HEADER_ALL_PKG AS
2 /*$Header: LNS_LNHDR_TBLH_B.pls 120.15 2011/10/25 20:38:34 scherkas ship $ */
3
4 PROCEDURE Insert_Row (
5 X_LOAN_ID IN OUT NOCOPY NUMBER,
6 X_ORG_ID IN NUMBER,
7 X_LOAN_NUMBER IN OUT NOCOPY VARCHAR2,
8 X_LOAN_DESCRIPTION IN VARCHAR2,
9 X_OBJECT_VERSION_NUMBER IN NUMBER,
10 X_LOAN_APPLICATION_DATE IN DATE,
11 X_END_DATE IN DATE,
12 X_INITIAL_LOAN_BALANCE IN NUMBER,
13 X_LAST_PAYMENT_DATE IN DATE,
14 X_LAST_PAYMENT_AMOUNT IN NUMBER,
15 X_LOAN_TERM IN NUMBER,
16 X_LOAN_TERM_PERIOD IN VARCHAR2,
17 X_AMORTIZED_TERM IN NUMBER,
18 X_AMORTIZED_TERM_PERIOD IN VARCHAR2,
19 X_LOAN_STATUS IN VARCHAR2,
20 X_LOAN_ASSIGNED_TO IN NUMBER,
21 X_LOAN_CURRENCY IN VARCHAR2,
22 X_LOAN_CLASS_CODE IN VARCHAR2,
23 X_LOAN_TYPE IN VARCHAR2,
24 X_LOAN_SUBTYPE IN VARCHAR2,
25 X_LOAN_PURPOSE_CODE IN VARCHAR2,
26 X_CUST_ACCOUNT_ID IN NUMBER,
27 X_BILL_TO_ACCT_SITE_ID IN NUMBER,
28 X_LOAN_MATURITY_DATE IN DATE,
29 X_LOAN_START_DATE IN DATE,
30 X_LOAN_CLOSING_DATE IN DATE,
31 X_REFERENCE_ID IN NUMBER,
32 X_REFERENCE_NUMBER IN VARCHAR2,
33 X_REFERENCE_DESCRIPTION IN VARCHAR2,
34 X_REFERENCE_AMOUNT IN NUMBER,
35 X_PRODUCT_FLAG IN VARCHAR2,
36 X_PRIMARY_BORROWER_ID IN NUMBER,
37 X_PRODUCT_ID IN NUMBER,
38 X_REQUESTED_AMOUNT IN NUMBER,
39 X_FUNDED_AMOUNT IN NUMBER,
40 X_LOAN_APPROVAL_DATE IN DATE,
41 X_LOAN_APPROVED_BY IN NUMBER,
42 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
43 X_ATTRIBUTE1 IN VARCHAR2,
44 X_ATTRIBUTE2 IN VARCHAR2,
45 X_ATTRIBUTE3 IN VARCHAR2,
46 X_ATTRIBUTE4 IN VARCHAR2,
47 X_ATTRIBUTE5 IN VARCHAR2,
48 X_ATTRIBUTE6 IN VARCHAR2,
49 X_ATTRIBUTE7 IN VARCHAR2,
50 X_ATTRIBUTE8 IN VARCHAR2,
51 X_ATTRIBUTE9 IN VARCHAR2,
52 X_ATTRIBUTE10 IN VARCHAR2,
53 X_ATTRIBUTE11 IN VARCHAR2,
54 X_ATTRIBUTE12 IN VARCHAR2,
55 X_ATTRIBUTE13 IN VARCHAR2,
56 X_ATTRIBUTE14 IN VARCHAR2,
57 X_ATTRIBUTE15 IN VARCHAR2,
58 X_ATTRIBUTE16 IN VARCHAR2,
59 X_ATTRIBUTE17 IN VARCHAR2,
60 X_ATTRIBUTE18 IN VARCHAR2,
61 X_ATTRIBUTE19 IN VARCHAR2,
62 X_ATTRIBUTE20 IN VARCHAR2,
63 X_LAST_BILLED_DATE IN DATE,
64 X_CUSTOM_PAYMENTS_FLAG IN VARCHAR2,
65 X_BILLED_FLAG IN VARCHAR2,
66 X_REFERENCE_NAME IN VARCHAR2,
67 X_REFERENCE_TYPE IN VARCHAR2,
68 X_REFERENCE_TYPE_ID IN NUMBER,
69 X_USSGL_TRANSACTION_CODE IN VARCHAR2,
70 X_GL_DATE IN DATE,
71 X_REC_ADJUSTMENT_NUMBER IN VARCHAR2,
72 X_CONTACT_REL_PARTY_ID IN NUMBER,
73 X_CONTACT_PERS_PARTY_ID IN NUMBER,
74 X_CREDIT_REVIEW_FLAG IN VARCHAR2,
75 X_EXCHANGE_RATE_TYPE IN VARCHAR2,
76 X_EXCHANGE_DATE IN DATE,
77 X_EXCHANGE_RATE IN NUMBER,
78 X_COLLATERAL_PERCENT IN NUMBER,
79 X_LAST_PAYMENT_NUMBER IN NUMBER,
80 X_LAST_AMORTIZATION_ID IN NUMBER,
81 X_LEGAL_ENTITY_ID IN NUMBER,
82 X_OPEN_TO_TERM_FLAG IN VARCHAR2,
83 X_MULTIPLE_FUNDING_FLAG IN VARCHAR2,
84 X_LOAN_TYPE_ID IN NUMBER,
85 X_SECONDARY_STATUS IN VARCHAR2,
86 X_OPEN_TO_TERM_EVENT IN VARCHAR2,
87 X_BALLOON_PAYMENT_TYPE IN VARCHAR2,
88 X_BALLOON_PAYMENT_AMOUNT IN NUMBER,
89 X_CURRENT_PHASE IN VARCHAR2,
90 X_OPEN_LOAN_START_DATE IN DATE,
91 X_OPEN_LOAN_TERM IN NUMBER,
92 X_OPEN_LOAN_TERM_PERIOD IN VARCHAR2,
93 X_OPEN_MATURITY_DATE IN DATE,
94 X_FUNDS_RESERVED_FLAG IN VARCHAR2,
95 X_FUNDS_CHECK_DATE IN DATE,
96 X_SUBSIDY_RATE IN NUMBER,
97 X_APPLICATION_ID IN NUMBER,
98 X_CREATED_BY_MODULE IN VARCHAR2,
99 X_PARTY_TYPE IN VARCHAR2,
100 X_FORGIVENESS_FLAG IN VARCHAR2,
101 X_FORGIVENESS_PERCENT IN NUMBER,
102 X_DISABLE_BILLING_FLAG IN VARCHAR2,
103 X_ADD_REQUESTED_AMOUNT IN NUMBER,
104 X_DISABLE_INT_ACCRUAL_FLAG IN VARCHAR2,
105 X_BILL_ON_APPR_AMOUNT_FLAG IN VARCHAR2
106 ) IS
107
108 CURSOR l_insert is
109 SELECT ROWID FROM LNS_LOAN_HEADERS_ALL
110 WHERE LOAN_ID = X_LOAN_ID ;
111
112 l_rowid varchar2(2000);
113
114 --To handle the reset of data due to change in loan currency in the UI
115 L_RESET_EXCHANGE_DATA VARCHAR2(1);
116 BEGIN
117
118 --To handle the reset of data due to change in loan currency in the UI
119 IF (X_EXCHANGE_RATE_TYPE = FND_API.G_MISS_CHAR) THEN
120 L_RESET_EXCHANGE_DATA := 'Y';
121 ELSE
122 L_RESET_EXCHANGE_DATA := 'N';
123 END IF;
124
125 INSERT INTO LNS_LOAN_HEADERS_ALL
126 (
127 LOAN_ID
128 ,LAST_UPDATE_DATE
129 ,LAST_UPDATED_BY
130 ,LAST_UPDATE_LOGIN
131 ,CREATION_DATE
132 ,CREATED_BY
133 ,PROGRAM_ID
134 ,OBJECT_VERSION_NUMBER
135 ,REQUEST_ID
136 ,ORG_ID
137 ,LOAN_NUMBER
138 ,LOAN_APPLICATION_DATE
139 ,END_DATE
140 ,INITIAL_LOAN_BALANCE
141 ,LAST_PAYMENT_DATE
142 ,LAST_PAYMENT_AMOUNT
143 ,LOAN_TERM
144 ,LOAN_TERM_PERIOD
145 ,AMORTIZED_TERM
146 ,AMORTIZED_TERM_PERIOD
147 ,LOAN_STATUS
148 ,LOAN_ASSIGNED_TO
149 ,LOAN_CURRENCY
150 ,LOAN_CLASS_CODE
151 ,LOAN_TYPE
152 ,LOAN_SUBTYPE
153 ,LOAN_PURPOSE_CODE
154 ,CUST_ACCOUNT_ID
155 ,BILL_TO_ACCT_SITE_ID
156 ,LOAN_MATURITY_DATE
157 ,LOAN_START_DATE
158 ,LOAN_CLOSING_DATE
159 ,REFERENCE_ID
160 ,REFERENCE_NUMBER
161 ,REFERENCE_AMOUNT
162 ,PRODUCT_FLAG
163 ,PRIMARY_BORROWER_ID
164 ,PRODUCT_ID
165 ,REQUESTED_AMOUNT
166 ,FUNDED_AMOUNT
167 ,LOAN_APPROVAL_DATE
168 ,LOAN_APPROVED_BY
169 ,ATTRIBUTE_CATEGORY
170 ,ATTRIBUTE1
171 ,ATTRIBUTE2
172 ,ATTRIBUTE3
173 ,ATTRIBUTE4
174 ,ATTRIBUTE5
175 ,ATTRIBUTE6
176 ,ATTRIBUTE7
177 ,ATTRIBUTE8
178 ,ATTRIBUTE9
179 ,ATTRIBUTE10
180 ,ATTRIBUTE11
181 ,ATTRIBUTE12
182 ,ATTRIBUTE13
183 ,ATTRIBUTE14
184 ,ATTRIBUTE15
185 ,ATTRIBUTE16
186 ,ATTRIBUTE17
187 ,ATTRIBUTE18
188 ,ATTRIBUTE19
189 ,ATTRIBUTE20
190 ,LAST_BILLED_DATE
191 ,CUSTOM_PAYMENTS_FLAG
192 ,BILLED_FLAG
193 ,REFERENCE_NAME
194 ,REFERENCE_TYPE
195 ,REFERENCE_TYPE_ID
196 ,USSGL_TRANSACTION_CODE
197 ,GL_DATE
198 ,REC_ADJUSTMENT_NUMBER
199 ,CONTACT_REL_PARTY_ID
200 ,CONTACT_PERS_PARTY_ID
201 ,CREDIT_REVIEW_FLAG
202 ,EXCHANGE_RATE_TYPE
203 ,EXCHANGE_DATE
204 ,EXCHANGE_RATE
205 ,COLLATERAL_PERCENT
206 ,LAST_PAYMENT_NUMBER
207 ,LAST_AMORTIZATION_ID
208 ,LEGAL_ENTITY_ID
209 ,OPEN_TO_TERM_FLAG
210 ,MULTIPLE_FUNDING_FLAG
211 ,LOAN_TYPE_ID
212 ,SECONDARY_STATUS
213 ,OPEN_TO_TERM_EVENT
214 ,BALLOON_PAYMENT_TYPE
215 ,BALLOON_PAYMENT_AMOUNT
216 ,CURRENT_PHASE
217 ,OPEN_LOAN_START_DATE
218 ,OPEN_LOAN_TERM
219 ,OPEN_LOAN_TERM_PERIOD
220 ,OPEN_MATURITY_DATE
221 ,FUNDS_RESERVED_FLAG
222 ,FUNDS_CHECK_DATE
223 ,SUBSIDY_RATE
224 ,APPLICATION_ID
225 ,CREATED_BY_MODULE
226 ,PARTY_TYPE
227 ,FORGIVENESS_FLAG
228 ,FORGIVENESS_PERCENT
229 ,DISABLE_BILLING_FLAG
230 ,ADD_REQUESTED_AMOUNT
231 ,DISABLE_INT_ACCRUAL_FLAG
232 ,BILL_ON_APPR_AMOUNT_FLAG
233 ) VALUES (
234 DECODE( X_LOAN_ID, FND_API.G_MISS_NUM, LNS_LOAN_HEADERS_S.NEXTVAL, NULL, LNS_LOAN_HEADERS_S.NEXTVAL, X_LOAN_ID )
235 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
236 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
237 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
238 ,LNS_UTILITY_PUB.CREATION_DATE
239 ,LNS_UTILITY_PUB.CREATED_BY
240 ,LNS_UTILITY_PUB.PROGRAM_ID
241 ,DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
242 ,LNS_UTILITY_PUB.REQUEST_ID
243 ,DECODE( X_ORG_ID, FND_API.G_MISS_NUM, NULL, X_ORG_ID )
244 ,DECODE( X_LOAN_NUMBER, FND_API.G_MISS_CHAR, 'L'||TO_CHAR(LNS_LOAN_NUMBER_S.NEXTVAL), NULL, 'L'||TO_CHAR(LNS_LOAN_NUMBER_S.NEXTVAL), X_LOAN_NUMBER )
245 ,DECODE( X_LOAN_APPLICATION_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_APPLICATION_DATE )
246 ,DECODE( X_END_DATE, FND_API.G_MISS_DATE, NULL, X_END_DATE )
247 ,DECODE( X_INITIAL_LOAN_BALANCE, FND_API.G_MISS_NUM, NULL, X_INITIAL_LOAN_BALANCE )
248 ,DECODE( X_LAST_PAYMENT_DATE, FND_API.G_MISS_DATE, NULL, X_LAST_PAYMENT_DATE )
249 ,DECODE( X_LAST_PAYMENT_AMOUNT, FND_API.G_MISS_NUM, NULL, X_LAST_PAYMENT_AMOUNT )
250 ,DECODE( X_LOAN_TERM, FND_API.G_MISS_NUM, NULL, X_LOAN_TERM )
251 ,DECODE( X_LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_LOAN_TERM_PERIOD )
252 ,DECODE( X_AMORTIZED_TERM, FND_API.G_MISS_NUM, NULL, X_AMORTIZED_TERM )
253 ,DECODE( X_AMORTIZED_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_AMORTIZED_TERM_PERIOD )
254 ,DECODE( X_LOAN_STATUS, FND_API.G_MISS_CHAR, NULL, X_LOAN_STATUS )
255 ,DECODE( X_LOAN_ASSIGNED_TO, FND_API.G_MISS_NUM, NULL, X_LOAN_ASSIGNED_TO )
256 ,DECODE( X_LOAN_CURRENCY, FND_API.G_MISS_CHAR, NULL, X_LOAN_CURRENCY )
257 ,DECODE( X_LOAN_CLASS_CODE, FND_API.G_MISS_CHAR, NULL, X_LOAN_CLASS_CODE )
258 ,DECODE( X_LOAN_TYPE, FND_API.G_MISS_CHAR, NULL, X_LOAN_TYPE )
259 ,DECODE( X_LOAN_SUBTYPE, FND_API.G_MISS_CHAR, NULL, X_LOAN_SUBTYPE )
260 ,DECODE( X_LOAN_PURPOSE_CODE, FND_API.G_MISS_CHAR, NULL, X_LOAN_PURPOSE_CODE )
261 ,DECODE( X_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ID )
262 ,DECODE( X_BILL_TO_ACCT_SITE_ID, FND_API.G_MISS_NUM, NULL, X_BILL_TO_ACCT_SITE_ID )
263 ,DECODE( X_LOAN_MATURITY_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_MATURITY_DATE )
264 ,DECODE( X_LOAN_START_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_START_DATE )
265 ,DECODE( X_LOAN_CLOSING_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_CLOSING_DATE )
266 ,DECODE( X_REFERENCE_ID, FND_API.G_MISS_NUM, NULL, X_REFERENCE_ID )
267 ,DECODE( X_REFERENCE_NUMBER, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_NUMBER )
268 ,DECODE( X_REFERENCE_AMOUNT, FND_API.G_MISS_NUM, NULL, X_REFERENCE_AMOUNT )
269 ,DECODE( X_PRODUCT_FLAG, FND_API.G_MISS_CHAR, NULL, X_PRODUCT_FLAG )
270 ,DECODE( X_PRIMARY_BORROWER_ID, FND_API.G_MISS_NUM, NULL, X_PRIMARY_BORROWER_ID )
271 ,DECODE( X_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, X_PRODUCT_ID )
272 ,DECODE( X_REQUESTED_AMOUNT, FND_API.G_MISS_NUM, NULL, X_REQUESTED_AMOUNT )
273 ,DECODE( X_FUNDED_AMOUNT, FND_API.G_MISS_NUM, NULL, X_FUNDED_AMOUNT )
274 ,DECODE( X_LOAN_APPROVAL_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_APPROVAL_DATE )
275 ,DECODE( X_LOAN_APPROVED_BY, FND_API.G_MISS_NUM, NULL, X_LOAN_APPROVED_BY )
276 ,DECODE( X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY )
277 ,DECODE( X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 )
278 ,DECODE( X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 )
279 ,DECODE( X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 )
280 ,DECODE( X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 )
281 ,DECODE( X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 )
282 ,DECODE( X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 )
283 ,DECODE( X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 )
284 ,DECODE( X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 )
285 ,DECODE( X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 )
286 ,DECODE( X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 )
287 ,DECODE( X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 )
288 ,DECODE( X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 )
289 ,DECODE( X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 )
290 ,DECODE( X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 )
291 ,DECODE( X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 )
292 ,DECODE( X_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 )
293 ,DECODE( X_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 )
294 ,DECODE( X_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 )
295 ,DECODE( X_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 )
296 ,DECODE( X_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 )
297 ,DECODE( X_LAST_BILLED_DATE, FND_API.G_MISS_CHAR, NULL, X_LAST_BILLED_DATE )
298 ,DECODE( X_CUSTOM_PAYMENTS_FLAG, FND_API.G_MISS_CHAR, NULL, X_CUSTOM_PAYMENTS_FLAG )
299 ,DECODE( X_BILLED_FLAG, FND_API.G_MISS_CHAR, NULL, X_BILLED_FLAG )
300 ,DECODE( X_REFERENCE_NAME, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_NAME )
301 ,DECODE( X_REFERENCE_TYPE, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_TYPE )
302 ,DECODE( X_REFERENCE_TYPE_ID, FND_API.G_MISS_NUM, NULL, X_REFERENCE_TYPE_ID )
303 ,DECODE( X_USSGL_TRANSACTION_CODE, FND_API.G_MISS_CHAR, NULL, X_USSGL_TRANSACTION_CODE )
304 ,DECODE( X_GL_DATE, FND_API.G_MISS_DATE, NULL, X_GL_DATE )
305 ,DECODE( X_REC_ADJUSTMENT_NUMBER, FND_API.G_MISS_CHAR, NULL, X_REC_ADJUSTMENT_NUMBER )
306 ,DECODE( X_CONTACT_REL_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_CONTACT_REL_PARTY_ID )
307 ,DECODE( X_CONTACT_PERS_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_CONTACT_PERS_PARTY_ID )
308 ,DECODE( X_CREDIT_REVIEW_FLAG, FND_API.G_MISS_CHAR, NULL, X_CREDIT_REVIEW_FLAG )
309 ,DECODE( X_EXCHANGE_RATE_TYPE, FND_API.G_MISS_CHAR, NULL, X_EXCHANGE_RATE_TYPE )
310 ,DECODE( L_RESET_EXCHANGE_DATA, 'Y', NULL, ( DECODE( X_EXCHANGE_DATE, FND_API.G_MISS_DATE, NULL, X_EXCHANGE_DATE ) ) )
311 ,DECODE( L_RESET_EXCHANGE_DATA, 'Y', NULL, ( DECODE( X_EXCHANGE_RATE, FND_API.G_MISS_NUM, NULL, X_EXCHANGE_RATE ) ) )
312 ,DECODE( X_COLLATERAL_PERCENT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_COLLATERAL_PERCENT )
313 ,DECODE( X_LAST_PAYMENT_NUMBER, FND_API.G_MISS_NUM, NULL, 0, NULL, X_LAST_PAYMENT_NUMBER )
314 ,DECODE( X_LAST_AMORTIZATION_ID, FND_API.G_MISS_NUM, NULL, 0, NULL, X_LAST_AMORTIZATION_ID )
315 ,DECODE( X_LEGAL_ENTITY_ID, FND_API.G_MISS_NUM, NULL, 0, NULL, X_LEGAL_ENTITY_ID )
316 ,DECODE( X_OPEN_TO_TERM_FLAG, FND_API.G_MISS_CHAR, NULL, X_OPEN_TO_TERM_FLAG )
317 ,DECODE( X_MULTIPLE_FUNDING_FLAG, FND_API.G_MISS_CHAR, NULL, X_MULTIPLE_FUNDING_FLAG )
318 ,DECODE( X_LOAN_TYPE_ID, FND_API.G_MISS_NUM, NULL, 0, NULL, X_LOAN_TYPE_ID )
319 ,DECODE( X_SECONDARY_STATUS, FND_API.G_MISS_CHAR, NULL, X_SECONDARY_STATUS )
320 ,DECODE( X_OPEN_TO_TERM_EVENT, FND_API.G_MISS_CHAR, NULL, X_OPEN_TO_TERM_EVENT )
321 ,DECODE( X_BALLOON_PAYMENT_TYPE, FND_API.G_MISS_CHAR, NULL, X_BALLOON_PAYMENT_TYPE )
322 ,DECODE( X_BALLOON_PAYMENT_AMOUNT, FND_API.G_MISS_NUM, NULL, X_BALLOON_PAYMENT_AMOUNT )
323 ,DECODE( X_CURRENT_PHASE, FND_API.G_MISS_CHAR, NULL, X_CURRENT_PHASE )
324 ,DECODE( X_OPEN_LOAN_START_DATE, FND_API.G_MISS_DATE, NULL, X_OPEN_LOAN_START_DATE )
325 ,DECODE( X_OPEN_LOAN_TERM, FND_API.G_MISS_NUM, NULL, X_OPEN_LOAN_TERM )
326 ,DECODE( X_OPEN_LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_OPEN_LOAN_TERM_PERIOD )
327 ,DECODE( X_OPEN_MATURITY_DATE, FND_API.G_MISS_DATE, NULL, X_OPEN_MATURITY_DATE )
328 ,DECODE( X_FUNDS_RESERVED_FLAG, FND_API.G_MISS_CHAR, NULL, X_FUNDS_RESERVED_FLAG )
329 ,DECODE( X_FUNDS_CHECK_DATE, FND_API.G_MISS_DATE, NULL, X_FUNDS_CHECK_DATE )
330 ,DECODE( X_SUBSIDY_RATE, FND_API.G_MISS_NUM, NULL, X_SUBSIDY_RATE )
331 ,DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
332 ,DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE )
333 ,DECODE( X_PARTY_TYPE, FND_API.G_MISS_CHAR, NULL, X_PARTY_TYPE )
334 ,DECODE( X_FORGIVENESS_FLAG, FND_API.G_MISS_CHAR, NULL, X_FORGIVENESS_FLAG )
335 ,DECODE( X_FORGIVENESS_PERCENT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_FORGIVENESS_PERCENT )
336 ,DECODE( X_DISABLE_BILLING_FLAG, FND_API.G_MISS_CHAR, NULL, X_DISABLE_BILLING_FLAG )
337 ,DECODE( X_ADD_REQUESTED_AMOUNT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_ADD_REQUESTED_AMOUNT )
338 ,DECODE( X_DISABLE_INT_ACCRUAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_DISABLE_INT_ACCRUAL_FLAG )
339 ,DECODE( X_BILL_ON_APPR_AMOUNT_FLAG, FND_API.G_MISS_CHAR, NULL, X_BILL_ON_APPR_AMOUNT_FLAG )
340
341 ) RETURNING
342 LOAN_ID,
343 LOAN_NUMBER
344 INTO
345 X_LOAN_ID,
346 X_LOAN_NUMBER;
347
348
349 INSERT INTO LNS_LOAN_HEADERS_ALL_TL (
350 LOAN_ID
351 ,LOAN_DESCRIPTION
352 ,REFERENCE_DESCRIPTION
353 ,LANGUAGE
354 ,SOURCE_LANG
355 ,CREATED_BY
356 ,CREATION_DATE
357 ,LAST_UPDATE_DATE
358 ,LAST_UPDATED_BY
359 ,LAST_UPDATE_LOGIN
360 ) SELECT
361 X_LOAN_ID
362 ,DECODE( X_LOAN_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_LOAN_DESCRIPTION )
363 ,DECODE( X_REFERENCE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_DESCRIPTION )
364 ,L.LANGUAGE_CODE
365 ,userenv('LANG')
366 ,fnd_global.user_id
367 ,sysdate
368 ,sysdate
369 ,fnd_global.user_id
370 ,fnd_global.user_id
371 FROM FND_LANGUAGES L
372 where L.INSTALLED_FLAG in ('I', 'B')
373 AND not exists
374 (select NULL
375 from LNS_LOAN_HEADERS_ALL_TL T
376 where T.LOAN_ID = X_LOAN_ID
377 and T.LANGUAGE = L.LANGUAGE_CODE);
378
379 OPEN l_insert;
380 FETCH l_insert INTO l_rowid;
381 IF (l_insert%notfound) THEN
382 CLOSE l_insert;
383 RAISE no_data_found;
384 END IF;
385 CLOSE l_insert;
386
387 END Insert_Row;
388
389 PROCEDURE Update_Row(
390 X_Rowid IN OUT NOCOPY VARCHAR2,
391 X_LOAN_ID IN NUMBER,
392 X_ORG_ID IN NUMBER,
393 X_LOAN_NUMBER IN VARCHAR2,
394 X_LOAN_DESCRIPTION IN VARCHAR2,
395 X_OBJECT_VERSION_NUMBER IN NUMBER,
396 X_LOAN_APPLICATION_DATE IN DATE,
397 X_END_DATE IN DATE,
398 X_INITIAL_LOAN_BALANCE IN NUMBER,
399 X_LAST_PAYMENT_DATE IN DATE,
400 X_LAST_PAYMENT_AMOUNT IN NUMBER,
401 X_LOAN_TERM IN NUMBER,
402 X_LOAN_TERM_PERIOD IN VARCHAR2,
403 X_AMORTIZED_TERM IN NUMBER,
404 X_AMORTIZED_TERM_PERIOD IN VARCHAR2,
405 X_LOAN_STATUS IN VARCHAR2,
406 X_LOAN_ASSIGNED_TO IN NUMBER,
407 X_LOAN_CURRENCY IN VARCHAR2,
408 X_LOAN_CLASS_CODE IN VARCHAR2,
409 X_LOAN_TYPE IN VARCHAR2,
410 X_LOAN_SUBTYPE IN VARCHAR2,
411 X_LOAN_PURPOSE_CODE IN VARCHAR2,
412 X_CUST_ACCOUNT_ID IN NUMBER,
413 X_BILL_TO_ACCT_SITE_ID IN NUMBER,
414 X_LOAN_MATURITY_DATE IN DATE,
415 X_LOAN_START_DATE IN DATE,
416 X_LOAN_CLOSING_DATE IN DATE,
417 X_REFERENCE_ID IN NUMBER,
418 X_REFERENCE_NUMBER IN VARCHAR2,
419 X_REFERENCE_DESCRIPTION IN VARCHAR2,
420 X_REFERENCE_AMOUNT IN NUMBER,
421 X_PRODUCT_FLAG IN VARCHAR2,
422 X_PRIMARY_BORROWER_ID IN NUMBER,
423 X_PRODUCT_ID IN NUMBER,
424 X_REQUESTED_AMOUNT IN NUMBER,
425 X_FUNDED_AMOUNT IN NUMBER,
426 X_LOAN_APPROVAL_DATE IN DATE,
427 X_LOAN_APPROVED_BY IN NUMBER,
428 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
429 X_ATTRIBUTE1 IN VARCHAR2,
430 X_ATTRIBUTE2 IN VARCHAR2,
431 X_ATTRIBUTE3 IN VARCHAR2,
432 X_ATTRIBUTE4 IN VARCHAR2,
433 X_ATTRIBUTE5 IN VARCHAR2,
434 X_ATTRIBUTE6 IN VARCHAR2,
435 X_ATTRIBUTE7 IN VARCHAR2,
436 X_ATTRIBUTE8 IN VARCHAR2,
437 X_ATTRIBUTE9 IN VARCHAR2,
438 X_ATTRIBUTE10 IN VARCHAR2,
439 X_ATTRIBUTE11 IN VARCHAR2,
440 X_ATTRIBUTE12 IN VARCHAR2,
441 X_ATTRIBUTE13 IN VARCHAR2,
442 X_ATTRIBUTE14 IN VARCHAR2,
443 X_ATTRIBUTE15 IN VARCHAR2,
444 X_ATTRIBUTE16 IN VARCHAR2,
445 X_ATTRIBUTE17 IN VARCHAR2,
446 X_ATTRIBUTE18 IN VARCHAR2,
447 X_ATTRIBUTE19 IN VARCHAR2,
448 X_ATTRIBUTE20 IN VARCHAR2,
449 X_LAST_BILLED_DATE IN DATE,
450 X_CUSTOM_PAYMENTS_FLAG IN VARCHAR2,
451 X_BILLED_FLAG IN VARCHAR2,
452 X_REFERENCE_NAME IN VARCHAR2,
453 X_REFERENCE_TYPE IN VARCHAR2,
454 X_REFERENCE_TYPE_ID IN NUMBER,
455 X_USSGL_TRANSACTION_CODE IN VARCHAR2,
456 X_GL_DATE IN DATE,
457 X_REC_ADJUSTMENT_NUMBER IN VARCHAR2,
458 X_CONTACT_REL_PARTY_ID IN NUMBER,
459 X_CONTACT_PERS_PARTY_ID IN NUMBER,
460 X_CREDIT_REVIEW_FLAG IN VARCHAR2,
461 X_EXCHANGE_RATE_TYPE IN VARCHAR2,
462 X_EXCHANGE_DATE IN DATE,
463 X_EXCHANGE_RATE IN NUMBER,
464 X_COLLATERAL_PERCENT IN NUMBER,
465 X_LAST_PAYMENT_NUMBER IN NUMBER,
466 X_LAST_AMORTIZATION_ID IN NUMBER,
467 X_LEGAL_ENTITY_ID IN NUMBER,
468 X_OPEN_TO_TERM_FLAG IN VARCHAR2,
469 X_MULTIPLE_FUNDING_FLAG IN VARCHAR2,
470 X_LOAN_TYPE_ID IN NUMBER,
471 X_SECONDARY_STATUS IN VARCHAR2,
472 X_OPEN_TO_TERM_EVENT IN VARCHAR2,
473 X_BALLOON_PAYMENT_TYPE IN VARCHAR2,
474 X_BALLOON_PAYMENT_AMOUNT IN NUMBER,
475 X_CURRENT_PHASE IN VARCHAR2,
476 X_OPEN_LOAN_START_DATE IN DATE,
477 X_OPEN_LOAN_TERM IN NUMBER,
478 X_OPEN_LOAN_TERM_PERIOD IN VARCHAR2,
479 X_OPEN_MATURITY_DATE IN DATE,
480 X_FUNDS_RESERVED_FLAG IN VARCHAR2,
481 X_FUNDS_CHECK_DATE IN DATE,
482 X_SUBSIDY_RATE IN NUMBER,
483 X_APPLICATION_ID IN NUMBER,
484 X_CREATED_BY_MODULE IN VARCHAR2,
485 X_PARTY_TYPE IN VARCHAR2,
486 X_FORGIVENESS_FLAG IN VARCHAR2,
487 X_FORGIVENESS_PERCENT IN NUMBER,
488 X_DISABLE_BILLING_FLAG IN VARCHAR2,
489 X_ADD_REQUESTED_AMOUNT IN NUMBER,
490 X_DISABLE_INT_ACCRUAL_FLAG IN VARCHAR2,
491 X_BILL_ON_APPR_AMOUNT_FLAG IN VARCHAR2
492 ) IS
493
494 --To handle the reset of data due to change in loan currency in the UI
495 L_RESET_EXCHANGE_DATA VARCHAR2(1);
496 BEGIN
497
498 --To handle the reset of data due to change in loan currency in the UI
499 IF (X_EXCHANGE_RATE_TYPE = FND_API.G_MISS_CHAR) THEN
500 L_RESET_EXCHANGE_DATA := 'Y';
501 ELSE
502 L_RESET_EXCHANGE_DATA := 'N';
503 END IF;
504
505 --Call to record history
506 LNS_LOAN_HISTORY_PUB.log_record_pre(
507 p_id => X_LOAN_ID,
508 p_primary_key_name => 'LOAN_ID',
509 p_table_name => 'LNS_LOAN_HEADERS_ALL'
510 );
511
512 UPDATE LNS_LOAN_HEADERS_ALL SET
513 LOAN_ID = DECODE( X_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, X_LOAN_ID )
514 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
515 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
516 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
517 ,PROGRAM_ID = LNS_UTILITY_PUB.PROGRAM_ID
518 ,OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
519 ,REQUEST_ID = LNS_UTILITY_PUB.REQUEST_ID
520 ,ORG_ID = DECODE( X_ORG_ID, NULL, ORG_ID, FND_API.G_MISS_NUM, NULL, X_ORG_ID )
521 ,LOAN_NUMBER = DECODE( X_LOAN_NUMBER, NULL, LOAN_NUMBER, FND_API.G_MISS_CHAR, NULL, X_LOAN_NUMBER )
522 ,LOAN_APPLICATION_DATE = DECODE( X_LOAN_APPLICATION_DATE, NULL, LOAN_APPLICATION_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_APPLICATION_DATE )
523 ,END_DATE = DECODE( X_END_DATE, NULL, END_DATE, FND_API.G_MISS_DATE, NULL, X_END_DATE )
524 ,INITIAL_LOAN_BALANCE = DECODE( X_INITIAL_LOAN_BALANCE, NULL, INITIAL_LOAN_BALANCE, FND_API.G_MISS_NUM, NULL, X_INITIAL_LOAN_BALANCE )
525 ,LAST_PAYMENT_DATE = DECODE( X_LAST_PAYMENT_DATE, NULL, LAST_PAYMENT_DATE, FND_API.G_MISS_DATE, NULL, X_LAST_PAYMENT_DATE )
526 ,LAST_PAYMENT_AMOUNT = DECODE( X_LAST_PAYMENT_AMOUNT, NULL, LAST_PAYMENT_AMOUNT, FND_API.G_MISS_NUM, NULL, X_LAST_PAYMENT_AMOUNT )
527 ,LOAN_TERM = DECODE( X_LOAN_TERM, NULL, LOAN_TERM, FND_API.G_MISS_NUM, NULL, X_LOAN_TERM )
528 ,LOAN_TERM_PERIOD = DECODE( X_LOAN_TERM_PERIOD, NULL, LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_LOAN_TERM_PERIOD )
529 ,AMORTIZED_TERM = DECODE( X_AMORTIZED_TERM, NULL, AMORTIZED_TERM, FND_API.G_MISS_NUM, NULL, X_AMORTIZED_TERM )
530 ,AMORTIZED_TERM_PERIOD = DECODE( X_AMORTIZED_TERM_PERIOD, NULL, AMORTIZED_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_AMORTIZED_TERM_PERIOD )
531 ,LOAN_STATUS = DECODE( X_LOAN_STATUS, NULL, LOAN_STATUS, FND_API.G_MISS_CHAR, NULL, X_LOAN_STATUS )
532 ,LOAN_ASSIGNED_TO = DECODE( X_LOAN_ASSIGNED_TO, NULL, LOAN_ASSIGNED_TO, FND_API.G_MISS_NUM, NULL, X_LOAN_ASSIGNED_TO )
533 ,LOAN_CURRENCY = DECODE( X_LOAN_CURRENCY, NULL, LOAN_CURRENCY, FND_API.G_MISS_CHAR, NULL, X_LOAN_CURRENCY )
534 ,LOAN_CLASS_CODE = DECODE( X_LOAN_CLASS_CODE, NULL, LOAN_CLASS_CODE, FND_API.G_MISS_CHAR, NULL, X_LOAN_CLASS_CODE )
535 ,LOAN_TYPE = DECODE( X_LOAN_TYPE, NULL, LOAN_TYPE, FND_API.G_MISS_CHAR, NULL, X_LOAN_TYPE )
536 ,LOAN_SUBTYPE = DECODE( X_LOAN_SUBTYPE, NULL, LOAN_SUBTYPE, FND_API.G_MISS_CHAR, NULL, X_LOAN_SUBTYPE )
537 ,LOAN_PURPOSE_CODE = DECODE( X_LOAN_PURPOSE_CODE, NULL, LOAN_PURPOSE_CODE, FND_API.G_MISS_CHAR, NULL, X_LOAN_PURPOSE_CODE )
538 ,CUST_ACCOUNT_ID = DECODE( X_CUST_ACCOUNT_ID, NULL, CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ID )
539 ,BILL_TO_ACCT_SITE_ID = DECODE( X_BILL_TO_ACCT_SITE_ID, NULL, BILL_TO_ACCT_SITE_ID, FND_API.G_MISS_NUM, NULL, X_BILL_TO_ACCT_SITE_ID )
540 ,LOAN_MATURITY_DATE = DECODE( X_LOAN_MATURITY_DATE, NULL, LOAN_MATURITY_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_MATURITY_DATE )
541 ,LOAN_START_DATE = DECODE( X_LOAN_START_DATE, NULL, LOAN_START_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_START_DATE )
542 ,LOAN_CLOSING_DATE = DECODE( X_LOAN_CLOSING_DATE, NULL, LOAN_CLOSING_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_CLOSING_DATE )
543 ,REFERENCE_ID = DECODE( X_REFERENCE_ID, NULL, REFERENCE_ID, FND_API.G_MISS_NUM, NULL, X_REFERENCE_ID )
544 ,REFERENCE_NUMBER = DECODE( X_REFERENCE_NUMBER, NULL, REFERENCE_NUMBER, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_NUMBER )
545 ,REFERENCE_AMOUNT = DECODE( X_REFERENCE_AMOUNT, NULL, REFERENCE_AMOUNT, FND_API.G_MISS_NUM, NULL, X_REFERENCE_AMOUNT )
546 ,PRODUCT_FLAG = DECODE( X_PRODUCT_FLAG, NULL, PRODUCT_FLAG, FND_API.G_MISS_CHAR, NULL, X_PRODUCT_FLAG )
547 ,PRIMARY_BORROWER_ID = DECODE( X_PRIMARY_BORROWER_ID, NULL, PRIMARY_BORROWER_ID, FND_API.G_MISS_NUM, NULL, X_PRIMARY_BORROWER_ID )
548 ,PRODUCT_ID = DECODE( X_PRODUCT_ID, NULL, PRODUCT_ID, FND_API.G_MISS_NUM, NULL, X_PRODUCT_ID )
549 ,REQUESTED_AMOUNT = DECODE( X_REQUESTED_AMOUNT, NULL, REQUESTED_AMOUNT, FND_API.G_MISS_NUM, NULL, X_REQUESTED_AMOUNT )
550 ,FUNDED_AMOUNT = DECODE( X_FUNDED_AMOUNT, NULL, FUNDED_AMOUNT, FND_API.G_MISS_NUM, NULL, X_FUNDED_AMOUNT )
551 ,LOAN_APPROVAL_DATE = DECODE( X_LOAN_APPROVAL_DATE, NULL, LOAN_APPROVAL_DATE, FND_API.G_MISS_DATE, NULL, X_LOAN_APPROVAL_DATE )
552 ,LOAN_APPROVED_BY = DECODE( X_LOAN_APPROVED_BY, NULL, LOAN_APPROVED_BY, FND_API.G_MISS_NUM, NULL, X_LOAN_APPROVED_BY )
553 ,ATTRIBUTE_CATEGORY = DECODE( X_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY )
554 ,ATTRIBUTE1 = DECODE( X_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 )
555 ,ATTRIBUTE2 = DECODE( X_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 )
556 ,ATTRIBUTE3 = DECODE( X_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 )
557 ,ATTRIBUTE4 = DECODE( X_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 )
558 ,ATTRIBUTE5 = DECODE( X_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 )
559 ,ATTRIBUTE6 = DECODE( X_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 )
560 ,ATTRIBUTE7 = DECODE( X_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 )
561 ,ATTRIBUTE8 = DECODE( X_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 )
562 ,ATTRIBUTE9 = DECODE( X_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 )
563 ,ATTRIBUTE10 = DECODE( X_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 )
564 ,ATTRIBUTE11 = DECODE( X_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 )
565 ,ATTRIBUTE12 = DECODE( X_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 )
566 ,ATTRIBUTE13 = DECODE( X_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 )
567 ,ATTRIBUTE14 = DECODE( X_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 )
568 ,ATTRIBUTE15 = DECODE( X_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 )
569 ,ATTRIBUTE16 = DECODE( X_ATTRIBUTE16, NULL, ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 )
570 ,ATTRIBUTE17 = DECODE( X_ATTRIBUTE17, NULL, ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 )
571 ,ATTRIBUTE18 = DECODE( X_ATTRIBUTE18, NULL, ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 )
572 ,ATTRIBUTE19 = DECODE( X_ATTRIBUTE19, NULL, ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 )
573 ,ATTRIBUTE20 = DECODE( X_ATTRIBUTE20, NULL, ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 )
574 ,LAST_BILLED_DATE = DECODE( X_LAST_BILLED_DATE, NULL, LAST_BILLED_DATE, FND_API.G_MISS_DATE, NULL, X_LAST_BILLED_DATE )
575 ,CUSTOM_PAYMENTS_FLAG = DECODE( X_CUSTOM_PAYMENTS_FLAG, NULL, CUSTOM_PAYMENTS_FLAG, FND_API.G_MISS_CHAR, NULL, X_CUSTOM_PAYMENTS_FLAG )
576 ,BILLED_FLAG = DECODE( X_BILLED_FLAG, NULL, BILLED_FLAG, FND_API.G_MISS_CHAR, NULL, X_BILLED_FLAG )
577 ,REFERENCE_NAME = DECODE( X_REFERENCE_NAME, NULL, REFERENCE_NAME, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_NAME )
578 ,REFERENCE_TYPE = DECODE( X_REFERENCE_TYPE, NULL, REFERENCE_TYPE, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_TYPE )
579 ,REFERENCE_TYPE_ID = DECODE( X_REFERENCE_TYPE_ID, NULL, REFERENCE_TYPE_ID, FND_API.G_MISS_NUM, NULL, X_REFERENCE_TYPE_ID )
580 ,USSGL_TRANSACTION_CODE = DECODE( X_USSGL_TRANSACTION_CODE, NULL, USSGL_TRANSACTION_CODE, FND_API.G_MISS_CHAR, NULL, X_USSGL_TRANSACTION_CODE )
581 ,GL_DATE = DECODE( X_GL_DATE, NULL, GL_DATE, FND_API.G_MISS_DATE, NULL, X_GL_DATE )
582 ,REC_ADJUSTMENT_NUMBER = DECODE( X_REC_ADJUSTMENT_NUMBER, NULL, REC_ADJUSTMENT_NUMBER, FND_API.G_MISS_CHAR, NULL, X_REC_ADJUSTMENT_NUMBER )
583 ,CONTACT_REL_PARTY_ID = DECODE( X_CONTACT_REL_PARTY_ID, NULL, CONTACT_REL_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_CONTACT_REL_PARTY_ID )
584 ,CONTACT_PERS_PARTY_ID = DECODE( X_CONTACT_PERS_PARTY_ID, NULL, CONTACT_PERS_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_CONTACT_PERS_PARTY_ID )
585 ,CREDIT_REVIEW_FLAG = DECODE( X_CREDIT_REVIEW_FLAG, NULL, CREDIT_REVIEW_FLAG, FND_API.G_MISS_CHAR, NULL, X_CREDIT_REVIEW_FLAG )
586 ,EXCHANGE_RATE_TYPE = DECODE( X_EXCHANGE_RATE_TYPE, NULL, EXCHANGE_RATE_TYPE, FND_API.G_MISS_CHAR, NULL, X_EXCHANGE_RATE_TYPE )
587 ,EXCHANGE_DATE = DECODE( L_RESET_EXCHANGE_DATA, 'Y', NULL, ( DECODE( X_EXCHANGE_DATE, NULL, EXCHANGE_DATE, FND_API.G_MISS_DATE, NULL, X_EXCHANGE_DATE ) ) )
588 ,EXCHANGE_RATE = DECODE( L_RESET_EXCHANGE_DATA, 'Y', NULL, ( DECODE( X_EXCHANGE_RATE, NULL, EXCHANGE_RATE, FND_API.G_MISS_NUM, NULL, X_EXCHANGE_RATE ) ) )
589 ,COLLATERAL_PERCENT = DECODE( X_COLLATERAL_PERCENT, NULL, COLLATERAL_PERCENT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_COLLATERAL_PERCENT )
590 ,LAST_PAYMENT_NUMBER = DECODE( X_LAST_PAYMENT_NUMBER, NULL, LAST_PAYMENT_NUMBER, FND_API.G_MISS_NUM, NULL, X_LAST_PAYMENT_NUMBER )
591 ,LAST_AMORTIZATION_ID = DECODE( X_LAST_AMORTIZATION_ID, NULL, LAST_AMORTIZATION_ID, FND_API.G_MISS_NUM, NULL, X_LAST_AMORTIZATION_ID )
592 ,LEGAL_ENTITY_ID = DECODE( X_LEGAL_ENTITY_ID, NULL, LEGAL_ENTITY_ID, FND_API.G_MISS_NUM, NULL, X_LEGAL_ENTITY_ID )
593 ,OPEN_TO_TERM_FLAG = DECODE( X_OPEN_TO_TERM_FLAG, NULL, OPEN_TO_TERM_FLAG, FND_API.G_MISS_CHAR, NULL, X_OPEN_TO_TERM_FLAG )
594 ,MULTIPLE_FUNDING_FLAG = DECODE( X_MULTIPLE_FUNDING_FLAG, NULL, MULTIPLE_FUNDING_FLAG, FND_API.G_MISS_CHAR, NULL, X_MULTIPLE_FUNDING_FLAG )
595 ,LOAN_TYPE_ID = DECODE( X_LOAN_TYPE_ID, NULL, LOAN_TYPE_ID, FND_API.G_MISS_NUM, NULL, X_LOAN_TYPE_ID )
596 ,SECONDARY_STATUS = DECODE( X_SECONDARY_STATUS, NULL, SECONDARY_STATUS, FND_API.G_MISS_CHAR, NULL, X_SECONDARY_STATUS )
597 ,OPEN_TO_TERM_EVENT = DECODE( X_OPEN_TO_TERM_EVENT, NULL, OPEN_TO_TERM_EVENT, FND_API.G_MISS_CHAR, NULL, X_OPEN_TO_TERM_EVENT )
598 ,BALLOON_PAYMENT_TYPE = DECODE( X_BALLOON_PAYMENT_TYPE, NULL, BALLOON_PAYMENT_TYPE, FND_API.G_MISS_CHAR, NULL, X_BALLOON_PAYMENT_TYPE )
599 ,BALLOON_PAYMENT_AMOUNT = DECODE( X_BALLOON_PAYMENT_AMOUNT, NULL, BALLOON_PAYMENT_AMOUNT, FND_API.G_MISS_NUM, NULL, X_BALLOON_PAYMENT_AMOUNT )
600 ,CURRENT_PHASE = DECODE( X_CURRENT_PHASE, NULL, CURRENT_PHASE, FND_API.G_MISS_CHAR, NULL, X_CURRENT_PHASE )
601 ,OPEN_LOAN_START_DATE = DECODE( X_OPEN_LOAN_START_DATE, NULL, OPEN_LOAN_START_DATE, FND_API.G_MISS_DATE, NULL, X_OPEN_LOAN_START_DATE )
602 ,OPEN_LOAN_TERM = DECODE( X_OPEN_LOAN_TERM, NULL, OPEN_LOAN_TERM, FND_API.G_MISS_NUM, NULL, X_OPEN_LOAN_TERM )
603 ,OPEN_LOAN_TERM_PERIOD = DECODE( X_OPEN_LOAN_TERM_PERIOD, NULL, OPEN_LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR, NULL, X_OPEN_LOAN_TERM_PERIOD )
604 ,OPEN_MATURITY_DATE = DECODE( X_OPEN_MATURITY_DATE, NULL, OPEN_MATURITY_DATE, FND_API.G_MISS_DATE, NULL, X_OPEN_MATURITY_DATE )
605 ,FUNDS_RESERVED_FLAG = DECODE( X_FUNDS_RESERVED_FLAG, NULL, FUNDS_RESERVED_FLAG, FND_API.G_MISS_CHAR, NULL, X_FUNDS_RESERVED_FLAG )
606 ,FUNDS_CHECK_DATE = DECODE( X_FUNDS_CHECK_DATE, NULL, FUNDS_CHECK_DATE, FND_API.G_MISS_DATE, NULL, X_FUNDS_CHECK_DATE )
607 ,SUBSIDY_RATE = DECODE( X_SUBSIDY_RATE, NULL, SUBSIDY_RATE, FND_API.G_MISS_NUM, NULL, X_SUBSIDY_RATE )
608 ,APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
609 ,CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE )
610 ,PARTY_TYPE = DECODE( X_PARTY_TYPE, NULL, PARTY_TYPE, FND_API.G_MISS_CHAR, NULL, X_PARTY_TYPE )
611 ,FORGIVENESS_FLAG = DECODE( X_FORGIVENESS_FLAG, NULL, FORGIVENESS_FLAG, FND_API.G_MISS_CHAR, NULL, X_FORGIVENESS_FLAG )
612 ,FORGIVENESS_PERCENT = DECODE( X_FORGIVENESS_PERCENT, NULL, FORGIVENESS_PERCENT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_FORGIVENESS_PERCENT )
613 ,DISABLE_BILLING_FLAG = DECODE( X_DISABLE_BILLING_FLAG, NULL, DISABLE_BILLING_FLAG, FND_API.G_MISS_CHAR, NULL, X_DISABLE_BILLING_FLAG )
614 ,ADD_REQUESTED_AMOUNT = DECODE( X_ADD_REQUESTED_AMOUNT, NULL, ADD_REQUESTED_AMOUNT, FND_API.G_MISS_NUM, NULL, 0, NULL, X_ADD_REQUESTED_AMOUNT )
615 ,DISABLE_INT_ACCRUAL_FLAG = DECODE( X_DISABLE_INT_ACCRUAL_FLAG, NULL, DISABLE_INT_ACCRUAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_DISABLE_INT_ACCRUAL_FLAG )
616 ,BILL_ON_APPR_AMOUNT_FLAG = DECODE( X_BILL_ON_APPR_AMOUNT_FLAG, NULL, BILL_ON_APPR_AMOUNT_FLAG, FND_API.G_MISS_CHAR, NULL, X_BILL_ON_APPR_AMOUNT_FLAG )
617
618
619 WHERE ROWID = X_Rowid;
620
621 IF (SQL%NOTFOUND) THEN
622 RAISE NO_DATA_FOUND;
623 END IF;
624
625 UPDATE LNS_LOAN_HEADERS_ALL_TL SET
626 LOAN_DESCRIPTION = DECODE( X_LOAN_DESCRIPTION, NULL, LOAN_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_LOAN_DESCRIPTION )
627 ,REFERENCE_DESCRIPTION = DECODE( X_REFERENCE_DESCRIPTION, NULL, REFERENCE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, X_REFERENCE_DESCRIPTION )
628 ,LAST_UPDATE_DATE = sysdate
629 ,LAST_UPDATED_BY = fnd_global.user_id
630 ,LAST_UPDATE_LOGIN = fnd_global.user_id
631 ,SOURCE_LANG = userenv('LANG')
632 WHERE LOAN_ID = X_LOAN_ID
633 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
634
635 if (sql%notfound) then
636 raise no_data_found;
637 end if;
638
639
640 --Call to record history
641 LNS_LOAN_HISTORY_PUB.log_record_post(
642 p_id => X_LOAN_ID,
643 p_primary_key_name => 'LOAN_ID',
644 p_table_name => 'LNS_LOAN_HEADERS_ALL',
645 p_loan_id => X_LOAN_ID
646 );
647 END Update_Row;
648
649 PROCEDURE Lock_Row(
650 x_rowid VARCHAR2
651 ,p_LOAN_ID NUMBER
652 ,p_LAST_UPDATE_DATE DATE
653 ,p_LAST_UPDATED_BY NUMBER
654 ,p_LAST_UPDATE_LOGIN NUMBER
655 ,p_CREATION_DATE DATE
656 ,p_CREATED_BY NUMBER
657 ,p_PROGRAM_ID NUMBER
658 ,p_OBJECT_VERSION_NUMBER NUMBER
659 ,p_REQUEST_ID NUMBER
660 ,p_ORG_ID NUMBER
661 ,p_LOAN_NUMBER VARCHAR2
662 ,p_LOAN_DESCRIPTION VARCHAR2
663 ,p_LOAN_APPLICATION_DATE DATE
664 ,p_END_DATE DATE
665 ,p_INITIAL_LOAN_BALANCE NUMBER
666 ,p_LAST_PAYMENT_DATE DATE
667 ,p_LAST_PAYMENT_AMOUNT NUMBER
668 ,p_LOAN_TERM NUMBER
669 ,p_LOAN_TERM_PERIOD VARCHAR2
670 ,p_AMORTIZED_TERM NUMBER
671 ,p_AMORTIZED_TERM_PERIOD VARCHAR2
672 ,p_LOAN_STATUS VARCHAR2
673 ,p_LOAN_ASSIGNED_TO NUMBER
674 ,p_LOAN_CURRENCY VARCHAR2
675 ,p_LOAN_CLASS_CODE VARCHAR2
676 ,p_LOAN_TYPE VARCHAR2
677 ,p_LOAN_SUBTYPE VARCHAR2
678 ,p_LOAN_PURPOSE_CODE VARCHAR2
679 ,p_CUST_ACCOUNT_ID NUMBER
680 ,p_BILL_TO_ACCT_SITE_ID NUMBER
681 ,p_LOAN_MATURITY_DATE DATE
682 ,p_LOAN_START_DATE DATE
683 ,p_LOAN_CLOSING_DATE DATE
684 ,p_REFERENCE_ID NUMBER
685 ,p_REFERENCE_NUMBER VARCHAR2
686 ,p_REFERENCE_DESCRIPTION VARCHAR2
687 ,p_REFERENCE_AMOUNT NUMBER
688 ,p_PRODUCT_FLAG VARCHAR2
689 ,p_PRIMARY_BORROWER_ID NUMBER
690 ,p_PRODUCT_ID NUMBER
691 ,p_REQUESTED_AMOUNT NUMBER
692 ,p_FUNDED_AMOUNT NUMBER
693 ,p_LOAN_APPROVAL_DATE DATE
694 ,p_LOAN_APPROVED_BY NUMBER
695 ,p_ATTRIBUTE_CATEGORY VARCHAR2
696 ,p_ATTRIBUTE1 VARCHAR2
697 ,p_ATTRIBUTE2 VARCHAR2
698 ,p_ATTRIBUTE3 VARCHAR2
699 ,p_ATTRIBUTE4 VARCHAR2
700 ,p_ATTRIBUTE5 VARCHAR2
701 ,p_ATTRIBUTE6 VARCHAR2
702 ,p_ATTRIBUTE7 VARCHAR2
703 ,p_ATTRIBUTE8 VARCHAR2
704 ,p_ATTRIBUTE9 VARCHAR2
705 ,p_ATTRIBUTE10 VARCHAR2
706 ,p_ATTRIBUTE11 VARCHAR2
707 ,p_ATTRIBUTE12 VARCHAR2
708 ,p_ATTRIBUTE13 VARCHAR2
709 ,p_ATTRIBUTE14 VARCHAR2
710 ,p_ATTRIBUTE15 VARCHAR2
711 ,p_ATTRIBUTE16 VARCHAR2
712 ,p_ATTRIBUTE17 VARCHAR2
713 ,p_ATTRIBUTE18 VARCHAR2
714 ,p_ATTRIBUTE19 VARCHAR2
715 ,p_ATTRIBUTE20 VARCHAR2
716 ,p_LAST_BILLED_DATE DATE
717 ,p_CUSTOM_PAYMENTS_FLAG VARCHAR2
718 ,p_BILLED_FLAG VARCHAR2
719 ,p_REFERENCE_NAME VARCHAR2
720 ,p_REFERENCE_TYPE VARCHAR2
721 ,p_REFERENCE_TYPE_ID NUMBER
722 ,p_USSGL_TRANSACTION_CODE VARCHAR2
723 ,p_GL_DATE DATE
724 ,p_REC_ADJUSTMENT_NUMBER VARCHAR2
725 ,p_CONTACT_REL_PARTY_ID NUMBER
726 ,p_CONTACT_PERS_PARTY_ID NUMBER
727 ,p_CREDIT_REVIEW_FLAG VARCHAR2
728 ,p_EXCHANGE_RATE_TYPE VARCHAR2
729 ,p_EXCHANGE_DATE DATE
730 ,p_EXCHANGE_RATE NUMBER
731 ,p_COLLATERAL_PERCENT NUMBER
732 ,p_LAST_PAYMENT_NUMBER NUMBER
733 ,p_LAST_AMORTIZATION_ID NUMBER
734 ,p_LEGAL_ENTITY_ID NUMBER
735 ,p_OPEN_TO_TERM_FLAG VARCHAR2
736 ,p_MULTIPLE_FUNDING_FLAG VARCHAR2
737 ,p_LOAN_TYPE_ID NUMBER
738 ,p_SECONDARY_STATUS VARCHAR2
739 ,p_OPEN_TO_TERM_EVENT VARCHAR2
740 ,p_BALLOON_PAYMENT_TYPE VARCHAR2
741 ,p_BALLOON_PAYMENT_AMOUNT NUMBER
742 ,p_CURRENT_PHASE VARCHAR2
743 ,p_OPEN_LOAN_START_DATE DATE
744 ,p_OPEN_LOAN_TERM NUMBER
745 ,p_OPEN_LOAN_TERM_PERIOD VARCHAR2
746 ,p_OPEN_MATURITY_DATE DATE
747 ,p_FUNDS_RESERVED_FLAG VARCHAR2
748 ,p_FUNDS_CHECK_DATE DATE
749 ,p_SUBSIDY_RATE NUMBER
750 ,p_APPLICATION_ID NUMBER
751 ,p_CREATED_BY_MODULE VARCHAR2
752 ,p_PARTY_TYPE VARCHAR2
753 ,p_FORGIVENESS_FLAG VARCHAR2
754 ,p_FORGIVENESS_PERCENT NUMBER
755 ,p_DISABLE_BILLING_FLAG VARCHAR2
756 ,p_ADD_REQUESTED_AMOUNT NUMBER
757 ,p_DISABLE_INT_ACCRUAL_FLAG VARCHAR2
758 ,p_BILL_ON_APPR_AMOUNT_FLAG VARCHAR2
759 ) IS
760 CURSOR C IS SELECT * FROM LNS_LOAN_HEADERS_ALL
761 WHERE rowid = x_rowid
762 FOR UPDATE of LOAN_ID NOWAIT;
763 Recinfo C%ROWTYPE;
764
765 CURSOR C_TL IS SELECT
766 LOAN_DESCRIPTION
767 ,REFERENCE_DESCRIPTION
768 ,decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
769 FROM LNS_LOAN_HEADERS_ALL_TL
770 WHERE rowid = x_rowid
771 FOR UPDATE of LOAN_ID NOWAIT;
772
773 BEGIN
774 OPEN C;
775 FETCH C INTO Recinfo;
776 IF (C%NOTFOUND) THEN
777 CLOSE C;
778 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
779 APP_EXCEPTION.Raise_Exception;
780 END IF;
781 CLOSE C;
782
783 IF (
784 (Recinfo.LOAN_ID = p_LOAN_ID)
785 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
786 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
787 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
788 ) THEN
789 return;
790 ELSE
791 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
792 APP_EXCEPTION.Raise_Exception;
793 END IF;
794
795 FOR tlinfo in C_TL LOOP
796 IF (tlinfo.BASELANG = 'Y') THEN
797 if (
798 ((tlinfo.LOAN_DESCRIPTION = P_LOAN_DESCRIPTION)
799 OR ((tlinfo.LOAN_DESCRIPTION is null) AND (P_LOAN_DESCRIPTION is null)))
800 AND ( (tlinfo.REFERENCE_DESCRIPTION = P_REFERENCE_DESCRIPTION)
801 OR ( (tlinfo.REFERENCE_DESCRIPTION IS NULL)
802 AND (P_REFERENCE_DESCRIPTION IS NULL)))
803
804 ) THEN
805 null;
806 ELSE
807 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
808 APP_EXCEPTION.Raise_Exception;
809 END IF;
810 END IF;
811 END LOOP;
812
813
814 END Lock_Row;
815
816 procedure ADD_LANGUAGE
817 is
818 begin
819 delete from LNS_LOAN_HEADERS_ALL_TL T
820 where not exists
821 (select NULL
822 from LNS_LOAN_HEADERS_ALL B
823 where B.LOAN_ID = T.LOAN_ID
824 );
825
826 update LNS_LOAN_HEADERS_ALL_TL T set (
827 LOAN_DESCRIPTION,
828 REFERENCE_DESCRIPTION
829 ) = (select
830 B.LOAN_DESCRIPTION,
831 B.REFERENCE_DESCRIPTION
832 from LNS_LOAN_HEADERS_ALL_TL B
833 where B.LOAN_ID = T.LOAN_ID
834 and B.LANGUAGE = T.SOURCE_LANG)
835 where (
836 T.LOAN_ID,
837 T.LANGUAGE
838 ) in (select
839 SUBT.LOAN_ID,
840 SUBT.LANGUAGE
841 from LNS_LOAN_HEADERS_ALL_TL SUBB, LNS_LOAN_HEADERS_ALL_TL SUBT
842 where SUBB.LOAN_ID = SUBT.LOAN_ID
843 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
844 and (SUBB.LOAN_DESCRIPTION <> SUBT.LOAN_DESCRIPTION
845 or (SUBB.LOAN_DESCRIPTION is null and SUBT.LOAN_DESCRIPTION is not null)
846 or (SUBB.LOAN_DESCRIPTION is not null and SUBT.LOAN_DESCRIPTION is null)
847 or SUBB.REFERENCE_DESCRIPTION <> SUBT.REFERENCE_DESCRIPTION
848 or (SUBB.REFERENCE_DESCRIPTION is null and SUBT.REFERENCE_DESCRIPTION is not null)
849 or (SUBB.REFERENCE_DESCRIPTION is not null and SUBT.REFERENCE_DESCRIPTION is null)
850 ));
851
852 insert into LNS_LOAN_HEADERS_ALL_TL (
853 LOAN_ID,
854 LOAN_DESCRIPTION,
855 REFERENCE_DESCRIPTION,
856 LAST_UPDATE_DATE,
857 LAST_UPDATED_BY,
858 CREATION_DATE,
859 CREATED_BY,
860 LAST_UPDATE_LOGIN,
861 LANGUAGE,
862 SOURCE_LANG
863 ) select
864 B.LOAN_ID,
865 B.LOAN_DESCRIPTION,
866 B.REFERENCE_DESCRIPTION,
867 B.LAST_UPDATE_DATE,
868 B.LAST_UPDATED_BY,
869 B.CREATION_DATE,
870 B.CREATED_BY,
871 B.LAST_UPDATE_LOGIN,
872 L.LANGUAGE_CODE,
873 B.SOURCE_LANG
874 from LNS_LOAN_HEADERS_ALL_TL B, FND_LANGUAGES L
875 where L.INSTALLED_FLAG in ('I', 'B')
876 and B.LANGUAGE = userenv('LANG')
877 and not exists
878 (select NULL
879 from LNS_LOAN_HEADERS_ALL_TL T
880 where T.LOAN_ID = B.LOAN_ID
881 and T.LANGUAGE = L.LANGUAGE_CODE);
882 end ADD_LANGUAGE;
883
884 PROCEDURE Select_Row (
885 X_LOAN_ID IN OUT NOCOPY NUMBER,
886 X_ORG_ID OUT NOCOPY NUMBER,
887 X_LOAN_NUMBER OUT NOCOPY VARCHAR2,
888 X_LOAN_DESCRIPTION OUT NOCOPY VARCHAR2,
889 X_LOAN_APPLICATION_DATE OUT NOCOPY DATE,
890 X_END_DATE OUT NOCOPY DATE,
891 X_INITIAL_LOAN_BALANCE OUT NOCOPY NUMBER,
892 X_LAST_PAYMENT_DATE OUT NOCOPY DATE,
893 X_LAST_PAYMENT_AMOUNT OUT NOCOPY NUMBER,
894 X_LOAN_TERM OUT NOCOPY NUMBER,
895 X_LOAN_TERM_PERIOD OUT NOCOPY VARCHAR2,
896 X_AMORTIZED_TERM OUT NOCOPY NUMBER,
897 X_AMORTIZED_TERM_PERIOD OUT NOCOPY VARCHAR2,
898 X_LOAN_STATUS OUT NOCOPY VARCHAR2,
899 X_LOAN_ASSIGNED_TO OUT NOCOPY NUMBER,
900 X_LOAN_CURRENCY OUT NOCOPY VARCHAR2,
901 X_LOAN_CLASS_CODE OUT NOCOPY VARCHAR2,
902 X_LOAN_TYPE OUT NOCOPY VARCHAR2,
903 X_LOAN_SUBTYPE OUT NOCOPY VARCHAR2,
904 X_LOAN_PURPOSE_CODE OUT NOCOPY VARCHAR2,
905 X_CUST_ACCOUNT_ID OUT NOCOPY NUMBER,
906 X_BILL_TO_ACCT_SITE_ID OUT NOCOPY NUMBER,
907 X_LOAN_MATURITY_DATE OUT NOCOPY DATE,
908 X_LOAN_START_DATE OUT NOCOPY DATE,
909 X_LOAN_CLOSING_DATE OUT NOCOPY DATE,
910 X_REFERENCE_ID OUT NOCOPY NUMBER,
911 X_REFERENCE_NUMBER OUT NOCOPY VARCHAR2,
912 X_REFERENCE_DESCRIPTION OUT NOCOPY VARCHAR2,
913 X_REFERENCE_AMOUNT OUT NOCOPY NUMBER,
914 X_PRODUCT_FLAG OUT NOCOPY VARCHAR2,
915 X_PRIMARY_BORROWER_ID OUT NOCOPY NUMBER,
916 X_PRODUCT_ID OUT NOCOPY NUMBER,
917 X_REQUESTED_AMOUNT OUT NOCOPY NUMBER,
918 X_FUNDED_AMOUNT OUT NOCOPY NUMBER,
919 X_LOAN_APPROVAL_DATE OUT NOCOPY DATE,
920 X_LOAN_APPROVED_BY OUT NOCOPY NUMBER,
921 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
922 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
923 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
924 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
925 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
926 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
927 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
928 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
929 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
930 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
931 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
932 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
933 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
934 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
935 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
936 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
937 X_ATTRIBUTE16 OUT NOCOPY VARCHAR2,
938 X_ATTRIBUTE17 OUT NOCOPY VARCHAR2,
939 X_ATTRIBUTE18 OUT NOCOPY VARCHAR2,
940 X_ATTRIBUTE19 OUT NOCOPY VARCHAR2,
941 X_ATTRIBUTE20 OUT NOCOPY VARCHAR2,
942 X_LAST_BILLED_DATE OUT NOCOPY DATE,
943 X_CUSTOM_PAYMENTS_FLAG OUT NOCOPY VARCHAR2,
944 X_BILLED_FLAG OUT NOCOPY VARCHAR2,
945 X_REFERENCE_NAME OUT NOCOPY VARCHAR2,
946 X_REFERENCE_TYPE OUT NOCOPY VARCHAR2,
947 X_REFERENCE_TYPE_ID OUT NOCOPY NUMBER,
948 X_USSGL_TRANSACTION_CODE OUT NOCOPY VARCHAR2,
949 X_GL_DATE OUT NOCOPY DATE,
950 X_REC_ADJUSTMENT_NUMBER OUT NOCOPY VARCHAR2,
951 X_CONTACT_REL_PARTY_ID OUT NOCOPY NUMBER,
952 X_CONTACT_PERS_PARTY_ID OUT NOCOPY NUMBER,
953 X_CREDIT_REVIEW_FLAG OUT NOCOPY VARCHAR2,
954 X_EXCHANGE_RATE_TYPE OUT NOCOPY VARCHAR2,
955 X_EXCHANGE_DATE OUT NOCOPY DATE,
956 X_EXCHANGE_RATE OUT NOCOPY NUMBER,
957 X_COLLATERAL_PERCENT OUT NOCOPY NUMBER,
958 X_LAST_PAYMENT_NUMBER OUT NOCOPY NUMBER,
959 X_LAST_AMORTIZATION_ID OUT NOCOPY NUMBER,
960 X_LEGAL_ENTITY_ID OUT NOCOPY NUMBER,
961 X_OPEN_TO_TERM_FLAG OUT NOCOPY VARCHAR2,
962 X_MULTIPLE_FUNDING_FLAG OUT NOCOPY VARCHAR2,
963 X_LOAN_TYPE_ID OUT NOCOPY NUMBER,
964 X_SECONDARY_STATUS OUT NOCOPY VARCHAR2,
965 X_OPEN_TO_TERM_EVENT OUT NOCOPY VARCHAR2,
966 X_BALLOON_PAYMENT_TYPE OUT NOCOPY VARCHAR2,
967 X_BALLOON_PAYMENT_AMOUNT OUT NOCOPY NUMBER,
968 X_CURRENT_PHASE OUT NOCOPY VARCHAR2,
969 X_OPEN_LOAN_START_DATE OUT NOCOPY DATE,
970 X_OPEN_LOAN_TERM OUT NOCOPY NUMBER,
971 X_OPEN_LOAN_TERM_PERIOD OUT NOCOPY VARCHAR2,
972 X_OPEN_MATURITY_DATE OUT NOCOPY DATE,
973 X_FUNDS_RESERVED_FLAG OUT NOCOPY VARCHAR2,
974 X_FUNDS_CHECK_DATE OUT NOCOPY DATE,
975 X_SUBSIDY_RATE OUT NOCOPY NUMBER,
976 X_APPLICATION_ID OUT NOCOPY NUMBER,
977 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
978 X_PARTY_TYPE OUT NOCOPY VARCHAR2,
979 X_FORGIVENESS_FLAG OUT NOCOPY VARCHAR2,
980 X_FORGIVENESS_PERCENT OUT NOCOPY NUMBER,
981 X_DISABLE_BILLING_FLAG OUT NOCOPY VARCHAR2,
982 X_ADD_REQUESTED_AMOUNT OUT NOCOPY NUMBER,
983 X_DISABLE_INT_ACCRUAL_FLAG OUT NOCOPY VARCHAR2
984 ) IS
985
986 BEGIN
987
988 SELECT
989 NVL( LOAN_ID, FND_API.G_MISS_NUM ),
990 NVL( ORG_ID , FND_API.G_MISS_NUM ),
991 NVL( LOAN_NUMBER, FND_API.G_MISS_CHAR ),
992 NVL( LOAN_DESCRIPTION, FND_API.G_MISS_CHAR ),
993 NVL( LOAN_APPLICATION_DATE , FND_API.G_MISS_DATE ),
994 NVL( END_DATE , FND_API.G_MISS_DATE ),
995 NVL( INITIAL_LOAN_BALANCE , FND_API.G_MISS_NUM ),
996 NVL( LAST_PAYMENT_DATE , FND_API.G_MISS_DATE ),
997 NVL( LAST_PAYMENT_AMOUNT, FND_API.G_MISS_NUM ),
998 NVL( LOAN_TERM, FND_API.G_MISS_NUM ),
999 NVL( LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR ),
1000 NVL( AMORTIZED_TERM, FND_API.G_MISS_NUM ),
1001 NVL( AMORTIZED_TERM_PERIOD, FND_API.G_MISS_CHAR ),
1002 NVL( LOAN_STATUS, FND_API.G_MISS_CHAR ),
1003 NVL( LOAN_ASSIGNED_TO, FND_API.G_MISS_NUM ),
1004 NVL( LOAN_CURRENCY, FND_API.G_MISS_CHAR ),
1005 NVL( LOAN_CLASS_CODE , FND_API.G_MISS_CHAR ),
1006 NVL( LOAN_TYPE, FND_API.G_MISS_CHAR ),
1007 NVL( LOAN_SUBTYPE, FND_API.G_MISS_CHAR ),
1008 NVL( LOAN_PURPOSE_CODE , FND_API.G_MISS_CHAR ),
1009 NVL( CUST_ACCOUNT_ID , FND_API.G_MISS_NUM ),
1010 NVL( BILL_TO_ACCT_SITE_ID , FND_API.G_MISS_NUM ),
1011 NVL( LOAN_MATURITY_DATE , FND_API.G_MISS_DATE ),
1012 NVL( LOAN_START_DATE , FND_API.G_MISS_DATE ),
1013 NVL( LOAN_CLOSING_DATE , FND_API.G_MISS_DATE ),
1014 NVL( REFERENCE_ID, FND_API.G_MISS_NUM ),
1015 NVL( REFERENCE_NUMBER, FND_API.G_MISS_CHAR ),
1016 NVL( REFERENCE_DESCRIPTION , FND_API.G_MISS_CHAR ),
1017 NVL( REFERENCE_AMOUNT, FND_API.G_MISS_NUM ),
1018 NVL( PRODUCT_FLAG, FND_API.G_MISS_CHAR ),
1019 NVL( PRIMARY_BORROWER_ID, FND_API.G_MISS_NUM ),
1020 NVL( PRODUCT_ID , FND_API.G_MISS_NUM ),
1021 NVL( REQUESTED_AMOUNT, FND_API.G_MISS_NUM ),
1022 NVL( FUNDED_AMOUNT, FND_API.G_MISS_NUM ),
1023 NVL( LOAN_APPROVAL_DATE , FND_API.G_MISS_DATE ),
1024 NVL( LOAN_APPROVED_BY, FND_API.G_MISS_NUM ),
1025 NVL( ATTRIBUTE_CATEGORY , FND_API.G_MISS_CHAR ),
1026 NVL( ATTRIBUTE1 , FND_API.G_MISS_CHAR ),
1027 NVL( ATTRIBUTE2 , FND_API.G_MISS_CHAR ),
1028 NVL( ATTRIBUTE3 , FND_API.G_MISS_CHAR ),
1029 NVL( ATTRIBUTE4 , FND_API.G_MISS_CHAR ),
1030 NVL( ATTRIBUTE5 , FND_API.G_MISS_CHAR ),
1031 NVL( ATTRIBUTE6 , FND_API.G_MISS_CHAR ),
1032 NVL( ATTRIBUTE7 , FND_API.G_MISS_CHAR ),
1033 NVL( ATTRIBUTE8 , FND_API.G_MISS_CHAR ),
1034 NVL( ATTRIBUTE9 , FND_API.G_MISS_CHAR ),
1035 NVL( ATTRIBUTE10 , FND_API.G_MISS_CHAR ),
1036 NVL( ATTRIBUTE11 , FND_API.G_MISS_CHAR ),
1037 NVL( ATTRIBUTE12 , FND_API.G_MISS_CHAR ),
1038 NVL( ATTRIBUTE13 , FND_API.G_MISS_CHAR ),
1039 NVL( ATTRIBUTE14 , FND_API.G_MISS_CHAR ),
1040 NVL( ATTRIBUTE15 , FND_API.G_MISS_CHAR ),
1041 NVL( ATTRIBUTE16 , FND_API.G_MISS_CHAR ),
1042 NVL( ATTRIBUTE17 , FND_API.G_MISS_CHAR ),
1043 NVL( ATTRIBUTE18 , FND_API.G_MISS_CHAR ),
1044 NVL( ATTRIBUTE19 , FND_API.G_MISS_CHAR ),
1045 NVL( ATTRIBUTE20 , FND_API.G_MISS_CHAR ),
1046 NVL( LAST_BILLED_DATE , FND_API.G_MISS_DATE ),
1047 NVL( CUSTOM_PAYMENTS_FLAG , FND_API.G_MISS_CHAR ),
1048 NVL( BILLED_FLAG , FND_API.G_MISS_CHAR ),
1049 NVL( REFERENCE_NAME , FND_API.G_MISS_CHAR ),
1050 NVL( REFERENCE_TYPE , FND_API.G_MISS_CHAR ),
1051 NVL( REFERENCE_TYPE_ID , FND_API.G_MISS_NUM ),
1052 NVL( USSGL_TRANSACTION_CODE , FND_API.G_MISS_CHAR ),
1053 NVL( GL_DATE , FND_API.G_MISS_DATE ),
1054 NVL( REC_ADJUSTMENT_NUMBER , FND_API.G_MISS_CHAR ),
1055 NVL( CONTACT_REL_PARTY_ID , FND_API.G_MISS_NUM ),
1056 NVL( CONTACT_PERS_PARTY_ID , FND_API.G_MISS_NUM ),
1057 NVL( CREDIT_REVIEW_FLAG , FND_API.G_MISS_CHAR ),
1058 NVL( EXCHANGE_RATE_TYPE , FND_API.G_MISS_CHAR ),
1059 NVL( EXCHANGE_DATE , FND_API.G_MISS_DATE ),
1060 NVL( EXCHANGE_RATE , FND_API.G_MISS_NUM ),
1061 NVL( COLLATERAL_PERCENT , FND_API.G_MISS_NUM ),
1062 NVL( LAST_PAYMENT_NUMBER , FND_API.G_MISS_NUM ),
1063 NVL( LAST_AMORTIZATION_ID , FND_API.G_MISS_NUM ),
1064 NVL( LEGAL_ENTITY_ID , FND_API.G_MISS_NUM ),
1065 NVL( OPEN_TO_TERM_FLAG , FND_API.G_MISS_CHAR ),
1066 NVL( MULTIPLE_FUNDING_FLAG , FND_API.G_MISS_CHAR ),
1067 NVL( LOAN_TYPE_ID , FND_API.G_MISS_NUM ),
1068 NVL( SECONDARY_STATUS , FND_API.G_MISS_CHAR ),
1069 NVL( OPEN_TO_TERM_EVENT, FND_API.G_MISS_CHAR ),
1070 NVL( BALLOON_PAYMENT_TYPE, FND_API.G_MISS_CHAR ),
1071 NVL( BALLOON_PAYMENT_AMOUNT, FND_API.G_MISS_NUM ),
1072 NVL( CURRENT_PHASE, FND_API.G_MISS_CHAR ),
1073 NVL( OPEN_LOAN_START_DATE, FND_API.G_MISS_DATE ),
1074 NVL( OPEN_LOAN_TERM, FND_API.G_MISS_NUM ),
1075 NVL( OPEN_LOAN_TERM_PERIOD, FND_API.G_MISS_CHAR ),
1076 NVL( OPEN_MATURITY_DATE, FND_API.G_MISS_DATE ),
1077 NVL( FUNDS_RESERVED_FLAG, FND_API.G_MISS_CHAR ),
1078 NVL( FUNDS_CHECK_DATE, FND_API.G_MISS_DATE ),
1079 NVL( SUBSIDY_RATE, FND_API.G_MISS_NUM ),
1080 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
1081 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
1082 NVL( PARTY_TYPE, FND_API.G_MISS_CHAR ),
1083 NVL( FORGIVENESS_FLAG , FND_API.G_MISS_CHAR ),
1084 NVL( FORGIVENESS_PERCENT , FND_API.G_MISS_NUM ),
1085 NVL( DISABLE_BILLING_FLAG , FND_API.G_MISS_CHAR ),
1086 NVL( ADD_REQUESTED_AMOUNT , FND_API.G_MISS_NUM ),
1087 NVL( DISABLE_INT_ACCRUAL_FLAG, FND_API.G_MISS_CHAR )
1088 INTO
1089 X_LOAN_ID,
1090 X_ORG_ID,
1091 X_LOAN_NUMBER,
1092 X_LOAN_DESCRIPTION,
1093 X_LOAN_APPLICATION_DATE,
1094 X_END_DATE,
1095 X_INITIAL_LOAN_BALANCE,
1096 X_LAST_PAYMENT_DATE,
1097 X_LAST_PAYMENT_AMOUNT,
1098 X_LOAN_TERM,
1099 X_LOAN_TERM_PERIOD,
1100 X_AMORTIZED_TERM,
1101 X_AMORTIZED_TERM_PERIOD,
1102 X_LOAN_STATUS,
1103 X_LOAN_ASSIGNED_TO,
1104 X_LOAN_CURRENCY,
1105 X_LOAN_CLASS_CODE,
1106 X_LOAN_TYPE,
1107 X_LOAN_SUBTYPE,
1108 X_LOAN_PURPOSE_CODE,
1109 X_CUST_ACCOUNT_ID,
1110 X_BILL_TO_ACCT_SITE_ID,
1111 X_LOAN_MATURITY_DATE,
1112 X_LOAN_START_DATE,
1113 X_LOAN_CLOSING_DATE,
1114 X_REFERENCE_ID,
1115 X_REFERENCE_NUMBER,
1116 X_REFERENCE_DESCRIPTION,
1117 X_REFERENCE_AMOUNT,
1118 X_PRODUCT_FLAG,
1119 X_PRIMARY_BORROWER_ID,
1120 X_PRODUCT_ID,
1121 X_REQUESTED_AMOUNT,
1122 X_FUNDED_AMOUNT,
1123 X_LOAN_APPROVAL_DATE,
1124 X_LOAN_APPROVED_BY,
1125 X_ATTRIBUTE_CATEGORY,
1126 X_ATTRIBUTE1,
1127 X_ATTRIBUTE2,
1128 X_ATTRIBUTE3,
1129 X_ATTRIBUTE4,
1130 X_ATTRIBUTE5,
1131 X_ATTRIBUTE6,
1132 X_ATTRIBUTE7,
1133 X_ATTRIBUTE8,
1134 X_ATTRIBUTE9,
1135 X_ATTRIBUTE10,
1136 X_ATTRIBUTE11,
1137 X_ATTRIBUTE12,
1138 X_ATTRIBUTE13,
1139 X_ATTRIBUTE14,
1140 X_ATTRIBUTE15,
1141 X_ATTRIBUTE16,
1142 X_ATTRIBUTE17,
1143 X_ATTRIBUTE18,
1144 X_ATTRIBUTE19,
1145 X_ATTRIBUTE20,
1146 X_LAST_BILLED_DATE,
1147 X_CUSTOM_PAYMENTS_FLAG,
1148 X_BILLED_FLAG,
1149 X_REFERENCE_NAME,
1150 X_REFERENCE_TYPE,
1151 X_REFERENCE_TYPE_ID,
1152 X_USSGL_TRANSACTION_CODE,
1153 X_GL_DATE,
1154 X_REC_ADJUSTMENT_NUMBER,
1155 X_CONTACT_REL_PARTY_ID,
1156 X_CONTACT_PERS_PARTY_ID,
1157 X_CREDIT_REVIEW_FLAG,
1158 X_EXCHANGE_RATE_TYPE,
1159 X_EXCHANGE_DATE,
1160 X_EXCHANGE_RATE,
1161 X_COLLATERAL_PERCENT,
1162 X_LAST_PAYMENT_NUMBER,
1163 X_LAST_AMORTIZATION_ID,
1164 X_LEGAL_ENTITY_ID,
1165 X_OPEN_TO_TERM_FLAG,
1166 X_MULTIPLE_FUNDING_FLAG,
1167 X_LOAN_TYPE_ID,
1168 X_SECONDARY_STATUS,
1169 X_OPEN_TO_TERM_EVENT,
1170 X_BALLOON_PAYMENT_TYPE,
1171 X_BALLOON_PAYMENT_AMOUNT,
1172 X_CURRENT_PHASE,
1173 X_OPEN_LOAN_START_DATE,
1174 X_OPEN_LOAN_TERM,
1175 X_OPEN_LOAN_TERM_PERIOD,
1176 X_OPEN_MATURITY_DATE,
1177 X_FUNDS_RESERVED_FLAG,
1178 X_FUNDS_CHECK_DATE,
1179 X_SUBSIDY_RATE,
1180 X_APPLICATION_ID,
1181 X_CREATED_BY_MODULE,
1182 X_PARTY_TYPE,
1183 X_FORGIVENESS_FLAG,
1184 X_FORGIVENESS_PERCENT,
1185 X_DISABLE_BILLING_FLAG,
1186 X_ADD_REQUESTED_AMOUNT,
1187 X_DISABLE_INT_ACCRUAL_FLAG
1188 FROM LNS_LOAN_HEADERS_ALL_VL
1189 WHERE LOAN_ID = X_LOAN_ID;
1190
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND THEN
1193 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_NO_RECORD' );
1194 FND_MESSAGE.SET_TOKEN( 'RECORD', 'loan_header_rec');
1195 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_LOAN_ID ) );
1196 FND_MSG_PUB.ADD;
1197 RAISE FND_API.G_EXC_ERROR;
1198
1199 END Select_Row;
1200
1201 PROCEDURE Delete_Row (
1202 X_LOAN_ID IN NUMBER
1203 ) IS
1204
1205 BEGIN
1206
1207 DELETE FROM LNS_LOAN_HEADERS_ALL_TL
1208 WHERE LOAN_ID = X_LOAN_ID;
1209
1210 IF ( SQL%NOTFOUND ) THEN
1211 RAISE NO_DATA_FOUND;
1212 END IF;
1213
1214
1215 DELETE FROM LNS_LOAN_HEADERS_ALL
1216 WHERE LOAN_ID = X_LOAN_ID;
1217
1218 IF ( SQL%NOTFOUND ) THEN
1219 RAISE NO_DATA_FOUND;
1220 END IF;
1221
1222
1223 END Delete_Row;
1224
1225
1226
1227 END LNS_LOAN_HEADER_ALL_PKG;