Canceled AP Invoice exclude from Query based on Canceled Invioce date

[former_member209725]

Dear Expert,

I have a situation where we cancel back dated AP Invoices in current date because of GST return filing issue.

For example there is an AP Invoice document date 20-11-2019 and Nov month GST return filing is done and for some reason I have to cancel that Invoice, so I have to cancel the Invoice in Dec month so I will change dates on canceled invoice to 20-12-2019.

In Purchase Analysis Invoice effect will take place in Dec month and not in Nov based on canceled invoice dates.

But same is not the case with My Query report, In my query report effect will take place in Nov month which is not right.

I am copying my Query report here under. Kindly help me to solve this issue. The effect of canceled invoice should take place in Dec month only and not in Nov as per canceled invoice date.

--------------GST OVERALL PURCAHSE REPORT DOC NUMBER WISE------------
SELECT T3.[GSTRegnNo] AS 'GST No', T0.[DocNum] AS 'INV No.', t0.numatcard,
T0.Taxdate as 'Bill Date',
T0.Cardcode,
T2.CardName as 'Vendor Name',
(select top 1 ocst.Name from OCST inner join CRD1 on crd1.State = OCST.Code
INNER JOIN OCRY ON OCRY.Code = CRD1.Country AND T0.CardCode = CRD1.CardCode
AND T2.ShipToDef= CRD1.Address AND OCRY.Code = OCST.Country AND AdresType = 'S' ) as 'SState',
(select top 1 ocst.GSTCode from OCST inner join CRD1 on crd1.State = OCST.Code
INNER JOIN OCRY ON OCRY.Code = CRD1.Country AND T0.CardCode = CRD1.CardCode
AND T2.ShipToDef= CRD1.Address AND OCRY.Code = OCST.Country AND AdresType = 'S' ) as 'Sstatecode',
convert(varchar(100),DATEPART(day,T0.[DocDate])) + '-' + convert(varchar(100),DATENAME(month, T0.[DocDate]))+ '-' + convert(varchar(100),YEAR(T0.[DocDate])) as 'INV DATE',
T0.[DocTotal] AS 'INV VALUE',
(select top 1 ocst.GSTCode from OCST inner join CRD1 on crd1.State = OCST.Code
INNER JOIN OCRY ON OCRY.Code = CRD1.Country AND T0.CardCode = CRD1.CardCode
AND OCRY.Code = OCST.Country AND AdresType = 'S' ) + '-' + (select top 1 ocst.Name from OCST inner join CRD1 on crd1.State = OCST.Code
INNER JOIN OCRY ON OCRY.Code = CRD1.Country AND T0.CardCode = CRD1.CardCode
AND OCRY.Code = OCST.Country AND AdresType = 'S' ) as 'Place Of Supply','N' AS 'REVERSE CHARGES', 'REGULAR' AS 'INV TYPE', ' ' AS 'ECOMMERCE GSTIN', --T1.[TaxCode] AS 'TAX RATE',
ISNULL((SELECT Sum(ISNULL(TaxSum,0)) FROM pch4 where statype=-100 and DocEntry=T0.DocEntry), 0 ) 'CGST Total',
ISNULL((SELECT Sum(ISNULL(TaxSum,0)) FROM pch4 where statype=-110 and DocEntry=T0.DocEntry), 0 ) 'SGST Total',
ISNULL((SELECT Sum(ISNULL(TaxSum,0)) FROM pch4 where statype=-120 and DocEntry=T0.DocEntry), 0 ) 'IGST Total',
SUM(T1.[LineTotal]) - DiscSum AS 'Basic Value', TotalExpns AS 'Freight(Rs.)',
T0.wtsum as 'TDS amt','0.00' AS 'CESS AMT', T0.rounddif as 'rounding',
(select distinct case when left(SeriesName,3) = 'JW1' then 'JW' else case when left(SeriesName,3) = 'RM1' then 'RM' else case when left(SeriesName,3) = 'FA1' then 'FA' else left(SeriesName,3) End END End from NNM1 where T0.Series = nnm1.Series
) as 'SeriesName',OADM.CompnyName,T0.DocTotal as 'Total (Rs.)',T3.GSTRegnNo,
CASE WHEN T0.[GSTTranTyp] = '--' THEN 'BILL OF SUPPLY' ELSE CASE WHEN T0.[GSTTranTyp] = 'GA' THEN 'GST' End END AS 'GST TYPE'
FROM OPCH T0
LEFT OUTER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT OUTER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
LEFT OUTER JOIN CRD1 T3 ON T0.[CardCode] = T3.[CardCode] and T2.ShipToDef = T3.Address
--LEFT OUTER JOIN OCST T4 ON T2.[State1] = T4.[CODE]and t3.Country != t4.Country
LEFT JOIN NNM1 N ON N.Series = T0.Series
CROSS JOIN ADM1 INNER JOIN OADM ON OADM.Code = ADM1.Code
WHERE
T3.[AdresType] = 's' and T0.CANCELED NOT IN ('c','y') and t0.taxdate between '20191001' and '20191031'
GROUP BY T3.[GSTRegnNo] , T0.[DocNum] , T0.[DocDate] , T0.[DocTotal],
-- T4.[GSTCode], T4.[Name],
T0.TaxDate, t0.numatcard,T2.ShipToDef,
T0.CardCode,T2.CardName,T0.DocEntry,T0.TotalExpns,T0.WTSum,T0.RoundDif,T0.Series,OADM.CompnyName,T0.[GSTTranTyp],T3.GSTRegnNo,t0.DiscSum
ORDER BY T0.docnum asc

Pardon me if my question seems confusing. I will explain again in answers.

Thanks and Regards,

Ravi

[msundararaja_perumal · Accepted solution]

Hello,

Add cancelled date column in the query.

The do an outer query to check if the date range is applicable to the cancelled date column

Thanks.