[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* select exchange_rate */
select decode( nvl(fnd_profile.value('DISPLAY_INVERSE_RATE'),'N'),'N',exchange_rate,
'Y', 1/exchange_rate)
into l_rate
from ap_user_exchange_rates
where checkrun_id = p_checkrun_id
and ledger_currency_code = p_base_currency_code
and payment_currency_code = p_payment_currency_code;
select check_id
from ap_checks_all
where completed_pmts_group_id = p_completed_pmts_group_id
and org_id = p_current_org_id;
select next_voucher_number
into l_next_voucher_number
from ap_inv_selection_criteria_all
where checkrun_id = p_checkrun_id
for update;
update ap_checks_all
set check_voucher_num = l_next_voucher_number
where check_id = l_check_id;
l_debug_info := 'updating ap_inv_selection_criteria_all with voucher number ';
update ap_inv_selection_criteria_all
set next_voucher_number = l_next_voucher_number
where checkrun_id = p_checkrun_id;
SELECT i.invoice_id
FROM ap_invoice_payments_all aip,
ap_checks_all c,
ap_invoices_all i,
ap_invoice_relationships ir
WHERE c.check_id = p_check_id
AND c.check_id = aip.check_id
AND aip.invoice_id = ir.related_invoice_id
AND aip.invoice_id = i.invoice_id
AND i.invoice_type_lookup_code = 'INTEREST';
update ap_invoices_all
set doc_sequence_id = l_int_dbseqid,
doc_sequence_value = l_int_seqval,
doc_category_code = 'INT INV'
where invoice_id = l_invoice_id;
select ac.check_id,
ac.payment_document_id,
ac.payment_method_code,
ac.ce_bank_acct_use_id
from ap_checks_all ac,
iby_payment_profiles ipp
where completed_pmts_group_id = p_completed_pmts_group_id
and ac.checkrun_id = p_checkrun_id --bug15924350
and ipp.payment_profile_id = ac.payment_profile_id
and ac.org_id = p_current_org_id;
update ap_checks_all
set doc_sequence_id = l_dbseqid,
doc_sequence_value = l_seqval,
doc_category_code = l_doc_category_code
where check_id = l_check_id;
l_checkrun_name ap_inv_selection_criteria_all.checkrun_name%type;
select distinct calling_app_doc_unique_ref1
from iby_fd_docs_payable_v
where rejected_docs_group_id = p_rejected_docs_group_id;
SELECT invoice_id,
vendor_id,
payment_num
FROM ap_SELECTed_invoices_all ASI,
ap_system_parameters_all asp,
iby_fd_docs_payable_v ibydocs
WHERE checkrun_name = l_checkrun_name
AND original_invoice_id IS NULL
/* Bug 6950891. Added TO_CHAR */
/* Bug 12730662. Removed TO_CHAR */
AND ibydocs.calling_app_doc_unique_ref1 = ASI.checkrun_id
AND ibydocs.calling_app_doc_unique_ref2 = ASI.invoice_id
AND ibydocs.calling_app_doc_unique_ref3 = ASI.payment_num
AND ibydocs.rejected_docs_group_id = p_rejected_docs_group_id
AND asp.org_id = asi.org_id
and ibydocs.org_id=asp.org_id
and ibydocs.calling_app_id = 200
and checkrun_id = l_checkrun_id
AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
decode(ASP.create_awt_dists_type,'BOTH','Y','PAYMENT',
'Y', decode(ASP.create_awt_invoices_type,'BOTH','Y','PAYMENT',
'Y', 'N'),
'N'),--Bug6660355
'N') = 'Y';
select checkrun_name, check_date
into l_checkrun_name, l_check_date
from ap_inv_selection_criteria_all
where checkrun_id = l_checkrun_id;
l_debug_info := 'Fetch CURSOR for all SELECTed invoices';
,P_Last_Updated_By => to_number(FND_PROFILE.VALUE('USER_ID'))
,P_Last_Update_Login => to_number(FND_PROFILE.VALUE('LOGIN_ID'))
,P_Program_Application_Id => to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID'))
,P_Program_Id => to_number(FND_PROFILE.VALUE('PROGRAM_ID'))
,P_Request_Id => to_number(FND_PROFILE.VALUE('REQUEST_ID'))
,P_Awt_Success => undo_output
,P_checkrun_id => l_checkrun_id );
l_debug_info := 'CLOSE CURSOR for all SELECTed invoices';
delete from ap_unselected_invoices_all
where checkrun_id = l_checkrun_id;
delete from ap_selected_invoices_all
where checkrun_id = l_checkrun_id
/* Bug 6950891. Added TO_CHAR */
/* Bug 12679356. Removed to_char */
and (invoice_id, payment_num) in
(select calling_app_doc_unique_ref2,
calling_app_doc_unique_ref3
from iby_fd_docs_payable_v
where rejected_docs_group_id = p_rejected_docs_group_id);
update ap_payment_schedules_all
set checkrun_id = null
where checkrun_id = l_checkrun_id
/* Bug 6950891. Added TO_CHAR */
/* Bug 12679356. Removed to_char */
and (invoice_id, payment_num) in
(select calling_app_doc_unique_ref2,
calling_app_doc_unique_ref3
from iby_fd_docs_payable_v
where rejected_docs_group_id = p_rejected_docs_group_id);
l_last_updated_by number;
SELECT distinct calling_app_doc_unique_ref1
FROM iby_fd_payments_v pmts,
iby_fd_docs_payable_v docs
WHERE pmts.payment_id = docs.payment_id
AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
AND pmts.org_type = 'OPERATING_UNIT';
SELECT distinct pmts.org_id
FROM iby_fd_payments_v pmts,
iby_fd_docs_payable_v docs
WHERE pmts.payment_id = docs.payment_id
AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
AND pmts.org_type = 'OPERATING_UNIT'
AND docs.calling_app_doc_unique_ref1 = c_checkrun_id;
SELECT Distinct AC.Check_ID,
APR.Reg_Application_ID
FROM AP_Checks_All AC,
AP_Invoice_Payments_All AIP,
AP_Invoices_All AI,
AP_Product_Registrations APR
WHERE AC.Checkrun_Name = l_checkrun_name
AND AC.Completed_Pmts_Group_ID = p_completed_pmts_group_id
AND AC.Org_ID = l_current_org_id
AND AC.Check_ID = AIP.Check_ID
AND AIP.Invoice_ID = AI.Invoice_ID
AND AI.Application_ID = APR.Reg_Application_ID
AND APR.Registration_Event_Type = 'PAYMENT_CREATED';
CURSOR c_invoice_amounts(p_last_updated_by Number,
p_completed_group_id Number,
p_current_org_id Number,
p_checkrun_name Varchar2) IS
SELECT sysdate,
p_last_updated_by,
iby_amount_paid,
iby_discount_amount_taken,
AP_INVOICES_UTILITY_PKG.get_payment_status(inv.invoice_id),
inv.invoice_id
FROM ap_invoices_all inv,
ap_selected_invoices_all si,
(SELECT sum(ibydocs.payment_amount) iby_amount_paid,
nvl(sum(ibydocs.payment_curr_discount_taken),0) iby_discount_amount_taken,
ibydocs.calling_app_doc_unique_ref1 ref1,
ibydocs.calling_app_doc_unique_ref2 ref2,
ibydocs.calling_app_doc_unique_ref3 ref3
FROM iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
WHERE ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_group_id
AND ibypmts.org_id = p_current_org_id
GROUP BY ibydocs.calling_app_doc_unique_ref1,
ibydocs.calling_app_doc_unique_ref2,
ibydocs.calling_app_doc_unique_ref3) ibydpm
WHERE inv.invoice_id = si.invoice_id
AND si.checkrun_name = p_checkrun_name
AND inv.invoice_type_lookup_code <> 'INTEREST'
AND ibydpm.ref2 = to_char(inv.invoice_id)
AND ibydpm.ref1 = to_char(si.checkrun_id)
AND ibydpm.ref2 = to_char(si.invoice_id)
AND ibydpm.ref3 = to_char(si.payment_num);
CURSOR c_schedule_amounts(p_last_updated_by Number,
p_completed_group_id Number,
p_current_org_id Number,
p_checkrun_name Varchar2) IS
SELECT sysdate,
p_last_updated_by,
(si.amount_remaining - ibydocs.payment_amount -
nvl(ibydocs.payment_curr_discount_taken,0)),
0,
decode(si.amount_remaining - ibydocs.payment_amount -
nvl(ibydocs.payment_curr_discount_taken,0), 0,
'Y', 'P'),
/* commented by zrehman for Bug#6836199 on 24-Jun-2008
(si.amount_remaining - si.proposed_payment_amount -
nvl(ibydocs.payment_curr_discount_taken,0)),
0,
decode(si.amount_remaining - si.proposed_payment_amount -
nvl(ibydocs.payment_curr_discount_taken,0), 0,
'Y', 'P'),*/
-- Added by epajaril to capture the AWT
-- Bug8477014: Undoing changes done for bug6836199
-- Bug8752557: Undoing changes done here for bug8477014
si.withholding_amount,
Null,
ps.invoice_id,
ps.payment_num
FROM ap_payment_schedules_all ps,
ap_invoices_all inv,
ap_selected_invoices_all si,
/*iby_fd_payments_v ibypmts, Commented for Bug#9459810 */
iby_fd_docs_payable_v ibydocs
WHERE si.checkrun_name = p_checkrun_name
AND si.payment_num = ps.payment_num
AND si.invoice_id = ps.invoice_id
AND 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_group_id
AND ibypmts.org_id = p_current_org_id
AND ibypmts.org_type = 'OPERATING_UNIT' Commented for bug#9459810*/
/* Added for bug#9459810 Start */
AND ibydocs.completed_pmts_group_id = p_completed_group_id
AND ibydocs.org_id = p_current_org_id
AND ibydocs.org_type = 'OPERATING_UNIT'
/* Added for bug#9459810 End */
AND inv.invoice_id = si.invoice_id
AND inv.invoice_id = ps.invoice_id
AND inv.invoice_type_lookup_code <> 'INTEREST';
last_update_date_inv_l t_date_tab;
last_updated_by_inv_l t_number_tab;
last_update_date_ps_l t_date_tab;
last_updated_by_ps_l t_number_tab;
select payment_id, payment_reference_number, payment_instruction_id
, null, null, 'IBY_PMT'
FROM iby_fd_payments_v ifp
WHERE ifp.completed_pmts_group_id = p_completed_pmts_group_id
and not exists
(select 1
from ap_checks_all c
where c.completed_pmts_group_id = ifp.completed_pmts_group_id
and c.payment_id = ifp.payment_id);
select ifp.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
, ifd.calling_app_doc_unique_ref2, ifd.calling_app_doc_unique_ref3
, 'IBY_DOC'
from iby_fd_docs_payable_v ifd
, iby_fd_payments_v ifp
where ifd.payment_id = ifp.payment_id (+)
and ifd.completed_pmts_group_id = p_completed_pmts_group_id
and not exists
(select 1
from ap_invoice_payments_all ip
, ap_checks_all c
where c.payment_id = ifd.payment_id
and c.completed_pmts_group_id = ifd.completed_pmts_group_id
and c.check_id = ip.check_id
and ifd.calling_app_doc_unique_ref2 = TO_CHAR(ip.invoice_id)
and ifd.calling_app_doc_unique_ref3 = TO_CHAR(ip.payment_num));
select c.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
, null, null, 'AP_CHECK'
from ap_checks_all c
, iby_fd_payments_v ifp
where c.completed_pmts_group_id = ifp.completed_pmts_group_id
and c.payment_id = ifp.payment_id
and c.completed_pmts_group_id = p_completed_pmts_group_id
group by c.payment_id, ifp.payment_reference_number, ifp.payment_instruction_id
having count(c.check_id) > 1;
select ifp.payment_id, ifp.payment_reference_number
, ifp.payment_instruction_id, TO_CHAR(ip.invoice_id), TO_CHAR(ip.payment_num)
, 'AP_INV_PAY'
from ap_invoice_payments_all ip
, ap_checks_all c
, iby_fd_payments_v ifp
where ip.check_id = c.check_id
and ifp.payment_id = c.payment_id
and ifp.completed_pmts_group_id = c.completed_pmts_group_id
and c.completed_pmts_group_id = p_completed_pmts_group_id
group by ifp.payment_id, ifp.payment_reference_number
, ifp.payment_instruction_id, ip.invoice_id, ip.payment_num
having count(ip.invoice_payment_id) > 1;
SELECT displayed_field
INTO l_nls_int_inv_desc
FROM ap_lookup_codes
WHERE lookup_type = 'NLS TRANSLATION'
AND lookup_code = 'INTEREST OVERDUE INVOICE';
SELECT calling_app_doc_unique_ref1
INTO l_checkrun_id
FROM iby_fd_payments_v pmts,
iby_fd_docs_payable_v docs
WHERE pmts.payment_id = docs.payment_id
AND pmts.completed_pmts_group_id = p_completed_pmts_group_id
AND pmts.org_type = 'OPERATING_UNIT'
AND rownum=1;
SELECT exchange_rate_type,
transfer_priority,
check_date,
checkrun_name,
first_voucher_number
INTO l_exchange_rate_type,
l_transfer_priority,
l_check_date, --use this for the exchange date also, PM confirmed
l_checkrun_name,
l_first_voucher_number
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = l_checkrun_id;
SELECT apt.term_id
INTO l_interest_terms_id
FROM ap_terms apt, ap_terms_lines atl
WHERE apt.term_id = atl.term_id
AND atl.due_days=0
AND nvl(end_date_active,sysdate+1) >= sysdate
AND rownum < 2;
l_last_updated_by := to_number(FND_GLOBAL.USER_ID);
SELECT nvl(when_to_account_pmt,'ALWAYS'),
--nvl(prorate_int_inv_across_dists,'N'),
interest_code_combination_id,
base_currency_code,
nvl(auto_calculate_interest_flag, 'N'),
interest_accts_pay_ccid,
DECODE(account_type, 'A','Y','N'),
to_char(sysdate, 'DD-MON-RR HH24:MI'),
gsob.name,
decode(l_batch_control_flag, 'Y', AP_BATCHES_S.nextval, null),
/* Added BOTH for performing AWT -- Bug 9697441 */
decode(nvl(ASP.allow_awt_flag, 'N'),
'Y', decode(ASP.create_awt_dists_type,
'PAYMENT', 'Y', 'BOTH', 'Y',
decode(ASP.create_awt_invoices_type,
'PAYMENT', 'Y', 'BOTH', 'Y',
'N')),
'N'),
asp.set_of_books_id,
ap_utilities_pkg.get_gl_period_name(l_check_date,l_current_org_id)
INTO l_when_to_account_payment,
--l_prorate_int_inv_across_dists,
l_interest_code_combination_id,
l_base_currency_code,
l_auto_calculate_interest_flag,
l_interest_accts_pay_ccid,
l_int_asset_tracking_flag,
l_report_date,
l_company_name,
l_int_batch_id,
l_perform_awt_flag,
l_set_of_books_id,
l_period_name
FROM ap_system_parameters_all asp,
gl_code_combinations gc,
gl_sets_of_books gsob
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 = l_current_org_id;
select minimum_accountable_unit, precision
into l_base_currency_mac, l_base_currency_precision
from fnd_currencies
where currency_code = l_base_currency_code;
l_debug_info := 'do interest invoice insertions';
INSERT INTO ap_invoices_all(
invoice_id,
last_update_date,
last_updated_by,
vendor_id,
invoice_num,
invoice_amount,
vendor_site_id,
amount_paid,
discount_amount_taken,
invoice_date,
invoice_type_lookup_code,
description,
batch_id,
amount_applicable_to_discount,
tax_amount,
terms_id,
terms_date,
voucher_num,
pay_group_lookup_code,
set_of_books_id,
accts_pay_code_combination_id,
invoice_currency_code,
payment_currency_code,
payment_status_flag,
posting_status,
creation_date,
created_by,
payment_cross_rate,
exchange_rate,
exchange_rate_type,
exchange_date,
base_amount,
source,
payment_method_code,
pay_curr_invoice_amount,
payment_cross_rate_date,
payment_cross_rate_type,
gl_date,
exclusive_payment_flag,
approval_ready_flag,
wfapproval_status,
legal_entity_id,
org_id,
party_id,
party_site_id,
-- added below columns for 7673570
remit_to_supplier_name,
remit_to_supplier_id,
remit_to_supplier_site,
remit_to_supplier_site_id,
relationship_id)
SELECT
new.invoice_id,
SYSDATE,
l_last_updated_by,
new.vendor_id,
new.invoice_num,
decode(fcinv.minimum_accountable_unit, null,
round((new.payment_amount/orig.payment_cross_rate),
fcinv.precision),
round((new.payment_amount/orig.payment_cross_rate)
/fcinv.minimum_accountable_unit)
* fcinv.minimum_accountable_unit),
new.vendor_site_id,
ibydocs.payment_amount,
0,
new.due_date,
'INTEREST',
new.invoice_description||orig.invoice_num,
l_int_batch_id,
null,
null,
orig.terms_id, /* bug 5124784. Terms will be the parent Invoice term. */
orig.terms_date,
orig.voucher_num,
orig.pay_group_lookup_code,
orig.set_of_books_id,
l_interest_accts_pay_ccid,
orig.invoice_currency_code,
orig.payment_currency_code,
'Y',
null,
SYSDATE,
l_last_updated_by,
orig.payment_cross_rate,
-- Start bug 8899917 use new instead of orig.
new.payment_exchange_rate,
new.payment_exchange_rate_type,
l_check_date, -- exchange_date
decode(orig.invoice_currency_code, l_base_currency_code,
NULL,
decode(l_base_currency_mac, null,
round(new.payment_amount / orig.payment_cross_rate *
nvl(new.payment_exchange_rate,1), l_base_currency_precision),
round( (new.payment_amount / orig.payment_cross_rate *
nvl(new.payment_exchange_rate,1)) /
l_base_currency_mac) *
l_base_currency_mac ) ),
--end bug 8899917
'Confirm PaymentBatch',
orig.payment_method_code,
new.payment_amount,
orig.payment_cross_rate_date,
orig.payment_cross_rate_type,
new.due_date,
new.exclusive_payment_flag,
'Y',
'NOT REQUIRED',
ibypmts.legal_entity_id,
ibypmts.org_id,
orig.party_id,
orig.party_site_id,
-- added below columns for 7673570
ibypmts.PAYEE_NAME,
NVL(aps.vendor_id,-222), -- Modified for bug 8405513
aps.vendor_site_code,
NVL(ibypmts.supplier_site_id,-222), --modifed for bug 8405513
ibypmts.relationship_id
FROM ap_invoices_all orig,
ap_supplier_sites_all aps, -- bug 7673570
iby_fd_payments_v ibypmts,
ap_selected_invoices_all new,
iby_fd_docs_payable_v ibydocs,
fnd_currencies fcinv
/* Bug 6950891 . Added TO_CHAR */
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
AND new.original_invoice_id = orig.invoice_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
AND fcinv.currency_code = orig.invoice_currency_code
AND ibypmts.org_id = l_current_org_id;
l_debug_info := 'do interest invoice line insertions';
INSERT INTO ap_invoice_lines_all(
INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ACCOUNTING_DATE,
PERIOD_NAME,
AMOUNT,
BASE_AMOUNT,
DESCRIPTION,
TYPE_1099,
SET_OF_BOOKS_ID,
ASSETS_TRACKING_FLAG,
-- ASSET_BOOK_TYPE_CODE, ??
-- ASSET_CATEGORY_ID, ??
LINE_SOURCE,
GENERATE_DISTS,
WFAPPROVAL_STATUS,
org_id)
SELECT new.invoice_id,
1,
'ITEM',
SYSDATE,
l_last_updated_by,
sysdate,
l_last_updated_by,
new.due_date,
l_period_name,
decode(fcinv.minimum_accountable_unit, null,
round((ibydocs.payment_amount/orig.payment_cross_rate),
fcinv.precision),
round((ibydocs.payment_amount/orig.payment_cross_rate)
/fcinv.minimum_accountable_unit)
* fcinv.minimum_accountable_unit),
decode(orig.invoice_currency_code, l_base_currency_code,
NULL,
decode(l_base_currency_mac, null,
round(new.payment_amount / orig.payment_cross_rate *
--bug 8899917 take exchange rate from selected inv
nvl(new.payment_exchange_rate,1), l_base_currency_precision),
round( (new.payment_amount / orig.payment_cross_rate *
nvl(new.payment_exchange_rate,1)) /
l_base_currency_mac) *
l_base_currency_mac ) ),
new.invoice_description||orig.invoice_num,
pv.type_1099,
l_set_of_books_id,
l_int_asset_tracking_flag,
'AUTO INVOICE CREATION',
'N',
'NOT REQUIRED',
l_current_org_id
FROM
po_vendors pv,
ap_invoices_all orig,
iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
ap_selected_invoices_all new, -- Modified for bug 8744658
fnd_currencies fcinv
/* Bug 6950891 Added TO_CHAR */
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
AND 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 new.original_invoice_id = orig.invoice_id
AND new.vendor_id = pv.vendor_id
AND new.checkrun_name = l_checkrun_name
AND fcinv.currency_code = orig.invoice_currency_code;
P_last_updated_by => l_last_updated_by,
P_period_name => l_period_name,
P_asset_account_flag => l_int_asset_tracking_flag,
P_calling_sequence => l_current_calling_sequence,
p_checkrun_id => l_checkrun_id,
p_completed_pmts_group_id => p_completed_pmts_group_id,
p_org_id => l_current_org_id);
l_debug_info := 'INSERT INTO ap_payment_schedules_all';
INSERT INTO ap_payment_schedules_all(
invoice_id,
payment_num,
last_update_date,
last_updated_by,
due_date,
gross_amount,
discount_amount_available,
amount_remaining,
discount_amount_remaining,
payment_priority,
payment_status_flag,
batch_id,
payment_cross_rate,
creation_date,
created_by,
payment_method_code,
inv_curr_gross_amount,
org_id,
-- added below columns for 7673570
remit_to_supplier_name,
remit_to_supplier_id,
remit_to_supplier_site,
remit_to_supplier_site_id,
relationship_id)
SELECT
new.invoice_id,
1,
SYSDATE,
l_last_updated_by,
new.due_date,
ibydocs.payment_amount,
0,
0,
0,
new.payment_priority,
'Y',
l_int_batch_id,
orig.payment_cross_rate,
SYSDATE,
l_last_updated_by,
orig.payment_method_code,
decode(fcinv.minimum_accountable_unit, null,
round((new.payment_amount/orig.payment_cross_rate),
fcinv.precision),
round((new.payment_amount/orig.payment_cross_rate)
/fcinv.minimum_accountable_unit)
* fcinv.minimum_accountable_unit),
l_current_org_id,
-- added below columns for 7673570
ibypmts.PAYEE_NAME,
NVL(aps.vendor_id, -222), --Modified for bug 8405513
aps.vendor_site_code,
NVL(ibypmts.supplier_site_id, -222), --modifed for bug 8405513
ibypmts.relationship_id
FROM ap_invoices_all orig,
ap_selected_invoices_all new,
ap_supplier_sites_all aps, -- bug 7673570
fnd_currencies fcinv,
iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs
/* Bug 6950891 Added TO_CHAR */
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
AND ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.org_id = l_current_org_id
AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
AND new.original_invoice_id = orig.invoice_id
AND new.checkrun_name = l_checkrun_name
AND fcinv.currency_code = orig.invoice_currency_code;
l_debug_info := 'INSERT INTO ap_invoice_relationships';
INSERT INTO ap_invoice_relationships(
original_invoice_id,
related_invoice_id,
created_by,
creation_date,
original_payment_num,
last_updated_by,
last_update_date,
checkrun_name)
SELECT orig.invoice_id,
new.invoice_id,
l_last_updated_by,
SYSDATE,
new.original_payment_num,
l_last_updated_by,
SYSDATE,
l_checkrun_name
FROM ap_invoices_all orig,
ap_selected_invoices_all new,
iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs
/* Bug 6950891 Added TO_CHAR */
WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(new.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(new.invoice_id)
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(new.payment_num)
AND 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 new.original_invoice_id = orig.invoice_id
AND new.checkrun_name = l_checkrun_name;
l_debug_info := 'INSERT INTO ap_batches_all';
INSERT INTO ap_batches_all(
batch_id,
batch_name,
batch_date,
last_update_date,
last_updated_by,
control_invoice_count,
actual_invoice_count,
creation_date,
created_by,
org_id) --4945922
SELECT l_int_batch_id,
substrb(LC.displayed_field||l_checkrun_name, 1,50),
SYSDATE,
SYSDATE,
l_last_updated_by,
count(*),
count(*),
SYSDATE,
l_last_updated_by,
i.org_id
FROM ap_invoices_all I,
ap_lookup_codes LC
WHERE I.batch_id= l_int_batch_id
AND LC.lookup_type = 'NLS TRANSLATION' /* bug 9868737 */
AND LC.lookup_code = 'INTEREST ON PAYMENTBATCH' /* bug 9868737 */
GROUP BY l_int_batch_id, l_checkrun_name,
LC.displayed_field, SYSDATE, l_last_updated_by, i.org_id;
/*bug8224330, transported the insert into ap_checks_all here, after the insertion of interest invoices related data
into ap tables*/
l_debug_info := 'insert into ap_checks_all';
INSERT INTO ap_checks_all
(CHECK_ID,
-- Bug 6845440 commented below field
-- BANK_ACCOUNT_ID,
CE_BANK_ACCT_USE_ID,
BANK_ACCOUNT_NAME,
AMOUNT,
CHECK_NUMBER,
CHECK_DATE,
CURRENCY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_ID,
VENDOR_NAME,
VENDOR_SITE_ID,
STATUS_LOOKUP_CODE,
CHECKRUN_ID,
CHECKRUN_NAME,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINE4,
CITY,
STATE,
ZIP,
PROVINCE,
COUNTRY,
-- VENDOR_SITE_CODE,
BANK_ACCOUNT_NUM,
-- IBAN_NUMBER,
BANK_NUM,
BANK_ACCOUNT_TYPE,
EXTERNAL_BANK_ACCOUNT_ID,
TRANSFER_PRIORITY,
PAYMENT_TYPE_FLAG,
CREATION_DATE,
CREATED_BY,
PAYMENT_METHOD_code,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
BASE_AMOUNT,
FUTURE_PAY_DUE_DATE,
MATURITY_EXCHANGE_DATE,
MATURITY_EXCHANGE_RATE_TYPE,
MATURITY_EXCHANGE_RATE,
ANTICIPATED_VALUE_DATE,
LEGAL_ENTITY_ID,
ORG_ID,
PAYMENT_ID,
COMPLETED_PMTS_GROUP_ID,
PAYMENT_PROFILE_ID, --SO WE CAN REFER BACK TO IBY
PARTY_ID,
PARTY_SITE_ID,
PAYMENT_DOCUMENT_ID, --4752808
PAYMENT_INSTRUCTION_ID, --4884849
-- added below columns for 7673570
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
RELATIONSHIP_ID)
SELECT
ap_checks_s.nextval,
-- Bug 6845440 commented below field
-- ce.bank_account_id,
ce.bank_acct_use_id,
substr(ceb.bank_account_name,1,80),
iby.payment_amount,
nvl(iby.paper_document_number, iby.payment_reference_number),
iby.payment_date,
iby.payment_currency_code,
SYSDATE,
l_last_updated_by,
pv.vendor_id,
nvl(pv.vendor_name, iby.payee_name), /* Added the nvl for bug#9976033 */
iby.inv_supplier_site_id, -- 7673570
decode(iby.maturity_date,null,'NEGOTIABLE','ISSUED'),
l_checkrun_id,
l_checkrun_name,
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address1), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address2), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address3), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Address4), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_City), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_State), /* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Postal_Code),/* Bug10061011 */
Decode(Pv.Vendor_Type_Lookup_Code,'EMPLOYEE',Null,Iby.Payee_Province),/* Bug10061011 */
DECODE(pv.vendor_type_lookup_code,'EMPLOYEE',null,iby.payee_country),/* Bug10061011 */
-- sc.vendor_site_code,
iby.ext_bank_account_number,
-- SC.iban_number, --need FROM iby
-- iby.ext_bank_account_name, -- Bug 5090441
iby.EXT_BANK_NUMBER,
iby.ext_bank_account_type,
iby.external_bank_account_id,
l_transfer_priority,
'A',
sysdate,
l_last_updated_by,
iby.payment_method_code,
decode(iby.payment_currency_code, l_base_currency_code,
null,
decode(l_exchange_rate_type, 'User',
ap_pmt_callout_pkg.get_user_rate(
l_base_currency_code,
iby.payment_currency_code,
l_checkrun_id),
ap_utilities_pkg.get_exchange_rate(
iby.payment_currency_code,
l_base_currency_code,
l_exchange_rate_type,
l_check_date,
'CONFIRM'))),
decode (iby.payment_currency_code, l_base_currency_code,null,l_exchange_rate_type), /* Added Decode for bug13560190 */
l_check_date, --exchange rate date
decode(iby.payment_currency_code, l_base_currency_code,
null,
ap_pmt_callout_pkg.get_base_amount(l_base_currency_code,
iby.payment_currency_code,
l_checkrun_id,
l_exchange_rate_type,
l_base_currency_mac,
iby.payment_amount,
l_base_currency_precision,
l_check_date)),
iby.maturity_date,
decode(iby.payment_currency_code, l_base_currency_code,
null,
decode(l_exchange_rate_type, 'User', l_check_date,
iby.maturity_date)),
decode (iby.payment_currency_code, l_base_currency_code,null,l_exchange_rate_type), /* Added Decode for bug13560190 */
decode(iby.payment_currency_code, l_base_currency_code, NULL,
decode(l_exchange_rate_type, 'User',
ap_pmt_callout_pkg.get_user_rate(l_base_currency_code,
iby.payment_currency_code,
l_checkrun_id),
ap_utilities_pkg.get_exchange_rate(
iby.payment_currency_code,
l_base_currency_code,
l_exchange_rate_type,
iby.maturity_date,
'CONFIRM'))),
iby.anticipated_value_date,
iby.legal_entity_id,
iby.org_id,
iby.payment_id,
iby.completed_pmts_group_id,
iby.payment_profile_id,
iby.inv_payee_party_id, -- 7673570 iby.payee_party_id,
iby.inv_party_site_id, -- 7673570 iby.party_site_id,
iby.payment_document_id,
iby.payment_instruction_id,
-- added below columns for 7673570
iby.PAYEE_NAME,
NVL(aps.vendor_id, -222), -- modifed for bug 8405513
aps.vendor_site_code,
NVL(iby.supplier_site_id, -222), --modifed for bug 8405513
iby.relationship_id
FROM iby_fd_payments_v iby,
po_vendors pv,
ce_bank_acct_uses_all ce,
ce_bank_accounts ceb,
ap_supplier_sites_all aps -- 7673570
WHERE iby.inv_payee_party_id = pv.party_id(+) -- 7673570
-- iby.payee_party_id = pv.party_id(+) -- 7673570
AND aps.vendor_site_id(+) = iby.supplier_site_id -- 7673570 --modifed for bug 8405513 to handle Payment Request
--AND pv.end_date_active IS NULL -- bug7166247
-- commented the above condition and added the below condition for bug 8401306
/* AND trunc(nvl(pv.end_date_active,sysdate)) >= trunc(sysdate) Commented for bug#8773583 */
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) --bug 8657535. Changed -99 to nvl(pv.vendor_id,-99)
ELSE nvl(vendor_id,-99)
END --Bug7493630 and Bug 8260736 (8348480)
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.calling_app_doc_unique_ref1=l_checkrun_id
and rownum=1
) --7196023
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 = l_current_org_id
AND iby.org_id = l_current_org_id
AND iby.completed_pmts_group_id = p_completed_pmts_group_id
-- Bug 6752984
AND iby.payment_service_request_id =
(SELECT payment_service_request_id
FROM IBY_PAY_SERVICE_REQUESTS
WHERE call_app_pay_service_req_code = l_checkrun_name
AND CALLING_APP_ID = 200);
l_debug_info := 'AP_ACCOUNTING_EVENTS_PKG.update_pmt_batch_event_status';
AP_ACCOUNTING_EVENTS_PKG.update_pmt_batch_event_status(
p_checkrun_name => l_checkrun_name,
p_org_id => l_current_org_id,
p_completed_pmts_group_id => p_completed_pmts_group_id,
p_calling_sequence => l_current_calling_sequence);
l_debug_info := 'UPDATE ap_selected_invoices_all';
UPDATE ap_selected_invoices_all ASI
SET ASI.invoice_payment_id = ap_invoice_payments_s.nextval
WHERE ASI.checkrun_id = l_checkrun_id
/* Bug 6950891. Added TO_CHAR */
AND (TO_CHAR(ASI.invoice_id), TO_CHAR(ASI.payment_num)) in
(select calling_app_doc_unique_ref2, calling_app_doc_unique_ref3
FROM iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
WHERE ibydocs.payment_id = ibypmts.payment_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND ibypmts.org_id = l_current_org_id);
l_debug_info := ' UPDATE ap_awt_temp_distributions_all';
UPDATE ap_awt_temp_distributions_all AATD
SET AATD.invoice_payment_id =
(SELECT ASI.invoice_payment_id
FROM ap_selected_invoices_all ASI
WHERE ASI.checkrun_id = AATD.checkrun_id
AND ASI.invoice_id = AATD.invoice_id
AND ASI.payment_num = AATD.payment_num
AND asi.org_id = l_current_org_id)
WHERE AATD.checkrun_id = l_checkrun_id
AND aatd.org_id = l_current_org_id
/* Bug 6950891. Added TO_CHAR */
AND (TO_CHAR(AATD.invoice_id), TO_CHAR(AATD.payment_num)) in
/* Bug 5383066, calling_app_doc_unique_ref3 should be used for payment_num*/
(select calling_app_doc_unique_ref2, calling_app_doc_unique_ref3
FROM iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
WHERE ibydocs.payment_id = ibypmts.payment_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND ibypmts.org_id = l_current_org_id);
l_last_updated_by,
--4863216
to_number(FND_PROFILE.VALUE('LOGIN_ID')),
to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID')),
to_number(FND_PROFILE.VALUE('PROGRAM_ID')),
to_number(FND_PROFILE.VALUE('REQUEST_ID')),
l_checkrun_id,
p_completed_pmts_group_id,
l_current_org_id,
l_check_date);
l_debug_info := 'DELETE FROM ap_awt_temp_distributions_all';
/* Bug 5383066. Foloowing Delete is not requeired. As call to
AP_WITHHOLDING_PKG.AP_WITHHOLD_CANCEL cleans up temporary withholding dists.
WHERE checkrun_name = l_checkrun_name
AND org_id = l_current_org_id
and checkrun_id = l_checkrun_id
and (invoice_id, payment_num) in
(select calling_app_doc_unique_ref2, calling_app_doc_unique_ref2
FROM iby_fd_docs_payable_v ibydocs
where calling_app_doc_unique_ref1 = l_checkrun_id
and completed_pmts_group_id = p_completed_pmts_group_id
and org_id = l_current_org_id);
OPEN c_schedule_amounts(l_last_updated_by --Bug5733731
,p_completed_pmts_group_id
,l_current_org_id
,l_checkrun_name);
last_update_date_ps_l,
last_updated_by_ps_l,
amount_remaining_ps_l,
discount_remaining_ps_l,
payment_status_ps_l,
--awt_num_ps_l, --Bug8477014
awt_num_ps_l, --Bug8752557
checkrun_id_ps_l,
invoice_id_ps_l,
payment_num_ps_l
LIMIT 1000;
l_debug_info := 'UPDATE ap_payment_schedules_all';
UPDATE ap_payment_schedules_all
SET last_update_date = last_update_date_ps_l(i)
,last_updated_by = last_updated_by_ps_l(i)
-- Modified by epajaril, need to consider the AWT
--Bug8477014: Undoing changes done for bug6836199
--Bug8752557: Undoing changes done here for bug8477014
--,amount_remaining = amount_remaining_ps_l(i)
,amount_remaining = (amount_remaining_ps_l(i) -
nvl(awt_num_ps_l(i),0)) --bug:7523065 --Bug8752557
,discount_amount_remaining = discount_remaining_ps_l(i)
--,payment_status_flag = payment_status_ps_l(i)
, payment_status_flag = DECODE((amount_remaining_ps_l(i)-nvl(awt_num_ps_l(i),0)), 0,'Y', 'P')--Bug8759364
,checkrun_id = checkrun_id_ps_l(i)
WHERE invoice_id = invoice_id_ps_l(i)
AND payment_num = payment_num_ps_l(i);
OPEN c_invoice_amounts(l_last_updated_by
,p_completed_pmts_group_id
,l_current_org_id
,l_checkrun_name);
last_update_date_inv_l,
last_updated_by_inv_l,
amount_paid_inv_l,
discount_taken_inv_l,
payment_status_inv_l,
invoice_id_inv_l
LIMIT 1000;
l_debug_info := 'UPDATE ap_invoices_all';
UPDATE ap_invoices_all
SET last_update_date = last_update_date_inv_l(i)
,last_updated_by = last_updated_by_inv_l(i)
,amount_paid = nvl(amount_paid,0) + amount_paid_inv_l(i)
,discount_amount_taken = nvl(discount_amount_taken,0) + discount_taken_inv_l(i)
,payment_status_flag = payment_status_inv_l(i)
WHERE invoice_id = invoice_id_inv_l(i);
l_debug_info := 'insert into ap_invoice_payments_all';
INSERT INTO ap_invoice_payments_all(
INVOICE_PAYMENT_ID,
INVOICE_ID,
PAYMENT_NUM,
CHECK_ID,
AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ELECTRONIC_TRANSFER_ID,
SET_OF_BOOKS_ID,
ACCTS_PAY_CODE_COMBINATION_ID,
ACCOUNTING_DATE,
PERIOD_NAME,
POSTED_FLAG,
ACCRUAL_POSTED_FLAG,
CASH_POSTED_FLAG,
DISCOUNT_TAKEN,
DISCOUNT_LOST,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
GAIN_CODE_COMBINATION_ID,
LOSS_CODE_COMBINATION_ID,
ASSET_CODE_COMBINATION_ID,
INVOICE_BASE_AMOUNT,
PAYMENT_BASE_AMOUNT,
EXCHANGE_DATE,
BANK_ACCOUNT_NUM,
-- IBAN_NUMBER, --Bug 2633878
BANK_NUM,
BANK_ACCOUNT_TYPE,
EXTERNAL_BANK_ACCOUNT_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY, -- Bug 4087878
FUTURE_PAY_CODE_COMBINATION_ID,
FUTURE_PAY_POSTED_FLAG,
ACCOUNTING_EVENT_ID,
CREATION_DATE,
CREATED_BY,
ORG_ID, --4945922
INVOICING_PARTY_ID, /*4739343, added 3rd party columns*/
INVOICING_PARTY_SITE_ID,
INVOICING_VENDOR_SITE_ID, -- Bug 5658623
-- added below columns for 7673570
REMIT_TO_SUPPLIER_NAME,
REMIT_TO_SUPPLIER_ID,
REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID,
assets_addition_flag) -- bug 8741899: add
SELECT /*hint added for bug14009966*/
/*+ Leading( xeg AC IBYPMTS FORE CEGL IBYDOCS SI ) index( ac ap_checks_u1 ) use_nl(IBYDOCS SI ) index(SI AP_SELECTED_INVOICES_N5) */
SI.invoice_payment_id,
SI.invoice_id,
SI.payment_num,
ac.check_id,
ibydocs.payment_amount,
sysdate,
l_last_updated_by,
NULL,
l_set_of_books_id,
null,
trunc(l_check_date), --bug6602676
l_period_name,
'N',
'N',
'N',
DECODE(ibydocs.payment_curr_discount_taken,0,'',ibydocs.payment_curr_discount_taken),
DECODE(ps.invoice_id, null, 0,
DECODE(ps.gross_amount, 0, 0,
(DECODE(FORE.minimum_accountable_unit,NULL,
ROUND((((ibydocs.payment_amount+ibydocs.payment_curr_discount_taken)/
DECODE(ps.gross_amount,0,1,ps.gross_amount)) *
(greatest (nvl(PS.discount_amount_available,0),
nvl(PS.second_disc_amt_available,0),
nvl(PS.third_disc_amt_available,0)))),
FORE.precision),
ROUND((((ibydocs.payment_amount+ibydocs.payment_curr_discount_taken)/
DECODE(ps.gross_amount,0,1,ps.gross_amount)) *
(greatest (nvl(PS.discount_amount_available,0),
nvl(PS.second_disc_amt_available,0),
nvl(PS.third_disc_amt_available,0))))
/ FORE.minimum_accountable_unit)
* FORE.minimum_accountable_unit)
- ibydocs.payment_curr_discount_taken))),
ac.exchange_rate,
ac.exchange_rate_type,
decode(ibydocs.payment_currency_code, l_base_currency_code,null,cegl.gain_code_combination_id),
decode(ibydocs.payment_currency_code, l_base_currency_code,null,cegl.loss_code_combination_id),
cegl.ap_asset_ccid,
decode(AI.invoice_currency_code, l_base_currency_code,
decode(ibydocs.payment_currency_code, l_base_currency_code,
null,
decode(gl_currency_api.convert_amount_sql(
ibydocs.payment_currency_code,
l_base_currency_code,
AI.payment_cross_rate_date,
AI.payment_cross_rate_type,
abs(ibydocs.payment_amount)),
-1, null, -2, null,
-1, null, -2, null,
gl_currency_api.convert_amount_sql(
ibydocs.payment_currency_code,
l_base_currency_code,
AI.payment_cross_rate_date,
AI.payment_cross_rate_type,
ibydocs.payment_amount))),
decode(SI.invoice_exchange_rate, null,
null,
decode(l_base_currency_mac,NULL,
ROUND((ibydocs.payment_amount * SI.invoice_exchange_rate)
/ SI.payment_cross_rate, l_base_currency_precision),
ROUND(((ibydocs.payment_amount * SI.invoice_exchange_rate)
/ SI.payment_cross_rate)
/ l_base_currency_mac)
* l_base_currency_mac))), --invoice_base_amount
decode(ibydocs.payment_currency_code, l_base_currency_code,
decode(AI.invoice_currency_code, l_base_currency_code,
null,
ibydocs.payment_amount),
--bug 8899917 take ex rate from check
decode(ac.exchange_rate, NULL, NULL,
decode(l_base_currency_mac, NULL,
ROUND((ibydocs.payment_amount * ac.exchange_rate)
,l_base_currency_precision),
ROUND((ibydocs.payment_amount * ac.exchange_rate)
/ l_base_currency_mac)
* l_base_currency_mac))), -- payment_base_amount
l_check_date,
SI.bank_account_num,
-- SI.iban_number,
SI.bank_num,
SI.bank_account_type,
SI.external_bank_account_id,
SI.attribute1,
SI.attribute2,
SI.attribute3,
SI.attribute4,
SI.attribute5,
SI.attribute6,
SI.attribute7,
SI.attribute8,
SI.attribute9,
SI.attribute10,
SI.attribute11,
SI.attribute12,
SI.attribute13,
SI.attribute14,
SI.attribute15,
SI.attribute_category,
cegl.future_dated_payment_ccid,
'N',
XEG.event_id,
sysdate,
l_last_updated_by,
ai.org_id, --4945922
ibydocs.beneficiary_party,
decode(ibydocs.beneficiary_party, null, null, ai.party_site_id),
decode(ibydocs.beneficiary_party, null, null, ai.vendor_site_id),
-- added below columns for 7673570
/* Bug 9074840 replaced following with ap_checks_all values
ibypmts.PAYEE_NAME,
NVL(aps.vendor_id, -222), --modifed for bug 8405513
aps.vendor_site_code,
NVL(ibypmts.supplier_site_id, -222), --modifed for bug 8405513
*/
ac.remit_to_supplier_name,
ac.remit_to_supplier_id,
ac.remit_to_supplier_site,
ac.remit_to_supplier_site_id,
'U' -- bug 8741899: add
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,
ap_checks_all ac,
--ap_supplier_sites_all aps, -- bug 7673570 --Removed by bug 9074840
--ce_bank_acct_uses_all ceu, --Removed by bug 9074840
ce_gl_accounts_ccid cegl,
XLA_EVENTS_INT_GT xeg
/* Bug 6950891. Added TO_CHAR */
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.org_id = l_current_org_id
and ibypmts.org_type = 'OPERATING_UNIT'
/* bug 9074840
AND aps.vendor_site_id(+) = ibypmts.supplier_site_id -- bug 7673570 --modifed for bug 8405513
*/
AND SI.checkrun_name = l_checkrun_name
AND ac.payment_id = ibypmts.payment_id
AND ac.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
/* bug 9074840
AND ceu.bank_account_id = ibypmts.internal_bank_account_id
AND ceu.org_id = l_current_org_id
*/
AND ac.ce_bank_acct_use_id = cegl.bank_acct_use_id --bug 9074840
AND xeg.application_id = 200
AND XEG.ENTITY_CODE = 'AP_PAYMENTS'
AND XEG.SOURCE_ID_INT_1 = ac.check_id ;
l_debug_info := 'AP_DBI_PKG.Insert_Payment_Confirm_DBI';
AP_DBI_PKG.Insert_Payment_Confirm_DBI(
p_checkrun_name => l_checkrun_name,
p_base_currency_code => l_base_currency_code,
p_key_table => 'AP_INVOICE_PAYMENTS_ALL',
p_calling_sequence => l_current_calling_sequence );
l_debug_info := 'AP_Accounting_Events_Pkg.Batch_Update_Payment_Info';
AP_Accounting_Events_Pkg.Batch_Update_Payment_Info(
p_checkrun_name => l_checkrun_name,
p_completed_pmts_group_id => p_completed_pmts_group_id,
p_org_id => l_current_org_id,
p_calling_sequence=>l_current_calling_sequence);
/* OPEN c_schedule_amounts(l_last_updated_by --Bug5733731
,p_completed_pmts_group_id
,l_current_org_id
,l_checkrun_name);
last_update_date_ps_l,
last_updated_by_ps_l,
amount_remaining_ps_l,
discount_remaining_ps_l,
payment_status_ps_l,
checkrun_id_ps_l,
invoice_id_ps_l,
payment_num_ps_l
LIMIT 1000;
l_debug_info := 'UPDATE ap_payment_schedules_all';
UPDATE ap_payment_schedules_all
SET last_update_date = last_update_date_ps_l(i)
,last_updated_by = last_updated_by_ps_l(i)
,amount_remaining = amount_remaining_ps_l(i)
,discount_amount_remaining = discount_remaining_ps_l(i)
,payment_status_flag = payment_status_ps_l(i)
,checkrun_id = checkrun_id_ps_l(i)
WHERE invoice_id = invoice_id_ps_l(i)
AND payment_num = payment_num_ps_l(i);
OPEN c_invoice_amounts(l_last_updated_by
,p_completed_pmts_group_id
,l_current_org_id
,l_checkrun_name);
last_update_date_inv_l,
last_updated_by_inv_l,
amount_paid_inv_l,
discount_taken_inv_l,
payment_status_inv_l,
invoice_id_inv_l
LIMIT 1000;
l_debug_info := 'UPDATE ap_invoices_all';
UPDATE ap_invoices_all
SET last_update_date = last_update_date_inv_l(i)
,last_updated_by = last_updated_by_inv_l(i)
,amount_paid = nvl(amount_paid,0) + amount_paid_inv_l(i)
,discount_amount_taken = nvl(discount_amount_taken,0) + discount_taken_inv_l(i)
,payment_status_flag = payment_status_inv_l(i)
WHERE invoice_id = invoice_id_inv_l(i);
/* UPDATE ap_payment_schedules_all ps1
SET (last_update_date,
last_updated_by,
amount_remaining,
discount_amount_remaining,
payment_status_flag,
checkrun_id) =
(SELECT sysdate,
l_last_updated_by,
SI1.amount_remaining - ibydocs.payment_amount - nvl(ibydocs.payment_curr_discount_taken,0),
0,
decode(SI1.amount_remaining - ibydocs.payment_amount - nvl(ibydocs.payment_curr_discount_taken,0), 0,
'Y', 'P'),
null --set checkrun_id to null
FROM ap_selected_invoices_all SI1,
iby_fd_docs_payable_v ibydocs
WHERE checkrun_name = l_checkrun_name
AND SI1.payment_num = ps1.payment_num
AND SI1.invoice_id = ps1.invoice_id
AND ibydocs.calling_app_doc_unique_ref1 = to_char(si1.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = to_char(si1.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = to_char(si1.payment_num))
WHERE (ps1.invoice_id, ps1.payment_num) in
(SELECT SI3.invoice_id, SI3.payment_num
FROM ap_selected_invoices_all SI3,
iby_fd_payments_v ibypmts,
iby_fd_docs_payable_v ibydocs,
ap_invoices_all AI
WHERE SI3.checkrun_name = l_checkrun_name
AND ibydocs.calling_app_doc_unique_ref1 = to_char(si3.checkrun_id)
AND ibydocs.calling_app_doc_unique_ref2 = to_char(si3.invoice_id)
AND ibydocs.calling_app_doc_unique_ref3 = to_char(si3.payment_num)
AND 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 AI.invoice_id = SI3.invoice_id
AND AI.invoice_type_lookup_code <> 'INTEREST');
l_debug_info := 'UPDATE ap_invoices_all';
UPDATE ap_invoices_all inv1
SET (last_update_date,
last_updated_by,
amount_paid,
discount_amount_taken,
payment_status_flag)=
(SELECT sysdate,
l_last_updated_by,
nvl(inv1.amount_paid,0) + sum(ibydocs.payment_amount),
nvl(inv1.discount_amount_taken,0) + nvl(sum(ibydocs.payment_curr_discount_taken),0),
AP_INVOICES_UTILITY_PKG.get_payment_status( inv1.invoice_id )
FROM iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
WHERE ibypmts.org_type = 'OPERATING_UNIT'
AND 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
(ibydocs.calling_app_doc_unique_ref1,ibydocs.calling_app_doc_unique_ref2,ibydocs.calling_app_doc_unique_ref3)
in (select si.checkrun_id,si.invoice_id,si.payment_num from
ap_selected_invoices_all si where si.invoice_id=inv1.invoice_id
and checkrun_name = l_checkrun_name)
)
WHERE invoice_id IN
(SELECT ibydocs.calling_app_doc_unique_ref2
FROM iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
WHERE ibypmts.payment_id = ibydocs.payment_id
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.org_id = l_current_org_id)
AND invoice_type_lookup_code <> 'INTEREST';
l_last_updated_by,
--4863216
to_number(FND_GLOBAL.USER_ID),--Bug6489464
--to_number(FND_PROFILE.VALUE('LOGIN_ID')),
to_number(FND_PROFILE.VALUE('PROGRAM_APPLICATION_ID')),
to_number(FND_PROFILE.VALUE('PROGRAM_ID')),
to_number(FND_PROFILE.VALUE('REQUEST_ID')),
l_checkrun_id,
p_completed_pmts_group_id,
l_current_org_id);
SELECT 1
INTO l_wf_event_exists
FROM wf_events we
WHERE owner_tag = 'SQLAP'
AND name = 'oracle.apps.ap.payment'
AND status = 'ENABLED';
l_debug_info := 'DELETE FROM ap_selected_invoices_all';
DELETE FROM ap_selected_invoices_all
WHERE checkrun_id = l_checkrun_id
/* Bug 6950891. Added TO_CHAR */
and (TO_CHAR(invoice_id), TO_CHAR(payment_num)) in
(select ibydocs.calling_app_doc_unique_ref2,
ibydocs.calling_app_doc_unique_ref3
from iby_fd_docs_payable_v ibydocs,
iby_fd_payments_v ibypmts
where ibypmts.payment_id = ibydocs.payment_id
and ibypmts.org_type = 'OPERATING_UNIT'
AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
AND ibypmts.org_id = l_current_org_id);
l_debug_info := 'DELETE FROM ap_unselected_invoices_all';
DELETE FROM ap_unselected_invoices_all
WHERE checkrun_id = l_checkrun_id;
SELECT COUNT(*)
INTO l_iby_check_count
FROM iby_fd_payments_v
WHERE completed_pmts_group_id = p_completed_pmts_group_id;
SELECT COUNT(*)
INTO l_iby_docs_count
FROM iby_fd_docs_payable_v
WHERE completed_pmts_group_id = p_completed_pmts_group_id;
l_api_name CONSTANT VARCHAR2(30) := 'Payment_Status_Updated';
update ap_checks_all
set status_lookup_code = 'STOP INITIATED',
stopped_date= p_stopped_date, -- Bug 6957071
stopped_by= p_stopped_by -- Bug 6957071
where payment_id = p_payment_id;
l_api_name CONSTANT VARCHAR2(30) := 'Payment_Status_Updated';
update ap_checks_all
set status_lookup_code = 'NEGOTIABLE',
stopped_date=null, -- Bug 6957071
stopped_by=null -- Bug 6957071
where payment_id = p_payment_id;
SELECT status_lookup_code
INTO l_payment_status
FROM AP_CHECKS_ALL
WHERE payment_id = p_payment_id;
SELECT COUNT(*) INTO l_prepay_app_exists
FROM ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoice_distributions_all aid,
ap_invoices_all ai
WHERE ac.payment_id = p_payment_id
AND ac.check_id = aip.check_id
AND aip.invoice_id = ai.invoice_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND nvl(aid.prepay_amount_remaining,aid.amount) <> aid.amount
AND nvl(aid.reversal_flag, 'N') <> 'Y'
AND rownum = 1;
SELECT 'Y'
INTO l_check_prepay_unapply
FROM dual
WHERE EXISTS
(SELECT 1
FROM ap_invoice_distributions_all aid_prepay,
ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoices_all ai_prepay,
ap_invoice_distributions_all aid,
ap_invoice_distributions_all aidp
WHERE aip.check_id = ac.check_id
AND ac.payment_id = p_payment_id
AND aip.invoice_id = ai_prepay.invoice_id
AND ai_prepay.invoice_type_lookup_code = 'PREPAYMENT'
AND aid_prepay.invoice_id = ai_prepay.invoice_id
AND aid_prepay.invoice_distribution_id = aid.prepay_distribution_id
AND aid.prepay_distribution_id IS NOT NULL
AND aid.parent_reversal_id IS NOT NULL
AND aid.amount > 0
AND nvl(aid.posted_flag, 'N') = 'N'
AND aid.invoice_id = aidp.invoice_id
AND aid.invoice_line_number = aidp.invoice_line_number
AND aid.parent_reversal_id = aidp.invoice_distribution_id
AND aid.prepay_distribution_id = aidp.prepay_distribution_id
AND nvl(aidp.posted_flag, 'N') = 'Y');