hi to all....... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

hi to all.......

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2009-08-05 : 09:34:46

can u help me to code below to procedures which i can implement properly and through that i can send mail after hit the apply button in the erp module.in the leave section...i highlighted that in below...

CREATE PROC ps02_l03
@Leave_tmp LEAVCD,
@RecOption Char(1), /*--'C', 'E', 'P', 'N', 'O', 'A', 'S', 'T', 'M'--*/
@c03_organisation_code GLCOMPANY = NULL,
@c29_location_code GLLOCN = NULL

AS
BEGIN
DECLARE @pamt INT
DECLARE @prate INT
DECLARE @pqty INT
DECLARE @pexch_rate INT
DECLARE @phigh INT
DECLARE @pmed INT
DECLARE @plow INT

DECLARE @LV_UNITS cmn_plow

DECLARE @Modified_Date_Tmp DATETIME

DECLARE @Organisation_Code GLCOMPANY
DECLARE @Location_Code GLLOCN

IF @c03_organisation_code is NULL
BEGIN
EXEC GETORG @Organisation_Code OUT
EXEC GetLoc @Location_Code OUT
END
ELSE
BEGIN
SELECT @Organisation_Code = @c03_organisation_code
SELECT @location_code = @c29_location_code
END

/*---Retrieve precision details for each of the NUMERIC fields---*/

exec common..cmn_precision_details_rtr
'B',
@Organisation_Code,
@pamt OUT,
@prate OUT,
@pqty OUT,
@pexch_rate OUT,
@phigh OUT,
@pmed OUT,
@plow OUT

SELECT @LV_UNITS = P01_BU_Conv_Fact
From P01_Period_Units
Where C03_Organisation_Code = @Organisation_Code
AND P01_Period_Unit_Code =
(
SELECT L03_Leave_Unit
FROM L03_LeaveCode
WHERE C03_Organisation_Code = @Organisation_Code
AND L03_Leave_Code = @Leave_tmp
)

IF @LV_UNITS IS NULL
SELECT @LV_UNITS = 1.0

/*--Commented out check for LOP to prevent view/modification of
LOP leave type. Renu Joy on 30-May-1998 MAT BUG HRM3.1S_003121--*/

IF @RecOption = 'C' /*--Get leave codes and descriptions--*/
BEGIN
SELECT L03_leave_code,
L03_leave_desc
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
/* code commented and Added by Sanjay kumar patro on 27th Aug 2001 for SQL 80 porting - Failure id 28417 Begins*/
/* AND L03_Leave_code like LTRIM(RTRIM(@Leave_Tmp)) + '%' */
AND L03_Leave_code like CASE WHEN LTRIM(RTRIM(@Leave_Tmp))IS NULL THEN '%'
ELSE LTRIM(RTRIM(@Leave_Tmp)) + '%' END
/* code commented and Added by Sanjay kumar patro on 27th Aug 2001 for SQL 80 porting - Failure id 28417 ends*/
-- AND L03_Leave_code <> 'LOP'
END

IF @RecOption = 'P' /*-Get Posting details--*/
BEGIN
SELECT L03_posting_rule,
ROUND((L03_minimum_unit / @LV_UNITS),@plow),
ROUND((L03_maximum_unit / @LV_UNITS),@plow),
L03_affects_service_growth,
L03_encash_leave,
L03_holiday_inclusive,
L03_medical_certification,
L03_without_entitlement
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
AND L03_Leave_code = @Leave_Tmp
AND L03_Leave_code <> 'LOP'

END

IF @RecOption = 'E' /*-Get Entitlement details--*/
BEGIN
SELECT L03_entitlement_rule,
ROUND((L03_entitled_unit / @LV_UNITS),@plow),
L03_without_entitlement
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
AND L03_Leave_code = @Leave_Tmp
AND L03_Leave_code <> 'LOP'

END

IF @RecOption = 'N' /*-Get Encashment details--*/
BEGIN
SELECT L03_Encashment_Rule,
ROUND((L03_Encashment_Unit /@LV_UNITS),@plow)
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Leave_code = @Leave_Tmp AND
L03_encash_leave = 'Y' AND
L03_Leave_code <> 'LOP'

END

IF @RecOption = 'O' /*-Get Carry Over details--*/
BEGIN
SELECT L03_leave_code,
L03_leave_desc,
L03_leave_unit,
L03_carryover_rule,
ROUND((L03_carryover_days / @LV_UNITS),@plow),
L03_affects_service_growth,
L03_encash_leave,
L03_holiday_inclusive,
L03_medical_certification,
L03_without_entitlement
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Leave_code = @Leave_Tmp
END

IF @RecOption = 'A' /*-Filling Combo Box in Entitlement--*/
BEGIN
SELECT L03_leave_desc,
L03_leave_code
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Without_Entitlement = 'N' AND
L03_Leave_code <> 'LOP'
END

/* IF @RecOption = 'F' /*-Filling Combo Box in Entitlement--*/
BEGIN
SELECT L03_leave_desc,
L03_leave_code
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Without_Entitlement = 'N' AND
L03_Leave_code <> 'LOP'

END
*/
IF @RecOption = 'S' /*-Filling Combo Box in Encashment--*/
BEGIN
SELECT L03_leave_desc,
L03_leave_code
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Encash_Leave = 'Y' AND
L03_Leave_code <> 'LOP'
END

IF @RecOption = 'T' /*-Filling Combo Box in Posting--*/
BEGIN
SELECT L03_leave_desc ,
L03_leave_code
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
END

IF @RecOption = 'I' /*-Filling Combo Box in Posting--*/
BEGIN
SELECT L03_leave_desc
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
END

IF @RecOption = 'M'
BEGIN
SELECT L03_medical_certification
FROM L03_LeaveCode
WHERE C03_Organisation_Code = @Organisation_Code
AND L03_Leave_Code = @Leave_tmp
END

IF @RecOption = 'H' /*-Whether Holiday Incl. or not--*/
BEGIN
SELECT L03_holiday_inclusive
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
AND L03_Leave_code = @Leave_Tmp
END

/*Code Added by Bala on 07 Oct 1999 for failure id: 18243Begins*/
IF @RecOption = 'D' /*-Whether Holiday Incl. or not, NO.of hours/day--*/
BEGIN
DECLARE @P01_BU_Conv_Fact cmn_plow
SELECT @P01_BU_Conv_Fact = P01_BU_Conv_Fact
FROM P01_period_units p01
WHERE P01.C03_Organisation_code = @Organisation_Code
AND P01_period_unit_code = 'DAY'

SELECT L03_holiday_inclusive, P01_BU_Conv_Fact= @P01_BU_Conv_Fact
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
AND L03_Leave_code = @Leave_Tmp
END
/*Code Added by Bala on 07 Oct 1999 for failure id: 18243Ends*/

//////////////////////////////////////////////////////////////////////
IF @RecOption = 'Z' /*-Get Posting details--*/
BEGIN
SELECT L03_posting_rule,
Minimum_units = ROUND((L03_minimum_unit / @LV_UNITS),@plow),
Maximum_units = ROUND((L03_maximum_unit / @LV_UNITS),@plow)
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code
AND L03_Leave_code = @Leave_Tmp
AND L03_Leave_code <> 'LOP'

END
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

IF @RecOption = 'O' /*-Get Carry Over details--*/
BEGIN
SELECT L03_leave_unit
FROM L03_Leavecode
WHERE C03_Organisation_code = @Organisation_Code AND
L03_Leave_code = @Leave_Tmp
END

END /*---End of procedure ps02_l03-----*/

below the mail procedure is their how attached with above proc. the highlighted lines are the leave postings paragraph.

-- Enable Database Mail for this instance
EXECUTE sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs',1;
RECONFIGURE;
GO

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Primary Account',
@description = 'Account used by all mail profiles.',
@email_address = 'myaddress@mydomain.com',
@replyto_address = 'myaddress@mydomain.com',
@display_name = 'Database Mail',
@mailserver_name = 'mail.mydomain.com';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Default Public Profile',
@description = 'Default public profile for all users';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Default Public Profile',
@account_name = ' Primary Account',
@sequence_number = 1;

-- Grant access to the profile to all msdb database users
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Default Public Profile',
@principal_name = 'public',
@is_default = 1;
GO

--send a test email
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Test Database Mail Message',
@recipients = 'testaddress@mydomain.com',
@query = 'SELECT @@SERVERNAME';
GO

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 14:58:23

Is t5here a question or problem we need to address?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources