APPS.PN_VAR_RENTS_PKG SQL Statements

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

Line: 10

procedure INSERT_ROW (
   X_ROWID                 in out NOCOPY VARCHAR2,
   X_VAR_RENT_ID           in out NOCOPY NUMBER,
   X_RENT_NUM              in out NOCOPY VARCHAR2,
   X_LEASE_ID              in NUMBER,
   X_LOCATION_ID           in NUMBER,
   X_PRORATION_DAYS        in NUMBER,
   X_PURPOSE_CODE          in VARCHAR2,
   X_TYPE_CODE             in VARCHAR2,
   X_COMMENCEMENT_DATE     in DATE,
   X_TERMINATION_DATE      in DATE,
   X_ABSTRACTED_BY_USER    in NUMBER,
   X_CUMULATIVE_VOL        in VARCHAR2,
   X_ACCRUAL               in VARCHAR2,
   X_UOM_CODE              in VARCHAR2,
   --X_ROUNDING            in VARCHAR2,
   X_INVOICE_ON            in VARCHAR2,
   X_NEGATIVE_RENT         in VARCHAR2,
   X_TERM_TEMPLATE_ID      in NUMBER,
  -- codev  X_ABATEMENT_AMOUNT      in NUMBER,
   X_ATTRIBUTE_CATEGORY    in VARCHAR2,
   X_ATTRIBUTE1            in VARCHAR2,
   X_ATTRIBUTE2            in VARCHAR2,
   X_ATTRIBUTE3            in VARCHAR2,
   X_ATTRIBUTE4            in VARCHAR2,
   X_ATTRIBUTE5            in VARCHAR2,
   X_ATTRIBUTE6            in VARCHAR2,
   X_ATTRIBUTE7            in VARCHAR2,
   X_ATTRIBUTE8            in VARCHAR2,
   X_ATTRIBUTE9            in VARCHAR2,
   X_ATTRIBUTE10           in VARCHAR2,
   X_ATTRIBUTE11           in VARCHAR2,
   X_ATTRIBUTE12           in VARCHAR2,
   X_ATTRIBUTE13           in VARCHAR2,
   X_ATTRIBUTE14           in VARCHAR2,
   X_ATTRIBUTE15           in VARCHAR2,
   X_ORG_ID                in NUMBER,
   X_CREATION_DATE         in DATE,
   X_CREATED_BY            in NUMBER,
   X_LAST_UPDATE_DATE      in DATE,
   X_LAST_UPDATED_BY       in NUMBER,
   X_LAST_UPDATE_LOGIN     in NUMBER,
   X_CURRENCY_CODE         in VARCHAR2,
   X_AGREEMENT_TEMPLATE_ID in NUMBER,
   X_PRORATION_RULE        in VARCHAR2,
   X_CHG_CAL_VAR_RENT_ID   in NUMBER
   )
IS

   CURSOR var_rents IS
      SELECT ROWID
      FROM   PN_VAR_RENTS_ALL
      WHERE  VAR_RENT_ID = X_VAR_RENT_ID ;

Line: 65

     SELECT org_id FROM pn_leases_all WHERE lease_id = x_lease_id;

Line: 73

   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.INSERT_ROW (+)');

Line: 79

      SELECT  pn_var_rents_s.nextval
      INTO    X_VAR_RENT_ID
      FROM    dual;

Line: 110

   INSERT INTO PN_VAR_RENTS_ALL
   (
      VAR_RENT_ID,
      RENT_NUM,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      LEASE_ID,
      LOCATION_ID,
      PRORATION_DAYS,
      PURPOSE_CODE,
      TYPE_CODE,
      COMMENCEMENT_DATE,
      TERMINATION_DATE,
      ABSTRACTED_BY_USER,
      CUMULATIVE_VOL,
      ACCRUAL,
      UOM_CODE,
      --ROUNDING,
      INVOICE_ON,
      NEGATIVE_RENT,
      TERM_TEMPLATE_ID,
     -- codev  ABATEMENT_AMOUNT,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      ORG_ID,
      CURRENCY_CODE,
      AGREEMENT_TEMPLATE_ID,
      PRORATION_RULE,
      CHG_CAL_VAR_RENT_ID
   )
   VALUES
   (
      X_VAR_RENT_ID,
      X_RENT_NUM,
      X_LAST_UPDATE_DATE,
      X_LAST_UPDATED_BY,
      X_CREATION_DATE,
      X_CREATED_BY,
      X_LAST_UPDATE_LOGIN,
      X_LEASE_ID,
      X_LOCATION_ID,
      X_PRORATION_DAYS,
      X_PURPOSE_CODE,
      X_TYPE_CODE,
      X_COMMENCEMENT_DATE,
      X_TERMINATION_DATE,
      X_ABSTRACTED_BY_USER,
      X_CUMULATIVE_VOL,
      X_ACCRUAL,
      X_UOM_CODE,
      --X_ROUNDING,
      X_INVOICE_ON,
      X_NEGATIVE_RENT,
      X_TERM_TEMPLATE_ID,
    -- codev  X_ABATEMENT_AMOUNT,
      X_ATTRIBUTE_CATEGORY,
      X_ATTRIBUTE1,
      X_ATTRIBUTE2,
      X_ATTRIBUTE3,
      X_ATTRIBUTE4,
      X_ATTRIBUTE5,
      X_ATTRIBUTE6,
      X_ATTRIBUTE7,
      X_ATTRIBUTE8,
      X_ATTRIBUTE9,
      X_ATTRIBUTE10,
      X_ATTRIBUTE11,
      X_ATTRIBUTE12,
      X_ATTRIBUTE13,
      X_ATTRIBUTE14,
      X_ATTRIBUTE15,
      l_org_id,
      X_CURRENCY_CODE,
      X_AGREEMENT_TEMPLATE_ID,
      X_PRORATION_RULE,
      X_CHG_CAL_VAR_RENT_ID
   );

Line: 213

   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.INSERT_ROW (-)');

Line: 215

END INSERT_ROW;

Line: 267

      SELECT *
      FROM PN_VAR_RENTS_ALL
      WHERE VAR_RENT_ID = X_VAR_RENT_ID
      FOR UPDATE OF VAR_RENT_ID NOWAIT;

Line: 514

procedure UPDATE_ROW
(
   X_VAR_RENT_ID              IN NUMBER,
   X_RENT_NUM                 IN VARCHAR2,
   X_LEASE_ID                 IN NUMBER,
   X_LOCATION_ID              IN NUMBER,
   X_PRORATION_DAYS           IN NUMBER,
   X_PURPOSE_CODE             IN VARCHAR2,
   X_TYPE_CODE                IN VARCHAR2,
   X_COMMENCEMENT_DATE        IN DATE,
   X_TERMINATION_DATE         IN DATE,
   X_ABSTRACTED_BY_USER       IN NUMBER,
   X_CUMULATIVE_VOL           IN VARCHAR2,
   X_ACCRUAL                  IN VARCHAR2,
   X_UOM_CODE                 IN VARCHAR2,
   --X_ROUNDING               IN VARCHAR2,
   X_INVOICE_ON               IN VARCHAR2,
   X_NEGATIVE_RENT            IN VARCHAR2,
   X_TERM_TEMPLATE_ID         IN NUMBER,
  -- codev  X_ABATEMENT_AMOUNT         IN NUMBER,
   X_ATTRIBUTE_CATEGORY       IN VARCHAR2,
   X_ATTRIBUTE1               IN VARCHAR2,
   X_ATTRIBUTE2               IN VARCHAR2,
   X_ATTRIBUTE3               IN VARCHAR2,
   X_ATTRIBUTE4               IN VARCHAR2,
   X_ATTRIBUTE5               IN VARCHAR2,
   X_ATTRIBUTE6               IN VARCHAR2,
   X_ATTRIBUTE7               IN VARCHAR2,
   X_ATTRIBUTE8               IN VARCHAR2,
   X_ATTRIBUTE9               IN VARCHAR2,
   X_ATTRIBUTE10              IN VARCHAR2,
   X_ATTRIBUTE11              IN VARCHAR2,
   X_ATTRIBUTE12              IN VARCHAR2,
   X_ATTRIBUTE13              IN VARCHAR2,
   X_ATTRIBUTE14              IN VARCHAR2,
   X_ATTRIBUTE15              IN VARCHAR2,
   X_LAST_UPDATE_DATE         IN DATE,
   X_LAST_UPDATED_BY          IN NUMBER,
   X_LAST_UPDATE_LOGIN        IN NUMBER,
   X_CURRENCY_CODE            IN VARCHAR2,
   X_AGREEMENT_TEMPLATE_ID    IN NUMBER,
   X_PRORATION_RULE           IN VARCHAR2,
   X_CHG_CAL_VAR_RENT_ID      in NUMBER
)
IS
   l_return_status         VARCHAR2(30)    := NULL;

Line: 563

   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.UPDATE_ROW (+)');

Line: 568

   SELECT  org_id
   INTO    l_org_id
   FROM    PN_VAR_RENTS_ALL      bkdetails
   WHERE VAR_RENT_ID    = X_VAR_RENT_ID;

Line: 585

   UPDATE PN_VAR_RENTS_ALL
   SET
      VAR_RENT_ID             = X_VAR_RENT_ID,
      RENT_NUM                = X_RENT_NUM,
      LEASE_ID                = X_LEASE_ID,
      LOCATION_ID             = X_LOCATION_ID,
      PRORATION_DAYS          = X_PRORATION_DAYS,
      PURPOSE_CODE            = X_PURPOSE_CODE,
      TYPE_CODE               = X_TYPE_CODE,
      COMMENCEMENT_DATE       = X_COMMENCEMENT_DATE,
      TERMINATION_DATE        = X_TERMINATION_DATE,
      ABSTRACTED_BY_USER      = X_ABSTRACTED_BY_USER,
      CUMULATIVE_VOL          = X_CUMULATIVE_VOL,
      ACCRUAL                 = X_ACCRUAL,
      UOM_CODE                = X_UOM_CODE,
      --ROUNDING              = X_ROUNDING,
      INVOICE_ON              = X_INVOICE_ON,
      NEGATIVE_RENT           = X_NEGATIVE_RENT,
      TERM_TEMPLATE_ID        = X_TERM_TEMPLATE_ID,
   -- codev   ABATEMENT_AMOUNT        = X_ABATEMENT_AMOUNT,
      ATTRIBUTE_CATEGORY      = X_ATTRIBUTE_CATEGORY,
      ATTRIBUTE1              = X_ATTRIBUTE1,
      ATTRIBUTE2              = X_ATTRIBUTE2,
      ATTRIBUTE3              = X_ATTRIBUTE3,
      ATTRIBUTE4              = X_ATTRIBUTE4,
      ATTRIBUTE5              = X_ATTRIBUTE5,
      ATTRIBUTE6              = X_ATTRIBUTE6,
      ATTRIBUTE7              = X_ATTRIBUTE7,
      ATTRIBUTE8              = X_ATTRIBUTE8,
      ATTRIBUTE9              = X_ATTRIBUTE9,
      ATTRIBUTE10             = X_ATTRIBUTE10,
      ATTRIBUTE11             = X_ATTRIBUTE11,
      ATTRIBUTE12             = X_ATTRIBUTE12,
      ATTRIBUTE13             = X_ATTRIBUTE13,
      ATTRIBUTE14             = X_ATTRIBUTE14,
      ATTRIBUTE15             = X_ATTRIBUTE15,
      LAST_UPDATE_DATE        = X_LAST_UPDATE_DATE,
      LAST_UPDATED_BY         = X_LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN       = X_LAST_UPDATE_LOGIN,
      CURRENCY_CODE           = X_CURRENCY_CODE,
      AGREEMENT_TEMPLATE_ID   = X_AGREEMENT_TEMPLATE_ID,
      PRORATION_RULE          = X_PRORATION_RULE,
      CHG_CAL_VAR_RENT_ID   = X_CHG_CAL_VAR_RENT_ID


   WHERE VAR_RENT_ID = X_VAR_RENT_ID
   ;

Line: 637

   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.UPDATE_ROW (-)');

Line: 639

END UPDATE_ROW;

Line: 649

procedure DELETE_ROW
(
   X_VAR_RENT_ID in NUMBER
)
IS
BEGIN
   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.DELETE_ROW (+)');

Line: 657

   DELETE FROM PN_VAR_RENTS_ALL
   WHERE VAR_RENT_ID = X_VAR_RENT_ID;

Line: 664

   PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.DELETE_ROW (-)');

Line: 666

END DELETE_ROW;

Line: 688

   SELECT  1
   INTO    l_dummy
   FROM    dual
   WHERE   not exists
   (
      SELECT  1
      FROM    pn_var_rents_all   pnvr
      WHERE   pnvr.rent_num   = x_rent_num
      AND ((x_var_rent_id    is null) or
         (pnvr.var_rent_id  <> x_var_rent_id))
      AND  org_id = x_org_id
   );

Line: 738

   pn_var_rents_pkg.insert_row (
      X_ROWID               => l_rowid,
      X_VAR_RENT_ID         => l_var_rent_id ,
      X_RENT_NUM            => l_var_rent_num,
      X_LEASE_ID            => p_pn_var_rents_Rec.lease_id,
      X_LOCATION_ID         => p_pn_var_rents_rec.location_id,
      X_CHG_CAL_VAR_RENT_ID => p_pn_var_rents_rec.chg_cal_var_rent_id,
      X_PRORATION_DAYS      => p_pn_var_rents_Rec.proration_days,
      X_PURPOSE_CODE        => p_pn_var_rents_rec.purpose_code,
      X_TYPE_CODE           => p_pn_var_rents_rec.type_code,
      X_COMMENCEMENT_DATE   => p_pn_var_rents_rec.commencement_date,
      X_TERMINATION_DATE    => p_pn_var_rents_rec.termination_date,
      X_ABSTRACTED_BY_USER  => p_pn_var_rents_rec.abstracted_by_user,
      X_CUMULATIVE_VOL      => p_pn_var_rents_rec.cumulative_vol,
      X_ACCRUAL             => p_pn_var_rents_rec.accrual,
      X_UOM_CODE            => p_pn_var_rents_rec.uom_code,
      X_INVOICE_ON          => p_pn_var_rents_rec.invoice_on,
      X_NEGATIVE_RENT       => p_pn_var_rents_rec.negative_rent,
      X_TERM_TEMPLATE_ID    => p_pn_var_rents_rec.term_template_id,
      --X_ABATEMENT_AMOUNT    => p_pn_var_rents_rec.abatement_amount,
      X_ATTRIBUTE_CATEGORY  => p_pn_var_rents_rec.attribute_category,
      X_ATTRIBUTE1          => p_pn_var_rents_rec.attribute1,
      X_ATTRIBUTE2          => p_pn_var_rents_rec.attribute2,
      X_ATTRIBUTE3          => p_pn_var_rents_rec.attribute3,
      X_ATTRIBUTE4          => p_pn_var_rents_rec.attribute4,
      X_ATTRIBUTE5          => p_pn_var_rents_rec.attribute5,
      X_ATTRIBUTE6          => p_pn_var_rents_rec.attribute6,
      X_ATTRIBUTE7          => p_pn_var_rents_rec.attribute7,
      X_ATTRIBUTE8          => p_pn_var_rents_rec.attribute8,
      X_ATTRIBUTE9          => p_pn_var_rents_rec.attribute9,
      X_ATTRIBUTE10         => p_pn_var_rents_rec.attribute10,
      X_ATTRIBUTE11         => p_pn_var_rents_rec.attribute11,
      X_ATTRIBUTE12         => p_pn_var_rents_rec.attribute12,
      X_ATTRIBUTE13         => p_pn_var_rents_rec.attribute13,
      X_ATTRIBUTE14         => p_pn_var_rents_rec.attribute14,
      X_ATTRIBUTE15         => p_pn_var_rents_rec.attribute15,
      X_ORG_ID              => p_pn_var_rents_rec.org_id,
      X_CREATION_DATE       => sysdate,
      X_CREATED_BY          => NVL(FND_PROFILE.VALUE('USER_ID'),1),
      X_LAST_UPDATE_DATE    => sysdate,
      X_LAST_UPDATED_BY     => NVL(FND_PROFILE.VALUE('USER_ID'),1),
      X_LAST_UPDATE_LOGIN   => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
      X_CURRENCY_CODE       => p_pn_var_rents_rec.currency_code,
      X_PRORATION_RULE      => p_pn_var_rents_rec.proration_rule,
      X_AGREEMENT_TEMPLATE_ID => p_pn_var_rents_rec.agreement_template_id
      );

Line: 787

   dbms_output.put_line('calling insert into pn_var_rent_dates_pkg.insert_row');

Line: 788

      pn_var_rent_dates_pkg.insert_row (
         X_ROWID              => l_rowid,
         X_VAR_RENT_DATE_ID   => l_var_rent_date_id,
         X_VAR_RENT_ID        => l_var_rent_id,
         X_GL_PERIOD_SET_NAME => p_var_rent_dates_rec.gl_period_set_name,
         X_PERIOD_FREQ_CODE   => p_var_rent_dates_rec.period_freq_code,
         X_REPTG_FREQ_CODE    => p_var_rent_dates_rec.reptg_freq_code,
         X_REPTG_DAY_OF_MONTH => p_var_rent_dates_rec.reptg_day_of_month,
         X_REPTG_DAYS_AFTER   => p_var_rent_dates_rec.reptg_days_after,
         X_INVG_FREQ_CODE     => p_var_rent_dates_rec.invg_freq_code,
         X_INVG_DAY_OF_MONTH  => p_var_rent_dates_rec.invg_day_of_month,
         X_INVG_DAYS_AFTER    => p_var_rent_dates_rec.invg_days_after,
         X_INVG_SPREAD_CODE   => p_var_rent_dates_rec.invg_spread_code,
         X_INVG_TERM          => p_var_rent_dates_rec.invg_term,
         X_AUDIT_FREQ_CODE    => p_var_rent_dates_rec.audit_freq_code,
         X_AUDIT_DAY_OF_MONTH => p_var_rent_dates_rec.audit_day_of_month,
         X_AUDIT_DAYS_AFTER   => p_var_rent_dates_rec.audit_days_after,
         X_RECON_FREQ_CODE    => p_var_rent_dates_rec.recon_Freq_code,
         X_RECON_DAY_OF_MONTH => p_var_rent_dates_rec.recon_day_of_month,
         X_RECON_DAYS_AFTER   => p_var_rent_dates_rec.recon_days_after,
         X_ATTRIBUTE_CATEGORY  => p_var_rent_dates_rec.attribute_category,
         X_ATTRIBUTE1          => p_var_rent_dates_rec.attribute1,
         X_ATTRIBUTE2          => p_var_rent_dates_rec.attribute2,
         X_ATTRIBUTE3          => p_var_rent_dates_rec.attribute3,
         X_ATTRIBUTE4          => p_var_rent_dates_rec.attribute4,
         X_ATTRIBUTE5          => p_var_rent_dates_rec.attribute5,
         X_ATTRIBUTE6          => p_var_rent_dates_rec.attribute6,
         X_ATTRIBUTE7          => p_var_rent_dates_rec.attribute7,
         X_ATTRIBUTE8          => p_var_rent_dates_rec.attribute8,
         X_ATTRIBUTE9          => p_var_rent_dates_rec.attribute9,
         X_ATTRIBUTE10         => p_var_rent_dates_rec.attribute10,
         X_ATTRIBUTE11         => p_var_rent_dates_rec.attribute11,
         X_ATTRIBUTE12         => p_var_rent_dates_rec.attribute12,
         X_ATTRIBUTE13         => p_var_rent_dates_rec.attribute13,
         X_ATTRIBUTE14         => p_var_rent_dates_rec.attribute14,
         X_ATTRIBUTE15         => p_var_rent_dates_rec.attribute15,
         X_ORG_ID              => p_var_rent_dates_rec.org_id,
         X_CREATION_DATE       => sysdate,
         X_CREATED_BY          => NVL(FND_PROFILE.VALUE('USER_ID'),1),
         X_LAST_UPDATE_DATE    => sysdate,
         X_LAST_UPDATED_BY     => NVL(FND_PROFILE.VALUE('USER_ID'),1),
         X_LAST_UPDATE_LOGIN   => NVL(FND_PROFILE.VALUE('LOGIN_ID'),1),
         X_USE_GL_CALENDAR      => p_var_rent_dates_rec.use_gl_calendar,
         X_PERIOD_TYPE          => p_var_rent_dates_rec.period_type,
         X_YEAR_START_DATE      => p_var_rent_dates_rec.year_start_date,
         X_COMMENTS            => p_var_rent_dates_rec.comments,
         X_EFFECTIVE_DATE      => p_var_rent_dates_rec.effective_date);

Line: 866

   UPDATE pn_var_rents_all
   SET excess_abat_code = x_excess_abat_code,
       order_of_appl_code = x_order_of_appl_code,
       LAST_UPDATE_DATE  = sysdate,
       LAST_UPDATED_BY   = NVL(fnd_profile.value('USER_ID'),-1),
       LAST_UPDATE_LOGIN = NVL(fnd_profile.value('USER_ID'),-1)
   WHERE var_rent_id=x_var_rent_id ;

Line: 890

PROCEDURE DELETE_VAR_RENT_AGREEMENT(p_lease_id IN NUMBER,
                                    p_termination_dt IN DATE)
IS
CURSOR get_var_rents(p1_lease_id IN NUMBER,p1_termination_dt IN DATE) IS
SELECT var_rent_id
FROM pn_var_rents_all vrent
WHERE lease_id = p1_lease_id
AND commencement_date > p1_termination_dt
AND NOT EXISTS ( SELECT NULL
                 FROM pn_payment_schedules_all ps,
                      pn_payment_items_all     pi,
                      pn_payment_terms_all     pterm
                 WHERE pi.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
                   AND  pi.PAYMENT_TERM_ID    = pterm.PAYMENT_TERM_ID
                   AND  pterm.var_rent_inv_id IN (SELECT var_rent_inv_id FROM pn_var_rent_inv_all
                                                  WHERE var_rent_id= vrent.var_rent_id
                                                  )
                   AND  ps.PAYMENT_STATUS_LOOKUP_CODE='APPROVED'
               );

Line: 919

   PN_VAR_RENT_PKG.delete_var_rent_periods(l_varent_id);

Line: 921

   PN_VAR_TRX_PKG.delete_transactions( p_var_rent_id => l_varent_id
                                      ,p_period_id  => NULL
                                      ,p_line_item_id => NULL);

Line: 925

   DELETE FROM pn_var_rent_dates_all
   WHERE var_rent_id=l_varent_id;

Line: 928

   DELETE FROM pn_var_rents_all
   WHERE var_rent_id=l_varent_id;

Line: 937

END delete_var_rent_agreement;