/ ************************************************** ******************* File Name: ** Copyright (C) 2003-2004 ********* BESTEC ** Created: 卢 弋 ** Date: 2004-9-7 ** Modify: ** Date: ** Description: ** ** According to the project number Proj_ID. ** Through the external cooperative account (T_R_Invoice_MONEYAPPLY) ** Travel Resell (T_Form_Money) ** Invoice Collection (T_Form_Invoicein) ** calculation, the topic of the total cost amount is summarized and the current project The cost amount of the BalanceCost and the balance (diffbalancecost) ** with its corresponding invoice (invoice_MONEY), ** amount (Total) **** input parameters: @ proj_id * * Version: v1.0 ** ------------------------------------------- -------------------------------- *************** *********************************************************** /
CREATE FUNCTION getBalanceCost (@Proj_ID int) RETURNS @tmp TABLE (SumBalanceCost money, DiffBalanceCost money, BalanceCost money, Invoice_Money money, MoneyWipeOut money, Total money) ASBEGIN DECLARE @tempSumBalanceCost money DECLARE @tempDiffBalanceCost money DECLARE @tempBalanceCost money DECLARE @tempInvoice_Money money DECLARE @ Tempmoneywipeout Money Declare @temptotal Money
SELECT @tempInvoice_Money = isnull (Sum (Invoice_Money), 0) From T_R_Invoice_MoneyApply Where Cheque_ID in (Select distinct System_ID From T_Form_InnerChequeOut Where Proj_ID = @Proj_ID)
Select @tempmoneywipeout = isnull (SUM (Moneywipeout), 0) from T_Form_Moneylend Where ApplyProjNumber in (Select Projnumber from T_Form_Projaccount Where Proj_ID = @proj_id)
SELECT @tempTotal = isnull (Sum (Total), 0) From T_Form_InvoiceIn Where Proj_ID = @Proj_ID --System_ID in (Select Invoice_ID From T_Form_DailyWipeOut Where InnerChecque_ID in (Select System_ID From T_Form_InnerChequeOut Where Proj_ID = @Proj_ID)) SELECT @tempBalanceCost = isnull ( SUM (BalanceCost), 0) from t_form_projbalance where proj_id = @proj_id
SELECT @tempsumbalanceCost = @ Tempinvoice_MONEY @ TempMoneywipeout @ Temptotal
Select @tempdiffbalanceCost = @ TempsumbalanceCost- @ TempbalanceCost
INSERT INTO @TMP (SumBalanceCost, DiffBalanceCost, BalanceCost, Invoice_Money, MoneyWipeOut, Total) Values (@ tempSumBalanceCost, @ tempDiffBalanceCost, @ tempBalanceCost, @ tempInvoice_Money, @ tempMoneyWipeOut, @ tempTotal)
Returned
/ * ------------------ Code end ------------------- * /
/ * Detachment specified string * /
Declare @separator char (1), - Separator @ReturndEpt Varchar (1000) - Return Value Select @ separator = ',' SELECT @ ReturndEpt = '' SELECT @ Dept = @ Dept ',' While Len (@DEPT) > 0 BEGIN SELECT @ dcode = substring (@ Dept, 0, CHARINDEX (@ Separator, @ Dept)) SELECT @ ReturnDept = @ ReturnDept Char (39) @ dcode Char (39) @ Separator SELECT @ Dept = substring (@ Dept, charindex (@ separator, @ dept) 1, len (@DEPT)) endselect @ returndept = subString (@ ReturNDEPT, 0, LEN (@ReturNDept) -1)
Set quoted_identifier off goset ANSI_NULLS OFF Go
/ ************************************************** ******************* File Name: ** Copyright (C) 2003-2004 ********* BESTEC ** Created: 卢 弋 ** Date: 2004-9-7 ** Modifier: ** Date: ** Description: ** Enter parameters: @ dept, @ Year, @ Month, @ Yearmonth ** version: v1.0 ** ----- -------------------------------------------------- -------------------- **************************** ********************************************* /
ALTER FUNCTION HRP_FactQuotaCompare (@Dept varchar (1000), @ Year int, @ Month int, @ YearMonth int) RETURNS @tmp TABLE (YearSpan int, MonthSpan int, Dept varchar (1000), BasePayQuota money, BasePayFact money, BasePayBalance money, BasePayBalanceTotal money, PerformancePayQuota money, PerformancePayFact money, PerformancePayBalance money, PerformancePayBalanceTotal money, YearEndSubSidyQuota money, YearEndSubSidyFact money, YearFact money, LaborFact money) ASBEGIN DECLARE @tempBasePayQuota money DECLARE @tempBasePayFact money DECLARE @tempBasePayBalance money DECLARE @tempBasePayBalanceTotal money DECLARE @tempPerformancePayQuota money DECLARE @tempPerformancePayFact money DECLARE @tempPerformancePayBalance money DECLARE @tempPerformancePayBalanceTotal money DECLARE @tempYearEndSubSidyQuota money DECLARE @tempYearEndSubSidyFact money DECLARE @tempYearFact money DECLARE @tempLaborFact money DECLARE @tempQuota money DECLARE @tempFact money DECLARE @dcode varchar (24) DECLARE @Separator char (1) - Separator SELECT @ Separator = ',' SELECT @ Dept = @ Dept ',' WHILE len ( @DEPT)> 0 Begin Select @ dcode = Substring (@DEPT, 0, Charindex (@ separator, @ dept))
SELECT @ Dept = substring (@ Dept, CHARINDEX (@ Separator, @ Dept) 1, len (@Dept)) SELECT @tempBasePayQuota = ISNULL (Sum (ISNULL (BasePayQuota, 0)), 0) From HR_PayQuotaSub Where Dept = @ dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth) - monthly salary department quota SELECT @tempBasePayFact = ISNULL (Sum (ISNULL (BasePay, 0)), 0) From HR_FactPayInfoSub Where PID In (Select Sid from HR_FACTPAYINFO where dept = @dcode and status = '40' and yearmonth = @YARMONTH) - Department monthly salary
Select @tempbasepaybalance = @TempBasePayquota - @tempbasepayFact - Department monthly salary
SELECT @tempFact = ISNULL (Sum (ISNULL (BasePay, 0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth <(@ YearMonth * 10))
SELECT @tempQuota = ISNULL (Sum (ISNULL (BasePayQuota, 0)), 0) From HR_PayQuotaSub Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth <(@ YearMonth * 10))
Select @tempbasepaybalanceA - @TempFact - Department accumulated wages - ================================ ============================================================================================================================================================================================================= ================================================== SELECT @tempPerformancePayQuota = ISNULL (Sum (ISNULL (PerformanceQuota, 0)), 0) From HR_PayQuotaSub Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth) - department monthly performance quota
SELECT @tempPerformancePayFact = ISNULL (Sum (ISNULL (PerformancePay, 0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth) - Department monthly performance Disagreement
Select @tempperformancePaybalance = @TempperFormancePayquota - @TempperFormancePayFact - Department Monthly Performance Festival
SELECT @tempFact = ISNULL (Sum (ISNULL (PerformancePay, 0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth <(@ YearMonth * 10))
SELECT @tempQuota = ISNULL (Sum (ISNULL (PerformanceQuota, 0)), 0) From HR_PayQuotaSub Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth <(@ YearMonth * 10)) SELECT @TempperformancePayBalanceTotAl = @Tempquota - @tempFact - Department accumulated performance festival - ================================ ============================================================================================================================================================================================================= =================================================
SELECT @tempYearEndSubSidyQuota = ISNULL (Sum (ISNULL (YearEndBonusQuota, 0)), 0) From HR_PayQuotaSub Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth) - Monthly end sector Prize quota
SELECT @tempYearEndSubSidyFact = ISNULL (Sum (ISNULL (0,0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth) - Monthly end sector Award-winning $
- ================================================================================================================================================================== ============================================================================================================================================================================================================= =========================================== SELECT @tempFact = isnull (SUM (isnull (BasePay, 0)) , 0) from HR_FACTPAYINFOSUB WHERE PID IN (SELECT SID from HR_FACTPAYINFO WHERE DEPT = @dcode and status = '40' And Convert (INT, SUBSTRING (Convert (Varchar, Yearmonth), 0, 4) = @Year) - Department Annual salary
Select @tempyearfact = 0 select @tempyearfact = @TempyearFact @tempFact
SELECT @tempFact = ISNULL (Sum (ISNULL (PerformancePay, 0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And Convert (Int, SubString (Convert (varchar, Yearmonth, 0, 4)) = @Year) - Demonstration
Select @tempyearfact = @TempyearFact @tempFact
SELECT @tempFact = ISNULL (Sum (ISNULL (0,0)), 0) From HR_FactPayInfoSub Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And Convert (Int, SubString (Convert (varchar, Yearmonth, 0, 4)) = @Year) - Department monthly year-end award
Select @tempyearfact = @tempyearfact @tempFact - @TempFact - ======================================== ============================================================================================================================================================================================================= ==================================================== Select @templaborfact = isnull (SUM (ISNULL (Factfee, 0)), 0) from HR_FACTPAYLABORSUB WHERE PID in (Select Sid from HR_FACTPAYLABOR WHERE DEPT = @dcode and status = '40' and yearmonth = @YARMONTH)
- ================================================================================================================================================================== ============================================================================================================================================================================================================= ==================================
INSERT INTO @TMP (YearSpan, MonthSpan, Dept, BasePayQuota, BasePayFact, BasePayBalance, BasePayBalanceTotal, PerformancePayQuota, PerformancePayFact, PerformancePayBalance, PerformancePayBalanceTotal, YearEndSubSidyQuota, YearEndSubSidyFact, YearFact, LaborFact) Values (@ Year, @ Month, @ dcode, @ tempBasePayQuota, @ tempBasePayFact, @ tempBasePayQuota, @ tempBasePayBalanceTotal, @ tempPerformancePayQuota, @ tempPerformancePayFact, @ tempPerformancePayBalance, @ tempPerformancePayBalanceTotal, @ tempYearEndSubSidyQuota, @ tempYearEndSubSidyFact, @ tempYearFact, @ tempLaborFact) END
Returned
/ * ------------------ Code end ------------------- * /
Goset quoted_identifier off goset ANSI_NULLS ON Go
* /
/ * Use of the cursor * /
CREATE FUNCTION getManagerName (@Proj_ID int) RETURNS Nvarchar (500) BEGIN DECLARE @ManagerName Nvarchar (500) SET @ManagerName = '' DECLARE @Name Nvarchar (50) DECLARE manager_cursor CURSOR FORselect ecode.ename from T_Form_ProjTeamSub left outer join ecode on T_Form_ProjTeamSub. ecode = ecode.ecode where T_Form_ProjTeamSub.Source_System_ID in (select System_ID from T_Form_ProjTeam where Proj_ID = @Proj_ID) and T_Form_ProjTeamSub.Title = '001'OPEN manager_cursorFETCH NEXT fROM manager_cursorINTO @NameWHILE @@ FETCH_STATUS = 0BEGINSET @ManagerName = @ManagerName @Name ',' FETCH NEXT FROM manager_cursor INTO @NameENDCLOSE manager_cursorDEALLOCATE manager_cursorif (len (@ManagerName)> 1) begin if (substring (@ ManagerName, len (@ManagerName), 1) = ',') return substring (@ ManagerName, 1, Len (@managername) -1) endreturn @ managernamend * /
