[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select displayed_field
from ap_lookup_codes
where (lookup_code = LookupCode)
and (lookup_type = LookupType);
select FC.minimum_accountable_unit, FC.precision
into l_minimum_acct_unit, l_precision
from fnd_currencies FC
where FC.currency_code = P_Currency_Code;
select FC.minimum_accountable_unit, FC.precision
into l_minimum_acct_unit, l_precision
from fnd_currencies FC
where FC.currency_code = P_Currency_Code;
order to use a stored function in the select-list of a
query. Therefore, I am commenting it out NOCOPY and simply
returning null if no record is retrieved.
--
raise_application_error(-20000,'APUT002/No such currency ' ||
P_Currency_Code);
SELECT SP.base_currency_code,
FC.precision,
FC.minimum_accountable_unit
INTO l_func_currency,
l_fc_precision,
l_fc_min_acct_unit
FROM ap_system_parameters SP,
fnd_currencies FC
WHERE FC.currency_code = P_Currency_Code;
SELECT FC.precision,
FC.minimum_accountable_unit
INTO l_fc_precision,
l_fc_min_acct_unit
FROM fnd_currencies FC
WHERE FC.currency_code = P_Currency_Code;
select decode(P_Min_unit,
null, round(P_Amount, P_Precision),
round(P_Amount/P_Min_unit) * P_Min_unit)
into l_rounded_amount
from sys.dual;
SELECT period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND trunc(P_Date) BETWEEN start_date AND end_date
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = p_org_id
AND GLPS.set_of_books_id = SP.set_of_books_id
/* Bug 5368685 */
AND trunc(P_Date) BETWEEN start_date AND end_date
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT MIN(start_date),
period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND end_date >= P_Date --Bug6809792
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N'
GROUP BY period_name
ORDER BY MIN(start_date);
SELECT MIN(start_date),
period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP --8281653
WHERE application_id = 200
AND SP.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND (start_date > P_Date OR
P_Date BETWEEN start_date AND end_date)
AND closing_status = 'O'
AND NVL(adjustment_period_flag, 'N') = 'N'
GROUP BY period_name
ORDER BY MIN(start_date);
order to use a stored function in the select-list of a
query. Therefore, I am commenting it out NOCOPY and simply
returning null if no record is retrieved.
if (SQLCODE <> -20001) then
FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
SELECT responsibility_name
INTO l_wnd_context
FROM fnd_responsibility_vl
WHERE application_id = l_application_id
AND responsibility_id = l_resp_id;
SELECT nvl(multi_org_flag, 'N')
, nvl(multi_currency_flag, 'N')
INTO l_multi_org
, l_multi_cur
FROM fnd_product_groups;
select substrb((g.SHORT_NAME || decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || substr(g.currency_code, 1, 5)))),1,60)
into l_wnd_context
from gl_sets_of_books g
, ap_system_parameters aps
where aps.SET_OF_BOOKS_ID = g.SET_OF_BOOKS_ID;
select substrb((substr(h.Name, 1, 53)
|| decode(g.mrc_sob_type_code, 'N', NULL,
decode(l_multi_cur, 'N', NULL,
': ' || substr(g.currency_code, 1, 5)))),1,60)
into l_wnd_context
from gl_sets_of_books g,
ap_system_parameters aps,
hr_operating_units h
where h.organization_id = to_number(l_id)
--Bug 13975870
and h.organization_id = aps.org_id
--Bug 13975870
and aps.set_of_books_id = g.set_of_books_id;
debug_info := 'Select Charts of Account';
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
debug_info := 'Select Charts of Account';
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
debug_info := 'Select Charts of Account';
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT nvl(enabled_flag,'N')
INTO l_enabled_flag
FROM gl_code_combinations
WHERE code_combination_id = p_ccid
AND chart_of_accounts_id = p_chart_of_accounts_id
-- Bug 3486932 - Added the following conditions to verify if GL account
-- is valid and summary flag and template id are proper.
-- Bug 3379623 deleted the previous AND stmt and added the below two.
AND NVL(start_date_active, TRUNC(p_date)) <= TRUNC(p_date)
AND NVL(end_date_active,
TO_DATE('12/31/4012','MM/DD/YYYY')) >= TRUNC(p_date)
AND summary_flag = 'N'
AND template_id is NULL;
select inventory_organization_id
into inv_org_id
from financials_system_parameters
where org_id = p_org_id;
SELECT DISTINCT primary_set_of_books_id
INTO l_primary_sob_id
FROM gl_mc_reporting_options
WHERE reporting_set_of_books_id = P_SOB_ID
AND application_id = 200;
SELECT match_status_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
UNION
SELECT 'N'
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND match_status_flag IS NULL
AND EXISTS
(SELECT 'There are both untested and tested lines'
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND match_status_flag IN ('T','A'))
UNION -- Bug 6866672
SELECT 'N'
FROM ap_invoice_lines_all ail, ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND ai.invoice_id = ail.invoice_id
AND ai.cancelled_date is NULL
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = ail.line_number)
AND ail.amount <> 0; -- Bug 6911199. Should ignore 0 Line Amounts.(Also one test case is an open issue)
SELECT org_id
INTO l_org_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT NVL(purch_encumbrance_flag,'N')
INTO encumbrance_flag
FROM financials_system_params_all
WHERE NVL(org_id, -99) = NVL(l_org_id, -99);
SELECT count(*)
INTO invoice_holds
FROM ap_holds_all
WHERE invoice_id = p_invoice_id
AND release_lookup_code is NULL;
SELECT nvl(ai.force_revalidation_flag, 'N')
INTO l_force_revalidation_flag
FROM ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id;
SELECT nvl((0 - sum(AIL.amount)),0)
INTO l_prepay_amount
FROM ap_invoice_lines_all AIL
WHERE AIL.invoice_id = p_invoice_id
AND nvl(AIL.invoice_includes_prepay_flag,'N') = 'N'
AND (AIL.line_type_lookup_code = 'PREPAY'
OR (AIL.line_type_lookup_code = 'TAX'
AND AIL.prepay_invoice_id IS NOT NULL
AND AIL.prepay_line_number IS NOT NULL));
SELECT nvl(AI.invoice_amount,0)- l_prepay_amount
- nvl(AP_INVOICES_UTILITY_PKG.get_amount_withheld(p_invoice_id),0)
INTO l_net_inv_amount
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id;
SELECT set_of_books_id,
nvl(liability_post_lookup_code, 'NONE')
INTO l_primary_sob_id,
l_liability_post_lookup_code
FROM ap_system_parameters
where org_id = nvl(AP_UTILITIES_PKG.g_org_id,org_id); /*Bug11720134*/
SELECT chart_of_accounts_id
INTO l_chart_of_accts_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_primary_sob_id;
SELECT set_of_books_id,
nvl(liability_post_lookup_code, 'NONE')
INTO l_primary_sob_id,
l_liability_post_lookup_code
FROM ap_system_parameters;
SELECT chart_of_accounts_id
INTO l_chart_of_accts_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_primary_sob_id;
FUNCTION delete_invoice_from_interface(p_invoice_id_table in number_table_type,
p_invoice_line_id_table in number_table_type,
p_calling_sequence VARCHAR2) return boolean as
current_calling_sequence VARCHAR2(2000);
'AP_UTILITIES_PKG.delete_invoice_from_interface<-'||P_calling_sequence;
debug_info := 'Delete records from rejection and interface tables';
/* Delete invoices from interface */
forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
delete from ap_invoices_interface where invoice_id = p_invoice_id_table(i);
/* Delete invoice lines from interface */
forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
delete from ap_invoice_lines_interface where invoice_id = p_invoice_id_table(i);
/* Delete invoice rejections from the rejections table */
forall i in nvl(p_invoice_id_table.first,0)..nvl(p_invoice_id_table.last,0)
delete from ap_interface_rejections
where parent_id = p_invoice_id_table(i) and
parent_table = 'AP_INVOICES_INTERFACE';
/* Delete invoice lines rejections from the rejections table */
forall i in nvl(p_invoice_line_id_table.first,0)..nvl(p_invoice_line_id_table.last,0)
delete from ap_interface_rejections
where parent_id = p_invoice_line_id_table(i) and
parent_table = 'AP_INVOICE_LINES_INTERFACE';
END delete_invoice_from_interface ;
SELECT nvl(calc_user_xrate, 'N')
INTO l_calc_user_xrate
FROM ap_system_parameters;
SELECT jb.name INTO ret_val
FROM gl_je_batches jb
WHERE JB.je_batch_id IN (SELECT IR.je_batch_id
FROM gl_import_references IR
WHERE IR.gl_sl_link_id = P_GL_SL_link_id
AND IR.gl_sl_link_table = 'APECL');
SELECT '-999' into ret_val
FROM DUAL
WHERE NOT EXISTS (SELECT 'this link id exists in IR'
FROM gl_import_references IR
WHERE IR.gl_sl_link_id=P_gl_sl_link_id
AND IR.gl_sl_link_table = 'APECL');
SELECT jb.name INTO ret_val
FROM gl_je_batches jb
WHERE JB.je_batch_id = P_batch_id
and P_GL_SL_link_id IN
(SELECT IR.gl_sl_link_id
FROM gl_import_references IR, gl_je_headers JEH
WHERE IR.je_header_id = JEH.je_header_id
AND JEH.ledger_id = P_Ledger_id
AND JEH.je_batch_id = P_batch_id
AND IR.gl_sl_link_table = 'APECL');
SELECT meaning
FROM fnd_lookups
WHERE (lookup_code = LookupCode)
AND (lookup_type = LookupType);
p_default_last_updated_by IN NUMBER,
p_default_last_update_login IN NUMBER,
p_pa_default_dist_ccid OUT NOCOPY NUMBER,
p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
p_debug_Info OUT NOCOPY VARCHAR2,
p_debug_Context OUT NOCOPY VARCHAR2,
p_calling_sequence IN VARCHAR2,
p_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE --bug 5386396
) RETURN BOOLEAN
IS
procedure_billable_flag VARCHAR2(60) := '';
SELECT bill_to_location_id,
accts_pay_code_combination_id
INTO l_bill_to_location_id,
l_supp_site_liab_ccid
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id;
SELECT account_owner_org_id
INTO l_legal_entity_id
FROM ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
WHERE cbau.bank_account_id = cba.bank_account_id
AND cbau.bank_acct_use_id = p_bank_acct_use_id;
SELECT bill_to_location_id,
accts_pay_code_combination_id
INTO l_bill_to_location_id,
l_supp_site_liab_ccid
FROM po_vendor_sites
WHERE vendor_site_id = p_vendor_site_id;
PROCEDURE Delete_AP_Profiles
(P_Profile_Option_Name IN VARCHAR2)
IS
BEGIN
FND_PROFILE_OPTIONS_PKG.Delete_Row(P_Profile_Option_Name);
END Delete_AP_Profiles;
SELECT GLPS.closing_status
FROM gl_period_statuses GLPS,
ap_system_parameters SP
WHERE GLPS.application_id = 200
AND GLPS.set_of_books_id = SP.set_of_books_id
AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
SELECT GLPS.closing_status
FROM gl_period_statuses GLPS,
ap_system_parameters SP
WHERE GLPS.application_id = 8721
AND SP.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND TRUNC(p_gl_date) BETWEEN GLPS.start_date AND GLPS.end_date
AND NVL(GLPS.adjustment_period_flag, 'N') = 'N';
SELECT sum(nvl(ael.accounted_cr,0) - nvl(ael.accounted_dr,0) )
FROM (SELECT DISTINCT old_distribution_id, encumbered_flag, org_id
FROM AP_INVOICE_DISTRIBUTIONS dist
WHERE po_distribution_id = p_po_distribution_id
) aid,
AP_ENCUMBRANCE_LINES ael,
financials_system_parameters fsp
WHERE 1=1 --aid.po_distribution_id = P_po_distribution_id --commented for bug12962585
AND aid.old_distribution_id = ael.invoice_distribution_id -- added for bug12962585
-- AND aid.invoice_distribution_id = ael.invoice_distribution_id --commented for bug12962585
AND ( ( p_start_gl_date is not null
and p_start_gl_date <= ael.accounting_date ) or
( p_start_gl_date is null ) )
AND ( (p_end_gl_date is not null
and p_end_gl_date >= ael.accounting_date ) or
(p_end_gl_date is null ) )
--AND ael.encumbrance_line_type not in ('IPV', 'ERV', 'QV','AV') --commented for bug12962585
AND nvl(aid.org_id,-1) = nvl(fsp.org_id,-1)
AND ael.encumbrance_type_id = fsp.purch_encumbrance_type_id
--added below condition for bug12962585
AND ( (ael.ae_header_id is null and aid.encumbered_flag = 'Y' ) or
(ael.ae_header_id is not null and
'Y' = ( select gl_transfer_flag
from ap_ae_headers aeh
where aeh.ae_header_id = ael.ae_header_id ) )
)
;
SELECT sum (nvl(nvl(aid.base_amount,aid.amount),0) -
nvl(aid.base_invoice_price_variance ,0) -
nvl(aid.exchange_rate_variance,0) -
nvl(aid.base_quantity_variance,0))
FROM ap_invoice_dists_arch aid, --bug12962585, changed to old table
po_distributions pd,
financials_system_parameters fs
where aid.po_distribution_id = p_po_distribution_id
and aid.po_distribution_id = pd.po_distribution_id
and nvl(aid.org_id,-1) = nvl(fs.org_id,-1)
/* and fs.inv_encumbrance_type_id <> fs.purch_encumbrance_type_id Bug 14063588*/
and NVL(PD.accrue_on_receipt_flag,'N') = 'N'
AND AID.po_distribution_id is not null
AND nvl(aid.match_status_flag, 'N') = 'A'
AND nvl(aid.encumbered_flag, 'N') = 'Y'
/* AND nvl(aid.historical_flag, 'N') = 'Y' Bug 14063588*/
AND aid.line_type_lookup_code NOT IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV', 'QV', 'AV')
AND (aid.accrual_posted_flag = 'Y' or aid.cash_posted_flag = 'Y')
AND (( p_start_gl_date is not null and p_start_gl_date <= aid.accounting_date) or (p_start_gl_date is null))
AND ((p_end_gl_date is not null and p_end_gl_date >= aid.accounting_date) or (p_end_gl_date is null))
AND NOT EXISTS (SELECT 'release 11.5 encumbrance'
from ap_encumbrance_lines_all ael
where ael.invoice_distribution_id = aid.invoice_distribution_id)
-- bug 7225570
AND aid.bc_event_id is null
AND NOT EXISTS (SELECT 'release 11.5 encumbrance tax'
from ap_encumbrance_lines_all ael
where ael.invoice_distribution_id = aid.charge_applicable_to_dist_id);
SELECT book_type_code
INTO l_asset_book
FROM fa_book_controls fc
WHERE fc.book_class = 'CORPORATE' -- bug 8843743: modify
AND fc.set_of_books_id = p_ledger_id
AND fc.date_ineffective IS NULL;
SELECT book_type_code
INTO l_asset_book
FROM fa_book_controls fc
WHERE fc.book_class = 'CORPORATE' -- bug 8843743: modify
AND fc.set_of_books_id = p_ledger_id
AND fc.date_ineffective IS NULL;
EXECUTE IMMEDIATE 'SELECT '|| l_segment_num ||
' from gl_code_combinations where code_combination_id = :a '
INTO l_nat_account USING p_ccid;
select bal_seg_value_option_code
into l_bal_seg_value_option_code
from gl_ledgers
where ledger_id = p_set_of_books_id;
SELECT 'Y'
INTO l_valid
FROM gl_ledger_segment_values glsv
WHERE glsv.segment_value = p_balancing_segment_value
AND glsv.segment_type_code = 'B'
AND glsv.ledger_id = p_set_of_books_id
AND p_date BETWEEN NVL(glsv.start_date, p_date)
AND NVL(glsv.end_date, p_date)
AND rownum = 1;
SELECT NVL(P_Batch_GL_Date,
DECODE(SP.gl_date_from_receipt_flag,
'S',TRUNC(SYSDATE),
'Y',y_date,
'N',n_date,
TRUNC(P_Date)))
INTO l_current_date
FROM ap_system_parameters_all SP --5126689
WHERE sp.org_id = p_org_id;
SELECT period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND trunc(P_Date) BETWEEN start_date AND end_date
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT MIN(start_date),
period_name
FROM gl_period_statuses GLPS,
ap_system_parameters_all SP
WHERE application_id = 200
AND sp.org_id = P_Org_Id
AND GLPS.set_of_books_id = SP.set_of_books_id
AND end_date >= P_Date --Bug6809792
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N'
GROUP BY period_name
ORDER BY MIN(start_date);