[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pmt.payment_id,
ins.payment_document_id,
pmt.payment_method_code,
ce.bank_acct_use_id
FROM iby_fd_payments_v pmt,
iby_pay_instructions_all ins,
iby_payment_profiles ipp,
ce_bank_acct_uses_all ce
WHERE pmt.completed_pmts_group_id = p_completed_pmts_group_id
AND ipp.payment_profile_id = ins.payment_profile_id
AND ins.payment_instruction_id = pmt.payment_instruction_id
AND ce.org_id = pmt.org_id
AND ce.bank_account_id = pmt.internal_bank_account_id;
SELECT process_type
INTO l_process_type
FROM iby_payments_all
WHERE completed_pmts_group_id = p_completed_pmts_group_id
AND rownum =1;
SELECT COUNT(*) validation1
INTO l_err_count
FROM iby_fd_payments_v pmts
WHERE pmts.completed_pmts_group_id = p_completed_pmts_group_id
AND NOT EXISTS
(SELECT 1
FROM ap_system_parameters_all asp,
gl_code_combinations gc,
gl_sets_of_books gsob,
fnd_currencies fndc
WHERE gc.code_combination_id(+) = asp.interest_code_combination_id
AND gsob.set_of_books_id = asp.set_of_books_id
AND asp.org_id = pmts.org_id
AND base_currency_code = fndc.currency_code
);
/* Number of selected invoices that should result in interest invoices: */
select count(*)
into l_int_inv_expected_count
from ap_selected_invoices_all asi
, iby_fd_docs_payable_v ibydocs
where asi.original_invoice_id is not null
and ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(asi.checkrun_id)
and ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(asi.invoice_id)
and ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(asi.payment_num)
and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id;
SELECT COUNT(*) validation2
INTO l_int_inv_actual_count
FROM iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
ap_supplier_sites_all aps,
ap_selected_invoices_all apsi,
ap_invoices_all orig
WHERE ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
--AND ibypmts.org_id = :l_current_org_id
AND ibypmts.org_type = 'OPERATING_UNIT'
AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
AND aps.vendor_site_id(+) = ibypmts.supplier_site_id
AND apsi.original_invoice_id = orig.invoice_id;
SELECT COUNT(*) validation3
INTO l_int_line_actual_count
FROM iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
po_vendors pv,
ap_selected_invoices_all apsi,
ap_invoices_all orig
WHERE ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
--AND ibypmts.org_id = :l_current_org_id
AND ibypmts.org_type = 'OPERATING_UNIT'
AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
AND apsi.vendor_id = pv.vendor_id
AND apsi.original_invoice_id = orig.invoice_id;
SELECT (
CASE
WHEN COUNT(*) > 0
THEN 0
ELSE 1
END) validation4
INTO l_err_count
FROM ap_lookup_codes lc
WHERE lookup_type = 'NLS TRANSLATION'
AND lookup_code = 'INTEREST ON PAYMENTBATCH';
SELECT COUNT(*) iby_check_count
INTO l_iby_pmt_count
FROM iby_fd_payments_v
WHERE completed_pmts_group_id = p_completed_pmts_group_id;
SELECT COUNT(*) ap_check_count
INTO l_ap_pmt_count
FROM iby_fd_payments_v iby,
po_vendors pv,
ce_bank_acct_uses_all ce,
ce_gl_accounts_ccid cegl,
ce_bank_accounts ceb,
ap_supplier_sites_all aps
WHERE iby.inv_payee_party_id = pv.party_id(+)
AND aps.vendor_site_id(+) = iby.supplier_site_id
AND NVL(pv.vendor_id,-99) =
(SELECT
CASE
WHEN inv.invoice_type_lookup_code = 'PAYMENT REQUEST'
AND SIGN(inv.vendor_id) = -1
THEN NVL(pv.vendor_id, -99)
ELSE NVL(vendor_id, -99)
END
FROM ap_invoices_all inv,
iby_docs_payable_all idp
WHERE inv.invoice_id =idp.calling_app_doc_unique_ref2
AND idp.payment_id =iby.payment_id
AND idp.payment_service_request_id = iby.payment_service_request_id
AND rownum =1
)
AND ce.bank_account_id = iby.internal_bank_account_id
AND ceb.bank_account_id = ce.bank_account_id
AND iby.org_type = 'OPERATING_UNIT'
AND ce.org_id = iby.org_id
AND ce.bank_acct_use_id = cegl.bank_acct_use_id
AND iby.completed_pmts_group_id = p_completed_pmts_group_id;
SELECT COUNT(*) iby_doc_count
INTO l_iby_doc_count
FROM iby_fd_docs_payable_v
WHERE payment_id IN
(SELECT payment_id
FROM iby_payments_all
WHERE completed_pmts_group_id = p_completed_pmts_group_id
);
SELECT COUNT(*) ap_pmt_sch_count
INTO l_ap_pmtsch_count
FROM iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
ap_selected_invoices_all SI,
fnd_currencies FORE,
ap_payment_schedules_all PS,
ap_invoices_all AI
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(si.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(si.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(si.payment_num)
AND ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND ibypmts.payment_currency_code = FORE.currency_code
AND PS.invoice_id(+) = SI.invoice_id
AND PS.payment_num(+) = SI.payment_num
AND AI.invoice_id = SI.invoice_id;
select count(*)
into l_err_count
from ap_invoices_all ai
, ap_selected_invoices_all apsi
, iby_fd_docs_payable_v ibydocs
where ai.source in ( 'Both Pay')
and (nvl(ai.paid_on_behalf_employee_id, -1) <> -1
or ai.invoice_type_lookup_code = 'EXPENSE REPORT')
and ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
and ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
and ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
and apsi.invoice_id = ai.invoice_id
and exists
(SELECT 1
FROM ap_expense_report_headers_all aerh1,
ap_expense_report_headers_all aerh2
WHERE aerh1.bothpay_parent_id = aerh2.report_header_id
AND aerh1.invoice_num = ai.invoice_num
AND aerh1.org_id = ai.org_id
AND aerh1.source = 'Both Pay'
group by aerh1.invoice_num
having count(*) > 1);
select
(CASE
WHEN COUNT(*) > 0
THEN 0
ELSE 1
END) validationG2
into l_err_count
from iby_ext_bank_accounts_v eba
, iby_fd_payments_v ibypmts
where eba.ext_bank_account_id = ibypmts.external_bank_account_id
and ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
and ibypmts.external_bank_account_id is not null;
SELECT COUNT(*)
INTO l_err_count
FROM iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
ap_selected_invoices_all SI,
ap_awt_temp_distributions_all atd
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(si.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(si.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(si.payment_num)
AND ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND atd.invoice_id = si.invoice_id
and not exists
(SELECT 1
FROM gl_Period_Statuses gps,
ap_System_Parameters_All Asp
WHERE gps.Application_Id = 200
AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
AND atd.accounting_date BETWEEN Trunc(gps.Start_Date)
AND Trunc(gps.End_Date)
AND Nvl(Asp.Org_Id,- 99) = Nvl(atd.org_id,- 99)
AND gps.closing_Status in ('O', 'F'));