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