[Home] [Help]
2334: select rule_id from csp_return_routing_rules
2335: where inv_cat_set_id in (select distinct category_set_id
2336: from MTL_ITEM_CATEGORIES
2337: where inventory_item_id = v_item_id
2338: and organization_id = cs_std.get_item_valdn_orgzn_id)
2339: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2340: and sysdate between nvl(start_active_date, sysdate)
2341: and nvl(end_active_date, sysdate + 1)
2342: union
2343: select rule_id from csp_return_routing_rules
2344: where inv_cat_id in (select distinct category_id
2345: from MTL_ITEM_CATEGORIES
2346: where inventory_item_id = v_item_id
2347: and organization_id = cs_std.get_item_valdn_orgzn_id)
2348: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2349: and sysdate between nvl(start_active_date, sysdate)
2350: and nvl(end_active_date, sysdate + 1)
2351: union
2367: csp_return_routing_rules r,
2368: MTL_ITEM_CATEGORIES mtl
2369: where r.rule_id = prob.rule_id
2370: and mtl.inventory_item_id = v_item_id
2371: and mtl.organization_id = cs_std.get_item_valdn_orgzn_id
2372: and nvl(decode(r.source_terr_id, 0, null, r.source_terr_id), v_source_terr_id) = v_source_terr_id
2373: and nvl(r.return_type, v_ret_trans_type) = v_ret_trans_type
2374: and nvl(decode(r.inv_cat_set_id, 0, null, r.inv_cat_set_id), mtl.category_set_id) = mtl.category_set_id
2375: and nvl(decode(r.inv_cat_id, 0, null, r.inv_cat_id), mtl.category_id) = mtl.category_id