CREATE TABLE TAG_TBL
(
  OFFER_HEADER_ID  NUMBER,
  SECTION_NO       NUMBER,
  LEVEL_NO         VARCHAR2(100 BYTE),
  ITEM_DESC        VARCHAR2(240 BYTE),
  ITEM_QUANTITY    NUMBER,
  TAG_STATUS       VARCHAR2(1 BYTE),
  TAG_NO           VARCHAR2(100 BYTE)
)


Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.1', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.2', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.3', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.13', 'WFE/BEL/CLI/6/60/2000/', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.14', 'MOT/TESQC/1.5/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.24', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 2, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.25', 'FAN/CEN-BC/BFI/27500/250/CEM/', 3, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 10, '10.26', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.1', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.2', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.3', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.13', 'BFI/RPJ/BH/CEM/25000/1.4/AMB', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.14', 'FAN/CEN-BC/BFI/27500/250/CEM/', 1, 'Y');
Insert into TAG_TBL
   (OFFER_HEADER_ID, SECTION_NO, LEVEL_NO, ITEM_DESC, ITEM_QUANTITY, TAG_STATUS)
 Values
   (76, 20, '20.15', 'MOT/TESQC/30/1440/415-50/FOM/S1', 1, 'N');
COMMIT;


Output Before update

OFFER_HEADER_ID	SECTION_NO	LEVEL_NO	ITEM_DESC			ITEM_QUANTITY	TAG_STATUS	TAG_NO
76		10		10.1	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y	
76		10		10.2	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y	
76		10		10.3	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	
76		10		10.13	WFE/BEL/CLI/6/60/2000/			1		Y	
76		10		10.14	MOT/TESQC/1.5/1440/415-50/FOM/S1	1		N	
76		10		10.24	BFI/RPJ/BH/CEM/25000/1.4/AMB		2		Y	
76		10		10.25	FAN/CEN-BC/BFI/27500/250/CEM/		3		Y	
76		10		10.26	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	
76		20		20.1	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y	
76		20		20.2	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y	
76		20		20.3	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	
76		20		20.13	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y	
76		20		20.14	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y	
76		20		20.15	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	


Output After update

OFFER_HEADER_ID	SECTION_NO	LEVEL_NO	ITEM_DESC			ITEM_QUANTITY	TAG_STATUS	TAG_NO
76		10		10.1	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y		10BF-1-1
76		10		10.2	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y		10FAN-1-1
76		10		10.3	MOT/TESQC/30/1440/415-50/FOM/S1		1		N		
76		10		10.13	WFE/BEL/CLI/6/60/2000/			1		Y		10WFE.1-1	
76		10		10.14	MOT/TESQC/1.5/1440/415-50/FOM/S1	1		N	
76		10		10.24	BFI/RPJ/BH/CEM/25000/1.4/AMB		2		Y		10BFI-2-3	
76		10		10.25	FAN/CEN-BC/BFI/27500/250/CEM/		3		Y		10FAN-2-4
76		10		10.26	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	
76		20		20.1	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y		20BFI-1-1	
76		20		20.2	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y		20FAN-1-1
76		20		20.3	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	
76		20		20.13	BFI/RPJ/BH/CEM/25000/1.4/AMB		1		Y		20BFI-2-2
76		20		20.14	FAN/CEN-BC/BFI/27500/250/CEM/		1		Y		20FAN-2-2
76		20		20.15	MOT/TESQC/30/1440/415-50/FOM/S1		1		N	

Sir, here we need to update column TAG_NO where tag_status is 'Y', 
that basis on each SECTION_NO,LEVEL_NO, ITEM_DESC (partial before first '/') AND ITEM_QUANTITY.

Condition like
section_no (i.e. 10) is mapped with level_no 10.1 to 10.26, 

  If any item_desc (partial before first '/') comes in level_no between 10.1 to 10.26, 

  it will comes in tag_no i.e. section_no||item_desc(partial before first '/')||'-'||previous(item_quantity)(first time 1)||'-'||last(item_quantity).(output is 10BF-1-1)
Like in first row 10BF-1-1 first time 1 to 1 
Next time BFI again comes in level_no 10.24 with item_quantity(i.e. 2) in same section_no(i.e. 10) 
then it will add in previous last quantity+1 to item_quantity(i.e. 2)+1. (output is 10BFI-2-3).