<FOID>9FE43D20EB7A497C9FB1CB9D11F9F69C</FOID>
<FTitle>گزارش مانده مرخصي</FTitle>
<FDDTableName></FDDTableName>
<FQueryText>DECLARE @Remainder INT
DECLARE @LeaveTypeID INT
DECLARE @EmployeeID INT
DECLARE @RowNum INT
DECLARE @EmpCode VARCHAR(20)
DECLARE @Name NVARCHAR(200)
DECLARE @EffDate DATETIME
DECLARE @EffDate1 DATETIME
DECLARE @LeaveTypeDesc NVARCHAR(200)
DECLARE @EmployeeStatusCode INT
Set @EffDate = :"d-  تاريخ موثر"

Set @LeaveTypeID = :"n-نوع مرخصي"
/*pq-"نوع مرخصي";Select '' AS NOP, LeaveTypeID, Title  from Hrm_tblLeaveType;*/

Set @EmployeeStatusCode = :"n-وضعيت کارمند"
/*pq-"وضعيت کارمند";Select '' AS NOP, Code, Description  FROM CMS_tblStatus WHERE Code IN (1, 2, 3);*/


IF OBJECT_ID('TempDB..#TempLeaveRemain') IS NOT NULL
  DROP TABLE #TempLeaveRemain

CREATE TABLE #TempLeaveRemain
(
Row INT, EmployeeID INT, EmpCode VARCHAR(20), Name NVARCHAR(200), Remainder INT
)

IF @EffDate IS NULL
   SET @EffDate = GetDate()

DECLARE crs CURSOR FOR
SELECT EmployeeID, EmpCode, FullName FROM HRM_vwEmployeeParty WHERE StatusCode = @EmployeeStatusCode
OPEN Crs
SET @RowNum = 1
FETCH NEXT FROM crs INTO @EmployeeID, @EmpCode, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC @Remainder = HRM_spGetLeaveRemainder @EmployeeID, @LeaveTypeID, @EffDate, 0
	INSERT #TempLeaveRemain SELECT @RowNum, @EmployeeID, @EmpCode, @Name, @Remainder

	SET @RowNum = @RowNum + 1
	FETCH NEXT FROM crs INTO @EmployeeID, @EmpCode, @Name
END
CLOSE crs
DEALLOCATE crs

SELECT LR.Row, LR.EmpCode, LR.Name, 
dbo.HRM_fnGetLeaveRemainFormat(LR.Remainder, LTP.LeaveTypePropertiesID, 0) Remainder,
dbo.HRM_fnGetLeaveRemainFormat(LR.Remainder, LTP.LeaveTypePropertiesID, 1) RemainderDesc,
WL.Description WorkLocation, ET.Description EmploymentType, LT.Title AS LeaveTypeDesc
FROM #TempLeaveRemain LR
OUTER APPLY [dbo].[HRM_fnGetProperConfirmedEmpStatute](LR.EmployeeID, @EffDate) ES
LEFT OUTER JOIN CMS_tblWorkLocation WL ON ES.WorkLocationCode = WL.Code
LEFT OUTER JOIN CMS_tblEmploymentType ET ON ES.EmploymentTypeCode = ET.Code
LEFT OUTER JOIN HRM_tblLeaveType LT ON LT.LeaveTypeID = @LeaveTypeID
CROSS APPLY dbo.HRM_fnGetProperLeaveType(@LeaveTypeID, ES.EmploymentTypeCode, @EffDate) LTP




</FQueryText>
<FCategoryRef>D94CBC9DA02F4612840F9424737F1DAB</FCategoryRef>
<FPassword>0</FPassword>
<FLastUpdate>03/07/2012 10:13:00 AM</FLastUpdate>
<FStructure>[Storage]
Version=2

[Rreport]
Distinct Rows=0

[Tables]
<TABLES>
<TABLE>
Query:Query
<FIELDS>
<FIELD>
<NAME>Row</NAME><TITLE>Row</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>EmpCode</NAME><TITLE>EmpCode</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>Name</NAME><TITLE>Name</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>Remainder</NAME><TITLE>Remainder</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>RemainderDesc</NAME><TITLE>RemainderDesc</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>WorkLocation</NAME><TITLE>WorkLocation</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>EmploymentType</NAME><TITLE>EmploymentType</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
<FIELD>
<NAME>LeaveTypeDesc</NAME><TITLE>LeaveTypeDesc</TITLE><HIDEFORFILTER>0</HIDEFORFILTER><SELECTIONQUERY></SELECTIONQUERY>
</FIELD>
</FIELDS>
</TABLE>
</TABLES>
<PARAMS>
<PARAM>
<NAME>d-  تاريخ موثر</NAME><VALUE>‏90/11/30</VALUE><HIDEFORPARAM>0</HIDEFORPARAM>
</PARAM>
<PARAM>
<NAME>n-نوع مرخصي</NAME><VALUE>1</VALUE><HIDEFORPARAM>0</HIDEFORPARAM>
</PARAM>
<PARAM>
<NAME>n-وضعيت کارمند</NAME><VALUE>1</VALUE><HIDEFORPARAM>0</HIDEFORPARAM>
</PARAM>
</PARAMS>

</FStructure>
<FSystemic>0</FSystemic>
<FWebReport>0</FWebReport>
