Oracle apps AP Payment R12
For AP Invoice api check this link :
https://mogalafzal.blogspot.com/2017/06/oracle-apps-r12-ap-invoice-creation.html
For AP payment starts from here :
CREATE OR REPLACE PROCEDURE XX_AP_INVOICE
(p_invoice_id number,
p_internal_bank_acct_id number, --from invoice payment form
p_payment_method_code varchar2, --PAYMENT_METHOD_CODE from the invoice workbench
p_payment_profile_id number, --166 check any invoice payment from form
p_check_date date default sysdate,
x_return_status out varchar2,
x_msg_data out varchar2,
x_check_id out number
) AS
G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PAY_SINGLE_INVOICE_PKG';
G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_PAY_SINGLE_INVOICE_PKG';
-- Bug 2842080
p_api_version NUMBER := 1.0;
p_init_msg_list VARCHAR2(200) := fnd_api.g_true;
--p_invoice_id NUMBER := 10473423;
p_payment_type_flag VARCHAR2(200) := 'A';
--p_internal_bank_acct_id NUMBER := 11360;
--p_payment_method_code VARCHAR2(200) := 'FUJ_Check';
--p_payment_profile_id NUMBER := 166;
p_payment_document_id NUMBER := null;
p_take_discount VARCHAR2(200) := 'N';
--p_check_date DATE := trunc(sysdate);
p_doc_category_code VARCHAR2(200) := null;
p_exchange_rate_type VARCHAR2(200) := null;
p_exchange_rate NUMBER := null;
p_exchange_date DATE := null;
--x_return_status VARCHAR2(200) ;
x_msg_count VARCHAR2(200);
--x_msg_data VARCHAR2(200) ;
x_errorids IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab;-- := l_error_ids
l_amount NUMBER;
l_base_amount NUMBER;
l_num_invs_sel_for_pmt NUMBER;
l_return_code NUMBER;
l_application_id NUMBER;
l_name VARCHAR2(80);
l_dummy_rowid VARCHAR2(18);
l_seq_num_profile VARCHAR2(80);
l_hold_all_payments_flag ap_suppliers.hold_all_payments_flag%TYPE;
l_active_pay_sites ap_supplier_sites_all.pay_site_flag%TYPE;
l_accounting_event_id NUMBER(38);
l_quick_check_id AP_CHECKS.checkrun_name%type;
l_next_check_number AP_CHECKS.check_number%type;
l_check_number AP_CHECKS.check_number%type;
l_period_name GL_PERIOD_STATUSES.period_name%TYPE;
l_debug_info VARCHAR2(100);
l_curr_calling_sequence VARCHAR2(2000);
l_doc_category_code varchar2(30);
l_seqval number;
l_dbseqid number;
l_docseq_id number;
l_docseq_type varchar(1);
l_docseq_name varchar2(30);
l_db_seq_name varchar2(30);
l_seq_ass_id number;
l_prd_tab_name varchar2(30);
l_aud_tab_name varchar2(30);
l_msg_flag varchar(1);
l_valid_sequence_exists varchar2(30);
l_payment_type_flag ap_checks.payment_type_flag%TYPE; -- Bug3343314
l_creation_date ap_checks.creation_date%TYPE; -- Bug3343314
l_created_by ap_checks.created_by%TYPE; -- Bug3343314
l_last_update_date ap_checks.last_update_date%TYPE; -- Bug3343314
l_last_updated_by ap_checks.last_updated_by%TYPE; -- Bug3343314
l_last_update_login ap_checks.last_update_login%TYPE; -- Bug3343314
l_org_id NUMBER;
l_bank_account_id ce_bank_acct_uses_all.bank_acct_use_id%TYPE;
l_bank_account_name ce_bank_accounts.bank_account_name%TYPE;
l_processing_type iby_payment_profiles.processing_type%TYPE;
l_print_instr_immed_flag iby_payment_profiles.print_instruction_immed_flag%TYPE;
l_default_printer iby_payment_profiles.default_printer%TYPE;
l_payment_document_id ce_payment_documents.payment_document_id %TYPE;
l_bills_payable iby_payment_methods_vl.support_bills_payable_flag%TYPE;
l_maturity_date_offset_days iby_payment_methods_vl.maturity_date_offset_days%TYPE;
l_maturity_date ap_checks.future_pay_due_date%TYPE;
l_vendor_type_lookup_code ap_suppliers.vendor_type_lookup_code%TYPE;
l_return_status VARCHAR2(100);
l_return_message VARCHAR2(3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(3000);
l_temp_status VARCHAR2(1);
l_is_duplicate BOOLEAN := TRUE;
l_module_name VARCHAR2(200) := G_PKG_NAME ||
'.ap_pay_invoice_in_full';
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'ap_pay_invoice_in_full';
l_errbuf VARCHAR2(5000);
l_retcode VARCHAR2(2000);
l_transaction_type AP_PAYMENT_HISTORY_ALL.TRANSACTION_TYPE%TYPE;
TYPE r_ap_system_param_info IS RECORD
(
Auto_Calc_Int_Flag AP_SYSTEM_PARAMETERS_ALL.Auto_Calculate_Interest_Flag%TYPE
,Base_Currency_Code AP_SYSTEM_PARAMETERS_ALL.Base_Currency_Code%TYPE
,Pay_Doc_Override AP_SYSTEM_PARAMETERS_ALL.Pay_Doc_Category_Override%TYPE
,Make_Rate_Mandatory_Flag AP_SYSTEM_PARAMETERS_ALL.Make_Rate_Mandatory_Flag%TYPE
,Set_Of_Books_id AP_SYSTEM_PARAMETERS_ALL.Set_Of_Books_id%TYPE
,Default_Exchange_Rate_Type AP_SYSTEM_PARAMETERS_ALL.Default_Exchange_Rate_Type%TYPE
,Multi_Currency_Flag AP_SYSTEM_PARAMETERS_ALL.Multi_Currency_Flag%TYPE
,Xrate_type AP_SYSTEM_PARAMETERS_ALL.default_exchange_rate_type%TYPE
);
TYPE r_check_info IS RECORD
(
Xrate AP_CHECKS_ALL.exchange_rate%TYPE
,Xrate_date AP_CHECKS_ALL.exchange_date%TYPE
,Xrate_type AP_CHECKS_ALL.exchange_rate_type%TYPE
,Check_id AP_CHECKS_ALL.check_id%TYPE
,Checkrun_Name AP_CHECKS_ALL.checkrun_name%TYPE
,Internal_Bank_Acct_Id CE_BANK_ACCT_USES_ALL.bank_acct_use_id%TYPE
,Bank_Account_Name CE_BANK_ACCOUNTS.bank_account_name%TYPE
,Bank_Account_Num CE_BANK_ACCOUNTS.bank_account_num%TYPE
,Bank_Account_Type CE_BANK_ACCOUNTS.account_classification%TYPE
,Bank_Num CE_BANK_BRANCHES_V.branch_number%TYPE
,Legal_Entity_Id CE_BANK_ACCOUNTS.account_owner_org_id%TYPE
,Status_Lookup_Code AP_CHECKS_ALL.status_lookup_code%TYPE
);
TYPE r_ap_ven_sites_info IS RECORD
(
Address_Line1 HZ_LOCATIONS.Address1%TYPE
,Address_Line2 HZ_LOCATIONS.Address2%TYPE
,Address_Line3 HZ_LOCATIONS.Address3%TYPE
,City HZ_LOCATIONS.City%TYPE
,country HZ_LOCATIONS.Country%TYPE
,Vendor_site_code AP_SUPPLIER_SITES_ALL.Vendor_Site_Code%TYPE
,Zip HZ_LOCATIONS.Postal_Code%TYPE
,Province HZ_LOCATIONS.Province%TYPE
,State HZ_LOCATIONS.State%TYPE
,Address_Line4 HZ_LOCATIONS.Address4%TYPE
,County HZ_LOCATIONS.Country%TYPE
,Address_Style HZ_LOCATIONS.Address_Style%TYPE
,Vendor_Id AP_SUPPLIERS.Vendor_ID%TYPE
,Vendor_Site_Id AP_SUPPLIER_SITES_ALL.Vendor_Site_ID%TYPE
,Pay_Site_Flag AP_SUPPLIER_SITES_ALL.Pay_Site_Flag%TYPE
,Primary_Pay_Site AP_SUPPLIER_SITES_ALL.Primary_Pay_Site_Flag%TYPE
);
TYPE r_vendor_info IS RECORD
(
Auto_calc_int_flag AP_SUPPLIERS.Auto_calculate_interest_flag%TYPE
,Vendor_name AP_SUPPLIERS.Vendor_name%TYPE
);
TYPE r_apinv_info IS RECORD
(
Vendor_id AP_INVOICES_ALL.vendor_id%TYPE
,Vendor_Site_id AP_INVOICES_ALL.vendor_site_id%TYPE
,Party_Id AP_INVOICES_ALL.party_id%TYPE
,Party_Site_Id AP_INVOICES_ALL.party_site_id%TYPE
,External_Bank_Account_Id AP_PAYMENT_SCHEDULES_ALL.external_bank_account_id%TYPE
,Pmt_Currency_code AP_INVOICES_ALL.payment_currency_code%TYPE
,Payment_Method AP_INVOICES_ALL.Payment_Method_Code%TYPE
,Inv_Currency_code AP_INVOICES_ALL.invoice_currency_code%TYPE
,Org_id AP_INVOICES_ALL.org_id%TYPE
,Payment_Function AP_INVOICES_ALL.payment_function%TYPE
,Pay_Proc_Trxn_Type_Code AP_INVOICES_ALL.pay_proc_trxn_type_code%TYPE
);
l_vendor_rec r_vendor_info;
l_inv_rec r_apinv_info;
l_apvs_rec r_ap_ven_sites_info;
l_check_rec r_check_info;
l_asp_rec r_ap_system_param_info;
l_manual_payment_flag VARCHAR2(1);
l_num_printed_docs NUMBER;
l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
l_pmt_ref_num IBY_PAYMENTS_ALL.payment_reference_number%TYPE;
l_errorIds IBY_DISBURSE_SINGLE_PMT_PKG.trxnErrorIdsTab;
l_msg_index_out NUMBER;
l_payment_id NUMBER;
l_error_msg VARCHAR2(2000);
BEGIN
/* standard call to check for api compatibility */
IF NOT FND_API.Compatible_API_Call(
l_api_version,
p_api_version,
l_api_name,
G_PKG_NAME) THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
END IF;
/* initialize message list if p_init_msg_list is set to TRUE. */
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
END IF;
/* initialize API return status to success */
x_return_status := FND_API.G_RET_STS_SUCCESS;
l_temp_status := 'S';
l_debug_info := 'Get invoice_info';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
-- All we want to make sure here is that can we pay the invoice
-- that has been passed in (invoice_id)
l_debug_info := 'Select the Invoice if it can be paid ';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
SELECT apiv.vendor_id,
apiv.vendor_site_id,
apiv.party_id,
apiv.party_site_id,
apiv.external_bank_account_id,
ai.payment_currency_code,
ai.invoice_currency_code,
ai.payment_method_code,
ai.org_id,
NVL(ai.payment_function, 'PAYABLES_DISB'),
NVL(ai.pay_proc_trxn_type_code, 'PAYABLES_DOC')
INTO l_inv_rec.vendor_id,
l_inv_rec.vendor_site_id,
l_inv_rec.party_id,
l_inv_rec.party_site_id,
l_inv_rec.external_bank_account_id,
l_inv_rec.pmt_currency_code,
l_inv_rec.inv_currency_code,
l_inv_rec.payment_method,
l_inv_rec.org_id,
l_inv_rec.payment_function,
l_inv_rec.pay_proc_trxn_type_code
FROM ap_invoices_ready_to_pay_v apiv,
ap_invoices_all ai
WHERE apiv.invoice_id = ai.invoice_id
AND apiv.invoice_id = p_invoice_id
AND apiv.payment_method_code = NVL(p_payment_method_code,
apiv.payment_method_code)
GROUP BY apiv.vendor_id,
apiv.vendor_site_id,
apiv.party_id,
apiv.party_site_id,
apiv.external_bank_account_id,
ai.payment_currency_code,
ai.invoice_currency_code,
ai.payment_method_code,
ai.org_id,
ai.payment_function,
ai.pay_proc_trxn_type_code;
l_debug_info := 'Get system parameter info ';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
l_debug_info := 'Select System Option based on Invoice Org Id ';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
SELECT nvl(asp.auto_calculate_interest_flag,'N'),
asp.base_currency_code,
nvl(asp.pay_doc_category_override, 'N'),
nvl(make_rate_mandatory_flag,'N'),
set_of_books_id,
nvl(default_exchange_rate_type, 'User'),
nvl(multi_currency_flag,'N')
INTO l_asp_rec.auto_calc_int_flag,
l_asp_rec.base_currency_code,
l_asp_rec.pay_doc_override,
l_asp_rec.make_rate_mandatory_flag,
l_asp_rec.set_of_books_id,
l_asp_rec.xrate_type,
l_asp_rec.multi_currency_flag
FROM ap_system_parameters_all asp
WHERE org_id = l_inv_rec.org_id;
-- In the Payment work bench user has the option to to change the
-- payment date, so here we check to see if what they have passed
-- or the current date is within an open period.
l_debug_info := 'Get open period name ';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
l_period_name := ap_utilities_pkg.get_current_gl_date(p_check_date,
l_inv_rec.org_id);
IF l_period_name IS NULL THEN
-- We call the ap debug pkg to get the message onto the stack. We pass
-- in a TRUE value for the P_called_Online parameter (the last param)
-- because we don't want the ap debug pkg to get it off the stack at
-- the same time.
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_PERIOD_NOT_OPEN');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END IF;
l_debug_info := 'Checking For Valid Internal Bank Account';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
SELECT BA.account_owner_org_id legal_entity_id,
BA.bank_account_name,
BA.bank_account_num,
BA.account_classification,
CBB.branch_number
INTO l_check_rec.legal_entity_id,
l_check_rec.bank_account_name,
l_check_rec.bank_account_num,
l_check_rec.bank_account_type,
l_check_rec.bank_num
FROM CE_BANK_ACCOUNTS BA,
CE_BANK_BRANCHES_V CBB,
CE_BANK_ACCT_USES_OU_V CBAU
WHERE CBAU.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND CBB.branch_party_id = BA.bank_branch_id
AND SYSDATE < NVL(BA.END_DATE,SYSDATE+1)
AND BA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND CBAU.ap_use_enable_flag = 'Y'
AND CBAU.org_id = l_inv_rec.org_id
AND CBAU.bank_acct_use_id = p_internal_bank_acct_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_CE_INVALID_BANK_ACCT');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
l_debug_info := 'Checking Exchange Rate related info';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
IF (l_inv_rec.pmt_currency_code <> l_asp_rec.base_currency_code) THEN
l_check_rec.xrate_type := nvl(P_Exchange_Rate_Type,
l_asp_rec.xrate_type);
IF (l_check_rec.xrate_type = 'User' ) THEN
IF (P_exchange_rate is NULL) THEN
-- State that we need to to have an exchange rate if the
-- type is USER this is irrespective of the value of the
-- make_rate_mandatory_flag on AP_SYSTEM_PARAMETERS
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_USER_XRATE');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
ELSE
l_check_rec.xrate := P_Exchange_Rate;
END IF;
ELSE -- exchange rate is other than USER
-- --------------------------------------------------------
-- To calculate the exchange_rate we need to see what the
-- the value of the system parameter make_rate_mandatory_flag
-- --------------------------------------------------------
l_check_rec.xrate_date := nvl(trunc(P_exchange_date),trunc(sysdate));
l_check_rec.xrate := ap_utilities_pkg.get_exchange_rate(
l_inv_rec.pmt_currency_code,
l_asp_rec.base_currency_code,
l_check_rec.xrate_type,
l_check_rec.xrate_date,
'APAYFULB');
IF (l_check_rec.xrate is NULL
AND l_asp_rec.make_rate_mandatory_flag = 'Y') THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_XRATE');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END IF; -- mandatory_flag = Y
END IF; -- Exchange Rate = USER
END IF; -- currency_code <> base_currency_code
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_CANNOT_BE_PAID');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
WHEN TOO_MANY_ROWS THEN
-- This means that the payment schedules for the one invoice may
-- have different payment methods. If that is the case, we cannot
-- handle in one payment.
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_MISMATCHED_PMT_SCHEDS');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
l_debug_info := 'Check if the Invoice is being paid by a payment batch';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
SELECT count(*)
INTO l_num_invs_sel_for_pmt
FROM ap_selected_invoices_all
WHERE invoice_id = P_invoice_id;
IF (l_num_invs_sel_for_pmt > 0) THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_INV_PAY_FAIL_SEL_BY_BATCH');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END IF;
/*
|| ==============================================================
|| Verify if we should pay invoices for this supplier
|| -------------------------------------------------------------
|| We want to verify that if Oracle Payables should place all
|| payments for this supplier on HOLD also, verify that the
|| vendor site that we have selected in step 1 is Pay Site
|| which is also active
|| ==============================================================
*/
l_debug_info := 'Verrify Supplier Site Address related info';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
SELECT nvl(asup.hold_all_payments_flag,'N'),
nvl(assp.pay_site_flag, 'N'),
asup.vendor_type_lookup_code
INTO l_hold_all_payments_flag,
l_active_pay_sites,
l_vendor_type_lookup_code
FROM ap_suppliers asup,
ap_supplier_sites_all assp
WHERE asup.vendor_id = l_inv_rec.vendor_id
AND asup.vendor_id = assp.vendor_id
AND assp.vendor_site_id = l_inv_rec.vendor_site_id;
IF (l_hold_all_payments_flag = 'Y' or l_active_pay_sites = 'N') THEN
RAISE NO_DATA_FOUND;
ELSE
IF l_vendor_type_lookup_code <> 'EMPLOYEE' THEN
SELECT hzl.Address1,
hzl.Address2,
hzl.Address3,
hzl.City,
hzl.Country,
hzl.Postal_Code,
hzl.Province,
hzl.State,
hzl.Address4,
hzl.County,
hzl.Address_Style,
asus.Vendor_Id,
asus.Vendor_Site_Id,
asus.Vendor_site_code,
nvl(asus.Pay_Site_Flag,'N'),
nvl(asus.Primary_Pay_Site_Flag,'N')
INTO l_apvs_rec.Address_Line1,
l_apvs_rec.Address_Line2,
l_apvs_rec.Address_Line3,
l_apvs_rec.City,
l_apvs_rec.Country,
l_apvs_rec.Zip,
l_apvs_rec.Province,
l_apvs_rec.State,
l_apvs_rec.Address_Line4,
l_apvs_rec.County,
l_apvs_rec.Address_Style,
l_apvs_rec.Vendor_Id,
l_apvs_rec.Vendor_Site_Id,
l_apvs_rec.Vendor_site_code,
l_apvs_rec.Pay_Site_Flag,
l_apvs_rec.Primary_Pay_Site
FROM ap_supplier_sites_all asus,
hz_locations hzl
WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id
AND asus.location_id = hzl.location_id
AND nvl(trunc(asus.inactive_date),sysdate+1) > trunc(sysdate);
IF l_apvs_rec.Pay_Site_Flag = 'N' THEN
BEGIN
-- We want to search if there is a Primary Pay Site for
-- for this Vendor
SELECT hzl.Address1,
hzl.Address2,
hzl.Address3,
hzl.City,
hzl.Country,
hzl.Postal_Code,
hzl.Province,
hzl.State,
hzl.Address4,
hzl.County,
hzl.Address_Style,
asus.Vendor_Id,
asus.Vendor_Site_Id,
asus.Vendor_Site_Code,
nvl(asus.Pay_Site_Flag,'N'),
nvl(asus.Primary_Pay_Site_Flag,'N')
INTO l_apvs_rec.Address_Line1,
l_apvs_rec.Address_Line2,
l_apvs_rec.Address_Line3,
l_apvs_rec.City,
l_apvs_rec.Country,
l_apvs_rec.Zip,
l_apvs_rec.Province,
l_apvs_rec.State,
l_apvs_rec.Address_Line4,
l_apvs_rec.County,
l_apvs_rec.Address_Style,
l_apvs_rec.Vendor_Id,
l_apvs_rec.Vendor_Site_Id,
l_apvs_rec.Vendor_site_code,
l_apvs_rec.Pay_Site_Flag,
l_apvs_rec.Primary_Pay_Site
FROM ap_supplier_sites_all asus,
hz_locations hzl
WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id
AND asus.location_id = hzl.location_id
AND nvl(trunc(asus.inactive_date),sysdate+1)
> trunc(sysdate)
AND nvl(asus.primary_pay_site_flag,'N') = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT hzl.Address1,
hzl.Address2,
hzl.Address3,
hzl.City,
hzl.Country,
hzl.Postal_Code,
hzl.Province,
hzl.State,
hzl.Address4,
hzl.County,
hzl.Address_Style,
asus.Vendor_Id,
asus.Vendor_Site_Id,
asus.Vendor_site_code,
nvl(asus.Pay_Site_Flag,'N'),
nvl(asus.Primary_Pay_Site_Flag,'N')
INTO l_apvs_rec.Address_Line1,
l_apvs_rec.Address_Line2,
l_apvs_rec.Address_Line3,
l_apvs_rec.City,
l_apvs_rec.Country,
l_apvs_rec.Zip,
l_apvs_rec.Province,
l_apvs_rec.State,
l_apvs_rec.Address_Line4,
l_apvs_rec.County,
l_apvs_rec.Address_Style,
l_apvs_rec.Vendor_Id,
l_apvs_rec.Vendor_Site_Id,
l_apvs_rec.Vendor_site_code,
l_apvs_rec.Pay_Site_Flag,
l_apvs_rec.Primary_Pay_Site
FROM ap_supplier_sites_all asus,
hz_locations hzl
WHERE asus.vendor_site_id = l_inv_rec.vendor_site_id
AND asus.location_id = hzl.location_id
AND nvl(trunc(asus.inactive_date),sysdate+1)
> trunc(sysdate)
AND nvl(asus.pay_site_flag,'N') = 'Y'
AND rownum = 1;
-- We want to find ANY Pay Site for this Vendor
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL; -- Can't have no data found here as there
-- has to be another pay site (verify)
END;
END;
END IF; -- l_apvs_rec.Pay_Site_Flag = 'N'
END IF; -- l_vendor_type_lookup_code
END IF;
EXCEPTION
WHEN NO_DATA_FOUND Then
-- The Vendor Site that was on the invoice does not exist or
-- we cannot pay invoices for this vendor or the vendor has no
-- active pay sites
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_VENDOR_SITE');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
-- At this stage the we are assuming that the Vendor Site is a
-- pay site and is active
-- Verify if Processing Type of Payment Process Profile provided
-- is 'PRINTED', then Payment Document Id needs to be provided
l_debug_info := 'Deriving Proceeing Type for Payment Process Profile';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
SELECT processing_type,
print_instruction_immed_flag,
default_printer
INTO l_processing_type,
l_print_instr_immed_flag,
l_default_printer
FROM IBY_PAYMENT_PROFILES
WHERE payment_profile_id = p_payment_profile_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_IBY_INVALID_PAYMENT_PROF');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
-- Verify payment document is needed or not
IF l_processing_type = 'PRINTED' THEN
IF p_payment_document_id IS NULL THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_CE_PAYMENT_DOC_NEEDED');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END IF;
END IF;
-- Verify whether payment_document_id provided is locked by
-- batch and also valid for the internal bank account that
-- is provided
l_debug_info := 'Check If Payment Document Id is Valid and Get the next check';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
IF p_payment_document_id IS NOT NULL THEN
BEGIN
SELECT payment_document_id
INTO l_payment_document_id
FROM CE_PAYMENT_DOCUMENTS
WHERE payment_document_id = p_payment_document_id
AND internal_bank_account_id = p_internal_bank_acct_id
AND payment_instruction_id IS NULL;
IBY_DISBURSE_UI_API_PUB_PKG.Validate_Paper_Doc_Number
(p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_payment_doc_id => p_payment_document_id,
x_paper_doc_num => l_next_check_number,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
show_warn_msgs_flag => 'T');
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_PAY_DOCUMENT_ALREADY_IN_USE');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
END IF;
/*
|| =======================================================
|| Need to get the party name from hz_parties as it
|| it is needed for the insert into ap_checks
|| =======================================================
*/
l_debug_info := 'Get Supplier Party Name';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
SELECT asup.auto_calculate_interest_flag,
hp.party_name
INTO l_vendor_rec.Auto_calc_int_flag,
l_vendor_rec.Vendor_name
FROM ap_suppliers asup,
hz_parties hp
WHERE asup.vendor_id = l_inv_rec.vendor_id
AND asup.party_id = hp.party_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_PAY_NO_VENDOR');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
/*
|| ==============================================================
|| - Lock Invoices
|| --------------------------------------------------------------
|| Payment workbench actually calls the following code to
|| AP_PAY_IN_FULL_PKG.AP_LOCK_INVOICES
||
|| However all this code does is that it locks all the invoices
|| that are passed in via the p_invoice_id_list parameter.
|| It also will return the payment_method_code for each
|| payment_num on the list p_payment_num_list.
||
|| It will also return the following bits of information needed
|| to insert a row in the table AP_CHECKS_ALL
||
|| Currency_code,
|| Payment_method,
|| Vendor_id, <-- Already have this information
|| Vendor_site_id <-- Alreadt have this information
||
|| For this requirement, we will always have a single invoice
|| and a single payment so all we really need to do is to lock
|| the invoice row
||
|| Any additional bits of information we need, we can select
|| outside in this procedure.
|| ==============================================================
*/
l_debug_info := 'Lock The Invoice';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
AP_INVOICES_PKG.LOCK_ROW(p_invoice_id,
l_curr_calling_sequence);
/*
|| ==============================================================
|| -- - Get the the follwoing for the document :-
|| o Status_Lookup_Code
|| o Payment_Type_Flag
|| --------------------------------------------------------------
*/
-- Derive check_status based on payment method
l_debug_info := 'Is Payment Method Bills Payable enabled ';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
BEGIN
SELECT support_bills_payable_flag,
maturity_date_offset_days
INTO l_bills_payable,
l_maturity_date_offset_days
FROM IBY_PAYMENT_METHODS_VL
WHERE payment_method_code = l_inv_rec.payment_method;
IF l_bills_payable = 'Y' THEN
l_check_rec.status_lookup_code := 'ISSUED';
l_maturity_date := NVL(trunc(p_check_date),trunc(sysdate))
+ NVL(l_maturity_date_offset_days, 0);
ELSE
l_check_rec.status_lookup_code := 'NEGOTIABLE';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_IBY_INVALID_PAYMENT_METHOD');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(
p_count => x_msg_count,
p_data => x_msg_data
);
--
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
l_temp_status := 'E';
END;
-- ---------------------------------------------------------------
-- Note: We will only populate the Checkrun_ID for Automatic
-- Payments i.e. Payments in a Batch for Quick and Manual
-- Payments this value will be Null.
-- ---------------------------------------------------------------
-- ---------------------------------------------------------------
-- Step 1 : Sequential Numbering Logic
-- ---------------------------------------------------------------
FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
--5007576, re-wrote the doc sequencing section below
l_doc_category_code := p_doc_category_code;
if l_seq_num_profile in ('P','A') then
if l_doc_category_code is not null then
begin
SELECT 'row exists'
INTO l_valid_sequence_exists
FROM fnd_doc_sequence_categories
WHERE code = l_doc_category_code
AND table_name IN ('AP_CHECKS','AP_CHECKS_ALL');
exception
when no_data_found then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_NO_DOC_CAT');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
p_data => x_msg_data);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);
l_temp_status := 'E';
return;
end;
else
CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat(p_payment_document_id,
p_payment_method_code,
p_internal_bank_acct_id,
l_doc_category_code);
end if;
if l_doc_category_code = '-1' and l_seq_num_profile = 'A' then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_DOC_CAT_NO_FOUND');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
p_data => x_msg_data);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);
l_temp_status := 'E';
return;
end if;
if l_doc_category_code <> '-1' and
l_asp_rec.pay_doc_override <> 'Y' and
l_doc_category_code <> p_doc_category_code then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_DOC_NO_REQ');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
p_data => x_msg_data);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);
l_temp_status := 'E';
return;
end if;
--at this point if we haven't rejected we need to verify the sequence assignment is valid
if l_doc_category_code <> '-1' then
l_return_code := fnd_seqnum.get_seq_info(
app_id => 200,
cat_code => l_doc_category_code,
sob_id => l_asp_rec.set_of_books_id,
met_code => 'A',
trx_date => nvl(trunc(p_check_date), trunc(sysdate)),
docseq_id => l_docseq_id,
docseq_type => l_docseq_type,
docseq_name => l_docseq_name,
db_seq_name => l_db_seq_name,
seq_ass_id => l_seq_ass_id,
prd_tab_name => l_prd_tab_name,
aud_tab_name => l_aud_tab_name,
msg_flag => l_msg_flag);
--reject if invalid and seq numbering is always used
if (l_return_code <> 0 or l_docseq_type = 'M') and l_seq_num_profile = 'A' then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_CREATE_ERROR');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
p_data => x_msg_data);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);
l_temp_status := 'E';
return;
elsif l_return_code = 0 and l_docseq_type = 'A' then
--get seq value
l_return_code := FND_SEQNUM.GET_SEQ_VAL(
200,
l_doc_category_code,
l_asp_rec.set_of_books_id,
'A',
nvl(trunc(p_check_date), trunc(sysdate)),
l_seqval,
l_dbseqid,
'N',
'N');
if l_return_code <> 0 and l_seq_num_profile = 'A' then
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_CREATE_ERROR');
FND_MSG_PUB.ADD;
FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
p_data => x_msg_data);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',FND_LOG.G_CURRENT_RUNTIME_LEVEL);
l_temp_status := 'E';
return;
end if;
end if;
end if; --l_doc_category_code is not null then
end if; --l_seq_num_profile in ('P','A')
-- We call Get_Single_Payment_Amount to get the amount for the payment
-- net of the available discount. We pass in NULL for the payment num
-- parameter because we want the entire invoice (all pmt scheds)
-- to be paid
l_debug_info := 'Getting the Check Amount';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
l_amount := AP_PAY_IN_FULL_PKG.AP_Get_Check_Amount(
to_char(p_invoice_id),
NULL,
p_payment_type_flag,
sysdate,
l_inv_rec.Pmt_Currency_code,
p_take_discount,
l_asp_rec.Auto_Calc_Int_Flag,
l_vendor_rec.auto_calc_int_flag,
'APAYFULB');
IF ( l_inv_rec.Pmt_Currency_code <> l_asp_rec.base_currency_code ) THEN
IF ( l_check_rec.xrate_type = 'User' ) THEN
l_base_amount := ap_utilities_pkg.ap_round_currency(
(l_amount*l_check_rec.xrate),
l_asp_rec.base_currency_code);
ELSE -- exchange rate is other than USER
l_base_amount := gl_currency_api.convert_amount(
l_inv_rec.pmt_currency_code,
l_asp_rec.base_currency_code,
l_check_rec.xrate_date,
l_check_rec.xrate_type,
l_amount);
END IF;
END IF;
-- Only when we are sure we want to insert should we select the
-- check_id
/*
|| ==============================================================
|| Step ?: Get Check_ID for creaation of record in the table
|| AP_CHECKS
|| --------------------------------------------------------------
*/
-- First select the value for the CHECK_ID before insert
SELECT ap_checks_s.nextval
INTO l_check_rec.check_id
FROM sys.dual;
l_debug_info := 'Get Quick Check Id';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
IF (P_payment_type_flag = 'Q') THEN
SELECT alc1.displayed_field
INTO l_quick_check_id
FROM ap_lookup_codes alc1
WHERE alc1.lookup_type = 'NLS TRANSLATION'
AND alc1.lookup_code = 'QUICKCHECK ID';
l_check_rec.checkrun_name :=
substrb(l_quick_check_id, 1,
30 - nvl(lengthb(to_char(l_check_rec.check_id)), 0))
||to_char(l_check_rec.check_id);
END IF;
/*
|| ==============================================================
|| Step ? - Create Check (record in the table AP_CHECKS
|| --------------------------------------------------------------
*/
l_debug_info := 'Calling Ap_Check_Pkg.Insert_Row';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
if l_next_check_number is null and l_seq_num_profile in ('P') and p_doc_category_code is null then
l_next_check_number := -1;
end if;
AP_CHECKS_PKG.Insert_Row(
X_Rowid => l_dummy_rowid,
X_Amount => l_amount,
X_Ce_Bank_Acct_Use_Id => p_internal_bank_acct_id,
X_Bank_Account_Name => l_check_rec.bank_account_name,
X_Check_Date => nvl(trunc(p_check_date),
trunc(sysdate)),
X_Check_Id => l_check_rec.check_id,
X_Check_Number => l_next_check_number, --Afzal
X_Currency_Code => l_inv_rec.Pmt_Currency_code,
X_Last_Updated_By => FND_GLOBAL.USER_ID,
X_Last_Update_Date => SYSDATE,
X_Payment_Type_Flag => p_payment_type_flag,
X_Address_Line1 => l_apvs_rec.Address_Line1,
X_Address_Line2 => l_apvs_rec.Address_Line2,
X_Address_Line3 => l_apvs_rec.Address_Line3,
X_Checkrun_Name => l_check_rec.Checkrun_Name,
X_Check_Format_Id => NULL,
X_Check_Stock_Id => NULL,
X_City => l_apvs_rec.City,
X_Country => l_apvs_rec.Country,
X_Created_By => FND_GLOBAL.USER_ID,
X_Creation_Date => SYSDATE,
X_Last_Update_Login => FND_GLOBAL.USER_ID,
X_Status_Lookup_Code => l_check_rec.status_lookup_code,
X_Vendor_Name => l_vendor_rec.vendor_name,
X_Vendor_Site_Code => l_apvs_rec.vendor_site_code,
X_External_Bank_Account_Id => l_inv_rec.external_bank_account_id,
X_Zip => l_apvs_rec.Zip,
X_Bank_Account_Num => NULL, -- For Electronic Pmts
X_Bank_Account_Type => NULL, -- For Electronic Pmts
X_Bank_Num => NULL, -- For Electronic Pmts
X_Check_Voucher_Num => NULL,
X_Cleared_Amount => NULL,
X_Cleared_Date => NULL,
X_Doc_Category_Code => l_Doc_Category_Code,
X_Doc_Sequence_Id => l_dbseqid, -- Seq Num
X_Doc_Sequence_Value => l_seqval, -- Seq Num
X_Province => l_apvs_rec.Province, -- PO_VENDORS
X_Released_Date => NULL,
X_Released_By => NULL,
X_State => l_apvs_rec.State,
X_Stopped_Date => NULL,
X_Stopped_By => NULL,
X_Void_Date => NULL,
X_Attribute1 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute_Category => NULL,
X_Future_Pay_Due_Date => l_maturity_date,
X_Treasury_Pay_Date => NULL,
X_Treasury_Pay_Number => NULL,
X_Withholding_Status_Lkup_Code => NULL,
X_Reconciliation_Batch_Id => NULL,
X_Cleared_Base_Amount => NULL,
X_Cleared_Exchange_Rate => NULL,
X_Cleared_Exchange_Date => NULL,
X_Cleared_Exchange_Rate_Type => NULL,
X_Address_Line4 => l_apvs_rec.Address_Line4,
X_County => l_apvs_rec.County,
X_Address_Style => l_apvs_rec.Address_Style,
X_Org_id => l_inv_rec.org_id,
X_Vendor_Id => l_inv_rec.vendor_id,
X_Vendor_Site_Id => l_apvs_rec.Vendor_Site_Id,
X_Exchange_Rate => l_check_rec.xrate,
X_Exchange_Date => l_check_rec.xrate_date,
X_Exchange_Rate_Type => l_check_rec.xrate_type,
X_Base_Amount => l_base_amount,
X_Checkrun_Id => NULL,
X_Calling_Sequence => 'APAYFULB.PLS',
X_Global_Attribute_Category => NULL,
X_Global_Attribute1 => NULL,
X_Global_Attribute2 => NULL,
X_Global_Attribute3 => NULL,
X_Global_Attribute4 => NULL,
X_Global_Attribute5 => NULL,
X_Global_Attribute6 => NULL,
X_Global_Attribute7 => NULL,
X_Global_Attribute8 => NULL,
X_Global_Attribute9 => NULL,
X_Global_Attribute10 => NULL,
X_Global_Attribute11 => NULL,
X_Global_Attribute12 => NULL,
X_Global_Attribute13 => NULL,
X_Global_Attribute14 => NULL,
X_Global_Attribute15 => NULL,
X_Global_Attribute16 => NULL,
X_Global_Attribute17 => NULL,
X_Global_Attribute18 => NULL,
X_Global_Attribute19 => NULL,
X_Global_Attribute20 => NULL,
X_transfer_priority => NULL,
X_maturity_exchange_rate_type => NULL,
X_maturity_exchange_date => NULL,
X_maturity_exchange_rate => NULL,
X_description => NULL,
X_anticipated_value_date => NULL,
X_actual_value_date => NULL,
X_payment_profile_id => p_payment_profile_id,
X_bank_charge_bearer => NULL,
X_settlement_priority => NULL,
X_payment_method_code => l_inv_rec.payment_method,
X_payment_document_id => p_payment_document_id,
X_party_id => l_inv_rec.party_id,
X_party_site_id => l_inv_rec.party_site_id,
X_legal_entity_id => l_check_rec.legal_entity_id,
X_payment_id => NULL);
l_debug_info := 'Calling Ap_Reconciliation_Pkg.Insert_Payment_History';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
IF ( p_payment_type_flag = 'R' ) THEN
l_transaction_type := 'REFUND RECORDED';
ELSE
l_transaction_type := 'PAYMENT CREATED';
END IF;
l_debug_info := 'transaction type = ' || l_transaction_type;
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
-- Bug3343314
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => l_check_rec.check_id,
x_transaction_type => l_transaction_type,
x_accounting_date => nvl(trunc(p_check_date),
trunc(sysdate)),
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => l_amount,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_inv_rec.pmt_currency_code,
x_pmt_to_base_xrate_type => l_check_rec.xrate_type,
x_pmt_to_base_xrate_date => l_check_rec.xrate_date,
x_pmt_to_base_xrate => l_check_rec.xrate,
x_trx_base_amount => l_base_amount,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => l_inv_rec.org_id,
x_creation_date => SYSDATE,
x_created_by => FND_GLOBAL.User_Id,
x_last_update_date => SYSDATE,
x_last_updated_by => FND_GLOBAL.User_Id,
x_last_update_login => FND_GLOBAL.User_Id,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id
);
-- Any witholding distributions or Interest Invoices are created
-- in the call to Create_Single_Payments.
l_debug_info := 'Calling Ap_Pay_In_Full_Pkg.Create_Payments';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
AP_PAY_IN_FULL_PKG.AP_Create_Payments(
to_char(p_invoice_id),
NULL,
l_check_rec.check_id,
p_payment_type_flag,
l_inv_rec.Payment_Method,
p_internal_bank_acct_id,
null, -- bank_account_num
null, -- bank_account_type,
null, -- bank_num,
nvl(trunc(p_check_date), trunc(sysdate)),
l_period_name,
l_inv_rec.Pmt_currency_code,
l_asp_rec.base_currency_code,
l_check_rec.Checkrun_Name,
l_seqval,
l_dbseqid,
l_check_rec.xrate,
l_check_rec.xrate_type,
l_check_rec.xrate_date,
p_take_discount, -- Pay In Full Take Discount
l_asp_rec.auto_calc_int_flag,
l_vendor_rec.auto_calc_int_flag,
l_asp_rec.set_of_books_id,
null, -- future_pay_code_combination_id
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
'APXPAWKB',
l_seq_num_profile,
l_accounting_Event_id,
l_inv_rec.org_id);
l_debug_info := 'Calling IBY Single Payment API';
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, l_debug_info,
-- G_LEVEL_PROCEDURE);
IF l_temp_status <> 'E' THEN
IF p_payment_type_flag IN ('Q', 'M') THEN
IF p_payment_type_flag = 'Q' THEN
l_manual_payment_flag := 'N';
--bug 5982788
select checkrun_name into l_check_rec.Checkrun_name
from ap_checks_all
where check_id = l_check_rec.check_id ;
ELSIF p_payment_type_flag = 'M' THEN
l_manual_payment_flag := 'Y';
--bug 5982788
update ap_checks_all
set checkrun_name = l_check_rec.check_id
where check_id = l_check_rec.check_id ;
select checkrun_name into l_check_rec.Checkrun_name
from ap_checks_all
where check_id = l_check_rec.check_id ;
END IF;
--Bug 5982788
IBY_DISBURSE_SINGLE_PMT_PKG.submit_single_payment(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_calling_app_id => 200,
p_calling_app_payreq_cd => l_check_rec.Checkrun_Name,
p_is_manual_payment_flag => l_manual_payment_flag,
p_payment_function => l_inv_rec.payment_function,
p_internal_bank_account_id => p_internal_bank_acct_id,
p_pay_process_profile_id => p_payment_profile_id,
p_payment_method_cd => l_inv_rec.payment_method,
p_legal_entity_id => l_check_rec.legal_entity_id,
p_organization_id => l_inv_rec.org_id,
p_organization_type => 'OPERATING_UNIT',
p_payment_date => nvl(trunc(p_check_date),
trunc(sysdate)),
p_payment_amount => l_amount,
p_payment_currency => l_inv_rec.pmt_currency_code,
p_payee_party_id => l_inv_rec.party_id,
p_payee_party_site_id => l_inv_rec.party_site_id,
p_supplier_site_id => l_inv_rec.vendor_site_id,
p_payee_bank_account_id => l_inv_rec.external_bank_account_id,
p_override_pmt_complete_pt => 'Y',
p_bill_payable_flag => NVL(l_bills_payable, 'N'),
p_anticipated_value_date => NULL,
p_maturity_date => l_maturity_date,
p_payment_document_id => p_payment_document_id,
p_paper_document_number => l_next_check_number,
p_printer_name => l_default_printer,
p_print_immediate_flag => l_print_instr_immed_flag,
p_transmit_immediate_flag => Null,
p_payee_address_line1 => l_apvs_rec.address_line1,
p_payee_address_line2 => l_apvs_rec.address_line2,
p_payee_address_line3 => l_apvs_rec.address_line3,
p_payee_address_line4 => l_apvs_rec.address_line4,
p_payee_address_city => l_apvs_rec.city,
p_payee_address_county => l_apvs_rec.county,
p_payee_address_state => l_apvs_rec.state,
p_payee_address_zip => l_apvs_rec.zip,
p_payee_address_country => l_apvs_rec.country,
p_attribute_category => Null,
p_attribute1 => Null,
p_attribute2 => Null,
p_attribute3 => Null,
p_attribute4 => Null,
p_attribute5 => Null,
p_attribute6 => Null,
p_attribute7 => Null,
p_attribute8 => Null,
p_attribute9 => Null,
p_attribute10 => Null,
p_attribute11 => Null,
p_attribute12 => Null,
p_attribute13 => Null,
p_attribute14 => Null,
p_attribute15 => Null,
x_num_printed_docs => l_num_printed_docs,
x_payment_id => l_payment_id,
x_paper_doc_num => l_paper_doc_num,
x_pmt_ref_num => l_pmt_ref_num,
x_return_status => l_return_status,
x_error_ids_tab => x_errorIds,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
UPDATE AP_CHECKS_ALL
SET payment_id = l_payment_id
WHERE check_id = l_check_rec.check_id;
IF p_payment_type_flag <> 'M' THEN
IF l_paper_doc_num IS NOT NULL THEN
l_check_number := l_paper_doc_num;
ELSE
l_check_number := l_pmt_ref_num;
END IF;
-- if l_next_check_number is null and l_seq_num_profile in ('P') and p_doc_category_code is null then
-- l_next_check_number := -1;
--end if;
IF l_next_check_number <> l_check_number THEN
UPDATE AP_CHECKS_ALL
SET check_number = l_check_number
WHERE check_id = l_check_rec.check_id;
END IF;
END IF;
x_return_status := l_return_status;
ELSE
x_return_status := l_return_status;
END IF;
END IF; -- p_payment_type_flag
ELSE
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
END IF; -- l_temp_status
x_check_id := l_check_rec.check_id;
DBMS_OUTPUT.PUT_LINE('x_return_status-'||x_return_status||'-l_check_id-'||l_check_rec.check_id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION-'||SQLERRM);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'Exception occured when '
-- || 'Payable single payment API fialed. Single payment creation will '
-- || 'be aborted and no records will be committed for '
-- || 'the invoice_id: '
-- || p_invoice_id, G_LEVEL_PROCEDURE
-- );
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE,
-- G_LEVEL_PROCEDURE);
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM,
-- G_LEVEL_PROCEDURE);
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
-- XXAP_PAY_SINGLE_INVOICE_PKG.print_debuginfo(l_module_name, 'EXIT',
-- G_LEVEL_PROCEDURE);
RETURN;
END ;
--END;
---running anonymous block
declare
l_return_status varchar2(2000);
l_msg_data varchar2(2000);
l_check_id number;
begin
XX_AP_INVOICE(
10473427,
11360,
'XX_CHECK',
166,
sysdate,
l_return_status,
l_msg_data,
l_check_id
);
dbms_output.put_line('l_return_status-'||l_return_status||'-l_msg_data-'||l_msg_data||'-l_check_id-'||l_check_id);
exception when others then
dbms_output.put_line(sqlerrm);
end;
SELECT * FROM AP_CHECKS
WHERE CHECK_ID = 8173679;
