[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_OCCURRENCE_DOCS_PKG
Source
1 PACKAGE BODY JL_BR_AR_OCCURRENCE_DOCS_PKG as
2 /* $Header: jlbrriob.pls 120.5 2003/09/15 21:56:33 vsidhart ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Occurrence_Id NUMBER,
6 X_Document_Id NUMBER,
7 X_Bank_Occurrence_Code NUMBER,
8 --X_Bank_Number VARCHAR2,
9 X_Bank_Party_Id NUMBER,
10 X_Bank_Occurrence_Type VARCHAR2,
11 X_Occurrence_Date DATE ,
12 X_Occurrence_Status VARCHAR2 ,
13 X_Original_Remittance_Media VARCHAR2 ,
14 X_Remittance_Media VARCHAR2 ,
15 X_Selection_Date DATE ,
16 X_Bordero_Id NUMBER ,
17 X_Portfolio_Code NUMBER ,
18 X_Trade_Note_Number VARCHAR2 ,
19 X_Due_Date DATE ,
20 X_Document_Amount NUMBER ,
21 X_Bank_Instruction_Code1 NUMBER ,
22 X_Bank_Instruction_Code2 NUMBER ,
23 X_Num_Days_Instruction NUMBER ,
24 X_Interest_Percent NUMBER ,
25 X_Interest_Period NUMBER ,
26 X_Interest_Amount NUMBER ,
27 X_Grace_Days NUMBER ,
28 X_Discount_Limit_Date DATE ,
29 X_Discount_Amount NUMBER ,
30 X_Customer_Id NUMBER ,
31 X_Site_Use_Id NUMBER ,
32 X_Abatement_Amount NUMBER ,
33 X_Flag_Post_Gl VARCHAR2 ,
34 X_Gl_Date DATE ,
35 X_Gl_Posted_Date DATE ,
36 X_Endorsement_Credit_Ccid NUMBER ,
37 X_Endorsement_Debit_Ccid NUMBER ,
38 X_Endorsement_Debit_Amount NUMBER ,
39 X_Endorsement_Credit_Amount NUMBER ,
40 X_Bank_Charge_Amount NUMBER ,
41 X_Bank_Charges_Credit_Ccid NUMBER ,
42 X_Bank_Charges_Debit_Ccid NUMBER ,
43 X_Bank_Charges_Credit_Amount NUMBER ,
44 X_Bank_Charges_Debit_Amount NUMBER ,
45 X_Request_Id NUMBER ,
46 X_Return_Info VARCHAR2 ,
47 X_Interest_Indicator VARCHAR2 ,
48 X_Return_Request_Id NUMBER ,
49 X_Gl_Cancel_Date DATE ,
50 X_Attribute_Category VARCHAR2 ,
51 X_Attribute1 VARCHAR2 ,
52 X_Attribute2 VARCHAR2 ,
53 X_Attribute3 VARCHAR2 ,
54 X_Attribute4 VARCHAR2 ,
55 X_Attribute5 VARCHAR2 ,
56 X_Attribute6 VARCHAR2 ,
57 X_Attribute7 VARCHAR2 ,
58 X_Attribute8 VARCHAR2 ,
59 X_Attribute9 VARCHAR2 ,
60 X_Attribute10 VARCHAR2 ,
61 X_Attribute11 VARCHAR2 ,
62 X_Attribute12 VARCHAR2 ,
63 X_Attribute13 VARCHAR2 ,
64 X_Attribute14 VARCHAR2 ,
65 X_Attribute15 VARCHAR2 ,
66 X_Last_Update_Date DATE,
67 X_Last_Updated_By NUMBER,
68 X_Creation_Date DATE ,
69 X_Created_By NUMBER,
70 X_Last_Update_Login NUMBER ,
71 X_calling_sequence IN VARCHAR2,
72 X_ORG_ID NUMBER) IS
73 --
74 CURSOR C IS
75 SELECT rowid
76 FROM JL_BR_AR_OCCURRENCE_DOCS
77 WHERE document_id = X_Document_Id;
78 --
79 current_calling_sequence VARCHAR2(2000);
80 debug_info VARCHAR2(100);
81 --
82 BEGIN
83 -- Update the calling sequence
84 current_calling_sequence := 'JL_BR_AR_OCCURRENCE_DOCS_PKG.INSERT_ROW<-' ||
85 X_calling_sequence;
86
87 debug_info := 'Insert into JL_BR_AR_OCCURRENCE_DOCS';
88 INSERT INTO JL_BR_AR_OCCURRENCE_DOCS(
89 occurrence_id,
90 document_id,
91 bank_occurrence_code,
92 --bank_number,
93 bank_party_id,
94 bank_occurrence_type,
95 occurrence_date,
96 occurrence_status,
97 original_remittance_media,
98 remittance_media,
99 selection_date,
100 bordero_id,
101 portfolio_code,
102 trade_note_number,
103 due_date,
104 document_amount,
105 bank_instruction_code1,
106 bank_instruction_code2,
107 num_days_instruction,
108 interest_percent,
109 interest_period,
110 interest_amount,
111 grace_days,
112 discount_limit_date,
113 discount_amount,
114 customer_id,
115 site_use_id,
116 abatement_amount,
117 flag_post_gl,
118 gl_date,
119 gl_posted_date,
120 endorsement_credit_ccid,
121 endorsement_debit_ccid,
122 endorsement_debit_amount,
123 endorsement_credit_amount,
124 bank_charge_amount,
125 bank_charges_credit_ccid,
126 bank_charges_debit_ccid,
127 bank_charges_credit_amount,
128 bank_charges_debit_amount,
129 request_id,
130 return_info,
131 interest_indicator,
132 return_request_id,
133 gl_cancel_date,
134 attribute_category,
135 attribute1,
136 attribute2,
137 attribute3,
138 attribute4,
139 attribute5,
140 attribute6,
141 attribute7,
142 attribute8,
143 attribute9,
144 attribute10,
145 attribute11,
146 attribute12,
147 attribute13,
148 attribute14,
149 attribute15,
150 last_update_date,
151 last_updated_by,
152 creation_date,
153 created_by,
154 last_update_login,
155 org_id)
156 VALUES (
157 X_Occurrence_Id,
158 X_Document_Id,
159 X_Bank_Occurrence_Code,
160 --X_Bank_Number,
161 X_bank_Party_Id,
162 X_Bank_Occurrence_Type,
163 X_Occurrence_Date,
164 X_Occurrence_Status,
165 X_Original_Remittance_Media,
166 X_Remittance_Media,
167 X_Selection_Date,
168 X_Bordero_Id,
169 X_Portfolio_Code,
170 X_Trade_Note_Number,
171 X_Due_Date,
172 X_Document_Amount,
173 X_Bank_Instruction_Code1,
174 X_Bank_Instruction_Code2,
175 X_Num_Days_Instruction,
176 X_Interest_Percent,
177 X_Interest_Period,
178 X_Interest_Amount,
179 X_Grace_Days,
180 X_Discount_Limit_Date,
181 X_Discount_Amount,
182 X_Customer_Id,
183 X_Site_Use_Id,
184 X_Abatement_Amount,
185 X_Flag_Post_Gl,
186 X_Gl_Date,
187 X_Gl_Posted_Date,
188 X_Endorsement_Credit_Ccid,
189 X_Endorsement_Debit_Ccid,
190 X_Endorsement_Debit_Amount,
191 X_Endorsement_Credit_Amount,
192 X_Bank_Charge_Amount,
193 X_Bank_Charges_Credit_Ccid,
194 X_Bank_Charges_Debit_Ccid,
195 X_Bank_Charges_Credit_Amount,
196 X_Bank_Charges_Debit_Amount,
197 X_Request_Id,
198 X_Return_Info,
199 X_Interest_Indicator,
200 X_Return_Request_Id,
201 X_Gl_Cancel_Date,
202 X_Attribute_Category,
203 X_Attribute1,
204 X_Attribute2,
205 X_Attribute3,
206 X_Attribute4,
207 X_Attribute5,
208 X_Attribute6,
209 X_Attribute7,
210 X_Attribute8,
211 X_Attribute9,
212 X_Attribute10,
213 X_Attribute11,
214 X_Attribute12,
215 X_Attribute13,
216 X_Attribute14,
217 X_Attribute15,
218 X_Last_Update_Date,
219 X_Last_Updated_By,
220 X_Creation_Date,
221 X_Created_By,
222 X_Last_Update_Login,
223 X_Org_Id);
224 --
225 debug_info := 'Open cursor C';
226 OPEN C;
227 debug_info := 'Fetch cursor C';
228 FETCH C INTO X_Rowid;
229 IF (C%NOTFOUND) THEN
230 debug_info := 'Close cursor C - DATA NOTFOUND';
231 CLOSE C;
232 RAISE NO_DATA_FOUND;
233 END IF;
234 debug_info := 'Close cursor C';
235 CLOSE C;
236 --
237 EXCEPTION
238 WHEN OTHERS THEN
239 IF (SQLCODE <> -20001) THEN
240 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
241 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
242 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
243 FND_MESSAGE.SET_TOKEN('PARAMETERS','DOCUMENT_ID = ' ||
244 X_Document_Id);
245 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
246 END IF;
247 APP_EXCEPTION.RAISE_EXCEPTION;
248 --
249 END Insert_Row;
250
251 PROCEDURE Update_Row(X_Rowid VARCHAR2,
252 X_Occurrence_Id NUMBER,
253 X_Document_Id NUMBER,
254 X_Bank_Occurrence_Code NUMBER,
255 --X_Bank_Number VARCHAR2,
256 X_bank_Party_Id NUMBER,
257 X_Bank_Occurrence_Type VARCHAR2,
258 X_Occurrence_Date DATE ,
259 X_Occurrence_Status VARCHAR2 ,
260 X_Original_Remittance_Media VARCHAR2 ,
261 X_Remittance_Media VARCHAR2 ,
262 X_Selection_Date DATE ,
263 X_Bordero_Id NUMBER ,
264 X_Portfolio_Code NUMBER ,
265 X_Trade_Note_Number VARCHAR2 ,
266 X_Due_Date DATE ,
267 X_Document_Amount NUMBER ,
268 X_Bank_Instruction_Code1 NUMBER ,
269 X_Bank_Instruction_Code2 NUMBER ,
270 X_Num_Days_Instruction NUMBER ,
271 X_Interest_Percent NUMBER ,
272 X_Interest_Period NUMBER ,
273 X_Interest_Amount NUMBER ,
274 X_Grace_Days NUMBER ,
275 X_Discount_Limit_Date DATE ,
276 X_Discount_Amount NUMBER ,
277 X_Customer_Id NUMBER ,
278 X_Site_Use_Id NUMBER ,
279 X_Abatement_Amount NUMBER ,
280 X_Flag_Post_Gl VARCHAR2 ,
281 X_Gl_Date DATE ,
282 X_Gl_Posted_Date DATE ,
283 X_Endorsement_Credit_Ccid NUMBER ,
284 X_Endorsement_Debit_Ccid NUMBER ,
285 X_Endorsement_Debit_Amount NUMBER ,
286 X_Endorsement_Credit_Amount NUMBER ,
287 X_Bank_Charge_Amount NUMBER ,
288 X_Bank_Charges_Credit_Ccid NUMBER ,
289 X_Bank_Charges_Debit_Ccid NUMBER ,
290 X_Bank_Charges_Credit_Amount NUMBER ,
291 X_Bank_Charges_Debit_Amount NUMBER ,
292 X_Request_Id NUMBER ,
293 X_Return_Info VARCHAR2 ,
294 X_Interest_Indicator VARCHAR2 ,
295 X_Return_Request_Id NUMBER ,
296 X_Gl_Cancel_Date DATE ,
297 X_Attribute_Category VARCHAR2 ,
298 X_Attribute1 VARCHAR2 ,
299 X_Attribute2 VARCHAR2 ,
300 X_Attribute3 VARCHAR2 ,
301 X_Attribute4 VARCHAR2 ,
302 X_Attribute5 VARCHAR2 ,
303 X_Attribute6 VARCHAR2 ,
304 X_Attribute7 VARCHAR2 ,
305 X_Attribute8 VARCHAR2 ,
306 X_Attribute9 VARCHAR2 ,
307 X_Attribute10 VARCHAR2 ,
308 X_Attribute11 VARCHAR2 ,
309 X_Attribute12 VARCHAR2 ,
310 X_Attribute13 VARCHAR2 ,
311 X_Attribute14 VARCHAR2 ,
312 X_Attribute15 VARCHAR2 ,
313 X_Last_Update_Date DATE,
314 X_Last_Updated_By NUMBER,
315 X_Creation_Date DATE ,
316 X_Created_By NUMBER,
317 X_Last_Update_Login NUMBER ,
318 X_calling_sequence IN VARCHAR2) IS
319 --
320 current_calling_sequence VARCHAR2(2000);
321 debug_info VARCHAR2(100);
322 BEGIN
323 -- Update the calling sequence
324 --
325 current_calling_sequence := 'JL_BR_AR_OCCURRENCE_DOCS_PKG.UPDATE_ROW<-' ||
326 X_calling_sequence;
327 debug_info := 'Update JL_BR_AR_OCCURRENCE_DOCS';
328 UPDATE JL_BR_AR_OCCURRENCE_DOCS
329 SET Occurrence_Id = X_Occurrence_Id,
330 Document_Id = X_Document_Id,
331 bank_occurrence_Code = X_Bank_Occurrence_Code,
332 --Bank_Number = X_Bank_Number,
333 Bank_Party_Id = X_bank_Party_Id,
334 Bank_Occurrence_type = X_Bank_Occurrence_Type,
335 occurrence_date = X_Occurrence_Date,
336 occurrence_status = X_Occurrence_Status,
337 original_remittance_media = X_Original_Remittance_Media,
338 remittance_media = X_Remittance_Media,
339 selection_date = X_Selection_Date,
343 due_date = X_Due_Date,
340 bordero_id = X_Bordero_Id,
341 portfolio_code = X_Portfolio_Code,
342 trade_note_number = X_Trade_Note_Number,
344 document_amount = X_Document_Amount,
345 bank_instruction_code1 = X_Bank_Instruction_Code1,
346 bank_instruction_code2 = X_Bank_Instruction_Code2,
347 num_days_instruction = X_Num_Days_Instruction,
348 interest_percent = X_Interest_Percent,
349 interest_period = X_Interest_Period,
350 interest_amount = X_Interest_Amount,
351 grace_days = X_Grace_Days,
352 discount_limit_date = X_Discount_Limit_Date,
353 discount_amount = X_Discount_Amount,
354 customer_id = X_Customer_Id,
355 site_use_id = X_Site_Use_Id,
356 abatement_amount = X_Abatement_Amount,
357 flag_post_gl = X_Flag_Post_Gl,
358 gl_date = X_Gl_Date,
359 gl_posted_date = X_Gl_Posted_Date,
360 endorsement_credit_ccid = X_Endorsement_Credit_Ccid,
361 endorsement_debit_ccid = X_Endorsement_Debit_Ccid,
362 endorsement_debit_amount = X_Endorsement_Debit_Amount,
363 endorsement_credit_amount = X_Endorsement_Credit_Amount,
364 bank_charge_amount = X_Bank_Charge_Amount,
365 bank_charges_credit_ccid = X_Bank_Charges_Credit_Ccid,
366 bank_charges_debit_ccid = X_Bank_Charges_Debit_Ccid,
367 bank_charges_credit_amount = X_Bank_Charges_Credit_Amount,
368 bank_charges_debit_amount = X_Bank_Charges_Debit_Amount,
369 request_id = X_Request_Id,
370 return_info = X_Return_Info,
371 interest_indicator = X_Interest_Indicator,
372 return_request_id = X_Return_Request_Id,
373 gl_cancel_date = X_Gl_Cancel_Date,
374 attribute_category = X_Attribute_Category,
375 attribute1 = X_Attribute1,
376 attribute2 = X_Attribute2,
377 attribute3 = X_Attribute3,
378 attribute4 = X_Attribute4,
379 attribute5 = X_Attribute5,
380 attribute6 = X_Attribute6,
381 attribute7 = X_Attribute7,
382 attribute8 = X_Attribute8,
383 attribute9 = X_Attribute9,
384 attribute10 = X_Attribute10,
385 attribute11 = X_Attribute11,
386 attribute12 = X_Attribute12,
387 attribute13 = X_Attribute13,
388 attribute14 = X_Attribute14,
389 attribute15 = X_Attribute15,
390 last_update_date = X_Last_Update_Date,
391 last_updated_by = X_Last_Updated_By,
392 creation_date = X_Creation_Date,
393 created_by = X_Created_By,
394 last_update_login = X_Last_Update_Login
395 WHERE rowid = X_Rowid;
396 --
397 IF (SQL%NOTFOUND) THEN
398 RAISE NO_DATA_FOUND;
399 END IF;
400 --
401 EXCEPTION
402 WHEN OTHERS THEN
403 IF (SQLCODE <> -20001) THEN
404 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
405 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
406 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
407 FND_MESSAGE.SET_TOKEN('PARAMETERS','DOCUMENT_ID = ' ||
408 X_Document_Id);
409 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
410 END IF;
411 APP_EXCEPTION.RAISE_EXCEPTION;
412 --
413 END Update_Row;
414 END JL_BR_AR_OCCURRENCE_DOCS_PKG;