[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_BORDEROS_PKG
Source
1 PACKAGE BODY JL_BR_AR_BORDEROS_PKG as
2 /* $Header: jlbrrbdb.pls 120.5 2003/09/18 20:24:26 vsidhart ship $ */
3
4 PROCEDURE Insert_Row ( X_Rowid IN OUT NOCOPY VARCHAR2,
5
6 X_BORDERO_ID NUMBER,
7 X_SELECTION_CONTROL_ID NUMBER,
8 X_BANK_ACCT_USE_ID NUMBER,
9 X_BORDERO_STATUS VARCHAR2,
10 X_SEQUENTIAL_NUMBER_GENERATION NUMBER,
11 X_BORDERO_TYPE VARCHAR2,
12 X_TOTAL_COUNT NUMBER,
13 X_TOTAL_AMOUNT NUMBER,
14 X_SELECTION_DATE DATE,
15 X_REMITTANCE_DATE DATE,
16 X_REFUSED_DATE DATE,
17 X_CANCELLATION_DATE DATE,
18 X_COLLECTION_DATE DATE,
19 X_WRITE_OFF_DATE DATE,
20 X_DATE_IN_RECEIPT DATE,
21 X_RECEIVED_DATE DATE,
22 X_OUTPUT_PROGRAM_ID NUMBER,
23 X_SELECT_ACCOUNT_ID NUMBER,
24 X_OUTPUT_FORMAT VARCHAR2,
25 X_LAST_UPDATE_DATE DATE,
26 X_LAST_UPDATED_BY NUMBER,
27 X_CREATION_DATE DATE,
28 X_CREATED_BY NUMBER,
29 X_LAST_UPDATE_LOGIN NUMBER,
30
31 X_calling_sequence VARCHAR2,
32 X_ORG_ID NUMBER
33 ) IS
34 CURSOR C IS SELECT rowid FROM JL_BR_AR_BORDEROS
35 WHERE bordero_id = X_bordero_id;
36 current_calling_sequence VARCHAR2(2000);
37 debug_info VARCHAR2(100);
38
39 BEGIN
40 -- Update the calling sequence
41 --
42 current_calling_sequence := 'JL_BR_AR_BORDEROS_PKG.INSERT_ROW<-' ||
43 X_calling_sequence;
44
45 debug_info := 'Insert into JL_BR_AR_BORDEROS';
46 INSERT INTO JL_BR_AR_BORDEROS
47 (
48 BORDERO_ID,
49 SELECTION_CONTROL_ID,
50 BANK_ACCT_USE_ID,
51 BORDERO_STATUS,
52 SEQUENTIAL_NUMBER_GENERATION,
53 BORDERO_TYPE,
54 TOTAL_COUNT,
55 TOTAL_AMOUNT,
56 SELECTION_DATE,
57 REMITTANCE_DATE,
58 REFUSED_DATE,
59 CANCELLATION_DATE,
60 COLLECTION_DATE,
61 WRITE_OFF_DATE,
62 DATE_IN_RECEIPT,
63 RECEIVED_DATE,
64 OUTPUT_PROGRAM_ID,
65 SELECT_ACCOUNT_ID,
66 OUTPUT_FORMAT,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 CREATION_DATE,
70 CREATED_BY,
71 LAST_UPDATE_LOGIN,
72 ORG_ID
73 )
74 VALUES (
75 X_BORDERO_ID,
76 X_SELECTION_CONTROL_ID,
77 X_BANK_ACCT_USE_ID,
78 X_BORDERO_STATUS,
79 X_SEQUENTIAL_NUMBER_GENERATION,
80 X_BORDERO_TYPE,
81 X_TOTAL_COUNT,
82 X_TOTAL_AMOUNT,
83 X_SELECTION_DATE,
84 X_REMITTANCE_DATE,
85 X_REFUSED_DATE,
86 X_CANCELLATION_DATE,
87 X_COLLECTION_DATE,
88 X_WRITE_OFF_DATE,
89 X_DATE_IN_RECEIPT,
90 X_RECEIVED_DATE,
91 X_OUTPUT_PROGRAM_ID,
92 X_SELECT_ACCOUNT_ID,
93 X_OUTPUT_FORMAT,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_CREATION_DATE,
97 X_CREATED_BY,
98 X_LAST_UPDATE_LOGIN,
99 X_ORG_ID
100 );
101
102 debug_info := 'Open cursor C';
103 OPEN C;
104 debug_info := 'Fetch cursor C';
105 FETCH C INTO X_Rowid;
106 if (C%NOTFOUND) then
107 debug_info := 'Close cursor C - DATA NOTFOUND';
108 CLOSE C;
109 Raise NO_DATA_FOUND;
110 end if;
111 debug_info := 'Close cursor C';
112 CLOSE C;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 IF (SQLCODE <> -20001) THEN
117 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
118 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
119 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
120 FND_MESSAGE.SET_TOKEN('PARAMETERS','bordero_id = ' ||
121 X_bordero_id );
122 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
123 END IF;
124 APP_EXCEPTION.RAISE_EXCEPTION;
125
126 END Insert_Row;
127
128
129 PROCEDURE Lock_Row ( X_Rowid VARCHAR2,
130
131 X_BORDERO_ID NUMBER,
132 X_SELECTION_CONTROL_ID NUMBER,
133 X_BANK_ACCT_USE_ID NUMBER,
134 X_BORDERO_STATUS VARCHAR2,
135 X_SEQUENTIAL_NUMBER_GENERATION NUMBER,
136 X_BORDERO_TYPE VARCHAR2,
137 X_TOTAL_COUNT NUMBER,
138 X_TOTAL_AMOUNT NUMBER,
139 X_SELECTION_DATE DATE,
140 X_REMITTANCE_DATE DATE,
141 X_REFUSED_DATE DATE,
142 X_CANCELLATION_DATE DATE,
143 X_COLLECTION_DATE DATE,
144 X_WRITE_OFF_DATE DATE,
145 X_DATE_IN_RECEIPT DATE,
146 X_RECEIVED_DATE DATE,
147 X_OUTPUT_PROGRAM_ID NUMBER,
148 X_SELECT_ACCOUNT_ID NUMBER,
149 X_OUTPUT_FORMAT VARCHAR2,
150 X_LAST_UPDATE_DATE DATE,
151 X_LAST_UPDATED_BY NUMBER,
152 X_CREATION_DATE DATE,
153 X_CREATED_BY NUMBER,
154 X_LAST_UPDATE_LOGIN NUMBER,
155
156 X_calling_sequence VARCHAR2
157 ) IS
158 CURSOR C IS
159 SELECT *
160 FROM JL_BR_AR_BORDEROS
161 WHERE rowid = X_Rowid
162 FOR UPDATE of bordero_id NOWAIT;
163 Recinfo C%ROWTYPE;
164
165 current_calling_sequence VARCHAR2(2000);
166 debug_info VARCHAR2(100);
167
168 BEGIN
169 -- Update the calling sequence
170 --
171 current_calling_sequence := 'JL_BR_AR_BORDEROS_PKG.LOCK_ROW<-' ||
172 X_calling_sequence;
173 debug_info := 'Open cursor C';
174 OPEN C;
175 debug_info := 'Fetch cursor C';
176 FETCH C INTO Recinfo;
177 if (C%NOTFOUND) then
178 debug_info := 'Close cursor C - DATA NOTFOUND';
179 CLOSE C;
180 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
181 APP_EXCEPTION.Raise_Exception;
182 end if;
183 debug_info := 'Close cursor C';
184 CLOSE C;
185 if (
186 (Recinfo.bordero_id = X_bordero_id)
187 AND (Recinfo.selection_control_id = X_selection_control_id)
188 AND (Recinfo.bank_acct_use_id = X_bank_acct_use_id)
189 AND (Recinfo.bordero_status = X_bordero_status)
190 AND ( (Recinfo.SEQUENTIAL_NUMBER_GENERATION = X_SEQUENTIAL_NUMBER_GENERATION)
191 OR ( (Recinfo.SEQUENTIAL_NUMBER_GENERATION IS NULL)
192 AND (X_SEQUENTIAL_NUMBER_GENERATION IS NULL)))
193 AND ( (Recinfo.BORDERO_TYPE = X_BORDERO_TYPE)
194 OR ( (Recinfo.BORDERO_TYPE IS NULL)
195 AND (X_BORDERO_TYPE IS NULL)))
196 AND ( (Recinfo.TOTAL_COUNT = X_TOTAL_COUNT)
197 OR ( (Recinfo.TOTAL_COUNT IS NULL)
198 AND (X_TOTAL_COUNT IS NULL)))
199 AND ( (Recinfo.TOTAL_AMOUNT = X_TOTAL_AMOUNT)
200 OR ( (Recinfo.TOTAL_AMOUNT IS NULL)
201 AND (X_TOTAL_AMOUNT IS NULL)))
202 AND ( (Recinfo.SELECTION_DATE = X_SELECTION_DATE)
203 OR ( (Recinfo.SELECTION_DATE IS NULL)
204 AND (X_SELECTION_DATE IS NULL)))
205 AND ( (Recinfo.REMITTANCE_DATE = X_REMITTANCE_DATE)
206 OR ( (Recinfo.REMITTANCE_DATE IS NULL)
207 AND (X_REMITTANCE_DATE IS NULL)))
208 AND ( (Recinfo.REFUSED_DATE = X_REFUSED_DATE)
209 OR ( (Recinfo.REFUSED_DATE IS NULL)
210 AND (X_REFUSED_DATE IS NULL)))
211 AND ( (Recinfo.CANCELLATION_DATE = X_CANCELLATION_DATE)
212 OR ( (Recinfo.CANCELLATION_DATE IS NULL)
213 AND (X_CANCELLATION_DATE IS NULL)))
214 AND ( (Recinfo.COLLECTION_DATE = X_COLLECTION_DATE)
215 OR ( (Recinfo.COLLECTION_DATE IS NULL)
216 AND (X_COLLECTION_DATE IS NULL)))
217 AND ( (Recinfo.WRITE_OFF_DATE = X_WRITE_OFF_DATE)
218 OR ( (Recinfo.WRITE_OFF_DATE IS NULL)
219 AND (X_WRITE_OFF_DATE IS NULL)))
220 AND ( (Recinfo.DATE_IN_RECEIPT = X_DATE_IN_RECEIPT)
221 OR ( (Recinfo.DATE_IN_RECEIPT IS NULL)
222 AND (X_DATE_IN_RECEIPT IS NULL)))
223 AND ( (Recinfo.RECEIVED_DATE = X_RECEIVED_DATE)
224 OR ( (Recinfo.RECEIVED_DATE IS NULL)
225 AND (X_RECEIVED_DATE IS NULL)))
226 AND ( (Recinfo.OUTPUT_PROGRAM_ID = X_OUTPUT_PROGRAM_ID)
227 OR ( (Recinfo.OUTPUT_PROGRAM_ID IS NULL)
228 AND (X_OUTPUT_PROGRAM_ID IS NULL)))
229 AND ( (Recinfo.SELECT_ACCOUNT_ID = X_SELECT_ACCOUNT_ID)
230 OR ( (Recinfo.SELECT_ACCOUNT_ID IS NULL)
231 AND (X_SELECT_ACCOUNT_ID IS NULL)))
232 AND ( (Recinfo.OUTPUT_FORMAT = X_OUTPUT_FORMAT)
233 OR ( (Recinfo.OUTPUT_FORMAT IS NULL)
234 AND (X_OUTPUT_FORMAT IS NULL)))
235 ) then
236 return;
237 else
238 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
239 APP_EXCEPTION.Raise_Exception;
240 end if;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 IF (SQLCODE <> -20001) THEN
245 IF (SQLCODE = -54) THEN
246 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
247 ELSE
248 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
249 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
250 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
251 FND_MESSAGE.SET_TOKEN('PARAMETERS','bordero_id = ' ||
252 X_bordero_id );
253 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
254 END IF;
255 END IF;
256 APP_EXCEPTION.RAISE_EXCEPTION;
257
258 END Lock_Row;
259
260
261
262 PROCEDURE Update_Row ( X_Rowid VARCHAR2,
263
264 X_BORDERO_ID NUMBER,
265 X_SELECTION_CONTROL_ID NUMBER,
266 X_BANK_ACCT_USE_ID NUMBER,
267 X_BORDERO_STATUS VARCHAR2,
268 X_SEQUENTIAL_NUMBER_GENERATION NUMBER,
269 X_BORDERO_TYPE VARCHAR2,
270 X_TOTAL_COUNT NUMBER,
271 X_TOTAL_AMOUNT NUMBER,
272 X_SELECTION_DATE DATE,
273 X_REMITTANCE_DATE DATE,
274 X_REFUSED_DATE DATE,
275 X_CANCELLATION_DATE DATE,
276 X_COLLECTION_DATE DATE,
277 X_WRITE_OFF_DATE DATE,
278 X_DATE_IN_RECEIPT DATE,
279 X_RECEIVED_DATE DATE,
280 X_OUTPUT_PROGRAM_ID NUMBER,
281 X_SELECT_ACCOUNT_ID NUMBER,
282 X_OUTPUT_FORMAT VARCHAR2,
283 X_LAST_UPDATE_DATE DATE,
284 X_LAST_UPDATED_BY NUMBER,
285 X_CREATION_DATE DATE,
286 X_CREATED_BY NUMBER,
287 X_LAST_UPDATE_LOGIN NUMBER,
288
289 X_calling_sequence VARCHAR2
290 ) IS
291 current_calling_sequence VARCHAR2(2000);
292 debug_info VARCHAR2(100);
293 BEGIN
294 -- Update the calling sequence
295 --
296 current_calling_sequence := 'JL_BR_AR_BORDEROS_PKG.UPDATE_ROW<-' ||
297 X_calling_sequence;
298 debug_info := 'Update JL_BR_AR_BORDEROS';
299 UPDATE JL_BR_AR_BORDEROS
300 SET
301 BORDERO_ID = X_BORDERO_ID ,
302 SELECTION_CONTROL_ID = X_SELECTION_CONTROL_ID ,
303 BANK_ACCT_USE_ID = X_BANK_ACCT_USE_ID ,
304 BORDERO_STATUS = X_BORDERO_STATUS ,
305 SEQUENTIAL_NUMBER_GENERATION = X_SEQUENTIAL_NUMBER_GENERATION ,
306 BORDERO_TYPE = X_BORDERO_TYPE ,
307 TOTAL_COUNT = X_TOTAL_COUNT ,
308 TOTAL_AMOUNT = X_TOTAL_AMOUNT ,
309 SELECTION_DATE = X_SELECTION_DATE ,
310 REMITTANCE_DATE = X_REMITTANCE_DATE ,
311 REFUSED_DATE = X_REFUSED_DATE ,
312 CANCELLATION_DATE = X_CANCELLATION_DATE ,
313 COLLECTION_DATE = X_COLLECTION_DATE ,
314 WRITE_OFF_DATE = X_WRITE_OFF_DATE ,
315 DATE_IN_RECEIPT = X_DATE_IN_RECEIPT ,
316 RECEIVED_DATE = X_RECEIVED_DATE ,
317 OUTPUT_PROGRAM_ID = X_OUTPUT_PROGRAM_ID ,
318 SELECT_ACCOUNT_ID = X_SELECT_ACCOUNT_ID ,
319 OUTPUT_FORMAT = X_OUTPUT_FORMAT ,
320 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
321 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
322 CREATION_DATE = X_CREATION_DATE ,
323 CREATED_BY = X_CREATED_BY ,
324 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
325 WHERE rowid = X_Rowid;
326
327 if (SQL%NOTFOUND) then
328 Raise NO_DATA_FOUND;
329 end if;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 IF (SQLCODE <> -20001) THEN
334 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
335 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
336 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
337 FND_MESSAGE.SET_TOKEN('PARAMETERS','bordero_id = ' ||
338 X_bordero_id );
339 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
340 END IF;
341 APP_EXCEPTION.RAISE_EXCEPTION;
342
343 END Update_Row;
344
345 PROCEDURE Delete_Row (X_Rowid VARCHAR2,
346 X_calling_sequence IN VARCHAR2
347 ) IS
348 current_calling_sequence VARCHAR2(2000);
349 debug_info VARCHAR2(100);
350 BEGIN
351 -- Update the calling sequence
352 --
353 current_calling_sequence := 'JL_BR_AR_BORDEROS_PKG.DELETE_ROW<-' ||
354 X_calling_sequence;
355 debug_info := 'Delete from JL_BR_AR_BORDEROS';
356 DELETE FROM JL_BR_AR_BORDEROS
357 WHERE rowid = X_Rowid;
358
359 if (SQL%NOTFOUND) then
360 Raise NO_DATA_FOUND;
361 end if;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 IF (SQLCODE <> -20001) THEN
366 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
367 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
368 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
369 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
370 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
371 END IF;
372 APP_EXCEPTION.RAISE_EXCEPTION;
373
374 END Delete_Row;
375
376
377 END JL_BR_AR_BORDEROS_PKG;