APPS.PN_VAR_UPG_TRX_PKG SQL Statements

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

Line: 30

SELECT pvr.var_rent_id
FROM   pn_leases            pl,
       pn_var_rents_all      pvr,
       pn_locations_all      ploc
WHERE  pl.lease_id = pvr.lease_id
AND    ploc.location_id = pvr.location_id
AND    ploc.location_id IN (SELECT location_id
                             FROM  pn_locations_all
                           START WITH location_id
                                              IN
                                                (SELECT location_id
                                                   FROM pn_locations_all
                                                  WHERE property_id IN(SELECT property_id
                                                                       FROM pn_properties_all
                                                                      WHERE property_code=NVL(p_property_code,property_code)
                                                                        AND property_name=NVL(p_property_name,property_name))
                                                )
                          CONNECT BY PRIOR location_id=parent_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)
ORDER BY pl.lease_id, pvr.var_rent_id;

Line: 57

SELECT pvr.var_rent_id
FROM   pn_leases           pl,
       pn_var_rents_all      pvr,
       pn_locations_all      ploc
WHERE  pl.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)
ORDER BY pl.lease_id, pvr.var_rent_id;

Line: 72

SELECT pvr.var_rent_id
FROM   pn_var_rents_all      pvr,
       pn_leases_all         pl
WHERE  pl.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)
ORDER BY pl.lease_id, pvr.var_rent_id;

Line: 152

     SELECT /*+ ROWID(vr) */ vr.var_rent_id, invoice_on
     FROM   pn_var_rents_all vr
     WHERE vr.var_rent_id = p_var_rent_id
     AND EXISTS (SELECT 1
                 FROM pn_var_rent_inv_all inv
                 WHERE inv.var_rent_id = p_var_rent_id
	               AND rownum=1);

Line: 162

   SELECT /*+ leading(trx) */
   summ.var_rent_summ_id, trx.trx_header_id
   FROM pn_var_trx_headers_all trx, pn_var_rent_summ_all summ
   WHERE trx.grp_date_id = summ.grp_date_id
   AND trx.var_rent_id = p_var_rent_id;

Line: 187

    l_summ_id_tab.DELETE;

Line: 188

    l_trx_id_tab.DELETE;

Line: 195

      /* after populating the sales, sales_vol_update_flag becomes NULL.
      Therefore need to set it to 'Y' so that forecasted sales get populated */
      UPDATE pn_var_lines_all
      SET sales_vol_update_flag = 'Y'
      WHERE var_rent_id = p_var_rent_id;

Line: 212

     UPDATE pn_var_trx_headers_all
     SET   var_rent_summ_id = l_summ_id_tab(i)
     WHERE trx_header_id    = l_trx_id_tab(i);