RFQ, AUOM, NumberOfPieces

Kinetic ERP
1

I’d like to print Pieces on the RFQ Report for Attribute Tracked Parts.

Has anyone figured out calculating NumberOfPieces from Qty/UOM for an attribute enabled part? Meaning from base UOM, say LB, through AttributeSet to get dimensions then through AUOM conversion to Pieces.

PORel and SORel store this calculated value so it’s easy to put the Pieces on those reports:

image
image759×162 18.5 KB

image
image1288×858 36.8 KB

I suppose the task is to make a BAQ so I know the relationships and then try to add all that to RDD? Am I on the right track?

Can I add a BAQ data source to an existing RDD. Maybe a BAQ report would be better?

Thanks in advance and have a nice weekend.

1 Like
2

Pretty sure you can NOT add a BAQ to an existing table-based RDD.

2 Likes
3

Oh it’s coming back to me now.

Relationships anyway. RDD is another beast for this newb.

1 Like
4

Progress. This works for one DynAttrClasses. Not yet sure how to avoid the magic strings and make it universal. Swear I saw a PiecesConversionFactor somewhere but don’t recall.

WITH [DynAttrCTE] AS ( 
    SELECT  
        [DynAttrValueSet].[Company] AS [Company], 
        [DynAttrValueSet].[AttributeSetID] AS [AttributeSetID], 
        [DynAttrValueSet].[Description] AS [DynAttrValueSet_Description], 
        [DynAttrValueSet].[ShortDescription] AS [DynAttrValueSet_ShortDescription], 
        [DynAttrValueSet].[AttrClassID] AS [DynAttrValueSet_AttrClassID], 
        [DynAttrValueSet].[Active] AS [DynAttrValueSet_Active], 
        [DynAttrValueSet].[ExpressionResultUOM] AS [DynAttrValueSet_ExpressionResultUOM], 
        [DynAttrValueSet].[FullExpression] AS [DynAttrValueSet_FullExpression], 
        [DynAttrValueSet].[ExpressionResultQty] AS [DynAttrValueSet_ExpressionResultQty], 
        [DynAttrValueSet].[HasBeenUsed] AS [DynAttrValueSet_HasBeenUsed], 
        [DynAttrClassDtl].[PlanningBaseUOM] AS [DynAttrClassDtl_PlanningBaseUOM], 
        [DynAttrValue].[AttributeID] AS [DynAttrValue_AttributeID], 
        [DynAttrValue].[DataCharacter] AS [DynAttrValue_DataCharacter], 
        [DynAttrValue].[DataDate] AS [DynAttrValue_DataDate], 
        [DynAttrValue].[DataDecimal] AS [DynAttrValue_DataDecimal], 
        [DynAttrValue].[DataInteger] AS [DynAttrValue_DataInteger], 
        [DynAttrValue].[DataLogical] AS [DynAttrValue_DataLogical], 
        [DynAttrValue].[FieldDataType] AS [DynAttrValue_FieldDataType], 
        [DynAttrValue].[IsCalculated] AS [DynAttrValue_IsCalculated], 
        [DynAttrValue].[IsActual] AS [DynAttrValue_IsActual] 

    FROM Erp.DynAttrValueSet AS [DynAttrValueSet]
    INNER JOIN Erp.DynAttrValue AS [DynAttrValue] ON 
        DynAttrValueSet.Company = DynAttrValue.Company
            AND  DynAttrValueSet.SysRowID = DynAttrValue.RelatedToSysRowID
            AND ( (DynAttrValue.AttributeID = 'WEIGHT' ) )
    INNER JOIN Erp.DynAttrClassDtl AS [DynAttrClassDtl] ON 
        DynAttrValue.Company = DynAttrClassDtl.Company
            AND  DynAttrValue.AttrClassID = DynAttrClassDtl.AttrClassID
            AND  DynAttrValue.AttributeID = DynAttrClassDtl.AttributeID
    INNER JOIN Erp.DynAttrClass AS [DynAttrClass] ON 
            DynAttrClassDtl.Company = DynAttrClass.Company
            AND  DynAttrClassDtl.AttrClassID = DynAttrClass.AttrClassID
)

SELECT  
	[RFQItem].[Company] AS [RFQItem_Company], 
	[RFQItem].[OpenItem] AS [RFQItem_OpenItem], 
	[RFQItem].[RFQNum] AS [RFQItem_RFQNum], 
	[RFQItem].[RFQLine] AS [RFQItem_RFQLine], 
	[RFQItem].[LineDesc] AS [RFQItem_LineDesc], 
	[RFQItem].[PartNum] AS [RFQItem_PartNum], 
	[RFQItem].[AttributeSetID] AS [RFQItem_AttributeSetID], 
	[RFQItem].[IUM] AS [RFQItem_IUM], 
	[RFQItem].[UOMClassID] AS [RFQItem_UOMClassID], 
	[RFQItem].[PUM] AS [RFQItem_PUM], 
	[DynAttrCTE].[DynAttrValueSet_Description] AS [DynAttrValueSet_Description], 
	[DynAttrCTE].[DynAttrValueSet_ShortDescription] AS [DynAttrValueSet_ShortDescription], 
	[DynAttrCTE].[DynAttrValueSet_AttrClassID] AS [DynAttrValueSet_AttrClassID], 
	[DynAttrCTE].[DynAttrClassDtl_PlanningBaseUOM] AS [DynAttrClassDtl_PlanningBaseUOM], 
	[DynAttrCTE].[DynAttrValue_AttributeID] AS [DynAttrValue_AttributeID], 
	[DynAttrCTE].[DynAttrValue_DataDecimal] AS [DynAttrValue_DataDecimal], 
	[DynAttrCTE].[DynAttrValue_IsCalculated] AS [DynAttrValue_IsCalculated], 
	[DynAttrCTE].[DynAttrValue_IsActual] AS [DynAttrValue_IsActual], 
	(RFQQty.Quantity / DynAttrCTE.DynAttrValue_DataDecimal) AS [Calculated_NumberOfPieces] 

FROM Erp.RFQItem AS [RFQItem]
INNER JOIN Erp.RFQQty AS [RFQQty] ON 
	  RFQItem.Company = RFQQty.Company
        AND  RFQItem.RFQNum = RFQQty.RFQNum
        AND  RFQItem.RFQLine = RFQQty.RFQLine
INNER JOIN  DynAttrCTE  AS [DynAttrCTE] ON 
	  RFQItem.Company = DynAttrCTE.Company
        AND  RFQItem.AttributesetID = DynAttrCTE.AttributeSetID
        AND  RFQItem.PUM = DynAttrCTE.DynAttrClassDtl_PlanningBaseUOM
1 Like