July 2, 2011 at 12:36 am
hi experts!
i want to create debtors outstanding report.but data is not exactly match.
i want to crate only one bucket which is >90.
please correct the code.
select (select RC.CUSTOMER_NUMBER from RA_CUSTOMERS RC WHERE RC.CUSTOMER_ID=ABC.CUSTOMER_ID )CUSTOMER_NUMBER,
(select RC.CUSTOMER_NAME from RA_CUSTOMERS RC WHERE RC.CUSTOMER_ID=ABC.CUSTOMER_ID )CUSTOMER_NAME,
abc.CT_REFERENCE,abc.COMMENTS,
PRODUCT,
BUISSNESS_CENTRE,
sum(balance_amount) BALANCE,
abc.customer_id
from
(select aps.customer_id,null CT_REFERENCE, null COMMENTS,acr.ATTRIBUTE1 PRODUCT,acr.ATTRIBUTE2 BUISSNESS_CENTRE,sum(aps.AMOUNT_DUE_REMAINING) balance_amount
from
AR_PAYMENT_SCHEDULES_ALL aps,
AR_CASH_RECEIPTS_ALL acr
where 1=1
and aps.CASH_RECEIPT_ID=acr.CASH_RECEIPT_ID
and aps.CUSTOMER_ID=NVL(:customer_id,aps.CUSTOMER_ID)
and acr.ATTRIBUTE1= nvl(:p_product,acr.ATTRIBUTE1)
and acr.ATTRIBUTE2= nvl(:p_buisness_center,acr.ATTRIBUTE2)
AND SYSDATE-APS.DUE_DATE >90
group by aps.customer_id,acr.ATTRIBUTE1,acr.ATTRIBUTE2
union
select aps.customer_id,cust.CT_REFERENCE ,cust.COMMENTS,cust.ATTRIBUTE10 PRODUCT,cust.ATTRIBUTE11 BUISSNESS_CENTRE,sum(aps.AMOUNT_DUE_REMAINING)balance_amount from
AR_PAYMENT_SCHEDULES_ALL aps ,
RA_CUSTOMER_TRX_ALL cust
where 1=1
and aps.CUSTOMER_TRX_ID=cust.CUSTOMER_TRX_ID
and cust.BILL_TO_CUSTOMER_ID=NVL(:customer_id,aps.CUSTOMER_ID)
and cust.ATTRIBUTE10= nvl(:p_product,cust.ATTRIBUTE10)
and cust.ATTRIBUTE11= nvl(:p_buisness_center,cust.ATTRIBUTE11)
AND SYSDATE-APS.DUE_DATE >90
GROUP BY aps.customer_id,cust.ATTRIBUTE10,cust.ATTRIBUTE11,cust.CT_REFERENCE ,cust.COMMENTS) ABC
GROUP BY ABC.CUSTOMER_ID,
PRODUCT,
BUISSNESS_CENTRE,abc.CT_REFERENCE,abc.COMMENTS
ORDER BY CUSTOMER_NAME
July 2, 2011 at 11:55 am
Direct replies here: http://www.sqlservercentral.com/Forums/Topic1135468-391-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply