SAP B1 query generator problem

[Former Member]

Experts,

I have written the below stored procedure

GO

/****** Object: StoredProcedure [dbo].[MIPLRG23D] Script Date: 10/20/2011 09:40:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[MIPLRG23D] @FrDt as Datetime,@ToDt as Datetime

AS

BEGIN

create table #temp (

TransNum int,

TransType int,

TypeOfTransaction varchar(100),

ItemCode varchar (100),

QuantityReceived int,

ChapterId int,

PREF varchar (100),

CUSTOMER varchar (100),

SUPPLIER varchar (100),

DUTYP varchar (100),

PDUTY Float,

PostingDate Date,

DocNum int,

CVD_DUTY Float,

EDU_CESS Float,

SEC_CESS Float,

ADC Float,

TotalDutyPassed Float,

QuantityIssued Float,

EntryNo int,

Stock int

)

Declare @TransNum int,

@TransType int,

@TypeOfTransaction varchar(100),

@ItemCode varchar (100),

@QuantityReceived int,

@ChapterId int,

@PREF varchar (100),

@CUSTOMER varchar (100),

@SUPPLIER varchar (100),

@DUTYP varchar (100),

@PDUTY Float,

@PostingDate Date,

@DocNum int,

@CVD_DUTY Float,

@EDU_CESS Float,

@SEC_CESS Float,

@ADC Float,

@TotalDutyPassed Float,

@QuantityIssued Float,

@EntryNo int,

@Stock int

SET @Stock = 0

DECLARE rt_cursor CURSOR FOR

select

T0.TransNum,

T0.TransType,

CASE

When T0.TransType = '59' Then 'Opening Balance / Goods Receipt'

When T0.TransType = '20' Then 'GRPO'

When T0.TransType = '18' Then 'AP Invoice'

When T0.TransType = '21' Then 'Goods Return'

When T0.TransType = '67' Then 'Inventory Transfer'

When T0.TransType = '60' Then 'Goods Issue'

When T0.TransType = '59' Then 'Goods Receipt'

When T0.TransType = '13' Then 'AR Invoice'

When T0.TransType = '15' Then 'Deliveries'

When T0.TransType = '69' Then 'Landed Cost'

End 'Type of Transaction' ,

T0.ItemCode,

ISNULL(CASE

when T0.TransType = '18' Then (Select MAX(T9.Quantity) from PCH1 T9 inner join OPCH T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

When T0.TransType = '59' Then T0.InQty

End, 0) 'Quantity Received',

(select MAX(T1.SuppCatNum) from OITM T1 where T1.ItemCode = T0.ItemCode) 'Chapter Id',

CASE

When T0.TransType = '13' Then

(Select MAX(T2.U_Att1 + '/' + T2.U_Att2) from INV1 T2 where T2.ItemCode = T0.ItemCode)

When T0.TransType = '18' Then

(Select MAX(T2.U_Att1 + '/' + T2.U_Att2) from PCH1 T2 where T2.ItemCode = T0.ItemCode)

End 'PREF',

CASE when T0.TransType = '13'

Then T0.CardName

End 'CUSTOMER',

CASE when T0.TransType = '18'

Then T0.CardName

End 'SUPPLIER',

CASE When T0.TransType = '18' Then

(select MAX (CAST(T2.U_CVDP as varchar(10)) '% ' CAST(T2.U_UNEP as varchar(10)) '% 'CAST(T2.U_USHP as varchar(30))

'% ' CAST(T2.U_ADCP as varchar(10)))+'%' from PCH1 T2 inner join OPCH T15 on T2.DocEntry = T15.DocEntry where T15.DocNum = T0.Base_Ref and T2.ItemCode = T0.ItemCode and T2.LineNum = T0.DocLineNum)

End 'DUTYP',

CASE When T0.TransType = '18' Then

(select MAX((T2.U_CVD * T2.Quantity) + (T2.U_UNE * T2.Quantity) + (T2.U_USH * T2.Quantity)

+ (T2.U_ADC * T2.Quantity)) from PCH1 T2 inner join OPCH T15 on T2.DocEntry = T15.DocEntry where T15.DocNum = T0.Base_Ref and T2.ItemCode = T0.ItemCode and T2.LineNum = T0.DocLineNum)

End 'PDUTY',

T0.DocDate 'Posting Date',

T0.BASE_REF 'Doc Num',

(Select MAX(T9.U_CVD * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'CVD_DUTY',

(Select MAX(T9.U_UNE * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'EDU_CESS',

(Select MAX(T9.U_USH * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'SEC CESS',

(Select MAX(T9.U_ADC * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'ADC',

(

(Select MAX(T9.U_CVD * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_UNE * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_USH * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_ADC * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

) 'Total Duty Passed',

ISNULL(CASE

when T0.TransType = '13' Then (Select MAX(T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

When T0.TransType = '60' Then T0.OutQty

End, 0) 'Quantity Issued',

CASE

When T0.TransType = '13' Then (Select MAX(T9.U_RG23D) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.BASE_REF and T9.LineNum = T0.DocLineNum)

When T0.TransType = '60' Then (Select MAX (T11.U_RG23D) from IGE1 T11 inner join OIGE T12 on T11.DocEntry = T12.DocEntry where T12.DocNum = T0.BASE_REF and T11.LineNum = T0.DocLineNum)

When T0.TransType = '59' Then (Select MAX (T11.U_RG23D) from IGN1 T11 inner join OIGN T12 on T11.DocEntry = T12.DocEntry where T12.DocNum = T0.BASE_REF and T11.LineNum = T0.DocLineNum)

When T0.TransType = '18' Then (Select MAX(T9.U_RG23D) from PCH1 T9 inner join OPCH T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.BASE_REF and T9.LineNum = T0.DocLineNum)

End 'Entry No'

from OINM T0

where

T0.ItemCode = 'ELE3ACC0010108' and

T0.DocDate between @FrDt and @ToDt

and T0.TransType NOT IN ( '15','20')

group by T0.TransNum,T0.TransType,T0.ItemCode, T0.CardName, T0.DocDate, T0.BASE_REF, T0.InQty, T0.OutQty, T0.DocLineNum

order by T0.DocDate

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Stock = @Stock + @QuantityReceived - @QuantityIssued

Insert #temp values

(

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo,

@Stock

)

FETCH NEXT FROM rt_cursor INTO

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

select * from #temp

drop table #temp

End

Now in SAP B1 query generator, I have written

Declare @FrDt as Datetime,@ToDt as Datetime

select @FrDt=T0.DocDate from OINM T0 where T0.DocDate='[%0]'

select @ToDt=T0.DocDate from OINM T0 where T0.DocDate='[%1]'

execute [MIPLRG23D] @FrDt,@ToDt

when i run this query, it gives error like

1). [Microsoft][SQL Server Native Client 10.0]Invalid cursor state 'User-Defined Values' (CSHS)

Why everytime problem is faced with SAP B1 query manager? am i going wrong somewhere? please help

[Former Member]

GO

/****** Object: StoredProcedure [dbo].[MIPLRG23D] Script Date: 10/20/2011 09:40:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[MIPLRG23D] @FrDt as Datetime,@ToDt as Datetime

AS

BEGIN

create table #temp (

TransNum int,

TransType int,

TypeOfTransaction varchar(100),

ItemCode varchar (100),

QuantityReceived int,

ChapterId int,

PREF varchar (100),

CUSTOMER varchar (100),

SUPPLIER varchar (100),

DUTYP varchar (100),

PDUTY Float,

PostingDate Date,

DocNum int,

CVD_DUTY Float,

EDU_CESS Float,

SEC_CESS Float,

ADC Float,

TotalDutyPassed Float,

QuantityIssued Float,

EntryNo int,

Stock int

)

Declare @TransNum int,

@TransType int,

@TypeOfTransaction varchar(100),

@ItemCode varchar (100),

@QuantityReceived int,

@ChapterId int,

@PREF varchar (100),

@CUSTOMER varchar (100),

@SUPPLIER varchar (100),

@DUTYP varchar (100),

@PDUTY Float,

@PostingDate Date,

@DocNum int,

@CVD_DUTY Float,

@EDU_CESS Float,

@SEC_CESS Float,

@ADC Float,

@TotalDutyPassed Float,

@QuantityIssued Float,

@EntryNo int,

@Stock int

SET @Stock = 0

DECLARE rt_cursor CURSOR FOR

select

T0.TransNum,

T0.TransType,

CASE

When T0.TransType = '59' Then 'Opening Balance / Goods Receipt'

When T0.TransType = '20' Then 'GRPO'

When T0.TransType = '18' Then 'AP Invoice'

When T0.TransType = '21' Then 'Goods Return'

When T0.TransType = '67' Then 'Inventory Transfer'

When T0.TransType = '60' Then 'Goods Issue'

When T0.TransType = '59' Then 'Goods Receipt'

When T0.TransType = '13' Then 'AR Invoice'

When T0.TransType = '15' Then 'Deliveries'

When T0.TransType = '69' Then 'Landed Cost'

End 'Type of Transaction' ,

T0.ItemCode,

ISNULL(CASE

when T0.TransType = '18' Then (Select MAX(T9.Quantity) from PCH1 T9 inner join OPCH T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

When T0.TransType = '59' Then T0.InQty

End, 0) 'Quantity Received',

(select MAX(T1.SuppCatNum) from OITM T1 where T1.ItemCode = T0.ItemCode) 'Chapter Id',

CASE

When T0.TransType = '13' Then

(Select MAX(T2.U_Att1 + '/' + T2.U_Att2) from INV1 T2 where T2.ItemCode = T0.ItemCode)

When T0.TransType = '18' Then

(Select MAX(T2.U_Att1 + '/' + T2.U_Att2) from PCH1 T2 where T2.ItemCode = T0.ItemCode)

End 'PREF',

CASE when T0.TransType = '13'

Then T0.CardName

End 'CUSTOMER',

CASE when T0.TransType = '18'

Then T0.CardName

End 'SUPPLIER',

CASE When T0.TransType = '18' Then

(select MAX (CAST(T2.U_CVDP as varchar(10)) '% ' CAST(T2.U_UNEP as varchar(10)) '% 'CAST(T2.U_USHP as varchar(30))

'% ' CAST(T2.U_ADCP as varchar(10)))+'%' from PCH1 T2 inner join OPCH T15 on T2.DocEntry = T15.DocEntry where T15.DocNum = T0.Base_Ref and T2.ItemCode = T0.ItemCode and T2.LineNum = T0.DocLineNum)

End 'DUTYP',

CASE When T0.TransType = '18' Then

(select MAX((T2.U_CVD * T2.Quantity) + (T2.U_UNE * T2.Quantity) + (T2.U_USH * T2.Quantity)

+ (T2.U_ADC * T2.Quantity)) from PCH1 T2 inner join OPCH T15 on T2.DocEntry = T15.DocEntry where T15.DocNum = T0.Base_Ref and T2.ItemCode = T0.ItemCode and T2.LineNum = T0.DocLineNum)

End 'PDUTY',

T0.DocDate 'Posting Date',

T0.BASE_REF 'Doc Num',

(Select MAX(T9.U_CVD * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'CVD_DUTY',

(Select MAX(T9.U_UNE * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'EDU_CESS',

(Select MAX(T9.U_USH * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'SEC CESS',

(Select MAX(T9.U_ADC * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum) 'ADC',

(

(Select MAX(T9.U_CVD * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_UNE * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_USH * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)+

(Select MAX(T9.U_ADC * T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

) 'Total Duty Passed',

ISNULL(CASE

when T0.TransType = '13' Then (Select MAX(T9.Quantity) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.Base_Ref and T9.LineNum = T0.DocLineNum)

When T0.TransType = '60' Then T0.OutQty

End, 0) 'Quantity Issued',

CASE

When T0.TransType = '13' Then (Select MAX(T9.U_RG23D) from INV1 T9 inner join OINV T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.BASE_REF and T9.LineNum = T0.DocLineNum)

When T0.TransType = '60' Then (Select MAX (T11.U_RG23D) from IGE1 T11 inner join OIGE T12 on T11.DocEntry = T12.DocEntry where T12.DocNum = T0.BASE_REF and T11.LineNum = T0.DocLineNum)

When T0.TransType = '59' Then (Select MAX (T11.U_RG23D) from IGN1 T11 inner join OIGN T12 on T11.DocEntry = T12.DocEntry where T12.DocNum = T0.BASE_REF and T11.LineNum = T0.DocLineNum)

When T0.TransType = '18' Then (Select MAX(T9.U_RG23D) from PCH1 T9 inner join OPCH T10 on T9.DocEntry = T10.DocEntry where T10.DocNum = T0.BASE_REF and T9.LineNum = T0.DocLineNum)

End 'Entry No'

from OINM T0

where

T0.ItemCode = 'ELE3ACC0010108' and

T0.DocDate between @FrDt and @ToDt

and T0.TransType NOT IN ( '15','20')

group by T0.TransNum,T0.TransType,T0.ItemCode, T0.CardName, T0.DocDate, T0.BASE_REF, T0.InQty, T0.OutQty, T0.DocLineNum

order by T0.DocDate

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Stock = @Stock + @QuantityReceived - @QuantityIssued

Insert #temp values

(

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo,

@Stock

)

FETCH NEXT FROM rt_cursor INTO

@TransNum,

@TransType,

@TypeOfTransaction,

@ItemCode,

@QuantityReceived,

@ChapterId,

@PREF,

@CUSTOMER,

@SUPPLIER,

@DUTYP,

@PDUTY,

@PostingDate,

@DocNum,

@CVD_DUTY,

@EDU_CESS,

@SEC_CESS,

@ADC,

@TotalDutyPassed,

@QuantityIssued,

@EntryNo

END

CLOSE rt_cursor

DEALLOCATE rt_cursor

select * from #temp

drop table #temp

End

[Former Member]

Hi Shruti............

Wherever you took UDF values in your query please try applying wither Cast(U_UDF as nvarchar (50)) or Isnull(U_UDF,0)

Please try above syntax......

Regards,

Rahul

[Former Member]

Everything working fine in SQL Server,

I think As i have used Cursor in Stored Procedure i t shows problem in SAP B1 query manager.

I used

Select [dbo].[MIPLRG23D1] ('20100810', '20111018')

It shows

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MIPLRG23D1", or the name is ambiguous. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service C

I f i use

Exec [dbo].[MIPLRG23D1] '20100810', '20111018'

it shows

1). [Microsoft][SQL Server Native Client 10.0]Invalid cursor state

Please help

what to use in SAP B1 query manager to make it run

[Former Member]

Any suggestions?

[Former Member]

Please help