APPS.JAI_EXCISE_SCRIPTS_PKG SQL Statements

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

Line: 20

  SELECT max(slno)
    FROM JAI_CMN_RG_23AC_II_TRXS jrg
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND fin_year        = p_fin_year
     AND slno            < p_slno
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type        = 1
                     AND tax_type           = p_tax_type
                     AND source_register_id = jrg.register_id );

Line: 34

  SELECT max(slno)
    FROM JAI_CMN_RG_23AC_II_TRXS jrg
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND fin_year        = p_fin_year - 1
     AND exists ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type        = 1
                     AND tax_type           = p_tax_type
                     AND source_register_id = jrg.register_id );

Line: 47

  SELECT closing_balance
    FROM JAI_CMN_RG_OTHERS
   WHERE source_type        = 1
     AND tax_type           = p_tax_type
     AND source_register_id = ( SELECT register_id
                                  FROM JAI_CMN_RG_23AC_II_TRXS
                                 WHERE organization_id = p_organization_id
                                   AND location_id     = p_location_id
                                   AND register_type   = p_register_type
                                   AND fin_year        = p_fin_year
                                   AND slno            = cp_slno) ;

Line: 60

  SELECT max(slno)
    FROM JAI_CMN_RG_PLA_TRXS jpl
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND fin_year        = p_fin_year
     AND slno            < p_slno
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type        = 2
                     AND tax_type           = p_tax_type
                     AND source_register_id = jpl.register_id );

Line: 74

  SELECT max(slno)
    FROM JAI_CMN_RG_PLA_TRXS jpl
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND fin_year        = p_fin_year - 1
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type        = 2
                     AND tax_type           = p_tax_type
                     AND source_register_id = jpl.register_id );

Line: 86

  SELECT closing_balance
   FROM JAI_CMN_RG_OTHERS
   WHERE source_type        = 2
     AND tax_type           = p_tax_type
     AND source_register_id = ( SELECT register_id
                                  FROM JAI_CMN_RG_PLA_TRXS
                                 WHERE organization_id = p_organization_id
                                   AND location_id     = p_location_id
                                   AND fin_year        = p_fin_year
                                   AND slno            = cp_slno );

Line: 160

  SELECT max(slno)
    FROM JAI_CMN_RG_23AC_II_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND fin_year        = p_fin_year
     AND slno            < p_slno ;

Line: 169

  SELECT max(slno)
    FROM JAI_CMN_RG_23AC_II_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND fin_year        = p_fin_year - 1;

Line: 177

  SELECT closing_balance
    FROM JAI_CMN_RG_23AC_II_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND fin_year        = p_fin_year
     AND slno            = cp_slno ;

Line: 187

  SELECT max(slno)
    FROM JAI_CMN_RG_PLA_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND fin_year        = p_fin_year
     AND slno            < p_slno ;

Line: 195

  SELECT max(slno)
    FROM JAI_CMN_RG_PLA_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND fin_year        = p_fin_year - 1;

Line: 202

  SELECT closing_balance
    FROM JAI_CMN_RG_PLA_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND fin_year        = p_fin_year
     AND slno            = cp_slno ;

Line: 276

    UPDATE JAI_CMN_RG_23AC_II_TRXS
       SET slno            = slno + p_dup_cnt - 1
     WHERE organization_id = p_organization_id
       AND location_id     = p_location_id
       AND register_type   = p_register_type
       AND fin_year        = p_fin_year
       AND slno            > p_slno;

Line: 284

    UPDATE JAI_CMN_RG_SLNOS
       SET slno              = slno + p_dup_cnt - 1
     WHERE organization_id   = p_organization_id
       AND location_id       = p_location_id
       AND register_type     = p_register_type
       AND current_fin_year  = p_fin_year;

Line: 292

    FOR dup_rec in ( SELECT *
                       FROM JAI_CMN_RG_23AC_II_TRXS
                      WHERE organization_id = p_organization_id
                        AND location_id     = p_location_id
                        AND register_type   = p_register_type
                        AND fin_year        = p_fin_year
                        AND slno            = p_slno
                      ORDER BY register_id ) LOOP


       UPDATE JAI_CMN_RG_23AC_II_TRXS
          SET slno            = slno + ln_cnt
        WHERE register_id     = dup_rec.register_id;

Line: 312

    UPDATE JAI_CMN_RG_PLA_TRXS
       SET slno            = slno + p_dup_cnt - 1
     WHERE organization_id = p_organization_id
       AND location_id     = p_location_id
       AND fin_year        = p_fin_year
       AND slno            > p_slno;

Line: 319

    UPDATE JAI_CMN_RG_SLNOS
       SET slno              = slno + p_dup_cnt - 1
     WHERE organization_id   = p_organization_id
       AND location_id       = p_location_id
       AND register_type     = p_register_type
       AND current_fin_year  = p_fin_year;

Line: 328

    FOR dup_rec in ( SELECT *
                       FROM JAI_CMN_RG_PLA_TRXS
                      WHERE organization_id = p_organization_id
                        AND location_id     = p_location_id
                        AND fin_year        = p_fin_year
                        AND slno            = p_slno
                      ORDER BY register_id ) LOOP

       UPDATE JAI_CMN_RG_PLA_TRXS
          SET slno            = slno + ln_cnt
        WHERE register_id     = dup_rec.register_id;

Line: 361

    DELETE JAI_CMN_RG_PERIOD_BALS
     WHERE organization_id = p_organization_id
       AND location_id     = p_location_id
       AND register_type   = decode(p_register_type,'A','RG23A','C','RG23C')
       AND start_date      >= p_start_date;

Line: 367

    UPDATE JAI_CMN_RG_23AC_II_TRXS
       SET period_balance_id = NULL
     WHERE organization_id        = p_organization_id
       AND location_id            = p_location_id
       AND register_type          = p_register_type
       AND trunc(creation_date)  >= p_start_date;

Line: 392

  SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
           - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
    FROM JAI_CMN_RG_23AC_II_TRXS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = p_register_type
     AND trunc(creation_date) between cp_start_date and cp_end_date
     AND inventory_item_id <> 0;

Line: 402

  SELECT closing_balance - opening_balance
    FROM JAI_CMN_RG_PERIOD_BALS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = decode(p_register_type,'A','RG23A','C','RG23C')
     AND start_date      = cp_start_date
     AND end_date        = cp_end_date;

Line: 412

  SELECT sum(nvl(b.credit,0) - nvl(b.debit,0)) total_cess
    FROM JAI_CMN_RG_23AC_II_TRXS  a, JAI_CMN_RG_OTHERS b
   WHERE a.organization_id   = p_organization_id
     AND a.location_id       = p_location_id
     AND a.register_type     = p_register_type
     AND trunc(a.creation_date) between cp_start_date and cp_end_date
     AND a.inventory_item_id <> 0
     AND b.source_register_id = a.register_id
     AND b.source_type        = 1
     AND b.tax_type           = cp_tax_type;

Line: 424

  SELECT exc_edu_cess_cl_bal,
         cvd_edu_cess_cl_bal,
         sh_exc_edu_cess_cl_bal,
         sh_cvd_edu_cess_cl_bal
    FROM JAI_CMN_RG_PERIOD_BALS
   WHERE organization_id = p_organization_id
     AND location_id     = p_location_id
     AND register_type   = decode(p_register_type,'A','RG23A','C','RG23C')
     AND start_date      = cp_start_date
     AND end_date        = cp_end_date;

Line: 597

                                                        p_last_updated_by  NUMBER
                                                     )
  IS
  CURSOR cur_rg23_next_records IS
  SELECT jrg.opening_balance ,
         jrg.closing_balance ,
         nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
         jrg.last_updated_by,
         jrg.last_update_date,
         jrg.rg_other_id,
         rg23.slno,
         rg23.organization_id,
         rg23.location_id
    FROM JAI_CMN_RG_OTHERS jrg,
         JAI_CMN_RG_23AC_II_TRXS rg23
   WHERE organization_id    = p_organization_id
     AND location_id        = p_location_id
     AND ((fin_year           = p_fin_year
           AND slno               >= p_slno)
          OR fin_year > p_fin_year )
     AND register_type      = p_register_type
     AND source_type        = 1
     AND source_register    = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
     AND tax_type           = p_tax_type
     AND source_register_id = rg23.register_id
   ORDER BY fin_year,slno
   FOR UPDATE OF jrg.opening_balance,
                 jrg.closing_balance,
                 jrg.last_updated_by,
                 jrg.last_update_date;

Line: 629

  SELECT jrg.opening_balance ,
         jrg.closing_balance ,
         nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
         jrg.last_updated_by,
         jrg.last_update_date,
         jrg.rg_other_id,
         jpl.slno,
         jpl.organization_id,
         jpl.location_id
    FROM JAI_CMN_RG_OTHERS jrg,
         JAI_CMN_RG_PLA_TRXS jpl
   WHERE source_type        = 2
     and tax_type           = p_tax_type
     and source_register_id = jpl.register_id
     and organization_id    = p_organization_id
     AND location_id        = p_location_id
     AND ((fin_year           = p_fin_year
           AND slno               >= p_slno)
          OR fin_year > p_fin_year )
   ORDER BY fin_year,slno
   FOR UPDATE OF jrg.opening_balance,
                 jrg.closing_balance,
                 jrg.last_updated_by,
                 jrg.last_update_date;

Line: 688

        UPDATE JAI_CMN_RG_OTHERS
           SET opening_balance  = ln_opening_balance,
               closing_balance  = ln_closing_balance,
               last_updated_by  = p_last_updated_by,
               last_update_date = sysdate
         WHERE CURRENT OF cur_rg23_next_records;

Line: 699

      UPDATE JAI_CMN_RG_OTH_BALANCES
         SET balance          = ln_prev_balance,
             last_updated_by  = p_last_updated_by,
             last_update_date = sysdate
       WHERE org_unit_id  = ( SELECT org_unit_id
                               FROM JAI_CMN_INVENTORY_ORGS
                              WHERE organization_id = p_organization_id
                                AND location_id     = p_location_id )
        AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
        AND tax_type      = p_tax_type;

Line: 717

        UPDATE JAI_CMN_RG_OTHERS
           SET opening_balance = ln_opening_balance,
               closing_balance = ln_closing_balance,
               last_updated_by  = p_last_updated_by,
               last_update_date = sysdate
         WHERE CURRENT OF cur_pla_next_records;

Line: 728

      UPDATE JAI_CMN_RG_OTH_BALANCES
         SET balance          = ln_prev_balance,
             last_updated_by  = p_last_updated_by,
             last_update_date = sysdate
       WHERE org_unit_id = ( SELECT org_unit_id
                               FROM JAI_CMN_INVENTORY_ORGS
                              WHERE organization_id = p_organization_id
                                AND location_id     = p_location_id )
        AND register_type = 'PLA'
        AND tax_type      = p_tax_type;

Line: 759

        UPDATE JAI_CMN_RG_BALANCES
           SET rg23a_balance   = p_closing_balance
         WHERE organization_id = p_organization_id
           AND location_id     = p_location_id
           AND rg23a_balance   <> p_closing_balance;

Line: 768

        UPDATE JAI_CMN_RG_BALANCES
           SET rg23c_balance   = p_closing_balance
         WHERE organization_id = p_organization_id
           AND location_id     = p_location_id
           AND rg23a_balance   <> p_closing_balance;

Line: 776

        UPDATE JAI_CMN_RG_BALANCES
           SET pla_balance   = p_closing_balance
         WHERE organization_id = p_organization_id
           AND location_id     = p_location_id
           AND rg23a_balance   <> p_closing_balance;

Line: 785

        UPDATE JAI_CMN_RG_SLNOS
           SET balance = p_closing_balance
         WHERE organization_id  = p_organization_id
           AND location_id      = p_location_id
           AND register_type    = p_register_type
           AND balance          <> p_closing_balance ;

Line: 794

      UPDATE JAI_CMN_RG_OTH_BALANCES
         SET balance = p_closing_balance
       WHERE org_unit_id  = ( SELECT org_unit_id
                                FROM JAI_CMN_INVENTORY_ORGS
                               WHERE organization_id  = p_organization_id
                                 AND location_id      = p_location_id )
         AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
         AND tax_type      = p_tax_type
         AND balance       <> p_closing_balance;

Line: 817

  UPDATE JAI_CMN_RG_SLNOS
     SET slno             = p_slno
   WHERE organization_id  = p_organization_id
     AND location_id      = p_location_id
     AND register_type    = p_register_type
     AND slno             <> p_slno ;

Line: 835

  SELECT jrg.opening_balance ,
         jrg.closing_balance ,
         nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
         jrg.last_updated_by,
         jrg.last_update_date,
         jrg.rg_other_id,
         rg23.slno,
         rg23.organization_id,
         rg23.location_id
    FROM JAI_CMN_RG_OTHERS jrg,
         JAI_CMN_RG_23AC_II_TRXS rg23
   WHERE organization_id    = p_organization_id
     AND location_id        = p_location_id
     AND fin_year           = cp_fin_year
     AND register_type      = p_register_type
     AND slno               > nvl(cp_slno,1)
     AND source_type        = 1
     AND source_register    = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
     AND tax_type           = cp_tax_type
     AND source_register_id = rg23.register_id
   ORDER BY slno;

Line: 858

  SELECT jrg.opening_balance ,
         jrg.closing_balance ,
         nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
         jrg.last_updated_by,
         jrg.last_update_date,
         jrg.rg_other_id,
         jpl.slno,
         jpl.organization_id,
         jpl.location_id
    FROM JAI_CMN_RG_OTHERS jrg,
         JAI_CMN_RG_PLA_TRXS jpl
   WHERE source_type        = 2
     and tax_type           = cp_tax_type
     and source_register_id = jpl.register_id
     and organization_id    = p_organization_id
     AND location_id        = p_location_id
     AND fin_year           = cp_fin_year
     AND slno               > nvl(cp_slno,1)
   ORDER BY slno;

Line: 879

  SELECT fin_year
    FROM JAI_CMN_FIN_YEARS
   WHERE organization_id = p_organization_id
     AND fin_active_flag = 'Y';

Line: 885

  SELECT fin_year
    FROM JAI_CMN_FIN_YEARS
   WHERE organization_id     = p_organization_id
     AND p_date between fin_year_start_date and fin_year_end_date;

Line: 891

  SELECT max(slno),min(slno)
    FROM JAI_CMN_RG_23AC_II_TRXS jrg
   WHERE organization_id       = p_organization_id
     AND location_id           = p_location_id
     AND fin_year              = cp_fin_year
     AND register_type         = p_register_type
     AND trunc(creation_date)  < cp_date
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type = 1
                     AND source_register_id = jrg.register_id
                     AND tax_type           = cp_tax_type );

Line: 906

  SELECT 1
    FROM JAI_CMN_RG_23AC_II_TRXS jrg
   WHERE organization_id       = p_organization_id
     AND location_id           = p_location_id
     AND fin_year              = cp_fin_year
     AND register_type         = p_register_type
     AND trunc(creation_date)  < cp_date
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type = 1
                     AND source_register_id = jrg.register_id
                     AND tax_type           = cp_tax_type );

Line: 922

  SELECT max(slno),min(slno)
    FROM JAI_CMN_RG_PLA_TRXS jpl
   WHERE organization_id       = p_organization_id
     AND location_id           = p_location_id
     AND fin_year              = cp_fin_year
     AND trunc(creation_date)  < cp_date
     AND EXISTS ( SELECT 1
                    FROM JAI_CMN_RG_OTHERS
                   WHERE source_type        = 2
                     AND source_register_id = jpl.register_id
                     AND tax_type           = cp_tax_type );

Line: 935

SELECT 1
  FROM JAI_CMN_RG_PLA_TRXS jpl
 WHERE organization_id       = p_organization_id
   AND location_id           = p_location_id
   AND fin_year              = cp_fin_year
   AND trunc(creation_date)  < cp_date
   AND EXISTS ( SELECT 1
                  FROM JAI_CMN_RG_OTHERS
                 WHERE source_type        = 2
                   AND source_register_id = jpl.register_id
                   AND tax_type           = cp_tax_type );

Line: 950

  SELECT closing_balance
    FROM JAI_CMN_RG_OTHERS
   WHERE source_type = 1
     AND tax_type    = cp_tax_type
     AND source_register_id in ( SELECT register_id
                                   FROM JAI_CMN_RG_23AC_II_TRXS
                                  WHERE organization_id = p_organization_id
                                    AND location_id     = p_location_id
                                    AND register_type   = p_register_type
                                    AND fin_year        = cp_fin_year
                                    AND slno            = nvl(cp_slno,1) );

Line: 963

  SELECT closing_balance
    FROM JAI_CMN_RG_OTHERS
   WHERE source_type = 2
     AND tax_type    = cp_tax_type
     AND source_register_id in ( SELECT register_id
                                   FROM JAI_CMN_RG_PLA_TRXS
                                  WHERE organization_id = p_organization_id
                                    AND location_id     = p_location_id
                                    AND fin_year        = cp_fin_year
                                    AND slno            = nvl(cp_slno,1) );

Line: 975

  SELECT balance
    FROM JAI_CMN_RG_OTH_BALANCES
   WHERE org_unit_id   = ( SELECT org_unit_id
                           FROM JAI_CMN_INVENTORY_ORGS
                          WHERE organization_id = p_organization_id
                            AND location_id     = p_location_id )
     AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
     AND tax_type      = cp_tax_type;

Line: 1075

                                  p_last_updated_by  => -5451134
                               );

Line: 1105

                                  p_last_updated_by  => -5451134
                               );

Line: 1146

                                  p_last_updated_by  => -5451134
                               );

Line: 1177

                                  p_last_updated_by  => -5451134
                               );

Line: 1279

                         p_last_updated_by  NUMBER  )
 IS
 CURSOR cur_next_pla_records IS
 SELECT opening_balance,
        closing_balance,
        nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0) -
        nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0) transaction_amount,
        register_id,
        slno
   FROM JAI_CMN_RG_PLA_TRXS
  WHERE organization_id = p_organization_id
    AND location_id     = p_location_id
    AND ((fin_year      = p_fin_year
           AND slno      >= p_slno ) OR
         ( fin_year > p_fin_year ))
  ORDER BY FIN_YEAR,SLNO
    FOR UPDATE ;

Line: 1301

 SELECT 1
   FROM all_tab_cols
  WHERE table_name = 'JAI_CMN_RG_23AC_II_TRXS'
    AND column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
    AND owner = l_apps_short_name ;    /*added by ssawant*/

Line: 1334

   lv_cursor_str :=  'SELECT '||
                            lv_trans_str||' ,
                            register_id,
                             slno
                       FROM JAI_CMN_RG_23AC_II_TRXS
                      WHERE organization_id ='|| p_organization_id||'
                        AND location_id     ='|| p_location_id||'
                        AND register_type   ='''|| p_register_type||'''
                        AND ((fin_year      ='|| p_fin_year||'
                              AND slno      >='|| p_slno||' ) OR
                              ( fin_year >'|| p_fin_year||' ))
                         ORDER BY fin_year,slno
                     FOR UPDATE ';

Line: 1374

       UPDATE JAI_CMN_RG_23AC_II_TRXS
          SET opening_balance  = ln_opening_balance,
              closing_balance  = ln_closing_balance,
              last_updated_by  = p_last_updated_by,
              last_update_date = sysdate
        WHERE register_id = ln_register_id;

Line: 1387

       UPDATE JAI_CMN_RG_BALANCES
          SET rg23a_balance    = ln_prev_balance,
              last_updated_by  = p_last_updated_by
        WHERE organization_id = p_organization_id
          AND location_id     = p_location_id ;

Line: 1393

       UPDATE JAI_CMN_RG_SLNOS
          SET balance         = ln_prev_balance
        WHERE organization_id = p_organization_id
          AND location_id     = p_location_id
          AND register_type   = 'A' ;

Line: 1401

        UPDATE JAI_CMN_RG_BALANCES
           SET rg23c_balance   = ln_prev_balance
         WHERE organization_id = p_organization_id
           AND location_id     = p_location_id ;

Line: 1406

        UPDATE JAI_CMN_RG_SLNOS
           SET balance         = ln_prev_balance
         WHERE organization_id = p_organization_id
           AND location_id     = p_location_id
           AND register_type   = 'C' ;

Line: 1421

        UPDATE JAI_CMN_RG_PLA_TRXS
           SET opening_balance  = ln_opening_balance,
               closing_balance  = ln_closing_balance,
               last_updated_by  = p_last_updated_by,
               last_update_date = sysdate
         WHERE CURRENT OF cur_next_pla_records;

Line: 1432

      UPDATE JAI_CMN_RG_BALANCES
         SET pla_balance     = ln_prev_balance,
             last_updated_by = p_last_updated_by
       WHERE organization_id = p_organization_id
         AND location_id     = p_location_id ;

Line: 1438

     UPDATE JAI_CMN_RG_SLNOS
        SET balance         = ln_prev_balance
      WHERE organization_id = p_organization_id
        AND location_id     = p_location_id
        AND register_type   = 'PLA' ;

Line: 1458

    UPDATE JAI_CMN_RG_23AC_II_TRXS
       SET other_tax_credit = ( SELECT sum(credit)
                                  FROM JAI_CMN_RG_OTHERS
                                 WHERE source_type = 1
                                   AND source_register_id = p_register_id ),
           other_tax_debit  = ( SELECT sum(debit)
                                  FROM JAI_CMN_RG_OTHERS
                                 WHERE source_type = 1
                                   AND source_register_id = p_register_id )
     WHERE register_id = p_register_id;

Line: 1471

    UPDATE JAI_CMN_RG_PLA_TRXS
       SET other_tax_credit = ( SELECT sum(credit)
                                  FROM JAI_CMN_RG_OTHERS
                                 WHERE source_type = 2
                                   AND source_register_id = p_register_id ),
           other_tax_debit  = ( SELECT sum(debit)
                                  FROM JAI_CMN_RG_OTHERS
                                 WHERE source_type = 2
                                   AND source_register_id = p_register_id )
     WHERE register_id = p_register_id;

Line: 1498

    select slno, count(*) rowcount
    from
      JAI_CMN_RG_PLA_TRXS
    where
      organization_id = p_organization_id and
      location_id     = p_location_id     and
      fin_year        = p_fin_year        and
      trunc(creation_date)    >= gd_date
      group by slno
      having count(*) > 1 ;

Line: 1511

    select  slno
    from
      JAI_CMN_RG_PLA_TRXS
    where
        closing_balance         <> nvl(opening_balance,0)                                               +
                                   ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) )    -
                                   ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) )    AND
        organization_id         = p_organization_id                                                     AND
        location_id             = p_location_id                                                           AND
        fin_year                = p_fin_year                                                            AND
        trunc(creation_date)    >= gd_date
    ORDER BY
        register_id ;

Line: 1527

    select
                rowid                                                                   ,
                slno                                                                    ,
                organization_id                                                         ,
                location_id                                                             ,
                register_id                                                             ,
                fin_year                                                                ,
                opening_balance                                                         ,
                closing_balance                                                         ,
                nvl(other_tax_credit,other_tax_debit)    rg_other_amt                   ,
                transaction_source_num
        FROM
                JAI_CMN_RG_PLA_TRXS
        WHERE
                organization_id = p_organization_id    AND
                location_id     = p_location_id        AND
                fin_year        = p_fin_year           AND
                trunc(creation_date)    >= gd_date
        ORDER BY
                slno   ;

Line: 1554

    select
           nvl(closing_balance,0)
    from
           JAI_CMN_RG_PLA_TRXS
    where
           organization_id = cp_organization_id    AND
           location_id     = cp_location_id        AND
           fin_year        = cp_fin_year           AND
           slno            =
              ( select max(slno)
                from
                  JAI_CMN_RG_PLA_TRXS
                where
                  organization_id = cp_organization_id and
                  location_id     = cp_location_id     and
                  fin_year        = cp_fin_year        and
                  slno            < cp_slno
               );

Line: 1577

    select nvl(closing_balance,0)
    from JAI_CMN_RG_PLA_TRXS
    where organization_id = cp_organization_id
    and   location_id     = cp_location_id
    and   fin_year = cp_fin_year
    and   slno in
                 ( select nvl(max(slno),0)
                   from JAI_CMN_RG_PLA_TRXS
                   where organization_id = cp_organization_id
                   and location_id       = cp_location_id
                   and fin_year          = cp_fin_year
                 );

Line: 1595

    select nvl(sum(credit), sum(debit))
    from JAI_CMN_RG_OTHERS
    where source_register = 'PLA'
    and   source_register_id = cp_source_register_id
    and   source_type        =  2 ;

Line: 1673

                      p_last_updated_by  => -5451134   );

Line: 1723

                        p_last_updated_by  => -5451134   );

Line: 1776

    select slno , count(*) rowcount
    from
      JAI_CMN_RG_23AC_II_TRXS
    where
      organization_id = p_organization_id and
      location_id     = p_location_id  and
      fin_year        = p_fin_year and
      register_type   = p_register_type and
      trunc(creation_date) >= gd_date
      group by slno
      having count(*) > 1 ;

Line: 1790

    select  slno
    from
      JAI_CMN_RG_23AC_II_TRXS
    where
        closing_balance         <> nvl(opening_balance,0)                                               +
                                   ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) )    -
                                   ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) )    AND
        register_type           = p_register_type                                                       AND
        organization_id         = p_organization_id                                                     AND
        location_id             = p_location_id                                                           AND
        fin_year                = p_fin_year                                                            AND
        trunc(creation_date)    >= gd_date
    ORDER BY
        register_id ;

Line: 1807

    select
                rowid                                                                   ,
                slno                                                                    ,
                organization_id                                                         ,
                location_id                                                             ,
                register_id                                                             ,
                register_type                                                           ,
                fin_year                                                                ,
                opening_balance                                                         ,
                closing_balance                                                         ,
                nvl(other_tax_credit,other_tax_debit)    rg_other_amt
        FROM
                JAI_CMN_RG_23AC_II_TRXS
        WHERE
                organization_id = p_organization_id    AND
                location_id     = p_location_id        AND
                fin_year        = p_fin_year           AND
                register_type   = p_register_type      AND
                trunc(creation_date)    >= gd_date
        ORDER BY
                organization_id ,
                location_id     ,
                fin_year        ,
                register_type   ,
                slno   ;

Line: 1840

    select
           nvl(closing_balance,0)
    from
           JAI_CMN_RG_23AC_II_TRXS
    where
           organization_id = cp_organization_id    AND
           location_id     = cp_location_id        AND
           fin_year        = cp_fin_year           AND
           register_type   = cp_register_type      AND
           slno            =
                         ( select max(slno)
                           from
                             JAI_CMN_RG_23AC_II_TRXS
                           where
                             organization_id = cp_organization_id and
                             location_id     = cp_location_id     and
                             fin_year        = cp_fin_year        and
                             register_type   = cp_register_type   and
                             slno            < cp_slno  ) ;

Line: 1864

    select nvl(closing_balance,0)
    from JAI_CMN_RG_23AC_II_TRXS
    where organization_id = cp_organization_id
    and   location_id     = cp_location_id
    and   register_type   = cp_register_type
    and   fin_year = cp_fin_year
    and   slno in
                 ( select nvl(max(slno),0)
                   from JAI_CMN_RG_23AC_II_TRXS
                   where organization_id = cp_organization_id
                   and location_id       = cp_location_id
                   and fin_year          = cp_fin_year
                   and register_type     = cp_register_type);

Line: 1880

    select nvl(sum(credit), sum(debit))
    from JAI_CMN_RG_OTHERS
    where source_register = decode(cp_register_type,'A','RG23A_P2','C','RG23C_P2')
    and   source_register_id = cp_source_register_id
    and   source_type        =  1 ;

Line: 1937

       '    select  slno
           from
             JAI_CMN_RG_23AC_II_TRXS
           where
               closing_balance         <> nvl(opening_balance,0)                                               +
                                          ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0))    -
                                          ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0))    AND
               register_type           = ''' || p_register_type || '''                                                     AND
               organization_id         = ' || p_organization_id  || '                                                    AND
               location_id             = ' || p_location_id      || '                                                     AND
               fin_year                = ' || p_fin_year         || '                                                   AND
               trunc(creation_date)    >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
               and rownum =1 ORDER BY register_id '
               into ln_register_id ;

Line: 1954

              '    select  count(1)
                  from
                    JAI_CMN_RG_23AC_II_TRXS
                  where
                      closing_balance         <> nvl(opening_balance,0)                                               +
                                                 ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) )    -
                                                 ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) )    AND
                      register_type           = ''' || p_register_type || '''                                                     AND
                      organization_id         = ' || p_organization_id  || '                                                    AND
                      location_id             = ' || p_location_id      || '                                                     AND
                      fin_year                = ' || p_fin_year         || '                                                   AND
                      trunc(creation_date)    >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
                      and rownum =1 ORDER BY register_id '
                    into ln_rowcount ;

Line: 2003

                        p_last_updated_by  => -5451134   );

Line: 2051

                        p_last_updated_by  => -5451134   );

Line: 2139

    insert into JAI_TRX_GT
    ( JAI_INFO_N1   ,
      JAI_INFO_N2   ,
      JAI_INFO_V1   ,
      JAI_INFO_N3   ,
      JAI_INFO_N4   ,
      JAI_INFO_V3   ,
      JAI_INFO_N8   ,
      JAI_INFO_N9   ,
      JAI_INFO_N10  ,
      JAI_INFO_V4   ,
      JAI_INFO_D1   ,
      JAI_INFO_V5   ,
      JAI_INFO_N11
    )
    values
    ( p_organization_id   ,
      p_location_id       ,
      p_register_type     ,
      p_fin_year          ,
      p_opening_balance   ,
      p_error_codes       ,
      p_slno              ,
      p_register_id       ,
      p_rowcount          ,
      p_tax_type          ,
      p_date              ,
      p_month             ,
      p_year
    ) ;

Line: 2194

    select closing_balance , slno
    from JAI_CMN_RG_23AC_II_TRXS
    where organization_id = cp_organization_id
    and   location_id     = cp_location_id
    and   register_type   = cp_register_type
    order by fin_year desc , slno desc ;

Line: 2205

    select decode(cp_register_type, 'A', rg23a_balance, 'C', rg23c_balance, 'PLA', pla_balance)
    from JAI_CMN_RG_BALANCES
    where organization_id = cp_organization_id
    and location_id       = cp_location_id ;

Line: 2213

    select closing_balance, slno
    from JAI_CMN_RG_PLA_TRXS
    where organization_id = cp_organization_id
    and   location_id     = cp_location_id
    order by fin_year desc , slno desc ;

Line: 2225

    SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
             - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
      FROM JAI_CMN_RG_PLA_TRXS
     WHERE organization_id = cp_organization_id
       AND location_id     = cp_location_id;

Line: 2238

    select balance , slno
    from JAI_CMN_RG_SLNOS
    where organization_id  = cp_organization_id
    and   location_id      = cp_location_id
    and   register_type    = cp_register_type;

Line: 2249

     select
       sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) pla_cons_amt ,
       sum(other_tax_credit)  pla_oth_amt
     from JAI_CMN_RG_PLA_TRXS
     where
       transaction_source_num is null and
       organization_id = cp_organization_id and
       location_id     = cp_location_id     and
       trunc(creation_date) = cp_creation_date ;

Line: 2263

    select 1
    from all_tab_cols
    where
     table_name = 'JAI_CMN_RG_23AC_II_TRXS'
     and column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
     AND owner = l_apps_short_name ; /*added by ssawant*/

Line: 2276

    select
           nvl(opening_balance,0)
    from
           JAI_CMN_RG_23AC_II_TRXS
    where
           organization_id = cp_organization_id    AND
           location_id     = cp_location_id        AND
           register_type   = cp_register_type      AND
           trunc(creation_date) >= cp_date
           order by fin_year, slno  ;

Line: 2293

    select
            sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0))  credit_amount ,
            sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0))  debit_amount
    from
           JAI_CMN_RG_23AC_II_TRXS
    where
           organization_id = cp_organization_id    AND
           location_id     = cp_location_id        AND
           register_type   = cp_register_type      AND
           trunc(creation_date) >= cp_date  ;

Line: 2309

    select balance
    from JAI_CMN_RG_SLNOS
    where
      organization_id = cp_organization_id    AND
      location_id     = cp_location_id        AND
      register_type   = cp_register_type      ;

Line: 2321

    select count(1)
    from JAI_TRX_GT
    where
      JAI_INFO_N1  = cp_organization_id and
      JAI_INFO_N2  = cp_location_id     and
      JAI_INFO_V1  = cp_register_type ;

Line: 2334

    select count(1)
    from JAI_TRX_GT
    where
      JAI_INFO_N1  = cp_organization_id AND
      JAI_INFO_N2  = cp_location_id     AND
      JAI_INFO_V1  = cp_register_type   AND
      JAI_INFO_V4  = cp_tax_type;

Line: 2347

      SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
              - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
      FROM JAI_CMN_RG_PLA_TRXS
      WHERE organization_id = cp_organization_id
      AND location_id     = cp_location_id
      and trunc(creation_date) < cp_date ;

Line: 2359

     select
             sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0))  credit_amount ,
             sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0))  debit_amount
     from
            JAI_CMN_RG_PLA_TRXS
     where
            organization_id = cp_organization_id    AND
            location_id     = cp_location_id        AND
            trunc(creation_date) >= cp_date  ;

Line: 2371

    SELECT fin_year
      FROM JAI_CMN_FIN_YEARS
     WHERE organization_id     = cp_organization_id
       AND cp_date between fin_year_start_date and fin_year_end_date;

Line: 2381

    SELECT max(slno)
      FROM JAI_CMN_RG_23AC_II_TRXS jrg
     WHERE organization_id       = p_organization_id
       AND location_id           = p_location_id
       AND fin_year              = cp_fin_year
       AND register_type         = p_register_type
       AND trunc(creation_date)  < cp_date
       AND EXISTS ( SELECT 1
                      FROM JAI_CMN_RG_OTHERS
                     WHERE source_type = 1
                       AND source_register_id = jrg.register_id
                       AND tax_type           = cp_tax_type );

Line: 2399

    SELECT sum(credit),sum(debit)
      FROM JAI_CMN_RG_OTHERS
     WHERE source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
       AND source_register_id in ( SELECT register_id
                                     FROM JAI_CMN_RG_23AC_II_TRXS
                                    WHERE organization_id = cp_organization_id
                                      AND location_id     = cp_location_id
                                      AND register_type   = p_register_type
                                      AND trunc(creation_date) >= cp_date)
       AND tax_type = cp_tax_type;

Line: 2415

    SELECT sum(credit),sum(debit)
      FROM JAI_CMN_RG_OTHERS
     WHERE source_register = 'PLA'
       AND source_register_id in ( SELECT register_id
                                     FROM JAI_CMN_RG_PLA_TRXS
                                    WHERE organization_id = cp_organization_id
                                      AND location_id     = cp_location_id
                                      AND trunc(creation_date) >= cp_date)
       AND tax_type = cp_tax_type;

Line: 2431

    SELECT max(slno)
      FROM JAI_CMN_RG_PLA_TRXS jpl
     WHERE organization_id       = p_organization_id
       AND location_id           = p_location_id
       AND fin_year              = cp_fin_year
       AND trunc(creation_date)  < cp_date
       AND EXISTS ( SELECT 1
                      FROM JAI_CMN_RG_OTHERS
                     WHERE source_type        = 2
                       AND source_register_id = jpl.register_id
                       AND tax_type           = cp_tax_type );

Line: 2444

    SELECT closing_balance
      FROM JAI_CMN_RG_OTHERS
     WHERE source_type = 1
       AND tax_type    = cp_tax_type
       AND source_register_id in ( SELECT register_id
                                     FROM JAI_CMN_RG_23AC_II_TRXS
                                    WHERE organization_id = p_organization_id
                                      AND location_id     = p_location_id
                                      AND register_type   = p_register_type
                                      AND fin_year        = cp_fin_year
                                      AND slno            = nvl(cp_slno,1) );

Line: 2457

    SELECT closing_balance
      FROM JAI_CMN_RG_OTHERS
     WHERE source_type = 2
       AND tax_type    = cp_tax_type
       AND source_register_id in ( SELECT register_id
                                     FROM JAI_CMN_RG_PLA_TRXS
                                    WHERE organization_id = p_organization_id
                                      AND location_id     = p_location_id
                                      AND fin_year        = cp_fin_year
                                      AND slno            = nvl(cp_slno,1) );

Line: 2471

    SELECT balance
      FROM JAI_CMN_RG_OTH_BALANCES
     WHERE org_unit_id   = ( SELECT org_unit_id
                             FROM JAI_CMN_INVENTORY_ORGS
                            WHERE organization_id = cp_organization_id
                              AND location_id     = cp_location_id )
       AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
       AND tax_type      = cp_tax_type;

Line: 2571

      for rec in ( select
                    distinct organization_id, location_id, fin_year, register_type
                   from JAI_CMN_RG_23AC_II_TRXS
                   where
                     trunc(creation_date) >= pv_date and
                     register_type   = nvl(p_register_type, register_type) and
                     location_id     = nvl(p_location_id , location_id) and
                     organization_id = nvl(p_organization_id, organization_id)
                   order by register_type, organization_id, location_id, fin_year )
      loop

        -- call to validate records in JAI_CMN_RG_23AC_II_TRXS
        rg23_part_ii_validation(  p_organization_id    => rec.organization_id ,
                                  p_location_id        => rec.location_id ,
                                  p_fin_year           => rec.fin_year ,
                                  p_register_type      => rec.register_type
                         ) ;

Line: 2593

      for rg23_bal_rec in (  select
                               distinct organization_id, location_id, register_type
                             from JAI_CMN_RG_23AC_II_TRXS
                             where
                               register_type   = nvl(p_register_type, register_type) and
                               organization_id = nvl(p_organization_id, organization_id) and
                               location_id     = nvl(p_location_id , location_id)        and
                               trunc(creation_date) >= pv_date
                             order by register_type, organization_id, location_id )
      loop

        validate_period_balances( p_organization_id  =>  rg23_bal_rec.organization_id,
                                  p_location_id      =>  rg23_bal_rec.location_id,
                                  p_register_type    =>  rg23_bal_rec.register_type,
                                  p_date             =>  pv_date
                                 ) ;

Line: 2699

      for rec in ( select
                    distinct organization_id, location_id, fin_year
                   from JAI_CMN_RG_PLA_TRXS
                   where
                     trunc(creation_date) >= pv_date and
                     location_id     = nvl(p_location_id , location_id) and
                     organization_id = nvl(p_organization_id, organization_id)
                   order by organization_id, location_id, fin_year )
      loop

        pla_validation( p_organization_id    => rec.organization_id ,
                 p_location_id        => rec.location_id ,
                 p_fin_year           => rec.fin_year
               ) ;

Line: 2716

      for pla_bal_rec in ( select
                             distinct organization_id, location_id
                           from JAI_CMN_RG_PLA_TRXS
                           where
                             organization_id = nvl(p_organization_id, organization_id) and
                             location_id     = nvl(p_location_id , location_id)         and
                             trunc(creation_date) >= pv_date
                           order by organization_id, location_id
                         )
      loop

        -- call to validate pla cess info
        validate_rg_others
                      (    p_organization_id   =>  pla_bal_rec.organization_id,
                           p_location_id       =>  pla_bal_rec.location_id,
                           p_register_type     =>  'PLA',
                           p_date              =>  pv_date
                      ) ;

Line: 2837

      For cons_rec in ( select
                          distinct organization_id, location_id
                        from JAI_CMN_RG_23AC_II_TRXS
                        where
                          organization_id = nvl(p_organization_id, organization_id) and
                          location_id     = nvl(p_location_id , location_id)        and
                          trunc(creation_date) >= pv_date
                        order by organization_id, location_id
                      )
      loop

        For rg23_cons_rec in (  select
                                  sum(  nvl(dr_basic_ed,0) +nvl(dr_additional_ed,0) +nvl(dr_other_ed,0) ) rg23_cons_amt ,
                                  sum(other_tax_debit)  rg23_oth_amt,
                                  trunc(creation_date) cons_date
                                from JAI_CMN_RG_23AC_II_TRXS
                                where
                                  transaction_source_num is null and
                                  organization_id = cons_rec.organization_id and
                                  location_id     = cons_rec.location_id     and
                                  trunc(creation_date)   >=  pv_date
                                group by trunc(creation_date)
                              )
        loop
          open  pla_cons_amt(cons_rec.organization_id,cons_rec.location_id , rg23_cons_rec.cons_date) ;

Line: 2919

      for rg23_log_rec in (  select
                               distinct organization_id, location_id, register_type
                             from JAI_CMN_RG_23AC_II_TRXS
                             where
                               register_type   = nvl(p_register_type, register_type) and
                               organization_id = nvl(p_organization_id, organization_id) and
                               location_id     = nvl(p_location_id , location_id)        and
                               trunc(creation_date) >= pv_date
                             order by organization_id, location_id, register_type )
      loop

      ln_open_bal      := null ;

Line: 2952

        ' select
            sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0))  credit_amount ,
            sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0))  debit_amount
    from
           JAI_CMN_RG_23AC_II_TRXS
    where
           organization_id = ' || rg23_log_rec.organization_id || ' AND
           location_id     = ' || rg23_log_rec.location_id     || ' AND
           register_type   = ''' || rg23_log_rec.register_type   || ''' AND
           trunc(creation_date) >= ''' ||  pv_date || ''''
           into ln_credit_amount, ln_debit_amount ;

Line: 3258

      for pla_log_rec in (  select
                               distinct organization_id, location_id
                             from JAI_CMN_RG_PLA_TRXS
                             where
                               organization_id = nvl(p_organization_id, organization_id) and
                               location_id     = nvl(p_location_id , location_id)        and
                               trunc(creation_date) >= pv_date
                             order by organization_id, location_id )
      loop

      ln_open_bal      := null ;

Line: 3591

     for rec in ( select * from JAI_TRX_GT)
     loop

       FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(nvl(to_char(rec.JAI_INFO_N1),' '), 17, ' ')|| '|' || RPAD(nvl(to_char(rec.JAI_INFO_N2),' '), 13, ' ') || '|'
       || RPAD(nvl(rec.JAI_INFO_V1,' '), 15, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N3),' '), 10, ' ') || '|' || RPAD(nvl(rec.JAI_INFO_V3,' '), 6, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N8),' '), 8, ' ')
       || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N9),' '), 13, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N10),' '), 10, ' ') || '|'
       ||  RPAD(nvl(rec.JAI_INFO_V4,' '), 23, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_D1),' '), 10, ' ') || '|' || RPAD(nvl(rec.JAI_INFO_V5,' '), 7, ' ') || '|'
       || RPAD(nvl(to_char(rec.JAI_INFO_N11),' '), 6, ' ')  ) ;