[Home] [Help]
2016:
2017: cursor get_rule_destination is
2018: SELECT dest_org_id,
2019: dest_subinv
2020: FROM csp_return_routing_rules
2021: WHERE rule_id = v_return_rule_id;
2022: BEGIN
2023:
2024: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2239: decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
2240: r.rule_id
2241: from
2242: (
2243: select rule_id from csp_return_routing_rules where source_org_id = v_source_org_id
2244: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2245: and sysdate between nvl(start_active_date, sysdate)
2246: and nvl(end_active_date, sysdate + 1)
2247: union
2244: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2245: and sysdate between nvl(start_active_date, sysdate)
2246: and nvl(end_active_date, sysdate + 1)
2247: union
2248: select rule_id from csp_return_routing_rules where source_subinv = v_source_subinv
2249: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2250: and sysdate between nvl(start_active_date, sysdate)
2251: and nvl(end_active_date, sysdate + 1)
2252: union
2249: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2250: and sysdate between nvl(start_active_date, sysdate)
2251: and nvl(end_active_date, sysdate + 1)
2252: union
2253: select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
2254: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2255: and sysdate between nvl(start_active_date, sysdate)
2256: and nvl(end_active_date, sysdate + 1)
2257: union
2254: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2255: and sysdate between nvl(start_active_date, sysdate)
2256: and nvl(end_active_date, sysdate + 1)
2257: union
2258: select rule_id from csp_return_routing_rules
2259: where inv_cat_set_id in (select distinct category_set_id
2260: from MTL_ITEM_CATEGORIES
2261: where inventory_item_id = v_item_id
2262: and organization_id = v_source_org_id)
2263: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2264: and sysdate between nvl(start_active_date, sysdate)
2265: and nvl(end_active_date, sysdate + 1)
2266: union
2267: select rule_id from csp_return_routing_rules
2268: where inv_cat_id in (select distinct category_id
2269: from MTL_ITEM_CATEGORIES
2270: where inventory_item_id = v_item_id
2271: and organization_id = v_source_org_id)
2272: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2273: and sysdate between nvl(start_active_date, sysdate)
2274: and nvl(end_active_date, sysdate + 1)
2275: union
2276: select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
2277: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2278: and sysdate between nvl(start_active_date, sysdate)
2279: and nvl(end_active_date, sysdate + 1)
2280: union
2277: and nvl(source_type, 'I') = 'I' and enabled = 'Y'
2278: and sysdate between nvl(start_active_date, sysdate)
2279: and nvl(end_active_date, sysdate + 1)
2280: union
2281: select rule_id from csp_return_routing_rules
2282: where nvl(source_type, 'I') = 'I' and enabled = 'Y'
2283: and sysdate between nvl(start_active_date, sysdate)
2284: and nvl(end_active_date, sysdate + 1)
2285: and decode(source_org_id, 0, null, source_org_id) is null
2288: and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
2289: and decode(inv_cat_id, 0, null, inv_cat_id) is null
2290: and decode(inv_item_id, 0, null, inv_item_id) is null
2291: ) prob,
2292: csp_return_routing_rules r,
2293: MTL_ITEM_CATEGORIES mtl
2294: where r.rule_id = prob.rule_id
2295: and mtl.inventory_item_id = v_item_id
2296: and mtl.organization_id = v_source_org_id
2320: decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
2321: r.rule_id
2322: from
2323: (
2324: select rule_id from csp_return_routing_rules where source_terr_id = v_source_terr_id
2325: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2326: and sysdate between nvl(start_active_date, sysdate)
2327: and nvl(end_active_date, sysdate + 1)
2328: union
2325: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2326: and sysdate between nvl(start_active_date, sysdate)
2327: and nvl(end_active_date, sysdate + 1)
2328: union
2329: select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
2330: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2331: and sysdate between nvl(start_active_date, sysdate)
2332: and nvl(end_active_date, sysdate + 1)
2333: union
2330: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2331: and sysdate between nvl(start_active_date, sysdate)
2332: and nvl(end_active_date, sysdate + 1)
2333: union
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
2352: select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
2353: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2354: and sysdate between nvl(start_active_date, sysdate)
2355: and nvl(end_active_date, sysdate + 1)
2356: union
2353: and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2354: and sysdate between nvl(start_active_date, sysdate)
2355: and nvl(end_active_date, sysdate + 1)
2356: union
2357: select rule_id from csp_return_routing_rules
2358: where nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
2359: and sysdate between nvl(start_active_date, sysdate)
2360: and nvl(end_active_date, sysdate + 1)
2361: and decode(source_terr_id, 0, null, source_terr_id) is null
2363: and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
2364: and decode(inv_cat_id, 0, null, inv_cat_id) is null
2365: and decode(inv_item_id, 0, null, inv_item_id) is null
2366: ) prob,
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
2742: crrr.dest_subinv,
2743: cpp.hz_location_id,
2744: hoa.location_id,
2745: ood.operating_unit
2746: from csp_return_routing_rules crrr,
2747: hr_organization_units hoa,
2748: csp_planning_parameters cpp,
2749: org_organization_definitions ood
2750: where crrr.rule_id = p_return_rule_id
2758: crrr.dest_subinv,
2759: cpp.hz_location_id,
2760: msi.location_id,
2761: ood.operating_unit
2762: from csp_return_routing_rules crrr,
2763: mtl_secondary_inventories msi,
2764: csp_planning_parameters cpp,
2765: org_organization_definitions ood
2766: where crrr.rule_id = p_return_rule_id