APPS.QP_JAVA_ENGINE_CACHE_PVT SQL Statements

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

Line: 6

PROCEDURE UPDATE_CACHE_STATS
(
 err_buff                OUT NOCOPY VARCHAR2,
 retcode                 OUT NOCOPY NUMBER
)
IS

/*****************************************************************
 Cursors for permanent full data objects
*****************************************************************/

CURSOR pattern_csr IS
SELECT count(*)
FROM qp_patterns;

Line: 22

SELECT count(*)
FROM qp_segments_b qps,
qp_prc_contexts_b qpc
WHERE qps.prc_context_id = qpc.prc_context_id;

Line: 28

SELECT count(*)
FROM qp_price_req_sources;

Line: 32

SELECT count(*)
FROM qp_pricing_phases qpp,
qp_event_phases qpe
WHERE qpe.pricing_phase_id = qpp.pricing_phase_id;

Line: 38

SELECT count(*)
FROM FND_PROFILE_OPTIONS o, FND_PROFILE_OPTION_VALUES ov
WHERE o.APPLICATION_ID = 661 and o.PROFILE_OPTION_ID = ov.PROFILE_OPTION_ID (+) and o.APP_ENABLED_FLAG = 'N' and o.RESP_ENABLED_FLAG = 'N' and o.USER_ENABLED_FLAG = 'N';

Line: 43

SELECT count(*)
FROM qp_cache_do_sizes;

Line: 47

SELECT count(*)
FROM qp_cache_stats;

Line: 51

SELECT count(*)
FROM qp_list_headers_b
WHERE active_flag = 'Y';

Line: 56

SELECT count(*)
FROM qp_qualifiers qpq, qp_list_headers_b qph1, qp_list_headers_b qph2
WHERE qualifier_context = 'MODLIST' and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
and qph1.active_flag = 'Y' and qph2.active_flag = 'Y'
and qph1.list_header_id = qpq.list_header_id
and to_char(qph2.list_header_id) = qpq.qualifier_attr_value
and qph1.list_type_code = 'PRL';

Line: 65

SELECT count(*) FROM (
SELECT 1
FROM qp_attribute_groups qp, qp_list_headers_b qph
WHERE qp.list_line_id = -1 and qph.active_flag = 'Y' and qp.list_header_id(+) = qph.list_header_id UNION ALL
SELECT 1
FROM qp_list_headers_b qp
WHERE qp.active_flag = 'Y' and NOT EXISTS (SELECT * FROM qp_attribute_groups qpg WHERE qpg.list_line_id=-1 and qpg.list_header_id = qp.list_header_id));

Line: 74

SELECT count(*)
FROM (select /*+ ordered index(qph qp_list_headers_b_n7) use_nl(qpl) */
      distinct qpl.pricing_phase_id, qpl.list_header_id, qph.currency_code
      FROM qp_list_headers_b qph, qp_list_lines qpl
      WHERE qph.active_flag = 'Y'
      and qph.ask_for_flag <> 'Y'
      and qph.list_header_id = qpl.list_header_id);

Line: 83

SELECT count(*)
FROM qp_pattern_phases qpph, qp_patterns qpp
WHERE qpp.pattern_type = 'HP' and qpp.pattern_id = qpph.pattern_id;

Line: 88

SELECT count(*)
FROM okx_units_of_measure_v;

Line: 92

SELECT count(*)
FROM okc_time_code_units_b
WHERE active_flag = 'Y';

Line: 101

SELECT count(*)
FROM qp_list_headers_b qph, qp_list_lines qpl
WHERE qpl.qualification_ind in (0, 2)
and qpl.list_header_id = qph.list_header_id
and qph.active_flag = 'Y';

Line: 108

SELECT count(*)
FROM mtl_uom_conversions
WHERE (disable_date is null or disable_date > sysdate)
and inventory_item_id = 0;

Line: 118

SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ count(DISTINCT
qpl.cache_key)
FROM qp_list_headers_b qph, qp_list_lines qpl
WHERE qph.active_flag = 'Y'
and qph.list_type_code not in ('PML', 'PRL', 'AGR')
and qpl.list_header_id = qph.list_header_id;

Line: 126

SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ count(DISTINCT
qpl.cache_key)
FROM qp_list_headers_b qph, qp_list_lines qpl
WHERE qph.active_flag = 'Y'
and qph.list_type_code = 'PRL'
and qpl.list_header_id = qph.list_header_id;

Line: 134

SELECT count(*)
FROM qp_price_formulas qpf;

Line: 138

SELECT count(*)
FROM qp_list_headers_b qlh
WHERE qlh.list_type_code = 'PML';

Line: 143

SELECT count(*)
FROM mtl_uom_conversions
WHERE (disable_date IS null or disable_date > sysdate)
and inventory_item_id <> 0;

Line: 149

SELECT count(DISTINCT qpc.currency_header_id)
FROM qp_currency_lists_b qpc, qp_list_headers_b qph
WHERE qph.active_flag = 'Y'
and qph.currency_code = qpc.base_currency_code;

Line: 155

SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ DISTINCT
qpl.cache_key
FROM qp_list_headers_b qph, qp_list_lines qpl
WHERE qph.active_flag = 'Y'
and qph.list_type_code not in ('PML', 'PRL', 'AGR')
and qpl.list_header_id = qph.list_header_id
and ROWNUM < 200;

Line: 164

SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ DISTINCT
qpl.cache_key
FROM qp_list_headers_b qph, qp_list_lines qpl
WHERE qph.active_flag = 'Y'
and qph.list_type_code = 'PRL'
and qpl.list_header_id = qph.list_header_id
and ROWNUM < 200;

Line: 173

SELECT
qpf.price_formula_id
FROM qp_price_formulas qpf
WHERE ROWNUM < 200;

Line: 179

SELECT
qlh.list_header_id
FROM qp_list_headers_b qlh
WHERE qlh.list_type_code = 'PML'
and ROWNUM < 200;

Line: 186

SELECT
inventory_item_id
FROM mtl_uom_conversions
WHERE (disable_date IS null or disable_date > sysdate)
and inventory_item_id <> 0
and ROWNUM < 200;

Line: 194

SELECT DISTINCT
qpc.currency_header_id
FROM qp_currency_lists_b qpc, qp_list_headers_b qph
WHERE qph.active_flag = 'Y'
and qph.currency_code = qpc.base_currency_code
and ROWNUM < 200;

Line: 206

SELECT sum(c) FROM (
  SELECT /*+ ordered use_nl(qph, qpr) index(qph qp_list_headers_b_pk) index(qpa qp_pricing_attributes_n2) */ count(*) c
  FROM qp_list_lines qpl, qp_list_headers_b qph, qp_pricing_attributes qpa
  WHERE qph.active_flag = 'Y' and qpl.list_header_id = qph.list_header_id and qpl.cache_key = p_cache_key and qpl.list_line_id = qpa.list_line_id(+)
  UNION ALL
  SELECT /*+ ordered use_nl(qph, qpr) index(qph qp_list_headers_b_pk) index(qpa qp_pricing_attributes_n2) */ count(*) c
  FROM qp_list_lines qpl2, qp_rltd_modifiers qpr, qp_list_lines qpl, qp_list_headers_b qph, qp_pricing_attributes qpa
  WHERE qph.active_flag = 'Y' and qpl.list_header_id = qph.list_header_id and qpl2.cache_key = p_cache_key and qpl2.list_line_id = qpr.from_rltd_modifier_id and qpl.list_line_id = qpr.to_rltd_modifier_id and qpl.list_line_id = qpa.list_line_id(+)
);

Line: 217

SELECT sum(c) FROM (
  SELECT count(*) c
  FROM qp_attribute_groups qp, qp_list_headers_b qph
  WHERE qp.pricing_phase_id <> -1 and qph.active_flag = 'Y' and qp.cache_key = p_cache_key and qp.list_header_id = qph.list_header_id
  UNION ALL
  SELECT /*+ ordered use_nl(qph) */ count(*) c
  FROM qp_list_lines qp, qp_list_headers_b qph
  WHERE qph.active_flag = 'Y' and qp.cache_key = p_cache_key and (qp.pattern_id IS not null or (qp.pattern_id is null and qp.qualification_ind in (0, 2))) and qp.list_header_id = qph.list_header_id
);

Line: 228

SELECT sum(c) FROM (
  SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
  FROM qp_list_lines qplag, qp_list_headers_b qph, qp_pricing_attributes qpaq
  WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.pricing_segment_id is not null and qplag.cache_key = p_cache_key and qpaq.comparison_operator_code <> '='
  UNION ALL
  SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
  FROM qp_list_lines qplag, qp_list_headers_b qph, qp_qualifiers qpaq
  WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.segment_id is not null and qplag.cache_key = p_cache_key and qpaq.comparison_operator_code <> '='
  UNION ALL
  SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
  FROM qp_list_lines qpl, qp_rltd_modifiers qpr, qp_list_lines qplag, qp_list_headers_b qph, qp_pricing_attributes qpaq
  WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.pricing_segment_id is not null and qpl.cache_key = p_cache_key
    and qpl.list_line_id = qpr.from_rltd_modifier_id and qplag.list_line_id = qpr.to_rltd_modifier_id and qpaq.comparison_operator_code <> '='
  UNION ALL
  SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
  FROM qp_list_lines qpl, qp_rltd_modifiers qpr, qp_list_lines qplag, qp_list_headers_b qph, qp_qualifiers qpaq
  WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.segment_id is not null and qpl.cache_key = p_cache_key
    and qpl.list_line_id = qpr.from_rltd_modifier_id and qplag.list_line_id = qpr.to_rltd_modifier_id and qpaq.comparison_operator_code <> '='
);

Line: 249

SELECT count(*)
FROM mtl_uom_conversions
WHERE (disable_date is null or disable_date > sysdate) and inventory_item_id = p_cache_key;

Line: 254

SELECT count(*)
FROM mtl_uom_class_conversions
WHERE (disable_date is null or disable_date > sysdate) and inventory_item_id = p_cache_key;

Line: 259

SELECT count(*)
FROM qp_price_formulas
WHERE price_formula_id = p_cache_key;

Line: 264

SELECT count(*)
FROM qp_price_formula_lines
WHERE price_formula_id = p_cache_key;

Line: 269

SELECT count(*)
FROM qp_list_headers_b qph, qp_currency_lists_b qpc
WHERE qph.active_flag = 'Y' and qpc.currency_header_id = p_cache_key and qph.currency_code = qpc.base_currency_code;

Line: 275

SELECT count(*)
FROM   qp_currency_details qpd
WHERE  exists
       (select 'X' from qp_list_headers_b
        where list_type_code in ('AGR', 'PRL')
        and active_flag = 'Y'
        and currency_header_id = p_cache_key)
AND    qpd.currency_header_id = p_cache_key;

Line: 285

SELECT
do_name,
do_size
FROM qp_cache_do_sizes
order by do_name;

Line: 302

l_last_update_date_final_tbl  date_tbl_type;

Line: 303

l_last_updated_by_final_tbl   number_tbl_type;

Line: 304

l_last_update_login_final_tbl number_tbl_type;

Line: 467

    l_cache_key_val_tbl.delete;

Line: 542

    l_cache_key_size_tbl.delete;

Line: 775

   Insert into qp_cache_stats;

Line: 836

    l_last_update_date_final_tbl(i) := sysdate;

Line: 837

    l_last_updated_by_final_tbl(i) := FND_GLOBAL.USER_ID;

Line: 838

    l_last_update_login_final_tbl(i) := FND_GLOBAL.LOGIN_ID;

Line: 845

  DELETE FROM qp_cache_stats;

Line: 848

    INSERT INTO qp_cache_stats (
      name,
      value,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      PROGRAM_APPLICATION_ID,
      PROGRAM_ID,
      PROGRAM_UPDATE_DATE,
      REQUEST_ID)
    VALUES (
      l_cache_type_tbl(i),
      l_cache_size_tbl(i),
      l_creation_date_final_tbl(i),
      l_created_by_final_tbl(i),
      l_last_update_date_final_tbl(i),
      l_last_updated_by_final_tbl(i),
      l_last_update_login_final_tbl(i),
      l_program_appl_id_final_tbl(i),
      l_program_id_final_tbl(i),
      l_program_upd_date_final_tbl(i),
      l_request_id_final_tbl(i)
    );

Line: 879

END UPDATE_CACHE_STATS;

Line: 882

PROCEDURE UPDATE_CAT_NO_PROD_PRICING IS

CURSOR cat_no_prod_mod_csr IS
SELECT DISTINCT 'M', category_id
FROM mtl_item_categories ic
WHERE NOT EXISTS (SELECT 1
                  FROM qp_pricing_attributes pa, qp_list_lines ll
                  WHERE pa.product_attribute_context = 'ITEM' and
                        pa.product_attribute = 'PRICING_ATTRIBUTE1' and
                        pa.product_attr_value = to_char(ic.inventory_item_id) and
                        ll.list_line_id = pa.list_line_id and
                        ll.list_line_type_code not in ('PLL'));

Line: 896

SELECT DISTINCT 'P', category_id
FROM mtl_item_categories ic
WHERE NOT EXISTS (SELECT 1
                  FROM qp_pricing_attributes pa, qp_list_lines ll
                  WHERE pa.product_attribute_context = 'ITEM' and
                        pa.product_attribute = 'PRICING_ATTRIBUTE1' and
                        pa.product_attr_value = to_char(ic.inventory_item_id) and
                        ll.list_line_id = pa.list_line_id and
                        ll.list_line_type_code in ('PLL'));

Line: 914

    dbms_output.put_line('qp_java_engine_cache_pub.update_cat_no_prod_pricing');

Line: 917

  DELETE FROM qp_cache_cat_no_prod_pricing;

Line: 930

    INSERT INTO qp_cache_cat_no_prod_pricing (
      cache_type,
      category_id)
    VALUES (
       l_cat_no_prod_type_tbl(i),
       l_cat_no_prod_cat_tbl(i)
    );

Line: 949

    INSERT INTO qp_cache_cat_no_prod_pricing (
      cache_type,
      category_id)
    VALUES (
      l_cat_no_prod_type_tbl(i),
      l_cat_no_prod_cat_tbl(i)
    );

Line: 959

END UPDATE_CAT_NO_PROD_PRICING;

Line: 1048

  update_cache_stats(
    err_buff,
    retcode
  );