[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
1799: and col.owner = l_customer_schema;
1800:
1801: If l_chk_col > 0 then
1802: sql_stmt := 'SELECT substrb(oel.user_item_description,1,240)'
1803: ||' FROM oe_order_lines_all oel , mtl_system_items msi'
1804: ||' WHERE oel.ship_from_org_id = msi.organization_id'
1805: ||' AND oel.inventory_item_id = msi.inventory_item_id'
1806: ||' AND oel.line_id = :p_interface_source_line_id'
1807: ||' AND msi.organization_id = :p_destination_org_id'
1886: l_stmt_num := 110;
1887: Begin
1888: SELECT 'Y'
1889: into l_pegging_flag
1890: FROM mtl_system_items_b
1891: WHERE inventory_item_id = p_item_id
1892: AND organization_id = p_destination_org_id
1893: AND end_assembly_pegging_flag IN ('I','X');
1894: Exception
1936:
1937: l_stmt_num := 140;
1938: select max(revision) into l_item_revision
1939: from mtl_item_revisions mir,
1940: mtl_system_items msi
1941: where msi.organization_id = p_destination_org_id
1942: and msi.inventory_item_id = p_item_id
1943: and mir.organization_id = msi.organization_id
1944: and mir.inventory_item_id = msi.inventory_item_id
2479: This is the main API which will get called by both online and batch program
2480: This will rollup the list price for the Buy configurations, from the components selected in
2481: the order. The list price will be taken from Po Validation org. If the component/Model is not
2482: Defined in the Po validation org, the price will be taken as 0. The rolled up price of the configuration
2483: will be update in Mtl_system_items in po validation org. Apart from Rolling up list price, This procedure
2484: will also Rollup the blanket price from the model blanket and create a new blanket and ASL entries for
2485: configuration items.
2486:
2487: Parameter explanations
2493: configuration will not be overwritten in Po validation org.
2494: Only if the list_price_per_unit is null in po validation org
2495: the rolled up price will be updated.
2496: If this parameter is passed as 'Y', this API will update the
2497: mtl_system_items anyway
2498:
2499: P_Called_in_batch -- When the purchase price rollup is done for more than one order
2500: this parameter should be set to 'Y'. If this is done online for a
2501: Single order then it should be 'N'. The default value for this is 'N'.
2631: from bom_explosion_temp
2632: where group_id = x_group_id
2633: and assembly_item_id = p_config_item_id
2634: and component_item_id = (select base_item_id
2635: from mtl_system_items
2636: where inventory_item_id = p_config_item_id
2637: and rownum =1);
2638: l_comp_exists := 'Y';
2639: Exception When no_data_found then
2780: exp.component_item_id comp_item_id,
2781: msi.primary_uom_code prim_uom_code,
2782: nvl(msi.list_price_per_unit,0) list_price_per_unit
2783: from bom_explosion_temp exp,
2784: mtl_system_items msi
2785: where exp.group_id = p_group_id
2786: and exp.assembly_item_id = p_config_item_id
2787: and exp.component_item_id = msi.inventory_item_id
2788: and msi.organization_id = p_org_id;
2794: where exp.group_id = p_group_id
2795: and exp.assembly_item_id = p_config_item_id
2796: and exp.configurator_flag = 'Y'
2797: and not exists (select 'X'
2798: from mtl_system_items msi
2799: where msi.inventory_item_id = exp.component_item_id
2800: and msi.organization_id = p_org_id);
2801:
2802: l_model_qty Number;
2976: x_rolled_price Number;
2977: l_doc_return Varchar2(5);
2978: i Number;
2979: x_int_header_id Number;
2980: x_segment1 mtl_system_items.segment1%type;
2981: l_assgn_set_id Number;
2982: x_start_date Date;
2983: x_end_date Date;
2984: l_doc_exists Boolean;
3001: select base_item_id,
3002: config_orgs
3003: into l_model_item_id,
3004: l_config_creation
3005: from mtl_system_items
3006: where inventory_item_id = p_config_item_id
3007: and rownum =1;
3008: IF PG_DEBUG <> 0 THEN
3009: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
3293: Begin
3294:
3295: select 'Y',organization_id
3296: into l_config_exists,l_po_valid_org
3297: from mtl_system_items
3298: where inventory_item_id = p_config_item_id
3299: and organization_id = (select fsp.inventory_organization_id
3300: from financials_system_params_all fsp,
3301: po_headers_all poh
3338: lStmtNumber := 170;
3339:
3340: select segment1
3341: into x_segment1
3342: from Mtl_system_items
3343: where inventory_item_id = p_config_item_id
3344: and rownum = 1;
3345:
3346: IF PG_DEBUG <> 0 THEN
3528: l_conv_qty Number;
3529: l_model_po_uom Bom_cto_order_lines.order_quantity_uom%type;
3530: l_model_order_uom Bom_cto_order_lines.order_quantity_uom%type;
3531: l_po_uom_qty Number;
3532: l_config_item_id Mtl_system_items.inventory_item_id%type;
3533: i Number;
3534: Cursor buy_comps_cur is
3535: select exp.component_item_id comp_item_id,
3536: exp.component_quantity comp_qty,
3553: If p_doc_line_id is not null then
3554:
3555: select base_item_id
3556: into l_base_model_id
3557: from mtl_system_items
3558: where inventory_item_id = p_config_item_id
3559: and rownum = 1;
3560:
3561: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3610: select nvl(list_price_per_unit,0),
3611: primary_uom_code
3612: into l_unit_price,
3613: l_po_uom
3614: from mtl_system_items
3615: where inventory_item_id = buy_comps.comp_item_id
3616: and organization_id = p_po_valid_org;
3617: Exception when no_data_found then
3618: l_unit_price := 0;
3773: p_item_id IN Number,
3774: p_item_rev IN Varchar2,
3775: p_price IN Number,
3776: p_int_header_id IN Number,
3777: p_segment1 IN Mtl_system_items.segment1%type,
3778: p_start_date IN Date,
3779: p_end_date IN Date,
3780: x_return_status OUT NOCOPY Varchar2,
3781: x_msg_count OUT NOCOPY Number,
3781: x_msg_count OUT NOCOPY Number,
3782: x_msg_data OUT NOCOPY varchar2) is
3783:
3784: l_interface_line_id Number;
3785: l_segment1 Mtl_system_items.segment1%type;
3786:
3787: --Bugfix 10145427
3788: l_new_price Number;
3789:
3793: g_pg_level := g_pg_level + 3;
3794:
3795: select segment1
3796: into l_segment1
3797: from mtl_system_items
3798: where inventory_item_id = p_item_id
3799: and rownum=1;
3800:
3801:
4060: */
4061:
4062: -- The following select statement will get the
4063: -- oss attribute and config creation attribute
4064: -- from mtl system items. Both these attributes
4065: -- in master org level, hence u can get the attribute
4066: -- from any organization.
4067:
4068: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
4068: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
4069:
4070: Select option_specific_sourced
4071: into l_option_specific
4072: from mtl_system_items
4073: where inventory_item_id = p_config_item_id
4074: and rownum<2;
4075:
4076: If p_mode = 'ORDER' then
4371: IF PG_DEBUG <> 0 THEN
4372: oe_debug_pub.add('Create_purchase_doc_batch: '||'Regular cursor.');
4373: END IF;
4374: sql_stmt := 'select distinct msi.inventory_item_id '||
4375: 'from mtl_system_items msi '||
4376: 'where msi.base_item_id is not null '||
4377: 'and msi.bom_item_type = 4 '||
4378: 'and msi.replenish_to_order_flag = ''Y'' '||
4379: 'and msi.pick_components_flag = ''N'' ';
4454: OPEN pprollup_oe_cur FOR
4455: SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
4456: from oe_order_lines_all oel,
4457: oe_order_headers_all oeh,
4458: mtl_system_items msi
4459: where oel.inventory_item_id = msi.inventory_item_id
4460: and oel.ship_from_org_id = msi.organization_id
4461: and oel.header_id = oeh.header_id
4462: and oel.source_type_code = 'INTERNAL' ---- For drop ship bug# 2234858
4701: begin
4702:
4703: SELECT substrb(kfv.concatenated_segments,1,35)
4704: INTO l_pass_config_description
4705: FROM mtl_system_items_kfv kfv
4706: WHERE kfv.inventory_item_id = passedItems(j).config_item_id
4707: AND rownum = 1;
4708:
4709: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4733: begin
4734:
4735: SELECT substrb(kfv.concatenated_segments,1,35)
4736: INTO l_err_config_description
4737: FROM mtl_system_items_kfv kfv
4738: WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
4739: AND rownum = 1;
4740:
4741: oe_debug_pub.add (' '|| j ||'.'||' '|| l_err_config_description ||
4996: select distinct nvl(fsp.inventory_organization_id,0)
4997: bulk collect into l_orgs_list
4998: from inv_organization_info_v org,
4999: financials_system_params_all fsp,
5000: mtl_system_items msi
5001: where org.organization_id in (select organization_id
5002: from mtl_system_items_b
5003: where inventory_item_id = p_config_item_id)
5004: and fsp.org_id = org.operating_unit
4998: from inv_organization_info_v org,
4999: financials_system_params_all fsp,
5000: mtl_system_items msi
5001: where org.organization_id in (select organization_id
5002: from mtl_system_items_b
5003: where inventory_item_id = p_config_item_id)
5004: and fsp.org_id = org.operating_unit
5005: and msi.inventory_item_id = p_config_item_id
5006: and msi.organization_id = fsp.inventory_organization_id;
5006: and msi.organization_id = fsp.inventory_organization_id;
5007:
5008: select base_item_id
5009: into l_model_item_id
5010: from mtl_system_items
5011: where inventory_item_id = p_config_item_id
5012: and rownum = 1;
5013:
5014: If PG_DEBUG <> 0 Then
5021: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Validation org = '||l_orgs_list(i),5);
5022: lStmtNumber := 40;
5023: Select list_price_per_unit
5024: into l_list_price
5025: from mtl_system_items
5026: where inventory_item_id = p_config_item_id
5027: and organization_id = l_orgs_list(i);
5028:
5029: If pg_debug <> 0 Then
5069: -- The list price comupted by the above API is for per qty for order uom
5070: -- We need to get the list price for per qty primary uom
5071:
5072: End if;
5073: -- Update the Mtl_system_items with rolled price in po validation org
5074: -- If the p_oerwrite_list_pirce is set to 'N' then only if the list_price is
5075: -- null the rolled up price should be updated.
5076:
5077: lStmtNumber := 50;
5079: If PG_DEBUG <> 0 Then
5080: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Updating item master with list price ',5);
5081: End if;
5082:
5083: Update Mtl_system_items
5084: set list_price_per_unit = x_rolled_price
5085: where inventory_item_id = p_config_item_id
5086: and organization_id = l_orgs_list(i)
5087: and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
5093: End if;
5094: lStmtNumber := 60;
5095: select config_orgs
5096: into l_config_creation
5097: from mtl_system_items
5098: where inventory_item_id = l_model_item_id
5099: and rownum=1;
5100:
5101: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
5199: drive_mark := 0;
5200: sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, status, inventory_item_id)'||
5201: 'select oel.line_id, oel.org_id, 1, 0 '||
5202: 'from oe_order_lines_all oel,'||
5203: ' mtl_system_items msi,'||
5204: ' wf_item_activity_statuses was,'||
5205: ' wf_process_activities WPA ';
5206:
5207: sql_stmt := sql_stmt || ' where oel.inventory_item_id = msi.inventory_item_id ' ||