[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(t.cp_service_transaction_id)
INTO x_first_cp_service_txn_id
FROM cs_cp_service_transactions t
WHERE t.cp_service_id = x_cp_service_id
AND t.effective_start_date <= x_terminate_effective_date
AND t.effective_end_date >= x_terminate_effective_date
AND t.transaction_type_code in ('RENEW', 'ORDER');
SELECT (t.current_end_date) - (t.effective_start_date),
(t.current_end_date) - (x_terminate_effective_date),
t.effective_end_date, t.current_end_date
INTO days_in_original_service,
days_in_terminated_service,
txn_end,
current_end
FROM cs_cp_service_transactions t
WHERE t.cp_service_transaction_id = x_first_cp_service_txn_id;
SELECT (MAX(t.service_selling_price * cp.quantity) -
SUM(NVL(t2.credit_amount,0)))
INTO available_credit
FROM cs_cp_service_transactions t,
cs_cp_service_transactions t2,
cs_cp_services s,
cs_customer_products cp
WHERE t.cp_service_transaction_id = x_first_cp_service_txn_id
AND t.cp_service_transaction_id = t2.terminated_transaction_id(+)
AND t.cp_service_id = s.cp_service_id
AND s.customer_product_id = cp.customer_product_id;
SELECT MAX(cp_service_transaction_id)
INTO term_cp_serv_txn_id
FROM cs_cp_service_transactions
WHERE transaction_type_code = 'TERMINATE'
AND terminated_transaction_id = x_first_cp_service_txn_id
AND cp_service_id = x_cp_service_id;
SELECT NVL(SUM(NVL(t.service_selling_price * cp.quantity , 0)) -
SUM(NVL(t.credit_amount, 0)),0)
INTO remain_credit_amount
FROM cs_cp_service_transactions t,
cs_cp_services s,
cs_customer_products cp
WHERE t.cp_service_transaction_id > term_cp_serv_txn_id
AND t.cp_service_id = s.cp_service_id
AND t.cp_service_id = x_cp_service_id
AND t.ra_interface_status = 'INV'
AND t.transaction_type_code in ('ORDER', 'RENEW', 'TERMINATE')
AND s.customer_product_id = cp.customer_product_id;
cp_last_update_login IN NUMBER,
cp_bill_to_contact_id IN NUMBER,
return_status OUT VARCHAR2,
return_msg OUT VARCHAR2) IS
l_ret_status VARCHAR2(1);
SELECT employee_id
INTO l_employee_id
FROM FND_USER
WHERE user_id = control_user_id;
SELECT customer_id
INTO l_customer_id
FROM cs_customer_products
WHERE customer_product_id = (SELECT customer_product_id
FROM cs_cp_services
WHERE cp_service_id = cp_cp_service_id);
p_login_id => cp_last_update_login,
p_org_id => FND_PROFILE.Value('ORG_ID'),
p_customer_id => l_customer_id,
p_contact_id => cp_bill_to_contact_id,
p_contact_lastname => NULL,
p_contact_firstname => NULL,
p_phone_area_code => NULL,
p_phone_number => NULL,
p_phone_extension => NULL,
p_fax_area_code => NULL,
p_fax_number => NULL,
p_email_address => NULL,
p_interaction_type_code => 'SRV_TER',
p_interaction_category_code => 'CS',
p_interaction_method_code => 'SYSTEM',
p_interaction_date => SYSDATE,
p_interaction_document_code => NULL,
p_source_document_id => NULL,
p_source_document_name => NULL,
p_reference_form => NULL,
p_source_document_status => NULL,
p_employee_id => l_employee_id,
p_public_flag => NULL,
p_follow_up_action => NULL,
p_notes => NULL,
p_parent_interaction_id => parent_interaction_id,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
x_interaction_id => l_interaction_id);