[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM | 30-JUN-03 rchandra Selected DATASET_ID from bisfv_target_levels to |
REM | populate BIS_Target_Level_PUB.Target_Level_Rec_Type|
REM | for bug 3004651 |
REM | 21-OCT-04 arhegde bug# 3634587 The SQL used shows up on performance |
REM | repository top-20, Removed Retrieve_Measure_Notify_Resps() |
REM | 05-jul-04 rpenneru Modified for bug#3735203 |
REM | 21-Mar-05 ankagarw bug#4235732 - changing count(*) to count(1) |
REM | 23-Jan-06 ankgoel bug#4946492 - do not update creation date on UPDATE|
REM +=======================================================================+
*/
--
G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_TARGET_LEVEL_PVT';
select target_level_id
from bis_target_levels
where short_name like p_tl_rec.target_level_short_name;
select target_level_id
from bis_target_levels
where NVL(INDICATOR_ID, -1) = NVL(p_tl_curs_rec.Measure_Id, -1)
AND (p_tl_curs_rec.Org_Level_Id IS NULL OR NVL(ORG_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Org_Level_Id, -1))
AND (p_tl_curs_rec.Time_Level_Id IS NULL OR NVL(TIME_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Time_Level_Id, -1))
AND NVL(DIMENSION1_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension1_Level_Id, -1)
AND NVL(DIMENSION2_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension2_Level_Id, -1)
AND NVL(DIMENSION3_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension3_Level_Id, -1)
AND NVL(DIMENSION4_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension4_Level_Id, -1)
AND NVL(DIMENSION5_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension5_Level_Id, -1)
AND NVL(DIMENSION6_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension6_Level_Id, -1)
AND NVL(DIMENSION7_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension7_Level_Id, -1);
l_msg := l_msg || ' could not be created/updated.';
select bis_target_levels_s.NEXTVAL into l_id from dual;
insert into bis_TARGET_LEVELS (
TARGET_LEVEL_ID,
INDICATOR_ID,
SHORT_NAME,
ORG_LEVEL_ID,
TIME_LEVEL_ID,
DIMENSION1_LEVEL_ID,
DIMENSION2_LEVEL_ID,
DIMENSION3_LEVEL_ID,
DIMENSION4_LEVEL_ID,
DIMENSION5_LEVEL_ID,
DIMENSION6_LEVEL_ID,
DIMENSION7_LEVEL_ID,
WF_PROCESS,
WF_ITEM_TYPE,
REPORT_FUNCTION_ID,
DEFAULT_COMPUTING_FUNCTION_ID,
-- UNIT_OF_MEASURE,
DEFAULT_ROLE_ID,
DEFAULT_ROLE,
SYSTEM_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SOURCE
)
values
( l_id
, l_Target_Level_Rec.Measure_ID
, l_Target_Level_Rec.Target_Level_Short_Name
, l_Target_Level_Rec.Org_Level_ID
, l_Target_Level_Rec.Time_Level_ID
, l_Target_Level_Rec.Dimension1_Level_ID
, l_Target_Level_Rec.Dimension2_Level_ID
, l_Target_Level_Rec.Dimension3_Level_ID
, l_Target_Level_Rec.Dimension4_Level_ID
, l_Target_Level_Rec.Dimension5_Level_ID
, l_Target_Level_Rec.Dimension6_Level_ID
, l_Target_Level_Rec.Dimension7_Level_ID
, l_Target_Level_Rec.Workflow_Process_Short_Name
, l_Target_Level_Rec.Workflow_Item_Type
, l_Target_Level_Rec.Report_Function_ID
, l_target_level_rec.Computing_function_ID
-- , l_Target_Level_Rec.Unit_Of_Measure
, l_Target_Level_Rec.Default_Notify_Resp_ID
, l_Target_Level_Rec.Default_Notify_Resp_short_name
, l_Target_Level_Rec.system_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, l_target_level_rec.Source
);
insert into bis_TARGET_LEVELS_TL (
TARGET_LEVEL_ID,
LANGUAGE,
NAME,
DESCRIPTION,
UNIT_OF_MEASURE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANSLATED,
SOURCE_LANG
) select
l_id
, L.LANGUAGE_CODE
, l_Target_Level_Rec.Target_Level_Name
, l_Target_Level_Rec.Description
, l_Target_Level_Rec.Unit_Of_Measure
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, 'Y'
, userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from bis_TARGET_LEVELS_TL T
where T.TARGET_LEVEL_ID = l_id
and T.LANGUAGE = L.LANGUAGE_CODE);
select count(1) into x_count
from bis_target_levels
where indicator_id = p_Measure_Rec.Measure_id;
Select measure_id
, TARGET_LEVEL_ID
, TARGET_LEVEL_SHORT_NAME
, TARGET_LEVEL_NAME
, DESCRIPTION
, ORG_LEVEL_ID
, TIME_LEVEL_ID
, DIMENSION1_LEVEL_ID
, DIMENSION2_LEVEL_ID
, DIMENSION3_LEVEL_ID
, DIMENSION4_LEVEL_ID
, DIMENSION5_LEVEL_ID
, DIMENSION6_LEVEL_ID
, DIMENSION7_LEVEL_ID
, WORKFLOW_ITEM_TYPE
, WORKFLOW_PROCESS_SHORT_NAME
, DEFAULT_NOTIFY_RESP_ID
, DEFAULT_NOTIFY_RESP_SHORT_NAME
, COMPUTING_FUNCTION_ID
, REPORT_FUNCTION_ID
, UNIT_OF_MEASURE
, SYSTEM_FLAG
from bisbv_target_levels
where measure_id = l_Measure_id;
select TARGET_LEVEL_ID
, TARGET_LEVEL_SHORT_NAME
, TARGET_LEVEL_NAME
, DESCRIPTION
, MEASURE_ID
, MEASURE_SHORT_NAME
, MEASURE_NAME
, ORG_LEVEL_ID
, ORG_LEVEL_SHORT_NAME
, ORG_LEVEL_NAME
, TIME_LEVEL_ID
, TIME_LEVEL_SHORT_NAME
, TIME_LEVEL_NAME
, DIMENSION1_LEVEL_ID
, DIMENSION1_LEVEL_SHORT_NAME
, DIMENSION1_LEVEL_NAME
, DIMENSION2_LEVEL_ID
, DIMENSION2_LEVEL_SHORT_NAME
, DIMENSION2_LEVEL_NAME
, DIMENSION3_LEVEL_ID
, DIMENSION3_LEVEL_SHORT_NAME
, DIMENSION3_LEVEL_NAME
, DIMENSION4_LEVEL_ID
, DIMENSION4_LEVEL_SHORT_NAME
, DIMENSION4_LEVEL_NAME
, DIMENSION5_LEVEL_ID
, DIMENSION5_LEVEL_SHORT_NAME
, DIMENSION5_LEVEL_NAME
, DIMENSION6_LEVEL_ID
, DIMENSION6_LEVEL_SHORT_NAME
, DIMENSION6_LEVEL_NAME
, DIMENSION7_LEVEL_ID
, DIMENSION7_LEVEL_SHORT_NAME
, DIMENSION7_LEVEL_NAME
, WORKFLOW_ITEM_TYPE
, WORKFLOW_PROCESS_SHORT_NAME
, WORKFLOW_PROCESS_NAME
, DEFAULT_NOTIFY_RESP_ID
, DEFAULT_NOTIFY_RESP_SHORT_NAME
, DEFAULT_NOTIFY_RESP_NAME
, COMPUTING_FUNCTION_ID
, COMPUTING_FUNCTION_NAME
, COMPUTING_USER_FUNCTION_NAME
, REPORT_FUNCTION_ID
, REPORT_FUNCTION_NAME
, REPORT_USER_FUNCTION_NAME
, UNIT_OF_MEASURE
, SYSTEM_FLAG
, DATASET_ID
from bisfv_target_levels
where measure_id = l_Measure_id;
Select measure_id
, TARGET_LEVEL_ID
, TARGET_LEVEL_SHORT_NAME
, TARGET_LEVEL_NAME
, DESCRIPTION
, ORG_LEVEL_ID
, TIME_LEVEL_ID
, DIMENSION1_LEVEL_ID
, DIMENSION2_LEVEL_ID
, DIMENSION3_LEVEL_ID
, DIMENSION4_LEVEL_ID
, DIMENSION5_LEVEL_ID
, DIMENSION6_LEVEL_ID
, DIMENSION7_LEVEL_ID
, WORKFLOW_ITEM_TYPE
, WORKFLOW_PROCESS_SHORT_NAME
, DEFAULT_NOTIFY_RESP_ID
, DEFAULT_NOTIFY_RESP_SHORT_NAME
, COMPUTING_FUNCTION_ID
, REPORT_FUNCTION_ID
, UNIT_OF_MEASURE
, SYSTEM_FLAG
into l_Target_Level_rec.measure_id
, l_Target_Level_rec.Target_Level_id
, l_Target_Level_rec.Target_Level_short_name
, l_Target_Level_rec.Target_Level_name
, l_Target_Level_rec.description
, l_Target_Level_rec.org_level_id
, l_Target_Level_rec.time_level_id
, l_Target_Level_rec.dimension1_level_id
, l_Target_Level_rec.dimension2_level_id
, l_Target_Level_rec.dimension3_level_id
, l_Target_Level_rec.dimension4_level_id
, l_Target_Level_rec.dimension5_level_id
, l_Target_Level_rec.dimension6_level_id
, l_Target_Level_rec.dimension7_level_id
, l_Target_Level_rec.Workflow_Item_Type
, l_Target_Level_rec.Workflow_process_short_name
, l_Target_Level_rec.Default_Notify_Resp_ID
, l_Target_Level_rec.Default_Notify_Resp_short_name
, l_Target_Level_rec.Computing_Function_Id
, l_Target_Level_rec.Report_Function_ID
, l_Target_Level_rec.Unit_Of_Measure
, l_Target_Level_rec.system_flag
from bisbv_target_levels
where target_level_ID = p_Target_Level_rec.Target_Level_ID;
SELECT INDICATOR_ID
, TARGET_LEVEL_ID
, SHORT_NAME
, DIMENSION1_LEVEL_ID
, DIMENSION2_LEVEL_ID
, DIMENSION3_LEVEL_ID
, DIMENSION4_LEVEL_ID
, DIMENSION5_LEVEL_ID
, DIMENSION6_LEVEL_ID
, DIMENSION7_LEVEL_ID
into l_target_level_rec.MEASURE_ID
, l_target_level_rec.TARGET_LEVEL_ID
, l_target_level_rec.TARGET_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION1_LEVEL_ID
, l_target_level_rec.DIMENSION2_LEVEL_ID
, l_target_level_rec.DIMENSION3_LEVEL_ID
, l_target_level_rec.DIMENSION4_LEVEL_ID
, l_target_level_rec.DIMENSION5_LEVEL_ID
, l_target_level_rec.DIMENSION6_LEVEL_ID
, l_target_level_rec.DIMENSION7_LEVEL_ID
FROM BIS_TARGET_LEVELS
WHERE TARGET_LEVEL_ID = p_Target_Level_rec.Target_Level_ID;
select TARGET_LEVEL_ID
, TARGET_LEVEL_SHORT_NAME
, TARGET_LEVEL_NAME
, DESCRIPTION
, MEASURE_ID
, MEASURE_SHORT_NAME
, MEASURE_NAME
, ORG_LEVEL_ID
, ORG_LEVEL_SHORT_NAME
, ORG_LEVEL_NAME
, TIME_LEVEL_ID
, TIME_LEVEL_SHORT_NAME
, TIME_LEVEL_NAME
, DIMENSION1_LEVEL_ID
, DIMENSION1_LEVEL_SHORT_NAME
, DIMENSION1_LEVEL_NAME
, DIMENSION2_LEVEL_ID
, DIMENSION2_LEVEL_SHORT_NAME
, DIMENSION2_LEVEL_NAME
, DIMENSION3_LEVEL_ID
, DIMENSION3_LEVEL_SHORT_NAME
, DIMENSION3_LEVEL_NAME
, DIMENSION4_LEVEL_ID
, DIMENSION4_LEVEL_SHORT_NAME
, DIMENSION4_LEVEL_NAME
, DIMENSION5_LEVEL_ID
, DIMENSION5_LEVEL_SHORT_NAME
, DIMENSION5_LEVEL_NAME
, DIMENSION6_LEVEL_ID
, DIMENSION6_LEVEL_SHORT_NAME
, DIMENSION6_LEVEL_NAME
, DIMENSION7_LEVEL_ID
, DIMENSION7_LEVEL_SHORT_NAME
, DIMENSION7_LEVEL_NAME
, WORKFLOW_ITEM_TYPE
, WORKFLOW_PROCESS_SHORT_NAME
, WORKFLOW_PROCESS_NAME
, DEFAULT_NOTIFY_RESP_ID
, DEFAULT_NOTIFY_RESP_SHORT_NAME
, DEFAULT_NOTIFY_RESP_NAME
, COMPUTING_FUNCTION_ID
, COMPUTING_FUNCTION_NAME
, COMPUTING_USER_FUNCTION_NAME
, REPORT_FUNCTION_ID
, REPORT_FUNCTION_NAME
, REPORT_USER_FUNCTION_NAME
, UNIT_OF_MEASURE
, SYSTEM_FLAG
, DATASET_ID
into l_target_level_rec.TARGET_LEVEL_ID
, l_target_level_rec.TARGET_LEVEL_SHORT_NAME
, l_target_level_rec.TARGET_LEVEL_NAME
, l_target_level_rec.DESCRIPTION
, l_target_level_rec.MEASURE_ID
, l_target_level_rec.MEASURE_SHORT_NAME
, l_target_level_rec.MEASURE_NAME
, l_target_level_rec.ORG_LEVEL_ID
, l_target_level_rec.ORG_LEVEL_SHORT_NAME
, l_target_level_rec.ORG_LEVEL_NAME
, l_target_level_rec.TIME_LEVEL_ID
, l_target_level_rec.TIME_LEVEL_SHORT_NAME
, l_target_level_rec.TIME_LEVEL_NAME
, l_target_level_rec.DIMENSION1_LEVEL_ID
, l_target_level_rec.DIMENSION1_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION1_LEVEL_NAME
, l_target_level_rec.DIMENSION2_LEVEL_ID
, l_target_level_rec.DIMENSION2_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION2_LEVEL_NAME
, l_target_level_rec.DIMENSION3_LEVEL_ID
, l_target_level_rec.DIMENSION3_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION3_LEVEL_NAME
, l_target_level_rec.DIMENSION4_LEVEL_ID
, l_target_level_rec.DIMENSION4_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION4_LEVEL_NAME
, l_target_level_rec.DIMENSION5_LEVEL_ID
, l_target_level_rec.DIMENSION5_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION5_LEVEL_NAME
, l_target_level_rec.DIMENSION6_LEVEL_ID
, l_target_level_rec.DIMENSION6_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION6_LEVEL_NAME
, l_target_level_rec.DIMENSION7_LEVEL_ID
, l_target_level_rec.DIMENSION7_LEVEL_SHORT_NAME
, l_target_level_rec.DIMENSION7_LEVEL_NAME
, l_target_level_rec.WORKFLOW_ITEM_TYPE
, l_target_level_rec.WORKFLOW_PROCESS_SHORT_NAME
, l_target_level_rec.WORKFLOW_PROCESS_NAME
, l_target_level_rec.DEFAULT_NOTIFY_RESP_ID
, l_target_level_rec.DEFAULT_NOTIFY_RESP_SHORT_NAME
, l_target_level_rec.DEFAULT_NOTIFY_RESP_NAME
, l_target_level_rec.COMPUTING_FUNCTION_ID
, l_target_level_rec.COMPUTING_FUNCTION_NAME
, l_target_level_rec.COMPUTING_USER_FUNCTION_NAME
, l_target_level_rec.REPORT_FUNCTION_ID
, l_target_level_rec.REPORT_FUNCTION_NAME
, l_target_level_rec.REPORT_USER_FUNCTION_NAME
, l_target_level_rec.UNIT_OF_MEASURE
, l_target_level_rec.SYSTEM_FLAG
, l_target_level_rec.DATASET_ID
from bisfv_target_levels
where target_level_ID = p_Target_Level_rec.Target_Level_ID;
x_sql := 'SELECT INDICATOR_ID
, TARGET_LEVEL_ID
, SHORT_NAME
, DIMENSION1_LEVEL_ID
, DIMENSION2_LEVEL_ID
, DIMENSION3_LEVEL_ID
, DIMENSION4_LEVEL_ID
, DIMENSION5_LEVEL_ID
, DIMENSION6_LEVEL_ID
, DIMENSION7_LEVEL_ID
FROM BIS_TARGET_LEVELS
WHERE short_name IN (';
PROCEDURE Update_Target_Level_Rec
( p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, p_Target_Level_Rec1 IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, p_up_loaded IN VARCHAR2 := FND_API.G_TRUE
, x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
, x_changed OUT NOCOPY VARCHAR2
)
IS
a BIS_Target_Level_PUB.Target_Level_Rec_Type;
htp.p('Exception in Update_Target_Level_Rec: '||SQLERRM);
END Update_Target_Level_Rec;
PROCEDURE Update_Target_Level
( p_api_version IN NUMBER
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
Update_Target_Level
( p_api_version => p_api_version
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_Target_Level_Rec => p_Target_Level_Rec
, p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
, p_up_loaded => FND_API.G_FALSE
, x_return_status => x_return_status
, x_error_Tbl => x_error_Tbl
);
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
END Update_Target_Level;
PROCEDURE Update_Target_Level
( p_api_version IN NUMBER
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, p_owner IN VARCHAR2
, p_up_loaded IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_user_id NUMBER;
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
Update_Target_Level_Rec
( p_target_level_rec => p_target_level_rec
, p_target_level_rec1 => l_target_level_orig
, p_up_loaded => p_up_loaded
, x_target_level_rec => l_target_level_rec
, x_changed => l_changed
);
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
UPDATE BIS_TARGET_LEVELS
set
INDICATOR_ID = l_Target_Level_Rec.Measure_ID
, SHORT_NAME = l_Target_Level_Rec.Target_Level_Short_Name
, ORG_LEVEL_ID = l_Target_Level_Rec.org_Level_ID
, TIME_LEVEL_ID = l_Target_Level_Rec.time_Level_ID
, DIMENSION1_LEVEL_ID = l_Target_Level_Rec.Dimension1_Level_ID
, DIMENSION2_LEVEL_ID = l_Target_Level_Rec.Dimension2_Level_ID
, DIMENSION3_LEVEL_ID = l_Target_Level_Rec.Dimension3_Level_ID
, DIMENSION4_LEVEL_ID = l_Target_Level_Rec.Dimension4_Level_ID
, DIMENSION5_LEVEL_ID = l_Target_Level_Rec.Dimension5_Level_ID
, DIMENSION6_LEVEL_ID = l_Target_Level_Rec.Dimension6_Level_ID
, DIMENSION7_LEVEL_ID = l_Target_Level_Rec.Dimension7_Level_ID
, WF_PROCESS = l_Target_Level_Rec.Workflow_Process_Short_Name
, WF_ITEM_TYPE = l_Target_Level_Rec.Workflow_Item_Type
, REPORT_FUNCTION_ID = l_Target_Level_Rec.Report_Function_ID
-- , UNIT_OF_MEASURE = l_Target_Level_Rec.Unit_Of_Measure
, DEFAULT_ROLE_ID = l_Target_Level_Rec.Default_Notify_Resp_ID
, DEFAULT_ROLE = l_Target_Level_Rec.Default_Notify_Resp_short_name
, SYSTEM_FLAG = l_Target_Level_Rec.System_Flag
, DEFAULT_COMPUTING_FUNCTION_ID = l_target_level_rec.Computing_function_ID
-- , CREATION_DATE = SYSDATE
, CREATED_BY = l_user_id
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_LOGIN = l_login_id
, SOURCE = l_Target_Level_Rec.Source
where TARGET_LEVEL_ID = l_Target_Level_Rec.Target_Level_Id;
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
, p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
END Update_Target_Level;
Update_Target_Level_Rec
( p_target_level_rec => p_target_level_rec
, p_target_level_rec1 => l_target_level_orig
, x_target_level_rec => l_target_level_rec
, x_changed => l_changed
);
Update BIS_TARGET_LEVELS_TL
set
NAME = l_Target_Level_Rec.Target_Level_Name
, DESCRIPTION = l_Target_Level_Rec.description
, UNIT_OF_MEASURE = l_Target_Level_Rec.Unit_Of_Measure
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = l_user_id
, LAST_UPDATE_LOGIN = l_login_id
, SOURCE_LANG = userenv('LANG')
where TARGET_LEVEL_ID = l_Target_Level_Rec.Target_Level_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
PROCEDURE Delete_Target_Level
( p_api_version IN NUMBER
, p_force_delete IN NUMBER := 0--gbhaloti #3148615
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_Target_Tbl BIS_TARGET_PUB.Target_Tbl_Type;
if (l_Target_Tbl.COUNT > 0 AND p_force_delete = 0) then
--added last two params
l_error_tbl := x_error_tbl;
( p_error_msg_name => 'BIS_NO_DELETE_TARGET_LEVEL'
, p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
, p_error_proc_name => G_PKG_NAME||'.Delete_Target_Level'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
delete from bis_TARGET_LEVELS
where TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_Id;
delete from bis_TARGET_LEVELS_TL
where TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_Id;
BIS_MEASURE_SECURITY_PVT.Delete_Measure_Security( p_api_version
, p_commit
, p_Target_Level_Rec
, x_return_status
, x_error_Tbl
);
delete from bis_indicator_resps
where target_level_id = p_Target_Level_Rec.Target_Level_Id;
, p_error_proc_name => G_PKG_NAME||'.Delete_Target_Level'
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
END Delete_Target_Level;
SELECT Target_Level_id into x_Target_Level_ID
FROM bisbv_Target_Levels
WHERE target_level_short_name = p_Target_Level_Short_Name;
SELECT Target_Level_id into x_Target_Level_ID
FROM bisbv_Target_Levels
WHERE target_level_name = p_Target_Level_Name;
select distinct ir.target_level_id, target_level_name
from bis_indicator_resps ir
, fnd_user_resp_groups ur
, bisbv_target_levels il
where ur.user_id = p_user_id
and ir.responsibility_id = ur.responsibility_id
AND ur.start_date <= sysdate
AND nvl(ur.end_date, sysdate) >= sysdate
and il.target_level_id = ir.target_level_id
order by UPPER(target_level_name);
PROCEDURE Retrieve_Last_Update_Date
( p_api_version IN NUMBER
, p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
, x_last_update_date OUT NOCOPY DATE
, x_return_status OUT NOCOPY VARCHAR2
, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_date_char VARCHAR2(32000);
SELECT NVL(LAST_UPDATE_DATE, CREATION_DATE)
INTO x_last_update_date
FROM BIS_TARGET_LEVELS
WHERE TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_ID;
, p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
, p_error_type => BIS_UTILITIES_PUB.G_ERROR
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
, p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
, p_error_table => l_error_tbl
, x_error_table => x_error_tbl
);
END Retrieve_Last_Update_Date;
l_last_update_date DATE;
BIS_Target_Level_PVT.Retrieve_Last_Update_Date
( p_api_version => 1.0
, p_Target_Level_Rec => p_Target_Level_Rec
, x_last_update_date => l_last_update_date
, x_return_status => x_return_status
, x_error_Tbl => x_error_Tbl
);
IF(l_form_date = l_last_update_date) THEN
x_return_status := FND_API.G_TRUE;
select
dimension1_level_id,
dimension2_level_id,
dimension3_level_id,
dimension4_level_id,
dimension5_level_id,
dimension6_level_id,
dimension7_level_id
from bisbv_target_levels
where target_level_id = p_target_level_id;
select
dimension1_level_id,
dimension2_level_id,
dimension3_level_id,
dimension4_level_id,
dimension5_level_id,
dimension6_level_id,
dimension7_level_id
from bisbv_target_levels
where target_level_short_name = p_target_level_short_name;
Select dimension_id
into l_time_dimension_id
from bisbv_dimensions
--'TIME'
where upper(dimension_short_name) =
BIS_UTILITIES_PVT.Get_Time_Dimension_Name_TL(p_Target_Level_Rec.target_level_id,NULL);
Select dimension_id
into l_time_dimension_id
from bisbv_dimensions
--'TIME'
where upper(dimension_short_name) =
BIS_UTILITIES_PVT.Get_Time_Dimension_Name_TL(NULL,p_Target_Level_Rec.target_level_short_name);
Select dimension_id
into l_time_dimension_id
from bisbv_dimensions
--'TIME'
where upper(dimension_short_name) =
BIS_UTILITIES_PVT.Get_Time_Dimension_Name;
select
dimension1_level_id,
dimension2_level_id,
dimension3_level_id,
dimension4_level_id,
dimension5_level_id,
dimension6_level_id,
dimension7_level_id
from bisbv_target_levels
where target_level_id = p_target_level_id;
select
dimension1_level_id,
dimension2_level_id,
dimension3_level_id,
dimension4_level_id,
dimension5_level_id,
dimension6_level_id,
dimension7_level_id
from bisbv_target_levels
where target_level_short_name = p_target_level_short_name;
Select dimension_id
into l_org_dimension_id
from bisbv_dimensions -- 'ORG'
where upper(dimension_short_name) = l_dimension_short_name;
Select dimension_id
into l_org_dimension_id
from bisbv_dimensions --''ORG''
where upper(dimension_short_name) = l_dimension_short_name;
SELECT level_id , name
FROM bis_levels_vl
WHERE short_name=p_dim_level_short_name;
SELECT target_level_id , last_updated_by , created_by
FROM bis_target_levels
WHERE short_name = p_tl_short_name FOR UPDATE OF last_updated_by , created_by;
UPDATE bis_target_levels SET last_updated_by = 1 , created_by = 1, short_name = p_tl_new_short_name
WHERE current of c_updt1;
UPDATE bis_target_levels SET last_updated_by = 1 , created_by = 1
WHERE current of c_updt1;
UPDATE bis_target_levels_tl SET last_updated_by = 1 , created_by = 1
WHERE target_level_id = i.target_level_id;
SELECT indicator_id
INTO l_measure_id
FROM bis_indicators
WHERE short_name = p_Target_Level_rec.Measure_short_Name;
SELECT short_name
INTO l_dim_short_name
FROM bis_dimensions
WHERE dimension_id = p_dim_tbl_type(i);
SELECT dimension_id
INTO l_dim_id
FROM bis_levels
WHERE short_name = p_level_shtnm;
SELECT COUNT(1)
INTO l_num_dims
FROM BIS_INDICATOR_DIMENSIONS
WHERE indicator_id = p_measure_rec.measure_id;
SELECT short_name
INTO l_dim_short_name
FROM BIS_DIMENSIONS
WHERE dimension_id = p_dim_tbl_type(i);
SELECT D.short_name
INTO l_dim_short_name
FROM BIS_LEVELS L,
BIS_DIMENSIONS D
WHERE
L.short_name = p_lvl_short_name
AND L.dimension_id = D.dimension_id;