[Home] [Help]
388: and inventory_item_id in
389: (
390: select /*+ leading(mcat bac) */ DISTINCT config_item_id -- bug 13876670 added hint
391: from bom_ato_configurations bac,
392: mtl_item_categories mcat
393: where bac.base_model_id = mcat.inventory_item_id
394: and mcat.category_id = p_cat_id
395: -- bug 13876670
396: and mcat.category_set_id = p_category_set_id
829: --
830: -- Removing from CTO category
831: --
832: IF (p_item = 2) THEN
833: delete from mtl_item_categories
834: where category_id = p_cat_id;
835:
836: WriteToLog('Rows deleted from category::'||sql%rowcount, 2);
837: END IF;
1766: --
1767: CURSOR c_bac(p_cat_id number) IS
1768: -- individual configs not in bcol and having item attribute 3
1769: select /*+ ORDERED */ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1770: from mtl_item_categories mcat, --Bugfix 6617686: Changed the order of tables
1771: mtl_system_items msi,
1772: bom_ato_configurations bac
1773: where NOT EXISTS
1774: (select 'exists'
1790: --
1791: CURSOR c_bac_top(p_cat_id number, p_category_set_id number) IS
1792: -- individual configs not in bcol and having item attribute 3
1793: select distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1794: from mtl_item_categories mcat, --Bugfix 6617686 Changed the order of tables
1795: mtl_system_items msi,
1796: bom_ato_configurations bac
1797: -- item attribute is 3
1798: where bac.base_model_id = msi.inventory_item_id
1938: from bom_cto_order_lines bcol1
1939: , bom_cto_order_lines bcol2
1940: , oe_order_lines_all oel
1941: , mtl_system_items msi
1942: , mtl_item_categories mcat
1943: -- select entire configuration
1944: where bcol2.ato_line_id = bcol1.ato_line_id
1945: and bcol1.config_item_id is not null
1946: -- for configs whose models are in CTO category
2046: , bom_cto_order_lines bcol2
2047: , bom_ato_configurations bac
2048: , oe_order_lines_all oel
2049: , mtl_system_items msi
2050: , mtl_item_categories mcat
2051: -- base model has item attr = 3
2052: where bac.base_model_id = msi.inventory_item_id
2053: and bac.organization_id = msi.organization_id
2054: and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
2151: , bcol.QTY_PER_PARENT_MODEL
2152: , 'UPG' --STATUS
2153: --, bcol.SEQUENCE
2154: , nvl(msi.config_orgs, '1')
2155: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
2156: , mtl_system_items msi
2157: , bom_ato_configurations bac
2158: , bom_cto_order_lines bcol
2159: -- base model has item attr = 3
2318: and bcol.ship_from_org_id = msi2.organization_id
2319: and oel.open_flag = 'Y' -- 13362916 removed NVL
2320: and oel.ato_line_id in -- bug 6617686 connect using oel rather than bcol to get better filtering
2321: (select /*+ leading(MCAT) */ distinct bcol2.ato_line_id --Bugfix 6617686 Added a hint
2322: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
2323: , mtl_system_items msi
2324: , bom_cto_order_lines bcol2
2325: where bcol2.config_item_id is not null
2326: and bcol2.inventory_item_id = msi.inventory_item_id
2424: , bom_cto_order_lines bcol2
2425: , bom_ato_configurations bac
2426: , oe_order_lines_all oel
2427: , mtl_system_items msi
2428: , mtl_item_categories mcat
2429: -- base model has item attr = 3
2430: where bac.base_model_id = msi.inventory_item_id
2431: and bac.organization_id = msi.organization_id
2432: and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
2533: , nvl(msi.CONFIG_ORGS, '1')
2534: from bom_ato_configurations bac
2535: , bom_cto_order_lines bcol
2536: , mtl_system_items msi
2537: , mtl_item_categories mcat
2538: -- base model has item attr = 3
2539: where bac.base_model_id = msi.inventory_item_id
2540: and bac.organization_id = msi.organization_id
2541: and nvl(msi.config_orgs, '1') = '3'
4317: where msim.inventory_item_id = msic.base_item_id
4318: and msim.organization_id = msic.organization_id)
4319: where msic.inventory_item_id in (select msi.inventory_item_id
4320: from mtl_system_items_b msi,
4321: mtl_item_categories mcat
4322: where msi.base_item_id = mcat.inventory_item_id
4323: and mcat.category_id = p_cat_id)
4324: and exists (select 'x' from mtl_system_items_b msim
4325: where msim.inventory_item_id = msic.base_item_id