Requirement: Dynamically assign the date value to the main cursor query to fetch data prior to past 3 business days. This is based on the day of the run of the concurrent program

i.e. if the concurrent program is run on SYSDATE i.e. today. IT needs to pick up data prior to last 3 business days and assign the attribution values. Ex as s below

SELECT a.inventory_item_id,
a.segment1,
a.segment2,
a.segment3,
a.organization_id,
a.description,
b.long_description,
a.Inventory_Item_Status_Code,
a.ITEM_CATALOG_GROUP_ID,
c.D_IM_GAAP_LOCK
FROM apps.mtl_system_items_b a,
apps.mtl_system_items_tl b,
APPS.EGO_D_IM_GAAP_LOCK_AGV c
WHERE     a.inventory_item_id = b.inventory_item_id(+)
AND a.organization_id = b.organization_id(+)
AND a.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID(+)
AND a.organization_id = C.ORGANIZATION_ID(+)
AND a.segment1 IN (SELECT VALUE
FROM TABLE (
xxego.xxego_pim_common_api.returnastable (
v_sku_list,
',')))
AND a.organization_id = 82
AND a.segment2 = 'SKU'
AND C.D_IM_GAAP_LOCK = 'Y'
AND TRUNC (a.creation_date) BETWEEN
(SELECT   TRUNC (TO_DATE (pin_start_date, 'DD-MM-YYYY'))
- rnum
FROM (SELECT rnum,
ROW_NUMBER () OVER (ORDER BY rnum) rn
FROM (    SELECT LEVEL rnum
FROM DUAL
CONNECT BY LEVEL <= 1500
ORDER BY 1)
WHERE TO_CHAR (
TRUNC (
TO_DATE (pin_start_date,
'DD-MM-YYYY'))
- rnum,
'DY') NOT IN ('SAT', 'SUN'))
WHERE rn = 3)
AND TRUNC (
TO_DATE (pin_end_date,
'DD-MM-YYYY'))
AND EXISTS
(SELECT 1
FROM Apps.ego_catalog_groups_v
WHERE     catalog_group IN ('IM Service',
'Variable Item')
AND item_catalog_group_id IN ('1201', '418210'));

Above query fetches the data for the SYSDATE - 3 Business days period. This would be the default query to be run if it is WD (i.e. weekdays between 2 and 6 (Monday to Friday) ignoring the week end. However if the business logic needs the job to pick up the data for the previous SAT, SUN & Monday (i.e. previous week) if this concurrent program job is run on the friday considering the 3 day business logic, we need to have the program evaluate the date dynamically to be able to pick and attribute the values.

Note: The above query is the main part of the cursor in the CP. Looking forward to your expertise here.

Basic logic i.e. point 1 is already in place as part of the inline subquery. Need expertise on the 2nd point.

1)Cp should have SKU list and start and end date parameters so user/appsupport can run in manually on adhoc basis.
2)Weekend records will be processed after 3 business days.
e.g. 5th(sat) and 6thSsun) March Records will be processed on 11th(Friday) March along with 7th(monday) March records.

create table calendar_dates (
  calendar_day   date not null primary key,
  is_working_day varchar2(1) not null,
  check ( calendar_day = trunc ( calendar_day ) )
);

where table_date in (
  select calendar_day from calendar_dates
  where  calendar_day = trunc (sysdate)
  and    is_working_day = 'Y'
  order  by calendar_day desc
  fetch  first 3 rows only
);

with working_days as (
  select *
  from   calendar_dates
  where  calendar_day = trunc (sysdate)
  and    is_working_day = 'Y'
  order  by calendar_day desc
  fetch  first 3 rows only
), working_date_range as (
  select min ( calendar_day ) start_date, 
         min ( calendar_day ) end_date
  from   working_days 
)
  select * from your_table yt
  join   working_date_range wdr
  on     yt.table_date between wdr.start_date and wdr.end_date;