[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select base_currency_code,
set_of_books_id,
expense_report_id,
default_exchange_rate_type
into p_base_curr_code,
p_set_of_books_id,
p_expense_report_id,
p_default_exch_rate_type
from ap_system_parameters;
SELECT name
INTO p_curr_name
FROM fnd_currencies_vl
WHERE currency_code = p_curr_code;
select GS.chart_of_accounts_id
into p_chart_of_accounts
from ap_system_parameters S,
gl_sets_of_books GS
where GS.set_of_books_id = S.set_of_books_id;
SELECT s.base_currency_code,
l_default_exch_rate_type,
c.name,
nvl(s.multi_currency_flag, 'N')
INTO p_sys_info_rec.base_currency,
p_sys_info_rec.default_exchange_rate_type,
p_sys_info_rec.base_curr_name,
p_sys_info_rec.sys_multi_curr_flag
FROM ap_system_parameters s,
fnd_currencies_vl c
WHERE c.currency_code = s.base_currency_code;
SELECT s.exchange_rate_type
INTO p_default_exchange_rate_type
FROM ap_pol_exrate_options s
WHERE enabled = 'Y';
SELECT s.default_exchange_rate_type
INTO p_default_exchange_rate_type
FROM ap_system_parameters s,
fnd_currencies_vl c
WHERE c.currency_code = s.base_currency_code;
SELECT expense_check_address_flag, vendor_id
INTO l_expense_check_address_flag, l_vendor_id
FROM (
SELECT emp.expense_check_address_flag, null vendor_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.expense_check_address_flag, vendor_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
);
SELECT expense_check_address_flag
INTO l_expense_check_address_flag
FROM financials_system_parameters;
SELECT vdr.vendor_id, site.payment_currency_code, c.name
INTO p_vendor_id, p_vend_pay_curr_code, p_vend_pay_curr_name
FROM ap_suppliers vdr, ap_supplier_sites site, fnd_currencies_vl c
WHERE site.vendor_id = vdr.vendor_id
AND ( (vdr.employee_id = p_employee_id and l_vendor_id is null ) OR (l_vendor_id is not null and l_vendor_id = vdr.vendor_id)) -- Bug 6978871(sodash) ,#12916260
AND c.currency_code(+) = site.payment_currency_code
AND upper(site.vendor_site_code) IN
(SELECT upper(meaning)
FROM fnd_lookup_values
WHERE lookup_code = l_expense_check_address_flag
AND lookup_type = 'HOME_OFFICE');
SELECT vendor_id,
payment_currency_code,
c.name
INTO p_vendor_id,
p_vend_pay_curr_code,
p_vend_pay_curr_name
FROM ap_suppliers v,
fnd_currencies_vl c
WHERE ( ( v.employee_id = p_employee_id and l_vendor_id is null ) OR (l_vendor_id is not null and l_vendor_id = v.vendor_id)) -- Bug 6978871(sodash),#12916260
AND c.currency_code(+) = v.payment_currency_code;
SELECT nvl(PV.allow_awt_flag, 'N'),
PV.awt_group_id
INTO p_ven_allow_awt_flag,
p_ven_awt_group_id
FROM ap_suppliers PV,
ap_expense_report_headers RH
WHERE RH.report_header_id = p_report_header_id
AND PV.employee_id = RH.employee_id;
SELECT vendor_id
INTO p_vendor_id
FROM ap_suppliers
WHERE employee_id = p_employee_id;
SELECT accts_pay_code_combination_id
INTO p_code_comb_id
FROM ap_supplier_sites
WHERE vendor_site_id = p_vendor_site_id;
SELECT accts_pay_code_combination_id
INTO p_accts_pay
FROM ap_suppliers
WHERE vendor_id = p_vendor_id;
SELECT pay_group_lookup_code
INTO p_pay_group_code
FROM ap_supplier_sites
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id;
SELECT ap_invoices_interface_s.nextval
INTO p_invoice_id
FROM sys.dual;
SELECT ap_invoice_lines_interface_s.nextval
INTO p_invoice_line_id
FROM sys.dual;
select s.signing_limit, p.base_currency_code,
nvl((SELECT exchange_rate_type FROM ap_pol_exrate_options WHERE enabled = 'Y'),p.default_exchange_rate_type)
from ap_system_parameters p,
ap_web_signing_limits s
where employee_id = p_approver_id
and to_number(cost_center) = to_number(l_doc_cost_center)
and document_type = p_item_type;
select s.signing_limit, p.base_currency_code,
nvl((SELECT exchange_rate_type FROM ap_pol_exrate_options WHERE enabled = 'Y'),p.default_exchange_rate_type)
from ap_system_parameters p,
ap_web_signing_limits s
where employee_id = p_approver_id
and cost_center = l_doc_cost_center
and document_type = p_item_type;
SELECT GS.chart_of_accounts_id,
HR.default_code_combination_id
FROM ap_system_parameters S,
gl_sets_of_books GS,
per_workforce_x HR
WHERE HR.person_id = p_employee_id
AND GS.set_of_books_id = S.set_of_books_id
AND S.set_of_books_id = nvl(HR.set_of_books_id,S.set_of_books_id)
AND rownum = 1;
SELECT set_of_books_id, default_code_combination_id
INTO l_emp_set_of_books_id, l_default_emp_ccid
FROM (
SELECT emp.set_of_books_id, emp.default_code_combination_id
FROM per_employees_x emp
WHERE emp.employee_id = l_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.set_of_books_id, emp.default_code_combination_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = l_employee_id
);
SELECT GS.chart_of_accounts_id
INTO l_emp_chart_of_accounts_id
FROM gl_sets_of_books GS
WHERE GS.set_of_books_id = l_emp_set_of_books_id;
SELECT expense_clearing_ccid, nvl(data_feed_level_code,'N')
INTO l_expense_clearing_ccid, l_data_feed_level_code
FROM ap_card_programs_all
WHERE card_program_id = p_card_program_id;
SELECT EXPENSE_CLEARING_CCID
INTO l_expense_clearing_ccid
FROM FINANCIALS_SYSTEM_PARAMETERS;
SELECT pera.default_code_comb_id
INTO l_default_emp_ccid
FROM per_assignments_f pera,
per_assignment_status_types peras
WHERE pera.person_id = p_employee_id
AND pera.assignment_status_type_id = peras.assignment_status_type_id
AND trunc(p_as_of_date) between pera.effective_start_date and pera.effective_end_date
AND pera.assignment_type in ('C', 'E')
AND pera.primary_flag='Y'
AND peras.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK');
SELECT ROUNDING_ERROR_CCID
INTO p_ccid
FROM ap_system_parameters;
SELECT nvl(sum(decode(payment_status_flag, 'Y',
decode(sign(earliest_settlement_date - sysdate),1,0,1),
0)),
0)
INTO p_available_prepays
FROM ap_invoices I,
ap_suppliers PV
WHERE (select sum(aid.prepay_amount_remaining)
from ap_invoice_distributions aid
where aid.invoice_id = I.invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y'
) > 0
AND I.vendor_id = PV.vendor_id
AND PV.employee_id = p_employee_id
AND I.invoice_type_lookup_code = 'PREPAYMENT'
AND earliest_settlement_date IS NOT NULL
AND I.invoice_amount > 0
AND I.invoice_currency_code = p_default_currency_code;
FUNCTION InsertInvoiceInterface(
p_invoice_id IN invIntf_invID,
p_party_id IN invIntf_partyID,
p_vendor_id IN invIntf_vendorID,
p_vendor_site_id IN invIntf_vendorSiteID,
p_sum IN invIntf_invAmt,
p_invoice_curr_code IN invIntf_invCurrCode,
p_source IN invIntf_source,
p_pay_group_lookup_code IN vendorSites_payGroupLookupCode,
p_org_id IN NUMBER,
p_doc_category_code IN invIntf_docCategoryCode,
p_invoice_type_lookup_code IN invIntf_invTypeCode,
p_accts_pay_ccid IN invIntf_acctsPayCCID,
p_party_site_id IN invIntf_partySiteID default null,
p_terms_id IN AP_TERMS.TERM_ID%TYPE default null
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
l_payment_priority ap_supplier_sites_all.payment_priority%TYPE;
select payment_priority
into l_payment_priority
from ap_supplier_sites_all
where vendor_site_id = p_vendor_site_id;
INSERT INTO AP_INVOICES_INTERFACE
(INVOICE_ID,
INVOICE_NUM,
PARTY_ID,
PARTY_SITE_ID,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
SOURCE,
PAY_GROUP_LOOKUP_CODE,
ORG_ID,
DOC_CATEGORY_CODE,
INVOICE_TYPE_LOOKUP_CODE,
ACCTS_PAY_CODE_COMBINATION_ID,
TERMS_ID,
PAYMENT_PRIORITY)
VALUES
(p_invoice_id,
substrb(to_char(p_invoice_id)||'-'||to_char(sysdate), 1, 50),
p_party_id,
p_party_site_id,
p_vendor_id,
p_vendor_site_id,
p_sum,
p_invoice_curr_code,
p_source,
p_pay_group_lookup_code,
p_org_id,
p_doc_category_code,
p_invoice_type_lookup_code,
p_accts_pay_ccid,
p_terms_id,
l_payment_priority);
AP_WEB_DB_UTIL_PKG.RaiseException( 'UpdateInvoiceInterface' );
END InsertInvoiceInterface;
FUNCTION InsertInvoiceLinesInterface(
p_invoice_id IN invLines_invID,
p_invoice_line_id IN invLines_invLineID,
p_count IN invLines_lineNum,
p_linetype IN invLines_lineTypeLookupCode,
p_amount IN invLines_amount,
p_trxn_date IN invLines_accountingDate,
p_ccid IN invLines_distCodeCombID,
p_card_trxn_id IN invLines_crdCardTrxID,
p_description IN invLines_description,
p_org_id IN NUMBER
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
BEGIN
INSERT INTO AP_INVOICE_LINES_INTERFACE(
INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
ACCOUNTING_DATE,
DIST_CODE_COMBINATION_ID,
CREDIT_CARD_TRX_ID,
DESCRIPTION,
ORG_ID)
VALUES
(p_invoice_id,
p_invoice_line_id,
p_count,
p_linetype,
p_amount,
p_trxn_date,
p_ccid,
p_card_trxn_id,
p_description,
p_org_id);
AP_WEB_DB_UTIL_PKG.RaiseException( 'InsertInvoiceLinesInerface' );
END InsertInvoiceLinesInterface;
SELECT NVL(WEB_ENABLED_FLAG, 'N')
INTO p_tax_web_enabled
FROM AP_TAX_CODES
WHERE NAME = P_ExpTypeDefaultTaxCode
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(web_enabled_flag,'N') = 'Y';
SELECT inv.invoice_amount, inv.exchange_rate,
F.minimum_accountable_unit, F.precision
INTO p_invoiceAmt, p_exchangeRate, p_minAcctUnit, p_precision
FROM AP_INVOICES inv, ap_system_parameters sp, fnd_currencies F
WHERE inv.invoice_id = p_invoiceId
AND inv.set_of_books_id = sp.set_of_books_id
AND F.currency_code = sp.base_currency_code;
UPDATE AP_INVOICES
SET invoice_amount = p_invoiceAmt,
pay_curr_invoice_amount = ap_web_utilities_pkg.oie_round_currency(
p_invoiceAmt * payment_cross_rate,
payment_currency_code),
base_amount = p_baseAmt
WHERE invoice_id = p_invoiceId;
SELECT NAME
INTO P_VatCode
FROM AP_TAX_CODES
WHERE TAX_ID = P_TaxCodeID;
SELECT TAX_ID
INTO P_TAXCodeID
FROM AP_TAX_CODES
WHERE NAME = P_VatCode;
SELECT TAX_ID
INTO P_TAXCodeID
FROM AP_TAX_CODES
WHERE NAME = P_VatCode
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(P_ExpLine_Date,sysdate) BETWEEN
nvl(start_date,nvl(P_ExpLine_Date,sysdate)) AND
nvl(inactive_date,nvl(P_ExpLine_Date,sysdate));
SELECT employee_id, txn.card_program_id, transaction_date
INTO l_employee_id, l_card_program_id, l_transaction_date
FROM ap_cards card,
ap_credit_card_trxns txn
WHERE card.card_program_id = txn.card_program_id
AND card.card_id = txn.card_id
AND txn.trx_id = p_trx_id;
SELECT default_exchange_rates, exchange_rate_allowance
INTO l_default_exchange_rates, l_exchange_rate_allowance
FROM ap_pol_exrate_options WHERE enabled = 'Y';
SELECT default_exchange_rate_flag INTO l_default_exchange_rate_flag
FROM ap_web_preferences WHERE employee_id = l_employee_id;