The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
SEED_QUAL_ID
FROM AS_SALES_LEAD_QUALS_VL
WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
SELECT
SEED_QUAL_ID,
UPPER(RANGE_FLAG),
UPPER(DATA_TYPE)
FROM AS_SALES_LEAD_QUALS_VL
WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
SELECT
SEED_QUAL_ID,
UPPER(RANGE_FLAG),
UPPER(DATA_TYPE)
FROM AS_SALES_LEAD_QUALS_VL
WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
SELECT
SEED_QUAL_ID,
UPPER(RANGE_FLAG),
UPPER(DATA_TYPE)
FROM AS_SALES_LEAD_QUALS_VL
WHERE SEED_QUAL_ID = IN_SEED_QUAL_ID;
SELECT AS_SALES_LEAD_SCORECARDS_S.NEXTVAL
FROM DUAL;
'Calling SCORECARDS_Insert_Row');
AS_SALES_LEAD_SCORECARDS_PKG.Insert_Row(
x_rowid => l_rowid
, x_scorecard_id => l_scorecard_id
, 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_description => l_SALES_LEAD_SCORECARD_rec.description
, x_enabled_flag => l_SALES_LEAD_SCORECARD_rec.enabled_flag
, x_start_date_active => l_SALES_LEAD_SCORECARD_rec.start_date_active
, x_end_date_active => l_SALES_LEAD_SCORECARD_rec.end_date_active
);
Procedure Update_ScoreCard (
p_api_version IN NUMBER := 2.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
P_SCORECARD_REC IN AS_SCORECARD_RULES_PUB.SCORECARD_REC_TYPE
:= AS_SCORECARD_RULES_PUB.G_MISS_SCORECARD_REC)
IS
CURSOR C_GET_LEAD_SCORECARD (IN_SCORECARD_ID NUMBER) IS
SELECT
ROWID,
SCORECARD_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM AS_SALES_LEAD_SCORECARDS
WHERE SCORECARD_ID = IN_SCORECARD_ID
FOR UPDATE NOWAIT;
SELECT fpo.PROFILE_OPTION_ID,
fpo.PROFILE_OPTION_NAME,
fpo.USER_PROFILE_OPTION_NAME
FROM FND_PROFILE_OPTIONS_VL fpo,
FND_PROFILE_OPTION_VALUES fpov
WHERE fpo.PROFILE_OPTION_NAME = 'AS_DEFAULT_SCORECARD'
AND fpo.PROFILE_OPTION_ID = fpov.PROFILE_OPTION_ID
AND fpov.profile_option_value = IN_SCORECARD_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Update_ScoreCard';
SAVEPOINT UPDATE_SCORECARD_PVT;
l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATE_DATE,
l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATED_BY,
l_REF_SALES_LEAD_SCORECARD_rec.CREATION_DATE,
l_REF_SALES_LEAD_SCORECARD_rec.CREATED_BY,
l_REF_SALES_LEAD_SCORECARD_rec.LAST_UPDATE_LOGIN,
l_REF_SALES_LEAD_SCORECARD_rec.DESCRIPTION,
l_REF_SALES_LEAD_SCORECARD_rec.ENABLED_FLAG,
l_REF_SALES_LEAD_SCORECARD_rec.START_DATE_ACTIVE,
l_REF_SALES_LEAD_SCORECARD_rec.END_DATE_ACTIVE;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
If (l_SALES_LEAD_SCORECARD_rec.last_update_date is NULL or
l_SALES_LEAD_SCORECARD_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('AS', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
'Calling _SCORECARDS_Update_Row');
update_scd_dates(l_sales_lead_scorecard_rec.start_date_active,
l_sales_lead_scorecard_rec.end_date_active);
AS_SALES_LEAD_SCORECARDS_PKG.Update_Row(
x_rowid => l_rowid
, x_scorecard_id => l_SALES_LEAD_SCORECARD_rec.scorecard_id
, x_last_update_date => SYSDATE
, x_last_updated_by => FND_GLOBAL.USER_ID
, x_creation_date => l_SALES_LEAD_SCORECARD_rec.creation_date
, x_created_by => l_SALES_LEAD_SCORECARD_rec.created_by
, x_last_update_login => FND_GLOBAL.USER_ID
, x_description => l_SALES_LEAD_SCORECARD_rec.description
, x_enabled_flag => l_SALES_LEAD_SCORECARD_rec.enabled_flag
, x_start_date_active => l_SALES_LEAD_SCORECARD_rec.start_date_active
, x_end_date_active => l_SALES_LEAD_SCORECARD_rec.end_date_active
);
END Update_ScoreCard;
/* Only Delete records from as_sales_lead_scorecards;
not delete records from as_sales_lead_card_rules;
Procedure Delete_ScoreCard (
p_api_version IN NUMBER := 2.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
P_SCORECARD_ID IN NUMBER)
IS
CURSOR C_GET_LEAD_SCORECARD (IN_SCORECARD_ID NUMBER) IS
SELECT
SCORECARD_ID
FROM AS_SALES_LEAD_SCORECARDS
WHERE SCORECARD_ID = IN_SCORECARD_ID;
SELECT CARD_RULE_ID
FROM AS_SALES_LEAD_CARD_RULES
WHERE SCORECARD_ID = IN_SCORECARD_ID;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ScoreCard';
SAVEPOINT DELETE_SCORECARD_PVT;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
'Calling _SCORECARD_Delete_Row');
AS_SALES_LEAD_SCORECARDS_PKG.Delete_Row(
x_scorecard_ID => l_SCORECARD_ID);
END Delete_ScoreCard;
SELECT AS_CARD_RULE_QUAL_VALUES_S.NEXTVAL
FROM DUAL;
'Calling CARDRULE_QUAL_Insert_Row');
AS_CARD_RULE_QUAL_VALUES_PKG.Insert_Row(
x_rowid => l_rowid
, x_qual_value_id => l_qual_value_id
, 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_scorecard_id => l_CARDRULE_QUAL_rec.scorecard_id
, x_score => l_CARDRULE_QUAL_rec.score
, x_card_rule_id => 0
, x_seed_qual_id => l_CARDRULE_QUAL_rec.seed_qual_id
, x_high_value_number => l_CARDRULE_QUAL_rec.high_value_number
, x_low_value_number => l_CARDRULE_QUAL_rec.low_value_number
, x_high_value_char => l_CARDRULE_QUAL_rec.high_value_char
, x_low_value_char => l_CARDRULE_QUAL_rec.low_value_char
, x_currency_code => l_CARDRULE_QUAL_rec.currency_code
, x_low_value_date => l_CARDRULE_QUAL_rec.low_value_date
, x_high_value_date => l_CARDRULE_QUAL_rec.high_value_date
, x_start_date_active => l_CARDRULE_QUAL_rec.start_date_active
, x_end_date_active => l_CARDRULE_QUAL_rec.end_date_active
);
'after insert '||l_qual_value_id);
Procedure Update_CardRule_QUAL
(p_api_version IN NUMBER := 2.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER :=
AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_CardRule_Qual_rec IN AS_SCORECARD_RULES_PUB.CARDRULE_QUAL_REC_TYPE)
IS
CURSOR C_GET_CARDRULE_QUAL (IN_QUAL_VALUE_ID NUMBER) IS
SELECT
rowid,
QUAL_VALUE_ID
FROM AS_CARD_RULE_QUAL_VALUES
WHERE QUAL_VALUE_ID = IN_QUAL_VALUE_ID
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_CARDRULE_QUAL';
SAVEPOINT UPDATE_CARDRULE_QUAL_PVT;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
If (l_CARDRULE_QUAL_rec.last_update_date is NULL or
l_CARDRULE_QUAL_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('AS', 'API_MISSING_ID');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
'Calling _CARDRULE_QUAL_Update_Row');
AS_CARD_RULE_QUAL_VALUES_PKG.Update_Row(
x_rowid => l_rowid
, x_qual_value_id => l_CARDRULE_QUAL_rec.qual_value_id
, x_last_update_date => SYSDATE
, x_last_updated_by => FND_GLOBAL.USER_ID
, x_last_update_login => FND_GLOBAL.USER_ID
, x_scorecard_id => l_CARDRULE_QUAL_rec.scorecard_id
, x_score => l_CARDRULE_QUAL_rec.score
, x_card_rule_id => -1
, x_seed_qual_id => l_CARDRULE_QUAL_rec.seed_qual_id
, x_high_value_number => l_CARDRULE_QUAL_rec.high_value_number
, x_low_value_number => l_CARDRULE_QUAL_rec.low_value_number
, x_high_value_char => l_CARDRULE_QUAL_rec.high_value_char
, x_low_value_char => l_CARDRULE_QUAL_rec.low_value_char
, x_currency_code => l_CARDRULE_QUAL_rec.currency_code
, x_low_value_date => l_CARDRULE_QUAL_rec.low_value_date
, x_high_value_date => l_CARDRULE_QUAL_rec.high_value_date
, x_start_date_active => l_CARDRULE_QUAL_rec.start_date_active
, x_end_date_active => l_CARDRULE_QUAL_rec.end_date_active
);
END Update_CardRule_Qual;
Procedure Delete_CardRule_QUAL
(p_api_version IN NUMBER := 2.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER :=
AS_UTILITY_PUB.G_VALID_LEVEL_ITEM,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_qual_value_id IN NUMBER)
IS
CURSOR C_GET_CARDRULE_QUAL (IN_QUAL_VALUE_ID NUMBER) IS
SELECT
QUAL_VALUE_ID
FROM AS_CARD_RULE_QUAL_VALUES
WHERE QUAL_VALUE_ID = IN_QUAL_VALUE_ID
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_CARDRULE_QUAL';
SAVEPOINT DELETE_CARD_RULE_PVT;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
'Calling _CARDRULE_QUAL_Delete_Row');
AS_CARD_RULE_QUAL_VALUES_PKG.Delete_Row(
x_QUAL_VALUE_ID => l_QUAL_VALUE_ID);
END Delete_CardRule_Qual;