FUNCTION get_part_amendment_history(
in_start_date DATE,
in_end_date DATE
) RETURN ie92_tab pipelined
AS
pmrow ie92_row := ie92_row();
start_date DATE;
end_date date;
desc1 varchar2(65);
desc2 varchar2(65);
long_desc varchar2(160);
partmaster_block exception;
BEGIN
start_date := nvl (in_start_date, trunc (CURRENT_DATE) - 1);
end_date := nvl (in_end_date, trunc (CURRENT_DATE));
for part_history in (
select distinct pm.long_part_number,
case
when pm.long_part_number is not null then
(
if ext1.description_sequence is not null then
select ext2.description_text desc1 from extended_part_descriptions ext2
where ext2.part_number = pm.part_number and ext2.description_sequence = 1
and ext2.description_sequence = 1 ;
end if
)
else desc1 || '1' end name1,
CASE pm.part_type
WHEN '0' THEN 'C'
ELSE 'R'
END part_type,
pm.stock_group,
pm.unit_of_issue,
CASE pm.withdrawn_part
WHEN 'Y' THEN 'N'
ELSE 'Y'
end withdrawn_part
from part_master pm
left join part_number_amendment_history pnah
on pm.part_number = pnah.part_number
left join extended_part_descriptions ext1
on pm.part_number = ext1.part_number
and
ext1.description_sequence = 1
where
pnah.history_date >= trunc (start_date)
AND pnah.history_date < trunc (end_date)
AND pnah.vduwka_field_name IN
('STMI02',
'STMI04',
'STMI08',
'STMI17',
'STMI05'))
LOOP
IF part_history.long_part_number IS NULL THEN
RAISE partmaster_block;
end if;
pmrow.part_number := part_history.long_part_number;
pmrow.part_type := part_history.part_type;
pmrow.stock_group := part_history.stock_group;
pmrow.uom := part_history.unit_of_issue;
pmrow.block_status := part_history.withdrawn_part;
PIPE ROW(pmrow);
END loop;
RETURN;
But getting the error below.
- Error(22,29): PL/SQL: SQL Statement ignored
- Error(26,14): PL/SQL: ORA-00907: missing right parenthesis
Thanks