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