AP請求書インポート(請求書ヘッダー行)
9276 ワード
1.請求書ヘッダーをap_に挿入invoices_インターフェイス
2.請求書明細をap_に挿入invoice_lines_インターフェイス
3.請求書インポート要求APXIIMPTの実行
具体例:
1.インタフェーステーブルへのデータの挿入
2.請求書インポート要求の実行
2.請求書明細をap_に挿入invoice_lines_インターフェイス
3.請求書インポート要求APXIIMPTの実行
具体例:
1.インタフェーステーブルへのデータの挿入
DECLARE
l_iface_rec ap_invoices_interface%ROWTYPE;
l_iface_lines_rec ap_invoice_lines_interface%ROWTYPE;
TYPE l_invoice_tbl_type IS TABLE OF cur_invoice%ROWTYPE INDEX BY BINARY_INTEGER;
l_invoice_tbl l_invoice_tbl_type;
CURSOR cur_invoice IS
--1. ： ：
SELECT temp.record_flag,
temp.org_id,
temp.req_number,
temp.req_header_id,
temp.vendor_id,
temp.vendor_site_id,
temp.req_coa_reg_code,
temp.group_type_code,
temp.doc_type_id,
temp.req_apply_date,
temp.req_emp_dept_code,
temp.req_employee_id,
temp.ebs_type_key,
temp.doc_type_name,
temp.src_system_id,
temp.short_code,
temp.src_system_name,
temp.ap_terms_id,
temp.terms_start_date,
temp.currency_code,
temp.comments,
temp.payment_method_code,
temp.pay_group_code,
temp.payment_flag,
temp.accts_pay_code_combination_id,
temp.invoice_amount
FROM (** ** n ** **) temp
ORDER BY temp.req_employee_id ASC;
BEGIN
OPEN cur_invoice;
LOOP
FETCH cur_invoice BULK COLLECT
INTO l_invoice_tbl LIMIT 5000;
EXIT WHEN l_invoice_tbl.count = 0;
FOR k IN l_invoice_tbl.first .. l_invoice_tbl.last
LOOP
--
l_iface_rec.org_id := l_invoice_tbl(k).org_id;
l_iface_rec.invoice_type_lookup_code := l_invoice_type_lookup_code; -- (CREDIT/STANDARD/PREPAYMENT/MIXED)
l_iface_rec.invoice_date := l_invoice_tbl(k)
.req_apply_date;
l_iface_rec.gl_date := to_date(to_char(l_invoice_tbl(k)
.req_apply_date,
'YYYY-MM-DD'), --GL
'YYYY-MM-DD');
l_iface_rec.terms_id := l_invoice_tbl(k).ap_terms_id; --
l_iface_rec.terms_date := l_invoice_tbl(k)
.terms_start_date; --
l_iface_rec.payment_method_lookup_code := l_invoice_tbl(k)
.payment_method_code; --
l_iface_rec.vendor_id := l_invoice_tbl(k).vendor_id;
l_iface_rec.vendor_site_id := l_invoice_tbl(k)
.vendor_site_id;
l_iface_rec.invoice_amount := round(l_invoice_tbl(k)
.invoice_amount,
2);
l_iface_rec.invoice_currency_code := l_invoice_tbl(k)
.currency_code;
l_iface_rec.control_amount := NULL;
l_iface_rec.exchange_rate_type := NULL;
l_iface_rec.exchange_date := NULL;
l_iface_rec.exchange_rate := NULL;
l_iface_rec.source := 'FINREQ'; -- ( )
l_iface_rec.group_id := l_group_id; -- ( )
l_iface_rec.pay_group_lookup_code := l_invoice_tbl(k)
.pay_group_code;
l_iface_rec.attribute_category := 'JD_AP_Settlement_Header'; --
l_iface_rec.attribute1 := l_invoice_tbl(k).req_number;
l_iface_rec.attribute2 := l_invoice_tbl(k)
.src_system_name;
l_iface_rec.attribute3 := l_invoice_tbl(k)
.doc_type_name;
l_iface_rec.attribute4 := l_invoice_tbl(k)
.req_coa_reg_code;
l_iface_rec.attribute5 := l_invoice_tbl(k)
.group_type_code;
l_iface_rec.attribute6 := get_empolyee_name(l_invoice_tbl(k)
.req_employee_id);
l_iface_rec.attribute7 := get_person_dept_code(l_invoice_tbl(k)
.req_employee_id);
l_iface_rec.attribute8 := l_attribute8;
l_iface_rec.accts_pay_code_combination_id := l_invoice_tbl(k)
.accts_pay_code_combination_id; --
l_iface_rec.description := substrb(l_invoice_tbl(k)
.comments,
1,
240);
l_iface_rec.last_update_date := SYSDATE;
l_iface_rec.last_updated_by := g_user_id;
l_iface_rec.creation_date := SYSDATE;
l_iface_rec.created_by := g_user_id;
l_iface_rec.last_update_login := fnd_global.login_id;
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec;
--
l_iface_lines_rec.org_id := l_iface_rec.org_id;
l_iface_lines_rec.invoice_id := l_iface_rec.invoice_id;
l_iface_lines_rec.accounting_date := SYSDATE;
l_iface_lines_rec.line_number := n;
l_iface_lines_rec.line_type_lookup_code := 'ITEM';
l_iface_lines_rec.amount := round(line_record.line_amount -
line_record.tax_amount,
2);
l_iface_lines_rec.tax_code := '0%_VAT'; -- ，
l_iface_lines_rec.dist_code_combination_id := l_ccid; --
l_iface_lines_rec.description := line_record.purchase_reason || ',' ||
line_record.comments;
l_iface_lines_rec.quantity_invoiced := line_record.quantity;
l_iface_lines_rec.last_update_date := SYSDATE;
l_iface_lines_rec.last_updated_by := l_user_id;
l_iface_lines_rec.creation_date := SYSDATE;
l_iface_lines_rec.created_by := l_user_id;
l_iface_lines_rec.last_update_login := l_login_id;
l_iface_lines_rec.attribute_category := 'JD_AP_Settlement_Line';
l_iface_lines_rec.attribute1 := line_record.cash_flow_code;
l_iface_lines_rec.attribute2 := line_record.line_type_name;
l_iface_lines_rec.attribute3 := line_record.line_number;
l_iface_lines_rec.attribute4 := line_record.project_code;
l_iface_lines_rec.attribute5 := cux_common_utl.get_lookup_meaning(line_record.process_type_code,
'CUX_FIN_LINE_PROCESS_TYPE');
l_iface_lines_rec.attribute6 := line_record.cost_flag;
INSERT INTO ap_invoice_lines_interface VALUES l_iface_lines_rec_tax;
END LOOP;
END LOOP;
CLOSE cur_invoice;
END;
2.請求書インポート要求の実行
p_req_id := fnd_request.submit_request( 'SQLAP', --
'APXIIMPT', --
'', -- ( )
'', --start time,
FALSE, --sub request
NULL, --Operating Unit
'FINREQ', --' ', --Source （ Source group_id）
record_array(j).group_id, --Group
record_array(j).batch_name, --Batch Name
NULL, --Hold Name
NULL, --Hold Reason
NULL, --GL
'N', --Purge
'N', --Trace Switch Trace，
'N', --Debug Switch ，
'N', --Summarize Report
NULL, --Commit Batch Size
l_user_id, --User id
l_login_id, --Login id
chr(0)); 'N', --Purge