iVend Database Database : Rpt_ItemWithoutSale User Defined Function

Creation Date

9/17/2019 9:19 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarWChar

8

@ToDate

In

VarWChar

8

@Subsidiary

In

VarWChar

400

@Store

In

VarWChar

400

@Location

In

VarWChar

400

@ProductGroup

In

VarWChar

400

@FromProduct

In

VarWChar

100

@ToProduct

In

VarWChar

100

@ProductCategory

In

VarChar

4000

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

1

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

2

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

2

InvGoodsReturn

Table

Stores the data related to Inventory Goods Return

2

InvGoodsReturnDetail

Table

Stores the details of inventory goods return

2

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvInventoryItemLog

Table

Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated.

2

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvInventoryLocationLog

Table

Stores the logs or Audit information of location for the Inventory available at different locations

2

InvInventoryTransaction

Table

This gets affected when the inventory of a particular item is consumed at the store itself for any reason.

2

InvInventoryTransactionDetail

Table

Defines the details of the product on the goods issue note

2

InvLocation

Table

Save the location infomation.

1

InvProduct

Table

Stores the product related details.

1

InvProductBuildBreak

Table

Stores the information when the product is Build or Broken into its components

2

InvProductBuildBreakDetail

Table

Defines the product details which is being built or break.

2

InvProductCategory

Table

Defines the categories under which the products can be put for logical classification.

1

InvProductCategoryProduct

Table

Defines the list of products that have been put under each category.

1

InvProductGroup

Table

Defined the various groups under which the products can be categorised.

1

InvStockTransfer

Table

Stores stock transfer details

2

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

2

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

rptOpeningBalance

User Defined Function

1

rptOpeningBalance_Location

User Defined Function

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

RtlSubsidiary

Table

Defines all the Subsidiaries defined in the application

1

SubSubsidiaryItem

Table

Stores Subsidiary information for each master, which can have multiple Subsidiaries assigned

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE Function [dbo].[Rpt_ItemWithoutSale](

@FromDate nVarchar(8),

@ToDate nVarchar(8),

@Subsidiary nVarchar(400),

@Store nVarchar(400),

@Location nVarchar(400),

@ProductGroup nVarchar(400),

@FromProduct nVarchar(100),

@ToProduct nVarchar(100),

@ProductCategory Varchar(4000)

)

RETURNS @Temp Table (

Type Nvarchar(100),

ProductKey NVarchar(200),

Item Nvarchar(100),

ItemDesc NVarchar(200),

ItemGroup NVarchar(100),

ItemGroupDesc NVarchar(200),

InStockQuantity Decimal(10,2),

SubsidiaryId Nvarchar(100),

SubsidiaryDesc Nvarchar(200),

StoreId Nvarchar(200),

StoreDesc Nvarchar(200),

LocationId Nvarchar(100),

LocationDesc Nvarchar(200),

IsLocationEnabled bit

)

AS

BEGIN

--Declare  @Temp Table (Type Nvarchar(100), ProductKey Bigint, Item Nvarchar(100), ItemDesc NVarchar(200), ItemGroup NVarchar(100), ItemGroupDesc NVarchar(200), InStockQuantity Decimal(10,2),

--SubsidiaryId Nvarchar(100), SubsidiaryDesc Nvarchar(200), StoreId Nvarchar(200), StoreDesc Nvarchar(200), LocationId Nvarchar(100), LocationDesc Nvarchar(200), IsLocationEnabled bit

--)

--DECLARE @FromDate nVarchar(8)='20160315', @ToDate nVarchar(8)='20160315', @Store nVarchar(200)='', @ProductGroup nVarchar(200)='', @Location Nvarchar(200)='',

--@FromProduct nVarchar(200)='', @ToProduct nVarchar(200)='', @ProductCategory Varchar(4000)='', @IncludeLayaway Bit=1, @IncludeOrder Bit=1 , @Subsidiary Nvarchar(200)=''

Declare @ISLocationEnable Bit

Select @ISLocationEnable = ISNULL(EnableLocation,0) From CfgEnterprise

IF @ISLocationEnable = 1

BEGIN

INSERT INTO @Temp (Type, ProductKey, Item, ItemDesc, ItemGroup, ItemGroupDesc, InStockQuantity, SubsidiaryId, SubsidiaryDesc, StoreId, StoreDesc,

    LocationId, LocationDesc, IsLocationEnabled)

SELECT 'Sale' AS Type, P.ProductKey, P.Id AS Item, P.Description AS ItemDesc, PG.Id AS ItemGroup, PG.Description AS ItemGroupDesc,

SUM(ISNULL(dbo.rptOpeningBalance_Location(@ToDate, @ToDate, P.ProductKey, Wh.WarehouseKey, Loc.LocationKey),0)) AS InStockQuantity,

 Sub.Id As SubsidiaryId, Sub.Description AS SubsidiaryDesc, ISNULL(S.Id,'Ho') AS StoreId, ISNULL(S.Description,'Enterprise') AS StoreDesc,

 Loc.Id AS LocationId, Loc.Description AS LocationDesc, Wh.IsLocationEnabled

FROM

 InvProduct P

CROSS JOIN InvWarehouse WH

LEFT OUTER JOIN InvProductGroup PG ON P.ProductGroupKey = PG.ProductGroupKey

LEFT OUTER JOIN InvProductCategoryProduct PCP ON PCP.ProductKey = P.ProductKey

LEFT OUTER JOIN InvProductCategory PC ON PCP.ProductCategoryKey = PC.ProductCategoryKey

LEFT OUTER JOIN InvInventoryLocation I ON P.ProductKey = I.ProductKey AND Wh.WarehouseKey =  I.WarehouseKey

LEFT OUTER JOIN SubSubsidiaryItem SSI ON P.ProductKey= SSI.SourceKey AND SourceType=46

LEFT OUTER JOIN RtlSubsidiary Sub ON SSI.SubsidiaryKey = Sub.SubsidiaryKey

LEFT JOIN RtlStore S ON Wh.WarehouseKey = S.WarehouseKey AND ISNULL(Sub.SubsidiaryKey, '0')= ISNULL(S.SubsidiaryKey, '0')

LEFT OUTER JOIN InvLocation Loc ON I.LocationKey = Loc.LocationKey

WHERE

 P.IsDeleted = 0 AND P.IsGiftCertificate=0 AND IsStoreCredit=0

AND ISNULL(Sub.Id,'')= CASE WHEN @Subsidiary <> '' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

AND CHARINDEX(ISNULL(S.Id,''), @Store) > CASE WHEN @Store <> '' THEN 0 ELSE -1 END

AND CHARINDEX(ISNULL(Loc.Id,''), @Location) > CASE WHEN @Location <> '' THEN 0 ELSE -1 END

AND CHARINDEX(PG.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

AND P.Id Between CASE WHEN @FromProduct <> '' THEN @FromProduct ELSE P.Id END AND CASE WHEN @ToProduct <> '' THEN @ToProduct ELSE P.Id END

AND CHARINDEX(ISNULL(PC.Id,''), @ProductCategory) > CASE WHEN @ProductCategory <> '' THEN 0 ELSE -1 END

AND P.ProductKey NOT IN (

    SELECT ProductKey FROM TrxTransactionSaleItem WHERE TransactionKey IN (

    SELECT TransactionKey FROM TrxTransaction

    Where CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

    AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END ))

GROUP BY P.ProductKey, P.Id, P.Description, PG.Id, PG.Description, PC.Id,

 Sub.Id, Sub.Description, S.Id, S.Description, Loc.Id, Loc.Description, Wh.IsLocationEnabled

END

IF (@ISLocationEnable =0 )

BEGIN

INSERT INTO @Temp (Type, ProductKey, Item, ItemDesc, ItemGroup, ItemGroupDesc, InStockQuantity, SubsidiaryId, SubsidiaryDesc, StoreId, StoreDesc,

    IsLocationEnabled)

SELECT 'Sale' AS Type, P.ProductKey, P.Id AS Item, P.Description AS ItemDesc, PG.Id AS ItemGroup, PG.Description AS ItemGroupDesc,

SUM(ISNULL(dbo.rptOpeningBalance(@ToDate, @ToDate, P.ProductKey, Wh.WarehouseKey),0)) AS InStockQuantity,

 Sub.Id As SubsidiaryId, Sub.Description AS SubsidiaryDesc, ISNULL(S.Id,'Ho') AS StoreId, ISNULL(S.Description,'Enterprise') AS StoreDesc,

 Wh.IsLocationEnabled

FROM

 InvProduct P

LEFT OUTER JOIN InvProductCategoryProduct PCP ON PCP.ProductKey = P.ProductKey

LEFT OUTER JOIN InvProductCategory PC ON PCP.ProductCategoryKey = PC.ProductCategoryKey

LEFT OUTER JOIN InvProductGroup PG ON P.ProductGroupKey = PG.ProductGroupKey

INNER JOIN InvInventoryItem I ON P.ProductKey = I.ProductKey

LEFT JOIN InvWarehouse Wh ON I.WarehouseKey = Wh.WarehouseKey

LEFT JOIN RtlStore S ON Wh.WarehouseKey = S.WarehouseKey

LEFT OUTER JOIN RtlSubsidiary Sub ON Wh.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN SubSubsidiaryItem SSI ON P.ProductKey= SSI.SourceKey AND S.SubsidiaryKey = SSI.SubsidiaryKey AND SourceType=46

WHERE

 P.IsDeleted = 0 AND P.IsGiftCertificate=0 AND IsStoreCredit=0

AND ISNULL(Sub.Id,'')= CASE WHEN @Subsidiary <> '' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

AND CHARINDEX(ISNULL(S.Id,''), @Store) > CASE WHEN @Store <> '' THEN 0 ELSE -1 END

AND CHARINDEX(PG.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

AND P.Id Between CASE WHEN @FromProduct <> '' THEN @FromProduct ELSE P.Id END AND CASE WHEN @ToProduct <> '' THEN @ToProduct ELSE P.Id END

AND CHARINDEX(ISNULL(PC.Id, ''), @ProductCategory) > CASE WHEN @ProductCategory <> '' THEN 0 ELSE -1 END

AND P.ProductKey NOT IN (

    SELECT ProductKey FROM TrxTransactionSaleItem WHERE TransactionKey IN (

    SELECT TransactionKey FROM TrxTransaction

    Where CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

    AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END ) AND Type=0)

GROUP BY P.ProductKey, P.Id, P.Description, PG.Id, PG.Description, PC.Id, Sub.Id, Sub.Description, S.Id, S.Description, Wh.IsLocationEnabled

END

---   Select * From @Temp

RETURN

END