[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
* (AutoSelect/Build Payment Stage) *
* Processing units to be executed: *
* 1. Create Temporary Distribution Lines *
* *
**************************************************************************/
PROCEDURE Do_AWT_Build_Payment_Batch
(P_Checkrun_Name IN Varchar2,
p_Checkrun_id IN Number,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
* It also updates buckets and credit letter amounts. *
* *
**************************************************************************/
PROCEDURE Calculate_AWT_Amounts
(P_Checkrun_Name IN Varchar2,
P_Checkrun_ID IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_AWT_Date IN Date,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_Total_Wh_Amount OUT NOCOPY Number,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
P_Total_Wh_Amount IN OUT NOCOPY Number,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null,
P_Calling_Module IN Varchar2 Default null,
P_Checkrun_Name IN Varchar2 Default null,
P_Checkrun_ID IN Number Default null,
P_Payment_Num IN Number Default null);
P_Selected_Check_Id IN Number,
P_Currency_Code IN Varchar2,
P_Tab_Inv_Amounts IN OUT NOCOPY Tab_Amounts,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2);
* Name : Update_Credit_Letter *
* Purpose : Updates the withheld amount for each tax name contained *
* into the PL/SQL table. The credit letters table is also *
* updated *
* *
**************************************************************************/
PROCEDURE Update_Credit_Letter
(P_Vendor_Id IN Number,
P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
P_AWT_Date IN Date,
P_Payment_Num IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
* Name : Insert_Credit_Letter_Amount *
* Purpose : Stores current information about credit letters into the *
* JL_AR_AP_SUP_AWT_CR_LTS table *
* *
**************************************************************************/
PROCEDURE Insert_Credit_Letter_Amount
(P_Vendor_Id IN Number,
P_AWT_Type_Code IN Varchar2,
P_Tax_Id IN Number,
P_AWT_Date IN Date,
P_Withheld_Amount IN Number,
P_Actual_Withheld_Amount IN Number,
P_Balance IN Number,
P_Status IN Varchar2,
P_Payment_Num IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
P_Selected_Check_Id IN Number,
P_AWT_Date IN Date,
P_Payment_Num IN Number,
P_Calling_Sequence IN Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null);
* Name : Update_Quick_Payment *
* Purpose : Updates the payment amount by subtracting the withheld *
* amount. *
* *
**************************************************************************/
PROCEDURE Update_Quick_Payment
(P_Check_Id IN Number,
P_Calling_Sequence IN Varchar2);
* Name : Update_Payment_Batch *
* Purpose : Updates the amounts of the payment batch by subtracting *
* the withholding amount. *
* *
**************************************************************************/
PROCEDURE Update_Payment_Batch
(P_Checkrun_Name IN Varchar2,
p_checkrun_id IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2);
P_Selected_Check_Id IN Number,
P_Calling_Module IN Varchar2,
P_Total_Wh_Amount IN Number,
P_Calling_Sequence IN Varchar2,
P_Vendor_Name OUT NOCOPY Varchar2,
P_Vendor_Site_Code OUT NOCOPY Varchar2)
RETURN Boolean;
* Purpose : Updates the credit letters table in order to store the *
* the final check ID, when users confirm a payment batch. *
* This procedure is not called for Quick Payments because *
* the check ID is known from the begining. *
* *
**************************************************************************/
PROCEDURE Confirm_Credit_Letters
(P_Checkrun_Name IN Varchar2,
P_Checkrun_ID IN Number,
P_Calling_Sequence IN Varchar2);
* Purpose : Sets the "Ok To Pay" flag for all the selected invoices *
* within the payment when the calculation routine is not *
* successful *
* *
**************************************************************************/
PROCEDURE Reject_Payment_Batch
(P_Selected_Check_Id IN Number,
P_AWT_Success IN Varchar2,
P_Calling_Sequence IN Varchar2);
P_Last_Updated_By IN Number,
P_Last_Update_Login IN Number,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null,
P_Awt_Success OUT NOCOPY Varchar2,
P_Invoice_Payment_Id IN Number Default null,
P_Check_Id IN Number Default null)
IS
l_debug_info Varchar2(300);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
* (AutoSelect/Build Payment Stage) *
* ---------------------------------------------------- *
* Processing units to be executed: *
* 1. Create Temporary Distribution Lines *
* *
********************************************************/
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
l_debug_info := 'Calculating Withholding for Payment Batch (Build)';
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
P_Last_Updated_By IN Number,
P_Last_Update_Login IN Number,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
------------------------------
-- Local variables definition
------------------------------
l_check_id Number;
l_selected_check_id Number;
SELECT apip.check_id check_id,
apip.payment_num payment_num,
apip.invoice_id invoice_id
INTO l_check_id,
l_payment_num,
l_invoice_id
FROM ap_invoice_payments apip
WHERE apip.invoice_payment_id = P_Parent_Id;
select ac.payment_id
into l_payment_id
from ap_checks ac
where ac.check_id = l_check_id;
null, -- Selected Check Id
P_Undo_AWT_Date,
l_payment_num,
l_calling_sequence,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
UPDATE ap_invoice_distributions
SET Global_Attribute5 = 0
WHERE invoice_id = l_invoice_id
and nvl(to_number(Global_Attribute5),0) > 0;
p_selected_check_id IN Number,
P_Calling_Sequence IN Varchar2)
IS
-----------VARIABLES-----------
l_debug_info Varchar2(300);
l_selected_check_id Number;
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: p_selected_check_id='||to_char(p_selected_check_id));
UPDATE jl_ar_ap_awt_certif
set status = 'VOID'
where checkrun_name = p_checkrun_name
and check_number NOT IN (
SELECT apsi.check_number
FROM ap_selected_invoice_checks apsi
WHERE apsi.checkrun_name = P_Checkrun_Name
AND (apsi.status_lookup_code ='NEGOTIABLE' or apsi.status_lookup_code='ISSUED') );
P_Last_Updated_By IN Number,
P_Last_Update_Login IN Number,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
-------------------------------
-- Local variables definition
-------------------------------
l_selected_check_id Number;
CURSOR c_selected_invoices (P_Invoice_Id IN Number,
P_Payment_Num IN Number,
P_Checkrun_Name IN Varchar2)
IS
SELECT Ihd.Payment_id selected_check_id
FROM IBY_Hook_Docs_in_PMT_T ihd
WHERE ihd.calling_app_doc_unique_ref2 = P_Invoice_Id
AND ihd.calling_app_doc_unique_ref3 = P_Payment_Num
AND ihd.calling_App_doc_unique_ref1 = P_Checkrun_ID
AND ihd.calling_app_id = 200 ;
OPEN c_selected_invoices (P_Invoice_Id, P_Payment_Num, P_Checkrun_Name);
FETCH c_selected_invoices INTO l_selected_check_id;
EXIT WHEN c_selected_invoices%NOTFOUND;
l_selected_check_id,
P_Undo_AWT_Date,
P_Payment_Num,
l_calling_sequence,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Cursor c_selected_invoices');
CLOSE c_selected_invoices;
If (P_Calling_Module='CANCEL') or (P_Calling_Module = 'AUTOSELECT') then
JL_AR_AP_WITHHOLDING_PKG.JL_AR_AP_VOID_SELEC_CERTIF(p_checkrun_name,
l_selected_check_id,
l_calling_sequence);
select pmt.payment_id
from iby_fd_payments_v pmt
where pmt.payment_instruction_id = p_pmt_instruction_id
and pmt.payment_status ='REMOVED_DOCUMENT_SPOILED' ;
select pmt.payment_id
from iby_fd_payments_v pmt
where pmt.payment_instruction_id = p_pmt_instruction_id
and pmt.payment_status ='READY_TO_REPRINT';
select pmt.payment_id
from iby_fd_payments_v pmt
where pmt.payment_instruction_id = p_pmt_instruction_id ;
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
------------------------------
-- Local variables definition
------------------------------
l_create_distr Varchar2(25);
SELECT apin.invoice_id invoice_id,
apin.invoice_currency_code invoice_currency_code,
apip.payment_num payment_num
FROM ap_invoice_payments apip,
ap_invoices apin
WHERE apin.invoice_id = apip.invoice_id
AND apip.check_id = P_Check_Id;
Select payment_type_flag
into l_payment_type
from ap_checks
where check_id = P_Check_id;
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Id,
P_Request_Id);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
l_calling_sequence,
-- Payment Exchange Rate ER 8648739 Start
P_Check_Id);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
l_calling_sequence);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Update_Quick_Payment');
Update_Quick_Payment (P_Check_Id,
l_calling_sequence);
* (AutoSelect/Build Payment Stage) *
* Processing units to be executed: *
* 1. Create Temporary Distribution Lines *
* *
**************************************************************************/
PROCEDURE Do_AWT_Build_Payment_Batch
(P_Checkrun_Name IN Varchar2,
P_Checkrun_ID IN Number,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
------------------------------------------------
-- Cursor to select all the checks ID included
-- within the payment batch
------------------------------------------------
CURSOR c_selected_checks (P_Checkrun_Name Varchar2)
IS
/* SELECT apsic.selected_check_id selected_check_id
FROM ap_selected_invoice_checks apsic
WHERE apsic.checkrun_name = P_Checkrun_Name;*/
SELECT ipmt.payment_id payment_id, ipmt.payment_date
from IBY_HOOK_PAYMENTS_T ipmt
where ipmt.call_app_pay_service_req_code = P_Checkrun_Name
and ipmt.calling_app_id= 200;
rec_selected_checks c_selected_checks%ROWTYPE;
/* SELECT apisc.check_date
INTO l_awt_date
FROM ap_invoice_selection_criteria apisc
WHERE apisc.checkrun_name = P_Checkrun_Name;
SELECT payment_date
INTO l_awt_date
FROM IBY_HOOK_PAYMENTS_T ipmt
WHERE ipmt.call_app_pay_service_req_code = P_Checkrun_Name
AND ipmt.calling_app_id=200;
OPEN c_selected_checks (P_Checkrun_Name);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Looping thru c_selected_checks');
FETCH c_selected_checks INTO rec_selected_checks;
EXIT WHEN c_selected_checks%NOTFOUND;
rec_selected_checks.payment_id,
rec_selected_checks.payment_date,
P_Calling_Module,
l_calling_sequence,
l_total_wh_amount,
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Id,
P_Request_Id);
Reject_Payment_Batch (rec_selected_checks.payment_id,
P_AWT_Success,
l_calling_sequence);
rec_selected_checks.payment_id,
P_Calling_Module,
l_total_wh_amount,
l_calling_sequence,
l_vendor_name,
l_vendor_site_code)) THEN
-- l_payment_amount)) Bug# 2807464
-- AND l_payment_amount > 0 THEN --- Bug 2157401 Bug# 2807464
-- Debug Information
IF (DEBUG_Var = 'Y') THEN
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling - NOT Partial_Payment_Paid_In_Full');
Reject_Payment_Batch (rec_selected_checks.payment_id,
P_AWT_Success,
l_calling_sequence);
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Update_Payment_Batch');
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: rec_selected_checks.selected_check_id = '||
to_char(rec_selected_checks.payment_id));
Update_Payment_Batch (P_Checkrun_Name,
p_checkrun_id,
rec_selected_checks.payment_id,
l_calling_sequence);
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Close Cursor c_selected_checks');
CLOSE c_selected_checks;
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
-------------------------------
-- Local Variables Definition
-------------------------------
l_create_distr Varchar2(25);
/* CURSOR c_selected_invoices (P_Checkrun_Name IN Varchar2)
IS
SELECT apsi.invoice_id invoice_id,
apsi.payment_num payment_num,
apin.invoice_currency_code invoice_curr_code
FROM ap_selected_invoices apsi,
ap_selected_invoice_checks apsic,
unique_ref2 invoice_id,
docs.calling_app_doc_uniq
ap_invoices apin
WHERE apsic.checkrun_name = P_Checkrun_Name
AND apsi.checkrun_name = P_Checkrun_Name
AND (apsic.status_lookup_code = 'NEGOTIABLE'
OR apsic.status_lookup_code = 'ISSUED')
AND apsic.selected_check_id = apsi.pay_selected_check_id
AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
AND apin.invoice_id = apsi.invoice_id
AND apsi.original_invoice_id IS NULL;
CURSOR c_selected_invoices (p_checkrun_id IN NUMBER) IS
SELECT docs.calling_app_doc_unique_ref2 invoice_id,
docs.calling_app_doc_unique_ref3 payment_num,
docs.document_currency_code invoice_curr_code,
docs.payment_date,
docs.org_id
FROM IBY_FD_PAYMENTS_V ipmt,
IBY_FD_DOCS_PAYABLE_V docs
WHERE to_number(docs.calling_app_doc_unique_ref1) = p_checkrun_id
AND ipmt.payment_id = docs.payment_id
AND (ipmt.payment_status = 'NEGOTIABLE'
OR ipmt.payment_status = 'ISSUED'
OR ipmt.payment_status = 'FORMATTED'
OR ipmt.payment_status = 'TRANSMITTED'
OR ipmt.payment_status = 'ACKNOWLEDGED'
OR ipmt.payment_status = 'BANK_VALIDATED'
OR ipmt.payment_status = 'PAID')
AND ipmt.payments_complete_flag ='Y'
AND docs.calling_app_id= 200;
rec_sel_inv c_selected_invoices%ROWTYPE;
OPEN c_selected_invoices (P_Checkrun_ID);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Looping Thru c_selected_invoices');
FETCH c_selected_invoices INTO rec_sel_inv;
EXIT WHEN c_selected_invoices%NOTFOUND;
SELECT nvl(create_awt_dists_type, 'NEVER'),
nvl(create_awt_invoices_type, 'NEVER')
INTO l_create_distr,
l_create_invoices
FROM ap_system_parameters_all
WHERE org_id = rec_sel_inv.org_id;
/* SELECT apisc.check_date
INTO l_awt_date
FROM ap_invoice_selection_criteria apisc
WHERE apisc.checkrun_name = P_Checkrun_Name;
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
l_calling_sequence);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
l_calling_sequence);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close Cursor c_selected_invoices');
CLOSE c_selected_invoices;
* It also updates buckets and credit letter amounts. *
* *
**************************************************************************/
PROCEDURE Calculate_AWT_Amounts
(P_Checkrun_Name IN Varchar2,
P_Checkrun_ID IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_AWT_Date IN Date,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_Total_Wh_Amount OUT NOCOPY Number,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
------------------------
-- Variables Definition
------------------------
l_previous_awt_type_code Varchar2(30);
SELECT
jlst.awt_type_code awt_type_code,
jlsc.tax_id tax_id,
apin.invoice_id invoice_id,
apin.vendor_id vendor_id,
apid.invoice_distribution_id invoice_distribution_id, -- Lines
nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
-- Payment Exchange Rate ER 8648739 Start 1
-- nvl(apid.base_amount, apid.amount) line_amount,
(apid.amount * nvl(apip.exchange_rate,1)) line_amount,
-- Payment Exchange Rate ER 8648739 End 1
apip.amount payment_amount,
apip.invoice_payment_id invoice_payment_id,
apip.payment_num payment_num,
jlty.taxable_base_amount_basis tax_base_amount_basis
FROM
jl_zz_ap_inv_dis_wh jlwh,
ap_invoices apin,
ap_invoice_distributions apid,
ap_invoice_payments apip,
jl_zz_ap_supp_awt_types jlst,
jl_zz_ap_sup_awt_cd jlsc,
jl_zz_ap_awt_types jlty
WHERE
apid.invoice_id = jlwh.invoice_id
-- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
AND apin.invoice_id = apid.invoice_id
AND apin.invoice_id = apip.invoice_id
AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
AND jlst.awt_type_code = jlty.awt_type_code
AND apip.check_id = P_Check_Id
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
AND NVL(apip.ACCOUNTING_DATE,sysdate) between -- Argentina AWT ER 6624809
NVL(jlsc.effective_start_date,To_Date('01-01-1950', 'DD-MM-YYYY'))
and NVL(jlsc.effective_end_date,To_Date('31-12-9999', 'DD-MM-YYYY'))
ORDER BY
to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
apin.invoice_id,
DUMMY_INVOICE_ID)),
jlst.awt_type_code,
jlsc.tax_id,
apin.invoice_id,
apip.invoice_payment_id;
SELECT
jlst.awt_type_code awt_type_code,
jlsc.tax_id tax_id,
apin.invoice_id invoice_id,
apin.vendor_id vendor_id,
apid.invoice_distribution_id invoice_distribution_id, -- Lines
nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
nvl(apid.base_amount, apid.amount) line_amount,
-- apsi.payment_amount payment_amount,
docs.document_amount payment_amount,
null invoice_payment_id,
-- apsi.payment_num payment_num,
to_number(docs.calling_app_doc_unique_ref3) payment_num,
jlty.taxable_base_amount_basis tax_base_amount_basis
FROM
jl_zz_ap_inv_dis_wh jlwh,
ap_invoices apin,
ap_invoice_distributions apid,
iby_hook_docs_in_pmt_t docs,
jl_zz_ap_supp_awt_types jlst,
jl_zz_ap_sup_awt_cd jlsc,
jl_zz_ap_awt_types jlty
WHERE docs.payment_id = P_Check_Id
AND apid.invoice_id = jlwh.invoice_id
-- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
AND apin.invoice_id = apid.invoice_id
AND apin.invoice_id = to_number(docs.calling_app_doc_unique_ref2)
AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
AND jlst.awt_type_code = jlty.awt_type_code
AND docs.dont_pay_flag = 'N'
AND docs.calling_app_id =200
ORDER BY
to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
apin.invoice_id,
DUMMY_INVOICE_ID)),
jlst.awt_type_code,
jlsc.tax_id,
docs.calling_app_doc_unique_ref2,
docs.calling_app_doc_unique_ref3;
CURSOR c_payment_batch_withholdings (P_Selected_Check_Id Number)
IS
SELECT distinct jlst.awt_type_code awt_type_code, --bug 12613506
jlsc.tax_id tax_id,
apin.invoice_id invoice_id,
apin.vendor_id vendor_id,
apid.invoice_distribution_id invoice_distribution_id, -- Lines
nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
-- Payment Exchange Rate ER 8648739 Start 2
-- nvl(apid.base_amount, apid.amount) line_amount,
(apid.amount * nvl(apsi.payment_exchange_rate,1)) line_amount,
-- Payment Exchange Rate ER 8648739 End 2
-- apsi.payment_amount payment_amount,
docs.document_amount payment_amount,
null invoice_payment_id,
-- apsi.payment_num payment_num,
to_number(docs.calling_app_doc_unique_ref3) payment_num,
jlty.taxable_base_amount_basis tax_base_amount_basis
FROM
jl_zz_ap_inv_dis_wh jlwh,
ap_invoices apin,
ap_invoice_distributions apid,
-- Payment Exchange Rate ER 8648739 Start 3
ap_selected_invoices apsi,
-- Payment Exchange Rate ER 8648739 End 3
iby_hook_docs_in_pmt_t docs,
jl_zz_ap_supp_awt_types jlst,
jl_zz_ap_sup_awt_cd jlsc,
jl_zz_ap_awt_types jlty
WHERE docs.payment_id = P_Selected_Check_Id
AND apid.invoice_id = jlwh.invoice_id
-- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
AND apin.invoice_id = apid.invoice_id
-- AND apin.invoice_id = apsi.invoice_id
AND apin.invoice_id = to_number(docs.calling_app_doc_unique_ref2)
AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
AND jlst.awt_type_code = jlty.awt_type_code
AND docs.dont_pay_flag = 'N'
AND docs.calling_app_id =200
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
-- AND apsi.pay_selected_check_id = P_Selected_Check_Id
-- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
-- AND apsi.original_invoice_id IS NULL
-- Payment Exchange Rate ER 8648739 Start 4
AND apsi.invoice_id = docs.calling_app_doc_unique_ref2
-- Payment Exchange Rate ER 8648739 End 4
ORDER BY
to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
apin.invoice_id,
DUMMY_INVOICE_ID)),
jlst.awt_type_code,
jlsc.tax_id,
apin.invoice_id, --bug 12613506
payment_num; --bug 12613506
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id : '||to_char(P_Selected_Check_Id));
P_Selected_Check_Id,
l_base_currency_code,
tab_inv_amounts,
P_Calling_Module,
l_calling_sequence);
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
OPEN c_payment_batch_withholdings (P_Selected_Check_Id);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Open Cursor c_payment_batch_withholdings for AutoSelect');
select TO_DATE(apip.ACCOUNTING_DATE, 'DD-MM-YYYY') into l_CODE_ACCOUNTING_DATE -- Argentina AWT ER
from ap_invoice_payments apip
where apip.INVOICE_PAYMENT_ID = rec_payment_wh.invoice_payment_id;
SELECT TO_DATE (apip.accounting_date, 'DD-MM-YYYY')
INTO l_code_accounting_date -- Argentina AWT ER
FROM ap_invoice_payments apip
WHERE apip.invoice_payment_id = rec_payment_wh.invoice_payment_id;
ELSIF (p_calling_module = 'AUTOSELECT') then
SELECT TO_DATE (apsc.check_date, 'DD-MM-YYYY')
INTO l_code_accounting_date -- Argentina AWT ER
FROM ap_inv_selection_criteria_all apsc
WHERE apsc.checkrun_id = p_checkrun_id;
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
l_not_found := c_payment_batch_withholdings%NOTFOUND;
P_Selected_Check_Id,
l_calling_sequence,
tab_payment_wh,
l_total_wh_amount,
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Checkrun_Name,
P_Checkrun_ID,
rec_payment_wh.payment_num);
P_Selected_Check_Id,
l_calling_sequence,
tab_payment_wh,
l_total_wh_amount,
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Checkrun_Name,
P_Checkrun_ID,
rec_payment_wh.payment_num);
SELECT TO_DATE (apip.accounting_date, 'DD-MM-YYYY')
INTO l_code_accounting_date -- Argentina AWT ER
FROM ap_invoice_payments apip
WHERE apip.invoice_payment_id = rec_payment_wh.invoice_payment_id;
ELSIF (p_calling_module = 'AUTOSELECT') then
SELECT TO_DATE (apsc.check_date, 'DD-MM-YYYY')
INTO l_code_accounting_date -- Argentina AWT ER
FROM ap_inv_selection_criteria_all apsc
WHERE apsc.checkrun_id = p_checkrun_id;
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
FETCH c_payment_batch_withholdings INTO rec_payment_wh;
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
-- Debug Information
IF (DEBUG_Var = 'Y') THEN
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close Cursor c_payment_batch_withholdings');
', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
', Calling Module= ' || P_Calling_Module);
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
P_Total_Wh_Amount IN OUT NOCOPY Number,
P_AWT_Success OUT NOCOPY Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null,
P_Calling_Module IN Varchar2 Default null,
P_Checkrun_Name IN Varchar2 Default null,
P_Checkrun_ID IN Number Default null,
P_Payment_Num IN Number Default null)
IS
l_debug_info Varchar2(300);
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id= '||to_char(P_Selected_Check_Id));
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Update_Credit_Letter');
Update_Credit_Letter (P_Vendor_Id,
P_Rec_AWT_Type,
P_AWT_Date,
P_Payment_Num,
P_Check_Id,
P_Selected_Check_Id,
l_calling_sequence,
P_Tab_Withhold,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Update_Credit_Letter');
TRUE, -- Update Bucket
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Checkrun_Name,
P_Checkrun_ID,
P_Payment_Num);
', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
', Calling Module= ' || P_Calling_Module ||
', Checkrun Name= ' || P_Checkrun_Name ||
', Payment Num= ' || to_char(P_Payment_Num));
P_Selected_Check_Id IN Number,
P_Currency_Code IN Varchar2,
P_Tab_Inv_Amounts IN OUT NOCOPY Tab_Amounts,
P_Calling_Module IN Varchar2,
P_Calling_Sequence IN Varchar2)
IS
------------------------
-- Variables definition
------------------------
l_not_found Boolean := TRUE;
SELECT apin.invoice_id invoice_id,
apid.invoice_distribution_id invoice_distribution_id , -- Lines
-- Payment Exchange Rate ER 8648739 Start 5
-- nvl(apid.base_amount, apid.amount) amount,
(apid.amount * nvl(apip.exchange_rate,1)) amount,
-- Payment Exchange Rate ER 8648739 End 5
nvl(apid.global_attribute4, 0) tax_inclusive_amount,
-- Payment Exchange Rate ER 8648739 Start 6
-- nvl(apip.invoice_base_amount,apip.amount) payment_amount,
(apip.amount * nvl(apip.exchange_rate,1)) payment_amount,
-- Payment Exchange Rate ER 8648739 End 6
apip.invoice_payment_id invo_payment_id
FROM ap_invoices apin,
ap_invoice_distributions apid,
ap_invoice_payments apip
WHERE apin.invoice_id = apid.invoice_id
AND apin.invoice_id = apip.invoice_id
AND apip.check_id = P_Check_Id
AND apid.line_type_lookup_code <> 'AWT'
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
ORDER BY apin.invoice_id,
apip.invoice_payment_id,
apid.invoice_distribution_id ; -- Lines
SELECT apin.invoice_id invoice_id,
apid.invoice_distribution_id invoice_distribution_id , -- Lines
nvl(apid.base_amount, apid.amount) amount,
nvl(apid.global_attribute4, 0) tax_inclusive_amount,
-- apsi.payment_amount*nvl(apsi.invoice_exchange_rate,1) payment_amount,
-- ,apsi.payment_num payment_num
docs.document_amount* nvl(apsi.invoice_exchange_rate,1) payment_amount,
docs.calling_app_doc_unique_ref3 payment_num
FROM ap_invoices apin,
ap_invoice_distributions apid,
ap_selected_invoices apsi,
iby_hook_docs_in_pmt_t docs
WHERE apin.invoice_id = apid.invoice_id
AND apin.invoice_id = apsi.invoice_id
-- AND apsi.pay_selected_check_id = P_Selected_Check_Id
and docs.payment_id = P_Check_Id
and apsi.invoice_id = docs.calling_app_doc_unique_ref2
-- AND apsi.original_invoice_id IS NULL
AND docs.dont_pay_flag = 'N'
AND apid.line_type_lookup_code <> 'AWT'
and docs.calling_app_id = 200
ORDER BY apin.invoice_id,
docs.calling_app_doc_unique_ref3,
apid.invoice_distribution_id ;
CURSOR c_batch_invoice_amounts (P_Selected_Check_Id IN Number) IS
SELECT distinct apin.invoice_id invoice_id, --bug 12613506
apid.invoice_distribution_id invoice_distribution_id , -- Lines
-- Payment Exchange Rate ER 8648739 Start 7
-- nvl(apid.base_amount, apid.amount) amount,
(apid.amount * nvl(apsi.payment_exchange_rate, 1)) amount,
-- Payment Exchange Rate ER 8648739 End 7
nvl(apid.global_attribute4, 0) tax_inclusive_amount,
-- Payment Exchange Rate ER 8648739 Start 8
-- docs.document_amount* nvl(apsi.invoice_exchange_rate,1) payment_amount,
(docs.document_amount * nvl(apsi.payment_exchange_rate,1)) payment_amount,
-- Payment Exchange Rate ER 8648739 End 8
docs.calling_app_doc_unique_ref3 payment_num
FROM ap_invoices apin,
ap_invoice_distributions apid,
ap_selected_invoices apsi,
iby_hook_docs_in_pmt_t docs
WHERE apin.invoice_id = apid.invoice_id
AND apin.invoice_id = apsi.invoice_id
-- AND apsi.pay_selected_check_id = P_Selected_Check_Id
and docs.payment_id = P_Selected_Check_Id
and apsi.invoice_id = docs.calling_app_doc_unique_ref2
-- AND apsi.original_invoice_id IS NULL
AND docs.dont_pay_flag = 'N'
AND apid.line_type_lookup_code <> 'AWT'
AND docs.calling_app_id = 200
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
ORDER BY apin.invoice_id,
docs.calling_app_doc_unique_ref3,
apid.invoice_distribution_id ; -- Lines
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id= '||to_char(P_Selected_Check_Id));
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
OPEN c_batch_invoice_amounts (P_Selected_Check_Id);
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
FETCH c_batch_invoice_amounts INTO l_invoice_id,
l_dist_line_no,
l_amount,
l_tax_inclusive_amount,
l_payment_amount,
l_invo_payment_num;
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
IF ((l_previous_invoice_id IS NOT NULL AND
l_previous_invoice_id <> l_invoice_id)
OR (l_previous_invoice_id IS NOT NULL AND
l_previous_invoice_id = l_invoice_id AND
l_invo_payment_num <> l_previous_inv_pay_num))
THEN
FOR i IN l_initial_position .. (l_position - 1) LOOP
P_Tab_Inv_Amounts(i).invoice_amount := l_invoice_amount;
JL_ZZ_AP_EXT_AWT_UTIL.Debug(' AUTOSELECT and l_previous_invoice_id <> l_invoice_id');
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
-- Debug Information
IF (DEBUG_Var = 'Y') THEN
JL_ZZ_AP_EXT_AWT_UTIL.Debug('Close Cursor c_invoice_amounts');
IF (P_Calling_Module = 'AUTOSELECT') THEN
-------------------------
-- Processes last amount
-------------------------
P_Tab_Inv_Amounts(P_Tab_Inv_Amounts.COUNT).taxable_base_amount :=
P_Tab_Inv_Amounts(P_Tab_Inv_Amounts.COUNT).payment_amount -
l_cumulative_amount;
JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Last Row for AUTOSELECT - Payment Amount');
', Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
', Currency Code= ' || P_Currency_Code ||
', Calling Module= ' || P_Calling_Module);
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
EXIT WHEN (P_Tab_Inv_Amounts(i).invoice_id > P_Invoice_Id);
SELECT max(seq_num)
INTO l_seq_num
FROM jl_ar_ap_sup_awt_cr_lts
WHERE po_vendor_id = P_Vendor_Id
AND awt_type_code = P_AWT_Type_Code;
SELECT balance
INTO l_credit_letter_amount
FROM jl_ar_ap_sup_awt_cr_lts
WHERE po_vendor_id = P_Vendor_Id
AND awt_type_code = P_AWT_Type_Code
AND seq_num = l_seq_num;
* Name : Update_Credit_Letter *
* Purpose : Updates the withheld amount for each tax name contained *
* into the PL/SQL table. The credit letters table is also *
* updated *
* *
**************************************************************************/
PROCEDURE Update_Credit_Letter
(P_Vendor_Id IN Number,
P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
P_AWT_Date IN Date,
P_Payment_Num IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
l_credit_letter_amount Number;
'Update_Credit_Letter<--' || P_Calling_Sequence;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('FUNCTION Update_Credit_Letter(+)');
Insert_Credit_Letter_Amount (P_Vendor_Id,
P_Rec_AWT_Type.awt_type_code,
l_tax_id,
P_AWT_Date,
l_orig_withheld_amount,
l_actual_withheld_amount,
l_credit_letter_amount,
'AA',
P_Payment_Num,
P_Check_Id,
P_Selected_Check_Id,
l_calling_sequence,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
Insert_Credit_Letter_Amount (P_Vendor_Id,
P_Rec_AWT_Type.awt_type_code,
l_tax_id,
P_AWT_Date,
l_orig_withheld_amount,
l_actual_withheld_amount,
l_credit_letter_amount,
'AA',
P_Payment_Num,
P_Check_Id,
P_Selected_Check_Id,
l_calling_sequence,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('FUNCTION Update_Credit_Letter(-)');
', Selected_Check_Id= ' || to_char(P_Selected_Check_Id));
END Update_Credit_Letter;
* Name : Insert_Credit_Letter_Amount *
* Purpose : Stores current information about credit letters into the *
* JL_AR_AP_SUP_AWT_CR_LTS table *
* *
**************************************************************************/
PROCEDURE Insert_Credit_Letter_Amount
(P_Vendor_Id IN Number,
P_AWT_Type_Code IN Varchar2,
P_Tax_Id IN Number,
P_AWT_Date IN Date,
P_Withheld_Amount IN Number,
P_Actual_Withheld_Amount IN Number,
P_Balance IN Number,
P_Status IN Varchar2,
P_Payment_Num IN Number,
P_Check_Id IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
l_debug_info Varchar2(300);
'Insert_Credit_Letter_Amount<--' ||
P_Calling_Sequence;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Insert_Credit_Letter_Amount(+)');
INSERT INTO jl_ar_ap_sup_awt_cr_lts
(seq_num,
po_vendor_id,
awt_type_code,
tax_id,
trx_date,
calc_wh_amnt,
act_wheld_amnt,
balance,
check_id,
selected_check_id,
pay_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
request_id,
status)
VALUES
(jl_ar_ap_sup_awt_cr_lts_s.nextval,
P_Vendor_Id,
P_AWT_Type_Code,
P_Tax_Id,
P_AWT_Date,
P_Withheld_Amount,
P_Actual_Withheld_Amount,
P_Balance,
P_Check_Id,
P_Selected_Check_Id,
P_Payment_Num,
fnd_global.user_id,
sysdate,
P_Last_Updated_By,
sysdate,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Status);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Insert_Credit_Letter_Amount(-)');
', Selected Check Id= ' || to_char(P_Selected_Check_Id));
END Insert_Credit_Letter_Amount;
P_Selected_Check_Id IN Number,
P_AWT_Date IN Date,
P_Payment_Num IN Number,
P_Calling_Sequence IN Varchar2,
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null)
IS
---------------------
-- Types definition
---------------------
TYPE Rec_Credit_Letter IS RECORD
(
vendor_id Number,
awt_type_code Varchar2(30),
amount_to_reverse Number
);
P_Selected_Check_Id IN Number) IS
SELECT jlcl.po_vendor_id vendor_id,
jlcl.awt_type_code awt_type_code,
jlcl.calc_wh_amnt calc_wh_amnt,
jlcl.act_wheld_amnt act_wheld_amnt
FROM jl_ar_ap_sup_awt_cr_lts jlcl
WHERE jlcl.status = 'AA'
AND ((P_Check_Id IS NOT NULL AND
jlcl.check_id = P_Check_Id) OR
(P_Selected_Check_Id IS NOT NULL AND
jlcl.selected_check_id = P_Selected_Check_Id))
ORDER BY jlcl.po_vendor_id,
jlcl.awt_type_code,
jlcl.seq_num
FOR UPDATE OF jlcl.status;
OPEN c_credit_letters (P_Check_Id, P_Selected_Check_Id);
UPDATE jl_ar_ap_sup_awt_cr_lts
SET status = 'AR'
WHERE CURRENT OF c_credit_letters;
Insert_Credit_Letter_Amount(tab_cr_letter(i).vendor_id,
tab_cr_letter(i).awt_type_code,
null, -- Tax ID
P_AWT_Date,
null, -- Calc. Withheld Amount
null, -- Actual Withheld Amount
l_balance,
'AR', -- Status
P_Payment_Num,
P_Check_Id,
P_Selected_Check_Id,
l_calling_sequence,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id);
', Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
', Payment Num= ' || to_char(P_Payment_Num));
* Name : Update_Quick_Payment *
* Purpose : Updates the payment amount by subtracting the withheld *
* amount. *
* *
**************************************************************************/
PROCEDURE Update_Quick_Payment
(P_Check_Id IN Number,
P_Calling_Sequence IN Varchar2)
IS
------------------------------
-- Local variables definition
------------------------------
l_invoice_payment_id Number;
SELECT apip.invoice_payment_id invoice_payment_id,
apip.invoice_id invoice_id,
apip.exchange_rate pay_exchange_rate,
apip.payment_num payment_num,
apip.amount amount,
apip.payment_base_amount payment_base_amount,
apip.invoice_base_amount invoice_base_amount
FROM ap_invoice_payments apip
WHERE apip.check_id = P_Check_Id
FOR UPDATE OF apip.amount,
apip.payment_base_amount,
apip.invoice_base_amount;
SELECT apch.amount amount,
apch.base_amount base_amount,
apch.currency_code currency_code -- Bug 2886571
FROM ap_checks apch
WHERE apch.check_id = P_Check_Id
FOR UPDATE OF apch.amount,
apch.base_amount;
'Update_Quick_Payment<--' || P_Calling_Sequence;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Update_Quick_Payment(+)');
SELECT nvl(sum(apid.amount), 0)
INTO l_withhold_amount
FROM ap_invoice_distributions apid
WHERE apid.invoice_id = l_invoice_id
AND apid.awt_invoice_payment_id = l_invoice_payment_id
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y';
SELECT apin.exchange_rate,
apps.payment_cross_rate,
apin.payment_currency_code
INTO l_inv_exchange_rate,
l_payment_cross_rate,
l_payment_currency_code -- Bug 2886571
FROM ap_invoices apin,
ap_payment_schedules apps
WHERE apin.invoice_id = l_invoice_id
AND apps.invoice_id = l_invoice_id
AND apps.payment_num = l_payment_num;
UPDATE ap_payment_schedules
SET amount_remaining = ap_utilities_pkg.ap_round_currency(
amount_remaining - (l_withhold_amount * nvl(l_payment_cross_rate, 1)),
l_payment_currency_code),
payment_status_flag = decode( ap_utilities_pkg.ap_round_currency(amount_remaining -
(l_withhold_amount *
nvl(l_payment_cross_rate, 1)),l_payment_currency_code),
0, 'Y',
amount_remaining,
payment_status_flag, 'P')
WHERE invoice_id = l_invoice_id
AND payment_num = l_payment_num;
UPDATE ap_invoices
SET amount_paid = ap_utilities_pkg.ap_round_currency(
nvl(amount_paid, 0) +
(l_withhold_amount *
nvl(l_payment_cross_rate, 1)),l_payment_currency_code),
payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status(l_invoice_id)
WHERE invoice_id = l_invoice_id;
AP_AIP_TABLE_HANDLER_PKG.Update_Amounts(
l_invoice_payment_id
,l_pay_amount
,l_invoice_base_amount
,l_payment_base_amount
,l_calling_sequence);
AP_AC_TABLE_HANDLER_PKG.Update_Amounts(
P_check_id
,l_amount
,l_base_amount
,l_calling_sequence);
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Procedure Update_Quick_Payment(-)');
END Update_Quick_Payment;
* Name : Update_Payment_Batch *
* Purpose : Updates the amounts of the payment batch by subtracting *
* the withholding amount. *
* just update invoices in same payment check *
**************************************************************************/
PROCEDURE Update_Payment_Batch
(P_Checkrun_Name IN Varchar2,
P_Checkrun_ID IN Number,
P_Selected_Check_Id IN Number,
P_Calling_Sequence IN Varchar2)
IS
----------------------
-- Cursor definition
----------------------
CURSOR c_selected_invoices (P_Selected_Check_Id IN Number) IS
/* RG update documents
SELECT apsi.invoice_id invoice_id,
apsi.payment_num payment_num,
apsi.payment_amount payment_amount,
nvl(apsi.invoice_exchange_rate, 1) invoice_exchange_rate,
nvl(apsi.payment_cross_rate, 1) payment_cross_rate
FROM ap_selected_invoices apsi
WHERE apsi.pay_selected_check_id = P_Selected_Check_id
AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
AND apsi.original_invoice_id IS NULL
FOR UPDATE;
SELECT distinct docs.CALLING_APP_DOC_UNIQUE_REF2 invoice_id, --bug 12613506
docs.document_payable_id document_payable_id,
docs.CALLING_APP_DOC_UNIQUE_REF3 payment_num,
docs.document_amount payment_amount ,
-- Payment Exchange Rate ER 8648739 Start 9
-- nvl(apsi.invoice_exchange_rate, 1) invoice_exchange_rate,
nvl(apsi.payment_exchange_rate, 1) payment_exchange_rate,
-- Payment Exchange Rate ER 8648739 End 9
nvl(apsi.payment_cross_rate, 1) payment_cross_rate
FROM iby_hook_docs_in_pmt_t docs,
ap_selected_invoices apsi
WHERE docs.payment_id = P_Selected_Check_id
AND docs.calling_app_id = 200
AND apsi.invoice_id = docs.calling_app_doc_unique_ref2
AND nvl(docs.dont_pay_flag,'N')='N';
CURSOR c_selected_invoice_checks (P_Selected_Check_Id IN Number) IS
SELECT apsic.check_amount check_amount,
apsic.vendor_amount vendor_amount
FROM ap_selected_invoice_checks apsic
WHERE apsic.selected_check_id = P_Selected_Check_Id
FOR UPDATE OF apsic.check_amount,
apsic.vendor_amount;
CURSOR c_selected_invoice_checks (P_Selected_Check_Id IN Number) IS
SELECT ipmt.payment_amount payment_amount
FROM iby_hook_payments_t ipmt
WHERE ipmt.payment_id = P_Selected_Check_id
AND ipmt.calling_app_id = 200
FOR UPDATE OF ipmt.payment_amount;
rec_sel_inv c_selected_invoices%ROWTYPE;
'Update_Payment_Batch<--' || P_Calling_Sequence;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Update_Payment_Batch(+)');
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Selected_Check_Id: '||to_char(P_Selected_Check_Id));
SELECT count(*)
INTO l_count_inv
FROM iby_hook_docs_in_pmt_t docs,
-- RG ap_selected_invoices apsi,
ap_awt_temp_distributions awtd
WHERE docs.payment_id = P_Selected_Check_Id
AND nvl(docs.dont_pay_flag,'N') ='N'
AND docs.calling_app_doc_unique_ref2 = awtd.invoice_id
AND docs.calling_app_id=200 ;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Just update the invoices in the same payment check: '||to_char(l_count_inv));
SELECT SUM(docs.document_amount)
INTO l_prop_payment_amount
FROM iby_hook_docs_in_pmt_t docs
-- ap_selected_invoices apsi
WHERE docs.payment_id = P_Selected_Check_Id
AND nvl(docs.dont_pay_flag,'N') ='N'
AND docs.calling_app_id =200;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('OPEN Cursor c_selected_invoices');
OPEN c_selected_invoices (P_Selected_Check_Id);
FETCH c_selected_invoices INTO rec_sel_inv;
EXIT WHEN c_selected_invoices%NOTFOUND;
SELECT payment_currency_code
INTO l_payment_currency_code
FROM ap_invoices_all
WHERE invoice_id = rec_sel_inv.invoice_id;
SELECT document_currency_code
INTO l_payment_currency_code
FROM IBY_HOOK_DOCS_IN_PMT_T
WHERE payment_id = P_Selected_Check_Id
AND document_payable_id = rec_sel_inv.document_payable_id;
SELECT nvl(sum(withholding_amount), 0)
INTO l_withholding_amount
FROM ap_awt_temp_distributions
WHERE checkrun_name = P_Checkrun_Name
AND checkrun_id= p_checkrun_id
AND invoice_id = rec_sel_inv.invoice_id
AND payment_num = rec_sel_inv.payment_num;
UPDATE ap_selected_invoices apsi
SET apsi.proposed_payment_amount = ap_utilities_pkg.ap_round_currency(
apsi.proposed_payment_amount - l_withholding_amount,
l_payment_currency_code),
apsi.payment_amount = ap_utilities_pkg.ap_round_currency(
apsi.payment_amount - l_withholding_amount,
l_payment_currency_code),
-- bug: 9037712 :: Amount_remaining should not be updated.
-- JL code modified to be in sync with AP side processing for PPRs.
-- apsi.amount_remaining = ap_utilities_pkg.ap_round_currency(
-- apsi.amount_remaining - l_withholding_amount,
-- l_payment_currency_code),
apsi.withholding_amount = ap_utilities_pkg.ap_round_currency(
l_withholding_amount, l_payment_currency_code)
WHERE invoice_id = rec_sel_inv.invoice_id
AND payment_num = rec_sel_inv.payment_num; --bug 12921144
UPDATE iby_hook_docs_in_pmt_t docs
SET docs.document_amount = ap_utilities_pkg.ap_round_currency(
docs.document_amount - l_withholding_amount,
l_payment_currency_code),
docs.amount_withheld = ap_utilities_pkg.ap_round_currency(
l_withholding_amount, l_payment_currency_code)
WHERE document_payable_id = rec_sel_inv.document_payable_id;
CLOSE c_selected_invoices;
OPEN c_selected_invoice_checks (P_Selected_Check_Id);
FETCH c_selected_invoice_checks INTO l_check_amount;
IF (NOT c_selected_invoice_checks%NOTFOUND) THEN
/* RG
UPDATE ap_selected_invoice_checks apsic
SET apsic.check_amount = ap_utilities_pkg.ap_round_currency(
NVL(l_prop_payment_amount, l_check_amount ) -
l_total_wh_amount, apsic.currency_code),
apsic.vendor_amount = ap_utilities_pkg.ap_round_currency(
-1 * l_total_wh_amount, apsic.currency_code)
WHERE CURRENT OF c_selected_invoice_checks;
UPDATE iby_hook_payments_t ipmt
SET ipmt.payment_amount = ap_utilities_pkg.ap_round_currency(
NVL(l_prop_payment_amount, l_check_amount ) -
l_total_wh_amount, ipmt.payment_currency_code)
WHERE CURRENT OF c_selected_invoice_checks;
CLOSE c_selected_invoice_checks;
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Update_Payment_Batch(-)');
', Selected Check Id= ' || to_char(P_Selected_Check_Id));
END Update_Payment_Batch;
SELECT count('Withholding Already Calculated')
INTO l_count
FROM ap_awt_temp_distributions apatd
WHERE apatd.invoice_id = P_Invoice_Id
AND apatd.tax_name = P_Tax_Name;
SELECT nvl(sum(apid.amount), 0)
INTO l_withheld_amount
FROM ap_invoice_distributions apid
WHERE apid.invoice_id = P_Invoice_Id
AND apid.line_type_lookup_code = 'AWT'
AND apid.withholding_tax_code_id = P_Tax_Id
-- added recently
AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y';
P_Selected_Check_Id IN Number,
P_Calling_Module IN Varchar2,
P_Total_Wh_Amount IN Number,
P_Calling_Sequence IN Varchar2,
P_Vendor_Name OUT NOCOPY Varchar2,
P_Vendor_Site_Code OUT NOCOPY Varchar2)
--P_Payment_Amount OUT NOCOPY Number) Bug# 2807464
RETURN Boolean
IS
l_payment_amount Number := 0;
SELECT nvl(apchk.base_amount, apchk.amount),
apchk.vendor_name,
apchk.vendor_site_code
INTO l_payment_amount,
l_vendor_name,
l_vendor_site_code
FROM ap_checks apchk
WHERE apchk.check_id = P_Check_Id;
ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
SELECT nvl(sum(docs.document_amount/
nvl(apsi.payment_cross_rate, 1) *
nvl(apsi.invoice_exchange_rate, 1)), 0)
INTO l_payment_amount
FROM iby_hook_docs_in_pmt_t docs,
ap_selected_invoices apsi
WHERE docs.payment_id = P_Selected_Check_id
AND apsi.invoice_id = docs.calling_app_doc_unique_ref2
AND docs.dont_pay_flag = 'N'
-- AND apsi.pay_selected_check_id = P_Selected_Check_id
-- AND apsi.original_invoice_id IS NULL
AND docs.calling_app_id=200;
/* RG SELECT vendor_name,
vendor_site_code
INTO l_vendor_name,
l_vendor_site_code
FROM ap_selected_invoice_checks
WHERE selected_check_id = P_Selected_Check_id;
SELECT asi.vendor_name,
asi.vendor_site_code
INTO l_vendor_name,
l_vendor_site_code
FROM IBY_HOOK_DOCS_IN_PMT_T docs,
ap_selected_invoices_all asi
WHERE docs.payment_id = P_Selected_Check_id
AND docs.calling_app_doc_unique_ref2 = asi.invoice_id
AND docs.calling_app_id=200;
select a.vendor_name, b.vendor_site_code
into l_vendor_name,
l_vendor_site_code
from ap_suppliers a, ap_supplier_sites_all b,
iby_hook_payments_t c
where c.PAYEE_PARTY_ID = a.party_id
and c.SUPPLIER_SITE_ID = b.vendor_site_id
and a.vendor_id = b.vendor_id
and c.payment_id = P_Selected_Check_id;
', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
', Calling Module= ' || P_Calling_Module ||
', Total Wh Amount= ' || to_char(P_Total_Wh_Amount));
* Purpose : Updates the credit letters table in order to store the *
* the final check ID, when users confirm a payment batch. *
* This procedure is not called for Quick Payments because *
* the check ID is known from the begining. *
* *
**************************************************************************/
PROCEDURE Confirm_Credit_Letters
(P_Checkrun_Name IN Varchar2,
p_checkrun_id IN Number,
P_Calling_Sequence IN Varchar2)
IS
------------------------------
-- Local variables definition
------------------------------
l_check_id Number;
l_selected_check_id Number;
CURSOR c_selected_invoice_checks
IS
SELECT distinct(d.payment_id) check_id
FROM iby_fd_payments_v p,iby_fd_docs_payable_v d
WHERE d.calling_app_doc_unique_ref1 = to_char(p_checkrun_id) --bug9836895
AND p.payment_id = d.payment_id
AND d.calling_app_id = 200; --bug9836895
SELECT apsic.selected_check_id selected_check_id,
apsic.check_id check_id
FROM ap_selected_invoice_checks apsic
WHERE checkrun_name = P_Checkrun_Name;
OPEN c_selected_invoice_checks;
FETCH c_selected_invoice_checks INTO l_check_id;
EXIT WHEN c_selected_invoice_checks%NOTFOUND;
UPDATE jl_ar_ap_sup_awt_cr_lts
SET check_id = l_check_id,
selected_check_id = null
WHERE selected_check_id = l_check_id;
CLOSE c_selected_invoice_checks;
* Purpose : Sets the "Ok To Pay" flag for all the selected invoices *
* within the payment when the calculation routine is not *
* successful *
* *
* RG Sets the DONT_PAY_FLAG for all documents in payment *
**************************************************************************/
PROCEDURE Reject_Payment_Batch
(P_Selected_Check_Id IN Number,
P_AWT_Success IN Varchar2,
P_Calling_Sequence IN Varchar2)
IS
------------------------------
-- Local variables definition
------------------------------
l_ok_to_pay_flag Varchar2(10);
CURSOR c_selected_invoices (P_Selected_Check_Id IN Number) IS
SELECT docs.dont_pay_flag dont_pay_flag ,
docs.dont_pay_reason_code dont_pay_reason,
docs.calling_app_doc_unique_ref2 invoice_id
-- apsi.ok_to_pay_flag ok_to_pay_flag,
-- apsi.dont_pay_reason_code dont_pay_reason_code,
-- apsi.dont_pay_description dont_pay_description
FROM iby_hook_docs_in_pmt_t docs
-- ap_selected_invoices apsi
WHERE docs.payment_id = P_Selected_Check_id
AND docs.dont_pay_flag = 'N'
AND docs.calling_app_id = 200
-- apsi.pay_selected_check_id = P_Selected_Check_id
-- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
-- AND apsi.original_invoice_id IS NULL
FOR UPDATE OF docs.dont_pay_flag,
docs.dont_pay_reason_code;
JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Selected_Check_Id: '||to_char(P_Selected_Check_Id));
OPEN c_selected_invoices (P_Selected_Check_Id);
FETCH c_selected_invoices INTO l_ok_to_pay_flag,
l_dont_pay_reason_code,
l_invoice_id;
EXIT WHEN c_selected_invoices%NOTFOUND;
UPDATE iby_hook_docs_in_pmt_t docs
SET docs.dont_pay_flag = 'Y',
docs.dont_pay_reason_code = AWT_ERROR
WHERE CURRENT OF c_selected_invoices;
UPDATE ap_selected_invoices
SET ok_to_pay_flag = 'N',
dont_pay_reason_code = AWT_ERROR
WHERE invoice_id = l_invoice_id;
CLOSE c_selected_invoices;
UPDATE iby_hook_payments_t ipmt
SET ipmt.dont_pay_flag = 'Y',
ipmt.dont_pay_reason_code = AWT_ERROR
WHERE ipmt.payment_id = P_Selected_Check_id;
' Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
', AWT Success= ' || P_AWT_Success);
,P_Last_Updated_By IN number
,P_Last_Update_Login IN number
,P_Program_Application_Id IN number
default null
,P_Program_Id IN number
default null
,P_Request_Id IN number
default null
,P_Awt_Success OUT NOCOPY varchar2
,P_Invoice_Payment_Id IN number
default null
,P_Check_Id IN number
)
IS
BEGIN
-- Debug Information
IF (DEBUG_Var = 'Y') THEN
JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE JL_CALL_DO_AWT(+)');
,P_Last_Updated_By
,P_Last_Update_Login
,P_Program_Application_id
,P_Program_Id
,P_Request_Id
,P_Awt_Success
,P_Invoice_Payment_Id
,P_Check_Id
);
* Purpose : Updates the payment amount by adding the withheld *
* amount. *
* *
**************************************************************************/
PROCEDURE Undo_Quick_Payment
(P_Check_Id IN Number,
P_Old_Check_Id IN Number,
P_Calling_Sequence IN Varchar2)
IS
------------------------------
-- Local variables definition
------------------------------
l_invoice_payment_id Number;
SELECT apip.invoice_payment_id invoice_payment_id,
apip.invoice_id invoice_id,
apip.exchange_rate pay_exchange_rate,
apip.payment_num payment_num,
apip.amount amount,
apip.payment_base_amount payment_base_amount,
apip.invoice_base_amount invoice_base_amount
FROM ap_invoice_payments apip
WHERE apip.check_id = P_Check_Id
FOR UPDATE OF apip.amount,
apip.payment_base_amount,
apip.invoice_base_amount;
SELECT apch.amount amount,
apch.base_amount base_amount
FROM ap_checks apch
WHERE apch.check_id = P_Check_Id
FOR UPDATE OF apch.amount,
apch.base_amount;
SELECT sum(aid.amount)
FROM ap_invoice_distributions aid,
ap_invoice_payments aip,
ap_invoices ai
WHERE aid.invoice_id = aip.invoice_id
AND ai.invoice_id = aid.invoice_id
AND aid.invoice_id = P_Invoice_Id
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
AND aid.amount < 0
AND aip.check_id = P_Old_Check_Id
AND ai.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT')
-- added recently
--bug 12335657 AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y'
UNION
SELECT sum(aid.amount)
FROM ap_invoice_distributions aid,
ap_invoice_payments aip,
ap_invoices ai
WHERE aid.invoice_id = aip.invoice_id
AND ai.invoice_id = aid.invoice_id
AND aid.invoice_id = P_Invoice_Id
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
AND aid.amount > 0
AND aip.check_id = P_Old_Check_Id
AND ai.invoice_type_lookup_code IN ('CREDIT','DEBIT')
-- added recently
--bug 12335657 AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y'
GROUP BY aid.invoice_id;
SELECT apin.exchange_rate,
apps.payment_cross_rate
INTO l_inv_exchange_rate,
l_payment_cross_rate
FROM ap_invoices apin,
ap_payment_schedules apps
WHERE apin.invoice_id = l_invoice_id
AND apps.invoice_id = l_invoice_id
AND apps.payment_num = l_payment_num;
UPDATE ap_payment_schedules
SET amount_remaining = amount_remaining +
(l_withhold_amount *
nvl(l_payment_cross_rate, 1)),
payment_status_flag = decode(amount_remaining +
(l_withhold_amount *
nvl(l_payment_cross_rate, 1)),
0, 'Y',
amount_remaining,
payment_status_flag, 'P')
WHERE invoice_id = l_invoice_id
AND payment_num = l_payment_num;
UPDATE ap_invoices
SET amount_paid = nvl(amount_paid, 0) -
(l_withhold_amount *
nvl(l_payment_cross_rate, 1)),
payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status(l_invoice_id)
WHERE invoice_id = l_invoice_id;
AP_AIP_TABLE_HANDLER_PKG.Update_Amounts(
l_invoice_payment_id
,l_pay_amount
,l_invoice_base_amount
,l_payment_base_amount
,l_calling_sequence);
AP_AC_TABLE_HANDLER_PKG.Update_Amounts(
P_check_id
,l_amount
,l_base_amount
,l_calling_sequence);