APPS.IGI_STP_NET_DOC_PKG SQL Statements

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

Line: 29

   SELECT min(trx_number)
   from igi_stp_candidates
   where stp_id      = p_stp_id
   and application   = p_application
   and user_id       = x_user_id
   and currency_code = x_currency_code ;

Line: 37

   SELECT max(trx_number)
   from igi_stp_candidates
   where stp_id      = p_stp_id
   and application   = p_application
   and user_id       = x_user_id
   and currency_code = x_currency_code;

Line: 45

   SELECT min(a.reference)
   from igi_stp_candidates a
   where a.stp_id      = x_vendor_id
   and a.application   = 'AP'
   and a.user_id       = x_user_id
   and a.currency_code = x_currency_code
   and a.reference in ( select b.reference from igi_stp_candidates b
                        where b.stp_id  = x_customer_id
                          and b.application = 'AR'
                          and b.user_id = x_user_id
                          and b.currency_code = x_currency_code);

Line: 58

   SELECT max(a.reference)
   from igi_stp_candidates a
   where a.stp_id      = x_vendor_id
   and a.application   = 'AP'
   and a.user_id       = x_user_id
   and a.currency_code = x_currency_code
   and a.reference in ( select b.reference from igi_stp_candidates b
                        where b.stp_id  = x_customer_id
                          and b.application = 'AR'
                          and b.user_id = x_user_id
                          and b.currency_code = x_currency_code);

Line: 76

   SELECT distinct p.reference
   FROM igi_stp_candidates p
   WHERE p.application = 'AP'
     AND p.user_id       =  x_user_id
     AND p.stp_id        =  x_vendor_id
     AND p.currency_code =  x_currency_code
     AND p.reference     >= nvl(p_ref_min, l_ref_min)
     AND p.reference     <= nvl(p_ref_max, l_ref_max)
     AND p.reference IN (SELECT distinct r.reference
                         FROM igi_stp_candidates r
                         WHERE application = 'AR'
                         AND r.user_id       = x_user_id
                         AND r.stp_id        = x_customer_id
                         AND r.currency_code = x_currency_code
                         AND r.reference     >= nvl(p_ref_min, l_ref_min)
                         AND r.reference     <= nvl(p_ref_max, l_ref_max)); */

Line: 99

/* Depending on the Netting Type only certain documents are selected either AP and AR, AP or AR */

   UPDATE igi_stp_candidates
   SET process_flag = 'R'
   WHERE user_id = x_user_id
     and stp_id in (x_customer_id, x_vendor_id)
     and currency_code = x_currency_code;

Line: 122

         UPDATE igi_stp_candidates
         SET process_flag = 'A'
         WHERE user_id       =  x_user_id
           and stp_id        =  x_vendor_id
           and currency_code =  x_currency_code
           and application   = 'AP'
           and trx_type      = 'STANDARD'
           and trx_number    >= nvl(x_ap_trx_min, v_ap_trx_min)
           and trx_number    <= nvl(x_ap_trx_max, v_ap_trx_max)
           and process_flag = 'R';

Line: 141

         UPDATE igi_stp_candidates
         SET process_flag = 'A'
         WHERE user_id       =  x_user_id
           and stp_id        =  x_customer_id
           and currency_code =  x_currency_code
           and application   = 'AR'
           and trx_type      = 'INV'
           and trx_number    >= nvl(x_ar_trx_min, v_ar_trx_min)
           and trx_number    <= nvl(x_ar_trx_max, v_ar_trx_max)
           and process_flag = 'R';

Line: 162

             UPDATE igi_stp_candidates
               SET process_flag = 'A'
-- Bug 1322996
--             SET process_flag = 'S'
--               , package_num  = x_counter
             WHERE user_id       =  x_user_id
               and currency_code =  x_currency_code
               and stp_id in ( x_customer_id, x_vendor_id)
               and trx_type in ('STANDARD', 'INV')
               and reference     =  rec_match.reference
               and process_flag = 'R';

Line: 186

          UPDATE igi_stp_candidates
          SET process_flag = 'A'
          WHERE user_id       =  x_user_id
            and stp_id        =  x_vendor_id
            and currency_code =  x_currency_code
            and application   = 'AP'
            and trx_type      = decode(x_type, 6,'CREDIT', 'STANDARD')
            and trx_number    >= nvl(x_ap_trx_min, v_ap_trx_min)
            and trx_number    <= nvl(x_ap_trx_max, v_ap_trx_max)
            and process_flag = 'R';

Line: 207

          UPDATE igi_stp_candidates
          SET process_flag    = 'A'
          WHERE user_id       =  x_user_id
            and stp_id        =  x_customer_id
            and currency_code =  x_currency_code
            and application   = 'AR'
            and trx_type      = 'CM'
            and trx_number    >= nvl(x_ar_trx_min, v_ar_trx_min)
            and trx_number    <= nvl(x_ar_trx_max, v_ar_trx_max)
            and process_flag = 'R';

Line: 222

PROCEDURE Update_Candidates (x_type        VARCHAR2,
                             x_batch_id    NUMBER,
                             x_package_id  NUMBER,
                             x_org_id      number) is

   x_user_id NUMBER := fnd_global.user_id;

Line: 232

   UPDATE igi_stp_candidates_all
   SET batch_id = x_batch_id
     , netting_trx_type_id = x_type
     , package_id = x_package_id
   WHERE user_id = x_user_id
   AND process_flag = 'S'
   AND batch_id = -1
   AND org_id = x_org_id;

Line: 247

END Update_Candidates;