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_APPROVE_VARENT_PKG SQL Statements

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

Line: 52


SELECT pvr.var_rent_id,
       pvr.rent_num,
       pvr.invoice_on,
       pl.status
FROM   pn_leases             pl,
       pn_lease_details_all  pld,
       pn_var_rents_all      pvr,
       pn_locations_all      ploc
WHERE  pl.lease_id = pvr.lease_id
AND    pld.lease_id = pvr.lease_id
AND    ploc.location_id = pvr.location_id
AND    pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
AND    pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
AND    ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
AND    ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
AND    pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
AND    pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
AND    pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND   (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id, pvr.var_rent_id;

Line: 74


SELECT pvr.var_rent_id,
       pvr.rent_num,
       pvr.invoice_on,
       pl.status
FROM   pn_leases             pl,
       pn_var_rents_all      pvr,
       pn_lease_details_all  pld
WHERE  pl.lease_id = pvr.lease_id
AND    pld.lease_id = pvr.lease_id
AND    pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
AND    pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
AND    pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
AND    pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
AND    pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND    pvr.var_rent_id = NVL(p_var_rent_id,pvr.var_rent_id)
AND    (pl.org_id = p_org_id or p_org_id is null)
ORDER BY pl.lease_id,pvr.var_rent_id;

Line: 95


SELECT * FROM
(SELECT per.period_num,
       inv.var_rent_inv_id,
       inv.adjust_num,
       inv.period_id,
       inv.var_rent_id,
       inv.invoice_date,
       inv.for_per_rent,
       inv.actual_invoiced_amount,
       decode(ip_rent_type,'FORECASTED',inv.forecasted_term_status,'ACTUAL',inv.actual_term_status,
                           'VARIANCE',inv.variance_term_status) term_status
FROM   pn_var_rent_inv_all inv,
       pn_var_periods_all per
WHERE  per.var_rent_id = inv.var_rent_id
AND per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.period_num >= NVL(p_period_num_from,per.period_num)
AND per.period_num <= NVL(p_period_num_to,period_num)
AND (inv.forecasted_exp_code = decode(inv.adjust_num
                                       ,0,decode(ip_rent_type,'FORECASTED','N','VARIANCE','Y',inv.forecasted_exp_code)
                                      ,inv.forecasted_exp_code)
     OR inv.true_up_exp_code = 'N')
AND inv.actual_exp_code = decode(ip_rent_type,'ACTUAL','N',inv.actual_exp_code)
AND inv.variance_exp_code = decode(ip_rent_type,'VARIANCE','N',inv.variance_exp_code)
AND inv.var_rent_inv_id = NVL(p_var_rent_inv_id,inv.var_rent_inv_id)
AND ip_rent_type = NVL(ip_var_rent_type,ip_rent_type)
AND invoice_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
UNION
SELECT per.period_num,
       inv.var_rent_inv_id,
       inv.adjust_num,
       inv.period_id,
       inv.var_rent_id,
       inv.invoice_date,
       inv.for_per_rent,
       inv.actual_invoiced_amount,
       decode(ip_rent_type,'FORECASTED',inv.forecasted_term_status,'ACTUAL',inv.actual_term_status,
                           'VARIANCE',inv.variance_term_status) term_status
FROM   pn_var_rent_inv_all inv,
       pn_var_periods_all per,
       pn_payment_terms_all pmt
WHERE per.var_rent_id = inv.var_rent_id
AND per.period_id = inv.period_id
AND inv.var_rent_id = ip_var_rent_id
AND per.period_num >= NVL(p_period_num_from,per.period_num)
AND per.period_num <= NVL(p_period_num_to,period_num)
AND (inv.forecasted_exp_code = decode(inv.adjust_num
                                       ,0,decode(ip_rent_type,'FORECASTED','N','VARIANCE','Y',inv.forecasted_exp_code)
                                      ,inv.forecasted_exp_code)
     OR inv.true_up_exp_code = 'N')
AND inv.actual_exp_code = decode(ip_rent_type,'ACTUAL','N',inv.actual_exp_code)
AND inv.variance_exp_code = decode(ip_rent_type,'VARIANCE','N',inv.variance_exp_code)
AND pmt.var_rent_inv_id = inv.var_rent_inv_id
AND pmt.status = 'APPROVED'
AND ip_rent_type = NVL(ip_var_rent_type,ip_rent_type)
AND invoice_date <= NVL(fnd_date.canonical_to_date(p_period_date),TO_DATE('12/31/4712','mm/dd/yyyy'))
)temp
ORDER BY period_num,invoice_date;

Line: 428


  SELECT  payment_term_id
         ,lease_id
         ,actual_amount
         ,schedule_day
         ,start_date
         ,end_date
         ,normalize
         ,project_id
         ,task_id
         ,organization_id
         ,expenditure_type
         ,expenditure_item_date
         ,distribution_set_id
         ,org_id
         ,customer_id
         ,CUSTOMER_SITE_USE_ID
  FROM   pn_payment_terms_all
  WHERE  var_rent_inv_id = ip_var_rent_inv_id
  /*AND    var_rent_type = ip_rent_type*/;

Line: 450


  SELECT pd.account_class
        ,pd.percentage
    FROM pn_distributions_all pd
   WHERE pd.payment_term_ID = p_term_ID;

Line: 457


  SELECT meaning
    FROM fnd_lookups
   WHERE lookup_code = p_acc_class_code
     AND lookup_type in ('PN_PAY_ACCOUNT_TYPE','PN_REC_ACCOUNT_TYPE');

Line: 464


  SELECT lease_class_code
    FROM pn_leases_all
   WHERE lease_ID = p_lease_ID;

Line: 472


  SELECT period_id
    FROM pn_var_periods_all pvp, pn_var_rents_all pvr
   WHERE pvr.var_rent_id = pvp.var_rent_id
     AND pvp.period_id = p_period_id
     AND pvr.var_rent_id = p_var_rent_id
     AND pvr.proration_rule IN ('FLY', 'FY')
     AND pvp.period_num = 1;

Line: 875


        UPDATE pn_payment_terms_all
        SET status = 'APPROVED',
            last_update_date = SYSDATE,
            last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
            last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
            approved_by = NVL (fnd_profile.VALUE ('USER_ID'), 0)
        WHERE payment_term_id = l_payment_term_id;

Line: 899


  UPDATE pn_var_rent_inv_all
  SET forecasted_exp_code = DECODE(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
      actual_exp_code = DECODE(p_rent_type,'ACTUAL','Y',actual_exp_code),
      variance_exp_code = DECODE(p_rent_type,'VARIANCE','Y',variance_exp_code),
      last_update_date = SYSDATE,
      last_updated_by = NVL(fnd_profile.VALUE ('USER_ID'), 0),
      last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
  WHERE var_rent_inv_id = p_var_rent_inv_id;

Line: 909


  UPDATE pn_var_rent_inv_all
  SET true_up_exp_code = 'Y'
  WHERE var_rent_inv_id = p_var_rent_inv_id
  AND   true_up_exp_code IS NOT NULL;

Line: 915


  UPDATE pn_var_grp_dates_all
  SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
      actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
      variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
      last_update_date = SYSDATE,
      last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
      last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
  WHERE period_id = p_period_id
  AND invoice_date = p_invoice_date
  AND var_rent_id = p_var_rent_id;

Line: 926


  UPDATE pn_var_vol_hist_all
  SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
      last_update_date = SYSDATE,
      last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
      last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
  WHERE period_id = p_period_id
  AND   p_rent_type = 'FORECASTED'
  AND grp_date_id IN
      (SELECT grp_date_id
       FROM pn_var_grp_dates_all
       WHERE period_id = p_period_id
       AND invoice_date = p_invoice_date
       AND var_rent_id = p_var_rent_id);

Line: 942


  UPDATE pn_var_vol_hist_all
  SET actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
      variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
      last_update_date = SYSDATE,
      last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
      last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
  WHERE period_id = p_period_id
  AND vol_hist_status_code = 'APPROVED'
  AND p_rent_type <> 'FORECASTED'
  AND grp_date_id IN
      (SELECT grp_date_id
       FROM pn_var_grp_dates_all
       WHERE period_id = p_period_id
       AND invoice_date = p_invoice_date
       AND var_rent_id = p_var_rent_id);

Line: 958


  /* update the transferred flag in pn_var_deductions to 'Y' for all the group
     dates that have been transferred only if variable rent term type is actual
     or variance */

  IF p_rent_type in ('ACTUAL','VARIANCE') THEN
    UPDATE pn_var_deductions_all
    SET exported_code = 'Y',
        last_update_date = SYSDATE,
        last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
        last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE period_id = p_period_id
    AND grp_date_id IN
         (SELECT grp_date_id
          FROM pn_var_grp_dates_all
          WHERE period_id = p_period_id
          AND invoice_date = p_invoice_date
          AND var_rent_id = p_var_rent_id);

Line: 981


    UPDATE pn_var_grp_dates_all
    SET forecasted_exp_code = decode(p_rent_type,'FORECASTED','Y',forecasted_exp_code),
        actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
        variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
        last_update_date = SYSDATE,
        last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
        last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE period_id = p_period_id
    AND var_rent_id = p_var_rent_id;

Line: 991


    UPDATE pn_var_vol_hist_all
    SET actual_exp_code = decode(p_rent_type,'ACTUAL','Y',actual_exp_code),
        variance_exp_code = decode(p_rent_type,'VARIANCE','Y',variance_exp_code),
        last_update_date = SYSDATE,
        last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
        last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE period_id = p_period_id
    AND vol_hist_status_code = 'APPROVED'
    AND p_rent_type <> 'FORECASTED'
    AND grp_date_id IN
        (SELECT grp_date_id
         FROM pn_var_grp_dates_all
         WHERE period_id = p_period_id);

Line: 1005


    UPDATE pn_var_deductions_all
    SET exported_code = 'Y',
        last_update_date = SYSDATE,
        last_updated_by = NVL (fnd_profile.VALUE ('USER_ID'), 0),
        last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE period_id = p_period_id
    AND p_rent_type <> 'FORECASTED'
    AND grp_date_id IN
         (SELECT grp_date_id
          FROM pn_var_grp_dates_all
          WHERE period_id = p_period_id);