Share
ReportHi there, I got this Query that I made a while ago. It was working well until someone asked to add the fulfillment order. So now I need to find a way to add the fulfillment order (The SOP10100 table) but with the same column then the present Query so maybe a other Union? How do you think will be the best?.
Thanks\ Here's the Query
SELECT * FROM ( SELECT CASE RM20101.RMDTYPAL WHEN 0 THEN 'Reserved for balance carried forward records' WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Reserved for scheduled payments' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service / Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS ClientTransactionType ,RM20101.DOCNUMBR as ClientTransactionNumber ,RM20101.DOCDATE as ClientTransactionDate ,RM20101.CUSTNMBR as ClientCode ,RM00101.CUSTNAME as ClientName ,RM00101.SLPRSNID as ClientSalesperson ,RM20101.TRXDSCRN as ClientTransactionAuxiliaryNumber ,CASE SOP30200.SOPTYPE WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 5 THEN 'Back Order' WHEN 5 THEN 'Fullfillment Order' END AS SalesDocumentType ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.DOCAMNT ELSE SOP30200.DOCAMNT END AS SalesDocumentAmount ,CASE RM20101.RMDTYPAL WHEN 8 THEN -RM20101.ORTRXAMT ELSE RM20101.ORTRXAMT END AS ClientTransactionAmount ,SOP30200.ShipToName as SalesDocumentProject ,SOP30200.ADDRESS1 as SalesDocumentProjectAddress ,SOP30200.CSTPONBR as SalesDocumentPOnumber ,CASE SOP30200.VOIDSTTS WHEN 0 THEN 'Not Voided' WHEN 1 THEN 'Voided' END AS SalesDocumentVoid ,SOP30200.BACHNUMB as SalesDocumentItemBachNumber ,SOP30300.ITEMNMBR as SalesDocumentItemCode ,SOP30300.ITEMDESC as SalesDocumentItemDesc ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30300.QUANTITY ELSE SOP30300.QUANTITY END AS SalesDocumentQuantity ,SOP30300.UNITPRCE as SalesDocumentUnitPrice ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30300.XTNDPRCE ELSE SOP30300.XTNDPRCE END AS SalesDocuemntExtendedPrice ,CASE IV00101.PRICMTHD WHEN 1 THEN CASE WHEN IV00101.ITEMNMBR LIKE '*%' THEN (IV00108.UOMPRICE/0.6) ELSE IV00108.UOMPRICE END WHEN 6 THEN CASE WHEN IV00101.ITEMNMBR LIKE '*%' THEN ( (IV00101.CURRCOST / ((100 - IV00108.UOMPRICE)/100))/0.6) ELSE (IV00101.STNDCOST / ((100 - IV00108.UOMPRICE)/100)) END ELSE 0 END AS ItemCardCurrent_X1 ,SOP10106.CMMTTEXT as SalesDocumentComment ,IV00101.ITMCLSCD as SalesDocumentItemClass ,CASE WHEN RM20101.DINVPDOF = '1900-01-01' AND RM20101.RMDTYPAL = 1 THEN 'Not Paid' WHEN RM20101.DINVPDOF <> '1900-01-01' AND RM20101.RMDTYPAL = 1 THEN 'Paid' END as ClientTransactionPaidInFullFlag ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.SUBTOTAL ELSE SOP30200.SUBTOTAL END AS SalesDocumentSubTotal ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.FRTAMNT ELSE SOP30200.FRTAMNT END AS SalesDocumentFreight ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.MISCAMNT ELSE SOP30200.MISCAMNT END AS SalesDocumentHandling ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.TAXAMNT ELSE SOP30200.TAXAMNT END AS SalesDocumentTaxes ,CASE SOP30200.SOPTYPE WHEN 4 THEN -RM20201.DISTKNAM ELSE RM20201.DISTKNAM END AS ClientPaiementAmountDiscountTaken ,SOP10106.USERDEF1 as SalesDocumentREFBILL ,SOP30300.LOCNCODE as SalesDocumentLocationSource ,RM20201.APTODCNM as ClientPaiementApplyTo ,RM20201.APPTOAMT as ClientPaiementAmountApply ,(SELECT RM30101.ORTRXAMT FROM RM30101 WHERE RM30101.DOCNUMBR = RM20201.APTODCNM AND RM30101.CUSTNMBR = RM20201.CUSTNMBR AND RM30101.RMDTYPAL = RM20201.APTODCTY) as SalesDocumentAmountPaid ,(SELECT RM30101.DISTKNAM FROM RM30101 WHERE RM30101.DOCNUMBR = RM20201.APTODCNM AND RM30101.CUSTNMBR = RM20201.CUSTNMBR AND RM30101.RMDTYPAL = RM20201.APTODCTY) as SalesDocumentAmountPaidDiscountTaken FROM RM20101 LEFT JOIN SOP30200 ON RM20101.CUSTNMBR = SOP30200.CUSTNMBR AND RM20101.TRXDSCRN = SOP30200.SOPNUMBE AND RM20101.DOCDATE = SOP30200.DOCDATE LEFT JOIN SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE LEFT JOIN IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR LEFT JOIN SOP10106 ON SOP30200.SOPTYPE = SOP10106.SOPTYPE AND SOP30200.SOPNUMBE = SOP10106.SOPNUMBE LEFT JOIN IV00108 ON SOP30300.ITEMNMBR = IV00108.ITEMNMBR AND IV00108.PRCLEVEL = 'X1' LEFT JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR LEFT JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.RMDTYPAL IN (7,8,9) WHERE SOP30200.VOIDSTTS=0 AND RM20101.DOCDATE > DATEADD(year,-3,GETDATE()) UNION ALL SELECT CASE RM30101.RMDTYPAL WHEN 0 THEN 'Reserved for balance carried forward records' WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Reserved for scheduled payments' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service / Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS ClientTransactionType ,RM30101.DOCNUMBR as ClientTransactionNumber ,RM30101.DOCDATE as ClientTransactionDate ,RM30101.CUSTNMBR as ClientCode ,RM00101.CUSTNAME as ClientName ,RM00101.SLPRSNID as ClientSalesperson ,RM30101.TRXDSCRN as ClientTransactionAuxiliaryNumber0 ,CASE SOP30200.SOPTYPE WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 3 THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 5 THEN 'Back Order' WHEN 5 THEN 'Fullfillment Order' END AS SalesDocumentType ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.DOCAMNT ELSE SOP30200.DOCAMNT END AS SalesDocumentAmount ,CASE RM30101.RMDTYPAL WHEN 8 THEN -RM30101.ORTRXAMT ELSE RM30101.ORTRXAMT END AS ClientTransactionAmount ,SOP30200.ShipToName as SalesDocumentProject ,SOP30200.ADDRESS1 as SalesDocumentProjectAddress ,SOP30200.CSTPONBR as SalesDocumentPOnumber ,CASE SOP30200.VOIDSTTS WHEN 0 THEN 'Not Voided' WHEN 1 THEN 'Voided' END AS SalesDocumentVoid ,SOP30200.BACHNUMB as SalesDocumentItemBachNumber ,SOP30300.ITEMNMBR as SalesDocumentItemCode ,SOP30300.ITEMDESC as SalesDocumentItemDesc ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30300.QUANTITY ELSE SOP30300.QUANTITY END AS SalesDocumentQuantity ,SOP30300.UNITPRCE as SalesDocumentUnitPrice ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30300.XTNDPRCE ELSE SOP30300.XTNDPRCE END AS SalesDocuemntExtendedPrice ,CASE IV00101.PRICMTHD WHEN 1 THEN CASE WHEN IV00101.ITEMNMBR LIKE '*%' THEN (IV00108.UOMPRICE/0.6) ELSE IV00108.UOMPRICE END WHEN 6 THEN CASE WHEN IV00101.ITEMNMBR LIKE '*%' THEN ( (IV00101.CURRCOST / ((100 - IV00108.UOMPRICE)/100))/0.6) ELSE (IV00101.STNDCOST / ((100 - IV00108.UOMPRICE)/100)) END ELSE 0 END AS ItemCardCurrent_X1 ,SOP10106.CMMTTEXT as SalesDocumentComment ,IV00101.ITMCLSCD as SalesDocumentItemClass ,CASE WHEN RM30101.DINVPDOF = '1900-01-01' AND RM30101.RMDTYPAL = 1 THEN 'Not Paid' WHEN RM30101.DINVPDOF <> '1900-01-01' AND RM30101.RMDTYPAL = 1 THEN 'Paid' END as ClientTransactionPaidInFullFlag ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.SUBTOTAL ELSE SOP30200.SUBTOTAL END AS SalesDocumentSubTotal ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.FRTAMNT ELSE SOP30200.FRTAMNT END AS SalesDocumentFreight ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.MISCAMNT ELSE SOP30200.MISCAMNT END AS SalesDocumentHandling ,CASE SOP30200.SOPTYPE WHEN 4 THEN -SOP30200.TAXAMNT ELSE SOP30200.TAXAMNT END AS SalesDocumentTaxes ,CASE SOP30200.SOPTYPE WHEN 4 THEN -RM30201.DISTKNAM ELSE RM30201.DISTKNAM END AS ClientPaiementAmountDiscountTaken ,SOP10106.USERDEF1 as SalesDocumentREFBILL ,SOP30300.LOCNCODE as SalesDocumentLocationSource ,RM30201.APTODCNM as ClientPaiementApplyTo ,RM30201.APPTOAMT as ClientPaiementAmountApply ,(SELECT RM30101.ORTRXAMT FROM RM30101 WHERE RM30101.DOCNUMBR = RM30201.APTODCNM AND RM30101.CUSTNMBR = RM30201.CUSTNMBR AND RM30101.RMDTYPAL = RM30201.APTODCTY) as SalesDocumentAmountPaid ,(SELECT RM30101.DISTKNAM FROM RM30101 WHERE RM30101.DOCNUMBR = RM30201.APTODCNM AND RM30101.CUSTNMBR = RM30201.CUSTNMBR AND RM30101.RMDTYPAL = RM30201.APTODCTY) as SalesDocumentAmountPaidDiscountTaken FROM RM30101 LEFT JOIN SOP30200 ON RM30101.CUSTNMBR = SOP30200.CUSTNMBR AND RM30101.TRXDSCRN = SOP30200.SOPNUMBE AND RM30101.DOCDATE = SOP30200.DOCDATE LEFT JOIN SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE LEFT JOIN IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR LEFT JOIN SOP10106 ON SOP30200.SOPTYPE = SOP10106.SOPTYPE AND SOP30200.SOPNUMBE = SOP10106.SOPNUMBE LEFT JOIN IV00108 ON SOP30300.ITEMNMBR = IV00108.ITEMNMBR AND IV00108.PRCLEVEL = 'X1' LEFT JOIN RM00101 ON RM30101.CUSTNMBR = RM00101.CUSTNMBR LEFT JOIN RM30201 ON RM30101.CUSTNMBR = RM30201.CUSTNMBR AND RM30101.DOCNUMBR = RM30201.APFRDCNM AND RM30101.RMDTYPAL IN (7,8,9) WHERE SOP30200.VOIDSTTS=0 AND RM30101.DOCDATE > DATEADD(year,-3,GETDATE()) )UNIONResult
*This post is locked for comments
All responses (
Answers (
291,280
