[Home] [Help]
1847: --
1848: UPDATE pon_item_prices_interface p1
1849: SET (item_description, category_name) =
1850: (SELECT poj.job_long_description,
1851: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
1852: FROM po_job_associations poj,
1853: mtl_categories_kfv cat
1854: WHERE cat.category_id = poj.category_id AND
1855: poj.job_id = p1.job_id)
1905: SET (item_description, additional_job_details, category_id, category_name) =
1906: (SELECT poj.job_description,
1907: decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
1908: cat.category_id,
1909: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
1910: FROM po_job_associations poj,
1911: mtl_categories_kfv cat
1912: WHERE poj.job_id = p1.job_id
1913: AND cat.category_id = poj.category_id)
2067:
2068: -- default the category if not entered for valid item numbers
2069:
2070: update pon_item_prices_interface p1
2071: set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2072: from mtl_default_sets_view mdsv,
2073: mtl_item_categories mic,
2074: mtl_categories_kfv mck
2075: where mdsv.functional_area_id = 2 and
2140: organization_id = l_inventory_org_id);
2141:
2142:
2143: update pon_item_prices_interface p1
2144: set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2145: from
2146: MTL_CATEGORIES_KFV mck
2147: ,PO_LINE_TYPES plt
2148: where
2237: -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2238: update pon_item_prices_interface p
2239: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2240: FROM (select category_id,
2241: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2242: ENABLED_FLAG,
2243: START_DATE_ACTIVE,
2244: END_DATE_ACTIVE,
2245: STRUCTURE_ID,
2265: -- For those which were not caught in the previous SQL
2266: update pon_item_prices_interface p
2267: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2268: FROM (select category_id,
2269: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2270: ENABLED_FLAG,
2271: START_DATE_ACTIVE,
2272: END_DATE_ACTIVE,
2273: STRUCTURE_ID,
2304: and category_name <> 'CAT_NON_ENTERED'
2305: AND category_id <> -1;
2306: */
2307: update pon_item_prices_interface p
2308: set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2309: from mtl_categories_kfv i
2310: where i.category_id = p.category_id)
2311: where batch_id = p_batch_id
2312: and category_name <> 'CAT_NON_ENTERED'
2354: batch_id = p_batch_id and
2355: purchase_basis <> 'TEMP LABOR' and
2356: order_type_lookup_code <> 'FIXED PRICE' and
2357: item_number is not null and
2358: category_name <> (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2359: from mtl_default_sets_view mdsv,
2360: mtl_item_categories mic,
2361: mtl_categories_kfv mck
2362: where mdsv.functional_area_id = 2 and