[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'
order by score_component_id;
|| 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;
select score.jtf_object_code
into l_jtf_obj_code
from iex_scores score
where score_id=p_score_comp_tbl(1).score_id;
select party_name
into l_object_name
from hz_parties
where party_id=t_object_ids(l_count);
select account_number
into l_object_name
from hz_cust_accounts
where cust_account_id=t_object_ids(l_count);
select hcsua.location,hca.account_number
into l_object_name,l_acct_number
from hz_cust_site_uses_all hcsua,hz_cust_acct_sites_all hcasa, hz_cust_accounts hca
where hcsua.cust_acct_site_id=hcasa.cust_acct_site_id
and hcasa.cust_account_id=hca.cust_account_id
and hcsua.site_use_code='BILL_TO'
and hcsua.site_use_id=t_object_ids(l_count);
select aps.trx_number,hca.account_number
into l_object_name,l_acct_number
from ar_payment_schedules_all aps,hz_cust_accounts hca
where aps.customer_id=hca.cust_account_id
and aps.payment_schedule_id=t_object_ids(l_count)
and aps.payment_schedule_id>0;
select jtf_object_code into t_object_code
from iex_scores
where score_id = l_score_comp_tbl(1).SCORE_ID;
l_objects_tbl.delete;
l_new_scores_tbl.delete;
l_scores_tbl.delete;
select jtf_object_code into t_object_code
from iex_scores
where score_id = l_score_comp_tbl(1).SCORE_ID;
l_scores_tbl.delete;
/*select score_name
into l_score_name
from iex_scores
where score_id=p_score_id;
select jtf_object_code into l_object_type
from iex_scores
where score_id = p_score_id;*/
SELECT score.score_name,
score.jtf_object_code,
obj.object_filter_name,
obj.entity_name,
score.concurrent_prog_name,
score.status_determination,
score.score_range_low,
score.score_range_high
into l_score_name,l_object_code,l_obj_filter_name,l_obj_filter_view,
l_cp_name,l_sts_det,l_score_low,l_score_high
FROM iex_scores score,
iex_object_filters obj
WHERE score.score_id =obj.object_id
AND obj.object_filter_type='IEXSCORE'
and score.score_id=p_score_id;
SELECT NAME
into l_object_type
FROM jtf_objects_vl
where object_code=l_object_code;
SELECT sc_typ_tl.score_comp_name,
sc_typ.function_flag,
sc.score_comp_weight,
sc_typ.score_comp_value
into l_score_comp_name,l_function_flg,l_score_comp_wgt,l_score_comp_val
FROM iex_score_components sc,
iex_score_comp_types_tl sc_typ_tl,
iex_score_comp_types_b sc_typ
WHERE sc.score_comp_type_id = sc_typ.score_comp_type_id
AND sc_typ.score_comp_type_id= sc_typ_tl.score_comp_type_id
AND sc_typ_tl.language ='US'
AND sc.score_component_id=l_components_tbl(comp).score_component_id;
select count(*)
into l_cnt
from iex_score_comp_det
where score_component_id=l_components_tbl(comp).score_component_id;
select range_low,range_high,new_value
bulk collect into l_sc_range_low,l_sc_range_high,l_sc_val
from iex_score_comp_det sc_det
where score_component_id=l_components_tbl(comp).score_component_id;
Select IEX_DEL_WF_S.NEXTVAL INTO l_del_buff_bridge FROM Dual; --Added by schekuri for bug#6373998 on 31-Aug-2007
l_universe.delete;
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 TO_CHAR(sysdate,'YYYY-MM-DD')
into l_sysdate
from dual;
select security_profile_name into l_pf_name from per_security_profiles
where security_profile_id = l_pf_value;
select name
into l_org_id
from hr_operating_units
where organization_id=p_org_id;
select score_name into l_score_name1 from iex_scores where score_id=p_score_id1;
select score_name into l_score_name2 from iex_scores where score_id=p_score_id2;
select score_name into l_score_name3 from iex_scores where score_id=p_score_id3;
select score_name into l_score_name4 from iex_scores where score_id=p_score_id4;
select score_name into l_score_name5 from iex_scores where score_id=p_score_id5;
|| 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_org_id IN NUMBER,
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 ;
EXECUTE IMMEDIATE 'select IEX_SCORE_HISTORIES_S.NEXTVAL +1 FROM DUAL' into v_num;
EXECUTE IMMEDIATE 'select IEX_SCORE_HISTORIES_S.NEXTVAL FROM DUAL' into v_num;
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
,ORG_ID)
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
,l_org_id);
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!!!!!');
select sctl.score_comp_name,sc.score_comp_weight
into l_score_comp_name,l_score_comp_wtg
from iex_score_components sc, iex_score_comp_types_tl sctl
where sc.score_comp_type_id=sctl.score_comp_type_id
and sc.score_component_id=l_score_component_id
and sctl.language='US';
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';
SELECT
SCORE_COMP_VALUE,
NVL(FUNCTION_FLAG, 'N') FUNCTION_FLAG
into l_scorecomp_exe,l_function_flag
FROM
IEX_SCORE_COMP_TYPES_VL
WHERE score_comp_type_id=p_scorecomp_id
AND NVL(METRIC_FLAG, 'N') = 'N';