[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
499:
500: end get_atp_flag;
501:
502:
503: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
504: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
505: return char
506: is
507: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
500: end get_atp_flag;
501:
502:
503: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
504: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
505: return char
506: is
507: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
508: begin
503: function evaluate_atp_attributes( p_atp_flag in mtl_system_items_b.atp_flag%type
504: , p_atp_components_flag in mtl_system_items_b.atp_components_flag%type )
505: return char
506: is
507: v_atp_components_flag mtl_system_items_b.atp_components_flag%type ;
508: begin
509:
510: /* Note: Although a lot of similarity exists for some combination of flag values
511: the results are bound to change based on new functionality/bug fix issues.
1072: from oe_order_lines_all oel,
1073: oe_order_headers_all oeh,
1074: oe_transaction_types_tl oet,
1075: mtl_sales_orders mso,
1076: mtl_system_items msi
1077: where oel.line_id = lConfigLineId
1078: and item_type_code = 'CONFIG'
1079: and oeh.header_id = oel.header_id
1080: and oet.transaction_type_id = oeh.order_type_id
1872: lGrpId number;
1873: rowcount number := 1;
1874: proc_error exception ;
1875:
1876: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1877: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1878:
1879: /*Bugfix 2047428 */
1880: latpcompflag mtl_system_items.atp_components_flag%type;
1873: rowcount number := 1;
1874: proc_error exception ;
1875:
1876: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1877: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1878:
1879: /*Bugfix 2047428 */
1880: latpcompflag mtl_system_items.atp_components_flag%type;
1881: /* End Bugfix 2047428 */
1876: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
1877: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
1878:
1879: /*Bugfix 2047428 */
1880: latpcompflag mtl_system_items.atp_components_flag%type;
1881: /* End Bugfix 2047428 */
1882:
1883: -- Bugfix 2425667
1884: lrowcount number;
1892: bet.sort_order,
1893: bet.plan_level,
1894: bet.line_id,
1895: substrb(msi.concatenated_segments,1,50)
1896: from bom_explosion_temp bet,mtl_system_items_kfv msi
1897: where bet.group_id = xGrpId
1898: and bet.organization_id = msi.organization_id
1899: and bet.component_item_id = msi.inventory_item_id;
1900:
1940: /* Bugfix 2047428 */
1941: lStmtNumber := 599;
1942: select NVL(msi.atp_components_flag,'N')
1943: into latpcompflag
1944: from mtl_system_items msi , oe_order_lines_all oel
1945: where oel.inventory_item_id = msi.inventory_item_id
1946: and oel.ship_from_org_id = msi.organization_id
1947: and oel.line_id = pLineId
1948: and oel.ordered_quantity > 0;
2002: oel.Line_Id,
2003: xGrpId
2004: from
2005: oe_order_lines_all oel,
2006: mtl_system_items msi
2007: where oel.line_id = pLineId
2008: and oel.inventory_item_id = msi.inventory_item_id
2009: and oel.ship_from_org_id = msi.organization_id
2010: and oel.ordered_quantity > 0;
2071: xGrpId
2072: from
2073: oe_order_lines_all oel,
2074: bom_explosion_temp bet,
2075: mtl_system_items msi
2076: where oel.ato_line_id = pLineId
2077: and oel.line_id <> pLineId
2078: and oel.inventory_item_id = msi.inventory_item_id
2079: and oel.ship_from_org_id = msi.organization_id
2110: xGrpId
2111: from
2112: oe_order_lines_all oel,
2113: bom_explosion_temp bet,
2114: mtl_system_items msi,
2115: bom_bill_of_materials bbm,
2116: bom_inventory_components bic
2117: where oel.ato_line_id = pLineId
2118: and oel.line_id <> pLineId
2180: begin
2181:
2182: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
2183: into v_msi_fixed_lead_time, v_msi_variable_lead_time
2184: from mtl_system_items msi , bom_explosion_temp be
2185: where be.organization_id = msi.organization_id
2186: and be.component_item_id = msi.inventory_item_id
2187: and be.line_id = pLineId
2188: and be.group_id = xGrpId; -- bugfix 1876997
2268: nvl(bic.wip_supply_type, msi2.wip_supply_type), -- 3254039 , 3298244
2269: xGrpId
2270: from
2271: bom_calendar_dates cal,
2272: mtl_system_items msi, /* PARENT */
2273: mtl_system_items msi2, /* CHILD */
2274: bom_inventory_components bic,
2275: eng_revised_items eri,
2276: bom_bill_of_materials bom,
2269: xGrpId
2270: from
2271: bom_calendar_dates cal,
2272: mtl_system_items msi, /* PARENT */
2273: mtl_system_items msi2, /* CHILD */
2274: bom_inventory_components bic,
2275: eng_revised_items eri,
2276: bom_bill_of_materials bom,
2277: mtl_parameters mp,
2415: lGrpId number;
2416: rowcount number := 1;
2417: proc_error exception ;
2418:
2419: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2420: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2421: latpcompflag mtl_system_items.atp_components_flag%type;
2422: lrowcount number;
2423: begin
2416: rowcount number := 1;
2417: proc_error exception ;
2418:
2419: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2420: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2421: latpcompflag mtl_system_items.atp_components_flag%type;
2422: lrowcount number;
2423: begin
2424:
2417: proc_error exception ;
2418:
2419: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2420: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2421: latpcompflag mtl_system_items.atp_components_flag%type;
2422: lrowcount number;
2423: begin
2424:
2425: /*---------------------------------------------------+
2449:
2450: lStmtNumber := 599;
2451: select NVL(msi.atp_components_flag,'N')
2452: into latpcompflag
2453: from mtl_system_items msi , oe_order_lines_all oel
2454: where oel.inventory_item_id = msi.inventory_item_id
2455: and oel.ship_from_org_id = msi.organization_id
2456: and oel.line_id = pLineId
2457: and oel.ordered_quantity > 0;
2500: oel.Line_Id,
2501: xGrpId
2502: from
2503: oe_order_lines_all oel,
2504: mtl_system_items msi
2505: where oel.line_id = pLineId
2506: and oel.inventory_item_id = msi.inventory_item_id
2507: and oel.ship_from_org_id = msi.organization_id
2508: and oel.ordered_quantity > 0;
2560: xGrpId
2561: from
2562: oe_order_lines_all oel,
2563: bom_explosion_temp bet,
2564: mtl_system_items msi
2565: where oel.ato_line_id = pLineId
2566: and oel.line_id <> pLineId
2567: and oel.inventory_item_id = msi.inventory_item_id
2568: and oel.ship_from_org_id = msi.organization_id
2592: begin
2593:
2594: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
2595: into v_msi_fixed_lead_time, v_msi_variable_lead_time
2596: from mtl_system_items msi , bom_explosion_temp be
2597: where be.organization_id = msi.organization_id
2598: and be.component_item_id = msi.inventory_item_id
2599: and be.line_id = pLineId
2600: and be.group_id = xGrpId;
2675: nvl(bic.wip_supply_type, msi2.wip_supply_type),
2676: xGrpId
2677: from
2678: bom_calendar_dates cal,
2679: mtl_system_items msi,
2680: mtl_system_items msi2,
2681: bom_inventory_components bic,
2682: eng_revised_items eri,
2683: bom_bill_of_materials bom,
2676: xGrpId
2677: from
2678: bom_calendar_dates cal,
2679: mtl_system_items msi,
2680: mtl_system_items msi2,
2681: bom_inventory_components bic,
2682: eng_revised_items eri,
2683: bom_bill_of_materials bom,
2684: mtl_parameters mp,
2796:
2797: -- 3779636
2798:
2799:
2800: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2801: , p_atp_comp mtl_system_items.atp_components_flag%type
2802: , p_item_type mtl_system_items.bom_item_type%type )
2803: return number
2804: is
2797: -- 3779636
2798:
2799:
2800: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2801: , p_atp_comp mtl_system_items.atp_components_flag%type
2802: , p_item_type mtl_system_items.bom_item_type%type )
2803: return number
2804: is
2805: begin
2798:
2799:
2800: function evaluate_order( p_atp_flag mtl_system_items.atp_flag%type
2801: , p_atp_comp mtl_system_items.atp_components_flag%type
2802: , p_item_type mtl_system_items.bom_item_type%type )
2803: return number
2804: is
2805: begin
2806: if( p_atp_comp in ( 'C' , 'Y' ) AND p_atp_flag in ( 'R' , 'C')) then
2836: rowcount number := 1;
2837: proc_error exception ;
2838: i number;
2839:
2840: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2841: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2842:
2843: /*Bugfix 2047428 */
2844: latpcompflag mtl_system_items.atp_components_flag%type;
2837: proc_error exception ;
2838: i number;
2839:
2840: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2841: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2842:
2843: /*Bugfix 2047428 */
2844: latpcompflag mtl_system_items.atp_components_flag%type;
2845: /* End Bugfix 2047428 */
2840: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
2841: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
2842:
2843: /*Bugfix 2047428 */
2844: latpcompflag mtl_system_items.atp_components_flag%type;
2845: /* End Bugfix 2047428 */
2846:
2847: -- 3893281 : New debug cursor and variables
2848: cursor debug_bet is
2852: bet.sort_order,
2853: bet.plan_level,
2854: bet.line_id,
2855: substrb(msi.concatenated_segments,1,50)
2856: from bom_explosion_temp bet,mtl_system_items_kfv msi
2857: where bet.group_id = xGrpId
2858: and bet.organization_id = msi.organization_id
2859: and bet.component_item_id = msi.inventory_item_id;
2860:
2892: /* Bugfix 2047428 */
2893: lStmtNumber := 599;
2894: select NVL(msi.atp_components_flag,'N')
2895: into latpcompflag
2896: from mtl_system_items msi
2897: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
2898: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
2899:
2900:
2933: primary_uom_code, -- Bugfix 1998386
2934: sort_order,
2935: group_id)
2936: -- Bugfix 1998386 Change this to select statement to select
2937: -- primary_uom_code from mtl_system_items.
2938: -- Also CONVERT_UOM function is used to convert
2939: -- p_ship_set.quantity_ordered(i) if the ordered UOM
2940: -- is different from primary UOM.
2941: /*values (
2960: p_ship_set.inventory_item_id(i)),
2961: msi.primary_uom_code,
2962: 2, -- BUG no 1288823 modification
2963: lGroupid2
2964: from mtl_system_items msi
2965: where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
2966: and msi.organization_id = p_ship_set.source_organization_id(i);
2967:
2968: i := p_ship_set.inventory_item_id.NEXT(i);
2985: line_id,
2986: group_id)
2987:
2988: -- Bugfix 1998386 Change this to select statement to
2989: -- select primary_uom_code from mtl_system_items.
2990: -- Also CONVERT_UOM function is used to convert
2991: -- p_ship_set.quantity_ordered(i) if the ordered UOM is
2992: -- different from primary UOM.
2993:
3006: msi.primary_uom_code,
3007: p_ship_set.inventory_item_id(p_model_index),
3008: 1,
3009: xGrpId
3010: from mtl_system_items msi
3011: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
3012: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
3013:
3014:
3062: xGrpId
3063: from
3064: bom_bill_of_materials bom,
3065: bom_inventory_components bic,
3066: mtl_system_items msi, -- bugfix 1998386
3067: bom_explosion_temp bet,
3068: bom_explosion_temp bet2 /* ship set */
3069: where bet.group_id = xGrpId
3070: and bet.plan_level = level_number -1
3132:
3133: begin
3134: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
3135: into v_msi_fixed_lead_time, v_msi_variable_lead_time
3136: from mtl_system_items msi , bom_explosion_temp be
3137: where be.organization_id = msi.organization_id
3138: and be.component_item_id = msi.inventory_item_id
3139: and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
3140:
3208: NULL,
3209: xGrpId
3210: from
3211: bom_calendar_dates cal,
3212: mtl_system_items msi, /* PARENT */
3213: mtl_system_items msi2, /* CHILD [BUG#1518894] */
3214: bom_inventory_components bic,
3215: eng_revised_items eri,
3216: bom_bill_of_materials bom,
3209: xGrpId
3210: from
3211: bom_calendar_dates cal,
3212: mtl_system_items msi, /* PARENT */
3213: mtl_system_items msi2, /* CHILD [BUG#1518894] */
3214: bom_inventory_components bic,
3215: eng_revised_items eri,
3216: bom_bill_of_materials bom,
3217: mtl_parameters mp,
3346: rowcount number := 1;
3347: proc_error exception ;
3348: i number;
3349:
3350: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3351: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3352: latpcompflag mtl_system_items.atp_components_flag%type;
3353:
3354:
3347: proc_error exception ;
3348: i number;
3349:
3350: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3351: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3352: latpcompflag mtl_system_items.atp_components_flag%type;
3353:
3354:
3355: begin
3348: i number;
3349:
3350: v_msi_fixed_lead_time mtl_system_items.fixed_lead_time%type ;
3351: v_msi_variable_lead_time mtl_system_items.variable_lead_time%type ;
3352: latpcompflag mtl_system_items.atp_components_flag%type;
3353:
3354:
3355: begin
3356:
3374:
3375: lStmtNumber := 599;
3376: select NVL(msi.atp_components_flag,'N')
3377: into latpcompflag
3378: from mtl_system_items msi
3379: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
3380: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
3381:
3382:
3426: p_ship_set.inventory_item_id(i)),
3427: msi.primary_uom_code,
3428: 2,
3429: lGroupid2
3430: from mtl_system_items msi
3431: where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
3432: and msi.organization_id = p_ship_set.source_organization_id(i);
3433:
3434: i := p_ship_set.inventory_item_id.NEXT(i);
3465: msi.primary_uom_code,
3466: p_ship_set.inventory_item_id(p_model_index),
3467: 1,
3468: xGrpId
3469: from mtl_system_items msi
3470: where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
3471: and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
3472:
3473:
3513: xGrpId
3514: from
3515: bom_bill_of_materials bom,
3516: bom_inventory_components bic,
3517: mtl_system_items msi,
3518: bom_explosion_temp bet,
3519: bom_explosion_temp bet2
3520: where bet.group_id = xGrpId
3521: and bet.plan_level = level_number -1
3550:
3551: begin
3552: select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
3553: into v_msi_fixed_lead_time, v_msi_variable_lead_time
3554: from mtl_system_items msi , bom_explosion_temp be
3555: where be.organization_id = msi.organization_id
3556: and be.component_item_id = msi.inventory_item_id
3557: and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
3558:
3622: NULL,
3623: xGrpId
3624: from
3625: bom_calendar_dates cal,
3626: mtl_system_items msi,
3627: mtl_system_items msi2,
3628: bom_inventory_components bic,
3629: eng_revised_items eri,
3630: bom_bill_of_materials bom,
3623: xGrpId
3624: from
3625: bom_calendar_dates cal,
3626: mtl_system_items msi,
3627: mtl_system_items msi2,
3628: bom_inventory_components bic,
3629: eng_revised_items eri,
3630: bom_bill_of_materials bom,
3631: mtl_parameters mp,
4028: select nvl(fixed_lead_time,0),
4029: nvl(variable_lead_time,0)
4030: into lFixedLt,
4031: lVarLt
4032: from mtl_system_items msi
4033: where msi.inventory_item_id = lItem_id
4034: and msi.organization_id = lOrg_id;
4035:
4036: lStmtNumber := 710;
4142:
4143:
4144:
4145: /*-----------------------------------------------------+
4146: Creates configuration item in mtl_system_items.
4147: This function does not check the validity of
4148: data in oe_order_lines. The validity checks
4149: like 'check that config row does not exists already'
4150: are implemented in activity wrapper / batch program
4150: are implemented in activity wrapper / batch program
4151: +-----------------------------------------------------*/
4152:
4153: --- The following procedure is modified by Renga Kannan on 08/31/01
4154: --- While instering the rows into MTL_SYSTEM_ITEMS one new function
4155: --- is called for list_price field. CTO_CUSTOM_LIST_PRICE_PK.get_list_price
4156: --- is called to get the list price thru custom calculation. if it is null then
4157: --- it will be copied from its model list price. This change is doen as part of
4158: --- Procuring config Phase I -- Patchset G
4193: lReturnStatus varchar2(1);
4194:
4195: lConfigSegName fnd_id_flex_segments.segment_name%type;
4196: app_column fnd_id_flex_segments.segment_name%type;
4197: new_item_num mtl_system_items.segment1%Type ;
4198:
4199: lCreateRules varchar2(1);
4200: lStatus number;
4201:
4199: lCreateRules varchar2(1);
4200: lStatus number;
4201:
4202: type lSegType is table of
4203: mtl_system_items_interface.segment1%type
4204: index by binary_integer;
4205:
4206: seg lSegType ;
4207:
4209:
4210: -- Start Bugfix 2157740
4211: cursor c_get_org_id is
4212: select msi.organization_id src_org_id
4213: from mtl_system_items msi
4214: where msi.inventory_item_id = pConfigId
4215: and not exists
4216: (SELECT 'x'
4217: FROM FND_ATTACHED_DOCUMENTS
4216: (SELECT 'x'
4217: FROM FND_ATTACHED_DOCUMENTS
4218: WHERE pk1_value = to_char(msi.organization_id) -- 2774571
4219: AND pk2_value = to_char(msi.inventory_item_id) -- 2774571
4220: AND entity_name = 'MTL_SYSTEM_ITEMS');
4221:
4222:
4223: v_src_org_id mtl_system_items.organization_id%type;
4224: l_document_id Number;
4219: AND pk2_value = to_char(msi.inventory_item_id) -- 2774571
4220: AND entity_name = 'MTL_SYSTEM_ITEMS');
4221:
4222:
4223: v_src_org_id mtl_system_items.organization_id%type;
4224: l_document_id Number;
4225:
4226:
4227: -- End bugfix 2157740
4431: seg(17),
4432: seg(18),
4433: seg(19),
4434: seg(20)
4435: from mtl_system_items msi
4436: where inventory_item_id = pModelId
4437: and organization_id = lValidationOrg;
4438:
4439: lStmtNumber := 50;
4438:
4439: lStmtNumber := 50;
4440:
4441: if lNumberMethod = 1 then
4442: select mtl_system_items_B_S.nextval
4443: into lNextNum
4444: from dual;
4445:
4446: -- bugfix 1933740 : Replaced seg(1) with seg(app_col_ind)
4449: into new_item_num
4450: from dual;
4451:
4452: elsif lNumberMethod = 2 then
4453: select to_char(mtl_system_items_B_S.nextval)
4454: into new_item_num
4455: from dual;
4456:
4457: -- 2652379 : When numbering method is 3 , i.e Replace with order num, line num;
4555: then
4556: IF PG_DEBUG <> 0 THEN
4557: oe_debug_pub.add( 'Create_Item: '|| 'generating unique name' ,2 );
4558: END IF;
4559: select to_char(mtl_system_items_B_S.nextval)
4560: into lNextNum
4561: from dual;
4562: seg(app_col_ind) := new_item_num || lCiDel || lNextNum;
4563: end if;
4570:
4571: IF PG_DEBUG <> 0 THEN
4572: oe_debug_pub.add('Create_Item: ' || 'generating unique name' ,2 );
4573: END IF;
4574: select to_char(mtl_system_items_B_S.nextval)
4575: into lNextNum
4576: from dual;
4577: seg(app_col_ind) := new_item_num || lCiDel || lNextNum;
4578: exit;
4584:
4585: oe_debug_pub.add('Create_Item: ' || 'new_item number is ' || seg(app_col_ind) ,2 );
4586: END IF;
4587:
4588: select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
4589:
4590: IF PG_DEBUG <> 0 THEN
4591: oe_debug_pub.add('Create_Item: ' || 'new pConfigId is '||to_char(pConfigId),2);
4592: END IF;
4636: seg(17),
4637: seg(18),
4638: seg(19),
4639: seg(20)
4640: from mtl_system_items msi
4641: where inventory_item_id = pConfigId;
4642:
4643: IF PG_DEBUG <> 0 THEN
4644: oe_debug_pub.add('Create_Item: ' || 'matched pConfigId is '||to_char(pConfigId),2);
4651: -- rkaza. 10/21/2004. bug 3860077. Item type should be null if profile is null.
4652: lItemType := FND_PROFILE.Value('BOM:CONFIG_ITEM_TYPE');
4653:
4654: /*-----------------------------------------------------------+
4655: Insert a row into the mtl_system_items table.
4656: +------------------------------------------------------------*/
4657:
4658: --xTableName := 'MTL_SYSTEM_ITEMS';
4659: lStmtNumber := 80;
4654: /*-----------------------------------------------------------+
4655: Insert a row into the mtl_system_items table.
4656: +------------------------------------------------------------*/
4657:
4658: --xTableName := 'MTL_SYSTEM_ITEMS';
4659: lStmtNumber := 80;
4660:
4661: /* need to add attribute controlled statement for preconfigured item */
4662: oe_debug_pub.add('Create_Item: ' || 'p_mode is '|| p_mode ,2);
4674:
4675: -- Bug 9223457.added additional attribute columns added in 12.1
4676: -- for mtl_sys_items.pdube
4677:
4678: insert into mtl_system_items_b
4679: (inventory_item_id,
4680: organization_id,
4681: last_update_date,
4682: last_updated_by,
5376: 3,
5377: 2
5378:
5379: from
5380: mtl_system_items_b m, -- Model
5381: bom_cto_src_orgs bcso,
5382: bom_cto_order_lines bcol
5383: where m.inventory_item_id = pModelId
5384: and bcso.model_item_id = pModelId
5386: and bcol.line_id = bcso.line_id
5387: and m.organization_id = bcso.organization_id
5388: and NOT EXISTS
5389: (select NULL
5390: from mtl_system_items_b
5391: where inventory_item_id = pConfigId
5392: and organization_id = m.organization_id);
5393:
5394: sqlcnt := sql%rowcount; -- Added for bug 8305535
5393:
5394: sqlcnt := sql%rowcount; -- Added for bug 8305535
5395:
5396: IF PG_DEBUG <> 0 THEN
5397: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5398:
5399: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sqlcnt||' rows in mtl_system_items_b',2);
5400: END IF;
5401: -- added as part of bugfix 1811007
5395:
5396: IF PG_DEBUG <> 0 THEN
5397: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
5398:
5399: oe_debug_pub.add('Create_Item: ' || 'Inserted '||sqlcnt||' rows in mtl_system_items_b',2);
5400: END IF;
5401: -- added as part of bugfix 1811007
5402:
5403: --Start Bugfix 8305535
5440: IF PG_DEBUG <> 0 THEN
5441: oe_debug_pub.add('Create_Item: ' || 'Fetched Org Id '|| v_get_org_id.src_org_id,3);
5442: END IF;
5443: fnd_attached_documents2_pkg.copy_attachments (
5444: X_from_entity_name => 'MTL_SYSTEM_ITEMS',
5445: X_from_pk1_value => v_get_org_id.src_org_id,
5446: X_from_pk2_value => pModelId,
5447: X_from_pk3_value => NULL,
5448: X_from_pk4_value => NULL,
5446: X_from_pk2_value => pModelId,
5447: X_from_pk3_value => NULL,
5448: X_from_pk4_value => NULL,
5449: X_from_pk5_value => NULL,
5450: X_to_entity_name => 'MTL_SYSTEM_ITEMS',
5451: X_to_pk1_value => v_get_org_id.src_org_id,
5452: X_to_pk2_value => pConfigId,
5453: X_to_pk3_value => NULL,
5454: X_to_pk4_value => NULL,
5495: -- begin bugfix 1811007
5496:
5497: DECLARE
5498:
5499: o_weight_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5500: o_volume_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5501: o_weight number := 0;
5502: o_volume number := 0;
5503: x_status number := 0;
5496:
5497: DECLARE
5498:
5499: o_weight_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5500: o_volume_uom MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
5501: o_weight number := 0;
5502: o_volume number := 0;
5503: x_status number := 0;
5504: lShippingOrg number ;
5510: -- Get the weight and volume UOM of the model from the OE:Validation org.
5511:
5512: select i.weight_uom_code, i.volume_uom_code
5513: into o_weight_uom, o_volume_uom
5514: from mtl_system_items i,
5515: bom_cto_order_lines l
5516: where l.line_id = pLineId -- model line id
5517: and l.inventory_item_id = i.inventory_item_id
5518: and i.organization_id = lValidationOrg;
5536: into o_weight_uom
5537: from mtl_units_of_measure
5538: where uom_class = (select uom1.uom_class
5539: from mtl_units_of_measure uom1,
5540: mtl_system_items i,
5541: bom_cto_order_lines l
5542: where l.parent_ato_line_id = pLineId
5543: -- mbsk: replaced ato_line_id with parent_ato_line_id
5544: and l.item_type_code not in ('INCLUDED', 'CONFIG')
5571: into o_volume_uom
5572: from mtl_units_of_measure
5573: where uom_class = (select uom1.uom_class
5574: from mtl_units_of_measure uom1,
5575: mtl_system_items i,
5576: bom_cto_order_lines l
5577: where l.parent_ato_line_id = pLineId
5578: -- mbsk: replaced ato_line_id with parent_ato_line_id
5579: and l.item_type_code not in ('INCLUDED', 'CONFIG')
5647: -- Added "and a.unit_weight is null" condition so that weight/vol is updated
5648: -- only if it is not already calculated.
5649:
5650: if (o_weight_uom is not null) then
5651: update mtl_system_items a
5652: set (unit_weight, weight_uom_code) =
5653: (select CTO_UTILITY_PK.convert_uom(
5654: o_weight_uom,
5655: nvl( b.weight_uom_code, o_weight_uom) , -- bug# 3358194
5655: nvl( b.weight_uom_code, o_weight_uom) , -- bug# 3358194
5656: o_weight,
5657: b.inventory_item_id)
5658: ,nvl(b.weight_uom_code, o_weight_uom) -- bug# 3358194
5659: from mtl_system_items b
5660: where b.inventory_item_id = a.base_item_id
5661: and b.organization_id = a.organization_id)
5662: where a.inventory_item_id = pConfigId
5663: and a.unit_weight is null; -- bugfix 5623437
5663: and a.unit_weight is null; -- bugfix 5623437
5664: end if;
5665:
5666: if (o_volume_uom is not null) then -- begin bugfix 4143695
5667: update mtl_system_items a
5668: set (unit_volume, volume_uom_code) =
5669: (select CTO_UTILITY_PK.convert_uom(
5670: o_volume_uom,
5671: nvl(b.volume_uom_code, o_volume_uom), -- bug# 3358194
5671: nvl(b.volume_uom_code, o_volume_uom), -- bug# 3358194
5672: o_volume,
5673: b.inventory_item_id)
5674: ,nvl(b.volume_uom_code, o_volume_uom) -- bug# 3358194
5675: from mtl_system_items b
5676: where b.inventory_item_id = a.base_item_id
5677: and b.organization_id = a.organization_id)
5678: where a.inventory_item_id = pConfigId
5679: and a.unit_volume is null; -- bugfix 5623437;
5687:
5688: -- end bugfix 1811007
5689:
5690:
5691: oe_debug_pub.add('Create_Item: ' || 'going to insert:mtl_system_items_tl',2);
5692:
5693: /*----------------------------------------+
5694: R11.5 MLS
5695: +----------------------------------------*/
5695: +----------------------------------------*/
5696:
5697: lStmtNumber := 90;
5698:
5699: insert into mtl_system_items_tl (
5700: inventory_item_id,
5701: organization_id,
5702: language,
5703: source_lang,
5722: gLoginId --last_update_login
5723: /*
5724: commented for reintroduction of bcso
5725: from
5726: mtl_system_items_tl m, -- 2457514
5727: fnd_languages l
5728: where m.inventory_item_id = pModelId
5729: and l.installed_flag In ('I', 'B')
5730: and l.language_code = m.language -- 2457514
5729: and l.installed_flag In ('I', 'B')
5730: and l.language_code = m.language -- 2457514
5731: and NOT EXISTS
5732: (select NULL
5733: from mtl_system_items_tl t
5734: where t.inventory_item_id = pConfigId
5735: and t.organization_id = m.organization_id
5736: and t.language = m.language );
5737: */
5735: and t.organization_id = m.organization_id
5736: and t.language = m.language );
5737: */
5738: from
5739: -- bugfix 2457514 mtl_system_items_b m, -- Model
5740: mtl_system_items_tl m, -- 2457514
5741: bom_cto_src_orgs bcso,
5742: fnd_languages l
5743: where
5736: and t.language = m.language );
5737: */
5738: from
5739: -- bugfix 2457514 mtl_system_items_b m, -- Model
5740: mtl_system_items_tl m, -- 2457514
5741: bom_cto_src_orgs bcso,
5742: fnd_languages l
5743: where
5744: --m.inventory_item_id = pModelId -- bugfix 12809414
5749: and l.installed_flag In ('I', 'B')
5750: and l.language_code = m.language -- 2457514
5751: and NOT EXISTS
5752: (select NULL
5753: from mtl_system_items_tl t
5754: where t.inventory_item_id = pConfigId
5755: and t.organization_id = m.organization_id
5756: and t.language = l.language_code );
5757:
5756: and t.language = l.language_code );
5757:
5758:
5759: IF PG_DEBUG <> 0 THEN
5760: oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_tl',2);
5761: END IF;
5762:
5763:
5764:
5973: msi.weight_uom_code uom,
5974: msi.inventory_item_id
5975: FROM bom_cto_order_lines l,
5976: bom_cto_order_lines l_model,
5977: mtl_system_items msi
5978: WHERE (l.parent_ato_line_id = x_a_line_id
5979: -- MLMO:replaced ato_line_id with parent_ato_line_id and added OR condn
5980: or
5981: l.line_id = x_a_line_id)
6008: msi.volume_uom_code uom,
6009: msi.inventory_item_id
6010: FROM bom_cto_order_lines l,
6011: bom_cto_order_lines l_model,
6012: mtl_system_items msi
6013: WHERE (l.parent_ato_line_id = x_a_line_id -- MLMO: replaced ato_line_id with parent_ato_line_id
6014: or
6015: l.line_id = x_a_line_id)
6016: -- Bugfix 2576422
6215: /*
6216: commented due to reintroduction of bcso
6217: from mtl_parameters mp1,
6218: cst_item_costs c,
6219: mtl_system_items msi
6220: where c.organization_id = mp1.organization_id
6221: and c.inventory_item_id = pModelId
6222: and C.COST_TYPE_ID = 2 -- Average Costing
6223: and msi.organization_id = mp1.organization_id
6401: null,
6402: null,
6403: sysdate,
6404: null -- req_id
6405: from mtl_system_items m,
6406: bom_cto_src_orgs bcso
6407: where m.inventory_item_id = pModelId
6408: and bcso.model_item_id = pModelId
6409: and bcso.line_id = pLineId
6466: gLoginId, -- last_update_login
6467: sysdate,
6468: sysdate
6469: from
6470: mtl_system_items m,
6471: bom_cto_src_orgs bcso,
6472: mtl_parameters mp1
6473: where m.inventory_item_id = pModelId
6474: and bcso.model_item_id = pModelId
6558: from
6559: mtl_parameters MP1,
6560: cst_item_costs C,
6561: cst_item_cost_details CICD,
6562: mtl_system_items S -- 4172300
6563: where S.organization_id = C.organization_id
6564: and S.inventory_item_id = C.inventory_item_id
6565: and C.organization_id = MP1.organization_id
6566: and C.inventory_item_id = pModelId
6737: C.attribute15
6738: from
6739: mtl_parameters MP1,
6740: cst_item_costs C,
6741: mtl_system_items S,
6742: bom_cto_src_orgs bcso
6743: where S.organization_id = C.organization_id
6744: and S.inventory_item_id = C.inventory_item_id
6745: and C.inventory_item_id = pModelId
6955: C.attribute15
6956: from
6957: mtl_parameters MP1,
6958: cst_item_costs C,
6959: mtl_system_items S,
6960: bom_cto_src_orgs bcso
6961: where S.organization_id = C.organization_id
6962: and S.inventory_item_id = C.inventory_item_id
6963: and C.inventory_item_id = pModelId
7115: C.attribute15
7116: from
7117: mtl_parameters MP1,
7118: cst_item_cost_details C,
7119: mtl_system_items S,
7120: bom_cto_src_orgs bcso
7121: where S.organization_id = C.organization_id
7122: and S.inventory_item_id = C.inventory_item_id
7123: and bcso.model_item_id = pModelId
7239: C.attribute15
7240: from
7241: mtl_parameters MP1,
7242: cst_item_cost_details C,
7243: mtl_system_items S,
7244: bom_cto_src_orgs bcso
7245: where S.organization_id = C.organization_id
7246: and S.inventory_item_id = C.inventory_item_id
7247: and bcso.model_item_id = pModelId
7508: NULL, -- program_application_id
7509: NULL, -- program_id
7510: SYSDATE, -- program_update_date
7511: NULL -- request_id
7512: from mtl_system_items s,
7513: mtl_descr_element_values D,
7514: mtl_descriptive_elements E
7515: where D.inventory_item_id = S.inventory_item_id
7516: and s.inventory_item_id = pModelid
8034: l.language_code,
8035: userenv('lang')
8036: FROM fnd_languages l,
8037: mtl_cross_references_b mtl_cross,
8038: mtl_system_items_tl mtl
8039: WHERE mtl_cross.inventory_item_id = pConfigId
8040: AND mtl_cross.inventory_item_id = mtl.inventory_item_id
8041: AND mtl_cross.organization_id = mtl.organization_id
8042: AND l.language_code = mtl.language
8475: END IF;
8476:
8477: Select concatenated_segments
8478: into l_model_tab(1).ordered_item
8479: from mtl_system_items_b_kfv
8480: where inventory_item_id = pConfigId
8481: and organization_id = pOrgId;
8482:
8483: lStmtNumber := 395;
9070: IF PG_DEBUG <> 0 THEN
9071: oe_debug_pub.add( 'Check_dup_item: '|| 'Checking for duplicate item name.. ' ,2 );
9072: END IF;
9073:
9074: sql_str := 'select 1 from mtl_system_items msi where 1=1 ';
9075:
9076: if pSegment1 is not null then
9077: sql_str := sql_str||' and msi.segment1 = :pSegment1';
9078: end if;
9315: MTL_ITEM_REVISIONS_B_S.nextval, -- 3338108 --:x_item_rev_seq --revision_id is generated from sequence
9316: mp1.starting_revision --3340844
9317: from
9318: mtl_parameters mp1,
9319: mtl_system_items m
9320: where m.inventory_item_id = pConfigId
9321: and m.organization_id = mp1.organization_id
9322: and NOT EXISTS
9323: (select NULL
9373: gUserId, --created_by
9374: gLoginId --last_update_login
9375: from
9376: mtl_parameters mp1,
9377: mtl_system_items_tl m,
9378: bom_cto_src_orgs bcso,
9379: fnd_languages l,
9380: mtl_item_revisions_b mr, --3338108
9381: mtl_parameters mp2 --4109427
9408:
9409: --EXECUTE IMMEDIATE sql_stmt USING pConfigId,gUserId, gUserId, gLoginId, pModelId, pLineId, pConfigId,pConfigId; Bugfix 6063990
9410:
9411: IF PG_DEBUG <> 0 THEN
9412: oe_debug_pub.add('Inserted into mtl_system_items_tl.');
9413: END IF;
9414:
9415: EXCEPTION
9416: when OTHERS then
9470:
9471:
9472: oe_debug_pub.add( 'Entered Create Preconfigured Item ' , 1 ) ;
9473:
9474: insert into mtl_system_items_b
9475: (inventory_item_id,
9476: organization_id,
9477: last_update_date,
9478: last_updated_by,
10171: 3, --repair_program
10172: 2 --outsourced_assembly
10173: --end r12,4574899
10174: from
10175: mtl_system_items_b m, -- Model
10176: mtl_system_items_b config,
10177: bom_cto_order_lines bcol,
10178: bom_cto_src_orgs bcso
10179: where m.inventory_item_id = p_model_id
10172: 2 --outsourced_assembly
10173: --end r12,4574899
10174: from
10175: mtl_system_items_b m, -- Model
10176: mtl_system_items_b config,
10177: bom_cto_order_lines bcol,
10178: bom_cto_src_orgs bcso
10179: where m.inventory_item_id = p_model_id
10180: and bcso.model_item_id = p_model_id
10184: and config.organization_id = bcol.ship_from_org_id
10185: and m.organization_id = bcso.organization_id
10186: and NOT EXISTS
10187: (select NULL
10188: from mtl_system_items_b
10189: where inventory_item_id = p_config_id
10190: and organization_id = bcso.organization_id);
10191:
10192: