I want to pass a human-readable param into my report, and use that to look up the corresponding ship-from location id. This will allow us to run the report once for each location.
So I added a P_ORGANIZATION_CODE param to the data model, and a WITH block to look up the location.
WITH ShipFromLocation As (
SELECT SHIP_FROM_LOCATION_ID
FROM WSH_DELIVERY_DETAILS
WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM WSH_SHIPPING_PARAMETERS WHERE ORGANIZATION_CODE = :p_organization_code) and ROWNUM=1
)
I will then use NVL() to check if we got that param, and if we did I will filter by the ship-from location.
The WITH block works fine when I set up a new data model with just that param and do a select against it.
WITH ShipFromLocation As (
SELECT SHIP_FROM_LOCATION_ID
FROM WSH_DELIVERY_DETAILS
WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM WSH_SHIPPING_PARAMETERS WHERE ORGANIZATION_CODE = :p_organization_code) and ROWNUM=1
)
SELECT * FROM ShipFromLocation
SHIP_FROM_LOCATION_ID
300000008801401
But when I add this param to my existing data model, select the Data tab and click on View, I get this:
ORA-06550: line 2, column 19: PLS-00302: component 'P_ORGANIZATION_CODE' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored
It is not referring to the WITH block. If I update that to
WHERE ORGANIZATION_CODE = :p_organization_codeZZZ
the result is the same. There is no other reference to P_ORGANIZATION_CODE in the query.
What is the problem?