Skip to Content
0
Dec 11, 2013 at 02:27 AM

AR Collection Query

38 Views

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