[Home] [Help]
315: delete from mrp_sr_assignments
316: where assignment_set_id = l_mrp_aset_id
317: and inventory_item_id in
318: (select config_item_id
319: from bom_ato_configurations bac
320: where not exists
321: (select 'exists'
322: from bom_cto_order_lines_upg bcolu
323: where bcolu.config_item_id = bac.config_item_id
387: where assignment_set_id = l_mrp_aset_id
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
464: --Bugfix 14359805
465: --Exists in BAC
466: and exists
467: (select 'exists'
468: from bom_ato_configurations bac
469: where bac.config_item_id = p_config_id)
470: -- not being upgraded
471: and not exists
472: (select 'exists'
910: --
911: CURSOR c_bac IS
912: -- individual configs not in bcol and having item attribute 3
913: select distinct bac.config_item_id config_id
914: from bom_ato_configurations bac,
915: mtl_system_items msi
916: where NOT EXISTS
917: (select 'exists'
918: from bom_cto_order_lines_upg bcolu
928: --
929: CURSOR c_bac_top IS
930: -- individual configs not in bcol and having item attribute 3
931: select distinct bac.config_item_id config_id
932: from bom_ato_configurations bac,
933: mtl_system_items msi
934: -- item attribute is 3
935: where bac.base_model_id = msi.inventory_item_id
936: and bac.organization_id = msi.organization_id
937: and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
938: -- and is top parent with attribute 3
939: and NOT EXISTS
940: (select 'exists'
941: from bom_ato_configurations bac2
942: , mtl_system_items msi2
943: where bac.config_item_id = bac2.component_item_id
944: and bac2.base_model_id = msi2.inventory_item_id
945: and bac2.organization_id = msi2.organization_id
1177: , 'UPG' --STATUS
1178: , nvl(msi.config_orgs, '1')
1179: from bom_cto_order_lines bcol1
1180: , bom_cto_order_lines bcol2
1181: , bom_ato_configurations bac
1182: , oe_order_lines_all oel
1183: , mtl_system_items msi
1184: -- base model has item attr = 3
1185: where bac.base_model_id = msi.inventory_item_id
1210: -- the lines are closed, I cannot change the OSS setup on the model to make
1211: -- the old warehouse invalid.
1212: -- Changed the logic. Now we do not pick up any configs on closed lines. If a
1213: -- matched CIB = 3 config is not found on any open lines, we look for the config
1214: -- in bom_ato_configurations table.
1215:
1216: -- Another change is the use of decode while populating perform_match flag.
1217: -- This flag is now populated using this decode statement:
1218: -- decode(bcol.perform_match, 'C', 'Y', bcol.perform_match). This is done to make
1295: , 'N' --REUSE_CONFIG
1296: , bcol.QTY_PER_PARENT_MODEL
1297: , 'UPG' --STATUS
1298: , nvl(msi.config_orgs, '1')
1299: from bom_ato_configurations bac
1300: , bom_cto_order_lines bcol
1301: , mtl_system_items msi
1302: -- base model has item attr = 3
1303: where bac.base_model_id = msi.inventory_item_id
1542: , 'UPG' --STATUS
1543: , nvl(msi.CONFIG_ORGS, '1')
1544: from bom_cto_order_lines bcol1
1545: , bom_cto_order_lines bcol2
1546: , bom_ato_configurations bac
1547: , oe_order_lines_all oel
1548: , mtl_system_items msi
1549: -- base model has item attr = 3
1550: where bac.base_model_id = msi.inventory_item_id
1644: , 'N' --REUSE_CONFIG
1645: , bcol.QTY_PER_PARENT_MODEL
1646: , 'UPG' --STATUS
1647: , nvl(msi.CONFIG_ORGS, '1')
1648: from bom_ato_configurations bac
1649: , bom_cto_order_lines bcol
1650: , mtl_system_items msi
1651: -- base model has item attr = 3
1652: where bac.base_model_id = msi.inventory_item_id
1654: and nvl(msi.config_orgs, '1') = '3'
1655: -- and is top parent with attribute 3
1656: and NOT EXISTS
1657: (select 'exists'
1658: from bom_ato_configurations bac2
1659: , mtl_system_items msi2
1660: where bac.config_item_id = bac2.component_item_id
1661: and bac2.base_model_id = msi2.inventory_item_id
1662: and bac2.organization_id = msi2.organization_id
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'
1775: from bom_cto_order_lines_upg bcolu
1776: where bcolu.config_item_id = bac.config_item_id)
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
1799: and bac.organization_id = msi.organization_id
1800: and msi.config_orgs = '3'
1806: and mcat.category_set_id = p_category_set_id
1807: -- and is top parent with attribute 3
1808: and NOT EXISTS
1809: (select /*+ no_unnest push_subq */ 'exists' -- bug 13876670 added hint
1810: from bom_ato_configurations bac2
1811: , mtl_system_items msi2
1812: where bac.config_item_id = bac2.component_item_id
1813: and bac2.base_model_id = msi2.inventory_item_id
1814: and bac2.organization_id = msi2.organization_id
2043: --, bcol2.SEQUENCE
2044: , nvl(msi.config_orgs, '1')
2045: from bom_cto_order_lines bcol1
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
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
2160: where bac.base_model_id = msi.inventory_item_id
2161: and bac.organization_id = msi.organization_id
2421: --, bcol2.SEQUENCE
2422: , nvl(msi.CONFIG_ORGS, '1')
2423: from bom_cto_order_lines bcol1
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
2530: , bcol.QTY_PER_PARENT_MODEL
2531: , 'UPG' --STATUS
2532: --, bcol.SEQUENCE
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
2545: and mcat.category_id = p_cat_id
2546: -- and is top parent with attribute 3
2547: and NOT EXISTS
2548: (select 'exists'
2549: from bom_ato_configurations bac2
2550: , mtl_system_items msi2
2551: where bac.config_item_id = bac2.component_item_id
2552: and bac2.base_model_id = msi2.inventory_item_id
2553: and bac2.organization_id = msi2.organization_id
2889: --, bcol2.SEQUENCE
2890: , nvl(msi.config_orgs, '1')
2891: from bom_cto_order_lines bcol1
2892: , bom_cto_order_lines bcol2
2893: , bom_ato_configurations bac
2894: , oe_order_lines_all oel
2895: , mtl_system_items msi
2896: where bac.config_item_id = p_config_id
2897: -- and exists in bcol
2991: , 'N' --bcol.REUSE_CONFIG
2992: , bcol.QTY_PER_PARENT_MODEL
2993: , 'UPG' --STATUS
2994: , nvl(msi.config_orgs, '1')
2995: from bom_ato_configurations bac
2996: , bom_cto_order_lines bcol
2997: , mtl_system_items msi
2998: where bac.config_item_id = p_config_id
2999: and NOT EXISTS
3118: cto_update_configs_pk.bac_program_id, -- program_id
3119: 'Y', -- perform_match /* Sushant made changes to identify matched items */
3120: 'N', -- reuse_config
3121: bac.organization_id
3122: from bom_ato_configurations bac,
3123: mtl_system_items msi
3124: where bac.config_item_id = p_config_id
3125: -- and bac.component_item_id <> bac.base_model_id -- not pick up top model
3126: and msi.inventory_item_id = substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)) -- bac.component_item_id
3150:
3151: BEGIN
3152: select base_model_id
3153: into l_base_model_id
3154: from bom_ato_configurations
3155: where config_item_id = p_config_id
3156: and rownum = 1;
3157:
3158: EXCEPTION
3562: sysdate, -- last_update_date
3563: nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
3564: cto_update_configs_pk.bac_program_id, -- program_id
3565: bac.organization_id --Bugfix 10240482
3566: from bom_ato_configurations bac,
3567: mtl_system_items msi
3568: where bac.config_item_id = l_curr_config_id
3569: and bac.component_item_id <> bac.base_model_id --not pick up top model
3570: and msi.inventory_item_id = bac.component_item_id