APPS.PN_VAR_NATURAL_BP_PKG SQL Statements

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

Line: 63

    SELECT bkhd_default_id, natural_break_rate
    FROM pn_var_bkhd_defaults_all
    WHERE var_rent_id   = p_var_rent_id
    AND break_type      = 'NATURAL';

Line: 73

    SELECT NVL(bkpt_rate,p_bkhd_def_rate) bkpt_rate
    FROM   pn_var_bkdt_defaults_all
    WHERE  bkhd_default_id = p_bkhd_def_id
    AND    p_bkhd_def_st_date <= bkdt_end_date
    AND    p_bkhd_def_end_date >= bkdt_start_date;

Line: 82

    SELECT period_id, var_rent_id
    FROM pn_var_lines_all
    WHERE line_item_id = p_line_item_id;

Line: 88

    select line_item_id
    from pn_var_lines_all
    where line_default_id IN (select line_default_id
                              from pn_var_bkhd_defaults_all
                              where bkhd_default_id = p_bkhd_default_id);

Line: 109

     SELECT lease_id
     INTO l_lease_id
     FROM pn_var_rents_all
     WHERE var_rent_id = l_var_rent_id;

Line: 114

     SELECT reptg_freq_code
     INTO l_reporting_freq
     FROM pn_var_rent_dates_all
     WHERE var_rent_id = p_var_rent_id;

Line: 159

        DELETE FROM pn_var_bkpts_det_all
        WHERE  var_rent_id = p_var_rent_id
        AND    bkpt_header_id IN (SELECT bkpt_header_id
                                  FROM   pn_var_bkpts_head_all
                                  WHERE  bkhd_default_id = l_head_dflt_id);

Line: 165

        DELETE FROM PN_VAR_BKDT_DEFAULTS_ALL
        WHERE bkhd_default_id = l_head_dflt_id;

Line: 168

        DELETE FROM pn_var_bkpts_head_all
        WHERE  bkhd_default_id = l_head_dflt_id;

Line: 187

              PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW(x_rowid                  => l_rowId,
                                                  x_bkdt_default_id        => l_bkpt_default_Id,
                                                  x_bkdt_detail_num        => l_bkpt_default_Num,
                                                  x_bkhd_default_id        => l_head_dflt_id,
                                                  x_bkdt_start_date        => l_start_date,
                                                  x_bkdt_end_date          => l_end_date,
                                                  x_period_bkpt_vol_start  => l_period_bkpt_vol,
                                                  x_period_bkpt_vol_end    => null,
                                                  x_group_bkpt_vol_start   => l_group_bkpt_vol,
                                                  x_group_bkpt_vol_end     => null,
                                                  x_bkpt_rate              => NVL(l_bkpt_rate,l_natural_break_rate),
                                                  x_processed_flag         => null,
                                                  x_var_rent_id            => p_var_rent_id,
                                                  x_creation_date          => SYSDATE,
                                                  x_created_by             => NVL (FND_PROFILE.VALUE ('USER_ID'), 0),
                                                  x_last_update_date       => SYSDATE,
                                                  x_last_updated_by        => NVL (FND_PROFILE.VALUE ('USER_ID'), 0),
                                                  x_last_update_login      => NVL (FND_PROFILE.VALUE ('LOGIN_ID'), 0),
                                                  x_org_id                 => NVL (FND_PROFILE.VALUE ('ORG_ID'), 0 ),
                                                  x_annual_basis_amount    => l_actual_amount,
                                                  x_attribute_category     => NULL,
                                                  x_attribute1             => NULL,
                                                  x_attribute2             => NULL,
                                                  x_attribute3             => NULL,
                                                  x_attribute4             => NULL,
                                                  x_attribute5             => NULL,
                                                  x_attribute6             => NULL,
                                                  x_attribute7             => NULL,
                                                  x_attribute8             => NULL,
                                                  x_attribute9             => NULL,
                                                  x_attribute10            => NULL,
                                                  x_attribute11            => NULL,
                                                  x_attribute12            => NULL,
                                                  x_attribute13            => NULL,
                                                  x_attribute14            => NULL,
                                                  x_attribute15            => NULL);

Line: 227

                /*DBMS_OUTPUT.PUT_LINE(SUBSTR('Error while PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW - '||
                                     TO_CHAR(SQLCODE)||' : '||SQLERRM, 1, 244));*/

Line: 314

       SELECT bkhd_start_date,
              bkhd_end_date,
              bkhd_default_id,
              natural_break_rate
       FROM   pn_var_bkhd_defaults_all
       WHERE  bkhd_default_id = p_head_dflt_id
       AND    break_type = 'NATURAL'
       ORDER BY bkhd_start_date;

Line: 333

       SELECT DISTINCT start_date FROM(
          SELECT distinct GREATEST(start_date, p_bkhd_start_date) start_date
          FROM pn_payment_terms_all
          WHERE lease_id = p_lease_id
          AND include_in_var_rent IN  ('BASETERM','INCLUDE_RI')
          AND index_period_id IS NULL
          AND p_bkhd_start_date <= end_date
          AND p_bkhd_end_date >= start_date
       UNION
          SELECT distinct (LEAST(end_date, p_bkhd_end_date) + 1) start_date
          FROM pn_payment_terms_all
          WHERE lease_id = p_lease_id
          AND include_in_var_rent IN  ('BASETERM','INCLUDE_RI')
          AND index_period_id IS NULL
          AND p_bkhd_start_date <= end_date
          AND p_bkhd_end_date >= start_date
          AND (LEAST(end_date, p_bkhd_end_date) + 1) <= p_bkhd_end_date
       UNION
          SELECT distinct GREATEST(start_date, p_bkhd_start_date) start_date
          FROM pn_payment_terms_all
          WHERE lease_id = p_lease_id
          AND include_in_var_rent IN  ('INCLUDE_RI')
          AND index_period_id IS NOT NULL
          AND status = 'APPROVED'
          AND p_bkhd_start_date <= end_date
          AND p_bkhd_end_date >= start_date
       UNION
          SELECT distinct (LEAST(end_date, p_bkhd_end_date) + 1) start_date
          FROM pn_payment_terms_all
          WHERE lease_id = p_lease_id
          AND include_in_var_rent IN  ('INCLUDE_RI')
          AND index_period_id IS NOT NULL
          AND status = 'APPROVED'
          AND p_bkhd_start_date <= end_date
          AND p_bkhd_end_date >= start_date
          AND (LEAST(end_date, p_bkhd_end_date) + 1) <= p_bkhd_end_date
       UNION
          SELECT p_bkhd_start_date start_date
          FROM DUAL
       UNION
          SELECT distinct bkdt_start_date start_date
          FROM pn_var_bkdt_defaults_all
          WHERE bkhd_default_id = p_bkhd_def_id
          AND bkpt_rate <> p_bkpt_rate
       UNION
          SELECT distinct (bkdt_end_date +1) start_date
          FROM pn_var_bkdt_defaults_all
          WHERE bkhd_default_id = p_bkhd_def_id
          AND bkpt_rate <> p_bkpt_rate
          )
       WHERE start_date <= p_bkhd_end_date
       ORDER BY start_date;

Line: 396

     SELECT DISTINCT * from (
        SELECT start_date, end_date, frequency_code, SUM(actual_amount) actual_amount
        FROM pn_payment_terms_all
        WHERE lease_id = p_lease_id
        AND include_in_var_rent in ('BASETERM','INCLUDE_RI')
        AND index_period_id IS NULL
        AND p_det_st_dt <= end_date
        AND p_det_end_date >= start_date
        GROUP by start_date, end_date, frequency_code, actual_amount
     UNION
        SELECT start_date, end_date, frequency_code, SUM(actual_amount) actual_amount
        FROM pn_payment_terms_all
        WHERE lease_id = p_lease_id
        AND include_in_var_rent in ('INCLUDE_RI')
        AND index_period_id IS NOT NULL
        AND status = 'APPROVED'
        AND p_det_st_dt <= end_date
        AND p_det_end_date >= start_date
        GROUP by start_date, end_date, frequency_code, actual_amount);

Line: 428

             SELECT lease_id
             INTO l_lease_id
             FROM pn_var_rents_all
             WHERE var_rent_id = p_var_rent_id;

Line: 445

       l_bkpt_rec.DELETE;

Line: 571

l_update_count  NUMBER           :=0;

Line: 581

   SELECT distinct lease_id
   FROM
   (
   SELECT lease_id
   FROM pn_leases_all
   WHERE location_id in (SELECT location_id
                         FROM   pn_locations_all loc
                         START with loc.location_id in (SELECT location_id
                                                        FROM pn_locations_all
                                                        WHERE property_id = p_prop_id)
                         CONNECT by prior loc.location_id = parent_location_id)
   UNION
   SELECT lease_id
   FROM pn_tenancies_all
   WHERE location_id in (SELECT location_id
                         FROM   pn_locations_all loc
                         START with loc.location_id in (SELECT location_id
                                                        FROM pn_locations_all
                                                        WHERE property_id = p_prop_id)
                         CONNECT by prior loc.location_id = parent_location_id)
   );

Line: 605

   SELECT lease_id from
   (SELECT lease_id
    FROM pn_tenancies_all
    WHERE location_id = p_loc_id
    UNION
    SELECT lease_id
    FROM pn_leases_all
    WHERE location_id = p_loc_id
    );

Line: 618

SELECT lease_id, rent_num
FROM PN_VAR_RENTS_ALL
WHERE var_rent_id = p_var_rent_id;

Line: 624

   SELECT var_rent_id,rent_num
   FROM pn_var_rents_All
   WHERE lease_id = p_lease_id
   ORDER BY var_rent_id DESC;

Line: 631

   SELECT period_id, start_date, end_date
   FROM pn_var_periods_all
   WHERE var_rent_id = p_var_rent_id;

Line: 638

   SELECT line_item_id, sales_type_code, item_category_code
   FROM pn_var_lines_all
   WHERE period_id = p_period_id;

Line: 645

   SELECT bkpt_header_id, base_rent, natural_break_rate
   FROM pn_var_bkpts_head_all
   WHERE line_item_id = p_line_item_id
   AND break_type = 'NATURAL'
   AND base_rent IS NOT NULL;

Line: 654

   SELECT fnd1.meaning
   FROM fnd_lookups fnd1
   WHERE lookup_type='PN_SALES_CHANNEL'
   AND lookup_code = p_sales_code
   AND sysdate between
       nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
   AND enabled_flag='Y';

Line: 665

   SELECT fnd2.meaning
   FROM fnd_lookups fnd2
   WHERE lookup_type='PN_ITEM_CATEGORY'
   AND lookup_code = p_item_code
   AND sysdate between
       nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
   AND enabled_flag='Y';

Line: 688

l_var_lease_tab.DELETE;

Line: 699

   l_lease_tab.DELETE;

Line: 859

                  UPDATE PN_VAR_BKPTS_HEAD_ALL
                  SET break_type = 'ARTIFICIAL'
                    , natural_break_rate = NULL
                  WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;

Line: 864

                  UPDATE PN_VAR_BKPTS_HEAD_ALL
                  SET breakpoint_type = 'FLAT'
                  WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;

Line: 868

                  UPDATE PN_VAR_BKPTS_DET_ALL
                  SET ANNUAL_BASIS_AMOUNT = NULL
                  WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;

Line: 872

                  l_update_count := l_update_count + 1;

Line: 889

   IF p_mode = 'U' and l_update_count > 0 THEN
      pnp_debug_pkg.log(l_update_count||' records updated' );