Share
Report
555I have create a new Pv in Intran.User1 field in Recipts Screen.
Have altered Site_With_InvtFlag Procedure ,Have a custom Table X_site in X_Site Columns are UserID,SiteId and Enable .
Intran.USer1 PV:"X_Site_With_InvtFlag", "bpes.cpnyid"; 0; 0; 0; 0, "bintran.invtid"; 0; 0; 0; 0, "bPes.UserId"; 0; 0; 0; 0,
CustomPv.Csv: X_Site_With_InvtFlag,,,SELECT * FROM vi_SiteWithInvtFlag WHERE CpnyID = @Parm1 AND (InvtID = @Parm2 or (InvtID is null and not exists (select 'x' from vi_SiteWithInvtFlag v2 where vi_SiteWithInvtFlag.CpnyID = v2.CpnyID AND v2.InvtID = @Parm2 AND vi_SiteWithInvtFlag.SiteID = v2.SiteID ))) And siteid =(CASE WHEN @Parm3 not in (select Top 1 UserId from X_site x where x.UserId=@Parm3) then SiteId WHEN vi_SiteWithInvtFlag.User5=0 then SiteId when vi_SiteWithInvtFlag.User5=1 AND vi_SiteWithInvtFlag.SiteId is not null and (not exists (select 'x' from X_site x where vi_SiteWithInvtFlag.SiteId=x.SiteId and not (x.UserId = @Parm3 and enable =1 ))or exists(select 'x' from X_site x where vi_SiteWithInvtFlag.SiteId=x.SiteId and (x.UserId = @Parm3 and enable=1))) Then siteid end) AND vi_SiteWithInvtFlag.SiteId LIKE @Parm4 ORDER BY vi_SiteWithInvtFlag.SiteID,Site,vi_SiteWithInvtFlag.SiteID;Site ID;118;0,vi_SiteWithInvtFlag.Name;Name;110;0,vi_SiteWithInvtFlag.ExistingSiteFlag;Active Site for this Item;0;0
if Site.User5=1 then it's restricted site and if the particular user have access in X_Site then only he can see the site.
X_site Table Sample:
UserId SiteId Enable
SYSADMIN 200 1
USerA 400 1
Thillai 200 1
ANBURAJ 200 1
My StoreProcedure is
In procedure have passing CurrentUserID as @Param3
Create PROCEDURE X_Site_With_InvtFlag
@Parm1 VARCHAR (10),--'0060' CpnyID
@Parm2 VARCHAR (30),--'0RBOLT' InvtID
@Parm3 VARCHAR (30),--'SYSADMIN' --Here have changed Invt Id to USerID
@Parm4 VARCHAR (10)--% --SiteID
As
SELECT distinct * FROM vi_SiteWithInvtFlag WHERE CpnyID = @Parm1 AND (InvtID = @Parm2 or (InvtID is null and not exists
(select 'x' from vi_SiteWithInvtFlag v2 where vi_SiteWithInvtFlag.CpnyID = v2.CpnyID AND v2.InvtID = @Parm2 AND vi_SiteWithInvtFlag.SiteID
= v2.SiteID ))) And siteid = ( CASE WHEN @Parm3 not in (select Top 1 UserId from X_site x where x.UserId=@Parm3) then SiteId WHEN
vi_SiteWithInvtFlag.User5=0 then SiteId when vi_SiteWithInvtFlag.User5=1 AND vi_SiteWithInvtFlag.SiteId is not null and (not exists
(select 'x' from X_site x where vi_SiteWithInvtFlag.SiteId=x.SiteId and not (x.UserId = @Parm3 and enable =1 ))or exists(select 'x' from X_site
x where vi_SiteWithInvtFlag.SiteId=x.SiteId and (x.UserId = @Parm3 and enable=1))) Then siteid end) AND vi_SiteWithInvtFlag.SiteId
LIKE @Parm4 ORDER BY vi_SiteWithInvtFlag.SiteID
The above sp is Perfectly working in Sql .but in Screen i press f3 then error only showing .Have attached the First error screen shot and 2 nd error is The cursor (82) has 0 columns associated it should be greater then o and less then or equal to 256.
Here UserID as @Param3 but in Error screen showing X.userid='0060' so it collaspe the Params.but have removed the case statement in Store procedure then it will works fine.but very difficult to write without case statements.
Please any one help me.
*This post is locked for comments
All responses (
Answers (
