[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* clchang updated 10/17/2003
* added P_SCORE_ID, and updated CURSOR C_GET_SCORE.
* This procedure will validate
* if any dup score name existing other than
* the score_name of the P_SCORE_ID.
*======================================================================*/
Procedure Validate_SCORE_Name(P_Init_Msg_List IN VARCHAR2 ,
P_Score_Name IN VARCHAR2 ,
P_Score_Id IN NUMBER ,
X_Dup_Status OUT NOCOPY VARCHAR2,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_SCORE_name (IN_SCORE_Name VARCHAR2, IN_SCORE_ID NUMBER) IS
SELECT score_Name
FROM iex_scores
WHERE SCORE_Name = IN_SCORE_Name
AND SCORE_ID <> IN_SCORE_ID;
SELECT score_Name
FROM iex_scores
WHERE SCORE_Name = IN_SCORE_Name and score_id <> IN_Score_ID;
SELECT score_comp_Name
FROM iex_score_comp_types_vl
WHERE SCORE_comp_name = IN_SCORE_comp_Name;
SELECT score_comp_Name
FROM iex_score_comp_types_vl
WHERE SCORE_comp_name = IN_SCORE_comp_Name
AND SCORE_COMP_TYPE_ID <> IN_SCORE_COMP_TYPE_ID;
SELECT IEX_SCORES_S.nextval
FROM sys.dual;
WriteLog(l_msg || 'insert row');
IEX_SCORES_PKG.insert_row(
x_rowid => l_rowid
, p_score_id => x_score_id
, p_security_group_id => l_score_rec.security_group_id
, p_score_name => l_score_rec.score_name
, p_score_description => l_score_rec.score_description
, p_enabled_flag => l_score_rec.enabled_flag
, p_valid_from_dt => l_score_rec.valid_from_dt
, p_valid_to_dt => l_score_rec.valid_to_dt
, p_campaign_sched_id => l_score_rec.campaign_sched_id
, p_jtf_object_code => l_score_rec.jtf_object_code
, p_concurrent_prog_name => l_score_rec.concurrent_prog_name
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
, p_request_id => l_score_rec.request_id
, p_program_application_id => l_score_rec.program_application_id
, p_program_id => l_score_rec.program_id
, p_program_update_date => l_score_rec.program_update_date
, p_STATUS_DETERMINATION => l_score_rec.STATUS_DETERMINATION
, p_WEIGHT_REQUIRED => l_score_rec.WEIGHT_REQUIRED
, p_SCORE_RANGE_LOW => l_score_rec.SCORE_RANGE_LOW
, p_SCORE_RANGE_HIGH => l_score_rec.SCORE_RANGE_HIGH
, p_OUT_OF_RANGE_RULE => l_score_rec.OUT_OF_RANGE_RULE);
Procedure Update_Score(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_SCORE_REC IN IEX_SCORE_PUB.SCORE_REC_TYPE,
x_dup_status OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_get_Score_Rec (IN_SCORE_ID NUMBER) is
SELECT ROWID,
SCORE_ID,
SCORE_NAME,
SCORE_DESCRIPTION,
ENABLED_FLAG ,
VALID_FROM_DT,
VALID_TO_DT,
CAMPAIGN_SCHED_ID,
JTF_OBJECT_CODE,
CONCURRENT_PROG_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
STATUS_DETERMINATION,
WEIGHT_REQUIRED,
SCORE_RANGE_LOW,
SCORE_RANGE_HIGH,
OUT_OF_RANGE_RULE
from iex_scores
where score_id = in_score_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Score';
SAVEPOINT UPDATE_SCORE_PVT;
l_score_ref_rec.LAST_UPDATE_DATE,
l_score_ref_rec.LAST_UPDATED_BY,
l_score_ref_rec.CREATION_DATE,
l_score_ref_rec.CREATED_BY,
l_score_ref_rec.LAST_UPDATE_LOGIN,
l_score_ref_rec.STATUS_DETERMINATION,
l_score_ref_rec.WEIGHT_REQUIRED,
l_score_ref_rec.SCORE_RANGE_LOW,
l_score_ref_rec.SCORE_RANGE_HIGH,
l_score_ref_rec.OUT_OF_RANGE_RULE;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IF (l_score_rec.last_update_date is NULL or
l_score_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog('iexvscrb:UpdScr: update row');
IEX_SCORES_PKG.update_row(
x_rowid => l_rowid
, p_score_id => l_score_rec.score_id
, p_security_group_id => l_score_rec.security_group_id
, p_score_name => l_score_rec.score_name
, p_score_description => l_score_rec.score_description
, p_enabled_flag => l_score_rec.enabled_flag
, p_valid_from_dt => l_score_rec.valid_from_dt
, p_valid_to_dt => l_score_rec.valid_to_dt
, p_campaign_sched_id => l_score_rec.campaign_sched_id
, p_jtf_object_code => l_score_rec.jtf_object_code
, p_concurrent_prog_name => l_score_rec.concurrent_prog_name
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => l_score_rec.creation_date
, p_created_by => l_score_rec.created_by
, p_last_update_login => FND_GLOBAL.USER_ID
, p_request_id => l_score_rec.request_id
, p_program_application_id => l_score_rec.program_application_id
, p_program_id => l_score_rec.program_id
, p_program_update_date => l_score_rec.program_update_date
, p_STATUS_DETERMINATION => l_score_rec.STATUS_DETERMINATION
, p_WEIGHT_REQUIRED => l_score_rec.WEIGHT_REQUIRED
, p_SCORE_RANGE_LOW => l_score_rec.SCORE_RANGE_LOW
, p_SCORE_RANGE_HIGH => l_score_rec.SCORE_RANGE_HIGH
, p_OUT_OF_RANGE_RULE => l_score_rec.OUT_OF_RANGE_RULE);
ROLLBACK TO UPDATE_SCORE_PVT;
IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: exc exp:'||SQLERRM);
ROLLBACK TO UPDATE_SCORE_PVT;
IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: unexc exp:'||SQLERRM);
ROLLBACK TO UPDATE_SCORE_PVT;
IEX_SCORE_PVT.WriteLog('iexvscrb:UpdateScr: other exp:'||SQLERRM);
END Update_Score;
Procedure Delete_Score(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_SCORE_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_SCORE (IN_SCORE_ID NUMBER) IS
SELECT rowid
FROM IEX_SCORES
WHERE SCORE_ID = IN_SCORE_ID;
SELECT SCORE_COMPONENT_ID
FROM IEX_SCORE_COMPONENTS
WHERE SCORE_ID = IN_SCORE_ID;
SELECT OBJECT_FILTER_ID
FROM IEX_OBJECT_FILTERS
WHERE OBJECT_ID = IN_SCORE_ID
AND OBJECT_FILTER_TYPE = 'IEXSCORE';
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score';
SAVEPOINT DELETE_SCORE_PVT;
IEX_DEBUG_PUB.LogMessage('Delete_Score: ' || 'iexvscrb.pls:Delete_Score=>scoreid='||p_score_id);
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog( 'iexvscrb: DelScr: Delete Row');
IEX_SCORES_PKG.Delete_Row(
x_rowid => l_rowid);
WriteLog('iexvscrb:Delete_Score=>delete scrcomp');
WriteLog('iexvscrb:Delete_Score=>scrcompid='||l_score_comp_id);
IEX_SCORE_PVT.Delete_Score_Comp(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_score_id => p_score_id
, p_score_comp_id => l_score_comp_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
WriteLog('iexvscrb:Delete_Score=>after delete scrcomp');
WriteLog('iexvscrb:Delete_Score=>delete filter');
WriteLog('iexvscrb:Delete_Score=>filterid='||l_object_filter_id);
IEX_FILTER_PUB.Delete_OBJECT_FILTER(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_object_filter_id => l_object_filter_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
WriteLog('iexvscrb:Delete_Score=>after delete filter');
WriteLog('iexvscrb:Delete_Score=>delete del statuses');
IEX_DEL_STATUSES_PKG.Delete_del_config(p_score_id);
WriteLog('iexvscrb:Delete_Score=>after delete del statuses');
WriteLog('iexvscrb:DeleteScore: End');
ROLLBACK TO DELETE_SCORE_PVT;
ROLLBACK TO DELETE_SCORE_PVT;
ROLLBACK TO DELETE_SCORE_PVT;
END Delete_Score;
SELECT IEX_SCORE_COMPONENTS_S.nextval
FROM sys.dual;
WriteLog(l_msg || 'Insert Row');
IEX_SCORE_COMPONENTS_PKG.insert_row(
x_rowid => l_rowid
, p_score_component_id => x_score_comp_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
, p_score_comp_weight => l_score_comp_rec.score_comp_weight
, p_score_id => l_score_comp_rec.score_id
, p_enabled_flag => l_score_comp_rec.enabled_flag
, P_SCORE_COMP_TYPE_ID => l_score_comp_rec.SCORE_COMP_TYPE_ID);
Procedure Update_SCORE_COMP(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_SCORE_COMP_Rec IN IEX_SCORE_PUB.SCORE_COMP_Rec_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_get_Score_Comp_Rec (IN_SCORE_COMP_ID NUMBER) is
select ROWID,
SCORE_COMPONENT_ID,
SCORE_COMP_WEIGHT,
SCORE_ID,
ENABLED_FLAG,
SCORE_COMP_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN
from iex_score_components
where score_component_id = in_score_comp_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp';
SAVEPOINT UPDATE_Score_Comp_PVT;
l_score_comp_ref_rec.LAST_UPDATE_DATE,
l_score_comp_ref_rec.LAST_UPDATED_BY,
l_score_comp_ref_rec.CREATION_DATE,
l_score_comp_ref_rec.CREATED_BY,
l_score_comp_ref_rec.LAST_UPDATE_LOGIN;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IF (l_score_comp_rec.last_update_date is NULL or
l_score_comp_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog('iexvscrb:UpdScrComp: Update Row');
IEX_SCORE_COMPONENTS_PKG.update_row(
x_rowid => l_rowid
, p_score_component_id => l_score_comp_rec.score_component_id
, p_score_comp_weight => l_score_comp_rec.score_comp_weight
, p_score_id => l_score_comp_rec.score_id
, p_enabled_flag => l_score_comp_rec.enabled_flag
, P_SCORE_COMP_TYPE_ID => l_score_comp_rec.SCORE_COMP_TYPE_ID
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => l_score_comp_rec.creation_date
, p_created_by => l_score_Comp_rec.created_by
, p_last_update_login => FND_GLOBAL.USER_ID);
ROLLBACK TO UPDATE_SCORE_COMP_PVT;
ROLLBACK TO UPDATE_SCORE_COMP_PVT;
ROLLBACK TO UPDATE_SCORE_COMP_PVT;
END Update_SCORE_COMP;
Procedure Delete_SCORE_COMP(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_SCORE_ID IN NUMBER,
p_SCORE_COMP_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
CURSOR C_GET_SCORE_COMP (IN_SCORE_COMP_ID NUMBER) IS
SELECT rowid
FROM IEX_SCORE_COMPONENTS
WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
SELECT Score_Comp_Det_id
FROM IEX_SCORE_COMP_DET
WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp';
SAVEPOINT DELETE_SCORE_COMP_PVT;
WriteLog('iexvscrb:Delete_Score_Comp=>Start');
WriteLog('iexvscrb:Delete_Score_Comp=>scorecompid='||p_score_comp_id);
WriteLog('iexvscrb:Delete_Score_Comp=>check score comp exists or not');
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
WriteLog('iexvscrb:Delete_Score_Comp=>Delete Row');
IEX_SCORE_COMPONENTS_PKG.Delete_Row(
x_rowid => l_rowid);
WriteLog('iexvscrb:Delete_Score_Comp=>delete scrcompdetails');
WriteLog('iexvscrb:Delete_Score_Comp=>scrcompdetid='||l_score_comp_Det_id);
IEX_SCORE_PVT.Delete_Score_Comp_Det(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_score_comp_id => p_score_comp_id
, p_score_comp_det_id => l_score_comp_det_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
WriteLog('iexvscrb:Delete_Score_Comp=>end');
ROLLBACK TO DELETE_SCORE_COMP_PVT;
ROLLBACK TO DELETE_SCORE_COMP_PVT;
ROLLBACK TO DELETE_SCORE_COMP_PVT;
END Delete_Score_Comp;
SELECT IEX_SCORE_COMP_TYPES_B_S.nextval
FROM sys.dual;
WriteLog('iexvscrb:CreateScrCompType: insert row');
IEX_SCORE_COMP_TYPES_PKG.insert_row(
x_rowid => l_rowid
, P_SCORE_COMP_TYPE_ID => x_score_comp_type_id
, p_OBJECT_Version_Number => l_score_Comp_Type_rec.object_version_number
, p_score_comp_value => l_score_comp_type_rec.score_comp_value
, p_score_comp_name => l_score_comp_type_rec.score_comp_name
, p_active_flag => l_score_comp_type_rec.active_flag
, P_description => l_score_comp_type_rec.Description
, P_jtf_object_code => l_score_comp_type_rec.jtf_object_code
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID
, p_function_flag => l_score_comp_type_rec.function_flag
, p_metric_flag => l_score_comp_type_rec.metric_flag
, p_display_order => l_score_comp_type_rec.display_order);
Procedure Update_SCORE_COMP_TYPE(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_SCORE_COMP_Type_Rec IN IEX_SCORE_PUB.SCORE_COMP_Type_Rec_TYPE,
x_dup_status OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_get_Score_Comp_Type_B_Rec (IN_SCORE_COMP_Type_ID NUMBER) is
select SCORE_COMP_TYPE_ID,
SCORE_COMP_value,
ACTIVE_FLAG,
JTF_OBJECT_CODE,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
FUNCTION_FLAG,
METRIC_FLAG,
DISPLAY_ORDER
from iex_score_comp_types_b
where score_comp_type_id = in_score_comp_type_id
FOR UPDATE NOWAIT;
select SCORE_COMP_TYPE_ID,
SCORE_COMP_NAME,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
DESCRIPTION
from iex_score_comp_types_tl
where score_comp_type_id = in_score_comp_type_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp_Type';
SAVEPOINT UPDATE_Score_Comp_TYPE_PVT;
l_score_comp_type_ref_rec.LAST_UPDATE_DATE,
l_score_comp_type_ref_rec.LAST_UPDATED_BY,
l_score_comp_type_ref_rec.CREATION_DATE,
l_score_comp_type_ref_rec.CREATED_BY,
l_score_comp_type_ref_rec.LAST_UPDATE_LOGIN,
l_score_comp_type_ref_rec.function_flag,
l_score_comp_type_ref_rec.METRIC_flag,
l_score_comp_type_ref_rec.DISPLAY_ORDER;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
l_score_comp_type_ref_rec.LAST_UPDATE_DATE,
l_score_comp_type_ref_rec.LAST_UPDATED_BY,
l_score_comp_type_ref_rec.CREATION_DATE,
l_score_comp_type_ref_rec.CREATED_BY,
l_score_comp_type_ref_rec.LAST_UPDATE_LOGIN,
l_score_comp_type_ref_rec.DESCRIPTION;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IF (l_score_comp_type_rec.last_update_date is NULL or
l_score_comp_type_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog('iexvscrb:UpdScrCompType:IEX_SCORE_COMP_TYPES.Update_Row');
IEX_SCORE_COMP_TYPES_PKG.update_row(
p_score_comp_TYPE_id => l_score_comp_type_rec.score_comp_type_id
, p_score_comp_name => l_score_comp_type_rec.score_comp_name
, p_score_comp_value => l_score_comp_type_rec.score_comp_value
, p_active_flag => l_score_comp_type_rec.active_flag
, p_jtf_object_code => l_score_comp_type_rec.jtf_object_code
, p_function_flag => l_score_comp_type_rec.function_flag
, p_metric_flag => l_score_comp_type_rec.metric_flag
, p_display_order => l_score_comp_type_rec.display_order
, P_object_version_number => l_score_comp_type_rec.object_version_number
, p_description => l_score_comp_type_rec.description
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID);
ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
ROLLBACK To UPDATE_Score_Comp_TYPE_PVT;
END Update_SCORE_COMP_TYPE;
Procedure Delete_SCORE_COMP_TYPE
(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
P_SCORE_COMP_Type_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_TYPE (IN_SCORE_COMP_TYPE_ID NUMBER) IS
SELECT rowid
FROM IEX_SCORE_COMP_TYPES_B
WHERE SCORE_COMP_TYPE_ID = IN_SCORE_COMP_TYPE_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp_Type';
SAVEPOINT DELETE_SCORE_COMP_TYPE_PVT;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IEX_SCORE_COMP_TYPES_PKG .Delete_Row(
p_score_comp_type_id => l_score_comp_type_id);
ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
ROLLBACK To DELETE_Score_Comp_TYPE_PVT;
END Delete_Score_comp_TYpe;
SELECT IEX_SCORE_COMP_DET_S.nextval
FROM sys.dual;
WriteLog(l_msg || 'Insert Row');
IEX_SCORE_COMP_DET_PKG.insert_row(
x_rowid => l_rowid
, p_score_comp_det_id => x_score_comp_det_id
, p_range_low => l_score_comp_det_rec.range_low
, p_range_high => l_score_comp_det_rec.range_high
, p_value => l_score_comp_det_rec.value
, p_new_value => l_score_comp_det_rec.new_value
, p_score_component_id => l_score_comp_det_rec.score_component_id
, p_object_version_number => l_score_comp_det_rec.object_version_number
, p_program_id => l_score_comp_det_rec.program_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => sysdate
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.USER_ID);
Procedure Update_SCORE_COMP_DET(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_SCORE_COMP_DET_Rec IN IEX_SCORE_PUB.SCORE_COMP_DET_REC_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_get_Score_Comp_Det_Rec (IN_SCORE_COMP_Det_ID NUMBER) is
select ROWID,
SCORE_COMP_DET_ID,
RANGE_LOW,
RANGE_HIGH,
VALUE,
NEW_VALUE,
SCORE_COMPONENT_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN
from iex_score_comp_det
where score_comp_det_id = in_score_comp_det_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Score_Comp_Det';
SAVEPOINT UPDATE_Score_Comp_DET_PVT;
l_score_comp_det_ref_rec.LAST_UPDATE_DATE,
l_score_comp_det_ref_rec.LAST_UPDATED_BY,
l_score_comp_det_ref_rec.CREATION_DATE,
l_score_comp_det_ref_rec.CREATED_BY,
l_score_comp_det_ref_rec.LAST_UPDATE_LOGIN;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IF (l_score_comp_det_rec.last_update_date is NULL or
l_score_comp_det_rec.last_update_date = FND_API.G_MISS_Date )
THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
WriteLog('iexvscrb: UpdSrCompDet: Update Row');
IEX_SCORE_COMP_DET_PKG.update_row(
x_rowid => l_rowid
, p_score_comp_det_id => l_score_comp_det_rec.score_comp_det_id
, p_last_update_date => sysdate
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => l_score_comp_det_rec.CREATION_DATE
, p_created_by => l_score_comp_det_rec.CREATED_BY
, p_last_update_login => FND_GLOBAL.USER_ID
, p_range_low => l_score_comp_det_rec.range_low
, p_range_high => l_score_comp_det_rec.range_high
, p_value => l_score_comp_det_rec.value
, p_new_value => l_score_comp_det_rec.new_value
, p_score_component_id => l_score_comp_det_rec.score_component_id
, p_object_version_number => l_score_comp_det_rec.object_version_number
, p_program_id => l_score_comp_det_rec.program_id);
ROLLBACK To UPDATE_Score_Comp_DET_PVT;
ROLLBACK To UPDATE_Score_Comp_DET_PVT;
ROLLBACK To UPDATE_Score_Comp_DET_PVT;
END Update_SCORE_COMP_DET;
Procedure Delete_SCORE_COMP_DET(p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_SCORE_COMP_ID IN NUMBER,
p_SCORE_COMP_DET_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR C_GET_SCORE_COMP_DET (IN_SCORE_COMP_DET_ID NUMBER) IS
SELECT rowid
FROM IEX_SCORE_COMP_DET
WHERE SCORE_COMP_DET_ID = IN_SCORE_COMP_DET_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Score_Comp_DET';
SAVEPOINT DELETE_SCORE_COMP_DET_PVT;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
IEX_SCORE_COMP_DET_PKG.Delete_Row(x_rowid => l_rowid);
ROLLBACK To DELETE_Score_Comp_DET_PVT;
ROLLBACK To DELETE_Score_Comp_DET_PVT;
ROLLBACK To DELETE_Score_Comp_DET_PVT;
END Delete_Score_Comp_Det;
* clchang updated 10/21/04 it should also copy data from iex_del_statuses;
SELECT SCORE_NAME,
LENGTH(SCORE_NAME),
SCORE_DESCRIPTION,
--ENABLED_FLAG ,
'N' ,
VALID_FROM_DT,
VALID_TO_DT,
CAMPAIGN_SCHED_ID,
JTF_OBJECT_CODE,
CONCURRENT_PROG_NAME,
STATUS_DETERMINATION,
WEIGHT_REQUIRED,
SCORE_RANGE_LOW,
SCORE_RANGE_HIGH,
OUT_OF_RANGE_RULE
FROM IEX_SCORES
WHERE SCORE_ID = IN_SCORE_ID;
SELECT SCORE_COMPONENT_ID
FROM IEX_SCORE_COMPONENTS
WHERE SCORE_ID = IN_SCORE_ID;
select SCORE_COMP_WEIGHT,
SCORE_ID,
ENABLED_FLAG,
SCORE_COMP_TYPE_ID
from iex_score_components
where score_component_id = in_score_comp_id;
SELECT Score_Comp_Det_id
FROM IEX_SCORE_COMP_DET
WHERE SCORE_COMPONENT_ID = IN_SCORE_COMP_ID;
select RANGE_LOW,
RANGE_HIGH,
VALUE,
NEW_VALUE,
SCORE_COMPONENT_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_ID
from iex_score_comp_det
where score_comp_det_id = in_score_comp_det_id;
SELECT OBJECT_FILTER_ID,
OBJECT_FILTER_NAME,
OBJECT_ID,
SELECT_COLUMN,
ENTITY_NAME,
ACTIVE_FLAG,
OBJECT_VERSION_NUMBER
FROM IEX_OBJECT_FILTERS
WHERE OBJECT_ID = IN_SCORE_ID
AND OBJECT_FILTER_TYPE = 'IEXSCORE';
SELECT DEL_STATUS_ID,
SCORE_VALUE_LOW,
SCORE_VALUE_HIGH,
DEL_STATUS,
SCORE_ID
FROM IEX_DEL_STATUSES
WHERE SCORE_ID = IN_SCORE_ID;
FND_MESSAGE.Set_Name('IEX', 'API_MISSING_UPDATE_TARGET');
l_filter_rec.SELECT_COLUMN,
l_filter_rec.ENTITY_NAME,
l_filter_rec.ACTIVE_FLAG,
l_filter_rec.OBJECT_VERSION_NUMBER;
IEX_DEL_STATUSES_PKG.Insert_Row_With_Defaults(
x_rowid => l_row_id
, p_score_value_low => d.score_value_low
, p_score_value_high => d.score_value_high
, p_del_status => d.del_status
, p_score_id => l_score_id
, commit_flag => p_commit);
SELECT DISTINCT PARTY_CUST_ID
--FROM IEX_DELINQUENCIES_ALL
FROM IEX_DELINQUENCIES
WHERE STATUS = 'OPEN';
/* 2. get the parties to update */
OPEN c_del_parties;
SELECT VALUE INTO l_raw_score
FROM iex_score_comp_det
WHERE score_component_id = l_score_comp_tbl(l_count).SCORE_COMPONENT_ID
AND l_component_score >= RANGE_LOW
AND l_component_score <= RANGE_HIGH;
/* UPDATE IEX_SCORE_HISTORIES with the collections score, score_id, and last_score_date */
/*
SELECT IEX_SCORE_HISTORIES_S.nextval
INTO l_score_history_id
FROM dual;
IEX_SCORE_HISTORIES_PKG.Insert_Row(X_ROWID => l_rowid,
P_SCORE_HISTORY_ID => l_score_history_id,
P_OBJECT_VERSION_NUMBER => 1,
P_PROGRAM_ID => 1,
P_LAST_UPDATE_DATE => sysdate,
P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
P_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID,
P_CREATION_DATE => sysdate,
P_CREATED_BY => FND_GLOBAL.USER_ID,
P_SCORE_VALUE => l_running_score,
P_SCORE_ID => l_score_id,
P_PARTY_ID => l_party_id);
insert into iex_score_histories(SCORE_HISTORY_ID
,OBJECT_VERSION_NUMBER
,PROGRAM_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,SCORE_VALUE
,SCORE_ID
,PARTY_ID)
values(IEX_SCORE_HISTORIES_S.nextval
,1
,1
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,l_score_tbl(n)
,l_score_id
,l_party_tbl(n));
SELECT SCORE_ID
FROM IEX_SCORES
WHERE SCORE_NAME = p_score_name AND
ENABLED_FLAG = 'Y' AND
VALID_FROM_DT < sysdate AND
VALID_TO_DT >= sysdate;
SELECT SCORE_ID
FROM IEX_SCORES
WHERE SCORE_ID = p_score_id AND
ENABLED_FLAG = 'Y' AND
VALID_FROM_DT < sysdate AND
VALID_TO_DT >= sysdate;
SELECT
SCORE_COMPONENT_ID,
SCORE_COMP_WEIGHT,
SCORE_COMP_VALUE
FROM
IEX_SCORE_ENG_COMPONENTS_V
WHERE SCORE_ID = p_score_id;