[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT attribute1, 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)
ORDER BY precedence
; --* end CURSOR cur_get_rules(..) *--
SELECT attribute_category,
attribute1,
attribute2
FROM csd_rule_conditions_b
WHERE rule_id = p_rule_id
; --* end CURSOR cur_get_rule_conditions(..) *--
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 7233924
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;
c_SELECT VARCHAR2(7) := 'SELECT ';
l_sql_stmt := c_SELECT || p_defaulting_value || 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 || ')';