[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query_stmt := 'select ' || p_column || ' ' ||
'from ' ||
'(SELECT ' || l_column1 || ' ' || p_column || ' ' ||
'FROM PER_EMPLOYEES_X EMP ' ||
'WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)=''Y'' ' ||
'AND EMP.EMPLOYEE_ID = :b1 ' ||
'UNION ALL ' ||
'SELECT ' || l_column2 || ' ' || p_column || ' ' ||
'FROM PER_CONT_WORKERS_CURRENT_X CWK ' ||
'WHERE CWK.PERSON_ID = :b2) wf';
select violation_type,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_POL_VIOLATION_TYPES',violation_type) violation
from AP_POL_VIOLATIONS_ALL
where report_header_id = p_report_header_id
and distribution_line_number = p_distribution_line_number
AND violation_type <> 'DUPLICATE_DETECTION'
order by violation_number;
SELECT violation_type, 'Duplicate Detection' violation,
dup_report_header_id, To_Char(dup_report_line_id) dup_report_line_id, dup_dist_line_number
FROM ap_pol_violations_all
WHERE report_header_id = p_report_header_id
AND distribution_line_number = p_distribution_line_number
AND violation_type = 'DUPLICATE_DETECTION'
ORDER BY violation_number;
SELECT nvl(WEB_FRIENDLY_PROMPT, PROMPT) expense_type_prompt
FROM AP_EXPENSE_REPORT_PARAMS_ALL
WHERE PARAMETER_ID = p_parameter_id;
select min(allowable_amount) allowable_amount
from ap_pol_violations_all
where report_header_id = p_report_header_id
and distribution_line_number = p_distribution_line_number
and violation_type in ('DAILY_LIMIT','INDIVIDUAL_LIMIT');
select min(allowable_amount) allowable_cc_amount
from ap_pol_violations_all
where report_header_id = p_report_header_id
and distribution_line_number = p_distribution_line_number
and violation_type in ('CC_REQUIRED');
select min(allowable_amount) allowable_amount
from ap_pol_violations_all
where report_header_id = p_report_header_id
and distribution_line_number = p_distribution_line_number
and violation_type in ('DAILY_SUM_LIMIT');
select aerh.payment_currency_code reimbursement_currency_code,
aerl.start_expense_date,
aerl.receipt_currency_code,
eo.exchange_rate_id,
eo.exchange_rate_type,
eo.exchange_rate_allowance,
eo.overall_tolerance,
eo.org_id
from AP_POL_EXRATE_OPTIONS_ALL eo,
AP_EXPENSE_REPORT_LINES_ALL aerl,
AP_EXPENSE_REPORT_HEADERS_ALL aerh,
AP_POL_VIOLATIONS_ALL pv
where aerl.report_header_id = pv.report_header_id
and aerl.distribution_line_number = pv.distribution_line_number
and aerl.credit_card_trx_id is null
and aerh.report_header_id = aerl.report_header_id
and aerh.org_id = aerl.org_id
and eo.org_id = aerl.org_id
and eo.enabled = 'Y'
and pv.report_header_id = p_report_header_id
and pv.distribution_line_number = p_distribution_line_number
and pv.violation_type in ('EXCHANGE_RATE_LIMIT');
l_query_stmt := 'select '||p_column||' result from '||p_table||' where '||p_key_column||' = :b1 '||p_order_by_clause;
select prompt web_prompt
from ap_expense_report_lines_all aerl,
ap_expense_report_params_all aerp
where aerl.report_header_id = p_report_header_id
and aerl.distribution_line_number = p_distribution_line_number
and aerp.parameter_id = aerl.web_parameter_id;
l_query_stmt := 'select '||p_column||' result from ap_expense_report_lines_all where report_header_id = :b1 and distribution_line_number = :b2';
select fs.id_flex_structure_code
from ap_system_parameters_all so, gl_sets_of_books sb, FND_ID_FLEX_STRUCTURES fs
where so.org_id = p_org_id
and sb.set_of_books_id = so.set_of_books_id
and application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = sb.chart_of_accounts_id;
select employee_id
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
select employee_id, show_audit_header_flag
from ap_web_preferences
where employee_id = p_employee_id
FOR UPDATE OF show_audit_header_flag NOWAIT;
INSERT INTO ap_web_preferences(
employee_id,
show_audit_header_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
) VALUES (
emp_rec.employee_id,
NVL(p_show_header,'Y'),
sysdate, /* last_update_date */
nvl(fnd_global.user_id, -1), /* last_updated_by*/
sysdate, /* creation_date */
nvl(fnd_global.user_id, -1), /* created_by */
fnd_global.conc_login_id /* last_update_login */
);
UPDATE ap_web_preferences
SET show_audit_header_flag = NVL(p_show_header,'Y')
WHERE CURRENT OF pref_cur;
select pref.employee_id, NVL(pref.show_audit_header_flag, 'Y') show_header_flag
from ap_web_preferences pref, fnd_user usr
where usr.user_id = FND_GLOBAL.USER_ID
and pref.employee_id = usr.employee_id;
select count(rule_assignment_id) assignment_count
from ap_aud_rule_assignments_all
where rule_set_id = p_rule_set_id;
l_query_stmt := 'select '||p_column||' result from AP_AUD_WORKLOADS where auditor_id = :b1 and sysdate between start_date and NVL(end_date,sysdate+1)';
select audit_reason_code,
AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_REASONS',audit_reason_code) audit_reason
from AP_AUD_AUDIT_REASONS
where report_header_id = p_report_header_id
order by audit_reason_id;
SELECT WF.ORGANIZATION_ID
FROM
(SELECT EMP.ORGANIZATION_ID ORGANIZATION_ID,
EMP.EMPLOYEE_ID PERSON_ID
FROM PER_EMPLOYEES_X EMP
WHERE NOT AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(EMP.EMPLOYEE_ID)='Y'
AND EMP.EMPLOYEE_ID = p_person_id
UNION ALL
SELECT CWK.ORGANIZATION_ID ORGANIZATION_ID,
CWK.PERSON_ID
FROM PER_CONT_WORKERS_CURRENT_X CWK
WHERE CWK.PERSON_ID = p_person_id) WF;
select aerh.employee_id, aerh.org_id
from AP_EXPENSE_REPORT_HEADERS_ALL aerh
where aerh.report_header_id = p_report_header_id;
select count(1) required_count
from AP_EXPENSE_REPORT_LINES_ALL aerl
where aerl.report_header_id = p_report_header_id
and nvl(aerl.receipt_required_flag, 'N') = 'Y';
select rs.audit_term_duration_days
from AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where rsa.org_id = p_org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'AUDIT_LIST'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
SELECT auto_audit_id
FROM ap_aud_auto_audits
WHERE employee_id = p_employee_id
AND trunc(sysdate) between trunc(start_date) and trunc(NVL(end_date, sysdate));
select DECODE(usr.user_id,
-1, fnd_message.GET_STRING('SQLAP','OIE_AUD_FALLBACK_AUDITOR'),
NVL(AP_WEB_AUDIT_UTILS.get_employee_info(usr.employee_id,'full_name','VARCHAR2'),
usr.user_name)) auditor_name
from fnd_user usr
where usr.user_id = p_auditor_id;
select rs.rule_set_id
from AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where rsa.org_id = p_org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = p_rule_type
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
SELECT hou.name org_name
FROM per_organization_list per,
HR_ORGANIZATION_UNITS hou
WHERE per.organization_id = hou.organization_id
AND per.security_profile_id = p_security_profile_id;
SELECT FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', u.user_id, r.responsibility_id, 200/*SQLAP*/) security_profile_id
FROM FND_USER u,
FND_USER_RESP_GROUPS g,
FND_RESPONSIBILITY r,
FND_FORM_FUNCTIONS f
WHERE u.user_id = g.user_id
AND u.user_id = p_user_id
AND g.responsibility_id = r.responsibility_id
AND AP_WEB_AUDIT_QUEUE_UTILS.IS_FUNCTION_ON_MENU(r.menu_id, f.function_id, 'Y') = 'Y'
AND f.function_name = 'OIE_AUD_AUDIT';
SELECT site.invoice_currency_code default_currency_code
FROM ap_suppliers vdr,
ap_supplier_sites_all site,
ap_expense_report_headers_all aerh,
financials_system_params_all fp
WHERE aerh.report_header_id = p_report_header_id
AND aerh.org_id = site.org_id
AND aerh.org_id = fp.org_id
AND site.vendor_id = vdr.vendor_id
AND vdr.employee_id = aerh.employee_id
AND upper(site.vendor_site_code) = UPPER(AP_WEB_POLICY_UTILS.get_lookup_meaning('HOME_OFFICE', fp.expense_check_address_flag));
SELECT nvl(vdr.invoice_currency_code,sp.base_currency_code) default_currency_code
FROM ap_suppliers vdr,
ap_expense_report_headers_all aerh,
ap_system_parameters_all sp
WHERE vdr.employee_id = aerh.employee_id
AND aerh.report_header_id = p_report_header_id
AND aerh.org_id = sp.org_id;
SELECT get_available_prepays(vdr.vendor_id) advance_count,
aerh.employee_id
FROM ap_invoices_all i,
ap_expense_report_headers_all aerh,
ap_suppliers vdr
WHERE i.vendor_id = vdr.vendor_id
AND aerh.report_header_id = p_report_header_id
AND vdr.employee_id = aerh.employee_id
AND i.org_id = aerh.org_id
AND i.invoice_type_lookup_code||'' = 'PREPAYMENT'
AND i.earliest_settlement_date IS NOT NULL
AND i.invoice_currency_code = p_default_currency_code
AND ROWNUM = 1;
SELECT nvl(sum(maximum_amount_to_apply),0) applied_prepayment
FROM ap_expense_report_headers_all aerh
WHERE aerh.employee_id = p_employee_id
AND aerh.vouchno = 0
AND aerh.default_currency_code = p_default_currency_code
GROUP BY aerh.employee_id;
SELECT nvl(sum( get_prepay_amount_remaining(i.invoice_id) ), 0) remaining_prepayment
FROM ap_invoices_all i, ap_suppliers vdr
WHERE i.vendor_id = vdr.vendor_id
AND vdr.employee_id = p_employee_id
AND i.invoice_type_lookup_code||'' = 'PREPAYMENT'
AND i.earliest_settlement_date IS NOT NULL
AND i.payment_status_flag||'' = 'Y'
AND i.invoice_currency_code = p_default_currency_code
GROUP BY vdr.employee_id;
SELECT closing_status
FROM gl_period_statuses_v
WHERE application_id = 200
and set_of_books_id = p_set_of_books_id
and adjustment_period_flag = 'N'
and p_gl_date between start_date and end_date;
SELECT max(end_date) default_date
FROM gl_period_statuses_v
WHERE application_id = 200
and set_of_books_id = p_set_of_books_id
and adjustment_period_flag = 'N'
and start_date < p_gl_date
and closing_status in ('O', 'F');
SELECT erl.report_header_id
FROM ap_expense_report_lines_all erl
WHERE erl.report_header_id = p_report_header_id
AND erl.credit_card_trx_id is not null
AND erl.amount <> NVL(erl.submitted_amount,erl.amount);
SELECT erl.report_header_id
FROM ap_expense_report_lines_all erl
WHERE erl.report_header_id = p_report_header_id
AND erl.itemization_parent_id is not null
AND erl.itemization_parent_id <> -1
AND NVL(erl.policy_shortpay_flag,'N') = 'Y';
SELECT cp.org_id, cp.expense_clearing_ccid
FROM ap_card_programs_all cp,
ap_credit_card_trxns_all cct
WHERE cp.card_program_id = cct.card_program_id
AND cct.trx_id = p_trx_id;
SELECT EXPENSE_CLEARING_CCID
FROM FINANCIALS_SYSTEM_PARAMS_ALL
WHERE org_id = p_org_id;
SELECT cct.payment_due_from_code
FROM ap_credit_card_trxns_all cct
WHERE cct.trx_id = p_trx_id;
SELECT parameter_id
FROM ap_expense_report_params erp
WHERE erp.expense_type_code = p_expense_type_code;
SELECT max(distribution_line_number) + 1
FROM AP_EXPENSE_REPORT_LINES_ALL
WHERE report_header_id = p_report_header_id;
SELECT ROUNDING_ERROR_CCID
FROM ap_system_parameters_all
WHERE org_id = p_org_id;
SELECT DECODE(usr.employee_id,
null, usr.user_name,
AP_WEB_AUDIT_UTILS.get_employee_info(usr.employee_id,'full_name','VARCHAR2')) last_audited_by_name
FROM fnd_user usr
WHERE usr.user_id = p_user_id;
SELECT description
INTO l_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT
aerh.employee_id employee_id,
aerh.flex_concatenated header_cost_center,
aerd.report_line_id report_line_id,
aerd.cost_center line_cost_center
FROM
ap_expense_report_headers_all aerh,
ap_exp_report_dists_all aerd
WHERE aerh.report_header_id = p_report_header_id
AND aerd.report_header_id = aerh.report_header_id
AND aerd.report_distribution_id = p_report_distribution_id;
select NVL(aerl.NUM_PDM_DAYS1,0) NUM_PDM_DAYS1,
NVL(aerl.NUM_PDM_DAYS2,0) NUM_PDM_DAYS2,
NVL(aerl.NUM_PDM_DAYS3,0) NUM_PDM_DAYS3,
NVL(aerl.PER_DIEM_RATE1,0) PER_DIEM_RATE1,
NVL(aerl.PER_DIEM_RATE2,0) PER_DIEM_RATE2,
NVL(aerl.PER_DIEM_RATE3,0) PER_DIEM_RATE3,
NVL(end_expense_date - start_expense_date,0)+1 number_of_days,
ph.day_period_code
from ap_expense_report_lines_all aerl,
ap_pol_headers ph,
ap_expense_report_params_all erp
where aerl.report_header_id = p_report_header_id
and aerl.distribution_line_number = p_distribution_line_number
and aerl.category_code = AP_WEB_POLICY_UTILS.c_PER_DIEM
and erp.parameter_id = aerl.web_parameter_id
and ph.policy_id = erp.company_policy_id;
select rs.auto_approval_tag,
rs.requires_audit_tag,
rs.paperless_audit_tag,
aerh.audit_code,
rs.image_audit_tag,
nvl(rs.aud_img_receipt_required,'X') aud_img_receipt_required,
nvl(rs.aud_paper_receipt_required, 'X') aud_paper_receipt_required,
nvl(aerh.image_receipts_status,'NOT_REQUIRED') hdr_img_receipt_required,
nvl(aerh.receipts_status,'NOT_REQUIRED') hdr_paper_receipt_required
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where aerh.report_header_id = p_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RULE'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
select aerh.source,
AERH.Expense_Status_Code,
AERH.Workflow_approved_flag,
AERH.AMT_DUE_CCARD_COMPANY,
AERH.AMT_DUE_EMPLOYEE,
AI.Payment_status_flag,
APS.GROSS_AMOUNT,
AI.CANCELLED_DATE
from ap_expense_report_headers_all aerh,
AP_INVOICES_ALL AI,
AP_PAYMENT_SCHEDULES_ALL APS
where AI.INVOICE_ID(+) = AERH.VOUCHNO
and APS.INVOICE_ID(+) = AI.INVOICE_ID
and aerh.report_header_id = p_report_header_id;
SELECT AI.Payment_status_flag,
APS.GROSS_AMOUNT,
AI.CANCELLED_DATE
from AP_INVOICES_ALL AI,
AP_PAYMENT_SCHEDULES_ALL APS
where AI.INVOICE_ID= APS.INVOICE_ID
AND AI.INVOICE_ID = p_invoice_id;
-- Update cache
grsc_old_status_code := l_status_code;
SELECT SUM(nvl(aid.prepay_amount_remaining,ail.amount))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions aid
WHERE ail.invoice_id = P_invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND ail.line_type_lookup_code IN ('ITEM','TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y';
SELECT SUM(DECODE(payment_status_flag,
'Y', DECODE(SIGN(earliest_settlement_date - SYSDATE),
1,0,
1),
0))
INTO prepay_count
FROM ap_invoices_all ai
WHERE vendor_id = l_vendor_id
AND invoice_type_lookup_code = 'PREPAYMENT'
AND earliest_settlement_date IS NOT NULL
AND get_prepay_amount_remaining(ai.invoice_id) > 0;
select rs.*
from AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where rsa.org_id = p_org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = p_rule_type
and TRUNC(p_date)
BETWEEN TRUNC(NVL(rsa.START_DATE,p_date))
AND TRUNC(NVL(rsa.END_DATE,p_date));
select nvl(trxn_detail_flag, 'N') as trxn_detail_flag
from ap_credit_card_trxns_all
where trx_id = p_cc_trx_id;
select 'Y'
into l_return
from OIE_ATTENDEES_ALL
where p_reportLineId = report_line_id
and rownum = 1;
select displayed_field
into l_return
from AP_LOOKUP_CODES
where lookup_type = 'OIE_ATTENDEE_TYPE'
and lookup_code = p_attendeeCode;
delete
from ap_aud_audit_reasons
where report_header_id = p_report_header_id
and audit_reason_code <> 'RANDOM';
SELECT
report_header_id,
report_line_id
INTO
l_report_header_id,
l_report_line_id
FROM
ap_exp_report_dists_all
WHERE
report_distribution_id = p_report_distribution_id;