The following lines contain the word 'select', 'insert', 'update' or 'delete':
update ap_expense_feed_lines efl
set reject_code = 'UNTESTED'
where card_program_id = P_CARD_PROGRAM_ID
and posted_date between nvl(P_START_DATE, posted_date - 1) and
nvl(P_END_DATE, posted_date + 1)
and not exists
(select 'A distribution exists for this expense feed line'
from ap_expense_feed_dists efd
where efd.feed_line_id = efl.feed_line_id);
/* update ap_expense_feed_lines efl
set card_id =
(select c.card_id
from ap_cards c
where c.card_number = efl.card_number)
where card_id is null
and reject_code = 'UNTESTED';*/
for i in (select card_number,feed_line_id
from ap_expense_feed_lines where reject_code='UNTESTED'
)
loop
iby_fndcpt_setup_pub.card_exists(1.0,NULL,
x_return_status, x_msg_count, x_msg_data,
null ,trim(i.card_number), -- party id is null as we reference cards through ap_cards_all.employee_id
p_card_instrument, x_response);
update ap_expense_feed_lines efl
set reject_code = 'INVALID CARD NUM'
where feed_line_id=i.feed_line_id;
select card_id into l_card from
ap_cards where card_reference_id=x_instr_id and rownum=1;
update ap_expense_feed_lines
set
card_id=l_card
where
feed_line_id=i.feed_line_id;
update ap_expense_feed_lines efl
set reject_code = 'INVALID CARD NUM'
where feed_line_id=i.feed_line_id;
update ap_expense_feed_lines efl
set reject_code = 'INVALID CARD NUM'
where feed_line_id=i.feed_line_id;
update ap_expense_feed_lines
set
card_number=-1
where
card_id is not null and reject_code='UNTESTED';
l_debug_info := 'Update employee_id on line ';
update ap_expense_feed_lines efl
set employee_id =
(select c.employee_id
from ap_cards c
where c.card_id = efl.card_id)
where card_id is not null
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'DUPLICATE REFERENCE'
where exists
(select 'A corresponding line already exists with this reference number'
from ap_expense_feed_lines efl2
where efl.reference_number = efl2.reference_number
and efl.feed_line_id <> efl2.feed_line_id
and efl2.card_program_id = P_CARD_PROGRAM_ID)
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'INVALID POST CURR'
where posted_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 = efl.posted_currency_code)
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set posted_currency_code =
(select cp.card_program_currency_code
from ap_card_programs cp
where cp.card_program_id = efl.card_program_id)
where posted_currency_code is null
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'INVALID TRX CURR'
where 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 = efl.posted_currency_code)
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'INVALID CARD CODE'
where not exists
(select 'A corresponding card code exists in AP_CARD_CODES'
from ap_card_codes cc,
ap_card_programs cp
where cc.code_value = efl.card_code_value
and cc.code_set_id = cp.card_code_set_id
and cp.card_program_id = P_CARD_PROGRAM_ID)
and nvl(create_distribution_flag,'N') = 'Y'
and exists
(select 'Profile mandates building account from card code'
from ap_card_profiles cp,
ap_cards c
where cp.profile_id = c.profile_id
and c.card_id = efl.card_id
and nvl(cp.build_acct_from_code_flag,'N') = 'Y')
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'DIST REQUIRED'
where exists
(select 'Employee verification or manager approval required'
from ap_card_profiles cp,
ap_cards c
where cp.profile_id = c.profile_id
and c.card_id = efl.card_id
and (cp.emp_notification_lookup_code = 'Y' OR
cp.mgr_approval_lookup_code = 'Y'))
and nvl(create_distribution_flag,'N') <> 'Y'
and reject_code = 'UNTESTED';
update ap_expense_feed_lines
set reject_code = ''
where card_program_id = P_CARD_PROGRAM_ID
and posted_date between nvl(P_START_DATE, posted_date - 1) and
nvl(P_END_DATE, posted_date + 1)
and reject_code = 'UNTESTED';
select efl.feed_line_id,
efl.amount,
efl.card_code_value,
nvl(cpr.exception_clearing_ccid,cpg.exception_clearing_ccid),
nvl(cpr.build_acct_from_code_flag,'N'),
cpr.default_acct_template,
hremp.default_code_combination_id,
cpg.card_code_set_id,
decode(cpr.mgr_approval_lookup_code,
'Y',decode(cpr.emp_notification_lookup_code,
'Y','VALIDATED',
'I','VALIDATED',
'VERIFIED'),
'I',decode(cpr.emp_notification_lookup_code,
'Y','VALIDATED',
'I','VALIDATED',
'VERIFIED'),
decode(cpr.emp_notification_lookup_code,
'Y','VALIDATED',
'I','VALIDATED',
'APPROVED')),
efl.description
from ap_expense_feed_lines efl,
ap_card_programs cpg,
ap_card_profiles cpr,
ap_cards c,
hr_employees_current_v hremp,
IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY
where efl.card_id = c.card_id
and c.card_reference_id=IBY.instrument_id
and c.profile_id = cpr.profile_id
and cpr.card_program_id = cpg.card_program_id
and efl.employee_id = hremp.employee_id
and efl.create_distribution_flag = 'Y'
and posted_date between nvl(P_START_DATE, posted_date - 1) and
nvl(P_END_DATE, posted_date + 1)
and reject_code is NULL
AND iby.instrument_type='CREDITCARD' -- veramach added for bug 7196074
and not exists
(select 'A distribution exists for this expense feed line'
from ap_expense_feed_dists efd
where efd.feed_line_id = efl.feed_line_id)
and efl.card_program_id = P_CARD_PROGRAM_ID;
select GS.chart_of_accounts_id
into l_chart_of_accounts_id
from ap_system_parameters S,
gl_sets_of_books GS
where GS.set_of_books_id = S.set_of_books_id;
select account_segment_value
into l_account_segment_value
from ap_card_codes
where code_set_id = l_card_code_set_id
and code_value = l_card_code_value;
l_debug_info := 'Update the REJECT_CODE for INVALID ACCOUNT';
update ap_expense_feed_lines
set reject_code = 'INVALID ACCOUNT'
where feed_line_id = l_feed_line_id;
select org_id into l_org_id
from
ap_expense_feed_lines
where
feed_line_id=l_feed_line_id;
insert into AP_EXPENSE_FEED_DISTS_ALL
(FEED_LINE_ID,
FEED_DISTRIBUTION_ID,
AMOUNT,
DIST_CODE_COMBINATION_ID,
STATUS_CHANGE_DATE,
STATUS_LOOKUP_CODE,
ACCOUNT_SEGMENT_VALUE,
ACCOUNT_SEGMENT_VALUE_DEFAULT,
COST_CENTER,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,ORG_ID,
CONC_REQUEST_ID) VALUES
(l_feed_line_id,
ap_expense_feed_dists_s.nextval,
l_amount,
l_exp_line_ccid,
sysdate,
l_distribution_status,
l_account_segment_value,
l_account_segment_value,
l_cost_center,
l_description,
sysdate,
-1,
-1,
sysdate,
-1,l_org_id,P_REQUEST_ID);