Incoming payment detail include A/R inv and A/r Credit memo

[Former Member]

Hi I have a problem on incoming payment query which must show up in one record, but in the attachment below the A/R invoice and A/R credit memo has been showed in separated rows but i want all of the details in the same incoming payment shows in one row only.

Please give me some guideline on this. Thank you.

select

Z.[RCP NO] as 'REC NO',

Z.[RCP Date] as 'DATE',

Z.CardCode as 'BP CODE',

Z.CardName as 'COMPANY',

Z.SalesPerson as 'SALES PERSON',

Z.[Inv Mth] as 'MONTH' ,

CONVERT(DECIMAL(38,2),Z.[Collection Amount]) as 'COLLECTION AMT',

sum(CONVERT(DECIMAL(38,2),Z.Tax)) as 'INV GST',

sum(CONVERT(DECIMAL(38,2),Z.CNGst)) as 'CN GST',

CONVERT(DECIMAL(38,2),(z.[Collection Amount]-sum(z.Tax)+sum(z.CNGst))) as 'AMT ENTITLED',

CONVERT(DECIMAL(38,2),(((z.[Collection Amount]-sum(z.Tax)+sum(z.CNGst)))*0.02) )as 'COMMISSION' from (

SELECT T1.[DocNum] as 'RCP NO',T1.DocDate as 'RCP Date',

right(convert(varchar(9), T3.[DocDate],6),6)  as 'Inv Mth',T1.[cardcode],T1.[cardname],

'SalesPerson'=case when  T0.[InvType]='13' then (select Slpname from OSLP where T3.[SlpCode]=[SlpCode]) else

(select Slpname from OSLP where T4.[SlpCode]=[SlpCode]) end,

'InvoiceNo'=case when T0.[InvType]='13' then T3.[DocNum] else T4.[DocNum]end ,

T0.[InvType],T0.[SumApplied], T4.[VatSum],

((SELECT case when SUM(SUMAPPLIED) is null then '0' else

sum(SumApplied) end FROM RCT2 B WHERE B.DOCNUM=T1.DOCNUM AND B.InvType='13' )-

(SELECT case when SUM(SUMAPPLIED) is null then '0' else

sum(sumapplied) end FROM RCT2 B WHERE B.DOCNUM=T1.DOCNUM AND B.InvType='14' )) as 'Collection Amount'

,'Tax' = case when T0.[InvType]='13' then T3.VatSum else '0' end,

'CNGst' = case when T0.[InvType]='14' then T4.VatSum else '0' end

--,'net' = (((SELECT case when SUM(SUMAPPLIED) is null then '0' else sum(SumApplied) end FROM RCT2 B WHERE B.DOCNUM=T1.DOCNUM AND B.InvType='13' )-(SELECT case when SUM(SUMAPPLIED) is null then '0' else sum(sumapplied) end FROM RCT2 B WHERE B.DOCNUM=T1.DOCNUM AND B.InvType='14' ))- (case when T0.[InvType]='13' then T3.VatSum else '0' end))

  FROM (((ORCT T1 left JOIN RCT2 T0 ON T1.[DocNum] = T0.[DocNum])

  left JOIN OINV T3 ON T0.[DocEntry] = T3.[DocEntry])

   left JOIN ORIN T4 on  T1.[DocNum] = T4.[RECEIPTNUM]  and  T0.[DocEntry] = T4.[DocEntry])

) Z

group by Z.[RCP NO],Z.[RCP Date],Z.[Inv Mth],Z.CardCode,Z.CardName,Z.SalesPerson,Z.[Collection Amount]

order by Z.SalesPerson

[Johan_H]

Hi Kimberly,

Your query has no time restraints, is this by design? As is, you would never know in which year each month is (Z.[Inv Mth]).

The month column (Z.[Inv Mth]) is causing the query to give you multiple lines.

Please test changing this:

right(convert(varchar(9), T3.[DocDate],6),6)  as 'Inv Mth'

to this:

CAST(DATEPART(YEAR, T3.[DocDate]) AS NVARCHAR) + '_' + CAST(DATEPART(MONTH, T3.[DocDate]) AS NVARCHAR)  as 'Inv Mth'

Regards,

Johan

[Former Member]

For the time restraints i just temporary removed it for testing purpose.