[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_debug_info := 'Select the Invoice if it can be paid ';
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 := 'Select System Option based on Invoice Org Id ';
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;
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_account_id = p_internal_bank_acct_id; /* Added for bug#12971160 */
SELECT count(*)
INTO l_num_invs_sel_for_pmt
FROM ap_selected_invoices_all
WHERE invoice_id = P_invoice_id;
|| 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';
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;
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);
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';
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;
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;
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;
|| it is needed for the insert into ap_checks
|| =======================================================
*/
l_debug_info := 'Get Supplier Party Name';
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;
|| 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';
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;
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');
SELECT ap_checks_s.nextval
INTO l_check_rec.check_id
FROM sys.dual;
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_debug_info := 'Calling Ap_Check_Pkg.Insert_Row';
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,
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';
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
);
select checkrun_name into l_check_rec.Checkrun_name
from ap_checks_all
where check_id = l_check_rec.check_id ;
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 ;
UPDATE AP_CHECKS_ALL
SET payment_id = l_payment_id
WHERE check_id = l_check_rec.check_id;
UPDATE AP_CHECKS_ALL
SET check_number = l_check_number
WHERE check_id = l_check_rec.check_id;