[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT PARTY_ID
FROM PA_CUSTOMERS_V
WHERE CUSTOMER_ID = p_customer_id
AND PARTY_TYPE = 'ORGANIZATION';
,p_action => 'INSERT'
,p_project_id => p_project_id
,p_customer_id => p_customer_id
,p_customer_bill_split => p_customer_bill_split
,p_bill_to_address_id => p_bill_to_address_id /* For Bug 2731449 */
,p_ship_to_address_id => p_ship_to_address_id /* For Bug 2731449 */
,p_inv_currency_code => p_inv_currency_code
,p_inv_rate_type => p_inv_rate_type
,p_inv_rate_date => p_inv_rate_date
,p_inv_exchange_rate => p_inv_exchange_rate
,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
,p_receiver_task_id => p_receiver_task_id
,x_bill_another_project_flag => l_bill_another_project_flag);
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
PA_PROJECT_CUSTOMERS_PKG.INSERT_ROW
( X_Rowid => l_rowid
,X_Project_Id => p_project_id
,X_Customer_Id => p_customer_id
,X_Last_Update_Date => l_last_update_date
,X_Last_Updated_By => l_last_updated_by
,X_Creation_Date => l_creation_date
,X_Created_By => l_created_by
,X_Last_Update_Login => l_last_update_login
,X_Project_Relationship_Code => p_project_relationship_code
,X_Customer_Bill_Split => p_customer_bill_split
,X_Bill_To_Customer_Id => p_bill_to_customer_id /* For Bug 2731449 */
,X_Ship_To_Customer_Id => p_ship_to_customer_id /* For Bug 2731449 */
,X_Bill_To_Address_Id => l_bill_to_address_id
,X_Ship_To_Address_Id => l_ship_to_address_id
,X_Inv_Currency_Code => l_inv_currency_code
,X_Inv_Rate_Type => l_inv_rate_type
,X_Inv_Rate_Date => l_inv_rate_date
,X_Inv_Exchange_Rate => l_inv_exchange_rate
,X_Allow_Inv_User_Rate_Type_Fg => p_allow_user_rate_type_flag
,X_Bill_Another_Project_Flag => l_bill_another_project_flag
,X_Receiver_Task_Id => l_receiver_task_id
,X_Record_Version_Number => 1
--Billing setup related changes for FP_M development. Tracking bug 3279981
,X_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = p_project_party_id
WHERE rowid = l_rowid;
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = l_project_party_id
WHERE rowid = l_rowid;
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = l_temp
WHERE rowid = l_rowid;
PROCEDURE UPDATE_PROJECT_CUSTOMER
( p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_project_id IN NUMBER
,p_customer_id IN NUMBER
,p_project_relationship_code IN VARCHAR2
,p_customer_bill_split IN NUMBER
,p_bill_to_customer_id IN NUMBER /* For Bug 2731449 */
,p_ship_to_customer_id IN NUMBER /* For Bug 2731449 */
,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
,p_bill_another_project_flag IN VARCHAR2 := 'N' --Added by Aditi for tracking bug 4153629
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
--Billing setup related changes for FP_M development. Tracking bug 3279981
,p_Default_Top_Task_Cust_Flag IN VARCHAR2
)
IS
l_bill_another_project_flag pa_project_customers.bill_another_project_flag%TYPE;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT rowid
FROM pa_project_customers
WHERE project_id = c_project_id AND
customer_id = c_customer_id;
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Project_Customer BEGIN');
savepoint update_project_customer_pvt;
SELECT 'x' INTO l_dummy
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND record_version_number = p_record_version_number;
,p_action => 'UPDATE'
,p_project_id => p_project_id
,p_customer_id => p_customer_id
,p_customer_bill_split => p_customer_bill_split
,p_bill_to_address_id => p_bill_to_address_id
,p_ship_to_address_id => p_ship_to_address_id
,p_inv_currency_code => p_inv_currency_code
,p_inv_rate_type => p_inv_rate_type
,p_inv_rate_date => p_inv_rate_date
,p_inv_exchange_rate => p_inv_exchange_rate
,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
,p_receiver_task_id => p_receiver_task_id
,x_bill_another_project_flag => l_bill_another_project_flag);
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
PA_PROJECT_CUSTOMERS_PKG.UPDATE_ROW
( X_Rowid => l_rowid
,X_Project_Id => p_project_id
,X_Customer_Id => p_customer_id
,X_Last_Update_Date => l_last_update_date
,X_Last_Updated_By => l_last_updated_by
,X_Last_Update_Login => l_last_update_login
,X_Project_Relationship_Code => p_project_relationship_code
,X_Customer_Bill_Split => p_customer_bill_split
,X_Bill_To_Customer_Id => p_bill_to_customer_id /* For Bug 2731449 */
,X_Ship_To_Customer_Id => p_ship_to_customer_id /* For Bug 2731449 */
,X_Bill_To_Address_Id => l_bill_to_address_id
,X_Ship_To_Address_Id => l_ship_to_address_id
,X_Inv_Currency_Code => l_inv_currency_code
,X_Inv_Rate_Type => l_inv_rate_type
,X_Inv_Rate_Date => l_inv_rate_date
,X_Inv_Exchange_Rate => l_inv_exchange_rate
,X_Allow_Inv_User_Rate_Type_Fg => p_allow_user_rate_type_flag
,X_Bill_Another_Project_Flag => l_bill_another_project_flag
,X_Receiver_Task_Id => l_receiver_task_id
,X_Record_Version_Number => p_record_version_number
--Billing setup related changes for FP_M development. Tracking bug 3279981
,X_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Project_Customer END');
rollback to update_project_customer_pvt;
rollback to update_project_customer_pvt;
p_procedure_name => 'Update_Project_Customer',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJECT_CUSTOMER;
PROCEDURE DELETE_PROJECT_CUSTOMER
( p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_project_id IN NUMBER
,p_customer_id IN NUMBER
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_bill_another_project_flag pa_project_customers.bill_another_project_flag%TYPE;
SELECT rowid
FROM pa_project_customers
WHERE project_id = c_project_id AND
customer_id = c_customer_id;
SELECT project_party_id
FROM pa_project_customers
WHERE project_id = p_project_id AND
customer_id = p_customer_id;
SELECT pp.project_party_id project_party_id
FROM pa_project_parties po,
pa_project_parties pp,
hz_party_relationships hzr
WHERE po.resource_type_id = 112
AND po.project_party_id = c_project_party_id
AND pp.resource_type_id = 112
AND pp.object_type = po.object_type
AND pp.object_id = po.object_id
AND hzr.party_relationship_type IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
AND hzr.subject_id = pp.resource_source_id
AND hzr.object_id = po.resource_source_id;
SELECT pp.project_party_id project_party_id
FROM pa_project_parties po,
pa_project_parties pp,
hz_relationships hzr
WHERE po.resource_type_id = 112
AND po.project_party_id = c_project_party_id
AND pp.resource_type_id = 112
AND pp.object_type = po.object_type
AND pp.object_id = po.object_id
AND hzr.relationship_code IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
AND hzr.subject_id = pp.resource_source_id
AND hzr.object_id = po.resource_source_id
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES';
SELECT project_party_id
FROM pa_project_customers
WHERE project_id = p_project_id
AND project_party_id = c_project_party_id
AND customer_id <> p_customer_id;
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Project_Customer BEGIN');
savepoint delete_project_customer_pvt;
SELECT 'x' INTO l_dummy
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_project_customers
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND record_version_number = p_record_version_number;
,p_action => 'DELETE'
,p_project_id => p_project_id
,p_customer_id => p_customer_id
,x_bill_another_project_flag => l_bill_another_project_flag);
PA_CUSTOMERS_CONTACTS_PVT.DELETE_ALL_CONTACTS
( p_commit => FND_API.G_FALSE
,p_validate_only => p_validate_only
,p_validation_level => p_validation_level
,p_calling_module => p_calling_module
,p_debug_mode => p_debug_mode
,p_max_msg_count => p_max_msg_count
,p_project_id => p_project_id
,p_customer_id => p_customer_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
PA_RETENTION_UTIL.delete_retn_rules_customer
( p_project_id => p_project_id
,p_customer_id => p_customer_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_project_parties_pkg.delete_row(x_project_id => p_project_id,
x_project_party_id => l_project_party_id,
x_record_version_number => null);
PA_PROJECT_CUSTOMERS_PKG.DELETE_ROW(l_rowid, p_record_version_number);
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Project_Customer END');
rollback to delete_project_customer_pvt;
rollback to delete_project_customer_pvt;
p_procedure_name => 'Delete_Project_Customer',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_PROJECT_CUSTOMER;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
,p_action => 'INSERT'
,p_project_id => p_project_id
,p_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
,p_contact_id => p_contact_id
,p_project_contact_type_code => p_project_contact_type_code);
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
PA_PROJECT_CONTACTS_PKG.INSERT_ROW
( X_Rowid => l_rowid
,X_Project_Id => p_project_id
,X_Customer_Id => p_customer_id
,X_Bill_Ship_Customer_Id => p_bill_ship_customer_id /* For Bug 2731449 */
,X_Contact_Id => p_contact_id
,X_Project_Contact_Type_Code => p_project_contact_type_code
,X_Last_Update_Date => l_last_update_date
,X_Last_Updated_By => l_last_updated_by
,X_Creation_Date => l_creation_date
,X_Created_By => l_created_by
,X_Last_Update_Login => l_last_update_login
,X_Record_Version_Number => 1);
PROCEDURE UPDATE_CUSTOMER_CONTACT
( p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_project_id IN NUMBER
,p_customer_id IN NUMBER
,p_bill_ship_customer_id IN NUMBER /* For Bug 2731449 */
,p_contact_id IN NUMBER
,p_project_contact_type_code IN VARCHAR2
,p_rowid IN VARCHAR2
,p_record_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_msg_count NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Customer_Contact BEGIN');
savepoint update_customer_contact_pvt;
SELECT 'x' INTO l_dummy
FROM pa_project_contacts
WHERE rowid = p_rowid
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_project_contacts
WHERE rowid = p_rowid
AND record_version_number = p_record_version_number;
,p_action => 'UPDATE'
,p_project_id => p_project_id
,p_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
,p_contact_id => p_contact_id
,p_project_contact_type_code => p_project_contact_type_code
,p_rowid => p_rowid);
l_last_update_date := sysdate;
l_last_updated_by := fnd_global.user_id;
l_last_update_login := fnd_global.login_id;
PA_PROJECT_CONTACTS_PKG.UPDATE_ROW
( X_Rowid => p_rowid
,X_Project_Id => p_project_id
,X_Customer_Id => p_customer_id
,X_Bill_Ship_Customer_Id => p_bill_ship_customer_id /* For Bug 2731449 */
,X_Contact_Id => p_contact_id
,X_Project_Contact_Type_Code => p_project_contact_type_code
,X_Last_Update_Date => l_last_update_date
,X_Last_Updated_By => l_last_updated_by
,X_Last_Update_Login => l_last_update_login
,X_Record_Version_Number => p_record_version_number);
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Customer_Contact END');
rollback to update_customer_contact_pvt;
rollback to update_customer_contact_pvt;
p_procedure_name => 'Update_Customer_Contact',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_CUSTOMER_CONTACT;
PROCEDURE DELETE_CUSTOMER_CONTACT
( p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_project_id IN NUMBER
,p_customer_id IN NUMBER
,p_contact_id IN NUMBER
,p_project_contact_type_code IN VARCHAR2
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_rowid VARCHAR2(250);
SELECT rowid
FROM pa_project_contacts
WHERE project_id = c_project_id AND
customer_id = c_customer_id AND
contact_id = c_contact_id AND
project_contact_type_code = c_project_contact_type_code;
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Customer_Contact BEGIN');
savepoint delete_customer_contact_pvt;
SELECT 'x' INTO l_dummy
FROM pa_project_contacts
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND contact_id = p_contact_id
AND project_contact_type_code = p_project_contact_type_code
AND record_version_number = p_record_version_number
FOR UPDATE OF record_version_number NOWAIT;
SELECT 'x' INTO l_dummy
FROM pa_project_contacts
WHERE project_id = p_project_id
AND customer_id = p_customer_id
AND contact_id = p_contact_id
AND project_contact_type_code = p_project_contact_type_code
AND record_version_number = p_record_version_number;
PA_PROJECT_CONTACTS_PKG.DELETE_ROW(l_rowid, p_record_version_number);
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Customer_Contact END');
rollback to delete_customer_contact_pvt;
rollback to delete_customer_contact_pvt;
p_procedure_name => 'Delete_Customer_Contact',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_CUSTOMER_CONTACT;
PROCEDURE DELETE_ALL_CONTACTS
( p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
,p_debug_mode IN VARCHAR2 := 'N'
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_project_id IN NUMBER
,p_customer_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_return_status VARCHAR2(1);
SELECT contact_id, project_contact_type_code, record_version_number
FROM pa_project_contacts
WHERE project_id = c_project_id AND
customer_id = c_customer_id;
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_Contacts BEGIN');
savepoint delete_all_contacts_pvt;
PA_CUSTOMERS_CONTACTS_PVT.DELETE_CUSTOMER_CONTACT
( p_commit => FND_API.G_FALSE
,p_validate_only => p_validate_only
,p_validation_level => p_validation_level
,p_calling_module => p_calling_module
,p_debug_mode => p_debug_mode
,p_max_msg_count => p_max_msg_count
,p_project_id => p_project_id
,p_customer_id => p_customer_id
,p_contact_id => l_recinfo.contact_id
,p_project_contact_type_code => l_recinfo.project_contact_type_code
,p_record_version_number => l_recinfo.record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_Contacts END');
rollback to delete_all_contacts_pvt;
rollback to delete_all_contacts_pvt;
p_procedure_name => 'Delete_All_Contacts',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_ALL_CONTACTS;
if p_action = 'INSERT' then
PA_CUSTOMERS_CONTACTS_UTILS.CHECK_DUPLICATE_CUSTOMER
( p_project_id => p_project_id
,p_customer_id => p_customer_id
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code);
if ((p_action = 'INSERT') OR (p_action = 'UPDATE')) then
--dbms_output.put_line('Before Calling CHECK_CONTRIBUTION_PERCENTAGE');
/* These checks are performed in Update_project, hence need not be done here again */
--dbms_output.put_line('Value of l_bill_another_project_flag'||l_bill_another_project_flag);
if p_action = 'UPDATE' then
PA_CUSTOMERS_CONTACTS_UTILS.CHECK_UPDATE_CONTRIB_ALLOWED
( p_project_id => p_project_id
,p_customer_id => p_customer_id
,p_customer_bill_split => p_customer_bill_split
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code);
if p_action = 'DELETE' then
PA_CUSTOMERS_CONTACTS_UTILS.CHECK_DELETE_CUSTOMER_ALLOWED
( p_project_id => p_project_id
,p_customer_id => p_customer_id
,x_return_status => l_return_status
,x_error_msg_code => l_error_msg_code);
SELECT rowid
FROM pa_project_contacts
WHERE project_id = c_project_id AND
customer_id = c_customer_id AND
contact_id = c_contact_id AND
project_contact_type_code = c_project_contact_type_code;
if ((p_action = 'INSERT') OR (p_action = 'UPDATE')) then
if p_action = 'UPDATE' then
if (p_rowid = FND_API.G_MISS_CHAR) OR (p_rowid is NULL) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;