1 PACKAGE BODY LNS_FEE_SCHEDULES_PKG AS
2 /* $Header: LNS_FEESC_TBLH_B.pls 120.1.12010000.3 2010/02/24 02:38:48 mbolli ship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_FEE_SCHEDULE_ID IN OUT NOCOPY NUMBER
7 ,P_FEE_ID IN NUMBER
8 ,P_LOAN_ID IN NUMBER
9 ,P_FEE_AMOUNT IN NUMBER
10 ,P_FEE_INSTALLMENT IN NUMBER
11 ,P_FEE_DESCRIPTION IN VARCHAR2
12 ,P_ACTIVE_FLAG IN VARCHAR2
13 ,P_BILLED_FLAG IN VARCHAR2
14 ,P_FEE_WAIVABLE_FLAG IN VARCHAR2
15 ,P_WAIVED_AMOUNT IN NUMBER
16 ,P_CREATED_BY IN NUMBER
17 ,P_CREATION_DATE IN DATE
18 ,P_LAST_UPDATED_BY IN NUMBER
19 ,P_LAST_UPDATE_DATE IN DATE
20 ,P_LAST_UPDATE_LOGIN IN NUMBER
21 ,P_PROGRAM_ID IN NUMBER
22 ,P_REQUEST_ID IN NUMBER
23 ,P_OBJECT_VERSION_NUMBER IN NUMBER
24 ,P_DISB_HEADER_ID IN NUMBER
25 ,P_PHASE IN VARCHAR2
26 ) IS
27 BEGIN
28 INSERT INTO LNS_FEE_SCHEDULES
29 (
30 FEE_SCHEDULE_ID
31 ,FEE_ID
32 ,LOAN_ID
33 ,FEE_AMOUNT
34 ,FEE_INSTALLMENT
35 ,FEE_DESCRIPTION
36 ,ACTIVE_FLAG
37 ,BILLED_FLAG
38 ,FEE_WAIVABLE_FLAG
39 ,WAIVED_AMOUNT
40 ,CREATED_BY
41 ,CREATION_DATE
42 ,LAST_UPDATED_BY
43 ,LAST_UPDATE_DATE
44 ,LAST_UPDATE_LOGIN
45 ,PROGRAM_ID
46 ,REQUEST_ID
47 ,OBJECT_VERSION_NUMBER
48 ,DISB_HEADER_ID
49 ,PHASE
50 ) VALUES (
51 DECODE(X_FEE_SCHEDULE_ID, FND_API.G_MISS_NUM,
52 LNS_FEE_SCHEDULE_S.NEXTVAL, NULL, LNS_FEE_SCHEDULE_S.NEXTVAL, X_FEE_SCHEDULE_ID)
53 ,DECODE(P_FEE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_ID)
54 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
55 ,DECODE(P_FEE_AMOUNT, FND_API.G_MISS_NUM, NULL, P_FEE_AMOUNT)
56 ,DECODE(P_FEE_INSTALLMENT, FND_API.G_MISS_NUM, NULL,
57 P_FEE_INSTALLMENT)
58 ,DECODE(P_FEE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,
59 P_FEE_DESCRIPTION)
60 ,DECODE(P_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL, P_ACTIVE_FLAG)
61 ,DECODE(P_BILLED_FLAG, FND_API.G_MISS_CHAR, NULL, P_BILLED_FLAG)
62 ,DECODE(P_FEE_WAIVABLE_FLAG, FND_API.G_MISS_CHAR, NULL,
63 P_FEE_WAIVABLE_FLAG)
64 ,DECODE(P_WAIVED_AMOUNT, FND_API.G_MISS_NUM, NULL,
65 P_WAIVED_AMOUNT)
66 ,LNS_UTILITY_PUB.CREATED_BY
67 ,LNS_UTILITY_PUB.CREATION_DATE
68 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
69 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
70 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
71 ,DECODE(P_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, P_PROGRAM_ID)
72 ,DECODE(P_REQUEST_ID, FND_API.G_MISS_NUM, NULL, P_REQUEST_ID)
73 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL,
74 P_OBJECT_VERSION_NUMBER)
75 ,DECODE(P_DISB_HEADER_ID, FND_API.G_MISS_NUM, NULL,
76 P_DISB_HEADER_ID)
77 ,DECODE(P_PHASE, FND_API.G_MISS_CHAR, NULL, P_PHASE)
78 ) RETURNING
79 FEE_SCHEDULE_ID
80 INTO
81 X_FEE_SCHEDULE_ID;
82 END Insert_Row;
83
84 /* Update_Row procedure */
85 PROCEDURE Update_Row(
86 P_FEE_SCHEDULE_ID IN NUMBER
87 ,P_FEE_ID IN NUMBER
88 ,P_LOAN_ID IN NUMBER
89 ,P_FEE_AMOUNT IN NUMBER
90 ,P_FEE_INSTALLMENT IN NUMBER
91 ,P_FEE_DESCRIPTION IN VARCHAR2
92 ,P_ACTIVE_FLAG IN VARCHAR2
93 ,P_BILLED_FLAG IN VARCHAR2
94 ,P_FEE_WAIVABLE_FLAG IN VARCHAR2
95 ,P_WAIVED_AMOUNT IN NUMBER
96 ,P_LAST_UPDATED_BY IN NUMBER
97 ,P_LAST_UPDATE_DATE IN DATE
98 ,P_LAST_UPDATE_LOGIN IN NUMBER
99 ,P_PROGRAM_ID IN NUMBER
100 ,P_REQUEST_ID IN NUMBER
101 ,P_OBJECT_VERSION_NUMBER IN NUMBER
102 ,P_DISB_HEADER_ID IN NUMBER
103 ,P_PHASE IN VARCHAR2
104 ) IS
105 BEGIN
106 UPDATE LNS_FEE_SCHEDULES SET
107 FEE_ID = DECODE(P_FEE_ID, NULL, FEE_ID, FND_API.G_MISS_NUM,
108 NULL, P_FEE_ID)
109 ,LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM,
110 NULL, P_LOAN_ID)
111 ,FEE_AMOUNT = DECODE(P_FEE_AMOUNT, NULL, FEE_AMOUNT,
112 FND_API.G_MISS_NUM, NULL, P_FEE_AMOUNT)
113 ,FEE_INSTALLMENT = DECODE(P_FEE_INSTALLMENT, NULL,
114 FEE_INSTALLMENT, FND_API.G_MISS_NUM, NULL, P_FEE_INSTALLMENT)
115 ,FEE_DESCRIPTION = DECODE(P_FEE_DESCRIPTION, NULL,
116 FEE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, P_FEE_DESCRIPTION)
117 ,ACTIVE_FLAG = DECODE(P_ACTIVE_FLAG, NULL, ACTIVE_FLAG,
118 FND_API.G_MISS_CHAR, NULL, P_ACTIVE_FLAG)
119 ,BILLED_FLAG = DECODE(P_BILLED_FLAG, NULL, BILLED_FLAG,
120 FND_API.G_MISS_CHAR, NULL, P_BILLED_FLAG)
121 ,FEE_WAIVABLE_FLAG = DECODE(P_FEE_WAIVABLE_FLAG, NULL,
122 FEE_WAIVABLE_FLAG, FND_API.G_MISS_CHAR, NULL, P_FEE_WAIVABLE_FLAG)
123 ,WAIVED_AMOUNT = DECODE(P_WAIVED_AMOUNT, NULL, WAIVED_AMOUNT,
124 FND_API.G_MISS_NUM, NULL, P_WAIVED_AMOUNT)
125 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
126 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
127 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
128 ,PROGRAM_ID = DECODE(P_PROGRAM_ID, NULL, PROGRAM_ID,
129 FND_API.G_MISS_NUM, NULL, P_PROGRAM_ID)
130 ,REQUEST_ID = DECODE(P_REQUEST_ID, NULL, REQUEST_ID,
131 FND_API.G_MISS_NUM, NULL, P_REQUEST_ID)
132 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL,
133 OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
134 ,DISB_HEADER_ID = DECODE(P_DISB_HEADER_ID, NULL, DISB_HEADER_ID,
135 FND_API.G_MISS_NUM, NULL, P_DISB_HEADER_ID)
136 ,PHASE = DECODE(P_PHASE, NULL, PHASE, FND_API.G_MISS_CHAR, NULL,
137 P_PHASE)
138 WHERE FEE_SCHEDULE_ID = P_FEE_SCHEDULE_ID;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 END Update_Row;
144
145 /* Delete_Row procedure */
146 PROCEDURE Delete_Row(P_FEE_SCHEDULE_ID IN NUMBER) IS
147 BEGIN
148 DELETE FROM LNS_FEE_SCHEDULES
149 WHERE FEE_SCHEDULE_ID = P_FEE_SCHEDULE_ID;
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154 END Delete_Row;
155
156 /* Lock_Row procedure */
157 PROCEDURE Lock_Row(
158 P_FEE_SCHEDULE_ID IN NUMBER
159 ,P_FEE_ID IN NUMBER
160 ,P_LOAN_ID IN NUMBER
161 ,P_FEE_AMOUNT IN NUMBER
162 ,P_FEE_INSTALLMENT IN NUMBER
163 ,P_FEE_DESCRIPTION IN VARCHAR2
164 ,P_ACTIVE_FLAG IN VARCHAR2
165 ,P_BILLED_FLAG IN VARCHAR2
166 ,P_FEE_WAIVABLE_FLAG IN VARCHAR2
167 ,P_WAIVED_AMOUNT IN NUMBER
168 ,P_CREATED_BY IN NUMBER
169 ,P_CREATION_DATE IN DATE
170 ,P_LAST_UPDATED_BY IN NUMBER
171 ,P_LAST_UPDATE_DATE IN DATE
172 ,P_LAST_UPDATE_LOGIN IN NUMBER
173 ,P_PROGRAM_ID IN NUMBER
174 ,P_REQUEST_ID IN NUMBER
175 ,P_OBJECT_VERSION_NUMBER IN NUMBER
176 ,P_DISB_HEADER_ID IN NUMBER
177 ,P_PHASE IN VARCHAR2
178 ) IS
179 CURSOR C IS SELECT * FROM LNS_FEE_SCHEDULES
180 WHERE FEE_SCHEDULE_ID = P_FEE_SCHEDULE_ID
181 FOR UPDATE of FEE_SCHEDULE_ID NOWAIT;
182 Recinfo C%ROWTYPE;
183 BEGIN
184 OPEN C;
185 FETCH C INTO Recinfo;
186 IF (C%NOTFOUND) THEN
187 CLOSE C;
188 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
189 APP_EXCEPTION.Raise_Exception;
190 END IF;
191 CLOSE C;
192
193 IF (
194 (Recinfo.FEE_SCHEDULE_ID = P_FEE_SCHEDULE_ID)
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.LOAN_ID = P_LOAN_ID)
199 OR ( (Recinfo.LOAN_ID IS NULL)
200 AND (P_LOAN_ID IS NULL)))
201 AND ( (Recinfo.FEE_AMOUNT = P_FEE_AMOUNT)
202 OR ( (Recinfo.FEE_AMOUNT IS NULL)
203 AND (P_FEE_AMOUNT IS NULL)))
204 AND ( (Recinfo.FEE_INSTALLMENT = P_FEE_INSTALLMENT)
205 OR ( (Recinfo.FEE_INSTALLMENT IS NULL)
206 AND (P_FEE_INSTALLMENT IS NULL)))
207 AND ( (Recinfo.FEE_DESCRIPTION = P_FEE_DESCRIPTION)
208 OR ( (Recinfo.FEE_DESCRIPTION IS NULL)
209 AND (P_FEE_DESCRIPTION IS NULL)))
210 AND ( (Recinfo.ACTIVE_FLAG = P_ACTIVE_FLAG)
211 OR ( (Recinfo.ACTIVE_FLAG IS NULL)
212 AND (P_ACTIVE_FLAG IS NULL)))
213 AND ( (Recinfo.BILLED_FLAG = P_BILLED_FLAG)
214 OR ( (Recinfo.BILLED_FLAG IS NULL)
215 AND (P_BILLED_FLAG IS NULL)))
216 AND ( (Recinfo.FEE_WAIVABLE_FLAG = P_FEE_WAIVABLE_FLAG)
217 OR ( (Recinfo.FEE_WAIVABLE_FLAG IS NULL)
218 AND (P_FEE_WAIVABLE_FLAG IS NULL)))
219 AND ( (Recinfo.WAIVED_AMOUNT = P_WAIVED_AMOUNT)
220 OR ( (Recinfo.WAIVED_AMOUNT IS NULL)
221 AND (P_WAIVED_AMOUNT IS NULL)))
222 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
223 OR ( (Recinfo.CREATED_BY IS NULL)
224 AND (P_CREATED_BY IS NULL)))
225 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
226 OR ( (Recinfo.CREATION_DATE IS NULL)
227 AND (P_CREATION_DATE IS NULL)))
228 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
229 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
230 AND (P_LAST_UPDATED_BY IS NULL)))
231 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
232 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
233 AND (P_LAST_UPDATE_DATE IS NULL)))
234 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
235 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
236 AND (P_LAST_UPDATE_LOGIN IS NULL)))
237 AND ( (Recinfo.PROGRAM_ID = P_PROGRAM_ID)
238 OR ( (Recinfo.PROGRAM_ID IS NULL)
239 AND (P_PROGRAM_ID IS NULL)))
240 AND ( (Recinfo.REQUEST_ID = P_REQUEST_ID)
241 OR ( (Recinfo.REQUEST_ID IS NULL)
242 AND (P_REQUEST_ID 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.DISB_HEADER_ID = P_DISB_HEADER_ID)
247 OR ( (Recinfo.DISB_HEADER_ID IS NULL)
248 AND (P_DISB_HEADER_ID IS NULL)))
249 AND ( (Recinfo.PHASE = P_PHASE)
250 OR ( (Recinfo.PHASE IS NULL)
251 AND (P_PHASE IS NULL)))
252 ) THEN
253 return;
254 ELSE
255 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
256 APP_EXCEPTION.Raise_Exception;
257 END IF;
258 END Lock_Row;
259 END;
260
[Home] [Help]