I am getting the following error when running the below script:
**************** ERROR *****************
SQL> @C:ShyamSpringsTestHIRCH.sql;
START WITH skudc_planorder_cursor.item
*
ERROR at line 36:
ORA-06550: line 36, column 58:
PLS-00382: expression is of wrong type
ORA-06550: line 34, column 23:
PL/SQL: SQL Statement ignored
ORA-06550: line 40, column 10:
PLS-00364: loop index variable 'INNER_LOOP' use is invalid
ORA-06550: line 40, column 7:
PL/SQL: Statement ignored
**************** SCRIPT ****************
DECLARE
n_param3 NUMBER(20,2) :=0;
n_count_subords NUMBER(10) :=0;
BEGIN
FOR skudc_planorder_cursor IN
(
SELECT /*+ RULE */ DISTINCT s.loc, s.item
FROM stsc.sku s, stsc.item i
WHERE s.scen = 0 AND i.scen = 0
AND s.item = i.item
AND s.loc IN ( SELECT NVL(TRIM(si_alt_loc),' ' )
FROM stsc.loc
WHERE scen = 0 AND NVL(TRIM(si_loc_type), ' ' ) =
'DC'
)
AND i.inv_class IN ( SELECT inv_class FROM siviews.si_fg_cls)
AND i.product_group IN ( SELECT 1 FROM
springs.si_jde_user_proc_params
WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
AND UPPER(TRIM(key2)) = 'OVERRIDE'
AND UPPER(TRIM(key3)) IS NULL
AND UPPER(TRIM(param1)) =
i.product_group
)
AND EXISTS ( SELECT 1 FROM stsc.bom b
WHERE scen = 0 AND s.item = b.item
AND EXISTS ( SELECT 1 FROM stsc.loc
WHERE scen = 0 AND b.loc =
NVL(TRIM(si_alt_loc),loc)
AND TRIM(si_seq_ind) = 'Y'
)
)
AND NOT EXISTS ( SELECT 1 FROM stsc.bom b1, stsc.bom b2
WHERE b1.item = b2.subord AND b2.scen = 0)
)
LOOP
FOR inner_loop IN ( SELECT DISTINCT subord
FROM stsc.bom
START WITH skudc_planorder_cursor.item
CONNECT BY PRIOR subord =
skudc_planorder_cursor.item
)
LOOP
IF inner_loop.subord IN ( SELECT item FROM stsc.item
WHERE scen = 0
AND item.product_type IN
( SELECT param2 FROM
springs.si_jde_user_proc_params
WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
AND UPPER(TRIM(key2)) = 'OVERRIDE'
AND TRIM(key3) IS NULL)
) THEN
n_count_subords := n_count_subords + 1;
END IF;
END LOOP;
IF n_count_subords = 0 THEN
UPDATE stsc.sku
SET planleadtime = ( n_param3 * 1440 )
WHERE scen = 0 AND item = skudc_planorder_cursor.item
AND loc = skudc_planorder_cursor.loc;
END IF;
n_count_subords := 0;
END LOOP;
END;
/
Requires Free Membership to View
You have two problems. The first problem (see listing below) is that "Start With" is missing a comparison. For instance: start with manager = 'Greg.'
FOR inner_loop IN ( SELECT DISTINCT subord
FROM stsc.bom
START WITH skudc_planorder_cursor.item
CONNECT BY PRIOR subord = skudc_planorder_cursor.item
The second problem (see listing below) is that you are using the variable "inner_loop.subord" as a loop index variable. This is not allowed for an IF statement:
IF inner_loop.subord IN ( SELECT item FROM stsc.item
WHERE scen = 0
AND item.product_type IN
( SELECT param2
FROM springs.si_jde_user_proc_params
WHERE UPPER(TRIM(key1)) = 'ADJPLANLT'
AND UPPER(TRIM(key2)) = 'OVERRIDE'
AND TRIM(key3) IS NULL)
) THEN
n_count_subords := n_count_subords + 1;
END IF;
This was first published in September 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation