I am trying to create a projection of the Service Order Detail joined to the Appointment detail.
This is the SQL statement I am trying to emulate:
SELECT FD.[SrvOrdType] ,FD.[RefNbr]
,FD.[OrigSrvOrdNbr],FD.[OrigLineNbr]
,FD.[UnitCost],FD.[UnitPrice]
,FS.[RefNbr] ,FS.[LineNbr]
,FS.[LineRef],FS.[UnitCost]
,FS.[UnitPrice],FS.[TranDesc]
,FS.[SODetID],FS.[SOID]
From [FSAppointmentDet] FD
JOIN [FSSODet] FS on FD.OrigSrvOrdNbr = FS.RefNbr
AND FS.LineNbr = FD.OrigLineNbr
WHERE fs.UnitPrice <> FD.UnitPrice
And this is the Projection I am trying to get together, but there are syntax errors here I can’t seem to trace down:
[PXProjection(typeof(Select2<FSAppointmentDet,
InnerJoin<FSSODet,
On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>,
Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]
I seem to always get lost somehow in the brackets… what am I doing wrong here?
This is the full DAC in case it matters:
using PX.Data;
using PX.Data.BQL;
using PX.Data.BQL.Fluent;
using PX.Objects.AR;
using PX.Objects.CS;
using PX.Objects.FS;
using PX.Objects.GL.FinPeriods.TableDefinition;
using PX.Objects.IN;
using PX.Objects.IN.S;
using PX.Objects.SO;
namespace MyOwn.DAC
{
/***************************************************************************
* This is a projection set up to allow the Process Screen to be run to
* fix the error we are getting in the FSSODET Unit Price.
****************************************************************************/
[PXProjection(typeof(Select2<FSAppointmentDet,
InnerJoin<FSSODet,
On<FSSODet.refNbr, Equal<FSAppointmentDet.origSrvOrdNbr>,
And<FSSODet.lineNbr, Equal<FSAppointmentDet.origLineNbr>>>,
Where<FSSODet.unitPrice, NotEqual<FSAppointmentDet.unitPrice>>>), Persistent = true)]
[PXCacheName("FixPriceProjection")]
public class PlyFixPriceProjDAC : IBqlTable
{
#region From the FSAppointmentDet DAC
#region FDRefNbr
[PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.refNbr))]
public virtual string FDRefNbr { get; set; }
public abstract class fDRefNbr : PX.Data.BQL.BqlString.Field<fDRefNbr> { }
#endregion
#region OrigSrvOrdNbr
[PXDBString(20, IsUnicode = true, BqlField = typeof(FSAppointmentDet.origSrvOrdNbr))]
public virtual string OrigSrvOrdNbr { get; set; }
public abstract class origSrvOrdNbr : PX.Data.BQL.BqlString.Field<origSrvOrdNbr> { }
#endregion
#region OrigLineNbr
[PXDBInt(BqlField = typeof(FSAppointmentDet.origLineNbr))]
public virtual int? OrigLineNbr { get; set; }
public abstract class origLineNbr : PX.Data.BQL.BqlInt.Field<origLineNbr> { }
#endregion
#region FDUnitCost
[PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitCost))]
public virtual decimal? FDUnitCost { get; set; }
public abstract class fDUnitCost : PX.Data.BQL.BqlDecimal.Field<fDUnitCost> { }
#endregion
#region FDUnitPrice
[PXDBDecimal(4, BqlField = typeof(FSAppointmentDet.unitPrice))]
public virtual decimal? FDUnitPrice { get; set; }
public abstract class fDUnitPrice : PX.Data.BQL.BqlDecimal.Field<fDUnitPrice> { }
#endregion
#endregion
#region From the FSSODet DAC
#region FSRefNbr
[PXDBString(20, IsUnicode = true, BqlField = typeof(FSSODet.refNbr))]
public virtual string FSRefNbr { get; set; }
public abstract class fSRefNbr : PX.Data.BQL.BqlString.Field<fSRefNbr> { }
#endregion
#region FSLineNbr
[PXDBInt(BqlField = typeof(FSSODet.lineNbr))]
public virtual int? FSLineNbr { get; set; }
public abstract class fSLineNbr : PX.Data.BQL.BqlInt.Field<fSLineNbr> { }
#endregion
#region FSLineRef
[PXDBString(30, IsUnicode = true, BqlField = typeof(FSSODet.lineRef))]
public virtual string FSLineRef { get; set; }
public abstract class fSLineRef : PX.Data.BQL.BqlString.Field<fSLineRef> { }
#endregion
#region FSUnitCost
[PXDBDecimal(4, BqlField = typeof(FSSODet.unitCost))]
public virtual decimal? FSUnitCost { get; set; }
public abstract class fSUnitCost : PX.Data.BQL.BqlDecimal.Field<fSUnitCost> { }
#endregion
#region FSUnitPrice
[PXDBDecimal(4, BqlField = typeof(FSSODet.unitPrice))]
public virtual decimal? FSUnitPrice { get; set; }
public abstract class fSUnitPrice : PX.Data.BQL.BqlDecimal.Field<fSUnitPrice> { }
#endregion
#region TranDesc
[PXDBString(255, IsUnicode = true, BqlField = typeof(FSSODet.tranDesc))]
public virtual string TranDesc { get; set; }
public abstract class tranDesc : PX.Data.BQL.BqlString.Field<tranDesc> { }
#endregion
#endregion
}
}