[Home] [Help]
126: | Copying Base Model attachement to Config item.
127: |
128: |
129: | 05-14-2002 Renga Kannan Removed the attribute list_price_per_unit
130: | from copying it to config item in mtl_system_items
131: | Look at the bug details 2370307
132: |
133: |
134: | 06-03-2002 Sushant Sawant Bugfix 2401654 [duplicate of 2400948
424:
425: end get_atp_flag;
426:
427:
428: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
429: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
430: return char
431: is
432: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
425: end get_atp_flag;
426:
427:
428: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
429: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
430: return char
431: is
432: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
433: begin
428: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
429: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
430: return char
431: is
432: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
433: begin
434:
435: /* Note: Although a lot of similarity exists for some combination of flag values
436: the results are bound to change based on new functionality/bug fix issues.
993: from oe_order_lines_all oel,
994: oe_order_headers_all oeh,
995: oe_transaction_types_tl oet,
996: mtl_sales_orders mso,
997: mtl_system_items msi
998: where oel.line_id = lConfigLineId
999: and item_type_code = 'CONFIG'
1000: and oeh.header_id = oel.header_id
1001: and oet.transaction_type_id = oeh.order_type_id
1779: lGrpId number;
1780: rowcount number := 1;
1781: proc_error exception ;
1782:
1783: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1784: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1785:
1786: /*Bugfix 2047428 */
1787: latpcompflag mtl_system_items.atp_components_flag%type;
1780: rowcount number := 1;
1781: proc_error exception ;
1782:
1783: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1784: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1785:
1786: /*Bugfix 2047428 */
1787: latpcompflag mtl_system_items.atp_components_flag%type;
1788: /* End Bugfix 2047428 */
1783: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1784: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1785:
1786: /*Bugfix 2047428 */
1787: latpcompflag mtl_system_items.atp_components_flag%type;
1788: /* End Bugfix 2047428 */
1789:
1790: -- Bugfix 2425667
1791: lrowcount number;
1799: bet.sort_order,
1800: bet.plan_level,
1801: bet.line_id,
1802: substrb(msi.concatenated_segments,1,50)
1803: from bom_explosion_temp bet,mtl_system_items_kfv msi
1804: where bet.group_id = xGrpId
1805: and bet.organization_id = msi.organization_id
1806: and bet.component_item_id = msi.inventory_item_id;
1807:
1847: /* Bugfix 2047428 */
1848: lStmtNumber := 599;
1849: select NVL(msi.atp_components_flag,'N')
1850: into latpcompflag
1851: from mtl_system_items msi , oe_order_lines_all oel
1852: where oel.inventory_item_id = msi.inventory_item_id
1853: and oel.ship_from_org_id = msi.organization_id
1854: and oel.line_id = pLineId
1855: and oel.ordered_quantity > 0;
1909: oel.Line_Id,
1910: xGrpId
1911: from
1912: oe_order_lines_all oel,
1913: mtl_system_items msi
1914: where oel.line_id = pLineId
1915: and oel.inventory_item_id = msi.inventory_item_id
1916: and oel.ship_from_org_id = msi.organization_id
1917: and oel.ordered_quantity > 0;
1978: xGrpId
1979: from
1980: oe_order_lines_all oel,
1981: bom_explosion_temp bet,
1982: mtl_system_items msi
1983: where oel.ato_line_id = pLineId
1984: and oel.line_id <> pLineId
1985: and oel.inventory_item_id = msi.inventory_item_id
1986: and oel.ship_from_org_id = msi.organization_id
2017: xGrpId
2018: from
2019: oe_order_lines_all oel,
2020: bom_explosion_temp bet,
2021: mtl_system_items msi,
2022: bom_bill_of_materials bbm,
2023: bom_inventory_components bic
2024: where oel.ato_line_id = pLineId
2025: and oel.line_id <> pLineId
2087: begin
2088:
2089: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
2090: into v_msi_fixed_lead_time, v_msi_variable_lead_time
2091: from mtl_system_items msi , bom_explosion_temp be
2092: where be.organization_id = msi.organization_id
2093: and be.component_item_id = msi.inventory_item_id
2094: and be.line_id = pLineId
2095: and be.group_id = xGrpId; -- bugfix 1876997
2175: nvl(bic.wip_supply_type, msi2.wip_supply_type), -- 3254039 , 3298244
2176: xGrpId
2177: from
2178: bom_calendar_dates cal,
2179: mtl_system_items msi, /* PARENT */
2180: mtl_system_items msi2, /* CHILD */
2181: bom_inventory_components bic,
2182: eng_revised_items eri,
2183: bom_bill_of_materials bom,
2176: xGrpId
2177: from
2178: bom_calendar_dates cal,
2179: mtl_system_items msi, /* PARENT */
2180: mtl_system_items msi2, /* CHILD */
2181: bom_inventory_components bic,
2182: eng_revised_items eri,
2183: bom_bill_of_materials bom,
2184: mtl_parameters mp,
2322: lGrpId number;
2323: rowcount number := 1;
2324: proc_error exception ;
2325:
2326: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2327: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2328: latpcompflag mtl_system_items.atp_components_flag%type;
2329: lrowcount number;
2330: begin
2323: rowcount number := 1;
2324: proc_error exception ;
2325:
2326: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2327: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2328: latpcompflag mtl_system_items.atp_components_flag%type;
2329: lrowcount number;
2330: begin
2331:
2324: proc_error exception ;
2325:
2326: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2327: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2328: latpcompflag mtl_system_items.atp_components_flag%type;
2329: lrowcount number;
2330: begin
2331:
2332: /*---------------------------------------------------+
2356:
2357: lStmtNumber := 599;
2358: select NVL(msi.atp_components_flag,'N')
2359: into latpcompflag
2360: from mtl_system_items msi , oe_order_lines_all oel
2361: where oel.inventory_item_id = msi.inventory_item_id
2362: and oel.ship_from_org_id = msi.organization_id
2363: and oel.line_id = pLineId
2364: and oel.ordered_quantity > 0;
2407: oel.Line_Id,
2408: xGrpId
2409: from
2410: oe_order_lines_all oel,
2411: mtl_system_items msi
2412: where oel.line_id = pLineId
2413: and oel.inventory_item_id = msi.inventory_item_id
2414: and oel.ship_from_org_id = msi.organization_id
2415: and oel.ordered_quantity > 0;
2467: xGrpId
2468: from
2469: oe_order_lines_all oel,
2470: bom_explosion_temp bet,
2471: mtl_system_items msi
2472: where oel.ato_line_id = pLineId
2473: and oel.line_id <> pLineId
2474: and oel.inventory_item_id = msi.inventory_item_id
2475: and oel.ship_from_org_id = msi.organization_id
2499: begin
2500:
2501: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
2502: into v_msi_fixed_lead_time, v_msi_variable_lead_time
2503: from mtl_system_items msi , bom_explosion_temp be
2504: where be.organization_id = msi.organization_id
2505: and be.component_item_id = msi.inventory_item_id
2506: and be.line_id = pLineId
2507: and be.group_id = xGrpId;
2582: nvl(bic.wip_supply_type, msi2.wip_supply_type),
2583: xGrpId
2584: from
2585: bom_calendar_dates cal,
2586: mtl_system_items msi,
2587: mtl_system_items msi2,
2588: bom_inventory_components bic,
2589: eng_revised_items eri,
2590: bom_bill_of_materials bom,
2583: xGrpId
2584: from
2585: bom_calendar_dates cal,
2586: mtl_system_items msi,
2587: mtl_system_items msi2,
2588: bom_inventory_components bic,
2589: eng_revised_items eri,
2590: bom_bill_of_materials bom,
2591: mtl_parameters mp,
2703:
2704: -- 3779636
2705:
2706:
2707: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2708: , p_atp_comp mtl_system_items.atp_components_flag%type
2709: , p_item_type mtl_system_items.bom_item_type%type )
2710: return number
2711: is
2704: -- 3779636
2705:
2706:
2707: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2708: , p_atp_comp mtl_system_items.atp_components_flag%type
2709: , p_item_type mtl_system_items.bom_item_type%type )
2710: return number
2711: is
2712: begin
2705:
2706:
2707: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2708: , p_atp_comp mtl_system_items.atp_components_flag%type
2709: , p_item_type mtl_system_items.bom_item_type%type )
2710: return number
2711: is
2712: begin
2713: if( p_atp_comp in ( 'C' , 'Y' ) AND p_atp_flag in ( 'R' , 'C')) then
2743: rowcount number := 1;
2744: proc_error exception ;
2745: i number;
2746:
2747: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2748: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2749:
2750: /*Bugfix 2047428 */
2751: latpcompflag mtl_system_items.atp_components_flag%type;
2744: proc_error exception ;
2745: i number;
2746:
2747: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2748: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2749:
2750: /*Bugfix 2047428 */
2751: latpcompflag mtl_system_items.atp_components_flag%type;
2752: /* End Bugfix 2047428 */
2747: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2748: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2749:
2750: /*Bugfix 2047428 */
2751: latpcompflag mtl_system_items.atp_components_flag%type;
2752: /* End Bugfix 2047428 */
2753:
2754: -- 3893281 : New debug cursor and variables
2755: cursor debug_bet is
2759: bet.sort_order,
2760: bet.plan_level,
2761: bet.line_id,
2762: substrb(msi.concatenated_segments,1,50)
2763: from bom_explosion_temp bet,mtl_system_items_kfv msi
2764: where bet.group_id = xGrpId
2765: and bet.organization_id = msi.organization_id
2766: and bet.component_item_id = msi.inventory_item_id;
2767:
2799: /* Bugfix 2047428 */
2800: lStmtNumber := 599;
2801: select NVL(msi.atp_components_flag,'N')
2802: into latpcompflag
2803: from mtl_system_items msi
2804: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
2805: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
2806:
2807:
2840: primary_uom_code, -- Bugfix 1998386
2841: sort_order,
2842: group_id)
2843: -- Bugfix 1998386 Change this to select statement to select
2844: -- primary_uom_code from mtl_system_items.
2845: -- Also CONVERT_UOM function is used to convert
2846: -- p_ship_set.quantity_ordered(i) if the ordered UOM
2847: -- is different from primary UOM.
2848: /*values (
2867: p_ship_set.inventory_item_id(i)),
2868: msi.primary_uom_code,
2869: 2, -- BUG no 1288823 modification
2870: lGroupid2
2871: from mtl_system_items msi
2872: where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
2873: and msi.organization_id = p_ship_set.source_organization_id(i);
2874:
2875: i := p_ship_set.inventory_item_id.NEXT(i);
2892: line_id,
2893: group_id)
2894:
2895: -- Bugfix 1998386 Change this to select statement to
2896: -- select primary_uom_code from mtl_system_items.
2897: -- Also CONVERT_UOM function is used to convert
2898: -- p_ship_set.quantity_ordered(i) if the ordered UOM is
2899: -- different from primary UOM.
2900:
2913: msi.primary_uom_code,
2914: p_ship_set.inventory_item_id(p_model_index),
2915: 1,
2916: xGrpId
2917: from mtl_system_items msi
2918: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
2919: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
2920:
2921:
2969: xGrpId
2970: from
2971: bom_bill_of_materials bom,
2972: bom_inventory_components bic,
2973: mtl_system_items msi, -- bugfix 1998386
2974: bom_explosion_temp bet,
2975: bom_explosion_temp bet2 /* ship set */
2976: where bet.group_id = xGrpId
2977: and bet.plan_level = level_number -1
3039:
3040: begin
3041: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
3042: into v_msi_fixed_lead_time, v_msi_variable_lead_time
3043: from mtl_system_items msi , bom_explosion_temp be
3044: where be.organization_id = msi.organization_id
3045: and be.component_item_id = msi.inventory_item_id
3046: and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
3047:
3115: NULL,
3116: xGrpId
3117: from
3118: bom_calendar_dates cal,
3119: mtl_system_items msi, /* PARENT */
3120: mtl_system_items msi2, /* CHILD [BUG#1518894] */
3121: bom_inventory_components bic,
3122: eng_revised_items eri,
3123: bom_bill_of_materials bom,
3116: xGrpId
3117: from
3118: bom_calendar_dates cal,
3119: mtl_system_items msi, /* PARENT */
3120: mtl_system_items msi2, /* CHILD [BUG#1518894] */
3121: bom_inventory_components bic,
3122: eng_revised_items eri,
3123: bom_bill_of_materials bom,
3124: mtl_parameters mp,
3253: rowcount number := 1;
3254: proc_error exception ;
3255: i number;
3256:
3257: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3258: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3259: latpcompflag mtl_system_items.atp_components_flag%type;
3260:
3261:
3254: proc_error exception ;
3255: i number;
3256:
3257: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3258: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3259: latpcompflag mtl_system_items.atp_components_flag%type;
3260:
3261:
3262: begin
3255: i number;
3256:
3257: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3258: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3259: latpcompflag mtl_system_items.atp_components_flag%type;
3260:
3261:
3262: begin
3263:
3281:
3282: lStmtNumber := 599;
3283: select NVL(msi.atp_components_flag,'N')
3284: into latpcompflag
3285: from mtl_system_items msi
3286: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
3287: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
3288:
3289:
3333: p_ship_set.inventory_item_id(i)),
3334: msi.primary_uom_code,
3335: 2,
3336: lGroupid2
3337: from mtl_system_items msi
3338: where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
3339: and msi.organization_id = p_ship_set.source_organization_id(i);
3340:
3341: i := p_ship_set.inventory_item_id.NEXT(i);
3372: msi.primary_uom_code,
3373: p_ship_set.inventory_item_id(p_model_index),
3374: 1,
3375: xGrpId
3376: from mtl_system_items msi
3377: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
3378: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
3379:
3380:
3420: xGrpId
3421: from
3422: bom_bill_of_materials bom,
3423: bom_inventory_components bic,
3424: mtl_system_items msi,
3425: bom_explosion_temp bet,
3426: bom_explosion_temp bet2
3427: where bet.group_id = xGrpId
3428: and bet.plan_level = level_number -1
3457:
3458: begin
3459: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
3460: into v_msi_fixed_lead_time, v_msi_variable_lead_time
3461: from mtl_system_items msi , bom_explosion_temp be
3462: where be.organization_id = msi.organization_id
3463: and be.component_item_id = msi.inventory_item_id
3464: and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
3465:
3529: NULL,
3530: xGrpId
3531: from
3532: bom_calendar_dates cal,
3533: mtl_system_items msi,
3534: mtl_system_items msi2,
3535: bom_inventory_components bic,
3536: eng_revised_items eri,
3537: bom_bill_of_materials bom,
3530: xGrpId
3531: from
3532: bom_calendar_dates cal,
3533: mtl_system_items msi,
3534: mtl_system_items msi2,
3535: bom_inventory_components bic,
3536: eng_revised_items eri,
3537: bom_bill_of_materials bom,
3538: mtl_parameters mp,
3935: select nvl(fixed_lead_time,0),
3936: nvl(variable_lead_time,0)
3937: into lFixedLt,
3938: lVarLt
3939: from mtl_system_items msi
3940: where msi.inventory_item_id = lItem_id
3941: and msi.organization_id = lOrg_id;
3942:
3943: lStmtNumber := 710;
4049:
4050:
4051:
4052: /*-----------------------------------------------------+
4053: Creates configuration item in mtl_system_items.
4054: This function does not check the validity of
4055: data in oe_order_lines. The validity checks
4056: like 'check that config row does not exists already'
4057: are implemented in activity wrapper / batch program
4057: are implemented in activity wrapper / batch program
4058: +-----------------------------------------------------*/
4059:
4060: --- The following procedure is modified by Renga Kannan on 08/31/01
4061: --- While instering the rows into MTL_SYSTEM_ITEMS one new function
4062: --- is called for list_price field. CTO_CUSTOM_LIST_PRICE_PK.get_list_price
4063: --- is called to get the list price thru custom calculation. if it is null then
4064: --- it will be copied from its model list price. This change is doen as part of
4065: --- Procuring config Phase I -- Patchset G
4100: lReturnStatus varchar2(1);
4101:
4102: lConfigSegName fnd_id_flex_segments.segment_name%type;
4103: app_column fnd_id_flex_segments.segment_name%type;
4104: new_item_num mtl_system_items.segment1%Type ;
4105:
4106: lCreateRules varchar2(1);
4107: lStatus number;
4108:
4106: lCreateRules varchar2(1);
4107: lStatus number;
4108:
4109: type lSegType is table of
4110: mtl_system_items_interface.segment1%type
4111: index by binary_integer;
4112:
4113: seg lSegType ;
4114:
4116:
4117: -- Start Bugfix 2157740
4118: cursor c_get_org_id is
4119: select msi.organization_id src_org_id
4120: from mtl_system_items msi
4121: where msi.inventory_item_id = pConfigId
4122: and not exists
4123: (SELECT 'x'
4124: FROM FND_ATTACHED_DOCUMENTS
4123: (SELECT 'x'
4124: FROM FND_ATTACHED_DOCUMENTS
4125: WHERE pk1_value = to_char(msi.organization_id) -- 2774571
4126: AND pk2_value = to_char(msi.inventory_item_id) -- 2774571
4127: AND entity_name = 'MTL_SYSTEM_ITEMS');
4128:
4129:
4130: v_src_org_id mtl_system_items.organization_id%type;
4131: l_document_id Number;
4126: AND pk2_value = to_char(msi.inventory_item_id) -- 2774571
4127: AND entity_name = 'MTL_SYSTEM_ITEMS');
4128:
4129:
4130: v_src_org_id mtl_system_items.organization_id%type;
4131: l_document_id Number;
4132:
4133:
4134: -- End bugfix 2157740
4336: seg(17),
4337: seg(18),
4338: seg(19),
4339: seg(20)
4340: from mtl_system_items msi
4341: where inventory_item_id = pModelId
4342: and organization_id = lValidationOrg;
4343:
4344: lStmtNumber := 50;
4343:
4344: lStmtNumber := 50;
4345:
4346: if lNumberMethod = 1 then
4347: select mtl_system_items_B_S.nextval
4348: into lNextNum
4349: from dual;
4350:
4351: -- bugfix 1933740 : Replaced seg(1) with seg(app_col_ind)
4354: into new_item_num
4355: from dual;
4356:
4357: elsif lNumberMethod = 2 then
4358: select to_char(mtl_system_items_B_S.nextval)
4359: into new_item_num
4360: from dual;
4361:
4362: -- 2652379 : When numbering method is 3 , i.e Replace with order num, line num;
4460: then
4461: IF PG_DEBUG <> 0 THEN
4462: oe_debug_pub.add( 'Create_Item: '|| 'generating unique name' ,2 );
4463: END IF;
4464: select to_char(mtl_system_items_B_S.nextval)
4465: into lNextNum
4466: from dual;
4467: seg(app_col_ind) := new_item_num || lCiDel || lNextNum;
4468: end if;
4475:
4476: IF PG_DEBUG <> 0 THEN
4477: oe_debug_pub.add('Create_Item: ' || 'generating unique name' ,2 );
4478: END IF;
4479: select to_char(mtl_system_items_B_S.nextval)
4480: into lNextNum
4481: from dual;
4482: seg(app_col_ind) := new_item_num || lCiDel || lNextNum;
4483: exit;
4489:
4490: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4491: END IF;
4492:
4493: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4494:
4495: IF PG_DEBUG <> 0 THEN
4496: oe_debug_pub.add('Create_Item: ' || 'new pConfigId is '||to_char(pConfigId),2);
4497: END IF;
4541: seg(17),
4542: seg(18),
4543: seg(19),
4544: seg(20)
4545: from mtl_system_items msi
4546: where inventory_item_id = pConfigId;
4547:
4548: IF PG_DEBUG <> 0 THEN
4549: oe_debug_pub.add('Create_Item: ' || 'matched pConfigId is '||to_char(pConfigId),2);
4556: -- rkaza. 10/21/2004. bug 3860077. Item type should be null if profile is null.
4557: lItemType := FND_PROFILE.Value('BOM:CONFIG_ITEM_TYPE');
4558:
4559: /*-----------------------------------------------------------+
4560: Insert a row into the mtl_system_items table.
4561: +------------------------------------------------------------*/
4562:
4563: --xTableName := 'MTL_SYSTEM_ITEMS';
4564: lStmtNumber := 80;
4559: /*-----------------------------------------------------------+
4560: Insert a row into the mtl_system_items table.
4561: +------------------------------------------------------------*/
4562:
4563: --xTableName := 'MTL_SYSTEM_ITEMS';
4564: lStmtNumber := 80;
4565:
4566: /* need to add attribute controlled statement for preconfigured item */
4567: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4576:
4577: --appsperf bug# 4905845, sql id 16104136
4578: --decrease comments within sql to max extent to reduce shared memory
4579:
4580: insert into mtl_system_items_b
4581: (inventory_item_id,
4582: organization_id,
4583: last_update_date,
4584: last_updated_by,
5238: 3,
5239: 2
5240:
5241: from
5242: mtl_system_items_b m, -- Model
5243: bom_cto_src_orgs bcso,
5244: bom_cto_order_lines bcol
5245: where m.inventory_item_id = pModelId
5246: and bcso.model_item_id = pModelId
5248: and bcol.line_id = bcso.line_id
5249: and m.organization_id = bcso.organization_id
5250: and NOT EXISTS
5251: (select NULL
5252: from mtl_system_items_b
5253: where inventory_item_id = pConfigId
5254: and organization_id = m.organization_id);
5255:
5256:
5255:
5256:
5257:
5258: IF PG_DEBUG <> 0 THEN
5259: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5260:
5261: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sql%rowcount||' rows in mtl_system_items_b',2);
5262: END IF;
5263: -- added as part of bugfix 1811007
5257:
5258: IF PG_DEBUG <> 0 THEN
5259: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5260:
5261: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sql%rowcount||' rows in mtl_system_items_b',2);
5262: END IF;
5263: -- added as part of bugfix 1811007
5264:
5265:
5292: IF PG_DEBUG <> 0 THEN
5293: oe_debug_pub.add('Create_Item: ' || 'Fetched Org Id '|| v_get_org_id.src_org_id,3);
5294: END IF;
5295: fnd_attached_documents2_pkg.copy_attachments (
5296: X_from_entity_name => 'MTL_SYSTEM_ITEMS',
5297: X_from_pk1_value => v_get_org_id.src_org_id,
5298: X_from_pk2_value => pModelId,
5299: X_from_pk3_value => NULL,
5300: X_from_pk4_value => NULL,
5298: X_from_pk2_value => pModelId,
5299: X_from_pk3_value => NULL,
5300: X_from_pk4_value => NULL,
5301: X_from_pk5_value => NULL,
5302: X_to_entity_name => 'MTL_SYSTEM_ITEMS',
5303: X_to_pk1_value => v_get_org_id.src_org_id,
5304: X_to_pk2_value => pConfigId,
5305: X_to_pk3_value => NULL,
5306: X_to_pk4_value => NULL,
5339: -- begin bugfix 1811007
5340:
5341: DECLARE
5342:
5343: o_weight_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5344: o_volume_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5345: o_weight number := 0;
5346: o_volume number := 0;
5347: x_status number := 0;
5340:
5341: DECLARE
5342:
5343: o_weight_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5344: o_volume_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5345: o_weight number := 0;
5346: o_volume number := 0;
5347: x_status number := 0;
5348: lShippingOrg number ;
5354: -- Get the weight and volume UOM of the model from the OE:Validation org.
5355:
5356: select i.weight_uom_code, i.volume_uom_code
5357: into o_weight_uom, o_volume_uom
5358: from mtl_system_items i,
5359: bom_cto_order_lines l
5360: where l.line_id = pLineId -- model line id
5361: and l.inventory_item_id = i.inventory_item_id
5362: and i.organization_id = lValidationOrg;
5380: into o_weight_uom
5381: from mtl_units_of_measure
5382: where uom_class = (select uom1.uom_class
5383: from mtl_units_of_measure uom1,
5384: mtl_system_items i,
5385: bom_cto_order_lines l
5386: where l.parent_ato_line_id = pLineId
5387: -- mbsk: replaced ato_line_id with parent_ato_line_id
5388: and l.item_type_code not in ('INCLUDED', 'CONFIG')
5415: into o_volume_uom
5416: from mtl_units_of_measure
5417: where uom_class = (select uom1.uom_class
5418: from mtl_units_of_measure uom1,
5419: mtl_system_items i,
5420: bom_cto_order_lines l
5421: where l.parent_ato_line_id = pLineId
5422: -- mbsk: replaced ato_line_id with parent_ato_line_id
5423: and l.item_type_code not in ('INCLUDED', 'CONFIG')
5491: -- Added "and a.unit_weight is null" condition so that weight/vol is updated
5492: -- only if it is not already calculated.
5493:
5494: if (o_weight_uom is not null) then
5495: update mtl_system_items a
5496: set (unit_weight, weight_uom_code) =
5497: (select CTO_UTILITY_PK.convert_uom(
5498: o_weight_uom,
5499: nvl( b.weight_uom_code, o_weight_uom) , -- bug# 3358194
5499: nvl( b.weight_uom_code, o_weight_uom) , -- bug# 3358194
5500: o_weight,
5501: b.inventory_item_id)
5502: ,nvl(b.weight_uom_code, o_weight_uom) -- bug# 3358194
5503: from mtl_system_items b
5504: where b.inventory_item_id = a.base_item_id
5505: and b.organization_id = a.organization_id)
5506: where a.inventory_item_id = pConfigId
5507: and a.unit_weight is null; -- bugfix 5623437
5507: and a.unit_weight is null; -- bugfix 5623437
5508: end if;
5509:
5510: if (o_volume_uom is not null) then -- begin bugfix 4143695
5511: update mtl_system_items a
5512: set (unit_volume, volume_uom_code) =
5513: (select CTO_UTILITY_PK.convert_uom(
5514: o_volume_uom,
5515: nvl(b.volume_uom_code, o_volume_uom), -- bug# 3358194
5515: nvl(b.volume_uom_code, o_volume_uom), -- bug# 3358194
5516: o_volume,
5517: b.inventory_item_id)
5518: ,nvl(b.volume_uom_code, o_volume_uom) -- bug# 3358194
5519: from mtl_system_items b
5520: where b.inventory_item_id = a.base_item_id
5521: and b.organization_id = a.organization_id)
5522: where a.inventory_item_id = pConfigId
5523: and a.unit_volume is null; -- bugfix 5623437;
5531:
5532: -- end bugfix 1811007
5533:
5534:
5535: oe_debug_pub.add('Create_Item: ' || 'going to insert:mtl_system_items_tl',2);
5536:
5537: /*----------------------------------------+
5538: R11.5 MLS
5539: +----------------------------------------*/
5539: +----------------------------------------*/
5540:
5541: lStmtNumber := 90;
5542:
5543: insert into mtl_system_items_tl (
5544: inventory_item_id,
5545: organization_id,
5546: language,
5547: source_lang,
5565: gLoginId --last_update_login
5566: /*
5567: commented for reintroduction of bcso
5568: from
5569: mtl_system_items_tl m, -- 2457514
5570: fnd_languages l
5571: where m.inventory_item_id = pModelId
5572: and l.installed_flag In ('I', 'B')
5573: and l.language_code = m.language -- 2457514
5572: and l.installed_flag In ('I', 'B')
5573: and l.language_code = m.language -- 2457514
5574: and NOT EXISTS
5575: (select NULL
5576: from mtl_system_items_tl t
5577: where t.inventory_item_id = pConfigId
5578: and t.organization_id = m.organization_id
5579: and t.language = m.language );
5580: */
5578: and t.organization_id = m.organization_id
5579: and t.language = m.language );
5580: */
5581: from
5582: -- bugfix 2457514 mtl_system_items_b m, -- Model
5583: mtl_system_items_tl m, -- 2457514
5584: bom_cto_src_orgs bcso,
5585: fnd_languages l
5586: where m.inventory_item_id = pModelId
5579: and t.language = m.language );
5580: */
5581: from
5582: -- bugfix 2457514 mtl_system_items_b m, -- Model
5583: mtl_system_items_tl m, -- 2457514
5584: bom_cto_src_orgs bcso,
5585: fnd_languages l
5586: where m.inventory_item_id = pModelId
5587: and bcso.model_item_id = pModelId
5590: and l.installed_flag In ('I', 'B')
5591: and l.language_code = m.language -- 2457514
5592: and NOT EXISTS
5593: (select NULL
5594: from mtl_system_items_tl t
5595: where t.inventory_item_id = pConfigId
5596: and t.organization_id = m.organization_id
5597: and t.language = l.language_code );
5598:
5597: and t.language = l.language_code );
5598:
5599:
5600: IF PG_DEBUG <> 0 THEN
5601: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_tl',2);
5602: END IF;
5603:
5604:
5605:
5814: msi.weight_uom_code uom,
5815: msi.inventory_item_id
5816: FROM bom_cto_order_lines l,
5817: bom_cto_order_lines l_model,
5818: mtl_system_items msi
5819: WHERE (l.parent_ato_line_id = x_a_line_id
5820: -- MLMO:replaced ato_line_id with parent_ato_line_id and added OR condn
5821: or
5822: l.line_id = x_a_line_id)
5849: msi.volume_uom_code uom,
5850: msi.inventory_item_id
5851: FROM bom_cto_order_lines l,
5852: bom_cto_order_lines l_model,
5853: mtl_system_items msi
5854: WHERE (l.parent_ato_line_id = x_a_line_id -- MLMO: replaced ato_line_id with parent_ato_line_id
5855: or
5856: l.line_id = x_a_line_id)
5857: -- Bugfix 2576422
6030: /*
6031: commented due to reintroduction of bcso
6032: from mtl_parameters mp1,
6033: cst_item_costs c,
6034: mtl_system_items msi
6035: where c.organization_id = mp1.organization_id
6036: and c.inventory_item_id = pModelId
6037: and C.COST_TYPE_ID = 2 -- Average Costing
6038: and msi.organization_id = mp1.organization_id
6216: null,
6217: null,
6218: sysdate,
6219: null -- req_id
6220: from mtl_system_items m,
6221: bom_cto_src_orgs bcso
6222: where m.inventory_item_id = pModelId
6223: and bcso.model_item_id = pModelId
6224: and bcso.line_id = pLineId
6281: gLoginId, -- last_update_login
6282: sysdate,
6283: sysdate
6284: from
6285: mtl_system_items m,
6286: bom_cto_src_orgs bcso,
6287: mtl_parameters mp1
6288: where m.inventory_item_id = pModelId
6289: and bcso.model_item_id = pModelId
6373: from
6374: mtl_parameters MP1,
6375: cst_item_costs C,
6376: cst_item_cost_details CICD,
6377: mtl_system_items S -- 4172300
6378: where S.organization_id = C.organization_id
6379: and S.inventory_item_id = C.inventory_item_id
6380: and C.organization_id = MP1.organization_id
6381: and C.inventory_item_id = pModelId
6552: C.attribute15
6553: from
6554: mtl_parameters MP1,
6555: cst_item_costs C,
6556: mtl_system_items S,
6557: bom_cto_src_orgs bcso
6558: where S.organization_id = C.organization_id
6559: and S.inventory_item_id = C.inventory_item_id
6560: and C.inventory_item_id = pModelId
6770: C.attribute15
6771: from
6772: mtl_parameters MP1,
6773: cst_item_costs C,
6774: mtl_system_items S,
6775: bom_cto_src_orgs bcso
6776: where S.organization_id = C.organization_id
6777: and S.inventory_item_id = C.inventory_item_id
6778: and C.inventory_item_id = pModelId
6930: C.attribute15
6931: from
6932: mtl_parameters MP1,
6933: cst_item_cost_details C,
6934: mtl_system_items S,
6935: bom_cto_src_orgs bcso
6936: where S.organization_id = C.organization_id
6937: and S.inventory_item_id = C.inventory_item_id
6938: and bcso.model_item_id = pModelId
7054: C.attribute15
7055: from
7056: mtl_parameters MP1,
7057: cst_item_cost_details C,
7058: mtl_system_items S,
7059: bom_cto_src_orgs bcso
7060: where S.organization_id = C.organization_id
7061: and S.inventory_item_id = C.inventory_item_id
7062: and bcso.model_item_id = pModelId
7323: NULL, -- program_application_id
7324: NULL, -- program_id
7325: SYSDATE, -- program_update_date
7326: NULL -- request_id
7327: from mtl_system_items s,
7328: mtl_descr_element_values D,
7329: mtl_descriptive_elements E
7330: where D.inventory_item_id = S.inventory_item_id
7331: and s.inventory_item_id = pModelid
7849: l.language_code,
7850: userenv('lang')
7851: FROM fnd_languages l,
7852: mtl_cross_references_b mtl_cross,
7853: mtl_system_items_tl mtl
7854: WHERE mtl_cross.inventory_item_id = pConfigId
7855: AND mtl_cross.inventory_item_id = mtl.inventory_item_id
7856: AND mtl_cross.organization_id = mtl.organization_id
7857: AND l.language_code = mtl.language
8189: END IF;
8190:
8191: Select concatenated_segments
8192: into l_model_tab(1).ordered_item
8193: from mtl_system_items_b_kfv
8194: where inventory_item_id = pConfigId
8195: and organization_id = pOrgId;
8196:
8197: lStmtNumber := 395;
8769: IF PG_DEBUG <> 0 THEN
8770: oe_debug_pub.add( 'Check_dup_item: '|| 'Checking for duplicate item name.. ' ,2 );
8771: END IF;
8772:
8773: sql_str := 'select 1 from mtl_system_items msi where 1=1 ';
8774:
8775: if pSegment1 is not null then
8776: sql_str := sql_str||' and msi.segment1 = :pSegment1';
8777: end if;
9014: MTL_ITEM_REVISIONS_B_S.nextval, -- 3338108 --:x_item_rev_seq --revision_id is generated from sequence
9015: mp1.starting_revision --3340844
9016: from
9017: mtl_parameters mp1,
9018: mtl_system_items m
9019: where m.inventory_item_id = pConfigId
9020: and m.organization_id = mp1.organization_id
9021: and NOT EXISTS
9022: (select NULL
9072: gUserId, --created_by
9073: gLoginId --last_update_login
9074: from
9075: mtl_parameters mp1,
9076: mtl_system_items_tl m,
9077: bom_cto_src_orgs bcso,
9078: fnd_languages l,
9079: mtl_item_revisions_b mr, --3338108
9080: mtl_parameters mp2 --4109427
9107:
9108: --EXECUTE IMMEDIATE sql_stmt USING pConfigId,gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId,pConfigId; Bugfix 6063990
9109:
9110: IF PG_DEBUG <> 0 THEN
9111: oe_debug_pub.add('Inserted into mtl_system_items_tl.');
9112: END IF;
9113:
9114: EXCEPTION
9115: when OTHERS then
9167:
9168:
9169: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9170:
9171: insert into mtl_system_items_b
9172: (inventory_item_id,
9173: organization_id,
9174: last_update_date,
9175: last_updated_by,
9828: 3, --repair_program
9829: 2 --outsourced_assembly
9830: --end r12,4574899
9831: from
9832: mtl_system_items_b m, -- Model
9833: mtl_system_items_b config,
9834: bom_cto_order_lines bcol,
9835: bom_cto_src_orgs bcso
9836: where m.inventory_item_id = p_model_id
9829: 2 --outsourced_assembly
9830: --end r12,4574899
9831: from
9832: mtl_system_items_b m, -- Model
9833: mtl_system_items_b config,
9834: bom_cto_order_lines bcol,
9835: bom_cto_src_orgs bcso
9836: where m.inventory_item_id = p_model_id
9837: and bcso.model_item_id = p_model_id
9841: and config.organization_id = bcol.ship_from_org_id
9842: and m.organization_id = bcso.organization_id
9843: and NOT EXISTS
9844: (select NULL
9845: from mtl_system_items_b
9846: where inventory_item_id = p_config_id
9847: and organization_id = bcso.organization_id);
9848:
9849: