need debtors outstanding report

Question

Post reply

need debtors outstanding report

ruby.malik

Valued Member

Points: 50
More actions
July 2, 2011 at 12:22 am

#240222

hi friends,
do you have debtors outstanding aging report?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ruby.malik (7/2/2011)
hi friends,
do you have debtors outstanding aging report?

If I did, it probably wouldn't work on your system.

Please read the first article I reference below in my signature block regarding asking for help.

ruby.malik Valued Member Points: 50 More actions · Answer 2

sir please check below query & correct me.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I may be wrong but looking at the code I have to ask, is this PL/SQL?

ruby.malik Valued Member Points: 50 More actions · Answer 4

ruby.malik

Valued Member

Points: 50

July 2, 2011 at 3:01 am

#1347422

It is SQL

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 5

ruby.malik (7/2/2011)
It is SQL

It is Oracle SQL, though. SQL Server has no NVL function nor a SYSDATE function.

It would also be helpful if we knew what you thought needed correction. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)