APPS.CST_UPD_GIR_MTA_WTA SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

SELECT jle.code_combination_id,
       gir.rowid,
       gir.reference_1,
       xla_gl_sl_link_id_s.nextval
  FROM gl_je_headers        gh,
       gl_import_references gir,
       gl_je_lines          jle
 WHERE gh.je_batch_id       = p_je_batch_id
   AND gh.je_header_id      = jle.je_header_id
   AND gir.je_header_id     = jle.je_header_id
   AND gir.je_line_num      = jle.je_line_num
   AND gir.gl_sl_link_table = 'MTA'
   AND gir.gl_sl_link_id    IS NULL;

Line: 41

SELECT l.rowid,
       m.gl_sl_link_id
  FROM mtl_transaction_accounts m,
       cst_lists_temp           c,
       xla_distribution_links   lk,
       xla_ae_lines             l
 WHERE lk.application_id               = 707
   AND lk.source_distribution_type     = 'MTL_TRANSACTION_ACCOUNTS'
   AND lk.source_distribution_id_num_1 = c.number_1
   AND lk.application_id               = 707
   AND lk.ae_header_id                 = l.ae_header_id
   AND lk.ae_line_num                  = l.ae_line_num
   AND l.application_id                = 707
   AND m.transaction_id                = c.number_2
   AND m.inv_sub_ledger_id             = c.number_1
   AND m.reference_account             = c.number_3;

Line: 66

SELECT NULL
  FROM cst_lists_temp
 WHERE number_1 IS NULL
   AND list_id   = 999;

Line: 94

     debug('  Update GIR.GL_SL_LINK_ID for MTA');

Line: 97

      UPDATE gl_import_references
         SET gl_sl_link_id = gl_sl_link_id_tab(i)
       WHERE rowid   = gir_rowid_tab(i);

Line: 105

       debug('  Update MTA.GL_SL_LINK_ID for MTA');

Line: 108

        UPDATE mtl_transaction_accounts
           SET gl_sl_link_id     = gl_sl_link_id_tab(i)
         WHERE gl_batch_id       = gl_batch_id_tab(i)
           AND reference_account = ccid_tab(i);

Line: 113

       debug('  Insert Cst_Lists_Temp for MTA');

Line: 116

         INSERT INTO cst_lists_temp
         (list_id ,
          number_1,
          number_2,
          number_3,
          varchar_1)
         SELECT 999,
                inv_sub_ledger_id,
                transaction_id,
                reference_account,
                NULL
           FROM mtl_transaction_accounts
          WHERE gl_batch_id       = gl_batch_id_tab(i)
            AND reference_account = ccid_tab(i);

Line: 136

          debug('  Update XAL.GL_SL_LINK_ID for MTA');

Line: 143

           UPDATE xla_ae_lines
              SET gl_sl_link_id   = xl_link_id_tab(i)
            WHERE rowid   = xline_rowid_tab(i);

Line: 149

     DELETE FROM cst_lists_temp;

Line: 173

SELECT jle.code_combination_id,
       gir.rowid,
       gir.reference_1,
       xla_gl_sl_link_id_s.nextval
  FROM gl_je_headers        gh,
       gl_import_references gir,
       gl_je_lines          jle
 WHERE gh.je_batch_id       = p_je_batch_id
   AND gh.je_header_id      = jle.je_header_id
   AND gir.je_header_id     = jle.je_header_id
   AND gir.je_line_num      = jle.je_line_num
   AND gir.gl_sl_link_table = 'WTA'
   AND gir.gl_sl_link_id    IS NULL;

Line: 188

SELECT l.rowid,
       m.gl_sl_link_id
  FROM wip_transaction_accounts m,
       cst_lists_temp           c,
       xla_distribution_links   lk,
       xla_ae_lines             l
 WHERE lk.application_id               = 707
   AND lk.source_distribution_type     = 'WIP_TRANSACTION_ACCOUNTS'
   AND lk.source_distribution_id_num_1 = c.number_1
   AND lk.application_id               = 707
   AND lk.ae_header_id                 = l.ae_header_id
   AND lk.ae_line_num                  = l.ae_line_num
   AND l.application_id                = 707
   AND m.transaction_id                = c.number_2
   AND m.wip_sub_ledger_id             = c.number_1
   AND m.reference_account             = c.number_3;

Line: 206

SELECT NULL
  FROM cst_lists_temp
 WHERE number_1 IS NULL
   AND list_id   = 999;

Line: 244

     debug('  Update GIR.GL_SL_LINK_ID for WTA');

Line: 247

      UPDATE gl_import_references
         SET gl_sl_link_id = gl_sl_link_id_tab(i)
       WHERE rowid   = gir_rowid_tab(i);

Line: 255

       debug('  Update WTA.GL_SL_LINK_ID for WTA');

Line: 258

        UPDATE wip_transaction_accounts
           SET gl_sl_link_id     = gl_sl_link_id_tab(i)
         WHERE gl_batch_id       = gl_batch_id_tab(i)
           AND reference_account = ccid_tab(i);

Line: 263

       debug('  Insert Cst_Lists_Temp for WTA');

Line: 266

         INSERT INTO cst_lists_temp
         (list_id ,
          number_1,
          number_2,
          number_3,
          varchar_1)
         SELECT 999,
                wip_sub_ledger_id,
                transaction_id,
                reference_account,
                NULL
           FROM wip_transaction_accounts
          WHERE gl_batch_id       = gl_batch_id_tab(i)
            AND reference_account = ccid_tab(i);

Line: 286

          debug('  Insert XAL.GL_SL_LINK_ID for WTA');

Line: 293

           UPDATE xla_ae_lines
              SET gl_sl_link_id   = xl_link_id_tab(i)
            WHERE rowid   = xline_rowid_tab(i);

Line: 299

     DELETE FROM cst_lists_temp;

Line: 318

PROCEDURE update_mta_wta
(errbuf           OUT  NOCOPY VARCHAR2,
 retcode          OUT  NOCOPY NUMBER,
 p_from_date      IN VARCHAR2,
 p_to_date        IN VARCHAR2,
 p_ledger_id      IN NUMBER)
IS
CURSOR c_glb(l_from_date IN DATE, l_to_date   IN DATE)
IS
select a.je_batch_id
  from gl_je_batches a,
       gl_period_statuses b
 where a.set_of_books_id_11i    = b.set_of_books_id
   and b.set_of_books_id        = p_ledger_id
   and a.default_effective_date >= l_from_date
   and a.default_effective_date <= l_to_date
   and b.migration_status_code  = 'U'
   and b.application_id         = 401
   and a.name                 like '%Inventory%';

Line: 341

  debug('update_mta_wta +');

Line: 365

  debug('update_mta_wta -');

Line: 370

    debug('EXCEPTION OTHERS update_mta_wta :'||SQLERRM);

Line: 372

END update_mta_wta;