iVend Database Database > Stored Procedures > rpt

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

rpt_LayAway Stored Procedure

Collapse All Expand All

iVend Database Database : rpt_LayAway Stored Procedure

Properties

Creation Date	8/27/2019 10:04 AM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@FromDate	In		VarChar	8
@ToDate	In	To Date (Part of Date Range in YYYYMMDD Format)	VarChar	8
@ProductGroup	In	Product Group (Prefixed and Postfixed with '?' character )	VarChar	4000
@FromProduct	In	From Product (Part of Product Range )	VarChar	40
@ToProduct	In	To Product (Part of Product Range )	VarChar	40
@CustomerGroup	In	Customer Group (Prefixed and Postfixed with '?' character)	VarChar	4000
@FromCustomer	In	From Customer (Part of Customer Range )	VarChar	40
@ToCustomer	In	To Customer (Part of Customer Range )	VarChar	40
@Store	In	Prefixed and Postfixed with '?' character	VarChar	4000
@Subsidiary	In	Prefixed and Postfixed with '?' character	VarChar	4000
@SalesPerson	In	Prefixed and Postfixed with '?' character	VarChar	4000
@DispOverDueOnly	In	Displays over due records only	Boolean	1
@IsDelivery	In	Specify Whether this is LayAway Delivery Report	Boolean	1
@DeliveryFrom	In	Delivery From Date (Part of Delivery Date Range in YYYYMMDD Format)	VarChar	8
@DeliveryTo	In	Delivery To Date (Part of Delivery Date Range in YYYYMMDD Format)	VarChar	8
@IsStatement	In	Specify Whether this is LayAway Statement Report	Boolean	1
@RETURN_VALUE	Return Value		Integer	4

Objects that rpt_LayAway depends on

Database Object	Object Type	Description	Dep Level
CusCustomer	Table	Defines the customer details that interact with the enterprise.	1
CusCustomerGroup	Table	Defines the groups in which the customers can be catagorised.	1
fnSplitValues	User Defined Function		1
InvProduct	Table	Stores the product related details.	1
InvProductGroup	Table	Defined the various groups under which the products can be categorised.	1
InvUOM	Table	Master table for the Inv UOM	1
InvUOMGroupDetail	Table	Stores the details for the UOM group	1
LbrUser	Table	Defines a list of all employees/ labor users created in the system	1
PmtCurrency	Table	Define a list of all currecies defined in the system.	1
RtlStore	Table	Contains the details of Stores defined in the system.	1
RtlSubsidiary	Table	Defines all the Subsidiaries defined in the application	1
TrxTransaction	Table	The main table which defined the primary details concerned with every type of transaction.	1
TrxTransactionInstallmentDetail	Table	Defines the installments details of all those transactions which have a layaway plan attached to it.	1
TrxTransactionLayaway	Table	Store layway type of transactions.	1
TrxTransactionOrder	Table	Stores information about various order booked in the system.	1

Procedure Source Code

-- this procedure LayAway Reports are generated.

-- Case1 ( If @IsDelivery = 0 And @DispOverDueOnly = 1 )

-- Then LayAway Installment Report is generated at iVend.

-- Case2 (If @IsDelivery = 1)

-- Then LayAway Delivery Report is generated at iVend.

-- Else LayAway Statement Report is generated at iVend.

--##SUMMARY_END

CREATE PROCEDURE [dbo].[rpt_LayAway](

@FromDate varchar(8),

@ToDate varchar(8),

@ProductGroup varchar(4000),

@FromProduct varchar(40),

@ToProduct varchar(40),

@CustomerGroup varchar(4000),

@FromCustomer varchar(40),

@ToCustomer varchar(40),

@Store varchar(4000),

@Subsidiary varchar(4000),

@SalesPerson varchar(4000),

@DispOverDueOnly bit,

@IsDelivery bit,

@DeliveryFrom varchar(8),

@DeliveryTo varchar(8),

@IsStatement bit

)

Begin

-- /*

--Exec rpt_LayAway '', '', --FromDate, ToDate

-- '', --ProductGroup

-- 'SERIAL01', 'LAYAWAY01', --FromProduct , ToProduct

-- '', --CustomerGroup

-- '', '', --FromCustomer, ToCustomer

-- '', --Store

-- '', -- Subsidiary

-- '', --POS

-- '', --Till

-- '', --SalesPerson

-- 0, --Display Overdue Only

-- 0, --Is Delivery

-- '', --Delivery From Date

-- '', --Delivery To Date

-- 1 --Is Statement

-- */

-- Set NoCount On

--Declare @FromDate varchar(8)='', @ToDate varchar(8)='', @Subsidiary Nvarchar(200)='', @Store Nvarchar(200)='', @SalesPerson Nvarchar(200)='', @CustomerGroup Nvarchar(200)='', @FromCustomer Nvarchar(200)='',

-- @ToCustomer Nvarchar(200)='', @ProductGroup Nvarchar(200)='', @FromProduct Nvarchar(200)='', @ToProduct Nvarchar(200)='', @IsDelivery bit =1, @DispOverDueOnly Bit=0,

-- @DeliveryFrom varchar(8)='', @DeliveryTo varchar(8)='', @IsStatement bit=0 --select Space(11) AS A

Select StoreId, StoreDesc, TransactionType, TransactionId, SaleData.TransactionKey, Item, ItemDescription, CustGroup, CustGroupDesc, Cust, CustomerName, CustPhone,

SalesPersonKey, SalesPersonId, SalesPersonName, Created, OriginalDetailKey, LayawayPlanKey, InstallmentCount, BusinessDate, SaleData.Status, CultureInfo, ExchangeRate,

Subsidiary, SubsidiaryDescription, BaseCultureInfo, UOM, HasFulfillment, Quantity, UOMQuantity, OpenQuantity, FullfilledQuantity, Discount, Surcharge, Tax,

TotalAmount, DiscountEC, SurchargeEC, TaxEC, TotalAmountEC, Sum(T.PaidAmount) As PaidtillDate,

Space(11) As LastInstallementPaidOn, Convert(Numeric(10,2),0.00) As LastInstallmentAmount, 0 As BalanceAmount,

Space(11) As NextInstallementDueOn, Convert(numeric(10,2),0.00) As NextInstallmentAmount, Space(11) As LastInstallementDate

--, InstallmentDetailKey, SourceType, SourceKey, InstallmentNumber, InstallmentDate, Amount, PaidAmount, PaidDate, Balance,

INTO #Temp

From (

SELECT RtlStore.Id AS StoreId, RtlStore.Description AS StoreDesc, 'Layaway' AS TransactionType, Trxn.TransactionId, Trxn.TransactionKey,

Prod.Id AS Item, LayAway.Description AS ItemDescription, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc,

Cust.Id AS Cust, ISNULL(Cust.FirstName,'')+' '+ISNULL(Cust.MiddleName,'')+' '+ISNULL(Cust.LastName,'') AS CustomerName,

Cust.PhoneNumber AS CustPhone, ISNULL(LbrUser.Id,'') AS SalesPersonId, isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName, Trxn.Created, LayAway.OriginalDetailKey,

LayAway.Status, LayAway.LayawayPlanKey, LayAway.InstallmentCount, Trxn.UserKey AS SalesPersonKey, Trxn.BusinessDate,

CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='0') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END AS CultureInfo,

Trxn.ExchangeRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo,

ISNULL(UOM.Description,'') AS UOM, Layaway.HasFulfillment,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Quantity ELSE LayAway.Quantity END AS Quantity,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*Layaway.UOMQuantity ELSE Layaway.UOMQuantity END UOMQuantity, Layaway.OpenQuantity,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*Layaway.FullfilledQuantity ELSE Layaway.FullfilledQuantity END AS FullfilledQuantity,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)) END AS Discount,

-1 AS Surcharge, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Tax ELSE Layaway.Tax END AS Tax,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscount ELSE LayAway.TotalPostSaleDiscount END AS TotalAmount,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) END AS DiscountEC,

-1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*Layaway.TaxEC ELSE Layaway.TaxEC END AS TaxEC,

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscountEC ELSE LayAway.TotalPostSaleDiscountEC END AS TotalAmountEC

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionLayAway AS Layaway WITH (NOLOCK) ON Trxn.TransactionKey = LayAway.TransactionKey

LEFT JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) ON LayAway.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON LayAway.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON RtlStore.SubsidiaryKey = Sub.SubsidiaryKey

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (Layaway.Type=0 OR Layaway.Status=0 OR Layaway.Status=2)

AND CONVERT(DateTime, Trxn.BusinessDate,112) BETWEEN CASE WHEN @FromDate <> '' THEN @FromDate ELSE CONVERT(DateTime, Trxn.BusinessDate,112) END AND CASE WHEN @ToDate <> '' THEN @ToDate ELSE CONVERT(DateTime, Trxn.BusinessDate, 112) END --Date Filter --Date Filter

AND (ProdGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@ProductGroup)) OR ProdGrp.Id IN (CASE WHEN @ProductGroup='' THEN ProdGrp.Id END)) -------------Filter Change to avoid Charindex as like operator

AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

(Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END)) --Product Filter

AND (CustGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@CustomerGroup)) OR CustGrp.Id IN (CASE WHEN @CustomerGroup='' THEN CustGrp.Id END)) -------------Filter Change to avoid Charindex as like operator

AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

(Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END)) --Customer Filter

AND (LbrUser.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@SalesPerson)) OR LbrUser.Id IN (CASE WHEN @SalesPerson='' THEN LbrUser.Id END)) -------------Filter Change to avoid Charindex as like operator

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END)) -------------Filter Change to avoid Charindex as like operator

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

UNION ALL

Select RtlStore.Id AS StoreId, RtlStore.Description AS StoreDesc, 'Order' AS TransactionType, Trxn.TransactionId, Trxn.TransactionKey,

Prod.Id AS Item, TrxnOrder.Description AS ItemDescription, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc,

Cust.Id AS Cust, ISNULL(Cust.FirstName,'')+' '+ISNULL(Cust.MiddleName,'')+' '+ISNULL(Cust.LastName,'') AS CustomerName,

Cust.PhoneNumber AS CustPhone, ISNULL(LbrUser.Id,'') AS SalesPersonId, isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName,

Trxn.Created, TrxnOrder.OriginalDetailKey,

TrxnOrder.Status, '-1' AS LayawayPlanKey, '-1' AS InstallmentCount, Trxn.UserKey AS SalesPersonKey, Trxn.BusinessDate,

Trxn.ExchangeRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo,

ISNULL(UOM.Description,'') AS UOM, TrxnOrder.HasFulfillment,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.Quantity ELSE TrxnOrder.Quantity END AS Quantity,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.UOMQuantity ELSE TrxnOrder.UOMQuantity END UOMQuantity, TrxnOrder.OpenQuantity,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.FullfilledQuantity ELSE TrxnOrder.FullfilledQuantity END AS FullfilledQuantity,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*(ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0)) ELSE (ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0)) END AS Discount,

-1 AS Surcharge, CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.Tax ELSE TrxnOrder.Tax END AS Tax,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.TotalPostSaleDiscount ELSE TrxnOrder.TotalPostSaleDiscount END AS TotalAmount,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*(ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) END AS DiscountEC,

-1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.TaxEC ELSE TrxnOrder.TaxEC END AS TaxEC,

CASE WHEN (TrxnOrder.Type =0 AND TrxnOrder.Status =1) THEN -1*TrxnOrder.TotalPostSaleDiscountEC ELSE TrxnOrder.TotalPostSaleDiscountEC END AS TotalAmountEC

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionOrder AS TrxnOrder WITH (NOLOCK) ON Trxn.TransactionKey = TrxnOrder.TransactionKey

LEFT JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) ON TrxnOrder.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnOrder.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON RtlStore.SubsidiaryKey = Sub.SubsidiaryKey

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (TrxnOrder.Type=0 OR TrxnOrder.Status=0 OR TrxnOrder.Status=1)

AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime,@FromDate, 112) END AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @ToDate, 112) END --Date Filter

AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

(Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END)) --Product Filter

AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

(Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END)) --Customer Filter

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END)) -------------Filter Change to avoid Charindex as like operator

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

) AS SaleData

INNER JOIN TrxTransactionInstallmentDetail T WITH (NOLOCK) ON T.TransactionKey=SaleData.TransactionKey

Group By

StoreId, StoreDesc, TransactionType, TransactionId, SaleData.TransactionKey, Item, ItemDescription, CustGroup, CustGroupDesc, Cust, CustomerName, CustPhone,

SalesPersonKey, SalesPersonId, SalesPersonName, Created, OriginalDetailKey, LayawayPlanKey, InstallmentCount, BusinessDate, SaleData.Status, CultureInfo, ExchangeRate,

Subsidiary, SubsidiaryDescription, BaseCultureInfo, UOM, HasFulfillment, Quantity, UOMQuantity, OpenQuantity, FullfilledQuantity, Discount, Surcharge, Tax,

TotalAmount, DiscountEC, SurchargeEC, TaxEC, TotalAmountEC

--Select * From #Temp

--Last Paid Installment Detail

Update #temp

Set LastInstallementPaidOn = LastInst.InstallmentDate,

LastInstallmentAmount = LastInst.PaidAmount

From #temp As temp, (

Select TransactionKey, Convert(Varchar, InstallmentDate, 106) As InstallmentDate, PaidAmount

From TrxTransactionInstallmentDetail

Where InstallmentDetailKey In(

Select InstallmentDetailKey From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And Inst.PaidAmount > 0 AND InstallmentNumber =(Select Max(InstallmentNumber) As InstallmentDetailKey

From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And Inst.PaidAmount > 0

Group By temp.TransactionKey )

)

) As LastInst

Where temp.TransactionKey = LastInst.TransactionKey

--Next Installment Detail

Update #temp

Set NextInstallementDueOn = NextInst.InstallmentDate,

NextInstallmentAmount = NextInst.Amount

From #temp As temp, (

Select TransactionKey, Convert(Varchar, InstallmentDate, 106) As InstallmentDate, Amount

From TrxTransactionInstallmentDetail

Where InstallmentDetailKey In(

Select InstallmentDetailKey From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And Inst.PaidAmount = 0 AND InstallmentNumber =(Select Min(InstallmentNumber) As InstallmentDetailKey

From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And Inst.PaidAmount = 0

Group By temp.TransactionKey ))

) As NextInst

Where temp.TransactionKey = NextInst.TransactionKey

--Drop Table #Temp

--Last Installment Date (To be Paid)

Update #temp

Set LastInstallementDate = LastInst.InstallmentDate,

BalanceAmount = LastInst.Balance

From #temp As temp, (

Select TransactionKey, Convert(Varchar, InstallmentDate, 106) As InstallmentDate, Balance

From TrxTransactionInstallmentDetail

Where InstallmentDetailKey In( Select InstallmentDetailKey

From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And temp.Status = 0 AND InstallmentNumber=(

Select Max(InstallmentNumber) As InstallmentDetailKey

From #temp As temp, TrxTransactionInstallmentDetail As Inst

Where temp.TransactionKey = Inst.TransactionKey

And temp.Status = 0

Group By temp.TransactionKey

) )

) As LastInst

Where temp.TransactionKey = LastInst.TransactionKey

--LayAway Installment Report

If @IsDelivery = 0

Begin

If @DispOverDueOnly = 1

Begin

Delete From #temp

Where LTrim(RTrim(NextInstallementDueOn)) = ''

Or Convert(Varchar, Cast(NextInstallementDueOn As Datetime), 112) > Convert(Varchar, dbo.GetCompanyDateTime(), 112)

End

--LayAway Delivery Report

If @IsDelivery = 1

Begin

Delete From #temp

Where LTrim(RTrim(LastInstallementDate)) = ''

--Or Convert(Varchar, Cast(LastInstallementDate As Datetime), 112) Not Between @DeliveryFrom And @DeliveryTo

Or Convert(Varchar, Cast(LastInstallementDate As Datetime), 112) Not Between Case When @DeliveryFrom = '' then Convert(Varchar, Cast(LastInstallementDate As Datetime), 112) else @DeliveryFrom End

And

Case When @DeliveryTo = '' then Convert(Varchar, Cast(LastInstallementDate As Datetime), 112) else @DeliveryTo End

End

Select temp.*, LayawayPlan.Id, LayawayPlan.Description, Case LayawayPlan.DurationType When 0 then 'Weekly'

When 1 then 'Monthly'

When 2 then 'Quarterly'

When 3 then 'Halfyearly'

When 4 then 'Yearly'

End As Mode,

Inst.InstallmentDate As DPInstDate, Inst.PaidAmount As DPAmount

From #temp As temp, PmtLayawayPlan As LayawayPlan, TrxTransactionInstallmentDetail As Inst

Where temp.LayAwayPlanKey = LayawayPlan.LayawayPlanKey

And temp.TransactionKey = Inst.TransactionKey

And Inst.InstallmentNumber = 0

--Drop Table #Temp

Set NoCount Off

End

Related Objects

iVend Database Database

Send comments on this topic.