cancel
Showing results for 
Search instead for 
Did you mean: 

Closed Status AR Invoices Are not showing in this query

Former Member
0 Kudos

Why "CLOSED"  status AR Invoices are not showing in this Query ?

declare @FDate as datetime

declare @TDate as datetime

declare @Location as VARCHAR (30)

/* SELECT FROM [dbo].[OLCT] S0 WHERE */ SET  @Location = /* S0.Location*/ '[%0]'

/* SELECT FROM [dbo].[OINM] S1 WHERE */ SET  @FDate = /* S1.TaxDate*/ '[%1]'

/* SELECT FROM [dbo].[OINM] S2 WHERE */ SET  @TDate = /* S2.TaxDate*/ '[%2]'

SELECT

  'Sales'Document,OBTN.DistNumber

  ,NNM1.SeriesName,OINV.DocNum,OCRD.CardName,CRD7.ECCNo,OINV.DocDate,OITM.SWW [HSN Number],INV1.ItemCode,INV1.Dscription,(ITL1.Quantity*-1) [Quantity]

  ,OEDR.Name,OBTN.U_EDuty*(ITL1.Quantity*-1) EDuty,OBTN.U_EDuty EDutyUnit,OBTN.U_EDCess*(ITL1.Quantity*-1) EDCess,OBTN.U_EDHSCess*(ITL1.Quantity*-1) EDHSCess

  ,OBTN.U_EDImpDuty SADUnit,OBTN.U_EDImpDuty*(ITL1.Quantity*-1) SADTotal

  ,OBTN.U_MfgName,OBTN.U_MECCNo,OBTN.U_MfgInvNo,OBTN.U_MfgInvDt,OBTN.U_MfgQty,OBTN.U_MfgValue

  ,OBTN.U_MfgDuty,OBTN.U_MfgCess,OBTN.U_MfgHSCess,OBTN.U_MfgImport

  ,OBTN.U_SupName,OBTN.U_SECCNo,OBTN.U_SupInvNo,OBTN.U_SupInvDt,OBTN.U_SupQty,OBTN.U_SupValue

  ,OBTN.U_SupDuty,OBTN.U_SupCess,OBTN.U_SupHSCess,OBTN.U_SupImport

FROM

  INV1

INNER JOIN

  OINV ON INV1.DocEntry=OINV.DocEntry

INNER JOIN

  OITM ON INV1.ItemCode=OITM.ItemCode

INNER JOIN

  OCRD ON OINV.CardCode = OCRD.CardCode

INNER JOIN

CRD7  ON OCRD.CardCode = CRD7.CardCode

INNER JOIN

  OITL ON INV1.BaseType=OITL.ApplyType AND INV1.BaseEntry=OITL.ApplyEntry AND INV1.BaseLine=OITL.ApplyLine

INNER JOIN

  ITL1 ON OITL.LogEntry=ITL1.LogEntry

INNER JOIN

  OBTN ON ITL1.MdAbsEntry=OBTN.AbsEntry and ITL1.SysNumber=OBTN.SysNumber AND ITL1.ItemCode=OBTN.Itemcode

INNER JOIN

  [@OEDR] OEDR ON OBTN.U_EDRate=OEDR.Code

LEFT JOIN

  NNM1 ON OINV.Series=NNM1.Series

WHERE INV1.LocCode IN (SELECT Code FROM OLCT WHERE Location=@Location)

  AND OINV.DocDate BETWEEN @FDate AND @TDate

UNION ALL

SELECT

  'Sales Return'Document,OBTN.DistNumber

  ,NNM1.SeriesName,ORIN.DocNum,OCRD.CardName,CRD7.ECCNo,ORIN.DocDate,OITM.SWW [HSN Number],RIN1.ItemCode,RIN1.Dscription,(ITL1.Quantity*-1) [Quantity]

  ,OEDR.Name,OBTN.U_EDuty*(ITL1.Quantity*-1) EDuty,OBTN.U_EDuty EDutyUnit,OBTN.U_EDCess*(ITL1.Quantity*-1) EDCess,OBTN.U_EDHSCess*(ITL1.Quantity*-1) EDHSCess

  ,OBTN.U_EDImpDuty SADUnit,OBTN.U_EDImpDuty*(ITL1.Quantity*-1) SADTotal

  ,OBTN.U_MfgName,OBTN.U_MECCNo,OBTN.U_MfgInvNo,OBTN.U_MfgInvDt,OBTN.U_MfgQty,OBTN.U_MfgValue

  ,OBTN.U_MfgDuty,OBTN.U_MfgCess,OBTN.U_MfgHSCess,OBTN.U_MfgImport

  ,OBTN.U_SupName,OBTN.U_SECCNo,OBTN.U_SupInvNo,OBTN.U_SupInvDt,OBTN.U_SupQty,OBTN.U_SupValue

  ,OBTN.U_SupDuty,OBTN.U_SupCess,OBTN.U_SupHSCess,OBTN.U_SupImport

FROM

  RIN1

INNER JOIN

  ORIN ON RIN1.DocEntry=ORIN.DocEntry

INNER JOIN

  OCRD ON ORIN.CardCode = OCRD.CardCode

INNER JOIN

CRD7  ON OCRD.CardCode = CRD7.CardCode

INNER JOIN

  OITM ON RIN1.ItemCode=OITM.ItemCode

INNER JOIN

  OITL ON RIN1.BaseType=OITL.ApplyType AND RIN1.BaseEntry=OITL.ApplyEntry AND RIN1.BaseLine=OITL.ApplyLine

INNER JOIN

  ITL1 ON OITL.LogEntry=ITL1.LogEntry

INNER JOIN

  OBTN ON ITL1.MdAbsEntry=OBTN.AbsEntry and ITL1.SysNumber=OBTN.SysNumber AND ITL1.ItemCode=OBTN.Itemcode

INNER JOIN

  [@OEDR] OEDR ON OBTN.U_EDRate=OEDR.Code

LEFT JOIN

  NNM1 ON ORIN.Series=NNM1.Series

WHERE RIN1.LocCode IN (SELECT Code FROM OLCT WHERE Location=@Location)

  AND ORIN.DocDate BETWEEN @FDate AND @TDate

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

There is no document status field in above query. Please include Docstatus field for OINV and ORIN and then check this query.

Thanks & Regards,

Nagarajan

Answers (0)