1 PACKAGE BODY JL_BR_AR_OCCURRENCE_DOCS_PKG1 as
2 /* $Header: jlbrri2b.pls 120.2 2005/02/23 23:28:30 vsidhart noship $ */
3
4 PROCEDURE Lock_Row(X_Rowid 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 DEFAULT NULL,
12 X_Occurrence_Status VARCHAR2 DEFAULT NULL,
13 X_Original_Remittance_Media VARCHAR2 DEFAULT NULL,
14 X_Remittance_Media VARCHAR2 DEFAULT NULL,
15 X_Selection_Date DATE DEFAULT NULL,
16 X_Bordero_Id NUMBER DEFAULT NULL,
17 X_Portfolio_Code NUMBER DEFAULT NULL,
18 X_Trade_Note_Number VARCHAR2 DEFAULT NULL,
19 X_Due_Date DATE DEFAULT NULL,
20 X_Document_Amount NUMBER DEFAULT NULL,
21 X_Bank_Instruction_Code1 NUMBER DEFAULT NULL,
22 X_Bank_Instruction_Code2 NUMBER DEFAULT NULL,
23 X_Num_Days_Instruction NUMBER DEFAULT NULL,
24 X_Interest_Percent NUMBER DEFAULT NULL,
25 X_Interest_Period NUMBER DEFAULT NULL,
26 X_Interest_Amount NUMBER DEFAULT NULL,
27 X_Grace_Days NUMBER DEFAULT NULL,
28 X_Discount_Limit_Date DATE DEFAULT NULL,
29 X_Discount_Amount NUMBER DEFAULT NULL,
30 X_Customer_Id NUMBER DEFAULT NULL,
31 X_Site_Use_Id NUMBER DEFAULT NULL,
32 X_Abatement_Amount NUMBER DEFAULT NULL,
33 X_Flag_Post_Gl VARCHAR2 DEFAULT NULL,
34 X_Gl_Date DATE DEFAULT NULL,
35 X_Gl_Posted_Date DATE DEFAULT NULL,
36 X_Endorsement_Credit_Ccid NUMBER DEFAULT NULL,
37 X_Endorsement_Debit_Ccid NUMBER DEFAULT NULL,
38 X_Endorsement_Debit_Amount NUMBER DEFAULT NULL,
39 X_Endorsement_Credit_Amount NUMBER DEFAULT NULL,
40 X_Bank_Charge_Amount NUMBER DEFAULT NULL,
41 X_Bank_Charges_Credit_Ccid NUMBER DEFAULT NULL,
42 X_Bank_Charges_Debit_Ccid NUMBER DEFAULT NULL,
43 X_Bank_Charges_Credit_Amount NUMBER DEFAULT NULL,
44 X_Bank_Charges_Debit_Amount NUMBER DEFAULT NULL,
45 X_Request_Id NUMBER DEFAULT NULL,
46 X_Return_Info VARCHAR2 DEFAULT NULL,
47 X_Interest_Indicator VARCHAR2 DEFAULT NULL,
48 X_Return_Request_Id NUMBER DEFAULT NULL,
49 X_Gl_Cancel_Date DATE DEFAULT NULL,
50 X_Attribute_Category VARCHAR2 DEFAULT NULL,
51 X_Attribute1 VARCHAR2 DEFAULT NULL,
52 X_Attribute2 VARCHAR2 DEFAULT NULL,
53 X_Attribute3 VARCHAR2 DEFAULT NULL,
54 X_Attribute4 VARCHAR2 DEFAULT NULL,
55 X_Attribute5 VARCHAR2 DEFAULT NULL,
56 X_Attribute6 VARCHAR2 DEFAULT NULL,
57 X_Attribute7 VARCHAR2 DEFAULT NULL,
58 X_Attribute8 VARCHAR2 DEFAULT NULL,
59 X_Attribute9 VARCHAR2 DEFAULT NULL,
60 X_Attribute10 VARCHAR2 DEFAULT NULL,
61 X_Attribute11 VARCHAR2 DEFAULT NULL,
62 X_Attribute12 VARCHAR2 DEFAULT NULL,
63 X_Attribute13 VARCHAR2 DEFAULT NULL,
64 X_Attribute14 VARCHAR2 DEFAULT NULL,
65 X_Attribute15 VARCHAR2 DEFAULT NULL,
66 X_Last_Update_Date DATE,
67 X_Last_Updated_By NUMBER,
68 X_Creation_Date DATE DEFAULT NULL,
69 X_Created_By NUMBER,
70 X_Last_Update_Login NUMBER DEFAULT NULL,
71 X_calling_sequence VARCHAR2) IS
72 --
73 CURSOR C IS
74 SELECT *
75 FROM JL_BR_AR_OCCURRENCE_DOCS
76 WHERE rowid = X_Rowid
77 FOR UPDATE of Document_Id NOWAIT;
78 Recinfo C%ROWTYPE;
79 --
80 current_calling_sequence VARCHAR2(2000);
81 debug_info VARCHAR2(100);
82
83 BEGIN
84 -- Update the calling sequence
85 current_calling_sequence := 'JL_BR_AR_OCCURRENCE_DOCS_PKG.LOCK_ROW<-' ||
86 X_calling_sequence;
87 debug_info := 'Open cursor C';
88 OPEN C;
89 debug_info := 'Fetch cursor C';
90 FETCH C INTO Recinfo;
91 IF (C%NOTFOUND) THEN
92 debug_info := 'Close cursor C - DATA NOTFOUND';
93 CLOSE C;
94 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
95 APP_EXCEPTION.Raise_Exception;
96 END IF;
97 debug_info := 'Close cursor C';
98 CLOSE C;
99 IF ((Recinfo.document_id = X_Document_Id)
100 AND (Recinfo.occurrence_id = X_Occurrence_Id)
101 AND (Recinfo.bank_occurrence_code = X_Bank_Occurrence_Code)
102 --AND (Recinfo.bank_number = X_Bank_Number)
103 AND (Recinfo.bank_party_id = X_bank_party_id)
104 AND (Recinfo.bank_occurrence_type = X_Bank_Occurrence_Type)
105 AND ((Recinfo.occurrence_date = X_Occurrence_Date)
106 OR ((Recinfo.occurrence_date IS NULL)
107 AND (X_Occurrence_Date IS NULL)))
108 AND ((Recinfo.occurrence_status = X_Occurrence_Status)
109 OR ((Recinfo.occurrence_status IS NULL)
110 AND (X_Occurrence_Status IS NULL)))
111 AND ((Recinfo.original_remittance_media = X_Original_Remittance_Media)
112 OR ((Recinfo.original_remittance_media IS NULL)
113 AND (X_Original_Remittance_Media IS NULL)))
114 AND ((Recinfo.remittance_media = X_Remittance_Media)
115 OR ((Recinfo.remittance_media IS NULL)
116 AND (X_Remittance_Media IS NULL)))
117 AND ((Recinfo.selection_date = X_Selection_Date)
118 OR ((Recinfo.selection_date IS NULL)
119 AND (X_Selection_Date IS NULL)))
120 AND ((Recinfo.bordero_id = X_Bordero_Id)
121 OR ((Recinfo.bordero_id IS NULL)
122 AND (X_Bordero_Id IS NULL)))
123 AND ((Recinfo.portfolio_code = X_Portfolio_Code)
124 OR ((Recinfo.portfolio_code IS NULL)
125 AND (X_Portfolio_Code IS NULL)))
126 AND ((Recinfo.trade_note_number = X_Trade_Note_Number)
127 OR ((Recinfo.trade_note_number IS NULL)
128 AND (X_Trade_Note_Number IS NULL)))
129 AND ((Recinfo.due_date = X_Due_Date)
130 OR ((Recinfo.due_date IS NULL)
131 AND (X_Due_Date IS NULL)))
132 AND ((Recinfo.document_amount = X_Document_Amount)
133 OR ((Recinfo.document_amount IS NULL)
134 AND (X_Document_Amount IS NULL)))
135 AND ((Recinfo.bank_instruction_code1 = X_Bank_Instruction_Code1)
136 OR ((Recinfo.bank_instruction_code1 IS NULL)
137 AND (X_Bank_Instruction_Code1 IS NULL)))
138 AND ((Recinfo.bank_instruction_code2 = X_Bank_Instruction_Code2)
139 OR ((Recinfo.bank_instruction_code2 IS NULL)
140 AND (X_Bank_Instruction_Code2 IS NULL)))
141 AND ((Recinfo.num_days_instruction = X_Num_Days_Instruction)
142 OR ((Recinfo.num_days_instruction IS NULL)
143 AND (X_Num_Days_Instruction IS NULL)))
144 AND ((Recinfo.interest_percent = X_Interest_Percent)
145 OR ((Recinfo.interest_percent IS NULL)
146 AND (X_Interest_Percent IS NULL)))
147 AND ((Recinfo.interest_period = X_Interest_Period)
148 OR ((Recinfo.interest_period IS NULL)
149 AND (X_Interest_Period IS NULL)))
150 AND ((Recinfo.interest_amount = X_Interest_Amount)
151 OR ((Recinfo.interest_amount IS NULL)
152 AND (X_Interest_Amount IS NULL)))
153 AND ((Recinfo.grace_days = X_Grace_Days)
154 OR ((Recinfo.grace_days IS NULL)
155 AND (X_Grace_Days IS NULL)))
156 AND ((Recinfo.discount_limit_date = X_Discount_Limit_Date)
157 OR ((Recinfo.discount_limit_date IS NULL)
158 AND (X_Discount_Limit_Date IS NULL)))
159 AND ((Recinfo.discount_amount = X_Discount_Amount)
160 OR ((Recinfo.discount_amount IS NULL)
161 AND (X_Discount_Amount IS NULL)))
162 AND ((Recinfo.customer_id = X_Customer_Id)
163 OR ((Recinfo.customer_id IS NULL)
164 AND (X_Customer_Id IS NULL)))
165 AND ((Recinfo.site_use_id = X_Site_Use_Id)
166 OR ((Recinfo.site_use_id IS NULL)
167 AND (X_Site_Use_Id IS NULL)))
168 AND ((Recinfo.abatement_amount = X_Abatement_Amount)
169 OR ((Recinfo.abatement_amount IS NULL)
170 AND (X_Abatement_Amount IS NULL)))
171 AND ((Recinfo.flag_post_gl = X_Flag_Post_Gl)
172 OR ((Recinfo.flag_post_gl IS NULL)
173 AND (X_Flag_Post_Gl IS NULL)))
174 AND ((Recinfo.gl_date = X_Gl_Date)
175 OR ((Recinfo.gl_date IS NULL)
176 AND (X_Gl_Date IS NULL)))
177 AND ((Recinfo.gl_posted_date = X_Gl_Posted_Date)
178 OR ((Recinfo.gl_posted_date IS NULL)
179 AND (X_Gl_Posted_Date IS NULL)))
180 AND ((Recinfo.endorsement_credit_ccid = X_Endorsement_Credit_Ccid)
181 OR ((Recinfo.endorsement_credit_ccid IS NULL)
182 AND (X_Endorsement_Credit_Ccid IS NULL)))
183 AND ((Recinfo.endorsement_debit_ccid = X_Endorsement_Debit_Ccid)
184 OR ((Recinfo.endorsement_debit_ccid IS NULL)
185 AND (X_Endorsement_Debit_Ccid IS NULL)))
186 AND ((Recinfo.endorsement_debit_amount = X_Endorsement_Debit_Amount)
187 OR ((Recinfo.endorsement_debit_amount IS NULL)
188 AND (X_Endorsement_Debit_Amount IS NULL)))
189 AND ((Recinfo.endorsement_credit_amount = X_Endorsement_Credit_Amount)
190 OR ((Recinfo.endorsement_credit_amount IS NULL)
191 AND (X_Endorsement_Credit_Amount IS NULL)))
192 AND ((Recinfo.bank_charge_amount = X_Bank_Charge_Amount)
193 OR ((Recinfo.bank_charge_amount IS NULL)
194 AND (X_Bank_Charge_Amount IS NULL)))
195 AND ((Recinfo.bank_charges_credit_ccid = X_Bank_Charges_Credit_Ccid)
196 OR ((Recinfo.bank_charges_credit_ccid IS NULL)
197 AND (X_Bank_Charges_Credit_Ccid IS NULL)))
198 AND ((Recinfo.bank_charges_debit_ccid = X_Bank_Charges_Debit_Ccid)
199 OR ((Recinfo.bank_charges_debit_ccid IS NULL)
200 AND (X_Bank_Charges_Debit_Ccid IS NULL)))
201 AND ((Recinfo.bank_charges_credit_amount = X_Bank_Charges_Credit_Amount)
202 OR ((Recinfo.bank_charges_credit_amount IS NULL)
203 AND (X_Bank_Charges_Credit_Amount IS NULL)))
204 AND ((Recinfo.bank_charges_debit_amount = X_Bank_Charges_Debit_Amount)
205 OR ((Recinfo.bank_charges_debit_amount IS NULL)
206 AND (X_Bank_Charges_Debit_Amount IS NULL)))
207 AND ((Recinfo.request_id = X_Request_Id)
208 OR ((Recinfo.request_id IS NULL)
209 AND (X_Request_Id IS NULL)))
210 AND ((Recinfo.return_info = X_Return_Info)
211 OR ((Recinfo.return_info IS NULL)
212 AND (X_Return_Info IS NULL)))
213 AND ((Recinfo.interest_indicator = X_Interest_Indicator)
214 OR ((Recinfo.interest_indicator IS NULL)
215 AND (X_Interest_Indicator IS NULL)))
216 AND ((Recinfo.return_request_id = X_Return_Request_Id)
217 OR ((Recinfo.return_request_id IS NULL)
218 AND (X_Return_Request_Id IS NULL)))
219 AND ((Recinfo.gl_cancel_date = X_Gl_Cancel_Date)
220 OR ((Recinfo.gl_cancel_date IS NULL)
221 AND (X_Gl_Cancel_Date IS NULL)))
222 AND ((Recinfo.attribute_category = X_Attribute_Category)
223 OR ((Recinfo.attribute_category IS NULL)
224 AND (X_Attribute_Category IS NULL)))
225 AND ((Recinfo.attribute1 = X_Attribute1)
226 OR ((Recinfo.attribute1 IS NULL)
227 AND (X_Attribute1 IS NULL)))
228 AND ((Recinfo.attribute2 = X_Attribute2)
229 OR ((Recinfo.attribute2 IS NULL)
230 AND (X_Attribute2 IS NULL)))
231 AND ((Recinfo.attribute3 = X_Attribute3)
232 OR ((Recinfo.attribute3 IS NULL)
233 AND (X_Attribute3 IS NULL)))
234 AND ((Recinfo.attribute4 = X_Attribute4)
235 OR ((Recinfo.attribute4 IS NULL)
236 AND (X_Attribute4 IS NULL)))
237 AND ((Recinfo.attribute5 = X_Attribute5)
238 OR ((Recinfo.attribute5 IS NULL)
239 AND (X_Attribute5 IS NULL)))
240 AND ((Recinfo.attribute6 = X_Attribute6)
241 OR ((Recinfo.attribute6 IS NULL)
242 AND (X_Attribute6 IS NULL)))
243 AND ((Recinfo.attribute7 = X_Attribute7)
244 OR ((Recinfo.attribute7 IS NULL)
245 AND (X_Attribute7 IS NULL)))
246 AND ((Recinfo.attribute8 = X_Attribute8)
247 OR ((Recinfo.attribute8 IS NULL)
248 AND (X_Attribute8 IS NULL)))
249 AND ((Recinfo.attribute9 = X_Attribute9)
250 OR ((Recinfo.attribute9 IS NULL)
251 AND (X_Attribute9 IS NULL)))
252 AND ((Recinfo.attribute10 = X_Attribute10)
253 OR ((Recinfo.attribute10 IS NULL)
254 AND (X_Attribute10 IS NULL)))
255 AND ((Recinfo.attribute11 = X_Attribute11)
256 OR ((Recinfo.attribute11 IS NULL)
257 AND (X_Attribute11 IS NULL)))
258 AND ((Recinfo.attribute12 = X_Attribute12)
259 OR ((Recinfo.attribute12 IS NULL)
260 AND (X_Attribute12 IS NULL)))
261 AND ((Recinfo.attribute13 = X_Attribute13)
262 OR ((Recinfo.attribute13 IS NULL)
263 AND (X_Attribute13 IS NULL)))
264 AND ((Recinfo.attribute14 = X_Attribute14)
265 OR ((Recinfo.attribute14 IS NULL)
266 AND (X_Attribute14 IS NULL)))
267 AND ((Recinfo.attribute15 = X_Attribute15)
268 OR ((Recinfo.attribute15 IS NULL)
269 AND (X_Attribute15 IS NULL)))
270 AND ((Recinfo.creation_date = X_Creation_Date)
271 OR ((Recinfo.creation_date IS NULL)
272 AND (X_Creation_Date IS NULL)))
273 AND ((Recinfo.created_by = X_Created_By)
274 OR ((Recinfo.created_by IS NULL)
275 AND (X_Created_By IS NULL)))) THEN
276 return;
277 ELSE
278 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
279 APP_EXCEPTION.Raise_Exception;
280 END IF;
281 --
282 EXCEPTION
283 WHEN OTHERS THEN
284 IF (SQLCODE <> -20001) THEN
285 IF (SQLCODE = -54) THEN
286 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
287 ELSE
288 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
289 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
290 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
291 FND_MESSAGE.SET_TOKEN('PARAMETERS','DOCUMENT_ID = ' ||
292 X_Document_Id);
293 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
294 END IF;
295 END IF;
296 APP_EXCEPTION.RAISE_EXCEPTION;
297 --
298 END Lock_Row;
299
300 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
301 X_calling_sequence IN VARCHAR2) IS
302 --
303 current_calling_sequence VARCHAR2(2000);
304 debug_info VARCHAR2(100);
305 BEGIN
306 -- Update the calling sequence
307 --
308 current_calling_sequence := 'JL_BR_AR_OCCURRENCE_DOCS_PKG.DELETE_ROW<-' ||
309 X_calling_sequence;
310 debug_info := 'Delete from JL_BR_AR_OCCURRENCE_DOCS';
311 DELETE FROM JL_BR_AR_OCCURRENCE_DOCS
312 WHERE rowid = X_Rowid;
313 --
314 IF (SQL%NOTFOUND) THEN
315 RAISE NO_DATA_FOUND;
316 END IF;
317 --
318 EXCEPTION
319 WHEN OTHERS THEN
320 IF (SQLCODE <> -20001) THEN
321 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
325 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
322 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
323 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
324 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
326 END IF;
327 APP_EXCEPTION.RAISE_EXCEPTION;
328 --
329 END Delete_Row;
330
331 END JL_BR_AR_OCCURRENCE_DOCS_PKG1;
[Home] [Help]