[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| p_col_name name of colum you wish to select on
|| p_table_name name of table to select from
||
|| Return value: True =OK; Falso=Error
' Select Select_ID, From_table ' ||
' From jtf_objects_b ' ||
' where object_code = :p_object_code'
into l_col_name, l_table_name
using p_object_type;
' Select jtf_object_code ' ||
' From iex_scores ' ||
' where score_id = :p_score_id'
into l_object_type
using p_score_id;
* out_of_range_rule for a given score engine, and update the
* global variables: G_MIN_SCORE, G_MAX_SCORE, G_RULE.
*
* Parameter: P_SCORE_ID Scoring_Engine
* Major Modifications:
* when who what
* 10/18/04 clchang created
******/
PROCEDURE getScoreRange(P_SCORE_ID IN NUMBER )
IS
CURSOR c_chk_range(p_score_id NUMBER) IS
SELECT NVL(WEIGHT_REQUIRED, 'N'),
NVL(SCORE_RANGE_LOW, IEX_SCORE_NEW_PVT.G_MIN_SCORE),
NVL(SCORE_RANGE_HIGH, IEX_SCORE_NEW_PVT.G_MAX_SCORE),
NVL(OUT_OF_RANGE_RULE, IEX_SCORE_NEW_PVT.G_RULE)
FROM IEX_SCORES
WHERE SCORE_ID = p_score_id;
|| Return value: select statement for the Universe
||
|| Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS, IEX_SCORE_COMP_TYPES_B/TL
||
|| Target Tables: none
||
|| Creation date: 01/14/02 1:55:PM
||
|| Major Modifications: when who what
|| 01/14/02 raverma created
|| 03/12/02 raverma added function_flag to return tbl
|| 10/18/04 clchang updated this procedure for scoring engine enhancement
|| in 11.5.11.
|| 1. new column METRIC_FLAG in iex_score_components
*/
PROCEDURE getComponents(P_SCORE_ID IN NUMBER,
X_SCORE_COMP_TBL OUT NOCOPY IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL)
IS
-- clchang updated the cursor 10/18/04 with metric_flag;
SELECT
SCORE_COMPONENT_ID,
SCORE_COMP_WEIGHT,
SCORE_COMP_VALUE,
NVL(FUNCTION_FLAG, 'N') FUNCTION_FLAG
FROM
IEX_SCORE_ENG_COMPONENTS_V
WHERE SCORE_ID = p_score_id
AND NVL(METRIC_FLAG, 'N') = 'N';
|| 10/18/04 clchang updated this procedure for scoring engine enhancement
|| in 11.5.11.
|| 1. new column METRIC_FLAG in iex_score_components
|| 2. new columns 'WEIGHT_REQUIRED, SCORE_RANGE_LOW,
|| SCORE_RANGE_HIGH, OUT_OF_RANGE_RULE' in iex_scores
|| 3. no 1-100 score limitation;
l_execute_style VARCHAR2(1); -- are we using select or function call
Select NVL(CONCURRENT_PROG_NAME, 'X')
Into l_conc_prog_name
From IEX_SCORES scr, IEX_SCORE_COMPONENTS scomp
Where scomp.score_component_id = p_score_comp_tbl(1).SCORE_COMPONENT_ID
AND scr.Score_ID = scomp.score_id;
l_objects_tbl.delete;
l_new_scores_tbl.delete;
l_scores_tbl.delete;
l_scores_tbl.delete;
select jtf_object_code into l_object_type
from iex_scores
where score_id = p_score_id;
Select IEX_DEL_WF_S.NEXTVAL INTO l_del_buff_bridge FROM Dual; --Added by schekuri for bug#6373998 on 31-Aug-2007
Select NVL(cp.Concurrent_Program_Name, 'X')
Into l_conc_prog_name
From IEX_SCORES scr, fnd_concurrent_programs cp
Where scr.concurrent_prog_name = cp.concurrent_program_name AND
scr.Score_ID = p_score_id;
select security_profile_name into l_pf_name from per_security_profiles
where security_profile_id = l_pf_value;
|| Overview: delete rows from IEX_SCORE_HISTORIES to improve performance
||
|| Parameter: p_score_object_code => score_object_code to erase
|| p_from_date => remove from this date
|| p_to_Date => remove up to this date
|| p_request_id => remove this request
|| p_save_last_run => save the last run of the object type
|| all parameters are AND logic on the where clause
||
|| Source Tables:
||
|| Target Tables: IEX_SCORE_HISTORIES
||
|| Creation date: 01/28/03 3:14:PM
||
|| Major Modifications: when who what
|| 01/28/03 raverma created
*/
Procedure eraseScores(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
P_TRUNCATE IN VARCHAR2, -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
P_SCORE_OBJECT_ID IN NUMBER ,
P_SCORE_OBJECT_CODE IN VARCHAR2 ,
-- begin bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
P_FROM_DATE IN varchar2 ,
P_TO_DATE IN varchar2 ,
-- end bug 4504193 by ctlee 2005/07/26 - update from date to varchar2
P_REQUEST_ID IN NUMBER ,
P_SAVE_LAST_RUN IN VARCHAR2,
P_BATCH_SIZE IN NUMBER)
IS
vPLSQL VARCHAR2(200);
vSelectCount Varchar2(1000) ;
vStr1 := 'SELECT SCORE_HISTORY_ID ' ;
v_del_sql := 'DELETE FROM IEX_SCORE_HISTORIES ' ;
vSelectCount := 'select count(1) from iex_score_histories ';
l_conditions(i).Value := '(SELECT trunc(MAX(creation_date)) FROM iex_Score_histories)'; -- Andre Fixed here so we use date only
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delete Filter Applied => '|| vPLSQL2);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'SELECT SCORE_HISTORY_ID ' ||
' FROM IEX_SCORE_HISTORIES ' ||
vPLSQL2);
vPLSQL := ' SELECT Count(1), Score_object_code ' ||
' FROM IEX_SCORE_HISTORIES ' ||
'GROUP BY SCORE_OBJECT_CODE ';
/* Removed by Andre 06/18/2004, we will need to delete in chunks
EXECUTE IMMEDIATE v_del_sql ;
vSelectCount := vSelectCount || vWhereClause;
open sql_cur for vSelectCount;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Final delete statement => '|| v_del_sql);
Select Count(1) into l_total
From IEX_SCORE_HISTORIES;
vstr1 := 'SELECT SCORE_HISTORY_ID, ';
'SELECT SCORE_HISTORY_ID, ' ||
' SCORE_ID, ' ||
' SCORE_VALUE, '||
' CREATION_DATE ' ||
' FROM IEX_SCORE_HISTORIES ' ||
vPLSQL;
IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeScoreHistory: Insert records!' );
select jtf_object_code into l_object_type
from iex_scores
where score_id = p_score_id;
insert into iex_score_histories(SCORE_HISTORY_ID
,SCORE_OBJECT_ID
,SCORE_OBJECT_CODE
,OBJECT_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,SCORE_VALUE
,SCORE_ID
,REQUEST_ID
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE)
values(IEX_SCORE_HISTORIES_S.nextval
,p_objects_tbl(n)
,l_object_type
,1
,sysdate
,l_user
,l_user
,sysdate
,l_user
,p_scores_tbl(n)
,p_score_id
,l_request
,l_program
,l_prog_appl
,sysdate);
IEX_DEBUG_PUB.logMessage('IEX_SCORE: storeDelBuffers: Insert records!' );
select jtf_object_code into l_object_type
from iex_scores
where score_id = p_score_id;
insert into IEX_DEL_BUFFERS(DEL_BUFFER_ID
,SCORE_OBJECT_ID
,SCORE_OBJECT_CODE
,OBJECT_VERSION_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,SCORE_VALUE
,SCORE_ID
,REQUEST_ID
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE)
values(IEX_DEL_BUFFERS_S.nextval
,p_objects_tbl(n)
,l_object_type
,1
,sysdate
,l_user
,l_user
,sysdate
,l_user
,p_scores_tbl(n)
,p_score_id
,nvl(l_bridge,0)
,l_program
,l_prog_appl
,sysdate);
l_execute_style VARCHAR2(1); -- are we using select or function call
SELECT Range_Low
BULK COLLECT INTO l_component_range_tbl
FROM iex_score_comp_det
where score_component_id = l_score_component_id
order by value;
l_component_range_tbl.delete;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_SCORE: get1Score: Exception selecting component detail range: WRONG ENGINE CONFIGURATION!!!!!');
vSql := 'SELECT upper(NEW_VALUE) ' ||
' FROM IEX_SCORE_COMP_DET ' ||
' WHERE SCORE_COMPONENT_ID = :p_score_comp_id AND ' ||
' :p_component_score >= RANGE_LOW AND ' ||
' :p_component_score <= RANGE_HIGH ';
vSql := 'SELECT ' || l_new_value || ' FROM DUAL';