EBS系统中各模块的API较多，一次性无法全部整理出来，后续我会逐步的整理和补充相关接口内容。稍安勿躁~
这个博客中暂时只介绍AP应付模块中的相关API及其使用。其他模块的部分API可以查看另外一个博客的内容：
《 EBS系统各模块API及接口 》；但是里面没有具体的使用方法哦~
AP 应付模块的相关接口详解
1、创建AP发票批
ap_batches_pkg.insert_row(x_rowid => x_rowid,
x_batch_id => l_batch_id,
x_batch_name => r.batch_name,
x_batch_date => trunc(SYSDATE),
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_control_invoice_count => NULL, --r.control_count,
x_control_invoice_total => NULL, --:x_control_invoice_total,
x_invoice_currency_code => NULL, -- :x_invoice_currency_code,
x_payment_currency_code => NULL, --:x_payment_currency_code,
x_last_update_login => fnd_global.login_id,
x_creation_date => (SYSDATE),
x_created_by => fnd_global.user_id,
x_pay_group_lookup_code => pay_group, --支付组
x_payment_priority => NULL, --:x_payment_priority,
x_batch_code_combination_id => NULL, --:x_batch_code_combination_id,
x_terms_id => NULL, --:x_terms_id,
x_attribute1 => NULL, --:x_attribute1,
x_attribute2 => NULL, --:x_attribute2,
x_attribute3 => NULL, --:x_attribute3,
x_attribute4 => NULL, --:x_attribute4,
x_attribute_category => NULL, --:x_attribute_category,
x_attribute5 => NULL, --:x_attribute5,
x_attribute6 => NULL, --:x_attribute6,
x_attribute7 => NULL, --:x_attribute7,
x_attribute8 => NULL, --:x_attribute8,
x_attribute9 => NULL, --:x_attribute9,
x_attribute10 => NULL, --:x_attribute10,
x_attribute11 => NULL, --:x_attribute11,
x_attribute12 => NULL, --:x_attribute12,
x_attribute13 => NULL, --:x_attribute13,
x_attribute14 => NULL, --:x_attribute14,
x_attribute15 => NULL, --:x_attribute15,
x_invoice_type_lookup_code => invoice_type_lookup_code,--发票类型
x_hold_lookup_code => NULL, --:x_hold_lookup_code,
x_hold_reason => NULL, --:x_hold_reason,
x_doc_category_code => NULL, --:x_doc_category_code,
x_org_id => r.org_id,
x_calling_sequence => NULL, --:x_calling_sequence,
x_gl_date => NULL --:x_gl_date
);
2、导入AP发票
2.1 插入数据
插入发票头：
-- 发票头信息
declare
l_inv_rec ap_invoices_interface%ROWTYPE;
begin
l_inv_rec.invoice_id := ap_invoices_interface_s.nextval;
l_inv_rec.org_id := l_data_rec.org_id;
l_inv_rec.po_number := l_data_rec.po_number;
l_inv_rec.vendor_id := l_data_rec.vendor_id;
l_inv_rec.party_id := l_data_rec.party_id;
l_inv_rec.vendor_site_id := l_data_rec.vendor_site_id;
l_inv_rec.invoice_date := l_data_rec.invoice_date_d;
l_inv_rec.pay_group_lookup_code := l_data_rec.pay_group;
l_inv_rec.invoice_num := l_data_rec.invoice_num;
l_inv_rec.invoice_type_lookup_code := l_data_rec.invoice_type_code;
l_inv_rec.description := l_data_rec.invoice_desc;
l_inv_rec.invoice_currency_code := l_data_rec.invoice_currency_code;
l_inv_rec.exchange_rate_type := l_data_rec.exchange_type_code;
l_inv_rec.exchange_rate := l_data_rec.exchange_rate_n;
l_inv_rec.exchange_date := l_data_rec.exchange_date_d;
l_inv_rec.invoice_amount := l_data_rec.invoice_amount_n;
l_inv_rec.gl_date := l_data_rec.invoice_gl_date_d;
l_inv_rec.terms_id := l_data_rec.terms_id;
l_inv_rec.payment_method_code := l_data_rec.payment_method_code;
l_inv_rec.terms_date := l_data_rec.terms_date_d;
l_inv_rec.accts_pay_code_combination_id := l_data_rec.accts_pay_ccid;
l_inv_rec.payment_currency_code := l_data_rec.invoice_currency_code;
l_inv_rec.source := 'CUX_AP_INVOICE_IMPORT'; ----
l_inv_rec.group_id := ap_interface_groups_s.nextval;
l_inv_rec.last_update_date := SYSDATE;
l_inv_rec.last_updated_by := fnd_global.user_id;
l_inv_rec.creation_date := SYSDATE;
l_inv_rec.created_by := fnd_global.user_id;
l_inv_rec.last_update_login := fnd_global.login_id;
INSERT INTO ap_invoices_interface VALUES l_inv_rec;
end;
插入发票行：
-- 发票行信息
declare
l_line_number NUMBER;
l_inv_line_rec ap_invoice_lines_interface%ROWTYPE;
l_tax_line_rec ap_invoice_lines_interface%ROWTYPE;
begin
l_line_number := 0;
for l in (SELECT t.* FROM cux_ap_invoice_import_temp t
where 1=1
and ...) loop
l_line_number := l_line_number + 1;
l_inv_line_rec.invoice_id := l_inv_rec.invoice_id;
l_inv_line_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;
l_inv_line_rec.org_id := l.org_id;
l_inv_line_rec.line_type_lookup_code := 'ITEM';
l_inv_line_rec.line_number := l_line_number;
l_inv_line_rec.description := l.line_desc;
l_inv_line_rec.amount := l.line_amount_n;
l_inv_line_rec.accounting_date := l.line_accounting_date_d;
l_inv_line_rec.dist_code_combination_id := l.dist_gcc_ccid;
l_inv_line_rec.inventory_item_id := NULL;
l_inv_line_rec.last_update_date := SYSDATE;
l_inv_line_rec.last_updated_by := fnd_global.user_id;
l_inv_line_rec.creation_date := SYSDATE;
l_inv_line_rec.created_by := fnd_global.user_id;
l_inv_line_rec.last_update_login := fnd_global.login_id;
l_inv_line_rec.tax_classification_code := l.tax_classification_code;
l_inv_line_rec.line_group_number := l_inv_line_rec.invoice_line_id; --
l_line_group_number := l_inv_line_rec.line_group_number;
INSERT INTO ap_invoice_lines_interface VALUES l_inv_line_rec;
----tax
IF l.tax_amount_n IS NOT NULL
AND l.tax_regime_code IS NOT NULL
AND l.tax IS NOT NULL
AND l.tax_jurisdiction_code IS NOT NULL
AND l.tax_status_code IS NOT NULL
AND l.tax_status_code IS NOT NULL THEN
l_line_number := l_line_number + 1;
l_tax_line_rec.invoice_id := l_inv_rec.invoice_id;
l_tax_line_rec.invoice_line_id := ap_invoice_lines_interface_s.nextval;
l_tax_line_rec.org_id := l.org_id;
l_tax_line_rec.line_type_lookup_code := 'TAX';
l_tax_line_rec.line_number := l_line_number;
l_tax_line_rec.description := NULL;
l_tax_line_rec.amount := l.tax_amount_n;
l_tax_line_rec.dist_code_combination_id := NULL;
l_tax_line_rec.last_update_date := SYSDATE;
l_tax_line_rec.last_updated_by := fnd_global.user_id;
l_tax_line_rec.creation_date := SYSDATE;
l_tax_line_rec.created_by := fnd_global.user_id;
l_tax_line_rec.last_update_login := fnd_global.login_id;
l_tax_line_rec.line_group_number := l_line_group_number; --
l_tax_line_rec.tax_regime_code := l.tax_regime_code;
l_tax_line_rec.tax := l.tax;
l_tax_line_rec.tax_jurisdiction_code := l.tax_jurisdiction_code;
l_tax_line_rec.tax_status_code := l.tax_status_code;
l_tax_line_rec.tax_rate_code := l.tax_rate_code;
INSERT INTO ap_invoice_lines_interface VALUES l_tax_line_rec;
END IF;
END LOOP;
end;
2.2 API 导入
BEGIN
ap_import_invoices_pkg.import_invoices(p_batch_name => b.batch_name,
p_gl_date => NULL,
p_hold_code => NULL,
p_hold_reason => NULL,
p_commit_cycles => NULL,
p_source => 'CUX_AP_INVOICE_IMPORT',
p_group_id => l_inv_rec.group_id,
p_conc_request_id => NULL,
p_debug_switch => NULL,
p_org_id => l_inv_rec.org_id,
p_batch_error_flag => l_batch_error_flag,
p_invoices_fetched => l_invoices_fetched,
p_invoices_created => l_invoices_created,
p_total_invoice_amount => l_total_invoice_amount,
p_print_batch => l_print_batch,
p_calling_sequence => NULL,
p_invoice_interface_id => l_inv_rec.invoice_id,
p_needs_invoice_approval => 'N',
p_commit => 'N');
END;
3、AP发票审核 APPROVED
BEGIN
ap_approval_pkg.approve(p_run_option => NULL,
p_invoice_batch_id => NULL,
p_begin_invoice_date => NULL,
p_end_invoice_date => NULL,
p_vendor_id => NULL,
p_pay_group => NULL,
p_invoice_id => l_invoice_id,
p_entered_by => NULL,
p_set_of_books_id => NULL,
p_trace_option => NULL,
p_conc_flag => NULL,
p_holds_count => l_holds_count,
p_approval_status => l_approval_status,
p_funds_return_code => l_funds_return_code,
p_calling_mode => 'APPROVE',
p_calling_sequence => NULL,
p_debug_switch => 'N',
p_budget_control => 'Y',
p_commit => 'Y');
END;
4、AP创建付款头
DECLARE
lr_check_rec ap.ap_checks_all%ROWTYPE;
lv_rowid VARCHAR2(30);
l_payment_id NUMBER;
BEGIN
SELECT iby_payments_all_s.nextval INTO l_payment_id FROM dual;
ap_ac_table_handler_pkg.insert_row(p_rowid => lv_rowid,
p_amount => rec_h.pay_amount,
p_ce_bank_acct_use_id => lr_check_rec.ce_bank_acct_use_id,
p_bank_account_name => lr_check_rec.bank_account_name,
p_check_date => lr_check_rec.check_date,
p_check_id => lr_check_rec.check_id,
p_check_number => lr_check_rec.check_number,
p_currency_code => lr_check_rec.currency_code,
p_last_updated_by => lr_check_rec.last_updated_by,
p_last_update_date => lr_check_rec.last_update_date,
p_payment_type_flag => lr_check_rec.payment_type_flag,
p_address_line1 => lr_check_rec.address_line1,
p_address_line2 => NULL,
p_address_line3 => NULL,
p_city => lr_check_rec.city,
p_country => lr_check_rec.country,
p_created_by => lr_check_rec.created_by,
p_creation_date => lr_check_rec.creation_date,
p_last_update_login => lr_check_rec.last_update_login,
p_status_lookup_code => 'NEGOTIABLE',
p_vendor_name => lr_check_rec.vendor_name,
p_vendor_site_code => lr_check_rec.vendor_site_code,
p_external_bank_account_id => lr_check_rec.external_bank_account_id,
p_zip => lr_check_rec.zip,
p_bank_account_num => lr_check_rec.bank_account_num,
p_bank_num => lr_check_rec.bank_num,
p_check_voucher_num => lr_check_rec.check_voucher_num,
p_province => lr_check_rec.province,
p_county => lr_check_rec.county,
p_address_style => lr_check_rec.address_style,
p_org_id => p_org_id,
p_vendor_id => lr_check_rec.vendor_id,
p_vendor_site_id => lr_check_rec.vendor_site_id,
p_exchange_rate => lr_check_rec.exchange_rate,
p_exchange_date => lr_check_rec.exchange_date,
p_exchange_rate_type => lr_check_rec.exchange_rate_type,
p_base_amount => lr_check_rec.base_amount,
p_actual_value_date => lr_check_rec.actual_value_date,
p_payment_method_code => lr_check_rec.payment_method_code,
p_payment_profile_id => lr_check_rec.payment_profile_id,
p_payment_document_id => lr_check_rec.payment_document_id,
p_party_id => lr_check_rec.party_id,
p_party_site_id => lr_check_rec.party_site_id,
p_legal_entity_id => lr_check_rec.legal_entity_id,
p_payment_id => l_payment_id,
p_calling_sequence => g_pkg_name);
END;
5、AP创建付款会计事件
DECLARE
lr_check_rec ap.ap_checks_all%ROWTYPE;
l_accounting_event_id NUMBER;
BEGIN
ap_accounting_events_pkg.create_events(p_event_type => 'PAYMENT',
p_doc_type => 'CHECK',
p_doc_id => lr_check_rec.check_id,
p_accounting_date => lr_check_rec.check_date,
p_accounting_event_id => l_accounting_event_id, --out
p_checkrun_name => lr_check_rec.checkrun_name,
p_calling_sequence => g_pkg_name);
END;
6、AP创建付款历史记录
DECLARE
lr_check_rec ap.ap_checks_all%ROWTYPE;
l_accounting_event_id NUMBER;
BEGIN
ap_reconciliation_pkg.insert_payment_history(x_check_id => lr_check_rec.check_id,
x_transaction_type => 'PAYMENT CREATED',
x_accounting_date => lr_check_rec.check_date,
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 => rec_h.pay_amount, --lr_check_rec.amount
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => lr_check_rec.currency_code,
x_pmt_to_base_xrate_type => lr_check_rec.exchange_rate_type,
x_pmt_to_base_xrate_date => lr_check_rec.exchange_date,
x_pmt_to_base_xrate => lr_check_rec.exchange_rate,
x_trx_base_amount => lr_check_rec.base_amount,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_creation_date => lr_check_rec.creation_date,
x_created_by => lr_check_rec.created_by,
x_last_update_date => lr_check_rec.last_update_date,
x_last_updated_by => lr_check_rec.last_updated_by,
x_last_update_login => lr_check_rec.last_update_login,
x_program_update_date => SYSDATE,
x_program_application_id => fnd_global.resp_appl_id,
x_program_id => fnd_global.conc_program_id,
x_request_id => NULL,
x_calling_sequence => 'APXPAWKB (pay_sum_folder_pkg_i.insert_row)',
x_accounting_event_id => l_accounting_event_id,
x_org_id => p_org_id,
x_invoice_adjustment_event_id => NULL);
END;
7、AP创建付款与发票关系
DECLARE
lr_check_rec ap.ap_checks_all%ROWTYPE;
l_accts_pay_ccid NUMBER;
l_payment_num NUMBER;
BEGIN
FOR lr_line IN (SELECT *
FROM cux.cux_ap_invoice ivh
WHERE ivh.session_id = p_session_id
AND ivh.vendor_num = p_vendor_num) LOOP
BEGIN
SELECT aps.payment_num
INTO l_payment_num
FROM cux_ap_payment_v abp, ap_payment_schedules_all aps
WHERE abp.invoice_id = aps.invoice_id
AND abp.invoice_id = lr_line.invoice_id
AND abp.due_date = aps.due_date;
END;
BEGIN
SELECT pv.accts_pay_code_combination_id
INTO l_accts_pay_ccid
FROM apps.po_vendors pv
WHERE pv.segment1 = rec_h.vendor_num;
END;
ap_pay_invoice_pkg.ap_pay_invoice(p_invoice_id => lr_line.invoice_id,
p_check_id => lr_check_rec.check_id,
p_payment_num => l_payment_num,
p_invoice_payment_id => ap_invoice_payments_s.nextval,
p_old_invoice_payment_id => NULL,
p_period_name => lr_line.period_name,
p_accounting_date => lr_line.accounting_date,
p_amount => lr_line.amount,
p_discount_taken => 0,
p_accrual_posted_flag => 'N',
p_cash_posted_flag => 'N',
p_posted_flag => 'N',
p_set_of_books_id => lr_line.set_of_books_id,
p_last_updated_by => g_user_id,
p_last_update_login => g_login_id,
p_currency_code => lr_line.currency_code,
p_base_currency_code => lr_line.base_currency_code,
p_exchange_rate => lr_line.exchange_rate,
p_exchange_rate_type => lr_line.exchange_rate_type,
p_exchange_date => lr_line.exchange_date,
p_ce_bank_acct_use_id => lr_line.ce_bank_acct_use_id,
p_bank_account_num => lr_line.vendor_bank_acct_num,
p_bank_account_type => NULL,
p_bank_num => NULL,
p_future_pay_posted_flag => 'N',
p_exclusive_payment_flag => 'N',
p_accts_pay_ccid => l_accts_pay_ccid,
p_payment_dists_flag => 'N',
p_payment_mode => 'PAY',
p_replace_flag => 'N',
p_calling_sequence => 'Pay Invoice Forms <- Pre_inser trigger',
p_accounting_event_id => l_accounting_event_id,
p_org_id => p_org_id);
END LOOP;
END;
8、AP预付款核销
DECLARE
l_result BOOLEAN;
l_calling_sequence VARCHAR2(30) := 'Apply Prepayment Form';
l_error_message VARCHAR2(4000);
BEGIN
l_result := ap_prepay_pkg.apply_prepay_fr_prepay(p_invoice_id => invoice_id,
p_prepay_num => prepay_invoice_num,
p_vendor_id => vendor_id,
p_prepay_apply_amount => apply_amount,
p_prepay_gl_date => apply_date,
p_prepay_period_name => period_name,
p_user_id => g_user_id,
p_last_update_login => g_login_id,
p_calling_sequence => l_calling_sequence,
p_error_message => l_error_message);
END;
9、获取下一个AP支付单据编号
DECLARE
l_init_msg_list VARCHAR2(10) := 'F';
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_payment_doc_id NUMBER;
l_check_number NUMBER;
BEGIN
--2.获取对应付款单据
SELECT pd.payment_document_id
INTO l_payment_doc_id
FROM ce_payment_documents pd
WHERE pd.internal_bank_account_id = 30020; -- ce_bank_accounts.bank_account_id
--3.获取/验证单据编号
iby_disburse_ui_api_pub_pkg.validate_paper_doc_number(p_api_version => 1.0
,p_init_msg_list => l_init_msg_list
,p_payment_doc_id => l_payment_doc_id
, --付款单据ID
x_paper_doc_num => l_check_number
, --单据编号
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,show_warn_msgs_flag => 'T');
dbms_output.put_line(l_check_number);
END;
+++==========================================================================+++
以上内容仅为个人学习记录使用。
如有转载，请附有原文出处链接。
浙公网安备 33010602011771号