AR INVOIVE INTERFACE
DECLARE
v_phase VARCHAR2 (100);
v_dev_phase VARCHAR2 (100);
v_status VARCHAR2 (100);
v_dev_status VARCHAR2 (100);
v_message VARCHAR2 (100);
v_reqid NUMBER (15);
v_pid BOOLEAN;
v_user_id NUMBER (30);
v_batch_source_id NUMBER;
v_order NUMBER;
v_org_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_appl_short_name fnd_application.application_short_name%TYPE;
CURSOR c1
IS
SELECT fcr.responsibility_id, fr.application_id
FROM fnd_concurrent_requests fcr,
fnd_responsibility fr
WHERE fcr.request_id = '${4}'
AND fcr.responsibility_id = fr.responsibility_id;
CURSOR c2
IS
SELECT fa.application_short_name
FROM fnd_concurrent_programs fcp,
fnd_application fa
WHERE fcp.concurrent_program_name = v_program_short_name
AND fcp.application_id = fa.application_id;
CURSOR c_batch_id
IS
SELECT 1, batch_source_id, NAME
FROM apps.ra_batch_sources_all
WHERE NAME IN (
SELECT DISTINCT a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name LIKE '%DEBIT'
AND filename = '${file1}')
UNION
SELECT 2, batch_source_id, NAME
FROM apps.ra_batch_sources_all
WHERE NAME IN (
SELECT DISTINCT a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name LIKE '%CREDIT'
AND filename = '${file1}')
ORDER BY 1;
BEGIN
OPEN c1;
FETCH c1
INTO
v_resp_id, v_resp_appl_id;
CLOSE c1;
OPEN c2;
FETCH c2
INTO
v_appl_short_name;
CLOSE c2;
FOR
v_batch_data IN c_batch_id
LOOP
fnd_global.apps_initialize ('${FCP_USERID}', v_resp_id, v_resp_appl_id);
v_reqid :=
fnd_request.submit_request ('AR',
'RAXMTR',
NULL,
TO_CHAR (TRUNC (SYSDATE),
'YYYY/MM/DD
HH24:MI:SS'
),
FALSE,
'1',
-99,
v_batch_data.batch_source_id,
v_batch_data.NAME,
TO_CHAR (TRUNC (SYSDATE),
'YYYY/MM/DD
HH24:MI:SS'
),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
NULL
);
COMMIT;
v_pid :=
fnd_concurrent.wait_for_request (v_reqid,
3,
0,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message
);
END LOOP;
END;
*********************
AR
INVOICE INTERFACE
********************/
Interface Tables are :-
-------------------------
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all
Concurrent
Program to import data from interface tables
--------------------------------------------
Autoinvoice
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