[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_PaymentAmount( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_count in integer,
i_amountRanges in AmountRange_table )
is
i int;
delete from iby_irf_pmt_amount
where ((payeeid = i_payeeid)
or (i_payeeid is null and payeeid is null));
insert into iby_irf_pmt_amount
(lower_limit, upper_limit, score, seq,
payeeid, object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values ( l_lowerLimit, l_upperLimit, l_score, l_seq,
i_payeeid,1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'PMTAMOUNT' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select lower_limit, upper_limit, seq, score
from iby_irf_pmt_amount
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid))
order by seq;
select count(*)
from iby_irf_pmt_amount
where payeeid = ci_payeeid;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_TimeOfPurchase( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_count in integer,
i_timeRanges in TimeRange_table )
is
i int;
delete from iby_irf_timeof_purchase
where ( ( payeeid = i_payeeid ) or
( i_payeeid is null and payeeid is null ) );
insert into iby_irf_timeof_purchase
( duration_from ,duration_to, score, seq,
payeeid , object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values ( l_lowerLimit, l_upperLimit, l_score, l_seq,
i_payeeid,1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'TIMEOFPURCHASE' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select duration_from, duration_to, seq, score
from iby_irf_timeof_purchase
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid))
order by seq;
select count(*)
from iby_irf_timeof_purchase
where payeeid = ci_payeeid;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_TrxnAmountLimit( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_duration in integer,
i_durationType in VARCHAR2,
i_amount in number )
is
begin
-- update the transaction amount table.
-- this will be successful either payeeid is
-- null or the payeeid was configured some information.
update iby_irf_trxn_amt_limit
set duration = i_duration,
duration_type = i_durationType,
amount = i_amount,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where (( payeeid is null and i_payeeid is null ) or
( payeeid = i_payeeid));
insert into iby_irf_trxn_amt_limit
( duration, duration_type, amount,
payeeid, object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values (i_duration, i_durationType, i_amount,
i_payeeid,1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'TRXNAMOUNT' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select duration, duration_type, amount
from iby_irf_trxn_amt_limit
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
select count(*)
from iby_irf_trxn_amt_limit
where payeeid = ci_payeeid;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_PaymentHistory(i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_duration in integer,
i_durationType in VARCHAR2,
i_count in integer,
i_freqRanges in FreqRange_table )
is
i int;
select id
from iby_irf_pmt_history
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
update iby_irf_pmt_history
set duration = i_duration,
duration_type = i_durationType,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where (( payeeid is null and i_payeeid is null) or
( payeeid = i_payeeid));
SELECT iby_irf_pmt_history_s.nextval into l_pmt_hist_id
FROM dual;
insert into iby_irf_pmt_history (id, duration, duration_type,
payeeid, object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values ( l_pmt_hist_id, i_duration, i_durationType, i_payeeid,
1, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
delete from iby_irf_pmt_hist_range
where payment_hist_id = l_pmt_hist_id;
insert into iby_irf_pmt_hist_range
( payment_hist_id, frequency_low_range,
frequency_high_range, score, seq,
object_version_number,
last_update_date, last_updated_by,
creation_date, created_by)
values ( l_pmt_hist_id, l_lowerlimit, l_upperlimit,
l_score, l_seq,
1,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'PMTHISTORY' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select id, duration, duration_type
from iby_irf_pmt_history
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
select frequency_low_range, frequency_high_range, score, seq
from iby_irf_pmt_hist_range
where payment_hist_id = ci_id
order by seq;
select count(*)
from iby_irf_pmt_history
where payeeid = ci_payeeid;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_AVSCodes( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_count in integer,
i_codes in codes_table )
is
i int;
delete from iby_mappings
where (( payeeid = i_payeeid) or
( payeeid is null and i_payeeid is null ))
and mapping_type = 'AVS_CODE_TYPE';
insert into iby_mappings ( payeeid, mapping_type, mapping_code,
value, object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values ( i_payeeid, 'AVS_CODE_TYPE', l_code,
l_score,1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'AVSCODES' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select mapping_code, value
from iby_mappings
where mapping_type = 'AVS_CODE_TYPE'
and (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
select count(*)
from iby_mappings
where mapping_type = 'AVS_CODE_TYPE'
and payeeid = ci_payeeid;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_RiskCodes( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_count in integer,
i_codes in codes_table )
is
i int;
delete from iby_mappings
where (( payeeid = i_payeeid) or
( payeeid is null and i_payeeid is null ))
and mapping_type = 'RISK_CODE_TYPE';
insert into iby_mappings ( payeeid, mapping_type, mapping_code, value,
last_update_date, last_updated_by, creation_date, created_by,object_version_number)
values ( i_payeeid, 'RISK_CODE_TYPE', l_code, l_score,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,1);
raise_application_error(-20000, 'Unable insert RISK CODES ');
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'RISKCODES' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select mapping_code lookup_code,value
from iby_mappings
where mapping_type = 'RISK_CODE_TYPE' and
payeeid = ci_payeeid
UNION
select lookup_code,null
from fnd_lookup_values
where lookup_type = 'RISK_CODE' and
enabled_flag = 'Y' and
language = userenv('LANG') and
--security_group_id = fnd_global.lookup_security_group
--(lookup_type,view_application_id) and
lookup_code not in ( select mapping_code
from iby_mappings
where mapping_type = 'RISK_CODE_TYPE' and
payeeid = ci_payeeid);
select lookup_code
from
fnd_lookup_values
where
lookup_type = 'RISK_CODE' and
enabled_flag = 'Y' and
language = userenv('LANG');
select lookup_code
from iby_mappings a,
fnd_lookup_values b
where b.lookup_type = 'RISK_CODE' and
b.enabled_flag = 'N' and
b.lookup_code = a.mapping_code and
b.language = userenv('LANG');
select count(*)
from iby_mappings
where mapping_type = 'RISK_CODE_TYPE' and
payeeid = ci_payeeid;
delete from iby_mappings
where mapping_type = 'RISK_CODE_TYPE' and
mapping_code = i.lookup_code;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_CreditRatingCodes( i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_count in integer,
i_codes in codes_table )
is
i int;
delete from iby_mappings
where (( payeeid = i_payeeid) or
( payeeid is null and i_payeeid is null ))
and mapping_type = 'CREDIT_CODE_TYPE';
insert into iby_mappings ( payeeid, mapping_type, mapping_code, value,
last_update_date, last_updated_by, creation_date, created_by,object_version_number)
values ( i_payeeid, 'CREDIT_CODE_TYPE', l_code, l_score,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,1);
raise_application_error(-20000, 'Unable insert Credit Codes');
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'CREDITRATINGCODES' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select mapping_code lookup_code,value
from iby_mappings
where mapping_type = 'CREDIT_CODE_TYPE' and
payeeid = ci_payeeid
UNION
select lookup_code,null
from fnd_lookup_values
where lookup_type = 'CREDIT_RATING' and
enabled_flag = 'Y' and
language = userenv('LANG') and
--security_group_id = fnd_global.lookup_security_group
--(lookup_type,view_application_id) and
lookup_code not in ( select mapping_code
from iby_mappings
where mapping_type = 'CREDIT_CODE_TYPE' and
payeeid = ci_payeeid);
select lookup_code
from
fnd_lookup_values
where
lookup_type = 'CREDIT_RATING' and
enabled_flag = 'Y' and
language = userenv('LANG');
select lookup_code
from iby_mappings a,
fnd_lookup_values b
where b.lookup_type = 'CREDIT_RATING' and
b.enabled_flag = 'N' and
b.lookup_code = a.mapping_code and
b.language = userenv('LANG');
select count(*)
from iby_mappings
where mapping_type = 'CREDIT_CODE_TYPE' and
payeeid = ci_payeeid;
delete from iby_mappings
where mapping_type = 'CREDIT_CODE_TYPE' and
mapping_code = i.lookup_code;
** null then updates the site level configuration values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_FreqOfPurchase(i_payeeid in VARCHAR2,
i_name in VARCHAR2,
i_description in VARCHAR2,
i_duration in integer,
i_durationType in VARCHAR2,
i_frequency in integer )
is
begin
-- update the FreqOfPurchase information. If payeeid is not null
-- and payeeid does not have configured information then
-- insert the configuration information.
update iby_irf_pmt_frequency
set duration = i_duration,
duration_type = i_durationType,
frequency = i_frequency,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where ( ( payeeid is null and i_payeeid is null ) or
( payeeid = i_payeeid ));
insert into iby_irf_pmt_frequency ( duration, duration_type,
frequency, payeeid, object_version_number,
last_update_date, last_updated_by, creation_date, created_by)
values ( i_duration, i_durationType, i_frequency, i_payeeid,1,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id);
select meaning, description
from FND_LOOKUP_VALUES
where
lookup_code = 'FREQOFPURCHASE' and
lookup_type = 'IBY_RISK_FACTOR_NAME' and
language = userenv('LANG');
select duration, duration_type, frequency
from iby_irf_pmt_frequency
where (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
select count(*)
from iby_irf_pmt_frequency
where payeeid = ci_payeeid;
** null then updates the site level RiskScores values
** otherwise checks if payee already has some entries.
** if payee has entries then updates them otherwise
** creates new entries.
*/
procedure save_RiskScores( i_payeeid in VARCHAR2,
i_lowval in integer,
i_lowMedVal in integer,
i_medVal in integer,
i_medHighVal in integer,
i_highVal in integer )
is
begin
-- update the risk scores based on the payeeid.
update iby_mappings
set value = i_lowVal,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where mapping_code = 'L'
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and i_payeeid is null ) or
(payeeid = i_payeeid) );
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( 0, 'S', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( 0, 'NR', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( i_lowVal, 'L', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( i_lowMedVal, 'LM', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( i_medVal, 'M', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( i_medHighVal, 'MH', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
insert into iby_mappings( value, mapping_code, mapping_type, payeeid,
last_update_date, last_updated_by, creation_date, created_by, object_version_number)
values( i_highVal, 'H', 'IBY_RISK_SCORE_TYPE', i_payeeid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, 1);
update iby_mappings
set value = i_lowMedVal,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where mapping_code = 'LM'
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and i_payeeid is null ) or
(payeeid = i_payeeid) );
update iby_mappings
set value = i_medVal,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where mapping_code = 'M'
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and i_payeeid is null ) or
(payeeid = i_payeeid) );
update iby_mappings
set value = i_medHighVal,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where mapping_code = 'MH'
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and i_payeeid is null ) or
(payeeid = i_payeeid) );
update iby_mappings
set value = i_highVal,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where mapping_code = 'H'
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and i_payeeid is null ) or
(payeeid = i_payeeid) );
cursor c_insert_scores( ci_code in iby_mappings.mapping_code%type,
ci_payeeid varchar2)
is
select value
from iby_mappings
where mapping_code = ci_code
and mapping_type = 'IBY_RISK_SCORE_TYPE'
and (( payeeid is null and ci_payeeid is null ) or
( payeeid = ci_payeeid));
select count(*)
from iby_mappings
where mapping_type = 'IBY_RISK_SCORE_TYPE'
and payeeid = ci_payeeid;
if ( c_insert_scores%isopen ) then
close c_insert_scores;
open c_insert_scores('L', l_payeeid);
fetch c_insert_scores into o_lowVal;
close c_insert_scores;
open c_insert_scores('LM', l_payeeid);
fetch c_insert_scores into o_lowMedVal;
close c_insert_scores;
open c_insert_scores('M', l_payeeid);
fetch c_insert_scores into o_medVal;
close c_insert_scores;
open c_insert_scores('MH', l_payeeid);
fetch c_insert_scores into o_medHighVal;
close c_insert_scores;
open c_insert_scores('H', l_payeeid);
fetch c_insert_scores into o_highVal;
close c_insert_scores;