[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function execute_update(p_stmt_str in varchar2, p_valid_only in boolean) return number;
procedure execute_select(c in out nocopy t_gen_cur,
p_stmt_str in out nocopy varchar2,
p_valid_only in boolean);
return execute_update( 'update ap_credit_card_trxns_all cc '||
'set org_id = (select org_id from ap_card_programs_all apcp where apcp.card_program_id = cc.card_program_id) '||
'where cc.org_id is null' , p_valid_only );
return execute_update( 'update ap_credit_card_trxns_all cc '||
'set request_id = ' || to_char(p_request_id) ||
'where cc.request_id is null' , l_valid_only );
stmt := 'select distinct cp.card_program_id, cp.card_exp_type_map_type_code, cp.card_exp_type_source_col '||
'from ap_credit_card_trxns_all cc, ap_card_programs_all cp '||
'where cc.card_program_id = cp.card_program_id ';
execute_select(c, stmt, p_valid_only);
l_stmt := 'update ap_credit_card_trxns_all cc '||
'set folio_type = ap_map_types_pkg.get_map_to_code(:r0, cc.'||l_column_name||') '||
'where cc.card_program_id = :r1';
l_count := execute_update('update ap_credit_card_trxns_all cc '||
'set merchant_country = nvl(merchant_country, ap_web_locations_pkg.default_country(card_program_id)) '||
'where merchant_country is null ', p_valid_only);
l_count := execute_update( 'update ap_credit_card_trxns_all cc '||
'set merchant_country_code = ' ||
'(select ap_map_types_pkg.get_map_to_code(c.country_map_type_code, cc.merchant_country) '||
' from ap_card_programs_all c '||
' where c.card_program_id = cc.card_program_id) '||
'where 1=1', p_valid_only );
num_rows := execute_update(
' update ap_credit_card_trxns_all cc
set payment_flag = ''Y''
where (mis_industry_code = ''PA''
or transaction_type = ''PAYMENTS''
or (upper(description) like ''%PAYMENT%'' and
(upper(description) not like ''%FEE%''
and upper(description) not like ''%REVERSAL%''
and upper(description) not like ''%ADJUSTMENT%''
and upper(description) not like ''%CREDIT%''
and upper(description) not like ''%DEBIT%''))
or transaction_type in (''0108'',''0440'')) ', p_valid_only);
num_rows := execute_update(
'update ap_credit_card_trxns_all cc
set cc.billed_currency_code =
(select currency_code
from ap_card_currencies
where numeric_currency_code = cc.billed_currency_code)
where cc.billed_currency_code is not null
and cc.billed_currency_code not in
(select fndcvl.currency_code
from fnd_currencies_vl fndcvl
where fndcvl.enabled_flag = ''Y''
and fndcvl.currency_flag = ''Y''
and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate))
and exists
(select currency_code
from ap_card_currencies
where numeric_currency_code = cc.billed_currency_code)', p_valid_only
);
num_rows := num_rows + execute_update(
'update ap_credit_card_trxns_all cc
set cc.billed_currency_code =
(select cp.card_program_currency_code
from ap_card_programs_all cp
where cp.card_program_id = cc.card_program_id)
where billed_currency_code is null', p_valid_only
);
num_rows := num_rows + execute_update(
'update ap_credit_card_trxns_all cc
set cc.posted_currency_code =
(select currency_code
from ap_card_currencies
where numeric_currency_code = cc.posted_currency_code)
where cc.posted_currency_code is not null
and exists
(select currency_code
from ap_card_currencies
where numeric_currency_code = cc.posted_currency_code)', p_valid_only
);
num_rows := num_rows + execute_update(
'update ap_credit_card_trxns_all cc
set cc.posted_currency_code =
(select cp.card_program_currency_code
from ap_card_programs_all cp
where cp.card_program_id = cc.card_program_id)
where posted_currency_code is null', p_valid_only
);
stmt := 'select *
from ap_credit_card_trxns_all cc
where location_id is null '||g_where_clause
|| ' for update of location_id nowait';
execute_select(l_loc_cur, stmt, p_valid_only);
update ap_credit_card_trxns_all set location_id = l_cc_trx.location_id
where trx_id = l_cc_trx.trx_id;
return execute_update(
' update ap_credit_card_trxns_all cc
set payment_due_from_code = (select payment_due_from_code
from ap_card_programs_all cp
where cp.card_program_id = cc.card_program_id)
where payment_due_from_code is null ', p_valid_only);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''DUPLICATE_TRANSACTION''
where exists
(select ''A corresponding transaction exists with this reference number''
from ap_credit_card_trxns_all cc2
where cc.reference_number = cc2.reference_number
and cc.trx_id <> cc2.trx_id
and cc.card_id = cc2.card_id
and cc.card_program_id = cc2.card_program_id)', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_BILL_AMOUNT''
where (billed_amount is null
or billed_amount = 0)', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_BILL_CURR_CODE''
where billed_currency_code is not null
and not exists
(select ''A corresponding currency exists in FND_CURRENCIES''
from fnd_currencies_vl fndcvl
where fndcvl.enabled_flag = ''Y''
and fndcvl.currency_flag = ''Y''
and trunc(nvl(fndcvl.start_date_active, sysdate)) <= trunc(sysdate)
and trunc(nvl(fndcvl.end_date_active, sysdate)) >= trunc(sysdate)
and fndcvl.currency_code = cc.billed_currency_code)', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_BILL_DATE''
where billed_date is null', p_valid_only
);
num_rows := execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INACTIVE_CARD_NUMBER''
where cc.transaction_date >=
(select max(nvl(apc.inactive_date,cc.transaction_date+1))
from ap_cards_all apc
where apc.card_program_id = cc.card_program_id
and apc.card_id = cc.card_id)', p_valid_only
);
num_rows := execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_CARD_NUMBER''
where card_id not in
(select apc.card_id from ap_cards_all apc
where apc.card_program_id = cc.card_program_id
and apc.card_id = cc.card_id)', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_MERCH_NAME''
where (merchant_name1 is null
and merchant_name2 is null)
and ( (transaction_type in (''11'',''20'',''22'',''80'')
and sic_code <> ''6012''
and (card_program_id in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''Visa'')))
or
(merchant_activity <> ''A''
and (card_program_id in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''MasterCard'')))
or
(mis_industry_code <> ''SP''
and transaction_type <> ''05''
and card_program_id in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''American Express''))
or
card_program_id not in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code in (''American Express'',''Visa'',''MasterCard''))
)
', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_POST_CURR_CODE''
where posted_currency_code is not null
and posted_currency_code not in (select currency_code from ap_card_currencies) ', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_TRX_AMOUNT''
where (transaction_amount is null
or transaction_amount = 0)', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_TRX_DATE''
where transaction_date is null
and ((mis_industry_code <> ''PA''
and card_program_id in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''American Express''))
or
card_program_id not in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''American Express'')
)
', p_valid_only
);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''Y''
where validate_code = ''UNTESTED''', true);
return execute_update(
'update ap_credit_card_trxns_all cc
set validate_code = ''INVALID_SIC_CODE''
where card_program_id in (select card_program_id
from ap_card_programs_all
where card_brand_lookup_code = ''Visa'')
and ((sic_code is null
and transaction_type in (''10'',''11'',''20'',''22'',''80''))
or
(transaction_type in (''20'',''22'',''80'')
and sic_code not in (''6010'', ''6011'', ''6012'', ''6050'', ''6051''))
)', p_valid_only
);
select full_name into l_full_name from ap_card_details where card_id = p_card_id;
select substrb(full_name, 1, 80) into l_full_name
from per_employees_x pap, financials_system_parameters fsp
where pap.business_group_id = fsp.business_group_id
and pap.employee_id = p_employee_id;
delete from ap_card_details where card_id = p_card_id;
update ap_cards_all
set employee_id = p_employee_id,
cardmember_name = p_full_name,
physical_card_flag = 'Y',
paper_statement_req_flag = 'N'
where card_id = p_card_id
and employee_id is null;-- bug 5224047
select card_reference_id into l_instrid
from ap_cards_all
where card_id = p_card_id;
select party_id into l_party_id
from per_people_f ppf
where ppf.person_id = p_employee_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and rownum = 1;
iby_fndcpt_setup_pub.update_card(1.0,NULL,'F',x_return_status,x_msg_count,x_msg_data,
p_card_instrument, x_response);
delete from ap_card_emp_candidates where card_id = p_card_id;
return execute_update( 'update ap_credit_card_trxns_all cc '||
'set validate_code = ''UNTESTED'' '||
'where validate_code <> ''Y'' ' ||
'and nvl(category,''BUSINESS'') <> ''DEACTIVATED'' ', false );
function execute_update(p_stmt_str in varchar2, p_valid_only in boolean) return number is
l_validate_where varchar2(50) := null;
module varchar2(50) := 'ap.oie_cc_validations_pkg.execute_update';
end execute_update;
procedure execute_select(c in out nocopy t_gen_cur,
p_stmt_str in out nocopy varchar2,
p_valid_only in boolean) is
l_validate_where varchar2(50) := null;
end execute_select;
return execute_update(
-- Bug 3313557: Replaced TRXN alias with CC, so that it is compatible
-- with the rest of the dynamic SQL.
'UPDATE AP_CREDIT_CARD_TRXNS_ALL CC
SET CATEGORY = ''PERSONAL''
WHERE CATEGORY <> ''PERSONAL''
AND EXISTS (SELECT 1
FROM AP_CARDS_ALL CARD,
PER_EMPLOYEES_X P
WHERE AP_WEB_DB_HR_INT_PKG.IsPersonTerminated(CARD.employee_id)=''Y''
AND CARD.employee_id=P.employee_id
AND P.inactive_date < CC.transaction_date
AND CARD.card_program_id=CC.card_program_id
AND CARD.card_id=CC.card_id)', p_valid_only --
);
return execute_update(
'update ap_credit_card_trxns_all cc
set trxn_detail_flag = NULL
where trxn_detail_flag = ''Y''
and abs(transaction_amount) <
(select abs(sum(transaction_amount))
from ap_cc_trx_details c
where c.trx_id = cc.trx_id)', true);
return execute_update( 'update ap_credit_card_trxns_all cc ' ||
'set merchant_name1 = (' ||
'select card_program_name ' ||
'from ap_card_programs_all apcp ' ||
'where apcp.card_program_id = cc.card_program_id and rownum = 1) ' ||
'where merchant_name1 is null ' ||
'and merchant_name2 is null ' ||
'and ( '||
'(transaction_type in (''01'',''02'',''03'',''06'',''08'',''09'',''10'',''11'',''12'') '||
'and (card_program_id in (select card_program_id '||
'from ap_card_programs_all where card_brand_lookup_code = ''American Express''))) '||
'or '||
'(merchant_activity = ''A'' '||
'and (card_program_id in (select card_program_id '||
'from ap_card_programs_all where card_brand_lookup_code = ''MasterCard'')))'||
') ',
p_valid_only );
function delete_invalid_rows(p_valid_only in boolean, card_program_id in number ) return number is
num_rows number;
num_rows := execute_update('delete ap_credit_card_trxns_all cc where card_id not in
(select card_id
from ap_cards_all apc
where apc.card_program_id = cc.card_program_id and
apc.card_id = cc.card_id)
and cc.card_number is null
and cc.validate_code != ''Y''' || card_prog_where, p_valid_only);
end delete_invalid_rows;