|
tantcu
Yak Posting Veteran
58 Posts |
 Posted - 2012-10-19 : 12:24:44
|
| Hi guysI have a situation here. I'm working on pricing report for a customer. I could get the report from our company software but it took more than 20 minutes to have all the pricing. My manager think it would be faster if I use SQL to retrive the report. However, I researched online to find a query for customer pricing report, the query only gives me a result for only one item ( based on inv_mast_uid)The query looks like this:execute dbo.p21_price_engine @customer_id = 6418,@company_id = 'TSD', @inv_mast_uid = '3826', @supplier_id = NULL, @disc_group_id = NULL, @prod_group_id = NULL, @mfr_class_id = NULL, @customer_part_no = '', @tran_date = {ts '2012-07-05 00:00:00.000'}, @oe_sales_unit_size = '1.0000', @oe_qty_ordered = '1', @source_location_id = 1, @oe_pricing_unit_size = '1.0000', @sales_cost = 3.5, @debug_mode = 0, @summary_price = 1, @order_type = 706, @rollup_component_price = 'N', @calculator_type = 'B', @udl_list = NULL, @configuration_id = 2703, @oe_source_location_id = 1, @limit_by_location_id = 'N',@forced_price_value = NULL,@check_inventory = 'N',@use_web_based_pricing = 'N',@sales_location_id = 1,@ship_to_id = 6418,@base_price_library_uid = NULL,@selected_price_library_uid = NULL,@customer_sensitivity_value = NULL,@customer_category_uid = NULL,@data_service_level = NULL,@data_services_exp_date_is_valid = NULL,@audit = 'N'If you looks on the @inv_mast_uid. I tried to apply more range to get more results by using BETWEEN or other operators such as ( >, >= 0,...). But it only works with = operator, do you know how I can extend this inv_mast_uid to get multiple items instead of giving only one item . |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-19 : 12:30:04
|
| You have to look at the code for the stored procedure dbo.p21_price_engine. If you have access to the database, use SQL Server Management studio and in object explorer, navigate to the server -> Databases -> YourDabaseName -> Programmability -> Stored procedures and find the stored proc. Right click and select Script Stored Proc as -> Create to -> New Query Editor window.Once you look at the code, you should be able to tell how they are using it and that will tell you what values it can process. If you like copy that part of the code and I am sure people on the forum would be able to advise you. |
 |
|
Exact multiple items