[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select p.full_name, o.organization_id, o.name
into l_inact_emp_display_name, l_inact_emp_org_id, l_inact_emp_org_name
from per_people_f p,
per_assignments_f a,
per_assignment_status_types s,
per_organization_units o
where p.person_id = p_inact_employee_id
and p.person_id = a.person_id
and a.primary_flag = 'Y'
and a.assignment_status_type_id = s.assignment_status_type_id
and o.organization_id = a.organization_id
and o.business_group_id = a.business_group_id
and per_system_status in ('TERM_ASSIGN', 'SUSP_ASSIGN')
and a.assignment_type in ('E', 'C')
and trunc(sysdate) between p.effective_start_date and p.effective_end_date
and trunc(sysdate) between a.effective_start_date and a.effective_end_date;
SELECT transaction_date,
merchant_name1,
merchant_city,
billed_amount,
billed_currency_code,
null invoice_num
FROM
ap_credit_card_trxns cct,
ap_cards ac
WHERE cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.expensed_amount,0) = 0
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and nvl(cct.billed_date, cct.posted_date) between
nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and ac.employee_id = p_employeeId
and cct.inactive_emp_wf_item_key = p_itemkey
and cct.report_header_id is NULL
union all
SELECT transaction_date,
merchant_name1,
merchant_city,
billed_amount,
billed_currency_code,
erh.invoice_num
FROM
ap_credit_card_trxns cct,
ap_cards ac,
ap_expense_report_headers erh
WHERE
cct.card_program_id = p_cardProgramId
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.expensed_amount <> 0
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED','DEACTIVATED')
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and nvl(cct.billed_date, cct.posted_date) between
nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
and erh.report_header_id = cct.report_header_id
and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,erh.report_header_id) in ('EMPAPPR','RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED')
and ac.employee_id = p_employeeId
and cct.inactive_emp_wf_item_key = p_itemkey
and rownum < 41
order by transaction_date;
l_debug_info := 'Inserting Securing Attribute';
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_UPDATE_login => c_last_update_login);
select web_user_id
from ak_web_user_sec_attr_values
where web_user_id = p_preparer_web_user_id
and attribute_code = 'ICX_HR_PERSON_ID'
and number_value = to_char(p_inact_employee_id);
ICX_User_Sec_Attr_PUB.Delete_User_Sec_Attr (
p_api_version_number => c_api_version_num,
p_commit => c_commit,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_web_user_id => l_preparer_web_user_id,
p_attribute_code => c_sec_attribute,
p_attribute_appl_id => c_attribute_appl_id,
p_varchar2_value => NULL,
p_date_value => NULL,
p_number_value => l_inact_employee_id);
select 1
from dual
where exists (select cct.trx_id
from ap_cards_all ac,
ap_credit_card_trxns cct
where ac.card_program_id = p_credit_card_program_id
and ac.employee_id = p_inact_employee_id
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and cct.inactive_emp_wf_item_key = p_itemkey
-- group by cct.trx_id
minus
(select cct.trx_id
from ap_cards_all ac,
ap_credit_card_trxns cct,
ap_expense_report_headers erh
where ac.card_program_id = p_credit_card_program_id
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and cct.report_header_id = erh.report_header_id
and erh.source <> 'NonValidatedWebExpense'
and nvl(cct.billed_date, cct.posted_date) between p_cc_billed_start_date and p_cc_billed_end_date
and ac.employee_id = p_inact_employee_id
and cct.inactive_emp_wf_item_key = p_itemkey
-- group by cct.trx_id
));
select 1
from dual
where exists (select cct.trx_id
from ap_credit_card_trxns cct,
ap_cards_all ac,
--ap_card_programs_all cp,
ap_expense_report_lines erl,
ap_expense_report_headers erh
where ac.card_program_id = p_credit_card_program_id
and cct.validate_code = 'Y'
and cct.payment_flag <> 'Y'
and cct.inactive_emp_wf_item_key is not null
and cct.inactive_emp_wf_item_key <> p_itemkey
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and cct.report_header_id = erh.report_header_id(+)
--and cct.trx_id = erl.credit_card_trx_id(+)
and erh.report_header_id = erl.report_header_id(+)
--and decode(erh.expense_status_code, null, decode(erh.workflow_approved_flag, 'S','SAVED',null, decode(erh.source, null,'UNSUBMITTED')), erh.expense_status_code)
and ac.employee_id = p_inact_employee_id
group by cct.trx_id);
SELECT orig_system_id
FROM wf_roles
WHERE orig_system = 'PER'
AND name = l_TransferToName;
select erh.source
from ap_expense_report_headers erh,
ap_credit_card_trxns cct,
ap_cards_all ac
where ac.card_program_id = p_credit_card_program_id
and cct.validate_code = 'Y'
and cct.inactive_emp_wf_item_key is not null
and cct.inactive_emp_wf_item_key = p_itemkey
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and cct.report_header_id = erh.report_header_id
and ac.employee_id = p_inact_employee_id;
SELECT user_id
INTO p_user_id
FROM fnd_user
WHERE user_name = p_emp_user_name;
select cct.trx_id
from ap_credit_card_trxns cct,
ap_cards_all ac
where ac.card_program_id = p_credit_card_program_id
and cct.validate_code = 'Y'
and cct.inactive_emp_wf_item_key is not null
and cct.inactive_emp_wf_item_key = p_itemkey
and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
and ac.card_program_id = cct.card_program_id
and ac.card_id = cct.card_id
and ac.employee_id = p_inact_employee_id;
update ap_credit_card_trxns
set inactive_emp_wf_item_key = NULL
where trx_id = l_trx_id;