The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT OZF_CLAIMS_INT_ALL_s.NEXTVAL
FROM dual;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1 FROM OZF_CLAIMS_INT_ALL
WHERE INTERFACE_CLAIM_ID = l_id);
OZF_CLAIMS_INT_PKG.Insert_Row(
px_interface_claim_id => l_interface_claim_id,
px_object_version_number => l_object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => p_claims_int_rec.request_id,
p_program_application_id => p_claims_int_rec.program_application_id,
p_program_update_date => p_claims_int_rec.program_update_date,
p_program_id => p_claims_int_rec.program_id,
p_created_from => p_claims_int_rec.created_from,
p_batch_id => p_claims_int_rec.batch_id,
p_claim_id => p_claims_int_rec.claim_id,
p_claim_number => p_claims_int_rec.claim_number,
p_claim_type_id => p_claims_int_rec.claim_type_id,
p_claim_class => p_claims_int_rec.claim_class,
p_claim_date => p_claims_int_rec.claim_date,
p_due_date => p_claims_int_rec.due_date,
p_owner_id => p_claims_int_rec.owner_id,
p_history_event => p_claims_int_rec.history_event,
p_history_event_date => p_claims_int_rec.history_event_date,
p_history_event_description => p_claims_int_rec.history_event_description,
p_split_from_claim_id => p_claims_int_rec.split_from_claim_id,
p_duplicate_claim_id => p_claims_int_rec.duplicate_claim_id,
p_split_date => p_claims_int_rec.split_date,
p_root_claim_id => p_claims_int_rec.root_claim_id,
p_amount => p_claims_int_rec.amount,
p_amount_adjusted => p_claims_int_rec.amount_adjusted,
p_amount_remaining => p_claims_int_rec.amount_remaining,
p_amount_settled => p_claims_int_rec.amount_settled,
p_acctd_amount => p_claims_int_rec.acctd_amount,
p_acctd_amount_remaining => p_claims_int_rec.acctd_amount_remaining,
p_tax_amount => p_claims_int_rec.tax_amount,
p_tax_code => p_claims_int_rec.tax_code,
p_tax_calculation_flag => p_claims_int_rec.tax_calculation_flag,
p_currency_code => p_claims_int_rec.currency_code,
p_exchange_rate_type => p_claims_int_rec.exchange_rate_type,
p_exchange_rate_date => p_claims_int_rec.exchange_rate_date,
p_exchange_rate => p_claims_int_rec.exchange_rate,
p_set_of_books_id => p_claims_int_rec.set_of_books_id,
p_original_claim_date => p_claims_int_rec.original_claim_date,
p_source_object_id => p_claims_int_rec.source_object_id,
p_source_object_class => p_claims_int_rec.source_object_class,
p_source_object_type_id => p_claims_int_rec.source_object_type_id,
p_source_object_number => p_claims_int_rec.source_object_number,
p_cust_account_id => p_claims_int_rec.cust_account_id,
p_cust_billto_acct_site_id => p_claims_int_rec.cust_billto_acct_site_id,
p_cust_shipto_acct_site_id => p_claims_int_rec.cust_shipto_acct_site_id,
p_location_id => p_claims_int_rec.location_id,
p_pay_related_account_flag => p_claims_int_rec.pay_related_account_flag,
p_related_cust_account_id => p_claims_int_rec.related_cust_account_id,
p_related_site_use_id => p_claims_int_rec.related_site_use_id,
p_relationship_type => p_claims_int_rec.relationship_type,
p_vendor_id => p_claims_int_rec.vendor_id,
p_vendor_site_id => p_claims_int_rec.vendor_site_id,
p_reason_type => p_claims_int_rec.reason_type,
p_reason_code_id => p_claims_int_rec.reason_code_id,
p_task_template_group_id => p_claims_int_rec.task_template_group_id,
p_status_code => p_claims_int_rec.status_code,
p_user_status_id => p_claims_int_rec.user_status_id,
p_sales_rep_id => p_claims_int_rec.sales_rep_id,
p_collector_id => p_claims_int_rec.collector_id,
p_contact_id => p_claims_int_rec.contact_id,
p_broker_id => p_claims_int_rec.broker_id,
p_territory_id => p_claims_int_rec.territory_id,
p_customer_ref_date => p_claims_int_rec.customer_ref_date,
p_customer_ref_number => p_claims_int_rec.customer_ref_number,
p_assigned_to => p_claims_int_rec.assigned_to,
p_receipt_id => p_claims_int_rec.receipt_id,
p_receipt_number => p_claims_int_rec.receipt_number,
p_doc_sequence_id => p_claims_int_rec.doc_sequence_id,
p_doc_sequence_value => p_claims_int_rec.doc_sequence_value,
p_gl_date => p_claims_int_rec.gl_date,
p_payment_method => p_claims_int_rec.payment_method,
p_voucher_id => p_claims_int_rec.voucher_id,
p_voucher_number => p_claims_int_rec.voucher_number,
p_payment_reference_id => p_claims_int_rec.payment_reference_id,
p_payment_reference_number => p_claims_int_rec.payment_reference_number,
p_payment_reference_date => p_claims_int_rec.payment_reference_date,
p_payment_status => p_claims_int_rec.payment_status,
p_approved_flag => p_claims_int_rec.approved_flag,
p_approved_date => p_claims_int_rec.approved_date,
p_approved_by => p_claims_int_rec.approved_by,
p_settled_date => p_claims_int_rec.settled_date,
p_settled_by => p_claims_int_rec.settled_by,
p_effective_date => p_claims_int_rec.effective_date,
p_custom_setup_id => p_claims_int_rec.custom_setup_id,
p_task_id => p_claims_int_rec.task_id,
p_country_id => p_claims_int_rec.country_id,
p_order_type_id => p_claims_int_rec.order_type_id,
p_comments => p_claims_int_rec.comments,
p_attribute_category => p_claims_int_rec.attribute_category,
p_attribute1 => p_claims_int_rec.attribute1,
p_attribute2 => p_claims_int_rec.attribute2,
p_attribute3 => p_claims_int_rec.attribute3,
p_attribute4 => p_claims_int_rec.attribute4,
p_attribute5 => p_claims_int_rec.attribute5,
p_attribute6 => p_claims_int_rec.attribute6,
p_attribute7 => p_claims_int_rec.attribute7,
p_attribute8 => p_claims_int_rec.attribute8,
p_attribute9 => p_claims_int_rec.attribute9,
p_attribute10 => p_claims_int_rec.attribute10,
p_attribute11 => p_claims_int_rec.attribute11,
p_attribute12 => p_claims_int_rec.attribute12,
p_attribute13 => p_claims_int_rec.attribute13,
p_attribute14 => p_claims_int_rec.attribute14,
p_attribute15 => p_claims_int_rec.attribute15,
p_deduction_attribute_category => p_claims_int_rec.deduction_attribute_category,
p_deduction_attribute1 => p_claims_int_rec.deduction_attribute1,
p_deduction_attribute2 => p_claims_int_rec.deduction_attribute2,
p_deduction_attribute3 => p_claims_int_rec.deduction_attribute3,
p_deduction_attribute4 => p_claims_int_rec.deduction_attribute4,
p_deduction_attribute5 => p_claims_int_rec.deduction_attribute5,
p_deduction_attribute6 => p_claims_int_rec.deduction_attribute6,
p_deduction_attribute7 => p_claims_int_rec.deduction_attribute7,
p_deduction_attribute8 => p_claims_int_rec.deduction_attribute8,
p_deduction_attribute9 => p_claims_int_rec.deduction_attribute9,
p_deduction_attribute10 => p_claims_int_rec.deduction_attribute10,
p_deduction_attribute11 => p_claims_int_rec.deduction_attribute11,
p_deduction_attribute12 => p_claims_int_rec.deduction_attribute12,
p_deduction_attribute13 => p_claims_int_rec.deduction_attribute13,
p_deduction_attribute14 => p_claims_int_rec.deduction_attribute14,
p_deduction_attribute15 => p_claims_int_rec.deduction_attribute15,
px_org_id => l_org_id,
p_customer_reason => p_claims_int_rec.customer_reason,
p_ship_to_cust_account_id => p_claims_int_rec.ship_to_cust_account_id
);
PROCEDURE Update_Claims_Int(
p_api_version_number 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_claims_int_rec IN claims_int_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
CURSOR c_get_claims_int(p_interface_claim_id NUMBER) IS
SELECT *
FROM OZF_CLAIMS_INT_ALL
WHERE interface_claim_id = p_interface_claim_id;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Claims_Int';
SAVEPOINT UPDATE_Claims_Int_PVT;
OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
'Private API: Calling update table handler');
OZF_CLAIMS_INT_PKG.Update_Row(
p_interface_claim_id => p_claims_int_rec.interface_claim_id,
p_object_version_number => p_claims_int_rec.object_version_number,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => p_claims_int_rec.request_id,
p_program_application_id => p_claims_int_rec.program_application_id,
p_program_update_date => p_claims_int_rec.program_update_date,
p_program_id => p_claims_int_rec.program_id,
p_created_from => p_claims_int_rec.created_from,
p_batch_id => p_claims_int_rec.batch_id,
p_claim_id => p_claims_int_rec.claim_id,
p_claim_number => p_claims_int_rec.claim_number,
p_claim_type_id => p_claims_int_rec.claim_type_id,
p_claim_class => p_claims_int_rec.claim_class,
p_claim_date => p_claims_int_rec.claim_date,
p_due_date => p_claims_int_rec.due_date,
p_owner_id => p_claims_int_rec.owner_id,
p_history_event => p_claims_int_rec.history_event,
p_history_event_date => p_claims_int_rec.history_event_date,
p_history_event_description => p_claims_int_rec.history_event_description,
p_split_from_claim_id => p_claims_int_rec.split_from_claim_id,
p_duplicate_claim_id => p_claims_int_rec.duplicate_claim_id,
p_split_date => p_claims_int_rec.split_date,
p_root_claim_id => p_claims_int_rec.root_claim_id,
p_amount => p_claims_int_rec.amount,
p_amount_adjusted => p_claims_int_rec.amount_adjusted,
p_amount_remaining => p_claims_int_rec.amount_remaining,
p_amount_settled => p_claims_int_rec.amount_settled,
p_acctd_amount => p_claims_int_rec.acctd_amount,
p_acctd_amount_remaining => p_claims_int_rec.acctd_amount_remaining,
p_tax_amount => p_claims_int_rec.tax_amount,
p_tax_code => p_claims_int_rec.tax_code,
p_tax_calculation_flag => p_claims_int_rec.tax_calculation_flag,
p_currency_code => p_claims_int_rec.currency_code,
p_exchange_rate_type => p_claims_int_rec.exchange_rate_type,
p_exchange_rate_date => p_claims_int_rec.exchange_rate_date,
p_exchange_rate => p_claims_int_rec.exchange_rate,
p_set_of_books_id => p_claims_int_rec.set_of_books_id,
p_original_claim_date => p_claims_int_rec.original_claim_date,
p_source_object_id => p_claims_int_rec.source_object_id,
p_source_object_class => p_claims_int_rec.source_object_class,
p_source_object_type_id => p_claims_int_rec.source_object_type_id,
p_source_object_number => p_claims_int_rec.source_object_number,
p_cust_account_id => p_claims_int_rec.cust_account_id,
p_cust_billto_acct_site_id => p_claims_int_rec.cust_billto_acct_site_id,
p_cust_shipto_acct_site_id => p_claims_int_rec.cust_shipto_acct_site_id,
p_location_id => p_claims_int_rec.location_id,
p_pay_related_account_flag => p_claims_int_rec.pay_related_account_flag,
p_related_cust_account_id => p_claims_int_rec.related_cust_account_id,
p_related_site_use_id => p_claims_int_rec.related_site_use_id,
p_relationship_type => p_claims_int_rec.relationship_type,
p_vendor_id => p_claims_int_rec.vendor_id,
p_vendor_site_id => p_claims_int_rec.vendor_site_id,
p_reason_type => p_claims_int_rec.reason_type,
p_reason_code_id => p_claims_int_rec.reason_code_id,
p_task_template_group_id => p_claims_int_rec.task_template_group_id,
p_status_code => p_claims_int_rec.status_code,
p_user_status_id => p_claims_int_rec.user_status_id,
p_sales_rep_id => p_claims_int_rec.sales_rep_id,
p_collector_id => p_claims_int_rec.collector_id,
p_contact_id => p_claims_int_rec.contact_id,
p_broker_id => p_claims_int_rec.broker_id,
p_territory_id => p_claims_int_rec.territory_id,
p_customer_ref_date => p_claims_int_rec.customer_ref_date,
p_customer_ref_number => p_claims_int_rec.customer_ref_number,
p_assigned_to => p_claims_int_rec.assigned_to,
p_receipt_id => p_claims_int_rec.receipt_id,
p_receipt_number => p_claims_int_rec.receipt_number,
p_doc_sequence_id => p_claims_int_rec.doc_sequence_id,
p_doc_sequence_value => p_claims_int_rec.doc_sequence_value,
p_gl_date => p_claims_int_rec.gl_date,
p_payment_method => p_claims_int_rec.payment_method,
p_voucher_id => p_claims_int_rec.voucher_id,
p_voucher_number => p_claims_int_rec.voucher_number,
p_payment_reference_id => p_claims_int_rec.payment_reference_id,
p_payment_reference_number => p_claims_int_rec.payment_reference_number,
p_payment_reference_date => p_claims_int_rec.payment_reference_date,
p_payment_status => p_claims_int_rec.payment_status,
p_approved_flag => p_claims_int_rec.approved_flag,
p_approved_date => p_claims_int_rec.approved_date,
p_approved_by => p_claims_int_rec.approved_by,
p_settled_date => p_claims_int_rec.settled_date,
p_settled_by => p_claims_int_rec.settled_by,
p_effective_date => p_claims_int_rec.effective_date,
p_custom_setup_id => p_claims_int_rec.custom_setup_id,
p_task_id => p_claims_int_rec.task_id,
p_country_id => p_claims_int_rec.country_id,
p_order_type_id => p_claims_int_rec.order_type_id,
p_comments => p_claims_int_rec.comments,
p_attribute_category => p_claims_int_rec.attribute_category,
p_attribute1 => p_claims_int_rec.attribute1,
p_attribute2 => p_claims_int_rec.attribute2,
p_attribute3 => p_claims_int_rec.attribute3,
p_attribute4 => p_claims_int_rec.attribute4,
p_attribute5 => p_claims_int_rec.attribute5,
p_attribute6 => p_claims_int_rec.attribute6,
p_attribute7 => p_claims_int_rec.attribute7,
p_attribute8 => p_claims_int_rec.attribute8,
p_attribute9 => p_claims_int_rec.attribute9,
p_attribute10 => p_claims_int_rec.attribute10,
p_attribute11 => p_claims_int_rec.attribute11,
p_attribute12 => p_claims_int_rec.attribute12,
p_attribute13 => p_claims_int_rec.attribute13,
p_attribute14 => p_claims_int_rec.attribute14,
p_attribute15 => p_claims_int_rec.attribute15,
p_deduction_attribute_category => p_claims_int_rec.deduction_attribute_category,
p_deduction_attribute1 => p_claims_int_rec.deduction_attribute1,
p_deduction_attribute2 => p_claims_int_rec.deduction_attribute2,
p_deduction_attribute3 => p_claims_int_rec.deduction_attribute3,
p_deduction_attribute4 => p_claims_int_rec.deduction_attribute4,
p_deduction_attribute5 => p_claims_int_rec.deduction_attribute5,
p_deduction_attribute6 => p_claims_int_rec.deduction_attribute6,
p_deduction_attribute7 => p_claims_int_rec.deduction_attribute7,
p_deduction_attribute8 => p_claims_int_rec.deduction_attribute8,
p_deduction_attribute9 => p_claims_int_rec.deduction_attribute9,
p_deduction_attribute10 => p_claims_int_rec.deduction_attribute10,
p_deduction_attribute11 => p_claims_int_rec.deduction_attribute11,
p_deduction_attribute12 => p_claims_int_rec.deduction_attribute12,
p_deduction_attribute13 => p_claims_int_rec.deduction_attribute13,
p_deduction_attribute14 => p_claims_int_rec.deduction_attribute14,
p_deduction_attribute15 => p_claims_int_rec.deduction_attribute15,
p_org_id => p_claims_int_rec.org_id,
p_customer_reason => p_claims_int_rec.customer_reason,
p_ship_to_cust_account_id => p_claims_int_rec.ship_to_cust_account_id
);
ROLLBACK TO UPDATE_Claims_Int_PVT;
ROLLBACK TO UPDATE_Claims_Int_PVT;
ROLLBACK TO UPDATE_Claims_Int_PVT;
End Update_Claims_Int;
PROCEDURE Delete_Claims_Int(
p_api_version_number 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_interface_claim_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Claims_Int';
SAVEPOINT DELETE_Claims_Int_PVT;
OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
OZF_CLAIMS_INT_PKG.Delete_Row(
p_INTERFACE_CLAIM_ID => p_INTERFACE_CLAIM_ID);
ROLLBACK TO DELETE_Claims_Int_PVT;
ROLLBACK TO DELETE_Claims_Int_PVT;
ROLLBACK TO DELETE_Claims_Int_PVT;
End Delete_Claims_Int;
SELECT INTERFACE_CLAIM_ID
FROM OZF_CLAIMS_INT_ALL
WHERE INTERFACE_CLAIM_ID = p_INTERFACE_CLAIM_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
IF p_claims_int_rec.last_update_date = FND_API.g_miss_date OR
p_claims_int_rec.last_update_date IS NULL
THEN
OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );
IF p_claims_int_rec.last_updated_by = FND_API.g_miss_num OR
p_claims_int_rec.last_updated_by IS NULL THEN
OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );
IF p_claims_int_rec.last_update_date IS NULL THEN
OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATE_DATE' );
IF p_claims_int_rec.last_updated_by IS NULL THEN
OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LAST_UPDATED_BY' );
SELECT *
FROM ozf_claims_int_all
WHERE interface_claim_id = p_claims_int_rec.interface_claim_id;
IF p_claims_int_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := NULL;
IF p_claims_int_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_claims_int_rec.last_update_date;
IF p_claims_int_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := NULL;
IF p_claims_int_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_claims_int_rec.last_updated_by;
IF p_claims_int_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := NULL;
IF p_claims_int_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_claims_int_rec.last_update_login;
IF p_claims_int_rec.program_update_date = FND_API.g_miss_date THEN
x_complete_rec.program_update_date := NULL;
IF p_claims_int_rec.program_update_date IS NULL THEN
x_complete_rec.program_update_date := l_claims_int_rec.program_update_date;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
delete from ozf_claims_all where claim_id = p_claim_id;
delete from ozf_claim_lines_all where claim_id = p_claim_id;
FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_DELETE_ERR');
SELECT *
FROM ozf_claims_int_all
WHERE claim_id is null;
SELECT *
FROM ozf_claim_lines_int_all
WHERE interface_claim_id = cv_claim_id;
SELECT type
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_id;
SELECT claim_number
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
l_claim_rec.program_update_date := sysdate;
-- Update claim_id in Claim Interface Table --
-----------------------------------------------
OPEN c_claim_line_int(l_claim_int_rec.interface_claim_id);
l_line_rec.program_update_date
:= sysdate;
l_line_rec.select_cust_children_flag
:= l_line_int_rec.select_cust_children_flag;
-- I will update the claim interface table with the new claim id.
IF OZF_DEBUG_HIGH_ON THEN
OZF_UTILITY_PVT.debug_message('Create Claims for '|| l_claim_int_rec.interface_claim_id ||' succeed');
UPDATE OZF_CLAIMS_INT_ALL
SET CLAIM_ID = l_claim_id
WHERE INTERFACE_CLAIM_ID = l_claim_int_rec.interface_claim_id;
delete FROM ozf_claim_lines_int_all a
where exists
(select 1 from ozf_claims_int_all b
where b.claim_id is not null
and b.interface_claim_id = a.interface_claim_id);
delete from ozf_claims_int_all
where claim_id is not null;