Total Pageviews

Saturday, March 9, 2013


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;

No comments:

Post a Comment