The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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(..) *--
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(..) *--
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;
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);
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;
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;
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
;
c_SELECT VARCHAR2(7) := 'SELECT ';
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;
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 *--
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;
SELECT promise_date
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
; --* end CURSOR get_promise_date *--
SELECT resolve_by_date
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
; --* end CURSOR get_resolve_by_date *--
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 *--
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 *--
SELECT creation_date
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
; -- end* cur_get_creation_date*--
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 *--
SELECT contract_line_id
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
; --* end cur_get_ro_contract_id *--
SELECT least(nvl(end_date, date_terminated), nvl(date_terminated, end_date))
FROM OKC_K_Lines_B
WHERE id = p_contract_line_id
;
SELECT customer_product_id
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
;--* end cur_get_instance_id *--
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;
l_sql_query := 'select dra.repair_line_id from csd_repairs dra, cs_incidents_b_sec csb '
|| 'where csb.incident_id = dra.incident_id';
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 || ')';
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 || ')';
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 || ')';
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 || ')';
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 || ')';
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 || ')';
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 || ')';
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;
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;
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 || ')';