[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pa_currency.get_currency_code
INTO l_agreement_in_rec.agreement_currency_code from dual;
,p_last_update_date => SYSDATE
,p_last_updated_by => G_USER_ID
,p_creation_date => SYSDATE
,p_created_by => G_USER_ID
,p_last_update_login => G_LOGIN_ID
,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
,p_term_id => l_agreement_in_rec.term_id
,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
,p_amount => l_agreement_in_rec.amount
,p_description => l_agreement_in_rec.description
,p_expiration_date => l_agreement_in_rec.expiration_date
,p_attribute_category => l_agreement_in_rec.attribute_category
,p_attribute1 => l_agreement_in_rec.attribute1
,p_attribute2 => l_agreement_in_rec.attribute2
,p_attribute3 => l_agreement_in_rec.attribute3
,p_attribute4 => l_agreement_in_rec.attribute4
,p_attribute5 => l_agreement_in_rec.attribute5
,p_attribute6 => l_agreement_in_rec.attribute6
,p_attribute7 => l_agreement_in_rec.attribute7
,p_attribute8 => l_agreement_in_rec.attribute8
,p_attribute9 => l_agreement_in_rec.attribute9
,p_attribute10 => l_agreement_in_rec.attribute10
,p_template_flag => l_agreement_in_rec.template_flag
,p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
,p_pm_product_code => p_pm_product_code
/* MCB2 params begin */
,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
/*Federal*/
,p_customer_order_number=> l_agreement_in_rec.customer_order_number
,p_advance_required => l_agreement_in_rec.advance_required
,p_start_date => l_agreement_in_rec.start_date
,p_billing_sequence => l_agreement_in_rec.billing_sequence
,p_line_of_account => l_agreement_in_rec.line_of_account
,p_attribute11 => l_agreement_in_rec.attribute11
,p_attribute12 => l_agreement_in_rec.attribute12
,p_attribute13 => l_agreement_in_rec.attribute13
,p_attribute14 => l_agreement_in_rec.attribute14
,p_attribute15 => l_agreement_in_rec.attribute15
,p_attribute16 => l_agreement_in_rec.attribute16
,p_attribute17 => l_agreement_in_rec.attribute17
,p_attribute18 => l_agreement_in_rec.attribute18
,p_attribute19 => l_agreement_in_rec.attribute19
,p_attribute20 => l_agreement_in_rec.attribute20
,p_attribute21 => l_agreement_in_rec.attribute21
,p_attribute22 => l_agreement_in_rec.attribute22
,p_attribute23 => l_agreement_in_rec.attribute23
,p_attribute24 => l_agreement_in_rec.attribute24
,p_attribute25 => l_agreement_in_rec.attribute25);
SELECT invproc_currency_type INTO l_invproc_currency_type
FROM pa_projects_all
WHERE project_id=l_funding_in_rec.project_id;
SELECT distinct funding_currency_code INTO l_old_fund_curr_code
FROM pa_summary_project_fundings
WHERE project_id=l_funding_in_rec.project_id
and not (total_baselined_amount = 0
and total_unbaselined_amount = 0); /* Added for bug 6510026 */
PROCEDURE delete_agreement
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_product_code IN VARCHAR2
,p_pm_agreement_reference IN VARCHAR2
,p_agreement_id IN NUMBER
)
IS
-- Local Cursors
CURSOR l_funding_id_csr(p_agreement_id NUMBER)
IS
SELECT project_funding_id,pm_funding_reference,agreement_id
FROM pa_project_fundings f
WHERE f.agreement_id = p_agreement_id;
l_api_name CONSTANT VARCHAR2(30):= 'delete_agreement';
SAVEPOINT delete_agreement_pub;
p_function_name => 'PA_AF_DELETE_AGREEMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed
);
IF pa_agreement_pvt.check_delete_agreement_ok
(p_agreement_id => p_agreement_id
,p_pm_agreement_reference => p_pm_agreement_reference) = 'N'
THEN
p_return_status := FND_API.G_RET_STS_ERROR;
pa_agreement_pub.delete_funding
( p_api_version_number => p_api_version_number
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_msg_count => p_msg_count
,p_msg_data => p_msg_data
,p_return_status => p_return_status
,p_pm_product_code => p_pm_product_code
,p_pm_funding_reference => l_funding_id_rec.pm_funding_reference
,p_funding_id => l_funding_id
,p_check_y_n => 'N' );
pa_agreement_utils.delete_agreement
(p_agreement_id => l_out_agreement_id);
ROLLBACK TO delete_agreement_pub;
ROLLBACK TO delete_agreement_pub;
ROLLBACK TO delete_agreement_pub;
ROLLBACK TO delete_agreement_pub;
END delete_agreement;
PROCEDURE update_agreement
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2
,p_pm_product_code IN VARCHAR2
,p_agreement_in_rec IN Agreement_Rec_In_Type
,p_agreement_out_rec OUT NOCOPY Agreement_Rec_Out_Type /*File.sql.39*/
,p_funding_in_tbl IN funding_in_tbl_type
,p_funding_out_tbl OUT NOCOPY funding_out_tbl_type /*File.sql.39*/
)
IS
-- Local variables
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'update_agreement';
SAVEPOINT update_agreement_pub;
p_function_name => 'PA_AF_UPDATE_AGREEMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed
);
SELECT pa_currency.get_currency_code
INTO l_agreement_in_rec.agreement_currency_code from dual;
out in check_update_agreement_ok */
IF pa_agreement_pvt.check_update_agreement_ok
(p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
,p_agreement_id => l_out_agreement_id
,p_funding_id => NULL
,p_customer_id => l_agreement_in_rec.customer_id
,p_agreement_type => l_agreement_in_rec.agreement_type
,p_term_id => l_agreement_in_rec.term_id
,p_template_flag => l_agreement_in_rec.template_flag
,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
/* MCB2 params begin */
,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
/* MCB2 params end */
/*Federal*/
,p_start_date => p_agreement_in_rec.start_date
,p_end_date => p_agreement_in_rec.expiration_date
,p_advance_required => p_agreement_in_rec.advance_required
,p_billing_sequence => p_agreement_in_rec.billing_sequence
,p_amount => p_agreement_in_rec.amount
) = 'N'
THEN
p_agreement_out_rec.return_status := FND_API.G_RET_STS_ERROR;
/* Added the below check to update an agreement even when no funding is there. Bug 5734567 */
if (l_funding_in_rec.project_id is NULL
or l_funding_in_rec.date_allocated is NULL
or l_funding_in_rec.allocated_amount is NULL
--or l_funding_in_rec.funding_category is NULL /* Commented for the bug 6780803 */
or l_funding_in_rec.project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
or l_funding_in_rec.date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
or l_funding_in_rec.allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
--or l_funding_in_rec.funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) /* Commented for the bug 6780803 */
then
--dbms_output.put_line('Funding not entered. ');
pa_agreement_utils.update_agreement
(p_agreement_id => l_out_agreement_id
,p_customer_id => l_agreement_in_rec.customer_id
,p_agreement_num => l_agreement_in_rec.agreement_num
,p_agreement_type => l_agreement_in_rec.agreement_type
,p_last_update_date => SYSDATE
,p_last_updated_by => G_USER_ID
,p_last_update_login => G_LOGIN_ID
,p_owned_by_person_id => l_agreement_in_rec.owned_by_person_id
,p_term_id => l_agreement_in_rec.term_id
,p_revenue_limit_flag => l_agreement_in_rec.revenue_limit_flag
,p_amount => l_agreement_in_rec.amount
,p_description => l_agreement_in_rec.description
,p_expiration_date => l_agreement_in_rec.expiration_date
,p_attribute_category => l_agreement_in_rec.attribute_category
,p_attribute1 => l_agreement_in_rec.attribute1
,p_attribute2 => l_agreement_in_rec.attribute2
,p_attribute3 => l_agreement_in_rec.attribute3
,p_attribute4 => l_agreement_in_rec.attribute4
,p_attribute5 => l_agreement_in_rec.attribute5
,p_attribute6 => l_agreement_in_rec.attribute6
,p_attribute7 => l_agreement_in_rec.attribute7
,p_attribute8 => l_agreement_in_rec.attribute8
,p_attribute9 => l_agreement_in_rec.attribute9
,p_attribute10 => l_agreement_in_rec.attribute10
,p_template_flag => l_agreement_in_rec.template_flag
,p_pm_agreement_reference => l_agreement_in_rec.pm_agreement_reference
,p_pm_product_code => p_pm_product_code
/* MCB2 params begin */
,p_owning_organization_id => l_agreement_in_rec.owning_organization_id
,p_agreement_currency_code => l_agreement_in_rec.agreement_currency_code
,p_invoice_limit_flag => l_agreement_in_rec.invoice_limit_flag
/* MCB2 params end */
/*Federal*/
,p_customer_order_number=> l_agreement_in_rec.customer_order_number
,p_advance_required => l_agreement_in_rec.advance_required
,p_start_date => l_agreement_in_rec.start_date
,p_billing_sequence => l_agreement_in_rec.billing_sequence
,p_line_of_account => l_agreement_in_rec.line_of_account
,p_attribute11 => l_agreement_in_rec.attribute11
,p_attribute12 => l_agreement_in_rec.attribute12
,p_attribute13 => l_agreement_in_rec.attribute13
,p_attribute14 => l_agreement_in_rec.attribute14
,p_attribute15 => l_agreement_in_rec.attribute15
,p_attribute16 => l_agreement_in_rec.attribute16
,p_attribute17 => l_agreement_in_rec.attribute17
,p_attribute18 => l_agreement_in_rec.attribute18
,p_attribute19 => l_agreement_in_rec.attribute19
,p_attribute20 => l_agreement_in_rec.attribute20
,p_attribute21 => l_agreement_in_rec.attribute21
,p_attribute22 => l_agreement_in_rec.attribute22
,p_attribute23 => l_agreement_in_rec.attribute23
,p_attribute24 => l_agreement_in_rec.attribute24
,p_attribute25 => l_agreement_in_rec.attribute25
);
/* Added the below check to update an agreement even when no funding is there. Bug 5734567 */
if (l_funding_in_rec.project_id is NULL
or l_funding_in_rec.date_allocated is NULL
or l_funding_in_rec.allocated_amount is NULL
--or l_funding_in_rec.funding_category is NULL /* Commented for the bug 6780803 */
or l_funding_in_rec.project_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
or l_funding_in_rec.date_allocated = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
or l_funding_in_rec.allocated_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
--or l_funding_in_rec.funding_category = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) /* Commented for the bug 6780803 */
then
--dbms_output.put_line('Funding not entered. ');
IF pa_agreement_pvt.check_add_update(p_funding_reference => l_funding_in_rec.pm_funding_reference)='U'
THEN
-- UPDATE FUNDING
pa_agreement_pub.update_funding
(p_api_version_number => p_api_version_number
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_msg_count => p_msg_count
,p_msg_data => p_msg_data
,p_return_status => l_return_status
,p_pm_product_code => p_pm_product_code
,p_pm_funding_reference => l_funding_in_rec.pm_funding_reference
,p_funding_id => l_funding_in_rec.project_funding_id
,p_project_id => l_funding_in_rec.project_id
,p_task_id => l_funding_in_rec.task_id
,p_agreement_id => l_out_agreement_id
,p_allocated_amount => l_funding_in_rec.allocated_amount
,p_date_allocated => l_funding_in_rec.date_allocated
,p_desc_flex_name => l_funding_in_rec.desc_flex_name
,p_attribute_category => l_funding_in_rec.attribute_category
,p_attribute1 => l_funding_in_rec.attribute1
,p_attribute2 => l_funding_in_rec.attribute2
,p_attribute3 => l_funding_in_rec.attribute3
,p_attribute4 => l_funding_in_rec.attribute4
,p_attribute5 => l_funding_in_rec.attribute5
,p_attribute6 => l_funding_in_rec.attribute6
,p_attribute7 => l_funding_in_rec.attribute7
,p_attribute8 => l_funding_in_rec.attribute8
,p_attribute9 => l_funding_in_rec.attribute9
,p_attribute10 => l_funding_in_rec.attribute10
,p_funding_id_out => p_funding_out_tbl(i).project_funding_id
/* MCB2 params begin */
,p_project_rate_type => l_funding_in_rec.project_rate_type
,p_project_rate_date => l_funding_in_rec.project_rate_date
,p_project_exchange_rate => l_funding_in_rec.project_exchange_rate
,p_projfunc_rate_type => l_funding_in_rec.projfunc_rate_type
,p_projfunc_rate_date => l_funding_in_rec.projfunc_rate_date
,p_projfunc_exchange_rate => l_funding_in_rec.projfunc_exchange_rate
/* MCB2 params end */
,p_funding_category => l_funding_in_rec.funding_category /* For Bug2244796 */
);
ELSIF pa_agreement_pvt.check_add_update(p_funding_reference => l_funding_in_rec.pm_funding_reference)='A'
THEN
-- ADD FUNDING
pa_agreement_pub.add_funding
(p_api_version_number => p_api_version_number
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_msg_count => p_msg_count
,p_msg_data => p_msg_data
,p_return_status => l_return_status
,p_pm_product_code => p_pm_product_code
,p_pm_funding_reference => l_funding_in_rec.pm_funding_reference
,p_funding_id => l_funding_in_rec.project_funding_id
,p_pa_project_id => l_funding_in_rec.project_id
,p_pa_task_id => l_funding_in_rec.task_id
,p_agreement_id => l_out_agreement_id
,p_allocated_amount => l_funding_in_rec.allocated_amount
,p_date_allocated => l_funding_in_rec.date_allocated
,p_desc_flex_name => l_funding_in_rec.desc_flex_name
,p_attribute_category => l_funding_in_rec.attribute_category
,p_attribute1 => l_funding_in_rec.attribute1
,p_attribute2 => l_funding_in_rec.attribute2
,p_attribute3 => l_funding_in_rec.attribute3
,p_attribute4 => l_funding_in_rec.attribute4
,p_attribute5 => l_funding_in_rec.attribute5
,p_attribute6 => l_funding_in_rec.attribute6
,p_attribute7 => l_funding_in_rec.attribute7
,p_attribute8 => l_funding_in_rec.attribute8
,p_attribute9 => l_funding_in_rec.attribute9
,p_attribute10 => l_funding_in_rec.attribute10
,p_funding_id_out => p_funding_out_tbl(i).project_funding_id
/* MCB2 params begin */
,p_project_rate_type => l_funding_in_rec.project_rate_type
,p_project_rate_date => l_funding_in_rec.project_rate_date
,p_project_exchange_rate => l_funding_in_rec.project_exchange_rate
,p_projfunc_rate_type => l_funding_in_rec.projfunc_rate_type
,p_projfunc_rate_date => l_funding_in_rec.projfunc_rate_date
,p_projfunc_exchange_rate => l_funding_in_rec.projfunc_exchange_rate
/* MCB2 params end */
,p_funding_category => l_funding_in_rec.funding_category /* For Bug2244796 */
);
ROLLBACK TO update_agreement_pub;
ROLLBACK TO update_agreement_pub;
ROLLBACK TO update_project_pub;
ROLLBACK TO update_agreement_pub;
END update_agreement;
SELECT a. customer_id
INTO l_customer_id
FROM pa_agreements_all a
WHERE a.agreement_id = p_agreement_id;
p_Last_Update_Date => SYSDATE,
p_Last_Updated_By => G_USER_ID,
p_Creation_Date => SYSDATE,
p_Created_By => G_USER_ID,
p_Last_Update_Login => G_LOGIN_ID,
p_Agreement_Id => p_agreement_id,
p_Project_Id => p_pa_project_id,
p_Task_Id => l_pa_task_id,
p_Allocated_Amount => p_allocated_amount,
p_Date_Allocated => p_date_allocated,
p_Attribute_Category => l_attribute_category,
p_Attribute1 => l_attribute1,
p_Attribute2 => l_attribute2,
p_Attribute3 => l_attribute3,
p_Attribute4 => l_attribute4,
p_Attribute5 => l_attribute5,
p_Attribute6 => l_attribute6,
p_Attribute7 => l_attribute7,
p_Attribute8 => l_attribute8,
p_Attribute9 => l_attribute9,
p_Attribute10 => l_attribute10,
p_pm_funding_reference => p_pm_funding_reference,
p_pm_product_code => p_pm_product_code,
/* MCB2 PARAMETERS BEGIN 5554070 Chaged to new introduced variable */
p_project_rate_type => l_project_rate_type,
p_project_rate_date => l_project_rate_date,
p_project_exchange_rate => p_project_exchange_rate,
p_projfunc_rate_type => l_projfunc_rate_type,
p_projfunc_rate_date => l_projfunc_rate_date,
p_projfunc_exchange_rate => p_projfunc_exchange_rate,
x_err_code => l_err_code,
x_err_msg => l_err_msg,
p_funding_category => l_funding_category /* For Bug 2244796 */
);
pa_agreement_utils.summary_funding_insert_row
(p_agreement_id => p_agreement_id
,p_project_id => p_pa_project_id
,p_task_id => p_pa_task_id
,p_login_id => G_LOGIN_ID
,p_user_id => G_USER_ID);
PROCEDURE delete_funding
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2/*File.sql.39*/
,p_pm_product_code IN VARCHAR2
,p_pm_funding_reference IN VARCHAR2
,p_funding_id IN NUMBER
,p_check_y_n IN VARCHAR2
)
IS
-- Local variables
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'delete_funding';
SELECT f.funding_category
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_pm_funding_reference;
SAVEPOINT delete_funding_pub;
p_function_name => 'PA_AF_DELETE_FUNDING',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed
);
( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF pa_agreement_pvt.check_delete_funding_ok
(p_agreement_id => pa_agreement_utils.get_agreement_id( p_funding_id => p_funding_id
,p_funding_reference => p_pm_funding_reference)
,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
,p_pm_funding_reference => p_pm_funding_reference) = 'N'
THEN
p_return_status := FND_API.G_RET_STS_ERROR;
pa_agreement_utils.delete_funding
(p_project_funding_id =>l_out_funding_id);
pa_agreement_utils.summary_funding_delete_row
(p_agreement_id => l_agreement_id
,p_project_id => l_project_id
,p_task_id => l_task_id
,p_login_id => G_LOGIN_ID
,p_user_id => G_USER_ID );
ROLLBACK TO delete_funding_pub;
ROLLBACK TO delete_funding_pub;
ROLLBACK TO delete_funding_pub;
ROLLBACK TO delete_funding_pub;
END delete_funding;
PROCEDURE update_funding
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_product_code IN VARCHAR2
,p_pm_funding_reference IN VARCHAR2
,p_funding_id IN NUMBER
,p_project_id IN NUMBER
,p_task_id IN NUMBER
,p_agreement_id IN NUMBER
,p_allocated_amount IN NUMBER
,p_date_allocated IN DATE
,p_desc_flex_name IN VARCHAR2
,p_attribute_category IN VARCHAR2
,p_attribute1 IN VARCHAR2
,p_attribute2 IN VARCHAR2
,p_attribute3 IN VARCHAR2
,p_attribute4 IN VARCHAR2
,p_attribute5 IN VARCHAR2
,p_attribute6 IN VARCHAR2
,p_attribute7 IN VARCHAR2
,p_attribute8 IN VARCHAR2
,p_attribute9 IN VARCHAR2
,p_attribute10 IN VARCHAR2
,p_funding_id_out OUT NOCOPY NUMBER /*File.sql.39*/
,p_project_rate_type IN VARCHAR2 DEFAULT NULL
,p_project_rate_date IN DATE DEFAULT NULL
,p_project_exchange_rate IN NUMBER DEFAULT NULL
,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
,p_projfunc_rate_date IN DATE DEFAULT NULL
,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
/* Added for Bug 2512483 Default Value- For Bug 2244796 */
)
IS
-- LOCAL VARIABLES
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'update_funding';
SELECT f.funding_category
FROM PA_PROJECT_FUNDINGS f
WHERE f.pm_funding_reference = p_pm_funding_reference
AND f.funding_category = 'REVALUATION';/* Added this condition for 3360593*/
SAVEPOINT update_funding_pub;
p_function_name => 'PA_AF_UPDATE_FUNDING',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed
);
( p_old_message_code => 'PA_UPDATE_DELETE_REVAL'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
/** changed p_funding_id to l_out_funding_id in call to check_update_funding_ok bug 2434153 **/
-- TO BE CORRECTED - NIKHIL
-- VALIDATE THE INCOMING PARAMETERS
IF pa_agreement_pvt.check_update_funding_ok
(p_project_id => p_project_id
,p_task_id => p_task_id
,p_agreement_id => p_agreement_id
,p_customer_id => pa_agreement_utils.get_customer_id
(p_funding_id => l_out_funding_id
,p_funding_reference => p_pm_funding_reference)
,p_pm_funding_reference => p_pm_funding_reference
,p_funding_id => l_out_funding_id
,p_funding_amt => p_allocated_amount
/* MCB2 PARAMETERS BEGIN */
,p_project_rate_type => p_project_rate_type
,p_project_rate_date => p_project_rate_date
,p_project_exchange_rate => p_project_exchange_rate
,p_projfunc_rate_type => p_projfunc_rate_type
,p_projfunc_rate_date => p_projfunc_rate_date
,p_projfunc_exchange_rate => p_projfunc_exchange_rate ) = 'N'
/* MCB2 PARAMETERS END */
THEN
p_return_status := FND_API.G_RET_STS_ERROR;
SELECT funding_category
INTO l_funding_category
FROM pa_project_fundings
WHERE PROJECT_FUNDING_ID = l_out_funding_id;
pa_agreement_utils.update_funding
(p_project_funding_id => l_out_funding_id
,p_last_update_date => SYSDATE
,p_last_updated_by => G_USER_ID
,p_last_update_login => G_LOGIN_ID
,p_agreement_id => p_agreement_id
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_allocated_amount => p_allocated_amount
,p_date_allocated => p_date_allocated
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_pm_funding_reference => p_pm_funding_reference
,p_pm_product_code => p_pm_product_code
/* MCB2 PARAMETERS BEGIN */
,p_project_rate_type => p_project_rate_type
,p_project_rate_date => p_project_rate_date
,p_project_exchange_rate => p_project_exchange_rate
,p_projfunc_rate_type => p_projfunc_rate_type
,p_projfunc_rate_date => p_projfunc_rate_date
,p_projfunc_exchange_rate => p_projfunc_exchange_rate
,x_err_code => l_err_code
,x_err_msg => l_err_msg
,p_funding_category => l_funding_category /* For Bug 2244796 */
);
pa_agreement_utils.summary_funding_update_row
(p_agreement_id => p_agreement_id
,p_project_id => p_project_id
,p_task_id => p_task_id
,p_login_id => G_LOGIN_ID
,p_user_id => G_USER_ID
);
ROLLBACK TO update_funding_pub;
ROLLBACK TO update_funding_pub;
ROLLBACK TO update_funding_pub;
END update_funding;
G_funding_in_tbl.delete;
G_funding_out_tbl.delete;
PROCEDURE execute_update_agreement
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_product_code IN VARCHAR2
)
IS
-- LOCAL VARIABLES
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'execute_update_agreement';
SAVEPOINT execute_update_agreement_pub;
p_function_name => 'PA_AF_EXECUTE_UPDATE_AGREEMENT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed
);
pa_agreement_pub.update_agreement
(p_api_version_number => p_api_version_number
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_msg_count => p_msg_count
,p_msg_data => p_msg_data
,p_return_status => p_return_status
,p_pm_product_code => p_pm_product_code
,p_agreement_in_rec => G_agreement_in_rec
,p_agreement_out_rec => G_agreement_out_rec
,p_funding_in_tbl => G_funding_in_tbl
,p_funding_out_tbl => G_funding_out_tbl);
ROLLBACK TO execute_update_agreement_pub;
ROLLBACK TO execute_update_agreement_pub;
ROLLBACK TO execute_update_agreement_pub;
END execute_update_agreement;
PROCEDURE check_delete_agreement_ok
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_agreement_reference IN VARCHAR2
,p_agreement_id IN NUMBER
,p_del_agree_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
)
IS
-- LOCAL VARIABLES
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'check_delete_agreement_ok';
SAVEPOINT check_delete_agreement_ok_pub;
p_del_agree_ok_flag := pa_agreement_pvt.check_delete_agreement_ok
(p_agreement_id => l_out_agreement_id
,p_pm_agreement_reference => p_pm_agreement_reference);
ROLLBACK TO check_delete_agreement_ok_pub;
ROLLBACK TO check_delete_agreement_ok_pub;
ROLLBACK TO check_delete_agreement_ok_pub;
END check_delete_agreement_ok;
SELECT a. customer_id
INTO l_customer_id
FROM pa_agreements_all a
WHERE a.agreement_id = p_agreement_id;
PROCEDURE check_delete_funding_ok
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_funding_reference IN VARCHAR2
,p_funding_id IN NUMBER
,p_del_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
)
IS
-- LOCAL VARIABLES
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'check_delete_funding_ok';
SAVEPOINT check_delete_funding_ok_pub;
p_del_funding_ok_flag := pa_agreement_pvt.check_delete_funding_ok
(p_agreement_id => pa_agreement_utils.get_agreement_id(p_funding_id => p_funding_id
,p_funding_reference => p_pm_funding_reference)
,p_funding_id => pa_agreement_utils.get_funding_id(p_funding_reference => p_pm_funding_reference)
,p_pm_funding_reference => p_pm_funding_reference);
ROLLBACK TO check_delete_funding_ok_pub;
ROLLBACK TO check_delete_funding_ok_pub;
ROLLBACK TO check_delete_funding_ok_pub;
END check_delete_funding_ok;
PROCEDURE check_update_funding_ok
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER /*File.sql.39*/
,p_msg_data OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_return_status OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_pm_product_code IN VARCHAR2
,p_pm_funding_reference IN VARCHAR2
,p_funding_id IN NUMBER
,p_pm_project_reference IN VARCHAR2
,p_project_id IN NUMBER
,p_pm_task_reference IN VARCHAR2
,p_task_id IN NUMBER
,p_pm_agreement_reference IN VARCHAR2
,p_agreement_id IN NUMBER
,p_allocated_amount IN NUMBER
,p_date_allocated IN DATE
,p_desc_flex_name IN VARCHAR2
,p_attribute_category IN VARCHAR2
,p_attribute1 IN VARCHAR2
,p_attribute2 IN VARCHAR2
,p_attribute3 IN VARCHAR2
,p_attribute4 IN VARCHAR2
,p_attribute5 IN VARCHAR2
,p_attribute6 IN VARCHAR2
,p_attribute7 IN VARCHAR2
,p_attribute8 IN VARCHAR2
,p_attribute9 IN VARCHAR2
,p_attribute10 IN VARCHAR2
,p_update_funding_ok_flag OUT NOCOPY VARCHAR2 /*File.sql.39*/
,p_project_rate_type IN VARCHAR2 DEFAULT NULL
,p_project_rate_date IN DATE DEFAULT NULL
,p_project_exchange_rate IN NUMBER DEFAULT NULL
,p_projfunc_rate_type IN VARCHAR2 DEFAULT NULL
,p_projfunc_rate_date IN DATE DEFAULT NULL
,p_projfunc_exchange_rate IN NUMBER DEFAULT NULL
,p_funding_category IN VARCHAR2 DEFAULT 'ADDITIONAL'
/* Added for Bug 2483081 to include Default value - For Bug 2244796 */
)
IS
-- LOCAL VARIABLES
l_msg_count NUMBER ;
l_api_name CONSTANT VARCHAR2(30):= 'check_update_funding_ok';
SAVEPOINT check_update_funding_ok_pub;
p_update_funding_ok_flag := pa_agreement_pvt.check_update_funding_ok
(p_project_id => p_project_id
,p_task_id => p_task_id
,p_agreement_id => p_agreement_id
,p_customer_id => pa_agreement_utils.get_customer_id
(p_funding_id => p_funding_id
,p_funding_reference => p_pm_funding_reference)
,p_pm_funding_reference => p_pm_funding_reference
,p_funding_id => p_funding_id
,p_funding_amt => p_allocated_amount
/* MCB2 PARAMETERS BEGIN */
,p_project_rate_type => p_project_rate_type
,p_project_rate_date => p_project_rate_date
,p_project_exchange_rate => p_project_exchange_rate
,p_projfunc_rate_type => p_projfunc_rate_type
,p_projfunc_rate_date => p_projfunc_rate_date
,p_projfunc_exchange_rate => p_projfunc_exchange_rate );
ROLLBACK TO check_update_funding_ok_pub;
ROLLBACK TO check_update_funding_ok_pub;
ROLLBACK TO check_update_funding_ok_pub;
END check_update_funding_ok;
SELECT R1.resource_list_id resource_list_id,
R1.name resource_list_name,
M.resource_list_member_id resource_list_member_id
FROM pa_resource_lists R1, pa_implementations I,
pa_resource_list_members M
WHERE R1.uncategorized_flag = 'Y'
AND R1.business_group_id = I.business_group_id
AND R1.resource_list_id = M.resource_list_id;
SELECT start_date, completion_date,nvl(baseline_funding_flag,'N') baseline_funding_flag
FROM pa_projects_all
WHERE project_id = p_pa_project_id;
SELECT max(budget_version_id)
FROM pa_budget_versions
WHERE project_id = p_pa_project_id
AND budget_type_code = 'AR'
AND budget_status_code = 'W'
AND version_number = 1;
SELECT nvl(pf.task_id,0) pa_task_id,
to_char(Null) pm_task_reference,
to_char(Null) resource_alias,
p_resource_list_member_id,
DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date) budget_start_date,
DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date) budget_end_date,
to_char(null) period_name,
'Default Created by Projects AMG Agreement Funding' description,
to_number(null) raw_cost,
to_number(null) burdened_cost,
sum(nvl(pf.projfunc_allocated_amount,0)) revenue,
to_number(null) quantity,
p_pm_product_code,
p_pm_budget_reference,
p_Attribute_Category,
p_Attribute1,
p_Attribute2,
p_Attribute3,
p_Attribute4,
p_Attribute5,
p_Attribute6,
p_Attribute7,
p_Attribute8,
p_Attribute9,
p_Attribute10,
p_Attribute11,
p_Attribute12,
p_Attribute13,
p_Attribute14,
p_Attribute15,
/* Bug 2866699 Added due to Fin plan impact */
pf.PROJFUNC_CURRENCY_CODE,
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL),
to_char(NULL)
/* Bug 2866699 Added due to Fin plan impact ends here */
FROM pa_project_fundings pf, pa_tasks t
WHERE pf.project_id = p_pa_project_id
AND pf.task_id = t.task_id(+)
AND pf.budget_type_code in ('BASELINE', 'DRAFT')
group by nvl(pf.task_id,0),
pf.projfunc_currency_code, /*projfunc_currency_code added for bug 3078560 */
DECODE(nvl(pf.task_id,0),0,p_start_date,t.start_date),
DECODE(nvl(pf.task_id,0),0,p_end_date,t.completion_date);