The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y' into has_detailed_trxn
from ap_credit_card_trxns_all
where (HOTEL_TELEPHONE_AMOUNT > 0 OR
HOTEL_BAR_AMOUNT > 0 OR
HOTEL_MOVIE_AMOUNT > 0 OR
HOTEL_GIFT_SHOP_AMOUNT > 0 OR
HOTEL_LAUNDRY_AMOUNT > 0 OR
HOTEL_HEALTH_AMOUNT > 0 OR
HOTEL_RESTAURANT_AMOUNT > 0 OR
HOTEL_BUSINESS_AMOUNT > 0 OR
HOTEL_PARKING_AMOUNT > 0 OR
HOTEL_ROOM_SERVICE_AMOUNT > 0 OR
HOTEL_TIP_AMOUNT > 0 OR
HOTEL_MISC_AMOUNT > 0)
and trx_id = p_trx_id
and folio_type = 'HOTEL';
select card_id into l_card_id
from ap_cards_all
where card_reference_id = l_instr_id
and card_program_id = p_card_program_id
and rownum = 1;
insert into ap_cards_all (card_id,
card_program_id,
org_id,
card_reference_id,
request_id,
last_update_date,
creation_date,
created_by,
last_updated_by
)
values( ap_cards_s.nextval,
p_card_program_id,
(select org_id
from ap_card_programs_all apcp
where apcp.card_program_id = p_card_program_id and rownum =1),
l_instr_id,
p_request_id,
sysdate,
sysdate,
nvl(l_user_id, -1),
nvl(l_user_id, -1)
) returning card_id into l_card_id ;
insert into ap_card_details (card_id,
request_id,
last_update_date,
creation_date,
created_by,
last_updated_by,
last_update_login)
values (l_card_id,
p_request_id,
sysdate,
sysdate,
nvl(l_user_id, -1),
nvl(l_user_id, -1),
nvl(l_user_id, -1)
);
select crd.card_reference_id -- Instrument ID
into l_card_reference_id
from ap_invoices_all inv,
iby_docs_payable_all iby,
ap_expense_report_headers_all erh,
ap_credit_card_trxns_all ctx,
ap_cards_all crd
where iby.document_payable_id = p_document_payable_id
and iby.calling_app_doc_unique_ref2 = inv.invoice_id
and inv.invoice_id = erh.vouchno
and inv.invoice_type_lookup_code in ('EXPENSE REPORT', 'MIXED')
and inv.source in ('SelfService', 'Both Pay', 'CREDIT CARD')
and ctx.report_header_id = erh.bothpay_parent_id
and crd.card_id = ctx.card_id
and rownum = 1;