AIM: Bellow query is used for getting the data of AR Collection report for last four weeks in account receivable.
Script:
select
( SELECT TERRITORY_SHORT_NAME
FROM fnd_territories_vl WHERE TERRITORY_CODE=COUNTRY.COUNTRY)COUNTRY,
CURRENCY_CODE,
(SELECT NAME FROM GL_LEDGERS WHERE LEDGER_ID=ACRA.SET_OF_BOOKS_ID)LEDGER_NAME,
Case when (acra.currency_code)=’USD’ THEN 1 else
(select round(conversion_rate,4) from apps.gl_daily_rates_v a where from_currency=acra.currency_code and to_currency=’USD’ and conversion_date=(select to_char(next_day(SYSDATE-7, ‘sunday’)-7) to_date from Dual) and USER_CONVERSION_TYPE=’Corporate’)end as conversion_rate
,SUM(First_week.Amount) First_week
,SUM(Second_week.Amount) Second_week
,SUM(Third_week.Amount) Third_week
,SUM(Fourth_week.Amount) Fourth_week
From ar_cash_receipts_all ACRA
,(select amount,CASH_RECEIPT_ID from ar_cash_receipts_all where TYPE<>’MISC’ AND RECEIPT_DATE between (select to_char((next_day(sysdate, ‘MON’)-7)-7) from_date from dual) AND (select to_char(next_day(sysdate, ‘sunday’)-7) to_date from Dual)) Fourth_week
,(select amount,CASH_RECEIPT_ID from ar_cash_receipts_all where TYPE<>’MISC’ AND RECEIPT_DATE between (select to_char((next_day(SYSDATE-7, ‘MON’)-7)-7) from_date from dual) AND (select to_char(next_day(SYSDATE-7, ‘sunday’)-7) to_date from Dual)) Third_week
,(select amount,CASH_RECEIPT_ID from ar_cash_receipts_all where TYPE<>’MISC’ AND RECEIPT_DATE between (select to_char((next_day(SYSDATE-14, ‘MON’)-7)-7) from_date from dual) AND (select to_char(next_day(SYSDATE-14, ‘sunday’)-7) to_date from Dual)) Second_week
,(select amount,CASH_RECEIPT_ID from ar_cash_receipts_all where TYPE<>’MISC’ AND RECEIPT_DATE between (select to_char((next_day(SYSDATE-21, ‘MON’)-7)-7) from_date from dual)AND (select to_char(next_day(SYSDATE-21, ‘sunday’)-7) to_date from Dual)) First_week
,(select DISTINCT
HRL.COUNTRY,gl.ledger_id ledger_id
from apps.XLE_ENTITY_PROFILES LEP,
apps.XLE_REGISTRATIONS REG,
apps.HR_LOCATIONS_ALL HRL,
apps.gl_ledgers gl,
apps.HR_OPERATING_UNITS HRO
where LEP.TRANSACTING_ENTITY_FLAG = ‘Y’
and LEP.LEGAL_ENTITY_ID = REG.SOURCE_ID
and REG.SOURCE_TABLE = ‘XLE_ENTITY_PROFILES’
and HRL.LOCATION_ID = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG = ‘Y’
and HRO.SET_OF_BOOKS_ID=GL.LEDGER_ID
and LEP.LEGAL_ENTITY_ID = HRO.DEFAULT_LEGAL_CONTEXT_ID
)COUNTRY
where acra.CASH_RECEIPT_ID = Fourth_week.CASH_RECEIPT_ID(+)
and acra.CASH_RECEIPT_ID = Third_week.CASH_RECEIPT_ID(+)
and acra.CASH_RECEIPT_ID = Second_week.CASH_RECEIPT_ID(+)
and acra.CASH_RECEIPT_ID = First_week.CASH_RECEIPT_ID(+)
AND COUNTRY.LEDGER_ID=ACRA.SET_OF_BOOKS_ID
group by SET_OF_BOOKS_ID,COUNTRY.COUNTRY,CURRENCY_CODE
Having (SUM(NVL(First_week.Amount,0))+SUM(NVL(Second_week.Amount,0))+SUM(NVL(Third_week.Amount,0))+SUM(NVL(Fourth_week.Amount,0)))>0
ORDER BY 1;
Table used :- ar_cash_receipts_all, apps.XLE_ENTITY_PROFILES LEP, apps.XLE_REGISTRATIONS REG,
apps.HR_LOCATIONS_ALL HRL, apps.gl_ledgers gl , apps.HR_OPERATING_UNITS HRO
Do drop a note by writing us at Dileep.dinesh@staging.doyensys.com or use the comment section below to ask your questions
