[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_BANK_RETURNS_PKG
Source
1 PACKAGE BODY JL_BR_AR_BANK_RETURNS_PKG as
2 /* $Header: jlbrremb.pls 120.4 2003/09/15 21:51:54 vsidhart ship $ */
3
4 PROCEDURE Insert_Row(X_rowid IN OUT NOCOPY VARCHAR2,
5
6 X_RETURN_ID NUMBER,
7 X_BANK_OCCURRENCE_CODE NUMBER,
8 X_OCCURRENCE_DATE DATE,
9 X_FILE_CONTROL VARCHAR2,
10 X_ENTRY_SEQUENTIAL_NUMBER NUMBER,
11 X_GENERATION_DATE DATE,
12 X_PROCESSING_DATE DATE,
13 X_DOCUMENT_ID NUMBER,
14 --X_BANK_NUMBER VARCHAR2,
15 X_BANK_PARTY_ID NUMBER,
16 X_BATCH_SOURCE_ID NUMBER,
17 X_OUR_NUMBER VARCHAR2,
18 X_TRADE_NOTE_NUMBER VARCHAR2,
19 X_DUE_DATE DATE,
20 X_TRADE_NOTE_AMOUNT NUMBER,
21 X_COLLECTOR_BANK_PARTY_ID NUMBER,
22 X_COLLECTOR_BRANCH_PARTY_ID NUMBER,
23 X_BANK_CHARGE_AMOUNT NUMBER,
24 X_ABATEMENT_AMOUNT NUMBER,
25 X_DISCOUNT_AMOUNT NUMBER,
26 X_CREDIT_AMOUNT NUMBER,
27 X_INTEREST_AMOUNT_RECEIVED NUMBER,
28 X_CUSTOMER_ID NUMBER,
29 X_RETURN_INFO VARCHAR2,
30 X_BANK_USE VARCHAR2,
31 X_COMPANY_USE NUMBER,
32 X_LAST_UPDATE_DATE DATE,
33 X_LAST_UPDATED_BY NUMBER,
34 X_CREATION_DATE DATE,
35 X_CREATED_BY NUMBER,
36 X_LAST_UPDATE_LOGIN NUMBER,
37 X_calling_sequence IN VARCHAR2,
38 X_ORG_ID NUMBER
39 ) IS
40 CURSOR C IS SELECT rowid
41 FROM jl_br_ar_bank_returns
42 WHERE return_id = X_return_id;
43
44 current_calling_sequence VARCHAR2(2000);
45 debug_info VARCHAR2(100);
46
47 BEGIN
48 -- Update the calling sequence
49 --
50 current_calling_sequence := 'JL_BR_AR_BANK_RETURNS_PKG.INSERT_ROW<-' ||
51 X_calling_sequence;
52 --
53 debug_info := 'Insert into JL_BR_AR_BANK_RETURNS';
54 insert into jl_br_ar_bank_returns(
55 RETURN_ID,
56 BANK_OCCURRENCE_CODE,
57 OCCURRENCE_DATE,
58 FILE_CONTROL,
59 ENTRY_SEQUENTIAL_NUMBER,
60 GENERATION_DATE,
61 PROCESSING_DATE,
62 DOCUMENT_ID,
63 --BANK_NUMBER,
64 BANK_PARTY_ID,
65 BATCH_SOURCE_ID,
66 OUR_NUMBER,
67 TRADE_NOTE_NUMBER,
68 DUE_DATE,
69 TRADE_NOTE_AMOUNT,
70 COLLECTOR_BANK_PARTY_ID,
71 COLLECTOR_BRANCH_PARTY_ID,
72 BANK_CHARGE_AMOUNT,
73 ABATEMENT_AMOUNT,
74 DISCOUNT_AMOUNT,
75 CREDIT_AMOUNT,
76 INTEREST_AMOUNT_RECEIVED,
77 CUSTOMER_ID,
78 RETURN_INFO,
79 BANK_USE,
80 COMPANY_USE,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_LOGIN,
86 ORG_ID )
87 VALUES ( X_RETURN_ID,
88 X_BANK_OCCURRENCE_CODE,
89 X_OCCURRENCE_DATE,
90 X_FILE_CONTROL,
91 X_ENTRY_SEQUENTIAL_NUMBER,
92 X_GENERATION_DATE,
93 X_PROCESSING_DATE,
94 X_DOCUMENT_ID,
95 --X_BANK_NUMBER,
96 X_BANK_PARTY_ID,
97 X_BATCH_SOURCE_ID,
98 X_OUR_NUMBER,
99 X_TRADE_NOTE_NUMBER,
100 X_DUE_DATE,
101 X_TRADE_NOTE_AMOUNT,
102 X_COLLECTOR_BANK_PARTY_ID,
103 X_COLLECTOR_BRANCH_PARTY_ID,
104 X_BANK_CHARGE_AMOUNT,
105 X_ABATEMENT_AMOUNT,
106 X_DISCOUNT_AMOUNT,
107 X_CREDIT_AMOUNT,
108 X_INTEREST_AMOUNT_RECEIVED,
109 X_CUSTOMER_ID,
110 X_RETURN_INFO,
111 X_BANK_USE,
112 X_COMPANY_USE,
113 X_LAST_UPDATE_DATE,
114 X_LAST_UPDATED_BY,
115 X_CREATION_DATE,
116 X_CREATED_BY,
117 X_LAST_UPDATE_LOGIN,
118 X_ORG_ID
119 );
120
121 debug_info := 'Open cursor C';
122 OPEN C;
123 debug_info := 'Fetch cursor C';
124 FETCH C INTO X_rowid;
125 if (C%NOTFOUND) then
126 debug_info := 'Close cursor C - DATA NOTFOUND';
127 CLOSE C;
128 Raise NO_DATA_FOUND;
129 end if;
130 debug_info := 'Close cursor C';
131 CLOSE C;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 IF (SQLCODE <> -20001) THEN
136 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
137 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
138 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
139 FND_MESSAGE.SET_TOKEN('PARAMETERS',
140 'return_id = ' || X_return_id);
141 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
142 END IF;
143 APP_EXCEPTION.RAISE_EXCEPTION;
144
145 END Insert_Row;
146
147 PROCEDURE Lock_Row( X_rowid VARCHAR2,
148
149 X_RETURN_ID NUMBER,
150 X_BANK_OCCURRENCE_CODE NUMBER,
151 X_OCCURRENCE_DATE DATE,
152 X_FILE_CONTROL VARCHAR2,
153 X_ENTRY_SEQUENTIAL_NUMBER NUMBER,
154 X_GENERATION_DATE DATE,
155 X_PROCESSING_DATE DATE,
156 X_DOCUMENT_ID NUMBER,
157 --X_BANK_NUMBER VARCHAR2,
158 X_BANK_PARTY_ID NUMBER,
159 X_BATCH_SOURCE_ID NUMBER,
160 X_OUR_NUMBER VARCHAR2,
161 X_TRADE_NOTE_NUMBER VARCHAR2,
162 X_DUE_DATE DATE,
163 X_TRADE_NOTE_AMOUNT NUMBER,
164 X_COLLECTOR_BANK_PARTY_ID NUMBER,
165 X_COLLECTOR_BRANCH_PARTY_ID NUMBER,
166 X_BANK_CHARGE_AMOUNT NUMBER,
167 X_ABATEMENT_AMOUNT NUMBER,
168 X_DISCOUNT_AMOUNT NUMBER,
169 X_CREDIT_AMOUNT NUMBER,
170 X_INTEREST_AMOUNT_RECEIVED NUMBER,
171 X_CUSTOMER_ID NUMBER,
172 X_RETURN_INFO VARCHAR2,
173 X_BANK_USE VARCHAR2,
174 X_COMPANY_USE NUMBER,
175 X_LAST_UPDATE_DATE DATE,
176 X_LAST_UPDATED_BY NUMBER,
177 X_CREATION_DATE DATE,
178 X_CREATED_BY NUMBER,
179 X_LAST_UPDATE_LOGIN NUMBER,
180
181 X_calling_sequence IN VARCHAR2
182 ) IS
183 CURSOR C IS SELECT *
184 FROM jl_br_ar_bank_returns
185 WHERE return_id = X_return_id
186 FOR UPDATE of return_id
187 NOWAIT;
188
189 Recinfo C%ROWTYPE;
190
191 current_calling_sequence VARCHAR2(2000);
192 debug_info VARCHAR2(100);
193
194 BEGIN
195 -- Update the calling sequence
196 --
197 current_calling_sequence := 'JL_BR_AR_BANK_RETURNS_PKG.LOCK_ROW<-' ||
198 X_calling_sequence;
199 debug_info := 'Open cursor C';
200 OPEN C;
201 debug_info := 'Fetch cursor C';
202 FETCH C INTO Recinfo;
203 IF (C%NOTFOUND)
204 THEN
205 debug_info := 'Close cursor C - DATA NOTFOUND';
206 CLOSE C;
207 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
208 APP_EXCEPTION.Raise_Exception;
209 END IF;
210 debug_info := 'Close cursor C';
211 CLOSE C;
212 IF (Recinfo.return_id = X_return_id AND
213 Recinfo.bank_occurrence_code = X_bank_occurrence_code AND
214 Recinfo.occurrence_date = X_occurrence_date AND
215 Recinfo.file_control = X_file_control AND
216 Recinfo.entry_sequential_number = X_entry_sequential_number AND
217 Recinfo.generation_date = X_generation_date AND
218 Recinfo.processing_date = X_processing_date AND
219 Recinfo.document_id = X_document_id AND
220 Recinfo.trade_note_number = X_trade_note_number AND
221 Recinfo.last_updated_by = X_last_updated_by AND
222 Recinfo.last_update_date = X_last_update_date AND
223 --(Recinfo.bank_number = X_bank_number OR
224 -- X_bank_number IS NULL) AND
225 (Recinfo.bank_party_id = X_bank_party_id OR
226 X_bank_party_id IS NULL) AND
227 (Recinfo.batch_source_id = X_batch_source_id OR
228 X_batch_source_id IS NULL) AND
229 (Recinfo.our_number = X_our_number OR
230 X_our_number IS NULL) AND
231 (Recinfo.due_date = X_due_date OR
232 X_due_date IS NULL) AND
233 (Recinfo.trade_note_amount = X_trade_note_amount OR
234 X_trade_note_amount IS NULL) AND
235 (Recinfo.COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID OR
236 X_COLLECTOR_BANK_PARTY_ID IS NULL) AND
237 (Recinfo.COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID OR
238 X_COLLECTOR_BRANCH_PARTY_ID IS NULL) AND
239 (Recinfo.bank_charge_amount = X_bank_charge_amount OR
240 X_bank_charge_amount IS NULL) AND
241 (Recinfo.abatement_amount = X_abatement_amount OR
242 X_abatement_amount IS NULL) AND
243 (Recinfo.discount_amount = X_discount_amount OR
244 X_discount_amount IS NULL) AND
245 (Recinfo.credit_amount = X_credit_amount OR
246 X_credit_amount IS NULL) AND
247 (Recinfo.interest_amount_received = X_interest_amount_received OR
248 X_interest_amount_received IS NULL) AND
249 (Recinfo.customer_id = X_customer_id OR
250 X_customer_id IS NULL) AND
251 (Recinfo.return_info = X_return_info OR
252 X_return_info IS NULL) AND
253 (Recinfo.bank_use = X_bank_use OR
254 X_bank_use IS NULL) AND
255 (Recinfo.company_use = X_company_use OR
256 X_company_use IS NULL) AND
257 (Recinfo.last_update_login = X_last_update_login OR
258 X_last_update_login IS NULL) AND
259 (Recinfo.creation_date = X_creation_date OR
260 X_creation_date IS NULL) AND
261 (Recinfo.created_by = X_created_by OR
262 X_created_by IS NULL)
263 )
264 THEN
265 return;
266 ELSE
267 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
268 APP_EXCEPTION.Raise_Exception;
269 END IF;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF (SQLCODE <> -20001) THEN
274 IF (SQLCODE = -54) THEN
275 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
276 ELSE
277 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
278 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
279 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
280 FND_MESSAGE.SET_TOKEN('PARAMETERS',
281 'return_id = ' || X_return_id);
282 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
283 END IF;
284 END IF;
285 APP_EXCEPTION.RAISE_EXCEPTION;
286 END Lock_Row;
287
288 PROCEDURE Update_Row(X_rowid VARCHAR2,
289
290 X_RETURN_ID NUMBER,
291 X_BANK_OCCURRENCE_CODE NUMBER,
292 X_OCCURRENCE_DATE DATE,
293 X_FILE_CONTROL VARCHAR2,
294 X_ENTRY_SEQUENTIAL_NUMBER NUMBER,
295 X_GENERATION_DATE DATE,
296 X_PROCESSING_DATE DATE,
297 X_DOCUMENT_ID NUMBER,
298 --X_BANK_NUMBER VARCHAR2,
299 X_BANK_PARTY_ID NUMBER,
300 X_BATCH_SOURCE_ID NUMBER,
301 X_OUR_NUMBER VARCHAR2,
302 X_TRADE_NOTE_NUMBER VARCHAR2,
303 X_DUE_DATE DATE,
304 X_TRADE_NOTE_AMOUNT NUMBER,
308 X_ABATEMENT_AMOUNT NUMBER,
305 X_COLLECTOR_BANK_PARTY_ID NUMBER,
306 X_COLLECTOR_BRANCH_PARTY_ID NUMBER,
307 X_BANK_CHARGE_AMOUNT NUMBER,
309 X_DISCOUNT_AMOUNT NUMBER,
310 X_CREDIT_AMOUNT NUMBER,
311 X_INTEREST_AMOUNT_RECEIVED NUMBER,
312 X_CUSTOMER_ID NUMBER,
313 X_RETURN_INFO VARCHAR2,
314 X_BANK_USE VARCHAR2,
315 X_COMPANY_USE NUMBER,
316 X_LAST_UPDATE_DATE DATE,
317 X_LAST_UPDATED_BY NUMBER,
318 X_CREATION_DATE DATE,
319 X_CREATED_BY NUMBER,
320 X_LAST_UPDATE_LOGIN NUMBER,
321
322 X_calling_sequence IN VARCHAR2
323 ) IS
324
325 BEGIN
326 UPDATE jl_br_ar_bank_returns
327 SET RETURN_ID = X_RETURN_ID,
328 BANK_OCCURRENCE_CODE = X_BANK_OCCURRENCE_CODE,
329 OCCURRENCE_DATE = X_OCCURRENCE_DATE,
330 FILE_CONTROL = X_FILE_CONTROL,
331 ENTRY_SEQUENTIAL_NUMBER = X_ENTRY_SEQUENTIAL_NUMBER,
332 GENERATION_DATE = X_GENERATION_DATE,
333 PROCESSING_DATE = X_PROCESSING_DATE,
334 DOCUMENT_ID = X_DOCUMENT_ID,
335 --BANK_NUMBER = X_BANK_NUMBER,
336 BANK_PARTY_ID = X_BANK_PARTY_ID,
337 BATCH_SOURCE_ID = X_BATCH_SOURCE_ID,
338 OUR_NUMBER = X_OUR_NUMBER,
339 TRADE_NOTE_NUMBER = X_TRADE_NOTE_NUMBER,
340 DUE_DATE = X_DUE_DATE,
341 TRADE_NOTE_AMOUNT = X_TRADE_NOTE_AMOUNT,
342 COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID,
343 COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID,
344 BANK_CHARGE_AMOUNT = X_BANK_CHARGE_AMOUNT,
345 ABATEMENT_AMOUNT = X_ABATEMENT_AMOUNT,
346 DISCOUNT_AMOUNT = X_DISCOUNT_AMOUNT,
347 CREDIT_AMOUNT = X_CREDIT_AMOUNT,
348 INTEREST_AMOUNT_RECEIVED = X_INTEREST_AMOUNT_RECEIVED,
349 CUSTOMER_ID = X_CUSTOMER_ID,
350 RETURN_INFO = X_RETURN_INFO,
351 BANK_USE = X_BANK_USE,
352 COMPANY_USE = X_COMPANY_USE,
353 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
354 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
355 CREATION_DATE = X_CREATION_DATE,
356 CREATED_BY = X_CREATED_BY,
357 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
358 WHERE rowid = X_rowid;
359
360 IF (SQL%NOTFOUND)
361 THEN
362 raise NO_DATA_FOUND;
363 END IF;
364 END Update_Row;
365
366 PROCEDURE Delete_Row( X_rowid VARCHAR2
367 ) IS
368 BEGIN
369 DELETE
370 FROM jl_br_ar_bank_returns
371 WHERE rowid = X_rowid;
372
373 IF (SQL%NOTFOUND)
374 THEN
375 raise NO_DATA_FOUND;
376 END IF;
377 END Delete_Row;
378
379 END JL_BR_AR_BANK_RETURNS_PKG;