Skip to content

Instantly share code, notes, and snippets.

@smarenich smarenich/cv_Rates.cs
Last active Oct 18, 2017

Embed
What would you like to do?
using System;
using PX.Data;
namespace PX.Objects.CM
{
[Serializable]
public class cv_Rates : IBqlTable
{
#region TenantID
[PXDBInt(IsKey = true)]
[PXUIField(DisplayName = "Tenant ID")]
public int? TenantID { get; set; }
public class tenantID : IBqlField { }
#endregion
#region FromCuryID
[PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "From Cury ID")]
public string FromCuryID { get; set; }
public class fromCuryID : IBqlField { }
#endregion
#region ToCuryID
[PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "To Cury ID")]
public string ToCuryID { get; set; }
public class toCuryID : IBqlField { }
#endregion
#region CuryEffDate
[PXDBDate(IsKey = true)]
[PXUIField(DisplayName = "Cury Eff Date")]
public DateTime? CuryEffDate { get; set; }
public class curyEffDate : IBqlField { }
#endregion
#region RateType
[PXDBString()]
[PXUIField(DisplayName = "Rate Type")]
public int? RateType { get; set; }
public class rateType : IBqlField { }
#endregion
#region RateID
[PXDBInt()]
[PXUIField(DisplayName = "Rate ID")]
public int? RateID { get; set; }
public class rateID : IBqlField { }
#endregion
}
}
IF OBJECT_ID ('dbo.[cv_Rates]', 'V') IS NOT NULL
DROP VIEW [dbo].[cv_Rates]
GO
CREATE VIEW [dbo].[cv_Rates] AS
select FromCury.CompanyID as CompanyID, FromCury.CuryID as FromCuryID, ToCury.CuryID as ToCuryID, Date as CuryEffDate, 'SPOT' as RateType,
Coalesce((Select top 1 CuryRateID
from CurrencyRate r
where r.CuryEffDate <= Date and r.CompanyID = FromCury.CompanyID and r.FromCuryID = FromCury.CuryID and r.ToCuryID = ToCury.CuryID and r.CuryRateType = 'SPOT'
order by CuryEffDate DESC), 0) as RateID
from (SELECT DATEADD(DAY, nbr - 1, '20110901') as Date
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, '20160101', '20250101')) Dates
Cross Join Currency FromCury
Left Join Currency ToCury on FromCury.CompanyID = ToCury.CompanyID and FromCury.CuryID <> ToCury.CuryID
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.