Hi, Im having a problem where my total amount financial is not tally to my AX report amountfin result. Im using INVENTRANS Table as my main table and have 3 other tables linked to it together. So let me ask how is this possible? Please help? This is my code..
SELECT
IG.ITEMGROUPID,
ITS.STATUSRECEIPT,
ITS.STATUSISSUE,
ITS.ITEMID,
EC.DESCRIPTION,
IT.BOMUNITID,
ITS.DATEPHYSICAL,
ID.INVENTSITEID,
ID.INVENTLOCATIONID,
ID.INVENTBATCHID,
CASE
WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT ='2'
THEN ITS.QTY
ELSE '0'
END AS 'PHYSICAL QTY',
CASE
WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT = '2'
THEN (ITS.COSTAMOUNTPHYSICAL / ITS.QTY)
ELSE '0'
END AS 'UNIT COST PHYSICAL',
CASE
WHEN ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2'
THEN ITS.COSTAMOUNTPHYSICAL
ELSE '0'
END AS 'AMOUNT PHYSICAL',
CASE
WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT ='2'
THEN 0
ELSE ITS.QTY
END AS 'FINANCIAL QTY',
((ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT)/ITS.QTY)AS 'UNIT COST',
CASE
WHEN ITS.STATUSISSUE = '1' or ITS.STATUSISSUE= '0'
THEN ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT
ELSE '0'
END AS 'AMOUNT FINANCIAL',
ITS.QTY AS 'TOTAL QTY',
CASE
WHEN ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2'
THEN ITS.COSTAMOUNTPHYSICAL + ITS.COSTAMOUNTADJUSTMENT
WHEN ITS.STATUSISSUE = '1' OR ITS.STATUSISSUE ='0'
THEN ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT
ELSE '0'
END AS 'TOTAL AMOUNT',
ITS.COSTAMOUNTADJUSTMENT AS 'AMOUNT ADJUSTMENT'
FROM MicrosoftDynamicsAX.dbo.INVENTTRANS ITS
LEFT JOIN MicrosoftDynamicsAX.dbo.INVENTTABLE IT ON ITS.ITEMID = IT.ITEMID AND ITS.DATAAREAID = IT.DATAAREAID
LEFT JOIN MicrosoftDynamicsAX.dbo.INVENTITEMGROUPITEM IG ON IT.ITEMID = IG.ITEMID AND ITS.DATAAREAID = IG.ITEMGROUPDATAAREAID
LEFT JOIN MicrosoftDynamicsAX.dbo.ECORESPRODUCTTRANSLATION EC ON IT.PRODUCT = EC.PRODUCT
LEFT JOIN MicrosoftDynamicsAX.dbo.INVENTDIM ID ON ITS.INVENTDIMID = ID.INVENTDIMID AND ITS.DATAAREAID = ID.DATAAREAID
WHERE ITS.DATAAREAID = @CMPNY AND ITS.STATUSISSUE <=2 AND ITS.STATUSRECEIPT <=2 AND (ITS.DATEPHYSICAL<= @DATE) and IG.ITEMGROUPID NOT LIKE 'H%' -
ORDER BY
IG.ITEMID
SELECT IG.ITEMGROUPID,ITS.STATUSRECEIPT,ITS.STATUSISSUE,ITS.ITEMID,EC.DESCRIPTION,IT.BOMUNITID,ITS.DATEPHYSICAL,ID.INVENTSITEID,ID.INVENTLOCATIONID,ID.INVENTBATCHID,
CASE WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT ='2' THEN ITS.QTYELSE '0'END AS 'PHYSICAL QTY',
CASE WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT = '2' THEN (ITS.COSTAMOUNTPHYSICAL / ITS.QTY)ELSE '0' END AS 'UNIT COST PHYSICAL',
CASE WHEN ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2' THEN ITS.COSTAMOUNTPHYSICAL ELSE '0'END AS 'AMOUNT PHYSICAL',
--CASE --WHEN ITS.STATUSISSUE = '0' OR ITS.STATUSISSUE ='1' AND ITS.STATUSISSUE = '1' AND ITS.STATUSRECEIPT = '0' --OR ITS.STATUSRECEIPT = '1' --OR ITS.STATUSRECEIPT = '2' --THEN ITS.QTY--ELSE '0'--END AS 'FINANCIAL QTY',CASE WHEN ITS.STATUSISSUE = '2' OR ITS.STATUSRECEIPT ='2' THEN 0ELSE ITS.QTYEND AS 'FINANCIAL QTY',
--CASE --WHEN ITS.STATUSISSUE = '0' OR ITS.STATUSISSUE = '1' OR ITS.STATUSRECEIPT= '0' OR ITS.STATUSRECEIPT= '1' --THEN (ITS.COSTAMOUNTPOSTED/ITS.QTY)--ELSE '0'--END AS 'UNIT COST',
((ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT)/ITS.QTY)AS 'UNIT COST',--ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT AS 'AMOUNT FINANCIAL',
CASE WHEN ITS.STATUSISSUE = '1' or ITS.STATUSISSUE= '0' --OR ITS.STATUSRECEIPT ='1' OR ITS.STATUSRECEIPT ='0'THEN ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENTELSE '0'END AS 'AMOUNT FINANCIAL',
ITS.QTY AS 'TOTAL QTY',--((ITS.COSTAMOUNTPHYSICAL) - ITS.COSTAMOUNTPOSTED) AS 'TOTAL AMOUNT',CASE WHEN ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2' THEN ITS.COSTAMOUNTPHYSICAL + ITS.COSTAMOUNTADJUSTMENTWHEN ITS.STATUSISSUE = '1' OR ITS.STATUSISSUE ='0' --OR ITS.STATUSRECEIPT ='0' --OR ITS.STATUSISSUE = '1' AND ITS.STATUSRECEIPT= '0' OR ITS.STATUSRECEIPT= '1'THEN ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENTELSE '0'END AS 'TOTAL AMOUNT',--CASE --WHEN ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2' --THEN ITS.COSTAMOUNTPHYSICAL + ITS.COSTAMOUNTADJUSTMENT--WHEN ITS.STATUSISSUE = '0' OR ITS.STATUSISSUE = '1' AND ITS.STATUSRECEIPT= '0' OR ITS.STATUSRECEIPT= '1'--THEN ITS.COSTAMOUNTPOSTED + ITS.COSTAMOUNTADJUSTMENT--ELSE '0'--END AS 'TOTAL AMOUNT',
--CASE --WHEN ITS.STATUSISSUE = '0' OR ITS.STATUSISSUE = '1' OR ITS.STATUSISSUE = '2' OR STATUSRECEIPT = '2' --THEN (ITS.COSTAMOUNTPHYSICAL + ITS.COSTAMOUNTPOSTED)--ELSE '0'--END AS 'TOTAL AMOUNT',
ITS.COSTAMOUNTADJUSTMENT AS 'AMOUNT ADJUSTMENT'
FROM MicrosoftDynamicsAX.dbo.INVENTTRANS ITS LEFT JOIN MicrosoftDynamicsAX.dbo.INVENTTABLE IT ON ITS.ITEMID = IT.ITEMID AND ITS.DATAAREAID = IT.DATAAREAIDLEFT JOIN MicrosoftDynamicsAX.dbo.INVENTITEMGROUPITEM IG ON IT.ITEMID = IG.ITEMID AND ITS.DATAAREAID = IG.ITEMGROUPDATAAREAIDLEFT JOIN MicrosoftDynamicsAX.dbo.ECORESPRODUCTTRANSLATION EC ON IT.PRODUCT = EC.PRODUCT
LEFT JOIN MicrosoftDynamicsAX.dbo.INVENTDIM ID ON ITS.INVENTDIMID = ID.INVENTDIMID AND ITS.DATAAREAID = ID.DATAAREAID
WHERE ITS.DATAAREAID = @CMPNY AND ITS.STATUSISSUE <=2 AND ITS.STATUSRECEIPT <=2 AND (ITS.DATEPHYSICAL<= @DATE) and IG.ITEMGROUPID NOT LIKE 'H%' --AND ITS.ITEMID = 'FMBC01022' AND ID.INVENTBATCHID = '411807101-07' --and ITS.QTY <>0 --AND ITS.ITEMID = 'GXOTH0009' -- AND ID.INVENTBATCHID = 'KI-P7-3/AUGUST2017' AND ITS.ITEMID = 'GFGAE012025'
