/**************************************************************************************************
PURPOSE : TO GET PAYROLL DATA INFORMATION
RETURNS : NILL
ARGUMENTS :
usp_SetPayRollData_XML @Resxml='<NewDataSet>
<Table1>
<Column1>HOVNV0004</Column1>
<Column2>ALtergame</Column2>
<Column3>100001</Column3>
</Table1>
</NewDataSet>',@Month=12,@year=2012,@locationId=3
CREATED BY : Venkat
----------------------------------------------------------------------------------------------------------------------
MODIFICATION LOG
----------------------------------------------------------------------------------------------------------------------
TRACK ID MODIFIED BY MODIFIED DATE REASON
----------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************************/
CREATE PROCEDURE usp_SetPayRollData_XML
(
@Resxml XML,
@LocationID BIGINT=NULL,
@User VARCHAR(30)=NULL,
@CreatedON DATETIME=NULL,
@CreatedIP VARCHAR(15)=NULL,
@Month Char(2)=NuLL,
@Year Char(4)=NuLL
)
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON
CREATE TABLE [dbo].[#Resxmloutput](
[LocationID] [bigint] NULL,
[EmpID] [nvarchar](50) NULL,
[Year] [smallint] NULL,
[Month] [tinyint] NULL,
[Empname] [varchar](100) NULL,
[Salary] [nvarchar](Max) NULL,
[CreatedBy] [varchar](30) NULL,
[CreatedOn] [datetime] NULL,
[CreatedIP] [varchar](15) NULL,
[ModifiedBy] [varchar](30) NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedIP] [varchar](15) NULL)
Insert into [#Resxmloutput] (
EmpID,
[Empname],
Salary)
SELECT Distinct
xmlVals.rowvals.query('Column1').value('.','VARCHAR(30)') as EmpID,
xmlVals.rowvals.query('Column2').value('.','VARCHAR(30)') as EmpName,
xmlVals.rowvals.query('Column3').value('.','NVARCHAR(MAX)') as Salary
FROM
@Resxml.nodes('/NewDataSet/Table1') as xmlVals(rowvals)
Update #Resxmloutput Set Month =@Month ,Year =@year ,LocationID =NULL
/* To Find out the Emprty Recs in the XML*/
Select 'No of Empty Records in the CSV File : ' + Cast(Count(*) AS Varchar)
From #Resxmloutput Where EmpID = '' OR Salary = '' OR EmpName = ''
Select 'No of Repeated Records in the CSV File : ' +
Cast(Count(Distinct r1.EmpID)AS Varchar)+';' + ' '
+ 'EmpID : ' From #Resxmloutput r1 Join #Resxmloutput r2
ON r1.EmpID =r2.EmpId and (r1.EmpName != r2.EmpName OR r1.Salary != r2.Salary)
Union ALL
Select Distinct(r1.EmpID)+ ' '+',' From #Resxmloutput r1 Join #Resxmloutput r2
ON r1.EmpID =r2.EmpId and (r1.EmpName != r2.EmpName OR r1.Salary != r2.Salary)
FOR XML PATH('')
/* To Delete the Duplicate Recs in the XML*/
Delete r1 From #Resxmloutput r1 Join #Resxmloutput r2
ON r1.EmpID =r2.EmpId and (r1.EmpName != r2.EmpName OR r1.Salary != r2.Salary)
--/* To find The UnMapped Employyes in Non-licm*/
--Select 'No of Employees Not Mapped in Non-LICM : '
--+Cast(Count(*) AS Varchar) + ' ' +';' + 'EmpID :' From #Resxmloutput tr
--Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
--And tr.EmpId Not in
--(Select EmpId From V_CM_M_EmpMaster
--Union
--Select User_id From V_empMaster_all)
--Union ALL
--Select Distinct(tr.Empid) + ',' From #Resxmloutput tr
--Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
--And tr.EmpId Not in
--(Select EmpId From V_CM_M_EmpMaster
--Union
--Select User_id From V_empMaster_all)
--For XML Path('')
--/* To find The UnMapped Employees in Licm*/
--Select 'No of Employees Not Mapped in LICM and Non-Licm : '
--+Cast(Count(*) AS Varchar) + ' ' +';' + 'EmpID :' From #Resxmloutput tr
-- Where tr.Empid Not IN
--(Select User_ID FrOm V_EmpMaster_all
--Union
--Select EmpId From v_CM_M_EmpMaster)
--and EmpID <> '' AND Salary <> '' AND EmpName <> ''
--Union ALL
--Select Distinct(tr.Empid) + ',' From #Resxmloutput tr
--Where tr.Empid Not IN
--(Select User_ID FrOm V_EmpMaster_all
--Union
--Select EmpId From v_CM_M_EmpMaster)
--and EmpID <> '' AND Salary <> '' AND EmpName <> ''
--For XML Path('')
/* To find The UnMapped Employyes in Non-licm*/
Select 'No of Employees Not Mapped in Non-LICM/LICM : '
+Cast(Count(*) AS Varchar) + ' ' +';' + 'EmpID :' From #Resxmloutput tr
Left Join V_empMaster_all Ve
ON Ve.User_ID = tr.EmpID
Left Join V_Cm_M_EmpMaster Vc
ON Vc.EmpID = tr.EmpID
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
AND Ve.USER_ID IS NULL AND Vc.EmpID IS NULL
Union ALL
Select Distinct(tr.Empid) + ',' From #Resxmloutput tr
Left Join V_empMaster_all Ve
ON Ve.User_ID = tr.EmpID
Left Join V_Cm_M_EmpMaster Vc
ON Vc.EmpID = tr.EmpID
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
AND Ve.USER_ID IS NULL AND Vc.EmpID IS NULL
For XML Path('')
/* To find The UnMapped Employyes Location*/
Select 'No of Employees Location Not Mapped in Non-LICM : '
+Cast(Count(*) AS Varchar) + ' ' +';' + 'EmpID :' From #Resxmloutput tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
AND Ve.SiteID Not IN
(Select SiteID FrOm V_CM_M_Site)
Union ALL
Select Distinct(tr.Empid) + ',' From #Resxmloutput tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
AND Ve.SiteID Not IN
(Select SiteID FrOm V_CM_M_Site)
For XML Path('')
/* To find The UnMapped Employres Location*/
Select 'No of Employees Location Not Mapped in LICM : '
+Cast(Count(*) AS Varchar) + ' ' +';' + 'EmpID :' From #Resxmloutput tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Where EmpID <> '' AND Salary <> '' AND EmpName <> ''
AND Ve.Location_ID Not IN
(Select Loc_ID FrOm V_locationMaster)
Union ALL
Select Distinct(tr.Empid) + ',' From #Resxmloutput tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Where EmpID <> '' AND Salary <> '' AND EmpName <> ''
AND Ve.Location_ID Not IN
(Select Loc_ID FrOm V_locationMaster)
For XML Path('')
CREATE TABLE [dbo].[#ResxmlLicl](
[LocationID] [bigint] NOT NULL,
[EmpID] [nvarchar](50) NOT NULL,
[Year] [smallint] NOT NULL,
[Month] [tinyint] NOT NULL,
[Empname] [varchar](100) NULL,
[Salary] [nvarchar](Max) NULL,
[CreatedBy] [varchar](30) NULL,
[CreatedOn] [datetime] NULL,
[CreatedIP] [varchar](15) NULL,
[ModifiedBy] [varchar](30) NULL,
[ModifiedOn] [datetime] NULL,
[ModifiedIP] [varchar](15) NULL,
CONSTRAINT [PK_#ResxmlLicl] PRIMARY KEY CLUSTERED
(
[EmpID] ASC,
[Year] ASC,
[Month] ASC,
[LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/* To get Location from the LICL*/
Insert Into #ResxmlLicl(
EmpID,
[Month],
[Year],
EmpName,
[Salary],
[LocationID],
[CreatedBy],
[CreatedIP],
[CreatedON])
Select Distinct
EmpID,
[Month],
[Year],
EmpName,
[Salary],
Ml.SeqID[LocationID],
@User,
@CreatedIP,
Getdate()[CreatedOn] From #Resxmloutput tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Join V_LocationMaster Vl
ON Vl.Loc_ID = Ve.Location_ID
Join M_location Ml
ON Ml.[System]= 'FF' And Ml.Country='India'
And Vl.Loc_City = Ml.LocationName
Where EmpID <> '' AND Salary <> '' AND EmpName <> ''
/*To Updte the T_paydata Table When XML And T_Payadata are same*/
Update tp1 Set tp1.Salary = tr.Salary ,
tp1.EmpName = tr.EmpName,
tp1.ModifiedON =Getdate(),
tp1.ModifiedBy = @user ,
tp1.ModifiedIP =@CreatedIP
From #ResxmlLicl tr
Join T_paydata tp1
ON tr.LocationID = tp1.LOCATIONID AND
tr.EmpID = tp1.DomainID
AND tr.[Year] = tp1.Payyear
AND tr.[Month] = tp1.PayMonth
/*To Get the Count How Many Recs Inserted Sucessfully*/
Select 'Total Records Inserted successfully For Licm Employees : '+ ' '
+Cast(Count(*) AS Varchar) From #ResxmlLicl tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Join V_LocationMaster Vl
ON Vl.Loc_ID = Ve.Location_ID
Join M_location Ml
ON Ml.[System]= 'FF' And Ml.Country='India'
And Vl.Loc_City = Ml.LocationName
Where EmpID <> '' AND Salary <> '' AND EmpName <> ''
/*After Update the data in T_paydata Table Delete it from Xml Data*/
Delete tr From T_paydata tp1
Join #ResxmlLicl tr
ON tr.LocationID = tp1.LOCATIONID AND
tr.EmpID = tp1.DomainID
AND tr.[Year] = tp1.Payyear
AND tr.[Month] = tp1.PayMonth
/* To Insert the Records in T_paydata Table*/
Insert Into T_paydata
(LocationID,
DomainID,
PayMonth,
PayYear,
Empname,
Salary,
CreatedBy,
CreatedIP,
CreatedOn)
(Select Distinct Ml.SeqID,
EmpID,
[Month],
[Year],
EmpName,
[Salary],
[CreatedBy],
CreatedIP,
Getdate() From #ResxmlLicl tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Join V_LocationMaster Vl
ON Vl.Loc_ID = Ve.Location_ID
Join M_location Ml
ON Ml.[System]= 'FF' And Ml.Country='India'
And Vl.Loc_City = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> '')
Delete tr From #ResxmlLicl tr
Join v_empMaster_all Ve ON
ve.[User_ID] = tr.EmpID
Join V_LocationMaster Vl
ON Vl.Loc_ID = Ve.Location_ID
Join M_location Ml
ON Ml.[System]= 'FF' And Ml.Country='India'
And Vl.Loc_City = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
/*To check if any data is available in the xml*/
If Exists(Select Top 1 EmpID From #Resxmloutput)
Begin
/* To get Location from the LICL*/
Select * into #ResxmlNonLicm From(
Select Distinct
tr.EmpID,
[Month],
[Year],
EmpName,
[Salary],
Ml.SeqID[LocationID],
@User[CreatedBy],
@CreatedIP[CreatedIP],
Getdate()[CreatedOn] From #Resxmloutput tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Join V_CM_M_Site Vl
ON Vl.SiteID = Ve.SiteID
Join M_location Ml
ON Ml.[System]= 'FF'
And Vl.SiteName = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> '')A
/*To Updte the T_paydata Table When XML And T_Payadata are same*/
Update tp1 Set tp1.Salary = tr.Salary ,
tp1.EmpName = tr.EmpName,
tp1.ModifiedON =Getdate(),
tp1.ModifiedBy = @user ,
tp1.ModifiedIP =@CreatedIP
From #ResxmlNonLicm tr
Join T_paydata tp1
ON tr.LocationID = tp1.LOCATIONID AND
tr.EmpID = tp1.DomainID
AND tr.[Year] = tp1.Payyear
AND tr.[Month] = tp1.PayMonth
/*To Get th Count How Many Recs Inserted Sucessfully*/
Select 'Total Records Inserted successfully For Non-Licm Employees : '+ ' '
+Cast(Count(*) AS Varchar) From #ResxmlNonLicm tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Join V_CM_M_Site Vl
ON Vl.SiteID = Ve.SiteID
Join M_location Ml
ON Ml.[System]= 'FF'
And Vl.SiteName = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
/*After Update the data in T_paydata Table Delete it from Xml Data*/
Delete tr From T_paydata tp1
Join #ResxmlNonLicm tr
ON tr.LocationID = tp1.LOCATIONID AND
tr.EmpID = tp1.DomainID
AND tr.[Year] = tp1.Payyear
AND tr.[Month] = tp1.PayMonth
/* To Insert the Records in T_paydata Table*/
Insert Into T_paydata
(LocationID,
DomainID,
PayMonth,
PayYear,
Empname,
Salary,
CreatedBy,
CreatedIP,
CreatedOn)
(Select Distinct Ml.SeqID,
tr.EmpID,
[Month],
[Year],
EmpName,
[Salary],
tr.CreatedBy,
tr.CreatedIP,
Getdate() From #ResxmlNonLicm tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Join V_CM_M_Site Vl
ON Vl.SiteID = Ve.SiteID
Join M_location Ml
ON Ml.[System]= 'FF'
And Vl.SiteName = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> '')
Delete tr From #ResxmlNonLicm tr
Join V_CM_M_EmpMaster Ve ON
ve.EmpID = tr.EmpID
Join V_CM_M_Site Vl
ON Vl.SiteID = Ve.SiteID
Join M_location Ml
ON Ml.[System]= 'FF'
And Vl.SiteName = Ml.LocationName
Where tr.EmpID <> '' AND tr.Salary <> '' AND tr.EmpName <> ''
End
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
SET NOCOUNT OFF
END
