geting wrong value in connect prior

I cannot run your test case because it is missing the im_l_names table.

There also isn't any data for the other tables.  Your connect by is for the item_assembly table but there is no sample data for that.  

So we don't have enough information to say why you are getting 6 instead of 2.

what about this query

select bi.nsc||'-'||bi.ncb||'-'||substr(bi.item_no,1,3)||'-'||substr(bi.item_no,4,7) NSN,n.name||';        '||n.sid name_desc,c.item_rec_status isc, c.structure_level 
  from im_item_bii bi
  left join im_item_assembly a on a.sin = n.sin
  left join im_l_names n on a.sin = bi.sin
  left join im_item_cii c on a.sin = c.sin 
 where a.next_higher_sin != 5507620 
       start with a.sin = 5507620
       connect by  a.next_higher_sin = prior a.sin and LEVEL = 1;

Select allOpen in new window

sory you can use

 CREATE TABLE IM_L_NAMES 
(
  SIN NUMBER(11, 0) NOT NULL 
, NAME VARCHAR2(504 BYTE) 
, SID VARCHAR2(504 BYTE) 
, LONG_NAME VARCHAR2(515 BYTE) 
) ;

Select allOpen in new window

Still doesn't have the data so I cannot see what the select is returning.

hi sorry
insert into IM_L_NAMES (SIN,NAME,SID,LONG_NAME) values(5507620,'test01','tes09','test018');
insert into IM_L_NAMES (SIN,NAME,SID,LONG_NAME) values(150422590,'test01','tes09','test');

I created all your tables.  Loaded the only test data you provided and get no rows selected when I run your select.

You only provided sample data for two of the 4 tables.  So, when the join happens, there is nothing to join on.

Your test case is still incomplete.  Have you tried running it on your side to make sure the select returns 6?

will get back to you

check this script
tablesnrelates.sql

what is expected result?

select bi.nsc||'-'||bi.ncb||'-'||substr(bi.item_no,1,3)||'-'||substr(bi.item_no,4,7) NSN,n.name||';        '||n.sid name_desc,c.item_rec_status isc, c.structure_level 
  from im_item_bii bi
  left join im_item_assembly a on a.sin = bi.sin
  left join im_l_names n on a.sin = bi.sin
  left join im_item_cii c on a.sin = c.sin 
 where a.next_higher_sin != 5507620 
       start with a.sin = 5507620
       connect by  a.next_higher_sin = prior a.sin and LEVEL = 1;

Select allOpen in new window

I get this

NSN	NAME_DESC	ISC	STRUCTURE_LEVEL
1005-00-500-9361	SCREW,FILLER,OIL BUFFER TUBE;        	6	G
1005-00-500-9361	END USE FOR;        MACHINE GUN,12,70 MM,BROWNING,L4	6	G

Select allOpen in new window

fiddle / scripts
https://livesql.oracle.com/apex/livesql/file/content_FEDWK7TRKCSVO2UN55IJRY5HI.html

correct