[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT G.period_name,
G.start_date,
G.end_date FROM
Gl_Periods G
WHERE
G.start_date >= c_st_dt and
G.end_date <= c_end_dt and
G.period_set_name = c_period_set_name and
G.period_type = c_period_type and
G.adjustment_period_flag = 'N'
ORDER BY G.start_date;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_date DATE := l_creation_date;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
UPDATE pa_proj_period_profiles SET
number_of_periods = l_tab_count,
period_name1 = l_period_name_tab(1),
period_name2 = l_period_name_tab(2),
period_name3 = l_period_name_tab(3),
period_name4 = l_period_name_tab(4),
period_name5 = l_period_name_tab(5),
period_name6 = l_period_name_tab(6),
period_name7 = l_period_name_tab(7),
period_name8 = l_period_name_tab(8),
period_name9 = l_period_name_tab(9),
period_name10 = l_period_name_tab(10),
period_name11 = l_period_name_tab(11),
period_name12 = l_period_name_tab(12),
period_name13 = l_period_name_tab(13),
period_name14 = l_period_name_tab(14),
period_name15 = l_period_name_tab(15),
period_name16 = l_period_name_tab(16),
period_name17 = l_period_name_tab(17),
period_name18 = l_period_name_tab(18),
period_name19 = l_period_name_tab(19),
period_name20 = l_period_name_tab(20),
period_name21 = l_period_name_tab(21),
period_name22 = l_period_name_tab(22),
period_name23 = l_period_name_tab(23),
period_name24 = l_period_name_tab(24),
period_name25 = l_period_name_tab(25),
period_name26 = l_period_name_tab(26),
period_name27 = l_period_name_tab(27),
period_name28 = l_period_name_tab(28),
period_name29 = l_period_name_tab(29),
period_name30 = l_period_name_tab(30),
period_name31 = l_period_name_tab(31),
period_name32 = l_period_name_tab(32),
period_name33 = l_period_name_tab(33),
period_name34 = l_period_name_tab(34),
period_name35 = l_period_name_tab(35),
period_name36 = l_period_name_tab(36),
period_name37 = l_period_name_tab(37),
period_name38 = l_period_name_tab(38),
period_name39 = l_period_name_tab(39),
period_name40 = l_period_name_tab(40),
period_name41 = l_period_name_tab(41),
period_name42 = l_period_name_tab(42),
period_name43 = l_period_name_tab(43),
period_name44 = l_period_name_tab(44),
period_name45 = l_period_name_tab(45),
period_name46 = l_period_name_tab(46),
period_name47 = l_period_name_tab(47),
period_name48 = l_period_name_tab(48),
period_name49 = l_period_name_tab(49),
period_name50 = l_period_name_tab(50),
period_name51 = l_period_name_tab(51),
period_name52 = l_period_name_tab(52),
period1_start_date = l_st_dt_tab(1),
period2_start_date = l_st_dt_tab(2),
period3_start_date = l_st_dt_tab(3),
period4_start_date = l_st_dt_tab(4),
period5_start_date = l_st_dt_tab(5),
period6_start_date = l_st_dt_tab(6),
period7_start_date = l_st_dt_tab(7),
period8_start_date = l_st_dt_tab(8),
period9_start_date = l_st_dt_tab(9),
period10_start_date = l_st_dt_tab(10),
period11_start_date = l_st_dt_tab(11),
period12_start_date = l_st_dt_tab(12),
period13_start_date = l_st_dt_tab(13),
period14_start_date = l_st_dt_tab(14),
period15_start_date = l_st_dt_tab(15),
period16_start_date = l_st_dt_tab(16),
period17_start_date = l_st_dt_tab(17),
period18_start_date = l_st_dt_tab(18),
period19_start_date = l_st_dt_tab(19),
period20_start_date = l_st_dt_tab(20),
period21_start_date = l_st_dt_tab(21),
period22_start_date = l_st_dt_tab(22),
period23_start_date = l_st_dt_tab(23),
period24_start_date = l_st_dt_tab(24),
period25_start_date = l_st_dt_tab(25),
period26_start_date = l_st_dt_tab(26),
period27_start_date = l_st_dt_tab(27),
period28_start_date = l_st_dt_tab(28),
period29_start_date = l_st_dt_tab(29),
period30_start_date = l_st_dt_tab(30),
period31_start_date = l_st_dt_tab(31),
period32_start_date = l_st_dt_tab(32),
period33_start_date = l_st_dt_tab(33),
period34_start_date = l_st_dt_tab(34),
period35_start_date = l_st_dt_tab(35),
period36_start_date = l_st_dt_tab(36),
period37_start_date = l_st_dt_tab(37),
period38_start_date = l_st_dt_tab(38),
period39_start_date = l_st_dt_tab(39),
period40_start_date = l_st_dt_tab(40),
period41_start_date = l_st_dt_tab(41),
period42_start_date = l_st_dt_tab(42),
period43_start_date = l_st_dt_tab(43),
period44_start_date = l_st_dt_tab(44),
period45_start_date = l_st_dt_tab(45),
period46_start_date = l_st_dt_tab(46),
period47_start_date = l_st_dt_tab(47),
period48_start_date = l_st_dt_tab(48),
period49_start_date = l_st_dt_tab(49),
period50_start_date = l_st_dt_tab(50),
period51_start_date = l_st_dt_tab(51),
period52_start_date = l_st_dt_tab(52),
period1_end_date = l_end_dt_tab(1),
period2_end_date = l_end_dt_tab(2),
period3_end_date = l_end_dt_tab(3),
period4_end_date = l_end_dt_tab(4),
period5_end_date = l_end_dt_tab(5),
period6_end_date = l_end_dt_tab(6),
period7_end_date = l_end_dt_tab(7),
period8_end_date = l_end_dt_tab(8),
period9_end_date = l_end_dt_tab(9),
period10_end_date = l_end_dt_tab(10),
period11_end_date = l_end_dt_tab(11),
period12_end_date = l_end_dt_tab(12),
period13_end_date = l_end_dt_tab(13),
period14_end_date = l_end_dt_tab(14),
period15_end_date = l_end_dt_tab(15),
period16_end_date = l_end_dt_tab(16),
period17_end_date = l_end_dt_tab(17),
period18_end_date = l_end_dt_tab(18),
period19_end_date = l_end_dt_tab(19),
period20_end_date = l_end_dt_tab(20),
period21_end_date = l_end_dt_tab(21),
period22_end_date = l_end_dt_tab(22),
period23_end_date = l_end_dt_tab(23),
period24_end_date = l_end_dt_tab(24),
period25_end_date = l_end_dt_tab(25),
period26_end_date = l_end_dt_tab(26),
period27_end_date = l_end_dt_tab(27),
period28_end_date = l_end_dt_tab(28),
period29_end_date = l_end_dt_tab(29),
period30_end_date = l_end_dt_tab(30),
period31_end_date = l_end_dt_tab(31),
period32_end_date = l_end_dt_tab(32),
period33_end_date = l_end_dt_tab(33),
period34_end_date = l_end_dt_tab(34),
period35_end_date = l_end_dt_tab(35),
period36_end_date = l_end_dt_tab(36),
period37_end_date = l_end_dt_tab(37),
period38_end_date = l_end_dt_tab(38),
period39_end_date = l_end_dt_tab(39),
period40_end_date = l_end_dt_tab(40),
period41_end_date = l_end_dt_tab(41),
period42_end_date = l_end_dt_tab(42),
period43_end_date = l_end_dt_tab(43),
period44_end_date = l_end_dt_tab(44),
period45_end_date = l_end_dt_tab(45),
period46_end_date = l_end_dt_tab(46),
period47_end_date = l_end_dt_tab(47),
period48_end_date = l_end_dt_tab(48),
period49_end_date = l_end_dt_tab(49),
period50_end_date = l_end_dt_tab(50),
period51_end_date = l_end_dt_tab(51),
period52_end_date = l_end_dt_tab(52),
LAST_UPDATE_LOGIN = l_last_update_login,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_DATE = l_last_update_date,
PROFILE_END_PERIOD_NAME = l_plan_end_period
WHERE period_profile_id = px_period_profile_id;
PA_DEBUG.g_err_stage := 'no record updated for the gievn id';
INSERT INTO pa_proj_period_profiles (
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
period_profile_id ,
period_profile_type ,
period_set_name ,
gl_period_type ,
plan_period_type,
project_id ,
period_name1 ,
period_name2 ,
period_name3 ,
period_name4 ,
period_name5 ,
period_name6 ,
period_name7 ,
period_name8 ,
period_name9 ,
period_name10 ,
period_name11 ,
period_name12 ,
period_name13 ,
period_name14 ,
period_name15 ,
period_name16 ,
period_name17 ,
period_name18 ,
period_name19 ,
period_name20 ,
period_name21 ,
period_name22 ,
period_name23 ,
period_name24 ,
period_name25 ,
period_name26 ,
period_name27 ,
period_name28 ,
period_name29 ,
period_name30 ,
period_name31 ,
period_name32 ,
period_name33 ,
period_name34 ,
period_name35 ,
period_name36 ,
period_name37 ,
period_name38 ,
period_name39 ,
period_name40 ,
period_name41 ,
period_name42 ,
period_name43 ,
period_name44 ,
period_name45 ,
period_name46 ,
period_name47 ,
period_name48 ,
period_name49 ,
period_name50 ,
period_name51 ,
period_name52 ,
period1_start_date ,
period2_start_date ,
period3_start_date ,
period4_start_date ,
period5_start_date ,
period6_start_date ,
period7_start_date ,
period8_start_date ,
period9_start_date ,
period10_start_date ,
period11_start_date ,
period12_start_date ,
period13_start_date ,
period14_start_date ,
period15_start_date ,
period16_start_date ,
period17_start_date ,
period18_start_date ,
period19_start_date ,
period20_start_date ,
period21_start_date ,
period22_start_date ,
period23_start_date ,
period24_start_date ,
period25_start_date ,
period26_start_date ,
period27_start_date ,
period28_start_date ,
period29_start_date ,
period30_start_date ,
period31_start_date ,
period32_start_date ,
period33_start_date ,
period34_start_date ,
period35_start_date ,
period36_start_date ,
period37_start_date ,
period38_start_date ,
period39_start_date ,
period40_start_date ,
period41_start_date ,
period42_start_date ,
period43_start_date ,
period44_start_date ,
period45_start_date ,
period46_start_date ,
period47_start_date ,
period48_start_date ,
period49_start_date ,
period50_start_date ,
period51_start_date ,
period52_start_date ,
period1_end_date ,
period2_end_date ,
period3_end_date ,
period4_end_date ,
period5_end_date ,
period6_end_date ,
period7_end_date ,
period8_end_date ,
period9_end_date ,
period10_end_date ,
period11_end_date ,
period12_end_date ,
period13_end_date ,
period14_end_date ,
period15_end_date ,
period16_end_date ,
period17_end_date ,
period18_end_date ,
period19_end_date ,
period20_end_date ,
period21_end_date ,
period22_end_date ,
period23_end_date ,
period24_end_date ,
period25_end_date ,
period26_end_date ,
period27_end_date ,
period28_end_date ,
period29_end_date ,
period30_end_date ,
period31_end_date ,
period32_end_date ,
period33_end_date ,
period34_end_date ,
period35_end_date ,
period36_end_date ,
period37_end_date ,
period38_end_date ,
period39_end_date ,
period40_end_date ,
period41_end_date ,
period42_end_date ,
period43_end_date ,
period44_end_date ,
period45_end_date ,
period46_end_date ,
period47_end_date ,
period48_end_date ,
period49_end_date ,
period50_end_date ,
period51_end_date ,
period52_end_date ,
number_of_periods,
PROFILE_END_PERIOD_NAME )
VALUES (
l_creation_date ,
l_created_by ,
l_last_update_login ,
l_last_updated_by ,
l_last_update_date ,
PA_PROJ_PERIOD_PROFILES_S.nextval,
p_period_profile_type,
p_period_set_name,
p_gl_period_type,
p_plan_period_type,
p_project_id,
l_period_name_tab(1),
l_period_name_tab(2),
l_period_name_tab(3),
l_period_name_tab(4),
l_period_name_tab(5),
l_period_name_tab(6),
l_period_name_tab(7),
l_period_name_tab(8),
l_period_name_tab(9),
l_period_name_tab(10),
l_period_name_tab(11),
l_period_name_tab(12),
l_period_name_tab(13),
l_period_name_tab(14),
l_period_name_tab(15),
l_period_name_tab(16),
l_period_name_tab(17),
l_period_name_tab(18),
l_period_name_tab(19),
l_period_name_tab(20),
l_period_name_tab(21),
l_period_name_tab(22),
l_period_name_tab(23),
l_period_name_tab(24),
l_period_name_tab(25),
l_period_name_tab(26),
l_period_name_tab(27),
l_period_name_tab(28),
l_period_name_tab(29),
l_period_name_tab(30),
l_period_name_tab(31),
l_period_name_tab(32),
l_period_name_tab(33),
l_period_name_tab(34),
l_period_name_tab(35),
l_period_name_tab(36),
l_period_name_tab(37),
l_period_name_tab(38),
l_period_name_tab(39),
l_period_name_tab(40),
l_period_name_tab(41),
l_period_name_tab(42),
l_period_name_tab(43),
l_period_name_tab(44),
l_period_name_tab(45),
l_period_name_tab(46),
l_period_name_tab(47),
l_period_name_tab(48),
l_period_name_tab(49),
l_period_name_tab(50),
l_period_name_tab(51),
l_period_name_tab(52),
l_st_dt_tab(1),
l_st_dt_tab(2),
l_st_dt_tab(3),
l_st_dt_tab(4),
l_st_dt_tab(5),
l_st_dt_tab(6),
l_st_dt_tab(7),
l_st_dt_tab(8),
l_st_dt_tab(9),
l_st_dt_tab(10),
l_st_dt_tab(11),
l_st_dt_tab(12),
l_st_dt_tab(13),
l_st_dt_tab(14),
l_st_dt_tab(15),
l_st_dt_tab(16),
l_st_dt_tab(17),
l_st_dt_tab(18),
l_st_dt_tab(19),
l_st_dt_tab(20),
l_st_dt_tab(21),
l_st_dt_tab(22),
l_st_dt_tab(23),
l_st_dt_tab(24),
l_st_dt_tab(25),
l_st_dt_tab(26),
l_st_dt_tab(27),
l_st_dt_tab(28),
l_st_dt_tab(29),
l_st_dt_tab(30),
l_st_dt_tab(31),
l_st_dt_tab(32),
l_st_dt_tab(33),
l_st_dt_tab(34),
l_st_dt_tab(35),
l_st_dt_tab(36),
l_st_dt_tab(37),
l_st_dt_tab(38),
l_st_dt_tab(39),
l_st_dt_tab(40),
l_st_dt_tab(41),
l_st_dt_tab(42),
l_st_dt_tab(43),
l_st_dt_tab(44),
l_st_dt_tab(45),
l_st_dt_tab(46),
l_st_dt_tab(47),
l_st_dt_tab(48),
l_st_dt_tab(49),
l_st_dt_tab(50),
l_st_dt_tab(51),
l_st_dt_tab(52),
l_end_dt_tab(1),
l_end_dt_tab(2),
l_end_dt_tab(3),
l_end_dt_tab(4),
l_end_dt_tab(5),
l_end_dt_tab(6),
l_end_dt_tab(7),
l_end_dt_tab(8),
l_end_dt_tab(9),
l_end_dt_tab(10),
l_end_dt_tab(11),
l_end_dt_tab(12),
l_end_dt_tab(13),
l_end_dt_tab(14),
l_end_dt_tab(15),
l_end_dt_tab(16),
l_end_dt_tab(17),
l_end_dt_tab(18),
l_end_dt_tab(19),
l_end_dt_tab(20),
l_end_dt_tab(21),
l_end_dt_tab(22),
l_end_dt_tab(23),
l_end_dt_tab(24),
l_end_dt_tab(25),
l_end_dt_tab(26),
l_end_dt_tab(27),
l_end_dt_tab(28),
l_end_dt_tab(29),
l_end_dt_tab(30),
l_end_dt_tab(31),
l_end_dt_tab(32),
l_end_dt_tab(33),
l_end_dt_tab(34),
l_end_dt_tab(35),
l_end_dt_tab(36),
l_end_dt_tab(37),
l_end_dt_tab(38),
l_end_dt_tab(39),
l_end_dt_tab(40),
l_end_dt_tab(41),
l_end_dt_tab(42),
l_end_dt_tab(43),
l_end_dt_tab(44),
l_end_dt_tab(45),
l_end_dt_tab(46),
l_end_dt_tab(47),
l_end_dt_tab(48),
l_end_dt_tab(49),
l_end_dt_tab(50),
l_end_dt_tab(51),
l_end_dt_tab(52),
l_tab_count ,
l_plan_end_period ) returning period_profile_id into
px_period_profile_id;
PA_DEBUG.g_err_stage := 'after inserting pd profile';
UPDATE PA_PROJ_PERIOD_PROFILES SET CURRENT_FLAG = 'N' WHERE
Period_Profile_Type = p_period_profile_type AND
Plan_Period_Type = p_plan_period_type AND
Project_Id = p_project_id AND
Current_Flag = 'Y';
UPDATE PA_PROJ_PERIOD_PROFILES SET CURRENT_FLAG = 'Y' WHERE
Period_Profile_Id = px_period_profile_id;
SELECT Period_Profile_Type,
Plan_Period_Type,
Period_Set_Name,
Gl_Period_Type,
Number_Of_Periods,
Period1_Start_Date INTO
x_period_profile_type,
x_plan_period_type,
x_period_set_name,
x_gl_period_type,
x_number_of_periods,
x_plan_start_date FROM Pa_Proj_Period_Profiles
WHERE Period_Profile_Id = NVL(p_period_profile_id,0);
l_stmt := 'select ' ||l_column_name
|| ' from pa_proj_period_profiles where ' ||
' period_profile_id = '||to_char(p_period_profile_id);
SELECT TO_CHAR(gl.start_date,'rrrr/mm/dd'),
TO_CHAR(gl.end_date,'rrrr/mm/dd') INTO l_start_date,l_end_Date FROM
Gl_Periods gl, Pa_Implementations_All imp ,Pa_Projects_All p ,
Gl_Sets_Of_Books sob WHERE
p.Project_Id = p_project_id AND
nvl(p.Org_Id,-99) = NVL(imp.Org_Id,-99) AND
imp.Set_Of_Books_Id = sob.Set_Of_Books_Id AND
gl.Period_Set_Name = imp.Period_Set_Name AND
gl.Period_Type = sob.Accounted_Period_Type AND
gl.Period_Name = p_period_name AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT TO_CHAR(gl.start_date,'rrrr/mm/dd'),
TO_CHAR(gl.end_date,'rrrr/mm/dd') INTO l_start_date,l_end_Date FROM
Gl_Periods gl, Pa_Implementations_All imp ,Pa_Projects_All p WHERE
p.Project_Id = p_project_id AND
nvl(p.Org_Id,-99) = nvl(imp.Org_Id,-99) AND
gl.Period_Set_Name = imp.Period_Set_Name AND
gl.Period_Type = imp.Pa_Period_Type AND
gl.Period_Name = p_period_name AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT imp.Pa_Period_Type,
imp.Period_Set_Name,
sob.Accounted_Period_Type INTO
l_pa_period_type,
l_gl_period_set_name,
l_gl_period_type FROM
Pa_Implementations_All imp ,Pa_Projects_All p ,
Gl_Sets_Of_Books sob WHERE
p.Project_Id = p_project_id AND
nvl(p.Org_Id,-99) = NVL(imp.Org_Id,-99) AND
imp.Set_Of_Books_id = sob.Set_Of_Books_Id;
SELECT gl.Start_Date,gl.End_Date INTO l_pa_start_date,l_pa_end_Date FROM
Gl_Periods gl WHERE
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_pa_period_type and
gl.Period_Name = p_pa_start_period AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT gl.Start_Date,gl.End_Date INTO l_pa_start_date1,l_pa_end_Date1 FROM
Gl_Periods gl WHERE
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_pa_period_type AND
gl.Period_Name = p_pa_end_period AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT gl.Start_Date,gl.End_Date INTO l_gl_start_date,l_gl_end_Date FROM
Gl_Periods gl WHERE
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_gl_period_type AND
gl.Period_Name = p_gl_start_period AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT gl.Start_Date,gl.End_Date INTO l_gl_start_date1,l_gl_end_Date1 FROM
Gl_Periods gl WHERE
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_gl_period_type AND
gl.Period_Name = p_gl_end_period AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT COUNT(*) INTO l_periods_count FROM Gl_Periods gl
WHERE
Start_Date BETWEEN l_pa_start_date AND l_pa_start_date1 AND
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_pa_period_type AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT COUNT(*) INTO l_periods_count FROM Gl_Periods gl
WHERE
Start_Date BETWEEN l_gl_start_date AND l_gl_start_date1 AND
gl.Period_Set_Name = l_gl_period_set_name AND
gl.Period_Type = l_gl_period_type AND
gl.Adjustment_Period_Flag = 'N' ;
SELECT start_Date
, completion_date
INTO l_prj_start_date
, l_prj_completion_date
FROM Pa_Projects_All
WHERE Project_Id = p_project_id;
SELECT Start_Date
INTO l_prj_PAper_start_date
FROM Gl_Periods
WHERE Period_Set_Name = l_gl_period_set_name
AND Period_Type = l_PA_period_type
AND Adjustment_Period_Flag = 'N'
and l_prj_start_date between start_date and end_date;
SELECT End_Date
INTO l_prj_PAper_end_date
FROM Gl_Periods
WHERE Period_Set_Name = l_gl_period_set_name
AND Period_Type = l_PA_period_type
AND Adjustment_Period_Flag = 'N'
and l_prj_completion_date between start_date and end_date;
SELECT Start_Date
INTO l_prj_GLper_start_date
FROM Gl_Periods
WHERE Period_Set_Name = l_gl_period_set_name
AND Period_Type = l_GL_period_type
AND Adjustment_Period_Flag = 'N'
and l_prj_start_date between start_date and end_date;
SELECT End_Date
INTO l_prj_GLper_end_date
FROM Gl_Periods
WHERE Period_Set_Name = l_gl_period_set_name
AND Period_Type = l_GL_period_type
AND Adjustment_Period_Flag = 'N'
and l_prj_completion_date between start_date and end_date;
SELECT PERIOD_NAME1 , PROFILE_END_PERIOD_NAME INTO
l_old_pa_start_period,l_old_pa_end_period FROM
Pa_Proj_Period_Profiles WHERE
Period_Profile_Id = p_old_pa_profile_id;
SELECT PERIOD_NAME1 , PROFILE_END_PERIOD_NAME INTO
l_old_gl_start_period,l_old_gl_end_period FROM
Pa_Proj_Period_Profiles WHERE
Period_Profile_Id = p_old_gl_profile_id;
l_bv_id_tab.DELETE;
l_locked_person_id_tab.DELETE;
l_plan_proc_code_tab.DELETE;
SELECT budget_version_id,
locked_by_person_id,
plan_processing_Code
BULK COLLECT INTO
l_bv_id_tab,
l_locked_person_id_tab,
l_plan_proc_code_tab
FROM
Pa_budget_versions
WHERE
project_id = p_project_id
AND period_profile_id IS NOT NULL;
ELSIF (p_refresh_option_code = 'SELECTED') THEN
SELECT budget_version_id,
locked_by_person_id,
plan_processing_Code
BULK COLLECT INTO
l_bv_id_tab,
l_locked_person_id_tab,
l_plan_proc_code_tab
FROM
Pa_budget_versions
WHERE
project_id = p_project_id
AND period_profile_id IS NOT NULL
AND
(
(current_working_flag = 'Y' AND budget_status_code IN ('W','S'))
OR (current_flag = 'Y' AND budget_status_code = 'B')
OR (current_original_flag = 'Y' AND budget_status_code = 'B')
);
UPDATE
END IF;
UPDATE pa_budget_versions SET
plan_processing_code = 'PPP',
locked_by_person_id = -98,
request_id = l_rpt_request_id,
record_version_number = nvl(record_version_number,0) + 1,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate
WHERE
budget_version_id = l_bv_id_tab(ii) AND
locked_by_person_id IS NULL;
SELECT Period_Name,Start_Date,
End_Date FROM Gl_Periods WHERE
Period_Set_Name = c_period_set_name AND
Period_Type = c_period_type AND Start_Date > c_start_date AND
Adjustment_Period_Flag = 'N' AND ROWNUM < 52
ORDER BY Start_Date;
SELECT Period_Name,Start_Date, End_Date
FROM
(
SELECT Period_Name,Start_Date, End_Date
FROM Gl_Periods
WHERE Period_Set_Name = c_period_set_name AND
Period_Type = c_period_type AND Start_Date > c_start_date AND
Adjustment_Period_Flag = 'N'
ORDER BY Start_Date
)
WHERE ROWNUM < 52;
SELECT COUNT(*) INTO l_count FROM Pa_Budget_Versions
WHERE Project_Id = p_project_id AND
Period_Profile_Id IS NOT NULL AND
Period_Profile_Id > 0;
SELECT COUNT(*) INTO l_profile_count
FROM pa_proj_period_profiles
WHERE
project_id = p_project_id AND
period_profile_type = 'FINANCIAL_PLANNING' AND
plan_period_type = 'PA';
SELECT COUNT(*) INTO l_profile_count
FROM pa_proj_period_profiles
WHERE
project_id = p_project_id AND
period_profile_type = 'FINANCIAL_PLANNING' AND
plan_period_type = 'GL';
SELECT start_Date, completion_date, nvl(org_id,-99)
INTO
l_prj_start_date,l_prj_completion_date,l_prj_org_id FROM
Pa_Projects_All WHERE
Project_Id = p_project_id;
/* Bug 3354518- FP.M -dbora- Modified the sql statement associated with the start_date select
*/
SELECT nvl(start_Date, trunc(sysdate))
, completion_date
, nvl(org_id,-99)
, decode (start_date, NULL, NULL, TO_CHAR(start_date,'rrrr/mm/dd') )
, decode (completion_date, NULL, NULL, TO_CHAR(completion_date,'rrrr/mm/dd') )
INTO l_prj_start_date
,l_prj_completion_date
,l_prj_org_id
,x_prj_start_date
,x_prj_end_date
FROM Pa_Projects_All
WHERE Project_Id = p_project_id;
END; -- select start_date
SELECT imp.Period_Set_Name,imp.Pa_Period_Type,sob.Accounted_Period_Type
INTO l_period_set_name , l_pa_period_type,l_gl_period_type
FROM Pa_Implementations_All imp, Gl_Sets_Of_Books sob WHERE
--NVL(imp.Org_Id,-99) = l_prj_org_id AND
imp.Org_Id = l_prj_org_id AND -- Bug Ref # 6327662
imp.Set_Of_Books_Id = sob.Set_Of_Books_Id;
SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
glp.end_date
INTO x_gl_start_period,x_gl_start_date,
l_pd_gl_end_date
FROM
Gl_Date_Period_Map gl,
gl_periods glp WHERE
gl.Period_Set_Name = l_period_set_name AND
gl.Period_Type = l_gl_period_type AND
gl.Accounting_Date = l_prj_start_date AND
glp.period_set_name = gl.Period_Set_Name AND
glp.Period_Type = gl.Period_Type AND
glp.adjustment_period_flag = 'N' AND
glp.period_name = gl.period_name;
SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
glp.end_date
INTO x_pa_start_period,x_pa_start_date,
l_pd_pa_end_date
FROM
Gl_Date_Period_Map gl,
gl_periods glp WHERE
gl.Period_Set_Name = l_period_set_name AND
gl.Period_Type = l_pa_period_type AND
gl.Accounting_Date = l_prj_start_date AND
glp.period_set_name = gl.Period_Set_Name AND
glp.Period_Type = gl.Period_Type AND
glp.adjustment_period_flag = 'N' AND
glp.period_name = gl.period_name;
/* The default period profile info just derived needs to created (inserted) for the project
only when p_create_defaults */
IF p_create_defaults = 'Y' THEN
IF x_pa_start_date IS NOT NULL AND
l_create_pa_profile = 'Y' THEN
IF p_pa_debug_mode = 'Y' THEN
pa_debug.g_err_stage := 'Calling Maintain_Prj_Period_Profile to create the PA period profile ....';
SELECT period_profile_id
,period_name1
,profile_end_period_name
INTO l_period_profile_id
,l_start_period
,l_end_period
FROM pa_proj_period_profiles
WHERE project_id = p_project_id
AND current_flag = 'Y'
AND period_profile_type = p_period_profile_type
AND plan_period_type = p_period_type;
SELECT atb.amount_type_id
,atb.amount_type_code
FROM pa_amount_types_b atb
WHERE atb.amount_type_class = 'R';
UPDATE pa_budget_versions bv
SET bv.period_profile_id = p_period_profile_id,
record_version_number = nvl(record_version_number,0) + 1,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate
WHERE bv.budget_version_id = l_budget_version_id
AND bv.project_id = p_project_id;
UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
p_return_status => x_return_status,
p_project_id => p_project_id,
p_request_id => l_request_id );
UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
p_return_status => x_return_status,
p_project_id => p_project_id,
p_request_id => l_request_id );
UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
p_return_status => x_return_status,
p_project_id => NULL,
p_request_id => l_request_id );
UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
p_return_status => x_return_status,
p_project_id => p_project_id,
p_request_id => l_request_id );
t_budget_version_id.DELETE;
l_req_id_tab.DELETE;
l_locked_person_id_tab.DELETE;
l_plan_proc_code_tab.DELETE;
select
nvl(locked_by_person_id,0),
nvl(plan_processing_code,'DUMMY'),
nvl(request_id,0) into
l_locked_person_id_tab(l_count),
l_plan_proc_code_tab(l_count),
l_req_id_tab(l_count)
from pa_budget_versions where
budget_version_id = l_budget_version_id1;
select
nvl(locked_by_person_id,0),
NVL(plan_processing_code,'DUMMY'),
nvl(request_id,0) into
l_locked_person_id_tab(l_count),
l_plan_proc_code_tab(l_count),
l_req_id_tab(l_count)
from pa_budget_versions where
budget_version_id = l_budget_version_id2;
SELECT budget_version_id,
nvl(locked_by_person_id,0),
NVL(plan_processing_code,'DUMMY'),
nvl(request_id,0)
BULK COLLECT INTO
t_budget_version_id,
l_locked_person_id_tab,
l_plan_proc_code_tab,
l_req_id_tab
FROM
Pa_budget_versions
WHERE
project_id = l_project_id
AND period_profile_id IS NOT NULL;
ELSIF (l_refresh_option_code = 'SELECTED') THEN
SELECT budget_version_id,
nvl(locked_by_person_id,0),
nvl(plan_processing_code,'DUMMY'),
nvl(request_id,0)
BULK COLLECT INTO
t_budget_version_id,
l_locked_person_id_tab,
l_plan_proc_code_tab,
l_req_id_tab
FROM
Pa_budget_versions
WHERE
project_id = l_project_id
AND period_profile_id IS NOT NULL
AND
(
(current_working_flag = 'Y' AND budget_status_code IN ('W','S'))
OR (current_flag = 'Y' AND budget_status_code = 'B')
OR (current_original_flag = 'Y' AND budget_status_code = 'B')
);
SELECT
DECODE
(po.fin_plan_preference_code,
'COST_ONLY',po.cost_time_phased_code,
'REVENUE_ONLY',po.revenue_time_phased_code,
'COST_AND_REV_SAME',po.all_time_phased_code,
DECODE
(bv.version_type,
'COST',po.cost_time_phased_code,
'REVENUE',po.revenue_time_phased_code
)
)
INTO
l_time_phased_code
FROM pa_budget_versions bv, pa_proj_fp_options po
WHERE
bv.budget_version_id = l_budget_version_id
AND po.fin_plan_version_id = bv.budget_version_id
AND po.fin_plan_option_level_code = 'PLAN_VERSION'
AND bv.project_id = p_project_id
AND po.project_id = bv.project_id;
UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
p_return_status => l_return_status,
p_project_id => p_project_id,
p_request_id => l_request_id );
select period_name1, period1_start_date, period1_end_date,
period_name2, period2_start_date, period2_end_date,
period_name3, period3_start_date, period3_end_date,
period_name4, period4_start_date, period4_end_date,
period_name5, period5_start_date, period5_end_date,
period_name6, period6_start_date, period6_end_date,
period_name7, period7_start_date, period7_end_date,
period_name8, period8_start_date, period8_end_date,
period_name9, period9_start_date, period9_end_date,
period_name10, period10_start_date, period10_end_date,
period_name11, period11_start_date, period11_end_date,
period_name12, period12_start_date, period12_end_date,
period_name13, period13_start_date, period13_end_date,
period_name14, period14_start_date, period14_end_date,
period_name15, period15_start_date, period15_end_date,
period_name16, period16_start_date, period16_end_date,
period_name17, period17_start_date, period17_end_date,
period_name18, period18_start_date, period18_end_date,
period_name19, period19_start_date, period19_end_date,
period_name20, period20_start_date, period20_end_date,
period_name21, period21_start_date, period21_end_date,
period_name22, period22_start_date, period22_end_date,
period_name23, period23_start_date, period23_end_date,
period_name24, period24_start_date, period24_end_date,
period_name25, period25_start_date, period25_end_date,
period_name26, period26_start_date, period26_end_date,
period_name27, period27_start_date, period27_end_date,
period_name28, period28_start_date, period28_end_date,
period_name29, period29_start_date, period29_end_date,
period_name30, period30_start_date, period30_end_date,
period_name31, period31_start_date, period31_end_date,
period_name32, period32_start_date, period32_end_date,
period_name33, period33_start_date, period33_end_date,
period_name34, period34_start_date, period34_end_date,
period_name35, period35_start_date, period35_end_date,
period_name36, period36_start_date, period36_end_date,
period_name37, period37_start_date, period37_end_date,
period_name38, period38_start_date, period38_end_date,
period_name39, period39_start_date, period39_end_date,
period_name40, period40_start_date, period40_end_date,
period_name41, period41_start_date, period41_end_date,
period_name42, period42_start_date, period42_end_date,
period_name43, period43_start_date, period43_end_date,
period_name44, period44_start_date, period44_end_date,
period_name45, period45_start_date, period45_end_date,
period_name46, period46_start_date, period46_end_date,
period_name47, period47_start_date, period47_end_date,
period_name48, period48_start_date, period48_end_date,
period_name49, period49_start_date, period49_end_date,
period_name50, period50_start_date, period50_end_date,
period_name51, period51_start_date, period51_end_date,
period_name52, period52_start_date, period52_end_date
from pa_proj_period_profiles
where period_profile_id = p_period_profile_id;
select sysdate
into l_current_date
from dual;
select unique 'Y'
into l_return_value
from pa_budget_lines
where budget_version_id = p_budget_version_id and
bucketing_period_code = 'PD';
select unique 'Y'
into l_return_value
from pa_budget_lines
where budget_version_id = p_budget_version_id and
bucketing_period_code = 'SD';
PROCEDURE UPDATE_BUDGET_VERSION(p_budget_version_id IN NUMBER,
p_return_status IN VARCHAR2,
p_project_id IN NUMBER,
p_request_id IN NUMBER ) IS
l_plan_proc_code pa_budget_versions.plan_processing_Code%type;
UPDATE PA_BUDGET_VERSIONS
SET PLAN_PROCESSING_CODE = l_plan_proc_code,
locked_by_person_id = NULL,
record_version_number = nvl(record_version_number,0) + 1,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate
WHERE
project_id = p_project_id and
request_id = p_request_id and
plan_processing_code = 'PPP';
UPDATE PA_BUDGET_VERSIONS
SET PLAN_PROCESSING_CODE = l_plan_proc_code,
locked_by_person_id = NULL,
record_version_number = nvl(record_version_number,0) + 1,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate
WHERE
budget_version_id = p_budget_version_id;