Dynamic grouping of report after every 7 Days
669555Jul 24 2009 — edited Jul 25 2009Hi
I have following query
select
PARTY.PARTY_NAME,
pay.due_date,
pay.trx_date,
PAY.TRX_NUMBER,
PAY.AMOUNT_DUE_ORIGINAL,PAY.AMOUNT_DUE_REMAINING,
pay.due_date - pay.trx_date due_days
--round(to_date(&TO_DATE,'dd-mon-rrrr') - pay.due_date)/7 weeks,
from
hz_parties party,
hz_cust_accounts cust,
ar_payment_schedules_all pay,
RA_CUSTOMER_TRX_ALL CUST_TRX
where
CUST.PARTY_ID = PARTY.PARTY_ID
and PAY.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND CUST_TRX.CUSTOMER_TRX_ID = PAY.CUSTOMER_TRX_ID
and pay.class in ('INV','DM')
and pay.AMOUNT_DUE_REMAINING > '0'
and pay.trx_date between :p_from_date and :p_to_date
My report will take two dates (from and to) as parameters and it should group data week wise.
For eg if i give 01-jul-2009 and 31-jul-2009 it should group the data in 4 weeks i.e 1-7 jul as group1,8-14 jul as group2,15-21jul as group3 and 22-28jul as group4
The Dates can be any so the report should break after every week or 7 days between two given days.
Please help me in acheiving this