Employee advances detail query

[Former Member]

Dear Experts,

I want staff advances with detail in sap b1.

I wrote following query get details but result not tally with Advances To Staff in Trial Balance. Please sea my query and help me to correct it.

SELECT CONCAT (CONCAT (NNM1."SeriesName", '-'),ODPO."DocNum")  AS "Series",
ODPO."CardCode",MAX(ODPO."CardName")
,ODPO."DocDate"
,( (ODPO."DocTotal") - COALESCE(SUM("DrawnSum"),0) - COALESCE(SUM(RPC1."LineTotal"),0))AS "Advance Amount"


FROM ODPO
INNER JOIN OCRD ON ODPO."CardCode" = OCRD."CardCode"
INNER JOIN NNM1 ON ODPO."Series" =  NNM1."Series"
LEFT JOIN(SELECT PCH9."BaseAbs",OPCH."DocDate",PCH9."DrawnSum",PCH9."DrawnSumFc" ,PCH9."ObjType"
				,OPCH."CANCELED",OCRD."GroupCode" ,OCRD."DpmClear"
			FROM OPCH 
			INNER JOIN PCH9 ON PCH9."DocEntry" = OPCH."DocEntry"
			INNER JOIN OCRD ON OPCH."CardCode" = OCRD."CardCode") AS  PCH9 
			ON PCH9."BaseAbs" = ODPO."DocEntry" AND PCH9."ObjType" = ODPO."ObjType" AND PCH9."CANCELED" ='N'
			AND PCH9."DpmClear" ='111010200'
			AND PCH9."GroupCode" = 105
		    AND (TO_CHAR(PCH9."DocDate", 'YYYY-MM-DD') <= TO_CHAR('2017-11-12', 'YYYY-MM-DD') ) 


LEFT JOIN(SELECT RPC1."BaseEntry",ORPC."DocDate",ORPC."DocTotal",RPC1."LineTotal",RPC1."BaseType" ,
ORPC."CANCELED",RPC1."AcctCode",OCRD."DpmClear",OCRD."GroupCode" 
			FROM ORPC 
			INNER JOIN RPC1 ON RPC1."DocEntry" = ORPC."DocEntry"
			INNER JOIN OCRD ON ORPC."CardCode" = OCRD."CardCode"
			) AS  RPC1 
			ON RPC1."BaseEntry" = ODPO."DocEntry" AND RPC1."BaseType" = ODPO."ObjType"
			AND RPC1."DpmClear" ='111010200'
			 AND RPC1."CANCELED" ='N' 
			 AND RPC1."GroupCode" = 105
		AND (TO_CHAR(RPC1."DocDate", 'YYYY-MM-DD') <= TO_CHAR('2017-11-12', 'YYYY-MM-DD') ) 
		
WHERE  OCRD."GroupCode" = 105
		AND OCRD."DpmClear" ='111010200'
		AND ODPO."CANCELED" ='N'
		AND (TO_CHAR(ODPO."DocDate", 'YYYY-MM-DD') <= TO_CHAR('2017-11-12', 'YYYY-MM-DD') ) 
GROUP BY NNM1."SeriesName",ODPO."DocNum",ODPO."DocCur",OCRD."CardType",ODPO."CardCode",
ODPO."DocDate",ODPO."DocTotal"
,ODPO."DocTotal",ODPO."DocTotalFC"
HAVING ( (ODPO."DocTotal") - COALESCE(SUM("DrawnSum"),0) - COALESCE(SUM(RPC1."LineTotal"),0)) >0
order by ODPO."DocNum"