AR Invoice Interface in Oracle Apps

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

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...