[Home] [Help]
114:
115: CURSOR c_configs(l_ato_line_id number) IS
116: select substrb(concatenated_segments,1,50) config_name
117: from bom_cto_order_lines_upg bcolu,
118: mtl_system_items_kfv msi
119: where bcolu.ato_line_id = l_ato_line_id
120: and bcolu.config_item_id is not null
121: and bcolu.config_item_id = msi.inventory_item_id
122: and bcolu.ship_from_org_id = msi.organization_id;
407:
408: If p_upgrade_mode = 2 Then
409: WriteToLog('Updating ATP attributes for configs in existing orgs');
410:
411: update mtl_system_items_b msic
412: set (msic.atp_components_flag,msic.atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,
413: msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
414: from mtl_system_items_b msim
415: where msim.inventory_item_id = msic.base_item_id
410:
411: update mtl_system_items_b msic
412: set (msic.atp_components_flag,msic.atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,
413: msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
414: from mtl_system_items_b msim
415: where msim.inventory_item_id = msic.base_item_id
416: and msim.organization_id = msic.organization_id)
417: where msic.inventory_item_id in
418: (
421: where status = 'BCSO'
422: and config_item_id is not null
423: )
424: and exists (select 'x'
425: from mtl_system_items_b msim1
426: where msim1.inventory_item_id = msic.base_item_id
427: and msim1.organization_id = msic.organization_id);
428:
429: WriteToLog('Number of Configs updated for ATP attributes ='||sql%rowcount);
951: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
952: bcolu.config_item_id cfg_item_id,
953: bcolu.inventory_item_id model_item_id,
954: msi.organization_id src_org_id
955: from mtl_system_items msi,
956: bom_cto_order_lines_upg bcolu
957: where bcolu.config_item_id is not null
958: and bcolu.status = 'BCSO'
959: and msi.inventory_item_id = bcolu.config_item_id
961: (SELECT 'x'
962: FROM FND_ATTACHED_DOCUMENTS
963: WHERE pk1_value = to_char(msi.organization_id)
964: AND pk2_value = to_char(msi.inventory_item_id)
965: AND entity_name = 'MTL_SYSTEM_ITEMS');
966:
967:
968:
969: Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
1009: WriteToLog('Entering Update_Item_Data', 3);
1010: lStmtNumber := 10;
1011: xReturnStatus := FND_API.G_RET_STS_SUCCESS;
1012:
1013: insert into mtl_system_items_tl (
1014: inventory_item_id,
1015: organization_id,
1016: language,
1017: source_lang,
1034: sysdate,
1035: gUserId, --created_by
1036: gLoginId --last_update_login
1037: from
1038: mtl_system_items_tl m,
1039: bom_cto_src_orgs bcso,
1040: bom_cto_order_lines_upg bcolu,
1041: fnd_languages l
1042: where bcolu.config_item_id is not null
1048: and l.installed_flag In ('I', 'B')
1049: and l.language_code = m.language
1050: and NOT EXISTS
1051: (select NULL
1052: from mtl_system_items_tl t
1053: where t.inventory_item_id = bcolu.config_item_id
1054: and t.organization_id = bcso.organization_id
1055: and t.language = l.language_code );
1056:
1053: where t.inventory_item_id = bcolu.config_item_id
1054: and t.organization_id = bcso.organization_id
1055: and t.language = l.language_code );
1056:
1057: WriteToLog('Inserted rows into mtl_system_items_tl:: '||sql%rowcount,2);
1058:
1059: lStmtNumber := 20;
1060: insert into MTL_PENDING_ITEM_STATUS (
1061: inventory_item_id,
1087: null,
1088: null,
1089: sysdate,
1090: null -- req_id
1091: from mtl_system_items m,
1092: bom_cto_src_orgs bcso,
1093: bom_cto_order_lines_upg bcolu
1094: where bcolu.config_item_id is not null
1095: and bcolu.status = 'BCSO'
1153: MTL_ITEM_REVISIONS_B_S.nextval, -- 3338108 --l_rev_id, --revision_id is generated from sequence
1154: mp1.starting_revision --3340844
1155: from
1156: mtl_parameters mp1,
1157: mtl_system_items m
1158: where m.inventory_item_id = v_bcolu_cfg.config_item_id
1159: and m.organization_id = mp1.organization_id
1160: and NOT EXISTS
1161: (select NULL
1192: sysdate,
1193: gUserId, --created_by
1194: gLoginId --last_update_login
1195: from
1196: mtl_system_items_tl m,
1197: bom_cto_src_orgs bcso,
1198: fnd_languages l,
1199: mtl_item_revisions_b mr --3338108
1200: where m.inventory_item_id = v_bcolu_cfg.inventory_item_id
1232:
1233: --performance bugfix4905887 shared memroy 1MB (sqlid :16104932 )
1234: --Removed comments to max possible
1235: --bsco is a mergable view now
1236: --removed un-necessary join on mtl_system_items
1237: --Removed a Distinct clause in IN sub-query
1238:
1239: select bcolu.config_item_id,
1240: C.organization_id, -- bug 4172300
1445: C.attribute15
1446: from
1447: mtl_parameters MP1,
1448: cst_item_costs C,
1449: mtl_system_items S,
1450: bom_cto_src_orgs bcso,
1451: bom_cto_order_lines_upg bcolu
1452: where bcolu.config_item_id is not null
1453: and bcolu.status = 'BCSO'
1668: C.attribute15
1669: from
1670: mtl_parameters MP1,
1671: cst_item_costs C,
1672: mtl_system_items S,
1673: bom_cto_src_orgs bcso,
1674: bom_cto_order_lines_upg bcolu
1675: where bcolu.config_item_id is not null
1676: and bcolu.status = 'BCSO'
1831: C.attribute15
1832: from
1833: mtl_parameters MP1,
1834: cst_item_cost_details C,
1835: mtl_system_items S,
1836: bom_cto_src_orgs bcso,
1837: bom_cto_order_lines_upg bcolu
1838: where bcolu.config_item_id is not null
1839: and bcolu.status = 'BCSO'
1953: C.attribute15
1954: from
1955: mtl_parameters MP1,
1956: cst_item_cost_details C,
1957: mtl_system_items S,
1958: bom_cto_src_orgs bcso,
1959: bom_cto_order_lines_upg bcolu
1960: where bcolu.config_item_id is not null
1961: and bcolu.status = 'BCSO'
2055: NULL, -- program_application_id
2056: NULL, -- program_id
2057: SYSDATE, -- program_update_date
2058: NULL -- request_id
2059: from mtl_system_items s,
2060: mtl_descr_element_values D,
2061: mtl_descriptive_elements E,
2062: bom_cto_order_lines_upg bcolu
2063: where bcolu.config_item_id is not null
2682: l.language_code,
2683: userenv('lang')
2684: FROM fnd_languages l,
2685: mtl_cross_references_b mtl_cross,
2686: mtl_system_items_tl mtl
2687: WHERE mtl_cross.inventory_item_id = t_cfg_item_id(i)
2688: AND mtl_cross.inventory_item_id = mtl.inventory_item_id
2689: AND mtl_cross.organization_id = mtl.organization_id
2690: AND l.language_code = mtl.language
2821: lStmtNumber := 200;
2822: FOR v_get_org_id in c_get_org_id LOOP
2823:
2824: fnd_attached_documents2_pkg.copy_attachments (
2825: X_from_entity_name => 'MTL_SYSTEM_ITEMS',
2826: X_from_pk1_value => v_get_org_id.src_org_id,
2827: X_from_pk2_value => v_get_org_id.model_item_id,
2828: X_from_pk3_value => NULL,
2829: X_from_pk4_value => NULL,
2827: X_from_pk2_value => v_get_org_id.model_item_id,
2828: X_from_pk3_value => NULL,
2829: X_from_pk4_value => NULL,
2830: X_from_pk5_value => NULL,
2831: X_to_entity_name => 'MTL_SYSTEM_ITEMS',
2832: X_to_pk1_value => v_get_org_id.src_org_id,
2833: X_to_pk2_value => v_get_org_id.cfg_item_id,
2834: X_to_pk3_value => NULL,
2835: X_to_pk4_value => NULL,
2938:
2939: --perf bugfix 4905887 (sql id 16105473)
2940: --Removed comments to extent possible
2941:
2942: insert into mtl_system_items_b
2943: (inventory_item_id,
2944: organization_id,
2945: last_update_date,
2946: last_updated_by,
3591: 2
3592:
3593: from
3594: mtl_parameters mp1,
3595: mtl_system_items_b m, -- model
3596: mtl_system_items_b c, -- config
3597: bom_cto_src_orgs bcso,
3598: bom_cto_order_lines_upg bcolu
3599: where bcolu.config_item_id is not null
3592:
3593: from
3594: mtl_parameters mp1,
3595: mtl_system_items_b m, -- model
3596: mtl_system_items_b c, -- config
3597: bom_cto_src_orgs bcso,
3598: bom_cto_order_lines_upg bcolu
3599: where bcolu.config_item_id is not null
3600: and bcolu.status = 'BCSO'
3600: and bcolu.status = 'BCSO'
3601: and m.inventory_item_id = bcolu.inventory_item_id
3602: -- get config item row for any one org
3603: and c.inventory_item_id = bcolu.config_item_id
3604: and c.organization_id = ( select organization_id from mtl_system_items
3605: where inventory_item_id = c.inventory_item_id and rownum = 1) /*BUGFIX 3576040 */
3606: -- config is not pc in any orgs
3607: and not exists
3608: (select 'pc'
3605: where inventory_item_id = c.inventory_item_id and rownum = 1) /*BUGFIX 3576040 */
3606: -- config is not pc in any orgs
3607: and not exists
3608: (select 'pc'
3609: from mtl_system_items msi1
3610: where msi1.inventory_item_id = bcolu.config_item_id
3611: and nvl(msi1.auto_created_config_flag,'N') = 'N')
3612: and bcso.model_item_id = bcolu.inventory_item_id
3613: and bcso.line_id = bcolu.line_id
3614: and m.organization_id = mp1.organization_id
3615: and mp1.organization_id = bcso.organization_id
3616: and NOT EXISTS
3617: (select NULL
3618: from mtl_system_items_b
3619: where inventory_item_id = bcolu.config_item_id
3620: and organization_id = mp1.organization_id);
3621:
3622: WriteToLog('Items created::'||sql%rowcount, 2);
3682: --perf bugfix 4905887 (sql id 16105473)
3683: --Removed comments to extent possible
3684:
3685: lStmtNumber := 20;
3686: insert into mtl_system_items_b
3687: (inventory_item_id,
3688: organization_id,
3689: last_update_date,
3690: last_updated_by,
4335: 3,
4336: 2
4337:
4338: from
4339: mtl_system_items_b m, -- model
4340: mtl_system_items_b config, -- config
4341: bom_cto_src_orgs bcso,
4342: bom_cto_order_lines_upg bcolu
4343: where bcolu.config_item_id is not null
4336: 2
4337:
4338: from
4339: mtl_system_items_b m, -- model
4340: mtl_system_items_b config, -- config
4341: bom_cto_src_orgs bcso,
4342: bom_cto_order_lines_upg bcolu
4343: where bcolu.config_item_id is not null
4344: and bcolu.status = 'BCSO'
4348: and config.organization_id = bcolu.ship_from_org_id
4349: -- config is pc in atleast one orgs
4350: and exists
4351: (select 'pc'
4352: from mtl_system_items msi1
4353: where msi1.inventory_item_id = bcolu.config_item_id
4354: and nvl(msi1.auto_created_config_flag,'N') = 'N')
4355: and bcso.model_item_id = bcolu.inventory_item_id
4356: and bcso.line_id = bcolu.line_id
4356: and bcso.line_id = bcolu.line_id
4357: and m.organization_id = bcso.organization_id
4358: and NOT EXISTS
4359: (select NULL
4360: from mtl_system_items_b
4361: where inventory_item_id = bcolu.config_item_id
4362: and organization_id = bcso.organization_id);
4363:
4364: WriteToLog('PC Items created::'||sql%rowcount, 2);