Enhanced Web Apps LABC Lookup

I recently upgraded our Web Apps LABC lookup and thought I would share the SQL code. Feel free to use and improve.

The lookup was requested to help ensure that we have appropriate rates set up for the people who are using them to cut down on future allocator errors. If employees need to charge to a specific LABC, they need to reach out to our accounting staff to add a rate BEFORE they can add to their TC. I should mention we have our LABC in PJCODE and use the value on PJCODE.data4 = ‘123456’ to signify inactive. Also, we are using rate type = ‘L’ (Labor) for most of our lookups, so I added that as a filter…unless certain project/task combos are met, then we use ‘M’ or ‘T’. The gist of the lookup is below.

  1. If a union employee, return the distinct union LABCs
  2. If not a union employee, loop through the Allocation Multi-level rate table starting at level 1 to see if any rates exist for that particular combination of Project, Employee, Customer, etc. If rates exist, return only those LABC otherwise, go to the next level
  3. If no rates are found, return the default LABC of the employee as the only option. However, if rates are found, but none established for that employee, don’t show the default LABC as we need to set up a new rate.
  4. We also added the ability to validate each timecard upon final submission and I am using this same procedure to make sure that what was selected onto the TC is still valid. Passing parameter @Validation = 1 for that.

Now, unfortunately, each one of our rate tables was set up differently as we have 20+ years of rates setup. So, I had to first get the the setup of the rate table to use in any lookups. There was some fun pivoting / unpivoting to get the setup into a temp table to easily join to other tables. And yes, I know cursors are frowned upon in SQL as we should be handling as a set, but due to the complex nature and differing lookups not only per rate table but also per level, I felt it was best for this solution.

alter proc zsp_LABCLOOKUP
  @page  int
 ,@size  int
 ,@sort   nvarchar(200)
 ,@Parm0 varchar(32) -- LABC to lookup
 ,@Parm1 varchar(52) -- Description
 ,@Parm2 varchar(16) -- project (passed)
 ,@Parm3 varchar(10) -- employee (passed)
 ,@Parm4 varchar(10) -- pjt_entity (passed)
 ,@Union varchar(10)='' 
 ,@Validate int = 0 

as

SET NOCOUNT ON

--msde 9/17/19 added @RateLevelsFound to control whether the default LABC is inserted into the #temp table based upon ANY rates being setup for that project / customer / task / employee
--msde 6/3/19 - updated to add condition for when LABC passed = '%' to return results and when LABC <> '%' for proper validation
--msde 5/20/19 - added validation to use in zwebtc_validate
--MSDE 3/19/19 built new LABC Lookup for web apps to replace LaborClassListSearch

--exec zsp_LABCLOOKUP @page=N'1',@size=N'20',@sort=N'Labor Class',@Parm0=N'%',@Parm1=N'%',@Parm2=N'154100000003', @Parm3=N'28071', @Parm4 = '001', @Union = '', @Validate = 1

DECLARE @LABC varchar(32) = @Parm0,
 @LABCALL varchar(32) = '%'
,@Project varchar(16) = @Parm2
,@EMPLOYEE varchar(10) = @Parm3
,@WBS varchar(30) = @Parm4
,@CUSTOMER varchar(15) 
,@RateTableID varchar(10)
,@AllocMethod varchar(10)
,@WBSContractType varchar(10)
,@ResultsFound bit = 0 

  DECLARE
    @STMT nvarchar(max),
    @lbound int,
    @ubound int

 --get customer and rate table info for project 
select  @CUSTOMER = Customer, 
		@RateTableID = rate_table_id,
		@AllocMethod = alloc_method_cd 
from PJPROJ(nolock) where project = @Project 

--get WBS contract type - if project = MUTM and pjt_entity contract = 'FP%' then use level M
select @WBSContractType = contract_type 
from PJPENT (nolock) where project = @Project and pjt_entity  = @WBS


IF @page < 1 SET @page = 1
IF @size < 1 SET @size = 1
SET @lbound = (@page-1) * @size
SET @ubound = @page * @size + 1


---table to insert matches / select at end
if OBJECT_ID('tempdb..#tempTbl') is not null drop table #tempTbl ;
Create table #tempTbl ([Labor Class] varchar(20) null, [Description] varchar(60) null, [level] int null) 

------------------------------------------------------------------------------------------------------------
	-- UNION
	------------------------------------------------------------------------------------------------------------
	--Check to see if it's union employee and union code is passed in, if true, then get union LABC codes only
	--otherwise, it would follow regular logic, e.g., union employee working on non-union tasks
	DECLARE @empType VARCHAR(4)

	If @Union <>'' BEGIN
		SELECT TOP 1 @empType= ep_id01
		FROM pjemppjt 
		WHERE 1=1
			AND project = 'na' 
			AND employee=@Parm3
			AND effect_date <= Convert(datetime, CONVERT(char(12), GETDATE(), 1)) 
		ORDER BY effect_date DESC

		If @empType<>''  
		BEGIN
			insert into #tempTbl
			([Labor Class], Description)
			SELECT DISTINCT 'Labor Class'= u.labor_class_cd ,Description=c.Code_Value_Desc 
			FROM pjWageUN u (NOLOCK)
				inner join pjcode c  (NOLOCK)
				ON  u.labor_class_cd = c.code_value 
					and c.code_type = 'LABC'
			where 1=1
				AND u.Union_CD = @Union 
				AND u.effect_Date<Convert(datetime, CONVERT(char(12), GETDATE(), 1)) 
				AND (u.labor_class_cd Like @Parm0 OR c.code_value_desc like @Parm1)
				AND c.code_value_desc like @Parm1
				AND c.data4<>123456
			if exists(select * from #tempTbl)
			GOTO FINISH
		END
	END


---Employee Default LABC and Code --> to use for Project/Client Employee lookup and if nothing else found 
Declare @EmpLABC varchar(20), @EmpLABCDesc varchar(100)

;with cte as
(SELECT e.labor_class_cd, c.code_value_desc, row_number() over (order by effect_date desc) as rn
   from pjemppjt e (NOLOCK) 
		INNER JOIN pjcode c (NOLOCK) 
			ON e.labor_class_cd = c.code_value AND c.code_type = 'LABC'
	where 1=1
		AND e.employee = @employee
		--AND c.Code_value Like @LABC
		AND effect_date <= Convert(datetime, CONVERT(char(12), GETDATE(), 1))  
		AND c.data4 <> 123456

) 
select @EmpLABC = cte.labor_class_cd, @EmpLABCDesc = cte.code_value_desc
from cte 
where rn = 1

--get the rate table setup (unpivoted)
IF OBJECT_ID('tempdb..#RateSetup','u') is not null DROP table #RateSetup
create table #RateSetup
(Rate_table_id char(4) not null,
Rate_type_cd char(1) not null,
Keyvalue char(4) not null,
KeyDesc varchar(40) not null,
rate_level char(1) not null,
rate_value char(1) not null)

--(pivot RS)
IF OBJECT_ID('tempdb..#RateLookup','u') is not null DROP table #RateLookup
create table #RateLookup
(Rate_table_id char(4) not null,
Rate_type_cd char(1) not null,
rate_level char(1) not null,
col1 varchar(100)  null, 
col2 varchar(100)  null, 
col3 varchar(100) null)

/*
	('PROJ','Project'),
	('EMPL','Employee'),
	('LABC', 'LABC'),
	('PRJ1', 'Customer'),
	('TASK', 'Task'), 
	('CONT','Contract')
*/

INSERT INTO #RateSetup
select	u.rate_table_id,
		u.rate_type_cd,
		u.keyvalue, 
		c.control_desc,
		SUBSTRING(u.code,2,1) as level,
		SUBSTRING(u.code,12,1) as keyvalue
from pjrtab 
UNPIVOT(keyvalue for code in (	l1_rate_key1_cd, l1_rate_key2_cd,l1_rate_key3_cd,
								l2_rate_key1_cd, l2_rate_key2_cd,l2_rate_key3_cd,
								l3_rate_key1_cd, l3_rate_key2_cd,l3_rate_key3_cd,
								l4_rate_key1_cd, l4_rate_key2_cd,l4_rate_key3_cd,
								l5_rate_key1_cd, l5_rate_key2_cd,l5_rate_key3_cd,
								l6_rate_key1_cd, l6_rate_key2_cd,l6_rate_key3_cd,
								l7_rate_key1_cd, l7_rate_key2_cd,l7_rate_key3_cd,
								l8_rate_key1_cd, l8_rate_key2_cd,l8_rate_key3_cd,
								l9_rate_key1_cd, l9_rate_key2_cd,l9_rate_key3_cd)) u
LEFT JOIN pjcontrl c on c.control_code = u.keyvalue and control_type = 'RK'
LEFT JOIN PJCODE cd on cd.code_value = u.rate_table_id and cd.code_type = 'RTAB' 
where u.keyvalue <> '' and cd.data4 <> '123456'
	and cd.code_value = @RateTableID


--pivot into 1 row per level	
insert into #RateLookup
select rate_table_id, rate_type_cd, rate_level, MAX(COALESCE([1],'')) as col1, MAX(COALESCE([2],'')) as col2, MAX(COALESCE([3],'')) as col3
from  #RateSetup  
PIVOT( MIN(keyvalue) for rate_value in ([1],[2],[3] ))p
where Rate_type_cd = case when RTRIM(@AllocMethod) = 'MUTM' 
							then case when @WBSContractType like 'FP%' 
										then 'M' 
										else 'T'
								 end 
							else 'L' 
					  end
group by rate_table_id, rate_type_cd, rate_level

--loop through the Multi-Level Rate table starting at 1, go up to 9 to find a match; as soon as we find a match, break
DECLARE @col1 varchar(100), @col2 varchar(100), @col3 varchar(100), @levelcsr int

declare c cursor for 
select rate_level, col1, col2, col3
from #RateLookup 
where Rate_table_id = @RateTableID
order by rate_level

open c;
fetch next from c into @levelcsr, @col1, @col2, @col3;

while @@FETCH_STATUS = 0 

BEGIN 

--use cte for window function to ony return most recent LABC when more than 1 effect date
with cte as (
select c.code_value, c.code_value_desc, @levelcsr as level, ROW_NUMBER() over (partition by r.rate_key_value1, r.rate_key_value2, r.rate_key_value3 order by effect_date desc) as rn
from PJRATE r
	join #RateLookup rl
		on rl.Rate_table_id = r.rate_table_id and rl.Rate_type_cd = r.rate_type_cd and r.rate_level = rl.rate_level
	join PJCODE c on c.code_type = 'LABC' and c.code_value = case when @col1 = 'LABC' then r.rate_key_value1
															 	  when @col2 = 'LABC' then r.rate_key_value2
															 	  when @col3 = 'LABC' then r.rate_key_value3
															 	  else 
																	---if level 1 is client or project and level 2 is employee, use default Employee LABC 
																		case when (@col1 = 'PRJ1' or @Col1 = 'PROJ') AND (@Col2 = 'EMPL')
																			 then @EmpLABC 
																			 else '' 
																		end 
															 end 
where r.rate_table_id = @RateTableID
	and r.rate_level = @levelcsr
	and r.effect_date <= Convert(datetime, CONVERT(char(12), GETDATE(), 1))  
	and r.rate_key_value1 like case when @col1 = 'PROJ' then @Project 
									when @col1 = 'EMPL' then @EMPLOYEE 
									when @col1 = 'PRJ1' then @CUSTOMER
									when @col1 = 'TASK' then @WBS
									when @col1 = 'LABC' then @LABCALL
									else ' ' 
								end 
	and r.rate_key_value2 like case when @col2 = 'PROJ' then @Project 
									when @col2 = 'EMPL' then @EMPLOYEE 
									when @col2 = 'PJR1' then @CUSTOMER
									when @col1 = 'TASK' then @WBS
									when @col2 = 'LABC' then @LABCALL
									else ' ' 
								end 
	and r.rate_key_value3 like case when @col3 = 'PROJ' then @Project 
									when @col3 = 'EMPL' then @EMPLOYEE 
									when @col3 = 'PRJ1' then @CUSTOMER
									when @col1 = 'TASK' then @WBS
									when @col3 = 'LABC' then @LABCALL
									else ' ' 
								end 
	and c.data4 <> '123456'
	)

insert into #tempTbl
select code_value, code_value_desc, level
from cte
where rn = 1  --only pull most recent 

--did we find a match? 	if so, break
if exists(select * from #tempTbl) 
begin
select @ResultsFound = 1
break 

end

fetch next from c into @levelcsr, @col1, @col2, @col3;

end;

close c; 
deallocate c; 


--if didn't find a match looping through the Multi-Level Rate table, use employee default
if not exists (select * from #tempTbl) and @ResultsFound = 0
insert into #tempTbl
 SELECT @EmpLABC, @EmpLABCDesc, 10


 FINISH:
 --validating again on final TC submission

 IF @Validate = 1
BEGIN
 DECLARE @LaborClassCount int 
 SELECT @LaborClassCount =  COUNT(*) from #tempTbl where [Labor Class] = @LABC
 RETURN @LaborClassCount
END

 ELSE
 BEGIN
 
  --paging for web apps
SET @STMT = '
with pagingCTE as 
(
SELECT TOP(' + CONVERT(varchar(9), @ubound-1) + ') [Labor Class], Description, level, row_number() over (order by [Labor Class]) as row
from #tempTbl 
where ([Labor Class] like ''' + @LABC + ''' )
  and [Description] like ''%' + @Parm1 + '%''
)
select [Labor Class], [Description],[level] from pagingCTE
WHERE  row > ' + CONVERT(varchar(9), @lbound) + ' AND
	row <  ' + CONVERT(varchar(9), @ubound) + '
ORDER BY row'


EXEC(@STMT)
END
Feel free to share...

Leave a Reply

Close Menu