[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.project_id --Award_Project_Id
INTO Award_Id_Check
FROM gms_awards b,
pa_projects a
WHERE b.award_id = X_Award_Id
AND a.project_id = b.award_project_id;
SELECT 1
INTO Project_Check
FROM PA_PROJECTS
WHERE NAME = X_Award_Project_Name;
SELECT 1
INTO Project_Check
FROM PA_PROJECTS
WHERE SEGMENT1 = X_Award_Project_Number;
PROCEDURE UPDATE_GMS_AWARDS(X_Award_Id IN NUMBER
,X_Agreement_Id IN NUMBER
,X_Award_Project_Id IN NUMBER) IS
BEGIN
UPDATE GMS_AWARDS
SET (AWARD_PROJECT_ID,
AGREEMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN) =
(SELECT X_Award_Project_Id,
X_Agreement_Id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
FROM dual)
WHERE AWARD_ID = X_Award_Id;
END UPDATE_GMS_AWARDS;
PROCEDURE UPDATE_PROJECT_ADD_INFO(X_Project_Id IN NUMBER,
X_IDC_Schedule_Id IN NUMBER,
X_IDC_Schedule_Fixed_Date IN DATE,
X_Labor_Invoice_Format_Id IN NUMBER,
X_Non_Labor_Invoice_Format_Id IN NUMBER,
X_Billing_Cycle_Id IN NUMBER,
X_Billing_Offset IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
BEGIN
--dbms_output.put_line('Inside UPDATE Project Info');
UPDATE PA_PROJECTS_ALL
SET cost_ind_rate_sch_id = X_IDC_Schedule_Id,
cost_ind_sch_fixed_date = X_IDC_Schedule_Fixed_Date,
labor_invoice_format_id = X_Labor_Invoice_Format_Id,
non_labor_invoice_format_Id = X_Non_Labor_Invoice_Format_Id,
billing_cycle_id = X_Billing_Cycle_Id,
billing_offset = X_Billing_Offset,
last_UPDATE_date = sysdate,
last_UPDATEd_by = fnd_global.user_id,
last_UPDATE_login = fnd_global.login_id
WHERE project_id = X_Project_Id;
FND_MESSAGE.SET_NAME('GMS','GMS_NO_PROJECT_UPDATED');
UPDATE pa_tasks pt
SET cost_ind_rate_sch_id= X_IDC_Schedule_Id,
cost_ind_sch_fixed_date=X_IDC_Schedule_Fixed_Date
WHERE project_id = X_project_id;
,p_procedure_name => 'UPDATE_PROJECT_ADD_INFO'
);
END UPDATE_PROJECT_ADD_INFO;
Select * FROM pa_projects_all
WHERE project_id = x_award_project_id;
Select 'Y' FROM pa_proj_elements
WHERE project_id = x_award_project_id;
/* bug 5282308 - commented the insert */
/*
INSERT INTO pa_project_options
(project_id,
option_code,
last_UPDATE_date,
last_UPDATEd_by,
creation_date,
created_by,
last_UPDATE_login)
SELECT x_award_project_id,
option_code,
SYSDATE,
fnd_global.user_id,
SYSDATE ,
fnd_global.user_id,
fnd_global.login_id
FROM pa_options
WHERE option_code NOT IN ( 'STRUCTURES', 'STRUCTURES_SS' );
SELECT project_id
FROM pa_project_copy_overrides
WHERE project_id = x_award_project_id
AND field_name = x_field_name;
PROCEDURE INSERT_AWARD_PROJECT(X_Customer_Id IN NUMBER,
X_Bill_to_customer_id IN NUMBER,
X_Award_Project_Name IN VARCHAR2,
X_Award_Project_Number IN VARCHAR2,
X_Award_Id IN NUMBER,
X_Carrying_Out_Organization_Id IN NUMBER,
X_IDC_Schedule_Id IN NUMBER,
X_IDC_Schedule_Fixed_Date IN DATE,
X_Labor_Invoice_Format_Id IN NUMBER,
X_Non_Labor_Invoice_Format_Id IN NUMBER,
X_Start_Date IN DATE,
X_End_Date IN DATE,
X_Close_Date IN DATE,
X_Person_Id IN NUMBER,
X_Billing_Frequency IN VARCHAR2,
X_Billing_cycle_id IN NUMBER,
X_Billing_offset IN NUMBER,
X_Award_Project_Id OUT NOCOPY NUMBER,
X_Bill_To_Address_Id OUT NOCOPY NUMBER,
X_Ship_To_Address_Id OUT NOCOPY NUMBER,
X_App_Short_Name OUT NOCOPY VARCHAR2,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
X_Product_Code VARCHAR2(30) ;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_AWARD_PROJECT';
SELECT START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM gms_personnel
WHERE award_id = X_Award_Id
AND award_role ='AM'; --For Bug 3229539
G_Stage := '(500:Select from gms_implementations)';
select
to_char(nvl(org_id,-999))
INTO
x_default_org_id
FROM
gms_implementations;
SELECT decode(X_Billing_Frequency,'ANNUALLY',365,'DAILY',1,'MONTHLY',30,'QUARTERLY',91,'WEEKLY',7)
INTO St_Billing_Cycle
FROM dual;
G_Stage := '(510:Select from pa_projects)';
SELECT project_id
INTO X_Created_From_Project_Id
FROM PA_PROJECTS
WHERE project_type = 'AWARD_PROJECT'
AND template_flag = 'Y'
AND (segment1 = 'AWD_PROJ_'||x_default_org_id
OR segment1 = 'AWD_PROJ_-999');
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_AWARD_PROJECT');
G_Stage := '(590:Calling update_project_add_info)';
update_project_add_info(St_Award_Project_Id,
X_IDC_Schedule_Id,
X_IDC_Schedule_Fixed_Date,
X_Labor_Invoice_Format_Id,
X_Non_Labor_Invoice_Format_Id,
X_Billing_Cycle_id,
X_Billing_Offset,
P_Return_Status,
X_Msg_Data);
END INSERT_AWARD_PROJECT;
SELECT
a.project_id, --Award Project Id for which funding is to be created
a.start_date,
nvl(a.completion_date,sysdate),
b.award_id,
b.agreement_id
INTO
Store_Project_Id,
Store_Project_Start_Date,
Store_Project_End_Date,
Store_Award_Id,
Store_Agreement_Id
FROM
PA_PROJECTS a,
GMS_AWARDS b,
GMS_INSTALLMENTS c
WHERE
c.installment_id = X_Installment_Id and
b.Award_Id = c.Award_Id and
a.project_id = b.award_project_id;
PROCEDURE INSERT_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
X_Agreement_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Allocated_Amount IN NUMBER,
X_Date_Allocated IN DATE,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
INSERT INTO PA_PROJECT_FUNDINGS(PROJECT_FUNDING_ID,
AGREEMENT_ID,
PROJECT_ID,
BUDGET_TYPE_CODE,
ALLOCATED_AMOUNT,
DATE_ALLOCATED,
-- Bug 2475640 : Added for 11.5 PA-J certification.
FUNDING_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
INVPROC_CURRENCY_CODE,
REVPROC_CURRENCY_CODE,
PROJECT_ALLOCATED_AMOUNT,
PROJFUNC_ALLOCATED_AMOUNT,
INVPROC_ALLOCATED_AMOUNT,
REVPROC_ALLOCATED_AMOUNT,
-- Bug 2475640 changes End
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES(X_Project_Funding_Id,
X_Agreement_Id,
X_Award_Project_Id,
'DRAFT',
X_Allocated_Amount,
X_Date_Allocated,
-- Bug 2475640 : Added for 11.5 PA-J certification.
x_currency_code,
x_currency_code,
x_currency_code,
x_currency_code,
x_currency_code,
X_Allocated_Amount,
X_Allocated_Amount,
X_Allocated_Amount,
X_Allocated_Amount,
-- Bug 2475640 Changes End
SYSDATE,
fnd_global.user_id,
SYSDATE ,
fnd_global.user_id,
fnd_global.login_id);
-- Bug 2475640 : Added Following UPDATE statement to UPDATE funding_category
UPDATE pa_project_fundings proj
SET funding_category = 'ORIGINAL'
WHERE proj.project_funding_id IN (SELECT min(project_funding_id)
FROM pa_project_fundings
WHERE project_id = X_award_project_id
AND agreement_id = X_agreement_id
GROUP BY agreement_id,project_id,NVL(task_id,0))
AND proj.project_funding_id = X_Project_Funding_Id
AND funding_category is null;
UPDATE pa_project_fundings
SET funding_category='ADDITIONAL'
WHERE funding_category is null
AND project_funding_id = X_Project_Funding_Id;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_DETAIL_PROJECT_FUNDING - 1');
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_DETAIL_PROJECT_FUNDING - 2');
END INSERT_DETAIL_PROJECT_FUNDING;
SELECT 1
INTO Summary_Funding_Check
FROM PA_SUMMARY_PROJECT_FUNDINGS
WHERE Agreement_Id = X_Agreement_Id
AND Project_Id = X_Award_Project_Id;
SELECT
nvl(sum(nvl(total_unbaselined_amount,0)),0),
nvl(sum(nvl(total_baselined_amount,0)),0)
INTO
St_Total_Unbaselined_Amount ,
St_Total_Baselined_Amount
FROM
PA_SUMMARY_PROJECT_FUNDINGS
WHERE
Project_Id = X_Award_Project_Id;
SELECT nvl(total_unbaselined_amount,0),
nvl(total_baselined_amount,0)
INTO St_Total_Unbaselined_Amount ,
St_Total_Baselined_Amount
FROM PA_SUMMARY_PROJECT_FUNDINGS
WHERE Agreement_id = X_Agreement_Id
AND Project_Id = X_Award_Project_Id;
PROCEDURE UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Total_Unbaselined_Amount IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
UPDATE PA_SUMMARY_PROJECT_FUNDINGS
SET TOTAL_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
-- Bug 2475640 : Added for 11.5 PA-J certification.
PROJECT_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
PROJFUNC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
INVPROC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
REVPROC_UNBASELINED_AMOUNT = X_Total_Unbaselined_Amount,
-- Bug 2475640 changes End
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE AGREEMENT_ID = X_Agreement_Id
AND PROJECT_ID = X_Award_Project_Id;
X_Err_Stage := 'Could not find a row to UPDATE for Agreement ' ||to_char(X_Agreement_Id)||' and Project '||to_char(X_Award_Project_Id);
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_PA_SUMM_PROJECT_FUNDING');
END UPDATE_PA_SUMM_PROJECT_FUNDING;
PROCEDURE INSERT_SUMMARY_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Total_Unbaselined_Amount IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
INSERT INTO
PA_SUMMARY_PROJECT_FUNDINGS(AGREEMENT_ID,
PROJECT_ID,
TOTAL_UNBASELINED_AMOUNT,
-- Bug 2475640 : Added for 11.5 PA-J certification.
FUNDING_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
INVPROC_CURRENCY_CODE,
REVPROC_CURRENCY_CODE,
PROJECT_UNBASELINED_AMOUNT,
PROJFUNC_UNBASELINED_AMOUNT,
INVPROC_UNBASELINED_AMOUNT,
REVPROC_UNBASELINED_AMOUNT,
-- Bug 2475640 Changes END
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES(X_Agreement_Id,
X_Award_Project_Id,
X_Total_Unbaselined_Amount,
-- Bug 2475640 : Added for 11.5 PA-J certification.
x_currency_code,
x_currency_code,
x_currency_code,
x_currency_code,
x_currency_code,
X_Total_Unbaselined_Amount,
X_Total_Unbaselined_Amount,
X_Total_Unbaselined_Amount,
X_Total_Unbaselined_Amount,
-- Bug 2475640 Changes END
SYSDATE,
fnd_global.user_id,
SYSDATE ,
fnd_global.user_id,
fnd_global.login_id);
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_SUMMARY_PROJECT_FUNDING ');
END INSERT_SUMMARY_PROJECT_FUNDING;
SELECT 1
INTO
Draft_Budget_Check
FROM PA_BUDGET_VERSIONS
WHERE
Project_Id = X_Award_Project_Id and
budget_type_code = 'AR' and
budget_status_code in ('W' , 'S');
PROCEDURE UPDATE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
X_Old_Allocated_Amount IN NUMBER,
X_New_Allocated_Amount IN NUMBER,
X_Old_Date_Allocated IN DATE,
X_New_Date_Allocated IN DATE,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
St_Budget_Type_Code VARCHAR2(30);
Select
BUDGET_TYPE_CODE
INTO
St_Budget_Type_Code
FROM
PA_PROJECT_FUNDINGS
WHERE
PROJECT_FUNDING_ID = X_Project_Funding_Id;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 1');
UPDATE PA_PROJECT_FUNDINGS
SET ALLOCATED_AMOUNT = X_New_Allocated_Amount,
-- Bug 2475640 : Added for 11.5 PA-J certification.
PROJECT_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
PROJFUNC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
INVPROC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
REVPROC_ALLOCATED_AMOUNT = X_New_Allocated_Amount,
-- Bug 2475640 Changes End
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE PROJECT_FUNDING_ID = X_Project_Funding_Id;
X_Err_Stage := 'Could not find a row to UPDATE for Project Funding '||to_char(X_Project_Funding_Id) ;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 2');
UPDATE PA_PROJECT_FUNDINGS
SET DATE_ALLOCATED = X_New_Date_Allocated,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE PROJECT_FUNDING_ID = X_Project_Funding_Id;
X_Err_Stage := 'Could not find a row to UPDATE for Project Funding '||to_char(X_Project_Funding_Id) ;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_DETAIL_PROJECT_FUNDING - 3');
,p_procedure_name => 'UPDATE_DETAIL_PROJECT_FUNDING'
);
END UPDATE_DETAIL_PROJECT_FUNDING;
PROCEDURE DELETE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
delete
FROM
PA_PROJECT_FUNDINGS
WHERE
PROJECT_FUNDING_ID = X_Project_Funding_Id;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_DETAIL_PROJECT_FUNDING');
,p_procedure_name => 'DELETE_DETAIL_PROJECT_FUNDING'
);
END DELETE_DETAIL_PROJECT_FUNDING;
PROCEDURE DELETE_SUMMARY_PROJECT_FUNDING(X_Agreement_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
delete
FROM
PA_SUMMARY_PROJECT_FUNDINGS
WHERE
AGREEMENT_ID = X_Agreement_Id and
PROJECT_ID = X_Award_Project_Id;
X_Err_Stage := 'There were no rows deleted FROM PA_SUMMARY_PROJECT_FUNDINGS';
X_Err_Stage := 'GMS_NO_SUMM_FUNDING_DELETED';
FND_MESSAGE.SET_NAME('GMS','GMS_NO_SUMM_FUNDING_DELETED');
END DELETE_SUMMARY_PROJECT_FUNDING;
PROCEDURE DELETE_BASELINED_VERSIONS(X_Award_Project_Id IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
--p_msg_count NUMBER;
SELECT
BUDGET_VERSION_ID
FROM
PA_BUDGET_VERSIONS
WHERE
PROJECT_ID = X_Award_Project_Id and
BUDGET_TYPE_CODE = 'AR';
SELECT
RESOURCE_ASSIGNMENT_ID
FROM
PA_RESOURCE_ASSIGNMENTS
WHERE
BUDGET_VERSION_ID = X_Budget_Version_Id;
SELECT
RESOURCE_LIST_ASSIGNMENT_ID
FROM
pa_resource_list_assignments
WHERE
project_id = X_Award_Project_Id ;
Delete FROM PA_BUDGET_LINES
WHERE
RESOURCE_ASSIGNMENT_ID = GRA_RECORD.RESOURCE_ASSIGNMENT_ID;
DELETE FROM PA_RESOURCE_ASSIGNMENTS
WHERE
BUDGET_VERSION_ID = GBV_RECORD.BUDGET_VERSION_ID;
DELETE FROM pa_resource_list_uses
WHERE
resource_list_assignment_id =RESOURCE_LIST_RECORD.resource_list_assignment_id;
DELETE FROM pa_resource_list_assignments
WHERE PROJECT_ID=X_Award_Project_Id ;
DELETE
FROM
PA_BUDGET_VERSIONS
WHERE
PROJECT_ID = X_Award_Project_Id and
BUDGET_TYPE_CODE = 'AR';
X_Err_Stage := ('No rows delete FROM PA_BUDGET_VERSIONS for Project Id '||X_Award_Project_Id);
X_Err_Stage := 'GMS_NO_BUD_VERS_DELETED';
FND_MESSAGE.SET_NAME('GMS','GMS_NO_BUD_VERS_DELETED');
,p_procedure_name => 'DELETE_BASELINED_VERSIONS'
);
END DELETE_BASELINED_VERSIONS;
PA_AGREEMENTS_PKG.INSERT_ROW(
X_ROWID => St_Row_Id,
X_AGREEMENT_ID => St_Agreement_Id,
X_CUSTOMER_ID => X_Customer_Id,
X_AGREEMENT_NUM => X_Agreement_Num,
X_AGREEMENT_TYPE => X_Agreement_Type,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_OWNED_BY_PERSON_ID => X_Owned_By_Person_Id,
X_TERM_ID => X_Term_Id,
X_REVENUE_LIMIT_FLAG => nvl(X_Revenue_Limit_Flag, 'N'),-- Bug 1841288 : Changed 'Y'to'N'
X_AMOUNT => 0,
X_DESCRIPTION => NULL,
X_EXPIRATION_DATE => X_Close_Date,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_TEMPLATE_FLAG => NULL,
X_PM_AGREEMENT_REFERENCE => NULL,
X_PM_PRODUCT_CODE => NULL,
-- Bug 2475640 : Added parameters for 11.5 PA-J certification.
X_OWNING_ORGANIZATION_ID => NULL,
X_AGREEMENT_CURRENCY_CODE => pa_currency.get_currency_code,
X_INVOICE_LIMIT_FLAG => nvl(X_Invoice_Limit_Flag, 'N'),
/*Passed value of X_Invoice_Limit_flag rather than X_Revenue_Limit_Flag for bug 6642901 */
X_ORG_ID => l_org_id
);
PROCEDURE DELETE_AGREEMENT(X_Agreement_Id IN NUMBER,
RETCODE OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2) IS
X_Row_Id VARCHAR2(30);
SELECT
rowid
INTO
X_Row_Id
FROM
PA_AGREEMENTS
WHERE
AGREEMENT_ID = X_Agreement_Id;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : DELETE_AGREEMENT');
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_AGREEMENT');
SELECT COUNT(X_Agreement_Id)
INTO
Check_Funding_Exists
FROM PA_SUMMARY_PROJECT_FUNDINGS
WHERE AGREEMENT_ID = X_Agreement_Id;
ERRBUF := 'Cannot delete Agreement while Funding Exists ';
PA_AGREEMENTS_PKG.DELETE_ROW( X_ROWID => X_Row_Id); -- Bug 1672982
,p_procedure_name => 'DELETE_AGREEMENT'
);
END DELETE_AGREEMENT;
FUNCTION ALLOW_REV_LIMIT_FLAG_UPDATE(X_Agreement_Id IN NUMBER,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
St_Award_Project_Id NUMBER;
Select
Award_Project_Id,
award_id,
revenue_distribution_rule,
billing_distribution_rule
INTO
St_Award_Project_Id,
St_award_id,
St_revenue_distribution_rule,
St_billing_distribution_rule
FROM
GMS_AWARDS_ALL
WHERE
AGREEMENT_ID = X_Agreement_Id
and award_template_flag = 'DEFERRED';
Select
count(draft_invoice_num)
INTO
St_Invoice_Num_Count
FROM
PA_DRAFT_INVOICES
WHERE
project_id = St_Award_Project_Id
and agreement_id = X_Agreement_Id;
Select
count(draft_revenue_num)
INTO
St_Revenue_Num_Count
FROM
PA_DRAFT_REVENUES
WHERE
project_id = St_Award_Project_Id
and agreement_id = X_Agreement_Id;
SELECT sum(nvl(total_funding_amount,0)),
sum(nvl(total_revenue_amount,0)),
sum(nvl(total_billed_amount,0))
INTO x_total_funding_amount,
x_total_revenue_amount,
x_total_billed_amount
FROM gms_summary_project_fundings gspf,
gms_installments ins
WHERE ins.installment_id = gspf.installment_id
and ins.award_id = St_award_id;
SELECT sum(bill_amount),
sum(revenue_amount)
INTO x_bill_amount, x_revenue_amount
FROM pa_events a,gms_awards_all b
WHERE a.project_id = b.award_project_id
and b.award_id = St_award_id;
/* Changed the Message Name FROM 'GMS_REVENUE_OR_INVOICES_EXIST' to 'GMS_CANNOT_UPDATE_FLAG'
as message Text of previous message got changed in 11i */
/* X_Err_Stage := 'GMS_CANNOT_UPDATE_FLAG';
FND_MESSAGE.SET_NAME('GMS','GMS_CANNOT_UPDATE_FLAG'); Commented for bug 6642901 and replaced with 2 new messages specific to revenue and invoice.*/
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: ALLOW_REV_LIMIT_FLAG_UPDATE');
END ALLOW_REV_LIMIT_FLAG_UPDATE;
PROCEDURE UPDATE_AGREEMENT(X_Agreement_Id IN NUMBER,
X_Agreement_Num IN VARCHAR2 DEFAULT NULL,
X_Agreement_Type IN VARCHAR2 DEFAULT NULL,
X_Revenue_Limit_Flag IN VARCHAR2 DEFAULT NULL,
X_Invoice_Limit_Flag IN VARCHAR2 DEFAULT NULL, /*Bug 6642901*/
X_Customer_Id IN NUMBER DEFAULT NULL,
X_Owned_By_Person_Id IN NUMBER DEFAULT NULL,
X_Term_Id IN NUMBER DEFAULT NULL,
X_Amount IN NUMBER DEFAULT 0,
X_Close_Date IN DATE DEFAULT NULL,
RETCODE OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2) IS
Store_Row_Id VARCHAR2(30);
SELECT
ROWID,
NVL(X_Term_Id,TERM_ID),
NVL(X_Customer_Id,CUSTOMER_ID),
NVL(X_Owned_By_Person_Id,OWNED_BY_PERSON_ID),
NVL(X_Agreement_Num,AGREEMENT_NUM),
NVL(X_Close_Date,EXPIRATION_DATE),
NVL(X_Agreement_Type,AGREEMENT_TYPE),
NVL(X_Revenue_Limit_Flag, REVENUE_LIMIT_FLAG),
NVL(X_Invoice_Limit_Flag, INVOICE_LIMIT_FLAG), /*Bug 6642901*/
nvl(AMOUNT,0),
agreement_currency_code -- Bug 2475640 : Added for 11.5 PA-J certification.
INTO
Store_Row_Id,
Store_Term_Id,
Store_Customer_Id,
Store_Owned_By_Person_Id,
Store_Agreement_Num,
Store_Expiration_Date,
Store_Agreement_Type,
Store_Revenue_Limit_Flag,
Store_Invoice_Limit_Flag, /*Bug 6642901*/
Store_Amount,
Store_agreement_currency_code -- Bug 2475640 : Added for 11.5 PA-J certification.
FROM
PA_AGREEMENTS
WHERE
AGREEMENT_ID = X_Agreement_Id;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING : UPDATE_AGREEMENT');
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AGREEMENT');
PA_AGREEMENTS_PKG.UPDATE_ROW (
X_ROWID => Store_Row_Id,
X_AGREEMENT_ID => X_AGREEMENT_ID,
X_CUSTOMER_ID => Store_Customer_Id,
X_AGREEMENT_NUM => Store_Agreement_Num,
X_AGREEMENT_TYPE => Store_Agreement_Type,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_OWNED_BY_PERSON_ID => Store_Owned_By_Person_Id,
X_TERM_ID => Store_Term_Id,
X_REVENUE_LIMIT_FLAG => Store_Revenue_Limit_Flag,
X_AMOUNT => Store_Amount,
X_DESCRIPTION => NULL,
X_EXPIRATION_DATE => Store_Expiration_Date,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_TEMPLATE_FLAG => NULL,
X_PM_AGREEMENT_REFERENCE => NULL,
X_PM_PRODUCT_CODE => NULL,
-- Bug 2475640 : Added parameters for 11.5 PA-J certification.
X_OWNING_ORGANIZATION_ID => NULL,
X_AGREEMENT_CURRENCY_CODE => Store_agreement_currency_code,
X_INVOICE_LIMIT_FLAG => Store_Invoice_Limit_Flag
/*Replaced Store_Revenue_Limit_Flag with Store_Invoice_Limit_Flag for bug 6642901*/
);
,p_procedure_name => 'UPDATE_AGREEMENT'
);
END UPDATE_AGREEMENT;
PROCEDURE INSERT_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
X_Budget_Type IN VARCHAR2,
X_Start_Date IN DATE,
X_End_Date IN DATE,
X_Predefined_Flag IN VARCHAR2,
X_Accumulation_Flag IN VARCHAR2,
X_Award_Flag IN VARCHAR2,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
BEGIN
INSERT INTO PA_BUDGET_TYPES(BUDGET_TYPE_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN ,
BUDGET_TYPE,
START_DATE_ACTIVE,
BUDGET_AMOUNT_CODE,
PREDEFINED_FLAG,
ACCUMULATION_FLAG,
END_DATE_ACTIVE)
--AWARD_FLAG )--11i change
VALUES(X_Budget_Type_Code,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
X_Budget_Type,
X_Start_Date,
'C',
X_Predefined_Flag,
X_Accumulation_Flag,
X_End_Date);
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: INSERT_AWARD_BUDGET_TYPE');
END INSERT_AWARD_BUDGET_TYPE;
PROCEDURE UPDATE_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
X_Budget_Type IN VARCHAR2,
X_Start_Date IN DATE,
X_End_Date IN DATE,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
BEGIN
UPDATE PA_BUDGET_TYPES
SET Budget_Type = X_Budget_Type
/* Start_Date_Active = X_Start_Date,
End_Date_Active = X_End_Date */
WHERE
Budget_Type_Code = X_Budget_Type_Code;
X_Err_Stage := 'UPDATE FAILED: No Budget_Type found with Budget Type Code '||X_Budget_Type_Code;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AWARD_BUDGET_TYPE');
END UPDATE_AWARD_BUDGET_TYPE;
PROCEDURE DELETE_AWARD_BUDGET_TYPE(X_Budget_Type_Code IN VARCHAR2,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
BEGIN
DELETE FROM PA_BUDGET_TYPES
WHERE
BUDGET_TYPE_CODE = X_Budget_Type_Code;
X_Err_Stage := 'DELETE of BUDGET_TYPE Failed: No Budget Type found with Budget Type Code '||X_Budget_Type_Code;
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: DELETE_AWARD_BUDGET_TYPE');
END DELETE_AWARD_BUDGET_TYPE;
Select a.Address_id
Into St_Bill_To_Address_Id
From Ra_Addresses a,
Ra_Site_Uses su
Where a.Address_Id = su.Address_id
And a.Customer_Id = X_Customer_Id
And Nvl(su.Status, 'A') = 'A'
And su.Site_Use_Code = 'BILL_TO'
And su.primary_flag = 'Y' ;
SELECT su.cust_acct_site_id
INTO St_Bill_To_Address_Id
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_SITE_USES SU
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.cust_account_id = X_Customer_Id
AND Nvl(su.Status, 'A') = 'A'
AND su.Site_Use_Code = 'BILL_TO'
AND su.primary_flag = 'Y' ;
Select a.Address_id
Into St_Ship_To_Address_Id
From Ra_Addresses a,
Ra_Site_Uses su
Where a.Address_Id = su.Address_id
And a.Customer_Id = X_Customer_Id
And Nvl(su.Status, 'A') = 'A'
And su.Site_Use_Code = 'SHIP_TO'
And su.primary_flag = 'Y' ;
SELECT su.cust_acct_site_id
INTO St_Ship_To_Address_Id
FROM hz_cust_acct_sites acct_site,
Hz_cust_site_Uses su
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.cust_account_id = X_Customer_Id
AND Nvl(su.Status, 'A') = 'A'
AND su.Site_Use_Code = 'SHIP_TO'
AND su.primary_flag = 'Y' ;
Select su.Contact_Id
Into X_Ship_To_Contact_Id
From hz_cust_acct_sites acct_site,
Hz_cust_site_Uses su
Where acct_site.cust_acct_site_id = su.cust_acct_site_id
And acct_site.cust_account_id = X_Customer_Id
And Nvl(su.Status, 'A') = 'A'
And su.Site_Use_Code = 'SHIP_TO'
And su.primary_flag = 'Y' ;
Select su.Contact_Id
Into X_Ship_To_Contact_Id
From hz_cust_acct_sites acct_site,
Hz_cust_site_Uses su
Where acct_site.cust_acct_site_id = su.cust_acct_site_id
And acct_site.cust_account_id = X_Customer_Id
And Nvl(su.Status, 'A') = 'A'
And su.Site_Use_Code = 'BILL_TO'
And su.primary_flag = 'Y' ;
Select
contact_id
INTO
St_Bill_To_Contact_Id
FROM
PA_PROJECT_CONTACTS
WHERE project_id = X_Award_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'BILLING';
Select
contact_id
INTO
St_Ship_To_Contact_Id
FROM
PA_PROJECT_CONTACTS
WHERE project_id = X_Award_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'SHIPPING';
delete FROM GMS_AWARDS_CONTACTS
WHERE
award_id = X_Award_Id
and usage_code in ('BILL_TO','SHIP_TO')
and primary_flag = 'Y';
/* UPDATE GMS_AWARDS_CONTACTS
set primary_flag = 'N'
WHERE
award_id = X_Award_Id
and usage_code in ('BILL_TO','SHIP_TO')
and primary_flag = 'Y';
/* Inserting Bill_To_Contact */
BEGIN
--------------------------------------------------------------------------------
/* Overriding the Ship_To_Contact_Id obtained FROM PA_PROJECT_CONTACTS with the one obtained FROM Award Template */
IF X_Awd_Templ_Bill_Cont_Id IS NOT NULL THEN
ST_Bill_To_Contact_Id := X_Awd_Templ_Bill_Cont_Id;
SELECT 1
INTO
X_Cust_Bill_Cont_Exists
FROM
GMS_AWARDS_CONTACTS
WHERE
award_id = X_Award_id
and customer_id = X_Customer_Id
and contact_id = St_Bill_To_Contact_Id
and usage_code = 'BILL_TO';
INSERT INTO GMS_AWARDS_CONTACTS(AWARD_ID
,CONTACT_ID
,CUSTOMER_ID
,USAGE_CODE
,PRIMARY_FLAG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
VALUES(X_Award_Id
,St_Bill_To_Contact_Id
,NVL(X_Bill_to_Customer_id,X_Customer_Id)
,'BILL_TO'
,'Y'
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
UPDATE GMS_AWARDS_CONTACTS
set
PRIMARY_FLAG = 'Y'
WHERE
award_id = X_Award_Id
and customer_id = NVL(X_Bill_to_Customer_id,X_Customer_Id)
and contact_id = St_Bill_To_Contact_Id
and usage_code = 'BILL_TO';
SELECT 1
INTO
X_Cust_Ship_Cont_Exists
FROM
GMS_AWARDS_CONTACTS
WHERE
award_id = X_Award_id
and customer_id = X_Customer_Id
and contact_id = St_Ship_To_Contact_Id
and usage_code = 'SHIP_TO';
INSERT INTO GMS_AWARDS_CONTACTS(AWARD_ID
,CONTACT_ID
,CUSTOMER_ID
,USAGE_CODE
,PRIMARY_FLAG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
VALUES(X_Award_Id
,St_Ship_To_Contact_Id
,NVL(X_Bill_to_customer_id,X_Customer_Id)
,'SHIP_TO'
,'Y'
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id);
UPDATE GMS_AWARDS_CONTACTS
set
PRIMARY_FLAG = 'Y'
WHERE
award_id = X_Award_Id
and customer_id = X_Customer_Id
and contact_id = St_Ship_To_Contact_Id
and usage_code = 'SHIP_TO';
G_Stage := '(130:Calling insert_award_project)';
INSERT_AWARD_PROJECT(X_Customer_Id,
X_Bill_to_customer_id,
X_Awd_Proj_Name,
X_Awd_Proj_Number,
X_Award_Id,
X_Carrying_Out_Organization_Id,
X_IDC_Schedule_Id,
X_IDC_Schedule_Fixed_Date,
X_Labor_Invoice_Format_Id,
X_Non_Labor_Invoice_Format_Id,
X_Start_Date,
X_End_Date,
X_Close_Date,
X_Person_Id,
X_Billing_Frequency,
X_Billing_Cycle_Id,
X_Billing_Offset,
Store_Project_Id,
Store_Bill_To_Addr_Id,
Store_Ship_To_Addr_Id,
X_App_Short_Name,
X_Err_Code,
X_Err_Stage);
UPDATE PA_PROJECT_CUSTOMERS
set BILL_TO_ADDRESS_ID = X_Bill_To_Address_Id_IN
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE project_id = Store_Project_Id
and customer_id = X_Customer_Id;
UPDATE PA_PROJECT_CUSTOMERS
set SHIP_TO_ADDRESS_ID = X_Ship_To_Address_Id_IN
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE project_id = Store_Project_Id
and customer_id = X_Customer_Id;
UPDATE PA_PROJECT_CONTACTS
set contact_id = X_Bill_To_Contact_Id_IN
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE project_id = Store_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'BILLING';
UPDATE PA_PROJECT_CONTACTS
set contact_id = X_Ship_To_Contact_Id_IN
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = fnd_global.user_id
,LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE project_id = Store_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'SHIPPING';
INSERT_AWARD_BUDGET_TYPE(X_Budget_Type_Code => to_char(X_Award_Id),
X_Budget_Type => X_Project_Name,
X_Start_Date => to_date('01011951','DDMMYYYY'),
X_End_Date => NULL,
X_Predefined_Flag => 'N',
X_Accumulation_Flag => 'N',
X_Award_Flag => 'Y',
X_Err_Code => X_Err_Code,
X_Err_Stage => X_Err_Stage) ;
G_Stage := '(200:Calling update_gms_awards)';
/* UPDATE GMS_AWARDS with the Agreement_Id and Award_Project_Id */
UPDATE_GMS_AWARDS(X_Award_Id,
Store_Agreement_Id,
Store_Project_Id);
PROCEDURE UPDATE_KEY_MEMBERS(X_Award_Project_Id IN NUMBER,
X_Person_Id_Old IN NUMBER,
X_Person_Id_New IN NUMBER,
X_Start_Date IN DATE,
X_End_Date IN DATE,
X_Err_Code OUT NOCOPY VARCHAR2,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
St_Start_Date DATE;
Select
start_date_active,
end_date_active
INTO
St_Start_Date,
St_End_Date
FROM PA_PROJECT_PLAYERS
WHERE PROJECT_ID = X_Award_Project_Id
and PERSON_ID = X_Person_Id_Old
and PROJECT_ROLE_TYPE = 'PROJECT MANAGER';
UPDATE pa_project_players
set person_id = X_Person_Id_New,
start_date_active = X_Start_Date,
end_date_active = X_End_Date
WHERE
project_id = X_Award_Project_Id
and person_id = X_Person_Id_Old
and project_role_type = 'PROJECT MANAGER';
UPDATE pa_project_parties
set resource_source_id = X_Person_Id_New,
start_date_active = X_Start_Date,
end_date_active = X_End_Date
WHERE
project_id = X_Award_Project_Id
and resource_source_id = X_Person_Id_Old
and project_role_id in (SELECT project_role_id FROM pa_project_role_types
WHERE project_role_type = 'PROJECT MANAGER');
END UPDATE_KEY_MEMBERS;
PROCEDURE update_award_project
( x_award_id IN NUMBER
,x_award_project_id IN NUMBER
,x_agreement_id IN NUMBER
,x_project_number IN VARCHAR2
,x_project_name IN VARCHAR2
,X_Customer_Id IN NUMBER
,X_Bill_to_Customer_Id IN NUMBER
,X_Carrying_Out_Organization_Id IN NUMBER
,X_IDC_Schedule_Id IN NUMBER
,X_IDC_Schedule_Fixed_Date IN DATE
,X_Labor_Invoice_Format_Id IN NUMBER
,X_Non_Labor_Invoice_Format_Id IN NUMBER
,X_Person_Id_Old IN NUMBER
,X_Person_Id_New IN NUMBER
,X_Term_Id IN NUMBER
,X_Start_Date IN DATE
,X_End_Date IN DATE
,X_Close_Date IN DATE
,X_Agreement_Type IN VARCHAR2
,X_Revenue_Limit_Flag IN VARCHAR2
,X_Invoice_Limit_Flag IN VARCHAR2 /*Bug 6642901*/
,X_Billing_Frequency IN VARCHAR2
,X_Billing_cycle_Id IN NUMBER
,X_Billing_Offset IN NUMBER
,X_Bill_To_Address_Id_IN IN NUMBER
,X_Ship_To_Address_Id_IN IN NUMBER
,X_output_tax_code IN VARCHAR2
,X_retention_tax_code IN VARCHAR2
,X_Bill_To_Address_Id_OUT OUT NOCOPY NUMBER
,X_Ship_To_Address_Id_OUT OUT NOCOPY NUMBER
,X_App_Short_Name OUT NOCOPY VARCHAR2
,X_Msg_Count OUT NOCOPY NUMBER
,retcode OUT NOCOPY VARCHAR2
,errbuf OUT NOCOPY VARCHAR2
) IS
X_Product_Code VARCHAR2(30);
X_Task_ID_To_UPDATE NUMBER(15);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AWARD_PROJECT';
SELECT org_id
INTO l_org_id
FROM gms_awards_all
WHERE award_id = X_award_id;
SELECT TO_CHAR(NVL(org_id,-999))
INTO x_default_org_id
FROM gms_implementations;
G_Stage := '(310:select from pa_projects)';
SELECT project_id
INTO X_Created_From_Project_Id
FROM PA_PROJECTS
WHERE project_type = 'AWARD_PROJECT'
AND template_flag = 'Y'
AND (segment1 = 'AWD_PROJ_'||x_default_org_id
OR
segment1 = 'AWD_PROJ_-999'
);
FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_MULTI_FUNDING: UPDATE_AWARD_PROJECT');
G_Stage := '(320:Select from pa_tasks)';
SELECT task_id, trunc(start_date), trunc(completion_date)
INTO X_Task_ID_To_UPDATE, X_Task_Proj_Start_Date, X_Task_Proj_Compl_Date
FROM pa_tasks
WHERE project_id = X_Award_Project_Id;
The Key Member information will be UPDATEd separately inorder to prevent Multiple Project Managers FROM
being created.
X_Key_Members_IN_REC.PERSON_ID := X_Person_Id;
X_Tasks_IN_REC.pa_task_id := X_Task_ID_To_UPDATE ;
/*-------- First Call to UPDATE_PROJECT ----------*/
X_Project_IN_REC.START_DATE := LEAST(X_Start_Date,X_Task_Proj_Start_Date);
** Task was not passed. PA_PROJECT_PUB.UPDATE_PROJECT will raise an error PA_TASK_REF_AND_ID_MISSING
** when task_id and task_reference both are NULL
**/
X_Tasks_IN_TBL(1) := X_Tasks_IN_REC;
G_Stage := '(340:Calling pa_project_pub.update_project)';
PA_PROJECT_PUB.UPDATE_PROJECT
( p_api_version_number => 1.0
,p_init_msg_list => 'T'
,p_msg_count => p_msg_count
,p_msg_data => X_Err_Stage
,p_return_status => X_Err_Code
,p_project_in => X_Project_IN_REC
,p_project_out => X_Project_OUT_REC
,p_pm_product_code => X_Product_Code
,p_key_members => X_Key_Members_IN_TBL --Key Members will be UPDATEd separately
,p_class_categories => X_Class_Categories_IN_TBL
,p_tasks_in => X_Tasks_IN_TBL
,p_tasks_out => X_Tasks_OUT_TBL
,p_workflow_started => X_Workflow_Started
--Bug 3576717
,p_deliverables_in => X_Deliverable_IN_TBL
--,p_deliverables_out => X_Deliverable_OUT_TBL (3650374)
,p_deliverable_actions_in => X_Deliverable_Action_IN_TBL
--,p_deliverable_actions_out => X_Deliverable_Action_OUT_TBL (3650374)
);
G_Stage := '(350:calling pa_project_pub.update_task)';
PA_PROJECT_PUB.UPDATE_TASK
(p_api_version_number => 1.0,
p_pa_project_id => X_Award_Project_Id,
p_pa_task_id => X_Task_Id_To_UPDATE,
p_init_msg_list => 'T',
p_msg_count => p_msg_count,
p_msg_data => X_Err_Stage,
p_return_status => X_Err_Code,
p_pm_project_reference => X_Awd_Proj_Number,
p_pm_task_reference => X_Awd_Proj_Number,
p_pm_product_code => X_Product_Code,
p_task_number => X_Awd_Proj_Number||'-'||'T1',
p_task_name => X_Awd_Proj_Number||'-'||'Tsk1',
p_task_start_date => X_Start_Date,
p_task_completion_date => X_End_Date,
p_out_pa_task_id => X_Task_ID_OUT,
p_out_pm_task_reference => X_Task_PM_Reference_OUT);
/* ----------------Second Call to UPDATE_PROJECT-----------------------*/
X_Project_IN_REC.START_DATE := X_Start_Date;
G_Stage := '(370:Calling update_project_add_info)';
UPDATE_PROJECT_ADD_INFO
( X_Award_Project_Id,
X_IDC_Schedule_Id,
X_IDC_Schedule_Fixed_Date,
X_Labor_Invoice_Format_Id,
X_Non_Labor_Invoice_Format_Id,
X_Billing_Cycle_Id,
X_Billing_Offset,
X_Err_Code,
X_Err_Stage
);
/* UPDATE_KEY_MEMBERS
(X_Award_Project_Id,
X_Person_Id_Old,
X_Person_Id_New,
X_Start_Date,
X_End_Date,
X_Err_Code,
X_Err_Stage);
UPDATE_PROJ_CUST_CONTACTS(X_Award_Project_Id,
X_Customer_Id,
X_Bill_To_Address_PASSED,
X_Ship_To_Address_PASSED,
X_Bill_To_Contact_Id,
X_Ship_To_Contact_Id,
X_Err_Code,
X_Err_Stage);
G_Stage := '(380:select from pa_project_contacts)';
Select
contact_id
INTO
Bill_To_Contact_exists
FROM
PA_PROJECT_CONTACTS
WHERE project_id = X_Award_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'BILLING';
G_Stage := '(400:Select from pa_project_contacts)';
Select contact_id
INTO Ship_To_Contact_exists
FROM PA_PROJECT_CONTACTS
WHERE project_id = X_Award_Project_Id
and customer_id = X_Customer_Id
and project_contact_type_code = 'SHIPPING';
UPDATE_AWARD_BUDGET_TYPE(X_Budget_Type_Code => to_char(X_Award_Id),
X_Budget_Type => X_Project_Name,
X_Start_Date => X_Start_Date, -- Start Date and
X_End_Date => X_End_Date, -- END Date are no longer being used in
X_Err_Code => X_Err_Code, -- UPDATE of Budget Type
X_Err_Stage => X_Err_Stage) ;
/*-------------------------Check for Revenue Limit Flag UPDATE Allowability---------------*/
/* Bug 1841288 : Commenting out NOCOPY below code as all the hard_limit_flag validation is already been done
in check_funding_limit procedure of GMSAWEAW.fmb.
BEGIN
select
revenue_limit_flag
INTO
St_Revenue_Limit_Flag
FROM
PA_AGREEMENTS
WHERE
agreement_id = X_Agreement_Id;
IF ALLOW_REV_LIMIT_FLAG_UPDATE(X_Agreement_Id,
X_Err_Code,
X_Err_Stage) THEN
IF ((X_Revenue_Limit_Flag IS NOT NULL) AND (St_Revenue_Limit_Flag <> X_Revenue_Limit_Flag)) THEN
RETCODE := X_Err_Code;
G_Stage := '(430:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
X_Agreement_Num => X_Awd_Proj_Number,
X_Agreement_Type => X_Agreement_Type,
X_Revenue_Limit_Flag => X_Revenue_Limit_Flag,
X_Invoice_Limit_Flag => X_Invoice_Limit_Flag, /*Bug 6642901*/
X_Customer_Id => X_Customer_Id,
X_Owned_By_Person_Id => X_Person_Id_New,
X_Term_Id => X_Term_Id,
X_Amount => 0,
X_Close_Date => X_Close_Date,
RETCODE => X_Err_Code,
ERRBUF => X_Err_Stage);
END update_award_project;
PROCEDURE DELETE_AWARD_PROJECT(X_Award_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Agreement_Id IN NUMBER,
X_App_Short_Name OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
RETCODE OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2) IS
X_Awd_Proj_Number VARCHAR2(30);
G_Stage := '(600:SELECT PA_PROJECTS)';
SELECT
segment1
INTO
X_Awd_Proj_Number
FROM
PA_PROJECTS
WHERE
PROJECT_ID = X_Award_Project_Id;
G_Stage := '(610:CALLING PA_PROJECT_PUB.DELETE_PROJECT)';
PA_PROJECT_PUB.DELETE_PROJECT( P_API_VERSION_NUMBER => 1.0,
p_init_msg_list => 'T',
P_MSG_COUNT => p_msg_count,
P_MSG_DATA => X_Err_Stage,
P_RETURN_STATUS => X_Err_Code,
P_PM_PRODUCT_CODE => X_Product_Code,
P_PM_PROJECT_REFERENCE => X_Awd_Proj_Number,
P_PA_PROJECT_ID => X_Award_Project_Id );
/* Delete AWARD_BUDGET_TYPE */
-- this procedure is not used in 11i as there is no award_budget_type -- Suresh
/*
DELETE_AWARD_BUDGET_TYPE(to_char(X_Award_Id),
X_Err_Code ,
X_Err_Stage);
G_Stage := '(620:CALLING GMS_MULTI_FUNDING.DELETE_AGREEMENT)';
GMS_MULTI_FUNDING.DELETE_AGREEMENT
(X_Agreement_Id,
X_Err_Code,
X_Err_Stage);
,p_procedure_name => 'DELETE_AWARD_PROJECT'
,p_error_text => substrb(SQLERRM||' (stage='||g_stage||')',1,240)
);
END DELETE_AWARD_PROJECT;
G_Stage := '(650:SELECT FROM GMS_AWARDS)';
SELECT Award_Id
INTO X_Award_Id
FROM gms_awards
WHERE award_project_id = X_Award_Project_Id;
G_Stage := '(660:SELECT FROM RESOURCE_LIST)';
SELECT A.RESOURCE_LIST_MEMBER_ID, B.RESOURCE_LIST_ID
INTO X_Resource_List_Member_Id, X_Resource_List_Id
FROM PA_RESOURCE_LIST_MEMBERS A,
PA_RESOURCE_LISTS B,
PA_IMPLEMENTATIONS PI -- Bug 2108191
WHERE B.uncategorized_flag = 'Y'
AND B.resource_list_id = a.resource_list_id
AND B.business_group_id = PI.business_group_id
AND NVL(A.migration_code,'M') ='M'
AND NVL(B.migration_code,'M') ='M' ; -- Bug 2108191
G_Stage := '(680:CALLING PA_BUDGET_PUB.DELETE_DRAFT_BUDGET)';
PA_BUDGET_PUB.DELETE_DRAFT_BUDGET
(p_api_version_number => 1.0,
p_init_msg_list => 'T',
p_pa_project_id => X_Award_Project_Id,
p_pm_project_reference => to_char(X_Award_Id),
p_pm_product_code => 'GMS',
p_budget_type_code => 'AR',
p_msg_count => p_msg_count,
p_msg_data => X_Err_Stage,
p_return_status => X_Err_Code);
G_Stage := '(690:AFTER PA_BUDGET_PUB.DELETE_DRAFT_BUDGET CALL)';
Select
PA_PROJECT_FUNDINGS_S.NEXTVAL
INTO
St_Project_Funding_Id
FROM
DUAL;
/* UPDATE Award Project's Agreement with the new Amount */
--dbms_output.put_line('Amount before UPDATE agreement '||X_Allocated_Amount);
G_Stage := '(1020:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
X_Agreement_Num => NULL,
X_Agreement_Type => NULL,
X_Revenue_Limit_Flag => NULL,
X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
X_Customer_Id => NULL,
X_Owned_By_Person_Id => NULL,
X_Term_Id => NULL,
X_Amount => X_Allocated_Amount,
X_Close_Date => NULL,
RETCODE => X_Err_Code,
ERRBUF => X_Err_Stage);
G_Stage := '(1030:CALLING INSERT_DETAIL_PROJECT_FUNDING)';
INSERT_DETAIL_PROJECT_FUNDING(St_Project_Funding_Id,
X_Agreement_Id,
X_Award_Project_Id,
X_Allocated_Amount,
X_Date_Allocated,
X_Err_Code,
X_Err_Stage);
UPDATE GMS_PROJECT_FUNDINGS
SET PROJECT_FUNDING_ID = St_Project_Funding_Id
WHERE GMS_PROJECT_FUNDING_ID = X_GMS_Project_Funding_Id;
G_Stage := '(1050:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
UPDATE_PA_SUMM_PROJECT_FUNDING
(X_Agreement_Id,
X_Award_Project_Id,
X_Total_Unbaselined_Amount,
X_Err_Code,
X_Err_Stage);
G_Stage := '(1060:CALLING INSERT_SUMMARY_PROJECT_FUNDING)';
INSERT_SUMMARY_PROJECT_FUNDING
(X_Agreement_Id,
X_Award_Project_Id,
X_Total_Unbaselined_Amount,
X_Err_Code,
X_Err_Stage);
/* Deletes any existing Draft Revenue Budget and Creates a new Draft Revenue Budget */
G_Stage := '(1080:CALLING CREATE_AWARD_PROJECT_BUDGET)';
PROCEDURE UPDATE_AWARD_FUNDING(X_Project_Funding_Id IN NUMBER,
X_Installment_Id IN NUMBER,
X_Old_Allocated_Amount IN NUMBER,
X_New_Allocated_Amount IN NUMBER,
X_Old_Date_Allocated IN DATE,
X_New_Date_Allocated IN DATE,
X_App_Short_Name OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
RETCODE OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2) IS
X_Err_Code VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AWARD_FUNDING';
G_Stage := '(1110:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
/* UPDATE Award Project's Agreement with the new Amount */
X_Agreement_Amt := (X_New_Allocated_Amount - X_Old_Allocated_Amount);
GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
X_Agreement_Num => NULL,
X_Agreement_Type => NULL,
X_Revenue_Limit_Flag => NULL,
X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
X_Customer_Id => NULL,
X_Owned_By_Person_Id => NULL,
X_Term_Id => NULL,
X_Amount => X_Agreement_Amt,
X_Close_Date => NULL,
RETCODE => X_Err_Code,
ERRBUF => X_Err_Stage);
G_Stage := '(1120:CALLING UPDATE_DETAIL_PROJECT_FUNDING)';
UPDATE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id,
X_Old_Allocated_Amount,
X_New_Allocated_Amount,
X_Old_Date_Allocated,
X_New_Date_Allocated,
X_Err_Code,
X_Err_Stage);
G_Stage := '(1140:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id,
X_Award_Project_Id,
X_Total_Unbaselined_Amount,
X_Err_Code,
X_Err_Stage);
-- Deletes any existing draft revenue budget and creates new draft Revenue Budget
CREATE_AWARD_PROJECT_BUDGET( X_Award_Project_Id,
X_Total_Funding_Budget,
X_Project_Start_Date,
X_Project_End_Date,
X_App_Short_Name,
X_Err_Code,
X_Err_Stage);
END UPDATE_AWARD_FUNDING;
PROCEDURE DELETE_AWARD_FUNDING(X_Project_Funding_Id IN NUMBER,
X_Installment_Id IN NUMBER,
X_Allocated_Amount IN NUMBER,
X_App_Short_Name OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
RETCODE OUT NOCOPY VARCHAR2,
ERRBUF OUT NOCOPY VARCHAR2)IS
X_Err_Code VARCHAR2(1);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_project_fundings
WHERE agreement_id = p_agreement_id
AND project_id = p_award_project_id);
G_Stage := '(810:CALLING GMS_MULTI_FUNDING.UPDATE_AGREEMENT)';
/* UPDATE Award Project's Agreement with the new Amount */
GMS_MULTI_FUNDING.UPDATE_AGREEMENT(X_Agreement_Id => X_Agreement_Id,
X_Agreement_Num => NULL,
X_Agreement_Type => NULL,
X_Revenue_Limit_Flag => NULL,
X_Invoice_Limit_Flag => NULL, /*Bug 6642901*/
X_Customer_Id => NULL,
X_Owned_By_Person_Id => NULL,
X_Term_Id => NULL,
X_Amount => (-1*X_Allocated_Amount),
X_Close_Date => NULL,
RETCODE => X_Err_Code,
ERRBUF => X_Err_Stage);
G_Stage := '(820:CALLING DELETE_DETAIL_PROJECT_FUNDING)';
DELETE_DETAIL_PROJECT_FUNDING(X_Project_Funding_Id,
X_Err_Code,
X_Err_Stage);
G_Stage := '(840:CALLING DELETE_SUMMARY_PROJECT_FUNDING)';
G_Stage := '(850:CALLING DELETE_SUMMARY_PROJECT_FUNDING)';
DELETE_SUMMARY_PROJECT_FUNDING(X_Agreement_Id,
X_Award_Project_Id,
X_Err_Code,
X_Err_Stage);
G_Stage := '(880:CALLING PA_BUDGET_PUB.DELETE_DRAFT_BUDGET)';
PA_BUDGET_PUB.DELETE_DRAFT_BUDGET
(p_api_version_number => 1.0,
p_pa_project_id => X_Award_Project_Id,
p_pm_project_reference => to_char(X_Award_Id),
p_pm_product_code => 'GMS',
p_budget_type_code => 'AR',
p_msg_count => X_Msg_Count,
p_msg_data => X_Err_Stage,
p_return_status => X_Err_Code);
G_Stage := '(890:CALLING DELETE_BASELINED_VERSIONS)';
/* Delete all the Existing Baselined Budgets also */
DELETE_BASELINED_VERSIONS
(X_Award_Project_Id,
X_Err_Code,
X_Err_Stage);
G_Stage := '(900:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
UPDATE_PA_SUMM_PROJECT_FUNDING(X_Agreement_Id,
X_Award_Project_Id,
X_Total_Unbaselined_Amount,
X_Err_Code,
X_Err_Stage);
G_Stage := '(910:CALLING UPDATE_PA_SUMM_PROJECT_FUNDING)';
, p_procedure_name => 'DELETE_AWARD_FUNDING');
END DELETE_AWARD_FUNDING;