declare
l_return_status varchar2(1);
p_count NUMBER;
l_msg_count number;
l_msg_data varchar2(2000);
l_batch_id number;
l_cnt number := 0;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
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_customer_trx_id number;
cnt number;
v_context varchar2(100);
trx_header_id NUMBER ;
trx_line_id NUMBER ;
trx_dist_id NUMBER ;
L_COUNT2 NUMBER;
LV_SQLERRM varchar2(100);
L_ERRMSG varchar2(100);
W_MESSAGE varchar2(100);
W_STATUS varchar2(100);
LN_MSG_INDEX_OUT varchar2(100);
l_bill_to_customer_id number;
CURSOR GET_RECORDS IS
SELECT CUSTOMER_NUMBER, TRX_DATE, GL_DATE,TRX_REFERENCE,QUANTITY, UNIT_PRICE, CURRENCY
FROM XX_AR_INV_INT_DN
;
BEGIN
FOR I IN GET_RECORDS LOOP
DBMS_OUTPUT.PUT_LINE('1');
SELECT CUSTOMER_ID
INTO l_bill_to_customer_id
FROM
AR_CUSTOMERS
WHERE CUSTOMER_NUMBER = I.CUSTOMER_NUMBER
;
--1. Set applications context if not already set.
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize ( user_id => 1110
,resp_id => 50917
,resp_appl_id => 222);
/* Setting the org context for the particular session */
mo_global.set_policy_context('S',121);
BEGIN
SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
END;
BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL INTO trx_line_id FROM DUAL;
END;
BEGIN
SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
INTO trx_dist_id
FROM DUAL;
END;
DBMS_OUTPUT.PUT_LINE('TRX_HEADER_ID-'||ra_customer_trx_s.CURRVAL);
l_batch_source_rec.batch_source_id := 4002;
l_trx_header_tbl(1).trx_header_id := trx_header_id;
l_trx_header_tbl(1).trx_number := I.TRX_NUMBER;
l_trx_header_tbl(1).bill_to_customer_id := l_bill_to_customer_id;
l_trx_header_tbl(1).cust_trx_type_id := 6002;
l_trx_header_tbl(1).trx_date :=I.TRX_DATE;
l_trx_header_tbl(1).gl_date :=TO_DATE('31-MAR-2023');
l_trx_header_tbl(1).trx_currency := I.CURRENCY;
-- l_trx_header_tbl(1).exchange_date := I.GL_DATE; --if other than functional curr
-- l_trx_header_tbl(1).exchange_rate_type := 'Corporate'; --if other than functional curr
l_trx_header_tbl(1).primary_salesrep_id := -3;
l_trx_header_tbl(1).org_id := 121;
l_trx_header_tbl(1).comments :=I.TRX_REFERENCE;
l_trx_header_tbl(1).trx_class:='DM';
-- l_trx_header_tbl(1).bill_to_site_use_id := 6051;
l_trx_header_tbl(1).term_id := NULL;
l_trx_header_tbl(1).finance_charges := null;
l_trx_header_tbl(1).status_trx := 'OP';
l_trx_header_tbl(1).printing_option := 'PRI';
---Calling API
BEGIN
SELECT
ra_customer_trx_lines_s.nextval
INTO
trx_line_id
FROM dual;
END;
BEGIN
SELECT
ra_cust_trx_line_gl_dist_s.nextval
INTO trx_dist_id
FROM dual;
END;
l_count2 := 1;
l_trx_lines_tbl(l_count2).trx_header_id := trx_header_id;
l_trx_lines_tbl(l_count2).trx_line_id := trx_line_id;
l_trx_lines_tbl(l_count2).line_number := l_count2;
l_trx_lines_tbl(l_count2).DESCRIPTION := NVL(I.DESCRIPTION,I.TRX_REFERENCE);
l_trx_lines_tbl(l_count2).quantity_invoiced := 1;
l_trx_lines_tbl(l_count2).unit_selling_price :=I.UNIT_PRICE;--P_line_amt(i);
l_trx_lines_tbl(l_count2).line_type := TRIM('LINE');
l_trx_lines_tbl(l_count2).taxable_flag := NULL;
l_trx_dist_tbl(l_count2).trx_dist_id := trx_dist_id;
l_trx_dist_tbl(l_count2).trx_header_id := trx_header_id;
l_trx_dist_tbl(l_count2).trx_line_id := trx_line_id;
l_trx_dist_tbl(l_count2).account_class := 'REV';
l_trx_dist_tbl(l_count2).percent := 100;
l_trx_dist_tbl(l_count2).amount := I.UNIT_PRICE;--P_line_amt(i);
l_trx_dist_tbl(l_count2).code_combination_id := 27016;
----Call the invoice api
fnd_global.apps_initialize(1110,50917,222);
mo_global.set_policy_context('S',121);
begin
AR_INVOICE_API_PUB.create_single_invoice(
p_api_version => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
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,
x_customer_trx_id => l_customer_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
commit;
exception when others then
W_Status:='E';
W_Message:=sqlerrm;
end;
END LOOP;
IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('FND_MSG_PUB.Count_Msg ');
dbms_output.put_line(l_return_status||':'||sqlerrm);
FOR l_msg_count IN 1..FND_MSG_PUB.Count_Msg
LOOP
FND_MSG_PUB.Get(p_msg_index => l_msg_count
,p_encoded => 'F'
,p_data => l_msg_data
,p_msg_index_OUT => ln_msg_index_out
);
lv_sqlerrm := SUBSTR(lv_sqlerrm||REPLACE(l_msg_data,CHR(10),''),1,3998);
W_Status := 'E';
W_Message := lv_sqlerrm;
dbms_output.put_line(l_return_status||':'||lv_sqlerrm);
END LOOP;
FND_MSG_PUB.Delete_Msg;
ELSE
dbms_output.put_line('FND_MSG_PUB.Count_Msg else :'||FND_MSG_PUB.Count_Msg);
END IF;
EXCEPTION WHEN OTHERS THEN
l_ErrMsg :=SQLERRM;
END ;
--for any issues check the table
SELECT * FROM ar_trx_errors_gt;
--OPEN Issues
Not able to find CT_Reference field, so used comments field to pass the value.
