APPS.AML_CATEGORY_MIGRATION SQL Statements

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

Line: 40

 |                        Also changed the update statements. Now udpate will be done in
 |                        batches of 10000 records.
 |
 |
 *----------------------------------------------------------------------------------------------------*/

PROCEDURE Load_Schema_Name IS
    l_status            VARCHAR2(2);

Line: 133

   SELECT to_char(interest_type_id) code, description meaning
     FROM as_interest_types_vl
    WHERE  product_category_id IS NULL
    UNION
   SELECT to_char(i.interest_type_id)||'/'||p.interest_code_id code,
          i.description||'/'||p.description meaning
     FROM as_interest_types_vl i, as_interest_codes_vl p
    WHERE i.interest_type_id*1 = p.interest_type_id
      AND p.parent_interest_code_id is null
      AND p.product_category_id IS NULL
    UNION
   SELECT to_char(i.interest_type_id)||'/'||p.interest_code_id||'/'||
          s.interest_code_id code, i.description||'/'||p.description||'/'||s.description meaning
     FROM as_interest_types_vl i,  as_interest_codes_vl p, as_interest_codes_vl s
    WHERE i.interest_type_id = p.interest_type_id
      AND p.interest_type_id = s.interest_type_id*1
      AND s.parent_interest_code_id = p.interest_code_id
      AND s.product_category_id IS NULL;

Line: 153

   SELECT distinct to_char(interest.interest_type_id) code ,interest.description meaning
     FROM as_sales_lead_lines line, as_interest_types_vl interest
    WHERE line.interest_type_id = interest.interest_type_id
      AND line.primary_interest_code_id is null
      AND line.secondary_interest_code_id is null
      AND interest.product_category_id is null
    UNION
   SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id code,
          interest.description||'/'||pic.description meaning
     FROM as_sales_lead_lines line, as_interest_codes_vl pic, as_interest_types_vl interest
    WHERE line.primary_interest_code_id = pic.interest_code_id
      AND pic.interest_type_id = interest.interest_type_id
      AND pic.parent_interest_code_id is null
      AND line.secondary_interest_code_id is null
      AND pic.product_category_id is null
    UNION
   SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id||'/'||sic.interest_code_id code,
          interest.description||'/'||pic.description||'/'||sic.description meaning
     FROM as_sales_lead_lines line, as_interest_codes_vl sic, as_interest_codes_vl pic, as_interest_types_vl interest
    WHERE line.secondary_interest_code_id = sic.interest_code_id
      AND line.primary_interest_code_id = sic.parent_interest_code_id
      AND sic.interest_type_id = interest.interest_type_id
	       and sic.parent_interest_code_id = pic.interest_code_id
	       and pic.product_category_id is null;

Line: 179

   SELECT to_char(lead.interest_type_id) code
     FROM as_sales_lead_lines lead
    WHERE lead.interest_type_id not in (SELECT int.interest_type_id
                                          FROM as_interest_types_b int)
    UNION
   SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id code
     FROM as_sales_lead_lines lead
    WHERE lead.primary_interest_code_id not in (SELECT pic.interest_code_id
                                                  FROM as_interest_codes_b pic
	                                         WHERE pic.parent_interest_code_id IS null)
    UNION
   SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id||'/'||lead.secondary_interest_code_id  code
     FROM as_sales_lead_lines lead
    WHERE lead.secondary_interest_code_id not in (SELECT  sic.interest_code_id
                                                    FROM as_interest_codes_b sic
                                                   WHERE sic.parent_interest_code_id is not null) ;

Line: 297

SELECT min(sales_lead_line_id)
  FROM as_sales_lead_lines;

Line: 301

SELECT max(sales_lead_line_id)
  FROM as_sales_lead_lines;

Line: 305

SELECT as_sales_lead_lines_s.nextval
  FROM dual;

Line: 309

SELECT trigger_name
  FROM all_triggers
 WHERE table_owner = c_schema_name
   AND trigger_name = 'AS_SALES_LEAD_LINES_BIUD'
   AND nvl(status,'DISABLED') = 'ENABLED';

Line: 366

        update as_sales_lead_lines l
           set (category_id, category_set_id) =
                (select int.product_category_id, int.product_cat_set_id
                from as_interest_types_b int
                where l.interest_type_id = int.interest_type_id)
         where l.sales_lead_line_id >= l_count
           and l.sales_lead_line_id < l_count+G_BATCH_SIZE
           and l.interest_type_id is not null
           and l.primary_interest_code_id is null
           and l.secondary_interest_code_id is null;

Line: 378

        update as_sales_lead_lines l
           set (category_id, category_set_id) =
	       (select int.product_category_id, int.product_cat_set_id
                  from as_interest_codes_b int
                 where l.primary_interest_code_id = int.interest_code_id)
         where l.sales_lead_line_id >= l_count
           and l.sales_lead_line_id < l_count+G_BATCH_SIZE
           and l.primary_interest_code_id is not null
           and l.secondary_interest_code_id is null;

Line: 389

        update as_sales_lead_lines l
           set (category_id, category_set_id) =
               (select int.product_category_id, int.product_cat_set_id
                from as_interest_codes_b int
                where l.secondary_interest_code_id = int.interest_code_id)
         where l.sales_lead_line_id >= l_count
           and l.sales_lead_line_id < l_count+G_BATCH_SIZE
           and l.secondary_interest_code_id is not null;