DBA Data[Home] [Help]

APPS.CSD_RULES_ENGINE_PVT SQL Statements

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

Line: 28

        SELECT fnd_api.g_true
        INTO l_return_val
        FROM mtl_item_categories mic,wip_entities we
        WHERE mic.inventory_item_id = p_mtl_txn_item_id
          AND mic.category_id = p_criterion
          AND mic.organization_id = we.organization_id
          AND we.wip_entity_id = p_wip_entity_id;
Line: 109

     SELECT attribute1, attribute2, attribute3, attribute4, rule_id, value_type_code, attribute_category
     FROM   csd_rules_b
     where  rule_type_code = p_rule_type
     AND    NVL(entity_attribute_type, c_TEMP_CHAR)=NVL(p_attr_type, c_TEMP_CHAR)
     AND    NVL(entity_attribute_code, c_TEMP_CHAR)=NVL(p_attr_code, c_TEMP_CHAR)
     AND    attribute1 = decode(p_rule_type,
                                'SOO', p_event_type,
                                attribute1)
     ORDER BY precedence
   ; --* end CURSOR cur_get_rules(..) *--
Line: 125

      SELECT decode(p_rule_type, 'SOO', soo_attribute_category, attribute_category) attribute_category,
             decode(p_rule_type, 'SOO', soo_attribute1, attribute1) attribute1,
             decode(p_rule_type, 'SOO', soo_attribute2, attribute2) attribute2
      FROM   csd_rule_conditions_b
      WHERE  rule_id = p_rule_id
   ; --* end CURSOR cur_get_rule_conditions(..) *--
Line: 509

     select cr.object_version_number
     into l_object_version_number
     from csd_repairs cr
     where cr.repair_line_id = p_rule_input_rec.repair_line_id;
Line: 521

     csd_repairs_pvt.update_ro_status(p_api_version        => p_api_version_number,
                                      p_commit             => fnd_api.g_false,
                                      p_init_msg_list      => p_init_msg_list,
                                      p_validation_level   => p_validation_level,
                                      x_return_status      => x_return_status,
                                      x_msg_count          => x_msg_count,
                                      x_msg_data           => x_msg_data,
                                      p_repair_status_Rec  => l_repair_status_rec,
                                      p_status_control_rec => l_status_upd_control_rec,
                                      x_object_version_number => x_object_version_number);
Line: 1244

                SELECT hl.city
                INTO G_SHIP_TO_CITY_CACHE(l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID)
                FROM hz_locations hl, hz_party_sites hps,
                    hz_party_site_uses hpsu
                WHERE hpsu.party_site_use_id = l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID
                  AND hpsu.site_use_type = 'SHIP_TO'
                  AND hpsu.party_site_id = hps.party_site_id
                  AND hps.location_id = hl.location_id;
Line: 1643

      SELECT a.customer_id,
             a.account_id,
             a.bill_to_site_use_id,
             a.ship_to_site_use_id,
             a.inventory_item_id,
             c.category_id,
             a.contract_id,
             a.problem_code,
             a.customer_product_id,
             b.inventory_item_id,  -- swai: 12.1.1 ER 7233924i
             b.incident_id,
             c.account_id,
             b.contract_line_id,
             b.flow_status_id,
             b.resource_id,
             b.ro_priority_code
      FROM   CSD_INCIDENTS_V a, CSD_REPAIRS b, CS_INCIDENTS_B_SEC c
      WHERE  a.incident_id = b.incident_id
      AND    a.incident_id = c.incident_id
      AND    b.repair_line_Id =  p_repair_line_id;
Line: 1665

      SELECT we.primary_item_id,
             we.organization_id,
             hrla.country,
             orgv.operating_unit
      FROM   wip_entities we, org_organization_definitions orgv,
             PER_ORGANIZATION_UNITS porg, hr_locations_all hrla
      WHERE  we.wip_entity_id = p_wip_entity_id
             and we.organization_id = orgv.organization_id
             and we.organization_id = porg.organization_id
             and hrla.location_id = porg.location_id
             ;
Line: 1773

   c_SELECT    VARCHAR2(7)   := 'SELECT ';
Line: 1847

         l_sql_stmt := c_SELECT ||
                       replace(p_defaulting_value, G_REPAIR_LINE_ID_PARAM, to_char(p_rule_input_rec.repair_line_id)) || --bug#12536477
                       c_FROM_DUAL;
Line: 1911

     SELECT b.country
     FROM   hz_party_sites a,
            hz_locations b,
            hz_party_site_uses c
     WHERE  a.location_id = b.location_id
     AND    a.party_site_id = c.party_site_id
     AND    c.party_site_use_id = p_site_use_id
   ; --* end CURSOR cur_get_country_code *--
Line: 1962

      SELECT 'X'
      FROM   mtl_item_categories_v
      WHERE  inventory_item_id = p_inventory_item_id
        and  category_id = p_category_id
        and  organization_id = cs_std.get_item_valdn_orgzn_id;
Line: 2026

      SELECT promise_date
      FROM   csd_repairs
      WHERE  repair_line_id = p_repair_line_id
   ; --* end CURSOR get_promise_date *--
Line: 2079

      SELECT resolve_by_date
      FROM   csd_repairs
      WHERE  repair_line_id = p_repair_line_id
   ; --* end CURSOR get_resolve_by_date *--
Line: 2145

      SELECT return_by_date, prod_txn_status
      FROM   csd_product_txns_v
      WHERE  action_type = p_action_type
      AND    action_code LIKE p_action_code  -- for 3rd party, pass in '%'
      AND    repair_line_id = p_repair_line_id
   ;  --* end CURSOR get_return_by_date *--
Line: 2213

      SELECT   MAX(a.date_closed)
      FROM     csd_repairs a
      WHERE    a.customer_product_id = p_instance_id
      AND      a.date_closed IS NOT NULL
   ; --* end cur_get_latest_repair_date *--
Line: 2220

      SELECT creation_date
      FROM   csd_repairs
      WHERE  repair_line_id = p_repair_line_id
   ; -- end* cur_get_creation_date*--
Line: 2292

      SELECT count(a.repair_line_id)
      FROM   csd_repairs a
      WHERE  a.customer_product_id = p_instance_id
      AND    a.date_closed   BETWEEN sysdate - p_period
                             AND     sysdate
   ; --* end cur_get_chronic_repairs *--
Line: 2359

      SELECT contract_line_id
      FROM   csd_repairs
      WHERE  repair_line_id = p_repair_line_id
   ; --* end cur_get_ro_contract_id *--
Line: 2368

      SELECT least(nvl(end_date, date_terminated), nvl(date_terminated, end_date))
      FROM   OKC_K_Lines_B
      WHERE  id = p_contract_line_id
   ;
Line: 2433

      SELECT customer_product_id
      FROM   csd_repairs
      WHERE  repair_line_id = p_repair_line_id
   ;--* end cur_get_instance_id *--
Line: 2477

    SELECT rule_condition_id,
           rule_id,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM CSD_RULE_CONDITIONS_B
    WHERE rule_id = p_rule_id;
Line: 2508

    l_sql_query := 'select dra.repair_line_id from csd_repairs dra, cs_incidents_b_sec csb '
                || 'where csb.incident_id = dra.incident_id';
Line: 2540

                    l_join_stmt :=  'csb.bill_to_site_use_id in (select hpsu.party_site_use_id'
                                 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
                                 || ' where hps.location_id = hl.location_id'
                                 || ' and hps.party_site_id = hpsu.party_site_id'
                                 || ' and hpsu.party_site_use_id = csb.bill_to_site_use_id'
                                 || ' and hl.country '
                                 || l_str_condition || ')';
Line: 2548

                    l_join_stmt :=  'csb.ship_to_site_use_id in (select hpsu.party_site_use_id'
                                 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
                                 || ' where hps.location_id = hl.location_id'
                                 || ' and hps.party_site_id = hpsu.party_site_id'
                                 || ' and hpsu.party_site_use_id = csb.ship_to_site_use_id'
                                 || ' and hl.country '
                                 || l_str_condition || ')';
Line: 2577

                       l_join_stmt :=  l_join_stmt || ' (select ''X'''
                                 || ' from   mtl_item_categories_v  cat'
                                 || ' where  cat.inventory_item_id = dra.inventory_item_id'
                                 || ' and  cat.organization_id = cs_std.get_item_valdn_orgzn_id'
                                 || ' and  cat.category_id = '
                                 || l_rule_condition_rec.attribute2 || ')';
Line: 2590

                    l_join_stmt :=  'dra.repair_line_id in (select prod.repair_line_id'
                                 || ' from csd_product_txns_v prod'
                                 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
                                 || ' AND prod.action_code = ''' || G_ACTION_CODE_EXCHANGE || ''''
                                 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
                                 || ' AND (prod.return_by_date - sysdate) '
                                 || l_num_condition || ')';
Line: 2598

                    l_join_stmt :=  'dra.repair_line_id in (select prod.repair_line_id'
                                 || ' from csd_product_txns_v prod'
                                 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
                                 || ' AND prod.action_code = ''' || G_ACTION_CODE_LOANER || ''''
                                 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
                                 || ' AND (prod.return_by_date - sysdate) '
                                 || l_num_condition || ')';
Line: 2606

                    l_join_stmt :=  'dra.repair_line_id in (select prod.repair_line_id'
                                 || ' from csd_product_txns_v prod'
                                 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA_THIRD_PTY || ''''
                                 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
                                 || ' AND (prod.return_by_date - sysdate) '
                                 || l_num_condition || ')';
Line: 2614

                    l_join_stmt :=  'dra.repair_line_id in (select prod.repair_line_id'
                                 || ' from csd_product_txns_v prod'
                                 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
                                 || ' AND prod.action_code = ''' || G_ACTION_CODE_CUST_PROD || ''''
                                 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED'''
                                 || ' AND (prod.return_by_date - sysdate) '
                                 || l_num_condition || ')';
Line: 2623

                    l_join_stmt :=  'sysdate - ( SELECT   MAX(dra2.date_closed)'
                                             || ' FROM  csd_repairs dra2 '
                                             || ' WHERE dra2.customer_product_id = dra.customer_product_id '
                                             || ' AND      dra2.date_closed IS NOT NULL) '
                                             || l_num_condition;
Line: 2629

                    l_join_stmt :=  '(SELECT count(dra2.repair_line_id) '
                                  || ' FROM   csd_repairs dra2 '
                                  || ' WHERE  dra2.customer_product_id = dra.customer_product_id '
                                  || ' AND dra2.date_closed BETWEEN sysdate - '
                                  || ' nvl(FND_PROFILE.VALUE(''CSD_QUALITY_CHECK_PERIOD''), 0) '
                                  || ' AND sysdate) ' || l_num_condition;
Line: 2638

                    l_join_stmt :=  'dra.contract_line_id in (select okl.id'
                                 || ' from okc_k_lines_b okl'
                                 || ' where  okl.id = dra.contract_line_id'
                                 || ' AND (least(nvl(end_date, date_terminated), nvl(date_terminated, end_date))  - sysdate)'
                                 -- || ' AND (okl.end_date - sysdate) '
                                 || l_num_condition || ')';