Dynamics Community Forum Thread Details

Add RM20101 to query

Is it possible to have someone help me add RM20101 to this query?

I specifically need CURTRXAM

SELECT * FROM

(

SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM20201

UNION ALL

SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM30201

) AS RM

LEFT OUTER JOIN

(

SELECT

Trx_Status,

TRXDATE Trx_Date,

JRNENTRY Journal_Entry,

ACTNUMST Account_Number,

ACTDESCR Account_Description,

DEBITAMT Debit_Amount,

CRDTAMNT Credit_Amount,

REFRENCE Reference,

SOURCDOC Source_Document,

ORTRXSRC Originating_TRX_Source,

ORMSTRID Originating_Master_ID,

ORMSTRNM Originating_Master_Name,

ORDOCNUM Originating_Doc_Number,

CURNCYID Currency_ID,

LASTUSER Last_User,

USWHPSTD User_Who_Posted

FROM

(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,

ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,

Trx_Status = 'Open', LASTUSER, USWHPSTD

FROM GL20000

WHERE SOURCDOC not in ('BBF','P/L')

AND VOIDED = 0

UNION ALL

SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,

ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,

Trx_Status = 'History', LASTUSER, USWHPSTD

FROM GL30000

WHERE SOURCDOC not in ('BBF','P/L')

AND VOIDED = 0

UNION ALL

SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY,

GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM,

GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work',

LASTUSER, USWHPSTD

FROM GL10000 GH

INNER JOIN GL10001 GD

ON GH.JRNENTRY = GD.JRNENTRY

WHERE VOIDED = 0) GL

INNER JOIN GL00105 GM

ON GL.ACTINDX = GM.ACTINDX

INNER JOIN GL00100 GA

ON GL.ACTINDX = GA.ACTINDX

) AS GL

ON RM.APFRDCNM= GL.Originating_Doc_Number

I don't see this as an applicable join, since RM20101 contains RM open documents. This join will result with "Null" Values, as the history documents are not joined.

Although, regardless of the logic behind this, to join this find the script below;

SELECT * FROM
(
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM20201
UNION ALL
SELECT DATE1,APTODCNM,APPTOAMT,APFRDCNM, APFRDCDT,APTODCDT,TRXSORCE FROM RM30201

) AS RM
LEFT OUTER JOIN
(
SELECT DOCNUMBR,CUSTNMBR ,ORTRXAMT, CURTRXAM FROM RM20101
)AS RMO
ON RM.APTODCNM = RMO.DOCNUMBR
LEFT OUTER JOIN
(
SELECT
Trx_Status,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
REFRENCE Reference,
SOURCDOC Source_Document,
ORTRXSRC Originating_TRX_Source,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
CURNCYID Currency_ID,
LASTUSER Last_User,
USWHPSTD User_Who_Posted
FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'Open', LASTUSER, USWHPSTD
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'History', LASTUSER, USWHPSTD
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')
AND VOIDED = 0
UNION ALL
SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY,
GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM,
GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work',
LASTUSER, USWHPSTD
FROM GL10000 GH
INNER JOIN GL10001 GD
ON GH.JRNENTRY = GD.JRNENTRY
WHERE VOIDED = 0) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
) AS GL
ON RM.APFRDCNM= GL.Originating_Doc_Number

Quick Links

Leaderboard