APPS.CN_WKSHT_CT_UP_PUB SQL Statements

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

Line: 9

Procedure Create_delete_Wrkhst
   ( p_api_version         IN   NUMBER,
     p_init_msg_list       IN   VARCHAR2,
     p_commit              IN   VARCHAR2,
     p_validation_level    IN   NUMBER,
     x_return_status       OUT NOCOPY  VARCHAR2,
     x_msg_count           OUT NOCOPY  NUMBER,
     x_msg_data            OUT NOCOPY  VARCHAR2,
     p_salesrep_id         IN   NUMBER,
     p_srp_pmt_asgn_id     IN   NUMBER,
     p_payrun_id           IN   NUMBER,
     x_status             OUT NOCOPY  VARCHAR2,
     x_loading_status     OUT NOCOPY  VARCHAR2
     )  IS

   l_api_name         CONSTANT VARCHAR2(30)  := 'Create_delete_Wrkhst';

Line: 32

  SELECT  pw.payment_worksheet_id,
          pw.salesrep_id
    FROM  cn_payment_worksheets pw,
          cn_payruns p,
          cn_srp_pmt_plans_v ppa
   WHERE  ppa.salesrep_id =  p_salesrep_id
     and   ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
     and   p.payrun_id  = p_payrun_id
     and   p.pay_period_id = ppa.period_id
     and pw.salesrep_id = ppa.salesrep_id
     and pw.payrun_id   = p.payrun_id
     and pw.quota_id   IS NULL
     and p.status = 'UNPAID' ;

Line: 46

    SELECT  pw.payment_worksheet_id,
            pw.salesrep_id
    FROM  cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
   WHERE  pw.salesrep_id = p_salesrep_id
     AND p.payrun_id  = p_payrun_id
     AND p.org_id     = ps.org_id
     AND ps.period_id = p.pay_period_id
     AND pw.payrun_id   = p.payrun_id
     AND pw.quota_id  IS NULL
     AND p.status = 'UNPAID'
     AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
                 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
                 AND ppa.salesrep_id = pw.salesrep_id
                 AND ppa.start_date <= ps.end_date
                 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);

Line: 73

   SAVEPOINT    Create_delete_Wrkhst;

Line: 107

   CN_Payment_Worksheet_PVT.Update_Worksheet
  (    p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_worksheet_id    => wksht_recs.payment_worksheet_id,
       p_operation       => 'REFRESH',
       x_status          => x_status,
       x_loading_status  => x_loading_status,
       x_ovn   => l_ovn
       );

Line: 155

      ROLLBACK TO Create_delete_Wrkhst;

Line: 165

      ROLLBACK TO Create_delete_Wrkhst;

Line: 178

      ROLLBACK TO Create_delete_Wrkhst;

Line: 191

END   Create_delete_Wrkhst ;

Line: 223

    SELECT  pw.payment_worksheet_id,
            pw.salesrep_id,
            p.object_version_number
    FROM  cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
   WHERE  pw.salesrep_id = p_salesrep_id
     AND p.payrun_id  = p_payrun_id
     AND ps.period_id = p.pay_period_id
     AND pw.payrun_id = p.payrun_id
     AND ps.org_id    = p.org_id
     AND pw.quota_id  IS NULL
     AND p.status = 'UNPAID'
     AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
                 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
                 AND ppa.salesrep_id = pw.salesrep_id
                 AND ppa.start_date <= ps.end_date
                 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);

Line: 291

     x_loading_status := 'CN_UPDATED';

Line: 293

     CN_SRP_PMT_PLANS_PUB.Update_Srp_Pmt_Plan
     ( p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_old_srp_pmt_plans_rec  =>  oldrec,
       p_srp_pmt_plans_rec  =>  newrec,
       x_loading_status  => x_loading_status );

Line: 305

      if x_loading_status <> 'CN_UPDATED' then
       RAISE fnd_api.g_exc_error;

Line: 316

      CN_Payment_Worksheet_PVT.Update_Worksheet
      (    p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_worksheet_id    => wksht_recs.payment_worksheet_id,
       p_operation       => 'REFRESH',
       x_status          => x_status,
       x_loading_status  => x_loading_status,
       x_ovn             => wksht_recs.object_version_number
       );

Line: 420

    SELECT  pw.payment_worksheet_id,
            pw.salesrep_id,
            p.object_version_number
    FROM  cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
   WHERE  pw.salesrep_id = p_salesrep_id
     AND p.payrun_id  = p_payrun_id
     AND ps.period_id = p.pay_period_id
     AND ps.org_id    = p.org_id
     AND pw.payrun_id   = p.payrun_id
     AND pw.quota_id  IS NULL
     AND p.status = 'UNPAID'
     AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
                 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
                 AND ppa.salesrep_id = pw.salesrep_id
                 AND ppa.start_date <= ps.end_date
                 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);

Line: 469

   x_loading_status := 'CN_DELETED';

Line: 479

   x_loading_status :=  'CN_DELETED';

Line: 481

   CN_Payment_Worksheet_PVT.Delete_Worksheet
    (  p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_worksheet_id    => wksht_recs.payment_worksheet_id,
       x_status          => x_status,
       x_loading_status  => x_loading_status,
       p_validation_only => 'N',
       p_ovn             => wksht_recs.object_version_number);

Line: 495

    if x_loading_status <> 'CN_DELETED' then
       RAISE fnd_api.g_exc_error;

Line: 505

    x_loading_status :=  'CN_DELETED';

Line: 516

     CN_SRP_PMT_PLANS_PUB.Delete_Srp_Pmt_Plan
     ( p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_srp_pmt_plans_rec  =>  newrec,
       x_loading_status  => x_loading_status );

Line: 527

      if x_loading_status <> 'CN_DELETED' then
       RAISE fnd_api.g_exc_error;

Line: 531

     x_loading_status :=  'CN_INSERTED';

Line: 545

    if x_loading_status <> 'CN_INSERTED' then
       RAISE fnd_api.g_exc_error;

Line: 634

  SELECT  pw.payment_worksheet_id,
          pw.salesrep_id
    FROM  cn_payment_worksheets pw,
          cn_payruns p,
          cn_srp_pmt_plans_v ppa
   WHERE  ppa.salesrep_id =  p_salesrep_id
     and   ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
     and   p.payrun_id  = p_payrun_id
     and   p.pay_period_id = ppa.period_id
     and pw.salesrep_id = ppa.salesrep_id
     and pw.payrun_id   = p.payrun_id
     AND pw.quota_id is null
     and p.status = 'UNPAID' ;

Line: 648

    SELECT  pw.payment_worksheet_id,
            pw.salesrep_id,
            p.object_version_number
    FROM  cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
   WHERE  pw.salesrep_id = p_salesrep_id
     AND p.payrun_id  = p_payrun_id
     AND ps.period_id = p.pay_period_id
     AND ps.org_id    = p.org_id
     AND pw.payrun_id = p.payrun_id
     AND pw.quota_id  IS NULL
     AND p.status = 'UNPAID'
     AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
                 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
                 AND ppa.salesrep_id = pw.salesrep_id
                 AND ppa.start_date <= ps.end_date
                 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);

Line: 699

   x_loading_status := 'CN_DELETED';

Line: 709

   x_loading_status :=  'CN_DELETED';

Line: 711

   CN_Payment_Worksheet_PVT.Delete_Worksheet
    (  p_api_version     =>  p_api_version,
       p_init_msg_list   => p_init_msg_list,
       p_commit          => p_commit,
       p_validation_level=> p_validation_level,
       x_return_status   => x_return_status,
       x_msg_count       => x_msg_count,
       x_msg_data        => x_msg_data,
       p_worksheet_id    => wksht_recs.payment_worksheet_id,
       x_status          => x_status,
       x_loading_status  => x_loading_status,
       p_validation_only => 'N',
       p_ovn             => wksht_recs.object_version_number);

Line: 725

    if x_loading_status <> 'CN_DELETED' then
       RAISE fnd_api.g_exc_error;

Line: 735

    x_loading_status :=  'CN_INSERTED';

Line: 758

      if x_loading_status <> 'CN_INSERTED' then
       RAISE fnd_api.g_exc_error;

Line: 762

     x_loading_status :=  'CN_INSERTED';

Line: 776

    if x_loading_status <> 'CN_INSERTED' then
       RAISE fnd_api.g_exc_error;