eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CTO_UPDATE_CONFIGS_PK dependencies on MTL_SYSTEM_ITEMS

Line 312: from mtl_system_items msi

308: and oel.item_type_code='CONFIG')
309: -- and item is not pre-configured
310: and not exists
311: (select 'pc'
312: from mtl_system_items msi
313: where msi.inventory_item_id = bac.config_item_id
314: and nvl(msi.auto_created_config_flag,'N') = 'N'));
315:
316: WriteToLog('Sourcing deleted::'||sql%rowcount, 2);

Line 378: from mtl_system_items msi

374: and open_flag = 'Y'
375: )
376: and not exists
377: (select 'pc'
378: from mtl_system_items msi
379: where msi.inventory_item_id = bac.config_item_id
380: and msi.auto_created_config_flag = 'N'
381: )
382: );

Line 440: from mtl_system_items msi

436: and nvl(oel.open_flag, 'N') = 'Y')
437: -- and item is not pre-configured
438: and not exists
439: (select 'pc'
440: from mtl_system_items msi
441: where msi.inventory_item_id = p_config_id
442: and nvl(msi.auto_created_config_flag,'N') = 'N');
443:
444: WriteToLog('Sourcing deleted::'||sql%rowcount, 2);

Line 728: mtl_system_items msi

724: CURSOR c_bac IS
725: -- individual configs not in bcol and having item attribute 3
726: select distinct bac.config_item_id config_id
727: from bom_ato_configurations bac,
728: mtl_system_items msi
729: where NOT EXISTS
730: (select 'exists'
731: from bom_cto_order_lines_upg bcolu
732: where bcolu.config_item_id = bac.config_item_id)

Line 746: mtl_system_items msi

742: CURSOR c_bac_top IS
743: -- individual configs not in bcol and having item attribute 3
744: select distinct bac.config_item_id config_id
745: from bom_ato_configurations bac,
746: mtl_system_items msi
747: -- item attribute is 3
748: where bac.base_model_id = msi.inventory_item_id
749: and bac.organization_id = msi.organization_id
750: and nvl(msi.config_orgs, '1') = '3'

Line 755: , mtl_system_items msi2

751: -- and is top parent with attribute 3
752: and NOT EXISTS
753: (select 'exists'
754: from bom_ato_configurations bac2
755: , mtl_system_items msi2
756: where bac.config_item_id = bac2.component_item_id
757: and bac2.base_model_id = msi2.inventory_item_id
758: and bac2.organization_id = msi2.organization_id
759: and nvl(msi2.config_orgs, '1') = '3')

Line 888: , mtl_system_items mtl

884: , nvl(mtl.config_orgs, '1')
885: --changed the where clause to use a subquery
886: --bugfix 3841575
887: from bom_cto_order_lines bcol2
888: , mtl_system_items mtl
889: -- select entire configuration
890: where mtl.inventory_item_id = bcol2.inventory_item_id
891: and mtl.organization_id = bcol2.ship_from_org_id
892: and bcol2.ato_line_id in

Line 896: , mtl_system_items msi

892: and bcol2.ato_line_id in
893: (select distinct bcol1.ato_line_id
894: from bom_cto_order_lines bcol1
895: , oe_order_lines_all oel
896: , mtl_system_items msi
897: -- for configs whose models have attr=2,3
898: where bcol1.config_item_id is not null
899: and bcol1.inventory_item_id = msi.inventory_item_id
900: and bcol1.ship_from_org_id = msi.organization_id

Line 994: , mtl_system_items msi

990: from bom_cto_order_lines bcol1
991: , bom_cto_order_lines bcol2
992: , bom_ato_configurations bac
993: , oe_order_lines_all oel
994: , mtl_system_items msi
995: -- base model has item attr = 3
996: where bac.base_model_id = msi.inventory_item_id
997: and bac.organization_id = msi.organization_id
998: and nvl(msi.config_orgs, '1') = '3'

Line 1092: , mtl_system_items msi

1088: , 'UPG' --STATUS
1089: , nvl(msi.config_orgs, '1')
1090: from bom_ato_configurations bac
1091: , bom_cto_order_lines bcol
1092: , mtl_system_items msi
1093: -- base model has item attr = 3
1094: where bac.base_model_id = msi.inventory_item_id
1095: and bac.organization_id = msi.organization_id
1096: and nvl(msi.config_orgs, '1') = '3'

Line 1242: , mtl_system_items msi

1238: , 'UPG' --STATUS
1239: , nvl(msi.CONFIG_ORGS, '1')
1240: from bom_cto_order_lines bcol
1241: , oe_order_lines_all oel
1242: , mtl_system_items msi
1243: -- select all configs on open order lines
1244: where bcol.ato_line_id = oel.ato_line_id
1245: and nvl(oel.open_flag, 'N') = 'Y'
1246: and bcol.inventory_item_id = msi.inventory_item_id

Line 1337: , mtl_system_items msi

1333: from bom_cto_order_lines bcol1
1334: , bom_cto_order_lines bcol2
1335: , bom_ato_configurations bac
1336: , oe_order_lines_all oel
1337: , mtl_system_items msi
1338: -- base model has item attr = 3
1339: where bac.base_model_id = msi.inventory_item_id
1340: and bac.organization_id = msi.organization_id
1341: and nvl(msi.config_orgs, '1') = '3'

Line 1437: , mtl_system_items msi

1433: , 'UPG' --STATUS
1434: , nvl(msi.CONFIG_ORGS, '1')
1435: from bom_ato_configurations bac
1436: , bom_cto_order_lines bcol
1437: , mtl_system_items msi
1438: -- base model has item attr = 3
1439: where bac.base_model_id = msi.inventory_item_id
1440: and bac.organization_id = msi.organization_id
1441: and nvl(msi.config_orgs, '1') = '3'

Line 1446: , mtl_system_items msi2

1442: -- and is top parent with attribute 3
1443: and NOT EXISTS
1444: (select 'exists'
1445: from bom_ato_configurations bac2
1446: , mtl_system_items msi2
1447: where bac.config_item_id = bac2.component_item_id
1448: and bac2.base_model_id = msi2.inventory_item_id
1449: and bac2.organization_id = msi2.organization_id
1450: and nvl(msi2.config_orgs, '1') = '3')

Line 1556: mtl_system_items msi,

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'
1560: from bom_cto_order_lines_upg bcolu

Line 1580: mtl_system_items msi,

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
1584: and bac.organization_id = msi.organization_id

Line 1594: , mtl_system_items msi2

1590: -- and is top parent with attribute 3
1591: and NOT EXISTS
1592: (select 'exists'
1593: from bom_ato_configurations bac2
1594: , mtl_system_items msi2
1595: where bac.config_item_id = bac2.component_item_id
1596: and bac2.base_model_id = msi2.inventory_item_id
1597: and bac2.organization_id = msi2.organization_id
1598: and nvl(msi2.config_orgs, '1') = '3')

Line 1723: , mtl_system_items msi

1719: , nvl(msi.config_orgs, '1')
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

Line 1830: , mtl_system_items msi

1826: from bom_cto_order_lines bcol1
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

Line 1935: , mtl_system_items msi

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
1939: where bac.base_model_id = msi.inventory_item_id

Line 2091: , mtl_system_items msi2

2087: --, bcol.SEQUENCE
2088: , nvl(msi2.CONFIG_ORGS, '1')
2089: from bom_cto_order_lines bcol
2090: , oe_order_lines_all oel
2091: , mtl_system_items msi2
2092: -- select all configs on open order lines
2093: where bcol.ato_line_id = oel.ato_line_id
2094: and bcol.inventory_item_id = msi2.inventory_item_id
2095: and bcol.ship_from_org_id = msi2.organization_id

Line 2100: , mtl_system_items msi

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
2104: and bcol2.ship_from_org_id = msi.organization_id

Line 2201: , mtl_system_items msi

2197: from bom_cto_order_lines bcol1
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

Line 2307: , mtl_system_items msi

2303: --, bcol.SEQUENCE
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

Line 2321: , mtl_system_items msi2

2317: -- and is top parent with attribute 3
2318: and NOT EXISTS
2319: (select 'exists'
2320: from bom_ato_configurations bac2
2321: , mtl_system_items msi2
2322: where bac.config_item_id = bac2.component_item_id
2323: and bac2.base_model_id = msi2.inventory_item_id
2324: and bac2.organization_id = msi2.organization_id
2325: and nvl(msi2.config_orgs, '1') = '3')

Line 2455: from mtl_system_items msi

2451: -- get item attribute for this config
2452: l_stmt_num := 40;
2453: select nvl(msi.config_orgs, '1')
2454: into l_attribute
2455: from mtl_system_items msi
2456: where msi.inventory_item_id =
2457: (select msi2.base_item_id
2458: from mtl_system_items msi2
2459: where msi2.inventory_item_id = p_config_id

Line 2458: from mtl_system_items msi2

2454: into l_attribute
2455: from mtl_system_items msi
2456: where msi.inventory_item_id =
2457: (select msi2.base_item_id
2458: from mtl_system_items msi2
2459: where msi2.inventory_item_id = p_config_id
2460: and rownum = 1)
2461: and rownum = 1;
2462:

Line 2555: , mtl_system_items msi

2551: , nvl(msi.config_orgs, '1')
2552: from bom_cto_order_lines bcol1
2553: , bom_cto_order_lines bcol2
2554: , oe_order_lines_all oel
2555: , mtl_system_items msi
2556: , oe_order_lines_all oel2 --bugfix 3371155
2557: -- select entire configuration
2558: where bcol2.ato_line_id = bcol1.ato_line_id
2559: -- to get item attribute

Line 2664: , mtl_system_items msi

2660: from bom_cto_order_lines bcol1
2661: , bom_cto_order_lines bcol2
2662: , bom_ato_configurations bac
2663: , oe_order_lines_all oel
2664: , mtl_system_items msi
2665: where bac.config_item_id = p_config_id
2666: -- and exists in bcol
2667: and bac.config_item_id = bcol1.config_item_id
2668: -- on open order lines

Line 2765: , mtl_system_items msi

2761: , 'UPG' --STATUS
2762: , nvl(msi.config_orgs, '1')
2763: from bom_ato_configurations bac
2764: , bom_cto_order_lines bcol
2765: , mtl_system_items msi
2766: where bac.config_item_id = p_config_id
2767: and NOT EXISTS
2768: (select 'exists'
2769: from bom_cto_order_lines_upg bcolu

Line 2891: mtl_system_items msi

2887: 'Y', -- perform_match /* Sushant made changes to identify matched items */
2888: 'N', -- reuse_config
2889: bac.organization_id
2890: from bom_ato_configurations bac,
2891: mtl_system_items msi
2892: where bac.config_item_id = p_config_id
2893: -- and bac.component_item_id <> bac.base_model_id -- not pick up top model
2894: and msi.inventory_item_id = substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)) -- bac.component_item_id
2895: and msi.organization_id = bac.organization_id;

Line 3199: mtl_system_items msi

3195: sysdate, -- last_update_date
3196: nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
3197: cto_update_configs_pk.bac_program_id -- program_id
3198: from bom_ato_configurations bac,
3199: mtl_system_items msi
3200: where bac.config_item_id = l_curr_config_id
3201: and bac.component_item_id <> bac.base_model_id --not pick up top model
3202: and msi.inventory_item_id = bac.component_item_id
3203: and msi.organization_id = bac.organization_id;

Line 3237: from mtl_system_items msi

3233: WriteToLog('t_bcol(l_parent_index).header_id=>'||t_bcol(l_parent_index).header_id);
3234:
3235: select nvl(msi.config_orgs, '1'), inventory_item_id
3236: into l_item_attr, l_child_model_id
3237: from mtl_system_items msi
3238: where msi.inventory_item_id = (select base_item_id --bugfix3845686
3239: from mtl_system_items
3240: where inventory_item_id = t_bcol(l_parent_index).header_id
3241: and rownum =1)

Line 3239: from mtl_system_items

3235: select nvl(msi.config_orgs, '1'), inventory_item_id
3236: into l_item_attr, l_child_model_id
3237: from mtl_system_items msi
3238: where msi.inventory_item_id = (select base_item_id --bugfix3845686
3239: from mtl_system_items
3240: where inventory_item_id = t_bcol(l_parent_index).header_id
3241: and rownum =1)
3242: --and msi.organization_id = t_bcol(l_parent_index).ship_from_org_id;
3243: and rownum = 1;

Line 3254: from mtl_system_items_kfv msi

3250:
3251: l_stmt_num := 30;
3252: select substrb(concatenated_segments,1,50) name
3253: into l_child_model_name
3254: from mtl_system_items_kfv msi
3255: where msi.inventory_item_id = l_child_model_id
3256: and rownum=1;/* Fixed bug 3529482 */
3257:
3258:

Line 3624: mtl_system_items_kfv msi

3620: CURSOR c_success IS
3621: select distinct substrb(concatenated_segments,1,50) name,msi.inventory_item_id
3622: item_id
3623: from bom_cto_order_lines_upg bcolu,
3624: mtl_system_items_kfv msi
3625: where bcolu.config_item_id is not null
3626: and bcolu.config_item_id = msi.inventory_item_id
3627: and bcolu.ship_from_org_id = msi.organization_id
3628: and ((bcolu.config_creation = '3'

Line 3649: mtl_system_items_kfv msi

3645: CURSOR c_error IS
3646: select distinct substrb(concatenated_segments,1,50) name,
3647: msi.inventory_item_id item_id
3648: from bom_cto_order_lines_upg bcolu,
3649: mtl_system_items_kfv msi
3650: where bcolu.config_item_id is not null
3651: and bcolu.config_item_id = msi.inventory_item_id
3652: and bcolu.ship_from_org_id = msi.organization_id
3653: and not exists (select 'exists'

Line 3670: mtl_system_items_kfv msi,

3666: msi.inventory_item_id item_id,
3667: oeh.order_number,
3668: decode(bcolu.status, 'MRP_SRC', 'was successfully processed', 'errored out') status
3669: from bom_cto_order_lines_upg bcolu,
3670: mtl_system_items_kfv msi,
3671: oe_order_lines_all oel,
3672: oe_order_headers_all oeh
3673: where bcolu.config_item_id is not null
3674: and bcolu.config_item_id = msi.inventory_item_id

Line 3916: update mtl_system_items_b msic

3912: If p_item = 1 Then
3913:
3914: -- Update based on all models
3915:
3916: update mtl_system_items_b msic
3917: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3918: from mtl_system_items_b msim
3919: where msim.inventory_item_id = msic.base_item_id
3920: and msim.organization_id = msic.organization_id)

Line 3918: from mtl_system_items_b msim

3914: -- Update based on all models
3915:
3916: update mtl_system_items_b msic
3917: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3918: from mtl_system_items_b msim
3919: where msim.inventory_item_id = msic.base_item_id
3920: and msim.organization_id = msic.organization_id)
3921:
3922: where msic.base_item_id is not null

Line 3924: from mtl_system_items_b msim1

3920: and msim.organization_id = msic.organization_id)
3921:
3922: where msic.base_item_id is not null
3923: and 'x'= (select 'x'
3924: from mtl_system_items_b msim1
3925: where msim1.inventory_item_id = msic.base_item_id
3926: and msim1.organization_id = msic.organization_id);
3927:
3928:

Line 3934: update mtl_system_items_b msic

3930: WriteToLog(' Number of records updated = '||sql%rowcount);
3931:
3932: elsif p_item = 2 then
3933: -- update based on the category id
3934: update mtl_system_items_b msic
3935: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3936: from mtl_system_items_b msim
3937: where msim.inventory_item_id = msic.base_item_id
3938: and msim.organization_id = msic.organization_id)

Line 3936: from mtl_system_items_b msim

3932: elsif p_item = 2 then
3933: -- update based on the category id
3934: update mtl_system_items_b msic
3935: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3936: from mtl_system_items_b msim
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,

Line 3940: from mtl_system_items_b msi,

3936: from mtl_system_items_b msim
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

Line 3944: and exists (select 'x' from mtl_system_items_b msim

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
3946: and msim.organization_id = msic.organization_id);
3947:
3948: WriteToLog(' Number of records updated = '||sql%rowcount);

Line 3952: update mtl_system_items_b msic

3948: WriteToLog(' Number of records updated = '||sql%rowcount);
3949:
3950: elsif p_item = 3 then
3951: -- update based on config item
3952: update mtl_system_items_b msic
3953: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
3954: from mtl_system_items_b msim
3955: where msim.inventory_item_id = msic.base_item_id
3956: and msim.organization_id = msic.organization_id)

Line 3954: from mtl_system_items_b msim

3950: elsif p_item = 3 then
3951: -- update based on config item
3952: update mtl_system_items_b msic
3953: set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
3954: from mtl_system_items_b msim
3955: where msim.inventory_item_id = msic.base_item_id
3956: and msim.organization_id = msic.organization_id)
3957: where msic.inventory_item_id = p_config_id
3958: and exists (select 'x' from mtl_system_items_b msim

Line 3958: and exists (select 'x' from mtl_system_items_b msim

3954: from mtl_system_items_b msim
3955: where msim.inventory_item_id = msic.base_item_id
3956: and msim.organization_id = msic.organization_id)
3957: where msic.inventory_item_id = p_config_id
3958: and exists (select 'x' from mtl_system_items_b msim
3959: where msim.inventory_item_id = msic.base_item_id
3960: and msim.organization_id = msic.organization_id);
3961: WriteToLog(' Number of records updated = '||sql%rowcount);
3962: end if;