-- b. Declaration section
DECLARE
L_CUSTOMER_TRX_ID number; W_TRX_NUMBER number; W_STATUS varchar2(2000); W_MESSAGE varchar2(2000);
W_CUSTOMER_TRX_ID number;
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);
l_batch_id number;
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_trx_created number;
l_cnt number;
trx_header_id number;trx_line_id number;trx_dist_id number;
cursor cbatch IS
select customer_trx_id
from ra_customer_trx_all
where batch_id = l_batch_id;
cursor list_errors is
SELECT trx_header_id, trx_line_id, trx_salescredit_id, trx_dist_id,
trx_contingency_id, error_message, invalid_value
FROM ar_trx_errors_gt;
BEGIN
-- c. Set the applications context
mo_global.init('AR');
mo_global.set_policy_context('S','2352');
fnd_global.apps_initialize( user_id => 2605
,resp_id => 52470
,resp_appl_id => 222);
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;
-- d. Populate batch source information.
l_batch_source_rec.batch_source_id := -1;
-- e. Populate header information for first invoice
l_trx_header_tbl(1).trx_header_id := trx_header_id;
l_trx_header_tbl(1).bill_to_customer_id := 9741114;--9703695;
l_trx_header_tbl(1).cust_trx_type_id := 13507;
l_trx_header_tbl(1).term_id := 5;
-- l_trx_header_tbl(1).AL_DEFAULT_TAX_EXEMPT_FLAG := 'AL_DEFAULT_TAX_EXEMPT_FLAG';
-- f. Populate lines information for first invoice
l_trx_lines_tbl(1).trx_header_id := trx_header_id;
l_trx_lines_tbl(1).trx_line_id := trx_line_id;
l_trx_lines_tbl(1).line_number := 1;
-- l_trx_lines_tbl(1).description := 'Product Description 1';
l_trx_lines_tbl(1).MEMO_LINE_ID := 78139;
l_trx_lines_tbl(1).quantity_invoiced := 1;
l_trx_lines_tbl(1).unit_selling_price := 1000.00;
l_trx_lines_tbl(1).line_type := 'LINE';
-- Populate Distribution Information
l_trx_dist_tbl(1).trx_dist_id := trx_dist_id;
l_trx_dist_tbl(1).trx_header_id := trx_header_id;
l_trx_dist_tbl(1).trx_LINE_ID := trx_line_id;
l_trx_dist_tbl(1).ACCOUNT_CLASS := 'REV';
l_trx_dist_tbl(1).AMOUNT := 1000;
l_trx_dist_tbl(1).CODE_COMBINATION_ID := 3398536;--3374927;
-- k. Call the invoice api to create multiple invoices in a batch.
/*AR_INVOICE_API_PUB.create_invoice(
p_api_version => 1.0,
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_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
-- l. check for errors
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('FAILURE: Unexpected errors were raised!');
ELSE
-- m. check batch/invoices created
select distinct batch_id
into l_batch_id
from ar_trx_header_gt;
IF l_batch_id IS NOT NULL THEN
dbms_output.put_line('SUCCESS: Created batch_id = ' || l_batch_id || ' containing the following customer_trx_id:');
for c in cBatch loop
dbms_output.put_line (' ' || c.customer_trx_id );
end loop;
END IF;
END IF;
-- n. Within the batch, check if some invoices raised errors
SELECT count(*)
INTO l_cnt
FROM ar_trx_errors_gt;
IF l_cnt > 0 THEN
dbms_output.put_line('FAILURE: Errors encountered, see list below:');
FOR i in list_errors LOOP
dbms_output.put_line('----------------------------------------------------');
dbms_output.put_line('Header ID = ' || to_char(i.trx_header_id));
-- dbms_output.put_line('Line ID = ' || to_char(i.trx_line_id));
dbms_output.put_line('Sales Credit ID = ' || to_char(i.trx_salescredit_id));
dbms_output.put_line('Dist Id = ' || to_char(i.trx_dist_id));
dbms_output.put_line('Contingency ID = ' || to_char(i.trx_contingency_id));
dbms_output.put_line('Message = ' || substr(i.error_message,1,80));
dbms_output.put_line('Invalid Value = ' || substr(i.invalid_value,1,80));
dbms_output.put_line('----------------------------------------------------');
END LOOP;
END IF;*/
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;
DBMS_OUTPUT.PUT_LINE ('l_return_status-' || L_RETURN_STATUS);
DBMS_OUTPUT.PUT_LINE ('l_customer_trx_id-' || L_CUSTOMER_TRX_ID);
DBMS_OUTPUT.PUT_LINE ('l_msg_count -' || L_MSG_COUNT);
IF L_CUSTOMER_TRX_ID > 0
THEN
BEGIN
W_customer_trx_id := L_CUSTOMER_TRX_ID;
SELECT TRX_NUMBER
INTO W_TRX_NUMBER
FROM RA_CUSTOMER_TRX
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;
END;
DBMS_OUTPUT.PUT_LINE (
'Generated Oracle Txn Number-->' || W_TRX_NUMBER);
W_STATUS := 'Success';
W_MESSAGE := 'Invoice is Created';
DBMS_OUTPUT.PUT_LINE (W_STATUS);
ELSE
W_STATUS := 'E';
END IF;
END;
/
COMMIT;
--DELETE
--select * FROM ar_trx_errors_gt;
--SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE CREATION_DATE LIKE SYSDATE;
