IS
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_cnt NUMBER := 0;
l_customer_trx_id NUMBER := NULL;
l_inventory_item_id NUMBER := NULL;
l_bill_to_customer NUMBER := NULL;
l_err_trx_header_id NUMBER := NULL;
l_err_trx_line_id NUMBER := NULL;
l_err_msg VARCHAR2 (1000);
l_err_val VARCHAR2 (100);
l_row_cnt NUMBER := 1;
l_return_status VARCHAR2 (80);
v_org_id NUMBER;
l_cust_trx_type_id NUMBER := NULL;
l_code_combination_id NUMBER;
l_status NUMBER := 0;
l_account_class VARCHAR2 (30);
CURSOR c1
IS
SELECT *
FROM xxcs.xx_iit_ar_invoice_headers
WHERE status_flag <> 'P';
CURSOR c2 (p_header_id IN NUMBER)
IS
SELECT x.trxn_line_id,
x.invoice_header_id,
x.line_number,
x.description,
decode (x.description, 'Difference', 'CR', x.line_type) line_type,
decode (x.description, 'Difference', (x.extended_amount * -1), x.extended_amount) extended_amount,
x.ebs_account_combination
FROM xxcs.xx_iit_ar_invoice_lines x
WHERE invoice_header_id = p_header_id
AND decode (x.description, 'Difference', 'CR', x.line_type) = 'CR';
l_error VARCHAR2 (240);
PROCEDURE mark_error (p_hdr_trxn_id NUMBER, p_error VARCHAR2)
IS
BEGIN
UPDATE xxcs.xx_iit_ar_invoice_headers
SET status_flag = 'E',
error_message = p_error,
last_update_date = sysdate,
last_updated_by = 'EBS'
WHERE invoice_header_id = p_hdr_trxn_id;
END;
BEGIN
errbuf := ' ';
retcode := ' ';
DELETE FROM ar_trx_errors_gt;
l_status := 0;
mo_global.set_policy_context ('S', 101);
SELECT mo_global.get_current_org_id ()
INTO v_org_id
FROM dual;
--fnd_file.put_line (fnd_file.LOG, 'Organization ID: ' || v_org_id);
l_batch_source_rec.batch_source_id := -1;
FOR hdr IN c1
LOOP
l_error := NULL;
--
-- Calling Function EBS Customer Account ID from HZ_CUST_ACCOUNTS_ALL
--
BEGIN
SELECT apps.xx_ora_utilities.decode_customer_acc_id (hdr.legacy_customer_code, 'EBS-CODE')
INTO l_bill_to_customer
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_error := 'invalid_customer_code';
END;
--
-- Rebate Applied / Duty Drawback
--
BEGIN
SELECT cust_trx_type_id
INTO l_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = hdr.cust_trx_type_id||' '||'NEW';
EXCEPTION
WHEN OTHERS
THEN
l_error := l_error || ':' || 'invalid_trx_type';
END;
IF l_error IS NOT NULL
THEN
mark_error (hdr.invoice_header_id, l_error);
ELSE
l_trx_header_tbl (1).trx_header_id := hdr.invoice_header_id;
l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
--l_trx_header_tbl (1).reference_number := hdr.legacy_invoice_no;
l_trx_header_tbl (1).attribute10 := hdr.legacy_invoice_no;
l_trx_header_tbl (1).attribute5 := hdr.legacy_invoice_customer;
l_trx_header_tbl (1).attribute_category := 'LEGACY_REFERENCE';
l_trx_header_tbl (1).trx_date := hdr.txn_date;
l_trx_header_tbl (1).bill_to_customer_id := l_bill_to_customer;
l_trx_header_tbl (1).trx_currency := hdr.currency_code;
l_trx_header_tbl (1).interface_header_attribute14 := hdr.src_je_reference;
l_trx_header_tbl (1).term_id := 5;
--l_trx_header_tbl (1).receipt_method_id := 1000;
FOR lines IN c2 (hdr.invoice_header_id)
LOOP
BEGIN
SELECT code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = lines.ebs_account_combination;
EXCEPTION
WHEN no_data_found
THEN
l_error := l_error || ':' || 'invalid_account_combination';
END;
IF l_error IS NOT NULL
THEN
mark_error (hdr.invoice_header_id, l_error);
ELSE
l_trx_lines_tbl (l_row_cnt).trx_header_id := lines.invoice_header_id;
l_trx_lines_tbl (l_row_cnt).trx_line_id := lines.trxn_line_id;
l_trx_lines_tbl (l_row_cnt).line_number := lines.line_number;
l_trx_lines_tbl (l_row_cnt).unit_selling_price := lines.extended_amount;
l_trx_lines_tbl (l_row_cnt).quantity_invoiced := 1;
l_trx_lines_tbl (l_row_cnt).description := lines.description;
l_trx_lines_tbl (l_row_cnt).line_type := 'LINE';
--
-- Distribution Lines
--
l_trx_dist_tbl (l_row_cnt).trx_dist_id := lines.trxn_line_id;
l_trx_dist_tbl (l_row_cnt).trx_line_id := lines.trxn_line_id;
l_trx_dist_tbl (l_row_cnt).account_class := 'REV';
l_trx_dist_tbl (l_row_cnt).PERCENT := 100;
l_trx_dist_tbl (l_row_cnt).code_combination_id := l_code_combination_id;
l_row_cnt := l_row_cnt + 1;
END IF;
END LOOP;
IF l_error IS NULL
THEN
ar_invoice_api_pub.create_single_invoice (p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_customer_trx_id => l_customer_trx_id,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl);
END IF;
IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error OR l_error IS NOT NULL
THEN
mark_error (hdr.invoice_header_id, SUBSTR (l_error || ':' || l_msg_data,
1,
225));
DELETE FROM ar_trx_errors_gt;
ELSE
UPDATE xxcs.xx_iit_ar_invoice_headers
SET status_flag = 'P',
error_message = NULL,
last_update_date = sysdate,
last_updated_by = 'EBS',
ebs_customer_trx_id = l_customer_trx_id
WHERE invoice_header_id = hdr.invoice_header_id;
END IF;
END IF;
END LOOP;
COMMIT;
END;
/
Hi,
ReplyDeleteCan you send me table information.
Thanks
I will recommend anyone looking for Business loan to Le_Meridian they helped me with Four Million USD loan to startup my Quilting business and it's was fast When obtaining a loan from them it was surprising at how easy they were to work with. They can finance up to the amount of $500,000.000.00 (Five Hundred Million Dollars) in any region of the world as long as there 1.9% ROI can be guaranteed on the projects.The process was fast and secure. It was definitely a positive experience.Avoid scammers on here and contact Le_Meridian Funding Service On. lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. if you looking for business loan.
ReplyDelete