[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_item IGS_RU_DESCRIPTION.rule_description%TYPE,
description VARCHAR2(2000),
selectable VARCHAR2(1) );
when delete rule, keep rule numbers to reuse
*/
gt_rule_numbers t_number;
when delete rule, keep set numbers to reuse
*/
gt_set_numbers t_number;
table of selectable items
*/
gt_rule_LOV t_LOV;
selected value in LOV's
*/
gv_prev VARCHAR2(100);
gv_select_count NUMBER := 0;
p_selectable IN VARCHAR2)
IS
BEGIN DECLARE
v_rule_length NUMBER;
gt_rule_LOV(gv_LOV_index).select_item := REPLACE(p_string,fnd_global.local_chr(10));
gt_rule_LOV(gv_LOV_index).selectable := p_selectable;
v_selectable IGS_RU_LOV.selectable%TYPE;
SELECT description,
selectable
INTO v_description,
v_selectable
FROM IGS_RU_LOV
WHERE sequence_number = p_LOV_number;
IF v_selectable = 'Y'
THEN
RETURN v_description;
PROCEDURE insert_LOV_tab
IS
X_ROWID VARCHAR2(25);
SELECT ROWID, rl.*
FROM IGS_RU_LOV rl
WHERE SEQUENCE_NUMBER = p_LOV_number ;
SELECT IGS_RU_LOV_SEQ_NUM_S.nextval
INTO p_LOV_number
FROM DUAL;
IGS_RU_LOV_PKG.DELETE_ROW (X_ROWID => C_RL_REC.ROWID );
IGS_RU_LOV_PKG.INSERT_ROW (
X_ROWID => X_ROWID,
X_SEQUENCE_NUMBER => p_LOV_number,
X_DESCRIPTION => LTRIM(RTRIM(gt_rule_LOV(v_index).select_item)),
X_HELP_TEXT => LTRIM(RTRIM(gt_rule_LOV(v_index).description)),
X_SELECTABLE => LTRIM(RTRIM(gt_rule_LOV(v_index).selectable)) );
UPDATE igs_ru_lov
SET help_text = LTRIM(RTRIM(gt_rule_LOV(v_index).description))
WHERE sequence_number = p_LOV_number
AND description = LTRIM(RTRIM(gt_rule_LOV(v_index).select_item));
END insert_LOV_tab;
SELECT igs_ru_rule_seq_num_s.NEXTVAL
FROM DUAL;
SELECT (sequence_number + 1) sequence_number
FROM igs_ru_rule
WHERE sequence_number =
(SELECT MAX (sequence_number)
FROM igs_ru_rule
WHERE sequence_number < 499999)
FOR UPDATE OF sequence_number NOWAIT;
use deleted rule number
*/
v_rule_number := gt_rule_numbers(gv_rn_index);
IGS_RU_RULE_PKG.INSERT_ROW (
X_ROWID => X_ROWID ,
X_SEQUENCE_NUMBER => V_RULE_NUMBER );
insert new set
*/
FUNCTION new_set (
p_set_type IN IGS_RU_SET.set_type%TYPE )
RETURN NUMBER IS
BEGIN DECLARE
v_set_number NUMBER;
SELECT igs_ru_set_seq_num_s.NEXTVAL
FROM DUAL;
SELECT (sequence_number + 1) sequence_number
FROM igs_ru_set
WHERE sequence_number =
(SELECT MAX (sequence_number)
FROM igs_ru_set
WHERE sequence_number < 499999)
FOR UPDATE OF sequence_number NOWAIT;
use deleted set number
*/
v_set_number := gt_set_numbers(gv_sn_index);
IGS_RU_SET_PKG.INSERT_ROW (
X_ROWID => X_ROWID,
X_SEQUENCE_NUMBER => v_set_number,
X_SET_TYPE => p_set_type );
gv_select_count := 0;
/* count the number of units selected, remember their might be wildcards*/
SELECT count(*)
INTO v_count
FROM IGS_PS_UNIT_VER
/*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1)); --Bug 2395891/2543627 --space not accepted in user defined rules*/
check if to many selected units (1000)
*/
SELECT count(*)
INTO v_max_count
FROM IGS_PS_UNIT_VER
/*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'; --Bug 2395891/2543627 --space not accepted in user defined rules*/
'*** To many units selected. ***',
'Suggest you restrict the select criteria further.',
'N');
SELECT unit_cd,
version_number,
unit_status,
short_title
FROM IGS_PS_UNIT_VER
/*WHERE unit_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%' --Bug 2395891/2543627 --space not accepted in user defined rules*/
WHERE unit_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%' --nshee
ORDER BY unit_cd,version_number DESC )
LOOP
v_no_units := FALSE;
gv_select_count := v_ii - 1;
count the number of UNIT sets selected, remember their might be wildcards
*/
SELECT count(*)
INTO v_count
FROM IGS_EN_UNIT_SET
/* WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1)); commented by nshee as part of fix of bug 2381638 and 2395891 and added the next line */
check if to many selected set members (1000)
*/
SELECT count(*)
INTO v_max_count
FROM IGS_EN_UNIT_SET
/*WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%';--Bug 2395891/2543627 --space not accepted in user defined rules*/
'*** To many unit sets selected. ***',
'Suggest you restrict the select criteria further.',
'N');
SELECT unit_set_cd,
version_number,
unit_set_status,
short_title
FROM IGS_EN_UNIT_SET
/*WHERE unit_set_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'--Bug 2395891/2543627 --space not accepted in user defined rules*/
WHERE unit_set_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%'--nshee
ORDER BY unit_set_cd,version_number DESC )
LOOP
v_no_units := FALSE;
count the number of members selected, remember their might be wildcards
*/
SELECT count(*)
INTO v_count
FROM IGS_PS_VER
/*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1));--Bug 2395891/2543627 --space not accepted in user defined rules*/
check if to many selected set members (1000)
*/
SELECT count(*)
INTO v_max_count
FROM IGS_PS_VER
/*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%';--Bug 2395891/2543627 --space not accepted in user defined rules*/
'*** To many courses selected. ***',
'Suggest you restrict the select criteria further.',
'N');
SELECT course_cd,
version_number,
course_status,
short_title
FROM IGS_PS_VER
/*WHERE course_cd LIKE UPPER(SUBSTR(p_rule,1,v_ii - 1))||'%'--Bug 2395891/2543627 --space not accepted in user defined rules*/
WHERE course_cd LIKE UPPER(SUBSTR(REPLACE(p_rule,cst_spacemod,' '),1,v_ii - 1))||'%'--nshee
ORDER BY course_cd,version_number DESC )
LOOP
v_no_members := FALSE;
insert set member
*/
PROCEDURE insert_set_member(
p_set_number IN IGS_RU_SET.sequence_number%TYPE,
p_unit IN VARCHAR2)
IS
v_unit VARCHAR2(200);
SELECT ROWID, rsmbr.*
FROM IGS_RU_SET_MEMBER rsmbr
WHERE rs_sequence_number = p_set_number
AND unit_cd = v_unit_cd;
IGS_RU_SET_MEMBER_PKG.INSERT_ROW (
X_ROWID => X_ROWID,
X_RS_SEQUENCE_NUMBER => p_set_number,
X_UNIT_CD => v_unit_cd,
X_VERSIONS => v_versions );
SELECT versions
INTO v_current_versions
FROM IGS_RU_SET_MEMBER
WHERE rs_sequence_number = p_set_number
AND unit_cd = v_unit_cd;
IGS_RU_SET_MEMBER_PKG.UPDATE_ROW (
X_ROWID => C_RSMBR_REC.ROWID,
X_RS_SEQUENCE_NUMBER => C_RSMBR_REC.RS_SEQUENCE_NUMBER,
X_UNIT_CD => C_RSMBR_REC.UNIT_CD ,
X_VERSIONS => v_new_versions );
END insert_set_member;
SELECT s_return_type
FROM IGS_RU_RET_TYPE
WHERE s_return_type LIKE SUBSTR(p_description,v_hash + 1,1)||'%'
ORDER BY s_return_type DESC )
LOOP
IF SUBSTR(p_description,v_hash + 1,LENGTH(return_types.s_return_type))
= return_types.s_return_type
THEN
p_action := return_types.s_return_type;
cascade delete RULE items
save RULE and set numbers
*/
PROCEDURE delete_rule_items(
p_rule_number IN IGS_RU_RULE.sequence_number%TYPE,
p_item IN IGS_RU_ITEM.item%TYPE)
IS
CURSOR c_rule_items IS
SELECT rowid, ri.*
FROM IGS_RU_ITEM ri
WHERE rul_sequence_number = p_rule_number
AND item >= p_item ;
SELECT rowid, rule.*
FROM IGS_RU_RULE rule
WHERE sequence_number = p_rule_number;
SELECT rowid, rsmbr.*
FROM IGS_RU_SET_MEMBER rsmbr
WHERE rs_sequence_number =p_set_number;
SELECT rowid, rs.*
FROM IGS_RU_SET rs
WHERE sequence_number = p_set_number;
IGS_RU_ITEM_PKG.DELETE_ROW (
X_ROWID => C_RULE_ITEMS_REC.ROWID );
delete_rule_items(c_rule_items_rec.rule_number,0);
IGS_RU_RULE_PKG.DELETE_ROW (X_ROWID => C_RULE_REC.ROWID );
IGS_RU_SET_MEMBER_PKG.DELETE_ROW (X_ROWID => C_RULE_SET_MBR_REC.ROWID);
IGS_RU_SET_PKG.DELETE_ROW (X_ROWID => C_RULE_SET_REC.ROWID );
END delete_rule_items;
build and insert RULE item
increment item if used
*/
PROCEDURE make_rule_item(
p_from IN VARCHAR2,
p_rule_num IN NUMBER,
p_item IN OUT NOCOPY NUMBER,
p_turin_function IN IGS_RU_ITEM.turin_function%TYPE,
p_rud_seq_num IN IGS_RU_DESCRIPTION.sequence_number%TYPE,
p_rule_number IN IGS_RU_ITEM.rule_number%TYPE,
p_set_number IN IGS_RU_ITEM.set_number%TYPE,
p_value IN IGS_RU_ITEM.value%TYPE)
IS
BEGIN DECLARE
v_named_rule IGS_RU_ITEM.named_rule%TYPE;
delete_rule_items(p_rule_num,p_item);
SELECT rul_sequence_number
INTO v_named_rule
FROM IGS_RU_NAMED_RULE
WHERE rud_sequence_number = p_rud_seq_num;
SELECT rule_description
INTO v_rule_description
FROM IGS_RU_DESCRIPTION
WHERE sequence_number = p_rud_seq_num;
IGS_RU_ITEM_PKG.INSERT_ROW (
X_ROWID => X_ROWID,
X_RUL_SEQUENCE_NUMBER => p_rule_num,
X_ITEM => p_item,
X_TURIN_FUNCTION => p_turin_function,
X_NAMED_RULE => v_named_rule,
X_RULE_NUMBER => p_rule_number,
X_SET_NUMBER => p_set_number,
X_VALUE => v_value,
X_DERIVED_RULE => NULL );
from an SQL select string match value or create a list of values
*/
FUNCTION do_LOV (
p_rule IN OUT NOCOPY VARCHAR2,
p_rule_number IN NUMBER,
p_item IN OUT NOCOPY NUMBER,
p_select_string IN VARCHAR2 )
RETURN BOOLEAN IS
v_select_string VARCHAR2(2000);
replace previously selected field(s) with their values
*/
v_select_string := REPLACE(p_select_string,'$PREV',gv_prev);
v_select_string := REPLACE(v_select_string,'$PPREV',gv_pprev);
DBMS_SQL.PARSE(v_cursor,v_select_string||' ORDER BY 1 DESC',dbms_sql.native);
add selected value
*/
make_rule_item('do_LOV',p_rule_number,p_item,
NULL,NULL,NULL,NULL,
v_value);
set previous selected values
*/
gv_pprev := gv_prev;
'*** No values selected ***',
'ERROR:No values selected while attempting '||
'match from a database defined LOV''s.',
'N');
from an SQL select string match value, create set member or
create a list of values
*/
FUNCTION make_set (
p_rule IN OUT NOCOPY VARCHAR2,
p_rule_number IN NUMBER,
p_item IN OUT NOCOPY NUMBER,
p_select_string IN VARCHAR2 )
RETURN BOOLEAN IS
v_select_string VARCHAR2(2000);
replace previously selected field(s) with their values
*/
v_select_string := REPLACE(p_select_string,'$PREV',gv_prev);
v_select_string := REPLACE(v_select_string,'$PPREV',gv_pprev);
DBMS_SQL.PARSE(v_cursor,v_select_string||' ORDER BY 1 DESC',dbms_sql.v7);
'*** No values selected ***',
'ERROR:No values selected while attempting '||
'match from a database defined LOV''s.',
'N');
select all descriptions where the first description item <> return type
*/
FOR rule_descriptions IN (
SELECT RUD.sequence_number,
s_turin_function,
rule_description,
description
FROM IGS_RU_DESCRIPTION RUD,
IGS_RU_GROUP_SET RGS
WHERE s_return_type = p_type
AND rule_description NOT LIKE '#'||p_type||'%'
AND RUD.sequence_number = RGS.rud_sequence_number
AND RGS.rug_sequence_number = p_group
ORDER BY rule_description DESC )
LOOP
v_rule_description := rule_descriptions.rule_description;
do list of values select thingo
*/
IF v_action = '[LOV]'
THEN
RETURN do_LOV(p_rule,p_rule_number,
p_item,v_rule_description);
insert_set_member(gv_set_number,
SUBSTR(v_rule,1,LENGTH(v_rule) - LENGTH(p_rule)));
select all descriptions where the first description item = return type
*/
FOR rule_descriptions IN (
SELECT RUD.sequence_number,
s_turin_function,
rule_description,
description
FROM IGS_RU_DESCRIPTION RUD,
IGS_RU_GROUP_SET RGS
WHERE s_return_type = p_type
AND rule_description LIKE '#'||p_type||'%'
AND RUD.sequence_number = RGS.rud_sequence_number
AND RGS.rug_sequence_number = p_group
ORDER BY rule_description DESC )
LOOP
v_rule_description := rule_descriptions.rule_description;
v_unit := UPPER(REPLACE(v_unit,cst_spacemod,' '));added this line by nshee as fix for bug 2395891 to insert back the space at the right places to validate with the DB record.
1) remove the following line in the procedure unit insert_set_member.
v_unit := UPPER(REPLACE(v_unit,cst_spacemod,' '));added this line by nshee as fix for bug 2395891 to insert back the space at the right places to validate with the DB record.
SELECT ROWID, rnr.*
FROM IGS_RU_NAMED_RULE rnr
WHERE rul_sequence_number = p_rule_number;
loop until more than 1 item to select
*/
LOOP
IF p_generate_rule = TRUE
THEN
gv_rn_index := 0;
delete_rule_items(p_rule_number,0);
build select from list
*/
insert_LOV_tab;
+ 1 /* + gv_select_count */);
update RULE text if it exists
*/
v_rule := IGS_RU_GEN_006.rulp_get_rule(p_rule_number);
IGS_RU_NAMED_RULE_PKG.UPDATE_ROW (
X_ROWID => C_RNR_REC.ROWID,
X_RUL_SEQUENCE_NUMBER => C_RNR_REC.RUL_SEQUENCE_NUMBER,
X_RUD_SEQUENCE_NUMBER => C_RNR_REC.RUD_SEQUENCE_NUMBER,
X_MESSAGE_RULE => C_RNR_REC.MESSAGE_RULE,
X_RUG_SEQUENCE_NUMBER => C_RNR_REC.RUG_SEQUENCE_NUMBER,
X_RULE_TEXT => v_rule );