Auto Invoice (Account Receivable) Script In Oracle Apps

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;

/