[Home] [Help]
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,card_id,feed_line_id
from ap_expense_feed_lines where reject_code='UNTESTED'
)
loop
--8726861
BEGIN
select aca.card_reference_id
into l_card_reference_id
from ap_cards aca
where aca.card_id = i.card_id
and aca.card_reference_id is not null ;
update ap_expense_feed_lines efl
set reject_code = 'INVALID CARD ID'
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;
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 NOT EXISTS (select 1 from ap_card_programs cp
where cp.card_program_id = efl.card_program_id
and cp.card_type_lookup_code = 'SUPPLIER')
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 efl
set reject_code = 'EMPLOYEE NOT VALID'
where not exists
(select 1
from hr_employees_current_v hremp
where efl.employee_id = hremp.employee_id
)
and exists (select 1 from ap_card_programs cp
where cp.card_program_id = efl.card_program_id
and cp.card_type_lookup_code = 'SUPPLIER')
and employee_id is not null
and reject_code = 'UNTESTED';
update ap_expense_feed_lines efl
set reject_code = 'EMPLOYEE INACTIVE'
where reject_code = 'UNTESTED'
and not exists (select 'employee is in hr_employees_current_v'
from hr_employees_current_v hremp
where hremp.employee_id = efl.employee_id)
and card_id is not null
and 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);
update ap_expense_feed_lines efl
set reject_code = 'EMPLOYEE REQUIRED'
where not exists
(select 1
from ap_card_profiles cp
where efl.card_program_id = cp.card_program_id
and nvl(cp.emp_notification_lookup_code, 'N') = 'N'
and nvl(cp.mgr_approval_lookup_code, 'N') = 'N'
)
and exists (select 1 from ap_card_programs cp
where cp.card_program_id = efl.card_program_id
and cp.card_type_lookup_code = 'SUPPLIER')
and employee_id is null
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 (+) -- Bug 10238210.
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);