iVend Database Database : GetReceiptNodeList Stored Procedure

Creation Date

7/15/2019 11:07 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@ReportType

In

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgUserDefinedFieldDetail

Table

Stores the Details of the User Defined field values

1

CfgUserDefinedTable

Table

Stores the Users defined tables

1

XMLReportHierarchy

Table

stores the hierarchy information of the data objects of the reports in the system.

1

CREATE PROCEDURE [dbo].[GetReceiptNodeList]

-- Add the parameters for the stored procedure here

@ReportType int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @ReceiptData TABLE

     (

     SectionType INT,

     GroupName nvarchar(max),

     FieldName nvarchar(max),

     Type nvarchar(max),

     DefaultName nvarchar(max),

     isUDF bit,

     SubSectionType INT

     )

IF (@ReportType = 13 /*TransactionReceipt for Customer Portal*/)

BEGIN

  SET @ReportType=3

END

  INSERT INTO @ReceiptData(SectionType, GroupName, FieldName, Type, DefaultName, isUDF, SubSectionType)

(SELECT DISTINCT  c.SectionType,c.GroupName

   , REVERSE(SUBSTRING(REVERSE(c.FieldName),CHARINDEX('.',REVERSE(c.FieldName), 1)+1, LEN(c.FieldName)))+'.UserDefinedFields!'+ b.FieldName AS FieldName

   , b.DataType

   , b.FieldName

   , 1 isUDF

   , 0 SubSectionType

  FROM XMLReportHierarchy c, CfgUserDefinedTable a, CfgUserDefinedFieldDetail b

    WHERE ReportType =3 --@ReportType

    and A.UserDefinedTableKey = b.UserDefinedTableKey and A.ObjectId = C.ParentObjectId)

INSERT INTO @ReceiptData(SectionType, GroupName, FieldName, Type, DefaultName, isUDF, SubSectionType)

(SELECT SectionType, GroupName, FieldName, Type, DefaultName, 0, SubSectionType FROM XMLReportHierarchy WHERE ReportType in (@ReportType, 0))

SELECT SectionType, GroupName, FieldName, Type, DefaultName, isUDF, SubSectionType FROM @ReceiptData

ORDER BY SectionType, GroupName, FieldName

END