cancel
Showing results for 
Search instead for 
Did you mean: 

SAP B1 - reusage of stored procedure - TmSp_Sales_An_Itm_SNG_M

sukhbold_altanbat
Active Participant
0 Kudos
529

Hi Experts,

We have defined UDF both in item and BP master data.

Standard B1 sales/purchase analysis report doesn't consider UDF (user defined fields) from item & BP master data. 1) How to display UDF as column in the standard report?

2) There are list of predefined stored procedures used during sales/purchase analysis report run. We tried to call the respective SP in our query and then to include the UDF.

However, our query results with an error

/*Temporary table for storing the result of calling the SP TmSp_Sales_An_Itm_SNG_M*/

Declare @analysisTable table ( 
ItemCode nvarchar(50), 
ItemName nvarchar(100), 
SlpCode smallint , 
SlpName nvarchar(50), 
Months smallint, 
Years int , 
Quantity int,  
TotalAmount decimal(19, 6), 
Amount1 decimal(19, 6),
Amount2 decimal(19, 6), 
Amount3 decimal(19, 6), 
Amount4 decimal(19, 6), 
Amount5 decimal(19, 6), 
Amount6 decimal(19, 6), 
Amount7 decimal(19, 6)); 

insert @analysisTable  EXEC [dbo].[TmSp_Sales_An_Itm_SNG_M] @ObjectType = N'INV', @ShowType = N'SNG', @DocDateFrom = N'20060122', @DocDateTo = N'20190301', @ItemCodeFrom = N'A00001', @ItemCodeTo = N'A00006', @cutbyObject = N'SLP'

ERROR: An INSERT EXEC statement cannot be nested.

I have tried to find other ways to resolve this issue and the following was suggested.

insert into @analysisTableselect a.* from 
openrowset ('SQLOLEDB', 'Server=localhost;Trusted_Connection=YES', 'set fmtonly off EXEC SBODemoSG.dbo.TmSp_Sales_An_Itm_Grp_M INV,SNG with result sets(( ItemCode nvarchar(50) , ItemName nvarchar(100) , SlpCode varchar(5)  , SlpName nvarchar(50) , Months smallint , Years int  , Quantity int  , TotalAmount decimal(19, 6) , Amount1 decimal(19, 6) , Amount2 decimal(19, 6) , Amount3 decimal(19, 6) , Amount4 decimal(19, 6) , Amount5 decimal(19, 6) , Amount6 decimal(19, 6) , Amount7 decimal(19, 6))) ') as a

Error:

Cannot process the object "set fmtonly off EXEC SBODemoSG.dbo.TmSp_Sales_An_Itm_Grp_M INV,SNG
with result sets(( ItemCode nvarchar(50), ...)) ". The OLE DB provider "SQLNCLI11" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Thanks,

Sukhbold

Accepted Solutions (0)

Answers (0)