Store Procedure Conversion on SAP B1 Hana.

[Former Member]

Dear All,

Is any one can help me to convert below mention Store Procedure on SAP B1 HANA .

Declare fromDate Datetime ,ToDate Datetime ,ItemGroup nvarchar(100);

Set FromDate = '2011-01-02 12:36:33.873';

Set ToDate = '2015-05-02 12:36:33.873';

Set ItemGroup = '';

With OB as

(

Select OITM ."ItemCode",Sum(OIVL ."InQty" - OIVL ."OutQty" ) as "QTY",Sum("SumStock") as "Value"

from OIVL inner join OITM on OIVL ."ItemCode" = OITM ."ItemCode"

where OITM ."SellItem" = 'Y' and CONVERT (Date ,OIVL ."DocDate" ) < CONVERT (Date ,fromDate)

group by OITM ."ItemCode"

),CB as

(

Select OITM ."ItemCode",Sum(OIVL ."InQty" - OIVL ."OutQty" ) as "QTY",Sum("SumStock") as "Value"

from OIVL inner join OITM on OIVL ."ItemCode" = OITM ."ItemCode"

where OITM ."SellItem" = 'Y' and CONVERT (Date ,OIVL ."DocDate" ) <= CONVERT (Date ,toDate )

group by OITM ."ItemCode"

),Prod as

(

Select OITM ."ItemCode"  ,sum(OIVL ."InQty" - OIVL ."OutQty" )as "Qty",SUM(OIVL ."SumStock") as "Value"

from OIVL inner join OILM on OIVL ."MessageID" = OILM ."MessageID"

inner join OITM on OIVL ."ItemCode" = OITM ."ItemCode"

where OILM ."TransType" in (59,60) and OILM ."BaseType" = 202 and OITM ."SellItem" = 'Y' and

CONVERT (date,OIVL ."DocDate" ) between CONVERT (date ,fromDate ) and CONVERT (date ,ToDate )

group by OITM ."ItemCode"

),Sale as

(

Select OITM ."ItemCode" ,

SUM(case when OCRD ."GroupCode" = 101 then  INV1 ."Quantity" else 0 end )as "QTY_Dist",

SUM(case when OCRD ."GroupCode" = 101 then  INV1 ."LineTotal" else 0 end ) as "Value_Dist",

SUM(case when OCRD ."GroupCode" <> 101 then  INV1 ."Quantity" else 0 end )as "QTY_Global",

SUM(case when OCRD ."GroupCode" <> 101 then  INV1 ."LineTotal" else 0 end ) as "Value_Global"

from OINV inner join INV1 on OINV ."DocEntry" = INV1 ."DocEntry"

inner join OCRD on OINV ."CardCode" = OCRD ."CardCode"

inner join OITM on INV1 ."ItemCode" = OITM ."ItemCode"

where OITM ."SellItem" = 'Y' and OINV .CANCELED = 'N' and

CONVERT (date,OINV ."DocDate" ) between CONVERT (date ,fromDate ) and CONVERT (date ,ToDate )

group by oitm ."ItemCode"

)

Select OITB ."ItmsGrpNam" ,OITM ."ItemCode" ,OITM ."ItemName" ,

ISNULL(Prod ."Qty",0 ) as "Prod_Qty",ISNULL(Prod ."Value" ,0) as "Prod_Value",

ISNULL(Sale ."QTY_Dist" ,0) as "Sale_Dist_Qty",ISNULL(Sale ."Value_Dist" ,0) as "Sale_Dist_Value",

ISNULL(Sale ."QTY_Global" ,0) as "Sal_Global_Qty",ISNULL(Sale ."Value_Global" ,0) as "Sale_Global_Value",

ISNULL(OB."QTY" ,0) as "OB_Qty" ,ISNULL(OB."Value" ,0) as "OB_Value",

ISNULL(CB."QTY" ,0) as "CB_Qty" ,ISNULL(CB."Value" ,0) as "CB_Value"

from OITM  inner join OITB on OITM ."ItmsGrpCod" = OITB ."ItmsGrpCod" 

left outer join Prod on OITM ."ItemCode" = Prod ."ItemCode"

left outer join Sale  on OITM ."ItemCode" = Sale ."ItemCode"

left outer join OB  on OITM ."ItemCode" = OB ."ItemCode"

left outer join CB  on OITM ."ItemCode" = CB ."ItemCode"

where OITM ."SellItem" = 'Y'and  (ISNULL(Prod ."Qty",0 ) <> 0 or ISNULL(Prod ."Value" ,0) <> 0 or

ISNULL(Sale ."QTY_Dist" ,0) <> 0 or ISNULL(Sale ."Value_Dist" ,0) <> 0 or

ISNULL(Sale .QTY_Global ,0) <> 0 or ISNULL(Sale ."Value_Global" ,0) <> 0 or

ISNULL(OB."QTY" ,0) <> 0 or ISNULL(OB."Value" ,0) <> 0 or

ISNULL(CB."QTY" ,0) <> 0 or ISNULL(CB."Value" ,0) <> 0)

[EunSeok_Bang]

Dear Muhammad

how about using table variable?

Regards

Eunseok

[frank_wang6]

Do u want to try the tool mentioned in above article?

Frank

[Former Member]

Dear Frank,

its good tool but not converting my SP .

[frank_wang6]

I think With statement is not supported by HANA.

I guess you might use temp table to do your job.

Frank

[Former Member]

yes Frank i am using temp table , any one have solution ??

[frank_wang6]

Check this, they said SP7 is ok. You might try to patch first.

Frank