1 PACKAGE BODY LNS_FEE_ASSIGNMENTS_PKG AS
2 /* $Header: LNS_FASGM_TBLH_B.pls 120.6 2010/06/28 10:34:57 gparuchu ship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_FEE_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
7 ,P_LOAN_ID IN NUMBER
8 ,P_FEE_ID IN NUMBER
9 ,P_FEE_TYPE IN VARCHAR2
10 ,P_FEE IN NUMBER
11 ,P_FEE_BASIS IN VARCHAR2
12 ,P_NUMBER_GRACE_DAYS IN NUMBER
13 ,P_CREATED_BY IN NUMBER
14 ,P_CREATION_DATE IN DATE
15 ,P_LAST_UPDATED_BY IN NUMBER
16 ,P_LAST_UPDATE_DATE IN DATE
17 ,P_LAST_UPDATE_LOGIN IN NUMBER
18 ,P_OBJECT_VERSION_NUMBER IN NUMBER
19 ,P_COLLECTED_THIRD_PARTY_FLAG IN VARCHAR2
20 ,P_BILLING_OPTION IN VARCHAR2
21 ,P_RATE_TYPE IN VARCHAR2
22 ,P_BEGIN_INSTALLMENT_NUMBER IN NUMBER
23 ,P_END_INSTALLMENT_NUMBER IN NUMBER
24 ,P_NUMBER_OF_PAYMENTS IN NUMBER
25 ,P_START_DATE_ACTIVE IN DATE
26 ,P_END_DATE_ACTIVE IN DATE
27 ,P_DISB_HEADER_ID IN NUMBER
28 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
29 ,P_OPEN_PHASE_FLAG IN VARCHAR2
30 ,P_PHASE IN VARCHAR2
31 ,P_CUSTOM_PROCEDURE IN VARCHAR2
32 ) IS
33 BEGIN
34 INSERT INTO LNS_FEE_ASSIGNMENTS
35 (
36 FEE_ASSIGNMENT_ID
37 ,LOAN_ID
38 ,FEE_ID
39 ,FEE_TYPE
40 ,FEE
41 ,FEE_BASIS
42 ,NUMBER_GRACE_DAYS
43 ,CREATED_BY
44 ,CREATION_DATE
45 ,LAST_UPDATED_BY
46 ,LAST_UPDATE_DATE
47 ,LAST_UPDATE_LOGIN
48 ,OBJECT_VERSION_NUMBER
49 ,COLLECTED_THIRD_PARTY_FLAG
50 ,BILLING_OPTION
51 ,RATE_TYPE
52 ,BEGIN_INSTALLMENT_NUMBER
53 ,END_INSTALLMENT_NUMBER
54 ,NUMBER_OF_PAYMENTS
55 ,START_DATE_ACTIVE
56 ,END_DATE_ACTIVE
57 ,DISB_HEADER_ID
58 ,DELETE_DISABLED_FLAG
59 ,OPEN_PHASE_FLAG
60 ,PHASE
61 ,CUSTOM_PROCEDURE
62 ) VALUES (
63 DECODE(X_FEE_ASSIGNMENT_ID, FND_API.G_MISS_NUM, LNS_FEE_ASSIGNMENTS_S.NEXTVAL, NULL, LNS_FEE_ASSIGNMENTS_S.NEXTVAL, X_FEE_ASSIGNMENT_ID)
64 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
65 ,DECODE(P_FEE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_ID)
66 ,DECODE(P_FEE_TYPE, FND_API.G_MISS_CHAR, NULL, P_FEE_TYPE)
67 ,DECODE(P_FEE, FND_API.G_MISS_NUM, NULL, P_FEE)
68 ,DECODE(P_FEE_BASIS, FND_API.G_MISS_CHAR, NULL, P_FEE_BASIS)
69 ,DECODE(P_NUMBER_GRACE_DAYS, FND_API.G_MISS_NUM, NULL, P_NUMBER_GRACE_DAYS)
70 ,LNS_UTILITY_PUB.CREATED_BY
71 ,LNS_UTILITY_PUB.CREATION_DATE
72 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
73 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
74 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
75 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
76 ,DECODE(P_COLLECTED_THIRD_PARTY_FLAG, FND_API.G_MISS_CHAR, NULL, P_COLLECTED_THIRD_PARTY_FLAG)
77 ,DECODE(P_BILLING_OPTION, FND_API.G_MISS_CHAR, NULL, P_BILLING_OPTION)
78 ,DECODE(P_RATE_TYPE, FND_API.G_MISS_CHAR, NULL, P_RATE_TYPE)
79 ,DECODE(P_BEGIN_INSTALLMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_BEGIN_INSTALLMENT_NUMBER)
80 ,DECODE(P_END_INSTALLMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_END_INSTALLMENT_NUMBER)
81 ,DECODE(P_NUMBER_OF_PAYMENTS, FND_API.G_MISS_NUM, NULL, P_NUMBER_OF_PAYMENTS)
82 ,DECODE(P_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
83 ,DECODE(P_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
84 ,DECODE(P_DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
85 ,DECODE(P_DELETE_DISABLED_FLAG, FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG )
86 ,DECODE(P_OPEN_PHASE_FLAG, FND_API.G_MISS_CHAR, NULL, P_OPEN_PHASE_FLAG )
87 ,DECODE(P_PHASE, FND_API.G_MISS_CHAR, NULL, P_PHASE )
88 ,DECODE(P_CUSTOM_PROCEDURE, FND_API.G_MISS_CHAR, NULL, P_CUSTOM_PROCEDURE )
89 ) RETURNING
90 FEE_ASSIGNMENT_ID
91 INTO
92 X_FEE_ASSIGNMENT_ID;
93 END Insert_Row;
94
95 /* Update_Row procedure */
96 PROCEDURE Update_Row(
97 P_FEE_ASSIGNMENT_ID IN NUMBER
98 ,P_LOAN_ID IN NUMBER
99 ,P_FEE_ID IN NUMBER
100 ,P_FEE_TYPE IN VARCHAR2
101 ,P_FEE IN NUMBER
102 ,P_FEE_BASIS IN VARCHAR2
103 ,P_NUMBER_GRACE_DAYS IN NUMBER
104 ,P_LAST_UPDATED_BY IN NUMBER
105 ,P_LAST_UPDATE_DATE IN DATE
106 ,P_LAST_UPDATE_LOGIN IN NUMBER
107 ,P_OBJECT_VERSION_NUMBER IN NUMBER
108 ,P_COLLECTED_THIRD_PARTY_FLAG IN VARCHAR2
109 ,P_BILLING_OPTION IN VARCHAR2
110 ,P_RATE_TYPE IN VARCHAR2
111 ,P_BEGIN_INSTALLMENT_NUMBER IN NUMBER
112 ,P_END_INSTALLMENT_NUMBER IN NUMBER
113 ,P_NUMBER_OF_PAYMENTS IN NUMBER
114 ,P_START_DATE_ACTIVE IN DATE
115 ,P_END_DATE_ACTIVE IN DATE
116 ,P_DISB_HEADER_ID IN NUMBER
117 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
118 ,P_OPEN_PHASE_FLAG IN VARCHAR2
119 ,P_PHASE IN VARCHAR2
120 ,P_CUSTOM_PROCEDURE IN VARCHAR2
121 ) IS
122 BEGIN
123 UPDATE LNS_FEE_ASSIGNMENTS SET
124 LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
125 ,FEE_ID = DECODE(P_FEE_ID, NULL, FEE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_ID)
126 ,FEE_TYPE = DECODE(P_FEE_TYPE, NULL, FEE_TYPE, FND_API.G_MISS_CHAR, NULL, P_FEE_TYPE)
127 ,FEE = DECODE(P_FEE, NULL, FEE, FND_API.G_MISS_NUM, NULL, P_FEE)
128 ,FEE_BASIS = DECODE(P_FEE_BASIS, NULL, FEE_BASIS, FND_API.G_MISS_CHAR, NULL, P_FEE_BASIS)
129 ,NUMBER_GRACE_DAYS = DECODE(P_NUMBER_GRACE_DAYS, NULL, NUMBER_GRACE_DAYS, FND_API.G_MISS_NUM, NULL, P_NUMBER_GRACE_DAYS)
130 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
131 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
132 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
133 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
134 ,COLLECTED_THIRD_PARTY_FLAG = DECODE(P_COLLECTED_THIRD_PARTY_FLAG, NULL, COLLECTED_THIRD_PARTY_FLAG, FND_API.G_MISS_CHAR, NULL, P_COLLECTED_THIRD_PARTY_FLAG)
135 ,BILLING_OPTION = DECODE(P_BILLING_OPTION, NULL, BILLING_OPTION, FND_API.G_MISS_CHAR, NULL, P_BILLING_OPTION)
136 ,RATE_TYPE = DECODE(P_RATE_TYPE, NULL, RATE_TYPE, FND_API.G_MISS_CHAR, NULL, P_RATE_TYPE)
137 ,BEGIN_INSTALLMENT_NUMBER = DECODE(P_BEGIN_INSTALLMENT_NUMBER, NULL, BEGIN_INSTALLMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_BEGIN_INSTALLMENT_NUMBER)
138 ,END_INSTALLMENT_NUMBER = DECODE(P_END_INSTALLMENT_NUMBER, NULL, END_INSTALLMENT_NUMBER, FND_API.G_MISS_NUM, NULL, P_END_INSTALLMENT_NUMBER)
139 ,NUMBER_OF_PAYMENTS = DECODE(P_NUMBER_OF_PAYMENTS, NULL, NUMBER_OF_PAYMENTS, FND_API.G_MISS_NUM, NULL, P_NUMBER_OF_PAYMENTS)
140 ,START_DATE_ACTIVE = DECODE(P_START_DATE_ACTIVE, NULL, START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_START_DATE_ACTIVE)
141 ,END_DATE_ACTIVE = DECODE(P_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, P_END_DATE_ACTIVE)
142 ,DISB_HEADER_ID = DECODE(P_DISB_HEADER_ID, NULL, DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
143 ,DELETE_DISABLED_FLAG = DECODE(P_DELETE_DISABLED_FLAG , NULL, DELETE_DISABLED_FLAG , FND_API.G_MISS_CHAR, NULL, P_DELETE_DISABLED_FLAG )
144 ,OPEN_PHASE_FLAG = DECODE(P_OPEN_PHASE_FLAG , NULL, OPEN_PHASE_FLAG , FND_API.G_MISS_CHAR, NULL, P_OPEN_PHASE_FLAG )
145 ,PHASE = DECODE(P_PHASE , NULL, PHASE , FND_API.G_MISS_CHAR, NULL, P_PHASE )
146 ,CUSTOM_PROCEDURE = DECODE(P_CUSTOM_PROCEDURE , NULL, CUSTOM_PROCEDURE , FND_API.G_MISS_CHAR, NULL, P_CUSTOM_PROCEDURE )
147 WHERE FEE_ASSIGNMENT_ID = P_FEE_ASSIGNMENT_ID;
148
149 if (sql%notfound) then
150 raise no_data_found;
151 end if;
152 END Update_Row;
153
154 /* Delete_Row procedure */
155 PROCEDURE Delete_Row(P_FEE_ASSIGNMENT_ID IN NUMBER) IS
156 BEGIN
157 DELETE FROM LNS_FEE_ASSIGNMENTS
158 WHERE FEE_ASSIGNMENT_ID = P_FEE_ASSIGNMENT_ID;
159
160 if (sql%notfound) then
161 raise no_data_found;
162 end if;
163 END Delete_Row;
164
165 /* Lock_Row procedure */
166 PROCEDURE Lock_Row(
167 P_FEE_ASSIGNMENT_ID IN NUMBER
168 ,P_LOAN_ID IN NUMBER
169 ,P_FEE_ID IN NUMBER
170 ,P_FEE_TYPE IN VARCHAR2
171 ,P_FEE IN NUMBER
172 ,P_FEE_BASIS IN VARCHAR2
173 ,P_NUMBER_GRACE_DAYS IN NUMBER
174 ,P_CREATED_BY IN NUMBER
175 ,P_CREATION_DATE IN DATE
176 ,P_LAST_UPDATED_BY IN NUMBER
177 ,P_LAST_UPDATE_DATE IN DATE
178 ,P_LAST_UPDATE_LOGIN IN NUMBER
179 ,P_OBJECT_VERSION_NUMBER IN NUMBER
180 ,P_COLLECTED_THIRD_PARTY_FLAG IN VARCHAR2
181 ,P_BILLING_OPTION IN VARCHAR2
182 ,P_RATE_TYPE IN VARCHAR2
183 ,P_BEGIN_INSTALLMENT_NUMBER IN NUMBER
184 ,P_END_INSTALLMENT_NUMBER IN NUMBER
185 ,P_NUMBER_OF_PAYMENTS IN NUMBER
186 ,P_START_DATE_ACTIVE IN DATE
187 ,P_END_DATE_ACTIVE IN DATE
188 ,P_DISB_HEADER_ID IN NUMBER
189 ,P_DELETE_DISABLED_FLAG IN VARCHAR2
190 ,P_OPEN_PHASE_FLAG IN VARCHAR2
191 ,P_PHASE IN VARCHAR2
192 ,P_CUSTOM_PROCEDURE IN VARCHAR2
193 ) IS
194 CURSOR C IS SELECT * FROM LNS_FEE_ASSIGNMENTS
195 WHERE FEE_ASSIGNMENT_ID = P_FEE_ASSIGNMENT_ID
196 FOR UPDATE of FEE_ASSIGNMENT_ID NOWAIT;
197 Recinfo C%ROWTYPE;
198 BEGIN
199 OPEN C;
200 FETCH C INTO Recinfo;
201 IF (C%NOTFOUND) THEN
202 CLOSE C;
203 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
204 APP_EXCEPTION.Raise_Exception;
205 END IF;
206 CLOSE C;
207
208 IF (
209 (Recinfo.FEE_ASSIGNMENT_ID = P_FEE_ASSIGNMENT_ID)
210 AND ( (Recinfo.LOAN_ID = P_LOAN_ID)
211 OR ( (Recinfo.LOAN_ID IS NULL)
212 AND (P_LOAN_ID IS NULL)))
213 AND ( (Recinfo.FEE_ID = P_FEE_ID)
214 OR ( (Recinfo.FEE_ID IS NULL)
215 AND (P_FEE_ID IS NULL)))
216 AND ( (Recinfo.FEE_TYPE = P_FEE_TYPE)
217 OR ( (Recinfo.FEE_TYPE IS NULL)
218 AND (P_FEE_TYPE IS NULL)))
219 AND ( (Recinfo.FEE = P_FEE)
220 OR ( (Recinfo.FEE IS NULL)
221 AND (P_FEE IS NULL)))
222 AND ( (Recinfo.FEE_BASIS = P_FEE_BASIS)
223 OR ( (Recinfo.FEE_BASIS IS NULL)
224 AND (P_FEE_BASIS IS NULL)))
225 AND ( (Recinfo.NUMBER_GRACE_DAYS = P_NUMBER_GRACE_DAYS)
226 OR ( (Recinfo.NUMBER_GRACE_DAYS IS NULL)
227 AND (P_NUMBER_GRACE_DAYS IS NULL)))
228 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
229 OR ( (Recinfo.CREATED_BY IS NULL)
230 AND (P_CREATED_BY IS NULL)))
231 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
232 OR ( (Recinfo.CREATION_DATE IS NULL)
233 AND (P_CREATION_DATE IS NULL)))
234 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
235 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
236 AND (P_LAST_UPDATED_BY IS NULL)))
237 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
238 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
239 AND (P_LAST_UPDATE_DATE IS NULL)))
240 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
241 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
242 AND (P_LAST_UPDATE_LOGIN IS NULL)))
243 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
244 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
245 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
246 AND ( (Recinfo.COLLECTED_THIRD_PARTY_FLAG = P_COLLECTED_THIRD_PARTY_FLAG)
247 OR ( (Recinfo.COLLECTED_THIRD_PARTY_FLAG IS NULL)
248 AND (P_COLLECTED_THIRD_PARTY_FLAG IS NULL)))
249 AND ( (Recinfo.BILLING_OPTION = P_BILLING_OPTION)
250 OR ( (Recinfo.BILLING_OPTION IS NULL)
251 AND (P_BILLING_OPTION IS NULL)))
252 AND ( (Recinfo.RATE_TYPE = P_RATE_TYPE)
253 OR ( (Recinfo.RATE_TYPE IS NULL)
254 AND (P_RATE_TYPE IS NULL)))
255 AND ( (Recinfo.BEGIN_INSTALLMENT_NUMBER = P_BEGIN_INSTALLMENT_NUMBER)
256 OR ( (Recinfo.BEGIN_INSTALLMENT_NUMBER IS NULL)
257 AND (P_BEGIN_INSTALLMENT_NUMBER IS NULL)))
258 AND ( (Recinfo.END_INSTALLMENT_NUMBER = P_END_INSTALLMENT_NUMBER)
259 OR ( (Recinfo.END_INSTALLMENT_NUMBER IS NULL)
260 AND (P_END_INSTALLMENT_NUMBER IS NULL)))
261 AND ( (Recinfo.NUMBER_OF_PAYMENTS = P_NUMBER_OF_PAYMENTS)
262 OR ( (Recinfo.NUMBER_OF_PAYMENTS IS NULL)
263 AND (P_NUMBER_OF_PAYMENTS IS NULL)))
264 AND ( (Recinfo.START_DATE_ACTIVE = P_START_DATE_ACTIVE)
265 OR ( (Recinfo.START_DATE_ACTIVE IS NULL)
266 AND (P_START_DATE_ACTIVE IS NULL)))
267 AND ( (Recinfo.END_DATE_ACTIVE = P_END_DATE_ACTIVE)
268 OR ( (Recinfo.END_DATE_ACTIVE IS NULL)
269 AND (P_END_DATE_ACTIVE IS NULL)))
270 AND ( (Recinfo.DISB_HEADER_ID = P_DISB_HEADER_ID)
271 OR ( (Recinfo.DISB_HEADER_ID IS NULL)
272 AND (P_DISB_HEADER_ID IS NULL)))
273 AND ( (Recinfo.DELETE_DISABLED_FLAG = P_DELETE_DISABLED_FLAG )
274 OR ( (Recinfo.DELETE_DISABLED_FLAG IS NULL)
275 AND (P_DELETE_DISABLED_FLAG IS NULL)))
276 AND ( (Recinfo.OPEN_PHASE_FLAG = P_OPEN_PHASE_FLAG )
277 OR ( (Recinfo.OPEN_PHASE_FLAG IS NULL)
278 AND (P_OPEN_PHASE_FLAG IS NULL)))
279 AND ( (Recinfo.PHASE = P_PHASE )
280 OR ( (Recinfo.PHASE IS NULL)
281 AND (P_PHASE IS NULL)))
282 AND ( (Recinfo.CUSTOM_PROCEDURE = P_CUSTOM_PROCEDURE )
283 OR ( (Recinfo.CUSTOM_PROCEDURE IS NULL)
284 AND (P_CUSTOM_PROCEDURE IS NULL)))
285 ) THEN
286 return;
287 ELSE
288 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
289 APP_EXCEPTION.Raise_Exception;
290 END IF;
291 END Lock_Row;
292 END;
293
[Home] [Help]