APPS.JG_ZZ_VAT_REGISTERS_PKG SQL Statements

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

Line: 7

23/6/2006       BRATHOD      120.1         5166688         Modified the signature of INSERT_ROW procedure in
                                                           to return rowid to caller of API by adding out
                                                           parameter in the call. Refer bug# 5166688 for details
-----------------------------------------------------------------------------------------------------------*/

  procedure insert_row
              ( x_record                            jg_zz_vat_registers_vl%rowtype
              , x_vat_register_id in  out nocopy    jg_zz_vat_registers_b.vat_register_id%type
              , x_row_id          out     nocopy    rowid
              )
  is


  cursor c_gen_vat_register_id
  is
  select jg_zz_vat_registers_b_s.nextval
  from   dual;

Line: 35

    insert into jg_zz_vat_registers_b
              (  vat_register_id
              ,  vat_reporting_entity_id
              ,  register_type
              ,  effective_from_date
              ,  effective_to_date
              ,  created_by
              ,  creation_date
              ,  last_updated_by
              ,  last_update_date
              ,  last_update_login
              )
    values    (  x_vat_register_id
              ,  x_record.vat_reporting_entity_id
              ,  x_record.register_type
              ,  x_record.effective_from_date
              ,  x_record.effective_to_date
              ,  x_record.created_by
              ,  x_record.creation_date
              ,  x_record.last_updated_by
              ,  x_record.last_update_date
              ,  x_record.last_update_login
              ) returning rowid into x_row_id;

Line: 59

    insert into jg_zz_vat_registers_tl
              (  vat_register_id
              ,  register_name
              ,  language
              ,  source_lang
              ,  created_by
              ,  creation_date
              ,  last_updated_by
              ,  last_update_date
              ,  last_update_login
              )
              (
              select
                 x_vat_register_id
              ,  x_record.register_name
              ,  fndlang.language_code
              ,  userenv('LANG')
              ,  x_record.created_by
              ,  x_record.creation_date
              ,  x_record.last_updated_by
              ,  x_record.last_update_date
              ,  x_record.last_update_login
              from  fnd_languages fndlang
              where fndlang.installed_flag in ('I','B')
              and   not exists (select 1
                                from   jg_zz_vat_registers_tl jzvrgtl
                                where  jzvrgtl.vat_register_id = x_vat_register_id
                                and    jzvrgtl.language = fndlang.language_code
                                )
              );

Line: 94

  end insert_row ;

Line: 107

    select  *
    from    jg_zz_vat_registers_b
    where   vat_register_id = x_record.vat_register_id
    for update nowait;

Line: 114

    select  *
    from    jg_zz_vat_registers_tl
    where   vat_register_id = x_record.vat_register_id
    and     ( language    = lv_usr_env_lang
           or source_lang = lv_usr_env_lang
            )
    for update nowait;

Line: 131

      fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 166

  procedure update_row (x_record  jg_zz_vat_registers_vl%rowtype)
  is

  lv_usr_env_lang     fnd_languages.language_code%type;

Line: 175

    update  jg_zz_vat_registers_b
    set     vat_reporting_entity_id=     x_record.vat_reporting_entity_id
          , register_type          =     x_record.register_type
          , effective_from_date    =     x_record.effective_from_date
          , effective_to_date      =     x_record.effective_to_date
          , last_updated_by        =     x_record.last_updated_by
          , last_update_date       =     x_record.last_update_date
          , last_update_login      =     x_record.last_update_login
    where vat_register_id          =     x_record.vat_register_id;

Line: 185

    update   jg_zz_vat_registers_tl
    set      register_name         =    x_record.register_name
           , source_lang           =    lv_usr_env_lang
           , last_updated_by       =    x_record.last_updated_by
           , last_update_date      =    x_record.last_update_date
           , last_update_login     =    x_record.last_update_login
    where  vat_register_id         =    x_record.vat_register_id
    and   ( language               =    lv_usr_env_lang
         or source_lang            =    lv_usr_env_lang
          ) ;

Line: 196

  end update_row;

Line: 200

  procedure delete_row (x_vat_register_id jg_zz_vat_registers_b.vat_register_id%type)
  is
  begin
    delete from jg_zz_vat_registers_b
    where vat_register_id = x_vat_register_id;

Line: 206

    delete from jg_zz_vat_registers_tl
    where vat_register_id = x_vat_register_id;

Line: 209

  end delete_row;

Line: 218

  delete from jg_zz_vat_registers_tl T
  where not exists (
    select NULL
    from jg_zz_vat_registers_b B
    where B.VAT_REGISTER_ID = T.VAT_REGISTER_ID
  );

Line: 225

  update jg_zz_vat_registers_tl T
  set REGISTER_NAME = (select
                             B.REGISTER_NAME
                      from jg_zz_vat_registers_tl B
                      where B.vAT_REGISTER_ID = T.VAT_REGISTER_ID
                      and B.LANGUAGE = T.SOURCE_LANG)
  where (T.VAT_REGISTER_ID, T.LANGUAGE )  in
       (select subt.vat_register_id, subt.language
        from jg_zz_vat_registers_tl SUBB, jg_zz_vat_registers_tl SUBT
        where SUBB.VAT_REGISTER_ID = SUBT.VAT_REGISTER_ID
        and SUBB.LANGUAGE = SUBT.SOURCE_LANG
        and (SUBB.REGISTER_NAME <> SUBT.REGISTER_NAME
            or (SUBB.REGISTER_NAME is null and SUBT.REGISTER_NAME is not null)
            or (SUBB.REGISTER_NAME is not null and SUBT.REGISTER_NAME is null)
            )
       );

Line: 242

  insert into jg_zz_vat_registers_tl (
    VAT_REGISTER_ID,
    REGISTER_NAME,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.VAT_REGISTER_ID,
    B.REGISTER_NAME,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from jg_zz_vat_registers_tl b, fnd_languages l
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from jg_zz_vat_registers_tl T
    where T.VAT_REGISTER_ID = B.VAT_REGISTER_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 301

  l_table_rec.last_update_date         := sysdate   ;

Line: 302

  l_table_rec.last_updated_by          := l_user_id    ;

Line: 305

  l_table_rec.last_update_login        := 0  ;

Line: 308

  jg_zz_vat_registers_pkg.UPDATE_ROW (
    x_record =>   l_table_rec
  );

Line: 314

    jg_zz_vat_registers_pkg.INSERT_ROW (
       x_record          => l_table_rec
      ,x_vat_register_id => ln_vat_register_id
      ,x_row_id          => l_row_id
    );

Line: 329

  update jg_zz_vat_registers_tl set
    REGISTER_NAME = X_REGISTER_NAME,
    LAST_UPDATE_DATE = sysdate,
    LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
    LAST_UPDATE_LOGIN = 0,
    SOURCE_LANG = userenv('LANG')
  where VAT_REGISTER_ID = X_VAT_REGISTER_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);