[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_COLLECTION_DOCS_PKG
Source
1 PACKAGE BODY JL_BR_AR_COLLECTION_DOCS_PKG as
2 /* $Header: jlbrrcdb.pls 120.5 2003/09/15 21:52:25 vsidhart ship $ */
3
4 PROCEDURE Insert_Row ( X_Rowid IN OUT NOCOPY VARCHAR2,
5
6 X_DOCUMENT_ID NUMBER,
7 X_BORDERO_ID NUMBER,
8 X_PAYMENT_SCHEDULE_ID NUMBER,
9 X_DOCUMENT_STATUS VARCHAR2,
10 X_ORIGIN_TYPE VARCHAR2,
11 X_DUE_DATE DATE,
12 X_SELECTION_DATE DATE,
13 X_PORTFOLIO_CODE NUMBER,
14 X_BATCH_SOURCE_ID NUMBER,
15 X_RECEIPT_METHOD_ID NUMBER,
16 X_CUSTOMER_TRX_ID NUMBER,
17 X_TERMS_SEQUENCE_NUMBER NUMBER,
18 X_DOCUMENT_TYPE VARCHAR2,
19 X_BANK_ACCT_USE_ID NUMBER ,
20 X_PREVIOUS_DOC_STATUS VARCHAR2 ,
21 X_OUR_NUMBER VARCHAR2 ,
22 X_BANK_USE VARCHAR2 ,
23 X_COLLECTOR_BANK_PARTY_ID NUMBER ,
24 X_COLLECTOR_BRANCH_PARTY_ID NUMBER ,
25 X_FACTORING_RATE NUMBER ,
26 X_FACTORING_RATE_PERIOD NUMBER ,
27 X_FACTORING_AMOUNT NUMBER ,
28 X_FACTORING_DATE DATE ,
29 X_CANCELLATION_DATE DATE ,
30 X_BANK_INSTRUCTION_CODE1 NUMBER ,
31 X_BANK_INSTRUCTION_CODE2 NUMBER ,
32 X_NUM_DAYS_INSTRUCTION NUMBER ,
33 X_BANK_CHARGE_AMOUNT NUMBER ,
34 X_CASH_CCID NUMBER ,
35 X_BANK_CHARGES_CCID NUMBER ,
36 X_COLL_ENDORSEMENTS_CCID NUMBER ,
37 X_BILLS_COLLECTION_CCID NUMBER ,
38 X_CALCULATED_INTEREST_CCID NUMBER ,
39 X_INTEREST_WRITEOFF_CCID NUMBER ,
40 X_ABATEMENT_WRITEOFF_CCID NUMBER ,
41 X_ABATEMENT_REVENUE_CCID NUMBER ,
42 X_INTEREST_REVENUE_CCID NUMBER ,
43 X_CALCULATED_INT_RECTRX_ID NUMBER ,
44 X_INTEREST_WRITEOFF_RECTRX_ID NUMBER ,
45 X_INTEREST_REVENUE_RECTRX_ID NUMBER ,
46 X_ABATEMENT_WRITEOFF_RECTRX_ID NUMBER ,
47 X_ABATE_REVENUE_RECTRX_ID NUMBER ,
48 X_ATTRIBUTE_CATEGORY VARCHAR2 ,
49 X_ATTRIBUTE1 VARCHAR2 ,
50 X_ATTRIBUTE2 VARCHAR2 ,
51 X_ATTRIBUTE3 VARCHAR2 ,
52 X_ATTRIBUTE4 VARCHAR2 ,
53 X_ATTRIBUTE5 VARCHAR2 ,
54 X_ATTRIBUTE6 VARCHAR2 ,
55 X_ATTRIBUTE7 VARCHAR2 ,
56 X_ATTRIBUTE8 VARCHAR2 ,
57 X_ATTRIBUTE9 VARCHAR2 ,
58 X_ATTRIBUTE10 VARCHAR2 ,
59 X_ATTRIBUTE11 VARCHAR2 ,
60 X_ATTRIBUTE12 VARCHAR2 ,
61 X_ATTRIBUTE13 VARCHAR2 ,
62 X_ATTRIBUTE14 VARCHAR2 ,
63 X_ATTRIBUTE15 VARCHAR2 ,
64 X_LAST_UPDATE_DATE DATE,
65 X_LAST_UPDATED_BY NUMBER,
66 X_CREATION_DATE DATE,
67 X_CREATED_BY NUMBER,
68 X_LAST_UPDATE_LOGIN NUMBER,
69
70 X_calling_sequence VARCHAR2,
71 X_ORG_ID NUMBER
72 ) IS
73 CURSOR C IS SELECT rowid FROM JL_BR_AR_COLLECTION_DOCS
74 WHERE document_id = X_document_id;
75 current_calling_sequence VARCHAR2(2000);
76 debug_info VARCHAR2(100);
77
78 BEGIN
79 -- Update the calling sequence
80 --
81 current_calling_sequence := 'JL_BR_AR_COLLECTION_DOCS_PKG.INSERT_ROW<-' ||
82 X_calling_sequence;
83
84 debug_info := 'Insert into JL_BR_AR_COLLECTION_DOCS';
85 INSERT INTO JL_BR_AR_COLLECTION_DOCS
86 (
87 DOCUMENT_ID,
88 BORDERO_ID,
89 PAYMENT_SCHEDULE_ID,
90 DOCUMENT_STATUS,
91 ORIGIN_TYPE,
92 DUE_DATE,
93 SELECTION_DATE,
94 PORTFOLIO_CODE,
95 BATCH_SOURCE_ID,
96 RECEIPT_METHOD_ID,
97 CUSTOMER_TRX_ID,
98 TERMS_SEQUENCE_NUMBER,
99 DOCUMENT_TYPE,
100 BANK_ACCT_USE_ID,
101 PREVIOUS_DOC_STATUS,
102 OUR_NUMBER,
103 BANK_USE,
104 COLLECTOR_BANK_PARTY_ID,
105 COLLECTOR_BRANCH_PARTY_ID,
106 FACTORING_RATE,
107 FACTORING_RATE_PERIOD,
108 FACTORING_AMOUNT,
109 FACTORING_DATE,
110 CANCELLATION_DATE,
111 BANK_INSTRUCTION_CODE1,
112 BANK_INSTRUCTION_CODE2,
113 NUM_DAYS_INSTRUCTION,
114 BANK_CHARGE_AMOUNT,
115 CASH_CCID,
116 BANK_CHARGES_CCID,
117 COLL_ENDORSEMENTS_CCID,
118 BILLS_COLLECTION_CCID,
119 CALCULATED_INTEREST_CCID,
120 INTEREST_WRITEOFF_CCID,
121 ABATEMENT_WRITEOFF_CCID,
122 ABATEMENT_REVENUE_CCID,
123 INTEREST_REVENUE_CCID,
124 CALCULATED_INTEREST_RECTRX_ID,
125 INTEREST_WRITEOFF_RECTRX_ID,
126 INTEREST_REVENUE_RECTRX_ID,
127 ABATEMENT_WRITEOFF_RECTRX_ID,
128 ABATE_REVENUE_RECTRX_ID,
129 ATTRIBUTE_CATEGORY,
130 ATTRIBUTE1,
131 ATTRIBUTE2,
132 ATTRIBUTE3,
133 ATTRIBUTE4,
134 ATTRIBUTE5,
135 ATTRIBUTE6,
136 ATTRIBUTE7,
137 ATTRIBUTE8,
138 ATTRIBUTE9,
139 ATTRIBUTE10,
140 ATTRIBUTE11,
141 ATTRIBUTE12,
142 ATTRIBUTE13,
143 ATTRIBUTE14,
144 ATTRIBUTE15,
145 LAST_UPDATE_DATE,
146 LAST_UPDATED_BY,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_LOGIN,
150 ORG_ID
151 )
152 VALUES (
153 X_DOCUMENT_ID,
154 X_BORDERO_ID,
155 X_PAYMENT_SCHEDULE_ID,
156 X_DOCUMENT_STATUS,
157 X_ORIGIN_TYPE,
158 X_DUE_DATE,
159 X_SELECTION_DATE,
160 X_PORTFOLIO_CODE,
161 X_BATCH_SOURCE_ID,
162 X_RECEIPT_METHOD_ID,
163 X_CUSTOMER_TRX_ID,
164 X_TERMS_SEQUENCE_NUMBER,
165 X_DOCUMENT_TYPE,
166 X_BANK_ACCT_USE_ID,
167 X_PREVIOUS_DOC_STATUS,
168 X_OUR_NUMBER,
169 X_BANK_USE,
170 X_COLLECTOR_BANK_PARTY_ID,
171 X_COLLECTOR_BRANCH_PARTY_ID,
172 X_FACTORING_RATE,
173 X_FACTORING_RATE_PERIOD,
174 X_FACTORING_AMOUNT,
175 X_FACTORING_DATE,
176 X_CANCELLATION_DATE,
177 X_BANK_INSTRUCTION_CODE1,
178 X_BANK_INSTRUCTION_CODE2,
179 X_NUM_DAYS_INSTRUCTION,
180 X_BANK_CHARGE_AMOUNT,
181 X_CASH_CCID,
182 X_BANK_CHARGES_CCID,
183 X_COLL_ENDORSEMENTS_CCID,
184 X_BILLS_COLLECTION_CCID,
185 X_CALCULATED_INTEREST_CCID,
186 X_INTEREST_WRITEOFF_CCID,
187 X_ABATEMENT_WRITEOFF_CCID,
188 X_ABATEMENT_REVENUE_CCID,
189 X_INTEREST_REVENUE_CCID,
190 X_CALCULATED_INT_RECTRX_ID,
191 X_INTEREST_WRITEOFF_RECTRX_ID,
192 X_INTEREST_REVENUE_RECTRX_ID,
193 X_ABATEMENT_WRITEOFF_RECTRX_ID,
194 X_ABATE_REVENUE_RECTRX_ID,
195 X_ATTRIBUTE_CATEGORY,
196 X_ATTRIBUTE1,
197 X_ATTRIBUTE2,
198 X_ATTRIBUTE3,
199 X_ATTRIBUTE4,
200 X_ATTRIBUTE5,
201 X_ATTRIBUTE6,
202 X_ATTRIBUTE7,
203 X_ATTRIBUTE8,
204 X_ATTRIBUTE9,
205 X_ATTRIBUTE10,
206 X_ATTRIBUTE11,
207 X_ATTRIBUTE12,
208 X_ATTRIBUTE13,
209 X_ATTRIBUTE14,
210 X_ATTRIBUTE15,
211 X_LAST_UPDATE_DATE,
212 X_LAST_UPDATED_BY,
213 X_CREATION_DATE,
214 X_CREATED_BY,
215 X_LAST_UPDATE_LOGIN,
216 X_ORG_ID
217 );
218
219 debug_info := 'Open cursor C';
220 OPEN C;
221 debug_info := 'Fetch cursor C';
222 FETCH C INTO X_Rowid;
223 if (C%NOTFOUND) then
224 debug_info := 'Close cursor C - DATA NOTFOUND';
225 CLOSE C;
226 Raise NO_DATA_FOUND;
227 end if;
228 debug_info := 'Close cursor C';
229 CLOSE C;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 IF (SQLCODE <> -20001) THEN
234 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
235 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
236 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
237 FND_MESSAGE.SET_TOKEN('PARAMETERS','document_id = ' ||
238 X_document_id );
239 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
240 END IF;
241 APP_EXCEPTION.RAISE_EXCEPTION;
242
243 END Insert_Row;
244
245 PROCEDURE Update_Row ( X_Rowid VARCHAR2,
246
247 X_DOCUMENT_ID NUMBER,
248 X_BORDERO_ID NUMBER,
249 X_PAYMENT_SCHEDULE_ID NUMBER,
250 X_DOCUMENT_STATUS VARCHAR2,
251 X_ORIGIN_TYPE VARCHAR2,
252 X_DUE_DATE DATE,
253 X_SELECTION_DATE DATE,
254 X_PORTFOLIO_CODE NUMBER,
255 X_BATCH_SOURCE_ID NUMBER,
256 X_RECEIPT_METHOD_ID NUMBER,
257 X_CUSTOMER_TRX_ID NUMBER,
258 X_TERMS_SEQUENCE_NUMBER NUMBER,
259 X_DOCUMENT_TYPE VARCHAR2,
260 X_BANK_ACCT_USE_ID NUMBER ,
261 X_PREVIOUS_DOC_STATUS VARCHAR2 ,
262 X_OUR_NUMBER VARCHAR2 ,
263 X_BANK_USE VARCHAR2 ,
264 X_COLLECTOR_BANK_PARTY_ID NUMBER ,
265 X_COLLECTOR_BRANCH_PARTY_ID NUMBER ,
266 X_FACTORING_RATE NUMBER ,
267 X_FACTORING_RATE_PERIOD NUMBER ,
268 X_FACTORING_AMOUNT NUMBER ,
269 X_FACTORING_DATE DATE ,
270 X_CANCELLATION_DATE DATE ,
271 X_BANK_INSTRUCTION_CODE1 NUMBER ,
272 X_BANK_INSTRUCTION_CODE2 NUMBER ,
273 X_NUM_DAYS_INSTRUCTION NUMBER ,
274 X_BANK_CHARGE_AMOUNT NUMBER ,
275 X_CASH_CCID NUMBER ,
276 X_BANK_CHARGES_CCID NUMBER ,
277 X_COLL_ENDORSEMENTS_CCID NUMBER ,
278 X_BILLS_COLLECTION_CCID NUMBER ,
279 X_CALCULATED_INTEREST_CCID NUMBER ,
280 X_INTEREST_WRITEOFF_CCID NUMBER ,
281 X_ABATEMENT_WRITEOFF_CCID NUMBER ,
282 X_ABATEMENT_REVENUE_CCID NUMBER ,
283 X_INTEREST_REVENUE_CCID NUMBER ,
284 X_CALCULATED_INT_RECTRX_ID NUMBER ,
285 X_INTEREST_WRITEOFF_RECTRX_ID NUMBER ,
286 X_INTEREST_REVENUE_RECTRX_ID NUMBER ,
287 X_ABATEMENT_WRITEOFF_RECTRX_ID NUMBER ,
288 X_ABATE_REVENUE_RECTRX_ID NUMBER ,
289 X_ATTRIBUTE_CATEGORY VARCHAR2 ,
290 X_ATTRIBUTE1 VARCHAR2 ,
291 X_ATTRIBUTE2 VARCHAR2 ,
292 X_ATTRIBUTE3 VARCHAR2 ,
293 X_ATTRIBUTE4 VARCHAR2 ,
294 X_ATTRIBUTE5 VARCHAR2 ,
295 X_ATTRIBUTE6 VARCHAR2 ,
296 X_ATTRIBUTE7 VARCHAR2 ,
297 X_ATTRIBUTE8 VARCHAR2 ,
298 X_ATTRIBUTE9 VARCHAR2 ,
299 X_ATTRIBUTE10 VARCHAR2 ,
300 X_ATTRIBUTE11 VARCHAR2 ,
301 X_ATTRIBUTE12 VARCHAR2 ,
302 X_ATTRIBUTE13 VARCHAR2 ,
303 X_ATTRIBUTE14 VARCHAR2 ,
304 X_ATTRIBUTE15 VARCHAR2 ,
305 X_LAST_UPDATE_DATE DATE,
306 X_LAST_UPDATED_BY NUMBER,
307 X_CREATION_DATE DATE,
308 X_CREATED_BY NUMBER,
309 X_LAST_UPDATE_LOGIN NUMBER,
310
311 X_calling_sequence VARCHAR2
312 ) IS
313 current_calling_sequence VARCHAR2(2000);
314 debug_info VARCHAR2(100);
315 BEGIN
316 -- Update the calling sequence
317 --
318 current_calling_sequence := 'JL_BR_AR_COLLECTION_DOCS_PKG.UPDATE_ROW<-' ||
319 X_calling_sequence;
320 debug_info := 'Update JL_BR_AR_COLLECTION_DOCS';
321 UPDATE JL_BR_AR_COLLECTION_DOCS
322 SET
323 DOCUMENT_ID = X_DOCUMENT_ID ,
324 BORDERO_ID = X_BORDERO_ID ,
325 PAYMENT_SCHEDULE_ID = X_PAYMENT_SCHEDULE_ID ,
326 DOCUMENT_STATUS = X_DOCUMENT_STATUS ,
327 ORIGIN_TYPE = X_ORIGIN_TYPE ,
328 DUE_DATE = X_DUE_DATE ,
329 SELECTION_DATE = X_SELECTION_DATE ,
330 PORTFOLIO_CODE = X_PORTFOLIO_CODE ,
331 BATCH_SOURCE_ID = X_BATCH_SOURCE_ID ,
332 RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID ,
333 CUSTOMER_TRX_ID = X_CUSTOMER_TRX_ID ,
334 TERMS_SEQUENCE_NUMBER = X_TERMS_SEQUENCE_NUMBER ,
335 DOCUMENT_TYPE = X_DOCUMENT_TYPE ,
336 BANK_ACCT_USE_ID = X_BANK_ACCT_USE_ID ,
337 PREVIOUS_DOC_STATUS = X_PREVIOUS_DOC_STATUS ,
338 OUR_NUMBER = X_OUR_NUMBER ,
339 BANK_USE = X_BANK_USE ,
340 COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID ,
341 COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID ,
342 FACTORING_RATE = X_FACTORING_RATE ,
343 FACTORING_RATE_PERIOD = X_FACTORING_RATE_PERIOD ,
344 FACTORING_AMOUNT = X_FACTORING_AMOUNT ,
345 FACTORING_DATE = X_FACTORING_DATE ,
346 CANCELLATION_DATE = X_CANCELLATION_DATE ,
347 BANK_INSTRUCTION_CODE1 = X_BANK_INSTRUCTION_CODE1 ,
348 BANK_INSTRUCTION_CODE2 = X_BANK_INSTRUCTION_CODE2 ,
349 NUM_DAYS_INSTRUCTION = X_NUM_DAYS_INSTRUCTION ,
350 BANK_CHARGE_AMOUNT = X_BANK_CHARGE_AMOUNT ,
351 CASH_CCID = X_CASH_CCID ,
352 BANK_CHARGES_CCID = X_BANK_CHARGES_CCID ,
353 COLL_ENDORSEMENTS_CCID = X_COLL_ENDORSEMENTS_CCID ,
354 BILLS_COLLECTION_CCID = X_BILLS_COLLECTION_CCID ,
355 CALCULATED_INTEREST_CCID = X_CALCULATED_INTEREST_CCID ,
356 INTEREST_WRITEOFF_CCID = X_INTEREST_WRITEOFF_CCID ,
357 ABATEMENT_WRITEOFF_CCID = X_ABATEMENT_WRITEOFF_CCID ,
358 ABATEMENT_REVENUE_CCID = X_ABATEMENT_REVENUE_CCID ,
359 INTEREST_REVENUE_CCID = X_INTEREST_REVENUE_CCID ,
360 CALCULATED_INTEREST_RECTRX_ID = X_CALCULATED_INT_RECTRX_ID ,
361 INTEREST_WRITEOFF_RECTRX_ID = X_INTEREST_WRITEOFF_RECTRX_ID ,
362 INTEREST_REVENUE_RECTRX_ID = X_INTEREST_REVENUE_RECTRX_ID ,
363 ABATEMENT_WRITEOFF_RECTRX_ID = X_ABATEMENT_WRITEOFF_RECTRX_ID ,
364 ABATE_REVENUE_RECTRX_ID = X_ABATE_REVENUE_RECTRX_ID ,
365 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY ,
366 ATTRIBUTE1 = X_ATTRIBUTE1 ,
367 ATTRIBUTE2 = X_ATTRIBUTE2 ,
368 ATTRIBUTE3 = X_ATTRIBUTE3 ,
369 ATTRIBUTE4 = X_ATTRIBUTE4 ,
370 ATTRIBUTE5 = X_ATTRIBUTE5 ,
371 ATTRIBUTE6 = X_ATTRIBUTE6 ,
372 ATTRIBUTE7 = X_ATTRIBUTE7 ,
373 ATTRIBUTE8 = X_ATTRIBUTE8 ,
374 ATTRIBUTE9 = X_ATTRIBUTE9 ,
375 ATTRIBUTE10 = X_ATTRIBUTE10 ,
376 ATTRIBUTE11 = X_ATTRIBUTE11 ,
377 ATTRIBUTE12 = X_ATTRIBUTE12 ,
378 ATTRIBUTE13 = X_ATTRIBUTE13 ,
379 ATTRIBUTE14 = X_ATTRIBUTE14 ,
380 ATTRIBUTE15 = X_ATTRIBUTE15 ,
381 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE ,
382 LAST_UPDATED_BY = X_LAST_UPDATED_BY ,
383 CREATION_DATE = X_CREATION_DATE ,
384 CREATED_BY = X_CREATED_BY ,
385 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
386 WHERE rowid = X_Rowid;
387
388 if (SQL%NOTFOUND) then
389 Raise NO_DATA_FOUND;
390 end if;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 IF (SQLCODE <> -20001) THEN
395 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
396 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
397 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
398 FND_MESSAGE.SET_TOKEN('PARAMETERS','document_id = ' ||
399 X_document_id );
400 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
401 END IF;
402 APP_EXCEPTION.RAISE_EXCEPTION;
403
404 END Update_Row;
405
406 END JL_BR_AR_COLLECTION_DOCS_PKG;