APPS.JAI_AVLIST_VALIDATE_PKG SQL Statements

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

Line: 66

  SELECT
    category_set_name
  FROM
    mtl_default_category_sets_fk_v
  WHERE functional_area_desc = 'Order Entry';

Line: 77

  SELECT
    price_list_id      excise_av_list
  , vat_price_list_id  vat_av_list
  FROM
    jai_cmn_cus_addresses
  WHERE customer_id = pn_party_id
    AND address_id = NVL(pn_party_site_id,0);

Line: 88

  SELECT
    price_list_id      excise_av_list
  , vat_price_list_id  vat_av_list
  FROM
    jai_cmn_vendor_sites
  WHERE vendor_id = pn_party_id
    AND vendor_site_id = NVL(pn_party_site_id,0);

Line: 101

  SELECT
    name
  FROM
    qp_list_headers
  WHERE list_header_id = pn_list_header_id;

Line: 112

  SELECT
    micv.category_id         category_id
  FROM
    qp_list_lines          ql1
  , qp_pricing_attributes  qp1
  , mtl_item_categories_v  micv
  , qp_list_lines          ql2
  , qp_pricing_attributes  qp2
  WHERE ql1.list_header_id = pn_list_header_id
    AND ql1.list_line_id = qp1.list_line_id
    AND qp1.product_attr_value = TO_CHAR(micv.category_id)
    AND micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
    AND micv.category_set_name = lv_category_set_name
    AND qp2.product_attr_value = TO_CHAR(micv.inventory_item_id)
    AND qp1.list_header_id = qp2.list_header_id
    AND ql2.list_line_id = qp2.list_line_id
    AND qp1.product_uom_code = qp2.product_uom_code
    AND pd_ordered_date BETWEEN NVL( ql1.start_date_active, pd_ordered_date)
                               AND NVL( ql1.end_date_active, SYSDATE)
    AND pd_ordered_date BETWEEN NVL( ql2.start_date_active, pd_ordered_date)
                               AND NVL(ql2.end_date_active, SYSDATE)
    AND qp1.product_attribute = 'PRICING_ATTRIBUTE2'  /*Added  both conditions of product_attribute by mmurtuza for bug 13915552*/
    AND qp2.product_attribute = 'PRICING_ATTRIBUTE1'

  GROUP BY micv.inventory_item_id,micv.category_id;

Line: 144

  /*SELECT
    qp.product_uom_code           uom_code
  , COUNT(qp.product_attr_value)  category_number
  FROM
    qp_list_lines ql
  , qp_pricing_attributes qp
  WHERE ql.list_header_id = pn_list_header_id
    AND ql.list_line_id = qp.list_line_id
    AND EXISTS ( SELECT
                   micv.category_id
                 FROM
                   mtl_item_categories_v micv
                 WHERE micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
                   AND micv.category_set_name = lv_category_set_name
                   AND TO_CHAR(micv.category_id) = qp.product_attr_value
                )
    AND pd_ordered_date BETWEEN NVL( ql.start_date_active, pd_ordered_date)
                               AND NVL( ql.end_date_active, SYSDATE)
  GROUP BY qp.product_uom_code;*/

Line: 164

  SELECT
    qp.product_uom_code           uom_code
  , COUNT(distinct qp.product_attr_value)  category_number
  ,micv.inventory_item_id
  FROM
    qp_list_lines ql
  , qp_pricing_attributes qp
  , mtl_item_categories_v micv
  WHERE ql.list_header_id = pn_list_header_id
    AND ql.list_line_id = qp.list_line_id
    AND micv.inventory_item_id = NVL( pn_inventory_item_id, micv.inventory_item_id)
    AND micv.category_set_name = lv_category_set_name
    AND TO_CHAR(micv.category_id) = qp.product_attr_value
    AND pd_ordered_date BETWEEN NVL( ql.start_date_active, pd_ordered_date)
                               AND NVL( ql.end_date_active, SYSDATE)
  GROUP BY qp.product_uom_code, micv.inventory_item_id;

Line: 188

  select count(1)
  from qp_list_lines a, qp_list_lines_v b
  where a.list_line_id = b.list_line_id
    and b.product_attribute = 'PRICING_ATTRIBUTE2'
    and a.list_header_id = pn_list_header_id;