AR Collection Query

[kedalenechong]

Hi All

Anyone knows how to correct this Query?

There is missing paid Invoice when Incoming Payment is payment on account advance payment matched in internal BP reconciliation against AR Invoices fully paid and against another JE that Debited AR Customer for exchange gain loss account.

/* SELECT FROM [dbo].[OFPR] T01 */
/* WHERE */
DECLARE @DATEFR DATE
SET @DATEFR =/* T01.F_REFDATE */[%0]

/* SELECT FROM [dbo].[OFPR] T02 */
/* WHERE */
DECLARE @DATETO DATE
SET @DATETO =/* T02.T_REFDATE */[%1]

/* SELECT FROM [dbo].[OSLP] T2 */
/* WHERE */
DECLARE @SPFR NVARCHAR(50)
SET @SPFR =/* T2.SlpName*/'[%2]'

/* SELECT FROM [dbo].[OSLP] T3 */
/* WHERE */
DECLARE @SPTO NVARCHAR(50)
SET @SPTO =/* T3.SlpName*/'[%3]'

EXEC [AF_COLLECTION_RPT]  @DATEFR, @DATETO, @SPFR, @SPTO
[10/12/2013 5:08:37 PM] Kedalene Chong: GO
/****** Object:  StoredProcedure [dbo].[AF_COLLECTION_RPT]    Script Date: 12/06/2013 11:28:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[AF_COLLECTION_RPT]
  @DATEFR AS Date, @DATETO AS Date
  , @SPFR AS NVARCHAR(30), @SPTO AS NVARCHAR(30)
AS

BEGIN

SELECT *
FROM(
  Select
     T0.DocDate [RC Date]
   , T0.DocNum [RC #]
   , COALESCE(T2.DocNum, [RC_RECON_D].DOCNUM) [INV #]
   , T0.CardCode
   , T05.SlpName
   , T0.CardName [Customer]
   , COALESCE(T1.SumApplied,[RC_RECON_D].RECONSUM) [RC Amount]
   , COALESCE(T1.AppliedFC,[RC_RECON_D].RECONSUMFC)  [Amount (FC)]
   , COALESCE((T2.DocTotal - T2.VatSum - T2.DiscSum),(T3.DocTotal - T3.VatSum - T3.DiscSum),[RC_RECON_D].[IN TOTAL]) [Invoice Total bf GST]
   , '' ['%]
   , '' [Total]
   , COALESCE(T02.SlpName,T03.SlpName, [RC_RECON_D].SLPNAME, T05.SLPNAME)[Salesperson]
   ,T0.Canceled
  From ORCT T0
  LEFT OUTER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum
  INNER JOIN OCRD T04 ON T0.CardCode =T04.CardCode 
  LEFT OUTER JOIN OINV T2 ON T1.DocTransId = T2.TransId AND T1.InvType = T2.ObjType
  LEFT OUTER JOIN ORIN T3 ON T1.DocTransId = T3.TransId AND T1.InvType = T3.ObjType
  LEFT OUTER JOIN OSLP T02 ON T2.SlpCode = T02.SlpCode
  LEFT OUTER JOIN OSLP T03 ON T3.SlpCode = T03.SlpCode
  LEFT OUTER JOIN OSLP T05 ON T04.SlpCode = T05.SlpCode
  LEFT OUTER JOIN
  (
   SELECT T0.ReconDate , T1.RECONNUM, T1.SrcObjTyp, T1.SrcObjAbs
   FROM OITR T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum
   WHERE IsSystem = 'N'
   AND IsMultiBP = 'N'
   AND T1.SrcObjTyp = '24'
   AND T0.ReconDate BETWEEN @DATEFR AND @DATETO
  ) [RC_RECON_H] ON T0.DocEntry = SrcObjAbs
  LEFT OUTER JOIN
  (
   SELECT T1.RECONNUM,T1.SrcObjTyp, T1.SrcObjAbs
    , T1.RECONSUM, T1.ReconSumFC
    , T100.DocNum,( T100.DocTotal - T100.VatSum - T100.DiscSum) [IN TOTAL],T05.SLPNAME 
   FROM OITR T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum
   LEFT OUTER JOIN OINV T100 ON T1.SrcObjAbs = T100.DocEntry
   LEFT OUTER JOIN OSLP T05 ON T100.SlpCode = T05.SlpCode
   WHERE IsSystem = 'N'
   AND IsMultiBP = 'N'
   AND T1.SrcObjTyp = '13'
  ) [RC_RECON_D] ON [RC_RECON_H].ReconNum = [RC_RECON_D].ReconNum

)AS [TRC]
WHERE [TRC].[RC Date] BETWEEN @DATEFR AND @DATETO
    AND [TRC].[Salesperson] BETWEEN @SPFR AND @SPTO
AND TRC.Canceled='N'
ORDER BY 1,2

END

[kothandaraman_nagarajan]

Hi,

Please explain your payment process with an example.

Thanks & Regards,

Nagarajan

[kedalenechong]

Hi Nagarajan

Example

An Incoming Payment was posted as payment on account advance payment then later matched in internal BP reconciliation against AR Invoice 1380841 and 1380842 fully paid and against another JE that Debited AR Customer for exchange gain loss account.

Kedalene Chong