[Home] [Help]
2037: --
2038: UPDATE pon_item_prices_interface p1
2039: SET (item_description, category_name) =
2040: (SELECT poj.job_long_description,
2041: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
2042: FROM po_job_associations poj,
2043: mtl_categories_kfv cat
2044: WHERE cat.category_id = poj.category_id AND
2045: poj.job_id = p1.job_id)
2095: SET (item_description, additional_job_details, category_id, category_name) =
2096: (SELECT poj.job_description,
2097: decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
2098: cat.category_id,
2099: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
2100: FROM po_job_associations poj,
2101: mtl_categories_kfv cat
2102: WHERE poj.job_id = p1.job_id
2103: AND cat.category_id = poj.category_id)
2263:
2264: -- default the category if not entered for valid item numbers
2265:
2266: update pon_item_prices_interface p1
2267: set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2268: from mtl_default_sets_view mdsv,
2269: mtl_item_categories mic,
2270: mtl_categories_kfv mck
2271: where mdsv.functional_area_id = 2 and
2336: organization_id = l_inventory_org_id);
2337:
2338:
2339: update pon_item_prices_interface p1
2340: set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2341: from
2342: MTL_CATEGORIES_KFV mck
2343: ,PO_LINE_TYPES plt
2344: where
2440: -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2441: update pon_item_prices_interface p
2442: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2443: FROM (select category_id,
2444: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2445: ENABLED_FLAG,
2446: START_DATE_ACTIVE,
2447: END_DATE_ACTIVE,
2448: STRUCTURE_ID,
2468: -- For those which were not caught in the previous SQL
2469: update pon_item_prices_interface p
2470: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2471: FROM (select category_id,
2472: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2473: ENABLED_FLAG,
2474: START_DATE_ACTIVE,
2475: END_DATE_ACTIVE,
2476: STRUCTURE_ID,
2507: and category_name <> 'CAT_NON_ENTERED'
2508: AND category_id <> -1;
2509: */
2510: update pon_item_prices_interface p
2511: set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2512: from mtl_categories_kfv i
2513: where i.category_id = p.category_id)
2514: where batch_id = p_batch_id
2515: and category_name <> 'CAT_NON_ENTERED'
2557: batch_id = p_batch_id and
2558: purchase_basis <> 'TEMP LABOR' and
2559: order_type_lookup_code <> 'FIXED PRICE' and
2560: item_number is not null and
2561: category_name <> (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2562: from mtl_default_sets_view mdsv,
2563: mtl_item_categories mic,
2564: mtl_categories_kfv mck
2565: where mdsv.functional_area_id = 2 and