Query to get credit memo information

[jbrotto]

At the moment we are using several hird party solutions for B2B transactions. I generate this report to the information to import into a software. I noticed issues with the taxes as if we change from compound to not compound we end off with incorrect values. I was thinking using the journal entries to get the correct taxes.

EXEC _temp_ORIN
SELECT * INTO temp_ORIN FROM dbo.ORIN T2 WHERE T2.[DocDate] >= '[%0]' AND T2.[DocDate] <= '[%1]'
SELECT   Type,
    DUNS_Number,
    Dist_Number,
    AccountsPayable_Number,
    Invoice_Date,
    Invoice_Number,
    PO_Date,
    PO_Number,
    CR_DB,
    Terms_Of_Sale,
    Terms_Percent,
    Discount_Due_Date,
    Number_Of_Days,
    Net_Due_Date,
    Net_Days,
    Notes,
    Date_Shipped,
    Delivery_Date,
    GST_Amount,
    CAST(PST_Amount AS VARCHAR) AS 'PST_Amount',
    HST_Amount,
    CAST(Number_Of_Units AS VARCHAR) AS 'Number_Of_Units',
    UOM,
    Weight,
    UOM_For_Weight,
    CAST(Total_Amount AS VARCHAR) AS 'Total_Amount',
    Bill_To_Name,
    Bill_To_DUNS,
    Bill_To_Address,
    Bill_To_City,
    Bill_To_Province,
    Bill_To_Postal_Code,
    Bill_To_Country,
    CASE SUBSTRING(Ship_To_Name, 1, 3)
WHEN 'PJC' THEN 'PHARMACIE JEAN COUTU ' + SUBSTRING(Ship_To_DUNS,2,3)
ELSE Ship_To_Name
END AS 'Ship_To_Name',
    Ship_To_DUNS,
    Ship_To_Address,
    Ship_To_City,
    Ship_To_Province,
    Ship_To_Postal_Code,
    Ship_To_Country,
    Confirmation_Request
FROM (
SELECT DISTINCT ih.DocEntry,
    CAST('H' AS VARCHAR) AS 'Type',
    CAST('2492059980000' AS VARCHAR) AS 'DUNS_Number',
    CAST('724601' AS VARCHAR) AS 'Dist_Number',
    CAST('724601' AS VARCHAR) AS 'AccountsPayable_Number',
    CAST(ih.DocDate AS VARCHAR) AS 'Invoice_Date',
    CAST(ih.DocNum AS VARCHAR) AS 'Invoice_Number',
    CAST(NULL AS VARCHAR) AS 'PO_Date',
    CAST(NULL AS VARCHAR) AS 'PO_Number',
    CAST('CR' AS VARCHAR) AS 'CR_DB',
    CAST('01' AS VARCHAR) AS 'Terms_Of_Sale',
    NULL AS 'Terms_Percent',
    NULL AS 'Discount_Due_Date',
    NULL AS 'Number_Of_Days',
    NULL AS 'Net_Due_Date',
    30 AS 'Net_Days',
    ISNULL(ih.CardCode + '|' + ih.Comments + '|' + ih.U_Clerk + '|' + CASE WHEN ih.DiscSum > 0 THEN 'Discounted credit' END, 'No Notes') AS 'Notes',
    CAST(ih.DocDate AS VARCHAR) AS 'Date_Shipped',
    CAST((ih.DocDate + 0) AS VARCHAR) AS 'Delivery_Date',
    -(ih.VatSumSy - (CASE WHEN ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * ih.GrosProfit, 2) IS NULL THEN 0 ELSE ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * ih.GrosProfit, 2) END)) AS 'GST_Amount',
    -(CASE WHEN ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * ih.GrosProfit, 2) IS NULL THEN 0 ELSE ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * ih.GrosProfit, 2) END) AS 'PST_Amount',
    NULL AS 'HST_Amount',
    -(SELECT  SUM(Quantity)
    FROM  RIN1
    WHERE  DocEntry = ih.DocEntry) AS 'Number_Of_Units',
    'AS' AS 'UOM',
    NULL AS 'Weight',
    NULL AS 'UOM_For_Weight',
    -ih.DocTotal AS 'Total_Amount',
    'LE GROUPE JEAN COUTU PJC INC.' AS 'Bill_To_Name',
    '2088707580000' AS 'Bill_To_DUNS',
    '530 RUE BERIAULT' AS 'Bill_To_Address',
    'LONGUEUIL' AS 'Bill_To_City',
    'QC' AS 'Bill_To_Province',
    'J4G 1S8' AS 'Bill_To_Postal_Code',
    c.Country AS 'Bill_To_Country',
    c.CardName AS 'Ship_To_Name',
    RIGHT(REPLICATE('0' ,1) + SUBSTRING (c.CardName, 5, 3), 4) AS 'Ship_To_DUNS',
    c.MailAddres AS 'Ship_To_Address',
    c.MailCity AS 'Ship_To_City',
    c.State2 AS 'Ship_To_Province',
    c.MailZipCod AS 'Ship_To_Postal_Code',
    c.MailCountr AS 'Ship_To_Country',
    'Y' AS 'Confirmation_Request'
FROM   temp_ORIN AS ih
LEFT JOIN  RIN1 AS id ON (id.DocEntry = ih.DocEntry)
LEFT JOIN  OCRD AS c ON (c.CardCode = ih.CardCode)
WHERE   (c.CardName LIKE '%PJC%' OR c.CardName LIKE '%Jean Coutu%') AND
    ih.DocType = 'I' AND
    ih.Comments NOT LIKE '%cancel%' AND
    ih.Comments NOT LIKE '%void%' AND
    ih.DocStatus = 'O'

UNION ALL

SELECT   CAST(id.DocEntry AS VARCHAR),
    CAST('D' AS VARCHAR) AS 'Type',
    CAST(id.ItemCode AS VARCHAR) AS 'UPC_Code',
    CAST(c.CardCode AS VARCHAR),
    CAST(id.Dscription AS VARCHAR),
    CAST(-id.Quantity AS VARCHAR),
    CAST('AS' AS VARCHAR) AS 'UOM',
    CAST(ROUND(id.INMPrice, 2) AS VARCHAR) AS 'Price',
    CAST(NULL AS VARCHAR) AS 'Pack_Quantity',
    CAST(ROUND(id.VatSumSy, 2) - (CASE WHEN ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * id.GrssProfit, 2) IS NULL THEN 0 ELSE ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * id.GrssProfit, 2) END) AS VARCHAR) AS 'GST_Amount',
    CASE WHEN ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * id.GrssProfit, 2) IS NULL THEN 0 ELSE ROUND(((1 + ((SELECT TOP 1    o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 2 AND
          i.TaxCode = id.TaxCode) / 100)) * ((SELECT TOP 1  o.Rate
     FROM    RIN1 AS i
     LEFT JOIN   STC1 AS s ON (s.STCCode = i.TaxCode)
     LEFT JOIN   OSTA AS o ON (o.Code = s.STACode AND o.Type = s.STAType)
     WHERE    o.Type = 1 AND
          i.TaxCode = id.TaxCode) / 100)) * id.GrssProfit, 2) END AS 'PST_Amount',
    NULL AS 'HST_Amount',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
FROM   RIN1 AS id
LEFT JOIN  temp_ORIN AS ih ON (ih.DocEntry = id.DocEntry)
LEFT JOIN  OCRD AS c ON (c.CardCode = ih.CardCode)
WHERE   (c.CardName LIKE '%PJC%' OR c.CardName LIKE '%Jean Coutu%') AND
    ih.DocType = 'I' AND
    ih.Comments NOT LIKE '%void%' AND
    ih.Comments NOT LIKE '%c%cancel%%' AND
    ih.DocStatus = 'O'
) AS Invoice
ORDER BY  DocEntry, Type DESC

[jbrotto · Accepted solution]

I manage to et better results and now et HST amounts as this account only deals with 3 provinces so only QC is GST and the rest is HST even though we use the same GL. I know it is not the best but I think this is better than before.

/* Have to code the Exceptions for HST and GST as no GL for it
GL 2650 is GST
GL 2620 is PST */

EXEC _temp_ORIN
SELECT * INTO temp_ORIN FROM dbo.ORIN T2 WHERE T2.[DocDate] >= '[%0]' AND T2.[DocDate] <= '[%1]'
SELECT   Type,
    DUNS_Number,
    Dist_Number,
    AccountsPayable_Number,
    Invoice_Date,
    Invoice_Number + 'CC' AS 'Invoice_Number',
    PO_Date,
    PO_Number,
    CR_DB,
    Terms_Of_Sale,
    Terms_Percent,
    Discount_Due_Date,
    Number_Of_Days,
    Net_Due_Date,
    Net_Days,
    Notes,
    Date_Shipped,
    Delivery_Date,
    CAST(GST_Amount AS VARCHAR) AS 'GST_Amount',
    CAST(PST_Amount AS VARCHAR) AS 'PST_Amount',
    CAST(HST_Amount AS VARCHAR) AS 'HST_Amount',
    CAST(Number_Of_Units AS VARCHAR) AS 'Number_Of_Units',
    UOM,
    Weight,
    UOM_For_Weight,
    CAST(Total_Amount AS VARCHAR) AS 'Total_Amount',
    Bill_To_Name,
    Bill_To_DUNS,
    Bill_To_Address,
    Bill_To_City,
    Bill_To_Province,
    Bill_To_Postal_Code,
    Bill_To_Country,
    CASE SUBSTRING(Ship_To_Name, 1, 3)
WHEN 'PJC' THEN 'PHARMACIE JEAN COUTU ' + SUBSTRING(Ship_To_DUNS,2,3)
ELSE Ship_To_Name
END AS 'Ship_To_Name',
    Ship_To_DUNS,
    Ship_To_Address,
    Ship_To_City,
    Ship_To_Province,
    Ship_To_Postal_Code,
    Ship_To_Country,
    Confirmation_Request
FROM (
SELECT DISTINCT ih.DocEntry,
    CAST('H' AS VARCHAR) AS 'Type',
    CAST('2492059980000' AS VARCHAR) AS 'DUNS_Number',
    CAST('724601' AS VARCHAR) AS 'Dist_Number',
    CAST('724601' AS VARCHAR) AS 'AccountsPayable_Number',
    CAST(ih.DocDate AS VARCHAR) AS 'Invoice_Date',
    CAST(ih.DocNum AS VARCHAR) AS 'Invoice_Number',
    CAST(NULL AS VARCHAR) AS 'PO_Date',
    CAST(NULL AS VARCHAR) AS 'PO_Number',
    CAST('CR' AS VARCHAR) AS 'CR_DB',
    CAST('01' AS VARCHAR) AS 'Terms_Of_Sale',
    NULL AS 'Terms_Percent',
    NULL AS 'Discount_Due_Date',
    NULL AS 'Number_Of_Days',
    NULL AS 'Net_Due_Date',
    30 AS 'Net_Days',
    ISNULL(ih.CardCode + '|' + ih.Comments + '|' + ih.U_Clerk + '|' + CASE WHEN ih.DiscSum > 0 THEN 'Discounted credit' END, 'No Notes') AS 'Notes',
    CAST(ih.DocDate AS VARCHAR) AS 'Date_Shipped',
    CAST((ih.DocDate + 0) AS VARCHAR) AS 'Delivery_Date',
    CAST((SELECT TOP 1 T1.Credit FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId INNER JOIN OCRD T3 ON T3.CARDCODE = T2.CARDCODE WHERE T2.DocNum = ih.DocNum AND T1.Account = '2650' AND T3.State2 = 'QC') AS VARCHAR) AS 'GST_AMOUNT',
    CAST((SELECT TOP 1 T1.Credit FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId WHERE T2.DocNum = ih.DocNum AND T1.Account = '2620') AS VARCHAR) AS 'PST_AMOUNT',
    CAST((SELECT TOP 1 T1.Credit FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId INNER JOIN OCRD T3 ON T3.CARDCODE = T2.CARDCODE WHERE T2.DocNum = ih.DocNum AND T1.Account = '2650' AND T3.State2 != 'QC') AS VARCHAR) AS 'HST_AMOUNT',
    -(SELECT  SUM(Quantity)
    FROM  RIN1
    WHERE  DocEntry = ih.DocEntry) AS 'Number_Of_Units',
    'AS' AS 'UOM',
    NULL AS 'Weight',
    NULL AS 'UOM_For_Weight',
    -ih.DocTotal AS 'Total_Amount',
    'LE GROUPE JEAN COUTU PJC INC.' AS 'Bill_To_Name',
    '2088707580000' AS 'Bill_To_DUNS',
    '530 RUE BERIAULT' AS 'Bill_To_Address',
    'LONGUEUIL' AS 'Bill_To_City',
    'QC' AS 'Bill_To_Province',
    'J4G 1S8' AS 'Bill_To_Postal_Code',
    c.Country AS 'Bill_To_Country',
    c.CardName AS 'Ship_To_Name',
    RIGHT(REPLICATE('0' ,1) + SUBSTRING (c.CardName, 5, 3), 4) AS 'Ship_To_DUNS',
    c.MailAddres AS 'Ship_To_Address',
    c.MailCity AS 'Ship_To_City',
    c.State2 AS 'Ship_To_Province',
    c.MailZipCod AS 'Ship_To_Postal_Code',
    c.MailCountr AS 'Ship_To_Country',
    'Y' AS 'Confirmation_Request'
FROM   temp_ORIN AS ih
LEFT JOIN  RIN1 AS id ON (id.DocEntry = ih.DocEntry)
LEFT JOIN  OCRD AS c ON (c.CardCode = ih.CardCode)
WHERE   (c.CardName LIKE '%PJC%%' OR c.CardName LIKE '%Jean Coutu%') AND
    ih.DocType = 'I' AND
    ih.DocStatus = 'O'

UNION ALL

SELECT   CAST(id.DocEntry AS VARCHAR),
    CAST('D' AS VARCHAR) AS 'Type',
    CAST(id.ItemCode AS VARCHAR) AS 'UPC_Code',
    CAST(c.CardCode AS VARCHAR),
    CAST(id.Dscription AS VARCHAR),
    CAST(-id.Quantity AS VARCHAR),
    CAST('AS' AS VARCHAR) AS 'UOM',
    CAST(ROUND(id.INMPrice, 2) AS VARCHAR) AS 'Price',
    CAST(NULL AS VARCHAR) AS 'Pack_Quantity',
    CAST(ROUND((
     SELECT TOP 1 T1.Credit
     FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId INNER JOIN OCRD T3 ON T3.CARDCODE = T2.CARDCODE
     WHERE T2.DocNum = ih.DocNum AND T1.Account = '2650' AND T3.State2 = 'QC') * ((id.LineTotal/ (ih.DocTotal-ih.VatSum))*(1-(ih.DiscPrcnt/100))),2) AS VARCHAR) AS 'GST_AMOUNT',
    CAST(ROUND((
     SELECT TOP 1 T1.Credit
     FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId
     WHERE T2.DocNum = ih.DocNum AND T1.Account = '2620') * ((id.LineTotal/ (ih.DocTotal-ih.VatSum))*(1-(ih.DiscPrcnt/100))),2) AS VARCHAR) AS 'PST_AMOUNT',
    CAST(ROUND((
     SELECT TOP 1 T1.Credit
     FROM ORIN T2 INNER JOIN JDT1 T1 ON ih.TransId = T1.TransId INNER JOIN OCRD T3 ON T3.CARDCODE = T2.CARDCODE
     WHERE T2.DocNum = ih.DocNum AND T1.Account = '2650' AND T3.State2 != 'QC') * ((id.LineTotal/ (ih.DocTotal-ih.VatSum))*(1-(ih.DiscPrcnt/100))),2) AS VARCHAR) AS 'HST_AMOUNT',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
FROM   RIN1 AS id
LEFT JOIN  temp_ORIN AS ih ON (ih.DocEntry = id.DocEntry)
LEFT JOIN  OCRD AS c ON (c.CardCode = ih.CardCode)
WHERE   (c.CardName LIKE '%PJC%%' OR c.CardName LIKE '%Jean Coutu%') AND
    ih.DocType = 'I' AND
    ih.DocStatus = 'O'
) AS Invoice
ORDER BY  DocEntry, Type DESC