The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_debugInfo := 'Get Org ID from card program to pass into InsertInvoice*';
SELECT org_id
INTO l_org_id
FROM ap_card_programs
WHERE card_program_id = p_cardProgramID;
l_debugInfo := 'Inserting into AP_INVOICE_LINES_INTERFACE';
l_result := AP_WEB_DB_AP_INT_PKG.InsertInvoiceLinesInterface(
l_invoice_id,
l_invoice_line_id,
l_count,
'ITEM',
l_billed_amt,
l_transaction_date,
l_ccid,
l_card_trxn_id,
l_description,
l_org_id );
l_debugInfo := 'Update AP_CREDIT_CARD_TRXNS_ALL.COMPANY_PREPAID_INVOICE_ID';
select party_id
into l_party_id
from ap_suppliers
where vendor_id = l_vendor_id
and rownum = 1;
SELECT party_site_id,
nvl(accts_pay_code_combination_id,(select accts_pay_code_combination_id from ap_system_parameters)), terms_id
INTO l_party_site_id, l_accts_pay_ccid, l_terms_id
FROM ap_supplier_sites
WHERE vendor_site_id = l_vendor_site_id;
SELECT terms_id
INTO l_terms_id
FROM ap_suppliers
WHERE vendor_id = l_vendor_id;
l_debugInfo := 'Inserting into AP_INVOICES_INTERFACE';
l_result := AP_WEB_DB_AP_INT_PKG.InsertInvoiceInterface(
l_invoice_id,
l_party_id,
l_vendor_id,
l_vendor_site_id,
l_sum,
l_invoice_currency_code,
'SelfService',
l_pay_group_lookup_code,
l_org_id,
l_doc_cat_code,-- Bug:7345524, replaced 'PAY REQ INV',
'PAYMENT REQUEST',
l_accts_pay_ccid,
l_party_site_id,
l_terms_id);
update ap_credit_card_trxns_all
set company_prepaid_invoice_id = null
where company_prepaid_invoice_id = l_invoice_id_temp -- Bug 6687752
and card_program_id = p_cardProgramID;
delete from ap_interface_rejections
where parent_id = l_invoice_id;
delete from ap_invoices_interface
where invoice_id = l_invoice_id;
delete from ap_invoice_lines_interface
where invoice_id = l_invoice_id;
update ap_credit_card_trxns_all
set company_prepaid_invoice_id = l_invoice_id
where company_prepaid_invoice_id = l_invoice_id_temp -- Bug 8365869 update the company_prepaid_invoice_id as invoice_id in ap_invoices_all
and card_program_id = p_cardProgramID;
AP_WEB_WRAPPER_PKG.insert_dist(
p_invoice_id => p_invoiceId,
p_Line_Type => 'MISCELLANEOUS',
p_GL_Date => p_glDate,
p_Period_Name => p_periodName,
p_Type_1099 => null,
p_Income_Tax_Region => null,
p_Amount => (l_cCardLineAmt),
p_Vat_Code => null,
p_Code_Combination_Id => l_clearingCCID,
p_PA_Quantity => null,
p_Description => null,
p_Project_Acct_Cont => null,
p_Project_Id => null,
p_Task_Id => null,
p_Expenditure_Type => null,
p_Expenditure_Org_Id => null,
p_Exp_item_date => null,
p_Attribute_Category => null,
p_Attribute1 => null,
p_Attribute2 => null,
p_Attribute3 => null,
p_Attribute4 => null,
p_Attribute5 => null,
p_Attribute6 => null,
p_Attribute7 => null,
p_Attribute8 => null,
p_Attribute9 => null,
p_Attribute10 => null,
p_Attribute11 => null,
p_Attribute12 => null,
p_Attribute13 => null,
p_Attribute14 => null,
p_Attribute15 => null,
p_invoice_distribution_id => null,
p_Tax_Code_Id => null,
p_tax_recoverable_flag => null,
p_tax_recovery_rate => null,
p_tax_code_override_flag => null,
p_tax_recovery_override_flag => null,
p_po_distribution_id => null,
p_Calling_Sequence => l_callingSequence,
p_company_prepaid_invoice_id => l_prepaidInvId,
p_cc_reversal_flag => 'Y');
l_debugInfo := 'Update the invoice_amount.';
SELECT decode(erl.web_parameter_id,l_parameterId,-erl.amount,erl.amount),
cc.company_prepaid_invoice_id, cc.card_program_id,
decode(erl.web_parameter_id,l_parameterId, 'PERSONAL', 'BUSINESS'),
erl.org_id,
nvl(cc.transaction_date,sysdate),
erh.employee_id,
erl.vat_code,
erl.amount_includes_tax_flag
FROM ap_expense_report_lines_all erl,
ap_credit_card_trxns_all cc,
ap_expense_report_headers_all erh
WHERE erl.report_header_id = erh.report_header_id
AND nvl(erl.itemization_parent_id,0) <> -1 /* Itemization Project */
AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
AND cc.payment_due_from_code in ('BOTH','COMPANY') -- Both Pay split project
AND erh.report_header_id = p_expReportHeaderId
AND erh.source = 'SelfService';
INSERT INTO AP_INVOICE_LINES_INTERFACE
(INVOICE_ID,
INVOICE_LINE_ID,
LINE_TYPE_LOOKUP_CODE,
ACCOUNTING_DATE,
AMOUNT,
ASSETS_TRACKING_FLAG,
DIST_CODE_COMBINATION_ID,
ORG_ID,
CC_REVERSAL_FLAG,
TAX_CLASSIFICATION_CODE,
AMOUNT_INCLUDES_TAX_FLAG)
SELECT p_invoiceId,
AP_INVOICE_LINES_INTERFACE_S.nextval,
'MISCELLANEOUS',
p_gl_date,
-l_cCardLineAmt,
DECODE(nvl(gcc.account_type, 'x'),
'A',
'Y',
'N'),
l_clearingCCID,
l_org_id,
'Y',
l_vat_code,
l_amount_includes_tax_flag
FROM gl_code_combinations GCC
WHERE GCC.code_combination_id = l_clearingCCID;
l_debugInfo := 'Update the invoice_amount.';