[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- must be unique for insertion.
-- 4. For all above validations, in case of failure, only those records
-- will be rejected and rest inserted. Any other validation failure will
-- not insert any record (All or none are inserted/ rolled back).
-- 5. All data inserted or updated are case sensitive.
-- Parameters :
-- IN : p_api_version IN NUMBER Required
-- IN : p_bill_summaries IN bill_summaries_table
-- -- The table of bill summary records to be inserted.
-- OUT : x_return_status OUT NOCOPY VARCHAR2
-- -- Execution status returned.
-- -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_SUCCESS
-- OUT : x_msg_data OUT NOCOPY VARCHAR2
-- -- Error message returned.
-- Version : Current version 1.0
-- Initial version 1.0
-- End of comments
PROCEDURE Insert_Row_Batch(
p_api_version IN NUMBER,
p_bill_summaries IN bill_summaries_table,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
--Change: Added parameter p_api_version to comply to Business API standards.
-- Added Std API documentation header for the API.
-- Moved Assumptions from below this comment to API doc Header
--Other Files Impact: None.
l_api_version CONSTANT NUMBER := 1.0; -- added to fix Bug#:4159395
l_api_name CONSTANT VARCHAR2(20) := 'Insert_Row_Batch'; -- added to fix Bug#:4159395
l_bs_valid_rec_count NUMBER := 0; -- Valid bill summary record to be inserted
l_last_updated_by v_number;
l_last_update_date v_date;
l_last_update_login v_number;
select ACCOUNT_NUMBER
from HZ_CUST_ACCOUNTS
where ACCOUNT_NUMBER = tmp_acc_num;
select 1
from XNB_BILL_SUMMARIES
where ACCOUNT_NUMBER = t_acc_num
and BILL_NUMBER = t_bill_num
and BILLING_VENDOR_NAME = t_bill_vendor;
SAVEPOINT BULK_INSERT;
l_last_updated_by :=v_number(l_num_init);
l_last_updated_by.EXTEND(l_bs_all_rec_count,1);
l_last_update_date :=v_date(null);
l_last_update_date.EXTEND(l_bs_all_rec_count,1);
l_last_update_login :=v_number(l_num_init);
l_last_update_login.EXTEND(l_bs_all_rec_count,1);
IF (p_bill_summaries(i).LAST_UPDATED_BY IS NULL) THEN
l_last_updated_by(l_bs_valid_rec_count) := FND_GLOBAL.USER_ID;
l_last_updated_by(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATED_BY;
l_last_update_date(l_bs_valid_rec_count) := SYSDATE;
IF (p_bill_summaries(i).LAST_UPDATE_LOGIN IS NULL) THEN
l_last_update_login(l_bs_valid_rec_count) := FND_GLOBAL.LOGIN_ID;
l_last_update_login(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATE_LOGIN;
INSERT INTO XNB_BILL_SUMMARIES(
BILL_SUMMARY_ID,
ACCOUNT_NUMBER,
TOTAL_AMOUNT_DUE,
ADJUSTMENTS,
UNRESOLVED_DISPUTES,
BILL_NUMBER,
BILL_CYCLE_START_DATE,
BILL_CYCLE_END_DATE,
DUE_DATE,
NEW_CHARGES,
PAYMENT,
BALANCE,
PREVIOUS_BALANCE,
BILLING_VENDOR_NAME,
BILL_LOCATION_URL,
DUE_NOW,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) VALUES (
XNB_BILL_SUMMARIES_S.nextval,
l_account_number(i),
l_total_amount_due(i),
l_adjustments(i),
l_unresolved_disputes(i),
l_bill_number(i),
l_bill_cycle_start_date(i),
l_bill_cycle_end_date(i),
l_due_date(i),
l_new_charges(i),
l_payment(i),
l_balance(i),
l_previous_balance(i),
l_billing_vendor_name(i),
l_bill_location_url(i),
l_due_now(i),
l_created_by(i),
l_creation_date(i),
l_last_updated_by(i),
l_last_update_date(i),
l_last_update_login(i),
l_object_version_number(i),
l_attribute_category(i),
l_attribute1(i),
l_attribute2(i),
l_attribute3(i),
l_attribute4(i),
l_attribute5(i),
l_attribute6(i),
l_attribute7(i),
l_attribute8(i),
l_attribute9(i),
l_attribute10(i),
l_attribute11(i),
l_attribute12(i),
l_attribute13(i),
l_attribute14(i),
l_attribute15(i)
); -- End Forall loop
ROLLBACK TO BULK_INSERT;
ROLLBACK TO BULK_INSERT;
ROLLBACK TO BULK_INSERT;
End Insert_Row_Batch;
-- updated and hence must be present for updating.
-- 4. For all above validations, in case of failure, only those records
-- will be rejected and rest inserted. Any other validation failure
-- will not insert any record (All or none are inserted/ rolled back).
-- 5. All data inserted are case sensitive.
-- Parameters :
-- IN : p_api_version IN NUMBER Required
-- IN : p_bill_summaries IN bill_summaries_table
-- -- The table of bill summary records to be inserted.
-- OUT : x_return_status OUT NOCOPY VARCHAR2
-- -- Execution status returned.
-- -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_SUCCESS
-- OUT : x_msg_data OUT NOCOPY VARCHAR2
-- -- Error message returned.
-- Version : Current version 1.0
-- Initial version 1.0
-- End of comments
PROCEDURE Update_Row_Batch(
p_api_version IN NUMBER,
p_bill_summaries IN bill_summaries_table,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--Date:04-Feb-2005 Author:DPUTHIYE Bug#:4159395
--Change: Added parameter p_api_version to comply to Business API standards.
-- Added Std API documentation header for the API.
-- Moved Assumptions from below this comment to API doc Header
--Other Files Impact: None.
l_api_version CONSTANT NUMBER := 1.0; -- added to fix Bug#:4159395
l_api_name CONSTANT VARCHAR2(20) := 'Update_Row_Batch'; -- added to fix Bug#:4159395
l_bs_valid_rec_count NUMBER := 0; -- Valid bill summary record to be inserted
l_last_updated_by v_number;
l_last_update_date v_date;
l_last_update_login v_number;
select
TOTAL_AMOUNT_DUE ,
ADJUSTMENTS ,
UNRESOLVED_DISPUTES ,
BILL_CYCLE_START_DATE,
BILL_CYCLE_END_DATE,
DUE_DATE,
NEW_CHARGES,
PAYMENT,
BALANCE,
PREVIOUS_BALANCE,
BILL_LOCATION_URL,
DUE_NOW ,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
from XNB_BILL_SUMMARIES
where ACCOUNT_NUMBER = t_acc_num
and BILL_NUMBER = t_bill_num
and BILLING_VENDOR_NAME = t_bill_vendor;
SAVEPOINT BULK_UPDATE;
l_last_updated_by :=v_number(l_num_init);
l_last_updated_by.EXTEND(l_bs_all_rec_count,1);
l_last_update_date :=v_date(null);
l_last_update_date.EXTEND(l_bs_all_rec_count,1);
l_last_update_login :=v_number(l_num_init);
l_last_update_login.EXTEND(l_bs_all_rec_count,1);
IF (p_bill_summaries(i).LAST_UPDATED_BY IS NULL) THEN
l_last_updated_by(l_bs_valid_rec_count) := FND_GLOBAL.USER_ID;
l_last_updated_by(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATED_BY;
l_last_update_date(l_bs_valid_rec_count) := SYSDATE;
IF (p_bill_summaries(i).LAST_UPDATE_LOGIN IS NULL) THEN
l_last_update_login(l_bs_valid_rec_count) := FND_GLOBAL.LOGIN_ID;
l_last_update_login(l_bs_valid_rec_count) := p_bill_summaries(i).LAST_UPDATE_LOGIN;
Update XNB_BILL_SUMMARIES
SET
TOTAL_AMOUNT_DUE = l_total_amount_due(i),
ADJUSTMENTS = l_adjustments(i),
UNRESOLVED_DISPUTES = l_unresolved_disputes(i),
BILL_CYCLE_START_DATE = l_bill_cycle_start_date(i),
BILL_CYCLE_END_DATE = l_bill_cycle_end_date(i),
DUE_DATE = l_due_date(i),
NEW_CHARGES = l_new_charges(i),
PAYMENT = l_payment(i),
BALANCE = l_balance(i),
PREVIOUS_BALANCE = l_previous_balance(i),
BILL_LOCATION_URL = l_bill_location_url(i),
DUE_NOW = l_due_now(i),
LAST_UPDATED_BY = l_last_updated_by(i),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = l_last_update_login(i),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
ATTRIBUTE_CATEGORY = l_attribute_category(i),
ATTRIBUTE1 = l_attribute1(i),
ATTRIBUTE2 = l_attribute2(i),
ATTRIBUTE3 = l_attribute3(i),
ATTRIBUTE4 = l_attribute4(i),
ATTRIBUTE5 = l_attribute5(i),
ATTRIBUTE6 = l_attribute6(i),
ATTRIBUTE7 = l_attribute7(i),
ATTRIBUTE8 = l_attribute8(i),
ATTRIBUTE9 = l_attribute9(i),
ATTRIBUTE10 = l_attribute10(i),
ATTRIBUTE11 = l_attribute11(i),
ATTRIBUTE12 = l_attribute12(i),
ATTRIBUTE13 = l_attribute13(i),
ATTRIBUTE14 = l_attribute14(i),
ATTRIBUTE15 = l_attribute15(i)
where BILL_NUMBER = l_bill_number(i) and
ACCOUNT_NUMBER = l_account_number(i) and
BILLING_VENDOR_NAME = l_billing_vendor_name(i);
ROLLBACK TO BULK_UPDATE;
ROLLBACK TO BULK_UPDATE;
ROLLBACK TO BULK_UPDATE;
End Update_Row_Batch;