APPS.CN_UPG_PMT_TRXNS_PKG SQL Statements

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

Line: 55

   PROCEDURE Update_Commlines_WRK (
                  X_errbuf     out NOCOPY varchar2,
                  X_retcode    out NOCOPY varchar2,
                  X_batch_size  in number,
                  X_Worker_Id   in number,
                  X_Num_Workers in number)
   IS

      l_worker_id  number;

Line: 66

      l_update_name varchar2(30) := 'CNUPMTCL.5';

Line: 88

     fnd_file.put_line(FND_FILE.LOG, 'Entering Update_Commlines_WRK ');

Line: 108

           ad_parallel_updates_pkg.initialize_rowid_range(
                    ad_parallel_updates_pkg.ROWID_RANGE,
                    l_table_owner,
                    l_table_name,
                    l_update_name,
                    X_worker_id,
                    X_num_workers,
                    X_batch_size, 0);

Line: 117

           ad_parallel_updates_pkg.get_rowid_range(
                    l_start_rowid,
                    l_end_rowid,
                    l_any_rows_to_process,
                    X_batch_size,
                    TRUE);

Line: 133

              update /*+ rowid(CL) */  cn_commission_lines_all cl
              set posting_status = 'UNPOSTED',
                  last_update_date = sysdate
              where posting_status is NULL
              and cl.rowid between l_start_rowid and l_end_rowid;

Line: 141

              ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed, l_end_rowid);

Line: 147

              ad_parallel_updates_pkg.get_rowid_range(
                    l_start_rowid,
                    l_end_rowid,
                    l_any_rows_to_process,
                    X_batch_size,
                    FALSE);

Line: 163

   END Update_Commlines_WRK;

Line: 170

   PROCEDURE Update_Pmt_Trxns_WRK (
                  X_errbuf     out NOCOPY varchar2,
                  X_retcode    out NOCOPY varchar2,
                  X_batch_size  in number,
                  X_Worker_Id   in number,
                  X_Num_Workers in number)
   IS
      l_worker_id  number;

Line: 180

      l_update_name varchar2(30) := 'CNUPPMTRX.102';

Line: 201

     fnd_file.put_line(FND_FILE.LOG, 'Entering Update_Pmt_Trxns_WRK  ');

Line: 221

           ad_parallel_updates_pkg.initialize_rowid_range(
                    ad_parallel_updates_pkg.ROWID_RANGE,
                    l_table_owner,
                    l_table_name,
                    l_update_name,
                    X_worker_id,
                    X_num_workers,
                    X_batch_size, 0);

Line: 230

           ad_parallel_updates_pkg.get_rowid_range(
                    l_start_rowid,
                    l_end_rowid,
                    l_any_rows_to_process,
                    X_batch_size,
                    TRUE);

Line: 248

      UPDATE
      (
        SELECT /*+ rowid(pmt) use_nl(ch) */
            pmt.processed_date,
            pmt.last_update_login,
            pmt.last_update_date,
            CASE
            WHEN  (ch.commission_header_id IS NOT NULL) THEN ch.processed_date
            ELSE  (
                  select nvl(greatest(least(p.end_date,
                                              nvl(
                                                 (select -- return value if date is in period
                                                    case
                                                    when pln.end_date between p.start_date and p.end_date then pln.end_date
                                                    else null
                                                    end
                                                    from cn_srp_plan_assigns_all pln
                                                    where srp_plan_assign_id =
                                                      (select srp_plan_assign_id
                                                         from cn_srp_period_quotas_all
                                                        where salesrep_id = pmt.credited_salesrep_id
                                                          and period_id   = pmt.pay_period_id
                                                          and quota_id    = pmt.quota_id
                                                          and org_id      = pmt.org_id
                                                          and rownum=1)
                                                   )
                                               ,p.start_date),
                                              nvl(q.end_date,p.end_date)
                                             ),
                                           p.start_date
                                     ), p.start_date
                           )
                  from  cn_period_statuses_all p, cn_quotas_all q
                  where p.period_id   = pmt.pay_period_id
                    and q.quota_id(+) = pmt.quota_id
                    and q.org_id(+)   = p.org_id
                    and p.org_id      = pmt.org_id
              )
        END   AS new_processed_date
        FROM  cn_payment_transactions_all pmt, cn_commission_headers_all ch
        WHERE pmt.rowid BETWEEN l_start_rowid and l_end_rowid
        AND   pmt.commission_header_id = ch.commission_header_id (+)
        AND   pmt.org_id  = ch.org_id (+)
      ) SET   processed_date    = new_processed_date,
              last_update_login = -98989898,
              last_update_date  = sysdate;

Line: 300

    ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);

Line: 303

    ad_parallel_updates_pkg.get_rowid_range(
        l_start_rowid,
        l_end_rowid,
        l_any_rows_to_process,
        X_batch_size,
        FALSE);

Line: 319

   END Update_Pmt_Trxns_WRK;