[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from cs_cp_services
where service_inventory_item_id+0 = p_inventory_item_id
And service_manufacturing_org_id= p_manu_org_id
And customer_product_id = p_customer_product_id
And start_date_active <> end_date_active
and ( (p_start_date <= end_date_active
and start_date_active <= p_start_date)
OR (start_date_active <= p_end_date
and p_start_date <= start_date_active)
OR (start_date_active >= p_start_date
and end_date_active <= p_end_date)
OR (start_date_active <= p_start_date
and end_date_active >= p_end_date));
select 'Y'
from so_lines_interface
where inventory_item_id+0 = p_inventory_item_id
AND customer_product_id = p_customer_product_id;
select 'Y'
from so_lines sol,
so_headers soh
where inventory_item_id+0 = p_inventory_item_id
AND customer_product_id = p_customer_product_id
AND nvl(soh.cancelled_flag,'N') = 'N'
AND nvl(sol.open_flag,'N') = 'Y';
SELECT 'x'
FROM cs_service_availability serv
WHERE serv.service_inventory_item_id = p_ord_serv_inv_item_id
AND serv.service_manufacturing_org_id = p_control_manu_org_id ;
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND NVL(avail.inventory_item_id, p_Cp_inventory_item_id)
= p_cp_inventory_item_id
AND NVL(avail.customer_id, p_cp_customer_id)
= p_cp_customer_id
AND (NVL(p_cp_revision,'-999')
BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
AND (p_order_renew_date
BETWEEN NVL(avail.start_date_active, p_order_renew_date)
AND NVL(avail.end_date_active, p_order_renew_date))
AND avail.service_available_flag = 'Y';
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
= p_cp_inventory_item_id
AND NVL(avail.customer_id, p_cp_customer_id)
= p_cp_customer_id
AND (NVL(p_cp_revision,'-999')
BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
AND (p_order_renew_date
BETWEEN NVL(avail.start_date_active, p_order_renew_date)
AND NVL(avail.end_date_active, p_order_renew_date))
AND avail.service_available_flag = 'N';
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND avail.service_available_flag = 'N';
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND avail.service_available_flag = 'Y';
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
= p_cp_inventory_item_id
AND NVL(avail.customer_id, p_cp_customer_id)
= p_cp_customer_id
AND (NVL(p_cp_revision,'-999')
BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
AND avail.service_available_flag = 'N';
SELECT 'x'
FROM cs_service_availability avail
WHERE avail.service_inventory_item_id = p_ord_serv_inv_item_id
AND avail.service_manufacturing_org_id = p_control_manu_org_id
AND NVL(avail.inventory_item_id, p_cp_inventory_item_id)
= p_cp_inventory_item_id
AND NVL(avail.customer_id, p_cp_customer_id)
= p_cp_customer_id
AND (NVL(p_cp_revision,'-999')
BETWEEN NVL(avail.revision_low, NVL(p_cp_revision,'-999'))
AND NVL(avail.revision_high, NVL(p_cp_revision,'-999')))
AND avail.service_available_flag = 'Y';
from the lines block (CP) for each line selected of Renew Service.
**********************************************************************/
PROCEDURE Check_Price_List(check_value IN OUT VARCHAR2,
p_price_list_id IN NUMBER,
service_inv_item_id IN NUMBER,
uom_code IN VARCHAR2) IS
CURSOR get_price_list IS
SELECT 'Y'
FROM so_price_list_lines SOPL
WHERE SOPL.price_list_id = p_price_list_id
AND SOPL.inventory_item_id = service_inv_item_id
AND SOPL.unit_code = uom_code ;
SELECT DECODE((TO_NUMBER(TO_DATE(Service_End_Date,'DD-MON-RR')
- TO_DATE(Service_Start_Date,'DD-MON-RR'))
) ,0,1,
(TO_NUMBER(TO_DATE(Service_End_Date,'DD-MON-RR')
- TO_DATE(Service_Start_Date,'DD-MON-RR')))
)
INTO Duration_Days
FROM sys.dual;
cp_last_update_login IN NUMBER,
cp_bill_to_contact_id IN NUMBER,
order_customer_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;
p_login_id => cp_last_update_login,
p_org_id => FND_PROFILE.Value('ORG_ID'),
p_customer_id => order_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_ORD',
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);
cp_last_update_login IN NUMBER,
cp_bill_to_contact_id IN NUMBER,
cp_customer_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;
p_login_id => cp_last_update_login,
p_org_id => FND_PROFILE.Value('ORG_ID'),
p_customer_id => cp_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_REN',
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);