Qury to find Related Items for an existing Item
declare
a varchar2(1000):= null;
b varchar2(1000):= null;
CURSOR c_cur IS
select
ito.segment1 item,
--ito.description,
ITR.SEGMENT1 RELATED_ITEM,
--itr.description,
ML.MEANING RELATION
--ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
AND ML.LOOKUP_TYPE(+)='MTL_RELATIONSHIP_TYPES'
AND ITO.SEGMENT1 ='CMP-00580';
begin
a:= 'Related Items exists for the Item ' ||'. Please enter Related Item from below list'|| CHR(10);
a := a||rpad('Related Item',19,' ')||rpad('Relation',20,' ')|| CHR(10)||rpad('*************',22,' ')||rpad('******************',23,' ')|| CHR(10);
FOR r_line IN c_cur
LOOP
b:=b||rPAD(R_LINE.RELATED_ITEM,25,' ')||rPAD(R_LINE.RELATION,20,' ')||CHR(10);
end LOOP;
dbms_output.put_line( a||b);
EXCEPTION
when OTHERS then
dbms_output.put_line( 'No data found');
end;
a varchar2(1000):= null;
b varchar2(1000):= null;
CURSOR c_cur IS
select
ito.segment1 item,
--ito.description,
ITR.SEGMENT1 RELATED_ITEM,
--itr.description,
ML.MEANING RELATION
--ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
AND ML.LOOKUP_TYPE(+)='MTL_RELATIONSHIP_TYPES'
AND ITO.SEGMENT1 ='CMP-00580';
begin
a:= 'Related Items exists for the Item ' ||'. Please enter Related Item from below list'|| CHR(10);
a := a||rpad('Related Item',19,' ')||rpad('Relation',20,' ')|| CHR(10)||rpad('*************',22,' ')||rpad('******************',23,' ')|| CHR(10);
FOR r_line IN c_cur
LOOP
b:=b||rPAD(R_LINE.RELATED_ITEM,25,' ')||rPAD(R_LINE.RELATION,20,' ')||CHR(10);
end LOOP;
dbms_output.put_line( a||b);
EXCEPTION
when OTHERS then
dbms_output.put_line( 'No data found');
end;
No comments:
Post a Comment