[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_RET_INTERFACE_EXT_PKG
Source
1 PACKAGE BODY JL_BR_AR_RET_INTERFACE_EXT_PKG AS
2 /*$Header: jlbrrcbb.pls 120.2 2005/02/23 23:28:15 vsidhart noship $*/
3 PROCEDURE Update_Row(X_Rowid VARCHAR2,
4 X_FILE_CONTROL VARCHAR2,
5 X_ENTRY_SEQUENTIAL_NUMBER NUMBER,
6 X_COMPANY_CODE NUMBER,
7 X_COMPANY_NAME VARCHAR2,
8 X_GENERATION_DATE DATE,
9 X_REMITTANCE_CODE NUMBER,
10 --X_BANK_NUMBER VARCHAR2,
11 X_BANK_PARTY_ID NUMBER ,
12 X_BANK_OCCURRENCE_CODE NUMBER,
13 X_OCCURRENCE_DATE DATE,
14 X_PROCESSING_DATE DATE,
15 X_LAST_VALIDATION_DATE DATE,
16 X_INSCRIPTION_NUMBER NUMBER,
17 X_COMPANY_USE NUMBER,
18 X_OUR_NUMBER VARCHAR2,
19 X_PORTFOLIO_CODE NUMBER,
20 X_YOUR_NUMBER VARCHAR2,
21 X_DUE_DATE DATE,
22 X_TRADE_NOTE_AMOUNT NUMBER,
23 X_COLLECTOR_BANK_PARTY_ID NUMBER,
24 X_COLLECTOR_BRANCH_PARTY_ID NUMBER,
25 X_TRADE_NOTE_TYPE VARCHAR2,
26 X_BANK_CHARGE_AMOUNT NUMBER,
27 X_ABATEMENT_AMOUNT NUMBER,
28 X_DISCOUNT_AMOUNT NUMBER,
29 X_CREDIT_AMOUNT NUMBER,
30 X_INTEREST_AMOUNT_RECEIVED NUMBER,
31 X_CUSTOMER_NAME VARCHAR2,
32 X_RETURN_INFO VARCHAR2,
33 X_BANK_USE VARCHAR2,
34 X_ERROR_CODE VARCHAR2,
35 X_CANCELLATION_CODE VARCHAR2,
36 X_ORG_ID NUMBER,
37 X_LAST_UPDATE_DATE DATE,
38 X_LAST_UPDATED_BY NUMBER,
39 X_CREATION_DATE DATE,
40 X_CREATED_BY NUMBER,
41 X_LAST_UPDATE_LOGIN NUMBER,
42 X_CALLING_SEQUENCE IN VARCHAR2) IS
43 CURRENT_CALLING_SEQUENCE VARCHAR2(2000);
44 DEBUG_INFO VARCHAR2(100);
45 BEGIN
46 CURRENT_CALLING_SEQUENCE := 'JL_BR_AR_RET_INTERFACE_EXT_PKG.UPDATE_ROW <-'||X_CALLING_SEQUENCE;
47 DEBUG_INFO := 'UPDATE JL_BR_AR_RET_INTERFACE_EXT';
48
49 UPDATE JL_BR_AR_RET_INTERFACE_EXT
50 SET
51 FILE_CONTROL = X_FILE_CONTROL,
52 ENTRY_SEQUENTIAL_NUMBER = X_ENTRY_SEQUENTIAL_NUMBER,
53 COMPANY_CODE = X_COMPANY_CODE,
54 COMPANY_NAME = X_COMPANY_NAME,
55 GENERATION_DATE = X_GENERATION_DATE,
56 REMITTANCE_CODE = X_REMITTANCE_CODE,
57 --BANK_NUMBER = X_BANK_NUMBER,
58 BANK_PARTY_ID = X_BANK_PARTY_ID,
59 BANK_OCCURRENCE_CODE = X_BANK_OCCURRENCE_CODE,
60 OCCURRENCE_DATE = X_OCCURRENCE_DATE,
61 PROCESSING_DATE = X_PROCESSING_DATE,
62 LAST_VALIDATION_DATE = X_LAST_VALIDATION_DATE,
63 INSCRIPTION_NUMBER = X_INSCRIPTION_NUMBER,
64 COMPANY_USE = X_COMPANY_USE,
65 OUR_NUMBER = X_OUR_NUMBER,
66 PORTFOLIO_CODE = X_PORTFOLIO_CODE,
67 YOUR_NUMBER = X_YOUR_NUMBER,
68 DUE_DATE = X_DUE_DATE,
69 TRADE_NOTE_AMOUNT = X_TRADE_NOTE_AMOUNT,
70 COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID,
71 COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID,
72 TRADE_NOTE_TYPE = X_TRADE_NOTE_TYPE,
73 BANK_CHARGE_AMOUNT = X_BANK_CHARGE_AMOUNT,
74 ABATEMENT_AMOUNT = X_ABATEMENT_AMOUNT,
75 DISCOUNT_AMOUNT = X_DISCOUNT_AMOUNT,
76 CREDIT_AMOUNT = X_CREDIT_AMOUNT,
77 INTEREST_AMOUNT_RECEIVED = X_INTEREST_AMOUNT_RECEIVED,
78 CUSTOMER_NAME = X_CUSTOMER_NAME,
79 RETURN_INFO = X_RETURN_INFO,
80 BANK_USE = X_BANK_USE,
81 ERROR_CODE = X_ERROR_CODE,
82 CANCELLATION_CODE = X_CANCELLATION_CODE,
83 ORG_ID = X_ORG_ID,
84 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
85 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
86 CREATION_DATE = X_CREATION_DATE,
87 CREATED_BY = X_CREATED_BY,
88 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
89 WHERE rowid = X_Rowid;
90 if (SQL%NOTFOUND) then
91 Raise NO_DATA_FOUND;
92 end if;
93 EXCEPTION WHEN OTHERS THEN
94 IF (SQLCODE <> -20001) THEN
95 FND_MESSAGE.SET_NAME('SQLGL','GL_DEBUG');
96 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
97 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',CURRENT_CALLING_SEQUENCE);
98 FND_MESSAGE.SET_TOKEN('PARAMETERS','Rowid = '||X_Rowid);
99 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',DEBUG_INFO);
100 END IF;
101 APP_EXCEPTION.RAISE_EXCEPTION;
102 END Update_Row;
103
104 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
105 X_CALLING_SEQUENCE IN VARCHAR2) IS
106 CURRENT_CALLING_SEQUENCE VARCHAR2(2000);
107 DEBUG_INFO VARCHAR2(100);
108 BEGIN
109 CURRENT_CALLING_SEQUENCE := 'JL_BR_AR_RET_INTERFACE_EXT_PKG.DELETE_ROW<-'||X_CALLING_SEQUENCE;
110 DEBUG_INFO := 'DELETE FROM JL_BR_AR_RET_INTERFACE_EXT';
111 DELETE FROM JL_BR_AR_RET_INTERFACE_EXT
112 WHERE rowid = X_Rowid;
113 if (SQL%NOTFOUND) then
114 RAISE NO_DATA_FOUND;
115 end if;
116 EXCEPTION WHEN OTHERS THEN
117 IF (SQLCODE <> -20001) THEN
118 FND_MESSAGE.SET_NAME('SQLGL','GL_DEBUG');
119 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
120 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',CURRENT_CALLING_SEQUENCE);
121 FND_MESSAGE.SET_TOKEN('PARAMETERS','FILE_CONTROL = '||X_ROWID);
122 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',DEBUG_INFO);
123 END IF;
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 END Delete_Row;
126
127
128 PROCEDURE Lock_Row(X_ROWID VARCHAR2,
129 X_FILE_CONTROL VARCHAR2,
130 X_ENTRY_SEQUENTIAL_NUMBER NUMBER,
131 X_COMPANY_CODE NUMBER DEFAULT NULL,
132 X_COMPANY_NAME VARCHAR2,
133 X_GENERATION_DATE DATE,
134 X_REMITTANCE_CODE NUMBER,
135 --X_BANK_NUMBER VARCHAR2,
136 X_BANK_PARTY_ID NUMBER,
137 X_BANK_OCCURRENCE_CODE NUMBER,
138 X_OCCURRENCE_DATE DATE,
139 X_PROCESSING_DATE DATE,
140 X_LAST_VALIDATION_DATE DATE,
141 X_INSCRIPTION_NUMBER NUMBER DEFAULT NULL,
142 X_COMPANY_USE NUMBER DEFAULT NULL,
143 X_OUR_NUMBER VARCHAR2 DEFAULT NULL,
144 X_PORTFOLIO_CODE NUMBER DEFAULT NULL,
145 X_YOUR_NUMBER VARCHAR2 DEFAULT NULL,
146 X_DUE_DATE DATE DEFAULT NULL,
147 X_TRADE_NOTE_AMOUNT NUMBER DEFAULT NULL,
148 X_COLLECTOR_BANK_PARTY_ID NUMBER DEFAULT NULL,
149 X_COLLECTOR_BRANCH_PARTY_ID NUMBER DEFAULT NULL,
150 X_TRADE_NOTE_TYPE VARCHAR2 DEFAULT NULL,
151 X_BANK_CHARGE_AMOUNT NUMBER DEFAULT NULL,
152 X_ABATEMENT_AMOUNT NUMBER DEFAULT NULL,
153 X_DISCOUNT_AMOUNT NUMBER DEFAULT NULL,
154 X_CREDIT_AMOUNT NUMBER DEFAULT NULL,
155 X_INTEREST_AMOUNT_RECEIVED NUMBER DEFAULT NULL,
156 X_CUSTOMER_NAME VARCHAR2 DEFAULT NULL,
157 X_RETURN_INFO VARCHAR2 DEFAULT NULL,
158 X_BANK_USE VARCHAR2 DEFAULT NULL,
159 X_ERROR_CODE VARCHAR2 DEFAULT NULL,
160 X_CANCELLATION_CODE VARCHAR2 DEFAULT NULL,
161 X_ORG_ID NUMBER DEFAULT NULL,
162 X_LAST_UPDATE_DATE DATE,
163 X_LAST_UPDATED_BY NUMBER,
164 X_CREATION_DATE DATE DEFAULT NULL,
165 X_CREATED_BY NUMBER DEFAULT NULL,
166 X_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL,
167 X_CALLING_SEQUENCE VARCHAR2) IS
168 --
169 CURSOR C IS
170 SELECT *
171 FROM JL_BR_AR_RET_INTERFACE_EXT
172 WHERE rowid = X_Rowid
173 FOR UPDATE of File_Control NOWAIT;
174 Recinfo C%ROWTYPE;
175 --
176 current_calling_sequence VARCHAR2(2000);
177 debug_info VARCHAR2(100);
178
179 BEGIN
180 -- Update the calling sequence
181 current_calling_sequence := 'JL_BR_AR_RET_INTERFACE_EXT_PKG.LOCK_ROW<-' ||
182 X_calling_sequence;
183 debug_info := 'Open cursor C';
184 OPEN C;
185 debug_info := 'Fetch cursor C';
186 FETCH C INTO Recinfo;
187 IF (C%NOTFOUND) THEN
188 debug_info := 'Close cursor C - DATA NOTFOUND';
189 CLOSE C;
190 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
191 APP_EXCEPTION.Raise_Exception;
192 END IF;
193 debug_info := 'Close cursor C';
194 CLOSE C;
195 IF ((Recinfo.file_control = X_file_control)
196 AND (Recinfo.entry_sequential_number = X_entry_sequential_number)
197 AND ((Recinfo.company_code = X_company_code)
198 OR ((Recinfo.company_code IS NULL)
199 AND (X_company_code IS NULL)))
200 AND (Recinfo.COMPANY_NAME = X_COMPANY_NAME )
201 AND (Recinfo.REMITTANCE_CODE = X_REMITTANCE_CODE)
202 AND (Recinfo.GENERATION_DATE = X_GENERATION_DATE)
203 --AND (Recinfo.bank_number = X_bank_number)
204 AND (Recinfo.bank_party_id = X_bank_party_id)
205 AND (Recinfo.bank_occurrence_code = X_bank_occurrence_code)
206 AND (Recinfo.occurrence_date = X_Occurrence_Date)
207 AND (Recinfo.processing_date = X_processing_date)
208 AND (Recinfo.last_validation_date = X_last_validation_date)
209 AND ((Recinfo.inscription_number = X_inscription_number)
210 OR ((Recinfo.inscription_number IS NULL)
211 AND (X_inscription_number IS NULL)))
212 AND ((Recinfo.company_use = X_company_use)
213 OR ((Recinfo.company_use IS NULL)
214 AND (X_company_use IS NULL)))
215 AND ((Recinfo.OUR_NUMBER = X_OUR_NUMBER)
216 OR ((Recinfo.OUR_NUMBER IS NULL)
217 AND (X_OUR_NUMBER IS NULL)))
218 AND ((Recinfo.portfolio_code = X_Portfolio_Code)
219 OR ((Recinfo.portfolio_code IS NULL)
220 AND (X_Portfolio_Code IS NULL)))
221 AND ((Recinfo.your_number = X_your_number)
222 OR ((Recinfo.your_number IS NULL)
223 AND (X_your_number IS NULL)))
224 AND ((Recinfo.due_date = X_Due_Date)
225 OR ((Recinfo.due_date IS NULL)
226 AND (X_Due_Date IS NULL)))
227 AND ((Recinfo.trade_note_amount = X_trade_note_amount)
228 OR ((Recinfo.trade_note_amount IS NULL)
229 AND (X_trade_note_amount IS NULL)))
230 AND ((Recinfo.COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID)
231 OR ((Recinfo.COLLECTOR_BANK_PARTY_ID IS NULL)
232 AND (X_COLLECTOR_BANK_PARTY_ID IS NULL)))
233 AND ((Recinfo.COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID)
234 OR ((Recinfo.COLLECTOR_BRANCH_PARTY_ID IS NULL)
235 AND (X_COLLECTOR_BRANCH_PARTY_ID IS NULL)))
236 AND ((Recinfo.trade_note_type = X_trade_note_type)
237 OR ((Recinfo.trade_note_type IS NULL)
238 AND (X_trade_note_type IS NULL)))
239 AND ((Recinfo.bank_charge_amount = X_bank_charge_amount)
240 OR ((Recinfo.bank_charge_amount IS NULL)
241 AND (X_bank_charge_amount IS NULL)))
242 AND ((Recinfo.abatement_amount = X_abatement_amount)
243 OR ((Recinfo.abatement_amount IS NULL)
244 AND (X_abatement_amount IS NULL)))
245 AND ((Recinfo.discount_amount = X_Discount_Amount)
246 OR ((Recinfo.discount_amount IS NULL)
247 AND (X_Discount_Amount IS NULL)))
248 AND ((Recinfo.credit_amount = X_credit_amount)
249 OR ((Recinfo.credit_amount IS NULL)
250 AND (X_credit_amount IS NULL)))
251 AND ((Recinfo.interest_amount_received = X_interest_amount_received)
252 OR ((Recinfo.interest_amount_received IS NULL)
253 AND (X_interest_amount_received IS NULL)))
254 AND ((Recinfo.customer_name = X_customer_name)
255 OR ((Recinfo.customer_name IS NULL)
256 AND (X_customer_name IS NULL)))
257 AND ((Recinfo.return_info = X_return_info)
258 OR ((Recinfo.return_info IS NULL)
259 AND (X_return_info IS NULL)))
260 AND ((Recinfo.bank_use = X_bank_use)
261 OR ((Recinfo.bank_use IS NULL)
262 AND (X_bank_use IS NULL)))
263 AND ((Recinfo.error_code = X_error_code)
264 OR ((Recinfo.error_code IS NULL)
265 AND (X_error_code IS NULL)))
266 AND ((Recinfo.cancellation_code = X_cancellation_code)
267 OR ((Recinfo.cancellation_code IS NULL)
268 AND (X_cancellation_code IS NULL)))
269 AND ((Recinfo.org_id = X_Org_Id)
270 OR ((Recinfo.org_id IS NULL)
271 AND (X_Org_Id IS NULL)))
272 AND ((Recinfo.creation_date = X_Creation_Date)
273 OR ((Recinfo.creation_date IS NULL)
274 AND (X_Creation_Date IS NULL)))
275 AND ((Recinfo.created_by = X_Created_By)
276 OR ((Recinfo.created_by IS NULL)
277 AND (X_Created_By IS NULL)))) THEN
278 return;
279 ELSE
280 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
281 APP_EXCEPTION.Raise_Exception;
282 END IF;
283 --
284 EXCEPTION
285 WHEN OTHERS THEN
286 IF (SQLCODE <> -20001) THEN
287 IF (SQLCODE = -54) THEN
288 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
289 ELSE
290 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
291 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
292 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
293 FND_MESSAGE.SET_TOKEN('PARAMETERS','FILE_CONTROL = ' ||
294 X_FILE_CONTROL);
295 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
296 END IF;
297 END IF;
298 APP_EXCEPTION.RAISE_EXCEPTION;
299 --
300 END Lock_Row;
301
302 END JL_BR_AR_RET_INTERFACE_EXT_PKG;