Anaylitical Function Use

SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type,
quantity, request_id, rn,
MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq
FROM (
SELECT 'Y' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id,
ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn
FROM xxgenascp_forecast_stg
ORDER BY inventory_item_id, forecast_date)
WHERE rn <= p_max_forecastweeks UNION SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type, quantity, request_id, rn, MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq FROM (SELECT 'N' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id, ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn FROM xxgenascp_forecast_stgORDER BY inventory_item_id, forecast_date) WHERE rn > p_max_forecastweeks

Getting current concurrent request number in plsql fired by concurrent request

select FND_GLOBAL.CONC_REQUEST_ID from dual;

To get the Organization Name if you know its Id

select * from org_organization_definition where organization_id = 121;