APPS.PN_VAR_ABATEMENTS_PKG SQL Statements

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

Line: 12

procedure INSERT_ROW (
                     X_ROWID             IN out NOCOPY VARCHAR2,
                     X_VAR_ABATEMENT_ID  IN out NOCOPY NUMBER,
                     X_VAR_RENT_ID       IN NUMBER,
                     X_VAR_RENT_INV_ID   IN NUMBER,
                     X_PAYMENT_TERM_ID   IN NUMBER,
                     X_INCLUDE_TERM      IN VARCHAR2,
                     X_INCLUDE_INCREASES IN VARCHAR2,
                     X_UPDATE_FLAG       IN VARCHAR2,
                     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_ORG_ID            IN NUMBER
                     ) IS

  CURSOR var_abatements IS
  SELECT ROWID
  FROM   PN_VAR_ABATEMENTS_ALL
  WHERE  VAR_ABATEMENT_ID = X_VAR_ABATEMENT_ID;

Line: 35

  SELECT org_id
  FROM   pn_payment_terms_all
  WHERE  payment_term_id = x_payment_term_id;

Line: 44

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

Line: 59

          SELECT  pn_var_abatements_s.nextval
          INTO    X_VAR_ABATEMENT_ID
          FROM    dual;

Line: 64

        INSERT INTO PN_VAR_ABATEMENTS_ALL
        (         VAR_RENT_ID,
                  VAR_ABATEMENT_ID,
                  VAR_RENT_INV_ID,
                  PAYMENT_TERM_ID,
                  INCLUDE_TERM,
                  INCLUDE_INCREASES,
                  UPDATE_FLAG,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_LOGIN,
                  ORG_ID
        )
        VALUES
        (         X_VAR_RENT_ID,
                  X_VAR_ABATEMENT_ID,
                  X_VAR_RENT_INV_ID,
                  X_PAYMENT_TERM_ID,
                  X_INCLUDE_TERM,
                  X_INCLUDE_INCREASES,
                  X_UPDATE_FLAG,
                  X_LAST_UPDATE_DATE,
                  X_LAST_UPDATED_BY,
                  X_CREATION_DATE,
                  X_CREATED_BY,
                  X_LAST_UPDATE_LOGIN,
                  l_ORG_ID
        );

Line: 103

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

Line: 105

END INSERT_ROW;

Line: 121

SELECT *
FROM PN_VAR_ABATEMENTS_ALL
WHERE VAR_RENT_ID = X_VAR_RENT_ID AND
      VAR_RENT_INV_ID = X_VAR_RENT_INV_ID AND
      PAYMENT_TERM_ID = X_PAYMENT_TERM_ID
FOR UPDATE OF VAR_ABATEMENT_ID NOWAIT;

Line: 167

procedure UPDATE_ROW
        (
           X_VAR_RENT_ID       IN NUMBER,
           X_VAR_RENT_INV_ID   IN NUMBER,
           X_PAYMENT_TERM_ID   IN NUMBER,
           X_INCLUDE_TERM      IN VARCHAR2,
           X_INCLUDE_INCREASES IN VARCHAR2,
           X_UPDATE_FLAG       IN VARCHAR2,
           X_LAST_UPDATE_DATE  IN DATE,
           X_LAST_UPDATED_BY   IN NUMBER,
           X_LAST_UPDATE_LOGIN IN NUMBER
        ) IS

BEGIN

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

Line: 184

        UPDATE PN_VAR_ABATEMENTS_ALL SET
               VAR_RENT_ID       = X_VAR_RENT_ID,
               VAR_RENT_INV_ID   = X_VAR_RENT_INV_ID,
               PAYMENT_TERM_ID   = X_PAYMENT_TERM_ID,
               INCLUDE_TERM      = X_INCLUDE_TERM,
               INCLUDE_INCREASES = X_INCLUDE_INCREASES,
               UPDATE_FLAG       = X_UPDATE_FLAG,
               LAST_UPDATE_DATE  = X_LAST_UPDATE_DATE,
               LAST_UPDATED_BY   = X_LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
        WHERE VAR_RENT_ID = X_VAR_RENT_ID
        AND   VAR_RENT_INV_ID = X_VAR_RENT_INV_ID
        AND PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;

Line: 202

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

Line: 204

END UPDATE_ROW;

Line: 214

procedure DELETE_ROW
        ( X_VAR_RENT_ID       IN NUMBER,
          X_VAR_RENT_INV_ID   IN NUMBER,
          X_PAYMENT_TERM_ID   IN NUMBER
        ) IS

BEGIN

        PNP_DEBUG_PKG.debug ('PN_VAR_ABATEMENTS_PKG.DELETE_ROW (+)');

Line: 224

        DELETE FROM PN_VAR_ABATEMENTS_ALL
        WHERE VAR_RENT_ID = X_VAR_RENT_ID
        AND VAR_RENT_INV_ID = X_VAR_RENT_INV_ID
        AND PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;

Line: 234

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

Line: 236

END DELETE_ROW;

Line: 259

SELECT invoice_date
  FROM pn_var_rent_inv_all
 WHERE var_rent_inv_id=p1_var_rent_inv_id;

Line: 266

SELECT 'Y' calc_inv
FROM dual WHERE EXISTS
(SELECT *
  FROM pn_var_rent_inv_all
 WHERE var_rent_id=p1_var_rent_id
   AND invoice_date > p1_inv_date);

Line: 313

  SELECT 'y'
    FROM dual
   WHERE exists ( select null from pn_var_abatements_all
                  where var_rent_id=p_var_rentId AND
                  var_rent_inv_id=p_var_rent_inv_id AND
                  payment_term_id=p_pmt_term_id);

Line: 347

PROCEDURE RESET_UPDATE_FLAG(p_var_rentId IN NUMBER,
                            p_var_rent_inv_id IN NUMBER
                           )
IS
-- Get the details of
CURSOR get_update_cur(p_var_rentId IN NUMBER,p_var_rent_inv_id IN NUMBER) IS
  SELECT *
    FROM pn_var_abatements_all
   WHERE var_rent_id= p_var_rentId
     AND var_rent_inv_id = p_var_rent_inv_id
     AND update_flag = 'Y';

Line: 360

FOR get_update_rec IN get_update_cur(p_var_rentId,p_var_rent_inv_id) LOOP
  PN_VAR_ABATEMENTS_PKG.UPDATE_ROW (
  X_VAR_RENT_ID       =>  p_var_rentId  ,
  X_VAR_RENT_INV_ID   => p_var_rent_inv_id,
  X_PAYMENT_TERM_ID   => get_update_rec.PAYMENT_TERM_ID,
  X_INCLUDE_TERM      => get_update_rec.INCLUDE_TERM ,
  X_INCLUDE_INCREASES => get_update_rec.INCLUDE_INCREASES,
  X_UPDATE_FLAG       => NULL,
  X_LAST_UPDATE_DATE  => sysdate,
  X_LAST_UPDATED_BY   => NVL(fnd_profile.value('USER_ID'),-1),
  X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1)
  );

Line: 374

END RESET_UPDATE_FLAG;

Line: 407

  SELECT invoice_date
    FROM pn_var_rent_inv_all
   WHERE var_rent_inv_id=p_var_rent_inv_id;

Line: 413

  SELECT distinct gd1.invoice_date,decode(temp.inv_id,NULL,-1,temp.inv_id) v_inv_id
    FROM pn_var_grp_dates_all gd1,
    (SELECT gd.invoice_date inv_dt,vinv.var_rent_inv_id inv_id
     FROM pn_var_grp_dates_all gd , pn_var_rent_inv_all vinv
     WHERE vinv.var_rent_id=gd.var_rent_id
     AND vinv.invoice_date=gd.invoice_date
     AND vinv.period_id=gd.period_id
     AND gd.var_rent_id=p_var_rent_id
     AND vinv.adjust_num=0
     ) temp
    WHERE gd1.var_rent_id=p_var_rent_id
    AND gd1.invoice_date=temp.inv_dt(+)
    AND gd1.invoice_date>l_invoice_dt
    ORDER BY gd1.invoice_date;

Line: 431

  SELECT 'x' pterm_exists
  FROM dual WHERE EXISTS
  (SELECT  NULL
  FROM pn_payment_terms_all pterm,
     pn_var_rents_all vrent,
     pn_var_rent_inv_all vinv
  WHERE
    vrent.lease_id = pterm.lease_id
  AND vrent.var_rent_id = vinv.var_rent_id
  AND pterm.start_date <=
  (SELECT MAX(gd.grp_end_date)
   FROM pn_var_grp_dates_all gd
   WHERE gd.period_id = vinv.period_id
   AND gd.invoice_date = vinv.invoice_date
  )
  AND pterm.end_date >=
  (SELECT MIN(gd1.grp_start_date)
   FROM pn_var_grp_dates_all gd1
   WHERE gd1.period_id = vinv.period_id
   AND gd1.invoice_date = vinv.invoice_date
  )
  AND pterm.var_rent_inv_id IS NULL
  AND pterm.index_period_id IS NULL
  AND vinv.adjust_num = 0
  AND vinv.var_rent_inv_id=p_inv_id
  AND pterm.payment_term_id=p_term_id);

Line: 460

  SELECT 'x' abatement_exists
  FROM dual
  WHERE EXISTS (SELECT  payment_term_id
                FROM pn_var_abatements_all
                WHERE var_rent_inv_id=p_inv_id
                AND payment_term_id=p_term_id);

Line: 469

  SELECT payment_term_id,include_term,include_increases
    FROM pn_var_abatements_all pva
   WHERE pva.var_rent_id= p_var_rentId
     AND pva.var_rent_inv_id = p_var_rent_inv_id
     AND update_flag = 'Y';

Line: 476

  SELECT org_id
  FROM   pn_var_rents_all
  WHERE  var_rent_id =p_var_rentId;

Line: 524

            PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
            X_ROWID             => l_row_id,
            X_VAR_ABATEMENT_ID  => l_var_abmt_id,
            X_VAR_RENT_ID       => p_var_rentId,
            X_VAR_RENT_INV_ID   => l_inv_id,
            X_PAYMENT_TERM_ID   => upd_rec.payment_term_id,
            X_INCLUDE_TERM      => upd_rec.include_term,
            X_INCLUDE_INCREASES => upd_rec.include_increases,
            X_UPDATE_FLAG       => NULL,
            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('USER_ID'),-1),
            X_ORG_ID            => l_org_id  );

Line: 541

             PN_VAR_ABATEMENTS_PKG.UPDATE_ROW(
             X_VAR_RENT_ID       => p_var_rentId,
             X_VAR_RENT_INV_ID   => l_inv_id,
             X_PAYMENT_TERM_ID   => upd_rec.payment_term_id,
             X_INCLUDE_TERM      => upd_rec.include_term,
             X_INCLUDE_INCREASES => upd_rec.include_increases,
             X_UPDATE_FLAG       => NULL,
             X_LAST_UPDATE_DATE  => sysdate,
             X_LAST_UPDATED_BY   => NVL(fnd_profile.value('USER_ID'),-1),
             X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1));

Line: 572

         PN_VAR_ABATEMENTS_PKG.DELETE_ROW(
            X_VAR_RENT_ID       =>  p_var_rentId,
            X_VAR_RENT_INV_ID   =>  l_inv_id,
            X_PAYMENT_TERM_ID   =>  p_pmt_term_id);

Line: 610

  SELECT include_term
    FROM pn_var_abatements_all
   WHERE var_rent_id=p_var_rent_id
     AND payment_term_id=p_payment_term_id
     AND var_rent_inv_id=p_var_rent_inv_id;

Line: 653

  SELECT include_increases
    FROM pn_var_abatements_all
   WHERE var_rent_id=p_var_rent_id
     AND payment_term_id=p_payment_term_id
     AND var_rent_inv_id=p_var_rent_inv_id;

Line: 703

  SELECT distinct gd1.invoice_date,decode(temp.inv_id,NULL,-1,temp.inv_id) v_inv_id
    FROM pn_var_grp_dates_all gd1,
         pn_var_periods_all vp,
    (SELECT gd.invoice_date inv_dt,vinv.var_rent_inv_id inv_id
     FROM pn_var_grp_dates_all gd , pn_var_rent_inv_all vinv
     WHERE vinv.var_rent_id=gd.var_rent_id
     AND vinv.invoice_date=gd.invoice_date
     AND vinv.period_id=gd.period_id
     AND gd.var_rent_id=p_var_rent_id
     AND adjust_num=0
    ) temp
     WHERE gd1.var_rent_id=p_var_rent_id
     AND gd1.invoice_date=temp.inv_dt(+)
     AND gd1.period_id=vp.period_id
     AND vp.period_num >1
     --AND gd1.invoice_date>l_invoice_dt
     ORDER BY gd1.invoice_date;

Line: 723

  SELECT 'x' pterm_exists
  FROM dual WHERE EXISTS
  (SELECT  NULL
  FROM pn_payment_terms_all pterm,
     pn_var_rents_all vrent,
     pn_var_rent_inv_all vinv
  WHERE
    vrent.lease_id = pterm.lease_id
  AND vrent.var_rent_id = vinv.var_rent_id
  AND pterm.start_date <=
  (SELECT MAX(gd.grp_end_date)
   FROM pn_var_grp_dates_all gd
   WHERE gd.period_id = vinv.period_id
   AND gd.invoice_date = vinv.invoice_date
  )
  AND pterm.end_date >=
  (SELECT MIN(gd1.grp_start_date)
   FROM pn_var_grp_dates_all gd1
   WHERE gd1.period_id = vinv.period_id
   AND gd1.invoice_date = vinv.invoice_date
  )
  AND pterm.var_rent_inv_id IS NULL
  AND pterm.index_period_id IS NULL
  AND vinv.adjust_num = 0
  AND vinv.var_rent_inv_id=p_inv_id
  AND pterm.payment_term_id=p_term_id);

Line: 752

  SELECT 'x' abatement_exists
  FROM dual
  WHERE exists (select  payment_term_id
  FROM pn_var_abatements_all
  WHERE var_rent_inv_id=p_inv_id
  AND payment_term_id=p_term_id);

Line: 761

  SELECT payment_term_id,include_term,include_increases
    FROM pn_var_abatements_all pva
   WHERE pva.var_rent_id= p_var_rentId
     AND pva.var_rent_inv_id = p_var_rent_inv_id
     AND update_flag = 'Y';

Line: 768

  SELECT org_id
  FROM   pn_var_rents_all
  WHERE  var_rent_id =p_var_rentId;

Line: 811

            PN_VAR_ABATEMENTS_PKG.INSERT_ROW(
            X_ROWID             => l_row_id,
            X_VAR_ABATEMENT_ID  => l_var_abmt_id,
            X_VAR_RENT_ID       => p_var_rentId,
            X_VAR_RENT_INV_ID   => l_inv_id,
            X_PAYMENT_TERM_ID   => upd_rec.payment_term_id,
            X_INCLUDE_TERM      => upd_rec.include_term,
            X_INCLUDE_INCREASES => upd_rec.include_increases,
            X_UPDATE_FLAG       => NULL,
            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('USER_ID'),-1),
            X_ORG_ID            => l_org_id  );

Line: 828

             PN_VAR_ABATEMENTS_PKG.UPDATE_ROW(
             X_VAR_RENT_ID       => p_var_rentId,
             X_VAR_RENT_INV_ID   => l_inv_id,
             X_PAYMENT_TERM_ID   => upd_rec.payment_term_id,
             X_INCLUDE_TERM      => upd_rec.include_term,
             X_INCLUDE_INCREASES => upd_rec.include_increases,
             X_UPDATE_FLAG       => NULL,
             X_LAST_UPDATE_DATE  => sysdate,
             X_LAST_UPDATED_BY   => NVL(fnd_profile.value('USER_ID'),-1),
             X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),-1));

Line: 859

         PN_VAR_ABATEMENTS_PKG.DELETE_ROW(
            X_VAR_RENT_ID       => p_var_rentId,
            X_VAR_RENT_INV_ID  =>  l_inv_id,
            X_PAYMENT_TERM_ID  =>  p_pmt_term_id);

Line: 892

  SELECT 'Y' as true_up_flag
  FROM dual
  WHERE EXISTS(SELECT NULL
               FROM pn_var_rent_inv_all
               WHERE var_rent_inv_id = p_var_rent_inv_id
               AND true_up_amt IS NOT NULL
               );