Let's Take Assumptions for the Start Date and End Date.
START DATE: SYSDATE-5
END DATE: SYSDATE
Syntax:
select (SYSDATE-5) + level - 1 dt
from dual
connect by level <= (
(SYSDATE) - (SYSDATE-5) + 1
);
Example:
select date'2021-06-08' + level - 1 dt
from dual
connect by level <= (
date'2021-06-14' - date'2021-06-08' + 1
);
1) System Preference Defined Timezone based Date
SELECT
(cast(SYSDATE as timestamp) AT TIME ZONE FND_GLOBAL.TIMEZONE) DT
FROM Dual
2) Convert Date From One Timezone to another Timezone
select
TO_CHAR(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'GMT') at time zone 'Asia/Dubai','dd-Mon-yyyy HH24:mm:ss') D2
from dual
SELECT
*
FROM
(
SELECT
business_unit,
cash_receipt_id,
customer_name,
customer_number,
party_site_number,
receipt_number,
receipt_date,
currency_code,
status,
application_type,
SUM(onacc_amt) onacc_amt,
SUM(unapp_amt) unapp_amt,
nvl((SUM(onacc_amt) + SUM(unapp_amt)), 0) zero_check
FROM
(
SELECT
hou.name business_unit,
acra.cash_receipt_id,
rc.customer_name,
rc.customer_number,
rc.party_site_number,
acra.receipt_number,
acra.receipt_date,
acra.currency_code,
-- ( araa.amount_applied ) amount,
CASE
WHEN araa.status = 'ACC' THEN
araa.amount_applied
ELSE
0
END onacc_amt,
CASE
WHEN araa.status = 'UNAPP' THEN
araa.amount_applied
ELSE
0
END unapp_amt,
araa.status,
araa.application_type
-- , araa.gl_date
FROM
apps.ar_cash_receipts_all acra,
apps.ar_receivable_applications_all araa,
apps.hr_operating_units hou,
zacuser.bu_ra_customers_v rc
WHERE
acra.cash_receipt_id = araa.cash_receipt_id
AND araa.status IN (
'ACC',
'UNAPP'
)
AND hou.organization_id = acra.org_id
AND acra.pay_from_customer = rc.customer_id
AND rc.bill_to_site_use_id = acra.customer_site_use_id
AND acra.org_id IN (
1022,
1099,
1100
)
)
GROUP BY
business_unit,
cash_receipt_id,
customer_name,
customer_number,
party_site_number,
receipt_number,
receipt_date,
currency_code,
status,
application_type
)
WHERE
-- (ONACC_AMT+UNAPP_AMT) <> 0
zero_check <> 0
ORDER BY
business_unit,
customer_name,
receipt_number;