Sample Code For to Run Autoinvoice Master Program in Oracle Apps

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

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 ...