APPS.GL_JOURNAL_IMPORT_SLA_PKG SQL Statements

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

Line: 4

  PROCEDURE delete_batches(x_je_source_name  VARCHAR2,
			   x_group_id        NUMBER) IS
  BEGIN

    UPDATE GL_BC_PACKETS
      SET  je_batch_id = -1, group_id = NULL
    WHERE  group_id = x_group_id;

Line: 12

    DELETE gl_import_references
    WHERE je_header_id IN
      (SELECT jeh.je_header_id
       FROM gl_je_batches jeb,
            gl_je_headers jeh
       WHERE jeb.status = 'U'
       AND   jeb.group_id = x_group_id
       AND   jeh.je_batch_id = jeb.je_batch_id
       AND   jeh.ledger_id < 0
       AND   jeh.je_source = x_je_source_name);

Line: 23

    DELETE gl_je_lines
    WHERE je_header_id IN
      (SELECT jeh.je_header_id
       FROM gl_je_batches jeb,
            gl_je_headers jeh
       WHERE jeb.status = 'U'
       AND   jeb.group_id = x_group_id
       AND   jeh.je_batch_id = jeb.je_batch_id
       AND   jeh.ledger_id < 0
       AND   jeh.je_source = x_je_source_name);

Line: 34

    DELETE gl_je_headers jeh
    WHERE jeh.ledger_id < 0
    AND   jeh.je_source = x_je_source_name
    AND   jeh.je_batch_id IN
      (SELECT jeb.je_batch_id
       FROM gl_je_batches jeb
       WHERE jeb.status = 'U'
       AND   jeb.group_id = x_group_id);

Line: 43

    DELETE gl_je_batches jeb
    WHERE jeb.status = 'U'
    AND   jeb.group_id = x_group_id
    AND NOT EXISTS
      (SELECT 'has journals'
       FROM gl_je_headers jeh
       WHERE jeh.je_batch_id = jeb.je_batch_id);

Line: 50

  END delete_batches;

Line: 64

     SELECT max(abs(JEH.ledger_id))
     FROM   GL_JE_BATCHES JEB,
            GL_JE_HEADERS JEH
     WHERE  JEB.status = 'S'
     AND    JEB.posting_run_id = c_prun_id
     AND    JEH.je_batch_id = JEB.je_batch_id
     GROUP BY JEB.posting_run_id
     HAVING count(distinct abs(JEH.ledger_id)) = 1;

Line: 74

     SELECT '1'
     FROM   GL_JE_BATCHES JEB,
            GL_JE_HEADERS JEH
     WHERE  JEB.status = 'S'
     AND    JEB.posting_run_id = c_prun_id
     AND    JEH.je_batch_id = JEB.je_batch_id
     AND    JEH.actual_flag <> 'B'
     AND    JEH.reversed_je_header_id IS NULL
     AND EXISTS
       (SELECT 1
        FROM   GL_LEDGER_RELATIONSHIPS LRL
        WHERE  LRL.source_ledger_id = abs(JEH.ledger_id)
        AND    LRL.target_ledger_category_code = 'ALC'
        AND    LRL.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
        AND    LRL.application_id = 101
        AND    LRL.relationship_enabled_flag = 'Y'
        AND    JEH.je_source NOT IN
          (SELECT INC.je_source_name
           FROM   GL_JE_INCLUSION_RULES INC
           WHERE  INC.je_rule_set_id =
                     LRL.gl_je_conversion_set_id
           AND    INC.je_source_name = JEH.je_source
           AND    INC.je_category_name = 'Other'
           AND    INC.include_flag = 'N'
           AND    INC.user_updatable_flag = 'N'));

Line: 108

      SELECT GL_JE_POSTING_S.nextval
      INTO prun_id
      FROM dual;

Line: 112

      UPDATE gl_je_batches jeb
      SET    status = 'S',
             posting_run_id = prun_id
      WHERE jeb.status = 'U'
      AND   jeb.group_id = x_group_id
      AND   jeb.approval_status_code = 'Z'
      AND NOT EXISTS
        (SELECT 'not open period'
         FROM   gl_je_headers jeh,
                gl_ledgers lgr,
                gl_period_statuses ps
         WHERE  jeh.je_batch_id = jeb.je_batch_id
         AND    lgr.ledger_id = - jeh.ledger_id
         AND    ps.application_id = 101
         AND    ps.ledger_id = - jeh.ledger_id
         AND    ps.period_name = jeh.period_name
         AND    (    (    (jeh.actual_flag = 'A')
                      AND (ps.closing_status <> 'O'))
                 OR  (    (jeh.actual_flag = 'E')
                      AND (least(nvl(lgr.latest_encumbrance_year,0),
                                    ps.period_year) <> ps.period_year))
                 OR  (    (jeh.actual_flag = 'B')
                      AND (NOT EXISTS
                             (SELECT 'open year'
                              FROM   gl_budget_period_ranges pr
                              WHERE  pr.budget_version_id
                                       = jeh.budget_version_id
                              AND    pr.period_year = ps.period_year
                              AND    ps.period_num
                                       between pr.start_period_num
                                       and pr.end_period_num)))))
      AND  EXISTS
        (SELECT 'has negative journals'
         FROM  gl_je_headers jeh
         WHERE jeh.je_batch_id = jeb.je_batch_id
         AND   jeh.ledger_id < 0
         AND   jeh.je_source = x_je_source_name);

Line: 157

        SELECT chart_of_accounts_id
        INTO coa_id
        FROM gl_je_batches jeb
        WHERE jeb.status = 'S'
        AND   jeb.posting_run_id = prun_id
        AND   rownum = 1;

Line: 229

          UPDATE gl_je_batches jeb
          SET request_id = req_id
          WHERE jeb.status = 'S'
          AND  EXISTS
            (SELECT 'has negative journals'
             FROM  gl_je_headers jeh
             WHERE jeh.je_batch_id = jeb.je_batch_id
             AND   jeh.ledger_id < 0
             AND   jeh.je_source = x_je_source_name);

Line: 243

    UPDATE /*+ INDEX(jel GL_JE_LINES_U1) */
           gl_je_lines jel
    SET   jel.ledger_id = -jel.ledger_id
    WHERE jel.je_header_id IN
      (SELECT /*+ ORDERED
                  INDEX(jeb GL_JE_BATCHES_N1)
                  INDEX(jeh GL_JE_HEADERS_N1)
               */
              jeh.je_header_id
       FROM gl_je_batches jeb,
            gl_je_headers jeh
       WHERE jeb.status in ('U','S')
       AND   nvl(jeb.posting_run_id, prun_id) = prun_id
       AND   jeb.group_id = x_group_id
       AND   jeh.je_batch_id = jeb.je_batch_id
       AND   jeh.ledger_id < 0
       AND   jeh.je_source = x_je_source_name);

Line: 262

    UPDATE gl_je_headers jeh
    SET jeh.ledger_id = -jeh.ledger_id
    WHERE jeh.ledger_id < 0
    AND   jeh.je_source = x_je_source_name
    AND   jeh.je_batch_id IN
      (SELECT jeb.je_batch_id
       FROM gl_je_batches jeb
       WHERE jeb.status in ('U','S')
       AND   nvl(jeb.posting_run_id, prun_id) = prun_id
       AND   jeb.group_id = x_group_id);