[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
log_message('Year_End_Rollover: ' || 'Before Update in Upd_Ins_Budget_Acct_Line');
UPDATE
PA_Budget_Acct_Lines
SET
Curr_Ver_Budget_Amount = nvl(Curr_Ver_Budget_Amount,0) + P_Transfer_Amount,
Curr_Ver_Available_Amount = nvl(Curr_Ver_Available_Amount,0) + P_Transfer_Amount,
Request_ID = P_Request_ID
WHERE
Budget_Version_ID = P_Budget_Version_ID
-- AND GL_Period_Name = P_Period_Name
AND Start_Date = P_Period_Start_Date
AND Code_Combination_ID = P_CCID ;
log_message('Year_End_Rollover: ' || SQL%ROWCOUNT || ' record(s) updated');
log_message('Year_End_Rollover: ' || 'After Update in Upd_Ins_Budget_Acct_Line');
IF l_Update_Count = 0 -- No Data found and no records are updated
THEN
---------------------------------------------------------------------------------
-- Create new Budget Summary Account's data for the non-existent details
---------------------------------------------------------------------------------
INSERT INTO
PA_BUDGET_ACCT_LINES (
Budget_Acct_Line_ID,
Budget_Version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
Accounted_Amount,
Creation_date,
Created_By,
Last_Update_date,
Last_Updated_By,
Request_ID,
Last_Update_Login
)
VALUES (
PA_BUDGET_ACCT_LINES_S.NextVal,
P_Budget_Version_ID,
P_Period_Name,
P_Period_Start_Date,
P_Period_End_Date,
P_CCID,
0,
0,
-- 0,
P_Transfer_Amount,
P_Transfer_Amount,
0,
sysdate,
FND_GLOBAL.User_ID,
sysdate,
FND_GLOBAL.User_ID,
P_Request_ID,
FND_GLOBAL.User_ID
) ;
SELECT
PROJ.project_id Project_ID,
PROJ.Project_Status_Code Project_Status_Code,
PROJ.Completion_Date Project_Completion_Date,
BV.budget_version_id Budget_Version_ID,
BV.budget_type_code Budget_Type_Code,
BCO.Encumbrance_Type_ID Encumbrance_Type_ID,
BCO.External_Budget_Code External_Budget_Code,
BCO.GL_Budget_Version_ID GL_Budget_Version_ID,
BEM.Raw_Cost_Flag Raw_Cost_Flag,
BEM.Burdened_Cost_Flag Burdened_Cost_Flag,
BEM.Entry_Level_Code Entry_Level_Code,
BCO.Balance_type Balance_Type
FROM
PA_Budgetary_Control_Options BCO,
PA_Projects PROJ,
PA_Budget_Versions BV,
PA_Budget_Entry_Methods BEM
WHERE
PROJ.Carrying_Out_organization_id =
NVL(P_Organization_ID, PROJ.Carrying_Out_organization_id )
AND ( P_From_Project_Number IS NULL OR PROJ.Segment1 >= P_From_Project_Number )
AND ( P_To_Project_Number IS NULL OR PROJ.Segment1 <= P_To_Project_Number )
AND nvl(PROJ.template_flag,'N') <> 'Y'
-- AND PROJ.project_status_code <> 'CLOSED'
AND PROJ.Project_ID = BV.Project_ID
AND BV.Project_ID = BCO.Project_ID
AND BV.Budget_Type_Code = BCO.Budget_Type_Code
AND BV.Budget_Status_Code = 'B' -- Baselined ONLY Budget
AND BV.Current_Flag = 'Y' -- Latest Budget Version
AND BV.Budget_Entry_Method_Code = BEM.Budget_Entry_Method_Code
AND BCO.Balance_Type = 'E'
AND nvl(BCO.Yr_End_Rollover_Year,-1) <> P_Closing_Year;
SELECT
BCBL.Resource_List_Member_ID Resource_List_Member_ID,
BCBL.Project_ID Project_ID,
/* Commented out for bug 2838796 BCBL.Task_ID Task_ID, */
RA.Task_ID Task_ID, --Changed to RA.Task_Id bug 2838796
BL.Code_Combination_ID CCID,
SUM(nvl(BCBL.Budget_Period_To_Date, 0) -
( nvl(BCBL.Actual_Period_To_Date, 0) + nvl(BCBL.Encumb_Period_To_Date, 0)) ) Transfer_Amount, /* Bug#13598400: Added nvls */
MAX(BL.START_DATE) Budget_period_start_date
FROM
PA_Resource_Assignments RA,
PA_Budget_Lines BL,
PA_BC_Balances BCBL
WHERE
RA.Budget_Version_ID = l_Bslnd_Budget_Version_ID
AND RA.Resource_Assignment_ID = BL.Resource_Assignment_ID
AND BCBL.Start_Date = BL.Start_Date
AND RA.Resource_List_Member_ID= BCBL.Resource_List_Member_ID
AND RA.Budget_Version_ID = BCBL.Budget_Version_ID
AND RA.Project_ID = BCBL.Project_ID
-- AND NVL(RA.Task_ID, 0) = BCBL.Task_ID -- bug 2838796
AND ( (BCBL.Balance_Type = 'BGT' and RA.Task_ID = BCBL.Task_ID ) --bug 2838796 added start
OR
( BCBL.Balance_Type <> 'BGT' AND
(( RA.Task_ID = Decode(l_Entry_Level_Code, 'P', 0,
'T', BCBL.Top_Task_ID,
'L', BCBL.Task_ID ))
OR
( l_Entry_Level_Code = 'M' AND
RA.Task_ID IN ( BCBL.Top_Task_ID, BCBL.Task_ID )
))
)
) --bug 2838796 added ends
AND BCBL.Start_Date BETWEEN l_Cur_Yr_First_Prd_Start_Dt
AND l_Cur_Yr_Last_Prd_Start_Dt
AND BCBL.Set_Of_Books_ID = l_Set_Of_Books_ID
AND BCBL.Start_Date IN ( SELECT A.Start_Date
FROM GL_Period_Statuses A
WHERE
A.application_id = 101
AND A.set_of_books_id = l_Set_Of_Books_ID
AND A.Period_Year = P_Closing_year
AND A.Adjustment_Period_Flag <> 'Y'
AND A.Period_Type = l_Accounted_Period_Type
)
GROUP BY
BCBL.Resource_List_Member_ID,
BCBL.Project_ID,
/* BCBL.Task_ID, commented out for bug 2838796 */
RA.Task_ID,
BL.Code_Combination_ID
HAVING SUM(nvl(BCBL.Budget_Period_To_Date, 0) -
( nvl(BCBL.Actual_Period_To_Date, 0) + nvl(BCBL.Encumb_Period_To_Date, 0)) ) > 0; /*Bug#13598400: Added nvls */
PA_Sweeper.Update_Act_Enc_Balance (
X_Return_Status => l_Return_Status,
X_Error_Message_Code => l_Msg_Data
);
PA_Fck_Util.debug_msg('Year_End_Rollover: ' || 'Error occured while running sweeper process PA_Sweeper.Update_Act_Enc_Balance');
SELECT Set_Of_Books_ID
INTO l_Set_Of_Books_ID
FROM PA_Implementations;
SELECT Accounted_Period_Type
INTO l_Accounted_Period_Type
FROM GL_Sets_Of_Books
WHERE Set_Of_Books_ID = l_Set_Of_Books_ID ;
select 'CC'
into l_cc_budget_type_code
from pa_budgetary_control_options cc
where cc.project_id = l_Project_ID
and cc.external_budget_code = 'CC';
SELECT Top_Task_ID
INTO l_Top_Task_ID
FROM PA_TASKS
WHERE Task_ID = l_Task_ID
AND Project_ID = l_Project_ID ;
SELECT Parent_Member_ID
INTO l_Parent_Member_ID
FROM PA_RESOURCE_LIST_MEMBERS
WHERE Resource_List_Member_ID = l_Resource_List_Member_ID;
SELECT
SUM(Accounted_DR - Accounted_CR)
INTO
l_UnSwept_Amount
FROM
PA_BC_PACKETS
WHERE
Budget_Version_ID = l_Bslnd_Budget_Version_ID
AND Project_ID = l_Project_ID
-- AND nvl(Top_Task_ID,0) = l_Top_Task_ID --bug 2838796 start change
AND (( nvl(l_Task_ID,0) = Decode(l_Entry_Level_Code, 'P', 0,
'T', Top_Task_ID,
'L', Task_ID ))
OR
( l_Entry_Level_Code = 'M' AND
l_Task_ID IN ( Top_Task_ID, Task_ID ))) --bug 2838796 end change
AND Status_Code = 'A' -- Approved Transactions
AND Balance_Posted_Flag = 'N' -- Not yet posted/swept
AND Result_Code like 'P%' -- Pass Code series
AND Resource_List_Member_ID = l_Resource_List_Member_ID
AND Parent_Resource_ID = l_Parent_Member_ID ;
SELECT
SUM( bcbl.Actual_Period_To_Date + bcbl.Encumb_Period_To_Date)
INTO
l_Amount_with_no_budgetline
FROM
PA_BC_BALANCES bcbl
WHERE
bcbl.Budget_Version_ID = l_Bslnd_Budget_Version_ID
AND bcbl.balance_type <> 'BGT'
AND bcbl.Project_ID = l_Project_ID
AND (( nvl(l_Task_ID,0) = Decode(l_Entry_Level_Code, 'P', 0,
'T', bcbl.Top_Task_ID,
'L', bcbl.Task_ID ))
OR
( l_Entry_Level_Code = 'M' AND
l_Task_ID IN ( bcbl.Top_Task_ID, bcbl.Task_ID )))
AND bcbl.Resource_List_Member_ID = l_Resource_List_Member_ID
AND BCBL.Start_Date BETWEEN l_Cur_Yr_First_Prd_Start_Dt
AND l_Cur_Yr_Last_Prd_Start_Dt
AND NOT EXISTS (select 1 from pa_budget_lines bl,
pa_resource_assignments ra
where bl.resource_assignment_id = ra.resource_assignment_id
and ra.resource_list_member_id = bcbl.resource_list_member_id
and ra.project_id = bcbl.project_id
and ra.task_id = l_Task_ID
and ra.budget_version_id = bcbl.budget_version_id
and bl.start_date = bcbl.start_date);
SELECT
RA.Resource_Assignment_ID
INTO
l_Work_Resource_Assign_ID
FROM
PA_Budget_Versions BV,
PA_Resource_Assignments RA
WHERE
RA.resource_list_member_id = l_Resource_List_Member_ID
AND RA.Budget_Version_ID = l_Work_Budget_Version_ID
AND RA.Project_ID = l_Project_ID
AND RA.Task_ID = l_Task_ID
AND BV.Budget_Status_Code = 'W'
AND BV.Budget_Version_ID = RA.Budget_Version_ID
AND BV.Project_ID = RA.Project_ID
AND BV.Project_ID = l_Project_ID;
SELECT Budget_Version_ID
INTO l_New_Budget_Version_ID
FROM PA_Budget_Versions
WHERE Project_ID = l_Project_ID
AND Budget_Type_Code = l_Budget_Type_Code
AND Current_Flag = 'Y'
AND Budget_Status_Code = 'B';
UPDATE PA_BC_COMMITMENTS
SET Budget_Version_ID = l_New_Budget_Version_ID,
Request_ID = P_Request_ID
WHERE Project_ID = l_Project_ID
AND Budget_Version_ID = l_Bslnd_Budget_Version_ID ;
PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => l_New_Budget_Version_ID,
p_calling_mode => 'YEAR END ROLLOVER');
UPDATE PA_Budgetary_Control_Options
SET
Yr_End_Rollover_Message = l_Message_Code_Error,
Yr_End_Rollover_Year = -1,
Yr_End_Rollover_Flag = 'E',
Request_ID = P_Request_ID
WHERE
Project_ID = l_Project_ID
AND Budget_Type_Code = l_Budget_Type_Code ;
log_message('Doing ROLLBACK after update PA_Budgetary_Control_Options');
log_message('Year_End_Rollover: ' || 'Updated as Project Closed / End Date problem');
UPDATE PA_Budgetary_Control_Options
SET
Yr_End_Rollover_Amount = l_Total_Rollover_Amount,
Yr_End_Rollover_Year = P_Closing_Year,
Yr_End_Rollover_Flag = 'S', -- successfully done
Request_ID = P_Request_ID,
Yr_End_Rollover_Message= NULL
WHERE
Project_ID = l_Project_ID
AND Budget_Type_Code = l_Budget_Type_Code ;
log_message('Year_End_Rollover: ' || 'Updated SUCCESSFULLY');
log_message('Year_End_Rollover: ' || 'Updated Budget Version Record');
UPDATE PA_Budget_Versions
SET Change_Reason_Code = l_Change_Reason_Code
WHERE Budget_Version_ID = l_New_Budget_Version_ID ;
/* Bug 5726535 - The Year End Rollover Flag is updated to 'E' here in an autonomous transaction if an
unhandled exception occurs */
Upd_Yr_End_Rollover_Flag_To_E (
P_Request_ID => P_Request_ID);
SELECT
MIN(PSTS.Start_Date),
MAX(PSTS.start_date),
MAX(PSTS.end_date)
INTO
X_Cur_Yr_First_Prd_Start_Dt,
X_Cur_Yr_Last_Prd_Start_Dt,
X_Cur_Yr_Last_Prd_End_Dt
FROM
GL_Period_Statuses PSTS
WHERE
PSTS.application_id = 101
AND PSTS.set_of_books_id = P_Set_Of_Books_ID
AND PSTS.Period_Year = P_Fiscal_year
AND PSTS.Adjustment_Period_Flag <> 'Y'
AND PSTS.Period_Type = P_Accounted_Period_Type ;
SELECT
MIN(PSTS.Start_Date),
MIN(PSTS.End_Date)
INTO
X_Next_Yr_First_Prd_Start_Dt,
X_Next_Yr_First_Prd_End_Dt
FROM
GL_Period_Statuses PSTS
WHERE
PSTS.application_id = 101 -- = 8721 ?
AND PSTS.set_of_books_id = P_Set_Of_Books_ID
AND PSTS.Period_Year = P_Fiscal_year + 1
AND PSTS.Adjustment_Period_Flag <> 'Y'
AND PSTS.Period_Type = P_Accounted_Period_Type ;
SELECT
GS.Period_Name
INTO
X_Cur_Yr_Last_Prd_Name
FROM
GL_Period_Statuses GS
WHERE
GS.Set_Of_Books_ID = P_Set_Of_Books_ID
AND GS.Application_ID = 101
AND GS.Closing_Status = 'O'
AND GS.Start_Date = X_Cur_Yr_Last_Prd_Start_Dt
AND GS.End_Date = X_Cur_Yr_Last_Prd_End_Dt
AND GS.Period_Type = P_Accounted_Period_Type ;
SELECT
GS.Period_Name
INTO
X_Next_Yr_First_Prd_Name
FROM
GL_Period_Statuses GS
WHERE
GS.Set_Of_Books_ID = P_Set_Of_Books_ID
AND GS.Application_ID = 101
AND GS.Closing_Status IN ('O', 'F')
AND GS.Start_Date = X_Next_Yr_First_Prd_Start_Dt
AND GS.End_Date = X_Next_Yr_First_Prd_End_Dt
AND GS.Period_Type = P_Accounted_Period_Type ;
l_Update_Count NUMBER;
- Adding txn_currency_code in update for more clarity to indicate the update will
always update just one record. We get the budget_line_id of the updated record
and pass to mrc api */
BEGIN
SELECT Projfunc_Currency_Code
INTO l_Txn_Curr_Code
FROM PA_Projects_All a, PA_Budget_Versions b, PA_Resource_Assignments c
WHERE a.Project_Id = b.Project_Id
AND b.Budget_Version_Id = c.Budget_Version_Id
AND c.Resource_Assignment_Id = P_Resource_Assignment_Id;
UPDATE
PA_Budget_Lines
SET
Burdened_Cost = NVL(Burdened_Cost,0) + l_Burdened_Transfer_Amount,
Raw_Cost = NVL(Raw_Cost,0) + l_Raw_Transfer_Amount,
Request_ID = P_Request_ID
WHERE
Resource_Assignment_ID = P_Resource_Assignment_ID
AND Start_Date = P_Period_Start_Date
AND Txn_Currency_Code = l_Txn_Curr_Code /* FPB2: MRC */
AND Code_Combination_ID = P_CCID;
l_Update_Count := SQL%ROWCOUNT;
log_message('Year_End_Rollover: ' || 'New/Closing,Records updated:'
||P_Period_New_Or_Closing||';'||l_Update_Count);
If l_Update_Count = 0 and P_Period_New_Or_Closing = 'NEW' then
-- Update without using code combination_id ...
-- Case: Where there is a line that is already existing with
-- a diff. code combination ..
UPDATE
PA_Budget_Lines
SET
Burdened_Cost = NVL(Burdened_Cost,0) + l_Burdened_Transfer_Amount,
Raw_Cost = NVL(Raw_Cost,0) + l_Raw_Transfer_Amount,
Request_ID = P_Request_ID
WHERE
Resource_Assignment_ID = P_Resource_Assignment_ID
AND Start_Date = P_Period_Start_Date
AND Txn_Currency_Code = l_Txn_Curr_Code
RETURNING Code_Combination_ID into P_New_CCID;
l_Update_Count := SQL%ROWCOUNT;
log_message('Year_End_Rollover: ' || '2nd Update - New/Closing,Records updated,new CCID:'
||P_Period_New_Or_Closing||';'||p_new_ccid||';'||l_Update_Count);
IF l_Update_Count = 0 -- No Data Found ie. NO record are updated
THEN
IF P_DEBUG_MODE = 'Y' THEN
log_message('Year_End_Rollover: ' || 'Inserting into PA_BUDGET_LINES');
PA_BUDGET_LINES_V_PKG.Insert_Row (
X_ROWID => l_RowID,
X_Resource_Assignment_Id => P_Resource_Assignment_ID,
X_Budget_Version_Id => P_Budget_Version_ID,
X_Project_Id => P_Project_ID,
X_Task_Id => P_Task_ID,
X_Resource_List_Member_Id => P_Resource_List_Member_Id,
X_Description => NULL,
X_Start_Date => P_Period_Start_Date,
X_End_Date => P_Period_End_Date,
X_Period_Name => P_Period_Name,
X_Quantity => l_quantity,
X_Unit_Of_Measure => NULL,
X_Track_As_Labor_Flag => NULL,
X_Raw_Cost => l_Raw_Transfer_Amount,
X_Burdened_Cost => l_Burdened_Transfer_Amount,
X_Revenue => l_revenue,
X_Change_Reason_Code => NULL,
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.User_ID,
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_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- Bug Fix: 4569365. Removed MRC code.
-- X_Mrc_Flag => 'Y', /* FPB2: MRC */
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_Cost_source => 'M',
X_Burdened_Cost_source => 'M',
X_quantity_source => 'M',
X_revenue_source => 'M',
X_standard_Bill_rate => NULL,
X_Average_Bill_rate => NULL,
X_Average_Cost_rate => NULL,
X_project_Assignment_Id => -1,
X_plan_Error_Code => NULL,
X_total_plan_revenue => NULL,
X_total_plan_raw_Cost => NULL,
X_total_plan_Burdened_Cost => NULL,
X_total_plan_quantity => NULL,
X_Average_Discount_percentage => NULL,
X_Cost_rejection_Code => NULL,
X_Burden_rejection_Code => NULL,
X_revenue_rejection_Code => NULL,
X_other_rejection_Code => NULL);
/* FPB2: As of now amounts are not part of the below update and hence MRC need
not be called. If amount fields are added to the below update, MRC apis need
to be called to maintain MRC in budgets */
-- Updating Budget Version Amounts
UPDATE
PA_Budget_Versions
SET
Raw_Cost = NVL(Raw_Cost,0) - l_Raw_Transfer_Amount,
Burdened_Cost = NVL(Burdened_Cost,0) - l_Burdened_Transfer_Amount
WHERE
Budget_Version_ID = P_Budget_Version_ID;
END IF; -- End of inserting a new record into PA_BUDGET_LINES
Select
1
INTO
l_Exist_Flag
FROM
PA_Budget_Versions
WHERE
Project_ID = P_Project_ID
AND Budget_Type_Code = P_Budget_Type_Code
AND Budget_Status_Code = 'W' ;
log_message('Year_End_Rollover: Calling Update_Yr_End_Rollover_Flag'); /* Bug 5726535 */
log_message('Year_End_Rollover: ' || 'Updated PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to ''P''');
SELECT
Budget_Version_ID
INTO
X_Work_Budget_Version_ID
FROM
PA_BUDGET_VERSIONS
WHERE
Project_ID = P_Project_ID
AND Budget_Type_Code = P_Budget_Type_Code
AND Budget_Status_Code = 'W';
FOR i IN ( SELECT RA.Resource_List_Member_ID,
RA.Resource_Assignment_ID,
RA.Project_ID,
RA.Task_ID,
BL.Code_Combination_ID,
BL.Start_Date
FROM PA_Resource_Assignments RA,
PA_Budget_Lines BL
WHERE RA.Resource_Assignment_ID = BL.Resource_Assignment_ID
AND RA.Budget_Version_ID = P_Budget_Version_ID )
LOOP
/* FPB2: As of now amounts are not part of the below update and hence MRC need
not be called. If amount fields are added to the below update, MRC apis need
to be called to maintain MRC in budgets */
UPDATE
PA_Budget_Lines BL
SET
BL.Code_Combination_ID = i.Code_Combination_ID
WHERE
BL.Start_Date = i.Start_Date
AND BL.Resource_Assignment_ID = (
SELECT RA.Resource_Assignment_ID
FROM PA_Resource_Assignments RA
WHERE RA.Budget_Version_ID = l_Work_Budget_Version_ID
AND RA.Resource_List_Member_ID = i.Resource_List_Member_ID
AND RA.Project_ID = i.Project_ID
AND RA.Task_ID = i.Task_ID );
Delete from PA_Budget_Acct_Lines where Budget_version_ID = l_Work_Budget_Version_ID;
log_message('Year_End_Rollover: ' || 'Inserting new records into PA_Budget_Acct_Lines for newly created Draft Budget Version = '|| l_Work_Budget_Version_ID );
INSERT INTO PA_Budget_Acct_Lines (
Budget_Acct_Line_ID,
Budget_version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
Accounted_Amount,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_ID
)
SELECT
PA_Budget_Acct_Lines_S.NEXTVAL,
l_Work_Budget_Version_ID, -- Should be working Budget Version
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
0,
sysdate,
1234,
sysdate,
1234,
1234,
P_Request_ID
FROM
PA_Budget_Acct_Lines
WHERE
Budget_Version_ID = P_Budget_Version_ID ;
END; -- End of inserting New Budget Account Lines
insert into pa_bc_balances(
PROJECT_ID,
TASK_ID,
TOP_TASK_ID,
RESOURCE_LIST_MEMBER_ID,
BALANCE_TYPE,
SET_OF_BOOKS_ID,
BUDGET_VERSION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
PERIOD_NAME,
START_DATE,
END_DATE,
PARENT_MEMBER_ID,
BUDGET_PERIOD_TO_DATE,
ACTUAL_PERIOD_TO_DATE,
ENCUMB_PERIOD_TO_DATE,
REQUEST_ID)
select pa.project_id,
pa.task_id,
pt.top_task_id,
pa.resource_list_member_id,
'BGT',
p_set_of_books_id,
pbv.budget_version_id,
l_date,
l_user,
l_user,
l_date,
l_user,
pb.PERIOD_NAME,
pb.START_DATE,
pb.END_DATE,
rm.PARENT_MEMBER_ID,
pb.burdened_cost,
0,
0,
l_request_id
from pa_budget_lines pb,
pa_resource_assignments pa,
pa_tasks pt,
pa_resource_list_members rm,
pa_budget_versions pbv
where pbv.budget_version_id = p_budget_version_id
and pa.resource_assignment_id = pb.resource_assignment_id
and pa.task_id = pt.task_id (+)
and pa.budget_version_id = pbv.budget_version_id
and rm.resource_list_member_id = pa.resource_list_member_id;
INSERT INTO
PA_BC_Balances (
Project_ID,
Task_ID,
Resource_List_Member_ID,
Set_Of_Books_ID,
Budget_Version_ID,
Balance_Type,
Start_Date,
End_Date,
Created_By,
Creation_date,
Last_Updated_By,
Last_Update_date,
Last_Update_Login,
Top_Task_ID,
Parent_Member_ID,
Request_ID,
Program_ID,
Program_Application_ID,
Program_Update_Date,
Period_Name,
Actual_Period_To_Date,
Budget_period_To_Date,
Encumb_Period_To_Date
)
SELECT
Project_ID,
Task_ID,
Resource_List_Member_ID,
Set_Of_Books_ID,
p_budget_version_id,
Balance_Type,
Start_Date,
End_Date,
l_user,
l_date, -- Creation_Date
l_user,
l_date, -- Last_Update_Date
l_user,
Top_Task_ID,
Parent_Member_ID,
Request_ID,
Program_ID,
Program_Application_ID,
Program_Update_Date,
Period_Name,
Actual_Period_To_Date,
Budget_period_To_Date,
Encumb_Period_To_Date
FROM
PA_BC_BALANCES
WHERE
Budget_Version_ID = p_last_baselined_version_id
AND Balance_Type <> 'BGT' ;
SELECT distinct
Project_ID,
Budget_Type_Code
INTO
l_Project_ID,
l_Budget_Type_Code
FROM
PA_Budget_Versions
WHERE
Budget_Version_ID = P_Budget_Version_ID;
UPDATE
PA_Budgetary_Control_Options
SET
Yr_End_Rollover_Message = P_Message_Name,
Yr_End_Rollover_Flag = 'E',
Yr_End_Rollover_Year = -1,
Request_ID = P_Request_ID
WHERE
Project_ID = l_Project_ID
AND Budget_Type_Code = l_Budget_Type_Code;
SELECT Yr_End_Rollover_Flag
INTO l_Yr_End_Rollover_Flag
FROM PA_Budgetary_Control_Options
WHERE Project_ID = p_Project_ID
AND Budget_Type_Code = p_Budget_Type_Code;
/* This procedure updates PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'P'
in an autonomous transaction */
PROCEDURE Upd_Yr_End_Rollover_Flag_To_P(
P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
P_Project_ID IN PA_Projects_all.Project_ID%TYPE,
P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE PA_Budgetary_Control_Options
SET Yr_End_Rollover_Flag = 'P',
Request_ID = P_Request_ID
WHERE Project_ID = P_Project_ID
AND Budget_Type_Code = P_Budget_Type_Code;
/* This procedure updates PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'E'
in an autonomous transaction */
PROCEDURE Upd_Yr_End_Rollover_Flag_To_E(
P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE PA_Budgetary_Control_Options
SET Yr_End_Rollover_Flag = 'E'
WHERE Request_ID = P_Request_ID;