[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_delete CONSTANT VARCHAR2(30):='DELETE';
SELECT db_column_name
--, ak_attribute_code
FROM ams_column_rules
WHERE db_table_name = 'OZF_CLAIM_LINES_ALL'
AND rule_type = 'HISTORY'
AND object_type = 'CLAM'
AND object_attribute = cv_object_attribute;
select primary_uom_code
from mtl_system_items
where inventory_item_id = p_id
and organization_id = p_orgid;
SELECT claim_id
, valid_flag
, performance_complete_flag
, performance_attached_flag
, earnings_associated_flag
, set_of_books_id
, claim_currency_amount
, amount
, acctd_amount
, activity_type
, activity_id
, plan_id
, offer_id
, source_object_id
, source_object_line_id
, source_object_class
, quantity
, rate
, quantity_uom
, item_id
, item_description
, comments
, tax_code
, credit_to
FROM ozf_claim_lines_hist
WHERE claim_line_id = cv_claim_line_id
order by claim_line_history_id desc;
ELSIF p_mode = g_delete THEN
--x_create_hist_flag := 'Y';
ELSIF p_mode = JTF_PLSQL_API.g_update THEN
l_compare_result := Compare_Line_Items(
p_old_rec => l_old_line_rec
,p_new_rec => l_new_line_rec
,p_object_attribute => p_object_attribute
);
OZF_Utility_PVT.debug_message(l_full_name||': p_mode should be CREATE, UPDATE, or DELETE');
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT currency_code
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT transactional_curr_code
, conversion_type_code
, conversion_rate
, conversion_rate_date
FROM oe_order_headers
WHERE header_id = cv_doc_id;
SELECT invoice_currency_code
, exchange_rate_type
, exchange_rate
, exchange_date
FROM ra_customer_trx
WHERE customer_trx_id = cv_doc_id;
SELECT h.invoice_currency_code
, h.exchange_rate_type
, h.exchange_rate
, h.exchange_date
FROM ra_customer_trx h, ra_customer_trx_lines ln
WHERE h.customer_trx_id = ln.customer_trx_id
AND ln.customer_trx_line_id = cv_doc_id;
SELECT currency_code
, rate_type
, rate
, rate_date
FROM po_headers
WHERE po_header_id = cv_doc_id;
PROCEDURE Update_Line_Fm_Claim(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validation_level IN NUMBER := FND_API.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_new_claim_rec IN OZF_CLAIM_PVT.claim_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Fm_Claim';
SELECT currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
, cust_account_id
, payment_method
, tax_code
, set_of_books_id
, amount -- Bug:2781186
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT claim_line_id
, object_version_number
, claim_currency_amount
, tax_code
, earnings_associated_flag --Bug:2781186
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT count(*)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT claim_currency_amount
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SAVEPOINT Update_Line_Fm_Claim;
l_claim_line_tbl(i).update_from_tbl_flag := FND_API.g_true;
Update_Claim_Line(
p_api_version => 1.0
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, p_validation_level => p_validation_level
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_claim_line_rec => l_claim_line_tbl(i)
, x_object_version => l_object_version
);
ROLLBACK TO Update_Line_Fm_Claim;
ROLLBACK TO Update_Line_Fm_Claim;
ROLLBACK TO Update_Line_Fm_Claim;
END Update_Line_Fm_Claim;
PROCEDURE Update_Line_Fm_Claim(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_id IN NUMBER
,p_tax_action IN VARCHAR2
,p_remove_tax_code IN VARCHAR2 := FND_API.G_FALSE
,p_remove_related_cust IN VARCHAR2 := FND_API.G_FALSE
,p_amount_change_flag IN VARCHAR2 := FND_API.G_FALSE
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Fm_Claim';
SELECT currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
, tax_code
, amount
, payment_method
, vendor_id
, vendor_site_id
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
SELECT claim_line_id
, object_version_number
, claim_currency_amount
, tax_code
, earnings_associated_flag
FROM ozf_claim_lines_all
WHERE claim_id = p_claim_id;
SAVEPOINT Update_Line_Fm_Claim;
l_claim_line_tbl(i).update_from_tbl_flag := FND_API.g_true;
Update_Claim_Line(
p_api_version => 1.0
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_claim_line_rec => l_claim_line_tbl(i)
, x_object_version => l_object_version
);
ROLLBACK TO Update_Line_Fm_Claim;
ROLLBACK TO Update_Line_Fm_Claim;
ROLLBACK TO Update_Line_Fm_Claim;
END Update_Line_Fm_Claim;
SELECT amount_remaining, claim_class
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT NVL(SUM(claim_currency_amount), 0)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT currency_code
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT quantity_invoiced
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = cv_src_obj_line_id;
select NVL(sum(quantity),0) from ozf_claim_lines_all
WHERE claim_id = cv_claim_id
and source_object_line_id = cv_source_object_line_id;
l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
SELECT ozf_claim_lines_all_s.NEXTVAL
FROM DUAL;
SELECT COUNT(claim_line_id)
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT MAX(line_number)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT set_of_books_id, tax_code, org_id
FROM ozf_claims
WHERE CLAIM_ID = cv_claim_id;
SELECT amount_remaining
, claim_class
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
l_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT
orb.batch_type
FROM
ozf_resale_batches_all orb,
ozf_claims_all oc
WHERE
oc.batch_id = orb.resale_batch_id
AND oc.claim_id = p_claim_id;
l_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_x_claim_line_rec => l_claim_line_rec
);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
IF l_claim_line_rec.quantity IS NOT NULL AND
l_claim_line_rec.rate IS NOT NULL THEN
l_claim_line_rec.claim_currency_amount := l_claim_line_rec.quantity * l_claim_line_rec.rate;
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false AND
l_claim_class <> 'GROUP' THEN
-- get total of existing line amount (sum of claim_currency_amount)
OPEN c_line_sum_amt(l_claim_line_rec.claim_id);
| insert |
*-------------------------------------------------------*/
IF OZF_DEBUG_HIGH_ON THEN
OZF_Utility_PVT.debug_message(l_full_name ||': insert');
OZF_Utility_PVT.debug_message('l_claim_line_rec.claim_line_id before insert :' || l_claim_line_rec.claim_line_id);
INSERT INTO ozf_claim_lines_all (
claim_line_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_update_date,
program_id,
created_from,
claim_id,
line_number,
split_from_claim_line_id,
amount,
claim_currency_amount,
acctd_amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
set_of_books_id,
valid_flag,
source_object_id,
source_object_line_id,
source_object_class,
source_object_type_id,
plan_id,
offer_id,
utilization_id,
payment_method,
payment_reference_id,
payment_reference_number,
payment_reference_date,
voucher_id,
voucher_number,
payment_status,
approved_flag,
approved_date,
approved_by,
settled_date,
settled_by,
performance_complete_flag,
performance_attached_flag,
select_cust_children_flag,
item_id,
item_description,
quantity,
quantity_uom,
rate,
activity_type,
activity_id,
related_cust_account_id,
buy_group_cust_account_id,
relationship_type,
earnings_associated_flag,
comments,
tax_code,
credit_to,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
sale_date,
item_type,
tax_amount,
claim_curr_tax_amount,
acctd_tax_amount,
activity_line_id,
offer_type,
prorate_earnings_flag,
earnings_end_date,
buy_group_party_id,
dpp_cust_account_id, --12.1 Enhancement : Price Protection
batch_line_id , --Bugfix : 7811671
activity_name --Hbandi added code for resolving the bug 10164003
)
VALUES (
l_claim_line_rec.claim_line_id,
l_object_version_number, -- OBJECT_VERSION_NUMBER
SYSDATE, -- LAST_UPDATE_DATE
l_updated_by, -- LAST_UPDATED_BY
SYSDATE, -- CREATION_DATE
l_created_by, -- CREATED_BY
l_last_update_login, -- LAST_UPDATE_LOGIN
FND_GLOBAL.CONC_REQUEST_ID, -- REQUEST_ID
FND_GLOBAL.PROG_APPL_ID, -- PROGRAM_APPLICATION_ID
SYSDATE, -- PROGRAM_UPDATE_DATE
FND_GLOBAL.CONC_PROGRAM_ID, -- PROGRAM_ID
l_claim_line_rec.created_from, -- CREATED_FROM
l_claim_line_rec.claim_id,
l_claim_line_rec.line_number,
l_claim_line_rec.split_from_claim_line_id,
l_claim_line_rec.amount,
l_claim_line_rec.claim_currency_amount,
l_claim_line_rec.acctd_amount,
l_claim_line_rec.currency_code,
l_claim_line_rec.exchange_rate_type,
l_claim_line_rec.exchange_rate_date,
l_claim_line_rec.exchange_rate,
l_claim_line_rec.set_of_books_id,
l_valid_flag,
l_claim_line_rec.source_object_id,
l_claim_line_rec.source_object_line_id,
l_claim_line_rec.source_object_class,
l_claim_line_rec.source_object_type_id,
l_claim_line_rec.plan_id,
l_claim_line_rec.offer_id,
l_claim_line_rec.utilization_id,
l_claim_line_rec.payment_method,
l_claim_line_rec.payment_reference_id,
l_claim_line_rec.payment_reference_number,
l_claim_line_rec.payment_reference_date,
l_claim_line_rec.voucher_id,
l_claim_line_rec.voucher_number,
l_claim_line_rec.payment_status,
l_claim_line_rec.approved_flag,
l_claim_line_rec.approved_date,
l_claim_line_rec.approved_by,
l_claim_line_rec.settled_date,
l_claim_line_rec.settled_by,
l_claim_line_rec.performance_complete_flag,
l_claim_line_rec.performance_attached_flag,
l_claim_line_rec.select_cust_children_flag,
l_claim_line_rec.item_id,
l_claim_line_rec.item_description,
l_claim_line_rec.quantity,
l_claim_line_rec.quantity_uom,
l_claim_line_rec.rate,
l_claim_line_rec.activity_type,
l_claim_line_rec.activity_id,
l_claim_line_rec.related_cust_account_id,
l_claim_line_rec.buy_group_cust_account_id,
l_claim_line_rec.relationship_type,
l_claim_line_rec.earnings_associated_flag,
l_claim_line_rec.comments,
l_claim_line_rec.tax_code,
l_claim_line_rec.credit_to,
l_claim_line_rec.attribute_category,
l_claim_line_rec.attribute1,
l_claim_line_rec.attribute2,
l_claim_line_rec.attribute3,
l_claim_line_rec.attribute4,
l_claim_line_rec.attribute5,
l_claim_line_rec.attribute6,
l_claim_line_rec.attribute7,
l_claim_line_rec.attribute8,
l_claim_line_rec.attribute9,
l_claim_line_rec.attribute10,
l_claim_line_rec.attribute11,
l_claim_line_rec.attribute12,
l_claim_line_rec.attribute13,
l_claim_line_rec.attribute14,
l_claim_line_rec.attribute15,
l_org_id, -- ORG_ID
l_claim_line_rec.sale_date,
l_claim_line_rec.item_type,
l_claim_line_rec.tax_amount,
l_claim_line_rec.claim_curr_tax_amount,
l_claim_line_rec.acctd_tax_amount, --Bug4489415
l_claim_line_rec.activity_line_id,
l_claim_line_rec.offer_type,
l_claim_line_rec.prorate_earnings_flag,
l_claim_line_rec.earnings_end_date,
l_claim_line_rec.buy_group_party_id,
l_claim_line_rec.dpp_cust_account_id, --12.1 Enhancement : Price Protection
l_claim_line_rec.batch_line_id , --Bugfix : 7811671
l_claim_line_rec.activity_name --Hbandi added code for resolving the bug 10164003
);
PROCEDURE Delete_Claim_Line_Tbl(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validation_level IN NUMBER := FND_API.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_line_tbl IN claim_line_tbl_type
,p_change_object_version IN VARCHAR2 := FND_API.g_false
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Claim_Line_Tbl';
SELECT object_version_number
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT amount_remaining, claim_class
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT nvl(sum(claim_currency_amount), 0)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SAVEPOINT Delete_Claim_Line_Tbl;
Delete_Claim_Line(
p_api_version => 1.0
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_claim_line_id => l_claim_line_id
, p_object_version => l_object_version
, p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
);
ROLLBACK TO Delete_Claim_Line_Tbl;
ROLLBACK TO Delete_Claim_Line_Tbl;
ROLLBACK TO Delete_Claim_Line_Tbl;
END Delete_Claim_Line_Tbl;
PROCEDURE Delete_Claim_Line(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_commit IN VARCHAR2 := FND_API.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_claim_line_id IN NUMBER
,p_object_version IN NUMBER
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Claim_Line';
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT amount_remaining
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id
AND claim_line_id <> cv_line_id;
SELECT claim_line_util_id
, object_version_number
, currency_code
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SAVEPOINT Delete_Claim_Line;
OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_line_util_tbl => l_line_util_tbl
,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index => l_error_index
);
OZF_Utility_PVT.debug_message(l_full_name ||': delete');
OZF_Utility_PVT.debug_message(p_claim_line_id ||': delete : p_claim_line_id');
OZF_Utility_PVT.debug_message(p_object_version ||': delete : p_object_version');
DELETE FROM ozf_claim_lines_all
WHERE claim_line_id = p_claim_line_id
AND object_version_number = p_object_version;
ROLLBACK TO Delete_Claim_Line;
ROLLBACK TO Delete_Claim_Line;
ROLLBACK TO Delete_Claim_Line;
END Delete_Claim_Line;
SELECT claim_line_id
FROM ozf_claim_lines_all
WHERE claim_line_id = p_claim_line_id
AND object_version_number = p_object_version
FOR UPDATE OF claim_line_id NOWAIT;
SELECT NVL(source_object_id, FND_API.g_miss_num)
, NVL(source_object_class, FND_API.g_miss_char)
, NVL(source_object_type_id, FND_API.g_miss_num)
, NVL(plan_id, FND_API.g_miss_num)
, NVL(item_id, FND_API.g_miss_num)
, NVL(item_description, FND_API.g_miss_char)
, NVL(activity_type, FND_API.g_miss_char)
, NVL(activity_id, FND_API.g_miss_num)
, earnings_associated_flag
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
PROCEDURE Update_Claim_Line_Tbl(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validation_level IN NUMBER := FND_API.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_line_tbl IN claim_line_tbl_type
,p_change_object_version IN VARCHAR2 := FND_API.g_false
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Line_Tbl';
SELECT object_version_number
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT amount_remaining
, set_of_books_id
, currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
, claim_class
, offer_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT quantity_invoiced
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = cv_src_obj_line_id;
SELECT claim_currency_amount, currency_code
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT NVL(SUM(claim_currency_amount), 0)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SAVEPOINT Update_Claim_Line_Tbl;
l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
Update_Claim_Line(
p_api_version => 1.0
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, p_validation_level => p_validation_level
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_claim_line_rec => l_claim_line_rec
, p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
, x_object_version => l_object_version
);
ROLLBACK TO Update_Claim_Line_Tbl;
ROLLBACK TO Update_Claim_Line_Tbl;
ROLLBACK TO Update_Claim_Line_Tbl;
END Update_Claim_Line_Tbl;
PROCEDURE Update_Claim_Line(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validation_level IN NUMBER := FND_API.g_valid_level_full
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_claim_line_rec IN claim_line_rec_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_object_version OUT NOCOPY NUMBER
)
IS
-- Cursor to get claim amount
-- ER#9453443
CURSOR c_claim_amount(cv_claim_id IN NUMBER) IS
SELECT amount_remaining
, claim_class, offer_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT payment_method
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT nvl(SUM(acctd_amount),0), nvl(SUM(amount),0)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SELECT SUM(claim_currency_amount)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id
AND claim_line_id <> cv_line_id;
SELECT set_of_books_id
, currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT claim_line_util_id
, object_version_number
, currency_code
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Line';
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SAVEPOINT Update_Claim_Line;
l_last_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
OZF_CLAIM_SETTLEMENT_VAL_PVT.Default_Claim_Line(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_x_claim_line_rec => l_claim_line_rec
);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
Check_Claim_Line_Record(
p_claim_line_rec => p_claim_line_rec,
p_complete_rec => l_claim_line_rec,
x_return_status => l_return_status
);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
------------------ checking quantity * rate -------------------
IF (p_claim_line_rec.quantity IS NOT NULL AND
p_claim_line_rec.quantity <> FND_API.g_miss_num) AND
(p_claim_line_rec.rate IS NOT NULL
AND p_claim_line_rec.rate <> FND_API.g_miss_num)
THEN
l_claim_line_rec.claim_currency_amount := l_claim_line_rec.quantity * l_claim_line_rec.rate;
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
OPEN c_claim_default_exc(l_claim_line_rec.claim_id);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
-- get total of existing line amount (sum of claim_currency_amount)
OPEN c_line_sum_amt(l_claim_line_rec.claim_id, l_claim_line_rec.claim_line_id);
OZF_Claim_Accrual_PVT.Delete_Line_Util_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_line_util_tbl => l_line_util_tbl
,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index => l_error_index
);
IF l_claim_line_rec.update_from_tbl_flag = FND_API.g_false THEN
OZF_CLAIM_SETTLEMENT_VAL_PVT.Validate_Claim_Line(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_claim_line_rec => l_claim_line_rec
);
OZF_Utility_PVT.debug_message(l_full_name ||': update');
UPDATE ozf_claim_lines_all SET
object_version_number = l_object_version_number,
last_update_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_update_date = SYSDATE,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
created_from = l_claim_line_rec.created_from,
claim_id = l_claim_line_rec.claim_id,
line_number = l_claim_line_rec.line_number,
split_from_claim_line_id = l_claim_line_rec.split_from_claim_line_id,
amount = l_claim_line_rec.amount,
claim_currency_amount = l_claim_line_rec.claim_currency_amount,
acctd_amount = l_claim_line_rec.acctd_amount,
currency_code = l_claim_line_rec.currency_code,
exchange_rate_type = l_claim_line_rec.exchange_rate_type,
exchange_rate_date = l_claim_line_rec.exchange_rate_date,
exchange_rate = l_claim_line_rec.exchange_rate,
set_of_books_id = l_claim_line_rec.set_of_books_id,
valid_flag = l_claim_line_rec.valid_flag,
source_object_id = l_claim_line_rec.source_object_id,
source_object_line_id = l_claim_line_rec.source_object_line_id,
source_object_class = l_claim_line_rec.source_object_class,
source_object_type_id = l_claim_line_rec.source_object_type_id,
plan_id = l_claim_line_rec.plan_id,
offer_id = l_claim_line_rec.offer_id,
utilization_id = l_claim_line_rec.utilization_id,
payment_method = l_claim_line_rec.payment_method,
payment_reference_id = l_claim_line_rec.payment_reference_id,
payment_reference_number = l_claim_line_rec.payment_reference_number,
payment_reference_date = l_claim_line_rec.payment_reference_date,
voucher_id = l_claim_line_rec.voucher_id,
voucher_number = l_claim_line_rec.voucher_number,
payment_status = l_claim_line_rec.payment_status,
approved_flag = l_claim_line_rec.approved_flag,
approved_date = l_claim_line_rec.approved_date,
approved_by = l_claim_line_rec.approved_by,
settled_date = l_claim_line_rec.settled_date,
settled_by = l_claim_line_rec.settled_by,
performance_complete_flag = l_claim_line_rec.performance_complete_flag,
performance_attached_flag = l_claim_line_rec.performance_attached_flag,
select_cust_children_flag = l_claim_line_rec.select_cust_children_flag,
item_id = l_claim_line_rec.item_id,
item_description = l_claim_line_rec.item_description,
quantity = l_claim_line_rec.quantity,
quantity_uom = l_claim_line_rec.quantity_uom,
rate = l_claim_line_rec.rate,
activity_type = l_claim_line_rec.activity_type,
activity_id = l_claim_line_rec.activity_id,
related_cust_account_id = l_claim_line_rec.related_cust_account_id,
buy_group_cust_account_id = l_claim_line_rec.buy_group_cust_account_id,
relationship_type = l_claim_line_rec.relationship_type,
earnings_associated_flag = l_claim_line_rec.earnings_associated_flag,
comments = l_claim_line_rec.comments,
tax_code = l_claim_line_rec.tax_code,
credit_to = l_claim_line_rec.credit_to,
attribute_category = l_claim_line_rec.attribute_category,
attribute1 = l_claim_line_rec.attribute1,
attribute2 = l_claim_line_rec.attribute2,
attribute3 = l_claim_line_rec.attribute3,
attribute4 = l_claim_line_rec.attribute4,
attribute5 = l_claim_line_rec.attribute5,
attribute6 = l_claim_line_rec.attribute6,
attribute7 = l_claim_line_rec.attribute7,
attribute8 = l_claim_line_rec.attribute8,
attribute9 = l_claim_line_rec.attribute9,
attribute10 = l_claim_line_rec.attribute10,
attribute11 = l_claim_line_rec.attribute11,
attribute12 = l_claim_line_rec.attribute12,
attribute13 = l_claim_line_rec.attribute13,
attribute14 = l_claim_line_rec.attribute14,
attribute15 = l_claim_line_rec.attribute15,
sale_date = l_claim_line_rec.sale_date,
item_type = l_claim_line_rec.item_type,
tax_amount = l_claim_line_rec.tax_amount,
acctd_tax_amount = l_claim_line_rec.acctd_tax_amount,
claim_curr_tax_amount = l_claim_line_rec.claim_curr_tax_amount,
activity_line_id = l_claim_line_rec.activity_line_id,
offer_type = l_claim_line_rec.offer_type,
prorate_earnings_flag = l_claim_line_rec.prorate_earnings_flag,
earnings_end_date = l_claim_line_rec.earnings_end_date,
buy_group_party_id = l_claim_line_rec.buy_group_party_id,
--12.1 Enhancement : Price Protection
dpp_cust_account_id = l_claim_line_rec.dpp_cust_account_id,
activity_name = l_claim_line_rec.activity_name-- Hbandi added code for resolving the bug 10164003
WHERE claim_line_id = l_claim_line_rec.claim_line_id
AND object_version_number = l_claim_line_rec.object_version_number;
ROLLBACK TO Update_Claim_Line;
ROLLBACK TO Update_Claim_Line;
ROLLBACK TO Update_Claim_Line;
END Update_Claim_Line;
SELECT ocs.tax_for
FROM ozf_claim_sttlmnt_methods_all ocs,
ozf_claims_all oc
WHERE claim_id = cv_claim_id
AND ocs.claim_class = oc.claim_class
AND NVL(ocs.source_object_class,'NULL') = NVL(oc.source_object_class,'NULL')
AND ocs.settlement_method = oc.payment_method ;
SELECT lookup_code
FROM fnd_lookups lkp
WHERE lkp.lookup_type in ( 'ZX_INPUT_CLASSIFICATIONS', 'ZX_WEB_EXP_TAX_CLASSIFICATIONS' )
AND lkp.enabled_flag = 'Y' ;
SELECT lookup_code
FROM fnd_lookups lkp
WHERE lkp.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
AND lkp.enabled_flag = 'Y' ;
SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = cv_customer_trx_line_id;
SELECT object_version_number,
claim_id,
line_number,
split_from_claim_line_id,
amount,
claim_currency_amount,
acctd_amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
set_of_books_id,
valid_flag,
source_object_id,
source_object_line_id,
source_object_class,
source_object_type_id,
plan_id,
offer_id,
utilization_id,
payment_method,
payment_reference_id,
payment_reference_number,
payment_reference_date,
voucher_id,
voucher_number,
payment_status,
approved_flag,
approved_date,
approved_by,
settled_date,
settled_by,
performance_complete_flag,
performance_attached_flag,
select_cust_children_flag,
item_id,
item_description,
quantity,
quantity_uom,
rate,
activity_type,
activity_id,
related_cust_account_id,
buy_group_cust_account_id,
relationship_type,
earnings_associated_flag,
comments,
tax_code,
credit_to,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
sale_date,
item_type,
tax_amount,
claim_curr_tax_amount,
acctd_tax_amount, -- Bug4489415
activity_line_id,
offer_type,
prorate_earnings_flag,
earnings_end_date,
buy_group_party_id,
dpp_cust_account_id --12.1 Enhancement : Price Protection
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
IF p_claim_line_rec.select_cust_children_flag = FND_API.G_MISS_CHAR THEN
x_complete_rec.select_cust_children_flag := NULL;
IF p_claim_line_rec.select_cust_children_flag IS NULL THEN
x_complete_rec.select_cust_children_flag := l_claim_line_rec.select_cust_children_flag;
IF p_claim_line_rec.update_from_tbl_flag = FND_API.G_MISS_CHAR THEN
x_complete_rec.update_from_tbl_flag := NULL;
IF p_claim_line_rec.update_from_tbl_flag IS NULL THEN
x_complete_rec.update_from_tbl_flag := FND_API.g_false;
SELECT object_version_number,
claim_id,
line_number,
split_from_claim_line_id,
amount,
claim_currency_amount,
acctd_amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
set_of_books_id,
valid_flag,
source_object_id,
source_object_line_id,
source_object_class,
source_object_type_id,
plan_id,
offer_id,
utilization_id,
payment_method,
payment_reference_id,
payment_reference_number,
payment_reference_date,
voucher_id,
voucher_number,
payment_status,
approved_flag,
approved_date,
approved_by,
settled_date,
settled_by,
performance_complete_flag,
performance_attached_flag,
select_cust_children_flag,
item_id,
item_description,
quantity,
quantity_uom,
rate,
activity_type,
activity_id,
related_cust_account_id,
buy_group_cust_account_id,
relationship_type,
earnings_associated_flag,
comments,
tax_code,
credit_to,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
sale_date,
item_type,
tax_amount,
claim_curr_tax_amount,
activity_line_id,
offer_type,
prorate_earnings_flag,
earnings_end_date,
buy_group_party_id,
org_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_line_id;
SELECT offer_id,
activity_type,
product_level_type,
product_id,
SUM(amount),
SUM(acctd_amount),
SUM(util_curr_amount)
FROM
(SELECT fu.plan_id offer_id,
fu.plan_type activity_type,
fu.product_level_type product_level_type,
fu.product_id product_id,
utl.amount amount,
utl.acctd_amount acctd_amount,
utl.util_curr_amount util_curr_amount
FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util utl
WHERE utl.utilization_id = fu.utilization_id
AND utl.claim_line_id = cv_line_id
AND utl.utilization_id <> -1
UNION ALL
SELECT act.act_product_used_by_id offer_id,
'OFFR' activity_type,
act.level_type_code product_level_type,
NVL(act.inventory_item_id, act.category_id) product_id,
utl.amount amount,
utl.acctd_amount acctd_amount,
NVL(utl.util_curr_amount,utl.amount) util_curr_amount
FROM ams_act_products act, ozf_claim_lines_util utl
WHERE utl.activity_product_id = act.activity_product_id
AND utl.claim_line_id = cv_line_id
AND utl.utilization_id = -1)
GROUP BY offer_id,activity_type, product_level_type, product_id;
SELECT status_code
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT MAX(line_number)
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT sum(amount), sum(acctd_amount)
FROM ozf_claim_lines_util_all
WHERE claim_line_id = cv_claim_line_id;
SELECT nvl(amount,0), nvl(acctd_amount,0)
FROM ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;
UPDATE ozf_claim_lines
SET item_id = l_util_product_id,
item_type = l_util_product_level,
activity_id = NVL(l_old_line_rec.activity_id,l_offer_id),
activity_type = NVL(l_old_line_rec.activity_type,l_activity_type),
offer_id = NVL(l_old_line_rec.offer_id,l_offer_id),
quantity_uom = l_uom_code
WHERE claim_line_id = p_claim_line_id;
UPDATE ozf_claim_lines
SET amount = amount - l_claim_line_amount,
acctd_amount = acctd_amount - l_acctd_amount,
claim_currency_amount= claim_currency_amount - l_claim_line_amount
WHERE claim_line_id = p_claim_line_id;
l_claim_line_rec.select_cust_children_flag := l_old_line_rec.select_cust_children_flag;
l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
UPDATE ozf_claim_lines_util_all
SET claim_line_id = l_claim_line_id
WHERE claim_line_util_id IN ( SELECT util.claim_line_util_id
FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util_all util
WHERE fu.utilization_id = util.utilization_id
AND fu.product_id IS NULL
AND util.claim_line_id = p_claim_line_id
AND fu.product_level_type IS NULL
AND fu.plan_id = l_offer_id
AND util.utilization_id > -1);
UPDATE ozf_claim_lines_util_all
SET claim_line_id = l_claim_line_id
WHERE claim_line_util_id in ( SELECT util.claim_line_util_id
FROM ozf_funds_utilized_all_b fu, ozf_claim_lines_util_all util
WHERE fu.utilization_id = util.utilization_id
AND fu.product_id = l_util_product_id
AND util.claim_line_id = p_claim_line_id
AND fu.product_level_type = l_util_product_level
AND fu.plan_id = l_offer_id
AND util.utilization_id > -1);
UPDATE ozf_claim_lines_util_all
SET claim_line_id = l_claim_line_id
WHERE claim_line_util_id IN ( SELECT utl.claim_line_util_id
FROM ams_act_products act, ozf_claim_lines_util_all utl
WHERE utl.activity_product_id = act.activity_product_id
AND utl.claim_line_id = p_claim_line_id
AND act.level_type_code = l_util_product_level
AND NVL(act.inventory_item_id, act.category_id) = l_util_product_id
AND act.act_product_used_by_id = l_offer_id
AND utilization_id = -1);
update ozf_claim_lines_all
set acctd_amount = l_sum_util_acctd_amount
where claim_line_id = l_claim_line_id;
END IF; -- update/create claim line
PROCEDURE Delete_All_Association_AssoUI(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_claim_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_All_Association_AssoUI';
SELECT distinct cln.claim_line_id, cln.object_version_number
FROM ozf_claim_lines_all cln, ozf_claim_lines_util_all clu
WHERE clu.claim_line_id = cln.claim_line_id
AND claim_id = p_id;
SAVEPOINT delete_all_association;
OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_tbl => l_claim_line_tbl
,p_change_object_version => FND_API.g_false
,x_error_index => l_error_index
);
ROLLBACK TO delete_all_association;
ROLLBACK TO delete_all_association;
ROLLBACK TO delete_all_association;
END Delete_All_Association_AssoUI;