APPS.CN_WKSHT_GET_PUB SQL Statements

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

Line: 53

     l_select   VARCHAR2(9000);

Line: 55

     l_select1  VARCHAR2(9000) :=
      ' Select /*+ first_rows */ w.payment_worksheet_id,
             s.name salesrep_name,
             s.employee_number employee_number,
             s.salesrep_id,
             s.resource_id,
             s.cost_center  cost_center,
             s.charge_to_cost_center,
             0 pmt_amount_diff,
             nvl(w.pmt_amount_calc,0) + nvl(w.pmt_amount_recovery,0) pmt_amount_earnings,
             nvl(w.pmt_amount_adj,0)  pmt_amount_adj ,
             nvl(w.pmt_amount_adj_rec,0) + nvl(w.pmt_amount_adj_nrec,0) Pmt_amount_adj_rec ,
             nvl(w.pmt_amount_recovery,0) pmt_amount_recovery ,
             nvl(w.pmt_amount_calc,0) + nvl(w.pmt_amount_adj,0) +
             nvl(w.pmt_amount_adj_rec,0) + nvl(w.pmt_amount_adj_nrec,0)
             + nvl(w.pmt_amount_recovery,0) Pmt_amount_total,
             nvl(w.held_amount,0) held_amount,
             lk.meaning status_meaning,
             u.user_name status_by,
             s.assigned_to_user_name  analyst_name,
             w.worksheet_status,
             w.object_version_number,
             p.pay_date,
             p.org_id
       from cn_payment_worksheets w,
            cn_salesreps s,
            cn_payruns p,
            cn_lookups lk,
            fnd_user u
       where s.salesrep_id = w.salesrep_id
       and w.org_id        = s.org_id
       and w.worksheet_status = lk.lookup_code
       and w.payrun_id    = p.payrun_id
       and lk.lookup_type = ''WORKSHEET_STATUS'' and w.quota_id is NULL
       and u.user_id (+) = nvl(w.last_updated_by, w.created_by)
       and  w.payrun_id  = :B1 ';

Line: 99

       SELECT
       DISTINCT re2.user_id
       FROM jtf_rs_group_usages u2,
         jtf_rs_rep_managers m2,
         jtf_rs_resource_extns_vl re2,
       (SELECT DISTINCT m1.resource_id,
  greatest(pr.start_date,m1.start_date_active) start_date,
  least(pr.end_date,Nvl(m1.end_date_active,pr.end_date)) end_date
      FROM jtf_rs_resource_extns re1,
        cn_period_statuses pr, jtf_rs_group_usages u1,
        jtf_rs_rep_managers m1
      WHERE re1.user_id = :B7
            AND pr.period_id
                = ( select pay_period_id from cn_payruns where payrun_id = :B8)
            AND u1.usage = ''COMP_PAYMENT''
            AND ((m1.start_date_active <= pr.end_date) AND
                      (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
                      AND u1.group_id = m1.group_id
      AND m1.resource_id = re1.resource_id
                    AND m1.parent_resource_id = m1.resource_id
                  AND m1.hierarchy_type IN (''MGR_TO_MGR'',''REP_TO_REP'')
                  AND m1.category <> ''TBH''
         ) v3
   WHERE
   u2.usage = ''COMP_PAYMENT''
         AND u2.group_id = m2.group_id
   AND m2.parent_resource_id = v3.resource_id
         AND ((m2.start_date_active <= v3.end_date)
         AND (v3.start_date <= Nvl(m2.end_date_active,v3.start_date)))
         AND m2.category <> ''TBH''
   AND m2.hierarchy_type IN (''MGR_TO_MGR'',''MGR_TO_REP'',''REP_TO_REP'')
         AND m2.resource_id = re2.resource_id ) ' ;

Line: 177

   SELECT status
     FROM cn_payruns WHERE payrun_id = p_payrun_id;

Line: 182

     SELECT 'Y' FROM dual WHERE exists
     (SELECT 1 FROM cn_worksheet_qg_dtls
      WHERE payment_worksheet_id = l_payment_worksheet_id);

Line: 188

     SELECT 'Y' FROM dual WHERE exists
     (SELECT 1
      FROM JTF_NOTES_B WHERE SOURCE_OBJECT_CODE = 'CN_PAYMENT_WORKSHEETS'
      AND SOURCE_OBJECT_ID = l_payment_worksheet_id
      );

Line: 240

      SELECT 1 INTO l_tmp
  FROM cn_payruns pay
  WHERE pay.payrun_id = p_payrun_id
  AND (pay.status <> 'PAID' OR
       (pay.status = 'PAID'
        AND exists
        (SELECT 1
         FROM cn_worksheet_qg_dtls dtls, cn_payment_worksheets wrk
         WHERE dtls.payment_worksheet_id = wrk.payment_worksheet_id
         AND wrk.payrun_id = pay.payrun_id
         AND wrk.salesrep_id = dtls.salesrep_id)
        ));

Line: 263

   SELECT 1 INTO l_tmp FROM dual WHERE exists
     (SELECT 1
      FROM jtf_rs_resource_extns re1,
           cn_period_statuses pr,
           jtf_rs_group_usages u1,
           jtf_rs_rep_managers m1
      WHERE re1.user_id = fnd_global.user_id
      AND (pr.period_id, pr.org_id) = (
         SELECT pay_period_id, org_id
         FROM cn_payruns
         WHERE payrun_id = p_payrun_id)
      AND u1.usage = 'COMP_PAYMENT'
      AND ((m1.start_date_active <= pr.end_date) AND
          (pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
      AND u1.group_id = m1.group_id
      AND m1.resource_id = re1.resource_id
      AND m1.parent_resource_id = m1.resource_id
      AND m1.hierarchy_type IN ('MGR_TO_MGR','REP_TO_REP')
      AND m1.category <> 'TBH');

Line: 298

   l_select := l_select1 ;

Line: 301

      l_select := l_select || ' and ' || l_where1 ;

Line: 303

      l_select := l_select || ' and ' || ' 1 = :B2 ';

Line: 309

      l_select := l_select || ' and ' || l_where2 ;

Line: 311

      l_select := l_select || ' and 1 = :B3 ';

Line: 318

      l_select := l_select || ' and ' || l_where3 ;

Line: 320

      l_select := l_select || ' and 1 = :B4 ';

Line: 324

   l_select := l_select || ' and  (( ';

Line: 355

   l_select   := l_select || l_where ;

Line: 357

     l_select   := l_select || l_where9 ;

Line: 359

     l_select :=   l_select || l_where11 ;

Line: 362

   l_select := l_select || ' ) ' ||  ' ' ||  p_order_by ;

Line: 372

   INSERT INTO my_temp (select_clause1,select_clause2,select_clause3)
     SELECT substrb(l_select,1,2000),substrb(l_select,2001,2000),
     p_payrun_id || '*' || c_salesrep_name ||'*'||  c_employee_number ||'*'||
     c_worksheet_status ||'*'|| c_analyst_name ||'*'||   l_b7 ||'*'||  l_b8
     ||'*'||  l_b9
     FROM dual;

Line: 380

   OPEN wksht_cur FOR l_select using p_payrun_id,
     c_salesrep_name, c_employee_number, c_worksheet_status,
     c_analyst_name,  l_b7, l_b8, l_b9, l_org_id;