locked
adding all the column from newly created table into old store proc RRS feed

  • Question

  • Hi everyone i need some help i have to include all the column from 2 newly created tables into an existing store proc but the things is store proc was not
    created by me i dont from where to start.Below is the existing store proc and new created table1  Cube_Source_Bookings and tables2 Cube_Quotes
    USE [FCX]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_FillSalesReportingTable]    Script Date: 01/18/2012 11:55:18 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER proc [dbo].[usp_FillSalesReportingTable_test]
    AS
    BEGIN
    
    /*
    
    MOD DATE	WHO	DESCRIPTION
    --------	---	------------------------------------------------------------
    20071025	APH	CREATED
    20071026	APH	Added 'FC' RptFlag for company wide Financial numbers
    20071101	APH	Added a Last Month category for bookings, quotes, and 
    				invoices
    20071105	APH	Changed YESTERDAY to mean last Business Day (if today is
    				Monday, then "Yesterday" means Friday)
    20071108	APH	Removed criteria from where clause of backlog selects that
    				limited the results to only order dates after 04-Sep-2007
    				and change open qty calc to be qty ordered - qty invoiced
    				- qty canceled
    20071119	APH Changed PctProfit Case statement to check salesvalue between
    				-0.001 to 0.001 instead of exactly equal to 0 to fix a
    				numeric overflow problem where the PctProfit calculation was
    				way off because the salevalue on an order was 0.001
    20071121	APH	Added YTD numbers from LegacyP21
    				Removed Current Cash Balance from Financials
    20071126	APH	Fixed bug (typo) causing Invoicing numbers by Division to
    				list as Invoices by Salesrep ('ID' was 'IS') for last month
    				numbers
    20071205	APH	1. Fixed join clause (removed OR clause) to the 
    				company_division_branch_heirarchy view since I modified
    				the rollup structure yesterday and it broke the OR join
    				2. Modified to load numbers into a SalesReporting_LOAD table
    				then, at the end, replace the SalesReporting table's records
    				with those in LOAD, this way if an error occurs during 
    				processing, it won't undo yesterday's numbers, added better
    				error checking as part of this
    20071219	APH	Added logic to update the salesrep_name field in the
    				salesrep_branches table from the name as it is in Prophet21
    				so changes made each day in the live system will propogate to
    				to FCX tables
    20080115	APH	Added new logic that checks to see if the month has changed,
    				the quarter has changed, or the year has changed and if so,
    				run the usp_FillSalesReportingSnapshots proc to create a
    				snapshot for last month, last quarter, and last year.
    20080117	APH	Added @proc_name variable and test check to update either
    				the [SalesReporting_TEST] table or the [SalesReporting] table
    				based on whether the currently running stored procedure is
    				a "_TEST" version
    				...
    				Also moved sub-logic for creating quote and booked numbers
    				into a sub-proc and call it repeatedly instead of having the
    				code repeated over and over and over in this stored proc
    20080212	APH	Added code to print out comments showing where in the process
    				the proc was running so it's easier to debug when a problem
    				occurs...also changed a check for zero value to a "between
    				-0.01 and 0.01" check instead.
    20080507	APH Changed Backlog to look at salesrep_rollup and salesrep_branches
    				table to determine what sales are allocated to what salesrep
    				and from there the branch to allocate them to
    20080513	APH Added (nolock) clauses to all select statements to speedup and
    				avoid deadlock victimization ... and removed 'All' option from
    				date range selections
    20080806	APH Changed "Last Year" to "LYTD" and added logic to deal with zero
    				records returned from usp_GetBQ4SalesReporting
    20080904	APH Fixed an issue with setting @row_count after checking for an error
                    which caused it to always be zero (never mattered before because
                    it was always zero before today but since this is one year from the
                    day we went live in this system, the LYTD numbers started returning
                    records)
    20090609	APH Modified to make @RunID parameter used in running invoicing numbers
                    date and time specific
    20090805	APH Added code to generate invoicing numbers for invoices against orders
    				booked in the same month. New RptFlag Type is "K"
    20090821	APH added code to load cube_invoicing_data table every night
    20091215	APH Began adding code to create limitorque-only numbers
    20091216	APH additional limitorque code
    20091217	APH Fixed bug because I typod "QB" instead of "BB" when setting the
    				limitorque numbers for bookings at the branch level
    20101229	APH added 2739 as rep order type (RMA)
    20110113	APH Added code to include date_paid for new attribute in cube
    
    NOTES
    ----------------------------------------------------------------------------
    RptFlag is used to break out different types of reporting data and the
    different levels of reporting data.  The first character is the type and
    the second character is the level.
    
    The types are: 
    
    	* ALL *
    	Q = quotes
    	B = Bookings
    	I = Invoices
    	O = Open/Backlog
    	F = Financials
    	K = Invoices against Bookings in same month
    	
    	* LIMITORQUE (by product group) ONLY *
    
    	1 = Limitorque quotes
    	2 = Limitorque Bookings
    	3 = Limitorque Invoices
    
    The levels are: 
    
    	S = salesrep level...meaning the level a typical salesrep would see
    	B = branch level...meaning the level a branch manager would see
    	D = division level...meaning the level a division head would see
    	C = company level...meaning the level an executive would see
    
    Quotes, Bookings, and Invoices are reported by date range (Yesterday, Month
    To Date, and Year To Date) but Open/Backlog is just everything open, with
    no consideration given to any dates.  Financials have specific date ranges
    for some numbers and just current open balances/values for others.
    
    */
    
    -- CREATE VARIABLE TO HOLD ERROR FLAG IF AN ERROR OCCURS
    -- AND CREATE A VARIABLE TO HOLD THE NUMBER OF RECORDS AFFECTED BY THE LAST COMMAND
    
    declare @error_flag int, @row_count int
    set @error_flag = 0
    set @row_count = 0
    
    -- list of limitorque product group IDs
    declare @limitorque_prod_group_id_list varchar(255)
    set @limitorque_prod_group_id_list = '''590'',''591'',''592'',''593'',''594'',''595'''
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- UPDATE SALESREP BRANCHES TABLE WITH PROPER SALESREP NAMES'
    	print '-- BEFORE WE DO ANYTHING ELSE'
    end
    
    update salesrep_branches
    set salesrep_name = v.salesrep_name
    from salesrep_branches 
    	join vw_all_salesreps v (nolock)
    		on salesrep_branches.salesrep_id = v.salesrep_id
    where salesrep_branches.salesrep_name not like v.salesrep_name
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- CLEAR SALES REPORTING TABLE CURRENT CONTENTS'
    end
    
    delete from SalesReporting_LOAD
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '--'
    	print '--'
    	print '-- FILL IT WITH QUOTES DATA FIRST'
    	print '--'
    	print '--'
    end
    
    declare @date_range_int_param int, @date_range_desc nvarchar(50)
    
    declare date_ranges cursor for 
    	select int_param_value, range_desc 
    		from date_ranges 
    			where valid_quotes=1 
    				and delete_flag='N'
    				and range_desc <> 'All'
    
    open date_ranges
    
    fetch next from date_ranges into @date_range_int_param, @date_range_desc
    
    while (@@FETCH_STATUS=0)
    begin
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SALESREP level'
    		print '--'
    	end
    
    	-- once for everybody
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'S', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'S', @date_range_int_param 
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- save rptflag temporarily to keep separate from limitorque
    	-- will set back to QS at the end
    	update SalesReporting_LOAD
    	set RptFlag = 'xS' where RptFlag = 'QS'
    	
    	-- now get numbers for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'S', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'S', @date_range_int_param , null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- make RptFlag indicate these records are for limitorque only
    	update SalesReporting_LOAD
    	set RptFlag = '1S' where RptFlag = 'QS'
    		
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- Quotes info at the BRANCH level of detail'
    		print '--'
    	end
    
    	-- once for all
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'B', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'B', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to keep everybody and limitorque separate
    	-- will change back to QB at end
    	update SalesReporting_LOAD
    	set RptFlag = 'xB' where RptFlag = 'QB'
    	
    	-- and once for limitorque only
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'B', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'B', @date_range_int_param , null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to limitorque specific version
    	update SalesReporting_LOAD
    	set RptFlag = '1B' where RptFlag = 'QB'
    	
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- Quotes info at the DIVISION level of detail'
    		print '--'
    	end
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'D', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'D', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to save quotes for all items
    	-- from getting clobbered by next step
    	update SalesReporting_LOAD
    	set RptFlag = 'xD' where RptFlag = 'QD'
    
    	-- and now for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'D', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'D', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- move QD records (quotes for all items summarized at division level)
    	-- to LD records (quotes for LIMITORQUE items summarized ad division level)
    	update SalesReporting_LOAD
    	set RptFlag = '1D' where RptFlag = 'QD'
    	
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '---'
    		print '---  COMPANY WIDE TOTALS'
    		print '---'
    	end
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'C', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'C', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- temporarily make company wide quote summary records
    	-- use a different rptflag so it doesn't get mixed up with
    	-- limitorque numbers inserted in the next step
    	update SalesReporting_LOAD
    	set RptFlag = 'xC' where RptFlag = 'QC'
    	
    	-- and now for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'Q', 'C', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'Q', 'C', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- move QC records (quotes for all items summarized at company wide level)
    	-- to LC records (quotes for LIMITORQUE items summarized at company wide level)
    	update SalesReporting_LOAD
    	set RptFlag = '1C' where RptFlag = 'QC'
    
    	-- now get the next date range and to it all again until we've processed
    	-- all the valid date ranges for quotes dashboard reporting
    	fetch next from date_ranges into @date_range_int_param, @date_range_desc
    END
    
    CLOSE date_ranges
    deallocate date_ranges
    
    --
    -- Now all those records in SalesReporting_LOAD table where we switched
    -- the RptFlag to 'x%' need to be set back to 'Q%'
    --
    update SalesReporting_LOAD
    set RptFlag = 'Q'+substring(RptFlag,2,1) where left(RptFlag,1)='x'
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '--'
    	print '-- BOOKINGS DATA NEXT'
    	print '--'
    end
    
    declare date_ranges cursor for 
    	select int_param_value, range_desc 
    		from date_ranges 
    			where valid_booked=1 
    				and delete_flag='N'
    				and range_desc <> 'All'
    
    open date_ranges
    
    fetch next from date_ranges into @date_range_int_param, @date_range_desc
    
    while (@@FETCH_STATUS=0)
    begin
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SALESREP level'
    		print '--'
    	end
    
    	-- once for everybody
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'S', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'S', @date_range_int_param 
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- save rptflag temporarily to keep separate from limitorque
    	-- will set back to BS at the end
    	update SalesReporting_LOAD
    	set RptFlag = 'xS' where RptFlag = 'BS'
    	
    	-- now get numbers for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'S', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'S', @date_range_int_param , null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- make RptFlag indicate these records are for limitorque only
    	update SalesReporting_LOAD
    	set RptFlag = '2S' where RptFlag = 'BS'
    		
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- bookings info at the BRANCH level of detail'
    		print '--'
    	end
    
    	-- once for all
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'B', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'B', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to keep everybody and limitorque separate
    	-- will change back to BB at end
    	update SalesReporting_LOAD
    	set RptFlag = 'xB' where RptFlag = 'BB'
    	
    	-- and once for limitorque only
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'B', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'B', @date_range_int_param , null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to limitorque specific version
    	update SalesReporting_LOAD
    	set RptFlag = '2B' where RptFlag = 'BB'
    	
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- bookings info at the DIVISION level of detail'
    		print '--'
    	end
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'D', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'D', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- change rptflag to save quotes for all items
    	-- from getting clobbered by next step
    	update SalesReporting_LOAD
    	set RptFlag = 'xD' where RptFlag = 'BD'
    
    	-- and now for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'D', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'D', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- move BD records (bookings  for all items summarized at division level)
    	-- to MD records (bookings for LIMITORQUE items summarized ad division level)
    	update SalesReporting_LOAD
    	set RptFlag = '2D' where RptFlag = 'BD'
    	
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '---'
    		print '---  COMPANY WIDE TOTALS'
    		print '---'
    	end
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'C', @date_range_int_param
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'C', @date_range_int_param
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- temporarily make company wide bookings summary records
    	-- use a different rptflag so it doesn't get mixed up with
    	-- limitorque numbers inserted in the next step
    	update SalesReporting_LOAD
    	set RptFlag = 'xC' where RptFlag = 'BC'
    	
    	-- and now for limitorque
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting_TEST 'B', 'C', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    	else
    		insert into SalesReporting_LOAD
    		exec usp_GetBQ4SalesReporting 'B', 'C', @date_range_int_param, null, null, null, null, null, @limitorque_prod_group_id_list
    
    	set @row_count = @@ROWCOUNT
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- move BC records (bookings for all items summarized at company wide level)
    	-- to MC records (bookings for LIMITORQUE items summarized at company wide level)
    	update SalesReporting_LOAD
    	set RptFlag = '2C' where RptFlag = 'BC'
    
    	-- now get the next date range and to it all again until we've processed
    	-- all the valid date ranges for bookings dashboard reporting
    	fetch next from date_ranges into @date_range_int_param, @date_range_desc
    END
    
    CLOSE date_ranges
    deallocate date_ranges
    
    --
    -- Now all those records in SalesReporting_LOAD table where we switched
    -- the RptFlag to 'x%' need to be set back to 'B%'
    --
    update SalesReporting_LOAD
    set RptFlag = 'B'+substring(RptFlag,2,1) where left(RptFlag,1)='x'
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '--'
    	print '-- NOW INVOICING DATA'
    	print '--'
    	print ''
    	print '-- Run the sub-proc to get all invoicing detail'
    end
    
    declare @RunID varchar(36), @sql_cmd varchar(6000), @start_date_range varchar(100), @end_date_range varchar(100), @sort_order int
    
    set @RunID = convert(varchar(23), current_timestamp, 121) + ' - ' + left(cast(NewID() as varchar(36)),8)
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	exec usp_Rpt_InvoicedOrders_DETAIL_TEST 0, 5, 2, '0', null, @RunID
    else
    	exec usp_Rpt_InvoicedOrders_DETAIL 0, 5, 2, '0', null, @RunID
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    declare date_ranges cursor for 
    	select int_param_value, range_desc, invoicing_sort_order, start_date_sql, end_date_sql
    		from date_ranges 
    			where valid_invoicing=1 
    				and delete_flag='N'
    				and range_desc <> 'All'
    
    open date_ranges
    fetch next from date_ranges into @date_range_int_param, @date_range_desc, @sort_order, @start_date_range, @end_date_range
    
    WHILE (@@FETCH_STATUS=0)
    BEGIN
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- Salesrep level detail '
    		print '--'
    		print '-- date_range: ' + cast(@date_range_int_param as nvarchar(40)) + ' ... ' + @date_range_desc
    	end
    
    	set @sql_cmd = 'select ''IS'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		salesrep_id,
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, branch_id, salesrep_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- limitorque version
    	set @sql_cmd = 'select ''3S'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		salesrep_id,
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	and product_group_id in (' + @limitorque_prod_group_id_list + ') 
    	group by division_id, branch_id, salesrep_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- branch level detail '
    		print '--'
    	end
    
    	set @sql_cmd = 'select ''IB'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, branch_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- limitorq
    	set @sql_cmd = 'select ''3B'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	and product_group_id in (' + @limitorque_prod_group_id_list + ') 
    	group by division_id, branch_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- DIVISION LEVEL'
    		print '--'
    	end
    
    	set @sql_cmd = 'select ''ID'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- limitorq
    	set @sql_cmd = 'select ''3D'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	and product_group_id in (' + @limitorque_prod_group_id_list + ') 
    	group by division_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- COMPANY TOTAL'
    		print '--'
    	end
    
    	set @sql_cmd = 'select ''IC'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		0,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	-- limitorq
    	set @sql_cmd = 'select ''3C'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		0,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	and product_group_id in (' + @limitorque_prod_group_id_list + ') 
    	group by order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	fetch next from date_ranges into @date_range_int_param, @date_range_desc, @sort_order, @start_date_range, @end_date_range
    END
    
    close date_ranges
    deallocate date_ranges
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print ''
    	print '-- Invoicing data cube table load'
    	print ''
    end
    
    --
    -- QUESTION: HOW DO WE SPLIT THIS SO WE DON'T MESS WITH PRODUCTION CUBE DATA
    --           FROM THE TEST PROC?
    
    -- ANSWER: SKIP THIS SECTION IF IN %_TEST
    
    IF OBJECT_NAME(@@PROCID) not like '%_TEST' 
    BEGIN
    	delete from cube_invoicing_data
    
    	insert into cube_invoicing_data	(invoice_no, invoice_date, order_no, division_id, division_name, branch_name, branch_id, salesrep_id, salesrep_name, order_date, po_no, ship2_name, taker, SplitPct, order_type, dealer_commission, SalesValue, SalesCost, PctProfit, product_group_id, supplier_id)
    	select invoice_no, invoice_date, order_no, division_id, division_name, branch_name, branch_id, salesrep_id, salesrep_name, order_date, po_no, ship2_name, taker, SplitPct, order_type, dealer_commission, SalesValue, SalesCost, PctProfit, product_group_id, supplier_id from tbl_Rpt_InvoicedOrdersDetail where RunID = @RunID and year(invoice_date) > 2005
    
    	update cube_invoicing_data
    		set customer_id = i.customer_id,
    			date_paid = i.date_paid
    	from cube_invoicing_data
    		join prophet21.dbo.invoice_hdr i (nolock)
    			on cube_invoicing_data.invoice_no = i.invoice_no
    
    	update cube_invoicing_data
    		set invoice_date = dbo.StartOfDay(invoice_date),
    			date_paid = dbo.StartOfDay(date_paid)
    
    	update cube_invoicing_data
    		set product_group_id = 0
    	where product_group_id is null
    		or product_group_id = 'N/A'
    
    	update cube_invoicing_data
    		set SalesCost = SalesValue - dealer_commission
    	where order_type = 'Rep'
    END
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print ''
    	print '-- Clean up Invoice stuff'
    	print ''
    end
    
    delete from tbl_Rpt_InvoicedOrdersDetail where RunID = @RunID
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '--'
    	print '-- NOW INVOICING/BOOKING IN SAME MONTH DATA'
    	print '--'
    	print ''
    	print '-- Run the sub-proc to get all invoicing detail again'
    	print '-- but limit it to only invoices for orders in same month'
    end
    
    set @RunID = convert(varchar(23), current_timestamp, 121) + ' - ' + left(cast(NewID() as varchar(36)),8)
    
    -- the last parameter added (the 1) tells the proc to only grab invoices
    -- with order dates in the same month (booked and invoiced in same month)
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	exec usp_Rpt_InvoicedOrders_DETAIL_TEST 0, 5, 2, '0', null, @RunID, 1
    else
    	exec usp_Rpt_InvoicedOrders_DETAIL 0, 5, 2, '0', null, @RunID, 1
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    declare date_ranges cursor for 
    	select int_param_value, range_desc, invoicing_sort_order, start_date_sql, end_date_sql
    		from date_ranges 
    			where valid_invoicing=1 
    				and delete_flag='N'
    				and range_desc <> 'All'
    
    open date_ranges
    fetch next from date_ranges into @date_range_int_param, @date_range_desc, @sort_order, @start_date_range, @end_date_range
    
    WHILE (@@FETCH_STATUS=0)
    BEGIN
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- Salesrep level detail '
    		print '--'
    		print '-- date_range: ' + cast(@date_range_int_param as nvarchar(40)) + ' ... ' + @date_range_desc
    	end
    	set @sql_cmd = 'select ''KS'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		salesrep_id,
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, branch_id, salesrep_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- branch level detail '
    		print '--'
    	end
    	set @sql_cmd = 'select ''KB'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		branch_id,
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, branch_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- DIVISION LEVEL'
    		print '--'
    	end
    
    	set @sql_cmd = 'select ''KD'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		division_id,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by division_id, order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- COMPANY TOTAL'
    		print '--'
    	end
    
    	set @sql_cmd = 'select ''KC'',
    		'+cast(@sort_order as varchar(8)) + ' ,
    		'''+@date_range_desc+''',
    		0,
    		'''',
    		'''',
    		left(order_type,1),
    		count(distinct invoice_no),
    		sum(SalesValue),
    		sum(case when left(order_type,1)=''R'' then SalesValue-dealer_commission else SalesCost end),
    		sum(case when left(order_type,1)=''R'' then dealer_commission else SalesValue-SalesCost end),
    		case	when sum(SalesValue) between -0.01 and 0.01 then 0 
    				else case when order_type = ''Rep'' then sum(dealer_commission) / sum(SalesValue)
    						else (sum(SalesValue)-sum(SalesCost)) / sum(SalesValue) 
    					end
    		end
    	from tbl_Rpt_InvoicedOrdersDetail (nolock)
    	where RunID = '''+@RunID+'''
    	and invoice_date between ' + @start_date_range + ' and ' + @end_date_range + '
    	group by order_type'
    
    	IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		print '--'
    		print '-- SQL CMD: '
    		print '--'
    		print ''
    		print @sql_cmd
    		print ''
    		print '-----------------------------'
    	end
    
    	insert into SalesReporting_LOAD exec(@sql_cmd)
    
    	if (@@ERROR<>0) set @error_flag = @@ERROR
    
    	fetch next from date_ranges into @date_range_int_param, @date_range_desc, @sort_order, @start_date_range, @end_date_range
    END
    
    close date_ranges
    deallocate date_ranges
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print ''
    	print '-- Clean up Invoice stuff again'
    	print ''
    end
    
    delete from tbl_Rpt_InvoicedOrdersDetail where RunID = @RunID
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '--'
    	print '-- NOW GENERATE BACKLOG NUMBERS'
    	print '--'
    end
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Salesrep level'
    end
    
    insert into SalesReporting_LOAD
    select 'OS',
    		1,
    		'All',
    		cdbh.division_id,
    		cdbh.branch_id,
    		sr.parent_salesrep_id,
           case when h.source_code_no in (1877,2739) then 'R' else 'N' end,
           count(distinct h.order_no) as order_count,
           sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesValue, 
           sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesCost,
    	   sum((COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - (l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))) as GrossProfit,
    	   case	when sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled) ) = 0 Then 0.00
    	        else (sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)))  / sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))
    	   end as PctProfit
    	from Prophet21.dbo.oe_line l (nolock)
    	join Prophet21.dbo.oe_hdr h (nolock)
    		on l.oe_hdr_uid = h.oe_hdr_uid
    	join Prophet21.dbo.oe_hdr_salesrep s (nolock)
    		on h.order_no = s.order_number
    	join salesrep_rollup sr (nolock)
    		on s.salesrep_id = sr.salesrep_id
    	join salesrep_branches sb (nolock)
    		on sr.parent_salesrep_id = sb.salesrep_id
    	join company_division_branch_heirarchy cdbh (nolock)
    		on sb.branch_id = cdbh.sub_branch_id
    	where l.delete_flag = 'N'
    	and l.parent_oe_line_uid = 0
    	and h.projected_order = 'N'
    	and (l.disposition IS  NULL OR l.disposition<>'C')
    	and h.cancel_flag='N'
    	and l.cancel_flag='N'
    	and h.delete_flag = 'N'
    	and h.rma_flag = 'N'
    	and coalesce(s.delete_flag, 'N') = 'N'
    	and coalesce(s.commission_split,100) <> 0
    	-- Now the filters that make it open only
    	and h.completed = 'N'
    	and l.complete = 'N'
    	and (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  <> 0
    GROUP BY cdbh.division_id, cdbh.branch_id, sr.parent_salesrep_id, case when h.source_code_no in (1877,2739) then 'R' else 'N' end
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Branch level'
    end
    
    insert into SalesReporting_LOAD
    select 'OB',
    		1,
    		'All',
    		cdbh.division_id,
    		cdbh.branch_id,
    		'',
           case when h.source_code_no in (1877,2739) then 'R' else 'N' end,
           count(distinct h.order_no) as order_count,
           sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesValue, 
           sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesCost,
    	   sum((COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - (l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))) as GrossProfit,
    	   case	when sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled) ) = 0 Then 0.00
    	        else (sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)))  / sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))
    	   end as PctProfit
    	from Prophet21.dbo.oe_line l (nolock)
    	join Prophet21.dbo.oe_hdr h (nolock)
    		on l.oe_hdr_uid = h.oe_hdr_uid
    	join Prophet21.dbo.oe_hdr_salesrep s (nolock)
    		on h.order_no = s.order_number
    	join salesrep_rollup sr (nolock)
    		on s.salesrep_id = sr.salesrep_id
    	join salesrep_branches sb (nolock)
    		on sr.parent_salesrep_id = sb.salesrep_id
    	join company_division_branch_heirarchy cdbh (nolock)
    		on sb.branch_id = cdbh.sub_branch_id
    	where l.delete_flag = 'N'
    	and l.parent_oe_line_uid = 0
    	and h.projected_order = 'N'
    	and (l.disposition IS  NULL OR l.disposition<>'C')
    	and h.cancel_flag='N'
    	and l.cancel_flag='N'
    	and h.delete_flag = 'N'
    	and h.rma_flag = 'N'
    	and coalesce(s.delete_flag, 'N') = 'N'
    	and coalesce(s.commission_split,100) <> 0
    	-- Now the filters that make it open only
    	and h.completed = 'N'
    	and l.complete = 'N'
    	and (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  <> 0
    GROUP BY cdbh.division_id, cdbh.branch_id, case when h.source_code_no in (1877,2739) then 'R' else 'N' end
    
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Division level'
    end
    
    insert into SalesReporting_LOAD
    select 'OD',
    		1,
    		'All',
    		cdbh.division_id,
    		'',
    		'',
           case when h.source_code_no in (1877,2739) then 'R' else 'N' end,
           count(distinct h.order_no) as order_count,
           sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesValue, 
           sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesCost,
    	   sum((COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - (l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))) as GrossProfit,
    	   case	when sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled) ) = 0 Then 0.00
    	        else (sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)))  / sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))
    	   end as PctProfit
    	from Prophet21.dbo.oe_line l (nolock)
    	join Prophet21.dbo.oe_hdr h (nolock)
    		on l.oe_hdr_uid = h.oe_hdr_uid
    	join Prophet21.dbo.oe_hdr_salesrep s (nolock)
    		on h.order_no = s.order_number
    	join salesrep_rollup sr (nolock)
    		on s.salesrep_id = sr.salesrep_id
    	join salesrep_branches sb (nolock)
    		on sr.parent_salesrep_id = sb.salesrep_id
    	join company_division_branch_heirarchy cdbh (nolock)
    		on sb.branch_id = cdbh.sub_branch_id
    	where l.delete_flag = 'N'
    	and l.parent_oe_line_uid = 0
    	and h.projected_order = 'N'
    	and (l.disposition IS  NULL OR l.disposition<>'C')
    	and h.cancel_flag='N'
    	and l.cancel_flag='N'
    	and h.delete_flag = 'N'
    	and h.rma_flag = 'N'
    	and coalesce(s.delete_flag, 'N') = 'N'
    	and coalesce(s.commission_split,100) <> 0
    	-- Now the filters that make it open only
    	and h.completed = 'N'
    	and l.complete = 'N'
    	and (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  <> 0
    GROUP BY cdbh.division_id, case when h.source_code_no in (1877,2739) then 'R' else 'N' end
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Company level'
    end
    
    insert into SalesReporting_LOAD
    select 'OC',
    		1,
    		'All',
    		0,
    		'',
    		'',
           case when h.source_code_no in (1877,2739) then 'R' else 'N' end,
           count(distinct h.order_no) as order_count,
           sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesValue, 
           sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) as SalesCost,
    	   sum((COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - (l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))) as GrossProfit,
    	   case	when sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled) ) = 0 Then 0.00
    	        else (sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)) - sum(l.commission_cost * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100)))  / sum(COALESCE((l.unit_price / NULLIF(l.pricing_unit_size, 0)), 0) * (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  * (s.commission_split/100))
    	   end as PctProfit
    	from Prophet21.dbo.oe_line l (nolock)
    	join Prophet21.dbo.oe_hdr h (nolock)
    		on l.oe_hdr_uid = h.oe_hdr_uid
    	join Prophet21.dbo.oe_hdr_salesrep s (nolock)
    		on h.order_no = s.order_number
    	join salesrep_rollup sr (nolock)
    		on s.salesrep_id = sr.salesrep_id
    	join salesrep_branches sb (nolock)
    		on sr.parent_salesrep_id = sb.salesrep_id
    	join company_division_branch_heirarchy cdbh (nolock)
    		on sb.branch_id = cdbh.sub_branch_id
    	where l.delete_flag = 'N'
    	and l.parent_oe_line_uid = 0
    	and h.projected_order = 'N'
    	and (l.disposition IS  NULL OR l.disposition<>'C')
    	and h.cancel_flag='N'
    	and l.cancel_flag='N'
    	and h.delete_flag = 'N'
    	and h.rma_flag = 'N'
    	and coalesce(s.delete_flag, 'N') = 'N'
    	and coalesce(s.commission_split,100) <> 0
    	-- Now the filters that make it open only
    	and h.completed = 'N'
    	and l.complete = 'N'
    	and (l.qty_ordered - l.qty_invoiced - l.qty_canceled)  <> 0
    GROUP BY case when h.source_code_no in (1877,2739) then 'R' else 'N' end
    
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print ''
    	print '--'
    	print '-- Gather up financial indicators that are company wide only'
    	print '-- for exec view'
    	print '--'
    	print ''
    end
    
    declare @MoneyValue money
    
    
    /*
    --
    -- This section removed 2007-11-21 by APH as per RFRAZEE/CSIMON request
    
    -- Current Cash Balance
    select @MoneyValue = SUM(foo.cumulative_balance) 
    FROM prophet21.dbo.p21_bal_view_derived_home_amts foo
        WHERE foo.company_no = '1'
             AND foo.period = month(getdate())
             AND foo.year_for_period = year(getdate())
             AND foo.account_no IN ( SELECT gl_account_no FROM prophet21.dbo.bank_accounts WHERE company_no = '1' AND active = 'Y' AND delete_flag = 'N')
    
    
    insert into SalesReporting
    select 'FC', 1, 'Current Cash Balance', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    -- This section removed 2007-11-21 by APH as per RFRAZEE/CSIMON request
    --
    */
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Current Receivables'
    end
    
    create table #TempAR
    (
    division_id int,
    division_name nvarchar(50),
    branch_id nvarchar(10),
    branch_name nvarchar(50),
    orig_invc_amt money,
    open_amt money,
    curr_due money,
    age1to30 money,
    age31to60 money,
    age61to90 money,
    age91plus money
    )
    
    insert into #TempAR exec usp_Rpt_OpenAR_AgingSummary
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 2, 'Current A/R Balance', 0, '', '', '', null, sum(open_amt), null, null, null from #TempAR
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    drop table #TempAR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Current Payables'
    end
    
    select @MoneyValue = sum(open_amount)
    from (select distinct invoice_no, open_amount 
    		from prophet21.dbo.p21_ap_view (nolock)
    			where ( year_fully_paid > year(getdate()) or 
    						( year_fully_paid = year(getdate()) and period_fully_paid >= month(getdate()) ) 
    					or
    						( year_fully_paid is NULL and period_fully_paid is NULL )
    				) and
     			(
    				voucher_reference_number = voucher_no
    				or 
    				(
    					voucher_reference_number <> voucher_no and voucher_type not in ('C','D')
    				)
    			) 
    			and invoice_date <= dbo.EndOfDay(null)
    			and open_amount <> 0
    	) foo
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 3, 'Current A/P Balance', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Current Inventory Value'
    end
    
    SELECT @MoneyValue = SUM(ROUND(inv_loc.moving_average_cost * (inv_loc.qty_on_hand - COALESCE(drv_special_inv_layer.sku_special_inv_layer_qty,0)), 2)) + SUM(ROUND(COALESCE(special_layer_cost_total, 0), 2)) 
    FROM prophet21.dbo.inv_loc inv_loc (nolock)
    	JOIN prophet21.dbo.location location (nolock)
    		ON location.location_id = inv_loc.location_id
    	LEFT JOIN prophet21.dbo.branch branch  (nolock)
    		ON branch.branch_id = location.default_branch_id
    			AND branch.company_id = location.company_id
    	LEFT JOIN ( 
    				SELECT inv_mast_uid, 
    						location_id, 
    						sum(sku_special_inv_layer_qty) sku_special_inv_layer_qty, 
    						sum(sku_special_inv_layer_qty * sku_special_inv_layer_cost) special_layer_cost_total
    				FROM prophet21.dbo.special_inv_layer special_inv_layer  (nolock)
    					JOIN prophet21.dbo.system_setting system_setting_a  (nolock)
    						ON system_setting_a.name = 'use_specific_cost_for_spd_items'
    				WHERE special_inv_layer.sku_special_inv_layer_qty <> 0 
    					AND special_inv_layer.row_status_flag <> 701 
    					AND system_setting_a.value = 'Y'
    				GROUP BY inv_mast_uid, location_id
    				) drv_special_inv_layer
    		ON inv_loc.inv_mast_uid = drv_special_inv_layer.inv_mast_uid 
      			AND	inv_loc.location_id = drv_special_inv_layer.location_id 				
    										  
    WHERE inv_loc.company_id = '1'
    	AND inv_loc.qty_on_hand <> 0
    	AND	inv_loc.requisition = 'N'
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 4, 'Current Inventory Value', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Cash Receipts Yesterday'
    end
    
    SELECT @MoneyValue = cast(sum(ar_receipts_detail.payment_amount) as money)
    FROM prophet21.dbo.ar_receipts_detail ar_receipts_detail  (nolock)
    	JOIN prophet21.dbo.ar_receipts ar_receipts (nolock)
    		ON ar_receipts.receipt_number = ar_receipts_detail.receipt_number 
    	JOIN prophet21.dbo.invoice_hdr invoice_hdr (nolock)
    		ON invoice_hdr.invoice_no = ar_receipts_detail.invoice_no
    WHERE invoice_hdr.company_no = '1'
    	AND dbo.StartOfDay(ar_receipts.date_received) = dbo.GetPrevBusinessDay(getdate())
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    
    insert into SalesReporting_LOAD
    select 'FC', 5, 'Cash Receipts Yesterday', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Cash Receipts MTD'
    end
    
    SELECT @MoneyValue = cast(sum(ar_receipts_detail.payment_amount) as money)
    FROM prophet21.dbo.ar_receipts_detail ar_receipts_detail (nolock)
    	JOIN prophet21.dbo.ar_receipts ar_receipts (nolock)
    		ON ar_receipts.receipt_number = ar_receipts_detail.receipt_number 
    	JOIN prophet21.dbo.invoice_hdr invoice_hdr (nolock)
    		ON invoice_hdr.invoice_no = ar_receipts_detail.invoice_no
    WHERE invoice_hdr.company_no = '1'
    	and ar_receipts.date_received between dbo.FirstDayOfCurrMonth() and dbo.EndOfDay(null)
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 6, 'Cash Receipts MTD', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- Payables MTD'
    end
    
    select @MoneyValue = sum(invoice_amount)
    from (select distinct invoice_no, invoice_amount 
    		from prophet21.dbo.p21_ap_view (nolock)
    			where ( year_fully_paid > year(getdate()) or 
    						( year_fully_paid = year(getdate()) and period_fully_paid >= month(getdate()) ) 
    					or
    						( year_fully_paid is NULL and period_fully_paid is NULL )
    				) and
     			(
    				voucher_reference_number = voucher_no
    				or 
    				(
    					voucher_reference_number <> voucher_no and voucher_type not in ('C','D')
    				)
    			) 
    			and invoice_date between dbo.FirstDayOfCurrMonth() and dbo.EndOfDay(null)
    	) foo
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 7, 'Payables - MTD', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- AP Paid MTD'
    end
    
    SELECT @MoneyValue = sum(pd.amount_paid)
    FROM prophet21.dbo.payment_detail pd (nolock)
    	JOIN prophet21.dbo.payments p (nolock)
    		ON p.company_no = pd.company_no
    			AND p.bank_no = pd.bank_no
     			AND p.check_no = pd.check_no
    	JOIN prophet21.dbo.apinv_hdr apinv_hdr (nolock)
    		ON apinv_hdr.invoice_no = pd.invoice_no
     			AND apinv_hdr.check_no = pd.check_no
    	JOIN prophet21.dbo.address address (nolock)
    		ON address.id = p.vendor_id
    WHERE p.company_no = '1'
    	AND p.check_date BETWEEN dbo.FirstDayOfCurrMonth() AND dbo.EndOfDay(null)
    	AND p.void = 'N'
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    insert into SalesReporting_LOAD
    select 'FC', 8, 'Disbursements - MTD', 0, '', '', '', null, @MoneyValue, null, null, null 
    
    if (@@ERROR<>0) set @error_flag = @@ERROR
    
    if (@error_flag=0)
      BEGIN
    	if OBJECT_NAME(@@PROCID) like '%_TEST'
    	begin
    		delete from SalesReporting_TEST
    		insert into SalesReporting_TEST select * from SalesReporting_LOAD
    	end 
    	else begin
    		print OBJECT_NAME(@@PROCID)
    		delete from SalesReporting
    		insert into SalesReporting select * from SalesReporting_LOAD
    	end
      END
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    begin
    	print '-- now, if today is a new month, new quarter, or new year, create a snapshot record'
    	print '-- this stored proc should be scheduled to run early AM (like 4:00 or so)'
    	print '-- also, don''t do this if we''re in the test version of the proc, because these don''t'
    	print '-- have test versions (TODO: create test versions of all these)'
    end
    
    IF OBJECT_NAME(@@PROCID) like '%_TEST'
    	return
    ELSE BEGIN
    	if (datepart(month, dateadd(day, -1, getdate())) <> datepart(month, getdate()))
    	begin
    		-- yesterday was a different month than today, so create a snapshot of last month
    		exec usp_FillSalesReportingSnapshots 8 -- 8=last month
    	end
    
    	if (datepart(quarter, dateadd(day, -1, getdate())) <> datepart(quarter, getdate()))
    	begin
    		-- yesterday was a different quarter than today, so create a snapshot of last quarter
    		exec usp_FillSalesReportingSnapshots 9 -- 9=last quarter
    	end
    
    	if (datepart(year, dateadd(day, -1, getdate())) <> datepart(year, getdate()))
    	begin
    		-- yesterday was a different year than today, so create a snapshot of last year
    		exec usp_FillSalesReportingSnapshots 10 -- 10=last year
    	end
    END -- if running test version
    
    END -- PROC END
    
    first table has fallowing column and second table has fallowing columns
     division_id,branch_id salesrep_id,order_type,order_no,order_date,customer_id,supplier_id,product_group_id,line_type,salesvalue,sales_cost.
    • Changed type mm6991 Friday, January 20, 2012 12:43 PM sqlserver
    Wednesday, January 18, 2012 6:40 PM

Answers

  • hi thankz for ur reply i got the solution for this issue i was trying to joing the two tables for an existing store proc.
    • Marked as answer by mm6991 Sunday, January 22, 2012 3:26 AM
    Sunday, January 22, 2012 3:26 AM

All replies

  • I am not sure that understood your problem, but perhaps instead of putting so much text explain your problem with shorten unsustainable examples
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 19, 2012 8:53 AM
  • Also I am not so sure if this is a topic for the Database Mirroring forum...
    Friday, January 20, 2012 7:29 AM
  • What exactly you are trying to do .... 
    http://uk.linkedin.com/in/ramjaddu
    Saturday, January 21, 2012 9:55 AM
  • hi thankz for ur reply i got the solution for this issue i was trying to joing the two tables for an existing store proc.
    • Marked as answer by mm6991 Sunday, January 22, 2012 3:26 AM
    Sunday, January 22, 2012 3:26 AM