1 PACKAGE BODY IEX_DELINQUENCIES_ALL_PKG AS
2 /* $Header: iextdelb.pls 120.0 2004/01/24 03:21:48 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_DELINQUENCY_ID NUMBER
9 ,p_LAST_UPDATE_DATE DATE
10 ,p_LAST_UPDATED_BY NUMBER
11 ,p_LAST_UPDATE_LOGIN NUMBER
12 ,p_CREATION_DATE DATE
13 ,p_CREATED_BY NUMBER
14 ,p_PROGRAM_ID NUMBER
15 ,p_OBJECT_VERSION_NUMBER NUMBER
16 ,p_PARTY_CUST_ID NUMBER
17 ,p_PARTY_CLIENT_ID NUMBER
18 ,p_CUST_ACCOUNT_ID NUMBER
19 ,p_TRANSACTION_ID NUMBER
20 ,p_PAYMENT_SCHEDULE_ID NUMBER
21 ,p_AGING_BUCKET_LINE_ID NUMBER
22 ,p_CASE_ID NUMBER
23 ,p_RESOURCE_ID NUMBER
24 ,p_DUNN_YN VARCHAR2
25 ,p_AUTOASSIGN_YN VARCHAR2
26 ,p_STATUS VARCHAR2
27 ,p_CAMPAIGN_SCHED_ID NUMBER
28 ,p_ORG_ID NUMBER
29 ,p_REQUEST_ID NUMBER
30 ,p_PROGRAM_APPLICATION_ID NUMBER
31 ,p_PROGRAM_UPDATE_DATE DATE
32 ,p_SECURITY_GROUP_ID NUMBER
33 ,p_UNPAID_REASON_CODE VARCHAR2
34 --,p_STRATEGY_ID NUMBER
35 ) IS
36 CURSOR C IS SELECT ROWID FROM IEX_DELINQUENCIES_ALL
37 WHERE DELINQUENCY_ID = p_DELINQUENCY_ID;
38
39 BEGIN
40 INSERT INTO IEX_DELINQUENCIES_ALL
41 (
42 DELINQUENCY_ID
43 ,LAST_UPDATE_DATE
44 ,LAST_UPDATED_BY
45 ,LAST_UPDATE_LOGIN
46 ,CREATION_DATE
47 ,CREATED_BY
48 ,PROGRAM_ID
49 ,OBJECT_VERSION_NUMBER
50 ,PARTY_CUST_ID
51 ,PARTY_CLIENT_ID
52 ,CUST_ACCOUNT_ID
53 ,TRANSACTION_ID
54 ,PAYMENT_SCHEDULE_ID
55 ,AGING_BUCKET_LINE_ID
56 ,CASE_ID
57 ,RESOURCE_ID
58 ,DUNN_YN
59 ,AUTOASSIGN_YN
60 ,STATUS
61 ,CAMPAIGN_SCHED_ID
62 ,ORG_ID
63 ,REQUEST_ID
64 ,PROGRAM_APPLICATION_ID
65 ,PROGRAM_UPDATE_DATE
66 ,SECURITY_GROUP_ID
67 ,UNPAID_REASON_CODE
68 --,STRATEGY_ID
69 ) VALUES (
70 p_DELINQUENCY_ID
71 ,p_LAST_UPDATE_DATE
72 ,p_LAST_UPDATED_BY
73 ,p_LAST_UPDATE_LOGIN
74 ,p_CREATION_DATE
75 ,p_CREATED_BY
76 ,p_PROGRAM_ID
77 ,p_OBJECT_VERSION_NUMBER
78 ,p_PARTY_CUST_ID
79 ,p_PARTY_CLIENT_ID
80 ,p_CUST_ACCOUNT_ID
81 ,p_TRANSACTION_ID
82 ,p_PAYMENT_SCHEDULE_ID
83 ,p_AGING_BUCKET_LINE_ID
84 ,p_CASE_ID
85 ,p_RESOURCE_ID
86 ,p_DUNN_YN
87 ,p_AUTOASSIGN_YN
88 ,p_STATUS
89 ,p_CAMPAIGN_SCHED_ID
90 ,p_ORG_ID
91 ,p_REQUEST_ID
92 ,p_PROGRAM_APPLICATION_ID
93 ,p_PROGRAM_UPDATE_DATE
94 ,p_SECURITY_GROUP_ID
95 ,p_UNPAID_REASON_CODE
96 --,p_STRATEGY_ID
97 );
98
99 OPEN C;
100 FETCH C INTO x_rowid;
101 IF (C%NOTFOUND) THEN
102 CLOSE C;
103 RAISE NO_DATA_FOUND;
104 END IF;
105 CLOSE C;
106 END Insert_Row;
107
108 /* Update_Row procedure */
109 PROCEDURE Update_Row(x_rowid VARCHAR2
110 ,p_DELINQUENCY_ID NUMBER
111 ,p_LAST_UPDATE_DATE DATE
112 ,p_LAST_UPDATED_BY NUMBER
113 ,p_LAST_UPDATE_LOGIN NUMBER
114 ,p_CREATION_DATE DATE
115 ,p_CREATED_BY NUMBER
116 ,p_PROGRAM_ID NUMBER
117 ,p_OBJECT_VERSION_NUMBER NUMBER
118 ,p_PARTY_CUST_ID NUMBER
119 ,p_PARTY_CLIENT_ID NUMBER
120 ,p_CUST_ACCOUNT_ID NUMBER
121 ,p_TRANSACTION_ID NUMBER
122 ,p_PAYMENT_SCHEDULE_ID NUMBER
123 ,p_AGING_BUCKET_LINE_ID NUMBER
124 ,p_CASE_ID NUMBER
125 ,p_RESOURCE_ID NUMBER
126 ,p_DUNN_YN VARCHAR2
127 ,p_AUTOASSIGN_YN VARCHAR2
128 ,p_STATUS VARCHAR2
129 ,p_CAMPAIGN_SCHED_ID NUMBER
130 ,p_ORG_ID NUMBER
131 ,p_REQUEST_ID NUMBER
132 ,p_PROGRAM_APPLICATION_ID NUMBER
133 ,p_PROGRAM_UPDATE_DATE DATE
134 ,p_SECURITY_GROUP_ID NUMBER
135 ,p_UNPAID_REASON_CODE VARCHAR2
136 --,p_STRATEGY_ID NUMBER
137 ) IS
138 BEGIN
139 UPDATE IEX_DELINQUENCIES_ALL SET
140 DELINQUENCY_ID = p_DELINQUENCY_ID
141 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
142 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
143 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
144 ,CREATION_DATE = p_CREATION_DATE
145 ,CREATED_BY = p_CREATED_BY
146 ,PROGRAM_ID = p_PROGRAM_ID
147 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
148 ,PARTY_CUST_ID = p_PARTY_CUST_ID
149 ,PARTY_CLIENT_ID = p_PARTY_CLIENT_ID
150 ,CUST_ACCOUNT_ID = p_CUST_ACCOUNT_ID
151 ,TRANSACTION_ID = p_TRANSACTION_ID
152 ,PAYMENT_SCHEDULE_ID = p_PAYMENT_SCHEDULE_ID
153 ,AGING_BUCKET_LINE_ID = p_AGING_BUCKET_LINE_ID
154 ,CASE_ID = p_CASE_ID
155 ,RESOURCE_ID = p_RESOURCE_ID
156 ,DUNN_YN = p_DUNN_YN
157 ,AUTOASSIGN_YN = p_AUTOASSIGN_YN
158 ,STATUS = p_STATUS
159 ,CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID
160 ,ORG_ID = p_ORG_ID
161 ,REQUEST_ID = p_REQUEST_ID
162 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
163 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
164 ,SECURITY_GROUP_ID = p_SECURITY_GROUP_ID
165 ,UNPAID_REASON_CODE = p_UNPAID_REASON_CODE
166 --,STRATEGY_ID = p_STRATEGY_ID
167 WHERE rowid = x_rowid;
168
169 if (sql%notfound) then
170 raise no_data_found;
171 end if;
172 END Update_Row;
173
174 /* Delete_Row procedure */
175 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
176 BEGIN
177 DELETE FROM IEX_DELINQUENCIES_ALL
178 WHERE rowid = x_rowid;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183 END Delete_Row;
184
185 /* Lock_Row procedure */
186 PROCEDURE Lock_Row(x_rowid VARCHAR2
187 ,p_DELINQUENCY_ID NUMBER
188 ,p_LAST_UPDATE_DATE DATE
189 ,p_LAST_UPDATED_BY NUMBER
190 ,p_LAST_UPDATE_LOGIN NUMBER
191 ,p_CREATION_DATE DATE
192 ,p_CREATED_BY NUMBER
193 ,p_PROGRAM_ID NUMBER
194 ,p_OBJECT_VERSION_NUMBER NUMBER
195 ,p_PARTY_CUST_ID NUMBER
196 ,p_PARTY_CLIENT_ID NUMBER
197 ,p_CUST_ACCOUNT_ID NUMBER
198 ,p_TRANSACTION_ID NUMBER
199 ,p_PAYMENT_SCHEDULE_ID NUMBER
200 ,p_AGING_BUCKET_LINE_ID NUMBER
201 ,p_CASE_ID NUMBER
202 ,p_RESOURCE_ID NUMBER
203 ,p_DUNN_YN VARCHAR2
204 ,p_AUTOASSIGN_YN VARCHAR2
205 ,p_STATUS VARCHAR2
206 ,p_CAMPAIGN_SCHED_ID NUMBER
207 ,p_ORG_ID NUMBER
208 ,p_REQUEST_ID NUMBER
209 ,p_PROGRAM_APPLICATION_ID NUMBER
210 ,p_PROGRAM_UPDATE_DATE DATE
211 ,p_SECURITY_GROUP_ID NUMBER
212 ,p_UNPAID_REASON_CODE VARCHAR2
213 --,p_STRATEGY_ID NUMBER
214 ) IS
215 CURSOR C IS SELECT * FROM IEX_DELINQUENCIES_ALL
216 WHERE rowid = x_rowid
217 FOR UPDATE of DELINQUENCY_ID NOWAIT;
218 Recinfo C%ROWTYPE;
219 BEGIN
220 OPEN C;
221 FETCH C INTO Recinfo;
222 IF (C%NOTFOUND) THEN
223 CLOSE C;
224 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
225 APP_EXCEPTION.Raise_Exception;
226 END IF;
227 CLOSE C;
228
229 IF (
230 (Recinfo.DELINQUENCY_ID = p_DELINQUENCY_ID)
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_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_LOGIN = p_LAST_UPDATE_LOGIN)
238 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
239 AND (p_LAST_UPDATE_LOGIN IS NULL)))
240 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
241 OR ( (Recinfo.CREATION_DATE IS NULL)
242 AND (p_CREATION_DATE IS NULL)))
243 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
244 OR ( (Recinfo.CREATED_BY IS NULL)
245 AND (p_CREATED_BY IS NULL)))
246 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
247 OR ( (Recinfo.PROGRAM_ID IS NULL)
248 AND (p_PROGRAM_ID IS NULL)))
249 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
250 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
251 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
252 AND ( (Recinfo.PARTY_CUST_ID = p_PARTY_CUST_ID)
253 OR ( (Recinfo.PARTY_CUST_ID IS NULL)
254 AND (p_PARTY_CUST_ID IS NULL)))
255 AND ( (Recinfo.PARTY_CLIENT_ID = p_PARTY_CLIENT_ID)
256 OR ( (Recinfo.PARTY_CLIENT_ID IS NULL)
257 AND (p_PARTY_CLIENT_ID IS NULL)))
258 AND ( (Recinfo.CUST_ACCOUNT_ID = p_CUST_ACCOUNT_ID)
259 OR ( (Recinfo.CUST_ACCOUNT_ID IS NULL)
260 AND (p_CUST_ACCOUNT_ID IS NULL)))
261 AND ( (Recinfo.TRANSACTION_ID = p_TRANSACTION_ID)
262 OR ( (Recinfo.TRANSACTION_ID IS NULL)
263 AND (p_TRANSACTION_ID IS NULL)))
264 AND ( (Recinfo.PAYMENT_SCHEDULE_ID = p_PAYMENT_SCHEDULE_ID)
265 OR ( (Recinfo.PAYMENT_SCHEDULE_ID IS NULL)
266 AND (p_PAYMENT_SCHEDULE_ID IS NULL)))
267 AND ( (Recinfo.AGING_BUCKET_LINE_ID = p_AGING_BUCKET_LINE_ID)
268 OR ( (Recinfo.AGING_BUCKET_LINE_ID IS NULL)
269 AND (p_AGING_BUCKET_LINE_ID IS NULL)))
270 AND ( (Recinfo.CASE_ID = p_CASE_ID)
271 OR ( (Recinfo.CASE_ID IS NULL)
272 AND (p_CASE_ID IS NULL)))
273 AND ( (Recinfo.RESOURCE_ID = p_RESOURCE_ID)
274 OR ( (Recinfo.RESOURCE_ID IS NULL)
275 AND (p_RESOURCE_ID IS NULL)))
276 AND ( (Recinfo.DUNN_YN = p_DUNN_YN)
277 OR ( (Recinfo.DUNN_YN IS NULL)
278 AND (p_DUNN_YN IS NULL)))
279 AND ( (Recinfo.AUTOASSIGN_YN = p_AUTOASSIGN_YN)
280 OR ( (Recinfo.AUTOASSIGN_YN IS NULL)
281 AND (p_AUTOASSIGN_YN IS NULL)))
282 AND ( (Recinfo.STATUS = p_STATUS)
283 OR ( (Recinfo.STATUS IS NULL)
284 AND (p_STATUS IS NULL)))
285 AND ( (Recinfo.CAMPAIGN_SCHED_ID = p_CAMPAIGN_SCHED_ID)
286 OR ( (Recinfo.CAMPAIGN_SCHED_ID IS NULL)
287 AND (p_CAMPAIGN_SCHED_ID IS NULL)))
288 AND ( (Recinfo.ORG_ID = p_ORG_ID)
289 OR ( (Recinfo.ORG_ID IS NULL)
290 AND (p_ORG_ID IS NULL)))
291 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
292 OR ( (Recinfo.REQUEST_ID IS NULL)
293 AND (p_REQUEST_ID IS NULL)))
294 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
295 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
296 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
297 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
298 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
299 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
300 AND ( (Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
301 OR ( (Recinfo.SECURITY_GROUP_ID IS NULL)
302 AND (p_SECURITY_GROUP_ID IS NULL)))
303 AND ( (Recinfo.UNPAID_REASON_CODE = p_UNPAID_REASON_CODE)
304 OR ( (Recinfo.UNPAID_REASON_CODE IS NULL)
305 AND (p_UNPAID_REASON_CODE IS NULL)))
306 --AND ( (Recinfo.STRATEGY_ID = p_STRATEGY_ID)
307 --OR ( (Recinfo.STRATEGY_ID IS NULL)
308 --AND (p_STRATEGY_ID IS NULL)))
309 ) THEN
310 return;
311 ELSE
312 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
313 APP_EXCEPTION.Raise_Exception;
314 END IF;
315 END Lock_Row;
316 END;
317
[Home] [Help]