[Home] [Help]
356: and inventory_item_id in
357: (
358: select config_item_id
359: from bom_ato_configurations bac,
360: mtl_item_categories mcat
361: where bac.base_model_id = mcat.inventory_item_id
362: and mcat.category_id = p_cat_id
363: and not exists
364: (select 'exists'
653: --
654: -- Removing from CTO category
655: --
656: IF (p_item = 2) THEN
657: delete from mtl_item_categories
658: where category_id = p_cat_id;
659:
660: WriteToLog('Rows deleted from category::'||sql%rowcount, 2);
661: END IF;
1551: --
1552: CURSOR c_bac(p_cat_id number) IS
1553: -- individual configs not in bcol and having item attribute 3
1554: select /*+ ORDERED */ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1555: from mtl_item_categories mcat, --Bugfix 6617686: Changed the order of tables
1556: mtl_system_items msi,
1557: bom_ato_configurations bac
1558: where NOT EXISTS
1559: (select 'exists'
1575: --
1576: CURSOR c_bac_top(p_cat_id number) IS
1577: -- individual configs not in bcol and having item attribute 3
1578: select /*+ ORDERED*/ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1579: from mtl_item_categories mcat, --Bugfix 6617686 Changed the order of tables
1580: mtl_system_items msi,
1581: bom_ato_configurations bac
1582: -- item attribute is 3
1583: where bac.base_model_id = msi.inventory_item_id
1720: from bom_cto_order_lines bcol1
1721: , bom_cto_order_lines bcol2
1722: , oe_order_lines_all oel
1723: , mtl_system_items msi
1724: , mtl_item_categories mcat
1725: -- select entire configuration
1726: where bcol2.ato_line_id = bcol1.ato_line_id
1727: and bcol1.config_item_id is not null
1728: -- for configs whose models are in CTO category
1827: , bom_cto_order_lines bcol2
1828: , bom_ato_configurations bac
1829: , oe_order_lines_all oel
1830: , mtl_system_items msi
1831: , mtl_item_categories mcat
1832: -- base model has item attr = 3
1833: where bac.base_model_id = msi.inventory_item_id
1834: and bac.organization_id = msi.organization_id
1835: and nvl(msi.config_orgs, '1') = '3'
1930: , bcol.QTY_PER_PARENT_MODEL
1931: , 'UPG' --STATUS
1932: --, bcol.SEQUENCE
1933: , nvl(msi.config_orgs, '1')
1934: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
1935: , mtl_system_items msi
1936: , bom_ato_configurations bac
1937: , bom_cto_order_lines bcol
1938: -- base model has item attr = 3
2095: and bcol.ship_from_org_id = msi2.organization_id
2096: and nvl(oel.open_flag, 'N') = 'Y'
2097: and oel.ato_line_id in -- bug 6617686 connect using oel rather than bcol to get better filtering
2098: (select /*+ leading(MCAT) */ distinct bcol2.ato_line_id --Bugfix 6617686 Added a hint
2099: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
2100: , mtl_system_items msi
2101: , bom_cto_order_lines bcol2
2102: where bcol2.config_item_id is not null
2103: and bcol2.inventory_item_id = msi.inventory_item_id
2198: , bom_cto_order_lines bcol2
2199: , bom_ato_configurations bac
2200: , oe_order_lines_all oel
2201: , mtl_system_items msi
2202: , mtl_item_categories mcat
2203: -- base model has item attr = 3
2204: where bac.base_model_id = msi.inventory_item_id
2205: and bac.organization_id = msi.organization_id
2206: and nvl(msi.config_orgs, '1') = '3'
2304: , nvl(msi.CONFIG_ORGS, '1')
2305: from bom_ato_configurations bac
2306: , bom_cto_order_lines bcol
2307: , mtl_system_items msi
2308: , mtl_item_categories mcat
2309: -- base model has item attr = 3
2310: where bac.base_model_id = msi.inventory_item_id
2311: and bac.organization_id = msi.organization_id
2312: and nvl(msi.config_orgs, '1') = '3'
3937: where msim.inventory_item_id = msic.base_item_id
3938: and msim.organization_id = msic.organization_id)
3939: where msic.inventory_item_id in (select msi.inventory_item_id
3940: from mtl_system_items_b msi,
3941: mtl_item_categories mcat
3942: where msi.base_item_id = mcat.inventory_item_id
3943: and mcat.category_id = p_cat_id)
3944: and exists (select 'x' from mtl_system_items_b msim
3945: where msim.inventory_item_id = msic.base_item_id