[Home] [Help]
232: p_item_id IN Number,
233: p_item_rev IN Varchar2,
234: p_price IN Number,
235: p_int_header_id IN Number,
236: p_segment1 IN mtl_system_items.segment1%type,
237: p_start_date IN date,
238: p_end_date IN date,
239: x_return_status OUT NOCOPY Varchar2,
240: x_msg_count OUT NOCOPY Number,
341: -- Bugfix 3077912: Offset Scheduled ship date by post-processing
342: -- lead time to get need_by_date.
343:
344: select nvl(postprocessing_lead_time,0) into l_offset_days
345: from mtl_system_items
346: where inventory_item_id = p_item_id
347: and organization_id = p_org_id;
348:
349: IF PG_DEBUG <> 0 THEN
1476: and column_name = 'USER_ITEM_DESCRIPTION';
1477:
1478: If l_chk_col > 0 then
1479: sql_stmt := 'SELECT substrb(oel.user_item_description,1,240)'
1480: ||' FROM oe_order_lines_all oel , mtl_system_items msi'
1481: ||' WHERE oel.ship_from_org_id = msi.organization_id'
1482: ||' AND oel.inventory_item_id = msi.inventory_item_id'
1483: ||' AND oel.line_id = :p_interface_source_line_id'
1484: ||' AND msi.organization_id = :p_destination_org_id'
1563: l_stmt_num := 110;
1564: Begin
1565: SELECT 'Y'
1566: into l_pegging_flag
1567: FROM mtl_system_items_b
1568: WHERE inventory_item_id = p_item_id
1569: AND organization_id = p_destination_org_id
1570: AND end_assembly_pegging_flag IN ('I','X');
1571: Exception
1613:
1614: l_stmt_num := 140;
1615: select max(revision) into l_item_revision
1616: from mtl_item_revisions mir,
1617: mtl_system_items msi
1618: where msi.organization_id = p_destination_org_id
1619: and msi.inventory_item_id = p_item_id
1620: and mir.organization_id = msi.organization_id
1621: and mir.inventory_item_id = msi.inventory_item_id
2094: This is the main API which will get called by both online and batch program
2095: This will rollup the list price for the Buy configurations, from the components selected in
2096: the order. The list price will be taken from Po Validation org. If the component/Model is not
2097: Defined in the Po validation org, the price will be taken as 0. The rolled up price of the configuration
2098: will be update in Mtl_system_items in po validation org. Apart from Rolling up list price, This procedure
2099: will also Rollup the blanket price from the model blanket and create a new blanket and ASL entries for
2100: configuration items.
2101:
2102: Parameter explanations
2108: configuration will not be overwritten in Po validation org.
2109: Only if the list_price_per_unit is null in po validation org
2110: the rolled up price will be updated.
2111: If this parameter is passed as 'Y', this API will update the
2112: mtl_system_items anyway
2113:
2114: P_Called_in_batch -- When the purchase price rollup is done for more than one order
2115: this parameter should be set to 'Y'. If this is done online for a
2116: Single order then it should be 'N'. The default value for this is 'N'.
2246: from bom_explosion_temp
2247: where group_id = x_group_id
2248: and assembly_item_id = p_config_item_id
2249: and component_item_id = (select base_item_id
2250: from mtl_system_items
2251: where inventory_item_id = p_config_item_id
2252: and rownum =1);
2253: l_comp_exists := 'Y';
2254: Exception When no_data_found then
2395: exp.component_item_id comp_item_id,
2396: msi.primary_uom_code prim_uom_code,
2397: nvl(msi.list_price_per_unit,0) list_price_per_unit
2398: from bom_explosion_temp exp,
2399: mtl_system_items msi
2400: where exp.group_id = p_group_id
2401: and exp.assembly_item_id = p_config_item_id
2402: and exp.component_item_id = msi.inventory_item_id
2403: and msi.organization_id = p_org_id;
2409: where exp.group_id = p_group_id
2410: and exp.assembly_item_id = p_config_item_id
2411: and exp.configurator_flag = 'Y'
2412: and not exists (select 'X'
2413: from mtl_system_items msi
2414: where msi.inventory_item_id = exp.component_item_id
2415: and msi.organization_id = p_org_id);
2416:
2417: l_model_qty Number;
2591: x_rolled_price Number;
2592: l_doc_return Varchar2(5);
2593: i Number;
2594: x_int_header_id Number;
2595: x_segment1 mtl_system_items.segment1%type;
2596: l_assgn_set_id Number;
2597: x_start_date Date;
2598: x_end_date Date;
2599: l_doc_exists Boolean;
2616: select base_item_id,
2617: config_orgs
2618: into l_model_item_id,
2619: l_config_creation
2620: from mtl_system_items
2621: where inventory_item_id = p_config_item_id
2622: and rownum =1;
2623: IF PG_DEBUG <> 0 THEN
2624: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
2908: Begin
2909:
2910: select 'Y',organization_id
2911: into l_config_exists,l_po_valid_org
2912: from mtl_system_items
2913: where inventory_item_id = p_config_item_id
2914: and organization_id = (select fsp.inventory_organization_id
2915: from financials_system_params_all fsp,
2916: po_headers_all poh
2953: lStmtNumber := 170;
2954:
2955: select segment1
2956: into x_segment1
2957: from Mtl_system_items
2958: where inventory_item_id = p_config_item_id
2959: and rownum = 1;
2960:
2961: IF PG_DEBUG <> 0 THEN
3143: l_conv_qty Number;
3144: l_model_po_uom Bom_cto_order_lines.order_quantity_uom%type;
3145: l_model_order_uom Bom_cto_order_lines.order_quantity_uom%type;
3146: l_po_uom_qty Number;
3147: l_config_item_id Mtl_system_items.inventory_item_id%type;
3148: i Number;
3149: Cursor buy_comps_cur is
3150: select exp.component_item_id comp_item_id,
3151: exp.component_quantity comp_qty,
3168: If p_doc_line_id is not null then
3169:
3170: select base_item_id
3171: into l_base_model_id
3172: from mtl_system_items
3173: where inventory_item_id = p_config_item_id
3174: and rownum = 1;
3175:
3176: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3225: select nvl(list_price_per_unit,0),
3226: primary_uom_code
3227: into l_unit_price,
3228: l_po_uom
3229: from mtl_system_items
3230: where inventory_item_id = buy_comps.comp_item_id
3231: and organization_id = p_po_valid_org;
3232: Exception when no_data_found then
3233: l_unit_price := 0;
3385: p_item_id IN Number,
3386: p_item_rev IN Varchar2,
3387: p_price IN Number,
3388: p_int_header_id IN Number,
3389: p_segment1 IN Mtl_system_items.segment1%type,
3390: p_start_date IN Date,
3391: p_end_date IN Date,
3392: x_return_status OUT NOCOPY Varchar2,
3393: x_msg_count OUT NOCOPY Number,
3393: x_msg_count OUT NOCOPY Number,
3394: x_msg_data OUT NOCOPY varchar2) is
3395:
3396: l_interface_line_id Number;
3397: l_segment1 Mtl_system_items.segment1%type;
3398:
3399: begin
3400:
3401: g_pg_level := g_pg_level + 3;
3401: g_pg_level := g_pg_level + 3;
3402:
3403: select segment1
3404: into l_segment1
3405: from mtl_system_items
3406: where inventory_item_id = p_item_id
3407: and rownum=1;
3408:
3409:
3650: */
3651:
3652: -- The following select statement will get the
3653: -- oss attribute and config creation attribute
3654: -- from mtl system items. Both these attributes
3655: -- in master org level, hence u can get the attribute
3656: -- from any organization.
3657:
3658: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
3658: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
3659:
3660: Select option_specific_sourced
3661: into l_option_specific
3662: from mtl_system_items
3663: where inventory_item_id = p_config_item_id
3664: and rownum<2;
3665:
3666: If p_mode = 'ORDER' then
3961: IF PG_DEBUG <> 0 THEN
3962: oe_debug_pub.add('Create_purchase_doc_batch: '||'Regular cursor.');
3963: END IF;
3964: sql_stmt := 'select distinct msi.inventory_item_id '||
3965: 'from mtl_system_items msi '||
3966: 'where msi.base_item_id is not null '||
3967: 'and msi.bom_item_type = 4 '||
3968: 'and msi.replenish_to_order_flag = ''Y'' '||
3969: 'and msi.pick_components_flag = ''N'' ';
4044: OPEN pprollup_oe_cur FOR
4045: SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
4046: from oe_order_lines_all oel,
4047: oe_order_headers_all oeh,
4048: mtl_system_items msi
4049: where oel.inventory_item_id = msi.inventory_item_id
4050: and oel.ship_from_org_id = msi.organization_id
4051: and oel.header_id = oeh.header_id
4052: and oel.source_type_code = 'INTERNAL' ---- For drop ship bug# 2234858
4289: begin
4290:
4291: SELECT substrb(kfv.concatenated_segments,1,35)
4292: INTO l_pass_config_description
4293: FROM mtl_system_items_kfv kfv
4294: WHERE kfv.inventory_item_id = passedItems(j).config_item_id
4295: AND rownum = 1;
4296:
4297: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4321: begin
4322:
4323: SELECT substrb(kfv.concatenated_segments,1,35)
4324: INTO l_err_config_description
4325: FROM mtl_system_items_kfv kfv
4326: WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
4327: AND rownum = 1;
4328:
4329: oe_debug_pub.add (' '|| j ||'.'||' '|| l_err_config_description ||
4584: select distinct nvl(fsp.inventory_organization_id,0)
4585: bulk collect into l_orgs_list
4586: from inv_organization_info_v org,
4587: financials_system_params_all fsp,
4588: mtl_system_items msi
4589: where org.organization_id in (select organization_id
4590: from mtl_system_items_b
4591: where inventory_item_id = p_config_item_id)
4592: and fsp.org_id = org.operating_unit
4586: from inv_organization_info_v org,
4587: financials_system_params_all fsp,
4588: mtl_system_items msi
4589: where org.organization_id in (select organization_id
4590: from mtl_system_items_b
4591: where inventory_item_id = p_config_item_id)
4592: and fsp.org_id = org.operating_unit
4593: and msi.inventory_item_id = p_config_item_id
4594: and msi.organization_id = fsp.inventory_organization_id;
4594: and msi.organization_id = fsp.inventory_organization_id;
4595:
4596: select base_item_id
4597: into l_model_item_id
4598: from mtl_system_items
4599: where inventory_item_id = p_config_item_id
4600: and rownum = 1;
4601:
4602: If PG_DEBUG <> 0 Then
4609: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Validation org = '||l_orgs_list(i),5);
4610: lStmtNumber := 40;
4611: Select list_price_per_unit
4612: into l_list_price
4613: from mtl_system_items
4614: where inventory_item_id = p_config_item_id
4615: and organization_id = l_orgs_list(i);
4616:
4617: If pg_debug <> 0 Then
4657: -- The list price comupted by the above API is for per qty for order uom
4658: -- We need to get the list price for per qty primary uom
4659:
4660: End if;
4661: -- Update the Mtl_system_items with rolled price in po validation org
4662: -- If the p_oerwrite_list_pirce is set to 'N' then only if the list_price is
4663: -- null the rolled up price should be updated.
4664:
4665: lStmtNumber := 50;
4667: If PG_DEBUG <> 0 Then
4668: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Updating item master with list price ',5);
4669: End if;
4670:
4671: Update Mtl_system_items
4672: set list_price_per_unit = x_rolled_price
4673: where inventory_item_id = p_config_item_id
4674: and organization_id = l_orgs_list(i)
4675: and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
4681: End if;
4682: lStmtNumber := 60;
4683: select config_orgs
4684: into l_config_creation
4685: from mtl_system_items
4686: where inventory_item_id = l_model_item_id
4687: and rownum=1;
4688:
4689: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4787: drive_mark := 0;
4788: sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, status, inventory_item_id)'||
4789: 'select oel.line_id, oel.org_id, 1, 0 '||
4790: 'from oe_order_lines_all oel,'||
4791: ' mtl_system_items msi,'||
4792: ' wf_item_activity_statuses was,'||
4793: ' wf_process_activities WPA ';
4794:
4795: sql_stmt := sql_stmt || ' where oel.inventory_item_id = msi.inventory_item_id ' ||