DEclare
l_tax_combination_id number;
l_return_status VARCHAR2 (100);
l_error VARCHAR2 (1000);
v_request_id NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_return_status VARCHAR2 (1);
l_cm_trx_header_id NUMBER;
l_cm_trx_line_id NUMBER;
lc_customer_trx_id NUMBER;
pc_msg_count NUMBER;
pc_msg_data VARCHAR2 (1000);
pc_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
pc_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
pc_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
pc_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
pc_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
lc_currency_code VARCHAR2 (10);
lc_paymenttype VARCHAR2 (100);
ln_code_combination_id NUMBER;
ln_count_cm NUMBER;
ln_crm_trx_number VARCHAR2 (100);
k NUMBER;
l_trx_number VARCHAR2 (100);
l_bill_to_customer_id NUMBER;
o_status varchar2(1000);
o_cm_customer_trx_id number;
new_orderid varchar2(100);
l_purchase_order ra_customer_trx_all.purchase_order%type;
ln_batch_source_id number;
l_cust_trx_type_id number;
CURSOR cl
IS
SELECT error_message
FROM ar_trx_errors_gt;
Cursor c1_invoice_cur is
(select net ,’Refund’ trans_type from
<XXX_TABLE_NAME where po_number=’XXXX’ and trans_type =’XXXX’
union
select fee net ,’tax’ trans_type from
<XXX_TABLE_NAME where po_number=’XXXX’ and trans_type =’XXXX’ );
BEGIN
lc_currency_code := NULL;
lc_paymenttype := NULL;
k := 0;
ln_count_cm := NULL;
ln_crm_trx_number := NULL;
l_trx_number := NULL;
l_bill_to_customer_id := NULL;
l_purchase_order:=NULL;
mo_global.init(‘AR’);
mo_global.set_policy_context(‘S’, 3);
fnd_global.apps_initialize (0,20678,222);
BEGIN –Added on 23-Aug-2023
SELECT cust_trx_type_id
INTO l_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = XXXX’;
EXCEPTION
WHEN OTHERS
THEN
l_cust_trx_type_id := NULL;
END;
BEGIN
SELECT batch_source_id
INTO ln_batch_source_id
FROM ra_batch_sources_all
WHERE NAME = ‘MANUAL-CREDIT MEMO’;– AND org_id = 3;
EXCEPTION
WHEN OTHERS
THEN
ln_batch_source_id := NULL;
END;
BEGIN
SELECT rct.trx_number, rct.bill_to_customer_id,rct.purchase_order
into l_trx_number, l_bill_to_customer_id,l_purchase_order
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rctta
WHERE rct.purchase_order =’XXXX’
AND rctta.TYPE = ‘INV’
AND rct.cust_trx_type_id = rctta.cust_trx_type_id;
EXCEPTION
WHEN OTHERS THEN
l_trx_number:=NULL;
l_bill_to_customer_id:=NULL;
END;
BEGIN
SELECT ra_customer_trx_s.NEXTVAL
INTO l_cm_trx_header_id
FROM DUAL;
END;
BEGIN
SELECT invoice_currency_code
INTO lc_currency_code
FROM apps.ra_customer_trx_all
WHERE purchase_order=’XXXX’and rownum=1;
EXCEPTION
WHEN OTHERS
THEN
lc_currency_code := ‘USD’;
END;
BEGIN
SELECT COUNT (1)
INTO ln_count_cm
FROM ra_customer_trx_all
WHERE trx_number LIKE l_purchase_order || ‘%CM%’;
–and purchase_order=p_amz_order_number;
EXCEPTION
WHEN OTHERS
THEN
ln_count_cm := NULL;
END;
IF NVL (ln_count_cm, 0) >= 1
THEN
ln_crm_trx_number :=l_purchase_order || ‘-CM’ || ln_count_cm;
ELSIF NVL (ln_count_cm, 0) = 0
THEN
ln_crm_trx_number := l_purchase_order || ‘-CM’;
END IF;
k := 1;
— this is the header
pc_batch_source_rec.batch_source_id := ln_batch_source_id; –MANUAL-CREDIT MEMO
pc_trx_header_tbl (1).trx_header_id := l_cm_trx_header_id;
pc_trx_header_tbl (1).trx_number :=ln_crm_trx_number;
–p_invoice_number || ‘-CM’;
pc_trx_header_tbl (1).cust_trx_type_id :=l_cust_trx_type_id;
pc_trx_header_tbl (1).trx_date := trunc(sysdate);
pc_trx_header_tbl (1).bill_to_customer_id := l_bill_to_customer_id;
pc_trx_header_tbl (1).trx_currency := ‘USD’;
pc_trx_header_tbl (1).attribute14 := l_purchase_order;
pc_trx_header_tbl (1).purchase_order := l_purchase_order;
— pc_trx_header_tbl (1).p_line_credit_flag := ‘Y’;
For i in c1_invoice_cur
loop
BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL
INTO l_cm_trx_line_id
FROM DUAL;
END;
dbms_output.put_line (‘l_cm_trx_line_id: ‘ || l_cm_trx_line_id);
BEGIN
SELECT gl_id_rev
INTO ln_code_combination_id
FROM ar_memo_lines_vl
WHERE line_type=’LINE’ and name=’XXXX’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_code_combination_id:=NULL;
END;
BEGIN
SELECT gl_id_rev
INTO l_tax_combination_id
FROM ar_memo_lines_vl
WHERE line_type=’LINE’ and name=’XXXX’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_tax_combination_id:=NULL;
END;
–dbms_output.put_line (‘Code combination ‘ || ln_code_combination_id);
— this is the first line
pc_trx_lines_tbl (k).trx_header_id := l_cm_trx_header_id;
pc_trx_lines_tbl (k).trx_line_id := l_cm_trx_line_id;
–pc_trx_lines_tbl (k).inventory_item_id := i.inventory_item_id;
— pc_trx_lines_tbl(k).link_to_trx_line_id:= l_cm_trx_line_id;
pc_trx_lines_tbl (k).line_number := k;
pc_trx_lines_tbl (k).description := i.trans_type;
pc_trx_lines_tbl (k).quantity_invoiced := 1;
pc_trx_lines_tbl (k).unit_selling_price := (-1)* (i.net);
pc_trx_lines_tbl (k).line_type :=’LINE’;
pc_trx_dist_tbl (k).trx_dist_id := l_cm_trx_line_id;
— pass trx_line_id same as value2
pc_trx_dist_tbl (k).trx_line_id := l_cm_trx_line_id;
— pass trx_line_id same as value2
pc_trx_dist_tbl (k).account_class := ‘REV’;
pc_trx_dist_tbl (k).PERCENT := 100;
if i.trans_type!=’tax’ then
pc_trx_dist_tbl (k).code_combination_id := ln_code_combination_id;
else
pc_trx_dist_tbl (k).code_combination_id := l_tax_combination_id;
end if;
k := k + 1;
END LOOP;
ar_invoice_api_pub.create_single_invoice
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => pc_msg_count,
x_msg_data => pc_msg_data,
x_customer_trx_id => lc_customer_trx_id,
p_batch_source_rec => pc_batch_source_rec,
p_trx_header_tbl => pc_trx_header_tbl,
p_trx_lines_tbl => pc_trx_lines_tbl,
p_trx_dist_tbl => pc_trx_dist_tbl,
p_trx_salescredits_tbl => pc_trx_salescredits_tbl
);
dbms_output.put_line (‘l_return_status: ‘ || l_return_status);
DBMS_OUTPUT.put_line (l_return_status);
dbms_output.put_line (‘l_return_status: ‘ || pc_msg_data);
DBMS_OUTPUT.put_line (pc_msg_data);
–o_cm_customer_trx_id := lc_customer_trx_id; –l_cm_trx_header_id;
IF l_return_status = ‘S’
THEN
o_status := ‘S’;
o_cm_customer_trx_id := lc_customer_trx_id; –l_cm_trx_header_id;
ELSE
o_status := ‘E’;
o_cm_customer_trx_id := NULL; –l_cm_trx_header_id;
END IF;
dbms_output.put_line (‘Status ‘||o_status);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line (‘Error in Creating_cm:’ || SQLERRM);
END ;
