RELATION BETWEEN
LEDGERDIMENSION AND DIMENSIONATTRIBUTEVALUECOMBINATION AX 2012 USING SQL :
SELECT A.JOURNALID,C.[DESCRIPTION],
A.VOUCHER,
A.ITEMID,
A.TRANSDATE,
A.INVENTONHAND ,A.QTY,
B.INVENTLOCATIONID, B.WMSLOCATIONID,
(CASE WHEN
C.POSTED = 1 THEN 'YES'
WHEN C.POSTED = 0 THEN 'NO'
END) AS POSTED,
D.PRODUCTNAME,
(CASE WHEN A.JOURNALTYPE = 0 THEN 'MOVEMENT'
WHEN A.JOURNALTYPE = 2 THEN 'TRANSFER'
WHEN A.JOURNALTYPE = 3 THEN 'BOM'
WHEN A.JOURNALTYPE = 4 THEN 'COUNTING'
END) TYP,
B.INVENTSITEID,
E.DISPLAYVALUE
AS DEPT,
A.LEDGERDIMENSION,
(CASE WHEN
A.JOURNALTYPE = 3 THEN '1133-1001--'
ELSE
F.DISPLAYVALUE
END) AS ACCOUNT,
(SELECT TOP 1
Y.COSTAMOUNTPHYSICAL / Y.QTY FROM [MDXABU].[DBO].[INVENTTRANSORIGIN] AS X WITH
(NOLOCK)
INNER JOIN [MDXABU].[DBO].[INVENTTRANS] AS
Y WITH (NOLOCK)
ON Y.INVENTTRANSORIGIN = X.RECID
WHERE
X.INVENTTRANSID = A.INVENTTRANSID) AS COSTPRICE,
(SELECT TOP 1
Y.COSTAMOUNTPHYSICAL FROM
[MDXABU].[DBO].[INVENTTRANSORIGIN] AS X WITH (NOLOCK)
INNER JOIN [MDXABU].[DBO].[INVENTTRANS] AS
Y WITH (NOLOCK)
ON Y.INVENTTRANSORIGIN = X.RECID
WHERE
X.INVENTTRANSID = A.INVENTTRANSID) AS COSTAMOUNT
INTO #TEMPJOURNAL
FROM
[MDXABU].[DBO].INVENTJOURNALTRANS AS A WITH (NOLOCK)
INNER JOIN [MDXABU].[DBO].INVENTDIM AS B WITH (NOLOCK)
ON B.INVENTDIMID = A.INVENTDIMID
INNER JOIN [MDXABU].[DBO].INVENTJOURNALTABLE AS C WITH
(NOLOCK)
ON C.JOURNALID = A.JOURNALID
INNER JOIN [MDXABU].[DBO].ECORESPRODUCTTRANSLATIONS AS D WITH
(NOLOCK)
ON D.DISPLAYPRODUCTNUMBER = A.ITEMID
LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUESETITEMVIEW]
AS E WITH (NOLOCK)
ON E.DIMENSIONATTRIBUTEVALUESET = A.DEFAULTDIMENSION
AND E.DIMENSIONATTRIBUTE = 5637146078
LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUECOMBINATION]
AS F WITH (NOLOCK)
ON F.RECID = A.LEDGERDIMENSION
INNER JOIN [MDXABU].[DBO].[INVENTJOURNALTABLE] AS G WITH
(NOLOCK)
ON G.JOURNALID = A.JOURNALID
WHERE
CAST(G.POSTEDDATETIME AS DATE) >= @FROMDATE
AND
CAST(G.POSTEDDATETIME AS DATE) <= @TODATE
AND A.ITEMID LIKE 'P%'
SELECT B.NAME AS
COANAME, * FROM #TEMPJOURNAL AS A WITH (NOLOCK)
LEFT JOIN [MDXABU].[DBO].[MAINACCOUNT] AS B WITH (NOLOCK)
ON B.MAINACCOUNTID = A.ACCOUNT
-----------------------------------------------------------------------------------------
SELECT A.INVOICEID,
A.SALESID,
A.INVOICEACCOUNT,
A.INVOICINGNAME,
A.INVOICEDATE,
F.INVENTSITEID ,
F.INVENTLOCATION AS
INVENTLOCATIONID,
E.ITEMGROUPID,
A.INVOICEAMOUNT,
B.ITEMID,
C.PRODUCTNAME,
B.QTY,
B.SALESPRICE,
B.LINEAMOUNTTAX,
B.LINEAMOUNT,
(SELECT (SUM(RA.COSTAMOUNTADJUSTMENT)
+ SUM(RA.COSTAMOUNTPOSTED)) FROM [MDXABU].[DBO].INVENTTRANS AS RA WITH (NOLOCK)
INNER JOIN [MDXABU].[DBO].INVENTTRANSORIGIN
AS RB WITH (NOLOCK)
ON RA.INVENTTRANSORIGIN =
RB.RECID
WHERE RB.INVENTTRANSID = B.INVENTTRANSID
) AS COGS,
G.ABUITEMGROUPID AS
SUBCATEGORY
FROM [MDXABU].[DBO].CUSTINVOICEJOUR
AS A WITH (NOLOCK)
INNER JOIN [MDXABU].[DBO].CUSTINVOICETRANS
AS B WITH (NOLOCK)
ON B.INVOICEID = A.INVOICEID
LEFT JOIN [MDXABU].[DBO].ECORESPRODUCTTRANSLATIONS
AS C WITH (NOLOCK)
ON C.DISPLAYPRODUCTNUMBER
= B.ITEMID
LEFT JOIN [MDXABU].[DBO].INVENTDIM AS D WITH (NOLOCK)
ON D.INVENTDIMID = B.INVENTDIMID
AND D.DATAAREAID = A.DATAAREAID
LEFT JOIN [MDXABU].[DBO].INVENTITEMGROUPITEM
AS E WITH (NOLOCK)
ON E.ITEMID = B.ITEMID
INNER JOIN [MDXABU].[DBO].CUSTTABLE AS F WITH (NOLOCK)
ON F.ACCOUNTNUM = A.INVOICEACCOUNT
AND F.DATAAREAID = A.DATAAREAID
INNER JOIN [MDXABU].[DBO].[ABUITEMGROUP]
AS G WITH (NOLOCK)
ON G.ITEMID = B.ITEMID
AND G.ABUITEMGROUPID in (@CATEGORY)
LEFT JOIN
[MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUESETITEMVIEW]
AS H WITH (NOLOCK)
ON H.DIMENSIONATTRIBUTEVALUESET =
B.DEFAULTDIMENSION
AND H.DIMENSIONATTRIBUTE =
5637146077
AND H.DISPLAYVALUE in (@FINDIMBRANCHES)
LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUECOMBINATION] AS I WITH (NOLOCK)
ON I.RECID = B.LEDGERDIMENSION
WHERE A.DATAAREAID = 'ABU'
AND A.INVOICEDATE >= @FROMDATE
AND A.INVOICEDATE <= @TODATE
Facebook
0 comments:
Post a Comment