1 PACKAGE BODY JL_BR_AR_COLLECTION_DOC2_PKG as
2 /* $Header: jlbrrc2b.pls 120.3 2005/02/23 23:27:47 vsidhart ship $ */
3
4 PROCEDURE Lock_Row ( X_Rowid 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 DEFAULT NULL,
20 X_PREVIOUS_DOC_STATUS VARCHAR2 DEFAULT NULL,
21 X_OUR_NUMBER VARCHAR2 DEFAULT NULL,
22 X_BANK_USE VARCHAR2 DEFAULT NULL,
23 X_COLLECTOR_BANK_PARTY_ID NUMBER DEFAULT NULL,
24 X_COLLECTOR_BRANCH_PARTY_ID NUMBER DEFAULT NULL,
25 X_FACTORING_RATE NUMBER DEFAULT NULL,
26 X_FACTORING_RATE_PERIOD NUMBER DEFAULT NULL,
27 X_FACTORING_AMOUNT NUMBER DEFAULT NULL,
28 X_FACTORING_DATE DATE DEFAULT NULL,
29 X_CANCELLATION_DATE DATE DEFAULT NULL,
30 X_BANK_INSTRUCTION_CODE1 NUMBER DEFAULT NULL,
31 X_BANK_INSTRUCTION_CODE2 NUMBER DEFAULT NULL,
32 X_NUM_DAYS_INSTRUCTION NUMBER DEFAULT NULL,
33 X_BANK_CHARGE_AMOUNT NUMBER DEFAULT NULL,
34 X_CASH_CCID NUMBER DEFAULT NULL,
35 X_BANK_CHARGES_CCID NUMBER DEFAULT NULL,
36 X_COLL_ENDORSEMENTS_CCID NUMBER DEFAULT NULL,
37 X_BILLS_COLLECTION_CCID NUMBER DEFAULT NULL,
38 X_CALCULATED_INTEREST_CCID NUMBER DEFAULT NULL,
39 X_INTEREST_WRITEOFF_CCID NUMBER DEFAULT NULL,
40 X_ABATEMENT_WRITEOFF_CCID NUMBER DEFAULT NULL,
41 X_ABATEMENT_REVENUE_CCID NUMBER DEFAULT NULL,
42 X_INTEREST_REVENUE_CCID NUMBER DEFAULT NULL,
43 X_CALCULATED_INT_RECTRX_ID NUMBER DEFAULT NULL,
44 X_INTEREST_WRITEOFF_RECTRX_ID NUMBER DEFAULT NULL,
45 X_INTEREST_REVENUE_RECTRX_ID NUMBER DEFAULT NULL,
46 X_ABATEMENT_WRITEOFF_RECTRX_ID NUMBER DEFAULT NULL,
47 X_ABATE_REVENUE_RECTRX_ID NUMBER DEFAULT NULL,
48 X_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL,
49 X_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
50 X_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
51 X_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
52 X_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
53 X_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
54 X_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
55 X_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
56 X_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
57 X_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
58 X_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
59 X_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
60 X_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
61 X_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
62 X_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
63 X_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
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 X_calling_sequence VARCHAR2
70 ) IS
71 CURSOR C IS
72 SELECT *
73 FROM JL_BR_AR_COLLECTION_DOCS
74 WHERE rowid = X_Rowid
75 FOR UPDATE of document_id NOWAIT;
76 Recinfo C%ROWTYPE;
77
78 current_calling_sequence VARCHAR2(2000);
79 debug_info VARCHAR2(100);
80
81 BEGIN
82 -- Update the calling sequence
83 --
84 current_calling_sequence := 'JL_BR_AR_COLLECTION_DOC2_PKG.LOCK_ROW<-' ||
85 X_calling_sequence;
86 debug_info := 'Open cursor C';
87 OPEN C;
88 debug_info := 'Fetch cursor C';
89 FETCH C INTO Recinfo;
90 if (C%NOTFOUND) then
91 debug_info := 'Close cursor C - DATA NOTFOUND';
92 CLOSE C;
93 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
94 APP_EXCEPTION.Raise_Exception;
95 end if;
96 debug_info := 'Close cursor C';
97 CLOSE C;
98 if (
99 (Recinfo.document_id = X_document_id)
100 AND (Recinfo.bordero_id = X_bordero_id)
101 AND (Recinfo.payment_schedule_id = X_payment_schedule_id)
102 AND (Recinfo.document_status = X_document_status)
103 AND (Recinfo.origin_type = X_origin_type)
104 AND (Recinfo.due_date = X_due_date)
105 AND (Recinfo.selection_date = X_selection_date)
106 AND (Recinfo.portfolio_code = X_portfolio_code)
107 AND (Recinfo.batch_source_id = X_batch_source_id)
108 AND (Recinfo.receipt_method_id = X_receipt_method_id)
109 AND (Recinfo.customer_trx_id = X_customer_trx_id)
110 AND (Recinfo.terms_sequence_number = X_terms_sequence_number)
111 AND (Recinfo.document_type = X_document_type)
112 AND ( (Recinfo.BANK_ACCT_USE_ID = X_BANK_ACCT_USE_ID)
113 OR ( (Recinfo.BANK_ACCT_USE_ID IS NULL)
114 AND (X_BANK_ACCT_USE_ID IS NULL)))
115 AND ( (Recinfo.PREVIOUS_DOC_STATUS = X_PREVIOUS_DOC_STATUS)
116 OR ( (Recinfo.PREVIOUS_DOC_STATUS IS NULL)
117 AND (X_PREVIOUS_DOC_STATUS IS NULL)))
118 AND ( (Recinfo.OUR_NUMBER = X_OUR_NUMBER)
119 OR ( (Recinfo.OUR_NUMBER IS NULL)
120 AND (X_OUR_NUMBER IS NULL)))
121 AND ( (Recinfo.BANK_USE = X_BANK_USE)
122 OR ( (Recinfo.BANK_USE IS NULL)
123 AND (X_BANK_USE IS NULL)))
124 AND ( (Recinfo.COLLECTOR_BANK_PARTY_ID = X_COLLECTOR_BANK_PARTY_ID)
125 OR ( (Recinfo.COLLECTOR_BANK_PARTY_ID IS NULL)
126 AND (X_COLLECTOR_BANK_PARTY_ID IS NULL)))
127 AND ( (Recinfo.COLLECTOR_BRANCH_PARTY_ID = X_COLLECTOR_BRANCH_PARTY_ID)
128 OR ( (Recinfo.COLLECTOR_BRANCH_PARTY_ID IS NULL)
129 AND (X_COLLECTOR_BRANCH_PARTY_ID IS NULL)))
130 AND ( (Recinfo.FACTORING_RATE = X_FACTORING_RATE)
131 OR ( (Recinfo.FACTORING_RATE IS NULL)
132 AND (X_FACTORING_RATE IS NULL)))
133 AND ( (Recinfo.FACTORING_RATE_PERIOD = X_FACTORING_RATE_PERIOD)
134 OR ( (Recinfo.FACTORING_RATE_PERIOD IS NULL)
135 AND (X_FACTORING_RATE_PERIOD IS NULL)))
136 AND ( (Recinfo.FACTORING_AMOUNT = X_FACTORING_AMOUNT)
137 OR ( (Recinfo.FACTORING_AMOUNT IS NULL)
138 AND (X_FACTORING_AMOUNT IS NULL)))
139 AND ( (Recinfo.FACTORING_DATE = X_FACTORING_DATE)
140 OR ( (Recinfo.FACTORING_DATE IS NULL)
141 AND (X_FACTORING_DATE IS NULL)))
142 AND ( (Recinfo.CANCELLATION_DATE = X_CANCELLATION_DATE)
143 OR ( (Recinfo.CANCELLATION_DATE IS NULL)
144 AND (X_CANCELLATION_DATE IS NULL)))
145 AND ( (Recinfo.BANK_INSTRUCTION_CODE1 = X_BANK_INSTRUCTION_CODE1)
146 OR ( (Recinfo.BANK_INSTRUCTION_CODE1 IS NULL)
147 AND (X_BANK_INSTRUCTION_CODE1 IS NULL)))
148 AND ( (Recinfo.BANK_INSTRUCTION_CODE2 = X_BANK_INSTRUCTION_CODE2)
149 OR ( (Recinfo.BANK_INSTRUCTION_CODE2 IS NULL)
150 AND (X_BANK_INSTRUCTION_CODE2 IS NULL)))
151 AND ( (Recinfo.NUM_DAYS_INSTRUCTION = X_NUM_DAYS_INSTRUCTION)
152 OR ( (Recinfo.NUM_DAYS_INSTRUCTION IS NULL)
153 AND (X_NUM_DAYS_INSTRUCTION IS NULL)))
154 AND ( (Recinfo.BANK_CHARGE_AMOUNT = X_BANK_CHARGE_AMOUNT)
155 OR ( (Recinfo.BANK_CHARGE_AMOUNT IS NULL)
156 AND (X_BANK_CHARGE_AMOUNT IS NULL)))
157 AND ( (Recinfo.CASH_CCID = X_CASH_CCID)
158 OR ( (Recinfo.CASH_CCID IS NULL)
159 AND (X_CASH_CCID IS NULL)))
160 AND ( (Recinfo.BANK_CHARGES_CCID = X_BANK_CHARGES_CCID)
161 OR ( (Recinfo.BANK_CHARGES_CCID IS NULL)
162 AND (X_BANK_CHARGES_CCID IS NULL)))
163 AND ( (Recinfo.COLL_ENDORSEMENTS_CCID = X_COLL_ENDORSEMENTS_CCID)
164 OR ( (Recinfo.COLL_ENDORSEMENTS_CCID IS NULL)
165 AND (X_COLL_ENDORSEMENTS_CCID IS NULL)))
166 AND ( (Recinfo.BILLS_COLLECTION_CCID = X_BILLS_COLLECTION_CCID)
167 OR ( (Recinfo.BILLS_COLLECTION_CCID IS NULL)
168 AND (X_BILLS_COLLECTION_CCID IS NULL)))
169 AND ( (Recinfo.CALCULATED_INTEREST_CCID = X_CALCULATED_INTEREST_CCID)
170 OR ( (Recinfo.CALCULATED_INTEREST_CCID IS NULL)
171 AND (X_CALCULATED_INTEREST_CCID IS NULL)))
172 AND ( (Recinfo.INTEREST_WRITEOFF_CCID = X_INTEREST_WRITEOFF_CCID)
173 OR ( (Recinfo.INTEREST_WRITEOFF_CCID IS NULL)
174 AND (X_INTEREST_WRITEOFF_CCID IS NULL)))
175 AND ( (Recinfo.ABATEMENT_WRITEOFF_CCID = X_ABATEMENT_WRITEOFF_CCID)
176 OR ( (Recinfo.ABATEMENT_WRITEOFF_CCID IS NULL)
177 AND (X_ABATEMENT_WRITEOFF_CCID IS NULL)))
178 AND ( (Recinfo.ABATEMENT_REVENUE_CCID = X_ABATEMENT_REVENUE_CCID)
179 OR ( (Recinfo.ABATEMENT_REVENUE_CCID IS NULL)
180 AND (X_ABATEMENT_REVENUE_CCID IS NULL)))
181 AND ( (Recinfo.INTEREST_REVENUE_CCID = X_INTEREST_REVENUE_CCID)
182 OR ( (Recinfo.INTEREST_REVENUE_CCID IS NULL)
183 AND (X_INTEREST_REVENUE_CCID IS NULL)))
184 AND ( (Recinfo.CALCULATED_INTEREST_RECTRX_ID = X_CALCULATED_INT_RECTRX_ID)
185 OR ( (Recinfo.CALCULATED_INTEREST_RECTRX_ID IS NULL)
186 AND (X_CALCULATED_INT_RECTRX_ID IS NULL)))
187 AND ( (Recinfo.INTEREST_WRITEOFF_RECTRX_ID = X_INTEREST_WRITEOFF_RECTRX_ID)
188 OR ( (Recinfo.INTEREST_WRITEOFF_RECTRX_ID IS NULL)
189 AND (X_INTEREST_WRITEOFF_RECTRX_ID IS NULL)))
190 AND ( (Recinfo.INTEREST_REVENUE_RECTRX_ID = X_INTEREST_REVENUE_RECTRX_ID)
191 OR ( (Recinfo.INTEREST_REVENUE_RECTRX_ID IS NULL)
192 AND (X_INTEREST_REVENUE_RECTRX_ID IS NULL)))
193 AND ( (Recinfo.ABATEMENT_WRITEOFF_RECTRX_ID = X_ABATEMENT_WRITEOFF_RECTRX_ID)
194 OR ( (Recinfo.ABATEMENT_WRITEOFF_RECTRX_ID IS NULL)
195 AND (X_ABATEMENT_WRITEOFF_RECTRX_ID IS NULL)))
196 AND ( (Recinfo.ABATE_REVENUE_RECTRX_ID = X_ABATE_REVENUE_RECTRX_ID)
197 OR ( (Recinfo.ABATE_REVENUE_RECTRX_ID IS NULL)
198 AND (X_ABATE_REVENUE_RECTRX_ID IS NULL)))
199 AND ( (Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
200 OR ( (Recinfo.ATTRIBUTE_CATEGORY IS NULL)
201 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
202 AND ( (Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
203 OR ( (Recinfo.ATTRIBUTE1 IS NULL)
204 AND (X_ATTRIBUTE1 IS NULL)))
205 AND ( (Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
206 OR ( (Recinfo.ATTRIBUTE2 IS NULL)
207 AND (X_ATTRIBUTE2 IS NULL)))
208 AND ( (Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
209 OR ( (Recinfo.ATTRIBUTE3 IS NULL)
210 AND (X_ATTRIBUTE3 IS NULL)))
211 AND ( (Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
212 OR ( (Recinfo.ATTRIBUTE4 IS NULL)
213 AND (X_ATTRIBUTE4 IS NULL)))
214 AND ( (Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
215 OR ( (Recinfo.ATTRIBUTE5 IS NULL)
216 AND (X_ATTRIBUTE5 IS NULL)))
217 AND ( (Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
218 OR ( (Recinfo.ATTRIBUTE6 IS NULL)
219 AND (X_ATTRIBUTE6 IS NULL)))
220 AND ( (Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
221 OR ( (Recinfo.ATTRIBUTE7 IS NULL)
222 AND (X_ATTRIBUTE7 IS NULL)))
223 AND ( (Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
224 OR ( (Recinfo.ATTRIBUTE8 IS NULL)
225 AND (X_ATTRIBUTE8 IS NULL)))
226 AND ( (Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
227 OR ( (Recinfo.ATTRIBUTE9 IS NULL)
228 AND (X_ATTRIBUTE9 IS NULL)))
229 AND ( (Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
230 OR ( (Recinfo.ATTRIBUTE10 IS NULL)
231 AND (X_ATTRIBUTE10 IS NULL)))
232 AND ( (Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
233 OR ( (Recinfo.ATTRIBUTE11 IS NULL)
234 AND (X_ATTRIBUTE11 IS NULL)))
235 AND ( (Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
236 OR ( (Recinfo.ATTRIBUTE12 IS NULL)
237 AND (X_ATTRIBUTE12 IS NULL)))
238 AND ( (Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
239 OR ( (Recinfo.ATTRIBUTE13 IS NULL)
240 AND (X_ATTRIBUTE13 IS NULL)))
241 AND ( (Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
242 OR ( (Recinfo.ATTRIBUTE14 IS NULL)
243 AND (X_ATTRIBUTE14 IS NULL)))
244 AND ( (Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
245 OR ( (Recinfo.ATTRIBUTE15 IS NULL)
246 AND (X_ATTRIBUTE15 IS NULL)))
247 ) then
248 return;
249 else
250 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
251 APP_EXCEPTION.Raise_Exception;
252 end if;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 IF (SQLCODE <> -20001) THEN
257 IF (SQLCODE = -54) THEN
258 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
259 ELSE
260 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
261 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
262 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
263 FND_MESSAGE.SET_TOKEN('PARAMETERS','document_id = ' ||
264 X_document_id );
265 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
266 END IF;
267 END IF;
268 APP_EXCEPTION.RAISE_EXCEPTION;
269
270 END Lock_Row;
271
272 PROCEDURE Delete_Row (X_Rowid VARCHAR2,
273 X_calling_sequence IN VARCHAR2
274 ) IS
275 current_calling_sequence VARCHAR2(2000);
276 debug_info VARCHAR2(100);
277 BEGIN
278 -- Update the calling sequence
279 --
280 current_calling_sequence := 'JL_BR_AR_COLLECTION_DOC2_PKG.DELETE_ROW<-' ||
281 X_calling_sequence;
282 debug_info := 'Delete from JL_BR_AR_COLLECTION_DOCS';
283 DELETE FROM JL_BR_AR_COLLECTION_DOCS
284 WHERE rowid = X_Rowid;
285
286 if (SQL%NOTFOUND) then
287 Raise NO_DATA_FOUND;
288 end if;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF (SQLCODE <> -20001) THEN
293 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
294 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
295 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
296 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
297 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
298 END IF;
299 APP_EXCEPTION.RAISE_EXCEPTION;
300
301 END Delete_Row;
302
303
304 END JL_BR_AR_COLLECTION_DOC2_PKG;
[Home] [Help]