Auto Invoice Script

--Just replace the constant values with variables and call this procedure in a valid cursor loop

CREATE OR REPLACE PROCEDURE inv_proc_v (
asqlcode OUT NUMBER,
asqlerrm OUT VARCHAR2
)
IS
v_batch_source_name VARCHAR2 (100);
v_currency_code VARCHAR2 (10);
v_cust_trx_type_id NUMBER;
v_term_id NUMBER;
BEGIN
BEGIN
SELECT NAME
INTO v_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = 1228 AND org_id = 204;
END;

BEGIN
SELECT currency_code
INTO v_currency_code
FROM fnd_currencies
WHERE issuing_territory_code = 'US' AND enabled_flag = 'Y';
END;

BEGIN
SELECT cust_trx_type_id
INTO v_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = 'Invoice' AND org_id = 204;
END;

BEGIN
SELECT term_id
INTO v_term_id
FROM ra_terms_tl
WHERE NAME = '30 Net';
END;

BEGIN
SELECT set_of_books_id
INTO v_sob_id
FROM gl.gl_sets_of_books
WHERE short_name = 'Vision Operations';
END;

BEGIN
INSERT INTO ra_interface_lines_all
(interface_line_id, interface_line_context,
--ra_batch_sources_all
interface_line_attribute1, interface_line_attribute2,
batch_source_name, --ra_batch_sources_all
set_of_books_id, --gl.gl_setsof_books
line_type, tax_code,
--FND_lookups.tax_type
description, --mtl_system_items
currency_code, --fnd_currencies
amount, cust_trx_type_id,
--ra_cust_trx_types_all
term_id, --ar_terms
conversion_type,
--GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE
conversion_rate, trx_date,
gl_date, quantity, quantity_ordered, unit_selling_price,
unit_standard_price, inventory_item_id, --MTL_SYSTEM_ITEMS
territory_id,
--ra_territories
uom_code, --MTL_UNITS_OF_MEASURE
created_by, --fnd_global.user_id
creation_date, last_update_date,
last_updated_by, org_id, --fnd_global.org_id
tax_exempt_flag,
--FND_lookups.tax_control_flag
orig_system_bill_customer_id, --hz_customer_accounts
orig_system_bill_address_id, --hz_cust_sites_USES_all
orig_system_sold_customer_id
) --hz_customer_accounts
VALUES (ra_customer_trx_lines_s.NEXTVAL, 'LEGACY',
'1101233', '11045',
'LEGACY', 1, 'LINE', 'Exempt',
'Paper Carrier', 'USD', '1000.00', 1,
1060, 'User', 1, SYSDATE,
SYSDATE, 12, 12, 50.00,
50.00, 6074, 1003,
'Ea', 1318, SYSDATE, SYSDATE,
1318, 204, 'S',
5789,
5808, 5789
);
END;
END inv_proc_v;
/