PROCEDURE process_cux_to_ap(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_CUX_TO_AP';
x_party_id NUMBER;
x_profile_id NUMBER;
l_order_number VARCHAR2(240);
CURSOR cur_ch IS
SELECT ch.header_id,
ch.org_id,
ch.invoice_type,
ch.prepayment_type_code,
ch.vendor_id,
ch.vendor_site_id,
ch.invoice_date,
ch.gl_date,
ch.invoice_number,
ch.currency_code,
ch.invoice_amount,
ch.exchange_rate,
ch.exchange_rate_type,
ch.exchange_date,
ch.payment_currency_code,
ch.payment_rate_type,
ch.payment_rate,
ch.payment_rate_date,
ch.payment_method_lookup_code,
ch.pay_schedule_date,
ch.terms_id,
ch.pay_group_lookup_code,
ch.exp_report_id,
ch.apply_employee_name,
ch.invoice_id,
ch.description,
ch.source_code,
ch.source_line_id,
ch.source_reference,
ch.process_group_id,
ch.process_status,
ch.process_date,
ch.process_message,
ch.object_version_number,
ch.creation_date,
ch.created_by,
ch.last_updated_by,
ch.last_update_date,
ch.last_update_login,
ch.attribute_category,
ch.attribute1,
ch.attribute2,
ch.attribute3,
ch.attribute4,
ch.attribute5,
ch.attribute6,
ch.attribute7,
ch.attribute8,
ch.attribute9,
ch.attribute10,
ch.attribute11,
ch.attribute12,
ch.attribute13,
ch.attribute14,
ch.attribute15
FROM cux_oa_ap_invoice_header ch
WHERE (ch.process_status = 'PENDING' OR
ch.process_status = 'ERROR')
FOR UPDATE NOWAIT;
CURSOR cur_cl(p_header_id IN NUMBER) IS
SELECT cl.line_id,
cl.header_id,
cl.line_num,
cl.line_type_lookup_code,
cl.amount,
cl.accounting_date,
cl.inventory_item_id,
cl.line_description,
cl.tax_code,
cl.tax_code_id,
cl.amount_includes_tax_flag,
cl.dist_code_combination_id,
cl.exp_item_id,
cl.item_id,
cl.invoice_id,
cl.invoice_line_number,
cl.source_code,
cl.source_line_id,
cl.source_reference,
cl.process_group_id,
cl.process_status,
cl.process_date,
cl.process_message,
cl.object_version_number,
cl.creation_date,
cl.created_by,
cl.last_updated_by,
cl.last_update_date,
cl.last_update_login,
cl.attribute_category,
cl.attribute1,
cl.attribute2,
cl.attribute3,
cl.attribute4,
cl.attribute5,
cl.attribute6,
cl.attribute7,
cl.attribute8,
cl.attribute9,
cl.attribute10,
cl.attribute11,
cl.attribute12,
cl.attribute13,
cl.attribute14,
cl.attribute15
FROM cux_oa_ap_invoice_line cl
WHERE cl.header_id = p_header_id
AND (cl.process_status = 'PENDING' OR
cl.process_status = 'ERROR')
ORDER BY cl.line_num
FOR UPDATE NOWAIT;
l_rec_invoice_ifc ap_invoices_interface%ROWTYPE;
l_rec_invoice_line_ifc ap_invoice_lines_interface%ROWTYPE;
l_project_num VARCHAR2(100);
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(240);
l_msg_count NUMBER;
l_group_id NUMBER := cux_doc_sequence_utl.next_seq_number(p_doc_type => 'CUX_OA_AP_IMP',
p_init_number => 1);
l_line_number NUMBER;
l_batch_error_flag VARCHAR2(30);
l_invoices_fetched NUMBER;
l_invoices_created NUMBER;
l_total_invoice_amount NUMBER;
l_print_batch VARCHAR2(30);
l_ret_bool BOOLEAN;
l_ret_status VARCHAR2(1);
l_invoice_batch VARCHAR2(30);
v_invoice_id NUMBER;
BEGIN
x_return_status := cux_api.start_activity(p_pkg_name => g_pkg_name,
p_api_name => l_api_name,
p_init_msg_list => fnd_api.g_true);
raise_exception(x_return_status);
-- INIT
BEGIN
mo_global.init('SQLAP');
fnd_global.apps_initialize(user_id => 1370,
resp_id => 50717,
resp_appl_id => 20005);
END;
FOR rec_ch IN cur_ch LOOP
l_line_number := 0;
l_rec_invoice_ifc.invoice_id := ap_invoices_interface_s.nextval;
l_rec_invoice_ifc.invoice_num := rec_ch.invoice_number;
l_rec_invoice_ifc.invoice_type_lookup_code := rec_ch.invoice_type; -- test REDO
l_rec_invoice_ifc.invoice_date := rec_ch.invoice_date;
l_rec_invoice_ifc.po_number := NULL;
l_rec_invoice_ifc.vendor_id := rec_ch.vendor_id;
l_rec_invoice_ifc.vendor_num := NULL;
l_rec_invoice_ifc.vendor_name := NULL;
l_rec_invoice_ifc.vendor_site_id := rec_ch.vendor_site_id;
l_rec_invoice_ifc.vendor_site_code := NULL;
l_rec_invoice_ifc.invoice_amount := rec_ch.invoice_amount;
l_rec_invoice_ifc.invoice_currency_code := rec_ch.currency_code;
l_rec_invoice_ifc.exchange_rate := NULL;
l_rec_invoice_ifc.exchange_rate_type := NULL;
l_rec_invoice_ifc.exchange_date := NULL;
l_rec_invoice_ifc.terms_id := NULL;
l_rec_invoice_ifc.terms_name := NULL;
l_rec_invoice_ifc.description := rec_ch.description;
l_rec_invoice_ifc.awt_group_id := NULL;
l_rec_invoice_ifc.awt_group_name := NULL;
l_rec_invoice_ifc.last_update_date := SYSDATE;
l_rec_invoice_ifc.last_updated_by := g_user_id;
l_rec_invoice_ifc.last_update_login := g_login_id;
l_rec_invoice_ifc.creation_date := SYSDATE;
l_rec_invoice_ifc.created_by := g_user_id;
l_rec_invoice_ifc.attribute_category := NULL;
l_rec_invoice_ifc.attribute1 := 'OA';
l_rec_invoice_ifc.att
AP INVOICES IMPORT API(NOT request)
最新推荐文章于 2022-05-26 18:33:05 发布
该过程用于处理CUX到应付账款(AP)的发票接口,通过遍历待处理或错误状态的发票,创建或更新AP接口表,然后调用接口进行批量导入。导入成功后更新源表状态,异常处理确保数据完整。
摘要由CSDN通过智能技术生成