[Home] [Help]
PACKAGE BODY: APPS.XNB_BILL_SUMMARIES_PKG
Source
1 PACKAGE BODY XNB_BILL_SUMMARIES_PKG as
2 /* $Header: XNBTBSB.pls 120.0 2005/05/30 13:45:26 appldev noship $ */
3
4 -- Start of Comments
5 -- Package name : XNB_BILL_SUMMARIES_PKG
6 -- Purpose : Defines public APIs to insert/update records into XNB BILL SUMMARIES schema
7 -- NOTE :
8 -- History :
9 -- DATE AUTHOR COMMENTS
10 -- 24-Aug-2004 dbhagat Create table handler
11 -- 04-Feb-2005 DPUTHIYE Added p_api_version parameter to APIs. (Fixed bug 4159395).
12 -- 14-Apr-2005 dbhagat Removed Bill Cycle End Date a mandatory for update. (Fixed bug 4300093).
13 -- End of Comments
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'XNB_BILL_SUMMARIES_PKG';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'xnbtbsb.pls';
17
18
19 -- Start of comments
20 -- API name : Insert_Row_Batch
21 -- Type : Public
22 -- Pre-reqs : None.
23 -- Function : Inserts the given bill summary rows into the XNB table
24 -- . XNB_BILL_SUMMARIES
25 -- Assumptions :
26 -- All records whose
27 -- 1. Account Number must exit in eBusiness suite database schema
28 -- HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER.
29 -- 2. Account Number, Bill Number, Bill Cycle End Date and Billing
30 -- Vendor Name are required.
31 -- 3. Bill Number, Account Number and Billing Vendor Name combined
32 -- must be unique for insertion.
33 -- 4. For all above validations, in case of failure, only those records
34 -- will be rejected and rest inserted. Any other validation failure will
35 -- not insert any record (All or none are inserted/ rolled back).
36 -- 5. All data inserted or updated are case sensitive.
37 -- Parameters :
38 -- IN : p_api_version IN NUMBER Required
39 -- IN : p_bill_summaries IN bill_summaries_table
40 -- -- The table of bill summary records to be inserted.
41 -- OUT : x_return_status OUT NOCOPY VARCHAR2
42 -- -- Execution status returned.
43 -- -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_SUCCESS
44 -- OUT : x_msg_data OUT NOCOPY VARCHAR2
45 -- -- Error message returned.
46 -- Version : Current version 1.0
47 -- Initial version 1.0
48 -- End of comments
49 PROCEDURE Insert_Row_Batch(
50 p_api_version IN NUMBER,
51 p_bill_summaries IN bill_summaries_table,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_msg_data OUT NOCOPY VARCHAR2
54 )
55 IS
56 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
57 --Change: Added parameter p_api_version to comply to Business API standards.
58 -- Added Std API documentation header for the API.
59 -- Moved Assumptions from below this comment to API doc Header
60 --Other Files Impact: None.
61 l_api_version CONSTANT NUMBER := 1.0; -- added to fix Bug#:4159395
62 l_api_name CONSTANT VARCHAR2(20) := 'Insert_Row_Batch'; -- added to fix Bug#:4159395
63
64 null_excep EXCEPTION; -- exception declaration
65
66 l_bs_all_rec_count NUMBER := 0; -- Total bill summaries recpunt received
67 l_bs_valid_rec_count NUMBER := 0; -- Valid bill summary record to be inserted
68 l_bs_valid_rec_flag varchar2(1) := null; -- Validity of a bill summary record is true
69 --l_err_msg_count NUMBER := 0; -- Total number of error found
70
71 l_num_init NUMBER := 0; -- intialize varray variable of NUMBER type
72 l_var_init varchar2(5) := null; -- intialize varray variable of varchar2 type
73
74 tmp_acc_num varchar2(30) := null; -- account number to validate in eBusiness DB
75 tmp_acc_num2 varchar2(30) := null; -- account number to hold in temp account number
76
77 t_acc_num varchar2(30) := null; -- account number to hold in temp account number
78 t_bill_num varchar2(30) := null; -- bill number to hold in temp bill number
79 t_bill_vendor varchar2(30) := null; -- billing vendor name to hold in temp billing vendor name
80 t_uniq_val VARCHAR2(1) := null;
81
82 -- declaring varray to hold bill summary records
83 l_bill_summary_id v_number;
84 l_account_number v_var30;
85 l_total_amount_due v_var30;
86 l_adjustments v_var30;
87 l_unresolved_disputes v_var30;
88 l_bill_number v_var30;
89 l_bill_cycle_start_date v_date;
90 l_bill_cycle_end_date v_date;
91 l_due_date v_date;
92 l_new_charges v_var30;
93 l_payment v_var30;
94 l_balance v_var30;
95 l_previous_balance v_var30;
96 l_billing_vendor_name v_var240;
97 l_bill_location_url v_var240;
98 l_due_now v_var30;
99 l_created_by v_number;
100 l_creation_date v_date;
101 l_last_updated_by v_number;
102 l_last_update_date v_date;
103 l_last_update_login v_number;
104 l_object_version_number v_number;
105 l_attribute_category v_var30;
106 l_attribute1 v_var150;
107 l_attribute2 v_var150;
108 l_attribute3 v_var150;
109 l_attribute4 v_var150;
110 l_attribute5 v_var150;
111 l_attribute6 v_var150;
112 l_attribute7 v_var150;
113 l_attribute8 v_var150;
114 l_attribute9 v_var150;
115 l_attribute10 v_var150;
116 l_attribute11 v_var150;
117 l_attribute12 v_var150;
118 l_attribute13 v_var150;
119 l_attribute14 v_var150;
120 l_attribute15 v_var150;
121
122 Cursor c_get_acc_num is
123 select ACCOUNT_NUMBER
124 from HZ_CUST_ACCOUNTS
125 where ACCOUNT_NUMBER = tmp_acc_num;
126
127 Cursor c_check_uniq_constrain is
128 select 1
129 from XNB_BILL_SUMMARIES
130 where ACCOUNT_NUMBER = t_acc_num
131 and BILL_NUMBER = t_bill_num
132 and BILLING_VENDOR_NAME = t_bill_vendor;
133
134 BEGIN
135 SAVEPOINT BULK_INSERT;
136
137 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
138 --Change: The savepoint above has been moved from a place below to the cur loc.
139 --Change: The following check was added to check API compatibility
140 --Other Files Impact: None.
141
142 -- Standard call to check for call compatibility
143 IF NOT FND_API.Compatible_API_Call(
144 l_api_version,
145 p_api_version,
146 l_api_name,
147 G_PKG_NAME) THEN
148 RAISE FND_API.G_EXC_ERROR;
149 END IF;
150
151 -- Initialize API return status to success and other variables
152 l_bs_all_rec_count := p_bill_summaries.last;
153 l_bs_valid_rec_flag := 'T';
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155 x_msg_data := null;
156
157 -- Intialize all local varray and extend to intialize for all records
158 l_bill_summary_id :=v_number(l_num_init);
159 l_bill_summary_id.EXTEND(l_bs_all_rec_count,1);
160 l_account_number :=v_var30(l_var_init);
161 l_account_number.EXTEND(l_bs_all_rec_count,1);
162 l_total_amount_due :=v_var30(l_var_init);
163 l_total_amount_due.EXTEND(l_bs_all_rec_count,1);
164 l_adjustments :=v_var30(l_var_init);
165 l_adjustments.EXTEND(l_bs_all_rec_count,1);
166 l_unresolved_disputes :=v_var30(l_var_init);
167 l_unresolved_disputes.EXTEND(l_bs_all_rec_count,1);
168 l_bill_number :=v_var30(l_var_init);
169 l_bill_number.EXTEND(l_bs_all_rec_count,1);
170 l_bill_cycle_start_date :=v_date(null);
171 l_bill_cycle_start_date.EXTEND(l_bs_all_rec_count,1);
172 l_bill_cycle_end_date :=v_date(null);
173 l_bill_cycle_end_date.EXTEND(l_bs_all_rec_count,1);
174 l_due_date :=v_date(null);
175 l_due_date.EXTEND(l_bs_all_rec_count,1);
176 l_new_charges :=v_var30(l_var_init);
177 l_new_charges.EXTEND(l_bs_all_rec_count,1);
178 l_payment :=v_var30(l_var_init);
179 l_payment.EXTEND(l_bs_all_rec_count,1);
180 l_balance :=v_var30(l_var_init);
181 l_balance.EXTEND(l_bs_all_rec_count,1);
182 l_previous_balance :=v_var30(l_var_init);
183 l_previous_balance.EXTEND(l_bs_all_rec_count,1);
184 l_billing_vendor_name :=v_var240(l_var_init);
185 l_billing_vendor_name.EXTEND(l_bs_all_rec_count,1);
186 l_bill_location_url :=v_var240(l_var_init);
187 l_bill_location_url.EXTEND(l_bs_all_rec_count,1);
188 l_due_now :=v_var30(l_var_init);
189 l_due_now.EXTEND(l_bs_all_rec_count,1);
190 l_created_by :=v_number(l_num_init);
191 l_created_by.EXTEND(l_bs_all_rec_count,1);
192 l_creation_date :=v_date(null);
193 l_creation_date.EXTEND(l_bs_all_rec_count,1);
194 l_last_updated_by :=v_number(l_num_init);
195 l_last_updated_by.EXTEND(l_bs_all_rec_count,1);
196 l_last_update_date :=v_date(null);
197 l_last_update_date.EXTEND(l_bs_all_rec_count,1);
198 l_last_update_login :=v_number(l_num_init);
199 l_last_update_login.EXTEND(l_bs_all_rec_count,1);
200 l_object_version_number :=v_number(l_num_init);
201 l_object_version_number.EXTEND(l_bs_all_rec_count,1);
202 l_attribute_category :=v_var30(l_var_init);
203 l_attribute_category.EXTEND(l_bs_all_rec_count,1);
204 l_attribute1 :=v_var150(l_var_init);
205 l_attribute1.EXTEND(l_bs_all_rec_count,1);
206 l_attribute2 :=v_var150(l_var_init);
207 l_attribute2.EXTEND(l_bs_all_rec_count,1);
208 l_attribute3 :=v_var150(l_var_init);
209 l_attribute3.EXTEND(l_bs_all_rec_count,1);
210 l_attribute4 :=v_var150(l_var_init);
211 l_attribute4.EXTEND(l_bs_all_rec_count,1);
212 l_attribute5 :=v_var150(l_var_init);
213 l_attribute5.EXTEND(l_bs_all_rec_count,1);
214 l_attribute6 :=v_var150(l_var_init);
215 l_attribute6.EXTEND(l_bs_all_rec_count,1);
216 l_attribute7 :=v_var150(l_var_init);
217 l_attribute7.EXTEND(l_bs_all_rec_count,1);
218 l_attribute8 :=v_var150(l_var_init);
219 l_attribute8.EXTEND(l_bs_all_rec_count,1);
220 l_attribute9 :=v_var150(l_var_init);
221 l_attribute9.EXTEND(l_bs_all_rec_count,1);
222 l_attribute10 :=v_var150(l_var_init);
223 l_attribute10.EXTEND(l_bs_all_rec_count,1);
224 l_attribute11 :=v_var150(l_var_init);
225 l_attribute11.EXTEND(l_bs_all_rec_count,1);
226 l_attribute12 :=v_var150(l_var_init);
227 l_attribute12.EXTEND(l_bs_all_rec_count,1);
228 l_attribute13 :=v_var150(l_var_init);
229 l_attribute13.EXTEND(l_bs_all_rec_count,1);
230 l_attribute14 :=v_var150(l_var_init);
231 l_attribute14.EXTEND(l_bs_all_rec_count,1);
232 l_attribute15 :=v_var150(l_var_init);
233 l_attribute15.EXTEND(l_bs_all_rec_count,1);
234
235 -- Loop to validate and initialize all records
236 FOR i IN 1..l_bs_all_rec_count LOOP
237 l_bs_valid_rec_flag := 'T';
238
239 -- Record cannot be Null
240 /*
241 IF (p_bill_summaries(i) IS NULL) THEN
242 l_bs_valid_rec_flag := 'F'; -- validity is false
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BS_REC_IS_NULL');
245 fnd_message.SET_TOKEN('REC_NUM',n);
246 END IF;
247 */
248
249 -- Bill Number is mandatory (NOT NULL field)
250 IF (p_bill_summaries(i).BILL_NUMBER IS NULL ) THEN
251 l_bs_valid_rec_flag := 'F'; -- validity is false
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BILL_NUM_IS_NULL');
254 END IF;
255
256 -- Account Number is mandatory (NOT NULL field)
257 IF (p_bill_summaries(i).ACCOUNT_NUMBER IS NULL) THEN
258 l_bs_valid_rec_flag := 'F'; -- validity is false
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_ACC_NUM_IS_NULL');
261 ELSE
262 tmp_acc_num := p_bill_summaries(i).ACCOUNT_NUMBER;
263 tmp_acc_num2 := null;
264
265 -- Fetch account number from HZ_CUST_ACCOUNTS table
266 open c_get_acc_num;
267 begin
268 fetch c_get_acc_num into tmp_acc_num2;
269 if (c_get_acc_num%NOTFOUND OR tmp_acc_num2 IS NULL) then
270 raise no_data_found;
271 end if;
272 exception
273 when no_data_found then
274 l_bs_valid_rec_flag := 'F'; -- validity is false
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 fnd_message.SET_NAME('XNB','XNB_PLS_ACC_NUM_NOT_VALID');
277 fnd_message.SET_TOKEN('ACC_NUM',tmp_acc_num);
278 x_msg_data := x_msg_data || ' ' || fnd_message.GET;
279 --dbms_output.put_line(' no data found= ' || l_bs_valid_rec_flag );
280 end;
281 close c_get_acc_num;
282 -- Account Number must exist in eBusiness suit database
283 --IF (tmp_acc_num2 IS NULL) THEN
284 --END IF;
285 END IF;
286
287 -- Bill Cycle End Date is mandatory (NOT NULL field)
288 IF (p_bill_summaries(i).BILL_CYCLE_END_DATE IS NULL) THEN
289 l_bs_valid_rec_flag := 'F'; -- validity is false
293
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BILL_END_DT_IS_NULL');
292 END IF;
294 -- Bill Vendor Name is mandatory (NOT NULL field)
295 IF (p_bill_summaries(i).BILLING_VENDOR_NAME IS NULL) THEN
296 l_bs_valid_rec_flag := 'F'; -- validity is false
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BILL_VEN_IS_NULL');
299 END IF;
300
301 -- Check for Uniqe Constraints...
302 t_acc_num := p_bill_summaries(i).ACCOUNT_NUMBER;
303 t_bill_num := p_bill_summaries(i).BILL_NUMBER;
304 t_bill_vendor := p_bill_summaries(i).BILLING_VENDOR_NAME;
305 t_uniq_val := null;
306 open c_check_uniq_constrain;
307 begin
308 fetch c_check_uniq_constrain into t_uniq_val;
309 if (c_check_uniq_constrain%FOUND OR t_uniq_val ='1') then
310 l_bs_valid_rec_flag := 'F'; -- validity is false
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 fnd_message.SET_NAME('XNB','XNB_PLS_BILL_SUM_REC_NOT_UNQ');
313 fnd_message.SET_TOKEN('ACC_NUM',t_acc_num);
314 fnd_message.SET_TOKEN('BILL_NUM',t_bill_num);
315 fnd_message.SET_TOKEN('BILL_VEN',t_bill_vendor);
316 x_msg_data := x_msg_data || ' ' || fnd_message.GET;
317 end if;
318 exception
319 when no_data_found then
320 null;
321 end;
322 close c_check_uniq_constrain;
323
324 -- If the bill summary record is valid
325 IF l_bs_valid_rec_flag = 'T' THEN
326 l_bs_valid_rec_count := l_bs_valid_rec_count + 1;
327
328 l_account_number(l_bs_valid_rec_count) := p_bill_summaries(i).ACCOUNT_NUMBER;
329 l_total_amount_due(l_bs_valid_rec_count) := p_bill_summaries(i).TOTAL_AMOUNT_DUE;
330 l_adjustments(l_bs_valid_rec_count) := p_bill_summaries(i).ADJUSTMENTS;
331 l_unresolved_disputes(l_bs_valid_rec_count) := p_bill_summaries(i).UNRESOLVED_DISPUTES;
332 l_bill_number(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_NUMBER;
333 l_bill_cycle_start_date(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_CYCLE_START_DATE;
334 l_bill_cycle_end_date(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_CYCLE_END_DATE;
335 l_due_date(l_bs_valid_rec_count) := p_bill_summaries(i).DUE_DATE;
336 l_new_charges(l_bs_valid_rec_count) := p_bill_summaries(i).NEW_CHARGES;
337 l_payment(l_bs_valid_rec_count) := p_bill_summaries(i).PAYMENT;
338 l_balance(l_bs_valid_rec_count) := p_bill_summaries(i).BALANCE;
339 l_previous_balance(l_bs_valid_rec_count) := p_bill_summaries(i).PREVIOUS_BALANCE;
340 l_billing_vendor_name(l_bs_valid_rec_count) := p_bill_summaries(i).BILLING_VENDOR_NAME;
341 l_bill_location_url(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_LOCATION_URL;
342 l_due_now(l_bs_valid_rec_count) := p_bill_summaries(i).DUE_NOW;
343
344 IF (p_bill_summaries(i).CREATED_BY IS NULL) THEN
345 l_created_by(l_bs_valid_rec_count) := FND_GLOBAL.USER_ID;
346 ELSE
347 l_created_by(l_bs_valid_rec_count) := p_bill_summaries(i).CREATED_BY;
348 END IF;
349
350 l_creation_date(l_bs_valid_rec_count) := SYSDATE;
351
352 IF (p_bill_summaries(i).LAST_UPDATED_BY IS NULL) THEN
353 l_last_updated_by(l_bs_valid_rec_count) := FND_GLOBAL.USER_ID;
354 ELSE
355 l_last_updated_by(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATED_BY;
356 END IF;
357
358 l_last_update_date(l_bs_valid_rec_count) := SYSDATE;
359
360 IF (p_bill_summaries(i).LAST_UPDATE_LOGIN IS NULL) THEN
361 l_last_update_login(l_bs_valid_rec_count) := FND_GLOBAL.LOGIN_ID;
362 ELSE
363 l_last_update_login(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATE_LOGIN;
364 END IF;
365
366 l_object_version_number(l_bs_valid_rec_count) := 1;
367 l_attribute_category(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE_CATEGORY;
368 l_attribute1(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE1;
369 l_attribute2(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE2;
370 l_attribute3(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE3;
371 l_attribute4(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE4;
372 l_attribute5(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE5;
373 l_attribute6(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE6;
374 l_attribute7(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE7;
375 l_attribute8(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE8;
376 l_attribute9(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE9;
377 l_attribute10(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE10;
378 l_attribute11(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE11;
379 l_attribute12(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE12;
380 l_attribute13(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE13;
381 l_attribute14(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE14;
382 l_attribute15(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE15;
383 END IF;
384
385 END LOOP; -- End For loop
386
390 BILL_SUMMARY_ID,
387 -- batch insert all valid records in database
388 FORALL i IN 1..l_bs_valid_rec_count
389 INSERT INTO XNB_BILL_SUMMARIES(
391 ACCOUNT_NUMBER,
392 TOTAL_AMOUNT_DUE,
393 ADJUSTMENTS,
394 UNRESOLVED_DISPUTES,
395 BILL_NUMBER,
396 BILL_CYCLE_START_DATE,
397 BILL_CYCLE_END_DATE,
398 DUE_DATE,
399 NEW_CHARGES,
400 PAYMENT,
401 BALANCE,
402 PREVIOUS_BALANCE,
403 BILLING_VENDOR_NAME,
404 BILL_LOCATION_URL,
405 DUE_NOW,
406 CREATED_BY,
407 CREATION_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_DATE,
410 LAST_UPDATE_LOGIN,
411 OBJECT_VERSION_NUMBER,
412 ATTRIBUTE_CATEGORY,
413 ATTRIBUTE1,
414 ATTRIBUTE2,
415 ATTRIBUTE3,
416 ATTRIBUTE4,
417 ATTRIBUTE5,
418 ATTRIBUTE6,
419 ATTRIBUTE7,
420 ATTRIBUTE8,
421 ATTRIBUTE9,
422 ATTRIBUTE10,
423 ATTRIBUTE11,
424 ATTRIBUTE12,
425 ATTRIBUTE13,
426 ATTRIBUTE14,
427 ATTRIBUTE15
428 ) VALUES (
429 XNB_BILL_SUMMARIES_S.nextval,
430 l_account_number(i),
431 l_total_amount_due(i),
432 l_adjustments(i),
433 l_unresolved_disputes(i),
434 l_bill_number(i),
435 l_bill_cycle_start_date(i),
436 l_bill_cycle_end_date(i),
437 l_due_date(i),
438 l_new_charges(i),
439 l_payment(i),
440 l_balance(i),
441 l_previous_balance(i),
442 l_billing_vendor_name(i),
443 l_bill_location_url(i),
444 l_due_now(i),
445 l_created_by(i),
446 l_creation_date(i),
447 l_last_updated_by(i),
448 l_last_update_date(i),
449 l_last_update_login(i),
450 l_object_version_number(i),
451 l_attribute_category(i),
452 l_attribute1(i),
453 l_attribute2(i),
454 l_attribute3(i),
455 l_attribute4(i),
456 l_attribute5(i),
457 l_attribute6(i),
458 l_attribute7(i),
459 l_attribute8(i),
460 l_attribute9(i),
461 l_attribute10(i),
462 l_attribute11(i),
463 l_attribute12(i),
464 l_attribute13(i),
465 l_attribute14(i),
466 l_attribute15(i)
467 ); -- End Forall loop
468
469 -- If Null bill number or account number throw error
470 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
471 RAISE null_excep;
472 END IF;
473
474 -- Handle all exceptions
475 Exception
476 -- Handle null exceptions
477 WHEN null_excep THEN
478 x_return_status := FND_API.G_RET_STS_ERROR;
479 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_SUCC_INS');
480
481 WHEN NO_DATA_FOUND then
482 ROLLBACK TO BULK_INSERT;
483 x_return_status := FND_API.G_RET_STS_ERROR;
484 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_NO_REC_FND');
485
486 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
487 --Change: The following exception section was added to trap API version check exceptions.
488 --Other Files Impact: None.
489 WHEN FND_API.G_EXC_ERROR THEN
490 ROLLBACK TO BULK_INSERT;
491 FND_MESSAGE.SET_NAME('XNB','XNB_PLS_MSG_INCOMPAT_API');
492 FND_MESSAGE.SET_TOKEN('API_FULL_NAME', G_PKG_NAME || '.' || l_api_name );
493 FND_MESSAGE.SET_TOKEN('GIVEN_VER', p_api_version);
494 FND_MESSAGE.SET_TOKEN('CURR_VER', l_api_version);
495 x_return_status := FND_API.G_RET_STS_ERROR;
496 x_msg_data := x_msg_data || ' ' || FND_MESSAGE.GET;
497
498 WHEN OTHERS THEN
499 ROLLBACK TO BULK_INSERT;
500 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
501 --Change: On OTHERS error, the public API should return G_RET_STS_UNEXP_ERROR. Corrected return code.
502 --Other Files Impact: None.
503 --x_return_status := FND_API.G_RET_STS_ERROR;
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_NO_REC_INS');
506
507 End Insert_Row_Batch;
508
509
510 -- Start of comments
511 -- API name : Update_Row_Batch
515 -- . XNB_BILL_SUMMARIES.
512 -- Type : Public
513 -- Pre-reqs : None.
514 -- Function : Updates the given bill summary rows in the XNB table
516 -- Assumptions :
517 -- All records whose
518 -- 1. Account Number must exit in eBusiness suite database schema
519 -- HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER.
520 -- 2. Account Number, Bill Number, Bill Cycle End Date and Billing
521 -- Vendor Name are required.
522 -- 3. Account Number, Bill Number and Billing Vendor Name cannot be
523 -- updated and hence must be present for updating.
524 -- 4. For all above validations, in case of failure, only those records
525 -- will be rejected and rest inserted. Any other validation failure
526 -- will not insert any record (All or none are inserted/ rolled back).
527 -- 5. All data inserted are case sensitive.
528 -- Parameters :
529 -- IN : p_api_version IN NUMBER Required
530 -- IN : p_bill_summaries IN bill_summaries_table
531 -- -- The table of bill summary records to be inserted.
532 -- OUT : x_return_status OUT NOCOPY VARCHAR2
533 -- -- Execution status returned.
534 -- -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_SUCCESS
535 -- OUT : x_msg_data OUT NOCOPY VARCHAR2
536 -- -- Error message returned.
537 -- Version : Current version 1.0
538 -- Initial version 1.0
539 -- End of comments
540
541 PROCEDURE Update_Row_Batch(
542 p_api_version IN NUMBER,
543 p_bill_summaries IN bill_summaries_table,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_msg_data OUT NOCOPY VARCHAR2
546 )
547 IS
548 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
549 --Change: Added parameter p_api_version to comply to Business API standards.
550 -- Added Std API documentation header for the API.
551 -- Moved Assumptions from below this comment to API doc Header
552 --Other Files Impact: None.
553 l_api_version CONSTANT NUMBER := 1.0; -- added to fix Bug#:4159395
554 l_api_name CONSTANT VARCHAR2(20) := 'Update_Row_Batch'; -- added to fix Bug#:4159395
555
556 null_excep EXCEPTION; -- exception declaration
557
558 l_bs_all_rec_count NUMBER := 0; -- Total bill summaries recpunt received
559 l_bs_valid_rec_count NUMBER := 0; -- Valid bill summary record to be inserted
560 l_bs_valid_rec_flag varchar2(1) := null; -- Validity of a bill summary record is true
561 --l_err_msg_count NUMBER := 0; -- Total number of error found
562
563 l_num_init NUMBER := 0; -- intialize varray variable of NUMBER type
564 l_var_init varchar2(5) := null; -- intialize varray variable of varchar2 type
565
566 -- declaring varray to hold bill summary records
567 l_bill_summary_id v_number;
568 l_account_number v_var30;
569 l_total_amount_due v_var30;
570 l_adjustments v_var30;
571 l_unresolved_disputes v_var30;
572 l_bill_number v_var30;
573 l_bill_cycle_start_date v_date;
574 l_bill_cycle_end_date v_date;
575 l_due_date v_date;
576 l_new_charges v_var30;
577 l_payment v_var30;
578 l_balance v_var30;
579 l_previous_balance v_var30;
580 l_billing_vendor_name v_var240;
581 l_bill_location_url v_var240;
582 l_due_now v_var30;
583 l_created_by v_number;
584 l_creation_date v_date;
585 l_last_updated_by v_number;
586 l_last_update_date v_date;
587 l_last_update_login v_number;
588 l_object_version_number v_number;
589 l_attribute_category v_var30;
590 l_attribute1 v_var150;
591 l_attribute2 v_var150;
592 l_attribute3 v_var150;
593 l_attribute4 v_var150;
594 l_attribute5 v_var150;
595 l_attribute6 v_var150;
596 l_attribute7 v_var150;
597 l_attribute8 v_var150;
598 l_attribute9 v_var150;
599 l_attribute10 v_var150;
600 l_attribute11 v_var150;
601 l_attribute12 v_var150;
602 l_attribute13 v_var150;
603 l_attribute14 v_var150;
604 l_attribute15 v_var150;
605
606 t_acc_num varchar2(30) := null; -- account number to hold in temp account number
607 t_bill_num varchar2(30) := null; -- bill number to hold in temp bill number
608 t_bill_vendor varchar2(30) := null; -- billing vendor name to hold in temp billing vendor name
609 t_uniq_val VARCHAR2(1) := null;
610
611 Cursor c_check_uniq_constrain is
612 select
613 TOTAL_AMOUNT_DUE ,
614 ADJUSTMENTS ,
615 UNRESOLVED_DISPUTES ,
616 BILL_CYCLE_START_DATE,
617 BILL_CYCLE_END_DATE,
618 DUE_DATE,
622 PREVIOUS_BALANCE,
619 NEW_CHARGES,
620 PAYMENT,
621 BALANCE,
623 BILL_LOCATION_URL,
624 DUE_NOW ,
625 ATTRIBUTE_CATEGORY,
626 ATTRIBUTE1,
627 ATTRIBUTE2,
628 ATTRIBUTE3,
629 ATTRIBUTE4,
630 ATTRIBUTE5,
631 ATTRIBUTE6,
632 ATTRIBUTE7,
633 ATTRIBUTE8,
634 ATTRIBUTE9,
635 ATTRIBUTE10,
636 ATTRIBUTE11,
637 ATTRIBUTE12,
638 ATTRIBUTE13,
639 ATTRIBUTE14,
640 ATTRIBUTE15
641 from XNB_BILL_SUMMARIES
642 where ACCOUNT_NUMBER = t_acc_num
643 and BILL_NUMBER = t_bill_num
644 and BILLING_VENDOR_NAME = t_bill_vendor;
645
646 BEGIN
647 SAVEPOINT BULK_UPDATE;
648 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
649 --Change: The following check was added to check API compatibility
650 --Other Files Impact: None.
651
652 -- Standard call to check for call compatibility
653 IF NOT FND_API.Compatible_API_Call(
654 l_api_version,
655 p_api_version,
656 l_api_name,
657 G_PKG_NAME) THEN
658 RAISE FND_API.G_EXC_ERROR;
659 END IF;
660
661 -- Initialize API return status to success and other variables
662 l_bs_all_rec_count := p_bill_summaries.last;
663 l_bs_valid_rec_flag := 'T';
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665 x_msg_data := null;
666
667 -- Intialize all local varray and extend to intialize for all records
668 l_bill_summary_id :=v_number(l_num_init);
669 l_bill_summary_id.EXTEND(l_bs_all_rec_count,1);
670 l_account_number :=v_var30(l_var_init);
671 l_account_number.EXTEND(l_bs_all_rec_count,1);
672 l_total_amount_due :=v_var30(l_var_init);
673 l_total_amount_due.EXTEND(l_bs_all_rec_count,1);
674 l_adjustments :=v_var30(l_var_init);
675 l_adjustments.EXTEND(l_bs_all_rec_count,1);
676 l_unresolved_disputes :=v_var30(l_var_init);
677 l_unresolved_disputes.EXTEND(l_bs_all_rec_count,1);
678 l_bill_number :=v_var30(l_var_init);
679 l_bill_number.EXTEND(l_bs_all_rec_count,1);
680 l_bill_cycle_start_date :=v_date(null);
681 l_bill_cycle_start_date.EXTEND(l_bs_all_rec_count,1);
682 l_bill_cycle_end_date :=v_date(null);
683 l_bill_cycle_end_date.EXTEND(l_bs_all_rec_count,1);
684 l_due_date :=v_date(null);
685 l_due_date.EXTEND(l_bs_all_rec_count,1);
686 l_new_charges :=v_var30(l_var_init);
687 l_new_charges.EXTEND(l_bs_all_rec_count,1);
688 l_payment :=v_var30(l_var_init);
689 l_payment.EXTEND(l_bs_all_rec_count,1);
690 l_balance :=v_var30(l_var_init);
691 l_balance.EXTEND(l_bs_all_rec_count,1);
692 l_previous_balance :=v_var30(l_var_init);
693 l_previous_balance.EXTEND(l_bs_all_rec_count,1);
694 l_billing_vendor_name :=v_var240(l_var_init);
695 l_billing_vendor_name.EXTEND(l_bs_all_rec_count,1);
696 l_bill_location_url :=v_var240(l_var_init);
697 l_bill_location_url.EXTEND(l_bs_all_rec_count,1);
698 l_due_now :=v_var30(l_var_init);
699 l_due_now.EXTEND(l_bs_all_rec_count,1);
700 l_created_by :=v_number(l_num_init);
701 l_created_by.EXTEND(l_bs_all_rec_count,1);
702 l_creation_date :=v_date(null);
703 l_creation_date.EXTEND(l_bs_all_rec_count,1);
704 l_last_updated_by :=v_number(l_num_init);
705 l_last_updated_by.EXTEND(l_bs_all_rec_count,1);
706 l_last_update_date :=v_date(null);
707 l_last_update_date.EXTEND(l_bs_all_rec_count,1);
708 l_last_update_login :=v_number(l_num_init);
709 l_last_update_login.EXTEND(l_bs_all_rec_count,1);
710 l_object_version_number :=v_number(l_num_init);
711 l_object_version_number.EXTEND(l_bs_all_rec_count,1);
712 l_attribute_category :=v_var30(l_var_init);
713 l_attribute_category.EXTEND(l_bs_all_rec_count,1);
714 l_attribute1 :=v_var150(l_var_init);
715 l_attribute1.EXTEND(l_bs_all_rec_count,1);
716 l_attribute2 :=v_var150(l_var_init);
717 l_attribute2.EXTEND(l_bs_all_rec_count,1);
718 l_attribute3 :=v_var150(l_var_init);
719 l_attribute3.EXTEND(l_bs_all_rec_count,1);
720 l_attribute4 :=v_var150(l_var_init);
721 l_attribute4.EXTEND(l_bs_all_rec_count,1);
722 l_attribute5 :=v_var150(l_var_init);
723 l_attribute5.EXTEND(l_bs_all_rec_count,1);
724 l_attribute6 :=v_var150(l_var_init);
725 l_attribute6.EXTEND(l_bs_all_rec_count,1);
726 l_attribute7 :=v_var150(l_var_init);
727 l_attribute7.EXTEND(l_bs_all_rec_count,1);
728 l_attribute8 :=v_var150(l_var_init);
729 l_attribute8.EXTEND(l_bs_all_rec_count,1);
730 l_attribute9 :=v_var150(l_var_init);
731 l_attribute9.EXTEND(l_bs_all_rec_count,1);
735 l_attribute11.EXTEND(l_bs_all_rec_count,1);
732 l_attribute10 :=v_var150(l_var_init);
733 l_attribute10.EXTEND(l_bs_all_rec_count,1);
734 l_attribute11 :=v_var150(l_var_init);
736 l_attribute12 :=v_var150(l_var_init);
737 l_attribute12.EXTEND(l_bs_all_rec_count,1);
738 l_attribute13 :=v_var150(l_var_init);
739 l_attribute13.EXTEND(l_bs_all_rec_count,1);
740 l_attribute14 :=v_var150(l_var_init);
741 l_attribute14.EXTEND(l_bs_all_rec_count,1);
742 l_attribute15 :=v_var150(l_var_init);
743 l_attribute15.EXTEND(l_bs_all_rec_count,1);
744
745 -- Loop to validate and initialize all records
746 FOR i IN 1..l_bs_all_rec_count LOOP
747 l_bs_valid_rec_flag := 'T';
748
749 -- Bill Number is mandatory (NOT NULL field)
750 IF (p_bill_summaries(i).BILL_NUMBER IS NULL) THEN
751 l_bs_valid_rec_flag := 'F'; -- validity is false
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BILL_NUM_IS_NULL');
754 END IF;
755
756 -- Account Number is mandatory (NOT NULL field)
757 IF (p_bill_summaries(i).ACCOUNT_NUMBER IS NULL) THEN
758 l_bs_valid_rec_flag := 'F'; -- validity is false
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_ACC_NUM_IS_NULL');
761 END IF;
762
763 -- Bill Vendor Name is mandatory (NOT NULL field)
764 IF (p_bill_summaries(i).BILLING_VENDOR_NAME IS NULL) THEN
765 l_bs_valid_rec_flag := 'F'; -- validity is false
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 x_msg_data := x_msg_data || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_BILL_VEN_IS_NULL');
768 END IF;
769
770 -- Check for Uniqe Constraints...
771 t_acc_num := p_bill_summaries(i).ACCOUNT_NUMBER;
772 t_bill_num := p_bill_summaries(i).BILL_NUMBER;
773 t_bill_vendor := p_bill_summaries(i).BILLING_VENDOR_NAME;
774 t_uniq_val := null;
775 l_bs_valid_rec_count := l_bs_valid_rec_count + 1;
776 open c_check_uniq_constrain;
777 begin
778 fetch c_check_uniq_constrain into
779 l_total_amount_due(l_bs_valid_rec_count),
780 l_adjustments(l_bs_valid_rec_count),
781 l_unresolved_disputes(l_bs_valid_rec_count),
782 l_bill_cycle_start_date(l_bs_valid_rec_count),
783 l_bill_cycle_end_date(l_bs_valid_rec_count),
784 l_due_date(l_bs_valid_rec_count),
785 l_new_charges(l_bs_valid_rec_count),
786 l_payment(l_bs_valid_rec_count),
787 l_balance(l_bs_valid_rec_count),
788 l_previous_balance(l_bs_valid_rec_count),
789 l_bill_location_url(l_bs_valid_rec_count),
790 l_due_now(l_bs_valid_rec_count),
791 l_attribute_category(l_bs_valid_rec_count),
792 l_attribute1(l_bs_valid_rec_count),
793 l_attribute2(l_bs_valid_rec_count),
794 l_attribute3(l_bs_valid_rec_count),
795 l_attribute4(l_bs_valid_rec_count),
796 l_attribute5(l_bs_valid_rec_count),
797 l_attribute6(l_bs_valid_rec_count),
798 l_attribute7(l_bs_valid_rec_count),
799 l_attribute8(l_bs_valid_rec_count),
800 l_attribute9(l_bs_valid_rec_count),
801 l_attribute10(l_bs_valid_rec_count),
802 l_attribute11(l_bs_valid_rec_count),
803 l_attribute12(l_bs_valid_rec_count),
804 l_attribute13(l_bs_valid_rec_count),
805 l_attribute14(l_bs_valid_rec_count),
806 l_attribute15(l_bs_valid_rec_count)
807 ;
808
809 if (c_check_uniq_constrain%NOTFOUND ) then
810 raise no_data_found;
811 end if;
812 exception
813 when no_data_found then
814 l_bs_valid_rec_flag := 'F'; -- validity is false
815 l_bs_valid_rec_count := l_bs_valid_rec_count - 1;
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 fnd_message.SET_NAME('XNB','XNB_PLS_BILL_SUM_REC_UNQ');
818 fnd_message.SET_TOKEN('ACC_NUM',t_acc_num);
819 fnd_message.SET_TOKEN('BILL_NUM',t_bill_num);
820 fnd_message.SET_TOKEN('BILL_VEN',t_bill_vendor);
821 x_msg_data := x_msg_data || ' ' || fnd_message.GET;
822 end;
823 close c_check_uniq_constrain;
824
825 -- If the bill summary record is valid
826 IF l_bs_valid_rec_flag = 'T' THEN
827
828 l_account_number(l_bs_valid_rec_count) := p_bill_summaries(i).ACCOUNT_NUMBER;
829 l_bill_number(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_NUMBER;
830 l_billing_vendor_name(l_bs_valid_rec_count) := p_bill_summaries(i).BILLING_VENDOR_NAME;
831 IF (p_bill_summaries(i).TOTAL_AMOUNT_DUE IS NOT NULL) THEN
832 l_total_amount_due(l_bs_valid_rec_count) := p_bill_summaries(i).TOTAL_AMOUNT_DUE;
833 END IF;
834 IF (p_bill_summaries(i).ADJUSTMENTS IS NOT NULL) THEN
835 l_adjustments(l_bs_valid_rec_count) := p_bill_summaries(i).ADJUSTMENTS;
839 END IF;
836 END IF;
837 IF (p_bill_summaries(i).UNRESOLVED_DISPUTES IS NOT NULL) THEN
838 l_unresolved_disputes(l_bs_valid_rec_count) := p_bill_summaries(i).UNRESOLVED_DISPUTES;
840 IF (p_bill_summaries(i).BILL_CYCLE_START_DATE IS NOT NULL) THEN
841 l_bill_cycle_start_date(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_CYCLE_START_DATE;
842 END IF;
843 IF (p_bill_summaries(i).BILL_CYCLE_END_DATE IS NOT NULL) THEN
844 l_bill_cycle_end_date(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_CYCLE_END_DATE;
845 END IF;
846 IF (p_bill_summaries(i).DUE_DATE IS NOT NULL) THEN
847 l_due_date(l_bs_valid_rec_count) := p_bill_summaries(i).DUE_DATE;
848 END IF;
849 IF ( p_bill_summaries(i).NEW_CHARGES IS NOT NULL) THEN
850 l_new_charges(l_bs_valid_rec_count) := p_bill_summaries(i).NEW_CHARGES;
851 END IF;
852 IF (p_bill_summaries(i).PAYMENT IS NOT NULL) THEN
853 l_payment(l_bs_valid_rec_count) := p_bill_summaries(i).PAYMENT;
854 END IF;
855 IF (p_bill_summaries(i).BALANCE IS NOT NULL) THEN
856 l_balance(l_bs_valid_rec_count) := p_bill_summaries(i).BALANCE;
857 END IF;
858 IF (p_bill_summaries(i).PREVIOUS_BALANCE IS NOT NULL) THEN
859 l_previous_balance(l_bs_valid_rec_count) := p_bill_summaries(i).PREVIOUS_BALANCE;
860 END IF;
861 IF (p_bill_summaries(i).BILL_LOCATION_URL IS NOT NULL) THEN
862 l_bill_location_url(l_bs_valid_rec_count) := p_bill_summaries(i).BILL_LOCATION_URL;
863 END IF;
864 IF ( p_bill_summaries(i).DUE_NOW IS NOT NULL) THEN
865 l_due_now(l_bs_valid_rec_count) := p_bill_summaries(i).DUE_NOW;
866 END IF;
867
868 IF (p_bill_summaries(i).LAST_UPDATED_BY IS NULL) THEN
869 l_last_updated_by(l_bs_valid_rec_count) := FND_GLOBAL.USER_ID;
870 ELSE
871 l_last_updated_by(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATED_BY;
872 END IF;
873
874 l_last_update_date(l_bs_valid_rec_count) := SYSDATE;
875
876 IF (p_bill_summaries(i).LAST_UPDATE_LOGIN IS NULL) THEN
877 l_last_update_login(l_bs_valid_rec_count) := FND_GLOBAL.LOGIN_ID;
878 ELSE
879 l_last_update_login(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATE_LOGIN;
880 END IF;
881
882 IF (p_bill_summaries(i).ATTRIBUTE_CATEGORY IS NOT NULL) THEN
883 l_attribute_category(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE_CATEGORY;
884 END IF;
885 IF (p_bill_summaries(i).ATTRIBUTE1 IS NOT NULL) THEN
886 l_attribute1(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE1;
887 END IF;
888 IF (p_bill_summaries(i).ATTRIBUTE2 IS NOT NULL) THEN
889 l_attribute2(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE2;
890 END IF;
891 IF (p_bill_summaries(i).ATTRIBUTE3 IS NOT NULL) THEN
892 l_attribute3(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE3;
893 END IF;
894 IF (p_bill_summaries(i).ATTRIBUTE4 IS NOT NULL) THEN
895 l_attribute4(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE4;
896 END IF;
897 IF (p_bill_summaries(i).ATTRIBUTE5 IS NOT NULL) THEN
898 l_attribute5(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE5;
899 END IF;
900 IF (p_bill_summaries(i).ATTRIBUTE6 IS NOT NULL) THEN
901 l_attribute6(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE6;
902 END IF;
903 IF (p_bill_summaries(i).ATTRIBUTE7 IS NOT NULL) THEN
904 l_attribute7(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE7;
905 END IF;
906 IF (p_bill_summaries(i).ATTRIBUTE8 IS NOT NULL) THEN
907 l_attribute8(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE8;
908 END IF;
909 IF (p_bill_summaries(i).ATTRIBUTE9 IS NOT NULL) THEN
910 l_attribute9(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE9;
911 END IF;
912 IF (p_bill_summaries(i).ATTRIBUTE10 IS NOT NULL) THEN
913 l_attribute10(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE10;
914 END IF;
915 IF (p_bill_summaries(i).ATTRIBUTE11 IS NOT NULL) THEN
916 l_attribute11(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE11;
917 END IF;
918 IF (p_bill_summaries(i).ATTRIBUTE12 IS NOT NULL) THEN
919 l_attribute12(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE12;
920 END IF;
921 IF (p_bill_summaries(i).ATTRIBUTE13 IS NOT NULL) THEN
922 l_attribute13(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE13;
923 END IF;
924 IF (p_bill_summaries(i).ATTRIBUTE14 IS NOT NULL) THEN
925 l_attribute14(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE14;
926 END IF;
927 IF (p_bill_summaries(i).ATTRIBUTE15 IS NOT NULL) THEN
928 l_attribute15(l_bs_valid_rec_count) := p_bill_summaries(i).ATTRIBUTE15;
929 END IF;
930 END IF;
931
932 END LOOP; -- End For loop
933
934 -- batch update all valid records in database
938 TOTAL_AMOUNT_DUE = l_total_amount_due(i),
935 FORALL i IN 1..l_bs_valid_rec_count
936 Update XNB_BILL_SUMMARIES
937 SET
939 ADJUSTMENTS = l_adjustments(i),
940 UNRESOLVED_DISPUTES = l_unresolved_disputes(i),
941 BILL_CYCLE_START_DATE = l_bill_cycle_start_date(i),
942 BILL_CYCLE_END_DATE = l_bill_cycle_end_date(i),
943 DUE_DATE = l_due_date(i),
944 NEW_CHARGES = l_new_charges(i),
945 PAYMENT = l_payment(i),
946 BALANCE = l_balance(i),
947 PREVIOUS_BALANCE = l_previous_balance(i),
948 BILL_LOCATION_URL = l_bill_location_url(i),
949 DUE_NOW = l_due_now(i),
950 LAST_UPDATED_BY = l_last_updated_by(i),
951 LAST_UPDATE_DATE = SYSDATE,
952 LAST_UPDATE_LOGIN = l_last_update_login(i),
953 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
954 ATTRIBUTE_CATEGORY = l_attribute_category(i),
955 ATTRIBUTE1 = l_attribute1(i),
956 ATTRIBUTE2 = l_attribute2(i),
957 ATTRIBUTE3 = l_attribute3(i),
958 ATTRIBUTE4 = l_attribute4(i),
959 ATTRIBUTE5 = l_attribute5(i),
960 ATTRIBUTE6 = l_attribute6(i),
961 ATTRIBUTE7 = l_attribute7(i),
962 ATTRIBUTE8 = l_attribute8(i),
963 ATTRIBUTE9 = l_attribute9(i),
964 ATTRIBUTE10 = l_attribute10(i),
965 ATTRIBUTE11 = l_attribute11(i),
966 ATTRIBUTE12 = l_attribute12(i),
967 ATTRIBUTE13 = l_attribute13(i),
968 ATTRIBUTE14 = l_attribute14(i),
969 ATTRIBUTE15 = l_attribute15(i)
970 where BILL_NUMBER = l_bill_number(i) and
971 ACCOUNT_NUMBER = l_account_number(i) and
972 BILLING_VENDOR_NAME = l_billing_vendor_name(i);
973
974 --IF (SQL%NOTFOUND) THEN
975 --RAISE NO_DATA_FOUND;
976 --END IF;
977
978 -- If Null bill number or account number or Billing Vendor Name throw error
979 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
980 RAISE null_excep;
981 END IF;
982
983 -- Handle all exceptions
984 Exception
985 -- Handle null exceptions
986 WHEN null_excep THEN
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_SUCC_UPD');
989
990 WHEN NO_DATA_FOUND then
991 ROLLBACK TO BULK_UPDATE;
992 x_return_status := FND_API.G_RET_STS_ERROR;
993 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_ACC_BILL_NUM');
994
995 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
996 --Change: The following exception section was added to trap API version check exceptions.
997 --Other Files Impact: None.
998 WHEN FND_API.G_EXC_ERROR THEN
999 ROLLBACK TO BULK_UPDATE;
1000 FND_MESSAGE.SET_NAME('XNB','XNB_PLS_MSG_INCOMPAT_API');
1001 FND_MESSAGE.SET_TOKEN('API_FULL_NAME', G_PKG_NAME || '.' || l_api_name );
1002 FND_MESSAGE.SET_TOKEN('GIVEN_VER', p_api_version);
1003 FND_MESSAGE.SET_TOKEN('CURR_VER', l_api_version);
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 x_msg_data := x_msg_data || ' ' || FND_MESSAGE.GET;
1006
1007 WHEN OTHERS THEN
1008 ROLLBACK TO BULK_UPDATE;
1009 --Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
1010 --Change: On OTHERS error, the public API should return G_RET_STS_UNEXP_ERROR. Corrected return code.
1011 --Other Files Impact: None.
1012 --x_return_status := FND_API.G_RET_STS_ERROR;
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014 x_msg_data := x_msg_data || ' ' || SQLERRM || ' ' || fnd_message.GET_STRING('XNB','XNB_PLS_MSG_NO_REC_UPD');
1015
1016 End Update_Row_Batch;
1017
1018
1019 End XNB_BILL_SUMMARIES_PKG;