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