eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PN_VAR_ABATEMENT_AMOUNT_PKG SQL Statements

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

Line: 21


SELECT NVL(SUM(ppi.actual_amount),0)
FROM pn_payment_items_all  ppi,
     pn_var_rent_inv_all inv,    --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_var_rents_all var ,      --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_payment_schedules_all pps,
     pn_var_abatements_all abt
WHERE ppi.payment_term_id = abt.payment_term_id
AND   abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
                             WHERE inv1.var_rent_id = inv2.var_rent_id
                             AND   inv1.period_id = inv2.period_id
                             AND   inv1.invoice_date = inv2.invoice_date
                             AND   inv1.var_rent_inv_id = p_var_rent_inv_id
			     AND   inv2.true_up_amt IS NULL
			     AND   inv2.adjust_num =0)
AND   abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
AND   inv.var_rent_inv_id = p_var_rent_inv_id  --BUG#2452909,
AND   var.var_rent_id = inv.var_rent_id      --BUG#2452909
AND   ppi.currency_code = var.currency_code    --BUG#2452909
AND   ppi.payment_schedule_id = pps.payment_schedule_id
AND   ppi.payment_item_type_lookup_code = 'CASH'
AND   TRUNC(pps.schedule_date,'MM') BETWEEN
      TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');

Line: 45


SELECT NVL(SUM(ppi.actual_amount),0)
FROM pn_payment_items_all  ppi,
     pn_var_rent_inv_all inv,    --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_var_rents_all var ,      --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_payment_schedules_all pps,
     pn_var_abatements_all abt
WHERE ppi.payment_term_id = abt.payment_term_id
AND   abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
                             WHERE inv1.var_rent_id = inv2.var_rent_id
                             AND   inv1.period_id = inv2.period_id
                             AND   inv1.invoice_date = inv2.invoice_date
                             AND   inv1.var_rent_inv_id = p_var_rent_inv_id
			     AND   inv2.true_up_amt IS NOT NULL
			     AND   inv2.adjust_num =0)
AND   abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
AND   inv.var_rent_inv_id = p_var_rent_inv_id  --BUG#2452909,
AND   var.var_rent_id = inv.var_rent_id      --BUG#2452909
AND   ppi.currency_code = var.currency_code    --BUG#2452909
AND   ppi.payment_schedule_id = pps.payment_schedule_id
AND   ppi.payment_item_type_lookup_code = 'CASH'
AND   TRUNC(pps.schedule_date,'MM') BETWEEN
      TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');

Line: 71


  SELECT  true_up_amt
    FROM  pn_var_rent_inv_all inv
   WHERE  inv.var_rent_inv_id = ip_var_rent_inv_id;

Line: 130


SELECT NVL(SUM(ppi.actual_amount),0) amount
FROM pn_payment_items_all  ppi,
     pn_var_rent_inv_all inv,    --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_var_rents_all var ,      --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_payment_schedules_all pps,
     pn_var_abatements_all abt
WHERE ppi.payment_term_id = abt.payment_term_id
AND   abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
                             WHERE inv1.var_rent_id = inv2.var_rent_id
                             AND   inv1.period_id = inv2.period_id
                             AND   inv1.invoice_date = inv2.invoice_date
                             AND   inv1.var_rent_inv_id = ip_var_rent_inv_id
			     AND   inv2.true_up_amt IS NULL
			     AND   inv2.adjust_num =0)
AND   abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
AND   inv.var_rent_inv_id = ip_var_rent_inv_id  --BUG#2452909,
AND   var.var_rent_id = inv.var_rent_id      --BUG#2452909
AND   ppi.currency_code = var.currency_code    --BUG#2452909
AND   ppi.payment_schedule_id = pps.payment_schedule_id
AND   ppi.payment_item_type_lookup_code = 'CASH'
AND   TRUNC(pps.schedule_date,'MM') BETWEEN
      TRUNC(ip_min_grp_dt,'MM') AND TRUNC(ip_max_grp_dt,'MM');

Line: 154


SELECT NVL(SUM(ppi.actual_amount),0)
FROM pn_payment_items_all  ppi,
     pn_var_rent_inv_all inv,    --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_var_rents_all var ,      --BUG#2452909   /* hrodda_MOAC -changed to tablename_all*/
     pn_payment_schedules_all pps,
     pn_var_abatements_all abt
WHERE ppi.payment_term_id = abt.payment_term_id
AND   abt.var_rent_inv_id = (SELECT inv2.var_rent_inv_id FROM pn_var_rent_inv_all inv1, pn_var_rent_inv_all inv2
                             WHERE inv1.var_rent_id = inv2.var_rent_id
                             AND   inv1.period_id = inv2.period_id
                             AND   inv1.invoice_date = inv2.invoice_date
                             AND   inv1.var_rent_inv_id = p_var_rent_inv_id
			     AND   inv2.true_up_amt IS NOT NULL
			     AND   inv2.adjust_num =0)
AND   abt.include_term = pn_var_abatement_amount_pkg.G_INCLUDE_TERM_YES
AND   inv.var_rent_inv_id = p_var_rent_inv_id  --BUG#2452909,
AND   var.var_rent_id = inv.var_rent_id      --BUG#2452909
AND   ppi.currency_code = var.currency_code    --BUG#2452909
AND   ppi.payment_schedule_id = pps.payment_schedule_id
AND   ppi.payment_item_type_lookup_code = 'CASH'
AND   TRUNC(pps.schedule_date,'MM') BETWEEN
      TRUNC(p_min_grp_dt,'MM') AND TRUNC(p_max_grp_dt,'MM');

Line: 180


  SELECT  true_up_amt
    FROM  pn_var_rent_inv_all inv
   WHERE  inv.var_rent_inv_id = ip_var_rent_inv_id;

Line: 187


  SELECT var_rent_inv_id
    FROM pn_var_rent_inv_all
   WHERE period_id = ip_period_id;

Line: 194


  SELECT invoice_date
    FROM pn_var_rent_inv_all
   WHERE var_rent_inv_id = ip_inv_id
   AND true_up_amt IS NULL
   AND adjust_num = 0;

Line: 202


    SELECT proration_rule
    FROM pn_var_rents_all
    WHERE var_rent_id = ip_var_rent_id;

Line: 208


    SELECT period_id
      FROM pn_var_periods_all
     WHERE start_date = (SELECT min(start_date) from pn_var_periods_all WHERE var_rent_id = ip_var_rent_id)
       AND var_rent_id = ip_var_rent_id;

Line: 215


    SELECT per.start_date, per.end_date
      FROM pn_var_periods_all per
     WHERE period_id = ip_period_id;

Line: 332


      DELETE the term FROM pn_payment_terms */

   IF l_exported IS NULL THEN --Fix for bug # 2722191

    l_abt_amt := NVL(calc_abatement(p_var_rent_inv_id,p_min_grp_dt,p_max_grp_dt),0);

Line: 339


       DELETE FROM pn_payment_terms_all
       WHERE var_rent_inv_id=p_var_rent_inv_id
       and var_rent_type = p_var_rent_type
       and NVL(status,'DRAFT')='DRAFT';

Line: 345


    /* Update pn_var_rent_inv with the recurring abatement amount and also
       UPDATE the actual invoiced amount */

    UPDATE pn_var_rent_inv_all
    SET rec_abatement = l_abt_amt,
        actual_term_status = DECODE(p_var_rent_type,'ACTUAL','N',actual_term_status),
        variance_term_status = DECODE(p_var_rent_type,'VARIANCE','N',variance_term_status),
        actual_invoiced_amount = pn_variable_amount_pkg.derive_actual_invoiced_amt(
                                                 constr_actual_rent,
                                                 p_negative_rent_flag,
                                                 abatement_appl,
                                                 negative_rent,
                                                 l_abt_amt,
                                                 rec_abatement_override)
    WHERE var_rent_inv_id = p_var_rent_inv_id;

Line: 387


SELECT MIN(grp_start_date)
FROM pn_var_grp_dates_all
WHERE invoice_date = p_invoice_date
AND period_id = p_period_id;

Line: 393


SELECT MAX(grp_end_date)
FROM pn_var_grp_dates_all
WHERE invoice_date = p_invoice_date
AND period_id = p_period_id;

Line: 433


SELECT 'Y'
FROM dual
WHERE EXISTS(SELECT null
             FROM pn_var_abatements_all
             WHERE payment_term_id = p_payment_term_id
             AND var_rent_inv_id = p_var_rent_inv_id);