Interface Tables:
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all
Concurrent Program to import data from interface tables:-
Auto
invoice Import Program
Script to Migrate data from staging table to Interface Table:
CREATE OR REPLACE PROCEDURE xxx_ar_invoice_api (
errbuf
OUT
VARCHAR2,
rectcode
OUT
VARCHAR2
)
AS
l_org_id hr_operating_units.organization_id%TYPE;
l_sob_id hr_operating_units.set_of_books_id%TYPE;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%TYPE;
l_gl_id_rev ra_cust_trx_types_all.gl_id_rev%TYPE;
l_cust_trx_type_name ra_cust_trx_types_all.NAME%TYPE;
l_currency_code fnd_currencies.currency_code%TYPE;
l_term_id ra_terms_tl.term_id%TYPE;
l_term_name ra_terms_tl.NAME%TYPE;
l_address_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
l_customer_id hz_cust_accounts.cust_account_id%TYPE;
l_verify_flag CHAR (1);
l_error_message VARCHAR2 (2500);
BEGIN
BEGIN
SELECT organization_id,
set_of_books_id
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE NAME = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Operating Unit...';
END;
BEGIN
SELECT cust_trx_type_id, NAME, gl_id_rev
INTO l_cust_trx_type_id,
l_cust_trx_type_name, l_gl_id_rev
FROM ra_cust_trx_types_all
WHERE set_of_books_id =
l_sob_id
AND org_id = l_org_id
AND NAME = 'xxx-Spares-Inv';
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Invoice Type...';
END;
BEGIN
SELECT currency_code
INTO l_currency_code
FROM fnd_currencies
WHERE currency_code = 'INR';
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Currency Code...';
END;
BEGIN
SELECT term_id, NAME
INTO l_term_id, l_term_name
FROM ra_terms_tl
WHERE UPPER (NAME) = UPPER ('IMMEDIATE');
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Terms Name...';
END;
BEGIN
SELECT DISTINCT hcas.cust_acct_site_id, hca.cust_account_id
INTO l_address_id, l_customer_id
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all
hcas,
hz_cust_site_uses_all
hcsu
WHERE hca.party_id = hp.party_id
AND hp.party_id = hps.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND hcsu.primary_flag = 'Y'
AND UPPER (LTRIM (RTRIM (hp.party_name))) =
UPPER (LTRIM (RTRIM ('Customer Name')))
AND hcas.org_id = l_org_id;
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Customer Name...';
END;
INSERT INTO ra_interface_lines_all
(interface_line_id,
batch_source_name, line_type,
cust_trx_type_id, cust_trx_type_name,
trx_date, gl_date,
currency_code, term_id, term_name,
orig_system_bill_customer_id, orig_system_bill_customer_ref,
orig_system_bill_address_id, orig_system_bill_address_ref,
--orig_system_ship_customer_id,
--orig_system_ship_address_id,
orig_system_sold_customer_id, quantity,
--unit_selling_price
amount, description,
conversion_type, conversion_rate,
interface_line_context,
interface_line_attribute1, org_id
)
VALUES (ra_customer_trx_lines_s.NEXTVAL, 'Invoice Migration', 'LINE',
l_cust_trx_type_id, l_cust_trx_type_name, SYSDATE, SYSDATE,
l_currency_code, l_term_id, l_term_name,
l_customer_id, l_customer_id,
l_address_id, l_address_id,
--85222,
--87978,
l_customer_id, 1,
--40000
4000, 'Test Invoice2 12JUN08',
'User', 1, 'Invoice
Migration',
'RINV_000000002', l_org_id
);
INSERT INTO ra_interface_distributions_all
(interface_line_id,
account_class, amount,
code_combination_id, PERCENT, interface_line_context,
interface_line_attribute1, org_id
)
VALUES (ra_customer_trx_lines_s.CURRVAL, 'REV', 4000,
l_gl_id_rev, 100, 'Invoice
Migration',
'RINV_000000002', l_org_id
);
COMMIT;
END xxx_ar_invoice_api;
/
No comments:
Post a Comment